/* $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_competencies_v
AS
SELECT    pc.competence_id,
          pce.person_id,
          pce.competence_element_id,
          pce.effective_date_from start_date,
          pce.effective_date_to end_date,
          pce.proficiency_level_id,
          pce.high_proficiency_level_id,          
          pce.mandatory,
          pc.name competence_name,
          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.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					
     FROM per_competence_elements pce,
          per_competences_vl pc,
          per_rating_levels_vl r1
      WHERE pce.type = 'PERSONAL'
      AND pce.competence_id = pc.competence_id
      AND pce.proficiency_level_id = r1.rating_level_id(+);
            
BEGIN
  xxas_fnd_dictionary_pkg.add_view
   (p_application_short_name => xxas_util_pkg.app
   ,p_view_name              => 'XXAS_COMPETENCIES_V'
   ,p_description            => 'Applaud Competencies View');
END;     
/       

COMMIT;
EXIT;