EX_TRANS_DTL_VW

(SQL View)
 Index Back

Wallet Items View

Wallet Items View

SELECT TRANS.EMPLID , TRANS.DATA_SOURCE_EX , TRANS.TRANS_NBR , TRANS.SEQ_NBR , TRANS.ITEM_SEQ_NBR , TRANS.TXN_STATUS , TRANS.SHEET_ID , TRANS.LINE_NBR , TRANS.MONETARY_AMOUNT , TRANS.CURRENCY_CD , TRANS.TRANS_DT , TRANS.EXPENSE_TYPE , TRANS.MERCHANT_CD , TRANS.MERCHANT , TRANS.TXN_LOCATION , TRANS.TXN_AMOUNT , TRANS.TXN_CURRENCY_CD , TRANS.DESCR254 , TRANS.TXN_LOAD_DATE , CASE WHEN IMG.PSIMAGEVER > 0 THEN IMG.SETID ELSE ' ' END , IMG.EX_EXP_TYPE_IMG , IMG.PSIMAGEVER , ATTC.EX_MBL_NO_RECEIPTS FROM (( SELECT TRANS.EMPLID , TRANS.DATA_SOURCE_EX , TRANS.TRANS_NBR , TRANS.SEQ_NBR , TRANS.ITEM_SEQ_NBR , TRANS.TXN_STATUS , TRANS.SHEET_ID , TRANS.LINE_NBR , TRANS.MONETARY_AMOUNT , TRANS.CURRENCY_CD , TRANS.TRANS_DT , TRANS.EXPENSE_TYPE , TRANS.MERCHANT_CD , TRANS.MERCHANT , TRANS.TXN_LOCATION , TRANS.TXN_AMOUNT , TRANS.TXN_CURRENCY_CD , TRANS.DESCR254 , TRANS.TXN_LOAD_DATE , CNTRL.SETID FROM PS_EX_TRANS TRANS , PS_EX_EE_ORG_DTL ORG , PS_SET_CNTRL_REC CNTRL WHERE TRANS.EMPLID = ORG.EMPLID AND ORG.BUSINESS_UNIT_GL = CNTRL.SETCNTRLVALUE AND ORG.DFLT_EE_PROF_FLG = 'Y' AND CNTRL.RECNAME = 'EX_TYPES_TBL' ) TRANS LEFT OUTER JOIN PS_EX_EXP_TYPE_IMG IMG ON IMG.SETID = TRANS.SETID AND IMG.EXPENSE_TYPE = TRANS.EXPENSE_TYPE) LEFT OUTER JOIN PS_EX_TRAN_ATT_VW ATTC ON TRANS.EMPLID = ATTC.EMPLID AND TRANS.DATA_SOURCE_EX = ATTC.DATA_SOURCE_EX AND TRANS.TRANS_NBR = ATTC.TRANS_NBR AND TRANS.SEQ_NBR = ATTC.SEQ_NBR WHERE TRANS.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 SETID Character(5) VARCHAR2(5) NOT NULL SetID
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