EX_TRANS_MBL_VW (SQL View)	Index Back
Employee Transactions Employee Transactions

EX_TRANS_MBL_VW

(SQL View)

Employee Transactions

SELECT A.EMPLID , A.DATA_SOURCE_EX , A.TRANS_NBR , A.SEQ_NBR , A.ITEM_SEQ_NBR , A.AIRFARE_RCPT_NBR , A.MOBILE_POPWKEY , A.EX_TRANS_TYPE , A.CCTRANS_TYPE_CD , A.EX_DOC_TYPE , A.ADVANCE_ID , A.ADVANCE_NAME , A.SHEET_ID , A.SHEET_NAME , A.LINE_NBR , A.EXPENSE_TYPE , A.RECEIPT_VERIFIED , A.TRANS_DT , A.TXN_AMOUNT , A.TXN_CURRENCY_CD , A.MONETARY_AMOUNT , A.CURRENCY_CD , A.CUR_EXCHNG_RT , A.RATE_USR_DFN , A.RATE_MULT , A.RATE_DIV , A.CITY , A.COUNTRY , A.TXN_LOCATION , A.EXPEND_MTHD , A.PREF_MRCHNT_FLG , A.MERCHANT_CD , A.MERCHANT , A.NBR_NIGHTS , A.AIR_TKT_RECON_STAT , A.DAYS_OUTSTANDING , A.DESCR254 , A.CORP_ACCT_NBR , A.CRDMEM_ACCT_NBR , A.SUPPLIER_NBR , A.ADVANCE_SOURCE , A.TXN_STATUS , A.TXN_LOAD_DATE , A.DESCR_LN1 , A.DESCR_LN2 , A.DESCR_LN3 , A.DESCR_LN4 , A.EMP_NOTIFY_SW , A.MOBILE_DWNLD_FLG , A.DTTM_STAMP , A.DISTANCE , A.DISTANCE_TYPE , A.MOBILE_SYNC_STATUS , A.EX_DOC_ID , A.PROCESS_FLG , A.PROCESS_INSTANCE , CASE WHEN B.EX_MBL_NO_RECEIPTS IS NULL THEN 0 ELSE B.EX_MBL_NO_RECEIPTS END FROM PS_EX_TRANS A LEFT OUTER JOIN PS_EX_TRAN_ATT_VW B ON A.EMPLID = B.EMPLID AND A.DATA_SOURCE_EX = B.DATA_SOURCE_EX AND A.TRANS_NBR = B.TRANS_NBR AND A.SEQ_NBR = B.SEQ_NBR

# 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
Default Value: USR

3 TRANS_NBR Character(24) VARCHAR2(24) NOT NULL Transaction Number
Default Value: 1

4 SEQ_NBR Number(15,0) DECIMAL(15) NOT NULL Sequence Number
Default Value: 1

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

6 AIRFARE_RCPT_NBR Character(16) VARCHAR2(16) NOT NULL Airfare Receipt Number

7 MOBILE_POPWKEY Character(50) VARCHAR2(50) NOT NULL Mobile Id

8 EX_TRANS_TYPE Character(4) VARCHAR2(4) NOT NULL Expenses Transaction Type, used to categorize external data that is loaded into the expense product, for example corporate card expenses/credits, ATM advances and travel bookings.
ALL=All Transaction Types
ATMA=ATM Advance
CCCR=Credit
CCEX=Expense
TRBK=Travel Reservation

9 CCTRANS_TYPE_CD Character(2) VARCHAR2(2) NOT NULL Credit Card Transaction Code

10 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

11 ADVANCE_ID Character(10) VARCHAR2(10) NOT NULL Advance ID
Prompt Table: EX_ADV_HDR

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

13 SHEET_ID Character(10) VARCHAR2(10) NOT NULL Report ID
Prompt Table: EX_SHEET_HDR

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

15 LINE_NBR Number(5,0) INTEGER NOT NULL Line Number: 11/24/08 - Added TARGET label [PC product]

16 EXPENSE_TYPE Character(7) VARCHAR2(7) NOT NULL Expense Type
Prompt Table: EX_TYPES_TBL

17 RECEIPT_VERIFIED Character(1) VARCHAR2(1) NOT NULL Receipt Verified
Y/N Table Edit
Default Value: N

18 TRANS_DT Date(10) DATE Transaction Date

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

20 TXN_CURRENCY_CD Character(3) VARCHAR2(3) NOT NULL Transaction Currency
Default Value: BUS_UNIT_TBL_EX.BASE_CURRENCY
Prompt Table: CURRENCY_CD_TBL

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

22 CURRENCY_CD Character(3) VARCHAR2(3) NOT NULL Currency Code
Default Value: BUS_UNIT_TBL_EX.BASE_CURRENCY
Prompt Table: CURRENCY_CD_TBL

23 CUR_EXCHNG_RT Number(16,8) DECIMAL(15,8) NOT NULL Currency Exchange Rate
Default Value: 1

24 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
Default Value: S

25 RATE_MULT Signed Number(17,8) DECIMAL(15,8) NOT NULL Rate Multiplier

26 RATE_DIV Number(16,8) DECIMAL(15,8) NOT NULL Rate Divisor
Default Value: 1

27 CITY Character(30) VARCHAR2(30) NOT NULL City

28 COUNTRY Character(3) VARCHAR2(3) NOT NULL Country
Prompt Table: COUNTRY_TBL

29 TXN_LOCATION Character(5) VARCHAR2(5) NOT NULL Expense Location
Prompt Table: EX_LOCATION_VW

30 EXPEND_MTHD Character(3) VARCHAR2(3) NOT NULL Payment Type
Prompt Table: EX_EXP_MTHD_TBL

31 PREF_MRCHNT_FLG Character(1) VARCHAR2(1) NOT NULL Preferred Merchant
N=No
Y=Yes
Y/N Table Edit
Default Value: N

32 MERCHANT_CD Character(10) VARCHAR2(10) NOT NULL Preferred Merchant
Prompt Table: EX_MERCHANT_VW

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

34 NBR_NIGHTS Number(3,0) SMALLINT NOT NULL Number of Nights
Default Value: 0

35 AIR_TKT_RECON_STAT Character(1) VARCHAR2(1) NOT NULL Airline Ticket Reconciliation Status
C=Cancelled
N=Not Reconciled
R=Reconciled
Default Value: N

36 DAYS_OUTSTANDING Number(4,0) SMALLINT NOT NULL Days Outstanding

37 DESCR254 Character(254) VARCHAR2(254) NOT NULL Description of length 254

38 CORP_ACCT_NBR Character(20) VARCHAR2(20) NOT NULL Corporate Number

39 CRDMEM_ACCT_NBR Character(44) VARCHAR2(44) NOT NULL Cardmember Number
Prompt Table: EX_EMP_CARD_VW2

40 SUPPLIER_NBR Character(11) VARCHAR2(11) NOT NULL Supplier Reference Nbr

41 ADVANCE_SOURCE Character(4) VARCHAR2(4) NOT NULL Advance Source
Prompt Table: EX_ADV_SRC_TBL

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

43 TXN_LOAD_DATE Date(10) DATE Transaction Load Date

44 DESCR_LN1 Character(50) VARCHAR2(50) NOT NULL Description Line 1

45 DESCR_LN2 Character(50) VARCHAR2(50) NOT NULL Description Line 2

46 DESCR_LN3 Character(50) VARCHAR2(50) NOT NULL Description Line 3

47 DESCR_LN4 Character(50) VARCHAR2(50) NOT NULL Description Line 4

48 EMP_NOTIFY_SW Character(1) VARCHAR2(1) NOT NULL Employee Notified
Y/N Table Edit
Default Value: N

49 MOBILE_DWNLD_FLG Character(1) VARCHAR2(1) NOT NULL Picked Up By Mobile
N=No
Y=Yes
Default Value: N

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

51 DISTANCE Number(8,2) DECIMAL(7,2) NOT NULL Distance Traveled

52 DISTANCE_TYPE Character(1) VARCHAR2(1) NOT NULL Distance Type
3=Metres
K=Kilometers
M=Miles

53 MOBILE_SYNC_STATUS Character(1) VARCHAR2(1) NOT NULL Marks the synchronization status of a mobile transaction
A=Assigned
N=New
R=Repeat
S=Synchronized

54 EX_DOC_ID Character(10) VARCHAR2(10) NOT NULL Documentation ID

55 PROCESS_FLG Character(1) VARCHAR2(1) NOT NULL Processing Flag
Default Value: N

56 PROCESS_INSTANCE Number(10,0) DECIMAL(10) NOT NULL Process Instance
Default Value: 0

57 EX_MBL_NO_RECEIPTS Number(3,0) SMALLINT NOT NULL Nbr of Receipts

#	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 Default Value: USR
3	TRANS_NBR	Character(24)	VARCHAR2(24) NOT NULL	Transaction Number Default Value: 1
4	SEQ_NBR	Number(15,0)	DECIMAL(15) NOT NULL	Sequence Number Default Value: 1
5	ITEM_SEQ_NBR	Number(5,0)	INTEGER NOT NULL	Item Sequence Number Default Value: 0
6	AIRFARE_RCPT_NBR	Character(16)	VARCHAR2(16) NOT NULL	Airfare Receipt Number
7	MOBILE_POPWKEY	Character(50)	VARCHAR2(50) NOT NULL	Mobile Id
8	EX_TRANS_TYPE	Character(4)	VARCHAR2(4) NOT NULL	Expenses Transaction Type, used to categorize external data that is loaded into the expense product, for example corporate card expenses/credits, ATM advances and travel bookings. ALL=All Transaction Types ATMA=ATM Advance CCCR=Credit CCEX=Expense TRBK=Travel Reservation
9	CCTRANS_TYPE_CD	Character(2)	VARCHAR2(2) NOT NULL	Credit Card Transaction Code
10	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
11	ADVANCE_ID	Character(10)	VARCHAR2(10) NOT NULL	Advance ID Prompt Table: EX_ADV_HDR
12	ADVANCE_NAME	Character(30)	VARCHAR2(30) NOT NULL	Advance Description
13	SHEET_ID	Character(10)	VARCHAR2(10) NOT NULL	Report ID Prompt Table: EX_SHEET_HDR
14	SHEET_NAME	Character(30)	VARCHAR2(30) NOT NULL	Report Description
15	LINE_NBR	Number(5,0)	INTEGER NOT NULL	Line Number: 11/24/08 - Added TARGET label [PC product]
16	EXPENSE_TYPE	Character(7)	VARCHAR2(7) NOT NULL	Expense Type Prompt Table: EX_TYPES_TBL
17	RECEIPT_VERIFIED	Character(1)	VARCHAR2(1) NOT NULL	Receipt Verified Y/N Table Edit Default Value: N
18	TRANS_DT	Date(10)	DATE	Transaction Date
19	TXN_AMOUNT	Signed Number(28,3)	DECIMAL(26,3) NOT NULL	Transaction Amount
20	TXN_CURRENCY_CD	Character(3)	VARCHAR2(3) NOT NULL	Transaction Currency Default Value: BUS_UNIT_TBL_EX.BASE_CURRENCY Prompt Table: CURRENCY_CD_TBL
21	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.
22	CURRENCY_CD	Character(3)	VARCHAR2(3) NOT NULL	Currency Code Default Value: BUS_UNIT_TBL_EX.BASE_CURRENCY Prompt Table: CURRENCY_CD_TBL
23	CUR_EXCHNG_RT	Number(16,8)	DECIMAL(15,8) NOT NULL	Currency Exchange Rate Default Value: 1
24	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 Default Value: S
25	RATE_MULT	Signed Number(17,8)	DECIMAL(15,8) NOT NULL	Rate Multiplier
26	RATE_DIV	Number(16,8)	DECIMAL(15,8) NOT NULL	Rate Divisor Default Value: 1
27	CITY	Character(30)	VARCHAR2(30) NOT NULL	City
28	COUNTRY	Character(3)	VARCHAR2(3) NOT NULL	Country Prompt Table: COUNTRY_TBL
29	TXN_LOCATION	Character(5)	VARCHAR2(5) NOT NULL	Expense Location Prompt Table: EX_LOCATION_VW
30	EXPEND_MTHD	Character(3)	VARCHAR2(3) NOT NULL	Payment Type Prompt Table: EX_EXP_MTHD_TBL
31	PREF_MRCHNT_FLG	Character(1)	VARCHAR2(1) NOT NULL	Preferred Merchant N=No Y=Yes Y/N Table Edit Default Value: N
32	MERCHANT_CD	Character(10)	VARCHAR2(10) NOT NULL	Preferred Merchant Prompt Table: EX_MERCHANT_VW
33	MERCHANT	Character(40)	VARCHAR2(40) NOT NULL	Merchant
34	NBR_NIGHTS	Number(3,0)	SMALLINT NOT NULL	Number of Nights Default Value: 0
35	AIR_TKT_RECON_STAT	Character(1)	VARCHAR2(1) NOT NULL	Airline Ticket Reconciliation Status C=Cancelled N=Not Reconciled R=Reconciled Default Value: N
36	DAYS_OUTSTANDING	Number(4,0)	SMALLINT NOT NULL	Days Outstanding
37	DESCR254	Character(254)	VARCHAR2(254) NOT NULL	Description of length 254
38	CORP_ACCT_NBR	Character(20)	VARCHAR2(20) NOT NULL	Corporate Number
39	CRDMEM_ACCT_NBR	Character(44)	VARCHAR2(44) NOT NULL	Cardmember Number Prompt Table: EX_EMP_CARD_VW2
40	SUPPLIER_NBR	Character(11)	VARCHAR2(11) NOT NULL	Supplier Reference Nbr
41	ADVANCE_SOURCE	Character(4)	VARCHAR2(4) NOT NULL	Advance Source Prompt Table: EX_ADV_SRC_TBL
42	TXN_STATUS	Character(1)	VARCHAR2(1) NOT NULL	Transaction Status A=Assigned D=Marked for Delete R=Rejected T=All Transaction Statuses U=Unassigned Default Value: U
43	TXN_LOAD_DATE	Date(10)	DATE	Transaction Load Date
44	DESCR_LN1	Character(50)	VARCHAR2(50) NOT NULL	Description Line 1
45	DESCR_LN2	Character(50)	VARCHAR2(50) NOT NULL	Description Line 2
46	DESCR_LN3	Character(50)	VARCHAR2(50) NOT NULL	Description Line 3
47	DESCR_LN4	Character(50)	VARCHAR2(50) NOT NULL	Description Line 4
48	EMP_NOTIFY_SW	Character(1)	VARCHAR2(1) NOT NULL	Employee Notified Y/N Table Edit Default Value: N
49	MOBILE_DWNLD_FLG	Character(1)	VARCHAR2(1) NOT NULL	Picked Up By Mobile N=No Y=Yes Default Value: N
50	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.
51	DISTANCE	Number(8,2)	DECIMAL(7,2) NOT NULL	Distance Traveled
52	DISTANCE_TYPE	Character(1)	VARCHAR2(1) NOT NULL	Distance Type 3=Metres K=Kilometers M=Miles
53	MOBILE_SYNC_STATUS	Character(1)	VARCHAR2(1) NOT NULL	Marks the synchronization status of a mobile transaction A=Assigned N=New R=Repeat S=Synchronized
54	EX_DOC_ID	Character(10)	VARCHAR2(10) NOT NULL	Documentation ID
55	PROCESS_FLG	Character(1)	VARCHAR2(1) NOT NULL	Processing Flag Default Value: N
56	PROCESS_INSTANCE	Number(10,0)	DECIMAL(10) NOT NULL	Process Instance Default Value: 0
57	EX_MBL_NO_RECEIPTS	Number(3,0)	SMALLINT NOT NULL	Nbr of Receipts