/* $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_DIRECT_REPORT_TBL FORCE';
  EXCEPTION
    WHEN e_obj_not_exists THEN
      NULL;
  END;
  BEGIN
    EXECUTE IMMEDIATE 'DROP TYPE XXAS_DIRECT_REPORT FORCE';
  EXCEPTION
    WHEN e_obj_not_exists THEN
      NULL;
  END;
  BEGIN
    EXECUTE IMMEDIATE 'DROP TYPE XXAS_NEW_DIRECT_REPORT_TBL FORCE';
  EXCEPTION
    WHEN e_obj_not_exists THEN
      NULL;
  END;
  BEGIN
    EXECUTE IMMEDIATE 'DROP TYPE XXAS_NEW_DIRECT_REPORT FORCE';
  EXCEPTION
    WHEN e_obj_not_exists THEN
      NULL;
  END;
  BEGIN
    EXECUTE IMMEDIATE 'DROP TYPE XXAS_PER_INFO_TYPE_VARRAY FORCE';
  EXCEPTION
    WHEN e_obj_not_exists THEN
      NULL;
  END;
  BEGIN
    EXECUTE IMMEDIATE 'DROP TYPE XXAS_PER_INFO_TYPE FORCE';
  EXCEPTION
    WHEN e_obj_not_exists THEN
      NULL;
  END;
  BEGIN
    EXECUTE IMMEDIATE 'DROP TYPE XXAS_ORG_MGR_TBL FORCE';
  EXCEPTION
    WHEN e_obj_not_exists THEN
      NULL;
  END;
  BEGIN
    EXECUTE IMMEDIATE 'DROP TYPE XXAS_ORG_MGR FORCE';
  EXCEPTION
    WHEN e_obj_not_exists THEN
      NULL;
  END;
  BEGIN
    EXECUTE IMMEDIATE 'DROP TYPE XXAS_ORG_INFO_TYPE_VARRAY FORCE';
  EXCEPTION
    WHEN e_obj_not_exists THEN
      NULL;
  END;
  BEGIN
    EXECUTE IMMEDIATE 'DROP TYPE XXAS_ORG_INFO_TYPE FORCE';
  EXCEPTION
    WHEN e_obj_not_exists THEN
      NULL;
  END;
  BEGIN
    EXECUTE IMMEDIATE 'DROP TYPE XXAS_MGR_DIRECT_REPORT_TBL FORCE';
  EXCEPTION
    WHEN e_obj_not_exists THEN
      NULL;
  END;
  BEGIN
    EXECUTE IMMEDIATE 'DROP TYPE XXAS_MGR_DIRECT_REPORT FORCE';
  EXCEPTION
    WHEN e_obj_not_exists THEN
      NULL;
  END;
  BEGIN
    EXECUTE IMMEDIATE 'DROP TYPE XXAS_MGR_NEW_DIRECT_REPORT_TBL FORCE';
  EXCEPTION
    WHEN e_obj_not_exists THEN
      NULL;
  END;
  BEGIN
    EXECUTE IMMEDIATE 'DROP TYPE XXAS_MGR_NEW_DIRECT_REPORT FORCE';
  EXCEPTION
    WHEN e_obj_not_exists THEN
      NULL;
  END;

END;  
/

CREATE OR REPLACE TYPE XXAS_DIRECT_REPORT AS OBJECT
  (dr_effective_date              date
  ,dr_person_id                   number
  ,dr_li_person_id                varchar2(240)
  ,direct_report                  varchar2(240)
  ,job_id                         number
  ,dr_job                         varchar2(240)
  ,dr_cur_manager                 varchar2(1000)
  ,dr_new_manager                 number
  ,dr_new_manager_p               number
  );
/
  
CREATE TYPE XXAS_DIRECT_REPORT_TBL AS TABLE OF XXAS_DIRECT_REPORT;
/

CREATE OR REPLACE TYPE XXAS_NEW_DIRECT_REPORT AS OBJECT
  (new_dr_effective_date              date
  ,new_direct_report                  varchar2(1000)
  ,new_direct_report_p                varchar2(1000)
  ,new_dr_job                         varchar2(240)
  );
/
  
CREATE TYPE XXAS_NEW_DIRECT_REPORT_TBL AS TABLE OF XXAS_NEW_DIRECT_REPORT;
/

CREATE OR REPLACE TYPE XXAS_PER_INFO_TYPE AS OBJECT
  (person_id             number
  ,job_id                number
  ,job_name              varchar2(240)
  ,global_name           varchar2(240)
  ,local_name            varchar2(240)
  ,assignment_id         number
  ,mgr_person_id         number
  ,effective_date        date);
/

CREATE TYPE XXAS_PER_INFO_TYPE_VARRAY AS VARRAY(100000) OF XXAS_PER_INFO_TYPE;
/

CREATE OR REPLACE TYPE XXAS_ORG_MGR AS OBJECT
  (organization_name              varchar2(240)
  ,organization_id                number
  ,dr_cur_manager                 varchar2(240)
  ,dr_new_manager                 number
  ,dr_new_manager_p               number
  ,dr_person_id                   number
  ,start_date                     date
  ,start_date_p                   date
  ,end_date                       date
  ,end_date_p                     date
  ,org_information_id             number
  ,reporting_name                 varchar2(150)
  ,cur_start_date                 date
  ,cur_end_date                   date
  ,org_object_version_number      number
  );
/
  
CREATE TYPE XXAS_ORG_MGR_TBL AS TABLE OF XXAS_ORG_MGR;
/

CREATE OR REPLACE TYPE XXAS_ORG_INFO_TYPE AS OBJECT
  (organization_name              varchar2(240)
  ,organization_id                number
  ,cur_manager                    varchar2(240)
  ,manager_id                     number
  ,org_information_id             number
  ,reporting_name                 varchar2(150)
  ,cur_start_date                 date
  ,cur_end_date                   date
  ,org_object_version_number      number
  ,start_date                     date
  ,end_date                       date
  ,new_manager                    varchar2(240)
  ,new_manager_id                 varchar2(150));
/

CREATE TYPE XXAS_ORG_INFO_TYPE_VARRAY AS VARRAY(100000) OF XXAS_ORG_INFO_TYPE;
/

CREATE OR REPLACE TYPE XXAS_MGR_DIRECT_REPORT AS OBJECT
  (effective_date         date
  ,person_id              number
  ,assignment_id          number
  ,new_manager_person_id  number
  );
/

CREATE TYPE XXAS_MGR_DIRECT_REPORT_TBL AS TABLE OF XXAS_MGR_DIRECT_REPORT;
/

CREATE OR REPLACE TYPE XXAS_MGR_NEW_DIRECT_REPORT AS OBJECT
  (effective_date     date
  ,person_id          number
  );
/

CREATE TYPE XXAS_MGR_NEW_DIRECT_REPORT_TBL AS TABLE OF XXAS_MGR_NEW_DIRECT_REPORT;
/

COMMIT;
EXIT;
