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