EP_SRCH_ITEM_VW

(SQL View)
 Index Back

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 ,DOC.EP_REVIEW_TYPE ,DOC.EP_TEMPLATE_ID ,ITM.EP_SECTION_TYPE ,ITM.EP_ITEM_ID ,ITM.EP_ITEM_SEQ ,ITM.EP_TITLE ,ITM.EP_ITEM_PCT ,(CASE WHEN SEC.JPM_C_LIST_ID <> ' ' AND ITM.EP_ITEM_STATUS <> ' ' THEN ( SELECT DESCR100 FROM PS_JPM_C_LIST_VW L WHERE L.JPM_C_LIST_ID=SEC.JPM_C_LIST_ID AND L.JPM_C_LIST_VALUE=ITM.EP_ITEM_STATUS) WHEN ITM.EP_ITEM_STATUS <> ' ' THEN ( SELECT XLATLONGNAME FROM PSXLATITEM X WHERE X.FIELDNAME='EP_ITEM_STATUS' AND X.FIELDVALUE = ITM.EP_ITEM_STATUS AND %EffdtCheck(PSXLATITEM X1,X,%CurrentDateIn)) ELSE ' ' END) ,(CASE WHEN SEC.EP_ALL_ITEMS_SW='Y' THEN SEC.RATING_MODEL ELSE ITM.RATING_MODEL END) ,ITM.EP_MGR_REVW_POINTS ,ITM.EP_MGR_REVW_RATING ,ITM.EP_MGR_NUM_RATING ,ITM.EP_TARGET_RATING ,(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')) 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')) THEN %Coalesce(DOC.EP_CREATE_DTTM,%DateTimeIn('1900-01-01-00.00.00.000000')) ELSE %DateTimeIn('1900-01-01-00.00.00.000000') END) FROM PS_EP_APPR DOC , PS_EP_APPR_CHECKPT CHK , PS_EP_APPR_B_SEC SEC , PS_EP_APPR_B_ITEM ITM WHERE DOC.EP_REVIEW_STATUS IN ('TP','EC') AND CHK.EP_APPRAISAL_ID = DOC.EP_APPRAISAL_ID AND ((CHK.EP_CHKPT_COMPLETE = 'C' AND CHK.EP_CHKPT_TYPE = 'S') OR CHK.EP_CHKPT_TYPE = 'C') AND CHK.EP_CHKPT_NBR = ( SELECT MAX(EP_CHKPT_NBR) FROM PS_EP_APPR_CHECKPT CHK1 WHERE CHK1.EP_APPRAISAL_ID = CHK.EP_APPRAISAL_ID AND ((CHK1.EP_CHKPT_COMPLETE = 'C' AND CHK1.EP_CHKPT_TYPE = 'S') OR CHK1.EP_CHKPT_TYPE = 'C')) AND SEC.EP_APPRAISAL_ID = DOC.EP_APPRAISAL_ID AND ITM.EP_APPRAISAL_ID = SEC.EP_APPRAISAL_ID AND ITM.EP_SECTION_TYPE = SEC.EP_SECTION_TYPE UNION SELECT DOC.EP_APPRAISAL_ID ,DOC.EMPLID ,DOC.EMPL_RCD ,DOC.PERIOD_BEGIN_DT ,DOC.PERIOD_END_DT ,DOC.EP_MANAGER_ID ,DOC.EP_REVIEW_TYPE ,DOC.EP_TEMPLATE_ID ,ITM.EP_SECTION_TYPE ,ITM.EP_ITEM_ID ,ITM.EP_ITEM_SEQ ,ITM.EP_TITLE ,ITM.EP_ITEM_PCT ,(CASE WHEN SEC.JPM_C_LIST_ID <> ' ' AND ITM.EP_ITEM_STATUS <> ' ' THEN ( SELECT DESCR100 FROM PS_JPM_C_LIST_VW L WHERE L.JPM_C_LIST_ID=SEC.JPM_C_LIST_ID AND L.JPM_C_LIST_VALUE=ITM.EP_ITEM_STATUS) WHEN ITM.EP_ITEM_STATUS <> ' ' THEN ( SELECT XLATLONGNAME FROM PSXLATITEM X WHERE X.FIELDNAME='EP_ITEM_STATUS' AND X.FIELDVALUE = ITM.EP_ITEM_STATUS AND %EffdtCheck(PSXLATITEM X1,X,%CurrentDateIn)) ELSE ' ' END) ,(CASE WHEN SEC.EP_ALL_ITEMS_SW='Y' THEN SEC.RATING_MODEL ELSE ITM.RATING_MODEL END) ,ITM.REVIEW_POINTS ,ITM.REVIEW_RATING ,ITM.EP_RATING ,ITM.EP_TARGET_RATING ,(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(ROLE.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(ROLE.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(ROLE.LASTUPDDTTM,%DateTimeIn('1900-01-01-00.00.00.000000')) >=%Coalesce(DOC.LASTUPDDTTM,%DateTimeIn('1900-01-01-00.00.00.000000')) AND %Coalesce(ROLE.LASTUPDDTTM,%DateTimeIn('1900-01-01-00.00.00.000000')) >= %Coalesce(DOC.EP_CREATE_DTTM,%DateTimeIn('1900-01-01-00.00.00.000000'))THEN %Coalesce(ROLE.LASTUPDDTTM,%DateTimeIn('1900-01-01-00.00.00.000000')) ELSE %DateTimeIn('1900-01-01-00.00.00.000000') END) FROM PS_EP_APPR DOC , PS_EP_APPR_ROLE ROLE, PS_EP_APPR_SECTION SEC , PS_EP_APPR_ITEM ITM WHERE DOC.EP_REVIEW_STATUS IN ('AR','AK','PA','RH','IP') AND ROLE.EP_APPRAISAL_ID = DOC.EP_APPRAISAL_ID AND ROLE.EP_ROLE = 'M' AND SEC.EP_APPRAISAL_ID = ROLE.EP_APPRAISAL_ID AND SEC.EP_ROLE = ROLE.EP_ROLE AND SEC.EP_REVIEWER_ID = ROLE.EP_REVIEWER_ID AND ITM.EP_APPRAISAL_ID = SEC.EP_APPRAISAL_ID AND ITM.EP_ROLE = SEC.EP_ROLE AND ITM.EP_REVIEWER_ID = SEC.EP_REVIEWER_ID AND ITM.EP_SECTION_TYPE = SEC.EP_SECTION_TYPE

# 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_REVIEW_TYPE Character(8) VARCHAR2(8) NOT NULL Review type
8 EP_TEMPLATE_ID Character(8) VARCHAR2(8) NOT NULL Template unique identifier
9 EP_SECTION_TYPE Character(8) VARCHAR2(8) NOT NULL Section type
10 EP_ITEM_ID Character(12) VARCHAR2(12) NOT NULL Item unique identifier
11 EP_ITEM_SEQ Number(2,0) SMALLINT NOT NULL Item sequence number
12 EP_TITLE Character(60) VARCHAR2(60) NOT NULL Title of an item
13 EP_ITEM_PCT Number(3,0) SMALLINT NOT NULL Percentage complete an item is.
14 DESCR100 Character(100) VARCHAR2(100) NOT NULL Length 100 Description
15 RATING_MODEL Character(4) VARCHAR2(4) NOT NULL Rating Model
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_TARGET_RATING Character(1) VARCHAR2(1) NOT NULL Target Rating Field
20 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.