/* $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_COMPE_REVIEWS_IN_REV_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,
xprf.perf_review_form_id,
xprf.person_id,
xprf.status_code form_status_code,
xprf.current_step_id
from 
xxas_competency_reviews xcr,
xxas_perf_review_forms_v xprf
WHERE (xcr.perf_review_step_id IS NULL
		OR xcr.status_code = 'A' 
		OR xcr.perf_review_step_id IN 
			(
			SELECT perf_review_step_id 
			FROM xxas_perf_review_steps 
			WHERE perf_review_form_id = xprf.perf_review_form_id)
	   )
AND 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_COMPE_REVIEWS_IN_REV_V'
   ,p_description            => 'Applaud Competency Reviews in performance Review');
END;     
/
COMMIT;
EXIT;