EX_TRANS_BD_VW

(SQL View)
 Index Back

Wallet Enhanced Data View

Wallet Enhanced Data View

SELECT A.EMPLID , A.DATA_SOURCE_EX , A.TRANS_NBR , A.SEQ_NBR , A.ITEM_SEQ_NBR , A.TXN_STATUS , A.SHEET_ID , A.LINE_NBR , A.MONETARY_AMOUNT , A.CURRENCY_CD , A.TRANS_DT , A.EXPENSE_TYPE , A.MERCHANT_CD , A.MERCHANT , A.TXN_LOCATION , A.TXN_AMOUNT , A.TXN_CURRENCY_CD , A.DESCR254 , A.TXN_LOAD_DATE , A.PERSONAL_EXPENSE , B.EX_EXP_TYPE_IMG , B.PSIMAGEVER , C.EX_MBL_NO_RECEIPTS FROM (PS_EX_TRANS A LEFT OUTER JOIN PS_EX_EXP_TYPE_IMG B ON B.EXPENSE_TYPE = A.EXPENSE_TYPE) LEFT OUTER JOIN PS_EX_TRAN_ATT_VW C ON A.EMPLID = C.EMPLID AND A.DATA_SOURCE_EX = C.DATA_SOURCE_EX AND A.TRANS_NBR = C.TRANS_NBR AND A.SEQ_NBR = C.SEQ_NBR WHERE A.ITEM_SEQ_NBR <> 0

# PeopleSoft Field Name PeopleSoft Field Type Database Column Type Description
1 EMPLID Character(11) VARCHAR2(11) NOT NULL Employee ID

Prompt Table: PERSONAL_DATA

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 SHEET_ID Character(10) VARCHAR2(10) NOT NULL Report ID
8 LINE_NBR Number(5,0) INTEGER NOT NULL Line Number: 11/24/08 - Added TARGET label [PC product]
9 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.
10 CURRENCY_CD Character(3) VARCHAR2(3) NOT NULL Currency Code
11 TRANS_DT Date(10) DATE Transaction Date
12 EXPENSE_TYPE Character(7) VARCHAR2(7) NOT NULL Expense Type
13 MERCHANT_CD Character(10) VARCHAR2(10) NOT NULL Preferred Merchant

Prompt Table: EX_MERCHANT_VW1

14 MERCHANT Character(40) VARCHAR2(40) NOT NULL Merchant
15 TXN_LOCATION Character(5) VARCHAR2(5) NOT NULL Expense Location

Prompt Table: EX_LOCATION_VW6

16 TXN_AMOUNT Signed Number(28,3) DECIMAL(26,3) NOT NULL Transaction Amount
17 TXN_CURRENCY_CD Character(3) VARCHAR2(3) NOT NULL Transaction Currency
18 DESCR254 Character(254) VARCHAR2(254) NOT NULL Description of length 254
19 TXN_LOAD_DATE Date(10) DATE Transaction Load Date
20 PERSONAL_EXPENSE Character(1) VARCHAR2(1) NOT NULL Personal Expense
N=No
Y=Yes
21 EX_EXP_TYPE_IMG Image / Attachment(62) VARCHAR2(62) Expense Type Image
22 PSIMAGEVER Number(10,0) DECIMAL(10) NOT NULL Image Version
23 EX_MBL_NO_RECEIPTS Number(3,0) SMALLINT NOT NULL Nbr of Receipts