• Post category:BI Publisher
  • Post comments:0 Comments
  • Post last modified:June 26, 2020
  • Reading time:10 mins read
You are currently viewing SQL Query to find pending approval transactions from Workflow tables
SQL Query to find pending approval transactions from Workflow tables

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.