/* $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_change_asg_review_v 
AS
SELECT paaf.assignment_id
      ,paaf.object_version_number
      ,null effective_from_ldw
      ,trunc(paaf.effective_start_date) effective_start_date
      ,trunc(paaf.effective_end_date)   effective_end_date
      --,null approvers
      ,xxas_mss_util_pkg.has_future_dated_records(paaf.assignment_id,paaf.effective_end_date) has_future_dated_records
      ,null effective_date_option
      ,null element_changed
      ,null datetrack_update_mode
      ,paaf.business_group_id
      ,paaf.person_id
      ,fnd_global.employee_id login_person_id
      ,xxas_mss_util_pkg.is_contingent_worker(paaf.person_id,paaf.assignment_id) contingent_worker
      ,paaf.assignment_status_type_id asg_status
      ,paaf.assignment_type
      ,paaf.primary_flag
      ,paaf.organization_id
      ,paaf.job_id
      ,paaf.position_id
      ,paaf.grade_id
      ,paaf.location_id
      ,paaf.employment_category asg_category
      ,paaf.supervisor_id
      ,paaf.manager_flag
      ,paaf.normal_hours
      ,paaf.frequency
      ,paaf.time_normal_start
      ,paaf.time_normal_finish
      ,paaf.change_reason
      ,paaf.bargaining_unit_code
      ,paaf.labour_union_member_flag
      ,paaf.special_ceiling_step_id
      ,paaf.people_group_id
      ,paaf.soft_coding_keyflex_id
      ,paaf.payroll_id
      ,paaf.pay_basis_id
      ,paaf.sal_review_period
      ,paaf.sal_review_period_frequency
      ,paaf.perf_review_period
      ,paaf.perf_review_period_frequency
      ,paaf.date_probation_end
      ,paaf.probation_period
      ,paaf.probation_unit
      ,paaf.notice_period
      ,paaf.notice_period_uom
      ,paaf.employee_category
      ,paaf.work_at_home
      ,paaf.job_post_source_name
      ,paaf.internal_address_line
      ,paaf.contract_id
      ,paaf.establishment_id
      ,paaf.collective_agreement_id
      ,paaf.cagr_id_flex_num
      ,paaf.cagr_grade_def_id
      ,paaf.default_code_comb_id
      ,paaf.set_of_books_id
      ,paaf.vendor_id
      ,paaf.title
      ,paaf.project_title
      ,paaf.source_type
      ,paaf.vendor_assignment_number
      ,paaf.vendor_employee_number
      ,paaf.ass_attribute_category
      ,paaf.ass_attribute1
      ,xxas_com_vset_pkg.decode_descr_vset_value
         ('800'
         ,'PER_ALL_ASSIGNMENTS_F'
         ,paaf.ass_attribute_category
         ,'ASS_ATTRIBUTE1'
         ,paaf.ass_attribute1)                    ass_attribute1_meaning
      ,paaf.ass_attribute2
      ,xxas_com_vset_pkg.decode_descr_vset_value
         ('800'
         ,'PER_ALL_ASSIGNMENTS_F'
         ,paaf.ass_attribute_category
         ,'ASS_ATTRIBUTE2'
         ,paaf.ass_attribute2)                    ass_attribute2_meaning
      ,paaf.ass_attribute3
      ,xxas_com_vset_pkg.decode_descr_vset_value
         ('800'
         ,'PER_ALL_ASSIGNMENTS_F'
         ,paaf.ass_attribute_category
         ,'ASS_ATTRIBUTE3'
         ,paaf.ass_attribute3)                    ass_attribute3_meaning
      ,paaf.ass_attribute4
      ,xxas_com_vset_pkg.decode_descr_vset_value
         ('800'
         ,'PER_ALL_ASSIGNMENTS_F'
         ,paaf.ass_attribute_category
         ,'ASS_ATTRIBUTE4'
         ,paaf.ass_attribute4)                    ass_attribute4_meaning
      ,paaf.ass_attribute5
      ,xxas_com_vset_pkg.decode_descr_vset_value
         ('800'
         ,'PER_ALL_ASSIGNMENTS_F'
         ,paaf.ass_attribute_category
         ,'ASS_ATTRIBUTE5'
         ,paaf.ass_attribute5)                    ass_attribute5_meaning
      ,paaf.ass_attribute6
      ,xxas_com_vset_pkg.decode_descr_vset_value
         ('800'
         ,'PER_ALL_ASSIGNMENTS_F'
         ,paaf.ass_attribute_category
         ,'ASS_ATTRIBUTE6'
         ,paaf.ass_attribute6)                    ass_attribute6_meaning
      ,paaf.ass_attribute7
      ,xxas_com_vset_pkg.decode_descr_vset_value
         ('800'
         ,'PER_ALL_ASSIGNMENTS_F'
         ,paaf.ass_attribute_category
         ,'ASS_ATTRIBUTE7'
         ,paaf.ass_attribute7)                    ass_attribute7_meaning
      ,paaf.ass_attribute8
      ,xxas_com_vset_pkg.decode_descr_vset_value
         ('800'
         ,'PER_ALL_ASSIGNMENTS_F'
         ,paaf.ass_attribute_category
         ,'ASS_ATTRIBUTE8'
         ,paaf.ass_attribute8)                    ass_attribute8_meaning
      ,paaf.ass_attribute9
      ,xxas_com_vset_pkg.decode_descr_vset_value
         ('800'
         ,'PER_ALL_ASSIGNMENTS_F'
         ,paaf.ass_attribute_category
         ,'ASS_ATTRIBUTE9'
         ,paaf.ass_attribute9)                    ass_attribute9_meaning
      ,paaf.ass_attribute10
      ,xxas_com_vset_pkg.decode_descr_vset_value
         ('800'
         ,'PER_ALL_ASSIGNMENTS_F'
         ,paaf.ass_attribute_category
         ,'ASS_ATTRIBUTE10'
         ,paaf.ass_attribute10)                   ass_attribute10_meaning
      ,paaf.ass_attribute11
      ,xxas_com_vset_pkg.decode_descr_vset_value
         ('800'
         ,'PER_ALL_ASSIGNMENTS_F'
         ,paaf.ass_attribute_category
         ,'ASS_ATTRIBUTE11'
         ,paaf.ass_attribute11)                   ass_attribute11_meaning
      ,paaf.ass_attribute12
      ,xxas_com_vset_pkg.decode_descr_vset_value
         ('800'
         ,'PER_ALL_ASSIGNMENTS_F'
         ,paaf.ass_attribute_category
         ,'ASS_ATTRIBUTE12'
         ,paaf.ass_attribute12)                   ass_attribute12_meaning
      ,paaf.ass_attribute13
      ,xxas_com_vset_pkg.decode_descr_vset_value
         ('800'
         ,'PER_ALL_ASSIGNMENTS_F'
         ,paaf.ass_attribute_category
         ,'ASS_ATTRIBUTE13'
         ,paaf.ass_attribute13)                   ass_attribute13_meaning
      ,paaf.ass_attribute14
      ,xxas_com_vset_pkg.decode_descr_vset_value
         ('800'
         ,'PER_ALL_ASSIGNMENTS_F'
         ,paaf.ass_attribute_category
         ,'ASS_ATTRIBUTE14'
         ,paaf.ass_attribute14)                   ass_attribute14_meaning
      ,paaf.ass_attribute15
      ,xxas_com_vset_pkg.decode_descr_vset_value
         ('800'
         ,'PER_ALL_ASSIGNMENTS_F'
         ,paaf.ass_attribute_category
         ,'ASS_ATTRIBUTE15'
         ,paaf.ass_attribute15)                   ass_attribute15_meaning
      ,paaf.ass_attribute16
      ,xxas_com_vset_pkg.decode_descr_vset_value
         ('800'
         ,'PER_ALL_ASSIGNMENTS_F'
         ,paaf.ass_attribute_category
         ,'ASS_ATTRIBUTE16'
         ,paaf.ass_attribute16)                   ass_attribute16_meaning
      ,paaf.ass_attribute17
      ,xxas_com_vset_pkg.decode_descr_vset_value
         ('800'
         ,'PER_ALL_ASSIGNMENTS_F'
         ,paaf.ass_attribute_category
         ,'ASS_ATTRIBUTE17'
         ,paaf.ass_attribute17)                   ass_attribute17_meaning
      ,paaf.ass_attribute18
      ,xxas_com_vset_pkg.decode_descr_vset_value
         ('800'
         ,'PER_ALL_ASSIGNMENTS_F'
         ,paaf.ass_attribute_category
         ,'ASS_ATTRIBUTE18'
         ,paaf.ass_attribute18)                   ass_attribute18_meaning
      ,paaf.ass_attribute19
      ,xxas_com_vset_pkg.decode_descr_vset_value
         ('800'
         ,'PER_ALL_ASSIGNMENTS_F'
         ,paaf.ass_attribute_category
         ,'ASS_ATTRIBUTE19'
         ,paaf.ass_attribute19)                   ass_attribute19_meaning
      ,paaf.ass_attribute20
      ,xxas_com_vset_pkg.decode_descr_vset_value
         ('800'
         ,'PER_ALL_ASSIGNMENTS_F'
         ,paaf.ass_attribute_category
         ,'ASS_ATTRIBUTE20'
         ,paaf.ass_attribute20)                   ass_attribute20_meaning
      ,paaf.ass_attribute21
      ,xxas_com_vset_pkg.decode_descr_vset_value
         ('800'
         ,'PER_ALL_ASSIGNMENTS_F'
         ,paaf.ass_attribute_category
         ,'ASS_ATTRIBUTE21'
         ,paaf.ass_attribute21)                   ass_attribute21_meaning
      ,paaf.ass_attribute22
      ,xxas_com_vset_pkg.decode_descr_vset_value
         ('800'
         ,'PER_ALL_ASSIGNMENTS_F'
         ,paaf.ass_attribute_category
         ,'ASS_ATTRIBUTE22'
         ,paaf.ass_attribute22)                   ass_attribute22_meaning
      ,paaf.ass_attribute23
      ,xxas_com_vset_pkg.decode_descr_vset_value
         ('800'
         ,'PER_ALL_ASSIGNMENTS_F'
         ,paaf.ass_attribute_category
         ,'ASS_ATTRIBUTE23'
         ,paaf.ass_attribute23)                   ass_attribute23_meaning
      ,paaf.ass_attribute24
      ,xxas_com_vset_pkg.decode_descr_vset_value
         ('800'
         ,'PER_ALL_ASSIGNMENTS_F'
         ,paaf.ass_attribute_category
         ,'ASS_ATTRIBUTE24'
         ,paaf.ass_attribute24)                   ass_attribute24_meaning
      ,paaf.ass_attribute25
      ,xxas_com_vset_pkg.decode_descr_vset_value
         ('800'
         ,'PER_ALL_ASSIGNMENTS_F'
         ,paaf.ass_attribute_category
         ,'ASS_ATTRIBUTE25'
         ,paaf.ass_attribute25)                   ass_attribute25_meaning
      ,paaf.ass_attribute26
      ,xxas_com_vset_pkg.decode_descr_vset_value
         ('800'
         ,'PER_ALL_ASSIGNMENTS_F'
         ,paaf.ass_attribute_category
         ,'ASS_ATTRIBUTE26'
         ,paaf.ass_attribute26)                   ass_attribute26_meaning
      ,paaf.ass_attribute27
      ,xxas_com_vset_pkg.decode_descr_vset_value
         ('800'
         ,'PER_ALL_ASSIGNMENTS_F'
         ,paaf.ass_attribute_category
         ,'ASS_ATTRIBUTE27'
         ,paaf.ass_attribute27)                   ass_attribute27_meaning
      ,paaf.ass_attribute28
      ,xxas_com_vset_pkg.decode_descr_vset_value
         ('800'
         ,'PER_ALL_ASSIGNMENTS_F'
         ,paaf.ass_attribute_category
         ,'ASS_ATTRIBUTE28'
         ,paaf.ass_attribute28)                   ass_attribute28_meaning
      ,paaf.ass_attribute29
      ,xxas_com_vset_pkg.decode_descr_vset_value
         ('800'
         ,'PER_ALL_ASSIGNMENTS_F'
         ,paaf.ass_attribute_category
         ,'ASS_ATTRIBUTE29'
         ,paaf.ass_attribute29)                   ass_attribute29_meaning
      ,paaf.ass_attribute30
      ,xxas_com_vset_pkg.decode_descr_vset_value
         ('800'
         ,'PER_ALL_ASSIGNMENTS_F'
         ,paaf.ass_attribute_category
         ,'ASS_ATTRIBUTE30'
         ,paaf.ass_attribute30)                   ass_attribute30_meaning
      ,pgkf.pg_segment1
      ,pgkf.pg_segment1_value
      ,pgkf.pg_segment1_meaning
      ,pgkf.pg_segment2
      ,pgkf.pg_segment2_value
      ,pgkf.pg_segment2_meaning
      ,pgkf.pg_segment3
      ,pgkf.pg_segment3_value
      ,pgkf.pg_segment3_meaning
      ,pgkf.pg_segment4
      ,pgkf.pg_segment4_value
      ,pgkf.pg_segment4_meaning
      ,pgkf.pg_segment5
      ,pgkf.pg_segment5_value
      ,pgkf.pg_segment5_meaning
      ,pgkf.pg_segment6
      ,pgkf.pg_segment6_value
      ,pgkf.pg_segment6_meaning
      ,pgkf.pg_segment7
      ,pgkf.pg_segment7_value
      ,pgkf.pg_segment7_meaning
      ,pgkf.pg_segment8
      ,pgkf.pg_segment8_value
      ,pgkf.pg_segment8_meaning
      ,pgkf.pg_segment9
      ,pgkf.pg_segment9_value
      ,pgkf.pg_segment9_meaning
      ,pgkf.pg_segment10
      ,pgkf.pg_segment10_value
      ,pgkf.pg_segment10_meaning
      ,pgkf.pg_segment11
      ,pgkf.pg_segment11_value
      ,pgkf.pg_segment11_meaning
      ,pgkf.pg_segment12
      ,pgkf.pg_segment12_value
      ,pgkf.pg_segment12_meaning
      ,pgkf.pg_segment13
      ,pgkf.pg_segment13_value
      ,pgkf.pg_segment13_meaning
      ,pgkf.pg_segment14
      ,pgkf.pg_segment14_value
      ,pgkf.pg_segment14_meaning
      ,pgkf.pg_segment15
      ,pgkf.pg_segment15_value
      ,pgkf.pg_segment15_meaning
      ,pgkf.pg_segment16
      ,pgkf.pg_segment16_value
      ,pgkf.pg_segment16_meaning
      ,pgkf.pg_segment17
      ,pgkf.pg_segment17_value
      ,pgkf.pg_segment17_meaning
      ,pgkf.pg_segment18
      ,pgkf.pg_segment18_value
      ,pgkf.pg_segment18_meaning
      ,pgkf.pg_segment19
      ,pgkf.pg_segment19_value
      ,pgkf.pg_segment19_meaning
      ,pgkf.pg_segment20
      ,pgkf.pg_segment20_value
      ,pgkf.pg_segment20_meaning
      ,pgkf.pg_segment21
      ,pgkf.pg_segment21_value
      ,pgkf.pg_segment21_meaning
      ,pgkf.pg_segment22
      ,pgkf.pg_segment22_value
      ,pgkf.pg_segment22_meaning
      ,pgkf.pg_segment23
      ,pgkf.pg_segment23_value
      ,pgkf.pg_segment23_meaning
      ,pgkf.pg_segment24
      ,pgkf.pg_segment24_value
      ,pgkf.pg_segment24_meaning
      ,pgkf.pg_segment25
      ,pgkf.pg_segment25_value
      ,pgkf.pg_segment25_meaning
      ,pgkf.pg_segment26
      ,pgkf.pg_segment26_value
      ,pgkf.pg_segment26_meaning
      ,pgkf.pg_segment27
      ,pgkf.pg_segment27_value
      ,pgkf.pg_segment27_meaning
      ,pgkf.pg_segment28
      ,pgkf.pg_segment28_value
      ,pgkf.pg_segment28_meaning
      ,pgkf.pg_segment29
      ,pgkf.pg_segment29_value
      ,pgkf.pg_segment29_meaning
      ,pgkf.pg_segment30
      ,pgkf.pg_segment30_value
      ,pgkf.pg_segment30_meaning
      ,stkf.st_segment1
      ,stkf.st_segment1_value
      ,stkf.st_segment1_meaning
      ,stkf.st_segment2
      ,stkf.st_segment2_value
      ,stkf.st_segment2_meaning
      ,stkf.st_segment3
      ,stkf.st_segment3_value
      ,stkf.st_segment3_meaning
      ,stkf.st_segment4
      ,stkf.st_segment4_value
      ,stkf.st_segment4_meaning
      ,stkf.st_segment5
      ,stkf.st_segment5_value
      ,stkf.st_segment5_meaning
      ,stkf.st_segment6
      ,stkf.st_segment6_value
      ,stkf.st_segment6_meaning
      ,stkf.st_segment7
      ,stkf.st_segment7_value
      ,stkf.st_segment7_meaning
      ,stkf.st_segment8
      ,stkf.st_segment8_value
      ,stkf.st_segment8_meaning
      ,stkf.st_segment9
      ,stkf.st_segment9_value
      ,stkf.st_segment9_meaning
      ,stkf.st_segment10
      ,stkf.st_segment10_value
      ,stkf.st_segment10_meaning
      ,stkf.st_segment11
      ,stkf.st_segment11_value
      ,stkf.st_segment11_meaning
      ,stkf.st_segment12
      ,stkf.st_segment12_value
      ,stkf.st_segment12_meaning
      ,stkf.st_segment13
      ,stkf.st_segment13_value
      ,stkf.st_segment13_meaning
      ,stkf.st_segment14
      ,stkf.st_segment14_value
      ,stkf.st_segment14_meaning
      ,stkf.st_segment15
      ,stkf.st_segment15_value
      ,stkf.st_segment15_meaning
      ,stkf.st_segment16
      ,stkf.st_segment16_value
      ,stkf.st_segment16_meaning
      ,stkf.st_segment17
      ,stkf.st_segment17_value
      ,stkf.st_segment17_meaning
      ,stkf.st_segment18
      ,stkf.st_segment18_value
      ,stkf.st_segment18_meaning
      ,stkf.st_segment19
      ,stkf.st_segment19_value
      ,stkf.st_segment19_meaning
      ,stkf.st_segment20
      ,stkf.st_segment20_value
      ,stkf.st_segment20_meaning
      ,stkf.st_segment21
      ,stkf.st_segment21_value
      ,stkf.st_segment21_meaning
      ,stkf.st_segment22
      ,stkf.st_segment22_value
      ,stkf.st_segment22_meaning
      ,stkf.st_segment23
      ,stkf.st_segment23_value
      ,stkf.st_segment23_meaning
      ,stkf.st_segment24
      ,stkf.st_segment24_value
      ,stkf.st_segment24_meaning
      ,stkf.st_segment25
      ,stkf.st_segment25_value
      ,stkf.st_segment25_meaning
      ,stkf.st_segment26
      ,stkf.st_segment26_value
      ,stkf.st_segment26_meaning
      ,stkf.st_segment27
      ,stkf.st_segment27_value
      ,stkf.st_segment27_meaning
      ,stkf.st_segment28
      ,stkf.st_segment28_value
      ,stkf.st_segment28_meaning
      ,stkf.st_segment29
      ,stkf.st_segment29_value
      ,stkf.st_segment29_meaning
      ,stkf.st_segment30
      ,stkf.st_segment30_value
      ,stkf.st_segment30_meaning
      ,cakf.cagr_segment1
      ,cakf.cagr_segment1_value
      ,cakf.cagr_segment1_meaning
      ,cakf.cagr_segment2
      ,cakf.cagr_segment2_value
      ,cakf.cagr_segment2_meaning
      ,cakf.cagr_segment3
      ,cakf.cagr_segment3_value
      ,cakf.cagr_segment3_meaning
      ,cakf.cagr_segment4
      ,cakf.cagr_segment4_value
      ,cakf.cagr_segment4_meaning
      ,cakf.cagr_segment5
      ,cakf.cagr_segment5_value
      ,cakf.cagr_segment5_meaning
      ,cakf.cagr_segment6
      ,cakf.cagr_segment6_value
      ,cakf.cagr_segment6_meaning
      ,cakf.cagr_segment7
      ,cakf.cagr_segment7_value
      ,cakf.cagr_segment7_meaning
      ,cakf.cagr_segment8
      ,cakf.cagr_segment8_value
      ,cakf.cagr_segment8_meaning
      ,cakf.cagr_segment9
      ,cakf.cagr_segment9_value
      ,cakf.cagr_segment9_meaning
      ,cakf.cagr_segment10
      ,cakf.cagr_segment10_value
      ,cakf.cagr_segment10_meaning
      ,cakf.cagr_segment11
      ,cakf.cagr_segment11_value
      ,cakf.cagr_segment11_meaning
      ,cakf.cagr_segment12
      ,cakf.cagr_segment12_value
      ,cakf.cagr_segment12_meaning
      ,cakf.cagr_segment13
      ,cakf.cagr_segment13_value
      ,cakf.cagr_segment13_meaning
      ,cakf.cagr_segment14
      ,cakf.cagr_segment14_value
      ,cakf.cagr_segment14_meaning
      ,cakf.cagr_segment15
      ,cakf.cagr_segment15_value
      ,cakf.cagr_segment15_meaning
      ,cakf.cagr_segment16
      ,cakf.cagr_segment16_value
      ,cakf.cagr_segment16_meaning
      ,cakf.cagr_segment17
      ,cakf.cagr_segment17_value
      ,cakf.cagr_segment17_meaning
      ,cakf.cagr_segment18
      ,cakf.cagr_segment18_value
      ,cakf.cagr_segment18_meaning
      ,cakf.cagr_segment19
      ,cakf.cagr_segment19_value
      ,cakf.cagr_segment19_meaning
      ,cakf.cagr_segment20
      ,cakf.cagr_segment20_value
      ,cakf.cagr_segment20_meaning
      ,'GRP'                    pg_key_ff_code
      ,xxas_mss_util_pkg.pg_key_ff_structure_code(paaf.organization_id,'GRP',xxas_util_pkg.get_app_id('PAY')) pg_key_ff_st_code
      ,'SCL'                    st_key_ff_code
      ,xxas_mss_util_pkg.scl_key_ff_structure_code(xxas_util_pkg.get_leg_code_from_bg(paaf.business_group_id),'SCL',xxas_util_pkg.get_app_id('PER')) st_key_ff_st_code
      ,'CAGR'                   cagr_key_ff_code
      ,xxas_mss_util_pkg.cagr_key_ff_grade_type(paaf.cagr_id_flex_num,'CAGR',xxas_util_pkg.get_app_id('PER')) cagr_grade_type
      ,null j_changed
      ,null hrs_last_date
      ,null display_pos
      ,null display_org
      ,null display_job
      ,null display_ass_status
      ,null display_grade
      ,null grade_lov
      ,null approver_id
      ,paaf.grade_ladder_pgm_id
      ,paaf.po_header_id
      ,paaf.po_line_id
      ,paaf.vendor_site_id
      ,paaf.projected_assignment_end
      ,paaf.last_update_date
      ,paaf.last_updated_by
      ,paaf.last_update_login
      ,paaf.created_by
      ,paaf.creation_date
      ,null                                       comments_to_approver
      ,CASE WHEN pend.transaction_id IS NOT NULL
            THEN xxas_hr_api_transaction_pkg.number_value
                   (pend.person_id
                   ,pend.api_name
                   ,pend.transaction_step_id
                   ,'P_ASSIGNMENT_ID')
       END                                        assignment_id_p
      ,CASE WHEN pend.transaction_id IS NOT NULL
            THEN xxas_hr_api_transaction_pkg.number_value
                   (pend.person_id
                   ,pend.api_name
                   ,pend.transaction_step_id
                   ,'P_OBJECT_VERSION_NUMBER')
       END                                        object_version_number_p
      ,CASE WHEN pend.transaction_id IS NOT NULL
            THEN xxas_hr_api_transaction_pkg.date_value
                   (pend.person_id
                   ,pend.api_name
                   ,pend.transaction_step_id
                   ,'P_EFFECTIVE_DATE')
       END                                        effective_from_ldw_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_EFFECTIVE_DATE_OPTION')
       END                                        effective_date_option_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_ELEMENT_CHANGED')
       END                                        element_changed_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_DATETRACK_UPDATE_MODE')
       END                                        datetrack_update_mode_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 xxas_hr_api_transaction_pkg.number_value
                   (pend.person_id
                   ,pend.api_name
                   ,pend.transaction_step_id
                   ,'P_PERSON_ID')
       END                                        person_id_p
      ,CASE WHEN pend.transaction_id IS NOT NULL
            THEN xxas_hr_api_transaction_pkg.number_value
                   (pend.person_id
                   ,pend.api_name
                   ,pend.transaction_step_id
                   ,'P_LOGIN_PERSON_ID')
       END                                        login_person_id_p
      ,xxas_mss_util_pkg.is_contingent_worker(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_ID')
                                              END
                                             ,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_ID')
                                              END) contingent_worker_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_ASSIGNMENT_TYPE')
       END                                        assignment_type_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_PRIMARY_FLAG')
       END                                        primary_flag_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_ORGANIZATION_ID')
       END                                        organization_id_p
      ,CASE WHEN pend.transaction_id IS NOT NULL
            THEN xxas_hr_api_transaction_pkg.number_value
                   (pend.person_id
                   ,pend.api_name
                   ,pend.transaction_step_id
                   ,'P_JOB_ID')
       END                                        job_id_p
      ,CASE WHEN pend.transaction_id IS NOT NULL
            THEN xxas_hr_api_transaction_pkg.number_value
                   (pend.person_id
                   ,pend.api_name
                   ,pend.transaction_step_id
                   ,'P_POSITION_ID')
       END                                        position_id_p
      ,CASE WHEN pend.transaction_id IS NOT NULL
            THEN xxas_hr_api_transaction_pkg.number_value
                   (pend.person_id
                   ,pend.api_name
                   ,pend.transaction_step_id
                   ,'P_GRADE_ID')
       END                                        grade_id_p
      ,CASE WHEN pend.transaction_id IS NOT NULL
            THEN xxas_hr_api_transaction_pkg.number_value
                   (pend.person_id
                   ,pend.api_name
                   ,pend.transaction_step_id
                   ,'P_LOCATION_ID')
       END                                        location_id_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_EMPLOYMENT_CATEGORY')
       END                                        asg_category_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_SUPERVISOR_ID')
       END                                        supervisor_id_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_MANAGER_FLAG')
       END                                        manager_flag_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_NORMAL_HOURS')
       END                                        normal_hours_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_FREQUENCY')
       END                                        frequency_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_TIME_NORMAL_START')
       END                                        time_normal_start_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_TIME_NORMAL_FINISH')
       END                                        time_normal_finish_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_CHANGE_REASON')
       END                                        change_reason_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_BARGAINING_UNIT_CODE')
       END                                        bargaining_unit_code_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_LABOUR_UNION_MEMBER_FLAG')
       END                                        labour_union_member_flag_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_SPECIAL_CEILING_STEP_ID')
       END                                        special_ceiling_step_id_p
      ,CASE WHEN pend.transaction_id IS NOT NULL
            THEN xxas_hr_api_transaction_pkg.number_value
                   (pend.person_id
                   ,pend.api_name
                   ,pend.transaction_step_id
                   ,'P_PEOPLE_GROUP_ID')
       END                                        people_group_id_p
      ,CASE WHEN pend.transaction_id IS NOT NULL
            THEN xxas_hr_api_transaction_pkg.number_value
                   (pend.person_id
                   ,pend.api_name
                   ,pend.transaction_step_id
                   ,'P_SOFT_CODING_KEYFLEX_ID')
       END                                        soft_coding_keyflex_id_p
      ,CASE WHEN pend.transaction_id IS NOT NULL
            THEN xxas_hr_api_transaction_pkg.number_value
                   (pend.person_id
                   ,pend.api_name
                   ,pend.transaction_step_id
                   ,'P_PAYROLL_ID')
       END                                        payroll_id_p
      ,CASE WHEN pend.transaction_id IS NOT NULL
            THEN xxas_hr_api_transaction_pkg.number_value
                   (pend.person_id
                   ,pend.api_name
                   ,pend.transaction_step_id
                   ,'P_PAY_BASIS_ID')
       END                                        pay_basis_id_p
      ,CASE WHEN pend.transaction_id IS NOT NULL
            THEN xxas_hr_api_transaction_pkg.number_value
                   (pend.person_id
                   ,pend.api_name
                   ,pend.transaction_step_id
                   ,'P_SAL_REVIEW_PERIOD')
       END                                        sal_review_period_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_SAL_REVIEW_PERIOD_FREQUENCY')
       END                                        sal_review_period_frequency_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_PERF_REVIEW_PERIOD')
       END                                        perf_review_period_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_PERF_REVIEW_PERIOD_FREQUENCY')
       END                                        perf_review_period_frequency_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_PROBATION_END')
       END                                        date_probation_end_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_PROBATION_PERIOD')
       END                                        probation_period_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_PROBATION_UNIT')
       END                                        probation_unit_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_NOTICE_PERIOD')
       END                                        notice_period_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_NOTICE_PERIOD_UOM')
       END                                        notice_period_uom_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_EMPLOYEE_CATEGORY')
       END                                        employee_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_WORK_AT_HOME')
       END                                        work_at_home_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_JOB_POST_SOURCE_NAME')
       END                                        job_post_source_name_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_INTERNAL_ADDRESS_LINE')
       END                                        internal_address_line_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_CONTRACT_ID')
       END                                        contract_id_p
      ,CASE WHEN pend.transaction_id IS NOT NULL
            THEN xxas_hr_api_transaction_pkg.number_value
                   (pend.person_id
                   ,pend.api_name
                   ,pend.transaction_step_id
                   ,'P_ESTABLISHMENT_ID')
       END                                        establishment_id_p
      ,CASE WHEN pend.transaction_id IS NOT NULL
            THEN xxas_hr_api_transaction_pkg.number_value
                   (pend.person_id
                   ,pend.api_name
                   ,pend.transaction_step_id
                   ,'P_COLLECTIVE_AGREEMENT_ID')
       END                                        collective_agreement_id_p
      ,CASE WHEN pend.transaction_id IS NOT NULL
            THEN xxas_hr_api_transaction_pkg.number_value
                   (pend.person_id
                   ,pend.api_name
                   ,pend.transaction_step_id
                   ,'P_CAGR_ID_FLEX_NUM')
       END                                        cagr_id_flex_num_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_CAGR_GRADE_DEF_ID')
       END                                        cagr_grade_def_id_p
      ,CASE WHEN pend.transaction_id IS NOT NULL
            THEN xxas_hr_api_transaction_pkg.number_value
                   (pend.person_id
                   ,pend.api_name
                   ,pend.transaction_step_id
                   ,'P_DEFAULT_CODE_COMB_ID')
       END                                        default_code_comb_id_p
      ,CASE WHEN pend.transaction_id IS NOT NULL
            THEN xxas_hr_api_transaction_pkg.number_value
                   (pend.person_id
                   ,pend.api_name
                   ,pend.transaction_step_id
                   ,'P_SET_OF_BOOKS_ID')
       END                                        set_of_books_id_p
      ,CASE WHEN pend.transaction_id IS NOT NULL
            THEN xxas_hr_api_transaction_pkg.number_value
                   (pend.person_id
                   ,pend.api_name
                   ,pend.transaction_step_id
                   ,'P_VENDOR_ID')
       END                                        vendor_id_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_TITLE')
       END                                        title_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_PROJECT_TITLE')
       END                                        project_title_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_SOURCE_TYPE')
       END                                        source_type_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_VENDOR_ASSIGNMENT_NUMBER')
       END                                        vendor_assignment_number_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_VENDOR_EMPLOYEE_NUMBER')
       END                                        vendor_employee_number_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_ASS_ATTRIBUTE_CATEGORY')
       END                                        ass_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_ASS_ATTRIBUTE1')
       END                                        ass_attribute1_p
      ,CASE WHEN pend.transaction_id IS NOT NULL
            THEN xxas_com_vset_pkg.decode_descr_vset_value
                   ('800'
                   ,'PER_ALL_ASSIGNMENTS_F'
                   ,xxas_hr_api_transaction_pkg.varchar2_value
                      (pend.person_id
                      ,pend.api_name
                      ,pend.transaction_step_id
                      ,'P_ASS_ATTRIBUTE_CATEGORY')
                   ,'ASS_ATTRIBUTE1'
                   ,xxas_hr_api_transaction_pkg.varchar2_value
                      (pend.person_id
                      ,pend.api_name
                      ,pend.transaction_step_id
                      ,'P_ASS_ATTRIBUTE1'))
       END                                        ass_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_ASS_ATTRIBUTE2')
       END                                        ass_attribute2_p
      ,CASE WHEN pend.transaction_id IS NOT NULL
            THEN xxas_com_vset_pkg.decode_descr_vset_value
                   ('800'
                   ,'PER_ALL_ASSIGNMENTS_F'
                   ,xxas_hr_api_transaction_pkg.varchar2_value
                      (pend.person_id
                      ,pend.api_name
                      ,pend.transaction_step_id
                      ,'P_ASS_ATTRIBUTE_CATEGORY')
                   ,'ASS_ATTRIBUTE2'
                   ,xxas_hr_api_transaction_pkg.varchar2_value
                      (pend.person_id
                      ,pend.api_name
                      ,pend.transaction_step_id
                      ,'P_ASS_ATTRIBUTE2'))
       END                                        ass_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_ASS_ATTRIBUTE3')
       END                                        ass_attribute3_p
      ,CASE WHEN pend.transaction_id IS NOT NULL
            THEN xxas_com_vset_pkg.decode_descr_vset_value
                   ('800'
                   ,'PER_ALL_ASSIGNMENTS_F'
                   ,xxas_hr_api_transaction_pkg.varchar2_value
                      (pend.person_id
                      ,pend.api_name
                      ,pend.transaction_step_id
                      ,'P_ASS_ATTRIBUTE_CATEGORY')
                   ,'ASS_ATTRIBUTE3'
                   ,xxas_hr_api_transaction_pkg.varchar2_value
                      (pend.person_id
                      ,pend.api_name
                      ,pend.transaction_step_id
                      ,'P_ASS_ATTRIBUTE3'))
       END                                        ass_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_ASS_ATTRIBUTE4')
       END                                        ass_attribute4_p
      ,CASE WHEN pend.transaction_id IS NOT NULL
            THEN xxas_com_vset_pkg.decode_descr_vset_value
                   ('800'
                   ,'PER_ALL_ASSIGNMENTS_F'
                   ,xxas_hr_api_transaction_pkg.varchar2_value
                      (pend.person_id
                      ,pend.api_name
                      ,pend.transaction_step_id
                      ,'P_ASS_ATTRIBUTE_CATEGORY')
                   ,'ASS_ATTRIBUTE4'
                   ,xxas_hr_api_transaction_pkg.varchar2_value
                      (pend.person_id
                      ,pend.api_name
                      ,pend.transaction_step_id
                      ,'P_ASS_ATTRIBUTE4'))
       END                                        ass_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_ASS_ATTRIBUTE5')
       END                                        ass_attribute5_p
      ,CASE WHEN pend.transaction_id IS NOT NULL
            THEN xxas_com_vset_pkg.decode_descr_vset_value
                   ('800'
                   ,'PER_ALL_ASSIGNMENTS_F'
                   ,xxas_hr_api_transaction_pkg.varchar2_value
                      (pend.person_id
                      ,pend.api_name
                      ,pend.transaction_step_id
                      ,'P_ASS_ATTRIBUTE_CATEGORY')
                   ,'ASS_ATTRIBUTE5'
                   ,xxas_hr_api_transaction_pkg.varchar2_value
                      (pend.person_id
                      ,pend.api_name
                      ,pend.transaction_step_id
                      ,'P_ASS_ATTRIBUTE5'))
       END                                        ass_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_ASS_ATTRIBUTE6')
       END                                        ass_attribute6_p
      ,CASE WHEN pend.transaction_id IS NOT NULL
            THEN xxas_com_vset_pkg.decode_descr_vset_value
                   ('800'
                   ,'PER_ALL_ASSIGNMENTS_F'
                   ,xxas_hr_api_transaction_pkg.varchar2_value
                      (pend.person_id
                      ,pend.api_name
                      ,pend.transaction_step_id
                      ,'P_ASS_ATTRIBUTE_CATEGORY')
                   ,'ASS_ATTRIBUTE6'
                   ,xxas_hr_api_transaction_pkg.varchar2_value
                      (pend.person_id
                      ,pend.api_name
                      ,pend.transaction_step_id
                      ,'P_ASS_ATTRIBUTE6'))
       END                                        ass_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_ASS_ATTRIBUTE7')
       END                                        ass_attribute7_p
      ,CASE WHEN pend.transaction_id IS NOT NULL
            THEN xxas_com_vset_pkg.decode_descr_vset_value
                   ('800'
                   ,'PER_ALL_ASSIGNMENTS_F'
                   ,xxas_hr_api_transaction_pkg.varchar2_value
                      (pend.person_id
                      ,pend.api_name
                      ,pend.transaction_step_id
                      ,'P_ASS_ATTRIBUTE_CATEGORY')
                   ,'ASS_ATTRIBUTE7'
                   ,xxas_hr_api_transaction_pkg.varchar2_value
                      (pend.person_id
                      ,pend.api_name
                      ,pend.transaction_step_id
                      ,'P_ASS_ATTRIBUTE7'))
       END                                        ass_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_ASS_ATTRIBUTE8')
       END                                        ass_attribute8_p
      ,CASE WHEN pend.transaction_id IS NOT NULL
            THEN xxas_com_vset_pkg.decode_descr_vset_value
                   ('800'
                   ,'PER_ALL_ASSIGNMENTS_F'
                   ,xxas_hr_api_transaction_pkg.varchar2_value
                      (pend.person_id
                      ,pend.api_name
                      ,pend.transaction_step_id
                      ,'P_ASS_ATTRIBUTE_CATEGORY')
                   ,'ASS_ATTRIBUTE8'
                   ,xxas_hr_api_transaction_pkg.varchar2_value
                      (pend.person_id
                      ,pend.api_name
                      ,pend.transaction_step_id
                      ,'P_ASS_ATTRIBUTE8'))
       END                                        ass_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_ASS_ATTRIBUTE9')
       END                                        ass_attribute9_p
      ,CASE WHEN pend.transaction_id IS NOT NULL
            THEN xxas_com_vset_pkg.decode_descr_vset_value
                   ('800'
                   ,'PER_ALL_ASSIGNMENTS_F'
                   ,xxas_hr_api_transaction_pkg.varchar2_value
                      (pend.person_id
                      ,pend.api_name
                      ,pend.transaction_step_id
                      ,'P_ASS_ATTRIBUTE_CATEGORY')
                   ,'ASS_ATTRIBUTE9'
                   ,xxas_hr_api_transaction_pkg.varchar2_value
                      (pend.person_id
                      ,pend.api_name
                      ,pend.transaction_step_id
                      ,'P_ASS_ATTRIBUTE9'))
       END                                        ass_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_ASS_ATTRIBUTE10')
       END                                        ass_attribute10_p
      ,CASE WHEN pend.transaction_id IS NOT NULL
            THEN xxas_com_vset_pkg.decode_descr_vset_value
                   ('800'
                   ,'PER_ALL_ASSIGNMENTS_F'
                   ,xxas_hr_api_transaction_pkg.varchar2_value
                      (pend.person_id
                      ,pend.api_name
                      ,pend.transaction_step_id
                      ,'P_ASS_ATTRIBUTE_CATEGORY')
                   ,'ASS_ATTRIBUTE10'
                   ,xxas_hr_api_transaction_pkg.varchar2_value
                      (pend.person_id
                      ,pend.api_name
                      ,pend.transaction_step_id
                      ,'P_ASS_ATTRIBUTE10'))
       END                                        ass_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_ASS_ATTRIBUTE11')
       END                                        ass_attribute11_p
      ,CASE WHEN pend.transaction_id IS NOT NULL
            THEN xxas_com_vset_pkg.decode_descr_vset_value
                   ('800'
                   ,'PER_ALL_ASSIGNMENTS_F'
                   ,xxas_hr_api_transaction_pkg.varchar2_value
                      (pend.person_id
                      ,pend.api_name
                      ,pend.transaction_step_id
                      ,'P_ASS_ATTRIBUTE_CATEGORY')
                   ,'ASS_ATTRIBUTE11'
                   ,xxas_hr_api_transaction_pkg.varchar2_value
                      (pend.person_id
                      ,pend.api_name
                      ,pend.transaction_step_id
                      ,'P_ASS_ATTRIBUTE11'))
       END                                        ass_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_ASS_ATTRIBUTE12')
       END                                        ass_attribute12_p
      ,CASE WHEN pend.transaction_id IS NOT NULL
            THEN xxas_com_vset_pkg.decode_descr_vset_value
                   ('800'
                   ,'PER_ALL_ASSIGNMENTS_F'
                   ,xxas_hr_api_transaction_pkg.varchar2_value
                      (pend.person_id
                      ,pend.api_name
                      ,pend.transaction_step_id
                      ,'P_ASS_ATTRIBUTE_CATEGORY')
                   ,'ASS_ATTRIBUTE12'
                   ,xxas_hr_api_transaction_pkg.varchar2_value
                      (pend.person_id
                      ,pend.api_name
                      ,pend.transaction_step_id
                      ,'P_ASS_ATTRIBUTE12'))
       END                                        ass_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_ASS_ATTRIBUTE13')
       END                                        ass_attribute13_p
      ,CASE WHEN pend.transaction_id IS NOT NULL
            THEN xxas_com_vset_pkg.decode_descr_vset_value
                   ('800'
                   ,'PER_ALL_ASSIGNMENTS_F'
                   ,xxas_hr_api_transaction_pkg.varchar2_value
                      (pend.person_id
                      ,pend.api_name
                      ,pend.transaction_step_id
                      ,'P_ASS_ATTRIBUTE_CATEGORY')
                   ,'ASS_ATTRIBUTE13'
                   ,xxas_hr_api_transaction_pkg.varchar2_value
                      (pend.person_id
                      ,pend.api_name
                      ,pend.transaction_step_id
                      ,'P_ASS_ATTRIBUTE13'))
       END                                        ass_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_ASS_ATTRIBUTE14')
       END                                        ass_attribute14_p
      ,CASE WHEN pend.transaction_id IS NOT NULL
            THEN xxas_com_vset_pkg.decode_descr_vset_value
                   ('800'
                   ,'PER_ALL_ASSIGNMENTS_F'
                   ,xxas_hr_api_transaction_pkg.varchar2_value
                      (pend.person_id
                      ,pend.api_name
                      ,pend.transaction_step_id
                      ,'P_ASS_ATTRIBUTE_CATEGORY')
                   ,'ASS_ATTRIBUTE14'
                   ,xxas_hr_api_transaction_pkg.varchar2_value
                      (pend.person_id
                      ,pend.api_name
                      ,pend.transaction_step_id
                      ,'P_ASS_ATTRIBUTE14'))
       END                                        ass_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_ASS_ATTRIBUTE15')
       END                                        ass_attribute15_p
      ,CASE WHEN pend.transaction_id IS NOT NULL
            THEN xxas_com_vset_pkg.decode_descr_vset_value
                   ('800'
                   ,'PER_ALL_ASSIGNMENTS_F'
                   ,xxas_hr_api_transaction_pkg.varchar2_value
                      (pend.person_id
                      ,pend.api_name
                      ,pend.transaction_step_id
                      ,'P_ASS_ATTRIBUTE_CATEGORY')
                   ,'ASS_ATTRIBUTE15'
                   ,xxas_hr_api_transaction_pkg.varchar2_value
                      (pend.person_id
                      ,pend.api_name
                      ,pend.transaction_step_id
                      ,'P_ASS_ATTRIBUTE15'))
       END                                        ass_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_ASS_ATTRIBUTE16')
       END                                        ass_attribute16_p
      ,CASE WHEN pend.transaction_id IS NOT NULL
            THEN xxas_com_vset_pkg.decode_descr_vset_value
                   ('800'
                   ,'PER_ALL_ASSIGNMENTS_F'
                   ,xxas_hr_api_transaction_pkg.varchar2_value
                      (pend.person_id
                      ,pend.api_name
                      ,pend.transaction_step_id
                      ,'P_ASS_ATTRIBUTE_CATEGORY')
                   ,'ASS_ATTRIBUTE16'
                   ,xxas_hr_api_transaction_pkg.varchar2_value
                      (pend.person_id
                      ,pend.api_name
                      ,pend.transaction_step_id
                      ,'P_ASS_ATTRIBUTE16'))
       END                                        ass_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_ASS_ATTRIBUTE17')
       END                                        ass_attribute17_p
      ,CASE WHEN pend.transaction_id IS NOT NULL
            THEN xxas_com_vset_pkg.decode_descr_vset_value
                   ('800'
                   ,'PER_ALL_ASSIGNMENTS_F'
                   ,xxas_hr_api_transaction_pkg.varchar2_value
                      (pend.person_id
                      ,pend.api_name
                      ,pend.transaction_step_id
                      ,'P_ASS_ATTRIBUTE_CATEGORY')
                   ,'ASS_ATTRIBUTE17'
                   ,xxas_hr_api_transaction_pkg.varchar2_value
                      (pend.person_id
                      ,pend.api_name
                      ,pend.transaction_step_id
                      ,'P_ASS_ATTRIBUTE17'))
       END                                        ass_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_ASS_ATTRIBUTE18')
       END                                        ass_attribute18_p
      ,CASE WHEN pend.transaction_id IS NOT NULL
            THEN xxas_com_vset_pkg.decode_descr_vset_value
                   ('800'
                   ,'PER_ALL_ASSIGNMENTS_F'
                   ,xxas_hr_api_transaction_pkg.varchar2_value
                      (pend.person_id
                      ,pend.api_name
                      ,pend.transaction_step_id
                      ,'P_ASS_ATTRIBUTE_CATEGORY')
                   ,'ASS_ATTRIBUTE18'
                   ,xxas_hr_api_transaction_pkg.varchar2_value
                      (pend.person_id
                      ,pend.api_name
                      ,pend.transaction_step_id
                      ,'P_ASS_ATTRIBUTE18'))
       END                                        ass_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_ASS_ATTRIBUTE19')
       END                                        ass_attribute19_p
      ,CASE WHEN pend.transaction_id IS NOT NULL
            THEN xxas_com_vset_pkg.decode_descr_vset_value
                   ('800'
                   ,'PER_ALL_ASSIGNMENTS_F'
                   ,xxas_hr_api_transaction_pkg.varchar2_value
                      (pend.person_id
                      ,pend.api_name
                      ,pend.transaction_step_id
                      ,'P_ASS_ATTRIBUTE_CATEGORY')
                   ,'ASS_ATTRIBUTE19'
                   ,xxas_hr_api_transaction_pkg.varchar2_value
                      (pend.person_id
                      ,pend.api_name
                      ,pend.transaction_step_id
                      ,'P_ASS_ATTRIBUTE19'))
       END                                        ass_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_ASS_ATTRIBUTE20')
       END                                        ass_attribute20_p
      ,CASE WHEN pend.transaction_id IS NOT NULL
            THEN xxas_com_vset_pkg.decode_descr_vset_value
                   ('800'
                   ,'PER_ALL_ASSIGNMENTS_F'
                   ,xxas_hr_api_transaction_pkg.varchar2_value
                      (pend.person_id
                      ,pend.api_name
                      ,pend.transaction_step_id
                      ,'P_ASS_ATTRIBUTE_CATEGORY')
                   ,'ASS_ATTRIBUTE20'
                   ,xxas_hr_api_transaction_pkg.varchar2_value
                      (pend.person_id
                      ,pend.api_name
                      ,pend.transaction_step_id
                      ,'P_ASS_ATTRIBUTE20'))
       END                                        ass_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_ASS_ATTRIBUTE21')
       END                                        ass_attribute21_p
      ,CASE WHEN pend.transaction_id IS NOT NULL
            THEN xxas_com_vset_pkg.decode_descr_vset_value
                   ('800'
                   ,'PER_ALL_ASSIGNMENTS_F'
                   ,xxas_hr_api_transaction_pkg.varchar2_value
                      (pend.person_id
                      ,pend.api_name
                      ,pend.transaction_step_id
                      ,'P_ASS_ATTRIBUTE_CATEGORY')
                   ,'ASS_ATTRIBUTE21'
                   ,xxas_hr_api_transaction_pkg.varchar2_value
                      (pend.person_id
                      ,pend.api_name
                      ,pend.transaction_step_id
                      ,'P_ASS_ATTRIBUTE21'))
       END                                        ass_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_ASS_ATTRIBUTE22')
       END                                        ass_attribute22_p
      ,CASE WHEN pend.transaction_id IS NOT NULL
            THEN xxas_com_vset_pkg.decode_descr_vset_value
                   ('800'
                   ,'PER_ALL_ASSIGNMENTS_F'
                   ,xxas_hr_api_transaction_pkg.varchar2_value
                      (pend.person_id
                      ,pend.api_name
                      ,pend.transaction_step_id
                      ,'P_ASS_ATTRIBUTE_CATEGORY')
                   ,'ASS_ATTRIBUTE22'
                   ,xxas_hr_api_transaction_pkg.varchar2_value
                      (pend.person_id
                      ,pend.api_name
                      ,pend.transaction_step_id
                      ,'P_ASS_ATTRIBUTE22'))
       END                                        ass_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_ASS_ATTRIBUTE23')
       END                                        ass_attribute23_p
      ,CASE WHEN pend.transaction_id IS NOT NULL
            THEN xxas_com_vset_pkg.decode_descr_vset_value
                   ('800'
                   ,'PER_ALL_ASSIGNMENTS_F'
                   ,xxas_hr_api_transaction_pkg.varchar2_value
                      (pend.person_id
                      ,pend.api_name
                      ,pend.transaction_step_id
                      ,'P_ASS_ATTRIBUTE_CATEGORY')
                   ,'ASS_ATTRIBUTE23'
                   ,xxas_hr_api_transaction_pkg.varchar2_value
                      (pend.person_id
                      ,pend.api_name
                      ,pend.transaction_step_id
                      ,'P_ASS_ATTRIBUTE23'))
       END                                        ass_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_ASS_ATTRIBUTE24')
       END                                        ass_attribute24_p
      ,CASE WHEN pend.transaction_id IS NOT NULL
            THEN xxas_com_vset_pkg.decode_descr_vset_value
                   ('800'
                   ,'PER_ALL_ASSIGNMENTS_F'
                   ,xxas_hr_api_transaction_pkg.varchar2_value
                      (pend.person_id
                      ,pend.api_name
                      ,pend.transaction_step_id
                      ,'P_ASS_ATTRIBUTE_CATEGORY')
                   ,'ASS_ATTRIBUTE24'
                   ,xxas_hr_api_transaction_pkg.varchar2_value
                      (pend.person_id
                      ,pend.api_name
                      ,pend.transaction_step_id
                      ,'P_ASS_ATTRIBUTE24'))
       END                                        ass_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_ASS_ATTRIBUTE25')
       END                                        ass_attribute25_p
      ,CASE WHEN pend.transaction_id IS NOT NULL
            THEN xxas_com_vset_pkg.decode_descr_vset_value
                   ('800'
                   ,'PER_ALL_ASSIGNMENTS_F'
                   ,xxas_hr_api_transaction_pkg.varchar2_value
                      (pend.person_id
                      ,pend.api_name
                      ,pend.transaction_step_id
                      ,'P_ASS_ATTRIBUTE_CATEGORY')
                   ,'ASS_ATTRIBUTE25'
                   ,xxas_hr_api_transaction_pkg.varchar2_value
                      (pend.person_id
                      ,pend.api_name
                      ,pend.transaction_step_id
                      ,'P_ASS_ATTRIBUTE25'))
       END                                        ass_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_ASS_ATTRIBUTE26')
       END                                        ass_attribute26_p
      ,CASE WHEN pend.transaction_id IS NOT NULL
            THEN xxas_com_vset_pkg.decode_descr_vset_value
                   ('800'
                   ,'PER_ALL_ASSIGNMENTS_F'
                   ,xxas_hr_api_transaction_pkg.varchar2_value
                      (pend.person_id
                      ,pend.api_name
                      ,pend.transaction_step_id
                      ,'P_ASS_ATTRIBUTE_CATEGORY')
                   ,'ASS_ATTRIBUTE26'
                   ,xxas_hr_api_transaction_pkg.varchar2_value
                      (pend.person_id
                      ,pend.api_name
                      ,pend.transaction_step_id
                      ,'P_ASS_ATTRIBUTE26'))
       END                                        ass_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_ASS_ATTRIBUTE27')
       END                                        ass_attribute27_p
      ,CASE WHEN pend.transaction_id IS NOT NULL
            THEN xxas_com_vset_pkg.decode_descr_vset_value
                   ('800'
                   ,'PER_ALL_ASSIGNMENTS_F'
                   ,xxas_hr_api_transaction_pkg.varchar2_value
                      (pend.person_id
                      ,pend.api_name
                      ,pend.transaction_step_id
                      ,'P_ASS_ATTRIBUTE_CATEGORY')
                   ,'ASS_ATTRIBUTE27'
                   ,xxas_hr_api_transaction_pkg.varchar2_value
                      (pend.person_id
                      ,pend.api_name
                      ,pend.transaction_step_id
                      ,'P_ASS_ATTRIBUTE27'))
       END                                        ass_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_ASS_ATTRIBUTE28')
       END                                        ass_attribute28_p
      ,CASE WHEN pend.transaction_id IS NOT NULL
            THEN xxas_com_vset_pkg.decode_descr_vset_value
                   ('800'
                   ,'PER_ALL_ASSIGNMENTS_F'
                   ,xxas_hr_api_transaction_pkg.varchar2_value
                      (pend.person_id
                      ,pend.api_name
                      ,pend.transaction_step_id
                      ,'P_ASS_ATTRIBUTE_CATEGORY')
                   ,'ASS_ATTRIBUTE28'
                   ,xxas_hr_api_transaction_pkg.varchar2_value
                      (pend.person_id
                      ,pend.api_name
                      ,pend.transaction_step_id
                      ,'P_ASS_ATTRIBUTE28'))
       END                                        ass_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_ASS_ATTRIBUTE29')
       END                                        ass_attribute29_p
      ,CASE WHEN pend.transaction_id IS NOT NULL
            THEN xxas_com_vset_pkg.decode_descr_vset_value
                   ('800'
                   ,'PER_ALL_ASSIGNMENTS_F'
                   ,xxas_hr_api_transaction_pkg.varchar2_value
                      (pend.person_id
                      ,pend.api_name
                      ,pend.transaction_step_id
                      ,'P_ASS_ATTRIBUTE_CATEGORY')
                   ,'ASS_ATTRIBUTE29'
                   ,xxas_hr_api_transaction_pkg.varchar2_value
                      (pend.person_id
                      ,pend.api_name
                      ,pend.transaction_step_id
                      ,'P_ASS_ATTRIBUTE29'))
       END                                        ass_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_ASS_ATTRIBUTE30')
       END                                        ass_attribute30_p
      ,CASE WHEN pend.transaction_id IS NOT NULL
            THEN xxas_com_vset_pkg.decode_descr_vset_value
                   ('800'
                   ,'PER_ALL_ASSIGNMENTS_F'
                   ,xxas_hr_api_transaction_pkg.varchar2_value
                      (pend.person_id
                      ,pend.api_name
                      ,pend.transaction_step_id
                      ,'P_ASS_ATTRIBUTE_CATEGORY')
                   ,'ASS_ATTRIBUTE30'
                   ,xxas_hr_api_transaction_pkg.varchar2_value
                      (pend.person_id
                      ,pend.api_name
                      ,pend.transaction_step_id
                      ,'P_ASS_ATTRIBUTE30'))
       END                                        ass_attribute30_meaning_p
      ,CASE WHEN pend.transaction_id IS NOT NULL
            THEN pgkp.pg_segment1
       END                                        pg_segment1_p
      ,CASE WHEN pend.transaction_id IS NOT NULL
            THEN pgkp.pg_segment1_value
       END                                        pg_segment1_value_p
      ,CASE WHEN pend.transaction_id IS NOT NULL
            THEN pgkp.pg_segment1_meaning
       END                                        pg_segment1_meaning_p
      ,CASE WHEN pend.transaction_id IS NOT NULL
            THEN pgkp.pg_segment2
       END                                        pg_segment2_p
      ,CASE WHEN pend.transaction_id IS NOT NULL
            THEN pgkp.pg_segment2_value
       END                                        pg_segment2_value_p
      ,CASE WHEN pend.transaction_id IS NOT NULL
            THEN pgkp.pg_segment2_meaning
       END                                        pg_segment2_meaning_p
      ,CASE WHEN pend.transaction_id IS NOT NULL
            THEN pgkp.pg_segment3
       END                                        pg_segment3_p
      ,CASE WHEN pend.transaction_id IS NOT NULL
            THEN pgkp.pg_segment3_value
       END                                        pg_segment3_value_p
      ,CASE WHEN pend.transaction_id IS NOT NULL
            THEN pgkp.pg_segment3_meaning
       END                                        pg_segment3_meaning_p
      ,CASE WHEN pend.transaction_id IS NOT NULL
            THEN pgkp.pg_segment4
       END                                        pg_segment4_p
      ,CASE WHEN pend.transaction_id IS NOT NULL
            THEN pgkp.pg_segment4_value
       END                                        pg_segment4_value_p
      ,CASE WHEN pend.transaction_id IS NOT NULL
            THEN pgkp.pg_segment4_meaning
       END                                        pg_segment4_meaning_p
      ,CASE WHEN pend.transaction_id IS NOT NULL
            THEN pgkp.pg_segment5
       END                                        pg_segment5_p
      ,CASE WHEN pend.transaction_id IS NOT NULL
            THEN pgkp.pg_segment5_value
       END                                        pg_segment5_value_p
      ,CASE WHEN pend.transaction_id IS NOT NULL
            THEN pgkp.pg_segment5_meaning
       END                                        pg_segment5_meaning_p
      ,CASE WHEN pend.transaction_id IS NOT NULL
            THEN pgkp.pg_segment6
       END                                        pg_segment6_p
      ,CASE WHEN pend.transaction_id IS NOT NULL
            THEN pgkp.pg_segment6_value
       END                                        pg_segment6_value_p
      ,CASE WHEN pend.transaction_id IS NOT NULL
            THEN pgkp.pg_segment6_meaning
       END                                        pg_segment6_meaning_p
      ,CASE WHEN pend.transaction_id IS NOT NULL
            THEN pgkp.pg_segment7
       END                                        pg_segment7_p
      ,CASE WHEN pend.transaction_id IS NOT NULL
            THEN pgkp.pg_segment7_value
       END                                        pg_segment7_value_p
      ,CASE WHEN pend.transaction_id IS NOT NULL
            THEN pgkp.pg_segment7_meaning
       END                                        pg_segment7_meaning_p
      ,CASE WHEN pend.transaction_id IS NOT NULL
            THEN pgkp.pg_segment8
       END                                        pg_segment8_p
      ,CASE WHEN pend.transaction_id IS NOT NULL
            THEN pgkp.pg_segment8_value
       END                                        pg_segment8_value_p
      ,CASE WHEN pend.transaction_id IS NOT NULL
            THEN pgkp.pg_segment8_meaning
       END                                        pg_segment8_meaning_p
      ,CASE WHEN pend.transaction_id IS NOT NULL
            THEN pgkp.pg_segment9
       END                                        pg_segment9_p
      ,CASE WHEN pend.transaction_id IS NOT NULL
            THEN pgkp.pg_segment9_value
       END                                        pg_segment9_value_p
      ,CASE WHEN pend.transaction_id IS NOT NULL
            THEN pgkp.pg_segment9_meaning
       END                                        pg_segment9_meaning_p
      ,CASE WHEN pend.transaction_id IS NOT NULL
            THEN pgkp.pg_segment10
       END                                        pg_segment10_p
      ,CASE WHEN pend.transaction_id IS NOT NULL
            THEN pgkp.pg_segment10_value
       END                                        pg_segment10_value_p
      ,CASE WHEN pend.transaction_id IS NOT NULL
            THEN pgkp.pg_segment10_meaning
       END                                        pg_segment10_meaning_p
      ,CASE WHEN pend.transaction_id IS NOT NULL
            THEN pgkp.pg_segment11
       END                                        pg_segment11_p
      ,CASE WHEN pend.transaction_id IS NOT NULL
            THEN pgkp.pg_segment11_value
       END                                        pg_segment11_value_p
      ,CASE WHEN pend.transaction_id IS NOT NULL
            THEN pgkp.pg_segment11_meaning
       END                                        pg_segment11_meaning_p
      ,CASE WHEN pend.transaction_id IS NOT NULL
            THEN pgkp.pg_segment12
       END                                        pg_segment12_p
      ,CASE WHEN pend.transaction_id IS NOT NULL
            THEN pgkp.pg_segment12_value
       END                                        pg_segment12_value_p
      ,CASE WHEN pend.transaction_id IS NOT NULL
            THEN pgkp.pg_segment12_meaning
       END                                        pg_segment12_meaning_p
      ,CASE WHEN pend.transaction_id IS NOT NULL
            THEN pgkp.pg_segment13
       END                                        pg_segment13_p
      ,CASE WHEN pend.transaction_id IS NOT NULL
            THEN pgkp.pg_segment13_value
       END                                        pg_segment13_value_p
      ,CASE WHEN pend.transaction_id IS NOT NULL
            THEN pgkp.pg_segment13_meaning
       END                                        pg_segment13_meaning_p
      ,CASE WHEN pend.transaction_id IS NOT NULL
            THEN pgkp.pg_segment14
       END                                        pg_segment14_p
      ,CASE WHEN pend.transaction_id IS NOT NULL
            THEN pgkp.pg_segment14_value
       END                                        pg_segment14_value_p
      ,CASE WHEN pend.transaction_id IS NOT NULL
            THEN pgkp.pg_segment14_meaning
       END                                        pg_segment14_meaning_p
      ,CASE WHEN pend.transaction_id IS NOT NULL
            THEN pgkp.pg_segment15
       END                                        pg_segment15_p
      ,CASE WHEN pend.transaction_id IS NOT NULL
            THEN pgkp.pg_segment15_value
       END                                        pg_segment15_value_p
      ,CASE WHEN pend.transaction_id IS NOT NULL
            THEN pgkp.pg_segment15_meaning
       END                                        pg_segment15_meaning_p
      ,CASE WHEN pend.transaction_id IS NOT NULL
            THEN pgkp.pg_segment16
       END                                        pg_segment16_p
      ,CASE WHEN pend.transaction_id IS NOT NULL
            THEN pgkp.pg_segment16_value
       END                                        pg_segment16_value_p
      ,CASE WHEN pend.transaction_id IS NOT NULL
            THEN pgkp.pg_segment16_meaning
       END                                        pg_segment16_meaning_p
      ,CASE WHEN pend.transaction_id IS NOT NULL
            THEN pgkp.pg_segment17
       END                                        pg_segment17_p
      ,CASE WHEN pend.transaction_id IS NOT NULL
            THEN pgkp.pg_segment17_value
       END                                        pg_segment17_value_p
      ,CASE WHEN pend.transaction_id IS NOT NULL
            THEN pgkp.pg_segment17_meaning
       END                                        pg_segment17_meaning_p
      ,CASE WHEN pend.transaction_id IS NOT NULL
            THEN pgkp.pg_segment18
       END                                        pg_segment18_p
      ,CASE WHEN pend.transaction_id IS NOT NULL
            THEN pgkp.pg_segment18_value
       END                                        pg_segment18_value_p
      ,CASE WHEN pend.transaction_id IS NOT NULL
            THEN pgkp.pg_segment18_meaning
       END                                        pg_segment18_meaning_p
      ,CASE WHEN pend.transaction_id IS NOT NULL
            THEN pgkp.pg_segment19
       END                                        pg_segment19_p
      ,CASE WHEN pend.transaction_id IS NOT NULL
            THEN pgkp.pg_segment19_value
       END                                        pg_segment19_value_p
      ,CASE WHEN pend.transaction_id IS NOT NULL
            THEN pgkp.pg_segment19_meaning
       END                                        pg_segment19_meaning_p
      ,CASE WHEN pend.transaction_id IS NOT NULL
            THEN pgkp.pg_segment20
       END                                        pg_segment20_p
      ,CASE WHEN pend.transaction_id IS NOT NULL
            THEN pgkp.pg_segment20_value
       END                                        pg_segment20_value_p
      ,CASE WHEN pend.transaction_id IS NOT NULL
            THEN pgkp.pg_segment20_meaning
       END                                        pg_segment20_meaning_p
      ,CASE WHEN pend.transaction_id IS NOT NULL
            THEN pgkp.pg_segment21
       END                                        pg_segment21_p
      ,CASE WHEN pend.transaction_id IS NOT NULL
            THEN pgkp.pg_segment21_value
       END                                        pg_segment21_value_p
      ,CASE WHEN pend.transaction_id IS NOT NULL
            THEN pgkp.pg_segment21_meaning
       END                                        pg_segment21_meaning_p
      ,CASE WHEN pend.transaction_id IS NOT NULL
            THEN pgkp.pg_segment22
       END                                        pg_segment22_p
      ,CASE WHEN pend.transaction_id IS NOT NULL
            THEN pgkp.pg_segment22_value
       END                                        pg_segment22_value_p
      ,CASE WHEN pend.transaction_id IS NOT NULL
            THEN pgkp.pg_segment22_meaning
       END                                        pg_segment22_meaning_p
      ,CASE WHEN pend.transaction_id IS NOT NULL
            THEN pgkp.pg_segment23
       END                                        pg_segment23_p
      ,CASE WHEN pend.transaction_id IS NOT NULL
            THEN pgkp.pg_segment23_value
       END                                        pg_segment23_value_p
      ,CASE WHEN pend.transaction_id IS NOT NULL
            THEN pgkp.pg_segment23_meaning
       END                                        pg_segment23_meaning_p
      ,CASE WHEN pend.transaction_id IS NOT NULL
            THEN pgkp.pg_segment24
       END                                        pg_segment24_p
      ,CASE WHEN pend.transaction_id IS NOT NULL
            THEN pgkp.pg_segment24_value
       END                                        pg_segment24_value_p
      ,CASE WHEN pend.transaction_id IS NOT NULL
            THEN pgkp.pg_segment24_meaning
       END                                        pg_segment24_meaning_p
      ,CASE WHEN pend.transaction_id IS NOT NULL
            THEN pgkp.pg_segment25
       END                                        pg_segment25_p
      ,CASE WHEN pend.transaction_id IS NOT NULL
            THEN pgkp.pg_segment25_value
       END                                        pg_segment25_value_p
      ,CASE WHEN pend.transaction_id IS NOT NULL
            THEN pgkp.pg_segment25_meaning
       END                                        pg_segment25_meaning_p
      ,CASE WHEN pend.transaction_id IS NOT NULL
            THEN pgkp.pg_segment26
       END                                        pg_segment26_p
      ,CASE WHEN pend.transaction_id IS NOT NULL
            THEN pgkp.pg_segment26_value
       END                                        pg_segment26_value_p
      ,CASE WHEN pend.transaction_id IS NOT NULL
            THEN pgkp.pg_segment26_meaning
       END                                        pg_segment26_meaning_p
      ,CASE WHEN pend.transaction_id IS NOT NULL
            THEN pgkp.pg_segment27
       END                                        pg_segment27_p
      ,CASE WHEN pend.transaction_id IS NOT NULL
            THEN pgkp.pg_segment27_value
       END                                        pg_segment27_value_p
      ,CASE WHEN pend.transaction_id IS NOT NULL
            THEN pgkp.pg_segment27_meaning
       END                                        pg_segment27_meaning_p
      ,CASE WHEN pend.transaction_id IS NOT NULL
            THEN pgkp.pg_segment28
       END                                        pg_segment28_p
      ,CASE WHEN pend.transaction_id IS NOT NULL
            THEN pgkp.pg_segment28_value
       END                                        pg_segment28_value_p
      ,CASE WHEN pend.transaction_id IS NOT NULL
            THEN pgkp.pg_segment28_meaning
       END                                        pg_segment28_meaning_p
      ,CASE WHEN pend.transaction_id IS NOT NULL
            THEN pgkp.pg_segment29
       END                                        pg_segment29_p
      ,CASE WHEN pend.transaction_id IS NOT NULL
            THEN pgkp.pg_segment29_value
       END                                        pg_segment29_value_p
      ,CASE WHEN pend.transaction_id IS NOT NULL
            THEN pgkp.pg_segment29_meaning
       END                                        pg_segment29_meaning_p
      ,CASE WHEN pend.transaction_id IS NOT NULL
            THEN pgkp.pg_segment30
       END                                        pg_segment30_p
      ,CASE WHEN pend.transaction_id IS NOT NULL
            THEN pgkp.pg_segment30_value
       END                                        pg_segment30_value_p
      ,CASE WHEN pend.transaction_id IS NOT NULL
            THEN pgkp.pg_segment30_meaning
       END                                        pg_segment30_meaning_p
      ,CASE WHEN pend.transaction_id IS NOT NULL
            THEN stkp.st_segment1
       END                                        st_segment1_p
      ,CASE WHEN pend.transaction_id IS NOT NULL
            THEN stkp.st_segment1_value
       END                                        st_segment1_value_p
      ,CASE WHEN pend.transaction_id IS NOT NULL
            THEN stkp.st_segment1_meaning
       END                                        st_segment1_meaning_p
      ,CASE WHEN pend.transaction_id IS NOT NULL
            THEN stkp.st_segment2
       END                                        st_segment2_p
      ,CASE WHEN pend.transaction_id IS NOT NULL
            THEN stkp.st_segment2_value
       END                                        st_segment2_value_p
      ,CASE WHEN pend.transaction_id IS NOT NULL
            THEN stkp.st_segment2_meaning
       END                                        st_segment2_meaning_p
      ,CASE WHEN pend.transaction_id IS NOT NULL
            THEN stkp.st_segment3
       END                                        st_segment3_p
      ,CASE WHEN pend.transaction_id IS NOT NULL
            THEN stkp.st_segment3_value
       END                                        st_segment3_value_p
      ,CASE WHEN pend.transaction_id IS NOT NULL
            THEN stkp.st_segment3_meaning
       END                                        st_segment3_meaning_p
      ,CASE WHEN pend.transaction_id IS NOT NULL
            THEN stkp.st_segment4
       END                                        st_segment4_p
      ,CASE WHEN pend.transaction_id IS NOT NULL
            THEN stkp.st_segment4_value
       END                                        st_segment4_value_p
      ,CASE WHEN pend.transaction_id IS NOT NULL
            THEN stkp.st_segment4_meaning
       END                                        st_segment4_meaning_p
      ,CASE WHEN pend.transaction_id IS NOT NULL
            THEN stkp.st_segment5
       END                                        st_segment5_p
      ,CASE WHEN pend.transaction_id IS NOT NULL
            THEN stkp.st_segment5_value
       END                                        st_segment5_value_p
      ,CASE WHEN pend.transaction_id IS NOT NULL
            THEN stkp.st_segment5_meaning
       END                                        st_segment5_meaning_p
      ,CASE WHEN pend.transaction_id IS NOT NULL
            THEN stkp.st_segment6
       END                                        st_segment6_p
      ,CASE WHEN pend.transaction_id IS NOT NULL
            THEN stkp.st_segment6_value
       END                                        st_segment6_value_p
      ,CASE WHEN pend.transaction_id IS NOT NULL
            THEN stkp.st_segment6_meaning
       END                                        st_segment6_meaning_p
      ,CASE WHEN pend.transaction_id IS NOT NULL
            THEN stkp.st_segment7
       END                                        st_segment7_p
      ,CASE WHEN pend.transaction_id IS NOT NULL
            THEN stkp.st_segment7_value
       END                                        st_segment7_value_p
      ,CASE WHEN pend.transaction_id IS NOT NULL
            THEN stkp.st_segment7_meaning
       END                                        st_segment7_meaning_p
      ,CASE WHEN pend.transaction_id IS NOT NULL
            THEN stkp.st_segment8
       END                                        st_segment8_p
      ,CASE WHEN pend.transaction_id IS NOT NULL
            THEN stkp.st_segment8_value
       END                                        st_segment8_value_p
      ,CASE WHEN pend.transaction_id IS NOT NULL
            THEN stkp.st_segment8_meaning
       END                                        st_segment8_meaning_p
      ,CASE WHEN pend.transaction_id IS NOT NULL
            THEN stkp.st_segment9
       END                                        st_segment9_p
      ,CASE WHEN pend.transaction_id IS NOT NULL
            THEN stkp.st_segment9_value
       END                                        st_segment9_value_p
      ,CASE WHEN pend.transaction_id IS NOT NULL
            THEN stkp.st_segment9_meaning
       END                                        st_segment9_meaning_p
      ,CASE WHEN pend.transaction_id IS NOT NULL
            THEN stkp.st_segment10
       END                                        st_segment10_p
      ,CASE WHEN pend.transaction_id IS NOT NULL
            THEN stkp.st_segment10_value
       END                                        st_segment10_value_p
      ,CASE WHEN pend.transaction_id IS NOT NULL
            THEN stkp.st_segment10_meaning
       END                                        st_segment10_meaning_p
      ,CASE WHEN pend.transaction_id IS NOT NULL
            THEN stkp.st_segment11
       END                                        st_segment11_p
      ,CASE WHEN pend.transaction_id IS NOT NULL
            THEN stkp.st_segment11_value
       END                                        st_segment11_value_p
      ,CASE WHEN pend.transaction_id IS NOT NULL
            THEN stkp.st_segment11_meaning
       END                                        st_segment11_meaning_p
      ,CASE WHEN pend.transaction_id IS NOT NULL
            THEN stkp.st_segment12
       END                                        st_segment12_p
      ,CASE WHEN pend.transaction_id IS NOT NULL
            THEN stkp.st_segment12_value
       END                                        st_segment12_value_p
      ,CASE WHEN pend.transaction_id IS NOT NULL
            THEN stkp.st_segment12_meaning
       END                                        st_segment12_meaning_p
      ,CASE WHEN pend.transaction_id IS NOT NULL
            THEN stkp.st_segment13
       END                                        st_segment13_p
      ,CASE WHEN pend.transaction_id IS NOT NULL
            THEN stkp.st_segment13_value
       END                                        st_segment13_value_p
      ,CASE WHEN pend.transaction_id IS NOT NULL
            THEN stkp.st_segment13_meaning
       END                                        st_segment13_meaning_p
      ,CASE WHEN pend.transaction_id IS NOT NULL
            THEN stkp.st_segment14
       END                                        st_segment14_p
      ,CASE WHEN pend.transaction_id IS NOT NULL
            THEN stkp.st_segment14_value
       END                                        st_segment14_value_p
      ,CASE WHEN pend.transaction_id IS NOT NULL
            THEN stkp.st_segment14_meaning
       END                                        st_segment14_meaning_p
      ,CASE WHEN pend.transaction_id IS NOT NULL
            THEN stkp.st_segment15
       END                                        st_segment15_p
      ,CASE WHEN pend.transaction_id IS NOT NULL
            THEN stkp.st_segment15_value
       END                                        st_segment15_value_p
      ,CASE WHEN pend.transaction_id IS NOT NULL
            THEN stkp.st_segment15_meaning
       END                                        st_segment15_meaning_p
      ,CASE WHEN pend.transaction_id IS NOT NULL
            THEN stkp.st_segment16
       END                                        st_segment16_p
      ,CASE WHEN pend.transaction_id IS NOT NULL
            THEN stkp.st_segment16_value
       END                                        st_segment16_value_p
      ,CASE WHEN pend.transaction_id IS NOT NULL
            THEN stkp.st_segment16_meaning
       END                                        st_segment16_meaning_p
      ,CASE WHEN pend.transaction_id IS NOT NULL
            THEN stkp.st_segment17
       END                                        st_segment17_p
      ,CASE WHEN pend.transaction_id IS NOT NULL
            THEN stkp.st_segment17_value
       END                                        st_segment17_value_p
      ,CASE WHEN pend.transaction_id IS NOT NULL
            THEN stkp.st_segment17_meaning
       END                                        st_segment17_meaning_p
      ,CASE WHEN pend.transaction_id IS NOT NULL
            THEN stkp.st_segment18
       END                                        st_segment18_p
      ,CASE WHEN pend.transaction_id IS NOT NULL
            THEN stkp.st_segment18_value
       END                                        st_segment18_value_p
      ,CASE WHEN pend.transaction_id IS NOT NULL
            THEN stkp.st_segment18_meaning
       END                                        st_segment18_meaning_p
      ,CASE WHEN pend.transaction_id IS NOT NULL
            THEN stkp.st_segment19
       END                                        st_segment19_p
      ,CASE WHEN pend.transaction_id IS NOT NULL
            THEN stkp.st_segment19_value
       END                                        st_segment19_value_p
      ,CASE WHEN pend.transaction_id IS NOT NULL
            THEN stkp.st_segment19_meaning
       END                                        st_segment19_meaning_p
      ,CASE WHEN pend.transaction_id IS NOT NULL
            THEN stkp.st_segment20
       END                                        st_segment20_p
      ,CASE WHEN pend.transaction_id IS NOT NULL
            THEN stkp.st_segment20_value
       END                                        st_segment20_value_p
      ,CASE WHEN pend.transaction_id IS NOT NULL
            THEN stkp.st_segment20_meaning
       END                                        st_segment20_meaning_p
      ,CASE WHEN pend.transaction_id IS NOT NULL
            THEN stkp.st_segment21
       END                                        st_segment21_p
      ,CASE WHEN pend.transaction_id IS NOT NULL
            THEN stkp.st_segment21_value
       END                                        st_segment21_value_p
      ,CASE WHEN pend.transaction_id IS NOT NULL
            THEN stkp.st_segment21_meaning
       END                                        st_segment21_meaning_p
      ,CASE WHEN pend.transaction_id IS NOT NULL
            THEN stkp.st_segment22
       END                                        st_segment22_p
      ,CASE WHEN pend.transaction_id IS NOT NULL
            THEN stkp.st_segment22_value
       END                                        st_segment22_value_p
      ,CASE WHEN pend.transaction_id IS NOT NULL
            THEN stkp.st_segment22_meaning
       END                                        st_segment22_meaning_p
      ,CASE WHEN pend.transaction_id IS NOT NULL
            THEN stkp.st_segment23
       END                                        st_segment23_p
      ,CASE WHEN pend.transaction_id IS NOT NULL
            THEN stkp.st_segment23_value
       END                                        st_segment23_value_p
      ,CASE WHEN pend.transaction_id IS NOT NULL
            THEN stkp.st_segment23_meaning
       END                                        st_segment23_meaning_p
      ,CASE WHEN pend.transaction_id IS NOT NULL
            THEN stkp.st_segment24
       END                                        st_segment24_p
      ,CASE WHEN pend.transaction_id IS NOT NULL
            THEN stkp.st_segment24_value
       END                                        st_segment24_value_p
      ,CASE WHEN pend.transaction_id IS NOT NULL
            THEN stkp.st_segment24_meaning
       END                                        st_segment24_meaning_p
      ,CASE WHEN pend.transaction_id IS NOT NULL
            THEN stkp.st_segment25
       END                                        st_segment25_p
      ,CASE WHEN pend.transaction_id IS NOT NULL
            THEN stkp.st_segment25_value
       END                                        st_segment25_value_p
      ,CASE WHEN pend.transaction_id IS NOT NULL
            THEN stkp.st_segment25_meaning
       END                                        st_segment25_meaning_p
      ,CASE WHEN pend.transaction_id IS NOT NULL
            THEN stkp.st_segment26
       END                                        st_segment26_p
      ,CASE WHEN pend.transaction_id IS NOT NULL
            THEN stkp.st_segment26_value
       END                                        st_segment26_value_p
      ,CASE WHEN pend.transaction_id IS NOT NULL
            THEN stkp.st_segment26_meaning
       END                                        st_segment26_meaning_p
      ,CASE WHEN pend.transaction_id IS NOT NULL
            THEN stkp.st_segment27
       END                                        st_segment27_p
      ,CASE WHEN pend.transaction_id IS NOT NULL
            THEN stkp.st_segment27_value
       END                                        st_segment27_value_p
      ,CASE WHEN pend.transaction_id IS NOT NULL
            THEN stkp.st_segment27_meaning
       END                                        st_segment27_meaning_p
      ,CASE WHEN pend.transaction_id IS NOT NULL
            THEN stkp.st_segment28
       END                                        st_segment28_p
      ,CASE WHEN pend.transaction_id IS NOT NULL
            THEN stkp.st_segment28_value
       END                                        st_segment28_value_p
      ,CASE WHEN pend.transaction_id IS NOT NULL
            THEN stkp.st_segment28_meaning
       END                                        st_segment28_meaning_p
      ,CASE WHEN pend.transaction_id IS NOT NULL
            THEN stkp.st_segment29
       END                                        st_segment29_p
      ,CASE WHEN pend.transaction_id IS NOT NULL
            THEN stkp.st_segment29_value
       END                                        st_segment29_value_p
      ,CASE WHEN pend.transaction_id IS NOT NULL
            THEN stkp.st_segment29_meaning
       END                                        st_segment29_meaning_p
      ,CASE WHEN pend.transaction_id IS NOT NULL
            THEN stkp.st_segment30
       END                                        st_segment30_p
      ,CASE WHEN pend.transaction_id IS NOT NULL
            THEN stkp.st_segment30_value
       END                                        st_segment30_value_p
      ,CASE WHEN pend.transaction_id IS NOT NULL
            THEN stkp.st_segment30_meaning
       END                                        st_segment30_meaning_p
      ,CASE WHEN pend.transaction_id IS NOT NULL
            THEN cakp.cagr_segment1
       END                                        cagr_segment1_p
      ,CASE WHEN pend.transaction_id IS NOT NULL
            THEN cakp.cagr_segment1_value
       END                                        cagr_segment1_value_p
      ,CASE WHEN pend.transaction_id IS NOT NULL
            THEN cakp.cagr_segment1_meaning
       END                                        cagr_segment1_meaning_p
      ,CASE WHEN pend.transaction_id IS NOT NULL
            THEN cakp.cagr_segment2
       END                                        cagr_segment2_p
      ,CASE WHEN pend.transaction_id IS NOT NULL
            THEN cakp.cagr_segment2_value
       END                                        cagr_segment2_value_p
      ,CASE WHEN pend.transaction_id IS NOT NULL
            THEN cakp.cagr_segment2_meaning
       END                                        cagr_segment2_meaning_p
      ,CASE WHEN pend.transaction_id IS NOT NULL
            THEN cakp.cagr_segment3
       END                                        cagr_segment3_p
      ,CASE WHEN pend.transaction_id IS NOT NULL
            THEN cakp.cagr_segment3_value
       END                                        cagr_segment3_value_p
      ,CASE WHEN pend.transaction_id IS NOT NULL
            THEN cakp.cagr_segment3_meaning
       END                                        cagr_segment3_meaning_p
      ,CASE WHEN pend.transaction_id IS NOT NULL
            THEN cakp.cagr_segment4
       END                                        cagr_segment4_p
      ,CASE WHEN pend.transaction_id IS NOT NULL
            THEN cakp.cagr_segment4_value
       END                                        cagr_segment4_value_p
      ,CASE WHEN pend.transaction_id IS NOT NULL
            THEN cakp.cagr_segment4_meaning
       END                                        cagr_segment4_meaning_p
      ,CASE WHEN pend.transaction_id IS NOT NULL
            THEN cakp.cagr_segment5
       END                                        cagr_segment5_p
      ,CASE WHEN pend.transaction_id IS NOT NULL
            THEN cakp.cagr_segment5_value
       END                                        cagr_segment5_value_p
      ,CASE WHEN pend.transaction_id IS NOT NULL
            THEN cakp.cagr_segment5_meaning
       END                                        cagr_segment5_meaning_p
      ,CASE WHEN pend.transaction_id IS NOT NULL
            THEN cakp.cagr_segment6
       END                                        cagr_segment6_p
      ,CASE WHEN pend.transaction_id IS NOT NULL
            THEN cakp.cagr_segment6_value
       END                                        cagr_segment6_value_p
      ,CASE WHEN pend.transaction_id IS NOT NULL
            THEN cakp.cagr_segment6_meaning
       END                                        cagr_segment6_meaning_p
      ,CASE WHEN pend.transaction_id IS NOT NULL
            THEN cakp.cagr_segment7
       END                                        cagr_segment7_p
      ,CASE WHEN pend.transaction_id IS NOT NULL
            THEN cakp.cagr_segment7_value
       END                                        cagr_segment7_value_p
      ,CASE WHEN pend.transaction_id IS NOT NULL
            THEN cakp.cagr_segment7_meaning
       END                                        cagr_segment7_meaning_p
      ,CASE WHEN pend.transaction_id IS NOT NULL
            THEN cakp.cagr_segment8
       END                                        cagr_segment8_p
      ,CASE WHEN pend.transaction_id IS NOT NULL
            THEN cakp.cagr_segment8_value
       END                                        cagr_segment8_value_p
      ,CASE WHEN pend.transaction_id IS NOT NULL
            THEN cakp.cagr_segment8_meaning
       END                                        cagr_segment8_meaning_p
      ,CASE WHEN pend.transaction_id IS NOT NULL
            THEN cakp.cagr_segment9
       END                                        cagr_segment9_p
      ,CASE WHEN pend.transaction_id IS NOT NULL
            THEN cakp.cagr_segment9_value
       END                                        cagr_segment9_value_p
      ,CASE WHEN pend.transaction_id IS NOT NULL
            THEN cakp.cagr_segment9_meaning
       END                                        cagr_segment9_meaning_p
      ,CASE WHEN pend.transaction_id IS NOT NULL
            THEN cakp.cagr_segment10
       END                                        cagr_segment10_p
      ,CASE WHEN pend.transaction_id IS NOT NULL
            THEN cakp.cagr_segment10_value
       END                                        cagr_segment10_value_p
      ,CASE WHEN pend.transaction_id IS NOT NULL
            THEN cakp.cagr_segment10_meaning
       END                                        cagr_segment10_meaning_p
      ,CASE WHEN pend.transaction_id IS NOT NULL
            THEN cakp.cagr_segment11
       END                                        cagr_segment11_p
      ,CASE WHEN pend.transaction_id IS NOT NULL
            THEN cakp.cagr_segment11_value
       END                                        cagr_segment11_value_p
      ,CASE WHEN pend.transaction_id IS NOT NULL
            THEN cakp.cagr_segment11_meaning
       END                                        cagr_segment11_meaning_p
      ,CASE WHEN pend.transaction_id IS NOT NULL
            THEN cakp.cagr_segment12
       END                                        cagr_segment12_p
      ,CASE WHEN pend.transaction_id IS NOT NULL
            THEN cakp.cagr_segment12_value
       END                                        cagr_segment12_value_p
      ,CASE WHEN pend.transaction_id IS NOT NULL
            THEN cakp.cagr_segment12_meaning
       END                                        cagr_segment12_meaning_p
      ,CASE WHEN pend.transaction_id IS NOT NULL
            THEN cakp.cagr_segment13
       END                                        cagr_segment13_p
      ,CASE WHEN pend.transaction_id IS NOT NULL
            THEN cakp.cagr_segment13_value
       END                                        cagr_segment13_value_p
      ,CASE WHEN pend.transaction_id IS NOT NULL
            THEN cakp.cagr_segment13_meaning
       END                                        cagr_segment13_meaning_p
      ,CASE WHEN pend.transaction_id IS NOT NULL
            THEN cakp.cagr_segment14
       END                                        cagr_segment14_p
      ,CASE WHEN pend.transaction_id IS NOT NULL
            THEN cakp.cagr_segment14_value
       END                                        cagr_segment14_value_p
      ,CASE WHEN pend.transaction_id IS NOT NULL
            THEN cakp.cagr_segment14_meaning
       END                                        cagr_segment14_meaning_p
      ,CASE WHEN pend.transaction_id IS NOT NULL
            THEN cakp.cagr_segment15
       END                                        cagr_segment15_p
      ,CASE WHEN pend.transaction_id IS NOT NULL
            THEN cakp.cagr_segment15_value
       END                                        cagr_segment15_value_p
      ,CASE WHEN pend.transaction_id IS NOT NULL
            THEN cakp.cagr_segment15_meaning
       END                                        cagr_segment15_meaning_p
      ,CASE WHEN pend.transaction_id IS NOT NULL
            THEN cakp.cagr_segment16
       END                                        cagr_segment16_p
      ,CASE WHEN pend.transaction_id IS NOT NULL
            THEN cakp.cagr_segment16_value
       END                                        cagr_segment16_value_p
      ,CASE WHEN pend.transaction_id IS NOT NULL
            THEN cakp.cagr_segment16_meaning
       END                                        cagr_segment16_meaning_p
      ,CASE WHEN pend.transaction_id IS NOT NULL
            THEN cakp.cagr_segment17
       END                                        cagr_segment17_p
      ,CASE WHEN pend.transaction_id IS NOT NULL
            THEN cakp.cagr_segment17_value
       END                                        cagr_segment17_value_p
      ,CASE WHEN pend.transaction_id IS NOT NULL
            THEN cakp.cagr_segment17_meaning
       END                                        cagr_segment17_meaning_p
      ,CASE WHEN pend.transaction_id IS NOT NULL
            THEN cakp.cagr_segment18
       END                                        cagr_segment18_p
      ,CASE WHEN pend.transaction_id IS NOT NULL
            THEN cakp.cagr_segment18_value
       END                                        cagr_segment18_value_p
      ,CASE WHEN pend.transaction_id IS NOT NULL
            THEN cakp.cagr_segment18_meaning
       END                                        cagr_segment18_meaning_p
      ,CASE WHEN pend.transaction_id IS NOT NULL
            THEN cakp.cagr_segment19
       END                                        cagr_segment19_p
      ,CASE WHEN pend.transaction_id IS NOT NULL
            THEN cakp.cagr_segment19_value
       END                                        cagr_segment19_value_p
      ,CASE WHEN pend.transaction_id IS NOT NULL
            THEN cakp.cagr_segment19_meaning
       END                                        cagr_segment19_meaning_p
      ,CASE WHEN pend.transaction_id IS NOT NULL
            THEN cakp.cagr_segment20
       END                                        cagr_segment20_p
      ,CASE WHEN pend.transaction_id IS NOT NULL
            THEN cakp.cagr_segment20_value
       END                                        cagr_segment20_value_p
      ,CASE WHEN pend.transaction_id IS NOT NULL
            THEN cakp.cagr_segment20_meaning
       END                                        cagr_segment20_meaning_p
      ,'GRP'                    pg_key_ff_code_p
      ,xxas_mss_util_pkg.pg_key_ff_structure_code(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_ORGANIZATION_ID')
                                                  END,'GRP',xxas_util_pkg.get_app_id('PAY')) pg_key_ff_st_code_p
      ,'SCL'                    st_key_ff_code_p
      ,xxas_mss_util_pkg.scl_key_ff_structure_code(xxas_util_pkg.get_leg_code_from_bg(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),'SCL',xxas_util_pkg.get_app_id('PER')) st_key_ff_st_code_p
      ,'CAGR'                   cagr_key_ff_code_p
      ,xxas_mss_util_pkg.cagr_key_ff_grade_type(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_CAGR_ID_FLEX_NUM')
                                                END,'CAGR',xxas_util_pkg.get_app_id('PER'))  cagr_grade_type_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_J_CHANGED')
       END                                        j_changed_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_HRS_LAST_DATE')
       END                                        hrs_last_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_DISPLAY_POS')
       END                                        display_pos_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_DISPLAY_ORG')
       END                                        display_org_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_DISPLAY_JOB')
       END                                        display_job_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_DISPLAY_ASS_STATUS')
       END                                        display_ass_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_DISPLAY_GRADE')
       END                                        display_grade_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_GRADE_LOV')
       END                                        grade_lov_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_APPROVER_ID')
       END                                        approver_id_p
      ,CASE WHEN pend.transaction_id IS NOT NULL
            THEN xxas_hr_api_transaction_pkg.number_value
                   (pend.person_id
                   ,pend.api_name
                   ,pend.transaction_step_id
                   ,'P_GRADE_LADDER_PGM_ID')
       END                                        grade_ladder_pgm_id_p
      ,CASE WHEN pend.transaction_id IS NOT NULL
            THEN xxas_hr_api_transaction_pkg.number_value
                   (pend.person_id
                   ,pend.api_name
                   ,pend.transaction_step_id
                   ,'P_PO_HEADER_ID')
       END                                        po_header_id_p
      ,CASE WHEN pend.transaction_id IS NOT NULL
            THEN xxas_hr_api_transaction_pkg.number_value
                   (pend.person_id
                   ,pend.api_name
                   ,pend.transaction_step_id
                   ,'P_PO_LINE_ID')
       END                                        po_line_id_p
      ,CASE WHEN pend.transaction_id IS NOT NULL
            THEN xxas_hr_api_transaction_pkg.number_value
                   (pend.person_id
                   ,pend.api_name
                   ,pend.transaction_step_id
                   ,'P_VENDOR_SITE_ID')
       END                                        vendor_site_id_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_PROJ_ASGN_END')
       END                                        projected_assignment_end_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_all_people_f          papf
      ,per_all_assignments_f     paaf
     ,(SELECT  ppf.person_id
              ,paf.assignment_id
              ,ppf.effective_start_date  per_effective_start_date
              ,ppf.effective_end_date    per_effective_end_date
              ,paf.effective_start_date asg_effective_start_date
              ,paf.effective_end_date   asg_effective_end_date
              ,hats.api_name
              ,hat.transaction_id
              ,eid.transaction_step_id
              ,'UPDATE'                                                   transaction_type
              ,hat.status                                                 hr_api_status_code
              ,xxas_selfservice_workflow_pkg.transcode_status(hat.status) workflow_status_code
        FROM   per_all_people_f          ppf
              ,per_all_assignments_f     paf
              ,hr_api_transactions       hat
              ,hr_api_transaction_steps  hats
              ,hr_api_transaction_values act
              ,hr_api_transaction_values eid
        WHERE  ppf.person_id             = hat.selected_person_id
        AND    hat.transaction_id        = hats.transaction_id
        AND    hat.status          NOT IN ('D', 'N', 'ACTIVE', 'AC')
        AND    hats.api_name             = 'HR_PROCESS_ASSIGNMENT_SS.PROCESS_API'
        AND    hats.transaction_step_id  = act.transaction_step_id
        AND    act.name                  = 'P_REVIEW_PROC_CALL'
        AND    upper(act.varchar2_value) LIKE 'HRASSIGNMENT%'
        AND    hats.transaction_step_id  = eid.transaction_step_id
        AND    eid.name                  = 'P_ASSIGNMENT_ID'
        AND    eid.number_value          = paf.assignment_id
        AND    ppf.person_id             = paf.person_id
        AND    (NVL(ppf.current_employee_flag,'N') = 'Y' OR NVL(ppf.current_npw_flag,'N') = 'Y')
        AND    xxas_mss_util_pkg.max_per_last_change_date(ppf.person_id) BETWEEN ppf.effective_start_date
                                                                         AND     ppf.effective_end_date
        AND    xxas_mss_util_pkg.max_asg_last_change_date(paf.assignment_id) BETWEEN paf.effective_start_date
                                                                             AND     paf.effective_end_date
        ) pend
      ,xxas_pg_key_ff_v       pgkf
      ,xxas_st_key_ff_v       stkf
      ,xxas_cagr_key_ff_v     cakf
      ,xxas_pg_key_ff_v       pgkp
      ,xxas_st_key_ff_v       stkp
      ,xxas_cagr_key_ff_v     cakp
WHERE  xxas_mss_util_pkg.max_per_last_change_date(papf.person_id) BETWEEN papf.effective_start_date 
                                                                  AND     papf.effective_end_date
AND    papf.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(papf.current_employee_flag,'N') = 'Y' OR NVL(papf.current_npw_flag,'N') = 'Y')
AND    paaf.people_group_id        = pgkf.people_group_id(+)
AND    paaf.soft_coding_keyflex_id = stkf.soft_coding_keyflex_id(+)
AND    paaf.cagr_grade_def_id      = cakf.cagr_grade_def_id(+)
AND    paaf.cagr_id_flex_num       = cakf.id_flex_num(+)
AND    paaf.assignment_id          = pend.assignment_id(+)
AND    (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_PEOPLE_GROUP_ID')
        END) = pgkp.people_group_id(+)
AND    (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_SOFT_CODING_KEYFLEX_ID')
        END) = stkp.soft_coding_keyflex_id(+)
AND    (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_CAGR_GRADE_DEF_ID')
        END) = cakp.cagr_grade_def_id(+)
AND    (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_CAGR_ID_FLEX_NUM')
        END) = cakp.id_flex_num(+);

BEGIN
  xxas_fnd_dictionary_pkg.add_view
   (p_application_short_name => xxas_util_pkg.app
   ,p_view_name              => 'XXAS_CHANGE_ASG_REVIEW_V'
   ,p_description            => 'Applaud Change Assignment Review View');
END;
/

COMMIT;
EXIT;

