Tax Registration Number

-- Getting from Party Site

    (
        SELECT
            zr.registration_number trn_customer_site
        FROM
            zx_party_tax_profile zptf,
            zx_registrations zr
        WHERE
            zptf.party_id = hps.party_site_id
            AND zptf.party_tax_profile_id = zr.party_tax_profile_id
            AND zptf.party_type_code = 'THIRD_PARTY_SITE'
    ) trn_customer_site,