Inter Org Transfer and Subinventory transfer through API

PACKAGE MMD_INTERORG_SUBINVENTORY_TRANS IS
  PROCEDURE TRANSACT_ITEMS;
END;
PACKAGE BODY MMD_INTERORG_SUBINVENTORY_TRANS IS
   PROCEDURE TRANSACT_ITEMS IS
     
      CURSOR C_ITEMS_TRAN IS
      SELECT m.mis_number,M.FROM_ORG,M.TO_ORG,M.ORG_TXFR,m.from_whse,m.to_whse,m.reason_code,l.*,
             lt.lot_id,lt.lot_number,lt.sublot,lt.lot_grade,lt.lot_status,
             lt.from_location,lt.to_location,lt.issue_qty,lt.received_qty
      FROM mmd_trans_mst m,mmd_trans_lines l,mmd_trans_lot lt
      where m.mis_trn_id = l.mis_trn_id
      AND l.mis_line_id = lt.mis_line_id
      AND m.from_whse = :sddl_mis_mst.from_whse
      AND m.mis_number = :sddl_mis_mst.mis_number
      AND M.ORG_TXFR = 'N'
      AND nvl(lt.received_qty,0) > 0
      ORDER BY l.mis_line_id,lt.mis_lot_id;
 -- Mahammad added cursor for inter_org transfer on 16.SEP.12    
     
      CURSOR C_ITEMS_TRAN_INTORG IS
      SELECT M.MIS_NUMBER,M.FROM_ORG,M.TO_ORG,M.ORG_TXFR,M.FROM_WHSE,M.TO_WHSE,M.REASON_CODE,L.*,
            LT.LOT_ID,LT.LOT_NUMBER,LT.SUBLOT,LT.LOT_GRADE,LT.LOT_STATUS,
            LT.FROM_LOCATION,LT.TO_LOCATION,LT.ISSUE_QTY,LT.RECEIVED_QTY
      FROM mmd_trans_mst m,mmd_trans_lines l,mmd_trans_lot lt
      WHERE M.MIS_TRN_ID = L.MIS_TRN_ID
      AND L.MIS_LINE_ID = LT.MIS_LINE_ID
     AND M.TO_ORG=:SDDL_MIS_MST.TO_ORG
     AND m.from_whse = :sddl_mis_mst.from_whse
      AND M.MIS_NUMBER = :SDDL_MIS_MST.MIS_NUMBER
     AND M.ORG_TXFR = 'Y'
      AND nvl(lt.received_qty,0) > 0
      ORDER BY l.mis_line_id,lt.mis_lot_id;
--
      l_TRANSACTION_INTERFACE_ID NUMBER;
      l_transaction_header_id NUMBER;
      l_primary_UOM VARCHAR2(10);
      l_sec_UOM VARCHAR2(10);
      l_org_id number;
      l_return_status VARCHAR2(100);
      L_TRANS_COUNT NUMBER;
      L_MSG_DATA VARCHAR2(200);
      L_MSG_CNT NUMBER;
      l_init_msg_list VARCHAr2(10):= 'T';
      l_commit VARCHAr2(10):= 'T';
      l_validation_level NUMBER:= 1;
      V_RET_VAL NUMBER;
      l_from_loc_id NUMBER;
      l_to_loc_id NUMBER;
      V_GROUP_ID NUMBER;
      v_header_id number;
      l_TRANSACT_INTERFACE_ID number;
      l_from_orgid number;
      l_to_orgid number;
   BEGIN
  
   ---- Mahammad added for inter_org transfer on 16.SEP.12
begin 
  if :SDDL_MIS_MST.ORG_TXFR='Y' then
 
   FOR R_ITEMS_TRAN_INTORG IN C_ITEMS_TRAN_INTORG  
    LOOP
     select organization_id
   into l_from_orgid
   from org_organization_definitions
   where organization_code=R_ITEMS_TRAN_INTORG.FROM_ORG;
   
   select organization_id
   into l_to_orgid
   from org_organization_definitions
   where organization_code=R_ITEMS_TRAN_INTORG.TO_ORG;

 
       IF R_ITEMS_TRAN_INTORG.from_location IS NOT NULL THEN
      select inventory_location_id
       INTO l_from_loc_id
       from MTL_ITEM_LOCATIONS_KFV MIL
      where mil.Concatenated_segments=R_ITEMS_TRAN_INTORG.from_location
        and  Organization_id=l_from_orgid;
       END IF;       
  
     select inventory_location_id
       INTO l_to_loc_id
       from MTL_ITEM_LOCATIONS_KFV MIL
      where mil.inventory_item_id=R_ITEMS_TRAN_INTORG.item_id
        and  Organization_id=l_to_orgid;      
       select  primary_uom_code,secondary_uom_code
         INTO  l_primary_UOM,l_sec_UOM
         FROM  MTL_SYSTEM_ITEMS
        WHERE  INVENTORY_ITEM_ID=R_ITEMS_TRAN_INTORG.item_id
         and  Organization_id=:GLOBAL.ORG_ID;
       
     select RCV_TRANSACTIONS_INTERFACE_S.NEXTVAL
        into l_transaction_INTERFACE_ID
      from DUAL;
  
     SELECT MTL_MATERIAL_TRANSACTIONS_S.NEXTVAL
       INTO l_TRANSACT_INTERFACE_ID
       FROM DUAL;
     SELECT MTL_MATERIAL_TRANSACTIONS_S.NEXTVAL
       INTO l_transaction_header_id
       FROM DUAL;
      
     select RCV_INTERFACE_GROUPS_S.NEXTVAL
    into V_GROUP_ID
   from dual;

      select rcv_headers_interface_s.nextval
    into v_header_id
   from dual;
        
     INSERT INTO MTL_TRANSACTIONS_INTERFACE
       ( CREATION_DATE,
       CREATED_BY,
       LAST_UPDATE_DATE,
       LAST_UPDATED_BY,
       last_update_login ,
       SOURCE_CODE,
       SOURCE_LINE_ID,
       SOURCE_HEADER_ID,
       PROCESS_FLAG,
       INVENTORY_ITEM_ID,
       ORGANIZATION_ID,
       SUBINVENTORY_CODE,
       TRANSACTION_TYPE_ID,
       TRANSACTION_QUANTITY,
       TRANSACTION_UOM,
       TRANSACTION_DATE,
       TRANSFER_ORGANIZATION,
       TRANSFER_SUBINVENTORY,
       TRANSACTION_MODE,
       REVISION,
       SHIPMENT_NUMBER,
       TRANSACTION_HEADER_ID ,
       TRANSACTION_INTERFACE_ID,
       TRANSFER_LOCATOR ,
       locator_id
       )
        VALUES
        (sysdate,
         1234,
         sysdate,
         1234,
         2213,
         R_ITEMS_TRAN_INTORG.MIS_NUMBER,
         R_ITEMS_TRAN_INTORG.MIS_LINE_ID,
         R_ITEMS_TRAN_INTORG.MIS_TRN_ID,
         1,--process_flag,
         R_ITEMS_TRAN_INTORG.item_id,
         l_from_orgid,
         R_ITEMS_TRAN_INTORG.FROM_ORG,
         3,--TRANSACTION_TYPE_ID
         (R_ITEMS_TRAN_INTORG.received_qty)*(-1),
         l_primary_UOM,
         sysdate,
         l_to_orgid,
         R_ITEMS_TRAN_INTORG.TO_ORG,
         1,--TRANSACTION_MODE
         null,
         R_ITEMS_TRAN_INTORG.MIS_NUMBER,
         l_transaction_header_id,
         l_TRANSACT_INTERFACE_ID,
         l_to_loc_id,
         l_from_loc_id                
         );
       
                
         INSERT
         INTO MTL_TRANSACTION_LOTS_INTERFACE
       (
         TRANSACTION_INTERFACE_ID,
         SOURCE_CODE,
         SOURCE_LINE_ID,
         LOT_NUMBER,
         TRANSACTION_QUANTITY,  
         CREATED_BY,
         CREATION_DATE,
         LAST_UPDATED_BY,
         LAST_UPDATE_DATE,
         last_update_login     
       )
        VALUES
       (l_TRANSACT_INTERFACE_ID,
         R_ITEMS_TRAN_INTORG.MIS_NUMBER,
         R_ITEMS_TRAN_INTORG.MIS_LINE_ID,
         R_ITEMS_TRAN_INTORG.LOT_NUMBER,
         (R_ITEMS_TRAN_INTORG.received_qty)*(-1),
         1234,
         sysdate,
         1234,
         sysdate,
         2213
       );
   
      INSERT INTO RCV_HEADERS_INTERFACE
      (
      HEADER_INTERFACE_ID,
      GROUP_ID,
      PROCESSING_STATUS_CODE,
      RECEIPT_SOURCE_CODE,
      TRANSACTION_TYPE,
      AUTO_TRANSACT_CODE,
      LAST_UPDATE_DATE,
      LAST_UPDATED_BY,
      CREATION_DATE,
      CREATED_BY,
      VALIDATION_FLAG,
      COMMENTS,
      SHIPMENT_NUM,
      FROM_ORGANIZATION_ID,
      SHIP_TO_ORGANIZATION_ID
      )
      VALUES
      (
      V_HEADER_ID, -- HEADER_INTERFACE_ID,
      V_GROUP_ID, -- GROUP_ID,
      'PENDING', -- PROCESSING_STATUS_CODE,
      'INVENTORY',-- RECEIPT_SOURCE_CODE,
      'NEW',-- TRANSACTION_TYPE,
      'DELIVER',-- AUT_TRANSACT_CODE,
      SYSDATE, -- LAST_UPDATE_DATE,
      1234,-- LAST_UPDATE_BY,
      SYSDATE, -- CREATION_DATE,
      1234,-- CREATED_BY,
      'Y',-- VALIDATION_FLAG,
      'Receiving Through Interface',-- COMMENTS,
      R_ITEMS_TRAN_INTORG.MIS_NUMBER,-- SHIPMENT_NUM,
      l_from_orgid,-- FROM_ORGANIZATION_ID,
      l_to_orgid-- SHIP_TO_ORGANIZATION_ID
      );
        
        
       insert into RCV_TRANSACTIONS_INTERFACE
       (
       INTERFACE_TRANSACTION_ID,
       HEADER_INTERFACE_ID,
       GROUP_ID,
       TRANSACTION_TYPE,
       TRANSACTION_DATE,
       PROCESSING_STATUS_CODE,
       PROCESSING_MODE_CODE,
       TRANSACTION_STATUS_CODE,
       QUANTITY,
       LAST_UPDATE_DATE,
       LAST_UPDATED_BY,
       CREATION_DATE,
       CREATED_BY,
       RECEIPT_SOURCE_CODE,
       DESTINATION_TYPE_CODE,
       AUTO_TRANSACT_CODE,
       SOURCE_DOCUMENT_CODE,
       UNIT_OF_MEASURE,
       ITEM_ID,
       UOM_CODE,     
       SHIPMENT_HEADER_ID,
       SHIPMENT_LINE_ID,
       TO_ORGANIZATION_ID,
       SUBINVENTORY,
       FROM_ORGANIZATION_ID,
       FROM_SUBINVENTORY
       )
       VALUES
       (
       L_TRANSACTION_INTERFACE_ID,-- INTERFACE_TRANSACTION_ID,
       V_HEADER_ID, --- HEADER_INTERFACE_ID,
       V_GROUP_ID, --- GROUP_ID,
       'RECEIVE',-- TRANSACTION_TYPE,
       SYSDATE, -- TRANSACTION_DATE,
       'PENDING',-- PROCESSING_STATUS_CODE,
       'BATCH',-- PROCESSING_MODE_CODE,
       'PENDING',-- TRANSACTION_STATUS_CODE,
       (R_ITEMS_TRAN_INTORG.received_qty),-- QUANTITY,
       SYSDATE, -- LAST_UPDATE_DATE,
       1234,-- LAST_UPDATED_BY,
       SYSDATE, -- CREATION_DATE,
       1234,-- CREATED_BY,
       'INVENTORY',-- RECEIPT_SOURCE_CODE,
       'INVENTORY',-- DESTINATION_TYPE_CODE,
       'DELIVER',-- AUTO_TRANSACT_CODE,
       'INVENTORY',-- SOURCE_DOCUMENT_CODE,
       l_primary_UOM,-- UNIT_OF_MEASURE,
       R_ITEMS_TRAN_INTORG.item_id,-- ITEM_ID,
       l_primary_UOM,-- UOM_CODE,     
       V_HEADER_ID,-- SHIPMENT_HEADER_ID,
       V_HEADER_ID,-- SHIPMENT_LINE_ID,
       l_to_orgid,-- TO_ORGANIZATION_ID,
       R_ITEMS_TRAN_INTORG.TO_ORG,-- SUBINVENTORY_ID,
       l_from_orgid,-- FROM_ORGANIZATION_ID,
       R_ITEMS_TRAN_INTORG.FROM_ORG -- FROM_SUBINVENTORY
       );     
        
           
         V_RET_VAL:=INV_TXN_MANAGER_PUB.process_transactions
        ( p_api_version => 1.0 ,
          p_init_msg_list => l_init_msg_list ,
          p_commit => l_commit ,
          p_validation_level =>l_validation_level ,
          x_return_status => l_return_status ,
          x_msg_count => l_msg_cnt ,
          x_msg_data => l_msg_data ,
          x_trans_count => l_trans_count ,
          p_table => 1 ,
          P_HEADER_ID => L_TRANSACTION_HEADER_ID);
       
               
     IF (l_return_status <> 'S') THEN
       l_msg_cnt:=nvl(l_msg_cnt,0)+1;
       For i IN 1..l_msg_cnt LOOP
         IF i = 1 then
            l_msg_data :='Error for Item : '|| R_ITEMS_TRAN_INTORG.LOT_NUMBER  ||chr(10) || fnd_msg_pub.get(i,'F');
         ELSE
             l_msg_data := fnd_msg_pub.get(i,'F');        
         END IF;
          fnd_message.set_string(substrb(l_msg_data,1,2000));
          fnd_message.show;
          raise form_trigger_failure;
       END LOOP;  
      END IF;
   end loop;
 end if;
exception
 when others then
  message('cursor in exception of interorg transfer'||sqlerrm);
end;  
 --      
 -- if codition for subinventory transfer
begin
if :SDDL_MIS_MST.ORG_TXFR='N' then

    FOR R_ITEMS_TRAN IN C_ITEMS_TRAN
  
    LOOP
     IF R_ITEMS_TRAN.from_location IS NOT NULL THEN
      select inventory_location_id
        INTO l_from_loc_id
        from MTL_ITEM_LOCATIONS_KFV MIL
       where mil.Concatenated_segments=R_ITEMS_TRAN.from_location
         and  Organization_id=:GLOBAL.ORG_ID;
     END IF;
     
     IF R_ITEMS_TRAN.to_location IS NOT NULL THEN
      select inventory_location_id
        INTO l_to_loc_id
        from MTL_ITEM_LOCATIONS_KFV MIL
       where mil.Concatenated_segments=R_ITEMS_TRAN.to_location
         and  Organization_id=:GLOBAL.ORG_ID;
     END IF;
       select  primary_uom_code,secondary_uom_code
         INTO  l_primary_UOM,l_sec_UOM
         FROM  MTL_SYSTEM_ITEMS
        WHERE  INVENTORY_ITEM_ID=R_ITEMS_TRAN.item_id
          and  Organization_id=:GLOBAL.ORG_ID;
       
     SELECT MTL_MATERIAL_TRANSACTIONS_S.NEXTVAL
       INTO l_TRANSACTION_INTERFACE_ID
       FROM DUAL;
     SELECT MTL_MATERIAL_TRANSACTIONS_S.NEXTVAL
       INTO l_transaction_header_id
       FROM DUAL;
  
    
   INSERT INTO MTL_TRANSACTIONS_INTERFACE
  (
    created_by ,
    creation_date ,
    last_updated_by ,
    last_update_date ,
    last_update_login ,
    organization_id,
    inventory_item_id ,
    subinventory_code ,
    locator_id ,   
    process_flag ,
    transaction_mode ,
    transaction_source_name,
    source_code ,
    source_header_id ,
    source_line_id ,
    transaction_date ,
    transaction_header_id ,
    transaction_interface_id ,
    transaction_type_id ,
    transaction_quantity ,
    transaction_uom ,
    transfer_locator ,
    transfer_subinventory
  )
   VALUES
   (1234,
    sysdate,
    1234,
    sysdate,
    2213,
    :GLOBAL.ORG_ID,
    R_ITEMS_TRAN.item_id,
    R_ITEMS_TRAN.from_whse,
    l_from_loc_id,
    1,--process_flag,
    2,--transaction_mode
    R_ITEMS_TRAN.MIS_NUMBER,
    R_ITEMS_TRAN.MIS_NUMBER,
    R_ITEMS_TRAN.MIS_TRN_ID,
    R_ITEMS_TRAN.MIS_LINE_ID,
    sysdate,
    l_transaction_header_id, --header_id
    l_TRANSACTION_INTERFACE_ID, -- interface_id
    2,--subinventory
    (R_ITEMS_TRAN.received_qty)*(-1),
    l_primary_UOM,
    l_to_loc_id,
    R_ITEMS_TRAN.to_whse
   );
  

   INSERT
    INTO MTL_TRANSACTION_LOTS_INTERFACE
  (
    TRANSACTION_INTERFACE_ID,
    SOURCE_CODE,
    SOURCE_LINE_ID,
    LOT_NUMBER, 
    TRANSACTION_QUANTITY, 
    CREATED_BY,
    CREATION_DATE,
    LAST_UPDATED_BY,
    LAST_UPDATE_DATE,   
    LAST_UPDATE_LOGIN
  )
  VALUES
  (l_TRANSACTION_INTERFACE_ID,
    R_ITEMS_TRAN.MIS_NUMBER,
    R_ITEMS_TRAN.MIS_LINE_ID,
    R_ITEMS_TRAN.LOT_NUMBER,
    (R_ITEMS_TRAN.received_qty)*(-1),
    1234,
    sysdate,
    1234,
    sysdate,
    2213
  );
 
    
   V_RET_VAL:=INV_TXN_MANAGER_PUB.process_transactions
   ( p_api_version => 1.0 ,
     p_init_msg_list => l_init_msg_list ,
     p_commit => l_commit ,
     p_validation_level =>l_validation_level ,
     x_return_status => l_return_status ,
     x_msg_count => l_msg_cnt ,
     x_msg_data => l_msg_data ,
     x_trans_count => l_trans_count ,
     p_table => 1 ,
     P_HEADER_ID => L_TRANSACTION_HEADER_ID);

   IF (l_return_status <> 'S') THEN
   l_msg_cnt:=nvl(l_msg_cnt,0)+1;
       For i IN 1..l_msg_cnt LOOP
         IF i = 1 then
            l_msg_data :='Error for Item : '|| R_ITEMS_TRAN.LOT_NUMBER  ||chr(10) || fnd_msg_pub.get(i,'F');
         ELSE
             l_msg_data := fnd_msg_pub.get(i,'F');        
         END IF;
          fnd_message.set_string(substrb(l_msg_data,1,2000));
          fnd_message.show;
          raise form_trigger_failure;
       END LOOP;  
  END IF;    
      
   END LOOP;
end if;
exception
 when others then
  message('cursor in exception of subinventory transfer'||sqlerrm);
end;  
-- end if for subinventory transfer
 
   END  TRANSACT_ITEMS;  
END;