Physical Database Documentation
 
Library Physical Database

Commonly Used Tables

Products Salelines Sales User Data Accounts Contactlog Eftposreceipts Eftposreceipts2 Locations Payments Pricemaps Reference Data Statetable System Data R_paymenttypes Salelog Usagetoken Analytics Data Ds_customer_a Ds_customer_b Ds_product_a Ds_sales_a Other Tables Accountadjustments Accountallocations Accountchangeslog Accountshumanrequired Accountsupport Activitylog Advisor_actions Advisor_tests Allocatedrights Apikeys Apikeys_access Apikeys_lock Apikeys_resource_locks Apitxnlog Apitxnlogdata Asndetail Asnheader Asnheader2 Asnitem Attribute Attributedefinitions Authcodelog Autobalancerecon Autobalancereconlog Backgroundlog Barcode_patterns Barcodefailurelog Barcodeinvalid Barcodeoneoff Barcodeoneofflog Barcodepatterns Barcodes Barcodes_ean99 Barcodes_trade Barcodeusedlog Bisho_status Bistx Bookingres Bookingsales Bookingscans Bookingsessions Bulkloadlog Cabinets Callerid Cameradef Carriers Cashdraweractionlog Cashdrawerlog Cautionlist Ceres_productlist Chatheader Chatmessage Companies Configui Contact Contactdetails Contactdetailsmap Costchangesseen Counters Counters_log Createapps Customercardgroups Customercards Customermessages Customers Customersdcid Customersdcidrpt Customersdcids Customersdcidslinks Customerstate Customertracks Customertrackspending Decisiontreeheader Decisiontreelines Demographic Discountfilter Discounts Discountsmanual Docuheader Doculine Documentformats Documentparsematch Documentprocessing Documulti Doesnotexist Drivercashlog Ediauditlog Employees Eventaudit Expectedsales Expensetypes Faults_detail Fdlonline_enablematrix Feectl Fees Floatlog Flow_anchor_raw_string Fmea Fpuicontrol Fpuicontrol2 Franchise_barcodes Franchise_departments Franchise_products Franchise_products_original Futuredatachanges Futuretxns Gateways Geoareas Globalvalues Goodsinheader Goodsinhistory Goodszeropoint Headerreceive Headerreceive_head Heartbeat Hiregroup Httpurllog Idset2 Idsetctl Idsetdata Idsetdata2 Inventorylevels Ldflog Linkedserver Livepayments Livesalelines Livesales Localsaleflags Location_area Location_cab Location_tradingdays Longdatavalues Machinesuser Macro Maintlist Marketingprogram Measurements Media Mediacontrol Mediafiles Mediaresource Mediastorage Membership Membershipdata_all Messageformat Messageq Messageuser Messagingt Modifiergroups Modifierheaders Modifieritems Modifierpriceheader Modifierpriceline Modifierproductgroups Modifieruplifts Modififergroups Multichain_sourcemap Multilane Multilink_all Multiplu Networklane Networklane_environment Networklane_status Networklanecode Networklaneconfig Ordercategories Ordersreceived Ordersreceivedlines Orderstxns Overflow_gnap Overflow_tmpr Perfcounters Pidcoststatus Plicence Poheader Poheader_head Poheaderreceive Poheaderreceive_head Poline Poline_head Poms Posaudit Posendtypes Poshipping Poslat Posrelationships Prefsaleitems Pricebandmap Pricebands Pricebookcurrent Pricebooksource Pricebookspecialsheader Pricebookspecialsline Pricebufferdist Pricechangepending Pricegroups Pricehistory Pricemapsexceptions Priceoptions Pricerevision Priceused Printformats Printing_pending Printingformats Printingrules Procuserlogicline Product_cab_active Product_dispatch Product_dispatch_error Product_packsize Productattribute Productconnections Productconnectionstype Productcontrollog Productkitdef Productkits Productkitsext Productlocspacemap Productlocspacesets Products1 Products2 Products3 Products5 Products_category0 Products_category1 Products_costhistory Products_inventory Products_inventory_changes Products_inventory_point Products_manufacturer Products_overlay Products_properties Productsbom Productsbomheader Productsinst Productslocation Productslocationbis Productsordering Productspacesets Productssale Productsupplier Productsupplierdist Productvariant Profanity Purchasecontrols Purchaseorderprocessing Qoh_pub Qtydiscounts Quickcodeaudit R_colors R_departments R_departments2 R_departments3 R_departments4 R_departments5 R_departments6 R_departments7 R_departments8 R_fashiongroups R_pgm_periods R_returnreasons R_sizes R_styles R_writeoffs Rcvstock Rcvstockline Receiptextn Remotecommands Remotecommandslog Rewardmovements Rewardprogramoptions Rewardprogrampids Rewardprograms Rewardprogramspids Rewardtxns Rewardtxnslog Sale_delivery Salealloc Salechangesaftercomplete Salechar Salecomments2 Saleinfo Salelines_cost Salelines_deleted Saleloadtrace Saleloadtraceline Sales_errors Sales_picking Sales_recall Sales_standingorders Salesdcid Saleservercommands Salesprofilesingle Salestaff Salestatistics Salesunpaid Salevar Salevar2 Samplelist Scriptmap Scripts Seritem Seritemlog Servercommands Settings Settingsactive Settingsdist Settingskv Settingskvdist Settingsmembership Settingspolicy Settingspolicymap Settlecashdetail Settlepaydetail Shelfvisit Shifttrack Ss_pricebuffer Staff Staff_eauth Staff_magid Staff_ownership Staff_rights Staff_stats Stafftablecontrol Staffused Staffused_log Statementhistory Stickies Stock_fifo Stock_lifo Stockcount Stockholdrequests Stockmovements Stockpoints Stockreceipts Stocktakedetail Stocktakedetail2 Stocktakemaster Stocktakesnapshot Stocktakesnapshot_tracked Stocktxnhdr Stocktxnitem Subscriptions Supplier_inventory Suppliermessages Supplierpricebook Supplierpricebookline Suppliers Suppliers2 Suppliersautoorder Svcaccount Svcards Svctxnhistory2 System_status Tabledisthead Tablemgmt Taxrates Telephone_number_plans_override Ticketpricerequired Ticketpriceshowing Timeclock Timeclocklog Timeschedule Tmpr_log Tnotes Topology Tracelogic Trackeditem Trackeditemobservation Traffic_counts Transit_inventory Transit_inventory_lines Transit_request Transit_request_lines Triggers Tushlog Typesuser U_cw_cartridges U_cw_printers Upstream Usagerecord Userlogicvariable Valuedomainrule Vehicle_makes Vehicle_models Versioncontrol Verticalsecurity Voucher Voucherprogram Webgroup Webhooks Webpages Webpagesrules Worklist Workqueue Workqueuetx Internal Technical Tables B2backup Customers2 Customers3 Dbcomm Emailinrules Firewallrules Keylease Keyleasefreelist Localdb_datpq Localdb_datpq_inbound Localdb_lanetx_queue Localdb_membership_replicate Localdb_pinb_cache Localdb_receivequeue Localdb_replicationstatus Localdb_salestechnicalaudit Localdb_sendqueue Localdb_sendqueuedata Localdb_settings Localdb_values_write Localdb_version Loginkeys Products4 Replicationcontrol Salememory Sales2 Serverprocessing Serverprocessingdates Tubtlocal Wipheader Wipline Deprecated Tables Auditsystem Pictures Retired Tables Biscontrol Cjl Eftpos_txns Linkproductspictures Pomprodlink Experimental Tables Keytranslate Posscriptpage Posscripttrigger

Sales Database Table

Distrbuted Access: Application controlled

Fields

sid
#100, Datatype: Integer
completeddt
#101, Datatype: DateTime
Date the sale is completed and will not undergo any futher change. This value is in the local time of the store. See also Completeddtutc
saletotal
#102, Datatype: Money/Currency
tax_totinc
#103, Datatype: Money/Currency
tax_totex
#104, Datatype: Money/Currency
tax_tr1
#105, Datatype: Money/Currency
tax_tr2
#106, Datatype: Money/Currency
tax_tr3
#107, Datatype: Money/Currency
phase
#108, Datatype: Integer
State of the sale, as in is it complete, parked, void etc. When selecting sales for reports you will typically only want to select those where phase=1

Possible Values

ValueDescription
0Active on POS
1Normal completed sale
2Manual completion override
3Suspend for later, requires customer
4Reserved for payment handling substates
5Temporary sale park
6Transfer to another lane for completion
7Copy of sale which cannot be altered. Used internally
8Stock write-off
9Manufactured
10Appro
11Pickup
12
200Sale awaiting stock picking. Typically an eCommerce sale
201Online sale transmitted directly to counter Pos
202Sale awaiting verification
203
300Active customer shopping cart
10000Trash. Place in holding for audit purposes, but ignore. All stock has been returned.
10001Quote. As trash, but sale was used to produce a quote
10002Trash via delete attempt.
10005Receiving
10006Reserved SaleId. Used to reserve an id for a lane to use in the future
10007Invisible. Known sale id but unable to read detail for some business rule reason
10008Support Delete. Internal code used by support to mark sales for physical deletion
startdt
#109, Datatype: DateTime
restartflags
#110, Datatype: Integer
Bitmask containing internal processing flags.

Possible Values

BitmaskDescription
1Eftpos transaction in progress
8Stocktake mode
16Layby mode
32Locked
64Transit mode
128Autocomplete sale
0x01000000Sale number was locally allocated as network offline
rid
#114, Datatype: Integer
Teller
#115, Datatype: Integer
cid
#117, Datatype: Integer
PrintCount
#118, Datatype: Integer
Status of receipt printing and sending. Technically a bitmask, however the lower 8 bits are a count of prints
originalsid
#121, Datatype: Integer
srcuid
#122, Datatype: Integer
srcuidkey
#123, Datatype: Integer
location
#125, Datatype: Integer
billingrun
#128, Datatype: Integer
externalid
#130, Datatype: String 32 bytes
salestype
#131, Datatype: Integer
Number indicating the type of sale, such as where or how the sale was initiated

Possible Values

ValueDescription
0Counter sale
2eCommerce/web sale
4A reduced price sale, often used for trade sales. This value is not used for a normal retail sales with discounts, they remain normal sales
8This sale was created as part of editting another completed sale. It holds the financial changes applied to the original sale(s)
16Telephone sale
17Email sale
18WebChat sale
19Voice Assistant sale
20Txt message sale
21Standing Order
22Usage Generated
258Sale created from an online web presence that is managed for a franchise group
Comments
#133, Datatype: String 250 bytes
posversion
#142, Datatype: String 24 bytes
physkey
#145, Datatype: String 54 bytes
physkeyc
#146, Datatype: String 44 bytes
Physkey of the customer record
stocklocation
#149, Datatype: Integer
phys_location
#161, Datatype: Integer
Parent
#162, Datatype: Integer
rm0
#164
rm1
#165
rm2
#166
rm3
#167
createapp
#170, Datatype: Integer
Coded number indicating the program, app or web page that created this sale
createip
#171, Datatype: String 90 bytes
The source IP address of the machine or browser client that created this sale.
quoteexpiry
#174, Datatype: DateTime
Date/time this quotation expires and will no longer be valid
GeoLatitude
#175, Datatype: Double
Latitude of user or Pos capture program when sale recorded
GeoLongitude
#176, Datatype: Double
Longitude of user or Pos capture program when sale recorded
GeoAltitude
#177, Datatype: Double
Altitude of user or Pos capture program when sale recorded
completeddtutc
#180, Datatype: DateTime
Date the sale is completed and will not undergo any futher change. This value is a UTC date. See also Completeddt
relatedphyskey
#181, Datatype: String 44 bytes
relatedcause
#182, Datatype: Integer
Coded number indicating what or why the field relatedphyskey has a key value

Possible Values

ValueDescription
1This is a return sale. relatedphyskey is the original sale
locationstart
#183, Datatype: Integer
Location where this sale first started
locationend
#184, Datatype: Integer
Location where this sale finally completed
orderno
#185, Datatype: String 50 bytes
Customers order number
rvv
#186, Datatype: String 129 bytes
Internal replication information. Do not edit
custsource
#187, Datatype: Integer
How the customer was identified and selected to this sale

Possible Values

ValueDescription
0Unknown or invalid
1PosCommand
2Customer Barcode
3Lookup screen
4SVC Detected
5Return
6Lookup, old screen
7SFF
8Typed
9Another database table
22Default setting
rve
#188, Datatype: Double
rvel
#189, Datatype: Double
srcuidchild
#190, Datatype: String 96 bytes
Internal column used to manage active sales. This value is no longer required when a sale is completed or void and may be reset to NULL to save space if desired
randomphyskey
#191, Datatype: String 90 bytes
A random string identifying this sale. Typically used on public facing applications
randompassword
#192, Datatype: String 24 bytes
A random password associated with this sale
indicators
#193, Datatype: Integer
Bitmask containing various state indication flags

Possible Values

ValueDescription
1Not fully complete
2Disputed
4Work in Progress
deliveryphyskey
#300, Datatype: String 44 bytes
Reference to sale_delivery
CommentsInternal
#301 Comments for this sale that are internal use only
myob_extractdate
#302, Datatype: DateTime
date this sale was sent to MYOB
rmsystem
#6002, Datatype: String 44 bytes
x_ms_workflow_run_id
#6003, Datatype: String 250 bytes
serverprocessingstate
#6004, Datatype: Integer
Used on Head Office databases to track status of post sale processing. See ServerProcessing table for more information.

Possible Values

ValueDescription
0/nullAll processing has completed, or not using ServerProcessing
-1New sale, or processing in progress. See ServerProcessing table for exact status
-2Internal error code, not stored in database
rloaderplevel
#6005, Datatype: Integer
Version of application that loaded this sale into a multichain retail database