HR_GSS_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 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 , 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_GSS_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_GSS_PG_RT_VW RT2 WHERE RT2.FROM_CUR=GRD.CURRENCY_CD)),2) ELSE %Round((%DecDiv(B.HOURLY_RT, GRD.MID_RT_HOURLY)),2) 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_GSS_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_GSS_PG_RT_VW RT2 WHERE RT2.FROM_CUR=GRD.CURRENCY_CD)),2) ELSE %Round((%DecDiv(B.HOURLY_RT, GRD.MID_RT_HOURLY)),2) END) END , APPR.DESCR , C.NAME_DISPLAY FROM PS_HR_DIRECT_REP_2 A , PS_JOB B LEFT OUTER JOIN PS_HR_GSS_PG_GRD_V GRD ON B.SETID_SALARY = GRD.SETID AND B.SAL_ADMIN_PLAN = GRD.SAL_ADMIN_PLAN AND B.GRADE = GRD.GRADE LEFT OUTER JOIN PS_HR_GSS_PG_EP_VW APPR ON B.EMPLID = APPR.EMPLID AND B.EMPL_RCD = APPR.EMPL_RCD , PS_PERSON_NAME C WHERE 2 = ( SELECT CFG.ACCESS_TYPE FROM PS_HR_DR_UI_CFG CFG WHERE CFG.PNLGRPNAME='HR_MSS_CT_FL') 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 , 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_GSS_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_GSS_PG_RT_VW RT2 WHERE RT2.FROM_CUR=GRD.CURRENCY_CD)),2) ELSE %Round((%DecDiv(B.HOURLY_RT, GRD.MID_RT_HOURLY)),2) 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_GSS_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_GSS_PG_RT_VW RT2 WHERE RT2.FROM_CUR=GRD.CURRENCY_CD)),2) ELSE %Round((%DecDiv(B.HOURLY_RT, GRD.MID_RT_HOURLY)),2) END) END , APPR.DESCR , C.NAME_DISPLAY FROM PS_HR_DIRECT_REP_3 A , PS_JOB B LEFT OUTER JOIN PS_HR_GSS_PG_GRD_V GRD ON B.SETID_SALARY = GRD.SETID AND B.SAL_ADMIN_PLAN = GRD.SAL_ADMIN_PLAN AND B.GRADE = GRD.GRADE LEFT OUTER JOIN PS_HR_GSS_PG_EP_VW APPR ON B.EMPLID = APPR.EMPLID AND B.EMPL_RCD = APPR.EMPL_RCD , PS_PERSON_NAME C WHERE 3 = ( SELECT CFG.ACCESS_TYPE FROM PS_HR_DR_UI_CFG CFG WHERE CFG.PNLGRPNAME='HR_MSS_CT_FL') 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 , 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_GSS_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_GSS_PG_RT_VW RT2 WHERE RT2.FROM_CUR=GRD.CURRENCY_CD)),2) ELSE %Round((%DecDiv(B.HOURLY_RT, GRD.MID_RT_HOURLY)),2) 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_GSS_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_GSS_PG_RT_VW RT2 WHERE RT2.FROM_CUR=GRD.CURRENCY_CD)),2) ELSE %Round((%DecDiv(B.HOURLY_RT, GRD.MID_RT_HOURLY)),2) END) END , APPR.DESCR , C.NAME_DISPLAY FROM PS_HR_DIRECT_REP_4 A , PS_JOB B LEFT OUTER JOIN PS_HR_GSS_PG_GRD_V GRD ON B.SETID_SALARY = GRD.SETID AND B.SAL_ADMIN_PLAN = GRD.SAL_ADMIN_PLAN AND B.GRADE = GRD.GRADE LEFT OUTER JOIN PS_HR_GSS_PG_EP_VW APPR ON B.EMPLID = APPR.EMPLID AND B.EMPL_RCD = APPR.EMPL_RCD , PS_PERSON_NAME C WHERE 4 = ( SELECT CFG.ACCESS_TYPE FROM PS_HR_DR_UI_CFG CFG WHERE CFG.PNLGRPNAME='HR_MSS_CT_FL') 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 , 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_GSS_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_GSS_PG_RT_VW RT2 WHERE RT2.FROM_CUR=GRD.CURRENCY_CD)),2) ELSE %Round((%DecDiv(B.HOURLY_RT, GRD.MID_RT_HOURLY)),2) 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_GSS_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_GSS_PG_RT_VW RT2 WHERE RT2.FROM_CUR=GRD.CURRENCY_CD)),2) ELSE %Round((%DecDiv(B.HOURLY_RT, GRD.MID_RT_HOURLY)),2) END) END , APPR.DESCR , C.NAME_DISPLAY FROM PS_HR_DIRECT_REP_5 A , PS_JOB B LEFT OUTER JOIN PS_HR_GSS_PG_GRD_V GRD ON B.SETID_SALARY = GRD.SETID AND B.SAL_ADMIN_PLAN = GRD.SAL_ADMIN_PLAN AND B.GRADE = GRD.GRADE LEFT OUTER JOIN PS_HR_GSS_PG_EP_VW APPR ON B.EMPLID = APPR.EMPLID AND B.EMPL_RCD = APPR.EMPL_RCD , PS_PERSON_NAME C WHERE 5 = ( SELECT CFG.ACCESS_TYPE FROM PS_HR_DR_UI_CFG CFG WHERE CFG.PNLGRPNAME='HR_MSS_CT_FL') 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 , 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_GSS_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_GSS_PG_RT_VW RT2 WHERE RT2.FROM_CUR=GRD.CURRENCY_CD)),2) ELSE %Round((%DecDiv(B.HOURLY_RT, GRD.MID_RT_HOURLY)),2) 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_GSS_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_GSS_PG_RT_VW RT2 WHERE RT2.FROM_CUR=GRD.CURRENCY_CD)),2) ELSE %Round((%DecDiv(B.HOURLY_RT, GRD.MID_RT_HOURLY)),2) END) END , APPR.DESCR , C.NAME_DISPLAY FROM PS_HR_DIRECT_REP_6 A , PS_JOB B LEFT OUTER JOIN PS_HR_GSS_PG_GRD_V GRD ON B.SETID_SALARY = GRD.SETID AND B.SAL_ADMIN_PLAN = GRD.SAL_ADMIN_PLAN AND B.GRADE = GRD.GRADE LEFT OUTER JOIN PS_HR_GSS_PG_EP_VW APPR ON B.EMPLID = APPR.EMPLID AND B.EMPL_RCD = APPR.EMPL_RCD , PS_PERSON_NAME C WHERE 6 = ( SELECT CFG.ACCESS_TYPE FROM PS_HR_DR_UI_CFG CFG WHERE CFG.PNLGRPNAME='HR_MSS_CT_FL') 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_GSS_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