Tuesday 27 November 2012

How to define Cursor with report


DECLARE
    V_venid  ap_invoices_all.vendor_id%type;
    V_invid   ap_invoices_all.invoice_id%type;
    V_invamt  ap_invoices_all.invoice_amount%type;
    V_taxamt  number;
   
   
    CURSOR StdInv_cursor IS  
    SELECT v.vendor_id,sum(v.invoice_amount),sum(idv.amount*awt.tax_rate/100)
       FROM
       Ap_Invoices_All v,
       AP_INVOICE_DISTRIBUTIONS_V idv,
       AP_AWT_TAX_RATES_ALL        awt
       WHERE
       awt.tax_name(+)=idv.awt_group_name
       and v.invoice_id=idv.invoice_id
       and v.invoice_date between '1-jul-2009' and '30-Jun-2010'
       and v.vendor_id=0000
       and v.invoice_type_lookup_code='STANDARD'
       group by v.vendor_id;
   
 BEGIN
 OPEN StdInv_cursor;
          loop
          FETCH StdInv_cursor INTO V_venid,V_invamt,V_taxamt;
          exit when StdInv_cursor%NOTFound;
          dbms_output.put_line (V_venid||'    '||'      '||V_invamt||'     '||V_taxamt);
          END Loop;
   CLOSE StdInv_cursor;
 END;

No comments:

Post a Comment