SQL Views

For systems that consume APRO data at scale — BI tools, data warehouses, accounting exporters — the POS database exposes three read-only views that cover the core transactional footprint:

• vw_Receipts — receipts.
• vw_Receipt_Items — line items.
• vw_Payments — payments.

All three join via ReceiptId, so the full picture of a receipt is a three-table query.

Note

SQL views are made available on a per-deployment basis. Ask your APRO account manager to enable the read-only SQL user for your tenant.

1. vw_Receipts

One row per business transaction. The TransactionTypeId column distinguishes what the row represents.

Column	Type	Description
Id	GUID	Unique transaction record ID.
TransactionId	GUID	Unique transaction ID.
TransactionTypeId	enum	See Transaction types.
DateTime	datetime	Time of the transaction.
ReceiptNr	string	Receipt / invoice number.
TotalAmount	decimal	Receipt total.
TipAmount	decimal	Tip (on settlements).
DiscountAmount	decimal	Total discount applied.
BusinessDay	date	Virtual business day (fiscal).
BusinessId	integer	Operating company ID.
BusinessName	string	Operating company name.
TableId	integer	Table / spot ID.
TableNr	integer	Table number.
TableName	string	Table name.
StaffId	integer	Staff ID.
StaffNr	integer	Personnel number.
StaffName	string	Staff name.
CustomerId	integer	Customer ID.
CustomerNr	integer	Customer number.
CustomerName	string	Customer name.
DeviceId	integer	Device that performed the transaction.

Transaction types

TransactionTypeId	Meaning
1	Booking (order at the table or self-service).
2	Move (table change).
3	Settlement (close with payment).
4	Reversal (undo a booking).
5	Retrieve receipt (undo a settlement).
14	Outgoing invoice created.
25	Outgoing invoice paid.

2. vw_Receipt_Items

Line items. Join to vw_Receipts on ReceiptId.

Column	Type	Description
ReceiptId	GUID	Receipt ID (join to vw_Receipts.Id).
TransactionId	GUID	Transaction ID.
ReceiptItemId	GUID	Unique line item ID.
ProductCategoryId	integer	Category ID.
ProductCategoryNr	integer	Category number.
ProductCategoryName	string	Category name.
ProductId	integer	Product ID.
ProductNr	integer	Product number.
ProductName	string	Product name.
Quantity	decimal	Quantity sold.
OriginalPrice	decimal	Original unit price at the time of the sale.
EffectivePrice	decimal	Effective unit price after discounts.
OriginalAmount	decimal	Quantity × original price.
EffectiveAmount	decimal	Quantity × original price × discount factor.
DiscountAmount	decimal	Discount amount.
DiscountPercentage	decimal	Discount as a percentage of the original price.
TaxId	integer	Tax rate ID.
TaxName	string	Tax rate name.
TaxAmount	decimal	Tax rate as a decimal.

3. vw_Payments

Payments recorded against receipts. Join to vw_Receipts on ReceiptId.

Column	Type	Description
TransactionId	GUID	Transaction ID.
ReceiptId	GUID	Receipt ID.
PaymentId	GUID	Unique payment ID.
PredecessorReceiptId	GUID	Prior receipt this payment cancels / replaces (e.g. on a retrieved settlement).
DateTime	datetime	Time of the payment.
TotalAmount	decimal	Amount paid.
PaymentTypeId	integer	Payment method ID.
PaymentTypeNr	integer	Payment method number.
PaymentTypeName	string	Payment method name.

Example query

Turnover per business day, split by tax rate:

SELECT
  r.BusinessDay,
  ri.TaxName,
  SUM(ri.EffectiveAmount) AS net_turnover
FROM vw_Receipts AS r
JOIN vw_Receipt_Items AS ri ON ri.ReceiptId = r.Id
WHERE r.TransactionTypeId = 3        -- settlements only
  AND r.BusinessDay BETWEEN @from AND @to
GROUP BY r.BusinessDay, ri.TaxName
ORDER BY r.BusinessDay, ri.TaxName;