I am using Oracle EBS 12.1, db 10g, and looking for the correct way to accomplish a custom task.
The employee's employment category (Salaried, Fulltime, Partttime, etc.) is kept in the assignment table. The users can either change the employment category as a 'Correction' (the wrong employment category was originally entered) or as an 'Update' (the employee received a promotion or demotion). The only way to tell the difference from the database is to see if there is a new set of effective dates.
Incidentally, whether the user intended a 'Correction' or an 'Update', the employment category is always implemented in an update statement in the database: if the user intended an 'Update', a new assignment row is created with a new set of effective dates and then that row is updated with the employment category; if the user intended a 'Correction', then the existing row is updated with the employment category.
I put a trigger on the assignment table after an update of the employment category, which calls a procedure that inserts a row into a custom table. However, prior to inserting the row into the custom table, I want to check the effective dates in the assignment table. However, I can't do this because I cannot query the assignment table until the transaction is completed.
I understand that I could use PRAGMA AUTONOMOUS_TRANSACTION to get around this, but I have heard this is not necessarily always a good solution.
After the row is inserted into the custom table, it sits there until a user runs a concurrent program that does something with the data in the table. I suppose I could prepend to this concurrent process a check on the data to eliminate the rows that I really don't want.
What is the correct way/best practice to implement this custom task? Some background info, or reasons why, would be much appreciated with the answer.