Shulou(Shulou.com)06/01 Report--

Background:

Recently, when a company was doing inventory collection in ERP, it found that it reported an error after selecting the material: user exit unexpected error (1), from the QT_EU.Create structure tree.

This form interface is customized, and the error comes from querying existing information.

Analyze the problem:

Then inquire about the existing quantity of the material, and after inputting the material, it is found that the existing quantity is available, but the quantity of the availability interface is empty.

It's a strange question to trace the form quantity assignment logic and find that the standard API of the existing quantity query is called. The API is executed through plql after the copy parameter, and an error is reported.

Then to check the sub-inventory settings, the key points come, in the sub-inventory settings interface, you can query the BATB sub-library, but can not query the GCH sub-library.

Plsql query sub-inventory information:

SELECT * FROM inv.mtl_secondary_inventories msi WHERE 1mm 1 AND msi.organization_id = 101AND msi.secondary_inventory_name = 'GCH'

There is data, but why can't you find it in the front desk interface?

The query condition of the copy foreground interface, system.last_query, is found to query the MTL_SECONDARY_INVENTORIES_FK_V view.

Plsql initializes the environment variable to query the view:

BEGIN Fnd_Global.Apps_Initialize (43943, 53249, 401); Mo_Global.Init ('INV');-- MOAC Mo_Global.Set_Policy_Context (' INV', 101); END;SELECT * FROM MTL_SECONDARY_INVENTORIES_FK_V msi WHERE 1 AND msi.organization_id = 101 AND msi.secondary_inventory_name = 'GCH'

There is still data. Why?

It suddenly occurred to me that the inventory organization information was not initialized, so try:

BEGIN Fnd_Global.Apps_Initialize (43943, 53249); Mo_Global.Init ('INV');-- MOAC Mo_Global.Set_Policy_Context (' MFG_ORGANIZATION_ID',101); fnd_profile.put ('MFG_ORGANIZATION_ID',101); END

There is no data! GCH sublibrary can not be found in MTL_SECONDARY_INVENTORIES_FK_V and GCH can not be found in mtl_secondary_inventories.

Assign the inventory organization information to empty:

Fnd_profile.put ('MFG_ORGANIZATION_ID',null)

We can find GCH again.

TRACE Analysis:

In the sub-inventory settings interface, do a query trace tracking.

After the query, finish the trace tracking, and then go to the application server to get the trace file.

You can see two pieces of custom code in trace:

Begin: con: = "CUX_EAM_UTIL_PKG". "CHECK_SUBINV_SEC" (: sn,: on); end;begin: con: = "CUXPOLICY_MTL_SECONDARY_GCH" (: sn,: on); end

When I opened it, I found that it was function, and the two parameters of function, named named function Schema, made me realize that this is an applicable function for VPD.

SELECT * FROM Dba_Policies t WHERE 1 = 1 AND t.Object_Name = 'MTL_SECONDARY_INVENTORIES'

Sure enough, two policy were found, and the CUXPOLICY_MTL_SECONDARY_GCH was the culprit that led to the failure to find the GCH subdatabase after initializing the inventory organization information.

However, why there is no result in the query inv.mtl_secondary_inventories? normally, it is understandable that the synonym cannot find the data, but it does not make sense if the base table is not found.

Is this strategy added to the base table?

Sure enough, after looking up the Dba_Policies table, we found that object_owner is INV rather than APPS.

So far, the reason has been identified.

VPD&POLICY&DBMS_RLS:

VPD is mentioned above, and here is an explanation.

VPD (virtual Private database): virtual private database, through technical means to achieve data isolation of different objects in the same database.

ORACLE implements VPD through Oracle Policy.

When Oracle Policy is applied to database row access control, its function is to automatically add a where clause to the query result when querying the data table. If the query already has a where clause, add and... after the where clause.

A where clause that is automatically added by Oracle Policy, usually implemented by a function. The result of data row-level access control is also returned through this function.

The control core of this technology, which queries the same data source to get different data in different users and different business scenarios, is row-level security control RLS (row level security).

Oracle gives the DBMS_RLS package to implement this function.

1. Create a policy

BEGIN Dbms_Rls.Add_Policy (Object_Schema = > 'APPS',-- the Schema name of the datasheet (or view) Object_Name = >' MTL_SECONDARY_INVENTORIES',-- the name of the datasheet (or view) Policy_Name = > 'MTL_SECONDARY_INVENTORIES_GCH',-- the name of the POLICY Mainly used for future management of Policy Function_Schema = > 'APPS',-- Schema name of the function that returns the Where clause Policy_Function = >' CUXPOLICY_MTL_SECONDARY_GCH',-- the function name that returns the Where clause Statement_Types = > 'Select,Insert,Update,Delete' -- the DML type to use the Policy For example, 'Select,Insert,Update,Delete' Update_Check = > FALSE,-- only applicable if Statement_Type is' Insert,Update', value is' True' or 'False' ENABLE = > TRUE,-- whether to enable, the value is' True' or 'False' Static_Policy = > FALSE,-- the default value is FALSE. If it is set to TRUE, the policy is enabled for all users, except sys or privileged users. Policy_Type = > NULL,-- the default value is null, which means that the value of static_policy determines, and any policy specified here will override the value of static_policy. Long_Predicate = > NULL,-- default value is null,max predicate length 4000 bytes (default) or 32K Sec_Relevant_Cols = > NULL,-- default value is null, sensitive field name, Sec_Relevant_Cols_Opt = > NULL-default value is null, set to dbms_rls.ALL_ROWS to display all rows The value of the sensitive column is null) END

two。 Delete Policy

BEGIN Dbms_Rls.Drop_Policy (Object_Schema = > 'APPS',-- the Schema name of the datasheet (or view) Object_Name = >' MTL_SECONDARY_INVENTORIES',-- the name of the datasheet (or view) Policy_Name = > 'MTL_SECONDARY_INVENTORIES_GCH'-- the name of the POLICY); END

3. Enable / deactivate policy

BEGIN Dbms_Rls.Enable_Policy (Object_Schema = > 'APPS',-- the Schema name of the datasheet (or view) Object_Name = >' MTL_SECONDARY_INVENTORIES',-- the name of the datasheet (or view) Policy_Name = > 'MTL_SECONDARY_INVENTORIES_GCH' -- name of POLICY ENABLE = > TRUE-- 'default True, whether to enable it True is enabled policy, False is disabled policy') END

The above lists the common functions of the DBMS_RLS package, as well as some less commonly used functions such as refresh, policy group, etc., please study it yourself.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.