/* $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_goals_review_base_v
AS
 SELECT xg.goal_id "id",
        'goal' "type",
        --xg.goal_type_meaning||decode(xg.category_meaning,null,null,' - ')||xg.category_meaning "label",
        --xg.goal "title",
        CASE WHEN xprf.status_code NOT IN ('A','O') THEN xg.goal
             ELSE xxas_perf_util_pkg.get_last_approved_goal(p_goal_id     => xg.goal_id)
        END "title",
        CASE WHEN xprf.status_code NOT IN ('A','O') THEN xg.goal_type_meaning 
                                           || DECODE(xg.category_meaning,null,null,' - ')
                                           || xg.category_meaning
             ELSE xxas_util_pkg.decode_lookup ('XXAS_GOAL_TYPE'
                                             ,xxas_perf_util_pkg.get_last_approved_goal_info
                                               (p_goal_id => xg.goal_id
                                               ,p_column_name => 'GOAL_TYPE_CODE')) 
                || DECODE(xxas_perf_util_pkg.get_last_approved_goal_info(p_goal_id => xg.goal_id
                                                               ,p_column_name => 'CATEGORY_CODE') ,null,null,' - ')
                ||xxas_util_pkg.decode_lookup ('XXAS_GOAL_CATEGORY'
                                               ,xxas_perf_util_pkg.get_last_approved_goal_info
                                                 (p_goal_id => xg.goal_id
                                                 ,p_column_name => 'CATEGORY_CODE'))
        END "label",
        CASE 
         WHEN xxas_com_sec_pkg.test_instance('XXAS_DT_GOAL_UPD_IN_REV',
                                              xg.goal_id, 
                                              xprf.perf_review_form_id) = 'Y' THEN
	           'true'
	      ELSE
             'false'
	      END  "statusUpdateAllowed",
        CASE WHEN xxas_com_sec_pkg.test_instance('XXAS_DT_GOAL_W_UPD_IN_REV',
                                                  xg.goal_id, 
                                                  xprf.perf_review_form_id) = 'Y' THEN
	                 'true'
	           ELSE
                   'false'
	           END  "weightingUpdateAllowed",
        CASE WHEN xxas_com_sec_pkg.test_instance('XXAS_DT_GOAL_C_UPD_IN_REV',
                                                  xg.goal_id, 
                                                  xprf.perf_review_form_id) = 'Y' THEN
	                 'true'
	           ELSE
                   'false'
	           END  "completionUpdateAllowed",
        xg.goal,
        xg.goal_id,
        xg.perf_plan_enrollment_id,
        xg.assignment_id,
        xg.goal_type_code,
        xg.goal_type_meaning,
        xg.creation_date,
        xg.last_update_date,
        xg.status_code goal_status_code,
        xg.status_meaning goal_status_meaning,
        xg.goal_state goal_state,
        xg.perf_review_step_id,
        xg.category_code,
        xg.category_meaning,
        xg.parent_goal_id,
        xprf.status_code form_status_code,
        xprf.status_meaning form_status_meaning,
        xg.created_by goal_creator,
        xprf.perf_review_form_id,
        xprf.person_id,
        xprf.current_step_id,
        xprf.current_step_id "reviewStepId",
        xprf.current_step_person_id
        ,xg.attribute_category
        ,xg.attribute1
        ,xg.attribute2
        ,xg.attribute3
        ,xg.attribute4
        ,xg.attribute5
        ,xg.attribute6
        ,xg.attribute7
        ,xg.attribute8
        ,xg.attribute9
        ,xg.attribute10
        ,xg.attribute11
        ,xg.attribute12
        ,xg.attribute13
        ,xg.attribute14
        ,xg.attribute15
        ,xg.attribute16
        ,xg.attribute17
        ,xg.attribute18
        ,xg.attribute19
        ,xg.attribute20
        ,xg.attribute21
        ,xg.attribute22
        ,xg.attribute23
        ,xg.attribute24
        ,xg.attribute25
        ,xg.attribute26
        ,xg.attribute27
        ,xg.attribute28
        ,xg.attribute29
        ,xg.attribute30
        ,XMLForest
               (xg.status_code AS "id",
                xg.status_meaning AS "label")"status"
   FROM xxas_perf_review_forms_v xprf,
        xxas_goals_v xg
  WHERE xprf.perf_plan_enrollment_id = xg.perf_plan_enrollment_id
  AND (  xprf.status_code NOT IN ('A','O') OR xxas_perf_util_pkg.was_goal_visible(xg.goal_id) = 'Y'  );

BEGIN
  xxas_fnd_dictionary_pkg.add_view
   (p_application_short_name => xxas_util_pkg.app
   ,p_view_name              => 'XXAS_GOALS_REVIEW_BASE_V'
   ,p_description            => 'Applaud Performance Goals List/Detail base-view to be used in REST APIs');
END;
/

COMMIT;
EXIT;

