VIEW: APPS.XLE_ESTABLISHMENT_V

Source

View Text - Preformatted

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,
         HR_LOCATIONS_ALL    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')

View Text - HTML Formatted

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
, HR_LOCATIONS_ALL 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')