R12: Getting list of operating units user has access to in R12

With the introduction of MOAC feature in R12, now user can access multiple operating units in subledgers without leaving the current responsibility. Sometimes we need to find out what operating units are accessible from a responsibility for an application (like AP, PO etc) for investigation / debugging purpose.

I have found two ways to get those details,

1. By setting MOAC enviornment from backend in PL/SQL and get the list of operating units from the temp table MO_GLOB_ORG_ACCESS_TMP,
For e.g.- If you want to see what operating units can be accessbile from OPERATIONS user through "Payables Manager" responsibility then run the below anonymous block,

--This sets the User enviornment---

begin

FND_GLOBAL.APPS_INITIALIZE(1318 ---user 'OPERATIONS'
,20639 ---responsibility "Payables Manager"
,200 ---Application "Oracle Payable"
);

MO_GLOBAL.INIT('SQLAP'); --This will initialize MOAC for AP application

end;

Then querying below would give all the operating units user has access to,

select * from MO_GLOB_ORG_ACCESS_TMP

The downside of this script that you should have access privilege to execute FND_GLOBAL.APPS_INITIALIZE AND MO_GLOBAL.INIT

2.
If you do not then alternate method is to write a query which would read HR security profile, HR Org hierarchy, FND profile etc and get you the list of OUs. Following query can be used for that which would give the list of OUs accessible from "Payables Manager" responsibility,

SELECT distinct B.PROFILE_OPTION_VALUE,C.RESPONSIBILITY_NAME,d.SECURITY_PROFILE_NAME, d1.name ou_name
FROM FND_PROFILE_OPTIONS_VL A
, fnd_profile_option_values b
, fnd_responsibility_vl c
, per_security_profiles_v d
, per_organization_structures a1
, PER_ORG_STRUCTURE_VERSIONS b1
, per_org_structure_elements_v c1
, hr_operating_units d1
WHERE A.user_PROFILE_OPTION_NAME LIKE 'MO%Security%'
and d.ORGANIZATION_STRUCTURE_ID = a1.ORGANIZATION_STRUCTURE_ID
and a.PROFILE_OPTION_ID=b.PROFILE_OPTION_ID
and TO_NUMBER(b.LEVEL_VALUE)=c.RESPONSIBILITY_ID
and d.SECURITY_PROFILE_ID = b.PROFILE_OPTION_VALUE
AND upper(c.RESPONSIBILITY_NAME) like 'PAYABLES MANAGER'
and a1.ORGANIZATION_STRUCTURE_ID = b1.ORGANIZATION_STRUCTURE_ID
and b1.ORG_STRUCTURE_VERSION_ID= c1.ORG_STRUCTURE_VERSION_ID
and (d1.organization_id = c1.ORGANIZATION_ID_CHILD
or d1.organization_id = c1.ORGANIZATION_ID_parent)
UNION ALL
SELECT B.PROFILE_OPTION_VALUE,C.RESPONSIBILITY_NAME,d.SECURITY_PROFILE_NAME, d1.name ou_name
FROM FND_PROFILE_OPTIONS_VL A
, fnd_profile_option_values b
, fnd_responsibility_vl c
, per_security_profiles_v d
,PER_SECURITY_ORGANIZATIONS_V c1
, hr_operating_units d1
WHERE A.user_PROFILE_OPTION_NAME LIKE 'MO%Security%'
and a.PROFILE_OPTION_ID=b.PROFILE_OPTION_ID
and TO_NUMBER(b.LEVEL_VALUE)=c.RESPONSIBILITY_ID
and d.SECURITY_PROFILE_ID = b.PROFILE_OPTION_VALUE
AND upper(c.RESPONSIBILITY_NAME) like 'PAYABLES MANAGER'
AND c1.SECURITY_PROFILE_ID = d.SECURITY_PROFILE_ID
and c1.ORGANIZATION_ID = d1.ORGANIZATION_ID