HRS_PG_JO_OPN_I

(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.

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_LOCATION , A.COMPANY 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_SEQ = 0 AND B.HRS_SEQ_NUM = 0 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 = B1.HRS_JO_PST_SEQ))

# 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_LOCATION Character(10) VARCHAR2(10) NOT NULL Primary Recruiting Location of Job Opening,
17 COMPANY Character(3) VARCHAR2(3) NOT NULL Company