OSI / OTI table data mismatch, OTI being subset of OSI
Hi,
We have a custom application where we fetch count and data for all provisioning records assigned to logged in user.
To fetch details of open provisioning tasks, Oracle recommendation is to use OTI table.
When we studied current system, we had below findings regarding behavior of OSI and OTI tables -
1. When request is submitted to OIM, approval process is triggered depending upon approval policy.
2. this workflow is responsbile for creating approval tasks.
3. when approval tasks are approved, request in OIM is closed and provisioning task for the system gets created.
4. OIM database tables which are used to fetch these provisioning tasks are OSI/OTI.
5. According to Oracle documentation, OTI table is subset of OSI table.
6. when we checked both these tables, we found that entry in both these tables is not exactly same.
7. For a particular request, OSI table had OSI_ASSIGN_TYPE as 'Group' and OTI table had OSI_ASSIGN_TYPE as 'User'.
8. As OTI table has osi_assigned_to_ugp_key value as null, this table cannot be used.
Iit looks like OTI table does not hold correct data. And OSI table has around 9.6 million records. So when we query OSI table we get correct data but it takes 6min to get the data.
We are now looking for the issue why there is this mismatch and how can it be solved ?
Regards,
Deepika