Friday, 16 November 2012

EXAMPLE Excluding the Tax Invoices of Particular vendor form AP_Invoices_ALL Table using the following Query.


SELECT
v.vendor_id,
v.invoice_id,
v.invoice_amount,
idv.amount*awt.tax_rate/100 tax_amount
--c.check_id  cid,
--c.amount,  
--p.check_id pid,
--p.amount   pamt
FROM
Ap_Invoices_All             v,
AP_INVOICE_DISTRIBUTIONS_V  idv,
AP_AWT_TAX_RATES_ALL        awt
--Ap_checks_all               c,
--Ap_Invoice_Payments_All     p
WHERE
--c.check_id=p.check_id
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='1'
and v.invoice_type_lookup_code='AWT'
and to_number(trim(substr(v.invoice_num,19,6)),'999999') in(SELECT v.invoice_id FROM
Ap_Invoices_All v
WHERE
v.invoice_date between '1-jul-2009' and '30-July-2010'
and v.vendor_id='0000'
and v.invoice_type_lookup_code='STANDARD'
)

No comments:

Post a Comment