VIEW: APPS.ZX_TRL_ESL_VAT_NO_V

Source

View Text - Preformatted

SELECT  DISTINCT
        reg.registration_number       tax_registration_number,
        ptp.party_tax_profile_id      legal_estb_ptp_id,
        etb.establishment_name        establishment_name,
        le.name                       legal_entity_name ,
        le.legal_entity_id            legal_entity_id,
        hp.party_number               organization_number,
        etb.address_line_1            address_line_1,
        etb.address_line_2            address_line_2,
        etb.town_or_city              town_or_city,
        etb.region_1                  region_1,
        etb.postal_code               postal_code,
        geo.geography_name            geography_name,
        Decode(etb.address_line_1, NULL, '', etb.address_line_1 || ', ') ||
        Decode(etb.address_line_2, NULL, '', etb.address_line_2 || ', ') ||
        Decode(etb.town_or_city, NULL, '', etb.town_or_city || Decode(etb.region_1, NULL, '', ', ')) 
        ||  Nvl(etb.region_1, '') ||
        Decode(etb.postal_code, NULL, '', ' - ' || etb.postal_code) ||
        Decode(geo.geography_name, NULL, '', ', ' || geo.geography_name)     estb_address
   FROM zx_registrations             reg,
        zx_party_tax_profile         ptp,
        xle_fp_establishment_v       etb,
        xle_firstparty_information_v le,
        hz_parties                   hp,
        hz_geographies               geo
  WHERE ptp.party_type_code         = 'LEGAL_ESTABLISHMENT'
    AND reg.party_tax_profile_id    = ptp.party_tax_profile_id
    AND reg.registration_number     IS NOT NULL
    AND ptp.party_id                = etb.party_id
    AND etb.legal_entity_id         = le.legal_entity_id
    AND etb.party_id                = hp.party_id
    AND etb.country                 = geo.geography_code(+)
    AND geo.geography_type(+)          = 'COUNTRY'

View Text - HTML Formatted

SELECT DISTINCT REG.REGISTRATION_NUMBER TAX_REGISTRATION_NUMBER
, PTP.PARTY_TAX_PROFILE_ID LEGAL_ESTB_PTP_ID
, ETB.ESTABLISHMENT_NAME ESTABLISHMENT_NAME
, LE.NAME LEGAL_ENTITY_NAME
, LE.LEGAL_ENTITY_ID LEGAL_ENTITY_ID
, HP.PARTY_NUMBER ORGANIZATION_NUMBER
, ETB.ADDRESS_LINE_1 ADDRESS_LINE_1
, ETB.ADDRESS_LINE_2 ADDRESS_LINE_2
, ETB.TOWN_OR_CITY TOWN_OR_CITY
, ETB.REGION_1 REGION_1
, ETB.POSTAL_CODE POSTAL_CODE
, GEO.GEOGRAPHY_NAME GEOGRAPHY_NAME
, DECODE(ETB.ADDRESS_LINE_1
, NULL
, ''
, ETB.ADDRESS_LINE_1 || '
, ') || DECODE(ETB.ADDRESS_LINE_2
, NULL
, ''
, ETB.ADDRESS_LINE_2 || '
, ') || DECODE(ETB.TOWN_OR_CITY
, NULL
, ''
, ETB.TOWN_OR_CITY || DECODE(ETB.REGION_1
, NULL
, ''
, '
, ')) || NVL(ETB.REGION_1
, '') || DECODE(ETB.POSTAL_CODE
, NULL
, ''
, ' - ' || ETB.POSTAL_CODE) || DECODE(GEO.GEOGRAPHY_NAME
, NULL
, ''
, '
, ' || GEO.GEOGRAPHY_NAME) ESTB_ADDRESS
FROM ZX_REGISTRATIONS REG
, ZX_PARTY_TAX_PROFILE PTP
, XLE_FP_ESTABLISHMENT_V ETB
, XLE_FIRSTPARTY_INFORMATION_V LE
, HZ_PARTIES HP
, HZ_GEOGRAPHIES GEO
WHERE PTP.PARTY_TYPE_CODE = 'LEGAL_ESTABLISHMENT'
AND REG.PARTY_TAX_PROFILE_ID = PTP.PARTY_TAX_PROFILE_ID
AND REG.REGISTRATION_NUMBER IS NOT NULL
AND PTP.PARTY_ID = ETB.PARTY_ID
AND ETB.LEGAL_ENTITY_ID = LE.LEGAL_ENTITY_ID
AND ETB.PARTY_ID = HP.PARTY_ID
AND ETB.COUNTRY = GEO.GEOGRAPHY_CODE(+)
AND GEO.GEOGRAPHY_TYPE(+) = 'COUNTRY'