/* $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 LIKE 'XXAS_PERF_REVIEW_TEMPL_STEPS' );
                
    BEGIN -- Alter
       SELECT 'Y'
       INTO l_alter_flag   
       FROM dual
          WHERE EXISTS
                  (SELECT 'true'
                     FROM all_tab_columns
                    WHERE table_name LIKE 'XXAS_PERF_REVIEW_TEMPL_STEPS'
                      AND column_name = 'GOAL_EDITING');
    EXCEPTION -- Alter
      WHEN NO_DATA_FOUND THEN                       
         EXECUTE IMMEDIATE 'ALTER TABLE XXAS_PERF_REVIEW_TEMPL_STEPS
          ADD (
           goal_editing              varchar2(30)
          ,goal_commenting           varchar2(30)
          ,competency_editing        varchar2(30)
          ,competency_commenting     varchar2(30)
          ,questionnaire_editing     varchar2(30) 
              )';
    END; -- Alter
    
  EXCEPTION -- Create
    WHEN NO_DATA_FOUND THEN 
        EXECUTE IMMEDIATE 'CREATE TABLE XXAS_PERF_REVIEW_TEMPL_STEPS
        (perf_review_templ_step_id number         NOT NULL
        ,perf_review_template_id   number         NOT NULL
        ,stage_code                varchar2(30)   NOT NULL /* R (review), S (signatures) */
        ,sequence                  number         NOT NULL
        ,title_message             varchar2(30)   NOT NULL
        ,step_duration_days        number
        ,description_message       varchar2(4000) 
        ,interaction_code          varchar2(30)   NOT NULL /* S (single participant), I (iterative), P (parallel) */
        ,start_perf_participant_id number 
        ,goal_editing              varchar2(30)
        ,goal_commenting           varchar2(30)
        ,competency_editing        varchar2(30)
        ,competency_commenting     varchar2(30)
        ,questionnaire_editing     varchar2(30)  
        ,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  'ALTER TABLE XXAS_PERF_REVIEW_TEMPL_STEPS ADD CONSTRAINT perf_part_id_not_null_chk 
            CHECK (start_perf_participant_id IS NOT NULL OR interaction_code = ''P'')';
            
        EXECUTE IMMEDIATE 'GRANT ALL ON XXAS_PERF_REVIEW_TEMPL_STEPS TO &1 WITH GRANT OPTION';   
  END;    -- Create  
  
END;

/

COMMIT; 
EXIT;
