Oracle Apps Outbound Interface Process - part1

Oracle Apps Outbound Interface Process

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 GE_INV_Out_BAL(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;

x_id     utl_file.file_type;

l_count  number(5) default 0;

begin

x_id:=utl_file.fopen('c:\temp','invoutdata.txt','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 GE_INV_Out_BAL;

RUN ABOVE PROCEDURE SUCCESSFULLY

GOTO SYSTEM ADMINISTRATOR

Part1                                         Part2                                             Part3                                     Part4