Skip to main content

How to overview 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

Comments

Post a Comment

Popular posts from this blog

SQL Query to extract Oracle Purchase Order Information

SELECT   poh.po_header_id,    poh.type_lookup_code PO_TYPE,   poh.authorization_status PO_STATUS,   poh.segment1 PO_NUMBER,   pov.vendor_name SUPPLIER_NAME,   povs.vendor_site_code Location,   hrls.location_code Ship_To,   hrlb.location_code Bill_to,   pol.line_num ,   msib.segment1 Item,   pol.unit_price,   pol.quantity,   pod.amount_billed Amount,   pod.destination_subinventory,   ppf.full_name Buyer_Name,   poh.closed_Code  FROM   PO_HEADERS_ALL poh,   PO_LINES_ALL pol,   mtl_system_items_b msib,   PO_LINE_LOCATIONS_ALL poll,   PO_DISTRIBUTIONS_ALL pod,   po_vendors pov,   po_vendor_sites_All povs,   hr_locations_all hrls,   hr_locations_all hrlb,   per_all_people_f ppf,   po_line_types polt WHERE   1                         =1 AND polt.line...
Post a Comment
Read more

Query to find Operating Unit, Business Group and Legal Entity Information

SELECT   DISTINCT   hrl . country ,                  hroutl_bg . name              bg ,                  hroutl_bg . organization_id ,                  lep . legal_entity_id ,                  lep . name                    legal_entity ,                  hroutl_ou . name              ou_name ,               ...
Post a Comment
Read more

How To Enable / Disable Forms Personalization Option

Forms Personalization gives great flexibility to execute custom business logic without performing so much of technical work. To start forms personalization navigate to Help -> Diagnostics -> Custom Code -> Personalize But many time when we click on personalize it give below error  " Function is not available for this respnosibility. Change responsibilities or contact your System Administrator " To Enable access to forms personalization function we need to set below profile option.  -  Utilities:Diagnostics -> Yes / No It determines the diagnostics option is enabled for a user / responsibility or site, depending on the level profile option is set. Navigate to System Administrator -> Profile -> System Query for your user / responsibility for which you want to provide access. Set the value to 'Yes' , If you want allow access to forms personalization Since we change the profile option please change the respons...
Post a Comment
Read more