/* $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_applicants_v
AS
SELECT distinct papf.person_id apl_person_id
      ,inp.person_id person_id
      ,papf.party_id
      ,xxas_per_image_pkg.url
         (papf.person_id, img.last_update_date, 'MEDIUM') image_href
      ,papf.effective_start_date person_start_date
      ,papf.effective_end_date person_end_date
      ,vac.vacancy_id
      ,xxas_util_pkg.get_formatted_name
         (papf.full_name, papf.global_name, papf.local_name
         ,papf.first_name, papf.last_name, papf.known_as) person_name
      ,papf.last_name
      ,papf.first_name
      ,papf.known_as
      ,papf.email_address
      ,paaf.assignment_id
      ,paaf.business_group_id
      ,paaf.change_reason
      ,paaf.effective_start_date applicant_asg_start_date
      ,paaf.effective_end_date applicant_asg_end_date
      ,decode(papf.current_applicant_flag, 'Y', 'Y', 'N') current_applicant
      ,decode(papf.current_employee_flag, 'Y', 'Y', 'N') current_employee
      ,decode(papf.current_npw_flag, 'Y', 'Y', 'N') current_contractor
	  ,decode(papf.sex,null,null,hr_general.decode_lookup('SEX',papf.sex)) sex
      ,papf.attribute_category attribute_category
      ,papf.attribute1 attribute1
      ,xxas_com_vset_pkg.decode_descr_vset_value
         ('800'
         ,'PER_PEOPLE'
         ,papf.attribute_category
         ,'ATTRIBUTE1'
         ,papf.attribute1)                 attribute1_meaning
      ,papf.attribute2 attribute2
      ,xxas_com_vset_pkg.decode_descr_vset_value
         ('800'
         ,'PER_PEOPLE'
         ,papf.attribute_category
         ,'ATTRIBUTE2'
         ,papf.attribute2)                 attribute2_meaning
      ,papf.attribute3	attribute3
      ,xxas_com_vset_pkg.decode_descr_vset_value
         ('800'
         ,'PER_PEOPLE'
         ,papf.attribute_category
         ,'ATTRIBUTE3'
         ,papf.attribute3)                 attribute3_meaning
      ,papf.attribute4	attribute4
      ,xxas_com_vset_pkg.decode_descr_vset_value
         ('800'
         ,'PER_PEOPLE'
         ,papf.attribute_category
         ,'ATTRIBUTE4'
         ,papf.attribute4)                 attribute4_meaning
      ,papf.attribute5	attribute5
      ,xxas_com_vset_pkg.decode_descr_vset_value
         ('800'
         ,'PER_PEOPLE'
         ,papf.attribute_category
         ,'ATTRIBUTE5'
         ,papf.attribute5)                 attribute5_meaning
      ,papf.attribute6	attribute6
      ,xxas_com_vset_pkg.decode_descr_vset_value
         ('800'
         ,'PER_PEOPLE'
         ,papf.attribute_category
         ,'ATTRIBUTE6'
         ,papf.attribute6)                 attribute6_meaning
      ,papf.attribute7	attribute7
      ,xxas_com_vset_pkg.decode_descr_vset_value
         ('800'
         ,'PER_PEOPLE'
         ,papf.attribute_category
         ,'ATTRIBUTE7'
         ,papf.attribute7)                 attribute7_meaning
      ,papf.attribute8	attribute8
      ,xxas_com_vset_pkg.decode_descr_vset_value
         ('800'
         ,'PER_PEOPLE'
         ,papf.attribute_category
         ,'ATTRIBUTE8'
         ,papf.attribute8)                 attribute8_meaning
      ,papf.attribute9	attribute9
      ,xxas_com_vset_pkg.decode_descr_vset_value
         ('800'
         ,'PER_PEOPLE'
         ,papf.attribute_category
         ,'ATTRIBUTE9'
         ,papf.attribute9)                 attribute9_meaning
      ,papf.attribute10	attribute10
      ,xxas_com_vset_pkg.decode_descr_vset_value
         ('800'
         ,'PER_PEOPLE'
         ,papf.attribute_category
         ,'ATTRIBUTE10'
         ,papf.attribute10)                 attribute10_meaning
      ,papf.attribute11	attribute11
      ,xxas_com_vset_pkg.decode_descr_vset_value
         ('800'
         ,'PER_PEOPLE'
         ,papf.attribute_category
         ,'ATTRIBUTE11'
         ,papf.attribute11)                 attribute11_meaning
      ,papf.attribute12	attribute12
      ,xxas_com_vset_pkg.decode_descr_vset_value
         ('800'
         ,'PER_PEOPLE'
         ,papf.attribute_category
         ,'ATTRIBUTE12'
         ,papf.attribute12)                 attribute12_meaning
      ,papf.attribute13	attribute13
      ,xxas_com_vset_pkg.decode_descr_vset_value
         ('800'
         ,'PER_PEOPLE'
         ,papf.attribute_category
         ,'ATTRIBUTE13'
         ,papf.attribute13)                 attribute13_meaning
      ,papf.attribute14	attribute14
      ,xxas_com_vset_pkg.decode_descr_vset_value
         ('800'
         ,'PER_PEOPLE'
         ,papf.attribute_category
         ,'ATTRIBUTE14'
         ,papf.attribute14)                 attribute14_meaning
      ,papf.attribute15	attribute15
      ,xxas_com_vset_pkg.decode_descr_vset_value
         ('800'
         ,'PER_PEOPLE'
         ,papf.attribute_category
         ,'ATTRIBUTE15'
         ,papf.attribute15)                 attribute15_meaning
      ,papf.attribute16	attribute16
      ,xxas_com_vset_pkg.decode_descr_vset_value
         ('800'
         ,'PER_PEOPLE'
         ,papf.attribute_category
         ,'ATTRIBUTE16'
         ,papf.attribute16)                 attribute16_meaning
      ,papf.attribute17	attribute17
      ,xxas_com_vset_pkg.decode_descr_vset_value
         ('800'
         ,'PER_PEOPLE'
         ,papf.attribute_category
         ,'ATTRIBUTE17'
         ,papf.attribute17)                 attribute17_meaning
      ,papf.attribute18	attribute18
      ,xxas_com_vset_pkg.decode_descr_vset_value
         ('800'
         ,'PER_PEOPLE'
         ,papf.attribute_category
         ,'ATTRIBUTE18'
         ,papf.attribute18)                 attribute18_meaning
      ,papf.attribute19	attribute19
      ,xxas_com_vset_pkg.decode_descr_vset_value
         ('800'
         ,'PER_PEOPLE'
         ,papf.attribute_category
         ,'ATTRIBUTE19'
         ,papf.attribute19)                 attribute19_meaning
      ,papf.attribute20	attribute20
      ,xxas_com_vset_pkg.decode_descr_vset_value
         ('800'
         ,'PER_PEOPLE'
         ,papf.attribute_category
         ,'ATTRIBUTE20'
         ,papf.attribute20)                 attribute20_meaning
      ,papf.attribute21	attribute21
      ,xxas_com_vset_pkg.decode_descr_vset_value
         ('800'
         ,'PER_PEOPLE'
         ,papf.attribute_category
         ,'ATTRIBUTE21'
         ,papf.attribute21)                 attribute21_meaning
      ,papf.attribute22	attribute22
      ,xxas_com_vset_pkg.decode_descr_vset_value
         ('800'
         ,'PER_PEOPLE'
         ,papf.attribute_category
         ,'ATTRIBUTE22'
         ,papf.attribute22)                 attribute22_meaning
      ,papf.attribute23	attribute23
      ,xxas_com_vset_pkg.decode_descr_vset_value
         ('800'
         ,'PER_PEOPLE'
         ,papf.attribute_category
         ,'ATTRIBUTE23'
         ,papf.attribute23)                 attribute23_meaning
      ,papf.attribute24	attribute24
      ,xxas_com_vset_pkg.decode_descr_vset_value
         ('800'
         ,'PER_PEOPLE'
         ,papf.attribute_category
         ,'ATTRIBUTE24'
         ,papf.attribute24)                 attribute24_meaning
      ,papf.attribute25	attribute25
      ,xxas_com_vset_pkg.decode_descr_vset_value
         ('800'
         ,'PER_PEOPLE'
         ,papf.attribute_category
         ,'ATTRIBUTE25'
         ,papf.attribute25)                 attribute25_meaning
      ,papf.attribute26	attribute26
      ,xxas_com_vset_pkg.decode_descr_vset_value
         ('800'
         ,'PER_PEOPLE'
         ,papf.attribute_category
         ,'ATTRIBUTE26'
         ,papf.attribute26)                 attribute26_meaning
      ,papf.attribute27	attribute27
      ,xxas_com_vset_pkg.decode_descr_vset_value
         ('800'
         ,'PER_PEOPLE'
         ,papf.attribute_category
         ,'ATTRIBUTE27'
         ,papf.attribute27)                 attribute27_meaning
      ,papf.attribute28	attribute28
      ,xxas_com_vset_pkg.decode_descr_vset_value
         ('800'
         ,'PER_PEOPLE'
         ,papf.attribute_category
         ,'ATTRIBUTE28'
         ,papf.attribute28)                 attribute28_meaning
      ,papf.attribute29	attribute29
      ,xxas_com_vset_pkg.decode_descr_vset_value
         ('800'
         ,'PER_PEOPLE'
         ,papf.attribute_category
         ,'ATTRIBUTE29'
         ,papf.attribute29)                 attribute29_meaning
      ,papf.attribute30	attribute30
      ,xxas_com_vset_pkg.decode_descr_vset_value
         ('800'
         ,'PER_PEOPLE'
         ,papf.attribute_category
         ,'ATTRIBUTE30'
         ,papf.attribute30)                 attribute30_meaning
      ,papf.per_information_category per_information_category
      ,papf.per_information1 per_information1
      ,xxas_com_vset_pkg.decode_descr_vset_value
         ('800'
         ,'Person Developer DF'
         ,papf.per_information_category
         ,'PER_INFORMATION1'
         ,papf.per_information1)                 per_information1_meaning
      ,papf.per_information2 per_information2
      ,xxas_com_vset_pkg.decode_descr_vset_value
         ('800'
         ,'Person Developer DF'
         ,papf.per_information_category
         ,'PER_INFORMATION2'
         ,papf.per_information2)                 per_information2_meaning
      ,papf.per_information3	per_information3
      ,xxas_com_vset_pkg.decode_descr_vset_value
         ('800'
         ,'Person Developer DF'
         ,papf.per_information_category
         ,'PER_INFORMATION3'
         ,papf.per_information3)                 per_information3_meaning
      ,papf.per_information4	per_information4
      ,xxas_com_vset_pkg.decode_descr_vset_value
         ('800'
         ,'Person Developer DF'
         ,papf.per_information_category
         ,'PER_INFORMATION4'
         ,papf.per_information4)                 per_information4_meaning
      ,papf.per_information5	per_information5
      ,xxas_com_vset_pkg.decode_descr_vset_value
         ('800'
         ,'Person Developer DF'
         ,papf.per_information_category
         ,'PER_INFORMATION5'
         ,papf.per_information5)                 per_information5_meaning
      ,papf.per_information6	per_information6
      ,xxas_com_vset_pkg.decode_descr_vset_value
         ('800'
         ,'Person Developer DF'
         ,papf.per_information_category
         ,'PER_INFORMATION6'
         ,papf.per_information6)                 per_information6_meaning
      ,papf.per_information7	per_information7
      ,xxas_com_vset_pkg.decode_descr_vset_value
         ('800'
         ,'Person Developer DF'
         ,papf.per_information_category
         ,'PER_INFORMATION7'
         ,papf.per_information7)                 per_information7_meaning
      ,papf.per_information8	per_information8
      ,xxas_com_vset_pkg.decode_descr_vset_value
         ('800'
         ,'Person Developer DF'
         ,papf.per_information_category
         ,'PER_INFORMATION8'
         ,papf.per_information8)                 per_information8_meaning
      ,papf.per_information9	per_information9
      ,xxas_com_vset_pkg.decode_descr_vset_value
         ('800'
         ,'Person Developer DF'
         ,papf.per_information_category
         ,'PER_INFORMATION9'
         ,papf.per_information9)                 per_information9_meaning
      ,papf.per_information10	per_information10
      ,xxas_com_vset_pkg.decode_descr_vset_value
         ('800'
         ,'Person Developer DF'
         ,papf.per_information_category
         ,'PER_INFORMATION10'
         ,papf.per_information10)                 per_information10_meaning
      ,papf.per_information11	per_information11
      ,xxas_com_vset_pkg.decode_descr_vset_value
         ('800'
         ,'Person Developer DF'
         ,papf.per_information_category
         ,'PER_INFORMATION11'
         ,papf.per_information11)                 per_information11_meaning
      ,papf.per_information12	per_information12
      ,xxas_com_vset_pkg.decode_descr_vset_value
         ('800'
         ,'Person Developer DF'
         ,papf.per_information_category
         ,'PER_INFORMATION12'
         ,papf.per_information12)                 per_information12_meaning
      ,papf.per_information13	per_information13
      ,xxas_com_vset_pkg.decode_descr_vset_value
         ('800'
         ,'Person Developer DF'
         ,papf.per_information_category
         ,'PER_INFORMATION13'
         ,papf.per_information13)                 per_information13_meaning
      ,papf.per_information14	per_information14
      ,xxas_com_vset_pkg.decode_descr_vset_value
         ('800'
         ,'Person Developer DF'
         ,papf.per_information_category
         ,'PER_INFORMATION14'
         ,papf.per_information14)                 per_information14_meaning
      ,papf.per_information15	per_information15
      ,xxas_com_vset_pkg.decode_descr_vset_value
         ('800'
         ,'Person Developer DF'
         ,papf.per_information_category
         ,'PER_INFORMATION15'
         ,papf.per_information15)                 per_information15_meaning
      ,papf.per_information16	per_information16
      ,xxas_com_vset_pkg.decode_descr_vset_value
         ('800'
         ,'Person Developer DF'
         ,papf.per_information_category
         ,'PER_INFORMATION16'
         ,papf.per_information16)                 per_information16_meaning
      ,papf.per_information17	per_information17
      ,xxas_com_vset_pkg.decode_descr_vset_value
         ('800'
         ,'Person Developer DF'
         ,papf.per_information_category
         ,'PER_INFORMATION17'
         ,papf.per_information17)                 per_information17_meaning
      ,papf.per_information18	per_information18
      ,xxas_com_vset_pkg.decode_descr_vset_value
         ('800'
         ,'Person Developer DF'
         ,papf.per_information_category
         ,'PER_INFORMATION18'
         ,papf.per_information18)                 per_information18_meaning
      ,papf.per_information19	per_information19
      ,xxas_com_vset_pkg.decode_descr_vset_value
         ('800'
         ,'Person Developer DF'
         ,papf.per_information_category
         ,'PER_INFORMATION19'
         ,papf.per_information19)                 per_information19_meaning
      ,papf.per_information20	per_information20
      ,xxas_com_vset_pkg.decode_descr_vset_value
         ('800'
         ,'Person Developer DF'
         ,papf.per_information_category
         ,'PER_INFORMATION20'
         ,papf.per_information20)                 per_information20_meaning
      ,papf.per_information21	per_information21
      ,xxas_com_vset_pkg.decode_descr_vset_value
         ('800'
         ,'Person Developer DF'
         ,papf.per_information_category
         ,'PER_INFORMATION21'
         ,papf.per_information21)                 per_information21_meaning
      ,papf.per_information22	per_information22
      ,xxas_com_vset_pkg.decode_descr_vset_value
         ('800'
         ,'Person Developer DF'
         ,papf.per_information_category
         ,'PER_INFORMATION22'
         ,papf.per_information22)                 per_information22_meaning
      ,papf.per_information23	per_information23
      ,xxas_com_vset_pkg.decode_descr_vset_value
         ('800'
         ,'Person Developer DF'
         ,papf.per_information_category
         ,'PER_INFORMATION23'
         ,papf.per_information23)                 per_information23_meaning
      ,papf.per_information24	per_information24
      ,xxas_com_vset_pkg.decode_descr_vset_value
         ('800'
         ,'Person Developer DF'
         ,papf.per_information_category
         ,'PER_INFORMATION24'
         ,papf.per_information24)                 per_information24_meaning
      ,papf.per_information25	per_information25
      ,xxas_com_vset_pkg.decode_descr_vset_value
         ('800'
         ,'Person Developer DF'
         ,papf.per_information_category
         ,'PER_INFORMATION25'
         ,papf.per_information25)                 per_information25_meaning
      ,papf.per_information26	per_information26
      ,xxas_com_vset_pkg.decode_descr_vset_value
         ('800'
         ,'Person Developer DF'
         ,papf.per_information_category
         ,'PER_INFORMATION26'
         ,papf.per_information26)                 per_information26_meaning
      ,papf.per_information27	per_information27
      ,xxas_com_vset_pkg.decode_descr_vset_value
         ('800'
         ,'Person Developer DF'
         ,papf.per_information_category
         ,'PER_INFORMATION27'
         ,papf.per_information27)                 per_information27_meaning
      ,papf.per_information28	per_information28
      ,xxas_com_vset_pkg.decode_descr_vset_value
         ('800'
         ,'Person Developer DF'
         ,papf.per_information_category
         ,'PER_INFORMATION28'
         ,papf.per_information28)                 per_information28_meaning
      ,papf.per_information29	per_information29
      ,xxas_com_vset_pkg.decode_descr_vset_value
         ('800'
         ,'Person Developer DF'
         ,papf.per_information_category
         ,'PER_INFORMATION29'
         ,papf.per_information29)                 per_information29_meaning
      ,papf.per_information30	per_information30
      ,xxas_com_vset_pkg.decode_descr_vset_value
         ('800'
         ,'Person Developer DF'
         ,papf.per_information_category
         ,'PER_INFORMATION30'
         ,papf.per_information30)                 per_information30_meaning
      ,xxas_applicant_pkg.get_applicant_rank
         (paaf.applicant_rank, paaf.ass_attribute1, paaf.ass_attribute2, paaf.ass_attribute3
         ,paaf.ass_attribute4, paaf.ass_attribute5, paaf.ass_attribute6, paaf.ass_attribute7
         ,paaf.ass_attribute8, paaf.ass_attribute9, paaf.ass_attribute10, paaf.ass_attribute11
         ,paaf.ass_attribute12, paaf.ass_attribute13, paaf.ass_attribute14, paaf.ass_attribute15
         ,paaf.ass_attribute16, paaf.ass_attribute17, paaf.ass_attribute18, paaf.ass_attribute19
         ,paaf.ass_attribute20, paaf.ass_attribute21, paaf.ass_attribute22, paaf.ass_attribute23
         ,paaf.ass_attribute24, paaf.ass_attribute25, paaf.ass_attribute26, paaf.ass_attribute27
         ,paaf.ass_attribute28, paaf.ass_attribute29, paaf.ass_attribute30) applicant_rank
      ,xxas_applicant_pkg.get_interested
         (paaf.ass_attribute1, paaf.ass_attribute2, paaf.ass_attribute3
         ,paaf.ass_attribute4, paaf.ass_attribute5, paaf.ass_attribute6, paaf.ass_attribute7
         ,paaf.ass_attribute8, paaf.ass_attribute9, paaf.ass_attribute10, paaf.ass_attribute11
         ,paaf.ass_attribute12, paaf.ass_attribute13, paaf.ass_attribute14, paaf.ass_attribute15
         ,paaf.ass_attribute16, paaf.ass_attribute17, paaf.ass_attribute18, paaf.ass_attribute19
         ,paaf.ass_attribute20, paaf.ass_attribute21, paaf.ass_attribute22, paaf.ass_attribute23
         ,paaf.ass_attribute24, paaf.ass_attribute25, paaf.ass_attribute26, paaf.ass_attribute27
         ,paaf.ass_attribute28, paaf.ass_attribute29, paaf.ass_attribute30) interested
      ,addr.style
      ,addr.address_type address_type
      ,addr.address_line1 address_line1
      ,addr.address_line2 address_line2
      ,addr.address_line3 address_line3
      ,addr.town_or_city town_or_city
      ,addr.region_1 region_1
      ,addr.region_2 region_2
      ,addr.region_3 region_3
      ,addr.postal_code postal_code
      ,addr.country country
      ,addr.derived_locale derived_locale
      ,(SELECT lat.y
        FROM   TABLE(sdo_util.getvertices(addr.geometry)) lat) latitude
      ,(SELECT lon.x
        FROM   TABLE(sdo_util.getvertices(addr.geometry)) lon) longtitude
      ,addr.town_or_city || ', ' || region_1 address
      ,phn.phone_number
      ,to_char
        (irc_skills_matching_pkg.vacancy_match_percent
          (inp.person_id, vac.vacancy_id, trunc(sysdate))) as match_percent
      ,(SELECT COUNT(*)
        FROM   per_all_assignments_f paaf2
              ,per_all_people_f papf2
        WHERE  paaf2.assignment_type = 'A'
        AND    trunc(sysdate) BETWEEN
               paaf2.effective_start_date AND paaf2.effective_end_date
        AND    paaf2.person_id = papf2.person_id
        AND    paaf2.effective_start_date BETWEEN
               papf2.effective_start_date AND papf2.effective_end_date
        AND    papf2.party_id = papf.party_id
        HAVING COUNT(*) > 0) as jobs_applied_for
      ,(SELECT MIN(paaf2.effective_start_date)
        FROM   per_all_assignments_f paaf2
        WHERE  paaf2.assignment_id = paaf.assignment_id) as last_application
      ,past.assignment_status_type_id
      ,past.per_system_status system_status
      ,past.system_meaning system_meaning
      ,past.customer_status assignment_status
      ,doc.document_id
      ,doc.file_name
      ,pov.vendor_id
      ,pov.vendor_name
      ,regatmpt.attempt_id reg_attempt_id
      ,to_number(decode(regatmpt.raw_score, -1000, null, regatmpt.raw_score)) reg_attempt_score
      ,nvl2(regatmpt.mastery_score
           ,decode
             (greatest(regatmpt.mastery_score, regatmpt.raw_score)
             ,regatmpt.raw_score
             ,'P'
             ,'F')
           ,nvl2
             (fnd_profile.value('IRC_REGISTER_TEST')
             ,nvl2
               (inp.attempt_id
               ,'A'
               ,'N')
             ,null)) reg_attempt_status
      ,aplatmpt.attempt_id apl_attempt_id
      ,to_number
        (decode
          (aplatmpt.raw_score
          ,-1000
          ,null
          ,aplatmpt.raw_score)) apl_attempt_score
      ,nvl2
        (aplatmpt.mastery_score
        ,decode
          (greatest(aplatmpt.mastery_score, aplatmpt.raw_score)
          ,aplatmpt.raw_score
          ,'P'
          ,'F')
      ,nvl2
        (vac.assessment_id
        ,nvl2
          (iad.attempt_id
          ,'A'
          ,'N')
        ,null)) apl_attempt_status
      ,iad.qualified
      ,iad.assignment_details_id
      ,iad.considered
      /*,xxas_util_pkg.decode_lookup('REC_TYPE', iri.source_type) source_type
      ,decode
        (iri.source_type
        ,'ER'
        ,(SELECT xxas_util_pkg.get_formatted_name
                   (papfr.full_name, papfr.global_name, papfr.local_name
                   ,papfr.first_name, papfr.last_name, papfr.known_as)
          FROM   per_all_people_f papfr
          WHERE  papfr.person_id = iri.source_person_id
          AND    trunc(sysdate) BETWEEN
                 papfr.effective_start_date and papfr.effective_end_date)
        ,iri.source_name) as source_name */
      ,CASE WHEN paaf.effective_end_date < trunc(sysdate) THEN
            'Y'
            ELSE 'N'
       END is_terminated
      ,xxas_util_pkg.decode_lookup('APL_ASSIGN_REASON', ias.status_change_reason) status_change_reason
      ,nvl2(iof.offer_status, 'Y', 'N') has_offer
      ,iof.offer_status offer_status_code
      ,xxas_util_pkg.decode_lookup('IRC_OFFER_STATUSES', iof.offer_status) offer_status_meaning
      /*,ias.status_change_comments */
      ,ias.status_change_date
      ,CASE
         WHEN EXISTS (SELECT NULL
                      FROM   per_all_assignments_f paaf4
                      WHERE  paaf4.vacancy_id = paaf.vacancy_id
                      AND    paaf4.person_id = paaf.person_id
                      AND    paaf4.assignment_type = 'E')
        THEN 'Y'
        ELSE 'N'
       END is_hired
	  --work preferences columns begin
	  ,isc.keywords
	  ,fnd_number.canonical_to_number(isc.distance_to_location) distance_to_location_int
	  ,decode(isc.travel_percentage, NULL, NULL, xxas_util_pkg.decode_lookup('IRC_TRAVEL_PERCENTAGE',isc.travel_percentage)) amount_of_travel
	  ,decode(isc.work_at_home, NULL, NULL, xxas_util_pkg.decode_lookup('IRC_WORK_AT_HOME',isc.work_at_home)) work_from_home
	  ,decode(isc.employee, NULL, NULL, xxas_util_pkg.decode_lookup('YES_NO',isc.employee)) employee_display
	  ,decode(isc.contractor, NULL, NULL, xxas_util_pkg.decode_lookup('YES_NO',isc.contractor)) contractor_display
	  ,xxas_applicant_pkg.get_applicant_prof_area_pref(isc.search_criteria_id) professional_area_str
	  ,isc.location
	  ,decode(isc.employment_category, NULL, NULL, xxas_util_pkg.decode_lookup('IRC_EMP_CAT',isc.employment_category)) emp_category
	  ,isc.min_salary
	  ,isc.salary_currency currency
	  ,decode(isc.match_competence, NULL, NULL, xxas_util_pkg.decode_lookup('YES_NO',isc.match_competence)) match_competencies
	  ,decode(isc.match_qualification, NULL, NULL, xxas_util_pkg.decode_lookup('YES_NO',isc.match_qualification)) match_qualifications
      ,isc.attribute_category isc_attribute_category
	  ,isc.attribute1 isc_attribute1
	  ,xxas_com_vset_pkg.decode_descr_vset_value ('800','IRC_SEARCH_CRITERIA',isc.attribute_category,'ATTRIBUTE1',isc.attribute1) isc_attribute1_meaning
	  ,isc.attribute2 isc_attribute2
	  ,xxas_com_vset_pkg.decode_descr_vset_value ('800','IRC_SEARCH_CRITERIA',isc.attribute_category,'ATTRIBUTE2',isc.attribute2) isc_attribute2_meaning
	  ,isc.attribute3 isc_attribute3
	  ,xxas_com_vset_pkg.decode_descr_vset_value ('800','IRC_SEARCH_CRITERIA',isc.attribute_category,'ATTRIBUTE3',isc.attribute3) isc_attribute3_meaning
	  ,isc.attribute4 isc_attribute4
	  ,xxas_com_vset_pkg.decode_descr_vset_value ('800','IRC_SEARCH_CRITERIA',isc.attribute_category,'ATTRIBUTE4',isc.attribute4) isc_attribute4_meaning
	  ,isc.attribute5 isc_attribute5
	  ,xxas_com_vset_pkg.decode_descr_vset_value ('800','IRC_SEARCH_CRITERIA',isc.attribute_category,'ATTRIBUTE5',isc.attribute5) isc_attribute5_meaning
	  ,isc.attribute6 isc_attribute6
	  ,xxas_com_vset_pkg.decode_descr_vset_value ('800','IRC_SEARCH_CRITERIA',isc.attribute_category,'ATTRIBUTE6',isc.attribute6) isc_attribute6_meaning
	  ,isc.attribute7 isc_attribute7
	  ,xxas_com_vset_pkg.decode_descr_vset_value ('800','IRC_SEARCH_CRITERIA',isc.attribute_category,'ATTRIBUTE7',isc.attribute7) isc_attribute7_meaning
	  ,isc.attribute8 isc_attribute8
	  ,xxas_com_vset_pkg.decode_descr_vset_value ('800','IRC_SEARCH_CRITERIA',isc.attribute_category,'ATTRIBUTE8',isc.attribute8) isc_attribute8_meaning
	  ,isc.attribute9 isc_attribute9
	  ,xxas_com_vset_pkg.decode_descr_vset_value ('800','IRC_SEARCH_CRITERIA',isc.attribute_category,'ATTRIBUTE9',isc.attribute9) isc_attribute9_meaning
	  ,isc.attribute10 isc_attribute10
	  ,xxas_com_vset_pkg.decode_descr_vset_value ('800','IRC_SEARCH_CRITERIA',isc.attribute_category,'ATTRIBUTE10',isc.attribute10) isc_attribute10_meaning
	  ,isc.attribute11 isc_attribute11
	  ,xxas_com_vset_pkg.decode_descr_vset_value ('800','IRC_SEARCH_CRITERIA',isc.attribute_category,'ATTRIBUTE11',isc.attribute11) isc_attribute11_meaning
	  ,isc.attribute12 isc_attribute12
	  ,xxas_com_vset_pkg.decode_descr_vset_value ('800','IRC_SEARCH_CRITERIA',isc.attribute_category,'ATTRIBUTE12',isc.attribute12) isc_attribute12_meaning
	  ,isc.attribute13 isc_attribute13
	  ,xxas_com_vset_pkg.decode_descr_vset_value ('800','IRC_SEARCH_CRITERIA',isc.attribute_category,'ATTRIBUTE13',isc.attribute13) isc_attribute13_meaning
	  ,isc.attribute14 isc_attribute14
	  ,xxas_com_vset_pkg.decode_descr_vset_value ('800','IRC_SEARCH_CRITERIA',isc.attribute_category,'ATTRIBUTE14',isc.attribute14) isc_attribute14_meaning
	  ,isc.attribute15 isc_attribute15
	  ,xxas_com_vset_pkg.decode_descr_vset_value ('800','IRC_SEARCH_CRITERIA',isc.attribute_category,'ATTRIBUTE15',isc.attribute15) isc_attribute15_meaning
	  ,isc.attribute16 isc_attribute16
	  ,xxas_com_vset_pkg.decode_descr_vset_value ('800','IRC_SEARCH_CRITERIA',isc.attribute_category,'ATTRIBUTE16',isc.attribute16) isc_attribute16_meaning
	  ,isc.attribute17 isc_attribute17
	  ,xxas_com_vset_pkg.decode_descr_vset_value ('800','IRC_SEARCH_CRITERIA',isc.attribute_category,'ATTRIBUTE17',isc.attribute17) isc_attribute17_meaning
	  ,isc.attribute18 isc_attribute18
	  ,xxas_com_vset_pkg.decode_descr_vset_value ('800','IRC_SEARCH_CRITERIA',isc.attribute_category,'ATTRIBUTE18',isc.attribute18) isc_attribute18_meaning
	  ,isc.attribute19 isc_attribute19
	  ,xxas_com_vset_pkg.decode_descr_vset_value ('800','IRC_SEARCH_CRITERIA',isc.attribute_category,'ATTRIBUTE19',isc.attribute19) isc_attribute19_meaning
	  ,isc.attribute20 isc_attribute20
	  ,xxas_com_vset_pkg.decode_descr_vset_value ('800','IRC_SEARCH_CRITERIA',isc.attribute_category,'ATTRIBUTE20',isc.attribute20) isc_attribute20_meaning
	  ,isc.attribute21 isc_attribute21
	  ,xxas_com_vset_pkg.decode_descr_vset_value ('800','IRC_SEARCH_CRITERIA',isc.attribute_category,'ATTRIBUTE21',isc.attribute21) isc_attribute21_meaning
	  ,isc.attribute22 isc_attribute22
	  ,xxas_com_vset_pkg.decode_descr_vset_value ('800','IRC_SEARCH_CRITERIA',isc.attribute_category,'ATTRIBUTE22',isc.attribute22) isc_attribute22_meaning
	  ,isc.attribute23 isc_attribute23
	  ,xxas_com_vset_pkg.decode_descr_vset_value ('800','IRC_SEARCH_CRITERIA',isc.attribute_category,'ATTRIBUTE23',isc.attribute23) isc_attribute23_meaning
	  ,isc.attribute24 isc_attribute24
	  ,xxas_com_vset_pkg.decode_descr_vset_value ('800','IRC_SEARCH_CRITERIA',isc.attribute_category,'ATTRIBUTE24',isc.attribute24) isc_attribute24_meaning
	  ,isc.attribute25 isc_attribute25
	  ,xxas_com_vset_pkg.decode_descr_vset_value ('800','IRC_SEARCH_CRITERIA',isc.attribute_category,'ATTRIBUTE25',isc.attribute25) isc_attribute25_meaning
	  ,isc.attribute26 isc_attribute26
	  ,xxas_com_vset_pkg.decode_descr_vset_value ('800','IRC_SEARCH_CRITERIA',isc.attribute_category,'ATTRIBUTE26',isc.attribute26) isc_attribute26_meaning
	  ,isc.attribute27 isc_attribute27
	  ,xxas_com_vset_pkg.decode_descr_vset_value ('800','IRC_SEARCH_CRITERIA',isc.attribute_category,'ATTRIBUTE27',isc.attribute27) isc_attribute27_meaning
	  ,isc.attribute28 isc_attribute28
	  ,xxas_com_vset_pkg.decode_descr_vset_value ('800','IRC_SEARCH_CRITERIA',isc.attribute_category,'ATTRIBUTE28',isc.attribute28) isc_attribute28_meaning
	  ,isc.attribute29 isc_attribute29
	  ,xxas_com_vset_pkg.decode_descr_vset_value ('800','IRC_SEARCH_CRITERIA',isc.attribute_category,'ATTRIBUTE29',isc.attribute29) isc_attribute29_meaning
	  ,isc.attribute30 isc_attribute30
	  ,xxas_com_vset_pkg.decode_descr_vset_value ('800','IRC_SEARCH_CRITERIA',isc.attribute_category,'ATTRIBUTE30',isc.attribute30) isc_attribute30_meaning
	  --work preferences columns end
	  ,(IRC_UTILITIES_PKG.GET_CURRENT_EMPLOYER_PTY( papf.party_id, TRUNC(sysdate))) AS PREVIOUS_EMPLOYER
	  ,(IRC_UTILITIES_PKG.GET_LAST_QUAL_PTY( papf.party_id, TRUNC(sysdate)))        AS LAST_QUAL
	  ,decode(papf.CORRESPONDENCE_LANGUAGE,null,null,hr_general.decode_lookup('PQP_LANGUAGE',papf.CORRESPONDENCE_LANGUAGE)) as correspondence_language
	  ,DECODE(irc_utilities_pkg.is_open_party(papf.PARTY_ID, TRUNC(sysdate)), 'TRUE',
              (SELECT COUNT(*)
               FROM PER_ALL_ASSIGNMENTS_F PAF2,
                    PER_ALL_PEOPLE_F PPF2
               WHERE papf.PARTY_ID = PPF2.PARTY_ID
               AND   TRUNC(sysdate) BETWEEN PPF2.EFFECTIVE_START_DATE AND PPF2.EFFECTIVE_END_DATE
               AND   PAF2.ASSIGNMENT_TYPE = 'A'
               AND   TRUNC(sysdate) BETWEEN PAF2.EFFECTIVE_START_DATE AND PAF2.EFFECTIVE_END_DATE
               AND   PAF2.person_id = PPF2.person_id
              ), 0) AS ACTIVE_APPLICATIONS_COUNT
FROM   per_all_people_f papf
      ,xxas_per_thumbnails img
      ,irc_notification_preferences inp
      ,per_all_assignments_f paaf
      ,per_all_vacancies vac
      ,per_addresses addr
      ,per_phones phn
      ,irc_assignment_statuses ias
      ,xxas_assignment_status_types_v past
      ,irc_documents doc
      ,po_vendors pov
      ,ota_attempts regatmpt
      ,ota_attempts aplatmpt
      ,irc_assignment_details_f iad
      /*,irc_referral_info iri */
      ,irc_offers iof
      ,irc_search_criteria isc
      ,per_business_groups_perf bg
WHERE  papf.person_id = paaf.person_id
AND    paaf.assignment_type = 'A'
AND    trunc(sysdate) BETWEEN
       papf.effective_start_date AND papf.effective_end_date
AND    paaf.effective_start_date =
         (SELECT max(paaf2.effective_start_date)
          FROM   per_all_assignments_f paaf2
          WHERE  paaf2.assignment_id = paaf.assignment_id
          AND    paaf2.assignment_type = 'A')
AND    paaf.business_group_id = bg.business_group_id
AND    papf.person_id = img.person_id(+)
AND    vac.vacancy_id = paaf.vacancy_id
AND    irc_utilities_pkg.get_recruitment_person_id
         (papf.person_id, trunc(sysdate)) = inp.person_id(+)
AND    inp.agency_id = pov.vendor_id(+)
AND    inp.person_id = addr.person_id(+)
AND    nvl(inp.address_id
          ,decode(addr.address_type(+),'REC', addr.address_id(+), -1)) = addr.address_id(+)
AND    trunc(sysdate) BETWEEN
       addr.date_from(+) AND nvl(addr.date_to(+), trunc(sysdate))
AND    inp.person_id = phn.parent_id(+)
AND    phn.parent_table(+) = 'PER_ALL_PEOPLE_F'
AND    phn.phone_type(+) = xxas_applicant_pkg.get_appl_phone_type(inp.person_id)
AND    trunc(sysdate) BETWEEN
       nvl(phn.date_from(+), trunc(sysdate)) AND nvl(phn.date_to(+), trunc(sysdate))
/*1103*/
AND  (phn.phone_id  =
      (
      SELECT max(phone_id) from per_phones phn1
       WHERE   phn1.parent_id = inp.person_id
       AND    phn1.parent_table = 'PER_ALL_PEOPLE_F'
       AND    phn1.phone_type(+) = xxas_applicant_pkg.get_appl_phone_type(inp.person_id)
       AND    trunc(sysdate) BETWEEN
       nvl(phn1.date_from(+), trunc(sysdate)) AND nvl(phn1.date_to(+), trunc(sysdate))
      )
      OR
      NOT EXISTS(SELECT 1 from per_phones phn1
       WHERE   phn1.parent_id = inp.person_id
       AND    phn1.parent_table = 'PER_ALL_PEOPLE_F'
       AND    phn1.phone_type(+) = xxas_applicant_pkg.get_appl_phone_type(inp.person_id)
       AND    trunc(sysdate) BETWEEN
       nvl(phn1.date_from(+), trunc(sysdate)) AND nvl(phn1.date_to(+), trunc(sysdate))))
/*1103*/
AND    paaf.assignment_id = ias.assignment_id
AND    ias.assignment_status_type_id = past.assignment_status_type_id(+)
AND    nvl(past.business_group_id, paaf.business_group_id) = paaf.business_group_id
AND    nvl(past.legislation_code, bg.legislation_code) = bg.legislation_code
AND NOT EXISTS
         (SELECT null
          FROM   irc_assignment_statuses ias2
          WHERE  ias2.assignment_id = paaf.assignment_id
          AND    trunc(ias2.status_change_date) > trunc(ias.status_change_date))
AND    ias.last_update_date =
         (SELECT max(ias3.last_update_date)
          FROM   irc_assignment_statuses ias3
          WHERE  ias3.assignment_id = paaf.assignment_id
          AND    trunc(ias3.status_change_date) = trunc(ias.status_change_date))
AND    inp.person_id = doc.person_id(+)
AND    doc.type(+) LIKE '%RESUME'
AND    doc.end_date(+) IS NULL
AND    nvl(doc.document_id, 1) =
       nvl
        ((SELECT MAX(idoc1.document_id)
          FROM   irc_documents idoc1
          WHERE  idoc1.person_id = inp.person_id
          AND    idoc1.type IN('RESUME', 'AUTO_RESUME')
          AND    idoc1.end_date IS NULL
          AND    last_update_date =
            (SELECT max(idoc2.last_update_date)
             FROM   irc_documents idoc2
             WHERE  idoc2.person_id = inp.person_id
             AND    idoc2.type IN('RESUME','AUTO_RESUME')
             AND    idoc2.end_date IS NULL))
        ,1)
AND    inp.attempt_id = regatmpt.attempt_id(+)
AND    paaf.assignment_id = iad.assignment_id(+)
AND    trunc(sysdate) BETWEEN
       iad.effective_start_date(+) AND iad.effective_end_date(+)
AND    iad.latest_details(+) = 'Y'
AND    iad.attempt_id = aplatmpt.attempt_id(+)
AND    iof.latest_offer(+) = 'Y'
AND    iof.applicant_assignment_id(+) = paaf.assignment_id
AND	   isc.object_id(+) = papf.person_id
AND	   isc.object_type(+) = 'WPREF'
/*AND    paaf.assignment_id = iri.object_id(+)
AND    iri.object_type(+) = 'APPLICATION'
AND    trunc(sysdate) BETWEEN
       iri.start_date(+) AND iri.end_date(+) */;

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

COMMIT;
EXIT;