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
|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)
|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|
|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.|
|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 (V22.214.171.124)|
|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|
|billingrun||128/E||Which billing run and statement this sale was processed on. Loaded by account statement runs.|
|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.|
|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
|physkey||string||Unique Physical key of this sale. This number is globally unique|
- 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
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")
select ... from sales, customers where sales.physkeyc = customers.physkey