|
SELECT JO.HRS_JOB_OPENING_ID , OPR.OPRID , JO.HRS_PRM_PST_TITLE , JO.COMPANY , COALESCE(( SELECT CO.DESCR FROM PS_COMPANY_TBL CO WHERE CO.COMPANY = JO.COMPANY AND CO.EFFDT = ( SELECT MAX(CO1.EFFDT) FROM PS_COMPANY_TBL CO1 WHERE CO1.COMPANY = CO.COMPANY AND CO1.EFFDT <= %CurrentDateIn)),' ') , JO.BUSINESS_UNIT , COALESCE(( SELECT BU.DESCR FROM PS_BUS_UNIT_TBL_HR BU WHERE BU.BUSINESS_UNIT = JO.BUSINESS_UNIT),' ') , JO.DEPTID , COALESCE(( SELECT D.DESCR FROM PS_DEPT_TBL D WHERE D.DEPTID = JO.DEPTID AND D.SETID = JO.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)),' ') , JO.JOB_FAMILY , COALESCE(( SELECT JOF.DESCR FROM PS_JOB_FAMILY_TBL JOF WHERE JOF.JOB_FAMILY = JO.JOB_FAMILY AND JOF.EFFDT = ( SELECT MAX(JO1.EFFDT) FROM PS_JOB_FAMILY_TBL JO1 WHERE JO1.JOB_FAMILY = JOF.JOB_FAMILY AND JO1.EFFDT <= %CurrentDateIn)),' ') , JO.HRS_PRM_JOBCODE , COALESCE( ( SELECT JC.DESCR FROM PS_JOBCODE_TBL JC WHERE JC.SETID = JO.SETID_DEPT AND JC.JOBCODE = JO.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)),' ') , JO.RECRUITER_ID , COALESCE(( SELECT R.NAME_DISPLAY FROM PS_NAMES R WHERE R.EMPLID = JO.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)),' ') , JO.HRS_PRM_LOCATION , COALESCE(( SELECT L.DESCR FROM PS_LOCATION_TBL L WHERE L.LOCATION = JO.HRS_PRM_LOCATION AND L.SETID = JO.SETID_DEPT 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 <= %CurrentDateIn)),' ') , COALESCE(( SELECT LC.COUNTRY FROM PS_LOCATION_TBL LC WHERE LC.SETID = JO.SETID_DEPT AND LC.LOCATION = JO.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 <= %CurrentDateIn)),' ') , COALESCE(( SELECT CN.DESCR FROM PS_COUNTRY_TBL CN WHERE CN.COUNTRY = ( SELECT CN1.COUNTRY FROM PS_LOCATION_TBL CN1 WHERE CN1.SETID = JO.SETID_DEPT AND CN1.LOCATION = JO.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 <= %CurrentDateIn))),' ') , JO.OPEN_DT , CASE WHEN (%DateDiff(JO.OPEN_DT, %CurrentDateIn) < 30) THEN '<30' ELSE CASE WHEN (%DateDiff(JO.OPEN_DT,%CurrentDateIn) BETWEEN 31 AND 60) THEN '31-60' ELSE CASE WHEN (%DateDiff(JO.OPEN_DT,%CurrentDateIn) BETWEEN 61 AND 90) THEN '61-90' ELSE CASE WHEN (%DateDiff(JO.OPEN_DT,%CurrentDateIn) BETWEEN 91 AND 120) THEN '91-120' ELSE CASE WHEN (%DateDiff(JO.OPEN_DT,%CurrentDateIn) BETWEEN 121 AND 150) THEN '121-150' ELSE CASE WHEN (%DateDiff(JO.OPEN_DT,%CurrentDateIn) BETWEEN 151 AND 180) THEN '151-180' ELSE CASE WHEN (%DateDiff(JO.OPEN_DT,%CurrentDateIn) > 180) THEN '>180' END END END END END END END,%DateNull,%DateNull , JO.RECRUITER_ID , SEC.SUPERVISOR_ID , (CASE WHEN JO.STATUS_CODE = ( SELECT B1.STATUS_CODE FROM PS_HRS_STS_TBL B1 WHERE B1.STATUS_AREA = '1' AND B1.OPEN_STS_IND = 'Y') THEN 'O' ELSE 'C' END) FROM PS_HRS_PG_JO_OPN_I JO, PS_HRS_EMPL_SEC_I SEC , PSOPRDEFN OPR WHERE JO.RECRUITER_ID = SEC.EMPLID AND SEC.SUPERVISOR_ID = OPR.EMPLID AND JO.STATUS_CODE IN ( SELECT B.STATUS_CODE FROM PS_HRS_STS_TBL B WHERE B.STATUS_AREA = '1' AND (B.OPEN_STS_IND = 'Y' OR B.COMPLETE_STS_IND = 'Y')) AND SEC.ACCESS_TYPE = ( SELECT ATYPE.ACCESS_TYPE FROM PS_SS_LINK_TBL ATYPE WHERE ATYPE.PNLGRPNAME = 'HRS_PE_MGR_SUMM' AND ATYPE.MARKET = 'GBL')
|
HRS_JOB_OPENING_ID