WCS_PG_CNT_VW

(SQL View)
 Index Back

Counts Pivot View


SELECT STS.BUDGET_ID , DMG.BUDGET_START_DT , DMG.TREE_NODE_NUM , STS.TREE_NODE_NUM_END , DMG.EMPLID , DMG.empl_rcd , emp.name , DMG.JOBCODE , STS.WCS_NODE_STATUS , xlat.xlatlongname , CASE WHEN (DMG.ANNUAL_RT > DMG.MAX_RT_ANNUAL) THEN '4-' || ( SELECT %Substring(MESSAGE_TEXT, 1, 10) FROM PSMSGCATDEFN WHERE MESSAGE_SET_NBR = 1020 AND MESSAGE_NBR = 313) WHEN (DMG.ANNUAL_RT <= DMG.MAX_RT_ANNUAL) AND (DMG.ANNUAL_RT >= DMG.MIN_RT_ANNUAL) THEN CASE WHEN (DMG.ANNUAL_RT >= DMG.MID_RT_ANNUAL) THEN '3-' || ( SELECT %Substring(MESSAGE_TEXT, 1, 8) FROM PSMSGCATDEFN WHERE MESSAGE_SET_NBR = 1020 AND MESSAGE_NBR = 314) || '_' || ( SELECT %Substring(MESSAGE_TEXT, 1, 8) FROM PSMSGCATDEFN WHERE MESSAGE_SET_NBR = 1020 AND MESSAGE_NBR = 313) || '' || ( SELECT %Substring(MESSAGE_TEXT, 1, 10) FROM PSMSGCATDEFN WHERE MESSAGE_SET_NBR = 1020 AND MESSAGE_NBR = 115) WHEN (DMG.ANNUAL_RT < DMG.MID_RT_ANNUAL) THEN '2-' || ( SELECT %Substring(MESSAGE_TEXT, 1, 8) FROM PSMSGCATDEFN WHERE MESSAGE_SET_NBR = 1020 AND MESSAGE_NBR = 314) || '_' || ( SELECT %Substring(MESSAGE_TEXT, 1, 8) FROM PSMSGCATDEFN WHERE MESSAGE_SET_NBR = 1020 AND MESSAGE_NBR = 306) || '' || ( SELECT %Substring(MESSAGE_TEXT, 1, 10) FROM PSMSGCATDEFN WHERE MESSAGE_SET_NBR = 1020 AND MESSAGE_NBR = 115) END WHEN (DMG.ANNUAL_RT < DMG.MIN_RT_ANNUAL) THEN '1-' || ( SELECT %Substring(MESSAGE_TEXT, 1, 10) FROM PSMSGCATDEFN WHERE MESSAGE_SET_NBR = 1020 AND MESSAGE_NBR = 306) END , JTBL.JOB_FAMILY , 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, 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_JOBCODE_TBL JTBL , PS_JOB JOB1 , PS_EMPLEE_NAME_VW EMP , xlattable_vw XLAT 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 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 XLAT.FIELDNAME='WCS_NODE_STATUS' AND XLAT.FIELDVALUE=STS.WCS_NODE_STATUS

  • Related Language Record: WCS_PG_CNT_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 JOBCODE Character(6) VARCHAR2(6) NOT NULL Job Code
    9 WCS_NODE_STATUS Character(3) VARCHAR2(3) NOT NULL Node Status
    CLC=Calculated
    CME=Completed with Errors
    CMP=Completed
    CNF=Confirmed
    INP=In Progress
    PSB=Pushed Back
    RTL=Ready to Load
    SUB=Submitted
    10 DESCR3 Character(30) VARCHAR2(30) NOT NULL Descr 3
    11 DESCR Character(30) VARCHAR2(30) NOT NULL Description
    12 JOB_FAMILY Character(6) VARCHAR2(6) NOT NULL Job Family
    13 DESCR2 Character(30) VARCHAR2(30) NOT NULL Descr2
    14 OPRID Character(30) VARCHAR2(30) NOT NULL A user's ID (see PSOPRDEFN).
    15 DESCR1 Character(30) VARCHAR2(30) NOT NULL Descr