Oracle Fusion: BPM - SQL Query to Get Approval Group Details

 SELECT 

FWG.APPROVALGROUPID ,

PPNF.DISPLAY_NAME EMP_NAME,

PAPF.PERSON_NUMBER EMP_NUM,

FWGM.MEMBER USER_NAME, 

FWGM.TYPE, 

FWGM.SEQUENCE,

FWG.APPROVALGROUPNAME

FROM 

FA_FUSION_SOAINFRA.WFAPPROVALGROUPS FWG,

FA_FUSION_SOAINFRA.WFAPPROVALGROUPMEMBERS FWGM,

PER_USERS PU,

PER_PERSON_NAMES_F PPNF,

PER_ALL_PEOPLE_F PAPF

WHERE 1=1

AND FWG.APPROVALGROUPID = FWGM.APPROVALGROUPID

AND FWGM.MEMBER=PU.USERNAME

AND PU.PERSON_ID=PPNF.PERSON_ID

AND PPNF.NAME_TYPE='GLOBAL'

AND PAPF.PERSON_ID=PPNF.PERSON_ID

AND PAPF.PERSON_NUMBER = NVL(:P_EMP_NUM,PAPF.PERSON_NUMBER)

AND FWGM.MEMBER=NVL(:P_USERNAME,FWGM.MEMBER)

GROUP BY

PPNF.DISPLAY_NAME ,

PAPF.PERSON_NUMBER ,

FWGM.MEMBER , 

FWGM.TYPE, 

FWGM.SEQUENCE,

FWG.APPROVALGROUPNAME

ORDER BY PPNF.DISPLAY_NAME ,FWG.APPROVALGROUPNAME