Skip to content

Office Archive Database

Andy Theuninck edited this page May 29, 2015 · 2 revisions

__needs_initialization

Name Type Info
tdate datetime Indexed
store_id SMALLINT
register_no SMALLINT
emp_no SMALLINT
trans_no INT
upc VARCHAR(13) Indexed
description VARCHAR(30)
trans_type VARCHAR(1) Indexed
trans_subtype VARCHAR(2)
trans_status VARCHAR(1)
department SMALLINT Indexed
quantity DOUBLE
scale TINYINT Default=0
cost MONEY
unitPrice MONEY
total MONEY
regPrice MONEY
tax SMALLINT
foodstamp TINYINT
discount MONEY
memDiscount MONEY
discountable TINYINT
discounttype TINYINT
voided TINYINT
percentDiscount TINYINT
ItemQtty DOUBLE
volDiscType TINYINT
volume TINYINT
VolSpecial MONEY
mixMatch VARCHAR(13)
matched SMALLINT
memType TINYINT
staff TINYINT
numflag INT Default=0
charflag VARCHAR(2) Default=''
card_no INT Indexed
trans_id INT
pos_row_id BIGINT UNSIGNED Indexed
store_row_id BIGINT UNSIGNED PK Indexed Increment
trans_num VARCHAR(25)

Depends on:

  • dtransactions (table)

Use: This view presents simplified access to dtransactions. It omits rows from canceled transactions and testing lane(s)/cashier(s)

bigArchive

Name Type Info
datetime DATETIME Indexed
store_id SMALLINT
register_no SMALLINT
emp_no SMALLINT
trans_no INT
upc VARCHAR(13) Indexed
description VARCHAR(30)
trans_type VARCHAR(1) Indexed
trans_subtype VARCHAR(2)
trans_status VARCHAR(1)
department SMALLINT Indexed
quantity DOUBLE
scale TINYINT Default=0
cost MONEY
unitPrice MONEY
total MONEY
regPrice MONEY
tax SMALLINT
foodstamp TINYINT
discount MONEY
memDiscount MONEY
discountable TINYINT
discounttype TINYINT
voided TINYINT
percentDiscount TINYINT
ItemQtty DOUBLE
volDiscType TINYINT
volume TINYINT
VolSpecial MONEY
mixMatch VARCHAR(13)
matched SMALLINT
memType TINYINT
staff TINYINT
numflag INT Default=0
charflag VARCHAR(2) Default=''
card_no INT Indexed
trans_id INT
pos_row_id BIGINT UNSIGNED Indexed
store_row_id BIGINT UNSIGNED PK Indexed Increment

Use: This is IT CORE's transaction log. A rather important table.

A transaction can be uniquely identified by: date + register_no + emp_no + trans_no A record in a transaction can be uniquely identified by: date + register_no + emp_no + trans_no + trans_id Note that "date" is not necessary datetime. All records in a transaction don't always have the exact same time to the second.

pos_row_id is an incrementing ID generated by a lane machine. This value should be unique for each given register_no. The store_row_id is an incrementing ID for this table. It should be unique for every record but does not have any particular meaning beyond that.

upc is generally a product. The column is always a varchar here, regardless of dbms, because sometimes non-numeric data goes here such as 'DISCOUNT', 'TAX', or 'amountDPdept' (transaction discounts, applicable tax, and open rings, respectively).

description is what's displayed on screen and on receipts.

trans_type indicates the record's type Values include (but may not be limited to at all co-ops): I => normally a product identified by upc, but can also be a discount line (upc='DISCOUNT') or a YOU SAVED line (upc='0'). A => tax total line C => a commentary line. These generally exist only for generating the on-screen display at the register (subtotal lines, etc). D => open ring to a department. In this case, upc will be the amount, 'DP', and the department number T => tender record. UPC is generally, but not always, '0' (e.g., manufacturer coupons have their own UPCs) 0 => another commentary line

trans_subtype refines the record's type. Values include (but may not be limited to at all co-ops): CM => record is a cashier-written comment. Used to make notes on a transaction (tender code) => goes with trans_type 'T', exact values depends what's in core_op.tenders 0 => no refinement available for this trans_type blank => no refinement available for this trans_type

trans_status is a fairly all-purpose indicator. Values include (but may not be limited to at all co-ops): X => the transaction is canceled D => this can be omitted with back-end reporting R => this line is a refund V => this line is a void M => this line is a member special discount C => this line is a coupon Z => this item was damaged, not sold (WFC) 0 => no particular meaning blank => no particular meaning

department is set for a UPC item, an open-department ring, a member special discount, or a manufacturer coupon. All other lines have zero here.

quantity and ItemQtty are the number of items sold on that line. These can be fractional for by-weight items. These values are normally the same, except for: 1. member special lines, where ItemQtty is always zero. This is useful for tracking actual movement by UPC 2. refund lines, where quantity is negative and ItemQtty is not. No idea what the reasoning was here.

scale indicates an item sold by-weight. Meaningless on non-item records.

cost indicates an item's cost. Meaningless on non-item records.

unitPrice is the price a customer will be charged per item. total is quantity times unitPrice. This is what the customer actually pays. If an item is on sale, regPrice indicates the regular price per item. On non-item records, total is usually the only relevant column. Sales are positive, voids/refunds/tenders are negative.

tax indicates whether to tax an item and at what rate

foodstamp indicates whether an item can be paid for using foodstamps

discount is any per-item discount that was applied. In the simplest case, this is the regularPrice minus the unitPrice (times quantity). Discounts are listed as positive values.

memDiscount is the same as discount, but these discounts are only applied if the customer is a member (custdata.Type = 'PC')

discountable indicates whether an item is eligible for transaction-wide percent discounts.

discounttype indicates what type of sale an item is on. 0 => not on sale 1 => on sale for everyone 2 => on sale for members Values over 2 may be used, but aren't used consistently across co-ops at this time.

voided indicates whether a line has been voided 0 => no 1 => yes voided is also used as a status flag in some cases You'd have to dig into IT CORE code a bit to get a handle on that.

percentDiscount is a percentage discount applied to the whole transaction. This is an integer, so 5 = 5% = 0.05

volDiscType is a volume discount type. Usage varies a lot here, but in general: volDiscType => core_op.products.pricemethod volume => core_op.products.quantity VolSpecial => core_op.products.groupprice If an item is on sale, those become specialpricemethod, specialquantity, and specialgroupprice (respectively). Exact calculations depend a lot of volDiscType. 0 means there is no volume discount, and either 1 or 2 (depending on IT CORE version) will probably do a simple 3 for $2 style sale (quantity=3, groupprice=2.00). Higher type values vary.

mixMatch relates to volume pricing. In general, items with the same mixMatch setting are interchangeable. This is so you can do sales across a set of products (e.g., Clif Bars) and the customer can buy various flavors but still get the discount.

matched notes item quantites that have already been used for a volume pricing group. This is so the same item doesn't get counted more than once.

memType and staff match values in core_op.custdata. Including them here helps determine membership status at the time of purchase as opposed to current status.

numflag and charflag are generic status indicators. As far as I know, there's no uniform usage across implementations.

card_no is the customer number from core_op.custdata.

dLogBig

Name Type Info
tdate datetime Indexed
store_id SMALLINT
register_no SMALLINT
emp_no SMALLINT
trans_no INT
upc VARCHAR(13) Indexed
description VARCHAR(30)
trans_type VARCHAR(1) Indexed
trans_subtype VARCHAR(2)
trans_status VARCHAR(1)
department SMALLINT Indexed
quantity DOUBLE
scale TINYINT Default=0
cost MONEY
unitPrice MONEY
total MONEY
regPrice MONEY
tax SMALLINT
foodstamp TINYINT
discount MONEY
memDiscount MONEY
discountable TINYINT
discounttype TINYINT
voided TINYINT
percentDiscount TINYINT
ItemQtty DOUBLE
volDiscType TINYINT
volume TINYINT
VolSpecial MONEY
mixMatch VARCHAR(13)
matched SMALLINT
memType TINYINT
staff TINYINT
numflag INT Default=0
charflag VARCHAR(2) Default=''
card_no INT Indexed
trans_id INT
pos_row_id BIGINT UNSIGNED Indexed
store_row_id BIGINT UNSIGNED PK Indexed Increment
trans_num VARCHAR(25)

Depends on:

  • dtransactions (table)

Use: This view presents simplified access to dtransactions. It omits rows from canceled transactions and testing lane(s)/cashier(s)

productSummaryLastQuarter

Name Type Info
productSummaryLastQuarterID INT Indexed Increment
upc INT PK
qtyThisWeek DECIMAL(10,2)
totalThisWeek MONEY
qtyLastQuarter DECIMAL(10,2)
totalLastQuarter MONEY
percentageStoreSales DECIMAL(10,5)
percentageSuperDeptSales DECIMAL(10,5)
percentageDeptSales DECIMAL(10,5)

Depends on:

  • productWeeklyLastQuarter
  • weeksLastQuarter

Use: Provides per-item sales for the previous quarter. See weeksLastQuarter for more information about how the quarter is defined.

Quantity columns are number of items sold; total columns are in monetary value. Percentages are calculated in terms of monetary value.

Percentages in this table represent a weighted average of sales - i.e., sales last week count more heavily than sales ten weeks ago. The primary purpose of this table and everything that feeds into it is to forecast margin. The percentage captures how an individual item contributes to margin, and weighting over a longer period should capture long-term trends while smoothing over random fluctuations.

productWeeklyLastQuarter

Name Type Info
productWeeklyLastQuarterID INT Indexed Increment
upc VARCHAR(13) PK
weekLastQuarterID INT PK
quantity DECIMAL(10,2)
total MONEY
percentageStoreSales DECIMAL(10,5)
percentageSuperDeptSales DECIMAL(10,5)
percentageDeptSales DECIMAL(10,5)

Use: Per-item sales numbers for a given week. As always, quantity is the number of items sold and total is the monetary value. Percentages are calculated in terms of monetary value.

This is essentially an intermediate calculation for building productSummaryLastQuarter. The results are saved here on the off-chance they prove useful for something else.

reportDataCache

Name Type Info
hash_key VARCHAR(32) PK
report_data TEXT
expires DATETIME

Use: Caches reporting datasets

weeksLastQuarter

Name Type Info
weekLastQuarterID INT PK Increment
weekStart DATETIME
weekEnd DATETIME

Use: Keep track of weeks in the last quarter. This imposes several conventions:

  • Weeks start on Monday and end on Sunday, ISO-style
  • The current week is ID zero. The previous week is ID one. The week before that is ID two, etc.
  • The Last Quarter is week IDs one through thirteen

Week #0 is provided for completeness in information. The other thirteen weeks are used for the last quarter so any comparisions are between full, 7-day weeks.

Clone this wiki locally
You can’t perform that action at this time.