R12 Multi Org Access Control (MOAC) Reporting Notes

---------------------------------------------------------------------------------------------------------------------- 

// Context Initialization Procedure for MOAC in Database Session

----------------------------------------------------------------------------------------------------------------------

-- Author: Faraz Anwar

-- Dated : 2010/11/3

//For Multiple Org Context Initialization in R12

BEGIN

MO_GLOBAL.init('AR');

mo_global.set_policy_context('M', 115);

--username, responsibility_id, resp_application_id, security_profile_id, server_id

FND_GLOBAL.APPS_INITIALIZE(1116,50651,222,62,7118);

END;

//For Single Org Context Initialization in R12

BEGIN

MO_GLOBAL.init('AR');

mo_global.set_policy_context('S', 115);

FND_GLOBAL.APPS_INITIALIZE(1116,50651,222,62,7118);

END;

 

// Important Procedures

-- This procedure only works with Security Profiles. MO Operating Unit is not Affected with this procedure

MO_UTILS.check_org_in_sp(p_organization_id, p_class);   

e.g.,

SELECT organization_id, name, MO_UTILS.check_org_in_sp(ORGANIZATION_ID, 'OPERATING_UNIT') accessable FROM HR_OPERATING_UNITS;

-- Returns Y or N depending on whether user has access to given organization

mo_global.check_access(p_organization_id)

e.g.,

SELECT organization_id, name, mo_global.check_access(ORGANIZATION_ID) accessable FROM HR_OPERATING_UNITS;

//Related Tables

-- Lists all the Organizations as per Current Security Context either Orgs Accessable through "MO Operating Unit" or

-- MO Security Profile" profile options

mo_glob_org_access_tmp   

-- Lists products enabled for MOAC

FND_MO_PRODUCT_INIT    

-- Other Misc Tables

PER_SECURITY_PROFILES 

PER_ORGANIZATION_LIST 

// Example Standard Report

Accounts Payable Trial Balance

---------------------------------------------------------------------------------------------------------------------- 

// Multi-Org Reporting in R12 for Custom Reports

----------------------------------------------------------------------------------------------------------------------

// Modify Report Definintion to use either 'Single' or 'Multiple' Operating Unit Mode.

1. Login into application with System Administration responsibility (NOT System Administrator)

2. Navigate: Concurrent -> Programs

3. Query for short name of the concurrent program

4. Click on Update pencil icon of your program

5. Under 'Update Concurrent Program' region, select Request tab

6. Under 'Request Setting' region, select 'Single' from the drop down of 'Operating Unit

Mode' field

7. Save changes by clicking on 'Apply' button.

OR

UPDATE FND_CONCURRENT_PROGRAMS

SET MULTI_ORG_CATEGORY='S'

WHERE CONCURRENT_PROGRAM_NAME = '<your program name>'

Reference Document:

1. Metalink Note: Operating Unit Not Selectable When starting Concurrent Requests For MOAC Responsibility [ID 837615.1]

2. Forum Ref:     http://forums.oracle.com/forums/thread.jspa?threadID=1056143&tstart=60

// Value Set in Report

The Default Value Set provided by Oracle Applications for MOAC based Security is

FND_MO_OU

// Convert your reports to use synonyms created for _ALL Tables

e.g.,

RA_CUSTOMER_TRX_ALL Table has synonym RA_CUSTOMER_TRX for APPS Schema.

This RA_CUSTOMER_TRX synonym has security predicate attached to it using VPD (Virtual Private Database).

OR

// OR If you don't wish to use VPD synonyms directly

If you don't wish to use VPD synonyms directly then as a work around you can join

your _ALL tables org_id column with

   mo_glob_org_access_tmp.organization_id

// Create User Parameter

P_CONC_REQUEST_ID

Datatype: Number

Length 15

// Add following code to After Parameter Form (NOT Before Report Trigger) to Initialize Context

SRW.USER_EXIT('FND SRWINIT');

--You may also put some debug messages here for instance

SRW.MESSAGE(999,'After SRWINIT');

// Add following code in After Report Trigger

SRW.USER_EXIT('FND SRWEXIT');

// VPD References

http://www.devshed.com/c/a/Oracle/RowLevel-Security-with-Virtual-Private-Database/1/

Oracle Applications Multiple Organizations Access Control for Custom Code Doc ID: Note:420787.1

API Hook to Validate Payroll Element Entry Value

CREATE OR REPLACE PACKAGE APPS.xx_py_api_hooks

AS

   PROCEDURE restrict_element_entry (p_assignment_id NUMBER, p_element_type_id NUMBER);

   PROCEDURE check_entry_value (p_element_entry_id NUMBER);

END;

/

CREATE OR REPLACE PACKAGE BODY apps.xx_py_api_hooks

AS

   /***

    *       Author:         Faraz Anwar

    *       Desciption:     Element Entry Level Validations

    ***/

  /**

   * Modlue Type        = PAY_ELEMENT_ENTRIES_F

   * Hook Called from   = Before Insert

   * Purpose            = Restrict Element Entries for Employees on Vacation

   **/

  

   PROCEDURE restrict_element_entry (p_assignment_id NUMBER, p_element_type_id NUMBER)

   AS

      l_element_type_id     NUMBER;

      l_emp_cat             VARCHAR2 (30);

      l_classification_id   NUMBER;

      CURSOR csr_vac

      IS

         SELECT employment_category

           FROM per_assignments_f paf, fnd_sessions sesh

          WHERE assignment_id = p_assignment_id

            AND sesh.session_id = USERENV ('sessionid')

            AND sesh.effective_date BETWEEN paf.effective_start_date AND paf.effective_end_date;

      CURSOR csr_clasf

      IS

         SELECT classification_id

           FROM pay_element_types_f pet, fnd_sessions sesh

          WHERE element_type_id = p_element_type_id

            AND sesh.session_id = USERENV ('sessionid')

            AND sesh.effective_date BETWEEN pet.effective_start_date AND pet.effective_end_date

            AND classification_id IN (SELECT classification_id

                                        FROM pay_element_classifications);

   /*WHERE (classification_name) IN

            ('Voluntary Deductions', 'Involuntary Deductions', 'Employer Charges', 'Statutory Deductions',

             'Pre-Statutory Deductions')

     AND legislation_code = 'SA');*/

   BEGIN

      OPEN csr_vac;

      FETCH csr_vac

       INTO l_emp_cat;

      CLOSE csr_vac;

      OPEN csr_clasf;

      FETCH csr_clasf

       INTO l_classification_id;

      CLOSE csr_clasf;

      IF (l_classification_id IS NOT NULL) AND (l_emp_cat = 'VAC')

      THEN

         fnd_message.set_name ('FND', 'FND_GENERIC_MESSAGE');

         fnd_message.set_token ('MESSAGE', 'Employee on Vacation. Element Entries are not Allowed.');

         app_exception.raise_exception;

      END IF;

   END;

    /**

   * Modlue Type        = PAY_ELEMENT_ENTRIES_F

   * Hook Called from   = Before Insert, Before Update

   * Purpose            = Validate Entries for Recovery Elements

   **/

   PROCEDURE check_entry_value (p_element_entry_id NUMBER)

   IS

      /* Cursor to Fetch Screen Entry Value */

      CURSOR c_curr_entry_value (p_element_entry_id NUMBER, p_input_name VARCHAR2, p_element_type_id NUMBER, p_effective_date DATE)

      IS

         SELECT peev.screen_entry_value

           FROM pay_element_entry_values_f peev, pay_input_values_f piv

          WHERE peev.element_entry_id = p_element_entry_id

            AND piv.NAME = p_input_name

            AND peev.input_value_id = piv.input_value_id

            AND piv.element_type_id = p_element_type_id

            AND p_effective_date BETWEEN peev.effective_start_date AND peev.effective_end_date

            AND p_effective_date BETWEEN piv.effective_start_date AND piv.effective_end_date;

      /* Cursor to Fetch Element Type Id */

      CURSOR c_elem_type (p_element_entry_id NUMBER, p_effective_date DATE)

      IS

         SELECT pel.element_type_id

           FROM pay_element_entries_f pee, pay_element_links_f pel

          WHERE pee.element_link_id = pel.element_link_id

            AND pee.element_entry_id = p_element_entry_id

            AND p_effective_date BETWEEN pee.effective_start_date AND pee.effective_end_date

            AND p_effective_date BETWEEN pel.effective_start_date AND pel.effective_end_date;

      CURSOR c_elem_set (p_element_type_id NUMBER)

      IS

         SELECT 'X'

           FROM apps.xx_py_element_set_members

          WHERE element_set_id = 13071 AND element_type_id = p_element_type_id;

      --

      --Element Set ID 13071 equals "Recovery Elements Entry Validation Set"

      --

      l_installment_amt      NUMBER;

      l_total_recovery_amt   NUMBER;

      l_repmt_amt            NUMBER;

      l_override_amt         NUMBER;

      l_total_due            NUMBER;

      l_effective_date       DATE;

      l_element_type_id      NUMBER;

      l_recovery_type        VARCHAR2(1);

   BEGIN

     

      l_effective_date := hr_general.effective_date;

      OPEN c_elem_type (p_element_entry_id, l_effective_date);

      FETCH c_elem_type

       INTO l_element_type_id;

      CLOSE c_elem_type;

      OPEN c_elem_set (L_element_type_id);

      FETCH c_elem_set

       INTO l_recovery_type;

      

      CLOSE c_elem_set;

      /* For Recovery Elements */

      IF l_recovery_type is not null                                                                 

      THEN

         OPEN c_curr_entry_value (p_element_entry_id, 'Total Recovery Amount', l_element_type_id, l_effective_date);

         FETCH c_curr_entry_value

          INTO l_total_recovery_amt;

         CLOSE c_curr_entry_value;

         OPEN c_curr_entry_value (p_element_entry_id, 'Installment Amount', l_element_type_id, l_effective_date);

         FETCH c_curr_entry_value

          INTO l_installment_amt;

         CLOSE c_curr_entry_value;

         OPEN c_curr_entry_value (p_element_entry_id, 'Repayment Amount', l_element_type_id, l_effective_date);

         FETCH c_curr_entry_value

          INTO l_repmt_amt;

         CLOSE c_curr_entry_value;

         /*

         open c_curr_entry_value (p_element_entry_id, 'Override Installment', p_element_type_id, l_effective_date);

         fetch c_curr_entry_value into  l_override_amt;

         close c_curr_entry_value;

         */

         /* verify that amounts entered are positive */

         IF (l_total_recovery_amt < 0) OR (l_installment_amt < 0) OR (l_repmt_amt < 0)

         THEN

            fnd_message.set_name ('FND', 'FND_GENERIC_MESSAGE');

            fnd_message.set_token ('MESSAGE', 'Negative Amount not Allowed');

            app_exception.raise_exception;

         END IF;

         /* verify that amounts entered are logically correct */

         IF l_installment_amt > l_total_recovery_amt

         THEN

            fnd_message.set_name ('FND', 'FND_GENERIC_MESSAGE');

            fnd_message.set_token ('MESSAGE', 'Installment Amount cannot be greater than Total Recovery Amount');

            app_exception.raise_exception;

         ELSIF l_repmt_amt > l_total_due

         THEN

            fnd_message.set_name ('FND', 'FND_GENERIC_MESSAGE');

            fnd_message.set_token ('MESSAGE', 'Repayment Amount cannot be greater than Total Due');

            app_exception.raise_exception;

         END IF;

      END IF;

   END;

END;

/