Example : ITEM UPLOAD INTERFACE

Example : ITEM UPLOAD INTERFACE

  shrikantgarud on Sun Jan 27, 2008 3:28 pm

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

  shrikantgarud on Sun Jan 27, 2008 3:28 pm

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