|
SELECT SLPF.BUDGET_ID , SLPF.BUDGET_START_DT , SLPF.TREE_NODE_NUM , SLPF.TREE_NODE_NUM_END , SLPF.emplid , SLPF.empl_rcd , CSALLNG.LANGUAGE_CD , DEPTLNG.Descr , ratel.descr , xlatl.xlatlongname , CSALLNG.DESCR20 , CASE WHEN CTL.TREE_NODE_NUM IS NULL THEN (CASE WHEN SLPF.TREE_NODE_NUM=( SELECT MIN(MTX.TREE_NODE_NUM) FROM PS_WCS_ECM_MTX_VW MTX WHERE SLPF.BUDGET_ID = MTX.BUDGET_ID AND SLPF.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=CSALLNG.LANGUAGE_CD) ELSE ( SELECT %Substring(MESSAGE_TEXT, 1, 30) FROM PSMSGCATLANG WHERE MESSAGE_SET_NBR = 1020 AND MESSAGE_NBR = 3036 AND LANGUAGE_CD=CSALLNG.LANGUAGE_CD )END) ELSE (CASE WHEN SLPF.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=CSALLNG.LANGUAGE_CD ) ELSE ( SELECT %Substring(MESSAGE_TEXT, 1, 30) FROM PSMSGCATLANG WHERE MESSAGE_SET_NBR = 1020 AND MESSAGE_NBR = 3036 AND LANGUAGE_CD=CSALLNG.LANGUAGE_CD ) END) END , JTBLLNG.DESCR FROM PS_WCS_PG_SLPF_VW1 SLPF , PS_WCS_ECM_EE_DMG DMG , 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_CYC_SAL_LNG CSALLNG , ps_wcs_ecm_cyc_cfg CFG , PS_EP_APPR APPR , PS_REVW_RATING_LNG RATEL , PSXLATITEMLANG xlatl , PS_JOB JOB1 , PS_JOBCODE_LANG JTBLLNG , PS_DEPT_TBL_LANG DEPTLNG WHERE DMG.BUDGET_ID = SLPF.BUDGET_ID AND DMG.BUDGET_START_DT = SLPF.BUDGET_START_DT AND DMG.TREE_NODE_NUM = SLPF.TREE_NODE_NUM AND DMG.emplid= SLPF.emplid AND DMG.empl_rcd= SLPF.empl_rcd AND DMG.BUDGET_ID = MGR.BUDGET_ID AND DMG.BUDGET_START_DT = MGR.BUDGET_START_DT AND cfg.budget_id= SLPF.budget_id AND SLPF.BUDGET_ID = CSALLNG.BUDGET_ID AND SLPF.BUDGET_START_DT = CSALLNG.BUDGET_START_DT AND SLPF.SEQNUM = CSALLNG.SEQNUM AND APPR.emplid= dmg.emplid AND APPR.ep_review_type= cfg.ep_review_type AND APPR.period_begin_dt=cfg.period_begin_dt AND APPR.period_end_dt= cfg.period_end_dt AND RATEL.rating_model= APPR.rating_model AND (RATEL.review_rating=DMG.REVIEW_RATING) AND RATEL.EFFDT = ( SELECT MAX(EFFDT) FROM PS_REVW_RATING_LNG F WHERE F.rating_model = RATEL.rating_model AND F.review_rating=RATEL.review_rating AND EFFDT <= %CurrentDateIn) AND CSALLNG.LANGUAGE_CD =RATEL.LANGUAGE_CD AND dmg.wcs_annual_rt<>0 AND xlatl.fieldvalue=cfg.wcs_rating_type AND xlatl.fieldname='WCS_RATING_TYPE' AND CSALLNG.LANGUAGE_CD =xlatl.LANGUAGE_CD 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 JTBLLNG.JOBCODE=DMG.JOBCODE AND JTBLLNG.SETID=JOB1.SETID_JOBCODE AND JTBLLNG.EFFDT=( SELECT MAX(JTBL1.EFFDT) FROM PS_JOBCODE_LANG JTBL1 WHERE JTBL1.JOBCODE=JTBLLNG.JOBCODE AND JTBL1.EFFDT<=%CurrentDateIn AND JTBL1.setid=JTBLLNG.SETID) AND JTBLLNG.LANGUAGE_CD=CSALLNG.LANGUAGE_CD AND DEPTLNG.DEPTID=dmg.deptid AND DEPTLNG.SETID=JOB1.SETID_DEPT AND DEPTLNG.LANGUAGE_CD=CSALLNG.LANGUAGE_CD 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)
|
BUDGET_ID