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;
/
