Fieldpine Logo Documentation Home  

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

Columns

FieldNameDatatypeMeta InfoGnapDescription
PrcidlongReporting Key122/EA unique id assigned to this row. This value is used to for reporting purposes and is not used as the main primary key for the table. It should be unique.
Physkeystring,44 zerolenDistributed Primary Key100/sUnique key allocated to this record for all time
pidnumber101/EProduct id
depidnumber102/EDepartment id
cidnumber103/ECustomer id
locidnumber104/ELocation id
prioritynumber105/EApplication priority, used as a tie breaker if multiple rows can match. Larger numbers indicate higher priority.
enddtdate106/sEnd date that this record will no longer be selectable. Only the date part of this field is used, any time supplied is ignored.
startdtdate107/sStart date for this record. It cannot be selected until this date. Only the date part of this field is used, any time supplied is ignored.
unit_pricemoney108/sAbsolulte price to set too.
pricepctdouble109/sDiscount to be applied to current price
tidnumber110/sTeller id
ridutcnumber111/s
commentsstring, 140 zerolen112/sInternal comments. This are not shown to customers
dowstring:7113/s Day of week mask. Used to set priceoption entries that should only apply on certain days of the week.

If a DOW mask is present (not empty), all days default to off unless present and enabled. To enable a field place a Y (or 'y' or '1') into its day position. Sunday is the first field, followed by Monday. We recommend capital Y and N as the mask characters for maximum reliability

Mask: YY - only allow on Sunday and Monday
Mask: NY - only allow on Monday
Mask: YNNNNNY - only allow on Sunday and Saturday
Mask: NNNYyNN - only allow on Wednesday and Thursday
Mask: 0111 - only allow on Monday, Tuesday and Wednesday

timestartdatetime114/sSets a start time for this pricing. Only the time component is used, and date is ignored.
timeenddatetime115/sSets an end time for this pricing. Only the time component is used, and date is ignored.
physkeylstring:48, zerolen116/s
OptionTypenumber117/sDefines the type of pricing information being defined.
  • 0 or null. the unit_price field contains a price to be applied to the base price
  • 1. The pricepct field should be applied to the base price
  • 2. The unit_price field is applied as a discount.
cflagslong118/EControl flags. A bitmask.
bit 1 is set for disabled/deleted records. This bit should only be set exceptionally, records outside start/end dates are already ignored.
bit 2 (P1837) Indicates that this price command should not be applied to products marked no-discount.
pvariantlong119/E Optional Product variant this can apply to. If variants are enabled the following rules are used
Selling Product VariantPriceOptions RecordEffect
00Applies.
0Not zeroNot applied. Sale variant unknown
Not zero0Applies. Record is for all variants
Not zeroNot zeroApplies if variants match

Indexing

A unique primary key should exist on the pmid field.

Remarks

The fields pid, depid, cid and locid should contain a specific value, or 0 to wildcard that field. So to set pid=65 in locid=67, you would also set depid and cid fields to zero.

If timestart and timeend are both specified, the timeend must be later than timestart, without wrapping. You cannot have a start time of 22:00 and an end time of 02:00 (2am the next morning). If you wish to cross midnight, two records are required, one with a start time of 22:00, and the other with an end time of 02:00

If unit_price and pricepct are both defined, unit_price takes priority and pricepct will be ignored

The POS uses the following style of SQL query to read this table when a product is being priced onto a sale.

	select ... from priceoptions where pid in (0,PID) and depid in (0,DEPID)
		and cid in (0, CID) and locid in (0, CURRENT-LOCATION)
		order by priority desc
Each record is then further checked against start and end dates and times.