EP_SRCH_PERF_VW (SQL View)	Index Back
Perf Analytics Document View Returns Document header data for Insights Performance Analytics

EP_SRCH_PERF_VW

(SQL View)

Perf Analytics Document View

Returns Document header data for Insights Performance Analytics

SELECT DOC.EP_APPRAISAL_ID ,DOC.EMPLID ,DOC.EMPL_RCD ,DOC.PERIOD_BEGIN_DT ,DOC.PERIOD_END_DT ,DOC.EP_MANAGER_ID ,TYP.EP_DOC_USAGE_IND ,DOC.EP_REVIEW_TYPE ,TYP.EP_REVW_DESCR ,DOC.EP_TEMPLATE_ID ,TMP.DESCR ,DOC.EP_REVIEW_STATUS ,%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) ,DOC.RATING_MODEL ,DOC.EP_CALC_METHOD ,SEC.REVIEW_POINTS ,SEC.REVIEW_RATING ,SEC.EP_RATING ,DOC.EP_PRE_REVW_RATING ,DOC.EP_PRE_RATING ,DOC.EP_PRE_POINTS ,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') THEN NULL WHEN (DOC.EP_REVIEW_STATUS ='CO') THEN DOC.PERIOD_END_DT 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 ,( SELECT COUNT(*) FROM PS_EP_APPR DOC2 WHERE DOC.PERIOD_BEGIN_DT = DOC2.PERIOD_BEGIN_DT AND DOC.PERIOD_END_DT = DOC2.PERIOD_END_DT AND DOC.EP_REVIEW_TYPE = DOC2.EP_REVIEW_TYPE AND DOC.EP_MANAGER_ID = DOC2.EP_MANAGER_ID) ,( SELECT COUNT(*) FROM PS_EP_APPR DOC3 , PS_EP_APPR_SECTION SEC2 WHERE DOC.PERIOD_BEGIN_DT = DOC3.PERIOD_BEGIN_DT AND DOC.PERIOD_END_DT = DOC3.PERIOD_END_DT AND DOC.EP_REVIEW_TYPE = DOC3.EP_REVIEW_TYPE AND DOC.EP_MANAGER_ID = DOC3.EP_MANAGER_ID AND DOC3.EP_APPRAISAL_ID = SEC2.EP_APPRAISAL_ID AND DOC3.EP_MANAGER_ID = SEC2.EP_REVIEWER_ID AND SEC.REVIEW_RATING = SEC2.REVIEW_RATING AND SEC2.EP_ROLE = 'M' AND SEC2.EP_SECTION_TYPE = 'SUMMARY') ,(CASE WHEN %Coalesce(DOC.LASTUPDDTTM,%DateTimeIn('1900-01-01-00.00.00.000000')) >= %Coalesce(DOC.EP_CREATE_DTTM,%DateTimeIn('1900-01-01-00.00.00.000000')) AND %Coalesce(DOC.LASTUPDDTTM,%DateTimeIn('1900-01-01-00.00.00.000000')) >= %Coalesce(APROLE.LASTUPDDTTM,%DateTimeIn('1900-01-01-00.00.00.000000')) THEN %Coalesce(DOC.LASTUPDDTTM,%DateTimeIn('1900-01-01-00.00.00.000000')) WHEN %Coalesce(DOC.EP_CREATE_DTTM,%DateTimeIn('1900-01-01-00.00.00.000000')) >= %Coalesce(DOC.LASTUPDDTTM,%DateTimeIn('1900-01-01-00.00.00.000000')) AND %Coalesce(DOC.EP_CREATE_DTTM,%DateTimeIn('1900-01-01-00.00.00.000000')) >= %Coalesce(APROLE.LASTUPDDTTM,%DateTimeIn('1900-01-01-00.00.00.000000')) THEN %Coalesce(DOC.EP_CREATE_DTTM,%DateTimeIn('1900-01-01-00.00.00.000000')) WHEN %Coalesce(APROLE.LASTUPDDTTM,%DateTimeIn('1900-01-01-00.00.00.000000')) >= %Coalesce(DOC.LASTUPDDTTM,%DateTimeIn('1900-01-01-00.00.00.000000')) AND %Coalesce(APROLE.LASTUPDDTTM,%DateTimeIn('1900-01-01-00.00.00.000000')) >= %Coalesce(DOC.EP_CREATE_DTTM,%DateTimeIn('1900-01-01-00.00.00.000000')) THEN %Coalesce(APROLE.LASTUPDDTTM,%DateTimeIn('1900-01-01-00.00.00.000000')) ELSE %DateTimeIn('1900-01-01-00.00.00.000000') 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' LEFT OUTER JOIN PS_EP_APPR_SECTION SEC ON DOC.EP_APPRAISAL_ID = SEC.EP_APPRAISAL_ID AND DOC.EP_MANAGER_ID = SEC.EP_REVIEWER_ID AND SEC.EP_ROLE = 'M' AND SEC.EP_SECTION_TYPE = 'SUMMARY') , PS_EP_REVW_TYP_TBL TYP , PS_EP_TMPL_DEFN TMP, PSXLATITEM XLAT , PSXLATITEM XLAT1 WHERE DOC.EP_REVIEW_STATUS NOT IN ('CA','NS') AND TYP.EP_REVIEW_TYPE = DOC.EP_REVIEW_TYPE AND %EffdtCheck(EP_REVW_TYP_TBL TYP1,TYP,%CurrentDateIn) AND TMP.EP_REVIEW_TYPE = DOC.EP_REVIEW_TYPE AND DOC.EP_TEMPLATE_ID = TMP.EP_TEMPLATE_ID AND %EffdtCheck(EP_TMPL_DEFN TMP1,TMP,%CurrentDateIn) AND DOC.EP_REVIEW_STATUS=XLAT.FIELDVALUE AND XLAT.FIELDNAME='EP_REVIEW_STATUS' AND %EffdtCheck(PSXLATITEM XLATA,XLAT,%CurrentDateIn) AND DOC.EP_APPROVAL_STATUS=XLAT1.FIELDVALUE AND XLAT1.FIELDNAME='EP_APPROVAL_STATUS' AND %EffdtCheck(PSXLATITEM XLAT1A,XLAT1,%CurrentDateIn)

Related Language Record: EP_SRCH_PERF_LV

# PeopleSoft Field Name PeopleSoft Field Type Database Column Type Description

1 EP_APPRAISAL_ID Number(8,0) INTEGER NOT NULL Document ID

2 EMPLID Character(11) VARCHAR2(11) NOT NULL Employee ID

3 EMPL_RCD Number(3,0) SMALLINT NOT NULL Empl Record

4 PERIOD_BEGIN_DT Date(10) DATE Period Begin Date

5 PERIOD_END_DT Date(10) DATE Period End Date

6 EP_MANAGER_ID Character(11) VARCHAR2(11) NOT NULL Manager/Mentor ID.

7 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

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_TEMPLATE_ID Character(8) VARCHAR2(8) NOT NULL Template unique identifier

11 EP_TMPL_DESCR Character(30) VARCHAR2(30) NOT NULL Template

12 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

13 EP_REVW_STAT_DESCR Character(40) VARCHAR2(40) NOT NULL Review Status for Performance Documents

14 RATING_MODEL Character(4) VARCHAR2(4) NOT NULL Rating Model

15 EP_CALC_METHOD Character(1) VARCHAR2(1) NOT NULL Calculation method used - Summary, Average, Review Band
A=Average
R=Review Band
S=Summation

16 REVIEW_POINTS Number(3,0) SMALLINT NOT NULL Review Points

17 REVIEW_RATING Character(1) VARCHAR2(1) NOT NULL Review Rating

18 EP_RATING Number(6,2) DECIMAL(5,2) NOT NULL Rating

19 EP_PRE_REVW_RATING Character(1) VARCHAR2(1) NOT NULL Preliminary Review Rating Code (character).

20 EP_PRE_RATING Number(6,2) DECIMAL(5,2) NOT NULL Preliminary Rating (numeric)

21 EP_PRE_POINTS Number(3,0) SMALLINT NOT NULL Preliminary Rating Points

22 EP_NEXT_DUE_DT Date(10) DATE Next Due Date

23 EP_DOC_COUNT Number(12,0) DECIMAL(12) NOT NULL Document Count

24 EP_RATING_COUNT Number(5,0) INTEGER NOT NULL Rated Document Count

25 LASTUPDDTTM DateTime(26) TIMESTAMP Specifies the date and time of the last update to an entry. This field is maintained by PeopleSoft and is used in a variety of contexts.

#	PeopleSoft Field Name	PeopleSoft Field Type	Database Column Type	Description
1	EP_APPRAISAL_ID	Number(8,0)	INTEGER NOT NULL	Document ID
2	EMPLID	Character(11)	VARCHAR2(11) NOT NULL	Employee ID
3	EMPL_RCD	Number(3,0)	SMALLINT NOT NULL	Empl Record
4	PERIOD_BEGIN_DT	Date(10)	DATE	Period Begin Date
5	PERIOD_END_DT	Date(10)	DATE	Period End Date
6	EP_MANAGER_ID	Character(11)	VARCHAR2(11) NOT NULL	Manager/Mentor ID.
7	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
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_TEMPLATE_ID	Character(8)	VARCHAR2(8) NOT NULL	Template unique identifier
11	EP_TMPL_DESCR	Character(30)	VARCHAR2(30) NOT NULL	Template
12	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
13	EP_REVW_STAT_DESCR	Character(40)	VARCHAR2(40) NOT NULL	Review Status for Performance Documents
14	RATING_MODEL	Character(4)	VARCHAR2(4) NOT NULL	Rating Model
15	EP_CALC_METHOD	Character(1)	VARCHAR2(1) NOT NULL	Calculation method used - Summary, Average, Review Band A=Average R=Review Band S=Summation
16	REVIEW_POINTS	Number(3,0)	SMALLINT NOT NULL	Review Points
17	REVIEW_RATING	Character(1)	VARCHAR2(1) NOT NULL	Review Rating
18	EP_RATING	Number(6,2)	DECIMAL(5,2) NOT NULL	Rating
19	EP_PRE_REVW_RATING	Character(1)	VARCHAR2(1) NOT NULL	Preliminary Review Rating Code (character).
20	EP_PRE_RATING	Number(6,2)	DECIMAL(5,2) NOT NULL	Preliminary Rating (numeric)
21	EP_PRE_POINTS	Number(3,0)	SMALLINT NOT NULL	Preliminary Rating Points
22	EP_NEXT_DUE_DT	Date(10)	DATE	Next Due Date
23	EP_DOC_COUNT	Number(12,0)	DECIMAL(12) NOT NULL	Document Count
24	EP_RATING_COUNT	Number(5,0)	INTEGER NOT NULL	Rated Document Count
25	LASTUPDDTTM	DateTime(26)	TIMESTAMP	Specifies the date and time of the last update to an entry. This field is maintained by PeopleSoft and is used in a variety of contexts.