Interfaces

Interface is one of the PL/SQL program, which will be used to transfer the files from Legacy System to Oracle Application or from Oracle Applications to Legacy System. There are two types of interfaces in oracle apps.

 1. Inbound Interface.

 2. Outbound Interface.

Inbound Interface: To transfer the data from legacy system to oracle applications base tables is called inbound interface. In inbound interface we use SQL * Loader, External Tables,

Example: If the client is using the one or more applications like .net, SAP, java along with oracle applications. We need to develop inbound interface program to transfer other applications data to Oracle Applications.

Following are the steps used at inbound interface program development.

1. First we receive a flat file (data file) from client.

2. We will create staging/temp table based on the flat file structure (Columns).

3. By using SQL * Loader program, we will upload the data from flat file to staging table.

4. We will create a PL/SQL Package to validate the staging data and insert the valid data to oracle interface tables.

5. Run the Oracle Standard Import program to transfer the data from interface table to base tables in oracle.

Outbound Interface: To transfer the data from oracle applications to legacy system is called outbound interface. In outbound interface we use UTL_FILE package to extract the data from oracle apps base tables and will generate flat file.

Example: If the client is implemented two different application modules like HRMS and Financials. We have to transfer the HRMS data to financial modules.

Following are the steps used at outbound interface program development.

 1. Write the cursor to retrieve the required columns data from database tables

 2. Define the file using UTL_FILE.FOPEN().

 3. Open the cursor for loop and write all the records information to a file by using  UTL_FILE.PUT_LINE().

 4. Close the cursor and close the file by using UTL_FILE.FCLOSE().

 5. Create a package with above steps and register it as a concurrent program in oracle apps.

To find utl_file directory:-

Select * from V$Parameter where name like ‘%UTL_FILE%’;

Example:-

Create or Replace Procedure Supp_interface ( errbuf  out varchar2

  , retcode out varchar2)

As

Cursor c1 

   is

     SELECT  Pv.venodr_id   Vid

   , Pv.vendor_name Vname

, Pvs.vendor_site_code  Vsite

, Pvs.address_line1         Vaddress

, Pvs.City                        Vcity

  FROM   Po_vendors pv

   , po_vendor_sites_all pvs

  WHERE  Pv.vendor_id  =  pvs.venodr_id;

V_File    UTL_FILE.file_type;

V_Count   NUMBER (10) := 0;

Begin

 

v_file := UTL_FILE,fopen(‘d:\Oracle\Proddb\8.1.7\pl/Sql\temp’,’suppliers.dat’,’w’);

for c2 in c1

Loop

   v_count := v_count+1

UTL_FILE.PUT_LINE (v_file, C2.Vid ||’,’||

    C2.Vname ||’,’||

  C2.Address ||’,’||

  C2.Vcity);

End loop;

UTL_FILE.fclose (V_file);

FND_FILE.put_line (FND_FILE.output, ’Number of Records Transferred  =’||V_count);

Exception

  WHEN utl_file.invalid_operation THEN

   fnd_file.put_line(fnd_File.log,'invalid operation');

utl_file.fclose_all;

  WHEN utl_file.invalid_path THEN

fnd_file.put_line(fnd_File.log,'invalid path');

utl_file.fclose_all;

  WHEN utl_file.invalid_mode THEN

fnd_file.put_line(fnd_File.log,'invalid mode');

     utl_file.fclose_all;

  WHEN utl_file.invalid_filehandle THEN

   fnd_file.put_line(fnd_File.log,'invalid filehandle');

utl_file.fclose_all;

  WHEN utl_file.read_error THEN

   fnd_file.put_line(fnd_File.log,'read error');

utl_file.fclose_all;

  WHEN utl_file.internal_error THEN

   fnd_file.put_line(fnd_File.log,'internal error');

utl_file.fclose_all;

  WHEN OTHERS THEN

   fnd_file.put_line(fnd_File.log,'other error');

utl_file.fclose_all;

End Supp_interface;