aa r12 custom development neoaug fall09 2

Steps And Methodology For Custom Development In R12 E-

Business Suite

October 26, 2009

New England Oracle Applications User Group

Srinivas Pothireddy

Steps And Methodology For Custom Development In R12 2

Offices in USA, Germany and India MA (HQ); NY/NJ & Atlanta in 2009

Founded in 2002 with >100 % growth every year for last 5 years:

Inc. 500 list of fastest growing U.S. companies

Over 250 employees (end of Q3 2009) with strong local presence

Management & Sr. Consultants: 75% former Oracle employees

Making Global Delivery Feel Closer To Home

Oracle’s Pillar Partner for OBI and BI Apps

Top 20 in North America

#1 in North East

About Apps Associates


• Introduction

• Guidelines for Forms Based Applications

• Guidelines for (OA) Framework Pages Based Applications

• Virtual Private Database

• Guidelines for Reference Views

• Guidelines for Workflows

• Guidelines for Packages

• Public API’s

• Other Changes

• Q&A

Agenda


• Multiple Organizations architecture (Multi-Org) includes a new feature Multiple Organizations Access Control (MOAC) in Release 12

• The Access Control feature is backward compatible, which means that there are no code or procedural changes if MOAC is not implemented (i.e. The user is assigned one operating unit for a responsibility)

• The Access Control feature in Release 12 allows the user to enter or query records in one or more operating units without changing application responsibility

• Pre-R12: The CLIENT_INFO application context space stores the multiple organizations context value.

Introduction


• Operating Unit Field in Forms

• Display the Operating Unit field on the top left corner of the form as the first navigable field.

• If the user can access one operating unit only, then the operating unit field displays the default value and its dependent attributes

• User can enter non-multiple organizations stripped data before specifying the operating unit for a record.

• The operating unit specific data can be entered only after user sets the operating unit context.

Guidelines for Forms Based Applications


• Multiple Organizations InitializationBEGIN

APP_STANDARD.EVENT(„PRE-FORM? );

MO_GLOBAL.init ('SQLAP'); can be 'S' for single, 'M' for multiple mode or

'<custom application short code you registered in fnd_mo_product_init table>'

END

• The app_standard.event() call in the pre-form trigger executes the AOL initialization (fnd_global.apps_initialize()). You must execute the multiple organizations initialization after this call.

• If you do not follow this order, then the MO: Operating Unit and MO: Security Profile options are not cached for the right context, which results in incorrect initialization for the session.



• List Of Values for Operating Unit Fieldselect hr.organization_id,

org_id,

hr.name operating_unit

FROM hr_operating_units hr

WHERE mo_global.check_access(hr.organization_id) = 'Y'

• Create a query based record group to display the operating units that are included in the security profile for a responsibility

• The multiple organizations global temporary table is populated with the operating unit information depending on the MO: Security Profile option

• Use the APIs to obtain the operating unit name from the temporary table instead of accessing the temporary table directly

• You must call the API MO_UTILS.get_default_ou to copy the global variables value to the form parameters



• Multiple Organizations Initialization• Every form updated for multiple organizations access control must

include a call to the multiple organizations initialization API (MO_GLOBAL.init) in the pre-form trigger.

• Pass S or M as the input parameter for non-multiple organizations access control enabled and multiple organizations access control enabled applications respectively.

• Do not set the "current organization" in different triggers for the new forms. Use the _ALL tables and include the form block ORG_ID to restrict data for the operating unit that the user selects and avoid Virtual Private Database context switching.

• To improve performance in high volume transactional forms, you must avoid policy context.



• The Operating unit field is visible on OA Framework or JTT based user interfaces, similar to the Oracle Forms user interface

• Fields that depend on the operating unit, are visible after selecting the operating unit or if the profile option defaults the operating unit.

• Similar to forms, the OA Framework pages allows users to default an operating unit using the profile option: "MO: Default Operating Unit”

• BC4J components are available for initializing multiple organizations access control in OA Framework pages

Guidelines for (OA) Framework Pages Based Applications


• The Virtual Private Database (VPD) feature allows developers to enforce security by attaching a security policy to database objects such as tables, views and synonyms

• It attaches a predicate function to every SQL statement to the objects by applying security policies. When a user directly or indirectly accesses the secure objects

• This indicates that the security is in place irrespective of the tools used to access the data

• Do not attach the security policy to base tables directly because there is code around the base tables (_ALL, ALL_B, _ALL_TL) that must access the operating Units.

• Package MO_UTILS contains the utilities for administering policies (add policy, drop policy or check if a policy exists on an object).

Virtual Private Database (VPD)


• Rewrite reference views that join data from multiple single organization views

• Replace it with one secured synonym that has the security policy attached.

• The remaining reference to _ALL tables instead of single organization views

• This improves performance because the policy is used once for the reference views that join data from multiple single organization views

• Synonyms replace single organization views that contain the CLIENT_INFO predicate attached to them

• The secured synonym is a driving table

• The secured synonym includes small volume of data (typically a setup table and not a transaction table)

Guidelines for Reference Views


• Access must be validated at the beginning when processing the workflow using the multiple organizations profile options: MO: Security Profile, MO: Default Operating Unit

• Pre-R12

• Set context manually

• Set the context in every activity

• R12

• Set context using callback functions

• Callback functions are executed once per session per item key and are more efficient

• SET_CTX – Establish context information for an item type and item key combination

Guidelines for Workflow


• Must consider operating unit as an input parameter to the Program

• Multiple Organizations API and MO_GLOBAL.init('<ACCESS_MODE') –ACCESS_MODE is S or M – must be executed before executing the program when executing from SQL*Plus or any other tools and when multiple organizations is not initialized.

• Multiple organizations is initialized when the programs are invoked through the user interface

• It is better to sort the data by operating unit and then process the data belonging to the same organization and reset the context

Guidelines for Packages


• In pre Release 12,

• you would have had following methodology for PO_HEADERS_ALL

• A table is created in PO Schema, named PO_HEADERS_ALL

• A synonym named PO_HEADERS_ALL is created in APPS schema, referring to PO.PO_HEADERS_ALL

• Create a view PO_HEADERS in APPS schema, as "select * from po_headers_all where org_id=client_info“

How is CLIENT_INFO being replaced in R12


• Now In R12,

• A table is created in PO Schema, named PO_HEADERS_ALL

• A synonym named PO_HEADERS_ALL is created in APPS schema, referring to PO.PO_HEADERS_ALL

• Another synonym named PO_HEADERS is created in APPS, referring to PO_HEADERS_ALL

• A Row Level security is applied to PO_HEADERS, using package function MO_GLOBAL.ORG_SECURITY.



• Now In R12,

• The effect of this policy is that,whenever you access PO_HEADERS, Oracle will dynamically append WHERE CLAUSE similar to below

• SELECT * FROM PO_HEADERS WHERE EXISTS (SELECT 1 FROM mo_glob_org_access_tmp oaWHERE oa.organization_id = org_id)

• Multi Org Row Level security can be applied against the table, synonym or the view.

• In practice, you will apply VPD against Objects(Synonyms) in APPS Schema

• No code change is required where the pre-R12 Multi-Org secured view was being accessed



• If the new MO security profile is set, then mo_global.initinserts one record, for each Organization in Org Hierarchy, in table mo_glob_org_access_tmp

• This package procedure will be called as soon as you login or as soon as you switch responsibility. Just like FND_GLOBAL.INITIALIZE is called.

• Call MO_GLOBAL.INIT after FND_GLOBAL.INITIALIZE

• mo_glob_org_access_tmp table is a global temporary table

• Hence after Multi Org is initialised for your session, your session will have X number of records in table mo_glob_org_access_tmp. X is the number of organizations assigned to MO Security profile

What is MO_GLOBAL.INIT


• Single Operating Unit

• Access mode is ‘S’

• One Operating Unit assigned to the “MO: Security Profile”

• MO: Security Profile is not set and the user access depends on MO: Operating Unit

• Example: SELECT trx_number from ra_customer_trx

• ORG_ID = sys_context('multi_org2','current_org_id')

Access Control


• Multiple Operating Units

• The security profile provides access to multiple operating units

• The access mode is set to 'M' for this case

• The Statement is dynamically modified to use the policy predicate

• The profile option MO: Security Profile takes precedence over MO: Operating Unit

• Example: SELECT trx_number from ra_customer_trx

• WHERE (EXISTS (SELECT 1

• FROM mo_glob_org_access_tmp oa

• WHERE oa.organization_id = org_id))

Access Control


• This procedure has two parameters

• p_access_mode

• Pass a value "S" in case you want your current session to work against Single ORG_ID

• Pass a value of "M" in case you want your current session to work against multiple ORG_ID's

• p_org_id (Only applicable if p_access_mode is passed value of "S“)

What is MO_GLOBAL.SET_POLICY_CONTEXT


• Call FND_GLOBAL.INITIALIZE

• This will set your responsibility id, user_id etc

• Call MO_GLOBAL.INIT

• This will read the MO profile option values for your responsibility/user, and will initialize the Multi Org Access.

Simulate login to a specific responsibility from SQL Plus


• A new profile option, MO: Default Operating Unit, is available to define the defaulting operating unit

• The default operating unit is visible in the Operating Unit field when the form is opened

• The user can overwrite the default value with another operating unit which the user can access

Default Operating Unit


• A new field “Operating Unit Mode” is added in the Define Concurrent Programs in the OA Framework pages.

• The user can query the program or report based on an

operating unit by updating the “Operating Unit Mode” field with one of the following values:

• Single

• Multiple

• Empty

• The default value is Empty.

• The multiple organizations context is automatically initialized by the concurrent program if the “Operating Unit

Mode” is set to either single or multiple• There is no need to change the code for single org reports.

• Pass the current organizations to populate fnd_requests.submit_request() API for single organization concurrent programs

Concurrent Programs & Reports


• MO_GLOBAL• JTT_INIT Active Initialize multiple organizations for JTT based

application

• is_multi_org_enabled

• check_access

• get_ou_name

• check_valid_org

• set_policy_context

• get_current_org_id

• get_access_mode

• get_ou_count

• MO_UTILS• Get_Ledger_Name

• get_orgid_fr_ledger

Public API’s


• MO_UTILS• Get_Ledger_Name

• get_orgid_fr_ledger

• check_org_in_sp

• get_default_org_id

• Multi-Org Access Control Java Wrappers• Init

• setPolicyContext

• Equivalent Java API? s for every PL/SQL API

Public API’s


• Existed in 11.5.10

• What? s new in R12

• Support for the creation of record group from query .• Done to change the underlying WHERE clause for the query

for an LOV

Forms Personalization


• Sub-Ledger to General Ledger Reconciliation has been made easy with the SLA data structure and would help with the reporting requirements and the link between the Sub-ledger and General Ledger

• The backward compatible views that existed in 11i after TCA was introduced have been removed for Receivables

• More reports are XML based in R12

Other Changes

aa r12 custom development neoaug fall09 2

Documents