Update Backend Database

How can I update my department backend database with the credit card information?

Academic Computing 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

HEAD,01-may-2002,10010
SUMMARY,10,"01-may-2002","10:15:34","Y","thehub@andrew.cmu.edu","SAR-XXXX","Carnegie","Andrew",
"andrew@andrew.cmu.edu","4122680000","5000 Forbes Ave","Pittsburgh","PA","15238","US","V",
"...1111","01","2004","128.2.12.9","1","ok","1","ok","","",2000.00,2000.00,0.00
DETAIL,10,"auth","tuition","",1,2000.00,"00000000000000000000000"," "
DETAIL,10,"settle","tuition","",1,2000.00,"00000000000000000000000"," "