Loading Data into Oracle HR

When we implemented Oracle HR we worked closely with an Oracle Partner. We split the work: they focussed on the configuration tasks whilst we concentrated on our data. We decided to use the APIs rather than learn Data Pump: we didn't know Data Pump and adding another dependancy into the project did not seem sensible.

We created a table for each API with columns to match the API parameters. This allowed us to create a series of SQL scripts to populate and manipulate the data in easy stages before a simple API call to load it. Simple scripts like this made it much easier to debug and fix errors.

We added a couple of extra fields to the API tables: TRANSACTION_STATUS and ERROR_MESSAGE. The first let us filter on the rows to be loaded and record success or failure, whilst the second was used to record any error messages for that row (essential for debugging).

Selecting the right APIs to use took soem time, but the hardest thing to get our heads (and the data) around was the EFFECTIVE_START_DATE and EFFECTIVE_END_DATE fields used in the PER_ALL_PEOPLE_F and PER_ALL_ASSIGNMENTS_F tables. Knowing when to UPDATE a record and when to apply a CORRECTION is important (UPDATES create a new row in the table on the specified effective date with the current record ending on the day before).

In our case we were implementing HR onto an existing Financials installation. We had a fixed start date for our HR data of the first of April (the start of our financial year) and much of the people data predated this. The first task was therefore to update all the existing records with an effective date of the first of April.

We then created all the additional people that were missing from Financials. We used the APIs to correct all the records starting on the first of April to ensure these had all the correct information.

The final stage was to cycle through data changes from the first of April to reflect all staff movements and career changes since then.

For each career change we had to call two API's as the information on which position an assignment is for and the supervisor for an assignment cannot be updated by a single API call (or at least not by one we found!).