Requisition Pending Approval for multiple Approves SQL Report in Oracle Fusion ORC.

 If you have any queries, please reach out to sathwikhcm@gmail.com

YouTube Link: https://www.youtube.com/watch?v=wusdFrOw3RE 

 

WITH RAW_DATA AS (
SELECT DISTINCT
IRV.REQUISITION_NUMBER,
IRV.TITLE,
WFS.ENDDATE,
WFS.STARTDATE,
WFS.ASSIGNEE
FROM
FA_FUSION_SOAINFRA.WFTASK WFT,
FA_FUSION_SOAINFRA.WFTASKASSIGNMENTSTATISTIC WFS,
IRC_REQUISITIONS_VL IRV,
IRC_PHASES_VL IPV,
IRC_STATES_VL ISV

WHERE
WFT.IDENTIFICATIONKEY = TO_CHAR(IRV.REQUISITION_ID)
AND IRV.CURRENT_PHASE_ID = IPV.PHASE_ID
AND IRV.CURRENT_STATE_ID = ISV.STATE_ID
AND WFT.TASKID = WFS.TASKID
AND IPV.NAME = 'Approval'
AND ISV.NAME = 'Pending'
AND (WFS.ENDACTION IN ('OUTCOME_UPDATE','COMPLETE')
OR WFS.ENDACTION IS NULL)
--AND IRV.REQUISITION_NUMBER = '10175'
),

ROWNUM_TABLE AS (
SELECT DISTINCT
REQUISITION_NUMBER,
TITLE,
ASSIGNEE,
STARTDATE,
ENDDATE,
(ROW_NUMBER() OVER (PARTITION BY REQUISITION_NUMBER ORDER BY STARTDATE)) ROW_NUM1
FROM
RAW_DATA
),

MAIN_TABLE AS (
SELECT DISTINCT
REQUISITION_NUMBER,
TITLE,
MIN(CASE WHEN ROW_NUM1=1 THEN ASSIGNEE END) ASSIGNEE1,
MIN(CASE WHEN ROW_NUM1=1 THEN STARTDATE END) STARTDATE1,
MIN(CASE WHEN ROW_NUM1=1 THEN ENDDATE END) ENDDATE1,
MIN(CASE WHEN ROW_NUM1=2 THEN ASSIGNEE END) ASSIGNEE2,
MIN(CASE WHEN ROW_NUM1=2 THEN STARTDATE END) STARTDATE2,
MIN(CASE WHEN ROW_NUM1=2 THEN ENDDATE END) ENDDATE2
FROM
ROWNUM_TABLE

GROUP BY REQUISITION_NUMBER,
TITLE
)

SELECT DISTINCT IRV.REQUISITION_NUMBER,
IRV.TITLE REQ_TITLE,
HAP.NAME POSITION_NAME,
HAP.POSITION_CODE,
HAOU_LE.NAME LE_NAME,
HAOU_BU.NAME BU_NAME,
HAOU_DEP.NAME DEP_NAME,
HLA.LOCATION_NAME,
PJ.NAME JOB_NAME,
PG.NAME GRADE_NAME,
MT.ASSIGNEE1 APPROVER1,
MT.STARTDATE1 ASSIGNED1DATE,
MT.ENDDATE1 APPROVED1DATE,
(CASE WHEN MT.STARTDATE1 IS NOT NULL AND MT.ENDDATE1 IS NULL THEN 'Pending'
WHEN MT.STARTDATE1 IS NOT NULL AND MT.ENDDATE1 IS NOT NULL THEN 'Completed' END) APPROVER1STATUS,
MT.ASSIGNEE2 APPROVER2,
MT.STARTDATE2 ASSIGNED2DATE,
MT.ENDDATE2 APPROVED2DATE,
(CASE WHEN MT.STARTDATE2 IS NOT NULL AND MT.ENDDATE2 IS NULL THEN 'Pending'
WHEN MT.STARTDATE2 IS NOT NULL AND MT.ENDDATE2 IS NOT NULL THEN 'Completed' END) APPROVER2STATUS

FROM
MAIN_TABLE MT,
HR_ALL_POSITIONS HAP,
HR_LOCATIONS_ALL HLA,
HR_ALL_ORGANIZATION_UNITS HAOU_DEP,
HR_ALL_ORGANIZATION_UNITS HAOU_BU,
HR_ALL_ORGANIZATION_UNITS HAOU_LE,
PER_JOBS PJ,
PER_GRADES PG,
IRC_REQUISITIONS_VL IRV

WHERE
IRV.REQUISITION_NUMBER = MT.REQUISITION_NUMBER
AND IRV.POSITION_ID = HAP.POSITION_ID
AND HAP.LOCATION_ID = HLA.LOCATION_ID(+)
AND HAP.ORGANIZATION_ID = HAOU_DEP.ORGANIZATION_ID(+)
AND HAP.BUSINESS_UNIT_ID = HAOU_BU.ORGANIZATION_ID(+)
AND IRV.LEGAL_EMPLOYER_ID = HAOU_LE.ORGANIZATION_ID(+)
AND HAP.JOB_ID = PJ.JOB_ID(+)
AND HAP.ENTRY_GRADE_ID = PG.GRADE_ID(+)