A long while back, there was a question about linking the transaction ID to a notification ID and item key within Oracle HR – I wrote the blog post below shortly afterwards, but somehow it never got published. So, somewhat later than I’d planned, here’s the answer!
The original question can be found here and reads
how can i link Transaction_id in HR_API_TRANSACTIONS table with NOTIFICATION_ID and ITEM_KEY in wf_notifications table?
There are a couple of ways that you can link the different pieces of information – it depends on what your starting point is, though.
Firstly, the HR_AMEUTIL_SS package contains functions to find the item type and the item key from the transaction ID:
SELECT hr_ameutil_ss.get_item_type(transaction ID) , hr_ameutil_ss.get_item_key(transaction ID) FROM DUAL /
From here, you can then find the notification ID by looking at the WF_NOTIFICATIONS table, eg.
SELECT notification_id FROM wf_notifications WHERE message_type = hr_ameutil_ss.get_item_type(transaction ID) AND item_key = hr_ameutil_ss.get_item_key(transaction ID) /
If, however, you have the notification ID and you want to get to the transaction ID, then you need to look at the Workflow tables to find that information:
SELECT wiav.item_type , wiav.item_key , wiav.number_value FROM wf_item_attribute_values wiav JOIN wf_notifications wfn ON ( wfn.message_type = wiav.item_type AND wfn.item_key = wiav.item_key ) WHERE wiav.name = 'TRANSACTION_ID' AND wfn.notification_id = <i>notification ID</i> /
The number value for the item attribute will contain the transaction ID and you can then work from there to do what you need.
Apologies it’s taken over a year for me to get round to publishing this one – there may well be some more posts being published which I never got round to making public soon!