How to get an overview of your organization hierarchy

Within Oracle E-Business Suite hierarchies are used a lot. Some examples are the position hierarchy, supervisor hierarchy, expenditure organization hierarchy and the organization hierarchy.

Common functionality which is using hierarchies are the approval workflows, to generate the approval list in the Approval Management Engine (AME) for example. Also data security can be handled by incorporating an organization hierarchy within normal or global security profiles in HR. User John may only see data from organization A while user Doe 🙂 may see everything from org A but also the lower AA organization data.

To get an overview of your organization hierarchy you may use the (global) diagrammer options in Oracle HR however a representation of your hierarchy can also be achieved by firing a small sql statement. Adapt below statement to your needs by giving the correct top organiation id from which you want to generate the organization tree, optionally (I commented this part) provide a version id for the structure.

SELECT
    LPAD(' ',10 * (LEVEL-1)) || ORG.NAME HIERARCHY,
    ORG.ORGANIZATION_ID ORGANIZATION_ID,
    ORG.BUSINESS_GROUP_ID BUSINESS_GROUP_ID
FROM
    HR_ALL_ORGANIZATION_UNITS ORG,
    PER_ORG_STRUCTURE_ELEMENTS OSE
WHERE 
    1=1
    AND ORG.ORGANIZATION_ID = OSE.ORGANIZATION_ID_CHILD
    --AND OSE.ORG_STRUCTURE_VERSION_ID = 61 -- STRUCTURE VERSION
START WITH
    OSE.ORGANIZATION_ID_PARENT = 81 -- PARENT ID OF TOP LEVEL ORGANIZATION
CONNECT BY PRIOR
    OSE.ORGANIZATION_ID_CHILD = OSE.ORGANIZATION_ID_PARENT
ORDER SIBLINGS BY
    ORG.LOCATION_ID,
    OSE.ORGANIZATION_ID_CHILD