Interfaces | Inbound | Outbound | Purchasing | EBS | Oracle Apps R12 | Oracle EBS

Interfaces

What is Interface?

          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.

          Interfaces in oracle apps r12 are ways to exchange data between Oracle Applications and external systems.

The main components in Interfaces: -

1.  Transfer Program (The data Transferred from Staging table to Interface table.
2.  Interface Table
3.  Import Program (The data Transferred from Interface table to Base table.)

1. Transfer Program:

          Sometimes, we need to move data from one system to another. Oracle Applications is a system that has many modules, such as accounting, inventory, sales, etc. Each module has its own data. If we want to move data from one module to another in Oracle Applications, we can use Transfer Programs that are already built-in. But if we want to move data from another system that is not Oracle Applications, we have to make our own Transfer Programs. Transfer Programs are usually written in PL/SQL, JAVA or SQL Loader, which are languages that computers understand.

2. Interface Tables:

The Interface tables basically have 4 types of columns.

·        Mandatory Columns.

·        Conditionally Required Columns.

·        Optional Columns.

·        Internal Processing Columns.

 

            Mandatory Columns: -

            These are the primary columns of the destination tables (oracle application module tables) that must be present. Only the Import Program can transform the records from source to destination with the help of required columns.

 

Conditionally Required Columns: -

               These columns' values are determined by taking the values of the mandatory columns. For example, you must include conditionally required columns such as Currency conversion rate, Conversion Time, and Conversion Date if you are converting foreign currency transactions to Indian rupees.

 

Optional Columns: -

               When a customer needs to move some extra data from the source to the destination, they are used. These are based on client’s requirement.

Internal Processing Columns: -

               Internal Processing Columns are those that contain the Status and Error Messages. These are unique to Interface Table alone. If a record fails validation during import from the Interface Table to the Destination Table, the Import Program will use these columns to update the status and error message.

3. Import Program: -

               The Oracle Application Package will offer import programs for all Interface Tables. The destination modules are typically registered with these. The languages used to create these import programs are PL/SQL, Java, C, C++, etc.

Types of Interfaces: -

      There are 2 types of Interfaces:

1. Inbound Interface.
2. Outbound Interface.

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

Inbound Process: -

1. Get data from legacy system Called Flat file.
2. Load the flat file data into temporary table called staging table using SQL loader(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 takes up the data to the interface tables.
5. These Validate data can be directed to base tables using predefined Seeded programs.

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

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

Interface vs Application Program Interface:

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

Interfaces and Conversions Process:

Data can be feeded in 3 ways:

1. The data can be entered using the Application Screens.
2. The data can be entered using Oracle’s Open System Interface.  
3. The data can be stored in the database table directly.

Example for Inbound Process: -

According to the flat file data we create a table and control file.

Step 1: Create a Staging table to insert data from flat file.

CREATE TABLE xxbb_po_hed_line_dist_stg (

                              operating_unit                  VARCHAR2(240),

                              document_type_code     VARCHAR2(25),

                              vendor_name                   VARCHAR2(240),

                              vendor_site_code            VARCHAR2(15),

                              ship_to_location               VARCHAR2(60),

                              bill_to_location                 VARCHAR2(60),

                              buyer                                  VARCHAR2(80),

                              batch_id                             NUMBER,

                              line_type                            VARCHAR2(25),

                              item                                    VARCHAR2(1000),

                              category                             VARCHAR2(2000),

                              unit_of_measure              VARCHAR2(25),

                              quantity                             NUMBER,

                              unit_price                          NUMBER,

                              destination_type              VARCHAR2(25),

                              status                                  CHAR (1),

                              creation_date                   DATE,

                              created_by                        NUMBER,

                              last_update_date             DATE,

                              last_updated_by              NUMBER,

                              last_update_login            NUMBER           

                              );

Step 2: Create a control file to load the flat file data into the staging table.

 options(SKIP=1)

LOAD DATA

infile *

TRUNCATE

INTO TABLE XXBB_PO_HED_LINE_DIST_STG

FIELDS TERMINATED BY ','

OPTIONALLY ENCLOSED BY '"'

TRAILING NULLCOLS

                 (OPERATING_UNIT                       "TRIM(:OPERATING_UNIT)"                   

                 ,DOCUMENT_TYPE_CODE          "TRIM(:DOCUMENT_TYPE_CODE)"                                       

                 ,VENDOR_NAME                           "TRIM(:VENDOR_NAME)"           

                 ,VENDOR_SITE_CODE                 "TRIM(:VENDOR_SITE_CODE)"            

                 ,SHIP_TO_LOCATION                   "TRIM(:SHIP_TO_LOCATION)"         

                 ,BILL_TO_LOCATION                   "TRIM(:BILL_TO_LOCATION)"

                 ,Buyer                                                "TRIM(:Buyer)"

                 ,BATCH_ID                                      "TRIM(:BATCH_ID)"

                 ,LINE_TYPE                                    "TRIM(:LINE_TYPE)"              

                 ,ITEM                                               "TRIM(:ITEM)"           

                 ,CATEGORY                                    "TRIM(:CATEGORY)"            

                 ,UNIT_OF_MEASURE                   "TRIM(:UNIT_OF_MEASURE)"          

                 ,QUANTITY                                    "TRIM(:QUANTITY)"          

                 ,UNIT_PRICE                                  "TRIM(:UNIT_PRICE)"          

                 ,DESTINATION_TYPE                   "TRIM(:DESTINATION_TYPE)"           

                 ,STATUS                                           "nvl(:STATUS,'N')"             

                 ,CREATION_DATE                         SYSDATE                              

                 ,CREATED_BY                                "fnd_global.user_id"                      

                 ,LAST_UPDATE_DATE                 SYSDATE               

                 ,LAST_UPDATED_BY                   "fnd_global.user_id"                  

                 ,LAST_UPDATE_LOGIN               "fnd_global.LOGIN_ID")

Step 3: After creating the creating file, we need to place the control file and flat file in respective module bin folder. 

Ex-Path:  D:\oracle\VIS\apps\apps_st\appl\po\12.0.0\bin

Step 4: Create a concurrent program for inserting records from flat file to staging table.

Ø First, we create an executable.

               Navigation: Application Developer à Concurrent à Executables

o   Executable Name                : ‘XX_PO_INV_ITEM’

o   Short Name                         : ‘XX_PO_INV_ITEM’

o   Application                         : ‘Purchasing’

o   Execution Method              : ‘SQL *Loader’

o   Execution Method Name   : <ctl file name> ‘PO_H_L_D_FILE’

    

    Click on SAVE Button.

Ø  Create a Concurrent program for the above executable

Navigation: Application Developer à Concurrent à program

o   Program                : ‘XX_PO_INV_ITEM’   

o   Short Name           : ‘XX_PO_INV_ITEM’

o   Application           : ‘Purchasing’

o   Executable Name  : ‘XX_PO_INV_ITEM’

Then click on Parameters.

Give the parameter name as PATH

1. Value Set : ’240 Characters Optional’
2. Default Type : ‘Constant’

Click on SAVE Button.

  Assign a Concurrent Program to the Respective responsibility.

Example : (Purchasing, Vision Operation (USA)) 

Navigation: System Administrator => Security => Responsibility => Request

1.    Group             : ‘All Reports’
2.    Application     : ‘Purchasing’ 

Click on type and add a concurrent program <XX_PO_INV_ITEM>

  Click on SAVE Button.

  Run the Program

Navigation: Purchasing, Vision Operation (USA) => top menu view => request =>

                     Submit a new Request

·       Name: ‘XX_PO_INV_ITEM’

Click on OK, then Click on SUBMIT Button.

The Concurrent program Successfully completed… then the data in flat file is inserted into a stagging table.

Step 5: Create a concurrent program to transfer the data from staging table to interface table.

• Firstly, we create an Executable.

Navigation: Application Developer => Concurrent => Executables

• Executable Name                     : ‘XX_PO_PKG’
• Short Name                               : ‘XX_PO_PKG’
• Application                                : ‘Purchasing’
• Execution Method                    : ‘PL/SQL Stored Procedure’
• Execution Method Name        : <Package.Procedure name> 

Example: - ‘PO_IMPORT_PKG.MAIN_PRC’

        To see the package Spec and body click on below link.

                 Inbound.sql

        

       The file talk about the validations required to insert a data from stagging table to interface table of   Purchase Order.

      

        

        Then click on SAVE Button.

 Create a Concurrent program for the above executable.

Navigation: Application Developer à Concurrent à program.

• Program                       : ‘XX_PO_PKG’
• Short Name                  : ‘XX_PO_PKG’
• Application                  : ‘Purchasing’
• Executable Name           : ‘XX_PO_PKG’ 

Note: For this Executable method file, there is no parameters. So, no need to create parameters.

Click on SAVE Button. 

Assign a Concurrent Program to the Respective responsibility 

Example: (Purchasing, Vision Operation (USA))

Navigation: System Administrator =>  Security =>  Responsibility => Request

• Group             : ‘All Reports’
• Application     : ‘Purchasing’ 

Click on type and add a concurrent program <XX_PO_PKG>

Click on SAVE Button.

Run the Program

Navigation: Purchasing, Vision Operation (USA) => top menu => view => request => Submit a new Request.

Name: ‘XX_PO_PKG’

Click on SUBMIT Button.

The Concurrent program Successfully completed… then the data in stagging table is inserted into a respective interface table.

In a staging table, the status column is updated only when all validations pass in the preceding concurrent program.

After Step 4, Status of the staging table is by default ‘E’,

but after Step 5 Status of the staging table is ‘V’.

v Data inserted in PO_HEADERS_INTERFACE

 

v Data Inserted in PO_LINES_INTERFACE

v Data inserted in PO_DISTRIBUTIONS_INTERFACE

 Step 6: Now we need to transfer data from Interface Table to Base table, for that we need to run one                seeded Concurrent Program (Import Standard Purchase Orders).

Navigation: Purchasing, Vision Operation (USA)à top menuà view à request àSubmit a new Request.

Name  : ‘XX_PO_PKG’

·       Parameters      à

• Default Buyer                 : ‘Stock. Mr. Pat’
• Create or Update Items  : ‘Yes’
• Approved Status             : ‘Approved’
• Batch Id                             : ‘100’

        

Click on OK, then Click on SUBMIT Button.

 

The Concurrent program Successfully completed… then the data in Interface table is inserted into a respective Base table.

v Data inserted in PO_HEADERS_ALL

v Data inserted in PO_LINES_ALL

v Data inserted on PO_DISTRIBUTIONS_ALL

• PO INTERFACE TABLES: -                                                PO BASE TABLES: -
• PO_DISTRIBUTIONS_INTERFACE TABLE                        PO_DISTRIBUTIONS_ALL
• PO_HEADERS_INTERFACE TABLE                                    PO_HEADERS_ALL
• PO_LINES_INTERFACE TABLE                                           PO_LINES_ALL

Inbound Process complete.

Outbound Interface Example Using UTL_FILE: -

Outbound Interface will be used to extract the data from oracle Database tables into the flat files. While developing the outbound Interface we will use UTL_File to Extract the data.

Step 1: First, we need to create our own directory in oracle 

Syntax: CREATE OR REPLACE DIRECTORY Directory_name AS ‘Path for directory’.

Example: CREATE OR REPLACE DIRECTORY utl_file AS 'E:';

Step 2: After creating directory, we need to give necessary grants to directory to read, write and execute.

Syntax: GRANT READ, WRITE ON DIRECTORY directory_name TO username;

Example: GRANT READ, WRITE ON DIRECTORY utl_file TO APPS;

Here is an example or Outbound Interface:

Please refer below link for Outbound Query.

Outbound.sql 

 

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.

OUTPUT FILE: -

                                                           Output File

v  Open the above file to see the output.