SIMPLE ITEM UPLOAD EXAMPLE THROUGH ITEM INTERFACE

SIMPLE ITEM UPLOAD EXAMPLE THROUGH ITEM INTERFACE

STEP1.

GO TO APPS AND CREATE STAGING TABLE

create table SG_ITEM_STAGE
(segment1 varchar2(25),
DESCRIPTION VARCHAR2(25),
TEMPLATE_NAME VARCHAR2(25),
MATERIAL_COST NUMBER,
PROCESS_FLAG NUMBER,
ENABLED_FLAG VARCHAR2(1),
TRANSACTION_TYPE VARCHAR2(25),
ORGANIZATION_CODE VARCHAR2(25))

STEP 2.

Create a SG_ITEM_DATA.dat File

SGTest Item51, SGTest Item11,Finished Good,100,
SGTest Item52, SGTest Item12, Finished Good, 500,
SGTest Item53, SGTest item13, Finished Good, 600,
SGtest Item54, SGTest Item14, Finished Good,300,

STEP 3.
Create a shri.ctl file (control file which contains the logic of uploading data from flat file to the stagging table)

LOAD DATA
INFILE '/apps/visappl/cust/11.5.0/bin/SG_ITEM_DATA.dat'
INTO TABLE SG_ITEM_STAGE
APPEND
FIELDS TERMINATED BY "," OPTIONALLY ENCLOSED BY '"'
(SEGMENT1,
DESCRIPTION,
TEMPLATE_NAME,
MATERIAL_COST,
PROCESS_FLAG CONSTANT 1,
ENABLED_FLAG CONSTANT 'Y',
TRANSACTION_TYPE CONSTANT 'Create',
ORGANIZATION_CODE CONSTANT "V1")

STEP 4.

UP LOAD CTL AND DAT FILE
FTP
TELNET

STEP 5.

CHECK DATA IN STAGING TABLE
select count(*) from
SG_STAGE_TABLE;

STEP 6.

CREATE PRE_INTERFACE TABLE AS INTERFACE TABLE

create table SG_ITEM_PRE_INTERFACE AS
select * from mtl_system_items_interface ;

STEP 7.


RUN THIS PROCEDURE TO UPLOAD DATA FROM STAGING TABLE TO PREINTERFACE
TABLE

STAGING TABLE---------------------------->PRE-INTERFACE TABLE

CREATE OR REPLACE Procedure SG_ITEM_PRE_INTERFACE Is
m_Segment1 varchar2(40);
m_Description varchar2(40);
M_TEMPLATE_NAME VARCHAR2(40);
M_MATERIAL_COST NUMBER;
M_PROCESS_FLAG NUMBER;
m_Transaction_Type varchar2(10);
m_Organization_CODE VARCHAR2(10);
M_ENABLED_FLAG VARCHAR2(1);
m_set_process_id number;
Cursor C1 is Select * from SG_STAGE_TABLE;
Begin
For rec in C1
loop
m_Segment1 := REC.SEGMENT1;
m_Description := REC.DESCRIPTION;
M_TEMPLATE_NAME := REC.TEMPLATE_NAME;
IF M_MATERIAL_COST>500 THEN
M_MaTERIAL_COST:=500;
END IF;
IF M_PROCESS_FLAG IN(2,3,4,5) THEN
M_PROCESS_FLAG:=1;
ELSE
M_PROCESS_FLAG:=REC.PROCESS_FLAG;
END IF;
M_MATERIAL_COST := abs(REC.MATERIAL_COST);
M_PROCESS_FLAG := REC.PROCESS_FLAG;
m_Transaction_Type := REC.TRANSACTION_TYPE;
m_Organization_CODE:= REC.ORGANIZATION_CODE;
M_ENABLED_FLAG := REC.ENABLED_FLAG;
m_set_process_id := 25101974;
Insert into
SG1_ITEM_PRE_INTERFACE
(
SEGMENT1,
DESCRIPTION,
TEMPLATE_NAME,
MATERIAL_COST,
PROCESS_FLAG,
TRANSACTION_TYPE,
ORGANIZATION_CODE,
ENABLED_FLAG,
SET_PROCESS_ID
)
values
(
m_Segment1,
m_Description,
M_TEMPLATE_NAME,
M_MATERIAL_COST,
M_PROCESS_FLAG,
m_Transaction_Type,
m_Organization_CODE,
M_ENABLED_FLAG,
M_SET_PROCESS_ID
);
End Loop;
End SG_ITEM_PRE_INTERFACE;
/

Compile & execute this procedure. After this check data is uploaded in the pre-interface
table.
EXEC SG_ITEM_PRE_INTERFACE;
SELECT COUNT(*) FROM SG1_ITEM_PRE_INTERFACE;

STEP 7.

TRANSFER DATA FROM
PRE-INTERFACE TABLE-------------------------->INTERFACE TABLE

CREATE OR REPLACE Procedure SG2_ITEM_INTERFACE Is
m_Segment1 varchar2(40);
m_Description varchar2(40);
M_TEMPLATE_NAME VARCHAR2(40);
M_MATERIAL_COST NUMBER;
M_PROCESS_FLAG NUMBER;
m_Transaction_Type varchar2(10);
m_Organization_CODE VARCHAR2(10);
M_ENABLED_FLAG VARCHAR2(1);
m_set_process_id number;
Cursor C1 is Select * from SG1_ITEM_PRE_INTERFACE;
Begin
For rec in C1
loop
m_Segment1 := REC.SEGMENT1;
m_Description := REC.DESCRIPTION;
M_TEMPLATE_NAME := REC.TEMPLATE_NAME;
M_MATERIAL_COST := REC.MATERIAL_COST;
M_PROCESS_FLAG := REC.PROCESS_FLAG;
m_Transaction_Type := REC.TRANSACTION_TYPE;
m_Organization_CODE:= REC.ORGANIZATION_CODE;
M_ENABLED_FLAG := REC.ENABLED_FLAG;
m_set_process_id := 25101974;
Insert into MTL_SYSTEM_ITEMS_INTERFACE
(
SEGMENT1,
DESCRIPTION,
TEMPLATE_NAME,
MATERIAL_COST,
PROCESS_FLAG,
TRANSACTION_TYPE,
ORGANIZATION_CODE,
ENABLED_FLAG,
SET_PROCESS_ID
)
values
(
m_Segment1,
m_Description,
M_TEMPLATE_NAME,
M_MATERIAL_COST,
M_PROCESS_FLAG,
m_Transaction_Type,
m_Organization_CODE,
M_ENABLED_FLAG,
M_SET_PROCESS_ID
);
End Loop;
End SG2_ITEM_INTERFACE;
EXEC SG_ITEM_PRE_INTERFACE;

SELECT COUNT(*) FROM SG1_ITEM_PRE_INTERFACE;

STEP 8.
Check the data
SELECT COUNT(*) FROM MTL_SYSTEM_ITEMS_INTERFACE;

STEP 9.
Log on to apps(mfg/welcome).
select ==>Inventory.
In Navigator ==> Items==>Import Items ==> Select master organisation


Once the data has been loaded into interface table then run standard concurrent program Import Items from
INV SUPER USER Responsibility to import data into base tables.



1] All Organizations:
Yes: Run the interface for all organization codes in the item interface table.
No: Run the interface only for the organization you are currently in. Item interface rows for organizations other than your current organization are ignored.

2] Validate Items:
Yes: Validate all items and their data residing in the interface table that have not yet been validated. If items are not validated, they will not be processed into Oracle Inventory.
No:  Do not validate items in the interface table.

3] Process Items:
Yes: All qualifying items in the interface table are inserted into Oracle Inventory.
No: Do not insert items into Oracle Inventory.

4] Delete Processed Rows:
Yes: Delete successfully processed items from the item interface tables.
No: Leave all rows in the item interface tables.

5] Process Set:
Enter a number for the set id for the set of rows you want to process. The program picks up the rows marked with that id in the SET_PROCESS_ID column. If you leave this field blank, all rows are picked up for processing regardless of the SET_PROCESS_ID column value.



Working with failed interface rows:
If a row fails validation, the Item Interface sets the PROCESS_FLAG to 3 (Assign/validation failed) and inserts a row in the interface errors table,
 MTL_INTERFACE_ERRORS. To identify the error message for the failed row, the program automatically populates the TRANSACTION_ID column in this table with the TRANSACTION_ID value from the corresponding item interface table.

The UNIQUE_ID column in MTL_INTERFACE_ERRORS is populated from the sequence MTL_SYSTEM_ITEMS_INTERFACE_S. Thus, for a given row, the sequence of errors can be determined by examining UNIQUE_ID for a given TRANSACTION_ID.

You should resolve errors in the sequence that they were found by the interface, that is, in increasing order of UNIQUE_ID for any TRANSACTION_ID.
Resubmitting an Errored Row:
During Item Interface processing, rows can error out either due to validation (indicated by PROCESS_FLAG = 3 in MTL_SYSTEM_ITEMS_INTERFACE and the corresponding error in MTL_INTERFACE_ERRORS) or due to an Oracle Error.

When an Oracle Error is encountered, the processing is stopped and everything is rolled back to the previous save point. This could be at PROCESS_FLAG = 1, 2, 3, or 4.

When you encounter rows errored out due to validations, you must first fix the row corresponding to the error with the appropriate value. Then reset PROCESS_FLAG = 1, INVENTORY_ITEM_ID = null, and TRANSACTION_ID = null. Then resubmit the row for reprocessing.
To check the errors in interface table.

Select
   ORGANIZATION_ID,
   UNIQUE_ID,
   REQUEST_ID ,
   TABLE_NAME ,
   COLUMN_NAME,
   ERROR_MESSAGE
   CREATION_DATE,
   MESSAGE_TYPE
from
    MTL_INTERFACE_ERRORS where request_id= :p_req_id
order by CREATION_DATE;


Once the items has been successfully imported then process_flag value will be changed 7 in interface table.

You can also able to see the error in interface table MTL_INTERFACE_ERRORS

PROCESS_FLAG Values after running the Import Items concurrent program

1= Pending,
2= Assign Complete,
3= Assign/Validation Failed,
4= Validation succeeded; Import failed,
5 = Import in Process,
7 = Import succeeded