Update Back-End Database-Computing Services - Carnegie Mellon University

Update Back-End Database

Information included here will walk your through the process of updating your department or group's backend database with credit card information. To do this, we will create a standard comma delimited outload file of all credit card transaction information for each department.  This file will include the merchant_ref_no, which you can use to identify the transaction in your database.

Outload File Format for Credit Card Transactions

The file will be created daily and can be downloaded from the production report area (authorized access only).  There is a test report area as well (also authorized access only). 

The datafile will not contain full credit card numbers for security reasons.

There are 3 record types:

header HEAD appears once at the top
summary SUMMARY contains customer information
detail DETAIL contains detailed information on the items ordered

The record type appears as the 1st element in the row.

Header Record

HEAD, date, store #
Field Name Data type/format Notes
HEAD char 4 This field will always contain the value 'HEAD'
date dd-mon-yyyy This is the date the report was generated
store # char 5 The store number will be assigned to you

Summary Record

SUMMARY, cc_id, date, time, settle_now, transaction confirmation email, merchant reference no, cardholder last name, cardholder first name, customer email, customer phone, bill address, bill_city, bill_state, bill_zip, bill_country, credit card type, first4 & last4 cardholder credit card number, credit card expiration month, credit card expiration year, customer ip address, auth_code, auth_msg, settle_code, settle_msg, auth_amount, settle_amount, credit_amount
Field Name Data type/format Notes
SUMMARY char 7 This field will always contain the value 'SUMMARY'
cc_id integer Transaction identifier, unique key
date   dd-mon-yyyy
time   hh:mm:ss
settle_now char 1 Y or N
transaction confirmation email char 50  
merchant reference no char 50  
cardholder last name char 60  
cardholder first name char 60  
customer email char 255  
customer phone char 15  
bill address char 60  
bill city char 50  
bill state char 2  
bill zip char 10  
bill country char 60  
credit card type char 1 V for Visa, M for Mastercard, A for Amex
cardholder credit card number char 11 in the format ...1234
credit card expiration month char 2 (ie: 02)
credit card expiration year char 4  
customer ip address char 15 IP address from which order was placed
auth_code char 1 One-digit code that indicates whether the authorization request was successful
auth_msg char 255 Descriptive response to authorization request
settle_code char One-digit code that indicates whether the settlement request was successful
settle_msg char 255 Descriptive response to settlement request
auth_amount float Amount successfully authorized
settle_amount float Amount successfully settled
credit_amount float Amount successfully credited

Order Detail Record

DETAIL, cc_id, transaction_type, item name, item_sku, item_qty, item_price_each, item_gl_string, product_sku
Field Name Data type/format Notes
DETAIL char 6 This field will always contain the value 'DETAIL'
cc_id integer Transaction identifier, unique key  
transaction_type char 6 auth or settle or credit  
item_name char 50 Name of item (good or service) purchased
item_sku char 20 SKU
item_qty float Quantity purchased
item_price_each float Per Item Price
item_gl_string char 25 General Ledger String for use in Oracle Financials
product_sku char 30 Optional Field to contain SKU number for item

Sample Datafile

Credit Card Sample Data File

"andrew@andrew.cmu.edu","4122680000","5000 Forbes Ave","Pittsburgh","PA","15238","US","V",
DETAIL,10,"auth","tuition","",1,2000.00,"00000000000000000000000"," "
DETAIL,10,"settle","tuition","",1,2000.00,"00000000000000000000000"," "