PLSQL Script to Load Item Default Sub-inventories

 

DECLARE
PROCESS_FLAG VARCHAR(40);
ERR_MSG1    VARCHAR2(4000);
ERR_MSG2    VARCHAR2(4000);
ERR_MSG    VARCHAR2(4000);
ITEM_ID NUMBER(38);
secondary_inv_name VARCHAR2(400);
count_num2   NUMBER;
CURSOR C IS
SELECT ITEM_CODE,SUBINVENTORY,ENTRY_ID  FROM    XX_ROHIT_ITEM_SUBINV_DEFAULT
WHERE P_FLAG='G';
BEGIN
FOR I IN C LOOP
PROCESS_FLAG:='Y';
BEGIN
SELECT INVENTORY_ITEM_ID INTO ITEM_ID FROM MTL_SYSTEM_ITEMS_B
WHERE SEGMENT1||'.'||SEGMENT2||'.'||SEGMENT3||'.'||SEGMENT4||'.'||SEGMENT5=I.ITEM_CODE
AND ORGANIZATION_ID=85;
EXCEPTION WHEN NO_DATA_FOUND THEN
PROCESS_FLAG:='N';
ERR_MSG:='ITEM NOT DEFINE';
END ;
BEGIN
SELECT DISTINCT secondary_inventory_name
            INTO secondary_inv_name
              FROM mtl_secondary_inventories_fk_v msf
             WHERE msf.organization_id = 5677
               AND msf.secondary_inventory_name = i.SUBINVENTORY;
EXCEPTION WHEN OTHERS THEN
PROCESS_FLAG:='N';
ERR_MSG1:='SUBINVENTORY NOT DEFINE';
END ;
BEGIN
            SELECT COUNT (*)
              INTO count_num2
              FROM mtl_item_sub_defaults
             WHERE inventory_item_id = item_id
               AND organization_id = 85
               AND UPPER (subinventory_code) = UPPER (secondary_inv_name);
         EXCEPTION
            WHEN NO_DATA_FOUND
            THEN
               count_num2 := 0;
               ERR_MSG2:='ALREADY_DEFINE';
         END;

IF PROCESS_FLAG='Y'  AND count_num2=0   THEN
INSERT INTO mtl_item_sub_defaults
                        (inventory_item_id, organization_id,

                         subinventory_code, default_type, last_update_date,
                         last_updated_by, creation_date, created_by,
                         last_update_login, request_id,
                         program_application_id, program_id,
                         program_update_date
                        )
                 VALUES (item_id,5677,
                         secondary_inv_name,2, SYSDATE,
                         0, SYSDATE,0,
                         NULL, NULL,
                         NULL, NULL,
                         NULL
                        );
                       
UPDATE XX_ROHIT_ITEM_SUBINV_DEFAULT
SET P_FLAG='OK'
WHERE ENTRY_ID=I.ENTRY_ID;
ELSE
UPDATE XX_ROHIT_ITEM_SUBINV_DEFAULT
SET P_FLAG='N',
ERR_MSG=ERR_MSG1||'.'||ERR_MSG||'.'||ERR_MSG2
WHERE ENTRY_ID=I.ENTRY_ID;
END IF;
COMMIT;
END LOOP;

end;