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.
This SQL query retrieves a list of bank names from the CE_BANKS_V view in Oracle HCM for banks located in the United Kingdom (HOME_COUNTRY = 'GB'). The results are ordered alphabetically by bank name, making it useful for payroll setup, validations, or user interface dropdowns.
This SQL query retrieves active grade names and their corresponding grade codes from Oracle HCM. It joins PER_GRADES_F and PER_GRADES_F_TL, filters for U.S. English language entries, active grades (ACTIVE_STATUS = 'A'), and ensures the grades are valid as of the current date. The output is sorted by grade code, making it suitable for payroll, HR analytics, or data mapping.
This SQL query extracts a detailed list of positions in Oracle HCM, including position name, business unit, department, job name, job code, and grade. It joins HR_ALL_POSITIONS_F, PER_JOBS_F, PER_GRADES_F, and organizational unit tables, filtering active records based on SYSDATE. The result offers a complete view of position attributes for reporting or auditing purposes.
This SQL query fetches a current list of job names and their corresponding job codes from Oracle HCM by joining PER_JOBS_F_TL and PER_JOBS_F. It filters for active jobs (based on the current date and effective date range) and selects only records in the English language (LANGUAGE = 'US'). The output is sorted by job name and job code for easy reference.
This SQL query retrieves content item codes and names from Oracle HCM for specified context types such as Certifications, Training, Skills, and custom-defined types. It joins HRT_CONTENT_ITEMS_B, HRT_CONTENT_ITEMS_TL, and HRT_CONTENT_TYPES_B to display content items along with their associated context names, enabling efficient classification and reporting of workforce qualifications and competencies.
This SQL query fetches active lookup values from Oracle Fusion’s common lookup types, including lookup type, code, meaning, tag, and application name. It filters based on specific lookup types like TITLE, EMAIL_TYPE, and SEX, and ensures only enabled and date-effective values are returned in the user’s current session language.
This SQL query fetches all profile items associated with a specific person number (e.g., 7662) in Oracle HCM. It returns the profile code, content type, content item name, active date range, and audit details such as creator and last update information. Useful for analyzing worker skills, competencies, certifications, or qualifications stored in Talent Profile data.
This SQL query retrieves detailed message logs for a specific HDL (HCM Data Loader) upload using the UCM_CONTENT_ID. It joins message lines with related business object and data set tables to provide insight into successes, warnings, and errors during the HDL process. Replace 'UCMFA03221083' with the relevant content ID to check specific load results.