HR_PG_CURR_LNG

(SQL View)
 Index Back

Lang View for Current HC - Mgr


SELECT OPR.OPRID , JOB.EMPLID , JOB.EMPL_RCD , JOB.EFFDT , L.LANGUAGE_CD , CASE C.HR_DR_LEVEL WHEN 1 THEN ( SELECT M.MESSAGE_TEXT FROM PSMSGCATLANG M WHERE M.MESSAGE_SET_NBR = 1000 AND M.LANGUAGE_CD = L.LANGUAGE_CD AND M.MESSAGE_NBR = 30168) ELSE ( SELECT M.MESSAGE_TEXT FROM PSMSGCATLANG M WHERE M.MESSAGE_SET_NBR = 1000 AND M.LANGUAGE_CD = L.LANGUAGE_CD AND M.MESSAGE_NBR = 30162) END, ( 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 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 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)), ( SELECT POS.DESCR FROM PS_POSN_DATA_LANG POS WHERE JOB.POSITION_NBR = POS.POSITION_NBR AND POS.LANGUAGE_CD = L.LANGUAGE_CD AND %EffdtCheck(POSN_DATA_LANG POS1, POS, %CurrentDateIn)), ( SELECT XLT1.XLATLONGNAME FROM PSXLATITEMLANG XLT1 WHERE XLT1.FIELDNAME = 'FULL_PART_TIME' AND XLT1.FIELDVALUE = JOB.FULL_PART_TIME AND XLT1.LANGUAGE_CD = L.LANGUAGE_CD AND %EffdtCheck(PSXLATITEMLANG XLT11, XLT1, %CurrentDateIn)), ( SELECT XLT2.XLATLONGNAME FROM PSXLATITEMLANG XLT2 WHERE XLT2.FIELDNAME = 'PER_ORG' AND XLT2.FIELDVALUE = JOB.PER_ORG AND XLT2.LANGUAGE_CD = L.LANGUAGE_CD AND %EffdtCheck(PSXLATITEMLANG XLT21, XLT2, %CurrentDateIn)), ( SELECT ESTAB.DESCR FROM PS_ESTAB_TBL_LANG ESTAB WHERE ESTAB.ESTABID = JOB.ESTABID AND ESTAB.LANGUAGE_CD = L.LANGUAGE_CD AND %EffdtCheck(ESTAB_TBL_LANG ESTAB1, ESTAB, %CurrentDateIn)), ( SELECT PDL.NAME_DISPLAY FROM PS_PERSONAL_DTALNG PDL WHERE PDL.EMPLID = JOB.EMPLID AND PDL.LANGUAGE_CD = L.LANGUAGE_CD), CASE WHEN JOB.ACTION = 'XFR' THEN ( SELECT MSG2.MESSAGE_TEXT FROM PSMSGCATLANG MSG2 WHERE MSG2.MESSAGE_SET_NBR = 1000 AND MSG2.MESSAGE_NBR = 30164 AND MSG2.LANGUAGE_CD = L.LANGUAGE_CD) 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 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 SUP.NAME_DISPLAY FROM PS_PERSONAL_DTALNG SUP WHERE SUP.EMPLID = JOB.SUPERVISOR_ID AND SUP.LANGUAGE_CD = L.LANGUAGE_CD), ( SELECT A6.DESCR FROM PS_POSN_DATA_LANG A6 WHERE A6.POSITION_NBR = JOB.REPORTS_TO AND A6.LANGUAGE_CD = L.LANGUAGE_CD AND %EffdtCheck(POSN_DATA_LANG A61, A6, %CurrentDateIn)), ( 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)) FROM PS_JOB JOB, PS_HR_DIRECT_REP_2 C, PSOPRDEFN OPR, PSLANGUAGES L WHERE C.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(JO.EFFDT) FROM PS_JOB JO WHERE JO.EMPLID = JOB.EMPLID AND JO.EMPL_RCD = JOB.EMPL_RCD AND JO.EFFDT <= %CurrentDateIn) AND C.EMPLID = JOB.EMPLID AND C.EMPL_RCD = JOB.EMPL_RCD AND C.JOB_EFFDT = JOB.EFFDT AND JOB.EFFSEQ = ( SELECT MAX(J1.EFFSEQ) FROM PS_JOB J1 WHERE J1.EMPLID = JOB.EMPLID AND J1.EMPL_RCD = JOB.EMPL_RCD AND J1.EFFDT = JOB.EFFDT) AND OPR.EMPLID = C.SUPERVISOR_ID AND L.INSTALLED = 1 AND L.LANGUAGE_CD <> ( SELECT OPT.LANGUAGE_CD FROM PSOPTIONS OPT) UNION ALL SELECT OPR.OPRID , JOB.EMPLID , JOB.EMPL_RCD , JOB.EFFDT , L.LANGUAGE_CD , CASE C.HR_DR_LEVEL WHEN 1 THEN ( SELECT M.MESSAGE_TEXT FROM PSMSGCATLANG M WHERE M.MESSAGE_SET_NBR = 1000 AND M.LANGUAGE_CD = L.LANGUAGE_CD AND M.MESSAGE_NBR = 30168) ELSE ( SELECT M.MESSAGE_TEXT FROM PSMSGCATLANG M WHERE M.MESSAGE_SET_NBR = 1000 AND M.LANGUAGE_CD = L.LANGUAGE_CD AND M.MESSAGE_NBR = 30162) END , ( 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 JC.DESCR FROM PS_JOBCODE_LANG JC WHERE JC.SETID = JOB.SETID_JOBCODE AND JC.JOBCODE = JOB.JOBCODE AND JC.LANGUAGE_CD = L.LANGUAGE_CD AND %EffdtCheck(JOBCODE_LANG JC1, JC, %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)), ( SELECT POS.DESCR FROM PS_POSN_DATA_LANG POS WHERE JOB.POSITION_NBR = POS.POSITION_NBR AND POS.LANGUAGE_CD = L.LANGUAGE_CD AND %EffdtCheck(POSN_DATA_LANG POS1, POS, %CurrentDateIn)), ( SELECT XLT1.XLATLONGNAME FROM PSXLATITEMLANG XLT1 WHERE XLT1.FIELDNAME = 'FULL_PART_TIME' AND XLT1.FIELDVALUE = JOB.FULL_PART_TIME AND XLT1.LANGUAGE_CD = L.LANGUAGE_CD AND %EffdtCheck(PSXLATITEMLANG XLT11, XLT1, %CurrentDateIn)), ( SELECT XLT2.XLATLONGNAME FROM PSXLATITEMLANG XLT2 WHERE XLT2.FIELDNAME = 'PER_ORG' AND XLT2.FIELDVALUE = JOB.PER_ORG AND XLT2.LANGUAGE_CD = L.LANGUAGE_CD AND %EffdtCheck(PSXLATITEMLANG XLT21, XLT2, %CurrentDateIn)), ( SELECT ESTAB.DESCR FROM PS_ESTAB_TBL_LANG ESTAB WHERE ESTAB.ESTABID = JOB.ESTABID AND ESTAB.LANGUAGE_CD = L.LANGUAGE_CD AND %EffdtCheck(ESTAB_TBL_LANG ESTAB1, ESTAB, %CurrentDateIn)), ( SELECT PDL.NAME_DISPLAY FROM PS_PERSONAL_DTALNG PDL WHERE PDL.EMPLID = JOB.EMPLID AND PDL.LANGUAGE_CD = L.LANGUAGE_CD), CASE WHEN JOB.ACTION = 'XFR' THEN ( SELECT MSG2.MESSAGE_TEXT FROM PSMSGCATLANG MSG2 WHERE MSG2.MESSAGE_SET_NBR = 1000 AND MSG2.MESSAGE_NBR = 30164 AND MSG2.LANGUAGE_CD = L.LANGUAGE_CD) 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 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 SUP.NAME_DISPLAY FROM PS_PERSONAL_DTALNG SUP WHERE SUP.EMPLID = JOB.SUPERVISOR_ID AND SUP.LANGUAGE_CD = L.LANGUAGE_CD), ( SELECT A6.DESCR FROM PS_POSN_DATA_LANG A6 WHERE A6.POSITION_NBR = JOB.REPORTS_TO AND A6.LANGUAGE_CD = L.LANGUAGE_CD AND %EffdtCheck(POSN_DATA_LANG A61, A6, %CurrentDateIn)), ( 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)) FROM PS_JOB JOB, PS_HR_DIRECT_REP_3 C, PSOPRDEFN OPR, PSLANGUAGES L WHERE C.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(JO.EFFDT) FROM PS_JOB JO WHERE JO.EMPLID = JOB.EMPLID AND JO.EMPL_RCD = JOB.EMPL_RCD AND JO.EFFDT <= %CurrentDateIn) AND C.EMPLID = JOB.EMPLID AND C.EMPL_RCD = JOB.EMPL_RCD AND C.JOB_EFFDT = JOB.EFFDT AND JOB.EFFSEQ = ( SELECT MAX(J1.EFFSEQ) FROM PS_JOB J1 WHERE J1.EMPLID = JOB.EMPLID AND J1.EMPL_RCD = JOB.EMPL_RCD AND J1.EFFDT = JOB.EFFDT) AND OPR.EMPLID = C.SUPERVISOR_ID AND L.INSTALLED = 1 AND L.LANGUAGE_CD <> ( SELECT OPT.LANGUAGE_CD FROM PSOPTIONS OPT) UNION ALL SELECT OPR.OPRID , JOB.EMPLID , JOB.EMPL_RCD , JOB.EFFDT , L.LANGUAGE_CD , CASE C.HR_DR_LEVEL WHEN 1 THEN ( SELECT M.MESSAGE_TEXT FROM PSMSGCATLANG M WHERE M.MESSAGE_SET_NBR = 1000 AND M.LANGUAGE_CD = L.LANGUAGE_CD AND M.MESSAGE_NBR = 30168) ELSE ( SELECT M.MESSAGE_TEXT FROM PSMSGCATLANG M WHERE M.MESSAGE_SET_NBR = 1000 AND M.LANGUAGE_CD = L.LANGUAGE_CD AND M.MESSAGE_NBR = 30162) END , ( 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 JC.DESCR FROM PS_JOBCODE_LANG JC WHERE JC.SETID = JOB.SETID_JOBCODE AND JC.JOBCODE = JOB.JOBCODE AND JC.LANGUAGE_CD = L.LANGUAGE_CD AND %EffdtCheck(JOBCODE_LANG JC1, JC, %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)), ( SELECT POS.DESCR FROM PS_POSN_DATA_LANG POS WHERE JOB.POSITION_NBR = POS.POSITION_NBR AND POS.LANGUAGE_CD = L.LANGUAGE_CD AND %EffdtCheck(POSN_DATA_LANG POS1, POS, %CurrentDateIn)), ( SELECT XLT1.XLATLONGNAME FROM PSXLATITEMLANG XLT1 WHERE XLT1.FIELDNAME = 'FULL_PART_TIME' AND XLT1.FIELDVALUE = JOB.FULL_PART_TIME AND XLT1.LANGUAGE_CD = L.LANGUAGE_CD AND %EffdtCheck(PSXLATITEMLANG XLT11, XLT1, %CurrentDateIn)), ( SELECT XLT2.XLATLONGNAME FROM PSXLATITEMLANG XLT2 WHERE XLT2.FIELDNAME = 'PER_ORG' AND XLT2.FIELDVALUE = JOB.PER_ORG AND XLT2.LANGUAGE_CD = L.LANGUAGE_CD AND %EffdtCheck(PSXLATITEMLANG XLT21, XLT2, %CurrentDateIn)), ( SELECT ESTAB.DESCR FROM PS_ESTAB_TBL_LANG ESTAB WHERE ESTAB.ESTABID = JOB.ESTABID AND ESTAB.LANGUAGE_CD = L.LANGUAGE_CD AND %EffdtCheck(ESTAB_TBL_LANG ESTAB1, ESTAB, %CurrentDateIn)), ( SELECT PDL.NAME_DISPLAY FROM PS_PERSONAL_DTALNG PDL WHERE PDL.EMPLID = JOB.EMPLID AND PDL.LANGUAGE_CD = L.LANGUAGE_CD), CASE WHEN JOB.ACTION = 'XFR' THEN ( SELECT MSG2.MESSAGE_TEXT FROM PSMSGCATLANG MSG2 WHERE MSG2.MESSAGE_SET_NBR = 1000 AND MSG2.MESSAGE_NBR = 30164 AND MSG2.LANGUAGE_CD = L.LANGUAGE_CD) 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 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 SUP.NAME_DISPLAY FROM PS_PERSONAL_DTALNG SUP WHERE SUP.EMPLID = JOB.SUPERVISOR_ID AND SUP.LANGUAGE_CD = L.LANGUAGE_CD), ( SELECT A6.DESCR FROM PS_POSN_DATA_LANG A6 WHERE A6.POSITION_NBR = JOB.REPORTS_TO AND A6.LANGUAGE_CD = L.LANGUAGE_CD AND %EffdtCheck(POSN_DATA_LANG A61, A6, %CurrentDateIn)), ( 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)) FROM PS_JOB JOB, PS_HR_DIRECT_REP_4 C, PSOPRDEFN OPR, PSLANGUAGES L WHERE C.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(JO.EFFDT) FROM PS_JOB JO WHERE JO.EMPLID = JOB.EMPLID AND JO.EMPL_RCD = JOB.EMPL_RCD AND JO.EFFDT <= %CurrentDateIn) AND C.EMPLID = JOB.EMPLID AND C.EMPL_RCD = JOB.EMPL_RCD AND C.JOB_EFFDT = JOB.EFFDT AND JOB.EFFSEQ = ( SELECT MAX(J1.EFFSEQ) FROM PS_JOB J1 WHERE J1.EMPLID = JOB.EMPLID AND J1.EMPL_RCD = JOB.EMPL_RCD AND J1.EFFDT = JOB.EFFDT) AND OPR.EMPLID = C.SUPERVISOR_ID AND L.INSTALLED = 1 AND L.LANGUAGE_CD <> ( SELECT OPT.LANGUAGE_CD FROM PSOPTIONS OPT) UNION ALL SELECT OPR.OPRID , JOB.EMPLID , JOB.EMPL_RCD , JOB.EFFDT , L.LANGUAGE_CD , CASE C.HR_DR_LEVEL WHEN 1 THEN ( SELECT M.MESSAGE_TEXT FROM PSMSGCATLANG M WHERE M.MESSAGE_SET_NBR = 1000 AND M.LANGUAGE_CD = L.LANGUAGE_CD AND M.MESSAGE_NBR = 30168) ELSE ( SELECT M.MESSAGE_TEXT FROM PSMSGCATLANG M WHERE M.MESSAGE_SET_NBR = 1000 AND M.LANGUAGE_CD = L.LANGUAGE_CD AND M.MESSAGE_NBR = 30162) END , ( 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 JC.DESCR FROM PS_JOBCODE_LANG JC WHERE JC.SETID = JOB.SETID_JOBCODE AND JC.JOBCODE = JOB.JOBCODE AND JC.LANGUAGE_CD = L.LANGUAGE_CD AND %EffdtCheck(JOBCODE_LANG JC1, JC, %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)), ( SELECT POS.DESCR FROM PS_POSN_DATA_LANG POS WHERE JOB.POSITION_NBR = POS.POSITION_NBR AND POS.LANGUAGE_CD = L.LANGUAGE_CD AND %EffdtCheck(POSN_DATA_LANG POS1, POS, %CurrentDateIn)), ( SELECT XLT1.XLATLONGNAME FROM PSXLATITEMLANG XLT1 WHERE XLT1.FIELDNAME = 'FULL_PART_TIME' AND XLT1.FIELDVALUE = JOB.FULL_PART_TIME AND XLT1.LANGUAGE_CD = L.LANGUAGE_CD AND %EffdtCheck(PSXLATITEMLANG XLT11, XLT1, %CurrentDateIn)), ( SELECT XLT2.XLATLONGNAME FROM PSXLATITEMLANG XLT2 WHERE XLT2.FIELDNAME = 'PER_ORG' AND XLT2.FIELDVALUE = JOB.PER_ORG AND XLT2.LANGUAGE_CD = L.LANGUAGE_CD AND %EffdtCheck(PSXLATITEMLANG XLT21, XLT2, %CurrentDateIn)), ( SELECT ESTAB.DESCR FROM PS_ESTAB_TBL_LANG ESTAB WHERE ESTAB.ESTABID = JOB.ESTABID AND ESTAB.LANGUAGE_CD = L.LANGUAGE_CD AND %EffdtCheck(ESTAB_TBL_LANG ESTAB1, ESTAB, %CurrentDateIn)), ( SELECT PDL.NAME_DISPLAY FROM PS_PERSONAL_DTALNG PDL WHERE PDL.EMPLID = JOB.EMPLID AND PDL.LANGUAGE_CD = L.LANGUAGE_CD), CASE WHEN JOB.ACTION = 'XFR' THEN ( SELECT MSG2.MESSAGE_TEXT FROM PSMSGCATLANG MSG2 WHERE MSG2.MESSAGE_SET_NBR = 1000 AND MSG2.MESSAGE_NBR = 30164 AND MSG2.LANGUAGE_CD = L.LANGUAGE_CD) 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 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 SUP.NAME_DISPLAY FROM PS_PERSONAL_DTALNG SUP WHERE SUP.EMPLID = JOB.SUPERVISOR_ID AND SUP.LANGUAGE_CD = L.LANGUAGE_CD), ( SELECT A6.DESCR FROM PS_POSN_DATA_LANG A6 WHERE A6.POSITION_NBR = JOB.REPORTS_TO AND A6.LANGUAGE_CD = L.LANGUAGE_CD AND %EffdtCheck(POSN_DATA_LANG A61, A6, %CurrentDateIn)), ( 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)) FROM PS_JOB JOB, PS_HR_DIRECT_REP_5 C, PSOPRDEFN OPR, PSLANGUAGES L WHERE C.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(JO.EFFDT) FROM PS_JOB JO WHERE JO.EMPLID = JOB.EMPLID AND JO.EMPL_RCD = JOB.EMPL_RCD AND JO.EFFDT <= %CurrentDateIn) AND C.EMPLID = JOB.EMPLID AND C.EMPL_RCD = JOB.EMPL_RCD AND C.JOB_EFFDT = JOB.EFFDT AND JOB.EFFSEQ = ( SELECT MAX(J1.EFFSEQ) FROM PS_JOB J1 WHERE J1.EMPLID = JOB.EMPLID AND J1.EMPL_RCD = JOB.EMPL_RCD AND J1.EFFDT = JOB.EFFDT) AND OPR.EMPLID = C.SUPERVISOR_ID AND L.INSTALLED = 1 AND L.LANGUAGE_CD <> ( SELECT OPT.LANGUAGE_CD FROM PSOPTIONS OPT) UNION ALL SELECT OPR.OPRID , JOB.EMPLID , JOB.EMPL_RCD , JOB.EFFDT , L.LANGUAGE_CD , CASE C.HR_DR_LEVEL WHEN 1 THEN ( SELECT M.MESSAGE_TEXT FROM PSMSGCATLANG M WHERE M.MESSAGE_SET_NBR = 1000 AND M.LANGUAGE_CD = L.LANGUAGE_CD AND M.MESSAGE_NBR = 30168) ELSE ( SELECT M.MESSAGE_TEXT FROM PSMSGCATLANG M WHERE M.MESSAGE_SET_NBR = 1000 AND M.LANGUAGE_CD = L.LANGUAGE_CD AND M.MESSAGE_NBR = 30162) END , ( 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 JC.DESCR FROM PS_JOBCODE_LANG JC WHERE JC.SETID = JOB.SETID_JOBCODE AND JC.JOBCODE = JOB.JOBCODE AND JC.LANGUAGE_CD = L.LANGUAGE_CD AND %EffdtCheck(JOBCODE_LANG JC1, JC, %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)), ( SELECT POS.DESCR FROM PS_POSN_DATA_LANG POS WHERE JOB.POSITION_NBR = POS.POSITION_NBR AND POS.LANGUAGE_CD = L.LANGUAGE_CD AND %EffdtCheck(POSN_DATA_LANG POS1, POS, %CurrentDateIn)), ( SELECT XLT1.XLATLONGNAME FROM PSXLATITEMLANG XLT1 WHERE XLT1.FIELDNAME = 'FULL_PART_TIME' AND XLT1.FIELDVALUE = JOB.FULL_PART_TIME AND XLT1.LANGUAGE_CD = L.LANGUAGE_CD AND %EffdtCheck(PSXLATITEMLANG XLT11, XLT1, %CurrentDateIn)), ( SELECT XLT2.XLATLONGNAME FROM PSXLATITEMLANG XLT2 WHERE XLT2.FIELDNAME = 'PER_ORG' AND XLT2.FIELDVALUE = JOB.PER_ORG AND XLT2.LANGUAGE_CD = L.LANGUAGE_CD AND %EffdtCheck(PSXLATITEMLANG XLT21, XLT2, %CurrentDateIn)), ( SELECT ESTAB.DESCR FROM PS_ESTAB_TBL_LANG ESTAB WHERE ESTAB.ESTABID = JOB.ESTABID AND ESTAB.LANGUAGE_CD = L.LANGUAGE_CD AND %EffdtCheck(ESTAB_TBL_LANG ESTAB1, ESTAB, %CurrentDateIn)), ( SELECT PDL.NAME_DISPLAY FROM PS_PERSONAL_DTALNG PDL WHERE PDL.EMPLID = JOB.EMPLID AND PDL.LANGUAGE_CD = L.LANGUAGE_CD), CASE WHEN JOB.ACTION = 'XFR' THEN ( SELECT MSG2.MESSAGE_TEXT FROM PSMSGCATLANG MSG2 WHERE MSG2.MESSAGE_SET_NBR = 1000 AND MSG2.MESSAGE_NBR = 30164 AND MSG2.LANGUAGE_CD = L.LANGUAGE_CD) 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 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 SUP.NAME_DISPLAY FROM PS_PERSONAL_DTALNG SUP WHERE SUP.EMPLID = JOB.SUPERVISOR_ID AND SUP.LANGUAGE_CD = L.LANGUAGE_CD), ( SELECT A6.DESCR FROM PS_POSN_DATA_LANG A6 WHERE A6.POSITION_NBR = JOB.REPORTS_TO AND A6.LANGUAGE_CD = L.LANGUAGE_CD AND %EffdtCheck(POSN_DATA_LANG A61, A6, %CurrentDateIn)), ( 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)) FROM PS_JOB JOB, PS_HR_DIRECT_REP_6 C, PSOPRDEFN OPR, PSLANGUAGES L WHERE C.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(JO.EFFDT) FROM PS_JOB JO WHERE JO.EMPLID = JOB.EMPLID AND JO.EMPL_RCD = JOB.EMPL_RCD AND JO.EFFDT <= %CurrentDateIn) AND C.EMPLID = JOB.EMPLID AND C.EMPL_RCD = JOB.EMPL_RCD AND C.JOB_EFFDT = JOB.EFFDT AND JOB.EFFSEQ = ( SELECT MAX(J1.EFFSEQ) FROM PS_JOB J1 WHERE J1.EMPLID = JOB.EMPLID AND J1.EMPL_RCD = JOB.EMPL_RCD AND J1.EFFDT = JOB.EFFDT) AND OPR.EMPLID = C.SUPERVISOR_ID AND L.INSTALLED = 1 AND L.LANGUAGE_CD <> ( SELECT OPT.LANGUAGE_CD FROM PSOPTIONS OPT)

  • Related Language Record for HR_PG_CURR_M_I

    • # PeopleSoft Field Name PeopleSoft Field Type Database Column Type Description
    1 OPRID Character(30) VARCHAR2(30) NOT NULL A user's ID (see PSOPRDEFN).
    2 EMPLID Character(11) VARCHAR2(11) NOT NULL Employee ID
    3 EMPL_RCD Number(3,0) SMALLINT NOT NULL Empl Record
    4 EFFDT Date(10) DATE Effective Date

    Default Value: %date

    5 LANGUAGE_CD Character(3) VARCHAR2(3) NOT NULL Language Code
    6 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.
    7 DEPT_DESCR Character(30) VARCHAR2(30) NOT NULL Department Description
    8 JOBCODE_DESCR Character(30) VARCHAR2(30) NOT NULL Job Code Description
    9 LOCATION_DESCR Character(30) VARCHAR2(30) NOT NULL Location Description
    10 POSN_DESCR Character(30) VARCHAR2(30) NOT NULL Position Description
    11 DESCR Character(30) VARCHAR2(30) NOT NULL Description
    12 DESCR1 Character(30) VARCHAR2(30) NOT NULL Descr
    13 ESTAB_DESCR Character(30) VARCHAR2(30) NOT NULL Description
    14 NAME_DISPLAY Character(50) VARCHAR2(50) NOT NULL Display Name - name formatted for Display based on the Country
    15 DESCR3 Character(30) VARCHAR2(30) NOT NULL Descr 3
    16 DESCR50 Character(50) VARCHAR2(50) NOT NULL Description of length 50
    17 SUPERVISOR_NAME Character(50) VARCHAR2(50) NOT NULL Supervisor Name
    18 NAME2 Character(40) VARCHAR2(40) NOT NULL Name 2
    19 DESCR50_1 Character(50) VARCHAR2(50) NOT NULL Description of length 50