/* $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 FORCE VIEW xxas_goals_v
AS
SELECT
   xg.goal_id
  ,xg.perf_plan_enrollment_id
  ,xppe.person_id
  ,xppe.perf_plan_id
  ,xppe.assignment_id
  ,xg.goal_type_code
  ,case 
     when xxas_com_sec_pkg.test_instance('XXAS_DT_GOAL_UPD', xg.goal_id) = 'Y' THEN
	   'true'
	 else
       'false'
	end                                               "statusUpdateAllowed"
  ,xxas_util_pkg.decode_lookup('XXAS_GOAL_TYPE', xg.goal_type_code) goal_type_meaning
  ,xg.category_code
  ,xxas_util_pkg.decode_lookup('XXAS_GOAL_CATEGORY', xg.category_code) category_meaning
  ,xg.goal
  ,xg.access_code
  ,xxas_util_pkg.decode_lookup('XXAS_GOAL_ACCESS', xg.access_code) access_meaning
  ,xg.parent_goal_id
  ,xg.weighting_percent
  ,xg.start_date
  ,xg.due_date
  ,xg.status_code
  ,xg.status_code goal_status_code
  ,xxas_util_pkg.decode_lookup('XXAS_GOAL_STATUS', xg.status_code) status_meaning
  ,xg.complete_percent
  ,(complete_percent * (weighting_percent / 100)) weighted_complete_percent
  ,xg.created_by
  ,xg.creation_date
  ,xg.last_update_date
  ,xg.last_updated_by
  ,xg.last_update_login
  ,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
  ,xg.perf_review_step_id
  ,xcd.current_state_code goal_state
  ,xcd.current_state
  ,xcd.current_ins_upd_del
  ,xcd.internal_data_id
  ,xcd.data_id
  ,xcd.data_type
  ,xcd.comments
  FROM xxas_goals xg, xxas_perf_plan_enrollments xppe, xxas_com_data_v xcd
  WHERE 
   xg.perf_plan_enrollment_id = xppe.perf_plan_enrollment_id
    AND xcd.data_type = xxas_com_data_type_pkg.get_data_type_goal
    AND xcd.data_id = xg.goal_id 
    AND xcd.current_ins_upd_del <> 'D';
            
BEGIN
  xxas_fnd_dictionary_pkg.add_view
   (p_application_short_name => xxas_util_pkg.app
   ,p_view_name              => 'XXAS_GOALS_V'
   ,p_description            => 'Applaud Performance Goals View');
END;     
/       

COMMIT;
EXIT;

