Reporting for Fabric / SQL
Use Bevica data in Microsoft Fabric or SQL. Covers paid reserves, broking, cellarage, duty status, and reporting patterns for sales, margin, and inventory.
Business Central (BC) comes with many out-of-the-box reports and data analysis capabilities. On top of this, it's easy to define Power BI reports that read data from standard and custom APIs, define Power BI metrics scorecards, and embed all of these directly in the Business Central client.
For customers with more advanced data science or business intelligence scenarios that require richer data engineering or data integration, Microsoft Fabric might be a good option.
BC does not provide built‑in Fabric reporting. However, using standard Microsoft capabilities, data can be exposed into Microsoft Fabric for reporting, typically with support from a specialist third-party provider.
This documentation:
is aimed at external reporting contractors with access to BC + extension tables in Microsoft Fabric.
familiarity with SQL and BC table structures.
concentrates on reporting based on the Fine Wine suite of extensions in Bevica.
assumes no prior knowledge of Bevica business processes.
What is Bevica & Fine Wine?
Bevica has a suite of BC extensions purpose‑built for the fine wine sector. It extends the standard software with industry‑specific functionality. Fine wine capabilities are delivered through the following areas:
Paid Reserves — customer-owned stock held and managed on their behalf.
Broking — selling a customer's paid reserve stock to another customer.
Cellarage — invoicing customers for storage of their paid reserve stock.
En Primeur — selling wine before it is physically available (futures).
Additional features include: Bottling, HMRC Duty tracking, Market Values.
Core principle: the wine merchant holds stock on behalf of its customers. The stock has been paid for, is stored in the merchant’s warehouse, and is managed by the merchant — including storage, transfers, withdrawals, and resale (broking).
Extension Landscape
Bevica is modular. Each extension adds specific functionality. All depend on Bevica Essential as the base.
Bevica Essential
Core: items, sales, purchasing, paid reserves, broking worksheets
TVT Paid Reserve Information, TVT Paid Reserve Led. Entry, TVT Broking Worksheet Line
Bevica Fine Wine
Posting logic for paid reserve entries during item journal posting
Extends Item Ledger Entry, Value Entry, Sales Line
Bevica Broking
Broking worksheet creation and management
Uses TVT Broking Worksheet Line (in Essential)
Bevica Cellarage
Storage fee calculation and invoicing
TVTCE Cellarage Ledger Entry, TVTCE Cellarage Worksheet, TVTCE Cellarage Rate
Key Concepts for Reporting
The Dual Ledger Pattern
When a paid reserve transaction posts, records are created in two parallel ledgers:
Standard BC Ledger — Item Ledger Entry + Value Entry (for inventory and costing)
Paid Reserve Ledger — TVT Paid Reserve Led. Entry (for reserve-specific tracking)
These are linked by TVT Paid Reserve No. and Item Ledger Entry No..
Identifying Bevica Transactions
All Bevica-specific transactions can be identified by checking specific extension fields on standard BC tables:
TVT Paid Reserve No.
Item Ledger Entry
This item movement relates to a paid reserve
TVT Paid Reserve No.
Value Entry
This value/cost entry relates to a paid reserve
TVTBE Transaction Type
Sales Line / Posted Sales Invoice Line
The line has a Bevica transaction classification
TVT Broking Id
Sales Line / Purchase Line
This line is part of a broking transaction
TVT Broking Owner Code
Sales Line / Purchase Line
Identifies the paid reserve owner in broking
TVT Sales Identifier
Sales Header
Identifies the type of sale (e.g., "Cellarage"). This is not mandatory.
TVT Sales Document Type
Sales Header / Purchase Header
Identifies a type of document and linked to Sales Identifier. Not mandatory.
Paid Reserves
What is a Paid Reserve?
A paid reserve is stock that a customer has purchased but which the merchant continues to hold and manage on their behalf. It is the customer's property, stored in the merchant's warehouse. The merchant may charge storage fees (cellarage) and can sell it on the customer's behalf (broking).
Each paid reserve has a unique identifier (No.) and tracks a specific item, quantity, customer, location, and duty status.
Primary tables involved
TVT Paid Reserve Information (70528)
Master record — one per reserve. Shows what's held, for whom, how much remains
TVT Paid Reserve Led. Entry (70529)
Transaction history — every movement (in, out, transfer, split, cancel)
TVT Paid Res. Booking Entry (70542)
Quantity tracking — what's booked on open documents or broking worksheets. This enables tracking of non-posted transactions in Bevica.
TVT Paid Reserve Summary (70543)
Aggregated view — by item, location, variant, customer
Extension Fields on Standard BC Tables
Item Ledger Entry
TVT Paid Reserve No. (70551)
Links item movements to a specific paid reserve
Item Ledger Entry
TVT Paid Reserve Customer No. (70550)
Identifies the customer who owns the reserve
Value Entry
TVT Paid Reserve No. (70551)
Links value/cost entries to a paid reserve
Sales Line (and posted variants)
TVT Paid Reserve No. (70551)
Which reserve the sales line relates to
Sales Line
TVTBE Transaction Type (70550)
What type of reserve transaction
Sales Line
TVT Paid Reserve Split (70555)
How the reserve is split on posting
Key Fields on TVT Paid Reserve Information
No.
Code[20]
Unique paid reserve identifier (e.g., "PR-001")
Source Type
Enum
Customer or Vendor
Source No.
Code[20]
Customer No. who owns the reserve
Item No.
Code[20]
The item being held
Quantity
Decimal
Original quantity placed into reserve
Remaining Quantity
Decimal (FlowField)
Current balance — calculated from ledger entries
Qty. Booked
Decimal (FlowField)
Quantity booked on open documents
Qty. on Broking
Decimal (FlowField)
Quantity on the broking worksheet
Unit Price
Decimal
Customer's original purchase price (LCY)
Unit Cost
Decimal
Company's cost of the goods (LCY)
Location Code
(via ledger)
Where the stock is physically stored
Variant Code
(via ledger)
Duty status — e.g., Duty Paid / Duty Free
Rotation No.
Code[50]
Wine vintage/rotation tracking
Posting Date
Date
Date the reserve was created
Document No.
Code[20]
Source sales document that created the reserve
How to Identify Paid Reserve Stock
In Item Ledger Entry:
In Value Entry:
Paid Reserve Lifecycle
Paid Reserve Reporting — Inventory Position
Important — see Paid Reserves in Item Ledger & Value Entries for a detailed explanation of how paid reserves appear as double entries in Item Ledger Entries and Value Entries, and why this matters for reporting.
To report on current paid reserve inventory, use the TVT Paid Reserve Information table:
Important: Remaining Quantity is a FlowField calculated from TVT Paid Reserve Led. Entry. In Fabric/SQL you may need to calculate this yourself by summing ledger entries:
Paid Reserves in Item Ledger & Value Entries
This section explains how paid reserve transactions appear in the standard BC Item Ledger Entry and Value Entry tables, and why the report builder must account for the "double-entry" pattern to avoid double-counting.
The Double-Entry Pattern: Sale into Reserve
When a customer buys wine to hold in paid reserve, the sales order posting creates two sets of item ledger entries for the same physical goods:
Entry 1: The Sale (standard BC)
This is the normal sales posting — goods are shipped to the customer. In the item ledger:
Entry Type
Sale
Quantity
-6 (negative, stock leaving)
TVT Paid Reserve No.
(blank)
Document Type
Sales Shipment
This entry has corresponding Value Entries recording the sales amount and cost amount. From BC's perspective, this is a regular sale — revenue is recognised, stock is reduced.
Entry 2: The Reserve Receipt (Bevica)
Bevica then creates a positive item ledger entry to receive the stock back into inventory under the paid reserve:
Entry Type
Sale
Quantity
+6 (positive, stock back in under PR)
TVT Paid Reserve No.
PR-001
TVT Paid Reserve Customer No.
CUST001
This entry has corresponding Value Entries with cost amounts, also marked with TVT Paid Reserve No..
Why this matters for reporting
If you sum all item ledger entries with Entry Type = "Sale", the sale into reserve entries will net to zero quantity (the -6 and +6 cancel out). This is correct from an inventory perspective — the physical stock hasn't left the building.
But if you are building sales reports:
Sales Revenue: The revenue from the sale into reserve is on Entry 1 (the standard sale). This IS genuine revenue — the customer has paid.
Sales Quantity: If you sum all Sale entry quantities, the PR receipt (+6) will offset the sale (-6), making it appear as if nothing was sold. You must exclude paid reserve entries (
TVT Paid Reserve No. <> '') from sales quantity.Cost of Sales: The cost of the sale is on Entry 1. Entry 2 (the PR receipt) has a cost that represents the inventory being held. You must exclude paid reserve entries from cost of sales to avoid the cost being netted off.
Inventory Valuation: Entry 2 adds the stock back under the PR flag. Exclude paid reserve entries (
TVT Paid Reserve No. <> '') from your company inventory valuation — this is customer-owned stock.
Valuation on the Paid Reserve Item Ledger Entry
The paid reserve receipt ILE (Entry 2 above) creates Value Entries — but at what value?
The “cost” recorded on the paid reserve ILE is the customer’s sale price, not the item’s cost to the company.
When posting the reserve receipt back into inventory, the system sets:
This means the Value Entry for a paid reserve ILE contains:
Cost Amount (Actual)
Customer’s purchase price × quantity — the amount they paid for the reserve
Sales Amount (Actual)
Zero (this is a receipt into inventory, not a sale)
The TVT Paid Reserve Information record stores both values separately:
Unit Price
What the customer paid (their purchase/sale price)
Unit Cost
The company’s cost of the goods
The Withdrawal Pattern
When a customer withdraws stock from their paid reserve, the posting creates:
Entry 1: The Sale/Shipment
Entry Type
Sale
Quantity
-6 (negative, stock leaving)
TVT Paid Reserve No.
PR-001
TVT Paid Reserve Customer No.
CUST001
Note: Unlike a sale into reserve, the withdrawal sale IS marked with the Paid Reserve No. because it is drawing directly from the reserve.
This has corresponding Value Entries for the withdrawal amount and cost.
What this means for reporting
Sales Quantity: This is already a paid reserve entry, so if you are filtering
Paid Reserve Entry = FALSE, it will be excluded from sales quantity. This is correct — a withdrawal is not a commercial sale.Sales Revenue: The withdrawal invoice amount appears in the Value Entry. Whether to include this depends on your reporting purpose — it is revenue from the customer but for releasing their own goods.
Cost of Sales: The cost of the withdrawal is against a paid reserve entry. Excluding paid reserve entries from cost of sales automatically excludes withdrawal costs. This is correct.
Inventory: The -6 reduces the paid reserve inventory. Since PR entries are excluded from company inventory, this has no impact on company stock valuation.
The Practical Filter: Paid Reserve Entry Flag
The simplest and most reliable way to handle paid reserves in reporting is to create a boolean flag on your dataset:
Then apply this filter consistently:
Sales Quantity
Paid Reserve Entry = FALSE
Sales Amount
No filter (includes all revenue) OR Paid Reserve Entry = FALSE for trading-only view
Cost of Sales
Paid Reserve Entry = FALSE
Sales Margin
Paid Reserve Entry = FALSE
Inventory Quantity
Paid Reserve Entry = FALSE
Inventory Valuation
Paid Reserve Entry = FALSE
PR Inventory Quantity
Paid Reserve Entry = TRUE
PR Sales Quantity
Paid Reserve Entry = TRUE AND Entry Type = Sale
Summary: What Each ILE Entry Type + PR Flag Combination Means
Sale
FALSE
Negative
Normal commercial sale — goods shipped to customer
Sale
TRUE (+ve qty)
Positive
Stock received INTO paid reserve (the "other half" of a sale into reserve)
Sale
TRUE (-ve qty)
Negative
Withdrawal FROM paid reserve — customer taking delivery
Purchase
FALSE
Positive
Normal purchase receipt
Purchase
TRUE
Positive
Purchase into paid reserve (e.g., from broker or import)
Transfer
FALSE
+/-
Standard stock transfer between locations
Transfer
TRUE
+/-
Paid reserve transfer (location move, duty transfer)
Duty Status (Duty Free / Duty Paid)
Overview
Wine can be held in two duty statuses:
Duty Free (DF) — stored in a bonded warehouse, duty not yet paid
Duty Paid (DP) — duty has been paid, goods cleared
In Bevica, duty status is tracked via the Variant Code on item ledger entries and paid reserves. When a customer withdraws Duty Free stock for Duty Paid delivery, Bevica automatically calculates and charges duty and VAT.
Identifying Duty Status
Duty Transfers
A duty transfer changes a paid reserve from Duty Free to Duty Paid (or vice versa). These appear in the Paid Reserve Ledger with Entry Type = 2 ("Transfer Stock") and a Variant Code change.
On sales lines, TVTBE Transaction Type = 6 ("Duty Transfer") indicates a duty transfer.
HMRC / Duty Tracking
Item Ledger Entries include the field TVTHM HMRC Movement Type (Enum) which classifies movements for HMRC reporting purposes. This is relevant for duty reporting but may not be needed for standard financial/sales reporting.
Paid Reserve Withdrawals
What is a Withdrawal?
A withdrawal is when a customer takes delivery of stock from their paid reserve. It is processed via a Sales Order with Transaction Type = "Withdrawal". The customer is "buying" their own stock — the sale ships goods from the reserve and creates a sales invoice.
Critical for reporting — the item line always has Unit Price = 0 and Unit Cost = 0. This is set explicitly by the system, not by the user. The withdrawal item line therefore contributes zero sales revenue and zero cost to the posted invoice. There is no trading margin on a withdrawal item line.
How to Identify Withdrawals
On Posted Sales Invoice Lines:
On Item Ledger Entries:
On Paid Reserve Ledger:
Withdrawal Impact on Other Reports
Inventory Valuation
Withdrawal reduces paid reserve stock. Since paid reserves are typically excluded from standard inventory valuation, the withdrawal itself should also be excluded.
Sales Margin
A withdrawal sale is not a normal commercial sale. The customer is receiving their own stock. Including withdrawals in sales margin analysis would be misleading.
Revenue Reporting
Withdrawal invoices create revenue entries, but this is the customer paying for release of their own goods (plus any duty/VAT). Treat separately from normal trading revenue.
Duty and VAT Lines on Withdrawal
When withdrawing Duty Free stock for delivery to a Duty Paid address, Bevica automatically inserts two additional G/L Account lines on the sales order. This handles the duty liability and the VAT on the original reserve value.
Trigger Condition
Both conditions must be true:
IsDutyFreeVariantCode(ILE."Variant Code")
The item ledger entry being withdrawn was held as Duty Free stock
IsDutyPaidVariantCode(SalesHeader."TVT Duty Status")
The sales order's Duty Status equals Duty Paid (i.e. customer wants delivered duty paid)
If jurisdictions are enabled, Bevica applies jurisdiction-specific duty rates using the ILE's storage jurisdiction and the delivery jurisdiction.
Line 1 — Duty Charge
Type
G/L Account
No.
BevicaSetup."PR Withdrawal Duty Acc."
Quantity
Same as the withdrawal item line
Unit Price
Per-unit duty amount (calculated by the duty engine, currency-converted if applicable)
VAT
Standard VAT applied via posting groups (customer pays VAT on duty)
TVT Paid Reserve No.
Copied from the item line
TVT Paid Reserve ILE No.
Copied from the item line
TVT Parent Line No.
Line No. of the withdrawal item line
Line 2 — Full VAT on Reserve Value
This line collects the VAT on the original paid reserve purchase price — i.e. the VAT the customer owes on the underlying value of the goods now that they are leaving the duty-suspended regime.
Type
G/L Account
No.
BevicaSetup."PR Withdrawal Full VAT Acc."
Quantity
Same as the withdrawal item line
Unit Price
PaidReserveInfo."Unit Price" × VAT% / 100 (rounded to 2dp)
VAT
Full VAT posting setup — this line is the VAT amount; it posts to a dedicated full VAT G/L account
TVT Paid Reserve No.
Copied from the item line
TVT Paid Reserve ILE No.
Copied from the item line
TVT Parent Line No.
Line No. of the withdrawal item line
Reporting Implications
Both extra lines are G/L Account type, so they do not appear in Item Ledger Entries. They appear in Customer Ledger Entries (via the invoice) and as G/L entries.
They are linked to their parent item line via
TVT Parent Line No.To aggregate total withdrawal receipts per reserve, join on
TVT Paid Reserve No.across all three line types.For a clean margin analysis: the item line has zero cost and zero revenue; the duty and VAT lines are pass-through charges, not trading margin.
Broking
What is Broking?
Broking is when the merchant sells a customer's paid reserve stock to a different customer. The merchant acts as broker — buying from the original reserve owner (via a linked purchase order to the "contra vendor") and selling to the new buyer.
The merchant earns a broking fee — the difference between buying and selling price.
Tables Involved
TVT Broking Worksheet Line (70553)
The broking "list" — confirmed reserves available for sale
TVT Broking Status Code (70552)
Workflow status master (e.g., Draft, Quoted, Confirmed)
TVT Paid Res. Booking Entry (70542)
Tracks booked quantities against broking lines
TVT Paid Reserve Led. Entry (70529)
Entry Type = 20 ("Broking Purchase") for broking movements
Extension Fields on Sales/Purchase Documents
Sales Line
TVT Broking Id (70556)
Links to Broking Worksheet Line SystemId
Sales Line
TVT Broking Apply Entry No. (70557)
Links to the original ILE of the paid reserve
Sales Line
TVT Broking Paid Res. No. (70558)
Which paid reserve is being sold
Sales Line
TVT Broking Owner Code (70559)
Customer who owns the paid reserve
Purchase Header
TVT Broking Sales Order No. (70581)
Links the PO back to the triggering sales order
Purchase Header
TVT Broking Owner Code (70559)
Paid reserve owner
Purchase Line
TVT From Broking Sales Line Id (70554)
Links PO line to SO line
How to Identify Broking Transactions
In Sales Lines / Posted Sales Invoice Lines:
In Purchase Lines / Posted Purchase Invoice Lines:
In Item Ledger Entries:
In Paid Reserve Ledger:
Broking Financial Flow
A broking transaction creates two linked documents:
Sales Order to the new buyer (at the selling price)
Purchase Order from the original reserve owner's "contra vendor" (at the buying price)
Key Fields on TVT Broking Worksheet Line
Source No.
Customer who owns the paid reserve
Item No.
The item being brokered
Paid Reserve No.
FK to TVT Paid Reserve Information
Quantity
Total quantity available for broking
Remaining Quantity
Quantity not yet sold/posted
Posted Quantity
Quantity already posted via sales/purchase
Confirmed
TRUE = confirmed and available for sale
Closed
TRUE = fully posted, no remaining quantity
Buying Unit Price (LCY)
Price paid to original owner
Selling Unit Price (LCY)
Price charged to new buyer
Broking Fee %
Fee percentage
Min. Broking Fee Amt. (LCY)
Minimum fee floor
Purch. to Stock
TRUE = purchased to company stock (not sold to customer)
Broking Profitability
Contra Vendor
Each customer who has paid reserves that can be brokered must have a linked Contra Vendor (field TVT Contra Vendor No. on the Customer table). This is the vendor account used for the purchase side of broking transactions.
Cellarage
What is Cellarage?
Cellarage is the fee charged to customers for storing their paid reserve stock. The merchant periodically calculates charges based on the quantity of equivalent cases held, applies tiered pricing rates, generates sales invoices, and records entries in a dedicated cellarage ledger.
Tables Involved
TVTCE Cellarage Ledger Entry (72652)
Posted cellarage charges — the main reporting table
TVTCE Cellarage Worksheet (72653)
Staging table — calculated charges before invoicing
TVTCE Cellarage Rate (72650)
Tiered pricing — rate per equivalent case at volume thresholds
TVTCE Cellarage Rate Group (72651)
Groups customers into pricing tiers
TVTCE Cellarage Setup (72655)
Configuration — GL account, default rate group, invoicing period
TVTCE Cellarage Sales Line (72654)
Links sales invoice lines to cellarage period dates
Extension Fields
TVT Paid Reserve Information
TVTCE Last Cellarage End Date
Prevents double-invoicing — last period invoiced
TVT Paid Reserve Information
TVTCE Cellarage Charge Type
Default charge behaviour (Blank/Exempt/Zero Value)
TVT Paid Reserve Information
TVTCE Cellarage Amount (LCY)
FlowField — sum of all cellarage ledger entries
Customer
TVTCE Cellarage Rate Gr. Code
Customer's pricing tier
Key Concepts
Equivalent Cases
Cellarage is charged per 9-litre equivalent case, which is the standard wine industry unit for comparing different container sizes. The calculation is:
The divisor depends on the Volume Unit configured in Bevica Setup:
ml (millilitres)
9,000
750ml bottle × 12 = 9,000ml = 1 eq. case
cl (centilitres)
900
75cl bottle × 12 = 900cl = 1 eq. case
dl (decilitres)
90
7.5dl bottle × 12 = 90dl = 1 eq. case
l (litres)
9
0.75l bottle × 12 = 9l = 1 eq. case
In SQL, if the Item table stores Unit Volume in centilitres (the most common setup):
Important: Check the Bevica Setup Volume Unit field to confirm which unit is in use. The divisor must match. One 9-litre equivalent case = 12 standard 75cl bottles = 1 standard case of 12.
Tiered Pricing
Rates are volume-based. The more a customer stores, the lower the per-case rate:
STANDARD
0
£0.50
STANDARD
100
£0.45
STANDARD
500
£0.40
The system finds the highest threshold that the customer's total volume meets.
Charge Types
0
(blank)
Normal — charge calculated and invoiced
1
Exempt
Skipped entirely — no invoice line created
2
Zero Value
Invoice line created at £0 — for tracking/visibility
Cellarage Invoicing Flow
Identifying Cellarage in Documents
Sales Invoices:
Cellarage Ledger Detail:
Cellarage Revenue Reporting
Building a Reporting Dataset — Sales, Cost, Margin
This section provides practical guidance for building a sales reporting dataset from Item Ledger Entries and Value Entries. The patterns here are based on the proven Bevica Power BI dataset, adapted for SQL/Fabric.
Core Principle: Filter on Paid Reserve Entry
The single most important pattern when building sales measures: always decide whether to include or exclude paid reserve entries, and be explicit about the choice.
Create a boolean column in your dataset:
This flag drives every decision below.
Sales Quantity
Sales Quantity should EXCLUDE paid reserve entries.
Why: A sale into reserve creates a negative ILE (the sale) AND a positive ILE (the reserve receipt). If you include both, they net to zero. Even individually, the reserve receipt is not a "sale" to report on.
Sales Amount
Sales Amount can be reported with or without paid reserve entries, depending on purpose:
Cost of Sales
Cost of Sales should EXCLUDE paid reserve entries.
Why: The cost entries against paid reserve ILEs represent the customer's stock value, not the company's cost of goods sold. Including them would distort margin.
Sales Margin
Sales Margin should be calculated on non-paid-reserve items only:
Consider also:
Including Non-Inventoriable Costs (e.g., distribution charges) for a fully-loaded margin
Using "Line Type" to filter to item-only transactions (some datasets add non-item sales lines for charges and services)
Inventory Valuation
Company inventory EXCLUDES paid reserve entries. Paid reserve inventory is a separate measure.
Inventory Ageing & Turns
When calculating days in stock, turns per year, or oldest stock dates, always exclude paid reserve entries:
Quantity Conversions
Bevica uses three quantity representations:
Base Quantity
Quantity on ILE (always in base UoM, typically bottles)
Precise counting, unit-level reporting
Case Quantity
Quantity / Qty. per Unit of Measure (from Item or ILE)
Trade-level reporting
Equivalent Cases
Quantity × Item.Unit Volume / 900 (if Volume Unit = cl)
Standardised volume comparison, cellarage pricing
Data Source Architecture Considerations
The Bevica Power BI dataset combines data from both Item Ledger Entries and Value Entries into a single denormalised fact table. It also appends Non-Inventory Transactions (sales invoice lines for non-item charges like delivery fees, admin charges) as pseudo-ILE rows with a Transaction Source = "Non ILE" flag.
Consider adopting a similar pattern:
Join ILE + VE to get quantity from ILE and amounts from VE in one row
Add a
Transaction Sourcecolumn ("ILE" or "Non ILE") to distinguish inventory vs. non-inventory transactionsAdd a
Line Typecolumn ("Item" or "Charge") to distinguish product sales from service linesAdd the
Paid Reserve Entryboolean flagBring in document-level fields from posted sales invoices/shipments (Sales Document Type, Ship-to Code, etc.) for enrichment
This gives you a single fact table that supports all sales, cost, inventory, and margin reporting with simple filters.
Reporting Considerations & Exclusions
11.1 Inventory Valuation
Paid reserves must be EXCLUDED from standard inventory valuation reports.
The company does not own paid reserve stock — the customer does. Including it would overstate the company's inventory.
To report on paid reserve inventory separately:
Sales Margin / Profitability
Exclude paid reserve withdrawals from sales margin analysis.
A withdrawal is not a commercial sale — the customer is receiving their own goods. The "revenue" from a withdrawal sale does not represent trading income.
Broking P&L
Broking revenue/cost should be reported separately from normal trading, as the merchant is acting as intermediary:
Cellarage Revenue
Cellarage is service revenue, not goods revenue. It should be reported from the TVTCE Cellarage Ledger Entry for accurate period and rate detail, or from Sales Invoice Lines where TVT Sales Identifier indicates Cellarage.
Summary: Transaction Classification Matrix
Normal sale
TVTBE Transaction Type = blank, no broking fields
Yes
N/A (reduces stock)
Sale into reserve
TVTBE Transaction Type = 1
Separate — customer deposit
No — customer's stock
Withdrawal
TVTBE Transaction Type = 2
No — customer's own goods
No
Broking sale
TVT Broking Apply Entry No. <> 0
Separate — report as broking fee
No — was customer's stock
Cellarage invoice
TVT Sales Identifier = Cellarage
Separate — service revenue
N/A
Duty transfer
TVTBE Transaction Type = 6
No — reclassification only
No value change
Cancel / Credit
TVTBE Transaction Type = 4
Reversal of original
Reversal
Table References
Bevica Essential Tables
70528
TVT Paid Reserve Information
Master paid reserve record
70529
TVT Paid Reserve Led. Entry
Paid reserve transaction history
70527
TVT Paid Reserve Journal Line
Staging for paid reserve postings
70542
TVT Paid Res. Booking Entry
Quantity bookings against documents/broking
70543
TVT Paid Reserve Summary
Aggregated paid reserve inventory
70552
TVT Broking Status Code
Broking workflow statuses
70553
TVT Broking Worksheet Line
Broking list — reserves available for sale
Bevica Cellarage Tables
72650
TVTCE Cellarage Rate
Tiered pricing per equivalent case
72651
TVTCE Cellarage Rate Group
Customer pricing tier groups
72652
TVTCE Cellarage Ledger Entry
Posted cellarage charges (main audit)
72653
TVTCE Cellarage Worksheet
Pre-invoice staging/calculation
72654
TVTCE Cellarage Sales Line
Links invoice lines to cellarage periods
72655
TVTCE Cellarage Setup
Configuration (GL account, periods, defaults)
Key BC Tables with Extension Fields
Item Ledger Entry
TVT Paid Reserve No., TVT Paid Reserve Customer No., TVT Broking Apply Entry No., TVT Broking Owner Code, TVT Rotation No., TVTHM HMRC Movement Type
Value Entry
TVT Paid Reserve No.
Sales Line / Posted variants
TVTBE Transaction Type, TVT Paid Reserve No., TVT Broking Id, TVT Broking Apply Entry No., TVT Broking Owner Code, TVT Sales Identifier
Purchase Line / Posted variants
TVT Broking Owner Code, TVT Broking Sales Order No., TVT From Broking Sales Line Id
Customer
TVT Contra Vendor No., TVTCE Cellarage Rate Gr. Code
Item
Qty. on Paid Reserve (ext field)
Enum Reference
TVTBE Transaction Type (70506) — Sales Line Level
0
(blank)
Normal sale/delivery
1
Sale into Reserve
Customer buying into paid reserve
2
Withdrawal
Customer withdrawing from reserve
3
Transfer Reserve
Reserve transfer
4
Cancel
Cancelling a sale into reserve
5
Purchase
Purchase reserve
6
Duty Transfer
Duty status change
TVT Paid Reserve Entry Type (70509) — Reserve Ledger Level
0
Stock Into
Adding to reserve
1
Withdrawal Stock
Removing from reserve
2
Transfer Stock
Transfer (location/duty)
3
Cancel
Reversal
4
Split
Case → bottles etc.
5
Purchase
Purchase into reserve
6
Transfer Ownership
Change customer owner
20
Broking Purchase
Broking acquisition
TVT Paid Reserve Split (70505) — Posting Behaviour
0
None