Outbound Interface

Outbound Interface will be used to extract the data from oracle Database tables into the flat files.

when we are aiming for file to table data transfer(inbound), we will use SQL *Loader, otherwise we use UTL_FILE procedure to transfer from table data to a file (outbound).

Functions to generate the file

1. Declare the file pointer to find out file location - UTL_FILE.FILE_TYPE;

2. To open or create file - UTL_FILE.FOPEN(‘PATH’,’FILE NAME’,’W’);

3. To write data into file - UTL_FILE.PUT_LINE(FILE POINTER, COLUMN NAME);

4. To close the file - UTL_FILE.FCLOSE(FILE POINTER);

Outbound Interface Process:

1. Develop the PL/SQL program (Procedure or Package).

2. Define the cursor to get the data from the database tables.

3. Open the cursor and open the file by using the UTL_FILE Package(utl_file.fopen()).

4. If any validations, write the validations.

5. Transfer the data from oracle base to flat files by using the UTL_FILE (utl_file.put_line()).

6. Close the file by using the UTL_FILE Package(utl_file.fclose()).

6. Register the PL/SQL program as concurrent program.

7. This program we can run in required responsibility.

To Find UTL Path:

SELECT * FROM V$PARAMETER WHERE UPPER(NAME) LIKE '%UTL%';

select substr(value,1,14)path from v$parameter where name like 'utl%';

Select From List of UTL Paths:

/usr/tmp,

/oracle/DEV/db/tech_st/12.1.03/appsutil/outbound/DEV_seaerpdev,

/oracle/DEV/inst/apps/DEV_seaerpdev/logs/appl/conc/log,

/oracle/DEV/inst/apps/DEV_seaerpdev/logs/appl/conc/out,

/oracle/DEV/apps/apps_st/appl/SFDC/sfdc_ebs,

/oracle/DEV/apps/apps_st/appl/SFDC/in/CanceledSalesOrders/CSOL

Sample Table Query to Get Output :

Sample procedure structure

Simple Outbound Interface

CREATE OR REPLACE PROCEDURE RTL_SUPP_Out1(Errbuf OUT varchar2, 
                                         Retcode OUT varchar2 
                                         ) AS 
cursor cur_suppliers is select 
               AS1.SEGMENT1, 
               AS1.VENDOR_NAME, 
               AS1.CREATION_DATE, 
               ASS1.VENDOR_SITE_CODE 
             FROM AP_SUPPLIERS AS1, 
                  AP_SUPPLIER_SITES_ALL ASS1 
             WHERE AS1.VENDOR_ID = ASS1.VENDOR_ID; 
l_count number(9) default 0; 
x_id     utl_file.file_type;    -- Declare to get the information about the File. 
BEGIN
x_id:=utl_file.fopen('/oraDB/oracle/VIS/db/tech_st/11.1.0/appsutil/outbound/VIS_glo','Suppliers.dat','W');        
--SELECT * FROM V$PARAMETER WHERE name like '%utl_file%' 
--init.ora file 

for x1 in cur_suppliers loop 
    l_count:=l_count+1; 
    utl_file.put_line(x_id,x1.SEGMENT1    ||'$'|| 
        x1.VENDOR_NAME  ||'$'|| 
        x1.CREATION_DATE||'$'|| 
        x1.VENDOR_SITE_CODE   ||'$'|| 
    ); 
end loop; 

UTL_file.fclose(x_id); 
Fnd_file.Put_line(Fnd_file.output,'No of Records transferred to the data file :'||l_count); 
End;

Outbound Interface with Parameters

CREATE OR REPLACE PROCEDURE ITEMS_OUTBOUND(Errbuf OUT varchar2,
        Retcode ouT varchar2,
        f_id    in number,
        t_id    in number) AS       

cursor c1 is select msi.segment1 item,
    msi.inventory_item_id Itemid,
    msi.description  itemdesc,
    msi.primary_uom_code Uom,
    ood.organization_name name,
    ood.organization_id   id,
    mc . segment1||','||mc.segment2 Category
from
    mtl_system_items_b           msi,
    org_organization_definitions ood,    
    mtl_item_categories          mic,    
    mtl_categories               mc
where
    msi.organization_id       = ood.organization_id
    and msi.inventory_item_id = mic.inventory_item_id
    and msi.organization_id   = mic.organization_id
    and mic.category_id       = mc.category_id
    and msi.purchasing_item_flag = 'Y'
    and msi.organization_id between f_id and t_id;
x_id     utl_file.file_type;
l_count  number(5) default 0;

BEGIN
x_id:=utl_file.fopen('/oraDB/oracle/VIS/db/tech_st/11.1.0/appsutil/outbound/VIS_glo','ITEMS_DATA.dat','W');
-- select * from v$parameter where name like '%utl_file%'

for x1 in c1 loop
    l_count:=l_count+1;
    utl_file.put_line(x_id,x1.item    ||'^^'||
    x1.itemid  ||'^^'||
    x1.itemdesc||'^^'||
    x1.uom   ||'^^'||
    x1.name   ||'^^'||
    x1.id   ||'^^'||
    x1.category   );
end loop;

utl_file.fclose(x_id);
Fnd_file.Put_line(Fnd_file.output,'No of Records transfered to the data file :'||l_count);
Fnd_File.Put_line(fnd_File.Output,' ');
Fnd_File.Put_line(fnd_File.Output,'Submitted User name            = '||Fnd_Profile.Value('USERNAME'));
Fnd_File.Put_line(fnd_File.Output,' ');
Fnd_File.Put_line(fnd_File.Output,'Submitted Responsibility name  = '||Fnd_profile.value('RESP_NAME'));
Fnd_File.Put_line(fnd_File.Output,' ');
Fnd_File.Put_line(fnd_File.Output,'Submission Date                = '||SYSDATE);

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;

Other Important topics

UTL_FILE EXCEPTIONS :

• Invalid_path

• Invalid_Mode

• Invalid_Operations

• Invalid_filehandle

• Invalid_file_name

• Read_error

• Write_error

Executable :

Concurrent program :

Add the concurrent program to the required request group

Submit the program and then check in the flat file generated in the file directory or not.