SELECT hp.party_name,
hp.party_number,
hca.account_number,
hca.cust_account_id,
hp.party_id,
hps.party_site_id,
hcsu.cust_acct_site_id,
hps.location_id,
hl.address1,
hl.address2,
hl.address3,
hl.city,
hl.state,
ter.nls_territory,
hl.postal_code,
hl.province,
hcsu.site_use_code,
hcsu.site_use_id,
hcsa.bill_to_flag,
(SELECT concatenated_segments FROM gl_code_combinations_kfv WHERE code_combination_id=hcsu.GL_ID_REC) RECEIVABLES_AC,
(SELECT concatenated_segments FROM gl_code_combinations_kfv WHERE code_combination_id=hcsu.GL_ID_REV) REVENUE_AC,
(Select Name from hr_operating_units where organization_id = hcsa.ORG_ID) OU_NAME,
zp.REP_REGISTRATION_NUMBER,
zr.TAX_REGIME_CODE, zr.tax,
zr.REGISTRATION_NUMBER
FROM hz_parties hp,
hz_party_sites hps,
hz_locations hl,
hz_cust_accounts_all hca,
hz_cust_acct_sites_all hcsa,
hz_cust_site_uses_all hcsu,
fnd_territories ter,
zx_party_tax_profile zp,
zx_registrations zr
WHERE hca.STATUS ='A' --Active
AND hp.party_id = hps.party_id
AND hps.location_id = hl.location_id
AND hp.party_id = hca.party_id
AND hcsa.party_site_id = hps.party_site_id
AND hcsu.cust_acct_site_id = hcsa.cust_acct_site_id
AND hca.cust_account_id = hcsa.cust_account_id
AND hl.country = ter.territory_code
AND hps.party_site_id = zp.party_id
--- AND zp.PARTY_TYPE_CODE = 'THIRD_PARTY'
AND PARTY_TYPE_CODE = 'THIRD_PARTY_SITE'
AND zr.party_tax_profile_id(+)= zp.party_tax_profile_id
hp.party_number,
hca.account_number,
hca.cust_account_id,
hp.party_id,
hps.party_site_id,
hcsu.cust_acct_site_id,
hps.location_id,
hl.address1,
hl.address2,
hl.address3,
hl.city,
hl.state,
ter.nls_territory,
hl.postal_code,
hl.province,
hcsu.site_use_code,
hcsu.site_use_id,
hcsa.bill_to_flag,
(SELECT concatenated_segments FROM gl_code_combinations_kfv WHERE code_combination_id=hcsu.GL_ID_REC) RECEIVABLES_AC,
(SELECT concatenated_segments FROM gl_code_combinations_kfv WHERE code_combination_id=hcsu.GL_ID_REV) REVENUE_AC,
(Select Name from hr_operating_units where organization_id = hcsa.ORG_ID) OU_NAME,
zp.REP_REGISTRATION_NUMBER,
zr.TAX_REGIME_CODE, zr.tax,
zr.REGISTRATION_NUMBER
FROM hz_parties hp,
hz_party_sites hps,
hz_locations hl,
hz_cust_accounts_all hca,
hz_cust_acct_sites_all hcsa,
hz_cust_site_uses_all hcsu,
fnd_territories ter,
zx_party_tax_profile zp,
zx_registrations zr
WHERE hca.STATUS ='A' --Active
AND hp.party_id = hps.party_id
AND hps.location_id = hl.location_id
AND hp.party_id = hca.party_id
AND hcsa.party_site_id = hps.party_site_id
AND hcsu.cust_acct_site_id = hcsa.cust_acct_site_id
AND hca.cust_account_id = hcsa.cust_account_id
AND hl.country = ter.territory_code
AND hps.party_site_id = zp.party_id
--- AND zp.PARTY_TYPE_CODE = 'THIRD_PARTY'
AND PARTY_TYPE_CODE = 'THIRD_PARTY_SITE'
AND zr.party_tax_profile_id(+)= zp.party_tax_profile_id
Order By party_name