WCS_PG_SALPF_VW

(SQL View)
 Index Back

Salary by Performance View


SELECT SLPF.BUDGET_ID , SLPF.BUDGET_START_DT , SLPF.TREE_NODE_NUM , SLPF.TREE_NODE_NUM_END , SLPF.emplid , SLPF.empl_rcd , emp.name , DMG.deptid , DEPT.Descr , rate.descr , CASE WHEN cfg.wcs_rating_type='EPR' THEN %NumToChar(DMG.ep_rating) WHEN cfg.wcs_rating_type='RVP' THEN %NumToChar(DMG.review_points) WHEN cfg.wcs_rating_type='RVR' THEN DMG.REVIEW_RATING END , xlat.xlatlongname , SLPF.WCS_P_AMT , %Round(SLPF.WCS_C_PCT, 1) , DMG.currency_cd , CSAL.DESCR20 , SLPF.SEQNUM , SLPF.WCS_MIN_PCT , SLPF.WCS_MAX_PCT , CASE WHEN CTL.TREE_NODE_NUM IS NULL THEN (CASE WHEN SLPF.TREE_NODE_NUM=( SELECT MIN(MTX.TREE_NODE_NUM) FROM PS_WCS_ECM_MTX_VW MTX WHERE SLPF.BUDGET_ID = MTX.BUDGET_ID AND SLPF.BUDGET_START_DT = MTX.BUDGET_START_DT AND MGR.MANAGER_ID=MTX.MANAGER_ID GROUP BY MTX.BUDGET_ID, MTX.BUDGET_START_DT, MTX.MANAGER_ID) THEN ( SELECT %Substring(MESSAGE_TEXT, 1, 30) FROM PSMSGCATDEFN WHERE MESSAGE_SET_NBR = 1020 AND MESSAGE_NBR = 3035) ELSE ( SELECT %Substring(MESSAGE_TEXT, 1, 30) FROM PSMSGCATDEFN WHERE MESSAGE_SET_NBR = 1020 AND MESSAGE_NBR = 3036) END) ELSE (CASE WHEN SLPF.TREE_NODE_NUM=CTL.TREE_NODE_NUM THEN ( SELECT %Substring(MESSAGE_TEXT, 1, 30) FROM PSMSGCATDEFN WHERE MESSAGE_SET_NBR = 1020 AND MESSAGE_NBR = 3035) ELSE ( SELECT %Substring(MESSAGE_TEXT, 1, 30) FROM PSMSGCATDEFN WHERE MESSAGE_SET_NBR = 1020 AND MESSAGE_NBR = 3036) END) END, MGR.OPRID, DMG.JOBCODE, JTBL.DESCR FROM PS_WCS_PG_SLPF_VW1 SLPF , PS_WCS_ECM_EE_DMG DMG , PS_WCS_ECM_MGR_VW MGR LEFT OUTER JOIN PS_WCS_PG_RUN_CNTL CTL ON MGR.BUDGET_ID = CTL.BUDGET_ID AND MGR.BUDGET_START_DT = CTL.BUDGET_START_DT AND MGR.OPRID=CTL.OPRID, PS_WCS_ECM_CYC_SAL CSAL , ps_wcs_ecm_cyc_cfg CFG , PS_EP_APPR APPR , PS_REVW_RATING_TBL RATE , PS_EMPLEE_NAME_VW EMP , xlattable_vw xlat , PS_JOBCODE_TBL JTBL , PS_JOB JOB1 , PS_DEPT_TBL DEPT WHERE DMG.BUDGET_ID = SLPF.BUDGET_ID AND DMG.BUDGET_START_DT = SLPF.BUDGET_START_DT AND DMG.TREE_NODE_NUM = SLPF.TREE_NODE_NUM AND DMG.emplid= SLPF.emplid AND DMG.empl_rcd= SLPF.empl_rcd AND DMG.BUDGET_ID = MGR.BUDGET_ID AND DMG.BUDGET_START_DT = MGR.BUDGET_START_DT AND cfg.budget_id= SLPF.budget_id AND SLPF.BUDGET_ID = CSAL.BUDGET_ID AND SLPF.BUDGET_START_DT = CSAL.BUDGET_START_DT AND SLPF.SEQNUM = CSAL.SEQNUM AND APPR.emplid= SLPF.emplid AND APPR.ep_review_type= cfg.ep_review_type AND APPR.period_begin_dt=cfg.period_begin_dt AND APPR.period_end_dt= cfg.period_end_dt AND rate.rating_model= APPR.rating_model AND (rate.review_rating=DMG.REVIEW_RATING) AND RATE.EFFDT = ( SELECT MAX(EFFDT) FROM PS_REVW_RATING_TBL F WHERE F.rating_model = RATE.rating_model AND F.review_rating=RATE.review_rating AND EFFDT <= %CurrentDateIn) AND EMP.emplid=dmg.emplid AND dmg.wcs_annual_rt<>0 AND xlat.fieldvalue=cfg.wcs_rating_type AND xlat.fieldname='WCS_RATING_TYPE' AND JOB1.emplid=dmg.emplid AND JOB1.empl_rcd=DMG.empl_rcd AND JOB1.EFFDT = DMG.JOB_EFFDT AND JOB1.EFFSEQ = DMG.JOB_EFFSEQ AND JOB1.JOBCODE=DMG.JOBCODE AND JTBL.JOBCODE=DMG.JOBCODE AND JTBL.EFF_STATUS='A' AND JTBL.SETID=JOB1.SETID_JOBCODE AND JTBL.EFFDT=( SELECT MAX(JTBL1.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) AND DEPT.DEPTID=dmg.deptid 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) AND DEPT.EFF_STATUS='A' AND DEPT.SETID=JOB1.SETID_DEPT

  • Related Language Record: WCS_SALPF_LVW

    • # PeopleSoft Field Name PeopleSoft Field Type Database Column Type Description
    1 BUDGET_ID Character(10) VARCHAR2(10) NOT NULL Budget ID
    2 BUDGET_START_DT Date(10) DATE Budget Start Date
    3 TREE_NODE_NUM Number(10,0) DECIMAL(10) NOT NULL Internal beginning node range number
    4 TREE_NODE_NUM_END Number(10,0) DECIMAL(10) NOT NULL Internal ending node range number
    5 EMPLID Character(11) VARCHAR2(11) NOT NULL Employee ID
    6 EMPL_RCD Number(3,0) SMALLINT NOT NULL Empl Record
    7 EMPLNAME Character(50) VARCHAR2(50) NOT NULL EmplName
    8 DEPTID Character(10) VARCHAR2(10) NOT NULL Department
    9 DESCR4 Character(30) VARCHAR2(30) NOT NULL Descr 4
    10 DESCR Character(30) VARCHAR2(30) NOT NULL Description
    11 DESCRSHORT Character(10) VARCHAR2(10) NOT NULL Short Description
    12 DESCR1 Character(30) VARCHAR2(30) NOT NULL Descr
    13 WCS_P_AMT Number(16,6) DECIMAL(15,6) NOT NULL Employee Proposed Amount
    14 WCS_C_PCT Number(6,2) DECIMAL(5,2) NOT NULL Calculated Percent
    15 CURRENCY_CD Character(3) VARCHAR2(3) NOT NULL Currency Code
    16 DESCR20 Character(20) VARCHAR2(20) NOT NULL Description
    17 SEQNUM Number(3,0) SMALLINT NOT NULL Sequence Number
    18 WCS_MIN_PCT Number(6,2) DECIMAL(5,2) NOT NULL Minimum Percentage
    19 WCS_MAX_PCT Number(6,2) DECIMAL(5,2) NOT NULL Maximum Percentage
    20 DESCR2 Character(30) VARCHAR2(30) NOT NULL Descr2
    21 OPRID Character(30) VARCHAR2(30) NOT NULL A user's ID (see PSOPRDEFN).
    22 JOBCODE Character(6) VARCHAR2(6) NOT NULL Job Code
    23 DESCR3 Character(30) VARCHAR2(30) NOT NULL Descr 3