Fieldpine Logo Documentation Home  

Sales PHYSICAL Table Structure

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.


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


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


FieldNameDatatypeMeta InfoGNAPDescription
SidNumber/32Not null, primary key100/EA 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.
startdt109/sThe date and time this sale was first created.
completeddt101/sThe date and time this sale was finally completed, can be NULL it the sale is still active.
Phase108/EA 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.

restartflags110/ENormally 0, but non-zero values are used as temporary flags for powerfail recovery.
purch_control_1111/ENot documented at this time.
purch_control_2112/ENot documented at this time.
identification113/sA comment field for each sale. Typically loaded from PastSales for storing post sale identification information, such as car registration numbers
rid114/EReplicate Id.
teller115/EThe teller id, if known. Where more than one teller worked on a sale, this holds the last teller.
ctellerNumber/32116/EThe teller id, if known of the teller that CREATED the sale.
cid117/ECustomer Id, zero if no customer known for this sale.
PrintCount118/EUse deprecated.
ctv119/sNot documented at this time.
PaymentTryCount120/EA counter of how many payments we have attempted to add to the sale.
originalsid121/EHolds the original sid number as transfered from a lane system, while the sid field is renumbered.
lane124/EThe lane number generating this sale
location125/EThe location number where this sale was generated
saletotal102/sThe 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.
Comments133/sGeneral 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)
srcuid122/EThe remote system that generated this sale.
srcuidkey123/EThe "sid" from the lane that sent the sale to this server
billingrun128/EWhich billing run and statement this sale was processed on. Loaded by account statement runs.
externalid130/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.
salestype131/EBroad indicator indicating the type of the sale such as instore or online. (Reference Data)
ressrcuid132/EThe remote system that reserved a sale. This is valid where central servers are holding and ditributing parked sales to offline lanes.
flybuystring: 16+Zero length allowed134/sThe customers Fly-Buys card number if known. This is the complete 16 digit card number not just the raw barcode.
flybuysourceNumber/32Default 0135/EHow 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

promocodeNumber/32Default 0136/E
physkeystringUnique Physical key of this sale. This number is globally unique


Joins to Other Tables


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 > '') )
		(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")

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