WCS_PG_CNT_LVW

(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 , JTBLLNG.LANGUAGE_CD , xlatl.xlatlongname , CASE WHEN (DMG.ANNUAL_RT > DMG.MAX_RT_ANNUAL) THEN '4- ' || ( SELECT %Substring(MESSAGE_TEXT, 1, 10) FROM PSMSGCATLANG WHERE MESSAGE_SET_NBR = 1020 AND MESSAGE_NBR = 313 AND LANGUAGE_CD=JTBLLNG.LANGUAGE_CD) 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 PSMSGCATLANG WHERE MESSAGE_SET_NBR = 1020 AND MESSAGE_NBR = 314 AND LANGUAGE_CD=JTBLLNG.LANGUAGE_CD) || '_' || ( SELECT %Substring(MESSAGE_TEXT, 1, 8) FROM PSMSGCATLANG WHERE MESSAGE_SET_NBR = 1020 AND MESSAGE_NBR = 313 AND LANGUAGE_CD=JTBLLNG.LANGUAGE_CD) || '' || ( SELECT %Substring(MESSAGE_TEXT, 1, 10) FROM PSMSGCATLANG WHERE MESSAGE_SET_NBR = 1020 AND MESSAGE_NBR = 115 AND LANGUAGE_CD=JTBLLNG.LANGUAGE_CD) WHEN (DMG.ANNUAL_RT < DMG.MID_RT_ANNUAL) THEN '2- ' || ( SELECT %Substring(MESSAGE_TEXT, 1, 8) FROM PSMSGCATLANG WHERE MESSAGE_SET_NBR = 1020 AND MESSAGE_NBR = 314 AND LANGUAGE_CD=JTBLLNG.LANGUAGE_CD) || '_' || ( SELECT %Substring(MESSAGE_TEXT, 1, 8) FROM PSMSGCATLANG WHERE MESSAGE_SET_NBR = 1020 AND MESSAGE_NBR = 306 AND LANGUAGE_CD=JTBLLNG.LANGUAGE_CD) || '' || ( SELECT %Substring(MESSAGE_TEXT, 1, 10) FROM PSMSGCATLANG WHERE MESSAGE_SET_NBR = 1020 AND MESSAGE_NBR = 115 AND LANGUAGE_CD=JTBLLNG.LANGUAGE_CD) END WHEN (DMG.ANNUAL_RT < DMG.MIN_RT_ANNUAL) THEN '1- ' || ( SELECT %Substring(MESSAGE_TEXT, 1, 10) FROM PSMSGCATLANG WHERE MESSAGE_SET_NBR = 1020 AND MESSAGE_NBR = 306 AND LANGUAGE_CD=JTBLLNG.LANGUAGE_CD) END, 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=JTBLLNG.LANGUAGE_CD) ELSE ( SELECT %Substring(MESSAGE_TEXT, 1, 30) FROM PSMSGCATLANG WHERE MESSAGE_SET_NBR = 1020 AND MESSAGE_NBR = 3036 AND LANGUAGE_CD=JTBLLNG.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=JTBLLNG.LANGUAGE_CD ) ELSE ( SELECT %Substring(MESSAGE_TEXT, 1, 30) FROM PSMSGCATLANG WHERE MESSAGE_SET_NBR = 1020 AND MESSAGE_NBR = 3036 AND LANGUAGE_CD=JTBLLNG.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_JOB JOB1 , PS_JOBCODE_LANG JTBLLNG , PSXLATITEMLANG xlatl 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 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 xlatl.FIELDNAME='WCS_NODE_STATUS' AND xlatl.FIELDVALUE=STS.WCS_NODE_STATUS AND JTBLLNG.LANGUAGE_CD =xlatl.LANGUAGE_CD

  • Related Language Record for WCS_PG_CNT_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 DESCR3 Character(30) VARCHAR2(30) NOT NULL Descr 3
    9 DESCR Character(30) VARCHAR2(30) NOT NULL Description
    10 DESCR2 Character(30) VARCHAR2(30) NOT NULL Descr2
    11 DESCR1 Character(30) VARCHAR2(30) NOT NULL Descr