/* $Id$ */
/*============================================================================*
 |                 Copyright (c) 2016 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 FORCE VIEW xxas_competency_details_v
AS
SELECT    pc.competence_id,
          pce.person_id,
          pce.competence_element_id,
          pce.effective_date_from compe_start_date,
          pce.effective_date_to compe_end_date,
          pce.type,
          pce.party_id,
          pce.business_group_id,
          pce.status,
          pce.proficiency_level_id, 
          pce.high_proficiency_level_id,          
          pce.mandatory,
          pc.name competence_name,
          pc.behavioural_indicator,
          pc.description competence_desc,
          r1.rating_scale_id,
          r1.step_value,
          r1.name step_name,
          decode(pce.proficiency_level_id, null, null, r1.step_value || ' - ' || r1.name ) proficiency_name,
          r1.step_value||'.'||r1.name proficiency,
          pce.source_of_proficiency_level acquired_by,
          pce.certification_date,
          pce.certification_method measured_by,
          pce.next_certification_date next_review_date,
          pce.comments justification,
          pce.created_by,
          pce.creation_date,
          pce.last_update_date,
          pce.last_updated_by,
          pce.last_update_login,
          pce.attribute_category,
          pce.attribute1,
          pce.attribute2,
          pce.attribute3,
          pce.attribute4,
          pce.attribute5,
          pce.attribute6,
          pce.attribute7,
          pce.attribute8,
          pce.attribute9,
          pce.attribute10,
          pce.attribute11,
          pce.attribute12,
          pce.attribute13,
          pce.attribute14,
          pce.attribute15,
          pce.attribute16,
          pce.attribute17,
          pce.attribute18,
          pce.attribute19,
          pce.attribute20,
          xppev.perf_plan_enrollment_id,
          pce.weighting_level_id       
     FROM per_competence_elements pce,
          per_competences_vl pc,
          per_rating_levels_vl r1,
          xxas_perf_plan_enrollments_v xppev 
      WHERE pce.type = 'PERSONAL'
      AND --(pce.effective_date_from BETWEEN trunc(xppev.start_date) AND trunc(xppev.end_date) 
          --OR pce.effective_date_to BETWEEN trunc(xppev.start_date) AND trunc(xppev.end_date)
          --OR trunc(xppev.end_date) BETWEEN pce.effective_date_from AND nvl(pce.effective_date_to,'31-DEC-4712'))
          --nvl(pce.effective_date_to,'31-DEC-4712') >= trunc(xppev.start_date)
		  nvl(pce.effective_date_to,to_date('31-12-4712','DD-MM-YYYY')) >= trunc(xppev.start_date)
      AND pce.competence_id = pc.competence_id
      AND pce.proficiency_level_id = r1.rating_level_id(+)
      AND xppev.person_id = pce.person_id;
            
BEGIN
  xxas_fnd_dictionary_pkg.add_view
   (p_application_short_name => xxas_util_pkg.app
   ,p_view_name              => 'XXAS_COMPETENCY_DETAILS_V'
   ,p_description            => 'Applaud Competency Details View');
END;     
/       

COMMIT;
EXIT;