1. Get rid of all advertisements and get unlimited access to documents by upgrading to Premium Membership. Upgrade to Premium Now and also get a Premium Badge!

Date track history is updated by the Anomunos for all historic changes

Discussion in 'Oracle HRMS & Payroll' started by bazthejockey, Jul 8, 2014.

  1. bazthejockey

    bazthejockey Active Member

    Likes Received:
    Trophy Points:
    HI, I wanted to ask your advice on auditing the HRMS system as the HR teams and users use auditing on the LAST_UPDATED_BY field on many of the date tracked tables such as the assignments form who has last updated a leavers assignment of an employee’s record on the PER_ALL_ASSIGNMENTS_F table. Discover reports are used with this to keep track of changes. I have advised the users that the last_updated_by field should not be used as their primary focus for auditing as the system can update and overwrite the last update by a real FND user. It also can not show other users on that day if there is more then one change a day but please advise me otherwise?

    our biggest critical issue seems to be that there was an EIT data in the assignment table which was causing issues with a bespoke system. The decision was to update all the EIT data as NULL which did fix the issue with the bespoke application but for those who had any historical changed all of the LAST_UPDATED_BY rows of all data are all updated by the ANONYMOUS user and not their actual HR user for auditing.

    Is there a possible work around to fix this and what would be the recomended way to deal with this issue?
    is there a recommendation for Auditing to keep the date track of the history of employees changes by an FND user?

    The HR super-users are considering rolling back the assignments table to a backup 3 weeks ago which is not my recommendation as the amount of work would be lost.

    Thanks for the help as always and spelt ANONYMOUS wrong in the title, lol

  2. rajenb

    rajenb Forum Expert

    Likes Received:
    Trophy Points:
    Hi Barry,

    I'm sorry there's hardly anything you can do now to correct the situation.

    I'll just try to contribute with a few comments and questions which may help us to understand the situation (and try to avoid similar occurrences in future...):

    Yes, you're 100% correct. The "UPDATE" mode in Oracle HRMS, does indeed create a new record and keep the history but automatically changes to "CORRECTION" if "updates" are done within the same day. You can trap such updates by use of database triggers or API Hooks based on specific events catered for in EBS - you usually insert into a custom table and base your reports on them.

    Was it done by Oracle supplied APIs ?
    I don't understand how assignment data got updated when you updated/"deleted" the assignment EIT.
    It's not supposed to update PER_ALL_ASSIGNMENTS_F table but only PER_ASSIGNMENT_EXTRA_INFO table.

    Anyway, in general, when we do such massive updates (similar to a migration), it's recommended to set the following in your PL/SQL script:
    Code (SQL):
    hr_general.g_data_migrator_mode := 'Y'
    This ensures that the standard audit features (LAST_UPDATED_BY /LAST_UPDATE_DATE) are not activated.... This is for .. next time !:p

    I guess not :( if you don't have a backup before the operation. Such an intervention (massive updates) should be run on a test environment, verified and then applied on Production instance (after ensuring that you have a proper backup). If you had at least a backup of PER_ALL_ASSIGNMENTS_F, a script could have been written to re-apply the "audit" updates ...

    It depends on the business requirement, Oracle HR module already already provides the date track feature (with the limitation we've highlighted above).
    From an IS or Technical Support perspective, you audit all columns and extract ad-hoc reports (as you're doing now) using standard reporting tools.