|
SELECT JOPN.HRS_JOB_OPENING_ID , JOSEC.OPRID , JOPN.HRS_PRM_PST_TITLE , JOPN.COMPANY , COALESCE(( SELECT CO.DESCR FROM PS_COMPANY_TBL CO WHERE CO.COMPANY = JOPN.COMPANY AND CO.EFFDT = ( SELECT MAX(CO1.EFFDT) FROM PS_COMPANY_TBL CO1 WHERE CO1.COMPANY = CO.COMPANY AND CO1.EFFDT <= %CurrentDateIn)), ' ') , JOPN.BUSINESS_UNIT , COALESCE(( SELECT BU.DESCR FROM PS_BUS_UNIT_TBL_HR BU WHERE BU.BUSINESS_UNIT = JOPN.BUSINESS_UNIT),' ') , JOPN.DEPTID , COALESCE(( SELECT D.DESCR FROM PS_DEPT_TBL D WHERE D.DEPTID = JOPN.DEPTID AND D.SETID = JOPN.SETID_DEPT AND D.EFFDT = ( SELECT MAX(D1.EFFDT) FROM PS_DEPT_TBL D1 WHERE D1.DEPTID = D.DEPTID AND D1.SETID = D.SETID AND D1.EFFDT <= %CurrentDateIn)),' ') , JOPN.JOB_FAMILY , COALESCE(( SELECT JO.DESCR FROM PS_JOB_FAMILY_TBL JO WHERE JO.JOB_FAMILY = JOPN.JOB_FAMILY AND JO.EFFDT = ( SELECT MAX(JO1.EFFDT) FROM PS_JOB_FAMILY_TBL JO1 WHERE JO1.JOB_FAMILY = JO.JOB_FAMILY AND JO1.EFFDT <= %CurrentDateIn)),' ') , JOPN.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) = JOPN.BUSINESS_UNIT AND JC.JOBCODE = JOPN.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)),' ') , JOPN.RECRUITER_ID , COALESCE( ( SELECT R.NAME_DISPLAY FROM PS_NAMES R WHERE R.EMPLID = JOPN.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)),' ') , JOPN.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 = JOPN.HRS_PRM_LOCATION AND L.SETID = C.SETID AND %Substring(C.SETCNTRLVALUE , 1 ,5) = JOPN.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 <= JOPN.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) = JOPN.BUSINESS_UNIT AND LC.LOCATION = JOPN.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 <= JOPN.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) = JOPN.BUSINESS_UNIT AND CN1.LOCATION = JOPN.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 <= JOPN.STATUS_DT))),' ') , JOPN.OPEN_DT , CASE WHEN (%DateDiff(JOPN.OPEN_DT, %CurrentDateIn) < 30) THEN '<30' ELSE CASE WHEN (%DateDiff(JOPN.OPEN_DT, %CurrentDateIn) BETWEEN 31 AND 60) THEN '31-60' ELSE CASE WHEN (%DateDiff(JOPN.OPEN_DT, %CurrentDateIn) BETWEEN 61 AND 90) THEN '61-90' ELSE CASE WHEN (%DateDiff(JOPN.OPEN_DT, %CurrentDateIn) BETWEEN 91 AND 120) THEN '91-120' ELSE CASE WHEN (%DateDiff(JOPN.OPEN_DT, %CurrentDateIn) BETWEEN 121 AND 150) THEN '121-150' ELSE CASE WHEN (%DateDiff(JOPN.OPEN_DT, %CurrentDateIn) BETWEEN 151 AND 180) THEN '151-180' ELSE CASE WHEN (%DateDiff(JOPN.OPEN_DT, %CurrentDateIn) > 180) THEN '>180' END END END END END END END FROM PS_HRS_PG_JO_OPN_I JOPN, PS_HRS_JO_SEC_VW JOSEC WHERE JOPN.STATUS_CODE IN ( SELECT STS.STATUS_CODE FROM PS_HRS_STS_TBL STS WHERE STS.STATUS_AREA = '1' AND STS.OPEN_STS_IND = 'Y') AND JOPN.HRS_JOB_OPENING_ID = JOSEC.HRS_JOB_OPENING_ID
|
HRS_JOB_OPENING_ID