HR_TM_EP_VW

(SQL View)
 Index Back

My Team - Performance Status

Used in Performance tab pivot grid in My Team. This view is for My Team analytics.

SELECT DFLT.OPRID ,DOC.EP_APPRAISAL_ID ,DOC.EMPLID ,EMP.NAME_DISPLAY ,DOC.EMPL_RCD ,DOC.EP_MANAGER_ID ,NAM.NAME_DISPLAY ,DOC.EP_REVIEW_TYPE ,TYP.EP_REVW_DESCR ,DOC.EP_REVIEW_STATUS ,XLAT.XLATLONGNAME ,DOC.PERIOD_BEGIN_DT ,DOC.PERIOD_END_DT ,DIR.SUPERVISOR_ID ,DFLT.ACCESS_TYPE ,DIR.SUPERVISOR_FLAG ,DIR.DRILL_DOWN_FLAG ,DIR.JOB_EFFDT ,DIR.EMPL_STATUS ,DIR.HR_DR_LEVEL ,JOB.SETID_JOBCODE ,JOB.JOBCODE ,JOB.SETID_DEPT ,JOB.DEPTID ,DEP.DESCR ,JOB.SETID_LOCATION ,JOB.LOCATION ,LOC.DESCR ,DOC.EP_TP_SUB_STATUS ,DOC.EP_APPROVAL_STATUS ,XLAT1.XLATSHORTNAME ,TYP.EP_DOC_USAGE_IND ,%Coalesce (CASE WHEN DOC.EP_REVIEW_STATUS ='PA' THEN XLAT.XLATLONGNAME ||' '||XLAT1.XLATSHORTNAME END, CASE WHEN DOC.EP_REVIEW_STATUS ='TP' THEN ( SELECT %Sql(HR_LONG_TO_CHAR254, HR_SSTEXT_TEXT) FROM PS_HR_SSTEXT_TEXT TEX WHERE TEX.OBJECTOWNERID='HEP' AND %EffdtCheck(HR_SSTEXT_TEXT TEX1,TEX,%CurrentDateIn) AND ((TEX.HR_SSTEXT_SUB_ID = ' ' AND TYP.EP_DOC_USAGE_IND='P') OR (TEX.HR_SSTEXT_SUB_ID = 'D' AND TYP.EP_DOC_USAGE_IND='D')) AND TEX.TEXT_ID = 'TP_DOC_SUB_STATUS' AND TEX.HR_SSTEXT_KEY1=' ' AND TEX.HR_SSTEXT_KEY2=' ' AND TEX.HR_SSTEXT_KEY3=' ' AND TEX.HR_SSTEXT_KEY4=' ' AND TEX.HR_SSTEXT_KEY5 = DOC.EP_TP_SUB_STATUS AND TEX.HR_SSTEXT_KEY6 =' ') END, CASE WHEN DOC.EP_REVIEW_STATUS <>'PA' AND DOC.EP_REVIEW_STATUS <>'TP' THEN XLAT.XLATLONGNAME END) ,CASE WHEN (DOC.EP_REVIEW_STATUS ='AK' OR DOC.EP_REVIEW_STATUS ='AR' OR DOC.EP_REVIEW_STATUS ='PA' OR DOC.EP_REVIEW_STATUS ='RH') THEN APROLE.EP_DUE_DT WHEN (DOC.EP_REVIEW_STATUS ='CA' OR DOC.EP_REVIEW_STATUS ='CO') THEN NULL WHEN DOC.EP_REVIEW_STATUS ='EC' THEN DOC.EP_EST_DUE_DATE WHEN DOC.EP_REVIEW_STATUS ='TP' THEN DOC.EP_TP_CURR_DUE_DT WHEN APROLE.EP_DUE_DT IS NOT NULL THEN APROLE.EP_DUE_DT ELSE ( SELECT PART.EP_DUE_DT FROM PS_EP_APPR_PARTIC PART WHERE PART.EP_APPRAISAL_ID = DOC.EP_APPRAISAL_ID AND PART.EP_ROLE = 'M') END FROM PS_EP_APPR DOC LEFT OUTER JOIN PS_EP_APPR_ROLE APROLE ON DOC.EP_APPRAISAL_ID = APROLE.EP_APPRAISAL_ID AND DOC.EP_MANAGER_ID = APROLE.EP_REVIEWER_ID AND APROLE.EP_ROLE = 'M' ,PS_HR_DIRECT_REP_2 DIR ,PS_JOB JOB,PS_EP_REVW_TYP_TBL TYP,PS_PERSON_NAME NAM,PS_PERSON_NAME EMP,PSXLATITEM XLAT, ( SELECT OPRID , EP_REVIEW_TYPE , FROM_DATE , TO_DATE , EP_MANAGER_ID , 0 AS EMPL_RCD , CFG.HR_DR_EX_CWR , CFG.HR_DR_ALLOW_IND , CFG.ACCESS_TYPE FROM PS_EP_PG_USER_DFLT , PS_HR_DR_UI_CFG CFG WHERE CFG.PNLGRPNAME = 'HR_DR_TEAM_FLU' UNION ALL SELECT OPR.OPRID , DFLT.EP_REVIEW_TYPE , DFLT.FROM_DATE , DFLT.TO_DATE , OPR.EMPLID , 0 AS EMPL_RCD , CFG.HR_DR_EX_CWR , CFG.HR_DR_ALLOW_IND , CFG.ACCESS_TYPE FROM PS_EP_DEFAULT DFLT ,PSOPRDEFN OPR , PS_HR_DR_PR_JOB_VW JOB , PS_HR_DR_UI_CFG CFG WHERE OPR.EMPLID=JOB.EMPLID AND CFG.PNLGRPNAME='HR_DR_TEAM_FLU' AND NOT EXISTS( SELECT 'X' FROM PS_EP_PG_USER_DFLT DFL WHERE DFL.OPRID=OPR.OPRID) AND DFLT.EP_TRANSACTION_NM='P') DFLT,PS_LOCATION_TBL LOC,PS_DEPT_TBL DEP,PSXLATITEM XLAT1 WHERE DOC.EMPLID=DIR.EMPLID AND DIR.EMPL_RCD=DOC.EMPL_RCD AND JOB.EMPLID=DOC.EMPLID AND JOB.EMPL_RCD=DOC.EMPL_RCD AND JOB.EFFDT=DIR.JOB_EFFDT AND JOB.EFFSEQ =( SELECT MAX(C1.EFFSEQ) FROM PS_JOB C1 WHERE C1.EMPLID=JOB.EMPLID AND C1.EMPL_RCD=JOB.EMPL_RCD AND C1.EFFDT=JOB.EFFDT) AND DOC.EP_REVIEW_STATUS <> 'CA' AND TYP.EP_REVIEW_TYPE = DOC.EP_REVIEW_TYPE AND %EffdtCheck(EP_REVW_TYP_TBL TYP1,TYP,%CurrentDateIn) AND DOC.EP_MANAGER_ID=NAM.EMPLID AND DOC.EMPLID=EMP.EMPLID AND DOC.EP_REVIEW_STATUS=XLAT.FIELDVALUE AND XLAT.FIELDNAME='EP_REVIEW_STATUS' AND %EffdtCheck(PSXLATITEM XLATA,XLAT,%CurrentDateIn) AND DFLT.EP_REVIEW_TYPE=DOC.EP_REVIEW_TYPE AND DOC.PERIOD_BEGIN_DT BETWEEN DFLT.FROM_DATE AND DFLT.TO_DATE AND DOC.PERIOD_END_DT BETWEEN DFLT.FROM_DATE AND DFLT.TO_DATE AND DFLT.EP_MANAGER_ID=DIR.SUPERVISOR_ID AND JOB.SETID_LOCATION = LOC.SETID AND JOB.LOCATION=LOC.LOCATION AND %EffdtCheck(LOCATION_TBL LOC1,LOC,%CurrentDateIn) AND JOB.SETID_DEPT=DEP.SETID AND JOB.DEPTID=DEP.DEPTID AND %EffdtCheck(DEPT_TBL DEP1,DEP,%CurrentDateIn) AND DOC.EP_APPROVAL_STATUS=XLAT1.FIELDVALUE AND XLAT1.FIELDNAME='EP_APPROVAL_STATUS' AND %EffdtCheck(PSXLATITEM XLAT1A,XLAT1,%CurrentDateIn) AND ((DFLT.HR_DR_EX_CWR='Y' AND JOB.PER_ORG IN ('EMP')) OR (DFLT.HR_DR_EX_CWR='N' AND JOB.PER_ORG IN ('EMP','CWR'))) AND ((DFLT.HR_DR_ALLOW_IND='Y' AND DIR.HR_DR_LEVEL >=1) OR (DFLT.HR_DR_ALLOW_IND='N' AND DIR.HR_DR_LEVEL = 1)) AND DIR.DRILL_DOWN_FLAG ='Y' AND 2= DFLT.ACCESS_TYPE UNION ALL SELECT DFLT.OPRID ,DOC.EP_APPRAISAL_ID ,DOC.EMPLID ,EMP.NAME_DISPLAY ,DOC.EMPL_RCD ,DOC.EP_MANAGER_ID ,NAM.NAME_DISPLAY ,DOC.EP_REVIEW_TYPE ,TYP.EP_REVW_DESCR ,DOC.EP_REVIEW_STATUS ,XLAT.XLATLONGNAME ,DOC.PERIOD_BEGIN_DT ,DOC.PERIOD_END_DT ,DIR.SUPERVISOR_ID ,DFLT.ACCESS_TYPE ,DIR.SUPERVISOR_FLAG ,DIR.DRILL_DOWN_FLAG ,DIR.JOB_EFFDT ,DIR.EMPL_STATUS ,DIR.HR_DR_LEVEL ,JOB.SETID_JOBCODE ,JOB.JOBCODE ,JOB.SETID_DEPT ,JOB.DEPTID ,DEP.DESCR ,JOB.SETID_LOCATION ,JOB.LOCATION ,LOC.DESCR ,DOC.EP_TP_SUB_STATUS ,DOC.EP_APPROVAL_STATUS ,XLAT1.XLATSHORTNAME ,TYP.EP_DOC_USAGE_IND ,%Coalesce (CASE WHEN DOC.EP_REVIEW_STATUS ='PA' THEN XLAT.XLATLONGNAME ||' '||XLAT1.XLATSHORTNAME END, CASE WHEN DOC.EP_REVIEW_STATUS ='TP' THEN ( SELECT %Sql(HR_LONG_TO_CHAR254, HR_SSTEXT_TEXT) FROM PS_HR_SSTEXT_TEXT TEX WHERE TEX.OBJECTOWNERID='HEP' AND %EffdtCheck(HR_SSTEXT_TEXT TEX1,TEX,%CurrentDateIn) AND ((TEX.HR_SSTEXT_SUB_ID = ' ' AND TYP.EP_DOC_USAGE_IND='P') OR (TEX.HR_SSTEXT_SUB_ID = 'D' AND TYP.EP_DOC_USAGE_IND='D')) AND TEX.TEXT_ID = 'TP_DOC_SUB_STATUS' AND TEX.HR_SSTEXT_KEY1=' ' AND TEX.HR_SSTEXT_KEY2=' ' AND TEX.HR_SSTEXT_KEY3=' ' AND TEX.HR_SSTEXT_KEY4=' ' AND TEX.HR_SSTEXT_KEY5 = DOC.EP_TP_SUB_STATUS AND TEX.HR_SSTEXT_KEY6 =' ') END, CASE WHEN DOC.EP_REVIEW_STATUS <>'PA' AND DOC.EP_REVIEW_STATUS <>'TP' THEN XLAT.XLATLONGNAME END) ,CASE WHEN (DOC.EP_REVIEW_STATUS ='AK' OR DOC.EP_REVIEW_STATUS ='AR' OR DOC.EP_REVIEW_STATUS ='PA' OR DOC.EP_REVIEW_STATUS ='RH') THEN APROLE.EP_DUE_DT WHEN (DOC.EP_REVIEW_STATUS ='CA' OR DOC.EP_REVIEW_STATUS ='CO') THEN NULL WHEN DOC.EP_REVIEW_STATUS ='EC' THEN DOC.EP_EST_DUE_DATE WHEN DOC.EP_REVIEW_STATUS ='TP' THEN DOC.EP_TP_CURR_DUE_DT WHEN APROLE.EP_DUE_DT IS NOT NULL THEN APROLE.EP_DUE_DT ELSE ( SELECT PART.EP_DUE_DT FROM PS_EP_APPR_PARTIC PART WHERE PART.EP_APPRAISAL_ID = DOC.EP_APPRAISAL_ID AND PART.EP_ROLE = 'M') END FROM PS_EP_APPR DOC LEFT OUTER JOIN PS_EP_APPR_ROLE APROLE ON DOC.EP_APPRAISAL_ID = APROLE.EP_APPRAISAL_ID AND DOC.EP_MANAGER_ID = APROLE.EP_REVIEWER_ID AND APROLE.EP_ROLE = 'M' ,PS_HR_DIRECT_REP_3 DIR ,PS_JOB JOB,PS_EP_REVW_TYP_TBL TYP,PS_PERSON_NAME NAM,PS_PERSON_NAME EMP,PSXLATITEM XLAT, ( SELECT OPRID , EP_REVIEW_TYPE , FROM_DATE , TO_DATE , EP_MANAGER_ID , 0 AS EMPL_RCD , CFG.HR_DR_EX_CWR , CFG.HR_DR_ALLOW_IND , CFG.ACCESS_TYPE FROM PS_EP_PG_USER_DFLT , PS_HR_DR_UI_CFG CFG WHERE CFG.PNLGRPNAME='HR_DR_TEAM_FLU' UNION ALL SELECT OPR.OPRID , DFLT.EP_REVIEW_TYPE , DFLT.FROM_DATE , DFLT.TO_DATE , OPR.EMPLID , 0 AS EMPL_RCD , CFG.HR_DR_EX_CWR , CFG.HR_DR_ALLOW_IND , CFG.ACCESS_TYPE FROM PS_EP_DEFAULT DFLT ,PSOPRDEFN OPR , PS_HR_DR_PR_JOB_VW JOB , PS_HR_DR_UI_CFG CFG WHERE OPR.EMPLID=JOB.EMPLID AND CFG.PNLGRPNAME='HR_DR_TEAM_FLU' AND NOT EXISTS( SELECT 'X' FROM PS_EP_PG_USER_DFLT DFL WHERE DFL.OPRID=OPR.OPRID) AND DFLT.EP_TRANSACTION_NM='P') DFLT,PS_LOCATION_TBL LOC,PS_DEPT_TBL DEP,PSXLATITEM XLAT1 WHERE DOC.EMPLID=DIR.EMPLID AND DIR.EMPL_RCD=DOC.EMPL_RCD AND JOB.EMPLID=DOC.EMPLID AND JOB.EMPL_RCD=DOC.EMPL_RCD AND JOB.EFFDT=DIR.JOB_EFFDT AND JOB.EFFSEQ =( SELECT MAX(C1.EFFSEQ) FROM PS_JOB C1 WHERE C1.EMPLID=JOB.EMPLID AND C1.EMPL_RCD=JOB.EMPL_RCD AND C1.EFFDT=JOB.EFFDT) AND DOC.EP_REVIEW_STATUS <> 'CA' AND TYP.EP_REVIEW_TYPE = DOC.EP_REVIEW_TYPE AND %EffdtCheck(EP_REVW_TYP_TBL TYP1,TYP,%CurrentDateIn) AND DOC.EP_MANAGER_ID=NAM.EMPLID AND DOC.EMPLID=EMP.EMPLID AND DOC.EP_REVIEW_STATUS=XLAT.FIELDVALUE AND XLAT.FIELDNAME='EP_REVIEW_STATUS' AND %EffdtCheck(PSXLATITEM XLATA,XLAT,%CurrentDateIn) AND DFLT.EP_REVIEW_TYPE=DOC.EP_REVIEW_TYPE AND DOC.PERIOD_BEGIN_DT BETWEEN DFLT.FROM_DATE AND DFLT.TO_DATE AND DOC.PERIOD_END_DT BETWEEN DFLT.FROM_DATE AND DFLT.TO_DATE AND DFLT.EP_MANAGER_ID=DIR.SUPERVISOR_ID AND JOB.SETID_LOCATION = LOC.SETID AND JOB.LOCATION=LOC.LOCATION AND %EffdtCheck(LOCATION_TBL LOC1,LOC,%CurrentDateIn) AND JOB.SETID_DEPT=DEP.SETID AND JOB.DEPTID=DEP.DEPTID AND %EffdtCheck(DEPT_TBL DEP1,DEP,%CurrentDateIn) AND DOC.EP_APPROVAL_STATUS=XLAT1.FIELDVALUE AND XLAT1.FIELDNAME='EP_APPROVAL_STATUS' AND %EffdtCheck(PSXLATITEM XLAT1A,XLAT1,%CurrentDateIn) AND ((DFLT.HR_DR_EX_CWR='Y' AND JOB.PER_ORG IN ('EMP')) OR (DFLT.HR_DR_EX_CWR='N' AND JOB.PER_ORG IN ('EMP','CWR'))) AND ((DFLT.HR_DR_ALLOW_IND='Y' AND DIR.HR_DR_LEVEL >=1) OR (DFLT.HR_DR_ALLOW_IND='N' AND DIR.HR_DR_LEVEL = 1)) AND DIR.DRILL_DOWN_FLAG ='Y' AND 3= DFLT.ACCESS_TYPE UNION ALL SELECT DFLT.OPRID ,DOC.EP_APPRAISAL_ID ,DOC.EMPLID ,EMP.NAME_DISPLAY ,DOC.EMPL_RCD ,DOC.EP_MANAGER_ID ,NAM.NAME_DISPLAY ,DOC.EP_REVIEW_TYPE ,TYP.EP_REVW_DESCR ,DOC.EP_REVIEW_STATUS ,XLAT.XLATLONGNAME ,DOC.PERIOD_BEGIN_DT ,DOC.PERIOD_END_DT ,DIR.SUPERVISOR_ID ,DFLT.ACCESS_TYPE ,DIR.SUPERVISOR_FLAG ,DIR.DRILL_DOWN_FLAG ,DIR.JOB_EFFDT ,DIR.EMPL_STATUS ,DIR.HR_DR_LEVEL ,JOB.SETID_JOBCODE ,JOB.JOBCODE ,JOB.SETID_DEPT ,JOB.DEPTID ,DEP.DESCR ,JOB.SETID_LOCATION ,JOB.LOCATION ,LOC.DESCR ,DOC.EP_TP_SUB_STATUS ,DOC.EP_APPROVAL_STATUS ,XLAT1.XLATSHORTNAME ,TYP.EP_DOC_USAGE_IND ,%Coalesce (CASE WHEN DOC.EP_REVIEW_STATUS ='PA' THEN XLAT.XLATLONGNAME ||' '||XLAT1.XLATSHORTNAME END, CASE WHEN DOC.EP_REVIEW_STATUS ='TP' THEN ( SELECT %Sql(HR_LONG_TO_CHAR254, HR_SSTEXT_TEXT) FROM PS_HR_SSTEXT_TEXT TEX WHERE TEX.OBJECTOWNERID='HEP' AND %EffdtCheck(HR_SSTEXT_TEXT TEX1,TEX,%CurrentDateIn) AND ((TEX.HR_SSTEXT_SUB_ID = ' ' AND TYP.EP_DOC_USAGE_IND='P') OR (TEX.HR_SSTEXT_SUB_ID = 'D' AND TYP.EP_DOC_USAGE_IND='D')) AND TEX.TEXT_ID = 'TP_DOC_SUB_STATUS' AND TEX.HR_SSTEXT_KEY1=' ' AND TEX.HR_SSTEXT_KEY2=' ' AND TEX.HR_SSTEXT_KEY3=' ' AND TEX.HR_SSTEXT_KEY4=' ' AND TEX.HR_SSTEXT_KEY5 = DOC.EP_TP_SUB_STATUS AND TEX.HR_SSTEXT_KEY6 =' ') END, CASE WHEN DOC.EP_REVIEW_STATUS <>'PA' AND DOC.EP_REVIEW_STATUS <>'TP' THEN XLAT.XLATLONGNAME END) ,CASE WHEN (DOC.EP_REVIEW_STATUS ='AK' OR DOC.EP_REVIEW_STATUS ='AR' OR DOC.EP_REVIEW_STATUS ='PA' OR DOC.EP_REVIEW_STATUS ='RH') THEN APROLE.EP_DUE_DT WHEN (DOC.EP_REVIEW_STATUS ='CA' OR DOC.EP_REVIEW_STATUS ='CO') THEN NULL WHEN DOC.EP_REVIEW_STATUS ='EC' THEN DOC.EP_EST_DUE_DATE WHEN DOC.EP_REVIEW_STATUS ='TP' THEN DOC.EP_TP_CURR_DUE_DT WHEN APROLE.EP_DUE_DT IS NOT NULL THEN APROLE.EP_DUE_DT ELSE ( SELECT PART.EP_DUE_DT FROM PS_EP_APPR_PARTIC PART WHERE PART.EP_APPRAISAL_ID = DOC.EP_APPRAISAL_ID AND PART.EP_ROLE = 'M') END FROM PS_EP_APPR DOC LEFT OUTER JOIN PS_EP_APPR_ROLE APROLE ON DOC.EP_APPRAISAL_ID = APROLE.EP_APPRAISAL_ID AND DOC.EP_MANAGER_ID = APROLE.EP_REVIEWER_ID AND APROLE.EP_ROLE = 'M' ,PS_HR_DIRECT_REP_4 DIR ,PS_JOB JOB,PS_EP_REVW_TYP_TBL TYP,PS_PERSON_NAME NAM,PS_PERSON_NAME EMP,PSXLATITEM XLAT, ( SELECT OPRID , EP_REVIEW_TYPE , FROM_DATE , TO_DATE , EP_MANAGER_ID , SUPERVISOR_ERN AS EMPL_RCD , CFG.HR_DR_EX_CWR , CFG.HR_DR_ALLOW_IND , CFG.ACCESS_TYPE FROM PS_EP_PG_USER_DFLT , PS_HR_DR_UI_CFG CFG WHERE CFG.PNLGRPNAME='HR_DR_TEAM_FLU' UNION ALL SELECT OPR.OPRID , DFLT.EP_REVIEW_TYPE , DFLT.FROM_DATE , DFLT.TO_DATE , OPR.EMPLID , JOB.EMPL_RCD , CFG.HR_DR_EX_CWR , CFG.HR_DR_ALLOW_IND , CFG.ACCESS_TYPE FROM PS_EP_DEFAULT DFLT ,PSOPRDEFN OPR , PS_HR_DR_PR_JOB_VW JOB , PS_HR_DR_UI_CFG CFG WHERE OPR.EMPLID=JOB.EMPLID AND CFG.PNLGRPNAME='HR_DR_TEAM_FLU' AND NOT EXISTS( SELECT 'X' FROM PS_EP_PG_USER_DFLT DFL WHERE DFL.OPRID=OPR.OPRID) AND DFLT.EP_TRANSACTION_NM='P') DFLT,PS_LOCATION_TBL LOC,PS_DEPT_TBL DEP,PSXLATITEM XLAT1 WHERE DOC.EMPLID=DIR.EMPLID AND DIR.EMPL_RCD=DOC.EMPL_RCD AND JOB.EMPLID=DOC.EMPLID AND JOB.EMPL_RCD=DOC.EMPL_RCD AND JOB.EFFDT=DIR.JOB_EFFDT AND JOB.EFFSEQ =( SELECT MAX(C1.EFFSEQ) FROM PS_JOB C1 WHERE C1.EMPLID=JOB.EMPLID AND C1.EMPL_RCD=JOB.EMPL_RCD AND C1.EFFDT=JOB.EFFDT) AND DOC.EP_REVIEW_STATUS <> 'CA' AND TYP.EP_REVIEW_TYPE = DOC.EP_REVIEW_TYPE AND %EffdtCheck(EP_REVW_TYP_TBL TYP1,TYP,%CurrentDateIn) AND DOC.EP_MANAGER_ID=NAM.EMPLID AND DOC.EMPLID=EMP.EMPLID AND DOC.EP_REVIEW_STATUS=XLAT.FIELDVALUE AND XLAT.FIELDNAME='EP_REVIEW_STATUS' AND %EffdtCheck(PSXLATITEM XLATA,XLAT,%CurrentDateIn) AND DFLT.EP_REVIEW_TYPE=DOC.EP_REVIEW_TYPE AND DOC.PERIOD_BEGIN_DT BETWEEN DFLT.FROM_DATE AND DFLT.TO_DATE AND DOC.PERIOD_END_DT BETWEEN DFLT.FROM_DATE AND DFLT.TO_DATE AND DFLT.EP_MANAGER_ID=DIR.SUPERVISOR_ID AND DFLT.EMPL_RCD=DIR.SUPERVIS_EMPL_RCD AND JOB.SETID_LOCATION = LOC.SETID AND JOB.LOCATION=LOC.LOCATION AND %EffdtCheck(LOCATION_TBL LOC1,LOC,%CurrentDateIn) AND JOB.SETID_DEPT=DEP.SETID AND JOB.DEPTID=DEP.DEPTID AND %EffdtCheck(DEPT_TBL DEP1,DEP,%CurrentDateIn) AND DOC.EP_APPROVAL_STATUS=XLAT1.FIELDVALUE AND XLAT1.FIELDNAME='EP_APPROVAL_STATUS' AND %EffdtCheck(PSXLATITEM XLAT1A,XLAT1,%CurrentDateIn) AND ((DFLT.HR_DR_EX_CWR='Y' AND JOB.PER_ORG IN ('EMP')) OR (DFLT.HR_DR_EX_CWR='N' AND JOB.PER_ORG IN ('EMP','CWR'))) AND ((DFLT.HR_DR_ALLOW_IND='Y' AND DIR.HR_DR_LEVEL >=1) OR (DFLT.HR_DR_ALLOW_IND='N' AND DIR.HR_DR_LEVEL = 1)) AND DIR.DRILL_DOWN_FLAG ='Y' AND 4= DFLT.ACCESS_TYPE UNION ALL SELECT DFLT.OPRID ,DOC.EP_APPRAISAL_ID ,DOC.EMPLID ,EMP.NAME_DISPLAY ,DOC.EMPL_RCD ,DOC.EP_MANAGER_ID ,NAM.NAME_DISPLAY ,DOC.EP_REVIEW_TYPE ,TYP.EP_REVW_DESCR ,DOC.EP_REVIEW_STATUS ,XLAT.XLATLONGNAME ,DOC.PERIOD_BEGIN_DT ,DOC.PERIOD_END_DT ,DIR.SUPERVISOR_ID ,DFLT.ACCESS_TYPE ,DIR.SUPERVISOR_FLAG ,DIR.DRILL_DOWN_FLAG ,DIR.JOB_EFFDT ,DIR.EMPL_STATUS ,DIR.HR_DR_LEVEL ,JOB.SETID_JOBCODE ,JOB.JOBCODE ,JOB.SETID_DEPT ,JOB.DEPTID ,DEP.DESCR ,JOB.SETID_LOCATION ,JOB.LOCATION ,LOC.DESCR ,DOC.EP_TP_SUB_STATUS ,DOC.EP_APPROVAL_STATUS ,XLAT1.XLATSHORTNAME ,TYP.EP_DOC_USAGE_IND ,%Coalesce (CASE WHEN DOC.EP_REVIEW_STATUS ='PA' THEN XLAT.XLATLONGNAME ||' '||XLAT1.XLATSHORTNAME END, CASE WHEN DOC.EP_REVIEW_STATUS ='TP' THEN ( SELECT %Sql(HR_LONG_TO_CHAR254, HR_SSTEXT_TEXT) FROM PS_HR_SSTEXT_TEXT TEX WHERE TEX.OBJECTOWNERID='HEP' AND %EffdtCheck(HR_SSTEXT_TEXT TEX1,TEX,%CurrentDateIn) AND ((TEX.HR_SSTEXT_SUB_ID = ' ' AND TYP.EP_DOC_USAGE_IND='P') OR (TEX.HR_SSTEXT_SUB_ID = 'D' AND TYP.EP_DOC_USAGE_IND='D')) AND TEX.TEXT_ID = 'TP_DOC_SUB_STATUS' AND TEX.HR_SSTEXT_KEY1=' ' AND TEX.HR_SSTEXT_KEY2=' ' AND TEX.HR_SSTEXT_KEY3=' ' AND TEX.HR_SSTEXT_KEY4=' ' AND TEX.HR_SSTEXT_KEY5 = DOC.EP_TP_SUB_STATUS AND TEX.HR_SSTEXT_KEY6 =' ') END, CASE WHEN DOC.EP_REVIEW_STATUS <>'PA' AND DOC.EP_REVIEW_STATUS <>'TP' THEN XLAT.XLATLONGNAME END) ,CASE WHEN (DOC.EP_REVIEW_STATUS ='AK' OR DOC.EP_REVIEW_STATUS ='AR' OR DOC.EP_REVIEW_STATUS ='PA' OR DOC.EP_REVIEW_STATUS ='RH') THEN APROLE.EP_DUE_DT WHEN (DOC.EP_REVIEW_STATUS ='CA' OR DOC.EP_REVIEW_STATUS ='CO') THEN NULL WHEN DOC.EP_REVIEW_STATUS ='EC' THEN DOC.EP_EST_DUE_DATE WHEN DOC.EP_REVIEW_STATUS ='TP' THEN DOC.EP_TP_CURR_DUE_DT WHEN APROLE.EP_DUE_DT IS NOT NULL THEN APROLE.EP_DUE_DT ELSE ( SELECT PART.EP_DUE_DT FROM PS_EP_APPR_PARTIC PART WHERE PART.EP_APPRAISAL_ID = DOC.EP_APPRAISAL_ID AND PART.EP_ROLE = 'M') END FROM PS_EP_APPR DOC LEFT OUTER JOIN PS_EP_APPR_ROLE APROLE ON DOC.EP_APPRAISAL_ID = APROLE.EP_APPRAISAL_ID AND DOC.EP_MANAGER_ID = APROLE.EP_REVIEWER_ID AND APROLE.EP_ROLE = 'M' ,PS_HR_DIRECT_REP_5 DIR ,PS_JOB JOB,PS_EP_REVW_TYP_TBL TYP,PS_PERSON_NAME NAM,PS_PERSON_NAME EMP,PSXLATITEM XLAT, ( SELECT OPRID , EP_REVIEW_TYPE , FROM_DATE , TO_DATE , EP_MANAGER_ID , SUPERVISOR_ERN AS EMPL_RCD , CFG.HR_DR_EX_CWR , CFG.HR_DR_ALLOW_IND , CFG.ACCESS_TYPE FROM PS_EP_PG_USER_DFLT , PS_HR_DR_UI_CFG CFG WHERE CFG.PNLGRPNAME='HR_DR_TEAM_FLU' UNION ALL SELECT OPR.OPRID , DFLT.EP_REVIEW_TYPE , DFLT.FROM_DATE , DFLT.TO_DATE , OPR.EMPLID , JOB.EMPL_RCD , CFG.HR_DR_EX_CWR , CFG.HR_DR_ALLOW_IND , CFG.ACCESS_TYPE FROM PS_EP_DEFAULT DFLT ,PSOPRDEFN OPR , PS_HR_DR_PR_JOB_VW JOB , PS_HR_DR_UI_CFG CFG WHERE OPR.EMPLID=JOB.EMPLID AND CFG.PNLGRPNAME='HR_DR_TEAM_FLU' AND NOT EXISTS( SELECT 'X' FROM PS_EP_PG_USER_DFLT DFL WHERE DFL.OPRID=OPR.OPRID) AND DFLT.EP_TRANSACTION_NM='P') DFLT,PS_LOCATION_TBL LOC,PS_DEPT_TBL DEP,PSXLATITEM XLAT1 WHERE DOC.EMPLID=DIR.EMPLID AND DIR.EMPL_RCD=DOC.EMPL_RCD AND JOB.EMPLID=DOC.EMPLID AND JOB.EMPL_RCD=DOC.EMPL_RCD AND JOB.EFFDT=DIR.JOB_EFFDT AND JOB.EFFSEQ =( SELECT MAX(C1.EFFSEQ) FROM PS_JOB C1 WHERE C1.EMPLID=JOB.EMPLID AND C1.EMPL_RCD=JOB.EMPL_RCD AND C1.EFFDT=JOB.EFFDT) AND DOC.EP_REVIEW_STATUS <> 'CA' AND TYP.EP_REVIEW_TYPE = DOC.EP_REVIEW_TYPE AND %EffdtCheck(EP_REVW_TYP_TBL TYP1,TYP,%CurrentDateIn) AND DOC.EP_MANAGER_ID=NAM.EMPLID AND DOC.EMPLID=EMP.EMPLID AND DOC.EP_REVIEW_STATUS=XLAT.FIELDVALUE AND XLAT.FIELDNAME='EP_REVIEW_STATUS' AND %EffdtCheck(PSXLATITEM XLATA,XLAT,%CurrentDateIn) AND DFLT.EP_REVIEW_TYPE=DOC.EP_REVIEW_TYPE AND DOC.PERIOD_BEGIN_DT BETWEEN DFLT.FROM_DATE AND DFLT.TO_DATE AND DOC.PERIOD_END_DT BETWEEN DFLT.FROM_DATE AND DFLT.TO_DATE AND DFLT.EP_MANAGER_ID=DIR.SUPERVISOR_ID AND DFLT.EMPL_RCD=DIR.SUPERVIS_EMPL_RCD AND JOB.SETID_LOCATION = LOC.SETID AND JOB.LOCATION=LOC.LOCATION AND %EffdtCheck(LOCATION_TBL LOC1,LOC,%CurrentDateIn) AND JOB.SETID_DEPT=DEP.SETID AND JOB.DEPTID=DEP.DEPTID AND %EffdtCheck(DEPT_TBL DEP1,DEP,%CurrentDateIn) AND DOC.EP_APPROVAL_STATUS=XLAT1.FIELDVALUE AND XLAT1.FIELDNAME='EP_APPROVAL_STATUS' AND %EffdtCheck(PSXLATITEM XLAT1A,XLAT1,%CurrentDateIn) AND ((DFLT.HR_DR_EX_CWR='Y' AND JOB.PER_ORG IN ('EMP')) OR (DFLT.HR_DR_EX_CWR='N' AND JOB.PER_ORG IN ('EMP','CWR'))) AND ((DFLT.HR_DR_ALLOW_IND='Y' AND DIR.HR_DR_LEVEL >=1) OR (DFLT.HR_DR_ALLOW_IND='N' AND DIR.HR_DR_LEVEL = 1)) AND DIR.DRILL_DOWN_FLAG ='Y' AND 5= DFLT.ACCESS_TYPE UNION ALL SELECT DFLT.OPRID ,DOC.EP_APPRAISAL_ID ,DOC.EMPLID ,EMP.NAME_DISPLAY ,DOC.EMPL_RCD ,DOC.EP_MANAGER_ID ,NAM.NAME_DISPLAY ,DOC.EP_REVIEW_TYPE ,TYP.EP_REVW_DESCR ,DOC.EP_REVIEW_STATUS ,XLAT.XLATLONGNAME ,DOC.PERIOD_BEGIN_DT ,DOC.PERIOD_END_DT ,DIR.SUPERVISOR_ID ,DFLT.ACCESS_TYPE ,DIR.SUPERVISOR_FLAG ,DIR.DRILL_DOWN_FLAG ,DIR.JOB_EFFDT ,DIR.EMPL_STATUS ,DIR.HR_DR_LEVEL ,JOB.SETID_JOBCODE ,JOB.JOBCODE ,JOB.SETID_DEPT ,JOB.DEPTID ,DEP.DESCR ,JOB.SETID_LOCATION ,JOB.LOCATION ,LOC.DESCR ,DOC.EP_TP_SUB_STATUS ,DOC.EP_APPROVAL_STATUS ,XLAT1.XLATSHORTNAME ,TYP.EP_DOC_USAGE_IND ,%Coalesce (CASE WHEN DOC.EP_REVIEW_STATUS ='PA' THEN XLAT.XLATLONGNAME ||' '||XLAT1.XLATSHORTNAME END, CASE WHEN DOC.EP_REVIEW_STATUS ='TP' THEN ( SELECT %Sql(HR_LONG_TO_CHAR254, HR_SSTEXT_TEXT) FROM PS_HR_SSTEXT_TEXT TEX WHERE TEX.OBJECTOWNERID='HEP' AND %EffdtCheck(HR_SSTEXT_TEXT TEX1,TEX,%CurrentDateIn) AND ((TEX.HR_SSTEXT_SUB_ID = ' ' AND TYP.EP_DOC_USAGE_IND='P') OR (TEX.HR_SSTEXT_SUB_ID = 'D' AND TYP.EP_DOC_USAGE_IND='D')) AND TEX.TEXT_ID = 'TP_DOC_SUB_STATUS' AND TEX.HR_SSTEXT_KEY1=' ' AND TEX.HR_SSTEXT_KEY2=' ' AND TEX.HR_SSTEXT_KEY3=' ' AND TEX.HR_SSTEXT_KEY4=' ' AND TEX.HR_SSTEXT_KEY5 = DOC.EP_TP_SUB_STATUS AND TEX.HR_SSTEXT_KEY6 =' ') END, CASE WHEN DOC.EP_REVIEW_STATUS <>'PA' AND DOC.EP_REVIEW_STATUS <>'TP' THEN XLAT.XLATLONGNAME END) ,CASE WHEN (DOC.EP_REVIEW_STATUS ='AK' OR DOC.EP_REVIEW_STATUS ='AR' OR DOC.EP_REVIEW_STATUS ='PA' OR DOC.EP_REVIEW_STATUS ='RH') THEN APROLE.EP_DUE_DT WHEN (DOC.EP_REVIEW_STATUS ='CA' OR DOC.EP_REVIEW_STATUS ='CO') THEN NULL WHEN DOC.EP_REVIEW_STATUS ='EC' THEN DOC.EP_EST_DUE_DATE WHEN DOC.EP_REVIEW_STATUS ='TP' THEN DOC.EP_TP_CURR_DUE_DT WHEN APROLE.EP_DUE_DT IS NOT NULL THEN APROLE.EP_DUE_DT ELSE ( SELECT PART.EP_DUE_DT FROM PS_EP_APPR_PARTIC PART WHERE PART.EP_APPRAISAL_ID = DOC.EP_APPRAISAL_ID AND PART.EP_ROLE = 'M') END FROM PS_EP_APPR DOC LEFT OUTER JOIN PS_EP_APPR_ROLE APROLE ON DOC.EP_APPRAISAL_ID = APROLE.EP_APPRAISAL_ID AND DOC.EP_MANAGER_ID = APROLE.EP_REVIEWER_ID AND APROLE.EP_ROLE = 'M' ,PS_HR_DIRECT_REP_6 DIR ,PS_JOB JOB,PS_EP_REVW_TYP_TBL TYP,PS_PERSON_NAME NAM,PS_PERSON_NAME EMP,PSXLATITEM XLAT, ( SELECT OPRID , EP_REVIEW_TYPE , FROM_DATE , TO_DATE , EP_MANAGER_ID , SUPERVISOR_ERN AS EMPL_RCD , CFG.HR_DR_EX_CWR , CFG.HR_DR_ALLOW_IND , CFG.ACCESS_TYPE FROM PS_EP_PG_USER_DFLT , PS_HR_DR_UI_CFG CFG WHERE CFG.PNLGRPNAME='HR_DR_TEAM_FLU' UNION ALL SELECT OPR.OPRID , DFLT.EP_REVIEW_TYPE , DFLT.FROM_DATE , DFLT.TO_DATE , OPR.EMPLID , JOB.EMPL_RCD , CFG.HR_DR_EX_CWR , CFG.HR_DR_ALLOW_IND , CFG.ACCESS_TYPE FROM PS_EP_DEFAULT DFLT ,PSOPRDEFN OPR , PS_HR_DR_PR_JOB_VW JOB , PS_HR_DR_UI_CFG CFG WHERE OPR.EMPLID=JOB.EMPLID AND CFG.PNLGRPNAME='HR_DR_TEAM_FLU' AND NOT EXISTS( SELECT 'X' FROM PS_EP_PG_USER_DFLT DFL WHERE DFL.OPRID=OPR.OPRID) AND DFLT.EP_TRANSACTION_NM='P') DFLT,PS_LOCATION_TBL LOC,PS_DEPT_TBL DEP,PSXLATITEM XLAT1 WHERE DOC.EMPLID=DIR.EMPLID AND DIR.EMPL_RCD=DOC.EMPL_RCD AND JOB.EMPLID=DOC.EMPLID AND JOB.EMPL_RCD=DOC.EMPL_RCD AND JOB.EFFDT=DIR.JOB_EFFDT AND JOB.EFFSEQ =( SELECT MAX(C1.EFFSEQ) FROM PS_JOB C1 WHERE C1.EMPLID=JOB.EMPLID AND C1.EMPL_RCD=JOB.EMPL_RCD AND C1.EFFDT=JOB.EFFDT) AND DOC.EP_REVIEW_STATUS <> 'CA' AND TYP.EP_REVIEW_TYPE = DOC.EP_REVIEW_TYPE AND %EffdtCheck(EP_REVW_TYP_TBL TYP1,TYP,%CurrentDateIn) AND DOC.EP_MANAGER_ID=NAM.EMPLID AND DOC.EMPLID=EMP.EMPLID AND DOC.EP_REVIEW_STATUS=XLAT.FIELDVALUE AND XLAT.FIELDNAME='EP_REVIEW_STATUS' AND %EffdtCheck(PSXLATITEM XLATA,XLAT,%CurrentDateIn) AND DFLT.EP_REVIEW_TYPE=DOC.EP_REVIEW_TYPE AND DOC.PERIOD_BEGIN_DT BETWEEN DFLT.FROM_DATE AND DFLT.TO_DATE AND DOC.PERIOD_END_DT BETWEEN DFLT.FROM_DATE AND DFLT.TO_DATE AND DFLT.EP_MANAGER_ID=DIR.SUPERVISOR_ID AND DFLT.EMPL_RCD=DIR.SUPERVIS_EMPL_RCD AND JOB.SETID_LOCATION = LOC.SETID AND JOB.LOCATION=LOC.LOCATION AND %EffdtCheck(LOCATION_TBL LOC1,LOC,%CurrentDateIn) AND JOB.SETID_DEPT=DEP.SETID AND JOB.DEPTID=DEP.DEPTID AND %EffdtCheck(DEPT_TBL DEP1,DEP,%CurrentDateIn) AND DOC.EP_APPROVAL_STATUS=XLAT1.FIELDVALUE AND XLAT1.FIELDNAME='EP_APPROVAL_STATUS' AND %EffdtCheck(PSXLATITEM XLAT1A,XLAT1,%CurrentDateIn) AND ((DFLT.HR_DR_EX_CWR='Y' AND JOB.PER_ORG IN ('EMP')) OR (DFLT.HR_DR_EX_CWR='N' AND JOB.PER_ORG IN ('EMP','CWR'))) AND ((DFLT.HR_DR_ALLOW_IND='Y' AND DIR.HR_DR_LEVEL >=1) OR (DFLT.HR_DR_ALLOW_IND='N' AND DIR.HR_DR_LEVEL = 1)) AND DIR.DRILL_DOWN_FLAG ='Y' AND 6= DFLT.ACCESS_TYPE

  • Related Language Record: HR_TM_EP_LVW

    • # PeopleSoft Field Name PeopleSoft Field Type Database Column Type Description
    1 OPRID Character(30) VARCHAR2(30) NOT NULL A user's ID (see PSOPRDEFN).
    2 EP_APPRAISAL_ID Number(8,0) INTEGER NOT NULL Document ID
    3 EMPLID Character(11) VARCHAR2(11) NOT NULL Employee ID
    4 NAME Character(50) VARCHAR2(50) NOT NULL Name
    5 EMPL_RCD Number(3,0) SMALLINT NOT NULL Empl Record
    6 EP_MANAGER_ID Character(11) VARCHAR2(11) NOT NULL Manager/Mentor ID.
    7 NAME_DISPLAY Character(50) VARCHAR2(50) NOT NULL Display Name - name formatted for Display based on the Country
    8 EP_REVIEW_TYPE Character(8) VARCHAR2(8) NOT NULL Review type
    9 EP_REVW_DESCR Character(30) VARCHAR2(30) NOT NULL Review description
    10 EP_REVIEW_STATUS Character(4) VARCHAR2(4) NOT NULL This field tracks the status of a review document through it's life cycle. This is separate from the approval status of the review, which is tracked separately.
    AK=Acknowledged
    AR=Shared with Employee
    CA=Canceled
    CO=Completed
    EC=Define Criteria
    IP=Evaluation in Progress
    NS=Not Started
    PA=Approval
    RH=Pending Acknowledgement
    TP=Track Progress
    11 DESCR Character(30) VARCHAR2(30) NOT NULL Description
    12 PERIOD_BEGIN_DT Date(10) DATE Period Begin Date
    13 PERIOD_END_DT Date(10) DATE Period End Date
    14 SUPERVISOR_ID Character(11) VARCHAR2(11) NOT NULL Supervisor ID
    15 ACCESS_TYPE Character(1) VARCHAR2(1) NOT NULL Access Type
    1=By Dept Security Tree
    2=By Supervisor ID
    3=By Department Manager ID
    4=By Reports To Position
    5=By Part Posn Mgmt Supervisor
    6=By Part Posn Mgmt Dept Mgr ID
    7=By Group ID
    16 SUPERVISOR_FLAG Character(1) VARCHAR2(1) NOT NULL Y/N field to determine if a employee is a supervisor on the direct reports tables.
    17 DRILL_DOWN_FLAG Character(1) VARCHAR2(1) NOT NULL Y/N field to determine if the row will appear in the dynamic drill down query. These rows appear in the getsupervisor query.
    18 JOB_EFFDT Date(10) DATE Job Effective Date
    19 EMPL_STATUS Character(1) VARCHAR2(1) NOT NULL Payroll Status
    A=Active
    D=Deceased
    L=Leave of Absence
    P=Leave With Pay
    Q=Retired With Pay
    R=Retired
    S=Suspended
    T=Terminated
    U=Terminated With Pay
    V=Terminated Pension Pay Out
    W=Short Work Break
    X=Retired-Pension Administration
    20 HR_DR_LEVEL Number(3,0) SMALLINT NOT NULL Number denoting how many levels down the organizational chart the employee is from the supervisor. Direct reports are always level 1.
    21 SETID_JOBCODE Character(5) VARCHAR2(5) NOT NULL Job Code Set ID
    22 JOBCODE Character(6) VARCHAR2(6) NOT NULL Job Code
    23 SETID_DEPT Character(5) VARCHAR2(5) NOT NULL Department Set ID
    24 DEPTID Character(10) VARCHAR2(10) NOT NULL Department
    25 DEPT_DESCR Character(30) VARCHAR2(30) NOT NULL Department Description
    26 SETID_LOCATION Character(5) VARCHAR2(5) NOT NULL Location Set ID
    27 LOCATION Character(10) VARCHAR2(10) NOT NULL Location Code
    28 LOCATION_DESCR Character(30) VARCHAR2(30) NOT NULL Location Description
    29 EP_TP_SUB_STATUS Character(2) VARCHAR2(2) NOT NULL Sub Status for the Track Progress step in the process. The value will be 1-11 or 99 for Finalize.
    1=1
    10=10
    11=11
    2=2
    3=3
    4=4
    5=5
    6=6
    7=7
    8=8
    9=9
    F=F
    30 EP_APPROVAL_STATUS Character(4) VARCHAR2(4) NOT NULL This field tracks the current approval status of a review. This is separate from the review's lifecycle status, which is tracked separately.
    APRV=Approved
    DENY=Denied
    ERRO=Approval Error
    NREQ=Not Required
    OPEN=Not Submitted
    SUBM=Submitted
    31 DESCR1 Character(30) VARCHAR2(30) NOT NULL Descr
    32 EP_DOC_USAGE_IND Character(1) VARCHAR2(1) NOT NULL Indicator used to determine what document types are available for performance assessments and which are available for development assessments.
    D=Development
    P=Performance
    33 EP_REVW_STAT_DESCR Character(40) VARCHAR2(40) NOT NULL Review Status for Performance Documents
    34 EP_NEXT_DUE_DT Date(10) DATE Next Due Date