WCS_ALERT_LVW

(SQL View)
 Index Back

Cycle Alert language View


SELECT DMG.BUDGET_ID , DMG.BUDGET_START_DT , DMG.TREE_NODE_NUM , STS.TREE_NODE_NUM_END , DMG.emplid , DMG.EMPL_RCD , SPLANLNG.LANGUAGE_CD , STSLNG.descr , XLATL.XLATLongname , %Coalesce(( SELECT DEPTLNG.Descr FROM PS_DEPT_TBL_LANG DEPTLNG WHERE DEPTLNG.DEPTID=DMG.deptid AND SPLANLNG.LANGUAGE_CD =DEPTLNG.LANGUAGE_CD 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)),'') , %Coalesce(( SELECT LOCLNG.descr FROM PS_LOCATION_LANG LOCLNG WHERE LOCLNG.LOCATION=JOB1.LOCATION AND LOCLNG.SETID=JOB1.SETID_LOCATION AND SPLANLNG.LANGUAGE_CD =LOCLNG.LANGUAGE_CD 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)),'') , REGL.descr50 , BULNG.descr , %Coalesce(( SELECT POSLNG.descr FROM PS_POSN_DATA_LANG POSLNG WHERE POSLNG.POSITION_NBR=DMG.POSITION_NBR AND SPLANLNG.LANGUAGE_CD=POSLNG.LANGUAGE_CD AND POSLNG.EFFDT=( SELECT MAX(EFFDT) FROM PS_POSN_DATA_LANG POS2 WHERE POS2.POSITION_NBR=POSLNG.POSITION_NBR AND POS2.EFFDT<=%CurrentDateIn)),'') , %Coalesce(( SELECT JTBLLNG.descr FROM PS_JOBCODE_LANG JTBLLNG WHERE JTBLLNG.JOBCODE=JOB1.JOBCODE AND JTBLLNG.SETID=JOB1.SETID_JOBCODE AND SPLANLNG.LANGUAGE_CD =JTBLLNG.LANGUAGE_CD 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)),'') , SPLANLNG.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 PSMSGCATLANG WHERE MESSAGE_SET_NBR = 1020 AND MESSAGE_NBR = 3037 AND LANGUAGE_CD=SPLANLNG.LANGUAGE_CD) ELSE ( SELECT %Substring(MESSAGE_TEXT, 1, 40) FROM PSMSGCATLANG WHERE MESSAGE_SET_NBR = 1020 AND MESSAGE_NBR = 3038 AND LANGUAGE_CD=SPLANLNG.LANGUAGE_CD) END FROM PS_WCS_ECM_EE_DMG DMG, PS_WCS_ECM_NOD_STS STS , PS_WCS_ECM_EE_SAL SAL ,PS_JOB JOB1 , PS_BUS_UNIT_HR_LNG BULNG, PS_WCS_CYC_SAL_LNG SPLANLNG , PSXLATITEMLANG XLATL , PS_WCS_NOD_STS_LNG STSLNG , PS_REG_REGION_LANG REGL 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 STSLNG.BUDGET_ID = STS.BUDGET_ID AND STSLNG.BUDGET_START_DT = STS.BUDGET_START_DT AND STSLNG.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 JOB1.JOBCODE=DMG.JOBCODE AND JOB1.EFFDT=DMG.JOB_EFFDT AND JOB1.EFFSEQ=DMG.JOB_EFFSEQ AND JOB1.EMPLID=DMG.EMPLID AND SPLANLNG.budget_id= SAL.budget_id AND SPLANLNG.budget_start_dt=SAL.budget_start_dt AND SPLANLNG.SEQNUM=SAL.SEQNUM AND XLATL.FIELDNAME='WCS_NODE_STATUS' AND XLATL.FIELDVALUE=STS.WCS_NODE_STATUS AND DMG.REG_REGION=REGL.REG_REGION AND BULNG.BUSINESS_UNIT=JOB1.BUSINESS_UNIT AND SPLANLNG.LANGUAGE_CD =STSLNG.LANGUAGE_CD AND SPLANLNG.LANGUAGE_CD =XLATL.LANGUAGE_CD AND SPLANLNG.LANGUAGE_CD =REGL.LANGUAGE_CD AND SAL.wcs_ecm_excluded='N' AND DMG.wcs_annual_rt<>0

  • Related Language Record for WCS_PG_ALERT_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 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 DESCR50 Character(50) VARCHAR2(50) NOT NULL Description of length 50
    13 DESCR4 Character(30) VARCHAR2(30) NOT NULL Descr 4
    14 DESCR5 Character(30) VARCHAR2(30) NOT NULL This field is used in a complex query that shows many descr fields.
    15 DESCR6 Character(30) VARCHAR2(30) NOT NULL This field is used in a complex query that shows many descr fields.
    16 DESCR20 Character(20) VARCHAR2(20) NOT NULL Description
    17 DESCR40 Character(40) VARCHAR2(40) NOT NULL Description