/* $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_NEW_HIRE_REVIEW_V 
AS
SELECT hats1.transaction_id
	  ,fnd_global.employee_id person_id
	  ,xxas_hr_api_transaction_pkg.date_value
                   (hats1.api_name
                   ,hats1.transaction_step_id
                   ,'P_DATE_START') date_start
	  ,xxas_hr_api_transaction_pkg.varchar2_value
                   (hats1.api_name
                   ,hats1.transaction_step_id
                   ,'P_SEX') sex
      ,xxas_hr_api_transaction_pkg.number_value
                   (hats1.api_name
                   ,hats1.transaction_step_id
                   ,'P_PERSON_TYPE_ID') person_type_id
      ,xxas_hr_api_transaction_pkg.varchar2_value
                   (hats1.api_name
                   ,hats1.transaction_step_id
                   ,'P_TITLE') title
      ,xxas_hr_api_transaction_pkg.varchar2_value
                   (hats1.api_name
                   ,hats1.transaction_step_id
                   ,'P_FIRST_NAME') first_name
      ,xxas_hr_api_transaction_pkg.varchar2_value
                   (hats1.api_name
                   ,hats1.transaction_step_id
                   ,'P_MIDDLE_NAMES') middle_names
      ,xxas_hr_api_transaction_pkg.varchar2_value
                   (hats1.api_name
                   ,hats1.transaction_step_id
                   ,'P_PRE_NAME_ADJUNCT') prefix
      ,xxas_hr_api_transaction_pkg.varchar2_value
                   (hats1.api_name
                   ,hats1.transaction_step_id
                   ,'P_LAST_NAME') last_name
      ,xxas_hr_api_transaction_pkg.varchar2_value
                   (hats1.api_name
                   ,hats1.transaction_step_id
                   ,'P_SUFFIX') suffix
      ,xxas_hr_api_transaction_pkg.varchar2_value
                   (hats1.api_name
                   ,hats1.transaction_step_id
                   ,'P_KNOWN_AS') preferred_name
      ,xxas_hr_api_transaction_pkg.varchar2_value
                   (hats1.api_name
                   ,hats1.transaction_step_id
                   ,'P_PREVIOUS_LAST_NAME') previous_last_name
      ,xxas_hr_api_transaction_pkg.varchar2_value
                   (hats1.api_name
                   ,hats1.transaction_step_id
                   ,'P_NATIONAL_IDENTIFIER') national_identifier
      ,xxas_hr_api_transaction_pkg.date_value
                   (hats1.api_name
                   ,hats1.transaction_step_id
                   ,'P_DATE_OF_BIRTH') date_of_birth
      ,xxas_hr_api_transaction_pkg.varchar2_value
                   (hats1.api_name
                   ,hats1.transaction_step_id
                   ,'P_MARITAL_STATUS') marital_status
      ,xxas_hr_api_transaction_pkg.varchar2_value
                   (hats1.api_name
                   ,hats1.transaction_step_id
                   ,'P_EMAIL_ADDRESS') org_email_address
	  ,xxas_hr_api_transaction_pkg.date_value
                   (hats1.api_name
         		   ,hats1.transaction_step_id
                   ,'P_ADJUSTED_SVC_DATE') adjusted_svc_date
	  ,xxas_hr_api_transaction_pkg.varchar2_value
                   (hats1.api_name
         		   ,hats1.transaction_step_id
                   ,'P_APPLICANT_NUMBER') applicant_number
	  ,xxas_hr_api_transaction_pkg.number_value
                   (hats1.api_name
         		   ,hats1.transaction_step_id
                   ,'P_APPL_ASSIGNMENT_ID') appl_assignment_id
	  ,xxas_hr_api_transaction_pkg.number_value
                   (hats1.api_name
         		   ,hats1.transaction_step_id
                   ,'P_ASG_OBJECT_VERSION_NUMBER') asg_ovn
	  ,xxas_hr_api_transaction_pkg.varchar2_value
                   (hats1.api_name
         		   ,hats1.transaction_step_id
                   ,'P_ATTRIBUTE_CATEGORY') attribute_category
	  ,xxas_hr_api_transaction_pkg.varchar2_value
                   (hats1.api_name
         		   ,hats1.transaction_step_id
                   ,'P_ATTRIBUTE1') attribute1
	  ,xxas_hr_api_transaction_pkg.varchar2_value
                   (hats1.api_name
         		   ,hats1.transaction_step_id
                   ,'P_ATTRIBUTE2') attribute2
	  ,xxas_hr_api_transaction_pkg.varchar2_value
                   (hats1.api_name
         		   ,hats1.transaction_step_id
                   ,'P_ATTRIBUTE3') attribute3
	  ,xxas_hr_api_transaction_pkg.varchar2_value
                   (hats1.api_name
         		   ,hats1.transaction_step_id
                   ,'P_ATTRIBUTE4') attribute4
	  ,xxas_hr_api_transaction_pkg.varchar2_value
                   (hats1.api_name
         		   ,hats1.transaction_step_id
                   ,'P_ATTRIBUTE5') attribute5
	  ,xxas_hr_api_transaction_pkg.varchar2_value
                   (hats1.api_name
         		   ,hats1.transaction_step_id
                   ,'P_ATTRIBUTE6') attribute6
	  ,xxas_hr_api_transaction_pkg.varchar2_value
                   (hats1.api_name
         		   ,hats1.transaction_step_id
                   ,'P_ATTRIBUTE7') attribute7
	  ,xxas_hr_api_transaction_pkg.varchar2_value
                   (hats1.api_name
         		   ,hats1.transaction_step_id
                   ,'P_ATTRIBUTE8') attribute8
	  ,xxas_hr_api_transaction_pkg.varchar2_value
                   (hats1.api_name
         		   ,hats1.transaction_step_id
                   ,'P_ATTRIBUTE9') attribute9
	  ,xxas_hr_api_transaction_pkg.varchar2_value
                   (hats1.api_name
         		   ,hats1.transaction_step_id
                   ,'P_ATTRIBUTE10') attribute10
	  ,xxas_hr_api_transaction_pkg.varchar2_value
                   (hats1.api_name
         		   ,hats1.transaction_step_id
                   ,'P_ATTRIBUTE11') attribute11
	  ,xxas_hr_api_transaction_pkg.varchar2_value
                   (hats1.api_name
         		   ,hats1.transaction_step_id
                   ,'P_ATTRIBUTE12') attribute12
	  ,xxas_hr_api_transaction_pkg.varchar2_value
                   (hats1.api_name
         		   ,hats1.transaction_step_id
                   ,'P_ATTRIBUTE13') attribute13
	  ,xxas_hr_api_transaction_pkg.varchar2_value
                   (hats1.api_name
         		   ,hats1.transaction_step_id
                   ,'P_ATTRIBUTE14') attribute14
	  ,xxas_hr_api_transaction_pkg.varchar2_value
                   (hats1.api_name
         		   ,hats1.transaction_step_id
                   ,'P_ATTRIBUTE15') attribute15
	  ,xxas_hr_api_transaction_pkg.varchar2_value
                   (hats1.api_name
         		   ,hats1.transaction_step_id
                   ,'P_ATTRIBUTE16') attribute16
	  ,xxas_hr_api_transaction_pkg.varchar2_value
                   (hats1.api_name
         		   ,hats1.transaction_step_id
                   ,'P_ATTRIBUTE17') attribute17
	  ,xxas_hr_api_transaction_pkg.varchar2_value
                   (hats1.api_name
         		   ,hats1.transaction_step_id
                   ,'P_ATTRIBUTE18') attribute18
	  ,xxas_hr_api_transaction_pkg.varchar2_value
                   (hats1.api_name
         		   ,hats1.transaction_step_id
                   ,'P_ATTRIBUTE19') attribute19
	  ,xxas_hr_api_transaction_pkg.varchar2_value
                   (hats1.api_name
         		   ,hats1.transaction_step_id
                   ,'P_ATTRIBUTE20') attribute20
	  ,xxas_hr_api_transaction_pkg.varchar2_value
                   (hats1.api_name
         		   ,hats1.transaction_step_id
                   ,'P_ATTRIBUTE21') attribute21
	  ,xxas_hr_api_transaction_pkg.varchar2_value
                   (hats1.api_name
         		   ,hats1.transaction_step_id
                   ,'P_ATTRIBUTE22') attribute22
	  ,xxas_hr_api_transaction_pkg.varchar2_value
                   (hats1.api_name
         		   ,hats1.transaction_step_id
                   ,'P_ATTRIBUTE23') attribute23
	  ,xxas_hr_api_transaction_pkg.varchar2_value
                   (hats1.api_name
         		   ,hats1.transaction_step_id
                   ,'P_ATTRIBUTE24') attribute24
	  ,xxas_hr_api_transaction_pkg.varchar2_value
                   (hats1.api_name
         		   ,hats1.transaction_step_id
                   ,'P_ATTRIBUTE25') attribute25
	  ,xxas_hr_api_transaction_pkg.varchar2_value
                   (hats1.api_name
         		   ,hats1.transaction_step_id
                   ,'P_ATTRIBUTE26') attribute26
	  ,xxas_hr_api_transaction_pkg.varchar2_value
                   (hats1.api_name
         		   ,hats1.transaction_step_id
                   ,'P_ATTRIBUTE27') attribute27
	  ,xxas_hr_api_transaction_pkg.varchar2_value
                   (hats1.api_name
         		   ,hats1.transaction_step_id
                   ,'P_ATTRIBUTE28') attribute28
	  ,xxas_hr_api_transaction_pkg.varchar2_value
                   (hats1.api_name
         		   ,hats1.transaction_step_id
                   ,'P_ATTRIBUTE29') attribute29
	  ,xxas_hr_api_transaction_pkg.varchar2_value
                   (hats1.api_name
         		   ,hats1.transaction_step_id
                   ,'P_ATTRIBUTE30') attribute30
	  ,xxas_hr_api_transaction_pkg.varchar2_value
                   (hats1.api_name
         		   ,hats1.transaction_step_id
                   ,'P_ATTRIBUTE_UPDATE_MODE') attribute_update_mode
	  ,xxas_hr_api_transaction_pkg.varchar2_value
                   (hats1.api_name
         		   ,hats1.transaction_step_id
                   ,'P_BACKGROUND_CHECK_STATUS') background_check_status
	  ,xxas_hr_api_transaction_pkg.date_value
                   (hats1.api_name
         		   ,hats1.transaction_step_id
                   ,'P_BACKGROUND_DATE_CHECK') background_date_check
	  ,xxas_hr_api_transaction_pkg.number_value
                   (hats1.api_name
         		   ,hats1.transaction_step_id
                   ,'P_BENEFIT_GROUP_ID') benefit_group_id
	  ,xxas_hr_api_transaction_pkg.number_value
                   (hats1.api_name
         		   ,hats1.transaction_step_id
                   ,'P_BUSINESS_GROUP_ID') business_group_1
	  ,xxas_hr_api_transaction_pkg.varchar2_value
                   (hats1.api_name
         		   ,hats1.transaction_step_id
                   ,'P_BLOOD_TYPE') blood_type
	  ,xxas_hr_api_transaction_pkg.number_value
                   (hats1.api_name
         		   ,hats1.transaction_step_id
                   ,'P_BUSINESS_GROUP_ID') business_group_id
	  ,xxas_hr_api_transaction_pkg.varchar2_value
                   (hats1.api_name
         		   ,hats1.transaction_step_id
                   ,'P_COORD_BEN_MED_PLN_NO') coord_ben_med_pln_no
	  ,xxas_hr_api_transaction_pkg.varchar2_value
                   (hats1.api_name
         		   ,hats1.transaction_step_id
                   ,'P_COORD_BEN_NO_CVG_FLAG') coord_ben_no_cvg_flag
	  ,xxas_hr_api_transaction_pkg.varchar2_value
                   (hats1.api_name
         		   ,hats1.transaction_step_id
                   ,'P_CORRESPONDENCE_LANGUAGE') correspondence_language
	  ,xxas_hr_api_transaction_pkg.varchar2_value
                   (hats1.api_name
         		   ,hats1.transaction_step_id
                   ,'P_COUNTRY_OF_BIRTH') country_of_birth
	  ,xxas_hr_api_transaction_pkg.date_value
                   (hats1.api_name
         		   ,hats1.transaction_step_id
                   ,'P_DATE_EMPLOYEE_DATA_VERIFIED') date_employee_data_verified
	  ,xxas_hr_api_transaction_pkg.date_value
                   (hats1.api_name
         		   ,hats1.transaction_step_id
                   ,'P_DATE_OF_DEATH') date_of_death
	  ,xxas_hr_api_transaction_pkg.date_value
                   (hats1.api_name
         		   ,hats1.transaction_step_id
                   ,'P_DPDNT_ADOPTION_DATE') dpdnt_adoption_date
	  ,xxas_hr_api_transaction_pkg.varchar2_value
                   (hats1.api_name
         		   ,hats1.transaction_step_id
                   ,'P_DPDNT_VLNTRY_SVCE_FLAG') dpdnt_vlntry_svce_flag
	  ,xxas_hr_api_transaction_pkg.date_value
                   (hats1.api_name
         		   ,hats1.transaction_step_id
                   ,'P_EFFECTIVE_DATE') effective_date
	  ,xxas_hr_api_transaction_pkg.varchar2_value
                   (hats1.api_name
         		   ,hats1.transaction_step_id
                   ,'P_EMPLOYEE_NUMBER') employee_number
	  ,xxas_hr_api_transaction_pkg.varchar2_value
                   (hats1.api_name
         		   ,hats1.transaction_step_id
                   ,'P_EXPENSE_CHECK_SEND_TO_ADDRES') expense_check_send_to_addres
	  ,xxas_hr_api_transaction_pkg.varchar2_value
                   (hats1.api_name
         		   ,hats1.transaction_step_id
                   ,'P_FAST_PATH_EMPLOYEE') fast_path_employee
	  ,xxas_hr_api_transaction_pkg.number_value
                   (hats1.api_name
         		   ,hats1.transaction_step_id
                   ,'P_FTE_CAPACITY') fte_capacity_p
	  ,xxas_hr_api_transaction_pkg.varchar2_value
                   (hats1.api_name
         		   ,hats1.transaction_step_id
                   ,'P_FULL_NAME') full_name
	  ,xxas_hr_api_transaction_pkg.number_value
                   (hats1.api_name
         		   ,hats1.transaction_step_id
                   ,'P_GLOBAL_PERSON_ID') global_person_id
	  ,xxas_hr_api_transaction_pkg.date_value
                   (hats1.api_name
         		   ,hats1.transaction_step_id
                   ,'P_HOLD_APPLICANT_DATE_UNTIL') hold_applicant_date_until
	  ,xxas_hr_api_transaction_pkg.varchar2_value
                   (hats1.api_name
         		   ,hats1.transaction_step_id
                   ,'P_HONORS') honors
	  ,xxas_hr_api_transaction_pkg.varchar2_value
                   (hats1.api_name
         		   ,hats1.transaction_step_id
                   ,'P_INTERNAL_LOCATION') internal_location
	  ,xxas_hr_api_transaction_pkg.varchar2_value
                   (hats1.api_name
         		   ,hats1.transaction_step_id
                   ,'P_LAST_MEDICAL_TEST_BY') last_medical_test_by
	  ,xxas_hr_api_transaction_pkg.date_value
                   (hats1.api_name
         		   ,hats1.transaction_step_id
                   ,'P_LAST_MEDICAL_TEST_DATE') last_medical_test_date
	  ,xxas_hr_api_transaction_pkg.varchar2_value
                   (hats1.api_name
         		   ,hats1.transaction_step_id
                   ,'P_MAILSTOP') mailstop
	  ,xxas_hr_api_transaction_pkg.varchar2_value
                   (hats1.api_name
         		   ,hats1.transaction_step_id
                   ,'P_NATIONALITY') nationality
	  ,xxas_hr_api_transaction_pkg.varchar2_value
                   (hats1.api_name
         		   ,hats1.transaction_step_id
                   ,'P_NPW_NUMBER') npw_number
	  ,xxas_hr_api_transaction_pkg.number_value
                   (hats1.api_name
         		   ,hats1.transaction_step_id
                   ,'P_OBJECT_VERSION_NUMBER') object_version_number
	  ,xxas_hr_api_transaction_pkg.varchar2_value
                   (hats1.api_name
         		   ,hats1.transaction_step_id
                   ,'P_OFFICE_NUMBER') office_number
	  ,xxas_hr_api_transaction_pkg.varchar2_value
                   (hats1.api_name
         		   ,hats1.transaction_step_id
                   ,'P_ON_MILITARY_SERVICE') on_military_service
	  ,xxas_hr_api_transaction_pkg.date_value
                   (hats1.api_name
         		   ,hats1.transaction_step_id
                   ,'P_ORIGINAL_DATE_OF_HIRE') original_date_of_hire
	  ,xxas_hr_api_transaction_pkg.varchar2_value
                   (hats1.api_name
         		   ,hats1.transaction_step_id
                   ,'P_PER_INFORMATION_CATEGORY') per_information_category
	  ,xxas_hr_api_transaction_pkg.varchar2_value
                   (hats1.api_name
         		   ,hats1.transaction_step_id
                   ,'P_PER_INFORMATION1') per_information1
	  ,xxas_hr_api_transaction_pkg.varchar2_value
                   (hats1.api_name
         		   ,hats1.transaction_step_id
                   ,'P_PER_INFORMATION2') per_information2
	  ,xxas_hr_api_transaction_pkg.varchar2_value
                   (hats1.api_name
         		   ,hats1.transaction_step_id
                   ,'P_PER_INFORMATION3') per_information3
	  ,xxas_hr_api_transaction_pkg.varchar2_value
                   (hats1.api_name
         		   ,hats1.transaction_step_id
                   ,'P_PER_INFORMATION4') per_information4
	  ,xxas_hr_api_transaction_pkg.varchar2_value
                   (hats1.api_name
         		   ,hats1.transaction_step_id
                   ,'P_PER_INFORMATION5') per_information5
	  ,xxas_hr_api_transaction_pkg.varchar2_value
                   (hats1.api_name
         		   ,hats1.transaction_step_id
                   ,'P_PER_INFORMATION6') per_information6
	  ,xxas_hr_api_transaction_pkg.varchar2_value
                   (hats1.api_name
         		   ,hats1.transaction_step_id
                   ,'P_PER_INFORMATION7') per_information7
	  ,xxas_hr_api_transaction_pkg.varchar2_value
                   (hats1.api_name
         		   ,hats1.transaction_step_id
                   ,'P_PER_INFORMATION8') per_information8
	  ,xxas_hr_api_transaction_pkg.varchar2_value
                   (hats1.api_name
         		   ,hats1.transaction_step_id
                   ,'P_PER_INFORMATION9') per_information9
	  ,xxas_hr_api_transaction_pkg.varchar2_value
                   (hats1.api_name
         		   ,hats1.transaction_step_id
                   ,'P_PER_INFORMATION10') per_information10
	  ,xxas_hr_api_transaction_pkg.varchar2_value
                   (hats1.api_name
         		   ,hats1.transaction_step_id
                   ,'P_PER_INFORMATION11') per_information11
	  ,xxas_hr_api_transaction_pkg.varchar2_value
                   (hats1.api_name
         		   ,hats1.transaction_step_id
                   ,'P_PER_INFORMATION12') per_information12
	  ,xxas_hr_api_transaction_pkg.varchar2_value
                   (hats1.api_name
         		   ,hats1.transaction_step_id
                   ,'P_PER_INFORMATION13') per_information13
	  ,xxas_hr_api_transaction_pkg.varchar2_value
                   (hats1.api_name
         		   ,hats1.transaction_step_id
                   ,'P_PER_INFORMATION14') per_information14
	  ,xxas_hr_api_transaction_pkg.varchar2_value
                   (hats1.api_name
         		   ,hats1.transaction_step_id
                   ,'P_PER_INFORMATION15') per_information15
	  ,xxas_hr_api_transaction_pkg.varchar2_value
                   (hats1.api_name
         		   ,hats1.transaction_step_id
                   ,'P_PER_INFORMATION16') per_information16
	  ,xxas_hr_api_transaction_pkg.varchar2_value
                   (hats1.api_name
         		   ,hats1.transaction_step_id
                   ,'P_PER_INFORMATION17') per_information17
	  ,xxas_hr_api_transaction_pkg.varchar2_value
                   (hats1.api_name
         		   ,hats1.transaction_step_id
                   ,'P_PER_INFORMATION18') per_information18
	  ,xxas_hr_api_transaction_pkg.varchar2_value
                   (hats1.api_name
         		   ,hats1.transaction_step_id
                   ,'P_PER_INFORMATION19') per_information19
	  ,xxas_hr_api_transaction_pkg.varchar2_value
                   (hats1.api_name
         		   ,hats1.transaction_step_id
                   ,'P_PER_INFORMATION20') per_information20
	  ,xxas_hr_api_transaction_pkg.varchar2_value
                   (hats1.api_name
         		   ,hats1.transaction_step_id
                   ,'P_PER_INFORMATION21') per_information21
	  ,xxas_hr_api_transaction_pkg.varchar2_value
                   (hats1.api_name
         		   ,hats1.transaction_step_id
                   ,'P_PER_INFORMATION22') per_information22
	  ,xxas_hr_api_transaction_pkg.varchar2_value
                   (hats1.api_name
         		   ,hats1.transaction_step_id
                   ,'P_PER_INFORMATION23') per_information23
	  ,xxas_hr_api_transaction_pkg.varchar2_value
                   (hats1.api_name
         		   ,hats1.transaction_step_id
                   ,'P_PER_INFORMATION24') per_information24
	  ,xxas_hr_api_transaction_pkg.varchar2_value
                   (hats1.api_name
         		   ,hats1.transaction_step_id
                   ,'P_PER_INFORMATION25') per_information25
	  ,xxas_hr_api_transaction_pkg.varchar2_value
                   (hats1.api_name
         		   ,hats1.transaction_step_id
                   ,'P_PER_INFORMATION26') per_information26
	  ,xxas_hr_api_transaction_pkg.varchar2_value
                   (hats1.api_name
         		   ,hats1.transaction_step_id
                   ,'P_PER_INFORMATION27') per_information27
	  ,xxas_hr_api_transaction_pkg.varchar2_value
                   (hats1.api_name
         		   ,hats1.transaction_step_id
                   ,'P_PER_INFORMATION28') per_information28
	  ,xxas_hr_api_transaction_pkg.varchar2_value
                   (hats1.api_name
         		   ,hats1.transaction_step_id
                   ,'P_PER_INFORMATION29') per_information29
	  ,xxas_hr_api_transaction_pkg.varchar2_value
                   (hats1.api_name
         		   ,hats1.transaction_step_id
                   ,'P_PER_INFORMATION30') per_information30
	  ,xxas_hr_api_transaction_pkg.date_value
                   (hats1.api_name
         		   ,hats1.transaction_step_id
                   ,'P_PROJECTED_START_DATE') projected_start_date
	  ,xxas_hr_api_transaction_pkg.date_value
                   (hats1.api_name
         		   ,hats1.transaction_step_id
                   ,'P_RECEIPT_OF_DEATH_CERT_DATE') receipt_of_death_cert_date
	  ,xxas_hr_api_transaction_pkg.varchar2_value
                   (hats1.api_name
         		   ,hats1.transaction_step_id
                   ,'P_REGION_OF_BIRTH') region_of_birth
	  ,xxas_hr_api_transaction_pkg.varchar2_value
                   (hats1.api_name
         		   ,hats1.transaction_step_id
                   ,'P_REGISTERED_DISABLED_FLAG') registered_disabled_flag
	  ,xxas_hr_api_transaction_pkg.varchar2_value
                   (hats1.api_name
         		   ,hats1.transaction_step_id
                   ,'P_REHIRE_AUTHORIZOR') rehire_authorizor
	  ,xxas_hr_api_transaction_pkg.varchar2_value
                   (hats1.api_name
         		   ,hats1.transaction_step_id
                   ,'P_REHIRE_REASON') rehire_reason
	  ,xxas_hr_api_transaction_pkg.varchar2_value
                   (hats1.api_name
         		   ,hats1.transaction_step_id
                   ,'P_REHIRE_RECOMMENDATION') rehire_recommendation
	  ,xxas_hr_api_transaction_pkg.varchar2_value
                   (hats1.api_name
         		   ,hats1.transaction_step_id
                   ,'P_RESUME_EXISTS') resume_exists
	  ,xxas_hr_api_transaction_pkg.date_value
                   (hats1.api_name
         		   ,hats1.transaction_step_id
                   ,'P_RESUME_LAST_UPDATED') resume_last_updated_p
	  ,xxas_hr_api_transaction_pkg.varchar2_value
                   (hats1.api_name
         		   ,hats1.transaction_step_id
                   ,'P_SECOND_PASSPORT_EXISTS') second_passport_exists
	  ,xxas_hr_api_transaction_pkg.varchar2_value
                   (hats1.api_name
         		   ,hats1.transaction_step_id
                   ,'P_STUDENT_STATUS') student_status
	  ,xxas_hr_api_transaction_pkg.varchar2_value
                   (hats1.api_name
         		   ,hats1.transaction_step_id
                   ,'P_TOWN_OF_BIRTH') town_of_birth
	  ,xxas_hr_api_transaction_pkg.varchar2_value
                   (hats1.api_name
         		   ,hats1.transaction_step_id
                   ,'P_USES_TOBACCO_FLAG') uses_tobacco_flag
	  ,xxas_hr_api_transaction_pkg.number_value
                   (hats1.api_name
         		   ,hats1.transaction_step_id
                   ,'P_VENDOR_ID') vendor_id
	  ,xxas_hr_api_transaction_pkg.varchar2_value
                   (hats1.api_name
         		   ,hats1.transaction_step_id
                   ,'P_WORK_SCHEDULE') work_schedule
	  ,xxas_hr_api_transaction_pkg.varchar2_value
                   (hats1.api_name
         		   ,hats1.transaction_step_id
                   ,'P_WORK_TELEPHONE') work_telephone
	  ,xxas_hr_api_transaction_pkg.varchar2_value
                   (hats2.api_name
                   ,hats2.transaction_step_id
                   ,'P_ADDRESS_TYPE') address_type
	  ,xxas_hr_api_transaction_pkg.varchar2_value
                   (hats2.api_name
                   ,hats2.transaction_step_id
                   ,'P_ADDRESS_TYPE_CODE') address_type_code
      ,xxas_hr_api_transaction_pkg.varchar2_value
                   (hats2.api_name
                   ,hats2.transaction_step_id
                   ,'P_ADDR_ATTRIBUTE_CATEGORY') addr_attribute_category
	  ,xxas_hr_api_transaction_pkg.varchar2_value
                   (hats2.api_name
                   ,hats2.transaction_step_id
                   ,'P_STYLE') style
      ,xxas_hr_api_transaction_pkg.varchar2_value
                   (hats2.api_name
                   ,hats2.transaction_step_id
                   ,'P_COUNTRY') country
      ,xxas_hr_api_transaction_pkg.varchar2_value
                   (hats2.api_name
                   ,hats2.transaction_step_id
                   ,'P_COUNTRY_CODE') country_code
	  ,xxas_hr_api_transaction_pkg.varchar2_value
                   (hats2.api_name
                   ,hats2.transaction_step_id
                   ,'P_ADDRESS_LINE1') address_line1
      ,xxas_hr_api_transaction_pkg.varchar2_value
                   (hats2.api_name
                   ,hats2.transaction_step_id
                   ,'P_ADDRESS_LINE2') address_line2
      ,xxas_hr_api_transaction_pkg.varchar2_value
                   (hats2.api_name
                   ,hats2.transaction_step_id
                   ,'P_ADDRESS_LINE3') address_line3
	  ,xxas_hr_api_transaction_pkg.varchar2_value
                   (hats2.api_name
                   ,hats2.transaction_step_id
                   ,'P_CITY') town_or_city
	  ,xxas_hr_api_transaction_pkg.varchar2_value
                   (hats2.api_name
                   ,hats2.transaction_step_id
                   ,'P_POSTAL_CODE') postal_code
	  ,xxas_hr_api_transaction_pkg.varchar2_value
                   (hats2.api_name
                   ,hats2.transaction_step_id
                   ,'P_REGION1') region_1
	  ,xxas_hr_api_transaction_pkg.varchar2_value
                   (hats2.api_name
                   ,hats2.transaction_step_id
                   ,'P_REGION2') region_2
	  ,xxas_hr_api_transaction_pkg.varchar2_value
                   (hats2.api_name
                   ,hats2.transaction_step_id
                   ,'P_REGION3') region_3
	  ,xxas_hr_api_transaction_pkg.varchar2_value
                   (hats2.api_name
                   ,hats2.transaction_step_id
                   ,'P_TELEPHONE_NUMBER1') telephone_number_1
	  ,xxas_hr_api_transaction_pkg.varchar2_value
                   (hats2.api_name
                   ,hats2.transaction_step_id
                   ,'P_TELEPHONE_NUMBER2') telephone_number_2
	  ,xxas_hr_api_transaction_pkg.varchar2_value
                   (hats2.api_name
                   ,hats2.transaction_step_id
                   ,'P_TELEPHONE_NUMBER3') telephone_number_3
      ,xxas_hr_api_transaction_pkg.varchar2_value
				  (hats2.api_name
				  ,hats2.transaction_step_id
				  ,'P_ADDR_ATTRIBUTE1') ADD_INFORMATION1
	  ,xxas_hr_api_transaction_pkg.varchar2_value
				  (hats2.api_name
				  ,hats2.transaction_step_id
				  ,'P_ADDR_ATTRIBUTE2') ADD_INFORMATION2
	  ,xxas_hr_api_transaction_pkg.varchar2_value
				  (hats2.api_name
				  ,hats2.transaction_step_id
				  ,'P_ADDR_ATTRIBUTE3') ADD_INFORMATION3
	  ,xxas_hr_api_transaction_pkg.varchar2_value
				  (hats2.api_name
				  ,hats2.transaction_step_id
				  ,'P_ADDR_ATTRIBUTE4') ADD_INFORMATION4
	  ,xxas_hr_api_transaction_pkg.varchar2_value
				  (hats2.api_name
				  ,hats2.transaction_step_id
				  ,'P_ADDR_ATTRIBUTE5') ADD_INFORMATION5
	  ,xxas_hr_api_transaction_pkg.varchar2_value
				  (hats2.api_name
				  ,hats2.transaction_step_id
				  ,'P_ADDR_ATTRIBUTE6') ADD_INFORMATION6
	  ,xxas_hr_api_transaction_pkg.varchar2_value
				  (hats2.api_name
				  ,hats2.transaction_step_id
				  ,'P_ADDR_ATTRIBUTE7') ADD_INFORMATION7
	  ,xxas_hr_api_transaction_pkg.varchar2_value
				  (hats2.api_name
				  ,hats2.transaction_step_id
				  ,'P_ADDR_ATTRIBUTE8') ADD_INFORMATION8
	  ,xxas_hr_api_transaction_pkg.varchar2_value
				  (hats2.api_name
				  ,hats2.transaction_step_id
				  ,'P_ADDR_ATTRIBUTE9') ADD_INFORMATION9
	  ,xxas_hr_api_transaction_pkg.varchar2_value
				  (hats2.api_name
				  ,hats2.transaction_step_id
				  ,'P_ADDR_ATTRIBUTE10') ADD_INFORMATION10
	  ,xxas_hr_api_transaction_pkg.varchar2_value
				  (hats2.api_name
				  ,hats2.transaction_step_id
				  ,'P_ADDR_ATTRIBUTE11') ADD_INFORMATION11
	  ,xxas_hr_api_transaction_pkg.varchar2_value
				  (hats2.api_name
				  ,hats2.transaction_step_id
				  ,'P_ADDR_ATTRIBUTE12') ADD_INFORMATION12
	  ,xxas_hr_api_transaction_pkg.varchar2_value
				  (hats2.api_name
				  ,hats2.transaction_step_id
				  ,'P_ADDR_ATTRIBUTE13') ADD_INFORMATION13
	  ,xxas_hr_api_transaction_pkg.varchar2_value
				  (hats2.api_name
				  ,hats2.transaction_step_id
				  ,'P_ADDR_ATTRIBUTE14') ADD_INFORMATION14
	  ,xxas_hr_api_transaction_pkg.varchar2_value
				  (hats2.api_name
				  ,hats2.transaction_step_id
				  ,'P_ADDR_ATTRIBUTE15') ADD_INFORMATION15
	  ,xxas_hr_api_transaction_pkg.varchar2_value
				  (hats2.api_name
				  ,hats2.transaction_step_id
				  ,'P_ADDR_ATTRIBUTE16') ADD_INFORMATION16
	  ,xxas_hr_api_transaction_pkg.varchar2_value
				  (hats2.api_name
				  ,hats2.transaction_step_id
				  ,'P_ADDR_ATTRIBUTE17') ADD_INFORMATION17
	  ,xxas_hr_api_transaction_pkg.varchar2_value
				  (hats2.api_name
				  ,hats2.transaction_step_id
				  ,'P_ADDR_ATTRIBUTE18') ADD_INFORMATION18
	  ,xxas_hr_api_transaction_pkg.varchar2_value
				  (hats2.api_name
				  ,hats2.transaction_step_id
				  ,'P_ADDR_ATTRIBUTE19') ADD_INFORMATION19
	  ,xxas_hr_api_transaction_pkg.varchar2_value
				  (hats2.api_name
				  ,hats2.transaction_step_id
				  ,'P_ADDR_ATTRIBUTE20') ADD_INFORMATION20
	  ,xxas_hr_api_transaction_pkg.number_value
				  (hats3.api_name
				  ,hats3.transaction_step_id
				  ,'P_ASSIGNMENT_ID') assignment_id
	  ,xxas_hr_api_transaction_pkg.number_value
				  (hats3.api_name
				  ,hats3.transaction_step_id
				  ,'P_BUSINESS_GROUP_ID') asg_business_group_id 
	  ,xxas_hr_api_transaction_pkg.varchar2_value
				  (hats3.api_name
				  ,hats3.transaction_step_id
				  ,'P_WORK_AT_HOME') work_at_home
	  ,xxas_hr_api_transaction_pkg.varchar2_value
				  (hats3.api_name
				  ,hats3.transaction_step_id
				  ,'P_ORG_NAME') org_name
	  ,xxas_hr_api_transaction_pkg.varchar2_value
				  (hats3.api_name
				  ,hats3.transaction_step_id
				  ,'P_JOB_NAME') job_name
	  ,xxas_hr_api_transaction_pkg.number_value
				  (hats3.api_name
				  ,hats3.transaction_step_id
				  ,'P_PEOPLE_GROUP_ID') people_group_id
	  ,xxas_hr_api_transaction_pkg.number_value
				  (hats3.api_name
				  ,hats3.transaction_step_id
				  ,'P_PAY_BASIS_ID') pay_basis_id
	  ,xxas_hr_api_transaction_pkg.number_value
				  (hats3.api_name
				  ,hats3.transaction_step_id
				  ,'P_SAL_REVIEW_PERIOD') salary_review_period
	  ,xxas_hr_api_transaction_pkg.varchar2_value
				  (hats3.api_name
				  ,hats3.transaction_step_id
				  ,'P_SAL_REVIEW_PERIOD_FREQUENCY') sal_rev_period_freq
	  ,xxas_hr_api_transaction_pkg.number_value
				  (hats3.api_name
				  ,hats3.transaction_step_id
				  ,'P_PERF_REVIEW_PERIOD') perf_rev_period
	  ,xxas_hr_api_transaction_pkg.varchar2_value
				  (hats3.api_name
				  ,hats3.transaction_step_id
				  ,'P_PERF_REVIEW_PERIOD_FREQUENCY') pref_rev_period_freq
	  ,xxas_hr_api_transaction_pkg.number_value
				  (hats3.api_name
				  ,hats3.transaction_step_id
				  ,'P_LOCATION_ID') location_id
	  ,xxas_hr_api_transaction_pkg.number_value
				  (hats3.api_name
				  ,hats3.transaction_step_id
				  ,'P_NORMAL_HOURS') work_hours
	  ,xxas_hr_api_transaction_pkg.varchar2_value
				  (hats3.api_name
				  ,hats3.transaction_step_id
				  ,'P_FREQUENCY') frequency
	  ,xxas_hr_api_transaction_pkg.varchar2_value
				  (hats3.api_name
				  ,hats3.transaction_step_id
				  ,'P_TIME_NORMAL_START') time_normal_start
	  ,xxas_hr_api_transaction_pkg.varchar2_value
				  (hats3.api_name
				  ,hats3.transaction_step_id
				  ,'P_TIME_NORMAL_FINISH') time_normal_finish
	  ,xxas_hr_api_transaction_pkg.varchar2_value
				  (hats3.api_name
				  ,hats3.transaction_step_id
				  ,'P_EMPLOYMENT_CATEGORY') asg_category
	  ,xxas_hr_api_transaction_pkg.varchar2_value
				  (hats3.api_name
				  ,hats3.transaction_step_id
				  ,'P_EMPLOYEE_CATEGORY') emp_category
	  ,xxas_hr_api_transaction_pkg.varchar2_value
				  (hats3.api_name
				  ,hats3.transaction_step_id
				  ,'P_TITLE') billing_title
	  ,xxas_hr_api_transaction_pkg.varchar2_value
				  (hats3.api_name
				  ,hats3.transaction_step_id
				  ,'P_BARGAINING_UNIT_CODE') bargaining_unit_code
	  ,xxas_hr_api_transaction_pkg.varchar2_value
				  (hats3.api_name
				  ,hats3.transaction_step_id
				  ,'P_LABOUR_UNION_MEMBER_FLAG') union_member_flag
	  ,xxas_hr_api_transaction_pkg.varchar2_value
				  (hats3.api_name
				  ,hats3.transaction_step_id
				  ,'P_PRIMARY_FLAG') primary_assignment
	  ,xxas_hr_api_transaction_pkg.varchar2_value
				  (hats3.api_name
				  ,hats3.transaction_step_id
				  ,'P_POS_NAME') position_name
	  ,xxas_hr_api_transaction_pkg.number_value
				  (hats3.api_name
				  ,hats3.transaction_step_id
				  ,'P_GRADE_ID') grade_id
	  ,xxas_hr_api_transaction_pkg.varchar2_value
				  (hats3.api_name
				  ,hats3.transaction_step_id
				  ,'P_GRADE_NAME') grade_name
	  ,xxas_hr_api_transaction_pkg.number_value
				  (hats3.api_name
				  ,hats3.transaction_step_id
				  ,'P_APPROVER_ID') approver_id
	  ,xxas_hr_api_transaction_pkg.number_value
				  (hats3.api_name
				  ,hats3.transaction_step_id
				  ,'P_ASSIGNMENT_STATUS_TYPE_ID') asg_status
	  ,xxas_hr_api_transaction_pkg.varchar2_value
				  (hats3.api_name
				  ,hats3.transaction_step_id
				  ,'P_ASSIGNMENT_TYPE') assignment_type
	  ,xxas_hr_api_transaction_pkg.varchar2_value
				  (hats3.api_name
				  ,hats3.transaction_step_id
				  ,'P_ASS_ATTRIBUTE_CATEGORY') ass_attribute_category
	  ,xxas_hr_api_transaction_pkg.varchar2_value
				  (hats3.api_name
				  ,hats3.transaction_step_id
				  ,'P_ASS_ATTRIBUTE1') ass_attribute1
	  ,xxas_hr_api_transaction_pkg.varchar2_value
				  (hats3.api_name
				  ,hats3.transaction_step_id
				  ,'P_ASS_ATTRIBUTE2') ass_attribute2
	  ,xxas_hr_api_transaction_pkg.varchar2_value
				  (hats3.api_name
				  ,hats3.transaction_step_id
				  ,'P_ASS_ATTRIBUTE3') ass_attribute3
	  ,xxas_hr_api_transaction_pkg.varchar2_value
				  (hats3.api_name
				  ,hats3.transaction_step_id
				  ,'P_ASS_ATTRIBUTE4') ass_attribute4
	  ,xxas_hr_api_transaction_pkg.varchar2_value
				  (hats3.api_name
				  ,hats3.transaction_step_id
				  ,'P_ASS_ATTRIBUTE5') ass_attribute5
	  ,xxas_hr_api_transaction_pkg.varchar2_value
				  (hats3.api_name
				  ,hats3.transaction_step_id
				  ,'P_ASS_ATTRIBUTE6') ass_attribute6
	  ,xxas_hr_api_transaction_pkg.varchar2_value
				  (hats3.api_name
				  ,hats3.transaction_step_id
				  ,'P_ASS_ATTRIBUTE7') ass_attribute7
	  ,xxas_hr_api_transaction_pkg.varchar2_value
				  (hats3.api_name
				  ,hats3.transaction_step_id
				  ,'P_ASS_ATTRIBUTE8') ass_attribute8
	  ,xxas_hr_api_transaction_pkg.varchar2_value
				  (hats3.api_name
				  ,hats3.transaction_step_id
				  ,'P_ASS_ATTRIBUTE9') ass_attribute9
	  ,xxas_hr_api_transaction_pkg.varchar2_value
				  (hats3.api_name
				  ,hats3.transaction_step_id
				  ,'P_ASS_ATTRIBUTE10') ass_attribute10
	  ,xxas_hr_api_transaction_pkg.varchar2_value
				  (hats3.api_name
				  ,hats3.transaction_step_id
				  ,'P_ASS_ATTRIBUTE11') ass_attribute11
	  ,xxas_hr_api_transaction_pkg.varchar2_value
				  (hats3.api_name
				  ,hats3.transaction_step_id
				  ,'P_ASS_ATTRIBUTE12') ass_attribute12
	  ,xxas_hr_api_transaction_pkg.varchar2_value
				  (hats3.api_name
				  ,hats3.transaction_step_id
				  ,'P_ASS_ATTRIBUTE13') ass_attribute13
	  ,xxas_hr_api_transaction_pkg.varchar2_value
				  (hats3.api_name
				  ,hats3.transaction_step_id
				  ,'P_ASS_ATTRIBUTE14') ass_attribute14
	  ,xxas_hr_api_transaction_pkg.varchar2_value
				  (hats3.api_name
				  ,hats3.transaction_step_id
				  ,'P_ASS_ATTRIBUTE15') ass_attribute15
	  ,xxas_hr_api_transaction_pkg.varchar2_value
				  (hats3.api_name
				  ,hats3.transaction_step_id
				  ,'P_ASS_ATTRIBUTE16') ass_attribute16
	  ,xxas_hr_api_transaction_pkg.varchar2_value
				  (hats3.api_name
				  ,hats3.transaction_step_id
				  ,'P_ASS_ATTRIBUTE17') ass_attribute17
	  ,xxas_hr_api_transaction_pkg.varchar2_value
				  (hats3.api_name
				  ,hats3.transaction_step_id
				  ,'P_ASS_ATTRIBUTE18') ass_attribute18
	  ,xxas_hr_api_transaction_pkg.varchar2_value
				  (hats3.api_name
				  ,hats3.transaction_step_id
				  ,'P_ASS_ATTRIBUTE19') ass_attribute19
	  ,xxas_hr_api_transaction_pkg.varchar2_value
				  (hats3.api_name
				  ,hats3.transaction_step_id
				  ,'P_ASS_ATTRIBUTE20') ass_attribute20
	  ,xxas_hr_api_transaction_pkg.number_value
				  (hats3.api_name
				  ,hats3.transaction_step_id
				  ,'P_CAGR_GRADE_DEF_ID') cagr_grade_def_id
	  ,xxas_hr_api_transaction_pkg.number_value
				  (hats3.api_name
				  ,hats3.transaction_step_id
				  ,'P_CAGR_ID_FLEX_NUM') cagr_id_flex_num
	  ,xxas_hr_api_transaction_pkg.varchar2_value
				  (hats3.api_name
				  ,hats3.transaction_step_id
				  ,'P_CHANGE_REASON') change_reason
	  ,xxas_hr_api_transaction_pkg.number_value
				  (hats3.api_name
				  ,hats3.transaction_step_id
				  ,'P_COLLECTIVE_AGREEMENT_ID') collective_agreement_id
	  ,xxas_hr_api_transaction_pkg.number_value
				  (hats3.api_name
				  ,hats3.transaction_step_id
				  ,'P_CONTRACT_ID') contract_id
	  ,xxas_hr_api_transaction_pkg.varchar2_value
				  (hats3.api_name
				  ,hats3.transaction_step_id
				  ,'P_DATETRACK_UPDATE_MODE') datetrack_update_mode
	  ,xxas_hr_api_transaction_pkg.date_value
				  (hats3.api_name
				  ,hats3.transaction_step_id
				  ,'P_DATE_PROBATION_END') date_probation_end
	  ,xxas_hr_api_transaction_pkg.number_value
				  (hats3.api_name
				  ,hats3.transaction_step_id
				  ,'P_DEFAULT_CODE_COMB_ID') default_code_comb_id
	  ,xxas_hr_api_transaction_pkg.varchar2_value
				  (hats3.api_name
				  ,hats3.transaction_step_id
				  ,'P_DISPLAY_ASS_STATUS') display_ass_status
	  ,xxas_hr_api_transaction_pkg.varchar2_value
				  (hats3.api_name
				  ,hats3.transaction_step_id
				  ,'P_DISPLAY_GRADE') display_grade
	  ,xxas_hr_api_transaction_pkg.varchar2_value
				  (hats3.api_name
				  ,hats3.transaction_step_id
				  ,'P_DISPLAY_JOB') display_job
	  ,xxas_hr_api_transaction_pkg.varchar2_value
				  (hats3.api_name
				  ,hats3.transaction_step_id
				  ,'P_DISPLAY_ORG') display_org
	  ,xxas_hr_api_transaction_pkg.varchar2_value
				  (hats3.api_name
				  ,hats3.transaction_step_id
				  ,'P_DISPLAY_POS') display_pos
	  ,xxas_hr_api_transaction_pkg.date_value
				  (hats3.api_name
				  ,hats3.transaction_step_id
				  ,'P_EFFECTIVE_DATE') asg_effective_date
	  ,xxas_hr_api_transaction_pkg.varchar2_value
				  (hats3.api_name
				  ,hats3.transaction_step_id
				  ,'P_EFFECTIVE_DATE_OPTION') effective_date_option
	  ,xxas_hr_api_transaction_pkg.varchar2_value
				  (hats3.api_name
				  ,hats3.transaction_step_id
				  ,'P_ELEMENT_CHANGED') element_changed
	  ,xxas_hr_api_transaction_pkg.number_value
				  (hats3.api_name
				  ,hats3.transaction_step_id
				  ,'P_ESTABLISHMENT_ID') establishment_id
	  ,xxas_hr_api_transaction_pkg.number_value
				  (hats3.api_name
				  ,hats3.transaction_step_id
				  ,'P_GRADE_LADDER_PGM_ID') grade_ladder_pgm_id
	  ,xxas_hr_api_transaction_pkg.varchar2_value
				  (hats3.api_name
				  ,hats3.transaction_step_id
				  ,'P_GRADE_LOV') grade_lov
	  ,xxas_hr_api_transaction_pkg.date_value
				  (hats3.api_name
				  ,hats3.transaction_step_id
				  ,'P_HRS_LAST_DATE') hrs_last_date
	  ,xxas_hr_api_transaction_pkg.varchar2_value
				  (hats3.api_name
				  ,hats3.transaction_step_id
				  ,'P_INTERNAL_ADDRESS_LINE') internal_address_line
	  ,xxas_hr_api_transaction_pkg.number_value
				  (hats3.api_name
				  ,hats3.transaction_step_id
				  ,'P_JOB_ID') job_id
	  ,xxas_hr_api_transaction_pkg.varchar2_value
				  (hats3.api_name
				  ,hats3.transaction_step_id
				  ,'P_JOB_POST_SOURCE_NAME') job_post_source_name
	  ,xxas_hr_api_transaction_pkg.varchar2_value
				  (hats3.api_name
				  ,hats3.transaction_step_id
				  ,'P_J_CHANGED') j_changed
	  ,xxas_hr_api_transaction_pkg.number_value
				  (hats3.api_name
				  ,hats3.transaction_step_id
				  ,'P_LOGIN_PERSON_ID') login_person_id
	  ,xxas_hr_api_transaction_pkg.varchar2_value
				  (hats3.api_name
				  ,hats3.transaction_step_id
				  ,'P_MANAGER_FLAG') manager_flag
	  ,xxas_hr_api_transaction_pkg.number_value
				  (hats3.api_name
				  ,hats3.transaction_step_id
				  ,'P_NOTICE_PERIOD') notice_period
	  ,xxas_hr_api_transaction_pkg.varchar2_value
				  (hats3.api_name
				  ,hats3.transaction_step_id
				  ,'P_NOTICE_PERIOD_UOM') notice_period_uom
	  ,xxas_hr_api_transaction_pkg.number_value
				  (hats3.api_name
				  ,hats3.transaction_step_id
				  ,'P_OBJECT_VERSION_NUMBER') ovn
	  ,xxas_hr_api_transaction_pkg.number_value
				  (hats3.api_name
				  ,hats3.transaction_step_id
				  ,'P_ORGANIZATION_ID') organization_id
	  ,xxas_hr_api_transaction_pkg.number_value
				  (hats3.api_name
				  ,hats3.transaction_step_id
				  ,'P_PAYROLL_ID') payroll_id
	  ,xxas_hr_api_transaction_pkg.number_value
				  (hats3.api_name
				  ,hats3.transaction_step_id
				  ,'P_POSITION_ID') position_id
	  ,xxas_hr_api_transaction_pkg.number_value
				  (hats3.api_name
				  ,hats3.transaction_step_id
				  ,'P_PO_HEADER_ID') po_header_id
	  ,xxas_hr_api_transaction_pkg.number_value
				  (hats3.api_name
				  ,hats3.transaction_step_id
				  ,'P_PO_LINE_ID') po_line_id_p
	  ,xxas_hr_api_transaction_pkg.number_value
				  (hats3.api_name
				  ,hats3.transaction_step_id
				  ,'P_PROBATION_PERIOD') probation_period
	  ,xxas_hr_api_transaction_pkg.varchar2_value
				  (hats3.api_name
				  ,hats3.transaction_step_id
				  ,'P_PROBATION_UNIT') probation_unit
	  ,xxas_hr_api_transaction_pkg.varchar2_value
				  (hats3.api_name
				  ,hats3.transaction_step_id
				  ,'P_PROJECT_TITLE') project_title
	  ,xxas_hr_api_transaction_pkg.date_value
				  (hats3.api_name
				  ,hats3.transaction_step_id
				  ,'P_PROJ_ASGN_END') projected_assignment_end
	  ,xxas_hr_api_transaction_pkg.number_value
				  (hats3.api_name
				  ,hats3.transaction_step_id
				  ,'P_SET_OF_BOOKS_ID') set_of_books_id
	  ,xxas_hr_api_transaction_pkg.varchar2_value
				  (hats3.api_name
				  ,hats3.transaction_step_id
				  ,'P_SOURCE_TYPE') source_type
	  ,xxas_hr_api_transaction_pkg.number_value
				  (hats3.api_name
				  ,hats3.transaction_step_id
				  ,'P_SPECIAL_CEILING_STEP_ID') special_ceiling_step_id
	  ,xxas_hr_api_transaction_pkg.number_value
				  (hats3.api_name
				  ,hats3.transaction_step_id
				  ,'P_SUPERVISOR_ID') supervisor_id
	  ,xxas_hr_api_transaction_pkg.varchar2_value
				  (hats3.api_name
				  ,hats3.transaction_step_id
				  ,'P_VENDOR_ASSIGNMENT_NUMBER') vendor_assignment_number
	  ,xxas_hr_api_transaction_pkg.varchar2_value
				  (hats3.api_name
				  ,hats3.transaction_step_id
				  ,'P_VENDOR_EMPLOYEE_NUMBER') vendor_employee_number
	  ,xxas_hr_api_transaction_pkg.number_value
				  (hats3.api_name
				  ,hats3.transaction_step_id
				  ,'P_VENDOR_SITE_ID') vendor_site_id
	  ,xxas_new_hire_pkg.get_statutory_kff_cols
				  (hats3.soft_coding_keyflex_id
				  ,'SEGMENT1') SEGMENT1
       ,xxas_new_hire_pkg.get_statutory_kff_cols
       				  (hats3.soft_coding_keyflex_id
       				  ,'SEGMENT2') SEGMENT2
       ,xxas_new_hire_pkg.get_statutory_kff_cols
       				  (hats3.soft_coding_keyflex_id
       				  ,'SEGMENT3') SEGMENT3
       ,xxas_new_hire_pkg.get_statutory_kff_cols
       				  (hats3.soft_coding_keyflex_id
       				  ,'SEGMENT4') SEGMENT4
       ,xxas_new_hire_pkg.get_statutory_kff_cols
       				  (hats3.soft_coding_keyflex_id
       				  ,'SEGMENT5') SEGMENT5
       ,xxas_new_hire_pkg.get_statutory_kff_cols
       				  (hats3.soft_coding_keyflex_id
       				  ,'SEGMENT6') SEGMENT6
       ,xxas_new_hire_pkg.get_statutory_kff_cols
       				  (hats3.soft_coding_keyflex_id
       				  ,'SEGMENT7') SEGMENT7
       ,xxas_new_hire_pkg.get_statutory_kff_cols
       				  (hats3.soft_coding_keyflex_id
       				  ,'SEGMENT8') SEGMENT8
       ,xxas_new_hire_pkg.get_statutory_kff_cols
       				  (hats3.soft_coding_keyflex_id
       				  ,'SEGMENT9') SEGMENT9
       ,xxas_new_hire_pkg.get_statutory_kff_cols
       				  (hats3.soft_coding_keyflex_id
       				  ,'SEGMENT10') SEGMENT10
       ,xxas_new_hire_pkg.get_statutory_kff_cols
       				  (hats3.soft_coding_keyflex_id
       				  ,'SEGMENT11') SEGMENT11
       ,xxas_new_hire_pkg.get_statutory_kff_cols
       				  (hats3.soft_coding_keyflex_id
       				  ,'SEGMENT12') SEGMENT12
       ,xxas_new_hire_pkg.get_statutory_kff_cols
       				  (hats3.soft_coding_keyflex_id
       				  ,'SEGMENT13') SEGMENT13
       ,xxas_new_hire_pkg.get_statutory_kff_cols
       				  (hats3.soft_coding_keyflex_id
       				  ,'SEGMENT14') SEGMENT14
       ,xxas_new_hire_pkg.get_statutory_kff_cols
       				  (hats3.soft_coding_keyflex_id
       				  ,'SEGMENT15') SEGMENT15	
       ,xxas_new_hire_pkg.get_statutory_kff_cols
       				  (hats3.soft_coding_keyflex_id
       				  ,'SEGMENT16') SEGMENT16
       ,xxas_new_hire_pkg.get_statutory_kff_cols
       				  (hats3.soft_coding_keyflex_id
       				  ,'SEGMENT17') SEGMENT17
       ,xxas_new_hire_pkg.get_statutory_kff_cols
       				  (hats3.soft_coding_keyflex_id
       				  ,'SEGMENT18') SEGMENT18
       ,xxas_new_hire_pkg.get_statutory_kff_cols
       				  (hats3.soft_coding_keyflex_id
       				  ,'SEGMENT19') SEGMENT19
       ,xxas_new_hire_pkg.get_statutory_kff_cols
       				  (hats3.soft_coding_keyflex_id
       				  ,'SEGMENT20') SEGMENT20
       ,xxas_new_hire_pkg.get_statutory_kff_cols
       				  (hats3.soft_coding_keyflex_id
       				  ,'SEGMENT21') SEGMENT21
       ,xxas_new_hire_pkg.get_statutory_kff_cols
       				  (hats3.soft_coding_keyflex_id
       				  ,'SEGMENT22') SEGMENT22
       ,xxas_new_hire_pkg.get_statutory_kff_cols
       				  (hats3.soft_coding_keyflex_id
       				  ,'SEGMENT23') SEGMENT23
       ,xxas_new_hire_pkg.get_statutory_kff_cols
       				  (hats3.soft_coding_keyflex_id
       				  ,'SEGMENT24') SEGMENT24
       ,xxas_new_hire_pkg.get_statutory_kff_cols
       				  (hats3.soft_coding_keyflex_id
       				  ,'SEGMENT25') SEGMENT25
       ,xxas_new_hire_pkg.get_statutory_kff_cols
       				  (hats3.soft_coding_keyflex_id
       				  ,'SEGMENT26') SEGMENT26
       ,xxas_new_hire_pkg.get_statutory_kff_cols
       				  (hats3.soft_coding_keyflex_id
       				  ,'SEGMENT27') SEGMENT27
       ,xxas_new_hire_pkg.get_statutory_kff_cols
       				  (hats3.soft_coding_keyflex_id
       				  ,'SEGMENT28') SEGMENT28
       ,xxas_new_hire_pkg.get_statutory_kff_cols
       				  (hats3.soft_coding_keyflex_id
       				  ,'SEGMENT29') SEGMENT29
       ,xxas_new_hire_pkg.get_statutory_kff_cols
				  (hats3.soft_coding_keyflex_id
				  ,'SEGMENT30') SEGMENT30
      ,cast(multiset(
               select ph.phone_type,
                      ph.phone_number
               from XMLTable('//value/*'
                        PASSING (select xxas_new_hire_pkg.get_phone_numbers(hats1.transaction_id)
                                 from dual)
                        COLUMNS PHONE_TYPE  VARCHAR2(100)  PATH '/XXAS_PHONE_NUMBERS/PH_TYPE',
                                PHONE_NUMBER VARCHAR2(100)  PATH '/XXAS_PHONE_NUMBERS/PH_NUMBER'
                            ) ph
                    ) as xxas_nh_phone_tbl) PHONE_DETAILS
	  ,cast(multiset(
               select mgr.selected_emp_name,
                      nvl(mgr.manager_name,fnd_global.employee_id),
					  fnd_date.canonical_to_date(mgr.passed_effective_date) 
               from XMLTable('//value/*'
                        PASSING (select xxas_new_hire_pkg.get_new_manager_details(hats1.transaction_id)
                                 from dual)
                        COLUMNS SELECTED_EMP_NAME  VARCHAR2(100)    PATH '/XXAS_NEW_MANAGER/SELECTED_EMP_NAME',
                                MANAGER_NAME VARCHAR2(100)          PATH '/XXAS_NEW_MANAGER/MANAGER_NAME',
								PASSED_EFFECTIVE_DATE VARCHAR2(100) PATH '/XXAS_NEW_MANAGER/PASSED_EFFECTIVE_DATE'
                            ) mgr
                    ) as xxas_nh_mgr_tbl) NEW_MANAGER_DETAILS
	  ,cast(multiset(
               select dr.dr_name,
                      dr.dr_job,
					  fnd_date.canonical_to_date(dr.dr_transfer_date) 
               from XMLTable('//value/*'
                        PASSING (select xxas_new_hire_pkg.get_direct_reports(hats1.transaction_id)
                                 from dual)
                        COLUMNS DR_NAME  VARCHAR2(100)  PATH '/XXAS_DIRECT_REPORTS/DR_NAME',
                                DR_JOB VARCHAR2(100)    PATH '/XXAS_DIRECT_REPORTS/DR_JOB',
								DR_TRANSFER_DATE VARCHAR2(100)   PATH '/XXAS_DIRECT_REPORTS/DR_TRANSFER_DATE'
                            ) dr
                    ) as xxas_nh_dr_tbl) DR_DETAILS
	  ,xxas_new_hire_pkg.get_sal_basis(hats1.transaction_id) salary_basis
	  ,xxas_new_hire_pkg.get_effective_date(hats1.transaction_id) change_pay_date
	  ,xxas_new_hire_pkg.get_proposed_salary(hats1.transaction_id) proposed_salary
	  ,xxas_new_hire_pkg.get_change_amount(hats1.transaction_id) change_amount
	  ,xxas_new_hire_pkg.get_annual_equivalent(hats1.transaction_id) annual_equivalent
	  ,xxas_new_hire_pkg.get_annual_fulltime_eq(hats1.transaction_id) annual_fulltime_eq
	  ,xxas_new_hire_pkg.get_transaction_step_id(hats1.transaction_id,'HR_PROCESS_PHONE_NUMBERS_SS.PROCESS_API') phone_number_step_id
	  ,xxas_new_hire_pkg.get_transaction_step_id(hats1.transaction_id,'PER_SSHR_CHANGE_PAY.PROCESS_API') change_pay_step_id
	  ,xxas_new_hire_pkg.get_transaction_step_id(hats1.transaction_id,'HR_SUPERVISOR_SS.PROCESS_API') supervisor_step_id
	  ,xxas_new_hire_pkg.get_no_of_drs(hats1.transaction_id) no_of_drs
	  ,hats1.transaction_id approvers
	  ,CASE WHEN xxas_new_hire_pkg.get_newhire_workflow_details(hats1.transaction_id) IS NULL THEN 'N' ELSE 'Y' END has_approvers
FROM   
(SELECT transaction_id
       ,transaction_step_id
       ,api_name
 FROM hr_api_transaction_steps 
 WHERE api_name like 'HR_PROCESS_PERSON_SS.PROCESS_API'
) hats1,
(SELECT transaction_id
       ,transaction_step_id
       ,api_name
 FROM hr_api_transaction_steps 
 WHERE api_name like 'HR_PROCESS_ADDRESS_SS.PROCESS_API'
) hats2,
(SELECT transaction_id
       ,transaction_step_id
       ,api_name
	   ,xxas_hr_api_transaction_pkg.number_value
				  (api_name
				  ,transaction_step_id
				  ,'P_SOFT_CODING_KEYFLEX_ID') soft_coding_keyflex_id
 FROM hr_api_transaction_steps 
 WHERE api_name like 'HR_PROCESS_ASSIGNMENT_SS.PROCESS_API'
) hats3
where hats1.transaction_id = hats2.transaction_id
and hats2.transaction_id = hats3.transaction_id;

BEGIN
  xxas_fnd_dictionary_pkg.add_view
   (p_application_short_name => xxas_util_pkg.app
   ,p_view_name              => 'XXAS_NEW_HIRE_REVIEW_V'
   ,p_description            => 'Applaud New Hire Review');
END;
/
  
COMMIT;
EXIT;
