Position Data Query

 with Position_FTE as (SELECT Positions.position_id position_id

      ,Positions.FTE Position_Current_FTE

      ,Positions.INCUMBENT_FTE      CurrentIncumbentFTE

      ,(Positions.FTE - Positions.INCUMBENT_FTE)      Difference_FTE

  FROM   

(SELECT HAPFT.NAME,

HAPFT.position_id,

        HAPF.FTE, 

    (select SUM(PAWMF.VALUE)

           from PER_ALL_ASSIGNMENTS_M PAAM,

                PER_ASSIGN_WORK_MEASURES_F PAWMF

  where 1=1

            AND PAAM.POSITION_ID = HAPF.POSITION_ID 

            AND SYSDATE  BETWEEN PAAM.EFFECTIVE_START_DATE AND PAAM.EFFECTIVE_END_DATE

AND SYSDATE  BETWEEN PAWMF.EFFECTIVE_START_DATE AND PAWMF.EFFECTIVE_END_DATE

            AND PAAM.ASSIGNMENT_TYPE = 'E'

    AND PAAM.ASSIGNMENT_STATUS_TYPE = 'ACTIVE'

            AND PAAM.ASSIGNMENT_ID = PAWMF.ASSIGNMENT_ID

            AND PAWMF.UNIT = 'FTE') AS INCUMBENT_FTE

   FROM HR_ALL_POSITIONS_F HAPF, 

        HR_ALL_POSITIONS_F_TL HAPFT

  WHERE HAPF.POSITION_ID = HAPFT.POSITION_ID 

    AND USERENV('LANG') = HAPFT.LANGUAGE 

    AND TRUNC(SYSDATE)  BETWEEN HAPF.EFFECTIVE_START_DATE AND HAPF.EFFECTIVE_END_DATE

    AND TRUNC(SYSDATE)  BETWEEN HAPFT.EFFECTIVE_START_DATE AND HAPFT.EFFECTIVE_END_DATE

  ORDER BY HAPFT.NAME ) Positions)

SELECT 

  HLPF.position_id, 

  HLPF.position_code, 

  HLPF.effective_start_date, 

  HLPF.effective_end_date, 

  HLPF.location_id, 

  HLPF.active_status, 

  HLPF.supervisor_id, 

  HLPF.supervisor_assignment_id, 

  HLPF.permanent_temporary_flag, 

  HLPF.fte, 

  HLPF.hiring_status, 

  HLPF.full_part_time, 

  HLPF.standard_working_frequency AS FREQUENCY, 

  HLPF.position_type, 

  HLPF.working_hours, 

  HLPF.overlap_allowed, 

  HLPF.seasonal_flag AS SEASONAL, 

  HLPF.NAME, 

  HLPF.budgeted_position_flag AS BUDGETED_POSITION, 

  HLPF.assignment_category, 

  --HLPF.entry_grade_id AS ENTRY_GRADE, 

  HLPF.standard_working_frequency, 

  HLPF.standard_working_hours, 

  --HLPF.grade_ladder_id, 

  (

    SELECT 

      haou.NAME 

    FROM 

      hr_all_organization_units haou 

    WHERE 

      haou.organization_id = hlpf.business_unit_id

  ) BU_NAME, 

  hlpf.organization_id DEPT_ID, 

  (

    SELECT 

      haou.NAME 

    FROM 

      hr_all_organization_units haou 

    WHERE 

      haou.organization_id = hlpf.organization_id

  ) DEPT_NAME, 

  (

    SELECT 

      hla.LOCATION_NAME 

    FROM 

      HR_LOCATIONS_ALL_F_VL hla 

    WHERE 

      hla.location_id = HLPF.location_id

  and trunc(sysdate) between hla.effective_start_date and hla.effective_end_date 

  ) LOC_NAME, 

  hlpf.max_persons head_count, 

  (

    SELECT 

      par.action_reason 

    FROM 

      per_action_occurrences pao, 

      per_action_reasons_vl par 

    WHERE 

      pao.action_reason_id = par.action_reason_id 

      AND pao.action_occurrence_id = HLPF.action_occurrence_id

  ) ACTION_REASON, 

  hlpf.ATTRIBUTE2 Regular_Shift, 

  hlpf.ATTRIBUTE3 Payroll_location, 

  hlpf.ATTRIBUTE5 HR_Department, 

  pjfv.job_code,

  (select pg.name from per_grades_f_vl pg where pg.grade_id=hlpf.entry_grade_id

  and trunc(sysdate) between pg.effective_start_date and pg.effective_end_date )  Entry_Grade,

  (SELECT pglf.name

        FROM   per_grade_ladders_f_vl pglf

        WHERE  pglf.grade_ladder_id = pjfv.grade_ladder_id

               AND Trunc(SYSDATE) BETWEEN pglf.effective_start_date AND

                                          pglf.effective_end_date)

       grade_ladder,

hlpf.ATTRIBUTE1 Salary_Basis_name,

hlpf.ATTRIBUTE4 Job_Car_Eligibility ,

  pjfv.name Job,

  (select ppnf.full_name from per_person_names_f ppnf where ppnf.name_type='GLOBAL' and ppnf.person_id=hlpf.supervisor_id

  and trunc(sysdate) between ppnf.effective_start_date and ppnf.effective_end_date 

  ) Manager,

  (select  HLPF_par.name from PER_POSITION_HIERARCHY_F phr,hr_all_positions_f_vl HLPF_par

  where phr.parent_position_id = hlpf_par.position_id

  and trunc(sysdate) between phr.effective_start_date 

  and phr.effective_end_date 

  and trunc(sysdate) between HLPF_par.effective_start_date 

  and HLPF_par.effective_end_date 

  and phr.position_id = HLPF.position_id) parent_position,

  hlpf.ATTRIBUTE10 Local_position_name,

  hlpf.ATTRIBUTE13 Local_position_Desc,

  pfte.Position_Current_FTE current_position_fte, 

  pfte.CurrentIncumbentFTE,

  pfte.Difference_FTE,

  --DELEGATE_POSITION_ID,

  (select  HLPF_del.name from hr_all_positions_f_vl HLPF_del

  where HLPF_del.position_id = HLPF.DELEGATE_POSITION_ID

  and trunc(sysdate) between HLPF_del.effective_start_date and HLPF_del.effective_end_date) Delegate_position,

  (select ppleg.INFORMATION1 from PER_POSITION_LEG_F ppleg where ppleg.legislation_code='BR'

  and trunc(sysdate) between ppleg.effective_start_date and ppleg.effective_end_date

  and ppleg.position_id=hlpf.position_id)  CBO_Occupation,

  hlpf.FUNDED_BY_EXISTING_POSITION,

  hlpf.COST_CENTER,

  hlpf.ENTRY_STEP_ID,

  hlpf.ATTRIBUTE8 Officer_Code,

  hlpf.ATTRIBUTE9 EBA,

  hlpf.last_updated_by,

  hlpf.last_update_date,

  (select pg.name from per_grades_f_vl pg,PER_VALID_GRADES_F pvgf where 1=1 

  and pvgf.position_id= hlpf.position_id

  and pvgf.grade_id = pg.grade_id

  and trunc(sysdate) between pg.effective_start_date and pg.effective_end_date  

  and trunc(sysdate) between pvgf.effective_start_date and pvgf.effective_end_date and rownum=1) grade,

  (select pg.grade_code from per_grades_f_vl pg,PER_VALID_GRADES_F pvgf where 1=1 

  and pvgf.position_id= hlpf.position_id

  and pvgf.grade_id = pg.grade_id

  and trunc(sysdate) between pg.effective_start_date and pg.effective_end_date  

  and trunc(sysdate) between pvgf.effective_start_date and pvgf.effective_end_date and rownum=1) grade_code,

  (select p.name from PER_GRADE_STEPS_F_VL p where p.GRADE_STEP_ID = hlpf.ENTRY_STEP_ID

  and trunc(sysdate) between p.effective_start_date and p.effective_end_date  ) Entry_Step,

  (select fss.SET_CODE from per_grades_f_vl pg,PER_VALID_GRADES_F pvgf,FND_SETID_SETS fss where 1=1 

  and pvgf.position_id= hlpf.position_id

  and pvgf.grade_id = pg.grade_id

  and pg.set_id = fss.set_id

  and trunc(sysdate) between pg.effective_start_date and pg.effective_end_date  

  and trunc(sysdate) between pvgf.effective_start_date and pvgf.effective_end_date and rownum=1) set_code

FROM 

  hr_all_positions_f_vl HLPF, 

  per_jobs_f_vl pjfv ,

  Position_FTE pfte

WHERE 

  1 = 1 

  and HLPF.job_id = pjfv.job_id 

  and HLPF.position_id = pfte.position_id(+)

  and trunc(sysdate) between hlpf.effective_start_date 

  and hlpf.effective_end_date 

  AND Trunc(SYSDATE) BETWEEN pjfv.effective_start_date 

  AND pjfv.effective_end_date

  and hlpf.position_code in ( '123')