/* $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_GOAL_REVIEWS_HIST_V AS 
SELECT  
        NVL(xppe.person_id,-1) person_id,
        xgrv.comments,
        xgrv.goal_id,
        xgrv.goal_review_id,
        'REVIEW' action,
        xcda1.data_action_details changes,
        xgrv.last_update_date,
        EXTRACT ( day FROM ( systimestamp - xgrv.last_update_date ) )  days,
        EXTRACT ( hour FROM ( systimestamp - xgrv.last_update_date ) )  hours,
        EXTRACT ( minute FROM ( systimestamp - xgrv.last_update_date ) )  minutes
   FROM xxas_goal_reviews xgrv,
        xxas_com_data_actions xcda1,
        xxas_com_data xcd1,
        xxas_perf_plan_enrollments xppe
  WHERE  xcd1.data_id = xgrv.goal_review_id
    AND xppe.perf_plan_enrollment_id = xgrv.perf_plan_enrollment_id
    AND xcd1.current_state_code <> 'D'
    AND xcd1.data_type = xxas_com_data_type_pkg.get_data_type_goal_review    
    AND xcd1.internal_data_id = xcda1.internal_data_id
    AND EXISTS (SELECT 'exists' 
                  FROM xxas_goals_v xgv
                  WHERE xgv.goal_id = xgrv.goal_id
				  )
    AND xcda1.data_action_details.getRootElement() is NULL -- Only Create
    AND xcda1.data_action_id IN (
         SELECT max ( data_action_id )
           FROM xxas_com_data_actions xcda
          WHERE xcd1.internal_data_id = xcda.internal_data_id
                               )
order by last_update_date desc;

/
COMMIT;
EXIT;