Sales Table

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

Overview

Contains a single header row for each sale. Holds information such as start and end times as well as useful rollup information like saletotal value.

Related Tables: Salelines Payments SalesDcid

Mesh

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

Columns

FieldName	Datatype	Meta Info	GNAP	Description
Sid	Number/32	Not null, primary key	100/E	A unique internal number allocated to each sale. This number is allocated in both the original trading lane (where it is unique to that lane) and again in central servers (where it is unique to that central server). When loaded in central servers, the original "sid" value from the lane is stored in the srcuidkey field.
startdt			109/s	The date and time this sale was first created.
completeddt			101/s	The date and time this sale was finally completed, can be NULL it the sale is still active.
Phase			108/E	A coded number indicated the state of the sale: 0 Sale is currently active in some form. Historic sales can rarely be "0" state if they are being actively editted. (Reference Data) 1 Normally completed Sale. For most reporting use you should select only phase=1 sales 5 Sale is currently parked 10,000 Sale is void and stored for audit purposes. 10,002 Sale is void and stored for audit purposes.
restartflags			110/E	Normally 0, but non-zero values are used as temporary flags for powerfail recovery.
purch_control_1			111/E	Not documented at this time.
purch_control_2			112/E	Not documented at this time.
identification			113/s	A comment field for each sale. Typically loaded from PastSales for storing post sale identification information, such as car registration numbers
rid			114/E	Replicate Id.
teller			115/E	The teller id, if known. Where more than one teller worked on a sale, this holds the last teller.
cteller	Number/32		116/E	The teller id, if known of the teller that CREATED the sale.
cid			117/E	Customer Id, zero if no customer known for this sale.
PrintCount			118/E	Use deprecated.
ctv			119/s	Not documented at this time.
PaymentTryCount			120/E	A counter of how many payments we have attempted to add to the sale.
originalsid			121/E	Holds the original sid number as transfered from a lane system, while the sid field is renumbered.
lane			124/E	The lane number generating this sale
location			125/E	The location number where this sale was generated
saletotal			102/s	The total value of the sale, when it was completed. This value is also present in the salelines table, but is opionally placed here so that searchs for sales of certain value can be quickly found.
Comments			133/s	General Comments for sale. If more space is required than available in the field, then extra fields named comments1, comments2 may be used.
OrderNo			/	The Customers order number for this sale
CheckSheetNo			/	When external paperwork is being used as part of the sale process, this field stores the external paperwork number.
Drawer			/	The last cash drawer that was opened for this sale. Used in multiple cash drawer configured systems to record which cash drawer was actually used for this sale. This field is not officially supported (V2.4.4.27)
srcuid			122/E	The remote system that generated this sale.
srcuidkey			123/E	The "sid" from the lane that sent the sale to this server
tfrcnt			126/E
acctfrdt			127/s
billingrun			128/E	Which billing run and statement this sale was processed on. Loaded by account statement runs.
lastpriceband			129/s
externalid			130/s	A unique identifier provided from external systems where sales are created on other systems first. Most eCommerce interfaces will supply an externalid value which holds the eCommerce sale identifier.
salestype			131/E	Broad indicator indicating the type of the sale such as instore or online. (Reference Data)
ressrcuid			132/E	The remote system that reserved a sale. This is valid where central servers are holding and ditributing parked sales to offline lanes.
tax_totinc			103/s
tax_totex			104/s
tax_tr1			105/s
tax_tr2			106/s
tax_tr3			107/s
flybuy	string: 16+	Zero length allowed	134/s	The customers Fly-Buys card number if known. This is the complete 16 digit card number not just the raw barcode.
flybuysource	Number/32	Default 0	135/E	How the Fly-Buys card number was collected. A coded number. 0 or null No Flybuy captured 1 Swiped on Direct Payment Solutions Pinpad (magswipe) 2 Manual entered 3 Copied from a related sale. Typical on a return sale 4 Barcode scanned 5 Reserved (for QR scan) 6 User removed from sale. ie Was known before but editted to be unknown 7 Copied from a known database source, typically customer reference tables
promocode	Number/32	Default 0	136/E
physkey	string			Unique Physical key of this sale. This number is globally unique

Indexing

The primary key is "sid" and a unique index should exist on this column. This is the only field that can guarantee a unique index, do not create unique indexes on other fields/combinations unless "sid" is part of the index.
On trading lanes, an index on "completeddt" should be present to ensure End Of Day screen performance is acceptable. This index must allow duplicates.

Joins to Other Tables

Customers

There are two fields that link the customers table to the sales table. sales.cid which joins to customers.cid and sales.physkeyc which joins to customers.physkey All systems have "cid" field, but newer versions will also have the physkey fields that are used for fully decentralised operation. The following documentation describes using inner joins but you may prefer to use outer joins as customer is typically an optional attribute of a sale.

If your environment only creates customers in a central location and distributes these to stores and lanes then the join will be:
("The Original Join")

	select ... from sales, customers where sales.cid = customers.cid

If your environment uses distributed customer creation, then the physkey join is considered stronger in a decentralised system however older records will not have these values which complicates the join. The basic join is (example only, not recommended)

	select ... from sales, customers where sales.cid = customers.cid or sales.physkeyc = customers.physkey

The above join will work correctly the vast majority of the time, but will be incorrect under certain timing conditions, especially when sales transactions arrive before the customer create transaction, or where the customer create gets renumbered (a new "cid" is allocated) by a head office server.

The full expanded form of the join is as follows. This will work correctly in all environments. Fieldpine use variations of this join internally.
("The Transistion Join")

	select ... from sales, customers where 
		( sales.physkeyc = customers.physkey and
			( sales.physkeyc is not NULL and sales.physkeyc > '') )
	or
		(sales.cid = customers.cid and
			sales.cid is not NULL and sales.cid <> 0 and
			sales.physkeyc is NULL or sales.physkeyc  <= '' )

If your SQL reporting is working on exported databases and not the main transactional database directly, then it is highly likely that physkey values have been populated for all rows, in which case the join is very simply
("The Future Join")