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

BEGIN --
  BEGIN -- Create
    SELECT 'Y'
     INTO l_create_flag
     FROM dual
     WHERE EXISTS
              (SELECT 'true'
                 FROM all_tab_columns
                WHERE table_name = 'XXAS_COMPETENCY_REVIEWS' );
                
    BEGIN -- Alter -- Add 'source'
       SELECT 'Y'
       INTO l_alter_flag   
       FROM dual
          WHERE EXISTS
                  (SELECT 'true'
                     FROM all_tab_columns
                    WHERE table_name = 'XXAS_COMPETENCY_REVIEWS'
                      AND column_name = 'SOURCE');
    EXCEPTION -- Alter
      WHEN NO_DATA_FOUND THEN                       
         EXECUTE IMMEDIATE 'ALTER TABLE XXAS_COMPETENCY_REVIEWS
          ADD (
           source     varchar2(30)
              )';
    END; -- Alter -- Add 'source'
    
    BEGIN -- Alter -- Add 'comments_clob'
       SELECT 'Y'
       INTO l_alter_flag   
       FROM dual
          WHERE EXISTS
                  (SELECT 'true'
                     FROM all_tab_columns
                    WHERE table_name = 'XXAS_COMPETENCY_REVIEWS'
                      AND column_name = 'COMMENTS_CLOB');
    EXCEPTION -- Alter
      WHEN NO_DATA_FOUND THEN                       
         EXECUTE IMMEDIATE 'ALTER TABLE xxas_competency_reviews
          ADD (comments_clob   clob )';
         EXECUTE IMMEDIATE 'UPDATE xxas_competency_reviews SET comments_clob = comments';
    END; -- Alter -- Add 'comments_clob'
    
  EXCEPTION -- Create
    WHEN NO_DATA_FOUND THEN 
        EXECUTE IMMEDIATE 'CREATE TABLE XXAS_COMPETENCY_REVIEWS
                          (competency_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
													,competency_element_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
													,source                  varchar2(30)     
													)';
        
            
        EXECUTE IMMEDIATE 'GRANT ALL ON XXAS_COMPETENCY_REVIEWS TO &1 WITH GRANT OPTION';   
  END;    -- Create  
  
END;

/

COMMIT; 
EXIT;
