/* $Id$ */
/*============================================================================*
 |                 Copyright (c) 2015 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_dor_v
AS
SELECT 
  'flexible'                                                                "type"
 ,case when (DocsOfRecordEO.verified_by IS NOT NULL 
             or DocsOfRecordEO.transaction_id IS NULL)  THEN
    'tick'
  else
    'timer' 
  end                                                                       "icon"
 ,DocsOfRecordEO.DOCUMENT_EXTRA_INFO_ID                                     "id"
 ,HDT.DOCUMENT_TYPE || ' - ' || HDT.CATEGORY_NAME                           "line1"
 ,CASE WHEN DocsOfRecordEO.date_to IS NOT NULL THEN
    to_char(DocsOfRecordEO.date_from,'fmDD Mon') 
	  || ' - ' || 
	to_char(DocsOfRecordEO.date_to,'fmDD Mon YYYY')
  ELSE
    to_char(DocsOfRecordEO.date_from,'fmDD Mon YYYY')
	  || ' - ' || 
	xxas_com_msg_pkg.get_string('XXAS_ABS_ONGOING')
  END                                                                       "line2"
 ,case when (DocsOfRecordEO.verified_by IS NOT NULL 
             or DocsOfRecordEO.transaction_id IS NULL)  THEN
    'balanced'
  else
    'energized' 
  end                                                                       "statusColor"
 ,case when (DocsOfRecordEO.verified_by IS NOT NULL
             or DocsOfRecordEO.transaction_id IS NULL) THEN
    '<font class=''color-balanced''>' || 
    xxas_com_msg_pkg.get_string('XXAS_DOR_VERIFIED')
	|| '</font>'
  else
    '<font class=''color-energized''>' || 
    xxas_com_msg_pkg.get_string('XXAS_DOR_PEN_VERIFICATION')
	|| '</font>'
  end                                                                       "line3"
  ,'dor-view'                                                               "pageId"
  ,DocsOfRecordEO.DOCUMENT_EXTRA_INFO_ID 
     || '_' || 
	 DocsOfRecordEO.person_id       
     || '_'	||
	 DocsOfRecordEO.TRANSACTION_ID
	 || '_' ||
	 DocsOfRecordEO.TRANSACTION_STEP_ID                                     "objectId"
  ,'modal'                                                                  "target"
 /*,xxas_dor_pkg.dor_item_actions
    (DocsOfRecordEO.document_extra_info_id).get_xml()                       "linked"*/
 ,DocsOfRecordEO.DOCUMENT_EXTRA_INFO_ID,
  DocsOfRecordEO.TRANSACTION_ID,
  DocsOfRecordEO.TRANSACTION_STEP_ID,
  DocsOfRecordEO.PERSON_ID,
  DocsOfRecordEO.DOCUMENT_TYPE_ID,
  DocsOfRecordEO.DOCUMENT_NUMBER,
  DocsOfRecordEO.DATE_FROM,
  DocsOfRecordEO.DATE_TO,
  DocsOfRecordEO.ISSUED_BY,
  DocsOfRecordEO.ISSUED_AT,
  DocsOfRecordEO.ISSUED_DATE,
  DocsOfRecordEO.ISSUING_AUTHORITY,
  DocsOfRecordEO.VERIFIED_BY,
  DocsOfRecordEO.VERIFIED_DATE,
  DocsOfRecordEO.DEI_ATTRIBUTE_CATEGORY,
  DocsOfRecordEO.DEI_ATTRIBUTE1,
  DocsOfRecordEO.DEI_ATTRIBUTE2,
  DocsOfRecordEO.DEI_ATTRIBUTE3,
  DocsOfRecordEO.DEI_ATTRIBUTE4,
  DocsOfRecordEO.DEI_ATTRIBUTE5,
  DocsOfRecordEO.DEI_ATTRIBUTE6,
  DocsOfRecordEO.DEI_ATTRIBUTE7,
  DocsOfRecordEO.DEI_ATTRIBUTE8,
  DocsOfRecordEO.DEI_ATTRIBUTE9,
  DocsOfRecordEO.DEI_ATTRIBUTE10,
  DocsOfRecordEO.DEI_ATTRIBUTE11,
  DocsOfRecordEO.DEI_ATTRIBUTE12,
  DocsOfRecordEO.DEI_ATTRIBUTE13,
  DocsOfRecordEO.DEI_ATTRIBUTE14,
  DocsOfRecordEO.DEI_ATTRIBUTE15,
  DocsOfRecordEO.DEI_ATTRIBUTE16,
  DocsOfRecordEO.DEI_ATTRIBUTE17,
  DocsOfRecordEO.DEI_ATTRIBUTE18,
  DocsOfRecordEO.DEI_ATTRIBUTE19,
  DocsOfRecordEO.DEI_ATTRIBUTE20,
  DocsOfRecordEO.DEI_ATTRIBUTE21,
  DocsOfRecordEO.DEI_ATTRIBUTE22,
  DocsOfRecordEO.DEI_ATTRIBUTE23,
  DocsOfRecordEO.DEI_ATTRIBUTE24,
  DocsOfRecordEO.DEI_ATTRIBUTE25,
  DocsOfRecordEO.DEI_ATTRIBUTE26,
  DocsOfRecordEO.DEI_ATTRIBUTE27,
  DocsOfRecordEO.DEI_ATTRIBUTE28,
  DocsOfRecordEO.DEI_ATTRIBUTE29,
  DocsOfRecordEO.DEI_ATTRIBUTE30,
  DocsOfRecordEO.DEI_INFORMATION_CATEGORY,
  DocsOfRecordEO.DEI_INFORMATION1,
  DocsOfRecordEO.DEI_INFORMATION2,
  DocsOfRecordEO.DEI_INFORMATION3,
  DocsOfRecordEO.DEI_INFORMATION4,
  DocsOfRecordEO.DEI_INFORMATION5,
  DocsOfRecordEO.DEI_INFORMATION6,
  DocsOfRecordEO.DEI_INFORMATION7,
  DocsOfRecordEO.DEI_INFORMATION8,
  DocsOfRecordEO.DEI_INFORMATION9,
  DocsOfRecordEO.DEI_INFORMATION10,
  DocsOfRecordEO.DEI_INFORMATION11,
  DocsOfRecordEO.DEI_INFORMATION12,
  DocsOfRecordEO.DEI_INFORMATION13,
  DocsOfRecordEO.DEI_INFORMATION14,
  DocsOfRecordEO.DEI_INFORMATION15,
  DocsOfRecordEO.DEI_INFORMATION16,
  DocsOfRecordEO.DEI_INFORMATION17,
  DocsOfRecordEO.DEI_INFORMATION18,
  DocsOfRecordEO.DEI_INFORMATION19,
  DocsOfRecordEO.DEI_INFORMATION20,
  DocsOfRecordEO.DEI_INFORMATION21,
  DocsOfRecordEO.DEI_INFORMATION22,
  DocsOfRecordEO.DEI_INFORMATION23,
  DocsOfRecordEO.DEI_INFORMATION24,
  DocsOfRecordEO.DEI_INFORMATION25,
  DocsOfRecordEO.DEI_INFORMATION26,
  DocsOfRecordEO.DEI_INFORMATION27,
  DocsOfRecordEO.DEI_INFORMATION28,
  DocsOfRecordEO.DEI_INFORMATION29,
  DocsOfRecordEO.DEI_INFORMATION30,
  DocsOfRecordEO.OBJECT_VERSION_NUMBER,
  DocsOfRecordEO.LAST_UPDATE_DATE,
  DocsOfRecordEO.LAST_UPDATED_BY,
  DocsOfRecordEO.LAST_UPDATE_LOGIN,
  DocsOfRecordEO.CREATED_BY,
  DocsOfRecordEO.CREATION_DATE,
  DECODE(fnd_profile.value('BEN_DISPLAY_EMPLOYEE_NAME'),'FN',ppf.full_name, ppf.first_name
  ||' '
  || ppf.last_name) FULL_NAME,
  HDT.DOCUMENT_TYPE,
  HRL.MEANING CATEGORY_NAME,
  PST.SHARED_TYPE_NAME SUB_CATEGORY_NAME,
  NVL(FU.USER_NAME, hr_se_utility.get_message('PER','HR_449784_DOR_ACTION')) VERIFIED_BY_USER_NAME,
  AUTHORIZATION_REQUIRED,
  HDT.SYSTEM_DOCUMENT_TYPE,
  HDT.CATEGORY_CODE,
  PPF.EFFECTIVE_START_DATE,
  PPF.EFFECTIVE_END_DATE,
  FTV.TERRITORY_SHORT_NAME,
  FTV.TERRITORY_CODE,
  xxas_dor_pkg.attachments_xml
    (NULL
    ,'R_DOCUMENT_EXTRA_INFO'
	,DocsOfRecordEO.DOCUMENT_EXTRA_INFO_ID
	,DocsOfRecordEO.TRANSACTION_ID) ATTACHMENTS
FROM (select DOCUMENT_EXTRA_INFO_ID,
             PERSON_ID,
			 null TRANSACTION_ID,
			 NULL TRANSACTION_STEP_ID,
             DOCUMENT_TYPE_ID,
             DOCUMENT_NUMBER,
             DATE_FROM,
             DATE_TO,
             ISSUED_BY,
             ISSUED_AT,
             ISSUED_DATE,
             ISSUING_AUTHORITY,
             VERIFIED_BY,
             VERIFIED_DATE,
             DEI_ATTRIBUTE_CATEGORY,
             DEI_ATTRIBUTE1,
             DEI_ATTRIBUTE2,
			 DEI_ATTRIBUTE3,
			 DEI_ATTRIBUTE4,
			 DEI_ATTRIBUTE5,
			 DEI_ATTRIBUTE6,
			 DEI_ATTRIBUTE7,
			 DEI_ATTRIBUTE8,
			 DEI_ATTRIBUTE9,
			 DEI_ATTRIBUTE10,
			 DEI_ATTRIBUTE11,
             DEI_ATTRIBUTE12,
			 DEI_ATTRIBUTE13,
			 DEI_ATTRIBUTE14,
			 DEI_ATTRIBUTE15,
			 DEI_ATTRIBUTE16,
			 DEI_ATTRIBUTE17,
			 DEI_ATTRIBUTE18,
			 DEI_ATTRIBUTE19,
			 DEI_ATTRIBUTE20,
			 DEI_ATTRIBUTE21,
             DEI_ATTRIBUTE22,
			 DEI_ATTRIBUTE23,
			 DEI_ATTRIBUTE24,
			 DEI_ATTRIBUTE25,
			 DEI_ATTRIBUTE26,
			 DEI_ATTRIBUTE27,
			 DEI_ATTRIBUTE28,
			 DEI_ATTRIBUTE29,
			 DEI_ATTRIBUTE30,
             DEI_INFORMATION_CATEGORY,
             DEI_INFORMATION1,
			 DEI_INFORMATION2,
			 DEI_INFORMATION3,
			 DEI_INFORMATION4,
			 DEI_INFORMATION5,
			 DEI_INFORMATION6,
			 DEI_INFORMATION7,
			 DEI_INFORMATION8,
			 DEI_INFORMATION9,
			 DEI_INFORMATION10,
			 DEI_INFORMATION11,
             DEI_INFORMATION12,
			 DEI_INFORMATION13,
			 DEI_INFORMATION14,
			 DEI_INFORMATION15,
			 DEI_INFORMATION16,
			 DEI_INFORMATION17,
			 DEI_INFORMATION18,
			 DEI_INFORMATION19,
			 DEI_INFORMATION20,
			 DEI_INFORMATION21,
             DEI_INFORMATION22,
			 DEI_INFORMATION23,
			 DEI_INFORMATION24,
			 DEI_INFORMATION25,
			 DEI_INFORMATION26,
			 DEI_INFORMATION27,
			 DEI_INFORMATION28,
			 DEI_INFORMATION29,
			 DEI_INFORMATION30,
             OBJECT_VERSION_NUMBER,
             LAST_UPDATE_DATE,
             LAST_UPDATED_BY,
             LAST_UPDATE_LOGIN,
             CREATED_BY,
             CREATION_DATE
      FROM 
	  HR_DOCUMENT_EXTRA_INFO DOR
	  WHERE NOT EXISTS(SELECT '1'
                       FROM   hr_api_transaction_values hatv3
                             ,hr_api_transaction_steps hats
                             ,hr_api_transactions hat
                       WHERE hatv3.name              = 'P_DOCUMENT_EXTRA_INFO_ID'
                       AND hatv3.transaction_step_id = hats.transaction_step_id
                       AND hats.transaction_id       = hat.transaction_id
                       AND hat.SELECTED_PERSON_ID    = DOR.PERSON_ID
                       AND hat.transaction_ref_table = 'HR_DOCUMENT_EXTRA_INFO'
                       AND hat.status NOT           IN ('D', 'E', 'W', 'AC')
                       AND hatv3.number_value        = DOR.DOCUMENT_EXTRA_INFO_ID
					  )
	  UNION
	  SELECT xxas_hr_api_transaction_pkg.number_value
                      (tran.selected_person_id
                      ,steps.api_name
                      ,steps.transaction_step_id
                      ,'P_DOCUMENT_EXTRA_INFO_ID')  DOCUMENT_EXTRA_INFO_ID,
	         tran.selected_person_id PERSON_ID,
			 tran.transaction_id,
			 steps.transaction_step_id,
	         xxas_hr_api_transaction_pkg.number_value
                      (tran.selected_person_id
                      ,steps.api_name
                      ,steps.transaction_step_id
                      ,'P_DOCUMENT_TYPE_ID') DOCUMENT_TYPE_ID,
	         xxas_hr_api_transaction_pkg.varchar2_value
                      (tran.selected_person_id
                      ,steps.api_name
                      ,steps.transaction_step_id
                      ,'P_DOCUMENT_NUMBER') DOCUMENT_NUMBER,
	         xxas_hr_api_transaction_pkg.date_value
                      (tran.selected_person_id
                      ,steps.api_name
                      ,steps.transaction_step_id
                      ,'P_DATE_FROM') DATE_FROM,
	         xxas_hr_api_transaction_pkg.date_value
                      (tran.selected_person_id
                      ,steps.api_name
                      ,steps.transaction_step_id
                      ,'P_DATE_TO') DATE_TO,
	         xxas_hr_api_transaction_pkg.varchar2_value
                      (tran.selected_person_id
                      ,steps.api_name
                      ,steps.transaction_step_id
                      ,'P_ISSUED_BY') ISSUED_BY,
	         xxas_hr_api_transaction_pkg.varchar2_value
                      (tran.selected_person_id
                      ,steps.api_name
                      ,steps.transaction_step_id
                      ,'P_ISSUED_AT') ISSUED_AT,
	         xxas_hr_api_transaction_pkg.date_value
                      (tran.selected_person_id
                      ,steps.api_name
                      ,steps.transaction_step_id
                      ,'P_ISSUED_DATE') ISSUED_DATE,
	         xxas_hr_api_transaction_pkg.varchar2_value
                      (tran.selected_person_id
                      ,steps.api_name
                      ,steps.transaction_step_id
                      ,'P_ISSUING_AUTHORITY') ISSUING_AUTHORITY,
	         NULL VERIFIED_BY,
	         NULL VERIFIED_DATE,
	         xxas_hr_api_transaction_pkg.varchar2_value
                      (tran.selected_person_id
                      ,steps.api_name
                      ,steps.transaction_step_id
                      ,'P_DEI_ATTRIBUTE_CATEGORY') DEI_ATTRIBUTE_CATEGORY,
	         xxas_hr_api_transaction_pkg.varchar2_value
                      (tran.selected_person_id
                      ,steps.api_name
                      ,steps.transaction_step_id
                      ,'P_DEI_ATTRIBUTE1') DEI_ATTRIBUTE1,
		    xxas_hr_api_transaction_pkg.varchar2_value
                      (tran.selected_person_id
                      ,steps.api_name
                      ,steps.transaction_step_id
                      ,'P_DEI_ATTRIBUTE2') DEI_ATTRIBUTE2,
			xxas_hr_api_transaction_pkg.varchar2_value
                      (tran.selected_person_id
                      ,steps.api_name
                      ,steps.transaction_step_id
                      ,'P_DEI_ATTRIBUTE3') DEI_ATTRIBUTE3,
			xxas_hr_api_transaction_pkg.varchar2_value
                      (tran.selected_person_id
                      ,steps.api_name
                      ,steps.transaction_step_id
                      ,'P_DEI_ATTRIBUTE4') DEI_ATTRIBUTE4,
			xxas_hr_api_transaction_pkg.varchar2_value
                      (tran.selected_person_id
                      ,steps.api_name
                      ,steps.transaction_step_id
                      ,'P_DEI_ATTRIBUTE5') DEI_ATTRIBUTE5,
			xxas_hr_api_transaction_pkg.varchar2_value
                      (tran.selected_person_id
                      ,steps.api_name
                      ,steps.transaction_step_id
                      ,'P_DEI_ATTRIBUTE6') DEI_ATTRIBUTE6,
		     xxas_hr_api_transaction_pkg.varchar2_value
                      (tran.selected_person_id
                      ,steps.api_name
                      ,steps.transaction_step_id
                      ,'P_DEI_ATTRIBUTE7') DEI_ATTRIBUTE7,
			xxas_hr_api_transaction_pkg.varchar2_value
                      (tran.selected_person_id
                      ,steps.api_name
                      ,steps.transaction_step_id
                      ,'P_DEI_ATTRIBUTE8') DEI_ATTRIBUTE8,
			xxas_hr_api_transaction_pkg.varchar2_value
                      (tran.selected_person_id
                      ,steps.api_name
                      ,steps.transaction_step_id
                      ,'P_DEI_ATTRIBUTE9') DEI_ATTRIBUTE9,
			xxas_hr_api_transaction_pkg.varchar2_value
                      (tran.selected_person_id
                      ,steps.api_name
                      ,steps.transaction_step_id
                      ,'P_DEI_ATTRIBUTE10') DEI_ATTRIBUTE10,		
            xxas_hr_api_transaction_pkg.varchar2_value
                      (tran.selected_person_id
                      ,steps.api_name
                      ,steps.transaction_step_id
                      ,'P_DEI_ATTRIBUTE11') DEI_ATTRIBUTE11,
		     xxas_hr_api_transaction_pkg.varchar2_value
                      (tran.selected_person_id
                      ,steps.api_name
                      ,steps.transaction_step_id
                      ,'P_DEI_ATTRIBUTE12') DEI_ATTRIBUTE12,
			xxas_hr_api_transaction_pkg.varchar2_value
                      (tran.selected_person_id
                      ,steps.api_name
                      ,steps.transaction_step_id
                      ,'P_DEI_ATTRIBUTE13') DEI_ATTRIBUTE13,
			xxas_hr_api_transaction_pkg.varchar2_value
                      (tran.selected_person_id
                      ,steps.api_name
                      ,steps.transaction_step_id
                      ,'P_DEI_ATTRIBUTE14') DEI_ATTRIBUTE14,
			xxas_hr_api_transaction_pkg.varchar2_value
                      (tran.selected_person_id
                      ,steps.api_name
                      ,steps.transaction_step_id
                      ,'P_DEI_ATTRIBUTE15') DEI_ATTRIBUTE15,
			xxas_hr_api_transaction_pkg.varchar2_value
                      (tran.selected_person_id
                      ,steps.api_name
                      ,steps.transaction_step_id
                      ,'P_DEI_ATTRIBUTE16') DEI_ATTRIBUTE16,
		     xxas_hr_api_transaction_pkg.varchar2_value
                      (tran.selected_person_id
                      ,steps.api_name
                      ,steps.transaction_step_id
                      ,'P_DEI_ATTRIBUTE17') DEI_ATTRIBUTE17,
			xxas_hr_api_transaction_pkg.varchar2_value
                      (tran.selected_person_id
                      ,steps.api_name
                      ,steps.transaction_step_id
                      ,'P_DEI_ATTRIBUTE18') DEI_ATTRIBUTE18,
			xxas_hr_api_transaction_pkg.varchar2_value
                      (tran.selected_person_id
                      ,steps.api_name
                      ,steps.transaction_step_id
                      ,'P_DEI_ATTRIBUTE19') DEI_ATTRIBUTE19,
			xxas_hr_api_transaction_pkg.varchar2_value
                      (tran.selected_person_id
                      ,steps.api_name
                      ,steps.transaction_step_id
                      ,'P_DEI_ATTRIBUTE20') DEI_ATTRIBUTE20,
            xxas_hr_api_transaction_pkg.varchar2_value
                      (tran.selected_person_id
                      ,steps.api_name
                      ,steps.transaction_step_id
                      ,'P_DEI_ATTRIBUTE21') DEI_ATTRIBUTE21,
		     xxas_hr_api_transaction_pkg.varchar2_value
                      (tran.selected_person_id
                      ,steps.api_name
                      ,steps.transaction_step_id
                      ,'P_DEI_ATTRIBUTE22') DEI_ATTRIBUTE22,
			xxas_hr_api_transaction_pkg.varchar2_value
                      (tran.selected_person_id
                      ,steps.api_name
                      ,steps.transaction_step_id
                      ,'P_DEI_ATTRIBUTE23') DEI_ATTRIBUTE23,
			xxas_hr_api_transaction_pkg.varchar2_value
                      (tran.selected_person_id
                      ,steps.api_name
                      ,steps.transaction_step_id
                      ,'P_DEI_ATTRIBUTE24') DEI_ATTRIBUTE24,
			xxas_hr_api_transaction_pkg.varchar2_value
                      (tran.selected_person_id
                      ,steps.api_name
                      ,steps.transaction_step_id
                      ,'P_DEI_ATTRIBUTE25') DEI_ATTRIBUTE25,
			xxas_hr_api_transaction_pkg.varchar2_value
                      (tran.selected_person_id
                      ,steps.api_name
                      ,steps.transaction_step_id
                      ,'P_DEI_ATTRIBUTE26') DEI_ATTRIBUTE26,
		     xxas_hr_api_transaction_pkg.varchar2_value
                      (tran.selected_person_id
                      ,steps.api_name
                      ,steps.transaction_step_id
                      ,'P_DEI_ATTRIBUTE27') DEI_ATTRIBUTE27,
			xxas_hr_api_transaction_pkg.varchar2_value
                      (tran.selected_person_id
                      ,steps.api_name
                      ,steps.transaction_step_id
                      ,'P_DEI_ATTRIBUTE28') DEI_ATTRIBUTE28,
			xxas_hr_api_transaction_pkg.varchar2_value
                      (tran.selected_person_id
                      ,steps.api_name
                      ,steps.transaction_step_id
                      ,'P_DEI_ATTRIBUTE29') DEI_ATTRIBUTE29,
			xxas_hr_api_transaction_pkg.varchar2_value
                      (tran.selected_person_id
                      ,steps.api_name
                      ,steps.transaction_step_id
                      ,'P_DEI_ATTRIBUTE30') DEI_ATTRIBUTE30,					  
	         xxas_hr_api_transaction_pkg.varchar2_value
                      (tran.selected_person_id
                      ,steps.api_name
                      ,steps.transaction_step_id
                      ,'P_DEI_INFORMATION_CATEGORY') DEI_INFORMATION_CATEGORY,
	         xxas_hr_api_transaction_pkg.varchar2_value
                      (tran.selected_person_id
                      ,steps.api_name
                      ,steps.transaction_step_id
                      ,'P_DEI_INFORMATION1') DEI_INFORMATION1,
		    xxas_hr_api_transaction_pkg.varchar2_value
                      (tran.selected_person_id
                      ,steps.api_name
                      ,steps.transaction_step_id
                      ,'P_DEI_INFORMATION2') DEI_INFORMATION2,
			xxas_hr_api_transaction_pkg.varchar2_value
                      (tran.selected_person_id
                      ,steps.api_name
                      ,steps.transaction_step_id
                      ,'P_DEI_INFORMATION3') DEI_INFORMATION3,
			xxas_hr_api_transaction_pkg.varchar2_value
                      (tran.selected_person_id
                      ,steps.api_name
                      ,steps.transaction_step_id
                      ,'P_DEI_INFORMATION4') DEI_INFORMATION4,
			xxas_hr_api_transaction_pkg.varchar2_value
                      (tran.selected_person_id
                      ,steps.api_name
                      ,steps.transaction_step_id
                      ,'P_DEI_INFORMATION5') DEI_INFORMATION5,
			xxas_hr_api_transaction_pkg.varchar2_value
                      (tran.selected_person_id
                      ,steps.api_name
                      ,steps.transaction_step_id
                      ,'P_DEI_INFORMATION6') DEI_INFORMATION6,
		     xxas_hr_api_transaction_pkg.varchar2_value
                      (tran.selected_person_id
                      ,steps.api_name
                      ,steps.transaction_step_id
                      ,'P_DEI_INFORMATION7') DEI_INFORMATION7,
			xxas_hr_api_transaction_pkg.varchar2_value
                      (tran.selected_person_id
                      ,steps.api_name
                      ,steps.transaction_step_id
                      ,'P_DEI_INFORMATION8') DEI_INFORMATION8,
			xxas_hr_api_transaction_pkg.varchar2_value
                      (tran.selected_person_id
                      ,steps.api_name
                      ,steps.transaction_step_id
                      ,'P_DEI_INFORMATION9') DEI_INFORMATION9,
			xxas_hr_api_transaction_pkg.varchar2_value
                      (tran.selected_person_id
                      ,steps.api_name
                      ,steps.transaction_step_id
                      ,'P_DEI_INFORMATION10') DEI_INFORMATION10,		
            xxas_hr_api_transaction_pkg.varchar2_value
                      (tran.selected_person_id
                      ,steps.api_name
                      ,steps.transaction_step_id
                      ,'P_DEI_INFORMATION11') DEI_INFORMATION11,
		     xxas_hr_api_transaction_pkg.varchar2_value
                      (tran.selected_person_id
                      ,steps.api_name
                      ,steps.transaction_step_id
                      ,'P_DEI_INFORMATION12') DEI_INFORMATION12,
			xxas_hr_api_transaction_pkg.varchar2_value
                      (tran.selected_person_id
                      ,steps.api_name
                      ,steps.transaction_step_id
                      ,'P_DEI_INFORMATION13') DEI_INFORMATION13,
			xxas_hr_api_transaction_pkg.varchar2_value
                      (tran.selected_person_id
                      ,steps.api_name
                      ,steps.transaction_step_id
                      ,'P_DEI_INFORMATION14') DEI_INFORMATION14,
			xxas_hr_api_transaction_pkg.varchar2_value
                      (tran.selected_person_id
                      ,steps.api_name
                      ,steps.transaction_step_id
                      ,'P_DEI_INFORMATION15') DEI_INFORMATION15,
			xxas_hr_api_transaction_pkg.varchar2_value
                      (tran.selected_person_id
                      ,steps.api_name
                      ,steps.transaction_step_id
                      ,'P_DEI_INFORMATION16') DEI_INFORMATION16,
		     xxas_hr_api_transaction_pkg.varchar2_value
                      (tran.selected_person_id
                      ,steps.api_name
                      ,steps.transaction_step_id
                      ,'P_DEI_INFORMATION17') DEI_INFORMATION17,
			xxas_hr_api_transaction_pkg.varchar2_value
                      (tran.selected_person_id
                      ,steps.api_name
                      ,steps.transaction_step_id
                      ,'P_DEI_INFORMATION18') DEI_INFORMATION18,
			xxas_hr_api_transaction_pkg.varchar2_value
                      (tran.selected_person_id
                      ,steps.api_name
                      ,steps.transaction_step_id
                      ,'P_DEI_INFORMATION19') DEI_INFORMATION19,
			xxas_hr_api_transaction_pkg.varchar2_value
                      (tran.selected_person_id
                      ,steps.api_name
                      ,steps.transaction_step_id
                      ,'P_DEI_INFORMATION20') DEI_INFORMATION20,
            xxas_hr_api_transaction_pkg.varchar2_value
                      (tran.selected_person_id
                      ,steps.api_name
                      ,steps.transaction_step_id
                      ,'P_DEI_INFORMATION21') DEI_INFORMATION21,
		     xxas_hr_api_transaction_pkg.varchar2_value
                      (tran.selected_person_id
                      ,steps.api_name
                      ,steps.transaction_step_id
                      ,'P_DEI_INFORMATION22') DEI_INFORMATION22,
			xxas_hr_api_transaction_pkg.varchar2_value
                      (tran.selected_person_id
                      ,steps.api_name
                      ,steps.transaction_step_id
                      ,'P_DEI_INFORMATION23') DEI_INFORMATION23,
			xxas_hr_api_transaction_pkg.varchar2_value
                      (tran.selected_person_id
                      ,steps.api_name
                      ,steps.transaction_step_id
                      ,'P_DEI_INFORMATION24') DEI_INFORMATION24,
			xxas_hr_api_transaction_pkg.varchar2_value
                      (tran.selected_person_id
                      ,steps.api_name
                      ,steps.transaction_step_id
                      ,'P_DEI_INFORMATION25') DEI_INFORMATION25,
			xxas_hr_api_transaction_pkg.varchar2_value
                      (tran.selected_person_id
                      ,steps.api_name
                      ,steps.transaction_step_id
                      ,'P_DEI_INFORMATION26') DEI_INFORMATION26,
		     xxas_hr_api_transaction_pkg.varchar2_value
                      (tran.selected_person_id
                      ,steps.api_name
                      ,steps.transaction_step_id
                      ,'P_DEI_INFORMATION27') DEI_INFORMATION27,
			xxas_hr_api_transaction_pkg.varchar2_value
                      (tran.selected_person_id
                      ,steps.api_name
                      ,steps.transaction_step_id
                      ,'P_DEI_INFORMATION28') DEI_INFORMATION28,
			xxas_hr_api_transaction_pkg.varchar2_value
                      (tran.selected_person_id
                      ,steps.api_name
                      ,steps.transaction_step_id
                      ,'P_DEI_INFORMATION29') DEI_INFORMATION29,
			xxas_hr_api_transaction_pkg.varchar2_value
                      (tran.selected_person_id
                      ,steps.api_name
                      ,steps.transaction_step_id
                      ,'P_DEI_INFORMATION30') DEI_INFORMATION30,
	         xxas_hr_api_transaction_pkg.number_value
                      (tran.selected_person_id
                      ,steps.api_name
                      ,steps.transaction_step_id
                      ,'P_OBJECT_VERSION_NUMBER') OBJECT_VERSION_NUMBER,
	         tran.LAST_UPDATE_DATE  LAST_UPDATE_DATE,
	         tran.LAST_UPDATED_BY  LAST_UPDATED_BY,
	         tran.LAST_UPDATE_LOGIN LAST_UPDATE_LOGIN,
	         tran.CREATED_BY   CREATED_BY,
	         tran.creation_date   CREATION_DATE
	  FROM   hr_api_transactions tran
	        ,hr_api_transaction_steps steps
	  WHERE  tran.transaction_ref_table = 'HR_DOCUMENT_EXTRA_INFO'
	  AND    tran.transaction_id = steps.transaction_id
	  --AND    tran.api_addtnl_info = 'DOR_INSERT'
	  AND    steps.api_name = 'HR_DOR_REVIEW_SS.PROCESS_API'
	  AND    tran.status NOT IN ('D', 'E', 'W','AC')
	  ) DocsOfRecordEO,
  FND_USER FU,
  PER_SHARED_TYPES_VL PST,
  HR_LOOKUPS HRL,
  HR_DOCUMENT_TYPES_V HDT,
  PER_PEOPLE_F PPF,
  FND_TERRITORIES_VL FTV
WHERE FU.USER_ID (+)         = DocsOfRecordEO.VERIFIED_BY
AND PST.LOOKUP_TYPE (+)      = 'DOCUMENT_CATEGORY'
AND PST.SHARED_TYPE_CODE (+) = HDT.SUB_CATEGORY_CODE
AND PST.SYSTEM_TYPE_CD (+)   = HDT.CATEGORY_CODE
AND HRL.LOOKUP_TYPE          = 'DOCUMENT_CATEGORY'
AND HRL.LOOKUP_CODE          = HDT.CATEGORY_CODE
AND HDT.DOCUMENT_TYPE_ID     = DocsOfRecordEO.DOCUMENT_TYPE_ID
AND PPF.PERSON_ID            = DocsOfRecordEO.PERSON_ID
AND HDT.LEGISLATION_CODE     = FTV.TERRITORY_CODE(+)
and sysdate between effective_start_date and effective_end_date
ORDER BY DocsOfRecordEO.last_update_date desc;   
BEGIN
  xxas_fnd_dictionary_pkg.add_view
   (p_application_short_name => xxas_util_pkg.app
   ,p_view_name              => 'XXAS_DOR_V'
   ,p_description            => 'Applaud Documents of record view');
END;
/

COMMIT;
EXIT;

