|
SELECT JODT.HRS_JOB_OPENING_ID , OPR.OPRID ,'O' , JOLNG.LANGUAGE_CD , JOLNG.HRS_PRM_PST_TITLE , COALESCE(( SELECT R.NAME_DISPLAY FROM PS_NAMES_LNG R WHERE R.EMPLID = JODT.RECRUITER_ID AND R.LANGUAGE_CD = JOLNG.LANGUAGE_CD AND R.NAME_TYPE = 'PRI' AND R.EFFDT = ( SELECT MAX(R1.EFFDT) FROM PS_NAMES_LNG R1 WHERE R1.EMPLID = R.EMPLID AND R1.NAME_TYPE = R.NAME_TYPE AND R1.LANGUAGE_CD = R.LANGUAGE_CD AND R1.EFFDT <= %CurrentDateIn)),' ') , COALESCE(( SELECT BU.DESCR FROM PS_BUS_UNIT_HR_LNG BU WHERE JODT.BUSINESS_UNIT = BU.BUSINESS_UNIT AND BU.LANGUAGE_CD = JOLNG.LANGUAGE_CD),' ') , COALESCE(( SELECT JF.DESCR FROM PS_JOB_FAMILY_LNG JF WHERE JODT.JOB_FAMILY = JF.JOB_FAMILY AND JF.LANGUAGE_CD = JOLNG.LANGUAGE_CD AND JF.EFFDT = ( SELECT MAX(JF1.EFFDT) FROM PS_JOB_FAMILY_LNG JF1 WHERE JF1.JOB_FAMILY = JF.JOB_FAMILY AND JF.LANGUAGE_CD = JF1.LANGUAGE_CD AND JF1.EFFDT <= %CurrentDateIn)),' ') , COALESCE(( SELECT DEPT.DESCR FROM PS_DEPT_TBL_LANG DEPT WHERE JODT.SETID_DEPT = DEPT.SETID AND JODT.DEPTID = DEPT.DEPTID AND DEPT.LANGUAGE_CD = JOLNG.LANGUAGE_CD AND DEPT.EFFDT = ( SELECT MAX(DEPT1.EFFDT) FROM PS_DEPT_TBL_LANG DEPT1 WHERE DEPT.SETID = DEPT1.SETID AND DEPT.LANGUAGE_CD = DEPT1.LANGUAGE_CD AND DEPT.DEPTID = DEPT1.DEPTID AND DEPT1.EFFDT <= %CurrentDateIn)),' ') , COALESCE(( SELECT JC.DESCR FROM PS_JOBCODE_LANG JC WHERE JC.SETID = JODT.SETID_DEPT AND JC.JOBCODE = JODT.HRS_PRM_JOBCODE AND JC.LANGUAGE_CD = JOLNG.LANGUAGE_CD AND JC.EFFDT = ( SELECT MAX(JC1.EFFDT) FROM PS_JOBCODE_LANG JC1 WHERE JC1.SETID = JC.SETID AND JC1.JOBCODE = JC.JOBCODE AND JC1.LANGUAGE_CD = JC.LANGUAGE_CD AND JC1.EFFDT <= %CurrentDateIn)),' ') , COALESCE(( SELECT LOCN.DESCR FROM PS_LOCATION_LANG LOCN WHERE JODT.SETID_DEPT = LOCN.SETID AND JODT.HRS_PRM_LOCATION = LOCN.LOCATION AND LOCN.EFFDT = ( SELECT MAX(LOCN1.EFFDT) FROM PS_LOCATION_LANG LOCN1 WHERE LOCN.SETID = LOCN1.SETID AND LOCN.LOCATION = LOCN1.LOCATION AND LOCN.EFFDT <= %CurrentDateIn) AND LOCN.LANGUAGE_CD = JOLNG.LANGUAGE_CD),' ') , COALESCE(( SELECT CN.DESCR FROM PS_COUNTRYTBL_LANG CN WHERE CN.COUNTRY = ( SELECT CN1.COUNTRY FROM PS_LOCATION_TBL CN1 WHERE CN1.SETID = JODT.SETID_DEPT AND CN1.LOCATION = JODT.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)) AND CN.LANGUAGE_CD = JOLNG.LANGUAGE_CD),' ') , ( SELECT XLT.MESSAGE_TEXT FROM PSMSGCATLANG XLT WHERE XLT.MESSAGE_SET_NBR = 18176 AND XLT.MESSAGE_NBR = 6199 AND XLT.LANGUAGE_CD = JOLNG.LANGUAGE_CD) FROM PS_HRS_JO_LNG JOLNG, PS_HRS_PG_JO_OPN_I JODT ,PS_HRS_EMPL_SEC_I DIR, PSOPRDEFN OPR WHERE JODT.HRS_JOB_OPENING_ID = JOLNG.HRS_JOB_OPENING_ID AND JODT.RECRUITER_ID = DIR.EMPLID AND DIR.SUPERVISOR_ID = OPR.EMPLID 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') UNION ALL SELECT JOPN.HRS_JOB_OPENING_ID , OPR.OPRID ,'C' , JOLNG.LANGUAGE_CD , JOLNG.HRS_PRM_PST_TITLE , COALESCE(( SELECT R.NAME_DISPLAY FROM PS_NAMES_LNG R WHERE R.EMPLID = JOPN.RECRUITER_ID AND R.LANGUAGE_CD = JOLNG.LANGUAGE_CD AND R.NAME_TYPE = 'PRI' AND R.EFFDT = ( SELECT MAX(R1.EFFDT) FROM PS_NAMES_LNG R1 WHERE R1.EMPLID = R.EMPLID AND R1.NAME_TYPE = R.NAME_TYPE AND R1.LANGUAGE_CD = R.LANGUAGE_CD AND R1.EFFDT <= %CurrentDateIn)),' ') , COALESCE(( SELECT BU.DESCR FROM PS_BUS_UNIT_HR_LNG BU WHERE JOPN.BUSINESS_UNIT = BU.BUSINESS_UNIT AND BU.LANGUAGE_CD = JOLNG.LANGUAGE_CD),' ') , COALESCE(( SELECT JF.DESCR FROM PS_JOB_FAMILY_LNG JF WHERE JOPN.JOB_FAMILY = JF.JOB_FAMILY AND JF.LANGUAGE_CD = JOLNG.LANGUAGE_CD AND JF.EFFDT = ( SELECT MAX(JF1.EFFDT) FROM PS_JOB_FAMILY_LNG JF1 WHERE JF1.JOB_FAMILY = JF.JOB_FAMILY AND JF.LANGUAGE_CD = JF1.LANGUAGE_CD AND JF1.EFFDT <= %CurrentDateIn)),' ') , COALESCE(( SELECT DEPT.DESCR FROM PS_DEPT_TBL_LANG DEPT WHERE JOPN.SETID_DEPT = DEPT.SETID AND JOPN.DEPTID = DEPT.DEPTID AND DEPT.LANGUAGE_CD = JOLNG.LANGUAGE_CD AND DEPT.EFFDT = ( SELECT MAX(DEPT1.EFFDT) FROM PS_DEPT_TBL_LANG DEPT1 WHERE DEPT.SETID = DEPT1.SETID AND DEPT.LANGUAGE_CD = DEPT1.LANGUAGE_CD AND DEPT.DEPTID = DEPT1.DEPTID AND DEPT1.EFFDT <= %CurrentDateIn)),' ') , COALESCE(( SELECT JC.DESCR FROM PS_JOBCODE_LANG JC WHERE JC.SETID = JOPN.SETID_DEPT AND JC.JOBCODE = JOPN.HRS_PRM_JOBCODE AND JC.LANGUAGE_CD = JOLNG.LANGUAGE_CD AND JC.EFFDT = ( SELECT MAX(JC1.EFFDT) FROM PS_JOBCODE_LANG JC1 WHERE JC1.SETID = JC.SETID AND JC1.JOBCODE = JC.JOBCODE AND JC1.LANGUAGE_CD = JC.LANGUAGE_CD AND JC1.EFFDT <= %CurrentDateIn)),' ') , COALESCE(( SELECT LOCN.DESCR FROM PS_LOCATION_LANG LOCN WHERE JOPN.SETID_DEPT = LOCN.SETID AND JOPN.HRS_PRM_LOCATION = LOCN.LOCATION AND LOCN.EFFDT = ( SELECT MAX(LOCN1.EFFDT) FROM PS_LOCATION_LANG LOCN1 WHERE LOCN.SETID = LOCN1.SETID AND LOCN.LOCATION = LOCN1.LOCATION AND LOCN.EFFDT <= %CurrentDateIn) AND LOCN.LANGUAGE_CD = JOLNG.LANGUAGE_CD),' ') , COALESCE(( SELECT CN.DESCR FROM PS_COUNTRYTBL_LANG CN WHERE CN.COUNTRY = ( SELECT CN1.COUNTRY FROM PS_LOCATION_TBL CN1 WHERE CN1.SETID = JOPN.SETID_DEPT 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 <= %CurrentDateIn)) AND CN.LANGUAGE_CD = JOLNG.LANGUAGE_CD),' ') , ( SELECT XLT.MESSAGE_TEXT FROM PSMSGCATLANG XLT WHERE XLT.MESSAGE_SET_NBR = 18176 AND XLT.MESSAGE_NBR = 6200 AND XLT.LANGUAGE_CD = JOLNG.LANGUAGE_CD) FROM PS_HRS_JOB_OPENING JOPN, PS_HRS_JO_LNG JOLNG,PS_HRS_EMPL_SEC_I DIR, PSOPRDEFN OPR WHERE JOPN.HRS_JOB_OPENING_ID = JOLNG.HRS_JOB_OPENING_ID AND JOPN.RECRUITER_ID = DIR.EMPLID AND DIR.SUPERVISOR_ID = OPR.EMPLID 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') AND JOPN.CLOSE_DT IS NOT NULL
|
HRS_JOB_OPENING_ID