We are currently live with payroll and looking at implementing some of the other oracle apps. The payroll department are concerned with the fact that consultants will be coming in to use an environment cloned from production that will contain payment information about their employees.

Is there an API, or something, available that can purge all the payroll, balance, bank, etc tables? The only information that they would like to leave would be the person and assignment records.

Thanks for your help

Hi Marina
There are lots of API's, many of which could probably delete some of the data for you, although this would have to be done in the right order.

You could also consider doing a mass of roll-backs on all the payroll processes, although this would start to get arduous as time rolls on.

There are other options which may be preferable to your client...
1. You may wish to consider obfuscating personal details to make them less identifiable? Things like NI number (or your Legislation equivilent), employee number, name etc. There are some 3rd party companies which sell software which can scramble the data, or you could hire a consultant to do it for you (hint hint).
2. How about creating a training database? Use a 'gold' master configuration environment with all the associated setup date (orgs, jobs, positions etc) and then generate sample employee / assignment data in it. This can then provide a development instance for your developers to play in?
3. Finally, you could get your developers to sign non-disclosure agreements and get them vetted/security cleared etc and simply trust them loose on a production copy.

Here at NHS we have tried all variations. The obfuscation/scrambing of the data did not work too well as we originally used these environments for support work too, and by scrambling the data it made it difficult to correctly identify issues with people!

We now have 2 systems in place.
For pure development work, we have a cloned instance containing the config and set-up, which some then use and create their own test data in. I built a set of processes to generate random data - creates employees, assignments (with multiple date-tracked changes), addresses and self-service users etc.
For support and system test work, we use cloned copies of the production system, and then control access to certain accounts only. People requiring access need to sign non-disclosures etc.

I guess your solution would depend on how much effort the client is willing to invest (time and money), against how much trust they place in their developers. We're not all hackers

Regards

Baz

Ok I have the bar of soap in my hand already ...but.....

Depends how 'clean' you want to be, you could delete direct from the tables without using the API.

A few years ago I was on a site that wanted to 'mask' the database details

This included deleting all the execs so no-one could see anything (even thru the masking)

I seem to remember there where about 50 steps to deleting the Execs.....

Baz....the dreaded killer script ?? LOL

Marina
Hi - hope you're well. CT said he'd seen the post and thought I should get in touch. For the last year here at 'not the N*S but very big UK client'... I have been working with a tool called Optim from IBM which is a packaged solution for masking. Oracle also do one and we had a look at most of the options. EDS chose IBM's Optim... However, after working with it for a year and having talked to others on other projects using packaged solutions, it is clear that none have really ever done anything in anger (or anything at all - Optim) on Oracle eBiz. We have struggled on through, but I think to be honest it would have been as well to have gone with what Barry said and do it in house. Even with a packaged solution you still need to tell it what tables to mask and how to mask them or as is the case with us subsetting the environments for development, you have to tell it which tables to trunc etc. And most solutions don't support application level integrity so you only find out you've broken something when you bring the app up...
So if you're going to do it in house then you could look at iSetup which can give you an idea of the key tables that need to be populated for certain entities such as a person / position etc and then from there you'd have a good idea of what needs to be masked where.
Give me a shout if I can add any more info.
Take care
Corinne

I have tried the non-disclosure route, but the customer doesn't feel that that is enough cover. I did try to explain that they are more like to have internal people going through the data than external people, as it has little relevance to a consultant!

We have discussed scrambling the data, but as Corinne mentioned I have come across issues with this too. Baz, I'll see what I can do about getting you over to write the obfuscating(!) code I like the idea of creating a completely fresh environment without any personal payroll data in it.

The DBAs have been thinking about the killer script idea, but were hoping to use a standard api, just in case.

Thanks for the replies, certainly provided some questions I can go back to them with...

Noooo not the killer script!

As its official unofficial minder i have it on good authority that its hung up its deletion hat and now spends its days ant farming...

Sun-cream, hat, passport and plsql toolkit at the ready

Lubble

It is a matter of public knowledge that all Bazzer's code is self-obfuscating - that's why he never documents it! But no worries - just leave him alone with your instance for a few minutes, and it will be guaranteed to have no personal data left in it by the end! Especially if you tell him not to touch it! In fact, it probably won't have anything in it, even setup data... or tables for that matter.

Slander Sir. I shall see you swing from the yard-arm for this.

Hee hee... yes, we'll leave the killer script to the ants!! They were thinking of creating something themselves, although they have been warned!!

Keep the sunblock at the ready

Thanks for the info.....