EftposReceipts Table

This page contains technical detail about the physical database structure. This documentation is not required for normal day to day operation of the Point Of Sale.

Overview

This table holds details about the result each eftpos payment. It can include both sucess and failure payments details

Mesh

On mesh configured systems, this table is maintained as an output

Columns

FieldName	Datatype	Meta Info	Gnap	Description
eftrid	number		100/E
sid	long		105/E	Internal sale number allocated to this sale. References Sales.sid
stan	number		103/E	The STAN from the EFTPOS subsystem. This number is a form of unique identifier on the payment processing side to identify the EFT transaction.
cardtype	string			The card type as reported by the EFT software.
card4	string			Last 4 digits of eft cardnumbers, if enabled, encrypted, to aid auditing. Generally easier to use Right(4) on PAN field if available
datetime	datetime		102/s	The time on the PC when this transaction was recorded
txncode	string			The EFT subsystem transaction code
txntype	string:8		106/s	Type of transaction performed. Exact value varies according to eftpos subsystem in use.
amtpurchase	money			The amount of the transaction processed as a purchase component.
amtcash	string			The amount of the transaction processed as a cash out component.
amttip	string			The amount of the transaction processed as a tip component
messagetype	string			The messagetype used by the EFT subsystem in bank communication. Message types are defined by bank hosts and vary by EFT subsystem.
account	string			The account number selected by the user to pay the transaction.
datafield	string			The optional datafield parameter returned by the host. This is not used in the POS
caid	string			The EFT CAID parameter.
catid	string			The EFT CATID parameter.
txnref	string			The transaction reference used between the POS and the EFT subsystem. While this is a definite pattern, no assumptions about contents of this field should be made as the form may change without notice.
authcode	string			The authorisation code returned, if available.
responsecode	string			The response code returned from the host. Values and meanings depend on eftpos interface in use, although values are somewhat standardisded
txn_duration	number		107/E	Time the eftpos transaction took in seconds
pan	string:24		104/3	Customer Primary Account Number as reported by Eftpos. Older versions of eftpos interfaces may have incorrectly supplied the complete value, newer versions typically supply only a part of the actual number
cardname	string
track1	string			The contents of track1 from the magswipe. Adding this field to the database may violate eftpos provider agreements and/or subject you to higher PCI DSS requirements
track2	string			The contents of track2 from the magswipe. Adding this field to the database may violate eftpos provider agreements and/or subject you to higher PCI DSS requirements
responsetext	string			The text from of the response from the acquirer
tdate	string			The transacation date as reported by the EFT subsystem, not the POS date
merchant	string			The merchant used for the transaction
totalcash	string			Total cash amount
enabletip	string			Indicates if tipping mode was enabled
dateexpiry	string			The card holders expiry date, if present
datesettlement	string			The settlement window this transaction belongs too
receipt0 /1/2/3/4	string			Copy of the eftpos receipt, with longer string broken over several fields.
dayseqnum	number			Cash drawer day sequence number.
srcuid	number
srcuidkey	number
paysequence	number			The payment.sequence that this record relates too. Numbers can sometimes be reused if certain abnormal conditions are met

Indexing

A unique index is required on "eftrid"

Selling counters should have a non unique index on "sid" to assist performance of end of day and reprinting receipts.

Archiving

This table is automatically archived as sales are deleted using the sale archive process in Point Of Sale

It is safe, but not recommended, to delete random rows from this table if needed. The POS treats this information as should be present, but an error is not raised if information cannot be found. Deleting rows from this table will mean that reprinting receipts can no longer include copies of eftpos receipts

Using the Data

To select only good records (those where payment was accepted) the table needs to join to payments in order to eliminate the unsuccesful payment attempts

select e.* from eftposreceipts e, payments p, sales s where
	e.sid = p.sid and
	e.eftrid = p.eftpos_id and 
	p.sid = s.sid and
	s.phase = 1 and ....

For unsuccessful payments the values stored in the database are "as reported" by the eftpos engine being used. Often, the information reported is stale, so caution is required if attempting to interpret values. Fields like Pan, CardType, CardName, Receipt may be reporting working values and not actual values. The Pos records all information, even suspect data, as it may have forensic value at a future date.

To investigate errors the field responsecode is a good starting point. The response code is an internal status code about the payment that most eftpos subsystems return.

select responsecode,count(*) from eftposreceipts group by responsecode