|
SELECT A2.HRS_JOB_OPENING_ID , A1.OPRID , A2.HRS_PRM_PST_TITLE , A2.OPEN_DT , A2.RECRUITER_ID , COALESCE(( SELECT R.NAME_DISPLAY FROM PS_NAMES R WHERE R.EMPLID = A2.RECRUITER_ID AND R.NAME_TYPE = 'PRI' AND R.EFFDT = ( SELECT MAX(R1.EFFDT) FROM PS_NAMES R1 WHERE R1.EMPLID = R.EMPLID AND R1.NAME_TYPE = R.NAME_TYPE AND R1.EFFDT <= %CurrentDateIn)),' ') , A2.BUSINESS_UNIT , COALESCE(( SELECT BU.DESCR FROM PS_BUS_UNIT_TBL_HR BU WHERE BU.BUSINESS_UNIT = A2.BUSINESS_UNIT),' ') , A2.JOB_FAMILY , COALESCE(( SELECT JF.DESCR FROM PS_JOB_FAMILY_TBL JF WHERE JF.JOB_FAMILY = A2.JOB_FAMILY AND JF.EFFDT = ( SELECT MAX(JF1.EFFDT) FROM PS_JOB_FAMILY_TBL JF1 WHERE JF1.JOB_FAMILY = JF.JOB_FAMILY AND JF1.EFFDT <= %CurrentDateIn)),' ') , A2.DEPTID , COALESCE(( SELECT DEPT.DESCR FROM PS_DEPT_TBL DEPT WHERE DEPT.SETID = A2.SETID_DEPT AND DEPT.DEPTID = A2.DEPTID AND DEPT.EFFDT = ( SELECT MAX(DEPT1.EFFDT) FROM PS_DEPT_TBL DEPT1 WHERE DEPT1.SETID = DEPT.SETID AND DEPT1.DEPTID = DEPT.DEPTID AND DEPT1.EFFDT <= %CurrentDateIn)),' ') , A2.HRS_PRM_JOBCODE , COALESCE(( SELECT JC.DESCR FROM PS_JOBCODE_TBL JC ,PS_SET_CNTRL_REC C WHERE C.recname = 'HRS_JOBCODE_I' AND JC.SETID = C.SETID AND %Substring(C.SETCNTRLVALUE, 1, 5) = A2.BUSINESS_UNIT AND JC.JOBCODE = A2.HRS_PRM_JOBCODE AND JC.EFFDT = ( SELECT MAX(JC1.EFFDT) FROM PS_JOBCODE_TBL JC1 WHERE JC1.SETID=JC.SETID AND JC1.JOBCODE = JC.JOBCODE AND JC1.EFFDT <= %CurrentDateIn)),' ') , A2.HRS_PRM_LOCATION , COALESCE(( SELECT L.DESCR FROM PS_LOCATION_TBL L , PS_SET_CNTRL_REC C WHERE C.recname = 'HRS_LOCATN_I' AND L.LOCATION = A2.HRS_PRM_LOCATION AND L.SETID = C.SETID AND %Substring(C.SETCNTRLVALUE, 1, 5) = A2.BUSINESS_UNIT AND L.EFFDT = ( SELECT MAX(L1.EFFDT) FROM PS_LOCATION_TBL L1 WHERE L1.LOCATION = L.LOCATION AND L1.SETID = L.SETID AND L1.EFFDT <= A2.STATUS_DT)),' ') , COALESCE(( SELECT LC.COUNTRY FROM PS_LOCATION_TBL LC , PS_SET_CNTRL_REC C WHERE C.recname = 'HRS_LOCATN_I' AND LC.SETID = C.SETID AND %Substring(C.SETCNTRLVALUE, 1, 5) = A2.BUSINESS_UNIT AND LC.LOCATION = A2.HRS_PRM_LOCATION AND LC.EFFDT = ( SELECT MAX(LC1.EFFDT) FROM PS_LOCATION_TBL LC1 WHERE LC1.LOCATION = LC.LOCATION AND LC1.SETID = LC.SETID AND LC1.EFFDT <= A2.STATUS_DT)),' ') , COALESCE(( SELECT CN.DESCR FROM PS_COUNTRY_TBL CN WHERE CN.COUNTRY = ( SELECT CN1.COUNTRY FROM PS_LOCATION_TBL CN1 , PS_SET_CNTRL_REC C WHERE C.recname = 'HRS_LOCATN_I' AND CN1.SETID = C.SETID AND %Substring(C.SETCNTRLVALUE, 1, 5) = A2.BUSINESS_UNIT AND CN1.LOCATION = A2.HRS_PRM_LOCATION AND CN1.EFFDT = ( SELECT MAX(CN2.EFFDT) FROM PS_LOCATION_TBL CN2 WHERE CN2.LOCATION = CN1.LOCATION AND CN2.SETID = CN1.SETID AND CN2.EFFDT <= A2.STATUS_DT))),' ') , ( SELECT MESSAGE_TEXT FROM PSMSGCATDEFN WHERE MESSAGE_SET_NBR = 18176 AND MESSAGE_NBR = 6199) , %Sql(FUNCLIB_HRS_DATE_YEAR,A2.OPEN_DT)%Concat '-' %Concat %Sql(FUNCLIB_HRS_DATE_MONTH,A2.OPEN_DT) , %DateNull, %DateNull FROM PS_HRS_JO_SEC_VW A1, PS_HRS_PG_JO_OPN_V A2 WHERE A2.OPEN_DT IS NOT NULL AND A2.HRS_JOB_OPENING_ID = A1.HRS_JOB_OPENING_ID UNION SELECT B.HRS_JOB_OPENING_ID , B1.OPRID , B.HRS_PRM_PST_TITLE , B.CLOSE_DT , B.RECRUITER_ID , COALESCE(( SELECT N.NAME_DISPLAY FROM PS_NAMES N WHERE N.EMPLID = B.RECRUITER_ID AND N.NAME_TYPE = 'PRI' AND N.EFFDT = ( SELECT MAX(N1.EFFDT) FROM PS_NAMES N1 WHERE N1.EMPLID = N.EMPLID AND N1.NAME_TYPE = N.NAME_TYPE AND N1.EFFDT <= %CurrentDateIn)),' ') , B.BUSINESS_UNIT , COALESCE(( SELECT BUS.DESCR FROM PS_BUS_UNIT_TBL_HR BUS WHERE BUS.BUSINESS_UNIT = B.BUSINESS_UNIT),' ') , B.JOB_FAMILY , COALESCE(( SELECT JFAM.DESCR FROM PS_JOB_FAMILY_TBL JFAM WHERE JFAM.JOB_FAMILY = B.JOB_FAMILY AND JFAM.EFFDT = ( SELECT MAX(JFAM1.EFFDT) FROM PS_JOB_FAMILY_TBL JFAM1 WHERE JFAM1.JOB_FAMILY = JFAM.JOB_FAMILY AND JFAM1.EFFDT <= %CurrentDateIn)),' ') , B.DEPTID , COALESCE(( SELECT DT.DESCR FROM PS_DEPT_TBL DT WHERE DT.SETID = B.SETID_DEPT AND DT.DEPTID = B.DEPTID AND DT.EFFDT = ( SELECT MAX(DT1.EFFDT) FROM PS_DEPT_TBL DT1 WHERE DT1.SETID = DT.SETID AND DT1.DEPTID = DT.DEPTID AND DT1.EFFDT <= %CurrentDateIn)),' ') , B.HRS_PRM_JOBCODE , COALESCE(( SELECT JOC.DESCR FROM PS_JOBCODE_TBL JOC , PS_SET_CNTRL_REC C WHERE C.recname = 'HRS_JOBCODE_I' AND JOC.SETID = C.SETID AND %Substring(C.SETCNTRLVALUE, 1, 5) = B.BUSINESS_UNIT AND JOC.JOBCODE = B.HRS_PRM_JOBCODE AND JOC.EFFDT = ( SELECT MAX(JOC1.EFFDT) FROM PS_JOBCODE_TBL JOC1 WHERE JOC1.SETID = JOC.SETID AND JOC1.JOBCODE = JOC.JOBCODE AND JOC1.EFFDT <= %CurrentDateIn)),' ') , B.HRS_PRM_LOCATION , COALESCE(( SELECT L.DESCR FROM PS_LOCATION_TBL L , PS_SET_CNTRL_REC C WHERE C.recname = 'HRS_LOCATN_I' AND L.LOCATION = B.HRS_PRM_LOCATION AND L.SETID = C.SETID AND %Substring(C.SETCNTRLVALUE, 1, 5) = B.BUSINESS_UNIT AND L.EFFDT = ( SELECT MAX(L1.EFFDT) FROM PS_LOCATION_TBL L1 WHERE L1.LOCATION = L.LOCATION AND L1.SETID = L.SETID AND L1.EFFDT <= B.STATUS_DT)),' ') , COALESCE(( SELECT LC.COUNTRY FROM PS_LOCATION_TBL LC , PS_SET_CNTRL_REC C WHERE C.recname = 'HRS_LOCATN_I' AND LC.SETID = C.SETID AND %Substring(C.SETCNTRLVALUE, 1, 5) = B.BUSINESS_UNIT AND LC.LOCATION = B.HRS_PRM_LOCATION AND LC.EFFDT = ( SELECT MAX(LC1.EFFDT) FROM PS_LOCATION_TBL LC1 WHERE LC1.LOCATION = LC.LOCATION AND LC1.SETID = LC.SETID AND LC1.EFFDT <= B.STATUS_DT)),' ') , COALESCE(( SELECT CN.DESCR FROM PS_COUNTRY_TBL CN WHERE CN.COUNTRY = ( SELECT CN1.COUNTRY FROM PS_LOCATION_TBL CN1 , PS_SET_CNTRL_REC C WHERE C.recname = 'HRS_LOCATN_I' AND CN1.SETID = C.SETID AND %Substring(C.SETCNTRLVALUE, 1, 5) = B.BUSINESS_UNIT AND CN1.LOCATION = B.HRS_PRM_LOCATION AND CN1.EFFDT = ( SELECT MAX(CN2.EFFDT) FROM PS_LOCATION_TBL CN2 WHERE CN2.LOCATION = CN1.LOCATION AND CN2.SETID = CN1.SETID AND CN2.EFFDT <= B.STATUS_DT))),' ') , ( SELECT MESSAGE_TEXT FROM PSMSGCATDEFN WHERE MESSAGE_SET_NBR = 18176 AND MESSAGE_NBR = 6200) ,%Sql(FUNCLIB_HRS_DATE_YEAR,B.CLOSE_DT)%Concat '-' %Concat %Sql(FUNCLIB_HRS_DATE_MONTH,B.CLOSE_DT), %DateNull, %DateNull FROM PS_HRS_PG_JO_OPN_V B, PS_HRS_JO_SEC_VW B1 WHERE B.CLOSE_DT IS NOT NULL AND B.HRS_JOB_OPENING_ID = B1.HRS_JOB_OPENING_ID
|
HRS_JOB_OPENING_ID