Understanding External Expense DataThis chapter provides an overview of external expense data and discusses how to:
Load external expense data.
Load credit card data.
Resolve credit card maintenance errors.
Resolve credit card transaction errors.
Load benchmark data.
Load airline ticket data from vendors.
Understanding External Expense DataOrganizations are simplifying the administrative operations that are associated with expense reimbursement. PeopleSoft 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
This section provides an overview of the Load External Data process and discusses how to load external data into PeopleSoft Expenses.
Understanding the Load External Data ProcessPeopleSoft Process Scheduler runs the Load External Data 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.
The Include Account Maintenance and Update Profile for New Account options on the Installation Options - Expenses page determine if credit card maintenance is processed, and whether updates of new accounts to existing employees occur automatically. If these options are selected, the Load External Data process for American Express, MasterCard, and Visa adds new accounts to existing profiles and updates the employee profile and corporate card information in Expenses for these fields:
Card Member Account Number.
Account Status.
Account Credit Limit.
Transaction Limit.
Anniversary/Issue Date.
Expiration Date.
If the account number for an employee does not exist and the employee identifier exists in the credit card vendor file, then the new account is created. If the account number for an employee does not exist and the employee identifier does not exist in the credit card vendor file, then the rows are inserted into an error table.
This diagram illustrates the Load External Data process flow for account maintenance:
Load External Data process flow for account maintenance
During the Load External Data process for account maintenance:
Data is loaded from a file that is received from a credit card vendor containing credit card account maintenance records.
American Express provides a separate file containing account maintenance.
Visa and MasterCard include account maintenance as another record type in the same file containing the account transactions.
Maintenance to an existing account occurs if the account number from the credit card vendor can be matched with an existing account in the PeopleSoft system.
The update of a new account to an existing employee occurs if the employee identifier from the credit card vendor is valid and matches an existing employee id in the PeopleSoft system.
Maintenance records that are not matched to an employee profile are written to an error table where they can be reviewed using the Corporate Card Maintenance Errors page.
Records that are reviewed can be reloaded to update the appropriate employee profile, or the profile can be updated manually.
Warning! If you are an HCM customer and use this feature in PeopleSoft Expenses, you should ensure that the HCM business process does not inadvertently overlay employee profile updates from the credit card issuer when running the data sync process between HCM and Expenses.
MasterCard and Visa
For MasterCard and Visa, if you select the Reprocess Corporate Card Maintenance Errors check box, the Load External Data is automatically selected. You can run the Reprocess Corporate Card Errors and Reprocess Corporate Card Maintenance Errors at the same time. The Reprocess Corporate Card Maintenance Errors process will run first. You can also select the Reprocess Corporate Card Errors or the Reprocess Corporate Card Maintenance Errors check box.
This table explains the type of data that loads when the check boxes are selected or deselected:
|
Load External Data |
Reprocess Corporate Card Errors |
Reprocess Corporate Card Maintenance Errors |
Description |
|
Selected |
Deselected |
Deselected |
The file name is required and flat file loads. |
|
Selected |
Selected |
Deselected |
The file name is not required and only transaction errors load. |
|
Selected |
Selected |
Selected |
The file name is not required and all errors load. |
|
Selected |
Deselected |
Selected |
The file name is not required and only maintenance errors load. |
|
Deselected |
Deselected |
Deselected |
The system issues an error instructing you to select something. |
|
Deselected |
Selected |
Selected |
This is not a valid combination. The system will automatically select the Load External Data check box. |
Page Used to Load External Expense Data|
Page Name |
Definition Name |
Navigation |
Usage |
|
RUN_EXDATALD |
Travel and Expenses, Corporate Credit Cards, Load External Data Sources |
Load data from outside sources into the PeopleSoft Expenses tables. |
Loading External Data into PeopleSoft Expenses
Access the Load External Data page (Travel and Expenses, Corporate Credit Cards, Load External Data Sources).
|
Select a source. Options are:
Note. The file for American Express (KR1205) is sent one time per week. Therefore, you should select and run American Express - Member List in the Data Source field prior to running the Load External Data process for the American Express data source. This ensures that all maintenance and file updates occur prior to processing transactions. |
|
|
Load External Data |
Select to load data files that you specify in the File Name field into PeopleSoft Expenses tables. The Load External Data process loads valid data into the EX_TRANS table and loads invalid data into vendor-specific error tables. |
|
File Name |
Enter the path and name of the file that contains the data that you want to process. |
|
Select to have the Load External Data process reprocess credit card transaction data that has been corrected using the Credit Card Transaction Errors pages. |
|
|
Select to have the Load External Data process reprocess credit card maintenance data that has been corrected using the Credit Card Maintenance Errors pages. This option is displayed only when the value in the Data Source field is American Express - Members List, MasterCard, or Visa. |
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 LoadsPeopleSoft 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 Oracle's PeopleSoft system to establish a vendor ID number, address, and bank information. PeopleSoft Expenses enables you to pay vendors directly if the vendor is appropriately set up in the accounts payable system. If you purchase PeopleSoft 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, MCv2 for MasterCard CDF V2, MCv3 for MasterCard CDF V3, 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 PeopleSoft 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. PeopleSoft 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 PeopleSoft Expenses. You must:
Map credit card vendor codes to corresponding payment types in PeopleSoft Expenses.
Map credit card vendor merchant category codes to corresponding merchant category codes in PeopleSoft Expenses.
Map credit card vendor expense types to corresponding expense types in PeopleSoft Expenses.
Map credit card vendor cash advance codes to corresponding cash advance codes in PeopleSoft Expenses.
Map credit card vendor currency codes to corresponding currency codes in PeopleSoft Expenses.
Map credit card vendor country names to corresponding country codes in PeopleSoft Expenses.
Map credit card vendor data source locations to corresponding expense location codes in PeopleSoft Expenses.
Map credit card vendor level 3 data to corresponding expense types in PeopleSoft 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.
Note. PeopleSoft Expenses uses PeopleSoft PeopleTools credit card encryption technology to protect credit card data. PeopleSoft Expenses also masks credit card data entered for employees, so that only the last 4 digits are displayed. All credit card numbers received as data into PeopleSoft Expenses are encrypted using the PeopleSoft PeopleTools credit card strong encryption feature. Credit card data received as part of a file transfer from vendors may not be encrypted. These files should be protected using other methods such as file system security provided by the host computer's operating system.
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 PeopleSoft 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.
Note. To load data into the PeopleSoft system the currency of the credit card must be the same as the base currency of the employee. However, the currency of the transaction can be any currency. The base currency of the employee is determined by the currency of the business unit that is defined on the Employee Profile page.
PeopleSoft Expenses supports importing level 3 detail data for hotel expense transactions that are charged on a Visa and MasterCard credit card. This data is transferred into Expense reports or My Wallet tables. To support this process, you need to indicate whether level 3 data can be loaded into your Expenses application using the Corporate Card Vendor setup component. You also need to map the level 3 data to expense types.
This diagram illustrates the process of loading level 3 data:
Load External Data process for credit card level 3 data
The Expenses Load External Data process loads the credit card vendor file that may contain the main transaction and the level 3 data in the same file. However, the main transaction may come in a file prior to the level 3 data. For example, the main transaction might be sent in a file that a credit card vendor sends on Monday, and the level three information is sent in a file that the same credit card vendor sends on Thursday. In this case, the system incorporates the delay days to connect the main transaction with the level 3 data. The delay days is defined on the Credit Card Vendor page.
If the delay days is greater than zero, all hotel transactions are loaded to staging tables (EX_LVL3_VSTG, EX_LVL3_VSTG2, and EX_LVL3_VSTG3). This includes the main transaction data, the lodging summary data, and the lodging detail data.
The system retrieves the data from the staging table when the delay days are met. If the detail or summary data equals the total amount of the main transaction record, the data is moved into My Wallet and appears on the My Wallet Detail page. If the detail or summary amounts do not match, only the main transaction data is moved into My Wallet.
When the system loads transactions into My Wallet, the transactions are divided into new expense lines. The Load External Data process takes the summary or detail lines and break them into individual expense lines. The level 3 mapping table is used to determine what expense type to assign to the new expense lines. The amounts that are in the appropriate fields are mapped into transaction amount and transaction currency.
If any of the level 3 detail data is marked as personal, then all level 3 detail lines are marked as personal. Also, if any of the level 3 detail data is deleted from the expense sheet, then all level 3 detail lines are deleted and sent back to My Wallet. If the lines are deleted from My Wallet, then they can be restored back to the Wallet using the current restore process.
See Also
Maintaining Employee Credit Card Data
Changing Credit Card Encryption
Loading Data from American Express
Access the Load External Data page.
Loading Transaction Data from American Express
To load transaction data from American Express:
Select American Express in the Data Source field.
Enter the path and name of the American Express data file in the File Name field.
Entering a file name and path is optional if you reprocess credit card data.
Select Load External Data.
American Express delivers multiple transaction types in a record called KR-1025, but PeopleSoft Expenses uses only transaction type 1.
The 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.
Loading Maintenance Data from American Express
To load maintenance data from American Express:
Select American Express - Members List in the Data Source field.
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 reprocess credit card data.
Select Load External Data.
Note. The file for American Express (KR1205) is sent one time per week. Therefore, you should select and run American Express - Member List in the Data Source field prior to running the Load External Data process for the American Express data source. This ensures that all maintenance and file updates occur prior to processing transactions.
The Process Scheduler runs the Load External Data process that drives the publishing process (EX_PUB_AMEX2) and the loading process EX_LOAD_AMX2 See the Load External Data process flow for account maintenance diagram:
See Understanding the Load External Data Process.
American Express KR-1025 - Transaction Type 1
When you import credit card transaction 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. |
American Express KR-1205 – Maintenance File
When you import credit card maintenance data from American Express, the input file is formatted as shown in this table:
|
Field Name |
Type |
Length |
Comments |
|
Record Type |
Char |
1 |
|
|
Corporate ID |
Char |
6 |
|
|
Request Control Account |
Char |
15 |
|
|
Basic Control Account |
Char |
15 |
|
|
Basic Control Account Name |
Char |
25 |
|
|
Card member number |
Char |
15 |
The first 11 characters of the account number. |
|
Card member name |
Char |
25 |
The card member name as it appears on the card. |
|
Cost Center |
Char |
10 |
|
|
Employee Identifier |
Char |
10 |
|
|
Universal Number |
Char |
25 |
|
|
Filler |
Char |
12 |
Filler, 12 positions of spaces. |
|
TSS Indicator |
Char |
1 |
Telecommunications Systems Service Indicator:
|
|
Funds Access Indicator |
Char |
2 |
Funds Access Indicator, which is also known as Express Cash Indicator:
Standard liability values are: 01,02,03,04,05,06,18,19,20 and 21. Expanded liability values are: 23, 24, 25,26,27, 28,29, and 30. |
|
Corporate TC Indicator |
Char |
1 |
Values are:
|
|
Direct Pay Indicator |
Char |
1 |
Values are:
|
|
Direct Reimbursement Indicator |
Char |
1 |
Values are:
|
|
Anniversary Date |
Char |
6 |
Card anniversary format is CCYYMM, where:
|
|
Suspense Indicator |
Char |
1 |
Values are: ' ' Inactive '0' Suspended at last cutoff '1' thru '9' Account in Suspense
|
|
Social Security Number |
Char |
9 |
|
|
Company Name |
Char |
20 |
|
|
Card Member Address |
Char |
25 |
|
|
Card member City |
Char |
18 |
|
|
State company resides in |
Char |
2 |
|
|
Zip code of company address |
Char |
9 |
|
|
Country Code or State Code |
Char |
3 |
For example: 804 is USA |
|
Home Phone |
Char |
10 |
|
|
Office Phone |
Char |
10 |
|
|
Account Status |
Char |
2 |
Values are:
|
|
Cancel Code |
Char |
2 |
Values are:
|
|
Status Date |
Char |
8 |
Status date could be either cancel date or anniversary date. If the Cancel Code is 00 then the status date is the anniversary date CCYYMMDD. |
|
Expiration Date |
Char |
8 |
Expiration Date format is CCYYMMDD |
|
Current Balance |
Char |
10 |
|
|
Filler |
Char |
19 |
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 PeopleSoft 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.
MasterCard produces multiple transaction types with two types of files:
CDF v2: is a flat file version.
The 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).
CDF v3: is an XML version.
The Process Scheduler runs the Load External Data Application Engine process, which drives the staging process (EX_PUB_MCV3) and the loading process (EX_LOAD_MCV3).
Transaction types are:
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
To load data from a MasterCard flat file (CDF v2):
Select MasterCard in the Data Source field.
Enter the path and name of the MasterCard (CDF v2) flat file in the File Name field.
Select Load External Data.
To load data from a MasterCard XML file (CDF v3):
Select XML MasterCard in the Data Source field.
Enter the path and name of the MasterCard (CDF v3) XML file in the File Name field.
Select Load External Data.
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 Number |
Field Name |
Type |
Length |
Comments |
|
1 |
RecordIdentifier |
Char |
4 |
Identifies the record type. |
|
10 |
AccountNumber |
Char |
19 |
Account number that the corporation owns. |
|
12 |
Effective Date |
Char |
8 |
The date the account was made effective in a format of YYYYMMDD |
|
13 |
Expiration Date |
Char |
8 |
The date the account is made ineffective in a format of MMYYYY |
|
14 |
CardholderName1 |
Char |
35 |
If the account has the capability to perform merchant transactions, this field contains the cardholder's name. |
|
26 |
Single Transaction Dollar Limit |
Number |
16 |
The authorized limit amount of a single cardholder transaction |
|
43 |
Credit Limit Sign |
Char |
1 |
Debit or credit indicator |
|
44 |
Credit Limit |
Number |
16 |
Account credit limit |
5000 (0) - Financial Transaction Record
|
Field Number |
Field Name |
Type |
Length |
Comments |
|
|
1 |
RecordIdentifier |
Char |
4 |
Identifies the record type. |
|
|
5 |
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. |
|
|
9 |
AccountNumber |
Char |
19 |
Account number that the corporation owns. |
|
|
15 |
TransactionAmount |
Number |
16,4 |
Transaction amount. |
|
|
17 |
TransactionDate |
Char |
8 |
Date (in YYYYMMDD format) on which the transaction took place. |
|
|
23 |
MerchantMCC |
Char |
4 |
MasterCard-assigned code to the merchant. Required if the MerchantActivityIndicator value is M. |
|
|
24 |
AmountInOriginal Currency |
Number |
16 |
Amount in original currency. |
|
5000 (2) - Passenger Transport Addendum Record
|
Field Number |
Field Name |
Type |
Length |
Comments |
|
1 |
RecordIdentifier |
Char |
4 |
Record type. |
|
5 |
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. |
|
13 |
TicketNumber |
Number |
15 |
Ticket number. |
5000 (3) - Lodging Addendum Record
|
Field Number |
Field Name |
Type |
Length |
Comments |
|
1 |
RecordIdentifier |
Char |
4 |
Record type. |
|
5 |
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. |
|
8 |
ArrivalDate |
Char |
8 |
Date (in YYYYMMDD format) on which the customer arrived. |
|
9 |
DepartureDate |
Char |
8 |
Date (in YYYYMMDD format) on which the customer departed. |
MasterCard CDF V3
MasterCard CDF 3.0 files are exchanged as XML between processors, MasterCard, and customers. The CDF transmission file element in CDF 3.0 XML consists of an ordered nesting of entities. The order and nesting of these entities is established by the CDF 3.0 schema file (.CDF Transmission File.xsd.). Each transmission file must begin with a transmission header record (tag: Transmission Header_1000) and terminated with a transmission trailer record (tag: Transmission Trailer_9999). All information comprising the transmission exists between these two records.
The nesting of records in a transmission file determines their relationship. Records can be thought of as describing the organization or describing transactions of that organization. This categorization helps when describing different requirements on the particular elements of a transmission file.
This graphic illustrates the portion of the MasterCard XML file that is used by PeopleSoft Expenses when loading the MasterCard CDF V3 file:
MasterCard CDF V3 schema
Note. For a complete schema of the MasterCard XML file, see your MasterCard representative.
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. PeopleSoft 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 |
NA |
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. PeopleSoft 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
EX_VISA_INFO_26
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 |
Char |
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 |
Char |
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 |
Char |
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 |
Char |
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 |
Char |
16 |
Maximum amount that is allowed per transaction for a purchasing card account. |
|
Corporation Payment Indicator |
Char |
1 |
Values are Yes (the client company pays the bill) and No (the cardholder pays). |
|
Billing Account Number |
Char |
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 |
Displays any endorsements or restrictions associated with the travel 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 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. |
InfoSpan Version 3.0 and 4.0 - Transaction Type 26
|
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 |
Char |
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. |
|
Charge Date |
Char |
8 |
Date account was charged for service provided. |
|
Item Sequence Number |
Nbr |
5 |
Sequence number that uniquely identifies a detail or summary row with respect to the transaction it is associated with. |
|
Daily Room Rate |
Sign |
18 |
Daily room rate for a hotel transaction. |
|
Discount Rate |
Sign |
18 |
Daily discount room rate for a hotel transaction. |
|
Food and Beverage Amount |
Sign |
18 |
Food and beverage charges in a card transaction for hotel lodging. This value does not include mini-bar charges. |
|
Folio Cash Advances |
Sign |
18 |
Cash advances charged against the folio. |
|
Valet Parking |
Sign |
18 |
Valet parking charges of a card transaction for hotel lodging. |
|
Mini Bar Charge |
Sign |
18 |
Charges of in-room mini bar food and beverage expenses. |
|
Laundry Charge |
Sign |
18 |
Laundry charges in a card transaction for hotel lodging. |
|
Telephone Charge |
Sign |
18 |
Charges of telephone expenses, including in room fax machine usage. |
|
Gift Shop Purchases |
Sign |
18 |
Charges of gift shop and merchandise purchases. |
|
Movie Charge |
Sign |
18 |
Charges for movies and other in room entertainment expenses. |
|
Business Center Charges |
Sign |
18 |
Charges for copies, faxes, and other business center expenses. |
|
Health Club Charges |
Sign |
18 |
Charges for health club or pool expenses, including court and equipment rental and golf green fees. |
|
Room Tax Amount |
Sign |
18 |
The amount of room tax applied against this transaction. |
|
Room Tax 2 Amount |
Sign |
18 |
The amount of room tax 2 applied against this transaction. |
|
Room Tax 3 Amount |
Sign |
18 |
The amount of room tax 3 applied against this transaction. |
|
Room Tax 4 Amount |
Sign |
18 |
The amount of room tax 4 applied against this transaction. |
|
Other Charge |
Sign |
18 |
The amount of another charge defined by the supplier. |
|
Description of Other Charge |
Char |
20 |
The description of the other charge defined by the supplier. |
|
Other Charge Code |
Char |
5 |
The code used by the supplier for the other charge. |
|
Message Identifier |
Char |
16 |
Message identifier in a transactions. This information could be used to match enhanced data with the card transaction. |
|
Purchase ID |
Char |
50 |
Number assigned by the merchant to the transaction. It can be a supplier order number, a folio number, or a car rental agreement number. This information could be used to match enhanced data with the card transaction. |
Resolving Credit Card Maintenance Errors
This section provides an overview of credit card maintenance error resolution, and discusses how to resolve credit card maintenance errors:
Understanding Credit Card Maintenance Error ResolutionThe PeopleSoft Expenses system tracks credit card maintenance errors only when the Include Account Maintenance option is selected on the Installation Options - Expenses page.
If you want the Load External Data process to automatically update the employee profile for new accounts you must also select the Update Profile for New Accounts option on the Installation Options - Expenses page. The Update Profile for New Accounts option on the Installation Options - Expenses page determines if all new accounts are sent to the Credit Card Maintenance Errors pages or if only the new accounts that do not have a valid employee ID are sent to the Credit Card Maintenance Errors pages.
For more information about the Include Account Maintenance and Update Profile for New Accounts options on the Installation Options - Expenses page:
See External Data Load Options.
Pages Used to Resolve Credit Card Maintenance Errors|
Page Name |
Definition Name |
Navigation |
Usage |
|
EX_AMEX_MNT_ERR |
|
Resolve American Express credit card maintenance errors. |
|
|
EX_VISA_MNT_ERR |
|
Resolve Visa credit card maintenance errors. |
|
|
EX_MC_MNT_ERR |
|
Resolve MasterCard credit card maintenance errors. |
Resolving Credit Card Maintenance Errors
Access the Credit Card Maintenance Errors - Amex Card Errors page (Travel and Expenses, Corporate Credit Cards, Credit Card Maintenance Errors).
|
Filter |
Select an option to view error rows for the credit card. Options include:
|
|
Refresh |
Click to refresh the page when the Filter option is changed. |
|
Complete |
Select to indicate that the credit card maintenance error has been corrected and the row is complete. Deselect to indicate that the credit card maintenance error has not been corrected and the row is not complete. Deselected rows are selected when the Reprocess Corporate Card Maintenance Errors option is selected on the Load External Data page. |
|
Assign to Profile |
Click to access the employee profile component. If an employee profile exists, the system takes you to the Corporate Card Information page for the employee. If an employee profile does not exist, the system takes you to the Employee Profile search page where the employee can be added. |
Resolving Credit Card Transaction Errors
This section provides an overview of credit card transaction error resolution, lists common elements, and discusses how to:
Resolve American Express transaction errors.
Resolve Diners Club transaction errors.
Resolve MasterCard transaction errors.
Resolve US Bank transaction errors.
Resolve Visa transaction errors.
Reprocess credit card transaction errors.
Understanding Credit Card Transaction Error ResolutionDuring 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. The credit card number is masked and displays only the last 4 digits of the credit card number after the data is entered and saved. |
|
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 PeopleSoft 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 PeopleSoft 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 PeopleSoft Expenses. |
|
Reprocess Transaction |
Select the transactions that you corrected and want to reprocess 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. |
See Also
Pages Used to Resolve Credit Card Transaction ErrorsResolving American Express Transaction Errors
Access the Amex Card Errors page (Travel and Expenses, Corporate Credit Cards, Credit Card Transaction Errors).
Card Errors Tab
|
MIS Industry Code |
Displays the American Express code for the type of expense transaction. PeopleSoft 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. PeopleSoft 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. PeopleSoft Expenses displays Y in the Invalid Transaction Currency field if this code is not mapped correctly. |
Resolving Diners Club Transaction Errors
Access the Diners Club Card Errors page (Travel and Expenses, Load External Information, Review Corporate Card Errors).
Card Errors Tab
|
Standard Industry Code |
Displays Diners Club code for the type of expense transaction. PeopleSoft 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. PeopleSoft 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. PeopleSoft Expenses displays Y in the Invalid Transaction Currency field if this code is not mapped correctly. |
Resolving MasterCard Transaction Errors
Access the MasterCard Errors page (Travel and Expenses, Load External Information, Review Corporate Card Errors).
Card Errors Tab
|
Merchant Type |
Displays the MasterCard code for the type of expense transaction. PeopleSoft 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. PeopleSoft 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. PeopleSoft Expenses displays Y in the Invalid Transaction Currency field if this code is not mapped correctly. |
Resolving US Bank Transaction Errors
Access the US Bank Card Errors page (Travel and Expenses, Load External Information, Review Corporate Card Errors).
Card Errors Tab
|
Standard Industry Code |
Displays the US Bank code for the type of expense transaction. PeopleSoft 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. PeopleSoft 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. PeopleSoft Expenses displays Y in the Invalid Transaction Currency field if this code is not mapped correctly. |
Resolving Visa Transaction Errors
Access the Visa Card Errors page (Travel and Expenses, Load External Information, Review Corporate Card Errors).
Card Errors Tab
|
Merchant Cat Cd (merchant category code) |
Displays the Visa code that indicates the type of expense transaction. PeopleSoft 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. PeopleSoft 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. PeopleSoft Expenses displays Y in the Invalid Transaction Currency field if this code is not mapped correctly. |
Reprocessing Credit Card Transaction Errors
Access the Load External Data page (Travel and Expenses, Load External Information, Load External Data Sources).
When you are ready to reprocess 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.
This field is not required if the Reprocess Corporate Card Errors option is selected.
Select Load External Data and Reprocess Corporate Card Errors.
Loading Benchmark Data
PeopleSoft 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 |
Definition Name |
Navigation |
Usage |
|
EO_FILE_INBOUND |
Enterprise Components, Integration Definitions, Inbound File Rule |
Set up inbound flat file processing. See PeopleSoft Enterprise FSCM PeopleBook: Enterprise Components |
|
|
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 FSCM PeopleBook: Enterprise Components |
Loading Runzheimer Data
Access the File Inbound page (Enterprise Components, Integration Definitions, Inbound File Rule).
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.
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 (Enterprise Components, Integration Definitions, Inbound File Rule).
Select and enter these parameters:
|
File Identifier |
Inbound File |
Definition Name |
Message Name |
|
CONUS_LOAD |
CONUSNM.TXT |
CONUS_DATA_FL |
CONUS_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_CONUS_TAO).
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 (Enterprise Components, Integration Definitions, Inbound File Rule).
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).
Note. Because OCONUS provides monthly updates, there may be more than one row of data for an expense location amount. The loading process does not replace existing rows. Instead, it adds rows and uses the effective date to differentiate rows of the same expense location amount.
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
PeopleSoft 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 |
Definition Name |
Navigation |
Usage |
|
EO_FILE_INBOUND |
Enterprise Components, Integration Definitions, Inbound File Rule |
Set up inbound flat file processing. See PeopleSoft Enterprise FSCM PeopleBook: Enterprise Components |
|
|
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 FSCM PeopleBook: Enterprise Components. |
Loading Airline Ticket DataAccess the File Inbound page (Enterprise Components, Integration Definitions, Inbound File Rule).
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. |
|
TRAN_DATE |
Char |
10 |
Transaction date. |
|
MONETARY_AMOUNT |
Sign |
28 |
Monetary amount. |
|
CURRENCY_CD |
Char |
3 |
Currency code. |
|
CRDMEM_ACCT_NBR |
Char |
20 |
Cardmember account number. |
|
AUDIT_ACTN |
Char |
1 |
Audit action |
See Also