/* $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_HISTORY_V AS 
 SELECT -1 person_id,
        xppe.person_id goal_person_id,
        xg.goal,
        xg.goal_id,
        decode(xcda1.ins_upd_del,'D','DELETE',
               decode ( xcda1.data_action_details.getRootElement(),
                     NULL,
                    'CREATE',
                    'UPDATE' )
              )action,
        xcda1.data_action_details changes,
        xg.last_update_date,
        EXTRACT ( day FROM ( systimestamp - xg.last_update_date ) )  days,
        EXTRACT ( hour FROM ( systimestamp - xg.last_update_date ) )  hours,
        EXTRACT ( minute FROM ( systimestamp - xg.last_update_date ) )  minutes
   FROM xxas_goals xg,
        xxas_com_data_actions xcda1,
        xxas_com_data xcd1,
        xxas_perf_plan_enrollments xppe
  WHERE xppe.perf_plan_enrollment_id = xg.perf_plan_enrollment_id
    AND xcd1.data_id = xg.goal_id
    AND xcd1.internal_data_id = xcda1.internal_data_id
    AND xcd1.data_type = xxas_com_data_type_pkg.get_data_type_goal
    AND xcda1.data_action_id IN (
         SELECT max ( data_action_id )
           FROM xxas_com_data_actions xcda
           WHERE xcda.state_code <> 'A'
           AND xcda.internal_data_id = xcd1.internal_data_id
            )
order by last_update_date desc;			
/
COMMIT;
EXIT;