EX_TRANS_RST_VW

(SQL View)
 Index Back

Employee Transactions

Employee Transactions

SELECT EMPLID , DATA_SOURCE_EX , TRANS_NBR , SEQ_NBR , ITEM_SEQ_NBR , TXN_STATUS , TRANS_DT , MERCHANT_CD , MERCHANT , MONETARY_AMOUNT , CURRENCY_CD , AIRFARE_RCPT_NBR , EXPENSE_TYPE , TXN_AMOUNT , TXN_CURRENCY_CD , CUR_EXCHNG_RT , RATE_USR_DFN , CITY , TXN_LOCATION , EXPEND_MTHD , PREF_MRCHNT_FLG , NBR_NIGHTS , DESCR254 , DESCR_LN1 , DESCR_LN2 , DESCR_LN3 , DESCR_LN4 , MOBILE_DWNLD_FLG , DTTM_STAMP , RATE_MULT , RATE_DIV , CRDMEM_ACCT_NBR , PERSONAL_EXPENSE FROM PS_EX_TRANS WHERE TXN_STATUS = 'D'

# 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 TXN_STATUS Character(1) VARCHAR2(1) NOT NULL Transaction Status
A=Assigned
D=Marked for Delete
R=Rejected
T=All Transaction Statuses
U=Unassigned
7 TRANS_DT Date(10) DATE Transaction Date
8 MERCHANT_CD Character(10) VARCHAR2(10) NOT NULL Preferred Merchant

Prompt Table: EX_MERCHANT_VW

9 MERCHANT Character(40) VARCHAR2(40) NOT NULL Merchant
10 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.
11 CURRENCY_CD Character(3) VARCHAR2(3) NOT NULL Currency Code

Prompt Table: CURRENCY_CD_TBL

12 AIRFARE_RCPT_NBR Character(16) VARCHAR2(16) NOT NULL Airfare Receipt Number
13 EXPENSE_TYPE Character(7) VARCHAR2(7) NOT NULL Expense Type

Prompt Table: EX_TYPES_TBL

14 TXN_AMOUNT Signed Number(28,3) DECIMAL(26,3) NOT NULL Transaction Amount
15 TXN_CURRENCY_CD Character(3) VARCHAR2(3) NOT NULL Transaction Currency
16 CUR_EXCHNG_RT Number(16,8) DECIMAL(15,8) NOT NULL Currency Exchange Rate
17 RATE_USR_DFN Character(1) VARCHAR2(1) NOT NULL The field indicates the type of Exchange rate to be used. User defined or system default
D=Custom - Direct
I=Custom - Indirect
S=System Default
18 CITY Character(30) VARCHAR2(30) NOT NULL City
19 TXN_LOCATION Character(5) VARCHAR2(5) NOT NULL Expense Location
20 EXPEND_MTHD Character(3) VARCHAR2(3) NOT NULL Payment Type

Prompt Table: EX_EXP_MTHD_TBL

21 PREF_MRCHNT_FLG Character(1) VARCHAR2(1) NOT NULL Preferred Merchant
N=No
Y=Yes

Y/N Table Edit

22 NBR_NIGHTS Number(3,0) SMALLINT NOT NULL Number of Nights
23 DESCR254 Character(254) VARCHAR2(254) NOT NULL Description of length 254
24 DESCR_LN1 Character(50) VARCHAR2(50) NOT NULL Description Line 1
25 DESCR_LN2 Character(50) VARCHAR2(50) NOT NULL Description Line 2
26 DESCR_LN3 Character(50) VARCHAR2(50) NOT NULL Description Line 3
27 DESCR_LN4 Character(50) VARCHAR2(50) NOT NULL Description Line 4
28 MOBILE_DWNLD_FLG Character(1) VARCHAR2(1) NOT NULL Picked Up By Mobile
N=No
Y=Yes

Y/N Table Edit

29 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.
30 RATE_MULT Signed Number(17,8) DECIMAL(15,8) NOT NULL Rate Multiplier
31 RATE_DIV Number(16,8) DECIMAL(15,8) NOT NULL Rate Divisor
32 CRDMEM_ACCT_NBR Character(44) VARCHAR2(44) NOT NULL Cardmember Number
33 PERSONAL_EXPENSE Character(1) VARCHAR2(1) NOT NULL Personal Expense
N=No
Y=Yes

Y/N Table Edit