SELECT XR.registered_name LEGAL_ENTITY_NAME
,XR.registration_number TAX_PAYER_ID
,FT.territory_short_name LE_COUNTRY
,HL.address_line_1 ADDRESS_LINE_1
,HL.address_line_2 ADDRESS_LINE_2
,HL.address_line_3 ADDRESS_LINE_3
,HL.town_or_city TOWN_OR_CITY
,HL.postal_code POSTAL_CODE
,hp.party_id LE_PARTY_ID
,HL.region_1 REGION
,ZPTP.rep_registration_number VAT_REGISTRATION_NUMBER
,gll.currency_code CURRENCY_CODE
FROM xle_registrations XR
,xle_entity_profiles XEP
,hr_locations_all HL
,hz_parties HP
,fnd_territories_vl FT
,ZX_PARTY_TAX_PROFILE ZPTP
,XLE_ETB_PROFILES XETPB
,GL_LEDGER_LE_V GLL
WHERE XR.source_id = XEP.legal_entity_id
AND XR.source_table = 'XLE_ENTITY_PROFILES'
AND XEP.legal_entity_id = :P_LEGAL_ENTITY_ID
AND XR.location_id = HL.location_id
AND HL.country = FT.TERRITORY_CODE
AND XEP.party_id = HP.party_id
AND ZPTP.PARTY_TYPE_CODE = 'LEGAL_ESTABLISHMENT'
AND XETPB.party_id=ZPTP.party_id
AND XETPB.legal_entity_id = XEP.legal_entity_id
AND GLL.legal_entity_id=XEP.legal_entity_id
AND XETPB.MAIN_ESTABLISHMENT_FLAG = 'Y'
AND XR.identifying_flag = 'Y'
AND GLL.ledger_category_code = 'PRIMARY' |
