WCS_PG_ALERT_VW

(SQL View)
 Index Back

Cycle Alert View


SELECT DMG.BUDGET_ID , DMG.BUDGET_START_DT , DMG.TREE_NODE_NUM , STS.TREE_NODE_NUM_END , DMG.emplid , DMG.EMPL_RCD , STS.tree_node , STS.descr , XLAT.xlatlongname , EMP.name , DMG.deptid , %Coalesce(( SELECT DEPT.Descr FROM PS_DEPT_TBL DEPT WHERE DEPT.DEPTID=DMG.deptid AND DEPT.EFF_STATUS='A' AND DEPT.SETID=JOB1.SETID_DEPT 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)),'') , DMG.location , %Coalesce(( SELECT LOC.descr FROM PS_LOCATION_TBL LOC WHERE LOC.LOCATION=JOB1.LOCATION AND LOC.EFF_STATUS='A' AND LOC.SETID=JOB1.SETID_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)),'') , DMG.reg_region , REG.descr50 , DMG.business_unit , BU.descr , DMG.position_nbr , %Coalesce(( SELECT POS1.descr FROM PS_POSITION_DATA POS1 WHERE POS1.POSITION_NBR=DMG.POSITION_NBR AND POS1.EFFDT=( SELECT MAX(EFFDT) FROM PS_POSITION_DATA POS2 WHERE POS2.POSITION_NBR=POS1.POSITION_NBR AND POS2.EFFDT<=%CurrentDateIn)),'') , DMG.jobcode , %Coalesce(( SELECT JTBL.descr FROM PS_JOBCODE_TBL JTBL WHERE JTBL.JOBCODE=JOB1.JOBCODE AND JTBL.EFF_STATUS='A' AND JTBL.SETID=JOB1.SETID_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)),'') , SPLAN.descr20 , CASE WHEN ((%Round(%DecMult(%DecDiv(%Coalesce(SAL.WCS_P_AMT,SAL.WCS_A_AMT), DMG.wcs_annual_rt), 100), 2)) >= SAL.wcs_min_pct AND (%Round(%DecMult(%DecDiv(%Coalesce(SAL.WCS_P_AMT,SAL.WCS_A_AMT), DMG.wcs_annual_rt), 100), 2)) <= SAL.wcs_max_pct) OR SAL.wcs_max_pct=0 THEN ( SELECT %Substring(MESSAGE_TEXT, 1, 40) FROM PSMSGCATDEFN WHERE MESSAGE_SET_NBR = 1020 AND MESSAGE_NBR = 3037) ELSE ( SELECT %Substring(MESSAGE_TEXT, 1, 40) FROM PSMSGCATDEFN WHERE MESSAGE_SET_NBR = 1020 AND MESSAGE_NBR = 3038) END FROM PS_WCS_ECM_EE_DMG DMG, PS_WCS_ECM_NOD_STS STS, PS_WCS_ECM_EE_SAL SAL, PS_WCS_ECM_CYC_SAL SPLAN, PS_WCS_ECM_CYC_CFG CFG, PS_EMPLEE_NAME_VW EMP, XLATTABLE_VW XLAT, PS_REG_REGION_TBL REG, PS_BUS_UNIT_TBL_HR BU, PS_JOB JOB1 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.BUDGET_ID = SAL.BUDGET_ID AND DMG.BUDGET_START_DT = SAL.BUDGET_START_DT AND DMG.TREE_NODE_NUM = SAL.TREE_NODE_NUM AND DMG.EMPLID = SAL.EMPLID AND DMG.EMPL_RCD = SAL.EMPL_RCD AND SPLAN.budget_id= SAL.budget_id AND SPLAN.budget_start_dt=SAL.budget_start_dt AND SPLAN.SEQNUM=SAL.SEQNUM AND CFG.budget_id= DMG.budget_id AND JOB1.JOBCODE=DMG.JOBCODE AND JOB1.EFFDT=DMG.JOB_EFFDT AND JOB1.EFFSEQ=DMG.JOB_EFFSEQ AND JOB1.EMPLID=DMG.EMPLID AND XLAT.FIELDNAME='WCS_NODE_STATUS' AND XLAT.FIELDVALUE=STS.WCS_NODE_STATUS AND EMP.emplid=DMG.emplid AND REG.REG_REGION=DMG.REG_REGION AND BU.BUSINESS_UNIT=JOB1.BUSINESS_UNIT AND SAL.wcs_ecm_excluded='N' AND DMG.wcs_annual_rt<>0

  • Related Language Record: WCS_ALERT_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 TREE_NODE Character(20) VARCHAR2(20) NOT NULL Node name
    8 DESCR Character(30) VARCHAR2(30) NOT NULL Description
    9 DESCR1 Character(30) VARCHAR2(30) NOT NULL Descr
    10 NAME Character(50) VARCHAR2(50) NOT NULL Name
    11 DEPTID Character(10) VARCHAR2(10) NOT NULL Department
    12 DESCR2 Character(30) VARCHAR2(30) NOT NULL Descr2
    13 LOCATION Character(10) VARCHAR2(10) NOT NULL Location Code
    14 DESCR3 Character(30) VARCHAR2(30) NOT NULL Descr 3
    15 REG_REGION Character(5) VARCHAR2(5) NOT NULL Regulatory Region
    16 DESCR50 Character(50) VARCHAR2(50) NOT NULL Description of length 50
    17 BUSINESS_UNIT Character(5) VARCHAR2(5) NOT NULL Business Unit
    18 DESCR4 Character(30) VARCHAR2(30) NOT NULL Descr 4
    19 POSITION_NBR Character(8) VARCHAR2(8) NOT NULL Position Number
    20 DESCR5 Character(30) VARCHAR2(30) NOT NULL This field is used in a complex query that shows many descr fields.
    21 JOBCODE Character(6) VARCHAR2(6) NOT NULL Job Code
    22 DESCR6 Character(30) VARCHAR2(30) NOT NULL This field is used in a complex query that shows many descr fields.
    23 DESCR20 Character(20) VARCHAR2(20) NOT NULL Description
    24 DESCR40 Character(40) VARCHAR2(40) NOT NULL Description