> For the complete documentation index, see [llms.txt](https://kb.bevica.com/llms.txt). Markdown versions of documentation pages are available by appending `.md` to page URLs; this page is available as [Markdown](https://kb.bevica.com/documentation/reporting-and-analytics/reporting_in_bevica_-_overview/reporting-for-fabric-sql.md).

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

{% embed url="<https://learn.microsoft.com/en-gb/dynamics365/business-central/admin-fabric>" %}

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.

{% hint style="success" %}
**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).
{% endhint %}

***

### Extension Landscape

Bevica is modular. Each extension adds specific functionality. All depend on **Bevica Essential** as the base.

<table data-header-hidden="false" data-header-sticky><thead><tr><th width="198">Extension</th><th>Purpose</th><th>Key Tables</th></tr></thead><tbody><tr><td><strong>Bevica Essential</strong></td><td>Core: items, sales, purchasing, paid reserves, broking worksheets</td><td>TVT Paid Reserve Information, TVT Paid Reserve Led. Entry, TVT Broking Worksheet Line</td></tr><tr><td><strong>Bevica Fine Wine</strong></td><td>Posting logic for paid reserve entries during item journal posting</td><td>Extends Item Ledger Entry, Value Entry, Sales Line</td></tr><tr><td><strong>Bevica Broking</strong></td><td>Broking worksheet creation and management</td><td>Uses TVT Broking Worksheet Line (in Essential)</td></tr><tr><td><strong>Bevica Cellarage</strong></td><td>Storage fee calculation and invoicing</td><td>TVTCE Cellarage Ledger Entry, TVTCE Cellarage Worksheet, TVTCE Cellarage Rate</td></tr></tbody></table>

{% hint style="info" %}
**Note on table naming convention:** Extension tables appear in Fabric using AL table names. Fields added by extensions to base BC tables (e.g., `TVT Paid Reserve No.` on `Item Ledger Entry`) appear as additional columns. Extension-specific tables will carry the extension prefix (e.g., `TVT`, `TVTCE`).
{% endhint %}

***

### Key Concepts for Reporting

#### The Dual Ledger Pattern

When a paid reserve transaction posts, records are created in **two parallel ledgers**:

1. **Standard BC Ledger** — Item Ledger Entry + Value Entry (for inventory and costing)
2. **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:

<table data-header-hidden="false" data-header-sticky><thead><tr><th width="218">Field</th><th width="228">Table</th><th>If populated, means...</th></tr></thead><tbody><tr><td><code>TVT Paid Reserve No.</code></td><td>Item Ledger Entry</td><td>This item movement relates to a paid reserve</td></tr><tr><td><code>TVT Paid Reserve No.</code></td><td>Value Entry</td><td>This value/cost entry relates to a paid reserve</td></tr><tr><td><code>TVTBE Transaction Type</code></td><td>Sales Line / Posted Sales Invoice Line</td><td>The line has a Bevica transaction classification</td></tr><tr><td><code>TVT Broking Id</code></td><td>Sales Line / Purchase Line</td><td>This line is part of a broking transaction</td></tr><tr><td><code>TVT Broking Owner Code</code></td><td>Sales Line / Purchase Line</td><td>Identifies the paid reserve owner in broking</td></tr><tr><td><code>TVT Sales Identifier</code></td><td>Sales Header</td><td>Identifies the type of sale (e.g., "Cellarage"). This is not mandatory.</td></tr><tr><td><code>TVT Sales Document Type</code></td><td>Sales Header / Purchase Header</td><td>Identifies a type of document and linked to Sales Identifier. Not mandatory.</td></tr></tbody></table>

{% hint style="info" %}
For detailed enum values for Transaction Type and Paid Reserve Entry Type, see Enum Reference
{% endhint %}

***

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

<table data-header-hidden="false" data-header-sticky><thead><tr><th width="270">Table</th><th>Purpose</th></tr></thead><tbody><tr><td><strong>TVT Paid Reserve Information</strong> (70528)</td><td>Master record — one per reserve. Shows what's held, for whom, how much remains</td></tr><tr><td><strong>TVT Paid Reserve Led. Entry</strong> (70529)</td><td>Transaction history — every movement (in, out, transfer, split, cancel)</td></tr><tr><td><strong>TVT Paid Res. Booking Entry</strong> (70542)</td><td>Quantity tracking — what's booked on open documents or broking worksheets. This enables tracking of non-posted transactions in Bevica.</td></tr><tr><td><strong>TVT Paid Reserve Summary</strong> (70543)</td><td>Aggregated view — by item, location, variant, customer</td></tr></tbody></table>

#### **Extension Fields on Standard BC Tables**

<table data-header-hidden="false" data-header-sticky><thead><tr><th width="185">BC Table</th><th width="249">Extension Field</th><th>Purpose</th></tr></thead><tbody><tr><td><strong>Item Ledger Entry</strong></td><td><code>TVT Paid Reserve No.</code> (70551)</td><td>Links item movements to a specific paid reserve</td></tr><tr><td><strong>Item Ledger Entry</strong></td><td><code>TVT Paid Reserve Customer No.</code> (70550)</td><td>Identifies the customer who owns the reserve</td></tr><tr><td><strong>Value Entry</strong></td><td><code>TVT Paid Reserve No.</code> (70551)</td><td>Links value/cost entries to a paid reserve</td></tr><tr><td><strong>Sales Line</strong> (and posted variants)</td><td><code>TVT Paid Reserve No.</code> (70551)</td><td>Which reserve the sales line relates to</td></tr><tr><td><strong>Sales Line</strong></td><td><code>TVTBE Transaction Type</code> (70550)</td><td>What type of reserve transaction</td></tr><tr><td><strong>Sales Line</strong></td><td><code>TVT Paid Reserve Split</code> (70555)</td><td>How the reserve is split on posting</td></tr></tbody></table>

#### Key Fields on TVT Paid Reserve Information

<table><thead><tr><th width="185">Field</th><th width="190">Type</th><th>Description</th></tr></thead><tbody><tr><td><code>No.</code></td><td>Code[20]</td><td>Unique paid reserve identifier (e.g., "PR-001")</td></tr><tr><td><code>Source Type</code></td><td>Enum</td><td>Customer or Vendor</td></tr><tr><td><code>Source No.</code></td><td>Code[20]</td><td>Customer No. who owns the reserve</td></tr><tr><td><code>Item No.</code></td><td>Code[20]</td><td>The item being held</td></tr><tr><td><code>Quantity</code></td><td>Decimal</td><td>Original quantity placed into reserve</td></tr><tr><td><code>Remaining Quantity</code></td><td>Decimal (FlowField)</td><td>Current balance — calculated from ledger entries</td></tr><tr><td><code>Qty. Booked</code></td><td>Decimal (FlowField)</td><td>Quantity booked on open documents</td></tr><tr><td><code>Qty. on Broking</code></td><td>Decimal (FlowField)</td><td>Quantity on the broking worksheet</td></tr><tr><td><code>Unit Price</code></td><td>Decimal</td><td>Customer's original purchase price (LCY)</td></tr><tr><td><code>Unit Cost</code></td><td>Decimal</td><td>Company's cost of the goods (LCY)</td></tr><tr><td><code>Location Code</code></td><td>(via ledger)</td><td>Where the stock is physically stored</td></tr><tr><td><code>Variant Code</code></td><td>(via ledger)</td><td>Duty status — e.g., Duty Paid / Duty Free</td></tr><tr><td><code>Rotation No.</code></td><td>Code[50]</td><td>Wine vintage/rotation tracking</td></tr><tr><td><code>Posting Date</code></td><td>Date</td><td>Date the reserve was created</td></tr><tr><td><code>Document No.</code></td><td>Code[20]</td><td>Source sales document that created the reserve</td></tr></tbody></table>

#### How to Identify Paid Reserve Stock

In Item Ledger Entry:

```sql
-- All paid reserve item movements
SELECT * FROM [Item Ledger Entry]
WHERE [TVT Paid Reserve No.] <> ''

-- All NON-paid-reserve item movements (company's own stock)
SELECT * FROM [Item Ledger Entry]
WHERE [TVT Paid Reserve No.] = ''
```

In Value Entry:

```sql
-- Value entries linked to paid reserves
SELECT * FROM [Value Entry]
WHERE [TVT Paid Reserve No.] <> ''
```

#### Paid Reserve Lifecycle

```
┌───────────────────────────────────────────────────────────────────────────┐
│ SALE INTO RESERVE (Customer buys wine, held in storage)           	      │
│                                                                           │
│ Sales Order with Transaction Type = "Sale into Reserve"            	      │
│ On Post creates:													  			                        |
|	→ Item Ledger Entry (standard to "sell" the stock)					  		        │	
│   → Item Ledger Entry (with TVT Paid Reserve No. to put into paid reserve)│
│   → Value Entry (with TVT Paid Reserve No.)                         			│
│   → TVT Paid Reserve Information (the reserve master record)        			│
│   → TVT Paid Reserve Led. Entry (Entry Type = "Stock Into")         			│
└──────────────────────────────┬────────────────────────────────────────────┘
                               │
              ┌────────────────┼────────────────┐
              ▼                ▼                ▼
     ┌──────────────┐ ┌──────────────┐ ┌──────────────────┐
     │ WITHDRAWAL   │ │ BROKING      │ │ TRANSFER/SPLIT   │
     │ Customer     │ │ Sell to      │ │ Location move,   │
     │ takes stock  │ │ another      │ │ duty change, or  │
     │ via SO       │ │ customer     │ │ case → bottles   │
     └──────────────┘ └──────────────┘ └──────────────────┘
```

#### Paid Reserve Reporting — Inventory Position

{% hint style="warning" %}
**Important — see** [Paid Reserves in Item Ledger & Value Entries](#paid-reserves-in-item-ledger-and-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.
{% endhint %}

To report on current paid reserve inventory, use the **TVT Paid Reserve Information** table:

```sql
-- Current paid reserve inventory by customer
SELECT
    pri.[Source No.] AS CustomerNo,
    pri.[Item No.],
    pri.[Item Description],
    pri.[Remaining Quantity],
    pri.[Unit Price],
    pri.[Remaining Quantity] * pri.[Unit Price] AS HoldingValue,
    pri.[Location Code],
    pri.[Unit of Measure Code]
FROM [TVT Paid Reserve Information] pri
WHERE pri.[Remaining Quantity] > 0
ORDER BY pri.[Source No.], pri.[Item No.]
```

{% hint style="warning" %}
**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:
{% endhint %}

```sql
-- Calculate remaining quantity from ledger entries
SELECT
    prle.[Paid Reserve No.],
    SUM(prle.[Quantity]) AS RemainingQuantity
FROM [TVT Paid Reserve Led. Entry] prle
WHERE prle.[Open] = 1
GROUP BY prle.[Paid Reserve No.]
```

***

### 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:

<table><thead><tr><th width="214">Field</th><th>Value</th></tr></thead><tbody><tr><td>Entry Type</td><td>Sale</td></tr><tr><td>Quantity</td><td>-6 (negative, stock leaving)</td></tr><tr><td>TVT Paid Reserve No.</td><td><em>(blank)</em></td></tr><tr><td>Document Type</td><td>Sales Shipment</td></tr></tbody></table>

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:

<table><thead><tr><th width="288">Field</th><th>Value</th></tr></thead><tbody><tr><td>Entry Type</td><td>Sale</td></tr><tr><td>Quantity</td><td>+6 (positive, stock back in under PR)</td></tr><tr><td>TVT Paid Reserve No.</td><td>PR-001</td></tr><tr><td>TVT Paid Reserve Customer No.</td><td>CUST001</td></tr></tbody></table>

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:

```
ItemJournalLine."Unit Cost" := PaidReserveLedgerEntry."Unit Price"
```

This means the Value Entry for a paid reserve ILE contains:

| Value Entry Field       | What it contains on a PR entry                                              |
| ----------------------- | --------------------------------------------------------------------------- |
| `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:

| Field        | What it holds                                      |
| ------------ | -------------------------------------------------- |
| `Unit Price` | What the customer paid (their purchase/sale price) |
| `Unit Cost`  | The company’s cost of the goods                    |

{% hint style="info" %}
**For reporting:** If you query Cost Amount on paid-reserve-flagged Value Entries, you are seeing the **customer’s purchase value**, not the company’s cost of goods. This is another reason to always filter paid reserve entries separately — mixing PR “costs” with trading costs would distort margin calculations.
{% endhint %}

#### The Withdrawal Pattern

When a customer withdraws stock from their paid reserve, the posting creates:

**Entry 1: The Sale/Shipment**

<table><thead><tr><th width="314">Field</th><th>Value</th></tr></thead><tbody><tr><td><code>Entry Type</code></td><td>Sale</td></tr><tr><td><code>Quantity</code></td><td>-6 (negative, stock leaving)</td></tr><tr><td><code>TVT Paid Reserve No.</code></td><td>PR-001</td></tr><tr><td><code>TVT Paid Reserve Customer No.</code></td><td>CUST001</td></tr></tbody></table>

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:

```sql
-- Derive the Paid Reserve Entry flag
CASE
    WHEN [TVT Paid Reserve No.] <> '' THEN 1  -- TRUE: Paid Reserve Entry
    ELSE 0  -- FALSE: Normal company transaction
END AS [Paid Reserve Entry]
```

Then apply this filter consistently:

<table><thead><tr><th width="242">Measure</th><th>Filter</th></tr></thead><tbody><tr><td>Sales Quantity</td><td><code>Paid Reserve Entry = FALSE</code></td></tr><tr><td>Sales Amount</td><td>No filter (includes all revenue) OR <code>Paid Reserve Entry = FALSE</code> for trading-only view</td></tr><tr><td>Cost of Sales</td><td><code>Paid Reserve Entry = FALSE</code></td></tr><tr><td>Sales Margin</td><td><code>Paid Reserve Entry = FALSE</code></td></tr><tr><td>Inventory Quantity</td><td><code>Paid Reserve Entry = FALSE</code></td></tr><tr><td>Inventory Valuation</td><td><code>Paid Reserve Entry = FALSE</code></td></tr><tr><td>PR Inventory Quantity</td><td><code>Paid Reserve Entry = TRUE</code></td></tr><tr><td>PR Sales Quantity</td><td><code>Paid Reserve Entry = TRUE</code> AND <code>Entry Type = Sale</code></td></tr></tbody></table>

{% hint style="info" %}
**Note on Sales Amount:** The existing Bevica Power BI dataset does NOT filter Sales Amount by Paid Reserve Entry — it includes all sales revenue including withdrawal revenue. However, it DOES filter Cost of Sales, Quantity, and Margin by `Paid Reserve Entry = FALSE`. This means Sales Amount shows total revenue, while margin is calculated only on trading sales. Consider your reporting requirements carefully.
{% endhint %}

#### Summary: What Each ILE Entry Type + PR Flag Combination Means

<table><thead><tr><th width="143">Entry Type</th><th width="167">PR Entry?</th><th width="131">Quantity</th><th>What it is</th></tr></thead><tbody><tr><td>Sale</td><td>FALSE</td><td>Negative</td><td>Normal commercial sale — goods shipped to customer</td></tr><tr><td>Sale</td><td>TRUE (+ve qty)</td><td>Positive</td><td>Stock received INTO paid reserve (the "other half" of a sale into reserve)</td></tr><tr><td>Sale</td><td>TRUE (-ve qty)</td><td>Negative</td><td>Withdrawal FROM paid reserve — customer taking delivery</td></tr><tr><td>Purchase</td><td>FALSE</td><td>Positive</td><td>Normal purchase receipt</td></tr><tr><td>Purchase</td><td>TRUE</td><td>Positive</td><td>Purchase into paid reserve (e.g., from broker or import)</td></tr><tr><td>Transfer</td><td>FALSE</td><td>+/-</td><td>Standard stock transfer between locations</td></tr><tr><td>Transfer</td><td>TRUE</td><td>+/-</td><td>Paid reserve transfer (location move, duty transfer)</td></tr></tbody></table>

***

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

```sql
-- Look at variant codes on paid reserve item movements
SELECT
    ile.[TVT Paid Reserve No.],
    ile.[Variant Code],  -- Indicates duty status
    ile.[Location Code],
    ile.[Quantity]
FROM [Item Ledger Entry] ile
WHERE ile.[TVT Paid Reserve No.] <> ''
```

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

{% hint style="info" %}
The only revenue on a withdrawal invoice comes from the duty and VAT lines that Bevica may add automatically (see Section 7.4). These are G/L Account type lines, not Item lines.
{% endhint %}

#### How to Identify Withdrawals

**On Posted Sales Invoice Lines:**

```sql
SELECT *
FROM [Sales Invoice Line]
WHERE [TVTBE Transaction Type] = 2  -- Withdrawal
```

**On Item Ledger Entries:**

```sql
-- Withdrawals create negative item ledger entries with a paid reserve no.
SELECT ile.*
FROM [Item Ledger Entry] ile
WHERE ile.[TVT Paid Reserve No.] <> ''
  AND ile.[Entry Type] = 1  -- Sale
  AND ile.[Quantity] < 0
```

**On Paid Reserve Ledger:**

```sql
SELECT *
FROM [TVT Paid Reserve Led. Entry]
WHERE [Entry Type] = 1  -- "Withdrawal Stock"
```

#### Withdrawal Impact on Other Reports

<table><thead><tr><th width="213">Report</th><th>Impact</th></tr></thead><tbody><tr><td><strong>Inventory Valuation</strong></td><td>Withdrawal reduces paid reserve stock. Since paid reserves are typically <strong>excluded</strong> from standard inventory valuation, the withdrawal itself should also be excluded.</td></tr><tr><td><strong>Sales Margin</strong></td><td>A withdrawal sale is <strong>not a normal commercial sale</strong>. The customer is receiving their own stock. Including withdrawals in sales margin analysis would be misleading.</td></tr><tr><td><strong>Revenue Reporting</strong></td><td>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.</td></tr></tbody></table>

#### 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:

<table><thead><tr><th width="283">Condition</th><th>Where evaluated</th></tr></thead><tbody><tr><td><code>IsDutyFreeVariantCode(ILE."Variant Code")</code></td><td>The item ledger entry being withdrawn was held as Duty Free stock</td></tr><tr><td><code>IsDutyPaidVariantCode(SalesHeader."TVT Duty Status")</code></td><td>The sales order's Duty Status equals Duty Paid (i.e. customer wants delivered duty paid)</td></tr></tbody></table>

If jurisdictions are enabled, Bevica applies jurisdiction-specific duty rates using the ILE's storage jurisdiction and the delivery jurisdiction.

**Line 1 — Duty Charge**

<table><thead><tr><th width="233">Field</th><th>Value</th></tr></thead><tbody><tr><td><strong>Type</strong></td><td>G/L Account</td></tr><tr><td><strong>No.</strong></td><td><code>BevicaSetup."PR Withdrawal Duty Acc."</code></td></tr><tr><td><strong>Quantity</strong></td><td>Same as the withdrawal item line</td></tr><tr><td><strong>Unit Price</strong></td><td>Per-unit duty amount (calculated by the duty engine, currency-converted if applicable)</td></tr><tr><td><strong>VAT</strong></td><td>Standard VAT applied via posting groups (customer pays VAT on duty)</td></tr><tr><td><strong>TVT Paid Reserve No.</strong></td><td>Copied from the item line</td></tr><tr><td><strong>TVT Paid Reserve ILE No.</strong></td><td>Copied from the item line</td></tr><tr><td><strong>TVT Parent Line No.</strong></td><td>Line No. of the withdrawal item line</td></tr></tbody></table>

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

| Field                        | Value                                                                                                |
| ---------------------------- | ---------------------------------------------------------------------------------------------------- |
| **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                                                                 |

{% hint style="info" %}
`PaidReserveInfo."Unit Price"` is the customer's **original purchase price** recorded on the Paid Reserve Card — not a market value or current price.
{% endhint %}

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

```sql
-- All lines for a given paid reserve's withdrawals (Posted Sales Invoice Lines)
SELECT
    psi.[TVT Paid Reserve No.],
    psi.[No.],
    psi.[Type],
    psi.[Description],
    psi.[TVTBE Transaction Type],
    psi.[Quantity],
    psi.[Unit Price],
    psi.[Amount],
    psi.[TVT Parent Line No.]
FROM [Sales Invoice Line] psi
WHERE psi.[TVT Paid Reserve No.] = '<PR_NO>'
  AND psi.[TVTBE Transaction Type] = 2  -- Withdrawal
ORDER BY psi.[Document No.], psi.[TVT Parent Line No.], psi.[Line No.]
```

***

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

| Table                                   | Purpose                                                    |
| --------------------------------------- | ---------------------------------------------------------- |
| **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**

<table><thead><tr><th width="193">Table</th><th width="260">Field</th><th>Purpose</th></tr></thead><tbody><tr><td>Sales Line</td><td><code>TVT Broking Id</code> (70556)</td><td>Links to Broking Worksheet Line SystemId</td></tr><tr><td>Sales Line</td><td><code>TVT Broking Apply Entry No.</code> (70557)</td><td>Links to the original ILE of the paid reserve</td></tr><tr><td>Sales Line</td><td><code>TVT Broking Paid Res. No.</code> (70558)</td><td>Which paid reserve is being sold</td></tr><tr><td>Sales Line</td><td><code>TVT Broking Owner Code</code> (70559)</td><td>Customer who owns the paid reserve</td></tr><tr><td>Purchase Header</td><td><code>TVT Broking Sales Order No.</code> (70581)</td><td>Links the PO back to the triggering sales order</td></tr><tr><td>Purchase Header</td><td><code>TVT Broking Owner Code</code> (70559)</td><td>Paid reserve owner</td></tr><tr><td>Purchase Line</td><td><code>TVT From Broking Sales Line Id</code> (70554)</td><td>Links PO line to SO line</td></tr></tbody></table>

#### How to Identify Broking Transactions

**In Sales Lines / Posted Sales Invoice Lines:**

```sql
-- Broking sales lines
SELECT *
FROM [Sales Invoice Line]
WHERE [TVT Broking Apply Entry No.] <> 0
```

**In Purchase Lines / Posted Purchase Invoice Lines:**

```sql
-- Broking purchase lines
SELECT *
FROM [Purch. Inv. Line]
WHERE [TVT Broking Owner Code] <> ''
```

**In Item Ledger Entries:**

```sql
-- Item movements from broking
SELECT *
FROM [Item Ledger Entry]
WHERE [TVT Broking Apply Entry No.] <> 0
```

**In Paid Reserve Ledger:**

```sql
SELECT *
FROM [TVT Paid Reserve Led. Entry]
WHERE [Entry Type] = 20  -- "Broking Purchase"
```

#### Broking Financial Flow

A broking transaction creates **two linked documents**:

1. **Sales Order** to the new buyer (at the selling price)
2. **Purchase Order** from the original reserve owner's "contra vendor" (at the buying price)

```
Original Owner (Customer A)                New Buyer (Customer B)
        │                                          │
        │  ◄── Purchase Order ──┐                  │
        │      (Buying Price)   │                  │
        │                       │                  │
        │               ┌──────────────┐           │
        │               │   MERCHANT   │           │
        │               │  Broking Fee │           │
        │               │  = Sell-Buy  │           │
        │               └──────────────┘           │
        │                       │                  │
        │                       └── Sales Order ──►│
        │                           (Selling Price) │
```

#### Key Fields on TVT Broking Worksheet Line

<table><thead><tr><th width="278">Field</th><th>Description</th></tr></thead><tbody><tr><td><code>Source No.</code></td><td>Customer who owns the paid reserve</td></tr><tr><td><code>Item No.</code></td><td>The item being brokered</td></tr><tr><td><code>Paid Reserve No.</code></td><td>FK to TVT Paid Reserve Information</td></tr><tr><td><code>Quantity</code></td><td>Total quantity available for broking</td></tr><tr><td><code>Remaining Quantity</code></td><td>Quantity not yet sold/posted</td></tr><tr><td><code>Posted Quantity</code></td><td>Quantity already posted via sales/purchase</td></tr><tr><td><code>Confirmed</code></td><td>TRUE = confirmed and available for sale</td></tr><tr><td><code>Closed</code></td><td>TRUE = fully posted, no remaining quantity</td></tr><tr><td><code>Buying Unit Price (LCY)</code></td><td>Price paid to original owner</td></tr><tr><td><code>Selling Unit Price (LCY)</code></td><td>Price charged to new buyer</td></tr><tr><td><code>Broking Fee %</code></td><td>Fee percentage</td></tr><tr><td><code>Min. Broking Fee Amt. (LCY)</code></td><td>Minimum fee floor</td></tr><tr><td><code>Purch. to Stock</code></td><td>TRUE = purchased to company stock (not sold to customer)</td></tr></tbody></table>

#### Broking Profitability

```sql
-- Broking profitability from worksheet
SELECT
    bwl.[Source No.] AS OriginalOwner,
    bwl.[Item No.],
    SUM(bwl.[Posted Quantity]) AS QtySold,
    SUM(bwl.[Selling Unit Price (LCY)] * bwl.[Posted Quantity]) AS GrossSales,
    SUM(bwl.[Buying Unit Price (LCY)] * bwl.[Posted Quantity]) AS GrossCost,
    SUM((bwl.[Selling Unit Price (LCY)] - bwl.[Buying Unit Price (LCY)]) * bwl.[Posted Quantity]) AS BrokingFee,
    AVG(bwl.[Broking Fee %]) AS AvgFeePercent
FROM [TVT Broking Worksheet Line] bwl
WHERE bwl.[Closed] = 1
GROUP BY bwl.[Source No.], bwl.[Item No.]
```

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

```sql
-- Customer to Contra Vendor mapping
SELECT
    c.[No.] AS CustomerNo,
    c.[Name],
    c.[TVT Contra Vendor No.] AS ContraVendorNo
FROM [Customer] c
WHERE c.[TVT Contra Vendor No.] <> ''
```

***

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

<table><thead><tr><th width="328">Table</th><th>Purpose</th></tr></thead><tbody><tr><td><strong>TVTCE Cellarage Ledger Entry</strong> (72652)</td><td>Posted cellarage charges — the main reporting table</td></tr><tr><td><strong>TVTCE Cellarage Worksheet</strong> (72653)</td><td>Staging table — calculated charges before invoicing</td></tr><tr><td><strong>TVTCE Cellarage Rate</strong> (72650)</td><td>Tiered pricing — rate per equivalent case at volume thresholds</td></tr><tr><td><strong>TVTCE Cellarage Rate Group</strong> (72651)</td><td>Groups customers into pricing tiers</td></tr><tr><td><strong>TVTCE Cellarage Setup</strong> (72655)</td><td>Configuration — GL account, default rate group, invoicing period</td></tr><tr><td><strong>TVTCE Cellarage Sales Line</strong> (72654)</td><td>Links sales invoice lines to cellarage period dates</td></tr></tbody></table>

**Extension Fields**

<table><thead><tr><th width="198">Table</th><th width="202">Field</th><th>Purpose</th></tr></thead><tbody><tr><td><strong>TVT Paid Reserve Information</strong></td><td><code>TVTCE Last Cellarage End Date</code></td><td>Prevents double-invoicing — last period invoiced</td></tr><tr><td><strong>TVT Paid Reserve Information</strong></td><td><code>TVTCE Cellarage Charge Type</code></td><td>Default charge behaviour (Blank/Exempt/Zero Value)</td></tr><tr><td><strong>TVT Paid Reserve Information</strong></td><td><code>TVTCE Cellarage Amount (LCY)</code></td><td>FlowField — sum of all cellarage ledger entries</td></tr><tr><td><strong>Customer</strong></td><td><code>TVTCE Cellarage Rate Gr. Code</code></td><td>Customer's pricing tier</td></tr></tbody></table>

#### 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:

```
Eq. Cases = (Item."Unit Volume" × Quantity) ÷ Volume Unit Divisor
```

The divisor depends on the **Volume Unit** configured in Bevica Setup:

<table><thead><tr><th width="185">Volume Unit</th><th width="157">Divisor</th><th>Example</th></tr></thead><tbody><tr><td>ml (millilitres)</td><td>9,000</td><td>750ml bottle × 12 = 9,000ml = 1 eq. case</td></tr><tr><td>cl (centilitres)</td><td>900</td><td>75cl bottle × 12 = 900cl = 1 eq. case</td></tr><tr><td>dl (decilitres)</td><td>90</td><td>7.5dl bottle × 12 = 90dl = 1 eq. case</td></tr><tr><td>l (litres)</td><td>9</td><td>0.75l bottle × 12 = 9l = 1 eq. case</td></tr></tbody></table>

**In SQL**, if the Item table stores `Unit Volume` in centilitres (the most common setup):

```sql
-- Calculate equivalent cases (assuming cl volume unit)
[Unit Volume] * [Quantity] / 900.0 AS [Eq Cases]
```

{% hint style="warning" %}
**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.
{% endhint %}

**Tiered Pricing**

Rates are volume-based. The more a customer stores, the lower the per-case rate:

<table><thead><tr><th width="173">Rate Group</th><th width="195">Min Qty (Eq. Cases)</th><th>Rate per Eq. Case (LCY)</th></tr></thead><tbody><tr><td>STANDARD</td><td>0</td><td>£0.50</td></tr><tr><td>STANDARD</td><td>100</td><td>£0.45</td></tr><tr><td>STANDARD</td><td>500</td><td>£0.40</td></tr></tbody></table>

The system finds the highest threshold that the customer's total volume meets.

**Charge Types**

<table><thead><tr><th width="115">Value</th><th width="167">Name</th><th>Behaviour</th></tr></thead><tbody><tr><td>0</td><td><em>(blank)</em></td><td>Normal — charge calculated and invoiced</td></tr><tr><td>1</td><td>Exempt</td><td>Skipped entirely — no invoice line created</td></tr><tr><td>2</td><td>Zero Value</td><td>Invoice line created at £0 — for tracking/visibility</td></tr></tbody></table>

#### Cellarage Invoicing Flow

```
1. SUGGEST CELLARAGE (Report 72650)
   → Reads paid reserve positions at a given date
   → Creates worksheet lines per Customer × Paid Reserve
   → Respects "Last Cellarage End Date" to prevent duplicates

2. CALCULATE RATES (Manual action)
   → Applies tiered rates from Cellarage Rate table
   → Invoice Amount (LCY) = Rate per Unit × Eq. Quantity
   → Exempt and Zero Value charge types set to £0

3. CREATE SALES DOCUMENTS (Report 72651)
   → Creates/updates Sales Invoices (or Credit Memos)
   → Sales lines are G/L Account type (not item lines)
   → TVT Sales Identifier = "Cellarage" on the sales header

4. POST
   → Standard BC posting
   → Creates GL Entry, Customer Ledger Entry
   → Creates TVTCE Cellarage Ledger Entry (detail audit)
   → Updates Paid Reserve "Last Cellarage End Date"
   → Deletes worksheet line (cleanup)
```

#### Identifying Cellarage in Documents

**Sales Invoices:**

```sql
-- Posted cellarage invoices
SELECT *
FROM [Sales Invoice Header]
WHERE [TVT Sales Identifier] = 72650  -- Cellarage enum value
```

**Cellarage Ledger Detail:**

```sql
-- All cellarage charges by customer and period
SELECT
    cle.[Customer No.],
    cle.[Customer Name],
    cle.[Paid Reserve No.],
    cle.[Item No.],
    cle.[Period Start Date],
    cle.[Period End Date],
    cle.[Cell. Worksheet Eq. Quantity] AS EquivalentCases,
    cle.[Cellarage Rate per Unit],
    cle.[Amount (LCY)],
    cle.[Document No.] AS PostedInvoiceNo,
    cle.[TVTCE Cellarage Charge Type]
FROM [TVTCE Cellarage Ledger Entry] cle
ORDER BY cle.[Customer No.], cle.[Period Start Date]
```

#### Cellarage Revenue Reporting

```sql
-- Cellarage revenue by customer (YTD)
SELECT
    cle.[Customer No.],
    cle.[Customer Name],
    SUM(cle.[Amount (LCY)]) AS TotalCellarage,
    SUM(cle.[Cell. Worksheet Eq. Quantity]) AS TotalEqCases,
    COUNT(DISTINCT cle.[Paid Reserve No.]) AS NoOfReserves
FROM [TVTCE Cellarage Ledger Entry] cle
WHERE cle.[Posting Date] >= '2026-01-01'
  AND cle.[TVTCE Cellarage Charge Type] = 0  -- Normal charges only
GROUP BY cle.[Customer No.], cle.[Customer Name]
```

***

### 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:

```sql
CASE WHEN ile.[TVT Paid Reserve No.] <> '' THEN 1 ELSE 0 END AS [Paid Reserve Entry]
```

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.

```sql
-- Sales Quantity (Base) — excludes paid reserves
SELECT
    SUM(ile.[Quantity] * -1) AS [Sales Quantity Base]  -- multiply by -1 as sales are negative
FROM [Item Ledger Entry] ile
WHERE ile.[Entry Type] = 1  -- Sale
  AND ile.[TVT Paid Reserve No.] = ''  -- Exclude paid reserve entries
```

#### Sales Amount

Sales Amount can be reported **with or without** paid reserve entries, depending on purpose:

```sql
-- Sales Amount (Actual) — ALL sales revenue including withdrawals
SELECT SUM(ve.[Sales Amount Actual])
FROM [Value Entry] ve
WHERE ve.[Item Ledger Entry Type] = 1  -- Sale

-- Sales Amount (Actual) — TRADING revenue only (excludes withdrawal revenue)
SELECT SUM(ve.[Sales Amount Actual])
FROM [Value Entry] ve
WHERE ve.[Item Ledger Entry Type] = 1  -- Sale
  AND ve.[TVT Paid Reserve No.] = ''  -- Exclude paid reserve entries
```

{% hint style="info" %}
**Design choice**: The existing Bevica PBI dataset includes ALL sales revenue in its Sales Amount measure (no PR filter). This gives a complete revenue picture. Withdrawal revenue can then be isolated separately for analysis.
{% endhint %}

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

```sql
-- Cost of Sales (Actual)
SELECT SUM(ve.[Cost Amount Actual])
FROM [Value Entry] ve
WHERE ve.[Item Ledger Entry Type] = 1  -- Sale
  AND ve.[TVT Paid Reserve No.] = ''  -- Exclude paid reserve entries
```

#### Sales Margin

**Sales Margin should be calculated on non-paid-reserve items only:**

```sql
-- Margin = Sales Amount + Cost of Sales (cost is negative)
SELECT
    SUM(ve.[Sales Amount Actual]) + SUM(ve.[Cost Amount Actual]) AS [Sales Margin]
FROM [Value Entry] ve
WHERE ve.[Item Ledger Entry Type] = 1  -- Sale
  AND ve.[TVT Paid Reserve No.] = ''  -- Exclude paid reserve entries
  AND ve.[Item No.] <> ''  -- Item lines only (exclude G/L account charges etc.)
```

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

```sql
-- Company-owned inventory
SELECT
    ile.[Item No.],
    SUM(ile.[Quantity]) AS [Inventory Qty Base],
    SUM(ve.[Cost Amount Actual]) AS [Inventory Cost]
FROM [Item Ledger Entry] ile
JOIN [Value Entry] ve ON ve.[Item Ledger Entry No.] = ile.[Entry No.]
WHERE ile.[TVT Paid Reserve No.] = ''  -- Company stock only
GROUP BY ile.[Item No.]

-- Paid reserve inventory (customer-owned)
SELECT
    ile.[TVT Paid Reserve Customer No.] AS [Customer No.],
    ile.[Item No.],
    SUM(ile.[Quantity]) AS [PR Inventory Qty Base]
FROM [Item Ledger Entry] ile
WHERE ile.[TVT Paid Reserve No.] <> ''  -- PR stock only
GROUP BY ile.[TVT Paid Reserve Customer No.], ile.[Item No.]
```

#### Inventory Ageing & Turns

When calculating days in stock, turns per year, or oldest stock dates, **always exclude paid reserve entries**:

```sql
-- Average days in stock for open entries (company stock only)
SELECT
    ile.[Item No.],
    AVG(DATEDIFF(day, ile.[Posting Date], GETDATE())) AS [Avg Days In Stock]
FROM [Item Ledger Entry] ile
WHERE ile.[Open] = 1
  AND ile.[TVT Paid Reserve No.] = ''  -- Company stock only
  AND ile.[Quantity] > 0  -- Positive (inbound) entries only
GROUP BY ile.[Item No.]
```

#### Quantity Conversions

Bevica uses three quantity representations:

| Quantity Type        | How to derive                                             | Use for                                           |
| -------------------- | --------------------------------------------------------- | ------------------------------------------------- |
| **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 |

{% hint style="info" %}
**Note**: `Qty. per Unit of Measure` is the conversion factor between the base UoM and the transaction UoM. For a case of 12 bottles, this is 12. For a single bottle, this is 1.
{% endhint %}

#### 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:

1. **Join ILE + VE** to get quantity from ILE and amounts from VE in one row
2. **Add a `Transaction Source` column** ("ILE" or "Non ILE") to distinguish inventory vs. non-inventory transactions
3. **Add a `Line Type` column** ("Item" or "Charge") to distinguish product sales from service lines
4. **Add the `Paid Reserve Entry` boolean flag**
5. **Bring 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.

```sql
-- Company-owned inventory (exclude paid reserves)
SELECT
    ve.[Item No.],
    SUM(ve.[Cost Amount (Actual)]) AS InventoryValue
FROM [Value Entry] ve
WHERE ve.[TVT Paid Reserve No.] = ''  -- EXCLUDE paid reserves
GROUP BY ve.[Item No.]
```

To report on paid reserve inventory **separately**:

```sql
-- Customer-owned inventory (paid reserves only)
SELECT
    ve.[TVT Paid Reserve No.],
    ile.[TVT Paid Reserve Customer No.] AS CustomerNo,
    ve.[Item No.],
    SUM(ve.[Cost Amount (Actual)]) AS ReserveInventoryValue
FROM [Value Entry] ve
JOIN [Item Ledger Entry] ile ON ve.[Item Ledger Entry No.] = ile.[Entry No.]
WHERE ve.[TVT Paid Reserve No.] <> ''  -- ONLY paid reserves
GROUP BY ve.[TVT Paid Reserve No.], ile.[TVT Paid Reserve Customer No.], ve.[Item No.]
```

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

```sql
-- True commercial sales (excluding withdrawals and sale-into-reserve)
SELECT
    sil.[Sell-to Customer No.],
    sil.[No.] AS ItemNo,
    SUM(sil.[Amount]) AS Revenue,
    SUM(sil.[Amount] - sil.[Unit Cost (LCY)] * sil.[Quantity]) AS Margin
FROM [Sales Invoice Line] sil
WHERE sil.[TVTBE Transaction Type] IN (0)  -- Blank = normal delivery
  AND sil.[TVT Broking Apply Entry No.] = 0  -- Not broking (handle separately)
  AND sil.[Type] = 2  -- Item type lines only
GROUP BY sil.[Sell-to Customer No.], sil.[No.]
```

#### Broking P\&L

Broking revenue/cost should be reported **separately** from normal trading, as the merchant is acting as intermediary:

```sql
-- Broking margin from posted documents
SELECT
    sil.[TVT Broking Owner Code] AS OriginalOwner,
    sil.[Sell-to Customer No.] AS Buyer,
    sil.[No.] AS ItemNo,
    SUM(sil.[Amount]) AS SellingRevenue,
    SUM(pil.[Direct Unit Cost] * pil.[Quantity]) AS BuyingCost,
    SUM(sil.[Amount]) - SUM(pil.[Direct Unit Cost] * pil.[Quantity]) AS BrokingMargin
FROM [Sales Invoice Line] sil
JOIN [Purch. Inv. Line] pil
    ON pil.[TVT From Broking Sales Line Id] = sil.[SystemId]
WHERE sil.[TVT Broking Apply Entry No.] <> 0
GROUP BY sil.[TVT Broking Owner Code], sil.[Sell-to Customer No.], sil.[No.]
```

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

| Scenario              | How to identify                                     | Include in trading revenue?          | Include in inventory valuation? |
| --------------------- | --------------------------------------------------- | ------------------------------------ | ------------------------------- |
| **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

<table><thead><tr><th width="132">Table ID</th><th>Name</th><th>Description</th></tr></thead><tbody><tr><td>70528</td><td>TVT Paid Reserve Information</td><td>Master paid reserve record</td></tr><tr><td>70529</td><td>TVT Paid Reserve Led. Entry</td><td>Paid reserve transaction history</td></tr><tr><td>70527</td><td>TVT Paid Reserve Journal Line</td><td>Staging for paid reserve postings</td></tr><tr><td>70542</td><td>TVT Paid Res. Booking Entry</td><td>Quantity bookings against documents/broking</td></tr><tr><td>70543</td><td>TVT Paid Reserve Summary</td><td>Aggregated paid reserve inventory</td></tr><tr><td>70552</td><td>TVT Broking Status Code</td><td>Broking workflow statuses</td></tr><tr><td>70553</td><td>TVT Broking Worksheet Line</td><td>Broking list — reserves available for sale</td></tr></tbody></table>

#### Bevica Cellarage Tables

<table><thead><tr><th width="130">Table ID</th><th>Name</th><th>Description</th></tr></thead><tbody><tr><td>72650</td><td>TVTCE Cellarage Rate</td><td>Tiered pricing per equivalent case</td></tr><tr><td>72651</td><td>TVTCE Cellarage Rate Group</td><td>Customer pricing tier groups</td></tr><tr><td>72652</td><td>TVTCE Cellarage Ledger Entry</td><td>Posted cellarage charges (main audit)</td></tr><tr><td>72653</td><td>TVTCE Cellarage Worksheet</td><td>Pre-invoice staging/calculation</td></tr><tr><td>72654</td><td>TVTCE Cellarage Sales Line</td><td>Links invoice lines to cellarage periods</td></tr><tr><td>72655</td><td>TVTCE Cellarage Setup</td><td>Configuration (GL account, periods, defaults)</td></tr></tbody></table>

#### Key BC Tables with Extension Fields

<table><thead><tr><th width="290">Table</th><th>Key Extension Fields</th></tr></thead><tbody><tr><td>Item Ledger Entry</td><td>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</td></tr><tr><td>Value Entry</td><td>TVT Paid Reserve No.</td></tr><tr><td>Sales Line / Posted variants</td><td>TVTBE Transaction Type, TVT Paid Reserve No., TVT Broking Id, TVT Broking Apply Entry No., TVT Broking Owner Code, TVT Sales Identifier</td></tr><tr><td>Purchase Line / Posted variants</td><td>TVT Broking Owner Code, TVT Broking Sales Order No., TVT From Broking Sales Line Id</td></tr><tr><td>Customer</td><td>TVT Contra Vendor No., TVTCE Cellarage Rate Gr. Code</td></tr><tr><td>Item</td><td>Qty. on Paid Reserve (ext field)</td></tr></tbody></table>

***

### Enum Reference

#### TVTBE Transaction Type (70506) — Sales Line Level

<table><thead><tr><th width="119">Value</th><th width="241">Name</th><th>Used for</th></tr></thead><tbody><tr><td>0</td><td><em>(blank)</em></td><td>Normal sale/delivery</td></tr><tr><td>1</td><td>Sale into Reserve</td><td>Customer buying into paid reserve</td></tr><tr><td>2</td><td>Withdrawal</td><td>Customer withdrawing from reserve</td></tr><tr><td>3</td><td>Transfer Reserve</td><td>Reserve transfer</td></tr><tr><td>4</td><td>Cancel</td><td>Cancelling a sale into reserve</td></tr><tr><td>5</td><td>Purchase</td><td>Purchase reserve</td></tr><tr><td>6</td><td>Duty Transfer</td><td>Duty status change</td></tr></tbody></table>

#### TVT Paid Reserve Entry Type (70509) — Reserve Ledger Level

<table><thead><tr><th width="134">Value</th><th width="213">Name</th><th>Used for</th></tr></thead><tbody><tr><td>0</td><td>Stock Into</td><td>Adding to reserve</td></tr><tr><td>1</td><td>Withdrawal Stock</td><td>Removing from reserve</td></tr><tr><td>2</td><td>Transfer Stock</td><td>Transfer (location/duty)</td></tr><tr><td>3</td><td>Cancel</td><td>Reversal</td></tr><tr><td>4</td><td>Split</td><td>Case → bottles etc.</td></tr><tr><td>5</td><td>Purchase</td><td>Purchase into reserve</td></tr><tr><td>6</td><td>Transfer Ownership</td><td>Change customer owner</td></tr><tr><td>20</td><td>Broking Purchase</td><td>Broking acquisition</td></tr></tbody></table>

#### TVT Paid Reserve Split (70505) — Posting Behaviour

<table><thead><tr><th width="136">Value</th><th width="231">Name</th><th>Meaning</th></tr></thead><tbody><tr><td>0</td><td>None</td><td>One reserve record per sales line</td></tr><tr><td>1</td><td>Base Unit of Measure</td><td>One record per base unit (e.g., per bottle)</td></tr><tr><td>2</td><td>Unit of Measure</td><td>One record per transaction UoM</td></tr><tr><td>3</td><td>Convert to Base</td><td>Convert and create in base UoM</td></tr></tbody></table>

#### TVTCE Cellarage Charge Type (72651) — Invoicing Behaviour

<table><thead><tr><th width="127">Value</th><th width="178">Name</th><th>Meaning</th></tr></thead><tbody><tr><td>0</td><td><em>(blank)</em></td><td>Normal — calculated and invoiced</td></tr><tr><td>1</td><td>Exempt</td><td>Skipped entirely</td></tr><tr><td>2</td><td>Zero Value</td><td>Invoiced at £0 for tracking</td></tr></tbody></table>

#### TVT Sales Identifier — Document Classification

<table><thead><tr><th width="131">Value</th><th width="170">Name</th><th>Meaning</th></tr></thead><tbody><tr><td>72650</td><td>Cellarage</td><td>Sales document created by cellarage invoicing</td></tr></tbody></table>


---

# Agent Instructions
This documentation is published with GitBook. GitBook is the documentation platform designed so that both humans and AI agents can read, navigate, and reason over technical content effectively. Learn more at gitbook.com.

## Querying This Documentation
If you need additional information that is not directly available in this page, you can query the documentation dynamically by asking a question.

Perform an HTTP GET request on the current page URL with the `ask` query parameter, and the optional `goal` query parameter:

```
GET https://kb.bevica.com/documentation/reporting-and-analytics/reporting_in_bevica_-_overview/reporting-for-fabric-sql.md?ask=<question>&goal=<endgoal>
```

`ask` is the immediate question: it should be specific, self-contained, and written in natural language.
`goal` is optional and describes the broader end goal you are ultimately trying to accomplish on behalf of the user. GitBook uses it to tailor the answer towards what is most useful for that goal.

The response will contain a direct answer to the question and relevant excerpts and sources from the documentation.

Use this mechanism when the answer is not explicitly present in the current page, you need clarification or additional context, or you want to retrieve related documentation sections.