Hi Folks

Firstly can I say, these forums are great - only been signed up for a couple of weeks but they've been a huge help looking through some previous topics etc...

We have a requirement to link HR to Active Directory.
This isn't for Security or anything like that but just simply a nightly job that will take some date from Oracle (Names, Addresses, Position, Supervisor) and transfer that data to Active Directory and take some data from Active Directory (AD Username, Email address, phone number) and transfer that to Oracle.
An initial load has been done to put the current AD Username into Oracle and to put the Oracle Employee_Number into AD, so we have identifiers in place.
I could do with some help though as to how to work a nightly process that will only take changes in this data over to the required system, So I guess my question is split in 2

1.If I receive an output from AD (csv?) of the changes that day, how am I best to upload it into Oracle?
2.How am I best to extract output from Oracle (csv?) of the changes that day that I can give to the folk who run Active Directory?

Really interested to hear any views.

Thanks
Martin

Martin

Flattery will get you everywhere!

For a bit of clarification, where in Oracle HR do you hold the AD stuff, and what changes would happen in AD that would want to be reflected in Oracle HR?

At the moment I'm just holding the AD Account Name in a flexifield held against the person on per_all_people_f.

We would take any updates to email address, phone numbers and office locations from AD.

Thanks

Martin

I think there are 3rd-party things one can purchase to do this (just ask Dr Google!), but licencing costs might be an issue.

If you are looking to write something in-house, then of course it's possible - certainly from the point of view of changing those items of info - there are apis available for all that stuff you mention, assuming the items are being stored in the logical place. The issues will be:

Making sense of the 'date-effectiveness' of the change from AD to Oracle - that is, AD probably won't have the notion of date-track, whereas Oracle HR patently does! For assignments and person records at least.

For sending stuff back the other way, detecting what changed since the last time the interface ran: a materialized view, refreshed at the end of processing, would probably fit the bill there. Your extract process would probably compare the items of information as they currently look with what they looked like the day before (via the MV); and act accordingly.

All you need to worry about then is the uploading/extracting to/from AD - simples!

Hi

Just to update.
I've got the materialized view in place and it seems to be working nicely in detecting what was changed since the previous day and extracting that information via a small sql script into a .csv.

The guys who deal with Active Directory are doing the import into it and the export from it so I don't need to worry about those bits.

What I have left now is a file containing the changes in Active Directory. And I'm still not sure the best way to get them into Oracle. They will need to be datetracked as of the date they are being entered. Any further thoughts/info on this bit?

Thanks

Martin

I guess you need to confirm the entities that are affected - as mentioned earlier, it looks like you'll need to consider uploading to various entities.

I would assume:
email address held in person entity
phone held in phones entity
location held in assignment entity
I would also assume there is never the requirement to create an entity from scratch, although I accept that might not be a valid assumption!

Updating the person record, for example, would involve use of hr_person_api.update_person

With all these update apis, you supply the effective date, the primary id of the record(e.g. person_id), the effective date (which by the sound of it will always be trunc(sysdate), the object version number of the record as at that date, and the date-track mode (where the entity requires it - not phones, oddly enough) - typically this would be 'UPDATE' all the time, although there could easily be the need to alter that.

At the risk of being seen to try and design the solution here (never a good idea on this forum!), you have options (including Web-ADI), but if you are after a fully-automated solution, it would probably involve something, running as a periodic concurrent program, that detects that a file has been received (in a specified starting directory somewhere), then reads the contents into a custom table that then can be processed via your main uploading program(s). You might, in that custom table, also make provision to hold a status and possibly an error message (should the api have encountered any problems) against each line. Once the process has dealt with the file, move the file to some sort of archive directory, out of the way.

A bit simplistic, but it might give you some food for further thought.