SaleLog 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.

Goto: Documentation Home

SaleLog records major events performed against a sale, such as cancel, park or photo capture. It does not generally record anything for a normal/simple sale flow that simply sells items in one go without issue

Salelog primarily holds logging information about a sale, but does not contain information that changes sales. The table SaleChangesAfterComplete holds transactional information after the sale was finalised, and this information can be read back by the POS to understand sales.

Columns

FieldName	Datatype	Meta Info	Gnap	Description
Physkey	string:44,zerolen	Required	100/s	Unique key assigned to this record
Physkeys	string:44,zerolen		101/s	Reference key to original sale. The join to sales is "sales.physkey = salelog.physkeys". There can be multiple rows in this table with the same Physkeys value.
LaneSid	long		102/E	Original sale id on the source lane.
Dt	datetime		103/s	Date/Time this record was created
Code	long		104/E	A coded number indicating what type of record is being stored 20. Sale was parked 21. Sale was un-parked (from P2016) 22. Receipt was reprinted manually from PastSales. (reserved for future use) 50. A Photo reference taken when the first item was sold. param1 contains the camera number and fileref contains the name of the original capture file 100. A photo was captured as a prepay/SVC card was used to pay a sale 101. A photo was captured as a prepay/SVC card was topped up or purchased 200. Status of network transmission to offsite. 10002. Sale was voided. Param1 contains the source of the delete request
Tid	long		107/E	Pos Login Id#
Srcuid	long		109/E	Unique id referencing lane number
param1	long		105/E	General purpose value. Meaning depends on code value
fileref	string:200,zerolen		106/s	General purpose value. Meaning depends on code value
ErrorMessage	string:250,zerolen		120/s	Error message. If the operation encountered an unexpected error the details of the error might be recorded here.

Indexing

❖ A unique primary key should exist on the physkey field.
❖ You may wish to index Physkeys as an alternative for performance reasons.

Remarks

Network Transmission to Offsite

This record is stored after a network attempt is made to send a sale to an external system. The fields contain the following transaction specific information

fileref

Contains the hostname that we sent too

param1

Holds the HTTP response code received

ErrorMessage

Technical error informaton if an error was encounterd. Most offsite transmissions will retry for a period of time so transient errors may be logged that do not indicate the final succeed/fail status. An ErrorMessage of "Offsite Transmission Expired (4)" indicates that the record failed to send in the required time limit and was discarded.

A record is not stored for every attempt to send, the following rules are used to determine if a record is stored or not:

If this is the first attempt it will be recorded
If the record expires and is discarded it will be recorded.
If the attempt is the final attempt it will be recorded. Final attempt is detected using the HTTP response codes of (>=200 and < 299) or 404. 404 is included as a final code as there is no point in continuing to attempt transmission.
Otherwise, the sending program maintains a list of failures and will not send another failure message for that data. The list is in memory, so if the program restarts it will consider this the first attempt and record a message.

To detect a final "good" status, you can query the salelog table for " (param1 >= 200) and (param1 <= 299) "

To list records that had a final failure status use a query similar to the following example which shows one possible method. You will need to extend this query if you have non simple conditions, such as multiple offsite targets

select * from salelog a where code=200
    and a.dt = (select max(dt) from salelog b where b.physkeys = a.physkeys and b.code=200)
    and not (a.param >=200 and a.param <= 299)

Sale was voided

Indicates a sale was voided at users request

param1

Holds a reference to where in the code the decision to void the sale was taken.

Value	Description
10	The PosCommand sale(quit) was executed. There can be several places where this command can be issued from.
20	The user pressed "escape" to void a sale, and that sale had zero items. Some users will often delete all items first and then delete a sale.
30	A paid sale was voided under user control. The ability to do this is disabled by default on all systems and is a hidden configuration option, so this value will be rarely seen.
40	User pressed "escape" and pressed yes when asked to confirm the cancel sale operation.
109	All active sales were voided using the "quitall" PosCommand function. This PosCommand is rarely used and provided for special case environments where a skilled operator may not be present