Conversion

General Conversion Steps:

1. We will get the flat file from customer, in .XLS .CSV .TXT .XML
2. Create staging table, custom error table based on the flat file structure
3. Create control file to transfer data from flat file to staging table

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.

4. Run the control file and data would be transferred to staging table
5. Create PL/SQL validation program to validate the records and transfer the records into interface table
6. If record is validated, then the status_flag should be updated to ‘V’

If record is error, then the status_flag should be updated to ‘E’ and the record should be inserted into custom error table

7. PL/SQL program will pick the validated records and insert into interface table




Run the seeded concurrent program to import data from interface table into base table.

Item Conversion steps

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 is 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