/* $Id$ */
/*============================================================================*
 |                 Copyright (c) 2013 Applaud Solutions UK Ltd                |
 |                             All rights reserved                            |
 *===========================================================================*/

SET VERIFY OFF
SET FEEDBACK OFF
WHENEVER OSERROR  EXIT FAILURE ROLLBACK
WHENEVER SQLERROR EXIT FAILURE ROLLBACK

CREATE OR REPLACE VIEW
xxas_performance_history_v 
AS
SELECT *
FROM (SELECT hr_util_misc_ss.getObjectName
              ('JOB', paaf.job_id, papf.business_group_id, job.name) job
             ,dept.name department
             ,ppr.performance_rating performance_rating
             ,hr_general.decode_lookup('PERFORMANCE_RATING',ppr.performance_rating) performance_rating_meaning
             ,decode(ppr.performance_rating, null
                    ,null 
                   ,ppr.performance_rating || ' - ' || hr_general.decode_lookup('PERFORMANCE_RATING',ppr.performance_rating)) last_rating
             ,review_date review_date
             ,next_perf_review_date next_review_date
             ,hr_general.decode_lookup
               (decode(pe.emp_or_apl, 'A', 'APL_', 'EMP_')
             || decode(pe.event_or_interview,'I', 'INTERVIEW_TYPE', 'EVENT_TYPE'), pe.type) related_event
             ,appr.appraisal_period_start_date period_start_date
             ,appr.appraisal_period_end_date period_end_date
             ,pe.date_start event_start_date
             ,pe.time_start event_start_time
             ,pe.date_end event_end_date
             ,pe.time_end event_end_time
             ,ma.person_id appraiser_person_id
             ,xxas_util_pkg.get_formatted_name
               (ma.full_name, ma.global_name, ma.local_name
               ,ma.first_name, ma.last_name, ma.known_as) main_appraiser
             ,ma.full_name main_appraiser_full_name
             ,appr.comments overall_comments
             ,papf.person_id
             ,paaf.assignment_id
             ,paaf.period_of_service_id
             ,pop.period_of_placement_id
             ,paaf.assignment_number
             ,ppr.attribute_category
             ,ppr.attribute1
             ,ppr.attribute2
             ,ppr.attribute3
             ,ppr.attribute4
             ,ppr.attribute5
             ,ppr.attribute6
             ,ppr.attribute7
             ,ppr.attribute8
             ,ppr.attribute9
             ,ppr.attribute10
             ,ppr.attribute11
             ,ppr.attribute12
             ,ppr.attribute13
             ,ppr.attribute14
             ,ppr.attribute15
             ,ppr.attribute16
             ,ppr.attribute17
             ,ppr.attribute18
             ,ppr.attribute19
             ,ppr.attribute20
             ,appr.attribute_category appr_attribute_category
             ,appr.attribute1 appr_attribute1
             ,appr.attribute2 appr_attribute2
             ,appr.attribute3 appr_attribute3
             ,appr.attribute4 appr_attribute4
             ,appr.attribute5 appr_attribute5
             ,appr.attribute6 appr_attribute6
             ,appr.attribute7 appr_attribute7
             ,appr.attribute8 appr_attribute8
             ,appr.attribute9 appr_attribute9
             ,appr.attribute10 appr_attribute10
             ,appr.attribute11 appr_attribute11
             ,appr.attribute12 appr_attribute12
             ,appr.attribute13 appr_attribute13
             ,appr.attribute14 appr_attribute14
             ,appr.attribute15 appr_attribute15
             ,appr.attribute16 appr_attribute16
             ,appr.attribute17 appr_attribute17
             ,appr.attribute18 appr_attribute18
             ,appr.attribute19 appr_attribute19
             ,appr.attribute20 appr_attribute20
             ,paaf.primary_flag
             ,l.location_code location
             ,paaf.assignment_type
             ,l.description location_description
             ,l.derived_locale
      FROM  per_all_people_f papf
           ,per_all_assignments_f paaf
           ,per_performance_reviews ppr
           ,per_events pe
           ,per_appraisals appr
           ,per_all_people_f ma
           ,per_jobs_tl job
           ,hr_all_organization_units_tl dept
           ,per_periods_of_placement pop
           ,hr_locations_all_vl l
      WHERE ppr.person_id = papf.person_id
      AND   papf.person_id = paaf.person_id 
      AND   ppr.review_date BETWEEN
            papf.effective_start_date AND papf.effective_end_date
      AND   ppr.review_date BETWEEN 
            paaf.effective_start_date AND paaf.effective_end_date
      AND   paaf.assignment_type in ('E','C')
      AND   paaf.primary_flag = 'Y' 
      AND   paaf.period_of_placement_date_start = pop.date_start(+)
      AND   paaf.person_id = pop.person_id(+)
      AND   paaf.job_id = job.job_id(+)
      AND   job.language(+) = userenv('LANG')
      AND   ppr.event_id = pe.event_id(+)
      AND   pe.location_id = l.location_id(+)
      AND   pe.event_id = appr.event_id(+)
      AND   appr.main_appraiser_id = ma.person_id(+)
      AND   sysdate BETWEEN
            ma.effective_start_date(+) AND ma.effective_end_date(+)
      AND   paaf.organization_id = dept.organization_id
      AND   dept.language = userenv('LANG')) qrslt
WHERE    review_date <= SYSDATE
ORDER BY review_date DESC;

BEGIN
  xxas_fnd_dictionary_pkg.add_view
   (p_application_short_name => xxas_util_pkg.app
   ,p_view_name              => 'XXAS_PERFORMANCE_HISTORY_V'
   ,p_description            => 'Applaud Performance History');
END;
/

COMMIT;
EXIT;
