|
SELECT JOAGE.HRS_JOB_OPENING_ID , JOAGE.OPRID , JOLN.LANGUAGE_CD , JOLN.HRS_PRM_PST_TITLE , COALESCE(( SELECT CMPNY.DESCR FROM PS_COMPNY_TBL_LANG CMPNY WHERE JOAGE.COMPANY = CMPNY.COMPANY AND CMPNY.LANGUAGE_CD = JOLN.LANGUAGE_CD AND CMPNY.EFFDT = ( SELECT MAX(CMPNY1.EFFDT) FROM PS_COMPNY_TBL_LANG CMPNY1 WHERE CMPNY1.COMPANY = CMPNY.COMPANY AND CMPNY1.EFFDT <= (%CurrentDateIn))) ,' ') , COALESCE(( SELECT BU.DESCR FROM PS_BUS_UNIT_HR_LNG BU WHERE JOAGE.BUSINESS_UNIT = BU.BUSINESS_UNIT AND BU.LANGUAGE_CD = JOLN.LANGUAGE_CD),' ') , COALESCE(( SELECT DEPT.DESCR FROM PS_DEPT_TBL_LANG DEPT WHERE JOAGE.SETID_DEPT = DEPT.SETID AND JOAGE.DEPTID = DEPT.DEPTID AND DEPT.LANGUAGE_CD = JOLN.LANGUAGE_CD AND DEPT.EFFDT = ( SELECT MAX(DEPT1.EFFDT) FROM PS_DEPT_TBL_LANG DEPT1 WHERE DEPT.SETID = DEPT1.SETID AND DEPT.DEPTID = DEPT1.DEPTID AND DEPT1.EFFDT <= %CurrentDateIn)),' ') , COALESCE(( SELECT JF.DESCR FROM PS_JOB_FAMILY_LNG JF WHERE JOAGE.JOB_FAMILY = JF.JOB_FAMILY AND JF.LANGUAGE_CD = JOLN.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 JC.DESCR FROM PS_JOBCODE_LANG JC WHERE JC.SETID = JOAGE.SETID_DEPT AND JC.JOBCODE = JOAGE.JOBCODE AND JC.LANGUAGE_CD = JOLN.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 R.NAME_DISPLAY FROM PS_NAMES_LNG R WHERE R.EMPLID = JOAGE.RECRUITER_ID AND R.NAME_TYPE = 'PRI' AND R.LANGUAGE_CD = JOLN.LANGUAGE_CD 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 M.NAME_DISPLAY FROM PS_NAMES_LNG M WHERE M.EMPLID = JOAGE.MANAGER_ID AND M.NAME_TYPE = 'PRI' AND M.LANGUAGE_CD = JOLN.LANGUAGE_CD AND M.EFFDT = ( SELECT MAX(M1.EFFDT) FROM PS_NAMES_LNG M1 WHERE M1.EMPLID = M.EMPLID AND M1.NAME_TYPE = M.NAME_TYPE AND M1.LANGUAGE_CD = M.LANGUAGE_CD AND M1.EFFDT <= %CurrentDateIn)) , ' '),COALESCE(( SELECT L.DESCR FROM PS_HRS_LOC_LNG L WHERE L.HRS_LOCATION_ID = JOAGE.HRS_PRM_LOC_AREA AND L.SETID = JOAGE.SETID_DEPT AND L.LANGUAGE_CD = JOLN.LANGUAGE_CD),' ') , COALESCE(( SELECT POSN.DESCR FROM PS_POSN_DATA_LANG POSN WHERE JOAGE.HRS_PRM_POSITION = POSN.POSITION_NBR AND POSN.LANGUAGE_CD = JOLN.LANGUAGE_CD AND POSN.EFFDT = ( SELECT MAX(POSN1.EFFDT) FROM PS_POSN_DATA_LANG POSN1 WHERE POSN.POSITION_NBR = POSN1.POSITION_NBR AND POSN.LANGUAGE_CD = POSN1.LANGUAGE_CD AND POSN1.EFFDT <= %CurrentDateIn) ), ' ') , COALESCE(( SELECT CATLN.DESCR FROM ps_HRS_JOBCAT_ULNG CATLN WHERE CATLN.OPRID = JOAGE.OPRID AND CATLN.HRS_JOBCAT_ID = ( SELECT X.HRS_JOBCAT_ID FROM PS_HRS_JO_JOB_CATG X WHERE X.HRS_JOB_OPENING_ID = JOAGE.HRS_JOB_OPENING_ID AND X.OPRID = JOAGE.OPRID) AND CATLN.LANGUAGE_CD = JOLN.LANGUAGE_CD), ' ') FROM PS_HRS_SA_JO_AGE_I JOAGE , PS_HRS_JO_LNG JOLN WHERE JOAGE.HRS_JOB_OPENING_ID = JOLN.HRS_JOB_OPENING_ID
|
HRS_JOB_OPENING_ID