/* $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_DIGEST_V AS 
 SELECT a.goal_person_id person_id,
        a.goal_id,
        a.goal,
        a.action,
        a.changes,
        a.days goal_days,
        b.days review_days,
        least ( nvl ( b.days,
                      a.days ),
                nvl ( a.days,
                      b.days ) ) days,
        b.count comments
   FROM xxas_goals_history_v a
        FULL OUTER JOIN (
         SELECT b.goal_id,
                b.days,
                b.person_id,
                count ( 1 ) count
           FROM xxas_goal_reviews_hist_v b
          WHERE b.action = 'REVIEW'
        GROUP BY b.goal_id,
                 b.days,
                 b.person_id ) b
             ON a.goal_id = b.goal_id
ORDER BY days;

COMMIT;
EXIT;