Saturday 6 January 2018

PLSQL Script to Load Item Default Subinventories


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;






5 comments:

12345 said...

Good Blog , Thanks for sharing this informative article.
Oracle Fusion HCM Online Training
Oracle Fusion SCM Online Training
Oracle Fusion Financials Online Training
Big Data and Hadoop Training In Hyderabad

12345 said...

Good Blog , Thanks for sharing this informative article.
Oracle Fusion HCM Online Training
Oracle Fusion SCM Online Training
Oracle Fusion Financials Online Training
Big Data and Hadoop Training In Hyderabad

Anonymous said...

Thanks for sharing This informative article. It would be helpful to all ,Keep sharing.
Oracle Fusion HCM Online Training

shaik shah said...

Goodblog, thanks for sharing this informative article. It would be helpful to all .your way of explaining in this good in this article.
Oracle Fusion HCM Online Training

KITS Technologies said...

Looking forward to reading more. Great blog article.Really thank you! Will read on...

oracle rac online training
oracle rac training

Post a Comment

Contact us for any Collaboration, Project Support & On Job Support Work

Name

Email *

Message *