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

CREATE OR REPLACE VIEW XXAS_BASIC_OFFER_DETAILS_V 
AS
SELECT offer.proposed_start_date     as offer_proposed_start_date
      ,offer.discretionary_job_title as off_discretionary_job_title
      ,offer.creation_date           as offer_creation_date
      ,offer.expiry_date             as offer_expiry_date
      ,offer.offer_version           as off_version
      ,ipt.job_title                 as posting_name
      ,offer.offer_status            as offer_status
      ,offer.offer_id                as offer_id
      ,offer.object_version_number   as offer_object_version_number
      ,offer.last_update_date        as offer_last_update_date
      ,offer.created_by              as offer_created_by
      ,offer.last_updated_by         as offer_last_updated_by
      ,offer.last_update_login       as offer_last_update_login
      ,bg.name              as bg_name
      ,org.name             as organization_name
      ,loc.location_code    as location_name
      ,mgr.full_name        as manager_name
      ,recr.full_name       as recruiter_name
      ,job.name             as job_name
      ,grade.name           as grade_name
      ,pos.name             as position_name
      ,asg.assignment_id    as assignment_id
      ,asg.ass_attribute1   as ass_attribute1
      ,asg.ass_attribute2   as ass_attribute2
      ,asg.ass_attribute3   as ass_attribute3
      ,asg.ass_attribute4   as ass_attribute4
      ,asg.ass_attribute5   as ass_attribute5
      ,asg.ass_attribute6   as ass_attribute6
      ,asg.ass_attribute7   as ass_attribute7
      ,asg.ass_attribute8   as ass_attribute8
      ,asg.ass_attribute9   as ass_attribute9
      ,asg.ass_attribute10  as ass_attribute10
      ,asg.ass_attribute11  as ass_attribute11
      ,asg.ass_attribute12  as ass_attribute12
      ,asg.ass_attribute13  as ass_attribute13
      ,asg.ass_attribute14  as ass_attribute14
      ,asg.ass_attribute15  as ass_attribute15
      ,asg.ass_attribute16  as ass_attribute16
      ,asg.ass_attribute17  as ass_attribute17
      ,asg.ass_attribute18  as ass_attribute18
      ,asg.ass_attribute19  as ass_attribute19
      ,asg.ass_attribute20  as ass_attribute20
      ,asg.ass_attribute21  as ass_attribute21
      ,asg.ass_attribute22  as ass_attribute22
      ,asg.ass_attribute23  as ass_attribute23
      ,asg.ass_attribute24  as ass_attribute24
      ,asg.ass_attribute25  as ass_attribute25
      ,asg.ass_attribute26  as ass_attribute26
      ,asg.ass_attribute27  as ass_attribute27
      ,asg.ass_attribute28  as ass_attribute28
      ,asg.ass_attribute29  as ass_attribute29
      ,asg.ass_attribute30  as ass_attribute30
FROM   irc_offers offer
      ,per_all_vacancies vac
      ,irc_posting_contents_vl ipt
      ,per_all_assignments_f asg
      ,hr_all_organization_units_vl bg
      ,hr_all_organization_units_vl org
      ,hr_locations_all_vl loc
      ,per_all_people_f mgr
      ,per_all_people_f recr
      ,per_jobs_vl job
      ,per_grades_vl grade
      ,hr_all_positions_f_vl pos
WHERE  vac.vacancy_id       = offer.vacancy_id
AND    offer.offer_assignment_id = asg.assignment_id
AND    ipt.posting_content_id(+) = vac.primary_posting_id
AND    bg.organization_id = asg.business_group_id
AND    job.job_id(+)        = asg.job_id
AND    grade.grade_id(+)    = asg.grade_id
AND    pos.position_id(+)   = asg.position_id
AND    TRUNC(sysdate) BETWEEN NVL(pos.effective_start_date,TRUNC(sysdate)) AND NVL(pos.effective_end_date,TRUNC(sysdate))
AND    mgr.person_id(+) = asg.supervisor_id
AND    TRUNC(sysdate) BETWEEN NVL(mgr.effective_start_date,TRUNC(sysdate)) AND NVL(mgr.effective_end_date,TRUNC(sysdate))
AND    recr.person_id(+) = asg.recruiter_id
AND    TRUNC(sysdate) BETWEEN NVL(recr.effective_start_date,TRUNC(sysdate)) AND NVL(recr.effective_end_date,TRUNC(sysdate))
AND    org.organization_id(+)   = asg.organization_id
AND    loc.location_id(+)       = asg.location_id
AND    asg.effective_start_date =
       (SELECT MAX(effective_start_date)
        FROM   per_assignments_f asg2
        WHERE  asg.assignment_id = asg2.assignment_id
        AND    asg2.effective_start_date <= TRUNC(sysdate)
       );

BEGIN
  xxas_fnd_dictionary_pkg.add_view
   (p_application_short_name => xxas_util_pkg.app
   ,p_view_name              => 'XXAS_BASIC_OFFER_DETAILS_V'
   ,p_description            => 'Applaud Basic Offer Details');
END;
/
  
COMMIT;
EXIT;