XLE_ESTABLISHMENT_V

Details

• Schema: FUSION

• Object owner: XLE

• Object type: VIEW

Columns

Name
ESTABLISHMENT_ID ESTABLISHMENT_NAME PARTY_ID LEGAL_ENTITY_ID MAIN_ESTABLISHMENT_FLAG ACTIVITY_CODE SUB_ACTIVITY_CODE TYPE_OF_COMPANY ETB_EFFECTIVE_FROM ETB_EFFECTIVE_TO REGISTRATION_NUMBER LEGISLATIVE_CAT_CODE 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 etbp.establishment_id, etbp.name establishment_name, etbp.party_id, etbp.legal_entity_id, etbp.main_establishment_flag, etbp.activity_code, etbp.sub_activity_code, etbp.type_of_company, etbp.effective_from etb_effective_from, etbp.effective_to etb_effective_to, reg.registration_number, jur.legislative_cat_code, 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 etbp, XLE_REGISTRATIONS reg, XLE_LOCATIONS_V hrl, xle_jurisdictions_vl jur WHERE etbp.establishment_id = reg.source_id AND reg.source_table = 'XLE_ETB_PROFILES' AND hrl.location_id = reg.location_id AND reg.identifying_flag = 'Y' AND jur.jurisdiction_id = reg.jurisdiction_id UNION SELECT etbp.establishment_id, etbp.name establishment_name, etbp.party_id, etbp.legal_entity_id, etbp.main_establishment_flag, etbp.activity_code, etbp.sub_activity_code, etbp.type_of_company, etbp.effective_from etb_effective_from, etbp.effective_to etb_effective_to, NULL registration_number, NULL legislative_cat_code, NULL effective_from, NULL effective_to, NULL location_id, NULL ADDRESS_STYLE, NULL address_line_1, NULL address_line_2, NULL address_line_3, NULL town_or_city, NULL region_1, NULL region_2, NULL region_3, NULL postal_code, NULL country FROM XLE_ETB_PROFILES etbp WHERE etbp.establishment_id not in (SELECT xlr.source_id FROM XLE_REGISTRATIONS xlr WHERE xlr.source_table = 'XLE_ETB_PROFILES')