FUSION: Query to get Pending Approval in Fusion

 

SELECT 

    LISTAGG(ASSIGNEESDISPLAYNAME, ', ') 

        WITHIN GROUP (ORDER BY ASSIGNEESDISPLAYNAME) AS approvers

FROM fa_fusion_soainfra.wftask f

WHERE f.assignees IS NOT NULL

  AND f.title LIKE '%' || :P_RFQ_NUMBER || '%'

  and f.COMPONENTNAME='NegotiationApproval'

  and f.IDENTIFICATIONKEY like '%'||:P_AUCTION_HDR_ID||'%'

  AND version = (

        SELECT MAX(version) 

        FROM fa_fusion_soainfra.wftask ffsw 

        WHERE NVL(ffsw.OUTCOME, '@') != NVL('REJECT', '@') 

          AND ffsw.title LIKE '%' || :P_RFQ_NUMBER || '%'

          AND ffsw.IDENTIFICATIONKEY = f.IDENTIFICATIONKEY

  );