/* $Id$ */
/*============================================================================*
 |                 Copyright (c) 2016 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_emp_leaver_request_v 
AS
SELECT pps.period_of_service_id
      ,pps.object_version_number
      ,pps.business_group_id
      ,null effective_date_option
      ,null last_day_of_work
      ,(SELECT count(*) FROM table(xxas_mss_util_pkg.direct_reportees_data(pps.person_id,paaf.assignment_id))) direct_reports_count
      ,(CASE WHEN (SELECT count(*) FROM table(xxas_mss_util_pkg.direct_reportees_data(pps.person_id,paaf.assignment_id))) >= 1
             THEN 'Y'
             ELSE 'N'
        END) is_manager
      ,(CASE WHEN (SELECT count(*) FROM table(xxas_mss_util_pkg.org_info_data(pps.person_id))) >= 1
             THEN 'Y'
             ELSE 'N'
        END) is_org_info
      ,pps.person_id new_manager_dr
      ,pps.termination_accepted_person_id term_acc_person_id
      ,pps.person_id
      ,null person_type
      ,null asg_status
      ,paaf.assignment_id
      ,ppf.rehire_recommendation                  rehire_flag
      ,ppf.rehire_reason
      ,pps.date_start
      ,pps.accepted_termination_date
      ,pps.actual_termination_date
      ,pps.final_process_date
      ,pps.last_standard_process_date
      ,pps.leaving_reason                         leav_reas
      ,pps.notified_termination_date              notification_date
      ,pps.projected_termination_date             projected_date
      ,pps.request_id
      ,pps.program_application_id
      ,pps.program_id
      ,pps.program_update_date
      ,pps.attribute_category
      ,pps.attribute1
      ,xxas_com_vset_pkg.decode_descr_vset_value
         ('800'
         ,'PER_PERIODS_OF_SERVICE'
         ,pps.attribute_category
         ,'ATTRIBUTE1'
         ,pps.attribute1)                         attribute1_meaning 
      ,pps.attribute2
      ,xxas_com_vset_pkg.decode_descr_vset_value
         ('800'
         ,'PER_PERIODS_OF_SERVICE'
         ,pps.attribute_category
         ,'ATTRIBUTE2'
         ,pps.attribute2)                         attribute2_meaning 
      ,pps.attribute3
      ,xxas_com_vset_pkg.decode_descr_vset_value
         ('800'
         ,'PER_PERIODS_OF_SERVICE'
         ,pps.attribute_category
         ,'ATTRIBUTE3'
         ,pps.attribute3)                         attribute3_meaning 
      ,pps.attribute4
      ,xxas_com_vset_pkg.decode_descr_vset_value
         ('800'
         ,'PER_PERIODS_OF_SERVICE'
         ,pps.attribute_category
         ,'ATTRIBUTE4'
         ,pps.attribute4)                         attribute4_meaning 
      ,pps.attribute5
      ,xxas_com_vset_pkg.decode_descr_vset_value
         ('800'
         ,'PER_PERIODS_OF_SERVICE'
         ,pps.attribute_category
         ,'ATTRIBUTE5'
         ,pps.attribute5)                         attribute5_meaning 
      ,pps.attribute6
      ,xxas_com_vset_pkg.decode_descr_vset_value
         ('800'
         ,'PER_PERIODS_OF_SERVICE'
         ,pps.attribute_category
         ,'ATTRIBUTE6'
         ,pps.attribute6)                         attribute6_meaning 
      ,pps.attribute7
      ,xxas_com_vset_pkg.decode_descr_vset_value
         ('800'
         ,'PER_PERIODS_OF_SERVICE'
         ,pps.attribute_category
         ,'ATTRIBUTE7'
         ,pps.attribute7)                         attribute7_meaning 
      ,pps.attribute8
      ,xxas_com_vset_pkg.decode_descr_vset_value
         ('800'
         ,'PER_PERIODS_OF_SERVICE'
         ,pps.attribute_category
         ,'ATTRIBUTE8'
         ,pps.attribute8)                         attribute8_meaning 
      ,pps.attribute9
      ,xxas_com_vset_pkg.decode_descr_vset_value
         ('800'
         ,'PER_PERIODS_OF_SERVICE'
         ,pps.attribute_category
         ,'ATTRIBUTE9'
         ,pps.attribute9)                         attribute9_meaning 
      ,pps.attribute10
      ,xxas_com_vset_pkg.decode_descr_vset_value
         ('800'
         ,'PER_PERIODS_OF_SERVICE'
         ,pps.attribute_category
         ,'ATTRIBUTE10'
         ,pps.attribute10)                       attribute10_meaning 
      ,pps.attribute11
      ,xxas_com_vset_pkg.decode_descr_vset_value
         ('800'
         ,'PER_PERIODS_OF_SERVICE'
         ,pps.attribute_category
         ,'ATTRIBUTE11'
         ,pps.attribute11)                       attribute11_meaning 
      ,pps.attribute12
      ,xxas_com_vset_pkg.decode_descr_vset_value
         ('800'
         ,'PER_PERIODS_OF_SERVICE'
         ,pps.attribute_category
         ,'ATTRIBUTE12'
         ,pps.attribute12)                        attribute12_meaning 
      ,pps.attribute13
      ,xxas_com_vset_pkg.decode_descr_vset_value
         ('800'
         ,'PER_PERIODS_OF_SERVICE'
         ,pps.attribute_category
         ,'ATTRIBUTE13'
         ,pps.attribute13)                        attribute13_meaning 
      ,pps.attribute14
      ,xxas_com_vset_pkg.decode_descr_vset_value
         ('800'
         ,'PER_PERIODS_OF_SERVICE'
         ,pps.attribute_category
         ,'ATTRIBUTE14'
         ,pps.attribute14)                        attribute14_meaning 
      ,pps.attribute15
      ,xxas_com_vset_pkg.decode_descr_vset_value
         ('800'
         ,'PER_PERIODS_OF_SERVICE'
         ,pps.attribute_category
         ,'ATTRIBUTE15'
         ,pps.attribute15)                        attribute15_meaning 
      ,pps.attribute16
      ,xxas_com_vset_pkg.decode_descr_vset_value
         ('800'
         ,'PER_PERIODS_OF_SERVICE'
         ,pps.attribute_category
         ,'ATTRIBUTE16'
         ,pps.attribute16)                        attribute16_meaning 
      ,pps.attribute17
      ,xxas_com_vset_pkg.decode_descr_vset_value
         ('800'
         ,'PER_PERIODS_OF_SERVICE'
         ,pps.attribute_category
         ,'ATTRIBUTE17'
         ,pps.attribute17)                        attribute17_meaning 
      ,pps.attribute18
      ,xxas_com_vset_pkg.decode_descr_vset_value
         ('800'
         ,'PER_PERIODS_OF_SERVICE'
         ,pps.attribute_category
         ,'ATTRIBUTE18'
         ,pps.attribute18)                        attribute18_meaning 
      ,pps.attribute19
      ,xxas_com_vset_pkg.decode_descr_vset_value
         ('800'
         ,'PER_PERIODS_OF_SERVICE'
         ,pps.attribute_category
         ,'ATTRIBUTE19'
         ,pps.attribute19)                        attribute19_meaning 
      ,pps.attribute20
      ,xxas_com_vset_pkg.decode_descr_vset_value
         ('800'
         ,'PER_PERIODS_OF_SERVICE'
         ,pps.attribute_category
         ,'ATTRIBUTE20'
         ,pps.attribute20)                        attribute20_meaning 
      ,pps.last_update_date
      ,pps.last_updated_by
      ,pps.last_update_login
      ,pps.created_by
      ,pps.creation_date
      ,pps.prior_employment_ssp_paid_to          
      ,pps.prior_employment_ssp_weeks
      ,pps.adjusted_svc_date
      ,pps.pds_information_category
      ,pps.pds_information1
      ,xxas_com_vset_pkg.decode_descr_vset_value
         ('800'
         ,'PER_PDS_DEVELOPER_DF'
         ,pps.pds_information_category
         ,'PDS_INFORMATION1'
         ,pps.pds_information1)                   pds_information1_meaning 
      ,pps.pds_information2
      ,xxas_com_vset_pkg.decode_descr_vset_value
         ('800'
         ,'PER_PDS_DEVELOPER_DF'
         ,pps.pds_information_category
         ,'PDS_INFORMATION2'
         ,pps.pds_information2)                   pds_information2_meaning
      ,pps.pds_information3
      ,xxas_com_vset_pkg.decode_descr_vset_value
         ('800'
         ,'PER_PDS_DEVELOPER_DF'
         ,pps.pds_information_category
         ,'PDS_INFORMATION3'
         ,pps.pds_information3)                   pds_information3_meaning
      ,pps.pds_information4
      ,xxas_com_vset_pkg.decode_descr_vset_value
         ('800'
         ,'PER_PDS_DEVELOPER_DF'
         ,pps.pds_information_category
         ,'PDS_INFORMATION4'
         ,pps.pds_information4)                   pds_information4_meaning
      ,pps.pds_information5
      ,xxas_com_vset_pkg.decode_descr_vset_value
         ('800'
         ,'PER_PDS_DEVELOPER_DF'
         ,pps.pds_information_category
         ,'PDS_INFORMATION5'
         ,pps.pds_information5)                   pds_information5_meaning
      ,pps.pds_information6
      ,xxas_com_vset_pkg.decode_descr_vset_value
         ('800'
         ,'PER_PDS_DEVELOPER_DF'
         ,pps.pds_information_category
         ,'PDS_INFORMATION6'
         ,pps.pds_information6)                   pds_information6_meaning
      ,pps.pds_information7
      ,xxas_com_vset_pkg.decode_descr_vset_value
         ('800'
         ,'PER_PDS_DEVELOPER_DF'
         ,pps.pds_information_category
         ,'PDS_INFORMATION7'
         ,pps.pds_information7)                   pds_information7_meaning
      ,pps.pds_information8
      ,xxas_com_vset_pkg.decode_descr_vset_value
         ('800'
         ,'PER_PDS_DEVELOPER_DF'
         ,pps.pds_information_category
         ,'PDS_INFORMATION8'
         ,pps.pds_information8)                   pds_information8_meaning
      ,pps.pds_information9
      ,xxas_com_vset_pkg.decode_descr_vset_value
         ('800'
         ,'PER_PDS_DEVELOPER_DF'
         ,pps.pds_information_category
         ,'PDS_INFORMATION9'
         ,pps.pds_information9)                   pds_information9_meaning
      ,pps.pds_information10
      ,xxas_com_vset_pkg.decode_descr_vset_value
         ('800'
         ,'PER_PDS_DEVELOPER_DF'
         ,pps.pds_information_category
         ,'PDS_INFORMATION10'
         ,pps.pds_information10)                  pds_information10_meaning
      ,pps.pds_information11
      ,xxas_com_vset_pkg.decode_descr_vset_value
         ('800'
         ,'PER_PDS_DEVELOPER_DF'
         ,pps.pds_information_category
         ,'PDS_INFORMATION11'
         ,pps.pds_information11)                  pds_information11_meaning
      ,pps.pds_information12
      ,xxas_com_vset_pkg.decode_descr_vset_value
         ('800'
         ,'PER_PDS_DEVELOPER_DF'
         ,pps.pds_information_category
         ,'PDS_INFORMATION12'
         ,pps.pds_information12)                  pds_information12_meaning
      ,pps.pds_information13
      ,xxas_com_vset_pkg.decode_descr_vset_value
         ('800'
         ,'PER_PDS_DEVELOPER_DF'
         ,pps.pds_information_category
         ,'PDS_INFORMATION13'
         ,pps.pds_information13)                  pds_information13_meaning
      ,pps.pds_information14
      ,xxas_com_vset_pkg.decode_descr_vset_value
         ('800'
         ,'PER_PDS_DEVELOPER_DF'
         ,pps.pds_information_category
         ,'PDS_INFORMATION14'
         ,pps.pds_information14)                  pds_information14_meaning
      ,pps.pds_information15
      ,xxas_com_vset_pkg.decode_descr_vset_value
         ('800'
         ,'PER_PDS_DEVELOPER_DF'
         ,pps.pds_information_category
         ,'PDS_INFORMATION15'
         ,pps.pds_information15)                  pds_information15_meaning
      ,pps.pds_information16
      ,xxas_com_vset_pkg.decode_descr_vset_value
         ('800'
         ,'PER_PDS_DEVELOPER_DF'
         ,pps.pds_information_category
         ,'PDS_INFORMATION16'
         ,pps.pds_information16)                  pds_information16_meaning
      ,pps.pds_information17
      ,xxas_com_vset_pkg.decode_descr_vset_value
         ('800'
         ,'PER_PDS_DEVELOPER_DF'
         ,pps.pds_information_category
         ,'PDS_INFORMATION17'
         ,pps.pds_information17)                  pds_information17_meaning
      ,pps.pds_information18
      ,xxas_com_vset_pkg.decode_descr_vset_value
         ('800'
         ,'PER_PDS_DEVELOPER_DF'
         ,pps.pds_information_category
         ,'PDS_INFORMATION18'
         ,pps.pds_information18)                  pds_information18_meaning
      ,pps.pds_information19
      ,xxas_com_vset_pkg.decode_descr_vset_value
         ('800'
         ,'PER_PDS_DEVELOPER_DF'
         ,pps.pds_information_category
         ,'PDS_INFORMATION19'
         ,pps.pds_information19)                  pds_information19_meaning
      ,pps.pds_information20
      ,xxas_com_vset_pkg.decode_descr_vset_value
         ('800'
         ,'PER_PDS_DEVELOPER_DF'
         ,pps.pds_information_category
         ,'PDS_INFORMATION20'
         ,pps.pds_information20)                  pds_information20_meaning
      ,pps.pds_information21
      ,xxas_com_vset_pkg.decode_descr_vset_value
         ('800'
         ,'PER_PDS_DEVELOPER_DF'
         ,pps.pds_information_category
         ,'PDS_INFORMATION21'
         ,pps.pds_information21)                  pds_information21_meaning
      ,pps.pds_information22
      ,xxas_com_vset_pkg.decode_descr_vset_value
         ('800'
         ,'PER_PDS_DEVELOPER_DF'
         ,pps.pds_information_category
         ,'PDS_INFORMATION22'
         ,pps.pds_information22)                  pds_information22_meaning
      ,pps.pds_information23
      ,xxas_com_vset_pkg.decode_descr_vset_value
         ('800'
         ,'PER_PDS_DEVELOPER_DF'
         ,pps.pds_information_category
         ,'PDS_INFORMATION23'
         ,pps.pds_information23)                  pds_information23_meaning
      ,pps.pds_information24
      ,xxas_com_vset_pkg.decode_descr_vset_value
         ('800'
         ,'PER_PDS_DEVELOPER_DF'
         ,pps.pds_information_category
         ,'PDS_INFORMATION24'
         ,pps.pds_information24)                  pds_information24_meaning
      ,pps.pds_information25
      ,xxas_com_vset_pkg.decode_descr_vset_value
         ('800'
         ,'PER_PDS_DEVELOPER_DF'
         ,pps.pds_information_category
         ,'PDS_INFORMATION25'
         ,pps.pds_information25)                  pds_information25_meaning
      ,pps.pds_information26
      ,xxas_com_vset_pkg.decode_descr_vset_value
         ('800'
         ,'PER_PDS_DEVELOPER_DF'
         ,pps.pds_information_category
         ,'PDS_INFORMATION26'
         ,pps.pds_information26)                  pds_information26_meaning
      ,pps.pds_information27
      ,xxas_com_vset_pkg.decode_descr_vset_value
         ('800'
         ,'PER_PDS_DEVELOPER_DF'
         ,pps.pds_information_category
         ,'PDS_INFORMATION27'
         ,pps.pds_information27)                  pds_information27_meaning
      ,pps.pds_information28
      ,xxas_com_vset_pkg.decode_descr_vset_value
         ('800'
         ,'PER_PDS_DEVELOPER_DF'
         ,pps.pds_information_category
         ,'PDS_INFORMATION28'
         ,pps.pds_information28)                  pds_information28_meaning
      ,pps.pds_information29
      ,xxas_com_vset_pkg.decode_descr_vset_value
         ('800'
         ,'PER_PDS_DEVELOPER_DF'
         ,pps.pds_information_category
         ,'PDS_INFORMATION29'
         ,pps.pds_information29)                  pds_information29_meaning
      ,pps.pds_information30
      ,xxas_com_vset_pkg.decode_descr_vset_value
         ('800'
         ,'PER_PDS_DEVELOPER_DF'
         ,pps.pds_information_category
         ,'PDS_INFORMATION30'
         ,pps.pds_information30)                  pds_information30_meaning
      ,xxas_util_pkg.long_to_varchar2
           ('PER_PERIODS_OF_SERVICE'
		   ,'COMMENTS' 
		   ,pps.rowid)                            comments_to_approver
      ,CASE WHEN pend.transaction_id IS NOT NULL
            THEN xxas_hr_api_transaction_pkg.number_value
                   (pend.person_id
                   ,pend.api_name
                   ,pend.transaction_step_id
                   ,'P_PERIOD_OF_SERVICE_ID')
       END                                        period_of_service_id_p
      ,CASE WHEN pend.transaction_id IS NOT NULL
            THEN xxas_hr_api_transaction_pkg.number_value
                   (pend.person_id
                   ,pend.api_name
                   ,pend.transaction_step_id
                   ,'P_OBJECT_VERSION_NUMBER')
       END                                        object_version_number_p
      ,CASE WHEN pend.transaction_id IS NOT NULL
            THEN xxas_hr_api_transaction_pkg.number_value
                   (pend.person_id
                   ,pend.api_name
                   ,pend.transaction_step_id
                   ,'P_BUSINESS_GROUP_ID')
       END                                        business_group_id_p
      ,CASE WHEN pend.transaction_id IS NOT NULL
            THEN pend.effective_date_option
       END                                        effective_date_option_p
      ,CASE WHEN pend.transaction_id IS NOT NULL
            THEN 'Y'
            ELSE 'N'
       END                                        pend_req
      ,CASE WHEN pend.transaction_id IS NOT NULL
            THEN xxas_hr_api_transaction_pkg.date_value
                   (pend.person_id
                   ,pend.api_name
                   ,pend.transaction_step_id
                   ,'P_ACTUAL_TERMINATION_DATE')
       END                                        last_day_of_work_p
      ,CASE WHEN pend.transaction_id IS NOT NULL
            THEN cast
                  (multiset
                    (SELECT xxas_direct_report
                              (pend_dr.effective_date
                              ,pend_dr.person_id
                              ,to_char(pend_dr.person_id) || '_' || to_char(pend_dr.assignment_id)
                              ,decode(fnd_profile.value('HR_LOCAL_OR_GLOBAL_NAME_FORMAT'),'G',pend_dr.global_name,pend_dr.local_name)
                              ,pend_dr.job_id
                              ,pend_dr.job_name
                              ,xxas_mss_util_pkg.person_full_name(pend.person_id)
                              ,pend.person_id
                              ,pend_dr.mgr_person_id
                              )
                     FROM    TABLE(xxas_mss_util_pkg.pend_req_direct_reports_data(pend.transaction_id)) pend_dr
                    )
                   AS xxas_direct_report_tbl)
            ELSE  cast
                  (multiset
                    (SELECT xxas_direct_report
                              (trunc(sysdate)
                              ,hier.person_id
                              ,to_char(hier.person_id) || '_' || to_char(hier.assignment_id)
                              ,decode(fnd_profile.value('HR_LOCAL_OR_GLOBAL_NAME_FORMAT'),'G',hier.global_name,hier.local_name)
                              ,hier.job_id
                              ,hier.job_name
                              ,xxas_mss_util_pkg.person_full_name(pps.person_id)
                              ,hier.mgr_person_id
                              ,null
                              )
                     FROM    TABLE(xxas_mss_util_pkg.direct_reportees_data(pps.person_id,paaf.assignment_id)) hier
                    )
                   AS xxas_direct_report_tbl)
       END                                        direct_reports
      ,CASE WHEN pend.transaction_id IS NOT NULL
            THEN cast
                  (multiset
                    (SELECT xxas_org_mgr
                              (pend_org.organization_name
                              ,pend_org.organization_id
                              ,pend_org.cur_manager
                              ,pend_org.manager_id
                              ,to_number(pend_org.new_manager_id)
                              ,pend_org.manager_id
                              ,pend_org.cur_start_date
                              ,pend_org.start_date
                              ,pend_org.cur_end_date
                              ,pend_org.end_date
                              ,pend_org.org_information_id
                              ,pend_org.reporting_name
                              ,pend_org.cur_start_date
                              ,pend_org.cur_end_date
                              ,pend_org.org_object_version_number
                              )
                     FROM    TABLE(xxas_mss_util_pkg.pend_req_org_mgr_data(pend.transaction_id)) pend_org
                    )
                   AS xxas_org_mgr_tbl) 
            ELSE cast
                  (multiset
                    (SELECT xxas_org_mgr
                              (org.organization_name
                              ,org.organization_id
                              ,org.cur_manager
                              ,(SELECT mgr.person_id
                                FROM   table(xxas_com_hier_pkg.mgrs_for_asg_tbl(paaf.assignment_id, trunc(sysdate))) mgr
                                WHERE  rownum = 1)
                              ,null
                              ,org.manager_id
                              ,trunc(sysdate)
                              ,null
                              ,trunc(sysdate)
                              ,null
                              ,org.org_information_id
                              ,org.reporting_name
                              ,org.cur_start_date
                              ,org.cur_end_date
                              ,org.org_object_version_number
                              )
                     FROM    TABLE(xxas_mss_util_pkg.org_info_data(pps.person_id)) org
                    )
                   AS xxas_org_mgr_tbl)
       END                                        org_information
      ,null                                       new_manager_dr_p
      ,CASE WHEN pend.transaction_id IS NOT NULL
            THEN xxas_hr_api_transaction_pkg.number_value
                   (pend.person_id
                   ,pend.api_name
                   ,pend.transaction_step_id
                   ,'P_PERSON_TYPE_ID')
       END                                        person_type_p
      ,CASE WHEN pend.transaction_id IS NOT NULL
            THEN xxas_hr_api_transaction_pkg.number_value
                   (pend.person_id
                   ,pend.api_name
                   ,pend.transaction_step_id
                   ,'P_ASSIGNMENT_STATUS_TYPE_ID')
       END                                        asg_status_p
      ,CASE WHEN pend.transaction_id IS NOT NULL
            THEN xxas_hr_api_transaction_pkg.varchar2_value
                   (pend.person_id
                   ,pend.api_name
                   ,pend.transaction_step_id
                   ,'P_REHIRE_RECOMMENDATION')
       END                                        rehire_flag_p
      ,CASE WHEN pend.transaction_id IS NOT NULL
            THEN xxas_hr_api_transaction_pkg.varchar2_value
                   (pend.person_id
                   ,pend.api_name
                   ,pend.transaction_step_id
                   ,'P_REHIRE_REASON')
       END                                        rehire_reason_p
      ,CASE WHEN pend.transaction_id IS NOT NULL
            THEN xxas_hr_api_transaction_pkg.date_value
                   (pend.person_id
                   ,pend.api_name
                   ,pend.transaction_step_id
                   ,'P_ACTUAL_TERMINATION_DATE')
       END                                        actual_termination_date_p
      ,CASE WHEN pend.transaction_id IS NOT NULL
            THEN xxas_hr_api_transaction_pkg.date_value
                   (pend.person_id
                   ,pend.api_name
                   ,pend.transaction_step_id
                   ,'P_FINAL_PROCESS_DATE')
       END                                        final_process_date_p
      ,CASE WHEN pend.transaction_id IS NOT NULL
            THEN xxas_hr_api_transaction_pkg.date_value
                   (pend.person_id
                   ,pend.api_name
                   ,pend.transaction_step_id
                   ,'P_LAST_STANDARD_PROCESS_DATE')
       END                                        last_standard_process_date_p
      ,CASE WHEN pend.transaction_id IS NOT NULL
            THEN xxas_hr_api_transaction_pkg.varchar2_value
                    (pend.person_id
                    ,pend.api_name
                    ,pend.transaction_step_id
                    ,'P_LEAVING_REASON')
       END                                        leav_reas_p
      ,CASE WHEN pend.transaction_id IS NOT NULL
            THEN xxas_hr_api_transaction_pkg.date_value
                   (pend.person_id
                   ,pend.api_name
                   ,pend.transaction_step_id
                   ,'P_NOTIFIED_TERMINATION_DATE')
       END                                        notification_date_p
      ,CASE WHEN pend.transaction_id IS NOT NULL
            THEN xxas_hr_api_transaction_pkg.date_value
                   (pend.person_id
                   ,pend.api_name
                   ,pend.transaction_step_id
                   ,'P_PROJECTED_TERMINATION_DATE')
       END                                        projected_date_p
      ,CASE WHEN pend.transaction_id IS NOT NULL
            THEN xxas_hr_api_transaction_pkg.varchar2_value
                   (pend.person_id
                   ,pend.api_name
                   ,pend.transaction_step_id
                   ,'P_ATTRIBUTE_CATEGORY')
       END                                        attribute_category_p
      ,CASE WHEN pend.transaction_id IS NOT NULL
            THEN xxas_hr_api_transaction_pkg.varchar2_value
                   (pend.person_id
                   ,pend.api_name
                   ,pend.transaction_step_id
                   ,'P_ATTRIBUTE1')
       END                                        attribute1_p
      ,CASE WHEN pend.transaction_id IS NOT NULL
            THEN xxas_com_vset_pkg.decode_descr_vset_value
                   ('800'
                   ,'PER_PERIODS_OF_SERVICE'
                   ,xxas_hr_api_transaction_pkg.varchar2_value
                      (pend.person_id
                      ,pend.api_name
                      ,pend.transaction_step_id
                      ,'P_ATTRIBUTE_CATEGORY')
                   ,'ATTRIBUTE1'
                   ,xxas_hr_api_transaction_pkg.varchar2_value
                      (pend.person_id
                      ,pend.api_name
                      ,pend.transaction_step_id
                      ,'P_ATTRIBUTE1'))
       END                                        attribute1_meaning_p
      ,CASE WHEN pend.transaction_id IS NOT NULL
            THEN xxas_hr_api_transaction_pkg.varchar2_value
                   (pend.person_id
                   ,pend.api_name
                   ,pend.transaction_step_id
                   ,'P_ATTRIBUTE2')
       END                                        attribute2_p
      ,CASE WHEN pend.transaction_id IS NOT NULL
            THEN xxas_com_vset_pkg.decode_descr_vset_value
                   ('800'
                   ,'PER_PERIODS_OF_SERVICE'
                   ,xxas_hr_api_transaction_pkg.varchar2_value
                      (pend.person_id
                      ,pend.api_name
                      ,pend.transaction_step_id
                      ,'P_ATTRIBUTE_CATEGORY')
                   ,'ATTRIBUTE2'
                   ,xxas_hr_api_transaction_pkg.varchar2_value
                      (pend.person_id
                      ,pend.api_name
                      ,pend.transaction_step_id
                      ,'P_ATTRIBUTE2'))
       END                                        attribute2_meaning_p
      ,CASE WHEN pend.transaction_id IS NOT NULL
            THEN xxas_hr_api_transaction_pkg.varchar2_value
                   (pend.person_id
                   ,pend.api_name
                   ,pend.transaction_step_id
                   ,'P_ATTRIBUTE3')
       END                                        attribute3_p
      ,CASE WHEN pend.transaction_id IS NOT NULL
            THEN xxas_com_vset_pkg.decode_descr_vset_value
                   ('800'
                   ,'PER_PERIODS_OF_SERVICE'
                   ,xxas_hr_api_transaction_pkg.varchar2_value
                      (pend.person_id
                      ,pend.api_name
                      ,pend.transaction_step_id
                      ,'P_ATTRIBUTE_CATEGORY')
                   ,'ATTRIBUTE3'
                   ,xxas_hr_api_transaction_pkg.varchar2_value
                      (pend.person_id
                      ,pend.api_name
                      ,pend.transaction_step_id
                      ,'P_ATTRIBUTE3'))
       END                                        attribute3_meaning_p
      ,CASE WHEN pend.transaction_id IS NOT NULL
            THEN xxas_hr_api_transaction_pkg.varchar2_value
                   (pend.person_id
                   ,pend.api_name
                   ,pend.transaction_step_id
                   ,'P_ATTRIBUTE4')
       END                                        attribute4_p
      ,CASE WHEN pend.transaction_id IS NOT NULL
            THEN xxas_com_vset_pkg.decode_descr_vset_value
                   ('800'
                   ,'PER_PERIODS_OF_SERVICE'
                   ,xxas_hr_api_transaction_pkg.varchar2_value
                      (pend.person_id
                      ,pend.api_name
                      ,pend.transaction_step_id
                      ,'P_ATTRIBUTE_CATEGORY')
                   ,'ATTRIBUTE4'
                   ,xxas_hr_api_transaction_pkg.varchar2_value
                      (pend.person_id
                      ,pend.api_name
                      ,pend.transaction_step_id
                      ,'P_ATTRIBUTE4'))
       END                                        attribute4_meaning_p
      ,CASE WHEN pend.transaction_id IS NOT NULL
            THEN xxas_hr_api_transaction_pkg.varchar2_value
                   (pend.person_id
                   ,pend.api_name
                   ,pend.transaction_step_id
                   ,'P_ATTRIBUTE5')
       END                                        attribute5_p
      ,CASE WHEN pend.transaction_id IS NOT NULL
            THEN xxas_com_vset_pkg.decode_descr_vset_value
                   ('800'
                   ,'PER_PERIODS_OF_SERVICE'
                   ,xxas_hr_api_transaction_pkg.varchar2_value
                      (pend.person_id
                      ,pend.api_name
                      ,pend.transaction_step_id
                      ,'P_ATTRIBUTE_CATEGORY')
                   ,'ATTRIBUTE5'
                   ,xxas_hr_api_transaction_pkg.varchar2_value
                      (pend.person_id
                      ,pend.api_name
                      ,pend.transaction_step_id
                      ,'P_ATTRIBUTE5'))
       END                                        attribute5_meaning_p
      ,CASE WHEN pend.transaction_id IS NOT NULL
            THEN xxas_hr_api_transaction_pkg.varchar2_value
                   (pend.person_id
                   ,pend.api_name
                   ,pend.transaction_step_id
                   ,'P_ATTRIBUTE6')
       END                                        attribute6_p
      ,CASE WHEN pend.transaction_id IS NOT NULL
            THEN xxas_com_vset_pkg.decode_descr_vset_value
                   ('800'
                   ,'PER_PERIODS_OF_SERVICE'
                   ,xxas_hr_api_transaction_pkg.varchar2_value
                      (pend.person_id
                      ,pend.api_name
                      ,pend.transaction_step_id
                      ,'P_ATTRIBUTE_CATEGORY')
                   ,'ATTRIBUTE6'
                   ,xxas_hr_api_transaction_pkg.varchar2_value
                      (pend.person_id
                      ,pend.api_name
                      ,pend.transaction_step_id
                      ,'P_ATTRIBUTE6'))
       END                                        attribute6_meaning_p
      ,CASE WHEN pend.transaction_id IS NOT NULL
            THEN xxas_hr_api_transaction_pkg.varchar2_value
                   (pend.person_id
                   ,pend.api_name
                   ,pend.transaction_step_id
                   ,'P_ATTRIBUTE7')
       END                                        attribute7_p
      ,CASE WHEN pend.transaction_id IS NOT NULL
            THEN xxas_com_vset_pkg.decode_descr_vset_value
                   ('800'
                   ,'PER_PERIODS_OF_SERVICE'
                   ,xxas_hr_api_transaction_pkg.varchar2_value
                      (pend.person_id
                      ,pend.api_name
                      ,pend.transaction_step_id
                      ,'P_ATTRIBUTE_CATEGORY')
                   ,'ATTRIBUTE7'
                   ,xxas_hr_api_transaction_pkg.varchar2_value
                      (pend.person_id
                      ,pend.api_name
                      ,pend.transaction_step_id
                      ,'P_ATTRIBUTE7'))
       END                                        attribute7_meaning_p
      ,CASE WHEN pend.transaction_id IS NOT NULL
            THEN xxas_hr_api_transaction_pkg.varchar2_value
                   (pend.person_id
                   ,pend.api_name
                   ,pend.transaction_step_id
                   ,'P_ATTRIBUTE8')
       END                                        attribute8_p
      ,CASE WHEN pend.transaction_id IS NOT NULL
            THEN xxas_com_vset_pkg.decode_descr_vset_value
                   ('800'
                   ,'PER_PERIODS_OF_SERVICE'
                   ,xxas_hr_api_transaction_pkg.varchar2_value
                      (pend.person_id
                      ,pend.api_name
                      ,pend.transaction_step_id
                      ,'P_ATTRIBUTE_CATEGORY')
                   ,'ATTRIBUTE8'
                   ,xxas_hr_api_transaction_pkg.varchar2_value
                      (pend.person_id
                      ,pend.api_name
                      ,pend.transaction_step_id
                      ,'P_ATTRIBUTE8'))
       END                                        attribute8_meaning_p
      ,CASE WHEN pend.transaction_id IS NOT NULL
            THEN xxas_hr_api_transaction_pkg.varchar2_value
                   (pend.person_id
                   ,pend.api_name
                   ,pend.transaction_step_id
                   ,'P_ATTRIBUTE9')
       END                                        attribute9_p
      ,CASE WHEN pend.transaction_id IS NOT NULL
            THEN xxas_com_vset_pkg.decode_descr_vset_value
                   ('800'
                   ,'PER_PERIODS_OF_SERVICE'
                   ,xxas_hr_api_transaction_pkg.varchar2_value
                      (pend.person_id
                      ,pend.api_name
                      ,pend.transaction_step_id
                      ,'P_ATTRIBUTE_CATEGORY')
                   ,'ATTRIBUTE9'
                   ,xxas_hr_api_transaction_pkg.varchar2_value
                      (pend.person_id
                      ,pend.api_name
                      ,pend.transaction_step_id
                      ,'P_ATTRIBUTE9'))
       END                                        attribute9_meaning_p
      ,CASE WHEN pend.transaction_id IS NOT NULL
            THEN xxas_hr_api_transaction_pkg.varchar2_value
                   (pend.person_id
                   ,pend.api_name
                   ,pend.transaction_step_id
                   ,'P_ATTRIBUTE10')
       END                                        attribute10_p
      ,CASE WHEN pend.transaction_id IS NOT NULL
            THEN xxas_com_vset_pkg.decode_descr_vset_value
                   ('800'
                   ,'PER_PERIODS_OF_SERVICE'
                   ,xxas_hr_api_transaction_pkg.varchar2_value
                      (pend.person_id
                      ,pend.api_name
                      ,pend.transaction_step_id
                      ,'P_ATTRIBUTE_CATEGORY')
                   ,'ATTRIBUTE10'
                   ,xxas_hr_api_transaction_pkg.varchar2_value
                      (pend.person_id
                      ,pend.api_name
                      ,pend.transaction_step_id
                      ,'P_ATTRIBUTE10'))
       END                                        attribute10_meaning_p
      ,CASE WHEN pend.transaction_id IS NOT NULL
            THEN xxas_hr_api_transaction_pkg.varchar2_value
                   (pend.person_id
                   ,pend.api_name
                   ,pend.transaction_step_id
                   ,'P_ATTRIBUTE11')
       END                                        attribute11_p
      ,CASE WHEN pend.transaction_id IS NOT NULL
            THEN xxas_com_vset_pkg.decode_descr_vset_value
                   ('800'
                   ,'PER_PERIODS_OF_SERVICE'
                   ,xxas_hr_api_transaction_pkg.varchar2_value
                      (pend.person_id
                      ,pend.api_name
                      ,pend.transaction_step_id
                      ,'P_ATTRIBUTE_CATEGORY')
                   ,'ATTRIBUTE11'
                   ,xxas_hr_api_transaction_pkg.varchar2_value
                      (pend.person_id
                      ,pend.api_name
                      ,pend.transaction_step_id
                      ,'P_ATTRIBUTE11'))
       END                                        attribute11_meaning_p
      ,CASE WHEN pend.transaction_id IS NOT NULL
            THEN xxas_hr_api_transaction_pkg.varchar2_value
                   (pend.person_id
                   ,pend.api_name
                   ,pend.transaction_step_id
                   ,'P_ATTRIBUTE12')
       END                                        attribute12_p
      ,CASE WHEN pend.transaction_id IS NOT NULL
            THEN xxas_com_vset_pkg.decode_descr_vset_value
                   ('800'
                   ,'PER_PERIODS_OF_SERVICE'
                   ,xxas_hr_api_transaction_pkg.varchar2_value
                      (pend.person_id
                      ,pend.api_name
                      ,pend.transaction_step_id
                      ,'P_ATTRIBUTE_CATEGORY')
                   ,'ATTRIBUTE12'
                   ,xxas_hr_api_transaction_pkg.varchar2_value
                      (pend.person_id
                      ,pend.api_name
                      ,pend.transaction_step_id
                      ,'P_ATTRIBUTE12'))
       END                                        attribute12_meaning_p
      ,CASE WHEN pend.transaction_id IS NOT NULL
            THEN xxas_hr_api_transaction_pkg.varchar2_value
                   (pend.person_id
                   ,pend.api_name
                   ,pend.transaction_step_id
                   ,'P_ATTRIBUTE13')
       END                                        attribute13_p
      ,CASE WHEN pend.transaction_id IS NOT NULL
            THEN xxas_com_vset_pkg.decode_descr_vset_value
                   ('800'
                   ,'PER_PERIODS_OF_SERVICE'
                   ,xxas_hr_api_transaction_pkg.varchar2_value
                      (pend.person_id
                      ,pend.api_name
                      ,pend.transaction_step_id
                      ,'P_ATTRIBUTE_CATEGORY')
                   ,'ATTRIBUTE13'
                   ,xxas_hr_api_transaction_pkg.varchar2_value
                      (pend.person_id
                      ,pend.api_name
                      ,pend.transaction_step_id
                      ,'P_ATTRIBUTE13'))
       END                                        attribute13_meaning_p
      ,CASE WHEN pend.transaction_id IS NOT NULL
            THEN xxas_hr_api_transaction_pkg.varchar2_value
                   (pend.person_id
                   ,pend.api_name
                   ,pend.transaction_step_id
                   ,'P_ATTRIBUTE14')
       END                                        attribute14_p
      ,CASE WHEN pend.transaction_id IS NOT NULL
            THEN xxas_com_vset_pkg.decode_descr_vset_value
                   ('800'
                   ,'PER_PERIODS_OF_SERVICE'
                   ,xxas_hr_api_transaction_pkg.varchar2_value
                      (pend.person_id
                      ,pend.api_name
                      ,pend.transaction_step_id
                      ,'P_ATTRIBUTE_CATEGORY')
                   ,'ATTRIBUTE14'
                   ,xxas_hr_api_transaction_pkg.varchar2_value
                      (pend.person_id
                      ,pend.api_name
                      ,pend.transaction_step_id
                      ,'P_ATTRIBUTE14'))
       END                                        attribute14_meaning_p
      ,CASE WHEN pend.transaction_id IS NOT NULL
            THEN xxas_hr_api_transaction_pkg.varchar2_value
                   (pend.person_id
                   ,pend.api_name
                   ,pend.transaction_step_id
                   ,'P_ATTRIBUTE15')
       END                                        attribute15_p
      ,CASE WHEN pend.transaction_id IS NOT NULL
            THEN xxas_com_vset_pkg.decode_descr_vset_value
                   ('800'
                   ,'PER_PERIODS_OF_SERVICE'
                   ,xxas_hr_api_transaction_pkg.varchar2_value
                      (pend.person_id
                      ,pend.api_name
                      ,pend.transaction_step_id
                      ,'P_ATTRIBUTE_CATEGORY')
                   ,'ATTRIBUTE15'
                   ,xxas_hr_api_transaction_pkg.varchar2_value
                      (pend.person_id
                      ,pend.api_name
                      ,pend.transaction_step_id
                      ,'P_ATTRIBUTE15'))
       END                                        attribute15_meaning_p
      ,CASE WHEN pend.transaction_id IS NOT NULL
            THEN xxas_hr_api_transaction_pkg.varchar2_value
                   (pend.person_id
                   ,pend.api_name
                   ,pend.transaction_step_id
                   ,'P_ATTRIBUTE16')
       END                                        attribute16_p
      ,CASE WHEN pend.transaction_id IS NOT NULL
            THEN xxas_com_vset_pkg.decode_descr_vset_value
                   ('800'
                   ,'PER_PERIODS_OF_SERVICE'
                   ,xxas_hr_api_transaction_pkg.varchar2_value
                      (pend.person_id
                      ,pend.api_name
                      ,pend.transaction_step_id
                      ,'P_ATTRIBUTE_CATEGORY')
                   ,'ATTRIBUTE16'
                   ,xxas_hr_api_transaction_pkg.varchar2_value
                      (pend.person_id
                      ,pend.api_name
                      ,pend.transaction_step_id
                      ,'P_ATTRIBUTE16'))
       END                                        attribute16_meaning_p
      ,CASE WHEN pend.transaction_id IS NOT NULL
            THEN xxas_hr_api_transaction_pkg.varchar2_value
                   (pend.person_id
                   ,pend.api_name
                   ,pend.transaction_step_id
                   ,'P_ATTRIBUTE17')
       END                                        attribute17_p
      ,CASE WHEN pend.transaction_id IS NOT NULL
            THEN xxas_com_vset_pkg.decode_descr_vset_value
                   ('800'
                   ,'PER_PERIODS_OF_SERVICE'
                   ,xxas_hr_api_transaction_pkg.varchar2_value
                      (pend.person_id
                      ,pend.api_name
                      ,pend.transaction_step_id
                      ,'P_ATTRIBUTE_CATEGORY')
                   ,'ATTRIBUTE17'
                   ,xxas_hr_api_transaction_pkg.varchar2_value
                      (pend.person_id
                      ,pend.api_name
                      ,pend.transaction_step_id
                      ,'P_ATTRIBUTE17'))
       END                                        attribute17_meaning_p
      ,CASE WHEN pend.transaction_id IS NOT NULL
            THEN xxas_hr_api_transaction_pkg.varchar2_value
                   (pend.person_id
                   ,pend.api_name
                   ,pend.transaction_step_id
                   ,'P_ATTRIBUTE18')
       END                                        attribute18_p
      ,CASE WHEN pend.transaction_id IS NOT NULL
            THEN xxas_com_vset_pkg.decode_descr_vset_value
                   ('800'
                   ,'PER_PERIODS_OF_SERVICE'
                   ,xxas_hr_api_transaction_pkg.varchar2_value
                      (pend.person_id
                      ,pend.api_name
                      ,pend.transaction_step_id
                      ,'P_ATTRIBUTE_CATEGORY')
                   ,'ATTRIBUTE18'
                   ,xxas_hr_api_transaction_pkg.varchar2_value
                      (pend.person_id
                      ,pend.api_name
                      ,pend.transaction_step_id
                      ,'P_ATTRIBUTE18'))
       END                                        attribute18_meaning_p
      ,CASE WHEN pend.transaction_id IS NOT NULL
            THEN xxas_hr_api_transaction_pkg.varchar2_value
                   (pend.person_id
                   ,pend.api_name
                   ,pend.transaction_step_id
                   ,'P_ATTRIBUTE19')
       END                                        attribute19_p
      ,CASE WHEN pend.transaction_id IS NOT NULL
            THEN xxas_com_vset_pkg.decode_descr_vset_value
                   ('800'
                   ,'PER_PERIODS_OF_SERVICE'
                   ,xxas_hr_api_transaction_pkg.varchar2_value
                      (pend.person_id
                      ,pend.api_name
                      ,pend.transaction_step_id
                      ,'P_ATTRIBUTE_CATEGORY')
                   ,'ATTRIBUTE19'
                   ,xxas_hr_api_transaction_pkg.varchar2_value
                      (pend.person_id
                      ,pend.api_name
                      ,pend.transaction_step_id
                      ,'P_ATTRIBUTE19'))
       END                                        attribute19_meaning_p
      ,CASE WHEN pend.transaction_id IS NOT NULL
            THEN xxas_hr_api_transaction_pkg.varchar2_value
                   (pend.person_id
                   ,pend.api_name
                   ,pend.transaction_step_id
                   ,'P_ATTRIBUTE20')
       END                                        attribute20_p
      ,CASE WHEN pend.transaction_id IS NOT NULL
            THEN xxas_com_vset_pkg.decode_descr_vset_value
                   ('800'
                   ,'PER_PERIODS_OF_SERVICE'
                   ,xxas_hr_api_transaction_pkg.varchar2_value
                      (pend.person_id
                      ,pend.api_name
                      ,pend.transaction_step_id
                      ,'P_ATTRIBUTE_CATEGORY')
                   ,'ATTRIBUTE20'
                   ,xxas_hr_api_transaction_pkg.varchar2_value
                      (pend.person_id
                      ,pend.api_name
                      ,pend.transaction_step_id
                      ,'P_ATTRIBUTE20'))
       END                                        attribute20_meaning_p
      ,CASE WHEN pend.transaction_id IS NOT NULL
            THEN xxas_hr_api_transaction_pkg.varchar2_value
                   (pend.person_id
                   ,pend.api_name
                   ,pend.transaction_step_id
                   ,'P_PDS_INFORMATION_CATEGORY')
       END                                        pds_information_category_p
      ,CASE WHEN pend.transaction_id IS NOT NULL
            THEN xxas_hr_api_transaction_pkg.varchar2_value
                   (pend.person_id
                   ,pend.api_name
                   ,pend.transaction_step_id
                   ,'P_PDS_INFORMATION1')
       END                                        pds_information1_p
      ,CASE WHEN pend.transaction_id IS NOT NULL
            THEN xxas_com_vset_pkg.decode_descr_vset_value
                   ('800'
                   ,'PER_PDS_DEVELOPER_DF'
                   ,xxas_hr_api_transaction_pkg.varchar2_value
                      (pend.person_id
                      ,pend.api_name
                      ,pend.transaction_step_id
                      ,'P_PDS_INFORMATION_CATEGORY')
                   ,'PDS_INFORMATION1'
                   ,xxas_hr_api_transaction_pkg.varchar2_value
                      (pend.person_id
                      ,pend.api_name
                      ,pend.transaction_step_id
                      ,'P_PDS_INFORMATION1'))
       END                                        pds_information1_meaning_p
      ,CASE WHEN pend.transaction_id IS NOT NULL
            THEN xxas_hr_api_transaction_pkg.varchar2_value
                   (pend.person_id
                   ,pend.api_name
                   ,pend.transaction_step_id
                   ,'P_PDS_INFORMATION2')
       END                                        pds_information2_p
      ,CASE WHEN pend.transaction_id IS NOT NULL
            THEN xxas_com_vset_pkg.decode_descr_vset_value
                   ('800'
                   ,'PER_PDS_DEVELOPER_DF'
                   ,xxas_hr_api_transaction_pkg.varchar2_value
                      (pend.person_id
                      ,pend.api_name
                      ,pend.transaction_step_id
                      ,'P_PDS_INFORMATION_CATEGORY')
                   ,'PDS_INFORMATION2'
                   ,xxas_hr_api_transaction_pkg.varchar2_value
                      (pend.person_id
                      ,pend.api_name
                      ,pend.transaction_step_id
                      ,'P_PDS_INFORMATION2'))
       END                                        pds_information2_meaning_p
      ,CASE WHEN pend.transaction_id IS NOT NULL
            THEN xxas_hr_api_transaction_pkg.varchar2_value
                   (pend.person_id
                   ,pend.api_name
                   ,pend.transaction_step_id
                   ,'P_PDS_INFORMATION3')
       END                                        pds_information3_p
      ,CASE WHEN pend.transaction_id IS NOT NULL
            THEN xxas_com_vset_pkg.decode_descr_vset_value
                   ('800'
                   ,'PER_PDS_DEVELOPER_DF'
                   ,xxas_hr_api_transaction_pkg.varchar2_value
                      (pend.person_id
                      ,pend.api_name
                      ,pend.transaction_step_id
                      ,'P_PDS_INFORMATION_CATEGORY')
                   ,'PDS_INFORMATION3'
                   ,xxas_hr_api_transaction_pkg.varchar2_value
                      (pend.person_id
                      ,pend.api_name
                      ,pend.transaction_step_id
                      ,'P_PDS_INFORMATION3'))
       END                                        pds_information3_meaning_p
      ,CASE WHEN pend.transaction_id IS NOT NULL
            THEN xxas_hr_api_transaction_pkg.varchar2_value
                   (pend.person_id
                   ,pend.api_name
                   ,pend.transaction_step_id
                   ,'P_PDS_INFORMATION4')
       END                                        pds_information4_p
      ,CASE WHEN pend.transaction_id IS NOT NULL
            THEN xxas_com_vset_pkg.decode_descr_vset_value
                   ('800'
                   ,'PER_PDS_DEVELOPER_DF'
                   ,xxas_hr_api_transaction_pkg.varchar2_value
                      (pend.person_id
                      ,pend.api_name
                      ,pend.transaction_step_id
                      ,'P_PDS_INFORMATION_CATEGORY')
                   ,'PDS_INFORMATION4'
                   ,xxas_hr_api_transaction_pkg.varchar2_value
                      (pend.person_id
                      ,pend.api_name
                      ,pend.transaction_step_id
                      ,'P_PDS_INFORMATION4'))
       END                                        pds_information4_meaning_p
      ,CASE WHEN pend.transaction_id IS NOT NULL
            THEN xxas_hr_api_transaction_pkg.varchar2_value
                   (pend.person_id
                   ,pend.api_name
                   ,pend.transaction_step_id
                   ,'P_PDS_INFORMATION5')
       END                                        pds_information5_p
      ,CASE WHEN pend.transaction_id IS NOT NULL
            THEN xxas_com_vset_pkg.decode_descr_vset_value
                   ('800'
                   ,'PER_PDS_DEVELOPER_DF'
                   ,xxas_hr_api_transaction_pkg.varchar2_value
                      (pend.person_id
                      ,pend.api_name
                      ,pend.transaction_step_id
                      ,'P_PDS_INFORMATION_CATEGORY')
                   ,'PDS_INFORMATION5'
                   ,xxas_hr_api_transaction_pkg.varchar2_value
                      (pend.person_id
                      ,pend.api_name
                      ,pend.transaction_step_id
                      ,'P_PDS_INFORMATION5'))
       END                                        pds_information5_meaning_p
      ,CASE WHEN pend.transaction_id IS NOT NULL
            THEN xxas_hr_api_transaction_pkg.varchar2_value
                   (pend.person_id
                   ,pend.api_name
                   ,pend.transaction_step_id
                   ,'P_PDS_INFORMATION6')
       END                                        pds_information6_p
      ,CASE WHEN pend.transaction_id IS NOT NULL
            THEN xxas_com_vset_pkg.decode_descr_vset_value
                   ('800'
                   ,'PER_PDS_DEVELOPER_DF'
                   ,xxas_hr_api_transaction_pkg.varchar2_value
                      (pend.person_id
                      ,pend.api_name
                      ,pend.transaction_step_id
                      ,'P_PDS_INFORMATION_CATEGORY')
                   ,'PDS_INFORMATION6'
                   ,xxas_hr_api_transaction_pkg.varchar2_value
                      (pend.person_id
                      ,pend.api_name
                      ,pend.transaction_step_id
                      ,'P_PDS_INFORMATION6'))
       END                                        pds_information6_meaning_p
      ,CASE WHEN pend.transaction_id IS NOT NULL
            THEN xxas_hr_api_transaction_pkg.varchar2_value
                   (pend.person_id
                   ,pend.api_name
                   ,pend.transaction_step_id
                   ,'P_PDS_INFORMATION7')
       END                                        pds_information7_p
      ,CASE WHEN pend.transaction_id IS NOT NULL
            THEN xxas_com_vset_pkg.decode_descr_vset_value
                   ('800'
                   ,'PER_PDS_DEVELOPER_DF'
                   ,xxas_hr_api_transaction_pkg.varchar2_value
                      (pend.person_id
                      ,pend.api_name
                      ,pend.transaction_step_id
                      ,'P_PDS_INFORMATION_CATEGORY')
                   ,'PDS_INFORMATION7'
                   ,xxas_hr_api_transaction_pkg.varchar2_value
                      (pend.person_id
                      ,pend.api_name
                      ,pend.transaction_step_id
                      ,'P_PDS_INFORMATION7'))
       END                                        pds_information7_meaning_p
      ,CASE WHEN pend.transaction_id IS NOT NULL
            THEN xxas_hr_api_transaction_pkg.varchar2_value
                   (pend.person_id
                   ,pend.api_name
                   ,pend.transaction_step_id
                   ,'P_PDS_INFORMATION8')
       END                                        pds_information8_p
      ,CASE WHEN pend.transaction_id IS NOT NULL
            THEN xxas_com_vset_pkg.decode_descr_vset_value
                   ('800'
                   ,'PER_PDS_DEVELOPER_DF'
                   ,xxas_hr_api_transaction_pkg.varchar2_value
                      (pend.person_id
                      ,pend.api_name
                      ,pend.transaction_step_id
                      ,'P_PDS_INFORMATION_CATEGORY')
                   ,'PDS_INFORMATION8'
                   ,xxas_hr_api_transaction_pkg.varchar2_value
                      (pend.person_id
                      ,pend.api_name
                      ,pend.transaction_step_id
                      ,'P_PDS_INFORMATION8'))
       END                                        pds_information8_meaning_p
      ,CASE WHEN pend.transaction_id IS NOT NULL
            THEN xxas_hr_api_transaction_pkg.varchar2_value
                   (pend.person_id
                   ,pend.api_name
                   ,pend.transaction_step_id
                   ,'P_PDS_INFORMATION9')
       END                                        pds_information9_p
      ,CASE WHEN pend.transaction_id IS NOT NULL
            THEN xxas_com_vset_pkg.decode_descr_vset_value
                   ('800'
                   ,'PER_PDS_DEVELOPER_DF'
                   ,xxas_hr_api_transaction_pkg.varchar2_value
                      (pend.person_id
                      ,pend.api_name
                      ,pend.transaction_step_id
                      ,'P_PDS_INFORMATION_CATEGORY')
                   ,'PDS_INFORMATION9'
                   ,xxas_hr_api_transaction_pkg.varchar2_value
                      (pend.person_id
                      ,pend.api_name
                      ,pend.transaction_step_id
                      ,'P_PDS_INFORMATION9'))
       END                                        pds_information9_meaning_p
      ,CASE WHEN pend.transaction_id IS NOT NULL
            THEN xxas_hr_api_transaction_pkg.varchar2_value
                   (pend.person_id
                   ,pend.api_name
                   ,pend.transaction_step_id
                   ,'P_PDS_INFORMATION10')
       END                                        pds_information10_p
      ,CASE WHEN pend.transaction_id IS NOT NULL
            THEN xxas_com_vset_pkg.decode_descr_vset_value
                   ('800'
                   ,'PER_PDS_DEVELOPER_DF'
                   ,xxas_hr_api_transaction_pkg.varchar2_value
                      (pend.person_id
                      ,pend.api_name
                      ,pend.transaction_step_id
                      ,'P_PDS_INFORMATION_CATEGORY')
                   ,'PDS_INFORMATION10'
                   ,xxas_hr_api_transaction_pkg.varchar2_value
                      (pend.person_id
                      ,pend.api_name
                      ,pend.transaction_step_id
                      ,'P_PDS_INFORMATION10'))
       END                                        pds_information10_meaning_p
      ,CASE WHEN pend.transaction_id IS NOT NULL
            THEN xxas_hr_api_transaction_pkg.varchar2_value
                   (pend.person_id
                   ,pend.api_name
                   ,pend.transaction_step_id
                   ,'P_PDS_INFORMATION11')
       END                                        pds_information11_p
      ,CASE WHEN pend.transaction_id IS NOT NULL
            THEN xxas_com_vset_pkg.decode_descr_vset_value
                   ('800'
                   ,'PER_PDS_DEVELOPER_DF'
                   ,xxas_hr_api_transaction_pkg.varchar2_value
                      (pend.person_id
                      ,pend.api_name
                      ,pend.transaction_step_id
                      ,'P_PDS_INFORMATION_CATEGORY')
                   ,'PDS_INFORMATION11'
                   ,xxas_hr_api_transaction_pkg.varchar2_value
                      (pend.person_id
                      ,pend.api_name
                      ,pend.transaction_step_id
                      ,'P_PDS_INFORMATION11'))
       END                                        pds_information11_meaning_p
      ,CASE WHEN pend.transaction_id IS NOT NULL
            THEN xxas_hr_api_transaction_pkg.varchar2_value
                   (pend.person_id
                   ,pend.api_name
                   ,pend.transaction_step_id
                   ,'P_PDS_INFORMATION12')
       END                                        pds_information12_p
      ,CASE WHEN pend.transaction_id IS NOT NULL
            THEN xxas_com_vset_pkg.decode_descr_vset_value
                   ('800'
                   ,'PER_PDS_DEVELOPER_DF'
                   ,xxas_hr_api_transaction_pkg.varchar2_value
                      (pend.person_id
                      ,pend.api_name
                      ,pend.transaction_step_id
                      ,'P_PDS_INFORMATION_CATEGORY')
                   ,'PDS_INFORMATION12'
                   ,xxas_hr_api_transaction_pkg.varchar2_value
                      (pend.person_id
                      ,pend.api_name
                      ,pend.transaction_step_id
                      ,'P_PDS_INFORMATION12'))
       END                                        pds_information12_meaning_p
      ,CASE WHEN pend.transaction_id IS NOT NULL
            THEN xxas_hr_api_transaction_pkg.varchar2_value
                   (pend.person_id
                   ,pend.api_name
                   ,pend.transaction_step_id
                   ,'P_PDS_INFORMATION13')
       END                                        pds_information13_p
      ,CASE WHEN pend.transaction_id IS NOT NULL
            THEN xxas_com_vset_pkg.decode_descr_vset_value
                   ('800'
                   ,'PER_PDS_DEVELOPER_DF'
                   ,xxas_hr_api_transaction_pkg.varchar2_value
                      (pend.person_id
                      ,pend.api_name
                      ,pend.transaction_step_id
                      ,'P_PDS_INFORMATION_CATEGORY')
                   ,'PDS_INFORMATION13'
                   ,xxas_hr_api_transaction_pkg.varchar2_value
                      (pend.person_id
                      ,pend.api_name
                      ,pend.transaction_step_id
                      ,'P_PDS_INFORMATION13'))
       END                                        pds_information13_meaning_p
      ,CASE WHEN pend.transaction_id IS NOT NULL
            THEN xxas_hr_api_transaction_pkg.varchar2_value
                   (pend.person_id
                   ,pend.api_name
                   ,pend.transaction_step_id
                   ,'P_PDS_INFORMATION14')
       END                                        pds_information14_p
      ,CASE WHEN pend.transaction_id IS NOT NULL
            THEN xxas_com_vset_pkg.decode_descr_vset_value
                   ('800'
                   ,'PER_PDS_DEVELOPER_DF'
                   ,xxas_hr_api_transaction_pkg.varchar2_value
                      (pend.person_id
                      ,pend.api_name
                      ,pend.transaction_step_id
                      ,'P_PDS_INFORMATION_CATEGORY')
                   ,'PDS_INFORMATION14'
                   ,xxas_hr_api_transaction_pkg.varchar2_value
                      (pend.person_id
                      ,pend.api_name
                      ,pend.transaction_step_id
                      ,'P_PDS_INFORMATION14'))
       END                                        pds_information14_meaning_p
      ,CASE WHEN pend.transaction_id IS NOT NULL
            THEN xxas_hr_api_transaction_pkg.varchar2_value
                   (pend.person_id
                   ,pend.api_name
                   ,pend.transaction_step_id
                   ,'P_PDS_INFORMATION15')
       END                                        pds_information15_p
      ,CASE WHEN pend.transaction_id IS NOT NULL
            THEN xxas_com_vset_pkg.decode_descr_vset_value
                   ('800'
                   ,'PER_PDS_DEVELOPER_DF'
                   ,xxas_hr_api_transaction_pkg.varchar2_value
                      (pend.person_id
                      ,pend.api_name
                      ,pend.transaction_step_id
                      ,'P_PDS_INFORMATION_CATEGORY')
                   ,'PDS_INFORMATION15'
                   ,xxas_hr_api_transaction_pkg.varchar2_value
                      (pend.person_id
                      ,pend.api_name
                      ,pend.transaction_step_id
                      ,'P_PDS_INFORMATION15'))
       END                                        pds_information15_meaning_p
      ,CASE WHEN pend.transaction_id IS NOT NULL
            THEN xxas_hr_api_transaction_pkg.varchar2_value
                   (pend.person_id
                   ,pend.api_name
                   ,pend.transaction_step_id
                   ,'P_PDS_INFORMATION16')
       END                                        pds_information16_p
      ,CASE WHEN pend.transaction_id IS NOT NULL
            THEN xxas_com_vset_pkg.decode_descr_vset_value
                   ('800'
                   ,'PER_PDS_DEVELOPER_DF'
                   ,xxas_hr_api_transaction_pkg.varchar2_value
                      (pend.person_id
                      ,pend.api_name
                      ,pend.transaction_step_id
                      ,'P_PDS_INFORMATION_CATEGORY')
                   ,'PDS_INFORMATION16'
                   ,xxas_hr_api_transaction_pkg.varchar2_value
                      (pend.person_id
                      ,pend.api_name
                      ,pend.transaction_step_id
                      ,'P_PDS_INFORMATION16'))
       END                                        pds_information16_meaning_p
      ,CASE WHEN pend.transaction_id IS NOT NULL
            THEN xxas_hr_api_transaction_pkg.varchar2_value
                   (pend.person_id
                   ,pend.api_name
                   ,pend.transaction_step_id
                   ,'P_PDS_INFORMATION17')
       END                                        pds_information17_p
      ,CASE WHEN pend.transaction_id IS NOT NULL
            THEN xxas_com_vset_pkg.decode_descr_vset_value
                   ('800'
                   ,'PER_PDS_DEVELOPER_DF'
                   ,xxas_hr_api_transaction_pkg.varchar2_value
                      (pend.person_id
                      ,pend.api_name
                      ,pend.transaction_step_id
                      ,'P_PDS_INFORMATION_CATEGORY')
                   ,'PDS_INFORMATION17'
                   ,xxas_hr_api_transaction_pkg.varchar2_value
                      (pend.person_id
                      ,pend.api_name
                      ,pend.transaction_step_id
                      ,'P_PDS_INFORMATION17'))
       END                                        pds_information17_meaning_p
      ,CASE WHEN pend.transaction_id IS NOT NULL
            THEN xxas_hr_api_transaction_pkg.varchar2_value
                   (pend.person_id
                   ,pend.api_name
                   ,pend.transaction_step_id
                   ,'P_PDS_INFORMATION18')
       END                                        pds_information18_p
      ,CASE WHEN pend.transaction_id IS NOT NULL
            THEN xxas_com_vset_pkg.decode_descr_vset_value
                   ('800'
                   ,'PER_PDS_DEVELOPER_DF'
                   ,xxas_hr_api_transaction_pkg.varchar2_value
                      (pend.person_id
                      ,pend.api_name
                      ,pend.transaction_step_id
                      ,'P_PDS_INFORMATION_CATEGORY')
                   ,'PDS_INFORMATION18'
                   ,xxas_hr_api_transaction_pkg.varchar2_value
                      (pend.person_id
                      ,pend.api_name
                      ,pend.transaction_step_id
                      ,'P_PDS_INFORMATION18'))
       END                                        pds_information18_meaning_p
      ,CASE WHEN pend.transaction_id IS NOT NULL
            THEN xxas_hr_api_transaction_pkg.varchar2_value
                   (pend.person_id
                   ,pend.api_name
                   ,pend.transaction_step_id
                   ,'P_PDS_INFORMATION19')
       END                                        pds_information19_p
      ,CASE WHEN pend.transaction_id IS NOT NULL
            THEN xxas_com_vset_pkg.decode_descr_vset_value
                   ('800'
                   ,'PER_PDS_DEVELOPER_DF'
                   ,xxas_hr_api_transaction_pkg.varchar2_value
                      (pend.person_id
                      ,pend.api_name
                      ,pend.transaction_step_id
                      ,'P_PDS_INFORMATION_CATEGORY')
                   ,'PDS_INFORMATION19'
                   ,xxas_hr_api_transaction_pkg.varchar2_value
                      (pend.person_id
                      ,pend.api_name
                      ,pend.transaction_step_id
                      ,'P_PDS_INFORMATION19'))
       END                                        pds_information19_meaning_p
      ,CASE WHEN pend.transaction_id IS NOT NULL
            THEN xxas_hr_api_transaction_pkg.varchar2_value
                   (pend.person_id
                   ,pend.api_name
                   ,pend.transaction_step_id
                   ,'P_PDS_INFORMATION20')
       END                                        pds_information20_p
      ,CASE WHEN pend.transaction_id IS NOT NULL
            THEN xxas_com_vset_pkg.decode_descr_vset_value
                   ('800'
                   ,'PER_PDS_DEVELOPER_DF'
                   ,xxas_hr_api_transaction_pkg.varchar2_value
                      (pend.person_id
                      ,pend.api_name
                      ,pend.transaction_step_id
                      ,'P_PDS_INFORMATION_CATEGORY')
                   ,'PDS_INFORMATION20'
                   ,xxas_hr_api_transaction_pkg.varchar2_value
                      (pend.person_id
                      ,pend.api_name
                      ,pend.transaction_step_id
                      ,'P_PDS_INFORMATION20'))
       END                                        pds_information20_meaning_p
      ,CASE WHEN pend.transaction_id IS NOT NULL
            THEN xxas_hr_api_transaction_pkg.varchar2_value
                   (pend.person_id
                   ,pend.api_name
                   ,pend.transaction_step_id
                   ,'P_PDS_INFORMATION21')
       END                                        pds_information21_p
      ,CASE WHEN pend.transaction_id IS NOT NULL
            THEN xxas_com_vset_pkg.decode_descr_vset_value
                   ('800'
                   ,'PER_PDS_DEVELOPER_DF'
                   ,xxas_hr_api_transaction_pkg.varchar2_value
                      (pend.person_id
                      ,pend.api_name
                      ,pend.transaction_step_id
                      ,'P_PDS_INFORMATION_CATEGORY')
                   ,'PDS_INFORMATION21'
                   ,xxas_hr_api_transaction_pkg.varchar2_value
                      (pend.person_id
                      ,pend.api_name
                      ,pend.transaction_step_id
                      ,'P_PDS_INFORMATION21'))
       END                                        pds_information21_meaning_p
      ,CASE WHEN pend.transaction_id IS NOT NULL
            THEN xxas_hr_api_transaction_pkg.varchar2_value
                   (pend.person_id
                   ,pend.api_name
                   ,pend.transaction_step_id
                   ,'P_PDS_INFORMATION22')
       END                                        pds_information22_p
      ,CASE WHEN pend.transaction_id IS NOT NULL
            THEN xxas_com_vset_pkg.decode_descr_vset_value
                   ('800'
                   ,'PER_PDS_DEVELOPER_DF'
                   ,xxas_hr_api_transaction_pkg.varchar2_value
                      (pend.person_id
                      ,pend.api_name
                      ,pend.transaction_step_id
                      ,'P_PDS_INFORMATION_CATEGORY')
                   ,'PDS_INFORMATION22'
                   ,xxas_hr_api_transaction_pkg.varchar2_value
                      (pend.person_id
                      ,pend.api_name
                      ,pend.transaction_step_id
                      ,'P_PDS_INFORMATION22'))
       END                                        pds_information22_meaning_p
      ,CASE WHEN pend.transaction_id IS NOT NULL
            THEN xxas_hr_api_transaction_pkg.varchar2_value
                   (pend.person_id
                   ,pend.api_name
                   ,pend.transaction_step_id
                   ,'P_PDS_INFORMATION23')
       END                                        pds_information23_p
      ,CASE WHEN pend.transaction_id IS NOT NULL
            THEN xxas_com_vset_pkg.decode_descr_vset_value
                   ('800'
                   ,'PER_PDS_DEVELOPER_DF'
                   ,xxas_hr_api_transaction_pkg.varchar2_value
                      (pend.person_id
                      ,pend.api_name
                      ,pend.transaction_step_id
                      ,'P_PDS_INFORMATION_CATEGORY')
                   ,'PDS_INFORMATION23'
                   ,xxas_hr_api_transaction_pkg.varchar2_value
                      (pend.person_id
                      ,pend.api_name
                      ,pend.transaction_step_id
                      ,'P_PDS_INFORMATION23'))
       END                                        pds_information23_meaning_p
      ,CASE WHEN pend.transaction_id IS NOT NULL
            THEN xxas_hr_api_transaction_pkg.varchar2_value
                   (pend.person_id
                   ,pend.api_name
                   ,pend.transaction_step_id
                   ,'P_PDS_INFORMATION24')
       END                                        pds_information24_p
      ,CASE WHEN pend.transaction_id IS NOT NULL
            THEN xxas_com_vset_pkg.decode_descr_vset_value
                   ('800'
                   ,'PER_PDS_DEVELOPER_DF'
                   ,xxas_hr_api_transaction_pkg.varchar2_value
                      (pend.person_id
                      ,pend.api_name
                      ,pend.transaction_step_id
                      ,'P_PDS_INFORMATION_CATEGORY')
                   ,'PDS_INFORMATION24'
                   ,xxas_hr_api_transaction_pkg.varchar2_value
                      (pend.person_id
                      ,pend.api_name
                      ,pend.transaction_step_id
                      ,'P_PDS_INFORMATION24'))
       END                                        pds_information24_meaning_p
      ,CASE WHEN pend.transaction_id IS NOT NULL
            THEN xxas_hr_api_transaction_pkg.varchar2_value
                   (pend.person_id
                   ,pend.api_name
                   ,pend.transaction_step_id
                   ,'P_PDS_INFORMATION25')
       END                                        pds_information25_p
      ,CASE WHEN pend.transaction_id IS NOT NULL
            THEN xxas_com_vset_pkg.decode_descr_vset_value
                   ('800'
                   ,'PER_PDS_DEVELOPER_DF'
                   ,xxas_hr_api_transaction_pkg.varchar2_value
                      (pend.person_id
                      ,pend.api_name
                      ,pend.transaction_step_id
                      ,'P_PDS_INFORMATION_CATEGORY')
                   ,'PDS_INFORMATION25'
                   ,xxas_hr_api_transaction_pkg.varchar2_value
                      (pend.person_id
                      ,pend.api_name
                      ,pend.transaction_step_id
                      ,'P_PDS_INFORMATION25'))
       END                                        pds_information25_meaning_p
      ,CASE WHEN pend.transaction_id IS NOT NULL
            THEN xxas_hr_api_transaction_pkg.varchar2_value
                   (pend.person_id
                   ,pend.api_name
                   ,pend.transaction_step_id
                   ,'P_PDS_INFORMATION26')
       END                                        pds_information26_p
      ,CASE WHEN pend.transaction_id IS NOT NULL
            THEN xxas_com_vset_pkg.decode_descr_vset_value
                   ('800'
                   ,'PER_PDS_DEVELOPER_DF'
                   ,xxas_hr_api_transaction_pkg.varchar2_value
                      (pend.person_id
                      ,pend.api_name
                      ,pend.transaction_step_id
                      ,'P_PDS_INFORMATION_CATEGORY')
                   ,'PDS_INFORMATION26'
                   ,xxas_hr_api_transaction_pkg.varchar2_value
                      (pend.person_id
                      ,pend.api_name
                      ,pend.transaction_step_id
                      ,'P_PDS_INFORMATION26'))
       END                                        pds_information26_meaning_p
      ,CASE WHEN pend.transaction_id IS NOT NULL
            THEN xxas_hr_api_transaction_pkg.varchar2_value
                   (pend.person_id
                   ,pend.api_name
                   ,pend.transaction_step_id
                   ,'P_PDS_INFORMATION27')
       END                                        pds_information27_p
      ,CASE WHEN pend.transaction_id IS NOT NULL
            THEN xxas_com_vset_pkg.decode_descr_vset_value
                   ('800'
                   ,'PER_PDS_DEVELOPER_DF'
                   ,xxas_hr_api_transaction_pkg.varchar2_value
                      (pend.person_id
                      ,pend.api_name
                      ,pend.transaction_step_id
                      ,'P_PDS_INFORMATION_CATEGORY')
                   ,'PDS_INFORMATION27'
                   ,xxas_hr_api_transaction_pkg.varchar2_value
                      (pend.person_id
                      ,pend.api_name
                      ,pend.transaction_step_id
                      ,'P_PDS_INFORMATION27'))
       END                                        pds_information27_meaning_p
      ,CASE WHEN pend.transaction_id IS NOT NULL
            THEN xxas_hr_api_transaction_pkg.varchar2_value
                   (pend.person_id
                   ,pend.api_name
                   ,pend.transaction_step_id
                   ,'P_PDS_INFORMATION28')
       END                                        pds_information28_p
      ,CASE WHEN pend.transaction_id IS NOT NULL
            THEN xxas_com_vset_pkg.decode_descr_vset_value
                   ('800'
                   ,'PER_PDS_DEVELOPER_DF'
                   ,xxas_hr_api_transaction_pkg.varchar2_value
                      (pend.person_id
                      ,pend.api_name
                      ,pend.transaction_step_id
                      ,'P_PDS_INFORMATION_CATEGORY')
                   ,'PDS_INFORMATION28'
                   ,xxas_hr_api_transaction_pkg.varchar2_value
                      (pend.person_id
                      ,pend.api_name
                      ,pend.transaction_step_id
                      ,'P_PDS_INFORMATION28'))
       END                                        pds_information28_meaning_p
      ,CASE WHEN pend.transaction_id IS NOT NULL
            THEN xxas_hr_api_transaction_pkg.varchar2_value
                   (pend.person_id
                   ,pend.api_name
                   ,pend.transaction_step_id
                   ,'P_PDS_INFORMATION29')
       END                                        pds_information29_p
      ,CASE WHEN pend.transaction_id IS NOT NULL
            THEN xxas_com_vset_pkg.decode_descr_vset_value
                   ('800'
                   ,'PER_PDS_DEVELOPER_DF'
                   ,xxas_hr_api_transaction_pkg.varchar2_value
                      (pend.person_id
                      ,pend.api_name
                      ,pend.transaction_step_id
                      ,'P_PDS_INFORMATION_CATEGORY')
                   ,'PDS_INFORMATION29'
                   ,xxas_hr_api_transaction_pkg.varchar2_value
                      (pend.person_id
                      ,pend.api_name
                      ,pend.transaction_step_id
                      ,'P_PDS_INFORMATION29'))
       END                                        pds_information29_meaning_p
      ,CASE WHEN pend.transaction_id IS NOT NULL
            THEN xxas_hr_api_transaction_pkg.varchar2_value
                   (pend.person_id
                   ,pend.api_name
                   ,pend.transaction_step_id
                   ,'P_PDS_INFORMATION30')
       END                                        pds_information30_p
      ,CASE WHEN pend.transaction_id IS NOT NULL
            THEN xxas_com_vset_pkg.decode_descr_vset_value
                   ('800'
                   ,'PER_PDS_DEVELOPER_DF'
                   ,xxas_hr_api_transaction_pkg.varchar2_value
                      (pend.person_id
                      ,pend.api_name
                      ,pend.transaction_step_id
                      ,'P_PDS_INFORMATION_CATEGORY')
                   ,'PDS_INFORMATION30'
                   ,xxas_hr_api_transaction_pkg.varchar2_value
                      (pend.person_id
                      ,pend.api_name
                      ,pend.transaction_step_id
                      ,'P_PDS_INFORMATION30'))
       END                                        pds_information30_meaning_p
      ,CASE WHEN pend.transaction_id IS NOT NULL
            THEN xxas_hr_api_transaction_pkg.varchar2_value
                   (pend.person_id
                   ,pend.api_name
                   ,pend.transaction_step_id
                   ,'P_COMMENTS')
       END                                        comments_to_approver_p
      ,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
      ,'PQH_SS_ATTACHMENT'                        attachment_entity
      ,pend.transaction_id                        attachment_pk1
      ,pend.transaction_id                        approvers
FROM   per_periods_of_service pps
      ,per_all_people_f       ppf
      ,per_all_assignments_f  paaf
      ,(SELECT pps.person_id
              ,pps.period_of_service_id
              ,hats.api_name
              ,hat.transaction_id
              ,eid.transaction_step_id
              ,'UPDATE'                                                   transaction_type
              ,hat.status                                                 hr_api_status_code
              ,xxas_selfservice_workflow_pkg.transcode_status(hat.status) workflow_status_code
              ,hat.effective_date_option
        FROM   per_periods_of_service      pps
              ,hr_api_transactions         hat
              ,hr_api_transaction_steps    hats
              ,hr_api_transaction_values   act
              ,hr_api_transaction_values   eid
        WHERE  pps.person_id            = hat.selected_person_id
        AND    hat.transaction_id       = hats.transaction_id
        AND    hat.status          NOT IN ('D', 'N', 'W', 'ACTIVE', 'AC')
        AND    hats.api_name            = 'HR_TERMINATION_SS.PROCESS_API'
        AND    hats.transaction_step_id = act.transaction_step_id
        AND    act.name                 = 'P_ACTIVITY_NAME'
        AND    act.varchar2_value       = 'HR_TERMINATION_SS'
        AND    hats.transaction_step_id = eid.transaction_step_id
        AND    eid.name                 = 'P_PERIOD_OF_SERVICE_ID'
        AND    eid.number_value         = pps.period_of_service_id
        ) pend
WHERE  pps.person_id                    = ppf.person_id
AND    xxas_mss_util_pkg.max_per_last_change_date(ppf.person_id) BETWEEN ppf.effective_start_date 
                                                                 AND     ppf.effective_end_date
AND    ppf.person_id                    = paaf.person_id
AND    xxas_mss_util_pkg.max_asg_last_change_date(paaf.assignment_id) BETWEEN paaf.effective_start_date 
                                                                      AND     paaf.effective_end_date
AND    NVL(paaf.primary_flag,'N') = 'Y'
AND    paaf.period_of_service_id = pps.period_of_service_id
AND    NVL(ppf.current_employee_flag,'N') = 'Y'
AND    pps.person_id                    = pend.person_id(+)
AND    pps.period_of_service_id         = pend.period_of_service_id(+)
AND    nvl(pps.final_process_date,xxas_mss_util_pkg.max_per_last_change_date(ppf.person_id)) >= xxas_mss_util_pkg.max_per_last_change_date(ppf.person_id);

BEGIN
  xxas_fnd_dictionary_pkg.add_view
   (p_application_short_name => xxas_util_pkg.app
   ,p_view_name              => 'XXAS_EMP_LEAVER_REQUEST_V'
   ,p_description            => 'Applaud Emp Leaver Request View');
END;
/

COMMIT;
EXIT;

