/* $Id$ */
/*============================================================================*
 |                 Copyright (c) 2014 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_VACANCY_APPLICANT_TBL FORCE';
  EXCEPTION
    WHEN e_obj_not_exists THEN
      NULL;
  END;
  BEGIN
    EXECUTE IMMEDIATE 'DROP TYPE XXAS_VACANCY_APPLICANT FORCE';
  EXCEPTION
    WHEN e_obj_not_exists THEN
      NULL;
  END;
  BEGIN
    EXECUTE IMMEDIATE 'DROP TYPE XXAS_PER_QUALIFICATION_TBL FORCE';
  EXCEPTION
    WHEN e_obj_not_exists THEN
      NULL;
  END;
  BEGIN
    EXECUTE IMMEDIATE 'DROP TYPE XXAS_PER_QUALIFICATION FORCE';
  EXCEPTION
    WHEN e_obj_not_exists THEN
      NULL;
  END;
	
  BEGIN
    EXECUTE IMMEDIATE 'DROP TYPE XXAS_PER_ADDL_QUAL_TBL FORCE';
  EXCEPTION
    WHEN e_obj_not_exists THEN
      NULL;
  END;
  BEGIN
    EXECUTE IMMEDIATE 'DROP TYPE XXAS_PER_ADDL_QUAL FORCE';
  EXCEPTION
    WHEN e_obj_not_exists THEN
      NULL;
  END;	
  BEGIN
    EXECUTE IMMEDIATE 'DROP TYPE XXAS_PER_COMPETENCIES_TBL FORCE';
  EXCEPTION
    WHEN e_obj_not_exists THEN
      NULL;
  END;
  BEGIN
    EXECUTE IMMEDIATE 'DROP TYPE XXAS_PER_COMPETENCIES FORCE';
  EXCEPTION
    WHEN e_obj_not_exists THEN
      NULL;
  END;  
  BEGIN
    EXECUTE IMMEDIATE 'DROP TYPE XXAS_PER_ASSESSMENT_TBL FORCE';
  EXCEPTION
    WHEN e_obj_not_exists THEN
      NULL;
  END;
  BEGIN
    EXECUTE IMMEDIATE 'DROP TYPE XXAS_PER_ASSESSMENT FORCE';
  EXCEPTION
    WHEN e_obj_not_exists THEN
      NULL;
  END;    
  BEGIN
    EXECUTE IMMEDIATE 'DROP TYPE XXAS_PER_EEOINFO_TBL FORCE';
  EXCEPTION
    WHEN e_obj_not_exists THEN
      NULL;
  END;
  BEGIN
    EXECUTE IMMEDIATE 'DROP TYPE XXAS_PER_EEOINFO FORCE';
  EXCEPTION
    WHEN e_obj_not_exists THEN
      NULL;
  END;   
  BEGIN
    EXECUTE IMMEDIATE 'DROP TYPE XXAS_PER_PREV_EMPLOYERS_TBL FORCE';
  EXCEPTION
    WHEN e_obj_not_exists THEN
      NULL;
  END;  
  BEGIN
    EXECUTE IMMEDIATE 'DROP TYPE XXAS_PER_PREVIOUS_EMPLOYERS FORCE';
  EXCEPTION
    WHEN e_obj_not_exists THEN
      NULL;
  END; 
  BEGIN
    EXECUTE IMMEDIATE 'DROP TYPE XXAS_PER_ASG_HISTORY_TBL FORCE';
  EXCEPTION
    WHEN e_obj_not_exists THEN
      NULL;
  END;  
  BEGIN
    EXECUTE IMMEDIATE 'DROP TYPE XXAS_PER_ASG_HISTORY FORCE';
  EXCEPTION
    WHEN e_obj_not_exists THEN
      NULL;
  END;  
  BEGIN
    EXECUTE IMMEDIATE 'DROP TYPE XXAS_PER_JOBS_CONSIDERED_TBL FORCE';
  EXCEPTION
    WHEN e_obj_not_exists THEN
      NULL;
  END;  
  BEGIN
    EXECUTE IMMEDIATE 'DROP TYPE XXAS_PER_JOBS_CONSIDERED FORCE';
  EXCEPTION
    WHEN e_obj_not_exists THEN
      NULL;
  END;
  BEGIN
    EXECUTE IMMEDIATE 'DROP TYPE XXAS_APL_OTHER_JOB_APPLS_TBL FORCE';
  EXCEPTION
    WHEN e_obj_not_exists THEN
      NULL;
  END;  
  BEGIN
    EXECUTE IMMEDIATE 'DROP TYPE XXAS_APL_OTHER_JOB_APPLS FORCE';
  EXCEPTION
    WHEN e_obj_not_exists THEN
      NULL;
  END;
  BEGIN
    EXECUTE IMMEDIATE 'DROP TYPE XXAS_APL_PHONE_NUMBERS_TBL FORCE';
  EXCEPTION
    WHEN e_obj_not_exists THEN
      NULL;
  END;  
  BEGIN
    EXECUTE IMMEDIATE 'DROP TYPE XXAS_APL_PHONE_NUMBERS FORCE';
  EXCEPTION
    WHEN e_obj_not_exists THEN
      NULL;
  END;
END;
/

CREATE OR REPLACE TYPE XXAS_PER_COMPETENCIES AS OBJECT
  (skill_level                     VARCHAR2(1000)
  ,skill_name                      VARCHAR2(1000)
  );
/

CREATE TYPE XXAS_PER_COMPETENCIES_TBL AS TABLE OF XXAS_PER_COMPETENCIES;
/

CREATE OR REPLACE TYPE XXAS_PER_ASG_HISTORY AS OBJECT
  (ASSIGNMENT_ID                   NUMBER(15)
  ,ASSIGNMENT_STATUS_ID            NUMBER(15)
  ,STATUS_CHANGE_DATE              DATE
  ,STATUS_CHANGE_REASON            VARCHAR2(240)
  ,ASSIGNMENT_STATUS               VARCHAR2(80)		
  ,ASSIGNMENT_STATUS_TYPE_ID       NUMBER(9)
  ,EXTERNAL_STATUS                 VARCHAR2(240)
  );
/

CREATE TYPE XXAS_PER_ASG_HISTORY_TBL AS TABLE OF XXAS_PER_ASG_HISTORY;
/

CREATE OR REPLACE TYPE XXAS_PER_EEOINFO AS OBJECT
  (sex                             VARCHAR2(30)
  ,ethinic_origin                  VARCHAR2(150)
  );
/

CREATE TYPE XXAS_PER_EEOINFO_TBL AS TABLE OF XXAS_PER_EEOINFO;
/

CREATE OR REPLACE TYPE XXAS_PER_ASSESSMENT AS OBJECT
  (question                        VARCHAR2(4000)
  ,user_response                   VARCHAR2(4000)
  );
/

CREATE TYPE XXAS_PER_ASSESSMENT_TBL AS TABLE OF XXAS_PER_ASSESSMENT;
/


CREATE OR REPLACE TYPE XXAS_PER_QUALIFICATION AS OBJECT
  (establishment                   VARCHAR2(255)
  ,address                         VARCHAR2(4000)
  ,start_date                      DATE
  ,end_date                        DATE
  ,pea_attribute1_meaning          VARCHAR2(4000) -- degree_level
  ,pea_attribute2_meaning          VARCHAR2(4000) -- major
  ,pea_attribute3_meaning          VARCHAR2(4000) -- minor
  ,pea_attribute4_meaning          VARCHAR2(4000) -- location
  ,pea_attribute5_meaning          VARCHAR2(4000) -- grade_point_average
  ,pea_attribute6_meaning          VARCHAR2(4000) -- credits_earned
  ,pea_attribute7_meaning          VARCHAR2(4000) -- graduation_date
  ,qualification_name 	           VARCHAR2(150)  -- degree
  ,title                           VARCHAR2(120)  -- major_subject
  ,awarded_date                    DATE           -- date_received
  ,grade_attained                  VARCHAR2(100)  -- GRADE_ATTAINED
  );
/
  
CREATE TYPE XXAS_PER_QUALIFICATION_TBL AS TABLE OF XXAS_PER_QUALIFICATION;
/

CREATE OR REPLACE TYPE XXAS_PER_ADDL_QUAL AS OBJECT
  (pq_category_meaning             VARCHAR2(4000)   --CATEGORY
  ,qualification_name              VARCHAR2(150)    -- Type
  ,title                           VARCHAR2(120)    -- QUALIFICATION
  ,grade_attained                  VARCHAR2(100)    -- GRADE/LEVEL
  ,awarded_date                    DATE	            -- DATE RECEIVED
  ,tuition_method                  VARCHAR2(30)     -- HOW RECEIVED
  );
/
  
CREATE TYPE XXAS_PER_ADDL_QUAL_TBL AS TABLE OF XXAS_PER_ADDL_QUAL;
/
CREATE OR REPLACE TYPE XXAS_PER_PREVIOUS_EMPLOYERS AS OBJECT
  (prev_empr_start_date            DATE
  ,prev_empr_end_date              DATE
  ,prev_empr_job_name              VARCHAR2(1000)
  ,prev_empr_name                  VARCHAR2(1000)
  ,prev_empr_descr                 VARCHAR2(1000)
  ,prev_empr_address               VARCHAR2(1000)
  ,prev_empr_duties                VARCHAR2(4000));
/
  
CREATE TYPE XXAS_PER_PREV_EMPLOYERS_TBL AS TABLE OF XXAS_PER_PREVIOUS_EMPLOYERS;
/

CREATE OR REPLACE TYPE XXAS_PER_JOBS_CONSIDERED AS OBJECT
  (vacancy_name                   VARCHAR2(30)
  ,organization_name              VARCHAR2(240)
  ,last_updated_date              DATE
  ,current_consideration          VARCHAR2(4000)
  );
/
  
CREATE TYPE XXAS_PER_JOBS_CONSIDERED_TBL AS TABLE OF XXAS_PER_JOBS_CONSIDERED;
/

CREATE OR REPLACE TYPE XXAS_APL_OTHER_JOB_APPLS AS OBJECT
  (vacancy_name                   VARCHAR2(30)
  ,job_name                       VARCHAR2(1000)
  ,position_name                  VARCHAR2(1000)
  ,organization_name              VARCHAR2(240)
  ,location_name                  VARCHAR2(240)
  ,status                         VARCHAR2(240)
  ,date_applied                   DATE
  );
/
  
CREATE TYPE XXAS_APL_OTHER_JOB_APPLS_TBL AS TABLE OF XXAS_APL_OTHER_JOB_APPLS;
/

CREATE OR REPLACE TYPE XXAS_APL_PHONE_NUMBERS AS OBJECT
  (phone_type_display             VARCHAR2(1000)
  ,phone_number_display           VARCHAR2(240)
  ,times_meaning_display          VARCHAR2(300)
  );
/
  
CREATE TYPE XXAS_APL_PHONE_NUMBERS_TBL AS TABLE OF XXAS_APL_PHONE_NUMBERS;
/

CREATE OR REPLACE TYPE XXAS_VACANCY_APPLICANT AS OBJECT
  (assignment_id                  NUMBER
  ,person_id                      NUMBER
  ,href                           VARCHAR2(1000)
  ,view_schema_href               VARCHAR2(1000)
  ,image_href                     VARCHAR2(1000)
  ,person_name                    VARCHAR2(1000)
  ,business_group_id              NUMBER
  ,contact1_icon                  VARCHAR2(30)
  ,contact1_text                  VARCHAR2(1000)
  ,contact2_icon                  VARCHAR2(30)
  ,contact2_text                  VARCHAR2(1000)
  ,contact3_icon                  VARCHAR2(30) 
  ,contact3_text                  VARCHAR2(1000)
  ,style                          VARCHAR2(30)
  ,address_type                   VARCHAR2(30)
  ,address_line1                  VARCHAR2(240)
  ,address_line2                  VARCHAR2(240)
  ,address_line3                  VARCHAR2(240)
  ,town_or_city                   VARCHAR2(30)
  ,region_1                       VARCHAR2(120)
  ,region_2                       VARCHAR2(120)
  ,region_3                       VARCHAR2(120)
  ,postal_code                    VARCHAR2(30)
  ,country                        VARCHAR2(60)
  ,sex                            VARCHAR2(30)
  ,internal_applicant             VARCHAR2(50)
  ,attribute_category             VARCHAR2(30)
  ,attribute1                     VARCHAR2(150)
  ,attribute1_meaning             VARCHAR2(1000)
  ,attribute2                     VARCHAR2(150)
  ,attribute2_meaning             VARCHAR2(1000)
  ,attribute3                     VARCHAR2(150)
  ,attribute3_meaning             VARCHAR2(1000)
  ,attribute4                     VARCHAR2(150)
  ,attribute4_meaning             VARCHAR2(1000)
  ,attribute5                     VARCHAR2(150)
  ,attribute5_meaning             VARCHAR2(1000)
  ,attribute6                     VARCHAR2(150)
  ,attribute6_meaning             VARCHAR2(1000)
  ,attribute7                     VARCHAR2(150)
  ,attribute7_meaning             VARCHAR2(1000)
  ,attribute8                     VARCHAR2(150)
  ,attribute8_meaning             VARCHAR2(1000)
  ,attribute9                     VARCHAR2(150)
  ,attribute9_meaning             VARCHAR2(1000)
  ,attribute10                    VARCHAR2(150)
  ,attribute10_meaning            VARCHAR2(1000)
  ,attribute11                    VARCHAR2(150)
  ,attribute11_meaning            VARCHAR2(1000)
  ,attribute12                    VARCHAR2(150)
  ,attribute12_meaning            VARCHAR2(1000)
  ,attribute13                    VARCHAR2(150)
  ,attribute13_meaning            VARCHAR2(1000)
  ,attribute14                    VARCHAR2(150)
  ,attribute14_meaning            VARCHAR2(1000)
  ,attribute15                    VARCHAR2(150)
  ,attribute15_meaning            VARCHAR2(1000)
  ,attribute16                    VARCHAR2(150)
  ,attribute16_meaning            VARCHAR2(1000)
  ,attribute17                    VARCHAR2(150)
  ,attribute17_meaning            VARCHAR2(1000)
  ,attribute18                    VARCHAR2(150)
  ,attribute18_meaning            VARCHAR2(1000)
  ,attribute19                    VARCHAR2(150)
  ,attribute19_meaning            VARCHAR2(1000)
  ,attribute20                    VARCHAR2(150)
  ,attribute20_meaning            VARCHAR2(1000)
  ,attribute21                    VARCHAR2(150)
  ,attribute21_meaning            VARCHAR2(1000)
  ,attribute22                    VARCHAR2(150)
  ,attribute22_meaning            VARCHAR2(1000)
  ,attribute23                    VARCHAR2(150)
  ,attribute23_meaning            VARCHAR2(1000)
  ,attribute24                    VARCHAR2(150)
  ,attribute24_meaning            VARCHAR2(1000)
  ,attribute25                    VARCHAR2(150)
  ,attribute25_meaning            VARCHAR2(1000)
  ,attribute26                    VARCHAR2(150)
  ,attribute26_meaning            VARCHAR2(1000)
  ,attribute27                    VARCHAR2(150)
  ,attribute27_meaning            VARCHAR2(1000)
  ,attribute28                    VARCHAR2(150)
  ,attribute28_meaning            VARCHAR2(1000)
  ,attribute29                    VARCHAR2(150)
  ,attribute29_meaning            VARCHAR2(1000)
  ,attribute30                    VARCHAR2(150)
  ,attribute30_meaning            VARCHAR2(1000)
  ,per_information_category       VARCHAR2(1000)
  ,per_information1               VARCHAR2(1000)
  ,per_information1_meaning       VARCHAR2(1000)
  ,per_information2               VARCHAR2(1000)
  ,per_information2_meaning       VARCHAR2(1000)
  ,per_information3               VARCHAR2(1000)
  ,per_information3_meaning       VARCHAR2(1000)
  ,per_information4               VARCHAR2(1000)
  ,per_information4_meaning       VARCHAR2(1000)
  ,per_information5               VARCHAR2(1000)
  ,per_information5_meaning       VARCHAR2(1000)
  ,per_information6               VARCHAR2(1000)
  ,per_information6_meaning       VARCHAR2(1000)
  ,per_information7               VARCHAR2(1000)
  ,per_information7_meaning       VARCHAR2(1000)
  ,per_information8               VARCHAR2(1000)
  ,per_information8_meaning       VARCHAR2(1000)
  ,per_information9               VARCHAR2(1000)
  ,per_information9_meaning       VARCHAR2(1000)
  ,per_information10              VARCHAR2(1000)
  ,per_information10_meaning      VARCHAR2(1000)
  ,per_information11              VARCHAR2(1000)
  ,per_information11_meaning      VARCHAR2(1000)
  ,per_information12              VARCHAR2(1000)
  ,per_information12_meaning      VARCHAR2(1000)
  ,per_information13              VARCHAR2(1000)
  ,per_information13_meaning      VARCHAR2(1000)
  ,per_information14              VARCHAR2(1000)
  ,per_information14_meaning      VARCHAR2(1000)
  ,per_information15              VARCHAR2(1000)
  ,per_information15_meaning      VARCHAR2(1000)
  ,per_information16              VARCHAR2(1000)
  ,per_information16_meaning      VARCHAR2(1000)
  ,per_information17              VARCHAR2(1000)
  ,per_information17_meaning      VARCHAR2(1000)
  ,per_information18              VARCHAR2(1000)
  ,per_information18_meaning      VARCHAR2(1000)
  ,per_information19              VARCHAR2(1000)
  ,per_information19_meaning      VARCHAR2(1000)
  ,per_information20              VARCHAR2(1000)
  ,per_information20_meaning      VARCHAR2(1000)
  ,per_information21              VARCHAR2(1000)
  ,per_information21_meaning      VARCHAR2(1000)
  ,per_information22              VARCHAR2(1000)
  ,per_information22_meaning      VARCHAR2(1000)
  ,per_information23              VARCHAR2(1000)
  ,per_information23_meaning      VARCHAR2(1000)
  ,per_information24              VARCHAR2(1000)
  ,per_information24_meaning      VARCHAR2(1000)
  ,per_information25              VARCHAR2(1000)
  ,per_information25_meaning      VARCHAR2(1000)
  ,per_information26              VARCHAR2(1000)
  ,per_information26_meaning      VARCHAR2(1000)
  ,per_information27              VARCHAR2(1000)
  ,per_information27_meaning      VARCHAR2(1000)
  ,per_information28              VARCHAR2(1000)
  ,per_information28_meaning      VARCHAR2(1000)
  ,per_information29              VARCHAR2(1000)
  ,per_information29_meaning      VARCHAR2(1000)
  ,per_information30              VARCHAR2(1000)
  ,per_information30_meaning      VARCHAR2(1000) 
  ,applicant_rank                 NUMBER
  ,interested                     VARCHAR2(400)
  ,current_status                 VARCHAR2(1000)
  ,previous_employer              VARCHAR2(120)
  ,last_qual                      VARCHAR2(120)
  ,correspondence_language        VARCHAR2(30)
  ,active_applications_count      NUMBER
  ,qualifications                 XXAS_PER_QUALIFICATION_TBL
  ,addl_qualifications            XXAS_PER_ADDL_QUAL_TBL
  ,competencies                   XXAS_PER_COMPETENCIES_TBL
  ,previous_employers             XXAS_PER_PREV_EMPLOYERS_TBL  
  ,assessments                    XXAS_PER_ASSESSMENT_TBL
  ,jobs_considered_for            XXAS_PER_JOBS_CONSIDERED_TBL
  ,other_job_applications         XXAS_APL_OTHER_JOB_APPLS_TBL
  ,phone_numbers                  XXAS_APL_PHONE_NUMBERS_TBL
  --WORK PREFERENCES COLUMN BEGIN
  ,keywords                       VARCHAR2(240)
  ,distance_to_location_int       NUMBER
  ,amount_of_travel               VARCHAR2(80)
  ,work_from_home                 VARCHAR2(80)
  ,employee_display               VARCHAR2(80)
  ,contractor_display             VARCHAR2(80)
  ,professional_area_str          VARCHAR2(4000)
  ,location                       VARCHAR2(240)
  ,emp_category                   VARCHAR2(30)
  ,min_salary                     NUMBER
  ,currency                       VARCHAR2(30)
  ,match_competencies             VARCHAR2(80)
  ,match_qualifications           VARCHAR2(80)
  ,isc_attribute_category         VARCHAR2(30)
  ,isc_attribute1                 VARCHAR2(150)
  ,isc_attribute1_meaning         VARCHAR2(1000)
  ,isc_attribute2                 VARCHAR2(150)
  ,isc_attribute2_meaning         VARCHAR2(1000)
  ,isc_attribute3                 VARCHAR2(150)
  ,isc_attribute3_meaning         VARCHAR2(1000)
  ,isc_attribute4                 VARCHAR2(150)
  ,isc_attribute4_meaning         VARCHAR2(1000)
  ,isc_attribute5                 VARCHAR2(150)
  ,isc_attribute5_meaning         VARCHAR2(1000)
  ,isc_attribute6                 VARCHAR2(150)
  ,isc_attribute6_meaning         VARCHAR2(1000)
  ,isc_attribute7                 VARCHAR2(150)
  ,isc_attribute7_meaning         VARCHAR2(1000)
  ,isc_attribute8                 VARCHAR2(150)
  ,isc_attribute8_meaning         VARCHAR2(1000)
  ,isc_attribute9                 VARCHAR2(150)
  ,isc_attribute9_meaning         VARCHAR2(1000)
  ,isc_attribute10                VARCHAR2(150)
  ,isc_attribute10_meaning        VARCHAR2(1000)
  ,isc_attribute11                VARCHAR2(150)
  ,isc_attribute11_meaning        VARCHAR2(1000)
  ,isc_attribute12                VARCHAR2(150)
  ,isc_attribute12_meaning        VARCHAR2(1000)
  ,isc_attribute13                VARCHAR2(150)
  ,isc_attribute13_meaning        VARCHAR2(1000)
  ,isc_attribute14                VARCHAR2(150)
  ,isc_attribute14_meaning        VARCHAR2(1000)
  ,isc_attribute15                VARCHAR2(150)
  ,isc_attribute15_meaning        VARCHAR2(1000)
  ,isc_attribute16                VARCHAR2(150)
  ,isc_attribute16_meaning        VARCHAR2(1000)
  ,isc_attribute17                VARCHAR2(150)
  ,isc_attribute17_meaning        VARCHAR2(1000)
  ,isc_attribute18                VARCHAR2(150)
  ,isc_attribute18_meaning        VARCHAR2(1000)
  ,isc_attribute19                VARCHAR2(150)
  ,isc_attribute19_meaning        VARCHAR2(1000)
  ,isc_attribute20                VARCHAR2(150)
  ,isc_attribute20_meaning        VARCHAR2(1000)
  ,isc_attribute21                VARCHAR2(150)
  ,isc_attribute21_meaning        VARCHAR2(1000)
  ,isc_attribute22                VARCHAR2(150)
  ,isc_attribute22_meaning        VARCHAR2(1000)
  ,isc_attribute23                VARCHAR2(150)
  ,isc_attribute23_meaning        VARCHAR2(1000)
  ,isc_attribute24                VARCHAR2(150)
  ,isc_attribute24_meaning        VARCHAR2(1000)
  ,isc_attribute25                VARCHAR2(150)
  ,isc_attribute25_meaning        VARCHAR2(1000)
  ,isc_attribute26                VARCHAR2(150)
  ,isc_attribute26_meaning        VARCHAR2(1000)
  ,isc_attribute27                VARCHAR2(150)
  ,isc_attribute27_meaning        VARCHAR2(1000)
  ,isc_attribute28                VARCHAR2(150)
  ,isc_attribute28_meaning        VARCHAR2(1000)
  ,isc_attribute29                VARCHAR2(150)
  ,isc_attribute29_meaning        VARCHAR2(1000)
  ,isc_attribute30                VARCHAR2(150)
  ,isc_attribute30_meaning        VARCHAR2(1000)
  --WORK PREFERENCES COLUMN END	
  ,assignment_history             XXAS_PER_ASG_HISTORY_TBL);
/

CREATE TYPE XXAS_VACANCY_APPLICANT_TBL AS TABLE OF XXAS_VACANCY_APPLICANT;
/

COMMIT;
EXIT;
