HRS_JO_PST_DEL

(SQL View)
 Index Back

Ext and Int Job Openings

Job openings with postings that are available for external and internal applicants.

SELECT A.HRS_JOB_OPENING_ID ,C.HRS_JO_PST_SEQ ,C.HRS_JO_PST_TYPE ,C.HRS_SOURCE_ID ,CASE WHEN (%DatePart(F.HRS_ROW_UPD_DTTM) < C.HRS_JO_PST_CLS_DT) THEN %Dttm(C.HRS_JO_PST_CLS_DT, %TimeIn('00.00.00.000000')) ELSE %Dttm(%DatePart(F.HRS_ROW_UPD_DTTM), %TimePart(F.HRS_ROW_UPD_DTTM)) END FROM PS_HRS_JOB_OPENING A , PS_HRS_JO_POSTING B , PS_HRS_JO_PST_DST C , PS_HRS_SOURCE D , PS_HRS_STS_JO_I E , PS_HRS_JOBINDX_CTL F WHERE A.HRS_JOB_OPENING_ID = B.HRS_JOB_OPENING_ID AND A.HRS_JOB_OPENING_ID = C.HRS_JOB_OPENING_ID AND A.HRS_JOB_OPENING_ID = F.HRS_JOB_OPENING_ID AND F.HRS_JO_PST_SEQ = 0 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_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' OR (C.HRS_JO_PST_CLS_DT <=%CurrentDateIn ) OR (C.HRS_JO_PST_OPN_DT >%CurrentDateIn )) 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') AND NOT EXISTS( SELECT C1.HRS_JOB_OPENING_ID FROM PS_HRS_JO_PST_DST C1 WHERE C.HRS_JOB_OPENING_ID = C1.HRS_JOB_OPENING_ID AND C.HRS_JO_PST_SEQ = C1.HRS_JO_PST_SEQ AND C.HRS_JO_PST_TYPE = C1.HRS_JO_PST_TYPE AND C.HRS_SOURCE_ID = C1.HRS_SOURCE_ID AND D.HRS_SOURCE_TYPE = '40' AND E.OPEN_STS_IND = 'Y' AND C1.HRS_JO_PST_OPN_DT <= %CurrentDateIn AND (C1.HRS_JO_PST_CLS_DT IS NULL OR C1.HRS_JO_PST_CLS_DT > %CurrentDateIn)) UNION SELECT A.HRS_JOB_OPENING_ID ,A.HRS_JO_PST_SEQ ,A.HRS_JO_PST_TYPE ,A.HRS_SOURCE_ID ,%Dttm(%DatePart(A.HRS_ROW_UPD_DTTM),%TimePart(A.HRS_ROW_UPD_DTTM)) FROM PS_HRS_JO_DEL_PST A

# 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

Prompt Table: HRS_JO_I

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_ROW_UPD_DTTM DateTime(26) TIMESTAMP Row Updated DateTime