/* $Id$ */
/*============================================================================*
 |                 Copyright (c) 2012 Applaud Solutions UK Ltd                |
 |                             All rights reserved                            |
 *===========================================================================*/

SET VERIFY OFF
SET FEEDBACK OFF
WHENEVER OSERROR  EXIT FAILURE ROLLBACK
WHENEVER SQLERROR EXIT FAILURE ROLLBACK

CREATE OR REPLACE VIEW xxas_per_people_extra_info_v
AS
SELECT ppei.person_extra_info_id
,      ppei.object_version_number
,      ppei.person_id
,      ppei.information_type
,      ppei.pei_attribute_category
,      ppei.pei_attribute1
,      ppei.pei_attribute2
,      ppei.pei_attribute3
,      ppei.pei_attribute4
,      ppei.pei_attribute5
,      ppei.pei_attribute6
,      ppei.pei_attribute7
,      ppei.pei_attribute8
,      ppei.pei_attribute9
,      ppei.pei_attribute10
,      ppei.pei_attribute11
,      ppei.pei_attribute12
,      ppei.pei_attribute13
,      ppei.pei_attribute14
,      ppei.pei_attribute15
,      ppei.pei_attribute16
,      ppei.pei_attribute17
,      ppei.pei_attribute18
,      ppei.pei_attribute19
,      ppei.pei_attribute20
,      ppei.pei_information_category
,      ppei.pei_information1
,      ppei_d.segment1_value                     pei_information1_value
,      ppei_d.segment1_meaning                   pei_information1_meaning          
,      ppei.pei_information2
,      ppei_d.segment2_value                     pei_information2_value
,      ppei_d.segment2_meaning                   pei_information2_meaning 
,      ppei.pei_information3
,      ppei_d.segment3_value                     pei_information3_value
,      ppei_d.segment3_meaning                   pei_information3_meaning 
,      ppei.pei_information4
,      ppei_d.segment4_value                     pei_information4_value
,      ppei_d.segment4_meaning                   pei_information4_meaning 
,      ppei.pei_information5
,      ppei_d.segment5_value                     pei_information5_value
,      ppei_d.segment5_meaning                   pei_information5_meaning 
,      ppei.pei_information6
,      ppei_d.segment6_value                     pei_information6_value
,      ppei_d.segment6_meaning                   pei_information6_meaning 
,      ppei.pei_information7
,      ppei_d.segment7_value                     pei_information7_value
,      ppei_d.segment7_meaning                   pei_information7_meaning 
,      ppei.pei_information8
,      ppei_d.segment8_value                     pei_information8_value
,      ppei_d.segment8_meaning                   pei_information8_meaning 
,      ppei.pei_information9
,      ppei_d.segment9_value                     pei_information9_value
,      ppei_d.segment9_meaning                   pei_information9_meaning 
,      ppei.pei_information10
,      ppei_d.segment10_value                    pei_information10_value
,      ppei_d.segment10_meaning                  pei_information10_meaning 
,      ppei.pei_information11
,      ppei_d.segment11_value                    pei_information11_value
,      ppei_d.segment11_meaning                  pei_information11_meaning 
,      ppei.pei_information12
,      ppei_d.segment12_value                    pei_information12_value
,      ppei_d.segment12_meaning                  pei_information12_meaning 
,      ppei.pei_information13
,      ppei_d.segment13_value                    pei_information13_value
,      ppei_d.segment13_meaning                  pei_information13_meaning 
,      ppei.pei_information14
,      ppei_d.segment14_value                    pei_information14_value
,      ppei_d.segment14_meaning                  pei_information14_meaning 
,      ppei.pei_information15
,      ppei_d.segment15_value                    pei_information15_value
,      ppei_d.segment15_meaning                  pei_information15_meaning 
,      ppei.pei_information16
,      ppei_d.segment16_value                    pei_information16_value
,      ppei_d.segment16_meaning                  pei_information16_meaning 
,      ppei.pei_information17
,      ppei_d.segment17_value                    pei_information17_value
,      ppei_d.segment17_meaning                  pei_information17_meaning 
,      ppei.pei_information18
,      ppei_d.segment18_value                    pei_information18_value
,      ppei_d.segment18_meaning                  pei_information18_meaning 
,      ppei.pei_information19
,      ppei_d.segment19_value                    pei_information19_value
,      ppei_d.segment19_meaning                  pei_information19_meaning 
,      ppei.pei_information20
,      ppei_d.segment20_value                    pei_information20_value
,      ppei_d.segment20_meaning                  pei_information20_meaning 
,      ppei.pei_information21
,      ppei_d.segment21_value                    pei_information21_value
,      ppei_d.segment21_meaning                  pei_information21_meaning 
,      ppei.pei_information22
,      ppei_d.segment22_value                    pei_information22_value
,      ppei_d.segment22_meaning                  pei_information22_meaning 
,      ppei.pei_information23
,      ppei_d.segment23_value                    pei_information23_value
,      ppei_d.segment23_meaning                  pei_information23_meaning 
,      ppei.pei_information24
,      ppei_d.segment24_value                    pei_information24_value
,      ppei_d.segment24_meaning                  pei_information24_meaning 
,      ppei.pei_information25
,      ppei_d.segment25_value                    pei_information25_value
,      ppei_d.segment25_meaning                  pei_information25_meaning 
,      ppei.pei_information26
,      ppei_d.segment26_value                    pei_information26_value
,      ppei_d.segment26_meaning                  pei_information26_meaning 
,      ppei.pei_information27
,      ppei_d.segment27_value                    pei_information27_value
,      ppei_d.segment27_meaning                  pei_information27_meaning 
,      ppei.pei_information28
,      ppei_d.segment28_value                    pei_information28_value
,      ppei_d.segment28_meaning                  pei_information28_meaning 
,      ppei.pei_information29
,      ppei_d.segment29_value                    pei_information29_value
,      ppei_d.segment29_meaning                  pei_information29_meaning 
,      ppei.pei_information30
,      ppei_d.segment30_value                    pei_information30_value
,      ppei_d.segment30_meaning                  pei_information30_meaning 
,      ppei.last_update_date
,      ppei.last_updated_by
,      ppei.last_update_login
,      ppei.created_by
,      ppei.creation_date
,      ppei.party_id
,      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   
,      CASE WHEN pend.transaction_type = 'UPDATE'
            THEN xxas_hr_api_transaction_pkg.number_value
                   (ppei.person_id
                   ,pend.api_name
                   ,pend.transaction_step_id
                   ,'P_OBJECT_VERSION_NUMBER_1')
       END                                       object_version_number_p
,      CASE WHEN pend.transaction_type = 'UPDATE'
            THEN xxas_hr_api_transaction_pkg.varchar2_value
                   (ppei.person_id
                   ,pend.api_name
                   ,pend.transaction_step_id
                   ,'P_INFORMATION_TYPE_1')
       END                                       pei_information_type_p
,      CASE WHEN pend.transaction_type = 'UPDATE'
            THEN xxas_hr_api_transaction_pkg.varchar2_value
                   (ppei.person_id
                   ,pend.api_name
                   ,pend.transaction_step_id
                   ,'P_ATTRIBUTE_CATEGORY_1')
       END                                       pei_attribute_category_p
,      CASE WHEN pend.transaction_type = 'UPDATE'
            THEN xxas_hr_api_transaction_pkg.varchar2_value
                   (ppei.person_id
                   ,pend.api_name
                   ,pend.transaction_step_id
                   ,'P_ATTRIBUTE1_1')
       END                                       pei_attribute1_p 
,      CASE WHEN pend.transaction_type = 'UPDATE'
            THEN xxas_hr_api_transaction_pkg.varchar2_value
                   (ppei.person_id
                   ,pend.api_name
                   ,pend.transaction_step_id
                   ,'P_ATTRIBUTE2_1')
       END                                       pei_attribute2_p 
,      CASE WHEN pend.transaction_type = 'UPDATE'
            THEN xxas_hr_api_transaction_pkg.varchar2_value
                   (ppei.person_id
                   ,pend.api_name
                   ,pend.transaction_step_id
                   ,'P_ATTRIBUTE3_1')
       END                                       pei_attribute3_p 
,      CASE WHEN pend.transaction_type = 'UPDATE'
            THEN xxas_hr_api_transaction_pkg.varchar2_value
                   (ppei.person_id
                   ,pend.api_name
                   ,pend.transaction_step_id
                   ,'P_ATTRIBUTE4_1')
       END                                       pei_attribute4_p 
,      CASE WHEN pend.transaction_type = 'UPDATE'
            THEN xxas_hr_api_transaction_pkg.varchar2_value
                   (ppei.person_id
                   ,pend.api_name
                   ,pend.transaction_step_id
                   ,'P_ATTRIBUTE5_1')
       END                                       pei_attribute5_p 
,      CASE WHEN pend.transaction_type = 'UPDATE'
            THEN xxas_hr_api_transaction_pkg.varchar2_value
                   (ppei.person_id
                   ,pend.api_name
                   ,pend.transaction_step_id
                   ,'P_ATTRIBUTE6_1')
       END                                       pei_attribute6_p 
,      CASE WHEN pend.transaction_type = 'UPDATE'
            THEN xxas_hr_api_transaction_pkg.varchar2_value
                   (ppei.person_id
                   ,pend.api_name
                   ,pend.transaction_step_id
                   ,'P_ATTRIBUTE7_1')
       END                                       pei_attribute7_p 
,      CASE WHEN pend.transaction_type = 'UPDATE'
            THEN xxas_hr_api_transaction_pkg.varchar2_value
                   (ppei.person_id
                   ,pend.api_name
                   ,pend.transaction_step_id
                   ,'P_ATTRIBUTE8_1')
       END                                       pei_attribute8_p 
,      CASE WHEN pend.transaction_type = 'UPDATE'
            THEN xxas_hr_api_transaction_pkg.varchar2_value
                   (ppei.person_id
                   ,pend.api_name
                   ,pend.transaction_step_id
                   ,'P_ATTRIBUTE9_1')
       END                                       pei_attribute9_p 
,      CASE WHEN pend.transaction_type = 'UPDATE'
            THEN xxas_hr_api_transaction_pkg.varchar2_value
                   (ppei.person_id
                   ,pend.api_name
                   ,pend.transaction_step_id
                   ,'P_ATTRIBUTE10_1')
       END                                       pei_attribute10_p 
,      CASE WHEN pend.transaction_type = 'UPDATE'
            THEN xxas_hr_api_transaction_pkg.varchar2_value
                   (ppei.person_id
                   ,pend.api_name
                   ,pend.transaction_step_id
                   ,'P_ATTRIBUTE11_1')
       END                                       pei_attribute11_p 
,      CASE WHEN pend.transaction_type = 'UPDATE'
            THEN xxas_hr_api_transaction_pkg.varchar2_value
                   (ppei.person_id
                   ,pend.api_name
                   ,pend.transaction_step_id
                   ,'P_ATTRIBUTE12_1')
       END                                       pei_attribute12_p 
,      CASE WHEN pend.transaction_type = 'UPDATE'
            THEN xxas_hr_api_transaction_pkg.varchar2_value
                   (ppei.person_id
                   ,pend.api_name
                   ,pend.transaction_step_id
                   ,'P_ATTRIBUTE13_1')
       END                                       pei_attribute13_p 
,      CASE WHEN pend.transaction_type = 'UPDATE'
            THEN xxas_hr_api_transaction_pkg.varchar2_value
                   (ppei.person_id
                   ,pend.api_name
                   ,pend.transaction_step_id
                   ,'P_ATTRIBUTE14_1')
       END                                       pei_attribute14_p 
,      CASE WHEN pend.transaction_type = 'UPDATE'
            THEN xxas_hr_api_transaction_pkg.varchar2_value
                   (ppei.person_id
                   ,pend.api_name
                   ,pend.transaction_step_id
                   ,'P_ATTRIBUTE15_1')
       END                                       pei_attribute15_p 
,      CASE WHEN pend.transaction_type = 'UPDATE'
            THEN xxas_hr_api_transaction_pkg.varchar2_value
                   (ppei.person_id
                   ,pend.api_name
                   ,pend.transaction_step_id
                   ,'P_ATTRIBUTE16_1')
       END                                       pei_attribute16_p 
,      CASE WHEN pend.transaction_type = 'UPDATE'
            THEN xxas_hr_api_transaction_pkg.varchar2_value
                   (ppei.person_id
                   ,pend.api_name
                   ,pend.transaction_step_id
                   ,'P_ATTRIBUTE17_1')
       END                                       pei_attribute17_p 
,      CASE WHEN pend.transaction_type = 'UPDATE'
            THEN xxas_hr_api_transaction_pkg.varchar2_value
                   (ppei.person_id
                   ,pend.api_name
                   ,pend.transaction_step_id
                   ,'P_ATTRIBUTE18_1')
       END                                       pei_attribute18_p 
,      CASE WHEN pend.transaction_type = 'UPDATE'
            THEN xxas_hr_api_transaction_pkg.varchar2_value
                   (ppei.person_id
                   ,pend.api_name
                   ,pend.transaction_step_id
                   ,'P_ATTRIBUTE19_1')
       END                                       pei_attribute19_p 
,      CASE WHEN pend.transaction_type = 'UPDATE'
            THEN xxas_hr_api_transaction_pkg.varchar2_value
                   (ppei.person_id
                   ,pend.api_name
                   ,pend.transaction_step_id
                   ,'P_ATTRIBUTE20_1')
       END                                       pei_attribute20_p 
,      CASE WHEN pend.transaction_type = 'UPDATE'
            THEN xxas_hr_api_transaction_pkg.varchar2_value
                   (ppei.person_id
                   ,pend.api_name
                   ,pend.transaction_step_id
                   ,'P_INFORMATION_CATEGORY_1')
       END                                       pei_information_category_p 
,      CASE WHEN pend.transaction_type = 'UPDATE'
            THEN xxas_hr_api_transaction_pkg.varchar2_value
                   (ppei.person_id
                   ,pend.api_name
                   ,pend.transaction_step_id
                   ,'P_INFORMATION1_1')
       END                                       pei_information1_p 
,      CASE WHEN pend.transaction_type = 'UPDATE'
            THEN pend_d.segment1_value
       END                                       pei_information1_value_p 
,      CASE WHEN pend.transaction_type = 'UPDATE'
            THEN pend_d.segment1_meaning
       END                                       pei_information1_meaning_p 
,      CASE WHEN pend.transaction_type = 'UPDATE'
            THEN xxas_hr_api_transaction_pkg.varchar2_value
                   (ppei.person_id
                   ,pend.api_name
                   ,pend.transaction_step_id
                   ,'P_INFORMATION2_1')
       END                                       pei_information2_p 
,      CASE WHEN pend.transaction_type = 'UPDATE'
            THEN pend_d.segment2_value
       END                                       pei_information2_value_p 
,      CASE WHEN pend.transaction_type = 'UPDATE'
            THEN pend_d.segment2_meaning
       END                                       pei_information2_meaning_p 
,      CASE WHEN pend.transaction_type = 'UPDATE'
            THEN xxas_hr_api_transaction_pkg.varchar2_value
                   (ppei.person_id
                   ,pend.api_name
                   ,pend.transaction_step_id
                   ,'P_INFORMATION3_1')
       END                                       pei_information3_p 
,      CASE WHEN pend.transaction_type = 'UPDATE'
            THEN pend_d.segment3_value
       END                                       pei_information3_value_p 
,      CASE WHEN pend.transaction_type = 'UPDATE'
            THEN pend_d.segment3_meaning
       END                                       pei_information3_meaning_p 
,      CASE WHEN pend.transaction_type = 'UPDATE'
            THEN xxas_hr_api_transaction_pkg.varchar2_value
                   (ppei.person_id
                   ,pend.api_name
                   ,pend.transaction_step_id
                   ,'P_INFORMATION4_1')
       END                                       pei_information4_p 
,      CASE WHEN pend.transaction_type = 'UPDATE'
            THEN pend_d.segment4_value
       END                                       pei_information4_value_p 
,      CASE WHEN pend.transaction_type = 'UPDATE'
            THEN pend_d.segment4_meaning
       END                                       pei_information4_meaning_p 
,      CASE WHEN pend.transaction_type = 'UPDATE'
            THEN xxas_hr_api_transaction_pkg.varchar2_value
                   (ppei.person_id
                   ,pend.api_name
                   ,pend.transaction_step_id
                   ,'P_INFORMATION5_1')
       END                                       pei_information5_p 
,      CASE WHEN pend.transaction_type = 'UPDATE'
            THEN pend_d.segment5_value
       END                                       pei_information5_value_p 
,      CASE WHEN pend.transaction_type = 'UPDATE'
            THEN pend_d.segment5_meaning
       END                                       pei_information5_meaning_p 
,      CASE WHEN pend.transaction_type = 'UPDATE'
            THEN xxas_hr_api_transaction_pkg.varchar2_value
                   (ppei.person_id
                   ,pend.api_name
                   ,pend.transaction_step_id
                   ,'P_INFORMATION6_1')
       END                                       pei_information6_p 
,      CASE WHEN pend.transaction_type = 'UPDATE'
            THEN pend_d.segment6_value
       END                                       pei_information6_value_p 
,      CASE WHEN pend.transaction_type = 'UPDATE'
            THEN pend_d.segment6_meaning
       END                                       pei_information6_meaning_p 
,      CASE WHEN pend.transaction_type = 'UPDATE'
            THEN xxas_hr_api_transaction_pkg.varchar2_value
                   (ppei.person_id
                   ,pend.api_name
                   ,pend.transaction_step_id
                   ,'P_INFORMATION7_1')
       END                                       pei_information7_p 
,      CASE WHEN pend.transaction_type = 'UPDATE'
            THEN pend_d.segment7_value
       END                                       pei_information7_value_p 
,      CASE WHEN pend.transaction_type = 'UPDATE'
            THEN pend_d.segment7_meaning
       END                                       pei_information7_meaning_p 
,      CASE WHEN pend.transaction_type = 'UPDATE'
            THEN xxas_hr_api_transaction_pkg.varchar2_value
                   (ppei.person_id
                   ,pend.api_name
                   ,pend.transaction_step_id
                   ,'P_INFORMATION8_1')
       END                                       pei_information8_p 
,      CASE WHEN pend.transaction_type = 'UPDATE'
            THEN pend_d.segment8_value
       END                                       pei_information8_value_p 
,      CASE WHEN pend.transaction_type = 'UPDATE'
            THEN pend_d.segment8_meaning
       END                                       pei_information8_meaning_p 
,      CASE WHEN pend.transaction_type = 'UPDATE'
            THEN xxas_hr_api_transaction_pkg.varchar2_value
                   (ppei.person_id
                   ,pend.api_name
                   ,pend.transaction_step_id
                   ,'P_INFORMATION9_1')
       END                                       pei_information9_p 
,      CASE WHEN pend.transaction_type = 'UPDATE'
            THEN pend_d.segment9_value
       END                                       pei_information9_value_p 
,      CASE WHEN pend.transaction_type = 'UPDATE'
            THEN pend_d.segment9_meaning
       END                                       pei_information9_meaning_p 
,      CASE WHEN pend.transaction_type = 'UPDATE'
            THEN xxas_hr_api_transaction_pkg.varchar2_value
                   (ppei.person_id
                   ,pend.api_name
                   ,pend.transaction_step_id
                   ,'P_INFORMATION10_1')
       END                                       pei_information10_p 
,      CASE WHEN pend.transaction_type = 'UPDATE'
            THEN pend_d.segment10_value
       END                                       pei_information10_value_p 
,      CASE WHEN pend.transaction_type = 'UPDATE'
            THEN pend_d.segment10_meaning
       END                                       pei_information10_meaning_p 
,      CASE WHEN pend.transaction_type = 'UPDATE'
            THEN xxas_hr_api_transaction_pkg.varchar2_value
                   (ppei.person_id
                   ,pend.api_name
                   ,pend.transaction_step_id
                   ,'P_INFORMATION11_1')
       END                                       pei_information11_p 
,      CASE WHEN pend.transaction_type = 'UPDATE'
            THEN pend_d.segment11_value
       END                                       pei_information11_value_p 
,      CASE WHEN pend.transaction_type = 'UPDATE'
            THEN pend_d.segment11_meaning
       END                                      pei_information11_meaning_p 
,      CASE WHEN pend.transaction_type = 'UPDATE'
            THEN xxas_hr_api_transaction_pkg.varchar2_value
                   (ppei.person_id
                   ,pend.api_name
                   ,pend.transaction_step_id
                   ,'P_INFORMATION12_1')
       END                                       pei_information12_p 
,      CASE WHEN pend.transaction_type = 'UPDATE'
            THEN pend_d.segment12_value
       END                                       pei_information12_value_p 
,      CASE WHEN pend.transaction_type = 'UPDATE'
            THEN pend_d.segment12_meaning
       END                                       pei_information12_meaning_p 
,      CASE WHEN pend.transaction_type = 'UPDATE'
            THEN xxas_hr_api_transaction_pkg.varchar2_value
                   (ppei.person_id
                   ,pend.api_name
                   ,pend.transaction_step_id
                   ,'P_INFORMATION13_1')
       END                                       pei_information13_p 
,      CASE WHEN pend.transaction_type = 'UPDATE'
            THEN pend_d.segment13_value
       END                                       pei_information13_value_p 
,      CASE WHEN pend.transaction_type = 'UPDATE'
            THEN pend_d.segment13_meaning
       END                                       pei_information13_meaning_p 
,      CASE WHEN pend.transaction_type = 'UPDATE'
            THEN xxas_hr_api_transaction_pkg.varchar2_value
                   (ppei.person_id
                   ,pend.api_name
                   ,pend.transaction_step_id
                   ,'P_INFORMATION14_1')
       END                                       pei_information14_p 
,      CASE WHEN pend.transaction_type = 'UPDATE'
            THEN pend_d.segment14_value
       END                                       pei_information14_value_p 
,      CASE WHEN pend.transaction_type = 'UPDATE'
            THEN pend_d.segment14_meaning
       END                                       pei_information14_meaning_p 
,      CASE WHEN pend.transaction_type = 'UPDATE'
            THEN xxas_hr_api_transaction_pkg.varchar2_value
                   (ppei.person_id
                   ,pend.api_name
                   ,pend.transaction_step_id
                   ,'P_INFORMATION15_1')
       END                                       pei_information15_p 
,      CASE WHEN pend.transaction_type = 'UPDATE'
            THEN pend_d.segment15_value
       END                                       pei_information15_value_p 
,      CASE WHEN pend.transaction_type = 'UPDATE'
            THEN pend_d.segment15_meaning
       END                                       pei_information15_meaning_p 
,      CASE WHEN pend.transaction_type = 'UPDATE'
            THEN xxas_hr_api_transaction_pkg.varchar2_value
                   (ppei.person_id
                   ,pend.api_name
                   ,pend.transaction_step_id
                   ,'P_INFORMATION16_1')
       END                                       pei_information16_p 
,      CASE WHEN pend.transaction_type = 'UPDATE'
            THEN pend_d.segment16_value
       END                                       pei_information16_value_p 
,      CASE WHEN pend.transaction_type = 'UPDATE'
            THEN pend_d.segment16_meaning
       END                                       pei_information16_meaning_p 
,      CASE WHEN pend.transaction_type = 'UPDATE'
            THEN xxas_hr_api_transaction_pkg.varchar2_value
                   (ppei.person_id
                   ,pend.api_name
                   ,pend.transaction_step_id
                   ,'P_INFORMATION17_1')
       END                                       pei_information17_p 
,      CASE WHEN pend.transaction_type = 'UPDATE'
            THEN pend_d.segment17_value
       END                                       pei_information17_value_p 
,      CASE WHEN pend.transaction_type = 'UPDATE'
            THEN pend_d.segment17_meaning
       END                                       pei_information17_meaning_p 
,      CASE WHEN pend.transaction_type = 'UPDATE'
            THEN xxas_hr_api_transaction_pkg.varchar2_value
                   (ppei.person_id
                   ,pend.api_name
                   ,pend.transaction_step_id
                   ,'P_INFORMATION18_1')
       END                                       pei_information18_p 
,      CASE WHEN pend.transaction_type = 'UPDATE'
            THEN pend_d.segment18_value
       END                                       pei_information18_value_p 
,      CASE WHEN pend.transaction_type = 'UPDATE'
            THEN pend_d.segment18_meaning
       END                                       pei_information18_meaning_p 
,      CASE WHEN pend.transaction_type = 'UPDATE'
            THEN xxas_hr_api_transaction_pkg.varchar2_value
                   (ppei.person_id
                   ,pend.api_name
                   ,pend.transaction_step_id
                   ,'P_INFORMATION19_1')
       END                                       pei_information19_p 
,      CASE WHEN pend.transaction_type = 'UPDATE'
            THEN pend_d.segment19_value
       END                                       pei_information19_value_p 
,      CASE WHEN pend.transaction_type = 'UPDATE'
            THEN pend_d.segment19_meaning
       END                                       pei_information19_meaning_p 
,      CASE WHEN pend.transaction_type = 'UPDATE'
            THEN xxas_hr_api_transaction_pkg.varchar2_value
                   (ppei.person_id
                   ,pend.api_name
                   ,pend.transaction_step_id
                   ,'P_INFORMATION20_1')
       END                                       pei_information20_p 
,      CASE WHEN pend.transaction_type = 'UPDATE'
            THEN pend_d.segment20_value
       END                                       pei_information20_value_p 
,      CASE WHEN pend.transaction_type = 'UPDATE'
            THEN pend_d.segment20_meaning
       END                                       pei_information20_meaning_p 
,      CASE WHEN pend.transaction_type = 'UPDATE'
            THEN xxas_hr_api_transaction_pkg.varchar2_value
                   (ppei.person_id
                   ,pend.api_name
                   ,pend.transaction_step_id
                   ,'P_INFORMATION21_1')
       END                                       pei_information21_p 
,      CASE WHEN pend.transaction_type = 'UPDATE'
            THEN pend_d.segment21_value
       END                                       pei_information21_value_p 
,      CASE WHEN pend.transaction_type = 'UPDATE'
            THEN pend_d.segment21_meaning
       END                                       pei_information21_meaning_p 
,      CASE WHEN pend.transaction_type = 'UPDATE'
            THEN xxas_hr_api_transaction_pkg.varchar2_value
                   (ppei.person_id
                   ,pend.api_name
                   ,pend.transaction_step_id
                   ,'P_INFORMATION22_1')
       END                                       pei_information22_p 
,      CASE WHEN pend.transaction_type = 'UPDATE'
            THEN pend_d.segment22_value
       END                                       pei_information22_value_p 
,      CASE WHEN pend.transaction_type = 'UPDATE'
            THEN pend_d.segment22_meaning
       END                                       pei_information22_meaning_p 
,      CASE WHEN pend.transaction_type = 'UPDATE'
            THEN xxas_hr_api_transaction_pkg.varchar2_value
                   (ppei.person_id
                   ,pend.api_name
                   ,pend.transaction_step_id
                   ,'P_INFORMATION23_1')
       END                                       pei_information23_p 
,      CASE WHEN pend.transaction_type = 'UPDATE'
            THEN pend_d.segment23_value
       END                                       pei_information23_value_p 
,      CASE WHEN pend.transaction_type = 'UPDATE'
            THEN pend_d.segment23_meaning
       END                                       pei_information23_meaning_p 
,      CASE WHEN pend.transaction_type = 'UPDATE'
            THEN xxas_hr_api_transaction_pkg.varchar2_value
                   (ppei.person_id
                   ,pend.api_name
                   ,pend.transaction_step_id
                   ,'P_INFORMATION24_1')
       END                                       pei_information24_p 
,      CASE WHEN pend.transaction_type = 'UPDATE'
            THEN pend_d.segment24_value
       END                                       pei_information24_value_p 
,      CASE WHEN pend.transaction_type = 'UPDATE'
            THEN pend_d.segment24_meaning
       END                                       pei_information24_meaning_p 
,      CASE WHEN pend.transaction_type = 'UPDATE'
            THEN xxas_hr_api_transaction_pkg.varchar2_value
                   (ppei.person_id
                   ,pend.api_name
                   ,pend.transaction_step_id
                   ,'P_INFORMATION25_1')
       END                                       pei_information25_p 
,      CASE WHEN pend.transaction_type = 'UPDATE'
            THEN pend_d.segment25_value
       END                                       pei_information25_value_p 
,      CASE WHEN pend.transaction_type = 'UPDATE'
            THEN pend_d.segment25_meaning
       END                                       pei_information25_meaning_p 
,      CASE WHEN pend.transaction_type = 'UPDATE'
            THEN xxas_hr_api_transaction_pkg.varchar2_value
                   (ppei.person_id
                   ,pend.api_name
                   ,pend.transaction_step_id
                   ,'P_INFORMATION26_1')
       END                                       pei_information26_p 
,      CASE WHEN pend.transaction_type = 'UPDATE'
            THEN pend_d.segment26_value
       END                                       pei_information26_value_p 
,      CASE WHEN pend.transaction_type = 'UPDATE'
            THEN pend_d.segment26_meaning
       END                                       pei_information26_meaning_p 
,      CASE WHEN pend.transaction_type = 'UPDATE'
            THEN xxas_hr_api_transaction_pkg.varchar2_value
                   (ppei.person_id
                   ,pend.api_name
                   ,pend.transaction_step_id
                   ,'P_INFORMATION27_1')
       END                                       pei_information27_p 
,      CASE WHEN pend.transaction_type = 'UPDATE'
            THEN pend_d.segment27_value
       END                                       pei_information27_value_p 
,      CASE WHEN pend.transaction_type = 'UPDATE'
            THEN pend_d.segment27_meaning
       END                                       pei_information27_meaning_p 
,      CASE WHEN pend.transaction_type = 'UPDATE'
            THEN xxas_hr_api_transaction_pkg.varchar2_value
                   (ppei.person_id
                   ,pend.api_name
                   ,pend.transaction_step_id
                   ,'P_INFORMATION28_1')
       END                                       pei_information28_p 
,      CASE WHEN pend.transaction_type = 'UPDATE'
            THEN pend_d.segment28_value
       END                                       pei_information28_value_p 
,      CASE WHEN pend.transaction_type = 'UPDATE'
            THEN pend_d.segment28_meaning
       END                                       pei_information28_meaning_p 
,      CASE WHEN pend.transaction_type = 'UPDATE'
            THEN xxas_hr_api_transaction_pkg.varchar2_value
                   (ppei.person_id
                   ,pend.api_name
                   ,pend.transaction_step_id
                   ,'P_INFORMATION29_1')
       END                                       pei_information29_p 
,      CASE WHEN pend.transaction_type = 'UPDATE'
            THEN pend_d.segment29_value
       END                                       pei_information29_value_p 
,      CASE WHEN pend.transaction_type = 'UPDATE'
            THEN pend_d.segment29_meaning
       END                                       pei_information29_meaning_p 
,      CASE WHEN pend.transaction_type = 'UPDATE'
            THEN xxas_hr_api_transaction_pkg.varchar2_value
                   (ppei.person_id
                   ,pend.api_name
                   ,pend.transaction_step_id
                   ,'P_INFORMATION30_1')
       END                                       pei_information30_p 
,      CASE WHEN pend.transaction_type = 'UPDATE'
            THEN pend_d.segment30_value
       END                                       pei_information30_value_p 
,      CASE WHEN pend.transaction_type = 'UPDATE'
            THEN pend_d.segment30_meaning
       END                                       pei_information30_meaning_p 
,      'PQH_SS_ATTACHMENT'                       attachment_entity
,      pend.transaction_id                       attachment_pk1 
FROM   per_people_extra_info ppei
,      (SELECT ppeip.person_id
        ,      ppeip.person_extra_info_id
        ,      hats.api_name
        ,      hat.transaction_id
        ,      eid.transaction_step_id
        ,      SUBSTR(act.varchar2_value 
                     ,1, INSTR(act.varchar2_value , '_')-1)          transaction_type
        ,      hat.status                                            hr_api_status_code
        ,      xxas_selfservice_workflow_pkg.transcode_status(hat.status) workflow_status_code
        FROM   per_people_extra_info     ppeip
        ,      hr_api_transactions       hat
        ,      hr_api_transaction_steps  hats
        ,      hr_api_transaction_values act
        ,      hr_api_transaction_values eid
        ,      hr_api_transaction_values bad
        WHERE  ppeip.person_id           = hat.selected_person_id
        AND    hat.status          NOT IN ('D', 'N', 'W', 'ACTIVE', 'AC')
        AND    hat.transaction_id       = hats.transaction_id
        AND    hats.api_name            = 'HR_PROCESS_EIT_SS.PROCESS_API'
        AND    hats.transaction_step_id = act.transaction_step_id
        AND    act.name                 = 'P_ACTION_1'
        AND    act.varchar2_value      IN ('UPDATE_ROW', 'DELETE_ROW')
        AND    hats.transaction_step_id = eid.transaction_step_id
        AND    eid.name                 = 'P_EXTRA_INFO_ID_1'
        AND    eid.number_value         = ppeip.person_extra_info_id
        AND    hats.transaction_step_id = bad.transaction_step_id(+)
        AND    bad.name(+)              = 'P_ACTION_2'
        AND    bad.varchar2_value      IS NULL ) pend
,      TABLE(xxas_com_flex_pkg.dff_value_set_details
                (800
                ,'Extra Person Info DDF'
                ,ppei.pei_information_category
                ,'PEI_INFORMATION1'
                ,ppei.pei_information1
                ,'PEI_INFORMATION2'
                ,ppei.pei_information2
                ,'PEI_INFORMATION3'
                ,ppei.pei_information3
                ,'PEI_INFORMATION4'
                ,ppei.pei_information4
                ,'PEI_INFORMATION5'
                ,ppei.pei_information5
                ,'PEI_INFORMATION6'
                ,ppei.pei_information6
                ,'PEI_INFORMATION7'
                ,ppei.pei_information7
                ,'PEI_INFORMATION8'
                ,ppei.pei_information8
                ,'PEI_INFORMATION9'
                ,ppei.pei_information9
                ,'PEI_INFORMATION10'
                ,ppei.pei_information10
                ,'PEI_INFORMATION11'
                ,ppei.pei_information11
                ,'PEI_INFORMATION12'
                ,ppei.pei_information12
                ,'PEI_INFORMATION13'
                ,ppei.pei_information13
                ,'PEI_INFORMATION14'
                ,ppei.pei_information14
                ,'PEI_INFORMATION15'
                ,ppei.pei_information15
                ,'PEI_INFORMATION16'
                ,ppei.pei_information16
                ,'PEI_INFORMATION17'
                ,ppei.pei_information17
                ,'PEI_INFORMATION18'
                ,ppei.pei_information18
                ,'PEI_INFORMATION19'
                ,ppei.pei_information19
                ,'PEI_INFORMATION20'
                ,ppei.pei_information20
                ,'PEI_INFORMATION21'
                ,ppei.pei_information21
                ,'PEI_INFORMATION22'
                ,ppei.pei_information22
                ,'PEI_INFORMATION23'
                ,ppei.pei_information23
                ,'PEI_INFORMATION24'
                ,ppei.pei_information24
                ,'PEI_INFORMATION25'
                ,ppei.pei_information25
                ,'PEI_INFORMATION26'
                ,ppei.pei_information26
                ,'PEI_INFORMATION27'
                ,ppei.pei_information27
                ,'PEI_INFORMATION28'
                ,ppei.pei_information28
                ,'PEI_INFORMATION29'
                ,ppei.pei_information29
                ,'PEI_INFORMATION30'
                ,ppei.pei_information30)) ppei_d        
,      TABLE(xxas_com_flex_pkg.dff_value_set_details
                (800
                ,'Extra Person Info DDF'
                ,ppei.pei_information_category
                ,'PEI_INFORMATION1'
                ,xxas_hr_api_transaction_pkg.varchar2_value
                      (ppei.person_id
                      ,pend.api_name
                      ,pend.transaction_step_id
                      ,'P_INFORMATION1_1')
                ,'PEI_INFORMATION2'
                ,xxas_hr_api_transaction_pkg.varchar2_value
                      (ppei.person_id
                      ,pend.api_name
                      ,pend.transaction_step_id
                      ,'P_INFORMATION2_1')
                ,'PEI_INFORMATION3'
                ,xxas_hr_api_transaction_pkg.varchar2_value
                      (ppei.person_id
                      ,pend.api_name
                      ,pend.transaction_step_id
                      ,'P_INFORMATION3_1')
                ,'PEI_INFORMATION4'
                ,xxas_hr_api_transaction_pkg.varchar2_value
                      (ppei.person_id
                      ,pend.api_name
                      ,pend.transaction_step_id
                      ,'P_INFORMATION4_1')
                ,'PEI_INFORMATION5'
                ,xxas_hr_api_transaction_pkg.varchar2_value
                      (ppei.person_id
                      ,pend.api_name
                      ,pend.transaction_step_id
                      ,'P_INFORMATION5_1')
                ,'PEI_INFORMATION6'
                ,xxas_hr_api_transaction_pkg.varchar2_value
                      (ppei.person_id
                      ,pend.api_name
                      ,pend.transaction_step_id
                      ,'P_INFORMATION6_1')
                ,'PEI_INFORMATION7'
                ,xxas_hr_api_transaction_pkg.varchar2_value
                      (ppei.person_id
                      ,pend.api_name
                      ,pend.transaction_step_id
                      ,'P_INFORMATION7_1')
                ,'PEI_INFORMATION8'
                ,xxas_hr_api_transaction_pkg.varchar2_value
                      (ppei.person_id
                      ,pend.api_name
                      ,pend.transaction_step_id
                      ,'P_INFORMATION8_1')
                ,'PEI_INFORMATION9'
                ,xxas_hr_api_transaction_pkg.varchar2_value
                      (ppei.person_id
                      ,pend.api_name
                      ,pend.transaction_step_id
                      ,'P_INFORMATION9_1')
                ,'PEI_INFORMATION10'
                ,xxas_hr_api_transaction_pkg.varchar2_value
                      (ppei.person_id
                      ,pend.api_name
                      ,pend.transaction_step_id
                      ,'P_INFORMATION10_1')
                ,'PEI_INFORMATION11'
                ,xxas_hr_api_transaction_pkg.varchar2_value
                      (ppei.person_id
                      ,pend.api_name
                      ,pend.transaction_step_id
                      ,'P_INFORMATION11_1')
                ,'PEI_INFORMATION12'
                ,xxas_hr_api_transaction_pkg.varchar2_value
                      (ppei.person_id
                      ,pend.api_name
                      ,pend.transaction_step_id
                      ,'P_INFORMATION12_1')
                ,'PEI_INFORMATION13'
                ,xxas_hr_api_transaction_pkg.varchar2_value
                      (ppei.person_id
                      ,pend.api_name
                      ,pend.transaction_step_id
                      ,'P_INFORMATION13_1')
                ,'PEI_INFORMATION14'
                ,xxas_hr_api_transaction_pkg.varchar2_value
                      (ppei.person_id
                      ,pend.api_name
                      ,pend.transaction_step_id
                      ,'P_INFORMATION14_1')
                ,'PEI_INFORMATION15'
                ,xxas_hr_api_transaction_pkg.varchar2_value
                      (ppei.person_id
                      ,pend.api_name
                      ,pend.transaction_step_id
                      ,'P_INFORMATION15_1')
                ,'PEI_INFORMATION16'
                ,xxas_hr_api_transaction_pkg.varchar2_value
                      (ppei.person_id
                      ,pend.api_name
                      ,pend.transaction_step_id
                      ,'P_INFORMATION16_1')
                ,'PEI_INFORMATION17'
                ,xxas_hr_api_transaction_pkg.varchar2_value
                      (ppei.person_id
                      ,pend.api_name
                      ,pend.transaction_step_id
                      ,'P_INFORMATION17_1')
                ,'PEI_INFORMATION18'
                ,xxas_hr_api_transaction_pkg.varchar2_value
                      (ppei.person_id
                      ,pend.api_name
                      ,pend.transaction_step_id
                      ,'P_INFORMATION18_1')
                ,'PEI_INFORMATION19'
                ,xxas_hr_api_transaction_pkg.varchar2_value
                      (ppei.person_id
                      ,pend.api_name
                      ,pend.transaction_step_id
                      ,'P_INFORMATION19_1')
                ,'PEI_INFORMATION20'
                ,xxas_hr_api_transaction_pkg.varchar2_value
                      (ppei.person_id
                      ,pend.api_name
                      ,pend.transaction_step_id
                      ,'P_INFORMATION20_1')
                ,'PEI_INFORMATION21'
                ,xxas_hr_api_transaction_pkg.varchar2_value
                      (ppei.person_id
                      ,pend.api_name
                      ,pend.transaction_step_id
                      ,'P_INFORMATION21_1')
                ,'PEI_INFORMATION22'
                ,xxas_hr_api_transaction_pkg.varchar2_value
                      (ppei.person_id
                      ,pend.api_name
                      ,pend.transaction_step_id
                      ,'P_INFORMATION22_1')
                ,'PEI_INFORMATION23'
                ,xxas_hr_api_transaction_pkg.varchar2_value
                      (ppei.person_id
                      ,pend.api_name
                      ,pend.transaction_step_id
                      ,'P_INFORMATION23_1')
                ,'PEI_INFORMATION24'
                ,xxas_hr_api_transaction_pkg.varchar2_value
                      (ppei.person_id
                      ,pend.api_name
                      ,pend.transaction_step_id
                      ,'P_INFORMATION24_1')
                ,'PEI_INFORMATION25'
                ,xxas_hr_api_transaction_pkg.varchar2_value
                      (ppei.person_id
                      ,pend.api_name
                      ,pend.transaction_step_id
                      ,'P_INFORMATION25_1')
                ,'PEI_INFORMATION26'
                ,xxas_hr_api_transaction_pkg.varchar2_value
                      (ppei.person_id
                      ,pend.api_name
                      ,pend.transaction_step_id
                      ,'P_INFORMATION26_1')
                ,'PEI_INFORMATION27'
                ,xxas_hr_api_transaction_pkg.varchar2_value
                      (ppei.person_id
                      ,pend.api_name
                      ,pend.transaction_step_id
                      ,'P_INFORMATION27_1')
                ,'PEI_INFORMATION28'
                ,xxas_hr_api_transaction_pkg.varchar2_value
                      (ppei.person_id
                      ,pend.api_name
                      ,pend.transaction_step_id
                      ,'P_INFORMATION28_1')
                ,'PEI_INFORMATION29'
                ,xxas_hr_api_transaction_pkg.varchar2_value
                      (ppei.person_id
                      ,pend.api_name
                      ,pend.transaction_step_id
                      ,'P_INFORMATION29_1')
                ,'PEI_INFORMATION30'
                ,xxas_hr_api_transaction_pkg.varchar2_value
                      (ppei.person_id
                      ,pend.api_name
                      ,pend.transaction_step_id
                      ,'P_INFORMATION30_1'))) pend_d        
WHERE  ppei.person_extra_info_id = pend.person_extra_info_id(+)
AND    ppei.person_id            = pend.person_id(+) 
UNION ALL
SELECT NULL                                      person_extra_info_id
,      NULL                                      object_version_number
,      hat.selected_person_id                    person_id
,      xxas_hr_api_transaction_pkg.varchar2_value
         (hat.selected_person_id
         ,hats.api_name
         ,hats.transaction_step_id
         ,'P_INFORMATION_TYPE_1')                information_type
,      NULL                                      pei_attribute_category
,      NULL                                      pei_attribute1
,      NULL                                      pei_attribute2
,      NULL                                      pei_attribute3
,      NULL                                      pei_attribute4
,      NULL                                      pei_attribute5
,      NULL                                      pei_attribute6
,      NULL                                      pei_attribute7
,      NULL                                      pei_attribute8
,      NULL                                      pei_attribute9
,      NULL                                      pei_attribute10
,      NULL                                      pei_attribute11
,      NULL                                      pei_attribute12
,      NULL                                      pei_attribute13
,      NULL                                      pei_attribute14
,      NULL                                      pei_attribute15
,      NULL                                      pei_attribute16
,      NULL                                      pei_attribute17
,      NULL                                      pei_attribute18
,      NULL                                      pei_attribute19
,      NULL                                      pei_attribute20
,      NULL                                      pei_information_category
,      NULL                                      pei_information1
,      NULL                                      pei_information1_value
,      NULL                                      pei_information1_meaning
,      NULL                                      pei_information2
,      NULL                                      pei_information2_value
,      NULL                                      pei_information2_meaning
,      NULL                                      pei_information3
,      NULL                                      pei_information3_value
,      NULL                                      pei_information3_meaning
,      NULL                                      pei_information4
,      NULL                                      pei_information4_value
,      NULL                                      pei_information4_meaning
,      NULL                                      pei_information5
,      NULL                                      pei_information5_value
,      NULL                                      pei_information5_meaning
,      NULL                                      pei_information6
,      NULL                                      pei_information6_value
,      NULL                                      pei_information6_meaning
,      NULL                                      pei_information7
,      NULL                                      pei_information7_value
,      NULL                                      pei_information7_meaning
,      NULL                                      pei_information8
,      NULL                                      pei_information8_value
,      NULL                                      pei_information8_meaning
,      NULL                                      pei_information9
,      NULL                                      pei_information9_value
,      NULL                                      pei_information9_meaning
,      NULL                                      pei_information10
,      NULL                                      pei_information10_value
,      NULL                                      pei_information10_meaning
,      NULL                                      pei_information11
,      NULL                                      pei_information11_value
,      NULL                                      pei_information11_meaning
,      NULL                                      pei_information12
,      NULL                                      pei_information12_value
,      NULL                                      pei_information12_meaning
,      NULL                                      pei_information13
,      NULL                                      pei_information13_value
,      NULL                                      pei_information13_meaning
,      NULL                                      pei_information14
,      NULL                                      pei_information14_value
,      NULL                                      pei_information14_meaning
,      NULL                                      pei_information15
,      NULL                                      pei_information15_value
,      NULL                                      pei_information15_meaning
,      NULL                                      pei_information16
,      NULL                                      pei_information16_value
,      NULL                                      pei_information16_meaning
,      NULL                                      pei_information17
,      NULL                                      pei_information17_value
,      NULL                                      pei_information17_meaning
,      NULL                                      pei_information18
,      NULL                                      pei_information18_value
,      NULL                                      pei_information18_meaning
,      NULL                                      pei_information19
,      NULL                                      pei_information19_value
,      NULL                                      pei_information19_meaning
,      NULL                                      pei_information20
,      NULL                                      pei_information20_value
,      NULL                                      pei_information20_meaning
,      NULL                                      pei_information21
,      NULL                                      pei_information21_value
,      NULL                                      pei_information21_meaning
,      NULL                                      pei_information22
,      NULL                                      pei_information22_value
,      NULL                                      pei_information22_meaning
,      NULL                                      pei_information23
,      NULL                                      pei_information23_value
,      NULL                                      pei_information23_meaning
,      NULL                                      pei_information24
,      NULL                                      pei_information24_value
,      NULL                                      pei_information24_meaning
,      NULL                                      pei_information25
,      NULL                                      pei_information25_value
,      NULL                                      pei_information25_meaning
,      NULL                                      pei_information26
,      NULL                                      pei_information26_value
,      NULL                                      pei_information26_meaning
,      NULL                                      pei_information27
,      NULL                                      pei_information27_value
,      NULL                                      pei_information27_meaning
,      NULL                                      pei_information28
,      NULL                                      pei_information28_value
,      NULL                                      pei_information28_meaning
,      NULL                                      pei_information29
,      NULL                                      pei_information29_value
,      NULL                                      pei_information29_meaning
,      NULL                                      pei_information30
,      NULL                                      pei_information30_value
,      NULL                                      pei_information30_meaning
,      NULL                                      last_update_date
,      NULL                                      last_updated_by
,      NULL                                      last_update_login
,      NULL                                      created_by
,      NULL                                      creation_date
,      NULL                                      party_id
,      hat.transaction_id
,      cat.transaction_step_id 
,      'INSERT'                                  transaction_type
,      hat.status                                hr_api_status_code
,      xxas_selfservice_workflow_pkg.transcode_status
         (hat.status)                            workflow_status_code
,      xxas_selfservice_workflow_pkg.decode_status
         (xxas_selfservice_workflow_pkg.transcode_status
         (hat.status))                           workflow_status_meaning   
,      xxas_hr_api_transaction_pkg.number_value
                   (hat.selected_person_id
                   ,hats.api_name
                   ,hats.transaction_step_id
                   ,'P_OBJECT_VERSION_NUMBER_1') object_version_number_p
,      xxas_hr_api_transaction_pkg.varchar2_value
                   (hat.selected_person_id
                   ,hats.api_name
                   ,hats.transaction_step_id
                   ,'P_INFORMATION_TYPE_1')      information_type_p
,      xxas_hr_api_transaction_pkg.varchar2_value
                   (hat.selected_person_id
                   ,hats.api_name
                   ,hats.transaction_step_id
                   ,'P_ATTRIBUTE_CATEGORY_1')    pei_attribute_category_p
,      xxas_hr_api_transaction_pkg.varchar2_value
                   (hat.selected_person_id
                   ,hats.api_name
                   ,hats.transaction_step_id
                   ,'P_ATTRIBUTE1_1')            pei_attribute1_p 
,      xxas_hr_api_transaction_pkg.varchar2_value
                   (hat.selected_person_id
                   ,hats.api_name
                   ,hats.transaction_step_id
                   ,'P_ATTRIBUTE2_1')            pei_attribute2_p 
,      xxas_hr_api_transaction_pkg.varchar2_value
                   (hat.selected_person_id
                   ,hats.api_name
                   ,hats.transaction_step_id
                   ,'P_ATTRIBUTE3_1')            pei_attribute3_p 
,      xxas_hr_api_transaction_pkg.varchar2_value
                   (hat.selected_person_id
                   ,hats.api_name
                   ,hats.transaction_step_id
                   ,'P_ATTRIBUTE4_1')            pei_attribute4_p 
,      xxas_hr_api_transaction_pkg.varchar2_value
                   (hat.selected_person_id
                   ,hats.api_name
                   ,hats.transaction_step_id
                   ,'P_ATTRIBUTE5_1')            pei_attribute5_p 
,      xxas_hr_api_transaction_pkg.varchar2_value
                   (hat.selected_person_id
                   ,hats.api_name
                   ,hats.transaction_step_id
                   ,'P_ATTRIBUTE6_1')            pei_attribute6_p 
,      xxas_hr_api_transaction_pkg.varchar2_value
                   (hat.selected_person_id
                   ,hats.api_name
                   ,hats.transaction_step_id
                   ,'P_ATTRIBUTE7_1')            pei_attribute7_p 
,      xxas_hr_api_transaction_pkg.varchar2_value
                   (hat.selected_person_id
                   ,hats.api_name
                   ,hats.transaction_step_id
                   ,'P_ATTRIBUTE8_1')            pei_attribute8_p 
,      xxas_hr_api_transaction_pkg.varchar2_value
                   (hat.selected_person_id
                   ,hats.api_name
                   ,hats.transaction_step_id
                   ,'P_ATTRIBUTE9_1')            pei_attribute9_p 
,      xxas_hr_api_transaction_pkg.varchar2_value
                   (hat.selected_person_id
                   ,hats.api_name
                   ,hats.transaction_step_id
                   ,'P_ATTRIBUTE10_1')           pei_attribute10_p 
,      xxas_hr_api_transaction_pkg.varchar2_value
                   (hat.selected_person_id
                   ,hats.api_name
                   ,hats.transaction_step_id
                   ,'P_ATTRIBUTE11_1')           pei_attribute11_p 
,      xxas_hr_api_transaction_pkg.varchar2_value
                   (hat.selected_person_id
                   ,hats.api_name
                   ,hats.transaction_step_id
                   ,'P_ATTRIBUTE12_1')           pei_attribute12_p 
,      xxas_hr_api_transaction_pkg.varchar2_value
                   (hat.selected_person_id
                   ,hats.api_name
                   ,hats.transaction_step_id
                   ,'P_ATTRIBUTE13_1')           pei_attribute13_p 
,      xxas_hr_api_transaction_pkg.varchar2_value
                   (hat.selected_person_id
                   ,hats.api_name
                   ,hats.transaction_step_id
                   ,'P_ATTRIBUTE14_1')           pei_attribute14_p 
,      xxas_hr_api_transaction_pkg.varchar2_value
                   (hat.selected_person_id
                   ,hats.api_name
                   ,hats.transaction_step_id
                   ,'P_ATTRIBUTE15_1')           pei_attribute15_p 
,      xxas_hr_api_transaction_pkg.varchar2_value
                   (hat.selected_person_id
                   ,hats.api_name
                   ,hats.transaction_step_id
                   ,'P_ATTRIBUTE16_1')           pei_attribute16_p 
,      xxas_hr_api_transaction_pkg.varchar2_value
                   (hat.selected_person_id
                   ,hats.api_name
                   ,hats.transaction_step_id
                   ,'P_ATTRIBUTE17_1')           pei_attribute17_p 
,      xxas_hr_api_transaction_pkg.varchar2_value
                   (hat.selected_person_id
                   ,hats.api_name
                   ,hats.transaction_step_id
                   ,'P_ATTRIBUTE18_1')           pei_attribute18_p 
,      xxas_hr_api_transaction_pkg.varchar2_value
                   (hat.selected_person_id
                   ,hats.api_name
                   ,hats.transaction_step_id
                   ,'P_ATTRIBUTE19_1')           pei_attribute19_p 
,      xxas_hr_api_transaction_pkg.varchar2_value
                   (hat.selected_person_id
                   ,hats.api_name
                   ,hats.transaction_step_id
                   ,'P_ATTRIBUTE20_1')           pei_attribute20_p 
,      xxas_hr_api_transaction_pkg.varchar2_value
                   (hat.selected_person_id
                   ,hats.api_name
                   ,hats.transaction_step_id
                   ,'P_INFORMATION_CATEGORY_1')  pei_information_category_p 
,      xxas_hr_api_transaction_pkg.varchar2_value
                   (hat.selected_person_id
                   ,hats.api_name
                   ,hats.transaction_step_id
                   ,'P_INFORMATION1_1')          pei_information1_p 
,      pend_d.segment1_value                     pei_information1_value_p 
,      pend_d.segment1_meaning                   pei_information1_meaning_p 
,      xxas_hr_api_transaction_pkg.varchar2_value
                   (hat.selected_person_id
                   ,hats.api_name
                   ,hats.transaction_step_id
                   ,'P_INFORMATION2_1')          pei_information2_p 
,      pend_d.segment2_value                     pei_information2_value_p 
,      pend_d.segment2_meaning                   pei_information2_meaning_p 
,      xxas_hr_api_transaction_pkg.varchar2_value
                   (hat.selected_person_id
                   ,hats.api_name
                   ,hats.transaction_step_id
                   ,'P_INFORMATION3_1')          pei_information3_p 
,      pend_d.segment3_value                     pei_information3_value_p 
,      pend_d.segment3_meaning                   pei_information3_meaning_p 
,      xxas_hr_api_transaction_pkg.varchar2_value
                   (hat.selected_person_id
                   ,hats.api_name
                   ,hats.transaction_step_id
                   ,'P_INFORMATION4_1')          pei_information4_p 
,      pend_d.segment4_value                     pei_information4_value_p 
,      pend_d.segment4_meaning                   pei_information4_meaning_p 
,      xxas_hr_api_transaction_pkg.varchar2_value
                   (hat.selected_person_id
                   ,hats.api_name
                   ,hats.transaction_step_id
                   ,'P_INFORMATION5_1')          pei_information5_p 
,      pend_d.segment5_value                     pei_information5_value_p 
,      pend_d.segment5_meaning                   pei_information5_meaning_p 
,      xxas_hr_api_transaction_pkg.varchar2_value
                   (hat.selected_person_id
                   ,hats.api_name
                   ,hats.transaction_step_id
                   ,'P_INFORMATION6_1')          pei_information6_p 
,      pend_d.segment6_value                     pei_information6_value_p 
,      pend_d.segment6_meaning                   pei_information6_meaning_p 
,      xxas_hr_api_transaction_pkg.varchar2_value
                   (hat.selected_person_id
                   ,hats.api_name
                   ,hats.transaction_step_id
                   ,'P_INFORMATION7_1')          pei_information7_p 
,      pend_d.segment7_value                     pei_information7_value_p 
,      pend_d.segment7_meaning                   pei_information7_meaning_p 
,      xxas_hr_api_transaction_pkg.varchar2_value
                   (hat.selected_person_id
                   ,hats.api_name
                   ,hats.transaction_step_id
                   ,'P_INFORMATION8_1')          pei_information8_p 
,      pend_d.segment8_value                     pei_information8_value_p 
,      pend_d.segment8_meaning                   pei_information8_meaning_p 
,      xxas_hr_api_transaction_pkg.varchar2_value
                   (hat.selected_person_id
                   ,hats.api_name
                   ,hats.transaction_step_id
                   ,'P_INFORMATION9_1')          pei_information9_p 
,      pend_d.segment9_value                     pei_information9_value_p 
,      pend_d.segment9_meaning                   pei_information9_meaning_p 
,      xxas_hr_api_transaction_pkg.varchar2_value
                   (hat.selected_person_id
                   ,hats.api_name
                   ,hats.transaction_step_id
                   ,'P_INFORMATION10_1')         pei_information10_p 
,      pend_d.segment10_value                    pei_information10_value_p 
,      pend_d.segment10_meaning                  pei_information10_meaning_p 
,      xxas_hr_api_transaction_pkg.varchar2_value
                   (hat.selected_person_id
                   ,hats.api_name
                   ,hats.transaction_step_id
                   ,'P_INFORMATION11_1')         pei_information11_p 
,      pend_d.segment11_value                    pei_information11_value_p 
,      pend_d.segment11_meaning                  pei_information11_meaning_p 
,      xxas_hr_api_transaction_pkg.varchar2_value
                   (hat.selected_person_id
                   ,hats.api_name
                   ,hats.transaction_step_id
                   ,'P_INFORMATION12_1')         pei_information12_p 
,      pend_d.segment12_value                    pei_information12_value_p 
,      pend_d.segment12_meaning                  pei_information12_meaning_p 
,      xxas_hr_api_transaction_pkg.varchar2_value
                   (hat.selected_person_id
                   ,hats.api_name
                   ,hats.transaction_step_id
                   ,'P_INFORMATION13_1')         pei_information13_p 
,      pend_d.segment13_value                    pei_information13_value_p 
,      pend_d.segment13_meaning                  pei_information13_meaning_p 
,      xxas_hr_api_transaction_pkg.varchar2_value
                   (hat.selected_person_id
                   ,hats.api_name
                   ,hats.transaction_step_id
                   ,'P_INFORMATION14_1')         pei_information14_p 
,      pend_d.segment14_value                    pei_information14_value_p 
,      pend_d.segment14_meaning                  pei_information14_meaning_p 
,      xxas_hr_api_transaction_pkg.varchar2_value
                   (hat.selected_person_id
                   ,hats.api_name
                   ,hats.transaction_step_id
                   ,'P_INFORMATION15_1')         pei_information15_p 
,      pend_d.segment15_value                    pei_information15_value_p 
,      pend_d.segment15_meaning                  pei_information15_meaning_p 
,      xxas_hr_api_transaction_pkg.varchar2_value
                   (hat.selected_person_id
                   ,hats.api_name
                   ,hats.transaction_step_id
                   ,'P_INFORMATION16_1')         pei_information16_p 
,      pend_d.segment16_value                    pei_information16_value_p 
,      pend_d.segment16_meaning                  pei_information16_meaning_p 
,      xxas_hr_api_transaction_pkg.varchar2_value
                   (hat.selected_person_id
                   ,hats.api_name
                   ,hats.transaction_step_id
                   ,'P_INFORMATION17_1')         pei_information17_p 
,      pend_d.segment17_value                    pei_information17_value_p 
,      pend_d.segment17_meaning                  pei_information17_meaning_p 
,      xxas_hr_api_transaction_pkg.varchar2_value
                   (hat.selected_person_id
                   ,hats.api_name
                   ,hats.transaction_step_id
                   ,'P_INFORMATION18_1')         pei_information18_p 
,      pend_d.segment18_value                    pei_information18_value_p 
,      pend_d.segment18_meaning                  pei_information18_meaning_p 
,      xxas_hr_api_transaction_pkg.varchar2_value
                   (hat.selected_person_id
                   ,hats.api_name
                   ,hats.transaction_step_id
                   ,'P_INFORMATION19_1')         pei_information19_p 
,      pend_d.segment19_value                    pei_information19_value_p 
,      pend_d.segment19_meaning                  pei_information19_meaning_p 
,      xxas_hr_api_transaction_pkg.varchar2_value
                   (hat.selected_person_id
                   ,hats.api_name
                   ,hats.transaction_step_id
                   ,'P_INFORMATION20_1')         pei_information20_p 
,      pend_d.segment20_value                    pei_information20_value_p 
,      pend_d.segment20_meaning                  pei_information20_meaning_p 
,      xxas_hr_api_transaction_pkg.varchar2_value
                   (hat.selected_person_id
                   ,hats.api_name
                   ,hats.transaction_step_id
                   ,'P_INFORMATION21_1')         pei_information21_p 
,      pend_d.segment21_value                    pei_information21_value_p 
,      pend_d.segment21_meaning                  pei_information21_meaning_p 
,      xxas_hr_api_transaction_pkg.varchar2_value
                   (hat.selected_person_id
                   ,hats.api_name
                   ,hats.transaction_step_id
                   ,'P_INFORMATION22_1')         pei_information22_p 
,      pend_d.segment22_value                    pei_information22_value_p 
,      pend_d.segment22_meaning                  pei_information22_meaning_p 
,      xxas_hr_api_transaction_pkg.varchar2_value
                   (hat.selected_person_id
                   ,hats.api_name
                   ,hats.transaction_step_id
                   ,'P_INFORMATION23_1')         pei_information23_p 
,      pend_d.segment23_value                    pei_information23_value_p 
,      pend_d.segment23_meaning                  pei_information23_meaning_p 
,      xxas_hr_api_transaction_pkg.varchar2_value
                   (hat.selected_person_id
                   ,hats.api_name
                   ,hats.transaction_step_id
                   ,'P_INFORMATION24_1')         pei_information24_p 
,      pend_d.segment24_value                    pei_information24_value_p 
,      pend_d.segment24_meaning                  pei_information24_meaning_p 
,      xxas_hr_api_transaction_pkg.varchar2_value
                   (hat.selected_person_id
                   ,hats.api_name
                   ,hats.transaction_step_id
                   ,'P_INFORMATION25_1')         pei_information25_p 
,      pend_d.segment25_value                    pei_information25_value_p 
,      pend_d.segment25_meaning                  pei_information25_meaning_p 
,      xxas_hr_api_transaction_pkg.varchar2_value
                   (hat.selected_person_id
                   ,hats.api_name
                   ,hats.transaction_step_id
                   ,'P_INFORMATION26_1')         pei_information26_p 
,      pend_d.segment26_value                    pei_information26_value_p 
,      pend_d.segment26_meaning                  pei_information26_meaning_p 
,      xxas_hr_api_transaction_pkg.varchar2_value
                   (hat.selected_person_id
                   ,hats.api_name
                   ,hats.transaction_step_id
                   ,'P_INFORMATION27_1')         pei_information27_p 
,      pend_d.segment27_value                    pei_information27_value_p 
,      pend_d.segment27_meaning                  pei_information27_meaning_p 
,      xxas_hr_api_transaction_pkg.varchar2_value
                   (hat.selected_person_id
                   ,hats.api_name
                   ,hats.transaction_step_id
                   ,'P_INFORMATION28_1')         pei_information28_p 
,      pend_d.segment28_value                    pei_information28_value_p 
,      pend_d.segment28_meaning                  pei_information28_meaning_p 
,      xxas_hr_api_transaction_pkg.varchar2_value
                   (hat.selected_person_id
                   ,hats.api_name
                   ,hats.transaction_step_id
                   ,'P_INFORMATION29_1')         pei_information29_p 
,      pend_d.segment29_value                    pei_information29_value_p 
,      pend_d.segment29_meaning                  pei_information29_meaning_p 
,      xxas_hr_api_transaction_pkg.varchar2_value
                   (hat.selected_person_id
                   ,hats.api_name
                   ,hats.transaction_step_id
                   ,'P_INFORMATION30_1')         pei_information30_p 
,      pend_d.segment30_value                    pei_information30_value_p 
,      pend_d.segment30_meaning                  pei_information30_meaning_p 
,     'PQH_SS_ATTACHMENT'                        attachment_entity
,      hat.transaction_id                        attachment_pk1
FROM   hr_api_transactions       hat
,      hr_api_transaction_steps  hats
,      hr_api_transaction_values act
,      hr_api_transaction_values cat
,      hr_api_transaction_values bad
,      TABLE(xxas_com_flex_pkg.dff_value_set_details
                (800
                ,'Extra Person Info DDF'
                ,xxas_hr_api_transaction_pkg.varchar2_value
                   (hat.selected_person_id
                   ,hats.api_name
                   ,hats.transaction_step_id
                   ,'P_INFORMATION_CATEGORY_1')
                ,'PEI_INFORMATION1'
                ,xxas_hr_api_transaction_pkg.varchar2_value
                      (hat.selected_person_id
                      ,hats.api_name
                      ,hats.transaction_step_id
                      ,'P_INFORMATION1_1')
                ,'PEI_INFORMATION2'
                ,xxas_hr_api_transaction_pkg.varchar2_value
                      (hat.selected_person_id
                      ,hats.api_name
                      ,hats.transaction_step_id
                      ,'P_INFORMATION2_1')
                ,'PEI_INFORMATION3'
                ,xxas_hr_api_transaction_pkg.varchar2_value
                      (hat.selected_person_id
                      ,hats.api_name
                      ,hats.transaction_step_id
                      ,'P_INFORMATION3_1')
                ,'PEI_INFORMATION4'
                ,xxas_hr_api_transaction_pkg.varchar2_value
                      (hat.selected_person_id
                      ,hats.api_name
                      ,hats.transaction_step_id
                      ,'P_INFORMATION4_1')
                ,'PEI_INFORMATION5'
                ,xxas_hr_api_transaction_pkg.varchar2_value
                      (hat.selected_person_id
                      ,hats.api_name
                      ,hats.transaction_step_id
                      ,'P_INFORMATION5_1')
                ,'PEI_INFORMATION6'
                ,xxas_hr_api_transaction_pkg.varchar2_value
                      (hat.selected_person_id
                      ,hats.api_name
                      ,hats.transaction_step_id
                      ,'P_INFORMATION6_1')
                ,'PEI_INFORMATION7'
                ,xxas_hr_api_transaction_pkg.varchar2_value
                      (hat.selected_person_id
                      ,hats.api_name
                      ,hats.transaction_step_id
                      ,'P_INFORMATION7_1')
                ,'PEI_INFORMATION8'
                ,xxas_hr_api_transaction_pkg.varchar2_value
                      (hat.selected_person_id
                      ,hats.api_name
                      ,hats.transaction_step_id
                      ,'P_INFORMATION8_1')
                ,'PEI_INFORMATION9'
                ,xxas_hr_api_transaction_pkg.varchar2_value
                      (hat.selected_person_id
                      ,hats.api_name
                      ,hats.transaction_step_id
                      ,'P_INFORMATION9_1')
                ,'PEI_INFORMATION10'
                ,xxas_hr_api_transaction_pkg.varchar2_value
                      (hat.selected_person_id
                      ,hats.api_name
                      ,hats.transaction_step_id
                      ,'P_INFORMATION10_1')
                ,'PEI_INFORMATION11'
                ,xxas_hr_api_transaction_pkg.varchar2_value
                      (hat.selected_person_id
                      ,hats.api_name
                      ,hats.transaction_step_id
                      ,'P_INFORMATION11_1')
                ,'PEI_INFORMATION12'
                ,xxas_hr_api_transaction_pkg.varchar2_value
                      (hat.selected_person_id
                      ,hats.api_name
                      ,hats.transaction_step_id
                      ,'P_INFORMATION12_1')
                ,'PEI_INFORMATION13'
                ,xxas_hr_api_transaction_pkg.varchar2_value
                      (hat.selected_person_id
                      ,hats.api_name
                      ,hats.transaction_step_id
                      ,'P_INFORMATION13_1')
                ,'PEI_INFORMATION14'
                ,xxas_hr_api_transaction_pkg.varchar2_value
                      (hat.selected_person_id
                      ,hats.api_name
                      ,hats.transaction_step_id
                      ,'P_INFORMATION14_1')
                ,'PEI_INFORMATION15'
                ,xxas_hr_api_transaction_pkg.varchar2_value
                      (hat.selected_person_id
                      ,hats.api_name
                      ,hats.transaction_step_id
                      ,'P_INFORMATION15_1')
                ,'PEI_INFORMATION16'
                ,xxas_hr_api_transaction_pkg.varchar2_value
                      (hat.selected_person_id
                      ,hats.api_name
                      ,hats.transaction_step_id
                      ,'P_INFORMATION16_1')
                ,'PEI_INFORMATION17'
                ,xxas_hr_api_transaction_pkg.varchar2_value
                      (hat.selected_person_id
                      ,hats.api_name
                      ,hats.transaction_step_id
                      ,'P_INFORMATION17_1')
                ,'PEI_INFORMATION18'
                ,xxas_hr_api_transaction_pkg.varchar2_value
                      (hat.selected_person_id
                      ,hats.api_name
                      ,hats.transaction_step_id
                      ,'P_INFORMATION18_1')
                ,'PEI_INFORMATION19'
                ,xxas_hr_api_transaction_pkg.varchar2_value
                      (hat.selected_person_id
                      ,hats.api_name
                      ,hats.transaction_step_id
                      ,'P_INFORMATION19_1')
                ,'PEI_INFORMATION20'
                ,xxas_hr_api_transaction_pkg.varchar2_value
                      (hat.selected_person_id
                      ,hats.api_name
                      ,hats.transaction_step_id
                      ,'P_INFORMATION20_1')
                ,'PEI_INFORMATION21'
                ,xxas_hr_api_transaction_pkg.varchar2_value
                      (hat.selected_person_id
                      ,hats.api_name
                      ,hats.transaction_step_id
                      ,'P_INFORMATION21_1')
                ,'PEI_INFORMATION22'
                ,xxas_hr_api_transaction_pkg.varchar2_value
                      (hat.selected_person_id
                      ,hats.api_name
                      ,hats.transaction_step_id
                      ,'P_INFORMATION22_1')
                ,'PEI_INFORMATION23'
                ,xxas_hr_api_transaction_pkg.varchar2_value
                      (hat.selected_person_id
                      ,hats.api_name
                      ,hats.transaction_step_id
                      ,'P_INFORMATION23_1')
                ,'PEI_INFORMATION24'
                ,xxas_hr_api_transaction_pkg.varchar2_value
                      (hat.selected_person_id
                      ,hats.api_name
                      ,hats.transaction_step_id
                      ,'P_INFORMATION24_1')
                ,'PEI_INFORMATION25'
                ,xxas_hr_api_transaction_pkg.varchar2_value
                      (hat.selected_person_id
                      ,hats.api_name
                      ,hats.transaction_step_id
                      ,'P_INFORMATION25_1')
                ,'PEI_INFORMATION26'
                ,xxas_hr_api_transaction_pkg.varchar2_value
                      (hat.selected_person_id
                      ,hats.api_name
                      ,hats.transaction_step_id
                      ,'P_INFORMATION26_1')
                ,'PEI_INFORMATION27'
                ,xxas_hr_api_transaction_pkg.varchar2_value
                      (hat.selected_person_id
                      ,hats.api_name
                      ,hats.transaction_step_id
                      ,'P_INFORMATION27_1')
                ,'PEI_INFORMATION28'
                ,xxas_hr_api_transaction_pkg.varchar2_value
                      (hat.selected_person_id
                      ,hats.api_name
                      ,hats.transaction_step_id
                      ,'P_INFORMATION28_1')
                ,'PEI_INFORMATION29'
                ,xxas_hr_api_transaction_pkg.varchar2_value
                      (hat.selected_person_id
                      ,hats.api_name
                      ,hats.transaction_step_id
                      ,'P_INFORMATION29_1')
                ,'PEI_INFORMATION30'
                ,xxas_hr_api_transaction_pkg.varchar2_value
                      (hat.selected_person_id
                      ,hats.api_name
                      ,hats.transaction_step_id
                      ,'P_INFORMATION30_1'))) pend_d        
WHERE  hat.transaction_id       = hats.transaction_id
AND    hat.status          NOT IN ('D', 'N', 'W', 'ACTIVE', 'AC')
AND    hats.api_name            = 'HR_PROCESS_EIT_SS.PROCESS_API'
AND    hats.transaction_step_id = act.transaction_step_id
AND    act.name                 = 'P_ACTION_1'
AND    act.varchar2_value       = 'NEW_ROW'
AND    hats.transaction_step_id = cat.transaction_step_id
AND    cat.name                 = 'P_INFORMATION_CATEGORY_1'
AND    hats.transaction_step_id = bad.transaction_step_id(+)
AND    bad.name(+)              = 'P_ACTION_2'
AND    bad.varchar2_value      IS NULL;

BEGIN
  xxas_fnd_dictionary_pkg.add_view
   (p_application_short_name => xxas_util_pkg.app
   ,p_view_name              => 'XXAS_PER_PEOPLE_EXTRA_INFO_V'
   ,p_description            => 'Applaud Person EIT');
END;
/

COMMIT;
EXIT;
