/* $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_applicants_v
AS
SELECT a.assignment_id
           ,a.person_id
           ,a.person_name
           ,a.business_group_id
		   ,a.vacancy_id
           ,a.phone_number --contact2_text
           ,a.email_address --contact3_text
           ,a.style
           ,a.address_type
           ,a.address_line1
           ,a.address_line2
           ,a.address_line3
           ,a.town_or_city
           ,a.region_1
           ,a.region_2
           ,a.region_3
           ,a.postal_code
           ,a.country
           ,a.sex
           ,a.attribute_category
           ,a.attribute1
           ,a.attribute1_meaning
           ,a.attribute2
           ,a.attribute2_meaning
           ,a.attribute3
           ,a.attribute3_meaning
           ,a.attribute4
           ,a.attribute4_meaning
           ,a.attribute5
           ,a.attribute5_meaning
           ,a.attribute6
           ,a.attribute6_meaning
           ,a.attribute7
           ,a.attribute7_meaning
           ,a.attribute8
           ,a.attribute8_meaning
           ,a.attribute9
           ,a.attribute9_meaning
           ,a.attribute10
           ,a.attribute10_meaning
           ,a.attribute11
           ,a.attribute11_meaning
           ,a.attribute12
           ,a.attribute12_meaning
           ,a.attribute13
           ,a.attribute13_meaning
           ,a.attribute14
           ,a.attribute14_meaning
           ,a.attribute15
           ,a.attribute15_meaning
           ,a.attribute16
           ,a.attribute16_meaning
           ,a.attribute17
           ,a.attribute17_meaning
           ,a.attribute18
           ,a.attribute18_meaning
           ,a.attribute19
           ,a.attribute19_meaning
           ,a.attribute20
           ,a.attribute20_meaning
           ,a.attribute21
           ,a.attribute21_meaning
           ,a.attribute22
           ,a.attribute22_meaning
           ,a.attribute23
           ,a.attribute23_meaning
           ,a.attribute24
           ,a.attribute24_meaning
           ,a.attribute25
           ,a.attribute25_meaning
           ,a.attribute26
           ,a.attribute26_meaning
           ,a.attribute27
           ,a.attribute27_meaning
           ,a.attribute28
           ,a.attribute28_meaning
           ,a.attribute29
           ,a.attribute29_meaning
           ,a.attribute30
           ,a.attribute30_meaning
           ,a.per_information_category
           ,a.per_information1
           ,a.per_information1_meaning
           ,a.per_information2
           ,a.per_information2_meaning
           ,a.per_information3
           ,a.per_information3_meaning
           ,a.per_information4
           ,a.per_information4_meaning
           ,a.per_information5
           ,a.per_information5_meaning
           ,a.per_information6
           ,a.per_information6_meaning
           ,a.per_information7
           ,a.per_information7_meaning
           ,a.per_information8
           ,a.per_information8_meaning
           ,a.per_information9
           ,a.per_information9_meaning
           ,a.per_information10
           ,a.per_information10_meaning
           ,a.per_information11
           ,a.per_information11_meaning
           ,a.per_information12
           ,a.per_information12_meaning
           ,a.per_information13
           ,a.per_information13_meaning
           ,a.per_information14
           ,a.per_information14_meaning
           ,a.per_information15
           ,a.per_information15_meaning
           ,a.per_information16
           ,a.per_information16_meaning
           ,a.per_information17
           ,a.per_information17_meaning
           ,a.per_information18
           ,a.per_information18_meaning
           ,a.per_information19
           ,a.per_information19_meaning
           ,a.per_information20
           ,a.per_information20_meaning
           ,a.per_information21
           ,a.per_information21_meaning
           ,a.per_information22
           ,a.per_information22_meaning
           ,a.per_information23
           ,a.per_information23_meaning
           ,a.per_information24
           ,a.per_information24_meaning
           ,a.per_information25
           ,a.per_information25_meaning
           ,a.per_information26
           ,a.per_information26_meaning
           ,a.per_information27
           ,a.per_information27_meaning
           ,a.per_information28
           ,a.per_information28_meaning
           ,a.per_information29
           ,a.per_information29_meaning
           ,a.per_information30
           ,a.per_information30_meaning
           ,a.applicant_rank
           ,a.interested
		   ,decode(a.has_offer, 'N'
                         ,decode(xxas_com_sec_pkg.person_on_rec_team_edit_flag(a.vacancy_id,'Y','Y'),'Y',
                                 xxas_com_sec_pkg.test_instance('XXAS_APL_CREATE_OFFER_PERM', a.assignment_status_type_id),'N')
                          ) offer_perm
           ,a.assignment_status --current_status
           ,a.previous_employer
           ,a.last_qual
           ,a.correspondence_language
           ,a.active_applications_count
           ,cast
             (multiset
               (SELECT xxas_per_qualification
                         (q.establishment
                         ,q.address
                         ,q.start_date
                         ,q.end_date
                         ,null -- degree_level
                         ,null -- major
                         ,null -- minor
                         ,null -- location
                         ,null -- grade_point_average
                         ,null -- credits_earned
                         ,null -- graduation_date
                         ,q.qualification_name -- degree
                         ,q.title -- major_subject
                         ,q.awarded_date -- date_received
                         ,q.grade_attained)
                FROM   xxas_per_qualifications_v q
                WHERE  q.person_id = a.person_id
                AND    q.education_or_award = 'EDUCATION'
               )
             AS xxas_per_qualification_tbl) qualifications
           ,cast
             (multiset
               (SELECT xxas_per_addl_qual
                         (q.pq_category_meaning	-- CATEGORY
                         ,q.qualification_name		-- TYPE
                         ,q.title								-- QUALIFICATION
                         ,q.grade_attained				-- GRADE/LEVEL
                         ,q.awarded_date					-- DATE RECEIVED
                         ,q.tuition_method				-- HOW RECEIVED
                         )
                FROM   xxas_per_qualifications_v q 
                WHERE  q.person_id = a.person_id
                AND    q.education_or_award = 'AWARDS'
                AND    q.pq_category_code <> 'DT'
               )
             AS xxas_per_addl_qual_tbl) addl_qualifications
           ,cast
             (multiset
               (SELECT xxas_per_competencies
                         (c.skill_level
                         ,c.skill_name
                         )
                FROM   xxas_per_competencies_v c
                WHERE  c.person_id = a.person_id
               )
             AS xxas_per_competencies_tbl) competencies
		   ,cast
             (multiset
               (SELECT xxas_per_previous_employers
                         (null
                         ,null
                         ,null
                         ,substr(p.prev_empr_name || ' (' || p.prev_empr_address || '), '|| p.prev_empr_job_name || ' ( ' || to_char(p.prev_empr_start_date,'DD-MON-YYYY')
						 || decode(to_char(p.prev_empr_end_date,'DD-MON-YYYY'),null, ') ', ' - '|| to_char(p.prev_empr_end_date,'DD-MON-YYYY') || ' )' ),0,255)
                         ,null
                         ,null
                         ,p.prev_empr_duties)
                FROM   xxas_per_previous_employers_v p
                WHERE  p.person_id = a.person_id
               )
             AS xxas_per_prev_employers_tbl) previous_employers			 
		   ,cast
             (multiset
               (SELECT xxas_per_assessment
                         (assmnt.question
						 ,assmnt.user_response)
                FROM   xxas_per_assessments_v assmnt
                WHERE  assmnt.attempt_id = a.apl_attempt_id
               )
             AS xxas_per_assessment_tbl) assessments
           ,cast	
             (multiset
               (SELECT xxas_per_jobs_considered
                         (j.vacancy_name
                         ,j.organization_name
                         ,j.last_updated_date
                         ,j.current_consideration)
                FROM   xxas_per_jobs_considered_v j
                WHERE  j.person_id = a.person_id
               )
             AS xxas_per_jobs_considered_tbl) jobs_considered_for
           ,cast	
             (multiset
               (SELECT xxas_apl_other_job_appls
                         (o.vacancy_name
                         ,o.job_name
                         ,o.position_name
                         ,o.organization_name
                         ,o.location_name
                         ,o.status
                         ,o.date_applied)
                FROM xxas_appl_other_job_appls_v o
                WHERE o.person_id  =  a.person_id
                AND   o.vacancy_id <> a.vacancy_id
               )
             AS xxas_apl_other_job_appls_tbl)  other_job_applications
           ,cast	
             (multiset
               (SELECT xxas_apl_phone_numbers
                         (p.phone_type_display
                         ,p.phone_number_display
                         ,p.times_meaning_display)
                FROM xxas_appl_phone_numbers_v p
                WHERE p.parent_id =  a.person_id
               )
             AS xxas_apl_phone_numbers_tbl) phone_numbers
			 -- work preferences begin			 
           ,a.keywords
           ,a.distance_to_location_int
           ,a.amount_of_travel
           ,a.work_from_home
           ,a.employee_display
           ,a.contractor_display
           ,a.professional_area_str -- professional area TODO
           ,a.location
           ,a.emp_category
           ,a.min_salary
           ,a.currency
           ,a.match_competencies
           ,a.match_qualifications
			 -- work preferences end
           ,a.isc_attribute_category
           ,a.isc_attribute1
           ,a.isc_attribute1_meaning
           ,a.isc_attribute2
           ,a.isc_attribute2_meaning
           ,a.isc_attribute3
           ,a.isc_attribute3_meaning
           ,a.isc_attribute4
           ,a.isc_attribute4_meaning
           ,a.isc_attribute5
           ,a.isc_attribute5_meaning
           ,a.isc_attribute6
           ,a.isc_attribute6_meaning
           ,a.isc_attribute7
           ,a.isc_attribute7_meaning
           ,a.isc_attribute8
           ,a.isc_attribute8_meaning
           ,a.isc_attribute9
           ,a.isc_attribute9_meaning
           ,a.isc_attribute10
           ,a.isc_attribute10_meaning
           ,a.isc_attribute11
           ,a.isc_attribute11_meaning
           ,a.isc_attribute12
           ,a.isc_attribute12_meaning
           ,a.isc_attribute13
           ,a.isc_attribute13_meaning
           ,a.isc_attribute14
           ,a.isc_attribute14_meaning
           ,a.isc_attribute15
           ,a.isc_attribute15_meaning
           ,a.isc_attribute16
           ,a.isc_attribute16_meaning
           ,a.isc_attribute17
           ,a.isc_attribute17_meaning
           ,a.isc_attribute18
           ,a.isc_attribute18_meaning
           ,a.isc_attribute19
           ,a.isc_attribute19_meaning
           ,a.isc_attribute20
           ,a.isc_attribute20_meaning
           ,a.isc_attribute21
           ,a.isc_attribute21_meaning
           ,a.isc_attribute22
           ,a.isc_attribute22_meaning
           ,a.isc_attribute23
           ,a.isc_attribute23_meaning
           ,a.isc_attribute24
           ,a.isc_attribute24_meaning
           ,a.isc_attribute25
           ,a.isc_attribute25_meaning
           ,a.isc_attribute26
           ,a.isc_attribute26_meaning
           ,a.isc_attribute27
           ,a.isc_attribute27_meaning
           ,a.isc_attribute28
           ,a.isc_attribute28_meaning
           ,a.isc_attribute29
           ,a.isc_attribute29_meaning
           ,a.isc_attribute30
           ,a.isc_attribute30_meaning
           ,cast
             (multiset 
               (SELECT xxas_per_asg_history
                         (asg_history.assignment_id
						 ,asg_history.assignment_status_id
						 ,asg_history.status_change_date
						 ,asg_history.status_change_reason
						 ,asg_history.assignment_status
						 ,asg_history.assignment_status_type_id
						 ,asg_history.external_status)
                FROM   xxas_per_asg_history_v asg_history
                WHERE  asg_history.assignment_id = a.assignment_id
               )
             AS xxas_per_asg_history_tbl) assignment_history
  FROM   xxas_vacancy_applicants_v a;

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

COMMIT;
EXIT;