SQL query to get current approver and future or next approver for resignation transactions

 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