View: XLE_ESTABLISHMENT_V

Product:	XLE - Legal Entity Configurator
Description:
Implementation/DBA Data:	APPS.XLE_ESTABLISHMENT_V

View Text

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