/* $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_cwk_leaver_request_v 
AS
SELECT ppp.period_of_placement_id
      ,ppp.object_version_number
      ,ppp.business_group_id
      ,null effective_date_option
      ,null last_day_of_work
      ,(SELECT count(*) FROM table(xxas_mss_util_pkg.direct_reportees_data(ppp.person_id,paaf.assignment_id))) direct_reports_count
      ,(CASE WHEN (SELECT count(*) FROM table(xxas_mss_util_pkg.direct_reportees_data(ppp.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(ppp.person_id))) >= 1
             THEN 'Y'
             ELSE 'N'
        END) is_org_info
      ,ppp.person_id new_manager_dr
      ,ppp.person_id
      ,null person_type
      ,null asg_status
      ,paaf.assignment_id
      ,ppf.rehire_recommendation                  rehire_flag
      ,ppf.rehire_reason
      ,ppp.date_start
      ,ppp.actual_termination_date
      ,ppp.final_process_date
      ,ppp.last_standard_process_date
      ,ppp.termination_reason                     leav_reas
      ,ppp.projected_termination_date             projected_date
      ,ppp.attribute_category
      ,ppp.attribute1
      ,xxas_com_vset_pkg.decode_descr_vset_value
         ('800'
         ,'PER_PERIODS_OF_PLACEMENT'
         ,ppp.attribute_category
         ,'ATTRIBUTE1'
         ,ppp.attribute1)                        attribute1_meaning 
      ,ppp.attribute2
      ,xxas_com_vset_pkg.decode_descr_vset_value
         ('800'
         ,'PER_PERIODS_OF_PLACEMENT'
         ,ppp.attribute_category
         ,'ATTRIBUTE2'
         ,ppp.attribute2)                        attribute2_meaning 
      ,ppp.attribute3
      ,xxas_com_vset_pkg.decode_descr_vset_value
         ('800'
         ,'PER_PERIODS_OF_PLACEMENT'
         ,ppp.attribute_category
         ,'ATTRIBUTE3'
         ,ppp.attribute3)                        attribute3_meaning 
      ,ppp.attribute4
      ,xxas_com_vset_pkg.decode_descr_vset_value
         ('800'
         ,'PER_PERIODS_OF_PLACEMENT'
         ,ppp.attribute_category
         ,'ATTRIBUTE4'
         ,ppp.attribute4)                        attribute4_meaning 
      ,ppp.attribute5
      ,xxas_com_vset_pkg.decode_descr_vset_value
         ('800'
         ,'PER_PERIODS_OF_PLACEMENT'
         ,ppp.attribute_category
         ,'ATTRIBUTE5'
         ,ppp.attribute5)                        attribute5_meaning 
      ,ppp.attribute6
      ,xxas_com_vset_pkg.decode_descr_vset_value
         ('800'
         ,'PER_PERIODS_OF_PLACEMENT'
         ,ppp.attribute_category
         ,'ATTRIBUTE6'
         ,ppp.attribute6)                        attribute6_meaning 
      ,ppp.attribute7
      ,xxas_com_vset_pkg.decode_descr_vset_value
         ('800'
         ,'PER_PERIODS_OF_PLACEMENT'
         ,ppp.attribute_category
         ,'ATTRIBUTE7'
         ,ppp.attribute7)                        attribute7_meaning 
      ,ppp.attribute8
      ,xxas_com_vset_pkg.decode_descr_vset_value
         ('800'
         ,'PER_PERIODS_OF_PLACEMENT'
         ,ppp.attribute_category
         ,'ATTRIBUTE8'
         ,ppp.attribute8)                        attribute8_meaning 
      ,ppp.attribute9
      ,xxas_com_vset_pkg.decode_descr_vset_value
         ('800'
         ,'PER_PERIODS_OF_PLACEMENT'
         ,ppp.attribute_category
         ,'ATTRIBUTE9'
         ,ppp.attribute9)                        attribute9_meaning 
      ,ppp.attribute10
      ,xxas_com_vset_pkg.decode_descr_vset_value
         ('800'
         ,'PER_PERIODS_OF_PLACEMENT'
         ,ppp.attribute_category
         ,'ATTRIBUTE10'
         ,ppp.attribute10)                      attribute10_meaning 
      ,ppp.attribute11
      ,xxas_com_vset_pkg.decode_descr_vset_value
         ('800'
         ,'PER_PERIODS_OF_PLACEMENT'
         ,ppp.attribute_category
         ,'ATTRIBUTE11'
         ,ppp.attribute11)                      attribute11_meaning 
      ,ppp.attribute12
      ,xxas_com_vset_pkg.decode_descr_vset_value
         ('800'
         ,'PER_PERIODS_OF_PLACEMENT'
         ,ppp.attribute_category
         ,'ATTRIBUTE12'
         ,ppp.attribute12)                       attribute12_meaning 
      ,ppp.attribute13
      ,xxas_com_vset_pkg.decode_descr_vset_value
         ('800'
         ,'PER_PERIODS_OF_PLACEMENT'
         ,ppp.attribute_category
         ,'ATTRIBUTE13'
         ,ppp.attribute13)                       attribute13_meaning 
      ,ppp.attribute14
      ,xxas_com_vset_pkg.decode_descr_vset_value
         ('800'
         ,'PER_PERIODS_OF_PLACEMENT'
         ,ppp.attribute_category
         ,'ATTRIBUTE14'
         ,ppp.attribute14)                       attribute14_meaning 
      ,ppp.attribute15
      ,xxas_com_vset_pkg.decode_descr_vset_value
         ('800'
         ,'PER_PERIODS_OF_PLACEMENT'
         ,ppp.attribute_category
         ,'ATTRIBUTE15'
         ,ppp.attribute15)                       attribute15_meaning 
      ,ppp.attribute16
      ,xxas_com_vset_pkg.decode_descr_vset_value
         ('800'
         ,'PER_PERIODS_OF_PLACEMENT'
         ,ppp.attribute_category
         ,'ATTRIBUTE16'
         ,ppp.attribute16)                       attribute16_meaning 
      ,ppp.attribute17
      ,xxas_com_vset_pkg.decode_descr_vset_value
         ('800'
         ,'PER_PERIODS_OF_PLACEMENT'
         ,ppp.attribute_category
         ,'ATTRIBUTE17'
         ,ppp.attribute17)                       attribute17_meaning 
      ,ppp.attribute18
      ,xxas_com_vset_pkg.decode_descr_vset_value
         ('800'
         ,'PER_PERIODS_OF_PLACEMENT'
         ,ppp.attribute_category
         ,'ATTRIBUTE18'
         ,ppp.attribute18)                       attribute18_meaning 
      ,ppp.attribute19
      ,xxas_com_vset_pkg.decode_descr_vset_value
         ('800'
         ,'PER_PERIODS_OF_PLACEMENT'
         ,ppp.attribute_category
         ,'ATTRIBUTE19'
         ,ppp.attribute19)                       attribute19_meaning 
      ,ppp.attribute20
      ,xxas_com_vset_pkg.decode_descr_vset_value
         ('800'
         ,'PER_PERIODS_OF_PLACEMENT'
         ,ppp.attribute_category
         ,'ATTRIBUTE20'
         ,ppp.attribute20)                       attribute20_meaning
      ,ppp.attribute21
      ,xxas_com_vset_pkg.decode_descr_vset_value
         ('800'
         ,'PER_PERIODS_OF_PLACEMENT'
         ,ppp.attribute_category
         ,'ATTRIBUTE21'
         ,ppp.attribute21)                      attribute21_meaning 
      ,ppp.attribute22
      ,xxas_com_vset_pkg.decode_descr_vset_value
         ('800'
         ,'PER_PERIODS_OF_PLACEMENT'
         ,ppp.attribute_category
         ,'ATTRIBUTE22'
         ,ppp.attribute22)                       attribute22_meaning 
      ,ppp.attribute23
      ,xxas_com_vset_pkg.decode_descr_vset_value
         ('800'
         ,'PER_PERIODS_OF_PLACEMENT'
         ,ppp.attribute_category
         ,'ATTRIBUTE23'
         ,ppp.attribute23)                       attribute23_meaning 
      ,ppp.attribute24
      ,xxas_com_vset_pkg.decode_descr_vset_value
         ('800'
         ,'PER_PERIODS_OF_PLACEMENT'
         ,ppp.attribute_category
         ,'ATTRIBUTE24'
         ,ppp.attribute24)                       attribute24_meaning 
      ,ppp.attribute25
      ,xxas_com_vset_pkg.decode_descr_vset_value
         ('800'
         ,'PER_PERIODS_OF_PLACEMENT'
         ,ppp.attribute_category
         ,'ATTRIBUTE25'
         ,ppp.attribute25)                       attribute25_meaning 
      ,ppp.attribute26
      ,xxas_com_vset_pkg.decode_descr_vset_value
         ('800'
         ,'PER_PERIODS_OF_PLACEMENT'
         ,ppp.attribute_category
         ,'ATTRIBUTE26'
         ,ppp.attribute26)                       attribute26_meaning 
      ,ppp.attribute27
      ,xxas_com_vset_pkg.decode_descr_vset_value
         ('800'
         ,'PER_PERIODS_OF_PLACEMENT'
         ,ppp.attribute_category
         ,'ATTRIBUTE27'
         ,ppp.attribute27)                       attribute27_meaning 
      ,ppp.attribute28
      ,xxas_com_vset_pkg.decode_descr_vset_value
         ('800'
         ,'PER_PERIODS_OF_PLACEMENT'
         ,ppp.attribute_category
         ,'ATTRIBUTE28'
         ,ppp.attribute28)                       attribute28_meaning 
      ,ppp.attribute29
      ,xxas_com_vset_pkg.decode_descr_vset_value
         ('800'
         ,'PER_PERIODS_OF_PLACEMENT'
         ,ppp.attribute_category
         ,'ATTRIBUTE29'
         ,ppp.attribute29)                       attribute29_meaning 
      ,ppp.attribute30
      ,xxas_com_vset_pkg.decode_descr_vset_value
         ('800'
         ,'PER_PERIODS_OF_PLACEMENT'
         ,ppp.attribute_category
         ,'ATTRIBUTE30'
         ,ppp.attribute30)                       attribute30_meaning 
      ,ppp.last_update_date
      ,ppp.last_updated_by
      ,ppp.last_update_login
      ,ppp.created_by
      ,ppp.creation_date
      ,ppp.information_category
      ,ppp.information1
      ,xxas_com_vset_pkg.decode_descr_vset_value
         ('800'
         ,'PER_PDP_DEVELOPER_DF'
         ,ppp.information_category
         ,'INFORMATION1'
         ,ppp.information1)                  information1_meaning 
      ,ppp.information2
      ,xxas_com_vset_pkg.decode_descr_vset_value
         ('800'
         ,'PER_PDP_DEVELOPER_DF'
         ,ppp.information_category
         ,'INFORMATION2'
         ,ppp.information2)                  information2_meaning
      ,ppp.information3
      ,xxas_com_vset_pkg.decode_descr_vset_value
         ('800'
         ,'PER_PDP_DEVELOPER_DF'
         ,ppp.information_category
         ,'INFORMATION3'
         ,ppp.information3)                  information3_meaning
      ,ppp.information4
      ,xxas_com_vset_pkg.decode_descr_vset_value
         ('800'
         ,'PER_PDP_DEVELOPER_DF'
         ,ppp.information_category
         ,'INFORMATION4'
         ,ppp.information4)                  information4_meaning
      ,ppp.information5
      ,xxas_com_vset_pkg.decode_descr_vset_value
         ('800'
         ,'PER_PDP_DEVELOPER_DF'
         ,ppp.information_category
         ,'INFORMATION5'
         ,ppp.information5)                  information5_meaning
      ,ppp.information6
      ,xxas_com_vset_pkg.decode_descr_vset_value
         ('800'
         ,'PER_PDP_DEVELOPER_DF'
         ,ppp.information_category
         ,'INFORMATION6'
         ,ppp.information6)                  information6_meaning
      ,ppp.information7
      ,xxas_com_vset_pkg.decode_descr_vset_value
         ('800'
         ,'PER_PDP_DEVELOPER_DF'
         ,ppp.information_category
         ,'INFORMATION7'
         ,ppp.information7)                  information7_meaning
      ,ppp.information8
      ,xxas_com_vset_pkg.decode_descr_vset_value
         ('800'
         ,'PER_PDP_DEVELOPER_DF'
         ,ppp.information_category
         ,'INFORMATION8'
         ,ppp.information8)                  information8_meaning
      ,ppp.information9
      ,xxas_com_vset_pkg.decode_descr_vset_value
         ('800'
         ,'PER_PDP_DEVELOPER_DF'
         ,ppp.information_category
         ,'INFORMATION9'
         ,ppp.information9)                  information9_meaning
      ,ppp.information10
      ,xxas_com_vset_pkg.decode_descr_vset_value
         ('800'
         ,'PER_PDP_DEVELOPER_DF'
         ,ppp.information_category
         ,'INFORMATION10'
         ,ppp.information10)                 information10_meaning
      ,ppp.information11
      ,xxas_com_vset_pkg.decode_descr_vset_value
         ('800'
         ,'PER_PDP_DEVELOPER_DF'
         ,ppp.information_category
         ,'INFORMATION11'
         ,ppp.information11)                 information11_meaning
      ,ppp.information12
      ,xxas_com_vset_pkg.decode_descr_vset_value
         ('800'
         ,'PER_PDP_DEVELOPER_DF'
         ,ppp.information_category
         ,'INFORMATION12'
         ,ppp.information12)                 information12_meaning
      ,ppp.information13
      ,xxas_com_vset_pkg.decode_descr_vset_value
         ('800'
         ,'PER_PDP_DEVELOPER_DF'
         ,ppp.information_category
         ,'INFORMATION13'
         ,ppp.information13)                 information13_meaning
      ,ppp.information14
      ,xxas_com_vset_pkg.decode_descr_vset_value
         ('800'
         ,'PER_PDP_DEVELOPER_DF'
         ,ppp.information_category
         ,'INFORMATION14'
         ,ppp.information14)                 information14_meaning
      ,ppp.information15
      ,xxas_com_vset_pkg.decode_descr_vset_value
         ('800'
         ,'PER_PDP_DEVELOPER_DF'
         ,ppp.information_category
         ,'INFORMATION15'
         ,ppp.information15)                 information15_meaning
      ,ppp.information16
      ,xxas_com_vset_pkg.decode_descr_vset_value
         ('800'
         ,'PER_PDP_DEVELOPER_DF'
         ,ppp.information_category
         ,'INFORMATION16'
         ,ppp.information16)                 information16_meaning
      ,ppp.information17
      ,xxas_com_vset_pkg.decode_descr_vset_value
         ('800'
         ,'PER_PDP_DEVELOPER_DF'
         ,ppp.information_category
         ,'INFORMATION17'
         ,ppp.information17)                 information17_meaning
      ,ppp.information18
      ,xxas_com_vset_pkg.decode_descr_vset_value
         ('800'
         ,'PER_PDP_DEVELOPER_DF'
         ,ppp.information_category
         ,'INFORMATION18'
         ,ppp.information18)                 information18_meaning
      ,ppp.information19
      ,xxas_com_vset_pkg.decode_descr_vset_value
         ('800'
         ,'PER_PDP_DEVELOPER_DF'
         ,ppp.information_category
         ,'INFORMATION19'
         ,ppp.information19)                 information19_meaning
      ,ppp.information20
      ,xxas_com_vset_pkg.decode_descr_vset_value
         ('800'
         ,'PER_PDP_DEVELOPER_DF'
         ,ppp.information_category
         ,'INFORMATION20'
         ,ppp.information20)                 information20_meaning
      ,ppp.information21
      ,xxas_com_vset_pkg.decode_descr_vset_value
         ('800'
         ,'PER_PDP_DEVELOPER_DF'
         ,ppp.information_category
         ,'INFORMATION21'
         ,ppp.information21)                 information21_meaning
      ,ppp.information22
      ,xxas_com_vset_pkg.decode_descr_vset_value
         ('800'
         ,'PER_PDP_DEVELOPER_DF'
         ,ppp.information_category
         ,'INFORMATION22'
         ,ppp.information22)                 information22_meaning
      ,ppp.information23
      ,xxas_com_vset_pkg.decode_descr_vset_value
         ('800'
         ,'PER_PDP_DEVELOPER_DF'
         ,ppp.information_category
         ,'INFORMATION23'
         ,ppp.information23)                 information23_meaning
      ,ppp.information24
      ,xxas_com_vset_pkg.decode_descr_vset_value
         ('800'
         ,'PER_PDP_DEVELOPER_DF'
         ,ppp.information_category
         ,'INFORMATION24'
         ,ppp.information24)                 information24_meaning
      ,ppp.information25
      ,xxas_com_vset_pkg.decode_descr_vset_value
         ('800'
         ,'PER_PDP_DEVELOPER_DF'
         ,ppp.information_category
         ,'INFORMATION25'
         ,ppp.information25)                 information25_meaning
      ,ppp.information26
      ,xxas_com_vset_pkg.decode_descr_vset_value
         ('800'
         ,'PER_PDP_DEVELOPER_DF'
         ,ppp.information_category
         ,'INFORMATION26'
         ,ppp.information26)                 information26_meaning
      ,ppp.information27
      ,xxas_com_vset_pkg.decode_descr_vset_value
         ('800'
         ,'PER_PDP_DEVELOPER_DF'
         ,ppp.information_category
         ,'INFORMATION27'
         ,ppp.information27)                 information27_meaning
      ,ppp.information28
      ,xxas_com_vset_pkg.decode_descr_vset_value
         ('800'
         ,'PER_PDP_DEVELOPER_DF'
         ,ppp.information_category
         ,'INFORMATION28'
         ,ppp.information28)                 information28_meaning
      ,ppp.information29
      ,xxas_com_vset_pkg.decode_descr_vset_value
         ('800'
         ,'PER_PDP_DEVELOPER_DF'
         ,ppp.information_category
         ,'INFORMATION29'
         ,ppp.information29)                 information29_meaning
      ,ppp.information30
      ,xxas_com_vset_pkg.decode_descr_vset_value
         ('800'
         ,'PER_PDP_DEVELOPER_DF'
         ,ppp.information_category
         ,'INFORMATION30'
         ,ppp.information30)                 information30_meaning
      ,null                                  comments_to_approver
      ,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 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 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(ppp.person_id)
                              ,hier.mgr_person_id
                              ,null
                              )
                     FROM    TABLE(xxas_mss_util_pkg.direct_reportees_data(ppp.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(ppp.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_DATE_START')
       END                                       date_start_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_TERMINATION_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_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_PLACEMENT'
                   ,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_PLACEMENT'
                   ,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_PLACEMENT'
                   ,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_PLACEMENT'
                   ,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_PLACEMENT'
                   ,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_PLACEMENT'
                   ,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_PLACEMENT'
                   ,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_PLACEMENT'
                   ,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_PLACEMENT'
                   ,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_PLACEMENT'
                   ,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_PLACEMENT'
                   ,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_PLACEMENT'
                   ,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_PLACEMENT'
                   ,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_PLACEMENT'
                   ,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_PLACEMENT'
                   ,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_PLACEMENT'
                   ,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_PLACEMENT'
                   ,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_PLACEMENT'
                   ,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_PLACEMENT'
                   ,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_PLACEMENT'
                   ,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_ATTRIBUTE21')
       END                                       attribute21_p
      ,CASE WHEN pend.transaction_id IS NOT NULL
            THEN xxas_com_vset_pkg.decode_descr_vset_value
                   ('800'
                   ,'PER_PERIODS_OF_PLACEMENT'
                   ,xxas_hr_api_transaction_pkg.varchar2_value
                      (pend.person_id
                      ,pend.api_name
                      ,pend.transaction_step_id
                      ,'P_ATTRIBUTE_CATEGORY')
                   ,'ATTRIBUTE21'
                   ,xxas_hr_api_transaction_pkg.varchar2_value
                      (pend.person_id
                      ,pend.api_name
                      ,pend.transaction_step_id
                      ,'P_ATTRIBUTE21'))
       END                                       attribute21_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_ATTRIBUTE22')
       END                                       attribute22_p
      ,CASE WHEN pend.transaction_id IS NOT NULL
            THEN xxas_com_vset_pkg.decode_descr_vset_value
                   ('800'
                   ,'PER_PERIODS_OF_PLACEMENT'
                   ,xxas_hr_api_transaction_pkg.varchar2_value
                      (pend.person_id
                      ,pend.api_name
                      ,pend.transaction_step_id
                      ,'P_ATTRIBUTE_CATEGORY')
                   ,'ATTRIBUTE22'
                   ,xxas_hr_api_transaction_pkg.varchar2_value
                      (pend.person_id
                      ,pend.api_name
                      ,pend.transaction_step_id
                      ,'P_ATTRIBUTE22'))
       END                                       attribute22_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_ATTRIBUTE23')
       END                                       attribute23_p
      ,CASE WHEN pend.transaction_id IS NOT NULL
            THEN xxas_com_vset_pkg.decode_descr_vset_value
                   ('800'
                   ,'PER_PERIODS_OF_PLACEMENT'
                   ,xxas_hr_api_transaction_pkg.varchar2_value
                      (pend.person_id
                      ,pend.api_name
                      ,pend.transaction_step_id
                      ,'P_ATTRIBUTE_CATEGORY')
                   ,'ATTRIBUTE23'
                   ,xxas_hr_api_transaction_pkg.varchar2_value
                      (pend.person_id
                      ,pend.api_name
                      ,pend.transaction_step_id
                      ,'P_ATTRIBUTE23'))
       END                                       attribute23_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_ATTRIBUTE24')
       END                                       attribute24_p
      ,CASE WHEN pend.transaction_id IS NOT NULL
            THEN xxas_com_vset_pkg.decode_descr_vset_value
                   ('800'
                   ,'PER_PERIODS_OF_PLACEMENT'
                   ,xxas_hr_api_transaction_pkg.varchar2_value
                      (pend.person_id
                      ,pend.api_name
                      ,pend.transaction_step_id
                      ,'P_ATTRIBUTE_CATEGORY')
                   ,'ATTRIBUTE24'
                   ,xxas_hr_api_transaction_pkg.varchar2_value
                      (pend.person_id
                      ,pend.api_name
                      ,pend.transaction_step_id
                      ,'P_ATTRIBUTE24'))
       END                                       attribute24_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_ATTRIBUTE25')
       END                                       attribute25_p
      ,CASE WHEN pend.transaction_id IS NOT NULL
            THEN xxas_com_vset_pkg.decode_descr_vset_value
                   ('800'
                   ,'PER_PERIODS_OF_PLACEMENT'
                   ,xxas_hr_api_transaction_pkg.varchar2_value
                      (pend.person_id
                      ,pend.api_name
                      ,pend.transaction_step_id
                      ,'P_ATTRIBUTE_CATEGORY')
                   ,'ATTRIBUTE25'
                   ,xxas_hr_api_transaction_pkg.varchar2_value
                      (pend.person_id
                      ,pend.api_name
                      ,pend.transaction_step_id
                      ,'P_ATTRIBUTE25'))
       END                                       attribute25_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_ATTRIBUTE26')
       END                                       attribute26_p
      ,CASE WHEN pend.transaction_id IS NOT NULL
            THEN xxas_com_vset_pkg.decode_descr_vset_value
                   ('800'
                   ,'PER_PERIODS_OF_PLACEMENT'
                   ,xxas_hr_api_transaction_pkg.varchar2_value
                      (pend.person_id
                      ,pend.api_name
                      ,pend.transaction_step_id
                      ,'P_ATTRIBUTE_CATEGORY')
                   ,'ATTRIBUTE26'
                   ,xxas_hr_api_transaction_pkg.varchar2_value
                      (pend.person_id
                      ,pend.api_name
                      ,pend.transaction_step_id
                      ,'P_ATTRIBUTE26'))
       END                                       attribute26_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_ATTRIBUTE27')
       END                                       attribute27_p
      ,CASE WHEN pend.transaction_id IS NOT NULL
            THEN xxas_com_vset_pkg.decode_descr_vset_value
                   ('800'
                   ,'PER_PERIODS_OF_PLACEMENT'
                   ,xxas_hr_api_transaction_pkg.varchar2_value
                      (pend.person_id
                      ,pend.api_name
                      ,pend.transaction_step_id
                      ,'P_ATTRIBUTE_CATEGORY')
                   ,'ATTRIBUTE27'
                   ,xxas_hr_api_transaction_pkg.varchar2_value
                      (pend.person_id
                      ,pend.api_name
                      ,pend.transaction_step_id
                      ,'P_ATTRIBUTE27'))
       END                                       attribute27_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_ATTRIBUTE28')
       END                                       attribute28_p
      ,CASE WHEN pend.transaction_id IS NOT NULL
            THEN xxas_com_vset_pkg.decode_descr_vset_value
                   ('800'
                   ,'PER_PERIODS_OF_PLACEMENT'
                   ,xxas_hr_api_transaction_pkg.varchar2_value
                      (pend.person_id
                      ,pend.api_name
                      ,pend.transaction_step_id
                      ,'P_ATTRIBUTE_CATEGORY')
                   ,'ATTRIBUTE28'
                   ,xxas_hr_api_transaction_pkg.varchar2_value
                      (pend.person_id
                      ,pend.api_name
                      ,pend.transaction_step_id
                      ,'P_ATTRIBUTE28'))
       END                                       attribute28_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_ATTRIBUTE29')
       END                                       attribute29_p
      ,CASE WHEN pend.transaction_id IS NOT NULL
            THEN xxas_com_vset_pkg.decode_descr_vset_value
                   ('800'
                   ,'PER_PERIODS_OF_PLACEMENT'
                   ,xxas_hr_api_transaction_pkg.varchar2_value
                      (pend.person_id
                      ,pend.api_name
                      ,pend.transaction_step_id
                      ,'P_ATTRIBUTE_CATEGORY')
                   ,'ATTRIBUTE29'
                   ,xxas_hr_api_transaction_pkg.varchar2_value
                      (pend.person_id
                      ,pend.api_name
                      ,pend.transaction_step_id
                      ,'P_ATTRIBUTE29'))
       END                                       attribute29_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_ATTRIBUTE30')
       END                                       attribute30_p
      ,CASE WHEN pend.transaction_id IS NOT NULL
            THEN xxas_com_vset_pkg.decode_descr_vset_value
                   ('800'
                   ,'PER_PERIODS_OF_PLACEMENT'
                   ,xxas_hr_api_transaction_pkg.varchar2_value
                      (pend.person_id
                      ,pend.api_name
                      ,pend.transaction_step_id
                      ,'P_ATTRIBUTE_CATEGORY')
                   ,'ATTRIBUTE30'
                   ,xxas_hr_api_transaction_pkg.varchar2_value
                      (pend.person_id
                      ,pend.api_name
                      ,pend.transaction_step_id
                      ,'P_ATTRIBUTE30'))
       END                                       attribute30_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_INFORMATION_CATEGORY')
       END                                       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_INFORMATION1')
       END                                       information1_p
      ,CASE WHEN pend.transaction_id IS NOT NULL
            THEN xxas_com_vset_pkg.decode_descr_vset_value
                   ('800'
                   ,'PER_PDP_DEVELOPER_DF'
                   ,xxas_hr_api_transaction_pkg.varchar2_value
                      (pend.person_id
                      ,pend.api_name
                      ,pend.transaction_step_id
                      ,'P_INFORMATION_CATEGORY')
                   ,'INFORMATION1'
                   ,xxas_hr_api_transaction_pkg.varchar2_value
                      (pend.person_id
                      ,pend.api_name
                      ,pend.transaction_step_id
                      ,'P_INFORMATION1'))
       END                                       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_INFORMATION2')
       END                                       information2_p
      ,CASE WHEN pend.transaction_id IS NOT NULL
            THEN xxas_com_vset_pkg.decode_descr_vset_value
                   ('800'
                   ,'PER_PDP_DEVELOPER_DF'
                   ,xxas_hr_api_transaction_pkg.varchar2_value
                      (pend.person_id
                      ,pend.api_name
                      ,pend.transaction_step_id
                      ,'P_INFORMATION_CATEGORY')
                   ,'INFORMATION2'
                   ,xxas_hr_api_transaction_pkg.varchar2_value
                      (pend.person_id
                      ,pend.api_name
                      ,pend.transaction_step_id
                      ,'P_INFORMATION2'))
       END                                       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_INFORMATION3')
       END                                       information3_p
      ,CASE WHEN pend.transaction_id IS NOT NULL
            THEN xxas_com_vset_pkg.decode_descr_vset_value
                   ('800'
                   ,'PER_PDP_DEVELOPER_DF'
                   ,xxas_hr_api_transaction_pkg.varchar2_value
                      (pend.person_id
                      ,pend.api_name
                      ,pend.transaction_step_id
                      ,'P_INFORMATION_CATEGORY')
                   ,'INFORMATION3'
                   ,xxas_hr_api_transaction_pkg.varchar2_value
                      (pend.person_id
                      ,pend.api_name
                      ,pend.transaction_step_id
                      ,'P_INFORMATION3'))
       END                                       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_INFORMATION4')
       END                                       information4_p
      ,CASE WHEN pend.transaction_id IS NOT NULL
            THEN xxas_com_vset_pkg.decode_descr_vset_value
                   ('800'
                   ,'PER_PDP_DEVELOPER_DF'
                   ,xxas_hr_api_transaction_pkg.varchar2_value
                      (pend.person_id
                      ,pend.api_name
                      ,pend.transaction_step_id
                      ,'P_INFORMATION_CATEGORY')
                   ,'INFORMATION4'
                   ,xxas_hr_api_transaction_pkg.varchar2_value
                      (pend.person_id
                      ,pend.api_name
                      ,pend.transaction_step_id
                      ,'P_INFORMATION4'))
       END                                       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_INFORMATION5')
       END                                       information5_p
      ,CASE WHEN pend.transaction_id IS NOT NULL
            THEN xxas_com_vset_pkg.decode_descr_vset_value
                   ('800'
                   ,'PER_PDP_DEVELOPER_DF'
                   ,xxas_hr_api_transaction_pkg.varchar2_value
                      (pend.person_id
                      ,pend.api_name
                      ,pend.transaction_step_id
                      ,'P_INFORMATION_CATEGORY')
                   ,'INFORMATION5'
                   ,xxas_hr_api_transaction_pkg.varchar2_value
                      (pend.person_id
                      ,pend.api_name
                      ,pend.transaction_step_id
                      ,'P_INFORMATION5'))
       END                                       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_INFORMATION6')
       END                                       information6_p
      ,CASE WHEN pend.transaction_id IS NOT NULL
            THEN xxas_com_vset_pkg.decode_descr_vset_value
                   ('800'
                   ,'PER_PDP_DEVELOPER_DF'
                   ,xxas_hr_api_transaction_pkg.varchar2_value
                      (pend.person_id
                      ,pend.api_name
                      ,pend.transaction_step_id
                      ,'P_INFORMATION_CATEGORY')
                   ,'INFORMATION6'
                   ,xxas_hr_api_transaction_pkg.varchar2_value
                      (pend.person_id
                      ,pend.api_name
                      ,pend.transaction_step_id
                      ,'P_INFORMATION6'))
       END                                       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_INFORMATION7')
       END                                       information7_p
      ,CASE WHEN pend.transaction_id IS NOT NULL
            THEN xxas_com_vset_pkg.decode_descr_vset_value
                   ('800'
                   ,'PER_PDP_DEVELOPER_DF'
                   ,xxas_hr_api_transaction_pkg.varchar2_value
                      (pend.person_id
                      ,pend.api_name
                      ,pend.transaction_step_id
                      ,'P_INFORMATION_CATEGORY')
                   ,'INFORMATION7'
                   ,xxas_hr_api_transaction_pkg.varchar2_value
                      (pend.person_id
                      ,pend.api_name
                      ,pend.transaction_step_id
                      ,'P_INFORMATION7'))
       END                                       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_INFORMATION8')
       END                                       information8_p
      ,CASE WHEN pend.transaction_id IS NOT NULL
            THEN xxas_com_vset_pkg.decode_descr_vset_value
                   ('800'
                   ,'PER_PDP_DEVELOPER_DF'
                   ,xxas_hr_api_transaction_pkg.varchar2_value
                      (pend.person_id
                      ,pend.api_name
                      ,pend.transaction_step_id
                      ,'P_INFORMATION_CATEGORY')
                   ,'INFORMATION8'
                   ,xxas_hr_api_transaction_pkg.varchar2_value
                      (pend.person_id
                      ,pend.api_name
                      ,pend.transaction_step_id
                      ,'P_INFORMATION8'))
       END                                       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_INFORMATION9')
       END                                       information9_p
      ,CASE WHEN pend.transaction_id IS NOT NULL
            THEN xxas_com_vset_pkg.decode_descr_vset_value
                   ('800'
                   ,'PER_PDP_DEVELOPER_DF'
                   ,xxas_hr_api_transaction_pkg.varchar2_value
                      (pend.person_id
                      ,pend.api_name
                      ,pend.transaction_step_id
                      ,'P_INFORMATION_CATEGORY')
                   ,'INFORMATION9'
                   ,xxas_hr_api_transaction_pkg.varchar2_value
                      (pend.person_id
                      ,pend.api_name
                      ,pend.transaction_step_id
                      ,'P_INFORMATION9'))
       END                                       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_INFORMATION10')
       END                                       information10_p
      ,CASE WHEN pend.transaction_id IS NOT NULL
            THEN xxas_com_vset_pkg.decode_descr_vset_value
                   ('800'
                   ,'PER_PDP_DEVELOPER_DF'
                   ,xxas_hr_api_transaction_pkg.varchar2_value
                      (pend.person_id
                      ,pend.api_name
                      ,pend.transaction_step_id
                      ,'P_INFORMATION_CATEGORY')
                   ,'INFORMATION10'
                   ,xxas_hr_api_transaction_pkg.varchar2_value
                      (pend.person_id
                      ,pend.api_name
                      ,pend.transaction_step_id
                      ,'P_INFORMATION10'))
       END                                       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_INFORMATION11')
       END                                       information11_p
      ,CASE WHEN pend.transaction_id IS NOT NULL
            THEN xxas_com_vset_pkg.decode_descr_vset_value
                   ('800'
                   ,'PER_PDP_DEVELOPER_DF'
                   ,xxas_hr_api_transaction_pkg.varchar2_value
                      (pend.person_id
                      ,pend.api_name
                      ,pend.transaction_step_id
                      ,'P_INFORMATION_CATEGORY')
                   ,'INFORMATION11'
                   ,xxas_hr_api_transaction_pkg.varchar2_value
                      (pend.person_id
                      ,pend.api_name
                      ,pend.transaction_step_id
                      ,'P_INFORMATION11'))
       END                                       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_INFORMATION12')
       END                                       information12_p
      ,CASE WHEN pend.transaction_id IS NOT NULL
            THEN xxas_com_vset_pkg.decode_descr_vset_value
                   ('800'
                   ,'PER_PDP_DEVELOPER_DF'
                   ,xxas_hr_api_transaction_pkg.varchar2_value
                      (pend.person_id
                      ,pend.api_name
                      ,pend.transaction_step_id
                      ,'P_INFORMATION_CATEGORY')
                   ,'INFORMATION12'
                   ,xxas_hr_api_transaction_pkg.varchar2_value
                      (pend.person_id
                      ,pend.api_name
                      ,pend.transaction_step_id
                      ,'P_INFORMATION12'))
       END                                       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_INFORMATION13')
       END                                       information13_p
      ,CASE WHEN pend.transaction_id IS NOT NULL
            THEN xxas_com_vset_pkg.decode_descr_vset_value
                   ('800'
                   ,'PER_PDP_DEVELOPER_DF'
                   ,xxas_hr_api_transaction_pkg.varchar2_value
                      (pend.person_id
                      ,pend.api_name
                      ,pend.transaction_step_id
                      ,'P_INFORMATION_CATEGORY')
                   ,'INFORMATION13'
                   ,xxas_hr_api_transaction_pkg.varchar2_value
                      (pend.person_id
                      ,pend.api_name
                      ,pend.transaction_step_id
                      ,'P_INFORMATION13'))
       END                                       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_INFORMATION14')
       END                                       information14_p
      ,CASE WHEN pend.transaction_id IS NOT NULL
            THEN xxas_com_vset_pkg.decode_descr_vset_value
                   ('800'
                   ,'PER_PDP_DEVELOPER_DF'
                   ,xxas_hr_api_transaction_pkg.varchar2_value
                      (pend.person_id
                      ,pend.api_name
                      ,pend.transaction_step_id
                      ,'P_INFORMATION_CATEGORY')
                   ,'INFORMATION14'
                   ,xxas_hr_api_transaction_pkg.varchar2_value
                      (pend.person_id
                      ,pend.api_name
                      ,pend.transaction_step_id
                      ,'P_INFORMATION14'))
       END                                       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_INFORMATION15')
       END                                       information15_p
      ,CASE WHEN pend.transaction_id IS NOT NULL
            THEN xxas_com_vset_pkg.decode_descr_vset_value
                   ('800'
                   ,'PER_PDP_DEVELOPER_DF'
                   ,xxas_hr_api_transaction_pkg.varchar2_value
                      (pend.person_id
                      ,pend.api_name
                      ,pend.transaction_step_id
                      ,'P_INFORMATION_CATEGORY')
                   ,'INFORMATION15'
                   ,xxas_hr_api_transaction_pkg.varchar2_value
                      (pend.person_id
                      ,pend.api_name
                      ,pend.transaction_step_id
                      ,'P_INFORMATION15'))
       END                                       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_INFORMATION16')
       END                                       information16_p
      ,CASE WHEN pend.transaction_id IS NOT NULL
            THEN xxas_com_vset_pkg.decode_descr_vset_value
                   ('800'
                   ,'PER_PDP_DEVELOPER_DF'
                   ,xxas_hr_api_transaction_pkg.varchar2_value
                      (pend.person_id
                      ,pend.api_name
                      ,pend.transaction_step_id
                      ,'P_INFORMATION_CATEGORY')
                   ,'INFORMATION16'
                   ,xxas_hr_api_transaction_pkg.varchar2_value
                      (pend.person_id
                      ,pend.api_name
                      ,pend.transaction_step_id
                      ,'P_INFORMATION16'))
       END                                       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_INFORMATION17')
       END                                       information17_p
      ,CASE WHEN pend.transaction_id IS NOT NULL
            THEN xxas_com_vset_pkg.decode_descr_vset_value
                   ('800'
                   ,'PER_PDP_DEVELOPER_DF'
                   ,xxas_hr_api_transaction_pkg.varchar2_value
                      (pend.person_id
                      ,pend.api_name
                      ,pend.transaction_step_id
                      ,'P_INFORMATION_CATEGORY')
                   ,'INFORMATION17'
                   ,xxas_hr_api_transaction_pkg.varchar2_value
                      (pend.person_id
                      ,pend.api_name
                      ,pend.transaction_step_id
                      ,'P_INFORMATION17'))
       END                                       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_INFORMATION18')
       END                                       information18_p
      ,CASE WHEN pend.transaction_id IS NOT NULL
            THEN xxas_com_vset_pkg.decode_descr_vset_value
                   ('800'
                   ,'PER_PDP_DEVELOPER_DF'
                   ,xxas_hr_api_transaction_pkg.varchar2_value
                      (pend.person_id
                      ,pend.api_name
                      ,pend.transaction_step_id
                      ,'P_INFORMATION_CATEGORY')
                   ,'INFORMATION18'
                   ,xxas_hr_api_transaction_pkg.varchar2_value
                      (pend.person_id
                      ,pend.api_name
                      ,pend.transaction_step_id
                      ,'P_INFORMATION18'))
       END                                       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_INFORMATION19')
       END                                       information19_p
      ,CASE WHEN pend.transaction_id IS NOT NULL
            THEN xxas_com_vset_pkg.decode_descr_vset_value
                   ('800'
                   ,'PER_PDP_DEVELOPER_DF'
                   ,xxas_hr_api_transaction_pkg.varchar2_value
                      (pend.person_id
                      ,pend.api_name
                      ,pend.transaction_step_id
                      ,'P_INFORMATION_CATEGORY')
                   ,'INFORMATION19'
                   ,xxas_hr_api_transaction_pkg.varchar2_value
                      (pend.person_id
                      ,pend.api_name
                      ,pend.transaction_step_id
                      ,'P_INFORMATION19'))
       END                                       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_INFORMATION20')
       END                                       information20_p
      ,CASE WHEN pend.transaction_id IS NOT NULL
            THEN xxas_com_vset_pkg.decode_descr_vset_value
                   ('800'
                   ,'PER_PDP_DEVELOPER_DF'
                   ,xxas_hr_api_transaction_pkg.varchar2_value
                      (pend.person_id
                      ,pend.api_name
                      ,pend.transaction_step_id
                      ,'P_INFORMATION_CATEGORY')
                   ,'INFORMATION20'
                   ,xxas_hr_api_transaction_pkg.varchar2_value
                      (pend.person_id
                      ,pend.api_name
                      ,pend.transaction_step_id
                      ,'P_INFORMATION20'))
       END                                       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_INFORMATION21')
       END                                       information21_p
      ,CASE WHEN pend.transaction_id IS NOT NULL
            THEN xxas_com_vset_pkg.decode_descr_vset_value
                   ('800'
                   ,'PER_PDP_DEVELOPER_DF'
                   ,xxas_hr_api_transaction_pkg.varchar2_value
                      (pend.person_id
                      ,pend.api_name
                      ,pend.transaction_step_id
                      ,'P_INFORMATION_CATEGORY')
                   ,'INFORMATION21'
                   ,xxas_hr_api_transaction_pkg.varchar2_value
                      (pend.person_id
                      ,pend.api_name
                      ,pend.transaction_step_id
                      ,'P_INFORMATION21'))
       END                                       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_INFORMATION22')
       END                                       information22_p
      ,CASE WHEN pend.transaction_id IS NOT NULL
            THEN xxas_com_vset_pkg.decode_descr_vset_value
                   ('800'
                   ,'PER_PDP_DEVELOPER_DF'
                   ,xxas_hr_api_transaction_pkg.varchar2_value
                      (pend.person_id
                      ,pend.api_name
                      ,pend.transaction_step_id
                      ,'P_INFORMATION_CATEGORY')
                   ,'INFORMATION22'
                   ,xxas_hr_api_transaction_pkg.varchar2_value
                      (pend.person_id
                      ,pend.api_name
                      ,pend.transaction_step_id
                      ,'P_INFORMATION22'))
       END                                       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_INFORMATION23')
       END                                       information23_p
      ,CASE WHEN pend.transaction_id IS NOT NULL
            THEN xxas_com_vset_pkg.decode_descr_vset_value
                   ('800'
                   ,'PER_PDP_DEVELOPER_DF'
                   ,xxas_hr_api_transaction_pkg.varchar2_value
                      (pend.person_id
                      ,pend.api_name
                      ,pend.transaction_step_id
                      ,'P_INFORMATION_CATEGORY')
                   ,'INFORMATION23'
                   ,xxas_hr_api_transaction_pkg.varchar2_value
                      (pend.person_id
                      ,pend.api_name
                      ,pend.transaction_step_id
                      ,'P_INFORMATION23'))
       END                                       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_INFORMATION24')
       END                                       information24_p
      ,CASE WHEN pend.transaction_id IS NOT NULL
            THEN xxas_com_vset_pkg.decode_descr_vset_value
                   ('800'
                   ,'PER_PDP_DEVELOPER_DF'
                   ,xxas_hr_api_transaction_pkg.varchar2_value
                      (pend.person_id
                      ,pend.api_name
                      ,pend.transaction_step_id
                      ,'P_INFORMATION_CATEGORY')
                   ,'INFORMATION24'
                   ,xxas_hr_api_transaction_pkg.varchar2_value
                      (pend.person_id
                      ,pend.api_name
                      ,pend.transaction_step_id
                      ,'P_INFORMATION24'))
       END                                       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_INFORMATION25')
       END                                       information25_p
      ,CASE WHEN pend.transaction_id IS NOT NULL
            THEN xxas_com_vset_pkg.decode_descr_vset_value
                   ('800'
                   ,'PER_PDP_DEVELOPER_DF'
                   ,xxas_hr_api_transaction_pkg.varchar2_value
                      (pend.person_id
                      ,pend.api_name
                      ,pend.transaction_step_id
                      ,'P_INFORMATION_CATEGORY')
                   ,'INFORMATION25'
                   ,xxas_hr_api_transaction_pkg.varchar2_value
                      (pend.person_id
                      ,pend.api_name
                      ,pend.transaction_step_id
                      ,'P_INFORMATION25'))
       END                                       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_INFORMATION26')
       END                                       information26_p
      ,CASE WHEN pend.transaction_id IS NOT NULL
            THEN xxas_com_vset_pkg.decode_descr_vset_value
                   ('800'
                   ,'PER_PDP_DEVELOPER_DF'
                   ,xxas_hr_api_transaction_pkg.varchar2_value
                      (pend.person_id
                      ,pend.api_name
                      ,pend.transaction_step_id
                      ,'P_INFORMATION_CATEGORY')
                   ,'INFORMATION26'
                   ,xxas_hr_api_transaction_pkg.varchar2_value
                      (pend.person_id
                      ,pend.api_name
                      ,pend.transaction_step_id
                      ,'P_INFORMATION26'))
       END                                       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_INFORMATION27')
       END                                       information27_p
      ,CASE WHEN pend.transaction_id IS NOT NULL
            THEN xxas_com_vset_pkg.decode_descr_vset_value
                   ('800'
                   ,'PER_PDP_DEVELOPER_DF'
                   ,xxas_hr_api_transaction_pkg.varchar2_value
                      (pend.person_id
                      ,pend.api_name
                      ,pend.transaction_step_id
                      ,'P_INFORMATION_CATEGORY')
                   ,'INFORMATION27'
                   ,xxas_hr_api_transaction_pkg.varchar2_value
                      (pend.person_id
                      ,pend.api_name
                      ,pend.transaction_step_id
                      ,'P_INFORMATION27'))
       END                                       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_INFORMATION28')
       END                                       information28_p
      ,CASE WHEN pend.transaction_id IS NOT NULL
            THEN xxas_com_vset_pkg.decode_descr_vset_value
                   ('800'
                   ,'PER_PDP_DEVELOPER_DF'
                   ,xxas_hr_api_transaction_pkg.varchar2_value
                      (pend.person_id
                      ,pend.api_name
                      ,pend.transaction_step_id
                      ,'P_INFORMATION_CATEGORY')
                   ,'INFORMATION28'
                   ,xxas_hr_api_transaction_pkg.varchar2_value
                      (pend.person_id
                      ,pend.api_name
                      ,pend.transaction_step_id
                      ,'P_INFORMATION28'))
       END                                       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_INFORMATION29')
       END                                       information29_p
      ,CASE WHEN pend.transaction_id IS NOT NULL
            THEN xxas_com_vset_pkg.decode_descr_vset_value
                   ('800'
                   ,'PER_PDP_DEVELOPER_DF'
                   ,xxas_hr_api_transaction_pkg.varchar2_value
                      (pend.person_id
                      ,pend.api_name
                      ,pend.transaction_step_id
                      ,'P_INFORMATION_CATEGORY')
                   ,'INFORMATION29'
                   ,xxas_hr_api_transaction_pkg.varchar2_value
                      (pend.person_id
                      ,pend.api_name
                      ,pend.transaction_step_id
                      ,'P_INFORMATION29'))
       END                                       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_INFORMATION30')
       END                                       information30_p
      ,CASE WHEN pend.transaction_id IS NOT NULL
            THEN xxas_com_vset_pkg.decode_descr_vset_value
                   ('800'
                   ,'PER_PDP_DEVELOPER_DF'
                   ,xxas_hr_api_transaction_pkg.varchar2_value
                      (pend.person_id
                      ,pend.api_name
                      ,pend.transaction_step_id
                      ,'P_INFORMATION_CATEGORY')
                   ,'INFORMATION30'
                   ,xxas_hr_api_transaction_pkg.varchar2_value
                      (pend.person_id
                      ,pend.api_name
                      ,pend.transaction_step_id
                      ,'P_INFORMATION30'))
       END                                       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_placement ppp
      ,per_all_people_f         ppf
      ,per_all_assignments_f    paaf
      ,(SELECT ppp.person_id
              ,ppp.period_of_placement_id
              ,hats.api_name
              ,hat.transaction_id
              ,hats.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_placement    ppp
              ,hr_api_transactions         hat
              ,hr_api_transaction_steps    hats
              ,hr_api_transaction_values   act
        WHERE  ppp.person_id            = hat.selected_person_id
        AND    hat.transaction_id       = hats.transaction_id
        AND    hat.status          NOT IN ('D', 'N', 'W', 'ACTIVE', 'AC')
        AND    upper(hats.api_name)     = 'HR_CWK_TERMINATION_SWI.PROCESS_API'
        AND    hats.transaction_step_id = act.transaction_step_id
        AND    act.name                 = 'P_ACTIVITY_NAME'
        AND    act.varchar2_value       = 'HR_CWK_TERMINATION_SWI'
        ) pend
WHERE  ppp.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_placement_date_start = ppp.date_start
AND    NVL(ppf.current_npw_flag,'N') = 'Y'
AND    ppp.person_id                    = pend.person_id(+)
AND    ppp.period_of_placement_id       = pend.period_of_placement_id(+)
AND    nvl(ppp.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_CWK_LEAVER_REQUEST_V'
   ,p_description            => 'Applaud CWK Leaver Request View');
END;
/

COMMIT;
EXIT;

