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

Interface Steps

Interfaces:

   -------------

Interface is one of the Program which will be used to transfer the data from Oracle

database tables in to flat file   (or)

Flat files      into  Database tables

We have two types of Interfaces.

1)Inbound Interface

2)outbound Interface

Outbound Interface will be used to extract the data from oracle Database tables into

the flat files.

Inbound Interface will be used to upload the data from legacy system (Flat files) into

Oracle Applications base tables.

While developing the outbound Interface we will use UTL_File to Extract the data.

While Developing the Inbound interface  we will use SQL * loader to import the data

into base taqbles.

Converstion Vs Interfaces

Interfaces:

It is schedule concurrent Process which will be executed multiple times

We will not be knowing flat file Volume

We need to handle all the expected exceptions

Error Reports and sending mail alerts, If any error occurs

Only in Enhancement or customization projects

Conversion:

One time data transfer

We will be know exact flat file Volume

No need to know the exceptions

Not required to upload all records

Only in Implementation, Migration or up-gradation projects

UTL_FILE Package :

==================

this is One of the PL/SQL Package which will be used to transfer the data

from table to files

from files to tables

But when we are working for file to table we will use SQl *Loader

to transfer from table to file we have no alternative we have to use UTL_FILE.

We will use following three functions to generate the file.

1)Utl_File.fopen     = To open (or) Create the file

2)Utl_File.Put_line  = To Transfer the data into the File.

3)Utl_File.fclose    = To close the File after Data transfer.

outbound Interface Process:

=============================

1)Develop the PL/SQL Program (Either Procedure or Package)

2)Write the Cursor to retrieve the data from database tables.

3)Create file or Open the File by using UTL_File.fopen().

4)Open the Cursor

5)If any validations are there write the validations

6)Transfer the Data into File by using UTL_File.Put_Line().

7)Close the Cursor.

8)Close the File by using UTL_File.fclose();

9)Register the Procedure or Package as Concurrent Program and submit from SRS Window.

For Ex : We required the flat file with following Format.

Supplierno,Supliername,Cdate,Sitename

Develop the Program to Transfer Supplier Purchase orders datainto file

Supplier No from

Supplier No To 

PoNumber,POtype,Amount,Cdate,No,Name,Site

Validations:

1)If Parameters are null select all the supplier details

2)If 'from' is given  'To' is not given take till Last Supplier

3)If 'To' is given and 'from' is not given take from first.

4)If Purchase order amount is more then 3500 then only transfer into file.

Matrix Reloaded

Main aim of this blog to discuss and pull the information regarding Oracle Application and general stuff.