RPOWER.SQL Database Schema

From RPOWERWiki

Jump to: navigation, search

Defined below is the database table schema for RPOWER's SQL database as of 01-08-10

Legend

PK indicates a primary key field. Fields in a primary key appear in the key in the order they appear in the table.

FK indicates a foreign key. Foreign keys are indicated by a FK and a number. A foreign key of FK0 stands alone while a foreign key containing a number greater than 0 is a compound foriegn key. In those cases, fields are ordered in the foreign key by letter.

IN indicates an index. Indexes are indicated by an IN and a number. An index of IN0 stands alone while an index containing a number greater than 0 is a compound index. In those cases, fields are ordered in the index by letter. A U in an index indicates that it is a unique index.

A description containing mid indicates that the field contains a unique id, now called a PUN (Publicly Unique Number) which uniquely identifies that record. PUNs are 18 digits long.

A consolidation group id is an id allowing for a variety groupings of data. A consolidation group could be a regional grouping of stores which a manager likes to have report together, or it can be used to uniquely identify groups of stores owned by franchisees.

Tables

The tbl_table_version table contains information used by the RPOWER Database Importer when it updates the database schema.

tbl_table_version
Field Name Field Type Length Key Type Refers To Not Null Index Default Description
name varchar 30 PK table name
t_version decimal 7,3 table version number


The tbl_timeclass table contains time class information used by the RPOWER Database Importer as it warehouses data.

tbl_timeclass
Field Name Field Type Length Key Type Refers To Not Null Index Default Description
cg int PK consolidation group number
tc int PK INI1UA time class id
fixed_length bigint length of time period in milliseconds, if time class has a fixed duration
time_only tinyint flag: 1 if time only, 0 if date and time
active tinyint flag: 1 if active, 0 if in-active
options int bit flag containing option flags
window int duration window
name varchar 21 time class name


The tbl_time_range table is a date range lookup table. It is not used at this time.

tbl_time_range
Field Name Field Type Length Key Type Refers To Not Null Index Default Description
cg int PK consolidation group number
tc bigint PK,FK0A tbl_timeclass.tc time class id
begin_dttm datetime PK beginning date/time
end_dttm datetime PK ending date/time
description varchar 24 time range description


The tbl_master_id table contains information about records which were updated during the import process.

tbl_master_id
Field Name Field Type Length Key Type Refers To Not Null Index Default Description
from_id bigint PK IN1B pre-import id of imported item
dttm datetime PK IN1A import date/time
from_name varchar 62 pre-import name of imported item
to_mid bigint NN the post-import id of imported item
to_name varchar 62 post-import name of the item
from_local tinyint flag: 1 if local id, 0 if mid
from_table varchar 32 name of the table the item was imported into
cg int consolidation group of the imported item
sequence int item sequence number (NOT USED)
exported tinyint flag: 1 if exported, 0 if not yet exported


The tbl_curr_master_id table contains information about the currently assigned master id. Be aware that this table might be deprecated in the future.

tbl_curr_master_id
Field Name Field Type Length Key Type Refers To Not Null Index Default Description
mid bigint PK last mid to be assigned
sequence int mid sequence number (NOT USED)
db_version decimal 7,3 database schema version (NOT USED)


The tbl_store table contains store information. The lup_dttm and lup_zdttm fields can be used to track the last time the store sent data to the SQL database.

tbl_store or view_store
Field Name Field Type Length Key Type Refers To Not Null Index Default Description
mid bigint PK store mid
cg int IN1A,IN2A store consolidation group
serial_number int NN IN1B 4-digit store serial number
name varchar 63 IN2B store name
store_id varchar 11 store id number
tag_name varchar 63 alternate store name
ip_address varchar 63 file server IP address
ot_dow int
pay_period char 1
pp_desc varchar 15 pay period description
pp_start_date datetime current pay period start date
pp_end_date datetime current pay period end date
pp_prev_start datetime pervious pay period start date
pp_prev_end datetime previous pay period end date
lup_dttm datetime last update time (local)
lup_zdttm datetime last update time (zulu)


The tbl_price_level table contains menu item price information.

tbl_price_level or view_price_level
Field Name Field Type Length Key Type Refers To Not Null Index Default Description
mid bigint PK price level mid
cg int IN1A price level consolidation group
name varchar 20 IN1B price level name


tbl_sales_department or view_sales_department
Field Name Field Type Length Key Type Refers To Not Null Index Default Description
mid bigint PK sales department mid
cg int IN1A sales department consolidation group
name varchar 20 IN1B sales department name


tbl_sales_type or view_sales_type
Field Name Field Type Length Key Type Refers To Not Null Index Default Description
mid bigint PK sales type mid
cg int IN1A sales type consolidation group
name varchar 20 IN1B sales type name
abbr5 varchar 5
abbr1 char 1
type_sale tinyint flag: 1 if sales type
type_nonsale tinyint flag: 1 if non-sales type
type_return tinyint flag: 1 if return type
type_refund tinyint flag: 1 if refund type
type_comp tinyint flag: 1 if comp type
type_void tinyint flag: 1 if void type
type_error tinyint flag: 1 if error type
type_promo tinyint flag: 1 if promo type
type_discnt tinyint flag: 1 if discount type
type_hash tinyint flag: 1 if hash type
type_cash tinyint flag: 1 if cash type
impact_sales tinyint flag: 1 if impacts sales
impact_costs tinyint flag: 1 if impacts costs


The tbl_sales_category table contains information about sales categories. Sales Categories are just groupings of menu items. They are also used to track discounts, since discounted items are placed in their own sales category.

tbl_sales_category or view_sales_category
Field Name Field Type Length Key Type Refers To Not Null Index Default Description
mid bigint PK sales category mid
cg int IN1A sales category consolidation group
name varchar 20 IN1B,IN2B sales category name
slsdep_mid bigint FK0A tbl_sales_department.mid IN2A 3 sales department mid
alt_qty_unit varchar 10 alternate quantity unit
alt_rpt_unit varchar 8,4 alternate report unit name
aqu_in_aru tinyint alternate quantity units in one alternate report unit
is_bar tinyint flag: 1 if sales category contains bar items
is_hash tinyint flag: 1 if sales category contains hash items


tbl_void_reason or view_void_reason
Field Name Field Type Length Key Type Refers To Not Null Index Default Description
mid bigint PK void reason mid
cg int IN1A void reason consolidation group
name varchar 20 IN1B,IN2B void reason name
slstype_mid bigint FK0A tbl_sales_type.mid IN2A 3 sales type mid
is_system tinyint flag: 1 if is system


tbl_payment_department or view_payment_department
Field Name Field Type Length Key Type Refers To Not Null Index Default Description
mid bigint PK payment department name
cg int IN1A consolidation group
name varchar 20 IN1B payment department name


tbl_payment_method or view_payment_method
Field Name Field Type Length Key Type Refers To Not Null Index Default Description
mid bigint PK payment method mid
cg int IN1A consolidation group id
name varchar 20 IN1B,IN2B payment method name
pmtdep_mid bigint FK0A tbl_payment_department.mid IN2A 3 payment department mid
is_cash tinyint flag: 1 if cash payment method
is_cc tinyint flag: 1 if credit card payment method
is_deposit tinyint flag: 1 if deposit payment method
tender_key int
report_id varchar 7


tbl_tax_category or view_tax_category
Field Name Field Type Length Key Type Refers To Not Null Index Default Description
mid bigint PK tax category mid
cg int IN1A consolidation group id
name varchar 20 IN1B tax category name
number int tax category number
pct decimal 7,4 tax percent


tbl_store_tax or view_store_tax
Field Name Field Type Length Key Type Refers To Not Null Index Default Description
cg int consolidation group id
store_mid bigint PK, FK0A tbl_store.mid 3 store mid
taxcat1_mid bigint FK0A tbl_tax_category.mid 3 tax category mid
taxcat2_mid bigint FK0A tbl_tax_category.mid 3 tax category mid
taxcat3_mid bigint FK0A tbl_tax_category.mid 3 tax category mid
taxcat4_mid bigint FK0A tbl_tax_category.mid 3 tax category mid


tbl_inventory_department or view_inventory_department
Field Name Field Type Length Key Type Refers To Not Null Index Default Description
mid bigint PK inventory department mid
cg int IN1A consolidation group
name varchar IN1B inventory department name


tbl_inventory_category or view_inventory_category
Field Name Field Type Length Key Type Refers To Not Null Index Default Description
mid bigint PK inventory category mid
cg int IN1A consolidation group
name varchar 20 IN1B,IN2B inventory category name
invdep_mid bigint FK0A tbl_inventory_department.mid IN2A 3 inventory department mid


tbl_inv_adj_category or view_inv_adj_category
Field Name Field Type Length Key Type Refers To Not Null Index Default Description
mid bigint PK inventory adjustment category mid
cg int IN1A consolidation group
name varchar 20 IN1B inventory adjustment category name


tbl_payroll_department or view_payroll_department
Field Name Field Type Length Key Type Refers To Not Null Index Default Description
mid bigint PK payroll department mid
cg int IN1A consolidation group id
name varchar 20 IN1B payroll department name


tbl_payroll_category or view_payroll_category
Field Name Field Type Length Key Type Refers To Not Null Index Default Description
mid bigint PK payroll category mid
cg int IN1A consolidation group id
name varchar 20 IN1B,IN2B payroll category name
pyrldep_mid bigint FK0A tbl_payroll_department IN2A payroll department mid


tbl_payout_category or view_payout_category
Field Name Field Type Length Key Type Refers To Not Null Index Default Description
mid bigint PK,PM payout category mid
cg int IN1A consolidation group id
name varchar 20 IN1B payout category name
slstype_mid bigint FK0A tbl_sales_type.mid IN2A sales type mid
is_payout tinyint flag: 1 if true
is_system tinyint flag: 1 if true
is_cash tinyint flag: 1 if true


The tbl_profit_center table contains information about profit centers. Examples of profit centers would be: Delivery, Counter, Take-Out, etc.

tbl_profit_center or view_profit_center
Field Name Field Type Length Key Type Refers To Not Null Index Default Description
mid bigint PK profit center mid
cg int IN1A consolidation group id
name varchar 11 IN1B profit center name


The tbl_meal_time table contains information about meal times. Examples would be Happy Hour, Breakfast, Dinner, etc.

tbl_meal_time or view_meal_time
Field Name Field Type Length Key Type Refers To Not Null Index Default Description
mid bigint PK meal time mid
cg int IN1A consolidation group id
name varchar 11 IN1B meal time name


tbl_day_time_period or view_day_time_period
Field Name Field Type Length Key Type Refers To Not Null Index Default Description
mid bigint PK day/time period mid
cg int IN1A consolidation goup id
name varchar 20 IN1B day/time period name
prclvl_mid bigint FK0A tbl_price_level.mid 3 price level mid
mealtime_mid bigint FK0A tbl_meal_time.mid 3 meal time mid


tbl_discount_itemizer or view_discount_itemizer
Field Name Field Type Length Key Type Refers To Not Null Index Default Description
mid bigint PK discount itemizer mid
cg int IN1A consolidation group id
name varchar 20 IN1B discount itemizer name


tbl_job or view_job
Field Name Field Type Length Key Type Refers To Not Null Index Default Description
mid bigint PK job mid
cg int IN1A consolidation group id
name varchar 20 IN1B,IN2B,IN3B job name
ext_id varchar 7 external job id
pyrlcat_mid bigint FK0A tbl_payroll_category.mid IN2A 3 payroll category mid
lcenter_mid bigint FK0A tbl_profit_center IN3A 3 labor center mid


The tbl_employee table contains employee information. The store_mid_any field contains the store mid for newly added employees. For employees added before RPOWER 2009, it contains a 2 (default value).

tbl_employee or view_employee
Field Name Field Type Length Key Type Refers To Not Null Index Default Description
mid bigint PK employee mid
cg int IN1A,IN2A,IN3A consolidation group id
name varchar 30 IN1B employee full name
phone varchar 15 IN1C employee phone number
payroll_id varchar 11 IN1D,IN3B payroll id
store_mid_any bigint FK0A tbl_store.mid IN1E 2 store mid
lname varchar 30 IN2B last name
fname varchar 30 IN2C first name
mname varchar 30 IN2D middle name
aname varchar 30 IN2E alternate name/nickname


The tbl_employee_store table tracks employees who work at multiple stores.

tbl_employee_store or view_employee_store
Field Name Field Type Length Key Type Refers To Not Null Index Default Description
cg int consolidation group id
store_mid bigint PK,FK0A tbl_store.mid 5 store mid
emp_mid bigint PK,FK0A tbl_employee.mid IN1A 3 employee mid
id_num int employee id number
card_num int employee card number
passcode int employee passcode
print_name varchar 20 print format of employee name
is_active tinyint flag: 1 if employee is active


The tbl_employee_job table tracks the jobs employees perform.


tbl_employee_job or view_employee_job
Field Name Field Type Length Key Type Refers To Not Null Index Default Description
cg int consolidation group id
store_mid bigint PK,FK0A tbl_store.mid 3 store mid
emp_mid bigint PK,FK0A tbl_employee.mid IN1A 3 employee mid
job_mid bigint PK,FK0A tbl_job.mid IN1A 3 job mid


tbl_room or view_room
Field Name Field Type Length Key Type Refers To Not Null Index Default Description
mid bigint PK room mid
cg int consolidation id
store_mid bigint FK0A tbl_store.mid IN1A 3 store mid
name varchar 20 IN1B room name
is_bar tinyint flag: 1 if room is a bar


tbl_table or view_table
Field Name Field Type Length Key Type Refers To Not Null Index Default Description
mid bigint PK NN table mid
cg int consolidation group id
room_mid bigint FK0A tbl_room.mid IN1A 3 room mid
name varchar 11 table name


tbl_workstation or view_workstation
Field Name Field Type Length Key Type Refers To Not Null Index Default Description
mid bigint PK workstation mid
cg int consolidation group id
store_mid bigint FK0A tbl_store.mid IN1A store mid
name varchar 15 IN1B workstation name


tbl_menuitem_type or view_menuitem_type
Field Name Field Type Length Key Type Refers To Not Null Index Default Description
mid bigint PK menu item type mid
cg int IN1A consolidation group id
name varchar 20 IN1B menu item type name


tbl_menu_item or view_menu_item
Field Name Field Type Length Key Type Refers To Not Null Index Default Description
mid bigint PK menu item mid
cg int IN1A consolidation group id
name varchar 33 IN1B,IN2B,IN3B,IN4B menu item name
mittype_mid bigint FK0A tbl_meunitem_type.mid IN3A 3 menu item type mid
slscat_mid bigint FK0A tbl_sales_category.mid IN2A 3 sales category mid
slstype_mid bigint FK0A tbl_sales_type.mid IN4A 3 sales type mid
alt_name1 varchar 31 alternate name
alt_name2 varchar 31 alternate name
alt_name3 varchar 31 alternate name
aqu_count decimal 10,4 alternate name
last_cost decimal 8,4
upc varchar 14 UPC code
plu varchar 6 PLU code


tbl_menuitem_price or view_menuitem_price
Field Name Field Type Length Key Type Refers To Not Null Index Default Description
cg int consolidation group id
store_mid bigint PK, FK0A tbl_store.mid IN1B,IN2B 5 store mid
menuitem_mid bigint PK,FK0A tbl_menu_itm.mid IN1A,IN2C 3 menu item mid
prclvl_mid bigint FK0A tbl_price_level.mid IN1C,IN2A 3 price level mid
price decimal 8,2 menu item price


tbl_customer_group or view_customer_group
Field Name Field Type Length Key Type Refers To Not Null Index Default Description
mid bigint PK customer group mid
cg int IN1A consolidation group id
name varchar 40 customer group name
phone varchar 15 phone number
ext varchar 6 extension
fax varchar 12 fax number
acct_num varchar 10 account number
contact varchar 40 contact name
address1 varchar 40 address line 1
address2 varchar 40 address line 2
cross_st varchar 40 cross streets
city varchar 30
state varchar 10
zip varchar 10
zone varchar 5
email varchar 40 email address
last_order datetime last order date
user_date1 datetime user date of importance
user_date2 datetime user date of importance
user_string varchar 10
credit_limg decimal 8,2
prclvl_mid bigint FK0A tbl_price_level.mid price level mid
paymeth_mid bigint FK0A tbl_payment_level.mid payment method mid
menuitem_mid bigint FK0A tbl_menu_item.mid favorite menu item mid
is_cash_only tinyint flag: 1 if only accept cash
is_credit_hold tinyint flag: 1 if hold on credit


tbl_custgrp_bal or view_custgrp_bal
Field Name Field Type Length Key Type Refers To Not Null Index Default Description
cg int consolidation group mid
custgrp_mid bigint PK,FK0A tbl_cutomer_group.mid IN1B 3 customer group mid
store_mid bigint PK,FK0A tbl_store.mid IN1A 3 store mid
charge_due decimal 8,2 charge amount due
lp_points int loyalty program points
ttd_lp_accum int total loyalty program point accumulated
ttd_lp_redm int total loyalty program points redeemed


tbl_customer or view_customer
Field Name Field Type Length Key Type Refers To Not Null Index Default Description
mid bigint PK customer mid
cg int IN1A consolidation group
name varchar 40 IN1B,IN2B full name
phone varchar 15 IN1C,IN2C phone number
store_mid_any bigint FK0A tbl_store.mid IN1D 2 source store mid or 2 if source store is undetermined
custgrp_mid bigint FK0A tbl_customer_group.mid IN2A 3 customer group mid
ext varchar 6 extension
fax varchar 12 fax number
card_acct varchar 10 card account number
contact varchar 40 contact name
address1 varchar 40 address line 1
address2 varchar 40 address line 2
cross_st varchar 40 cross streets
city varchar 30
state varchar 10
zip varchar 10
zone varchar 5
email varchar 63 email address
last_order datetime last order date
user_date1 datetime user date of importance
user_date2 datetime user date of importance
user_string varchar 10
credit_lim decimal 8,2 customer credit limit
prclvl_mid bigint FK0A tbl_price_level.mid 3 menu item price level
menuitem_mid bigint FK0A tbl_menu_item.mid 3 favorite menu item mid
is_cash_only tinyint flag: 1 if cash only customer
is_credit_hold tinyint flag: 1 if hold on credit


tbl_customer_bal or view_customer_bal
Field Name Field Type Length Key Type Refers To Not Null Index Default Description
cg int consolidation group id
cust_mid bigint PK,FK0A tbl_customer.mid IN1B 3 customer mid
store_mid bigint PK,FK0A tbl_store.mid IN1A 3 store_mid
charge_due decimal 8,2 charge amount due
lp_points int loyalty points
ttd_lp_accum int total loyalty points accumulated
ttd_lp_redm int total loyalty points redeemed


tbl_time_clock or view_time_clock
Field Name Field Type Length Key Type Refers To Not Null Index Default Description
cg int PK IN1A consolidation group
store_mid bigint PK,FK0A tbl_store.mid IN2A 3 store mid
record_id int PK unique record id
in_dttm datetime DT IN1B,IN2B,IN3B,IN4B,IN5B punch in date/time
emp_mid bigint FK0A ebl_employee.mid IN3C,IN1C,IN2C,IN4C,IN5C 3 employee mid
job_mid bigint FK0A tbl_job.mid IN4A 3 job mid
lcenter_mid bigint FK0A tbl_profit_center.mid IN5A 3 labor center mid
out_dttm datetime punch out date/time
payroll_id varchar 11 employee payroll id
break_minutes int minutes used for break if employees punch out for lunch
reg_hours decimal 12,4 hours worked at regular pay
ot_hours decimal 12,4 hours worked at overtime pay
dt_hours decimal 12,4 hours worked at double-time pay
reg_rate decimal 8,2 regular pay rate
ot_rate decimal 8,2 overtime pay rate
dt_rate decimal 8,2 double time pay rate
reg_pay decimal 12,2 total regular pay
ot_pay decimal 12,2 total overtime pay
dt_pay decimal 12,2 total double-time pay
tip_charge_sls decimal 12,2 charged sales for calculating tips
tip_cash_sls decimal 12,2 cash sales for calculating tips
tip_gross_sls decimal 12,2 gross tip amount
tip_charge decimal 12,2 total charged tips
tip_fees decimal 12,2 tip fees
tip_retained decimal 12,2 retained tips
tip_cash decimal 12,2 cash tips
tip_pool decimal 12,2 total tip pool contribution
tip_net decimal 12,2 net tips
pay_period char 1 pay period
pp_start_date datetime pay period start date
pp_end_date datetime pay period end date
ot_start_date datetime overtime start date


tbl_time_clock_tc or view_time_clock_tc
Field Name Field Type Length Key Type Refers To Not Null Index Default Description
tc int PK tbl.timeclass.tc IN1A,IN2A,IN3A,IN4A time class id
cg int PK consolidation group id
in_dttm datetime PK IN1C,IN2C,IN3C,IN4C punch in date/time
store_mid bigint PK,FK0A tbl.store.mid IN1B 3 store mid
emp_mid bigint PK,FK0A tbl_employee.mid IN2B 3 employee mid
job_mid bigint PK,FK0A tbl_job.mid IN3B 3 job_mid
lcenter_mid bigint PK,FK0A tbl_profit_center.mid IN4B 3 labor center mid
break_minutes int
reg_hours decimal 12,4 regular hours worked
ot_hours decimal 12,4 overtime hours worked
dt_hours decimal 12,4 double time hours worked
reg_pay decimal 12,2 regular pay earned
ot_pay decimal 12,2 overtime pay earned
dt_pay decimal 12,2 double time pay earned
tip_charge_sls decimal 12,2 charge sales
tip_cash_sls decimal 12,2 cash sales
tip_gross_sls decimal 12,2 gross sales
tip_charge decimal 12,2 tips earned on charge sales
tip_fees decimal 12,2
tip_retained decimal 12,2
tip_cash decimal 12,2 tips earned from cash sales
tip_pool decimal 12,2 tip pool contributions
tip_net decimal 12,2 net tips


tbl_menuitem_sales or view_menuitem_sales
Field Name Field Type Length Key Type Refers To Not Null Index Default Description
cg int PK consolidation group id
date datetime PK IN1B,IN2B,IN3B,IN4B,IN5B,IN6B sale date
store_mid bigint PK,FK0A tbl_store.mid IN1A 3 store mid
shift char 1 PK shift: A or B
pcenter_mid bigint PK,FK0A tbl_profit_center.mid IN3A 3 profit center mid
mealtime_mid bigint PK,FK0A tbl_meal_time.mid IN4A 3 meal time mid
emp_mid bigint PK,FK0A tbl_employee.mid IN5A 3 employee mid
cust_mid bigint PK,FK0A tbl_customer.mid IN6A 3 customer mid
menuitem_mid bigint PK,FK0A tbl_menu_item.mid IN2A 3 menu item mid
price decimal 8,2 item price
qty decimal 12,2 quantity sold
sales decimal 12,2 total sales amount
cost decimal 12,2
void_qty decimal 12,2 number of items voided
void_cost decimal 12,2 cost of items voided
void_sales decimal 12,2 sales amount voided
comp_qty decimal 12,2 number of items comp'ed
comp_cost decimal 12,2 cost of items comp'ed
comp_sales decimal 12,2 sales amount comp'ed


tbl_menuitem_sales_tc or view_menuitem_sales_tc
Field Name Field Type Length Key Type Refers To Not Null Index Default Description
tc int PK time class id
cg int PK consolidation group id
date datetime PK IN1A,IN2A,IN3A,IN4A,IN5A,IN6A sale date
store_mid bigint PK,FK0A tbl_store.mid IN1B 3 store mid
pcenter_mid bigint PK,FK0A tbl_profit_center.mid IN3B 3 profit center mid
mealtime_mid bigint PK,FK0A tbl_meal_time.mid IN4B 3 meal time mid
emp_mid bigint PK,FK0A tbl_employee.mid IN5B 3 employee mid
cust_mid bigint PK,FK0A tbl_customer.mid IN6B 3 customer mid
menuitem_mid bigint PK,FK0A tbl_menu_item.mid IN2B 3 menu item mid
price decimal 8,2 item price
qty decimal 12,2 quantity sold
sales decimal 12,2 sales amount
cost decimal 12,2 item cost
void_qty decimal 12,2 quantity voided
void_cost decimal 12,2 cost of items voided
void_sales decimal 12,2 sales amount voided
comp_qty decimal 12,2 quantity comp'ed
comp_cost decimal 12,2 cost of items comp'ed
comp_sales decimal 12,2 sales amount comp'ed


tbl_ticket or view_ticket
Field Name Field Type Length Key Type Refers To Not Null Index Default Description
cg int PK consolidation group id
date datetime PK IN1B,IN2B,IN3B,IN4B,IN5B,IN6B,IN7B,IN8B,IN9B,IN10B,IN11B date/time of ticket
shift char 1 PK shift: A or B
store_mid bigint PK,FK0A tbl_store.mid IN1A store mid
ticket int PK IN1C ticket number
pcenter_mid bigint FK0A tbl_profit_center.mid IN2A 3 profit center
mealtime_mid bigint FK0A tbl_meal_time.mid IN3A 3 meal time
mgr_mid bigint FK0A tbl_employee.mid 1 employee mid
cashier_mid bigint FK0A tbl_employee.mid IN11A 3 cashier mid
cust_mid bigint FK0A tbl_customer.mid IN10A 3 customer mid
refnum11 bigint
open_bdate datetime
open_bshift char 1
open_dttn datetime date/time ticket was opened
close_dttm datetime date time ticket was closed
need_dttm datetime date/time ticket is needed
sale_dttm datetime date/time the sale was made
run_dttm datetime date/time the ticket was assigned to a runner
scan_dttm datetime date/time the ticket barcode was scanned
kvs_dttm datetime date/time the ticket appeared in the kvs system
bump_dttm datetime date/time the ticket was bumped
main_server bigint FK0A tbl_emloyee.mid IN4A 3 mid of main server on ticket
first_server bigint FK0A tbl_emloyee.mid 3 mid of the first server on ticket
last_server bigint FK0A tbl_emloyee.mid 3 mid of last server on ticket
timeclk_id int
table_mid bigint FK0A tbl_table.mid IN7A 3 table mid
open_wkstn bigint FK0A tbl_workstation.mid IN8A 3 mid of workstation that opened ticket
close_wkstn bigint FK0A tbl_workstation.mid IN9A 3 mid of workstation that closed ticket
is_cancelled tinyint flag: 1 if ticket was cancelled
voidrsn_mid bigint FK0A tbl_void_reason.mid IN5A 3 reason ticket was voided
voidmgr_mid bigint FK0A tbl_employee.mid IN6A 3 mid of manager who voided ticket
guest_count int
entree_count int
bev_count int
items decimal 12,2 sales amount of menu items
discount decimal 12,2 amount of discount
hash decimal 12,2 amount placed on hash
hash_discount decimal 12,2 discount applied to hash amount
tip decimal 12,2 tip amount
retained_tip decimal 12,2
grat decimal 12,2 gratuity amount
hidden_grat decimal 12,2
house_grat decimal 12,2
service_grat decimal 12,2
taxable1 decimal 12,2
taxable2 decimal 12,2
taxable3 decimal 21,2
taxable4 decimal 12,2
taxable decimal 12,2 sum of taxable1, taxable2, taxable3, taxable4
non_tax decimal 12,2
tax_exempt decimal 12,2
tax1 decimal 12,2
tax2 decimal 12,2
tax3 decimal 12,2
tax4 decimal 12,2
tax decimal 12,2 sales tax, sum of tax1, tax2, tax3, tax4
acct_taxable1 decimal 12,2
acct_taxable2 decimal 12,2
acct_taxable3 decimal 12,2
acct_taxable4 decimal 12,2
acct_tax1 decimal 12,2
acct_tax2 decimal 12,2
acct_tax3 decimal 12,2
acct_tax4 decimal 21,2
tip_allocable decimal 12,2
pointable decimal 12,2


tbl_ticket_tc or view_ticket_tc
Field Name Field Type Length Key Type Refers To Not Null Index Default Description
tc int PK tbl_timeclass.tc IN1A,IN2A,IN3A,IN4A,IN5A,IN6A,IN7A,IN8A time class id
cg int PK consolidation group id
date datetime PK IN1C,IN2C,IN3C,IN4C,IN5C,IN6C,IN7C,IN8C ticket date
store_mid bigint PK,FK0A tbl_store.mid IN1B store mid
pcenter_mid bigint FK0A tbl_profit_center.mid IN2B 3 profit center mid
mealtime_mid bigint FK0A tbl_meal_time.mid IN3B 3 meal time mid
cashier_mid bigint FK0A tbl_employee.mid IN8B 3 cashier mid
cust_mid bigint FK0A tbl_customer.mid IN7B 3 customer mid
main_server bigint FK0A tbl_emloyee.mid IN4B 3 main server mid
is_cancelled tinyint PK flag: 1 if the order was cancelled
voidrsn_mid bigint FK0A tbl_void_reason.mid IN5B 3 void reason mid
voidmgr_mid bigint FK0A tbl_employee.mid IN6B 3 void manager mid
guest_count int number of covers on ticket
entree_count int number of entrees on ticket
bev_count int number of beverages on ticket
items decimal 12,2 sales amount of menu items
discount decimal 12,2
hash decimal 12,2 amount placed on hash
hash_discount decimal 12,2 discount applied to hash amount
tip decimal 12,2 tip amount
retained_tip decimal 12,2
grat decimal 12,2 gratuity amount
hidden_grat decimal 12,2
house_grat decimal 12,2
retained_grat decimal 12,2
service_grat decimal 12,2
taxable1 decimal 12,2
taxable2 decimal 12,2
taxable3 decimal 21,2
taxable4 decimal 12,2
taxable decimal 12,2 sum of taxable1, taxable2, taxable3, taxable4
non_tax decimal 12,2
tax_exempt decimal 12,2
tax1 decimal 12,2
tax2 decimal 12,2
tax3 decimal 12,2
tax4 decimal 12,2
tax decimal 12,2 sales tax, sum of tax1, tax2, tax3, tax4
acct_taxable1 decimal 12,2
acct_taxable2 decimal 12,2
acct_taxable3 decimal 12,2
acct_taxable4 decimal 12,2
acct_tax1 decimal 12,2
acct_tax2 decimal 12,2
acct_tax3 decimal 12,2
acct_tax4 decimal 21,2
tip_allocable decimal 12,2
pointable decimal 12,2


tbl_ticket_employee or view_ticket_employee
Field Name Field Type Length Key Type Refers To Not Null Index Default Description
cg int PK,FK1A tbl_ticket.cg ticket consolidation group
date datetime PK,FK1B tbl_ticket.date IN1B ticket date
shift char 1 PK,FK1C tbl_ticket.shift ticket shift
store_mid bigint PK,FK1D tbl_ticket.store_mid ticket store mid
ticket int PK,FK1E tbl_ticket.ticket ticket number
emp_mid bigint PK,FK0A tbl_employee.mid IN1A 3 employee mid
timeclk_id int time clock record id


tbl_ticket_item or view_ticket_item
Field Name Field Type Length Key Type Refers To Not Null Index Default Description
cg int PK,FK1A tbl_ticket.cg ticket consolidation group id
date datetime PK,FK1B tbl_ticket.date IN1B,IN2B,IN3B,IN4B,IN5B,IN6B ticket date
shift char 1 PK,FK1C tbl_ticket.shift ticket shift
store_mid bigint PK,FK1D tbl_ticket.store_mid IN1A ticket store mid
ticket int PK,FK1E tbl_ticket.ticket ticket number
atom int PK
pcenter_mid bigint FK0A tbl_profit_center.mid IN2A 3 profit center mid
mealtime_mid bigint FK0A tbl_meal_time.mid IN4A 3 meal time mid
menu_item.mid bigint FK0A tbl_menu_item.mid IN3A 3 menu item mid
cust_mid bigint FK0A tbl_customer.mid IN5A 3 customer mid
mgr_mid bigint FK0A tbl_employee.mid IN6A 3 employee mid
item_dttm datetime sale time of item
qty decimal 12,2 quantity sold
disp_total decimal 12,2 total displayed
real_total decimal 12,2 real total
parent_atom int
mod_level int
cnumber varchar 19
note varchar 1024 item note


tbl_ticket_sales or view_ticket_sales
Field Name Field Type Length Key Type Refers To Not Null Index Default Description
cg int PK,FK1A tbl_ticket.cg ticket consolidation group
date datetime PK,FK1B tbl_ticket.date IN1B,IN2B,IN3B,IN4B,IN5B,IN6B,IN7B,IN8B,IN9B ticket date
shift char 1 PK,FK1C tbl_ticket.shift ticket shift
store_mid bigint PK,FK1D tbl_ticket.store_mid IN1A ticket store mid
ticket int PK,FK1E tbl_ticket.ticket ticket number
atom int PK
pcenter_mid bigint FK0A tbl_profit_center.mid FM,IN2A 3 profit center mid
mealtime_mid bigint FK0A tbl_meal_time.mid FM,IN3A 3 meal time mid
mgr_mid bigint FK0A tbl_employee.mid FM,IN4A 3 manager mid
cust_mid bigint FK0A tbl_customer.mid FM,IN5A 3 customer mid
menuitem_mid bigint FK0A tbl_menu_item.mid FM,IN6A 3 menu item mid
voidrsn_mid bigint FK0A tbl_void_reason.mid FM,IN7A 3 void reason mid
voidmgr_mid bigint FK0A tbl_employee.mid FM,IN8A 3 employee mid
slstype_mid bigint FK0A tbl_sales_type.mid FM,IN9A 3 sales type mid
slstype_mid bigint FK0A tbl_sales_type.mid FM,IN9A 3 sales type mid
item_dttm datetime date/time item was sold
qty decimal 12,2 quantity sold
sales decimal 12,2
cost decimal 12,2


tbl_ticket_sales_tc or view_ticket_sales_tc
Field Name Field Type Length Key Type Refers To Not Null Index Default Description
tc int PK tbl_timeclass.tc IN1A,IN2A,IN3A,IN4A,IN5A,IN6A,IN7A,IN8A,IN9A time class id
cg int PK consolidation group id
item_dttm datetime PK IN1C,IN2C,IN3C,IN4C,IN5C,IN6C,IN7C,IN8C,IN9C date/time item was sold
store_mid bigint PK IN1C,IN2C,IN3C,IN4C,IN5C,IN6C,IN7C,IN8C,IN9C store mid
pcenter_mid bigint FK0A tbl_profit_center.mid FM,IN2B 3 profit center mid
mealtime_mid bigint FK0A tbl_meal_time.mid.mid FM,IN3B 3 meal time mid
mgr_mid bigint FK0A tbl_employee.mid FM,IN4B 3 manager mid
cust_mid bigint FK0A tbl_customer.mid FM,IN5B 3 customer mid
menuitem_mid bigint FK0A tbl_menu_item.mid FM,IN6B 3 menu item mid
voidrsn_mid bigint FK0A tbl_void_reason.mid FM,IN7B 3 void reason
voidmgr_mid bigint FK0A tbl_employee.mid FM,IN8B 3 employee mid
slstype_mid bigint FK0A tbl_sales_type.mid FM,IN9B 3 sales type mid
qty decimal 12,2 quantity sold
sales decimal 12,2


tbl_ticket_payment or view_ticket_payment
Field Name Field Type Length Key Type Refers To Not Null Index Default Description
cg int PK tbl_ticket.cg FK1A consolidation group id
date datetime PK,FK1B tbl_ticket.date IN1B,IN2B,IN3B,IN4B,IN5B,IN6B date ticket was started
shift char 1 PK,FK1C tbl_ticket.shift shift
store_mid bigint PK,FK1D tbl_ticket.store_mid IN1A store mid
ticket int PK,FK1E tbl_ticket.ticket ticket number
number int PK
pcenter_mid bigint FK0A tbl_profit_center.mid IN5A 3 profit center mid
mealtime_mid bigint FK0A tbl_meal_time.mid IN6A 3 meal time mid
paymeth_mid bigint FK0A tbl_payment_method.mid IN2A 1 payment method mid
cashier_mid bigint FK0A tbl_employee.mid IN3A 3 employee mid
cust_mid bigint FK0A tbl_customer.mid IN4A 3 customer mid
refstr10 char 10
target_amt decimal 12,2
paid_ticket decimal 12,2 amount paid on ticket, sum of taxable, tax, non_tax, tax_exempt, grat, hash
paid_tip decimal 12,2 tip paid on ticket
cash_change decimal 12,2 change returned to customer
reference varchar 30
cnumber varchar 19
hash decimal 8,2 hash amount on ticket, summed into paid_ticket
grat decimal 12,2 gratuity on ticket, sum of hidden_grat, house_grat, retained_grat, service_grat
hidden_grat decimal 12,2 hidden gratuity on ticket, summed into grat
house_grat decimal 12,2 house grat on ticket, summed into grat
retained_grat decimal 12,2 retained gratuity on ticket, summed into grat
service_grat decimal 12,2 service gratutity on ticket, summed into grat
taxable1 decimal 12,2
taxable2 decimal 12,2
taxable3 decimal 12,2
taxable4 decimal 12,2
taxable decimal 12,2 sum of taxable1, taxable2, taxable3, taxable4, summed into paid_ticket
non_tax decimal 12,2 non taxable amount on ticket, summed into paid_ticket
tax_exempt decimal 12,2 tax exempt amount on ticket, summed into paid_ticket
tax1 decimal 12,2
tax2 decimal 12,2
tax3 decimal 12,2
tax4 decimal 12,2
tax decimal 12,2 sum of tax1, tax2, tax3, tax4, summed into paid_ticket
tip_allocable decimal 12,2


tbl_ticket_payment_tc or view_ticket_payment_tc
Field Name Field Type Length Key Type Refers To Not Null Index Default Description
tc int PK tbl_timeclass.tc IN1A,IN2A,IN3A,IN4A,IN5A,IN6A time class id
cg int PK consolidation group id
date datetime PK tbl_ticket.date IN1C,IN2C,IN3C,IN4C,IN5C,IN6C ticket date
shift char 1 PK ticket shift
store_mid bigint PK,FK0A tbl_store.mid IN1B store mid
ticket int PK ticket number
number int PK number
pcenter_mid bigint FK0A tbl_profit_center.mid IN5B 3 profit center mid
mealtime_mid bigint FK0A tbl_meal_time.mid IN6B 3 meal time mid
paymeth_mid bigint FK0A tbl_payment_method.mid IN2B 3 payment method mid
cashier_mid bigint FK0A tbl_employee.mid IN3B 3 cashier mid
cust_mid bigint FK0A tbl_customer.mid IN4B 3 customer mid
paid_ticket decimal 12,2 amount paid on ticket, sum of taxable, tax, non_tax, tax_exempt, grat, hash
paid_tip decimal 12,2 tip paid on ticket
cash_change decimal 12,2 change returned to customer
hash decimal 8,2 hash amount on ticket, summed into paid_ticket
grat decimal 12,2 gratuity on ticket, sum of hidden_grat, house_grat, retained_grat, service_grat
hidden_grat decimal 12,2 hidden gratuity on ticket, summed into grat
house_grat decimal 12,2 house grat on ticket, summed into grat
service_grat decimal 12,2 service grat on ticket, summed into grat
taxable1 decimal 12,2
taxable2 decimal 12,2
taxable3 decimal 12,2
taxable4 decimal 12,2
taxable decimal 12,2 sum of taxable1, taxable2, taxable3, taxable4, summed into paid_ticket
non_tax decimal 12,2 non taxable amount on ticket, summed into paid_ticket
tax_exempt decimal 12,2 tax exempt amount on ticket, summed into paid_ticket
tax1 decimal 12,2
tax2 decimal 12,2
tax3 decimal 12,2
tax4 decimal 12,2
tax decimal 12,2 sum of tax1, tax2, tax3, tax4, summed into paid_ticket
tip_allocable decimal 12,2


tbl_payout or view_payout
Field Name Field Type Length Key Type Refers To Not Null Index Default Description
cg int PK consolidation group id
date datetime PK NN IN1B,IN2B,IN3B,IN4B,IN5B,IN6B,IN7B,IN8B payout date
shift char 1 PK NN payout shift
store_mid bigint PK,FK0A NN IN1A store mid
ticket int PK NN IN1C ticket number
pcenter_mid bigint FK0A tbl_profit_center.mid IN2A 3 profit center mid
mealtime_mid bigint FK0A tbl_meal_time.mid IN3A 3 meal time mid
pocat_mid bigint FK0A tbl_payout_category.mid IN4A 3 payout category mid
wkstn_mid bigint FK0A tbl_workstation.mid IN5A 3 workstation mid
paymeth_mid bigint FK0A tbl_payment_method.mid IN6A 3 payment method mid
emp_mid bigint FK0A tbl_employee.mid IN7A 3 employee mid
mgr_mid bigint FK0A tbl_employee.mid IN8A 3 manager mid
dttm datetime payout date/time
base decimal 12,2
adjust decimal 8,2
net decimal 12,2
is_payin tinyint
discount decimal 12,2
reference decimal 12,2


tbl_payout_tc or view_ticket_payout_tc
Field Name Field Type Length Key Type Refers To Not Null Index Default Description
tc int PK tbl_timeclass.tc IN1A,IN2A,IN3A,IN4A,IN5A,IN6A,IN7A,IN8A time class id
cg int PK consolidation group id
date datetime PK NN IN1C,IN2C,IN3C,IN4C,IN5C,IN6C,IN7C,IN8C date/time of payout
shift char 1 PK NN shift
store_mid bigint PK,FK0A NN IN1A store mid
pcenter_mid bigint FK0A tbl_profit_center.mid IN2B 3 profit center mid
mealtime_mid bigint FK0A tbl_meal_time.mid IN3B 3 meal time mid
pocat_mid bigint FK0A tbl_payout_category.mid IN4B 3 payout category mid
wkstn_mid bigint FK0A tbl_workstation.mid IN5B 3 workstation mid
paymeth_mid bigint FK0A tbl_payment_method.mid IN6B 3 payment method mid
emp_mid bigint FK0A tbl_employee.mid IN7B 3 employee mid
mgr.mid bigint FK0A tbl_employee.mid IN8B 3 manager mid
base decimal 12,2
adjust decimal 8,2
net decimal 12,2
discount decimal 12,2
record_count int count of records composing this total record


tbl_close_day or view_close_day
Field Name Field Type Length Key Type Refers To Not Null Index Default Description
cg int consolidation group id
store_mid bigint PK,FK0A tbl_store.mid NN IN1A 3 store mid
thru_date datetime PK last date covered by this close day
thru_shift char 1 PK last shift covered by this close day
from_date datetime IN1B first date covered by this close day
from_shift char 1 IN1C first shift covered by this close day
dttm datetime date/time of close
zdttm datetime Zulu date/time of close
Views
Personal tools