HRS_APPIDXDT_VW

(SQL View)
 Index Back

View populates HRS_APPIDXDT_TM

This view populates HRS_APPIDXDT_TM Temp table which is used in APPINDEX pre-processing AE

SELECT APP.HRS_PERSON_ID , PROF.HRS_PROFILE_SEQ , %DateTimeOut(CASE WHEN AI.HRS_ROW_UPD_DTTM IS NOT NULL AND R.HRS_ROW_UPD_DTTM >= PROF.HRS_ROW_UPD_DTTM AND R.HRS_ROW_UPD_DTTM >= AI.HRS_ROW_UPD_DTTM THEN R.HRS_ROW_UPD_DTTM WHEN AI.HRS_ROW_UPD_DTTM IS NOT NULL AND AI.HRS_ROW_UPD_DTTM >= PROF.HRS_ROW_UPD_DTTM AND AI.HRS_ROW_UPD_DTTM >= R.HRS_ROW_UPD_DTTM THEN AI.HRS_ROW_UPD_DTTM WHEN AI.HRS_ROW_UPD_DTTM IS NULL AND R.HRS_ROW_UPD_DTTM > PROF.HRS_ROW_UPD_DTTM THEN R.HRS_ROW_UPD_DTTM ELSE PROF.HRS_ROW_UPD_DTTM END) FROM PS_HRS_APPLICANT APP , PS_HRS_APP_PROFILE PROF LEFT OUTER JOIN PS_HRS_APPINDX_CTL AI ON (PROF.HRS_PERSON_ID = AI.HRS_PERSON_ID AND PROF.HRS_PROFILE_SEQ = AI.HRS_PROFILE_SEQ) , PS_HRS_RCMNT R WHERE APP.HRS_PERSON_ID = PROF.HRS_PERSON_ID AND R.HRS_PROFILE_SEQ = PROF.HRS_PROFILE_SEQ AND (APP.STATUS_CODE IN ( SELECT STATUS_CODE FROM PS_HRS_SRCHIDX_APS APS WHERE APS.HRS_SRCH_INDEX_CD = 'A') OR NOT EXISTS ( SELECT 1 FROM PS_HRS_SRCHIDX_APS APS WHERE APS.HRS_SRCH_INDEX_CD = 'A')) AND APP.HRS_PERSON_ID = R.HRS_PERSON_ID AND R.HRS_RCMNT_ID = ( SELECT MAX(R2.HRS_RCMNT_ID) FROM PS_HRS_RCMNT R2 WHERE R2.HRS_PERSON_ID = R.HRS_PERSON_ID AND R.HRS_PROFILE_SEQ = R2.HRS_PROFILE_SEQ) AND R.HRS_LOGICAL_DELETE = 'N' AND (PROF.HRS_SUBMITTED_DTTM) >= ( SELECT %Dttm(HRS_FROM_DATE,NULL) FROM PS_HRS_APPIDX_DT_V)

# PeopleSoft Field Name PeopleSoft Field Type Database Column Type Description
1 HRS_PERSON_ID Number(15,0) DECIMAL(15) NOT NULL Person Id
2 HRS_PROFILE_SEQ Number(3,0) SMALLINT NOT NULL Profile sequece
3 HRS_ROW_UPD_DTTM DateTime(26) TIMESTAMP Row Updated DateTime