Oracle Performance Management Module Important Query

When you setup the appraisal templates in Core HR, that will be stored in "PER_APPRAISAL_TEMPLATES_V" table.

There are 3 temporary tables that hold data for entries made in SSHR until the approval cycle is complete

1) HR_API_TRANSACTIONS
2) HR_API_TRANSACTIONS_STEPS
3) HR_API_TRANSACTIONS_VALUES
Once the request is approved, data will be updated in "PER_PAY_PROPOSALS and PER_PERFORMANCE_REVIEWS_V" tables.

Below tables are interlinked:
PER_ALL_ASSIGNMENTS_F, PER_PAY_PROPOSALS and PER_PERFORMANCE_REVIEWS_V TABLES.

PER_ALL_ASSIGNMENTS_F
PER_ALL_PEOPLE_F
PER_JOBS
PER_GRADES
PER_APPRAISALS
PER_APPRAISAL_TEMPLATES
PER_ASSESSMENTS
PER_ASSESSMENT_GROUPS
PER_ASSESSMENT_TYPES
PER_COMPETENCES
PER_COMPETENCES_TL
PER_COMPETENCE_DEFINITIONS
PER_COMPETENCE_ELEMENTS
PER_PARTICIPANTS
PER_PERFORMANCE_RATINGS
PER_PERFORMANCE_REVIEWS
PER_RATING_LEVELS
PER_RATING_LEVELS_TL
PER_RATING_SCALES
PER_RATING_SCALES_TL
HR_QUESTIONNAIRES
HR_QUEST_ANSWERS
HR_QUEST_ANSWER_VALUES
HR_QUEST_FIELD
HR_ORGANIZATION_UNITS
PER_POSITIONS
---------------------------------------------------------------------------------------------------------------------------------------------
SELECT 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) 
order by A.APPRAISEE



SELECT bgrt.NAME business_group_name, orgt.NAME department,
apse.full_name appraisee, appr.full_name appraiser,
mapr.full_name main_appraiser, apr.appraisal_date appraisal_date,
apr.appraisal_period_start_date appraisal_start_date,
apr.appraisal_period_end_date appraisal_end_date,
apr.next_appraisal_date next_appraisal_date,
apr.comments appraisal_comments, rtt.NAME rating_level_name,
rtl.step_value step_value,
hr_bis.bis_decode_lookup ('APPRAISAL_TYPE',
apr.TYPE) appraisal_type,
hr_bis.bis_decode_lookup
('APPRAISAL_SYSTEM_STATUS',
apr.appraisal_system_status
) appraisal_status,
hr_bis.bis_decode_lookup ('APPRAISEE_ACCESS',
apr.appraisee_access
) appraisee_access,
apr.TYPE appraisal_type_code,
apr.appraisal_system_status appraisal_status_code,
apr.appraisee_access appraisee_access_code,
apr.creation_date creation_date,
apr.last_update_date last_update_date, '_DF:PER:PER_APPRAISALS:APR',
apr.appraisal_id appraisal_id,
apr.business_group_id business_group_id,
apr.appraisal_template_id appraisal_template_id,
apr.appraisee_person_id appraisee_person_id,
apr.appraiser_person_id appraiser_person_id,
apr.group_initiator_id group_initiator_id,
apr.overall_performance_level_id overall_performance_level_id,
apr.main_appraiser_id main_appraiser_id,
apr.assignment_id assignment_id, apr.event_id event_id,
rtl.rating_scale_id rating_scale_id,
rtl.competence_id competence_id,
orgt.organization_id organization_id
FROM per_appraisals apr,
hr_all_organization_units_tl bgrt,
hr_all_organization_units_tl orgt,
per_people_x apse,
per_people_x appr,
per_people_x mapr,
per_rating_levels rtl,
per_rating_levels_tl rtt
WHERE apr.business_group_id = bgrt.organization_id
AND bgrt.LANGUAGE = USERENV ('LANG')
AND apr.assignment_organization_id = orgt.organization_id
AND orgt.LANGUAGE = USERENV ('LANG')
AND apr.appraisee_person_id = apse.person_id
AND apr.appraiser_person_id = appr.person_id
AND apr.main_appraiser_id = mapr.person_id(+)
AND apr.overall_performance_level_id = rtl.rating_level_id(+)
AND rtl.rating_level_id = rtt.rating_level_id(+)
AND rtt.LANGUAGE(+) = USERENV ('LANG')
AND apr.business_group_id =
NVL (hr_bis.get_sec_profile_bg_id, apr.business_group_id)
WITH READ ONLY;