/* $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 VIEW xxas_competency_reviews_v
AS
select 
xcr.competency_review_id,
xcr.perf_review_step_id,
xcr.reviewer_person_id,
xcr.competency_element_id,
xcr.status_code,
xcr.rating,
xcr.comments_clob comments,
xcr.created_by,
xcr.creation_date,
xcr.last_update_date,
xcr.last_updated_by,
xcr.last_update_login,
xcr.perf_plan_enrollment_id,
xcr.source
from 
xxas_competency_reviews xcr
where 
NOT EXISTS
    (
     SELECT 1
       FROM xxas_com_data
      WHERE data_type = xxas_com_data_type_pkg.get_data_type_competency_rvw
        AND current_state_code = 'D'
        AND data_id = competency_review_id );
BEGIN
  xxas_fnd_dictionary_pkg.add_view
   (p_application_short_name => xxas_util_pkg.app
   ,p_view_name              => 'XXAS_COMPETENCY_REVIEWS_V'
   ,p_description            => 'Applaud Competency Reviews');
END;
/

COMMIT;
EXIT;

