Tuesday, January 10, 2017

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;

No comments:

Post a Comment

Oracle Fusion - Cost Lines and Expenditure Item link in Projects

SELECT   ccd.transaction_id,ex.expenditure_item_id,cacat.serial_number FROM fusion.CST_INV_TRANSACTIONS cit,   fusion.cst_cost_distribution_...