Item Sub Inventories Conversion Script

Staging Table Script:

  CREATE TABLE XXXX_INV_ITEM_SUB_INV_STG

   ( "OLD_INV_ORGANIZATION_CODE" VARCHAR2(250 BYTE), 

"INVENTORY_ORGANIZATION_CODE" VARCHAR2(250 BYTE), 

"OLD_SUBINVENTORY" VARCHAR2(250 BYTE), 

"SUBINVENTORY_CODE" VARCHAR2(250 BYTE), 

"OLD_ITEM_CODE" VARCHAR2(250 BYTE), 

"ITEM_CODE" VARCHAR2(250 BYTE), 

"DESCRIPTION" VARCHAR2(250 BYTE), 

"MIN_MAX_PLANNING" VARCHAR2(250 BYTE), 

"MIN_QTY" NUMBER, 

"MAX_QTY" NUMBER, 

"OLD_UNIT_OF_MEASURE" VARCHAR2(250 BYTE), 

"UOM" VARCHAR2(250 BYTE), 

"ORDER_MOD_FIX_LOT_MULTIPLE" VARCHAR2(250 BYTE), 

"SOURCING_TYPE" VARCHAR2(250 BYTE), 

"SOURCING_ORGANIZATION" VARCHAR2(250 BYTE), 

"OLD_SOURCING_SUBINVENTORY" VARCHAR2(250 BYTE), 

"SOURCING_SUBINVENTORY" VARCHAR2(250 BYTE), 

"ERROR_MSG" VARCHAR2(4000 BYTE), 

"STATUS" VARCHAR2(100 BYTE), 

"REFERENCE1" VARCHAR2(150 BYTE), 

"PROGRAM_ID" NUMBER, 

"SOURCE" VARCHAR2(20 BYTE)

   );

Package Specification ;

CREATE OR REPLACE PACKAGE xxxx_item_sub_inv_conv_pkg
AS
   PROCEDURE xxxx_item_sub_inv_conv_proc (
      errbuff     OUT      VARCHAR2,
      retcode     OUT      NUMBER);
END xxxx_item_sub_inv_conv_pkg;
/

Package Body ;

CREATE OR REPLACE PACKAGE BODY APPS.xxxx_item_sub_inv_conv_pkg
AS
   PROCEDURE xxxx_item_sub_inv_conv_proc (
      errbuff     OUT      VARCHAR2,
      retcode     OUT      NUMBER)
   AS
      CURSOR c_sub_inv
      IS
         SELECT old_inv_organization_code,inventory_organization_code,old_subinventory,subinventory_code,
               OLD_ITEM_CODE,ITEM_CODE,DESCRIPTION,MIN_MAX_PLANNING,MIN_QTY,MAX_QTY,OLD_UNIT_OF_MEASURE,
               uom,order_mod_fix_lot_multiple,
               DECODE(sourcing_type,'Subinventory',3,'Supplier',2,'Inventory',1,sourcing_type) sourcing_type,
               SOURCING_ORGANIZATION,OLD_SOURCING_SUBINVENTORY,SOURCING_SUBINVENTORY
               FROM xxxx_inv_item_sub_inv_stg xis
          WHERE status = 'V'
          AND NOT EXISTS (SELECT 1
                         FROM mtl_item_sub_inventories   misi
                              ,mtl_secondary_inventories msi
                              ,mtl_system_items_b        msb
                              ,mtl_parameters            mp
                         WHERE 1=1
                         AND misi.inventory_item_id=msb.inventory_item_id
                         AND misi.organization_id= msb.organization_id
                         AND misi.secondary_inventory=msi.secondary_inventory_name
                         AND misi.organization_id=mp.organization_id
                         AND mp.organization_code=xis.inventory_organization_code
                         AND msb.segment1=xis.item_code
                         AND msi.secondary_inventory_name=xis.subinventory_code);

      ln_org_id        NUMBER;
      ln_item_id       NUMBER;
      ln_source_org    NUMBER;
      LN_CONC_REQ_ID   number;
      LN_PROCESSED     number:= 0;
      ln_not_processed number:= 0;
      lc_error_msg     VARCHAR2 (4000);
      lc_error_flag    VARCHAR2 (1);
      LN_COUNT1        number          := 0;
      lc_ins_err       VARCHAR2(1000);
      ln_inventory_planning_code  NUMBER;
     
   BEGIN
      LN_CONC_REQ_ID := FND_PROFILE.value ('CONC_REQUEST_ID');
      FND_FILE.PUT_LINE(FND_FILE.log,' CONC_REQUEST_ID : '||LN_CONC_REQ_ID);
      FOR a_cur IN c_sub_inv
      LOOP
        LC_ERROR_FLAG:='N';      
         begin
              SELECT organization_id INTO ln_org_id FROM org_organization_definitions
               WHERE organization_code = a_cur.inventory_organization_code;
 
              SELECT inventory_item_id INTO ln_item_id FROM mtl_system_items_b
               WHERE segment1 = a_cur.item_code AND organization_id = ln_org_id;

              begin
                    SELECT organization_id INTO ln_source_org FROM org_organization_definitions
                     where ORGANIZATION_CODE = A_CUR.SOURCING_ORGANIZATION;
              EXCEPTION
                  when NO_DATA_FOUND then
                        ln_source_org:='';
                  WHEN OTHERS THEN
                        fnd_file.put_line (fnd_file.LOG, SQLERRM||' Error occured while fetching source organization.');
              END;

           UPDATE xxxx_inv_item_sub_inv_stg SET program_id = ln_conc_req_id WHERE status = 'V'
               AND old_inv_organization_code = a_cur.old_inv_organization_code
               AND old_subinventory = a_cur.old_subinventory
               and OLD_ITEM_CODE = A_CUR.OLD_ITEM_CODE;
            COMMIT;
         EXCEPTION
            WHEN OTHERS
            then
               lc_error_flag='Y';
               lc_error_msg := SQLERRM;
               xxxx_conv_error_insert_proc ('ITEM SUBINVENTORY','I','PROGRAM_ID',ln_conc_req_id,lc_error_msg,ln_conc_req_id,NULL,NULL,NULL,NULL,lc_ins_err);
               fnd_file.put_line (fnd_file.LOG,'Error in Fetching Program Id');
         END;

         begin
            SELECT DECODE(a_cur.min_qty,NULL,6,2) INTO ln_inventory_planning_code FROM dual;
           
            fnd_file.put_line (fnd_file.LOG,'enters into sub inventory loop with -'
                               || a_cur.inventory_organization_code ||'-'||ln_item_id||'-'||a_cur.max_qty||'-'||a_cur.min_qty||'-'||ln_org_id||'-'||
                             a_cur.subinventory_code||'-'|| ln_source_org||'-'||a_cur.sourcing_subinventory||'-'||a_cur.sourcing_type
                              );

            INSERT INTO mtl_item_sub_inventories
                        (created_by, creation_date, inventory_item_id,
                         inventory_planning_code, last_update_date,
                         last_update_login,
                         last_updated_by, max_minmax_quantity,
                         min_minmax_quantity, organization_id,
                         secondary_inventory, source_organization_id,
                         source_subinventory, source_type
                        )
                 VALUES (fnd_profile.VALUE ('USER_ID'), SYSDATE, ln_item_id,
                         ln_inventory_planning_code,                         --INVENTORY_PLANNING_CODE,
                           SYSDATE,
                         fnd_profile.VALUE ('USER_ID'),
                         fnd_profile.VALUE ('USER_ID'), a_cur.max_qty,
                         a_cur.min_qty, ln_org_id,
                         a_cur.subinventory_code, ln_source_org,
                         a_cur.sourcing_subinventory, a_cur.sourcing_type
                        );

            fnd_file.put_line (fnd_file.LOG,'Inserted into Item Sub Inventories Table');
            commit;
         EXCEPTION
            WHEN OTHERS
            THEN
               lc_error_flag := 'Y';
               LC_ERROR_MSG :=SQLERRM;
               xxxx_conv_error_insert_proc ('ITEM SUBINVENTORY','I','Insertion','Organization_code : '||a_cur.old_inv_organization_code||'Subinventory : '||a_cur.old_subinventory||'Item Code : '||A_CUR.OLD_ITEM_CODE,lc_error_msg,ln_conc_req_id,NULL,NULL,NULL,NULL,lc_ins_err);
               fnd_file.put_line(fnd_file.LOG,'Exception Occured  while inserting into Item Sub Inventories '|| SQLERRM);
         END;

         BEGIN
            IF lc_error_flag = 'Y' THEN
               UPDATE xxxx_inv_item_sub_inv_stg
                  SET status = 'E',error_msg=lc_error_msg
                WHERE old_inv_organization_code =a_cur.old_inv_organization_code
                  AND old_subinventory = a_cur.old_subinventory
                  AND old_item_code = a_cur.old_item_code
                  and PROGRAM_ID = LN_CONC_REQ_ID;
              commit;
               LN_NOT_PROCESSED := LN_NOT_PROCESSED + 1;
               fnd_file.put_line (fnd_file.LOG,'Error Occurred status set to E');
            ELSE
               UPDATE xxxx_inv_item_sub_inv_stg
                  SET status = 'P'
                WHERE old_inv_organization_code =a_cur.old_inv_organization_code
                  AND old_subinventory = a_cur.old_subinventory
                  AND old_item_code = a_cur.old_item_code
                  and PROGRAM_ID = LN_CONC_REQ_ID;
                commit;
                  LN_PROCESSED:=LN_PROCESSED+1;
               fnd_file.put_line (fnd_file.LOG, 'Record is Processed status set to P');
            END IF;
         END;
      END LOOP;
      FND_FILE.PUT_LINE (FND_FILE.log,'Number of Records Processed : '||LN_PROCESSED);
      fnd_file.put_line (fnd_file.LOG,'Number of Records Not Processed : '||ln_not_processed);
   END xxxx_item_sub_inv_conv_proc;
END xxxx_item_sub_inv_conv_pkg;
/