XLE_REGISTRATIONS

Details

Schema: FUSION
Object owner: XLE
Object type: VIEW

Columns

Name
LEGAL_ENTITY_ID LEGAL_ENTITY_NAME PARTY_ID ESTABLISHMENT_ID ESTABLISHMENT_NAME ESTABLISHMENT_PARTY_ID REGISTRATION_ID REGISTRATION_NUMBER REGISTERED_NAME ALTERNATE_REGISTERED_NAME IDENTIFYING LEGISLATIVE_CATEGORY JURISDICTION_ID REGISTRATION_CODE_LE REGISTRATION_CODE_ETB LEGALAUTH_ID LEGALAUTH_NAME LEGALAUTH_ADDRESS EFFECTIVE_FROM EFFECTIVE_TO LOCATION_ID ADDRESS_STYLE ADDRESS_LINE_1 ADDRESS_LINE_2 ADDRESS_LINE_3 TOWN_OR_CITY REGION_1 REGION_2 REGION_3 POSTAL_CODE COUNTRY

Name

LEGAL_ENTITY_ID

LEGAL_ENTITY_NAME

PARTY_ID

ESTABLISHMENT_ID

ESTABLISHMENT_NAME

ESTABLISHMENT_PARTY_ID

REGISTRATION_ID

REGISTRATION_NUMBER

REGISTERED_NAME

ALTERNATE_REGISTERED_NAME

IDENTIFYING

LEGISLATIVE_CATEGORY

JURISDICTION_ID

REGISTRATION_CODE_LE

REGISTRATION_CODE_ETB

LEGALAUTH_ID

LEGALAUTH_NAME

LEGALAUTH_ADDRESS

EFFECTIVE_FROM

EFFECTIVE_TO

LOCATION_ID

ADDRESS_STYLE

ADDRESS_LINE_1

ADDRESS_LINE_2

ADDRESS_LINE_3

TOWN_OR_CITY

REGION_1

REGION_2

REGION_3

POSTAL_CODE

COUNTRY

Query

SQL_Statement
SELECT lep.legal_entity_id, lep.name LEGAL_ENTITY_NAME, lep.party_id, null as establishment_id, null as establishment_name, null as establishment_party_id, reg.registration_id, reg.registration_number, reg.registered_name registered_name, reg.alternate_registered_name, reg.identifying_flag IDENTIFYING, jur.legislative_cat_code LEGISLATIVE_CATEGORY, jur.jurisdiction_id, jur.registration_code_le, jur.registration_code_etb, reg.issuing_authority_id LEGALAUTH_ID, (select party_name from hz_parties where party_id = reg.issuing_authority_id) LEGALAUTH_NAME, (select hzl.address1 \|\| ' ' \|\| hzl.address2 \|\| ' ' \|\| hzl.city \|\| ',' \|\| hzl.state \|\| ',' \|\| hzl.country \|\| ' ' \|\| hzl.postal_code from hz_locations hzl, hz_party_sites hps where hps.location_id = hzl.location_id and hps.party_site_id = reg.issuing_authority_site_id) LEGALAUTH_ADDRESS, reg.effective_from, reg.effective_to, reg.location_id, hrl.style ADDRESS_STYLE, hrl.address_line_1, hrl.address_line_2, hrl.address_line_3, hrl.town_or_city, hrl.region_1, hrl.region_2, hrl.region_3, hrl.postal_code, hrl.country FROM XLE_ENTITY_PROFILES lep, XLE_REGISTRATIONS reg, XLE_LOCATIONS_V hrl, XLE_JURISDICTIONS_VL jur WHERE lep.transacting_entity_flag = 'Y' AND lep.legal_entity_id = reg.source_id AND reg.source_table = 'XLE_ENTITY_PROFILES' AND hrl.location_id = reg.location_id AND jur.jurisdiction_id = reg.jurisdiction_id UNION SELECT null as legal_entity_id, null as LEGAL_ENTITY_NAME, null as party_id, etb.establishment_id establishment_id, etb.name establishment_name, etb.party_id establishment_party_id, reg.registration_id, reg.registration_number, reg.registered_name, reg.alternate_registered_name, reg.identifying_flag IDENTIFYING, jur.legislative_cat_code LEGISLATIVE_CATEGORY, jur.jurisdiction_id, jur.registration_code_le, jur.registration_code_etb, reg.issuing_authority_id LEGALAUTH_ID, (select party_name from hz_parties where party_id = reg.issuing_authority_id) LEGALAUTH_NAME, (select hzl.address1 \|\| ' ' \|\| hzl.address2 \|\| ' ' \|\| hzl.city \|\| ',' \|\| hzl.state \|\| ',' \|\| hzl.country \|\| ' ' \|\| hzl.postal_code from hz_locations hzl, hz_party_sites hps where hps.location_id = hzl.location_id and hps.party_site_id = reg.issuing_authority_site_id) LEGALAUTH_ADDRESS, reg.effective_from, reg.effective_to, reg.location_id, hrl.style ADDRESS_STYLE, hrl.address_line_1, hrl.address_line_2, hrl.address_line_3, hrl.town_or_city, hrl.region_1, hrl.region_2, hrl.region_3, hrl.postal_code, hrl.country FROM XLE_ETB_PROFILES etb, xle_entity_profiles lep, XLE_REGISTRATIONS reg, XLE_LOCATIONS_V hrl, XLE_JURISDICTIONS_VL jur WHERE lep.legal_entity_id = etb.legal_entity_id and lep.transacting_entity_flag = 'Y' and etb.establishment_id = reg.source_id AND reg.source_table = 'XLE_ETB_PROFILES' AND hrl.location_id = reg.location_id AND jur.jurisdiction_id = reg.jurisdiction_id

SQL_Statement

SELECT lep.legal_entity_id,

lep.name LEGAL_ENTITY_NAME,

lep.party_id,

null as establishment_id,

null as establishment_name,

null as establishment_party_id,

reg.registration_id,

reg.registration_number,

reg.registered_name registered_name,

reg.alternate_registered_name,

reg.identifying_flag IDENTIFYING,

jur.legislative_cat_code LEGISLATIVE_CATEGORY,

jur.jurisdiction_id,

jur.registration_code_le,

jur.registration_code_etb,

reg.issuing_authority_id LEGALAUTH_ID,

(select party_name from hz_parties where party_id = reg.issuing_authority_id) LEGALAUTH_NAME,

(select hzl.address1 || ' ' || hzl.address2 || ' ' || hzl.city || ',' || hzl.state

|| ',' || hzl.country || ' ' || hzl.postal_code

from hz_locations hzl, hz_party_sites hps

where hps.location_id = hzl.location_id

and hps.party_site_id = reg.issuing_authority_site_id) LEGALAUTH_ADDRESS,

reg.effective_from,

reg.effective_to,

reg.location_id,

hrl.style ADDRESS_STYLE,

hrl.address_line_1,

hrl.address_line_2,

hrl.address_line_3,

hrl.town_or_city,

hrl.region_1,

hrl.region_2,

hrl.region_3,

hrl.postal_code,

hrl.country

FROM XLE_ENTITY_PROFILES lep,

XLE_REGISTRATIONS reg,

XLE_LOCATIONS_V hrl,

XLE_JURISDICTIONS_VL jur

WHERE

lep.transacting_entity_flag = 'Y'

AND lep.legal_entity_id = reg.source_id

AND reg.source_table = 'XLE_ENTITY_PROFILES'

AND hrl.location_id = reg.location_id

AND jur.jurisdiction_id = reg.jurisdiction_id

UNION

SELECT null as legal_entity_id,

null as LEGAL_ENTITY_NAME,

null as party_id,

etb.establishment_id establishment_id,

etb.name establishment_name,

etb.party_id establishment_party_id,