HRS_OPEN_JO_VW

(SQL View)
 Index Back

Job Openings with open dates

This view displays the earliest posting date or the open date if there are no postings for all open job openings. This includes Standard Requisitions only.

SELECT A.HRS_JOB_OPENING_ID , CASE WHEN (((B.HRS_JO_PST_OPN_DT) IS NOT NULL)) THEN (B.HRS_JO_PST_OPN_DT) ELSE A.OPEN_DT END , A.STATUS_CODE , A.HRS_OPENING_LIMIT , A.HRS_JO_TYPE , A.CLOSE_DT , A.STATUS_DT , A.BUSINESS_UNIT , A.SETID_DEPT , A.HRS_PRM_PST_TITLE , A.JOB_FAMILY , A.RECRUITER_ID , A.MANAGER_ID , A.HRS_PRM_JOBCODE , A.DEPTID , A.HRS_PRM_LOC_AREA , A.COMPANY , ' ' , A.HOT_JOB , A.HRS_PRM_POSITION FROM PS_HRS_JOB_OPENING A LEFT OUTER JOIN PS_HRS_JO_PST_DST B ON A.HRS_JOB_OPENING_ID = B.HRS_JOB_OPENING_ID AND B.HRS_JO_PST_OPN_DT IS NOT NULL WHERE (B.HRS_JO_PST_OPN_DT IS NULL OR B.HRS_JO_PST_OPN_DT = ( SELECT MIN(B1.HRS_JO_PST_OPN_DT) FROM PS_HRS_JO_PST_DST B1 WHERE A.HRS_JOB_OPENING_ID = B1.HRS_JOB_OPENING_ID ) AND B.HRS_JO_PST_SEQ = ( SELECT MAX(B2.HRS_JO_PST_SEQ) FROM PS_HRS_JO_PST_DST B2 WHERE B.HRS_JOB_OPENING_ID = B2.HRS_JOB_OPENING_ID AND B.HRS_JO_PST_OPN_DT = B2.HRS_JO_PST_OPN_DT) AND B.HRS_SEQ_NUM = ( SELECT MAX(B3.HRS_SEQ_NUM) FROM PS_HRS_JO_PST_DST B3 WHERE B.HRS_JOB_OPENING_ID = B3.HRS_JOB_OPENING_ID AND B.HRS_JO_PST_OPN_DT = B3.HRS_JO_PST_OPN_DT AND B.HRS_JO_PST_SEQ = B3.HRS_JO_PST_SEQ)) AND A.HRS_JO_TYPE = '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 OPEN_DT Date(10) DATE Created
3 STATUS_CODE Character(3) VARCHAR2(3) NOT NULL Identifies a status.
4 HRS_OPENING_LIMIT Character(1) VARCHAR2(1) NOT NULL Unlimited or limited number of job openings flag
L=Limited Number of Openings
U=Unlimited Number of Openings
5 HRS_JO_TYPE Character(1) VARCHAR2(1) NOT NULL Job Opening Type
P=Continuous Job Opening
R=Standard Requisition
6 CLOSE_DT Date(10) DATE Date Closed
7 STATUS_DT Date(10) DATE Status Date
8 BUSINESS_UNIT Character(5) VARCHAR2(5) NOT NULL Business Unit
9 SETID_DEPT Character(5) VARCHAR2(5) NOT NULL Department Set ID
10 HRS_PRM_PST_TITLE Character(200) VARCHAR2(200) NOT NULL Job posting title
11 JOB_FAMILY Character(6) VARCHAR2(6) NOT NULL Job Family
12 RECRUITER_ID Character(11) VARCHAR2(11) NOT NULL Recruiter ID
13 MANAGER_ID Character(11) VARCHAR2(11) NOT NULL Manager ID
14 HRS_PRM_JOBCODE Character(6) VARCHAR2(6) NOT NULL Primary Job Code of Job Opening.
15 DEPTID Character(10) VARCHAR2(10) NOT NULL Department
16 HRS_PRM_LOC_AREA Number(15,0) DECIMAL(15) NOT NULL Primary recruiting location area
17 COMPANY Character(3) VARCHAR2(3) NOT NULL Company
18 HRS_SOURCE_ID Number(15,0) DECIMAL(15) NOT NULL Source ID
19 HOT_JOB Character(1) VARCHAR2(1) NOT NULL Indicates which Job requisitions are classed as urgent to fill. Hot Jobs are identified during the develop requisition process and used as an indicator for both the organization and applicant.
N=No
Y=Yes
20 HRS_PRM_POSITION Character(8) VARCHAR2(8) NOT NULL Position Number