Search Results legislative_cat_code

Overview

The XLE_ESTABLISHMENT_V view in the APPS schema is a reporting and integration object within the Oracle E-Business Suite Legal Entity Configurator (XLE) module. It exposes establishment records registered against legal entities, combining establishment profile attributes with the registration information and the corresponding address data maintained in Oracle HR. The view is owned by APPS, is documented as VALID, and is available in both EBS 12.1.1 and 12.2.2.

The view is of particular interest to users searching on the column address_style, because ADDRESS_STYLE is one of the columns projected from HR_LOCATIONS_ALL. This makes the view a convenient single source for retrieving the formatted address style (for example, the country-specific address format defined in Oracle HR) alongside the establishment name, registration number, and jurisdiction. Rather than joining XLE_ETB_PROFILES, XLE_REGISTRATIONS, XLE_JURISDICTIONS_VL, and HR_LOCATIONS_ALL manually, consumers can query this view directly.

Underlying Base Objects

The view is defined over four documented base objects: XLE_ETB_PROFILES, XLE_REGISTRATIONS, XLE_JURISDICTIONS_VL, and HR_LOCATIONS_ALL. The first three are accessed through synonyms in the APPS schema; XLE_JURISDICTIONS_VL is itself a view.

The view text is a UNION of two branches. The first branch joins establishment profiles (ETBP) to registrations (REG) on ESTABLISHMENT_ID = REG.SOURCE_ID, restricted to REG.SOURCE_TABLE = 'XLE_ETB_PROFILES' and REG.IDENTIFYING_FLAG = 'Y'. It then joins HR_LOCATIONS_ALL (HRL) on LOCATION_ID and XLE_JURISDICTIONS_VL (JUR) on JURISDICTION_ID. The second branch returns establishments from XLE_ETB_PROFILES that have no matching registration row, padding all registration, jurisdiction, and address columns with NULL. This UNION pattern guarantees that every establishment is returned at least once, whether or not a registration and address exist.

Key Columns

• ESTABLISHMENT_ID – Primary identifier of the establishment profile.
• ESTABLISHMENT_NAME – Name of the establishment (aliased from ETBP.NAME).
• PARTY_ID / LEGAL_ENTITY_ID – Party and legal entity to which the establishment belongs.
• MAIN_ESTABLISHMENT_FLAG – Indicates whether this is the main establishment.
• ACTIVITY_CODE / SUB_ACTIVITY_CODE – Establishment activity classification.
• TYPE_OF_COMPANY – Company type attribute of the establishment.
• ETB_EFFECTIVE_FROM / ETB_EFFECTIVE_TO – Effective dating of the establishment profile.
• REGISTRATION_NUMBER – Registration number from XLE_REGISTRATIONS; NULL when no registration exists.
• LEGISLATIVE_CAT_CODE – Legislative category code from the jurisdiction.
• LOCATION_ID – HR location identifier for the registered address.
• ADDRESS_STYLE – Address style from HR_LOCATIONS_ALL.STYLE, describing the address format.
• ADDRESS_LINE_1/2/3, TOWN_OR_CITY, REGION_1/2/3, POSTAL_CODE, COUNTRY – Address components sourced from HR_LOCATIONS_ALL.

Common Use Cases and Queries

A typical requirement is listing establishments with their registration, jurisdiction, and formatted address, optionally filtered by address style. For example:

• Retrieve all establishments and their address style: SELECT establishment_id, establishment_name, registration_number, address_style, country FROM apps.xle_establishment_v;
• Filter by a specific address style: SELECT establishment_name, address_line_1, town_or_city, postal_code FROM apps.xle_establishment_v WHERE address_style = :p_style;
• Identify establishments lacking a registration or address: SELECT establishment_id, establishment_name FROM apps.xle_establishment_v WHERE registration_number IS NULL;
• Report main establishments by legal entity: SELECT legal_entity_id, establishment_name, legislative_cat_code FROM apps.xle_establishment_v WHERE main_establishment_flag = 'Y';

Because the second UNION branch supplies NULLs for address and registration columns, queries relying on ADDRESS_STYLE should account for establishments without registration or location data by applying appropriate NVL or IS NOT NULL predicates.