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

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...

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 , ...

Oracle Purchasing – Receipt Accounting

Oracle Purchasing – Receipt Accounting (Accrue On Receipt and Accrue at Period End) Inventory Accruals: Inventory and Purchasing provides visibility and control of accrued liabilities for inventory items. Purchasing automatically records the accrued liability for the inventory items at the time of receipt. This transaction is automatically recorded in the general ledger at the time of receipt. The inventory expense is recorded at delivery if Standard Delivery is used and at receipt if Direct Delivery used. Expense Accruals: Purchasing optionally accrues un-invoiced receipts of non-inventory items when a period is closed. Purchasing automatically creates a balanced journal entry for each receipt un-invoiced at period end which can be reversed at the beginning of the next period. Navigation: India Local Purchasing > Oracle Purchasing > Setup > Organizations > Purchasing Options Difference between 'Accrue On Receipt' and 'Accrue at Per...

Sreekanth Reddy Pothula's Oracle ERP Blog

Search This Blog