WHT Function

create or replace function getJulyInvoicesCount(FDATE date, TODATE date,taxname varchar2,vendId number) return  number
is
rowno number;
begin
select count(*) into rowno  from(select v.invoice_date Invoice_Date,c.check_date,v.invoice_amount from
ap_checks_all c,
ap_invoice_payments_all p,
ap_invoices_all     v
where
v.invoice_id=p.invoice_id      --This function return AWT invoices which are created before or within june,
                               --but their payment are made after june of finacial year.  
and c.check_id=p.check_id
and v.invoice_date  between FDATE and TODATE
and c.check_date >=TODATE                               --c.check_date >='30-jun-12'   --or between '1-jul-12' and '30-aug-12'
and v.invoice_id in (select d.awt_invoice_id   --This Query gives us the awt invoices of vendor, from standard invoices
from
ap_invoices_all v,                             -- distribution that is relation between invoices and distribution along with
                                            --  ap_awt_tax_rates
ap_invoice_distributions_all d,
ap_awt_tax_rates_all         tr,
ap_suppliers                 s

where
v.invoice_id=d.invoice_id
and tr.tax_rate_id=d.awt_tax_rate_id
and s.vendor_id=v.vendor_id
and d.line_type_lookup_code='AWT'
and v.invoice_date between FDATE and TODATE
and v.invoice_type_lookup_code='STANDARD'
and v.payment_status_flag='Y'
and tr.tax_name=taxname
and s.segment1=vendId))r;
--dbms_output.put_line(rowno);
return rowno;
end;