Postion Hierarchy Details with Superior in Oracle HRMS

SELECT peha.position_structure_id,
  peha.EMPLOYEE_ID,
  fndu.user_id,
  ppos.POSITION_ID,
  pps.name Hierarchy,
  fndu.USER_NAME UserName,
  papf.FULL_NAME Person,
  ppos.NAME Position,
  peha.SUPERIOR_LEVEL SuperiorPositionLevel,
  ppos2.NAME SuperiorPosition,
  papf2.FULL_NAME SuperiorPerson,
  fndu2.USER_NAME SuperiorUsername,
  peha.SUPERIOR_ID SuperiorPersonId,
  fndu2.user_id SuperiorUserId,
  ppos2.POSITION_ID SuperiorPosId
FROM PO_EMPLOYEE_HIERARCHIES_ALL peha,
  PER_POSITIONS ppos,
  PER_POSITIONS ppos2,
  per_all_people_f papf,
  per_all_people_f papf2,
  fnd_user fndu,
  fnd_user fndu2,
  per_position_structures pps
WHERE pps.business_group_id   = peha.business_group_id
   AND pps.position_structure_id = peha.position_structure_id
   AND fndu2.EMPLOYEE_ID         = papf2.PERSON_ID
   AND papf2.PERSON_ID           = peha.SUPERIOR_ID
   AND papf2.EFFECTIVE_END_DATE  > sysdate
   AND papf.PERSON_ID            = peha.employee_id
   AND papf.EFFECTIVE_END_DATE   > sysdate
   AND ppos2.POSITION_ID         = peha.SUPERIOR_POSITION_ID
   AND ppos.position_id          = peha.EMPLOYEE_POSITION_ID
   AND peha.superior_level       > 0
   AND peha.employee_id          = fndu.EMPLOYEE_ID
  --and fndu.USER_NAME = upper('&StartingUsername')
ORDER BY peha.position_structure_id,
  peha.superior_level,
  papf2.full_name
Best Blogger TemplatesBest Blogger Tips
Posted by Raju Ch at 5:58 PM 0 comments
Email This
BlogThis!
Share to Twitter
Share to Facebook
Share to Pinterest

Labels: HRMS, SCRIPTS


MONDAY, FEBRUARY 25, 2013
Employee Superviser Query


SELECT asg.organization_id,
       emp.employee_number,
       emp.full_name emp_name,
       pos.NAME POSITION,
       sup.employee_number sup_emp_no,
       sup.full_name sup_name,
       sup_pos.NAME sup_pos,
       sppt.user_person_type,
       emp.effective_start_date emp_esd,
       emp.effective_end_date emp_eed,
       asg.effective_start_date asg_esd,
       asg.effective_end_date asg_eed,
       sup.effective_start_date sup_esd,
       sup.effective_end_date sup_eed,
       sup_asg.effective_start_date sup_asg_esd,
       sup_asg.effective_end_date sup_asg_eed,
       sptu.effective_start_date sptu_esd,
       sptu.effective_end_date sptu_eed
  FROM per_all_people_f emp,
       per_all_assignments_f asg,
       per_all_positions pos,
       per_all_people_f sup,
       per_all_assignments_f sup_asg,
       per_all_positions sup_pos,
       per_person_type_usages_f sptu,
       per_person_types sppt
 WHERE asg.person_id = emp.person_id
   AND pos.position_id(+) = asg.position_id
   AND sup.person_id(+) = asg.supervisor_id
   AND sup_asg.person_id(+) = sup.person_id
   AND sup_pos.position_id(+) = sup_asg.position_id
   AND sptu.person_id = emp.person_id
   AND sppt.person_type_id = sptu.person_type_id
   AND emp.person_type_id = 1120