• Post category:BI Publisher
  • Post comments:0 Comments
  • Post last modified:July 24, 2020
  • Reading time:2 mins read
You are currently viewing SQL Query to find the Position Incumbents
SQL Query to find the Position Incumbents

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.