Wednesday, September 9, 2015

Oracle Purchase Orders Hierarchy Query

SELECT DISTINCT hpx.business_group_id,
                hpx.entry_grade_id,
                hpx.job_id,
                hpx.location_id,
                hpx.organization_id,
                hpx.fte,
                hpx.max_persons,
                hpx.NAME POSITION,
                ppx.full_name,
                hier.lev
  FROM hr_positions_x hpx,
       per_assignments_x pax,
       per_people_x ppx,
       (    SELECT pse.parent_position_id, LEVEL lev
              FROM per_pos_structure_elements pse
             WHERE     1 = 1
                   AND pse.pos_structure_version_id =
                          (SELECT ppsv.pos_structure_version_id
                             FROM per_position_structures pps,
                                  per_pos_structure_versions ppsv
                            WHERE     pps.position_structure_id = ppsv.position_structure_id
                                  AND pps.NAME = 'PO Requisition Hierarchy' -- Change the hierarchy as per your need
                                  AND TRUNC (SYSDATE) BETWEEN ppsv.date_from AND NVL ( ppsv.date_to, TO_DATE ( '31-Dec-4712','DD-MON-YYYY')))
        START WITH pse.subordinate_position_id =
                      (SELECT position_id FROM hr_positions_x WHERE NAME = '0110.215.Executive Admin') -- Change the position to the one you need
        CONNECT BY PRIOR pse.parent_position_id = pse.subordinate_position_id
          ORDER BY LEVEL ASC) hier
 WHERE     hier.parent_position_id = hpx.position_id
       AND pax.position_id = hier.parent_position_id

       AND pax.person_id = ppx.person_id

No comments:

Post a Comment

Best Blogger TipsGet Flower Effect