HRS_APPCNTDT_VW

(SQL View)
 Index Back

View populates HRS_APPCNT_STG

This view populates HRS_APPCNT_STG Temp table which is used in APPCONTENT

SELECT A.HRS_PERSON_ID , B.HRS_PROFILE_SEQ , CASE WHEN IDX.HRS_ROW_UPD_DTTM IS NOT NULL AND D.HRS_ROW_UPD_DTTM > B.HRS_ROW_UPD_DTTM AND D.HRS_ROW_UPD_DTTM > IDX.HRS_ROW_UPD_DTTM THEN D.HRS_ROW_UPD_DTTM WHEN IDX.HRS_ROW_UPD_DTTM IS NOT NULL AND IDX.HRS_ROW_UPD_DTTM > B.HRS_ROW_UPD_DTTM AND IDX.HRS_ROW_UPD_DTTM > D.HRS_ROW_UPD_DTTM THEN IDX.HRS_ROW_UPD_DTTM WHEN IDX.HRS_ROW_UPD_DTTM IS NULL AND D.HRS_ROW_UPD_DTTM > B.HRS_ROW_UPD_DTTM THEN D.HRS_ROW_UPD_DTTM ELSE B.HRS_ROW_UPD_DTTM END FROM PS_HRS_APPLICANT A , PS_HRS_APP_PROFILE B LEFT OUTER JOIN PS_HRS_APPINDX_CTL IDX ON (B.HRS_PERSON_ID = IDX.HRS_PERSON_ID AND B.HRS_PROFILE_SEQ = IDX.HRS_PROFILE_SEQ) , PS_HRS_RCMNT D WHERE A.HRS_PERSON_ID = B.HRS_PERSON_ID AND A.HRS_PERSON_ID = D.HRS_PERSON_ID AND D.HRS_RCMNT_ID = ( SELECT MAX(D2.HRS_RCMNT_ID) FROM PS_HRS_RCMNT D2 WHERE D2.HRS_PERSON_ID = D.HRS_PERSON_ID) AND D.HRS_LOGICAL_DELETE = 'N' AND (EXISTS ( SELECT 1 FROM PS_HRS_SRCHIDX_CTL CTL WHERE CTL.HRS_SRCH_INDEX_CD = 'A' AND %DatePart(B.HRS_SUBMITTED_DTTM) >= CTL.HRS_FROM_DATE) OR NOT EXISTS ( SELECT 1 FROM PS_HRS_SRCHIDX_CTL CTL WHERE CTL.HRS_SRCH_INDEX_CD = 'A') ) AND ( A.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') )

# 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