/* $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

l_create_flag varchar2(1);
l_alter_flag  varchar2(1);
l_data_type   VARCHAR2(255) := NULL;
  
BEGIN --
  BEGIN -- Create
    SELECT 'Y'
     INTO l_create_flag
     FROM dual
     WHERE EXISTS
              (SELECT 'true'
                 FROM all_tab_columns
                WHERE table_name LIKE 'XXAS_GOALS' );
                
    BEGIN -- Alter
       SELECT 'Y'
       INTO l_alter_flag   
       FROM dual
          WHERE EXISTS
                  (SELECT 'true'
                     FROM all_tab_columns
                    WHERE table_name LIKE 'XXAS_GOALS'
                      AND column_name = 'PERF_REVIEW_STEP_ID');
    EXCEPTION -- Alter
      WHEN NO_DATA_FOUND THEN                       
         EXECUTE IMMEDIATE 'ALTER TABLE XXAS_GOALS
          ADD (
           perf_review_step_id     number
              )';
    END; -- Alter
    
    BEGIN -- Alter
	  SELECT data_type
	  INTO l_data_type
	  FROM all_tab_columns
	  WHERE table_name = 'XXAS_GOALS'
	  AND column_name  = 'GOAL';
  
      IF l_data_type = 'VARCHAR2' THEN
  
        EXECUTE IMMEDIATE 'ALTER TABLE xxas_goals ADD goal_clob CLOB';
        EXECUTE IMMEDIATE 'UPDATE xxas_goals SET goal_clob = goal';
        EXECUTE IMMEDIATE 'ALTER TABLE xxas_goals DROP COLUMN goal';
        EXECUTE IMMEDIATE 'ALTER TABLE xxas_goals RENAME COLUMN goal_clob TO goal';
  
      END IF;
  
    END; -- Alter
	
  EXCEPTION -- Create
    WHEN NO_DATA_FOUND THEN 
        EXECUTE IMMEDIATE 'CREATE TABLE XXAS_GOALS
                          (goal_id                 number              NOT NULL PRIMARY KEY
                          ,perf_plan_enrollment_id number              NOT NULL
                          ,goal_type_code          varchar2(30)
                          ,category_code           varchar2(30)
                          ,goal                    CLOB NOT NULL
                          ,access_code             varchar2(30)        NOT NULL
                          ,parent_goal_id          number
                          ,weighting_percent       number
                          ,start_date              date
                          ,due_date                date
                          ,status_code             varchar2(30)
                          ,complete_percent        number
                          ,perf_review_step_id     number
                          ,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
                          ,attribute_category      varchar2(30)
                          ,attribute1              varchar2(4000)
                          ,attribute2              varchar2(4000)
                          ,attribute3              varchar2(4000)
                          ,attribute4              varchar2(4000)
                          ,attribute5              varchar2(4000)
                          ,attribute6              varchar2(4000)
                          ,attribute7              varchar2(4000)
                          ,attribute8              varchar2(4000)
                          ,attribute9              varchar2(4000)
                          ,attribute10             varchar2(4000)
                          ,attribute11             varchar2(4000)
                          ,attribute12             varchar2(4000)
                          ,attribute13             varchar2(4000)
                          ,attribute14             varchar2(4000)
                          ,attribute15             varchar2(4000)
                          ,attribute16             varchar2(4000)
                          ,attribute17             varchar2(4000)
                          ,attribute18             varchar2(4000)
                          ,attribute19             varchar2(4000)
                          ,attribute20             varchar2(4000)
                          ,attribute21             varchar2(4000)
                          ,attribute22             varchar2(4000)
                          ,attribute23             varchar2(4000)
                          ,attribute24             varchar2(4000)
                          ,attribute25             varchar2(4000)
                          ,attribute26             varchar2(4000)
                          ,attribute27             varchar2(4000)
                          ,attribute28             varchar2(4000)
                          ,attribute29             varchar2(4000)
                          ,attribute30             varchar2(4000))';
        
            
        EXECUTE IMMEDIATE 'GRANT ALL ON XXAS_GOALS TO &1 WITH GRANT OPTION';   
  END;    -- Create  
  
END;

/

COMMIT; 
EXIT;
