379 total views
If the client is using position management, then there will be requirements to create reports to fetch the Positions and their incumbents.
Incumbents are the persons hired into those positions. If positions have open headcount more than 1, then multiple employees will be hired into that position.
If you want to know the current hired count into that position out of the total open headcount, then you need to create a report based on the position and assignment tables to get this information.
SQL Query for Position Incumbents:
SELECT hap.name, ( SELECT name FROM per_grades_x pg, PER_VALID_GRADES_F pvg WHERE pg.grade_id IN (pvg.grade_id) AND pvg.position_id = hap.position_id ) valid_grade, ( SELECT name FROM per_grades_x pg, PER_VALID_GRADES_F pvg WHERE pg.grade_id IN (pvg.grade_id) AND pvg.position_id = hap.position_id ) existing_grade, ( SELECT count(paaf.person_id) FROM per_all_assignments_f paaf, per_periods_of_service_v ppos WHERE paaf.person_id = ppos.person_id AND paaf.assignment_type = 'E' AND paaf.primary_assignment_flag = 'Y' AND sysdate BETWEEN paaf.effective_start_date AND paaf.effective_end_date AND paaf.position_id = hap.position_id AND ( ppos.actual_termination_date IS NULL OR ppos.actual_termination_date >= trunc(sysdate) ) ) incumbants, hap.attribute10 average_ctc, hap.attribute11 minimum_ctc, hap.attribute13 age, hap.attribute14 Education, hap.attribute15 experience, hap.attribute16 finance_budget, hap.attribute12 maximum_annucal_ctc2 FROM HR_ALL_POSITIONS_X hap
Apart from this, we have lot of OTBI reports available on Cloud Connect in Report sharing Center which provides Length in Position, Position Incumbent History, etc. Please go through them if you have similar requirement.
Latest posts by Sricharan Monigari (see all)
- Renaming HCM Extracts is a possibility from 20D - November 24, 2020
- Sangam20 – Oracle Users Group Conference - November 23, 2020
- Passing Parameter values from Flow Instance to HDL Transformation Formula - November 23, 2020
- Solve manual FTP server update issue for HCM Extracts post every P2T refresh - November 16, 2020