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;
