EX_EXP_HIST_VW3 (SQL View)	Index Back
Employee History Inquiry View

EX_EXP_HIST_VW3

(SQL View)

Employee History Inquiry View

SELECT A.EMPLID , ' ' , A.SHEET_ID , ' ' , ' ' , ' ' , A.SHEET_NAME , A.SHEET_STATUS , ' ' , ' ' , MIN(B.TRANS_DT) , MAX(B.TRANS_DT) , SUM(B.MONETARY_AMOUNT) , B.CURRENCY_CD , A.SUBMISSION_DATE , A.POST_STATUS_EX , 'S' FROM PS_EX_SHEET_HDR A , PS_EX_SHEET_LINE B WHERE A.SHEET_ID = B.SHEET_ID AND A.SHEET_STATUS NOT IN ('CLS', 'ESC', 'DEN', 'DNA', 'DNU', 'PD', 'STG') GROUP BY A.EMPLID, A.SHEET_ID, A.SHEET_NAME, A.SHEET_STATUS, B.CURRENCY_CD, A.SUBMISSION_DATE, A.POST_STATUS_EX

# PeopleSoft Field Name PeopleSoft Field Type Database Column Type Description

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

2 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.

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

4 ADVANCE_ID Character(10) VARCHAR2(10) NOT NULL Advance ID

5 TRAVEL_AUTH_NAME Character(30) VARCHAR2(30) NOT NULL Field used to give a descriptive name to each travel authorization. Associated with the header information of a travel authorization and found on many records and panels within travel authorization objects.

6 TRAVEL_AUTH_STATUS Character(3) VARCHAR2(3) NOT NULL Describes the current status of the travel authorization - whether pending, submitted or approved. This field is associated with the header record of travel authorizations and is found on many records and panels within travel authorization objects. The status will change depending on the action taken upon the travel authorization. PeopleCode is used to update the status.
APR=Approved
CLS=Closed
DEN=Denied
DNA=Denied by Approver
DNU=Denied by Auditor
HDA=On Hold, with Approver
HDU=On Hold, with Auditor
HLD=On Hold
PAR=Approvals in Process
PND=Pending
PRO=In Process
RCN=Reconciled
RRT=Transaction Rerouted
SFA=Submission in Process
STG=Staged
SUB=Submitted for Approval
XML=Submitted, Pending Validation

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

8 SHEET_STATUS Character(3) VARCHAR2(3) NOT NULL Report Status
APY=Approved for Payment
APZ=Approved For Zero Payment
CLS=Closed
DEN=Denied
DNA=Denied by Approver
DNU=Denied by Auditor
ESC=Escheated Payment
HDA=On Hold, with Approver
HDU=On Hold, with Auditor
HLD=On Hold
MFS=Marked for Submit
OPN=Open
PAR=Approvals in Process
PD=Paid
PND=Pending
PRO=In Process
RAP=Approved
SFA=Submission in Process
STG=Staged
SUB=Submitted for Approval
XML=Submitted, Pending Validation

9 ADVANCE_NAME Character(30) VARCHAR2(30) NOT NULL Advance Description

10 ADV_STATUS Character(3) VARCHAR2(3) NOT NULL Advance Status
APY=Approved for Payment
CLS=Closed
DEN=Denied
DNA=Denied by Approver
DNU=Denied by Auditor
HDA=On Hold, with Approver
HDU=On Hold, with Auditor
HLD=On Hold
PAR=Approvals in Process
PD=Paid
PND=Pending
PRO=In Process
PST=Past Due Balance
RAP=Approved
RCN=Reconciled
RRT=Reroute Transaction
SFA=Submission in Process
STG=Staged
SUB=Submitted for Approval
XML=Submitted, Pending Validation

11 FROM_DT Date(10) DATE From Date

12 THRU_DATE Date(10) DATE Through Date

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 CURRENCY_CD Character(3) VARCHAR2(3) NOT NULL Currency Code

15 SUBMISSION_DATE Date(10) DATE Submission Date

16 POST_STATUS_EX Character(1) VARCHAR2(1) NOT NULL Expenses Post Status
C=Closed
M=Marked for Unpost
N=Not Applied
O=Unpost In Process
P=Posted
S=Close In Process
U=Unposted

17 EX_DOC_TYPE Character(1) VARCHAR2(1) NOT NULL Identifies Expenses Document Type
A=Cash Advance
C=Time Report
J=Time Adjustment
M=Accrual - My Wallet
R=Accrual - Expense Report
S=Expense Report
T=Travel Authorization

#	PeopleSoft Field Name	PeopleSoft Field Type	Database Column Type	Description
1	EMPLID	Character(11)	VARCHAR2(11) NOT NULL	Employee ID
2	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.
3	SHEET_ID	Character(10)	VARCHAR2(10) NOT NULL	Report ID
4	ADVANCE_ID	Character(10)	VARCHAR2(10) NOT NULL	Advance ID
5	TRAVEL_AUTH_NAME	Character(30)	VARCHAR2(30) NOT NULL	Field used to give a descriptive name to each travel authorization. Associated with the header information of a travel authorization and found on many records and panels within travel authorization objects.
6	TRAVEL_AUTH_STATUS	Character(3)	VARCHAR2(3) NOT NULL	Describes the current status of the travel authorization - whether pending, submitted or approved. This field is associated with the header record of travel authorizations and is found on many records and panels within travel authorization objects. The status will change depending on the action taken upon the travel authorization. PeopleCode is used to update the status. APR=Approved CLS=Closed DEN=Denied DNA=Denied by Approver DNU=Denied by Auditor HDA=On Hold, with Approver HDU=On Hold, with Auditor HLD=On Hold PAR=Approvals in Process PND=Pending PRO=In Process RCN=Reconciled RRT=Transaction Rerouted SFA=Submission in Process STG=Staged SUB=Submitted for Approval XML=Submitted, Pending Validation
7	SHEET_NAME	Character(30)	VARCHAR2(30) NOT NULL	Report Description
8	SHEET_STATUS	Character(3)	VARCHAR2(3) NOT NULL	Report Status APY=Approved for Payment APZ=Approved For Zero Payment CLS=Closed DEN=Denied DNA=Denied by Approver DNU=Denied by Auditor ESC=Escheated Payment HDA=On Hold, with Approver HDU=On Hold, with Auditor HLD=On Hold MFS=Marked for Submit OPN=Open PAR=Approvals in Process PD=Paid PND=Pending PRO=In Process RAP=Approved SFA=Submission in Process STG=Staged SUB=Submitted for Approval XML=Submitted, Pending Validation
9	ADVANCE_NAME	Character(30)	VARCHAR2(30) NOT NULL	Advance Description
10	ADV_STATUS	Character(3)	VARCHAR2(3) NOT NULL	Advance Status APY=Approved for Payment CLS=Closed DEN=Denied DNA=Denied by Approver DNU=Denied by Auditor HDA=On Hold, with Approver HDU=On Hold, with Auditor HLD=On Hold PAR=Approvals in Process PD=Paid PND=Pending PRO=In Process PST=Past Due Balance RAP=Approved RCN=Reconciled RRT=Reroute Transaction SFA=Submission in Process STG=Staged SUB=Submitted for Approval XML=Submitted, Pending Validation
11	FROM_DT	Date(10)	DATE	From Date
12	THRU_DATE	Date(10)	DATE	Through Date
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	CURRENCY_CD	Character(3)	VARCHAR2(3) NOT NULL	Currency Code
15	SUBMISSION_DATE	Date(10)	DATE	Submission Date
16	POST_STATUS_EX	Character(1)	VARCHAR2(1) NOT NULL	Expenses Post Status C=Closed M=Marked for Unpost N=Not Applied O=Unpost In Process P=Posted S=Close In Process U=Unposted
17	EX_DOC_TYPE	Character(1)	VARCHAR2(1) NOT NULL	Identifies Expenses Document Type A=Cash Advance C=Time Report J=Time Adjustment M=Accrual - My Wallet R=Accrual - Expense Report S=Expense Report T=Travel Authorization