SELECT XR.registered_name LEGAL_ENTITY_NAME
,XR.registration_number TAXPAYER_ID
,vatrep.tax_registration_number VAT_REGISTRATION_NUMBER
,HL.country COUNTRY
,HL.address_line_1 ADDRESS_LINE_1
,HL.address_line_2 ADDRESS_LINE_2
,NVL(HL.address_line_3, HL.town_or_city||' '||HL.postal_code||' '||HL.country) ADDRESS_LINE_3
,HL.town_or_city TOWN_OR_CITY
,HL.postal_code POSTAL_CODE
,HL.region_1 PROVINCE
,XEP.legal_entity_id LEGAL_ENTITY_ID
,fl.meaning REPORTING_TYPE
FROM xle_registrations XR
,xle_entity_profiles XEP
,hr_locations_all HL
,hz_parties HP
,jg_zz_vat_rep_entities VATREP
,jg_zz_vat_rep_entities ACC
,fnd_lookups fl
WHERE ACC.VAT_REPORTING_ENTITY_ID = :P_VAT_REPORTING_ENTITY_ID
AND ((ACC.ENTITY_TYPE_CODE = 'ACCOUNTING'
AND ACC.MAPPING_VAT_REP_ENTITY_ID = VATREP.VAT_REPORTING_ENTITY_ID)
OR
(ACC.ENTITY_TYPE_CODE = 'LEGAL'
AND ACC.VAT_REPORTING_ENTITY_ID = VATREP.VAT_REPORTING_ENTITY_ID)
)
AND XR.source_id = XEP.legal_entity_id
AND XR.source_table = 'XLE_ENTITY_PROFILES'
AND XEP.legal_entity_id = VATREP.legal_entity_id
AND XR.location_id = HL.location_id
AND XEP.party_id = HP.party_id
AND xr.identifying_flag = 'Y'
AND fl.lookup_type = 'JGZZ_REPORT_TYPE'
AND fl.lookup_code = :P_REPORT_TYPE |
