[Home] [Help]
View: XLE_REGISTRATIONS_V
View Text
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
,
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
,
HR_LOCATIONS_ALL 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
,
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
,
HR_LOCATIONS_ALL 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