JE Israeli Withholding Tax Reconciliation

➜ Back to Library
Description
Categories: BI Publisher
Imported from BI Publisher
Application: European Localizations
Source: Israeli Withholding Tax Reconciliation Report
Short Name: JEILWHTR
DB package: JE_IL_TAX_PKG
Run JE Israeli Withholding Tax Reconciliation and other Oracle EBS reports with Blitz Report™ on our demo environment
 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
Parameter Name SQL text Validation
From Period 
 
 LOV Oracle
To Period 
 
 LOV Oracle
Ledger 
 
 LOV Oracle
Legal Entity 
 
 LOV Oracle
Vendor Name 
 
 LOV Oracle
Vendor Site 
 
 LOV Oracle
Vendor Type 
 
 LOV Oracle
Order by 
 
 LOV Oracle
Information Level 
 
 LOV Oracle
Name Level 
 
 LOV Oracle
Download
Blitz Report™

Blitz Report™ provides multiple benefits: