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

FieldName

Datatype

Meta Info

Gnap

Description

Prcid

long

Reporting Key

122/E

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

Physkey

string,44 zerolen

Distributed Primary Key

100/s

Unique key allocated to this record for all time

pid

number

101/E

Product id

depid

number

102/E

Department id

cid

number

103/E

Customer id

locid

number

104/E

Location id

priority

number

105/E

Application priority, used as a tie breaker if multiple rows can match. Larger numbers indicate higher priority.

enddt

date

106/s

End date that this record will no longer be selectable. Only the date part of this field is used, any time supplied is ignored.

startdt

date

107/s

Start 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_price

money

108/s

Absolulte price to set too.

pricepct

double

109/s

Discount to be applied to current price

tid

number

110/s

Teller id

ridutc

number

111/s

comments

string, 140 zerolen

112/s

Internal comments. This are not shown to customers

dow

string:7

113/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

timestart

datetime

114/s

Sets a start time for this pricing. Only the time component is used, and date is ignored.

timeend

datetime

115/s

Sets an end time for this pricing. Only the time component is used, and date is ignored.

physkeyl

string:48, zerolen

116/s

OptionType

number

117/s

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

cflags

long

118/E

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

pvariant

long

119/E

Optional Product variant this can apply to. If variants are enabled the following rules are used

Selling Product Variant	PriceOptions Record	Effect
0	0	Applies.
0	Not zero	Not applied. Sale variant unknown
Not zero	0	Applies. Record is for all variants
Not zero	Not zero	Applies 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.