Summary:

We are trying to create a BI report to know with whom the Job Requisition Approval is pending with. We have used the below query to pull the details but when the Module Identifier is hardcoded the Approvers and Assignee name do not appear. But when we comment /remove the hard coded part the Approver/ Assignee name appear.

Content (please ensure you mask any confidential information):

Version (include the version you are using, if applicable):

Code Snippet (add any code snippets that support your topic, if applicable):

SELECT htce.status_category
,htce.created_by
,htce.object_name
,htce.process_category
,htce.change_effective_date
,htd.status
,hth.object
,htd.last_update_date
,hth.module_identifier
,wft.Approvers
,Wft.Assignees
FROM fusion.hrc_txn_console_entry htce
,fusion.hrc_txn_header hth
,fusion.hrc_txn_data htd
,fa_fusion_soainfra.wftask wft
WHERE hth.transaction_id = htd.transaction_id
AND htd.transaction_id = htce.transaction_id
AND To_Char(hth.Transaction_Id) = Wft.Identificationkey(+)
--AND hth.module_identifier = 'IRC_REQUISITION_APPROVAL'
--AND hth.object = 'IRC_REQUISITIONS_B'
--AND htce.process_category = 'Approve Job Requisition'
AND htd.status = 'PENDING'