In this article, we will look into a way to fetch the Pending Job Requisitions waiting to be approved.
If the approvals have been enabled for Job Requisitions in ORC, then all the Job Requisitions created by Hiring Manager/Recruiters will go for approval based upon the approval rules configured.
Sometimes, the approvers might forget to approve these, and these requisitions will be hanging without getting posted. In order to overcome this challenge, we can create a BI Report to identify all the pending requisitions which are waiting to be approved. This report will also list the user with whom the requisition is waiting so we can follow up with the specific user to get the requisition approved in order to post it to the internal/external career site.
SQL Query to fetch the pending Job Requisitions:
SELECT SOA.ASSIGNEESDISPLAYNAME Assigned_To
,SOA.TITLE Notification_Subject
,SUBSTR(SOA.TITLE, -21,3) Req_Number
,TXNH.SECTION_DISPLAY_NAME Action_Title
,TXND.STATUS
,TXND.LAST_UPDATED_BY
,TO_CHAR(TXND.LAST_UPDATE_DATE,'DD/MM/YY') Last_Updated_Date
,TXND.CREATED_BY
,TO_CHAR(TXND.CREATION_DATE,'DD/MM/YY') Creation_Date
FROM HRC_TXN_HEADER TXNH
,FA_FUSION_SOAINFRA.WFTASK SOA
,HRC_TXN_DATA TXND
Where TXNH.Object_ID = SOA.IDENTIFICATIONKEY
AND SOA.COMPONENTNAME = 'RequisitionApprovalHumanTask'
AND UPPER(TXND.STATUS) = 'PENDING'
AND TXND.TRANSACTION_ID = TXNH.TRANSACTION_ID
ORDER BY TXND.LAST_UPDATE_DATE DESCSample output will look like:
We can also modify this query to add additional fields as per the requirement.
If you like the content, please follow us on LinkedIn, Facebook, and Twitter to get updated with the latest content.
