Search Results establishment_name




Overview
Explore in AI Chat

Overview

APPS.XLE_ESTABLISHMENT_V is a reporting and integration view in the Oracle E-Business Suite Legal Entity Configurator (also referred to as ETRM, the E-Business Tax and legal entity regime module). It presents establishment (also known as "ETB profile") records held in the XLE_ETB_PROFILES table, enriched with their associated registration details, registered address attributes, and jurisdiction legislative category information. The view is owned by APPS and is intended to provide a single, denormalized source of establishment data for inquiries, reports, extracts, and downstream integrations without requiring callers to join the underlying registration and location tables themselves.

A distinguishing structural feature of the view is that it is defined as a UNION of two queries. The first query returns establishments that have at least one identifying registration (identified by identifying_flag = 'Y'), joining registration, address, and jurisdiction data. The second query returns establishments that have no registrations at all, exposing the establishment-level attributes while returning NULL for all registration, address, and jurisdiction columns. This design ensures that every establishment in XLE_ETB_PROFILES appears at least once in the view, whether or not registration data exists.

Underlying Base Objects

The view is defined over four documented objects:

  • XLE_ETB_PROFILES (synonym) — the primary establishment profile table, aliased etbp. It supplies the establishment identifier, name, party, legal entity, main establishment flag, activity codes, company type, and the establishment effective dates.
  • XLE_REGISTRATIONS (synonym) — aliased reg. Joined on etbp.establishment_id = reg.source_id with reg.source_table = 'XLE_ETB_PROFILES' and reg.identifying_flag = 'Y'. It supplies the registration number, registration effective dates, and the location and jurisdiction references.
  • HR_LOCATIONS_ALL (synonym) — aliased hrl. Joined on hrl.location_id = reg.location_id. It supplies the registered address attributes, including the address style and address lines through country.
  • XLE_JURISDICTIONS_VL (view) — aliased jur. Joined on jur.jurisdiction_id = reg.jurisdiction_id. It supplies the legislative category code.

The second branch of the UNION references only XLE_ETB_PROFILES, using a NOT IN subquery against XLE_REGISTRATIONS.

Key Columns

Common Use Cases and Queries

Typical uses include listing establishments by effective date, locating establishments for a legal entity, and extracting registration data for compliance reporting.

  • Active establishments for a legal entity: SELECT establishment_id, establishment_name FROM apps.xle_establishment_v WHERE legal_entity_id = :p_legal_entity_id AND (etb_effective_to IS NULL OR etb_effective_to >= SYSDATE);
  • Establishments with a registration number: SELECT establishment_name, registration_number, legislative_cat_code FROM apps.xle_establishment_v WHERE registration_number IS NOT NULL;
  • Establishments lacking registrations (the UNION's second branch): SELECT establishment_id, establishment_name FROM apps.xle_establishment_v WHERE registration_number IS NULL;