Oracle Applications: Interfaces in Oracle Application

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:

http://imdjkoch.files.wordpress.com/2010/08/interface.jpg?w=500&h=227

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 process

Interfaces and conversions procedure

Data can feeded 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)

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.

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.

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

PO INTERFACES

Purchase Order

The Purchasing Document Open Interface concurrent program was replaced by two new concurrent programs Import Price Catalogs and Import Standard Purchase Orders. Import Price Catalogs concurrent program is used to import Catalog Quotations, Standard Quotations, and Blanket Purchase Agreements. Import Standard Purchase Orders concurrent program is used to import Unapproved or Approved Standard Purchase Orders.

Import Standard Purchase Orders

Pre-requisites:

Suppliers, sites and contacts

Buyers

Line Types

Items

Charge account setup

Interface Tables:

PO_HEADERS_INTERFACE

PO_LINES_INTERFACE

PO_DISTRIBUTIONS_INTERFACE

PO_INTERFACE_ERRORS (Fallouts)

Interface Program:

Import Standard Purchase Orders.

Base Tables:

PO_HEADERS_ALL

PO_LINES_ALL

PO_DISTRIBUTIONS_ALL

PO_LINE_LOCATIONS_ALL

Validations:

Header:

Check if OU name is valid

Check if Supplier is valid

Check if Supplier site is valid

Check if buyer is valid

Check if Payment term is valid

Check if Bill to and ship to are valid

Check if FOB, freight terms are valid

Lines:

Check if Line_type, ship_to_org, item, uom, ship_to_location_id, requestor, charge_account, deliver_to_location are valid

General:

Check for duplicate records in interface tables

Check if the record already exists in base tables.

Some important columns that need to be populated in the interface tables:

PO_HEADERS_INTERFACE:

INTERFACE_HEADER_ID (PO_HEADERS_INTERFACE_S.NEXTVAL), BATCH_ID, ORG_ID, INTERFACE_SOURCE_CODE, ACTION (‘ORIGINAL’,’UPDATE’,’REPLACE’), GROUP_CODE, DOCUMENT_TYPE_CODE, PO_HEADER_ID (NULL), RELEASE_ID, RELEASE_NUM, CURRENCY_CODE, RATE, AGENT_NAME, VENDOR_ID, VENDOR_SITE_ID, SHIP_TO_LOCATION, BILL_TO_LOCATION, , PAYMENT_TERMS

PO_LINES_INTERFACE:

INTERFACE_LINE_ID, INTERFACE_HEADER_ID, LINE_NUM, SHIPMENT_NUM, ITEM, REQUISITION_LINE_ID, UOM, UNIT_PRICE, FREIGHT_TERMS, FOB

PO_DISTRIBUTIONS_INTERFACE:

INTERFACE_LINE_ID, INTERFACE_HEADER_ID, INTERFACE_DISTRIBUTION_ID, DISTRIBUTION_NUM, QUANTITY_ORDERED, QTY_DELIVERED, QTY_BILLED, QTY_CANCELLED, DELIVER_TO_LOCATION_ID, DELIVER_TO_PERSON_ID, SET_OF_BOOKS, CHARGE_ACCT, AMOUNT_BILLED.

--Staging Tables

CREATE TABLE xx_po_header_stg(

interface_header_id NUMBER

,batch_id NUMBER

,org_id NUMBER

,action VARCHAR2(25)

,document_type_code VARCHAR2(25)

,currency_code VARCHAR2(15)

,agent_id NUMBER

,full_name VARCHAR2(240)

,vendor_name VARCHAR2(240)

,vendor_site_code VARCHAR2(15)

,ship_to_location VARCHAR2(60)

,bill_to_location VARCHAR2(60)

,approval_status VARCHAR2(25)

,freight_carrier VARCHAR2(25)

,fob VARCHAR2(25)

,freight_terms VARCHAR2(25))

CREATE TABLE xx_po_line_stg

(

interface_header_id NUMBER

,interface_line_id NUMBER

,line_num NUMBER

,shipment_num NUMBER

,line_type VARCHAR2(25)

,item VARCHAR2(1000)

,item_description VARCHAR2(240)

,item_id NUMBER

,uom_code VARCHAR2(3)

,quantity NUMBER

,unit_price NUMBER

,ship_to_organization_code VARCHAR2(3)

,ship_to_location VARCHAR2(60)

,list_price_per_unit NUMBER)

CREATE TABLE xx_po_distribution_stg

(interface_header_id NUMBER,

interface_line_id NUMBER,

interface_distribution_id NUMBER,

org_id NUMBER,

quantity_ordered NUMBER,

destination_organization_id NUMBER,

set_of_books_id NUMBER,

charge_account_id VARCHAR2(2000),

distribution_num NUMBER);

--Creation Of The Package.

CREATE OR REPLACE PACKAGE xxak_po_imp_pkg

PROCEDURE xxak_po_imp_prc (errbuf OUT VARCHAR2, retcode OUT NUMBER);

END;/

--Creation Of The Procedure.

CREATE OR REPLACE PACKAGE BODY xxak_po_imp_pkg

PROCEDURE xxak_po_imp_prc (errbuf OUT VARCHAR2, retcode OUT NUMBER)

CURSOR cur_head

SELECT *

FROM xx_po_header_stg;

CURSOR cur_line (p_interface_header_id NUMBER)

SELECT *

FROM xx_po_line_stg

WHERE interface_header_id = p_interface_header_id;

CURSOR cur_dist (p_interface_line_id NUMBER)

SELECT *

FROM xx_po_distribution_stg

WHERE interface_line_id = p_interface_line_id;

lv_vendor_id NUMBER (10);

lv_agent_id NUMBER (10);

lv_itemid NUMBER;

lv_site_code VARCHAR2 (100);

lv_lookup_code VARCHAR2 (25);

lv_curr_code VARCHAR2 (10);

lv_org_id NUMBER (6);

BEGIN

SELECT organization_id

INTO lv_org_id

FROM hr_operating_units

WHERE NAME LIKE 'Vision Operations';

EXCEPTION

WHEN OTHERS

THEN

fnd_file.put_line (fnd_file.LOG, 'invalid org_id');

END;

BEGIN

FOR var1 IN cur_head

LOOP

BEGIN

SELECT vendor_id

INTO lv_vendor_id

FROM po_vendors

WHERE vendor_name = var1.vendor_name;

EXCEPTION

WHEN OTHERS

THEN

fnd_file.put_line (fnd_file.LOG, 'invalid vendor_id');

END;

BEGIN

SELECT vendor_site_code

INTO lv_site_code

FROM po_vendor_sites_all

WHERE vendor_site_code = var1.vendor_site_code;

EXCEPTION

WHEN OTHERS

THEN

fnd_file.put_line (fnd_file.LOG,

'invalid vendor_site_code');

END;

BEGIN

SELECT currency_code

INTO lv_curr_code

FROM fnd_currencies

WHERE currency_code = var1.currency_code;

EXCEPTION

WHEN OTHERS

THEN

fnd_file.put_line (fnd_file.LOG, 'invalid currency_code');

END;

BEGIN

SELECT document_type_code

INTO lv_lookup_code

FROM po_document_types

WHERE document_type_code = var1.document_type_code;

EXCEPTION

WHEN OTHERS

THEN

fnd_file.put_line (fnd_file.LOG, 'invalid typecode');

END;

BEGIN

SELECT person_id

INTO lv_agent_id

FROM per_all_people_f

WHERE full_name = var1.full_name;

EXCEPTION

WHEN OTHERS

THEN

fnd_file.put_line (fnd_file.LOG, 'invalid Buyer');

END;

BEGIN

INSERT INTO po_headers_interface

(interface_header_id,

batch_id,

org_id,

action,

document_type_code,

currency_code,

agent_id,

agent_name,

vendor_name,

vendor_site_code,

ship_to_location, bill_to_location,

approval_status, freight_terms,

fob, freight_carrier, created_by, creation_date,

last_update_date, last_updated_by

)

VALUES (po_headers_interface_s.NEXTVAL, var1.batch_id,

lv_org_id, var1.action, var1.document_type_code,

var1.currency_code, lv_agent_id, var1.full_name,

var1.vendor_name, var1.vendor_site_code,

var1.ship_to_location, var1.bill_to_location,

var1.approval_status, var1.freight_terms,

var1.fob, var1.freight_carrier, -1, SYSDATE,

SYSDATE, -1

);

EXCEPTION

WHEN OTHERS

THEN

fnd_file.put_line (fnd_file.output,

'insertion successful into headers int'

);

END;

FOR var2 IN cur_line (var1.interface_header_id)

LOOP

BEGIN

SELECT inventory_item_id

INTO lv_itemid

FROM mtl_system_items_b

WHERE segment1 = var2.item AND organization_id = lv_org_id;

EXCEPTION

WHEN OTHERS

THEN

-- lv_item := NULL;

fnd_file.put_line (fnd_file.LOG, 'invalid item');

END;

BEGIN

INSERT INTO po_lines_interface

(interface_line_id,

interface_header_id,

line_num,

shipment_num,

line_type,

item,

item_description,

item_id,

uom_code,

quantity,

unit_price,

ship_to_organization_code,

ship_to_location,

need_by_date,

-- ,PROMISED_DATE

list_price_per_unit,

created_by,

creation_date,

last_update_date,

last_updated_by

)

VALUES (po_lines_interface_s.NEXTVAL,

po_headers_interface_s.CURRVAL,

var2.line_num,

var2.shipment_num,

var2.line_type,

var2.item,

var2.item_description,

var2.item_id,

var2.uom_code,

var2.quantity,

var2.unit_price,

var2.ship_to_organization_code,

var2.ship_to_location,

SYSDATE,

-- ,SYSDATE

var2.list_price_per_unit,

-1,

SYSDATE,

-1

);

EXCEPTION

WHEN OTHERS

THEN

fnd_file.put_line (fnd_file.output,

'insertion successful into lines int'

);

END;

FOR var3 IN cur_dist (var2.interface_line_id)

LOOP

BEGIN

INSERT INTO po_distributions_interface

(interface_header_id,

interface_line_id,

interface_distribution_id,

org_id, quantity_ordered,

destination_organization_id,

set_of_books_id,

charge_account_id,

distribution_num, created_by,

creation_date, last_update_date,

last_updated_by

)

VALUES (po_headers_interface_s.CURRVAL,

po_lines_interface_s.CURRVAL,

po_distributions_interface_s.NEXTVAL,

lv_org_id, var3.quantity_ordered,

var3.destination_organization_id,

var3.set_of_books_id,

var3.charge_account_id,

var3.distribution_num, -1,

SYSDATE, SYSDATE,

-1

);

EXCEPTION

WHEN OTHERS

THEN

fnd_file.put_line

(fnd_file.output,

'insertion successful into dist int'

);

END;

END LOOP;

END;

COMMIT;

END xxak_po_imp_prc;

END xxak_po_imp_pkg;/

Pages

Tuesday, February 12, 2013

Interfaces in Oracle Application

Interfaces and conversions process

Interfaces and conversions procedure

Important Interface Tables

PO INTERFACES

PO INTERFACES

2 comments:

Post a Comment