|
SELECT JO.HRS_JOB_OPENING_ID , JO.EMPLID FROM PS_HRS_SJT_JO JO WHERE JO.EMPLID <> ' ' UNION SELECT JO.HRS_JOB_OPENING_ID , CLS.CLASSID FROM PS_HRS_SJT_JO JO ,PS_SJT_CLASS_ALL CLS WHERE CLS.SCRTY_SET_CD = 'RSOPN' AND CLS.SCRTY_TYPE_CD = JO.SCRTY_TYPE_CD AND CLS.SCRTY_KEY1 = JO.SCRTY_KEY1 AND CLS.SCRTY_KEY2 = JO.SCRTY_KEY2 AND CLS.SCRTY_KEY3 = JO.SCRTY_KEY3 AND JO.EMPLID = ' ' UNION SELECT JO.HRS_JOB_OPENING_ID , CLS.CLASSID FROM PS_HRS_SJT_JO JO ,PS_SJT_CLASS_ALL CLS WHERE CLS.SCRTY_SET_CD = 'PPLJOB' AND CLS.SCRTY_TYPE_CD = '001' AND CLS.SCRTY_KEY1 = JO.SCRTY_KEY1 AND CLS.SCRTY_KEY2 = JO.SCRTY_KEY2 AND CLS.SCRTY_KEY3 = JO.SCRTY_KEY3 AND JO.EMPLID = ' ' UNION SELECT DISTINCT A.HRS_JOB_OPENING_ID ,'A' FROM PS_HRS_JOB_OPENING A LEFT OUTER JOIN PS_HRS_JOBINDX_CTL F ON A.HRS_JOB_OPENING_ID = F.HRS_JOB_OPENING_ID AND F.HRS_JO_PST_SEQ = 0 , PS_HRS_JO_POSTING B , PS_HRS_JO_PST_DST C , PS_HRS_SOURCE D , PS_HRS_STS_TBL 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 C.HRS_JO_PST_OPN_DT <= %CurrentDateIn AND C.HRS_JO_PST_OPN_DT IS NOT NULL 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 E.STATUS_AREA = '1' 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 X.HRS_SOURCE_STATUS = '10') AND C.HRS_SEQ_NUM=( SELECT MAX(C1.HRS_SEQ_NUM) 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 (C1.HRS_JO_PST_CLS_DT > %CurrentDateIn OR C1.HRS_JO_PST_CLS_DT IS NULL) AND C1.HRS_JO_PST_OPN_DT <= %CurrentDateIn AND C1.HRS_JO_PST_OPN_DT IS NOT NULL )
|
HRS_JOB_OPENING_ID