Madonna test

SELECT
    hou.name                                                              org_name,
    'CUSTOMER_SITE'                                                       entity_type,
    hp.party_name                                                         customer_name,
    hp.party_number                                                       customer_number,
    ftr.territory_short_name                                              customer_country,
    hp.status,
    zr.registration_number                                                trn_number,
    ftvr.territory_short_name                                             trn_country,
    zr.tax_regime_code,
    zr.tax,
    zr.registration_status_code,
    zr.effective_from,
    zr.effective_to,
    zptp.rep_registration_number                                          default_trn_number,
    hcsu.tax_reference                                                    legacy_trn_field,
    apps.hz_format_pub.format_address(hl.location_id, NULL, NULL, '', '') AS site_address,
    hcsu.site_use_code,
    hp.party_type,
    hps.party_site_number,
    hca.account_number                                                    cust_account_number
FROM
    hz_parties             hp,
    hz_party_sites         hps,
    hz_cust_accounts_all   hca,
    hz_cust_acct_sites_all hcas,
    hz_cust_site_uses_all  hcsu,
    hz_locations           hl,
    zx_party_tax_profile   zptp,
    zx_registrations       zr,
    hr_operating_units     hou,
    zx_regimes_b           zxb,
    fnd_territories_vl     ftvr,
    fnd_territories_vl     ftr
WHERE
        hp.party_id = hps.party_id
    AND zptp.party_id = hps.party_site_id
    AND zptp.party_tax_profile_id = zr.party_tax_profile_id (+)
    AND hp.party_id = hca.party_id
    AND hps.location_id = hl.location_id (+)
    AND hca.cust_account_id = hcas.cust_account_id (+)
    AND hps.party_site_id (+) = hcas.party_site_id
    AND hcas.cust_acct_site_id = hcsu.cust_acct_site_id
    AND hou.organization_id = hcas.org_id
    AND zr.tax_regime_code = zxb.tax_regime_code (+)
    AND zxb.country_code = ftvr.territory_code (+)
    AND hp.country = ftr.territory_code (+)
    AND hp.party_type IN ( 'ORGANIZATION', 'PERSON' )
ORDER BY
    hp.party_name,
    hps.party_site_number