HR Orgs - Hierarchies

Project Organization Hierarchy

-- get list of organization hierarchies

-- can get the ORGANIZATION_STRUCTURE_ID from this SQL, to use in SQLs below

SELECT * FROM apps.per_organization_structures_v;

 

Tree View

 SELECT LPAD (' ', 10 * (LEVEL - 1)) || pose.d_child_name name

         , LEVEL

         , org.TYPE

         , fu.description org_created_by

         , fu.user_name

         , org.creation_date org_create_date

      FROM apps.per_org_structure_elements_v pose

         , hr.hr_all_organization_units org

         , applsys.fnd_user fu

     WHERE pose.organization_id_child = org.organization_id

       AND org.created_by = fu.user_id

       AND pose.org_structure_version_id = :yourid

START WITH pose.organization_id_parent = 0

CONNECT BY PRIOR pose.organization_id_child = pose.organization_id_parent;

 

Flat View

SELECT org.name hr_org

     , pose.org_structure_element_id

     , pose.business_group_id

     , pose.organization_id_parent

     , pose.d_parent_name

     , pose.org_structure_version_id

     , pose.organization_id_child

     , pose.d_child_name

  FROM apps.per_org_structure_elements_v pose

     , hr.hr_all_organization_units org   

 WHERE pose.organization_id_child = org.organization_id

   AND pose.org_structure_version_id = :yourid;

 

Sample Data Messing About

WITH sample_data AS

     (SELECT 159 organization_id_parent, 'ABC CHEESE' d_parent_name, 2504 organization_id_child, 'ABC CHEESE Blue' d_child_name FROM DUAL UNION ALL

      SELECT 159, 'ABC CHEESE',  2505, 'ABC CHEESE Green' FROM DUAL UNION ALL

      SELECT 159, 'ABC CHEESE',  2506, 'ABC CHEESE Other' FROM DUAL UNION ALL

      SELECT 159, 'ABC CHEESE',  2507, 'ABC CHEESE Smelly' FROM DUAL UNION ALL

      SELECT 1944, 'ABC CHEESE', 159, 'ABC CHEESE' FROM DUAL UNION ALL

      SELECT 159, 'ABC CHEESE',  398, 'ABC CHEESE Sock Smell' FROM DUAL UNION ALL

      SELECT 159, 'ABC CHEESE',  462, 'ABC CHEESE (Fresh)' FROM DUAL)

SELECT LPAD (' ', 10 * (LEVEL - 1)) || d_child_name name

         , LEVEL FROM sample_data

         START WITH organization_id_parent = 1944

CONNECT BY PRIOR organization_id_child = organization_id_parent;

 

HR Org Classifications

SELECT haou.name

     , hoiv.org_information1_meaning classification

     , hoiv.org_information2_meaning enabled

     , hoiv.creation_date cr_date

     , fu1.description cr_by

     , hoiv.last_update_date up_date

     , fu2.description up_by

  FROM apps.hr_organization_information_v hoiv

     , hr.hr_all_organization_units haou

     , applsys.fnd_user fu1

     , applsys.fnd_user fu2

 WHERE hoiv.organization_id = haou.organization_id

   AND hoiv.created_by = fu1.user_id

   AND hoiv.last_updated_by = fu2.user_id;