EX_AIR_TKT_VW

(SQL View)
 Index Back

Expense Transactions

Used for SABRE/BTS Credit Card Feed. Credit Card Transactions are loaded into this table for expense sheet processing.

SELECT D.EMPLID ,'SAB' , A.TRAN_REF_NUM AS TRANS_NBR ,'TRBK' , L.EXPEND_MTHD , G.EXPENSE_TYPE , 'N' AS RECEIPT_VERIFIED , %Substring(B.TKT_NUM, 1,16) AS AIRFARE_RCPT_NBR , 'N' AS AIR_TKT_RECON_STAT , A.MERCHANT_NAME AS MERCHANT , %DateIn(B.EXPENSE_DATE) AS TRANS_DT , A.BILLING_CURR_AMT AS TXN_AMOUNT , H.CURRENCY_CD AS TXN_CURRENCY_CD , A.BILLING_CURR_AMT AS MONETARY_AMOUNT , H.CURRENCY_CD , A.MERCHANT_CITY AS CITY , A.MERCHANT_COUNTRY AS COUNTRY , D.CRDMEM_ACCT_NBR , %CurrentDateIn , 'U' AS TXN_STATUS FROM PS_EX_AIR_TKT_HDR A , PS_EX_AIR_TKT_DTL B , PS_CC_CARD_DATA_EX D , PS_EX_EE_ORG_DTL E , PS_SET_CNTRL_REC F , PS_EX_CCET_MAP_TBL G , PS_EX_CURRENCY_MAP H , PS_EX_EM_MAP_TBL L WHERE B.CC_TRXID = A.CC_TRXID AND D.CREDIT_CARD_VENDOR = A.CARD_TYPE AND D.CRDMEM_ACCT_NBR = A.CC_ACCT_NUM AND D.EMPLID = E.EMPLID AND E.DFLT_EE_PROF_FLG = 'Y' AND E.EE_VAL_FLG = 'Y' AND F.RECNAME='EX_CCET_MAP_TBL' AND F.SETCNTRLVALUE = E.BUSINESS_UNIT_GL AND G.CC_TO_ET_GRP = 'AIR' AND G.SETID = F.SETID AND G.DATA_SOURCE_EX='SAB' AND H.DATA_SOURCE_EX = 'SAB' AND H.CURRENCY_ABRV = A.SOURCE_CURR_CODE AND L.SETID=F.SETID AND A.VENDOR_TYPE=L.CC_TO_EM_CD AND L.DATA_SOURCE_EX='SAB'

# 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 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
5 EXPEND_MTHD Character(3) VARCHAR2(3) NOT NULL Payment Type
6 EXPENSE_TYPE Character(7) VARCHAR2(7) NOT NULL Expense Type
7 RECEIPT_VERIFIED Character(1) VARCHAR2(1) NOT NULL Receipt Verified
8 AIRFARE_RCPT_NBR Character(16) VARCHAR2(16) NOT NULL Airfare Receipt Number
9 AIR_TKT_RECON_STAT Character(1) VARCHAR2(1) NOT NULL Airline Ticket Reconciliation Status
C=Cancelled
N=Not Reconciled
R=Reconciled
10 MERCHANT Character(40) VARCHAR2(40) NOT NULL Merchant
11 TRANS_DT Date(10) DATE Transaction Date
12 TXN_AMOUNT Signed Number(28,3) DECIMAL(26,3) NOT NULL Transaction Amount
13 TXN_CURRENCY_CD Character(3) VARCHAR2(3) NOT NULL Transaction Currency
14 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.
15 CURRENCY_CD Character(3) VARCHAR2(3) NOT NULL Currency Code
16 CITY Character(30) VARCHAR2(30) NOT NULL City
17 COUNTRY Character(3) VARCHAR2(3) NOT NULL Country
18 CRDMEM_ACCT_NBR Character(44) VARCHAR2(44) NOT NULL Cardmember Number
19 TXN_LOAD_DATE Date(10) DATE Transaction Load Date
20 TXN_STATUS Character(1) VARCHAR2(1) NOT NULL Transaction Status
A=Assigned
D=Marked for Delete
R=Rejected
T=All Transaction Statuses
U=Unassigned