1. Get rid of all advertisements and get unlimited access to documents by upgrading to Premium Membership. Upgrade to Premium Now and also get a Premium Badge!

How can we achieve multi-org functionality in R12 for custom tables in 11i

Discussion in 'Oracle Apps Technical' started by sivasankar.g, Sep 7, 2012.

  1. sivasankar.g

    sivasankar.g Active Member

    Messages:
    6
    Likes Received:
    1
    Trophy Points:
    65
    Hi

    We have custom tables in 11i and we are upgrading to R12
    How can we achieve multi-org functionality for custom tables.

    Thanks,
    Siva
     
  2. Bharat

    Bharat Community Moderator Forum Guru

    Messages:
    1,747
    Likes Received:
    147
    Trophy Points:
    1,805
    Location:
    Vijayawada, India
    Hi Siva,

    Does this table attached to any form and does this table contains operating unit field? I think we can do this multi-org functionality only when we have that table under any of the form.
     
  3. sivasankar.g

    sivasankar.g Active Member

    Messages:
    6
    Likes Received:
    1
    Trophy Points:
    65
    Thanks Bharat..
    Yes table have operating unit column.
    we can get multi-org fun by using the MO_GLOBAL.ORG_SECURITY API for applying RLS.

    Regards,
    Siva.
     
  4. Bharat

    Bharat Community Moderator Forum Guru

    Messages:
    1,747
    Likes Received:
    147
    Trophy Points:
    1,805
    Location:
    Vijayawada, India
    Hi Siva,

    Did you solved that? If so can you please post the steps for that
     
    sivasankar.g likes this.
  5. sivasankar.g

    sivasankar.g Active Member

    Messages:
    6
    Likes Received:
    1
    Trophy Points:
    65
    R12 Multi Org Design For Custom Tables.
    Steps:
    1. Create a Table in Custom Schema for multi org data.
    Ex: CREATE TABLE XX_TABLE_ALL
    (
    num NUMBER,
    name VARCHAR2 (100),
    org_id NUMBER
    );
    Insert some data with different org_id’s

    2. Give the Grants to Apps Schema for above created table
    GRANT ALL ON XX.XX_TABLE_ALL TO APPS

    3. Connect to APPS Schema
    Create a synonym named XX_TABLE_ALL in APPS schema, referring to XX.XX_TABLE_ALL.
    CREATE OR REPLACE SYNONYM APPS.XX_TABLE_ALL FOR PO.XX_TABLE_ALL;

    Create another synonym named XX_TABLE in APPS, referring to XX.XX_TABLE_ALL.
    CREATE OR REPLACE SYNONYM APPS.XX_TABLE FOR PO.XX_TABLE_ALL;

    4. Now we can apply Row Level security to XX_TABLE, using function MO_GLOBAL.ORG_SECURITY.

    Oracle RLS will dynamically append WHERE CLAUSE similar to below
    SELECT * FROM XX_TABLE WHERE EXISTS (SELECT 1 FROM mo_glob_org_access_tmp oa WHERE oa.organization_id = org_id)

    For Applying RLS we can add policy for XX_TABLE synonym by using DBMS_RLS.ADD_POLICY API.
    Example for add Policy with ADD_POLICY API
    BEGIN
    dbms_rls.add_policy ('apps', -- Schema Name
    'TAB1', -- synonym name
    'ORG_SEC',-- use policy_name 'ORG_SEC' –standard policy
    'apps', --function_schem
    'MO_GLOBAL.ORG_SECURITY',--Standard MO VPD policy fun
    'SELECT, INSERT, UPDATE, DELETE', -- statement_type
    TRUE, -- update_check
    TRUE -- Policy enable
    );
    END;
    After creating policy for the synonym we can get data only for which org_id initialized in the back end by using MO_GLOBAL.SET_POLICY_CONTEXT('S',’Org_id’);
    Example for Testing:
    Run the query
    SELECT * FROM XX_TABLE
    (The query returns no data)
    Initialize the org_id
    BEGIN
    MO_GLOBAL.SET_POLICY_CONTEXT('S',12);
    END;

    Rerun the query
    You will get the data for org_id 12 only.


    Regards,
    Siva.
     
    sambuduk likes this.
  6. Bharat

    Bharat Community Moderator Forum Guru

    Messages:
    1,747
    Likes Received:
    147
    Trophy Points:
    1,805
    Location:
    Vijayawada, India
    Hi Siva,

    Thanks for posting Script.
     
  7. sambuduk

    sambuduk Forum Advisor

    Messages:
    242
    Likes Received:
    73
    Trophy Points:
    455
    Location:
    Hyderabad , Telangana
    Thank you Siva...
     
  8. vamshi3108

    vamshi3108 Guest

    Hi Siva,
    Thanks for Posting.