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:
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
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
Thanks for sharing This informative article. It would be helpful to all ,Keep sharing.
Oracle Fusion HCM Online Training
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
Looking forward to reading more. Great blog article.Really thank you! Will read on...
oracle rac online training
oracle rac training
Post a Comment