HR_TM_COMP_VW

(SQL View)
 Index Back

Current Annual Salary

Current Annual Salary for use in My Team analytics.

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 DEPT.EFFDT=( SELECT MAX(EFFDT) FROM PS_DEPT_TBL DEPT1 WHERE DEPT1.DEPTID=DEPT.DEPTID AND DEPT1.effdt<=%CurrentDateIn AND DEPT1.eff_status=DEPT.EFF_STATUS AND DEPT1.setid=DEPT.SETID)),'') , 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 LOC.EFFDT=( SELECT MAX(EFFDT) FROM PS_LOCATION_TBL LOC1 WHERE LOC1.LOCATION=LOC.LOCATION AND LOC1.effdt<=%CurrentDateIn AND LOC1.eff_status=LOC.EFF_STATUS AND LOC1.setid=LOC.SETID)),'') , 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 JTBL.EFFDT=( SELECT MAX(EFFDT) FROM PS_JOBCODE_TBL JTBL1 WHERE JTBL1.JOBCODE=JTBL.JOBCODE AND JTBL1.EFFDT<=%CurrentDateIn AND JTBL1.eff_status=JTBL.eff_status AND JTBL1.setid=JTBL.SETID)),'') , %Round(B.ANNUAL_RT,2) , B.CURRENCY_CD , 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_COMP_RT_V 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_COMP_RT_V 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_COMP_RT_V 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_COMP_RT_V 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 , C.NAME_DISPLAY FROM PS_HR_DIRECT_REP_2 A , PS_JOB B LEFT OUTER JOIN PS_HR_TM_GRD_VW GRD ON B.SETID_SALARY = GRD.SETID AND B.SAL_ADMIN_PLAN = GRD.SAL_ADMIN_PLAN AND B.GRADE = GRD.GRADE AND GRD.EFF_STATUS = 'A' , PS_PERSON_NAME C 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 B.EFFDT = ( SELECT MAX(EFFDT) FROM PS_JOB JB1 WHERE JB1.EMPLID = B.EMPLID AND JB1.EMPL_RCD = B.EMPL_RCD AND JB1.EFFDT <= %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 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 DEPT.EFFDT=( SELECT MAX(EFFDT) FROM PS_DEPT_TBL DEPT1 WHERE DEPT1.DEPTID=DEPT.DEPTID AND DEPT1.effdt<=%CurrentDateIn AND DEPT1.eff_status=DEPT.EFF_STATUS AND DEPT1.setid=DEPT.SETID)),'') , 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 LOC.EFFDT=( SELECT MAX(EFFDT) FROM PS_LOCATION_TBL LOC1 WHERE LOC1.LOCATION=LOC.LOCATION AND LOC1.effdt<=%CurrentDateIn AND LOC1.eff_status=LOC.EFF_STATUS AND LOC1.setid=LOC.SETID)),'') , 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 JTBL.EFFDT=( SELECT MAX(EFFDT) FROM PS_JOBCODE_TBL JTBL1 WHERE JTBL1.JOBCODE=JTBL.JOBCODE AND JTBL1.EFFDT<=%CurrentDateIn AND JTBL1.eff_status=JTBL.eff_status AND JTBL1.setid=JTBL.SETID)),'') , %Round(B.ANNUAL_RT,2) , B.CURRENCY_CD , 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_COMP_RT_V 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_COMP_RT_V 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_COMP_RT_V 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_COMP_RT_V 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 , C.NAME_DISPLAY FROM PS_HR_DIRECT_REP_3 A , PS_JOB B LEFT OUTER JOIN PS_HR_TM_GRD_VW GRD ON B.SETID_SALARY = GRD.SETID AND B.SAL_ADMIN_PLAN = GRD.SAL_ADMIN_PLAN AND B.GRADE = GRD.GRADE AND GRD.EFF_STATUS = 'A' , PS_PERSON_NAME C 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 B.EFFDT = ( SELECT MAX(EFFDT) FROM PS_JOB JB1 WHERE JB1.EMPLID = B.EMPLID AND JB1.EMPL_RCD = B.EMPL_RCD AND JB1.EFFDT <= %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 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 DEPT.EFFDT=( SELECT MAX(EFFDT) FROM PS_DEPT_TBL DEPT1 WHERE DEPT1.DEPTID=DEPT.DEPTID AND DEPT1.effdt<=%CurrentDateIn AND DEPT1.eff_status=DEPT.EFF_STATUS AND DEPT1.setid=DEPT.SETID)),'') , 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 LOC.EFFDT=( SELECT MAX(EFFDT) FROM PS_LOCATION_TBL LOC1 WHERE LOC1.LOCATION=LOC.LOCATION AND LOC1.effdt<=%CurrentDateIn AND LOC1.eff_status=LOC.EFF_STATUS AND LOC1.setid=LOC.SETID)),'') , 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 JTBL.EFFDT=( SELECT MAX(EFFDT) FROM PS_JOBCODE_TBL JTBL1 WHERE JTBL1.JOBCODE=JTBL.JOBCODE AND JTBL1.EFFDT<=%CurrentDateIn AND JTBL1.eff_status=JTBL.eff_status AND JTBL1.setid=JTBL.SETID)),'') , %Round(B.ANNUAL_RT,2) , B.CURRENCY_CD , 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_COMP_RT_V 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_COMP_RT_V 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_COMP_RT_V 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_COMP_RT_V 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 , C.NAME_DISPLAY FROM PS_HR_DIRECT_REP_4 A , PS_JOB B LEFT OUTER JOIN PS_HR_TM_GRD_VW GRD ON B.SETID_SALARY = GRD.SETID AND B.SAL_ADMIN_PLAN = GRD.SAL_ADMIN_PLAN AND B.GRADE = GRD.GRADE AND GRD.EFF_STATUS = 'A' , PS_PERSON_NAME C 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 B.EFFDT = ( SELECT MAX(EFFDT) FROM PS_JOB JB1 WHERE JB1.EMPLID = B.EMPLID AND JB1.EMPL_RCD = B.EMPL_RCD AND JB1.EFFDT <= %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 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 DEPT.EFFDT=( SELECT MAX(EFFDT) FROM PS_DEPT_TBL DEPT1 WHERE DEPT1.DEPTID=DEPT.DEPTID AND DEPT1.effdt<=%CurrentDateIn AND DEPT1.eff_status=DEPT.EFF_STATUS AND DEPT1.setid=DEPT.SETID)),'') , 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 LOC.EFFDT=( SELECT MAX(EFFDT) FROM PS_LOCATION_TBL LOC1 WHERE LOC1.LOCATION=LOC.LOCATION AND LOC1.effdt<=%CurrentDateIn AND LOC1.eff_status=LOC.EFF_STATUS AND LOC1.setid=LOC.SETID)),'') , 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 JTBL.EFFDT=( SELECT MAX(EFFDT) FROM PS_JOBCODE_TBL JTBL1 WHERE JTBL1.JOBCODE=JTBL.JOBCODE AND JTBL1.EFFDT<=%CurrentDateIn AND JTBL1.eff_status=JTBL.eff_status AND JTBL1.setid=JTBL.SETID)),'') , %Round(B.ANNUAL_RT,2) , B.CURRENCY_CD , 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_COMP_RT_V 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_COMP_RT_V 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_COMP_RT_V 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_COMP_RT_V 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 , C.NAME_DISPLAY FROM PS_HR_DIRECT_REP_5 A , PS_JOB B LEFT OUTER JOIN PS_HR_TM_GRD_VW GRD ON B.SETID_SALARY = GRD.SETID AND B.SAL_ADMIN_PLAN = GRD.SAL_ADMIN_PLAN AND B.GRADE = GRD.GRADE AND GRD.EFF_STATUS = 'A' , PS_PERSON_NAME C 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 B.EFFDT = ( SELECT MAX(EFFDT) FROM PS_JOB JB1 WHERE JB1.EMPLID = B.EMPLID AND JB1.EMPL_RCD = B.EMPL_RCD AND JB1.EFFDT <= %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 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 DEPT.EFFDT=( SELECT MAX(EFFDT) FROM PS_DEPT_TBL DEPT1 WHERE DEPT1.DEPTID=DEPT.DEPTID AND DEPT1.effdt<=%CurrentDateIn AND DEPT1.eff_status=DEPT.EFF_STATUS AND DEPT1.setid=DEPT.SETID)),'') , 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 LOC.EFFDT=( SELECT MAX(EFFDT) FROM PS_LOCATION_TBL LOC1 WHERE LOC1.LOCATION=LOC.LOCATION AND LOC1.effdt<=%CurrentDateIn AND LOC1.eff_status=LOC.EFF_STATUS AND LOC1.setid=LOC.SETID)),'') , 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 JTBL.EFFDT=( SELECT MAX(EFFDT) FROM PS_JOBCODE_TBL JTBL1 WHERE JTBL1.JOBCODE=JTBL.JOBCODE AND JTBL1.EFFDT<=%CurrentDateIn AND JTBL1.eff_status=JTBL.eff_status AND JTBL1.setid=JTBL.SETID)),'') , %Round(B.ANNUAL_RT,2) , B.CURRENCY_CD , 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_COMP_RT_V 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_COMP_RT_V 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_COMP_RT_V 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_COMP_RT_V 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 , C.NAME_DISPLAY FROM PS_HR_DIRECT_REP_6 A , PS_JOB B LEFT OUTER JOIN PS_HR_TM_GRD_VW GRD ON B.SETID_SALARY = GRD.SETID AND B.SAL_ADMIN_PLAN = GRD.SAL_ADMIN_PLAN AND B.GRADE = GRD.GRADE AND GRD.EFF_STATUS = 'A' , PS_PERSON_NAME C 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 B.EFFDT = ( SELECT MAX(EFFDT) FROM PS_JOB JB1 WHERE JB1.EMPLID = B.EMPLID AND JB1 .EMPL_RCD = B.EMPL_RCD AND JB1.EFFDT <= %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 A.EMPLID = C.EMPLID AND (B.PER_ORG = 'EMP' OR B.PER_ORG = 'CWR')

  • Related Language Record: HR_TM_COMP_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 COMPA_RATIO Number(4,2) DECIMAL(3,2) NOT NULL Compa-Ratio
    15 NAME_DISPLAY Character(50) VARCHAR2(50) NOT NULL Display Name - name formatted for Display based on the Country