XLE Legal Entities

select
x.*
from
(
select
nvl(hg.geography_name,max(hg.geography_name) over (partition by xep.legal_entity_id)) jurisdiction_country,
xep0.name parent_legal_entity,
xep.type,
xep.name,
hp.party_number,
hla.location_code,
nvl2(hla.address_line_1,hla.address_line_1||' ','')||
nvl2(hla.address_line_2,hla.address_line_2||' ','')||
nvl2(hla.address_line_3,hla.address_line_3||' ','')||
nvl2(hla.town_or_city,hla.town_or_city||' ','')||
nvl2(hla.region_2,hla.region_2||' ','')||
hla.postal_code address,
ftv.territory_short_name country,
xep.main_establishment,
xep.effective_from,
xep.effective_to,
xxen_util.meaning(xjv.legislative_cat_code,'LEGISLATIVE_CATEGORY',222) legislative_category,
xjv.name jurisdiction_name,
xr.registered_name,
xr.registration_number,
nvl2(flvv.meaning,flvv.meaning||' ('||flvv.description||')',null) registration_code,
hp0.party_name legalauth_name,
nvl2(hl.address1,hl.address1||' ','')||
nvl2(hl.address2,hl.address2||' ','')||
nvl2(hl.city,hl.city||' ','')||
nvl2(hl.state,hl.state||' ','')||
nvl2(hl.country,hl.country||' ','')||
hl.postal_code legalauth_address
from
(
select
'Legal Entity' type,
xep.party_id,
xep.legal_entity_id,
'XLE_ENTITY_PROFILES' source_table,
xep.legal_entity_id source_id,
xep.name,
xep.effective_from,
xep.effective_to,
null main_establishment
from
xle_entity_profiles xep
union all
select
'Establishment' type,
xestp.party_id,
xestp.legal_entity_id,
'XLE_ETB_PROFILES' source_table,
xestp.establishment_id source_id,
xestp.name,
xestp.effective_from,
xestp.effective_to,
decode(xestp.main_establishment_flag,'Y','Yes') main_establishment
from
xle_entity_profiles xep,
xle_etb_profiles xestp
where
xep.legal_entity_id=xestp.legal_entity_id
) xep,
hz_parties hp,
xle_entity_profiles xep0,
xle_registrations xr,
xle_jurisdictions_vl xjv,
hz_geographies hg,
fnd_lookup_values_vl flvv,
hr_locations_all hla,
fnd_territories_vl ftv,
hz_parties hp0,
hz_party_sites hps,
hz_locations hl
where
xep.party_id=hp.party_id and
xep.legal_entity_id=xep0.legal_entity_id and
xep.source_table=xr.source_table(+) and
xep.source_id=xr.source_id(+) and
xr.jurisdiction_id=xjv.jurisdiction_id(+) and
xjv.geography_id=hg.geography_id(+) and
xjv.registration_code_le=flvv.lookup_code(+) and
flvv.lookup_type(+)='XLE_REG_CODE' and
flvv.view_application_id(+)=204 and
flvv.security_group_id(+)=0 and
xr.location_id=hla.location_id(+) and
hla.country=ftv.territory_code(+) and
xr.issuing_authority_id=hp0.party_id(+) and
xr.issuing_authority_site_id=hps.party_site_id(+) and
hps.location_id=hl.location_id(+)
) x
where
1=1
order by
x.jurisdiction_country,
x.parent_legal_entity,
x.type desc,
x.main_establishment,
x.effective_to desc,
x.party_number,
x.legislative_category,
x.registration_number

select x.* from ( select nvl(hg.geography_name,max(hg.geography_name) over (partition by xep.legal_entity_id)) jurisdiction_country, xep0.name parent_legal_entity, xep.type, xep.name, hp.party_number, hla.location_code, nvl2(hla.address_line_1,hla.address_line_1||' ','')|| nvl2(hla.address_line_2,hla.address_line_2||' ','')|| nvl2(hla.address_line_3,hla.address_line_3||' ','')|| nvl2(hla.town_or_city,hla.town_or_city||' ','')|| nvl2(hla.region_2,hla.region_2||' ','')|| hla.postal_code address, ftv.territory_short_name country, xep.main_establishment, xep.effective_from, xep.effective_to, xxen_util.meaning(xjv.legislative_cat_code,'LEGISLATIVE_CATEGORY',222) legislative_category, xjv.name jurisdiction_name, xr.registered_name, xr.registration_number, nvl2(flvv.meaning,flvv.meaning||' ('||flvv.description||')',null) registration_code, hp0.party_name legalauth_name, nvl2(hl.address1,hl.address1||' ','')|| nvl2(hl.address2,hl.address2||' ','')|| nvl2(hl.city,hl.city||' ','')|| nvl2(hl.state,hl.state||' ','')|| nvl2(hl.country,hl.country||' ','')|| hl.postal_code legalauth_address from ( select 'Legal Entity' type, xep.party_id, xep.legal_entity_id, 'XLE_ENTITY_PROFILES' source_table, xep.legal_entity_id source_id, xep.name, xep.effective_from, xep.effective_to, null main_establishment from xle_entity_profiles xep union all select 'Establishment' type, xestp.party_id, xestp.legal_entity_id, 'XLE_ETB_PROFILES' source_table, xestp.establishment_id source_id, xestp.name, xestp.effective_from, xestp.effective_to, decode(xestp.main_establishment_flag,'Y','Yes') main_establishment from xle_entity_profiles xep, xle_etb_profiles xestp where xep.legal_entity_id=xestp.legal_entity_id ) xep, hz_parties hp, xle_entity_profiles xep0, xle_registrations xr, xle_jurisdictions_vl xjv, hz_geographies hg, fnd_lookup_values_vl flvv, hr_locations_all hla, fnd_territories_vl ftv, hz_parties hp0, hz_party_sites hps, hz_locations hl where xep.party_id=hp.party_id and xep.legal_entity_id=xep0.legal_entity_id and xep.source_table=xr.source_table(+) and xep.source_id=xr.source_id(+) and xr.jurisdiction_id=xjv.jurisdiction_id(+) and xjv.geography_id=hg.geography_id(+) and xjv.registration_code_le=flvv.lookup_code(+) and flvv.lookup_type(+)='XLE_REG_CODE' and flvv.view_application_id(+)=204 and flvv.security_group_id(+)=0 and xr.location_id=hla.location_id(+) and hla.country=ftv.territory_code(+) and xr.issuing_authority_id=hp0.party_id(+) and xr.issuing_authority_site_id=hps.party_site_id(+) and hps.location_id=hl.location_id(+) ) x where 1=1 order by x.jurisdiction_country, x.parent_legal_entity, x.type desc, x.main_establishment, x.effective_to desc, x.party_number, x.legislative_category, x.registration_number