/* $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_v
AS
  SELECT goal_review_id
  ,perf_plan_enrollment_id
  ,perf_review_step_id
  ,reviewer_person_id
  ,goal_id
  ,status_code
  ,xxas_util_pkg.decode_lookup('XXAS_COM_DATA_STATES', status_code) status_meaning
  ,rating
  ,flv.meaning rating_meaning
  ,flv.description rating_percent
  ,comments_clob comments
  ,xgv.created_by
  ,xgv.creation_date
  ,xgv.last_update_date
  ,xgv.last_updated_by
  ,xgv.last_update_login  
   FROM  xxas_goal_reviews xgv
        ,fnd_lookup_values flv
  WHERE  flv.lookup_type (+) = 'XXAS_GOAL_REVIEW_RATING'
  AND    flv.lookup_code (+) = rating
  AND    flv.language (+)= userenv('LANG')
  AND    flv.enabled_flag (+)= 'Y'
  AND    trunc(sysdate) BETWEEN
         nvl(flv.start_date_active, trunc(sysdate)) AND
         nvl(flv.end_date_active, trunc(SYSDATE)) AND
         flv.security_group_id (+)= 0
   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 = goal_review_id );
BEGIN
  xxas_fnd_dictionary_pkg.add_view
   (p_application_short_name => xxas_util_pkg.app
   ,p_view_name              => 'XXAS_GOAL_REVIEWS_V'
   ,p_description            => 'Applaud Goal Reviews');
END;
/

COMMIT;
EXIT;

