INVENTORY ITEM INTERFACE

Item Interface Table :-

------------------------

MTL_SYSTEM_ITEMS_INTERFACE


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

Step 1  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)

)

Step 2  Insert The Data into stage table through sql loader or toad


Step 3 Run the following script to upload data from staging table to interface table


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   --Validate Organization

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  -- Check Item Already Exists

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   --Item segment (1 to 4) Not Null

(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  --Description Should be not null

L_VERIFY_FLAG:= 'N';

L_ERROR_MESSAGE:= L_ERROR_MESSAGE|| 'INVALID DESCRIPTION';

END IF;

BEGIN   --Validate Item Template Name

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    -- Validate Unit of measure

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;

/


Step 4  Then run the Import Items.

Import Program

----------------

-> Go to Inventory Responsibility

-> Items

-> Import

-> Import Items
*********************************************************************************************

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;

/