/* $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_ASG_EXTRA_INFO_V
AS
SELECT paei.assignment_extra_info_id
,      paei.object_version_number
,      paei.assignment_id
,      paei.information_type
,      paei.aei_attribute_category
,      paei.aei_attribute1
,      paei.aei_attribute2
,      paei.aei_attribute3
,      paei.aei_attribute4
,      paei.aei_attribute5
,      paei.aei_attribute6
,      paei.aei_attribute7
,      paei.aei_attribute8
,      paei.aei_attribute9
,      paei.aei_attribute10
,      paei.aei_attribute11
,      paei.aei_attribute12
,      paei.aei_attribute13
,      paei.aei_attribute14
,      paei.aei_attribute15
,      paei.aei_attribute16
,      paei.aei_attribute17
,      paei.aei_attribute18
,      paei.aei_attribute19
,      paei.aei_attribute20
,      paei.aei_information_category
,      paei.aei_information1
,      paei_d.segment1_value                     aei_information1_value
,      paei_d.segment1_meaning                   aei_information1_meaning          
,      paei.aei_information2
,      paei_d.segment2_value                     aei_information2_value
,      paei_d.segment2_meaning                   aei_information2_meaning 
,      paei.aei_information3
,      paei_d.segment3_value                     aei_information3_value
,      paei_d.segment3_meaning                   aei_information3_meaning 
,      paei.aei_information4
,      paei_d.segment4_value                     aei_information4_value
,      paei_d.segment4_meaning                   aei_information4_meaning 
,      paei.aei_information5
,      paei_d.segment5_value                     aei_information5_value
,      paei_d.segment5_meaning                   aei_information5_meaning 
,      paei.aei_information6
,      paei_d.segment6_value                     aei_information6_value
,      paei_d.segment6_meaning                   aei_information6_meaning 
,      paei.aei_information7
,      paei_d.segment7_value                     aei_information7_value
,      paei_d.segment7_meaning                   aei_information7_meaning 
,      paei.aei_information8
,      paei_d.segment8_value                     aei_information8_value
,      paei_d.segment8_meaning                   aei_information8_meaning 
,      paei.aei_information9
,      paei_d.segment9_value                     aei_information9_value
,      paei_d.segment9_meaning                   aei_information9_meaning 
,      paei.aei_information10
,      paei_d.segment10_value                    aei_information10_value
,      paei_d.segment10_meaning                  aei_information10_meaning 
,      paei.aei_information11
,      paei_d.segment11_value                    aei_information11_value
,      paei_d.segment11_meaning                  aei_information11_meaning 
,      paei.aei_information12
,      paei_d.segment12_value                    aei_information12_value
,      paei_d.segment12_meaning                  aei_information12_meaning 
,      paei.aei_information13
,      paei_d.segment13_value                    aei_information13_value
,      paei_d.segment13_meaning                  aei_information13_meaning 
,      paei.aei_information14
,      paei_d.segment14_value                    aei_information14_value
,      paei_d.segment14_meaning                  aei_information14_meaning 
,      paei.aei_information15
,      paei_d.segment15_value                    aei_information15_value
,      paei_d.segment15_meaning                  aei_information15_meaning 
,      paei.aei_information16
,      paei_d.segment16_value                    aei_information16_value
,      paei_d.segment16_meaning                  aei_information16_meaning 
,      paei.aei_information17
,      paei_d.segment17_value                    aei_information17_value
,      paei_d.segment17_meaning                  aei_information17_meaning 
,      paei.aei_information18
,      paei_d.segment18_value                    aei_information18_value
,      paei_d.segment18_meaning                  aei_information18_meaning 
,      paei.aei_information19
,      paei_d.segment19_value                    aei_information19_value
,      paei_d.segment19_meaning                  aei_information19_meaning 
,      paei.aei_information20
,      paei_d.segment20_value                    aei_information20_value
,      paei_d.segment20_meaning                  aei_information20_meaning 
,      paei.aei_information21
,      paei_d.segment21_value                    aei_information21_value
,      paei_d.segment21_meaning                  aei_information21_meaning 
,      paei.aei_information22
,      paei_d.segment22_value                    aei_information22_value
,      paei_d.segment22_meaning                  aei_information22_meaning 
,      paei.aei_information23
,      paei_d.segment23_value                    aei_information23_value
,      paei_d.segment23_meaning                  aei_information23_meaning 
,      paei.aei_information24
,      paei_d.segment24_value                    aei_information24_value
,      paei_d.segment24_meaning                  aei_information24_meaning 
,      paei.aei_information25
,      paei_d.segment25_value                    aei_information25_value
,      paei_d.segment25_meaning                  aei_information25_meaning 
,      paei.aei_information26
,      paei_d.segment26_value                    aei_information26_value
,      paei_d.segment26_meaning                  aei_information26_meaning 
,      paei.aei_information27
,      paei_d.segment27_value                    aei_information27_value
,      paei_d.segment27_meaning                  aei_information27_meaning 
,      paei.aei_information28
,      paei_d.segment28_value                    aei_information28_value
,      paei_d.segment28_meaning                  aei_information28_meaning 
,      paei.aei_information29
,      paei_d.segment29_value                    aei_information29_value
,      paei_d.segment29_meaning                  aei_information29_meaning 
,      paei.aei_information30
,      paei_d.segment30_value                    aei_information30_value
,      paei_d.segment30_meaning                  aei_information30_meaning 
,      paei.last_update_date
,      paei.last_updated_by
,      paei.last_update_login
,      paei.created_by
,      paei.creation_date
--,      paei.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
                   (xxas_util_pkg.get_person_from_asg(paei.assignment_id,sysdate)
                   ,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
                   (xxas_util_pkg.get_person_from_asg(paei.assignment_id,sysdate)
                   ,pend.api_name
                   ,pend.transaction_step_id
                   ,'P_INFORMATION_TYPE_1')
       END                                       aei_information_type_p
,      CASE WHEN pend.transaction_type = 'UPDATE'
            THEN xxas_hr_api_transaction_pkg.varchar2_value
                   (xxas_util_pkg.get_person_from_asg(paei.assignment_id,sysdate)
                   ,pend.api_name
                   ,pend.transaction_step_id
                   ,'P_ATTRIBUTE_CATEGORY_1')
       END                                       aei_attribute_category_p
,      CASE WHEN pend.transaction_type = 'UPDATE'
            THEN xxas_hr_api_transaction_pkg.varchar2_value
                   (xxas_util_pkg.get_person_from_asg(paei.assignment_id,sysdate)
                   ,pend.api_name
                   ,pend.transaction_step_id
                   ,'P_ATTRIBUTE1_1')
       END                                       aei_attribute1_p 
,      CASE WHEN pend.transaction_type = 'UPDATE'
            THEN xxas_hr_api_transaction_pkg.varchar2_value
                   (xxas_util_pkg.get_person_from_asg(paei.assignment_id,sysdate)
                   ,pend.api_name
                   ,pend.transaction_step_id
                   ,'P_ATTRIBUTE2_1')
       END                                       aei_attribute2_p 
,      CASE WHEN pend.transaction_type = 'UPDATE'
            THEN xxas_hr_api_transaction_pkg.varchar2_value
                   (xxas_util_pkg.get_person_from_asg(paei.assignment_id,sysdate)
                   ,pend.api_name
                   ,pend.transaction_step_id
                   ,'P_ATTRIBUTE3_1')
       END                                       aei_attribute3_p 
,      CASE WHEN pend.transaction_type = 'UPDATE'
            THEN xxas_hr_api_transaction_pkg.varchar2_value
                   (xxas_util_pkg.get_person_from_asg(paei.assignment_id,sysdate)
                   ,pend.api_name
                   ,pend.transaction_step_id
                   ,'P_ATTRIBUTE4_1')
       END                                       aei_attribute4_p 
,      CASE WHEN pend.transaction_type = 'UPDATE'
            THEN xxas_hr_api_transaction_pkg.varchar2_value
                   (xxas_util_pkg.get_person_from_asg(paei.assignment_id,sysdate)
                   ,pend.api_name
                   ,pend.transaction_step_id
                   ,'P_ATTRIBUTE5_1')
       END                                       aei_attribute5_p 
,      CASE WHEN pend.transaction_type = 'UPDATE'
            THEN xxas_hr_api_transaction_pkg.varchar2_value
                   (xxas_util_pkg.get_person_from_asg(paei.assignment_id,sysdate)
                   ,pend.api_name
                   ,pend.transaction_step_id
                   ,'P_ATTRIBUTE6_1')
       END                                       aei_attribute6_p 
,      CASE WHEN pend.transaction_type = 'UPDATE'
            THEN xxas_hr_api_transaction_pkg.varchar2_value
                   (xxas_util_pkg.get_person_from_asg(paei.assignment_id,sysdate)
                   ,pend.api_name
                   ,pend.transaction_step_id
                   ,'P_ATTRIBUTE7_1')
       END                                       aei_attribute7_p 
,      CASE WHEN pend.transaction_type = 'UPDATE'
            THEN xxas_hr_api_transaction_pkg.varchar2_value
                   (xxas_util_pkg.get_person_from_asg(paei.assignment_id,sysdate)
                   ,pend.api_name
                   ,pend.transaction_step_id
                   ,'P_ATTRIBUTE8_1')
       END                                       aei_attribute8_p 
,      CASE WHEN pend.transaction_type = 'UPDATE'
            THEN xxas_hr_api_transaction_pkg.varchar2_value
                   (xxas_util_pkg.get_person_from_asg(paei.assignment_id,sysdate)
                   ,pend.api_name
                   ,pend.transaction_step_id
                   ,'P_ATTRIBUTE9_1')
       END                                       aei_attribute9_p 
,      CASE WHEN pend.transaction_type = 'UPDATE'
            THEN xxas_hr_api_transaction_pkg.varchar2_value
                   (xxas_util_pkg.get_person_from_asg(paei.assignment_id,sysdate)
                   ,pend.api_name
                   ,pend.transaction_step_id
                   ,'P_ATTRIBUTE10_1')
       END                                       aei_attribute10_p 
,      CASE WHEN pend.transaction_type = 'UPDATE'
            THEN xxas_hr_api_transaction_pkg.varchar2_value
                   (xxas_util_pkg.get_person_from_asg(paei.assignment_id,sysdate)
                   ,pend.api_name
                   ,pend.transaction_step_id
                   ,'P_ATTRIBUTE11_1')
       END                                       aei_attribute11_p 
,      CASE WHEN pend.transaction_type = 'UPDATE'
            THEN xxas_hr_api_transaction_pkg.varchar2_value
                   (xxas_util_pkg.get_person_from_asg(paei.assignment_id,sysdate)
                   ,pend.api_name
                   ,pend.transaction_step_id
                   ,'P_ATTRIBUTE12_1')
       END                                       aei_attribute12_p 
,      CASE WHEN pend.transaction_type = 'UPDATE'
            THEN xxas_hr_api_transaction_pkg.varchar2_value
                   (xxas_util_pkg.get_person_from_asg(paei.assignment_id,sysdate)
                   ,pend.api_name
                   ,pend.transaction_step_id
                   ,'P_ATTRIBUTE13_1')
       END                                       aei_attribute13_p 
,      CASE WHEN pend.transaction_type = 'UPDATE'
            THEN xxas_hr_api_transaction_pkg.varchar2_value
                   (xxas_util_pkg.get_person_from_asg(paei.assignment_id,sysdate)
                   ,pend.api_name
                   ,pend.transaction_step_id
                   ,'P_ATTRIBUTE14_1')
       END                                       aei_attribute14_p 
,      CASE WHEN pend.transaction_type = 'UPDATE'
            THEN xxas_hr_api_transaction_pkg.varchar2_value
                   (xxas_util_pkg.get_person_from_asg(paei.assignment_id,sysdate)
                   ,pend.api_name
                   ,pend.transaction_step_id
                   ,'P_ATTRIBUTE15_1')
       END                                       aei_attribute15_p 
,      CASE WHEN pend.transaction_type = 'UPDATE'
            THEN xxas_hr_api_transaction_pkg.varchar2_value
                   (xxas_util_pkg.get_person_from_asg(paei.assignment_id,sysdate)
                   ,pend.api_name
                   ,pend.transaction_step_id
                   ,'P_ATTRIBUTE16_1')
       END                                       aei_attribute16_p 
,      CASE WHEN pend.transaction_type = 'UPDATE'
            THEN xxas_hr_api_transaction_pkg.varchar2_value
                   (xxas_util_pkg.get_person_from_asg(paei.assignment_id,sysdate)
                   ,pend.api_name
                   ,pend.transaction_step_id
                   ,'P_ATTRIBUTE17_1')
       END                                       aei_attribute17_p 
,      CASE WHEN pend.transaction_type = 'UPDATE'
            THEN xxas_hr_api_transaction_pkg.varchar2_value
                   (xxas_util_pkg.get_person_from_asg(paei.assignment_id,sysdate)
                   ,pend.api_name
                   ,pend.transaction_step_id
                   ,'P_ATTRIBUTE18_1')
       END                                       aei_attribute18_p 
,      CASE WHEN pend.transaction_type = 'UPDATE'
            THEN xxas_hr_api_transaction_pkg.varchar2_value
                   (xxas_util_pkg.get_person_from_asg(paei.assignment_id,sysdate)
                   ,pend.api_name
                   ,pend.transaction_step_id
                   ,'P_ATTRIBUTE19_1')
       END                                       aei_attribute19_p 
,      CASE WHEN pend.transaction_type = 'UPDATE'
            THEN xxas_hr_api_transaction_pkg.varchar2_value
                   (xxas_util_pkg.get_person_from_asg(paei.assignment_id,sysdate)
                   ,pend.api_name
                   ,pend.transaction_step_id
                   ,'P_ATTRIBUTE20_1')
       END                                       aei_attribute20_p 
,      CASE WHEN pend.transaction_type = 'UPDATE'
            THEN xxas_hr_api_transaction_pkg.varchar2_value
                   (xxas_util_pkg.get_person_from_asg(paei.assignment_id,sysdate)
                   ,pend.api_name
                   ,pend.transaction_step_id
                   ,'P_INFORMATION_CATEGORY_1')
       END                                       aei_information_category_p 
,      CASE WHEN pend.transaction_type = 'UPDATE'
            THEN xxas_hr_api_transaction_pkg.varchar2_value
                   (xxas_util_pkg.get_person_from_asg(paei.assignment_id,sysdate)
                   ,pend.api_name
                   ,pend.transaction_step_id
                   ,'P_INFORMATION1_1')
       END                                       aei_information1_p 
,      CASE WHEN pend.transaction_type = 'UPDATE'
            THEN pend_d.segment1_value
       END                                       aei_information1_value_p 
,      CASE WHEN pend.transaction_type = 'UPDATE'
            THEN pend_d.segment1_meaning
       END                                       aei_information1_meaning_p 
,      CASE WHEN pend.transaction_type = 'UPDATE'
            THEN xxas_hr_api_transaction_pkg.varchar2_value
                   (xxas_util_pkg.get_person_from_asg(paei.assignment_id,sysdate)
                   ,pend.api_name
                   ,pend.transaction_step_id
                   ,'P_INFORMATION2_1')
       END                                       aei_information2_p 
,      CASE WHEN pend.transaction_type = 'UPDATE'
            THEN pend_d.segment2_value
       END                                       aei_information2_value_p 
,      CASE WHEN pend.transaction_type = 'UPDATE'
            THEN pend_d.segment2_meaning
       END                                       aei_information2_meaning_p 
,      CASE WHEN pend.transaction_type = 'UPDATE'
            THEN xxas_hr_api_transaction_pkg.varchar2_value
                   (xxas_util_pkg.get_person_from_asg(paei.assignment_id,sysdate)
                   ,pend.api_name
                   ,pend.transaction_step_id
                   ,'P_INFORMATION3_1')
       END                                       aei_information3_p 
,      CASE WHEN pend.transaction_type = 'UPDATE'
            THEN pend_d.segment3_value
       END                                       aei_information3_value_p 
,      CASE WHEN pend.transaction_type = 'UPDATE'
            THEN pend_d.segment3_meaning
       END                                       aei_information3_meaning_p 
,      CASE WHEN pend.transaction_type = 'UPDATE'
            THEN xxas_hr_api_transaction_pkg.varchar2_value
                   (xxas_util_pkg.get_person_from_asg(paei.assignment_id,sysdate)
                   ,pend.api_name
                   ,pend.transaction_step_id
                   ,'P_INFORMATION4_1')
       END                                       aei_information4_p 
,      CASE WHEN pend.transaction_type = 'UPDATE'
            THEN pend_d.segment4_value
       END                                       aei_information4_value_p 
,      CASE WHEN pend.transaction_type = 'UPDATE'
            THEN pend_d.segment4_meaning
       END                                       aei_information4_meaning_p 
,      CASE WHEN pend.transaction_type = 'UPDATE'
            THEN xxas_hr_api_transaction_pkg.varchar2_value
                   (xxas_util_pkg.get_person_from_asg(paei.assignment_id,sysdate)
                   ,pend.api_name
                   ,pend.transaction_step_id
                   ,'P_INFORMATION5_1')
       END                                       aei_information5_p 
,      CASE WHEN pend.transaction_type = 'UPDATE'
            THEN pend_d.segment5_value
       END                                       aei_information5_value_p 
,      CASE WHEN pend.transaction_type = 'UPDATE'
            THEN pend_d.segment5_meaning
       END                                       aei_information5_meaning_p 
,      CASE WHEN pend.transaction_type = 'UPDATE'
            THEN xxas_hr_api_transaction_pkg.varchar2_value
                   (xxas_util_pkg.get_person_from_asg(paei.assignment_id,sysdate)
                   ,pend.api_name
                   ,pend.transaction_step_id
                   ,'P_INFORMATION6_1')
       END                                       aei_information6_p 
,      CASE WHEN pend.transaction_type = 'UPDATE'
            THEN pend_d.segment6_value
       END                                       aei_information6_value_p 
,      CASE WHEN pend.transaction_type = 'UPDATE'
            THEN pend_d.segment6_meaning
       END                                       aei_information6_meaning_p 
,      CASE WHEN pend.transaction_type = 'UPDATE'
            THEN xxas_hr_api_transaction_pkg.varchar2_value
                   (xxas_util_pkg.get_person_from_asg(paei.assignment_id,sysdate)
                   ,pend.api_name
                   ,pend.transaction_step_id
                   ,'P_INFORMATION7_1')
       END                                       aei_information7_p 
,      CASE WHEN pend.transaction_type = 'UPDATE'
            THEN pend_d.segment7_value
       END                                       aei_information7_value_p 
,      CASE WHEN pend.transaction_type = 'UPDATE'
            THEN pend_d.segment7_meaning
       END                                       aei_information7_meaning_p 
,      CASE WHEN pend.transaction_type = 'UPDATE'
            THEN xxas_hr_api_transaction_pkg.varchar2_value
                   (xxas_util_pkg.get_person_from_asg(paei.assignment_id,sysdate)
                   ,pend.api_name
                   ,pend.transaction_step_id
                   ,'P_INFORMATION8_1')
       END                                       aei_information8_p 
,      CASE WHEN pend.transaction_type = 'UPDATE'
            THEN pend_d.segment8_value
       END                                       aei_information8_value_p 
,      CASE WHEN pend.transaction_type = 'UPDATE'
            THEN pend_d.segment8_meaning
       END                                       aei_information8_meaning_p 
,      CASE WHEN pend.transaction_type = 'UPDATE'
            THEN xxas_hr_api_transaction_pkg.varchar2_value
                   (xxas_util_pkg.get_person_from_asg(paei.assignment_id,sysdate)
                   ,pend.api_name
                   ,pend.transaction_step_id
                   ,'P_INFORMATION9_1')
       END                                       aei_information9_p 
,      CASE WHEN pend.transaction_type = 'UPDATE'
            THEN pend_d.segment9_value
       END                                       aei_information9_value_p 
,      CASE WHEN pend.transaction_type = 'UPDATE'
            THEN pend_d.segment9_meaning
       END                                       aei_information9_meaning_p 
,      CASE WHEN pend.transaction_type = 'UPDATE'
            THEN xxas_hr_api_transaction_pkg.varchar2_value
                   (xxas_util_pkg.get_person_from_asg(paei.assignment_id,sysdate)
                   ,pend.api_name
                   ,pend.transaction_step_id
                   ,'P_INFORMATION10_1')
       END                                       aei_information10_p 
,      CASE WHEN pend.transaction_type = 'UPDATE'
            THEN pend_d.segment10_value
       END                                       aei_information10_value_p 
,      CASE WHEN pend.transaction_type = 'UPDATE'
            THEN pend_d.segment10_meaning
       END                                       aei_information10_meaning_p 
,      CASE WHEN pend.transaction_type = 'UPDATE'
            THEN xxas_hr_api_transaction_pkg.varchar2_value
                   (xxas_util_pkg.get_person_from_asg(paei.assignment_id,sysdate)
                   ,pend.api_name
                   ,pend.transaction_step_id
                   ,'P_INFORMATION11_1')
       END                                       aei_information11_p 
,      CASE WHEN pend.transaction_type = 'UPDATE'
            THEN pend_d.segment11_value
       END                                       aei_information11_value_p 
,      CASE WHEN pend.transaction_type = 'UPDATE'
            THEN pend_d.segment11_meaning
       END                                      aei_information11_meaning_p 
,      CASE WHEN pend.transaction_type = 'UPDATE'
            THEN xxas_hr_api_transaction_pkg.varchar2_value
                   (xxas_util_pkg.get_person_from_asg(paei.assignment_id,sysdate)
                   ,pend.api_name
                   ,pend.transaction_step_id
                   ,'P_INFORMATION12_1')
       END                                       aei_information12_p 
,      CASE WHEN pend.transaction_type = 'UPDATE'
            THEN pend_d.segment12_value
       END                                       aei_information12_value_p 
,      CASE WHEN pend.transaction_type = 'UPDATE'
            THEN pend_d.segment12_meaning
       END                                       aei_information12_meaning_p 
,      CASE WHEN pend.transaction_type = 'UPDATE'
            THEN xxas_hr_api_transaction_pkg.varchar2_value
                   (xxas_util_pkg.get_person_from_asg(paei.assignment_id,sysdate)
                   ,pend.api_name
                   ,pend.transaction_step_id
                   ,'P_INFORMATION13_1')
       END                                       aei_information13_p 
,      CASE WHEN pend.transaction_type = 'UPDATE'
            THEN pend_d.segment13_value
       END                                       aei_information13_value_p 
,      CASE WHEN pend.transaction_type = 'UPDATE'
            THEN pend_d.segment13_meaning
       END                                       aei_information13_meaning_p 
,      CASE WHEN pend.transaction_type = 'UPDATE'
            THEN xxas_hr_api_transaction_pkg.varchar2_value
                   (xxas_util_pkg.get_person_from_asg(paei.assignment_id,sysdate)
                   ,pend.api_name
                   ,pend.transaction_step_id
                   ,'P_INFORMATION14_1')
       END                                       aei_information14_p 
,      CASE WHEN pend.transaction_type = 'UPDATE'
            THEN pend_d.segment14_value
       END                                       aei_information14_value_p 
,      CASE WHEN pend.transaction_type = 'UPDATE'
            THEN pend_d.segment14_meaning
       END                                       aei_information14_meaning_p 
,      CASE WHEN pend.transaction_type = 'UPDATE'
            THEN xxas_hr_api_transaction_pkg.varchar2_value
                   (xxas_util_pkg.get_person_from_asg(paei.assignment_id,sysdate)
                   ,pend.api_name
                   ,pend.transaction_step_id
                   ,'P_INFORMATION15_1')
       END                                       aei_information15_p 
,      CASE WHEN pend.transaction_type = 'UPDATE'
            THEN pend_d.segment15_value
       END                                       aei_information15_value_p 
,      CASE WHEN pend.transaction_type = 'UPDATE'
            THEN pend_d.segment15_meaning
       END                                       aei_information15_meaning_p 
,      CASE WHEN pend.transaction_type = 'UPDATE'
            THEN xxas_hr_api_transaction_pkg.varchar2_value
                   (xxas_util_pkg.get_person_from_asg(paei.assignment_id,sysdate)
                   ,pend.api_name
                   ,pend.transaction_step_id
                   ,'P_INFORMATION16_1')
       END                                       aei_information16_p 
,      CASE WHEN pend.transaction_type = 'UPDATE'
            THEN pend_d.segment16_value
       END                                       aei_information16_value_p 
,      CASE WHEN pend.transaction_type = 'UPDATE'
            THEN pend_d.segment16_meaning
       END                                       aei_information16_meaning_p 
,      CASE WHEN pend.transaction_type = 'UPDATE'
            THEN xxas_hr_api_transaction_pkg.varchar2_value
                   (xxas_util_pkg.get_person_from_asg(paei.assignment_id,sysdate)
                   ,pend.api_name
                   ,pend.transaction_step_id
                   ,'P_INFORMATION17_1')
       END                                       aei_information17_p 
,      CASE WHEN pend.transaction_type = 'UPDATE'
            THEN pend_d.segment17_value
       END                                       aei_information17_value_p 
,      CASE WHEN pend.transaction_type = 'UPDATE'
            THEN pend_d.segment17_meaning
       END                                       aei_information17_meaning_p 
,      CASE WHEN pend.transaction_type = 'UPDATE'
            THEN xxas_hr_api_transaction_pkg.varchar2_value
                   (xxas_util_pkg.get_person_from_asg(paei.assignment_id,sysdate)
                   ,pend.api_name
                   ,pend.transaction_step_id
                   ,'P_INFORMATION18_1')
       END                                       aei_information18_p 
,      CASE WHEN pend.transaction_type = 'UPDATE'
            THEN pend_d.segment18_value
       END                                       aei_information18_value_p 
,      CASE WHEN pend.transaction_type = 'UPDATE'
            THEN pend_d.segment18_meaning
       END                                       aei_information18_meaning_p 
,      CASE WHEN pend.transaction_type = 'UPDATE'
            THEN xxas_hr_api_transaction_pkg.varchar2_value
                   (xxas_util_pkg.get_person_from_asg(paei.assignment_id,sysdate)
                   ,pend.api_name
                   ,pend.transaction_step_id
                   ,'P_INFORMATION19_1')
       END                                       aei_information19_p 
,      CASE WHEN pend.transaction_type = 'UPDATE'
            THEN pend_d.segment19_value
       END                                       aei_information19_value_p 
,      CASE WHEN pend.transaction_type = 'UPDATE'
            THEN pend_d.segment19_meaning
       END                                       aei_information19_meaning_p 
,      CASE WHEN pend.transaction_type = 'UPDATE'
            THEN xxas_hr_api_transaction_pkg.varchar2_value
                   (xxas_util_pkg.get_person_from_asg(paei.assignment_id,sysdate)
                   ,pend.api_name
                   ,pend.transaction_step_id
                   ,'P_INFORMATION20_1')
       END                                       aei_information20_p 
,      CASE WHEN pend.transaction_type = 'UPDATE'
            THEN pend_d.segment20_value
       END                                       aei_information20_value_p 
,      CASE WHEN pend.transaction_type = 'UPDATE'
            THEN pend_d.segment20_meaning
       END                                       aei_information20_meaning_p 
,      CASE WHEN pend.transaction_type = 'UPDATE'
            THEN xxas_hr_api_transaction_pkg.varchar2_value
                   (xxas_util_pkg.get_person_from_asg(paei.assignment_id,sysdate)
                   ,pend.api_name
                   ,pend.transaction_step_id
                   ,'P_INFORMATION21_1')
       END                                       aei_information21_p 
,      CASE WHEN pend.transaction_type = 'UPDATE'
            THEN pend_d.segment21_value
       END                                       aei_information21_value_p 
,      CASE WHEN pend.transaction_type = 'UPDATE'
            THEN pend_d.segment21_meaning
       END                                       aei_information21_meaning_p 
,      CASE WHEN pend.transaction_type = 'UPDATE'
            THEN xxas_hr_api_transaction_pkg.varchar2_value
                   (xxas_util_pkg.get_person_from_asg(paei.assignment_id,sysdate)
                   ,pend.api_name
                   ,pend.transaction_step_id
                   ,'P_INFORMATION22_1')
       END                                       aei_information22_p 
,      CASE WHEN pend.transaction_type = 'UPDATE'
            THEN pend_d.segment22_value
       END                                       aei_information22_value_p 
,      CASE WHEN pend.transaction_type = 'UPDATE'
            THEN pend_d.segment22_meaning
       END                                       aei_information22_meaning_p 
,      CASE WHEN pend.transaction_type = 'UPDATE'
            THEN xxas_hr_api_transaction_pkg.varchar2_value
                   (xxas_util_pkg.get_person_from_asg(paei.assignment_id,sysdate)
                   ,pend.api_name
                   ,pend.transaction_step_id
                   ,'P_INFORMATION23_1')
       END                                       aei_information23_p 
,      CASE WHEN pend.transaction_type = 'UPDATE'
            THEN pend_d.segment23_value
       END                                       aei_information23_value_p 
,      CASE WHEN pend.transaction_type = 'UPDATE'
            THEN pend_d.segment23_meaning
       END                                       aei_information23_meaning_p 
,      CASE WHEN pend.transaction_type = 'UPDATE'
            THEN xxas_hr_api_transaction_pkg.varchar2_value
                   (xxas_util_pkg.get_person_from_asg(paei.assignment_id,sysdate)
                   ,pend.api_name
                   ,pend.transaction_step_id
                   ,'P_INFORMATION24_1')
       END                                       aei_information24_p 
,      CASE WHEN pend.transaction_type = 'UPDATE'
            THEN pend_d.segment24_value
       END                                       aei_information24_value_p 
,      CASE WHEN pend.transaction_type = 'UPDATE'
            THEN pend_d.segment24_meaning
       END                                       aei_information24_meaning_p 
,      CASE WHEN pend.transaction_type = 'UPDATE'
            THEN xxas_hr_api_transaction_pkg.varchar2_value
                   (xxas_util_pkg.get_person_from_asg(paei.assignment_id,sysdate)
                   ,pend.api_name
                   ,pend.transaction_step_id
                   ,'P_INFORMATION25_1')
       END                                       aei_information25_p 
,      CASE WHEN pend.transaction_type = 'UPDATE'
            THEN pend_d.segment25_value
       END                                       aei_information25_value_p 
,      CASE WHEN pend.transaction_type = 'UPDATE'
            THEN pend_d.segment25_meaning
       END                                       aei_information25_meaning_p 
,      CASE WHEN pend.transaction_type = 'UPDATE'
            THEN xxas_hr_api_transaction_pkg.varchar2_value
                   (xxas_util_pkg.get_person_from_asg(paei.assignment_id,sysdate)
                   ,pend.api_name
                   ,pend.transaction_step_id
                   ,'P_INFORMATION26_1')
       END                                       aei_information26_p 
,      CASE WHEN pend.transaction_type = 'UPDATE'
            THEN pend_d.segment26_value
       END                                       aei_information26_value_p 
,      CASE WHEN pend.transaction_type = 'UPDATE'
            THEN pend_d.segment26_meaning
       END                                       aei_information26_meaning_p 
,      CASE WHEN pend.transaction_type = 'UPDATE'
            THEN xxas_hr_api_transaction_pkg.varchar2_value
                   (xxas_util_pkg.get_person_from_asg(paei.assignment_id,sysdate)
                   ,pend.api_name
                   ,pend.transaction_step_id
                   ,'P_INFORMATION27_1')
       END                                       aei_information27_p 
,      CASE WHEN pend.transaction_type = 'UPDATE'
            THEN pend_d.segment27_value
       END                                       aei_information27_value_p 
,      CASE WHEN pend.transaction_type = 'UPDATE'
            THEN pend_d.segment27_meaning
       END                                       aei_information27_meaning_p 
,      CASE WHEN pend.transaction_type = 'UPDATE'
            THEN xxas_hr_api_transaction_pkg.varchar2_value
                   (xxas_util_pkg.get_person_from_asg(paei.assignment_id,sysdate)
                   ,pend.api_name
                   ,pend.transaction_step_id
                   ,'P_INFORMATION28_1')
       END                                       aei_information28_p 
,      CASE WHEN pend.transaction_type = 'UPDATE'
            THEN pend_d.segment28_value
       END                                       aei_information28_value_p 
,      CASE WHEN pend.transaction_type = 'UPDATE'
            THEN pend_d.segment28_meaning
       END                                       aei_information28_meaning_p 
,      CASE WHEN pend.transaction_type = 'UPDATE'
            THEN xxas_hr_api_transaction_pkg.varchar2_value
                   (xxas_util_pkg.get_person_from_asg(paei.assignment_id,sysdate)
                   ,pend.api_name
                   ,pend.transaction_step_id
                   ,'P_INFORMATION29_1')
       END                                       aei_information29_p 
,      CASE WHEN pend.transaction_type = 'UPDATE'
            THEN pend_d.segment29_value
       END                                       aei_information29_value_p 
,      CASE WHEN pend.transaction_type = 'UPDATE'
            THEN pend_d.segment29_meaning
       END                                       aei_information29_meaning_p 
,      CASE WHEN pend.transaction_type = 'UPDATE'
            THEN xxas_hr_api_transaction_pkg.varchar2_value
                   (xxas_util_pkg.get_person_from_asg(paei.assignment_id,sysdate)
                   ,pend.api_name
                   ,pend.transaction_step_id
                   ,'P_INFORMATION30_1')
       END                                       aei_information30_p 
,      CASE WHEN pend.transaction_type = 'UPDATE'
            THEN pend_d.segment30_value
       END                                       aei_information30_value_p 
,      CASE WHEN pend.transaction_type = 'UPDATE'
            THEN pend_d.segment30_meaning
       END                                       aei_information30_meaning_p 
,      'PQH_SS_ATTACHMENT'                       attachment_entity
,      pend.transaction_id                       attachment_pk1 
FROM   per_assignment_extra_info paei
,      (SELECT paeip.assignment_id
        ,      paeip.assignment_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_assignment_extra_info     paeip
        ,      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  paeip.assignment_id           = hat.assignment_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         = paeip.assignment_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
                ,'Assignment Developer DF'
                ,paei.aei_information_category
                ,'AEI_INFORMATION1'
                ,paei.aei_information1
                ,'AEI_INFORMATION2'
                ,paei.aei_information2
                ,'AEI_INFORMATION3'
                ,paei.aei_information3
                ,'AEI_INFORMATION4'
                ,paei.aei_information4
                ,'AEI_INFORMATION5'
                ,paei.aei_information5
                ,'AEI_INFORMATION6'
                ,paei.aei_information6
                ,'AEI_INFORMATION7'
                ,paei.aei_information7
                ,'AEI_INFORMATION8'
                ,paei.aei_information8
                ,'AEI_INFORMATION9'
                ,paei.aei_information9
                ,'AEI_INFORMATION10'
                ,paei.aei_information10
                ,'AEI_INFORMATION11'
                ,paei.aei_information11
                ,'AEI_INFORMATION12'
                ,paei.aei_information12
                ,'AEI_INFORMATION13'
                ,paei.aei_information13
                ,'AEI_INFORMATION14'
                ,paei.aei_information14
                ,'AEI_INFORMATION15'
                ,paei.aei_information15
                ,'AEI_INFORMATION16'
                ,paei.aei_information16
                ,'AEI_INFORMATION17'
                ,paei.aei_information17
                ,'AEI_INFORMATION18'
                ,paei.aei_information18
                ,'AEI_INFORMATION19'
                ,paei.aei_information19
                ,'AEI_INFORMATION20'
                ,paei.aei_information20
                ,'AEI_INFORMATION21'
                ,paei.aei_information21
                ,'AEI_INFORMATION22'
                ,paei.aei_information22
                ,'AEI_INFORMATION23'
                ,paei.aei_information23
                ,'AEI_INFORMATION24'
                ,paei.aei_information24
                ,'AEI_INFORMATION25'
                ,paei.aei_information25
                ,'AEI_INFORMATION26'
                ,paei.aei_information26
                ,'AEI_INFORMATION27'
                ,paei.aei_information27
                ,'AEI_INFORMATION28'
                ,paei.aei_information28
                ,'AEI_INFORMATION29'
                ,paei.aei_information29
                ,'AEI_INFORMATION30'
                ,paei.aei_information30)) paei_d        
,      TABLE(xxas_com_flex_pkg.dff_value_set_details
                (800
                ,'Assignment Developer DF'
                ,paei.aei_information_category
                ,'AEI_INFORMATION1'
                ,xxas_hr_api_transaction_pkg.varchar2_value
                      (xxas_util_pkg.get_person_from_asg(paei.assignment_id,sysdate)
                      ,pend.api_name
                      ,pend.transaction_step_id
                      ,'P_INFORMATION1_1')
                ,'AEI_INFORMATION2'
                ,xxas_hr_api_transaction_pkg.varchar2_value
                      (xxas_util_pkg.get_person_from_asg(paei.assignment_id,sysdate)
                      ,pend.api_name
                      ,pend.transaction_step_id
                      ,'P_INFORMATION2_1')
                ,'AEI_INFORMATION3'
                ,xxas_hr_api_transaction_pkg.varchar2_value
                      (xxas_util_pkg.get_person_from_asg(paei.assignment_id,sysdate)
                      ,pend.api_name
                      ,pend.transaction_step_id
                      ,'P_INFORMATION3_1')
                ,'AEI_INFORMATION4'
                ,xxas_hr_api_transaction_pkg.varchar2_value
                      (xxas_util_pkg.get_person_from_asg(paei.assignment_id,sysdate)
                      ,pend.api_name
                      ,pend.transaction_step_id
                      ,'P_INFORMATION4_1')
                ,'AEI_INFORMATION5'
                ,xxas_hr_api_transaction_pkg.varchar2_value
                      (xxas_util_pkg.get_person_from_asg(paei.assignment_id,sysdate)
                      ,pend.api_name
                      ,pend.transaction_step_id
                      ,'P_INFORMATION5_1')
                ,'AEI_INFORMATION6'
                ,xxas_hr_api_transaction_pkg.varchar2_value
                      (xxas_util_pkg.get_person_from_asg(paei.assignment_id,sysdate)
                      ,pend.api_name
                      ,pend.transaction_step_id
                      ,'P_INFORMATION6_1')
                ,'AEI_INFORMATION7'
                ,xxas_hr_api_transaction_pkg.varchar2_value
                      (xxas_util_pkg.get_person_from_asg(paei.assignment_id,sysdate)
                      ,pend.api_name
                      ,pend.transaction_step_id
                      ,'P_INFORMATION7_1')
                ,'AEI_INFORMATION8'
                ,xxas_hr_api_transaction_pkg.varchar2_value
                      (xxas_util_pkg.get_person_from_asg(paei.assignment_id,sysdate)
                      ,pend.api_name
                      ,pend.transaction_step_id
                      ,'P_INFORMATION8_1')
                ,'AEI_INFORMATION9'
                ,xxas_hr_api_transaction_pkg.varchar2_value
                      (xxas_util_pkg.get_person_from_asg(paei.assignment_id,sysdate)
                      ,pend.api_name
                      ,pend.transaction_step_id
                      ,'P_INFORMATION9_1')
                ,'AEI_INFORMATION10'
                ,xxas_hr_api_transaction_pkg.varchar2_value
                      (xxas_util_pkg.get_person_from_asg(paei.assignment_id,sysdate)
                      ,pend.api_name
                      ,pend.transaction_step_id
                      ,'P_INFORMATION10_1')
                ,'AEI_INFORMATION11'
                ,xxas_hr_api_transaction_pkg.varchar2_value
                      (xxas_util_pkg.get_person_from_asg(paei.assignment_id,sysdate)
                      ,pend.api_name
                      ,pend.transaction_step_id
                      ,'P_INFORMATION11_1')
                ,'AEI_INFORMATION12'
                ,xxas_hr_api_transaction_pkg.varchar2_value
                      (xxas_util_pkg.get_person_from_asg(paei.assignment_id,sysdate)
                      ,pend.api_name
                      ,pend.transaction_step_id
                      ,'P_INFORMATION12_1')
                ,'AEI_INFORMATION13'
                ,xxas_hr_api_transaction_pkg.varchar2_value
                      (xxas_util_pkg.get_person_from_asg(paei.assignment_id,sysdate)
                      ,pend.api_name
                      ,pend.transaction_step_id
                      ,'P_INFORMATION13_1')
                ,'AEI_INFORMATION14'
                ,xxas_hr_api_transaction_pkg.varchar2_value
                      (xxas_util_pkg.get_person_from_asg(paei.assignment_id,sysdate)
                      ,pend.api_name
                      ,pend.transaction_step_id
                      ,'P_INFORMATION14_1')
                ,'AEI_INFORMATION15'
                ,xxas_hr_api_transaction_pkg.varchar2_value
                      (xxas_util_pkg.get_person_from_asg(paei.assignment_id,sysdate)
                      ,pend.api_name
                      ,pend.transaction_step_id
                      ,'P_INFORMATION15_1')
                ,'AEI_INFORMATION16'
                ,xxas_hr_api_transaction_pkg.varchar2_value
                      (xxas_util_pkg.get_person_from_asg(paei.assignment_id,sysdate)
                      ,pend.api_name
                      ,pend.transaction_step_id
                      ,'P_INFORMATION16_1')
                ,'AEI_INFORMATION17'
                ,xxas_hr_api_transaction_pkg.varchar2_value
                      (xxas_util_pkg.get_person_from_asg(paei.assignment_id,sysdate)
                      ,pend.api_name
                      ,pend.transaction_step_id
                      ,'P_INFORMATION17_1')
                ,'AEI_INFORMATION18'
                ,xxas_hr_api_transaction_pkg.varchar2_value
                      (xxas_util_pkg.get_person_from_asg(paei.assignment_id,sysdate)
                      ,pend.api_name
                      ,pend.transaction_step_id
                      ,'P_INFORMATION18_1')
                ,'AEI_INFORMATION19'
                ,xxas_hr_api_transaction_pkg.varchar2_value
                      (xxas_util_pkg.get_person_from_asg(paei.assignment_id,sysdate)
                      ,pend.api_name
                      ,pend.transaction_step_id
                      ,'P_INFORMATION19_1')
                ,'AEI_INFORMATION20'
                ,xxas_hr_api_transaction_pkg.varchar2_value
                      (xxas_util_pkg.get_person_from_asg(paei.assignment_id,sysdate)
                      ,pend.api_name
                      ,pend.transaction_step_id
                      ,'P_INFORMATION20_1')
                ,'AEI_INFORMATION21'
                ,xxas_hr_api_transaction_pkg.varchar2_value
                      (xxas_util_pkg.get_person_from_asg(paei.assignment_id,sysdate)
                      ,pend.api_name
                      ,pend.transaction_step_id
                      ,'P_INFORMATION21_1')
                ,'AEI_INFORMATION22'
                ,xxas_hr_api_transaction_pkg.varchar2_value
                      (xxas_util_pkg.get_person_from_asg(paei.assignment_id,sysdate)
                      ,pend.api_name
                      ,pend.transaction_step_id
                      ,'P_INFORMATION22_1')
                ,'AEI_INFORMATION23'
                ,xxas_hr_api_transaction_pkg.varchar2_value
                      (xxas_util_pkg.get_person_from_asg(paei.assignment_id,sysdate)
                      ,pend.api_name
                      ,pend.transaction_step_id
                      ,'P_INFORMATION23_1')
                ,'AEI_INFORMATION24'
                ,xxas_hr_api_transaction_pkg.varchar2_value
                      (xxas_util_pkg.get_person_from_asg(paei.assignment_id,sysdate)
                      ,pend.api_name
                      ,pend.transaction_step_id
                      ,'P_INFORMATION24_1')
                ,'AEI_INFORMATION25'
                ,xxas_hr_api_transaction_pkg.varchar2_value
                      (xxas_util_pkg.get_person_from_asg(paei.assignment_id,sysdate)
                      ,pend.api_name
                      ,pend.transaction_step_id
                      ,'P_INFORMATION25_1')
                ,'AEI_INFORMATION26'
                ,xxas_hr_api_transaction_pkg.varchar2_value
                      (xxas_util_pkg.get_person_from_asg(paei.assignment_id,sysdate)
                      ,pend.api_name
                      ,pend.transaction_step_id
                      ,'P_INFORMATION26_1')
                ,'AEI_INFORMATION27'
                ,xxas_hr_api_transaction_pkg.varchar2_value
                      (xxas_util_pkg.get_person_from_asg(paei.assignment_id,sysdate)
                      ,pend.api_name
                      ,pend.transaction_step_id
                      ,'P_INFORMATION27_1')
                ,'AEI_INFORMATION28'
                ,xxas_hr_api_transaction_pkg.varchar2_value
                      (xxas_util_pkg.get_person_from_asg(paei.assignment_id,sysdate)
                      ,pend.api_name
                      ,pend.transaction_step_id
                      ,'P_INFORMATION28_1')
                ,'AEI_INFORMATION29'
                ,xxas_hr_api_transaction_pkg.varchar2_value
                      (xxas_util_pkg.get_person_from_asg(paei.assignment_id,sysdate)
                      ,pend.api_name
                      ,pend.transaction_step_id
                      ,'P_INFORMATION29_1')
                ,'AEI_INFORMATION30'
                ,xxas_hr_api_transaction_pkg.varchar2_value
                      (xxas_util_pkg.get_person_from_asg(paei.assignment_id,sysdate)
                      ,pend.api_name
                      ,pend.transaction_step_id
                      ,'P_INFORMATION30_1'))) pend_d        
WHERE  paei.assignment_extra_info_id = pend.assignment_extra_info_id(+)
AND    paei.assignment_id            = pend.assignment_id(+)
UNION ALL
SELECT NULL                                 assignment_extra_info_id
,      NULL                                 object_version_number
,      hat.assignment_id                    assignment_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                                      aei_attribute_category
,      NULL                                      aei_attribute1
,      NULL                                      aei_attribute2
,      NULL                                      aei_attribute3
,      NULL                                      aei_attribute4
,      NULL                                      aei_attribute5
,      NULL                                      aei_attribute6
,      NULL                                      aei_attribute7
,      NULL                                      aei_attribute8
,      NULL                                      aei_attribute9
,      NULL                                      aei_attribute10
,      NULL                                      aei_attribute11
,      NULL                                      aei_attribute12
,      NULL                                      aei_attribute13
,      NULL                                      aei_attribute14
,      NULL                                      aei_attribute15
,      NULL                                      aei_attribute16
,      NULL                                      aei_attribute17
,      NULL                                      aei_attribute18
,      NULL                                      aei_attribute19
,      NULL                                      aei_attribute20
,      NULL                                      aei_information_category
,      NULL                                      aei_information1
,      NULL                                      aei_information1_value
,      NULL                                      aei_information1_meaning
,      NULL                                      aei_information2
,      NULL                                      aei_information2_value
,      NULL                                      aei_information2_meaning
,      NULL                                      aei_information3
,      NULL                                      aei_information3_value
,      NULL                                      aei_information3_meaning
,      NULL                                      aei_information4
,      NULL                                      aei_information4_value
,      NULL                                      aei_information4_meaning
,      NULL                                      aei_information5
,      NULL                                      aei_information5_value
,      NULL                                      aei_information5_meaning
,      NULL                                      aei_information6
,      NULL                                      aei_information6_value
,      NULL                                      aei_information6_meaning
,      NULL                                      aei_information7
,      NULL                                      aei_information7_value
,      NULL                                      aei_information7_meaning
,      NULL                                      aei_information8
,      NULL                                      aei_information8_value
,      NULL                                      aei_information8_meaning
,      NULL                                      aei_information9
,      NULL                                      aei_information9_value
,      NULL                                      aei_information9_meaning
,      NULL                                      aei_information10
,      NULL                                      aei_information10_value
,      NULL                                      aei_information10_meaning
,      NULL                                      aei_information11
,      NULL                                      aei_information11_value
,      NULL                                      aei_information11_meaning
,      NULL                                      aei_information12
,      NULL                                      aei_information12_value
,      NULL                                      aei_information12_meaning
,      NULL                                      aei_information13
,      NULL                                      aei_information13_value
,      NULL                                      aei_information13_meaning
,      NULL                                      aei_information14
,      NULL                                      aei_information14_value
,      NULL                                      aei_information14_meaning
,      NULL                                      aei_information15
,      NULL                                      aei_information15_value
,      NULL                                      aei_information15_meaning
,      NULL                                      aei_information16
,      NULL                                      aei_information16_value
,      NULL                                      aei_information16_meaning
,      NULL                                      aei_information17
,      NULL                                      aei_information17_value
,      NULL                                      aei_information17_meaning
,      NULL                                      aei_information18
,      NULL                                      aei_information18_value
,      NULL                                      aei_information18_meaning
,      NULL                                      aei_information19
,      NULL                                      aei_information19_value
,      NULL                                      aei_information19_meaning
,      NULL                                      aei_information20
,      NULL                                      aei_information20_value
,      NULL                                      aei_information20_meaning
,      NULL                                      aei_information21
,      NULL                                      aei_information21_value
,      NULL                                      aei_information21_meaning
,      NULL                                      aei_information22
,      NULL                                      aei_information22_value
,      NULL                                      aei_information22_meaning
,      NULL                                      aei_information23
,      NULL                                      aei_information23_value
,      NULL                                      aei_information23_meaning
,      NULL                                      aei_information24
,      NULL                                      aei_information24_value
,      NULL                                      aei_information24_meaning
,      NULL                                      aei_information25
,      NULL                                      aei_information25_value
,      NULL                                      aei_information25_meaning
,      NULL                                      aei_information26
,      NULL                                      aei_information26_value
,      NULL                                      aei_information26_meaning
,      NULL                                      aei_information27
,      NULL                                      aei_information27_value
,      NULL                                      aei_information27_meaning
,      NULL                                      aei_information28
,      NULL                                      aei_information28_value
,      NULL                                      aei_information28_meaning
,      NULL                                      aei_information29
,      NULL                                      aei_information29_value
,      NULL                                      aei_information29_meaning
,      NULL                                      aei_information30
,      NULL                                      aei_information30_value
,      NULL                                      aei_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')    aei_attribute_category_p
,      xxas_hr_api_transaction_pkg.varchar2_value
                   (hat.selected_person_id
                   ,hats.api_name
                   ,hats.transaction_step_id
                   ,'P_ATTRIBUTE1_1')            aei_attribute1_p 
,      xxas_hr_api_transaction_pkg.varchar2_value
                   (hat.selected_person_id
                   ,hats.api_name
                   ,hats.transaction_step_id
                   ,'P_ATTRIBUTE2_1')            aei_attribute2_p 
,      xxas_hr_api_transaction_pkg.varchar2_value
                   (hat.selected_person_id
                   ,hats.api_name
                   ,hats.transaction_step_id
                   ,'P_ATTRIBUTE3_1')            aei_attribute3_p 
,      xxas_hr_api_transaction_pkg.varchar2_value
                   (hat.selected_person_id
                   ,hats.api_name
                   ,hats.transaction_step_id
                   ,'P_ATTRIBUTE4_1')            aei_attribute4_p 
,      xxas_hr_api_transaction_pkg.varchar2_value
                   (hat.selected_person_id
                   ,hats.api_name
                   ,hats.transaction_step_id
                   ,'P_ATTRIBUTE5_1')            aei_attribute5_p 
,      xxas_hr_api_transaction_pkg.varchar2_value
                   (hat.selected_person_id
                   ,hats.api_name
                   ,hats.transaction_step_id
                   ,'P_ATTRIBUTE6_1')            aei_attribute6_p 
,      xxas_hr_api_transaction_pkg.varchar2_value
                   (hat.selected_person_id
                   ,hats.api_name
                   ,hats.transaction_step_id
                   ,'P_ATTRIBUTE7_1')            aei_attribute7_p 
,      xxas_hr_api_transaction_pkg.varchar2_value
                   (hat.selected_person_id
                   ,hats.api_name
                   ,hats.transaction_step_id
                   ,'P_ATTRIBUTE8_1')            aei_attribute8_p 
,      xxas_hr_api_transaction_pkg.varchar2_value
                   (hat.selected_person_id
                   ,hats.api_name
                   ,hats.transaction_step_id
                   ,'P_ATTRIBUTE9_1')            aei_attribute9_p 
,      xxas_hr_api_transaction_pkg.varchar2_value
                   (hat.selected_person_id
                   ,hats.api_name
                   ,hats.transaction_step_id
                   ,'P_ATTRIBUTE10_1')           aei_attribute10_p 
,      xxas_hr_api_transaction_pkg.varchar2_value
                   (hat.selected_person_id
                   ,hats.api_name
                   ,hats.transaction_step_id
                   ,'P_ATTRIBUTE11_1')           aei_attribute11_p 
,      xxas_hr_api_transaction_pkg.varchar2_value
                   (hat.selected_person_id
                   ,hats.api_name
                   ,hats.transaction_step_id
                   ,'P_ATTRIBUTE12_1')           aei_attribute12_p 
,      xxas_hr_api_transaction_pkg.varchar2_value
                   (hat.selected_person_id
                   ,hats.api_name
                   ,hats.transaction_step_id
                   ,'P_ATTRIBUTE13_1')           aei_attribute13_p 
,      xxas_hr_api_transaction_pkg.varchar2_value
                   (hat.selected_person_id
                   ,hats.api_name
                   ,hats.transaction_step_id
                   ,'P_ATTRIBUTE14_1')           aei_attribute14_p 
,      xxas_hr_api_transaction_pkg.varchar2_value
                   (hat.selected_person_id
                   ,hats.api_name
                   ,hats.transaction_step_id
                   ,'P_ATTRIBUTE15_1')           aei_attribute15_p 
,      xxas_hr_api_transaction_pkg.varchar2_value
                   (hat.selected_person_id
                   ,hats.api_name
                   ,hats.transaction_step_id
                   ,'P_ATTRIBUTE16_1')           aei_attribute16_p 
,      xxas_hr_api_transaction_pkg.varchar2_value
                   (hat.selected_person_id
                   ,hats.api_name
                   ,hats.transaction_step_id
                   ,'P_ATTRIBUTE17_1')           aei_attribute17_p 
,      xxas_hr_api_transaction_pkg.varchar2_value
                   (hat.selected_person_id
                   ,hats.api_name
                   ,hats.transaction_step_id
                   ,'P_ATTRIBUTE18_1')           aei_attribute18_p 
,      xxas_hr_api_transaction_pkg.varchar2_value
                   (hat.selected_person_id
                   ,hats.api_name
                   ,hats.transaction_step_id
                   ,'P_ATTRIBUTE19_1')           aei_attribute19_p 
,      xxas_hr_api_transaction_pkg.varchar2_value
                   (hat.selected_person_id
                   ,hats.api_name
                   ,hats.transaction_step_id
                   ,'P_ATTRIBUTE20_1')           aei_attribute20_p 
,      xxas_hr_api_transaction_pkg.varchar2_value
                   (hat.selected_person_id
                   ,hats.api_name
                   ,hats.transaction_step_id
                   ,'P_INFORMATION_CATEGORY_1')  aei_information_category_p 
,      xxas_hr_api_transaction_pkg.varchar2_value
                   (hat.selected_person_id
                   ,hats.api_name
                   ,hats.transaction_step_id
                   ,'P_INFORMATION1_1')          aei_information1_p 
,      pend_d.segment1_value                     aei_information1_value_p 
,      pend_d.segment1_meaning                   aei_information1_meaning_p 
,      xxas_hr_api_transaction_pkg.varchar2_value
                   (hat.selected_person_id
                   ,hats.api_name
                   ,hats.transaction_step_id
                   ,'P_INFORMATION2_1')          aei_information2_p 
,      pend_d.segment2_value                     aei_information2_value_p 
,      pend_d.segment2_meaning                   aei_information2_meaning_p 
,      xxas_hr_api_transaction_pkg.varchar2_value
                   (hat.selected_person_id
                   ,hats.api_name
                   ,hats.transaction_step_id
                   ,'P_INFORMATION3_1')          aei_information3_p 
,      pend_d.segment3_value                     aei_information3_value_p 
,      pend_d.segment3_meaning                   aei_information3_meaning_p 
,      xxas_hr_api_transaction_pkg.varchar2_value
                   (hat.selected_person_id
                   ,hats.api_name
                   ,hats.transaction_step_id
                   ,'P_INFORMATION4_1')          aei_information4_p 
,      pend_d.segment4_value                     aei_information4_value_p 
,      pend_d.segment4_meaning                   aei_information4_meaning_p 
,      xxas_hr_api_transaction_pkg.varchar2_value
                   (hat.selected_person_id
                   ,hats.api_name
                   ,hats.transaction_step_id
                   ,'P_INFORMATION5_1')          aei_information5_p 
,      pend_d.segment5_value                     aei_information5_value_p 
,      pend_d.segment5_meaning                   aei_information5_meaning_p 
,      xxas_hr_api_transaction_pkg.varchar2_value
                   (hat.selected_person_id
                   ,hats.api_name
                   ,hats.transaction_step_id
                   ,'P_INFORMATION6_1')          aei_information6_p 
,      pend_d.segment6_value                     aei_information6_value_p 
,      pend_d.segment6_meaning                   aei_information6_meaning_p 
,      xxas_hr_api_transaction_pkg.varchar2_value
                   (hat.selected_person_id
                   ,hats.api_name
                   ,hats.transaction_step_id
                   ,'P_INFORMATION7_1')          aei_information7_p 
,      pend_d.segment7_value                     aei_information7_value_p 
,      pend_d.segment7_meaning                   aei_information7_meaning_p 
,      xxas_hr_api_transaction_pkg.varchar2_value
                   (hat.selected_person_id
                   ,hats.api_name
                   ,hats.transaction_step_id
                   ,'P_INFORMATION8_1')          aei_information8_p 
,      pend_d.segment8_value                     aei_information8_value_p 
,      pend_d.segment8_meaning                   aei_information8_meaning_p 
,      xxas_hr_api_transaction_pkg.varchar2_value
                   (hat.selected_person_id
                   ,hats.api_name
                   ,hats.transaction_step_id
                   ,'P_INFORMATION9_1')          aei_information9_p 
,      pend_d.segment9_value                     aei_information9_value_p 
,      pend_d.segment9_meaning                   aei_information9_meaning_p 
,      xxas_hr_api_transaction_pkg.varchar2_value
                   (hat.selected_person_id
                   ,hats.api_name
                   ,hats.transaction_step_id
                   ,'P_INFORMATION10_1')         aei_information10_p 
,      pend_d.segment10_value                    aei_information10_value_p 
,      pend_d.segment10_meaning                  aei_information10_meaning_p 
,      xxas_hr_api_transaction_pkg.varchar2_value
                   (hat.selected_person_id
                   ,hats.api_name
                   ,hats.transaction_step_id
                   ,'P_INFORMATION11_1')         aei_information11_p 
,      pend_d.segment11_value                    aei_information11_value_p 
,      pend_d.segment11_meaning                  aei_information11_meaning_p 
,      xxas_hr_api_transaction_pkg.varchar2_value
                   (hat.selected_person_id
                   ,hats.api_name
                   ,hats.transaction_step_id
                   ,'P_INFORMATION12_1')         aei_information12_p 
,      pend_d.segment12_value                    aei_information12_value_p 
,      pend_d.segment12_meaning                  aei_information12_meaning_p 
,      xxas_hr_api_transaction_pkg.varchar2_value
                   (hat.selected_person_id
                   ,hats.api_name
                   ,hats.transaction_step_id
                   ,'P_INFORMATION13_1')         aei_information13_p 
,      pend_d.segment13_value                    aei_information13_value_p 
,      pend_d.segment13_meaning                  aei_information13_meaning_p 
,      xxas_hr_api_transaction_pkg.varchar2_value
                   (hat.selected_person_id
                   ,hats.api_name
                   ,hats.transaction_step_id
                   ,'P_INFORMATION14_1')         aei_information14_p 
,      pend_d.segment14_value                    aei_information14_value_p 
,      pend_d.segment14_meaning                  aei_information14_meaning_p 
,      xxas_hr_api_transaction_pkg.varchar2_value
                   (hat.selected_person_id
                   ,hats.api_name
                   ,hats.transaction_step_id
                   ,'P_INFORMATION15_1')         aei_information15_p 
,      pend_d.segment15_value                    aei_information15_value_p 
,      pend_d.segment15_meaning                  aei_information15_meaning_p 
,      xxas_hr_api_transaction_pkg.varchar2_value
                   (hat.selected_person_id
                   ,hats.api_name
                   ,hats.transaction_step_id
                   ,'P_INFORMATION16_1')         aei_information16_p 
,      pend_d.segment16_value                    aei_information16_value_p 
,      pend_d.segment16_meaning                  aei_information16_meaning_p 
,      xxas_hr_api_transaction_pkg.varchar2_value
                   (hat.selected_person_id
                   ,hats.api_name
                   ,hats.transaction_step_id
                   ,'P_INFORMATION17_1')         aei_information17_p 
,      pend_d.segment17_value                    aei_information17_value_p 
,      pend_d.segment17_meaning                  aei_information17_meaning_p 
,      xxas_hr_api_transaction_pkg.varchar2_value
                   (hat.selected_person_id
                   ,hats.api_name
                   ,hats.transaction_step_id
                   ,'P_INFORMATION18_1')         aei_information18_p 
,      pend_d.segment18_value                    aei_information18_value_p 
,      pend_d.segment18_meaning                  aei_information18_meaning_p 
,      xxas_hr_api_transaction_pkg.varchar2_value
                   (hat.selected_person_id
                   ,hats.api_name
                   ,hats.transaction_step_id
                   ,'P_INFORMATION19_1')         aei_information19_p 
,      pend_d.segment19_value                    aei_information19_value_p 
,      pend_d.segment19_meaning                  aei_information19_meaning_p 
,      xxas_hr_api_transaction_pkg.varchar2_value
                   (hat.selected_person_id
                   ,hats.api_name
                   ,hats.transaction_step_id
                   ,'P_INFORMATION20_1')         aei_information20_p 
,      pend_d.segment20_value                    aei_information20_value_p 
,      pend_d.segment20_meaning                  aei_information20_meaning_p 
,      xxas_hr_api_transaction_pkg.varchar2_value
                   (hat.selected_person_id
                   ,hats.api_name
                   ,hats.transaction_step_id
                   ,'P_INFORMATION21_1')         aei_information21_p 
,      pend_d.segment21_value                    aei_information21_value_p 
,      pend_d.segment21_meaning                  aei_information21_meaning_p 
,      xxas_hr_api_transaction_pkg.varchar2_value
                   (hat.selected_person_id
                   ,hats.api_name
                   ,hats.transaction_step_id
                   ,'P_INFORMATION22_1')         aei_information22_p 
,      pend_d.segment22_value                    aei_information22_value_p 
,      pend_d.segment22_meaning                  aei_information22_meaning_p 
,      xxas_hr_api_transaction_pkg.varchar2_value
                   (hat.selected_person_id
                   ,hats.api_name
                   ,hats.transaction_step_id
                   ,'P_INFORMATION23_1')         aei_information23_p 
,      pend_d.segment23_value                    aei_information23_value_p 
,      pend_d.segment23_meaning                  aei_information23_meaning_p 
,      xxas_hr_api_transaction_pkg.varchar2_value
                   (hat.selected_person_id
                   ,hats.api_name
                   ,hats.transaction_step_id
                   ,'P_INFORMATION24_1')         aei_information24_p 
,      pend_d.segment24_value                    aei_information24_value_p 
,      pend_d.segment24_meaning                  aei_information24_meaning_p 
,      xxas_hr_api_transaction_pkg.varchar2_value
                   (hat.selected_person_id
                   ,hats.api_name
                   ,hats.transaction_step_id
                   ,'P_INFORMATION25_1')         aei_information25_p 
,      pend_d.segment25_value                    aei_information25_value_p 
,      pend_d.segment25_meaning                  aei_information25_meaning_p 
,      xxas_hr_api_transaction_pkg.varchar2_value
                   (hat.selected_person_id
                   ,hats.api_name
                   ,hats.transaction_step_id
                   ,'P_INFORMATION26_1')         aei_information26_p 
,      pend_d.segment26_value                    aei_information26_value_p 
,      pend_d.segment26_meaning                  aei_information26_meaning_p 
,      xxas_hr_api_transaction_pkg.varchar2_value
                   (hat.selected_person_id
                   ,hats.api_name
                   ,hats.transaction_step_id
                   ,'P_INFORMATION27_1')         aei_information27_p 
,      pend_d.segment27_value                    aei_information27_value_p 
,      pend_d.segment27_meaning                  aei_information27_meaning_p 
,      xxas_hr_api_transaction_pkg.varchar2_value
                   (hat.selected_person_id
                   ,hats.api_name
                   ,hats.transaction_step_id
                   ,'P_INFORMATION28_1')         aei_information28_p 
,      pend_d.segment28_value                    aei_information28_value_p 
,      pend_d.segment28_meaning                  aei_information28_meaning_p 
,      xxas_hr_api_transaction_pkg.varchar2_value
                   (hat.selected_person_id
                   ,hats.api_name
                   ,hats.transaction_step_id
                   ,'P_INFORMATION29_1')         aei_information29_p 
,      pend_d.segment29_value                    aei_information29_value_p 
,      pend_d.segment29_meaning                  aei_information29_meaning_p 
,      xxas_hr_api_transaction_pkg.varchar2_value
                   (hat.selected_person_id
                   ,hats.api_name
                   ,hats.transaction_step_id
                   ,'P_INFORMATION30_1')         aei_information30_p 
,      pend_d.segment30_value                    aei_information30_value_p 
,      pend_d.segment30_meaning                  aei_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
                ,'Assignment Developer DF'
                ,xxas_hr_api_transaction_pkg.varchar2_value
                   (hat.selected_person_id
                   ,hats.api_name
                   ,hats.transaction_step_id
                   ,'P_INFORMATION_CATEGORY_1')
                ,'AEI_INFORMATION1'
                ,xxas_hr_api_transaction_pkg.varchar2_value
                      (hat.selected_person_id
                      ,hats.api_name
                      ,hats.transaction_step_id
                      ,'P_INFORMATION1_1')
                ,'AEI_INFORMATION2'
                ,xxas_hr_api_transaction_pkg.varchar2_value
                      (hat.selected_person_id
                      ,hats.api_name
                      ,hats.transaction_step_id
                      ,'P_INFORMATION2_1')
                ,'AEI_INFORMATION3'
                ,xxas_hr_api_transaction_pkg.varchar2_value
                      (hat.selected_person_id
                      ,hats.api_name
                      ,hats.transaction_step_id
                      ,'P_INFORMATION3_1')
                ,'AEI_INFORMATION4'
                ,xxas_hr_api_transaction_pkg.varchar2_value
                      (hat.selected_person_id
                      ,hats.api_name
                      ,hats.transaction_step_id
                      ,'P_INFORMATION4_1')
                ,'AEI_INFORMATION5'
                ,xxas_hr_api_transaction_pkg.varchar2_value
                      (hat.selected_person_id
                      ,hats.api_name
                      ,hats.transaction_step_id
                      ,'P_INFORMATION5_1')
                ,'AEI_INFORMATION6'
                ,xxas_hr_api_transaction_pkg.varchar2_value
                      (hat.selected_person_id
                      ,hats.api_name
                      ,hats.transaction_step_id
                      ,'P_INFORMATION6_1')
                ,'AEI_INFORMATION7'
                ,xxas_hr_api_transaction_pkg.varchar2_value
                      (hat.selected_person_id
                      ,hats.api_name
                      ,hats.transaction_step_id
                      ,'P_INFORMATION7_1')
                ,'AEI_INFORMATION8'
                ,xxas_hr_api_transaction_pkg.varchar2_value
                      (hat.selected_person_id
                      ,hats.api_name
                      ,hats.transaction_step_id
                      ,'P_INFORMATION8_1')
                ,'AEI_INFORMATION9'
                ,xxas_hr_api_transaction_pkg.varchar2_value
                      (hat.selected_person_id
                      ,hats.api_name
                      ,hats.transaction_step_id
                      ,'P_INFORMATION9_1')
                ,'AEI_INFORMATION10'
                ,xxas_hr_api_transaction_pkg.varchar2_value
                      (hat.selected_person_id
                      ,hats.api_name
                      ,hats.transaction_step_id
                      ,'P_INFORMATION10_1')
                ,'AEI_INFORMATION11'
                ,xxas_hr_api_transaction_pkg.varchar2_value
                      (hat.selected_person_id
                      ,hats.api_name
                      ,hats.transaction_step_id
                      ,'P_INFORMATION11_1')
                ,'AEI_INFORMATION12'
                ,xxas_hr_api_transaction_pkg.varchar2_value
                      (hat.selected_person_id
                      ,hats.api_name
                      ,hats.transaction_step_id
                      ,'P_INFORMATION12_1')
                ,'AEI_INFORMATION13'
                ,xxas_hr_api_transaction_pkg.varchar2_value
                      (hat.selected_person_id
                      ,hats.api_name
                      ,hats.transaction_step_id
                      ,'P_INFORMATION13_1')
                ,'AEI_INFORMATION14'
                ,xxas_hr_api_transaction_pkg.varchar2_value
                      (hat.selected_person_id
                      ,hats.api_name
                      ,hats.transaction_step_id
                      ,'P_INFORMATION14_1')
                ,'AEI_INFORMATION15'
                ,xxas_hr_api_transaction_pkg.varchar2_value
                      (hat.selected_person_id
                      ,hats.api_name
                      ,hats.transaction_step_id
                      ,'P_INFORMATION15_1')
                ,'AEI_INFORMATION16'
                ,xxas_hr_api_transaction_pkg.varchar2_value
                      (hat.selected_person_id
                      ,hats.api_name
                      ,hats.transaction_step_id
                      ,'P_INFORMATION16_1')
                ,'AEI_INFORMATION17'
                ,xxas_hr_api_transaction_pkg.varchar2_value
                      (hat.selected_person_id
                      ,hats.api_name
                      ,hats.transaction_step_id
                      ,'P_INFORMATION17_1')
                ,'AEI_INFORMATION18'
                ,xxas_hr_api_transaction_pkg.varchar2_value
                      (hat.selected_person_id
                      ,hats.api_name
                      ,hats.transaction_step_id
                      ,'P_INFORMATION18_1')
                ,'AEI_INFORMATION19'
                ,xxas_hr_api_transaction_pkg.varchar2_value
                      (hat.selected_person_id
                      ,hats.api_name
                      ,hats.transaction_step_id
                      ,'P_INFORMATION19_1')
                ,'AEI_INFORMATION20'
                ,xxas_hr_api_transaction_pkg.varchar2_value
                      (hat.selected_person_id
                      ,hats.api_name
                      ,hats.transaction_step_id
                      ,'P_INFORMATION20_1')
                ,'AEI_INFORMATION21'
                ,xxas_hr_api_transaction_pkg.varchar2_value
                      (hat.selected_person_id
                      ,hats.api_name
                      ,hats.transaction_step_id
                      ,'P_INFORMATION21_1')
                ,'AEI_INFORMATION22'
                ,xxas_hr_api_transaction_pkg.varchar2_value
                      (hat.selected_person_id
                      ,hats.api_name
                      ,hats.transaction_step_id
                      ,'P_INFORMATION22_1')
                ,'AEI_INFORMATION23'
                ,xxas_hr_api_transaction_pkg.varchar2_value
                      (hat.selected_person_id
                      ,hats.api_name
                      ,hats.transaction_step_id
                      ,'P_INFORMATION23_1')
                ,'AEI_INFORMATION24'
                ,xxas_hr_api_transaction_pkg.varchar2_value
                      (hat.selected_person_id
                      ,hats.api_name
                      ,hats.transaction_step_id
                      ,'P_INFORMATION24_1')
                ,'AEI_INFORMATION25'
                ,xxas_hr_api_transaction_pkg.varchar2_value
                      (hat.selected_person_id
                      ,hats.api_name
                      ,hats.transaction_step_id
                      ,'P_INFORMATION25_1')
                ,'AEI_INFORMATION26'
                ,xxas_hr_api_transaction_pkg.varchar2_value
                      (hat.selected_person_id
                      ,hats.api_name
                      ,hats.transaction_step_id
                      ,'P_INFORMATION26_1')
                ,'AEI_INFORMATION27'
                ,xxas_hr_api_transaction_pkg.varchar2_value
                      (hat.selected_person_id
                      ,hats.api_name
                      ,hats.transaction_step_id
                      ,'P_INFORMATION27_1')
                ,'AEI_INFORMATION28'
                ,xxas_hr_api_transaction_pkg.varchar2_value
                      (hat.selected_person_id
                      ,hats.api_name
                      ,hats.transaction_step_id
                      ,'P_INFORMATION28_1')
                ,'AEI_INFORMATION29'
                ,xxas_hr_api_transaction_pkg.varchar2_value
                      (hat.selected_person_id
                      ,hats.api_name
                      ,hats.transaction_step_id
                      ,'P_INFORMATION29_1')
                ,'AEI_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_ASG_EXTRA_INFO_V'
   ,p_description            => 'Applaud Assignment EIT');
END;
/

COMMIT;
EXIT;
