/* $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_qualifications_v 
AS
SELECT pea.attendance_id
      ,pea.person_id
      ,pea.object_version_number pea_object_version_number
      ,pq.object_version_number
      ,pq.business_group_id
      ,'EDUCATION' education_or_award
      ,pea.attended_start_date
      ,pea.attended_end_date
      ,pea.full_time full_time_flag
      ,xxas_util_pkg.decode_lookup('YES_NO', pea.full_time) full_time_meaning
      ,pea.establishment_id
      ,nvl(pe.name, pea.establishment) establishment
      ,pea.address
      ,pea.attribute_category pea_attribute_category
      ,pea.attribute1 pea_attribute1
      ,xxas_com_vset_pkg.decode_descr_vset_value
         ('800','PER_ESTABLISHMENT_ATTENDANCES',pea.attribute_category,'ATTRIBUTE1',pea.attribute1) pea_attribute1_meaning
      ,pea.attribute2 pea_attribute2
      ,xxas_com_vset_pkg.decode_descr_vset_value
         ('800','PER_ESTABLISHMENT_ATTENDANCES',pea.attribute_category,'ATTRIBUTE2',pea.attribute2) pea_attribute2_meaning
      ,pea.attribute3 pea_attribute3
      ,xxas_com_vset_pkg.decode_descr_vset_value
         ('800','PER_ESTABLISHMENT_ATTENDANCES',pea.attribute_category,'ATTRIBUTE3',pea.attribute3) pea_attribute3_meaning
      ,pea.attribute4 pea_attribute4
      ,xxas_com_vset_pkg.decode_descr_vset_value
         ('800','PER_ESTABLISHMENT_ATTENDANCES',pea.attribute_category,'ATTRIBUTE4',pea.attribute4) pea_attribute4_meaning
      ,pea.attribute5 pea_attribute5
      ,xxas_com_vset_pkg.decode_descr_vset_value
         ('800','PER_ESTABLISHMENT_ATTENDANCES',pea.attribute_category,'ATTRIBUTE5',pea.attribute5) pea_attribute5_meaning
      ,pea.attribute6 pea_attribute6
      ,xxas_com_vset_pkg.decode_descr_vset_value
         ('800','PER_ESTABLISHMENT_ATTENDANCES',pea.attribute_category,'ATTRIBUTE6',pea.attribute6) pea_attribute6_meaning
      ,pea.attribute7 pea_attribute7
      ,xxas_com_vset_pkg.decode_descr_vset_value
         ('800','PER_ESTABLISHMENT_ATTENDANCES',pea.attribute_category,'ATTRIBUTE7',pea.attribute7) pea_attribute7_meaning
      ,pea.attribute8 pea_attribute8
      ,xxas_com_vset_pkg.decode_descr_vset_value
         ('800','PER_ESTABLISHMENT_ATTENDANCES',pea.attribute_category,'ATTRIBUTE8',pea.attribute8) pea_attribute8_meaning
      ,pea.attribute9 pea_attribute9
      ,xxas_com_vset_pkg.decode_descr_vset_value
         ('800','PER_ESTABLISHMENT_ATTENDANCES',pea.attribute_category,'ATTRIBUTE9',pea.attribute9) pea_attribute9_meaning
      ,pea.attribute10 pea_attribute10
      ,xxas_com_vset_pkg.decode_descr_vset_value
         ('800','PER_ESTABLISHMENT_ATTENDANCES',pea.attribute_category,'ATTRIBUTE10',pea.attribute10) pea_attribute10_meaning
      ,pea.attribute11 pea_attribute11
      ,xxas_com_vset_pkg.decode_descr_vset_value
         ('800','PER_ESTABLISHMENT_ATTENDANCES',pea.attribute_category,'ATTRIBUTE11',pea.attribute11) pea_attribute11_meaning
      ,pea.attribute12 pea_attribute12
      ,xxas_com_vset_pkg.decode_descr_vset_value
         ('800','PER_ESTABLISHMENT_ATTENDANCES',pea.attribute_category,'ATTRIBUTE12',pea.attribute12) pea_attribute12_meaning
      ,pea.attribute13 pea_attribute13
      ,xxas_com_vset_pkg.decode_descr_vset_value
         ('800','PER_ESTABLISHMENT_ATTENDANCES',pea.attribute_category,'ATTRIBUTE13',pea.attribute13) pea_attribute13_meaning
      ,pea.attribute14 pea_attribute14
      ,xxas_com_vset_pkg.decode_descr_vset_value
         ('800','PER_ESTABLISHMENT_ATTENDANCES',pea.attribute_category,'ATTRIBUTE14',pea.attribute14) pea_attribute14_meaning
      ,pea.attribute15 pea_attribute15
      ,xxas_com_vset_pkg.decode_descr_vset_value
         ('800','PER_ESTABLISHMENT_ATTENDANCES',pea.attribute_category,'ATTRIBUTE15',pea.attribute15) pea_attribute15_meaning
      ,pea.attribute16 pea_attribute16
      ,xxas_com_vset_pkg.decode_descr_vset_value
         ('800','PER_ESTABLISHMENT_ATTENDANCES',pea.attribute_category,'ATTRIBUTE16',pea.attribute16) pea_attribute16_meaning
      ,pea.attribute17 pea_attribute17
      ,xxas_com_vset_pkg.decode_descr_vset_value
         ('800','PER_ESTABLISHMENT_ATTENDANCES',pea.attribute_category,'ATTRIBUTE17',pea.attribute17) pea_attribute17_meaning
      ,pea.attribute18 pea_attribute18
      ,xxas_com_vset_pkg.decode_descr_vset_value
         ('800','PER_ESTABLISHMENT_ATTENDANCES',pea.attribute_category,'ATTRIBUTE18',pea.attribute18) pea_attribute18_meaning
      ,pea.attribute19 pea_attribute19
      ,xxas_com_vset_pkg.decode_descr_vset_value
         ('800','PER_ESTABLISHMENT_ATTENDANCES',pea.attribute_category,'ATTRIBUTE19',pea.attribute19) pea_attribute19_meaning
      ,pea.attribute20 pea_attribute20
      ,xxas_com_vset_pkg.decode_descr_vset_value
         ('800','PER_ESTABLISHMENT_ATTENDANCES',pea.attribute_category,'ATTRIBUTE20',pea.attribute20) pea_attribute20_meaning
      ,pq.last_update_date
      ,pq.last_updated_by
      ,pq.last_update_login
      ,pq.created_by
      ,pq.creation_date
      ,pq.qualification_id
      ,pq.title
      ,pqt.qualification_type_id
      ,pqt.category pq_category_code
      ,xxas_util_pkg.decode_lookup('PER_CATEGORIES', pqt.category) pq_category_meaning
      ,pqt.rank
      ,pqt.name qualification_name
      ,pq.grade_attained
      ,pq.status status_code
      ,xxas_util_pkg.decode_lookup('PER_SUBJECT_STATUSES', pq.status) status_meaning
      ,pq.awarded_date
      ,pq.fee
      ,pq.fee_currency fee_currency_code
      ,fc.name fee_currency_meaning
      ,pq.training_completed_amount
      ,pq.reimbursement_arrangements
      ,pq.training_completed_units
      ,pq.total_training_amount
      ,pq.start_date
      ,pq.end_date
      ,pq.license_number
      ,pq.expiry_date
      ,pq.license_restrictions
      ,pq.projected_completion_date
      ,pq.awarding_body
      ,pq.tuition_method
      ,pq.group_ranking
      ,pq.professional_body_name
      ,pq.membership_number
      ,pq.membership_category
      ,pq.subscription_payment_method
      ,pq.comments
      ,pq.attribute_category qua_attribute_category
      ,pq.attribute1 qua_attribute1
      ,xxas_com_vset_pkg.decode_descr_vset_value
         ('800','PER_QUALIFICATIONS',pq.attribute_category,'ATTRIBUTE1',pq.attribute1) qua_attribute1_meaning
      ,pq.attribute2 qua_attribute2
      ,xxas_com_vset_pkg.decode_descr_vset_value
         ('800','PER_QUALIFICATIONS',pq.attribute_category,'ATTRIBUTE2',pq.attribute2) qua_attribute2_meaning
      ,pq.attribute3 qua_attribute3
      ,xxas_com_vset_pkg.decode_descr_vset_value
         ('800','PER_QUALIFICATIONS',pq.attribute_category,'ATTRIBUTE3',pq.attribute3) qua_attribute3_meaning
      ,pq.attribute4 qua_attribute4
      ,xxas_com_vset_pkg.decode_descr_vset_value
         ('800','PER_QUALIFICATIONS',pq.attribute_category,'ATTRIBUTE4',pq.attribute4) qua_attribute4_meaning
      ,pq.attribute5 qua_attribute5
      ,xxas_com_vset_pkg.decode_descr_vset_value
         ('800','PER_QUALIFICATIONS',pq.attribute_category,'ATTRIBUTE5',pq.attribute5) qua_attribute5_meaning
      ,pq.attribute6 qua_attribute6
      ,xxas_com_vset_pkg.decode_descr_vset_value
         ('800','PER_QUALIFICATIONS',pq.attribute_category,'ATTRIBUTE6',pq.attribute6) qua_attribute6_meaning
      ,pq.attribute7 qua_attribute7
      ,xxas_com_vset_pkg.decode_descr_vset_value
         ('800','PER_QUALIFICATIONS',pq.attribute_category,'ATTRIBUTE7',pq.attribute7) qua_attribute7_meaning
      ,pq.attribute8 qua_attribute8
      ,xxas_com_vset_pkg.decode_descr_vset_value
         ('800','PER_QUALIFICATIONS',pq.attribute_category,'ATTRIBUTE8',pq.attribute8) qua_attribute8_meaning
      ,pq.attribute9 qua_attribute9
      ,xxas_com_vset_pkg.decode_descr_vset_value
         ('800','PER_QUALIFICATIONS',pq.attribute_category,'ATTRIBUTE9',pq.attribute9) qua_attribute9_meaning
      ,pq.attribute10 qua_attribute10
      ,xxas_com_vset_pkg.decode_descr_vset_value
         ('800','PER_QUALIFICATIONS',pq.attribute_category,'ATTRIBUTE10',pq.attribute10) qua_attribute10_meaning
      ,pq.attribute11 qua_attribute11
      ,xxas_com_vset_pkg.decode_descr_vset_value
         ('800','PER_QUALIFICATIONS',pq.attribute_category,'ATTRIBUTE11',pq.attribute11) qua_attribute11_meaning
      ,pq.attribute12 qua_attribute12
      ,xxas_com_vset_pkg.decode_descr_vset_value
         ('800','PER_QUALIFICATIONS',pq.attribute_category,'ATTRIBUTE12',pq.attribute12) qua_attribute12_meaning
      ,pq.attribute13 qua_attribute13
      ,xxas_com_vset_pkg.decode_descr_vset_value
         ('800','PER_QUALIFICATIONS',pq.attribute_category,'ATTRIBUTE13',pq.attribute13) qua_attribute13_meaning
      ,pq.attribute14 qua_attribute14
      ,xxas_com_vset_pkg.decode_descr_vset_value
         ('800','PER_QUALIFICATIONS',pq.attribute_category,'ATTRIBUTE14',pq.attribute14) qua_attribute14_meaning
      ,pq.attribute15 qua_attribute15
      ,xxas_com_vset_pkg.decode_descr_vset_value
         ('800','PER_QUALIFICATIONS',pq.attribute_category,'ATTRIBUTE15',pq.attribute15) qua_attribute15_meaning
      ,pq.attribute16 qua_attribute16
      ,xxas_com_vset_pkg.decode_descr_vset_value
         ('800','PER_QUALIFICATIONS',pq.attribute_category,'ATTRIBUTE16',pq.attribute16) qua_attribute16_meaning
      ,pq.attribute17 qua_attribute17
      ,xxas_com_vset_pkg.decode_descr_vset_value
         ('800','PER_QUALIFICATIONS',pq.attribute_category,'ATTRIBUTE17',pq.attribute17) qua_attribute17_meaning
      ,pq.attribute18 qua_attribute18
      ,xxas_com_vset_pkg.decode_descr_vset_value
         ('800','PER_QUALIFICATIONS',pq.attribute_category,'ATTRIBUTE18',pq.attribute18) qua_attribute18_meaning
      ,pq.attribute19 qua_attribute19
      ,xxas_com_vset_pkg.decode_descr_vset_value
         ('800','PER_QUALIFICATIONS',pq.attribute_category,'ATTRIBUTE19',pq.attribute19) qua_attribute19_meaning
      ,pq.attribute20 qua_attribute20
      ,xxas_com_vset_pkg.decode_descr_vset_value
         ('800','PER_QUALIFICATIONS',pq.attribute_category,'ATTRIBUTE20',pq.attribute20) qua_attribute30_meaning
      ,pq.qua_information_category qua_information_category
      ,pq.qua_information1 qua_information1
      ,xxas_com_vset_pkg.decode_descr_vset_value
         ('800','Qualification Developer DF',pq.qua_information_category,'QUA_INFORMATION1',pq.qua_information1) qua_information1_meaning
      ,pq.qua_information2 qua_information2
      ,xxas_com_vset_pkg.decode_descr_vset_value
         ('800','Qualification Developer DF',pq.qua_information_category,'QUA_INFORMATION2',pq.qua_information2) qua_information2_meaning
      ,pq.qua_information3 qua_information3
      ,xxas_com_vset_pkg.decode_descr_vset_value
         ('800','Qualification Developer DF',pq.qua_information_category,'QUA_INFORMATION3',pq.qua_information3) qua_information3_meaning
      ,pq.qua_information4 qua_information4
      ,xxas_com_vset_pkg.decode_descr_vset_value
         ('800','Qualification Developer DF',pq.qua_information_category,'QUA_INFORMATION4',pq.qua_information4) qua_information4_meaning
      ,pq.qua_information5 qua_information5
      ,xxas_com_vset_pkg.decode_descr_vset_value
         ('800','Qualification Developer DF',pq.qua_information_category,'QUA_INFORMATION5',pq.qua_information5) qua_information5_meaning
      ,pq.qua_information6 qua_information6
      ,xxas_com_vset_pkg.decode_descr_vset_value
         ('800','Qualification Developer DF',pq.qua_information_category,'QUA_INFORMATION6',pq.qua_information6) qua_information6_meaning
      ,pq.qua_information7 qua_information7
      ,xxas_com_vset_pkg.decode_descr_vset_value
         ('800','Qualification Developer DF',pq.qua_information_category,'QUA_INFORMATION7',pq.qua_information7) qua_information7_meaning
      ,pq.qua_information8 qua_information8
      ,xxas_com_vset_pkg.decode_descr_vset_value
         ('800','Qualification Developer DF',pq.qua_information_category,'QUA_INFORMATION8',pq.qua_information8) qua_information8_meaning
      ,pq.qua_information9 qua_information9
      ,xxas_com_vset_pkg.decode_descr_vset_value
         ('800','Qualification Developer DF',pq.qua_information_category,'QUA_INFORMATION9',pq.qua_information9) qua_information9_meaning
      ,pq.qua_information10 qua_information10
      ,xxas_com_vset_pkg.decode_descr_vset_value
         ('800','Qualification Developer DF',pq.qua_information_category,'QUA_INFORMATION10',pq.qua_information10) qua_information10_meaning
      ,pq.qua_information11 qua_information11
      ,xxas_com_vset_pkg.decode_descr_vset_value
         ('800','Qualification Developer DF',pq.qua_information_category,'QUA_INFORMATION11',pq.qua_information11) qua_information11_meaning
      ,pq.qua_information12 qua_information12
      ,xxas_com_vset_pkg.decode_descr_vset_value
         ('800','Qualification Developer DF',pq.qua_information_category,'QUA_INFORMATION12',pq.qua_information12) qua_information12_meaning
      ,pq.qua_information13 qua_information13
      ,xxas_com_vset_pkg.decode_descr_vset_value
         ('800','Qualification Developer DF',pq.qua_information_category,'QUA_INFORMATION13',pq.qua_information13) qua_information13_meaning
      ,pq.qua_information14 qua_information14
      ,xxas_com_vset_pkg.decode_descr_vset_value
         ('800','Qualification Developer DF',pq.qua_information_category,'QUA_INFORMATION14',pq.qua_information14) qua_information14_meaning
      ,pq.qua_information15 qua_information15
      ,xxas_com_vset_pkg.decode_descr_vset_value
         ('800','Qualification Developer DF',pq.qua_information_category,'QUA_INFORMATION15',pq.qua_information15) qua_information15_meaning
      ,pq.qua_information16 qua_information16
      ,xxas_com_vset_pkg.decode_descr_vset_value
         ('800','Qualification Developer DF',pq.qua_information_category,'QUA_INFORMATION16',pq.qua_information16) qua_information16_meaning
      ,pq.qua_information17 qua_information17
      ,xxas_com_vset_pkg.decode_descr_vset_value
         ('800','Qualification Developer DF',pq.qua_information_category,'QUA_INFORMATION17',pq.qua_information17) qua_information17_meaning
      ,pq.qua_information18 qua_information18
      ,xxas_com_vset_pkg.decode_descr_vset_value
         ('800','Qualification Developer DF',pq.qua_information_category,'QUA_INFORMATION18',pq.qua_information18) qua_information18_meaning
      ,pq.qua_information19 qua_information19
      ,xxas_com_vset_pkg.decode_descr_vset_value
         ('800','Qualification Developer DF',pq.qua_information_category,'QUA_INFORMATION19',pq.qua_information19) qua_information19_meaning
      ,pq.qua_information20 qua_information20
      ,xxas_com_vset_pkg.decode_descr_vset_value
         ('800','Qualification Developer DF',pq.qua_information_category,'QUA_INFORMATION20',pq.qua_information20) qua_information20_meaning
      ,pend.transaction_id
      ,pend.transaction_step_id
      ,pend.transaction_type
      ,pend.hr_api_status_code
      ,pend.workflow_status_code
      ,xxas_selfservice_workflow_pkg.decode_status(pend.workflow_status_code) workflow_status_meaning
      ,pend.item_type
      ,pend.item_key
      ,'PQH_SS_ATTACHMENT' attachment_entity
      ,pend.transaction_id attachment_pk1
      ,xxas_hr_api_transaction_pkg.varchar2_value(pend.person_id, pend.api_name, pend.transaction_step_id,'P_ATTENDANCE_ID') attendance_id_p
      ,xxas_hr_api_transaction_pkg.varchar2_value(pend.person_id, pend.api_name, pend.transaction_step_id,'P_PEA_OBJECT_VERSION_NUMBER') pea_object_version_number_p
      ,xxas_hr_api_transaction_pkg.varchar2_value(pend.person_id, pend.api_name, pend.transaction_step_id,'P_PQ_OBJECT_VERSION_NUMBER') object_version_number_p
      ,xxas_hr_api_transaction_pkg.varchar2_value(pend.person_id, pend.api_name, pend.transaction_step_id,'P_BUSINESS_GROUP_ID') business_group_id_p
      ,xxas_hr_api_transaction_pkg.date_value(pend.person_id, pend.api_name, pend.transaction_step_id,'P_PEA_START_DATE') attended_start_date_p
      ,xxas_hr_api_transaction_pkg.date_value(pend.person_id, pend.api_name, pend.transaction_step_id,'P_PEA_END_DATE') attended_end_date_p
      ,xxas_hr_api_transaction_pkg.varchar2_value(pend.person_id, pend.api_name, pend.transaction_step_id,'P_PEA_FULL_TIME') full_time_flag_p
      ,xxas_util_pkg.decode_lookup
        ('YES_NO'
        ,xxas_hr_api_transaction_pkg.varchar2_value(pend.person_id, pend.api_name, pend.transaction_step_id,'P_PEA_FULL_TIME')) full_time_meaning_p
      ,xxas_hr_api_transaction_pkg.varchar2_value(pend.person_id, pend.api_name, pend.transaction_step_id,'P_ESTABLISHMENT_ID') establishment_id_p
      ,decode
        (xxas_hr_api_transaction_pkg.varchar2_value(pend.person_id, pend.api_name, pend.transaction_step_id,'P_ESTABLISHMENT_ID')
        ,null, xxas_hr_api_transaction_pkg.varchar2_value(pend.person_id, pend.api_name, pend.transaction_step_id,'P_PE_NAME')
        ,(SELECT pend_pe.name
          FROM   per_establishments pend_pe
          WHERE  pend_pe.establishment_id =
            xxas_hr_api_transaction_pkg.varchar2_value(pend.person_id, pend.api_name, pend.transaction_step_id,'P_ESTABLISHMENT_ID'))) establishment_p
      ,null address_p
      ,xxas_hr_api_transaction_pkg.varchar2_value(pend.person_id, pend.api_name, pend.transaction_step_id,'P_PEA_ATTRIBUTE_CATEGORY') pea_attribute_category_p
      ,xxas_hr_api_transaction_pkg.varchar2_value(pend.person_id, pend.api_name, pend.transaction_step_id,'P_PEA_ATTRIBUTE1') pea_attribute1_p
      ,xxas_com_vset_pkg.decode_descr_vset_value
         ('800','PER_ESTABLISHMENT_ATTENDANCES'
         ,xxas_hr_api_transaction_pkg.varchar2_value(pend.person_id, pend.api_name, pend.transaction_step_id,'P_PEA_ATTRIBUTE_CATEGORY')
         ,'ATTRIBUTE1'
         ,xxas_hr_api_transaction_pkg.varchar2_value(pend.person_id, pend.api_name, pend.transaction_step_id,'P_PEA_ATTRIBUTE1')) pea_attribute1_meaning_p
      ,xxas_hr_api_transaction_pkg.varchar2_value(pend.person_id, pend.api_name, pend.transaction_step_id,'P_PEA_ATTRIBUTE2') pea_attribute2_p
      ,xxas_com_vset_pkg.decode_descr_vset_value
         ('800','PER_ESTABLISHMENT_ATTENDANCES'
         ,xxas_hr_api_transaction_pkg.varchar2_value(pend.person_id, pend.api_name, pend.transaction_step_id,'P_PEA_ATTRIBUTE_CATEGORY')
         ,'ATTRIBUTE2'
         ,xxas_hr_api_transaction_pkg.varchar2_value(pend.person_id, pend.api_name, pend.transaction_step_id,'P_PEA_ATTRIBUTE2')) pea_attribute2_meaning_p
      ,xxas_hr_api_transaction_pkg.varchar2_value(pend.person_id, pend.api_name, pend.transaction_step_id,'P_PEA_ATTRIBUTE3') pea_attribute3_p
      ,xxas_com_vset_pkg.decode_descr_vset_value
         ('800','PER_ESTABLISHMENT_ATTENDANCES'
         ,xxas_hr_api_transaction_pkg.varchar2_value(pend.person_id, pend.api_name, pend.transaction_step_id,'P_PEA_ATTRIBUTE_CATEGORY')
         ,'ATTRIBUTE3'
         ,xxas_hr_api_transaction_pkg.varchar2_value(pend.person_id, pend.api_name, pend.transaction_step_id,'P_PEA_ATTRIBUTE3')) pea_attribute3_meaning_p
      ,xxas_hr_api_transaction_pkg.varchar2_value(pend.person_id, pend.api_name, pend.transaction_step_id,'P_PEA_ATTRIBUTE4') pea_attribute4_p
      ,xxas_com_vset_pkg.decode_descr_vset_value
         ('800','PER_ESTABLISHMENT_ATTENDANCES'
         ,xxas_hr_api_transaction_pkg.varchar2_value(pend.person_id, pend.api_name, pend.transaction_step_id,'P_PEA_ATTRIBUTE_CATEGORY')
         ,'ATTRIBUTE4'
         ,xxas_hr_api_transaction_pkg.varchar2_value(pend.person_id, pend.api_name, pend.transaction_step_id,'P_PEA_ATTRIBUTE4')) pea_attribute4_meaning_p
      ,xxas_hr_api_transaction_pkg.varchar2_value(pend.person_id, pend.api_name, pend.transaction_step_id,'P_PEA_ATTRIBUTE5') pea_attribute5_p
      ,xxas_com_vset_pkg.decode_descr_vset_value
         ('800','PER_ESTABLISHMENT_ATTENDANCES'
         ,xxas_hr_api_transaction_pkg.varchar2_value(pend.person_id, pend.api_name, pend.transaction_step_id,'P_PEA_ATTRIBUTE_CATEGORY')
         ,'ATTRIBUTE5'
         ,xxas_hr_api_transaction_pkg.varchar2_value(pend.person_id, pend.api_name, pend.transaction_step_id,'P_PEA_ATTRIBUTE5')) pea_attribute5_meaning_p
      ,xxas_hr_api_transaction_pkg.varchar2_value(pend.person_id, pend.api_name, pend.transaction_step_id,'P_PEA_ATTRIBUTE6') pea_attribute6_p
      ,xxas_com_vset_pkg.decode_descr_vset_value
         ('800','PER_ESTABLISHMENT_ATTENDANCES'
         ,xxas_hr_api_transaction_pkg.varchar2_value(pend.person_id, pend.api_name, pend.transaction_step_id,'P_PEA_ATTRIBUTE_CATEGORY')
         ,'ATTRIBUTE6'
         ,xxas_hr_api_transaction_pkg.varchar2_value(pend.person_id, pend.api_name, pend.transaction_step_id,'P_PEA_ATTRIBUTE6')) pea_attribute6_meaning_p
      ,xxas_hr_api_transaction_pkg.varchar2_value(pend.person_id, pend.api_name, pend.transaction_step_id,'P_PEA_ATTRIBUTE7') pea_attribute7_p
      ,xxas_com_vset_pkg.decode_descr_vset_value
         ('800','PER_ESTABLISHMENT_ATTENDANCES'
         ,xxas_hr_api_transaction_pkg.varchar2_value(pend.person_id, pend.api_name, pend.transaction_step_id,'P_PEA_ATTRIBUTE_CATEGORY')
         ,'ATTRIBUTE7'
         ,xxas_hr_api_transaction_pkg.varchar2_value(pend.person_id, pend.api_name, pend.transaction_step_id,'P_PEA_ATTRIBUTE7')) pea_attribute7_meaning_p
      ,xxas_hr_api_transaction_pkg.varchar2_value(pend.person_id, pend.api_name, pend.transaction_step_id,'P_PEA_ATTRIBUTE8') pea_attribute8_p
      ,xxas_com_vset_pkg.decode_descr_vset_value
         ('800','PER_ESTABLISHMENT_ATTENDANCES'
         ,xxas_hr_api_transaction_pkg.varchar2_value(pend.person_id, pend.api_name, pend.transaction_step_id,'P_PEA_ATTRIBUTE_CATEGORY')
         ,'ATTRIBUTE8'
         ,xxas_hr_api_transaction_pkg.varchar2_value(pend.person_id, pend.api_name, pend.transaction_step_id,'P_PEA_ATTRIBUTE8')) pea_attribute8_meaning_p
      ,xxas_hr_api_transaction_pkg.varchar2_value(pend.person_id, pend.api_name, pend.transaction_step_id,'P_PEA_ATTRIBUTE9') pea_attribute9_p
      ,xxas_com_vset_pkg.decode_descr_vset_value
         ('800','PER_ESTABLISHMENT_ATTENDANCES'
         ,xxas_hr_api_transaction_pkg.varchar2_value(pend.person_id, pend.api_name, pend.transaction_step_id,'P_PEA_ATTRIBUTE_CATEGORY')
         ,'ATTRIBUTE9'
         ,xxas_hr_api_transaction_pkg.varchar2_value(pend.person_id, pend.api_name, pend.transaction_step_id,'P_PEA_ATTRIBUTE9')) pea_attribute9_meaning_p
      ,xxas_hr_api_transaction_pkg.varchar2_value(pend.person_id, pend.api_name, pend.transaction_step_id,'P_PEA_ATTRIBUTE10') pea_attribute10_p
      ,xxas_com_vset_pkg.decode_descr_vset_value
         ('800','PER_ESTABLISHMENT_ATTENDANCES'
         ,xxas_hr_api_transaction_pkg.varchar2_value(pend.person_id, pend.api_name, pend.transaction_step_id,'P_PEA_ATTRIBUTE_CATEGORY')
         ,'ATTRIBUTE10'
         ,xxas_hr_api_transaction_pkg.varchar2_value(pend.person_id, pend.api_name, pend.transaction_step_id,'P_PEA_ATTRIBUTE10')) pea_attribute10_meaning_p
      ,xxas_hr_api_transaction_pkg.varchar2_value(pend.person_id, pend.api_name, pend.transaction_step_id,'P_PEA_ATTRIBUTE11') pea_attribute11_p
      ,xxas_com_vset_pkg.decode_descr_vset_value
         ('800','PER_ESTABLISHMENT_ATTENDANCES'
         ,xxas_hr_api_transaction_pkg.varchar2_value(pend.person_id, pend.api_name, pend.transaction_step_id,'P_PEA_ATTRIBUTE_CATEGORY')
         ,'ATTRIBUTE11'
         ,xxas_hr_api_transaction_pkg.varchar2_value(pend.person_id, pend.api_name, pend.transaction_step_id,'P_PEA_ATTRIBUTE11')) pea_attribute11_meaning_p
      ,xxas_hr_api_transaction_pkg.varchar2_value(pend.person_id, pend.api_name, pend.transaction_step_id,'P_PEA_ATTRIBUTE12') pea_attribute12_p
      ,xxas_com_vset_pkg.decode_descr_vset_value
         ('800','PER_ESTABLISHMENT_ATTENDANCES'
         ,xxas_hr_api_transaction_pkg.varchar2_value(pend.person_id, pend.api_name, pend.transaction_step_id,'P_PEA_ATTRIBUTE_CATEGORY')
         ,'ATTRIBUTE12'
         ,xxas_hr_api_transaction_pkg.varchar2_value(pend.person_id, pend.api_name, pend.transaction_step_id,'P_PEA_ATTRIBUTE12')) pea_attribute12_meaning_p
      ,xxas_hr_api_transaction_pkg.varchar2_value(pend.person_id, pend.api_name, pend.transaction_step_id,'P_PEA_ATTRIBUTE13') pea_attribute13_p
      ,xxas_com_vset_pkg.decode_descr_vset_value
         ('800','PER_ESTABLISHMENT_ATTENDANCES'
         ,xxas_hr_api_transaction_pkg.varchar2_value(pend.person_id, pend.api_name, pend.transaction_step_id,'P_PEA_ATTRIBUTE_CATEGORY')
         ,'ATTRIBUTE13'
         ,xxas_hr_api_transaction_pkg.varchar2_value(pend.person_id, pend.api_name, pend.transaction_step_id,'P_PEA_ATTRIBUTE13')) pea_attribute13_meaning_p
      ,xxas_hr_api_transaction_pkg.varchar2_value(pend.person_id, pend.api_name, pend.transaction_step_id,'P_PEA_ATTRIBUTE14') pea_attribute14_p
      ,xxas_com_vset_pkg.decode_descr_vset_value
         ('800','PER_ESTABLISHMENT_ATTENDANCES'
         ,xxas_hr_api_transaction_pkg.varchar2_value(pend.person_id, pend.api_name, pend.transaction_step_id,'P_PEA_ATTRIBUTE_CATEGORY')
         ,'ATTRIBUTE14'
         ,xxas_hr_api_transaction_pkg.varchar2_value(pend.person_id, pend.api_name, pend.transaction_step_id,'P_PEA_ATTRIBUTE14')) pea_attribute14_meaning_p
      ,xxas_hr_api_transaction_pkg.varchar2_value(pend.person_id, pend.api_name, pend.transaction_step_id,'P_PEA_ATTRIBUTE15') pea_attribute15_p
      ,xxas_com_vset_pkg.decode_descr_vset_value
         ('800','PER_ESTABLISHMENT_ATTENDANCES'
         ,xxas_hr_api_transaction_pkg.varchar2_value(pend.person_id, pend.api_name, pend.transaction_step_id,'P_PEA_ATTRIBUTE_CATEGORY')
         ,'ATTRIBUTE15'
         ,xxas_hr_api_transaction_pkg.varchar2_value(pend.person_id, pend.api_name, pend.transaction_step_id,'P_PEA_ATTRIBUTE15')) pea_attribute15_meaning_p
      ,xxas_hr_api_transaction_pkg.varchar2_value(pend.person_id, pend.api_name, pend.transaction_step_id,'P_PEA_ATTRIBUTE16') pea_attribute16_p
      ,xxas_com_vset_pkg.decode_descr_vset_value
         ('800','PER_ESTABLISHMENT_ATTENDANCES'
         ,xxas_hr_api_transaction_pkg.varchar2_value(pend.person_id, pend.api_name, pend.transaction_step_id,'P_PEA_ATTRIBUTE_CATEGORY')
         ,'ATTRIBUTE16'
         ,xxas_hr_api_transaction_pkg.varchar2_value(pend.person_id, pend.api_name, pend.transaction_step_id,'P_PEA_ATTRIBUTE16')) pea_attribute16_meaning_p
      ,xxas_hr_api_transaction_pkg.varchar2_value(pend.person_id, pend.api_name, pend.transaction_step_id,'P_PEA_ATTRIBUTE17') pea_attribute17_p
      ,xxas_com_vset_pkg.decode_descr_vset_value
         ('800','PER_ESTABLISHMENT_ATTENDANCES'
         ,xxas_hr_api_transaction_pkg.varchar2_value(pend.person_id, pend.api_name, pend.transaction_step_id,'P_PEA_ATTRIBUTE_CATEGORY')
         ,'ATTRIBUTE17'
         ,xxas_hr_api_transaction_pkg.varchar2_value(pend.person_id, pend.api_name, pend.transaction_step_id,'P_PEA_ATTRIBUTE17')) pea_attribute17_meaning_p
      ,xxas_hr_api_transaction_pkg.varchar2_value(pend.person_id, pend.api_name, pend.transaction_step_id,'P_PEA_ATTRIBUTE18') pea_attribute18_p
      ,xxas_com_vset_pkg.decode_descr_vset_value
         ('800','PER_ESTABLISHMENT_ATTENDANCES'
         ,xxas_hr_api_transaction_pkg.varchar2_value(pend.person_id, pend.api_name, pend.transaction_step_id,'P_PEA_ATTRIBUTE_CATEGORY')
         ,'ATTRIBUTE18'
         ,xxas_hr_api_transaction_pkg.varchar2_value(pend.person_id, pend.api_name, pend.transaction_step_id,'P_PEA_ATTRIBUTE18')) pea_attribute18_meaning_p
      ,xxas_hr_api_transaction_pkg.varchar2_value(pend.person_id, pend.api_name, pend.transaction_step_id,'P_PEA_ATTRIBUTE19') pea_attribute19_p
      ,xxas_com_vset_pkg.decode_descr_vset_value
         ('800','PER_ESTABLISHMENT_ATTENDANCES'
         ,xxas_hr_api_transaction_pkg.varchar2_value(pend.person_id, pend.api_name, pend.transaction_step_id,'P_PEA_ATTRIBUTE_CATEGORY')
         ,'ATTRIBUTE19'
         ,xxas_hr_api_transaction_pkg.varchar2_value(pend.person_id, pend.api_name, pend.transaction_step_id,'P_PEA_ATTRIBUTE19')) pea_attribute19_meaning_p
      ,xxas_hr_api_transaction_pkg.varchar2_value(pend.person_id, pend.api_name, pend.transaction_step_id,'P_PEA_ATTRIBUTE20') pea_attribute20_p
      ,xxas_com_vset_pkg.decode_descr_vset_value
         ('800','PER_ESTABLISHMENT_ATTENDANCES'
         ,xxas_hr_api_transaction_pkg.varchar2_value(pend.person_id, pend.api_name, pend.transaction_step_id,'P_PEA_ATTRIBUTE_CATEGORY')
         ,'ATTRIBUTE20'
         ,xxas_hr_api_transaction_pkg.varchar2_value(pend.person_id, pend.api_name, pend.transaction_step_id,'P_PEA_ATTRIBUTE20')) pea_attribute20_meaning_p
      ,xxas_hr_api_transaction_pkg.varchar2_value(pend.person_id, pend.api_name, pend.transaction_step_id,'P_QUALIFICATION_ID') qualification_id_p
      ,xxas_hr_api_transaction_pkg.varchar2_value(pend.person_id, pend.api_name, pend.transaction_step_id,'P_PQ_TITLE') title_p
      ,xxas_hr_api_transaction_pkg.varchar2_value(pend.person_id, pend.api_name, pend.transaction_step_id,'P_QUALIFICATION_TYPE_ID') qualification_type_id_p
      ,decode
        (xxas_hr_api_transaction_pkg.varchar2_value(pend.person_id, pend.api_name, pend.transaction_step_id,'P_QUALIFICATION_TYPE_ID')
        ,null, null
        ,(SELECT pend_pqtc.category
          FROM   per_qualification_types pend_pqtc
          WHERE  pend_pqtc.qualification_type_id = 
            xxas_hr_api_transaction_pkg.varchar2_value(pend.person_id, pend.api_name, pend.transaction_step_id,'P_QUALIFICATION_TYPE_ID'))) pq_category_code_p
      ,decode
        (xxas_hr_api_transaction_pkg.varchar2_value(pend.person_id, pend.api_name, pend.transaction_step_id,'P_QUALIFICATION_TYPE_ID')
        ,null, null
        ,(SELECT xxas_util_pkg.decode_lookup('PER_CATEGORIES', pend_pqtm.category) 
          FROM   per_qualification_types pend_pqtm
          WHERE  pend_pqtm.qualification_type_id = 
            xxas_hr_api_transaction_pkg.varchar2_value(pend.person_id, pend.api_name, pend.transaction_step_id,'P_QUALIFICATION_TYPE_ID'))) pq_category_meaning_p
      ,decode
        (xxas_hr_api_transaction_pkg.varchar2_value(pend.person_id, pend.api_name, pend.transaction_step_id,'P_QUALIFICATION_TYPE_ID')
        ,null, null
        ,(SELECT pend_pqtr.rank 
          FROM   per_qualification_types pend_pqtr
          WHERE  pend_pqtr.qualification_type_id = 
            xxas_hr_api_transaction_pkg.varchar2_value(pend.person_id, pend.api_name, pend.transaction_step_id,'P_QUALIFICATION_TYPE_ID'))) rank_p
      ,decode
        (xxas_hr_api_transaction_pkg.varchar2_value(pend.person_id, pend.api_name, pend.transaction_step_id,'P_QUALIFICATION_TYPE_ID')
        ,null, null
        ,(SELECT pend_pqtn.name 
          FROM   per_qualification_types pend_pqtn
          WHERE  pend_pqtn.qualification_type_id = 
            xxas_hr_api_transaction_pkg.varchar2_value(pend.person_id, pend.api_name, pend.transaction_step_id,'P_QUALIFICATION_TYPE_ID'))) qualification_name_p
      ,xxas_hr_api_transaction_pkg.varchar2_value(pend.person_id, pend.api_name, pend.transaction_step_id,'P_PQ_GRADE_ATTAINED') grade_attained_p
      ,xxas_hr_api_transaction_pkg.varchar2_value(pend.person_id, pend.api_name, pend.transaction_step_id,'P_PQ_STATUS') status_code_p
      ,xxas_util_pkg.decode_lookup
        ('PER_SUBJECT_STATUSES'
        ,xxas_hr_api_transaction_pkg.varchar2_value(pend.person_id, pend.api_name, pend.transaction_step_id,'P_PQ_STATUS')) status_meaning_p
      ,xxas_hr_api_transaction_pkg.date_value(pend.person_id, pend.api_name, pend.transaction_step_id,'P_PQ_AWARDED_DATE') awarded_date_p
      ,xxas_hr_api_transaction_pkg.varchar2_value(pend.person_id, pend.api_name, pend.transaction_step_id,'P_PQ_FEE') fee_p
      ,xxas_hr_api_transaction_pkg.varchar2_value(pend.person_id, pend.api_name, pend.transaction_step_id,'P_PQ_FEE_CURRENCY') fee_currency_code_p
      ,decode
        (xxas_hr_api_transaction_pkg.varchar2_value(pend.person_id, pend.api_name, pend.transaction_step_id,'P_PQ_FEE_CURRENCY')
        ,null, null
        ,(SELECT pend_fc.name
          FROM   fnd_currencies_vl pend_fc
          WHERE  pend_fc.currency_code = 
            xxas_hr_api_transaction_pkg.varchar2_value(pend.person_id, pend.api_name, pend.transaction_step_id,'P_PQ_FEE_CURRENCY'))) fee_currency_meaning_p
      ,xxas_hr_api_transaction_pkg.varchar2_value(pend.person_id, pend.api_name, pend.transaction_step_id,'P_PQ_COMPLETED_AMOUNT') training_completed_amount_p
      ,xxas_hr_api_transaction_pkg.varchar2_value(pend.person_id, pend.api_name, pend.transaction_step_id,'P_PQ_REIMBURSEMENT') reimbursement_arrangements_p
      ,xxas_hr_api_transaction_pkg.varchar2_value(pend.person_id, pend.api_name, pend.transaction_step_id,'P_PQ_COMPLETED_UNITS') training_completed_units_p
      ,xxas_hr_api_transaction_pkg.varchar2_value(pend.person_id, pend.api_name, pend.transaction_step_id,'P_PQ_TOTAL_AMOUNT') total_training_amount_p
      ,xxas_hr_api_transaction_pkg.date_value(pend.person_id, pend.api_name, pend.transaction_step_id,'P_PQ_START_DATE') start_date_p
      ,xxas_hr_api_transaction_pkg.date_value(pend.person_id, pend.api_name, pend.transaction_step_id,'P_PQ_END_DATE') end_date_p
      ,xxas_hr_api_transaction_pkg.varchar2_value(pend.person_id, pend.api_name, pend.transaction_step_id,'P_PQ_LICENSE_NUMBER') license_number_p
      ,xxas_hr_api_transaction_pkg.date_value(pend.person_id, pend.api_name, pend.transaction_step_id,'P_PQ_EXPIRY_DATE') expiry_date_p
      ,xxas_hr_api_transaction_pkg.varchar2_value(pend.person_id, pend.api_name, pend.transaction_step_id,'P_PQ_LICENSE_RESTRICTIONS') license_restrictions_p
      ,null projected_completion_date_p
      ,xxas_hr_api_transaction_pkg.varchar2_value(pend.person_id, pend.api_name, pend.transaction_step_id,'P_PQ_AWARDING_BODY') awarding_body_p
      ,xxas_hr_api_transaction_pkg.varchar2_value(pend.person_id, pend.api_name, pend.transaction_step_id,'P_PQ_TUITION_METHOD') tuition_method_p
      ,xxas_hr_api_transaction_pkg.varchar2_value(pend.person_id, pend.api_name, pend.transaction_step_id,'P_PQ_GROUP_RANKING') group_ranking_p
      ,null professional_body_name_p
      ,null membership_number_p
      ,null membership_category_p
      ,null subscription_payment_method_p
      ,xxas_hr_api_transaction_pkg.varchar2_value(pend.person_id, pend.api_name, pend.transaction_step_id,'P_PQ_COMMENTS') comments_p
      ,xxas_hr_api_transaction_pkg.varchar2_value(pend.person_id, pend.api_name, pend.transaction_step_id,'P_PQ_ATTRIBUTE_CATEGORY') qua_attribute_category_p
      ,xxas_hr_api_transaction_pkg.varchar2_value(pend.person_id, pend.api_name, pend.transaction_step_id,'P_PQ_ATTRIBUTE1') qua_attribute1_p
      ,xxas_com_vset_pkg.decode_descr_vset_value
         ('800','PER_QUALIFICATIONS'
         ,xxas_hr_api_transaction_pkg.varchar2_value(pend.person_id, pend.api_name, pend.transaction_step_id,'P_PQ_ATTRIBUTE_CATEGORY')
         ,'ATTRIBUTE1'
         ,xxas_hr_api_transaction_pkg.varchar2_value(pend.person_id, pend.api_name, pend.transaction_step_id,'P_PQ_ATTRIBUTE1')) qua_attribute1_meaning_p
      ,xxas_hr_api_transaction_pkg.varchar2_value(pend.person_id, pend.api_name, pend.transaction_step_id,'P_PQ_ATTRIBUTE2') qua_attribute2_p
      ,xxas_com_vset_pkg.decode_descr_vset_value
         ('800','PER_QUALIFICATIONS'
         ,xxas_hr_api_transaction_pkg.varchar2_value(pend.person_id, pend.api_name, pend.transaction_step_id,'P_PQ_ATTRIBUTE_CATEGORY')
         ,'ATTRIBUTE2'
         ,xxas_hr_api_transaction_pkg.varchar2_value(pend.person_id, pend.api_name, pend.transaction_step_id,'P_PQ_ATTRIBUTE2')) qua_attribute2_meaning_p
      ,xxas_hr_api_transaction_pkg.varchar2_value(pend.person_id, pend.api_name, pend.transaction_step_id,'P_PQ_ATTRIBUTE3') qua_attribute3_p
      ,xxas_com_vset_pkg.decode_descr_vset_value
         ('800','PER_QUALIFICATIONS'
         ,xxas_hr_api_transaction_pkg.varchar2_value(pend.person_id, pend.api_name, pend.transaction_step_id,'P_PQ_ATTRIBUTE_CATEGORY')
         ,'ATTRIBUTE3'
         ,xxas_hr_api_transaction_pkg.varchar2_value(pend.person_id, pend.api_name, pend.transaction_step_id,'P_PQ_ATTRIBUTE3')) qua_attribute3_meaning_p
      ,xxas_hr_api_transaction_pkg.varchar2_value(pend.person_id, pend.api_name, pend.transaction_step_id,'P_PQ_ATTRIBUTE4') qua_attribute4_p
      ,xxas_com_vset_pkg.decode_descr_vset_value
         ('800','PER_QUALIFICATIONS'
         ,xxas_hr_api_transaction_pkg.varchar2_value(pend.person_id, pend.api_name, pend.transaction_step_id,'P_PQ_ATTRIBUTE_CATEGORY')
         ,'ATTRIBUTE4'
         ,xxas_hr_api_transaction_pkg.varchar2_value(pend.person_id, pend.api_name, pend.transaction_step_id,'P_PQ_ATTRIBUTE4')) qua_attribute4_meaning_p
      ,xxas_hr_api_transaction_pkg.varchar2_value(pend.person_id, pend.api_name, pend.transaction_step_id,'P_PQ_ATTRIBUTE5') qua_attribute5_p
      ,xxas_com_vset_pkg.decode_descr_vset_value
         ('800','PER_QUALIFICATIONS'
         ,xxas_hr_api_transaction_pkg.varchar2_value(pend.person_id, pend.api_name, pend.transaction_step_id,'P_PQ_ATTRIBUTE_CATEGORY')
         ,'ATTRIBUTE5'
         ,xxas_hr_api_transaction_pkg.varchar2_value(pend.person_id, pend.api_name, pend.transaction_step_id,'P_PQ_ATTRIBUTE5')) qua_attribute5_meaning_p
      ,xxas_hr_api_transaction_pkg.varchar2_value(pend.person_id, pend.api_name, pend.transaction_step_id,'P_PQ_ATTRIBUTE6') qua_attribute6_p
      ,xxas_com_vset_pkg.decode_descr_vset_value
         ('800','PER_QUALIFICATIONS'
         ,xxas_hr_api_transaction_pkg.varchar2_value(pend.person_id, pend.api_name, pend.transaction_step_id,'P_PQ_ATTRIBUTE_CATEGORY')
         ,'ATTRIBUTE6'
         ,xxas_hr_api_transaction_pkg.varchar2_value(pend.person_id, pend.api_name, pend.transaction_step_id,'P_PQ_ATTRIBUTE6')) qua_attribute6_meaning_p
      ,xxas_hr_api_transaction_pkg.varchar2_value(pend.person_id, pend.api_name, pend.transaction_step_id,'P_PQ_ATTRIBUTE7') qua_attribute7_p
      ,xxas_com_vset_pkg.decode_descr_vset_value
         ('800','PER_QUALIFICATIONS'
         ,xxas_hr_api_transaction_pkg.varchar2_value(pend.person_id, pend.api_name, pend.transaction_step_id,'P_PQ_ATTRIBUTE_CATEGORY')
         ,'ATTRIBUTE7'
         ,xxas_hr_api_transaction_pkg.varchar2_value(pend.person_id, pend.api_name, pend.transaction_step_id,'P_PQ_ATTRIBUTE7')) qua_attribute7_meaning_p
      ,xxas_hr_api_transaction_pkg.varchar2_value(pend.person_id, pend.api_name, pend.transaction_step_id,'P_PQ_ATTRIBUTE8') qua_attribute8_p
      ,xxas_com_vset_pkg.decode_descr_vset_value
         ('800','PER_QUALIFICATIONS'
         ,xxas_hr_api_transaction_pkg.varchar2_value(pend.person_id, pend.api_name, pend.transaction_step_id,'P_PQ_ATTRIBUTE_CATEGORY')
         ,'ATTRIBUTE8'
         ,xxas_hr_api_transaction_pkg.varchar2_value(pend.person_id, pend.api_name, pend.transaction_step_id,'P_PQ_ATTRIBUTE8')) qua_attribute8_meaning_p
      ,xxas_hr_api_transaction_pkg.varchar2_value(pend.person_id, pend.api_name, pend.transaction_step_id,'P_PQ_ATTRIBUTE9') qua_attribute9_p
      ,xxas_com_vset_pkg.decode_descr_vset_value
         ('800','PER_QUALIFICATIONS'
         ,xxas_hr_api_transaction_pkg.varchar2_value(pend.person_id, pend.api_name, pend.transaction_step_id,'P_PQ_ATTRIBUTE_CATEGORY')
         ,'ATTRIBUTE9'
         ,xxas_hr_api_transaction_pkg.varchar2_value(pend.person_id, pend.api_name, pend.transaction_step_id,'P_PQ_ATTRIBUTE9')) qua_attribute9_meaning_p
      ,xxas_hr_api_transaction_pkg.varchar2_value(pend.person_id, pend.api_name, pend.transaction_step_id,'P_PQ_ATTRIBUTE10') qua_attribute10_p
      ,xxas_com_vset_pkg.decode_descr_vset_value
         ('800','PER_QUALIFICATIONS'
         ,xxas_hr_api_transaction_pkg.varchar2_value(pend.person_id, pend.api_name, pend.transaction_step_id,'P_PQ_ATTRIBUTE_CATEGORY')
         ,'ATTRIBUTE10'
         ,xxas_hr_api_transaction_pkg.varchar2_value(pend.person_id, pend.api_name, pend.transaction_step_id,'P_PQ_ATTRIBUTE10')) qua_attribute10_meaning_p
      ,xxas_hr_api_transaction_pkg.varchar2_value(pend.person_id, pend.api_name, pend.transaction_step_id,'P_PQ_ATTRIBUTE11') qua_attribute11_p
      ,xxas_com_vset_pkg.decode_descr_vset_value
         ('800','PER_QUALIFICATIONS'
         ,xxas_hr_api_transaction_pkg.varchar2_value(pend.person_id, pend.api_name, pend.transaction_step_id,'P_PQ_ATTRIBUTE_CATEGORY')
         ,'ATTRIBUTE11'
         ,xxas_hr_api_transaction_pkg.varchar2_value(pend.person_id, pend.api_name, pend.transaction_step_id,'P_PQ_ATTRIBUTE11')) qua_attribute11_meaning_p
      ,xxas_hr_api_transaction_pkg.varchar2_value(pend.person_id, pend.api_name, pend.transaction_step_id,'P_PQ_ATTRIBUTE12') qua_attribute12_p
      ,xxas_com_vset_pkg.decode_descr_vset_value
         ('800','PER_QUALIFICATIONS'
         ,xxas_hr_api_transaction_pkg.varchar2_value(pend.person_id, pend.api_name, pend.transaction_step_id,'P_PQ_ATTRIBUTE_CATEGORY')
         ,'ATTRIBUTE12'
         ,xxas_hr_api_transaction_pkg.varchar2_value(pend.person_id, pend.api_name, pend.transaction_step_id,'P_PQ_ATTRIBUTE12')) qua_attribute12_meaning_p
      ,xxas_hr_api_transaction_pkg.varchar2_value(pend.person_id, pend.api_name, pend.transaction_step_id,'P_PQ_ATTRIBUTE13') qua_attribute13_p
      ,xxas_com_vset_pkg.decode_descr_vset_value
         ('800','PER_QUALIFICATIONS'
         ,xxas_hr_api_transaction_pkg.varchar2_value(pend.person_id, pend.api_name, pend.transaction_step_id,'P_PQ_ATTRIBUTE_CATEGORY')
         ,'ATTRIBUTE13'
         ,xxas_hr_api_transaction_pkg.varchar2_value(pend.person_id, pend.api_name, pend.transaction_step_id,'P_PQ_ATTRIBUTE13')) qua_attribute13_meaning_p
      ,xxas_hr_api_transaction_pkg.varchar2_value(pend.person_id, pend.api_name, pend.transaction_step_id,'P_PQ_ATTRIBUTE14') qua_attribute14_p
      ,xxas_com_vset_pkg.decode_descr_vset_value
         ('800','PER_QUALIFICATIONS'
         ,xxas_hr_api_transaction_pkg.varchar2_value(pend.person_id, pend.api_name, pend.transaction_step_id,'P_PQ_ATTRIBUTE_CATEGORY')
         ,'ATTRIBUTE14'
         ,xxas_hr_api_transaction_pkg.varchar2_value(pend.person_id, pend.api_name, pend.transaction_step_id,'P_PQ_ATTRIBUTE14')) qua_attribute14_meaning_p
      ,xxas_hr_api_transaction_pkg.varchar2_value(pend.person_id, pend.api_name, pend.transaction_step_id,'P_PQ_ATTRIBUTE15') qua_attribute15_p
      ,xxas_com_vset_pkg.decode_descr_vset_value
         ('800','PER_QUALIFICATIONS'
         ,xxas_hr_api_transaction_pkg.varchar2_value(pend.person_id, pend.api_name, pend.transaction_step_id,'P_PQ_ATTRIBUTE_CATEGORY')
         ,'ATTRIBUTE15'
         ,xxas_hr_api_transaction_pkg.varchar2_value(pend.person_id, pend.api_name, pend.transaction_step_id,'P_PQ_ATTRIBUTE15')) qua_attribute15_meaning_p
      ,xxas_hr_api_transaction_pkg.varchar2_value(pend.person_id, pend.api_name, pend.transaction_step_id,'P_PQ_ATTRIBUTE16') qua_attribute16_p
      ,xxas_com_vset_pkg.decode_descr_vset_value
         ('800','PER_QUALIFICATIONS'
         ,xxas_hr_api_transaction_pkg.varchar2_value(pend.person_id, pend.api_name, pend.transaction_step_id,'P_PQ_ATTRIBUTE_CATEGORY')
         ,'ATTRIBUTE16'
         ,xxas_hr_api_transaction_pkg.varchar2_value(pend.person_id, pend.api_name, pend.transaction_step_id,'P_PQ_ATTRIBUTE16')) qua_attribute16_meaning_p
      ,xxas_hr_api_transaction_pkg.varchar2_value(pend.person_id, pend.api_name, pend.transaction_step_id,'P_PQ_ATTRIBUTE17') qua_attribute17_p
      ,xxas_com_vset_pkg.decode_descr_vset_value
         ('800','PER_QUALIFICATIONS'
         ,xxas_hr_api_transaction_pkg.varchar2_value(pend.person_id, pend.api_name, pend.transaction_step_id,'P_PQ_ATTRIBUTE_CATEGORY')
         ,'ATTRIBUTE17'
         ,xxas_hr_api_transaction_pkg.varchar2_value(pend.person_id, pend.api_name, pend.transaction_step_id,'P_PQ_ATTRIBUTE17')) qua_attribute17_meaning_p
      ,xxas_hr_api_transaction_pkg.varchar2_value(pend.person_id, pend.api_name, pend.transaction_step_id,'P_PQ_ATTRIBUTE18') qua_attribute18_p
      ,xxas_com_vset_pkg.decode_descr_vset_value
         ('800','PER_QUALIFICATIONS'
         ,xxas_hr_api_transaction_pkg.varchar2_value(pend.person_id, pend.api_name, pend.transaction_step_id,'P_PQ_ATTRIBUTE_CATEGORY')
         ,'ATTRIBUTE18'
         ,xxas_hr_api_transaction_pkg.varchar2_value(pend.person_id, pend.api_name, pend.transaction_step_id,'P_PQ_ATTRIBUTE18')) qua_attribute18_meaning_p
      ,xxas_hr_api_transaction_pkg.varchar2_value(pend.person_id, pend.api_name, pend.transaction_step_id,'P_PQ_ATTRIBUTE19') qua_attribute19_p
      ,xxas_com_vset_pkg.decode_descr_vset_value
         ('800','PER_QUALIFICATIONS'
         ,xxas_hr_api_transaction_pkg.varchar2_value(pend.person_id, pend.api_name, pend.transaction_step_id,'P_PQ_ATTRIBUTE_CATEGORY')
         ,'ATTRIBUTE19'
         ,xxas_hr_api_transaction_pkg.varchar2_value(pend.person_id, pend.api_name, pend.transaction_step_id,'P_PQ_ATTRIBUTE19')) qua_attribute19_meaning_p
      ,xxas_hr_api_transaction_pkg.varchar2_value(pend.person_id, pend.api_name, pend.transaction_step_id,'P_PQ_ATTRIBUTE20') qua_attribute20_p
      ,xxas_com_vset_pkg.decode_descr_vset_value
         ('800','PER_QUALIFICATIONS'
         ,xxas_hr_api_transaction_pkg.varchar2_value(pend.person_id, pend.api_name, pend.transaction_step_id,'P_PQ_ATTRIBUTE_CATEGORY')
         ,'ATTRIBUTE20'
         ,xxas_hr_api_transaction_pkg.varchar2_value(pend.person_id, pend.api_name, pend.transaction_step_id,'P_PQ_ATTRIBUTE20')) qua_attribute20_meaning_p
      ,xxas_hr_api_transaction_pkg.varchar2_value(pend.person_id, pend.api_name, pend.transaction_step_id,'P_PQ_QUA_INFORMATION_CATEGORY') qua_information_category_p
      ,xxas_hr_api_transaction_pkg.varchar2_value(pend.person_id, pend.api_name, pend.transaction_step_id,'P_PQ_QUA_INFORMATION1') qua_information1_p
      ,xxas_com_vset_pkg.decode_descr_vset_value
         ('800','Qualification Developer DF'
         ,xxas_hr_api_transaction_pkg.varchar2_value(pend.person_id, pend.api_name, pend.transaction_step_id,'P_PQ_QUA_INFORMATION_CATEGORY')
         ,'QUA_INFORMATION1'
         ,xxas_hr_api_transaction_pkg.varchar2_value(pend.person_id, pend.api_name, pend.transaction_step_id,'P_PQ_QUA_INFORMATION1')) qua_information1_meaning_p
      ,xxas_hr_api_transaction_pkg.varchar2_value(pend.person_id, pend.api_name, pend.transaction_step_id,'P_PQ_QUA_INFORMATION2') qua_information2_p
      ,xxas_com_vset_pkg.decode_descr_vset_value
         ('800','Qualification Developer DF'
         ,xxas_hr_api_transaction_pkg.varchar2_value(pend.person_id, pend.api_name, pend.transaction_step_id,'P_PQ_QUA_INFORMATION_CATEGORY')
         ,'QUA_INFORMATION2'
         ,xxas_hr_api_transaction_pkg.varchar2_value(pend.person_id, pend.api_name, pend.transaction_step_id,'P_PQ_QUA_INFORMATION2')) qua_information2_meaning_p
      ,xxas_hr_api_transaction_pkg.varchar2_value(pend.person_id, pend.api_name, pend.transaction_step_id,'P_PQ_QUA_INFORMATION3') qua_information3_p
      ,xxas_com_vset_pkg.decode_descr_vset_value
         ('800','Qualification Developer DF'
         ,xxas_hr_api_transaction_pkg.varchar2_value(pend.person_id, pend.api_name, pend.transaction_step_id,'P_PQ_QUA_INFORMATION_CATEGORY')
         ,'QUA_INFORMATION3'
         ,xxas_hr_api_transaction_pkg.varchar2_value(pend.person_id, pend.api_name, pend.transaction_step_id,'P_PQ_QUA_INFORMATION3')) qua_information3_meaning_p
      ,xxas_hr_api_transaction_pkg.varchar2_value(pend.person_id, pend.api_name, pend.transaction_step_id,'P_PQ_QUA_INFORMATION4') qua_information4_p
      ,xxas_com_vset_pkg.decode_descr_vset_value
         ('800','Qualification Developer DF'
         ,xxas_hr_api_transaction_pkg.varchar2_value(pend.person_id, pend.api_name, pend.transaction_step_id,'P_PQ_QUA_INFORMATION_CATEGORY')
         ,'QUA_INFORMATION4'
         ,xxas_hr_api_transaction_pkg.varchar2_value(pend.person_id, pend.api_name, pend.transaction_step_id,'P_PQ_QUA_INFORMATION4')) qua_information4_meaning_p
      ,xxas_hr_api_transaction_pkg.varchar2_value(pend.person_id, pend.api_name, pend.transaction_step_id,'P_PQ_QUA_INFORMATION5') qua_information5_p
      ,xxas_com_vset_pkg.decode_descr_vset_value
         ('800','Qualification Developer DF'
         ,xxas_hr_api_transaction_pkg.varchar2_value(pend.person_id, pend.api_name, pend.transaction_step_id,'P_PQ_QUA_INFORMATION_CATEGORY')
         ,'QUA_INFORMATION5'
         ,xxas_hr_api_transaction_pkg.varchar2_value(pend.person_id, pend.api_name, pend.transaction_step_id,'P_PQ_QUA_INFORMATION5')) qua_information5_meaning_p
      ,xxas_hr_api_transaction_pkg.varchar2_value(pend.person_id, pend.api_name, pend.transaction_step_id,'P_PQ_QUA_INFORMATION6') qua_information6_p
      ,xxas_com_vset_pkg.decode_descr_vset_value
         ('800','Qualification Developer DF'
         ,xxas_hr_api_transaction_pkg.varchar2_value(pend.person_id, pend.api_name, pend.transaction_step_id,'P_PQ_QUA_INFORMATION_CATEGORY')
         ,'QUA_INFORMATION6'
         ,xxas_hr_api_transaction_pkg.varchar2_value(pend.person_id, pend.api_name, pend.transaction_step_id,'P_PQ_QUA_INFORMATION6')) qua_information6_meaning_p
      ,xxas_hr_api_transaction_pkg.varchar2_value(pend.person_id, pend.api_name, pend.transaction_step_id,'P_PQ_QUA_INFORMATION7') qua_information7_p
      ,xxas_com_vset_pkg.decode_descr_vset_value
         ('800','Qualification Developer DF'
         ,xxas_hr_api_transaction_pkg.varchar2_value(pend.person_id, pend.api_name, pend.transaction_step_id,'P_PQ_QUA_INFORMATION_CATEGORY')
         ,'QUA_INFORMATION7'
         ,xxas_hr_api_transaction_pkg.varchar2_value(pend.person_id, pend.api_name, pend.transaction_step_id,'P_PQ_QUA_INFORMATION7')) qua_information7_meaning_p
      ,xxas_hr_api_transaction_pkg.varchar2_value(pend.person_id, pend.api_name, pend.transaction_step_id,'P_PQ_QUA_INFORMATION8') qua_information8_p
      ,xxas_com_vset_pkg.decode_descr_vset_value
         ('800','Qualification Developer DF'
         ,xxas_hr_api_transaction_pkg.varchar2_value(pend.person_id, pend.api_name, pend.transaction_step_id,'P_PQ_QUA_INFORMATION_CATEGORY')
         ,'QUA_INFORMATION8'
         ,xxas_hr_api_transaction_pkg.varchar2_value(pend.person_id, pend.api_name, pend.transaction_step_id,'P_PQ_QUA_INFORMATION8')) qua_information8_meaning_p
      ,xxas_hr_api_transaction_pkg.varchar2_value(pend.person_id, pend.api_name, pend.transaction_step_id,'P_PQ_QUA_INFORMATION9') qua_information9_p
      ,xxas_com_vset_pkg.decode_descr_vset_value
         ('800','Qualification Developer DF'
         ,xxas_hr_api_transaction_pkg.varchar2_value(pend.person_id, pend.api_name, pend.transaction_step_id,'P_PQ_QUA_INFORMATION_CATEGORY')
         ,'QUA_INFORMATION9'
         ,xxas_hr_api_transaction_pkg.varchar2_value(pend.person_id, pend.api_name, pend.transaction_step_id,'P_PQ_QUA_INFORMATION9')) qua_information9_meaning_p
      ,xxas_hr_api_transaction_pkg.varchar2_value(pend.person_id, pend.api_name, pend.transaction_step_id,'P_PQ_QUA_INFORMATION10') qua_information10_p
      ,xxas_com_vset_pkg.decode_descr_vset_value
         ('800','Qualification Developer DF'
         ,xxas_hr_api_transaction_pkg.varchar2_value(pend.person_id, pend.api_name, pend.transaction_step_id,'P_PQ_QUA_INFORMATION_CATEGORY')
         ,'QUA_INFORMATION10'
         ,xxas_hr_api_transaction_pkg.varchar2_value(pend.person_id, pend.api_name, pend.transaction_step_id,'P_PQ_QUA_INFORMATION10')) qua_information10_meaning_p
      ,xxas_hr_api_transaction_pkg.varchar2_value(pend.person_id, pend.api_name, pend.transaction_step_id,'P_PQ_QUA_INFORMATION11') qua_information11_p
      ,xxas_com_vset_pkg.decode_descr_vset_value
         ('800','Qualification Developer DF'
         ,xxas_hr_api_transaction_pkg.varchar2_value(pend.person_id, pend.api_name, pend.transaction_step_id,'P_PQ_QUA_INFORMATION_CATEGORY')
         ,'QUA_INFORMATION11'
         ,xxas_hr_api_transaction_pkg.varchar2_value(pend.person_id, pend.api_name, pend.transaction_step_id,'P_PQ_QUA_INFORMATION11')) qua_information11_meaning_p
      ,xxas_hr_api_transaction_pkg.varchar2_value(pend.person_id, pend.api_name, pend.transaction_step_id,'P_PQ_QUA_INFORMATION12') qua_information12_p
      ,xxas_com_vset_pkg.decode_descr_vset_value
         ('800','Qualification Developer DF'
         ,xxas_hr_api_transaction_pkg.varchar2_value(pend.person_id, pend.api_name, pend.transaction_step_id,'P_PQ_QUA_INFORMATION_CATEGORY')
         ,'QUA_INFORMATION12'
         ,xxas_hr_api_transaction_pkg.varchar2_value(pend.person_id, pend.api_name, pend.transaction_step_id,'P_PQ_QUA_INFORMATION12')) qua_information12_meaning_p
      ,xxas_hr_api_transaction_pkg.varchar2_value(pend.person_id, pend.api_name, pend.transaction_step_id,'P_PQ_QUA_INFORMATION13') qua_information13_p
      ,xxas_com_vset_pkg.decode_descr_vset_value
         ('800','Qualification Developer DF'
         ,xxas_hr_api_transaction_pkg.varchar2_value(pend.person_id, pend.api_name, pend.transaction_step_id,'P_PQ_QUA_INFORMATION_CATEGORY')
         ,'QUA_INFORMATION13'
         ,xxas_hr_api_transaction_pkg.varchar2_value(pend.person_id, pend.api_name, pend.transaction_step_id,'P_PQ_QUA_INFORMATION13')) qua_information13_meaning_p
      ,xxas_hr_api_transaction_pkg.varchar2_value(pend.person_id, pend.api_name, pend.transaction_step_id,'P_PQ_QUA_INFORMATION14') qua_information14_p
      ,xxas_com_vset_pkg.decode_descr_vset_value
         ('800','Qualification Developer DF'
         ,xxas_hr_api_transaction_pkg.varchar2_value(pend.person_id, pend.api_name, pend.transaction_step_id,'P_PQ_QUA_INFORMATION_CATEGORY')
         ,'QUA_INFORMATION14'
         ,xxas_hr_api_transaction_pkg.varchar2_value(pend.person_id, pend.api_name, pend.transaction_step_id,'P_PQ_QUA_INFORMATION14')) qua_information14_meaning_p
      ,xxas_hr_api_transaction_pkg.varchar2_value(pend.person_id, pend.api_name, pend.transaction_step_id,'P_PQ_QUA_INFORMATION15') qua_information15_p
      ,xxas_com_vset_pkg.decode_descr_vset_value
         ('800','Qualification Developer DF'
         ,xxas_hr_api_transaction_pkg.varchar2_value(pend.person_id, pend.api_name, pend.transaction_step_id,'P_PQ_QUA_INFORMATION_CATEGORY')
         ,'QUA_INFORMATION15'
         ,xxas_hr_api_transaction_pkg.varchar2_value(pend.person_id, pend.api_name, pend.transaction_step_id,'P_PQ_QUA_INFORMATION15')) qua_information15_meaning_p
      ,xxas_hr_api_transaction_pkg.varchar2_value(pend.person_id, pend.api_name, pend.transaction_step_id,'P_PQ_QUA_INFORMATION16') qua_information16_p
      ,xxas_com_vset_pkg.decode_descr_vset_value
         ('800','Qualification Developer DF'
         ,xxas_hr_api_transaction_pkg.varchar2_value(pend.person_id, pend.api_name, pend.transaction_step_id,'P_PQ_QUA_INFORMATION_CATEGORY')
         ,'QUA_INFORMATION16'
         ,xxas_hr_api_transaction_pkg.varchar2_value(pend.person_id, pend.api_name, pend.transaction_step_id,'P_PQ_QUA_INFORMATION16')) qua_information16_meaning_p
      ,xxas_hr_api_transaction_pkg.varchar2_value(pend.person_id, pend.api_name, pend.transaction_step_id,'P_PQ_QUA_INFORMATION17') qua_information17_p
      ,xxas_com_vset_pkg.decode_descr_vset_value
         ('800','Qualification Developer DF'
         ,xxas_hr_api_transaction_pkg.varchar2_value(pend.person_id, pend.api_name, pend.transaction_step_id,'P_PQ_QUA_INFORMATION_CATEGORY')
         ,'QUA_INFORMATION17'
         ,xxas_hr_api_transaction_pkg.varchar2_value(pend.person_id, pend.api_name, pend.transaction_step_id,'P_PQ_QUA_INFORMATION17')) qua_information17_meaning_p
      ,xxas_hr_api_transaction_pkg.varchar2_value(pend.person_id, pend.api_name, pend.transaction_step_id,'P_PQ_QUA_INFORMATION18') qua_information18_p
      ,xxas_com_vset_pkg.decode_descr_vset_value
         ('800','Qualification Developer DF'
         ,xxas_hr_api_transaction_pkg.varchar2_value(pend.person_id, pend.api_name, pend.transaction_step_id,'P_PQ_QUA_INFORMATION_CATEGORY')
         ,'QUA_INFORMATION18'
         ,xxas_hr_api_transaction_pkg.varchar2_value(pend.person_id, pend.api_name, pend.transaction_step_id,'P_PQ_QUA_INFORMATION18')) qua_information18_meaning_p
      ,xxas_hr_api_transaction_pkg.varchar2_value(pend.person_id, pend.api_name, pend.transaction_step_id,'P_PQ_QUA_INFORMATION19') qua_information19_p
      ,xxas_com_vset_pkg.decode_descr_vset_value
         ('800','Qualification Developer DF'
         ,xxas_hr_api_transaction_pkg.varchar2_value(pend.person_id, pend.api_name, pend.transaction_step_id,'P_PQ_QUA_INFORMATION_CATEGORY')
         ,'QUA_INFORMATION19'
         ,xxas_hr_api_transaction_pkg.varchar2_value(pend.person_id, pend.api_name, pend.transaction_step_id,'P_PQ_QUA_INFORMATION19')) qua_information19_meaning_p
      ,xxas_hr_api_transaction_pkg.varchar2_value(pend.person_id, pend.api_name, pend.transaction_step_id,'P_PQ_QUA_INFORMATION20') qua_information20_p
      ,xxas_com_vset_pkg.decode_descr_vset_value
         ('800','Qualification Developer DF'
         ,xxas_hr_api_transaction_pkg.varchar2_value(pend.person_id, pend.api_name, pend.transaction_step_id,'P_PQ_QUA_INFORMATION_CATEGORY')
         ,'QUA_INFORMATION20'
         ,xxas_hr_api_transaction_pkg.varchar2_value(pend.person_id, pend.api_name, pend.transaction_step_id,'P_PQ_QUA_INFORMATION20')) qua_information20_meaning_p
FROM  (SELECT xpea.*
             ,xpq.qualification_id
       FROM   per_establishment_attendances xpea
             ,per_qualifications xpq
       WHERE  xpea.attendance_id = xpq.attendance_id) pea
      ,per_establishments pe
      ,per_qualifications_vl pq
      ,per_qualification_types_vl pqt
      ,fnd_currencies_vl fc
      ,(SELECT   base.person_id
          ,      base.attendance_id
          ,      baseq.qualification_id
          ,      hats.api_name
          ,      hat.transaction_id
          ,      hats.transaction_step_id
          ,      hat.status  hr_api_status_code
          ,      decode(hatvd.varchar2_value, 'Y', 'DELETE', 'UPDATE') transaction_type
          ,      xxas_selfservice_workflow_pkg.transcode_status(hat.status) workflow_status_code
          ,      hat.item_type
          ,      hat.item_key
          from   per_establishment_attendances base
          ,      per_qualifications baseq
          ,      hr_api_transactions       hat
          ,      hr_api_transaction_steps  hats
          ,      hr_api_transaction_values hatv
          ,      hr_api_transaction_values hatvd
          where  base.person_id           = hat.selected_person_id
          AND    base.attendance_id       = baseq.attendance_id
          AND    hat.status          NOT IN ('D', 'N', 'W', 'ACTIVE', 'AC')
          AND    hat.transaction_id       = hats.transaction_id
          AND    hats.api_name            = 'HR_QUA_AWARDS_UTIL_SS.PROCESS_API'
          AND    hats.transaction_step_id = hatv.transaction_step_id
          and    hatv.name                = 'P_QUALIFICATION_ID'
          AND    hatv.varchar2_value      = baseq.qualification_id
          AND    hats.transaction_step_id = hatvd.transaction_step_id(+)
          AND    hatvd.name(+)            = 'P_DELETE_FLAG') pend
WHERE  pea.establishment_id = pe.establishment_id(+)
AND    pea.attendance_id = pq.attendance_id
AND    pea.qualification_id = pq.qualification_id
AND    pq.qualification_type_id = pqt.qualification_type_id
AND    pq.fee_currency = fc.currency_code(+)
AND    pea.person_id  = pend.person_id(+)
AND    pea.attendance_id = pend.attendance_id(+)
AND    pea.qualification_id = pend.qualification_id(+)
UNION ALL
SELECT null attendance_id
      ,pq.person_id
      ,null pea_object_version_number
      ,pq.object_version_number
      ,pq.business_group_id
      ,'AWARDS' education_or_award
      ,null attended_start_date
      ,null attended_end_date
      ,null full_time_flag
      ,null full_time_meaning
      ,null establishment_id
      ,null establishment
      ,null address
      ,null pea_attribute_category
      ,null pea_attribute1
      ,null pea_attribute1_meaning
      ,null pea_attribute2
      ,null pea_attribute2_meaning
      ,null pea_attribute3
      ,null pea_attribute3_meaning
      ,null pea_attribute4
      ,null pea_attribute4_meaning
      ,null pea_attribute5
      ,null pea_attribute5_meaning
      ,null pea_attribute6
      ,null pea_attribute6_meaning
      ,null pea_attribute7
      ,null pea_attribute7_meaning
      ,null pea_attribute8
      ,null pea_attribute8_meaning
      ,null pea_attribute9
      ,null pea_attribute9_meaning
      ,null pea_attribute10
      ,null pea_attribute10_meaning
      ,null pea_attribute11
      ,null pea_attribute11_meaning
      ,null pea_attribute12
      ,null pea_attribute12_meaning
      ,null pea_attribute13
      ,null pea_attribute13_meaning
      ,null pea_attribute14
      ,null pea_attribute14_meaning
      ,null pea_attribute15
      ,null pea_attribute15_meaning
      ,null pea_attribute16
      ,null pea_attribute16_meaning
      ,null pea_attribute17
      ,null pea_attribute17_meaning
      ,null pea_attribute18
      ,null pea_attribute18_meaning
      ,null pea_attribute19
      ,null pea_attribute19_meaning
      ,null pea_attribute20
      ,null pea_attribute20_meaning
      ,pq.last_update_date
      ,pq.last_updated_by
      ,pq.last_update_login
      ,pq.created_by
      ,pq.creation_date
      ,pq.qualification_id
      ,pq.title
      ,pqt.qualification_type_id
      ,pqt.category pq_category_code
      ,xxas_util_pkg.decode_lookup('PER_CATEGORIES', pqt.category) pq_category_meaning
      ,pqt.rank
      ,pqt.name qualification_name
      ,pq.grade_attained
      ,pq.status status_code
      ,xxas_util_pkg.decode_lookup('PER_SUBJECT_STATUSES', pq.status) status_meaning
      ,pq.awarded_date
      ,pq.fee
      ,pq.fee_currency fee_currency_code
      ,fc.name fee_currency_meaning
      ,pq.training_completed_amount
      ,pq.reimbursement_arrangements
      ,pq.training_completed_units
      ,pq.total_training_amount
      ,pq.start_date
      ,pq.end_date
      ,pq.license_number
      ,pq.expiry_date
      ,pq.license_restrictions
      ,pq.projected_completion_date
      ,pq.awarding_body
      ,pq.tuition_method
      ,pq.group_ranking
      ,pq.professional_body_name
      ,pq.membership_number
      ,pq.membership_category
      ,pq.subscription_payment_method
      ,pq.comments
      ,pq.attribute_category qua_attribute_category
      ,pq.attribute1 qua_attribute1
      ,xxas_com_vset_pkg.decode_descr_vset_value
         ('800','PER_QUALIFICATIONS',pq.attribute_category,'ATTRIBUTE1',pq.attribute1) qua_attribute1_meaning
      ,pq.attribute2 qua_attribute2
      ,xxas_com_vset_pkg.decode_descr_vset_value
         ('800','PER_QUALIFICATIONS',pq.attribute_category,'ATTRIBUTE2',pq.attribute2) qua_attribute2_meaning
      ,pq.attribute3 qua_attribute3
      ,xxas_com_vset_pkg.decode_descr_vset_value
         ('800','PER_QUALIFICATIONS',pq.attribute_category,'ATTRIBUTE3',pq.attribute3) qua_attribute3_meaning
      ,pq.attribute4 qua_attribute4
      ,xxas_com_vset_pkg.decode_descr_vset_value
         ('800','PER_QUALIFICATIONS',pq.attribute_category,'ATTRIBUTE4',pq.attribute4) qua_attribute4_meaning
      ,pq.attribute5 qua_attribute5
      ,xxas_com_vset_pkg.decode_descr_vset_value
         ('800','PER_QUALIFICATIONS',pq.attribute_category,'ATTRIBUTE5',pq.attribute5) qua_attribute5_meaning
      ,pq.attribute6 qua_attribute6
      ,xxas_com_vset_pkg.decode_descr_vset_value
         ('800','PER_QUALIFICATIONS',pq.attribute_category,'ATTRIBUTE6',pq.attribute6) qua_attribute6_meaning
      ,pq.attribute7 qua_attribute7
      ,xxas_com_vset_pkg.decode_descr_vset_value
         ('800','PER_QUALIFICATIONS',pq.attribute_category,'ATTRIBUTE7',pq.attribute7) qua_attribute7_meaning
      ,pq.attribute8 qua_attribute8
      ,xxas_com_vset_pkg.decode_descr_vset_value
         ('800','PER_QUALIFICATIONS',pq.attribute_category,'ATTRIBUTE8',pq.attribute8) qua_attribute8_meaning
      ,pq.attribute9 qua_attribute9
      ,xxas_com_vset_pkg.decode_descr_vset_value
         ('800','PER_QUALIFICATIONS',pq.attribute_category,'ATTRIBUTE9',pq.attribute9) qua_attribute9_meaning
      ,pq.attribute10 qua_attribute10
      ,xxas_com_vset_pkg.decode_descr_vset_value
         ('800','PER_QUALIFICATIONS',pq.attribute_category,'ATTRIBUTE10',pq.attribute10) qua_attribute10_meaning
      ,pq.attribute11 qua_attribute11
      ,xxas_com_vset_pkg.decode_descr_vset_value
         ('800','PER_QUALIFICATIONS',pq.attribute_category,'ATTRIBUTE11',pq.attribute11) qua_attribute11_meaning
      ,pq.attribute12 qua_attribute12
      ,xxas_com_vset_pkg.decode_descr_vset_value
         ('800','PER_QUALIFICATIONS',pq.attribute_category,'ATTRIBUTE12',pq.attribute12) qua_attribute12_meaning
      ,pq.attribute13 qua_attribute13
      ,xxas_com_vset_pkg.decode_descr_vset_value
         ('800','PER_QUALIFICATIONS',pq.attribute_category,'ATTRIBUTE13',pq.attribute13) qua_attribute13_meaning
      ,pq.attribute14 qua_attribute14
      ,xxas_com_vset_pkg.decode_descr_vset_value
         ('800','PER_QUALIFICATIONS',pq.attribute_category,'ATTRIBUTE14',pq.attribute14) qua_attribute14_meaning
      ,pq.attribute15 qua_attribute15
      ,xxas_com_vset_pkg.decode_descr_vset_value
         ('800','PER_QUALIFICATIONS',pq.attribute_category,'ATTRIBUTE15',pq.attribute15) qua_attribute15_meaning
      ,pq.attribute16 qua_attribute16
      ,xxas_com_vset_pkg.decode_descr_vset_value
         ('800','PER_QUALIFICATIONS',pq.attribute_category,'ATTRIBUTE16',pq.attribute16) qua_attribute16_meaning
      ,pq.attribute17 qua_attribute17
      ,xxas_com_vset_pkg.decode_descr_vset_value
         ('800','PER_QUALIFICATIONS',pq.attribute_category,'ATTRIBUTE17',pq.attribute17) qua_attribute17_meaning
      ,pq.attribute18 qua_attribute18
      ,xxas_com_vset_pkg.decode_descr_vset_value
         ('800','PER_QUALIFICATIONS',pq.attribute_category,'ATTRIBUTE18',pq.attribute18) qua_attribute18_meaning
      ,pq.attribute19 qua_attribute19
      ,xxas_com_vset_pkg.decode_descr_vset_value
         ('800','PER_QUALIFICATIONS',pq.attribute_category,'ATTRIBUTE19',pq.attribute19) qua_attribute19_meaning
      ,pq.attribute20 qua_attribute20
      ,xxas_com_vset_pkg.decode_descr_vset_value
         ('800','PER_QUALIFICATIONS',pq.attribute_category,'ATTRIBUTE20',pq.attribute20) qua_attribute30_meaning
      ,pq.qua_information_category qua_information_category
      ,pq.qua_information1 qua_information1
      ,xxas_com_vset_pkg.decode_descr_vset_value
         ('800','Qualification Developer DF',pq.qua_information_category,'QUA_INFORMATION1',pq.qua_information1) qua_information1_meaning
      ,pq.qua_information2 qua_information2
      ,xxas_com_vset_pkg.decode_descr_vset_value
         ('800','Qualification Developer DF',pq.qua_information_category,'QUA_INFORMATION2',pq.qua_information2) qua_information2_meaning
      ,pq.qua_information3 qua_information3
      ,xxas_com_vset_pkg.decode_descr_vset_value
         ('800','Qualification Developer DF',pq.qua_information_category,'QUA_INFORMATION3',pq.qua_information3) qua_information3_meaning
      ,pq.qua_information4 qua_information4
      ,xxas_com_vset_pkg.decode_descr_vset_value
         ('800','Qualification Developer DF',pq.qua_information_category,'QUA_INFORMATION4',pq.qua_information4) qua_information4_meaning
      ,pq.qua_information5 qua_information5
      ,xxas_com_vset_pkg.decode_descr_vset_value
         ('800','Qualification Developer DF',pq.qua_information_category,'QUA_INFORMATION5',pq.qua_information5) qua_information5_meaning
      ,pq.qua_information6 qua_information6
      ,xxas_com_vset_pkg.decode_descr_vset_value
         ('800','Qualification Developer DF',pq.qua_information_category,'QUA_INFORMATION6',pq.qua_information6) qua_information6_meaning
      ,pq.qua_information7 qua_information7
      ,xxas_com_vset_pkg.decode_descr_vset_value
         ('800','Qualification Developer DF',pq.qua_information_category,'QUA_INFORMATION7',pq.qua_information7) qua_information7_meaning
      ,pq.qua_information8 qua_information8
      ,xxas_com_vset_pkg.decode_descr_vset_value
         ('800','Qualification Developer DF',pq.qua_information_category,'QUA_INFORMATION8',pq.qua_information8) qua_information8_meaning
      ,pq.qua_information9 qua_information9
      ,xxas_com_vset_pkg.decode_descr_vset_value
         ('800','Qualification Developer DF',pq.qua_information_category,'QUA_INFORMATION9',pq.qua_information9) qua_information9_meaning
      ,pq.qua_information10 qua_information10
      ,xxas_com_vset_pkg.decode_descr_vset_value
         ('800','Qualification Developer DF',pq.qua_information_category,'QUA_INFORMATION10',pq.qua_information10) qua_information10_meaning
      ,pq.qua_information11 qua_information11
      ,xxas_com_vset_pkg.decode_descr_vset_value
         ('800','Qualification Developer DF',pq.qua_information_category,'QUA_INFORMATION11',pq.qua_information11) qua_information11_meaning
      ,pq.qua_information12 qua_information12
      ,xxas_com_vset_pkg.decode_descr_vset_value
         ('800','Qualification Developer DF',pq.qua_information_category,'QUA_INFORMATION12',pq.qua_information12) qua_information12_meaning
      ,pq.qua_information13 qua_information13
      ,xxas_com_vset_pkg.decode_descr_vset_value
         ('800','Qualification Developer DF',pq.qua_information_category,'QUA_INFORMATION13',pq.qua_information13) qua_information13_meaning
      ,pq.qua_information14 qua_information14
      ,xxas_com_vset_pkg.decode_descr_vset_value
         ('800','Qualification Developer DF',pq.qua_information_category,'QUA_INFORMATION14',pq.qua_information14) qua_information14_meaning
      ,pq.qua_information15 qua_information15
      ,xxas_com_vset_pkg.decode_descr_vset_value
         ('800','Qualification Developer DF',pq.qua_information_category,'QUA_INFORMATION15',pq.qua_information15) qua_information15_meaning
      ,pq.qua_information16 qua_information16
      ,xxas_com_vset_pkg.decode_descr_vset_value
         ('800','Qualification Developer DF',pq.qua_information_category,'QUA_INFORMATION16',pq.qua_information16) qua_information16_meaning
      ,pq.qua_information17 qua_information17
      ,xxas_com_vset_pkg.decode_descr_vset_value
         ('800','Qualification Developer DF',pq.qua_information_category,'QUA_INFORMATION17',pq.qua_information17) qua_information17_meaning
      ,pq.qua_information18 qua_information18
      ,xxas_com_vset_pkg.decode_descr_vset_value
         ('800','Qualification Developer DF',pq.qua_information_category,'QUA_INFORMATION18',pq.qua_information18) qua_information18_meaning
      ,pq.qua_information19 qua_information19
      ,xxas_com_vset_pkg.decode_descr_vset_value
         ('800','Qualification Developer DF',pq.qua_information_category,'QUA_INFORMATION19',pq.qua_information19) qua_information19_meaning
      ,pq.qua_information20 qua_information20
      ,xxas_com_vset_pkg.decode_descr_vset_value
         ('800','Qualification Developer DF',pq.qua_information_category,'QUA_INFORMATION20',pq.qua_information20) qua_information20_meaning
      ,pend.transaction_id
      ,pend.transaction_step_id
      ,pend.transaction_type
      ,pend.hr_api_status_code
      ,pend.workflow_status_code
      ,xxas_selfservice_workflow_pkg.decode_status(pend.workflow_status_code) workflow_status_meaning
      ,pend.item_type
      ,pend.item_key
      ,'PQH_SS_ATTACHMENT' attachment_entity
      ,pend.transaction_id attachment_pk1
      ,null attendance_id_p
      ,null pea_object_version_number_p
      ,xxas_hr_api_transaction_pkg.varchar2_value(pend.person_id, pend.api_name, pend.transaction_step_id,'P_PQ_OBJECT_VERSION_NUMBER') object_version_number_p
      ,xxas_hr_api_transaction_pkg.varchar2_value(pend.person_id, pend.api_name, pend.transaction_step_id,'P_BUSINESS_GROUP_ID') business_group_id_p
      ,null attended_start_date_p
      ,null attended_end_date_p
      ,null full_time_flag_p
      ,null full_time_meaning_p
      ,null establishment_id_p
      ,null establishment_p
      ,null address_p
      ,null pea_attribute_category_p
      ,null pea_attribute1_p
      ,null pea_attribute1_meaning_p
      ,null pea_attribute2_p
      ,null pea_attribute2_meaning_p
      ,null pea_attribute3_p
      ,null pea_attribute3_meaning_p
      ,null pea_attribute4_p
      ,null pea_attribute4_meaning_p
      ,null pea_attribute5_p
      ,null pea_attribute5_meaning_p
      ,null pea_attribute6_p
      ,null pea_attribute6_meaning_p
      ,null pea_attribute7_p
      ,null pea_attribute7_meaning_p
      ,null pea_attribute8_p
      ,null pea_attribute8_meaning_p
      ,null pea_attribute9_p
      ,null pea_attribute9_meaning_p
      ,null pea_attribute10_p
      ,null pea_attribute10_meaning_p
      ,null pea_attribute11_p
      ,null pea_attribute11_meaning_p
      ,null pea_attribute12_p
      ,null pea_attribute12_meaning_p
      ,null pea_attribute13_p
      ,null pea_attribute13_meaning_p
      ,null pea_attribute14_p
      ,null pea_attribute14_meaning_p
      ,null pea_attribute15_p
      ,null pea_attribute15_meaning_p
      ,null pea_attribute16_p
      ,null pea_attribute16_meaning_p
      ,null pea_attribute17_p
      ,null pea_attribute17_meaning_p
      ,null pea_attribute18_p
      ,null pea_attribute18_meaning_p
      ,null pea_attribute19_p
      ,null pea_attribute19_meaning_p
      ,null pea_attribute20_p
      ,null pea_attribute20_meaning_p
      ,xxas_hr_api_transaction_pkg.varchar2_value(pend.person_id, pend.api_name, pend.transaction_step_id,'P_QUALIFICATION_ID') qualification_id_p
      ,xxas_hr_api_transaction_pkg.varchar2_value(pend.person_id, pend.api_name, pend.transaction_step_id,'P_PQ_TITLE') title_p
      ,xxas_hr_api_transaction_pkg.varchar2_value(pend.person_id, pend.api_name, pend.transaction_step_id,'P_QUALIFICATION_TYPE_ID') qualification_type_id_p
      ,decode
        (xxas_hr_api_transaction_pkg.varchar2_value(pend.person_id, pend.api_name, pend.transaction_step_id,'P_QUALIFICATION_TYPE_ID')
        ,null, null
        ,(SELECT pend_pqtc.category
          FROM   per_qualification_types pend_pqtc
          WHERE  pend_pqtc.qualification_type_id = 
            xxas_hr_api_transaction_pkg.varchar2_value(pend.person_id, pend.api_name, pend.transaction_step_id,'P_QUALIFICATION_TYPE_ID'))) pq_category_code_p
      ,decode
        (xxas_hr_api_transaction_pkg.varchar2_value(pend.person_id, pend.api_name, pend.transaction_step_id,'P_QUALIFICATION_TYPE_ID')
        ,null, null
        ,(SELECT xxas_util_pkg.decode_lookup('PER_CATEGORIES', pend_pqtm.category) 
          FROM   per_qualification_types pend_pqtm
          WHERE  pend_pqtm.qualification_type_id = 
            xxas_hr_api_transaction_pkg.varchar2_value(pend.person_id, pend.api_name, pend.transaction_step_id,'P_QUALIFICATION_TYPE_ID'))) pq_category_meaning_p
      ,decode
        (xxas_hr_api_transaction_pkg.varchar2_value(pend.person_id, pend.api_name, pend.transaction_step_id,'P_QUALIFICATION_TYPE_ID')
        ,null, null
        ,(SELECT pend_pqtr.rank 
          FROM   per_qualification_types pend_pqtr
          WHERE  pend_pqtr.qualification_type_id = 
            xxas_hr_api_transaction_pkg.varchar2_value(pend.person_id, pend.api_name, pend.transaction_step_id,'P_QUALIFICATION_TYPE_ID'))) rank_p
      ,decode
        (xxas_hr_api_transaction_pkg.varchar2_value(pend.person_id, pend.api_name, pend.transaction_step_id,'P_QUALIFICATION_TYPE_ID')
        ,null, null
        ,(SELECT pend_pqtn.name 
          FROM   per_qualification_types pend_pqtn
          WHERE  pend_pqtn.qualification_type_id = 
            xxas_hr_api_transaction_pkg.varchar2_value(pend.person_id, pend.api_name, pend.transaction_step_id,'P_QUALIFICATION_TYPE_ID'))) qualification_name_p
      ,xxas_hr_api_transaction_pkg.varchar2_value(pend.person_id, pend.api_name, pend.transaction_step_id,'P_PQ_GRADE_ATTAINED') grade_attained_p
      ,xxas_hr_api_transaction_pkg.varchar2_value(pend.person_id, pend.api_name, pend.transaction_step_id,'P_PQ_STATUS') status_code_p
      ,xxas_util_pkg.decode_lookup
        ('PER_SUBJECT_STATUSES'
        ,xxas_hr_api_transaction_pkg.varchar2_value(pend.person_id, pend.api_name, pend.transaction_step_id,'P_PQ_STATUS')) status_meaning_p
      ,xxas_hr_api_transaction_pkg.date_value(pend.person_id, pend.api_name, pend.transaction_step_id,'P_PQ_AWARDED_DATE') awarded_date_p
      ,xxas_hr_api_transaction_pkg.varchar2_value(pend.person_id, pend.api_name, pend.transaction_step_id,'P_PQ_FEE') fee_p
      ,xxas_hr_api_transaction_pkg.varchar2_value(pend.person_id, pend.api_name, pend.transaction_step_id,'P_PQ_FEE_CURRENCY') fee_currency_code_p
      ,decode
        (xxas_hr_api_transaction_pkg.varchar2_value(pend.person_id, pend.api_name, pend.transaction_step_id,'P_PQ_FEE_CURRENCY')
        ,null, null
        ,(SELECT pend_fc.name
          FROM   fnd_currencies_vl pend_fc
          WHERE  pend_fc.currency_code = 
            xxas_hr_api_transaction_pkg.varchar2_value(pend.person_id, pend.api_name, pend.transaction_step_id,'P_PQ_FEE_CURRENCY'))) fee_currency_meaning_p
      ,xxas_hr_api_transaction_pkg.varchar2_value(pend.person_id, pend.api_name, pend.transaction_step_id,'P_PQ_COMPLETED_AMOUNT') training_completed_amount_p
      ,xxas_hr_api_transaction_pkg.varchar2_value(pend.person_id, pend.api_name, pend.transaction_step_id,'P_PQ_REIMBURSEMENT') reimbursement_arrangements_p
      ,xxas_hr_api_transaction_pkg.varchar2_value(pend.person_id, pend.api_name, pend.transaction_step_id,'P_PQ_COMPLETED_UNITS') training_completed_units_p
      ,xxas_hr_api_transaction_pkg.varchar2_value(pend.person_id, pend.api_name, pend.transaction_step_id,'P_PQ_TOTAL_AMOUNT') total_training_amount_p
      ,xxas_hr_api_transaction_pkg.date_value(pend.person_id, pend.api_name, pend.transaction_step_id,'P_PQ_START_DATE') start_date_p
      ,xxas_hr_api_transaction_pkg.date_value(pend.person_id, pend.api_name, pend.transaction_step_id,'P_PQ_END_DATE') end_date_p
      ,xxas_hr_api_transaction_pkg.varchar2_value(pend.person_id, pend.api_name, pend.transaction_step_id,'P_PQ_LICENSE_NUMBER') license_number_p
      ,xxas_hr_api_transaction_pkg.date_value(pend.person_id, pend.api_name, pend.transaction_step_id,'P_PQ_EXPIRY_DATE') expiry_date_p
      ,xxas_hr_api_transaction_pkg.varchar2_value(pend.person_id, pend.api_name, pend.transaction_step_id,'P_PQ_LICENSE_RESTRICTIONS') license_restrictions_p
      ,null projected_completion_date_p
      ,xxas_hr_api_transaction_pkg.varchar2_value(pend.person_id, pend.api_name, pend.transaction_step_id,'P_PQ_AWARDING_BODY') awarding_body_p
      ,xxas_hr_api_transaction_pkg.varchar2_value(pend.person_id, pend.api_name, pend.transaction_step_id,'P_PQ_TUITION_METHOD') tuition_method_p
      ,xxas_hr_api_transaction_pkg.varchar2_value(pend.person_id, pend.api_name, pend.transaction_step_id,'P_PQ_GROUP_RANKING') group_ranking_p
      ,null professional_body_name_p
      ,null membership_number_p
      ,null membership_category_p
      ,null subscription_payment_method_p
      ,xxas_hr_api_transaction_pkg.varchar2_value(pend.person_id, pend.api_name, pend.transaction_step_id,'P_PQ_COMMENTS') comments_p
      ,xxas_hr_api_transaction_pkg.varchar2_value(pend.person_id, pend.api_name, pend.transaction_step_id,'P_PQ_ATTRIBUTE_CATEGORY') qua_attribute_category_p
      ,xxas_hr_api_transaction_pkg.varchar2_value(pend.person_id, pend.api_name, pend.transaction_step_id,'P_PQ_ATTRIBUTE1') qua_attribute1_p
      ,xxas_com_vset_pkg.decode_descr_vset_value
         ('800','PER_QUALIFICATIONS'
         ,xxas_hr_api_transaction_pkg.varchar2_value(pend.person_id, pend.api_name, pend.transaction_step_id,'P_PQ_ATTRIBUTE_CATEGORY')
         ,'ATTRIBUTE1'
         ,xxas_hr_api_transaction_pkg.varchar2_value(pend.person_id, pend.api_name, pend.transaction_step_id,'P_PQ_ATTRIBUTE1')) qua_attribute1_meaning_p
      ,xxas_hr_api_transaction_pkg.varchar2_value(pend.person_id, pend.api_name, pend.transaction_step_id,'P_PQ_ATTRIBUTE2') qua_attribute2_p
      ,xxas_com_vset_pkg.decode_descr_vset_value
         ('800','PER_QUALIFICATIONS'
         ,xxas_hr_api_transaction_pkg.varchar2_value(pend.person_id, pend.api_name, pend.transaction_step_id,'P_PQ_ATTRIBUTE_CATEGORY')
         ,'ATTRIBUTE2'
         ,xxas_hr_api_transaction_pkg.varchar2_value(pend.person_id, pend.api_name, pend.transaction_step_id,'P_PQ_ATTRIBUTE2')) qua_attribute2_meaning_p
      ,xxas_hr_api_transaction_pkg.varchar2_value(pend.person_id, pend.api_name, pend.transaction_step_id,'P_PQ_ATTRIBUTE3') qua_attribute3_p
      ,xxas_com_vset_pkg.decode_descr_vset_value
         ('800','PER_QUALIFICATIONS'
         ,xxas_hr_api_transaction_pkg.varchar2_value(pend.person_id, pend.api_name, pend.transaction_step_id,'P_PQ_ATTRIBUTE_CATEGORY')
         ,'ATTRIBUTE3'
         ,xxas_hr_api_transaction_pkg.varchar2_value(pend.person_id, pend.api_name, pend.transaction_step_id,'P_PQ_ATTRIBUTE3')) qua_attribute3_meaning_p
      ,xxas_hr_api_transaction_pkg.varchar2_value(pend.person_id, pend.api_name, pend.transaction_step_id,'P_PQ_ATTRIBUTE4') qua_attribute4_p
      ,xxas_com_vset_pkg.decode_descr_vset_value
         ('800','PER_QUALIFICATIONS'
         ,xxas_hr_api_transaction_pkg.varchar2_value(pend.person_id, pend.api_name, pend.transaction_step_id,'P_PQ_ATTRIBUTE_CATEGORY')
         ,'ATTRIBUTE4'
         ,xxas_hr_api_transaction_pkg.varchar2_value(pend.person_id, pend.api_name, pend.transaction_step_id,'P_PQ_ATTRIBUTE4')) qua_attribute4_meaning_p
      ,xxas_hr_api_transaction_pkg.varchar2_value(pend.person_id, pend.api_name, pend.transaction_step_id,'P_PQ_ATTRIBUTE5') qua_attribute5_p
      ,xxas_com_vset_pkg.decode_descr_vset_value
         ('800','PER_QUALIFICATIONS'
         ,xxas_hr_api_transaction_pkg.varchar2_value(pend.person_id, pend.api_name, pend.transaction_step_id,'P_PQ_ATTRIBUTE_CATEGORY')
         ,'ATTRIBUTE5'
         ,xxas_hr_api_transaction_pkg.varchar2_value(pend.person_id, pend.api_name, pend.transaction_step_id,'P_PQ_ATTRIBUTE5')) qua_attribute5_meaning_p
      ,xxas_hr_api_transaction_pkg.varchar2_value(pend.person_id, pend.api_name, pend.transaction_step_id,'P_PQ_ATTRIBUTE6') qua_attribute6_p
      ,xxas_com_vset_pkg.decode_descr_vset_value
         ('800','PER_QUALIFICATIONS'
         ,xxas_hr_api_transaction_pkg.varchar2_value(pend.person_id, pend.api_name, pend.transaction_step_id,'P_PQ_ATTRIBUTE_CATEGORY')
         ,'ATTRIBUTE6'
         ,xxas_hr_api_transaction_pkg.varchar2_value(pend.person_id, pend.api_name, pend.transaction_step_id,'P_PQ_ATTRIBUTE6')) qua_attribute6_meaning_p
      ,xxas_hr_api_transaction_pkg.varchar2_value(pend.person_id, pend.api_name, pend.transaction_step_id,'P_PQ_ATTRIBUTE7') qua_attribute7_p
      ,xxas_com_vset_pkg.decode_descr_vset_value
         ('800','PER_QUALIFICATIONS'
         ,xxas_hr_api_transaction_pkg.varchar2_value(pend.person_id, pend.api_name, pend.transaction_step_id,'P_PQ_ATTRIBUTE_CATEGORY')
         ,'ATTRIBUTE7'
         ,xxas_hr_api_transaction_pkg.varchar2_value(pend.person_id, pend.api_name, pend.transaction_step_id,'P_PQ_ATTRIBUTE7')) qua_attribute7_meaning_p
      ,xxas_hr_api_transaction_pkg.varchar2_value(pend.person_id, pend.api_name, pend.transaction_step_id,'P_PQ_ATTRIBUTE8') qua_attribute8_p
      ,xxas_com_vset_pkg.decode_descr_vset_value
         ('800','PER_QUALIFICATIONS'
         ,xxas_hr_api_transaction_pkg.varchar2_value(pend.person_id, pend.api_name, pend.transaction_step_id,'P_PQ_ATTRIBUTE_CATEGORY')
         ,'ATTRIBUTE8'
         ,xxas_hr_api_transaction_pkg.varchar2_value(pend.person_id, pend.api_name, pend.transaction_step_id,'P_PQ_ATTRIBUTE8')) qua_attribute8_meaning_p
      ,xxas_hr_api_transaction_pkg.varchar2_value(pend.person_id, pend.api_name, pend.transaction_step_id,'P_PQ_ATTRIBUTE9') qua_attribute9_p
      ,xxas_com_vset_pkg.decode_descr_vset_value
         ('800','PER_QUALIFICATIONS'
         ,xxas_hr_api_transaction_pkg.varchar2_value(pend.person_id, pend.api_name, pend.transaction_step_id,'P_PQ_ATTRIBUTE_CATEGORY')
         ,'ATTRIBUTE9'
         ,xxas_hr_api_transaction_pkg.varchar2_value(pend.person_id, pend.api_name, pend.transaction_step_id,'P_PQ_ATTRIBUTE9')) qua_attribute9_meaning_p
      ,xxas_hr_api_transaction_pkg.varchar2_value(pend.person_id, pend.api_name, pend.transaction_step_id,'P_PQ_ATTRIBUTE10') qua_attribute10_p
      ,xxas_com_vset_pkg.decode_descr_vset_value
         ('800','PER_QUALIFICATIONS'
         ,xxas_hr_api_transaction_pkg.varchar2_value(pend.person_id, pend.api_name, pend.transaction_step_id,'P_PQ_ATTRIBUTE_CATEGORY')
         ,'ATTRIBUTE10'
         ,xxas_hr_api_transaction_pkg.varchar2_value(pend.person_id, pend.api_name, pend.transaction_step_id,'P_PQ_ATTRIBUTE10')) qua_attribute10_meaning_p
      ,xxas_hr_api_transaction_pkg.varchar2_value(pend.person_id, pend.api_name, pend.transaction_step_id,'P_PQ_ATTRIBUTE11') qua_attribute11_p
      ,xxas_com_vset_pkg.decode_descr_vset_value
         ('800','PER_QUALIFICATIONS'
         ,xxas_hr_api_transaction_pkg.varchar2_value(pend.person_id, pend.api_name, pend.transaction_step_id,'P_PQ_ATTRIBUTE_CATEGORY')
         ,'ATTRIBUTE11'
         ,xxas_hr_api_transaction_pkg.varchar2_value(pend.person_id, pend.api_name, pend.transaction_step_id,'P_PQ_ATTRIBUTE11')) qua_attribute11_meaning_p
      ,xxas_hr_api_transaction_pkg.varchar2_value(pend.person_id, pend.api_name, pend.transaction_step_id,'P_PQ_ATTRIBUTE12') qua_attribute12_p
      ,xxas_com_vset_pkg.decode_descr_vset_value
         ('800','PER_QUALIFICATIONS'
         ,xxas_hr_api_transaction_pkg.varchar2_value(pend.person_id, pend.api_name, pend.transaction_step_id,'P_PQ_ATTRIBUTE_CATEGORY')
         ,'ATTRIBUTE12'
         ,xxas_hr_api_transaction_pkg.varchar2_value(pend.person_id, pend.api_name, pend.transaction_step_id,'P_PQ_ATTRIBUTE12')) qua_attribute12_meaning_p
      ,xxas_hr_api_transaction_pkg.varchar2_value(pend.person_id, pend.api_name, pend.transaction_step_id,'P_PQ_ATTRIBUTE13') qua_attribute13_p
      ,xxas_com_vset_pkg.decode_descr_vset_value
         ('800','PER_QUALIFICATIONS'
         ,xxas_hr_api_transaction_pkg.varchar2_value(pend.person_id, pend.api_name, pend.transaction_step_id,'P_PQ_ATTRIBUTE_CATEGORY')
         ,'ATTRIBUTE13'
         ,xxas_hr_api_transaction_pkg.varchar2_value(pend.person_id, pend.api_name, pend.transaction_step_id,'P_PQ_ATTRIBUTE13')) qua_attribute13_meaning_p
      ,xxas_hr_api_transaction_pkg.varchar2_value(pend.person_id, pend.api_name, pend.transaction_step_id,'P_PQ_ATTRIBUTE14') qua_attribute14_p
      ,xxas_com_vset_pkg.decode_descr_vset_value
         ('800','PER_QUALIFICATIONS'
         ,xxas_hr_api_transaction_pkg.varchar2_value(pend.person_id, pend.api_name, pend.transaction_step_id,'P_PQ_ATTRIBUTE_CATEGORY')
         ,'ATTRIBUTE14'
         ,xxas_hr_api_transaction_pkg.varchar2_value(pend.person_id, pend.api_name, pend.transaction_step_id,'P_PQ_ATTRIBUTE14')) qua_attribute14_meaning_p
      ,xxas_hr_api_transaction_pkg.varchar2_value(pend.person_id, pend.api_name, pend.transaction_step_id,'P_PQ_ATTRIBUTE15') qua_attribute15_p
      ,xxas_com_vset_pkg.decode_descr_vset_value
         ('800','PER_QUALIFICATIONS'
         ,xxas_hr_api_transaction_pkg.varchar2_value(pend.person_id, pend.api_name, pend.transaction_step_id,'P_PQ_ATTRIBUTE_CATEGORY')
         ,'ATTRIBUTE15'
         ,xxas_hr_api_transaction_pkg.varchar2_value(pend.person_id, pend.api_name, pend.transaction_step_id,'P_PQ_ATTRIBUTE15')) qua_attribute15_meaning_p
      ,xxas_hr_api_transaction_pkg.varchar2_value(pend.person_id, pend.api_name, pend.transaction_step_id,'P_PQ_ATTRIBUTE16') qua_attribute16_p
      ,xxas_com_vset_pkg.decode_descr_vset_value
         ('800','PER_QUALIFICATIONS'
         ,xxas_hr_api_transaction_pkg.varchar2_value(pend.person_id, pend.api_name, pend.transaction_step_id,'P_PQ_ATTRIBUTE_CATEGORY')
         ,'ATTRIBUTE16'
         ,xxas_hr_api_transaction_pkg.varchar2_value(pend.person_id, pend.api_name, pend.transaction_step_id,'P_PQ_ATTRIBUTE16')) qua_attribute16_meaning_p
      ,xxas_hr_api_transaction_pkg.varchar2_value(pend.person_id, pend.api_name, pend.transaction_step_id,'P_PQ_ATTRIBUTE17') qua_attribute17_p
      ,xxas_com_vset_pkg.decode_descr_vset_value
         ('800','PER_QUALIFICATIONS'
         ,xxas_hr_api_transaction_pkg.varchar2_value(pend.person_id, pend.api_name, pend.transaction_step_id,'P_PQ_ATTRIBUTE_CATEGORY')
         ,'ATTRIBUTE17'
         ,xxas_hr_api_transaction_pkg.varchar2_value(pend.person_id, pend.api_name, pend.transaction_step_id,'P_PQ_ATTRIBUTE17')) qua_attribute17_meaning_p
      ,xxas_hr_api_transaction_pkg.varchar2_value(pend.person_id, pend.api_name, pend.transaction_step_id,'P_PQ_ATTRIBUTE18') qua_attribute18_p
      ,xxas_com_vset_pkg.decode_descr_vset_value
         ('800','PER_QUALIFICATIONS'
         ,xxas_hr_api_transaction_pkg.varchar2_value(pend.person_id, pend.api_name, pend.transaction_step_id,'P_PQ_ATTRIBUTE_CATEGORY')
         ,'ATTRIBUTE18'
         ,xxas_hr_api_transaction_pkg.varchar2_value(pend.person_id, pend.api_name, pend.transaction_step_id,'P_PQ_ATTRIBUTE18')) qua_attribute18_meaning_p
      ,xxas_hr_api_transaction_pkg.varchar2_value(pend.person_id, pend.api_name, pend.transaction_step_id,'P_PQ_ATTRIBUTE19') qua_attribute19_p
      ,xxas_com_vset_pkg.decode_descr_vset_value
         ('800','PER_QUALIFICATIONS'
         ,xxas_hr_api_transaction_pkg.varchar2_value(pend.person_id, pend.api_name, pend.transaction_step_id,'P_PQ_ATTRIBUTE_CATEGORY')
         ,'ATTRIBUTE19'
         ,xxas_hr_api_transaction_pkg.varchar2_value(pend.person_id, pend.api_name, pend.transaction_step_id,'P_PQ_ATTRIBUTE19')) qua_attribute19_meaning_p
      ,xxas_hr_api_transaction_pkg.varchar2_value(pend.person_id, pend.api_name, pend.transaction_step_id,'P_PQ_ATTRIBUTE20') qua_attribute20_p
      ,xxas_com_vset_pkg.decode_descr_vset_value
         ('800','PER_QUALIFICATIONS'
         ,xxas_hr_api_transaction_pkg.varchar2_value(pend.person_id, pend.api_name, pend.transaction_step_id,'P_PQ_ATTRIBUTE_CATEGORY')
         ,'ATTRIBUTE20'
         ,xxas_hr_api_transaction_pkg.varchar2_value(pend.person_id, pend.api_name, pend.transaction_step_id,'P_PQ_ATTRIBUTE20')) qua_attribute20_meaning_p
      ,xxas_hr_api_transaction_pkg.varchar2_value(pend.person_id, pend.api_name, pend.transaction_step_id,'P_PQ_QUA_INFORMATION_CATEGORY') qua_information_category_p
      ,xxas_hr_api_transaction_pkg.varchar2_value(pend.person_id, pend.api_name, pend.transaction_step_id,'P_PQ_QUA_INFORMATION1') qua_information1_p
      ,xxas_com_vset_pkg.decode_descr_vset_value
         ('800','Qualification Developer DF'
         ,xxas_hr_api_transaction_pkg.varchar2_value(pend.person_id, pend.api_name, pend.transaction_step_id,'P_PQ_QUA_INFORMATION_CATEGORY')
         ,'QUA_INFORMATION1'
         ,xxas_hr_api_transaction_pkg.varchar2_value(pend.person_id, pend.api_name, pend.transaction_step_id,'P_PQ_QUA_INFORMATION1')) qua_information1_meaning_p
      ,xxas_hr_api_transaction_pkg.varchar2_value(pend.person_id, pend.api_name, pend.transaction_step_id,'P_PQ_QUA_INFORMATION2') qua_information2_p
      ,xxas_com_vset_pkg.decode_descr_vset_value
         ('800','Qualification Developer DF'
         ,xxas_hr_api_transaction_pkg.varchar2_value(pend.person_id, pend.api_name, pend.transaction_step_id,'P_PQ_QUA_INFORMATION_CATEGORY')
         ,'QUA_INFORMATION2'
         ,xxas_hr_api_transaction_pkg.varchar2_value(pend.person_id, pend.api_name, pend.transaction_step_id,'P_PQ_QUA_INFORMATION2')) qua_information2_meaning_p
      ,xxas_hr_api_transaction_pkg.varchar2_value(pend.person_id, pend.api_name, pend.transaction_step_id,'P_PQ_QUA_INFORMATION3') qua_information3_p
      ,xxas_com_vset_pkg.decode_descr_vset_value
         ('800','Qualification Developer DF'
         ,xxas_hr_api_transaction_pkg.varchar2_value(pend.person_id, pend.api_name, pend.transaction_step_id,'P_PQ_QUA_INFORMATION_CATEGORY')
         ,'QUA_INFORMATION3'
         ,xxas_hr_api_transaction_pkg.varchar2_value(pend.person_id, pend.api_name, pend.transaction_step_id,'P_PQ_QUA_INFORMATION3')) qua_information3_meaning_p
      ,xxas_hr_api_transaction_pkg.varchar2_value(pend.person_id, pend.api_name, pend.transaction_step_id,'P_PQ_QUA_INFORMATION4') qua_information4_p
      ,xxas_com_vset_pkg.decode_descr_vset_value
         ('800','Qualification Developer DF'
         ,xxas_hr_api_transaction_pkg.varchar2_value(pend.person_id, pend.api_name, pend.transaction_step_id,'P_PQ_QUA_INFORMATION_CATEGORY')
         ,'QUA_INFORMATION4'
         ,xxas_hr_api_transaction_pkg.varchar2_value(pend.person_id, pend.api_name, pend.transaction_step_id,'P_PQ_QUA_INFORMATION4')) qua_information4_meaning_p
      ,xxas_hr_api_transaction_pkg.varchar2_value(pend.person_id, pend.api_name, pend.transaction_step_id,'P_PQ_QUA_INFORMATION5') qua_information5_p
      ,xxas_com_vset_pkg.decode_descr_vset_value
         ('800','Qualification Developer DF'
         ,xxas_hr_api_transaction_pkg.varchar2_value(pend.person_id, pend.api_name, pend.transaction_step_id,'P_PQ_QUA_INFORMATION_CATEGORY')
         ,'QUA_INFORMATION5'
         ,xxas_hr_api_transaction_pkg.varchar2_value(pend.person_id, pend.api_name, pend.transaction_step_id,'P_PQ_QUA_INFORMATION5')) qua_information5_meaning_p
      ,xxas_hr_api_transaction_pkg.varchar2_value(pend.person_id, pend.api_name, pend.transaction_step_id,'P_PQ_QUA_INFORMATION6') qua_information6_p
      ,xxas_com_vset_pkg.decode_descr_vset_value
         ('800','Qualification Developer DF'
         ,xxas_hr_api_transaction_pkg.varchar2_value(pend.person_id, pend.api_name, pend.transaction_step_id,'P_PQ_QUA_INFORMATION_CATEGORY')
         ,'QUA_INFORMATION6'
         ,xxas_hr_api_transaction_pkg.varchar2_value(pend.person_id, pend.api_name, pend.transaction_step_id,'P_PQ_QUA_INFORMATION6')) qua_information6_meaning_p
      ,xxas_hr_api_transaction_pkg.varchar2_value(pend.person_id, pend.api_name, pend.transaction_step_id,'P_PQ_QUA_INFORMATION7') qua_information7_p
      ,xxas_com_vset_pkg.decode_descr_vset_value
         ('800','Qualification Developer DF'
         ,xxas_hr_api_transaction_pkg.varchar2_value(pend.person_id, pend.api_name, pend.transaction_step_id,'P_PQ_QUA_INFORMATION_CATEGORY')
         ,'QUA_INFORMATION7'
         ,xxas_hr_api_transaction_pkg.varchar2_value(pend.person_id, pend.api_name, pend.transaction_step_id,'P_PQ_QUA_INFORMATION7')) qua_information7_meaning_p
      ,xxas_hr_api_transaction_pkg.varchar2_value(pend.person_id, pend.api_name, pend.transaction_step_id,'P_PQ_QUA_INFORMATION8') qua_information8_p
      ,xxas_com_vset_pkg.decode_descr_vset_value
         ('800','Qualification Developer DF'
         ,xxas_hr_api_transaction_pkg.varchar2_value(pend.person_id, pend.api_name, pend.transaction_step_id,'P_PQ_QUA_INFORMATION_CATEGORY')
         ,'QUA_INFORMATION8'
         ,xxas_hr_api_transaction_pkg.varchar2_value(pend.person_id, pend.api_name, pend.transaction_step_id,'P_PQ_QUA_INFORMATION8')) qua_information8_meaning_p
      ,xxas_hr_api_transaction_pkg.varchar2_value(pend.person_id, pend.api_name, pend.transaction_step_id,'P_PQ_QUA_INFORMATION9') qua_information9_p
      ,xxas_com_vset_pkg.decode_descr_vset_value
         ('800','Qualification Developer DF'
         ,xxas_hr_api_transaction_pkg.varchar2_value(pend.person_id, pend.api_name, pend.transaction_step_id,'P_PQ_QUA_INFORMATION_CATEGORY')
         ,'QUA_INFORMATION9'
         ,xxas_hr_api_transaction_pkg.varchar2_value(pend.person_id, pend.api_name, pend.transaction_step_id,'P_PQ_QUA_INFORMATION9')) qua_information9_meaning_p
      ,xxas_hr_api_transaction_pkg.varchar2_value(pend.person_id, pend.api_name, pend.transaction_step_id,'P_PQ_QUA_INFORMATION10') qua_information10_p
      ,xxas_com_vset_pkg.decode_descr_vset_value
         ('800','Qualification Developer DF'
         ,xxas_hr_api_transaction_pkg.varchar2_value(pend.person_id, pend.api_name, pend.transaction_step_id,'P_PQ_QUA_INFORMATION_CATEGORY')
         ,'QUA_INFORMATION10'
         ,xxas_hr_api_transaction_pkg.varchar2_value(pend.person_id, pend.api_name, pend.transaction_step_id,'P_PQ_QUA_INFORMATION10')) qua_information10_meaning_p
      ,xxas_hr_api_transaction_pkg.varchar2_value(pend.person_id, pend.api_name, pend.transaction_step_id,'P_PQ_QUA_INFORMATION11') qua_information11_p
      ,xxas_com_vset_pkg.decode_descr_vset_value
         ('800','Qualification Developer DF'
         ,xxas_hr_api_transaction_pkg.varchar2_value(pend.person_id, pend.api_name, pend.transaction_step_id,'P_PQ_QUA_INFORMATION_CATEGORY')
         ,'QUA_INFORMATION11'
         ,xxas_hr_api_transaction_pkg.varchar2_value(pend.person_id, pend.api_name, pend.transaction_step_id,'P_PQ_QUA_INFORMATION11')) qua_information11_meaning_p
      ,xxas_hr_api_transaction_pkg.varchar2_value(pend.person_id, pend.api_name, pend.transaction_step_id,'P_PQ_QUA_INFORMATION12') qua_information12_p
      ,xxas_com_vset_pkg.decode_descr_vset_value
         ('800','Qualification Developer DF'
         ,xxas_hr_api_transaction_pkg.varchar2_value(pend.person_id, pend.api_name, pend.transaction_step_id,'P_PQ_QUA_INFORMATION_CATEGORY')
         ,'QUA_INFORMATION12'
         ,xxas_hr_api_transaction_pkg.varchar2_value(pend.person_id, pend.api_name, pend.transaction_step_id,'P_PQ_QUA_INFORMATION12')) qua_information12_meaning_p
      ,xxas_hr_api_transaction_pkg.varchar2_value(pend.person_id, pend.api_name, pend.transaction_step_id,'P_PQ_QUA_INFORMATION13') qua_information13_p
      ,xxas_com_vset_pkg.decode_descr_vset_value
         ('800','Qualification Developer DF'
         ,xxas_hr_api_transaction_pkg.varchar2_value(pend.person_id, pend.api_name, pend.transaction_step_id,'P_PQ_QUA_INFORMATION_CATEGORY')
         ,'QUA_INFORMATION13'
         ,xxas_hr_api_transaction_pkg.varchar2_value(pend.person_id, pend.api_name, pend.transaction_step_id,'P_PQ_QUA_INFORMATION13')) qua_information13_meaning_p
      ,xxas_hr_api_transaction_pkg.varchar2_value(pend.person_id, pend.api_name, pend.transaction_step_id,'P_PQ_QUA_INFORMATION14') qua_information14_p
      ,xxas_com_vset_pkg.decode_descr_vset_value
         ('800','Qualification Developer DF'
         ,xxas_hr_api_transaction_pkg.varchar2_value(pend.person_id, pend.api_name, pend.transaction_step_id,'P_PQ_QUA_INFORMATION_CATEGORY')
         ,'QUA_INFORMATION14'
         ,xxas_hr_api_transaction_pkg.varchar2_value(pend.person_id, pend.api_name, pend.transaction_step_id,'P_PQ_QUA_INFORMATION14')) qua_information14_meaning_p
      ,xxas_hr_api_transaction_pkg.varchar2_value(pend.person_id, pend.api_name, pend.transaction_step_id,'P_PQ_QUA_INFORMATION15') qua_information15_p
      ,xxas_com_vset_pkg.decode_descr_vset_value
         ('800','Qualification Developer DF'
         ,xxas_hr_api_transaction_pkg.varchar2_value(pend.person_id, pend.api_name, pend.transaction_step_id,'P_PQ_QUA_INFORMATION_CATEGORY')
         ,'QUA_INFORMATION15'
         ,xxas_hr_api_transaction_pkg.varchar2_value(pend.person_id, pend.api_name, pend.transaction_step_id,'P_PQ_QUA_INFORMATION15')) qua_information15_meaning_p
      ,xxas_hr_api_transaction_pkg.varchar2_value(pend.person_id, pend.api_name, pend.transaction_step_id,'P_PQ_QUA_INFORMATION16') qua_information16_p
      ,xxas_com_vset_pkg.decode_descr_vset_value
         ('800','Qualification Developer DF'
         ,xxas_hr_api_transaction_pkg.varchar2_value(pend.person_id, pend.api_name, pend.transaction_step_id,'P_PQ_QUA_INFORMATION_CATEGORY')
         ,'QUA_INFORMATION16'
         ,xxas_hr_api_transaction_pkg.varchar2_value(pend.person_id, pend.api_name, pend.transaction_step_id,'P_PQ_QUA_INFORMATION16')) qua_information16_meaning_p
      ,xxas_hr_api_transaction_pkg.varchar2_value(pend.person_id, pend.api_name, pend.transaction_step_id,'P_PQ_QUA_INFORMATION17') qua_information17_p
      ,xxas_com_vset_pkg.decode_descr_vset_value
         ('800','Qualification Developer DF'
         ,xxas_hr_api_transaction_pkg.varchar2_value(pend.person_id, pend.api_name, pend.transaction_step_id,'P_PQ_QUA_INFORMATION_CATEGORY')
         ,'QUA_INFORMATION17'
         ,xxas_hr_api_transaction_pkg.varchar2_value(pend.person_id, pend.api_name, pend.transaction_step_id,'P_PQ_QUA_INFORMATION17')) qua_information17_meaning_p
      ,xxas_hr_api_transaction_pkg.varchar2_value(pend.person_id, pend.api_name, pend.transaction_step_id,'P_PQ_QUA_INFORMATION18') qua_information18_p
      ,xxas_com_vset_pkg.decode_descr_vset_value
         ('800','Qualification Developer DF'
         ,xxas_hr_api_transaction_pkg.varchar2_value(pend.person_id, pend.api_name, pend.transaction_step_id,'P_PQ_QUA_INFORMATION_CATEGORY')
         ,'QUA_INFORMATION18'
         ,xxas_hr_api_transaction_pkg.varchar2_value(pend.person_id, pend.api_name, pend.transaction_step_id,'P_PQ_QUA_INFORMATION18')) qua_information18_meaning_p
      ,xxas_hr_api_transaction_pkg.varchar2_value(pend.person_id, pend.api_name, pend.transaction_step_id,'P_PQ_QUA_INFORMATION19') qua_information19_p
      ,xxas_com_vset_pkg.decode_descr_vset_value
         ('800','Qualification Developer DF'
         ,xxas_hr_api_transaction_pkg.varchar2_value(pend.person_id, pend.api_name, pend.transaction_step_id,'P_PQ_QUA_INFORMATION_CATEGORY')
         ,'QUA_INFORMATION19'
         ,xxas_hr_api_transaction_pkg.varchar2_value(pend.person_id, pend.api_name, pend.transaction_step_id,'P_PQ_QUA_INFORMATION19')) qua_information19_meaning_p
      ,xxas_hr_api_transaction_pkg.varchar2_value(pend.person_id, pend.api_name, pend.transaction_step_id,'P_PQ_QUA_INFORMATION20') qua_information20_p
      ,xxas_com_vset_pkg.decode_descr_vset_value
         ('800','Qualification Developer DF'
         ,xxas_hr_api_transaction_pkg.varchar2_value(pend.person_id, pend.api_name, pend.transaction_step_id,'P_PQ_QUA_INFORMATION_CATEGORY')
         ,'QUA_INFORMATION20'
         ,xxas_hr_api_transaction_pkg.varchar2_value(pend.person_id, pend.api_name, pend.transaction_step_id,'P_PQ_QUA_INFORMATION20')) qua_information20_meaning_p
FROM   per_qualifications_vl pq
      ,per_qualification_types_vl pqt
      ,fnd_currencies_vl fc
      ,(SELECT   base.person_id
          ,      base.qualification_id
          ,      hats.api_name
          ,      hat.transaction_id
          ,      hats.transaction_step_id
          ,      hat.status  hr_api_status_code
          ,      decode(hatvd.varchar2_value, 'Y', 'DELETE', 'UPDATE') transaction_type
          ,      xxas_selfservice_workflow_pkg.transcode_status(hat.status) workflow_status_code
          ,      hat.item_type
          ,      hat.item_key
          FROM   per_qualifications base
          ,      hr_api_transactions       hat
          ,      hr_api_transaction_steps  hats
          ,      hr_api_transaction_values hatv
          ,      hr_api_transaction_values hatvd
          WHERE  base.person_id           = hat.selected_person_id
          AND    hat.status          NOT IN ('D', 'N', 'W', 'ACTIVE', 'AC')
          AND    hat.transaction_id       = hats.transaction_id
          AND    hats.api_name            = 'HR_QUA_AWARDS_UTIL_SS.PROCESS_API'
          AND    hats.transaction_step_id = hatv.transaction_step_id
          AND    hatv.name                = 'P_QUALIFICATION_ID'
          AND    hatv.varchar2_value      = base.qualification_id
          AND    hats.transaction_step_id = hatvd.transaction_step_id(+)
          AND    hatvd.name(+)            = 'P_DELETE_FLAG') pend
WHERE  pq.qualification_type_id = pqt.qualification_type_id
AND    pq.attendance_id IS NULL  /* Fix for ebiz#110 */
AND    pq.fee_currency = fc.currency_code(+)
AND    pq.person_id  = pend.person_id(+)
AND    pq.qualification_id = pend.qualification_id(+)
UNION ALL
SELECT null attendance_id
      ,pend.person_id
      ,null pea_object_version_number
      ,null object_version_number
      ,null business_group_id
      ,pend.education_or_award
      ,null attended_start_date
      ,null attended_end_date
      ,null full_time_flag
      ,null full_time_meaning
      ,null establishment_id
      ,null establishment
      ,null address
      ,null pea_attribute_category
      ,null pea_attribute1
      ,null pea_attribute1_meaning
      ,null pea_attribute2
      ,null pea_attribute2_meaning
      ,null pea_attribute3
      ,null pea_attribute3_meaning
      ,null pea_attribute4
      ,null pea_attribute4_meaning
      ,null pea_attribute5
      ,null pea_attribute5_meaning
      ,null pea_attribute6
      ,null pea_attribute6_meaning
      ,null pea_attribute7
      ,null pea_attribute7_meaning
      ,null pea_attribute8
      ,null pea_attribute8_meaning
      ,null pea_attribute9
      ,null pea_attribute9_meaning
      ,null pea_attribute10
      ,null pea_attribute10_meaning
      ,null pea_attribute11
      ,null pea_attribute11_meaning
      ,null pea_attribute12
      ,null pea_attribute12_meaning
      ,null pea_attribute13
      ,null pea_attribute13_meaning
      ,null pea_attribute14
      ,null pea_attribute14_meaning
      ,null pea_attribute15
      ,null pea_attribute15_meaning
      ,null pea_attribute16
      ,null pea_attribute16_meaning
      ,null pea_attribute17
      ,null pea_attribute17_meaning
      ,null pea_attribute18
      ,null pea_attribute18_meaning
      ,null pea_attribute19
      ,null pea_attribute19_meaning
      ,null pea_attribute20
      ,null pea_attribute20_meaning
      ,null last_update_date
      ,null last_updated_by
      ,null last_update_login
      ,null created_by
      ,null creation_date
      ,null qualification_id
      ,null title
      ,null qualification_type_id
      ,null pq_category_code
      ,null pq_category_meaning
      ,null rank
      ,null qualification_name
      ,null grade_attained
      ,null status_code
      ,null status_meaning
      ,null awarded_date
      ,null fee
      ,null fee_currency_code
      ,null fee_currency_meaning
      ,null training_completed_amount
      ,null reimbursement_arrangements
      ,null training_completed_units
      ,null total_training_amount
      ,null start_date
      ,null end_date
      ,null license_number
      ,null expiry_date
      ,null license_restrictions
      ,null projected_completion_date
      ,null awarding_body
      ,null tuition_method
      ,null group_ranking
      ,null professional_body_name
      ,null membership_number
      ,null membership_category
      ,null subscription_payment_method
      ,null comments
      ,null qua_attribute_category
      ,null qua_attribute1
      ,null qua_attribute1_meaning
      ,null qua_attribute2
      ,null qua_attribute2_meaning
      ,null qua_attribute3
      ,null qua_attribute3_meaning
      ,null qua_attribute4
      ,null qua_attribute4_meaning
      ,null qua_attribute5
      ,null qua_attribute5_meaning
      ,null qua_attribute6
      ,null qua_attribute6_meaning
      ,null qua_attribute7
      ,null qua_attribute7_meaning
      ,null qua_attribute8
      ,null qua_attribute8_meaning
      ,null qua_attribute9
      ,null qua_attribute9_meaning
      ,null qua_attribute10
      ,null qua_attribute10_meaning
      ,null qua_attribute11
      ,null qua_attribute11_meaning
      ,null qua_attribute12
      ,null qua_attribute12_meaning
      ,null qua_attribute13
      ,null qua_attribute13_meaning
      ,null qua_attribute14
      ,null qua_attribute14_meaning
      ,null qua_attribute15
      ,null qua_attribute15_meaning
      ,null qua_attribute16
      ,null qua_attribute16_meaning
      ,null qua_attribute17
      ,null qua_attribute17_meaning
      ,null qua_attribute18
      ,null qua_attribute18_meaning
      ,null qua_attribute19
      ,null qua_attribute19_meaning
      ,null qua_attribute20
      ,null qua_attribute20_meaning
      ,null qua_information_category
      ,null qua_information1
      ,null qua_information1_meaning
      ,null qua_information2
      ,null qua_information2_meaning
      ,null qua_information3
      ,null qua_information3_meaning
      ,null qua_information4
      ,null qua_information4_meaning
      ,null qua_information5
      ,null qua_information5_meaning
      ,null qua_information6
      ,null qua_information6_meaning
      ,null qua_information7
      ,null qua_information7_meaning
      ,null qua_information8
      ,null qua_information8_meaning
      ,null qua_information9
      ,null qua_information9_meaning
      ,null qua_information10
      ,null qua_information10_meaning
      ,null qua_information11
      ,null qua_information11_meaning
      ,null qua_information12
      ,null qua_information12_meaning
      ,null qua_information13
      ,null qua_information13_meaning
      ,null qua_information14
      ,null qua_information14_meaning
      ,null qua_information15
      ,null qua_information15_meaning
      ,null qua_information16
      ,null qua_information16_meaning
      ,null qua_information17
      ,null qua_information17_meaning
      ,null qua_information18
      ,null qua_information18_meaning
      ,null qua_information19
      ,null qua_information19_meaning
      ,null qua_information20
      ,null qua_information20_meaning
      ,pend.transaction_id
      ,pend.transaction_step_id
      ,pend.transaction_type
      ,pend.hr_api_status_code
      ,pend.workflow_status_code
      ,xxas_selfservice_workflow_pkg.decode_status(pend.workflow_status_code) workflow_status_meaning
      ,pend.item_type
      ,pend.item_key
      ,'PQH_SS_ATTACHMENT' attachment_entity
      ,pend.transaction_id attachment_pk1
      ,xxas_hr_api_transaction_pkg.varchar2_value(pend.person_id, pend.api_name, pend.transaction_step_id,'P_ATTENDANCE_ID') attendance_id_p
      ,xxas_hr_api_transaction_pkg.varchar2_value(pend.person_id, pend.api_name, pend.transaction_step_id,'P_PEA_OBJECT_VERSION_NUMBER') pea_object_version_number_p
      ,xxas_hr_api_transaction_pkg.varchar2_value(pend.person_id, pend.api_name, pend.transaction_step_id,'P_PQ_OBJECT_VERSION_NUMBER') object_version_number_p
      ,xxas_hr_api_transaction_pkg.varchar2_value(pend.person_id, pend.api_name, pend.transaction_step_id,'P_BUSINESS_GROUP_ID') business_group_id_p
      ,xxas_hr_api_transaction_pkg.date_value(pend.person_id, pend.api_name, pend.transaction_step_id,'P_PEA_START_DATE') attended_start_date_p
      ,xxas_hr_api_transaction_pkg.date_value(pend.person_id, pend.api_name, pend.transaction_step_id,'P_PEA_END_DATE') attended_end_date_p
      ,xxas_hr_api_transaction_pkg.varchar2_value(pend.person_id, pend.api_name, pend.transaction_step_id,'P_PEA_FULL_TIME') full_time_flag_p
      ,xxas_util_pkg.decode_lookup
        ('YES_NO'
        ,xxas_hr_api_transaction_pkg.varchar2_value(pend.person_id, pend.api_name, pend.transaction_step_id,'P_PEA_FULL_TIME')) full_time_meaning_p
      ,xxas_hr_api_transaction_pkg.varchar2_value(pend.person_id, pend.api_name, pend.transaction_step_id,'P_ESTABLISHMENT_ID') establishment_id_p
      ,decode
        (xxas_hr_api_transaction_pkg.varchar2_value(pend.person_id, pend.api_name, pend.transaction_step_id,'P_ESTABLISHMENT_ID')
        ,null, xxas_hr_api_transaction_pkg.varchar2_value(pend.person_id, pend.api_name, pend.transaction_step_id,'P_PE_NAME')
        ,(SELECT pend_pe.name
          FROM   per_establishments pend_pe
          WHERE  pend_pe.establishment_id =
            xxas_hr_api_transaction_pkg.varchar2_value(pend.person_id, pend.api_name, pend.transaction_step_id,'P_ESTABLISHMENT_ID'))) establishment_p
      ,null address_p
      ,xxas_hr_api_transaction_pkg.varchar2_value(pend.person_id, pend.api_name, pend.transaction_step_id,'P_PEA_ATTRIBUTE_CATEGORY') pea_attribute_category_p
      ,xxas_hr_api_transaction_pkg.varchar2_value(pend.person_id, pend.api_name, pend.transaction_step_id,'P_PEA_ATTRIBUTE1') pea_attribute1_p
      ,xxas_com_vset_pkg.decode_descr_vset_value
         ('800','PER_ESTABLISHMENT_ATTENDANCES'
         ,xxas_hr_api_transaction_pkg.varchar2_value(pend.person_id, pend.api_name, pend.transaction_step_id,'P_PEA_ATTRIBUTE_CATEGORY')
         ,'ATTRIBUTE1'
         ,xxas_hr_api_transaction_pkg.varchar2_value(pend.person_id, pend.api_name, pend.transaction_step_id,'P_PEA_ATTRIBUTE1')) pea_attribute1_meaning_p
      ,xxas_hr_api_transaction_pkg.varchar2_value(pend.person_id, pend.api_name, pend.transaction_step_id,'P_PEA_ATTRIBUTE2') pea_attribute2_p
      ,xxas_com_vset_pkg.decode_descr_vset_value
         ('800','PER_ESTABLISHMENT_ATTENDANCES'
         ,xxas_hr_api_transaction_pkg.varchar2_value(pend.person_id, pend.api_name, pend.transaction_step_id,'P_PEA_ATTRIBUTE_CATEGORY')
         ,'ATTRIBUTE2'
         ,xxas_hr_api_transaction_pkg.varchar2_value(pend.person_id, pend.api_name, pend.transaction_step_id,'P_PEA_ATTRIBUTE2')) pea_attribute2_meaning_p
      ,xxas_hr_api_transaction_pkg.varchar2_value(pend.person_id, pend.api_name, pend.transaction_step_id,'P_PEA_ATTRIBUTE3') pea_attribute3_p
      ,xxas_com_vset_pkg.decode_descr_vset_value
         ('800','PER_ESTABLISHMENT_ATTENDANCES'
         ,xxas_hr_api_transaction_pkg.varchar2_value(pend.person_id, pend.api_name, pend.transaction_step_id,'P_PEA_ATTRIBUTE_CATEGORY')
         ,'ATTRIBUTE3'
         ,xxas_hr_api_transaction_pkg.varchar2_value(pend.person_id, pend.api_name, pend.transaction_step_id,'P_PEA_ATTRIBUTE3')) pea_attribute3_meaning_p
      ,xxas_hr_api_transaction_pkg.varchar2_value(pend.person_id, pend.api_name, pend.transaction_step_id,'P_PEA_ATTRIBUTE4') pea_attribute4_p
      ,xxas_com_vset_pkg.decode_descr_vset_value
         ('800','PER_ESTABLISHMENT_ATTENDANCES'
         ,xxas_hr_api_transaction_pkg.varchar2_value(pend.person_id, pend.api_name, pend.transaction_step_id,'P_PEA_ATTRIBUTE_CATEGORY')
         ,'ATTRIBUTE4'
         ,xxas_hr_api_transaction_pkg.varchar2_value(pend.person_id, pend.api_name, pend.transaction_step_id,'P_PEA_ATTRIBUTE4')) pea_attribute4_meaning_p
      ,xxas_hr_api_transaction_pkg.varchar2_value(pend.person_id, pend.api_name, pend.transaction_step_id,'P_PEA_ATTRIBUTE5') pea_attribute5_p
      ,xxas_com_vset_pkg.decode_descr_vset_value
         ('800','PER_ESTABLISHMENT_ATTENDANCES'
         ,xxas_hr_api_transaction_pkg.varchar2_value(pend.person_id, pend.api_name, pend.transaction_step_id,'P_PEA_ATTRIBUTE_CATEGORY')
         ,'ATTRIBUTE5'
         ,xxas_hr_api_transaction_pkg.varchar2_value(pend.person_id, pend.api_name, pend.transaction_step_id,'P_PEA_ATTRIBUTE5')) pea_attribute5_meaning_p
      ,xxas_hr_api_transaction_pkg.varchar2_value(pend.person_id, pend.api_name, pend.transaction_step_id,'P_PEA_ATTRIBUTE6') pea_attribute6_p
      ,xxas_com_vset_pkg.decode_descr_vset_value
         ('800','PER_ESTABLISHMENT_ATTENDANCES'
         ,xxas_hr_api_transaction_pkg.varchar2_value(pend.person_id, pend.api_name, pend.transaction_step_id,'P_PEA_ATTRIBUTE_CATEGORY')
         ,'ATTRIBUTE6'
         ,xxas_hr_api_transaction_pkg.varchar2_value(pend.person_id, pend.api_name, pend.transaction_step_id,'P_PEA_ATTRIBUTE6')) pea_attribute6_meaning_p
      ,xxas_hr_api_transaction_pkg.varchar2_value(pend.person_id, pend.api_name, pend.transaction_step_id,'P_PEA_ATTRIBUTE7') pea_attribute7_p
      ,xxas_com_vset_pkg.decode_descr_vset_value
         ('800','PER_ESTABLISHMENT_ATTENDANCES'
         ,xxas_hr_api_transaction_pkg.varchar2_value(pend.person_id, pend.api_name, pend.transaction_step_id,'P_PEA_ATTRIBUTE_CATEGORY')
         ,'ATTRIBUTE7'
         ,xxas_hr_api_transaction_pkg.varchar2_value(pend.person_id, pend.api_name, pend.transaction_step_id,'P_PEA_ATTRIBUTE7')) pea_attribute7_meaning_p
      ,xxas_hr_api_transaction_pkg.varchar2_value(pend.person_id, pend.api_name, pend.transaction_step_id,'P_PEA_ATTRIBUTE8') pea_attribute8_p
      ,xxas_com_vset_pkg.decode_descr_vset_value
         ('800','PER_ESTABLISHMENT_ATTENDANCES'
         ,xxas_hr_api_transaction_pkg.varchar2_value(pend.person_id, pend.api_name, pend.transaction_step_id,'P_PEA_ATTRIBUTE_CATEGORY')
         ,'ATTRIBUTE8'
         ,xxas_hr_api_transaction_pkg.varchar2_value(pend.person_id, pend.api_name, pend.transaction_step_id,'P_PEA_ATTRIBUTE8')) pea_attribute8_meaning_p
      ,xxas_hr_api_transaction_pkg.varchar2_value(pend.person_id, pend.api_name, pend.transaction_step_id,'P_PEA_ATTRIBUTE9') pea_attribute9_p
      ,xxas_com_vset_pkg.decode_descr_vset_value
         ('800','PER_ESTABLISHMENT_ATTENDANCES'
         ,xxas_hr_api_transaction_pkg.varchar2_value(pend.person_id, pend.api_name, pend.transaction_step_id,'P_PEA_ATTRIBUTE_CATEGORY')
         ,'ATTRIBUTE9'
         ,xxas_hr_api_transaction_pkg.varchar2_value(pend.person_id, pend.api_name, pend.transaction_step_id,'P_PEA_ATTRIBUTE9')) pea_attribute9_meaning_p
      ,xxas_hr_api_transaction_pkg.varchar2_value(pend.person_id, pend.api_name, pend.transaction_step_id,'P_PEA_ATTRIBUTE10') pea_attribute10_p
      ,xxas_com_vset_pkg.decode_descr_vset_value
         ('800','PER_ESTABLISHMENT_ATTENDANCES'
         ,xxas_hr_api_transaction_pkg.varchar2_value(pend.person_id, pend.api_name, pend.transaction_step_id,'P_PEA_ATTRIBUTE_CATEGORY')
         ,'ATTRIBUTE10'
         ,xxas_hr_api_transaction_pkg.varchar2_value(pend.person_id, pend.api_name, pend.transaction_step_id,'P_PEA_ATTRIBUTE10')) pea_attribute10_meaning_p
      ,xxas_hr_api_transaction_pkg.varchar2_value(pend.person_id, pend.api_name, pend.transaction_step_id,'P_PEA_ATTRIBUTE11') pea_attribute11_p
      ,xxas_com_vset_pkg.decode_descr_vset_value
         ('800','PER_ESTABLISHMENT_ATTENDANCES'
         ,xxas_hr_api_transaction_pkg.varchar2_value(pend.person_id, pend.api_name, pend.transaction_step_id,'P_PEA_ATTRIBUTE_CATEGORY')
         ,'ATTRIBUTE11'
         ,xxas_hr_api_transaction_pkg.varchar2_value(pend.person_id, pend.api_name, pend.transaction_step_id,'P_PEA_ATTRIBUTE11')) pea_attribute11_meaning_p
      ,xxas_hr_api_transaction_pkg.varchar2_value(pend.person_id, pend.api_name, pend.transaction_step_id,'P_PEA_ATTRIBUTE12') pea_attribute12_p
      ,xxas_com_vset_pkg.decode_descr_vset_value
         ('800','PER_ESTABLISHMENT_ATTENDANCES'
         ,xxas_hr_api_transaction_pkg.varchar2_value(pend.person_id, pend.api_name, pend.transaction_step_id,'P_PEA_ATTRIBUTE_CATEGORY')
         ,'ATTRIBUTE12'
         ,xxas_hr_api_transaction_pkg.varchar2_value(pend.person_id, pend.api_name, pend.transaction_step_id,'P_PEA_ATTRIBUTE12')) pea_attribute12_meaning_p
      ,xxas_hr_api_transaction_pkg.varchar2_value(pend.person_id, pend.api_name, pend.transaction_step_id,'P_PEA_ATTRIBUTE13') pea_attribute13_p
      ,xxas_com_vset_pkg.decode_descr_vset_value
         ('800','PER_ESTABLISHMENT_ATTENDANCES'
         ,xxas_hr_api_transaction_pkg.varchar2_value(pend.person_id, pend.api_name, pend.transaction_step_id,'P_PEA_ATTRIBUTE_CATEGORY')
         ,'ATTRIBUTE13'
         ,xxas_hr_api_transaction_pkg.varchar2_value(pend.person_id, pend.api_name, pend.transaction_step_id,'P_PEA_ATTRIBUTE13')) pea_attribute13_meaning_p
      ,xxas_hr_api_transaction_pkg.varchar2_value(pend.person_id, pend.api_name, pend.transaction_step_id,'P_PEA_ATTRIBUTE14') pea_attribute14_p
      ,xxas_com_vset_pkg.decode_descr_vset_value
         ('800','PER_ESTABLISHMENT_ATTENDANCES'
         ,xxas_hr_api_transaction_pkg.varchar2_value(pend.person_id, pend.api_name, pend.transaction_step_id,'P_PEA_ATTRIBUTE_CATEGORY')
         ,'ATTRIBUTE14'
         ,xxas_hr_api_transaction_pkg.varchar2_value(pend.person_id, pend.api_name, pend.transaction_step_id,'P_PEA_ATTRIBUTE14')) pea_attribute14_meaning_p
      ,xxas_hr_api_transaction_pkg.varchar2_value(pend.person_id, pend.api_name, pend.transaction_step_id,'P_PEA_ATTRIBUTE15') pea_attribute15_p
      ,xxas_com_vset_pkg.decode_descr_vset_value
         ('800','PER_ESTABLISHMENT_ATTENDANCES'
         ,xxas_hr_api_transaction_pkg.varchar2_value(pend.person_id, pend.api_name, pend.transaction_step_id,'P_PEA_ATTRIBUTE_CATEGORY')
         ,'ATTRIBUTE15'
         ,xxas_hr_api_transaction_pkg.varchar2_value(pend.person_id, pend.api_name, pend.transaction_step_id,'P_PEA_ATTRIBUTE15')) pea_attribute15_meaning_p
      ,xxas_hr_api_transaction_pkg.varchar2_value(pend.person_id, pend.api_name, pend.transaction_step_id,'P_PEA_ATTRIBUTE16') pea_attribute16_p
      ,xxas_com_vset_pkg.decode_descr_vset_value
         ('800','PER_ESTABLISHMENT_ATTENDANCES'
         ,xxas_hr_api_transaction_pkg.varchar2_value(pend.person_id, pend.api_name, pend.transaction_step_id,'P_PEA_ATTRIBUTE_CATEGORY')
         ,'ATTRIBUTE16'
         ,xxas_hr_api_transaction_pkg.varchar2_value(pend.person_id, pend.api_name, pend.transaction_step_id,'P_PEA_ATTRIBUTE16')) pea_attribute16_meaning_p
      ,xxas_hr_api_transaction_pkg.varchar2_value(pend.person_id, pend.api_name, pend.transaction_step_id,'P_PEA_ATTRIBUTE17') pea_attribute17_p
      ,xxas_com_vset_pkg.decode_descr_vset_value
         ('800','PER_ESTABLISHMENT_ATTENDANCES'
         ,xxas_hr_api_transaction_pkg.varchar2_value(pend.person_id, pend.api_name, pend.transaction_step_id,'P_PEA_ATTRIBUTE_CATEGORY')
         ,'ATTRIBUTE17'
         ,xxas_hr_api_transaction_pkg.varchar2_value(pend.person_id, pend.api_name, pend.transaction_step_id,'P_PEA_ATTRIBUTE17')) pea_attribute17_meaning_p
      ,xxas_hr_api_transaction_pkg.varchar2_value(pend.person_id, pend.api_name, pend.transaction_step_id,'P_PEA_ATTRIBUTE18') pea_attribute18_p
      ,xxas_com_vset_pkg.decode_descr_vset_value
         ('800','PER_ESTABLISHMENT_ATTENDANCES'
         ,xxas_hr_api_transaction_pkg.varchar2_value(pend.person_id, pend.api_name, pend.transaction_step_id,'P_PEA_ATTRIBUTE_CATEGORY')
         ,'ATTRIBUTE18'
         ,xxas_hr_api_transaction_pkg.varchar2_value(pend.person_id, pend.api_name, pend.transaction_step_id,'P_PEA_ATTRIBUTE18')) pea_attribute18_meaning_p
      ,xxas_hr_api_transaction_pkg.varchar2_value(pend.person_id, pend.api_name, pend.transaction_step_id,'P_PEA_ATTRIBUTE19') pea_attribute19_p
      ,xxas_com_vset_pkg.decode_descr_vset_value
         ('800','PER_ESTABLISHMENT_ATTENDANCES'
         ,xxas_hr_api_transaction_pkg.varchar2_value(pend.person_id, pend.api_name, pend.transaction_step_id,'P_PEA_ATTRIBUTE_CATEGORY')
         ,'ATTRIBUTE19'
         ,xxas_hr_api_transaction_pkg.varchar2_value(pend.person_id, pend.api_name, pend.transaction_step_id,'P_PEA_ATTRIBUTE19')) pea_attribute19_meaning_p
      ,xxas_hr_api_transaction_pkg.varchar2_value(pend.person_id, pend.api_name, pend.transaction_step_id,'P_PEA_ATTRIBUTE20') pea_attribute20_p
      ,xxas_com_vset_pkg.decode_descr_vset_value
         ('800','PER_ESTABLISHMENT_ATTENDANCES'
         ,xxas_hr_api_transaction_pkg.varchar2_value(pend.person_id, pend.api_name, pend.transaction_step_id,'P_PEA_ATTRIBUTE_CATEGORY')
         ,'ATTRIBUTE20'
         ,xxas_hr_api_transaction_pkg.varchar2_value(pend.person_id, pend.api_name, pend.transaction_step_id,'P_PEA_ATTRIBUTE20')) pea_attribute20_meaning_p
      ,xxas_hr_api_transaction_pkg.varchar2_value(pend.person_id, pend.api_name, pend.transaction_step_id,'P_QUALIFICATION_ID') qualification_id_p
      ,xxas_hr_api_transaction_pkg.varchar2_value(pend.person_id, pend.api_name, pend.transaction_step_id,'P_PQ_TITLE') title_p
      ,xxas_hr_api_transaction_pkg.varchar2_value(pend.person_id, pend.api_name, pend.transaction_step_id,'P_QUALIFICATION_TYPE_ID') qualification_type_id_p
      ,decode
        (xxas_hr_api_transaction_pkg.varchar2_value(pend.person_id, pend.api_name, pend.transaction_step_id,'P_QUALIFICATION_TYPE_ID')
        ,null, null
        ,(SELECT pend_pqtc.category
          FROM   per_qualification_types pend_pqtc
          WHERE  pend_pqtc.qualification_type_id = 
            xxas_hr_api_transaction_pkg.varchar2_value(pend.person_id, pend.api_name, pend.transaction_step_id,'P_QUALIFICATION_TYPE_ID'))) pq_category_code_p
      ,decode
        (xxas_hr_api_transaction_pkg.varchar2_value(pend.person_id, pend.api_name, pend.transaction_step_id,'P_QUALIFICATION_TYPE_ID')
        ,null, null
        ,(SELECT xxas_util_pkg.decode_lookup('PER_CATEGORIES', pend_pqtm.category) 
          FROM   per_qualification_types pend_pqtm
          WHERE  pend_pqtm.qualification_type_id = 
            xxas_hr_api_transaction_pkg.varchar2_value(pend.person_id, pend.api_name, pend.transaction_step_id,'P_QUALIFICATION_TYPE_ID'))) pq_category_meaning_p
      ,decode
        (xxas_hr_api_transaction_pkg.varchar2_value(pend.person_id, pend.api_name, pend.transaction_step_id,'P_QUALIFICATION_TYPE_ID')
        ,null, null
        ,(SELECT pend_pqtr.rank 
          FROM   per_qualification_types pend_pqtr
          WHERE  pend_pqtr.qualification_type_id = 
            xxas_hr_api_transaction_pkg.varchar2_value(pend.person_id, pend.api_name, pend.transaction_step_id,'P_QUALIFICATION_TYPE_ID'))) rank_p
      ,decode
        (xxas_hr_api_transaction_pkg.varchar2_value(pend.person_id, pend.api_name, pend.transaction_step_id,'P_QUALIFICATION_TYPE_ID')
        ,null, null
        ,(SELECT pend_pqtn.name 
          FROM   per_qualification_types pend_pqtn
          WHERE  pend_pqtn.qualification_type_id = 
            xxas_hr_api_transaction_pkg.varchar2_value(pend.person_id, pend.api_name, pend.transaction_step_id,'P_QUALIFICATION_TYPE_ID'))) qualification_name_p
      ,xxas_hr_api_transaction_pkg.varchar2_value(pend.person_id, pend.api_name, pend.transaction_step_id,'P_PQ_GRADE_ATTAINED') grade_attained_p
      ,xxas_hr_api_transaction_pkg.varchar2_value(pend.person_id, pend.api_name, pend.transaction_step_id,'P_PQ_STATUS') status_code_p
      ,xxas_util_pkg.decode_lookup
        ('PER_SUBJECT_STATUSES'
        ,xxas_hr_api_transaction_pkg.varchar2_value(pend.person_id, pend.api_name, pend.transaction_step_id,'P_PQ_STATUS')) status_meaning_p
      ,xxas_hr_api_transaction_pkg.date_value(pend.person_id, pend.api_name, pend.transaction_step_id,'P_PQ_AWARDED_DATE') awarded_date_p
      ,xxas_hr_api_transaction_pkg.varchar2_value(pend.person_id, pend.api_name, pend.transaction_step_id,'P_PQ_FEE') fee_p
      ,xxas_hr_api_transaction_pkg.varchar2_value(pend.person_id, pend.api_name, pend.transaction_step_id,'P_PQ_FEE_CURRENCY') fee_currency_code_p
      ,decode
        (xxas_hr_api_transaction_pkg.varchar2_value(pend.person_id, pend.api_name, pend.transaction_step_id,'P_PQ_FEE_CURRENCY')
        ,null, null
        ,(SELECT pend_fc.name
          FROM   fnd_currencies_vl pend_fc
          WHERE  pend_fc.currency_code = 
            xxas_hr_api_transaction_pkg.varchar2_value(pend.person_id, pend.api_name, pend.transaction_step_id,'P_PQ_FEE_CURRENCY'))) fee_currency_meaning_p
      ,xxas_hr_api_transaction_pkg.varchar2_value(pend.person_id, pend.api_name, pend.transaction_step_id,'P_PQ_COMPLETED_AMOUNT') training_completed_amount_p
      ,xxas_hr_api_transaction_pkg.varchar2_value(pend.person_id, pend.api_name, pend.transaction_step_id,'P_PQ_REIMBURSEMENT') reimbursement_arrangements_p
      ,xxas_hr_api_transaction_pkg.varchar2_value(pend.person_id, pend.api_name, pend.transaction_step_id,'P_PQ_COMPLETED_UNITS') training_completed_units_p
      ,xxas_hr_api_transaction_pkg.varchar2_value(pend.person_id, pend.api_name, pend.transaction_step_id,'P_PQ_TOTAL_AMOUNT') total_training_amount_p
      ,xxas_hr_api_transaction_pkg.date_value(pend.person_id, pend.api_name, pend.transaction_step_id,'P_PQ_START_DATE') start_date_p
      ,xxas_hr_api_transaction_pkg.date_value(pend.person_id, pend.api_name, pend.transaction_step_id,'P_PQ_END_DATE') end_date_p
      ,xxas_hr_api_transaction_pkg.varchar2_value(pend.person_id, pend.api_name, pend.transaction_step_id,'P_PQ_LICENSE_NUMBER') license_number_p
      ,xxas_hr_api_transaction_pkg.date_value(pend.person_id, pend.api_name, pend.transaction_step_id,'P_PQ_EXPIRY_DATE') expiry_date_p
      ,xxas_hr_api_transaction_pkg.varchar2_value(pend.person_id, pend.api_name, pend.transaction_step_id,'P_PQ_LICENSE_RESTRICTIONS') license_restrictions_p
      ,null projected_completion_date_p
      ,xxas_hr_api_transaction_pkg.varchar2_value(pend.person_id, pend.api_name, pend.transaction_step_id,'P_PQ_AWARDING_BODY') awarding_body_p
      ,xxas_hr_api_transaction_pkg.varchar2_value(pend.person_id, pend.api_name, pend.transaction_step_id,'P_PQ_TUITION_METHOD') tuition_method_p
      ,xxas_hr_api_transaction_pkg.varchar2_value(pend.person_id, pend.api_name, pend.transaction_step_id,'P_PQ_GROUP_RANKING') group_ranking_p
      ,null professional_body_name_p
      ,null membership_number_p
      ,null membership_category_p
      ,null subscription_payment_method_p
      ,xxas_hr_api_transaction_pkg.varchar2_value(pend.person_id, pend.api_name, pend.transaction_step_id,'P_PQ_COMMENTS') comments_p
      ,xxas_hr_api_transaction_pkg.varchar2_value(pend.person_id, pend.api_name, pend.transaction_step_id,'P_PQ_ATTRIBUTE_CATEGORY') qua_attribute_category_p
      ,xxas_hr_api_transaction_pkg.varchar2_value(pend.person_id, pend.api_name, pend.transaction_step_id,'P_PQ_ATTRIBUTE1') qua_attribute1_p
      ,xxas_com_vset_pkg.decode_descr_vset_value
         ('800','PER_QUALIFICATIONS'
         ,xxas_hr_api_transaction_pkg.varchar2_value(pend.person_id, pend.api_name, pend.transaction_step_id,'P_PQ_ATTRIBUTE_CATEGORY')
         ,'ATTRIBUTE1'
         ,xxas_hr_api_transaction_pkg.varchar2_value(pend.person_id, pend.api_name, pend.transaction_step_id,'P_PQ_ATTRIBUTE1')) qua_attribute1_meaning_p
      ,xxas_hr_api_transaction_pkg.varchar2_value(pend.person_id, pend.api_name, pend.transaction_step_id,'P_PQ_ATTRIBUTE2') qua_attribute2_p
      ,xxas_com_vset_pkg.decode_descr_vset_value
         ('800','PER_QUALIFICATIONS'
         ,xxas_hr_api_transaction_pkg.varchar2_value(pend.person_id, pend.api_name, pend.transaction_step_id,'P_PQ_ATTRIBUTE_CATEGORY')
         ,'ATTRIBUTE2'
         ,xxas_hr_api_transaction_pkg.varchar2_value(pend.person_id, pend.api_name, pend.transaction_step_id,'P_PQ_ATTRIBUTE2')) qua_attribute2_meaning_p
      ,xxas_hr_api_transaction_pkg.varchar2_value(pend.person_id, pend.api_name, pend.transaction_step_id,'P_PQ_ATTRIBUTE3') qua_attribute3_p
      ,xxas_com_vset_pkg.decode_descr_vset_value
         ('800','PER_QUALIFICATIONS'
         ,xxas_hr_api_transaction_pkg.varchar2_value(pend.person_id, pend.api_name, pend.transaction_step_id,'P_PQ_ATTRIBUTE_CATEGORY')
         ,'ATTRIBUTE3'
         ,xxas_hr_api_transaction_pkg.varchar2_value(pend.person_id, pend.api_name, pend.transaction_step_id,'P_PQ_ATTRIBUTE3')) qua_attribute3_meaning_p
      ,xxas_hr_api_transaction_pkg.varchar2_value(pend.person_id, pend.api_name, pend.transaction_step_id,'P_PQ_ATTRIBUTE4') qua_attribute4_p
      ,xxas_com_vset_pkg.decode_descr_vset_value
         ('800','PER_QUALIFICATIONS'
         ,xxas_hr_api_transaction_pkg.varchar2_value(pend.person_id, pend.api_name, pend.transaction_step_id,'P_PQ_ATTRIBUTE_CATEGORY')
         ,'ATTRIBUTE4'
         ,xxas_hr_api_transaction_pkg.varchar2_value(pend.person_id, pend.api_name, pend.transaction_step_id,'P_PQ_ATTRIBUTE4')) qua_attribute4_meaning_p
      ,xxas_hr_api_transaction_pkg.varchar2_value(pend.person_id, pend.api_name, pend.transaction_step_id,'P_PQ_ATTRIBUTE5') qua_attribute5_p
      ,xxas_com_vset_pkg.decode_descr_vset_value
         ('800','PER_QUALIFICATIONS'
         ,xxas_hr_api_transaction_pkg.varchar2_value(pend.person_id, pend.api_name, pend.transaction_step_id,'P_PQ_ATTRIBUTE_CATEGORY')
         ,'ATTRIBUTE5'
         ,xxas_hr_api_transaction_pkg.varchar2_value(pend.person_id, pend.api_name, pend.transaction_step_id,'P_PQ_ATTRIBUTE5')) qua_attribute5_meaning_p
      ,xxas_hr_api_transaction_pkg.varchar2_value(pend.person_id, pend.api_name, pend.transaction_step_id,'P_PQ_ATTRIBUTE6') qua_attribute6_p
      ,xxas_com_vset_pkg.decode_descr_vset_value
         ('800','PER_QUALIFICATIONS'
         ,xxas_hr_api_transaction_pkg.varchar2_value(pend.person_id, pend.api_name, pend.transaction_step_id,'P_PQ_ATTRIBUTE_CATEGORY')
         ,'ATTRIBUTE6'
         ,xxas_hr_api_transaction_pkg.varchar2_value(pend.person_id, pend.api_name, pend.transaction_step_id,'P_PQ_ATTRIBUTE6')) qua_attribute6_meaning_p
      ,xxas_hr_api_transaction_pkg.varchar2_value(pend.person_id, pend.api_name, pend.transaction_step_id,'P_PQ_ATTRIBUTE7') qua_attribute7_p
      ,xxas_com_vset_pkg.decode_descr_vset_value
         ('800','PER_QUALIFICATIONS'
         ,xxas_hr_api_transaction_pkg.varchar2_value(pend.person_id, pend.api_name, pend.transaction_step_id,'P_PQ_ATTRIBUTE_CATEGORY')
         ,'ATTRIBUTE7'
         ,xxas_hr_api_transaction_pkg.varchar2_value(pend.person_id, pend.api_name, pend.transaction_step_id,'P_PQ_ATTRIBUTE7')) qua_attribute7_meaning_p
      ,xxas_hr_api_transaction_pkg.varchar2_value(pend.person_id, pend.api_name, pend.transaction_step_id,'P_PQ_ATTRIBUTE8') qua_attribute8_p
      ,xxas_com_vset_pkg.decode_descr_vset_value
         ('800','PER_QUALIFICATIONS'
         ,xxas_hr_api_transaction_pkg.varchar2_value(pend.person_id, pend.api_name, pend.transaction_step_id,'P_PQ_ATTRIBUTE_CATEGORY')
         ,'ATTRIBUTE8'
         ,xxas_hr_api_transaction_pkg.varchar2_value(pend.person_id, pend.api_name, pend.transaction_step_id,'P_PQ_ATTRIBUTE8')) qua_attribute8_meaning_p
      ,xxas_hr_api_transaction_pkg.varchar2_value(pend.person_id, pend.api_name, pend.transaction_step_id,'P_PQ_ATTRIBUTE9') qua_attribute9_p
      ,xxas_com_vset_pkg.decode_descr_vset_value
         ('800','PER_QUALIFICATIONS'
         ,xxas_hr_api_transaction_pkg.varchar2_value(pend.person_id, pend.api_name, pend.transaction_step_id,'P_PQ_ATTRIBUTE_CATEGORY')
         ,'ATTRIBUTE9'
         ,xxas_hr_api_transaction_pkg.varchar2_value(pend.person_id, pend.api_name, pend.transaction_step_id,'P_PQ_ATTRIBUTE9')) qua_attribute9_meaning_p
      ,xxas_hr_api_transaction_pkg.varchar2_value(pend.person_id, pend.api_name, pend.transaction_step_id,'P_PQ_ATTRIBUTE10') qua_attribute10_p
      ,xxas_com_vset_pkg.decode_descr_vset_value
         ('800','PER_QUALIFICATIONS'
         ,xxas_hr_api_transaction_pkg.varchar2_value(pend.person_id, pend.api_name, pend.transaction_step_id,'P_PQ_ATTRIBUTE_CATEGORY')
         ,'ATTRIBUTE10'
         ,xxas_hr_api_transaction_pkg.varchar2_value(pend.person_id, pend.api_name, pend.transaction_step_id,'P_PQ_ATTRIBUTE10')) qua_attribute10_meaning_p
      ,xxas_hr_api_transaction_pkg.varchar2_value(pend.person_id, pend.api_name, pend.transaction_step_id,'P_PQ_ATTRIBUTE11') qua_attribute11_p
      ,xxas_com_vset_pkg.decode_descr_vset_value
         ('800','PER_QUALIFICATIONS'
         ,xxas_hr_api_transaction_pkg.varchar2_value(pend.person_id, pend.api_name, pend.transaction_step_id,'P_PQ_ATTRIBUTE_CATEGORY')
         ,'ATTRIBUTE11'
         ,xxas_hr_api_transaction_pkg.varchar2_value(pend.person_id, pend.api_name, pend.transaction_step_id,'P_PQ_ATTRIBUTE11')) qua_attribute11_meaning_p
      ,xxas_hr_api_transaction_pkg.varchar2_value(pend.person_id, pend.api_name, pend.transaction_step_id,'P_PQ_ATTRIBUTE12') qua_attribute12_p
      ,xxas_com_vset_pkg.decode_descr_vset_value
         ('800','PER_QUALIFICATIONS'
         ,xxas_hr_api_transaction_pkg.varchar2_value(pend.person_id, pend.api_name, pend.transaction_step_id,'P_PQ_ATTRIBUTE_CATEGORY')
         ,'ATTRIBUTE12'
         ,xxas_hr_api_transaction_pkg.varchar2_value(pend.person_id, pend.api_name, pend.transaction_step_id,'P_PQ_ATTRIBUTE12')) qua_attribute12_meaning_p
      ,xxas_hr_api_transaction_pkg.varchar2_value(pend.person_id, pend.api_name, pend.transaction_step_id,'P_PQ_ATTRIBUTE13') qua_attribute13_p
      ,xxas_com_vset_pkg.decode_descr_vset_value
         ('800','PER_QUALIFICATIONS'
         ,xxas_hr_api_transaction_pkg.varchar2_value(pend.person_id, pend.api_name, pend.transaction_step_id,'P_PQ_ATTRIBUTE_CATEGORY')
         ,'ATTRIBUTE13'
         ,xxas_hr_api_transaction_pkg.varchar2_value(pend.person_id, pend.api_name, pend.transaction_step_id,'P_PQ_ATTRIBUTE13')) qua_attribute13_meaning_p
      ,xxas_hr_api_transaction_pkg.varchar2_value(pend.person_id, pend.api_name, pend.transaction_step_id,'P_PQ_ATTRIBUTE14') qua_attribute14_p
      ,xxas_com_vset_pkg.decode_descr_vset_value
         ('800','PER_QUALIFICATIONS'
         ,xxas_hr_api_transaction_pkg.varchar2_value(pend.person_id, pend.api_name, pend.transaction_step_id,'P_PQ_ATTRIBUTE_CATEGORY')
         ,'ATTRIBUTE14'
         ,xxas_hr_api_transaction_pkg.varchar2_value(pend.person_id, pend.api_name, pend.transaction_step_id,'P_PQ_ATTRIBUTE14')) qua_attribute14_meaning_p
      ,xxas_hr_api_transaction_pkg.varchar2_value(pend.person_id, pend.api_name, pend.transaction_step_id,'P_PQ_ATTRIBUTE15') qua_attribute15_p
      ,xxas_com_vset_pkg.decode_descr_vset_value
         ('800','PER_QUALIFICATIONS'
         ,xxas_hr_api_transaction_pkg.varchar2_value(pend.person_id, pend.api_name, pend.transaction_step_id,'P_PQ_ATTRIBUTE_CATEGORY')
         ,'ATTRIBUTE15'
         ,xxas_hr_api_transaction_pkg.varchar2_value(pend.person_id, pend.api_name, pend.transaction_step_id,'P_PQ_ATTRIBUTE15')) qua_attribute15_meaning_p
      ,xxas_hr_api_transaction_pkg.varchar2_value(pend.person_id, pend.api_name, pend.transaction_step_id,'P_PQ_ATTRIBUTE16') qua_attribute16_p
      ,xxas_com_vset_pkg.decode_descr_vset_value
         ('800','PER_QUALIFICATIONS'
         ,xxas_hr_api_transaction_pkg.varchar2_value(pend.person_id, pend.api_name, pend.transaction_step_id,'P_PQ_ATTRIBUTE_CATEGORY')
         ,'ATTRIBUTE16'
         ,xxas_hr_api_transaction_pkg.varchar2_value(pend.person_id, pend.api_name, pend.transaction_step_id,'P_PQ_ATTRIBUTE16')) qua_attribute16_meaning_p
      ,xxas_hr_api_transaction_pkg.varchar2_value(pend.person_id, pend.api_name, pend.transaction_step_id,'P_PQ_ATTRIBUTE17') qua_attribute17_p
      ,xxas_com_vset_pkg.decode_descr_vset_value
         ('800','PER_QUALIFICATIONS'
         ,xxas_hr_api_transaction_pkg.varchar2_value(pend.person_id, pend.api_name, pend.transaction_step_id,'P_PQ_ATTRIBUTE_CATEGORY')
         ,'ATTRIBUTE17'
         ,xxas_hr_api_transaction_pkg.varchar2_value(pend.person_id, pend.api_name, pend.transaction_step_id,'P_PQ_ATTRIBUTE17')) qua_attribute17_meaning_p
      ,xxas_hr_api_transaction_pkg.varchar2_value(pend.person_id, pend.api_name, pend.transaction_step_id,'P_PQ_ATTRIBUTE18') qua_attribute18_p
      ,xxas_com_vset_pkg.decode_descr_vset_value
         ('800','PER_QUALIFICATIONS'
         ,xxas_hr_api_transaction_pkg.varchar2_value(pend.person_id, pend.api_name, pend.transaction_step_id,'P_PQ_ATTRIBUTE_CATEGORY')
         ,'ATTRIBUTE18'
         ,xxas_hr_api_transaction_pkg.varchar2_value(pend.person_id, pend.api_name, pend.transaction_step_id,'P_PQ_ATTRIBUTE18')) qua_attribute18_meaning_p
      ,xxas_hr_api_transaction_pkg.varchar2_value(pend.person_id, pend.api_name, pend.transaction_step_id,'P_PQ_ATTRIBUTE19') qua_attribute19_p
      ,xxas_com_vset_pkg.decode_descr_vset_value
         ('800','PER_QUALIFICATIONS'
         ,xxas_hr_api_transaction_pkg.varchar2_value(pend.person_id, pend.api_name, pend.transaction_step_id,'P_PQ_ATTRIBUTE_CATEGORY')
         ,'ATTRIBUTE19'
         ,xxas_hr_api_transaction_pkg.varchar2_value(pend.person_id, pend.api_name, pend.transaction_step_id,'P_PQ_ATTRIBUTE19')) qua_attribute19_meaning_p
      ,xxas_hr_api_transaction_pkg.varchar2_value(pend.person_id, pend.api_name, pend.transaction_step_id,'P_PQ_ATTRIBUTE20') qua_attribute20_p
      ,xxas_com_vset_pkg.decode_descr_vset_value
         ('800','PER_QUALIFICATIONS'
         ,xxas_hr_api_transaction_pkg.varchar2_value(pend.person_id, pend.api_name, pend.transaction_step_id,'P_PQ_ATTRIBUTE_CATEGORY')
         ,'ATTRIBUTE20'
         ,xxas_hr_api_transaction_pkg.varchar2_value(pend.person_id, pend.api_name, pend.transaction_step_id,'P_PQ_ATTRIBUTE20')) qua_attribute20_meaning_p
      ,xxas_hr_api_transaction_pkg.varchar2_value(pend.person_id, pend.api_name, pend.transaction_step_id,'P_PQ_QUA_INFORMATION_CATEGORY') qua_information_category_p
      ,xxas_hr_api_transaction_pkg.varchar2_value(pend.person_id, pend.api_name, pend.transaction_step_id,'P_PQ_QUA_INFORMATION1') qua_information1_p
      ,xxas_com_vset_pkg.decode_descr_vset_value
         ('800','Qualification Developer DF'
         ,xxas_hr_api_transaction_pkg.varchar2_value(pend.person_id, pend.api_name, pend.transaction_step_id,'P_PQ_QUA_INFORMATION_CATEGORY')
         ,'QUA_INFORMATION1'
         ,xxas_hr_api_transaction_pkg.varchar2_value(pend.person_id, pend.api_name, pend.transaction_step_id,'P_PQ_QUA_INFORMATION1')) qua_information1_meaning_p
      ,xxas_hr_api_transaction_pkg.varchar2_value(pend.person_id, pend.api_name, pend.transaction_step_id,'P_PQ_QUA_INFORMATION2') qua_information2_p
      ,xxas_com_vset_pkg.decode_descr_vset_value
         ('800','Qualification Developer DF'
         ,xxas_hr_api_transaction_pkg.varchar2_value(pend.person_id, pend.api_name, pend.transaction_step_id,'P_PQ_QUA_INFORMATION_CATEGORY')
         ,'QUA_INFORMATION2'
         ,xxas_hr_api_transaction_pkg.varchar2_value(pend.person_id, pend.api_name, pend.transaction_step_id,'P_PQ_QUA_INFORMATION2')) qua_information2_meaning_p
      ,xxas_hr_api_transaction_pkg.varchar2_value(pend.person_id, pend.api_name, pend.transaction_step_id,'P_PQ_QUA_INFORMATION3') qua_information3_p
      ,xxas_com_vset_pkg.decode_descr_vset_value
         ('800','Qualification Developer DF'
         ,xxas_hr_api_transaction_pkg.varchar2_value(pend.person_id, pend.api_name, pend.transaction_step_id,'P_PQ_QUA_INFORMATION_CATEGORY')
         ,'QUA_INFORMATION3'
         ,xxas_hr_api_transaction_pkg.varchar2_value(pend.person_id, pend.api_name, pend.transaction_step_id,'P_PQ_QUA_INFORMATION3')) qua_information3_meaning_p
      ,xxas_hr_api_transaction_pkg.varchar2_value(pend.person_id, pend.api_name, pend.transaction_step_id,'P_PQ_QUA_INFORMATION4') qua_information4_p
      ,xxas_com_vset_pkg.decode_descr_vset_value
         ('800','Qualification Developer DF'
         ,xxas_hr_api_transaction_pkg.varchar2_value(pend.person_id, pend.api_name, pend.transaction_step_id,'P_PQ_QUA_INFORMATION_CATEGORY')
         ,'QUA_INFORMATION4'
         ,xxas_hr_api_transaction_pkg.varchar2_value(pend.person_id, pend.api_name, pend.transaction_step_id,'P_PQ_QUA_INFORMATION4')) qua_information4_meaning_p
      ,xxas_hr_api_transaction_pkg.varchar2_value(pend.person_id, pend.api_name, pend.transaction_step_id,'P_PQ_QUA_INFORMATION5') qua_information5_p
      ,xxas_com_vset_pkg.decode_descr_vset_value
         ('800','Qualification Developer DF'
         ,xxas_hr_api_transaction_pkg.varchar2_value(pend.person_id, pend.api_name, pend.transaction_step_id,'P_PQ_QUA_INFORMATION_CATEGORY')
         ,'QUA_INFORMATION5'
         ,xxas_hr_api_transaction_pkg.varchar2_value(pend.person_id, pend.api_name, pend.transaction_step_id,'P_PQ_QUA_INFORMATION5')) qua_information5_meaning_p
      ,xxas_hr_api_transaction_pkg.varchar2_value(pend.person_id, pend.api_name, pend.transaction_step_id,'P_PQ_QUA_INFORMATION6') qua_information6_p
      ,xxas_com_vset_pkg.decode_descr_vset_value
         ('800','Qualification Developer DF'
         ,xxas_hr_api_transaction_pkg.varchar2_value(pend.person_id, pend.api_name, pend.transaction_step_id,'P_PQ_QUA_INFORMATION_CATEGORY')
         ,'QUA_INFORMATION6'
         ,xxas_hr_api_transaction_pkg.varchar2_value(pend.person_id, pend.api_name, pend.transaction_step_id,'P_PQ_QUA_INFORMATION6')) qua_information6_meaning_p
      ,xxas_hr_api_transaction_pkg.varchar2_value(pend.person_id, pend.api_name, pend.transaction_step_id,'P_PQ_QUA_INFORMATION7') qua_information7_p
      ,xxas_com_vset_pkg.decode_descr_vset_value
         ('800','Qualification Developer DF'
         ,xxas_hr_api_transaction_pkg.varchar2_value(pend.person_id, pend.api_name, pend.transaction_step_id,'P_PQ_QUA_INFORMATION_CATEGORY')
         ,'QUA_INFORMATION7'
         ,xxas_hr_api_transaction_pkg.varchar2_value(pend.person_id, pend.api_name, pend.transaction_step_id,'P_PQ_QUA_INFORMATION7')) qua_information7_meaning_p
      ,xxas_hr_api_transaction_pkg.varchar2_value(pend.person_id, pend.api_name, pend.transaction_step_id,'P_PQ_QUA_INFORMATION8') qua_information8_p
      ,xxas_com_vset_pkg.decode_descr_vset_value
         ('800','Qualification Developer DF'
         ,xxas_hr_api_transaction_pkg.varchar2_value(pend.person_id, pend.api_name, pend.transaction_step_id,'P_PQ_QUA_INFORMATION_CATEGORY')
         ,'QUA_INFORMATION8'
         ,xxas_hr_api_transaction_pkg.varchar2_value(pend.person_id, pend.api_name, pend.transaction_step_id,'P_PQ_QUA_INFORMATION8')) qua_information8_meaning_p
      ,xxas_hr_api_transaction_pkg.varchar2_value(pend.person_id, pend.api_name, pend.transaction_step_id,'P_PQ_QUA_INFORMATION9') qua_information9_p
      ,xxas_com_vset_pkg.decode_descr_vset_value
         ('800','Qualification Developer DF'
         ,xxas_hr_api_transaction_pkg.varchar2_value(pend.person_id, pend.api_name, pend.transaction_step_id,'P_PQ_QUA_INFORMATION_CATEGORY')
         ,'QUA_INFORMATION9'
         ,xxas_hr_api_transaction_pkg.varchar2_value(pend.person_id, pend.api_name, pend.transaction_step_id,'P_PQ_QUA_INFORMATION9')) qua_information9_meaning_p
      ,xxas_hr_api_transaction_pkg.varchar2_value(pend.person_id, pend.api_name, pend.transaction_step_id,'P_PQ_QUA_INFORMATION10') qua_information10_p
      ,xxas_com_vset_pkg.decode_descr_vset_value
         ('800','Qualification Developer DF'
         ,xxas_hr_api_transaction_pkg.varchar2_value(pend.person_id, pend.api_name, pend.transaction_step_id,'P_PQ_QUA_INFORMATION_CATEGORY')
         ,'QUA_INFORMATION10'
         ,xxas_hr_api_transaction_pkg.varchar2_value(pend.person_id, pend.api_name, pend.transaction_step_id,'P_PQ_QUA_INFORMATION10')) qua_information10_meaning_p
      ,xxas_hr_api_transaction_pkg.varchar2_value(pend.person_id, pend.api_name, pend.transaction_step_id,'P_PQ_QUA_INFORMATION11') qua_information11_p
      ,xxas_com_vset_pkg.decode_descr_vset_value
         ('800','Qualification Developer DF'
         ,xxas_hr_api_transaction_pkg.varchar2_value(pend.person_id, pend.api_name, pend.transaction_step_id,'P_PQ_QUA_INFORMATION_CATEGORY')
         ,'QUA_INFORMATION11'
         ,xxas_hr_api_transaction_pkg.varchar2_value(pend.person_id, pend.api_name, pend.transaction_step_id,'P_PQ_QUA_INFORMATION11')) qua_information11_meaning_p
      ,xxas_hr_api_transaction_pkg.varchar2_value(pend.person_id, pend.api_name, pend.transaction_step_id,'P_PQ_QUA_INFORMATION12') qua_information12_p
      ,xxas_com_vset_pkg.decode_descr_vset_value
         ('800','Qualification Developer DF'
         ,xxas_hr_api_transaction_pkg.varchar2_value(pend.person_id, pend.api_name, pend.transaction_step_id,'P_PQ_QUA_INFORMATION_CATEGORY')
         ,'QUA_INFORMATION12'
         ,xxas_hr_api_transaction_pkg.varchar2_value(pend.person_id, pend.api_name, pend.transaction_step_id,'P_PQ_QUA_INFORMATION12')) qua_information12_meaning_p
      ,xxas_hr_api_transaction_pkg.varchar2_value(pend.person_id, pend.api_name, pend.transaction_step_id,'P_PQ_QUA_INFORMATION13') qua_information13_p
      ,xxas_com_vset_pkg.decode_descr_vset_value
         ('800','Qualification Developer DF'
         ,xxas_hr_api_transaction_pkg.varchar2_value(pend.person_id, pend.api_name, pend.transaction_step_id,'P_PQ_QUA_INFORMATION_CATEGORY')
         ,'QUA_INFORMATION13'
         ,xxas_hr_api_transaction_pkg.varchar2_value(pend.person_id, pend.api_name, pend.transaction_step_id,'P_PQ_QUA_INFORMATION13')) qua_information13_meaning_p
      ,xxas_hr_api_transaction_pkg.varchar2_value(pend.person_id, pend.api_name, pend.transaction_step_id,'P_PQ_QUA_INFORMATION14') qua_information14_p
      ,xxas_com_vset_pkg.decode_descr_vset_value
         ('800','Qualification Developer DF'
         ,xxas_hr_api_transaction_pkg.varchar2_value(pend.person_id, pend.api_name, pend.transaction_step_id,'P_PQ_QUA_INFORMATION_CATEGORY')
         ,'QUA_INFORMATION14'
         ,xxas_hr_api_transaction_pkg.varchar2_value(pend.person_id, pend.api_name, pend.transaction_step_id,'P_PQ_QUA_INFORMATION14')) qua_information14_meaning_p
      ,xxas_hr_api_transaction_pkg.varchar2_value(pend.person_id, pend.api_name, pend.transaction_step_id,'P_PQ_QUA_INFORMATION15') qua_information15_p
      ,xxas_com_vset_pkg.decode_descr_vset_value
         ('800','Qualification Developer DF'
         ,xxas_hr_api_transaction_pkg.varchar2_value(pend.person_id, pend.api_name, pend.transaction_step_id,'P_PQ_QUA_INFORMATION_CATEGORY')
         ,'QUA_INFORMATION15'
         ,xxas_hr_api_transaction_pkg.varchar2_value(pend.person_id, pend.api_name, pend.transaction_step_id,'P_PQ_QUA_INFORMATION15')) qua_information15_meaning_p
      ,xxas_hr_api_transaction_pkg.varchar2_value(pend.person_id, pend.api_name, pend.transaction_step_id,'P_PQ_QUA_INFORMATION16') qua_information16_p
      ,xxas_com_vset_pkg.decode_descr_vset_value
         ('800','Qualification Developer DF'
         ,xxas_hr_api_transaction_pkg.varchar2_value(pend.person_id, pend.api_name, pend.transaction_step_id,'P_PQ_QUA_INFORMATION_CATEGORY')
         ,'QUA_INFORMATION16'
         ,xxas_hr_api_transaction_pkg.varchar2_value(pend.person_id, pend.api_name, pend.transaction_step_id,'P_PQ_QUA_INFORMATION16')) qua_information16_meaning_p
      ,xxas_hr_api_transaction_pkg.varchar2_value(pend.person_id, pend.api_name, pend.transaction_step_id,'P_PQ_QUA_INFORMATION17') qua_information17_p
      ,xxas_com_vset_pkg.decode_descr_vset_value
         ('800','Qualification Developer DF'
         ,xxas_hr_api_transaction_pkg.varchar2_value(pend.person_id, pend.api_name, pend.transaction_step_id,'P_PQ_QUA_INFORMATION_CATEGORY')
         ,'QUA_INFORMATION17'
         ,xxas_hr_api_transaction_pkg.varchar2_value(pend.person_id, pend.api_name, pend.transaction_step_id,'P_PQ_QUA_INFORMATION17')) qua_information17_meaning_p
      ,xxas_hr_api_transaction_pkg.varchar2_value(pend.person_id, pend.api_name, pend.transaction_step_id,'P_PQ_QUA_INFORMATION18') qua_information18_p
      ,xxas_com_vset_pkg.decode_descr_vset_value
         ('800','Qualification Developer DF'
         ,xxas_hr_api_transaction_pkg.varchar2_value(pend.person_id, pend.api_name, pend.transaction_step_id,'P_PQ_QUA_INFORMATION_CATEGORY')
         ,'QUA_INFORMATION18'
         ,xxas_hr_api_transaction_pkg.varchar2_value(pend.person_id, pend.api_name, pend.transaction_step_id,'P_PQ_QUA_INFORMATION18')) qua_information18_meaning_p
      ,xxas_hr_api_transaction_pkg.varchar2_value(pend.person_id, pend.api_name, pend.transaction_step_id,'P_PQ_QUA_INFORMATION19') qua_information19_p
      ,xxas_com_vset_pkg.decode_descr_vset_value
         ('800','Qualification Developer DF'
         ,xxas_hr_api_transaction_pkg.varchar2_value(pend.person_id, pend.api_name, pend.transaction_step_id,'P_PQ_QUA_INFORMATION_CATEGORY')
         ,'QUA_INFORMATION19'
         ,xxas_hr_api_transaction_pkg.varchar2_value(pend.person_id, pend.api_name, pend.transaction_step_id,'P_PQ_QUA_INFORMATION19')) qua_information19_meaning_p
      ,xxas_hr_api_transaction_pkg.varchar2_value(pend.person_id, pend.api_name, pend.transaction_step_id,'P_PQ_QUA_INFORMATION20') qua_information20_p
      ,xxas_com_vset_pkg.decode_descr_vset_value
         ('800','Qualification Developer DF'
         ,xxas_hr_api_transaction_pkg.varchar2_value(pend.person_id, pend.api_name, pend.transaction_step_id,'P_PQ_QUA_INFORMATION_CATEGORY')
         ,'QUA_INFORMATION20'
         ,xxas_hr_api_transaction_pkg.varchar2_value(pend.person_id, pend.api_name, pend.transaction_step_id,'P_PQ_QUA_INFORMATION20')) qua_information20_meaning_p
FROM  (SELECT hat.selected_person_id person_id
       ,      hats.api_name
       ,      hat.transaction_id
       ,      hats.transaction_step_id
       ,      hat.status hr_api_status_code
       ,      'INSERT' transaction_type
       ,      eoa.varchar2_value education_or_award
       ,      xxas_selfservice_workflow_pkg.transcode_status(hat.status) workflow_status_code
       ,      hat.item_type
       ,      hat.item_key
       FROM   hr_api_transactions       hat
             ,hr_api_transaction_steps  hats
             ,hr_api_transaction_values tt
             ,hr_api_transaction_values eoa
      WHERE   hat.transaction_id       = hats.transaction_id
      AND     hats.api_name            = 'HR_QUA_AWARDS_UTIL_SS.PROCESS_API'
      AND     hats.transaction_step_id = tt.transaction_step_id
      AND     tt.name                  = 'P_QUALIFICATION_ID'
      AND     tt.varchar2_value        IS NULL
      AND     hats.transaction_step_id = eoa.transaction_step_id
      AND     eoa.name                  = 'P_MODE'
      AND     hat.status          NOT IN ('D', 'N', 'W', 'ACTIVE', 'AC')) pend;

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