Supplier Tax Payer ID:
SELECT ou.name OU,
aps.VENDOR_NAME,
aps.vendor_id,
aps.creation_date "VendorCreation Date",
aps.end_date_active "Vendor EndDate",
-- apsa.INACTIVE_DATE AS"SiteInactiveDate",
aps.segment1 AS "VendorNumber",
-- aps.vendor_type_lookup_code,
apsa.VENDOR_SITE_CODE,
--apsa.country,
(SELECT DECODE (
UPPER(aps.vendor_type_lookup_code),
'EMPLOYEE', (SELECTpapf.national_identifier
FROMapps.per_people_x papf
WHEREpapf.person_id = aps.employee_id),
DECODE(aps.organization_type_lookup_code,
'INDIVIDUAL',aps.individual_1099,
'FOREIGNINDIVIDUAL', aps.individual_1099,
hp.jgzz_fiscal_code))
FROM apps.hz_parties hp
WHERE hp.party_id = aps.party_id)
AS "Tax Payer ID"
--apsa.INACTIVE_DATE SiteEndDate,
-- apsa.creation_date VendorSiteCreation
FROM apps.ap_suppliers aps,
apps.ap_supplier_sites_all apsa,
apps.hr_operating_units ou
WHERE aps.vendor_id = apsa.vendor_id
AND ou.organization_id = apsa.org_id
AND ou.name='AU OU' --in('AU OU','NZOU','PG OU','FJ OU')-- 'AU OU'
--AND aps.end_date_active IS NULL
AND aps.vendor_type_lookup_code<> 'INTERCOMPANY'
AND aps.vendor_type_lookup_code<> 'EMPLOYEE';
--AND NVL (apsa.INACTIVE_DATE, SYSDATE+ 1) > SYSDATE
AND (SELECT DECODE (
UPPER(aps.vendor_type_lookup_code),
'EMPLOYEE', (SELECTpapf.national_identifier
FROMapps.per_people_x papf
WHEREpapf.person_id = aps.employee_id),
DECODE(aps.organization_type_lookup_code,
'INDIVIDUAL',aps.individual_1099,
'FOREIGN INDIVIDUAL',aps.individual_1099,
hp.jgzz_fiscal_code))
FROM apps.hz_parties hp
WHERE hp.party_id =aps.party_id) = '51109754627'
ORDER BY 1, 2, 3;--bau.bank_account_number, ou.name;--