SELECT pv.vendor_name,
pv.segment1 supplier_number,
pv.start_date_active header_start_date_active,
pv.end_date_active header_end_date_active,
(SELECT registration_status_code
FROM zx_registrations reg,
zx_party_tax_profile zptp
WHERE reg.party_tax_profile_id = zptp.party_tax_profile_id
AND zptp.party_id = pv.party_id
AND REG.EFFECTIVE_FROM <=SYSDATE
AND NVL(EFFECTIVE_TO,SYSDATE+10) > SYSDATE
AND zptp.party_type_code ='THIRD_PARTY'
) header_reg_status_code,
(SELECT registration_number
FROM zx_registrations reg,
zx_party_tax_profile zptp
WHERE reg.party_tax_profile_id = zptp.party_tax_profile_id
AND zptp.party_id = pv.party_id
AND REG.EFFECTIVE_FROM <=SYSDATE
AND NVL(EFFECTIVE_TO,SYSDATE+10) > SYSDATE
AND zptp.party_type_code ='THIRD_PARTY'
) header_reg_number,
pvsa.vendor_site_code,
pvsa.PURCHASING_SITE_FLAG,
pvsa.inactive_date SITE_INACTIVE_DATE,
(SELECT registration_status_code
FROM zx_registrations reg,
zx_party_tax_profile zptp
WHERE reg.party_tax_profile_id = zptp.party_tax_profile_id
AND zptp.party_id = pvsa.party_site_id
AND REG.EFFECTIVE_FROM <=SYSDATE
AND NVL(EFFECTIVE_TO,SYSDATE+10) > SYSDATE
AND zptp.party_type_code ='THIRD_PARTY_SITE'
) site_reg_status_code,
(SELECT registration_number
FROM zx_registrations reg,
zx_party_tax_profile zptp
WHERE reg.party_tax_profile_id = zptp.party_tax_profile_id
AND zptp.party_id = pvsa.party_site_id
AND REG.EFFECTIVE_FROM <=SYSDATE
AND NVL(EFFECTIVE_TO,SYSDATE+10) > SYSDATE
AND zptp.party_type_code ='THIRD_PARTY_SITE'
) site_reg_number,
pvsa.country,
PVSA.ADDRESS_LINE1,
PVSA.ADDRESS_LINE2,
PVSA.ADDRESS_LINE3,
PVSA.CITY,
PVSA.STATE,
PVSA.PROVINCE
FROM po_vendor_sites_all pvsa,
po_vendors pv
WHERE pvsa.org_id = 125
AND pv.vendor_id = pvsa.vendor_id
ORDER BY pv.vendor_name,
pv.segment1