ITEM INTERFACE- MTL_SYSTEM_ITEMS_INTERFACE

Item Interface

ITEM INTERFACE
*********************Item Interface Table :-
------------------------
MTL_SYSTEM_ITEMS_INTERFACE

Import Program
----------------
-> Go to Inventory Responsibility
-> Items
-> Import
-> Import Items

Two things you have to be careful
1. You have to select the item master organization or sub organization
for which you have to import the items.
2. If you are updating the existing item, instead of CREATE you have
to insert as UPDATE and select 2 in the Create or Update Items in the
parameter of Import Items.

Data Template :- 

SEGMENT1
SEGMENT2
SEGMENT3
SEGMENT4
DESCRIPTION
PRIMARY_UOM_CODE
ORGANIZATION_CODE
TEMPLATE_NAME
ATTRIBUTE_CATEGORY
ATTRIBUTE1
ATTRIBUTE2
ATTRIBUTE3
ATTRIBUTE4
ATTRIBUTE8
ATTRIBUTE9

Create staging table :- 

CREATE TABLE XXX_ITEM_MASTER_STG
(
SEGMENT1 VARCHAR2(40),
SEGMENT2 VARCHAR2(40),
SEGMENT3 VARCHAR2(40),
SEGMENT4 VARCHAR2(40),
DESCRIPTION VARCHAR2(240),
PRIMARY_UNIT_OF_MEASURE VARCHAR2(25),
ORGANIZATION_CODE VARCHAR2(3),
TEMPLATE_NAME VARCHAR2(30),
ATTRIBUTE_CATEGORY VARCHAR2(30),
ATTRIBUTE1 VARCHAR2(240),
ATTRIBUTE2 VARCHAR2(240),
ATTRIBUTE3 VARCHAR2(240),
ATTRIBUTE4 VARCHAR2(240),
ATTRIBUTE8 VARCHAR2(240),
ATTRIBUTE9 VARCHAR2(240),
VERIFY_FLAG CHAR(1),
ERROR_MESSAGE VARCHAR2(3000)
)


Run the following script to upload data from staging table to interface table
Then run the Import Items.



CREATE OR REPLACE PROCEDURE xxx_Item_Master_Org_Api
(errbuf out varchar2, rectcode out varchar2)
AS


L_VERIFY_FLAG VARCHAR2(3);
L_ERROR_MESSAGE VARCHAR2(2500);
L_COUNT NUMBER(2);
L_CATEGORY_SET_ID NUMBER(20);
L_CATEGORY_ID NUMBER(20);
L_ORG_ID NUMBER(4);
L_TEMPLATE_NAME VARCHAR2(250);
L_UOM VARCHAR2(20);
L_ITEM_TYPE VARCHAR2(20);
L_ORGANIZATION_ID NUMBER(10);
L_ORGANIZATION_CODE VARCHAR2(10);


CURSOR C1 IS
SELECT *
FROM
xxx_ITEM_MASTER_STG;

BEGIN

FOR C_REC IN C1 LOOP


L_VERIFY_FLAG:='Y';
L_ERROR_MESSAGE:= NULL;
L_COUNT := 0;


BEGIN
SELECT ORGANIZATION_ID,ORGANIZATION_CODE
INTO L_ORGANIZATION_ID,L_ORGANIZATION_CODE
FROM ORG_ORGANIZATION_DEFINITIONS
WHERE ORGANIZATION_CODE = TRIM(UPPER(C_REC.ORGANIZATION_CODE));
EXCEPTION
WHEN OTHERS THEN
L_VERIFY_FLAG := 'N';
L_ERROR_MESSAGE := 'INVALID ORGANIZATION' ;
END ;



BEGIN
SELECT COUNT(*)
INTO L_COUNT
FROM MTL_SYSTEM_ITEMS_B
WHERE SEGMENT1||'.'||SEGMENT2
||'.'||SEGMENT3||'.'||SEGMENT4 =
C_REC.SEGMENT1||'.'||C_REC.SEGMENT2
||'.'||C_REC.SEGMENT3||'.'||C_REC.SEGMENT4
AND ORGANIZATION_ID = L_ORGANIZATION_ID;
IF L_COUNT > 0 THEN
L_VERIFY_FLAG:= 'N';
L_ERROR_MESSAGE:=L_ERROR_MESSAGE|| 'ITEM ALREADY EXISTING' ;
END IF;
EXCEPTION
WHEN OTHERS THEN
NULL;
END;


IF ((TRIM(C_REC.SEGMENT1) IS NULL) OR
(TRIM(C_REC.SEGMENT2) IS NULL) OR
(TRIM(C_REC.SEGMENT3) IS NULL) OR
(TRIM(C_REC.SEGMENT4) IS NULL)) THEN
L_VERIFY_FLAG:= 'N';
L_ERROR_MESSAGE:= L_ERROR_MESSAGE|| 'ITEM SEGMENT SHOULD NOT BE NULL';
END IF;


IF TRIM(C_REC.DESCRIPTION) IS NULL THEN
L_VERIFY_FLAG:= 'N';
L_ERROR_MESSAGE:= L_ERROR_MESSAGE|| 'INVALID DESCRIPTION';
END IF;


BEGIN
SELECT TEMPLATE_NAME
INTO L_TEMPLATE_NAME
FROM MTL_ITEM_TEMPLATES
WHERE UPPER(TRIM(TEMPLATE_NAME)) = UPPER(TRIM(C_REC.TEMPLATE_NAME));
EXCEPTION
WHEN OTHERS THEN
L_VERIFY_FLAG:= 'N';
L_ERROR_MESSAGE := L_ERROR_MESSAGE||'INVALID TEMPLATE NAME';
END ;


BEGIN
SELECT UNIT_OF_MEASURE
INTO L_UOM
FROM MTL_UNITS_OF_MEASURE
WHERE UPPER(TRIM(UOM_CODE)) = UPPER(TRIM(C_REC.PRIMARY_UNIT_OF_MEASURE));
EXCEPTION
WHEN OTHERS THEN
L_VERIFY_FLAG:= 'N';
L_ERROR_MESSAGE := L_ERROR_MESSAGE||'INVALID UOM';
END;



IF L_VERIFY_FLAG <> 'N' THEN

BEGIN

INSERT INTO MTL_SYSTEM_ITEMS_INTERFACE
(
PROCESS_FLAG
, SEGMENT1
, SEGMENT2
, SEGMENT3
, SEGMENT4
, DESCRIPTION
, PRIMARY_UNIT_OF_MEASURE
, SET_PROCESS_ID
, TEMPLATE_NAME
, ORGANIZATION_ID
, ORGANIZATION_CODE
, TRANSACTION_TYPE
, ATTRIBUTE_CATEGORY
, ATTRIBUTE1
, ATTRIBUTE2
, ATTRIBUTE3
, ATTRIBUTE4
, ATTRIBUTE8
, ATTRIBUTE9
)
VALUES
(
1
, TRIM(C_REC.SEGMENT1)
, TRIM(C_REC.SEGMENT2)
, TRIM(C_REC.SEGMENT3)
, TRIM(C_REC.SEGMENT4)
, TRIM(C_REC.DESCRIPTION)
, L_UOM
, 1
, L_TEMPLATE_NAME
, L_ORGANIZATION_ID
, L_ORGANIZATION_CODE
, 'CREATE'
, C_REC.ATTRIBUTE_CATEGORY
, C_REC.ATTRIBUTE1
, C_REC.ATTRIBUTE2
, C_REC.ATTRIBUTE3
, C_REC.ATTRIBUTE4
, C_REC.ATTRIBUTE8
, C_REC.ATTRIBUTE9
);


UPDATE xxx_ITEM_MASTER_STG
SET VERIFY_FLAG = 'Y'
WHERE SEGMENT1||'.'||SEGMENT2
||'.'||SEGMENT3||'.'||SEGMENT4 =
C_REC.SEGMENT1||'.'||C_REC.SEGMENT2
||'.'||C_REC.SEGMENT3||'.'||C_REC.SEGMENT4;

EXCEPTION
WHEN OTHERS THEN
L_ERROR_MESSAGE:= SQLERRM;
UPDATE xxx_ITEM_MASTER_STG
SET VERIFY_FLAG = 'N',
ERROR_MESSAGE = L_ERROR_MESSAGE
WHERE SEGMENT1||'.'||SEGMENT2
||'.'||SEGMENT3||'.'||SEGMENT4 =
C_REC.SEGMENT1||'.'||C_REC.SEGMENT2||'.'||
C_REC.SEGMENT3||'.'||C_REC.SEGMENT4;
END;

COMMIT;

ELSE

UPDATE xxx_ITEM_MASTER_STG
SET VERIFY_FLAG = 'N'
,ERROR_MESSAGE = L_ERROR_MESSAGE
WHERE SEGMENT1||'.'||SEGMENT2||'.'||
SEGMENT3||'.'||SEGMENT4 =
C_REC.SEGMENT1||'.'||C_REC.SEGMENT2
||'.'||C_REC.SEGMENT3||'.'||C_REC.SEGMENT4;

COMMIT;

END IF;



END LOOP;

COMMIT;

END xxx_Item_Master_Org_Api;
/



UPDATE ITEMS
*****************CREATE OR REPLACE PROCEDURE xxx_item_update_api
AS

l_inventory_item_id number(10);
l_verify_flag varchar2(1) := 'N';
l_error_message varchar2(2500);
l_organization_id number(5);
l_organization_code varchar2(5);

CURSOR C_ITEM
IS
select segment1,
segment2,
segment3,
segment4,
min_minmax_qty
from xxx_ITEM_MASTER_STG ;

BEGIN

FOR c1 in c_item
loop

l_verify_flag := 'Y';
l_error_message := null;

BEGIN
select organization_id,organization_code
into l_organization_id,l_organization_code
from org_organization_definitions
where operating_unit = 284
and organization_code = 'SPT';
EXCEPTION
WHEN OTHERS THEN
l_verify_flag := 'N';
l_error_message := 'Organization not Valid';
END;


BEGIN
Select inventory_item_id
into l_inventory_item_id
from mtl_system_items_b
where organization_id = l_organization_id
and trim(segment1) = trim(c1.segment1)
and trim(segment2) = trim(c1.segment2)
and trim(segment3) = trim(c1.segment3)
and trim(segment4) = trim(c1.segment4);
Exception
when others then
l_verify_flag := 'N';
l_error_message := 'Item Not Valid';
END;

IF l_verify_flag <> 'N' THEN

BEGIN
INSERT INTO MTL_SYSTEM_ITEMS_INTERFACE
( INVENTORY_ITEM_ID
, SEGMENT1
, SEGMENT2
, SEGMENT3
, SEGMENT4
, PROCESS_FLAG
, SET_PROCESS_ID
, ORGANIZATION_ID
, ORGANIZATION_CODE
, TRANSACTION_TYPE
, SOURCE_TYPE
, MIN_MINMAX_QUANTITY
)
VALUES
( l_inventory_item_id
, trim(c1.SEGMENT1)
, trim(c1.SEGMENT2)
, trim(c1.SEGMENT3)
, trim(c1.SEGMENT4)
, 1
, 1
, l_organization_id
, l_organization_code
, 'UPDATE'
, 2
, trim(c1.min_minmax_qty)
);

UPDATE xxx_ITEM_MASTER_STG
SET VERIFY_FLAG ='Y'
WHERE trim(segment1) = trim(c1.segment1)
and trim(segment2) = trim(c1.segment2)
and trim(segment3) = trim(c1.segment3)
and trim(segment4) = trim(c1.segment4);
EXCEPTION
WHEN OTHERS THEN
l_error_message := SQLERRM;
UPDATE xxx_ITEM_MASTER_STG
SET VERIFY_FLAG ='N',
error_message = l_error_message
WHERE trim(segment1) = trim(c1.segment1)
and trim(segment2) = trim(c1.segment2)
and trim(segment3) = trim(c1.segment3)
and trim(segment4) = trim(c1.segment4);
END;

ELSE

UPDATE xxx_ITEM_MASTER_STG
SET VERIFY_FLAG ='N',
error_message = l_error_message
WHERE trim(segment1) = trim(c1.segment1)
and trim(segment2) = trim(c1.segment2)
and trim(segment3) = trim(c1.segment3)
and trim(segment4) = trim(c1.segment4) ;

END IF;
COMMIT;
END LOOP ;


END xxx_item_update_api;