Oracle HRMS Appraisal Status Query

SELECT

    B.APPRAISAL_ID

    , APPRAISAL_TYPE_M

    ,A.APPRAISEE

    ,C.EMPLOYEE_NUMBER

    ,A.MAIN_APPRAISER

    ,A.DEPARTMENT

    ,A.APPRAISAL_STATUS_M APPRAISAL_STATUS

    , DECODE (B.APPRAISAL_SYSTEM_STATUS ,'ONGOING','Appraisal Pending With - '||A.MAIN_APPRAISER

    ,'APPRFEEDBACK','Appraisal Pending With - '||a.APPRAISEE

    ,'SAVED',' Appraisal Pending With - '||a.APPRAISEE

    ,'TRANSFER','Appraisal Pending With - '||a.APPRAISEE

    ,'COMPLETED', 'Appraisal Completed'

    ,'PENDINGAPPR','Appraisal Pending With - '||A.MAIN_APPRAISER

    ,B.APPRAISAL_SYSTEM_STATUS ) ownership

FROM

    APPS.HRFV_APPRAISAL_DETAILS A

    ,PER_APPRAISALS B

    ,PER_ALL_PEOPLE_F C

WHERE C.PERSON_ID = A.APPRAISEE_PERSON_ID

    AND A.APPRAISAL_ID = B.APPRAISAL_ID

    AND A.appraisal_id in ( select max(e.appraisal_id) from per_appraisals e

                            where e.plan_id is null

                            group by e.appraisee_person_id

                            having count(*) >= 1

                          )

    AND TRUNC (SYSDATE) BETWEEN TRUNC (C.effective_start_date)

    AND TRUNC (C.effective_end_date)

    and B.APPRAISAL_SYSTEM_STATUS not in ('DELETED')

order by 

    B.APPRAISAL_ID desc