EX_DEL_SHT_VW (SQL View)	Index Back
Delete Expense Report Delete Expense Report

EX_DEL_SHT_VW

(SQL View)

Delete Expense Report

SELECT A.EMPLID , A.SHEET_ID , A.CREDIT_ADV , A.DOC_TYPE , A.DOC_SEQ_NBR , A.DOC_SEQ_DATE , A.BUSINESS_UNIT_GL , A.SHEET_NAME , A.BUSINESS_PURPOSE , A.ACCOUNTING_DT , A.CREATION_DT , A.CURRENCY_CD , A.TOTAL_AMT , A.TRAVEL_AUTH_ID FROM PS_EX_SHEET_HDR A WHERE A.SHEET_STATUS IN ('PND','DEN','DNA','DNU') AND A.SHEET_ID NOT IN ( SELECT C.SHEET_ID FROM PS_EX_SHEET_ADV C , PS_EX_ADV_HDR D WHERE C.ADVANCE_ID = D.ADVANCE_ID AND D.ADV_STATUS = 'PD' AND D.ADVANCE_ID <> A.CREDIT_ADV) AND (NOT EXISTS ( SELECT 'X' FROM PS_KK_SOURCE_HDR D WHERE D.SHEET_ID = A.SHEET_ID) OR (EXISTS ( SELECT 'X' FROM PS_KK_SOURCE_HDR D WHERE D.SHEET_ID = A.SHEET_ID) AND NOT EXISTS ( SELECT 'X' FROM PS_EX_APRVL_HIST H WHERE H.EX_DOC_ID = A.SHEET_ID AND H.EX_DOC_TYPE = 'S' AND H.ACTION_NAME = 'SUB')) OR (EXISTS ( SELECT 'X' FROM PS_KK_SOURCE_HDR D WHERE D.SHEET_ID = A.SHEET_ID) AND EXISTS ( SELECT 'X' FROM PS_EX_APRVL_HIST H WHERE H.EX_DOC_ID = A.SHEET_ID AND H.EX_DOC_TYPE = 'S' AND H.ACTION_NAME IN ('SBR','WDR','DNY') AND H.SEQ_NUM = ( SELECT MAX(H2.SEQ_NUM) FROM PS_EX_APRVL_HIST H2 WHERE H.EX_DOC_ID = H2.EX_DOC_ID AND H.EX_DOC_TYPE = H2.EX_DOC_TYPE AND H.VERSION_NUM = H2.VERSION_NUM))))

# PeopleSoft Field Name PeopleSoft Field Type Database Column Type Description

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

2 SHEET_ID Character(10) VARCHAR2(10) NOT NULL Report ID

3 CREDIT_ADV Character(10) VARCHAR2(10) NOT NULL Credit Advance

4 DOC_TYPE Character(8) VARCHAR2(8) NOT NULL Specifies the business purpose of a financial transaction in countries that require all financial transactions to be tracked as "documents". A Document Type is associated with one and only one Journal Code.

5 DOC_SEQ_NBR Character(12) VARCHAR2(12) NOT NULL Specifies the sequence number assigned to each financial transaction (a document). The sequence number may be manually entered or system-generated.

6 DOC_SEQ_DATE Date(10) DATE Specifies the date that a document sequence number is assigned to a document or the date the document was created.

7 BUSINESS_UNIT_GL Character(5) VARCHAR2(5) NOT NULL GL Business Unit

8 SHEET_NAME Character(30) VARCHAR2(30) NOT NULL Report Description

9 BUSINESS_PURPOSE Character(5) VARCHAR2(5) NOT NULL Business Purpose
Prompt Table: EX_PURPOSE_TBL

10 ACCOUNTING_DT Date(10) DATE The accounting entry construction date for a given transaction (a generic field that crosses multiple

11 CREATION_DT Date(10) DATE Creation Date

12 CURRENCY_CD Character(3) VARCHAR2(3) NOT NULL Currency Code

13 MONETARY_AMOUNT Signed Number(28,3) DECIMAL(26,3) NOT NULL Specifies the monetary amount of a debit or credit in the business unit base currency. Debit entries are positive and credit entries are negative. This amount is only zero if associated with a statistical account.

14 TRAVEL_AUTH_ID Character(10) VARCHAR2(10) NOT NULL Travel Authorization Identification. Number used to define travel authorizations. Key field on most travel authorization records and exists at level 0 for travel authorization panels.

#	PeopleSoft Field Name	PeopleSoft Field Type	Database Column Type	Description
1	EMPLID	Character(11)	VARCHAR2(11) NOT NULL	Employee ID
2	SHEET_ID	Character(10)	VARCHAR2(10) NOT NULL	Report ID
3	CREDIT_ADV	Character(10)	VARCHAR2(10) NOT NULL	Credit Advance
4	DOC_TYPE	Character(8)	VARCHAR2(8) NOT NULL	Specifies the business purpose of a financial transaction in countries that require all financial transactions to be tracked as "documents". A Document Type is associated with one and only one Journal Code.
5	DOC_SEQ_NBR	Character(12)	VARCHAR2(12) NOT NULL	Specifies the sequence number assigned to each financial transaction (a document). The sequence number may be manually entered or system-generated.
6	DOC_SEQ_DATE	Date(10)	DATE	Specifies the date that a document sequence number is assigned to a document or the date the document was created.
7	BUSINESS_UNIT_GL	Character(5)	VARCHAR2(5) NOT NULL	GL Business Unit
8	SHEET_NAME	Character(30)	VARCHAR2(30) NOT NULL	Report Description
9	BUSINESS_PURPOSE	Character(5)	VARCHAR2(5) NOT NULL	Business Purpose Prompt Table: EX_PURPOSE_TBL
10	ACCOUNTING_DT	Date(10)	DATE	The accounting entry construction date for a given transaction (a generic field that crosses multiple
11	CREATION_DT	Date(10)	DATE	Creation Date
12	CURRENCY_CD	Character(3)	VARCHAR2(3) NOT NULL	Currency Code
13	MONETARY_AMOUNT	Signed Number(28,3)	DECIMAL(26,3) NOT NULL	Specifies the monetary amount of a debit or credit in the business unit base currency. Debit entries are positive and credit entries are negative. This amount is only zero if associated with a statistical account.
14	TRAVEL_AUTH_ID	Character(10)	VARCHAR2(10) NOT NULL	Travel Authorization Identification. Number used to define travel authorizations. Key field on most travel authorization records and exists at level 0 for travel authorization panels.