add

About Me

My photo
Oracle Apps - Techno Functional consultant

Wednesday, June 5

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

No comments: