/* $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_COMPETENCE_ELEMENTS');
                
    BEGIN -- Alter
       SELECT 'Y'
       INTO l_alter_flag   
       FROM dual
          WHERE EXISTS
                  (SELECT 'true'
                     FROM all_tab_columns
                    WHERE table_name LIKE 'XXAS_COMPETENCE_ELEMENTS'
                      AND column_name = 'WEIGHTING_PERCENT');
    EXCEPTION -- Alter Exception
      WHEN NO_DATA_FOUND THEN                       
         EXECUTE IMMEDIATE 'ALTER TABLE XXAS_COMPETENCE_ELEMENTS
          ADD (
           weighting_percent     number
              )';
    END; -- Alter End

  EXCEPTION -- Create
    WHEN NO_DATA_FOUND THEN 
        EXECUTE IMMEDIATE 'CREATE TABLE XXAS_COMPETENCE_ELEMENTS
                            (competence_element_id                 number              NOT NULL PRIMARY KEY
                            ,business_group_id                     NUMBER(15)
                            ,type                                  VARCHAR2(30)        NOT NULL
                            ,competence_id                         varchar2(15)        NOT NULL
                            ,proficiency_level_id                  NUMBER(15)
                            ,person_id                             NUMBER(15)          NOT NULL  
                            ,perf_plan_enrollment_id               NUMBER              NOT NULL	
                            ,effective_date_from                   DATE           
                            ,effective_date_to                     DATE   
                            ,source_of_proficiency_level           VARCHAR2(30)   
                            ,certification_date                    DATE           
                            ,certification_method                  VARCHAR2(30)   
                            ,next_certification_date               DATE           
                            ,comments                              VARCHAR2(2000)  
                            ,party_id                              NUMBER(15)     
                            ,status                                VARCHAR2(40) 
                            ,attribute_category                    VARCHAR2(30)   
                            ,attribute1                            VARCHAR2(150)  
                            ,attribute2                            VARCHAR2(150)  
                            ,attribute3                            VARCHAR2(150)  
                            ,attribute4                            VARCHAR2(150)  
                            ,attribute5                            VARCHAR2(150)  
                            ,attribute6                            VARCHAR2(150)  
                            ,attribute7                            VARCHAR2(150)  
                            ,attribute8                            VARCHAR2(150)  
                            ,attribute9                            VARCHAR2(150)  
                            ,attribute10                           VARCHAR2(150)  
                            ,attribute11                           VARCHAR2(150)  
                            ,attribute12                           VARCHAR2(150)  
                            ,attribute13                           VARCHAR2(150)  
                            ,attribute14                           VARCHAR2(150)  
                            ,attribute15                           VARCHAR2(150)  
                            ,attribute16                           VARCHAR2(150)  
                            ,attribute17                           VARCHAR2(150)  
                            ,attribute18                           VARCHAR2(150)  
                            ,attribute19                           VARCHAR2(150)  
                            ,attribute20                           VARCHAR2(150)
                            ,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
                            ,perf_review_step_id                   NUMBER
                            ,parent_competence_element_id          NUMBER
                            ,weighting_percent                     NUMBER
                            )';  
      EXECUTE IMMEDIATE 'GRANT ALL ON XXAS_COMPETENCE_ELEMENTS TO &1 WITH GRANT OPTION';   
  END;    -- Create  end
END;
/

COMMIT; 
EXIT;