|
SELECT APP.HRS_PERSON_ID , APP.HRS_RCMNT_ID , APP.HRS_PROFILE_SEQ , APP.OPRID , JOLNG.LANGUAGE_CD , COALESCE(( SELECT APP_NM.NAME_DISPLAY FROM PS_HRS_APP_NM_LNG APP_NM WHERE APP_NM.HRS_PERSON_ID = APP.HRS_PERSON_ID AND APP_NM.NAME_TYPE = 'PRI' AND APP_NM.LANGUAGE_CD = JOLNG.LANGUAGE_CD),' ') , JOLNG.HRS_PRM_PST_TITLE , COALESCE(( SELECT STS.DESCR FROM PS_HRS_STS_LNG STS WHERE STS.STATUS_AREA = '3' AND STS.STATUS_CODE = APP.STATUS_CODE AND STS.LANGUAGE_CD = JOLNG.LANGUAGE_CD), ' ') ,COALESCE(( SELECT REC.NAME_DISPLAY FROM PS_NAMES_LNG REC WHERE REC.EMPLID = JO.RECRUITER_ID AND REC.NAME_TYPE = 'PRI' AND REC.LANGUAGE_CD = JOLNG.LANGUAGE_CD AND REC.EFFDT = ( SELECT MAX(REC1.EFFDT) FROM PS_NAMES_LNG REC1 WHERE REC1.EMPLID = REC.EMPLID AND REC1.NAME_TYPE = 'PRI' AND REC1.LANGUAGE_CD = REC.LANGUAGE_CD AND REC1.EFFDT <= %CurrentDateIn)),' '), COALESCE(( SELECT REC.NAME_DISPLAY FROM PS_NAMES_LNG REC WHERE REC.EMPLID = JO.MANAGER_ID AND REC.LANGUAGE_CD = JOLNG.LANGUAGE_CD AND REC.NAME_TYPE = 'PRI' AND REC.EFFDT = ( SELECT MAX(REC1.EFFDT) FROM PS_NAMES_LNG REC1 WHERE REC1.EMPLID = REC.EMPLID AND REC1.NAME_TYPE = 'PRI' AND REC1.LANGUAGE_CD = REC.LANGUAGE_CD AND REC1.EFFDT <= %CurrentDateIn)),' ') ,COALESCE(( SELECT JF.DESCR FROM PS_JOB_FAMILY_LNG JF WHERE APP.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 <= app.HRS_STATUS_DT)),' ') , COALESCE(( SELECT BU.DESCR FROM PS_BUS_UNIT_HR_LNG BU WHERE APP.BUSINESS_UNIT = BU.BUSINESS_UNIT AND BU.LANGUAGE_CD = JOLNG.LANGUAGE_CD),' ') , COALESCE(( SELECT DEPT.DESCR FROM PS_DEPT_TBL_LANG DEPT WHERE JO.SETID_DEPT = DEPT.SETID AND JO.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 <= app.HRS_STATUS_DT)),' ') , COALESCE(( SELECT L.DESCR FROM PS_HRS_LOC_LNG L WHERE L.HRS_LOCATION_ID = APP.HRS_PRM_LOC_AREA AND L.SETID = APP.SETID_DEPT AND L.LANGUAGE_CD = JOLNG.LANGUAGE_CD),' ') , COALESCE(( SELECT JC.DESCR FROM PS_JOBCODE_LANG JC WHERE JC.SETID = APP.SETID_DEPT AND JO.HRS_PRM_JOBCODE = JC.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 POSN.DESCR FROM PS_POSN_DATA_LANG POSN WHERE APP.HRS_PRM_POSITION = POSN.POSITION_NBR AND POSN.LANGUAGE_CD = JOLNG.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) ), ' ') FROM PS_HRS_SA_APPLST_I APP , PS_HRS_JOB_OPENING JO , PS_HRS_JO_LNG JOLNG WHERE APP.HRS_JOB_OPENING_ID = JO.HRS_JOB_OPENING_ID AND JO.HRS_JOB_OPENING_ID = JOLNG.HRS_JOB_OPENING_ID
|
HRS_PERSON_ID