/* $Id$ */
/*============================================================================*
 |                 Copyright (c) 2013 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_per_work_preference_v 
AS
SELECT iwp.SEARCH_CRITERIA_ID,
  iwp.OBJECT_ID,
  iwp.OBJECT_TYPE,
  iwp.LOCATION LOCATION,
  iwp.DISTANCE_TO_LOCATION DISTANCE_TO_LOCATION,
  iwp.EMPLOYEE EMPLOYEE,
  iwp.CONTRACTOR CONTRACTOR,
  iwp.EMPLOYMENT_CATEGORY EMPLOYMENT_CATEGORY,
  iwp.KEYWORDS KEYWORDS,
  iwp.TRAVEL_PERCENTAGE TRAVEL_PERCENTAGE,
  iwp.MIN_SALARY MIN_SALARY,
  iwp.MAX_SALARY MAX_SALARY,
  iwp.SALARY_CURRENCY CURRENCY,
  iwp.SALARY_PERIOD SALARY_PERIOD,
  iwp.MATCH_COMPETENCE MATCH_COMPETENCE,
  iwp.MATCH_QUALIFICATION MATCH_QUALIFICATION,
  iwp.JOB_TITLE JOB_TITLE,
  iwp.DEPARTMENT DEPARTMENT,
  iwp.WORK_AT_HOME WORK_AT_HOME,
  iwp.MIN_QUAL_LEVEL MIN_QUAL_LEVEL,
  iwp.MAX_QUAL_LEVEL MAX_QUAL_LEVEL,
  iwp.DESCRIPTION,
  iwp.ATTRIBUTE_CATEGORY isc_attribute_category,
  iwp.ATTRIBUTE1 isc_attribute1,
  xxas_com_vset_pkg.decode_descr_vset_value ('800','IRC_SEARCH_CRITERIA',iwp.attribute_category,'ATTRIBUTE1',iwp.attribute1) isc_attribute1_meaning ,
  iwp.ATTRIBUTE2 isc_attribute2,
  xxas_com_vset_pkg.decode_descr_vset_value ('800','IRC_SEARCH_CRITERIA',iwp.attribute_category,'ATTRIBUTE2',iwp.attribute2) isc_attribute2_meaning ,
  iwp.ATTRIBUTE3 isc_attribute3,
  xxas_com_vset_pkg.decode_descr_vset_value ('800','IRC_SEARCH_CRITERIA',iwp.attribute_category,'ATTRIBUTE3',iwp.attribute3) isc_attribute3_meaning ,
  iwp.attribute4 isc_attribute4,
  xxas_com_vset_pkg.decode_descr_vset_value ('800','IRC_SEARCH_CRITERIA',iwp.attribute_category,'ATTRIBUTE4',iwp.attribute4) isc_attribute4_meaning ,
  iwp.attribute5 isc_attribute5,
  xxas_com_vset_pkg.decode_descr_vset_value ('800','IRC_SEARCH_CRITERIA',iwp.attribute_category,'ATTRIBUTE5',iwp.attribute5) isc_attribute5_meaning ,
  iwp.attribute6 isc_attribute6,
  xxas_com_vset_pkg.decode_descr_vset_value ('800','IRC_SEARCH_CRITERIA',iwp.attribute_category,'ATTRIBUTE6',iwp.attribute6) isc_attribute6_meaning ,
  iwp.attribute7 isc_attribute7,
  xxas_com_vset_pkg.decode_descr_vset_value ('800','IRC_SEARCH_CRITERIA',iwp.attribute_category,'ATTRIBUTE7',iwp.attribute7) isc_attribute7_meaning ,
  iwp.attribute8 isc_attribute8,
  xxas_com_vset_pkg.decode_descr_vset_value ('800','IRC_SEARCH_CRITERIA',iwp.attribute_category,'ATTRIBUTE8',iwp.attribute8) isc_attribute8_meaning ,
  iwp.attribute9 isc_attribute9,
  xxas_com_vset_pkg.decode_descr_vset_value ('800','IRC_SEARCH_CRITERIA',iwp.attribute_category,'ATTRIBUTE9',iwp.attribute9) isc_attribute9_meaning ,
  iwp.attribute10 isc_attribute10,
  xxas_com_vset_pkg.decode_descr_vset_value ('800','IRC_SEARCH_CRITERIA',iwp.attribute_category,'ATTRIBUTE10',iwp.attribute10) isc_attribute10_meaning ,
  iwp.attribute11 isc_attribute11,
  xxas_com_vset_pkg.decode_descr_vset_value ('800','IRC_SEARCH_CRITERIA',iwp.attribute_category,'ATTRIBUTE11',iwp.attribute11) isc_attribute11_meaning ,
  iwp.attribute12 isc_attribute12,
  xxas_com_vset_pkg.decode_descr_vset_value ('800','IRC_SEARCH_CRITERIA',iwp.attribute_category,'ATTRIBUTE12',iwp.attribute12) isc_attribute12_meaning ,
  iwp.attribute13 isc_attribute13,
  xxas_com_vset_pkg.decode_descr_vset_value ('800','IRC_SEARCH_CRITERIA',iwp.attribute_category,'ATTRIBUTE13',iwp.attribute13) isc_attribute13_meaning ,
  iwp.attribute14 isc_attribute14,
  xxas_com_vset_pkg.decode_descr_vset_value ('800','IRC_SEARCH_CRITERIA',iwp.attribute_category,'ATTRIBUTE14',iwp.attribute14) isc_attribute14_meaning ,
  iwp.attribute15 isc_attribute15,
  xxas_com_vset_pkg.decode_descr_vset_value ('800','IRC_SEARCH_CRITERIA',iwp.attribute_category,'ATTRIBUTE15',iwp.attribute15) isc_attribute15_meaning ,
  iwp.attribute16 isc_attribute16,
  xxas_com_vset_pkg.decode_descr_vset_value ('800','IRC_SEARCH_CRITERIA',iwp.attribute_category,'ATTRIBUTE16',iwp.attribute16) isc_attribute16_meaning ,
  iwp.attribute17 isc_attribute17,
  xxas_com_vset_pkg.decode_descr_vset_value ('800','IRC_SEARCH_CRITERIA',iwp.attribute_category,'ATTRIBUTE17',iwp.attribute17) isc_attribute17_meaning ,
  iwp.attribute18 isc_attribute18,
  xxas_com_vset_pkg.decode_descr_vset_value ('800','IRC_SEARCH_CRITERIA',iwp.attribute_category,'ATTRIBUTE18',iwp.attribute18) isc_attribute18_meaning ,
  iwp.attribute19 isc_attribute19,
  xxas_com_vset_pkg.decode_descr_vset_value ('800','IRC_SEARCH_CRITERIA',iwp.attribute_category,'ATTRIBUTE19',iwp.attribute19) isc_attribute19_meaning ,
  iwp.attribute20 isc_attribute20,
  xxas_com_vset_pkg.decode_descr_vset_value ('800','IRC_SEARCH_CRITERIA',iwp.attribute_category,'ATTRIBUTE20',iwp.attribute20) isc_attribute20_meaning ,
  iwp.attribute21 isc_attribute21,
  xxas_com_vset_pkg.decode_descr_vset_value ('800','IRC_SEARCH_CRITERIA',iwp.attribute_category,'ATTRIBUTE21',iwp.attribute21) isc_attribute21_meaning ,
  iwp.attribute22 isc_attribute22,
  xxas_com_vset_pkg.decode_descr_vset_value ('800','IRC_SEARCH_CRITERIA',iwp.attribute_category,'ATTRIBUTE22',iwp.attribute22) isc_attribute22_meaning ,
  iwp.attribute23 isc_attribute23,
  xxas_com_vset_pkg.decode_descr_vset_value ('800','IRC_SEARCH_CRITERIA',iwp.attribute_category,'ATTRIBUTE23',iwp.attribute23) isc_attribute23_meaning ,
  iwp.attribute24 isc_attribute24,
  xxas_com_vset_pkg.decode_descr_vset_value ('800','IRC_SEARCH_CRITERIA',iwp.attribute_category,'ATTRIBUTE24',iwp.attribute24) isc_attribute24_meaning ,
  iwp.attribute25 isc_attribute25,
  xxas_com_vset_pkg.decode_descr_vset_value ('800','IRC_SEARCH_CRITERIA',iwp.attribute_category,'ATTRIBUTE25',iwp.attribute25) isc_attribute25_meaning ,
  iwp.attribute26 isc_attribute26,
  xxas_com_vset_pkg.decode_descr_vset_value ('800','IRC_SEARCH_CRITERIA',iwp.attribute_category,'ATTRIBUTE26',iwp.attribute26) isc_attribute26_meaning ,
  iwp.attribute27 isc_attribute27,
  xxas_com_vset_pkg.decode_descr_vset_value ('800','IRC_SEARCH_CRITERIA',iwp.attribute_category,'ATTRIBUTE27',iwp.attribute27) isc_attribute27_meaning ,
  iwp.attribute28 isc_attribute28,
  xxas_com_vset_pkg.decode_descr_vset_value ('800','IRC_SEARCH_CRITERIA',iwp.attribute_category,'ATTRIBUTE28',iwp.attribute28) isc_attribute28_meaning ,
  iwp.attribute29 isc_attribute29,
  xxas_com_vset_pkg.decode_descr_vset_value ('800','IRC_SEARCH_CRITERIA',iwp.attribute_category,'ATTRIBUTE29',iwp.attribute29) isc_attribute29_meaning ,
  iwp.attribute30 isc_attribute30,
  xxas_com_vset_pkg.decode_descr_vset_value ('800','IRC_SEARCH_CRITERIA',iwp.attribute_category,'ATTRIBUTE30',iwp.attribute30) isc_attribute30_meaning ,
  iwp.ISC_INFORMATION_CATEGORY ,
  iwp.ISC_INFORMATION1 ,
  xxas_com_vset_pkg.decode_descr_vset_value ('800','IRC_SEARCH_CRITERIA_DDF',iwp.isc_information_category,'ISC_INFORMATION1',iwp.isc_information1) isc_information1_meaning ,
  iwp.ISC_INFORMATION2 ,
  xxas_com_vset_pkg.decode_descr_vset_value ('800','IRC_SEARCH_CRITERIA_DDF',iwp.isc_information_category,'ISC_INFORMATION2',iwp.isc_information2) isc_information2_meaning ,
  iwp.ISC_INFORMATION3 ,
  xxas_com_vset_pkg.decode_descr_vset_value ('800','IRC_SEARCH_CRITERIA_DDF',iwp.isc_information_category,'ISC_INFORMATION3',iwp.isc_information3) isc_information3_meaning ,
  iwp.ISC_INFORMATION4 ,
  xxas_com_vset_pkg.decode_descr_vset_value ('800','IRC_SEARCH_CRITERIA_DDF',iwp.isc_information_category,'ISC_INFORMATION4',iwp.isc_information4) isc_information4_meaning ,
  iwp.ISC_INFORMATION5 ,
  xxas_com_vset_pkg.decode_descr_vset_value ('800','IRC_SEARCH_CRITERIA_DDF',iwp.isc_information_category,'ISC_INFORMATION5',iwp.isc_information5) isc_information5_meaning ,
  iwp.ISC_INFORMATION6 ,
  xxas_com_vset_pkg.decode_descr_vset_value ('800','IRC_SEARCH_CRITERIA_DDF',iwp.isc_information_category,'ISC_INFORMATION6',iwp.isc_information6) isc_information6_meaning ,
  iwp.ISC_INFORMATION7 ,
  xxas_com_vset_pkg.decode_descr_vset_value ('800','IRC_SEARCH_CRITERIA_DDF',iwp.isc_information_category,'ISC_INFORMATION7',iwp.isc_information7) isc_information7_meaning ,
  iwp.ISC_INFORMATION8 ,
  xxas_com_vset_pkg.decode_descr_vset_value ('800','IRC_SEARCH_CRITERIA_DDF',iwp.isc_information_category,'ISC_INFORMATION8',iwp.isc_information8) isc_information8_meaning ,
  iwp.ISC_INFORMATION9 ,
  xxas_com_vset_pkg.decode_descr_vset_value ('800','IRC_SEARCH_CRITERIA_DDF',iwp.isc_information_category,'ISC_INFORMATION9',iwp.isc_information9) isc_information9_meaning ,
  iwp.ISC_INFORMATION10 ,
  xxas_com_vset_pkg.decode_descr_vset_value ('800','IRC_SEARCH_CRITERIA_DDF',iwp.isc_information_category,'ISC_INFORMATION10',iwp.isc_information10) isc_information10_meaning ,
  iwp.ISC_INFORMATION11 ,
  xxas_com_vset_pkg.decode_descr_vset_value ('800','IRC_SEARCH_CRITERIA_DDF',iwp.isc_information_category,'ISC_INFORMATION11',iwp.isc_information11) isc_information11_meaning ,
  iwp.ISC_INFORMATION12 ,
  xxas_com_vset_pkg.decode_descr_vset_value ('800','IRC_SEARCH_CRITERIA_DDF',iwp.isc_information_category,'ISC_INFORMATION12',iwp.isc_information12) isc_information12_meaning ,
  iwp.ISC_INFORMATION13 ,
  xxas_com_vset_pkg.decode_descr_vset_value ('800','IRC_SEARCH_CRITERIA_DDF',iwp.isc_information_category,'ISC_INFORMATION13',iwp.isc_information13) isc_information13_meaning ,
  iwp.ISC_INFORMATION14 ,
  xxas_com_vset_pkg.decode_descr_vset_value ('800','IRC_SEARCH_CRITERIA_DDF',iwp.isc_information_category,'ISC_INFORMATION14',iwp.isc_information14) isc_information14_meaning ,
  iwp.ISC_INFORMATION15 ,
  xxas_com_vset_pkg.decode_descr_vset_value ('800','IRC_SEARCH_CRITERIA_DDF',iwp.isc_information_category,'ISC_INFORMATION15',iwp.isc_information15) isc_information15_meaning ,
  iwp.ISC_INFORMATION16 ,
  xxas_com_vset_pkg.decode_descr_vset_value ('800','IRC_SEARCH_CRITERIA_DDF',iwp.isc_information_category,'ISC_INFORMATION16',iwp.isc_information16) isc_information16_meaning ,
  iwp.ISC_INFORMATION17 ,
  xxas_com_vset_pkg.decode_descr_vset_value ('800','IRC_SEARCH_CRITERIA_DDF',iwp.isc_information_category,'ISC_INFORMATION17',iwp.isc_information17) isc_information17_meaning ,
  iwp.ISC_INFORMATION18 ,
  xxas_com_vset_pkg.decode_descr_vset_value ('800','IRC_SEARCH_CRITERIA_DDF',iwp.isc_information_category,'ISC_INFORMATION18',iwp.isc_information18) isc_information18_meaning ,
  iwp.ISC_INFORMATION19 ,
  xxas_com_vset_pkg.decode_descr_vset_value ('800','IRC_SEARCH_CRITERIA_DDF',iwp.isc_information_category,'ISC_INFORMATION19',iwp.isc_information19) isc_information19_meaning ,
  iwp.ISC_INFORMATION20 ,
  xxas_com_vset_pkg.decode_descr_vset_value ('800','IRC_SEARCH_CRITERIA_DDF',iwp.isc_information_category,'ISC_INFORMATION20',iwp.isc_information20) isc_information20_meaning ,
  iwp.ISC_INFORMATION21 ,
  xxas_com_vset_pkg.decode_descr_vset_value ('800','IRC_SEARCH_CRITERIA_DDF',iwp.isc_information_category,'ISC_INFORMATION21',iwp.isc_information21) isc_information21_meaning ,
  iwp.ISC_INFORMATION22 ,
  xxas_com_vset_pkg.decode_descr_vset_value ('800','IRC_SEARCH_CRITERIA_DDF',iwp.isc_information_category,'ISC_INFORMATION22',iwp.isc_information22) isc_information22_meaning ,
  iwp.ISC_INFORMATION23 ,
  xxas_com_vset_pkg.decode_descr_vset_value ('800','IRC_SEARCH_CRITERIA_DDF',iwp.isc_information_category,'ISC_INFORMATION23',iwp.isc_information23) isc_information23_meaning ,
  iwp.ISC_INFORMATION24 ,
  xxas_com_vset_pkg.decode_descr_vset_value ('800','IRC_SEARCH_CRITERIA_DDF',iwp.isc_information_category,'ISC_INFORMATION24',iwp.isc_information24) isc_information24_meaning ,
  iwp.ISC_INFORMATION25 ,
  xxas_com_vset_pkg.decode_descr_vset_value ('800','IRC_SEARCH_CRITERIA_DDF',iwp.isc_information_category,'ISC_INFORMATION25',iwp.isc_information25) isc_information25_meaning ,
  iwp.ISC_INFORMATION26 ,
  xxas_com_vset_pkg.decode_descr_vset_value ('800','IRC_SEARCH_CRITERIA_DDF',iwp.isc_information_category,'ISC_INFORMATION26',iwp.isc_information26) isc_information26_meaning ,
  iwp.ISC_INFORMATION27 ,
  xxas_com_vset_pkg.decode_descr_vset_value ('800','IRC_SEARCH_CRITERIA_DDF',iwp.isc_information_category,'ISC_INFORMATION27',iwp.isc_information27) isc_information27_meaning ,
  iwp.ISC_INFORMATION28 ,
  xxas_com_vset_pkg.decode_descr_vset_value ('800','IRC_SEARCH_CRITERIA_DDF',iwp.isc_information_category,'ISC_INFORMATION28',iwp.isc_information28) isc_information28_meaning ,
  iwp.ISC_INFORMATION29 ,
  xxas_com_vset_pkg.decode_descr_vset_value ('800','IRC_SEARCH_CRITERIA_DDF',iwp.isc_information_category,'ISC_INFORMATION29',iwp.isc_information29) isc_information29_meaning ,
  iwp.ISC_INFORMATION30 ,
  xxas_com_vset_pkg.decode_descr_vset_value ('800','IRC_SEARCH_CRITERIA_DDF',iwp.isc_information_category,'ISC_INFORMATION30',iwp.isc_information30) isc_information30_meaning,
  iwp.LAST_UPDATE_DATE,
  iwp.LAST_UPDATED_BY,
  iwp.LAST_UPDATE_LOGIN,
  iwp.CREATED_BY,
  iwp.CREATION_DATE,
  iwp.OBJECT_VERSION_NUMBER,
  iwp.GEOCODE_LOCATION,
  iwp.GEOCODE_COUNTRY,
  iwp.LOCATION_ID,
  iwp.geometry.sdo_point.x AS LONGITUDE,
  iwp.geometry.sdo_point.y AS LATITUDE,
  emp.MEANING,
  emp.LOOKUP_TYPE,
  emp.LOOKUP_CODE,
  cont.MEANING         AS MEANING1,
  cont.LOOKUP_TYPE     AS LOOKUP_TYPE1,
  cont.LOOKUP_CODE     AS LOOKUP_CODE1,
  comp.MEANING         AS MEANING2,
  comp.LOOKUP_TYPE     AS LOOKUP_TYPE2,
  comp.LOOKUP_CODE     AS LOOKUP_CODE2,
  qual.MEANING         AS MEANING3,
  qual.LOOKUP_TYPE     AS LOOKUP_TYPE3,
  qual.LOOKUP_CODE     AS LOOKUP_CODE3,
  EmpCat.MEANING       AS MEANING5,
  EmpCat.LOOKUP_TYPE   AS LOOKUP_TYPE5,
  EmpCat.LOOKUP_CODE   AS LOOKUP_CODE5,
  trv.MEANING          AS MEANING6,
  trv.LOOKUP_TYPE      AS LOOKUP_TYPE6,
  trv.LOOKUP_CODE      AS LOOKUP_CODE6,
  wah.MEANING          AS MEANING7,
  wah.LOOKUP_TYPE      AS LOOKUP_TYPE7,
  wah.LOOKUP_CODE      AS LOOKUP_CODE7,
  iwp.GEOCODE_LOCATION AS OldGeocodeLocation,
  loc.LOCATION_CODE,
  loc.LOCATION_ID                                                         AS LOCATION_ID1,
  xxas_applicant_pkg.get_applicant_prof_area_pref(iwp.search_criteria_id) AS PROFESSIONAL_AREA_STR,
  fnd_number.canonical_to_number(distance_to_location)                    AS DISTANCE_TO_LOCATION_INT,
  wah.Meaning                                                             AS WORK_FROM_HOME,
  trv.Meaning                                                             AS AMOUNT_OF_TRAVEL,
  EmpCat.Meaning                                                          AS EMP_CATEGORY,
  comp.Meaning                                                            AS MATCH_COMPETENCIES,
  qual.Meaning                                                            AS MATCH_QUALIFICATIONS,
  emp.Meaning                                                             AS EMPLOYEE_DISPLAY,
  cont.Meaning                                                            AS CONTRACTOR_DISPLAY
FROM IRC_SEARCH_CRITERIA iwp,
  HR_LOOKUPS emp,
  HR_LOOKUPS cont,
  HR_LOOKUPS comp,
  HR_LOOKUPS qual,
  HR_LOOKUPS EmpCat,
  HR_LOOKUPS trv,
  HR_LOOKUPS wah,
  HR_LOCATIONS_ALL_VL loc
WHERE iwp.object_type       ='WPREF'
AND emp.LOOKUP_TYPE(+)      = 'YES_NO'
AND iwp.EMPLOYEE            = emp.LOOKUP_CODE(+)
AND cont.LOOKUP_TYPE(+)     = 'YES_NO'
AND iwp.CONTRACTOR          = cont.LOOKUP_CODE(+)
AND comp.LOOKUP_TYPE(+)     = 'YES_NO'
AND iwp.MATCH_COMPETENCE    = comp.LOOKUP_CODE(+)
AND qual.LOOKUP_TYPE(+)     = 'YES_NO'
AND iwp.MATCH_QUALIFICATION = qual.LOOKUP_CODE(+)
AND EmpCat.LOOKUP_TYPE(+)   = 'IRC_EMP_CAT'
AND iwp.EMPLOYMENT_CATEGORY = EmpCat.LOOKUP_CODE(+)
AND trv.LOOKUP_TYPE(+)      = 'IRC_TRAVEL_PERCENTAGE'
AND iwp.TRAVEL_PERCENTAGE   = trv.LOOKUP_CODE(+)
AND wah.LOOKUP_TYPE(+)      = 'IRC_WORK_AT_HOME'
AND iwp.WORK_AT_HOME        = wah.LOOKUP_CODE(+)
AND iwp.location_id         =loc.location_id(+);

BEGIN
  xxas_fnd_dictionary_pkg.add_view
   (p_application_short_name => xxas_util_pkg.app
   ,p_view_name              => 'XXAS_PER_WORK_PREFERENCE_V'
   ,p_description            => 'Applaud Person Work Preference');
END;
/
  
COMMIT;
EXIT;