Search Results etb_effective_to

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

• ESTABLISHMENT_ID — primary identifier of the establishment profile.
• ESTABLISHMENT_NAME, PARTY_ID, LEGAL_ENTITY_ID, MAIN_ESTABLISHMENT_FLAG, ACTIVITY_CODE, SUB_ACTIVITY_CODE, TYPE_OF_COMPANY — core establishment attributes drawn from XLE_ETB_PROFILES.
• ETB_EFFECTIVE_FROM and ETB_EFFECTIVE_TO — the establishment's own effective date range, aliased from etbp.effective_from and etbp.effective_to. The alias prefix distinguishes these from the registration effective dates. A user searching for "etb_effective_to" is looking for this column, and it is the effective end date of the establishment profile itself as opposed to the registration.
• REGISTRATION_NUMBER, LEGISLATIVE_CAT_CODE, EFFECTIVE_FROM, EFFECTIVE_TO, LOCATION_ID — registration and jurisdiction attributes; NULL for establishments without registrations.
• ADDRESS_STYLE and the address line, city, region, postal code, and country columns — the registered address from HR_LOCATIONS_ALL.

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;