We are having an odd issue and I thought I would check in here to see if anyone has had a similar problem and if there is a fix for it.
It happens when we try to set a future dated location on an assignment.
1) We set the after effective date value in EBS to the future value we want.
2) We change the location on the assignment we want and save the record.
3) So the old assignment record should have its effective_end_date updated and a new insert record should be created.
4) There are numerous triggers that fire but the important two are as follows
a) The after every row trigger fires which calls a package to insert the assignment id, eff start and end date and a few other values for the update into an internal table.
b) The after statement trigger fires which calls a package which grabs the values in the internal table and passes them to another package which populates a staging table with information regarding the update.
c) The after every row trigger fires which calls a package to insert the assignment id, eff start and end date and a few other values for the insert into an internal table.
d) The after statement trigger fires which calls a package which grabs the values in the internal table and passes them to another package which populates a staging table with information regarding the insert.
4) Now this is the weird part
a) While the package mentioned above is processing the update record a query is performed against the assignment table to get more information to insert into the staging table
b) The query is using the person id and eff date of the update record to get this record so it should only return one record. It is returning two records.
c) There is nothing wrong with this query. When I run it in TOAD or sqlpus it works fine. I even stripped the query down to the bare essentials and hard coded the person id and eff date into the query and the package still returned two records.
d) I output the eff start date, end date, location id from the query to a log so I could view them.
e) The end date of the two records retrieved are for the update and insert record. However the eff start date and location fields which should be different are the same. They are from the update record.
f) It is like the update record was updated but the insert record was still in the middle of being created when the after statement trigger was fired.
g) I put a 60 second delay right before the problem query but the problem did not go away.
Has anyone else here run into this problem?