WCS_PEER_LVW3

(SQL View)
 Index Back

Peer Analysis Related


SELECT PEER.BUDGET_ID , PEER.BUDGET_START_DT , PEER.TREE_NODE_NUM , PEER.TREE_NODE_NUM_END , PEER.emplid , PEER.empl_rcd , COMPLNG.LANGUAGE_CD , DEPTLNG.Descr , LOCLNG.descr , JTBLLNG.descr , COMPLNG.descr , BULNG.descr , CASE WHEN CTL.TREE_NODE_NUM IS NULL THEN (CASE WHEN PEER.TREE_NODE_NUM=( SELECT MIN(MTX.TREE_NODE_NUM) FROM PS_WCS_ECM_MTX_VW MTX WHERE PEER.BUDGET_ID = MTX.BUDGET_ID AND PEER.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=COMPLNG.LANGUAGE_CD) ELSE ( SELECT %Substring(MESSAGE_TEXT, 1, 30) FROM PSMSGCATLANG WHERE MESSAGE_SET_NBR = 1020 AND MESSAGE_NBR = 3036 AND LANGUAGE_CD=COMPLNG.LANGUAGE_CD )END) ELSE (CASE WHEN PEER.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=COMPLNG.LANGUAGE_CD ) ELSE ( SELECT %Substring(MESSAGE_TEXT, 1, 30) FROM PSMSGCATLANG WHERE MESSAGE_SET_NBR = 1020 AND MESSAGE_NBR = 3036 AND LANGUAGE_CD=COMPLNG.LANGUAGE_CD ) END) END FROM PS_WCS_PG_PEER_VW2 PEER , 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_EE_DMG DMG , PS_JOB JOB1 , PS_COMPNY_TBL_LANG COMPLNG , PS_BUS_UNIT_HR_LNG BULNG , PS_DEPT_TBL_LANG DEPTLNG , PS_LOCATION_LANG LOCLNG , PS_JOBCODE_LANG JTBLLNG WHERE DMG.BUDGET_ID = PEER.BUDGET_ID AND DMG.BUDGET_START_DT = PEER.BUDGET_START_DT AND DMG.TREE_NODE_NUM = PEER.TREE_NODE_NUM AND DMG.emplid= PEER.emplid AND DMG.empl_rcd= PEER.empl_rcd AND DMG.BUDGET_ID = MGR.BUDGET_ID AND DMG.BUDGET_START_DT = MGR.BUDGET_START_DT 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 COMPLNG.COMPANY=JOB1.COMPANY AND COMPLNG.EFFDT= ( SELECT MAX(EFFDT) FROM PS_COMPNY_TBL_LANG COMP1 WHERE COMP1.company=COMPLNG.COMPANY AND COMP1.effdt<=%CurrentDateIn) 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 BULNG.BUSINESS_UNIT=JOB1.BUSINESS_UNIT AND LOCLNG.LOCATION=JOB1.LOCATION AND LOCLNG.SETID=JOB1.SETID_LOCATION AND LOCLNG.EFFDT=( SELECT MAX(EFFDT) FROM PS_LOCATION_LANG LOC1 WHERE LOC1.LOCATION=LOCLNG.LOCATION AND LOC1.effdt<=%CurrentDateIn AND LOC1.setid=LOCLNG.SETID) AND JTBLLNG.JOBCODE=JOB1.JOBCODE AND JTBLLNG.SETID=JOB1.SETID_JOBCODE AND JTBLLNG.EFFDT=( SELECT MAX(EFFDT) FROM PS_JOBCODE_LANG JTBL1 WHERE JTBL1.JOBCODE=JTBLLNG.JOBCODE AND JTBL1.EFFDT<=%CurrentDateIn AND JTBL1.setid=JTBLLNG.SETID) AND LOCLNG.LANGUAGE_CD=DEPTLNG.LANGUAGE_CD AND BULNG.LANGUAGE_CD=DEPTLNG.LANGUAGE_CD AND COMPLNG.LANGUAGE_CD=DEPTLNG.LANGUAGE_CD AND JTBLLNG.LANGUAGE_CD=DEPTLNG.LANGUAGE_CD

  • Related Language Record for WCS_PG_PEER_VW3

    • # 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 DESCR Character(30) VARCHAR2(30) NOT NULL Description
    9 DESCR1 Character(30) VARCHAR2(30) NOT NULL Descr
    10 DESCR2 Character(30) VARCHAR2(30) NOT NULL Descr2
    11 DESCR3 Character(30) VARCHAR2(30) NOT NULL Descr 3
    12 DESCR4 Character(30) VARCHAR2(30) NOT NULL Descr 4
    13 DESCR5 Character(30) VARCHAR2(30) NOT NULL This field is used in a complex query that shows many descr fields.