SRW Package Usage

procedure U_1ButtonAction is
p number ;

begin
  select sum(payments) into p  from a;
SRW.MESSAGE(100, 'The Total Payments are='||p);
  srw.run_report('report=D:\ReportPractice\FinalTest.rdf');

end;

We can use SRW.RUN_REPORT in AFTER REPORT TRIGGER AS FOLLOWS
srw.run_report('module=modulename.rdf destype=screen paramform=yes maximize=yes');

u can use srw.run_report as followssrw.run_report('module=modulename.rdf destype=screen paramform=yes');

We can use SRW.RUN_REPORT in AFTER REPORT TRIGGER AS FOLLOWS
srw.run_report('module=modulename.rdf destype=screen paramform=yes maximize=yes');


you can use SRW.RUN_REPORT AS FOLLOWS
SRW.RUN_REPORT('MODULE = MODULE NAME.RDF DESTYPE=SCREEN,PARAMFORM=YES,MAXIMIZE=YES');

procedure U_1ButtonAction is
begin
srw.run_report('saledone.rdf paramform=yes
P_custname="'||:custname||'" P_salesrep="'||:salesrep||'"');
end;

Driver report : dept.rdf
Dept.rdf is running a second report called emp.rdf.

dept.rdf has an user parameter called p_1.
emp.rdf has an user parameter called p_2.

Depending upon the data types of the above user parameters, use the following
syntax.

Examples:
==========

a) p_1 and p_2 are number parameters:
srw.run_report('report=c:\orant\bin\emp.rdf batch=yes destype=file
desname=c:\temp\emp.html desformat=html p_2='||to_char(:p_1));

b) p_1 and p_2 are character parameters:

srw.run_report('report=c:\orant\bin\emp.rdf batch=yes destype=file
desname=c:\temp\emp.html desformat=html p_2='||''''||:p_1||'''');

c) p_1 and p_2 are date parameters:
srw.run_report('report=c:\orant\bin\emp.rdf batch=yes destype=file
desname=c:\temp\emp.html desformat=html p_2='||''''||to_char(:p_1)||'''');

-------------------------------------------------------------------------

srw.run_report('report=d:\ReportPractice\FinalTest.rdf batch=yes destype=file desname=d:\ReportPractice\FinalTestOutput.html desformat=html p_2='||''''||:p_1||'''');
srw.run_report('report=d:\ReportPractice\FinalTest.rdf batch=yes destype=file desname=d:\ReportPractice\FinalTestOutput.html desformat=html p_2='||''''||:ENAME||'''');
_____________________________________________________________________



srw.run_report('report=d:\ReportPractice\FinalTest.rdf batch=yes destype=file desname=d:\ReportPractice\'||:ename ||'.html desformat=html p_2='||''''||:ENAME||'''');
--p_2 is user parameter in FinalTest.rdf report and link with its as where Ename=:P_2
--:EName is regular Column passed to FinalTest.rdf report from the report who is calling srw.run_report function
--:Ename is also sent as parameter to set system paramter i.e; desname to become the name of the file having the query report in html format
-- note p_2 is of type character and Ename is also Character type.

--------------------------------------------------------------------------------------------------
srw.run_report('report=d:\ReportPractice\FinalTest.rdf  destype=screen  p_2='||''''||:ENAME||'''');

How to use Union All in ORACLE

select
           *
from

(

(select
       distinct
 --      i.prepayments_applied_flag,
--       i.prepay_flag,
--       i.prepaid_amount,
       i.vendor_name,
       i.vendor_site_code,
       i.vendor_number,
       i.invoice_type,
       i.invoice_date,
       i.invoice_num,
       i.doc_sequence_value vno,
       c.check_number,

       i.invoice_currency_code,
       i.invoice_amount,
       i.prepaid_amount,
       i.gl_date,
       c.exchange_date payment_rate_date,
       c.exchange_rate,
       c.exchange_rate_type,
       i.description
/*       (select
             abs(nvl(SUM(ael.entered_dr)-SUM(ael.entered_cr),0) )

        from
             ap_ae_lines_all ael,
             ap_ae_headers_all aeh,
             ap_invoices_all api
             ,po_vendors po

        where     aeh.ae_header_id              =     ael.ae_header_id
        and         api.invoice_id                    =      ael.reference2
        and         po.vendor_name               =       ael.reference1
        AND      ael.reference10  NOT IN ('CHARGE' , 'AWT')

        and      aeh.accounting_date <:From_date
        and                po.vendor_id=200304
        and api.set_of_books_id=1003
        and api.org_id=302
        GROUP BY  po.vendor_name,po.vendor_id
       )Opening_bal

*/
     
     
     
     
     
     
     
   
from
     ap_invoices_v i ,
     ap_checks_v c,
     ap_invoice_payments_v p


where i.invoice_id=p.INVOICE_ID(+)
and c.check_id(+)=p.CHECK_ID
and c.check_status(+)<> 'Voided'
and i.posting_flag='Y'                                                              ------this flag shows that this invoice has been accounted or not --------
--and i.invoice_num='JV-1-236'--JV-3-4'
--and i.gl_date ='06-jan-09' --06-jan-09'

--and   i.gl_date between '01-jul-08' and '30-jun-09'
and   i.gl_date between :From_Date and :To_date

--and i.vendor_name = nvl(:Sup_name,i.vendor_name)
--and i.payment_status=nvl(:inv_paid_stat,i.payment_status)
--and i.vendor_id=200304
and  to_number(i.vendor_number) between nvl(:Vendor_From,i.vendor_number) and nvl(:Vendor_To,i.vendor_number)
and i.approval_status_lookup_code<>'CANCELLED'
and i.invoice_type in ('Prepayment')
--and i.prepayments_applied_flag='Y'
and i.payment_status_flag='Y'
)

union all


(
select
       distinct
--       i.prepayments_applied_flag,
--       i.prepay_flag,
--       i.prepaid_amount,
       i.vendor_name,
       i.vendor_site_code,
       i.vendor_number,
       i.invoice_type,
       i.invoice_date,
       i.invoice_num,
       i.doc_sequence_value vno,
       c.check_number,

       i.invoice_currency_code,
       i.invoice_amount,
       i.prepaid_amount,
       i.gl_date,
       c.exchange_date payment_rate_date,
       c.exchange_rate,
       c.exchange_rate_type,
       i.description

/*
       (select
 
           abs(nvl(SUM(ael.entered_dr)-SUM(ael.entered_cr),0) )

        from
             ap_ae_lines_all ael,
             ap_ae_headers_all aeh,
             ap_invoices_all api
             ,po_vendors po

        where     aeh.ae_header_id              =     ael.ae_header_id
        and         api.invoice_id                    =      ael.reference2
        and         po.vendor_name               =       ael.reference1
        AND      ael.reference10  NOT IN ('CHARGE' , 'AWT')

        and      aeh.accounting_date <:From_date
        and                po.vendor_id=200304
        and api.set_of_books_id=1003
        and api.org_id=302
        GROUP BY  po.vendor_name,po.vendor_id
       )Opening_bal

*/  

from
     ap_invoices_v i ,
     ap_checks_v c,
     ap_invoice_payments_v p


where i.invoice_id=p.INVOICE_ID(+)
and c.check_id(+)=p.CHECK_ID
and c.check_status(+)<> 'Voided'
and i.posting_flag='Y'                                                              ------this flag shows that this invoice has been accounted or not --------
--and   i.gl_date between '01-jul-08' and '30-jun-09'
and   i.gl_date between :From_Date and :To_date
--and i.vendor_id=200304
and  to_number(i.vendor_number) between nvl(:Vendor_From,i.vendor_number) and nvl(:Vendor_To,i.vendor_number)
and i.approval_status_lookup_code<>'CANCELLED'
and i.invoice_type in ('Standard')
and i.prepayments_applied_flag='Y'
and i.payment_status_flag in ('Y', 'P' )   --' P for partial '
--order by gl_date
)

) a
order by a.invoice_type,a.gl_date,a.invoice_date

Oracle Site for Learning Oracle Payable and Invoices

The best link i found on web of Oracle Site  for Learning  Oracle Payable and Invoices

http://docs.oracle.com/cd/E18727_01/doc.121/e12797/toc.htm

How to Query with condition in Oracle Report Database

select
i.vendor_name,
i.vendor_number,
i.invoice_amount,
nvl(i.invoice_amount,0)-nvl(i.amount_paid,0)+ nvl(i.amount_withheld,0)  OpeningBalancePrepaid,
i.invoice_type,
i.vendor_site_code, i.invoice_date,
i.invoice_num,
i.invoice_currency_code,
i.invoice_amount,
i.prepay_amount_applied,
i.gl_date,
i.doc_sequence_value vno,
c.check_number,
p.description

from
ap_invoices_v i,
ap_checks_v c,
ap_invoice_payments_v p

where
i.vendor_id=c.vendor_id
and
i.invoice_id=p.invoice_id
and
c.check_number=p.check_number
and
rownum<=10;

how Query a Database in Oracle Report

select     i.vendor_name, i.vendor_site_code,  i.vendor_number,   i.invoice_type,   i.invoice_date,

       i.invoice_num,      i.invoice_currency_code,   i.invoice_amount,

       nvl(i.amount_paid,0)+ nvl(i.amount_withheld,0) paid_amount,

       nvl(i.invoice_amount,0)-(nvl(i.amount_paid,0)+ nvl(i.amount_withheld,0)) balance,

      --  sum(p.AMOUNT)+sum(nvl(i.amount_withheld,0))+nvl(i.prepaid_amount,0) paid_amount,

 -- nvl(i.invoice_amount,0) -nvl(sum(p.AMOUNT)+sum(nvl(i.amount_withheld,0))+nvl(i.prepaid_amount,0),0) balance,

       --sum(P.AMOUNT) check_amount,

       i.prepaid_amount,

       I.amount_withheld,

       i.gl_date,

       --c.exchange_date payment_rate_date,

       i.description,

       i.doc_sequence_value vno

from

     ap_invoices_v i

where

   i.gl_date between :From_Date and :To_date

and  to_number(i.vendor_number) between nvl(:Vendor_From,i.vendor_number) and nvl(:Vendor_To,i.vendor_number)

Sample Report in ORacle how to build

How to Make Sample Group Above Report in Oracle

Example of Usage of PlaceHolder Column

function CF_Cal_MaxPaymentFormula return Number is
begin
  if :Payments=:CS_Max_Pay then
  :CP_MAX_Payer:= :vendor_name;
  end if;
  return (1);
end;

--take CF_CalMaxPaymentFormul as group Level in group
--CP_MAX_Payer is a report level out side the group placeholder column with data type Character 80
--CS_Max_Pay is a report level Summary Column having maxiumn Payments using max function

Clear Example of Group above Report in Oracle

Exampe of Group Above Report Format

Example of Group above Report in Oralcle Layout Only