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

    BEGIN -- Alter
       SELECT 'Y'
       INTO l_alter_flag   
       FROM dual
          WHERE EXISTS
                  (SELECT 'true'
                     FROM all_tab_columns
                    WHERE table_name LIKE 'XXAS_TCM_PENDING_CLOCKS'
                      AND column_name = 'STATUS_CODE');
    EXCEPTION -- Alter
      WHEN NO_DATA_FOUND THEN                       
         EXECUTE IMMEDIATE 'ALTER TABLE XXAS_TCM_PENDING_CLOCKS
          ADD (
            status_code varchar2(30) DEFAULT ''OPEN'' NOT NULL  
              )';
    END; -- Alter

    BEGIN -- Alter
       SELECT 'Y'
       INTO l_alter_flag   
       FROM dual
          WHERE EXISTS
                  (SELECT 'true'
                     FROM all_tab_columns
                    WHERE table_name LIKE 'XXAS_TCM_PENDING_CLOCKS'
                      AND column_name = 'MISSED_IN_FLAG');
    EXCEPTION -- Alter
      WHEN NO_DATA_FOUND THEN                       
         EXECUTE IMMEDIATE 'ALTER TABLE XXAS_TCM_PENDING_CLOCKS
          ADD (
            missed_in_flag varchar2(1) DEFAULT NULL
              )';
    END; -- Alter

    BEGIN -- Alter
       SELECT 'Y'
       INTO l_alter_flag   
       FROM dual
          WHERE EXISTS
                  (SELECT 'true'
                     FROM all_tab_columns
                    WHERE table_name LIKE 'XXAS_TCM_PENDING_CLOCKS'
                      AND column_name = 'MISSED_OUT_FLAG');
    EXCEPTION -- Alter
      WHEN NO_DATA_FOUND THEN                       
         EXECUTE IMMEDIATE 'ALTER TABLE XXAS_TCM_PENDING_CLOCKS
          ADD (
            missed_out_flag varchar2(1) DEFAULT NULL
              )';
    END; -- Alter

    BEGIN -- Alter
       SELECT 'Y'
       INTO l_alter_flag   
       FROM dual
          WHERE EXISTS
                  (SELECT 'true'
                     FROM all_tab_columns
                    WHERE table_name LIKE 'XXAS_TCM_PENDING_CLOCKS'
                      AND column_name = 'REASON_FOR_MISS');
    EXCEPTION -- Alter
      WHEN NO_DATA_FOUND THEN                       
         EXECUTE IMMEDIATE 'ALTER TABLE XXAS_TCM_PENDING_CLOCKS
          ADD (
            reason_for_miss varchar2(100) DEFAULT NULL
              )';
    END; -- Alter

    BEGIN -- Alter
       SELECT 'Y'
       INTO l_alter_flag   
       FROM dual
          WHERE EXISTS
                  (SELECT 'true'
                     FROM all_tab_columns
                    WHERE table_name LIKE 'XXAS_TCM_PENDING_CLOCKS'
                      AND column_name = 'OTHERS_REASON_DETAIL');
    EXCEPTION -- Alter
      WHEN NO_DATA_FOUND THEN                       
         EXECUTE IMMEDIATE 'ALTER TABLE XXAS_TCM_PENDING_CLOCKS
          ADD (
            others_reason_detail varchar2(4000) DEFAULT NULL
              )';
    END; -- Alter

    BEGIN -- Alter
       SELECT 'Y'
       INTO l_alter_flag   
       FROM dual
          WHERE EXISTS
                  (SELECT 'true'
                     FROM all_tab_columns
                    WHERE table_name LIKE 'XXAS_TCM_PENDING_CLOCKS'
                      AND column_name = 'AUTO_SAVED_START_TIME');
    EXCEPTION -- Alter
      WHEN NO_DATA_FOUND THEN                       
         EXECUTE IMMEDIATE 'ALTER TABLE XXAS_TCM_PENDING_CLOCKS
          ADD (
            auto_saved_start_time varchar2(10) DEFAULT NULL
              )';
    END; -- Alter

    BEGIN -- Alter
       SELECT 'Y'
       INTO l_alter_flag   
       FROM dual
          WHERE EXISTS
                  (SELECT 'true'
                     FROM all_tab_columns
                    WHERE table_name LIKE 'XXAS_TCM_PENDING_CLOCKS'
                      AND column_name = 'AUTO_SAVED_END_TIME');
    EXCEPTION -- Alter
      WHEN NO_DATA_FOUND THEN                       
         EXECUTE IMMEDIATE 'ALTER TABLE XXAS_TCM_PENDING_CLOCKS
          ADD (
            auto_saved_end_time varchar2(10) DEFAULT NULL
              )';
    END; -- Alter

    BEGIN -- Alter
       SELECT 'Y'
       INTO l_alter_flag   
       FROM dual
          WHERE EXISTS
                  (SELECT 'true'
                     FROM all_tab_columns
                    WHERE table_name LIKE 'XXAS_TCM_PENDING_CLOCKS'
                      AND column_name = 'CUSTOM_XML');
    EXCEPTION -- Alter
      WHEN NO_DATA_FOUND THEN                       
         EXECUTE IMMEDIATE 'ALTER TABLE XXAS_TCM_PENDING_CLOCKS
          ADD (
            custom_xml XMLTYPE DEFAULT NULL
              )';
    END; -- Alter

  EXCEPTION -- Create
    WHEN NO_DATA_FOUND THEN 
        EXECUTE IMMEDIATE 'CREATE TABLE  XXAS_TCM_PENDING_CLOCKS
                            (pending_clock_id            NUMBER         NOT NULL
                            ,person_id									 NUMBER         NOT NULL
                            ,assignment_id 							 NUMBER         NOT NULL
                            ,start_date_time 						 DATE           NOT NULL
                            ,element_type_id 						 NUMBER         NOT NULL
                            ,pending_details 						 XMLTYPE        NOT NULL
                            ,custom_xml 						     XMLTYPE        NOT NULL
                            ,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
                            ,status_code                 VARCHAR2(30)   DEFAULT ''OPEN'' NOT NULL
                            ,missed_in_flag              VARCHAR2(1)    DEFAULT NULL
                            ,missed_out_flag             VARCHAR2(1)    DEFAULT NULL
                            ,reason_for_miss             VARCHAR2(100)  DEFAULT NULL
                            ,others_reason_detail        VARCHAR2(4000) DEFAULT NULL
                            ,auto_saved_start_time       VARCHAR2(10)   DEFAULT NULL
                            ,auto_saved_end_time         VARCHAR2(10)   DEFAULT NULL)  ';

            
        EXECUTE IMMEDIATE 'GRANT ALL ON XXAS_TCM_PENDING_CLOCKS TO &1 WITH GRANT OPTION';   
  END;    -- Create  
  
END;

/

COMMIT; 
EXIT;
