/* $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_PER_ALL_VACANCY_TBL FORCE';
  EXCEPTION
    WHEN e_obj_not_exists THEN
      NULL;
  END;
  BEGIN
    EXECUTE IMMEDIATE 'DROP TYPE XXAS_PER_ALL_VACANCY FORCE';
  EXCEPTION
    WHEN e_obj_not_exists THEN
      NULL;
  END;
  BEGIN
    EXECUTE IMMEDIATE 'DROP TYPE XXAS_PER_REC_ACTIVITY_TBL FORCE';
  EXCEPTION
    WHEN e_obj_not_exists THEN
      NULL;
  END;
  BEGIN
    EXECUTE IMMEDIATE 'DROP TYPE XXAS_PER_REC_ACTIVITY FORCE';
  EXCEPTION
    WHEN e_obj_not_exists THEN
      NULL;
  END;  
  BEGIN
    EXECUTE IMMEDIATE 'DROP TYPE XXAS_IRC_REC_TEAM_MEMBER_TBL FORCE';
  EXCEPTION
    WHEN e_obj_not_exists THEN
      NULL;
  END;
  BEGIN
    EXECUTE IMMEDIATE 'DROP TYPE XXAS_IRC_REC_TEAM_MEMBER FORCE';
  EXCEPTION
    WHEN e_obj_not_exists THEN
      NULL;
  END;  
  BEGIN
    EXECUTE IMMEDIATE 'DROP TYPE XXAS_IRC_AGENCY_VACANCY_TBL FORCE';
  EXCEPTION
    WHEN e_obj_not_exists THEN
      NULL;
  END;
  BEGIN
    EXECUTE IMMEDIATE 'DROP TYPE XXAS_IRC_AGENCY_VACANCY FORCE';
  EXCEPTION
    WHEN e_obj_not_exists THEN
      NULL;
  END;
  BEGIN
    EXECUTE IMMEDIATE 'DROP TYPE XXAS_PER_COMPETENCE_ELE_TBL FORCE';
  EXCEPTION
    WHEN e_obj_not_exists THEN
      NULL;
  END;
  BEGIN
    EXECUTE IMMEDIATE 'DROP TYPE XXAS_PER_COMPETENCE_ELE FORCE';
  EXCEPTION
    WHEN e_obj_not_exists THEN
      NULL;
  END;
  BEGIN
    EXECUTE IMMEDIATE 'DROP TYPE XXAS_IRC_VARIABLE_COMP_ELE_TBL FORCE';
  EXCEPTION
    WHEN e_obj_not_exists THEN
      NULL;
  END;
  BEGIN
    EXECUTE IMMEDIATE 'DROP TYPE XXAS_IRC_VARIABLE_COMP_ELE FORCE';
  EXCEPTION
    WHEN e_obj_not_exists THEN
      NULL;
  END;
  BEGIN
    EXECUTE IMMEDIATE 'DROP TYPE XXAS_VACANCY_TASK_TBL FORCE';
  EXCEPTION
    WHEN e_obj_not_exists THEN
      NULL;
  END;
  BEGIN
    EXECUTE IMMEDIATE 'DROP TYPE XXAS_VACANCY_TASK FORCE';
  EXCEPTION
    WHEN e_obj_not_exists THEN
      NULL;
  END;
END;
/

CREATE OR REPLACE TYPE XXAS_VACANCY_TASK AS OBJECT
  (vacancy_id                     NUMBER
  ,task                           VARCHAR2(400)
  ,description                    VARCHAR2(4000)
  ,target                         VARCHAR2(1000)
  ,deep_link_info                 XMLType);
/
  
CREATE TYPE XXAS_VACANCY_TASK_TBL AS TABLE OF XXAS_VACANCY_TASK;
/

CREATE OR REPLACE TYPE XXAS_IRC_VARIABLE_COMP_ELE AS OBJECT
  (vacancy_id                     NUMBER
  ,variable_comp_code             VARCHAR2(30)
  ,variable_comp_meaning          VARCHAR2(80)
  ,object_version_number          NUMBER
  ,last_update_date               DATE
  ,last_updated_by                NUMBER
  ,last_update_login              NUMBER
  ,created_by                     NUMBER
  ,creation_date                  DATE);
/
  
CREATE TYPE XXAS_IRC_VARIABLE_COMP_ELE_TBL AS TABLE OF XXAS_IRC_VARIABLE_COMP_ELE;
/

CREATE OR REPLACE TYPE XXAS_PER_COMPETENCE_ELE AS OBJECT
  (competence_element_id          NUMBER
  ,object_version_number          NUMBER
  ,type_code                      VARCHAR2(30)
  ,type_meaning                   VARCHAR2(80)
  ,competence_id                  NUMBER
  ,competence_name                VARCHAR2(700)
  ,proficiency_level_id           NUMBER
  ,proficiency_level_value        VARCHAR2(80)
  ,high_proficiency_level_id      NUMBER
  ,high_proficiency_level_value   VARCHAR2(80)
  ,mandatory_code                 VARCHAR2(30)
  ,mandatory_meaning              VARCHAR2(80)
  ,attribute_category             VARCHAR2(30)
  ,attribute1                     VARCHAR2(150)
  ,attribute2                     VARCHAR2(150)
  ,attribute3                     VARCHAR2(150)
  ,attribute4                     VARCHAR2(150)
  ,attribute5                     VARCHAR2(150)
  ,attribute6                     VARCHAR2(150)
  ,attribute7                     VARCHAR2(150)
  ,attribute8                     VARCHAR2(150)
  ,attribute9                     VARCHAR2(150)
  ,attribute10                    VARCHAR2(150)
  ,attribute11                    VARCHAR2(150)
  ,attribute12                    VARCHAR2(150)
  ,attribute13                    VARCHAR2(150)
  ,attribute14                    VARCHAR2(150)
  ,attribute15                    VARCHAR2(150)
  ,attribute16                    VARCHAR2(150)
  ,attribute17                    VARCHAR2(150)
  ,attribute18                    VARCHAR2(150)
  ,attribute19                    VARCHAR2(150)
  ,attribute20                    VARCHAR2(150)
  ,last_update_date               DATE
  ,last_updated_by                NUMBER
  ,last_update_login              NUMBER
  ,created_by                     NUMBER
  ,creation_date                  DATE);
/
  
CREATE TYPE XXAS_PER_COMPETENCE_ELE_TBL AS TABLE OF XXAS_PER_COMPETENCE_ELE;
/

CREATE OR REPLACE TYPE XXAS_IRC_AGENCY_VACANCY AS OBJECT
  (agency_vacancy_id              NUMBER
  ,agency_id                      NUMBER
  ,agency_name                    VARCHAR2(240)
  ,vacancy_id                     NUMBER
  ,start_date                     DATE
  ,end_date                       DATE
  ,max_allowed_applicants         NUMBER
  ,manage_applicants_allowed      VARCHAR2(30)
  ,attribute_category             VARCHAR2(30)
  ,attribute1                     VARCHAR2(150)
  ,attribute2                     VARCHAR2(150)
  ,attribute3                     VARCHAR2(150)
  ,attribute4                     VARCHAR2(150)
  ,attribute5                     VARCHAR2(150)
  ,attribute6                     VARCHAR2(150)
  ,attribute7                     VARCHAR2(150)
  ,attribute8                     VARCHAR2(150)
  ,attribute9                     VARCHAR2(150)
  ,attribute10                    VARCHAR2(150)
  ,attribute11                    VARCHAR2(150)
  ,attribute12                    VARCHAR2(150)
  ,attribute13                    VARCHAR2(150)
  ,attribute14                    VARCHAR2(150)
  ,attribute15                    VARCHAR2(150)
  ,attribute16                    VARCHAR2(150)
  ,attribute17                    VARCHAR2(150)
  ,attribute18                    VARCHAR2(150)
  ,attribute19                    VARCHAR2(150)
  ,attribute20                    VARCHAR2(150)
  ,attribute21                    VARCHAR2(150)
  ,attribute22                    VARCHAR2(150)
  ,attribute23                    VARCHAR2(150)
  ,attribute24                    VARCHAR2(150)
  ,attribute25                    VARCHAR2(150)
  ,attribute26                    VARCHAR2(150)
  ,attribute27                    VARCHAR2(150)
  ,attribute28                    VARCHAR2(150)
  ,attribute29                    VARCHAR2(150)
  ,attribute30                    VARCHAR2(150)
  ,object_version_number          NUMBER
  ,last_update_date               DATE
  ,last_updated_by                NUMBER
  ,last_update_login              NUMBER
  ,created_by                     NUMBER
  ,creation_date                  DATE);
/
  
CREATE TYPE XXAS_IRC_AGENCY_VACANCY_TBL AS TABLE OF XXAS_IRC_AGENCY_VACANCY;
/

CREATE OR REPLACE TYPE XXAS_IRC_REC_TEAM_MEMBER AS OBJECT
  (rec_team_member_id             NUMBER
  ,person_id                      NUMBER
  ,party_id                       NUMBER
  ,vacancy_id                     NUMBER
  ,job_id                         NUMBER
  ,start_date                     DATE
  ,end_date                       DATE
  ,update_allowed                 VARCHAR2(30)
  ,delete_allowed                 VARCHAR2(30)
  ,object_version_number          NUMBER
  ,last_update_date               DATE
  ,last_updated_by                NUMBER
  ,last_update_login              NUMBER
  ,created_by                     NUMBER
  ,creation_date                  DATE);
/
  
CREATE TYPE XXAS_IRC_REC_TEAM_MEMBER_TBL AS TABLE OF XXAS_IRC_REC_TEAM_MEMBER;
/

CREATE OR REPLACE TYPE XXAS_PER_REC_ACTIVITY AS OBJECT
  (recruitment_activity_id        NUMBER
  ,recruitment_activity_for_id    NUMBER
  ,vacancy_id                     NUMBER
  ,date_start                     DATE
  ,date_end                       DATE
  ,name                           VARCHAR2(30)
  ,posting_content_id             NUMBER
  ,recruiting_site_id             NUMBER
  ,recruiting_site_name           VARCHAR2(240)
  ,object_version_number          NUMBER
  ,for_object_version_number      NUMBER
  ,last_update_date               DATE
  ,last_updated_by                NUMBER
  ,last_update_login              NUMBER
  ,created_by                     NUMBER
  ,creation_date                  DATE);
/
  
CREATE TYPE XXAS_PER_REC_ACTIVITY_TBL AS TABLE OF XXAS_PER_REC_ACTIVITY;
/

CREATE OR REPLACE TYPE XXAS_PER_ALL_VACANCY AS OBJECT
  (vacancy_id                     NUMBER
  ,href                           VARCHAR2(1000)
  ,vacancy_name                   VARCHAR2(400)
  ,edit_allowed                   VARCHAR2(30)
  ,edit_label                     VARCHAR2(400)
  ,delete_allowed                 VARCHAR2(30)
  ,delete_label                   VARCHAR2(400) 
  ,num_total                      NUMBER
  ,num_active                     NUMBER
  ,num_term                       NUMBER
  ,num_interview1                 NUMBER
  ,num_interview2                 NUMBER
  ,num_offer                      NUMBER
  ,num_accepted                   NUMBER
  ,num_not_set                    NUMBER
  ,num_no                         NUMBER
  ,num_unsure                     NUMBER
  ,num_yes                        NUMBER 
  ,view_schema_href               VARCHAR2(1000)
  ,form_schema_href               VARCHAR2(1000)
  ,applicants_href                VARCHAR2(1000)
  ,applicants_export_href         VARCHAR2(1000)
  ,offers_href                    VARCHAR2(1000)
  ,ad_hoc_comms_href              VARCHAR2(1000)
  ,transaction_id                 NUMBER
  ,transaction_type               VARCHAR2(30)
  ,hr_api_status_code             VARCHAR2(30)
  ,workflow_status_code           VARCHAR2(30)
  ,workflow_status_meaning        VARCHAR2(80)
  ,item_type                      VARCHAR2(8)
  ,item_key                       VARCHAR2(240)
  ,business_group_id              NUMBER
  ,business_group_name            VARCHAR2(400)
  ,position_id                    NUMBER
  ,position_name                  VARCHAR2(400)
  ,job_id                         NUMBER
  ,job_name                       VARCHAR2(400)
  ,grade_id                       NUMBER
  ,grade_name                     VARCHAR2(400)
  ,organization_id                NUMBER
  ,organization_name              VARCHAR2(400)
  ,requisition_id                 NUMBER
  ,req_object_version_number      NUMBER
  ,people_group_id                NUMBER
  ,location_id                    NUMBER
  ,location_name                  VARCHAR2(400)
  ,recruiter_id                   NUMBER
  ,date_from                      DATE
  ,date_to                        DATE
  ,description                    VARCHAR2(80)
  ,number_of_openings             NUMBER
  ,status_code                    VARCHAR2(30)
  ,status_meaning                 VARCHAR2(80)
  ,status_color                   VARCHAR2(30)
  ,request_id                     NUMBER
  ,program_application_id         NUMBER
  ,program_id                     NUMBER
  ,program_update_date            DATE
  ,attribute_category             VARCHAR2(30)
  ,attribute1                     VARCHAR2(150)
  ,attribute2                     VARCHAR2(150)
  ,attribute3                     VARCHAR2(150)
  ,attribute4                     VARCHAR2(150)
  ,attribute5                     VARCHAR2(150)
  ,attribute6                     VARCHAR2(150)
  ,attribute7                     VARCHAR2(150)
  ,attribute8                     VARCHAR2(150)
  ,attribute9                     VARCHAR2(150)
  ,attribute10                    VARCHAR2(150)
  ,attribute11                    VARCHAR2(150)
  ,attribute12                    VARCHAR2(150)
  ,attribute13                    VARCHAR2(150)
  ,attribute14                    VARCHAR2(150)
  ,attribute15                    VARCHAR2(150)
  ,attribute16                    VARCHAR2(150)
  ,attribute17                    VARCHAR2(150)
  ,attribute18                    VARCHAR2(150)
  ,attribute19                    VARCHAR2(150)
  ,attribute20                    VARCHAR2(150)
  ,last_update_date               DATE
  ,last_updated_by                NUMBER
  ,last_update_login              NUMBER
  ,created_by                     NUMBER
  ,creation_date                  DATE
  ,vacancy_category_code          VARCHAR2(30)
  ,vacancy_category_meaning       VARCHAR2(80)
  ,budget_type_code               VARCHAR2(30)
  ,budget_type_meaning            VARCHAR2(80)
  ,budget_value                   NUMBER
  ,manager_id                     NUMBER
  ,security_method                VARCHAR2(30)
  ,object_version_number          NUMBER
  ,assessment_id                  NUMBER
  ,primary_posting_id             NUMBER
  ,comments                       VARCHAR2(4000)
  ,comments_to_approver           VARCHAR2(4000)
  ,display_manager_info           VARCHAR2(30)
  ,display_recruiter_info         VARCHAR2(30)
  ,posting_name                   VARCHAR2(240)
  ,posting_org_name               VARCHAR2(240)
  ,posting_org_description        CLOB
  ,posting_job_title              VARCHAR2(240)
  ,posting_brief_description      CLOB
  ,posting_detailed_description   CLOB
  ,posting_job_requirements       CLOB
  ,posting_additional_details     CLOB
  ,posting_how_to_apply           CLOB
  ,image_url                      CLOB
  ,image_url_alt                  CLOB
  ,benefit_info                   CLOB
  ,ipc_attribute_category         VARCHAR2(30)
  ,ipc_attribute1                 VARCHAR2(150)
  ,ipc_attribute2                 VARCHAR2(150)
  ,ipc_attribute3                 VARCHAR2(150)
  ,ipc_attribute4                 VARCHAR2(150)
  ,ipc_attribute5                 VARCHAR2(150)
  ,ipc_attribute6                 VARCHAR2(150)
  ,ipc_attribute7                 VARCHAR2(150)
  ,ipc_attribute8                 VARCHAR2(150)
  ,ipc_attribute9                 VARCHAR2(150)
  ,ipc_attribute10                VARCHAR2(150)
  ,ipc_attribute11                VARCHAR2(150)
  ,ipc_attribute12                VARCHAR2(150)
  ,ipc_attribute13                VARCHAR2(150)
  ,ipc_attribute14                VARCHAR2(150)
  ,ipc_attribute15                VARCHAR2(150)
  ,ipc_attribute16                VARCHAR2(150)
  ,ipc_attribute17                VARCHAR2(150)
  ,ipc_attribute18                VARCHAR2(150)
  ,ipc_attribute19                VARCHAR2(150)
  ,ipc_attribute20                VARCHAR2(150)
  ,ipc_attribute21                VARCHAR2(150)
  ,ipc_attribute22                VARCHAR2(150)
  ,ipc_attribute23                VARCHAR2(150)
  ,ipc_attribute24                VARCHAR2(150)
  ,ipc_attribute25                VARCHAR2(150)
  ,ipc_attribute26                VARCHAR2(150)
  ,ipc_attribute27                VARCHAR2(150)
  ,ipc_attribute28                VARCHAR2(150)
  ,ipc_attribute29                VARCHAR2(150)
  ,ipc_attribute30                VARCHAR2(150)
  ,ipc_last_update_date           DATE
  ,ipc_last_updated_by            NUMBER
  ,ipc_last_update_login          NUMBER
  ,ipc_created_by                 NUMBER
  ,ipc_creation_date              DATE
  ,ipc_object_version_number      NUMBER
  ,recruiter_full_name            VARCHAR2(240)
  ,recruiter_email                VARCHAR2(240)
  ,recruiter_work_telephone       VARCHAR2(60)
  ,manager_full_name              VARCHAR2(240)
  ,manager_email                  VARCHAR2(240)
  ,manager_work_telephone         VARCHAR2(60)
  ,search_criteria_id             NUMBER
  ,employee_flag                  VARCHAR2(30)
  ,contractor_flag                VARCHAR2(30)
  ,employment_category_code       VARCHAR2(30)
  ,employment_category_meaning    VARCHAR2(80)
  ,travel_percentage              NUMBER
  ,travel_percentage_meaning      VARCHAR2(80)
  ,min_salary                     NUMBER(15,2)
  ,max_salary                     NUMBER
  ,salary_currency                VARCHAR2(30)
  ,salary_period_code             VARCHAR2(30)
  ,salary_period_meaning          VARCHAR2(80)
  ,professional_area_code         VARCHAR2(30)
  ,professional_area_meaning      VARCHAR2(80)
  ,work_at_home_code              VARCHAR2(30)
  ,work_at_home_meaning           VARCHAR2(80)
  ,min_qual_level_id              NUMBER
  ,min_qual_level_name            VARCHAR2(150)
  ,max_qual_level_id              NUMBER
  ,max_qual_level_name            VARCHAR2(150)
  ,isc_attribute_category         VARCHAR2(30)
  ,isc_attribute1                 VARCHAR2(150)
  ,isc_attribute2                 VARCHAR2(150)
  ,isc_attribute3                 VARCHAR2(150)
  ,isc_attribute4                 VARCHAR2(150)
  ,isc_attribute5                 VARCHAR2(150)
  ,isc_attribute6                 VARCHAR2(150)
  ,isc_attribute7                 VARCHAR2(150)
  ,isc_attribute8                 VARCHAR2(150)
  ,isc_attribute9                 VARCHAR2(150)
  ,isc_attribute10                VARCHAR2(150)
  ,isc_attribute11                VARCHAR2(150)
  ,isc_attribute12                VARCHAR2(150)
  ,isc_attribute13                VARCHAR2(150)
  ,isc_attribute14                VARCHAR2(150)
  ,isc_attribute15                VARCHAR2(150)
  ,isc_attribute16                VARCHAR2(150)
  ,isc_attribute17                VARCHAR2(150)
  ,isc_attribute18                VARCHAR2(150)
  ,isc_attribute19                VARCHAR2(150)
  ,isc_attribute20                VARCHAR2(150)
  ,isc_attribute21                VARCHAR2(150)
  ,isc_attribute22                VARCHAR2(150)
  ,isc_attribute23                VARCHAR2(150)
  ,isc_attribute24                VARCHAR2(150)
  ,isc_attribute25                VARCHAR2(150)
  ,isc_attribute26                VARCHAR2(150)
  ,isc_attribute27                VARCHAR2(150)
  ,isc_attribute28                VARCHAR2(150)
  ,isc_attribute29                VARCHAR2(150)
  ,isc_attribute30                VARCHAR2(150)
  ,isc_object_version_number      NUMBER
  ,pg_id_flex_num                 NUMBER
  ,segment1                       VARCHAR2(60)
  ,segment2                       VARCHAR2(60)
  ,segment3                       VARCHAR2(60)
  ,segment4                       VARCHAR2(60)
  ,segment5                       VARCHAR2(60)
  ,segment6                       VARCHAR2(60)
  ,segment7                       VARCHAR2(60)
  ,segment8                       VARCHAR2(60)
  ,segment9                       VARCHAR2(60)
  ,segment10                      VARCHAR2(60)
  ,segment11                      VARCHAR2(60)
  ,segment12                      VARCHAR2(60)
  ,segment13                      VARCHAR2(60)
  ,segment14                      VARCHAR2(60)
  ,segment15                      VARCHAR2(60)
  ,segment16                      VARCHAR2(60)
  ,segment17                      VARCHAR2(60)
  ,segment18                      VARCHAR2(60)
  ,segment19                      VARCHAR2(60)
  ,segment20                      VARCHAR2(60)
  ,segment21                      VARCHAR2(60)
  ,segment22                      VARCHAR2(60)
  ,segment23                      VARCHAR2(60)
  ,segment24                      VARCHAR2(60)
  ,segment25                      VARCHAR2(60)
  ,segment26                      VARCHAR2(60)
  ,segment27                      VARCHAR2(60)
  ,segment28                      VARCHAR2(60)
  ,segment29                      VARCHAR2(60)
  ,segment30                      VARCHAR2(60)
  ,variable_comp_elements         XXAS_IRC_VARIABLE_COMP_ELE_TBL
  ,competence_elements            XXAS_PER_COMPETENCE_ELE_TBL
  ,agency_vacancies               XXAS_IRC_AGENCY_VACANCY_TBL
  ,team_members                   XXAS_IRC_REC_TEAM_MEMBER_TBL
  ,recruitment_activities         XXAS_PER_REC_ACTIVITY_TBL
  ,vacancy_tasks                  XXAS_VACANCY_TASK_TBL
  ,attachments                    XXAS_ATTACHMENT_TABLE
  ,approvers                      XXAS_APPROVER_TBL);
/

CREATE TYPE XXAS_PER_ALL_VACANCY_TBL AS TABLE OF XXAS_PER_ALL_VACANCY;
/

COMMIT;
EXIT;
