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 :
~~~~~~
DECLARECURSOR 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;
