Queries to get Employee related information – Fusion
Start typing and press Enter to search
- Setting up SQL Connect
- Connecting to Oracle Fusion Cloud
- Running Your First SQL Query
- Intellisense
- Database Browser
- Multi-Line Editing
- Setting Up Advance Table Security
- Exporting Data to CSV/Excel
- SQL IN ACTION
- Frequently Asked Questions (FAQ)
- Software License Agreement
- Installation Instructions
- Release Notes
- Privacy Policy
HCM Cloud – Time in Job, Time in Position Query
This query in Oracle HCM Cloud provides information on Employees and how much time they have been in a certain job or in a certain Position.
As you can see in the below screenshot from SQLConnect, this query provides Time in Job and Time in a particular position in YEARS. Copy this query and use it in SQLConnect – Get the data you want without even logging into BI tools for Oracle HCM cloud.
You can remove a few conditions from this query, if you want the time to be calculated even across global transfers/rehires.
Previous Post Oracle Cloud Source System IDs for all Business Objects
Next post direct and indirect reports for a manager.
- Quick Start
- SOFTWARE LICENSE AGREEMENT
- PRIVACY POLICY
© 2024 All Rights Reserved | Splash Business Intelligence Inc
Oracle Analytics Cloud and Server
- All Categories
- 12.3K Forums
- User Groups
- 1 Announcements
- Find Partners
- For Partners
- Hall of Fame: Spotlights
- Hall of Fame: Monthly Highlights
- Hall of Fame: Leaderboard
HCM | SQL query to get details of the employees belonging to departments below the selected departme
Requirement :
1) Report should display employees belonging to the departments of the selected organization type. 2) Report should also display employees belonging to departments below the selected departments as per organization hierarchy.
Scenario : Suppose the department of the employee is DEPARTMENT4.3 then the report should fetch the data of the employee whose department is DEPARTMENT4.3 along with the employees who belong to DEPARTMENT4.3.1, DEPARTMENT4.3.2, DEPARTMENT4.3.3, DEPARTMENT4.3.3.1.
Organization tree hierarchy :
NODE1:LEGAL EMPLOYER(ROOT)
NODE2: DEPARTMENT1
NODE3: DEPARTMENT1.1
NODE4: DEPARTMENT1.1.1
DEPARTMENT1.1.2
NODE3: DEPARTMENT1.2
NODE2: DEPARTMENT2
NODE2: DEPARTMENT3
NODE3: DEPARTMENT3.1
DEPARTMENT3.2
NODE2: DEPARTMENT4
NODE3: DEPARTMENT4.1
DEPARTMENT4.2
DEPARTMENT4.3
NODE4: DEPARTMENT4.3.1
DEPARTMENT4.3.2
DEPARTMENT4.3.3
NODE5: DEPARTMENT4.3.3.1
PS : I am able to achieve the first requirement by using attached SQL code but I need help to achieve the second requirement. I am not able to get any lead how should I accomplish the second requirement like does it require code modification or can be handled in template( currently using RTF template of excel type).
Code Snippet
- Oracle Fusion Analytics
Hi @Arti P , did you get a solution for this?
Dear All, Pls give me the complete (employee/person/worker) query to get all the details for these employees load through HDL worker.dat (worker,person name,person email,work relationship,work terms,assignments) please help me. thank you
Hi @User_Z86UI ,
Seems this is related to HCM product. Actually this forum is for Oracle Analytics Cloud and Server product, so please post your question/request in the proper forum. Try below forum and see if that helps.
https://community.oracle.com/customerconnect/categories/hcm-reporting-and-analytics-for-hcm
Learn to Grow
BIP – Query to Extract Position FTE values
When position hierarchy is enabled in Cloud HCM, Manage Positions UI shows some dynamically populated fields along with Parent position.
For example:
Use the below query to extract above details:
Human Capital Management
- Mar 24, 2023
Daily Used SQL Queries Library
Updated: Jan 30
Let us know in comment section what else queries you are looking for, we will be updating the Library accordingly
Common Lookup -
Value Set -
Document of Records -
Query to get Active and Terminated Employees in Last one Year duration -
Get Overtime Status of Job (EXEMPT/NONEXEMPT)
Get Employee Date of Birth(DOB)
Get Legal Entity, DOJ, PPG details based on the Month of Joining
Get the Work Schedule detail of employee
Get Department Details
Get Customer and Site details
Get User-defined table (UDT) Details
to be continued . . .
Recent Posts
Commonly Used Administrator Profile Values in HCM
How to Create a BIP Report in Oracle Fusion Hcm
Enabling Environmental Health and safety (EHS) module in HCM
- Manage VIP Account
- Register for VIP Plan
- VIP Member-Only Content
- HCM Data Loader
- HCM Extract
- BI Publisher
- Fast Formula
- OTBI Analytics
- Personalizations
- Scheduled Processes
- Absence Management
- Performance Management
- Talent Management
- Time & Labor
- HCM Extracts Questions
- HCM Data Loader Questions
- BI Reports Questions
- Report Issues/suggestions
- Your Feedback counts
- Write for Us
- Privacy Policy
- Join Telegram Group
- Join LinkedIn Group
- Join Facebook Page
Resolving Assignment Name and Job Name not in sync issue
- Post category: HCM Data Loader
- Post comments: 5 Comments
- Post last modified: June 16, 2020
- Reading time: 11 mins read
In this article we will look into details on why the assignment name and job names wouldn’t be in sync and we will also look into how to resolve this issue manually or using HDL file.
Table of Contents
Background of Assignment Name and Job Name:
When an Assignment has a Job assigned to it, the “Assignment Name” is by default set to either the name of that Job, if the assignment sequence is 1 (eg: Doctor 123), or a combination of the name of that Job and the Assignment Sequence, if the assignment sequence is greater than 1 (eg: Doctor 123-2).
- From then on if the Job assigned to the Assignment is ever changed, the “Assignment Name” changes automatically to match the new “Job Name”.
- However, if for some reason, the “Assignment Name” becomes out of sync with the “Job Name”, then the “Assignment Name” will no longer change automatically if the Job is ever changed.
- If user has manually updated the Assignment Name to something other than Job Name, then any change to the Job will not reflect in the Assignment Name for that employee as the Assignment Name is manually overridden.
To fix this Assignment Name and Job Name not in sync issue, Oracle suggests to run the “ Assignment Name and Job Name Mismatch – Report ” with blank input, so it identifies all employees with such issue and reports them on the output file. However the diagnostic test will not automatically fix the sync issues. We have to either manually fix the Assignment Name if there are very less employees with this issue or prepare the HDL file and load it to fix the issue. You can refer the Self-Service Data Integrity Framework for Employment Flows – Part 1 (Doc ID 2548287.1)
If you want to fix the Assignment Names manually, then refer Fusion Global HR: Why Assignment Title In Manage Employment page Is Not Correctly Displaying The Job As Expected. (Doc ID 2373918.1)
It’s better to create a BI Report that finds out all discrepancies and then do the HDL load instead of the Diagnostic report which takes up many hours to run for all employees.
We will check sample employee 275 who has an overridden Assignment Name and the job and assignment name is not in sync.
Now, we will run the below SQL Query for this employee and it will produce the HDL output to update the Top of Stack row of the employee. We need to copy rename it Worker.dat and load it.
Now save this file as Worker.dat and zip it and import this HDL file.
Data Exchange > Import and Load Data
We can see that the Job and Assignment names are in sync now after the HDL update.
You can run the below query to find out all employees who have a change in the Assignment Name and Job Name and generate the HDL file.
Disclaimer: Do your due diligence before proceeding with this load by checking if all employees returned by the query really have the issue or not. Even if some employee has an overridden assignment name, it will get updated to Job Name to make them in sync.
SQL Query to get the HDL file for all employees whose Job and Assignment Names are not in sync on the Top Of Stack row:
Feel free to modify any criteria as per your need. Like hardcoding person numbers or adding assignment status condition or if you want to update the history records too.
Note: The above query will compare the data on the Top Of Stack row only as of the current date. History and future dated records are not going to be impacted with this change.
You Might Also Like
How to load Calculation Card for US legislation?
Comparison of HDL, HSDL, and APIs for Data Load
Ready to use Worker Sample HDL files for multiple scenarios
Superb Bro… keep it up 🙂
Thanks for your compliment. Keep following for latest updates.
Nice work Sri
Thanks for your compliment Farhan. Keep following for latest updates.
I could validate our program logic with this thank you! We took it a step forward and built an ETL HCM Extract to retrieve and load corrected data into HCM. That way we can manage this ongoing issue.
Session expired
Please log in again. The login page will open in a new tab. After logging in you can close it and return to this page.
IMAGES
VIDEO
COMMENTS
AND PAAM.ASSIGNMENT_TYPE IN ('E', 'C') Here, I am selecting employees with Assignment Types 'E' (Employee in the system) and 'C' (Contingent Worker, contractor) where: — 'E' represents an employee currently active in the system. — 'C' represents a Contingent Worker who is not actively working yet (will start working ...
ASSIGNMENT_STATUS_TYPE='INACTIVE' was what I needed (As mentioned in the question, this solution is without considering 'EFFECTIVE_END_DATE') Getting the 'latest' assignment status of an employee was what I needed to find. ... AND PAPF.PERSON_ID=PAAM.PERSON_ID AND PAAM.PRIMARY_FLAG='Y' AND PAAM.ASSIGNMENT_STATUS_TYPE='INACTIVE' AND TRUNC ...
SELECT papf.person_number, ppnf.full_name employee_full_name, ppnf.first_name, ppnf.last_name, to_char(ppos.date_start, 'MM/DD/YYYY') date_start, paam.assignment_number, paam.primary_flag, paam.assignment_status_type active_status, paam.bargaining_unit_code, aapf.payroll_name, PAAM.ASS_ATTRIBUTE6 attribute_value FROM per_all_people_f papf, per_all_assignments_m paam, per_person_names_f ...
AND PCL.ASSIGNMENT_ID = PAAM.ASSIGNMENT_ID. AND PAAM.ASSIGNMENT_STATUS_TYPE IN ('ACTIVE', 'SUSPENDED') AND TRUNC(SYSDATE) BETWEEN PAAM.EFFECTIVE_START_DATE AND PAAM.EFFECTIVE_END_DATE ... CONNECT_ASSIGNMENT_ID, CONNECT_TYPE, JOB_ID. ORDER BY COUNT(1) DESC . Skip to Content; Skip to Search; Home; Help Center. Tables and Views for HCM. Search is ...
Courses. Some Commonly Used Queries in Oracle HCM Cloud. In any typical ERP Implementation, there are requirements to prepare multiple reports. Most of the Reports try to display the Assignment related details like Job, Grade, Location, Position, Department, BusinessUnit, Assignment Status along with other fields from different Tables.
I named it PAAM1 here because I will make another naming below as PAAM. Here, after selecting Assignment Types 'ET' and Assignment Status 'Active,' I confirm the validity of the records by bringing the system date with TRUNC SYSDATE (SYDATUM in Sap) and checking if the records are between the Effective Start Date (Begda in Sap) and End ...
Describes Tables and Views to integrate with or extend Oracle HCM Cloud.
SQL Query to get Time in Job and Time in Position Information: SELECT papf.person_number ,paam.assignment_number ,paam.assignment_status_type ,pjfv.name Job_Name ,hapfv.name Position_Name ,round ( ( months_between (sysdate, ( SELECT MIN (paam1.effective_start_date) FROM per_all_assignments_m paam1 WHERE paam1.person_id = paam.person_id AND ...
Query to Fetch Employee's Assignment Status. SELECT papf.person_number, pastt.user_status assignmentstatus. FROM per_all_people_f papf, per_all_assignments_m paam, per_assignment_status_types past, per_assignment_status_types_tl pastt. WHERE papf.person_id = paam.person_id.
SELECT papf.person_number ,paam.assignment_number ,paam.assignment_status_type ,pjfv.name Job_Name ,hapfv.name Position_Name ,round(( months_between(sysdate, ( SELECT MIN(paam1.effective_start_date) FROM per_all_assignments_m paam1 WHERE paam1.person_id = paam.person_id AND paam1.assignment_id = paam.assignment_id /* Remove this if you want the time to be calculated even across global ...
select Distinct papf.person_number, paam.assignment_number,ple.NAME legal_employer_name ,pd.name department_name ,ppnf.DISPLAY_NAME EMP_NAME ,ppnf.first_name ,ppnf.LAst_name , EX.SEGMENT1 ,EX.SEGMENT2 ,EX.SEGMENT3 ,EX.SEGMENT4 ,EX.SEGMENT5 ,EX.SEGMENT6 ,EX.SEGMENT7 ,paam.action_code ,PAAM.assignment_status_type FROM per_all_people_f papf, per_all_assignments_m paam ,per_legal_employers ple ,GL ...
Dear All, Pls give me the complete (employee/person/worker) query to get all the details for these employees load through HDL worker.dat (worker,person name,person email,work relationship,work terms,assignments) please help me. thank you
This is how the first part of the page looks like. Here we would mention the criteria for the employees to be part of this role mapping. As we can see, the Assignment Type should be 'Employee', Assignment Status should be 'Active' and the Resource Role should be 'Incentive Compensation Super User' to be eligible for this Role Mapping of 'Incentive Compensation Super User'.
In this article, we will look into how to query the backend tables to pull the flattened supervisor hierarchy of the employees on a single row. Supervisor information is stored with respect to the assignment information in PER_ASSIGNMENT_SUPERVISORS_F table and is linked to PER_ALL_ASSIGNMENTS_M table by ASSIGNMENT_ID.
BIP - Query to Extract Position FTE values. When position hierarchy is enabled in Cloud HCM, Manage Positions UI shows some dynamically populated fields along with Parent position. For example: Use the below query to extract above details: SELECT Positions.name "Position Name". ,Positions.FTE "Position Current FTE".
Daily Used SQL Queries Library. Updated: Jan 30. Let us know in comment section what else queries you are looking for, we will be updating the Library accordingly. Common Lookup -. select * from HCM_LOOKUPS where lookup_type = 'LOOKUP_NAME' and meaning = 'MEANING' and description = 'DESCRIPTION'. Value Set -.
SELECT PPN.DISPLAY_NAME FROM per_all_assignments_m asg, per_person_names_f ppn, PER_USERS PU WHERE 1 = 1 AND PPn.PERSON_ID = ASG.PERSON_ID AND TRUNC (SYSDATE) BETWEEN ASG.EFFECTIVE_START_DATE AND ASG.EFFECTIVE_END_DATE AND ASG.ASSIGNMENT_TYPE IN ('E', 'C') AND ASG.ASSIGNMENT_STATUS_TYPE = 'ACTIVE' AND ASG.PRIMARY_FLAG = 'Y' AND TRUNC (SYSDATE ...
from per_all_people_f papf join per_all_assignments_m paam on ( papf.person_id = paam.person_id ) join per_periods_of_service pps on ( pps.person_id = papf.person_id and pps.period_of_service_id = paam.period_of_service_id ) left join ( select assignment_id, date_from, salary_amount, currency_code, salary_basis_id, row_number() over( partition ...
To fix this Assignment Name and Job Name not in sync issue, Oracle suggests to run the " Assignment Name and Job Name Mismatch - Report " with blank input, so it identifies all employees with such issue and reports them on the output file. However the diagnostic test will not automatically fix the sync issues. We have to either manually ...
The above query is returning if I pass the hardcode value. Th effective_date, action_type is in the same table. If in the passed p_from_date and p_to_date the action_type is null, then the query should give the above result. Side note: You shouldn't transform dates to strings and then compare them.