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.
This query retrieves absence details such as Absence Entry ID, Absence Type ID, Absence Type Name, Start Date, End Date, and duration for a given employee. It joins absence entries, absence types, people, and service periods to ensure accurate and current data is fetched.
This SQL condition filters out only active employees by checking assignment status and date range validity and ensuring no overlapping inactive status. It considers assignment types, termination dates, and the latest adequate records to identify actively employed individuals accurately.
This query retrieves key information related to Goal Plan Assignments, including Goal Plan Assignment ID, Goal Plan ID, Assignment ID, Goal Plan Name, and Person Number. It filters the results for a specific goal plan and ensures the correct language translation is applied.
To identify employees with duplicate email addresses in Oracle HCM, use SQL to group records by email address and filter those appearing more than once. This helps ensure data quality and prevents email-related issues in communications and integrations.
This query retrieves distinct person numbers from Oracle HCM where the employee exists in the profiles table but doesn't have a profile code assigned. It helps identify incomplete or missing profile information for cleanup or data validation purposes.
To find the seniority date version in Oracle HCM, query the PER_SENIORITY_DATES_SETUP table along with HCM_LOOKUPS to get the seniority date code, its meaning, and the corresponding version. This helps ensure accurate tracking of employee service calculations.
To determine if Rich Media services are enabled in your Oracle Fusion environment, you can query the ASK_DEPLOYED_APPLICATIONS table. This helps verify the deployment of services like video storage, transcoding, and content delivery.
This query retrieves employee assignment and Area of Responsibility (AOR) details, including Person Number, Name, Assignment Number, AOR Type, Scope Value, Responsibility Name, Scope Name (Business Unit, Department, or Legal Entity), and Responsibility Status. It joins assignment, person, responsibility, and organizational data while ensuring active and current records are displayed.
SELECT ACTIONSB.ACTION_CODE AS A_CODE, ACTIONS.ACTION_NAME AS A_NAME, USAGES.ACTION_REASON_CODE AS U_REASON_CD, REASONS.ACTION_REASON AS ACTION_REASON
In this blog post, I will show you how to query the Fusion.hrt_profile_typ_sections_vl view to get the names and IDs of the template-based sections. This view contains information about the sections that are defined in the profile-type templates. Template-based sections are those that have the template_based_section_flag set to 'Y'. To get the list of these sections, we can use the following SQL statement:
select papf_Reportee.person_number, to_char(pasf.EFFECTIVE_START_DATE,'yyyy/dd/mm') start_dt_spr from per_all_people_f papf, per_All_assignments_m paam, PER_ASSIGNMENT_SUPERVISORS_F pasf,
Each document type has a unique ID, a category code, a sub-category code, a legislation code, and a creation date The following query will select all the columns from the table and sort them by the creation date in descending order