ap_query

SELECT   APA.INVOICE_NUM,
         APA.INVOICE_TYPE_LOOKUP_CODE,
         APA.INVOICE_AMOUNT,
         APA.AMOUNT_PAID,
         DECODE(APA.PAYMENT_STATUS_FLAG,'Y','PAID','P','PARTIALLY PAID')STATUS
 FROM    AP_INVOICES_ALL APA,
         PO_DISTRIBUTIONS_ALL PDA
WHERE   APA.INVOICE_TYPE_LOOKUP_CODE='STANDARD'
AND     APA.PAYMENT_STATUS_FLAG IN('Y','P')



create or replace package AP_PKG as
procedure ap_proc(errbuf out varchar2,retcode out varchar2,p_fromdate in date,p_todate in date);
end ap_pkg;



create or replace package body ap_pkg as
PROCEDURE AP_PROC(errbuf OUT VARCHAR2,retcode OUT VARCHAR2,p_fromdate IN DATE,p_todate  IN DATE) AS
CURSOR c1 IS SELECT  APA.INVOICE_NUM "INVOICENUMBER",
                     APA.INVOICE_TYPE_LOOKUP_CODE "INVOICETYPE",
                     APA.INVOICE_AMOUNT "INVOICEAMOUNT",
                     APA.AMOUNT_PAID "AMOUNTPAID",
                     DECODE(APA.PAYMENT_STATUS_FLAG,'Y','PAID','P',
                    'PARTIALLYPAID') "STATUS"       
             FROM   AP_INVOICES_ALL APA
             WHERE  APA.invoice_TYPE_LOOKUP_CODE ='STANDARD'
             AND APA.payment_status_flag IN('Y','P')
             AND APA.INVOICE_DATE BETWEEN p_FROMDATE AND P_TODATE;   
BEGIN       
Fnd_File.PUT_LINE(Fnd_File.OUTPUT,'invoice from date is'||p_fromdate);
Fnd_File.PUT_LINE(Fnd_File.OUTPUT,'invoice from date is'||p_todate);
Fnd_File.PUT_LINE(Fnd_File.OUTPUT,'invoicenumber'||'~'||'invoicetype'||'~'||'invoiceamount'||'~'||
'amountpaid'||'~'||'status');
FOR I IN c1
LOOP
Fnd_File.PUT_LINE(Fnd_File.OUTPUT,I.INVOICENUMBER||I.INVOICETYPE||I.INVOICEAMOUnT||I.AMOUNTPAID||i.status);
END LOOP;
Fnd_File.PUT_LINE(Fnd_File.OUTPUT,'***end of the report***');
Fnd_File.PUT_LINE(Fnd_File.OUTPUT,'***confidential***');
END AP_PROC;
end ap_pkg;