EX_AATM_TRAN_VW (SQL View)	Index Back
Employee ATM Transactions Employee ATM Transactions

EX_AATM_TRAN_VW

(SQL View)

Employee ATM Transactions

SELECT A.EMPLID ,A.DATA_SOURCE_EX ,A.TRANS_NBR ,A.SEQ_NBR ,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.CITY ,A.COUNTRY ,A.TXN_LOCATION ,A.EXPEND_MTHD ,A.PREF_MRCHNT_FLG ,A.MERCHANT_CD ,A.MERCHANT ,A.NBR_NIGHTS ,A.AIRFARE_RCPT_NBR ,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 FROM PS_EX_TRANS A WHERE EX_TRANS_TYPE = 'ATMA' AND TXN_STATUS = 'U' AND MOBILE_DWNLD_FLG = 'N'

# 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 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

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

7 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

8 ADVANCE_ID Character(10) VARCHAR2(10) NOT NULL Advance ID

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

10 SHEET_ID Character(10) VARCHAR2(10) NOT NULL Report ID

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

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

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

14 RECEIPT_VERIFIED Character(1) VARCHAR2(1) NOT NULL Receipt Verified
Y/N Table Edit

15 TRANS_DT Date(10) DATE Transaction Date

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
Default Value: BUS_UNIT_TBL_EX.BASE_CURRENCY
Prompt Table: CURRENCY_CD_TBL

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

19 CURRENCY_CD Character(3) VARCHAR2(3) NOT NULL Currency Code
Prompt Table: CURRENCY_CD_TBL

20 CUR_EXCHNG_RT Number(16,8) DECIMAL(15,8) NOT NULL Currency Exchange Rate

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

22 COUNTRY Character(3) VARCHAR2(3) NOT NULL Country

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

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

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

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

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

28 NBR_NIGHTS Number(3,0) SMALLINT NOT NULL Number of Nights

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

30 AIR_TKT_RECON_STAT Character(1) VARCHAR2(1) NOT NULL Airline Ticket Reconciliation Status
C=Cancelled
N=Not Reconciled
R=Reconciled

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

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

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

34 CRDMEM_ACCT_NBR Character(44) VARCHAR2(44) NOT NULL Cardmember Number

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

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

37 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

38 TXN_LOAD_DATE Date(10) DATE Transaction Load Date

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

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

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

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

43 EMP_NOTIFY_SW Character(1) VARCHAR2(1) NOT NULL Employee Notified

44 MOBILE_DWNLD_FLG Character(1) VARCHAR2(1) NOT NULL Picked Up By Mobile
N=No
Y=Yes

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

#	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	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
6	CCTRANS_TYPE_CD	Character(2)	VARCHAR2(2) NOT NULL	Credit Card Transaction Code
7	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
8	ADVANCE_ID	Character(10)	VARCHAR2(10) NOT NULL	Advance ID
9	ADVANCE_NAME	Character(30)	VARCHAR2(30) NOT NULL	Advance Description
10	SHEET_ID	Character(10)	VARCHAR2(10) NOT NULL	Report ID
11	SHEET_NAME	Character(30)	VARCHAR2(30) NOT NULL	Report Description
12	LINE_NBR	Number(5,0)	INTEGER NOT NULL	Line Number: 11/24/08 - Added TARGET label [PC product]
13	EXPENSE_TYPE	Character(7)	VARCHAR2(7) NOT NULL	Expense Type Prompt Table: EX_TYPES_TBL
14	RECEIPT_VERIFIED	Character(1)	VARCHAR2(1) NOT NULL	Receipt Verified Y/N Table Edit
15	TRANS_DT	Date(10)	DATE	Transaction Date
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 Default Value: BUS_UNIT_TBL_EX.BASE_CURRENCY Prompt Table: CURRENCY_CD_TBL
18	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.
19	CURRENCY_CD	Character(3)	VARCHAR2(3) NOT NULL	Currency Code Prompt Table: CURRENCY_CD_TBL
20	CUR_EXCHNG_RT	Number(16,8)	DECIMAL(15,8) NOT NULL	Currency Exchange Rate
21	CITY	Character(30)	VARCHAR2(30) NOT NULL	City
22	COUNTRY	Character(3)	VARCHAR2(3) NOT NULL	Country
23	TXN_LOCATION	Character(5)	VARCHAR2(5) NOT NULL	Expense Location Prompt Table: EX_LOCATION_VW
24	EXPEND_MTHD	Character(3)	VARCHAR2(3) NOT NULL	Payment Type Prompt Table: EX_EXP_MTHD_TBL
25	PREF_MRCHNT_FLG	Character(1)	VARCHAR2(1) NOT NULL	Preferred Merchant N=No Y=Yes Y/N Table Edit Default Value: N
26	MERCHANT_CD	Character(10)	VARCHAR2(10) NOT NULL	Preferred Merchant Prompt Table: EX_MERCHANT_VW
27	MERCHANT	Character(40)	VARCHAR2(40) NOT NULL	Merchant
28	NBR_NIGHTS	Number(3,0)	SMALLINT NOT NULL	Number of Nights
29	AIRFARE_RCPT_NBR	Character(16)	VARCHAR2(16) NOT NULL	Airfare Receipt Number
30	AIR_TKT_RECON_STAT	Character(1)	VARCHAR2(1) NOT NULL	Airline Ticket Reconciliation Status C=Cancelled N=Not Reconciled R=Reconciled
31	DAYS_OUTSTANDING	Number(4,0)	SMALLINT NOT NULL	Days Outstanding
32	DESCR254	Character(254)	VARCHAR2(254) NOT NULL	Description of length 254
33	CORP_ACCT_NBR	Character(20)	VARCHAR2(20) NOT NULL	Corporate Number
34	CRDMEM_ACCT_NBR	Character(44)	VARCHAR2(44) NOT NULL	Cardmember Number
35	SUPPLIER_NBR	Character(11)	VARCHAR2(11) NOT NULL	Supplier Reference Nbr
36	ADVANCE_SOURCE	Character(4)	VARCHAR2(4) NOT NULL	Advance Source Prompt Table: EX_ADV_SRC_TBL
37	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
38	TXN_LOAD_DATE	Date(10)	DATE	Transaction Load Date
39	DESCR_LN1	Character(50)	VARCHAR2(50) NOT NULL	Description Line 1
40	DESCR_LN2	Character(50)	VARCHAR2(50) NOT NULL	Description Line 2
41	DESCR_LN3	Character(50)	VARCHAR2(50) NOT NULL	Description Line 3
42	DESCR_LN4	Character(50)	VARCHAR2(50) NOT NULL	Description Line 4
43	EMP_NOTIFY_SW	Character(1)	VARCHAR2(1) NOT NULL	Employee Notified
44	MOBILE_DWNLD_FLG	Character(1)	VARCHAR2(1) NOT NULL	Picked Up By Mobile N=No Y=Yes
45	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.