WCS_CMPPF_LVW

(SQL View)
 Index Back

Targeted VC Performance Relate


SELECT DMG.BUDGET_ID , DMG.BUDGET_START_DT , DMG.TREE_NODE_NUM , STS.TREE_NODE_NUM_END , dmg.emplid , DMG.EMPL_RCD , CSALLNG.LANGUAGE_CD , DEPTLNG.Descr , ratel.descr , xlatl.xlatlongname , CSALLNG.DESCR20 , 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 PSMSGCATLANG WHERE MESSAGE_SET_NBR = 1020 AND MESSAGE_NBR = 3035 AND LANGUAGE_CD=CSALLNG.LANGUAGE_CD) ELSE ( SELECT %Substring(MESSAGE_TEXT, 1, 30) FROM PSMSGCATLANG WHERE MESSAGE_SET_NBR = 1020 AND MESSAGE_NBR = 3036 AND LANGUAGE_CD=CSALLNG.LANGUAGE_CD )END) ELSE (CASE WHEN DMG.TREE_NODE_NUM=CTL.TREE_NODE_NUM THEN ( SELECT %Substring(MESSAGE_TEXT, 1, 30) FROM PSMSGCATLANG WHERE MESSAGE_SET_NBR = 1020 AND MESSAGE_NBR = 3035 AND LANGUAGE_CD=CSALLNG.LANGUAGE_CD ) ELSE ( SELECT %Substring(MESSAGE_TEXT, 1, 30) FROM PSMSGCATLANG WHERE MESSAGE_SET_NBR = 1020 AND MESSAGE_NBR = 3036 AND LANGUAGE_CD=CSALLNG.LANGUAGE_CD ) END) END, JTBLLNG.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_CYC_VC_LNG CSALLNG , ps_wcs_ecm_cyc_cfg CFG , PS_EP_APPR APPR , PS_REVW_RATING_LNG RATEL , PSXLATITEMLANG xlatl, PS_JOB JOB1 , PS_JOBCODE_LANG JTBLLNG , PS_DEPT_TBL_LANG DEPTLNG 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 CSAL.WCS_VC_TYPE='C' AND CSALLNG.BUDGET_ID = CSAL.BUDGET_ID AND CSALLNG.BUDGET_START_DT = CSAL.BUDGET_START_DT AND CSALLNG.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 RATEL.rating_model= APPR.rating_model AND RATEL.review_rating=DMG.REVIEW_RATING AND RATEL.EFFDT = ( SELECT MAX(EFFDT) FROM PS_REVW_RATING_LNG F WHERE F.rating_model = RATEL.rating_model AND F.review_rating=RATEL.review_rating AND F.EFFDT <= %CurrentDateIn) AND dmg.wcs_annual_rt<>0 AND xlatl.fieldvalue=cfg.wcs_rating_type AND xlatl.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 JTBLLNG.JOBCODE=DMG.JOBCODE AND JTBLLNG.SETID=JOB1.SETID_JOBCODE AND JTBLLNG.EFFDT=( SELECT MAX(JTBL1.EFFDT) FROM PS_JOBCODE_LANG JTBL1 WHERE JTBL1.JOBCODE=JTBLLNG.JOBCODE AND JTBL1.EFFDT<=%CurrentDateIn AND JTBL1.setid=JTBLLNG.SETID) AND DEPTLNG.DEPTID=dmg.deptid AND DEPTLNG.SETID=JOB1.SETID_DEPT AND DEPTLNG.EFFDT= ( SELECT MAX(EFFDT) FROM PS_DEPT_TBL_LANG DEPT1 WHERE DEPT1.DEPTID=DEPTLNG.DEPTID AND DEPT1.effdt<=%CurrentDateIn AND DEPT1.setid=DEPTLNG.SETID) AND CSALLNG.LANGUAGE_CD =RATEL.LANGUAGE_CD AND CSALLNG.LANGUAGE_CD =xlatl.LANGUAGE_CD AND JTBLLNG.LANGUAGE_CD=CSALLNG.LANGUAGE_CD AND DEPTLNG.LANGUAGE_CD=CSALLNG.LANGUAGE_CD

  • Related Language Record for WCS_PG_CMPPF_VW

    • # 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 LANGUAGE_CD Character(3) VARCHAR2(3) NOT NULL Language Code
    8 DESCR4 Character(30) VARCHAR2(30) NOT NULL Descr 4
    9 DESCR Character(30) VARCHAR2(30) NOT NULL Description
    10 DESCR1 Character(30) VARCHAR2(30) NOT NULL Descr
    11 DESCR20 Character(20) VARCHAR2(20) NOT NULL Description
    12 DESCR2 Character(30) VARCHAR2(30) NOT NULL Descr2
    13 DESCR3 Character(30) VARCHAR2(30) NOT NULL Descr 3