Loading Credit Card Data

This topic provides an overview of credit card data loads and discusses:

PeopleSoft Expenses delivers out-of-the-box functionality to support interfaces with five credit card suppliers that populates expense accounting lines with credit card data. You must work with your credit card supplier to determine how often data is available to download and the file format in which it comes.

After you establish a relationship with a credit card supplier, you must set up that company as a supplier in Oracle's PeopleSoft system to establish a supplier ID number, address, and bank information. PeopleSoft Expenses enables you to pay suppliers directly if the supplier 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 suppliers and employees. Use the same functionality to set up new credit card suppliers.

After you establish the credit card supplier in your system, you must link the supplier 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 supplier American Express.

Each credit card supplier 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 supplier file must be mapped to the corresponding data elements in PeopleSoft Expenses. You must:

  • Map credit card supplier codes to corresponding payment types in PeopleSoft Expenses.

  • Map credit card supplier merchant category codes to corresponding merchant category codes in PeopleSoft Expenses.

  • Map credit card supplier expense types to corresponding expense types in PeopleSoft Expenses.

  • Map credit card supplier cash advance codes to corresponding cash advance codes in PeopleSoft Expenses.

  • Map credit card supplier currency codes to corresponding currency codes in PeopleSoft Expenses.

  • Map credit card supplier country names to corresponding country codes in PeopleSoft Expenses.

  • Map credit card supplier data source locations to corresponding expense location codes in PeopleSoft Expenses.

  • Map credit card supplier enhanced data to corresponding expense types in PeopleSoft Expenses.

After you set up the credit card supplier 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 suppliers 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 supplier and employee setup and map the data elements, you can begin accepting data from the credit card supplier. 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.

Enhanced Data

This diagram illustrates the process of loading enhanced data, which creates expense lines. PeopleSoft Expenses supports importing enhanced detail data for hotel expense transactions that are charged on a Visa, American Express, and MasterCard credit card. This data is transferred into Expense Report or My Wallet tables. To support this process, you need to indicate whether enhanced data can be loaded into your Expenses application using the Corporate Card Supplier setup component. You also need to map the enhanced data to expense types.

 Load External Data process for credit card enhanced data

The diagram illustrates these decision points for the Load External Data process :

  1. Is the Data Source Amex (either GL or KR), Visa or MC3.0?

    1. If yes, process enhanced data checks and go to step 2.

    2. If no, run the Load External Data Process.

  2. Is the Load Enhanced data check box selected on the Corporate Card Supplier or the Card Issuer page for the supplier?

    1. If yes, go to step 3.

    2. If no, run the Load External Data Process.

  3. Is the Create Expense Lines check box selected on the Corporate Card Supplier or the Card Issuer page?

    1. If yes, go to step 4.

    2. If no, run the Load External Data Process.

  4. Are delay days greater than zero?

    1. If yes, load data into staging tables and go to step 5.

    2. If no, run the Load External Data Process.

  5. Have the delay days been reached?

    1. If yes, got to step 6.

    2. If no, stop.

  6. Are the amounts on the enhanced data lines equal to the transaction amounts?

    1. If yes, process the lines through the Load External Data process.

    2. If no, do not create expense line. Load the data into the Enhanced Data Detail and Summary tables.

The Expenses Load External Data process loads the credit card supplier file that may contain the main transaction and the enhanced data in the same file. However, the main transaction may come in a file prior to the enhanced data. For example, the main transaction might be sent in a file that a credit card supplier sends on Monday, and the enhanced data is sent in a file that the same credit card supplier sends on Thursday. In this case, the system incorporates the delay days to connect the main transaction with the enhanced data. The delay days is defined on the Credit Card Supplier page.

If the value in the Delay Days field is greater than zero, all hotel transactions are loaded to staging tables 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 breaks them into individual expense lines. The enhanced 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 enhanced detail data is marked as non-reimbursable, then all enhanced detail lines are marked as l non-reimbursable. Also, if any of the enhanced detail data is deleted from the expense sheet, then all enhanced 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 My Wallet using the current restore process.

Hotel Folio Files from American Express

It is important to understand how the Load External Data process handles the financial transaction data and the hotel folio data from American Express.

If importing a folio file from American Express and the delay days is zero, you need to load the financial transaction file (GL1025 or KR1025) and the hotel folio file in the same run control. If delay days = 0, and the GL1025, or KR1025 file is processed, and then the hotel folio file is processed, then only the hotel folio data is written to the enhanced data tables and individual expense lines are not created. This is because the financial transactions were written to the EX_TRANS table when the GL1025, or KR1025, file was processed and the system does not add enhanced data from American Express after the main transaction file is added to EX_TRANS.

If delay days is > 0 and both processes are run at the same time, then the financial data from GL1015, or KR1015, is written to the staging table and stays there until the delay days is reached or until the data is in the hotel folio file. If the hotel folio data exists and existing data is in the staging tables, then the rows are processed and the system attempts to create individual expense lines. If existing data is not in the staging tables, then the system looks for the data in EX_TRANS. If the transaction data exists in EX_TRANS, then the enhanced data is loaded to the enhanced detail table. If the transaction data cannot be found, then the hotel folio data is ignored.

If delay days > 0 and only the hotel folio file is processed, if the financial transaction is already in the staging tables, then the entire transaction is processed. If the financial transaction is not in the staging tables, then the process looks for the transaction in EX_TRANS. If the financial transaction is in EX_TRANS, then the process loads the data into the detail table. If the financial transaction is not in EX_TRANS, then the data is ignored. When the delay days has been reached, any financial data that is in the staging tables is processed, Any data in the enhanced data holding table is loaded to the enhanced detail table.

Error processing and Reprocess Errors do not apply to hotel folio files because rows from these files are not written to error tables. If a match cannot be made between the transaction data and the folio data, then the hotel data is not processed. However, if the Create Expense Lines check box is selected on the Corporate Card Supplier or the Card Issuer page, then the expense lines are processed through the error checks.

Important! To load data from credit card suppliers, please see the supplier for file layouts.

Access the Load External Data page.

Loading American Express Transaction Data

To load transaction data from American Express:

  1. Access the Load External Data page.

  2. Select American Express KR 1025 or GL 1025 in the Data Source field.

  3. Enter the path and name of the American Express data file in the File Name field.

  4. Entering a file name and path is optional if you reprocess credit card data.

  5. Select Load External Data.

American Express delivers multiple transaction types in a record called KR-1025 GL1025, 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 for KR1025 or EX_PUB_AMXGT for GL1025) and the loading process (EX_LOAD_AMEX). The staging process populates the staging table (EX_AM_K10_TA1 for KR1025 or EX_AM_G10_TA1 for GL1025). The loading process then:

  1. Checks for invalid values and loads errors into the EX_AMEX_STG_ERR table.

  2. Completes the load process by loading data into the EX_TRANS table.

Loading American Express Maintenance Data

To load maintenance data from American Express:

  1. Select American Express - KR1205 or GL1205 in the Data Source field.

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

  3. Select Load External Data.

Note: The file for American Express (KR1205 or GL 1205) is sent one time per week. Therefore, you should select and run American Express - KR1205 or GL 1205 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 for KR1205 or EX_PUB_AMXA for GL1205) and the loading process (EX_LOAD_AMX2 for KR1205 or EX_LOAD_AMX2 for GL1205).

See the Load External Data process flow for account maintenance diagram:

See Understanding the Load External Data Process.

MasterCard produces multiple transaction types with two types of files:

  • CDF v2: is a flat file version where 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 where 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 and 21 – Passenger Transport Addendum Record (MasterCard CDF 2)

    • Addendum type 3 – Lodging Addendum Record (MasterCard CDF 2)

    • Addendum type 4 – Car Rental Addendum Record

    • Addendum type 5020 and 5021 - Passenger Transport Addendum Record (MasterCard CDF 3)

    • Addendum type 5030 and 5010 – Lodging Addendum Record (MasterCard CDF 3)

  • Transaction Type 5040 – Vehicle Rental Detail Addendum Record

  • Transaction Type 5900 - Financial Adjustments Record (MasterCard CDF 3)

To load data from a MasterCard flat file (CDF v2):

  1. Access the Load External Data page.

  2. Select MasterCard CDF2 in the Data Source field.

  3. Enter the path and name of the MasterCard (CDF v2) flat file in the File Name field.

  4. Select Load External Data

To load data from a MasterCard XML file (CDF v3):

  1. Access the Load External Data page.

  2. Select MasterCard CDF3 in the Data Source field.

  3. Enter the path and name of the MasterCard (CDF v3) XML file in the File Name field.

  4. Select Load External Data

The staging process populates these staging tables that correspond to MasterCard transaction types

For CDF v2:

  • EX_MC4300_TA1

  • EX_MC5_00_TA1

  • EX_MC5_02_TA1

  • EX_MC5_21_TA1

  • EX_MC5_03_TA1

  • EX_MC5_04_TA1

For CDF v3:

  • EX_MC3_43_TA1 and EX_MC3_44_TA1

  • EX_MC3_50_TA1

  • EX_MC3_51_TA1

  • EX_MC3_52_TA1 and EX_MC3_52_TA2

  • EX_MC3_53_TA1 and EX_MC3_53_TA2

  • EX_MC3_54_TA1

  • EX_MC3_59_TA1

The loading process:

  1. Combines EX_MCXX_XX staging tables into one table (EX_MC2_ST_TA1 for CDF v2, EX_MC3_ST_TA1 for CDF v3).

  2. Checks for invalid values and loads errors into the EX_MC_STG_ERR table.

  3. Completes the load process by loading data into the EX_TRANS table.

Addendum Type 2 and 21 – Passenger Transport

MasterCard’s flat file version 2.0 contains Air Travel (type 2) and Leg Detail (type 21) data. The 5000 Passenger Transport Addendum Record provides the details about a financial transaction associated with a passenger transport ticket.

The first 5000 Passenger Transport Addendum record for the transaction must be immediately preceded by a 5000 Financial Transaction, or a 5000 Financial User Amount Addendum record. All monetary amounts in all addenda records are assumed to be in the original currency.

The 5000 Passenger Transport Leg Addendum Record (type 21) provides each leg logged for a passenger transport ticket. The first 5000 Passenger Transport Leg Addendum Record for the transaction must be immediately preceded by a 5000 Passenger Transport Addendum Record. One record is provided for each trip leg. All monetary amounts in all addenda records are assumed to be in original the currency

Addendum Type 3 – Lodging

The MasterCard CDF 2.0 (flat file) contains Lodging (type 3) data and provides the details about a financial transactions associated with lodging accommodations. All monetary amounts in all addenda records are assumed to be in the original currency.

Addendum Type 5020 and 5021 – Passenger Transport

MasterCard’s xml file version 3.0 contains Passenger Transport Detail General Ticket Information (type 5020) contains the summary information and Passenger Transport Detail Trip Leg Data (type 5021) contains the leg details.

Addendum Type 5030 and 5010 – Lodging

The MasterCard CDF 3.0 (XML file) contains summary lodging data is found in the LodgingSummaryAddendum_5030Type entity. The line details are found in the CorporateCardLineItemDetail_5010Type.

Addendum Type 5900 - Financial Adjustments

The MasterCard CDF 3.0 (XML file) contains adjustment data is found in the FinancialAdjustmentRecord_5900Type entity. By default this tag is mapped to the Miscellaneous Expense type. However, users can change this mapping to an Expense type of their choice, using the Expense Type - Corporate Card Page by adding MasterCard v3 as a new entry for Data Source and ADJ as a Merchant Category Group .

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.

Note: For a complete schema of the MasterCard XML file, see your MasterCard representative.

To load data from US Bank:

  1. Access the Load External Data page.

  2. Select a data source of US Bank.

  3. In the File Name field, enter the path and name of the US Bank data files.

  4. 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 populates these staging tables that correspond to US Bank transaction types:

  • EX_USB_02_TA1

  • EX_USB_05_TA1

  • EX_USB_10_TA1

The loading process:

  1. Combines the EX_USB_INFO_XX staging tables into one table (EX_USB_ST_TA1).

  2. Checks for invalid values and loads errors into the EX_USB_STG_ERR table.

  3. Completes the load process by loading data into the EX_TRANS table.

To load data from Visa International:

  1. Access the Load External Data page.

  2. Select a data source of Visa.

  3. (Optional) In the File Name field, enter the path and name of the Visa International data file.

  4. Select Load External Data.

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

PeopleSoft Expenses uses transaction types 2, 3, 4, 5, 9, 14, 15, 32 from InfoSpan version 4.4.

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 populates the staging tables that correspond to Visa transaction types.

The loading process:

  1. Combines the staging tables into one table (EX_VSTG_TA1).

  2. Checks for invalid values and loads errors into the EX_VISA_STG_ERR table.

  3. Completes the load process by loading data into the EX_TRANS table.

The Lodging Summary (type 09) record type contains summary information on Visa commercial card transactions conducted at hotels or places of lodging.

The Lodging Detail (type 26 for Visa 4.0, type 32 for Visa 4.4) record type contains detailed information on Visa commercial card transactions conducted at hotels or places of lodging.

Visa CDF File Record type 14 and 15 contains air travel data. The passenger itinerary (T14) record type contains summary travel information on an air or rail trip paid with a Visa commercial card. The leg-specific information (T15) record type contains summary travel information on an air or rail trip.

The Car Rental Summary (type 02) record type contains summary information for Visa commercial card transactions conducted at car rental merchants.