Saturday, December 3, 2016

Outbound Interface in oracle apps using 'utl_file_dir'

Outbound using UTL FILE Concept


select * from v$parameter where name = 'utl_file_dir'

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

Click on executable




CLICK ON PERAMETERS BUTTON


SAVE AND COLSE IT THEN GOTO BELOW NAVIGATION














GOTO C/TEMP PATH WE WILL GET FILE


No comments:

Post a Comment

Types of animations

Animation is a diverse and creative field with a variety of styles that artists and filmmakers use to bring their visions to life. Here are ...