Wednesday, April 23, 2014

Outbound Interface Process in Oracle


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.

Inbound Interface will be used to upload the data from legacy system (Flat files) into Oracle Applications base tables.
While Developing the Inbound interface we will use SQL * loader to import the data into base tables.

UTL_FILE Package :
==================
This is One of the PL/SQL Package which will be used to transfer the data from table to files from files to tables But when we are working for 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.
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.

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

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

CREATE OUTBOUND PROCEDURE
CREATE OR REPLACE PROCEDURE XXAA_INV_OUTBOUND
    (
      Errbuf OUT VARCHAR2
      ,Retcode OUT VARCHAR2
--    ,f_id IN NUMBER
--    ,t_id IN VARCHAR2
    )
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;
    AND msi.organization_id = 204;
   
  x_id utl_file.file_type;
  l_count NUMBER(5) DEFAULT 0;
 
BEGIN
  x_id:=utl_file.fopen('/usr/tmp', 'XXAA_INV_OUTBOUND.csv','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 XXAA_INV_OUTBOUND;

RUN ABOVE PROCEDURE SUCCESSFULLY
GOTO SYSTEM ADMINISTRATOR
http://3.bp.blogspot.com/-gx9eipnbFEc/URy0B-mBuII/AAAAAAAAABE/qAY6cApe-qA/s1600/112.jpg
Create executable as pl/SQL stored procedure
Create concurrent program and attach the executable
Attach the concurrent program to Request Group
Attach the request group to responsibility
Run the request from Responsibility


6 comments:

Anonymous said...

Good One....

Chandu Swathi said...

Can u post the Inbound Interface also..
thanks in advance..

Jonam Ramuk said...

Excellenet Article... Thanks a lot Raju

Anonymous said...

Good Job Friend. I was about to develop one critical Outbound Interface in GL, This would really help me.
God Bless you Raju.

Unknown said...

just great job

Rahul said...

how to write output into another location.

Post a Comment

Best Blogger TipsGet Flower Effect