Procedure That will return 4 Variables Example

Procedure That will take vendor id from calling environment and return 4 other variables like V_invamt,V_taxamt,V_vname,V_vntn to the calling environment.

create or replace procedure PrintInvoiceInfo(V_venid in number, V_invamt out number,V_taxamt out number,V_vname out varchar2,V_vntn out varchar2)

is

begin

SELECT sum(v.invoice_amount),sum(idv.amount*awt.tax_rate/100),pov.vendor_name,pov.num_1099 into V_invamt,V_taxamt,V_vname,V_vntn

       FROM

       Ap_Invoices_All v,

       AP_INVOICE_DISTRIBUTIONS_V idv,

       AP_AWT_TAX_RATES_ALL        awt,

       Po_Vendors                  pov

       WHERE

       awt.tax_name(+)=idv.awt_group_name

       and v.invoice_id=idv.invoice_id

       and pov.vendor_id=v.vendor_id

       and v.invoice_date between '1-jul-2009' and '30-Jun-2010'

       and v.vendor_id=V_venid

       and v.invoice_type_lookup_code='STANDARD'

       group by v.vendor_id,pov.vendor_name,pov.num_1099;

end PrintInvoiceInfo;

Test window-  The Calling Environment

declare

invamt number;

taxamt number;

vname  varchar2(30);

vntn   varchar2(10);

begin

dbms_application_info.set_client_info(000);

PrintInvoiceInfo(0000,invamt,taxamt,vname,vntn);

dbms_output.put_line(invamt||taxamt||vname||vntn);

end;