/* $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_competence_elements_v
AS
SELECT    pc.competence_id,
          xce.person_id,
          xce.competence_element_id,
          xce.effective_date_from compe_start_date,
          xce.effective_date_to compe_end_date,
					xce.type,
					xce.party_id,
					xce.business_group_id,
					xce.status,					
          xce.proficiency_level_id,        
          pc.name competence_name,
          pc.behavioural_indicator,
          pc.description competence_desc,
          r1.rating_scale_id,
          r1.step_value,
          r1.name step_name,
          decode(xce.proficiency_level_id, null, null, r1.step_value || ' - ' || r1.name ) proficiency_name,
          r1.step_value||'.'||r1.name proficiency,
          xce.source_of_proficiency_level acquired_by,
          xce.certification_date,
          xce.certification_method measured_by,
          xce.next_certification_date next_review_date,
          xce.comments justification,
          xce.created_by,
          xce.creation_date,
          xce.last_update_date,
          xce.last_updated_by,
          xce.last_update_login,
          xce.attribute_category,
          xce.attribute1,
          xce.attribute2,
          xce.attribute3,
          xce.attribute4,
          xce.attribute5,
          xce.attribute6,
          xce.attribute7,
          xce.attribute8,
          xce.attribute9,
          xce.attribute10,
          xce.attribute11,
          xce.attribute12,
          xce.attribute13,
          xce.attribute14,
          xce.attribute15,
          xce.attribute16,
          xce.attribute17,
          xce.attribute18,
          xce.attribute19,
          xce.attribute20,
          xce.perf_plan_enrollment_id, 
					xce.perf_review_step_id,
					xce.parent_competence_element_id,					
					xcd.current_state_code goal_state,
					xcd.current_state,
					xcd.current_ins_upd_del,
					xcd.internal_data_id,
					xcd.data_id,
					xcd.data_type,
					xcd.comments, 
          xce.weighting_percent				
     FROM xxas_competence_elements xce,
          per_competences_vl pc,
          per_rating_levels_vl r1,
					xxas_com_data_v xcd
      WHERE xce.type = 'PERSONAL'
      AND xce.competence_id = pc.competence_id
      AND xce.proficiency_level_id = r1.rating_level_id(+)
      AND xcd.data_type = xxas_com_data_type_pkg.get_data_type_competency
      AND xcd.data_id = xce.competence_element_id 
      AND xcd.current_ins_upd_del <> 'D';		      
            
BEGIN
  xxas_fnd_dictionary_pkg.add_view
   (p_application_short_name => xxas_util_pkg.app
   ,p_view_name              => 'XXAS_COMPETENCE_ELEMENTS_V'
   ,p_description            => 'Applaud Competence Elements View');
END;     
/       

COMMIT;
EXIT;