JE Israeli Withholding Annual Certificate To Vendors

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