Saturday, March 2, 2013

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

No comments:

Post a Comment

Best Blogger TipsGet Flower Effect