SQL Query to find the Position Incumbents
SQL Query to find the Position Incumbents

 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.

Join our fastest growing technology social groups to interact with experts in the Fusion space and get your queries resolved – Telegram group, Facebook Page and LinkedIn Group

The following two tabs change content below.
Sricharan is a Fusion HCM Solution Architect with over 13+ years of overall experience and 5+ years of Fusion experience. He is passionate about technical aspects of Fusion HCM and writes interesting articles on HCM Extracts, HCM Data Loader, Fast Formula, BI Publisher, Integrations and Automation etc.

Leave a Reply