Oracle Applications
Simple and easy steps to oracle Inventory management
A beginners guide for oracle Inventory managment
- Oracle Inventory managment
- Oracle apps Item convertion using interface
- Oracle apps Item creation thru interface
Introduction
You
can import items from any source into Oracle Inventory using the Item
Interface. When you import items via the Item Interface, you create new items
in your Item Master organization or assign existing items to additional
organizations. You can specify values for all the item attributes. Validation
of imported items is done using the same rules as the item definition forms, so
you are insured of valid items. The Item Interface reads data from two tables
for importing items and item details. You use the MTL_SYSTEMS_ITEM_INTERFACE
table for new item numbers and all item attributes. This is the main item
interface table, and can be the only table you choose to use. MTL_ITEM_CATEGORIES_INTERFACE
table is used to attach existing categories to the newly created Item. Table
MTL_INTERFACE_ERRORS, is used for error tracking of all items that the
Item Interface fails. Before you use the Item
Interface,
you must insert the new Item’s information using SQL Loader or any other package. Oracle recommends you truncate
all IOI interface tables before loading data
inmtl_system_items_interface table:
MTL_SYSTEM_ITEMS_INTERFACE
MTL_INTERFACE_ERRORS
MTL_ITEM_REVISIONS_INTERFACE
MTL_ITEM_CATEGORIES_INTERFACE
Creation of flat files -
You need to create flat
file for item which you want to inter via interface tables. Flat files which
contains the mandatory attributes of MTL_SYSTEM_ITEMS_INTERFACE
And MTL_ITEM_CATEGORIES_INTERFACE.
Minimum
required columns of MTL_SYSTEM_ITEMS_INTERFACE when creating new items are:
·
PROCESS_FLAG
= 1 {Changes
to ‘7’ if the items importing is ‘Successful’}
·
TRANSACTION_TYPE
= 'CREATE' {‘UPDATE’
– for updating existing item attributes}
·
SET_PROCESS_ID
= 123 {any
number can be chosen, here we will use 123}
·
ORGANIZATION_ID
= Master Org
·
id.DESCRIPTION
= 'Description of the item 'ITEM_NUMBER and/or SEGMENT (n)
For
attaching Categories to new Items being created, upload the required data in
table MTL_ITEM_CATEGORIES_INTERFACE. Minimum required columns when creating new
items are:
·
PROCESS_FLAG
= 1 {
Changes to ‘7’ if the items importing is ‘Successful’}
·
TRANSACTION_TYPE
= 'CREATE' {'UPDATE'
or 'DELETE' is not supported}
·
ORGANIZATION_ID
·
ITEM_NUMBER
·
CATEGORY_SET_NAME
{ At
NRB, can be either ‘Inventory’ or ‘Accounting’ }
·
CATEGORY_NAMESET_PROCESS_ID=123
Sample .CTL files used to import the
data:
Sql loader for adding data into staging table which
is for item attributes
LOAD DATA
INFILE *
APPEND INTO TABLE XX_TEMP_ITEM02
FIELDS TERMINATED BY '|' TRAILING NULLCOLS
(PROCESS_FLAG
INTEGER EXTERNAL,
TRANSACTION_TYPE CHAR,
SET_PROCESS_ID INTEGER EXTERNAL,
ORGANIZATION_ID INTEGER EXTERNAL,
ITEM_NUMBER CHAR,
DESCRIPTION CHAR,
PRIMARY_UOM_CODE CHAR,
ATTRIBUTE_CATEGORY CHAR,
ATTRIBUTE1 CHAR,
ATTRIBUTE2 CHAR,
ATTRIBUTE3 CHAR,
ATTRIBUTE15 CHAR,
TEMPLATE_NAME CHAR)
This control
file is for loading item cataegory
LOAD DATA
INFILE *
INFILE *
APPEND INTO XX_TEMP_CAT02
FIELDS
TERMINATED BY '|'
(PROCESS_FLAG INTEGER EXTERNAL ,
TRANSACTION_TYPE
CHAR ,
SET_PROCESS_ID INTEGER EXTERNAL ,
ORGANIZATION_ID INTEGER EXTERNAL ,
ITEM_NUMBER CHAR ,
CATEGORY_SET_NAME CHAR ,
CATEGORY_NAME CHAR )
Register the concurrent program in
application
·
After
creating the .ctl file you need to register
the control file as executable in oracle application under the inventory
manager responsibility using
Admin responsibility
·
After
register the .ctl file you can create a concurrent program using this
executable
- By using parameter button in the same window add one parameter for flat file path
• After successful completion of concurrent program your data will be uploaded to the desired staging table.
• Then use the plsql block to insert data into the interface table.
DECLARE
XTEMP XX_TEMP_ITEM02%ROWTYPE;
XTEMP01 XX_TEMP_CAT02%ROWTYPE;
I_FLAG VARCHAR2(3):= 'Y';--AFTER CHECK THIS VALUE DEFAULT SET OR NOT
VAL_SEGMENT1 VARCHAR2 (50);
VAL_ORGANIZATION_ID VARCHAR2(10);
VAL_PROCESS_FLAG VARCHAR2(10);
VAL_TRANSACTION_TYPE VARCHAR2(10);
CURSOR CUR_STAGE_TAB_ITEM
IS
SELECT * FROM XX_TEMP_ITEM02 ;
CURSOR CUR_STAGE_TAB_CAT
IS
SELECT * FROM XX_TEMP_CAT02 ;
BEGIN
FOR XTEMP IN CUR_STAGE_TAB_ITEM
LOOP
INSERT
INTO MTL_SYSTEM_ITEMS_INTERFACE
(
PROCESS_FLAG,
TRANSACTION_TYPE,
SET_PROCESS_ID,
ORGANIZATION_ID,
ITEM_NUMBER,
DESCRIPTION,
PRIMARY_UOM_CODE,
TEMPLATE_NAME,
ATTRIBUTE_CATEGORY,
ATTRIBUTE1,
ATTRIBUTE2,
ATTRIBUTE3,
ATTRIBUTE15
)
VALUES
(
XTEMP.PROCESS_FLAG,
XTEMP.TRANSACTION_TYPE,
XTEMP.SET_PROCESS_ID,
XTEMP.ORGANIZATION_ID,
XTEMP.ITEM_NUMBER,
XTEMP.DESCRIPTION,
XTEMP.PRIMARY_UOM_CODE,
XTEMP.TEMPLATE_NAME,
XTEMP.ATTRIBUTE_CATEGORY,
XTEMP.ATTRIBUTE1,
XTEMP.ATTRIBUTE2,
XTEMP.ATTRIBUTE3,
XTEMP.ATTRIBUTE15
);
END LOOP;
FOR XTEMP01 IN CUR_STAGE_TAB_CAT
LOOP
INSERT
INTO MTL_ITEM_CATEGORIES_INTERFACE
(
PROCESS_FLAG,
TRANSACTION_TYPE,
SET_PROCESS_ID,
ORGANIZATION_ID,
ITEM_NUMBER,
CATEGORY_SET_NAME,
CATEGORY_NAME
)
VALUES
(
XTEMP01.PROCESS_FLAG,
XTEMP01.TRANSACTION_TYPE,
XTEMP01.SET_PROCESS_ID,
XTEMP01.ORGANIZATION_ID,
XTEMP01.ITEM_NUMBER,
XTEMP01.CATEGORY_SET_NAME,
XTEMP01.CATEGORY_NAME
);
commit;
END LOOP;
commit;
END;
• At the successful compilation of the PLSQL block data will move from staging tables to interface table.
• After this step you can run the ITEM IMPORT program from application.
Steps to import item from interface table
• Log in to inventory management responsibility.
• Then use this path
• ITEM --> IMPORT --> IMPORT ITEMS
- Click on ok then submit.
- now you can see your item in Master item.
No comments:
Post a Comment