OPRIDEMPLIDEMPL_RCDEFFDTDefault Value: %date
LANGUAGE_CDHR_TM_CURR_LNG(SQL View) |
Index Back |
|---|---|
Lang View for Current HC - Mgr |
| SELECT OPR.OPRID , JOB.EMPLID , JOB.EMPL_RCD , JOB.EFFDT , JCLANG.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 = JCLANG.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 = JCLANG.LANGUAGE_CD AND M.MESSAGE_NBR = 30162) END, %Coalesce(( SELECT DEPT.DESCR FROM PS_DEPT_TBL_LANG DEPT WHERE JOB.SETID_DEPT = DEPT.SETID AND JOB.DEPTID = DEPT.DEPTID AND DEPT.LANGUAGE_CD = JCLANG.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(JCLANG.DESCR,' ') , %Coalesce(( SELECT LOCN.DESCR FROM PS_LOCATION_LANG LOCN WHERE JOB.SETID_DEPT = LOCN.SETID AND JOB.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 = JCLANG.LANGUAGE_CD),' ') , %Coalesce(( SELECT POS.DESCR FROM PS_POSN_DATA_LANG POS WHERE JOB.POSITION_NBR = POS.POSITION_NBR AND POS.EFFDT = ( SELECT MAX(POS1.EFFDT) FROM PS_POSN_DATA_LANG POS1 WHERE POS.POSITION_NBR = POS1.POSITION_NBR AND POS1.EFFDT <= %CURRENTDATEIN)AND POS.LANGUAGE_CD = JCLANG.LANGUAGE_CD),' ') , %Coalesce(( SELECT XLT1.XLATLONGNAME FROM PSXLATITEMLANG XLT1 WHERE XLT1.FIELDNAME = 'FULL_PART_TIME' AND XLT1.FIELDVALUE = JOB.FULL_PART_TIME AND XLT1.EFFDT = ( SELECT MAX(XLT11.EFFDT) FROM PSXLATITEMLANG XLT11 WHERE XLT11.FIELDNAME = XLT1.FIELDNAME AND XLT11.FIELDVALUE = XLT1.FIELDVALUE AND XLT11.EFFDT <= %CurrentDateIn) AND XLT1.LANGUAGE_CD = JCLANG.LANGUAGE_CD), ' ') , %Coalesce(( SELECT XLT2.XLATLONGNAME FROM PSXLATITEMLANG XLT2 WHERE XLT2.FIELDNAME = 'PER_ORG' AND XLT2.FIELDVALUE = JOB.PER_ORG AND XLT2.EFFDT = ( SELECT MAX(XLT21.EFFDT) FROM PSXLATITEMLANG XLT21 WHERE XLT21.FIELDNAME = XLT2.FIELDNAME AND XLT21.FIELDVALUE = XLT2.FIELDVALUE AND XLT21.EFFDT <= %CurrentDateIn) AND XLT2.LANGUAGE_CD = JCLANG.LANGUAGE_CD), ' ') , %Coalesce(( SELECT ESTAB.DESCR FROM PS_ESTAB_TBL_LANG ESTAB WHERE ESTAB.ESTABID = JOB.ESTABID AND ESTAB.EFFDT = ( SELECT MAX(ESTAB1.EFFDT) FROM PS_ESTAB_TBL_LANG ESTAB1 WHERE ESTAB1.ESTABID = ESTAB.ESTABID AND ESTAB1.EFFDT <= %CURRENTDATEIN) AND ESTAB.LANGUAGE_CD = JCLANG.LANGUAGE_CD),' ') , %Coalesce(( 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 = JCLANG.LANGUAGE_CD AND SA.EFFDT = ( SELECT MAX(SA1.EFFDT) FROM PS_SAL_PLAN_LANG SA1 WHERE SA.SETID = SA1.SETID AND SA.SAL_ADMIN_PLAN = SA1.SAL_ADMIN_PLAN AND SA1.LANGUAGE_CD = JCLANG.LANGUAGE_CD AND SA1.EFFDT <= (JOB.EFFDT))),' ') , %Coalesce(( SELECT RR.DESCR50 FROM PS_REG_REGION_LANG RR WHERE RR.REG_REGION = JOB.REG_REGION AND RR.LANGUAGE_CD = JCLANG.LANGUAGE_CD),' ') , %Coalesce(( SELECT POS.DESCR FROM PS_POSN_DATA_LANG POS WHERE JOB.POSITION_NBR = POS.POSITION_NBR AND POS.EFFDT = ( SELECT MAX(POS1.EFFDT) FROM PS_POSN_DATA_LANG POS1 WHERE POS.POSITION_NBR = POS1.POSITION_NBR AND POS1.EFFDT <= %CURRENTDATEIN) AND POS.LANGUAGE_CD = JCLANG.LANGUAGE_CD),' ') , %Coalesce(( SELECT X.XLATLONGNAME FROM PSXLATITEMLANG X WHERE X.FIELDNAME = 'HR_STATUS' AND X.FIELDVALUE = JOB.HR_STATUS AND X.EFFDT = ( SELECT MAX(X1.EFFDT) FROM PSXLATITEMLANG X1 WHERE X1.FIELDNAME = X.FIELDNAME AND X1.FIELDVALUE = X.FIELDVALUE AND X1.LANGUAGE_CD = JCLANG.LANGUAGE_CD AND X1.EFFDT <= %CurrentDateIn) AND X.LANGUAGE_CD = JCLANG.LANGUAGE_CD) , ' ') FROM PS_JOB JOB , PS_PERSONAL_DATA B , PS_HR_DIRECT_REP_2 C , PSOPRDEFN OPR , PS_JOBCODE_TBL JCODE , PS_JOBCODE_LANG JCLANG WHERE JOB.EMPLID = B.EMPLID AND C.DRILL_DOWN_FLAG = 'Y' AND '2' = ( SELECT J.ACCESS_TYPE FROM PS_SS_LINK_TBL J WHERE J.PNLGRPNAME = 'HR_DR_TEAM_FLU' 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 JOB.JOBCODE = JCODE.JOBCODE AND JOB.SETID_JOBCODE = JCODE.SETID AND JCLANG.JOBCODE = JCODE.JOBCODE AND %EffdtCheck(JOBCODE_TBL JOCDE1, JCODE, JOB.EFFDT) AND JCLANG.SETID = JCODE.SETID AND JCLANG.EFFDT = JCODE.EFFDT UNION ALL SELECT OPR.OPRID , JOB.EMPLID , JOB.EMPL_RCD , JOB.EFFDT , JCLANG.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 = JCLANG.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 = JCLANG.LANGUAGE_CD AND M.MESSAGE_NBR = 30162) END , %Coalesce(( SELECT DEPT.DESCR FROM PS_DEPT_TBL_LANG DEPT WHERE JOB.SETID_DEPT = DEPT.SETID AND JOB.DEPTID = DEPT.DEPTID AND DEPT.LANGUAGE_CD = JCLANG.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(JCLANG.DESCR,' ') , %Coalesce(( SELECT LOCN.DESCR FROM PS_LOCATION_LANG LOCN WHERE JOB.SETID_DEPT = LOCN.SETID AND JOB.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 = JCLANG.LANGUAGE_CD),' ') , %Coalesce(( SELECT POS.DESCR FROM PS_POSN_DATA_LANG POS WHERE JOB.POSITION_NBR = POS.POSITION_NBR AND POS.EFFDT = ( SELECT MAX(POS1.EFFDT) FROM PS_POSN_DATA_LANG POS1 WHERE POS.POSITION_NBR = POS1.POSITION_NBR AND POS1.EFFDT <= %CURRENTDATEIN)AND POS.LANGUAGE_CD = JCLANG.LANGUAGE_CD),' ') , %Coalesce(( SELECT XLT1.XLATLONGNAME FROM PSXLATITEMLANG XLT1 WHERE XLT1.FIELDNAME = 'FULL_PART_TIME' AND XLT1.FIELDVALUE = JOB.FULL_PART_TIME AND XLT1.EFFDT = ( SELECT MAX(XLT11.EFFDT) FROM PSXLATITEMLANG XLT11 WHERE XLT11.FIELDNAME = XLT1.FIELDNAME AND XLT11.FIELDVALUE = XLT1.FIELDVALUE AND XLT11.EFFDT <= %CurrentDateIn) AND XLT1.LANGUAGE_CD = JCLANG.LANGUAGE_CD), ' ') , %Coalesce(( SELECT XLT2.XLATLONGNAME FROM PSXLATITEMLANG XLT2 WHERE XLT2.FIELDNAME = 'PER_ORG' AND XLT2.FIELDVALUE = JOB.PER_ORG AND XLT2.EFFDT = ( SELECT MAX(XLT21.EFFDT) FROM PSXLATITEMLANG XLT21 WHERE XLT21.FIELDNAME = XLT2.FIELDNAME AND XLT21.FIELDVALUE = XLT2.FIELDVALUE AND XLT21.EFFDT <= %CurrentDateIn) AND XLT2.LANGUAGE_CD = JCLANG.LANGUAGE_CD), ' ') , %Coalesce(( SELECT ESTAB.DESCR FROM PS_ESTAB_TBL_LANG ESTAB WHERE ESTAB.ESTABID = JOB.ESTABID AND ESTAB.EFFDT = ( SELECT MAX(ESTAB1.EFFDT) FROM PS_ESTAB_TBL_LANG ESTAB1 WHERE ESTAB1.ESTABID = ESTAB.ESTABID AND ESTAB1.EFFDT <= %CURRENTDATEIN) AND ESTAB.LANGUAGE_CD = JCLANG.LANGUAGE_CD),' ') , %Coalesce(( 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 = JCLANG.LANGUAGE_CD AND SA.EFFDT = ( SELECT MAX(SA1.EFFDT) FROM PS_SAL_PLAN_LANG SA1 WHERE SA.SETID = SA1.SETID AND SA.SAL_ADMIN_PLAN = SA1.SAL_ADMIN_PLAN AND SA1.LANGUAGE_CD = JCLANG.LANGUAGE_CD AND SA1.EFFDT <= (JOB.EFFDT))),' ') , %Coalesce(( SELECT RR.DESCR50 FROM PS_REG_REGION_LANG RR WHERE RR.REG_REGION = JOB.REG_REGION AND RR.LANGUAGE_CD = JCLANG.LANGUAGE_CD),' ') , %Coalesce(( SELECT POS.DESCR FROM PS_POSN_DATA_LANG POS WHERE JOB.POSITION_NBR = POS.POSITION_NBR AND POS.EFFDT = ( SELECT MAX(POS1.EFFDT) FROM PS_POSN_DATA_LANG POS1 WHERE POS.POSITION_NBR = POS1.POSITION_NBR AND POS1.EFFDT <= %CURRENTDATEIN) AND POS.LANGUAGE_CD = JCLANG.LANGUAGE_CD),' ') , %Coalesce(( SELECT X.XLATLONGNAME FROM PSXLATITEMLANG X WHERE X.FIELDNAME = 'HR_STATUS' AND X.FIELDVALUE = JOB.HR_STATUS AND X.EFFDT = ( SELECT MAX(X1.EFFDT) FROM PSXLATITEMLANG X1 WHERE X1.FIELDNAME = X.FIELDNAME AND X1.FIELDVALUE = X.FIELDVALUE AND X1.LANGUAGE_CD = JCLANG.LANGUAGE_CD AND X1.EFFDT <= %CurrentDateIn) AND X.LANGUAGE_CD = JCLANG.LANGUAGE_CD) , ' ') FROM PS_JOB JOB , PS_PERSONAL_DATA B , PS_HR_DIRECT_REP_3 C , PSOPRDEFN OPR , PS_JOBCODE_TBL JCODE , PS_JOBCODE_LANG JCLANG WHERE JOB.EMPLID = B.EMPLID AND C.DRILL_DOWN_FLAG = 'Y' AND '3' = ( SELECT J.ACCESS_TYPE FROM PS_SS_LINK_TBL J WHERE J.PNLGRPNAME = 'HR_DR_TEAM_FLU' 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 JOB.JOBCODE = JCODE.JOBCODE AND JOB.SETID_JOBCODE = JCODE.SETID AND %EffdtCheck(JOBCODE_TBL JOCDE1, JCODE, JOB.EFFDT) AND JCLANG.JOBCODE = JCODE.JOBCODE AND JCLANG.SETID = JCODE.SETID AND JCLANG.EFFDT = JCODE.EFFDT UNION ALL SELECT OPR.OPRID , JOB.EMPLID , JOB.EMPL_RCD , JOB.EFFDT , JCLANG.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 = JCLANG.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 = JCLANG.LANGUAGE_CD AND M.MESSAGE_NBR = 30162) END , %Coalesce(( SELECT DEPT.DESCR FROM PS_DEPT_TBL_LANG DEPT WHERE JOB.SETID_DEPT = DEPT.SETID AND JOB.DEPTID = DEPT.DEPTID AND DEPT.LANGUAGE_CD = JCLANG.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(JCLANG.DESCR,' ') , %Coalesce(( SELECT LOCN.DESCR FROM PS_LOCATION_LANG LOCN WHERE JOB.SETID_DEPT = LOCN.SETID AND JOB.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 = JCLANG.LANGUAGE_CD),' ') , %Coalesce(( SELECT POS.DESCR FROM PS_POSN_DATA_LANG POS WHERE JOB.POSITION_NBR = POS.POSITION_NBR AND POS.EFFDT = ( SELECT MAX(POS1.EFFDT) FROM PS_POSN_DATA_LANG POS1 WHERE POS.POSITION_NBR = POS1.POSITION_NBR AND POS1.EFFDT <= %CURRENTDATEIN)AND POS.LANGUAGE_CD = JCLANG.LANGUAGE_CD),' ') , %Coalesce(( SELECT XLT1.XLATLONGNAME FROM PSXLATITEMLANG XLT1 WHERE XLT1.FIELDNAME = 'FULL_PART_TIME' AND XLT1.FIELDVALUE = JOB.FULL_PART_TIME AND XLT1.EFFDT = ( SELECT MAX(XLT11.EFFDT) FROM PSXLATITEMLANG XLT11 WHERE XLT11.FIELDNAME = XLT1.FIELDNAME AND XLT11.FIELDVALUE = XLT1.FIELDVALUE AND XLT11.EFFDT <= %CurrentDateIn) AND XLT1.LANGUAGE_CD = JCLANG.LANGUAGE_CD), ' ') , %Coalesce(( SELECT XLT2.XLATLONGNAME FROM PSXLATITEMLANG XLT2 WHERE XLT2.FIELDNAME = 'PER_ORG' AND XLT2.FIELDVALUE = JOB.PER_ORG AND XLT2.EFFDT = ( SELECT MAX(XLT21.EFFDT) FROM PSXLATITEMLANG XLT21 WHERE XLT21.FIELDNAME = XLT2.FIELDNAME AND XLT21.FIELDVALUE = XLT2.FIELDVALUE AND XLT21.EFFDT <= %CurrentDateIn) AND XLT2.LANGUAGE_CD = JCLANG.LANGUAGE_CD), ' ') , %Coalesce(( SELECT ESTAB.DESCR FROM PS_ESTAB_TBL_LANG ESTAB WHERE ESTAB.ESTABID = JOB.ESTABID AND ESTAB.EFFDT = ( SELECT MAX(ESTAB1.EFFDT) FROM PS_ESTAB_TBL_LANG ESTAB1 WHERE ESTAB1.ESTABID = ESTAB.ESTABID AND ESTAB1.EFFDT <= %CURRENTDATEIN) AND ESTAB.LANGUAGE_CD = JCLANG.LANGUAGE_CD),' ') , %Coalesce(( 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 = JCLANG.LANGUAGE_CD AND SA.EFFDT = ( SELECT MAX(SA1.EFFDT) FROM PS_SAL_PLAN_LANG SA1 WHERE SA.SETID = SA1.SETID AND SA.SAL_ADMIN_PLAN = SA1.SAL_ADMIN_PLAN AND SA1.LANGUAGE_CD = JCLANG.LANGUAGE_CD AND SA1.EFFDT <= (JOB.EFFDT))),' ') , %Coalesce(( SELECT RR.DESCR50 FROM PS_REG_REGION_LANG RR WHERE RR.REG_REGION = JOB.REG_REGION AND RR.LANGUAGE_CD = JCLANG.LANGUAGE_CD),' ') , %Coalesce(( SELECT POS.DESCR FROM PS_POSN_DATA_LANG POS WHERE JOB.POSITION_NBR = POS.POSITION_NBR AND POS.EFFDT = ( SELECT MAX(POS1.EFFDT) FROM PS_POSN_DATA_LANG POS1 WHERE POS.POSITION_NBR = POS1.POSITION_NBR AND POS1.EFFDT <= %CURRENTDATEIN) AND POS.LANGUAGE_CD = JCLANG.LANGUAGE_CD),' ') , %Coalesce(( SELECT X.XLATLONGNAME FROM PSXLATITEMLANG X WHERE X.FIELDNAME = 'HR_STATUS' AND X.FIELDVALUE = JOB.HR_STATUS AND X.EFFDT = ( SELECT MAX(X1.EFFDT) FROM PSXLATITEMLANG X1 WHERE X1.FIELDNAME = X.FIELDNAME AND X1.FIELDVALUE = X.FIELDVALUE AND X1.LANGUAGE_CD = JCLANG.LANGUAGE_CD AND X1.EFFDT <= %CurrentDateIn) AND X.LANGUAGE_CD = JCLANG.LANGUAGE_CD) , ' ') FROM PS_JOB JOB , PS_PERSONAL_DATA B , PS_HR_DIRECT_REP_4 C , PSOPRDEFN OPR , PS_JOBCODE_TBL JCODE , PS_JOBCODE_LANG JCLANG WHERE JOB.EMPLID = B.EMPLID AND C.DRILL_DOWN_FLAG = 'Y' AND '4' = ( SELECT J.ACCESS_TYPE FROM PS_SS_LINK_TBL J WHERE J.PNLGRPNAME = 'HR_DR_TEAM_FLU' 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 JOB.JOBCODE = JCODE.JOBCODE AND JOB.SETID_JOBCODE = JCODE.SETID AND %EffdtCheck(JOBCODE_TBL JOCDE1, JCODE, JOB.EFFDT) AND JCLANG.JOBCODE = JCODE.JOBCODE AND JCLANG.SETID = JCODE.SETID AND JCLANG.EFFDT = JCODE.EFFDT UNION ALL SELECT OPR.OPRID , JOB.EMPLID , JOB.EMPL_RCD , JOB.EFFDT , JCLANG.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 = JCLANG.LANGUAGE_CD AND M.MESSAGE_NBR = 30168) ELSE ( SELECT M.MESSAGE_TEXT FROM PSMSGCATLANG M WHERE M.MESSAGE_SET_NBR = 1000 AND M.LANGUAGE_C D = JCLANG.LANGUAGE_CD AND M.MESSAGE_NBR = 30162) END , %Coalesce(( SELECT DEPT.DESCR FROM PS_DEPT_TBL_LANG DEPT WHERE JOB.SETID_DEPT = DEPT.SETID AND JOB.DEPTID = DEPT.DEPTID AND DEPT.LANGUAGE_CD = JCLANG.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(JCLANG.DESCR,' ') , %Coalesce(( SELECT LOCN.DESCR FROM PS_LOCATION_LANG LOCN WHERE JOB.SETID_DEPT = LOCN.SETID AND JOB.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 = JCLANG.LANGUAGE_CD),' ') , %Coalesce(( SELECT POS.DESCR FROM PS_POSN_DATA_LANG POS WHERE JOB.POSITION_NBR = POS.POSITION_NBR AND POS.EFFDT = ( SELECT MAX(POS1.EFFDT) FROM PS_POSN_DATA_LANG POS1 WHERE POS.POSITION_NBR = POS1.POSITION_NBR AND POS1.EFFDT <= %CURRENTDATEIN)AND POS.LANGUAGE_CD = JCLANG.LANGUAGE_CD),' ') , %Coalesce(( SELECT XLT1.XLATLONGNAME FROM PSXLATITEMLANG XLT1 WHERE XLT1.FIELDNAME = 'FULL_PART_TIME' AND XLT1.FIELDVALUE = JOB.FULL_PART_TIME AND XLT1.EFFDT = ( SELECT MAX(XLT11.EFFDT) FROM PSXLATITEMLANG XLT11 WHERE XLT11.FIELDNAME = XLT1.FIELDNAME AND XLT11.FIELDVALUE = XLT1.FIELDVALUE AND XLT11.EFFDT <= %CurrentDateIn) AND XLT1.LANGUAGE_CD = JCLANG.LANGUAGE_CD), ' ') , %Coalesce(( SELECT XLT2.XLATLONGNAME FROM PSXLATITEMLANG XLT2 WHERE XLT2.FIELDNAME = 'PER_ORG' AND XLT2.FIELDVALUE = JOB.PER_ORG AND XLT2.EFFDT = ( SELECT MAX(XLT21.EFFDT) FROM PSXLATITEMLANG XLT21 WHERE XLT21.FIELDNAME = XLT2.FIELDNAME AND XLT21.FIELDVALUE = XLT2.FIELDVALUE AND XLT21.EFFDT <= %CurrentDateIn) AND XLT2.LANGUAGE_CD = JCLANG.LANGUAGE_CD), ' ') , %Coalesce(( SELECT ESTAB.DESCR FROM PS_ESTAB_TBL_LANG ESTAB WHERE ESTAB.ESTABID = JOB.ESTABID AND ESTAB.EFFDT = ( SELECT MAX(ESTAB1.EFFDT) FROM PS_ESTAB_TBL_LANG ESTAB1 WHERE ESTAB1.ESTABID = ESTAB.ESTABID AND ESTAB1.EFFDT <= %CURRENTDATEIN) AND ESTAB.LANGUAGE_CD = JCLANG.LANGUAGE_CD),' ') , %Coalesce(( 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 = JCLANG.LANGUAGE_CD AND SA.EFFDT = ( SELECT MAX(SA1.EFFDT) FROM PS_SAL_PLAN_LANG SA1 WHERE SA.SETID = SA1.SETID AND SA.SAL_ADMIN_PLAN = SA1.SAL_ADMIN_PLAN AND SA1.LANGUAGE_CD = JCLANG.LANGUAGE_CD AND SA1.EFFDT <= (JOB.EFFDT))),' ') , %Coalesce(( SELECT RR.DESCR50 FROM PS_REG_REGION_LANG RR WHERE RR.REG_REGION = JOB.REG_REGION AND RR.LANGUAGE_CD = JCLANG.LANGUAGE_CD),' ') , %Coalesce(( SELECT POS.DESCR FROM PS_POSN_DATA_LANG POS WHERE JOB.POSITION_NBR = POS.POSITION_NBR AND POS.EFFDT = ( SELECT MAX(POS1.EFFDT) FROM PS_POSN_DATA_LANG POS1 WHERE POS.POSITION_NBR = POS1.POSITION_NBR AND POS1.EFFDT <= %CURRENTDATEIN) AND POS.LANGUAGE_CD = JCLANG.LANGUAGE_CD),' ') , %Coalesce(( SELECT X.XLATLONGNAME FROM PSXLATITEMLANG X WHERE X.FIELDNAME = 'HR_STATUS' AND X.FIELDVALUE = JOB.HR_STATUS AND X.EFFDT = ( SELECT MAX(X1.EFFDT) FROM PSXLATITEMLANG X1 WHERE X1.FIELDNAME = X.FIELDNAME AND X1.FIELDVALUE = X.FIELDVALUE AND X1.LANGUAGE_CD = JCLANG.LANGUAGE_CD AND X1.EFFDT <= %CurrentDateIn) AND X.LANGUAGE_CD = JCLANG.LANGUAGE_CD) , ' ') FROM PS_JOB JOB , PS_PERSONAL_DATA B , PS_HR_DIRECT_REP_5 C , PSOPRDEFN OPR , PS_JOBCODE_TBL JCODE , PS_JOBCODE_LANG JCLANG WHERE JOB.EMPLID = B.EMPLID AND C.DRILL_DOWN_FLAG = 'Y' AND '5' = ( SELECT J.ACCESS_TYPE FROM PS_SS_LINK_TBL J WHERE J.PNLGRPNAME = 'HR_DR_TEAM_FLU' 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 JOB.JOBCODE = JCODE.JOBCODE AND JOB.SETID_JOBCODE = JCODE.SETID AND %EffdtCheck(JOBCODE_TBL JOCDE1, JCODE, JOB.EFFDT) AND JCLANG.JOBCODE = JCODE.JOBCODE AND JCLANG.SETID = JCODE.SETID AND JCLANG.EFFDT = JCODE.EFFDT UNION ALL SELECT OPR.OPRID , JOB.EMPLID , JOB.EMPL_RCD , JOB.EFFDT , JCLANG.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 = JCLANG.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 = JCLANG.LANGUAGE_CD AND M.MESSAGE_NBR = 30162) END , %Coalesce(( SELECT DEPT.DESCR FROM PS_DEPT_TBL_LANG DEPT WHERE JOB.SETID_DEPT = DEPT.SETID AND JOB.DEPTID = DEPT.DEPTID AND DEPT.LANGUAGE_CD = JCLANG.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(JCLANG.DESCR,' ') , %Coalesce(( SELECT LOCN.DESCR FROM PS_LOCATION_LANG LOCN WHERE JOB.SETID_DEPT = LOCN.SETID AND JOB.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 = JCLANG.LANGUAGE_CD),' ') , %Coalesce(( SELECT POS.DESCR FROM PS_POSN_DATA_LANG POS WHERE JOB.POSITION_NBR = POS.POSITION_NBR AND POS.EFFDT = ( SELECT MAX(POS1.EFFDT) FROM PS_POSN_DATA_LANG POS1 WHERE POS.POSITION_NBR = POS1.POSITION_NBR AND POS1.EFFDT <= %CURRENTDATEIN)AND POS.LANGUAGE_CD = JCLANG.LANGUAGE_CD),' ') , %Coalesce(( SELECT XLT1.XLATLONGNAME FROM PSXLATITEMLANG XLT1 WHERE XLT1.FIELDNAME = 'FULL_PART_TIME' AND XLT1.FIELDVALUE = JOB.FULL_PART_TIME AND XLT1.EFFDT = ( SELECT MAX(XLT11.EFFDT) FROM PSXLATITEMLANG XLT11 WHERE XLT11.FIELDNAME = XLT1.FIELDNAME AND XLT11.FIELDVALUE = XLT1.FIELDVALUE AND XLT11.EFFDT <= %CurrentDateIn) AND XLT1.LANGUAGE_CD = JCLANG.LANGUAGE_CD), ' ') , %Coalesce(( SELECT XLT2.XLATLONGNAME FROM PSXLATITEMLANG XLT2 WHERE XLT2.FIELDNAME = 'PER_ORG' AND XLT2.FIELDVALUE = JOB.PER_ORG AND XLT2.EFFDT = ( SELECT MAX(XLT21.EFFDT) FROM PSXLATITEMLANG XLT21 WHERE XLT21.FIELDNAME = XLT2.FIELDNAME AND XLT21.FIELDVALUE = XLT2.FIELDVALUE AND XLT21.EFFDT <= %CurrentDateIn) AND XLT2.LANGUAGE_CD = JCLANG.LANGUAGE_CD), ' ') , %Coalesce(( SELECT ESTAB.DESCR FROM PS_ESTAB_TBL_LANG ESTAB WHERE ESTAB.ESTABID = JOB.ESTABID AND ESTAB.EFFDT = ( SELECT MAX(ESTAB1.EFFDT) FROM PS_ESTAB_TBL_LANG ESTAB1 WHERE ESTAB1.ESTABID = ESTAB.ESTABID AND ESTAB1.EFFDT <= %CURRENTDATEIN) AND ESTAB.LANGUAGE_CD = JCLANG.LANGUAGE_CD),' ') , %Coalesce(( 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 = JCLANG.LANGUAGE_CD AND SA.EFFDT = ( SELECT MAX(SA1.EFFDT) FROM PS_SAL_PLAN_LANG SA1 WHERE SA.SETID = SA1.SETID AND SA.SAL_ADMIN_PLAN = SA1.SAL_ADMIN_PLAN AND SA1.LANGUAGE_CD = JCLANG.LANGUAGE_CD AND SA1.EFFDT <= (JOB.EFFDT))),' ') , %Coalesce(( SELECT RR.DESCR50 FROM PS_REG_REGION_LANG RR WHERE RR.REG_REGION = JOB.REG_REGION AND RR.LANGUAGE_CD = JCLANG.LANGUAGE_CD),' ') , %Coalesce(( SELECT POS.DESCR FROM PS_POSN_DATA_LANG POS WHERE JOB.POSITION_NBR = POS.POSITION_NBR AND POS.EFFDT = ( SELECT MAX(POS1.EFFDT) FROM PS_POSN_DATA_LANG POS1 WHERE POS.POSITION_NBR = POS1.POSITION_NBR AND POS1.EFFDT <= %CURRENTDATEIN) AND POS.LANGUAGE_CD = JCLANG.LANGUAGE_CD),' ') , %Coalesce(( SELECT X.XLATLONGNAME FROM PSXLATITEMLANG X WHERE X.FIELDNAME = 'HR_STATUS' AND X.FIELDVALUE = JOB.HR_STATUS AND X.EFFDT = ( SELECT MAX(X1.EFFDT) FROM PSXLATITEMLANG X1 WHERE X1.FIELDNAME = X.FIELDNAME AND X1.FIELDVALUE = X.FIELDVALUE AND X1.LANGUAGE_CD = JCLANG.LANGUAGE_CD AND X1.EFFDT <= %CurrentDateIn) AND X.LANGUAGE_CD = JCLANG.LANGUAGE_CD) , ' ') FROM PS_JOB JOB , PS_PERSONAL_DATA B , PS_HR_DIRECT_REP_6 C , PSOPRDEFN OPR , PS_JOBCODE_TBL JCODE , PS_JOBCODE_LANG JCLANG WHERE JOB.EMPLID = B.EMPLID AND C.DRILL_DOWN_FLAG = 'Y' AND '6' = ( SELECT J.ACCESS_TYPE FROM PS_SS_LINK_TBL J WHERE J.PNLGRPNAME = 'HR_DR_TEAM_FLU' 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 JOB.JOBCODE = JCODE.JOBCODE AND JOB.SETID_JOBCODE = JCODE.SETID AND %EffdtCheck(JOBCODE_TBL JOCDE1, JCODE, JOB.EFFDT) AND JCLANG.JOBCODE = JCODE.JOBCODE AND JCLANG.SETID = JCODE.SETID AND JCLANG.EFFDT = JCODE.EFFDT |
| # | 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 | DESCR3 | Character(30) | VARCHAR2(30) NOT NULL | Descr 3 |
| 15 | DESCR50 | Character(50) | VARCHAR2(50) NOT NULL | Description of length 50 |
| 16 | NAME2 | Character(40) | VARCHAR2(40) NOT NULL | Name 2 |
| 17 | DESCR50_1 | Character(50) | VARCHAR2(50) NOT NULL | Description of length 50 |