AR Outbound Interface code

create or replace package aroutpkg is
procedure aroutproc(errbuf out varchar2,retcode out varchar2);
end aroutpkg;


create or replace package body aroutpkg as
procedure aroutproc(errbuf out varchar2,retcode out varchar2) is
cursor c1 is
SELECT rcta.trx_number invoice_num,
       rcta.trx_date invoice_date,
       acra.AMOUNT amt,
       acra.RECEIPT_NUMBER receipt_num,
       ACRA.STATUS STATUS,
       RCTGDA.GL_POSTED_DATE GL_POSTING_DATE
FROM RA_CUSTOMER_TRX_ALL RCTA,
     RA_CUSTOMER_TRX_LINES_ALL RCTLA,
     RA_CUST_TRX_LINE_GL_DIST_ALL RCTGDA,
     AR_PAYMENT_SCHEDULES_ALL APSA,
     AR_CASH_RECEIPTS_ALL ACRA,
     AR_RECEIVABLE_APPLICATIONS_ALL ARAA,
     RA_CUSTOMERS RC   
WHERE RCTA.CUSTOMER_TRX_ID=RCTLA.CUSTOMER_TRX_ID
AND   RCTLA.CUSTOMER_TRX_LINE_ID=RCTGDA.CUSTOMER_TRX_LINE_ID
AND      RCTA.CUSTOMER_TRX_ID=APSA.CUSTOMER_TRX_ID
AND  araa.APPLIED_PAYMENT_SCHEDULE_ID=apsa.PAYMENT_SCHEDULE_ID
and     araa.APPLIED_CUSTOMER_TRX_ID=rcta.CUSTOMER_TRX_ID
AND   ACRA.CASH_RECEIPT_ID=ARAA.CASH_RECEIPT_ID
and      rc.CUSTOMER_ID=rcta.SOLD_TO_CUSTOMER_ID
AND  RCTGDA.GL_POSTED_DATE IS NOT NULL
and  acra.STATUS='APP'
GROUP BY rcta.trx_number,
       rcta.trx_date,
       acra.AMOUNT,
       acra.RECEIPT_NUMBER,
       ACRA.STATUS,
       RCTGDA.GL_POSTED_DATE;
ln_count number;
id1 utl_file.file_type;
begin
id1 := utl_file.fopen('d:\oracle\visdb\9.2.0\plsql\temp','arout.txt','w');
for i in c1
loop
ln_count :=ln_count+1;
utl_file.put_line(id1,i.invoice_num||'~'||i.invoice_date||'~'||i.amt||'~'||i.receipt_num||'~'||i.status||'~'||i.gl_posting_date);
end loop;
utl_file.fclose(id1);
if ln_count=0 then
fnd_file.put_line(fnd_file.log,'*****************NO DATA FOUND***********************');
end if;
exception
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 others then
fnd_file.put_line(fnd_file.log,'INVALID ERROR');
utl_file.fclose_all;
end aroutproc;
end aroutpkg;