JE Israeli Withholding Annual Certificate To Vendors

Description
Categories: BI Publisher
Application: European Localizations
Source: Israeli Withholding Annual Certificate To Vendors Report
Short Name: JEILAVCR
DB package: JE_IL_TAX_PKG
SELECT pvend.vendor_id vendor_id2,
					  pvend.segment1 vendor_num1,
					  &p_vendor_name_col
					  &p_vendor_sitecode_col
					  pvs.vendor_site_id vendor_site_id1,
					  &p_vat_reg_no
					  je_il_tax_pkg.is_number(&p_tax_payerid_cond,   pvend.segment1) taxpayer_id,
					  TRIM(pvs.address_line1 || ' ' || pvs.address_line2 || ' ' || pvs.address_line3 || ' ,' || pvs.CITY || '  ' || pvs.ZIP) vendor_address, 
					  &p_business_sec_cond
						(SELECT b.description
					   FROM fnd_flex_value_sets a,
						 fnd_flex_values_vl b
					   WHERE a.flex_value_set_name = 'JEIL_TAX_OFFICER_NUMBER'
					   AND a.flex_value_set_id = b.flex_value_set_id
					   AND b.flex_value = &p_flex_value_cond) irs_tax_officer,
					   (SELECT b.description
					   FROM fnd_flex_value_sets a,
						 fnd_flex_values_vl b
					   WHERE a.flex_value_set_name = 'JEIL_WHT_TAX_RPT_GROUP'
					   AND a.flex_value_set_id = b.flex_value_set_id
					   AND b.flex_value = SUBSTR(&p_deduction_type_cond,1,2)) deduction_type
				--	  SUBSTR(&p_deduction_type_cond,   1,   2) deduction_type
				FROM po_vendors pvend,
					  (SELECT DISTINCT person_id,
					   national_identifier
					 FROM PER_ALL_PEOPLE_F WHERE trunc(sysdate) BETWEEN effective_start_date AND effective_end_date) papf,
					  po_vendor_sites_all pvs,
					  ap_awt_groups aag
				WHERE pvend.vendor_id = pvs.vendor_id
				 AND nvl(pvend.employee_id,   -99) = papf.person_id(+)
				 AND aag.group_id(+) = nvl(pvs.awt_group_id,pvs.pay_awt_group_id)
				 AND pvs.vendor_site_id IN
				  (SELECT ai.vendor_site_id
				   FROM ap_invoices_all ai,
					 ap_invoice_distributions_all aid 
				  WHERE ai.invoice_id = aid.invoice_id 
				  AND ai.set_of_books_id = &l_primary_ledger_id
				  AND aid.set_of_books_id = &l_primary_ledger_id
				  AND ai.legal_entity_id = :p_legal_entity_id
				   AND aid.line_type_lookup_code = 'AWT'
				   AND aid.awt_flag = 'A'
				   AND aid.awt_invoice_payment_id IS NOT NULL
				   AND aid.accounting_date >= :p_start_date
				   AND aid.accounting_date <= :p_end_date
				   UNION
									 SELECT ac1.vendor_site_id
									 FROM  AP_INVOICE_PAYMENTS_ALL aip1
									      ,AP_CHECKS_ALL ac1
									   	  ,po_vendors pvend1
										  ,po_vendor_sites_all pvs1
									 WHERE ac1.vendor_id = pvend1.vendor_id
									 AND   ac1.vendor_site_id = pvs1.vendor_site_id
									 AND   pvend1.vendor_id = pvs1.vendor_id
									 AND   aip1.check_id = ac1.check_id
					                 AND   ac1.legal_entity_id = :P_Legal_Entity_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
									-- AND :P_REPORT_NAME = 'JEILWHTT'
									 &l_currency_check
									 &l_foreign_suppliers_check
									 ) 
				   &p_vendor_type_cond 
 				   &p_supplier_num_from 
				   &p_supplier_num_to 
				ORDER BY pvs.vendor_id, pvs.vendor_site_id
Parameter Name SQL text Validation
Report Name
 
Name Level
 
LOV Oracle
Information Level
 
LOV Oracle
To Supplier Number
 
LOV Oracle
From Supplier Number
 
LOV Oracle
Vendor Type
 
LOV Oracle
Include WHT = 0
 
LOV Oracle
Legal Entity
 
LOV Oracle
Ledger
 
LOV Oracle
To Period
 
LOV Oracle
From Period
 
LOV Oracle
Year
 
LOV Oracle