HRS_JOBPSTNG_VW

(SQL View)
 Index Back

Ext and Int Job Openings

Job openings with postings that are available for external and internal applicants. Used to build Job Content search index.

SELECT A.HRS_JOB_OPENING_ID , B.HRS_JO_PST_SEQ , C.HRS_JO_PST_TYPE , D.HRS_SOURCE_ID , D.HRS_SOURCE_TYPE , C.HRS_JO_PST_TYPE , B.POSTING_TITLE , B.HRS_JO_PRM_PST_FLG , C.HRS_JO_PST_OPN_DT , C.HRS_JO_PST_CLS_DT , A.HRS_JO_TYPE , A.HRS_PRM_JOBCODE , A.HRS_PRM_POSITION , A.STATUS_CODE , A.BUSINESS_UNIT , A.COMPANY , A.DEPTID , A.HRS_PRM_LOCATION , A.HRS_PRM_LOC_AREA , A.HOT_JOB , A.ORIGINATOR_ID , A.MANAGER_ID , A.RECRUITER_ID , CASE WHEN IDX.HRS_ROW_UPD_DTTM IS NOT NULL AND IDX.HRS_ROW_UPD_DTTM > B.HRS_ROW_UPD_DTTM THEN IDX.HRS_ROW_UPD_DTTM ELSE B.HRS_ROW_UPD_DTTM END FROM PS_HRS_JOB_OPENING A , PS_HRS_JO_POSTING B LEFT OUTER JOIN PS_HRS_JOBINDX_CTL IDX ON (B.HRS_JOB_OPENING_ID = IDX.HRS_JOB_OPENING_ID) , PS_HRS_JO_PST_DST C , PS_HRS_SOURCE D , PS_HRS_STS_JO_I E WHERE A.HRS_JOB_OPENING_ID = B.HRS_JOB_OPENING_ID AND A.HRS_JOB_OPENING_ID = C.HRS_JOB_OPENING_ID AND B.HRS_JO_PST_SEQ = C.HRS_JO_PST_SEQ AND (C.HRS_JO_PST_TYPE = 'E' OR C.HRS_JO_PST_TYPE = 'I') AND (C.HRS_JO_PST_CLS_DT >= %CurrentDateIn OR C.HRS_JO_PST_CLS_DT IS NULL) AND (EXISTS ( SELECT 1 FROM PS_HRS_SRCHIDX_CTL CTL WHERE CTL.HRS_SRCH_INDEX_CD = 'J' AND C.HRS_JO_PST_OPN_DT >= CTL.HRS_FROM_DATE) OR NOT EXISTS ( SELECT 1 FROM PS_HRS_SRCHIDX_CTL CTL WHERE CTL.HRS_SRCH_INDEX_CD = 'J' AND C.HRS_JO_PST_OPN_DT <= %CurrentDateIn )) AND C.HRS_SOURCE_ID = D.HRS_SOURCE_ID AND D.HRS_SOURCE_TYPE = '40' AND A.STATUS_CODE = E.STATUS_CODE AND E.OPEN_STS_IND = 'Y' AND D.EFFDT = ( SELECT MAX(X.EFFDT) FROM PS_HRS_SOURCE X WHERE X.HRS_SOURCE_ID = D.HRS_SOURCE_ID AND X.EFFDT <= %CurrentDateIn AND HRS_SOURCE_STATUS = '10')

# 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 HRS_JO_PST_SEQ Number(3,0) SMALLINT NOT NULL Job opening posting sequence
3 HRS_JO_PST_TYPE Character(1) VARCHAR2(1) NOT NULL Job Opening Posting Type
C=Contingent Workforce
E=External Posting
I=Internal Posting
L=Labor Office
R=Recruitment
4 HRS_SOURCE_ID Number(15,0) DECIMAL(15) NOT NULL Source ID
5 HRS_SOURCE_TYPE Character(3) VARCHAR2(3) NOT NULL Recruiting Source Type
10=Vendor
20=Marketing
30=Employee
40=Company
50=D-List
6 HRS_JO_PST_FLG Character(1) VARCHAR2(1) NOT NULL Flag to determine that this is to be used for a job opening of posting only type.
7 POSTING_TITLE Character(200) VARCHAR2(200) NOT NULL Job posting title
8 HRS_JO_PRM_PST_FLG Character(1) VARCHAR2(1) NOT NULL Primary posting title flag

Y/N Table Edit

Default Value: N

9 OPEN_DT Date(10) DATE Created
10 CLOSE_DT Date(10) DATE Date Closed
11 HRS_JO_TYPE Character(1) VARCHAR2(1) NOT NULL Job Opening Type
P=Continuous Job Opening
R=Standard Requisition
12 JOBCODE Character(6) VARCHAR2(6) NOT NULL Job Code
13 POSITION_NBR Character(8) VARCHAR2(8) NOT NULL Position Number
14 STATUS_CODE Character(3) VARCHAR2(3) NOT NULL Identifies a status.
15 BUSINESS_UNIT Character(5) VARCHAR2(5) NOT NULL Business Unit
16 COMPANY Character(3) VARCHAR2(3) NOT NULL Company
17 DEPTID Character(10) VARCHAR2(10) NOT NULL Department
18 LOCATION Character(10) VARCHAR2(10) NOT NULL Location Code
19 HRS_LOCATION_ID Number(15,0) DECIMAL(15) NOT NULL Recruiting location area
20 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

Y/N Table Edit

21 ORIGINATOR_ID Character(11) VARCHAR2(11) NOT NULL Originator's ID
22 MANAGER_ID Character(11) VARCHAR2(11) NOT NULL Manager ID
23 RECRUITER_ID Character(11) VARCHAR2(11) NOT NULL Recruiter ID
24 LASTUPDDTTM DateTime(26) TIMESTAMP Specifies the date and time of the last update to an entry. This field is maintained by PeopleSoft and is used in a variety of contexts.