PHP code:



---厂商应负帐款余额报表


set termout  on


set verify   off


set heading  on


set feedback off


set linesize 200


set pagesize 58


set newpage 0


set space 1


set recsep off








COL tmp_name NOPRINT NEW_VALUE list_name


SELECT 'KK'||USER||TO_CHAR(tempfile_s.nextval) tmp_name


FROM dual;








column today            noprint new_val         datevar


column vendor_name        form A26 wrap    head 'Vendor Name'


column currency_code        form A3        head 'Cur'


column bal_amount          form $99,999,999,999.99 head 'Balance Amount'








def acc_date = '&1';


def org_id = '&2';











select trunc(sysdate) today from dual;








CREATE TABLE &list_name (


        vendor_name     varchar2(100),


        currency_code   varchar2(100),


        inv_base_amount number,


        inv_amount      number,


        pmt_base_amount number,


        pmt_amount      number,


        bal_amount      number,


    invoice_num     varchar2(100)


       ) TABLESPACE temp ;





COL tmp_name2 NOPRINT NEW_VALUE list_name2


SELECT 'WK'||USER||TO_CHAR(tempfile_s.nextval) tmp_name2


FROM dual;





CREATE TABLE &list_name2 (


        vendor_name     varchar2(80),


        currency_code   varchar2(15),


        inv_base_amount number,


        inv_amount      number,


        pmt_base_amount number,


        pmt_amount      number,


        bal_amount      number,


        o_bal_amount    number


       ) TABLESPACE temp ;





declare


    cursor cu_02 is


select max(v.vendor_name) vendor_name,


       max(i.invoice_currency_code) currency_code,


       max(i.invoice_id) invoice_id,


       max(i.invoice_num) invoice_num,


       sum(nvl(id.base_amount, id.amount)) inv_base_amount,


       sum(id.amount) inv_amount


from   po_vendors v,


       ap_invoices_all i,


       ap_invoice_distributions_all id


where  v.vendor_id            = i.vendor_id


and    i.invoice_id           = id.invoice_id


and    id.accounting_date    <= '&acc_date'


and    id.org_id  =decode('&org_id','1','551','2','552')   


group by v.vendor_name,i.invoice_currency_code,i.invoice_id


;





     pcu_02 cu_02%rowtype;








    cursor cu_021 is


select sum(round(ip.amount * nvl(i.exchange_rate,1),0)) pmt_base_amount,


       sum(ip.amount) pmt_amount


from   ap_invoice_payments_all ip,


       ap_invoices_all i


where  ip.invoice_id     = pcu_02.invoice_id


       and i.invoice_id     = pcu_02.invoice_id


       and ip.accounting_date    <= '&acc_date'


;


     pcu_021 cu_021%rowtype;








    cursor cu_03 is


select  vendor_name vendor_name,


        currency_code currency_code ,


        sum(inv_base_amount) inv_base_amount,


        sum(inv_amount) inv_amount,


        sum(pmt_base_amount) pmt_base_amount,


        sum(pmt_amount) pmt_amount,


        sum(bal_amount) bal_amount


from &list_name


group by vendor_name,currency_code


;


     pcu_03 cu_03%rowtype;








begin


     open cu_02;


     loop


        fetch cu_02 into pcu_02;


        exit when cu_02%NOTFOUND;


        open cu_021;


        fetch cu_021 into pcu_021;


        close cu_021;


        insert into &list_name values (


            pcu_02.vendor_name,


            pcu_02.currency_code,


            pcu_02.inv_base_amount,


            pcu_02.inv_amount,


            pcu_021.pmt_base_amount,


            pcu_021.pmt_amount,


            pcu_02.inv_base_amount - nvl(pcu_021.pmt_base_amount,0),


        pcu_02.invoice_num


        );


        commit;


     end loop;


     close cu_02;





     open cu_03;


     loop


        fetch cu_03 into pcu_03;


        exit when cu_03%NOTFOUND;


        insert into &list_name2 values (


            pcu_03.vendor_name,


            pcu_03.currency_code,


            pcu_03.inv_base_amount,


            pcu_03.inv_amount,


            pcu_03.pmt_base_amount,


            pcu_03.pmt_amount,


            pcu_03.bal_amount,


        pcu_03.inv_amount - pcu_03.pmt_amount


        );


        commit;


     end loop;


     close cu_03;


end;


/





break on report 


compute sum of  bal_amount  on report





TTITLE '~x0;'-


       left 'Date: ' datevar -


       center 'Vendor Balance Report As of '&&1  -           


       right '页码:'  FORMAT 999 SQL.PNO -


       skip 3       








select  vendor_name vendor_name,


        currency_code currency_code ,      


        bal_amount bal_amount,    


        TO_CHAR(SYSDATE, 'DD-MON-YY HH24:MI') today


from &list_name2


where bal_amount <> 0


/





drop table &list_name;


drop table &list_name2;





clear columns


set termout  on


set verify   on


set heading  off


set feedback on


set linesize 80


set