Outbound Interface in oracle apps

Outbound Interface in oracle apps

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

Example :

~~~~~~

DECLARE
  CURSOR C1
  IS
    SELECT r.customer_name,
      h.cust_po_number ,
      h.order_number,
      h.creation_date
    FROM oe_order_headers_all h,
      ra_customers r
    WHERE h.sold_to_org_id = r.customer_id
      --and to_char(h.creation_date,'MMDDYYYY') LIKE SYSDATE
    AND r.customer_number IN ('5656')
    ORDER BY h.creation_date DESC;
  X_ID utl_file.file_type;
  file_name VARCHAR2(30);
BEGIN
  SELECT TO_CHAR(sysdate,'YYYYMMDD')
    ||'_'
    ||ORDER_UPLOAD.nextval
    ||'.'
    ||'xls'
  INTO file_name
  FROM dual;
  X_ID :=UTL_FILE.FOPEN('/usr/tmp/TEST',file_name,'W'); 
  FOR i IN c1
  LOOP
    utl_file.put_line(X_ID,i.customer_name||'      '||i.cust_po_number||'     '||i.order_number||'    '||i.creation_date);
  END LOOP;
  UTL_FILE.fclose(X_ID);
END;