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 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 Products_category0 Products_category1 Products_costhistory Products_inventory Products_inventory_changes Products_inventory_point Products_overlay 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 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 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_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

Products Database Table

Distrbuted Access: Lanes( read insert edit delete CanSync Edits-require-TUBT )

Fields

pid
#100, Datatype: Integer
description
#101, Datatype: String 64 bytes
Shopper friendly description of item as might be printed on receipts and invoices
shortname
#102, Datatype: String 20 bytes
A short product description that can be used where space to print may be limited
unitprice
#103, Datatype: Money/Currency
Retail Price. List/base price as shown when price is requested, but may not be actual price charged to customer. This price is typically tax inclusive
r_spid
#104, Datatype: Integer
plucode
#105, Datatype: String 24 bytes
PLU code used by retailer to quickly locate a product. PLU codes are generally considered retailer specific although some industry standardisation does exist.
r_depid
#106, Datatype: Integer
Department Id used to group this product into a broad area
costprice
#108, Datatype: Money/Currency
Last known cost price of single item, typically exclusive of tax
PurchaseControl
#109, Datatype: Integer
pomenabled
#110, Datatype: Integer
mesurement
#111, Datatype: Integer
Unit of measure for this product. Is the product a 'unit' or measured in Kg, gm, L, time ....
tax1flag
#113, Datatype: Integer
mmid
#114, Datatype: Integer
dmid
#115, Datatype: Integer
Unit of measure to be used for display purposes.
bomid
#118, Datatype: Integer
u_fre
#147, Datatype: Money/Currency
Last known freight allocation for this product, exclusive of tax
priceband1
#148 An alternative price step that allows customers or stores to use alternative 'bands'
u_pac
#149, Datatype: Money/Currency
Last known packaging allocation for this product, exclusive of tax
sellflags
#150, Datatype: Integer
NoDiscount
#170, Datatype: Integer
pcat0
#172, Datatype: Integer
pcat1
#173, Datatype: Integer
stockimportance
#178, Datatype: Integer
A number from 1 to 10 indicating broad level of importance of this product. This value is manually set and often used as a broad sorting order
stockflags
#179, Datatype: Integer
WebGroup
#182 The broad group this product should be placed on an eCommerce site
supplysource
#196 Where is product broadly supplied from
Publish
#198, Datatype: Integer
Allow product to be visible to external public systems such as customer websites
Physkey
#210, Datatype: String 44 bytes
poscommand
#218, Datatype: String 240 bytes
priority
#220 Relative priority for ordering and stock control purposes. Products can be assigned to "Core", "On Special", "Other", "Retiring" or "Normal".
OrderMessage
#221 A message that is shown when item is being ordered. Allows communication between a stock controller and ordering staff on a per product basis
NoOrder
#222 This product cannot be ordered, it has been manually blocked. Typically used on products you wish to retire
MinOrderQty
#223 A minimum quantity of items that can be ordered. This value is the retailers minimum and suppliers may apply different minimums.
MaxOrderQty
#224 A maximum quantity of items that can be ordered on a single order. This is the retailers maximum, not a supplier maximum. Typically used to control over ordering.
retire_startdt
#225 The date this product was placed in retirement state
retire_enddt
#226 The date this product will end retirement and enter the deleted state
removaldt
#227 The date after which is item can be physically removed from remote systems.
shelflabel_line1
#228 The first line to be placed on a shelf label
shelflabel_line2
#229 The second line to be placed on a shelf label
shelflabel_line3
#230 The third line to be placed on a shelf label
perishable
#231 Indicates that product is perishable
polarised
#232 Indicates this item is polarised
mirrored
#233 Indicates this item has mirror finishing
marketingcolor_1
#234 A descriptive colour used for marketing descriptions
marketingcolor_2
#235 A descriptive colour used for marketing descriptions
marketingcolor_3
#236 A descriptive colour used for marketing descriptions
marketingcolor_4
#237 A descriptive colour used for marketing descriptions
placement
#238 A general indication of where this item might be found in a store

Possible Values

ValueDescription
1Bulk
2Cabinet
3Checkout
4Display Case
5Drawer
6End Cap
7Freezer
8Outside
9Promotion
10Rack
11Roof
12Shelf
13Wall
14Window
41Other #1
42Other #2
43Other #3
44Other #4
45Other #5
r_u_certid
#239 Organic Certification
PubUrlInst
#240 An internet URL containing installation instructions. Typically used as part of receipts to aid customers
PubUrlTerms
#241 An internet URL containing terms and conditions for warranties or guarantees. Typically used as part of receipts to aid customers
PubUrlUpsell
#242 An internet URL containing additional offers for purchasers of a product. May contain accesories (eg "Carry case", "Desktop stand") or spare parts ("Replacement bulb").
PubUrlMarketing
#243 An internet URL with marketing details of this product. Typically would be included on a quotation to refer them to you website.
OrderIcons
#244 Bitmask indicating additional icons to be displayed when on ordering screens, where supported.
rve
#249, Datatype: Double
Internal system field used for replication, ignore this field.
RmSystemXor
#250
RmSystem
#251, Datatype: String 44 bytes
NationalProduct
#252
ProcessFlags
#253
comments
#256, Datatype: String 250 bytes
Internal general purpose product specific comments
rvel
#257, Datatype: Double
Internal system field used for replication, ignore this field.
priceband2
#302 An alternative price step that allows customers or stores to use alternative 'bands'
priceband3
#303 An alternative price step that allows customers or stores to use alternative 'bands'
priceband4
#304 An alternative price step that allows customers or stores to use alternative 'bands'
priceband5
#305 An alternative price step that allows customers or stores to use alternative 'bands'
priceband6
#306 An alternative price step that allows customers or stores to use alternative 'bands'
priceband7
#307 An alternative price step that allows customers or stores to use alternative 'bands'
priceband8
#308 An alternative price step that allows customers or stores to use alternative 'bands'
priceband9
#309 An alternative price step that allows customers or stores to use alternative 'bands'
priceband10
#310 An alternative price step that allows customers or stores to use alternative 'bands'
priceband11
#311 An alternative price step that allows customers or stores to use alternative 'bands'
priceband12
#312 An alternative price step that allows customers or stores to use alternative 'bands'
PageYield
#313, Datatype: String 48 bytes
Number of pages this product can print or produce
ManuPartCode
#314, Datatype: String 250 bytes
The part code assigned by the original manufacturer
PublishType
#315, Datatype: Integer
A coded number indicating how this product should be used on a website. 0=normal selling item. 1=display purposes only, cannot be added to shopping trolleys
WebSearchRank
#316, Datatype: Integer
Suggested ranking order to display on a web search page. 1=highest
SellingMsg
#317, Datatype: String 250 bytes
Simple text message displayed on screen when item sold
WebsiteControl
#318 Free form text that is sent to website as an indicator for programs. Do not use for general product details
WebDisplayRank
#319 Suggested display order to display on a web. 1=highest/first
ReorderLevel
#320, Datatype: Integer
ReorderMax
#321, Datatype: Integer
glcode
#322
franchiseprice
#325, Datatype: Money/Currency
Franchise Retail Price. Price recommended by head office or franchise owner. This price is not used as part of selling process directly.
proddef
#326, Datatype: Integer
vintage
#327
ordercategory
#328
globalkey
#329
glacct
#330
gldesc
#331
NonStock
#332
maxpurchase
#333
nocredit
#334
ForeignPrice
#335
ForeignCurrency
#336
SitePrice1
#337
SitePrice2
#338
SitePrice3
#339
TransferPrice
#340
LandedPrice
#341
ExtractFlags
#342, Datatype: Integer
decimalplaces
#346, Datatype: Integer
Overrides number of decimal places to print for a product

Possible Values

ValueDescription
0Use system default
32None
331 decimal place
342 decimal places
353 decimal places
364 decimal places
profilevisibility
#347, Datatype: Integer
Bitmask that disables this product from appearing in a lane depending on the lanes profile
model
#348, Datatype: String 40 bytes
shelflabelformat
#349, Datatype: String 44 bytes
r_depid2
#350, Datatype: Integer
Department Id(2) used to group this product into a broad area
r_depid3
#351, Datatype: Integer
Department Id(3) used to group this product into a broad area
r_depid4
#352, Datatype: Integer
Department Id(4) used to group this product into a broad area
length
#353, Datatype: Integer
Length in mm of product
width
#354, Datatype: Integer
Width in mm of product
height
#355, Datatype: Integer
Height in mm of product
shippingcubic
#356, Datatype: Double
Shipping size in cubic metres
ItemColor
#357, Datatype: String 40 bytes
Color of the item itself. A red pen packaged in a blue box would have red in this field
Cartridge
#358, Datatype: String 40 bytes
Name of the cartridge used by this product
CategorisationFlags
#359, Datatype: Integer
internalsupporturl
#361, Datatype: String 240 bytes
A url reference for this product to an internal web page that may contain additional information
bluelight
#362, Datatype: Integer
Mark a product as having blue light attribute. Typically used in the eyewear industry
releasedate
#363, Datatype: DateTime
The date this product is released into your store. Typically used for eCommerce sites
AvailableFromDate
#364, Datatype: DateTime
The date this product is available for purchase. Typically used for eCommerce sites. This date does not stop the POS from selling the item, but a future release might prompt for verification
ClassificationFlags
#365, Datatype: Integer
r_depid5
#371, Datatype: Integer
Department Id(5) used to group this product into a broad area
r_depid6
#372, Datatype: Integer
Department Id(6) used to group this product into a broad area
r_depid7
#373, Datatype: Integer
Department Id(7) used to group this product into a broad area
r_depid8
#374, Datatype: Integer
Department Id(8) used to group this product into a broad area
supplierrrp
#375, Datatype: Money/Currency
RRP as outlined by a supplier or manufacturer
wholesaleprice1
#376, Datatype: Money/Currency
A wholesale price, whatever that means within your environment
wholesaleprice2
#377, Datatype: Money/Currency
A wholesale price, whatever that means within your environment
wholesalePrice3
#378, Datatype: Money/Currency
A wholesale price, whatever that means within your environment
wholesaleprice4
#379, Datatype: Money/Currency
A wholesale price, whatever that means within your environment
rxable
#380, Datatype: Integer
Can this item be used as part of a prescription

Possible Values

ValueDescription
0Unknown
1Yes
2No
3Probably
casephyskey
#381, Datatype: String 44 bytes
Product that is the case of this item
warranty
#382, Datatype: String 140 bytes
Short warranty description as might be printed on customer receipts
manufacturerphyskey
#383, Datatype: String 44 bytes
The original manufacturer of this item
GatewayCrossRm
#520, Datatype: String 220 bytes
GatewayFormula
#522
VisibilityFormula
#523 Formula that controls if product is visible or not
u_AdditionalInfo
#20001
u_BulkQty
#20002
u_r_cartid
#20003, Datatype: Integer
checksheetreqd
#20004
LTSAPmtNoReqd
#20005
RegnoReqd
#20006
f8389009,u_brandname
#8389009, Datatype: String 64 bytes
Brand Name. The actual textual description of the brand.
f8388814
#8388814, Datatype: Integer
Weight, in Grams
MainColour
#8388908, Datatype: Integer
Main colour of item packaging if only one single colour was to be named. A red pen packaged in a blue box would have blue in this field
KnownBrandId
#8389008 Reference to standard brand names. Rarely used
MadeFrom
#8389208 List of materials this item is made from
DesignShape
#8389308 Shape of the item
f8390617
#8390617 English. Descriptive text of item. Short. Expected to be less than 200 letters. Typical use is under reference images on menu pages
webgroup
#8390712
f8390716
#8390716, Datatype: Integer
Special indicator. Used by Websites to indicate product should be marked on special or other agreed purpose.
f8390717
#8390717 Out of Stock indicator. This indicator is manually set as an override to indicate to web sites that this product should be considered out of stock, regardless of any other stock level indication.
f8390718
#8390718 Only available instore indicator. Used to indicate to websites that this product should be marked in-store purchase only
f8390719
#8390719 Delivery Charge indicator. A coded value indicating levels of delivery charge.
f8390720
#8390720 New Product indicator. Used by Websites to indicate this is considered a "new" product for this retailer. The exact meaning and duration of "new" is left to the individual retailer to decide.
WebHighlight
#8390721 Highlight indicator. Used by Websites as a suggestion they should prioritise or highlight this product.
f8391117
#8391117
f8392117
#8392117 English. Keywords for product search
f8391608
#8391608, Datatype: Integer
Gluten Free Indicator
f8391612
#8391612, Datatype: Integer
Vegan Indicator
f8392608
#8392608 Fair Trade Indicator.
f8392609
#8392609 Out of Season Indicator. Set to indicate that a seasonal product is currently out of season.
NZMade
#8392772 New Zealand made indicator
lenstype
#8393608
as_nzs_1067_2003
#8394608, Datatype: Integer
f8394609
#8394609 Prompt Vehicle registration number
f8394610
#8394610 Prompt LTNZ Reference number
f8394611
#8394611 Prompt for external document serial number. This is used for serialised forms that are used as part of selling this item.
f8394613
#8394612 Prompt Customer Details
f8408610
#8408610 Person gender this product is mainly designed for. This is about the product, not the customers gender preference

Possible Values

ValueDescription
AUnisex
FFemale
MMale
TTransexual
fslightly female
mslighly male
tslightly transexual
WebHighlightflags
#8480000
WebsiteTag
#8481000, Datatype: String 48 bytes
A short phrase or single word 'tag' that might be applied to a product. Examples might be 'new' 'on special' 'reduced'