/* $Id$ */
/*============================================================================*
 |                 Copyright (c) 2016 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
  BEGIN
    EXECUTE IMMEDIATE 'DROP TYPE XXAS_PH_CAL_EVENT_TBL FORCE';
  EXCEPTION
    WHEN e_obj_not_exists THEN
      NULL;
  END;
  BEGIN
    EXECUTE IMMEDIATE 'DROP TYPE XXAS_PH_CAL_EVENT FORCE';
  EXCEPTION
    WHEN e_obj_not_exists THEN
      NULL;
  END;
  BEGIN
    EXECUTE IMMEDIATE 'DROP TYPE XXAS_CAL_EVENT_VIEW_VARRAY FORCE';
  EXCEPTION
    WHEN e_obj_not_exists THEN
      NULL;
  END;
  BEGIN
    EXECUTE IMMEDIATE 'DROP TYPE XXAS_CAL_EVENT_VIEW_TYPE FORCE';
  EXCEPTION
    WHEN e_obj_not_exists THEN
      NULL;
  END;
  BEGIN
    EXECUTE IMMEDIATE 'DROP TYPE XXAS_NOTIF_TRANS_DATA_TBL FORCE';
  EXCEPTION
    WHEN e_obj_not_exists THEN
      NULL;
  END;
  BEGIN
    EXECUTE IMMEDIATE 'DROP TYPE XXAS_NOTIF_TRANS_DATA FORCE';
  EXCEPTION
    WHEN e_obj_not_exists THEN
      NULL;
  END;
  BEGIN
    EXECUTE IMMEDIATE 'DROP TYPE XXAS_NOTIF_INTERNAL_DATA_TBL FORCE';
  EXCEPTION
    WHEN e_obj_not_exists THEN
      NULL;
  END;
  BEGIN
    EXECUTE IMMEDIATE 'DROP TYPE XXAS_NOTIF_INTERNAL_DATA_TYPE FORCE';
  EXCEPTION
    WHEN e_obj_not_exists THEN
      NULL;
  END;
  BEGIN
    EXECUTE IMMEDIATE 'DROP TYPE XXAS_OTL_TCM_PERIOD_VARRAY FORCE';
  EXCEPTION
    WHEN e_obj_not_exists THEN
      NULL;
  END;
  BEGIN
    EXECUTE IMMEDIATE 'DROP TYPE XXAS_OTL_TCM_PERIOD_TYPE FORCE';
  EXCEPTION
    WHEN e_obj_not_exists THEN
      NULL;
  END;

END;  
/

CREATE OR REPLACE TYPE XXAS_PH_CAL_EVENT AS OBJECT
  (country      varchar2(80)
  ,holiday_name varchar2(400)
  ,start_date   date
  ,end_date     date
  );
/
  
CREATE TYPE XXAS_PH_CAL_EVENT_TBL AS TABLE OF XXAS_PH_CAL_EVENT;
/

CREATE OR REPLACE TYPE XXAS_CAL_EVENT_VIEW_TYPE AS OBJECT
  (row_id                varchar2(1000)
  ,person_id             number
  ,assignment_id         number
  ,summary_icon          varchar2(30)
  ,title                 varchar2(400)
  ,resource_name         varchar2(400)
  ,resource_initials     varchar2(400)
  ,sch_additional_info   varchar2(400)
  ,event_start           date
  ,event_end             date
  ,tooltip_line1         varchar2(400)
  ,tooltip_line2         varchar2(400)
  ,color                 varchar2(30)
  ,border_color          varchar2(30)
  ,text_color            varchar2(30)
  ,bg_color              varchar2(30)
  ,page_id               varchar2(4000)
  ,object_id             varchar2(4000)
  ,target                varchar2(4000)
  ,type                  varchar2(1000)
  ,absence_type          varchar2(1000)
  ,absence_category      varchar2(1000)
  ,absence_reason        varchar2(1000)
  ,payment_type          varchar2(1000)
  ,period_name           varchar2(1000)
  ,status                varchar2(1000)
  ,editable              varchar2(30)
  ,code                  varchar2(30)
  ,deleted_flag          varchar2(1)
  ,status_code           varchar2(10)
  ,summation_group       varchar2(150)
  ,summation_field       varchar2(150)
  ,duration_hours        number
  ,field1                varchar2(60)
  ,field2                varchar2(60)
  ,field3                varchar2(60)
  ,field4                varchar2(60)
  ,field5                varchar2(60)
  ,current_approver_role  varchar2(320)
  ,override_approver_role varchar2(320));
/

CREATE OR REPLACE TYPE XXAS_CAL_EVENT_VIEW_VARRAY AS
VARRAY(1000000) OF XXAS_CAL_EVENT_VIEW_TYPE;
/

CREATE OR REPLACE TYPE XXAS_NOTIF_TRANS_DATA AS OBJECT
  (notification_id       number
  ,transaction_id        number
  ,transaction_step_id   number
  ,person_id             number
  ,assignment_id         number
  ,trans_ref_table       varchar2(100)
  ,trans_ref_id          number
  ,item_type             varchar2(10)
  ,item_key              varchar2(240)
  ,process_name          varchar2(30)
  ,trans_group           varchar2(60)
  ,trans_identifier      varchar2(60)
  ,trans_document        CLOB
  );
/
  
CREATE TYPE XXAS_NOTIF_TRANS_DATA_TBL AS TABLE OF XXAS_NOTIF_TRANS_DATA;
/

CREATE OR REPLACE TYPE XXAS_NOTIF_INTERNAL_DATA_TYPE AS OBJECT
  (notification_id     number
  ,internal_data_ids   xxas_com_num_table
  );
/
  
CREATE TYPE XXAS_NOTIF_INTERNAL_DATA_TBL AS TABLE OF XXAS_NOTIF_INTERNAL_DATA_TYPE;
/

CREATE OR REPLACE TYPE XXAS_OTL_TCM_PERIOD_TYPE AS OBJECT
  (period_id             number
  ,period_start_date     date
  ,period_end_date       date
  ,period_name           varchar2(100));
/

CREATE OR REPLACE TYPE XXAS_OTL_TCM_PERIOD_VARRAY AS
VARRAY(1000000) OF XXAS_OTL_TCM_PERIOD_TYPE;
/

COMMIT;
EXIT;
