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_TRANSACTIONS
HR_API_TRANSACTION_STEPS
HR_API_TRANSACTION_VALUES
This is show in SQL as below
select name, varchar2_value, date_value, number_value
from HR_API_TRANSACTION_VALUES
where TRANSACTION_STEP_ID IN
(select TRANSACTION_STEP_ID
from HR_API_TRANSACTION_STEPS
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
In the old version of Self Service HRMS, all the temporary information is captured in the following tables.
HR_API_TRANSACTIONS
HR_API_TRANSACTION_STEPS
HR_API_TRANSACTION_VALUES
This is show in SQL as below
select name, varchar2_value, date_value, number_value
from HR_API_TRANSACTION_VALUES
where TRANSACTION_STEP_ID IN
(select TRANSACTION_STEP_ID
from HR_API_TRANSACTION_STEPS
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.
DECLARE
v_trans_api_result VARCHAR2(1);
BEGIN
v_trans_api_result :=
hr_transaction_swi.commit_transaction
(
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
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.
DECLARE
v_trans_api_result VARCHAR2(1);
BEGIN
v_trans_api_result :=
hr_transaction_swi.commit_transaction
(
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
Apps2Fusion Articles 
Comments
Thank you for such a good post.
I have one question regarding HR_API_TRANSACT ION table. I noticed that after few period of time, records seize to exist in this table. Can we know in what conditions/even ts records are deleted from this table?
Regards ,
Saurabh
When the workflow completes, the records disappear.
Howe ver in reality, this happens somewhere in between the flow once the transaction has either been applied to the base tables via commitAPI or rolledback
The refore I call this data in hr_api_transact ions as in-transit data
Thanks,
A nil
I need a small info regarding""TRAN SACTION_DOCUMEN T\" in HR_API_TRANSCAT IONS table . For the some of the process like leave of absence application is creating transcation document in HR_API_TRANSCAT ION table. But for some processes like Special information type or New hire process system is not creating any transcation document. Can you please do let me know what is the functionality behind this !
Thanks in advance
Regards
Have a look at following two tables
HR_API_T RANSACTION_STEP S
HR_API_TRANSA CTION_VALUES
T hanks,
Anil :) :)
I have made a custom workflow for approval but i am not getting item_key in hr_api_transact ion of custom worklfow .
I want to integrate custom workflow with AME.
so please help me.
Regards,
D akshesh Patel
All your articles are very informative and useful. Thanks for sharing these with us.
Please let me know how the value in the field "api_name" populates (in the table hr_api_transact ion_steps) whenever a transaction is initiated in SSHR? Is there a setup somewhere linking the various Processes and the API to be used?
Would appreciate your response.
Th ank you,
Sushmita
RSS feed for comments to this post