SQL Views

When you need direct SQL access to the POS — usually because APRO is installed on-premise alongside existing systems — the cash register database exposes a stable set of read-only views. They are intentionally shaped for the two most common consumers:

• On-premise integrations — accounting bridges, kitchen displays, stock management, loyalty engines — anything that runs inside the same network and wants a low-latency feed without going through REST.
• Existing BI workflows — Power BI, Tableau, Qlik, Metabase, Excel, native SQL Server Analysis Services — any tool that already understands SQL Server connections. Point the connector at the views and you get receipts, line items, payments and product master in the shape a data warehouse expects.

Views are read-only, queryable over the standard SQL Server protocol, and never join to operational tables that mutate under load — they are safe to drive dashboards and scheduled ETL jobs against.

SQL Views or Revenue API?

The SQL views and the Revenue API expose the same underlying data — pick whichever fits the runtime:

• SQL Views (this page) — flat, relational, on-premise. Perfect for ETL, Power BI / Tableau / Qlik / Metabase connectors and any workflow that wants bulk access via SQL.
• Revenue API — served over the cloud endpoint my.apro.at, returns a hierarchical JSON document with positions, payments and tax already nested per receipt. Better for real-time system-to-system integration, SaaS consumers and anything outside the local network.

Note

The read-only SQL user is provisioned per deployment. Ask your APRO account manager to enable access for your tenant and to share the server name, database, and credentials.

Overview

View	What it contains
vw_Receipts	One row per business transaction.
vw_Receipt_Items	Line items. Join on ReceiptId.
vw_Payments	Payments. Join on ReceiptId.
vw_Transactions	Light transaction index.
vw_TransactionTypes	Transaction-type lookup.
vw_Bi_Export	Denormalized receipt × item + payment summary — start here for Power BI.
vw_products_and_categories	Product master with category.

1. vw_Receipts

One row per business transaction. The TransactionType column distinguishes what the row represents; amounts are signed — reversal transactions (TransactionType = 4) return the booking with a negated amount so a plain SUM(TotalAmount) already nets them out.

Column	Type	Description
Id	GUID	Unique transaction record ID.
TransactionId	GUID	Transaction ID (join key for vw_Receipt_Items, vw_Payments).
TransactionType	enum	See Transaction types.
TransactionTypeName	string	Human-readable transaction type.
ReceiptNr	string	Receipt / invoice number.
TotalAmount	decimal	Gross total (signed — negative on reversals).
TipAmount	decimal	Tip (signed).
ReceiptDiscountAmount	decimal	Discount applied to the whole receipt (signed).
BookDate	datetime	Booking timestamp.
BusinessDay	date	Virtual fiscal business day.
ParentId	GUID | null	Predecessor receipt (e.g. retrieved settlement → original).
PaymentGroupId	integer | null	Payment group ID (e.g. Umsatzwirksam).
PaymentGroupName	string | null	Payment group name — useful as a turnover filter.
BusinessId	integer	Operating company ID.
BusinessName	string	Operating company name.
TableId	integer	Table ID.
TableNr	integer	Table number.
TableName	string	Table label.
AreaId	integer	Area / section ID (Bereich).
AreaNr	integer	Area number.
AreaName	string	Area name (e.g. Restaurant, Bar, Terrasse).
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

Only transactions with a TransactionType in (1, 2, 3, 4, 5, 14, 24, 25) are exposed through this view. The authoritative mapping lives in vw_TransactionTypes and is also joined onto every row via TransactionTypeName.

TransactionType	Meaning
1	Booking — order at the table or self-service.
2	Move — table change.
3	Settlement — paid receipt.
4	Reversal — undoes a booking (amounts negated).
5	Retrieve receipt — undoes a settlement.
14	Outgoing invoice created.
24	Outgoing invoice — ancillary (see vw_TransactionTypes).
25	Outgoing invoice paid.

2. vw_Receipt_Items

Line items. Join to vw_Receipts on ReceiptId / TransactionId. Values are signed the same way as the parent receipt.

Column	Type	Description
TransactionId	GUID	Transaction ID.
ReceiptId	GUID	Receipt ID (join to vw_Receipts.Id).
ReceiptItemId	GUID	Unique line item ID.
BookedItemId	GUID	Underlying booking ID (shared by bookings and their reversals).
BookedItemDate	datetime	Booking timestamp.
ProductCategoryId	integer	Category (Sparte) ID.
ProductCategoryNr	integer	Category number.
ProductCategoryName	string	Category name.
ProductCategoryAccountingNr	string	Accounting account number for the category.
ProductId	integer	Product ID.
ProductNr	integer	Product number.
ProductName	string	Product name.
Quantity	decimal	Quantity (signed on reversals / retrievals).
OriginalPrice	decimal	Unit price at the time of sale (signed on reversals).
EffectivePrice	decimal	Discounted unit price (signed on reversals).
OriginalAmount	decimal	Quantity × OriginalPrice (signed).
EffectiveAmount	decimal	Quantity × OriginalPrice × discount factor (signed).
DiscountAmount	decimal	Discount amount in money (signed on reversals / retrievals).
DiscountPercentage	decimal	Discount as % of the original price.
DiscountType	string	RABATT OFF for plain discounts, otherwise the campaign name (e.g. Happy Hour). Empty string if no discount.
TaxId	integer	Tax rate ID.
TaxName	string	Tax rate name.
TaxAmount	decimal	Tax rate as decimal.
TransactionTypeId	enum	Same codes as vw_Receipts.TransactionType.

3. vw_Payments

Payments recorded against receipts. Only settlements and their reversals are exposed (TransactionType in (3, 4, 5, 14, 24, 25)).

Column	Type	Description
TransactionId	GUID	Transaction ID.
ReceiptId	GUID	Receipt ID.
PaymentId	GUID	Unique payment ID.
TotalAmount	decimal	Amount paid (signed).
PaymentTypeId	integer	Payment method ID.
PaymentTypeNr	integer	Payment method number.
PaymentTypeName	string	Payment method name (e.g. Bar, Kreditkarte, Gutschein).
DateTime	datetime	Payment timestamp.
PredecessorReceiptId	GUID | null	When a settlement is retrieved, the original receipt it cancels.
Details	string | null	Free-form payment metadata (e.g. card masked PAN, voucher code).

4. vw_Transactions

Light index of every transaction. Useful for watermark-based ETL (pulling only new rows since the last checkpoint).

Column	Type	Description
TransactionId	GUID	Transaction ID.
TransactionType	enum	Transaction type.
TransactionTypeName	string	Human-readable name.
BookDate	datetime	Timestamp of the transaction.
DeviceId	integer	Device that performed it.

5. vw_TransactionTypes

Lookup for every transaction type the POS emits.

Column	Type	Description
Id	integer	Transaction-type ID.
Bezeichnung	string	Human-readable name (German).

6. vw_Bi_Export

The one-stop view for BI tools. Denormalized join of every turnover-relevant receipt with every line item, plus a comma-separated summary of the payment methods used on the receipt. Point Power BI or Tableau at this view and you have everything needed for revenue, margin, mix and payment-method dashboards without writing joins.

Columns are the union of vw_Receipts and vw_Receipt_Items (minus TransactionTypeId, which lives only on the receipt side), plus:

Column	Type	Description
Payments	string	Comma-separated list of PaymentTypeName values for the receipt (e.g. "Bar,Kreditkarte"). NULL on non-settlement rows.

Power BI example

1. Get data → SQL Server database — server + database as provided.
2. Import or DirectQuery mode, using the read-only user.
3. Pick dbo.vw_Bi_Export as the fact table.
4. Optionally add dbo.vw_Transactions, dbo.vw_Payments, dbo.vw_products_and_categories as companion tables and relate on TransactionId / ProductId.

Import mode is normally sufficient — the views are already aggregated to transaction grain and refresh in seconds for a typical venue.

7. vw_products_and_categories

Product master with category. Useful as a dimension alongside vw_Bi_Export when the BI tool wants product names to stay stable even if historical rows predate a rename.

Column	Type	Description
ProductId	integer	Product ID.
ProductNr	integer	Product number.
ProductName	string	Product name.
ProductCategoryId	integer	Category ID.
ProductCategoryNr	integer	Category number.
ProductCategoryName	string	Category name.
Price	decimal	Current list price (Preis1).

Only products where Geloescht = 0 (not soft-deleted) are included.

Example queries

Turnover per business day, split by tax rate — for a monthly KPI chart:

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.TransactionType IN (3, 4, 5)   -- settlements + reversals net out
  AND r.BusinessDay BETWEEN @from AND @to
GROUP BY r.BusinessDay, ri.TaxName
ORDER BY r.BusinessDay, ri.TaxName;

Top-10 products for the current month, using the BI view directly:

SELECT TOP 10
  ProductName,
  SUM(Quantity)        AS units_sold,
  SUM(EffectiveAmount) AS net_turnover
FROM vw_Bi_Export
WHERE BusinessDay >= DATEFROMPARTS(YEAR(GETDATE()), MONTH(GETDATE()), 1)
  AND TransactionType IN (3, 4, 5)
GROUP BY ProductName
ORDER BY net_turnover DESC;

Cash vs. card split for a single business day:

SELECT
  PaymentTypeName,
  SUM(TotalAmount) AS total
FROM vw_Payments
WHERE CAST(DateTime AS date) = @businessDay
GROUP BY PaymentTypeName;