Edi Yanto (何 萬 新)

How to Achieve R12 Multi Org for Custom Table

Posted on: September 17, 2014


We can achieve the Multi Org functionality for custom table with follow the below steps:
1. Create a custom table in custom schema, grant to user APPS, create synonyms for this table and insert some data.

CREATE TABLE xxey.ey_inv_all (
    inv_num     VARCHAR2(30),
    inv_date    DATE,
    org_id      NUMBER);

GRANT ALL ON xxey.ey_inv_all TO apps;
CREATE OR REPLACE SYNONYM apps.ey_inv_all FOR xxey.ey_inv_all;
CREATE OR REPLACE SYNONYM apps.ey_inv FOR xxey.ey_inv_all;

2. Apply RLS (Row Level Security) to EY_INV synonym using standard Virtual Private Database (VPD) policy function MO_GLOBAL.ORG_SECURITY and DBMS_RLS.ADD_POLICY API

BEGIN
dbms_rls.add_policy (
    ‘apps’, — Schema Name
    ‘EY_INV’, — synonym name
    ‘ORG_SEC’,– use policy_name ‘ORG_SEC’ standard policy
    ‘apps’, –function_schema
    ‘MO_GLOBAL.ORG_SECURITY’,–Standard MO VPD policy function
    ‘SELECT, INSERT, UPDATE, DELETE’, — statement_type
    TRUE, — update_check
    TRUE — Policy enable
);
END;

3. Check the policy already added or not in ALL_POLICIES view. If added, it will return the data.

SELECT *
FROM   all_policies
WHERE  object_name=’EY_INV’

4. Get the data for which org_id initialized by using MO_GLOBAL.SET_POLICY_CONTEXT procedure.

  • Without initialize the org_id
    wo_org_id
  • Initialize org_id 204
    org_id

4. Revoke the policy using DBMS_RLS.DROP_POLICY API. When you query to ALL_POLICIES view will get the result no row returned.

EXEC DBMS_RLS.DROP_POLICY(‘APPS’,’EY_INV’,’ORG_SEC’);

1 Response to "How to Achieve R12 Multi Org for Custom Table"

Very simple, very clean, Thanks a lot

Like

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Enter your email address to follow this blog and receive notifications of new posts by email.

Join 1,368 other followers


Edi Yanto
Oracle Applications Technical Consultant
View Edi Yanto's profile on LinkedIn

My Certifications






Follow me on Twitter

Archives

September 2014
M T W T F S S
« May   Dec »
1234567
891011121314
15161718192021
22232425262728
2930  

Visitors

Categories

Blog Stats

  • 140,585 hits

Users Online

web counter

Pages

%d bloggers like this: