Interface

• Interfaces are extensively used to integrate external system into oracle application
• It is used at the time of data migration

- data migration from external system into oracle application
- data migration from oracle application to external system

• It can also be used at the time of fresh installation of oracle application

TYPES OF INTERFACES

there are 2 main types of interfaces

1. INBOUND INTERFACE
these are used for transferring data from external system(legacy system) into oracle
application

2. OUTBOUND INTERFACE
these are used for transferring data from oracle application to external system.

There are 2 Distinct types of interfaces

1. OPEN INTERFACE
In this the logic is been provided by the oracle itself

OPEN INTERFACE LOGIC

• Data from the external file is transfered to the database table called as Interface table for that specific module.
• Then Validation are performed an that available data.
• Any error found are then transfered to the error table.
• Then the validated record are then transferred to the application tables.
  

2. CUSTOM INTERFACE
In this the logic has to be developed by the implementer.

For Interface we need A staging table

What is the need of a staging table or temporary table in between flat file and standard interface table while transferring data from flat file to interface table?

1.Once we have data in the interface table we are simply running the standard program and it automatically send data to the base table so in this time we can't do any validations in in between the interface table and base table that'y we use staging table to validate the data before putting into the interface table.Finally the staging tables are used to load the data from legacy systems what ever the client provided .after that we write a validation program to send proper valid data to interface table.

2.The basic need for a staging table is to serve as another step before loading into the interface tables after which we run oracle standard APIs to load them into the bas tables sometimes even after running APIS we may need some concurrent programmes to be run so for that data to be reflected across the base tables. The staging area serves for updates like date formats (MM-DD-RRRR) and other such critical data format changes neede before we load into interface tables and then running APIS in them . As explained above a bad file if formed while loading if some data format is not in consonance with the API or base file and hence the whole API/validation fails, In some case though it creates a corrupt log file which also indicates the failure of the validation.

3.These staging tables are used to hold temporary data i.e data with in the temporary table will be automatically get deleted when we issue commit or when we close oracle session that depends on how we create table

eg: create global temporary table eg1(name varchar2(20),num number(10)) on commit delete rows;

this will delete the rows on temporary table when we issue a commit at sql prompt.

moreover these staging tables are used to do some primray validations before inserting data into actual tables.

so we need data to be erased after inserting into acutal tables. so we go for staging tables.

we can use normal tables also as staging table but we need to truncate table for each insert. thats why we go for temporary tables.

What are Interface table in AP

1). AP_INTERFACE_CONTROLS.
2). AP_INTERFACE_REJECTIONS
3). AP_INVOICE_INTERFACE
4). AP_INVOICE_LINES_INTERFACE.

AR INTERFACE TABLE:

1). AR_PAYMENTS_INTERFACE_ALL
2). AR_TAX_INTERFACE
3). HZ_PARTY_INTERFACE
4). HZ_PARTY_INTERFACE_ERRORS
5). RA_CUSTOMERS_INTERFACE_ALL
6). RA_INTERFACE_DISTRIBUTIONS_ALL
7). RA_INTERFACE_ERRORS_ALL
8). RA_INTERFACE_LINES_ALL
9). RA_INTERFACE_SALESCREDITS_ALL

GL INTERFACE TABLE:

1). GL_BUDGET_INTERFACE
2). GL_DAILY_RATES_INTERFACE
3). GL_IEA_INTERFACE
4). GL_interface
5). GL_INTERFACE_CONTROL
6). GL_INTERFACE_HISTORY

EXAMPLE FOR INTERFACE TRY

ITEM UPLOAD INTERFACE

STEP1.

GO TO APPS AND CREATE STAGING TABLE

create table SG_ITEM_STAGE
(segment1 varchar2(25),
DESCRIPTION VARCHAR2(25),
TEMPLATE_NAME VARCHAR2(25),
MATERIAL_COST NUMBER,
PROCESS_FLAG NUMBER,
ENABLED_FLAG VARCHAR2(1),
TRANSACTION_TYPE VARCHAR2(25),
ORGANIZATION_CODE VARCHAR2(25))

STEP 2.

Create a SG_ITEM_DATA.dat File

SGTest Item51, SGTest Item11,Finished Good,100,
SGTest Item52, SGTest Item12, Finished Good, 500,
SGTest Item53, SGTest item13, Finished Good, 600,
SGtest Item54, SGTest Item14, Finished Good,300,

STEP 3.

Create a shri.ctl file (control file which contains the logic of uploading data from flat file to the stagging table)

LOAD DATA
INFILE '/apps/visappl/cust/11.5.0/bin/SG_ITEM_DATA.dat'
INTO TABLE SG_ITEM_STAGE
APPEND
FIELDS TERMINATED BY "," OPTIONALLY ENCLOSED BY '"'
(SEGMENT1,
DESCRIPTION,
TEMPLATE_NAME,
MATERIAL_COST,
PROCESS_FLAG CONSTANT 1,
ENABLED_FLAG CONSTANT 'Y',
TRANSACTION_TYPE CONSTANT 'Create',
ORGANIZATION_CODE CONSTANT "V1")

STEP 4.

UP LOAD CTL AND DAT FILE
FTP
TELNET

STEP 5.

CHECK DATA IN STAGING TABLE
select count(*) from
SG_STAGE_TABLE;

STEP 6.

CREATE PRE_INTERFACE TABLE AS INTERFACE TABLE

create table SG_ITEM_PRE_INTERFACE AS
select * from mtl_system_items_interface ;

STEP 7.

RUN THIS PROCEDURE TO UPLOAD DATA FROM STAGING TABLE TO PREINTERFACE
TABLE

STAGING TABLE---------------------------->PRE-INTERFACE TABLE

CREATE OR REPLACE Procedure SG_ITEM_PRE_INTERFACE Is
m_Segment1 varchar2(40);
m_Description varchar2(40);
M_TEMPLATE_NAME VARCHAR2(40);
M_MATERIAL_COST NUMBER;
M_PROCESS_FLAG NUMBER;
m_Transaction_Type varchar2(10);
m_Organization_CODE VARCHAR2(10);
M_ENABLED_FLAG VARCHAR2(1);
m_set_process_id number;
Cursor C1 is Select * from SG_STAGE_TABLE;
Begin
For rec in C1
loop
m_Segment1 := REC.SEGMENT1;
m_Description := REC.DESCRIPTION;
M_TEMPLATE_NAME := REC.TEMPLATE_NAME;
IF M_MATERIAL_COST>500 THEN
M_MaTERIAL_COST:=500;
END IF;
IF M_PROCESS_FLAG IN(2,3,4,5) THEN
M_PROCESS_FLAG:=1;
ELSE
M_PROCESS_FLAG:=REC.PROCESS_FLAG;
END IF;
M_MATERIAL_COST := abs(REC.MATERIAL_COST);
M_PROCESS_FLAG := REC.PROCESS_FLAG;
m_Transaction_Type := REC.TRANSACTION_TYPE;
m_Organization_CODE:= REC.ORGANIZATION_CODE;
M_ENABLED_FLAG := REC.ENABLED_FLAG;
m_set_process_id := 25101974;
Insert into
SG1_ITEM_PRE_INTERFACE
(
SEGMENT1,
DESCRIPTION,
TEMPLATE_NAME,
MATERIAL_COST,
PROCESS_FLAG,
TRANSACTION_TYPE,
ORGANIZATION_CODE,
ENABLED_FLAG,
SET_PROCESS_ID
)
values
(
m_Segment1,
m_Description,
M_TEMPLATE_NAME,
M_MATERIAL_COST,
M_PROCESS_FLAG,
m_Transaction_Type,
m_Organization_CODE,
M_ENABLED_FLAG,
M_SET_PROCESS_ID
);
End Loop;
End SG_ITEM_PRE_INTERFACE;
/

Compile & execute this procedure. After this check data is uploaded in the pre-interface
table.
EXEC SG_ITEM_PRE_INTERFACE;
SELECT COUNT(*) FROM SG1_ITEM_PRE_INTERFACE;

STEP 7.

TRANSFER DATA FROM
PRE-INTERFACE TABLE-------------------------->INTERFACE TABLE

CREATE OR REPLACE Procedure SG2_ITEM_INTERFACE Is
m_Segment1 varchar2(40);
m_Description varchar2(40);
M_TEMPLATE_NAME VARCHAR2(40);
M_MATERIAL_COST NUMBER;
M_PROCESS_FLAG NUMBER;
m_Transaction_Type varchar2(10);
m_Organization_CODE VARCHAR2(10);
M_ENABLED_FLAG VARCHAR2(1);
m_set_process_id number;
Cursor C1 is Select * from SG1_ITEM_PRE_INTERFACE;
Begin
For rec in C1
loop
m_Segment1 := REC.SEGMENT1;
m_Description := REC.DESCRIPTION;
M_TEMPLATE_NAME := REC.TEMPLATE_NAME;
M_MATERIAL_COST := REC.MATERIAL_COST;
M_PROCESS_FLAG := REC.PROCESS_FLAG;
m_Transaction_Type := REC.TRANSACTION_TYPE;
m_Organization_CODE:= REC.ORGANIZATION_CODE;
M_ENABLED_FLAG := REC.ENABLED_FLAG;
m_set_process_id := 25101974;
Insert into MTL_SYSTEM_ITEMS_INTERFACE
(
SEGMENT1,
DESCRIPTION,
TEMPLATE_NAME,
MATERIAL_COST,
PROCESS_FLAG,
TRANSACTION_TYPE,
ORGANIZATION_CODE,
ENABLED_FLAG,
SET_PROCESS_ID
)
values
(
m_Segment1,
m_Description,
M_TEMPLATE_NAME,
M_MATERIAL_COST,
M_PROCESS_FLAG,
m_Transaction_Type,
m_Organization_CODE,
M_ENABLED_FLAG,
M_SET_PROCESS_ID
);
End Loop;
End SG2_ITEM_INTERFACE;

EXEC SG_ITEM_PRE_INTERFACE;

SELECT COUNT(*) FROM SG1_ITEM_PRE_INTERFACE;

STEP 8.
Check the data
SELECT COUNT(*) FROM MTL_SYSTEM_ITEMS_INTERFACE;

STEP 9.
Log on to apps(mfg/welcome).
select ==>Inventory.
In Navigator ==> Items==>Import Items ==> Select master organisation