Steps to Load Data from MasterCard

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.