/* $Id$ */
/*============================================================================*
 |                 Copyright (c) 2015 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_IRC_OFFER_TBL';
  EXCEPTION
    WHEN e_obj_not_exists THEN
      NULL;
  END;
  BEGIN
    EXECUTE IMMEDIATE 'DROP TYPE XXAS_IRC_OFFER';
  EXCEPTION
    WHEN e_obj_not_exists THEN
      NULL;
  END;
  BEGIN
    EXECUTE IMMEDIATE 'DROP TYPE XXAS_OFFER_HISTORY_DETAILS_TBL';
  EXCEPTION
    WHEN e_obj_not_exists THEN
      NULL;
  END;
  BEGIN
    EXECUTE IMMEDIATE 'DROP TYPE XXAS_OFFER_HISTORY_DETAILS';
  EXCEPTION
    WHEN e_obj_not_exists THEN
      NULL;
  END;

END;
/



CREATE OR REPLACE TYPE XXAS_OFFER_HISTORY_DETAILS AS OBJECT
  (off_hist_status                VARCHAR2(1000)
  ,off_hist_status_change_date    DATE
  ,resp_full_name                 VARCHAR2(1000));
/
  
CREATE TYPE XXAS_OFFER_HISTORY_DETAILS_TBL AS TABLE OF XXAS_OFFER_HISTORY_DETAILS;
/

CREATE OR REPLACE TYPE XXAS_IRC_OFFER AS OBJECT
  (offer_id                       NUMBER
  ,href                           VARCHAR2(1000)
  ,person_id                      VARCHAR2(400)
  ,offer_assignment_id            NUMBER
  ,applicant_assignment_id        NUMBER
  ,vacancy_id                     NUMBER
  ,asg_effective_start_date       DATE
  ,asg_effective_end_date         DATE
  ,assignment_id                  NUMBER
  ,pay_basis_id                   NUMBER
  ,assignment_sequence            NUMBER
  ,assignment_type                VARCHAR2(10)
  ,primary_flag                   VARCHAR2(30)
  ,application_id                 NUMBER
  ,employment_category            VARCHAR2(100)
  ,asg_frequency                  VARCHAR2(30)
  ,asg_change_reason              VARCHAR2(30)
  ,manager_flag                   VARCHAR2(30)
  ,normal_hours                   NUMBER
  ,source_type                    VARCHAR2(30)
  ,time_normal_finish             VARCHAR2(10)
  ,time_normal_start              VARCHAR2(10)
  ,asg_last_update_date           DATE
  ,asg_last_updated_by            NUMBER
  ,asg_last_update_login          NUMBER
  ,asg_created_by                 NUMBER
  ,asg_creation_date              DATE
  ,asg_object_version_number      NUMBER
  ,work_at_home                   VARCHAR2(30)
  ,applicant_rank                 NUMBER
  ,labour_union_member_flag       VARCHAR2(30)
  ,posting_content_id             NUMBER
  ,pay_proposal_id                NUMBER
  ,proposal_change_date           DATE
  ,proposal_last_update_date      DATE
  ,proposal_last_updated_by       NUMBER
  ,proposal_last_update_login     NUMBER
  ,proposal_created_by            NUMBER
  ,proposal_creation_date         DATE
  ,proposal_obj_version_number    NUMBER
  ,proposal_approved              VARCHAR2(30)
  ,proposal_multiple_components   VARCHAR2(30)
  ,edit_allowed                   VARCHAR2(30)
  ,edit_label                     VARCHAR2(400)
  ,delete_allowed                 VARCHAR2(30)
  ,delete_label                   VARCHAR2(400)
  ,view_schema_href               VARCHAR2(1000)
  ,form_schema_href               VARCHAR2(1000)
  ,statuses_href                  VARCHAR2(1000)
  ,business_group_id              NUMBER
  ,recruiter_id                   NUMBER
  ,job_id                         NUMBER
  ,grade_id                       NUMBER
  ,assignment_status_type_id      NUMBER
  ,payroll_id                     NUMBER
  ,location_id                    NUMBER
  ,supervisor_id                  NUMBER
  ,organization_id                NUMBER
  ,people_group_id                NUMBER
  ,position_id                    NUMBER
  ,transaction_id                 NUMBER
  ,transaction_type               VARCHAR2(30)
  ,created_by                     NUMBER
  ,image_href                     VARCHAR2(1000)
  ,person_name                    VARCHAR2(1000)
  ,contact1_icon                  VARCHAR2(30)
  ,contact1_text                  VARCHAR2(1000)
  ,contact2_icon                  VARCHAR2(30)
  ,contact2_text                  VARCHAR2(1000)
  ,contact3_icon                  VARCHAR2(30)
  ,contact3_text                  VARCHAR2(1000)
  ,hr_api_status_code             VARCHAR2(30)
  ,workflow_status_code           VARCHAR2(30)
  ,workflow_status_meaning        VARCHAR2(80)
  ,item_type                      VARCHAR2(8)
  ,item_key                       VARCHAR2(240)
  ,status_meaning                 VARCHAR2(80)
  ,status_code                    VARCHAR2(80)
  ,proposed_start_date            DATE
  ,comments_to_approver           VARCHAR2(4000)
  ,full_name                      VARCHAR2(1000)
  ,vacancy_name                   VARCHAR2(1000)
  ,creation_date                  DATE
  ,previous_offer_id              NUMBER
  ,offer_last_update_date         DATE
  ,offer_created_by               NUMBER
  ,offer_last_updated_by          NUMBER
  ,offer_last_update_login        NUMBER
  ,expiry_date                    DATE
  ,offer_version                  NUMBER
  ,latest_offer                   VARCHAR2(30)
  ,address_id                     NUMBER
  ,template_id                    NUMBER
  ,offer_letter_file_type         VARCHAR2(30)
  ,change_reason_code             VARCHAR2(100)
  ,change_reason_meaning          VARCHAR2(1000)
  ,discretionary_job_title        VARCHAR2(1000)
  ,offer_object_version_number    NUMBER
  ,posting_name                   VARCHAR2(1000)
  ,manager_name                   VARCHAR2(1000)
  ,recruiter_name                 VARCHAR2(1000)
  ,pay_basis                      VARCHAR2(100)
  ,proposed_salary                NUMBER
  ,currency                       VARCHAR2(100)
  ,grade_range                    VARCHAR2(100)
  ,comparatio                     VARCHAR2(100)
  ,quartile                       VARCHAR2(1000)
  ,ass_attribute1                 VARCHAR2(1000)
  ,ass_attribute2                 VARCHAR2(1000)
  ,ass_attribute3                 VARCHAR2(1000)
  ,ass_attribute4                 VARCHAR2(1000)
  ,ass_attribute5                 VARCHAR2(1000)
  ,ass_attribute6                 VARCHAR2(1000)
  ,ass_attribute7                 VARCHAR2(1000)
  ,ass_attribute8                 VARCHAR2(1000)
  ,ass_attribute9                 VARCHAR2(1000)
  ,ass_attribute10                VARCHAR2(1000)
  ,ass_attribute11                VARCHAR2(1000)
  ,ass_attribute12                VARCHAR2(1000)
  ,ass_attribute13                VARCHAR2(1000)
  ,ass_attribute14                VARCHAR2(1000)
  ,ass_attribute15                VARCHAR2(1000)
  ,ass_attribute16                VARCHAR2(1000)
  ,ass_attribute17                VARCHAR2(1000)
  ,ass_attribute18                VARCHAR2(1000)
  ,ass_attribute19                VARCHAR2(1000)
  ,ass_attribute20                VARCHAR2(1000)
  ,ass_attribute21                VARCHAR2(1000)
  ,ass_attribute22                VARCHAR2(1000)
  ,ass_attribute23                VARCHAR2(1000)
  ,ass_attribute24                VARCHAR2(1000)
  ,ass_attribute25                VARCHAR2(1000)
  ,ass_attribute26                VARCHAR2(1000)
  ,ass_attribute27                VARCHAR2(1000)
  ,ass_attribute28                VARCHAR2(1000)
  ,ass_attribute29                VARCHAR2(1000)
  ,ass_attribute30                VARCHAR2(1000)
  ,attribute_category             VARCHAR2(1000)
  ,attribute1                     VARCHAR2(1000)
  ,attribute2                     VARCHAR2(1000)
  ,attribute3                     VARCHAR2(1000)
  ,attribute4                     VARCHAR2(1000)
  ,attribute5                     VARCHAR2(1000)
  ,attribute6                     VARCHAR2(1000)
  ,attribute7                     VARCHAR2(1000)
  ,attribute8                     VARCHAR2(1000)
  ,attribute9                     VARCHAR2(1000)
  ,attribute10                    VARCHAR2(1000)
  ,attribute11                    VARCHAR2(1000)
  ,attribute12                    VARCHAR2(1000)
  ,attribute13                    VARCHAR2(1000)
  ,attribute14                    VARCHAR2(1000)
  ,attribute15                    VARCHAR2(1000)
  ,attribute16                    VARCHAR2(1000)
  ,attribute17                    VARCHAR2(1000)
  ,attribute18                    VARCHAR2(1000)
  ,attribute19                    VARCHAR2(1000)
  ,attribute20                    VARCHAR2(1000)
  ,attribute21                    VARCHAR2(1000)
  ,attribute22                    VARCHAR2(1000)
  ,attribute23                    VARCHAR2(1000)
  ,attribute24                    VARCHAR2(1000)
  ,attribute25                    VARCHAR2(1000)
  ,attribute26                    VARCHAR2(1000)
  ,attribute27                    VARCHAR2(1000)
  ,attribute28                    VARCHAR2(1000)
  ,attribute29                    VARCHAR2(1000)
  ,attribute30                    VARCHAR2(1000)
  ,offer_history_details          XXAS_OFFER_HISTORY_DETAILS_TBL
  ,approvers                      XXAS_APPROVER_TBL);
/

CREATE TYPE XXAS_IRC_OFFER_TBL AS TABLE OF XXAS_IRC_OFFER;
/

COMMIT;
EXIT;
