|
SELECT JOB.HRS_JOB_OPENING_ID , OPR.OPRID , JOB.HRS_PRM_PST_TITLE , JOB.BUSINESS_UNIT , COALESCE(( SELECT BU.DESCR FROM PS_BUS_UNIT_TBL_HR BU WHERE BU.BUSINESS_UNIT = JOB.BUSINESS_UNIT),' ') , JOB.JOB_FAMILY , COALESCE(( SELECT JF.DESCR FROM PS_JOB_FAMILY_TBL JF WHERE JOB.JOB_FAMILY = JF.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 <= JOB.STATUS_DT)),' ') , JOB.RECRUITER_ID , COALESCE(( SELECT R.NAME_DISPLAY FROM PS_NAMES R WHERE R.EMPLID = JOB.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 <= JOB.STATUS_DT)),' ') , JOB.MANAGER_ID , COALESCE(( SELECT M.NAME_DISPLAY FROM PS_NAMES M WHERE M.EMPLID = JOB.MANAGER_ID AND M.NAME_TYPE = 'PRI' AND M.EFFDT = ( SELECT MAX(M1.EFFDT) FROM PS_NAMES M1 WHERE M1.EMPLID = M.EMPLID AND M1.NAME_TYPE = M.NAME_TYPE AND M1.EFFDT <= JOB.STATUS_DT)),' ') , JOB.HRS_PRM_JOBCODE , COALESCE(( SELECT JC.DESCR FROM PS_JOBCODE_TBL JC WHERE JC.SETID = JOB.SETID_DEPT AND JC.JOBCODE = JOB.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 <= JOB.STATUS_DT)),' ') , JOB.DEPTID , COALESCE(( SELECT D.DESCR FROM PS_DEPT_TBL D WHERE D.DEPTID = JOB.DEPTID AND D.SETID = JOB.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 <= JOB.STATUS_DT)),' ') , JOB.HRS_PRM_LOCATION , COALESCE(( SELECT L.DESCR FROM PS_LOCATION_TBL L WHERE L.LOCATION = JOB.HRS_PRM_LOCATION AND L.SETID = JOB.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 <= JOB.STATUS_DT)),' ') , COALESCE(( SELECT LC.COUNTRY FROM PS_LOCATION_TBL LC WHERE LC.SETID = JOB.SETID_DEPT AND LC.LOCATION = JOB.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 <= JOB.STATUS_DT)),' ') , COALESCE(( SELECT CN.DESCR FROM PS_COUNTRY_TBL CN WHERE CN.COUNTRY = ( SELECT CN1.COUNTRY FROM PS_LOCATION_TBL CN1 WHERE CN1.SETID = JOB.SETID_DEPT AND CN1.LOCATION = JOB.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 <= JOB.STATUS_DT))),' ') , JOB.STATUS_DT , 0 , %Sql(FUNCLIB_HRS_DATE_DIFF, JOB.OPEN_DT, JOB.CLOSE_DT) , %Sql(FUNCLIB_HRS_DATE_YEAR,JOB.STATUS_DT)%Concat '-' %Concat %Sql(FUNCLIB_HRS_DATE_MONTH,JOB.STATUS_DT) , %DateNull , %DateNull , JOB.SETID_DEPT FROM PS_HRS_PG_JO_OPN_I JOB , PS_HRS_EMPL_SEC_I DIR , PSOPRDEFN OPR WHERE JOB.RECRUITER_ID = DIR.EMPLID AND DIR.SUPERVISOR_ID = OPR.EMPLID AND JOB.STATUS_CODE = ( SELECT STS.STATUS_CODE FROM PS_HRS_STS_TBL STS WHERE STS.STATUS_AREA = '1' AND STS.COMPLETE_STS_IND = 'Y') AND JOB.CLOSE_DT IS NOT NULL AND DIR.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