/* $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_per_competencies_v 
AS
 SELECT pce_party.person_id
      , perratinglevelseo.name skill_level
      , percompetenceseo.name skill_name
      , percompetenceseo.rating_scale_id
      , pce_party.attribute_category
      , 'N' competencetypeswitch
      , pce_party.type
      , pce_party.effective_date_from pce_effective_date_from
      , nvl(pce_party.effective_date_to,trunc(sysdate)) pce_effective_date_to
      , percompetenceseo.date_from
      , percompetenceseo.date_to
      , percompetenceseo.evaluation_method
      , percompetenceseo.description
      , pce_party.position_id
      , pce_party.competence_id
      , pce_party.mandatory
      ,pce_party.attribute1
      ,xxas_com_vset_pkg.decode_descr_vset_value
         ('800','PER_COMPETENCE_ELEMENTS',pce_party.attribute_category,'ATTRIBUTE1',pce_party.attribute1) attribute1_meaning
      ,pce_party.attribute2
      ,xxas_com_vset_pkg.decode_descr_vset_value
         ('800','PER_COMPETENCE_ELEMENTS',pce_party.attribute_category,'ATTRIBUTE2',pce_party.attribute2) attribute2_meaning
      ,pce_party.attribute3
      ,xxas_com_vset_pkg.decode_descr_vset_value
         ('800','PER_COMPETENCE_ELEMENTS',pce_party.attribute_category,'ATTRIBUTE3',pce_party.attribute3) attribute3_meaning
      ,pce_party.attribute4
      ,xxas_com_vset_pkg.decode_descr_vset_value
         ('800','PER_COMPETENCE_ELEMENTS',pce_party.attribute_category,'ATTRIBUTE4',pce_party.attribute4) attribute4_meaning
      ,pce_party.attribute5
      ,xxas_com_vset_pkg.decode_descr_vset_value
         ('800','PER_COMPETENCE_ELEMENTS',pce_party.attribute_category,'ATTRIBUTE5',pce_party.attribute5) attribute5_meaning
      ,pce_party.attribute6
      ,xxas_com_vset_pkg.decode_descr_vset_value
         ('800','PER_COMPETENCE_ELEMENTS',pce_party.attribute_category,'ATTRIBUTE6',pce_party.attribute6) attribute6_meaning
      ,pce_party.attribute7
      ,xxas_com_vset_pkg.decode_descr_vset_value
         ('800','PER_COMPETENCE_ELEMENTS',pce_party.attribute_category,'ATTRIBUTE7',pce_party.attribute7) attribute7_meaning
      ,pce_party.attribute8
      ,xxas_com_vset_pkg.decode_descr_vset_value
         ('800','PER_COMPETENCE_ELEMENTS',pce_party.attribute_category,'ATTRIBUTE8',pce_party.attribute8) attribute8_meaning
      ,pce_party.attribute9
      ,xxas_com_vset_pkg.decode_descr_vset_value
         ('800','PER_COMPETENCE_ELEMENTS',pce_party.attribute_category,'ATTRIBUTE9',pce_party.attribute9) attribute9_meaning
      ,pce_party.attribute10
      ,xxas_com_vset_pkg.decode_descr_vset_value
         ('800','PER_COMPETENCE_ELEMENTS',pce_party.attribute_category,'ATTRIBUTE10',pce_party.attribute10) attribute10_meaning
      ,pce_party.attribute11
      ,xxas_com_vset_pkg.decode_descr_vset_value
         ('800','PER_COMPETENCE_ELEMENTS',pce_party.attribute_category,'ATTRIBUTE11',pce_party.attribute11) attribute11_meaning
      ,pce_party.attribute12
      ,xxas_com_vset_pkg.decode_descr_vset_value
         ('800','PER_COMPETENCE_ELEMENTS',pce_party.attribute_category,'ATTRIBUTE12',pce_party.attribute12) attribute12_meaning
      ,pce_party.attribute13
      ,xxas_com_vset_pkg.decode_descr_vset_value
         ('800','PER_COMPETENCE_ELEMENTS',pce_party.attribute_category,'ATTRIBUTE13',pce_party.attribute13) attribute13_meaning
      ,pce_party.attribute14
      ,xxas_com_vset_pkg.decode_descr_vset_value
         ('800','PER_COMPETENCE_ELEMENTS',pce_party.attribute_category,'ATTRIBUTE14',pce_party.attribute14) attribute14_meaning
      ,pce_party.attribute15
      ,xxas_com_vset_pkg.decode_descr_vset_value
         ('800','PER_COMPETENCE_ELEMENTS',pce_party.attribute_category,'ATTRIBUTE15',pce_party.attribute15) attribute15_meaning
      ,pce_party.attribute16
      ,xxas_com_vset_pkg.decode_descr_vset_value
         ('800','PER_COMPETENCE_ELEMENTS',pce_party.attribute_category,'ATTRIBUTE16',pce_party.attribute16) attribute16_meaning
      ,pce_party.attribute17
      ,xxas_com_vset_pkg.decode_descr_vset_value
         ('800','PER_COMPETENCE_ELEMENTS',pce_party.attribute_category,'ATTRIBUTE17',pce_party.attribute17) attribute17_meaning
      ,pce_party.attribute18
      ,xxas_com_vset_pkg.decode_descr_vset_value
         ('800','PER_COMPETENCE_ELEMENTS',pce_party.attribute_category,'ATTRIBUTE18',pce_party.attribute18) attribute18_meaning
      ,pce_party.attribute19
      ,xxas_com_vset_pkg.decode_descr_vset_value
         ('800','PER_COMPETENCE_ELEMENTS',pce_party.attribute_category,'ATTRIBUTE19',pce_party.attribute19) attribute19_meaning
      ,pce_party.attribute20
      ,xxas_com_vset_pkg.decode_descr_vset_value
         ('800','PER_COMPETENCE_ELEMENTS',pce_party.attribute_category,'ATTRIBUTE20',pce_party.attribute20) attribute20_meaning
      ,pce_party.INFORMATION1
      ,xxas_com_vset_pkg.decode_descr_vset_value
         ('800','Competence Element Developer',pce_party.information_category,'INFORMATION1',pce_party.information1) information1_meaning
      ,pce_party.INFORMATION2
      ,xxas_com_vset_pkg.decode_descr_vset_value
         ('800','Competence Element Developer',pce_party.information_category,'INFORMATION2',pce_party.information2) information2_meaning
      ,pce_party.INFORMATION3
      ,xxas_com_vset_pkg.decode_descr_vset_value
         ('800','Competence Element Developer',pce_party.information_category,'INFORMATION3',pce_party.information3) information3_meaning
      ,pce_party.INFORMATION4
      ,xxas_com_vset_pkg.decode_descr_vset_value
         ('800','Competence Element Developer',pce_party.information_category,'INFORMATION4',pce_party.information4) information4_meaning
      ,pce_party.INFORMATION5
      ,xxas_com_vset_pkg.decode_descr_vset_value
         ('800','Competence Element Developer',pce_party.information_category,'INFORMATION5',pce_party.information5) information5_meaning
      ,pce_party.INFORMATION6
      ,xxas_com_vset_pkg.decode_descr_vset_value
         ('800','Competence Element Developer',pce_party.information_category,'INFORMATION6',pce_party.information6) information6_meaning
      ,pce_party.INFORMATION7
      ,xxas_com_vset_pkg.decode_descr_vset_value
         ('800','Competence Element Developer',pce_party.information_category,'INFORMATION7',pce_party.information7) information7_meaning
      ,pce_party.INFORMATION8
      ,xxas_com_vset_pkg.decode_descr_vset_value
         ('800','Competence Element Developer',pce_party.information_category,'INFORMATION8',pce_party.information8) information8_meaning
      ,pce_party.INFORMATION9
      ,xxas_com_vset_pkg.decode_descr_vset_value
         ('800','Competence Element Developer',pce_party.information_category,'INFORMATION9',pce_party.information9) information9_meaning
      ,pce_party.INFORMATION10
      ,xxas_com_vset_pkg.decode_descr_vset_value
         ('800','Competence Element Developer',pce_party.information_category,'INFORMATION10',pce_party.information10) information10_meaning
      ,pce_party.INFORMATION11
      ,xxas_com_vset_pkg.decode_descr_vset_value
         ('800','Competence Element Developer',pce_party.information_category,'INFORMATION11',pce_party.information11) information11_meaning
      ,pce_party.INFORMATION12
      ,xxas_com_vset_pkg.decode_descr_vset_value
         ('800','Competence Element Developer',pce_party.information_category,'INFORMATION12',pce_party.information12) information12_meaning
      ,pce_party.INFORMATION13
      ,xxas_com_vset_pkg.decode_descr_vset_value
         ('800','Competence Element Developer',pce_party.information_category,'INFORMATION13',pce_party.information13) information13_meaning
      ,pce_party.INFORMATION14
      ,xxas_com_vset_pkg.decode_descr_vset_value
         ('800','Competence Element Developer',pce_party.information_category,'INFORMATION14',pce_party.information14) information14_meaning
      ,pce_party.INFORMATION15
      ,xxas_com_vset_pkg.decode_descr_vset_value
         ('800','Competence Element Developer',pce_party.information_category,'INFORMATION15',pce_party.information15) information15_meaning
      ,pce_party.INFORMATION16
      ,xxas_com_vset_pkg.decode_descr_vset_value
         ('800','Competence Element Developer',pce_party.information_category,'INFORMATION16',pce_party.information16) information16_meaning
      ,pce_party.INFORMATION17
      ,xxas_com_vset_pkg.decode_descr_vset_value
         ('800','Competence Element Developer',pce_party.information_category,'INFORMATION17',pce_party.information17) information17_meaning
      ,pce_party.INFORMATION18
      ,xxas_com_vset_pkg.decode_descr_vset_value
         ('800','Competence Element Developer',pce_party.information_category,'INFORMATION18',pce_party.information18) information18_meaning
      ,pce_party.INFORMATION19
      ,xxas_com_vset_pkg.decode_descr_vset_value
         ('800','Competence Element Developer',pce_party.information_category,'INFORMATION19',pce_party.information19) information19_meaning
      ,pce_party.INFORMATION20
      ,xxas_com_vset_pkg.decode_descr_vset_value
         ('800','Competence Element Developer',pce_party.information_category,'INFORMATION20',pce_party.information20) information20_meaning
      ,percompetenceseo.ATTRIBUTE1 percomp_attribute1
      ,xxas_com_vset_pkg.decode_descr_vset_value
         ('800','PER_COMPETENCES',percompetenceseo.attribute_category,'ATTRIBUTE1',percompetenceseo.attribute1) percomp_attribute1_meaning
      ,percompetenceseo.ATTRIBUTE2 percomp_attribute2
      ,xxas_com_vset_pkg.decode_descr_vset_value
         ('800','PER_COMPETENCES',percompetenceseo.attribute_category,'ATTRIBUTE2',percompetenceseo.attribute2) percomp_attribute2_meaning
      ,percompetenceseo.ATTRIBUTE3 percomp_attribute3
      ,xxas_com_vset_pkg.decode_descr_vset_value
         ('800','PER_COMPETENCES',percompetenceseo.attribute_category,'ATTRIBUTE3',percompetenceseo.attribute3) percomp_attribute3_meaning
      ,percompetenceseo.ATTRIBUTE4 percomp_attribute4
      ,xxas_com_vset_pkg.decode_descr_vset_value
         ('800','PER_COMPETENCES',percompetenceseo.attribute_category,'ATTRIBUTE4',percompetenceseo.attribute4) percomp_attribute4_meaning
      ,percompetenceseo.ATTRIBUTE5 percomp_attribute5
      ,xxas_com_vset_pkg.decode_descr_vset_value
         ('800','PER_COMPETENCES',percompetenceseo.attribute_category,'ATTRIBUTE5',percompetenceseo.attribute5) percomp_attribute5_meaning
      ,percompetenceseo.ATTRIBUTE6 percomp_attribute6
      ,xxas_com_vset_pkg.decode_descr_vset_value
         ('800','PER_COMPETENCES',percompetenceseo.attribute_category,'ATTRIBUTE6',percompetenceseo.attribute6) percomp_attribute6_meaning
      ,percompetenceseo.ATTRIBUTE7 percomp_attribute7
      ,xxas_com_vset_pkg.decode_descr_vset_value
         ('800','PER_COMPETENCES',percompetenceseo.attribute_category,'ATTRIBUTE7',percompetenceseo.attribute7) percomp_attribute7_meaning
      ,percompetenceseo.ATTRIBUTE8 percomp_attribute8
      ,xxas_com_vset_pkg.decode_descr_vset_value
         ('800','PER_COMPETENCES',percompetenceseo.attribute_category,'ATTRIBUTE8',percompetenceseo.attribute8) percomp_attribute8_meaning
      ,percompetenceseo.ATTRIBUTE9 percomp_attribute9
      ,xxas_com_vset_pkg.decode_descr_vset_value
         ('800','PER_COMPETENCES',percompetenceseo.attribute_category,'ATTRIBUTE9',percompetenceseo.attribute9) percomp_attribute9_meaning
      ,percompetenceseo.ATTRIBUTE10 percomp_attribute10
      ,xxas_com_vset_pkg.decode_descr_vset_value
         ('800','PER_COMPETENCES',percompetenceseo.attribute_category,'ATTRIBUTE10',percompetenceseo.attribute10) percomp_attribute10_meaning
      ,percompetenceseo.ATTRIBUTE11 percomp_attribute11
      ,xxas_com_vset_pkg.decode_descr_vset_value
         ('800','PER_COMPETENCES',percompetenceseo.attribute_category,'ATTRIBUTE11',percompetenceseo.attribute11) percomp_attribute11_meaning
      ,percompetenceseo.ATTRIBUTE12 percomp_attribute12
      ,xxas_com_vset_pkg.decode_descr_vset_value
         ('800','PER_COMPETENCES',percompetenceseo.attribute_category,'ATTRIBUTE12',percompetenceseo.attribute12) percomp_attribute12_meaning
      ,percompetenceseo.ATTRIBUTE13 percomp_attribute13
      ,xxas_com_vset_pkg.decode_descr_vset_value
         ('800','PER_COMPETENCES',percompetenceseo.attribute_category,'ATTRIBUTE13',percompetenceseo.attribute13) percomp_attribute13_meaning
      ,percompetenceseo.ATTRIBUTE14 percomp_attribute14
      ,xxas_com_vset_pkg.decode_descr_vset_value
         ('800','PER_COMPETENCES',percompetenceseo.attribute_category,'ATTRIBUTE14',percompetenceseo.attribute14) percomp_attribute14_meaning
      ,percompetenceseo.ATTRIBUTE15 percomp_attribute15
      ,xxas_com_vset_pkg.decode_descr_vset_value
         ('800','PER_COMPETENCES',percompetenceseo.attribute_category,'ATTRIBUTE15',percompetenceseo.attribute15) percomp_attribute15_meaning
      ,percompetenceseo.ATTRIBUTE16 percomp_attribute16
      ,xxas_com_vset_pkg.decode_descr_vset_value
         ('800','PER_COMPETENCES',percompetenceseo.attribute_category,'ATTRIBUTE16',percompetenceseo.attribute16) percomp_attribute16_meaning
      ,percompetenceseo.ATTRIBUTE17 percomp_attribute17
      ,xxas_com_vset_pkg.decode_descr_vset_value
         ('800','PER_COMPETENCES',percompetenceseo.attribute_category,'ATTRIBUTE17',percompetenceseo.attribute17) percomp_attribute17_meaning
      ,percompetenceseo.ATTRIBUTE18 percomp_attribute18
      ,xxas_com_vset_pkg.decode_descr_vset_value
         ('800','PER_COMPETENCES',percompetenceseo.attribute_category,'ATTRIBUTE18',percompetenceseo.attribute18) percomp_attribute18_meaning
      ,percompetenceseo.ATTRIBUTE19 percomp_attribute19
      ,xxas_com_vset_pkg.decode_descr_vset_value
         ('800','PER_COMPETENCES',percompetenceseo.attribute_category,'ATTRIBUTE19',percompetenceseo.attribute19) percomp_attribute19_meaning
      ,percompetenceseo.ATTRIBUTE20 percomp_attribute20
      ,xxas_com_vset_pkg.decode_descr_vset_value
         ('800','PER_COMPETENCES',percompetenceseo.attribute_category,'ATTRIBUTE20',percompetenceseo.attribute20) percomp_attribute20_meaning         
      ,percompetenceseo.INFORMATION1 percomp_information1
      ,xxas_com_vset_pkg.decode_descr_vset_value
         ('800','Competence Developer DF',percompetenceseo.information_category,'INFORMATION1',percompetenceseo.information1) percomp_information1_meaning
      ,percompetenceseo.INFORMATION2 percomp_information2
      ,xxas_com_vset_pkg.decode_descr_vset_value
         ('800','Competence Developer DF',percompetenceseo.information_category,'INFORMATION2',percompetenceseo.information2) percomp_information2_meaning
      ,percompetenceseo.INFORMATION3 percomp_information3
      ,xxas_com_vset_pkg.decode_descr_vset_value
         ('800','Competence Developer DF',percompetenceseo.information_category,'INFORMATION3',percompetenceseo.information3) percomp_information3_meaning
      ,percompetenceseo.INFORMATION4 percomp_information4
      ,xxas_com_vset_pkg.decode_descr_vset_value
         ('800','Competence Developer DF',percompetenceseo.information_category,'INFORMATION4',percompetenceseo.information4) percomp_information4_meaning
      ,percompetenceseo.INFORMATION5 percomp_information5
      ,xxas_com_vset_pkg.decode_descr_vset_value
         ('800','Competence Developer DF',percompetenceseo.information_category,'INFORMATION5',percompetenceseo.information5) percomp_information5_meaning
      ,percompetenceseo.INFORMATION6 percomp_information6
      ,xxas_com_vset_pkg.decode_descr_vset_value
         ('800','Competence Developer DF',percompetenceseo.information_category,'INFORMATION6',percompetenceseo.information6) percomp_information6_meaning
      ,percompetenceseo.INFORMATION7 percomp_information7
      ,xxas_com_vset_pkg.decode_descr_vset_value
         ('800','Competence Developer DF',percompetenceseo.information_category,'INFORMATION7',percompetenceseo.information7) percomp_information7_meaning
      ,percompetenceseo.INFORMATION8 percomp_information8
      ,xxas_com_vset_pkg.decode_descr_vset_value
         ('800','Competence Developer DF',percompetenceseo.information_category,'INFORMATION8',percompetenceseo.information8) percomp_information8_meaning
      ,percompetenceseo.INFORMATION9 percomp_information9
      ,xxas_com_vset_pkg.decode_descr_vset_value
         ('800','Competence Developer DF',percompetenceseo.information_category,'INFORMATION9',percompetenceseo.information9) percomp_information9_meaning
      ,percompetenceseo.INFORMATION10 percomp_information10
      ,xxas_com_vset_pkg.decode_descr_vset_value
         ('800','Competence Developer DF',percompetenceseo.information_category,'INFORMATION10',percompetenceseo.information10) percomp_information10_meaning
      ,percompetenceseo.INFORMATION11 percomp_information11
      ,xxas_com_vset_pkg.decode_descr_vset_value
         ('800','Competence Developer DF',percompetenceseo.information_category,'INFORMATION11',percompetenceseo.information11) percomp_information11_meaning
      ,percompetenceseo.INFORMATION12 percomp_information12
      ,xxas_com_vset_pkg.decode_descr_vset_value
         ('800','Competence Developer DF',percompetenceseo.information_category,'INFORMATION12',percompetenceseo.information12) percomp_information12_meaning
      ,percompetenceseo.INFORMATION13 percomp_information13
      ,xxas_com_vset_pkg.decode_descr_vset_value
         ('800','Competence Developer DF',percompetenceseo.information_category,'INFORMATION13',percompetenceseo.information13) percomp_information13_meaning
      ,percompetenceseo.INFORMATION14 percomp_information14
      ,xxas_com_vset_pkg.decode_descr_vset_value
         ('800','Competence Developer DF',percompetenceseo.information_category,'INFORMATION14',percompetenceseo.information14) percomp_information14_meaning
      ,percompetenceseo.INFORMATION15 percomp_information15
      ,xxas_com_vset_pkg.decode_descr_vset_value
         ('800','Competence Developer DF',percompetenceseo.information_category,'INFORMATION15',percompetenceseo.information15) percomp_information15_meaning
      ,percompetenceseo.INFORMATION16 percomp_information16
      ,xxas_com_vset_pkg.decode_descr_vset_value
         ('800','Competence Developer DF',percompetenceseo.information_category,'INFORMATION16',percompetenceseo.information16) percomp_information16_meaning
      ,percompetenceseo.INFORMATION17 percomp_information17
      ,xxas_com_vset_pkg.decode_descr_vset_value
         ('800','Competence Developer DF',percompetenceseo.information_category,'INFORMATION17',percompetenceseo.information17) percomp_information17_meaning
      ,percompetenceseo.INFORMATION18 percomp_information18
      ,xxas_com_vset_pkg.decode_descr_vset_value
         ('800','Competence Developer DF',percompetenceseo.information_category,'INFORMATION18',percompetenceseo.information18) percomp_information18_meaning
      ,percompetenceseo.INFORMATION19 percomp_information19
      ,xxas_com_vset_pkg.decode_descr_vset_value
         ('800','Competence Developer DF',percompetenceseo.information_category,'INFORMATION19',percompetenceseo.information19) percomp_information19_meaning
      ,percompetenceseo.INFORMATION20 percomp_information20
      ,xxas_com_vset_pkg.decode_descr_vset_value
         ('800','Competence Developer DF',percompetenceseo.information_category,'INFORMATION20',percompetenceseo.information20) percomp_information20_meaning
FROM    per_competence_elements pce_party
      , per_competences_vl percompetenceseo
      , per_rating_levels_vl perratinglevelseo
WHERE   pce_party.competence_id = percompetenceseo.competence_id
AND     pce_party.proficiency_level_id = perratinglevelseo.rating_level_id (+);

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