/* $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

CREATE OR REPLACE VIEW xxas_vacancy_offers_v
AS
SELECT iof.offer_id
      ,iof.offer_status status_code
      ,status.meaning status_meaning
      ,iof.proposed_start_date
      ,iof.discretionary_job_title
      ,iof.creation_date
      ,reason.lookup_code  as reason_code
      ,reason.meaning      as reason_meaning
      ,decline.lookup_code as decline_code
      ,decline.meaning     as decline_meaning
      ,osh.status_change_date
      ,osh.offer_status_history_id
      ,iof.expiry_date
      ,iof.offer_version
      ,fnd_profile.value('IRC_OFFER_DURATION_VALUE')
        ||' '
        || (SELECT meaning
            FROM   hr_lookups duration
            WHERE  lookup_type  ='IRC_OFFER_DURATION_MEASUREMENT'
            AND    lookup_code= fnd_profile.value('IRC_OFFER_DURATION_MEASUREMENT')) as offer_duration
      ,to_char(decode
                (iof.offer_status
                ,'EXTENDED', osh.status_change_date
                ,'CLOSED', (SELECT max(status_change_date)
                            FROM   irc_offer_status_history extended
                            WHERE  extended.offer_id = iof.offer_id
                            AND    extended.offer_status = 'EXTENDED'))) as offer_extended_date
      ,osh.change_reason
      ,osh.decline_reason
      ,ipc.posting_content_id as ipc_posting_content_id
      ,ipc.job_title as posting_title
      ,vac.name      as vacancy_name
      ,vac.vacancy_id
      ,iof.latest_offer
      ,iof.offer_assignment_id
      ,iof.address_id
      ,iof.template_id
      ,iof.offer_letter_file_type
      ,iof.attribute_category
      ,iof.attribute1
      ,iof.attribute2
      ,iof.attribute3
      ,iof.attribute4
      ,iof.attribute5
      ,iof.attribute6
      ,iof.attribute7
      ,iof.attribute8
      ,iof.attribute9
      ,iof.attribute10
      ,iof.attribute11
      ,iof.attribute12
      ,iof.attribute13
      ,iof.attribute14
      ,iof.attribute15
      ,iof.attribute16
      ,iof.attribute17
      ,iof.attribute18
      ,iof.attribute19
      ,iof.attribute20
      ,iof.attribute21
      ,iof.attribute22
      ,iof.attribute23
      ,iof.attribute24
      ,iof.attribute25
      ,iof.attribute26
      ,iof.attribute27
      ,iof.attribute28
      ,iof.attribute29
      ,iof.attribute30
      ,iof.offer_extended_method
      ,paf.assignment_id
      ,paf.effective_start_date as asg_effective_start_date
      ,paf.effective_end_date   as asg_effective_end_date
      ,paf.business_group_id
      ,paf.recruiter_id
      ,paf.job_id
      ,paf.grade_id
      ,paf.assignment_status_type_id
      ,paf.payroll_id
      ,paf.location_id
      ,paf.supervisor_id
      ,paf.organization_id
      ,paf.people_group_id
      ,paf.position_id
      ,paf.pay_basis_id
      ,paf.assignment_sequence
      ,paf.assignment_type
      ,paf.primary_flag
      ,paf.application_id
      ,paf.change_reason as asg_change_reason
      ,paf.employment_category
      ,paf.frequency     as asg_frequency
      ,paf.manager_flag
      ,paf.normal_hours
      ,paf.source_type
      ,paf.time_normal_finish
      ,paf.time_normal_start
      ,paf.last_update_date      as asg_last_update_date
      ,paf.last_updated_by       as asg_last_updated_by
      ,paf.last_update_login     as asg_last_update_login
      ,paf.created_by            as asg_created_by
      ,paf.creation_date         as asg_creation_date
      ,paf.object_version_number as asg_object_version_number
      ,paf.work_at_home
      ,paf.applicant_rank
      ,paf.labour_union_member_flag
      ,paf.posting_content_id
      ,ppf.full_name            as full_name
      ,ppf.person_id
      ,ppf.effective_start_date as effective_start_date1
      ,ppf.effective_end_date   as effective_end_date1
      ,nvl((SELECT 'N'
            FROM   per_vacancies pv
            WHERE  pv.vacancy_id = vac.vacancy_id)
           ,'Y') as disable_selection
      ,iof.applicant_assignment_id
      ,bod.posting_name        as posting_name
      ,bod.bg_name             as bg_name
      ,bod.organization_name   as organization_name
      ,bod.location_name       as location_name
      ,bod.manager_name        as manager_name
      ,bod.recruiter_name      as recruiter_name
      ,bod.job_name            as job_name
      ,bod.grade_name          as grade_name
      ,bod.position_name       as position_name
      ,bod.offer_last_update_date
      ,bod.offer_created_by
      ,bod.offer_last_updated_by
      ,bod.offer_last_update_login
      ,bod.offer_object_version_number
      ,osd.salary_basis    as salary_basis
      ,osd.pay_basis
      ,osd.proposed_salary as proposed_salary
      ,osd.currency        as currency
      ,osd.grade_range     as grade_range
      ,osd.comparatio      as comparatio
      ,osd.quartile        as quartile
      ,osd.pay_proposal_id
      ,osd.proposal_change_date
      ,osd.proposal_last_update_date
      ,osd.proposal_last_updated_by
      ,osd.proposal_last_update_login
      ,osd.proposal_created_by
      ,osd.proposal_creation_date
      ,osd.proposal_obj_version_number
      ,osd.proposal_approved
      ,osd.proposal_multiple_components
      ,bod.ass_attribute1
      ,bod.ass_attribute2
      ,bod.ass_attribute3
      ,bod.ass_attribute4
      ,bod.ass_attribute5
      ,bod.ass_attribute6
      ,bod.ass_attribute7
      ,bod.ass_attribute8
      ,bod.ass_attribute9
      ,bod.ass_attribute10
      ,bod.ass_attribute11
      ,bod.ass_attribute12
      ,bod.ass_attribute13
      ,bod.ass_attribute14
      ,bod.ass_attribute15
      ,bod.ass_attribute16
      ,bod.ass_attribute17
      ,bod.ass_attribute18
      ,bod.ass_attribute19
      ,bod.ass_attribute20
      ,bod.ass_attribute21
      ,bod.ass_attribute22
      ,bod.ass_attribute23
      ,bod.ass_attribute24
      ,bod.ass_attribute25
      ,bod.ass_attribute26
      ,bod.ass_attribute27
      ,bod.ass_attribute28
      ,bod.ass_attribute29
      ,bod.ass_attribute30
FROM   irc_offers iof
      ,irc_offer_status_history osh
      ,hr_lookups reason
      ,hr_lookups status
      ,hr_lookups decline
      ,per_all_vacancies vac
      ,irc_posting_contents_vl ipc
      ,per_all_people_f linkppf
      ,per_all_people_f ppf
      ,per_all_assignments_f paf
      ,xxas_basic_offer_details_v bod
      ,xxas_offer_salary_details_v osd
WHERE  iof.latest_offer = 'Y'
AND    osh.offer_id = iof.offer_id
AND    osh.offer_status = iof.offer_status
AND    osh.status_change_date =
        (SELECT max(osh2.status_change_date)
         FROM   irc_offer_status_history osh2
         WHERE  osh2.offer_id       = iof.offer_id
         AND    osh2.offer_status = iof.offer_status)
AND    status.lookup_code(+)       = iof.offer_status
AND    status.lookup_type(+)       = 'IRC_OFFER_STATUSES'
AND    reason.lookup_code(+)       = osh.change_reason
AND    reason.lookup_type(+)       = 'IRC_OFFER_STATUS_CHANGE_REASON'
AND    decline.lookup_type(+)      = osh.decline_reason
AND    decline.lookup_code(+)      = 'IRC_OFFER_DECLINE_REASON'
AND    vac.vacancy_id              = iof.vacancy_id
AND    ipc.posting_content_id(+)   = vac.primary_posting_id
AND    iof.offer_assignment_id = paf.assignment_id
AND    paf.effective_start_date =
        (SELECT max(paaf2.effective_start_date)
         FROM   per_all_assignments_f paaf2
         WHERE  paaf2.assignment_id = paf.assignment_id
         AND    paaf2.effective_start_date <= trunc(sysdate))
AND    linkppf.person_id = paf.person_id
AND    ppf.party_id = linkppf.party_id
AND    trunc(sysdate) BETWEEN
       ppf.effective_start_date AND ppf.effective_end_date
AND    trunc(sysdate) BETWEEN
       linkppf.effective_start_date AND linkppf.effective_end_date
AND    bod.offer_id = iof.offer_id
AND    bod.assignment_id = iof.offer_assignment_id
AND    osd.assignment_id = iof.offer_assignment_id
AND    osd.offer_id      = iof.offer_id
/* AND (NOT EXISTS
      (SELECT null
       FROM   per_all_people_f nonemp
       WHERE  :2 BETWEEN
              nonemp.effective_start_date and nonemp.effective_end_date
       AND    nonemp.party_id = ppf.party_id
      AND     nonemp.current_employee_flag = 'Y')
    OR EXISTS
      (SELECT null
       FROM   per_all_people_f ppf3
             ,per_all_assignments_f eaa
             ,per_vacancies svc
       WHERE  :3 BETWEEN
              ppf3.effective_start_date AND ppf3.effective_end_date
      AND ppf3.party_id       = ppf.party_id
      AND eaa.person_id       = ppf3.person_id
      AND svc.vacancy_id      = eaa.vacancy_id
      AND eaa.assignment_type = 'A')) */;

BEGIN
  xxas_fnd_dictionary_pkg.add_view
   (p_application_short_name => xxas_util_pkg.app
   ,p_view_name              => 'XXAS_VACANCY_OFFERS_V'
   ,p_description            => 'Applaud Vacancy Offers');
END;
/

COMMIT;
EXIT;

