Item Conversion
You can import items from any source into Oracle Inventory using the Item Interface
Following is the approach for Item conversion.
Step 1:
We will get the flat file from the customer.
Flat file types: .csv, .txt, .xls, .xml
Step 2:
Create a staging table based on the flat file structure.
Create the error table.
Step 3:
Create the control file to transfer the data from flat file to staging table.
SQL * Loader:
Following are the file we will come across while working with SQL* Loader.
Control file .ctl à to transfer the data from flat file to staging table.
Flat file .csv à raw data file which we will receive from legacy system
Log file .log à It will display the execution of the program.
Bad file .bad à These records could have been rejected by SQL*Loader
Discard file .dis à It will contain records that didn't meet the criteria.
Step 4 :
Run the control file.
Now the data has been transferred to staging table.
Step 5:
Create a PL/SQL validation program to validate the records and to transfer the records to the interface table.
The item interface table MTL_SYSTEM_ITEMS_INTERFACE contains every column in the Oracle Inventory item master table, MTL_SYSTEM_ITEMS_B. The columns in the item interface correspond directly to those in the item master table.
MTL_ITEM_CATEGORIES_INTERFACE
MTL_ITEM_REVISIONS_INTERFACE
Required Columns for MTL_SYSTEM_ITEMS_INTERFACE
ITEM_NUMBER
DESCRIPTION
ORGANIZATION_CODE
PROCESS_FLAG
TRANSACTION_TYPE
SET_PROCESS_ID
Validations for Item Import:
1) transaction_type should be ‘create’
2) ORGANIZATION CODE should be exist.
3) Item number should not be exist.
4) Description should be not null.
5) Item template should be exist and valid.
6) Item and organization combination should not exist.
7) ccid of cogs account should be exist.
8) PROCESS_FLAG should be 1.
9) UOM should be exist.
Step 6:
If the record is validated then status_flag should be updated with ‘V’.
If the record is error out then status_flag should be updated with ‘E’ and the record should be inserted into custom error table.
Step 7:
PL/SQL program will pick the validate records and then inserted into the interface table.
Step 8:
Run the standard concurrent program to import items to the base table.
Base tables:
MTL_SYSTEM_ITEMS_B
Error table:
MTL_INTERFACE_ERRORS
