HR_TM_EP_LVW

(SQL View)
 Index Back

EP_PG_DOCVW_LNG

Related language view for HR_TM_EP_VW that is used in Performance tab pivot grid in My Team. This view is for My Team analytics.

SELECT DFLT.OPRID ,DOC.EP_APPRAISAL_ID ,TYP.LANGUAGE_CD ,TYP.EP_REVW_DESCR ,XLAT.XLATLONGNAME ,%Coalesce((SELECT DEP.DESCR FROM PS_DEPT_TBL_LANG DEP WHERE JOB.SETID_DEPT=DEP.SETID AND JOB.DEPTID=DEP.DEPTID AND %EffdtCheck(DEPT_TBL DEP1,DEP,%CurrentDateIn) AND TYP.LANGUAGE_CD=DEP.LANGUAGE_CD), ' ') ,%Coalesce((SELECT LOC.DESCR FROM PS_LOCATION_LANG LOC WHERE JOB.SETID_LOCATION = LOC.SETID AND JOB.LOCATION=LOC.LOCATION AND %EffdtCheck(LOCATION_TBL LOC1,LOC,%CurrentDateIn) AND TYP.LANGUAGE_CD=LOC.LANGUAGE_CD), ' ') ,XLAT1.XLATSHORTNAME ,%Coalesce (CASE WHEN DOC.EP_REVIEW_STATUS ='PA' THEN XLAT.XLATLONGNAME ||' '||XLAT1.XLATSHORTNAME END, CASE WHEN DOC.EP_REVIEW_STATUS ='TP' THEN ( SELECT TEX.DESCR254 FROM PS_EP_SSTEXTVW_LNG TEX WHERE ((TEX.HR_SSTEXT_SUB_ID = ' ' AND REV.EP_DOC_USAGE_IND='P') OR (TEX.HR_SSTEXT_SUB_ID = 'D' AND REV.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) FROM PS_EP_APPR DOC ,PS_EP_DOCST_DIR_VW DIR ,PS_JOB JOB ,PS_EP_REVWTYP_LANG TYP ,PS_EP_REVW_TYP_TBL REV ,PSXLATITEMLANG XLAT ,PS_EP_PG_DFLT_VW DFLT ,PSXLATITEMLANG 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(JOB1.EFFSEQ) FROM PS_JOB JOB1 WHERE JOB1.EMPLID=JOB.EMPLID AND JOB1.EMPL_RCD=JOB.EMPL_RCD AND JOB1.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 TYP.LANGUAGE_CD=XLAT.LANGUAGE_CD AND TYP.LANGUAGE_CD=XLAT1.LANGUAGE_CD AND DOC.EP_REVIEW_STATUS=XLAT.FIELDVALUE AND XLAT.FIELDNAME='EP_REVIEW_STATUS' AND %EffdtCheck(PSXLATITEM XLATA,XLAT,%CurrentDateIn) 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 DOC.EP_APPROVAL_STATUS=XLAT1.FIELDVALUE AND XLAT1.FIELDNAME='EP_APPROVAL_STATUS' AND %EffdtCheck(PSXLATITEM XLAT1A,XLAT1,%CurrentDateIn) AND REV.EP_REVIEW_TYPE=TYP.EP_REVIEW_TYPE AND REV.EFFDT=TYP.EFFDT

  • Related Language Record for HR_TM_EP_VW

    • # 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 LANGUAGE_CD Character(3) VARCHAR2(3) NOT NULL Language Code
    4 EP_REVW_DESCR Character(30) VARCHAR2(30) NOT NULL Review description
    5 DESCR Character(30) VARCHAR2(30) NOT NULL Description
    6 DEPT_DESCR Character(30) VARCHAR2(30) NOT NULL Department Description
    7 LOCATION_DESCR Character(30) VARCHAR2(30) NOT NULL Location Description
    8 DESCR1 Character(30) VARCHAR2(30) NOT NULL Descr
    9 EP_REVW_STAT_DESCR Character(40) VARCHAR2(40) NOT NULL Review Status for Performance Documents