Oracle Interface – Oracle EBS R12 End to End Interface Process Training Manual and Code Pack

What are Interfaces?

Ø  Interfaces are used in Oracle Applications to integrate external systems and Data Conversion.

Ø  The interfaces are mainly used to either transfer data from Oracle Applications to a flat file or data from legacy system to Oracle Applications.

Ø  Used extensively at the time of Data Conversion from legacy/ old systems to a fresh implementation of Oracle Applications.

Ø  Used also at regular intervals when data transfer is from other live systems if the systems are not defined in Oracle Applications implementation.

Ø  Oracle provides flexible and flexible tools in the form of Interface programs to import the master and transactional data like Customers, Invoices, and Sales Orders etc from external systems into Oracle Applications.

Types of Interfaces

There are two major types of Interfaces:

Ø  Inbound Interface : These interfaces are used to transfer data from external systems to Oracle Applications.

Ø  Outbound Interface : These interfaces are used to transfer data from Oracle Applications to external systems.

Ø  Two other distinctions of Interfaces:

Ø  Open Interface: If the interface logic is provided by Oracle Applications, it is called an Open Interface.

Ø  Custom Interface: If the interface logic needs to be developed by the implementation team, it is called a Custom Interface.

Interface Components

Open Interface Logic

Ø  First the data from the source application is loaded into a database table (called Interface table).

Ø  Then the provided validation program logic validates the records whether they are correct or not .

Ø  If the validation fails, the errors are transferred into another table (called Error Table).

Ø  If the validation succeeds, the correct records are transferred through a process into the destination application table.

Components of an Interface

a] Source Application:

You obtain data from a source application to pass on to a destination application for further processing and/or storage.

b] Source Data Issues:

Type of file, Size, Frequency of upload, Record Length (Variable or fixed), Delimiter, Datatype for each field, Any unwanted data, Naming convention and uniqueness of file, Location of the file, Access on the file.

c] Destination Application:

You send data to a destination application so that the application can perform further processing and/or storage.

d] Interface Table:

For inbound interfaces, the interface table is the intermediary table where the data from your source application temporarily resides until it is validated and processed into the destination application.

e] Identifier columns:

Uniquely identify rows in the interface table provide foreign key reference to both the source and destination applications.

f] Control Columns:

• Control columns track the status of each row in the interface table, as it is inserted, validated, rejected, processed, and ultimately deleted.
• WHO columns are also control columns.

g] Data Columns:

·         Stores the data that is being converted.

·         Required columns store the minimum information needed by the destination application to successfully process the interface row.

h] Derived Columns:

Derived columns are created by the destination application from information in the required columns.

i] Optional Columns:

Optional columns are not necessarily required by the destination application, but can be used by the destination application for additional value-added functionality beyond the basics.

j] Error Table:

·         For inbound interfaces, the errors table stores all errors found by the validation and processing functions.

·         In some cases, the errors table is a child of the interface table. This allows each row in the interface table to have many errors, so that you can easily manage multiple errors at once.

·         In other cases, the errors are stored in a column within the interface table, which requires you to fix each error independently.

Developing an Interface

1] Identification:

Find out if there exists an Open Interface to carry out the functionality.

2] Creation of Pre-Interface table ( staging Table):

A table in the format of the data file which can be pruned to load as clean a data into the Interface table.

3] Load data into Pre-Interface table:

SQL*LOADER can be used to load the flat file into the pre-interface table.

4] Validate data in the Pre-Interface table:

Basic validation of the data loaded into the Pre-Interface table can be carried out like:

·         For checking NULL values in required columns

·         Checking for Foreign Key and Quick Code values.

·         Duplication Validation

·         Business Rule validation

5] Mapping the values:

Generated fields in Oracle Applications can be mapped in this step to either default values or sequences.

6] Load data into Interface table:

·         Once the data is as clean as you can get it, the data can be inserted into the Interface table.

·         At such a time, certain columns, which are necessary in Applications but not found in legacy system, need to be populated accordingly like WHO columns.

7] Run the interface program

8] Check for Errors

9] Report on the Interface

ABOUT INTERFACES:

In Oracle Apps Interfaces are generally tables, which act as a medium to transfer the data from one module to another module or to transfer the data from legacy system into Oracle Applications. There are 352 tables provided by the Oracle Package. Each module has its own Interface Tables.

A typical path to transfer the data from Legacy System to Oracle Apps:

What is Interfacing?

It is the process of converting the records from one format to another format. The main components of this interfacing are

• Transfer Program

• Interface Table and

• Import Program

A] Transfer Program:

If the source modules data are implemented in Oracle Applications then the Transfer Programs are integrated with the Package. If the source modules are implemented in external system (i.e. other than Oracle Applications) then we have to develop our own Transfer Programs. Generally these Transfer Programs are developed using PL/SQL, JAVA or SQL Loader.

What they do?

·         It maps the columns of source table with the columns of Interface Tables.

·         It performs Row Level and Column Level validations.

·         It transfers the data from Source to the Interface Table.

B] Interface Tables:

The Interface tables basically have 4 types of columns.

1. Mandatory Columns.
2. Conditionally Required Columns.
3. Optional Columns.
4. Internal Processing Columns.

Mandatory Columns:

These are the main columns which are required in the destination tables (i.e. Oracle Application Module Tables). With the help of mandatory columns only the Import Program will converts the records from source to destination.

Conditionally Required Columns:

The values for these columns are based on the values of Mandatory columns. For Example: If you are converting foreign currency transactions to INR then it as compulsory to provide conditionally required columns like Currency conversion rate, Conversion Time and Conversion Date.

Optional Columns:

These are used when a client wanted to transfer some additional information from source to destination. These are based on client’s requirement.

Internal Processing Columns:

Status and Error Message columns are called Internal Processing Columns. These are specific only to Interface Table. These columns are going to be used by the Import Program to update the status and error message, if the record fails its validation while importing from Interface Table to the Destination Table.

C] Import Program:

For all Interface Tables, Oracle Application Package is going to provide Import Programs. These are generally registered with destination modules. These Import Programs are designed using PL/SQL, JAVA, C, C++, etc.

What they do?

·         It maps the columns of the Interface Table with one or more columns in the destination table.

·         It performs row level and column level validation.

·         It imports the data from Interface Table to the Destination tables, if the records validated successfully.

·         It deletes all the successfully validated records from Interface Table.

·         If the record fails its validation then the Import Program will update the status and error message columns of Interface Table.

Interface Vs. Application Program Interface (API):

Interfaces are used to transfer the data from legacy system to Oracle Application system where as API is used to convert the data from one form to another form with in the Oracle Application Module.

Interfaces and conversions procedure

Data can seeded in 3 ways

The Data can be entered using the application Screens.
The data can be entered using Oracle’s Open System Interface.
The data can be stored in the database table directly

Conversion An overview of its process (Inbound Process)
1. Get data from legacy system Called Flat file.
2. Load the flat file data into temporary table called staging table using sqlloader (control files are used .ctl extensions)
3. Validate the data in staging table using pl/sql procedures to define rules of validation.
4. Successful Validation rakes up the data to the interface tables.
5. These Validate data can be directed to base tables using predefined Seeded programs. (Outbound process)

Difference Between Conversion and Interface
1. Conversion is one-time process 1. Interface is the post production which is performed once before process. production go on live.(Pre-production process)
2. Data comes in to Oracle Applications 2. Interface is the integration of only (One way process) two systems.
3. Interative Process 3. Scheduled and repetive process.

Open Interface Tables are standard Oracle tables.
-Oracle uses OITs to provide a simple interface to Oracle base tables.
-Oracle has list of all the open interface that oracle offered in there product.

Interface Program
- It is an inbuilt program in most of the modules to pull up the data from the interface table Under validations sends data to base tables, if validation fails Displays data to Error a error message

Important Interface Tables

GL INTERFACE TABLES　
GL_BUDGET_INTERFACE TABLE
GL_DAILY_RATES_INTERFACE TABLE
GL_IEA_INTERFACE TABLE
GL_INTERFACE TABLE
GL_INTERFACE_CONTROL TABLE
GL_INTERFACE_HISTORY TABLE

AP INTERFACE TABLES
AP_INTERFACE_CONTROLS TABLE
AP_INTERFACE_REJECTIONS TABLE
AP_INVOICES_INTERFACE TABLE
AP_INVOICE_LINES_INTERFACE TABLE

AR INTERFACE TABLES
AR_PAYMENTS_INTERFACE_ALL TABLE
AR_TAX_INTERFACE TABLE
HZ_DQM_SYNC_INTERFACE TABLE
HZ_PARTY_INTERFACE TABLE
HZ_PARTY_INTERFACE_ERRORS TABLE
RA_CUSTOMERS_INTERFACE_ALL TABLE
RA_INTERFACE_DISTRIBUTIONS_ALL TABLE
RA_INTERFACE_ERRORS_ALL TABLE
RA_INTERFACE_LINES_ALL TABLE
RA_INTERFACE_SALESCREDITS_ALL TABLE

FA INTERFACE TABLES
FA_BUDGET_INTERFACE TABLE
FA_INV_INTERFACE TABLE
FA_PRODUCTION_INTERFACE TABLE
FA_TAX_INTERFACE TABLE

INVENTORY INTERFACE TABLES
MTL_CC_ENTRIES_INTERFACE TABLE
MTL_CC_INTERFACE_ERRORS TABLE
MTL_CI_INTERFACE TABLE
MTL_CI_XREFS_INTERFACE TABLE
MTL_COPY_ORG_INTERFACE TABLE
MTL_CROSS_REFERENCES_INTERFACE TABLE
MTL_DEMAND_INTERFACE TABLE
MTL_DESC_ELEM_VAL_INTERFACE TABLE
MTL_EAM_ASSET_NUM_INTERFACE TABLE
MTL_EAM_ATTR_VAL_INTERFACE TABLE
MTL_INTERFACE_ERRORS TABLE
MTL_INTERFACE_PROC_CONTROLS TABLE
MTL_ITEM_CATEGORIES_INTERFACE TABLE
MTL_ITEM_CHILD_INFO_INTERFACE TABLE
MTL_ITEM_REVISIONS_INTERFACE TABLE
MTL_ITEM_SUB_INVS_INTERFACE TABLE
MTL_OBJECT_GENEALOGY_INTERFACE TABLE
MTL_RELATED_ITEMS_INTERFACE TABLE
MTL_RESERVATIONS_INTERFACE TABLE
MTL_RTG_ITEM_REVS_INTERFACE TABLE
MTL_SECONDARY_LOCS_INTERFACE TABLE
MTL_SERIAL_NUMBERS_INTERFACE TABLE

MTL_SO_RMA_INTERFACE TABLE
MTL_SYSTEM_ITEMS_INTERFACE TABLE
MTL_TRANSACTIONS_INTERFACE TABLE
MTL_TRANSACTION_LOTS_INTERFACE TABLE
MTL_TXN_COST_DET_INTERFACE TABLE

PO INTERFACE TABLES
PO_DISTRIBUTIONS_INTERFACE TABLE
PO_HEADERS_INTERFACE TABLE
PO_INTERFACE_ERRORS TABLE
PO_LINES_INTERFACE TABLE
PO_REQUISITIONS_INTERFACE_ALL TABLE
PO_REQ_DIST_INTERFACE_ALL TABLE
PO_RESCHEDULE_INTERFACE TABLE
RCV_HEADERS_INTERFACE TABLE
RCV_LOTS_INTERFACE TABLE
RCV_SERIALS_INTERFACE TABLE
RCV_TRANSACTIONS_INTERFACE TABLE

BOM INTERFACE TABLES
BOM_BILL_OF_MTLS_INTERFACE TABLE
BOM_INTERFACE_DELETE_GROUPS TABLE
BOM_INVENTORY_COMPS_INTERFACE TABLE
BOM_OP_RESOURCES_INTERFACE TABLE
BOM_OP_ROUTINGS_INTERFACE TABLE
BOM_OP_SEQUENCES_INTERFACE TABLE
BOM_REF_DESGS_INTERFACE TABLE
BOM_SUB_COMPS_INTERFACE TABLE
CST_COMP_SNAP_INTERFACE TABLE
CST_INTERFACE_ERRORS TABLE
CST_ITEM_COSTS_INTERFACE TABLE
CST_ITEM_CST_DTLS_INTERFACE TABLE
CST_PC_COST_DET_INTERFACE TABLE
CST_PC_ITEM_COST_INTERFACE TABLE

WIP INTERFACE TABLES
WIP_COST_TXN_INTERFACE TABLE
WIP_INTERFACE_ERRORS TABLE
WIP_JOB_DTLS_INTERFACE TABLE
WIP_JOB_SCHEDULE_INTERFACE TABLE
WIP_MOVE_TXN_INTERFACE TABLE
WIP_SCHEDULING_INTERFACE TABLE
WIP_TXN_INTERFACE_ERRORS TABLE

Single Insert Process for Inventory Item Creation

Ø  Deriving Fields and Validations for Item Creation

Ø  Insert Script for one item

Ø  Verify the Interface table

Ø  Submit Import items program (Interface to Base table)

Ø  Verify the imported record in base table

Ø  Verify the imported item from application

Inventory Item Interface Registration Process

Staging Table Creation

Control File Creation

Folder Structure in Oracle Server(Unix)

Host (Shell Script) File Creation

Soft link Creation

           Register the Host file in application (XXAA Inventory Item Load File Program)

Package File Creation

           Register the Package in application (XXAA Inventory Item Load Interface Program)

Procedure File Creation

           Register the Procedure in application (XXAA Inventory Item Load Interface Report)

Assign the Concurrent Program to Request Group

Inventory Item Interface Execution the Process

Submit the XXAA Inventory Item Load File Program Concurrent Program

           Verify the records in staging table

           Verify the data file in Archive folder

          

Submit the XXAA Inventory Item Load Interface Program Concurrent Program

           Verify record status in Staging table

           Verify the loaded records in interface table

          

Submit the Import Items Concurrent Program (Standard Program)

           Verify the Log report

           Verify the Interface error records

           Verify the loaded records in base table

          

Submit the XXAA Inventory Item Load Interface Report Concurrent Program

           Verify the staging table record status

           Verify the log report

           Verify the Output report

           Verify the loaded record from application  

           Verify the records in base table

Scheduling the Inventory Item Interface Process

Create the Request Set

Assign the Request set to Request Group

Schedule the Request set for daily

Migration Scripts for Concurrent Program and Request Set

Inventory Item Load Interface Code Pack list

Single Insert for Inventory Item Creation

Deriving Fields and Validations for Item Creation

select * from mtl_parameters

where organization_code = 'M1'

and master_organization_id = 204    -- V1 (Master Org Code)

and organization_id = 207    -- M1 (Child Org Code)

select template_id, template_name

from mtl_item_templates

where template_name = 'Finished Good'

and template_id = 107

select unit_of_measure, uom_code primary_uom_code

from mtl_units_of_measure_tl

where unit_of_measure = 'Each'

and primary_uom_code = 'Ea'

select user_id

from fnd_user

where user_name = 'OPERATIONS'

and user_id = 1318

Along with the specific columns, we need to pass the following columns                   

1.process_flag      = '1'          (in process)

2.transaction_type  = 'CREATE'     (in case of creation)

                      'UPDATE'     (in case of updating existing record)

3.set_process_id: used when multiple item open interface processes are run in parallel.

4.standard who columns like created_by, last_updated_by = user_id

                        and creation_date, last_update_date = sysdate.

Insert Script for one item

INSERT INTO MTL_SYSTEM_ITEMS_INTERFACE

                (organization_id,

                segment1,

                description,

                template_id,

                primary_uom_code,

                transaction_type,

                set_process_id,

                process_flag,

                creation_date,

                created_by,

                last_update_date,

                last_updated_by)

        VALUES    

                (204,            -- Master Organization Id

                'Raj_Item_01',   -- Segment1(Item Name)

                'Raj_Item_01',   -- Description(Description For The Item)

                107,             -- Template_Id

                'Ea',            -- Primary_Uom_Code

                'CREATE',        -- Transaction_Type

                1,               -- Set_Process_Id

                1,               -- Process_Flag

                sysdate,         -- Creation_Date

                1318,            -- Created_By

                sysdate,         -- Last_Update_Date

                1318             -- Last_Updated_By

                );

               

COMMIT;

Please click on the below link for more details:

Oracle Interface – Oracle EBS R12 End to End Interface Process

https://drive.google.com/file/d/1pJs5Gy2T9o-gF3vW_-zBd4HaS7AtTAxa

Download the code pack from below Google Drive link

https://drive.google.com/drive/u/7/folders/13LknqzJdYgx-p-c7KWbykhf8EencPnwH