Four applications of PLSQL_R12 MOAC Multi-Organization (case)

2014-05-31 Baoxinjian in Capgemini

I. Summary

R12 Form or two other development, in many cases will involve R12 MOAC Multi-Organization development, the following describes 4 common applications, also asked Xueyou to continue to supplement

1. Open the form when developing the automatic popup Organization selection implementation method (add choose org function)

2. Open the form in the development of the organization LOV select the implementation mode

3. R12 Multi-Organization Technology realization Mode-VPD Technology

4. VPD technical explanation, database security policy

Second, the case

Case 1, open Form Automatic popup organization selection Implementation mode (add choose Org Function) when developing

(1). Added 4 paramters:chart_of_account_id, Org_name, Org_code, org_id four parameters;

(2). Add fnd_org.choose_org to Pre-form in the form level;

(3). Added App_window.set_title (' bxj_windows ',:p arameter.org_code) in when-new-form-instance in Form level;

Case 2, open the form in the development of the organization LOV select the implementation mode

(1). Pre-form Trigger Initialize MOAC configuration environment

Mo_global.init (' ONT ');

IF l_default_org_id is not NULL then-default org ID NOT NULL
Mo_global. Set_policy_context (' S ', l_default_org_id);
ELSE
Mo_global. Set_policy_context (' M ', null);
END IF;

(2). Copy ou default values in When-create-record triggers

In this trigger, the default OU ID and OU name are copied to the corresponding automatic on the form interface, and the default OU information is taken out by default when the record is created.
Copy (name_in (' global.mo_default_org_id '), ');
Copy (name_in (' Global.mo_default_ou_name '), ');

(3). Create an OU lov

The OU Name field on the form interface creates an SQL statement that Lov,lov the corresponding record group as follows:
SELECT hr.organization_id organization_id, Hr.name organization_name
From Hr_operating_units HR
WHERE mo_global.check_access (hr.organization_id) = ' Y '
ORDER by Organization_name

Case 3,R12 Multi-Organization Technology realization Mode-VPD technology

(1). Three profile<mo:security profiles, Mo:default Operating Unit, mo:operating unit>

(2). Ap_invoices defined on ap_invoices_all basis

A. For the vpd<ap_invoices> table, simple queries are generally not returned records, if you want to find records, you need to set a context first

B. General Query VPD table

SELECT * from Ap_invoices; -->no Output

C. Single OU Mode

BEGIN Execute Mo_global.set_policy_context (' S ', 204);

--204 for org_id,s indicates that the single ORG Contextend;select * from po_headers;--will output all the PO below ou:204

--multiple OU Mode (simulate login to a specific responsibility)

Call Fnd_global.apps_initialize (userid,resp_id,resp_appl_id);

D. Call Mo_global. INIT (P_appl_short_name); This would read the MO profile option values for your responsibility/user,

And would initialize the Multi Org Access.

E.select * from Po_headers; -->output
(3). Mo_global.org_security's role is actually based on your settings for MOAC profiles, and then to the appropriate where condition (organization filter), then query

Case 4, VPD technology detailed, database security policy

(1). Create data for table Or view <bxj_invoices_policy_v>

CREATE OR REPLACE VIEW Apps.bxj_invoices_policy_v as

SELECT a.invoice_id,

B.vendor_name,

C.vendor_site_code,

A.invoice_num,

A.invoice_amount,

A.invoice_currency_code

From Ap_invoices_all A, ap_suppliers B, Ap_supplier_sites_all c

WHERE a.vendor_id = b.vendor_id and a.vendor_site_id = c.vendor_site_id

and rownum< = 10

(2). Create Policy Function

CREATE OR REPLACE FUNCTION bxj_fun_invoices_policy (S_schema in VARCHAR2,

S_object in VARCHAR2)

RETURN VARCHAR2

As

BEGIN

RETURN ' Invoice_currency_code = ' | | "EUR";

END;

(3). Register VPD Policy

BEGIN

Dbms_rls.add_policy (Object_schema = ' APPS ',

object_name = ' Bxj_invoices_policy_v ',

Policy_name = ' policy_limited_query_invoices ',

Function_schema = ' APPS ',

policy_function = ' bxj_fun_invoices_policy ');

END;

(4). Compare the registration policy before and after the view output, from 10 to 7, directly filtered the record of USD

A. Pre-registration

A. Post-registration