Wednesday, October 31, 2012

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;

22 comments:

  1. can u plz send script for ur custom tables which u hav used in this API. I mean for the below tables.. mmd_trans_mst m
    mmd_trans_lines
    mmd_trans_lot
    what are all the fields and data types u hav used for the above tables u hav created

    ReplyDelete
    Replies
    1. sent script files to your personal mail id. And also sent package script file.

      Delete
    2. can u plz send script for ur custom tables which u hav used in this API. I mean for the below tables.. mmd_trans_mst m
      mmd_trans_lines
      mmd_trans_lot
      what are all the fields and data types u hav used for the above tables u hav created

      Delete
  2. and also the sample data template to load into tat tables...hav u used sql loader to load data into ur tables

    ReplyDelete
    Replies
    1. I was not used SQL*Loader program to upload the data into custom tables. Actually We deveopled custom form, records inserted into staging tables through custom form.

      As your requirement you can use SQL*Loader program to upload the data into custom tables.

      And also by using package we can insert data into interface tables and also through API data move into base tables.

      Delete
    2. Aslamoalikum, can you pls send your custom table script with data type. thanks in advance.

      Delete
  3. Alsalam alykom
    thanks for ur effore
    i want to know how do u display these message fnd_message.set_string(substrb(l_msg_data,1,2000));
    fnd_message.show;
    i mean , do u see the output through toad ? or a form or what ?

    thanks a lot

    ReplyDelete
    Replies
    1. Valekuma assalam

      fnd_message.set_string(substrb(l_msg_data,1,2000));
      fnd_message.show;

      This one used in form to display the message.

      Delete
  4. can u plz send script for ur custom tables which u hav used in this API.
    I mean for the below tables.. mmd_trans_mst m
    mmd_trans_lines
    mmd_trans_lot
    what are all the fields and data types u hav used for the above tables u hav created

    ReplyDelete
  5. can u plz send script for ur custom tables which u hav used in this API.
    I mean for the below tables.. mmd_trans_mst m
    mmd_trans_lines
    mmd_trans_lot
    what are all the fields and data types u hav used for the above tables u hav created

    ReplyDelete
  6. Assalamun Aleykum Mahammad Sultan,
    Coul you please send scripts for custom tables which you have used in the API.
    mmd_trans_mst
    mmd_trans_lines
    mmd_trans_lot

    Thanks in adv.
    BR Aygul

    ReplyDelete
  7. can u plz send script for ur custom tables which u hav used in this API

    ReplyDelete
  8. can u plz send script for ur custom tables which u hav used in this API

    ReplyDelete
  9. Please send me staging tables scripts and data

    ReplyDelete
  10. Hi Mahammad Sultan,

    This post is useful for me and Please send custom tables script.

    Thanks
    Shashi

    ReplyDelete
  11. Please send me your script and package script as this would be very helpful with a current project of mine. Thank you

    ReplyDelete
  12. hi please end the script for customized tables pls anyone help me..

    ReplyDelete
  13. Regards
    Sridevi Koduru (Senior Oracle Apps Trainer Oracleappstechnical.com)
    LinkedIn profile - https://in.linkedin.com/in/sridevi-koduru-9b876a8b
    Please Contact for One to One Online Training on Oracle Apps Technical, Financials, SCM, SQL, PL/SQL, D2K at training@oracleappstechnical.com | +91 - 9581017828.

    ReplyDelete
  14. Hi Sir,

    Can you please script file for creating table on my email ID shyamheritage06@gmail.com

    ReplyDelete
  15. Hi, Its is really good api. very helpfull. Could you please send me custom table scripts as well.
    I mean for the below tables.. mmd_trans_mst m
    mmd_trans_lines
    mmd_trans_lot

    parveen50chauhan@gmail.com

    ReplyDelete
  16. Hi, Its is really good api. very helpfull. Could you please send me custom table scripts as well.
    I mean for the below tables.. mmd_trans_mst m
    mmd_trans_lines
    mmd_trans_lot
    Abody711@yahoo.com

    ReplyDelete
  17. Please send me your script and package script as this would be very helpful with a current project of mine. Thank you

    ReplyDelete