/* $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_goal_reviews_in_rev_v
AS
 SELECT xgrv.goal_review_id
  ,xgrv.perf_plan_enrollment_id
  ,xgrv.perf_review_step_id
  ,xprf.perf_review_form_id
  ,xprf.current_step_id
  ,xprf.current_step_person_id
  ,xprf.person_id
  ,xgrv.reviewer_person_id
  ,xgrv.goal_id
  ,xprf.assignment_id
  ,xgrv.status_code
  ,xxas_util_pkg.decode_lookup('XXAS_COM_DATA_STATES', xgrv.status_code) status_meaning
  ,xgrv.rating
  ,xgrv.comments_clob comments
  ,xgrv.created_by
  ,xgrv.creation_date
  ,xgrv.last_update_date
  ,xgrv.last_updated_by
  ,xgrv.last_update_login  
   FROM  xxas_goal_reviews xgrv,
            xxas_perf_review_forms_v xprf
   WHERE (xgrv.perf_review_step_id is null OR xgrv.status_code = 'A' OR xgrv.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_goal_review
            AND current_state_code = 'D'
            AND data_id = xgrv.goal_review_id );

BEGIN
  xxas_fnd_dictionary_pkg.add_view
   (p_application_short_name => xxas_util_pkg.app
   ,p_view_name              => 'XXAS_GOAL_REVIEWS_IN_REV_V'
   ,p_description            => 'Applaud Goal Reviews within a performance review');
END;
/

COMMIT;
EXIT;

