Working with External Expense Data

This chapter provides an overview of external expense data and discusses how to:

Load external expense data.
Load credit card data.
Handle credit card errors.
Load benchmark data.
Load airline ticket data from vendors.

Understanding External Expense Data

Organizations are simplifying the administrative operations that are associated with expense reimbursement. Expenses accepts and processes data from outside sources, such as credit card vendors and benchmark data distributors. You can use these external sources to provide expense data electronically, which reduces the data entry demands on employees. Using electronic data saves time in submitting and approving expenses, and helps monitor the legitimacy of expenses.

For successful expense processing, data files are already formatted properly for staging and loading. Depending on the data source, staging processes differ; however, you use the Load External Data Application Engine process (EX_DATA_LOAD) to load external data.

Loading External Expense Data

PeopleSoft Process Scheduler runs the Load External Data Application Engine process at user-defined intervals. This process drives all external data loading programs. Depending on the data source, the Load External Data process runs the appropriate program.

This section discusses how to load external data into Expenses.

Page Used to Load External Expense Data

Page Name	Object Name	Navigation	Usage
Load External Data	RUN_EXDATALD	Travel and Expenses, Load External Information, Load External Data Sources	Load data from outside sources into Expenses tables.

Loading External Data into Expenses

Access the Load External Data page.

Load External Data

Select to load data files that you specify in the File Name field into Expenses tables. The EX_LOAD Application Engine process loads valid data into the EX_TRANS table and loads invalid data into vendor-specific error tables.

Reload Corporate Card Errors

Select to have the EX_LOAD Application Engine process reload corrected credit card data from the EX_STG_ERR table.

Note. Before selecting this option, use the Review Corporate Card Errors page to select transactions to reload.

See Handling Credit Card Errors.

Data Source

Select a source. Options are:

American Express: Loads credit card transactions from American Express.

Diners Club: Loads credit card transactions from Diners Club.

Note. Diners Club has two data sources. When you select Diners Club, the system displays two File Name fields in which you enter TRANS.DAT and ACCOUNT.DAT file names and locations. You must enter TRANS.DAT and its location on the same line.

MasterCard: Loads credit card transactions from MasterCard.

US Bank: Loads credit card transactions from US Bank.

Visa: Loads credit card transactions from Visa International.

File Name

Enter the path and name of the file that contains the data that you want to process.

Loading Credit Card Data

This section provides an overview of credit card data loads and discusses how to:

Load data from American Express.
Load data from Diners Club.
Load data from MasterCard.
Load data from US Bank.
Load data from Visa International.

Understanding Credit Card Data Loads

Expenses delivers out-of-the-box functionality to support interfaces with five credit card vendors to populate expense accounting lines with credit card data. You must work with your credit card vendor to determine how often data is available for downloading and in what file format it comes.

After you establish a relationship with a credit card vendor, you must set up that company as a vendor in the PeopleSoft system; this establishes a vendor ID number, address, and bank information. Expenses enables you to pay vendors directly if the vendor is appropriately set up in the accounts payable system. If you purchase Expenses as a standalone product, it is delivered with common accounts payable objects that allow the set up to occur as well as a pay cycle component, which enables you to pay vendors and employees. Use the same functionality to set up new credit card vendors.

Warning! You must set up corporate card vendors using these card issuer names: AMEX for American Express, VISA for Visa International, DINER for Diners Club, MC for MasterCard, and USB for US Bank. If you do not use these values for the card issuer names, the EX_LOAD_[vendor] Application Engine process will not load credit card data.

After you establish the credit card vendor in your system, you must link the vendor to a payment type in Expenses. The payment type identifies who is reimbursed for the expense transaction. For example, for the payment type AMX, set up the system to reimburse the vendor American Express.

Each credit card vendor has unique codes to represent each type of transaction. These codes represent the type of expense incurred, the currency in which the expense was incurred in and to which it was translated, and the location where or country in which the transaction occurred. Expenses also has codes, or fields, that represent the same data elements. The data elements in the credit card vendor file must be mapped to the corresponding data elements in Expenses. You must:

Map credit card vendor codes to corresponding payment types in Expenses.
Map credit card vendor merchant category codes to corresponding merchant category codes in Expenses.
Map credit card vendor expense types to corresponding expense types in Expenses.
Map credit card vendor cash advance codes to corresponding cash advance codes in Expenses.
Map credit card vendor currency codes to corresponding currency codes in Expenses.
Map credit card vendor country names to corresponding country codes in Expenses.
Map credit card vendor data source locations to corresponding expense location codes in Expenses.

After you set up the credit card vendor and map the data elements, set up employees with valid credit card numbers using the employee profile component on the Corporate Card Information page. You can enter the credit card data for employees manually, or the system can populate the data fields directly from the PeopleSoft Human Resources database.

When you complete the vendor and employee setup and map the data elements, you can begin accepting data from the credit card vendor. The credit card data-load routes the transactions from the company interface to the business traveler based on employee ID, employee record number, corporate card type, and corporate card number. If errors result from incorrect mapping, fix the mapping and then reload the file. The credit card data-load functionality in Expenses:

Stages and loads credit card data.
Notifies employees of existing charges and overdue charges in My Wallet.
Provides employee access to transactions in My Wallet within an expense report and a cash advance to populate data at the transaction level.
Manipulates corporate changes through My Wallet entry.
Processes credits and refunds.

Loading Data from American Express

Access the Load External Data page.

To load data from American Express:

Select a data source of American Express.
In the File Name field, enter the path and name of the American Express data file.

Entering a file name and path is optional if you reload credit card data.
Select Load External Data.

American Express delivers multiple transaction types in a record called KR-1025, but Expenses uses only transaction type 1.

Process Scheduler runs the Load External Data Application Engine process that drives the staging process (EX_PUB_AMEX) and the loading process (EX_LOAD_AMEX). The staging process publishes data and subscribes to a message that populates the staging table (EX_AMEX_STG). The loading process then:

Checks for invalid values and loads errors into the EX_AMEX_STG_ERR table.
Completes the load process by loading data into the EX_TRANS table.

American Express KR-1025 - Transaction Type 1

When you import credit card data from American Express, the input file is formatted as shown in this table:

Field Name	Type	Length	Comments
Record Type	Char	1	Record Type. 1: Detail Billing Data.
Requesting Control Account Number	Char	19	Account number that is requesting the report.
Requesting Control Account Name	Char	35	Name that is associated with the account that's requesting the report.
Billing Basic Control Account Number	Char	19	Billed basic control account (BCA) for the account number that is billed.
Billing Basic Control Account Name	Char	35	Name that is associated with the billed BCA for the billed account number.
FIPS Code	Char	4	Internal U.S. Federal Agency grouping.
Billing Account Number	Char	19	Billed account number. Can be a CM or a diverted account.
Cardmember Embossed Name	Char	26	Name of the card member who incurs the charge.
Employee ID	Char	10	Client-assigned text field.
Cost Center	Char	10	Client-assigned text field.
Universal Number	Char	25	Client-assigned text field.
Social Security Number	Char	10	Client-assigned text field.
Language Preference Code	Char	1	Language preference of the card member for American Express correspondence. For example: ' ': English; for U.S. corporate (default) '0': English '1': French '2': Spanish '3': Portuguese
Corporate Identifier Number	Char	19	Number that represents a corporate client.
Supplier Ref Number	Char	11	Number that represents the supplier.
Sign Indicator	Char	1	Positive (+) or negative (-) amount indicator.
Billed Amount	Nbr	15,0	Amount that was charged.
Billed Tax Amount	Nbr	15,0	Tax amount.
Billed Currency Code	Char	3	International Standards Organization (ISO) code for the billed currency.
Billed Decimal Place Number	Char	1	Number of decimal places (one through nine) for the billing currency. For example, 1 is one decimal place.
Local Charge Amount	Nbr	15,0	Charge amount in local currency.
Local Tax Amount	Nbr	15,0	Tax amount in local currency.
Local Currency Code	Char	3	ISO code for the local currency.
Local Decimal Place Number	Char	1	Number of decimal places (one through nine) for the local currency. For example, 1 is one decimal place.
Currency Exchange Rate	Nbr	15,8	Exchange rate from the local currency to U.S. dollars.
Transaction Type Code	Char	2	Type of charge or credit for a transaction. Values are: 01: Adjustment 02: Remittance Attention 03: Remittance Regular 04: Life Insurance Premium 05: Returned Check Remittance 06: Annual Fee 07: Deferred 08: Regular Charges 09: Fee Reversal 10: Charge Write-off 11: Delinquency Charge Adjustment 12: Delinquency Charge 13: Air Insurance Charge 14: Open Balance Correction
Financial Category Code	Char	1	Financial category of transaction. Values are: 01: New Charge 02: Other Debits 03: Debit Remittance 04: Payment 05: Other Credit
Account Type	Char	5	Type of card member account. Values are CM, PREV, SUPP, BTA, BTBA, ACB, FCB, CRCB, TC, BEMYG, CACB.
Originating Base Control Account Number	Char	19	Originating BCA for the originating CM account number in diverted transactions.
Originating CM Account Number	Char	19	Originating CM account number for the actual charge in diverted transactions.
Transaction Number	Char	15	This field consists of the three fields that follow it.
Charge Date	Char	8	Date on which the transaction was charged to the account.
Business Process Date	Char	8	Date on which American Express processed the transaction.
Bill Date	Char	8	Date on which the transaction was billed.
CM Reference Number	Char	17	Field that the CM defines at the point of sale.
MIS Industry Code	Char	2	MIS industry code for the transaction. Values are: 01: Airline 02: Rail 03: Lodging 04: Car rental 05: Restaurant 06: Retail 07: Car miscellaneous 08: All other CA: Cash advance DC: Delinquency FE: Fees IN: Insurance NG: Returned check OA: Other adjustment OI: Oil PA: Payment TC: Telecommunication CT: Corporate TC NF: Nonfinancial SP: Stop payment
ROC ID (record of charge ID)	Char	13	Uniquely identifies a record of charge (ROC).
Charge Description Line 1	Char	42	First line of description for the ROC.
Charge Description Line 2	Char	42	Second line of description for the ROC.
Charge Description Line 3	Char	42	Third line of description for the ROC.
Charge Description Line 4	Char	42	Fourth line of description for the ROC.
Charge Description Line Other	Char	206	Other line of description for redefines.
SE Number	Char	10	Establishment Services account number for the SE that incurred the charge.
SE Chain Code	Char	10
SE Name 1	Char	40	First SE name field.
SE Name 2	Char	40	Second SE name field.
SE Street 1	Char	40	First SE address line.
SE Street 2	Char	40	Second SE address line.
SE City	Char	40	SE's city name.
SE State	Char	6	SE's state code.
SE Zip Code	Char	15	SE's ZIP code.
SE Country Name	Char	35	SE's country name.
SE Country Code	Char	3	ISO country code.
SE Corporate Status Code	Char	1	Corporate status. Values are: S: Sole proprietorship P: Partnership C: Corporation None: Unidentified
SE Purchasing Card Code	Char	2	Purchasing card and nonpurchasing card SEs. 00: Non-U.S. accounts. 01: Set up at client's request, no additional data capture. 02: Set up at client's request, no additional data capture. 03: Set up not at client's request, no additional data capture. 04: Set up without client request. 05: Set up at client's request, no additional data capture. 06: Set up at client's request. 07: Set up without client request, no additional data capture. 08: Set up without client request.
SE Purchasing Card Owner Type Code	Char	2	Identifies minority status. Values are: 00: Unknown 01: African American 02: Hispanic American 03: Native American 04: Asian Indian 05: Asian American 06: Nonethnic 07: Disabled 08: Hawaiian 09: All other minorities
SIC Code (Standard Industrial Classification code)	Char	4	Standard industry code.
SE Sales Tax Collected Flag	Char	1	Indicates whether sales tax was collected.
Ship To Postal Code	Char	15	Postal code shipped to.
Ship To City	Char	30	City name shipped to.
Ship To County	Char	30	County name shipped to.
Ship to State	Char	6	State code shipped to.
DDA Number	Char	22	Funds Access checking account number.
Bank Routing Number	Char	4	Routing number for the bank.
US CM ABA Number	Char	4	Number that identifies the domestic bank.
Spool Number	Char	22	Internal American Express number that identifies the transaction spooling order.
Funds Access Log Date	Char	8	Date that American Express logged the transaction.
Funds Access Log Time	Char	8	Time that American Express logged the transaction.
Machine Number	Char	8	Unique number that identifies the automated teller machine (ATM) from which a transaction originated.
Terminal Location	Char	40	ATM location.
Network Route and Transit Number	Char	10
Terminal Route and Transit Number	Char	10
Cash Batch Number	Char	6	Internal number that identifies the type of transaction for cash transactions.
Filler	Char	630	Filler.

Loading Data from Diners Club

Access the Load External Data page.

Select and enter these parameters:

Select a data source of Diners Club.
In the File Name field, enter the path and name of the transaction record data file from Diners Club. You must enter the path and name for the transactional record first.
In the second File Name field, enter the path and name for the account information data file from Diners Club.
Select Load External Data.

Diners Club delivers information in four files, but Expenses uses only two of them:

TRANS.DAT delivers transaction records.
ACCOUNTS.DAT delivers account information.

Process Scheduler runs the Load External Data Application Engine process, which drives the staging process (EX_PUB_DC) and the loading process (EX_LOAD_DC). The staging process publishes data and subscribes to a message that populates the staging table (EX_DC_STG). The loading process:

Checks for invalid values and loads errors into the EX_DC_STG_ERR table.
Completes the load process by loading data into the EX_TRANS table.

ACCOUNT.DAT File

When you import credit card data from Diners Club, the ACCOUNT.DAT input file is staged as shown in this table:

Field Name	Type	Length
Account Number	Character	19
Cardholder Name	Character	50
Last Name	Character	30
First Name	Character	26

TRANS.DAT File

When you import credit card data from Diners Club, the TRANS.DAT input file is formatted as shown in this table:

Field Name	Type	Length
Transaction Number	Character	17
Record Type	Character	1
Date Record Updated	Date-YYYYMMDD	8
Franchise Code	Character	25
Account Number	Character	19
Link Account Number	Character	19
Balance Account Number	Character	19
Transaction Date	Date-YYYYMMDD	8
Billing Date	Date-YYYYMMDD	8
Posting Date	Date-YYYYMMDD	8
Transaction Amount	Numeric w/Dec	16
Debit/Credit Flag	Logical T/F	1
Currency Code Description	Character	20
ISO Currency Code	Character	3
Charge Type	Character	20
Billing Level	Character	2
Description 1	Character	35
Description 2	Character	35
Description 3	Character	35
Description 4	Character	35
Establishment Number	Character	15
Establishment Name	Character	35
Establishment Address 1	Character	35
Establishment Address 2	Character	35
Establishment Address 3	Character	35
Establishment Address 4	Character	35
Establishment City/Town	Character	26
Establishment State	Character	20
Establishment Zipcode	Character	11
Establishment Country	Character	30
Establishment Phone Number	Character	20
DCI Country Flag	Logical T/F	1
SIC Code Description (Standard Industrial Classification code description)	Character	30
SIC Code	Character	4
Merchant Specific Code	Character	30
Intes Code	Character	30
DCI Charge Type	Character	30
Foreign Flag	Logical T/F	1
Original Transaction Amount	Numeric w/Dec	16
Original Currency Description	Character	20
ISO Currency Code	Character	3
Tax 1 Amount	Numeric w/Dec	16
Tax 2 Amount	Numeric w/Dec	16
Reference Number	Character	12
Record Of Charge/Ticket Number	Character	13
Dispute Flag	Logical T/F	1
Dispute Original Bill Date	Date-YYYYMMDD	8
Memo Billed Flag	Logical T/F	1
Rewards Flag	Logical T/F	1
General Ledger Number	Character	5
Indicative Data 1	Character	10
Indicative Data 2	Character	10
Indicative Data 3	Character	10
Indicative Data 4	Character	10
Indicative Data 5	Character	10
Indicative Data 6	Character	10
User Defined Information 1	Character	25
User Defined Information 2	Character	25
User Defined Information 3	Character	25
User Defined Information 4	Character	25
User Defined Information 5	Character	25
User Defined Information 6	Character	25
User Defined Information 7	Character	25
User Defined Information 8	Character	25
Supplemental Information	See Below	254

Loading Data from MasterCard

Access the Load External Data page.

Select and enter these parameters:

Select a data source of MasterCard.
In the File Name field, enter the path and name of the MasterCard data file.
Select Load External Data.

MasterCard produces multiple transaction types with Common Data Format 2.0 file. Expenses uses:

Transaction type 4300 – Account Address Maintenance Record
Transaction type 5000
- Addendum type 0 – Financial Transaction Record
- Addendum type 2 – Passenger Transport Addendum Record
- Addendum type 3 – Lodging Addendum Record

Process Scheduler runs the Load External Data Application Engine process, which drives the staging process (EX_PUB_MC) and the loading process (EX_LOAD_MC). The staging process publishes data and subscribes to messages that populate these staging tables that correspond to MasterCard transaction types:

EX_MC_4300
EX_MC_5000_0
EX_MC5000_2
EX_MC5000_3

The loading process:

Combines EX_MC_XX staging tables into one table (EX_MC_STG).
Checks for invalid values and loads errors into the EX_MC_STG_ERR table.
Completes the load process by loading data into the EX_TRANS table.

When you import credit card data from MasterCard, your input files are formatted as shown in these tables, depending on your version of Common Data Format.

4300 - Account Address Maintenance Record

Field Name	Type	Length	Comments
RecordIdentifier	Char	4	Identifies the record type.
AddendumType	Char	3	Filler for sorting (addendum type).
AccountNumber	Char	19	Account number that the corporation owns.
CardholderName1	Char	35	If the account has the capability to perform merchant transactions, this field contains the cardholder's name.
CardholderName2	Char	35	Optional descriptive name of the cardholder.

5000 (0) - Financial Transaction Record

Field Name	Type	Length	Comments
RecordIdentifier	Char	4	Identifies the record type.
IssuerICA	Number	5	Issuer's ICA number.
IssuerNumber	Char	11	Identifies the issuer along with the ICA number (blank - filled right justified).
CorpNumber	Char	19	Identifies the corporation to the issuer (blank-filled right justified).
AddendumType	Char	3	Code to identify the addendum type. For example, 0 - Financial Transaction - not an addendum, 01 - Financial User Amount Addendum, 02 - Financial Cost Allocation, 03 - Split Transaction, 031 - Split User Amount (not implemented), 032 - Split Cost Allocation, 1 - Purchasing Card, 11 - Purchasing Card User Amount Addendum, 12 - Purchasing Card Cost Allocation, 3 - Lodging, 4 - Car Rental, 5 - Generic, 6 - Fleet Card, 61 - Fleet Card Item, 7 - Merchant Description, 2 - Passenger Transport, and 21 - Passenger Transport Leg. Blank-filled left justified.
ProvidingEffectiveDate	Char	17	Original date and time stamp (in YYYYMMDD HH24:MI:SS format).
ReservedField	Char	6	This field is reserved for internal use and should be kept blank.
MerchantActivity Indicator	Char	1	Indicates whether a transaction is a merchant transaction or an account adjustment
AccountNumber	Char	19	Account number that the corporation owns.
MCTransReference Number	Char	10	This MasterCard-assigned transaction reference number is passed down to the desktop applications. This field should be kept blank by the input source.
AcquirerReference Number	Char	23	In case of adjustment activities, this stores the processor's unique identifier for the adjustment.
RecordType	Char	1	Reversal transaction if R, original if blank.
TransactionType	Char	1	These values are available when the MerchantActivityIndicator value is Merchant: 5: Retail Sale 6: Credit 7: Cash Disbursements 8: Unique 9: ATM These values are available when MerchantActivityIndicator value is Activity: 1: Corporate Level Activity 2: Organization Point Activity 3 Account Level Activity
DebitCreditIndicator	Char	1	Indicates debit or credit.
TransactionAmount	Number	16,4	Transaction amount.
PostingDate	Char	8	Date (in YYYYMMDD format) on which the transaction was posted to the cardholders account.
TransactionDate	Char	8	Date (in YYYYMMDD format) on which the transaction took place.
ProcessingDate	Char	8	The date (in YYYYMMDD format) that comes in on the DPFheader. If this is not present, it should be taken from the 1000 Customer Header record.
MerchantName	Char	25	Merchant's name. Required if MerchantActivityIndicator is M.
MerchantCity	Char	13	Merchant's city. Required if the MerchantActivityIndicator value is M.
MerchantStateProvince	Char	3	Merchant's state or province (or any geographic region equivalent of a U.S. state). Either this field or field #41, MerchantLocationState-ProvinceCode, must be required. Required if the MerchantActivityIndicator value is M.
MerchantCountry	Char	3	Merchant's country. Required if the MerchantActivityIndicator value is M.
MerchantMCC	Char	4	MasterCard-assigned code to the merchant. Required if the MerchantActivityIndicator value is M.
AmountInOriginal Currency	Number	16	Amount in original currency.
OriginalCurrencyCode	Char	3	Original currency code.
CurrencyConversion Date	Char	5	Currency conversion date (in Julian format, YYJJJ).
PostedCurrencyCode	Char	3	Posted currency code.
INETConversionRate	Number	16,7	INET conversion rate (signed decimal).
INETConversionExponent	Number	1	INET conversion exponent.
AcquiringICA	Number	4	MasterCard-assigned bank identification number.
CustomerCode	Char	17	Code that the corporation assigns to the merchant.
SalesTaxAmount	Number	16,4	Tax paid on goods purchased.
SalesTaxCalcIndicator	Char	1	MasterCard calculates the sales tax if the external source does not supply it. In such an event, the Sales Tax calculation indicator has these values: 0: Estimated tax not calculated. 1: Destination ZIP code used. 2: Merchant ZIP code used. 3: Merchant city used. 4: Information supplied at point of sale. 5: Unable to calculate estimated sales tax. 6: Tax exempt. 7: Cardholder province used. 8: Merchant state used.
SalesTaxRatePct	Number	5	The percentage that is used to calculate sales tax. This field is populated for outbound distribution. The provider system performs sales tax computation if the sales tax calculation indicator indicates that a sales tax needs to be calculated. The decimal is implied.
FreightAmount	Number	16,4	Freight charges portion of the total amount.
DestinationPostalCode	Char	10	Postal code where purchased goods are delivered.
MerchantType	Char	4	Merchant's business in relation to the corporation. Column 1: Business 0: Unknown 1: Corporation 2: Other Business Structure Column 2: Classification 0: Unknown 1: No Applicable Classification (Not female or handicapped) 2: Female Business Owner 3: Physically Handicapped Female Business Owner 4: Physically Handicapped Business Owner Column 3: Certification 0: Unknown 1: Not Certified as Such 2: SBA Certification as Small Business 3: SBA Certification as Small Disadvantaged Business 4: Other Government or Agency Recognized Certification 5: Self Certified Small Business
MerchantType (continued)	Char	4	Column 4: Ethnic Group 0: Unknown 1: African American 2: Asian Pacific American 3: Subcontinent Asian American 4: Hispanic American 5: Native American Indian 6: Native Hawaiian 7: Native Alaskan 8: Caucasian 9: Other
MerchantLocationPostalCode	Char	10	Merchant's postal code where the purchase was made.
DutyAmount	Number	16,4	Import duty portion of the total amount.
MerchantTaxId	Char	15	Merchant's tax identification number.
MerchantLocationState-ProvinceCode	Char	3	State code of the merchant's location where the purchase was made.
ShipFromPostalCode	Char	10	Postal code from where purchased goods are shipped.
AlternateSalesTax Amount	Number	16,4	In some countries, there is more than one value added tax.
DestinationCountry Code	Char	3	Country code where purchased goods are delivered.
MerchantReference Number	Char	17	Merchant's reference number.
AlternateTaxIndicator	Char	1	Indicates whether the alternate tax amount is included in the purchase amount.
AlternateTaxIdentifier	Char	15	An additional number used to identify the merchant to a taxing entity.
SalesTaxCollectedAt POSIndicator	Char	1	Indicates whether the sales tax amount is included in the transaction amount. This field must have a value if the AlternateTaxIdentifier field has a value.
AddendumDetailIndicator	Char	1	A system indicator of the additional data that is captured beyond the financial record.
MerchantId	Char	15	INET merchant ID.
AdjustmentReasonCode	Char	5	Type of adjustment.
AdjustmentDescription	Char	40	Free-formatted field to describe the activity.
TransMaintAction	Char	1	A: Add
InputFileRecordNumber	Number	6	This field is populated internally when the CDF is converted from the DPF. It contains the record number of the corresponding record in the DPF. When the input source gives a file in CDF format, the source should populate this field by the record number.
ReserveField	Char	6	Outbound error code. The Provider system formats this field if the record is rejected back to the input source (processor or issuer).

5000 (2) - Passenger Transport Addendum Record

Field Name	Type	Length	Comments
RecordIdentifier	Char	4	Record type.
IssuerICA	Number	5	ICA number of the issuer.
IssuerNumber	Char	11	The issuer along with the ICA number (blank-filled, right-justified).
CorpNumber	Char	19	The corporation to the issuer (blank-filled, right-justified).
AddendumType	Char	3	Code to identify the addendum type. For example: 0 - Financial Transaction - not an addendum 01 - Financial User Amount Addendum 02 - Financial Cost Allocation 03 - Split Transaction 031 - Split User Amount (not implemented) 032 - Split Cost Allocation 1 - Purchasing Card 11 - Purchasing Card User Amount Addendum 12 - Purchasing Card Cost Allocation 3 - Lodging 4 - Car Rental 5 - Generic 6 - Fleet Card 61 - Fleet Card Item 7 - Merchant Description 2 - Passenger Transport, and 21 - Passenger Transport Leg. Blank-filled left-justified.
ProvidingEffectiveDate	Char	17	Original date and time stamp (in YYYYMMDD HH24:MI:SS format).
ReservedField	Char	6	This field is reserved for internal use and should be kept blank.
PassengerName	Char	25	Person to whom the ticket was issued.
DepartureDate	Char	8	Departure date (in YYYYMMDD format).
CityOfOrigin/Airport Code	Char	5	City of origin airport code.
TravelAgencyCode	Char	8	Code that is assigned to the travel agency.
TravelAgencyName	Char	25	Name of the travel agency that is issuing the ticket.
TicketNumber	Number	15	Ticket number.
CustomerCode	Char	64	Code that the cardholder gave the merchant.
IssueDate	Char	8	Date (in YYYYMMDD format) on which the ticket was issued to the customer.
IssuingCarrier	Char	4	Standard abbreviation for the airline or railway carrier that is issuing the ticket.
TotalFare	Number	16,4	Ticket amount.
TotalFees	Number	16,4	Amount of the fees that are associated with the ticket.
TotalTaxes	Number	16,4	Amount of the taxes that are associated with the ticket.
InputFileRecordNumber	Number	6	This field is populated internally when the CDF is converted from the DPF. It contains the record number of the corresponding record in the DPF. When the input source gives file in the CDF format, the source should populate this field by the record number.
ReserveField	Char	6	Outbound error code. The Provider system formats this field if the record is rejected back to the input source (processor or issuer).

5000 (3) - Lodging Addendum Record

Field Name	Type	Length	Comments
RecordIdentifier	Char	4	Record type.
AddendumType	Char	3	Code to identify the addendum type. For example: 0 - Financial Transaction - not an addendum 01 - Financial User Amount Addendum, 02 - Financial Cost Allocation 03 - Split Transaction 031 - Split User Amount (not implemented) 032 - Split Cost Allocation 1 - Purchasing Card 11 - Purchasing Card User Amount Addendum 12 - Purchasing Card Cost Allocation 3 - Lodging, 4 - Car Rental 5 - Generic 6 - Fleet Card 61 - Fleet Card Item 7 - Merchant Description 2 - Passenger Transport 21 - Passenger Transport Leg (Blank-filled, left-justified.
ArrivalDate	Char	8	Date (in YYYYMMDD format) on which the customer arrived.
DepartureDate	Char	8	Date (in YYYYMMDD format) on which the customer departed.

Loading Data from US Bank

Access the Load External Data page.

Select and enter these parameters:

Select a data source of US Bank.
In the File Name field, enter the path and name of the US Bank data files.
Select Load External Data.

US Bank produces multiple transaction types. Expenses uses transaction types 2, 5, and 10.

Process Scheduler runs the Load External Data Application Engine process, which drives the staging process (EX_PUB_USB) and the loading process (EX_LOAD_USB). The staging process publishes the data and uses subscription codes to populate these staging tables that correspond to US Bank transaction types:

EX_USB_02_INFO
EX_USB_05_INFO
EX_USB_10_INFO

The loading process:

Combines the EX_USB_INFO_XX staging tables into one table (EX_USB_STG).
Checks for invalid values and loads errors into the EX_USB_STG_ERR table.
Completes the load process by loading data into the EX_TRANS table.

When you import credit card data from US Bank, the input files are formatted as shown in these tables:

US Bank Record Type 2

Field Name	Type	Length	Description
Record ID	Char	2	Code designating cardholder information.
Cardholder Account Number	Char	16	Cardholder's account number.
Corporate Account Number	Char	16	If a corporate bill, shows the company's billing account number.
Social Security Number	Char	9	Social security number.
Cardholder Name	Char	25	Name (in First MiddleInitial Last format) as it is embossed on card.

US Bank Record Type 5

Field Name	Type	Length	Description
Record ID	Char	2	Code designating transaction information.
Account Number	Char	16	USB account number.
Reference Number	Char	23	Transaction reference number that the merchant assigns at transaction time.
TSYS Tran Code	Char	4	Detailed transaction code.
Transaction Date	Char	8	Date on which the transaction occurred.
Posting Date	Char	8	Date on which the transaction was posted.
Source Curr Code	Char	3	Currency code of the country in which the transaction occurred.
Source Currency Amt	Nbr	12	Amount of purchase in source currency. Two decimals are implied for all currencies.
Billing Curr Code	Char	3	Billing currency code (always U.S. dollars).
Billing Currency Amt	Nbr	12	Amount of purchase in billing currency.
SIC Code	Char	4	Standard industrial classification for merchant.
Merchant Name	Char	25	Merchant's name. For passenger itinerary transactions, the original ticket number is in positions 13 through 25 of this field.
Merchant City	Char	26	Merchant's city.
Merchant State	Char	3	Merchant's state or province code.
Merchant Country	Char	3	Merchant's country code.
Merchant Zip	Char	9	ZIP code where merchant is located.
Merchant VAT No./Single Business Ref (merchant value-added tax number/single business reference)	Char	20	The ID number that the taxing authority assigns to the merchant. Canada:Single Bus Ref no. (Purch Card)
Customer VAT No. (customer value-added tax number)	Char	13	ID that value-added tax (VAT) authorities assign to buying company. (Purch Card)
Summary Commodity Code	Char	4	European VAT requirement that describes the items in the purchase. Used for VAT reporting. (Purch Card)
Accounting Code	Char	35	General ledger account number.
Memo Post Flag	Char	1	Specifies if the transaction is a memo that is posted to this cardholder. Values are: Y: Memo Post N: Live Dollars, Not Memo Post
LocalTax Included Flag	Char	1	Indicates presence of state or provincial tax information. Values are: 0: No Tax Provided 1: Tax Provided
Local Tax Amount	Char	12	State, local and other tax for calling card transactions. Also used for total amount of state or provincial tax included in this transaction. In the U.S., this is sales tax.
National Sales Tax Included Flag	Char	1	Indicator of presence of national tax information. 0: No Tax Provided 1: Tax Provided
National Sales Tax	Char	12	This field is used for federal tax on calling card transactions. Also used for the total amount of national or VAT tax included in the transaction amount. The amount must be in the source currency.
Other Tax	Char	12	Total amount of all of the other taxes in this transaction, excluding local and national tax. Not applicable in U.S. (Purch Card only)
Purchase Identifier Flag	Char	1	Information type that is provided in the purchase identifier field. Values are: 1: Order Number 2: Customer Defined Data 3: Car Rental Agreement Number (Corp Card) 4: Hotel Folio Number (Corp Card)
Purchase Identifier	Char	25	Identifies purchase to issuer and cardholder. Contains customer reference identifier, including customer code or merchant order number. Used mainly for purchasing card.
Service Identifier	Char	6	Value that is assigned to identify the type of record.
Source Currency Exchange Rate	Nbr	13	Currency exchange rate. Six decimal positions. U.S. transactions hold an exchange rate of 1.
Filler	Char	83

US Bank Record Type 10

Field Name	Position	Length	Description
Record ID	Char	2	Code designating Transaction Detail for Airline Data
Account Number	Char	16	USB Account number.
TSYS Tran Code	Char	4	Detailed transaction code.
Departure Date	Char	8	The date on which travel begins.
Origination City/Airport Code	Char	3	IATA airport code for origination city.
Ticket Number	Char	13	For passenger itinerary trans, Merchant Name field provides the original ticket number in positions 13 through 25 (of these 25).
Passenger Name	Char	20	Name of passenger as indicated on ticket.
Restricted Ticket	Char	1	Code indicating a restricted (nonrefundable) ticket. (Not currently available.) Values are: 0: No restriction 1: Restricted (nonrefundable ticket)
Travel Agency Name	Char	25	Name of travel agency that supplied the ticket.
Travel Agency Code	Char	8	Unique code for the travel agency that supplied the ticket.
Number of Legs	Char	2	Number of legs of itinerary data that follow; up to 16.
Filler	Char	298

Loading Data from Visa International

Access the Load External Data page.

Select and enter these parameters:

Select a data source of Visa.
(Optional) In the File Name field, enter the path and name of the Visa International data file.
Select Load External Data.

Visa International produces multiple transaction types with InfoSpan software. Expenses uses transaction types 3, 4, 5, 9, and 14 from InfoSpan version 3.0 and 4.0.

Process Scheduler runs the Load External Data Application Engine process, which drives the staging process (EX_PUB_VISA) and the loading program (EX_LOAD_VISA). The staging process publishes the data and uses subscription codes to populate these staging tables that correspond to Visa transaction types:

EX_VISA_INFO_03
EX_VISA_INFO_04
EX_VISA_INFO_05
EX_VISA_INFO_09
EX_VISA_INFO_14

The loading process:

Combines the EX_VISA_INFO_XX staging tables into one table (EX_VISA_STG).
Checks for invalid values and loads errors into the EX_VISA_STG_ERR table.
Completes the load process by loading data into the EX_TRANS table.

When you import credit card data from Visa International, the input files are formatted as shown in these tables, depending on your version of InfoSpan:

InfoSpan Version 3.0 and 4.0 - Transaction Type 3

Field Name	Type	Length	Comments
Load Transaction Code	Nbr	1	Code that determines how the InfoSpan load function processes this transaction.
Cardholder Information	Char	20	Unique identification numbers, such as employee numbers, by which a client company identifies employees or cardholders.
Account Number	Nbr	19	Cardholder account number that appears on the front of the card as Base II transmits it.
Hierarchy Node	Char	40	Active hierarchy (organization chart) node to which the card account belongs. A card account can be associated with only one hierarchy node for a specific period.
Effective Date	Nbr	8	Date on which the relation with the new node becomes effective after a change. The relation with the old hierarchy node ends the day before the effective date. The system ignores the effective date if the new hierarchy node is the same as the old one in InfoSpan.
Account Open Date	Nbr	8	Date on which the cardholder account was opened and on which the relation with a hierarchy node starts for a new account.
Account Close Date	Nbr	8	Date on which the cardholder's account closed.
Card Expire Date	Nbr	8	Expiration date of the card account.
Card Type	Nbr	1	Code that identifies the type of Visa card. Note. Purchasing card includes Fleet Service.
Spending Limit	Nbr	16	Spending limit that is permitted for the purchasing account in a billing period.
Statement Type	Nbr	1	Code that indicates the billing frequency.
Last Revision Date	Nbr	8	Date on which the issuer last reviewed this account.
Transaction Spending Limit	Nbr	16	Maximum amount that is allowed per transaction for a purchasing card account.
Corporation Payment Indicator	Nbr	1	Values are Yes (the client company pays the bill) and No (the cardholder pays).
Billing Account Number	Nbr	19	Card account to which the transactions are billed.
Cost Center	Char	50	Default cost center for an account.
G/L Sub-account	Char	76	Default general ledger sub-account for an account.
Transaction Daily Limit	Nbr	8	Represents the maximum number of transactions that can be charged to a card account in a day.
Transaction Cycle Limit	Nbr	8	Represents the maximum number of transactions that can be charged to a card account in a billing cycle.
Cash Limit Amount	Nbr	16	Represents the maximum amount of cash advance transactions that can be charged to the account in a billing cycle.
Status Code	Nbr	2	Status of a card account.
Reason Status Code	Nbr	2	Reason for a change in card account status.
Status Date	Nbr	8	Date on which the card account status changed.
Pre-funded Indicator	Nbr	1	Indicates if the card account is prefunded.
City Pair Program Indicator	Nbr	1	Indicates that the card account is subject to a City Pair Program.
Task Order Number	Char	26	Specifies and authorizes required products and services and the negotiated price for them.
Fleet Service Indicator	Nbr	1	Indicates that the card account can receive Fleet Service data.

InfoSpan Version 3.0 and 4.0 - Transaction Type 4

Field Name	Type	Length	Comments
Load Transaction Code	Nbr	1	Code that determines how the InfoSpan load function processes this transaction.
Company Identification	Nbr	10	Issuer-assigned company identification number. It identifies multiple companies in an issuer's portfolio.
Cardholder Information	Char	20	Unique identification numbers, such as employee numbers, by which a client company identifies employees or cardholders.
Hierarchy Node	Char	40	Number that represents the active hierarchy (organization chart) node to which the card account belongs. A card account can be associated with only one hierarchy node for a specific period.
First Name	Char	20	Cardholder's first name.
Last Name	Char	20	Cardholder's last name.
Address Line 1	Char	40	First line of cardholder's address.
Address Line 2	Char	40	Second line of cardholder's address.
City	Char	20	City where the cardholder works.
State/Province Code	Char	4	State or province code for where the cardholder works.
ISO Country Code	Nbr	5	ISO (International Organization for Standardization) code for the country in which the cardholder works.
Postal Code	Char	10	Postal code for the cardholder's business address. For cardholders in the U.S., it contains the ZIP code of the business address. For cardholders in other countries, it contains the country postal code.
Address Line 3	Char	40	Third line of cardholder's address.
Mail Stop	Char	14	Cardholder's physical location number at work.
Phone Number	Char	14	Cardholder's business telephone number.
Fax Number	Char	14	Cardholder's business fax number.
SSN Other ID (social security number other ID)	Char	20	Cardholder's social security number or other identifying number (other than the Cardholder Identification field).
Training Date	Nbr	8	Visa Cards training date for the cardholder.
E-mail Address	Char	128	Cardholder's email address.

InfoSpan Version 3.0 and 4.0 - Transaction Type 5

Field Name	Type	Length	Comments
Load Transaction Code	Nbr	1	Code that determines how the InfoSpan load function processes this transaction.
Account Number	Nbr	19	Cardholder account number that appears on the front of the card.
Posting Date	Nbr	8	Posting date of a transaction.
Transaction Reference Number	Char	24	Reference number for a transaction.
Sequence Number	Nbr	10	Transaction sequence number that the processor assigns during posting to uniquely identify a transaction within a posting run.
Period	Nbr	5	Billing period number that the issuer's processor assigns.
Acquiring BIN (acquiring bank identification number)	Nbr	6	Bank identification number of a supplier that is related to the transaction.
Card Acceptor ID	Char	26	Reserved for future use.
Supplier Name	Char	26	Supplier name that is included in a transaction.
Supplier City	Char	14	City of a supplier that is included in a transaction.
Supplier State/Province Code	Char	4	Code of a supplier state or province that is included in a transaction.
Supplier ISO Country Code	Nbr	5	ISO country code of the country in which the supplier is located. The system converts alphabetic ISO country codes to their numeric equivalents.
Supplier Postal Code	Char	14	Postal code of a supplier location that is included in a transaction.
Source Amount	Nbr	16	Supplier source amount of a transaction.
Billing Amount	Nbr	16	Amount to be billed or credited to the cardholder.
Source Currency Code	Nbr	5	Currency of the course amount of the transaction.
Merchant Category Code	Nbr	4	Four-digit numeric classification identifier, modeled after the Standard Industrial Classification code, designed to group suppliers that are in similar lines of business.
Transaction Type Code	Char	2	Code that identifies the type of account posting that results from a transaction.
Transaction Date	Nbr	8	Date of the transaction.
Billing Currency Code	Nbr	5	Currency in which the transaction amount is billed.
Tax Amount	Nbr	16	Sales tax amount included by point of sale (POS) supplier. For U.S. only.
Dispute Amount	Nbr	16	Amount that is in dispute for a transaction.
Dispute Reason Code	Nbr	1	Code that indicates the reason for a disputed transaction.
Dispute Date	Nbr	8	Date of disputed transaction.
Commodity Code	Char	4	Code that identifies a commodity for VAT purposes. For VAT, the commodity code means line item detail (LID) or summary tax data (SUMM).
Supplier VAT Number	Char	20	Supplier's VAT identification number.
Supplier Order Number	Char	50	Supplier's purchase order number for a transaction.
Customer VAT Number	Char	14	Customer's VAT registration number.
VAT Amount	Nbr	16	VAT amount of a transaction.
GST Amount	Nbr	16	Goods and services tax (GST) or harmonized sales tax (HST) amount for a transaction.
Purchase Identification Format	Char	2	Format of a purchase identification in a transaction.
Purchase Identification	Char	50	Identification code for POS transactions.

InfoSpan Version 3.0 and 4.0 - Transaction Type 9

Field Name	Type	Length	Comments
Load Transaction Code	Nbr	1	Code that determines how the InfoSpan load function processes this transaction.
Account Number	Nbr	19	Cardholder account number that appears on the front of the card as is transmitted by BASE II.
Posting Date	Nbr	8	Posting date of a transaction.
Transaction Reference Number	Char	24	Reference number of a transaction.
Sequence Number	Nbr	10	Transaction sequence number that the processor assigns during posting to uniquely identify a transaction within a posting run.
No Show Indicator	Nbr	1	Indicator of a transaction that results from a customer's failure to appear.
Check In Date	Nbr	8	Date that the customer checked into the hotel. This is the scheduled arrival date for a no show or an advance lodging reservation.
Daily Room Rate	Nbr	16	Daily rental rate for a hotel room.
Other Charges	Nbr	16	Additional charges as identified by the Lodging Extra Charges code in a lodging transaction. This includes no show charges in a transaction where the Lodging No Show indicator is Yes.
Total Tax Amount	Nbr	16	Total tax amount for a hotel lodging transaction.
Food/Beverage Charges	Nbr	16	Charges for food and beverages as a part of a hotel lodging transaction. This value does not include minibar charges.
Prepaid Expenses	Nbr	16	Total of any prepaid amounts or deposits applied against the folio.
Folio Cash Advances	Nbr	16	Total of cash advances that were charged on the folio against the folio.
Valet Parking Charges	Nbr	16	Valet parking charges as a part of a hotel lodging transaction.
Mini Bar Charges	Nbr	16	Total minibar food and beverage expenses.
Laundry Charges	Nbr	16	Laundry charges in a hotel lodging transaction.
Telephone Charges	Nbr	16	Telephone charges, including in-room fax machine usage.
Gift Shop Purchases	Nbr	16	Total of gift shop and merchandise purchases.
Movie Charges	Nbr	16	Total charges for movies and other in-room entertainment expenses.
Business Center Charges	Nbr	16	Total of copies, faxes, and other business center expenses.
Health Club Charges	Nbr	16	Total of health club or pool expenses, including court and equipment rental and golf green fees.
Extra Charge Code	Char	6	Type of additional charges to a bill after checkout. Each position may indicate a type of charge.
Total Room Tax Amount	Nbr	16	Amount of all room taxes for this transaction.
Lodging Nights	Nbr	2	Length of stay (in nights).
Total Non-Room Charges	Nbr	16	All non-room-related charges.
Message Identifier	Char	16	Message identifier in a transaction. This information helps match enhanced data with the credit card transaction.
Purchase ID	Char	50	Merchant-assigned number for the transaction. It can be a supplier order number, a folio number, or a car rental agreement number. This information is used to match enhanced data with the transaction.
Source Amount	Nbr	16	Amount (in the source currency) that was charged to the card account for the transaction. This information is used to match enhanced data with the transaction.
Transaction Date	Nbr	8	Date of the purchase, according to the financial record. This information is used to match enhanced data with the transaction.
Merchant Category Code	Nbr	4	Merchant category code for the transaction, according to the financial transaction. This information is used to match enhanced data with the transaction.
Supplier Name	Char	26	The name of the supplier, according to the financial transaction. This information is used to match enhanced data with the transaction.
Supplier Postal Code	Char	14	Postal code of a supplier, according to the financial transaction. This information is used to match enhanced data with the transaction.

InfoSpan Version 3.0 and 4.0 - Transaction Type 14

Field Name	Type	Length	Comments
Load Transaction Code	Nbr	1	Code that determines how the InfoSpan load function processes this transaction.
Account Number	Nbr	19	Cardholder account number appearing on the front of the card as it is transmitted by BASE II.
Posting Date	Nbr	8	Posting date of a transaction.
Transaction Reference Number	Char	24	Reference number of a transaction.
Sequence Number	Nbr	10	Transaction sequence number that the processor assigns during the posting process to uniquely identify a transaction within a posting run.
Departure Date	Nbr	8	Date on which the customer begins travelling.
Travel Agency Code	Char	8	Code that identifies the travel agency that supplied the ticket.
Travel Agency Name	Char	26	Name of the travel agency that supplied the ticket.
Ticket Indicator	Nbr	1	Indicates ticket restrictions.
Ticket Number	Char	14	Airline ticket number.
Passenger Name	Char	20	Customer name, as indicated on the ticket.
Exchange Ticket Number	Char	14	Number of the ticket for which a ticket was exchanged.
Exchange Ticket Amount	Nbr	16	Credit from tickets or coupons being used as payment in an exchange ticket transaction.
Internet Indicator	Nbr	1	Indicates if the ticket was purchased over the Internet.
Total Fare Amount	Nbr	16	Total fare for all legs of travel.
Total Fee Amount	Nbr	16	Sum of all applicable fees.
Total Tax Amount	Nbr	16	Sum of all taxes.
Message Identifier	Char	16	Message identifier in a transaction. This information is used to match enhanced data with the transaction.
Endorsements/Restrictions	Char	20
Purchase ID	Char	50	Merchant-assigned number for the transaction. It can be a supplier order number, a folio number, or a car rental agreement number. This information is used to match enhanced data with the transaction.
Source Amount	Nbr	16	Amount (in source currency) that was charged to the card account for the transaction. This information is used to match enhanced data with the transaction.
Transaction Date	Nbr	8	Date of the purchase, according to the financial record. This information is used to match enhanced data with the transaction.
Merchant Category Code	Nbr	4	Merchant category code for the transaction, according to the financial transaction. This information is used to match enhanced data with the transaction.
Supplier Name	Char	26	The name of the supplier, according to the financial transaction. This information is used to match enhanced data with the transaction.
Supplier Postal Code	Char	14	Postal code of a supplier, according to the financial transaction. This information helps match enhanced data with the transaction.

Handling Credit Card Errors

This section provides an overview of credit card error handling, lists common elements, and discusses how to:

Handle American Express errors.
Handle Diners Club errors.
Handle MasterCard errors.
Handle US Bank errors.
Handle Visa errors.
Reload credit card errors.

Understanding Credit Card Error Handling

During the Load External Data Application Engine process, the process checks for invalid values and loads errors into a credit card vendor-specific error table. For example, an error may be a transaction with a credit card number that is not assigned to an employee profile or an invalid transaction currency for the employee's country. Review and correct credit card errors that did not load successfully. On the vendor-specific errors page, select corrected transactions that you want to load the next time that you run the Reload Corporate Card Errors option on the Load External Data page.

Common Elements Used in This Section

Account Number	Employee's credit card account number.
Cardholder's Name	Employee to whom the corporate card is assigned.
Invalid EmpID (invalid employee ID)	Displays Y if the credit card error is due to an invalid employee ID that is associated with the transaction. To correct this error, use the Employee Profile - Corporate Card Information page to add a corporate account number to the profile.
Invalid Expense Type	Displays Y if the credit card error is due to an invalid expense type that is associated with the transaction. To correct this error, use the Expense Type - Corporate Card Mapping page to ensure that the credit card's MIS industry code (also known as merchant category group) is correctly mapped to an expense type in Expenses.
Invalid Monetary Currency	Displays Y if the credit card error is due to an invalid currency for the country in which the transaction occurred. To correct this error, use the Currency Code Mapping page to ensure that the credit card's currency code is correctly mapped to a currency code in Expenses.
Invalid Transaction Currency	Displays Y if the credit card error is due to an invalid currency for the employee's country. To correct this error, use the Currency Code Mapping page to ensure that the credit card's currency code is correctly mapped to a currency code in Expenses.
Reload Transaction	Select the transactions that you corrected and want to reload the next time that you run the Load External Data Application Engine process.
Transaction Number	Displays the unique identifier that is assigned to the expense transaction.

Pages Used to Handle Credit Card Errors

Page Name	Object Name	Navigation	Usage
Amex Card Errors (American Express card errors)	EX_AMEX_STG_ERR	Travel and Expenses, Load External Information, Review Corporate Card Errors	Review American Express credit card errors that did not load successfully. Select credit card transactions to load the next time that you run the Reload Corporate Card Errors option on the Load External Data page.
Diners Club Errors	EX_DC_STG_ERR	Travel and Expenses, Load External Information, Review Corporate Card Errors	Review Diners Club credit card errors that did not load successfully. Select credit card transactions to load the next time that you run the Reload Corporate Card Errors option on the Load External Data page.
Master Card Errors	EX_MC_STG_ERR	Travel and Expenses, Load External Information, Review Corporate Card Errors	Review MasterCard credit card errors that did not load successfully. Select credit card transactions to load the next time that you run the Reload Corporate Card Errors option on the Load External Data page.
US Bank Errors	EX_USB_STG_ERR	Travel and Expenses, Load External Information, Review Corporate Card Errors	Review US Bank credit card errors that did not load successfully. Select credit card transactions to load the next time that you run the Reload Corporate Card Errors option on the Load External Data page.
Visa Card Errors	EX_VISA_STG_ERR	Travel and Expenses, Load External Information, Review Corporate Card Errors	Review Visa credit card errors that did not load successfully. Select credit card transactions to load the next time that you run the Reload Corporate Card Errors option on the Load External Data page.
Load External Data	RUN_EXDATALD	Travel and Expenses, Load External Information, Load External Data Sources	Load data from outside sources into Expenses tables.

Handling American Express Errors

Access the Amex Card Errors page.

Card Errors Tab

MIS Industry Code

Displays the American Express code for the type of expense transaction. Expenses displays Y in the Invalid Expense Type field if this code is not mapped correctly.

Other Errors Tab

Billed Currency Code	Displays American Express code for the currency of the country in which the expense transaction occurred. Expenses displays Y in the Invalid Monetary Currency field if this code is not mapped correctly.
Local Currency Code	Displays American Express code for the currency of the employee's country. Expenses displays Y in the Invalid Transaction Currency field if this code is not mapped correctly.

Handling Diners Club Errors

Access the Diners Club Card Errors page.

Card Errors Tab

Standard Industry Code

Displays Diners Club code for the type of expense transaction. Expenses displays Y in the Invalid Expense Type field if this code is not mapped correctly.

Other Errors Tab

Billing Currency Code	Displays the Diners Club code for the currency of the country in which the expense transaction occurred. Expenses displays Y in the Invalid Monetary Currency field if this code is not mapped correctly.
Original Currency Code	Displays the currency code of the employee's country. Expenses displays Y in the Invalid Transaction Currency field if this code is not mapped correctly.

Handling MasterCard Errors

Access the Master Card Errors page.

Card Errors Tab

Merchant Type

Displays the MasterCard code for the type of expense transaction. Expenses displays Y in the Invalid Expense Type field if this code is not mapped correctly.

Other Errors Tab

Posted Currency Code	Displays the MasterCard code for the currency of the country in which the expense transaction occurred. Expenses displays Y in the Invalid Monetary Currency field if this code is not mapped correctly.
Original Currency Code	Displays the MasterCard code for the currency of the employee's country. Expenses displays Y in the Invalid Transaction Currency field if this code is not mapped correctly.

Handling US Bank Errors

Access the US Bank Card Errors page.

Card Errors Tab

Standard Industry Code

Displays the US Bank code for the type of expense transaction. Expenses displays Y in the Invalid Expense Type field if this code is not mapped correctly.

Other Errors Tab

Billing Currency	Displays the US Bank code for the currency of the country in which the expense transaction occurred. Expenses displays Y in the Invalid Monetary Currency field if this code is not mapped correctly.
Original Currency	Displays the US Bank code for the currency of the employee's country. Expenses displays Y in the Invalid Transaction Currency field if this code is not mapped correctly.

Handling Visa Errors

Access the Visa Card Errors page.

Card Errors Tab

Merchant Cat Cd (merchant category code)

Displays the Visa code that indicates the type of expense transaction. Expenses displays Y in the Invalid Expense Type field if this code is not mapped correctly.

Other Errors Tab

Visa Billing Currency	Displays the Visa code for the currency of the country in which the expense transaction occurred. Expenses displays Y in the Invalid Monetary Currency field if this code is not mapped correctly.
Visa Source Currency	Displays the Visa code for the currency of the employee's country. Expenses displays Y in the Invalid Transaction Currency field if this code is not mapped correctly.

Reloading Credit Card Errors

Access the Load External Data page.

When you are ready to load corrected data from American Express, Diners Club, MasterCard, Diners Club, US Bank, or Visa, follow these steps:

Select a data source of American Express, Diners Club, MasterCard, US Bank, or Visa.
Enter the path and file name for the American Express, Diners Club, MasterCard, US Bank, or Visa data.
Select Load External Data and Reload Corporate Card Errors.

Loading Benchmark Data

Expenses accepts benchmark data from two sources: Runzheimer International and the United States federal government. Benchmark data become expense location amounts for cost comparisons and expense management.

Runzheimer International is a consulting firm that distributes travel benchmark data used to establish spending averages and limits. The federal government provides expense rates for the continental United States (CONUS) and foreign countries (OCONUS).

This section discusses how to:

Load Runzheimer data.
Stage CONUS data.
Load CONUS data.
Stage OCONUS data.
Load OCONUS data.

Pages Used to Load Benchmark Data

Page Name	Object Name	Navigation	Usage
File Inbound	EO_FILE_INBOUND	Enterprise Components, Integration Definitions, Inbound File Rule	Set up inbound flat file processing. See PeopleSoft Enterprise Components 8.9 PeopleBook.
Inbound File	EO_FILETOMSG	Enterprise Components, Integration Definitions, Initiate Processes, Inbound File Publish	Initiate inbound flat file processing. This file-to-message processing function reads the file row set and publishes it as a message. See PeopleSoft Enterprise Components 8.9 PeopleBook.

Page Name

Object Name

Navigation

Usage

File Inbound

EO_FILE_INBOUND

Enterprise Components, Integration Definitions, Inbound File Rule

Set up inbound flat file processing.

See PeopleSoft Enterprise Components 8.9 PeopleBook.

Inbound File

EO_FILETOMSG

Enterprise Components, Integration Definitions, Initiate Processes, Inbound File Publish

Initiate inbound flat file processing. This file-to-message processing function reads the file row set and publishes it as a message.

See PeopleSoft Enterprise Components 8.9 PeopleBook.

Loading Runzheimer Data

Access the File Inbound page.

Select and enter these parameters:

Location	File Identifier	Inbound File	Definition Name	Message Name
United States	BENCHMARK_RNZ_USA_LOAD	BENCHMARK_RNZ_USA_DAT	BENCHMARK_RNZ_USA	BENCHMARK_RNZ_LOAD
Non-United States	BENCHMARK_RNZ_NONUSA_LOAD	BENCHMARK_RNZ_NONUSA_DAT	BENCHMARK_RNZ_NONUSA	BENCHMARK_RNZ_LOAD

After you set up the inbound file rule, access the Inbound File page to run the EOP_PUBLISHF Application Engine process, which publishes the data and uses subscription codes to populate the staging table (EX_BNCHMARK_TAO) and these temporary tables:

EX_BNCHMARK_MRK1_TAO
EX_BNCHMARK_MRK2_TAO
EX_BNCHMARK_MRK3_TAO
EX_BNCHMARK_MRK4_TAO

Subscription PeopleCode on the BENCHMARK_RNZ_LOAD message triggers a PeopleSoft Application Engine program that loads Runzheimer data into the EX_LOC_AMOUNT and EX_LOC_AMT_DTL tables.

Note. Because Runzheimer International provides seasonal data, there may be more than one row of data for an expense location amount. The loading process does not replace existing rows; it adds rows and uses the effective date to differentiate rows of the same expense location amount.

Runzheimer Data Format

When you import Runzheimer data, the comma-delimited input file is formatted as shown in this table:

Field Name	Type	Length	Comments
DATE_STRING	Char - Mixed	25	Date string.
CITY	Char - Mixed	30	City.
STATE	Char - Upper	6	State.
COUNTRY_FLD	Char - Upper	18	Country field.
COUNTRY_NAME	Char - Mixed	30	Country.
RPT_DT	Char - Upper	15	Report date.
FLAG_SEASONAL	Char - Upper	2	Seasonal indicator.
START_DT_JULIAN	Nbr	3	Julian start date.
END_DT_JULIAN	Nbr	3	Julian end date.
CURRENCY_ABRV	Char - Upper	5	Currency abbreviation.
DESCR	Char - Mixed	30	Description.
CUR_EXCHNG_RT	Nbr	7.8	Exchange rate.
AVG_BREAKFAST_AMT	Sign	13.2	Average breakfast amount.
LOW_BREAKFAST_AMT	Sign	13.2	Low breakfast amount.
HIGH_BREAKFAST_AMT	Sign	13.2	High breakfast amount.
AVG_LUNCH_AMT	Sign	13.2	Average lunch amount.
LOW_LUNCH_AMT	Sign	13.2	Low lunch amount.
HIGH_LUNCH_AMT	Sign	13.2	High lunch amount.
AVG_DINNER_AMT	Sign	13.2	Average dinner amount.
LOW_DINNER_AMT	Sign	13.2	Low dinner amount.
HIGH_DINNER_AMT	Sign	13.2	High dinner amount.
AVG_DLX_HOTEL_AMT	Sign	13.2	Average deluxe hotel amount.
LOW_DLX_HOTEL_AMT	Sign	13.2	Low deluxe hotel amount.
HIGH_DLX_HOTEL_AMT	Sign	13.2	High deluxe hotel amount.
AVG_1ST_HOTEL_AMT	Sign	13.2	First class hotel, average amount.
LOW_1ST_HOTEL_AMT	Sign	13.2	First class hotel, low amount.
HIGH_1ST_HOTEL_AMT	Sign	13.2	First class hotel, high amount.
AVG_ECON_HOTEL_AMT	Sign	13.2	Economy hotel, average amount.
LOW_ECON_HOTEL_AMT	Sign	13.2	Economy hotel, low amount.
HIGH_ECONHOTEL_AMT	Sign	13.2	Economy hotel, high amount.
STD_3MEAL_AMT	Sign	13.2	Standard three meal amount.
STD_PER_DIEM_AMT	Sign	13.2	Standard per diem amount
AMT1	Sign	23.3	Amount 1.
DESCR_LN1	Char - Mixed	50	Description line 1.
DESCR_LN2	Char - Mixed	50	Description line 2.
AMT2	Sign	23.3	Amount 2.
DESCR_LN3	Char - Mixed	50	Description line 3.
AMT3	Sign	23.3	Amount 3.
DESCR_LN4	Char - Mixed	50	Description line 4.
AMT4	Sign	23.3	Amount 4.
AMT5	Sign	23.3	Amount 5.
AMT6	Sign	23.3	Amount 6.
LODGING_TAX	Sign	7.8	Typical lodging tax.
START_OF_SEASON	Char	5	Start of season.
END_OF_SEASON	Char	5	End of season.

Loading CONUS Data

Access the File Inbound page.

Select and enter these parameters:

File Identifier	Inbound File	Definition Name	Message Name
CONUS_LOAD	CONUSNM.TXT	CONUS_DATA_FL	CONUS_LOAD

CONUS Data Format

When you import CONUS data, the input file is formatted as shown in this table:

Field Name	Type	Length	Comments
STATE	Char	60	State.
LOCALITY/CITY	Char	60	Locality or city.
COUNTY	Char	32	County.
SEASON BEGIN	Char	5	Beginning date of season.
SEASON END	Char	5	Ending date of season.
MAX LODGING	Nbr	17	Maximum lodging amount.
NO GOVERNMENT MEALS	Nbr	17	No government meals.
PROPORTIONAL MEALS	Nbr	17	Proportional meals.
MAX PER DIEM RATE	Nbr	17	Maximum per diem rate.
EFFECTIVE DATE	Char	10	Effective date.

Loading OCONUS Data

Access the Inbound File Rule page.

Select and enter these parameters:

File Identifier	Inbound File	Definition Name	Message Name
OCONUS_LOAD	OCONUSNM.TXT	OCONUS_DATA_FL	OCONUS_LOAD

Use the Inbound File page to run the staging program (EOP_PUBLISHF), which publishes the data and uses subscription codes to populate the staging table (EX_OCONUS_TAO).

OCONUS Data Format

When you import OCONUS data, the input file is formatted as shown in this table.

Field Name	Type	Length	Comments
COUNTRY	Char	60	Country.
LOCALITY/CITY	Char	60	Locality or city.
SEASON BEGIN	Char	5	Beginning date of season.
SEASON END	Char	5	Ending date of season.
MAX LODGING	Nbr	17	Maximum lodging amount.
NO GOVERNMENT MEALS	Nbr	17	No government meals.
PROPORTIONAL MEALS	Nbr	17	Proportional meals.
OFF BASE INC.	Nbr	17	Off base.
FOOTNOTE	Nbr	17	Footnote.
FOOTNOTE RATE	Nbr	17	Footnote rate.
MAX PER DIEM RATE	Nbr	17	Maximum per diem rate.
EFFECTIVE DATE	Char	10	Effective date.

Loading Airline Ticket Data from Vendors

Expenses accepts airline ticket data that populates expense lines. This data is used to reconcile airline tickets with business trips that employees take. To process airline ticket data in your expense system, obtain a comma-delimited file (a .CSV file) from your vendor.

This section discusses how to load airline ticket data.

Pages Used to Load Airline Ticket Data from Vendors

Page Name	Object Name	Navigation	Usage
File Inbound	EO_FILE_INBOUND	Enterprise Components, Integration Definitions, Inbound File Rule	Set up inbound flat file processing. See PeopleSoft Enterprise Components 8.9 PeopleBook.
Inbound File	EO_FILETOMSG	Enterprise Components, Integration Definitions, Initiate Processes, Inbound File Publish	Initiate inbound flat file processing. This file-to-message processing function reads the file row set and publishes it as a message. See PeopleSoft Enterprise Components 8.9 PeopleBook.

Page Name

Object Name

Navigation

Usage

File Inbound

EO_FILE_INBOUND

Enterprise Components, Integration Definitions, Inbound File Rule

Set up inbound flat file processing.

See PeopleSoft Enterprise Components 8.9 PeopleBook.

Inbound File

EO_FILETOMSG

Enterprise Components, Integration Definitions, Initiate Processes, Inbound File Publish

Initiate inbound flat file processing. This file-to-message processing function reads the file row set and publishes it as a message.

See PeopleSoft Enterprise Components 8.9 PeopleBook.

Loading Airline Ticket Data

Access the File Inbound page.

Select and enter these parameters:

File Identifier	Inbound File	Definition Name	Message Name
AIRLINE_TKT	AIRLINETKT.CSV	EX_AIRLINE_TKT	AIRLINE_TKT

Access the Inbound File page and run the staging program (EOP_PUBLISHF), which publishes the data and uses subscription codes to populate the EX_AIRLINE_TKT table.

Airline Ticket Data (AIRLINETKT.CSV) Format

When you import airline ticket data, the input file must be formatted as shown in this table:

Field Name	Type	Length	Comments
AIRFARE_RCPT_NBR	Char	16	Airfare receipt number.
EMPLID	Char	11	Employee ID.
AIR_TKT_RECON_STAT	Char	1	Airline ticket reconciliation.
MERCHANT	Char	40	Merchant.
MONETARY_AMOUNT	Sign	28	Monetary amount.
CURRENCY_CD	Char	3	Currency code.
CRDMEM_ACCT_NBR	Char	20	Cardmember account number.

Load External Data	Select to load data files that you specify in the File Name field into Expenses tables. The EX_LOAD Application Engine process loads valid data into the EX_TRANS table and loads invalid data into vendor-specific error tables.
Reload Corporate Card Errors	Select to have the EX_LOAD Application Engine process reload corrected credit card data from the EX_STG_ERR table. Note. Before selecting this option, use the Review Corporate Card Errors page to select transactions to reload. See Handling Credit Card Errors.
Data Source	Select a source. Options are: American Express: Loads credit card transactions from American Express. Diners Club: Loads credit card transactions from Diners Club. Note. Diners Club has two data sources. When you select Diners Club, the system displays two File Name fields in which you enter TRANS.DAT and ACCOUNT.DAT file names and locations. You must enter TRANS.DAT and its location on the same line. MasterCard: Loads credit card transactions from MasterCard. US Bank: Loads credit card transactions from US Bank. Visa: Loads credit card transactions from Visa International.
File Name	Enter the path and name of the file that contains the data that you want to process.