/* $Id$ */
/*============================================================================*
 |                 Copyright (c) 2008 Applaud Solutions UK Ltd                |
 |                             All rights reserved                            |
 *===========================================================================*/

SET VERIFY OFF
SET FEEDBACK OFF
WHENEVER OSERROR  EXIT FAILURE ROLLBACK
WHENEVER SQLERROR EXIT FAILURE ROLLBACK
DECLARE

BEGIN

EXECUTE IMMEDIATE 'CREATE TABLE xxas_goal_reviews
  (goal_review_id          number              NOT NULL PRIMARY KEY
  ,perf_plan_enrollment_id number              NOT NULL
  ,perf_review_step_id     number
  ,reviewer_person_id      number              NOT NULL
  ,goal_id                 number              NOT NULL
  ,status_code             varchar2(30)        NOT NULL
  ,rating                  number
  ,comments                varchar2(4000) 
  ,comments_clob           CLOB  
  ,created_by              number              NOT NULL
  ,creation_date           date                NOT NULL
  ,last_update_date        date                NOT NULL
  ,last_updated_by         number              NOT NULL
  ,last_update_login       number              NOT NULL
  )';
  
EXECUTE IMMEDIATE 'GRANT ALL ON xxas_goal_reviews TO &1 WITH GRANT OPTION';

EXCEPTION
WHEN OTHERS THEN
IF SQLCODE = -955 THEN
 EXECUTE IMMEDIATE 'ALTER TABLE xxas_goal_reviews
          ADD ( comments_clob CLOB )';
 EXECUTE IMMEDIATE 'UPDATE xxas_goal_reviews SET comments_clob = comments';
END IF;      
END;

/

COMMIT; 
EXIT;