Skip to content

Office Transaction Database

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

AR_EOM_Summary

Name Type Info
cardno INT PK
memName VARCHAR(100)
priorBalance MONEY
threeMonthCharges MONEY
threeMonthPayments MONEY
threeMonthBalance MONEY
twoMonthCharges MONEY
twoMonthPayments MONEY
twoMonthBalance MONEY
lastMonthCharges MONEY
lastMonthPayments MONEY
lastMonthBalance MONEY

Use: List of customer start/end AR balances over past few months

Maintenance: cron/nightly.ar.php, after updating ar_history, truncates ar_history_backup and then appends all of ar_history

CapturedSignature

Name Type Info
capturedSignatureID INT PK Increment
tdate DATETIME Indexed
emp_no INT
register_no INT Indexed
trans_no INT
trans_id INT
filetype CHAR(3)
filecontents BLOB

Use: This table contains digital images of customer signatures. The standard dtransactions columns indicate what transaction line the signature goes with. Filetype is a three letter extension indicating what kind of image it is, and filecontents is the raw image data. This data is in the database because it's the only existing pathway to transfer information from the lane to the server.

CashPerformDay

Name Type Info
proc_date DATETIME
emp_no SMALLINT Indexed
trans_num VARCHAR(25)
startTime DATETIME
endTime DATETIME
transInterval INT
items FLOAT
rings INT
Cancels INT
card_no INT

Use: Stores cashier performance metrics to speed up reporting.

CcReceiptView

View

Name Type Info
tranType VARCHAR(255)
amount MONEY
PAN VARCHAR(255)
entryMethod VARCHAR(255)
issuer VARCHAR(255)
name VARCHAR(255)
xResultMessage VARCHAR(255)
xApprovalNumber VARCHAR(255)
xTransactionID VARCHAR(255)
date INT
cashierNo INT
laneNo INT
transNo INT
transID INT
datetime DATETIME
sortorder INT

Depends on:

  • efsnetRequest
  • efsnetResponse
  • efsnetRequestMod

Use: View of transaction timing to generate cashier performance reports

CompleteSpecialOrder

Name Type Info
order_id INT Indexed
datetime DATETIME Indexed
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

Depends on:

  • PendingSpecialOrder

Use: This table is for a work-in-progress special order tracking system. Conceptually, it will work like a partial suspended transactions, where rows with a given order_id can be pulled in at a register when someone picks up their special order.

This particular table is for finished orders

PaycardTransactions

Name Type Info
paycardTransactionID INT PK Increment
dateID INT Indexed
empNo INT
registerNo INT Indexed
transNo INT Indexed
transID INT
previousPaycardTransactionID INT
processor VARCHAR(25)
refNum VARCHAR(50)
live TINYINT
cardType VARCHAR(15)
transType VARCHAR(15)
amount MONEY
PAN VARCHAR(19)
issuer VARCHAR(20)
name VARCHAR(50)
manual TINYINT
requestDatetime DATETIME
responseDatetime DATETIME
seconds FLOAT
commErr SMALLINT
httpCode SMALLINT
validResponse SMALLINT
xResultCode VARCHAR(8)
xApprovalNumber VARCHAR(20)
xResponseCode VARCHAR(8)
xResultMessage VARCHAR(100)
xTransactionID VARCHAR(12)
xBalance VARCHAR(8)
xToken VARCHAR(64)
xProcessorRef VARCHAR(24)
xAcquirerRef VARCHAR(100)

Use: This table records information about integrated card transactions.

The first set of columns simply identifies the transaction.

  • paycardTransactionID is an identity column. It should be unique at the lane level and unique in conjunction with registerNo at the server level.
  • dateID, empNo, registerNo, transNo, and transID refer to the corresponding tender record in dtransaction. The camelCase names instead of underscores are for compliance with newer project style guidlines.
  • previousPaycardTransactionID refers to a previous record in this table. A void transaction should refer to the previous approved transaction.

The next set of column has information about what was sent to the server.

  • processor is the name of the PHP class that's making the request
  • refNum is a reference number sent to the processor. This is usually just a memo field that will come back unchanged on the response.
  • live indicates whether it's a live or testing transaction
  • cardType is Credit, Debit, etc
  • transType is Sale, Return, etc
  • amount is the Sale, Return, etc amount. This value is always positive.
  • PAN is the last four digits of the card. Do not record full card numbers for credit or debit cards. May be permissible with some gift card providers to keep the whole number.
  • issuer is Visa, MasterCard, etc
  • name is the cardholder's name. This is not always available and depends on what's on the magnetic stripe.
  • manual indicates how the card was entered. 1 means keyed in, 0 means swiped.
  • requestDatetime is a timestamp when the request was sent

The last set of fields deal with the response. Fields that start with "x" are data returned by the processor.

  • responseDatetime is a timestamp when the response was received
  • seconds indicates how long the request took
  • commErr is cURL error code, if any
  • httpCode is the HTTP response code
  • validResponse is a normalized indicator of what happened. Not all processors use the same codes for approve, decline, etc 0 => no response at all 1 => approved 2 => declined 3 => processor reported error 4 => response was malformed
  • xResultCode indicates what happened - typically approved, declined, or an error
  • xApprovalNumber is the actual authorization number
  • xResponseCode is further data about the result such as a specific error code or decline code
  • xResultMessage is a descriptive text response of the the result or response code
  • xTransactionID is an additional processor reference like a sequence number
  • xBalance is remaining balance on the card
  • xToken is a reference value for making future modifications to the transaction
  • xProcessorRef is another reference number field
  • xAcquirerRef is another reference number field

Not all processors will provide data for all fields. At minimum, there should be a value for validResponse to show whether the transaction was approved. On approved transactions, there needs to be an xApprovalNumber and some kind of sequence or reference number in xTransactionID. There should be some value indicating approval in xResultMessage. All the other response fields are optional.

PendingSpecialOrder

Name Type Info
order_id INT Indexed
datetime DATETIME Indexed
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

Use: This table is for a work-in-progress special order tracking system. Conceptually, it will work like a partial suspended transactions, where rows with a given order_id can be pulled in at a register when someone picks up their special order.

This particular table is for orders that have not been picked up yet

SpecialOrderDeptMap

Name Type Info
dept_ID INT PK
map_to INT PK

Optional table for mapping product departments to alternate departments. Essentially, put entries into historic "special order" departments

SpecialOrderHistory

Name Type Info
specialOrderHistoryID INT PK Increment
order_id INT Indexed
entry_type VARCHAR(20)
entry_date DATETIME
entry_value TEXT

Depends on:

  • PendingSpecialOrder

Use: This table is for a work-in-progress special order tracking system. Conceptually, it will work like a partial suspended transactions, where rows with a given order_id can be pulled in at a register when someone picks up their special order.

This table stores a dated history for the order

SpecialOrders

Name Type Info
specialOrderID INT PK Increment
statusFlag INT
subStatus INT
notes TEXT
noteSuperID INT
firstName VARCHAR(30)
lastName VARCHAR(30)
street VARCHAR(255)
city VARCHAR(20)
state VARCHAR(2)
zip VARCHAR(10)
phone VARCHAR(30)
altPhone VARCHAR(30)
email VARCHAR(50)

This table just exists as an accumulator so that IDs in PendingSpecialOrder and CompletedSpecialOrder never conflict

TenderTapeGeneric

View

Name Type Info
tdate DATETIME
emp_no INT
register_no INT
trans_no INT
trans_subtype VARCHAR(2)
tender MONEY

Depends on:

  • dlog (view)

Use: This view lists all a cashier's tenders for the day. It is used for generating tender reports at the registers.

Ideally this deprecates the old system of having a different view for every tender type.

Behavior in calculating trans_subtype and total may be customized on a per-co-op basis without changes to the register code

ar_history

Name Type Info
card_no INT Indexed
charges MONEY Default=0
payments MONEY Default=0
tdate DATETIME
trans_num VARCHAR(50)

Depends on:

  • transarchive (table), i.e. dlog_15 (table)
  • was: dlog (view)

Depended on by:

  • table ar_history_backup and its descendents
  • view ar_history_sum and its descendents

Use: This table stores charges and payments on a customer's in-store charge account.

Maintenance: This table should be updated in conjunction with any day-end polling system to copy appropriate rows from transarchive to ar_history cron/nightly.ar.php appends selected columns from appropriate rows from dlog_15 (i.e. dtransactions)

ar_history_backup

Name Type Info
card_no INT Indexed
charges MONEY Default=0
payments MONEY Default=0
tdate DATETIME
trans_num VARCHAR(50)

Depends on:

  • dlog (view)
  • ar_history (table)

Depended on by:

  • Table AR_EOM_Summary

Use: Stores an extra copy of ar_history

Maintenance: cron/nightly.ar.php, after updating ar_history, truncates and then appends all of ar_history

ar_history_sum

Name Type Info
card_no INT PK
charges MONEY
payments MONEY
balance MONEY

Depends on:

  • ar_history (table)

Use: Summary of all charges and payments per customer (One row per customer.)

ar_history_today

View

Name Type Info
card_no INT
charges MONEY
payments MONEY
tdate DATETIME
trans_num VARCHAR(50)

Depends on:

  • dlog (view)
  • AR departments in Fannie config.

Use: In-store charge account activity summary for the current day. Combine with ar_history for a "live" view of account status

ar_history_today_sum

View

Name Type Info
card_no INT
charges MONEY
payments MONEY
balance MONEY

Depends on:

  • dlog (view)

Use: Total charges and payments for the current day by member number

ar_live_balance

View

Name Type Info
card_no INT PK
totcharges MONEY
totpayments MONEY
balance MONEY
mark TINYINT

Depends on:

  • core_op.custdata (table)
  • ar_history_sum (table)
  • ar_history_today_sum (view)

Use: This view lists real-time store charge balances by membership. The column "mark" indicates the balance changed today

dlog

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)

dlog_15

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:

  • dlog_90_view (view)

Use: This is just a look-up table. It contains the past 15 days worth of dlog entries. For reports on data within that time frame, it's faster to use this small table.

Maintenance: Truncated and populated by cron/nightly.dtrans.php

dlog_90_view

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:

  • transarchive (table)

Use: This view applies the same restrictions as dlog but to the table transarchive. With WFC's dayend polling, transarchive contains transaction entries from the past 90 days, hence the name of this view. For queries in the given time frame, using the view can be faster or simpler than alternatives.

dtransactions

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

efsnetRequest

Name Type Info
date INT
cashierNo INT
laneNo INT
transNo INT
transID INT
datetime DATETIME
refNum VARCHAR(50)
live TINYINT
mode VARCHAR(32)
amount MONEY
PAN VARCHAR(19)
issuer VARCHAR(16)
name VARCHAR(50)
manual TINYINT
sentPAN TINYINT
sentExp TINYINT
sentTr1 TINYINT
sentTr2 TINYINT
efsnetRequestID INT Indexed

Use: This table logs information that is sent to a credit-card payment gateway. All current paycard modules use this table structure. Future ones don't necessarily have to, but doing so may enable more code re-use.

Some column usage may vary depending on a given gateway's requirements and/or formatting, but in general:

cashierNo, laneNo, transNo, and transID are equivalent to emp_no, register_no, trans_no, and trans_id in dtransactions (respectively).

mode indicates the type of transaction, such as refund or sale. Exact value can vary from gateway to gateway.

PAN is the cardnumber - for the love of $deity only save the last 4 digits here - issuer is Visa, MC, etc, and name is the cardholder's name (if available).

The sent* columns indicate which information was sent. Most gateways will accept PAN + expiration date, or either track. Sending both tracks is usually fine; I've never seen a system where you send all 4 pieces of card info.

efsnetRequestID is an incrementing ID columns. This is unique at a lane level but not an overall system level since different lanes will increment through the same ID values. The combination of laneNo and efsnetRequestID should be unique though.

efsnetRequestMod

Name Type Info
date INT
cashierNo INT
laneNo INT
transNo INT
transID INT
datetime DATETIME
origRefNum VARCHAR(50)
origAmount MONEY
origTransactionID VARCHAR(12)
mode VARCHAR(32)
altRoute TINYINT
seconds FLOAT
commErr INT
httpCode INT
validResponse SMALLINT
xResponseCode VARCHAR(4)
xResultCode VARCHAR(8)
xResultMessage VARCHAR(100)

Depends on:

  • efsnetRequest (table)

Use: This table logs information that is returned from a credit-card payment gateway when modifying an earlier transaction. Generally, this means some kind of void. All current paycard modules use this table structure. Future ones don't necessarily have to, but doing so may enable more code re-use.

Some column usage may vary depending on a given gateway's requirements and/or formatting, but in general:

cashierNo, laneNo, transNo, and transID are equivalent to emp_no, register_no, trans_no, and trans_id in dtransactions (respectively).

mode is the operation type. Exact syntax varies by gateway. Some gateways provide multiple addresses. Using a different one can be noted in altRoute.

seconds, commErr, and httpCode are curl-related entries noting how long the network request took and errors that occurred, if any.

the x* columns vary a lot. What to store here depends what the gateway returns.

efsnetResponse

Name Type Info
date INT
cashierNo INT
laneNo INT
transNo INT
transID INT
datetime DATETIME
refNum VARCHAR(50)
seconds FLOAT
commErr INT
httpCode INT
validResponse SMALLINT
xResponseCode VARCHAR(4)
xResultCode VARCHAR(8)
xResultMessage VARCHAR(100)
xTransactionID VARCHAR(12)
xApprovalNumber VARCHAR(20)
efsnetRequestID INT Indexed

Depends on:

  • efsnetRequest (table)

Use: This table logs information that is returned from a credit-card payment gateway after sending a [non-void] request. All current paycard modules use this table structure. Future ones don't necessarily have to, but doing so may enable more code re-use.

Some column usage may vary depending on a given gateway's requirements and/or formatting, but in general:

cashierNo, laneNo, transNo, and transID are equivalent to emp_no, register_no, trans_no, and trans_id in dtransactions (respectively).

seconds, commErr, and httpCode are curl-related entries noting how long the network request took and errors that occurred, if any.

the x* columns vary a lot. What to store here depends what the gateway returns.

efsnetTokens

Name Type Info
expireDay DATETIME
refNum VARCHAR(50) PK
token VARCHAR(100) PK
processData VARCHAR(255)
acqRefData VARCHAR(255)

Depends on:

  • efsnetRequest (table)
  • efsnetResponse (table)

Use: This table logs tokens used for modifying later transactions.

expireDay is when(ish) the token is no longer valid

refNum maps to efsnetRequest & efsnetResponse records

token is the actual token

processData and acqRefData are additional values needed in addition to the token for certain kinds of modifying transactions

equity_history_sum

Name Type Info
card_no INT PK
payments MONEY
startdate DATETIME
mostRecent DATETIME

Depends on:

  • stockpurchases (table)

Use: Summary of all equity transactions (One row per customer.)

equity_live_balance

View

Name Type Info
memnum INT PK
payments MONEY
startdate DATETIME

Depends on:

  • core_op.meminfo (table)
  • equity_history_sum (table)
  • stockSum_today (view)

Use: This view lists real-time equity balances by membership

houseCouponThisMonth

View

Name Type Info
card_no INT
upc VARCHAR(13)
quantity FLOAT

Depends on:

  • dlog (view)
  • dlog_90_view (view)

Use: List of custom coupons redeemed, per member

memChargeBalance

View

Name Type Info
CardNo INT
availBal MONEY
balance MONEY
mark INT

Depends on:

  • core_op.custdata (table)
  • ar_live_balance (view of t.dtransactions -> .v.dlog)

Use: This view lists real-time store charge balances by membership. This view gets pushed to the lanes as a table to speed things up The "mark" column indicates an account

stockSumToday

View

Name Type Info
card_no INT
totPayments MONEY
startdate DATETIME

Depends on:

  • dlog (view)

Use: This view lists equity activity for the current day. It exists to calculate balances in real time.

The view's construction depends on Fannie's Equity Department configuration

stockpurchases

Name Type Info
card_no INT Indexed
stockPurchase MONEY
tdate DATETIME
trans_num VARCHAR(50)
dept INT

Depends on:

  • dlog (table)

Use: This table equity related transaction info. This table should be updated in conjunction with any day-end polling system to copy appropriate rows from dtransactions to stockpurchases

suspended

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

Depends on:

  • dtransactions (table)

Use: This table exists so that transactions that are suspended at one register can be resume at another.

When a transaction is suspended, that register's localtemptrans table is copied here. When a transaction is resumed, appropriate rows are sent from here to that register's localtemptrans table.

suspendedtoday

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

Depends on:

  • suspended (table)

Use: This view omits all entries in suspended that aren't from the current day. Resuming a transaction from a previous day wouldn't necessarily cause problems, but "stale" suspended transactions that never get resumed could eventually make the list of available transactions unwieldy.

transarchive

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

Depends on:

  • dtransactions (table)

Use: This is a look-up table. Under WFC's day end polling, transarchive contains the last 90 days' transaction entries. For queries in that time frame, using this table can simplify or speed up queries.

Maintenance: cron/nightly.dtrans.php appends all of dtransactions and deletes records older than 90 days.

unpaid_ar_balances

View

Name Type Info
card_no INT
old_balance MONEY
recent_payments MONEY

Depends on:

  • ar_history (table)

Depended on by:

  • unpaid_ar_today (view)

Use: This view lists A/R balances older than 20 days and payments made in the last 20 days.

The logic is pretty WFC-specific, but the general idea is to notify customers that they have a balance overdue at checkout

unpaid_ar_today

View

Name Type Info
card_no INT
old_balance MONEY
recent_payments MONEY
mark INT

Depends on:

  • ar_history (table)
  • unpaid_ar_balances (view of t.ar_history)
  • ar_history_today_sum (view of t.dtransactions via v.dlog)

Depended on by:

  • cron/LanePush/UpdateUnpaidAR.php to update each lane opdata.unpaid_ar_today.recent_payments

Use: This view adds payments from the current day to the view unpaid_ar_balances

The logic is pretty WFC-specific, but the general idea is to notify customers that they have a balance overdue at checkout

voidTransHistory

Name Type Info
tdate DATETIME Indexed
description VARCHAR(40)
trans_num VARCHAR(20)
total MONEY

Use: Store transaction numbers for voided transactions (they're identified by comment lines which are excluded from dlog views)

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