Query to get Set of Books, Operating Unit, Organization data

/* Formatted on 10/3/2016 8:38:19 PM (QP5 v5.114.809.3010) */
  SELECT   gl.set_of_books_id,
           gl.name,
           gl.short_name,
           hou.name operatin_unit,
           hou.organization_id operating_unit_id,
           org.organization_name warehouse_name,
           org.organization_id warehouse_id
    FROM   org_organization_definitions org,
           hr_operating_units hou,
           gl_sets_of_books gl
   WHERE   org.operating_unit = hou.organization_id
           AND hou.set_of_books_id = gl.set_of_books_id
ORDER BY   1, 3, 6

MOAC Tutorial Step 2: Define Security Profile in HRMS in Oracle Apps R12

Defining a Security Profile in HRMS Responsibility is an important part of configuring MOAC in Oracle Apps R12. The Security Profile is associated with a list of Organizations (which includes Operating Units). On assigning a Security Profile to a given Responsibility (e.g. Payables Vision Operations USA) using the Profile Option “MO: Security Profile”, the user who is assigned to that Responsibility Payables Vision Operations USA is allowed access only those Organizations associated with the Security Profile.
To define a Security Profile in Oracle Apps R12, navigate to the following location:
Responsibility: Human Resources, Vision Enterprises
Navigate to Menu: Security -> Profile (to associate Organizations within one Business Group)
Or
Security -> Global Profile (to associate Organizations across more than one Business Group)
In this example, we will only cover ‘Profile’ for the Business Group ‘Vision Corporation’.
In the form shown below, enter the following values for the parameters.

Define Security Profileal

1. Name: Give a name for the new Security Profile
2. Business Group: Select the business group within which we need to assign the Operating Units
3. Under ‘Organization Security’, for Security Type: Select ‘Secure organizations by organization hierarcy and/or organization list’
4. Classification: Select ‘Operating Unit’
5. Organization Name : Select ‘Vision Communications (USA)’
6. Add one more Operating Unit called ‘Vision Services’

After filling out the details, the form would like below:

Security Profile Details Entered

Save the form. A new security profile called ‘Testing new Security Profile’ has been created and saved.

The following are the steps to implement MOAC.

Introduction to MOAC: For a brief introduction of what MOAC is and stands for refer to MOAC article.
MOAC Tutorial Step 1: Create a Responsibility and assign to an existing user
MOAC Tutorial Step 2. Define Security Profile in HRMS Responsibility
MOAC Tutorial Step 3.  Run the concurrent request ‘Security List Maintenance’
MOAC Tutorial Step 4. Assign the Security Profile created above to the profile option ‘MO: Security Profile’
MOAC Tutorial Step 5. Test the MOAC

How to Define Operating Unit in R12

 Define Operating Unit.

An organization that uses Oracle subledgers, such as Oracle Cash Management, Order Management and Shipping Execution, Oracle Payables, Oracle Purchasing, Oracle Receivables, and related products. It may be a sales office, a division, or a department. Operating units are not associated with legal entities. Operating units are assigned to ledgers and a default legal context. Information is secured by operating unit for these applications using responsibilities. Each user can access, process, and report on data only for the operating units assigned to the MO: Operating Unit or MO: Security Profile profile option. The MO: Operating Unit profile option only provides access to one operating unit. The MO: Security Profile provides access to multiple operating units from a single responsibility. You can define operating units from the Define Organization window in Oracle HRMS or from Accounting Setup Manager in General Ledger. For further reference: Visit Google.com and search for "Oracle Multiple Organization Manual". download the pdf file for detailed information on multiple ORG.
  



Prerequisites for defining operating unit:

 1. Define Location.
 2. Define Business Group.
 3. Define Ledger.


 Navigation: HRMS --> Work Structures --> Organization --> Description.

Click on New.

 


Type your operating unit name, type ,location and organization classification.

   
Click on Others.

Select operating unit information.

   

Enter the Primary Ledger name, Legal entity name and Operating unit short code
 

click on ok and then save.

Query :-

  SELECT   gls.short_name,
           gls.NAME newname,
           hou1.SHORT_CODE,
           hou.NAME,
           hou.INTERNAL_EXTERNAL_FLAG,
           hou.TYPE,
           hou.DATE_FROM,
           hou.DATE_TO,
           hoi.ORG_INFORMATION1_MEANING,
           hoi.ORG_INFORMATION2,
           hou.organization_id
    FROM   HR_ORGANIZATION_INFORMATION_V HOI,
           HR_ORGANIZATION_UNITS_V HOU,
           hr_all_organization_units hao,
           hr_operating_units hou1,
           gl_sets_of_books gls
   WHERE       HOU.ORGANIZATION_ID = HOI.organization_id
           AND HOU.ORGANIZATION_ID = hao.ORGANIZATION_ID
           AND hao.ORGANIZATION_ID = hou1.ORGANIZATION_ID
           AND hou1.set_of_books_id = gls.set_of_books_id
           AND hao.NAME = 'New Org V23'
           -- and hou.ORGANIZATION_ID =8924
           AND ('' IS NULL
                OR EXISTS
                     (SELECT   1
                        FROM   pay_restriction_values prv
                       WHERE       prv.customized_restriction_id = ''
                               AND prv.restriction_code = 'ORG_CLASS'
                               AND (HOI.org_information_context = 'CLASS'/*AND prv.VALUE = HOI.org_information1*/
                                                                         )))
           AND (HOI.ORG_INFORMATION_CONTEXT = 'CLASS')
/* AND (HOI.ORGANIZATION_ID = :DISPOSITION_ID2)*/
ORDER BY   HOI.org_information1_meaning

Setting Organization context in R12

For those of you who are trying to set the organization context within your SQL sessions or custom programs, there’s a new API you must call in R12.

In 11i the way of setting organization context in 11i was to execute the following statement:

dbms_application_info.set_client_info(ORG_ID);

However, in R12, the new way of setting the organization context is as follows:

mo_global.init(’AR’);

mo_global.set_policy_context(’S',ORG_ID);

The 'S' parameter indicates a single organization context. Replace ORG_ID with the ID of the operating unit you wish to use. You can also set a context for multiple operating units by setting the security profile. This is done by executing the following statement:

mo_global.set_org_access(NULL,SECURITY_PROFILE_ID,’ASO’);

Replace SECURITY_PROFILE_ID with the ID of the appropriate security profile. ‘ASO’ represents the application short name associated with the responsibility you’ll be using.

Setting Organization context in 11i:

begin

fnd_client_info.set_org_context(‘1’);

end;