WCS_PG_PEER_VW3

(SQL View)
 Index Back

Peer Analysis View


SELECT PEER.BUDGET_ID , DMG.BUDGET_START_DT , PEER.TREE_NODE_NUM , PEER.TREE_NODE_NUM_END , PEER.emplid , PEER.empl_rcd , emp.name , dmg.deptid , DEPT.Descr , PEER.ANNUAL_RT , PEER.min_rt_annual , PEER.max_rt_annual , PEER.mid_rt_annual , dmg.currency_cd , JOB1.LOCATION , LOC.descr , JOB1.JOBCODE , JTBL.descr , COMP.descr , BU.descr , PEER.WCS_C_PCT , 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 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 PEER.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 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_EMPLEE_NAME_VW EMP ,PS_COMPANY_TBL COMP ,PS_BUS_UNIT_TBL_HR BU ,PS_DEPT_TBL DEPT ,PS_LOCATION_TBL LOC ,PS_JOBCODE_TBL JTBL WHERE DMG.BUDGET_ID = MGR.BUDGET_ID AND DMG.BUDGET_START_DT = MGR.BUDGET_START_DT AND 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 EMP.emplid=dmg.emplid 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 COMP.COMPANY=JOB1.COMPANY AND COMP.EFFDT= ( SELECT MAX(EFFDT) FROM PS_COMPANY_TBL COMP1 WHERE COMP1.company=COMP.COMPANY AND COMP1.effdt<=%CurrentDateIn AND COMP1.eff_status=COMP.EFF_STATUS) AND COMP.EFF_STATUS='A' AND DEPT.DEPTID=dmg.deptid AND DEPT.EFFDT= ( SELECT MAX(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 BU.BUSINESS_UNIT=JOB1.BUSINESS_UNIT AND LOC.LOCATION=JOB1.LOCATION AND LOC.EFFDT=( SELECT MAX(EFFDT) FROM PS_LOCATION_TBL LOC1 WHERE LOC1.LOCATION=LOC.LOCATION AND LOC1.effdt<=%CurrentDateIn AND LOC1.eff_status=LOC.EFF_STATUS AND LOC1.setid=LOC.SETID) AND LOC.EFF_STATUS='A' AND LOC.SETID=JOB1.SETID_LOCATION AND JTBL.JOBCODE=JOB1.JOBCODE AND JTBL.EFFDT=( SELECT MAX(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 JTBL.EFF_STATUS='A' AND JTBL.SETID=JOB1.SETID_JOBCODE

  • Related Language Record: WCS_PEER_LVW3

    • # 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 NAME Character(50) VARCHAR2(50) NOT NULL Name
    8 DEPTID Character(10) VARCHAR2(10) NOT NULL Department
    9 DESCR Character(30) VARCHAR2(30) NOT NULL Description
    10 ANNUAL_RT Number(19,3) DECIMAL(18,3) NOT NULL Annual Rate
    11 MIN_RT_ANNUAL Number(19,3) DECIMAL(18,3) NOT NULL Minimum Pay Rate-Annual
    12 MAX_RT_ANNUAL Number(19,3) DECIMAL(18,3) NOT NULL Maximum Pay Rate-Annual
    13 MID_RT_ANNUAL Number(19,3) DECIMAL(18,3) NOT NULL Midpoint Pay Rate-Annual
    14 CURRENCY_CD Character(3) VARCHAR2(3) NOT NULL Currency Code
    15 LOCATION Character(10) VARCHAR2(10) NOT NULL Location Code
    16 DESCR1 Character(30) VARCHAR2(30) NOT NULL Descr
    17 JOBCODE Character(6) VARCHAR2(6) NOT NULL Job Code
    18 DESCR2 Character(30) VARCHAR2(30) NOT NULL Descr2
    19 DESCR3 Character(30) VARCHAR2(30) NOT NULL Descr 3
    20 DESCR4 Character(30) VARCHAR2(30) NOT NULL Descr 4
    21 WCS_C_PCT Number(6,2) DECIMAL(5,2) NOT NULL Calculated Percent
    22 DESCR5 Character(30) VARCHAR2(30) NOT NULL This field is used in a complex query that shows many descr fields.
    23 OPRID Character(30) VARCHAR2(30) NOT NULL A user's ID (see PSOPRDEFN).