In this article we will look into how to get the Time in Job and Position information for an employee from assignment information.
Want to learn Fusion Technical tools? Checkout this article
You might also be interested in the below posts on BI Publisher:
- Adding hyperlink in Email using BI Bursting
- Security Matrix Users and their Roles
- Execute Reports from Navigator without exposing the BI Catalog
- How to modify XML tags in BI Publisher Output?
- Using Session Variables in BI Reports to get logged in user info
- SQL Query to fetch Flattened Organization Hierarchy Tree
- SQL Query to get the HDL file from staging tables
- SQL Query to get User last login information
- How to default date parameters in BI Report dynamically?
- SQL Query to get Geography Information
- Password Protect Excel Output in BI Publisher
- Prevent Merging Columns in Excel Output using RTF template
- SQL Query to get Fast Formula and related info
- Most Frequently Used SQLs for List of Values (LOVs)
- Most frequently used Date, Number and String functions
- Bursting attachment to Email along with html content in body
- How to get the Payroll Balance details in BI Report?
- Scheduling BIP Report using SOAP Services?
- Etext template tips and tricks Part 1
- How to get Position Hierarchy in SQL Query?
- SQL Query to get Source System IDs for all Business Objects
- Etext template tips and tricks Part 2
- Test/Preview/Troubleshoot eText templates
- Fix for BI reports generating stale/old data due to Cache options
- Creating Excel templates and troubleshooting them
Even though it is possible to get this information using OTBI we cannot achieve both Time in Job and Time in Position in a single OTBI analysis and we do not have the flexibility to choose if you want to group by the assignment or not. So it’s better to get this information is through a BI Report if you have some hands-on with SQL Query.
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 paam1.assignment_id = paam.assignment_id /* Remove this if you want the time to be calculated even across global transfers/rehires */ AND paam1.assignment_type NOT IN ('ET','CT','PT') AND paam1.job_id = paam.job_id AND NOT EXISTS ( SELECT 'x' FROM per_all_assignments_m paam2 WHERE paam2.person_id = paam.person_id AND paam2.assignment_id = paam.assignment_id /* Remove this if you want the time to be calculated even across global transfers/rehires */ AND paam2.job_id <> paam1.job_id AND paam2.assignment_type NOT IN ('ET','CT','PT') AND paam2.effective_start_date > paam1.effective_start_date ) )) / 12 ), 2) time_in_job_years ,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 transfers/rehires */ AND paam1.assignment_type NOT IN ('ET','CT','PT') AND paam1.position_id = paam.position_id AND NOT EXISTS ( SELECT 'x' FROM per_all_assignments_m paam2 WHERE paam2.person_id = paam.person_id AND paam2.assignment_id = paam.assignment_id /* Remove this if you want the time to be calculated even across global transfers/rehires */ AND paam2.position_id <> paam1.position_id AND paam2.assignment_type NOT IN ('ET','CT','PT') AND paam2.effective_start_date > paam1.effective_start_date ) )) / 12 ), 2) time_in_position_years FROM per_all_assignments_m paam ,per_all_people_f papf ,HR_ALL_POSITIONS_F_VL hapfv ,PER_JOBS_F_VL pjfv WHERE papf.person_id = paam.person_id AND paam.assignment_type NOT IN ('ET','CT','PT') AND paam.assignment_status_type = 'ACTIVE' AND paam.primary_flag = 'Y' AND paam.effective_latest_change = 'Y' AND paam.position_id = hapfv.position_id(+) AND paam.job_id = pjfv.job_id(+) AND sysdate BETWEEN pjfv.effective_start_date(+) AND pjfv.effective_end_date(+) AND sysdate BETWEEN hapfv.effective_start_date(+) AND hapfv.effective_end_date(+) AND sysdate BETWEEN papf.effective_start_date AND papf.effective_end_date AND sysdate BETWEEN paam.effective_start_date AND paam.effective_end_date
Tip: This query gives you the information of the Time in Job and Position for the primary active assignment as of system date. If you want to modify any criteria feel free to modify and use it as per your requirement.