EX_TRANS_VW (SQL View)	Index Back
Credit Card Ex Trans View This view is used for level 3 data for credit card transactions

EX_TRANS_VW

(SQL View)

Credit Card Ex Trans View

This view is used for level 3 data for credit card transactions

SELECT EMPLID , DATA_SOURCE_EX , TRANS_NBR , SEQ_NBR , ITEM_SEQ_NBR , TRANS_DT , EXPENSE_TYPE , MERCHANT , TXN_AMOUNT , TXN_CURRENCY_CD , MONETARY_AMOUNT , CURRENCY_CD , TXN_STATUS , DTTM_STAMP , PERSONAL_EXPENSE , EX_TYPE_FLAG FROM PS_EX_TRANS WHERE TXN_STATUS IN ('A', 'U') AND ITEM_SEQ_NBR <> 0

# PeopleSoft Field Name PeopleSoft Field Type Database Column Type Description

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

2 DATA_SOURCE_EX Character(3) VARCHAR2(3) NOT NULL External Data Sources for PS Expenses
AIR=Airline Ticket
AMA=American Express
AMG=American Express
AMH=American Express-Hotel Folio
AMM=American Express - Member List
AMX=American Express
CNS=CONUS
DC=Diners Club
IPH=iReceipts
MC=MasterCard CDFv2
MCX=MasterCard CDFv3
MSG=Text Message
NAP=PS NA Payroll
PDA=PDA
RNZ=Runzheimer
SAB=Sabre
UBT=User Chatbot
USB=US Bank
USR=User Input
VI4=Visa
VIS=Visa

3 TRANS_NBR Character(24) VARCHAR2(24) NOT NULL Transaction Number

4 SEQ_NBR Number(15,0) DECIMAL(15) NOT NULL Sequence Number

5 ITEM_SEQ_NBR Number(5,0) INTEGER NOT NULL Item Sequence Number

6 TRANS_DT Date(10) DATE Transaction Date

7 EXPENSE_TYPE Character(7) VARCHAR2(7) NOT NULL Expense Type

8 MERCHANT Character(40) VARCHAR2(40) NOT NULL Merchant

9 TXN_AMOUNT Signed Number(28,3) DECIMAL(26,3) NOT NULL Transaction Amount

10 TXN_CURRENCY_CD Character(3) VARCHAR2(3) NOT NULL Transaction Currency

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

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

13 TXN_STATUS Character(1) VARCHAR2(1) NOT NULL Transaction Status
A=Assigned
D=Marked for Delete
R=Rejected
T=All Transaction Statuses
U=Unassigned

14 DTTM_STAMP 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.

15 PERSONAL_EXPENSE Character(1) VARCHAR2(1) NOT NULL Personal Expense
N=No
Y=Yes
Y/N Table Edit

16 EX_TYPE_FLAG Character(1) VARCHAR2(1) NOT NULL Expense Type Flag

#	PeopleSoft Field Name	PeopleSoft Field Type	Database Column Type	Description
1	EMPLID	Character(11)	VARCHAR2(11) NOT NULL	Employee ID
2	DATA_SOURCE_EX	Character(3)	VARCHAR2(3) NOT NULL	External Data Sources for PS Expenses AIR=Airline Ticket AMA=American Express AMG=American Express AMH=American Express-Hotel Folio AMM=American Express - Member List AMX=American Express CNS=CONUS DC=Diners Club IPH=iReceipts MC=MasterCard CDFv2 MCX=MasterCard CDFv3 MSG=Text Message NAP=PS NA Payroll PDA=PDA RNZ=Runzheimer SAB=Sabre UBT=User Chatbot USB=US Bank USR=User Input VI4=Visa VIS=Visa
3	TRANS_NBR	Character(24)	VARCHAR2(24) NOT NULL	Transaction Number
4	SEQ_NBR	Number(15,0)	DECIMAL(15) NOT NULL	Sequence Number
5	ITEM_SEQ_NBR	Number(5,0)	INTEGER NOT NULL	Item Sequence Number
6	TRANS_DT	Date(10)	DATE	Transaction Date
7	EXPENSE_TYPE	Character(7)	VARCHAR2(7) NOT NULL	Expense Type
8	MERCHANT	Character(40)	VARCHAR2(40) NOT NULL	Merchant
9	TXN_AMOUNT	Signed Number(28,3)	DECIMAL(26,3) NOT NULL	Transaction Amount
10	TXN_CURRENCY_CD	Character(3)	VARCHAR2(3) NOT NULL	Transaction Currency
11	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.
12	CURRENCY_CD	Character(3)	VARCHAR2(3) NOT NULL	Currency Code
13	TXN_STATUS	Character(1)	VARCHAR2(1) NOT NULL	Transaction Status A=Assigned D=Marked for Delete R=Rejected T=All Transaction Statuses U=Unassigned
14	DTTM_STAMP	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.
15	PERSONAL_EXPENSE	Character(1)	VARCHAR2(1) NOT NULL	Personal Expense N=No Y=Yes Y/N Table Edit
16	EX_TYPE_FLAG	Character(1)	VARCHAR2(1) NOT NULL	Expense Type Flag