VIEW: APPS.XLE_REGISTRATIONS_V

Source

View Text - Preformatted

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
    

   

View Text - HTML Formatted

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