WCS_PEER_LVW2

(SQL View)
 Index Back

Peer Analysis Related Language


SELECT DMG.BUDGET_ID , DMG.BUDGET_START_DT , DMG.TREE_NODE_NUM , STS.TREE_NODE_NUM_END , dmg.emplid , DEPTLNG.LANGUAGE_CD , emp.name , dmg.deptid , DEPTLNG.Descr , DMG.currency_cd , JOB1.LOCATION , LOCLNG.descr , JOB1.JOBCODE , JTBLLNG.descr , COMPLNG.descr , BULNG.descr FROM PS_WCS_ECM_EE_DMG DMG , PS_WCS_ECM_NOD_STS STS , 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 , 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 = STS.BUDGET_ID AND DMG.BUDGET_START_DT = STS.BUDGET_START_DT AND DMG.TREE_NODE_NUM = STS.TREE_NODE_NUM AND dmg.WCS_ECM_EXCLUDED='N' AND DMG.WCS_ANNUAL_RT <>0 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 AND COMP.company=COMPLNG.COMPANY AND COMP.effdt =COMPLNG.effdt AND COMPLNG.LANGUAGE_CD=DEPTLNG.LANGUAGE_CD AND DEPT.DEPTID=DEPTLNG.DEPTID AND DEPT.effdt =DEPTLNG.effdt AND DEPT.SETID=DEPTLNG.SETID AND LOC.LOCATION=LOCLNG.LOCATION AND LOC.effdt =LOCLNG.effdt AND LOC.SETID=LOCLNG.SETID AND LOCLNG.LANGUAGE_CD=DEPTLNG.LANGUAGE_CD AND JTBL.JOBCODE=JTBLLNG.JOBCODE AND JTBL.effdt =JTBLLNG.effdt AND JTBL.SETID=JTBLLNG.SETID AND JTBLLNG.LANGUAGE_CD=DEPTLNG.LANGUAGE_CD

# 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 LANGUAGE_CD Character(3) VARCHAR2(3) NOT NULL Language Code
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 CURRENCY_CD Character(3) VARCHAR2(3) NOT NULL Currency Code
11 LOCATION Character(10) VARCHAR2(10) NOT NULL Location Code
12 DESCR1 Character(30) VARCHAR2(30) NOT NULL Descr
13 JOBCODE Character(6) VARCHAR2(6) NOT NULL Job Code
14 DESCR2 Character(30) VARCHAR2(30) NOT NULL Descr2
15 DESCR3 Character(30) VARCHAR2(30) NOT NULL Descr 3
16 DESCR4 Character(30) VARCHAR2(30) NOT NULL Descr 4