Outbound Interface in Oracle Apps

OUTBOUND INTERFACE PROCESS IN ORACLE APPS

Outbound Interface Process:

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.

UTL_FILE Package:

===============

 This is one of the PL/SQL package which will be used to transfer the data from the table to files as 

 well as from files to tables,But when we are working for the file to table we will use SQL*Loader to transfer from table to file we have no alternative  we have to use UTL_FILE.

we will use following three functions to generate the file.

• utl_file.fopen       =    To Open (or) Create the file
• utl_file.put_line   =    To Transfer the data into the file
• utl_file.fclose       =    To Close the file after the Data Transfer

Outbound Interface process:

=====================

1. Develop the PL/SQL program (Either Procedure or Package)
2. Write the Cursor to retrieve the data from data base 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 Program or Package as Concurrent Program and submit from SRS window

Sample Query:

==========

create or replace procedure Outbound_Interface

( errbuf     OUT      VARCHAR2,

  retcode     OUT      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=204;

x_id utl_file.file_type;

l_count number default 0;

begin

x_id:=utl_file.fopen('/usr/tmp','Outbound_data.csv','W');

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 :=' || l_count);

   fnd_file.put_line(fnd_file.output

                    ,    'USER NAME :='

                      || fnd_profile.VALUE(        --SELECT * FROM FND_PROFILE

                                           'USERNAME')

                    );

   fnd_file.put_line(fnd_file.output

                    , 'RESPONSIBILITY :=' || fnd_profile.VALUE('RESP_NAME')

                    );

   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');

   WHEN UTL_FILE.invalid_path

   THEN

      fnd_file.put_line(fnd_file.LOG, 'INVALID PATH');

   WHEN UTL_FILE.invalid_mode

   THEN

      fnd_file.put_line(fnd_file.LOG, 'INVALID MODE');

   WHEN UTL_FILE.invalid_filehandle

   THEN

      fnd_file.put_line(fnd_file.LOG, 'INVALID FILEHANDLE');

   WHEN UTL_FILE.read_error

   THEN

      fnd_file.put_line(fnd_file.LOG, 'READ_ERROR');

   WHEN UTL_FILE.internal_error

   THEN

      fnd_file.put_line(fnd_file.LOG, 'INTERNAL_ERROR ');

        UTL_FILE.fclose_all;

end Outbound_Interface;

/

Example:

=======

TYPE : select * from v$parameter where name like '%utl_file%'

Copy any Path in the above query result for outbound file destination

we will use this path in Procedure

NOTE : Only above Query Paths are suitable for Outbound files.

Run the Above Procedure successfully and submit the Concurrent Program in SRS window.