/* $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_addresses_v 
AS
SELECT pa.person_id
,      pa.person_id per_person_id /* Alternative name to avoid conflict with PERSON_ID FF context */
,      pa.address_id
,      pa.object_version_number
,      pa.date_from
,      pa.date_to
,      pa.primary_flag
,      pa.style
,      pa.address_type
,      xxas_util_pkg.decode_lookup
         ('ADDRESS_TYPE'
         ,pa.address_type)                       address_type_meaning                                     
,      pa.address_line1
,      xxas_com_vset_pkg.decode_descr_vset_value
         ('800'
         ,'Address Structure'
         ,pa.style
         ,'ADDRESS_LINE1'
         ,pa.address_line1)                      address_line1_meaning 
,      pa.address_line2
,      xxas_com_vset_pkg.decode_descr_vset_value
         ('800'
         ,'Address Structure'
         ,pa.style
         ,'ADDRESS_LINE2'
         ,pa.address_line2)                      address_line2_meaning 
,      pa.address_line3
,      xxas_com_vset_pkg.decode_descr_vset_value
         ('800'
         ,'Address Structure'
         ,pa.style
         ,'ADDRESS_LINE3'
         ,pa.address_line3)                      address_line3_meaning 
,      pa.town_or_city
,      xxas_com_vset_pkg.decode_descr_vset_value
         ('800'
         ,'Address Structure'
         ,pa.style
         ,'TOWN_OR_CITY'
         ,pa.town_or_city)                       town_or_city_meaning 
,      pa.region_1
,      xxas_com_vset_pkg.decode_descr_vset_value
         ('800'
         ,'Address Structure'
         ,pa.style
         ,'REGION_1'
         ,pa.region_1)                           region_1_meaning 
,      pa.region_2
,      xxas_com_vset_pkg.decode_descr_vset_value
         ('800'
         ,'Address Structure'
         ,pa.style
         ,'REGION_2'
         ,pa.region_2)                           region_2_meaning 
,      pa.region_3
,      xxas_com_vset_pkg.decode_descr_vset_value
         ('800'
         ,'Address Structure'
         ,pa.style
         ,'REGION_3'
         ,pa.region_3)                           region_3_meaning 
,      pa.postal_code
,      xxas_com_vset_pkg.decode_descr_vset_value
         ('800'
         ,'Address Structure'
         ,pa.style
         ,'POSTAL_CODE'
         ,pa.postal_code)                        postal_code_meaning 
,      pa.country
,      xxas_com_vset_pkg.decode_descr_vset_value
         ('800'
         ,'Address Structure'
         ,pa.style
         ,'COUNTRY'
         ,pa.country)                            country_meaning 
,      pa.telephone_number_1
,      xxas_com_vset_pkg.decode_descr_vset_value
         ('800'
         ,'Address Structure'
         ,pa.style
         ,'TELEPHONE_NUMBER_1'
         ,pa.telephone_number_1)                 telephone_number_1_meaning 
,      pa.telephone_number_2
,      xxas_com_vset_pkg.decode_descr_vset_value
         ('800'
         ,'Address Structure'
         ,pa.style
         ,'TELEPHONE_NUMBER_1'
         ,pa.telephone_number_2)                 telephone_number_2_meaning 
,      pa.telephone_number_3
,      xxas_com_vset_pkg.decode_descr_vset_value
         ('800'
         ,'Address Structure'
         ,pa.style
         ,'TELEPHONE_NUMBER_3'
         ,pa.telephone_number_3)                 telephone_number_3_meaning
,     (SELECT lat.y
       FROM   TABLE(sdo_util.getvertices(pa.geometry)) lat) latitude
,     (SELECT lon.x
       FROM   TABLE(sdo_util.getvertices(pa.geometry)) lon) longtitude
,      pa.addr_attribute_category
,      pa.addr_attribute1
,      xxas_com_vset_pkg.decode_descr_vset_value
         ('800'
         ,'PER_ADDRESSES'
         ,pa.addr_attribute_category
         ,'ADDR_ATTRIBUTE1'
         ,pa.addr_attribute1)                    addr_attribute1_meaning 
,      pa.addr_attribute2
,      xxas_com_vset_pkg.decode_descr_vset_value
         ('800'
         ,'PER_ADDRESSES'
         ,pa.addr_attribute_category
         ,'ADDR_ATTRIBUTE2'
         ,pa.addr_attribute2)                    addr_attribute2_meaning 
,      pa.addr_attribute3
,      xxas_com_vset_pkg.decode_descr_vset_value
         ('800'
         ,'PER_ADDRESSES'
         ,pa.addr_attribute_category
         ,'ADDR_ATTRIBUTE3'
         ,pa.addr_attribute3)                    addr_attribute3_meaning 
,      pa.addr_attribute4
,      xxas_com_vset_pkg.decode_descr_vset_value
         ('800'
         ,'PER_ADDRESSES'
         ,pa.addr_attribute_category
         ,'ADDR_ATTRIBUTE4'
         ,pa.addr_attribute4)                    addr_attribute4_meaning 
,      pa.addr_attribute5
,      xxas_com_vset_pkg.decode_descr_vset_value
         ('800'
         ,'PER_ADDRESSES'
         ,pa.addr_attribute_category
         ,'ADDR_ATTRIBUTE5'
         ,pa.addr_attribute5)                    addr_attribute5_meaning 
,      pa.addr_attribute6
,      xxas_com_vset_pkg.decode_descr_vset_value
         ('800'
         ,'PER_ADDRESSES'
         ,pa.addr_attribute_category
         ,'ADDR_ATTRIBUTE6'
         ,pa.addr_attribute6)                    addr_attribute6_meaning 
,      pa.addr_attribute7
,      xxas_com_vset_pkg.decode_descr_vset_value
         ('800'
         ,'PER_ADDRESSES'
         ,pa.addr_attribute_category
         ,'ADDR_ATTRIBUTE7'
         ,pa.addr_attribute7)                    addr_attribute7_meaning 
,      pa.addr_attribute8
,      xxas_com_vset_pkg.decode_descr_vset_value
         ('800'
         ,'PER_ADDRESSES'
         ,pa.addr_attribute_category
         ,'ADDR_ATTRIBUTE8'
         ,pa.addr_attribute8)                    addr_attribute8_meaning 
,      pa.addr_attribute9
,      xxas_com_vset_pkg.decode_descr_vset_value
         ('800'
         ,'PER_ADDRESSES'
         ,pa.addr_attribute_category
         ,'ADDR_ATTRIBUTE9'
         ,pa.addr_attribute9)                    addr_attribute9_meaning 
,      pa.addr_attribute10
,      xxas_com_vset_pkg.decode_descr_vset_value
         ('800'
         ,'PER_ADDRESSES'
         ,pa.addr_attribute_category
         ,'ADDR_ATTRIBUTE10'
         ,pa.addr_attribute10)                   addr_attribute10_meaning 
,      pa.addr_attribute11
,      xxas_com_vset_pkg.decode_descr_vset_value
         ('800'
         ,'PER_ADDRESSES'
         ,pa.addr_attribute_category
         ,'ADDR_ATTRIBUTE11'
         ,pa.addr_attribute11)                   addr_attribute11_meaning 
,      pa.addr_attribute12
,      xxas_com_vset_pkg.decode_descr_vset_value
         ('800'
         ,'PER_ADDRESSES'
         ,pa.addr_attribute_category
         ,'ADDR_ATTRIBUTE12'
         ,pa.addr_attribute12)                   addr_attribute12_meaning 
,      pa.addr_attribute13
,      xxas_com_vset_pkg.decode_descr_vset_value
         ('800'
         ,'PER_ADDRESSES'
         ,pa.addr_attribute_category
         ,'ADDR_ATTRIBUTE13'
         ,pa.addr_attribute13)                   addr_attribute13_meaning 
,      pa.addr_attribute14
,      xxas_com_vset_pkg.decode_descr_vset_value
         ('800'
         ,'PER_ADDRESSES'
         ,pa.addr_attribute_category
         ,'ADDR_ATTRIBUTE14'
         ,pa.addr_attribute14)                   addr_attribute14_meaning 
,      pa.addr_attribute15
,      xxas_com_vset_pkg.decode_descr_vset_value
         ('800'
         ,'PER_ADDRESSES'
         ,pa.addr_attribute_category
         ,'ADDR_ATTRIBUTE15'
         ,pa.addr_attribute15)                   addr_attribute15_meaning 
,      pa.addr_attribute16
,      xxas_com_vset_pkg.decode_descr_vset_value
         ('800'
         ,'PER_ADDRESSES'
         ,pa.addr_attribute_category
         ,'ADDR_ATTRIBUTE16'
         ,pa.addr_attribute16)                   addr_attribute16_meaning 
,      pa.addr_attribute17
,      xxas_com_vset_pkg.decode_descr_vset_value
         ('800'
         ,'PER_ADDRESSES'
         ,pa.addr_attribute_category
         ,'ADDR_ATTRIBUTE17'
         ,pa.addr_attribute17)                   addr_attribute17_meaning 
,      pa.addr_attribute18
,      xxas_com_vset_pkg.decode_descr_vset_value
         ('800'
         ,'PER_ADDRESSES'
         ,pa.addr_attribute_category
         ,'ADDR_ATTRIBUTE18'
         ,pa.addr_attribute18)                   addr_attribute18_meaning 
,      pa.addr_attribute19
,      xxas_com_vset_pkg.decode_descr_vset_value
         ('800'
         ,'PER_ADDRESSES'
         ,pa.addr_attribute_category
         ,'ADDR_ATTRIBUTE19'
         ,pa.addr_attribute19)                   addr_attribute19_meaning 
,      pa.addr_attribute20
,      xxas_com_vset_pkg.decode_descr_vset_value
         ('800'
         ,'PER_ADDRESSES'
         ,pa.addr_attribute_category
         ,'ADDR_ATTRIBUTE20'
         ,pa.addr_attribute20)                   addr_attribute20_meaning 
,      pa.add_information13
,      xxas_com_vset_pkg.decode_descr_vset_value
         ('800'
         ,'Address Structure'
         ,pa.style
         ,'ADD_INFORMATION13'
         ,pa.add_information13)                  add_information13_meaning
,      pa.add_information14
,      xxas_com_vset_pkg.decode_descr_vset_value
         ('800'
         ,'Address Structure'
         ,pa.style
         ,'ADD_INFORMATION14'
         ,pa.add_information14)                  add_information14_meaning
,      pa.add_information15
,      xxas_com_vset_pkg.decode_descr_vset_value
         ('800'
         ,'Address Structure'
         ,pa.style
         ,'ADD_INFORMATION15'
         ,pa.add_information15)                  add_information15_meaning
,      pa.add_information16
,      xxas_com_vset_pkg.decode_descr_vset_value
         ('800'
         ,'Address Structure'
         ,pa.style
         ,'ADD_INFORMATION16'
         ,pa.add_information16)                  add_information16_meaning
,      pa.add_information17
,      xxas_com_vset_pkg.decode_descr_vset_value
         ('800'
         ,'Address Structure'
         ,pa.style
         ,'ADD_INFORMATION17'
         ,pa.add_information17)                  add_information17_meaning
,      pa.add_information18
,      xxas_com_vset_pkg.decode_descr_vset_value
         ('800'
         ,'Address Structure'
         ,pa.style
         ,'ADD_INFORMATION18'
         ,pa.add_information18)                  add_information18_meaning
,      pa.add_information19
,      xxas_com_vset_pkg.decode_descr_vset_value
         ('800'
         ,'Address Structure'
         ,pa.style
         ,'ADD_INFORMATION19'
         ,pa.add_information19)                  add_information19_meaning
,      pa.add_information20
,      xxas_com_vset_pkg.decode_descr_vset_value
         ('800'
         ,'Address Structure'
         ,pa.style
         ,'ADD_INFORMATION20'
         ,pa.add_information20)                  add_information20_meaning
,      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  
,      xxas_hr_api_transaction_pkg.number_value
         (pend.selected_person_id
         ,pend.api_name
         ,pend.transaction_step_id
         ,'P_ADDRESS_ID')                        address_id_p
,      xxas_hr_api_transaction_pkg.number_value
         (pend.selected_person_id
         ,pend.api_name
         ,pend.transaction_step_id
         ,'P_OBJECT_VERSION_NUMBER')             object_version_number_p
,      xxas_hr_api_transaction_pkg.date_value
         (pend.selected_person_id
         ,pend.api_name
         ,pend.transaction_step_id
         ,'P_DATE_FROM')                         date_from_p
,      xxas_hr_api_transaction_pkg.date_value
         (pend.selected_person_id
         ,pend.api_name
         ,pend.transaction_step_id
         ,'P_DATE_TO')                           date_to_p
,      xxas_hr_api_transaction_pkg.varchar2_value
         (pend.selected_person_id
         ,pend.api_name
         ,pend.transaction_step_id
         ,'P_PRIMARY_FLAG')                      primary_flag_p
,      xxas_hr_api_transaction_pkg.varchar2_value
         (pend.selected_person_id
         ,pend.api_name
         ,pend.transaction_step_id
         ,'P_STYLE')                             style_p
,      xxas_hr_api_transaction_pkg.varchar2_value
         (pend.selected_person_id
         ,pend.api_name
         ,pend.transaction_step_id
         ,'P_ADDRESS_TYPE_CODE')                 address_type_p
,      xxas_util_pkg.decode_lookup
         ('ADDRESS_TYPE'
         ,xxas_hr_api_transaction_pkg.varchar2_value
           (pend.selected_person_id
           ,pend.api_name
           ,pend.transaction_step_id
           ,'P_ADDRESS_TYPE_CODE'))              address_type_meaning_p                                                        
,      xxas_hr_api_transaction_pkg.varchar2_value
         (pend.selected_person_id
         ,pend.api_name
         ,pend.transaction_step_id
         ,'P_ADDRESS_LINE1')                     address_line1_p
,      xxas_com_vset_pkg.decode_descr_vset_value
         ('800'
         ,'Address Structure'
         ,xxas_hr_api_transaction_pkg.varchar2_value
           (pend.selected_person_id
           ,pend.api_name
           ,pend.transaction_step_id
           ,'P_STYLE')
         ,'ADDRESS_LINE1'
         ,xxas_hr_api_transaction_pkg.varchar2_value
           (pend.selected_person_id
           ,pend.api_name
           ,pend.transaction_step_id
           ,'P_ADDRESS_LINE1'))                  address_line1_meaning_p 
,      xxas_hr_api_transaction_pkg.varchar2_value
         (pend.selected_person_id
         ,pend.api_name
         ,pend.transaction_step_id
         ,'P_ADDRESS_LINE2')                     address_line2_p
,      xxas_com_vset_pkg.decode_descr_vset_value
         ('800'
         ,'Address Structure'
         ,xxas_hr_api_transaction_pkg.varchar2_value
           (pend.selected_person_id
           ,pend.api_name
           ,pend.transaction_step_id
           ,'P_STYLE')
         ,'ADDRESS_LINE2'
         ,xxas_hr_api_transaction_pkg.varchar2_value
           (pend.selected_person_id
           ,pend.api_name
           ,pend.transaction_step_id
           ,'P_ADDRESS_LINE2'))                  address_line2_meaning_p 
,      xxas_hr_api_transaction_pkg.varchar2_value
         (pend.selected_person_id
         ,pend.api_name
         ,pend.transaction_step_id
         ,'P_ADDRESS_LINE3')                     address_line3_p
,      xxas_com_vset_pkg.decode_descr_vset_value
         ('800'
         ,'Address Structure'
         ,xxas_hr_api_transaction_pkg.varchar2_value
           (pend.selected_person_id
           ,pend.api_name
           ,pend.transaction_step_id
           ,'P_STYLE')
         ,'ADDRESS_LINE3'
         ,xxas_hr_api_transaction_pkg.varchar2_value
           (pend.selected_person_id
           ,pend.api_name
           ,pend.transaction_step_id
           ,'P_ADDRESS_LINE3'))                  address_line3_meaning_p 
, xxas_hr_api_transaction_pkg.varchar2_value
         (pend.selected_person_id
         ,pend.api_name
         ,pend.transaction_step_id
         ,'P_CITY')    town_or_city_p	
	 ,       xxas_com_vset_pkg.decode_descr_vset_value
         ('800'
         ,'Address Structure'
         ,xxas_hr_api_transaction_pkg.varchar2_value
           (pend.selected_person_id
           ,pend.api_name
           ,pend.transaction_step_id
           ,'P_STYLE')
         ,'TOWN_OR_CITY'
         ,xxas_hr_api_transaction_pkg.varchar2_value
           (pend.selected_person_id
           ,pend.api_name
           ,pend.transaction_step_id
           ,'P_CITY')) town_or_city_meaning_p	    
,      xxas_hr_api_transaction_pkg.varchar2_value
         (pend.selected_person_id
         ,pend.api_name
         ,pend.transaction_step_id
         ,'P_REGION1')                           region_1_p
,      xxas_com_vset_pkg.decode_descr_vset_value
         ('800'
         ,'Address Structure'
         ,xxas_hr_api_transaction_pkg.varchar2_value
           (pend.selected_person_id
           ,pend.api_name
           ,pend.transaction_step_id
           ,'P_STYLE')
         ,'REGION_1'
         ,xxas_hr_api_transaction_pkg.varchar2_value
           (pend.selected_person_id
           ,pend.api_name
           ,pend.transaction_step_id
           ,'P_REGION1'))                        region_1_meaning_p 
,      xxas_hr_api_transaction_pkg.varchar2_value
         (pend.selected_person_id
         ,pend.api_name
         ,pend.transaction_step_id
         ,'P_REGION2')                           region_2_p
,      xxas_com_vset_pkg.decode_descr_vset_value
         ('800'
         ,'Address Structure'
         ,xxas_hr_api_transaction_pkg.varchar2_value
           (pend.selected_person_id
           ,pend.api_name
           ,pend.transaction_step_id
           ,'P_STYLE')
         ,'REGION_2'
         ,xxas_hr_api_transaction_pkg.varchar2_value
           (pend.selected_person_id
           ,pend.api_name
           ,pend.transaction_step_id
           ,'P_REGION2'))                        region_2_meaning_p 
,      xxas_hr_api_transaction_pkg.varchar2_value
         (pend.selected_person_id
         ,pend.api_name
         ,pend.transaction_step_id
         ,'P_REGION3')                           region_3_p
,      xxas_com_vset_pkg.decode_descr_vset_value
         ('800'
         ,'Address Structure'
         ,xxas_hr_api_transaction_pkg.varchar2_value
           (pend.selected_person_id
           ,pend.api_name
           ,pend.transaction_step_id
           ,'P_STYLE')
         ,'REGION_3'
         ,xxas_hr_api_transaction_pkg.varchar2_value
           (pend.selected_person_id
           ,pend.api_name
           ,pend.transaction_step_id
           ,'P_REGION3'))                        region_3_meaning_p 
,      xxas_hr_api_transaction_pkg.varchar2_value
         (pend.selected_person_id
         ,pend.api_name
         ,pend.transaction_step_id
         ,'P_POSTAL_CODE')                       postal_code_p
,      xxas_com_vset_pkg.decode_descr_vset_value
         ('800'
         ,'Address Structure'
         ,xxas_hr_api_transaction_pkg.varchar2_value
           (pend.selected_person_id
           ,pend.api_name
           ,pend.transaction_step_id
           ,'P_STYLE')
         ,'POSTAL_CODE'
         ,xxas_hr_api_transaction_pkg.varchar2_value
           (pend.selected_person_id
           ,pend.api_name
           ,pend.transaction_step_id
           ,'P_POSTAL_CODE'))                     postal_code_meaning_p 
,      xxas_hr_api_transaction_pkg.varchar2_value
         (pend.selected_person_id
         ,pend.api_name
         ,pend.transaction_step_id
         ,'P_COUNTRY_CODE')                      country_code_p
,      xxas_com_vset_pkg.decode_descr_vset_value
         ('800'
         ,'Address Structure'
         ,xxas_hr_api_transaction_pkg.varchar2_value
           (pend.selected_person_id
           ,pend.api_name
           ,pend.transaction_step_id
           ,'P_STYLE')
         ,'COUNTRY'
         ,xxas_hr_api_transaction_pkg.varchar2_value
           (pend.selected_person_id
           ,pend.api_name
           ,pend.transaction_step_id
           ,'P_COUNTRY_CODE'))                   country_code_meaning_p 
,      xxas_hr_api_transaction_pkg.varchar2_value
         (pend.selected_person_id
         ,pend.api_name
         ,pend.transaction_step_id
         ,'P_TELEPHONE_NUMBER1')                 telephone_number1_p
,      xxas_com_vset_pkg.decode_descr_vset_value
         ('800'
         ,'Address Structure'
         ,xxas_hr_api_transaction_pkg.varchar2_value
           (pend.selected_person_id
           ,pend.api_name
           ,pend.transaction_step_id
           ,'P_STYLE')
         ,'TELEPHONE_NUMBER_1'
         ,xxas_hr_api_transaction_pkg.varchar2_value
           (pend.selected_person_id
           ,pend.api_name
           ,pend.transaction_step_id
           ,'P_TELEPHONE_NUMBER1'))              telephone_number1_meaning_p 
,      xxas_hr_api_transaction_pkg.varchar2_value
         (pend.selected_person_id
         ,pend.api_name
         ,pend.transaction_step_id
         ,'P_TELEPHONE_NUMBER2')                 telephone_number2_p
,      xxas_com_vset_pkg.decode_descr_vset_value
         ('800'
         ,'Address Structure'
         ,xxas_hr_api_transaction_pkg.varchar2_value
           (pend.selected_person_id
           ,pend.api_name
           ,pend.transaction_step_id
           ,'P_STYLE')
         ,'TELEPHONE_NUMBER_2'
         ,xxas_hr_api_transaction_pkg.varchar2_value
           (pend.selected_person_id
           ,pend.api_name
           ,pend.transaction_step_id
           ,'P_TELEPHONE_NUMBER2'))              telephone_number2_meaning_p 
,      xxas_hr_api_transaction_pkg.varchar2_value
         (pend.selected_person_id
         ,pend.api_name
         ,pend.transaction_step_id
         ,'P_TELEPHONE_NUMBER3')                 telephone_number3_p
,      xxas_com_vset_pkg.decode_descr_vset_value
         ('800'
         ,'Address Structure'
         ,xxas_hr_api_transaction_pkg.varchar2_value
           (pend.selected_person_id
           ,pend.api_name
           ,pend.transaction_step_id
           ,'P_STYLE')
         ,'TELEPHONE_NUMBER_3'
         ,xxas_hr_api_transaction_pkg.varchar2_value
           (pend.selected_person_id
           ,pend.api_name
           ,pend.transaction_step_id
           ,'P_TELEPHONE_NUMBER3'))              telephone_number3_meaning_p 
,      xxas_hr_api_transaction_pkg.varchar2_value
         (pend.selected_person_id
         ,pend.api_name
         ,pend.transaction_step_id
         ,'P_ADDR_ATTRIBUTE_CATEGORY')           addr_attribute_category_p
,      xxas_hr_api_transaction_pkg.varchar2_value
         (pend.selected_person_id
         ,pend.api_name
         ,pend.transaction_step_id
         ,'P_ADDR_ATTRIBUTE1')                   addr_attribute1_p
,      xxas_com_vset_pkg.decode_descr_vset_value
         ('800'
         ,'PER_ADDRESSES'
         ,xxas_hr_api_transaction_pkg.varchar2_value
           (pend.selected_person_id
           ,pend.api_name
           ,pend.transaction_step_id
           ,'P_ADDR_ATTRIBUTE_CATEGORY')
         ,'ADDR_ATTRIBUTE1'
         ,xxas_hr_api_transaction_pkg.varchar2_value
           (pend.selected_person_id
           ,pend.api_name
           ,pend.transaction_step_id
           ,'P_ADDR_ATTRIBUTE1'))                addr_attribute1_meaning_p 
,      xxas_hr_api_transaction_pkg.varchar2_value
         (pend.selected_person_id
         ,pend.api_name
         ,pend.transaction_step_id
         ,'P_ADDR_ATTRIBUTE2')                   addr_attribute2_p
,      xxas_com_vset_pkg.decode_descr_vset_value
         ('800'
         ,'PER_ADDRESSES'
         ,xxas_hr_api_transaction_pkg.varchar2_value
           (pend.selected_person_id
           ,pend.api_name
           ,pend.transaction_step_id
           ,'P_ADDR_ATTRIBUTE_CATEGORY')
         ,'ADDR_ATTRIBUTE2'
         ,xxas_hr_api_transaction_pkg.varchar2_value
           (pend.selected_person_id
           ,pend.api_name
           ,pend.transaction_step_id
           ,'P_ADDR_ATTRIBUTE2'))                addr_attribute2_meaning_p 
,      xxas_hr_api_transaction_pkg.varchar2_value
         (pend.selected_person_id
         ,pend.api_name
         ,pend.transaction_step_id
         ,'P_ADDR_ATTRIBUTE3')                   addr_attribute3_p
,      xxas_com_vset_pkg.decode_descr_vset_value
         ('800'
         ,'PER_ADDRESSES'
         ,xxas_hr_api_transaction_pkg.varchar2_value
           (pend.selected_person_id
           ,pend.api_name
           ,pend.transaction_step_id
           ,'P_ADDR_ATTRIBUTE_CATEGORY')
         ,'ADDR_ATTRIBUTE3'
         ,xxas_hr_api_transaction_pkg.varchar2_value
           (pend.selected_person_id
           ,pend.api_name
           ,pend.transaction_step_id
           ,'P_ADDR_ATTRIBUTE3'))                addr_attribute3_meaning_p 
,      xxas_hr_api_transaction_pkg.varchar2_value
         (pend.selected_person_id
         ,pend.api_name
         ,pend.transaction_step_id
         ,'P_ADDR_ATTRIBUTE4')                   addr_attribute4_p
,      xxas_com_vset_pkg.decode_descr_vset_value
         ('800'
         ,'PER_ADDRESSES'
         ,xxas_hr_api_transaction_pkg.varchar2_value
           (pend.selected_person_id
           ,pend.api_name
           ,pend.transaction_step_id
           ,'P_ADDR_ATTRIBUTE_CATEGORY')
         ,'ADDR_ATTRIBUTE4'
         ,xxas_hr_api_transaction_pkg.varchar2_value
           (pend.selected_person_id
           ,pend.api_name
           ,pend.transaction_step_id
           ,'P_ADDR_ATTRIBUTE4'))                addr_attribute4_meaning_p 
,      xxas_hr_api_transaction_pkg.varchar2_value
         (pend.selected_person_id
         ,pend.api_name
         ,pend.transaction_step_id
         ,'P_ADDR_ATTRIBUTE5')                   addr_attribute5_p
,      xxas_com_vset_pkg.decode_descr_vset_value
         ('800'
         ,'PER_ADDRESSES'
         ,xxas_hr_api_transaction_pkg.varchar2_value
           (pend.selected_person_id
           ,pend.api_name
           ,pend.transaction_step_id
           ,'P_ADDR_ATTRIBUTE_CATEGORY')
         ,'ADDR_ATTRIBUTE5'
         ,xxas_hr_api_transaction_pkg.varchar2_value
           (pend.selected_person_id
           ,pend.api_name
           ,pend.transaction_step_id
           ,'P_ADDR_ATTRIBUTE5'))                addr_attribute5_meaning_p 
,      xxas_hr_api_transaction_pkg.varchar2_value
         (pend.selected_person_id
         ,pend.api_name
         ,pend.transaction_step_id
         ,'P_ADDR_ATTRIBUTE6')                   addr_attribute6_p
,      xxas_com_vset_pkg.decode_descr_vset_value
         ('800'
         ,'PER_ADDRESSES'
         ,xxas_hr_api_transaction_pkg.varchar2_value
           (pend.selected_person_id
           ,pend.api_name
           ,pend.transaction_step_id
           ,'P_ADDR_ATTRIBUTE_CATEGORY')
         ,'ADDR_ATTRIBUTE6'
         ,xxas_hr_api_transaction_pkg.varchar2_value
           (pend.selected_person_id
           ,pend.api_name
           ,pend.transaction_step_id
           ,'P_ADDR_ATTRIBUTE6'))                addr_attribute6_meaning_p 
,      xxas_hr_api_transaction_pkg.varchar2_value
         (pend.selected_person_id
         ,pend.api_name
         ,pend.transaction_step_id
         ,'P_ADDR_ATTRIBUTE7')                   addr_attribute7_p
,      xxas_com_vset_pkg.decode_descr_vset_value
         ('800'
         ,'PER_ADDRESSES'
         ,xxas_hr_api_transaction_pkg.varchar2_value
           (pend.selected_person_id
           ,pend.api_name
           ,pend.transaction_step_id
           ,'P_ADDR_ATTRIBUTE_CATEGORY')
         ,'ADDR_ATTRIBUTE7'
         ,xxas_hr_api_transaction_pkg.varchar2_value
           (pend.selected_person_id
           ,pend.api_name
           ,pend.transaction_step_id
           ,'P_ADDR_ATTRIBUTE7'))                addr_attribute7_meaning_p 
,      xxas_hr_api_transaction_pkg.varchar2_value
         (pend.selected_person_id
         ,pend.api_name
         ,pend.transaction_step_id
         ,'P_ADDR_ATTRIBUTE8')                   addr_attribute8_p
,      xxas_com_vset_pkg.decode_descr_vset_value
         ('800'
         ,'PER_ADDRESSES'
         ,xxas_hr_api_transaction_pkg.varchar2_value
           (pend.selected_person_id
           ,pend.api_name
           ,pend.transaction_step_id
           ,'P_ADDR_ATTRIBUTE_CATEGORY')
         ,'ADDR_ATTRIBUTE8'
         ,xxas_hr_api_transaction_pkg.varchar2_value
           (pend.selected_person_id
           ,pend.api_name
           ,pend.transaction_step_id
           ,'P_ADDR_ATTRIBUTE8'))                addr_attribute8_meaning_p 
,      xxas_hr_api_transaction_pkg.varchar2_value
         (pend.selected_person_id
         ,pend.api_name
         ,pend.transaction_step_id
         ,'P_ADDR_ATTRIBUTE9')                   addr_attribute9_p
,      xxas_com_vset_pkg.decode_descr_vset_value
         ('800'
         ,'PER_ADDRESSES'
         ,xxas_hr_api_transaction_pkg.varchar2_value
           (pend.selected_person_id
           ,pend.api_name
           ,pend.transaction_step_id
           ,'P_ADDR_ATTRIBUTE_CATEGORY')
         ,'ADDR_ATTRIBUTE9'
         ,xxas_hr_api_transaction_pkg.varchar2_value
           (pend.selected_person_id
           ,pend.api_name
           ,pend.transaction_step_id
           ,'P_ADDR_ATTRIBUTE9'))                addr_attribute9_meaning_p 
,      xxas_hr_api_transaction_pkg.varchar2_value
         (pend.selected_person_id
         ,pend.api_name
         ,pend.transaction_step_id
         ,'P_ADDR_ATTRIBUTE10')                  addr_attribute10_p
,      xxas_com_vset_pkg.decode_descr_vset_value
         ('800'
         ,'PER_ADDRESSES'
         ,xxas_hr_api_transaction_pkg.varchar2_value
           (pend.selected_person_id
           ,pend.api_name
           ,pend.transaction_step_id
           ,'P_ADDR_ATTRIBUTE_CATEGORY')
         ,'ADDR_ATTRIBUTE10'
         ,xxas_hr_api_transaction_pkg.varchar2_value
           (pend.selected_person_id
           ,pend.api_name
           ,pend.transaction_step_id
           ,'P_ADDR_ATTRIBUTE10'))               addr_attribute10_meaning_p 
,      xxas_hr_api_transaction_pkg.varchar2_value
         (pend.selected_person_id
         ,pend.api_name
         ,pend.transaction_step_id
         ,'P_ADDR_ATTRIBUTE11')                  addr_attribute11_p
,      xxas_com_vset_pkg.decode_descr_vset_value
         ('800'
         ,'PER_ADDRESSES'
         ,xxas_hr_api_transaction_pkg.varchar2_value
           (pend.selected_person_id
           ,pend.api_name
           ,pend.transaction_step_id
           ,'P_ADDR_ATTRIBUTE_CATEGORY')
         ,'ADDR_ATTRIBUTE11'
         ,xxas_hr_api_transaction_pkg.varchar2_value
           (pend.selected_person_id
           ,pend.api_name
           ,pend.transaction_step_id
           ,'P_ADDR_ATTRIBUTE11'))               addr_attribute11_meaning_p 
,      xxas_hr_api_transaction_pkg.varchar2_value
         (pend.selected_person_id
         ,pend.api_name
         ,pend.transaction_step_id
         ,'P_ADDR_ATTRIBUTE12')                  addr_attribute12_p
,      xxas_com_vset_pkg.decode_descr_vset_value
         ('800'
         ,'PER_ADDRESSES'
         ,xxas_hr_api_transaction_pkg.varchar2_value
           (pend.selected_person_id
           ,pend.api_name
           ,pend.transaction_step_id
           ,'P_ADDR_ATTRIBUTE_CATEGORY')
         ,'ADDR_ATTRIBUTE12'
         ,xxas_hr_api_transaction_pkg.varchar2_value
           (pend.selected_person_id
           ,pend.api_name
           ,pend.transaction_step_id
           ,'P_ADDR_ATTRIBUTE12'))               addr_attribute12_meaning_p 
,      xxas_hr_api_transaction_pkg.varchar2_value
         (pend.selected_person_id
         ,pend.api_name
         ,pend.transaction_step_id
         ,'P_ADDR_ATTRIBUTE13')                  addr_attribute13_p
,      xxas_com_vset_pkg.decode_descr_vset_value
         ('800'
         ,'PER_ADDRESSES'
         ,xxas_hr_api_transaction_pkg.varchar2_value
           (pend.selected_person_id
           ,pend.api_name
           ,pend.transaction_step_id
           ,'P_ADDR_ATTRIBUTE_CATEGORY')
         ,'ADDR_ATTRIBUTE13'
         ,xxas_hr_api_transaction_pkg.varchar2_value
           (pend.selected_person_id
           ,pend.api_name
           ,pend.transaction_step_id
           ,'P_ADDR_ATTRIBUTE13'))               addr_attribute13_meaning_p 
,      xxas_hr_api_transaction_pkg.varchar2_value
         (pend.selected_person_id
         ,pend.api_name
         ,pend.transaction_step_id
         ,'P_ADDR_ATTRIBUTE14')                  addr_attribute14_p
,      xxas_com_vset_pkg.decode_descr_vset_value
         ('800'
         ,'PER_ADDRESSES'
         ,xxas_hr_api_transaction_pkg.varchar2_value
           (pend.selected_person_id
           ,pend.api_name
           ,pend.transaction_step_id
           ,'P_ADDR_ATTRIBUTE_CATEGORY')
         ,'ADDR_ATTRIBUTE14'
         ,xxas_hr_api_transaction_pkg.varchar2_value
           (pend.selected_person_id
           ,pend.api_name
           ,pend.transaction_step_id
           ,'P_ADDR_ATTRIBUTE14'))               addr_attribute14_meaning_p 
,      xxas_hr_api_transaction_pkg.varchar2_value
         (pend.selected_person_id
         ,pend.api_name
         ,pend.transaction_step_id
         ,'P_ADDR_ATTRIBUTE15')                  addr_attribute15_p
,      xxas_com_vset_pkg.decode_descr_vset_value
         ('800'
         ,'PER_ADDRESSES'
         ,xxas_hr_api_transaction_pkg.varchar2_value
           (pend.selected_person_id
           ,pend.api_name
           ,pend.transaction_step_id
           ,'P_ADDR_ATTRIBUTE_CATEGORY')
         ,'ADDR_ATTRIBUTE15'
         ,xxas_hr_api_transaction_pkg.varchar2_value
           (pend.selected_person_id
           ,pend.api_name
           ,pend.transaction_step_id
           ,'P_ADDR_ATTRIBUTE15'))               addr_attribute15_meaning_p 
,      xxas_hr_api_transaction_pkg.varchar2_value
         (pend.selected_person_id
         ,pend.api_name
         ,pend.transaction_step_id
         ,'P_ADDR_ATTRIBUTE16')                  addr_attribute16_p
,      xxas_com_vset_pkg.decode_descr_vset_value
         ('800'
         ,'PER_ADDRESSES'
         ,xxas_hr_api_transaction_pkg.varchar2_value
           (pend.selected_person_id
           ,pend.api_name
           ,pend.transaction_step_id
           ,'P_ADDR_ATTRIBUTE_CATEGORY')
         ,'ADDR_ATTRIBUTE16'
         ,xxas_hr_api_transaction_pkg.varchar2_value
           (pend.selected_person_id
           ,pend.api_name
           ,pend.transaction_step_id
           ,'P_ADDR_ATTRIBUTE16'))               addr_attribute16_meaning_p 
,      xxas_hr_api_transaction_pkg.varchar2_value
         (pend.selected_person_id
         ,pend.api_name
         ,pend.transaction_step_id
         ,'P_ADDR_ATTRIBUTE17')                  addr_attribute17_p
,      xxas_com_vset_pkg.decode_descr_vset_value
         ('800'
         ,'PER_ADDRESSES'
         ,xxas_hr_api_transaction_pkg.varchar2_value
           (pend.selected_person_id
           ,pend.api_name
           ,pend.transaction_step_id
           ,'P_ADDR_ATTRIBUTE_CATEGORY')
         ,'ADDR_ATTRIBUTE17'
         ,xxas_hr_api_transaction_pkg.varchar2_value
           (pend.selected_person_id
           ,pend.api_name
           ,pend.transaction_step_id
           ,'P_ADDR_ATTRIBUTE17'))               addr_attribute17_meaning_p 
,      xxas_hr_api_transaction_pkg.varchar2_value
         (pend.selected_person_id
         ,pend.api_name
         ,pend.transaction_step_id
         ,'P_ADDR_ATTRIBUTE18')                  addr_attribute18_p
,      xxas_com_vset_pkg.decode_descr_vset_value
         ('800'
         ,'PER_ADDRESSES'
         ,xxas_hr_api_transaction_pkg.varchar2_value
           (pend.selected_person_id
           ,pend.api_name
           ,pend.transaction_step_id
           ,'P_ADDR_ATTRIBUTE_CATEGORY')
         ,'ADDR_ATTRIBUTE18'
         ,xxas_hr_api_transaction_pkg.varchar2_value
           (pend.selected_person_id
           ,pend.api_name
           ,pend.transaction_step_id
           ,'P_ADDR_ATTRIBUTE18'))               addr_attribute18_meaning_p 
,      xxas_hr_api_transaction_pkg.varchar2_value
         (pend.selected_person_id
         ,pend.api_name
         ,pend.transaction_step_id
         ,'P_ADDR_ATTRIBUTE19')                  addr_attribute19_p
,      xxas_com_vset_pkg.decode_descr_vset_value
         ('800'
         ,'PER_ADDRESSES'
         ,xxas_hr_api_transaction_pkg.varchar2_value
           (pend.selected_person_id
           ,pend.api_name
           ,pend.transaction_step_id
           ,'P_ADDR_ATTRIBUTE_CATEGORY')
         ,'ADDR_ATTRIBUTE19'
         ,xxas_hr_api_transaction_pkg.varchar2_value
           (pend.selected_person_id
           ,pend.api_name
           ,pend.transaction_step_id
           ,'P_ADDR_ATTRIBUTE19'))               addr_attribute19_meaning_p 
,      xxas_hr_api_transaction_pkg.varchar2_value
         (pend.selected_person_id
         ,pend.api_name
         ,pend.transaction_step_id
         ,'P_ADDR_ATTRIBUTE20')                  addr_attribute20_p
,      xxas_com_vset_pkg.decode_descr_vset_value
         ('800'
         ,'PER_ADDRESSES'
         ,xxas_hr_api_transaction_pkg.varchar2_value
           (pend.selected_person_id
           ,pend.api_name
           ,pend.transaction_step_id
           ,'P_ADDR_ATTRIBUTE_CATEGORY')
         ,'ADDR_ATTRIBUTE20'
         ,xxas_hr_api_transaction_pkg.varchar2_value
           (pend.selected_person_id
           ,pend.api_name
           ,pend.transaction_step_id
           ,'P_ADDR_ATTRIBUTE20'))               addr_attribute20_meaning_p 
,      xxas_hr_api_transaction_pkg.varchar2_value
         (pend.selected_person_id
         ,pend.api_name
         ,pend.transaction_step_id
         ,'P_ADD_INFORMATION13')                 add_information13_p
,      xxas_com_vset_pkg.decode_descr_vset_value
         ('800'
         ,'Address Structure'
         ,xxas_hr_api_transaction_pkg.varchar2_value
           (pend.selected_person_id
           ,pend.api_name
           ,pend.transaction_step_id
           ,'P_STYLE')
         ,'ADD_INFORMATION13'
         ,xxas_hr_api_transaction_pkg.varchar2_value
           (pend.selected_person_id
           ,pend.api_name
           ,pend.transaction_step_id
           ,'P_ADD_INFORMATION13'))              add_information13_meaning_p
,      xxas_hr_api_transaction_pkg.varchar2_value
         (pend.selected_person_id
         ,pend.api_name
         ,pend.transaction_step_id
         ,'P_ADD_INFORMATION14')                 add_information14_p
,      xxas_com_vset_pkg.decode_descr_vset_value
         ('800'
         ,'Address Structure'
         ,xxas_hr_api_transaction_pkg.varchar2_value
           (pend.selected_person_id
           ,pend.api_name
           ,pend.transaction_step_id
           ,'P_STYLE')
         ,'ADD_INFORMATION14'
         ,xxas_hr_api_transaction_pkg.varchar2_value
           (pend.selected_person_id
           ,pend.api_name
           ,pend.transaction_step_id
           ,'P_ADD_INFORMATION14'))              add_information14_meaning_p
,      xxas_hr_api_transaction_pkg.varchar2_value
         (pend.selected_person_id
         ,pend.api_name
         ,pend.transaction_step_id
         ,'P_ADD_INFORMATION15')                 add_information15_p
,      xxas_com_vset_pkg.decode_descr_vset_value
         ('800'
         ,'Address Structure'
         ,xxas_hr_api_transaction_pkg.varchar2_value
           (pend.selected_person_id
           ,pend.api_name
           ,pend.transaction_step_id
           ,'P_STYLE')
         ,'ADD_INFORMATION15'
         ,xxas_hr_api_transaction_pkg.varchar2_value
           (pend.selected_person_id
           ,pend.api_name
           ,pend.transaction_step_id
           ,'P_ADD_INFORMATION15'))              add_information15_meaning_p
,      xxas_hr_api_transaction_pkg.varchar2_value
         (pend.selected_person_id
         ,pend.api_name
         ,pend.transaction_step_id
         ,'P_ADD_INFORMATION16')                 add_information16_p
,      xxas_com_vset_pkg.decode_descr_vset_value
         ('800'
         ,'Address Structure'
         ,xxas_hr_api_transaction_pkg.varchar2_value
           (pend.selected_person_id
           ,pend.api_name
           ,pend.transaction_step_id
           ,'P_STYLE')
         ,'ADD_INFORMATION16'
         ,xxas_hr_api_transaction_pkg.varchar2_value
           (pend.selected_person_id
           ,pend.api_name
           ,pend.transaction_step_id
           ,'P_ADD_INFORMATION16'))              add_information16_meaning_p
,      xxas_hr_api_transaction_pkg.varchar2_value
         (pend.selected_person_id
         ,pend.api_name
         ,pend.transaction_step_id
         ,'P_ADD_INFORMATION17')                 add_information17_p
,      xxas_com_vset_pkg.decode_descr_vset_value
         ('800'
         ,'Address Structure'
         ,xxas_hr_api_transaction_pkg.varchar2_value
           (pend.selected_person_id
           ,pend.api_name
           ,pend.transaction_step_id
           ,'P_STYLE')
         ,'ADD_INFORMATION17'
         ,xxas_hr_api_transaction_pkg.varchar2_value
           (pend.selected_person_id
           ,pend.api_name
           ,pend.transaction_step_id
           ,'P_ADD_INFORMATION17'))              add_information17_meaning_p
,      xxas_hr_api_transaction_pkg.varchar2_value
         (pend.selected_person_id
         ,pend.api_name
         ,pend.transaction_step_id
         ,'P_ADD_INFORMATION18')                 add_information18_p
,      xxas_com_vset_pkg.decode_descr_vset_value
         ('800'
         ,'Address Structure'
         ,xxas_hr_api_transaction_pkg.varchar2_value
           (pend.selected_person_id
           ,pend.api_name
           ,pend.transaction_step_id
           ,'P_STYLE')
         ,'ADD_INFORMATION18'
         ,xxas_hr_api_transaction_pkg.varchar2_value
           (pend.selected_person_id
           ,pend.api_name
           ,pend.transaction_step_id
           ,'P_ADD_INFORMATION18'))              add_information18_meaning_p
,      xxas_hr_api_transaction_pkg.varchar2_value
         (pend.selected_person_id
         ,pend.api_name
         ,pend.transaction_step_id
         ,'P_ADD_INFORMATION19')                 add_information19_p
,      xxas_com_vset_pkg.decode_descr_vset_value
         ('800'
         ,'Address Structure'
         ,xxas_hr_api_transaction_pkg.varchar2_value
           (pend.selected_person_id
           ,pend.api_name
           ,pend.transaction_step_id
           ,'P_STYLE')
         ,'ADD_INFORMATION19'
         ,xxas_hr_api_transaction_pkg.varchar2_value
           (pend.selected_person_id
           ,pend.api_name
           ,pend.transaction_step_id
           ,'P_ADD_INFORMATION19'))              add_information19_meaning_p
,      xxas_hr_api_transaction_pkg.varchar2_value
         (pend.selected_person_id
         ,pend.api_name
         ,pend.transaction_step_id
         ,'P_ADD_INFORMATION20')                 add_information20_p
,      xxas_com_vset_pkg.decode_descr_vset_value
         ('800'
         ,'Address Structure'
         ,xxas_hr_api_transaction_pkg.varchar2_value
           (pend.selected_person_id
           ,pend.api_name
           ,pend.transaction_step_id
           ,'P_STYLE')
         ,'ADD_INFORMATION20'
         ,xxas_hr_api_transaction_pkg.varchar2_value
           (pend.selected_person_id
           ,pend.api_name
           ,pend.transaction_step_id
           ,'P_ADD_INFORMATION20'))              add_information20_meaning_p
,     'PQH_SS_ATTACHMENT'                        attachment_entity
,      pend.transaction_id                       attachment_pk1
FROM   per_addresses pa
,     (SELECT ppa.person_id 
       ,      ppa.address_id
       ,      hats.api_name
       ,      hat.transaction_id
       ,      pid.transaction_step_id
       ,      'UPDATE'                                                   transaction_type
       ,      hat.status                                                 hr_api_status_code
       ,      xxas_selfservice_workflow_pkg.transcode_status(hat.status) workflow_status_code
       ,      hat.selected_person_id
       FROM   per_addresses               ppa
       ,      hr_api_transactions         hat
       ,      hr_api_transaction_steps    hats
       ,      hr_api_transaction_values   pid
       ,      hr_api_transaction_values   aid
       WHERE  ppa.person_id            = hat.selected_person_id
       AND    hat.selected_person_id   = fnd_profile.value('PER_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_ADDRESS_SS.PROCESS_API'
       AND    hats.transaction_step_id = pid.transaction_step_id
       AND    pid.name                 = 'P_ADDRESS_ID'
       AND    pid.number_value         = ppa.address_id
       AND    hats.transaction_step_id = aid.transaction_step_id
       AND    aid.name                 = 'P_ACTION'
       AND    aid.varchar2_value      IN ('CORRECT', 'CHANGE')
       UNION ALL
       SELECT ppa.person_id 
       ,      ppa.address_id
       ,      hats.api_name
       ,      hat.transaction_id
       ,      hats.transaction_step_id
       ,      'UPDATE'                                                   transaction_type
       ,      hat.status                                                 hr_api_status_code
       ,      xxas_selfservice_workflow_pkg.transcode_status(hat.status) workflow_status_code
       ,      hat.selected_person_id
       FROM   per_addresses               ppa
       ,      per_contact_relationships   pcrp
       ,      hr_api_transactions         hat
       ,      hr_api_transaction_steps    hats
       ,      hr_api_transaction_values   pid
       ,      hr_api_transaction_values   aid
       WHERE  ppa.person_id            = pcrp.contact_person_id
       AND    pcrp.contact_type       <> 'EMRG'
       AND    pcrp.person_id           = hat.selected_person_id
       AND    hat.selected_person_id   = fnd_profile.value('PER_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_ADDRESS_SS.PROCESS_API'
       AND    hats.transaction_step_id = pid.transaction_step_id
       AND    pid.name                 = 'P_ADDRESS_ID'
       AND    pid.number_value         = ppa.address_id
       AND    hats.transaction_step_id = aid.transaction_step_id
       AND    aid.name                 = 'P_ACTION'
       AND    aid.varchar2_value      IN ('CORRECT', 'CHANGE')
       UNION ALL
       SELECT ppa.person_id 
       ,      ppa.address_id
       ,      'HR_PROCESS_ADDRESS_SS.PROCESS_API'
       ,      hat.transaction_id
       ,      hatv.transaction_step_id
       ,      'DELETE'                                                   transaction_type
       ,      hat.status                                                 hr_api_status_code
       ,      xxas_selfservice_workflow_pkg.transcode_status(hat.status) workflow_status_code
       ,      hat.selected_person_id
       FROM   per_addresses             ppa
       ,      per_contact_relationships pcrp
       ,      hr_api_transactions       hat
       ,      hr_api_transaction_steps  hats
       ,      hr_api_transaction_values hatv
       ,      hr_api_transaction_values rwm
       WHERE  ppa.person_id            = pcrp.contact_person_id
       AND    pcrp.contact_type       <> 'EMRG'
       AND    pcrp.person_id           = hat.selected_person_id
       AND    hat.selected_person_id   = fnd_profile.value('PER_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_CONTACT_SS.PROCESS_API'
       AND    hats.transaction_step_id = hatv.transaction_step_id
       AND    hatv.name                = 'P_CONTACT_PERSON_ID'
       AND    hatv.number_value        = pcrp.contact_person_id
       AND    hats.transaction_step_id = rwm.transaction_step_id
       AND    rwm.name                 = 'P_RLTD_PER_RSDS_W_DSGNTR_FLAG'
       AND    rwm.varchar2_value       = 'Y') pend  
WHERE  pa.person_id  = pend.person_id(+)
AND    pa.address_id = pend.address_id(+)
UNION ALL
SELECT hat.selected_person_id                    person_id
,      hat.selected_person_id                    per_person_id
,      NULL                                      address_id
,      NULL                                      object_version_number
,      NULL                                      date_from
,      NULL                                      date_to
,      NULL                                      primary_flag
,      NULL                                      style
,      NULL                                      address_type
,      NULL                                      address_type_meaning                                     
,      NULL                                      address_line1
,      NULL                                      address_line1_meaning 
,      NULL                                      address_line2
,      NULL                                      address_line2_meaning 
,      NULL                                      address_line3
,      NULL                                      address_line3_meaning 
,      NULL                                      town_or_city
,      NULL                                      town_or_city_meaning 
,      NULL                                      region_1
,      NULL                                      region_1_meaning 
,      NULL                                      region_2
,      NULL                                      region_2_meaning 
,      NULL                                      region_3
,      NULL                                      region_3_meaning 
,      NULL                                      postal_code
,      NULL                                      postal_code_meaning 
,      NULL                                      country
,      NULL                                      country_meaning 
,      NULL                                      telephone_number_1
,      NULL                                      telephone_number_1_meaning 
,      NULL                                      telephone_number_2
,      NULL                                      telephone_number_2_meaning 
,      NULL                                      telephone_number_3
,      NULL                                      telephone_number_3_meaning
,      NULL                                      latitude
,      NULL                                      longtitude 
,      NULL                                      addr_attribute_category
,      NULL                                      addr_attribute1
,      NULL                                      addr_attribute1_meaning 
,      NULL                                      addr_attribute2
,      NULL                                      addr_attribute2_meaning 
,      NULL                                      addr_attribute3
,      NULL                                      addr_attribute3_meaning 
,      NULL                                      addr_attribute4
,      NULL                                      addr_attribute4_meaning 
,      NULL                                      addr_attribute5
,      NULL                                      addr_attribute5_meaning 
,      NULL                                      addr_attribute6
,      NULL                                      addr_attribute6_meaning 
,      NULL                                      addr_attribute7
,      NULL                                      addr_attribute7_meaning 
,      NULL                                      addr_attribute8
,      NULL                                      addr_attribute8_meaning 
,      NULL                                      addr_attribute9
,      NULL                                      addr_attribute9_meaning 
,      NULL                                      addr_attribute10
,      NULL                                      addr_attribute10_meaning 
,      NULL                                      addr_attribute11
,      NULL                                      addr_attribute11_meaning 
,      NULL                                      addr_attribute12
,      NULL                                      addr_attribute12_meaning 
,      NULL                                      addr_attribute13
,      NULL                                      addr_attribute13_meaning 
,      NULL                                      addr_attribute14
,      NULL                                      addr_attribute14_meaning 
,      NULL                                      addr_attribute15
,      NULL                                      addr_attribute15_meaning 
,      NULL                                      addr_attribute16
,      NULL                                      addr_attribute16_meaning 
,      NULL                                      addr_attribute17
,      NULL                                      addr_attribute17_meaning 
,      NULL                                      addr_attribute18
,      NULL                                      addr_attribute18_meaning 
,      NULL                                      addr_attribute19
,      NULL                                      addr_attribute19_meaning 
,      NULL                                      addr_attribute20
,      NULL                                      addr_attribute20_meaning 
,      NULL                                      add_information13
,      NULL                                      add_information13_meaning
,      NULL                                      add_information14
,      NULL                                      add_information14_meaning
,      NULL                                      add_information15
,      NULL                                      add_information15_meaning
,      NULL                                      add_information16
,      NULL                                      add_information16_meaning
,      NULL                                      add_information17
,      NULL                                      add_information17_meaning
,      NULL                                      add_information18
,      NULL                                      add_information18_meaning
,      NULL                                      add_information19
,      NULL                                      add_information19_meaning
,      NULL                                      add_information20
,      NULL                                      add_information20_meaning
,      hat.transaction_id
,      hats.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_ADDRESS_ID')                        address_id_p
,      xxas_hr_api_transaction_pkg.number_value
         (hat.selected_person_id
         ,hats.api_name
         ,hats.transaction_step_id
         ,'P_OBJECT_VERSION_NUMBER')             object_version_number_p
,      xxas_hr_api_transaction_pkg.date_value
         (hat.selected_person_id
         ,hats.api_name
         ,hats.transaction_step_id
         ,'P_DATE_FROM')                         date_from_p
,      xxas_hr_api_transaction_pkg.date_value
         (hat.selected_person_id
         ,hats.api_name
         ,hats.transaction_step_id
         ,'P_DATE_TO')                           date_to_p
,      xxas_hr_api_transaction_pkg.varchar2_value
         (hat.selected_person_id
         ,hats.api_name
         ,hats.transaction_step_id
         ,'P_PRIMARY_FLAG')                      primary_flag_p
,      xxas_hr_api_transaction_pkg.varchar2_value
         (hat.selected_person_id
         ,hats.api_name
         ,hats.transaction_step_id
         ,'P_STYLE')                             style_p
,      xxas_hr_api_transaction_pkg.varchar2_value
         (hat.selected_person_id
         ,hats.api_name
         ,hats.transaction_step_id
         ,'P_ADDRESS_TYPE_CODE')                 address_type_p
,      xxas_util_pkg.decode_lookup
         ('ADDRESS_TYPE'
         ,xxas_hr_api_transaction_pkg.varchar2_value
           (hat.selected_person_id
           ,hats.api_name
           ,hats.transaction_step_id
           ,'P_ADDRESS_TYPE_CODE'))              address_type_meaning_p                                                        
,      xxas_hr_api_transaction_pkg.varchar2_value
         (hat.selected_person_id
         ,hats.api_name
         ,hats.transaction_step_id
         ,'P_ADDRESS_LINE1')                     address_line1_p
,      xxas_com_vset_pkg.decode_descr_vset_value
         ('800'
         ,'Address Structure'
         ,xxas_hr_api_transaction_pkg.varchar2_value
           (hat.selected_person_id
           ,hats.api_name
           ,hats.transaction_step_id
           ,'P_STYLE')
         ,'ADDRESS_LINE1'
         ,xxas_hr_api_transaction_pkg.varchar2_value
           (hat.selected_person_id
           ,hats.api_name
           ,hats.transaction_step_id
           ,'P_ADDRESS_LINE1'))                  address_line1_meaning_p 
,      xxas_hr_api_transaction_pkg.varchar2_value
         (hat.selected_person_id
         ,hats.api_name
         ,hats.transaction_step_id
         ,'P_ADDRESS_LINE2')                     address_line2_p
,      xxas_com_vset_pkg.decode_descr_vset_value
         ('800'
         ,'Address Structure'
         ,xxas_hr_api_transaction_pkg.varchar2_value
           (hat.selected_person_id
           ,hats.api_name
           ,hats.transaction_step_id
           ,'P_STYLE')
         ,'ADDRESS_LINE2'
         ,xxas_hr_api_transaction_pkg.varchar2_value
           (hat.selected_person_id
           ,hats.api_name
           ,hats.transaction_step_id
           ,'P_ADDRESS_LINE2'))                  address_line2_meaning_p 
,      xxas_hr_api_transaction_pkg.varchar2_value
         (hat.selected_person_id
         ,hats.api_name
         ,hats.transaction_step_id
         ,'P_ADDRESS_LINE3')                     address_line3_p
,      xxas_com_vset_pkg.decode_descr_vset_value
         ('800'
         ,'Address Structure'
         ,xxas_hr_api_transaction_pkg.varchar2_value
           (hat.selected_person_id
           ,hats.api_name
           ,hats.transaction_step_id
           ,'P_STYLE')
         ,'ADDRESS_LINE3'
         ,xxas_hr_api_transaction_pkg.varchar2_value
           (hat.selected_person_id
           ,hats.api_name
           ,hats.transaction_step_id
           ,'P_ADDRESS_LINE3'))                  address_line3_meaning_p 
,      xxas_hr_api_transaction_pkg.varchar2_value
         (hat.selected_person_id
         ,hats.api_name
         ,hats.transaction_step_id
          ,'P_CITY')                   town_or_city_p
,      xxas_com_vset_pkg.decode_descr_vset_value
         ('800'
         ,'Address Structure'
         ,xxas_hr_api_transaction_pkg.varchar2_value
           (hat.selected_person_id
           ,hats.api_name
           ,hats.transaction_step_id
           ,'P_STYLE')
         ,'TOWN_OR_CITY'
         ,xxas_hr_api_transaction_pkg.varchar2_value
           (hat.selected_person_id
           ,hats.api_name
           ,hats.transaction_step_id
           ,'P_CITY'))                  town_or_city_meaning_p
,      xxas_hr_api_transaction_pkg.varchar2_value
         (hat.selected_person_id
         ,hats.api_name
         ,hats.transaction_step_id
         ,'P_REGION1')                           region_1_p
,      xxas_com_vset_pkg.decode_descr_vset_value
         ('800'
         ,'Address Structure'
         ,xxas_hr_api_transaction_pkg.varchar2_value
           (hat.selected_person_id
           ,hats.api_name
           ,hats.transaction_step_id
           ,'P_STYLE')
         ,'REGION_1'
         ,xxas_hr_api_transaction_pkg.varchar2_value
           (hat.selected_person_id
           ,hats.api_name
           ,hats.transaction_step_id
           ,'P_REGION1'))                        region_1_meaning_p 
,      xxas_hr_api_transaction_pkg.varchar2_value
         (hat.selected_person_id
         ,hats.api_name
         ,hats.transaction_step_id
         ,'P_REGION2')                           region_2_p
,      xxas_com_vset_pkg.decode_descr_vset_value
         ('800'
         ,'Address Structure'
         ,xxas_hr_api_transaction_pkg.varchar2_value
           (hat.selected_person_id
           ,hats.api_name
           ,hats.transaction_step_id
           ,'P_STYLE')
         ,'REGION_2'
         ,xxas_hr_api_transaction_pkg.varchar2_value
           (hat.selected_person_id
           ,hats.api_name
           ,hats.transaction_step_id
           ,'P_REGION2'))                        region_2_meaning_p 
,      xxas_hr_api_transaction_pkg.varchar2_value
         (hat.selected_person_id
         ,hats.api_name
         ,hats.transaction_step_id
         ,'P_REGION3')                           region_3_p
,      xxas_com_vset_pkg.decode_descr_vset_value
         ('800'
         ,'Address Structure'
         ,xxas_hr_api_transaction_pkg.varchar2_value
           (hat.selected_person_id
           ,hats.api_name
           ,hats.transaction_step_id
           ,'P_STYLE')
         ,'REGION_3'
         ,xxas_hr_api_transaction_pkg.varchar2_value
           (hat.selected_person_id
           ,hats.api_name
           ,hats.transaction_step_id
           ,'P_REGION3'))                        region_3_meaning_p 
,      xxas_hr_api_transaction_pkg.varchar2_value
         (hat.selected_person_id
         ,hats.api_name
         ,hats.transaction_step_id
         ,'P_POSTAL_CODE')                       postal_code_p
,      xxas_com_vset_pkg.decode_descr_vset_value
         ('800'
         ,'Address Structure'
         ,xxas_hr_api_transaction_pkg.varchar2_value
           (hat.selected_person_id
           ,hats.api_name
           ,hats.transaction_step_id
           ,'P_STYLE')
         ,'POSTAL_CODE'
         ,xxas_hr_api_transaction_pkg.varchar2_value
           (hat.selected_person_id
           ,hats.api_name
           ,hats.transaction_step_id
           ,'P_POSTAL_CODE'))                     postal_code_meaning_p 
,      xxas_hr_api_transaction_pkg.varchar2_value
         (hat.selected_person_id
         ,hats.api_name
         ,hats.transaction_step_id
         ,'P_COUNTRY_CODE')                      country_code_p
,      xxas_com_vset_pkg.decode_descr_vset_value
         ('800'
         ,'Address Structure'
         ,xxas_hr_api_transaction_pkg.varchar2_value
           (hat.selected_person_id
           ,hats.api_name
           ,hats.transaction_step_id
           ,'P_STYLE')
         ,'COUNTRY'
         ,xxas_hr_api_transaction_pkg.varchar2_value
           (hat.selected_person_id
           ,hats.api_name
           ,hats.transaction_step_id
           ,'P_COUNTRY_CODE'))                   country_code_meaning_p 
,      xxas_hr_api_transaction_pkg.varchar2_value
         (hat.selected_person_id
         ,hats.api_name
         ,hats.transaction_step_id
         ,'P_TELEPHONE_NUMBER1')                 telephone_number1_p
,      xxas_com_vset_pkg.decode_descr_vset_value
         ('800'
         ,'Address Structure'
         ,xxas_hr_api_transaction_pkg.varchar2_value
           (hat.selected_person_id
           ,hats.api_name
           ,hats.transaction_step_id
           ,'P_STYLE')
         ,'TELEPHONE_NUMBER_1'
         ,xxas_hr_api_transaction_pkg.varchar2_value
           (hat.selected_person_id
           ,hats.api_name
           ,hats.transaction_step_id
           ,'P_TELEPHONE_NUMBER1'))              telephone_number1_meaning_p 
,      xxas_hr_api_transaction_pkg.varchar2_value
         (hat.selected_person_id
         ,hats.api_name
         ,hats.transaction_step_id
         ,'P_TELEPHONE_NUMBER2')                 telephone_number2_p
,      xxas_com_vset_pkg.decode_descr_vset_value
         ('800'
         ,'Address Structure'
         ,xxas_hr_api_transaction_pkg.varchar2_value
           (hat.selected_person_id
           ,hats.api_name
           ,hats.transaction_step_id
           ,'P_STYLE')
         ,'TELEPHONE_NUMBER_2'
         ,xxas_hr_api_transaction_pkg.varchar2_value
           (hat.selected_person_id
           ,hats.api_name
           ,hats.transaction_step_id
           ,'P_TELEPHONE_NUMBER2'))              telephone_number2_meaning_p 
,      xxas_hr_api_transaction_pkg.varchar2_value
         (hat.selected_person_id
         ,hats.api_name
         ,hats.transaction_step_id
         ,'P_TELEPHONE_NUMBER3')                 telephone_number3_p
,      xxas_com_vset_pkg.decode_descr_vset_value
         ('800'
         ,'Address Structure'
         ,xxas_hr_api_transaction_pkg.varchar2_value
           (hat.selected_person_id
           ,hats.api_name
           ,hats.transaction_step_id
           ,'P_STYLE')
         ,'TELEPHONE_NUMBER_3'
         ,xxas_hr_api_transaction_pkg.varchar2_value
           (hat.selected_person_id
           ,hats.api_name
           ,hats.transaction_step_id
           ,'P_TELEPHONE_NUMBER3'))              telephone_number3_meaning_p 
,      xxas_hr_api_transaction_pkg.varchar2_value
         (hat.selected_person_id
         ,hats.api_name
         ,hats.transaction_step_id
         ,'P_ADDR_ATTRIBUTE_CATEGORY')           addr_attribute_category_p
,      xxas_hr_api_transaction_pkg.varchar2_value
         (hat.selected_person_id
         ,hats.api_name
         ,hats.transaction_step_id
         ,'P_ADDR_ATTRIBUTE1')                   addr_attribute1_p
,      xxas_com_vset_pkg.decode_descr_vset_value
         ('800'
         ,'PER_ADDRESSES'
         ,xxas_hr_api_transaction_pkg.varchar2_value
           (hat.selected_person_id
           ,hats.api_name
           ,hats.transaction_step_id
           ,'P_ADDR_ATTRIBUTE_CATEGORY')
         ,'ADDR_ATTRIBUTE1'
         ,xxas_hr_api_transaction_pkg.varchar2_value
           (hat.selected_person_id
           ,hats.api_name
           ,hats.transaction_step_id
           ,'P_ADDR_ATTRIBUTE1'))                addr_attribute1_meaning_p 
,      xxas_hr_api_transaction_pkg.varchar2_value
         (hat.selected_person_id
         ,hats.api_name
         ,hats.transaction_step_id
         ,'P_ADDR_ATTRIBUTE2')                   addr_attribute2_p
,      xxas_com_vset_pkg.decode_descr_vset_value
         ('800'
         ,'PER_ADDRESSES'
         ,xxas_hr_api_transaction_pkg.varchar2_value
           (hat.selected_person_id
           ,hats.api_name
           ,hats.transaction_step_id
           ,'P_ADDR_ATTRIBUTE_CATEGORY')
         ,'ADDR_ATTRIBUTE2'
         ,xxas_hr_api_transaction_pkg.varchar2_value
           (hat.selected_person_id
           ,hats.api_name
           ,hats.transaction_step_id
           ,'P_ADDR_ATTRIBUTE2'))                addr_attribute2_meaning_p 
,      xxas_hr_api_transaction_pkg.varchar2_value
         (hat.selected_person_id
         ,hats.api_name
         ,hats.transaction_step_id
         ,'P_ADDR_ATTRIBUTE3')                   addr_attribute3_p
,      xxas_com_vset_pkg.decode_descr_vset_value
         ('800'
         ,'PER_ADDRESSES'
         ,xxas_hr_api_transaction_pkg.varchar2_value
           (hat.selected_person_id
           ,hats.api_name
           ,hats.transaction_step_id
           ,'P_ADDR_ATTRIBUTE_CATEGORY')
         ,'ADDR_ATTRIBUTE3'
         ,xxas_hr_api_transaction_pkg.varchar2_value
           (hat.selected_person_id
           ,hats.api_name
           ,hats.transaction_step_id
           ,'P_ADDR_ATTRIBUTE3'))                addr_attribute3_meaning_p 
,      xxas_hr_api_transaction_pkg.varchar2_value
         (hat.selected_person_id
         ,hats.api_name
         ,hats.transaction_step_id
         ,'P_ADDR_ATTRIBUTE4')                   addr_attribute4_p
,      xxas_com_vset_pkg.decode_descr_vset_value
         ('800'
         ,'PER_ADDRESSES'
         ,xxas_hr_api_transaction_pkg.varchar2_value
           (hat.selected_person_id
           ,hats.api_name
           ,hats.transaction_step_id
           ,'P_ADDR_ATTRIBUTE_CATEGORY')
         ,'ADDR_ATTRIBUTE4'
         ,xxas_hr_api_transaction_pkg.varchar2_value
           (hat.selected_person_id
           ,hats.api_name
           ,hats.transaction_step_id
           ,'P_ADDR_ATTRIBUTE4'))                addr_attribute4_meaning_p 
,      xxas_hr_api_transaction_pkg.varchar2_value
         (hat.selected_person_id
         ,hats.api_name
         ,hats.transaction_step_id
         ,'P_ADDR_ATTRIBUTE5')                   addr_attribute5_p
,      xxas_com_vset_pkg.decode_descr_vset_value
         ('800'
         ,'PER_ADDRESSES'
         ,xxas_hr_api_transaction_pkg.varchar2_value
           (hat.selected_person_id
           ,hats.api_name
           ,hats.transaction_step_id
           ,'P_ADDR_ATTRIBUTE_CATEGORY')
         ,'ADDR_ATTRIBUTE5'
         ,xxas_hr_api_transaction_pkg.varchar2_value
           (hat.selected_person_id
           ,hats.api_name
           ,hats.transaction_step_id
           ,'P_ADDR_ATTRIBUTE5'))                addr_attribute5_meaning_p 
,      xxas_hr_api_transaction_pkg.varchar2_value
         (hat.selected_person_id
         ,hats.api_name
         ,hats.transaction_step_id
         ,'P_ADDR_ATTRIBUTE6')                   addr_attribute6_p
,      xxas_com_vset_pkg.decode_descr_vset_value
         ('800'
         ,'PER_ADDRESSES'
         ,xxas_hr_api_transaction_pkg.varchar2_value
           (hat.selected_person_id
           ,hats.api_name
           ,hats.transaction_step_id
           ,'P_ADDR_ATTRIBUTE_CATEGORY')
         ,'ADDR_ATTRIBUTE6'
         ,xxas_hr_api_transaction_pkg.varchar2_value
           (hat.selected_person_id
           ,hats.api_name
           ,hats.transaction_step_id
           ,'P_ADDR_ATTRIBUTE6'))                addr_attribute6_meaning_p 
,      xxas_hr_api_transaction_pkg.varchar2_value
         (hat.selected_person_id
         ,hats.api_name
         ,hats.transaction_step_id
         ,'P_ADDR_ATTRIBUTE7')                   addr_attribute7_p
,      xxas_com_vset_pkg.decode_descr_vset_value
         ('800'
         ,'PER_ADDRESSES'
         ,xxas_hr_api_transaction_pkg.varchar2_value
           (hat.selected_person_id
           ,hats.api_name
           ,hats.transaction_step_id
           ,'P_ADDR_ATTRIBUTE_CATEGORY')
         ,'ADDR_ATTRIBUTE7'
         ,xxas_hr_api_transaction_pkg.varchar2_value
           (hat.selected_person_id
           ,hats.api_name
           ,hats.transaction_step_id
           ,'P_ADDR_ATTRIBUTE7'))                addr_attribute7_meaning_p 
,      xxas_hr_api_transaction_pkg.varchar2_value
         (hat.selected_person_id
         ,hats.api_name
         ,hats.transaction_step_id
         ,'P_ADDR_ATTRIBUTE8')                   addr_attribute8_p
,      xxas_com_vset_pkg.decode_descr_vset_value
         ('800'
         ,'PER_ADDRESSES'
         ,xxas_hr_api_transaction_pkg.varchar2_value
           (hat.selected_person_id
           ,hats.api_name
           ,hats.transaction_step_id
           ,'P_ADDR_ATTRIBUTE_CATEGORY')
         ,'ADDR_ATTRIBUTE8'
         ,xxas_hr_api_transaction_pkg.varchar2_value
           (hat.selected_person_id
           ,hats.api_name
           ,hats.transaction_step_id
           ,'P_ADDR_ATTRIBUTE8'))                addr_attribute8_meaning_p 
,      xxas_hr_api_transaction_pkg.varchar2_value
         (hat.selected_person_id
         ,hats.api_name
         ,hats.transaction_step_id
         ,'P_ADDR_ATTRIBUTE9')                   addr_attribute9_p
,      xxas_com_vset_pkg.decode_descr_vset_value
         ('800'
         ,'PER_ADDRESSES'
         ,xxas_hr_api_transaction_pkg.varchar2_value
           (hat.selected_person_id
           ,hats.api_name
           ,hats.transaction_step_id
           ,'P_ADDR_ATTRIBUTE_CATEGORY')
         ,'ADDR_ATTRIBUTE9'
         ,xxas_hr_api_transaction_pkg.varchar2_value
           (hat.selected_person_id
           ,hats.api_name
           ,hats.transaction_step_id
           ,'P_ADDR_ATTRIBUTE9'))                addr_attribute9_meaning_p 
,      xxas_hr_api_transaction_pkg.varchar2_value
         (hat.selected_person_id
         ,hats.api_name
         ,hats.transaction_step_id
         ,'P_ADDR_ATTRIBUTE10')                  addr_attribute10_p
,      xxas_com_vset_pkg.decode_descr_vset_value
         ('800'
         ,'PER_ADDRESSES'
         ,xxas_hr_api_transaction_pkg.varchar2_value
           (hat.selected_person_id
           ,hats.api_name
           ,hats.transaction_step_id
           ,'P_ADDR_ATTRIBUTE_CATEGORY')
         ,'ADDR_ATTRIBUTE10'
         ,xxas_hr_api_transaction_pkg.varchar2_value
           (hat.selected_person_id
           ,hats.api_name
           ,hats.transaction_step_id
           ,'P_ADDR_ATTRIBUTE10'))               addr_attribute10_meaning_p 
,      xxas_hr_api_transaction_pkg.varchar2_value
         (hat.selected_person_id
         ,hats.api_name
         ,hats.transaction_step_id
         ,'P_ADDR_ATTRIBUTE11')                  addr_attribute11_p
,      xxas_com_vset_pkg.decode_descr_vset_value
         ('800'
         ,'PER_ADDRESSES'
         ,xxas_hr_api_transaction_pkg.varchar2_value
           (hat.selected_person_id
           ,hats.api_name
           ,hats.transaction_step_id
           ,'P_ADDR_ATTRIBUTE_CATEGORY')
         ,'ADDR_ATTRIBUTE11'
         ,xxas_hr_api_transaction_pkg.varchar2_value
           (hat.selected_person_id
           ,hats.api_name
           ,hats.transaction_step_id
           ,'P_ADDR_ATTRIBUTE11'))               addr_attribute11_meaning_p 
,      xxas_hr_api_transaction_pkg.varchar2_value
         (hat.selected_person_id
         ,hats.api_name
         ,hats.transaction_step_id
         ,'P_ADDR_ATTRIBUTE12')                  addr_attribute12_p
,      xxas_com_vset_pkg.decode_descr_vset_value
         ('800'
         ,'PER_ADDRESSES'
         ,xxas_hr_api_transaction_pkg.varchar2_value
           (hat.selected_person_id
           ,hats.api_name
           ,hats.transaction_step_id
           ,'P_ADDR_ATTRIBUTE_CATEGORY')
         ,'ADDR_ATTRIBUTE12'
         ,xxas_hr_api_transaction_pkg.varchar2_value
           (hat.selected_person_id
           ,hats.api_name
           ,hats.transaction_step_id
           ,'P_ADDR_ATTRIBUTE12'))               addr_attribute12_meaning_p 
,      xxas_hr_api_transaction_pkg.varchar2_value
         (hat.selected_person_id
         ,hats.api_name
         ,hats.transaction_step_id
         ,'P_ADDR_ATTRIBUTE13')                  addr_attribute13_p
,      xxas_com_vset_pkg.decode_descr_vset_value
         ('800'
         ,'PER_ADDRESSES'
         ,xxas_hr_api_transaction_pkg.varchar2_value
           (hat.selected_person_id
           ,hats.api_name
           ,hats.transaction_step_id
           ,'P_ADDR_ATTRIBUTE_CATEGORY')
         ,'ADDR_ATTRIBUTE13'
         ,xxas_hr_api_transaction_pkg.varchar2_value
           (hat.selected_person_id
           ,hats.api_name
           ,hats.transaction_step_id
           ,'P_ADDR_ATTRIBUTE13'))               addr_attribute13_meaning_p 
,      xxas_hr_api_transaction_pkg.varchar2_value
         (hat.selected_person_id
         ,hats.api_name
         ,hats.transaction_step_id
         ,'P_ADDR_ATTRIBUTE14')                  addr_attribute14_p
,      xxas_com_vset_pkg.decode_descr_vset_value
         ('800'
         ,'PER_ADDRESSES'
         ,xxas_hr_api_transaction_pkg.varchar2_value
           (hat.selected_person_id
           ,hats.api_name
           ,hats.transaction_step_id
           ,'P_ADDR_ATTRIBUTE_CATEGORY')
         ,'ADDR_ATTRIBUTE14'
         ,xxas_hr_api_transaction_pkg.varchar2_value
           (hat.selected_person_id
           ,hats.api_name
           ,hats.transaction_step_id
           ,'P_ADDR_ATTRIBUTE14'))               addr_attribute14_meaning_p 
,      xxas_hr_api_transaction_pkg.varchar2_value
         (hat.selected_person_id
         ,hats.api_name
         ,hats.transaction_step_id
         ,'P_ADDR_ATTRIBUTE15')                  addr_attribute15_p
,      xxas_com_vset_pkg.decode_descr_vset_value
         ('800'
         ,'PER_ADDRESSES'
         ,xxas_hr_api_transaction_pkg.varchar2_value
           (hat.selected_person_id
           ,hats.api_name
           ,hats.transaction_step_id
           ,'P_ADDR_ATTRIBUTE_CATEGORY')
         ,'ADDR_ATTRIBUTE15'
         ,xxas_hr_api_transaction_pkg.varchar2_value
           (hat.selected_person_id
           ,hats.api_name
           ,hats.transaction_step_id
           ,'P_ADDR_ATTRIBUTE15'))               addr_attribute15_meaning_p 
,      xxas_hr_api_transaction_pkg.varchar2_value
         (hat.selected_person_id
         ,hats.api_name
         ,hats.transaction_step_id
         ,'P_ADDR_ATTRIBUTE16')                  addr_attribute16_p
,      xxas_com_vset_pkg.decode_descr_vset_value
         ('800'
         ,'PER_ADDRESSES'
         ,xxas_hr_api_transaction_pkg.varchar2_value
           (hat.selected_person_id
           ,hats.api_name
           ,hats.transaction_step_id
           ,'P_ADDR_ATTRIBUTE_CATEGORY')
         ,'ADDR_ATTRIBUTE16'
         ,xxas_hr_api_transaction_pkg.varchar2_value
           (hat.selected_person_id
           ,hats.api_name
           ,hats.transaction_step_id
           ,'P_ADDR_ATTRIBUTE16'))               addr_attribute16_meaning_p 
,      xxas_hr_api_transaction_pkg.varchar2_value
         (hat.selected_person_id
         ,hats.api_name
         ,hats.transaction_step_id
         ,'P_ADDR_ATTRIBUTE17')                  addr_attribute17_p
,      xxas_com_vset_pkg.decode_descr_vset_value
         ('800'
         ,'PER_ADDRESSES'
         ,xxas_hr_api_transaction_pkg.varchar2_value
           (hat.selected_person_id
           ,hats.api_name
           ,hats.transaction_step_id
           ,'P_ADDR_ATTRIBUTE_CATEGORY')
         ,'ADDR_ATTRIBUTE17'
         ,xxas_hr_api_transaction_pkg.varchar2_value
           (hat.selected_person_id
           ,hats.api_name
           ,hats.transaction_step_id
           ,'P_ADDR_ATTRIBUTE17'))               addr_attribute17_meaning_p 
,      xxas_hr_api_transaction_pkg.varchar2_value
         (hat.selected_person_id
         ,hats.api_name
         ,hats.transaction_step_id
         ,'P_ADDR_ATTRIBUTE18')                  addr_attribute18_p
,      xxas_com_vset_pkg.decode_descr_vset_value
         ('800'
         ,'PER_ADDRESSES'
         ,xxas_hr_api_transaction_pkg.varchar2_value
           (hat.selected_person_id
           ,hats.api_name
           ,hats.transaction_step_id
           ,'P_ADDR_ATTRIBUTE_CATEGORY')
         ,'ADDR_ATTRIBUTE18'
         ,xxas_hr_api_transaction_pkg.varchar2_value
           (hat.selected_person_id
           ,hats.api_name
           ,hats.transaction_step_id
           ,'P_ADDR_ATTRIBUTE18'))               addr_attribute18_meaning_p 
,      xxas_hr_api_transaction_pkg.varchar2_value
         (hat.selected_person_id
         ,hats.api_name
         ,hats.transaction_step_id
         ,'P_ADDR_ATTRIBUTE19')                  addr_attribute19_p
,      xxas_com_vset_pkg.decode_descr_vset_value
         ('800'
         ,'PER_ADDRESSES'
         ,xxas_hr_api_transaction_pkg.varchar2_value
           (hat.selected_person_id
           ,hats.api_name
           ,hats.transaction_step_id
           ,'P_ADDR_ATTRIBUTE_CATEGORY')
         ,'ADDR_ATTRIBUTE19'
         ,xxas_hr_api_transaction_pkg.varchar2_value
           (hat.selected_person_id
           ,hats.api_name
           ,hats.transaction_step_id
           ,'P_ADDR_ATTRIBUTE19'))               addr_attribute19_meaning_p 
,      xxas_hr_api_transaction_pkg.varchar2_value
         (hat.selected_person_id
         ,hats.api_name
         ,hats.transaction_step_id
         ,'P_ADDR_ATTRIBUTE20')                  addr_attribute20_p
,      xxas_com_vset_pkg.decode_descr_vset_value
         ('800'
         ,'PER_ADDRESSES'
         ,xxas_hr_api_transaction_pkg.varchar2_value
           (hat.selected_person_id
           ,hats.api_name
           ,hats.transaction_step_id
           ,'P_ADDR_ATTRIBUTE_CATEGORY')
         ,'ADDR_ATTRIBUTE20'
         ,xxas_hr_api_transaction_pkg.varchar2_value
           (hat.selected_person_id
           ,hats.api_name
           ,hats.transaction_step_id
           ,'P_ADDR_ATTRIBUTE20'))               addr_attribute20_meaning_p 
,      xxas_hr_api_transaction_pkg.varchar2_value
         (hat.selected_person_id
         ,hats.api_name
         ,hats.transaction_step_id
         ,'P_ADD_INFORMATION13')                 add_information13_p
,      xxas_com_vset_pkg.decode_descr_vset_value
         ('800'
         ,'Address Structure'
         ,xxas_hr_api_transaction_pkg.varchar2_value
           (hat.selected_person_id
           ,hats.api_name
           ,hats.transaction_step_id
           ,'P_STYLE')
         ,'ADD_INFORMATION13'
         ,xxas_hr_api_transaction_pkg.varchar2_value
           (hat.selected_person_id
           ,hats.api_name
           ,hats.transaction_step_id
           ,'P_ADD_INFORMATION13'))              add_information13_meaning_p
,      xxas_hr_api_transaction_pkg.varchar2_value
         (hat.selected_person_id
         ,hats.api_name
         ,hats.transaction_step_id
         ,'P_ADD_INFORMATION14')                 add_information14_p
,      xxas_com_vset_pkg.decode_descr_vset_value
         ('800'
         ,'Address Structure'
         ,xxas_hr_api_transaction_pkg.varchar2_value
           (hat.selected_person_id
           ,hats.api_name
           ,hats.transaction_step_id
           ,'P_STYLE')
         ,'ADD_INFORMATION14'
         ,xxas_hr_api_transaction_pkg.varchar2_value
           (hat.selected_person_id
           ,hats.api_name
           ,hats.transaction_step_id
           ,'P_ADD_INFORMATION14'))              add_information14_meaning_p
,      xxas_hr_api_transaction_pkg.varchar2_value
         (hat.selected_person_id
         ,hats.api_name
         ,hats.transaction_step_id
         ,'P_ADD_INFORMATION15')                 add_information15_p
,      xxas_com_vset_pkg.decode_descr_vset_value
         ('800'
         ,'Address Structure'
         ,xxas_hr_api_transaction_pkg.varchar2_value
           (hat.selected_person_id
           ,hats.api_name
           ,hats.transaction_step_id
           ,'P_STYLE')
         ,'ADD_INFORMATION15'
         ,xxas_hr_api_transaction_pkg.varchar2_value
           (hat.selected_person_id
           ,hats.api_name
           ,hats.transaction_step_id
           ,'P_ADD_INFORMATION15'))              add_information15_meaning_p
,      xxas_hr_api_transaction_pkg.varchar2_value
         (hat.selected_person_id
         ,hats.api_name
         ,hats.transaction_step_id
         ,'P_ADD_INFORMATION16')                 add_information16_p
,      xxas_com_vset_pkg.decode_descr_vset_value
         ('800'
         ,'Address Structure'
         ,xxas_hr_api_transaction_pkg.varchar2_value
           (hat.selected_person_id
           ,hats.api_name
           ,hats.transaction_step_id
           ,'P_STYLE')
         ,'ADD_INFORMATION16'
         ,xxas_hr_api_transaction_pkg.varchar2_value
           (hat.selected_person_id
           ,hats.api_name
           ,hats.transaction_step_id
           ,'P_ADD_INFORMATION16'))              add_information16_meaning_p
,      xxas_hr_api_transaction_pkg.varchar2_value
         (hat.selected_person_id
         ,hats.api_name
         ,hats.transaction_step_id
         ,'P_ADD_INFORMATION17')                 add_information17_p
,      xxas_com_vset_pkg.decode_descr_vset_value
         ('800'
         ,'Address Structure'
         ,xxas_hr_api_transaction_pkg.varchar2_value
           (hat.selected_person_id
           ,hats.api_name
           ,hats.transaction_step_id
           ,'P_STYLE')
         ,'ADD_INFORMATION17'
         ,xxas_hr_api_transaction_pkg.varchar2_value
           (hat.selected_person_id
           ,hats.api_name
           ,hats.transaction_step_id
           ,'P_ADD_INFORMATION17'))              add_information17_meaning_p
,      xxas_hr_api_transaction_pkg.varchar2_value
         (hat.selected_person_id
         ,hats.api_name
         ,hats.transaction_step_id
         ,'P_ADD_INFORMATION18')                 add_information18_p
,      xxas_com_vset_pkg.decode_descr_vset_value
         ('800'
         ,'Address Structure'
         ,xxas_hr_api_transaction_pkg.varchar2_value
           (hat.selected_person_id
           ,hats.api_name
           ,hats.transaction_step_id
           ,'P_STYLE')
         ,'ADD_INFORMATION18'
         ,xxas_hr_api_transaction_pkg.varchar2_value
           (hat.selected_person_id
           ,hats.api_name
           ,hats.transaction_step_id
           ,'P_ADD_INFORMATION18'))              add_information18_meaning_p
,      xxas_hr_api_transaction_pkg.varchar2_value
         (hat.selected_person_id
         ,hats.api_name
         ,hats.transaction_step_id
         ,'P_ADD_INFORMATION19')                 add_information19_p
,      xxas_com_vset_pkg.decode_descr_vset_value
         ('800'
         ,'Address Structure'
         ,xxas_hr_api_transaction_pkg.varchar2_value
           (hat.selected_person_id
           ,hats.api_name
           ,hats.transaction_step_id
           ,'P_STYLE')
         ,'ADD_INFORMATION19'
         ,xxas_hr_api_transaction_pkg.varchar2_value
           (hat.selected_person_id
           ,hats.api_name
           ,hats.transaction_step_id
           ,'P_ADD_INFORMATION19'))              add_information19_meaning_p
,      xxas_hr_api_transaction_pkg.varchar2_value
         (hat.selected_person_id
         ,hats.api_name
         ,hats.transaction_step_id
         ,'P_ADD_INFORMATION20')                 add_information20_p
,      xxas_com_vset_pkg.decode_descr_vset_value
         ('800'
         ,'Address Structure'
         ,xxas_hr_api_transaction_pkg.varchar2_value
           (hat.selected_person_id
           ,hats.api_name
           ,hats.transaction_step_id
           ,'P_STYLE')
         ,'ADD_INFORMATION20'
         ,xxas_hr_api_transaction_pkg.varchar2_value
           (hat.selected_person_id
           ,hats.api_name
           ,hats.transaction_step_id
           ,'P_ADD_INFORMATION20'))              add_information20_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   aid     
,      hr_api_transaction_values   pid     
WHERE  hat.transaction_id       = hats.transaction_id
AND    hat.status          NOT IN ('D', 'N', 'W', 'ACTIVE', 'AC')
AND    hats.api_name            = 'HR_PROCESS_ADDRESS_SS.PROCESS_API'
AND    hats.transaction_step_id = aid.transaction_step_id
AND    aid.name                 = 'P_ADDRESS_ID'
AND    aid.number_value        IS  NULL
AND    hats.transaction_step_id  = pid.transaction_step_id
AND    pid.name                  = 'P_CONTACT_OR_PERSON'
AND    pid.varchar2_value        = 'PERSON'
UNION ALL
SELECT CASE WHEN cid.number_value = -1            
            THEN NULL
            ELSE cid.number_value 
       END                                       person_id
,      CASE WHEN cid.number_value = -1            
            THEN NULL
            ELSE cid.number_value 
       END                                       per_person_id
,      NULL                                      address_id
,      NULL                                      object_version_number
,      NULL                                      date_from
,      NULL                                      date_to
,      NULL                                      primary_flag
,      NULL                                      style
,      NULL                                      address_type
,      NULL                                      address_type_meaning                                     
,      NULL                                      address_line1
,      NULL                                      address_line1_meaning 
,      NULL                                      address_line2
,      NULL                                      address_line2_meaning 
,      NULL                                      address_line3
,      NULL                                      address_line3_meaning 
,      NULL                                      town_or_city
,      NULL                                      town_or_city_meaning 
,      NULL                                      region_1
,      NULL                                      region_1_meaning 
,      NULL                                      region_2
,      NULL                                      region_2_meaning 
,      NULL                                      region_3
,      NULL                                      region_3_meaning 
,      NULL                                      postal_code
,      NULL                                      postal_code_meaning 
,      NULL                                      country
,      NULL                                      country_meaning 
,      NULL                                      telephone_number_1
,      NULL                                      telephone_number_1_meaning 
,      NULL                                      telephone_number_2
,      NULL                                      telephone_number_2_meaning 
,      NULL                                      telephone_number_3
,      NULL                                      telephone_number_3_meaning
,      NULL                                      latitude
,      NULL                                      longtitude 
,      NULL                                      addr_attribute_category
,      NULL                                      addr_attribute1
,      NULL                                      addr_attribute1_meaning 
,      NULL                                      addr_attribute2
,      NULL                                      addr_attribute2_meaning 
,      NULL                                      addr_attribute3
,      NULL                                      addr_attribute3_meaning 
,      NULL                                      addr_attribute4
,      NULL                                      addr_attribute4_meaning 
,      NULL                                      addr_attribute5
,      NULL                                      addr_attribute5_meaning 
,      NULL                                      addr_attribute6
,      NULL                                      addr_attribute6_meaning 
,      NULL                                      addr_attribute7
,      NULL                                      addr_attribute7_meaning 
,      NULL                                      addr_attribute8
,      NULL                                      addr_attribute8_meaning 
,      NULL                                      addr_attribute9
,      NULL                                      addr_attribute9_meaning 
,      NULL                                      addr_attribute10
,      NULL                                      addr_attribute10_meaning 
,      NULL                                      addr_attribute11
,      NULL                                      addr_attribute11_meaning 
,      NULL                                      addr_attribute12
,      NULL                                      addr_attribute12_meaning 
,      NULL                                      addr_attribute13
,      NULL                                      addr_attribute13_meaning 
,      NULL                                      addr_attribute14
,      NULL                                      addr_attribute14_meaning 
,      NULL                                      addr_attribute15
,      NULL                                      addr_attribute15_meaning 
,      NULL                                      addr_attribute16
,      NULL                                      addr_attribute16_meaning 
,      NULL                                      addr_attribute17
,      NULL                                      addr_attribute17_meaning 
,      NULL                                      addr_attribute18
,      NULL                                      addr_attribute18_meaning 
,      NULL                                      addr_attribute19
,      NULL                                      addr_attribute19_meaning 
,      NULL                                      addr_attribute20
,      NULL                                      addr_attribute20_meaning 
,      NULL                                      add_information13
,      NULL                                      add_information13_meaning
,      NULL                                      add_information14
,      NULL                                      add_information14_meaning
,      NULL                                      add_information15
,      NULL                                      add_information15_meaning
,      NULL                                      add_information16
,      NULL                                      add_information16_meaning
,      NULL                                      add_information17
,      NULL                                      add_information17_meaning
,      NULL                                      add_information18
,      NULL                                      add_information18_meaning
,      NULL                                      add_information19
,      NULL                                      add_information19_meaning
,      NULL                                      add_information20
,      NULL                                      add_information20_meaning
,      hat.transaction_id
,      hats.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_ADDRESS_ID')                        address_id_p
,      xxas_hr_api_transaction_pkg.number_value
         (hat.selected_person_id
         ,hats.api_name
         ,hats.transaction_step_id
         ,'P_OBJECT_VERSION_NUMBER')             object_version_number_p
,      xxas_hr_api_transaction_pkg.date_value
         (hat.selected_person_id
         ,hats.api_name
         ,hats.transaction_step_id
         ,'P_DATE_FROM')                         date_from_p
,      xxas_hr_api_transaction_pkg.date_value
         (hat.selected_person_id
         ,hats.api_name
         ,hats.transaction_step_id
         ,'P_DATE_TO')                           date_to_p
,      xxas_hr_api_transaction_pkg.varchar2_value
         (hat.selected_person_id
         ,hats.api_name
         ,hats.transaction_step_id
         ,'P_PRIMARY_FLAG')                      primary_flag_p
,      xxas_hr_api_transaction_pkg.varchar2_value
         (hat.selected_person_id
         ,hats.api_name
         ,hats.transaction_step_id
         ,'P_STYLE')                             style_p
,      xxas_hr_api_transaction_pkg.varchar2_value
         (hat.selected_person_id
         ,hats.api_name
         ,hats.transaction_step_id
         ,'P_ADDRESS_TYPE_CODE')                 address_type_p
,      xxas_util_pkg.decode_lookup
         ('ADDRESS_TYPE'
         ,xxas_hr_api_transaction_pkg.varchar2_value
           (hat.selected_person_id
           ,hats.api_name
           ,hats.transaction_step_id
           ,'P_ADDRESS_TYPE_CODE'))              address_type_meaning_p                                                        
,      xxas_hr_api_transaction_pkg.varchar2_value
         (hat.selected_person_id
         ,hats.api_name
         ,hats.transaction_step_id
         ,'P_ADDRESS_LINE1')                     address_line1_p
,      xxas_com_vset_pkg.decode_descr_vset_value
         ('800'
         ,'Address Structure'
         ,xxas_hr_api_transaction_pkg.varchar2_value
           (hat.selected_person_id
           ,hats.api_name
           ,hats.transaction_step_id
           ,'P_STYLE')
         ,'ADDRESS_LINE1'
         ,xxas_hr_api_transaction_pkg.varchar2_value
           (hat.selected_person_id
           ,hats.api_name
           ,hats.transaction_step_id
           ,'P_ADDRESS_LINE1'))                  address_line1_meaning_p 
,      xxas_hr_api_transaction_pkg.varchar2_value
         (hat.selected_person_id
         ,hats.api_name
         ,hats.transaction_step_id
         ,'P_ADDRESS_LINE2')                     address_line2_p
,      xxas_com_vset_pkg.decode_descr_vset_value
         ('800'
         ,'Address Structure'
         ,xxas_hr_api_transaction_pkg.varchar2_value
           (hat.selected_person_id
           ,hats.api_name
           ,hats.transaction_step_id
           ,'P_STYLE')
         ,'ADDRESS_LINE2'
         ,xxas_hr_api_transaction_pkg.varchar2_value
           (hat.selected_person_id
           ,hats.api_name
           ,hats.transaction_step_id
           ,'P_ADDRESS_LINE2'))                  address_line2_meaning_p 
,      xxas_hr_api_transaction_pkg.varchar2_value
         (hat.selected_person_id
         ,hats.api_name
         ,hats.transaction_step_id
         ,'P_ADDRESS_LINE3')                     address_line3_p
,      xxas_com_vset_pkg.decode_descr_vset_value
         ('800'
         ,'Address Structure'
         ,xxas_hr_api_transaction_pkg.varchar2_value
           (hat.selected_person_id
           ,hats.api_name
           ,hats.transaction_step_id
           ,'P_STYLE')
         ,'ADDRESS_LINE3'
         ,xxas_hr_api_transaction_pkg.varchar2_value
           (hat.selected_person_id
           ,hats.api_name
           ,hats.transaction_step_id
           ,'P_ADDRESS_LINE3'))                  address_line3_meaning_p 
              ,   xxas_hr_api_transaction_pkg.varchar2_value
         (hat.selected_person_id
         ,hats.api_name
         ,hats.transaction_step_id
         ,'P_CITY')     town_or_city_p
	    ,xxas_com_vset_pkg.decode_descr_vset_value
         ('800'
         ,'Address Structure'
         ,xxas_hr_api_transaction_pkg.varchar2_value
           (hat.selected_person_id
           ,hats.api_name
           ,hats.transaction_step_id
           ,'P_STYLE')
         ,'TOWN_OR_CITY'
         ,xxas_hr_api_transaction_pkg.varchar2_value
           (hat.selected_person_id
           ,hats.api_name
           ,hats.transaction_step_id
           ,'P_CITY'))  town_or_city_meaning_p
,      xxas_hr_api_transaction_pkg.varchar2_value
         (hat.selected_person_id
         ,hats.api_name
         ,hats.transaction_step_id
         ,'P_REGION1')                           region_1_p
,      xxas_com_vset_pkg.decode_descr_vset_value
         ('800'
         ,'Address Structure'
         ,xxas_hr_api_transaction_pkg.varchar2_value
           (hat.selected_person_id
           ,hats.api_name
           ,hats.transaction_step_id
           ,'P_STYLE')
         ,'REGION_1'
         ,xxas_hr_api_transaction_pkg.varchar2_value
           (hat.selected_person_id
           ,hats.api_name
           ,hats.transaction_step_id
           ,'P_REGION1'))                        region_1_meaning_p 
,      xxas_hr_api_transaction_pkg.varchar2_value
         (hat.selected_person_id
         ,hats.api_name
         ,hats.transaction_step_id
         ,'P_REGION2')                           region_2_p
,      xxas_com_vset_pkg.decode_descr_vset_value
         ('800'
         ,'Address Structure'
         ,xxas_hr_api_transaction_pkg.varchar2_value
           (hat.selected_person_id
           ,hats.api_name
           ,hats.transaction_step_id
           ,'P_STYLE')
         ,'REGION_2'
         ,xxas_hr_api_transaction_pkg.varchar2_value
           (hat.selected_person_id
           ,hats.api_name
           ,hats.transaction_step_id
           ,'P_REGION2'))                        region_2_meaning_p 
,      xxas_hr_api_transaction_pkg.varchar2_value
         (hat.selected_person_id
         ,hats.api_name
         ,hats.transaction_step_id
         ,'P_REGION3')                           region_3_p
,      xxas_com_vset_pkg.decode_descr_vset_value
         ('800'
         ,'Address Structure'
         ,xxas_hr_api_transaction_pkg.varchar2_value
           (hat.selected_person_id
           ,hats.api_name
           ,hats.transaction_step_id
           ,'P_STYLE')
         ,'REGION_3'
         ,xxas_hr_api_transaction_pkg.varchar2_value
           (hat.selected_person_id
           ,hats.api_name
           ,hats.transaction_step_id
           ,'P_REGION3'))                        region_3_meaning_p 
,      xxas_hr_api_transaction_pkg.varchar2_value
         (hat.selected_person_id
         ,hats.api_name
         ,hats.transaction_step_id
         ,'P_POSTAL_CODE')                       postal_code_p
,      xxas_com_vset_pkg.decode_descr_vset_value
         ('800'
         ,'Address Structure'
         ,xxas_hr_api_transaction_pkg.varchar2_value
           (hat.selected_person_id
           ,hats.api_name
           ,hats.transaction_step_id
           ,'P_STYLE')
         ,'POSTAL_CODE'
         ,xxas_hr_api_transaction_pkg.varchar2_value
           (hat.selected_person_id
           ,hats.api_name
           ,hats.transaction_step_id
           ,'P_POSTAL_CODE'))                     postal_code_meaning_p 
,      xxas_hr_api_transaction_pkg.varchar2_value
         (hat.selected_person_id
         ,hats.api_name
         ,hats.transaction_step_id
         ,'P_COUNTRY_CODE')                      country_code_p
,      xxas_com_vset_pkg.decode_descr_vset_value
         ('800'
         ,'Address Structure'
         ,xxas_hr_api_transaction_pkg.varchar2_value
           (hat.selected_person_id
           ,hats.api_name
           ,hats.transaction_step_id
           ,'P_STYLE')
         ,'COUNTRY'
         ,xxas_hr_api_transaction_pkg.varchar2_value
           (hat.selected_person_id
           ,hats.api_name
           ,hats.transaction_step_id
           ,'P_COUNTRY_CODE'))                   country_code_meaning_p 
,      xxas_hr_api_transaction_pkg.varchar2_value
         (hat.selected_person_id
         ,hats.api_name
         ,hats.transaction_step_id
         ,'P_TELEPHONE_NUMBER1')                 telephone_number1_p
,      xxas_com_vset_pkg.decode_descr_vset_value
         ('800'
         ,'Address Structure'
         ,xxas_hr_api_transaction_pkg.varchar2_value
           (hat.selected_person_id
           ,hats.api_name
           ,hats.transaction_step_id
           ,'P_STYLE')
         ,'TELEPHONE_NUMBER_1'
         ,xxas_hr_api_transaction_pkg.varchar2_value
           (hat.selected_person_id
           ,hats.api_name
           ,hats.transaction_step_id
           ,'P_TELEPHONE_NUMBER1'))              telephone_number1_meaning_p 
,      xxas_hr_api_transaction_pkg.varchar2_value
         (hat.selected_person_id
         ,hats.api_name
         ,hats.transaction_step_id
         ,'P_TELEPHONE_NUMBER2')                 telephone_number2_p
,      xxas_com_vset_pkg.decode_descr_vset_value
         ('800'
         ,'Address Structure'
         ,xxas_hr_api_transaction_pkg.varchar2_value
           (hat.selected_person_id
           ,hats.api_name
           ,hats.transaction_step_id
           ,'P_STYLE')
         ,'TELEPHONE_NUMBER_2'
         ,xxas_hr_api_transaction_pkg.varchar2_value
           (hat.selected_person_id
           ,hats.api_name
           ,hats.transaction_step_id
           ,'P_TELEPHONE_NUMBER2'))              telephone_number2_meaning_p 
,      xxas_hr_api_transaction_pkg.varchar2_value
         (hat.selected_person_id
         ,hats.api_name
         ,hats.transaction_step_id
         ,'P_TELEPHONE_NUMBER3')                 telephone_number3_p
,      xxas_com_vset_pkg.decode_descr_vset_value
         ('800'
         ,'Address Structure'
         ,xxas_hr_api_transaction_pkg.varchar2_value
           (hat.selected_person_id
           ,hats.api_name
           ,hats.transaction_step_id
           ,'P_STYLE')
         ,'TELEPHONE_NUMBER_3'
         ,xxas_hr_api_transaction_pkg.varchar2_value
           (hat.selected_person_id
           ,hats.api_name
           ,hats.transaction_step_id
           ,'P_TELEPHONE_NUMBER3'))              telephone_number3_meaning_p 
,      xxas_hr_api_transaction_pkg.varchar2_value
         (hat.selected_person_id
         ,hats.api_name
         ,hats.transaction_step_id
         ,'P_ADDR_ATTRIBUTE_CATEGORY')           addr_attribute_category_p
,      xxas_hr_api_transaction_pkg.varchar2_value
         (hat.selected_person_id
         ,hats.api_name
         ,hats.transaction_step_id
         ,'P_ADDR_ATTRIBUTE1')                   addr_attribute1_p
,      xxas_com_vset_pkg.decode_descr_vset_value
         ('800'
         ,'PER_ADDRESSES'
         ,xxas_hr_api_transaction_pkg.varchar2_value
           (hat.selected_person_id
           ,hats.api_name
           ,hats.transaction_step_id
           ,'P_ADDR_ATTRIBUTE_CATEGORY')
         ,'ADDR_ATTRIBUTE1'
         ,xxas_hr_api_transaction_pkg.varchar2_value
           (hat.selected_person_id
           ,hats.api_name
           ,hats.transaction_step_id
           ,'P_ADDR_ATTRIBUTE1'))                addr_attribute1_meaning_p 
,      xxas_hr_api_transaction_pkg.varchar2_value
         (hat.selected_person_id
         ,hats.api_name
         ,hats.transaction_step_id
         ,'P_ADDR_ATTRIBUTE2')                   addr_attribute2_p
,      xxas_com_vset_pkg.decode_descr_vset_value
         ('800'
         ,'PER_ADDRESSES'
         ,xxas_hr_api_transaction_pkg.varchar2_value
           (hat.selected_person_id
           ,hats.api_name
           ,hats.transaction_step_id
           ,'P_ADDR_ATTRIBUTE_CATEGORY')
         ,'ADDR_ATTRIBUTE2'
         ,xxas_hr_api_transaction_pkg.varchar2_value
           (hat.selected_person_id
           ,hats.api_name
           ,hats.transaction_step_id
           ,'P_ADDR_ATTRIBUTE2'))                addr_attribute2_meaning_p 
,      xxas_hr_api_transaction_pkg.varchar2_value
         (hat.selected_person_id
         ,hats.api_name
         ,hats.transaction_step_id
         ,'P_ADDR_ATTRIBUTE3')                   addr_attribute3_p
,      xxas_com_vset_pkg.decode_descr_vset_value
         ('800'
         ,'PER_ADDRESSES'
         ,xxas_hr_api_transaction_pkg.varchar2_value
           (hat.selected_person_id
           ,hats.api_name
           ,hats.transaction_step_id
           ,'P_ADDR_ATTRIBUTE_CATEGORY')
         ,'ADDR_ATTRIBUTE3'
         ,xxas_hr_api_transaction_pkg.varchar2_value
           (hat.selected_person_id
           ,hats.api_name
           ,hats.transaction_step_id
           ,'P_ADDR_ATTRIBUTE3'))                addr_attribute3_meaning_p 
,      xxas_hr_api_transaction_pkg.varchar2_value
         (hat.selected_person_id
         ,hats.api_name
         ,hats.transaction_step_id
         ,'P_ADDR_ATTRIBUTE4')                   addr_attribute4_p
,      xxas_com_vset_pkg.decode_descr_vset_value
         ('800'
         ,'PER_ADDRESSES'
         ,xxas_hr_api_transaction_pkg.varchar2_value
           (hat.selected_person_id
           ,hats.api_name
           ,hats.transaction_step_id
           ,'P_ADDR_ATTRIBUTE_CATEGORY')
         ,'ADDR_ATTRIBUTE4'
         ,xxas_hr_api_transaction_pkg.varchar2_value
           (hat.selected_person_id
           ,hats.api_name
           ,hats.transaction_step_id
           ,'P_ADDR_ATTRIBUTE4'))                addr_attribute4_meaning_p 
,      xxas_hr_api_transaction_pkg.varchar2_value
         (hat.selected_person_id
         ,hats.api_name
         ,hats.transaction_step_id
         ,'P_ADDR_ATTRIBUTE5')                   addr_attribute5_p
,      xxas_com_vset_pkg.decode_descr_vset_value
         ('800'
         ,'PER_ADDRESSES'
         ,xxas_hr_api_transaction_pkg.varchar2_value
           (hat.selected_person_id
           ,hats.api_name
           ,hats.transaction_step_id
           ,'P_ADDR_ATTRIBUTE_CATEGORY')
         ,'ADDR_ATTRIBUTE5'
         ,xxas_hr_api_transaction_pkg.varchar2_value
           (hat.selected_person_id
           ,hats.api_name
           ,hats.transaction_step_id
           ,'P_ADDR_ATTRIBUTE5'))                addr_attribute5_meaning_p 
,      xxas_hr_api_transaction_pkg.varchar2_value
         (hat.selected_person_id
         ,hats.api_name
         ,hats.transaction_step_id
         ,'P_ADDR_ATTRIBUTE6')                   addr_attribute6_p
,      xxas_com_vset_pkg.decode_descr_vset_value
         ('800'
         ,'PER_ADDRESSES'
         ,xxas_hr_api_transaction_pkg.varchar2_value
           (hat.selected_person_id
           ,hats.api_name
           ,hats.transaction_step_id
           ,'P_ADDR_ATTRIBUTE_CATEGORY')
         ,'ADDR_ATTRIBUTE6'
         ,xxas_hr_api_transaction_pkg.varchar2_value
           (hat.selected_person_id
           ,hats.api_name
           ,hats.transaction_step_id
           ,'P_ADDR_ATTRIBUTE6'))                addr_attribute6_meaning_p 
,      xxas_hr_api_transaction_pkg.varchar2_value
         (hat.selected_person_id
         ,hats.api_name
         ,hats.transaction_step_id
         ,'P_ADDR_ATTRIBUTE7')                   addr_attribute7_p
,      xxas_com_vset_pkg.decode_descr_vset_value
         ('800'
         ,'PER_ADDRESSES'
         ,xxas_hr_api_transaction_pkg.varchar2_value
           (hat.selected_person_id
           ,hats.api_name
           ,hats.transaction_step_id
           ,'P_ADDR_ATTRIBUTE_CATEGORY')
         ,'ADDR_ATTRIBUTE7'
         ,xxas_hr_api_transaction_pkg.varchar2_value
           (hat.selected_person_id
           ,hats.api_name
           ,hats.transaction_step_id
           ,'P_ADDR_ATTRIBUTE7'))                addr_attribute7_meaning_p 
,      xxas_hr_api_transaction_pkg.varchar2_value
         (hat.selected_person_id
         ,hats.api_name
         ,hats.transaction_step_id
         ,'P_ADDR_ATTRIBUTE8')                   addr_attribute8_p
,      xxas_com_vset_pkg.decode_descr_vset_value
         ('800'
         ,'PER_ADDRESSES'
         ,xxas_hr_api_transaction_pkg.varchar2_value
           (hat.selected_person_id
           ,hats.api_name
           ,hats.transaction_step_id
           ,'P_ADDR_ATTRIBUTE_CATEGORY')
         ,'ADDR_ATTRIBUTE8'
         ,xxas_hr_api_transaction_pkg.varchar2_value
           (hat.selected_person_id
           ,hats.api_name
           ,hats.transaction_step_id
           ,'P_ADDR_ATTRIBUTE8'))                addr_attribute8_meaning_p 
,      xxas_hr_api_transaction_pkg.varchar2_value
         (hat.selected_person_id
         ,hats.api_name
         ,hats.transaction_step_id
         ,'P_ADDR_ATTRIBUTE9')                   addr_attribute9_p
,      xxas_com_vset_pkg.decode_descr_vset_value
         ('800'
         ,'PER_ADDRESSES'
         ,xxas_hr_api_transaction_pkg.varchar2_value
           (hat.selected_person_id
           ,hats.api_name
           ,hats.transaction_step_id
           ,'P_ADDR_ATTRIBUTE_CATEGORY')
         ,'ADDR_ATTRIBUTE9'
         ,xxas_hr_api_transaction_pkg.varchar2_value
           (hat.selected_person_id
           ,hats.api_name
           ,hats.transaction_step_id
           ,'P_ADDR_ATTRIBUTE9'))                addr_attribute9_meaning_p 
,      xxas_hr_api_transaction_pkg.varchar2_value
         (hat.selected_person_id
         ,hats.api_name
         ,hats.transaction_step_id
         ,'P_ADDR_ATTRIBUTE10')                  addr_attribute10_p
,      xxas_com_vset_pkg.decode_descr_vset_value
         ('800'
         ,'PER_ADDRESSES'
         ,xxas_hr_api_transaction_pkg.varchar2_value
           (hat.selected_person_id
           ,hats.api_name
           ,hats.transaction_step_id
           ,'P_ADDR_ATTRIBUTE_CATEGORY')
         ,'ADDR_ATTRIBUTE10'
         ,xxas_hr_api_transaction_pkg.varchar2_value
           (hat.selected_person_id
           ,hats.api_name
           ,hats.transaction_step_id
           ,'P_ADDR_ATTRIBUTE10'))               addr_attribute10_meaning_p 
,      xxas_hr_api_transaction_pkg.varchar2_value
         (hat.selected_person_id
         ,hats.api_name
         ,hats.transaction_step_id
         ,'P_ADDR_ATTRIBUTE11')                  addr_attribute11_p
,      xxas_com_vset_pkg.decode_descr_vset_value
         ('800'
         ,'PER_ADDRESSES'
         ,xxas_hr_api_transaction_pkg.varchar2_value
           (hat.selected_person_id
           ,hats.api_name
           ,hats.transaction_step_id
           ,'P_ADDR_ATTRIBUTE_CATEGORY')
         ,'ADDR_ATTRIBUTE11'
         ,xxas_hr_api_transaction_pkg.varchar2_value
           (hat.selected_person_id
           ,hats.api_name
           ,hats.transaction_step_id
           ,'P_ADDR_ATTRIBUTE11'))               addr_attribute11_meaning_p 
,      xxas_hr_api_transaction_pkg.varchar2_value
         (hat.selected_person_id
         ,hats.api_name
         ,hats.transaction_step_id
         ,'P_ADDR_ATTRIBUTE12')                  addr_attribute12_p
,      xxas_com_vset_pkg.decode_descr_vset_value
         ('800'
         ,'PER_ADDRESSES'
         ,xxas_hr_api_transaction_pkg.varchar2_value
           (hat.selected_person_id
           ,hats.api_name
           ,hats.transaction_step_id
           ,'P_ADDR_ATTRIBUTE_CATEGORY')
         ,'ADDR_ATTRIBUTE12'
         ,xxas_hr_api_transaction_pkg.varchar2_value
           (hat.selected_person_id
           ,hats.api_name
           ,hats.transaction_step_id
           ,'P_ADDR_ATTRIBUTE12'))               addr_attribute12_meaning_p 
,      xxas_hr_api_transaction_pkg.varchar2_value
         (hat.selected_person_id
         ,hats.api_name
         ,hats.transaction_step_id
         ,'P_ADDR_ATTRIBUTE13')                  addr_attribute13_p
,      xxas_com_vset_pkg.decode_descr_vset_value
         ('800'
         ,'PER_ADDRESSES'
         ,xxas_hr_api_transaction_pkg.varchar2_value
           (hat.selected_person_id
           ,hats.api_name
           ,hats.transaction_step_id
           ,'P_ADDR_ATTRIBUTE_CATEGORY')
         ,'ADDR_ATTRIBUTE13'
         ,xxas_hr_api_transaction_pkg.varchar2_value
           (hat.selected_person_id
           ,hats.api_name
           ,hats.transaction_step_id
           ,'P_ADDR_ATTRIBUTE13'))               addr_attribute13_meaning_p 
,      xxas_hr_api_transaction_pkg.varchar2_value
         (hat.selected_person_id
         ,hats.api_name
         ,hats.transaction_step_id
         ,'P_ADDR_ATTRIBUTE14')                  addr_attribute14_p
,      xxas_com_vset_pkg.decode_descr_vset_value
         ('800'
         ,'PER_ADDRESSES'
         ,xxas_hr_api_transaction_pkg.varchar2_value
           (hat.selected_person_id
           ,hats.api_name
           ,hats.transaction_step_id
           ,'P_ADDR_ATTRIBUTE_CATEGORY')
         ,'ADDR_ATTRIBUTE14'
         ,xxas_hr_api_transaction_pkg.varchar2_value
           (hat.selected_person_id
           ,hats.api_name
           ,hats.transaction_step_id
           ,'P_ADDR_ATTRIBUTE14'))               addr_attribute14_meaning_p 
,      xxas_hr_api_transaction_pkg.varchar2_value
         (hat.selected_person_id
         ,hats.api_name
         ,hats.transaction_step_id
         ,'P_ADDR_ATTRIBUTE15')                  addr_attribute15_p
,      xxas_com_vset_pkg.decode_descr_vset_value
         ('800'
         ,'PER_ADDRESSES'
         ,xxas_hr_api_transaction_pkg.varchar2_value
           (hat.selected_person_id
           ,hats.api_name
           ,hats.transaction_step_id
           ,'P_ADDR_ATTRIBUTE_CATEGORY')
         ,'ADDR_ATTRIBUTE15'
         ,xxas_hr_api_transaction_pkg.varchar2_value
           (hat.selected_person_id
           ,hats.api_name
           ,hats.transaction_step_id
           ,'P_ADDR_ATTRIBUTE15'))               addr_attribute15_meaning_p 
,      xxas_hr_api_transaction_pkg.varchar2_value
         (hat.selected_person_id
         ,hats.api_name
         ,hats.transaction_step_id
         ,'P_ADDR_ATTRIBUTE16')                  addr_attribute16_p
,      xxas_com_vset_pkg.decode_descr_vset_value
         ('800'
         ,'PER_ADDRESSES'
         ,xxas_hr_api_transaction_pkg.varchar2_value
           (hat.selected_person_id
           ,hats.api_name
           ,hats.transaction_step_id
           ,'P_ADDR_ATTRIBUTE_CATEGORY')
         ,'ADDR_ATTRIBUTE16'
         ,xxas_hr_api_transaction_pkg.varchar2_value
           (hat.selected_person_id
           ,hats.api_name
           ,hats.transaction_step_id
           ,'P_ADDR_ATTRIBUTE16'))               addr_attribute16_meaning_p 
,      xxas_hr_api_transaction_pkg.varchar2_value
         (hat.selected_person_id
         ,hats.api_name
         ,hats.transaction_step_id
         ,'P_ADDR_ATTRIBUTE17')                  addr_attribute17_p
,      xxas_com_vset_pkg.decode_descr_vset_value
         ('800'
         ,'PER_ADDRESSES'
         ,xxas_hr_api_transaction_pkg.varchar2_value
           (hat.selected_person_id
           ,hats.api_name
           ,hats.transaction_step_id
           ,'P_ADDR_ATTRIBUTE_CATEGORY')
         ,'ADDR_ATTRIBUTE17'
         ,xxas_hr_api_transaction_pkg.varchar2_value
           (hat.selected_person_id
           ,hats.api_name
           ,hats.transaction_step_id
           ,'P_ADDR_ATTRIBUTE17'))               addr_attribute17_meaning_p 
,      xxas_hr_api_transaction_pkg.varchar2_value
         (hat.selected_person_id
         ,hats.api_name
         ,hats.transaction_step_id
         ,'P_ADDR_ATTRIBUTE18')                  addr_attribute18_p
,      xxas_com_vset_pkg.decode_descr_vset_value
         ('800'
         ,'PER_ADDRESSES'
         ,xxas_hr_api_transaction_pkg.varchar2_value
           (hat.selected_person_id
           ,hats.api_name
           ,hats.transaction_step_id
           ,'P_ADDR_ATTRIBUTE_CATEGORY')
         ,'ADDR_ATTRIBUTE18'
         ,xxas_hr_api_transaction_pkg.varchar2_value
           (hat.selected_person_id
           ,hats.api_name
           ,hats.transaction_step_id
           ,'P_ADDR_ATTRIBUTE18'))               addr_attribute18_meaning_p 
,      xxas_hr_api_transaction_pkg.varchar2_value
         (hat.selected_person_id
         ,hats.api_name
         ,hats.transaction_step_id
         ,'P_ADDR_ATTRIBUTE19')                  addr_attribute19_p
,      xxas_com_vset_pkg.decode_descr_vset_value
         ('800'
         ,'PER_ADDRESSES'
         ,xxas_hr_api_transaction_pkg.varchar2_value
           (hat.selected_person_id
           ,hats.api_name
           ,hats.transaction_step_id
           ,'P_ADDR_ATTRIBUTE_CATEGORY')
         ,'ADDR_ATTRIBUTE19'
         ,xxas_hr_api_transaction_pkg.varchar2_value
           (hat.selected_person_id
           ,hats.api_name
           ,hats.transaction_step_id
           ,'P_ADDR_ATTRIBUTE19'))               addr_attribute19_meaning_p 
,      xxas_hr_api_transaction_pkg.varchar2_value
         (hat.selected_person_id
         ,hats.api_name
         ,hats.transaction_step_id
         ,'P_ADDR_ATTRIBUTE20')                  addr_attribute20_p
,      xxas_com_vset_pkg.decode_descr_vset_value
         ('800'
         ,'PER_ADDRESSES'
         ,xxas_hr_api_transaction_pkg.varchar2_value
           (hat.selected_person_id
           ,hats.api_name
           ,hats.transaction_step_id
           ,'P_ADDR_ATTRIBUTE_CATEGORY')
         ,'ADDR_ATTRIBUTE20'
         ,xxas_hr_api_transaction_pkg.varchar2_value
           (hat.selected_person_id
           ,hats.api_name
           ,hats.transaction_step_id
           ,'P_ADDR_ATTRIBUTE20'))               addr_attribute20_meaning_p 
,      xxas_hr_api_transaction_pkg.varchar2_value
         (hat.selected_person_id
         ,hats.api_name
         ,hats.transaction_step_id
         ,'P_ADD_INFORMATION13')                 add_information13_p
,      xxas_com_vset_pkg.decode_descr_vset_value
         ('800'
         ,'Address Structure'
         ,xxas_hr_api_transaction_pkg.varchar2_value
           (hat.selected_person_id
           ,hats.api_name
           ,hats.transaction_step_id
           ,'P_STYLE')
         ,'ADD_INFORMATION13'
         ,xxas_hr_api_transaction_pkg.varchar2_value
           (hat.selected_person_id
           ,hats.api_name
           ,hats.transaction_step_id
           ,'P_ADD_INFORMATION13'))              add_information13_meaning_p
,      xxas_hr_api_transaction_pkg.varchar2_value
         (hat.selected_person_id
         ,hats.api_name
         ,hats.transaction_step_id
         ,'P_ADD_INFORMATION14')                 add_information14_p
,      xxas_com_vset_pkg.decode_descr_vset_value
         ('800'
         ,'Address Structure'
         ,xxas_hr_api_transaction_pkg.varchar2_value
           (hat.selected_person_id
           ,hats.api_name
           ,hats.transaction_step_id
           ,'P_STYLE')
         ,'ADD_INFORMATION14'
         ,xxas_hr_api_transaction_pkg.varchar2_value
           (hat.selected_person_id
           ,hats.api_name
           ,hats.transaction_step_id
           ,'P_ADD_INFORMATION14'))              add_information14_meaning_p
,      xxas_hr_api_transaction_pkg.varchar2_value
         (hat.selected_person_id
         ,hats.api_name
         ,hats.transaction_step_id
         ,'P_ADD_INFORMATION15')                 add_information15_p
,      xxas_com_vset_pkg.decode_descr_vset_value
         ('800'
         ,'Address Structure'
         ,xxas_hr_api_transaction_pkg.varchar2_value
           (hat.selected_person_id
           ,hats.api_name
           ,hats.transaction_step_id
           ,'P_STYLE')
         ,'ADD_INFORMATION15'
         ,xxas_hr_api_transaction_pkg.varchar2_value
           (hat.selected_person_id
           ,hats.api_name
           ,hats.transaction_step_id
           ,'P_ADD_INFORMATION15'))              add_information15_meaning_p
,      xxas_hr_api_transaction_pkg.varchar2_value
         (hat.selected_person_id
         ,hats.api_name
         ,hats.transaction_step_id
         ,'P_ADD_INFORMATION16')                 add_information16_p
,      xxas_com_vset_pkg.decode_descr_vset_value
         ('800'
         ,'Address Structure'
         ,xxas_hr_api_transaction_pkg.varchar2_value
           (hat.selected_person_id
           ,hats.api_name
           ,hats.transaction_step_id
           ,'P_STYLE')
         ,'ADD_INFORMATION16'
         ,xxas_hr_api_transaction_pkg.varchar2_value
           (hat.selected_person_id
           ,hats.api_name
           ,hats.transaction_step_id
           ,'P_ADD_INFORMATION16'))              add_information16_meaning_p
,      xxas_hr_api_transaction_pkg.varchar2_value
         (hat.selected_person_id
         ,hats.api_name
         ,hats.transaction_step_id
         ,'P_ADD_INFORMATION17')                 add_information17_p
,      xxas_com_vset_pkg.decode_descr_vset_value
         ('800'
         ,'Address Structure'
         ,xxas_hr_api_transaction_pkg.varchar2_value
           (hat.selected_person_id
           ,hats.api_name
           ,hats.transaction_step_id
           ,'P_STYLE')
         ,'ADD_INFORMATION17'
         ,xxas_hr_api_transaction_pkg.varchar2_value
           (hat.selected_person_id
           ,hats.api_name
           ,hats.transaction_step_id
           ,'P_ADD_INFORMATION17'))              add_information17_meaning_p
,      xxas_hr_api_transaction_pkg.varchar2_value
         (hat.selected_person_id
         ,hats.api_name
         ,hats.transaction_step_id
         ,'P_ADD_INFORMATION18')                 add_information18_p
,      xxas_com_vset_pkg.decode_descr_vset_value
         ('800'
         ,'Address Structure'
         ,xxas_hr_api_transaction_pkg.varchar2_value
           (hat.selected_person_id
           ,hats.api_name
           ,hats.transaction_step_id
           ,'P_STYLE')
         ,'ADD_INFORMATION18'
         ,xxas_hr_api_transaction_pkg.varchar2_value
           (hat.selected_person_id
           ,hats.api_name
           ,hats.transaction_step_id
           ,'P_ADD_INFORMATION18'))              add_information18_meaning_p
,      xxas_hr_api_transaction_pkg.varchar2_value
         (hat.selected_person_id
         ,hats.api_name
         ,hats.transaction_step_id
         ,'P_ADD_INFORMATION19')                 add_information19_p
,      xxas_com_vset_pkg.decode_descr_vset_value
         ('800'
         ,'Address Structure'
         ,xxas_hr_api_transaction_pkg.varchar2_value
           (hat.selected_person_id
           ,hats.api_name
           ,hats.transaction_step_id
           ,'P_STYLE')
         ,'ADD_INFORMATION19'
         ,xxas_hr_api_transaction_pkg.varchar2_value
           (hat.selected_person_id
           ,hats.api_name
           ,hats.transaction_step_id
           ,'P_ADD_INFORMATION19'))              add_information19_meaning_p
,      xxas_hr_api_transaction_pkg.varchar2_value
         (hat.selected_person_id
         ,hats.api_name
         ,hats.transaction_step_id
         ,'P_ADD_INFORMATION20')                 add_information20_p
,      xxas_com_vset_pkg.decode_descr_vset_value
         ('800'
         ,'Address Structure'
         ,xxas_hr_api_transaction_pkg.varchar2_value
           (hat.selected_person_id
           ,hats.api_name
           ,hats.transaction_step_id
           ,'P_STYLE')
         ,'ADD_INFORMATION20'
         ,xxas_hr_api_transaction_pkg.varchar2_value
           (hat.selected_person_id
           ,hats.api_name
           ,hats.transaction_step_id
           ,'P_ADD_INFORMATION20'))              add_information20_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   aid     
,      hr_api_transaction_values   pid     
,      hr_api_transaction_values   cid     
WHERE  hat.transaction_id       = hats.transaction_id
AND    hat.status          NOT IN ('D', 'N', 'W', 'ACTIVE', 'AC')
AND    hats.api_name            = 'HR_PROCESS_ADDRESS_SS.PROCESS_API'
AND    hats.transaction_step_id = aid.transaction_step_id
AND    aid.name                 = 'P_ADDRESS_ID'
AND    aid.number_value        IS  NULL
AND    hats.transaction_step_id  = pid.transaction_step_id
AND    pid.name                  = 'P_CONTACT_OR_PERSON'
AND    pid.varchar2_value       IN ('DPDNT_CR_NEW_CONT','DPDNT_OVRW_UPD')
AND    hats.transaction_step_id  = cid.transaction_step_id
AND    cid.name                  = 'P_PERSON_ID';

BEGIN
  xxas_fnd_dictionary_pkg.add_view
   (p_application_short_name => xxas_util_pkg.app
   ,p_view_name              => 'XXAS_PER_ADDRESSES_V'
   ,p_description            => 'Applaud Person Addresses');
END;
/
  
COMMIT;
EXIT;
