WHTSTATEMETQ

select
c.check_number ,
c.check_date Payment_Date,
c.amount     Check_Amount,
c.bank_account_name,
c.doc_sequence_value BPV_NO,
pov.VENDOR_NAME_ALT,
pov.VAT_REGISTRATION_NUM  TAXFILE_NUM,

(select vv.description  from ap_invoices_all vv where vv.invoice_id
in(d.awt_invoice_id)
) WHT_INOVICE_DESCRIPTION,


(select vv.invoice_num  from ap_invoices_all vv where vv.invoice_id
in(d.awt_invoice_id)
) WHT_INOVICE_NUM,

v.invoice_id,
v.invoice_num,
v.invoice_date,
c.vendor_name,
c.address_line1 Vendor_Address,
pov.STANDARD_INDUSTRY_CLASS Vendor_NIC,
pov.NUM_1099 Vendor_NTN,
substr(tr.comments,1,instr(tr.comments,'-')-1) UNDER_SECTION,
substr(tr.comments,instr(tr.comments,'-')+1)   NATURE ,
v.invoice_amount,
tr.tax_rate,
v.invoice_amount-v.amount_paid TAX_AMOUNT,
v.amount_paid

--d.invoice_line_number,
--d.line_type_lookup_code,
--d.amount,

from
ap_invoices_all v,
ap_invoice_distributions_all d,
ap_awt_tax_rates_all tr,
po_vendors  pov,
ap_checks_all c,
ap_invoice_payments_all p

where
v.invoice_id=d.invoice_id
and tr.tax_rate_id=d.awt_tax_rate_id
and v.vendor_id=pov.VENDOR_ID
and p.invoice_id=v.invoice_id
and c.check_id=p.check_id
and pov.SEGMENT1=&Vendor_NO
and  d.line_type_lookup_code='AWT'
and nvl(d.reversal_flag,'N')='N'
and nvl(p.reversal_flag,'N')='N'
and c.check_date between &FDate and &TDate
-----------------------------------------------------------------------------------------------------------

selectc.doc_sequence_value BPV_NO,
v.invoice_id, 
v.invoice_num,
v.description,
' ' "WHT_INOVICE_NUM",
pov.vendor_name,
pov.vendor_name_alt,
c.address_line1 Vendor_Address,
c.city,
pov.num_1099 NTN,
pov.standard_industry_class NIC,
pov.vat_registration_num SaleTAX_REG_No,
' ' "Nature", 
 ' ' "SEC",
to_char(c.check_date, 'yyyymmdd') Payment_Date,
v.invoice_amount,
' ' "TAX_RATE",
 0 TAX_AMOUNT,
 sum(p.amount) AMOUNT_PAID,
to_char(c.check_date, 'yyyymmdd') Check_Date,
c.check_number,
p.reversal_flag
from 
ap_checks_all c,
ap_invoices_all v,
ap_invoice_payments_all p,
po_vendors pov
where 
c.check_id=p.check_id
and v.invoice_id=p.invoice_id
 and pov.vendor_id=v.vendor_id
 and pov.SEGMENT1=&VendorNo
 and c.check_date between &FromDate and &ToDate
 and p.reversal_flag='N'
 
and v.invoice_amount=v.amount_paid
 and v.cancelled_date is null
 
Group by v.invoice_id,c.doc_sequence_value,v.invoice_num,pov.vendor_name,pov.vendor_name_alt,c.city,
pov.num_1099,pov.standard_industry_class,pov.vat_registration_num,c.check_date,v.invoice_amount,c.check_number,v.description,c.address_line1,p.reversal_flagUNION ALL
 select 
r.BPV_NO,
r.invoice_id,
r.invoice_num,
r.WHT_INOVICE_DESCRIPTION,
r.WHT_INOVICE_NUM,
r.Vendor_Name,
r.VENDOR_NAME_ALT, 
r.Vendor_Address,
r.City,
r.Vendor_NTN,
r.Vendor_NIC,
r.TAXFILE_NUM,
r.NATURE,
r.UNDER_SECTION,
to_char(r.Payment_Date,'yyyymmdd') Payment_Date,
r.INVOICE_AMOUNT,
r.TAX_RATE,
r.TAX_AMOUNT,
r.AMOUNT_PAID,
to_char(r.Check_Date,'yyyymmdd')Check_Date,
r.check_No,
r.Payment_Reversal_Flagfrom(
 select 
　
(select cc.doc_sequence_value from ap_checks_all cc where cc.check_id in( 
(select pp.check_id from ap_invoice_payments_all pp where pp.invoice_payment_id
 in(d.awt_invoice_payment_id))))BPV_NO, 
 --v.invoice_id ,--v.invoice_num,--(select vv.description from ap_invoices_all vv where vv.invoice_id
 in(d.awt_invoice_id)
) WHT_INOVICE_DESCRIPTION,--(select vv.invoice_num from ap_invoices_all vv where vv.invoice_id
 in(d.awt_invoice_id)
) WHT_INOVICE_NUM,--(select cc.vendor_name from ap_checks_all cc where cc.check_id in( 
(select pp.check_id from ap_invoice_payments_all pp where pp.invoice_payment_id
 in(d.awt_invoice_payment_id))))Vendor_Name,
 --pov.VENDOR_NAME,pov.VENDOR_NAME_ALT,--(select cc.address_line1 from ap_checks_all cc where cc.check_id in( 
(select pp.check_id from ap_invoice_payments_all pp where pp.invoice_payment_id
 in(d.awt_invoice_payment_id))))Vendor_Address, 
 --(select cc.city from ap_checks_all cc where cc.check_id in( 
(select pp.check_id from ap_invoice_payments_all pp where pp.invoice_payment_id
 in(d.awt_invoice_payment_id))))City, 
 ---pov.NUM_1099 Vendor_NTN,
pov.STANDARD_INDUSTRY_CLASS Vendor_NIC,
pov.VAT_REGISTRATION_NUM TAXFILE_NUM,
substr(tr.comments,instr(tr.comments,'-')+1) NATURE ,
substr(tr.comments,1,instr(tr.comments,'-')-1) UNDER_SECTION,
 --(select cc.check_date from ap_checks_all cc where cc.check_id in( 
(select pp.check_id from ap_invoice_payments_all pp where pp.invoice_payment_id
 in(d.awt_invoice_payment_id))))Payment_Date, 
 ---1*d.amount/(tr.tax_rate/100) INVOICE_AMOUNT,
to_char(tr.tax_rate) TAX_RATE,--v.invoice_amount-v.amount_paid TAX_AMOUNT,to_number(-1*d.amount) TAX_AMOUNT,
 --(select pp.amount from ap_invoice_payments_all pp where pp.invoice_payment_id
 in(d.awt_invoice_payment_id)
) AMOUNT_PAID, --Payment of AWT_Inovice
-- 　
(select cc.check_date from ap_checks_all cc where cc.check_id in( 
(select pp.check_id from ap_invoice_payments_all pp where pp.invoice_payment_id
 in(d.awt_invoice_payment_id))))Check_Date,
 --(select cc.check_number from ap_checks_all cc where cc.check_id in( 
(select pp.check_id from ap_invoice_payments_all pp where pp.invoice_payment_id
 in(d.awt_invoice_payment_id))))check_No,
　
　
(select pp.reversal_flag from ap_invoice_payments_all pp where pp.invoice_payment_id
 in(d.awt_invoice_payment_id))Payment_Reversal_Flag 
 --to_char(c.check_date, 'yyyymmdd'), fromap_invoices_all v,
ap_invoice_distributions_all d,
ap_awt_tax_rates_all tr,
po_vendors povwhere 
v.invoice_id=d.invoice_idandtr.tax_rate_id=d.awt_tax_rate_id
 andv.vendor_id=pov.VENDOR_ID
 andpov.SEGMENT1=&VendorNo
 --and tr.tax_name=&Tax_Name --comment for tax exemptionandd.line_type_lookup_code='AWT' 
 andnvl(d.reversal_flag,'N')='N'
 )rwhereCheck_Date between &FromDate and &ToDate
 andnvl(Payment_Reversal_Flag,'N') ='N'

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;