This query compares the current and previous names and business units of selected positions as of a given effective date. It identifies the most recent updates to each position, showing when changes occurred and what the previous values were. This helps track organizational structure changes and supports audits or reporting on historical position movements.
This query returns the job and position grades for active primary employees from a given list. It uses current effective dates and joins assignment, job grade, and position data to show both the directly assigned grade and the default grade from the position. Ideal for identifying grade mismatches or validating employee grading structures in real time.
This query fetches active employees' current assignment and position names. It ensures records are current by checking effective dates and filters for primary, active employee assignments only. The output includes person number, assignment name, and position title—helpful for real-time reporting or validating workforce structure.
This query provides details of approval tasks configured in the HCM module, including task names, categories, subcategories, and their current status as enabled or bypassed. It helps identify how approvals are managed across different functional areas, offering insights into business process governance and compliance within the system.
This SQL query generates a real-time report of employee-to-manager relationships, showing the latest active assignments and display names for both. It ensures only current, primary records are included and supports filtering by manager or employee person number. The output highlights each employee’s reporting line, including their manager’s name, assignment status, and a unique sequence number for easy reference—ideal for auditing and organizational reporting.
This SQL query fetches detailed assignment information for a specific assignment ID in Oracle HCM Cloud. It ensures only active and primary assignments are retrieved by checking current effective dates and assignment status. The query joins key HCM tables including people, names, assignments, and periods of service to return the person number, assignment ID, work terms ID, and period of service ID, making it ideal for validating or enriching assignment-related data.
This SQL query retrieves the latest active and primary assignments for a list of person numbers in Oracle HCM. It splits comma-separated input values, filters data based on current effective dates and active statuses, and ensures only the most recent assignment per person is selected. The final output includes a sequence number, person number, assignment ID, work terms ID, and period of service ID in the original input order.
This query determines whether an employee's service record represents a Hire or Rehire based on multiple periods of service. It returns key dates like Hire Date, Rehire Date, and their corresponding Termination Dates, helping you track reemployment history. Use it to analyze employee rejoin patterns or verify compliance during audits and onboarding processes.
This SQL query helps HR teams extract essential details of terminated employees in Oracle HCM Cloud. It fetches person numbers, names, actual termination dates, rehire recommendations, and the usernames of users who submitted the terminations. By accepting a comma-separated list of person numbers as input, it filters and returns accurate, timely results for audit, rehire evaluation, or reporting purposes.
Now, without IT Security role, you can identified what all roles you have assigned and when this roles has assigned to you, and also who has assign this role to you user account.
If you don't have the security role assigned, no worries, now you have the access to do the role comparison between two users using SQL query.
This SQL query retrieves the home address details of all active US employees. It selects employee numbers, full names, assignment numbers, assignment status, and complete address details (address lines, city, state, postal code, and country). It joins PER_ALL_PEOPLE_F for employee data, PER_PERSON_NAMES_F for names, PER_ALL_ASSIGNMENTS_M for assignments, and PER_ADDRESSES_F for address details. The query filters active assignments with home addresses in the US.
This SQL query retrieves active employee assignment details (person number, assignment status, and assignment number) from HR tables. Only active employee assignments ('E' type) are included.
Get the person number, assignment id, workterm assignment id, and per period of service id
This SQL query retrieves distinct records of employees along with their names, supervisor names, and the department name of their managers. It joins person, assignment, supervisor, position, and organization tables, filtering for active and primary employee assignments. The query ensures only current effective records are included, providing a snapshot of reporting relationships and organizational hierarchy.
This SQL query fetches details of employees with the "Housing Allowance Deduction" element in Oracle HCM. It joins people, assignments, element types, entries, and payroll usage tables to list person number, assignment number, element name, and effective dates. The query filters based on active date ranges and ensures accurate linkage between element entries and payroll assignments.
This SQL query extracts detailed user account and role assignment data from Oracle HCM, including person number, name, user account status, and role assignment status. It uses multiple joins across PER_ALL_PEOPLE_F, PER_USERS, PER_USER_ROLES, and related role definition tables to determine if the user's role is active, terminated, or in a transitional state, providing a complete picture of user-role associations.
This SQL query returns a unique list of usernames, person numbers, role names, role codes, and departments by joining person, user, role, assignment, and department tables in Oracle HCM. It filters for primary employee assignments and ensures only active records in the current date range are included. This is ideal for user access audits, HR reporting, and department-level role mapping.
This SQL query lists all roles assigned to a specific user in Oracle HCM, including user status, role status, person number, full name, role name, common name, creator, and creation date. It joins PER_USERS, PER_USER_ROLES, PER_ROLES_DN, and PER_ROLES_DN_TL, filtering by the username. Useful for access audits, role validation, and security reviews.
This SQL query fetches UK-specific bank branch details from CE_BANK_BRANCHES_V, including the branch name, bank name, branch number, and SWIFT code. It filters records where BANK_HOME_COUNTRY = 'GB' and sorts results by bank name and branch name. Ideal for payroll configurations, payment method validations, and financial reporting.