OPRIDEMPLIDEMPL_RCDEFFDTDefault Value: %date
HR_TM_CURR_M_I(SQL View) |
Index Back |
|---|---|
Manager's Current HC Profile |
| SELECT OPR.OPRID , A.EMPLID , A.EMPL_RCD , A.EFFDT , CASE C.HR_DR_LEVEL WHEN 1 THEN ( SELECT M.MESSAGE_TEXT FROM PSMSGCATDEFN M WHERE M.MESSAGE_SET_NBR = 1000 AND M.MESSAGE_NBR = 30168) ELSE ( SELECT M.MESSAGE_TEXT FROM PSMSGCATDEFN M WHERE M.MESSAGE_SET_NBR = 1000 AND M.MESSAGE_NBR = 30162) END, %Coalesce(( SELECT A3.DESCR FROM PS_DEPT_TBL A3 WHERE A.SETID_DEPT = A3.SETID AND A.DEPTID = A3.DEPTID AND A3.EFFDT = ( SELECT MAX(A31.EFFDT) FROM PS_DEPT_TBL A31 WHERE A3.SETID = A31.SETID AND A3.DEPTID = A31.DEPTID AND A31.EFFDT <= %CurrentDateIn)),' ') , %Coalesce(( SELECT A4.DESCR FROM PS_JOBCODE_TBL A4 WHERE A.SETID_DEPT = A4.SETID AND A.JOBCODE = A4.JOBCODE AND A4.EFFDT = ( SELECT MAX(A41.EFFDT) FROM PS_JOBCODE_TBL A41 WHERE A4.SETID = A41.SETID AND A4.JOBCODE = A41.JOBCODE AND A41.EFFDT <= %CurrentDateIn)),' ') , %Coalesce(( SELECT A5.DESCR FROM PS_LOCATION_TBL A5 WHERE A.SETID_DEPT = A5.SETID AND A.LOCATION = A5.LOCATION AND A5.EFFDT = ( SELECT MAX(A51.EFFDT) FROM PS_LOCATION_TBL A51 WHERE A5.SETID = A51.SETID AND A5.LOCATION = A51.LOCATION AND A51.EFFDT <= %CurrentDateIn)),' ') , %Coalesce(( SELECT A6.DESCR FROM PS_POSITION_DATA A6 WHERE A.POSITION_NBR = A6.POSITION_NBR AND A6.EFFDT = ( SELECT MAX(A61.EFFDT) FROM PS_POSITION_DATA A61 WHERE A6.POSITION_NBR = A61.POSITION_NBR AND A61.EFFDT <= %CurrentDateIn)),' ') , %Coalesce(( SELECT A7.XLATLONGNAME FROM PSXLATITEM A7 WHERE A7.FIELDNAME = 'FULL_PART_TIME' AND A7.FIELDVALUE = A.FULL_PART_TIME AND A7.EFF_STATUS = 'A' AND A7.EFFDT = ( SELECT MAX(A71.EFFDT) FROM PSXLATITEM A71 WHERE A71.FIELDNAME = A7.FIELDNAME AND A71.FIELDVALUE = A7.FIELDVALUE AND A71.EFF_STATUS=A7.EFF_STATUS AND A71.EFFDT <= %CurrentDateIn)),' ') , %Coalesce(( SELECT A8.XLATLONGNAME FROM PSXLATITEM A8 WHERE A8.FIELDNAME = 'PER_ORG' AND A8.FIELDVALUE = A.PER_ORG AND A8.EFF_STATUS = 'A' AND A8.EFFDT = ( SELECT MAX(A81.EFFDT) FROM PSXLATITEM A81 WHERE A81.FIELDNAME = A8.FIELDNAME AND A81.FIELDVALUE = A8.FIELDVALUE AND A81.EFF_STATUS=A8.EFF_STATUS AND A81.EFFDT <= %CurrentDateIn)),' ') , %Coalesce(( SELECT A12.DESCR FROM PS_ESTAB_TBL A12 WHERE A12.ESTABID = A.ESTABID AND A12.EFF_STATUS = 'A' AND A12.EFFDT = ( SELECT MAX(A121.EFFDT) FROM PS_ESTAB_TBL A121 WHERE A121.ESTABID = A12.ESTABID AND A121.EFF_STATUS=A12.EFF_STATUS AND A121.EFFDT <= %CurrentDateIn)),' ') , B.NAME_DISPLAY , %Coalesce(CASE WHEN A.ACTION = 'XFR' THEN ( SELECT MESSAGE_TEXT FROM psmsgcatdefn WHERE message_set_nbr = 1000 AND message_nbr = 30164) ELSE ( SELECT ACT.ACTION_DESCR FROM PS_ACTION_TBL ACT WHERE ACT.ACTION = A.ACTION AND ACT.EFF_STATUS = 'A' AND ACT.EFFDT = ( SELECT MAX(ACT1.EFFDT) FROM PS_ACTION_TBL ACT1 WHERE ACT.ACTION = ACT1.ACTION AND ACT.EFF_STATUS = ACT1.EFF_STATUS AND ACT1.EFFDT <= %CurrentDateIn)) END,' ') , %Coalesce(( SELECT SA.DESCR FROM PS_SAL_PLAN_TBL SA WHERE SA.SETID = A.SETID_SALARY AND SA.SAL_ADMIN_PLAN = A.SAL_ADMIN_PLAN AND SA.EFFDT = ( SELECT MAX(SA1.EFFDT) FROM PS_SAL_PLAN_TBL SA1 WHERE SA.SETID = SA1.SETID AND SA.SAL_ADMIN_PLAN = SA1.SAL_ADMIN_PLAN AND SA.EFF_STATUS = SA1.EFF_STATUS AND SA1.EFFDT <= (A.EFFDT)) AND SA.EFF_STATUS<>'I'),' ') , C.SUPERVISOR_ID , %Coalesce(( SELECT S.NAME_DISPLAY FROM PS_PERSONAL_DATA S WHERE S.EMPLID = A.SUPERVISOR_ID),' ') , A.REPORTS_TO , %Coalesce(( SELECT A6.DESCR FROM PS_POSITION_DATA A6 WHERE A.REPORTS_TO = A6.POSITION_NBR AND A6.EFFDT = ( SELECT MAX(A61.EFFDT) FROM PS_POSITION_DATA A61 WHERE A6.POSITION_NBR = A61.POSITION_NBR AND A61.EFFDT <= %CurrentDateIn)),' ') , %Coalesce(( SELECT X.XLATLONGNAME FROM PSXLATITEM X WHERE X.FIELDNAME = 'HR_STATUS' AND X.FIELDVALUE = A.HR_STATUS AND X.EFFDT = ( SELECT MAX(X1.EFFDT) FROM PSXLATITEM X1 WHERE X1.FIELDNAME = X.FIELDNAME AND X1.FIELDVALUE = X.FIELDVALUE AND X1.EFFDT <= %CurrentDateIn)),' ') , %DateNull , %DateNull FROM PS_JOB A , PS_PERSONAL_DATA B , PS_HR_DIRECT_REP_2 C , PSOPRDEFN OPR WHERE A.EMPLID = B.EMPLID AND '2' = ( SELECT CFG.ACCESS_TYPE FROM PS_HR_DR_UI_CFG CFG WHERE CFG.PNLGRPNAME='HR_DR_TEAM_FLU') AND C.HR_DR_LEVEL = 1 AND C.DRILL_DOWN_FLAG = 'Y' AND C.EMPLID = A.EMPLID AND C.EMPL_RCD = A.EMPL_RCD AND A.EFFDT = ( SELECT MAX(JO.EFFDT) FROM PS_JOB JO WHERE JO.EMPLID=A.EMPLID AND JO.EMPL_RCD=A.EMPL_RCD AND JO.EFFDT <=%CurrentDateIn ) AND C.EMPLID = A.EMPLID AND C.EMPL_RCD = A.EMPL_RCD AND C.JOB_EFFDT = A.EFFDT AND A.EFFSEQ = ( SELECT MAX(J1.EFFSEQ) FROM PS_JOB J1 WHERE J1.EMPLID = A.EMPLID AND J1.EMPL_RCD = A.EMPL_RCD AND J1.EFFDT = A.EFFDT) AND OPR.EMPLID = C.SUPERVISOR_ID UNION ALL SELECT OPR.OPRID , A.EMPLID , A.EMPL_RCD , A.EFFDT , CASE C.HR_DR_LEVEL WHEN 1 THEN ( SELECT M.MESSAGE_TEXT FROM PSMSGCATDEFN M WHERE M.MESSAGE_SET_NBR = 1000 AND M.MESSAGE_NBR = 30168) ELSE ( SELECT M.MESSAGE_TEXT FROM PSMSGCATDEFN M WHERE M.MESSAGE_SET_NBR = 1000 AND M.MESSAGE_NBR = 30162) END , %Coalesce(( SELECT A3.DESCR FROM PS_DEPT_TBL A3 WHERE A.SETID_DEPT = A3.SETID AND A.DEPTID = A3.DEPTID AND A3.EFFDT = ( SELECT MAX(A31.EFFDT) FROM PS_DEPT_TBL A31 WHERE A3.SETID = A31.SETID AND A3.DEPTID = A31.DEPTID AND A31.EFFDT <= %CurrentDateIn)),' ') , %Coalesce(( SELECT A4.DESCR FROM PS_JOBCODE_TBL A4 WHERE A.SETID_DEPT = A4.SETID AND A.JOBCODE = A4.JOBCODE AND A4.EFFDT = ( SELECT MAX(A41.EFFDT) FROM PS_JOBCODE_TBL A41 WHERE A4.SETID = A41.SETID AND A4.JOBCODE = A41.JOBCODE AND A41.EFFDT <= %CurrentDateIn)),' ') , %Coalesce(( SELECT A5.DESCR FROM PS_LOCATION_TBL A5 WHERE A.SETID_DEPT = A5.SETID AND A.LOCATION = A5.LOCATION AND A5.EFFDT = ( SELECT MAX(A51.EFFDT) FROM PS_LOCATION_TBL A51 WHERE A5.SETID = A51.SETID AND A5.LOCATION = A51.LOCATION AND A51.EFFDT <= %CurrentDateIn)),' ') , %Coalesce(( SELECT A6.DESCR FROM PS_POSITION_DATA A6 WHERE A.POSITION_NBR = A6.POSITION_NBR AND A6.EFFDT = ( SELECT MAX(A61.EFFDT) FROM PS_POSITION_DATA A61 WHERE A6.POSITION_NBR = A61.POSITION_NBR AND A61.EFFDT <= %CurrentDateIn)),' ') , %Coalesce(( SELECT A7.XLATLONGNAME FROM PSXLATITEM A7 WHERE A7.FIELDNAME = 'FULL_PART_TIME' AND A7.FIELDVALUE = A.FULL_PART_TIME AND A7.EFF_STATUS = 'A' AND A7.EFFDT = ( SELECT MAX(A71.EFFDT) FROM PSXLATITEM A71 WHERE A71.FIELDNAME = A7.FIELDNAME AND A71.FIELDVALUE = A7.FIELDVALUE AND A71.EFF_STATUS=A7.EFF_STATUS AND A71.EFFDT <= %CurrentDateIn)),' ') , %Coalesce(( SELECT A8.XLATLONGNAME FROM PSXLATITEM A8 WHERE A8.FIELDNAME = 'PER_ORG' AND A8.FIELDVALUE = A.PER_ORG AND A8.EFF_STATUS = 'A' AND A8.EFFDT = ( SELECT MAX(A81.EFFDT) FROM PSXLATITEM A81 WHERE A81.FIELDNAME = A8.FIELDNAME AND A81.FIELDVALUE = A8.FIELDVALUE AND A81.EFF_STATUS=A8.EFF_STATUS AND A81.EFFDT <= %CurrentDateIn)),' ') , %Coalesce(( SELECT A12.DESCR FROM PS_ESTAB_TBL A12 WHERE A12.ESTABID = A.ESTABID AND A12.EFF_STATUS = 'A' AND A12.EFFDT = ( SELECT MAX(A121.EFFDT) FROM PS_ESTAB_TBL A121 WHERE A121.ESTABID = A12.ESTABID AND A121.EFF_STATUS=A12.EFF_STATUS AND A121.EFFDT <= %CurrentDateIn)),' ') , B.NAME_DISPLAY , %Coalesce(CASE WHEN A.ACTION = 'XFR' THEN ( SELECT MESSAGE_TEXT FROM psmsgcatdefn WHERE message_set_nbr = 1000 AND message_nbr = 30164) ELSE ( SELECT ACT.ACTION_DESCR FROM PS_ACTION_TBL ACT WHERE ACT.ACTION = A.ACTION AND ACT.EFF_STATUS = 'A' AND ACT.EFFDT = ( SELECT MAX(ACT1.EFFDT) FROM PS_ACTION_TBL ACT1 WHERE ACT.ACTION = ACT1.ACTION AND ACT.EFF_STATUS = ACT1.EFF_STATUS AND ACT1.EFFDT <= %CurrentDateIn)) END,' ') , %Coalesce(( SELECT SA.DESCR FROM PS_SAL_PLAN_TBL SA WHERE SA.SETID = A.SETID_SALARY AND SA.SAL_ADMIN_PLAN = A.SAL_ADMIN_PLAN AND SA.EFFDT = ( SELECT MAX(SA1.EFFDT) FROM PS_SAL_PLAN_TBL SA1 WHERE SA.SETID = SA1.SETID AND SA.SAL_ADMIN_PLAN = SA1.SAL_ADMIN_PLAN AND SA.EFF_STATUS = SA1.EFF_STATUS AND SA1.EFFDT <= (A.EFFDT)) AND SA.EFF_STATUS<>'I'),' ') , C.SUPERVISOR_ID , %Coalesce(( SELECT S.NAME_DISPLAY FROM PS_PERSONAL_DATA S WHERE S.EMPLID = A.SUPERVISOR_ID),' ') , A.REPORTS_TO , %Coalesce(( SELECT A6.DESCR FROM PS_POSITION_DATA A6 WHERE A.REPORTS_TO = A6.POSITION_NBR AND A6.EFFDT = ( SELECT MAX(A61.EFFDT) FROM PS_POSITION_DATA A61 WHERE A6.POSITION_NBR = A61.POSITION_NBR AND A61.EFFDT <= %CurrentDateIn)),' ') , %Coalesce(( SELECT X.XLATLONGNAME FROM PSXLATITEM X WHERE X.FIELDNAME = 'HR_STATUS' AND X.FIELDVALUE = A.HR_STATUS AND X.EFFDT = ( SELECT MAX(X1.EFFDT) FROM PSXLATITEM X1 WHERE X1.FIELDNAME = X.FIELDNAME AND X1.FIELDVALUE = X.FIELDVALUE AND X1.EFFDT <= %CurrentDateIn)),' ') , %DateNull , %DateNull FROM PS_JOB A , PS_PERSONAL_DATA B , PS_HR_DIRECT_REP_3 C , PSOPRDEFN OPR WHERE A.EMPLID = B.EMPLID AND '3' = ( SELECT CFG.ACCESS_TYPE FROM PS_HR_DR_UI_CFG CFG WHERE CFG.PNLGRPNAME='HR_DR_TEAM_FLU') AND C.HR_DR_LEVEL = 1 AND C.DRILL_DOWN_FLAG = 'Y' AND C.EMPLID = A.EMPLID AND C.EMPL_RCD = A.EMPL_RCD AND A.EFFDT = ( SELECT MAX(JO.EFFDT) FROM PS_JOB JO WHERE JO.EMPLID=A.EMPLID AND JO.EMPL_RCD=A.EMPL_RCD AND JO.EFFDT <=%CurrentDateIn ) AND C.EMPLID = A.EMPLID AND C.EMPL_RCD = A.EMPL_RCD AND C.JOB_EFFDT = A.EFFDT AND A.EFFSEQ = ( SELECT MAX(J1.EFFSEQ) FROM PS_JOB J1 WHERE J1.EMPLID = A.EMPLID AND J1.EMPL_RCD = A.EMPL_RCD AND J1.EFFDT = A.EFFDT) AND OPR.EMPLID = C.SUPERVISOR_ID UNION ALL SELECT OPR.OPRID , A.EMPLID , A.EMPL_RCD , A.EFFDT , CASE C.HR_DR_LEVEL WHEN 1 THEN ( SELECT M.MESSAGE_TEXT FROM PSMSGCATDEFN M WHERE M.MESSAGE_SET_NBR = 1000 AND M.MESSAGE_NBR = 30168) ELSE ( SELECT M.MESSAGE_TEXT FROM PSMSGCATDEFN M WHERE M.MESSAGE_SET_NBR = 1000 AND M.MESSAGE_NBR = 30162) END , %Coalesce(( SELECT A3.DESCR FROM PS_DEPT_TBL A3 WHERE A.SETID_DEPT = A3.SETID AND A.DEPTID = A3.DEPTID AND A3.EFFDT = ( SELECT MAX(A31.EFFDT) FROM PS_DEPT_TBL A31 WHERE A3.SETID = A31.SETID AND A3.DEPTID = A31.DEPTID AND A31.EFFDT <= %CurrentDateIn)),' ') , %Coalesce(( SELECT A4.DESCR FROM PS_JOBCODE_TBL A4 WHERE A.SETID_DEPT = A4.SETID AND A.JOBCODE = A4.JOBCODE AND A4.EFFDT = ( SELECT MAX(A41.EFFDT) FROM PS_JOBCODE_TBL A41 WHERE A4.SETID = A41.SETID AND A4.JOBCODE = A41.JOBCODE AND A41.EFFDT <= %CurrentDateIn)),' ') , %Coalesce(( SELECT A5.DESCR FROM PS_LOCATION_TBL A5 WHERE A.SETID_DEPT = A5.SETID AND A.LOCATION = A5.LOCATION AND A5.EFFDT = ( SELECT MAX(A51.EFFDT) FROM PS_LOCATION_TBL A51 WHERE A5.SETID = A51.SETID AND A5.LOCATION = A51.LOCATION AND A51.EFFDT <= %CurrentDateIn)),' ') , %Coalesce(( SELECT A6.DESCR FROM PS_POSITION_DATA A6 WHERE A.POSITION_NBR = A6.POSITION_NBR AND A6.EFFDT = ( SELECT MAX(A61.EFFDT) FROM PS_POSITION_DATA A61 WHERE A6.POSITION_NBR = A61.POSITION_NBR AND A61.EFFDT <= %CurrentDateIn)),' ') , %Coalesce(( SELECT A7.XLATLONGNAME FROM PSXLATITEM A7 WHERE A7.FIELDNAME = 'FULL_PART_TIME' AND A7.FIELDVALUE = A.FULL_PART_TIME AND A7.EFF_STATUS = 'A' AND A7.EFFDT = ( SELECT MAX(A71.EFFDT) FROM PSXLATITEM A71 WHERE A71.FIELDNAME = A7.FIELDNAME AND A71.FIELDVALUE = A7.FIELDVALUE AND A71.EFF_STATUS=A7.EFF_STATUS AND A71.EFFDT <= %CurrentDateIn)),' ') , %Coalesce(( SELECT A8.XLATLONGNAME FROM PSXLATITEM A8 WHERE A8.FIELDNAME = 'PER_ORG' AND A8.FIELDVALUE = A.PER_ORG AND A8.EFF_STATUS = 'A' AND A8.EFFDT = ( SELECT MAX(A81.EFFDT) FROM PSXLATITEM A81 WHERE A81.FIELDNAME = A8.FIELDNAME AND A81.FIELDVALUE = A8.FIELDVALUE AND A81.EFF_STATUS=A8.EFF_STATUS AND A81.EFFDT <= %CurrentDateIn)),' ') , %Coalesce(( SELECT A12.DESCR FROM PS_ESTAB_TBL A12 WHERE A12.ESTABID = A.ESTABID AND A12.EFF_STATUS = 'A' AND A12.EFFDT = ( SELECT MAX(A121.EFFDT) FROM PS_ESTAB_TBL A121 WHERE A121.ESTABID = A12.ESTABID AND A121.EFF_STATUS=A12.EFF_STATUS AND A121.EFFDT <= %CurrentDateIn)),' ') , B.NAME_DISPLAY , %Coalesce(CASE WHEN A.ACTION = 'XFR' THEN ( SELECT MESSAGE_TEXT FROM psmsgcatdefn WHERE message_set_nbr = 1000 AND message_nbr = 30164) ELSE ( SELECT ACT.ACTION_DESCR FROM PS_ACTION_TBL ACT WHERE ACT.ACTION = A.ACTION AND ACT.EFF_STATUS = 'A' AND ACT.EFFDT = ( SELECT MAX(ACT1.EFFDT) FROM PS_ACTION_TBL ACT1 WHERE ACT.ACTION = ACT1.ACTION AND ACT.EFF_STATUS = ACT1.EFF_STATUS AND ACT1.EFFDT <= %CurrentDateIn)) END,' ') , %Coalesce(( SELECT SA.DESCR FROM PS_SAL_PLAN_TBL SA WHERE SA.SETID = A.SETID_SALARY AND SA.SAL_ADMIN_PLAN = A.SAL_ADMIN_PLAN AND SA.EFFDT = ( SELECT MAX(SA1.EFFDT) FROM PS_SAL_PLAN_TBL SA1 WHERE SA.SETID = SA1.SETID AND SA.SAL_ADMIN_PLAN = SA1.SAL_ADMIN_PLAN AND SA.EFF_STATUS = SA1.EFF_STATUS AND SA1.EFFDT <= (A.EFFDT)) AND SA.EFF_STATUS<>'I'),' ') , C.SUPERVISOR_ID , %Coalesce(( SELECT S.NAME_DISPLAY FROM PS_PERSONAL_DATA S WHERE S.EMPLID = A.SUPERVISOR_ID),' ') , A.REPORTS_TO , %Coalesce(( SELECT A6.DESCR FROM PS_POSITION_DATA A6 WHERE A.REPORTS_TO = A6.POSITION_NBR AND A6.EFFDT = ( SELECT MAX(A61.EFFDT) FROM PS_POSITION_DATA A61 WHERE A6.POSITION_NBR = A61.POSITION_NBR AND A61.EFFDT <= %CurrentDateIn)),' ') , %Coalesce(( SELECT X.XLATLONGNAME FROM PSXLATITEM X WHERE X.FIELDNAME = 'HR_STATUS' AND X.FIELDVALUE = A.HR_STATUS AND X.EFFDT = ( SELECT MAX(X1.EFFDT) FROM PSXLATITEM X1 WHERE X1.FIELDNAME = X.FIELDNAME AND X1.FIELDVALUE = X.FIELDVALUE AND X1.EFFDT <= %CurrentDateIn)),' ') , %DateNull , %DateNull FROM PS_JOB A , PS_PERSONAL_DATA B , PS_HR_DIRECT_REP_4 C , PSOPRDEFN OPR WHERE A.EMPLID = B.EMPLID AND '4' = ( SELECT CFG.ACCESS_TYPE FROM PS_HR_DR_UI_CFG CFG WHERE CFG.PNLGRPNAME='HR_DR_TEAM_FLU') AND C.HR_DR_LEVEL = 1 AND C.DRILL_DOWN_FLAG = 'Y' AND C.EMPLID = A.EMPLID AND C.EMPL_RCD = A.EMPL_RCD AND A.EFFDT = ( SELECT MAX(JO.EFFDT) FROM PS_JOB JO WHERE JO.EMPLID=A.EMPLID AND JO.EMPL_RCD=A.EMPL_RCD AND JO.EFFDT <=%CurrentDateIn ) AND C.EMPLID = A.EMPLID AND C.EMPL_RCD = A.EMPL_RCD AND C.JOB_EFFDT = A.EFFDT AND A.EFFSEQ = ( SELECT MAX(J1.EFFSEQ) FROM PS_JOB J1 WHERE J1.EMPLID = A.EMPLID AND J1.EMPL_RCD = A.EMPL_RCD AND J1.EFFDT = A.EFFDT) AND OPR.EMPLID = C.SUPERVISOR_ID UNION ALL SELECT OPR.OPRID , A.EMPLID , A.EMPL_RCD , A.EFFDT , CASE C.HR_DR_LEVEL WHEN 1 THEN ( SELECT M.MESSAGE_TEXT FROM PSMSGCATDEFN M WHERE M.MESSAGE_SET_NBR = 1000 AND M.MESSAGE_NBR = 30168) ELSE ( SELECT M.MESSAGE_TEXT FROM PSMSGCATDEFN M WHERE M.MESSAGE_SET_NBR = 1000 AND M.MESSAGE_NBR = 30162) END , %Coalesce(( SELECT A3.DESCR FROM PS_DEPT_TBL A3 WHERE A.SETID_DEPT = A3.SETID AND A.DEPTID = A3.DEPTID AND A3.EFFDT = ( SELECT MAX(A31.EFFDT) FROM PS_DEPT_TBL A31 WHERE A3.SETID = A31.SETID AND A3.DEPTID = A31.DEPTID AND A31.EFFDT <= %CurrentDateIn)),' ') , %Coalesce(( SELECT A4.DESCR FROM PS_JOBC ODE_TBL A4 WHERE A.SETID_DEPT = A4.SETID AND A.JOBCODE = A4.JOBCODE AND A4.EFFDT = ( SELECT MAX(A41.EFFDT) FROM PS_JOBCODE_TBL A41 WHERE A4.SETID = A41.SETID AND A4.JOBCODE = A41.JOBCODE AND A41.EFFDT <= %CurrentDateIn)),' ') , %Coalesce(( SELECT A5.DESCR FROM PS_LOCATION_TBL A5 WHERE A.SETID_DEPT = A5.SETID AND A.LOCATION = A5.LOCATION AND A5.EFFDT = ( SELECT MAX(A51.EFFDT) FROM PS_LOCATION_TBL A51 WHERE A5.SETID = A51.SETID AND A5.LOCATION = A51.LOCATION AND A51.EFFDT <= %CurrentDateIn)),' ') , %Coalesce(( SELECT A6.DESCR FROM PS_POSITION_DATA A6 WHERE A.POSITION_NBR = A6.POSITION_NBR AND A6.EFFDT = ( SELECT MAX(A61.EFFDT) FROM PS_POSITION_DATA A61 WHERE A6.POSITION_NBR = A61.POSITION_NBR AND A61.EFFDT <= %CurrentDateIn)),' ') , %Coalesce(( SELECT A7.XLATLONGNAME FROM PSXLATITEM A7 WHERE A7.FIELDNAME = 'FULL_PART_TIME' AND A7.FIELDVALUE = A.FULL_PART_TIME AND A7.EFF_STATUS = 'A' AND A7.EFFDT = ( SELECT MAX(A71.EFFDT) FROM PSXLATITEM A71 WHERE A71.FIELDNAME = A7.FIELDNAME AND A71.FIELDVALUE = A7.FIELDVALUE AND A71.EFF_STATUS=A7.EFF_STATUS AND A71.EFFDT <= %CurrentDateIn)),' ') , %Coalesce(( SELECT A8.XLATLONGNAME FROM PSXLATITEM A8 WHERE A8.FIELDNAME = 'PER_ORG' AND A8.FIELDVALUE = A.PER_ORG AND A8.EFF_STATUS = 'A' AND A8.EFFDT = ( SELECT MAX(A81.EFFDT) FROM PSXLATITEM A81 WHERE A81.FIELDNAME = A8.FIELDNAME AND A81.FIELDVALUE = A8.FIELDVALUE AND A81.EFF_STATUS=A8.EFF_STATUS AND A81.EFFDT <= %CurrentDateIn)),' ') , %Coalesce(( SELECT A12.DESCR FROM PS_ESTAB_TBL A12 WHERE A12.ESTABID = A.ESTABID AND A12.EFF_STATUS = 'A' AND A12.EFFDT = ( SELECT MAX(A121.EFFDT) FROM PS_ESTAB_TBL A121 WHERE A121.ESTABID = A12.ESTABID AND A121.EFF_STATUS=A12.EFF_STATUS AND A121.EFFDT <= %CurrentDateIn)),' ') , B.NAME_DISPLAY , %Coalesce(CASE WHEN A.ACTION = 'XFR' THEN ( SELECT MESSAGE_TEXT FROM psmsgcatdefn WHERE message_set_nbr = 1000 AND message_nbr = 30164) ELSE ( SELECT ACT.ACTION_DESCR FROM PS_ACTION_TBL ACT WHERE ACT.ACTION = A.ACTION AND ACT.EFF_STATUS = 'A' AND ACT.EFFDT = ( SELECT MAX(ACT1.EFFDT) FROM PS_ACTION_TBL ACT1 WHERE ACT.ACTION = ACT1.ACTION AND ACT.EFF_STATUS = ACT1.EFF_STATUS AND ACT1.EFFDT <= %CurrentDateIn)) END,' ') , %Coalesce(( SELECT SA.DESCR FROM PS_SAL_PLAN_TBL SA WHERE SA.SETID = A.SETID_SALARY AND SA.SAL_ADMIN_PLAN = A.SAL_ADMIN_PLAN AND SA.EFFDT = ( SELECT MAX(SA1.EFFDT) FROM PS_SAL_PLAN_TBL SA1 WHERE SA.SETID = SA1.SETID AND SA.SAL_ADMIN_PLAN = SA1.SAL_ADMIN_PLAN AND SA.EFF_STATUS = SA1.EFF_STATUS AND SA1.EFFDT <= (A.EFFDT)) AND SA.EFF_STATUS<>'I'),' ') , C.SUPERVISOR_ID , %Coalesce(( SELECT S.NAME_DISPLAY FROM PS_PERSONAL_DATA S WHERE S.EMPLID = A.SUPERVISOR_ID),' ') , A.REPORTS_TO , %Coalesce(( SELECT A6.DESCR FROM PS_POSITION_DATA A6 WHERE A.REPORTS_TO = A6.POSITION_NBR AND A6.EFFDT = ( SELECT MAX(A61.EFFDT) FROM PS_POSITION_DATA A61 WHERE A6.POSITION_NBR = A61.POSITION_NBR AND A61.EFFDT <= %CurrentDateIn)),' ') , %Coalesce(( SELECT X.XLATLONGNAME FROM PSXLATITEM X WHERE X.FIELDNAME = 'HR_STATUS' AND X.FIELDVALUE = A.HR_STATUS AND X.EFFDT = ( SELECT MAX(X1.EFFDT) FROM PSXLATITEM X1 WHERE X1.FIELDNAME = X.FIELDNAME AND X1.FIELDVALUE = X.FIELDVALUE AND X1.EFFDT <= %CurrentDateIn)),' ') , %DateNull , %DateNull FROM PS_JOB A , PS_PERSONAL_DATA B , PS_HR_DIRECT_REP_5 C , PSOPRDEFN OPR WHERE A.EMPLID = B.EMPLID AND '5' = ( SELECT CFG.ACCESS_TYPE FROM PS_HR_DR_UI_CFG CFG WHERE CFG.PNLGRPNAME='HR_DR_TEAM_FLU') AND C.HR_DR_LEVEL = 1 AND C.DRILL_DOWN_FLAG = 'Y' AND C.EMPLID = A.EMPLID AND C.EMPL_RCD = A.EMPL_RCD AND A.EFFDT = ( SELECT MAX(JO.EFFDT) FROM PS_JOB JO WHERE JO.EMPLID=A.EMPLID AND JO.EMPL_RCD=A.EMPL_RCD AND JO.EFFDT <=%CurrentDateIn ) AND C.EMPLID = A.EMPLID AND C.EMPL_RCD = A.EMPL_RCD AND C.JOB_EFFDT = A.EFFDT AND A.EFFSEQ = ( SELECT MAX(J1.EFFSEQ) FROM PS_JOB J1 WHERE J1.EMPLID = A.EMPLID AND J1.EMPL_RCD = A.EMPL_RCD AND J1.EFFDT = A.EFFDT) AND OPR.EMPLID = C.SUPERVISOR_ID UNION ALL SELECT OPR.OPRID , A.EMPLID , A.EMPL_RCD , A.EFFDT ,CASE C.HR_DR_LEVEL WHEN 1 THEN ( SELECT M.MESSAGE_TEXT FROM PSMSGCATDEFN M WHERE M.MESSAGE_SET_NBR = 1000 AND M.MESSAGE_NBR = 30168) ELSE ( SELECT M.MESSAGE_TEXT FROM PSMSGCATDEFN M WHERE M.MESSAGE_SET_NBR = 1000 AND M.MESSAGE_NBR = 30162) END , %Coalesce(( SELECT A3.DESCR FROM PS_DEPT_TBL A3 WHERE A.SETID_DEPT = A3.SETID AND A.DEPTID = A3.DEPTID AND A3.EFFDT = ( SELECT MAX(A31.EFFDT) FROM PS_DEPT_TBL A31 WHERE A3.SETID = A31.SETID AND A3.DEPTID = A31.DEPTID AND A31.EFFDT <= %CurrentDateIn)),' ') , %Coalesce(( SELECT A4.DESCR FROM PS_JOBCODE_TBL A4 WHERE A.SETID_DEPT = A4.SETID AND A.JOBCODE = A4.JOBCODE AND A4.EFFDT = ( SELECT MAX(A41.EFFDT) FROM PS_JOBCODE_TBL A41 WHERE A4.SETID = A41.SETID AND A4.JOBCODE = A41.JOBCODE AND A41.EFFDT <= %CurrentDateIn)),' ') , %Coalesce(( SELECT A5.DESCR FROM PS_LOCATION_TBL A5 WHERE A.SETID_DEPT = A5.SETID AND A.LOCATION = A5.LOCATION AND A5.EFFDT = ( SELECT MAX(A51.EFFDT) FROM PS_LOCATION_TBL A51 WHERE A5.SETID = A51.SETID AND A5.LOCATION = A51.LOCATION AND A51.EFFDT <= %CurrentDateIn)),' ') , %Coalesce(( SELECT A6.DESCR FROM PS_POSITION_DATA A6 WHERE A.POSITION_NBR = A6.POSITION_NBR AND A6.EFFDT = ( SELECT MAX(A61.EFFDT) FROM PS_POSITION_DATA A61 WHERE A6.POSITION_NBR = A61.POSITION_NBR AND A61.EFFDT <= %CurrentDateIn)),' ') , %Coalesce(( SELECT A7.XLATLONGNAME FROM PSXLATITEM A7 WHERE A7.FIELDNAME = 'FULL_PART_TIME' AND A7.FIELDVALUE = A.FULL_PART_TIME AND A7.EFF_STATUS = 'A' AND A7.EFFDT = ( SELECT MAX(A71.EFFDT) FROM PSXLATITEM A71 WHERE A71.FIELDNAME = A7.FIELDNAME AND A71.FIELDVALUE = A7.FIELDVALUE AND A71.EFF_STATUS=A7.EFF_STATUS AND A71.EFFDT <= %CurrentDateIn)),' ') , %Coalesce(( SELECT A8.XLATLONGNAME FROM PSXLATITEM A8 WHERE A8.FIELDNAME = 'PER_ORG' AND A8.FIELDVALUE = A.PER_ORG AND A8.EFF_STATUS = 'A' AND A8.EFFDT = ( SELECT MAX(A81.EFFDT) FROM PSXLATITEM A81 WHERE A81.FIELDNAME = A8.FIELDNAME AND A81.FIELDVALUE = A8.FIELDVALUE AND A81.EFF_STATUS=A8.EFF_STATUS AND A81.EFFDT <= %CurrentDateIn)),' ') , %Coalesce(( SELECT A12.DESCR FROM PS_ESTAB_TBL A12 WHERE A12.ESTABID = A.ESTABID AND A12.EFF_STATUS = 'A' AND A12.EFFDT = ( SELECT MAX(A121.EFFDT) FROM PS_ESTAB_TBL A121 WHERE A121.ESTABID = A12.ESTABID AND A121.EFF_STATUS=A12.EFF_STATUS AND A121.EFFDT <= %CurrentDateIn)),' ') , B.NAME_DISPLAY , %Coalesce(CASE WHEN A.ACTION = 'XFR' THEN ( SELECT MESSAGE_TEXT FROM psmsgcatdefn WHERE message_set_nbr = 1000 AND message_nbr = 30164) ELSE ( SELECT ACT.ACTION_DESCR FROM PS_ACTION_TBL ACT WHERE ACT.ACTION = A.ACTION AND ACT.EFF_STATUS = 'A' AND ACT.EFFDT = ( SELECT MAX(ACT1.EFFDT) FROM PS_ACTION_TBL ACT1 WHERE ACT.ACTION = ACT1.ACTION AND ACT.EFF_STATUS = ACT1.EFF_STATUS AND ACT1.EFFDT <= %CurrentDateIn)) END,' ') , %Coalesce(( SELECT SA.DESCR FROM PS_SAL_PLAN_TBL SA WHERE SA.SETID = A.SETID_SALARY AND SA.SAL_ADMIN_PLAN = A.SAL_ADMIN_PLAN AND SA.EFFDT = ( SELECT MAX(SA1.EFFDT) FROM PS_SAL_PLAN_TBL SA1 WHERE SA.SETID = SA1.SETID AND SA.SAL_ADMIN_PLAN = SA1.SAL_ADMIN_PLAN AND SA.EFF_STATUS = SA1.EFF_STATUS AND SA1.EFFDT <= (A.EFFDT)) AND SA.EFF_STATUS<>'I'),' ') , C.SUPERVISOR_ID , %Coalesce(( SELECT S.NAME_DISPLAY FROM PS_PERSONAL_DATA S WHERE S.EMPLID = A.SUPERVISOR_ID),' ') , A.REPORTS_TO , %Coalesce(( SELECT A6.DESCR FROM PS_POSITION_DATA A6 WHERE A.REPORTS_TO = A6.POSITION_NBR AND A6.EFFDT = ( SELECT MAX(A61.EFFDT) FROM PS_POSITION_DATA A61 WHERE A6.POSITION_NBR = A61.POSITION_NBR AND A61.EFFDT <= %CurrentDateIn)),' ') , %Coalesce(( SELECT X.XLATLONGNAME FROM PSXLATITEM X WHERE X.FIELDNAME = 'HR_STATUS' AND X.FIELDVALUE = A.HR_STATUS AND X.EFFDT = ( SELECT MAX(X1.EFFDT) FROM PSXLATITEM X1 WHERE X1.FIELDNAME = X.FIELDNAME AND X1.FIELDVALUE = X.FIELDVALUE AND X1.EFFDT <= %CurrentDateIn)),' ') , %DateNull , %DateNull FROM PS_JOB A , PS_PERSONAL_DATA B , PS_HR_DIRECT_REP_6 C , PSOPRDEFN OPR WHERE A.EMPLID = B.EMPLID AND '6' = ( SELECT CFG.ACCESS_TYPE FROM PS_HR_DR_UI_CFG CFG WHERE CFG.PNLGRPNAME='HR_DR_TEAM_FLU') AND C.HR_DR_LEVEL = 1 AND C.DRILL_DOWN_FLAG = 'Y' AND C.EMPLID = A.EMPLID AND C.EMPL_RCD = A.EMPL_RCD AND A.EFFDT = ( SELECT MAX(JO.EFFDT) FROM PS_JOB JO WHERE JO.EMPLID=A.EMPLID AND JO.EMPL_RCD=A.EMPL_RCD AND JO.EFFDT <=%CurrentDateIn ) AND C.EMPLID = A.EMPLID AND C.EMPL_RCD = A.EMPL_RCD AND C.JOB_EFFDT = A.EFFDT AND A.EFFSEQ = ( SELECT MAX(J1.EFFSEQ) FROM PS_JOB J1 WHERE J1.EMPLID = A.EMPLID AND J1.EMPL_RCD = A.EMPL_RCD AND J1.EFFDT = A.EFFDT) AND OPR.EMPLID = C.SUPERVISOR_ID |
| # | 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 | 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. |
| 6 | DEPT_DESCR | Character(30) | VARCHAR2(30) NOT NULL | Department Description |
| 7 | JOBCODE_DESCR | Character(30) | VARCHAR2(30) NOT NULL | Job Code Description |
| 8 | LOCATION_DESCR | Character(30) | VARCHAR2(30) NOT NULL | Location Description |
| 9 | POSN_DESCR | Character(30) | VARCHAR2(30) NOT NULL | Position Description |
| 10 | DESCR | Character(30) | VARCHAR2(30) NOT NULL | Description |
| 11 | DESCR1 | Character(30) | VARCHAR2(30) NOT NULL | Descr |
| 12 | ESTAB_DESCR | Character(30) | VARCHAR2(30) NOT NULL | Description |
| 13 | NAME_DISPLAY | Character(50) | VARCHAR2(50) NOT NULL | Display Name - name formatted for Display based on the Country |
| 14 | DESCR3 | Character(30) | VARCHAR2(30) NOT NULL | Descr 3 |
| 15 | DESCR50 | Character(50) | VARCHAR2(50) NOT NULL | Description of length 50 |
| 16 | SUPERVISOR_ID | Character(11) | VARCHAR2(11) NOT NULL | Supervisor ID |
| 17 | SUPERVISOR_NAME | Character(50) | VARCHAR2(50) NOT NULL | Supervisor Name |
| 18 | REPORTS_TO | Character(8) | VARCHAR2(8) NOT NULL | Reports To Position Number |
| 19 | NAME2 | Character(40) | VARCHAR2(40) NOT NULL | Name 2 |
| 20 | DESCR50_1 | Character(50) | VARCHAR2(50) NOT NULL | Description of length 50 |
| 21 | FROM_DT | Date(10) | DATE | From Date |
| 22 | TO_DT | Date(10) | DATE | To Date |