WCS_PG_CMPPF_VW

(SQL View)
 Index Back

VC by Performance View


SELECT DMG.BUDGET_ID , DMG.BUDGET_START_DT , DMG.TREE_NODE_NUM , STS.TREE_NODE_NUM_END , DMG.emplid , DMG.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 , SAL.WCS_P_AMT , CASE WHEN %DecMult(%DecDiv(%Coalesce(SAL.WCS_P_AMT, SAL.WCS_A_AMT), dmg.wcs_annual_rt), 100) > 999.99 THEN 999.99 ELSE %Round(%DecMult(%DecDiv(%Coalesce(SAL.WCS_P_AMT, SAL.WCS_A_AMT), dmg.wcs_annual_rt), 100),2) END , DMG.currency_cd , CSAL.DESCR20 , SAL.VC_PLAN_ID , CASE WHEN CTL.TREE_NODE_NUM IS NULL THEN (CASE WHEN DMG.TREE_NODE_NUM=( SELECT MIN(MTX.TREE_NODE_NUM) FROM PS_WCS_ECM_MTX_VW MTX WHERE DMG.BUDGET_ID = MTX.BUDGET_ID AND DMG.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 DMG.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_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_NOD_STS STS , PS_WCS_ECM_EE_VC SAL , PS_WCS_ECM_CYC_VC 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 = MGR.BUDGET_ID AND DMG.BUDGET_START_DT = MGR.BUDGET_START_DT AND DMG.BUDGET_ID = STS.BUDGET_ID AND DMG.BUDGET_START_DT = STS.BUDGET_START_DT AND DMG.TREE_NODE_NUM = STS.TREE_NODE_NUM AND DMG.BUDGET_ID = SAL.BUDGET_ID AND DMG.BUDGET_START_DT = SAL.BUDGET_START_DT AND DMG.TREE_NODE_NUM = SAL.TREE_NODE_NUM AND DMG.EMPLID = SAL.EMPLID AND DMG.EMPL_RCD = SAL.EMPL_RCD AND cfg.budget_id= dmg.budget_id AND SAL.BUDGET_ID = CSAL.BUDGET_ID AND SAL.BUDGET_START_DT = CSAL.BUDGET_START_DT AND SAL.VC_PLAN_ID = CSAL.VC_PLAN_ID AND APPR.emplid= dmg.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(F.EFFDT) FROM PS_REVW_RATING_TBL F WHERE F.rating_model = RATE.rating_model AND F.review_rating=RATE.review_rating AND F.EFFDT <= %CurrentDateIn) 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 EMP.emplid=DMG.emplid AND DMG.wcs_annual_rt<>0 AND xlat.fieldvalue=cfg.wcs_rating_type AND xlat.fieldname='WCS_RATING_TYPE' AND DEPT.DEPTID=DMG.deptid AND DEPT.EFFDT= ( SELECT MAX(DEPT1.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 AND CSAL.WCS_VC_TYPE='C'

  • Related Language Record: WCS_CMPPF_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 VC_PLAN_ID Character(10) VARCHAR2(10) NOT NULL VC Plan ID
    18 DESCR2 Character(30) VARCHAR2(30) NOT NULL Descr2
    19 OPRID Character(30) VARCHAR2(30) NOT NULL A user's ID (see PSOPRDEFN).
    20 JOBCODE Character(6) VARCHAR2(6) NOT NULL Job Code
    21 DESCR3 Character(30) VARCHAR2(30) NOT NULL Descr 3