Oracle HCM SQL Toolkit – Part 1: Country, Legal Employer and Employee Count

Oracle HCM SQL Toolkit – Part 1: Country, Legal Employer and Employee Count

Over time, every Oracle HCM consultant builds a personal collection of SQL queries that become part of their everyday toolkit.

Some queries are useful for troubleshooting. Others help with reporting, validation, integrations, payroll, or simply understanding how an Oracle HCM environment is structured.

This series, Oracle HCM SQL Toolkit, is intended to build a practical library of those frequently used queries.

In Part 1, we will look at a simple but very useful query:

How many Legal Employers are configured, which countries do they belong to, and how many active employees are associated with each Legal Employer?

The Query

SELECT
    ple.name AS legal_employer,
    xep.le_information_context AS country_code,
    (
        SELECT COUNT(paaf.assignment_id)
        FROM per_all_assignments_f paaf
        WHERE paaf.legal_entity_id = ple.organization_id
          AND TRUNC(SYSDATE) BETWEEN paaf.effective_start_date
                                 AND paaf.effective_end_date
          AND paaf.primary_flag = 'Y'
          AND paaf.assignment_type = 'E'
          AND paaf.assignment_status_type_id = 1
    ) AS employee_count
FROM xle_entity_profiles xep
JOIN per_legal_employers ple
    ON xep.legal_entity_id = ple.legal_entity_id
WHERE xep.legal_employer_flag = 'Y'
  AND ple.status = 'A'
--AND xep.le_information_context = 'US'
ORDER BY ple.name;

Sample Output

Legal Employer	Country Code	Employee Count
Vision US LLC	US	12,450
Vision Canada Ltd	CA	2,180
Vision UK Ltd	GB	3,925
Vision India Pvt Ltd	IN	8,740

Why Keep This Query Handy?

This query provides a quick view of the organization's HCM footprint.

• Which Legal Employers exist
• Which country each Legal Employer belongs to
• Employee population by Legal Employer
• Which Legal Employers have large populations
• Whether any Legal Employers have unexpectedly small populations

It is useful during enterprise-structure discussions, Payroll analysis, reporting, data validation, troubleshooting, and general environment discovery.

Oracle HCM SQL Toolkit – Part 1 Recap

This query provides a simple view of:

Country → Legal Employer → Employee Count

It is easy to understand, easy to modify, and useful across many Oracle HCM functional and technical scenarios.