Atrex 14 Database Structure Changes

The following are the major changes that have been made to the Atrex database structure for Atrex 14 which can effect existing custom reports or SQL. Minor changes such as changes to the field length or type are not listed. If you are upgrading from Atrex 13 or order and use custom reports or access the data from outside of Atrex, the information in this KB article will assist you with updating your reports or programs for use with Atrex 14 and newer.

Table: AR

Cash - Removed
Chk - Removed
CreditCard - Removed
DebitCard - Removed
CreditMemo - Removed
CCType - Removed
CCNum - Removed
ECCNum - Removed
CCExpDate - Removed
CCName - Removed
CCAuth - Removed
CCPurchCd - Removed
CCAVAddr - Removed
DCNum - Removed
DCAuth - Removed
DCTraceNo - Removed
CCRetailMoto - Removed
AVSResult - Removed
CVVResult - Removed
SigDataCC - Removed
SigImageCC - Removed
GCNum - Removed
GCAuth - Removed
GCTraceNo - Removed

TotalPaid - Added
TransType - Added

Notes: TotalPaid contains the sum of the Cash,Chk,CreditCard, CreditMemo, and GiftCard values after conversion to v14. See reference to Payment table below for more information about the other removed fields.

Table: Cust

GMAcctNo - Removed

Table: DDetail

Cash - Removed
Chk - Removed
CreditCard - Removed
DebitCard - Removed
CreditMemo - Removed

PaymentType - Added
PaymentTypeInd - Added
Amount - Added

Notes: In order to accommodate custom payment types, the fields for specific payment types were removed and replaced with a single amount field. The PaymentType and PaymentTypeInd fields are used to designate what type of payment is in the amount field.

Table: INVOICE, ORD, SO

Cash - Removed
Chk - Removed
CreditCard - Removed
DebitCard - Removed
CreditMemo - Removed
CCType - Removed
CCNumber - Removed
ECCNum - Removed
CCExpDate - Removed
CCAuth - Removed
CCName - Removed
CCPurchCd - Removed
CCAVAddr - Removed
DCNum - Removed
DCAuth - Removed
DCTraceNo - Removed
CCRetailMoto - Removed
AVSResult - Removed
CVVResult - Removed
SigDataCC - Removed
SigImageCC - Removed
GCNum - Removed
GCAuth - Removed
GCTraceNo - Removed

TotalPaid - Added
TransType - Added

Serialized - Removed (Unused)

Table: RInvoice

CCType - Removed
CCNum - Removed
ECCNum - Removed
CCExpDate - Removed
CCAuth - Removed
CCAVAddr - Removed
CCName - Removed

CCGUID - Added

Notes: The values in the removed fields are copied into the customer credit card table during the conversion to Atrex 14. The new CCGUID field is a link to the credit card entry on the customer.

New Table: Payment

The new Payment table holds the payment information removed from the AR, Invoice, Ord, and SO tables. As Atrex 14 allows for custom payment types and multiple payments of the same payment type, the payment information had to be moved to a separate table.

During the conversion to Atrex 14, all existing payment information is moved into the Payment table. Values from the payment type specific fields were moved into the Amount field, and the PaymentType and PaymentTypeInd fields are used to designate what type of payment is in the amount field. The separate credit card, debit card, and gift card fields were moved into a single set of fields. So where the ECCNum, EDCNum, and EGCNum were used to store the credit card, debit card, and gift card numbers, the values for all three of these are now stored in a single ECNum field.

The values in the Payment table are linked back to the transactions by the TransType and TransNum fields. The AR table is also linked back to the payment table via these same fields and the AR table is considered the owner of the records. If the records for the transaction are deleted from the corresponding transaction table, the payment entries will remain until the corresponding transaction record is deleted from the AR table.

Article ID: 17, Created: 10/12/2013 at 6:08 PM, Modified: 9/5/2016 at 12:17 PM

Was this article helpful?

Share this article