/* $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_PERF_QUESTIONNAIRES
  (questionnaire_id         number NOT NULL PRIMARY KEY
  ,perf_plan_enrollment_id  number NOT NULL
  ,perf_review_step_id      number
  ,reviewer_person_id       number
  ,status_code              varchar2(30)
  ,rating                   varchar2(30)
  ,comments                 varchar2(4000 char)
  ,comments_clob            clob
  ,questionnaire_template   varchar2(30)
  ,question1                varchar2(4000 char)
  ,question2                varchar2(4000 char)
  ,question3                varchar2(4000 char)
  ,question4                varchar2(4000 char)
  ,question5                varchar2(4000 char)
  ,question6                varchar2(4000 char)
  ,question7                varchar2(4000 char)
  ,question8                varchar2(4000 char)
  ,question9                varchar2(4000 char)
  ,question10               varchar2(4000 char)
  ,question11               varchar2(4000 char)
  ,question12               varchar2(4000 char)
  ,question13               varchar2(4000 char)
  ,question14               varchar2(4000 char)
  ,question15               varchar2(4000 char)
  ,question16               varchar2(4000 char)
  ,question17               varchar2(4000 char)
  ,question18               varchar2(4000 char)
  ,question19               varchar2(4000 char)
  ,question20               varchar2(4000 char)
  ,question21               varchar2(4000 char)
  ,question22               varchar2(4000 char)
  ,question23               varchar2(4000 char)
  ,question24               varchar2(4000 char)
  ,question25               varchar2(4000 char)
  ,question26               varchar2(4000 char)
  ,question27               varchar2(4000 char)
  ,question28               varchar2(4000 char)
  ,question29               varchar2(4000 char)
  ,question30               varchar2(4000 char)
  ,question31               varchar2(4000 char)
  ,question32               varchar2(4000 char)
  ,question33               varchar2(4000 char)
  ,question34               varchar2(4000 char)
  ,question35               varchar2(4000 char)
  ,question36               varchar2(4000 char)
  ,question37               varchar2(4000 char)
  ,question38               varchar2(4000 char)
  ,question39               varchar2(4000 char)
  ,question40               varchar2(4000 char)
  ,question41               varchar2(4000 char)
  ,question42               varchar2(4000 char)
  ,question43               varchar2(4000 char)
  ,question44               varchar2(4000 char)
  ,question45               varchar2(4000 char)
  ,question46               varchar2(4000 char)
  ,question47               varchar2(4000 char)
  ,question48               varchar2(4000 char)
  ,question49               varchar2(4000 char)
  ,question50               varchar2(4000 char)
  ,question51               varchar2(4000 char)
  ,question52               varchar2(4000 char)
  ,question53               varchar2(4000 char)
  ,question54               varchar2(4000 char)
  ,question55               varchar2(4000 char)
  ,question56               varchar2(4000 char)
  ,question57               varchar2(4000 char)
  ,question58               varchar2(4000 char)
  ,question59               varchar2(4000 char)
  ,question60               varchar2(4000 char)
  ,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_PERF_QUESTIONNAIRES TO &1 WITH GRANT OPTION';

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