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 |
