In this article we will look into getting the list of pending approval transactions for an employee.
If the business objects are configured for approval, then any transaction on that business object will go for approval as per the approval rules configured in the application. When the transaction is under approval, no other transactions can be done on the business object or the dependent business objects.
First the transaction should be approved or rejected and then any other transaction can happen on the business object. Till the transaction is approved, the transaction data will reside in the approval related tables only and it will not get saved to the actual transaction tables. Once it is approved, the data will be moved from approval related tables into the actual transaction tables.
Approval related tables:
HRC_TXN_HEADER – Header table with details of transaction id.
HRX_TXN_DATA – Detail table which stores details of transaction for the transaction id. DATA_CACHE field stores the actual details of transaction in xml format.
HRC_TXN_ERROR – Any transaction related errors are stored in this table
FA_FUSION_SOAINFRA.WFTASK – This is the Workflow task table and stores the information of Created by, Assignee, Created Date, End Date, Last Updated Date, etc. identificationkey should be mapped with the transaction id of HRC_TXN_HEADER.
FA_FUSION_SOAINFRA.WFTASKHISTORY – This is the Workflow task history table.
FA_FUSION_SOAINFRA.WFTASK_VIEW — filter on WFTASK where STATE IS NULL OR STATE IN (‘ERRORED’, ‘STALE’, ‘WITHDRAWN’, ‘EXPIRED’)
FA_FUSION_SOAINFRA.WFTASKHISTORY_VIEW – history (join on TASKNUMBER)
FA_FUSION_SOAINFRA.WFTASKTL_VIEW – title (join on TASKID)
FA_FUSION_SOAINFRA.WFCOMMENTS_VIEW – comments (join on TASKID)
FA_FUSION_SOAINFRA.WFATTACHMENT_VIEW – attachments (join on TASKID)
FA_FUSION_SOAINFRA.WFASSIGNEE_VIEW – assignee (join on TASKID)
I have seen people posting many questions on our telegram group and cloud connect related to approval related tables and hence this post.
Table of Contents
SQL Query to find out the pending approval transactions for an employee:
select txnd.transaction_id, txnd.status, txnd.state, txnd.created_by, txnd.creation_date, txnd.last_update_date, txnd.last_updated_by, txnh.module_identifier, txnh.subject, txnh.subject_id, txnh.object, txnh.object_id, txne.Fault_Date, txne.Error_Details, wft.TITLE, wft.OUTCOME as WF_OUTCOME, wft.STATE as WF_STATE, Wft.Assignees, wft.Assigneddate, wft.Approvers, wft.compositeinstanceid from Fusion.Hrc_Txn_Header Txnh, Fusion.Hrc_Txn_Data Txnd, fa_fusion_soainfra.wftask wft, fusion.hrc_txn_error txne Where txnh.Transaction_Id = txnd.Transaction_Id and txnh.transaction_id = txne.transaction_id(+) And To_Char(Txnh.Transaction_Id) = Wft.Identificationkey(+) and ((txnh.subject_id in (select distinct person_id from per_all_people_f where person_number in ('1234') )) or (txnh.subject_id in (select assignment_id from per_all_assignments_m where assignment_type ='E' and person_id in (select distinct person_id from per_all_people_f where person_number in ('1234') ))) or (txnh.subject_id in (select period_of_service_id from per_periods_of_service where person_id in (select distinct person_id from per_all_people_f where person_number in ('1234') ))))
SQL Query to find Pending transaction for Assignment Changes:
SELECT person.person_number||'-'||INITCAP (names.display_name) pp,person.person_number FROM hrc_txn_header txnH, per_all_people_f person, per_all_assignments_m asg, per_person_names_f names, hrc_txn_data txnD, FA_FUSION_SOAINFRA.WFTASK wft, per_persons ppos WHERE UPPER (txnH.Object) IN ('PER_ALL_ASSIGNMENTS_M') AND txnH.object_id = asg.assignment_id AND SYSDATE BETWEEN asg.effective_start_date AND asg.effective_end_date AND asg.person_id = person.person_id AND SYSDATE BETWEEN person.effective_start_date AND person.effective_end_date AND names.person_id = person.person_id AND person.person_id = ppos.person_id AND TO_CHAR (txnh.transaction_id) = wft.identificationkey(+) AND SYSDATE BETWEEN names.effective_start_date AND names.effective_end_date AND names.name_type = 'GLOBAL' AND txnH.transaction_id = txnD.transaction_id AND txnD.status = 'PENDING' AND txnH.MODULE_IDENTIFIER = 'Transfers' and wft.ASSIGNEESDISPLAYNAME is not null
SQL Query to pull data from DATA_CACHE field on HRC_TXN_DATA for Transfer status:
SELECT person.person_number, EXTRACTVALUE ( xmltype ('' || txnD.data_cache || ''), 'TRANSACTION/TransCtx/PreRowAsgCalcAttributeHash/Department') Department_old, EXTRACTVALUE (xmltype ('' || txnD.data_cache || ''), 'TRANSACTION/TransCtx/asgCalcAttributeHash/Department') Department_new, INITCAP (names.display_name) display_name, EXTRACTVALUE ( xmltype ('' || txnD.data_cache || ''), 'TRANSACTION/TransCtx/PreRowAsgCalcAttributeHash/BusinessUnit') BusinessUnit_old, EXTRACTVALUE ( xmltype ('' || txnD.data_cache || ''), 'TRANSACTION/TransCtx/asgCalcAttributeHash/BusinessUnit') BusinessUnit_new, EXTRACTVALUE ( xmltype ('' || txnD.data_cache || ''), 'TRANSACTION/TransCtx/PreRowAsgCalcAttributeHash/Location') Location_old, EXTRACTVALUE (xmltype ('' || txnD.data_cache || ''), 'TRANSACTION/TransCtx/asgCalcAttributeHash/Location') Location_new, 'InProgress' STATUS, (SELECT per_all_people_f.person_number||'-'||INITCAP (per_person_names_f.display_name) FROM PER_ASSIGNMENT_SUPERVISORS_F, per_person_names_f, per_all_people_f WHERE PER_ASSIGNMENT_SUPERVISORS_F.manager_id = per_person_names_f.person_id AND per_person_names_f.person_id = per_all_people_f.person_id AND PER_ASSIGNMENT_SUPERVISORS_F.MANAGER_TYPE = 'LINE_MANAGER' AND TRUNC (SYSDATE) BETWEEN TRUNC ( PER_ASSIGNMENT_SUPERVISORS_F.EFFECTIVE_START_DATE) AND TRUNC ( PER_ASSIGNMENT_SUPERVISORS_F.EFFECTIVE_END_DATE) AND TRUNC (SYSDATE) BETWEEN TRUNC ( per_person_names_f.EFFECTIVE_START_DATE) AND TRUNC ( per_person_names_f.EFFECTIVE_END_DATE) AND TRUNC (SYSDATE) BETWEEN TRUNC ( per_all_people_f.EFFECTIVE_START_DATE) AND TRUNC ( per_all_people_f.EFFECTIVE_END_DATE) and PER_ASSIGNMENT_SUPERVISORS_F.assignment_id=asg.assignment_id AND per_person_names_f.name_type = 'GLOBAL' )curr_reporting, /* EXTRACTVALUE (xmltype ('' || replace(txnD.data_cache,',','') || ''), 'TRANSACTION/BUSINESSDATA/AM/TXN/EO/PeriodOfServiceEORow/CEO/EO/EmployeeTermsDEORow/CEO/EO/EmployeeAssignmentDEORow/CEO/EO/AssignmentSupervisorDEORow/ManagerName/DATA[1]') */null prapose_mgr, asg.effective_start_date doj, EXTRACTVALUE (xmltype ('' || txnD.data_cache || ''), 'TRANSACTION/TransCtx/basicInfoAttributes/ReasonName')REASON FROM hrc_txn_header txnH, per_all_people_f person, per_all_assignments_m asg, per_person_names_f names, hrc_txn_data txnD, FA_FUSION_SOAINFRA.WFTASK wft, per_persons ppos WHERE UPPER (txnH.Object) IN ('PER_ALL_ASSIGNMENTS_M') AND txnH.object_id = asg.assignment_id AND SYSDATE BETWEEN asg.effective_start_date AND asg.effective_end_date AND asg.person_id = person.person_id AND SYSDATE BETWEEN person.effective_start_date AND person.effective_end_date AND names.person_id = person.person_id AND person.person_id = ppos.person_id AND TO_CHAR (txnh.transaction_id) = wft.identificationkey(+) AND SYSDATE BETWEEN names.effective_start_date AND names.effective_end_date AND names.name_type = 'GLOBAL' AND txnH.transaction_id = txnD.transaction_id AND txnD.status = 'PENDING' AND txnH.MODULE_IDENTIFIER = 'Transfers' AND person.person_number =nvl(:L_PERSON_NUM,person.person_number) and (EXTRACTVALUE ( xmltype ('' || txnD.data_cache || ''), 'TRANSACTION/TransCtx/PreRowAsgCalcAttributeHash/BusinessUnit')) =nvl(:L_BU_NAME,(EXTRACTVALUE ( xmltype ('' || txnD.data_cache || ''), 'TRANSACTION/TransCtx/PreRowAsgCalcAttributeHash/BusinessUnit'))) and wft.ASSIGNEESDISPLAYNAME is not null
SQL Query to get Current Approver and Next Approver for Resignation Transaction:
SELECT wt.ASSIGNEESDISPLAYNAME next_approver,--hrops_name wt.FROMUSERDISPLAYNAME current_approver, txnh.object_id assignment_id, ( SELECT DISTINCT papf.person_number FROM per_all_people_f papf, per_all_assignments_f paaf WHERE 1 = 1 AND papf.person_id = paaf.person_id AND sysdate BETWEEN paaf.effective_start_date AND paaf.effective_end_date AND paaf.assignment_id = txnh.object_id ) person_number, ( SELECT to_date(EXTRACTVALUE(xmltype.createxml(DATA_CACHE), '/TRANSACTION/BUSINESSDATA/AM/TXN/EO/PeriodOfServiceEORow/NotifiedTerminationDate/DATA'), 'rrrr-mm-dd') FROM hrc_txn_data txnd WHERE 1 = 1 AND LENGTHB(TO_CHAR(SUBSTR(DATA_CACHE, 1, 4000))) <> 0 AND txnd.transaction_id = wt.identificationkey ) NTD, ( SELECT to_date(EXTRACTVALUE(xmltype.createxml(DATA_CACHE), '/TRANSACTION/BUSINESSDATA/AM/TXN/EO/PeriodOfServiceEORow/ActualTerminationDate/DATA'), 'rrrr-mm-dd') FROM hrc_txn_data txnd WHERE 1 = 1 AND LENGTHB(TO_CHAR(SUBSTR(DATA_CACHE, 1, 4000))) <> 0 AND txnd.transaction_id = wt.identificationkey ) ATD, ( SELECT DISTINCT ppnf.display_name FROM per_person_names_f ppnf, per_all_people_f papf, per_all_assignments_f paaf WHERE 1 = 1 AND ppnf.person_id = papf.person_id AND sysdate BETWEEN ppnf.effective_start_date AND ppnf.effective_end_date AND ppnf.NAME_TYPE = 'GLOBAL' AND papf.person_id = paaf.person_id AND sysdate BETWEEN paaf.effective_start_date AND paaf.effective_end_date AND paaf.assignment_id = txnh.object_id ) DISPLAY_NAME, trunc(CREATEDDATE) trx_date FROM fa_fusion_soainfra.wftask wt, hrc_txn_header txnh WHERE 1 = 1 AND wt.identificationkey = txnh.transaction_id AND ((ASSIGNMENTCONTEXT LIKE '%Above%' AND wt.PUSHBACKSEQUENCE <> 'INITIAL_ASSIGNEES;1') OR (ASSIGNMENTCONTEXT LIKE '%Below%')) and wt.TASKDEFINITIONNAME = 'ResignationApproval' AND Txnh.OBJECT = 'PER_ALL_ASSIGNMENTS_M' AND Txnh.module_identifier IN ('Resignation','Terminations') AND wt.COMPONENTNAME IN ('ResignationApproval','TerminationsApproval') AND trunc(wt.UPDATEDDATE) = trunc(sysdate) AND wt.ENDDATE IS NULL
Apart from these BI Queries we have Subject areas as well which can pull some of the above information using OTBI anaytics:
Human Capital Management – Approval Notification Archive Real Time
Provides archived information on completed approvals of various tasks transactions that are part of Fusion HCM. The tasks transactions comprise from various categories like compensation, payroll, core hr, absence/accrual, talent management and so on.
Human Capital Management – Transaction Administration Real Time
Provides real time information on on all BPM the transactions in the system related to HCM. Transactions that are stuck, pending, in auto recovery, error, so on are available for reporting
Hope these queries help you when you have requirement related to approval transactions.