/* $Id$ */
/*============================================================================*
 |                 Copyright (c) 2010 Applaud Solutions UK Ltd                |
 |                             All rights reserved                            |
 *===========================================================================*/

SET VERIFY OFF
SET FEEDBACK OFF
WHENEVER OSERROR  EXIT FAILURE ROLLBACK
WHENEVER SQLERROR EXIT FAILURE ROLLBACK

DECLARE
  e_obj_not_exists EXCEPTION;
  PRAGMA EXCEPTION_INIT(e_obj_not_exists, -04043);
BEGIN
  EXECUTE IMMEDIATE 'DROP TYPE XXAS_COM_AVAIL_ASG_VARRAY';
  EXECUTE IMMEDIATE 'DROP TYPE XXAS_COM_AVAIL_ASG_TYPE';
EXCEPTION
  WHEN e_obj_not_exists THEN
    NULL;
END;
/

DECLARE
  e_obj_not_exists EXCEPTION;
  PRAGMA EXCEPTION_INIT(e_obj_not_exists, -04043);
BEGIN
  EXECUTE IMMEDIATE 'DROP TYPE XXAS_COM_AVAIL_PER_VARRAY';
  EXECUTE IMMEDIATE 'DROP TYPE XXAS_COM_AVAIL_PER_TYPE';
EXCEPTION
  WHEN e_obj_not_exists THEN
    NULL;
END;
/

DECLARE
  e_obj_not_exists EXCEPTION;
  PRAGMA EXCEPTION_INIT(e_obj_not_exists, -04043);
BEGIN
  EXECUTE IMMEDIATE 'DROP TYPE XXAS_COM_AVAIL_ENR_VARRAY';
  EXECUTE IMMEDIATE 'DROP TYPE XXAS_COM_AVAIL_ENR_TYPE';
  EXECUTE IMMEDIATE 'DROP TYPE XXAS_COM_AVAIL_ABS_VARRAY';
  EXECUTE IMMEDIATE 'DROP TYPE XXAS_COM_AVAIL_ABS_TYPE';
  EXECUTE IMMEDIATE 'DROP TYPE XXAS_COM_AVAIL_EVENT_VARRAY';
  EXECUTE IMMEDIATE 'DROP TYPE XXAS_COM_AVAIL_EVENT_TYPE';
EXCEPTION
  WHEN e_obj_not_exists THEN
    NULL;
END;
/

CREATE OR REPLACE TYPE XXAS_COM_AVAIL_EVENT_TYPE AS OBJECT
  (start_datetime date
  ,end_datetime   date
  ,free_busy      varchar2(30)
  ,color          varchar2(30)
  ,availability   varchar2(255)
  ,description    varchar2(255)
  ,icon           varchar2(255)
  ,bg_color       varchar2(255))
/

CREATE OR REPLACE TYPE XXAS_COM_AVAIL_EVENT_VARRAY AS
VARRAY(100000) OF xxas_com_avail_event_type
/

CREATE OR REPLACE TYPE XXAS_COM_AVAIL_ABS_TYPE AS OBJECT
  (absence_id     number
  ,event          xxas_com_avail_event_type)
/

CREATE OR REPLACE TYPE XXAS_COM_AVAIL_ABS_VARRAY AS
VARRAY(100000) OF XXAS_COM_AVAIL_ABS_TYPE
/

CREATE OR REPLACE TYPE XXAS_COM_AVAIL_ENR_TYPE AS OBJECT
  (event_id       number
  ,event          xxas_com_avail_event_type)
/

CREATE OR REPLACE TYPE XXAS_COM_AVAIL_ENR_VARRAY AS
VARRAY(100000) OF XXAS_COM_AVAIL_ENR_TYPE
/

CREATE OR REPLACE TYPE XXAS_COM_AVAIL_ASG_TYPE AS OBJECT
  (assignment_id     number
  ,business_group_id number
  ,effective_date    date
  ,absences          xxas_com_avail_abs_varray
  ,training          xxas_com_avail_enr_varray
  ,custom            xxas_com_avail_event_varray)
/

CREATE OR REPLACE TYPE XXAS_COM_AVAIL_ASG_VARRAY AS
VARRAY(100000) OF XXAS_COM_AVAIL_ASG_TYPE
/

COMMIT;
EXIT;
