EP_SRCH_ITM_DEL

(SQL View)
 Index Back

Perf Analytics Item Deletions

Returns returns all item keys for items in a documents being cancelled or deleted.

SELECT DISTINCT DOC.EP_APPRAISAL_ID ,DOC.EMPLID ,DOC.EMPL_RCD ,ITM.EP_SECTION_TYPE ,ITM.EP_ITEM_ID ,ITM.EP_ITEM_SEQ ,%Coalesce(DOC.EP_CANCEL_DTTM,%CurrentDateTimeIn) FROM PS_EP_APPR DOC , PS_EP_APPR_B_SEC SEC , PS_EP_APPR_B_ITEM ITM WHERE DOC.EP_REVIEW_STATUS IN ('CA') 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 ,ITM.EP_SECTION_TYPE ,ITM.EP_ITEM_ID ,ITM.EP_ITEM_SEQ ,(CASE WHEN DOC.EP_REVIEW_STATUS = 'CO' THEN %Coalesce(ROLE.EP_COMPLETE_DTTM,%DateTimeIn('1900-01-01-00.00.00.000000')) WHEN DOC.EP_REVIEW_STATUS = 'CA' THEN %Coalesce(ROLE.EP_CANCEL_DTTM,%DateTimeIn('1900-01-01-00.00.00.000000')) ELSE %CurrentDateTimeIn 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 ('CO','CA') AND ROLE.EP_APPRAISAL_ID = DOC.EP_APPRAISAL_ID AND ROLE.EP_ROLE = 'M' AND SEC.EP_APPRAISAL_ID = DOC.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 EP_SECTION_TYPE Character(8) VARCHAR2(8) NOT NULL Section type
5 EP_ITEM_ID Character(12) VARCHAR2(12) NOT NULL Item unique identifier
6 EP_ITEM_SEQ Number(2,0) SMALLINT NOT NULL Item sequence number
7 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.