Organization Authority in Oracle Projects and the SQL you need

Within Oracle Projects exists a functionality called Organization Authority. With Organization Authority you have the ability to add an additional type of security to your Projects implementation and to your resources. You specify types of authorities to specific users. Please note that Organization Autority will not use the organization hierarchy as defined in your instance. You need to specify each organization individually.

To quickly assign organizations for which you want to assign authorities to a user you can use the Add Organizations button and select your organization hierarchy, version and the start organization. Select the authorities you want to assign to the resource. When you click OK all the organizations within the selected organization hierarchy and from the start organization all the way down to your lowest defined organization will be assigned to the resource with the authorities specified.

So what are these authorities you can assign to a resource? Let's sum the up:

• Project AuthorityIn principal when you assign this authority to a resource this resource will gain access to all the functions a project manager can normally do on his project. The only difference between the authority and the project manager role is that the project manager role can do it only on his own projects and a resource with the project authority can do the same but on ALL projects assigned to the specific project authority organization you select.
• Resource AuthorityThis authority gives in fact access to the same functions as a normal resource manager can perform within the projects. But again in this case a resource who is having this authority can do these function for all resources throughout the entire resource authority organization you select. So a resource with this authority can for example approve candidates and nominate candidates on project requirements and is able to view resource utilization figures for all the resources.
• Utilization Authority
  A resource with this authority is able to calculate and view utilization for ALL the resources throughout the entire utilization authority organization you select.

Beside the authorities there is an option to specify that the resource is the primary contact for the organization. If a resource is the primary contact for an organization things like workflow approvals for assignments will be routed also to this primary contact!

Oracle E-Business Suite maintains organization authority through predefined seeded menus and grants to this menu. Basically organization authority is a type of role-based security but than on organization level. So - let's go a step deeper.

As said authorities provides access to menus with functions and actually grants resources access to use the defined functions within the menus. See below list for the menus belonging to the authorities:

• Project Authority = menu PA_PRM_PROJ_AUTH
• Resource Authority = menu PA_PRM_RES_AUTH
• Utilization Authority = menu PA_PRM_UTL_AUTH

So, this means that if you need any additional functionality that a resource is able to do on ALL projects, or ALL resources belonging to the selected organizations within organization authority you can add these functions to the menus described above.

Let's show some SQL which is related to this organization authority functionality of Oracle Projects. As said, when you assign an authority to a resource you are in fact creating a grant to a menu (one of the menus described above) for a specific organization. Oracle E-Business Suite maintains these grants in table FND_GRANTS of the APPLSYS schema. The GRANTEE_TYPE and INSTANCE_TYPE of these kind of organization authority grant record are respectively USER and INSTANCE. Another thing you need to know is that grants are maintained by parties. Every resource is a party also. The id of the party, or party_id is recorded within column GRANTEE_ORIG_SYSTEM_ID. Of course this relates to table HZ_PARTIES in the AR schema. To find the employee you need column PERSON_IDENTIFIER of the HZ_PARTIES table. The PERSON_IDENTIFIER is the EMPLOYEE_ID/PERSON_ID of the employee.

So all of the above can result in a nice lookup query which tells you for example for which organizations an user (assigned to an HR employee) in Oracle E-Business Suite has Project Authority:

SELECT
    GRT.INSTANCE_PK1_VALUE ORG_ID
    , HAOU.NAME ORG_NAME
FROM
    APPLSYS.FND_GRANTS GRT
    , AR.HZ_PARTIES PRT
    , HR.HR_ALL_ORGANIZATION_UNITS HAOU
WHERE
    GRT.GRANTEE_TYPE = 'USER'
    AND GRT.INSTANCE_TYPE = 'INSTANCE'
    AND GRT.MENU_ID = (SELECT MENU_ID FROM APPLSYS.FND_MENUS WHERE MENU_NAME = 'PA_PRM_PROJ_AUTH')
    AND GRT.GRANTEE_ORIG_SYSTEM_ID = PRT.PARTY_ID
    AND HAOU.ORGANIZATION_ID = GRT.INSTANCE_PK1_VALUE
    AND PRT.PERSON_IDENTIFIER = (SELECT EMPLOYEE_ID FROM APPLSYS.FND_USER WHERE USER_NAME = 'SCHAIKC')

Of course, as always, adapt the SQL to your own needs. Feel free to comment, also as always! :-)