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.
This query retrieves employees from Oracle HCM whose records are associated with termination details by joining PER_ALL_PEOPLE_F and PER_PERIODS_OF_SERVICE tables. It displays the last update date, last updated by, and person number, helping track recently updated termination records.
This query retrieves the Seniority Date Version, Seniority Date Code, and corresponding Meaning from Oracle HCM. It joins the PER_SENIORITY_DATES_SETUP and HCM_LOOKUPS tables to provide active configurations used in determining employee seniority calculations.
This query retrieves failed approval transactions from Oracle HCM by checking the status and state of transactions in the HRC_TXN_HEADER and HRC_TXN_DATA tables. It helps identify transactions that encountered issues during the approval workflow process.
This SQL query retrieves the Areas of Responsibility (AOR) assigned to employees in Oracle HCM. It includes Person Number, Display Name, Assignment Number, AOR Type, Scope (BU/Department/Legal Entity), Responsibility Name, and Status. The query joins assignment, person, responsibility, and organizational tables to provide a complete AOR mapping.
To get the Person ID of an employee in Oracle HCM, use the PER_ALL_PEOPLE_F table and filter it using the Person Number. This helps identify the internal unique identifier associated with the employee.
This query provides a detailed employee profile, including department hierarchy (up to 9 levels), job title, assignment details, manager and second manager information, project code, and expense account. It also integrates Oracle HCM’s organization tree structure to display multi-level departmental relationships using a flattened hierarchy.
This query retrieves Content Item IDs for records categorized under Licenses and Certifications in Oracle HCM. It includes content type, item code, name, description, and audit details by joining content item base, translations, and type context tables filtered by context name as 'CERTIFICATION'.
This query retrieves approval transaction details in Oracle HCM, including transaction status, workflow task information, initiator details, error logs, and assigned approvers. It joins transaction headers, transaction data, workflow tasks, error logs, and person records to provide a complete view of an approval transaction's progress and current state.