|

BIP – Query to find legal entity registration number

ByMandeep Gupta
BIP – Query to find legal entity registration number in fusion – MSPL
Home › Fusion HCM › Reports ›

Overview

In Oracle Fusion HCM, each Legal Entity is assigned a registration number that uniquely identifies it within a Payroll Statutory Unit. This registration number is stored at the establishment level and linked via the XLE_REGISTRATIONS table. The query below retrieves the identifying registration number for all active legal employers.

SQL Query

The following query can be used in a BIP report to extract the legal entity name along with its registration number:

SELECT ple.name
      ,xeb.legal_entity_id
      ,xeb.establishment_id
      ,xr.registration_number
  FROM xle_etb_profiles xeb
      ,xle_registrations xr
      ,per_legal_employers ple
WHERE xr.source_id = xeb.establishment_id
  AND xr.source_table = 'XLE_ETB_PROFILES'
  AND xeb.legal_entity_id = ple.legal_entity_id
  AND TRUNC(SYSDATE) BETWEEN ple.effective_start_date AND ple.effective_end_date
  AND ple.status = 'A'
  AND xr.identifying_flag = 'Y'
ORDER BY ple.name

Tables Used

  • xle_etb_profiles — Stores establishment profile data for each legal entity.
  • xle_registrations — Holds registration numbers associated with establishments.
  • per_legal_employers — HCM legal employer table with effective dating and status.

Key Filter Conditions

  • source_table = ‘XLE_ETB_PROFILES’ — Scopes registrations to establishment-level records.
  • TRUNC(SYSDATE) BETWEEN effective_start_date AND effective_end_date — Returns only currently active legal employers.
  • status = ‘A’ — Filters for active legal employer records only.
  • identifying_flag = ‘Y’ — Retrieves the primary identifying registration number only.

Verifying via Setup and Maintenance UI

The registration number can be cross-checked through the Oracle Fusion UI using the Manage Legal Entity HCM Information task. Follow the steps below:

  1. 1
    Log in to Oracle Fusion and navigate to Setup and Maintenance from the Navigator menu.
  2. 2
    In the task search bar, type Manage Legal Entity HCM and press Enter.

    Setup and Maintenance → Search: “Manage Legal Entity HCM”
  3. 3
    Click on Manage Legal Entity HCM Information from the search results (Type: Task) to open the task.
  4. 4
    The task lists all configured legal entities along with their Legal Employer flag, Payroll Statutory Unit flag, and Legal Entity Identifier.

Similar Posts