Oracle HRMS Appraisal Status Query in oracle apps

/* Formatted on 5/15/2014 10:18:33 AM (QP5 v5.115.810.9015) */
  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