When a Self Service HRMS user creates any data from the screens, such data goes and resides in some temporary tables. This data is retained within the temporary tables until the transaction is Completed.

A transaction in this example gets completed when Holiday Rejection complaint is either Rejected or Approved. These temporary table names begin with name HR_API%. In reality, the data entered by the user is either stored within just one table i.e. HR_API_TRANSACTIONS or within HR_API_TRANSACTION_VALUES. When the data is stored in just the HR_API_TRANSACTIONS then the entire data entered by the user is captured in a CLOB column. This feature was introduced couple of years ago, whereby for performance reasons the temporary data is captured in a clob column named HR_API_TRANSACTIONS within HR_API_TRANSACTIONS. This CLOB column contains XML contents. By now, you might have the following questions 

1. What exactly is captured in CLOB column hr_api_transactions.transaction_document
2. How does Self Service HRMS decide whether the transaction data which is in-transit goes into CLOB or HR_API_TRANSACTION_VALUES.
3. How is this temporary data used by AME
4. How is the data transferred from temporary tables to base tables in HRMS.

In this article I will answer these questions, so that you can proceed with the AME bits that depends upon this information. In addition to this, you will also learn how the “SAVE FOR LATTER” feature works in HRMS.

As soon as you enter any data in Self Service HRMS screens and navigate to the next page, an entry is created in HR_API_TRANSACTIONS. Of course, the AME API’s are called “during” the approval process which happens after the data has been submitted for approval. This means at the time of “AME Engine processing” the data entered by the Self Service HRMS Screen is in-transit, i.e. in temporary tables. In fact, within the AME, you will be passed the transaction_id from hr_api_transactions. Therefore the AME engine has a handle to the entire in-transit data via hr_api_transactions.transaction_id. Using this TransactionId handle, you can either query the CLOB XML in SQL or you can query HR_API_TRANSACTION_VALUES.

1. What exactly is captured in CLOB column hr_api_transactions.transaction_document
In this column the entire state of the Application Module is captured. Application Module in OA Framework manages the transaction state and also serves as a cache of the data in the memory. In this example, when the user enters a Holiday Rejection Complaint and submits for approval, then entry is created in table HR_API_TRANSACTIONS, HR_API_TRANSACTION_STEPS and HR_API_TRANSACTION_VALUES. This also serves the purpose of SAVE FOR LATTER. When you save a transaction for latter, effectively the entire data in the cache is dumped into temporary tables. If the self service user wants to restart the transaction after few hours/days, then entered data is re-queried from these HR_API temporary tables

2. How does Self Service HRMS decide whether the in-transit transaction data goes into XML CLOB HR_API_TRANSACTIONS.TRANSACTION_DOCUMENT or into HR_API_TRANSACTION_VALUES.
In the old version of Self Service HRMS, all the temporary information is captured in the following tables.
HR_API_TRANSACTION_VALUESThis is show in SQL as below
select name, varchar2_value, date_value, number_value
where transaction_id = 76184)
and (varchar2_value is not null or date_value is not null or
number_value is not null)
In the latter versions of 11.5.10, “some” of the screens in SSHR have started dumping the Application Module state into CLOB column. Therefore, even in R12, you will notice that Special Information Types screen continue to use HR_API_TRANSACTION_VALUES as shown in image above. However some other screens like Talent Management Object Setting use HR_API_TRANSACTIONS.TRANSACTION_DOCUMENT in both 11.5.10 and R12. This is shown as below
3. How is this temporary data used by AME
Inside AME, you usually write SQL statements against AME Attributes and AME Approval Groups. In those SQL statements, you can reference HR_API_TRANSACTIONS.TRANSACTION_ID by using convention :transactionId
4. How is the data transferred from temporary tables to base tables in HRMS.
Oracle executes an API named hr_transaction_swi.commit_transaction which takes the transaction_id as parameter.
This transfers the data from temporary tables to the base tables.
v_trans_api_result VARCHAR2(1);
v_trans_api_result := 
p_transaction_id => n_hr_api_transaction_id –pass the transaction_id here
,p_validate       => hr_api.g_false_num
,p_effective_date => sysdate
END;The above API is usually called when Self Service HRMS Transaction is completed, for example when Approved. This is executed via one of the activities in HRSSA workflow after AME processing is completed.
In the event AME approval is not required, hr_transaction_swi.commit_transaction is called immediately after the user submits the data from Self Service HRMS screen

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s