Fieldpine Logo Documentation Home  

EftposReceipts PHYSICAL Table Structure

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.


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


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


FieldNameDatatypeMeta InfoGnapDescription
sidlong105/EInternal sale number allocated to this sale. References Sales.sid
stannumber103/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.
cardtypestringThe card type as reported by the EFT software.
card4stringLast 4 digits of eft cardnumbers, if enabled, encrypted, to aid auditing. Generally easier to use Right(4) on PAN field if available
datetimedatetime102/sThe time on the PC when this transaction was recorded
txncodestringThe EFT subsystem transaction code
txntypestring:8106/sType of transaction performed. Exact value varies according to eftpos subsystem in use.
amtpurchasemoneyThe amount of the transaction processed as a purchase component.
amtcashstringThe amount of the transaction processed as a cash out component.
amttipstringThe amount of the transaction processed as a tip component
messagetypestring The messagetype used by the EFT subsystem in bank communication. Message types are defined by bank hosts and vary by EFT subsystem.
accountstringThe account number selected by the user to pay the transaction.
datafieldstringThe optional datafield parameter returned by the host. This is not used in the POS
caidstringThe EFT CAID parameter.
catidstringThe EFT CATID parameter.
txnrefstring 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.
authcodestringThe authorisation code returned, if available.
responsecodestringThe response code returned from the host. Values and meanings depend on eftpos interface in use, although values are somewhat standardisded
txn_durationnumber107/ETime the eftpos transaction took in seconds
panstring:24104/3Customer 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
track1stringThe 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
track2stringThe 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
responsetextstringThe text from of the response from the acquirer
tdatestringThe transacation date as reported by the EFT subsystem, not the POS date
merchantstringThe merchant used for the transaction
totalcashstringTotal cash amount
enabletipstringIndicates if tipping mode was enabled
dateexpirystringThe card holders expiry date, if present
datesettlementstringThe settlement window this transaction belongs too
receipt0 /1/2/3/4stringCopy of the eftpos receipt, with longer string broken over several fields.
dayseqnumnumberCash drawer day sequence number.
paysequencenumberThe payment.sequence that this record relates too. Numbers can sometimes be reused if certain abnormal conditions are met


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.


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

ResponseCode "00" and "08" indicate success. More information on codes.