HRS_JO_POST_VW

(SQL View)
 Index Back

Filled Closed Job Openings

This view returns the number of days to fill/close a job opening based on the earliest posting date. If there is no posting, the Open Date is used instead.

SELECT A.HRS_JOB_OPENING_ID , A.STATUS_DT , TM.EMPLID , MAX(%DateDiff(C.HRS_JO_PST_OPN_DT, A.CLOSE_DT)) FROM PS_HRS_JOB_OPENING A , PS_HRS_JO_TEAM TM , PS_HRS_JO_POSTING B , PS_HRS_JO_PST_DST C WHERE TM.HRS_JOB_OPENING_ID = A.HRS_JOB_OPENING_ID AND B.HRS_JOB_OPENING_ID = A.HRS_JOB_OPENING_ID AND C.HRS_JOB_OPENING_ID = B.HRS_JOB_OPENING_ID AND C.HRS_JO_PST_SEQ = B.HRS_JO_PST_SEQ AND A.STATUS_CODE = ( SELECT D.STATUS_CODE FROM PS_HRS_STS_TBL D WHERE D.STATUS_AREA = '1' AND D.COMPLETE_STS_IND = 'Y') AND A.CLOSE_DT IS NOT NULL AND A.HRS_OPENING_LIMIT = 'L' AND A.HRS_JO_TYPE = 'R' AND TM.HRS_JO_TM_TYPE IN ('M', 'R') AND C.HRS_JO_PST_OPN_DT IS NOT NULL GROUP BY A.HRS_JOB_OPENING_ID , A.STATUS_DT, TM.EMPLID UNION SELECT A.HRS_JOB_OPENING_ID , A.STATUS_DT , TM.EMPLID , %DateDiff(A.OPEN_DT, A.CLOSE_DT) FROM PS_HRS_JOB_OPENING A , PS_HRS_JO_TEAM TM WHERE TM.HRS_JOB_OPENING_ID = A.HRS_JOB_OPENING_ID AND A.STATUS_CODE = ( SELECT D.STATUS_CODE FROM PS_HRS_STS_TBL D WHERE D.STATUS_AREA = '1' AND D.COMPLETE_STS_IND = 'Y') AND NOT EXISTS ( SELECT 'X' FROM PS_HRS_JO_POSTING B , PS_HRS_JO_PST_DST C WHERE B.HRS_JOB_OPENING_ID = A.HRS_JOB_OPENING_ID AND C.HRS_JOB_OPENING_ID = B.HRS_JOB_OPENING_ID AND C.HRS_JO_PST_SEQ = B.HRS_JO_PST_SEQ AND C.HRS_JO_PST_OPN_DT IS NOT NULL) AND A.CLOSE_DT IS NOT NULL AND A.HRS_OPENING_LIMIT = 'L' AND A.HRS_JO_TYPE = 'R' AND TM.HRS_JO_TM_TYPE IN ('M', 'R')

# PeopleSoft Field Name PeopleSoft Field Type Database Column Type Description
1 HRS_JOB_OPENING_ID Number(15,0) DECIMAL(15) NOT NULL Job Opening Clone
2 STATUS_DT Date(10) DATE Status Date
3 EMPLID Character(11) VARCHAR2(11) NOT NULL Employee ID
4 HRS_DAYS Signed Number(6,0) DECIMAL(5) NOT NULL Days