HR_PG_MVMT_LNG

(SQL View)
 Index Back

Lang View for HC Movement -Mgr


SELECT JOB.EMPLID , JOB.EMPL_RCD , JOB.EFFDT , L.LANGUAGE_CD , ( SELECT PER_DL.NAME_DISPLAY FROM PS_PERSONAL_DTALNG PER_DL WHERE PER_DL.EMPLID = JOB.EMPLID AND PER_DL.LANGUAGE_CD = L.LANGUAGE_CD), ( SELECT X.XLATLONGNAME FROM PSXLATITEMLANG X WHERE X.FIELDNAME = 'HR_STATUS' AND X.FIELDVALUE = JOB.HR_STATUS AND X.LANGUAGE_CD = L.LANGUAGE_CD AND %EffdtCheck(PSXLATITEMLANG X1, X, %CurrentDateIn)), ( SELECT RR.DESCR50 FROM PS_REG_REGION_LANG RR WHERE RR.REG_REGION = JOB.REG_REGION AND RR.LANGUAGE_CD = L.LANGUAGE_CD), ( SELECT SA.DESCR FROM PS_SAL_PLAN_LANG SA WHERE SA.SETID = JOB.SETID_SALARY AND SA.SAL_ADMIN_PLAN = JOB.SAL_ADMIN_PLAN AND SA.LANGUAGE_CD = L.LANGUAGE_CD AND %EffdtCheck(SAL_PLAN_LANG SA1, SA, %CurrentDateIn)), ( SELECT SUPL.NAME_DISPLAY FROM PS_PERSONAL_DTALNG SUPL WHERE SUPL.EMPLID = JOB.SUPERVISOR_ID AND SUPL.LANGUAGE_CD = L.LANGUAGE_CD), ( SELECT A6.DESCR FROM PS_POSN_DATA_LANG A6 WHERE JOB.REPORTS_TO = A6.POSITION_NBR AND A6.LANGUAGE_CD = L.LANGUAGE_CD AND %EffdtCheck(POSN_DATA_LANG A61, A6, %CurrentDateIn)), (CASE WHEN JOB.ACTION = 'XFR' THEN ( SELECT MESSAGE_TEXT FROM PSMSGCATLANG WHERE MESSAGE_SET_NBR = 1000 AND LANGUAGE_CD = L.LANGUAGE_CD AND MESSAGE_NBR = 30164) ELSE ( SELECT ACT.ACTION_DESCR FROM PS_ACTION_LNG ACT WHERE ACT.ACTION = JOB.ACTION AND ACT.LANGUAGE_CD = L.LANGUAGE_CD AND %EffdtCheck(ACTION_LNG ACT1, ACT, %CurrentDateIn)) END), ( SELECT A7.XLATLONGNAME FROM PSXLATITEMLANG A7 WHERE A7.FIELDNAME = 'FULL_PART_TIME' AND A7.FIELDVALUE = JOB.FULL_PART_TIME AND A7.LANGUAGE_CD = L.LANGUAGE_CD AND %EffdtCheck(PSXLATITEMLANG A71, A7, %CurrentDateIn)), (CASE H.HR_DR_LEVEL WHEN 1 THEN ( SELECT MESSAGE_TEXT FROM PSMSGCATLANG WHERE MESSAGE_SET_NBR = 1000 AND LANGUAGE_CD = L.LANGUAGE_CD AND MESSAGE_NBR = 30168) ELSE ( SELECT MESSAGE_TEXT FROM PSMSGCATLANG WHERE MESSAGE_SET_NBR = 1000 AND LANGUAGE_CD = L.LANGUAGE_CD AND MESSAGE_NBR = 30162) END), ( SELECT JCODE.DESCR FROM PS_JOBCODE_LANG JCODE WHERE JCODE.SETID = JOB.SETID_JOBCODE AND JCODE.JOBCODE = JOB.JOBCODE AND JCODE.LANGUAGE_CD = L.LANGUAGE_CD AND %EffdtCheck(JOBCODE_LANG JCODE1, JCODE, %CurrentDateIn)), ( SELECT A6.DESCR FROM PS_POSN_DATA_LANG A6 WHERE JOB.POSITION_NBR = A6.POSITION_NBR AND A6.LANGUAGE_CD = L.LANGUAGE_CD AND %EffdtCheck(POSN_DATA_LANG A61, A6, %CurrentDateIn)), ( SELECT DEPT.DESCR FROM PS_DEPT_TBL_LANG DEPT WHERE JOB.SETID_DEPT = DEPT.SETID AND JOB.DEPTID = DEPT.DEPTID AND DEPT.LANGUAGE_CD = L.LANGUAGE_CD AND %EffdtCheck(DEPT_TBL_LANG DEPT1, DEPT, %CurrentDateIn)), ( SELECT A12.DESCR FROM PS_ESTAB_TBL_LANG A12 WHERE A12.ESTABID = JOB.ESTABID AND A12.LANGUAGE_CD = L.LANGUAGE_CD AND %EffdtCheck(ESTAB_TBL_LANG A121, A12, %CurrentDateIn)), ( SELECT LOCN.DESCR FROM PS_LOCATION_LANG LOCN WHERE JOB.SETID_LOCATION = LOCN.SETID AND JOB.LOCATION = LOCN.LOCATION AND LOCN.LANGUAGE_CD = L.LANGUAGE_CD AND %EffdtCheck(LOCATION_LANG LOCN1, LOCN, %CurrentDateIn)), O.OPRID FROM PS_JOB JOB, PS_HR_DIRECT_REP_2 H, PSOPRDEFN O, PSLANGUAGES L WHERE JOB.EMPLID = H.EMPLID AND JOB.EMPL_RCD = H.EMPL_RCD AND H.DRILL_DOWN_FLAG = 'Y' AND '2' = ( SELECT J.ACCESS_TYPE FROM PS_SS_LINK_TBL J WHERE J.PNLGRPNAME = 'UX_DIRECTS_PLT' AND J.MARKET = 'GBL') AND H.SUPERVISOR_ID = O.EMPLID AND JOB.ACTION IN ('ADD','ADL','ASC','ASG','DEM','HIR','LOA','LOF','LTD','LTO','PLA','PLV','POI','PRO','REC','REH','RET','RFA','RFD','RFL','RNW','RTS','RWB','RWP','SF4','SF5','SF7','SFE','SFF','SFG','SFI','SFK','SFL','SFN','SFP','SFR','SFS','SFT','SFV','STD','STO','SUS','SWB','TAS','TDL','TER','TWB','TWP','XFR') AND L.INSTALLED = 1 AND L.LANGUAGE_CD <> ( SELECT OP.LANGUAGE_CD FROM PSOPTIONS OP) UNION ALL SELECT JOB.EMPLID , JOB.EMPL_RCD , BB.EFFDT , L.LANGUAGE_CD , ( SELECT PER_DL.NAME_DISPLAY FROM PS_PERSONAL_DTALNG PER_DL WHERE PER_DL.EMPLID = JOB.EMPLID AND PER_DL.LANGUAGE_CD = L.LANGUAGE_CD), ( SELECT X.XLATLONGNAME FROM PSXLATITEMLANG X WHERE X.FIELDNAME = 'HR_STATUS' AND X.FIELDVALUE = JOB.HR_STATUS AND X.LANGUAGE_CD = L.LANGUAGE_CD AND %EffdtCheck(PSXLATITEMLANG X1, X, %CurrentDateIn)), ( SELECT RR.DESCR50 FROM PS_REG_REGION_LANG RR WHERE RR.REG_REGION = JOB.REG_REGION AND RR.LANGUAGE_CD = L.LANGUAGE_CD), ( SELECT SA.DESCR FROM PS_SAL_PLAN_LANG SA WHERE SA.SETID = JOB.SETID_SALARY AND SA.SAL_ADMIN_PLAN = JOB.SAL_ADMIN_PLAN AND SA.LANGUAGE_CD = L.LANGUAGE_CD AND %EffdtCheck(SAL_PLAN_LANG SA1, SA, %CurrentDateIn)), ( SELECT SUPL.NAME_DISPLAY FROM PS_PERSONAL_DTALNG SUPL WHERE SUPL.EMPLID = JOB.SUPERVISOR_ID AND SUPL.LANGUAGE_CD = L.LANGUAGE_CD), ( SELECT A6.DESCR FROM PS_POSN_DATA_LANG A6 WHERE JOB.REPORTS_TO = A6.POSITION_NBR AND A6.LANGUAGE_CD = L.LANGUAGE_CD AND %EffdtCheck(POSN_DATA_LANG A61, A6, %CurrentDateIn)), ( SELECT MESSAGE_TEXT FROM PSMSGCATDEFN WHERE MESSAGE_SET_NBR = 1000 AND MESSAGE_NBR = 30163), ( SELECT A7.XLATLONGNAME FROM PSXLATITEMLANG A7 WHERE A7.FIELDNAME = 'FULL_PART_TIME' AND A7.FIELDVALUE = JOB.FULL_PART_TIME AND A7.LANGUAGE_CD = L.LANGUAGE_CD AND %EffdtCheck(PSXLATITEMLANG A71, A7, %CurrentDateIn)), (CASE H.HR_DR_LEVEL WHEN 1 THEN ( SELECT MESSAGE_TEXT FROM PSMSGCATLANG WHERE MESSAGE_SET_NBR = 1000 AND LANGUAGE_CD = L.LANGUAGE_CD AND MESSAGE_NBR = 30168) ELSE ( SELECT MESSAGE_TEXT FROM PSMSGCATLANG WHERE MESSAGE_SET_NBR = 1000 AND LANGUAGE_CD = L.LANGUAGE_CD AND MESSAGE_NBR = 30162) END), ( SELECT JCODE.DESCR FROM PS_JOBCODE_LANG JCODE WHERE JCODE.SETID = JOB.SETID_JOBCODE AND JCODE.JOBCODE = JOB.JOBCODE AND JCODE.LANGUAGE_CD = L.LANGUAGE_CD AND %EffdtCheck(JOBCODE_LANG JCODE1, JCODE, %CurrentDateIn)), ( SELECT A6.DESCR FROM PS_POSN_DATA_LANG A6 WHERE JOB.POSITION_NBR = A6.POSITION_NBR AND A6.LANGUAGE_CD = L.LANGUAGE_CD AND %EffdtCheck(POSN_DATA_LANG A61, A6, %CurrentDateIn)), ( SELECT DEPT.DESCR FROM PS_DEPT_TBL_LANG DEPT WHERE JOB.SETID_DEPT = DEPT.SETID AND JOB.DEPTID = DEPT.DEPTID AND DEPT.LANGUAGE_CD = L.LANGUAGE_CD AND %EffdtCheck(DEPT_TBL_LANG DEPT1, DEPT, %CurrentDateIn)), ( SELECT A12.DESCR FROM PS_ESTAB_TBL_LANG A12 WHERE A12.ESTABID = JOB.ESTABID AND A12.LANGUAGE_CD = L.LANGUAGE_CD AND %EffdtCheck(ESTAB_TBL_LANG A121, A12, %CurrentDateIn)), ( SELECT LOCN.DESCR FROM PS_LOCATION_LANG LOCN WHERE JOB.SETID_LOCATION = LOCN.SETID AND JOB.LOCATION = LOCN.LOCATION AND LOCN.LANGUAGE_CD = L.LANGUAGE_CD AND %EffdtCheck(LOCATION_LANG LOCN1, LOCN, %CurrentDateIn)), O.OPRID FROM PS_JOB JOB, PS_JOB BB, PS_HR_DIRECT_REP_2 H, PSOPRDEFN O, PSLANGUAGES L WHERE JOB.EMPLID = BB.EMPLID AND JOB.EMPL_RCD = BB.EMPL_RCD AND JOB.EFFSEQ = BB.EFFSEQ AND (BB.ACTION = 'XFR' OR (BB.ACTION = 'POS' AND BB.ACTION_REASON = 'XFR')) AND JOB.EMPLID = H.EMPLID AND JOB.EMPL_RCD = H.EMPL_RCD AND H.SUPERVISOR_ID = O.EMPLID AND H.DRILL_DOWN_FLAG = 'Y' AND '2' = ( SELECT J.ACCESS_TYPE FROM PS_SS_LINK_TBL J WHERE J.PNLGRPNAME = 'UX_DIRECTS_PLT' AND J.MARKET = 'GBL') AND JOB.EFFDT = ( SELECT MAX(AA1.EFFDT) FROM PS_JOB AA1 WHERE AA1.EMPLID = BB.EMPLID AND AA1.EMPL_RCD = BB.EMPL_RCD AND AA1.EFFSEQ = BB.EFFSEQ AND AA1.EFFDT < BB.EFFDT ) AND L.INSTALLED = 1 AND L.LANGUAGE_CD <> ( SELECT OP.LANGUAGE_CD FROM PSOPTIONS OP) UNION ALL SELECT JOB.EMPLID , JOB.EMPL_RCD , JOB.EFFDT , L.LANGUAGE_CD , ( SELECT PER_DL.NAME_DISPLAY FROM PS_PERSONAL_DTALNG PER_DL WHERE PER_DL.EMPLID = JOB.EMPLID AND PER_DL.LANGUAGE_CD = L.LANGUAGE_CD), ( SELECT X.XLATLONGNAME FROM PSXLATITEMLANG X WHERE X.FIELDNAME = 'HR_STATUS' AND X.FIELDVALUE = JOB.HR_STATUS AND X.LANGUAGE_CD = L.LANGUAGE_CD AND %EffdtCheck(PSXLATITEMLANG X1, X, %CurrentDateIn)), ( SELECT RR.DESCR50 FROM PS_REG_REGION_LANG RR WHERE RR.REG_REGION = JOB.REG_REGION AND RR.LANGUAGE_CD = L.LANGUAGE_CD), ( SELECT SA.DESCR FROM PS_SAL_PLAN_LANG SA WHERE SA.SETID = JOB.SETID_SALARY AND SA.SAL_ADMIN_PLAN = JOB.SAL_ADMIN_PLAN AND SA.LANGUAGE_CD = L.LANGUAGE_CD AND %EffdtCheck(SAL_PLAN_LANG SA1, SA, %CurrentDateIn)), ( SELECT SUPL.NAME_DISPLAY FROM PS_PERSONAL_DTALNG SUPL WHERE SUPL.EMPLID = JOB.SUPERVISOR_ID AND SUPL.LANGUAGE_CD = L.LANGUAGE_CD), ( SELECT A6.DESCR FROM PS_POSN_DATA_LANG A6 WHERE JOB.REPORTS_TO = A6.POSITION_NBR AND A6.LANGUAGE_CD = L.LANGUAGE_CD AND %EffdtCheck(POSN_DATA_LANG A61, A6, %CurrentDateIn)), (CASE WHEN JOB.ACTION = 'XFR' THEN ( SELECT MESSAGE_TEXT FROM PSMSGCATLANG WHERE MESSAGE_SET_NBR = 1000 AND LANGUAGE_CD = L.LANGUAGE_CD AND MESSAGE_NBR = 30164) ELSE ( SELECT ACT.ACTION_DESCR FROM PS_ACTION_LNG ACT WHERE ACT.ACTION = JOB.ACTION AND ACT.LANGUAGE_CD = L.LANGUAGE_CD AND %EffdtCheck(ACTION_LNG ACT1, ACT, %CurrentDateIn)) END), ( SELECT A7.XLATLONGNAME FROM PSXLATITEMLANG A7 WHERE A7.FIELDNAME = 'FULL_PART_TIME' AND A7.FIELDVALUE = JOB.FULL_PART_TIME AND A7.LANGUAGE_CD = L.LANGUAGE_CD AND %EffdtCheck(PSXLATITEMLANG A71, A7, %CurrentDateIn)), (CASE H.HR_DR_LEVEL WHEN 1 THEN ( SELECT MESSAGE_TEXT FROM PSMSGCATLANG WHERE MESSAGE_SET_NBR = 1000 AND LANGUAGE_CD = L.LANGUAGE_CD AND MESSAGE_NBR = 30168) ELSE ( SELECT MESSAGE_TEXT FROM PSMSGCATLANG WHERE MESSAGE_SET_NBR = 1000 AND LANGUAGE_CD = L.LANGUAGE_CD AND MESSAGE_NBR = 30162) END), ( SELECT JCODE.DESCR FROM PS_JOBCODE_LANG JCODE WHERE JCODE.SETID = JOB.SETID_JOBCODE AND JCODE.JOBCODE = JOB.JOBCODE AND JCODE.LANGUAGE_CD = L.LANGUAGE_CD AND %EffdtCheck(JOBCODE_LANG JCODE1, JCODE, %CurrentDateIn)), ( SELECT A6.DESCR FROM PS_POSN_DATA_LANG A6 WHERE JOB.POSITION_NBR = A6.POSITION_NBR AND A6.LANGUAGE_CD = L.LANGUAGE_CD AND %EffdtCheck(POSN_DATA_LANG A61, A6, %CurrentDateIn)), ( SELECT DEPT.DESCR FROM PS_DEPT_TBL_LANG DEPT WHERE JOB.SETID_DEPT = DEPT.SETID AND JOB.DEPTID = DEPT.DEPTID AND DEPT.LANGUAGE_CD = L.LANGUAGE_CD AND %EffdtCheck(DEPT_TBL_LANG DEPT1, DEPT, %CurrentDateIn)), ( SELECT A12.DESCR FROM PS_ESTAB_TBL_LANG A12 WHERE A12.ESTABID = JOB.ESTABID AND A12.LANGUAGE_CD = L.LANGUAGE_CD AND %EffdtCheck(ESTAB_TBL_LANG A121, A12, %CurrentDateIn)), ( SELECT LOCN.DESCR FROM PS_LOCATION_LANG LOCN WHERE JOB.SETID_LOCATION = LOCN.SETID AND JOB.LOCATION = LOCN.LOCATION AND LOCN.LANGUAGE_CD = L.LANGUAGE_CD AND %EffdtCheck(LOCATION_LANG LOCN1, LOCN, %CurrentDateIn)), O.OPRID FROM PS_JOB JOB, PS_HR_DIRECT_REP_3 H, PSOPRDEFN O, PSLANGUAGES L WHERE JOB.EMPLID = H.EMPLID AND JOB.EMPL_RCD = H.EMPL_RCD AND H.DRILL_DOWN_FLAG = 'Y' AND '3' = ( SELECT J.ACCESS_TYPE FROM PS_SS_LINK_TBL J WHERE J.PNLGRPNAME = 'UX_DIRECTS_PLT' AND J.MARKET = 'GBL') AND H.SUPERVISOR_ID = O.EMPLID AND JOB.ACTION IN ('ADD','ADL','ASC','ASG','DEM','HIR','LOA','LOF','LTD','LTO','PLA','PLV','POI','PRO','REC','REH','RET','RFA','RFD','RFL','RNW','RTS','RWB','RWP','SF4','SF5','SF7','SFE','SFF','SFG','SFI','SFK','SFL','SFN','SFP','SFR','SFS','SFT','SFV','STD','STO','SUS','SWB','TAS','TDL','TER','TWB','TWP','XFR') AND L.INSTALLED = 1 AND L.LANGUAGE_CD <> ( SELECT OP.LANGUAGE_CD FROM PSOPTIONS OP) UNION ALL SELECT JOB.EMPLID , JOB.EMPL_RCD , BB.EFFDT , L.LANGUAGE_CD , ( SELECT PER_DL.NAME_DISPLAY FROM PS_PERSONAL_DTALNG PER_DL WHERE PER_DL.EMPLID = JOB.EMPLID AND PER_DL.LANGUAGE_CD = L.LANGUAGE_CD), ( SELECT X.XLATLONGNAME FROM PSXLATITEMLANG X WHERE X.FIELDNAME = 'HR_STATUS' AND X.FIELDVALUE = JOB.HR_STATUS AND X.LANGUAGE_CD = L.LANGUAGE_CD AND %EffdtCheck(PSXLATITEMLANG X1, X, %CurrentDateIn)), ( SELECT RR.DESCR50 FROM PS_REG_REGION_LANG RR WHERE RR.REG_REGION = JOB.REG_REGION AND RR.LANGUAGE_CD = L.LANGUAGE_CD), ( SELECT SA.DESCR FROM PS_SAL_PLAN_LANG SA WHERE SA.SETID = JOB.SETID_SALARY AND SA.SAL_ADMIN_PLAN = JOB.SAL_ADMIN_PLAN AND SA.LANGUAGE_CD = L.LANGUAGE_CD AND %EffdtCheck(SAL_PLAN_LANG SA1, SA, %CurrentDateIn)), ( SELECT SUPL.NAME_DISPLAY FROM PS_PERSONAL_DTALNG SUPL WHERE SUPL.EMPLID = JOB.SUPERVISOR_ID AND SUPL.LANGUAGE_CD = L.LANGUAGE_CD), ( SELECT A6.DESCR FROM PS_POSN_DATA_LANG A6 WHERE JOB.REPORTS_TO = A6.POSITION_NBR AND A6.LANGUAGE_CD = L.LANGUAGE_CD AND %EffdtCheck(POSN_DATA_LANG A61, A6, %CurrentDateIn)), ( SELECT MESSAGE_TEXT FROM PSMSGCATDEFN WHERE MESSAGE_SET_NBR = 1000 AND MESSAGE_NBR = 30163), ( SELECT A7.XLATLONGNAME FROM PSXLATITEMLANG A7 WHERE A7.FIELDNAME = 'FULL_PART_TIME' AND A7.FIELDVALUE = JOB.FULL_PART_TIME AND A7.LANGUAGE_CD = L.LANGUAGE_CD AND %EffdtCheck(PSXLATITEMLANG A71, A7, %CurrentDateIn)), (CASE H.HR_DR_LEVEL WHEN 1 THEN ( SELECT MESSAGE_TEXT FROM PSMSGCATLANG WHERE MESSAGE_SET_NBR = 1000 AND LANGUAGE_CD = L.LANGUAGE_CD AND MESSAGE_NBR = 30168) ELSE ( SELECT MESSAGE_TEXT FROM PSMSGCATLANG WHERE MESSAGE_SET_NBR = 1000 AND LANGUAGE_CD = L.LANGUAGE_CD AND MESSAGE_NBR = 30162) END), ( SELECT JCODE.DESCR FROM PS_JOBCODE_LANG JCODE WHERE JCODE.SETID = JOB.SETID_JOBCODE AND JCODE.JOBCODE = JOB.JOBCODE AND JCODE.LANGUAGE_CD = L.LANGUAGE_CD AND %EffdtCheck(JOBCODE_LANG JCODE1, JCODE, %CurrentDateIn)), ( SELECT A6.DESCR FROM PS_POSN_DATA_LANG A6 WHERE JOB.POSITION_NBR = A6.POSITION_NBR AND A6.LANGUAGE_CD = L.LANGUAGE_CD AND %EffdtCheck(POSN_DATA_LANG A61, A6, %CurrentDateIn)), ( SELECT DEPT.DESCR FROM PS_DEPT_TBL_LANG DEPT WHERE JOB.SETID_DEPT = DEPT.SETID AND JOB.DEPTID = DEPT.DEPTID AND DEPT.LANGUAGE_CD = L.LANGUAGE_CD AND %EffdtCheck(DEPT_TBL_LANG DEPT1, DEPT, %CurrentDateIn)), ( SELECT A12.DESCR FROM PS_ESTAB_TBL_LANG A12 WHERE A12.ESTABID = JOB.ESTABID AND A12.LANGUAGE_CD = L.LANGUAGE_CD AND %EffdtCheck(ESTAB_TBL_LANG A121, A12, %CurrentDateIn)), ( SELECT LOCN.DESCR FROM PS_LOCATION_LANG LOCN WHERE JOB.SETID_LOCATION = LOCN.SETID AND JOB.LOCATION = LOCN.LOCATION AND LOCN.LANGUAGE_CD = L.LANGUAGE_CD AND %EffdtCheck(LOCATION_LANG LOC N1, LOCN, %CurrentDateIn)), O.OPRID FROM PS_JOB JOB, PS_JOB BB, PS_HR_DIRECT_REP_3 H, PSOPRDEFN O, PSLANGUAGES L WHERE JOB.EMPLID = BB.EMPLID AND JOB.EMPL_RCD = BB.EMPL_RCD AND JOB.EFFSEQ = BB.EFFSEQ AND (BB.ACTION = 'XFR' OR (BB.ACTION = 'POS' AND BB.ACTION_REASON = 'XFR')) AND JOB.EMPLID = H.EMPLID AND JOB.EMPL_RCD = H.EMPL_RCD AND H.SUPERVISOR_ID = O.EMPLID AND H.DRILL_DOWN_FLAG = 'Y' AND '3' = ( SELECT J.ACCESS_TYPE FROM PS_SS_LINK_TBL J WHERE J.PNLGRPNAME = 'UX_DIRECTS_PLT' AND J.MARKET = 'GBL') AND JOB.EFFDT = ( SELECT MAX(AA1.EFFDT) FROM PS_JOB AA1 WHERE AA1.EMPLID = BB.EMPLID AND AA1.EMPL_RCD = BB.EMPL_RCD AND AA1.EFFSEQ = BB.EFFSEQ AND AA1.EFFDT < BB.EFFDT ) AND L.INSTALLED = 1 AND L.LANGUAGE_CD <> ( SELECT OP.LANGUAGE_CD FROM PSOPTIONS OP) UNION ALL SELECT JOB.EMPLID , JOB.EMPL_RCD , JOB.EFFDT , L.LANGUAGE_CD , ( SELECT PER_DL.NAME_DISPLAY FROM PS_PERSONAL_DTALNG PER_DL WHERE PER_DL.EMPLID = JOB.EMPLID AND PER_DL.LANGUAGE_CD = L.LANGUAGE_CD), ( SELECT X.XLATLONGNAME FROM PSXLATITEMLANG X WHERE X.FIELDNAME = 'HR_STATUS' AND X.FIELDVALUE = JOB.HR_STATUS AND X.LANGUAGE_CD = L.LANGUAGE_CD AND %EffdtCheck(PSXLATITEMLANG X1, X, %CurrentDateIn)), ( SELECT RR.DESCR50 FROM PS_REG_REGION_LANG RR WHERE RR.REG_REGION = JOB.REG_REGION AND RR.LANGUAGE_CD = L.LANGUAGE_CD), ( SELECT SA.DESCR FROM PS_SAL_PLAN_LANG SA WHERE SA.SETID = JOB.SETID_SALARY AND SA.SAL_ADMIN_PLAN = JOB.SAL_ADMIN_PLAN AND SA.LANGUAGE_CD = L.LANGUAGE_CD AND %EffdtCheck(SAL_PLAN_LANG SA1, SA, %CurrentDateIn)), ( SELECT SUPL.NAME_DISPLAY FROM PS_PERSONAL_DTALNG SUPL WHERE SUPL.EMPLID = JOB.SUPERVISOR_ID AND SUPL.LANGUAGE_CD = L.LANGUAGE_CD), ( SELECT A6.DESCR FROM PS_POSN_DATA_LANG A6 WHERE JOB.REPORTS_TO = A6.POSITION_NBR AND A6.LANGUAGE_CD = L.LANGUAGE_CD AND %EffdtCheck(POSN_DATA_LANG A61, A6, %CurrentDateIn)), (CASE WHEN JOB.ACTION = 'XFR' THEN ( SELECT MESSAGE_TEXT FROM PSMSGCATLANG WHERE MESSAGE_SET_NBR = 1000 AND LANGUAGE_CD = L.LANGUAGE_CD AND MESSAGE_NBR = 30164) ELSE ( SELECT ACT.ACTION_DESCR FROM PS_ACTION_LNG ACT WHERE ACT.ACTION = JOB.ACTION AND ACT.LANGUAGE_CD = L.LANGUAGE_CD AND %EffdtCheck(ACTION_LNG ACT1, ACT, %CurrentDateIn)) END), ( SELECT A7.XLATLONGNAME FROM PSXLATITEMLANG A7 WHERE A7.FIELDNAME = 'FULL_PART_TIME' AND A7.FIELDVALUE = JOB.FULL_PART_TIME AND A7.LANGUAGE_CD = L.LANGUAGE_CD AND %EffdtCheck(PSXLATITEMLANG A71, A7, %CurrentDateIn)), (CASE H.HR_DR_LEVEL WHEN 1 THEN ( SELECT MESSAGE_TEXT FROM PSMSGCATLANG WHERE MESSAGE_SET_NBR = 1000 AND LANGUAGE_CD = L.LANGUAGE_CD AND MESSAGE_NBR = 30168) ELSE ( SELECT MESSAGE_TEXT FROM PSMSGCATLANG WHERE MESSAGE_SET_NBR = 1000 AND LANGUAGE_CD = L.LANGUAGE_CD AND MESSAGE_NBR = 30162) END), ( SELECT JCODE.DESCR FROM PS_JOBCODE_LANG JCODE WHERE JCODE.SETID = JOB.SETID_JOBCODE AND JCODE.JOBCODE = JOB.JOBCODE AND JCODE.LANGUAGE_CD = L.LANGUAGE_CD AND %EffdtCheck(JOBCODE_LANG JCODE1, JCODE, %CurrentDateIn)), ( SELECT A6.DESCR FROM PS_POSN_DATA_LANG A6 WHERE JOB.POSITION_NBR = A6.POSITION_NBR AND A6.LANGUAGE_CD = L.LANGUAGE_CD AND %EffdtCheck(POSN_DATA_LANG A61, A6, %CurrentDateIn)), ( SELECT DEPT.DESCR FROM PS_DEPT_TBL_LANG DEPT WHERE JOB.SETID_DEPT = DEPT.SETID AND JOB.DEPTID = DEPT.DEPTID AND DEPT.LANGUAGE_CD = L.LANGUAGE_CD AND %EffdtCheck(DEPT_TBL_LANG DEPT1, DEPT, %CurrentDateIn)), ( SELECT A12.DESCR FROM PS_ESTAB_TBL_LANG A12 WHERE A12.ESTABID = JOB.ESTABID AND A12.LANGUAGE_CD = L.LANGUAGE_CD AND %EffdtCheck(ESTAB_TBL_LANG A121, A12, %CurrentDateIn)), ( SELECT LOCN.DESCR FROM PS_LOCATION_LANG LOCN WHERE JOB.SETID_LOCATION = LOCN.SETID AND JOB.LOCATION = LOCN.LOCATION AND LOCN.LANGUAGE_CD = L.LANGUAGE_CD AND %EffdtCheck(LOCATION_LANG LOCN1, LOCN, %CurrentDateIn)), O.OPRID FROM PS_JOB JOB, PS_HR_DIRECT_REP_4 H, PSOPRDEFN O, PSLANGUAGES L WHERE JOB.EMPLID = H.EMPLID AND JOB.EMPL_RCD = H.EMPL_RCD AND H.DRILL_DOWN_FLAG = 'Y' AND '4' = ( SELECT J.ACCESS_TYPE FROM PS_SS_LINK_TBL J WHERE J.PNLGRPNAME = 'UX_DIRECTS_PLT' AND J.MARKET = 'GBL') AND H.SUPERVISOR_ID = O.EMPLID AND JOB.ACTION IN ('ADD','ADL','ASC','ASG','DEM','HIR','LOA','LOF','LTD','LTO','PLA','PLV','POI','PRO','REC','REH','RET','RFA','RFD','RFL','RNW','RTS','RWB','RWP','SF4','SF5','SF7','SFE','SFF','SFG','SFI','SFK','SFL','SFN','SFP','SFR','SFS','SFT','SFV','STD','STO','SUS','SWB','TAS','TDL','TER','TWB','TWP','XFR') AND L.INSTALLED = 1 AND L.LANGUAGE_CD <> ( SELECT OP.LANGUAGE_CD FROM PSOPTIONS OP) UNION ALL SELECT JOB.EMPLID , JOB.EMPL_RCD , BB.EFFDT , L.LANGUAGE_CD , ( SELECT PER_DL.NAME_DISPLAY FROM PS_PERSONAL_DTALNG PER_DL WHERE PER_DL.EMPLID = JOB.EMPLID AND PER_DL.LANGUAGE_CD = L.LANGUAGE_CD), ( SELECT X.XLATLONGNAME FROM PSXLATITEMLANG X WHERE X.FIELDNAME = 'HR_STATUS' AND X.FIELDVALUE = JOB.HR_STATUS AND X.LANGUAGE_CD = L.LANGUAGE_CD AND %EffdtCheck(PSXLATITEMLANG X1, X, %CurrentDateIn)), ( SELECT RR.DESCR50 FROM PS_REG_REGION_LANG RR WHERE RR.REG_REGION = JOB.REG_REGION AND RR.LANGUAGE_CD = L.LANGUAGE_CD), ( SELECT SA.DESCR FROM PS_SAL_PLAN_LANG SA WHERE SA.SETID = JOB.SETID_SALARY AND SA.SAL_ADMIN_PLAN = JOB.SAL_ADMIN_PLAN AND SA.LANGUAGE_CD = L.LANGUAGE_CD AND %EffdtCheck(SAL_PLAN_LANG SA1, SA, %CurrentDateIn)), ( SELECT SUPL.NAME_DISPLAY FROM PS_PERSONAL_DTALNG SUPL WHERE SUPL.EMPLID = JOB.SUPERVISOR_ID AND SUPL.LANGUAGE_CD = L.LANGUAGE_CD), ( SELECT A6.DESCR FROM PS_POSN_DATA_LANG A6 WHERE JOB.REPORTS_TO = A6.POSITION_NBR AND A6.LANGUAGE_CD = L.LANGUAGE_CD AND %EffdtCheck(POSN_DATA_LANG A61, A6, %CurrentDateIn)), ( SELECT MESSAGE_TEXT FROM PSMSGCATDEFN WHERE MESSAGE_SET_NBR = 1000 AND MESSAGE_NBR = 30163), ( SELECT A7.XLATLONGNAME FROM PSXLATITEMLANG A7 WHERE A7.FIELDNAME = 'FULL_PART_TIME' AND A7.FIELDVALUE = JOB.FULL_PART_TIME AND A7.LANGUAGE_CD = L.LANGUAGE_CD AND %EffdtCheck(PSXLATITEMLANG A71, A7, %CurrentDateIn)), (CASE H.HR_DR_LEVEL WHEN 1 THEN ( SELECT MESSAGE_TEXT FROM PSMSGCATLANG WHERE MESSAGE_SET_NBR = 1000 AND LANGUAGE_CD = L.LANGUAGE_CD AND MESSAGE_NBR = 30168) ELSE ( SELECT MESSAGE_TEXT FROM PSMSGCATLANG WHERE MESSAGE_SET_NBR = 1000 AND LANGUAGE_CD = L.LANGUAGE_CD AND MESSAGE_NBR = 30162) END), ( SELECT JCODE.DESCR FROM PS_JOBCODE_LANG JCODE WHERE JCODE.SETID = JOB.SETID_JOBCODE AND JCODE.JOBCODE = JOB.JOBCODE AND JCODE.LANGUAGE_CD = L.LANGUAGE_CD AND %EffdtCheck(JOBCODE_LANG JCODE1, JCODE, %CurrentDateIn)), ( SELECT A6.DESCR FROM PS_POSN_DATA_LANG A6 WHERE JOB.POSITION_NBR = A6.POSITION_NBR AND A6.LANGUAGE_CD = L.LANGUAGE_CD AND %EffdtCheck(POSN_DATA_LANG A61, A6, %CurrentDateIn)), ( SELECT DEPT.DESCR FROM PS_DEPT_TBL_LANG DEPT WHERE JOB.SETID_DEPT = DEPT.SETID AND JOB.DEPTID = DEPT.DEPTID AND DEPT.LANGUAGE_CD = L.LANGUAGE_CD AND %EffdtCheck(DEPT_TBL_LANG DEPT1, DEPT, %CurrentDateIn)), ( SELECT A12.DESCR FROM PS_ESTAB_TBL_LANG A12 WHERE A12.ESTABID = JOB.ESTABID AND A12.LANGUAGE_CD = L.LANGUAGE_CD AND %EffdtCheck(ESTAB_TBL_LANG A121, A12, %CurrentDateIn)), ( SELECT LOCN.DESCR FROM PS_LOCATION_LANG LOCN WHERE JOB.SETID_LOCATION = LOCN.SETID AND JOB.LOCATION = LOCN.LOCATION AND LOCN.LANGUAGE_CD = L.LANGUAGE_CD AND %EffdtCheck(LOCATION_LANG LOCN1, LOCN, %CurrentDateIn)), O.OPRID FROM PS_JOB JOB, PS_JOB BB, PS_HR_DIRECT_REP_4 H, PSOPRDEFN O, PSLANGUAGES L WHERE JOB.EMPLID = BB.EMPLID AND JOB.EMPL_RCD = BB.EMPL_RCD AND JOB.EFFSEQ = BB.EFFSEQ AND (BB.ACTION = 'XFR' OR (BB.ACTION = 'POS' AND BB.ACTION_REASON = 'XFR')) AND JOB.EMPLID = H.EMPLID AND JOB.EMPL_RCD = H.EMPL_RCD AND H.SUPERVISOR_ID = O.EMPLID AND H.DRILL_DOWN_FLAG = 'Y' AND '4' = ( SELECT J.ACCESS_TYPE FROM PS_SS_LINK_TBL J WHERE J.PNLGRPNAME = 'UX_DIRECTS_PLT' AND J.MARKET = 'GBL') AND JOB.EFFDT = ( SELECT MAX(AA1.EFFDT) FROM PS_JOB AA1 WHERE AA1.EMPLID = BB.EMPLID AND AA1.EMPL_RCD = BB.EMPL_RCD AND AA1.EFFSEQ = BB.EFFSEQ AND AA1.EFFDT < BB.EFFDT ) AND L.INSTALLED = 1 AND L.LANGUAGE_CD <> ( SELECT OP.LANGUAGE_CD FROM PSOPTIONS OP) UNION ALL SELECT JOB.EMPLID , JOB.EMPL_RCD , JOB.EFFDT , L.LANGUAGE_CD , ( SELECT PER_DL.NAME_DISPLAY FROM PS_PERSONAL_DTALNG PER_DL WHERE PER_DL.EMPLID = JOB.EMPLID AND PER_DL.LANGUAGE_CD = L.LANGUAGE_CD), ( SELECT X.XLATLONGNAME FROM PSXLATITEMLANG X WHERE X.FIELDNAME = 'HR_STATUS' AND X.FIELDVALUE = JOB.HR_STATUS AND X.LANGUAGE_CD = L.LANGUAGE_CD AND %EffdtCheck(PSXLATITEMLANG X1, X, %CurrentDateIn)), ( SELECT RR.DESCR50 FROM PS_REG_REGION_LANG RR WHERE RR.REG_REGION = JOB.REG_REGION AND RR.LANGUAGE_CD = L.LANGUAGE_CD), ( SELECT SA.DESCR FROM PS_SAL_PLAN_LANG SA WHERE SA.SETID = JOB.SETID_SALARY AND SA.SAL_ADMIN_PLAN = JOB.SAL_ADMIN_PLAN AND SA.LANGUAGE_CD = L.LANGUAGE_CD AND %EffdtCheck(SAL_PLAN_LANG SA1, SA, %CurrentDateIn)), ( SELECT SUPL.NAME_DISPLAY FROM PS_PERSONAL_DTALNG SUPL WHERE SUPL.EMPLID = JOB.SUPERVISOR_ID AND SUPL.LANGUAGE_CD = L.LANGUAGE_CD), ( SELECT A6.DESCR FROM PS_POSN_DATA_LANG A6 WHERE JOB.REPORTS_TO = A6.POSITION_NBR AND A6.LANGUAGE_CD = L.LANGUAGE_CD AND %EffdtCheck(POSN_DATA_LANG A61, A6, %CurrentDateIn)), (CASE WHEN JOB.ACTION = 'XFR' THEN ( SELECT MESSAGE_TEXT FROM PSMSGCATLANG WHERE MESSAGE_SET_NBR = 1000 AND LANGUAGE_CD = L.LANGUAGE_CD AND MESSAGE_NBR = 30164) ELSE ( SELECT ACT.ACTION_DESCR FROM PS_ACTION_LNG ACT WHERE ACT.ACTION = JOB.ACTION AND ACT.LANGUAGE_CD = L.LANGUAGE_CD AND %EffdtCheck(ACTION_LNG ACT1, ACT, %CurrentDateIn)) END), ( SELECT A7.XLATLONGNAME FROM PSXLATITEMLANG A7 WHERE A7.FIELDNAME = 'FULL_PART_TIME' AND A7.FIELDVALUE = JOB.FULL_PART_TIME AND A7.LANGUAGE_CD = L.LANGUAGE_CD AND %EffdtCheck(PSXLATITEMLANG A71, A7, %CurrentDateIn)), (CASE H.HR_DR_LEVEL WHEN 1 THEN ( SELECT MESSAGE_TEXT FROM PSMSGCATLANG WHERE MESSAGE_SET_NBR = 1000 AND LANGUAGE_CD = L.LANGUAGE_CD AND MESSAGE_NBR = 30168) ELSE ( SELECT MESSAGE_TEXT FROM PSMSGCATLANG WHERE MESSAGE_SET_NBR = 1000 AND LANGUAGE_CD = L.LANGUAGE_CD AND MESSAGE_NBR = 30162) END), ( SELECT JCODE.DESCR FROM PS_JOBCODE_LANG JCODE WHERE JCODE.SETID = JOB.SETID_JOBCODE AND JCODE.JOBCODE = JOB.JOBCODE AND JCODE.LANGUAGE_CD = L.LANGUAGE_CD AND %EffdtCheck(JOBCODE_LANG JCODE1, JCODE, %CurrentDateIn)), ( SELECT A6.DESCR FROM PS_POSN_DATA_LANG A6 WHERE JOB.POSITION_NBR = A6.POSITION_NBR AND A6.LANGUAGE_CD = L.LANGUAGE_CD AND %EffdtCheck(POSN_DATA_LANG A61, A6, %CurrentDateIn)), ( SELECT DEPT.DESCR FROM PS_DEPT_TBL_LANG DEPT WHERE JOB.SETID_DEPT = DEPT.SETID AND JOB.DEPTID = DEPT.DEPTID AND DEPT.LANGUAGE_CD = L.LANGUAGE_CD AND %EffdtCheck(DEPT_TBL_LANG DEPT1, DEPT, %CurrentDateIn)), ( SELECT A12.DESCR FROM PS_ESTAB_TBL_LANG A12 WHERE A12.ESTABID = JOB.ESTABID AND A12.LANGUAGE_CD = L.LANGUAGE_CD AND %EffdtCheck(ESTAB_TBL_LANG A121, A12, %CurrentDateIn)), ( SELECT LOCN.DESCR FROM PS_LOCATION_LANG LOCN WHERE JOB.SETID_LOCATION = LOCN.SETID AND JOB.LOCATION = LOCN.LOCATION AND LOCN.LANGUAGE_CD = L.LANGUAGE_CD AND %EffdtCheck(LOCATION_LANG LOCN1, LOCN, %CurrentDateIn)), O.OPRID FROM PS_JOB JOB, PS_HR_DIRECT_REP_5 H, PSOPRDEFN O, PSLANGUAGES L WHERE JOB.EMPLID = H.EMPLID AND JOB.EMPL_RCD = H.EMPL_RCD AND H.DRILL_DOWN_FLAG = 'Y' AND '5' = ( SELECT J.ACCESS_TYPE FROM PS_SS_LINK_TBL J WHERE J.PNLGRPNAME = 'UX_DIRECTS_PLT' AND J.MARKET = 'GBL') AND H.SUPERVISOR_ID = O.EMPLID AND JOB.ACTION IN ('ADD','ADL','ASC','ASG','DEM','HIR','LOA','LOF','LTD','LTO','PLA','PLV','POI','PRO','REC','REH','RET','RFA','RFD','RFL','RNW','RTS','RWB','RWP','SF4','SF5','SF7','SFE','SFF','SFG','SFI','SFK','SFL','SFN','SFP','SFR','SFS','SFT','SFV','STD','STO','SUS','SWB','TAS','TDL','TER','TWB','TWP','XFR') AND L.INSTALLED = 1 AND L.LANGUAGE_CD <> ( SELECT OP.LANGUAGE_CD FROM PSOPTIONS OP) UNION ALL SELECT JOB.EMPLID , JOB.EMPL_RCD , BB.EFFDT , L.LANGUAGE_CD , ( SELECT PER_DL.NAME_DISPLAY FROM PS_PERSONAL_DTALNG PER_DL WHERE PER_DL.EMPLID = JOB.EMPLID AND PER_DL.LANGUAGE_CD = L.LANGUAGE_CD), ( SELECT X.XLATLONGNAME FROM PSXLATITEMLANG X WHERE X.FIELDNAME = 'HR_STATUS' AND X.FIELDVALUE = JOB.HR_STATUS AND X.LANGUAGE_CD = L.LANGUAGE_CD AND %EffdtCheck(PSXLATITEMLANG X1, X, %CurrentDateIn)), ( SELECT RR.DESCR50 FROM PS_REG_REGION_LANG RR WHERE RR.REG_REGION = JOB.REG_REGION AND RR.LANGUAGE_CD = L.LANGUAGE_CD), ( SELECT SA.DESCR FROM PS_SAL_PLAN_LANG SA WHERE SA.SETID = JOB.SETID_SALARY AND SA.SAL_ADMIN_PLAN = JOB.SAL_ADMIN_PLAN AND SA.LANGUAGE_CD = L.LANGUAGE_CD AND %EffdtCheck(SAL_PLAN_LANG SA1, SA, %CurrentDateIn)), ( SELECT SUPL.NAME_DISPLAY FROM PS_PERSONAL_DTALNG SUPL WHERE SUPL.EMPLID = JOB.SUPERVISOR_ID AND SUPL.LANGUAGE_CD = L.LANGUAGE_CD), ( SELECT A6.DESCR FROM PS_POSN_DATA_LANG A6 WHERE JOB.REPORTS_TO = A6.POSITION_NBR AND A6.LANGUAGE_CD = L.LANGUAGE_CD AND %EffdtCheck(POSN_DATA_LANG A61, A6, %CurrentDateIn)), ( SELECT MESSAGE_TEXT FROM PSMSGCATDEFN WHERE MESSAGE_SET_NBR = 1000 AND MESSAGE_NBR = 30163), ( SELECT A7.XLATLONGNAME FROM PSXLATITEMLANG A7 WHERE A7.FIELDNAME = 'FULL_PART_TIME' AND A7.FIELDVALUE = JOB.FULL_PART_TIME AND A7.LANGUAGE_CD = L.LANGUAGE_CD AND %EffdtCheck(PSXLATITEMLANG A71, A7, %CurrentDateIn)), (CASE H.HR_DR_LEVEL WHEN 1 THEN ( SELECT MESSAGE_TEXT FROM PSMSGCATLANG WHERE MESSAGE_SET_NBR = 1000 AND LANGUAGE_CD = L.LANGUAGE_CD AND MESSAGE_NBR = 30168) ELSE ( SELECT MESSAGE_TEXT FROM PSMSGCATLANG WHERE MESSAGE_SET_NBR = 1000 AND LANGUAGE_CD = L.LANGUAGE_CD AND MESSAGE_NBR = 30162) END), ( SELECT JCODE.DESCR FROM PS_JOBCODE_LANG JCODE WHERE JCODE.SETID = JOB.SETID_JOBCODE AND JCODE.JOBCODE = JOB.JOBCODE AND JCODE.LANGUAGE_CD = L.LANGUAGE_CD AND %EffdtCheck(JOBCODE_LA NG JCODE1, JCODE, %CurrentDateIn)), ( SELECT A6.DESCR FROM PS_POSN_DATA_LANG A6 WHERE JOB.POSITION_NBR = A6.POSITION_NBR AND A6.LANGUAGE_CD = L.LANGUAGE_CD AND %EffdtCheck(POSN_DATA_LANG A61, A6, %CurrentDateIn)), ( SELECT DEPT.DESCR FROM PS_DEPT_TBL_LANG DEPT WHERE JOB.SETID_DEPT = DEPT.SETID AND JOB.DEPTID = DEPT.DEPTID AND DEPT.LANGUAGE_CD = L.LANGUAGE_CD AND %EffdtCheck(DEPT_TBL_LANG DEPT1, DEPT, %CurrentDateIn)), ( SELECT A12.DESCR FROM PS_ESTAB_TBL_LANG A12 WHERE A12.ESTABID = JOB.ESTABID AND A12.LANGUAGE_CD = L.LANGUAGE_CD AND %EffdtCheck(ESTAB_TBL_LANG A121, A12, %CurrentDateIn)), ( SELECT LOCN.DESCR FROM PS_LOCATION_LANG LOCN WHERE JOB.SETID_LOCATION = LOCN.SETID AND JOB.LOCATION = LOCN.LOCATION AND LOCN.LANGUAGE_CD = L.LANGUAGE_CD AND %EffdtCheck(LOCATION_LANG LOCN1, LOCN, %CurrentDateIn)), O.OPRID FROM PS_JOB JOB, PS_JOB BB, PS_HR_DIRECT_REP_5 H, PSOPRDEFN O, PSLANGUAGES L WHERE JOB.EMPLID = BB.EMPLID AND JOB.EMPL_RCD = BB.EMPL_RCD AND JOB.EFFSEQ = BB.EFFSEQ AND (BB.ACTION = 'XFR' OR (BB.ACTION = 'POS' AND BB.ACTION_REASON = 'XFR')) AND JOB.EMPLID = H.EMPLID AND JOB.EMPL_RCD = H.EMPL_RCD AND H.SUPERVISOR_ID = O.EMPLID AND H.DRILL_DOWN_FLAG = 'Y' AND '5' = ( SELECT J.ACCESS_TYPE FROM PS_SS_LINK_TBL J WHERE J.PNLGRPNAME = 'UX_DIRECTS_PLT' AND J.MARKET = 'GBL') AND JOB.EFFDT = ( SELECT MAX(AA1.EFFDT) FROM PS_JOB AA1 WHERE AA1.EMPLID = BB.EMPLID AND AA1.EMPL_RCD = BB.EMPL_RCD AND AA1.EFFSEQ = BB.EFFSEQ AND AA1.EFFDT < BB.EFFDT ) AND L.INSTALLED = 1 AND L.LANGUAGE_CD <> ( SELECT OP.LANGUAGE_CD FROM PSOPTIONS OP) UNION ALL SELECT JOB.EMPLID , JOB.EMPL_RCD , JOB.EFFDT , L.LANGUAGE_CD , ( SELECT PER_DL.NAME_DISPLAY FROM PS_PERSONAL_DTALNG PER_DL WHERE PER_DL.EMPLID = JOB.EMPLID AND PER_DL.LANGUAGE_CD = L.LANGUAGE_CD), ( SELECT X.XLATLONGNAME FROM PSXLATITEMLANG X WHERE X.FIELDNAME = 'HR_STATUS' AND X.FIELDVALUE = JOB.HR_STATUS AND X.LANGUAGE_CD = L.LANGUAGE_CD AND %EffdtCheck(PSXLATITEMLANG X1, X, %CurrentDateIn)), ( SELECT RR.DESCR50 FROM PS_REG_REGION_LANG RR WHERE RR.REG_REGION = JOB.REG_REGION AND RR.LANGUAGE_CD = L.LANGUAGE_CD), ( SELECT SA.DESCR FROM PS_SAL_PLAN_LANG SA WHERE SA.SETID = JOB.SETID_SALARY AND SA.SAL_ADMIN_PLAN = JOB.SAL_ADMIN_PLAN AND SA.LANGUAGE_CD = L.LANGUAGE_CD AND %EffdtCheck(SAL_PLAN_LANG SA1, SA, %CurrentDateIn)), ( SELECT SUPL.NAME_DISPLAY FROM PS_PERSONAL_DTALNG SUPL WHERE SUPL.EMPLID = JOB.SUPERVISOR_ID AND SUPL.LANGUAGE_CD = L.LANGUAGE_CD), ( SELECT A6.DESCR FROM PS_POSN_DATA_LANG A6 WHERE JOB.REPORTS_TO = A6.POSITION_NBR AND A6.LANGUAGE_CD = L.LANGUAGE_CD AND %EffdtCheck(POSN_DATA_LANG A61, A6, %CurrentDateIn)), (CASE WHEN JOB.ACTION = 'XFR' THEN ( SELECT MESSAGE_TEXT FROM PSMSGCATLANG WHERE MESSAGE_SET_NBR = 1000 AND LANGUAGE_CD = L.LANGUAGE_CD AND MESSAGE_NBR = 30164) ELSE ( SELECT ACT.ACTION_DESCR FROM PS_ACTION_LNG ACT WHERE ACT.ACTION = JOB.ACTION AND ACT.LANGUAGE_CD = L.LANGUAGE_CD AND %EffdtCheck(ACTION_LNG ACT1, ACT, %CurrentDateIn)) END), ( SELECT A7.XLATLONGNAME FROM PSXLATITEMLANG A7 WHERE A7.FIELDNAME = 'FULL_PART_TIME' AND A7.FIELDVALUE = JOB.FULL_PART_TIME AND A7.LANGUAGE_CD = L.LANGUAGE_CD AND %EffdtCheck(PSXLATITEMLANG A71, A7, %CurrentDateIn)), (CASE H.HR_DR_LEVEL WHEN 1 THEN ( SELECT MESSAGE_TEXT FROM PSMSGCATLANG WHERE MESSAGE_SET_NBR = 1000 AND LANGUAGE_CD = L.LANGUAGE_CD AND MESSAGE_NBR = 30168) ELSE ( SELECT MESSAGE_TEXT FROM PSMSGCATLANG WHERE MESSAGE_SET_NBR = 1000 AND LANGUAGE_CD = L.LANGUAGE_CD AND MESSAGE_NBR = 30162) END), ( SELECT JCODE.DESCR FROM PS_JOBCODE_LANG JCODE WHERE JCODE.SETID = JOB.SETID_JOBCODE AND JCODE.JOBCODE = JOB.JOBCODE AND JCODE.LANGUAGE_CD = L.LANGUAGE_CD AND %EffdtCheck(JOBCODE_LANG JCODE1, JCODE, %CurrentDateIn)), ( SELECT A6.DESCR FROM PS_POSN_DATA_LANG A6 WHERE JOB.POSITION_NBR = A6.POSITION_NBR AND A6.LANGUAGE_CD = L.LANGUAGE_CD AND %EffdtCheck(POSN_DATA_LANG A61, A6, %CurrentDateIn)), ( SELECT DEPT.DESCR FROM PS_DEPT_TBL_LANG DEPT WHERE JOB.SETID_DEPT = DEPT.SETID AND JOB.DEPTID = DEPT.DEPTID AND DEPT.LANGUAGE_CD = L.LANGUAGE_CD AND %EffdtCheck(DEPT_TBL_LANG DEPT1, DEPT, %CurrentDateIn)), ( SELECT A12.DESCR FROM PS_ESTAB_TBL_LANG A12 WHERE A12.ESTABID = JOB.ESTABID AND A12.LANGUAGE_CD = L.LANGUAGE_CD AND %EffdtCheck(ESTAB_TBL_LANG A121, A12, %CurrentDateIn)), ( SELECT LOCN.DESCR FROM PS_LOCATION_LANG LOCN WHERE JOB.SETID_LOCATION = LOCN.SETID AND JOB.LOCATION = LOCN.LOCATION AND LOCN.LANGUAGE_CD = L.LANGUAGE_CD AND %EffdtCheck(LOCATION_LANG LOCN1, LOCN, %CurrentDateIn)), O.OPRID FROM PS_JOB JOB, PS_HR_DIRECT_REP_6 H, PSOPRDEFN O, PSLANGUAGES L WHERE JOB.EMPLID = H.EMPLID AND JOB.EMPL_RCD = H.EMPL_RCD AND H.DRILL_DOWN_FLAG = 'Y' AND '6' = ( SELECT J.ACCESS_TYPE FROM PS_SS_LINK_TBL J WHERE J.PNLGRPNAME = 'UX_DIRECTS_PLT' AND J.MARKET = 'GBL') AND H.SUPERVISOR_ID = O.EMPLID AND JOB.ACTION IN ('ADD','ADL','ASC','ASG','DEM','HIR','LOA','LOF','LTD','LTO','PLA','PLV','POI','PRO','REC','REH','RET','RFA','RFD','RFL','RNW','RTS','RWB','RWP','SF4','SF5','SF7','SFE','SFF','SFG','SFI','SFK','SFL','SFN','SFP','SFR','SFS','SFT','SFV','STD','STO','SUS','SWB','TAS','TDL','TER','TWB','TWP','XFR') AND L.INSTALLED = 1 AND L.LANGUAGE_CD <> ( SELECT OP.LANGUAGE_CD FROM PSOPTIONS OP) UNION ALL SELECT JOB.EMPLID , JOB.EMPL_RCD , BB.EFFDT , L.LANGUAGE_CD , ( SELECT PER_DL.NAME_DISPLAY FROM PS_PERSONAL_DTALNG PER_DL WHERE PER_DL.EMPLID = JOB.EMPLID AND PER_DL.LANGUAGE_CD = L.LANGUAGE_CD), ( SELECT X.XLATLONGNAME FROM PSXLATITEMLANG X WHERE X.FIELDNAME = 'HR_STATUS' AND X.FIELDVALUE = JOB.HR_STATUS AND X.LANGUAGE_CD = L.LANGUAGE_CD AND %EffdtCheck(PSXLATITEMLANG X1, X, %CurrentDateIn)), ( SELECT RR.DESCR50 FROM PS_REG_REGION_LANG RR WHERE RR.REG_REGION = JOB.REG_REGION AND RR.LANGUAGE_CD = L.LANGUAGE_CD), ( SELECT SA.DESCR FROM PS_SAL_PLAN_LANG SA WHERE SA.SETID = JOB.SETID_SALARY AND SA.SAL_ADMIN_PLAN = JOB.SAL_ADMIN_PLAN AND SA.LANGUAGE_CD = L.LANGUAGE_CD AND %EffdtCheck(SAL_PLAN_LANG SA1, SA, %CurrentDateIn)), ( SELECT SUPL.NAME_DISPLAY FROM PS_PERSONAL_DTALNG SUPL WHERE SUPL.EMPLID = JOB.SUPERVISOR_ID AND SUPL.LANGUAGE_CD = L.LANGUAGE_CD), ( SELECT A6.DESCR FROM PS_POSN_DATA_LANG A6 WHERE JOB.REPORTS_TO = A6.POSITION_NBR AND A6.LANGUAGE_CD = L.LANGUAGE_CD AND %EffdtCheck(POSN_DATA_LANG A61, A6, %CurrentDateIn)), ( SELECT MESSAGE_TEXT FROM PSMSGCATDEFN WHERE MESSAGE_SET_NBR = 1000 AND MESSAGE_NBR = 30163), ( SELECT A7.XLATLONGNAME FROM PSXLATITEMLANG A7 WHERE A7.FIELDNAME = 'FULL_PART_TIME' AND A7.FIELDVALUE = JOB.FULL_PART_TIME AND A7.LANGUAGE_CD = L.LANGUAGE_CD AND %EffdtCheck(PSXLATITEMLANG A71, A7, %CurrentDateIn)), (CASE H.HR_DR_LEVEL WHEN 1 THEN ( SELECT MESSAGE_TEXT FROM PSMSGCATLANG WHERE MESSAGE_SET_NBR = 1000 AND LANGUAGE_CD = L.LANGUAGE_CD AND MESSAGE_NBR = 30168) ELSE ( SELECT MESSAGE_TEXT FROM PSMSGCATLANG WHERE MESSAGE_SET_NBR = 1000 AND LANGUAGE_CD = L.LANGUAGE_CD AND MESSAGE_NBR = 30162) END), ( SELECT JCODE.DESCR FROM PS_JOBCODE_LANG JCODE WHERE JCODE.SETID = JOB.SETID_JOBCODE AND JCODE.JOBCODE = JOB.JOBCODE AND JCODE.LANGUAGE_CD = L.LANGUAGE_CD AND %EffdtCheck(JOBCODE_LANG JCODE1, JCODE, %CurrentDateIn)), ( SELECT A6.DESCR FROM PS_POSN_DATA_LANG A6 WHERE JOB.POSITION_NBR = A6.POSITION_NBR AND A6.LANGUAGE_CD = L.LANGUAGE_CD AND %EffdtCheck(POSN_DATA_LANG A61, A6, %CurrentDateIn)), ( SELECT DEPT.DESCR FROM PS_DEPT_TBL_LANG DEPT WHERE JOB.SETID_DEPT = DEPT.SETID AND JOB.DEPTID = DEPT.DEPTID AND DEPT.LANGUAGE_CD = L.LANGUAGE_CD AND %EffdtCheck(DEPT_TBL_LANG DEPT1, DEPT, %CurrentDateIn)), ( SELECT A12.DESCR FROM PS_ESTAB_TBL_LANG A12 WHERE A12.ESTABID = JOB.ESTABID AND A12.LANGUAGE_CD = L.LANGUAGE_CD AND %EffdtCheck(ESTAB_TBL_LANG A121, A12, %CurrentDateIn)), ( SELECT LOCN.DESCR FROM PS_LOCATION_LANG LOCN WHERE JOB.SETID_LOCATION = LOCN.SETID AND JOB.LOCATION = LOCN.LOCATION AND LOCN.LANGUAGE_CD = L.LANGUAGE_CD AND %EffdtCheck(LOCATION_LANG LOCN1, LOCN, %CurrentDateIn)), O.OPRID FROM PS_JOB JOB, PS_JOB BB, PS_HR_DIRECT_REP_6 H, PSOPRDEFN O, PSLANGUAGES L WHERE JOB.EMPLID = BB.EMPLID AND JOB.EMPL_RCD = BB.EMPL_RCD AND JOB.EFFSEQ = BB.EFFSEQ AND (BB.ACTION = 'XFR' OR (BB.ACTION = 'POS' AND BB.ACTION_REASON = 'XFR')) AND JOB.EMPLID = H.EMPLID AND JOB.EMPL_RCD = H.EMPL_RCD AND H.SUPERVISOR_ID = O.EMPLID AND H.DRILL_DOWN_FLAG = 'Y' AND '6' = ( SELECT J.ACCESS_TYPE FROM PS_SS_LINK_TBL J WHERE J.PNLGRPNAME = 'UX_DIRECTS_PLT' AND J.MARKET = 'GBL') AND JOB.EFFDT = ( SELECT MAX(AA1.EFFDT) FROM PS_JOB AA1 WHERE AA1.EMPLID = BB.EMPLID AND AA1.EMPL_RCD = BB.EMPL_RCD AND AA1.EFFSEQ = BB.EFFSEQ AND AA1.EFFDT < BB.EFFDT ) AND L.INSTALLED = 1 AND L.LANGUAGE_CD <> ( SELECT OP.LANGUAGE_CD FROM PSOPTIONS OP)

  • Related Language Record for HR_PG_MOVMT_M_I

    • # PeopleSoft Field Name PeopleSoft Field Type Database Column Type Description
    1 EMPLID Character(11) VARCHAR2(11) NOT NULL Employee ID
    2 EMPL_RCD Number(3,0) SMALLINT NOT NULL Empl Record
    3 EFFDT Date(10) DATE Effective Date

    Default Value: %date

    4 LANGUAGE_CD Character(3) VARCHAR2(3) NOT NULL Language Code
    5 NAME_DISPLAY Character(50) VARCHAR2(50) NOT NULL Display Name - name formatted for Display based on the Country
    6 DESCR50_1 Character(50) VARCHAR2(50) NOT NULL Description of length 50
    7 DESCR50 Character(50) VARCHAR2(50) NOT NULL Description of length 50
    8 DESCR3 Character(30) VARCHAR2(30) NOT NULL Descr 3
    9 SUPERVISOR_NAME Character(50) VARCHAR2(50) NOT NULL Supervisor Name
    10 NAME2 Character(40) VARCHAR2(40) NOT NULL Name 2
    11 DESCR100_2 Character(100) VARCHAR2(100) NOT NULL Description
    12 DESCR Character(30) VARCHAR2(30) NOT NULL Description
    13 MESSAGE_TEXT Character(100) VARCHAR2(100) NOT NULL Message Text This field refers to the Text for a particular Message Number in the Message Catalog.
    14 JOBCODE_DESCR Character(30) VARCHAR2(30) NOT NULL Job Code Description
    15 POSN_DESCR Character(30) VARCHAR2(30) NOT NULL Position Description
    16 DEPT_DESCR Character(30) VARCHAR2(30) NOT NULL Department Description
    17 ESTAB_DESCR Character(30) VARCHAR2(30) NOT NULL Description
    18 LOCATION_DESCR Character(30) VARCHAR2(30) NOT NULL Location Description
    19 OPRID Character(30) VARCHAR2(30) NOT NULL A user's ID (see PSOPRDEFN).