Does anyone have any information at all on this subject?

Client is starting to talk about it and we need to stay at least a few pages ahead! ;o)

Many thanks,
Gavin

I believe Mr Cooper is your man here, Gavin - From what I remember, VPD underpins that whole project what he has been on since the old king was on the throne!

Hi Gavin
If you allow me a couple of hours this morning to gather my thoughts i will try to cobble together some notes of the subject - i will need to remove client-specific references (where possible).

Cheers
Baz

Gavin

To dampen your appetite, take a look at the following article on VPD found on the Oracle Technology Network

Its fairly generic - i will add meat to the bones by blathering on about its use within HR/Payroll once i have compiled my notes.

Where to start...
I will start by saying that the following text is taken from my own memory of having worked on this implementation for (what seems like) a lifetime. I have not directly copied, lifted, or plagiarized any official project documentation for this posting. The information which follows is not project confidential, and has been published and presented at various meetings (including Open World) throughout the lifecycle of this project. It is therefore regarded as being in the public domain. I will not be publishing any code within this post, nor will i provide any code via email following this post - i do not own the IPR to the code and therefore i am not in a position to provide it - please do not embarrass me by asking for it.

Right, thats the legalese out the way...

In order to indicate how the NHS have used VPD within the HCM applications context i will have to give you a very brief overview of the implementation for the NHS (without giving too much information away).
The NHS for England and Wales, have implemented Oracle HCM Applications: We have put in HR, Payroll, Recruitment, OTA/OLM plus a huge swathe of customisations. All of this has been implemented as a single business group. Within the NHS there are approx 600 employing authorities (give or take the odd merger/de-merge etc). These are in effect separate employers. Each can have its own set of locally agreed rates and conditions, as well as using a set of National (NHS wide) rates and conditions. Sitting on top of this is the national flexfield structures (key and DFF)

This means that we have a set of National Jobs and Grades and Elements, but also have local Grades and Positions and Elements, across the whole of the NHS.
The fundamental principal behind the implementation was that each authority (aka 'Trust') can only view their own local data plus any items set up to be National. In effect we are simulating multiple business groups within a single BG.
This viewing restriction also applies to transactional data. So this means that Trust 'A' can see their employees, applicants, payrolls, payroll runs etc, but cannot see such data that belongs to Trust 'B'.
The full list of items covered by this viewing restriction is long (and growing), but includes the following...

• Payroll Defintions - Local only, no national data allowed
• Payroll Calendars - local only, no national data allowed
• Pay Scales - Can have Local and National Scales
• Grade Scales - Local and National
• Scale Rates - Local and National
• Grade descriptions - Local and National
• Element Types - Local and National, although there are very few local elements
• User Defined Tables - Local and National
• User Defined Balances and Balance Feed - Local and National
• OSP and OMP Schemes (custom not Core Oracle) - Local and National
• Vehicle Details - Local data only
• Element Sets - Local and National
• Assignment Sets - Local only
• Payroll Actions - Local only, no national data allowed
• Element Links - Mainly national data with a few local links for the few local element types
• Payment Methods (personal and Org) - Local only, no national allowed
• Positions - Local data only, no national values allowed
• Locations - National and Local
• Organizations - Local data only
• Hierarchies (Org and Position) - Local data only
• Recruitment Activities - Local data only
• Vacancies - Local data only
• LOOKUP TYPES - National and Local
• LOOKUP VALUES - National and Local - can also have Local values within National lookups!
• VALUESETS - National only, as all flexfields are BG wide
• VALUESET VALUES - National and Local
• Responsibilities - National and Local - see notes later

I could go on, but you get the gist.

So how is this done? For fear of blowing the maximum post size i will continue in another post...

So how is this done?

The key is to find someway of 'tagging' these data items so that we can tell if a given item is local to a Trust, or is National.
For many of the data items we have the luxury of having access to a descriptive flexfield attribute. Where this is available, we have reserved an attribute to hold this tag (known in NHS project terms as the VPD code or TRUST ID). Were possible, we have used the same attribute number across the entities (although there are some exceptions to this).

For some entities however, there is no descriptive flexfield segment to use. In this case, we have resorted to having a central custom table. This table will hold the entity reference (ie table name), the entitiy key and the VPD code. Given the size and complexity of this implementation, this custom table is partitioned to provide performant non-locking access across the system.

For items that are national, we do not populate a value into this field. This enforces the assumption that an item is nationally available unless it is tagged with a value.
This also enforces the rule that an item can only be owned by 1 Trust.

Each trust is allocated its own unique Trust ID. This is a 3-character code, that is allocated from a centrally controlled list (ie the Trusts cannot pick and chose their own Trust ID).

I can hear you getting restless at this point.

Good question!.
This would be true, however by enforcing certain business rules upon the creation of such data we can get around this problem.

The example i will use is Organization.
The chances are, that with 600+ Trusts all using the same system, there is the likelyhood that one or more of them will have a department called Human Resources (say).

In a bog-standard clean HRMS install, you could not create 2 organizations with the same name of "Human Resources", as the name has to be unique within the business group. This is enforced via a database index constraint.

So for the NHS, Trust A create an organization called 'Human Resources' but they are forced into calling it 'nnn Human Resources', where nnn is their unique Trust ID.
Trust 'B' can create their Human Resources organization, and this gets called 'zzz Human Resources', where zzz is their unique Trust ID.

There are certain 'quirks' of the system that behave differently under VPD as well.

In a clean HRMS install, the system only allows you to have one primary organization hierarchy per business group. For the NHS each Trust requires to have its own primary hierarchy. However since the "1 primary per BG" rule is enforced by code, rather than by a database constraint, we can get around this quite happily without having to hack the core code.
For example....
Trust A create their primary hierarchy. It is local to them, and owned by them.
Trust B go ahead and create their primary hierarchy. As far as the system is concerned, at the time this hierarchy is being created it cannot see Trust A's hierarchy and so allows Trust B's hierarchy to be created.

I digress slightly, so moving back on track...

We have allocated somewhere to 'hold' the tagging attribute, either directly on the entitiy, or in a central repository. How do we actually maintain these tags and ensure they are populated?

For that, we have to jump onto another post...

Where was i....ah yes. How do we do it?
It would be easy to say that it is all achieved by Triggers. Whilst this is partly true it does not encapsulate the whole solution.

Triggers play an important part in the implementation of VPD within the NHS solution. Each table 'controlled' by VPD has a custom trigger. Its job is to maintain the VPD attribute (or the central repository). However the trigger needs to know what the VPD ID value is. This is done using session information (contexts).

I, as an applications user, sign into the application and am presented with my list of Responsibilities. As a user for a given Trust i am likely to have a selection such as

• nnn Payroll Administrator
• nnn HR Administrator
• nnn UK Super HRMS Administrator

etc

Where the 'nnn' is my trust ID
I then choose one of my responsibilities...I choose nnn Payroll Administrator
I get presented with my menu of available screens etc. Behind the scenes however, my session environment has been set so that i am now operating under the 'Trust nnn' context.

All tasks that i now do as this user are done as this trust user. This means if i go to the employee details screen and perform an open query i will only see employees within Trust nnn. Obviously you also have to add in the standard HRMS security layer onto this. So i probably would only see a restricted set of employees anyway, however i hope you get the gist.
In fact, had i chosen the "nnn UK Super HRMS Administrator" responsibility then this VPD context restriction would be more aparent, as i am operating at a 'view all' HRMS security i should be able to see all employees, but VPD restricts my viewing to just those within my Trust ID.

The same then goes if i chose to start creating some more data.
If i go and create a new position when i click to save the data the position is created, however because i am a 'local' user this position is stamped with my Trust ID. The trigger operating on the positions table ensures the securing VPD attribute is populated with the session context of my Trust ID.

If you have read the opening article link from the Oracle site, then you should understand that VPD is in fact applying modifiers to every statement that is sent to the SQL engine to be executed.
In simplified terms: I do an open query on the positions table, this is passed as (i know that it uses the view in reality, before you jump in to correct me clive )

This SQL is modified by VPD to read

If the entitiy is controlled via our central attribute table then the modifier contains slightly more complex code. For example the FND_USERS table does not have DFF attributes, so it has to be controlled in this manner.
So an open query on the users table of would be modified by VPD to read


Not only does this operate from within the application, but also operates from any SQL-access medium. So if we logged in via TOAD (say) and set our Session to that of an applications user then the same SQL modification would take place to limit what we see.

I'm hoping that this has given you a (somewhat simplified and brief) overview of VPD.
If you have any specific additional questons then i'd be happy to try to answer them where possible.

Regards

Barry