SELECT glp.period_num
--,count(distinct abc.vendor_id4) vendor_count
,count(distinct abc.taxpayer_id1) vendor_count
,NVL(sum(abc.gamount),0) gamount
,decode(sign(nvl(sum(abc.gawt),0)), -1, '-', '0') || substr(to_char(round(sum(abc.gawt)), '00000000000D00'), 2, 11) gawt
,decode(sign(nvl(sum(abc.gawt),0)- nvl(sum(abc.dawt),0)), -1, '-', '0') || nvl(substr(to_char(round(sum(abc.gawt) - sum(abc.dawt)), '00000000000D00'), 2, 11),'000000000000') gdawt
,NVL(sum(abc.damount),0) damount
,NVL(sum(abc.dawt),0) dawt
,round(NVL(sum(abc.gamount),0) - NVL(sum(abc.damount),0)) gamount3
FROM
(SELECT e.vendor_site_id3 vendor_site_id4
,e.vendor_id3 vendor_id4
,sum(e.gross_amount) gamount
,sum(e.awt_amount) gawt
,sum(e.div_amount) damount
,sum(e.div_awt) dawt
,e.period
,e.taxpayer_id1 taxpayer_id1
FROM (
SELECT
d.vendor_site_id3 vendor_site_id3
,d.vendor_id3 vendor_id3
,(CASE WHEN d.gamount_filter > 0 THEN d.gross_amount ELSE 0 END) gross_amount
,(CASE WHEN d.gawt_filter > 0 THEN d.awt_amount ELSE 0 END) awt_amount
,d.div_amount div_amount
,d.div_awt div_awt
,d.period
,d.taxpayer_id1 taxpayer_id1
FROM
(SELECT
c.vendor_site_id3 vendor_site_id3
,c.vendor_id3 vendor_id3
,c.gross_amount gross_amount
,c.awt_amount awt_amount
,Sum(c.gross_amount) over (Partition By c.Vendor_Site_ID3) gamount_filter
,Sum(c.awt_amount) over (Partition By c.Vendor_Site_ID3) gawt_filter
,c.div_amount div_amount
,c.div_awt div_awt
,c.period
,c.taxpayer_id1 taxpayer_id1
FROM (
Select a.* From (
SELECT distinct
ac.check_id Check_ID,
ac.vendor_id vendor_id3,
ac.vendor_site_id Vendor_Site_ID3,
&p_tax_payerid_cond taxpayer_id1,
ac.check_number Check_Number,
ac.check_date Check_Date,
JE_IL_TAX_PKG.get_gross_amount(ai.invoice_id,aip.check_id,:P_START_DATE,:P_END_DATE,aip.reversal_inv_pmt_id) gross_amount,
JE_IL_TAX_PKG.get_awt_amt(Ai.Invoice_Id,Aip.Check_Id,:P_START_DATE,:P_END_DATE,aip.reversal_inv_pmt_id) Awt_Amount,
JE_IL_TAX_PKG.get_div_amt(Ai.Invoice_Id,Aip.Check_Id,:P_START_DATE,:P_END_DATE,aip.reversal_inv_pmt_id) Div_Amount,
JE_IL_TAX_PKG.get_div_awt(Ai.Invoice_Id,Aip.Check_Id,:P_START_DATE,:P_END_DATE,aip.reversal_inv_pmt_id) Div_Awt,
ibyp.payment_method_name Payment_Type,
to_char(aip.accounting_date,'MM') period,
row_number( ) over (partition by aip.invoice_id,aip.check_id,aip.accounting_event_id order by aip.invoice_id,aip.check_id,aip.accounting_event_id) SLNO
FROM
ap_invoices_all ai,
ap_checks_all ac,
ap_invoice_payments_all aip,
iby_payment_methods_vl ibyp,
po_vendors pvend,
po_vendor_sites_all pvs,
(SELECT distinct person_id
,national_identifier
FROM PER_ALL_PEOPLE_F WHERE trunc(sysdate) BETWEEN effective_start_date AND effective_end_date) papf
WHERE ai.vendor_site_id = ac.vendor_site_id
AND ai.vendor_id = pvend.vendor_id
AND ai.vendor_site_id = pvs.vendor_site_id
AND pvend.vendor_id = pvs.vendor_id
And Nvl(Pvend.Employee_Id, -99) = Papf.Person_Id (+)
AND ac.payment_method_code = ibyp.payment_method_code
AND ai.invoice_id = aip.invoice_id
AND ac.check_id = aip.check_id
AND aip.reversal_inv_pmt_id IS NULL
AND ai.legal_entity_id = :P_Legal_Entity_ID
-- AND JE_IL_TAX_PKG.get_gross_amount(ai.invoice_id,aip.check_id,:P_START_DATE ,:P_END_DATE,aip.reversal_inv_pmt_id) <> 0 --Bug 12612501
AND (JE_IL_TAX_PKG.get_gross_amount(ai.invoice_id,aip.check_id,:P_START_DATE ,:P_END_DATE,aip.reversal_inv_pmt_id) <> 0 OR JE_IL_TAX_PKG.get_awt_amount <> 0 ) -- Bug 12612501
AND EXISTS ( select 1 from ap_invoice_distributions_all aid
WHERE aid.invoice_id = ai.invoice_id
AND aid.line_type_lookup_code = 'AWT'
AND aid.awt_flag ='A'
AND aid.awt_invoice_payment_id IS NOT NULL
AND aid.awt_invoice_payment_id = aip.invoice_payment_id
AND aid.accounting_date >= :P_START_DATE
AND aid.accounting_date <= :P_END_DATE
UNION
SELECT 1
FROM AP_INVOICES_ALL ai1
,AP_INVOICE_PAYMENTS_ALL aip1
,AP_CHECKS_ALL ac1
,po_vendors pvend1
,po_vendor_sites_all pvs1
WHERE ai1.invoice_id = ai.invoice_id
AND ai1.invoice_id= aip1.invoice_id
AND ai1.vendor_id = pvend1.vendor_id
AND ai1.vendor_site_id = pvs1.vendor_site_id
AND pvend1.vendor_id = pvs1.vendor_id
AND aip1.check_id = ac1.check_id
AND ai1.legal_entity_id = :P_Legal_Entity_ID
AND ai1.set_of_books_id = &l_primary_ledger_id
AND aip1.set_of_books_id= &l_primary_ledger_id
AND ac1.global_attribute_category='JE.IL.APXPAWKB.CHECKS'
AND NVL(ac1.global_attribute1,0) > 0
AND aip1.accounting_date >=:P_START_DATE
AND aip1.accounting_date <=:P_END_DATE
&l_currency_check
&l_foreign_suppliers_check)
&p_vendor_type_cond
) a
where a.slno=1
UNION ALL
Select b.* From (
SELECT distinct
ac.check_id Check_ID,
ac.vendor_id vendor_id3,
ac.vendor_site_id Vendor_Site_ID3,
&p_tax_payerid_cond taxpayer_id1,
ac.check_number Check_Number,
ac.void_date Check_Date,
JE_IL_TAX_PKG.get_gross_amount(ai.invoice_id,aip.check_id,:P_START_DATE,:P_END_DATE,aip.reversal_inv_pmt_id) gross_amount,
JE_IL_TAX_PKG.get_awt_amt(Ai.Invoice_Id,Aip.Check_Id,:P_START_DATE,:P_END_DATE,aip.reversal_inv_pmt_id) Awt_Amount,
JE_IL_TAX_PKG.get_div_amt(Ai.Invoice_Id,Aip.Check_Id,:P_START_DATE,:P_END_DATE,aip.reversal_inv_pmt_id) Div_Amount,
JE_IL_TAX_PKG.get_div_awt(Ai.Invoice_Id,Aip.Check_Id,:P_START_DATE,:P_END_DATE,aip.reversal_inv_pmt_id) Div_Awt,
ibyp.payment_method_name Payment_Type,
to_char(aip.accounting_date,'MM') period,
row_number( ) over (partition by aip.invoice_id,aip.check_id,aip.accounting_event_id order by aip.invoice_id,aip.check_id,aip.accounting_event_id) SLNO
FROM
ap_invoices_all ai,
ap_checks_all ac,
ap_invoice_payments_all aip,
iby_payment_methods_vl ibyp,
po_vendors pvend,
po_vendor_sites_all pvs,
(SELECT distinct person_id
,national_identifier
FROM PER_ALL_PEOPLE_F WHERE trunc(sysdate) BETWEEN effective_start_date AND effective_end_date) papf
WHERE ai.vendor_site_id = ac.vendor_site_id
AND ai.vendor_id = pvend.vendor_id
AND ai.vendor_site_id = pvs.vendor_site_id
AND pvend.vendor_id = pvs.vendor_id
And Nvl(Pvend.Employee_Id, -99) = Papf.Person_Id (+)
AND ac.payment_method_code = ibyp.payment_method_code
AND ai.invoice_id = aip.invoice_id
AND ac.check_id = aip.check_id
AND aip.reversal_inv_pmt_id IS NOT NULL
AND ai.legal_entity_id = :P_Legal_Entity_ID
-- AND JE_IL_TAX_PKG.get_gross_amount(ai.invoice_id,aip.check_id,:P_START_DATE ,:P_END_DATE,aip.reversal_inv_pmt_id) <> 0 --Bug 12612501
AND (JE_IL_TAX_PKG.get_gross_amount(ai.invoice_id,aip.check_id,:P_START_DATE ,:P_END_DATE,aip.reversal_inv_pmt_id) <> 0 OR JE_IL_TAX_PKG.get_awt_amount <> 0 ) -- Bug 12612501
AND EXISTS ( select 1 from ap_invoice_distributions_all aid
WHERE aid.invoice_id = ai.invoice_id
AND aid.line_type_lookup_code = 'AWT'
AND aid.awt_flag ='A'
AND aid.awt_invoice_payment_id IS NOT NULL
AND aid.awt_invoice_payment_id = aip.invoice_payment_id
AND aid.accounting_date >= :P_START_DATE
AND aid.accounting_date <= :P_END_DATE
UNION
SELECT 1
FROM AP_INVOICES_ALL ai1
,AP_INVOICE_PAYMENTS_ALL aip1
,AP_CHECKS_ALL ac1
,po_vendors pvend1
,po_vendor_sites_all pvs1
WHERE ai1.invoice_id = ai.invoice_id
AND ai1.invoice_id= aip1.invoice_id
AND ai1.vendor_id = pvend1.vendor_id
AND ai1.vendor_site_id = pvs1.vendor_site_id
AND pvend1.vendor_id = pvs1.vendor_id
AND aip1.check_id = ac1.check_id
AND ai1.legal_entity_id = :P_Legal_Entity_ID
AND ai1.set_of_books_id = &l_primary_ledger_id
AND aip1.set_of_books_id= &l_primary_ledger_id
AND ac1.global_attribute_category='JE.IL.APXPAWKB.CHECKS'
AND NVL(ac1.global_attribute1,0) > 0
AND aip1.accounting_date >=:P_START_DATE
AND aip1.accounting_date <=:P_END_DATE
&l_currency_check
&l_foreign_suppliers_check)
&p_vendor_type_cond
) b
where b.slno=1 ) c
) d
Where (d.gawt_filter > 0 OR d.gamount_filter > 0)
) e
group by e.period,e.Vendor_ID3,e.taxpayer_id1,e.Vendor_Site_ID3
having sum(e.gross_amount) <> 0 or sum(e.awt_amount)<> 0
) abc
,(select rownum period_num from dual connect by rownum < 13) glp
WHERE glp.period_num = to_number(abc.period(+))
-- and :P_REPORT_NAME = 'JEILWHTT'
group by glp.period_num
order by glp.period_num |
