Oracle SSHR Absence Transactions, Selfservice transactions – where did my data go?

When Oracle build Self Service they treated it as a new front-end of the system. To be able to use the standard API’s (Application Program Interfaces) they invented a series of new tables. In these tables the data is stored until everyone in the workflow has approved the new data. These tables are called transaction tables (they store transactions data…).

There are three tables which are holding the transnational data:

1. HR_API_TRANSACTIONS
2. HR_API_TRANSACTION_STEPS
3. HR_API_TRANSACTION_VALUES

For every workflow a record in the HR_API_TRANSACTIONS table is created. A workflow can be a set of different actions, i.e. changing the assignment and the supervisor of an employee. So, Oracle sees this as steps. In this case there are two steps.

For every step Oracle records the data in the HR_API_TRANSACTION_VALUES table.

To see all the data for a transaction you can use this query

SELECT hat.STATUS,hat.*
FROM hr_api_transactions hat,
hr_api_transaction_steps hats
WHERE hat.transaction_ref_table = ‘PER_ABSENCE_ATTENDANCES’
AND hat.transaction_group = ‘ABSENCE_MGMT’
AND hat.transaction_identifier = ‘ABSENCES’
AND hat.transaction_ref_id IS NOT NULL
AND hat.transaction_id = hats.transaction_id
AND hat.CREATOR_PERSON_ID = :p_person_id;

 

SSHR, Following is the list of tables where transactions or approvals history data stores.

1- PQH_SS_TRANSACTION_HISTORY
2- PQH_SS_STEP_HISTORY
3- PQH_SS_VALUE_HISTORY
4- PQH_SS_APPROVAL_HISTORY

 

List of statuses and their meanings in hr_api_transactions table.

Here is a list of statuses and their meanings in hr_api_transactions table.

D – Deleted
C – Cancelled
E – Error
RI – Return for Correction
RIS – Return for Correction + Save For Later
S – Save for Later
W – Review page
Y – Pending Approval
YS – Pending Approval + Save for Later

Important note: When the workflow is approved the data is written into the standard Oracle HR tables. The transaction records are purged, so the transactional data is gone!