HR_TM_PG_VW

(SQL View)
 Index Back

Scatter Plot Pivot Base View

Scatter Plot Pivot Base View

SELECT A.SUPERVISOR_ID , 0 , A.EMPLID , A.EMPL_RCD , B.EFFDT , B.DEPTID , COALESCE(( SELECT DEPT.DESCR FROM PS_DEPT_TBL DEPT WHERE DEPT.DEPTID= B.DEPTID AND DEPT.EFF_STATUS='A' AND DEPT.SETID= B.SETID_DEPT AND %EffdtCheck(DEPT_TBL DEPT1, DEPT, %CurrentDateIn)),'') , B.LOCATION , COALESCE(( SELECT LOC.DESCR FROM PS_LOCATION_TBL LOC WHERE LOC.LOCATION=B.LOCATION AND LOC.EFF_STATUS='A' AND LOC.SETID=B.SETID_LOCATION AND %EffdtCheck(LOCATION_TBL LOC1, LOC, %CurrentDateIn)),'') , B.JOBCODE , COALESCE(( SELECT JTBL.DESCR FROM PS_JOBCODE_TBL JTBL WHERE JTBL.JOBCODE=B.JOBCODE AND JTBL.EFF_STATUS='A' AND JTBL.SETID=B.SETID_JOBCODE AND %EffdtCheck(JOBCODE_TBL JTBL1, JTBL, %CurrentDateIn)),'') , %Round(B.ANNUAL_RT,2) , B.CURRENCY_CD , APPR.EP_RATING , CASE WHEN (CASE WHEN B.CURRENCY_CD <> GRD.CURRENCY_CD THEN %Round(%DecDiv((SELECT %DecMULT(%DecDiv(B.HOURLY_RT, RT1.RATE_DIV),RT1.RATE_MULT) FROM PS_HR_TM_PG_RT_VW RT1 WHERE RT1.FROM_CUR=B.CURRENCY_CD),(SELECT %DecMULT(%DecDiv(GRD.MID_RT_HOURLY, RT2.RATE_DIV),RT2.RATE_MULT) FROM PS_HR_TM_PG_RT_VW RT2 WHERE RT2.FROM_CUR=GRD.CURRENCY_CD)),2) ELSE CASE WHEN GRD.MID_RT_HOURLY = 0 THEN 0 ELSE %Round((%DecDiv(B.HOURLY_RT, GRD.MID_RT_HOURLY)),2) END END ) > 9.99 THEN 9.99 ELSE (CASE WHEN B.CURRENCY_CD <> GRD.CURRENCY_CD THEN %Round(%DecDiv((SELECT %DecMULT(%DecDiv(B.HOURLY_RT, RT1.RATE_DIV),RT1.RATE_MULT) FROM PS_HR_TM_PG_RT_VW RT1 WHERE RT1.FROM_CUR=B.CURRENCY_CD),(SELECT %DecMULT(%DecDiv(GRD.MID_RT_HOURLY, RT2.RATE_DIV),RT2.RATE_MULT) FROM PS_HR_TM_PG_RT_VW RT2 WHERE RT2.FROM_CUR=GRD.CURRENCY_CD)),2) ELSE CASE WHEN GRD.MID_RT_HOURLY = 0 THEN 0 ELSE %Round((%DecDiv(B.HOURLY_RT, GRD.MID_RT_HOURLY)),2) END END) END , RATE.DESCR , C.NAME_DISPLAY FROM PS_HR_DIRECT_REP_2 A , PS_JOB B , PS_SAL_GRADE_TBL GRD , PS_EP_APPR APPR , PS_REVW_RATING_TBL RATE , PS_PERSONAL_DATA C , PS_EP_DEFAULT EPDFLT WHERE 2 = ( SELECT CFG.ACCESS_TYPE FROM PS_HR_DR_UI_CFG CFG WHERE CFG.PNLGRPNAME='HR_DR_TEAM_FLU') AND A.HR_DR_LEVEL = 1 AND A.DRILL_DOWN_FLAG = 'Y' AND A.EMPLID = B.EMPLID AND A.EMPL_RCD = B.EMPL_RCD AND %EffdtCheck(JOB JB1, B, %CurrentDateIn) AND B.EFFSEQ = ( SELECT MAX(JB2.EFFSEQ) FROM PS_JOB JB2 WHERE JB2.EMPLID = B.EMPLID AND JB2.EFFDT = B.EFFDT AND JB2.EMPL_RCD = B.EMPL_RCD) AND B.SETID_SALARY = GRD.SETID AND B.SAL_ADMIN_PLAN = GRD.SAL_ADMIN_PLAN AND B.GRADE = GRD.GRADE AND GRD.EFF_STATUS = 'A' AND %EffdtCheck(SAL_GRADE_TBL ED2, GRD, %CurrentDateIn) AND B.EMPLID = APPR.EMPLID AND B.EMPL_RCD = APPR.EMPL_RCD AND APPR.EP_REVIEW_STATUS = 'CO' AND APPR.EP_REVIEW_TYPE=EPDFLT.EP_REVIEW_TYPE AND EPDFLT.EP_TRANSACTION_NM = 'M' AND APPR.PERIOD_END_DT =( SELECT MAX(A1.PERIOD_END_DT) FROM PS_EP_APPR A1 WHERE APPR.EMPLID =A1.EMPLID AND APPR.EMPL_RCD=A1.EMPL_RCD AND APPR.EP_REVIEW_STATUS=A1.EP_REVIEW_STATUS AND APPR.EP_REVIEW_TYPE=A1.EP_REVIEW_TYPE ) AND RATE.RATING_MODEL = APPR.RATING_MODEL AND RATE.REVIEW_RATING = APPR.REVIEW_RATING AND %EffdtCheck(REVW_RATING_TBL D, RATE, %CurrentDateIn) AND A.EMPLID = C.EMPLID AND (B.PER_ORG = 'EMP' OR B.PER_ORG = 'CWR') UNION ALL SELECT A.SUPERVISOR_ID , 0 , A.EMPLID , A.EMPL_RCD , B.EFFDT , B.DEPTID , COALESCE(( SELECT DEPT.DESCR FROM PS_DEPT_TBL DEPT WHERE DEPT.DEPTID= B.DEPTID AND DEPT.EFF_STATUS='A' AND DEPT.SETID= B.SETID_DEPT AND %EffdtCheck(DEPT_TBL DEPT1, DEPT, %CurrentDateIn)),'') , B.LOCATION , COALESCE(( SELECT LOC.DESCR FROM PS_LOCATION_TBL LOC WHERE LOC.LOCATION=B.LOCATION AND LOC.EFF_STATUS='A' AND LOC.SETID=B.SETID_LOCATION AND %EffdtCheck(LOCATION_TBL LOC1, LOC, %CurrentDateIn)),'') , B.JOBCODE , COALESCE(( SELECT JTBL.DESCR FROM PS_JOBCODE_TBL JTBL WHERE JTBL.JOBCODE=B.JOBCODE AND JTBL.EFF_STATUS='A' AND JTBL.SETID=B.SETID_JOBCODE AND %EffdtCheck(JOBCODE_TBL JTBL1, JTBL, %CurrentDateIn)),'') , %Round(B.ANNUAL_RT,2) , B.CURRENCY_CD , APPR.EP_RATING , CASE WHEN (CASE WHEN B.CURRENCY_CD <> GRD.CURRENCY_CD THEN %Round(%DecDiv((SELECT %DecMULT(%DecDiv(B.HOURLY_RT, RT1.RATE_DIV),RT1.RATE_MULT) FROM PS_HR_TM_PG_RT_VW RT1 WHERE RT1.FROM_CUR=B.CURRENCY_CD),(SELECT %DecMULT(%DecDiv(GRD.MID_RT_HOURLY, RT2.RATE_DIV),RT2.RATE_MULT) FROM PS_HR_TM_PG_RT_VW RT2 WHERE RT2.FROM_CUR=GRD.CURRENCY_CD)),2) ELSE CASE WHEN GRD.MID_RT_HOURLY = 0 THEN 0 ELSE %Round((%DecDiv(B.HOURLY_RT, GRD.MID_RT_HOURLY)),2) END END ) > 9.99 THEN 9.99 ELSE (CASE WHEN B.CURRENCY_CD <> GRD.CURRENCY_CD THEN %Round(%DecDiv((SELECT %DecMULT(%DecDiv(B.HOURLY_RT, RT1.RATE_DIV),RT1.RATE_MULT) FROM PS_HR_TM_PG_RT_VW RT1 WHERE RT1.FROM_CUR=B.CURRENCY_CD),(SELECT %DecMULT(%DecDiv(GRD.MID_RT_HOURLY, RT2.RATE_DIV),RT2.RATE_MULT) FROM PS_HR_TM_PG_RT_VW RT2 WHERE RT2.FROM_CUR=GRD.CURRENCY_CD)),2) ELSE CASE WHEN GRD.MID_RT_HOURLY = 0 THEN 0 ELSE %Round((%DecDiv(B.HOURLY_RT, GRD.MID_RT_HOURLY)),2) END END) END , RATE.DESCR , C.NAME_DISPLAY FROM PS_HR_DIRECT_REP_3 A , PS_JOB B , PS_SAL_GRADE_TBL GRD , PS_EP_APPR APPR , PS_REVW_RATING_TBL RATE , PS_PERSONAL_DATA C , PS_EP_DEFAULT EPDFLT WHERE 3 = ( SELECT CFG.ACCESS_TYPE FROM PS_HR_DR_UI_CFG CFG WHERE CFG.PNLGRPNAME='HR_DR_TEAM_FLU') AND A.HR_DR_LEVEL = 1 AND A.DRILL_DOWN_FLAG = 'Y' AND A.EMPLID = B.EMPLID AND A.EMPL_RCD = B.EMPL_RCD AND %EffdtCheck(JOB JB1, B, %CurrentDateIn) AND B.EFFSEQ = ( SELECT MAX(JB2.EFFSEQ) FROM PS_JOB JB2 WHERE JB2.EMPLID = B.EMPLID AND JB2.EFFDT = B.EFFDT AND JB2.EMPL_RCD = B.EMPL_RCD) AND B.SETID_SALARY = GRD.SETID AND B.SAL_ADMIN_PLAN = GRD.SAL_ADMIN_PLAN AND B.GRADE = GRD.GRADE AND GRD.EFF_STATUS = 'A' AND %EffdtCheck(SAL_GRADE_TBL ED2, GRD, %CurrentDateIn) AND B.EMPLID = APPR.EMPLID AND B.EMPL_RCD = APPR.EMPL_RCD AND APPR.EP_REVIEW_STATUS = 'CO' AND APPR.EP_REVIEW_TYPE=EPDFLT.EP_REVIEW_TYPE AND EPDFLT.EP_TRANSACTION_NM = 'M' AND APPR.PERIOD_END_DT =( SELECT MAX(A1.PERIOD_END_DT) FROM PS_EP_APPR A1 WHERE APPR.EMPLID =A1.EMPLID AND APPR.EMPL_RCD=A1.EMPL_RCD AND APPR.EP_REVIEW_STATUS=A1.EP_REVIEW_STATUS AND APPR.EP_REVIEW_TYPE=A1.EP_REVIEW_TYPE ) AND RATE.RATING_MODEL = APPR.RATING_MODEL AND RATE.REVIEW_RATING = APPR.REVIEW_RATING AND %EffdtCheck(REVW_RATING_TBL D, RATE, %CurrentDateIn) AND A.EMPLID = C.EMPLID AND (B.PER_ORG = 'EMP' OR B.PER_ORG = 'CWR') UNION ALL SELECT A.SUPERVISOR_ID , A.SUPERVIS_EMPL_RCD , A.EMPLID , A.EMPL_RCD , B.EFFDT , B.DEPTID , COALESCE(( SELECT DEPT.DESCR FROM PS_DEPT_TBL DEPT WHERE DEPT.DEPTID= B.DEPTID AND DEPT.EFF_STATUS='A' AND DEPT.SETID= B.SETID_DEPT AND %EffdtCheck(DEPT_TBL DEPT1, DEPT, %CurrentDateIn)),'') , B.LOCATION , COALESCE(( SELECT LOC.DESCR FROM PS_LOCATION_TBL LOC WHERE LOC.LOCATION=B.LOCATION AND LOC.EFF_STATUS='A' AND LOC.SETID=B.SETID_LOCATION AND %EffdtCheck(LOCATION_TBL LOC1, LOC, %CurrentDateIn)),'') , B.JOBCODE , COALESCE(( SELECT JTBL.DESCR FROM PS_JOBCODE_TBL JTBL WHERE JTBL.JOBCODE=B.JOBCODE AND JTBL.EFF_STATUS='A' AND JTBL.SETID=B.SETID_JOBCODE AND %EffdtCheck(JOBCODE_TBL JTBL1, JTBL, %CurrentDateIn)),'') , %Round(B.ANNUAL_RT,2) , B.CURRENCY_CD , APPR.EP_RATING , CASE WHEN (CASE WHEN B.CURRENCY_CD <> GRD.CURRENCY_CD THEN %Round(%DecDiv((SELECT %DecMULT(%DecDiv(B.HOURLY_RT, RT1.RATE_DIV),RT1.RATE_MULT) FROM PS_HR_TM_PG_RT_VW RT1 WHERE RT1.FROM_CUR=B.CURRENCY_CD),(SELECT %DecMULT(%DecDiv(GRD.MID_RT_HOURLY, RT2.RATE_DIV),RT2.RATE_MULT) FROM PS_HR_TM_PG_RT_VW RT2 WHERE RT2.FROM_CUR=GRD.CURRENCY_CD)),2) ELSE CASE WHEN GRD.MID_RT_HOURLY = 0 THEN 0 ELSE %Round((%DecDiv(B.HOURLY_RT, GRD.MID_RT_HOURLY)),2) END END ) > 9.99 THEN 9.99 ELSE (CASE WHEN B.CURRENCY_CD <> GRD.CURRENCY_CD THEN %Round(%DecDiv((SELECT %DecMULT(%DecDiv(B.HOURLY_RT, RT1.RATE_DIV),RT1.RATE_MULT) FROM PS_HR_TM_PG_RT_VW RT1 WHERE RT1.FROM_CUR=B.CURRENCY_CD),(SELECT %DecMULT(%DecDiv(GRD.MID_RT_HOURLY, RT2.RATE_DIV),RT2.RATE_MULT) FROM PS_HR_TM_PG_RT_VW RT2 WHERE RT2.FROM_CUR=GRD.CURRENCY_CD)),2) ELSE CASE WHEN GRD.MID_RT_HOURLY = 0 THEN 0 ELSE %Round((%DecDiv(B.HOURLY_RT, GRD.MID_RT_HOURLY)),2) END END) END , RATE.DESCR , C.NAME_DISPLAY FROM PS_HR_DIRECT_REP_4 A , PS_JOB B , PS_SAL_GRADE_TBL GRD , PS_EP_APPR APPR , PS_REVW_RATING_TBL RATE , PS_PERSONAL_DATA C , PS_EP_DEFAULT EPDFLT WHERE 4 = ( SELECT CFG.ACCESS_TYPE FROM PS_HR_DR_UI_CFG CFG WHERE CFG.PNLGRPNAME='HR_DR_TEAM_FLU') AND A.HR_DR_LEVEL = 1 AND A.DRILL_DOWN_FLAG = 'Y' AND A.EMPLID = B.EMPLID AND A.EMPL_RCD = B.EMPL_RCD AND %EffdtCheck(JOB JB1, B, %CurrentDateIn) AND B.EFFSEQ = ( SELECT MAX(JB2.EFFSEQ) FROM PS_JOB JB2 WHERE JB2.EMPLID = B.EMPLID AND JB2.EFFDT = B.EFFDT AND JB2.EMPL_RCD = B.EMPL_RCD) AND B.SETID_SALARY = GRD.SETID AND B.SAL_ADMIN_PLAN = GRD.SAL_ADMIN_PLAN AND B.GRADE = GRD.GRADE AND GRD.EFF_STATUS = 'A' AND %EffdtCheck(SAL_GRADE_TBL ED2, GRD, %CurrentDateIn) AND B.EMPLID = APPR.EMPLID AND B.EMPL_RCD = APPR.EMPL_RCD AND APPR.EP_REVIEW_STATUS = 'CO' AND APPR.EP_REVIEW_TYPE=EPDFLT.EP_REVIEW_TYPE AND EPDFLT.EP_TRANSACTION_NM = 'M' AND APPR.PERIOD_END_DT =( SELECT MAX(A1.PERIOD_END_DT) FROM PS_EP_APPR A1 WHERE APPR.EMPLID =A1.EMPLID AND APPR.EMPL_RCD=A1.EMPL_RCD AND APPR.EP_REVIEW_STATUS=A1.EP_REVIEW_STATUS AND APPR.EP_REVIEW_TYPE=A1.EP_REVIEW_TYPE ) AND RATE.RATING_MODEL = APPR.RATING_MODEL AND RATE.REVIEW_RATING = APPR.REVIEW_RATING AND %EffdtCheck(REVW_RATING_TBL D, RATE, %CurrentDateIn) AND A.EMPLID = C.EMPLID AND (B.PER_ORG = 'EMP' OR B.PER_ORG = 'CWR') UNION ALL SELECT A.SUPERVISOR_ID , A.SUPERVIS_EMPL_RCD , A.EMPLID , A.EMPL_RCD , B.EFFDT , B.DEPTID , COALESCE(( SELECT DEPT.DESCR FROM PS_DEPT_TBL DEPT WHERE DEPT.DEPTID= B.DEPTID AND DEPT.EFF_STATUS='A' AND DEPT.SETID= B.SETID_DEPT AND %EffdtCheck(DEPT_TBL DEPT1, DEPT, %CurrentDateIn)),'') , B.LOCATION , COALESCE(( SELECT LOC.DESCR FROM PS_LOCATION_TBL LOC WHERE LOC.LOCATION=B.LOCATION AND LOC.EFF_STATUS='A' AND LOC.SETID=B.SETID_LOCATION AND %EffdtCheck(LOCATION_TBL LOC1, LOC, %CurrentDateIn)),'') , B.JOBCODE , COALESCE(( SELECT JTBL.DESCR FROM PS_JOBCODE_TBL JTBL WHERE JTBL.JOBCODE=B.JOBCODE AND JTBL.EFF_STATUS='A' AND JTBL.SETID=B.SETID_JOBCODE AND %EffdtCheck(JOBCODE_TBL JTBL1, JTBL, %CurrentDateIn)),'') , %Round(B.ANNUAL_RT,2) , B.CURRENCY_CD , APPR.EP_RATING , CASE WHEN (CASE WHEN B.CURRENCY_CD <> GRD.CURRENCY_CD THEN %Round(%DecDiv((SELECT %DecMULT(%DecDiv(B.HOURLY_RT, RT1.RATE_DIV),RT1.RATE_MULT) FROM PS_HR_TM_PG_RT_VW RT1 WHERE RT1.FROM_CUR=B.CURRENCY_CD),(SELECT %DecMULT(%DecDiv(GRD.MID_RT_HOURLY, RT2.RATE_DIV),RT2.RATE_MULT) FROM PS_HR_TM_PG_RT_VW RT2 WHERE RT2.FROM_CUR=GRD.CURRENCY_CD)),2) ELSE CASE WHEN GRD.MID_RT_HOURLY = 0 THEN 0 ELSE %Round((%DecDiv(B.HOURLY_RT, GRD.MID_RT_HOURLY)),2) END END ) > 9.99 THEN 9.99 ELSE (CASE WHEN B.CURRENCY_CD <> GRD.CURRENCY_CD THEN %Round(%DecDiv((SELECT %DecMULT(%DecDiv(B.HOURLY_RT, RT1.RATE_DIV),RT1.RATE_MULT) FROM PS_HR_TM_PG_RT_VW RT1 WHERE RT1.FROM_CUR=B.CURRENCY_CD),(SELECT %DecMULT(%DecDiv(GRD.MID_RT_HOURLY, RT2.RATE_DIV),RT2.RATE_MULT) FROM PS_HR_TM_PG_RT_VW RT2 WHERE RT2.FROM_CUR=GRD.CURRENCY_CD)),2) ELSE CASE WHEN GRD.MID_RT_HOURLY = 0 THEN 0 ELSE %Round((%DecDiv(B.HOURLY_RT, GRD.MID_RT_HOURLY)),2) END END) END , RATE.DESCR , C.NAME_DISPLAY FROM PS_HR_DIRECT_REP_5 A , PS_JOB B , PS_SAL_GRADE_TBL GRD , PS_EP_APPR APPR , PS_REVW_RATING_TBL RATE , PS_PERSONAL_DATA C , PS_EP_DEFAULT EPDFLT WHERE 5 = ( SELECT CFG.ACCESS_TYPE FROM PS_HR_DR_UI_CFG CFG WHERE CFG.PNLGRPNAME='HR_DR_TEAM_FLU') AND A.HR_DR_LEVEL = 1 AND A.DRILL_DOWN_FLAG = 'Y' AND A.EMPLID = B.EMPLID AND A.EMPL_RCD = B.EMPL_RCD AND %EffdtCheck(JOB JB1, B, %CurrentDateIn) AND B.EFFSEQ = ( SELECT MAX(JB2.EFFSEQ) FROM PS_JOB JB2 WHERE JB2.EMPLID = B.EMPLID AND JB2.EFFDT = B.EFFDT AND JB2.EMPL_RCD = B.EMPL_RCD) AND B.SETID_SALARY = GRD.SETID AND B.SAL_ADMIN_PLAN = GRD.SAL_ADMIN_PLAN AND B.GRADE = GRD.GRADE AND GRD.EFF_STATUS = 'A' AND %EffdtCheck(SAL_GRADE_TBL ED2, GRD, %CurrentDateIn) AND B.EMPLID = APPR.EMPLID AND B.EMPL_RCD = APPR.EMPL_RCD AND APPR.EP_REVIEW_STATUS = 'CO' AND APPR.EP_REVIEW_TYPE=EPDFLT.EP_REVIEW_TYPE AND EPDFLT.EP_TRANSACTION_NM = 'M' AND APPR.PERIOD_END_DT =( SELECT MAX(A1.PERIOD_END_DT) FROM PS_EP_APPR A1 WHERE APPR.EMPLID =A1.EMPLID AND APPR.EMPL_RCD=A1.EMPL_RCD AND APPR.EP_REVIEW_STATUS=A1.EP_REVIEW_STATUS AND APPR.EP_REVIEW_TYPE=A1.EP_REVIEW_TYPE ) AND RATE.RATING_MODEL = APPR.RATING_MODEL AND RATE.REVIEW_RATING = APPR.REVIEW_RATING AND %EffdtCheck(REVW_RATING_TBL D, RATE, %CurrentDateIn) AND A.EMPLID = C.EMPLID AND (B.PER_ORG = 'EMP' OR B.PER_ORG = 'CWR') UNION ALL SELECT A.SUPERVISOR_ID , A.SUPERVIS_EMPL_RCD , A.EMPLID , A.EMPL_RCD , B.EFFDT , B.DEPTID , COALESCE(( SELECT DEPT.DESCR FROM PS_DEPT_TBL DEPT WHERE DEPT.DEPTID= B.DEPTID AND DEPT.EFF_STATUS='A' AND DEPT.SETID= B.SETID_DEPT AND %EffdtCheck(DEPT_TBL DEPT1, DEPT, %CurrentDateIn)),'') , B.LOCATION , COALESCE(( SELECT LOC.DESCR FROM PS_LOCATION_TBL LOC WHERE LOC.LOCATION=B.LOCATION AND LOC.EFF_STATUS='A' AND LOC.SETID=B.SETID_LOCATION AND %EffdtCheck(LOCATION_TBL LOC1, LOC, %CurrentDateIn)),'') , B.JOBCODE , COALESCE(( SELECT JTBL.DESCR FROM PS_JOBCODE_TBL JTBL WHERE JTBL.JOBCODE=B.JOBCODE AND JTBL.EFF_STATUS='A' AND JTBL.SETID=B.SETID_JOBCODE AND %EffdtCheck(JOBCODE_TBL JTBL1, JTBL, %CurrentDateIn)),'') , %Round(B.ANNUAL_RT,2) , B.CURRENCY_CD , APPR.EP_RATING , CASE WHEN (CASE WHEN B.CURRENCY_CD <> GRD.CURRENCY_CD THEN %Round(%DecDiv((SELECT %DecMULT(%DecDiv(B.HOURLY_RT, RT1.RATE_DIV),RT1.RATE_MULT) FROM PS_HR_TM_PG_RT_VW RT1 WHERE RT1.FROM_CUR=B.CURRENCY_CD),(SELECT %DecMULT(%DecDiv(GRD.MID_RT_HOURLY, RT2.RATE_DIV),RT2.RATE_MULT) FROM PS_HR_TM_PG_RT_VW RT2 WHERE RT2.FROM_CUR=GRD.CURRENCY_CD)),2) ELSE CASE WHEN GRD.MID_RT_HOURLY = 0 THEN 0 ELSE %Round((%DecDiv(B.HOURLY_RT, GRD.MID_RT_HOURLY)),2) END END ) > 9.99 THEN 9.99 ELSE (CASE WHEN B.CURRENCY_CD <> GRD.CURR ENCY_CD THEN %Round(%DecDiv((SELECT %DecMULT(%DecDiv(B.HOURLY_RT, RT1.RATE_DIV),RT1.RATE_MULT) FROM PS_HR_TM_PG_RT_VW RT1 WHERE RT1.FROM_CUR=B.CURRENCY_CD),(SELECT %DecMULT(%DecDiv(GRD.MID_RT_HOURLY, RT2.RATE_DIV),RT2.RATE_MULT) FROM PS_HR_TM_PG_RT_VW RT2 WHERE RT2.FROM_CUR=GRD.CURRENCY_CD)),2) ELSE CASE WHEN GRD.MID_RT_HOURLY = 0 THEN 0 ELSE %Round((%DecDiv(B.HOURLY_RT, GRD.MID_RT_HOURLY)),2) END END) END , RATE.DESCR , C.NAME_DISPLAY FROM PS_HR_DIRECT_REP_6 A , PS_JOB B , PS_SAL_GRADE_TBL GRD , PS_EP_APPR APPR , PS_REVW_RATING_TBL RATE , PS_PERSONAL_DATA C , PS_EP_DEFAULT EPDFLT WHERE 6 = ( SELECT CFG.ACCESS_TYPE FROM PS_HR_DR_UI_CFG CFG WHERE CFG.PNLGRPNAME='HR_DR_TEAM_FLU') AND A.HR_DR_LEVEL = 1 AND A.DRILL_DOWN_FLAG = 'Y' AND A.EMPLID = B.EMPLID AND A.EMPL_RCD = B.EMPL_RCD AND %EffdtCheck(JOB JB1, B, %CurrentDateIn) AND B.EFFSEQ = ( SELECT MAX(JB2.EFFSEQ) FROM PS_JOB JB2 WHERE JB2.EMPLID = B.EMPLID AND JB2.EFFDT = B.EFFDT AND JB2.EMPL_RCD = B.EMPL_RCD) AND B.SETID_SALARY = GRD.SETID AND B.SAL_ADMIN_PLAN = GRD.SAL_ADMIN_PLAN AND B.GRADE = GRD.GRADE AND GRD.EFF_STATUS = 'A' AND %EffdtCheck(SAL_GRADE_TBL ED2, GRD, %CurrentDateIn) AND B.EMPLID = APPR.EMPLID AND B.EMPL_RCD = APPR.EMPL_RCD AND APPR.EP_REVIEW_STATUS = 'CO' AND APPR.EP_REVIEW_TYPE=EPDFLT.EP_REVIEW_TYPE AND EPDFLT.EP_TRANSACTION_NM = 'M' AND APPR.PERIOD_END_DT =( SELECT MAX(A1.PERIOD_END_DT) FROM PS_EP_APPR A1 WHERE APPR.EMPLID =A1.EMPLID AND APPR.EMPL_RCD=A1.EMPL_RCD AND APPR.EP_REVIEW_STATUS=A1.EP_REVIEW_STATUS AND APPR.EP_REVIEW_TYPE=A1.EP_REVIEW_TYPE ) AND RATE.RATING_MODEL = APPR.RATING_MODEL AND RATE.REVIEW_RATING = APPR.REVIEW_RATING AND %EffdtCheck(REVW_RATING_TBL D, RATE, %CurrentDateIn) AND A.EMPLID = C.EMPLID AND (B.PER_ORG = 'EMP' OR B.PER_ORG = 'CWR')

  • Related Language Record: HR_TM_PG_LVW

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

    Default Value: %date

    6 DEPTID Character(10) VARCHAR2(10) NOT NULL Department
    7 DESCR1 Character(30) VARCHAR2(30) NOT NULL Descr
    8 LOCATION Character(10) VARCHAR2(10) NOT NULL Location Code
    9 DESCR2 Character(30) VARCHAR2(30) NOT NULL Descr2
    10 JOBCODE Character(6) VARCHAR2(6) NOT NULL Job Code
    11 DESCR3 Character(30) VARCHAR2(30) NOT NULL Descr 3
    12 ANNUAL_RT Number(19,3) DECIMAL(18,3) NOT NULL Annual Rate
    13 CURRENCY_CD Character(3) VARCHAR2(3) NOT NULL Currency Code
    14 EP_RATING Number(6,2) DECIMAL(5,2) NOT NULL Rating
    15 COMPA_RATIO Number(4,2) DECIMAL(3,2) NOT NULL Compa-Ratio
    16 DESCR Character(30) VARCHAR2(30) NOT NULL Description
    17 NAME_DISPLAY Character(50) VARCHAR2(50) NOT NULL Display Name - name formatted for Display based on the Country