/* $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_html_vacancy_offers_v
AS
SELECT iof.offer_id
           ,ppf.person_id
           ,iof.offer_assignment_id
           ,iof.applicant_assignment_id as assignment_id
           ,vac.name      as vacancy_name
           ,vac.vacancy_id
           ,iof.offer_status status_code
		   ,iof.offer_version
		   ,iof.latest_offer
		   ,iof.discretionary_job_title
		   ,paf.business_group_id
		   ,paf.position_id
		   ,paf.job_id
           ,paf.grade_id
		   ,paf.organization_id
		   ,paf.location_id
           ,paf.supervisor_id
		   ,paf.recruiter_id
		   ,paf.employment_category
		   ,paf.time_normal_finish
           ,paf.time_normal_start
		   ,paf.normal_hours
		   ,paf. frequency asg_frequency
		   ,paf.pay_basis_id
		   ,iof.expiry_date
		   ,iof.proposed_start_date
		   ,osd.pay_proposal_id
		   ,osd.proposed_salary
		   ,osd.currency   
           ,osd.grade_range
           ,osd.comparatio 
           ,osd.quartile   
		   ,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
		   ,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
           ,cast
             (multiset
               (SELECT xxas_offer_history_details
                         (ohd.off_hist_status
                         ,ohd.off_hist_status_change_date
                         ,ohd.resp_full_name)
                FROM   xxas_offer_history_details_v ohd
                WHERE  ohd.offer_id = iof.offer_id
               )
             AS xxas_offer_history_details_tbl) offer_history_details
            ,cast
             (multiset
               (SELECT a.attached_document_id
                          ,a.seq_num sequence_number
                          ,a.entity_name
                          ,a.pk1_value
                          ,a.pk2_value
                          ,a.pk3_value
                          ,a.pk4_value
                          ,a.pk5_value
                          ,c.name category_code
                          ,c.user_name category_name
                          ,d.document_id
                          ,d.file_name
                          ,d.title
                          ,d.description
                          ,l.file_id
                          ,l.file_content_type mime_type
                          ,l.file_format lob_format
                          ,fnd_gfm.construct_download_url(fnd_web_config.gfm_agent, l.file_id) file_url
                          ,null wp_link
                                                  ,a.creation_date
                          ,xxas_wf_role_util_pkg.get_display_name_from_user(a.created_by) created_by
                          ,a.last_update_date
                          ,xxas_wf_role_util_pkg.get_display_name_from_user(a.last_updated_by) last_updated_by
                 FROM   fnd_attached_documents a
                       ,fnd_documents_vl d
                       ,fnd_lobs l
                       ,fnd_document_categories_vl c
                       ,fnd_document_datatypes_vl t
                 WHERE  t.name = 'FILE'
                 AND    d.media_id = l.file_id
                 AND    a.document_id = d.document_id
                 AND    a.category_id = c.category_id
                 AND    d.datatype_id = t.datatype_id
                 AND    a.entity_name IN('IRC_EXT_OFFER')
                 AND    a.pk1_value IS NOT NULL
                 AND    a.pk1_value = to_char(iof.applicant_assignment_id))
                                 AS xxas_attachment_table) ATTACHMENTS
FROM   irc_offers iof
      ,hr_lookups status
      ,per_all_vacancies vac
      ,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    status.lookup_code(+)      = iof.offer_status
AND    status.lookup_type(+)      = 'IRC_OFFER_STATUSES'
AND    vac.vacancy_id             = iof.vacancy_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    bod.assignment_id          = iof.offer_assignment_id
AND    osd.assignment_id          = iof.offer_assignment_id
AND    osd.offer_id               = iof.offer_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;

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

COMMIT;
EXIT;
