Item Import and Sub Inventory Assignment

Oracle Item Import and Sub Inventory Assignment

Create a Temp table ( Attribute1 represents Supplier Id )

Column Name Null? Data Type
SEGMENT1 Y VARCHAR2 (40 Byte)
SEGMENT2 Y VARCHAR2 (40 Byte)
DESCRIPTION Y VARCHAR2 (240 Byte)
ORGANIZATION_ID N NUMBER
PRIMARY_UOM_CODE Y VARCHAR2 (3 Byte)
ATTRIBUTE1 Y VARCHAR2 (240 Byte)

/* Insert Test Data into Table */

insert into aeo_item_import values ('RARDE00093','551','RAR Switch - Ignition',423,'PCE',214658) ;


commit ;

/* Insert into interface Table
Keep a eye on Item Template_id its predefined template id */

INSERT INTO apps.mtl_system_items_interface
( process_flag,transaction_type,set_process_id,segment1, segment2,
attribute1,organization_id, description,primary_uom_code ,template_id,created_by,
creation_date, last_update_date, last_updated_by
)
(select '1' ,'CREATE','0', segment1,segment2, attribute1,organization_id,
description,primary_uom_code,1, -1, SYSDATE, SYSDATE, -1
from aeo_item_import );

COMMIT;

Go to Oracle Inventory Items > Import > Import Item

Check Item is Created and assigned to 423 (organization_id which is Master Org )

Now we want to assign item to Child Org .

repeat above all steps just change organization_id from 423 to child organizations_id

--------------------------------------------------------------
/* Assigning Sub inventories to Items Created just now */

DECLARE
l_item_id NUMBER;
l_item_count NUMBER;
l_vendor_id NUMBER := 0;
l_err_flag CHAR (1) := 'N';
l_organization_id NUMBER := 0;
m_exists varchar2 (1);

/* Selecting Sub Inventories from custom table for populated items */
-- Select the items that have been enabled through the mass enable script ----
CURSOR c
IS
SELECT SEGMENT1,
SEGMENT2,
INVENTORY_ITEM_ID,
ORGANIZATION_ID
FROM apps.MTL_SYSTEM_ITEMS
WHERE segment1 IN (SELECT segment1 FROM aeo_item_import)
AND--AND trunc(creation_date) = trunc(sysdate)
ORGANIZATION_ID = 423;
-- 423 is organization_id for which you want to Insert sub inventories

CURSOR sub_invs (p_organization_id IN NUMBER)
IS
SELECT DISTINCT secondary_inventory_name
FROM apps.mtl_secondary_inventories
WHERE organization_id = 425; -- Master Org
/* ---- Main Program ---- */
BEGIN
DBMS_OUTPUT.put_line('======================================================================');
DBMS_OUTPUT.put_line('Started populating interface tables at : '
|| TO_CHAR (SYSDATE, 'DD-MON-RRRR HH:MI:SS'));
l_item_count := 0;

FOR r IN c
LOOP
l_item_id := NULL;
l_vendor_id := NULL;
l_err_flag := 'N';
l_organization_id := NULL;

IF (l_err_flag <> 'Y')
THEN
FOR r1 IN sub_invs (r.organization_id)
LOOP
BEGIN
BEGIN
-- Check if the item- organization - subinventory combination is already existing --
SELECT 'm'
INTO m_exists
FROM apps.mtl_item_sub_inventories
WHERE inventory_item_id = r.inventory_item_id
AND organization_id = r.organization_id
AND secondary_inventory =
r1.secondary_inventory_name;
--dbms_output.put_line('Inventory_item_ID :'||r.inventory_item_id);
--dbms_output.put_line('Organization_ID :'||r.organization_id);
--dbms_output.put_line('Sub Inventory :'||r1.secondary_inventory_name);
----- If the combination doesnot exist then populate to the subinventory table ----------------
EXCEPTION
WHEN NO_DATA_FOUND
THEN
-- DBMS_OUTPUT.PUT_LINE(r.inventory_item_id || '-' || r.organization_id||'-'||r1.secondary_inventory_name);
INSERT INTO apps.mtl_item_sub_inventories (
inventory_item_id,
organization_id,
secondary_inventory,
last_update_date,
last_updated_by,
creation_date,
created_by,
last_update_login,
inventory_planning_code
)
VALUES (r.inventory_item_id,
r.ORGANIZATION_ID,
r1.secondary_inventory_name,
SYSDATE,
-1,
SYSDATE,
-1,
-1,
6);

-- COMMIT; -- Committing the Insert --
l_item_count := l_item_count + 1;

/* Update the Flag */
UPDATE apps.mtl_system_items
SET attribute15 = 'Y'
WHERE inventory_item_id = r.inventory_item_id
AND organization_id = r.organization_id;
-- COMMIT; -- Comitting the Update --
END; -- End of Check procedure ---
EXCEPTION
WHEN OTHERS
THEN
NULL;
END;
END LOOP;
END IF;
-- END IF;
END LOOP;

COMMIT;
DBMS_OUTPUT.put_line (
'Total Records populated into Item Sub-Inventories : ' || l_item_count
);
DBMS_OUTPUT.put_line('Completed populating Item Sub-Inventories at : '
|| TO_CHAR (SYSDATE, 'DD-MON-RRRR HH:MI:SS'));
DBMS_OUTPUT.put_line('======================================================================');
END;