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.
This SQL query detects employees whose termination has been reversed in Oracle HCM Cloud. It compares versions of PER_PERIODS_OF_SERVICE records, identifying those with earlier entries showing a termination date and later updates removing it. The query returns the person ID, last update date, and person number of affected employees.
This SQL query returns the total number of person records in Oracle HCM Cloud who are in PER_PERIODS_OF_SERVICE but do not have a work email ID (EMAIL_TYPE = 'W1'). It uses a NOT EXISTS clause to filter out individuals without an assigned work email, ensuring only active person records are considered.
This SQL query fetches active job family details from Oracle HCM Cloud, including job family name, code, status, and effective start date. It ensures only currently effective records are shown by filtering based on the system date and joins the translation and base tables for complete information.
This SQL query retrieves a list of legal employers and their associated IDs using Oracle HCM tables. It filters for active records classified under 'HCM_LEMP', ensuring the data is current based on the system date and language set to 'US'.
This SQL query fetches a list of business units along with their corresponding IDs and classification codes in Oracle HCM. It joins classification, organization unit, and translation tables while filtering for records classified as 'FUN_BUSINESS_UNIT' and active as of the current date.
This query returns a list of active departments in Oracle HCM, including each Department ID and Department Name. It filters by the 'DEPARTMENT' classification code and ensures only currently effective records are shown in the output.
This query retrieves the department hierarchy structure in Oracle HCM using the Department Tree. It shows both the department name and its parent department, along with tree metadata such as tree code, tree node ID, and hierarchical keys. The query joins department nodes with organizational units and filters only active tree versions.
These SQL queries generate HDL-formatted lines for loading WorkTerms and Assignments in Oracle HCM via HCM Data Loader (HDL). Each query outputs HDL MERGE action lines, including employee person number, assignment details, and other required fields based on per_all_assignments_m.
This query provides a list of System Person Types and User Person Types along with their Active Flag and Default Flag status in Oracle HCM. It helps identify which person types are active and which are marked as default in the system configuration.
This query retrieves details of the active department tree structure in Oracle HCM, including tree version ID, structure code, and tree code. It helps identify the current version of the Parent Department Tree being used within the organization.
This query retrieves department hierarchy information along with project and expense codes by joining Oracle HCM’s organization and tree structures. It displays the department name, parent department name, project code, and expense code, using hierarchical CONNECT BY logic to trace the structure.
This query retrieves a list of email addresses from Oracle HCM and identifies whether each is marked as the employee's primary email address. It compares the email address ID with the person's primary email ID and flags it accordingly.