Remove VO personalization

Personalization page supports to extend and remove the controller (CO) but no interface to extend the VO. 

VO extension requires Jdeveloper to create substitute, which will generate .jpx and server.xml files.
Later JPXImporter will apply extended VO to system.

To backout the extended VO, we can use database scripts to delete personalizations .

refer OAF migration

Steps to back out VO extension -
1)  Get the original VO name ( with complete path). This will be document path.
      Example /oracle/apps/po/lov/server/DocumentNumbersVO 

===========================================================
Use listcustomization to find the personalization details

BEGINJDR_UTILS.LISTCUSTOMIZATIONS('/oracle/apps/po/lov/server/DocumentNumbersVO');
END;

===========================================================
Use delete script to delete the personalization

Begin

jdr_utils.deletedocument(p_document=>'/oracle/apps/po/lov/server/customizations/site/0/DocumentNumbersVO');
END;

=========================================================== 

How to restrict LOV data in EBS

Requirement is to restrict some data from LOV available on OAF pages or Core Forms.

 

If LOV is available at Core forms, it’s difficult to restrict data as core form personalization does not support this level restriction.

If we are at OAF page view extension (OAF customization) is the option to restrict LOV data. In next lines we shall discuss an easy method to restrict data in LOV for both Core forms and OAF pages.

 

How to achieve this?

It’s quite easier to restrict LOV data using Oracle provided data restriction method. Oracle recommends VPD for data restriction, which can be a better approach over OAF customization.

This can be useful for core forms as well.

 

Use below steps to implement this –

>Find out the source for LOV data –

OAF Page – Go to about this page >> Find associated VO >> Check the underlying SQL

>> Find the table/view having actual data >> Use VPD to restrict the data on source table/view

 

Core Form – Get the fmb file (from AU_TOP) and open in form builder >> Find the associated LOV >> Find Attached Data Source

>> Find the table/view having actual data >> Use VPD to restrict the data on source table/view

 

Use of VPD over OAF customization will be easy to maintain and deploy to higher instances.

 

Example:

Restrict Create Order options on order (Buyer work center) page:

Create LOV has 2 options 1) SPO and 2) CSPO >> we need to exclude SPO option

 

Find the source for LOV data and got that source table is PO_DOC_STYLE_LINES_TL

 

Created function to restrict SPO on the source table

   

                     

                      

                       

 

 

 

 

 

 

Add this function to policy.

 

 

 

 

 

 

 

 

 

 

 

Go to application and check the create option –

 
 

What is Virtual Private Database (VPD)

VPD is new Oracle Database feature to enable policies on database objects ( i.e Table, Synonyms, View etc) This is used to control data access for security reasons.

 

What actually VPD does >> this append additional where condition to objects based on defined policies.  

 

How to use VPD>> below are the steps used to implement VPD in system.

1)     Identify the object:  Lest take APPS.PO_HEADERS_ALL (Synonym) for VPD testing.  

2)     Create the function: Create a function ( or package.function) which will return the required filter condition

3)     Add to policy – Use standard oracle API to create a policy for created function ( step 2)

  

Example –

Step#1) Assume PO_HEDAERS_ALL has 100 records. Will update DFF Column (Attribute1) =’Sandeep’ for any 10 records.

Requirement is  to restrict data having DFF value as ‘Sandeep’ for schema ‘xxxxx’.

 

Step#2) Created function Sandeep

 

 

 

 

 
 

 

 Step#3) 

 

 

 

 

 

 

  

Now run the below statement –

 

APPS Schema

Select count (1) from PO_Headers_all  

>> 100 rows selected

 

XXXXX Schema

Select count (1) from PO_Headers_all  

>> 10 rows selected

 

APPSQUERY Schema

Select count (1) from PO_Headers_all  

>> 100 rows selected

 

 

VPD is one of the major changes in MOAC concepts in R12.