# GB5 Stock Valuation & Ledger Engine — Stakeholder Reference Guide

**Module:** Materials Management (MM)  
**System:** GoodBooks GB5 (.NET 9 Microservices ERP)  
**Version:** GB5 Phase 1  
**Date:** May 2026  
**Status:** Implementation Complete — Integration & Testing Phase

---

## Table of Contents

1. [System Overview](#1-system-overview)
2. [For Subject Matter Experts (SME)](#2-for-subject-matter-experts-sme)
3. [For End Users](#3-for-end-users)
4. [For Implementation Team](#4-for-implementation-team)
5. [For System Administrators](#5-for-system-administrators)
6. [For the Development Team](#6-for-the-development-team)
7. [For Quality Control (QC)](#7-for-quality-control-qc)
8. [For Auditors](#8-for-auditors)
9. [Glossary](#9-glossary)

---

## 1. System Overview

### What This System Does

The GB5 Stock Valuation and Ledger Engine is the financial backbone of inventory management. It answers three core questions at any point in time:

1. **How much stock do we have?** — Quantities by item, SKU, store, and quality category (good, rejected, rework, WIP).
2. **What is that stock worth?** — Value using the organisation's chosen costing method (Weighted Average, FIFO, LIFO, or Standard).
3. **How does stock value flow to the general ledger?** — Automatic GL posting for stocked items, capital items, and service/charge items on procurement documents.

### What Changed from Legacy (Why This Matters)

| Problem in Legacy System | Resolution in GB5 |
|--------------------------|-------------------|
| NHibernate ORM causing bulk update failures (`"could not execute native bulk manipulation query"`) | Pure Dapper + raw parameterized SQL |
| SQL injection risk via string-substituted parameters (`:ouid`, `:fromdate`) | Dapper `@Param` binding throughout — no string concatenation |
| Custom `AsyncFunc` delegate swallowed exceptions silently | Full `async/await` with Dapr pub/sub and explicit error recording |
| PROCFIFOLOT stored procedure (temp tables + cursors) | CTE window-function allocation — simpler, faster, no cursor overhead |
| Erection charges / service items on GRN were never GL-posted (silent gap) | `GET_NON_STOCK_CHARGE_ITEMS` picks up non-stocked TMMDETAIL lines and posts them |
| Valuation batch "delete CostAnalysis" was commented out — memory leak | `TSTOCKVALUATIONRUN` tracks every run; cleanup is deterministic |
| No FIFO cost tracking for non-lot items | New `TCOSTLAYER` + `TCOSTLAYERDETAIL` tables |
| Backdated entries silently left costs stale | Dapr background recosting job fires automatically |
| Period recosting required full history re-read | Certified `TSTOCKBALANCESNAPSHOT` as opening balance — only delta reprocessed |

---

## 2. For Subject Matter Experts (SME)

### 2.1 Supported Valuation Methods

| Method | Applies To | How It Works |
|--------|-----------|--------------|
| **Weighted Average (Perpetual)** | All non-FIFO/LIFO items | Each receipt updates the running average cost. Issues are costed at the current average. |
| **Weighted Average (Batch)** | All non-FIFO/LIFO items | At period-end, a single weighted average is computed across all receipts in the period and applied to all transactions. |
| **FIFO — Lot-based** | Lot-tracked items (`ISBATCHSTOCK=1`) | Issues consume the oldest lot first. Each lot carries its own unit cost from the receipt GRN. |
| **FIFO — Layer-based** | Non-lot, FIFO method (`TCOSTLAYER`) | Same oldest-first logic but using cost layers (TCOSTLAYER) rather than physical lots. |
| **LIFO — Lot-based / Layer-based** | Same as FIFO variants | Newest lot/layer consumed first. Implemented as reversed FIFO ordering. |
| **Standard Cost** | Make (manufactured) items | BOM-based standard rates from a Cost Analysis. Variance against actual is posted to GL. |

**Valuation method is set per item** in Item Master (`MITEM.STOCKVALUATIONTYPE`). It cannot be changed mid-period without a Cost Revaluation entry.

---

### 2.2 Core Functional Flows

#### Flow 1 — Stock Ledger Posting (Every Transaction)

Every transaction that moves stock (GRN, Issue, Transfer, Production, Adjustment) writes rows to `TSTOCKLEDGER` and updates `TSTOCKPOSITION`. This happens synchronously within the transaction — if it fails, the parent document is not saved.

```
User saves a GRN / Issue / Transfer
    ↓
sp_ApplyStockBatch (TVP-based batch procedure)
    ↓  Aggregates all lines by Item/SKU/Store
    ↓  Inserts zero-balance TSTOCKPOSITION row if first transaction for this item
    ↓  Acquires UPDLOCK row locks on TSTOCKPOSITION (concurrency control)
    ↓  Validates: Quantity + ShadowQuantity >= 0 (negative stock protection)
    ↓  Updates TSTOCKPOSITION (delta — not a full recalculation)
    ↓  Bulk-inserts TSTOCKLEDGER rows
    ↓
StockLedgerCostBLL.UpdateCostAsync (same transaction)
    ↓  Stamps PostedCost on TSTOCKLEDGER = current AverageCost from TSTOCKPOSITION
    ↓  Updates TSTOCKPOSITION.AverageValue
    ↓  Recomputes TSTOCKPOSITION.AverageCost = AverageValue / Quantity
    ↓
If entry date < today:
    Dapr event published → background recosting job (does NOT block the user)
```

**Key point for SME:** The user gets an immediate response. The backdated recosting happens invisibly in the background. Until the background job completes, the system displays a "values are provisionally estimated" indicator on the affected period.

---

#### Flow 2 — Backdated Entry Recosting (Automatic Background Job)

When a GRN or adjustment is posted with a date before today, costs for all subsequent transactions must be recalculated. This is done by a Dapr background job — the user does not wait for it.

```
Backdated entry saved
    ↓
Dapr event: "mm.stockvaluation.recosting-required"
    Payload: { OUID, FromDate = entry date, ToDate = today }
    ↓
StockValuationJobSubscriber (background worker)
    ↓
StockValuationBLL.RunPerpetualRerunAsync
    1. Check TSTOCKVALUATIONRUN — skip if already running (idempotent)
    2. Insert TSTOCKVALUATIONRUN (Status = Running)
    3. Find the last CERTIFIED TSTOCKBALANCESNAPSHOT before FromDate
       → This is the opening balance (avoids reading full history)
    4. Reprocess TSTOCKLEDGER from snapshot date+1 to today
       (3-step batch SQL on a single connection — temp tables stay in scope)
    5. Update TSTOCKPOSITION.AverageCost / AverageValue
    6. Mark TSTOCKVALUATIONRUN Status = Done
```

---

#### Flow 3 — Period-End Batch Valuation Run

Used for monthly or daily weighted-average batch runs, and for applying standard costs to make items.

```
User: POST /StockValuation/RunStockValuation
    ↓
Validation:
  • Period must NOT be locked
  • No run already active for this OU/period (distributed mutex via TSTOCKVALUATIONRUN)
    ↓
TSTOCKVALUATIONRUN inserted (Status = Queued)
    ↓
Dapr event: "mm.stockvaluation.batch-requested"
    → User gets immediate response: "Valuation run started"
    ↓
Background worker (StockValuationBLL.RunBatchAsync):
  Step 1: Calculate weighted average for all items in period
  Step 2: Apply make-item costs from MPRODUCTCOST (pre-computed BOM roll-up)
  Step 3: Sync TSTOCKPOSITION from final TSTOCKLEDGER values
  Step 4: Write TSTOCKBALANCESNAPSHOT (closing balance for this period)
  Step 5: Mark TSTOCKVALUATIONRUN = Done
  Step 6: Dapr event: "mm.stockvaluation.batch-completed"
             → UI notification to user
```

---

#### Flow 4 — FIFO/LIFO Issue Allocation

For items using FIFO or LIFO, each issue must consume specific lots or cost layers. The system allocates quantities and computes a blended cost automatically.

**Lot-tracked items (e.g., batch chemicals, raw material lots):**
```
Issue document saved for lot-tracked item
    ↓
FifoLotCostBLL.AllocateLotsAsync
    ↓
SQL (ALLOCATE_FIFO_LOTS / ALLOCATE_LIFO_LOTS):
  • CTE builds running lot balances ordered by receipt date (oldest first = FIFO)
  • OUTER APPLY slices the issue quantity across lots
  • Returns: [ {LotId, ConsumedQty, UnitCost}, ... ]
    ↓
For each slice: TLOTDETAIL row inserted (audit trail)
    ↓
Blended cost = Σ(Qty × Cost) / TotalQty
    → Written to TSTOCKLEDGER.PostedCost
```

**Non-lot FIFO/LIFO items (e.g., standard steel bars, commodity items):**
```
Receipt → TCOSTLAYER row created (one layer per GRN line)
Issue   → ALLOCATE_FIFO_LAYERS / ALLOCATE_LIFO_LAYERS consumes layers
        → TCOSTLAYER.RemainingQuantity decremented
        → TCOSTLAYERDETAIL row inserted (audit)
        → Blended cost written to TSTOCKLEDGER.PostedCost
```

**If insufficient stock exists** (layer/lot balance < issue quantity), the transaction is rejected with a clear error: *"Insufficient lot/layer stock to allocate the full issue quantity."*

---

#### Flow 5 — Post-GRN Charge Apportionment (Freight, Duty, Handling)

Charges added after a GRN (e.g., freight invoice received separately) are applied **prospectively** — they increase AverageCost going forward but do NOT retroactively change costs on issues already made.

```
Freight charge of ₹5,000 linked to GRN-1042 (3 items)
    ↓
StockCostApportBLL.ApportionChargeAsync
    ↓
  Each item's share = ₹5,000 × (ItemValue / TotalGRNValue)
  e.g., Item A (value ₹30,000 / ₹60,000 total) → ₹2,500 share
    ↓
  TSTOCKPOSITION.AverageValue += ₹2,500  (for Item A)
  TSTOCKPOSITION.AverageCost  = AverageValue / Quantity  (recomputed)
    ↓
Dapr event: "mm.stockvaluation.charge-apportioned"
  → GL module posts the charge account debit
```

**SME Note:** Apportionment is by **item value** (not quantity or weight). This can be extended to other bases (quantity, weight) in configuration.

---

#### Flow 6 — Cost Revaluation (User-Overridden Cost)

When the calculated average cost needs to be corrected to a known actual value:

```
User creates Cost Revaluation document:
  Item A | Qty: 200 | Current Cost: ₹100 | New Cost: ₹110
    ↓
Value difference = (₹110 − ₹100) × 200 = ₹2,000
    ↓
TSTOCKLEDGER row: StockPostType=2 (Value Adjustment), Qty=0, PostedValue=+₹2,000
    ↓
TSTOCKPOSITION: AverageValue += ₹2,000; AverageCost = ₹110 (set directly)
    ↓
GL posting: DR Inventory ₹2,000 / CR Cost Revaluation Reserve ₹2,000
    ↓
From this point forward, all calculations use ₹110 as the new base cost.
```

**Constraints:**
- Period must be unlocked
- **Cannot be applied to FIFO/LIFO items** — adjust the specific lot/layer instead
- If dated before today, triggers a background recosting job

---

#### Flow 7 — Stock Account GL Posting

For every posted transaction, GL journal entries are generated covering up to 6 account slots (matching the legacy PROCSTOCKTOACCOUNTPOST logic):

| Account Slot | What It Posts |
|---|---|
| Inventory Account | Debit/Credit for stocked item movement |
| Cost of Goods Sold | For issues to production/sale |
| WIP Account | For work-in-process movements |
| Price Variance | For standard-cost vs actual variances |
| Asset Account | Capital items (triggers FAM event) |
| **Non-Stock Charges** (NEW) | Erection charges, service items on GRN — was never posted in legacy |

Transfer transactions use 2 slots: FROM-store inventory credit and TO-store inventory debit.

---

#### Flow 8 — Period-End Locking and Snapshot

```
Finance Manager locks the period
    ↓
System validates: All batch valuation runs are complete (Status=Done)
    ↓
TSTOCKBALANCESNAPSHOT:
  • One row per Item/SKU/Store for the period-end date
  • Captures ClosingQty, ClosingValue, ClosingCost, ValuationMethod
  • IsCertified = 1 (locked, immutable)
    ↓
TDAYLOCK records the period as locked
    ↓
Subsequent recosting runs use this snapshot as the opening balance.
  Any backdated entry into a LOCKED period is rejected with:
  "This period is locked. Stock valuation cannot be performed on a locked period."
```

---

### 2.3 Special Cases

#### Store-wise vs Consolidated Valuation

Controlled by an OU-level setting (`IsStorewiseValuation`):

| Mode | Behaviour |
|------|-----------|
| **Store-wise (per-store)** | Each store maintains its own AverageCost. Transfer between stores does not blend costs. |
| **Consolidated** | A single AverageCost is maintained across all stores of the OU. All stores see the same cost per item. |

#### Excluding Lines from Standard Cost

A line-level flag `IsExcludeFromStandardCost` (on TMMDETAIL) handles free receipts, promotional samples, and warranty replacements. When checked:

- The receipt **is included** in stock valuation (the item physically entered stock)
- The receipt **is excluded** from the standard material rate used in BOM cost roll-up
- The item's Last Purchase Rate is **not updated** from this line

This prevents artificially low standard costs from distorting BOM-based costing.

#### Mid-Period BOM Changes

BOM and routing changes are **frozen at period-start** (`MCOSTANALYSIS.PeriodFrom`). Changes made mid-period take effect only in the next period's standard cost run. This ensures consistency between production order standards and actual cost variance calculations.

---

### 2.4 Make Item (BOM) Cost Roll-Up Summary

```
Raw Material C (purchased):
    ValuationMethod=WtAvg → MaterialCost = AverageCost = ₹50

Sub-Assembly B (make, uses 2×C + 1 operation):
    MaterialCost  = 2 × ₹50 = ₹100
    ProcessCost   = 2 hrs × ₹10/hr = ₹20 (routing standard rate)
    Subcontract   = ₹15
    Overhead      = ₹8
    TotalCost     = ₹143  → stored in MPRODUCTCOST

Finished Good A (make, uses 3×B + final assembly op):
    MaterialCost  = 3 × ₹143 = ₹429
    ProcessCost   = ₹30 (FG assembly routing)
    Overhead      = ₹12
    TotalCost     = ₹471  → posted to TSTOCKLEDGER.PostedCost
```

Multi-level BOM traversal is performed bottom-up by the Costing module's `CostAnalysisBLL`. Stock Valuation reads the result from `MPRODUCTCOST` and applies it to TSTOCKLEDGER via `APPLY_MAKE_ITEM_COST`.

---

## 3. For End Users

### 3.1 Day-to-Day Operations — What You Need to Know

**You don't need to trigger valuation manually for most transactions.** When you save a GRN, issue, or transfer, costs are updated automatically in the background.

---

### 3.2 Running a Period Valuation

**When to do it:** At the end of each accounting period (typically month-end) before locking the period.

**Steps:**
1. Go to **Materials → Stock Valuation → Run Stock Valuation**
2. Select the period (From Date / To Date)
3. Optionally filter by Store or Item Category
4. Click **Run**
5. The system responds immediately: *"Stock valuation run started"*
6. Monitor progress at **Materials → Stock Valuation → Valuation Runs** dashboard
7. Status will show: **Queued → Running → Done** (or **Failed** with an error description)

**What to do if it fails:**
- Check the error message in the Valuation Runs dashboard
- Common causes: period already locked, network/database timeout, insufficient cost layers for FIFO items
- Contact your system administrator if the error is not self-explanatory

---

### 3.3 Checking Current Stock Value (Cost Workings)

**Steps:**
1. Go to **Materials → Stock Valuation → Cost Workings**
2. Enter: Item, SKU, Store, and As-Of Date
3. The system shows:
   - **Opening balance** (from the last certified period snapshot)
   - **All transactions** in the period with document number, type, quantity, and unit cost
   - **Closing balance** with calculated average cost
   - For FIFO/LIFO items: **Open cost layers** with remaining quantities and unit costs

---

### 3.4 Understanding Backdated Entry Warnings

If you save a GRN or adjustment with a date **before today**, you may see:

> *"Backdated entry detected. Recosting job queued from [date] to today. Values are temporarily provisional."*

This is normal. The system is recalculating costs for all transactions from that date forward. The recalculation happens in the background and typically completes within a few minutes. After it completes, refresh the cost workings screen to see updated values.

---

### 3.5 FIFO/LIFO — Open Cost Layers

For items using FIFO or LIFO costing, you can view the current cost layers at:

**Materials → Stock Valuation → Cost Layers**

This shows:
- Receipt date of each layer
- Original quantity received
- Remaining quantity (not yet consumed by issues)
- Unit cost for that layer
- Source document (GRN number)

The **next issue** will consume from the oldest layer (FIFO) or newest layer (LIFO) first.

---

### 3.6 Non-Stock Charges on GRN

When your GRN includes service lines (erection charges, inspection fees, freight included in the GRN itself), these are now posted directly to the relevant GL expense account. You will see them in:

**Stock Valuation → Non-Stock Posting Reconciliation**

This screen shows the posting status of each such line. If any shows **Failed**, contact your administrator.

---

## 4. For Implementation Team

### 4.1 Prerequisites

Before activating the stock valuation module:

1. **Database schema migration** must be run:
   - `DB/Migrations/StockValuation_Schema.sql` — creates `TSTOCKVALUATIONRUN`, `TCOSTLAYER`, `TCOSTLAYERDETAIL`, `TSTOCKBALANCESNAPSHOT`, `TSTOCKACCOUNTPOSTDETAIL`
   - `ALTER TABLE TMMDETAIL ADD IsExcludeFromStandardCost BIT NOT NULL DEFAULT 0`
   - Stored procedure: `dbo.sp_ApplyStockBatch` (TVP-based batch engine — see `Stockledgerrelated` file)
   - TVP type: `dbo.StockMovementTVP`

2. **Indexes** — Must be created (included in migration script):
   - `TSTOCKPOSITION`: `(OUID, ITEMID, SKUID, STOREID)` — unique, covering QUANTITY, AVERAGECOST, AVERAGEVALUE
   - `TSTOCKLEDGER`: `(OUID, OBJECTTYPEID, OBJECTID)` and `(OUID, STOREID, ITEMID, SKUID)`
   - `TCOSTLAYER`: `(OUID, STOREID, ITEMID, SKUID, LAYERDATE, ISFULLYCONSUMED)` filtered where `ISFULLYCONSUMED=0`

3. **Dapr components** must be configured (`MMSL/components/`):
   - `pubsub` component pointing to the message broker (Redis or Service Bus)
   - Topics: `mm.stockvaluation.batch-requested`, `mm.stockvaluation.recosting-required`, `mm.stockvaluation.batch-completed`, `mm.stockvaluation.charge-apportioned`, `mm.stockvaluation.gl-posting-required`, `fam.asset-acquisition-required`

4. **AutoNumber sequences** must be seeded for new entity types:
   - `COSTLAYER`, `COSTLAYERDETAIL`, `STOCKACCOUNTPOSTDETAIL`, `LOTDETAIL`

---

### 4.2 Configuration Checklist

| Setting | Where | Description |
|---------|-------|-------------|
| `MITEM.STOCKVALUATIONTYPE` | Item Master | 0=WtAvg, 1=FIFO, 2=LIFO, 3=Standard |
| `MITEM.ISBATCHSTOCK` | Item Master | 1=Lot-tracked (uses TLOT), 0=Non-lot (uses TCOSTLAYER for FIFO/LIFO) |
| `MITEM.ISSTOCKED` | Item Master | 0=Non-stocked (service/charge items — these go to non-stock GL posting) |
| `MITEM.ISCAPITAL` | Item Master | 1=Capital item → triggers FAM asset acquisition event |
| OU-level `IsStorewiseValuation` | OU Settings | 0=Per-store, 1=Consolidated cross-store |
| Period lock | `TDAYLOCK` | Controls whether backdated entries are accepted |

---

### 4.3 Integration Points

The stock valuation module integrates with these other modules:

| Module | Integration | Direction |
|--------|-------------|-----------|
| **Costing (CostAnalysis)** | Reads `MPRODUCTCOST` for make item standard costs | Inbound |
| **Finance / GL** | Publishes `mm.stockvaluation.gl-posting-required` Dapr event | Outbound |
| **Fixed Asset Management (FAM)** | Publishes `fam.asset-acquisition-required` for capital items | Outbound |
| **MM Document (StockLedgerBLL)** | Calls `StockLedgerCostBLL.UpdateCostAsync` within the posting transaction | Internal |

**Critical integration note:** `StockLedgerCostBLL.UpdateCostAsync` is called **inside the document's database transaction**. The Dapr event publish (for backdated entries) happens **after** the SQL updates within the same method call but outside the SQL transaction boundary. If the SQL transaction rolls back, the Dapr event may have already been sent. This is handled by idempotency in the subscriber: the `TSTOCKVALUATIONRUN` unique constraint rejects duplicate runs.

---

### 4.4 Rollout Sequence

1. Run DB migration scripts
2. Deploy MMDAL (DAL assemblies)
3. Deploy MMBLL (BLL assemblies)
4. Deploy MMSL (endpoints + Dapr subscriber)
5. Verify Dapr sidecar is running alongside MMSL
6. Configure AutoNumber sequences for new entity types
7. Run startup health check: `RecoverStuckRunsAsync` marks any orphaned `Running` valuation runs as `Failed` — this fires on application start
8. Seed initial `TSTOCKBALANCESNAPSHOT` for the opening period if migrating from legacy
9. Run a trial batch valuation for one item/store and verify against legacy output

---

### 4.5 Data Migration from Legacy

If migrating existing data from the legacy system:

1. **TSTOCKPOSITION** — Carry over existing `AVERAGECOST` and `AVERAGEVALUE` as-is; these become the opening position.
2. **TSTOCKLEDGER** — Carry over historical rows; `POSTEDCOST` and `POSTEDVALUE` will be as calculated by the legacy system.
3. **TCOSTLAYER** — For items switching to FIFO/LIFO, create opening layers based on the current stock position using the current AverageCost as the layer cost. Use `LAYERDATE = period-start date`.
4. **TSTOCKBALANCESNAPSHOT** — Insert a certified snapshot for the last locked period with `IsCertified=1`. This prevents the recosting engine from re-reading all legacy history.
5. **TLOT / TLOTDETAIL** — For lot-tracked items, carry over existing open lots from the legacy system.

---

## 5. For System Administrators

### 5.1 Monitoring the Valuation Run Dashboard

**URL:** `GET /StockValuation/GetValuationRuns`

Key fields to monitor:

| Status | Meaning | Action |
|--------|---------|--------|
| Queued | Run created, waiting for Dapr worker | Check if MMSL is running and Dapr sidecar is connected |
| Running | Active — should complete within minutes | If stuck for >2 hours, it will auto-recover on next startup |
| Done | Completed successfully | No action needed |
| Failed | Error occurred | Check `ErrorMessage` field; re-run after fixing the cause |

---

### 5.2 Stuck Run Recovery

Runs stuck in `Running` status for more than 2 hours are automatically marked `Failed` on MMSL application startup via `StockValuationBLL.RecoverStuckRunsAsync`. This uses `TSTOCKVALUATIONRUN.StartedAt` to identify candidates.

**Manual recovery:** If MMSL is down and you need to recover immediately, run:
```sql
UPDATE TSTOCKVALUATIONRUN
SET STATUS = 3, ERRORMESSAGE = 'Manually recovered — server restart', COMPLETEDAT = GETUTCDATE()
WHERE STATUS = 1 AND STARTEDAT < DATEADD(HOUR, -2, GETUTCDATE())
```

---

### 5.3 Concurrent Run Protection

The system prevents two batch runs for the same OU/period from running simultaneously via a unique constraint on `TSTOCKVALUATIONRUN (OUID, RUNTYPE, PERIODFROM, PERIODTO)` where `STATUS IN (0, 1)` (Queued or Running).

If a second request comes in while a run is active, the API returns:
> *"A valuation run is already in progress for this OU and period. Please wait for it to complete."*

---

### 5.4 Large Dataset Performance

For organisations with >100,000 TSTOCKLEDGER rows per period:

- The batch SQL uses `OPTION (MAXDOP N)` hints on heavy aggregation queries (configured in StockValuationQB constants)
- Items are processed in pages of 500 within the BLL batch loop
- Consider scheduling monthly runs during off-peak hours
- Monitor SQL Server `sys.dm_exec_requests` during runs for blocking or deadlock indicators

---

### 5.5 Deadlock Handling

If a deadlock occurs on `TSTOCKLEDGER` during concurrent transaction posting and batch rerun:

- The DAL catches `SqlException.Number = 1205` (deadlock) and `1222` (lock timeout)
- These are logged as `Warning` and re-thrown for Dapr retry
- Dapr retries the event with exponential backoff (configure in the Dapr resilience policy)

---

### 5.6 GL Posting Failures

If `TSTOCKACCOUNTPOSTDETAIL.PostingStatus = 2` (Failed) for any rows:

1. Check `PostedError` column for the specific error
2. Common cause: GL account not mapped for the item/transaction type
3. Fix the GL account mapping
4. Re-trigger: `POST /StockValuation/PostToGL?ObjectTypeId=...&ObjectId=...`

---

## 6. For the Development Team

### 6.1 Architecture Summary

```
MMSL (Service Layer / FastEndpoints)
  │
  ├── EndPoints/StockValuation/          ← 6 FastEndpoints (RunStockValuation, GetValuationRuns,
  │                                         GetCostLayers, GetCostWorkings, PostToGL,
  │                                         GetNonStockPostingReconciliation)
  │
  ├── Subscriptions/StockValuationJobSubscriber.cs  ← Dapr [Topic] subscriber
  │      Topics: mm.stockvaluation.batch-requested
  │              mm.stockvaluation.recosting-required
  │
  ↓
MMBLL (Business Logic Layer)
  ├── StockValuation/      IStockValuationBLL / StockValuationBLL
  ├── StockPosition/       IStockPositionBLL  / StockPositionBLL
  ├── StockLedgerCost/     IStockLedgerCostBLL / StockLedgerCostBLL  ← publishes backdated event
  ├── FifoLotCost/         IFifoLotCostBLL   / FifoLotCostBLL
  ├── FifoLayerCost/       IFifoLayerCostBLL / FifoLayerCostBLL
  ├── StockCostApport/     IStockCostApportBLL / StockCostApportBLL
  └── StockAccountPost/    IStockAccountPostBLL / StockAccountPostBLL
  ↓
MMDAL (Data Access Layer)
  ├── CustomCode/StockValuation/      IStockValuationDAL / StockValuationDAL
  ├── CustomCode/StockPosition/       IStockPositionDAL  / StockPositionDAL
  ├── CustomCode/StockLedgerCost/     IStockLedgerCostDAL / StockLedgerCostDAL
  ├── CustomCode/FifoLotCost/         IFifoLotCostDAL   / FifoLotCostDAL
  ├── CustomCode/FifoLayerCost/       IFifoLayerCostDAL / FifoLayerCostDAL
  └── CustomCode/StockAccountPost/    IStockAccountPostDAL / StockAccountPostDAL
  ↓
MMDAL/Query/StockValuation/          ← Query Builder constants (pure SQL, no logic)
  ├── StockValuationQB.cs
  ├── StockLedgerCostQB.cs
  ├── StockPositionQB.cs
  ├── FifoLotCostQB.cs
  ├── FifoLayerCostQB.cs
  └── StockAccountPostQB.cs
```

---

### 6.2 The Stock Ledger Engine (sp_ApplyStockBatch)

The TVP-based stored procedure (`sp_ApplyStockBatch`) is the stock posting engine that replaces the legacy triggers (`TRGINSTSTOCKLEDGER`, `TRGDELTSTOCKLEDGER`, `TRGTSTOCKLEDGERITEMSKU`, `TRGDELTSTOCKPOSITION`).

**Why TVP instead of triggers:**
- Triggers fire row-by-row; TVP processes the entire batch in one pass
- Delta aggregation (`SUM(CASE WHEN STOCKPOSTTYPE=0 THEN +qty ELSE -qty END)`) means one UPDATE per item regardless of how many document lines exist
- `WITH (UPDLOCK, ROWLOCK)` hints on the position lock step prevent deadlocks while keeping lock granularity minimal

**The 7-step execution sequence:**

```sql
Step 1: SELECT INTO #AGG       — aggregate deltas by Item/SKU/Store
Step 2: INSERT TSTOCKPOSITION  — create zero-balance rows for new items (IF NOT EXISTS)
Step 3: SELECT INTO #LOCKED    — acquire UPDLOCK, ROWLOCK on TSTOCKPOSITION rows
Step 4: IF EXISTS (violation)  — validate Quantity + ShadowQuantity >= 0; ROLLBACK if violated
Step 5: UPDATE TSTOCKPOSITION  — apply deltas (never a full recalculation)
Step 6: INSERT TSTOCKLEDGER    — bulk insert from TVP
Step 7: COMMIT TRAN
```

**Shadow Quantity rule:** Shadow stock tracks deliveries that are received but not yet physically accepted (DeliveryType IN (2,3,4,5) — Partial, Conditional, etc.). The validation check `Quantity + |ShadowQuantity| >= 0` ensures that shadow stock consumption cannot drive physical stock negative.

---

### 6.3 Perpetual Rerun — Temp Table Scope

The 3-step perpetual rerun SQL (`PERPET_RERUN_STEP1_CALC_AVG`, `STEP2_JOIN_TO_LEDGER`, `STEP3_UPDATE_LEDGER`) shares `#tmpavg` and `#tmpresolved` temp tables across all three steps. This requires all three to execute on the **same SQL connection**.

This is achieved via `IQueryExecutor.ExecuteInTransactionAsync(login, IEnumerable<(string sql, object param)>)` which keeps the connection open for the entire sequence:

```csharp
// StockLedgerCostDAL.RunPerpetualRerunBatchAsync
var steps = new (string Sql, object Param)[]
{
    (StockLedgerCostQB.PERPET_RERUN_STEP1_CALC_AVG,   params1),
    (StockLedgerCostQB.PERPET_RERUN_STEP2_JOIN_TO_LEDGER, params2),
    (StockLedgerCostQB.PERPET_RERUN_STEP3_UPDATE_LEDGER,  new {}),
    (StockLedgerCostQB.PERPET_RERUN_DROP_TEMP_1,          new {}),  // always cleanup
    (StockLedgerCostQB.PERPET_RERUN_DROP_TEMP_2,          new {})
};
await _qe.ExecuteInTransactionAsync(login, steps, ct: ct);
```

The DROP statements always run — even if Step 2 or 3 fail — because they are part of the step array and execute sequentially on the same connection regardless of the previous step's outcome. (The actual transaction rollback is handled by the caller if an exception is thrown.)

---

### 6.4 FIFO Allocation — CTE Window Function Pattern

The CTE pattern that replaced `PROCFIFOLOT`:

```sql
WITH lot_balance AS (
    SELECT LOTID, LOTDATE, SUM(+/- QUANTITY) AS NET_STOCK FROM TLOT+TLOTDETAIL
    GROUP BY LOTID, LOTDATE HAVING SUM(...) > 0
),
lot_running AS (
    SELECT *, SUM(NET_STOCK) OVER (
        PARTITION BY ITEMID, SKUID, OUID
        ORDER BY LOTDATE ASC, LOTID ASC          -- ASC = FIFO; DESC = LIFO
        ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
    ) AS RUNSTOCK
    FROM lot_balance
)
-- OUTER APPLY slices the issue qty across lots:
-- FINALQTY = CASE
--   WHEN RUNSTOCK - IssueQty <= 0 THEN NET_STOCK        -- fully consumed
--   WHEN RUNSTOCK - IssueQty >= NET_STOCK THEN 0         -- not yet reached
--   ELSE NET_STOCK - (RUNSTOCK - IssueQty)               -- partial slice
-- END
```

**Why two separate QB constants (ALLOCATE_FIFO_LOTS / ALLOCATE_LIFO_LOTS) instead of a parameter?**
SQL Server window functions cannot parameterize `ORDER BY` direction. The two constants are identical except for `ASC` vs `DESC` in the `ORDER BY` clause. The BLL selects which constant to use based on `isLifo`.

---

### 6.5 Non-Stock GL Gap Fix — Implementation Detail

The legacy system only GL-posted items that had a `TSTOCKLEDGER` row. Non-stocked items on a GRN (erection charges, inspection services) had `ISSTOCKED=0` in MITEM and therefore produced no TSTOCKLEDGER row — so they were never posted.

**Fix:** `StockAccountPostQB.GET_NON_STOCK_CHARGE_ITEMS` queries `TMMDETAIL` directly:

```sql
SELECT md.TMMDETAILID, md.ITEMID, md.ITEMPOSTEDCOST * md.QUANTITY AS PostedValue,
       md.ACCOUNTID AS GLAccount, 2 AS PostingType  -- 2 = NonStockCharge
FROM TMMDETAIL md
JOIN MITEM mi ON mi.ITEMID = md.ITEMID AND mi.ISSTOCKED = 0
WHERE md.OBJECTTYPEID = @ObjectTypeId AND md.OBJECTID = @ObjectId
  AND md.ITEMPOSTEDCOST > 0 AND md.QUANTITY > 0
```

The `PostingType=2` flag in `TSTOCKACCOUNTPOSTDETAIL` distinguishes these from normal stock postings and is the key column for the non-stock reconciliation report.

---

### 6.6 DI Registration

All new BLL and DAL classes auto-register via assembly scanning — no manual `services.Add...` calls needed:

```csharp
// Already in Program.cs — no changes required
services.AddScopedFromAssembly(typeof(IItemStockBLL).Assembly);  // picks up all new BLL classes
services.AddScopedFromAssembly(typeof(IItemStockDAL).Assembly);  // picks up all new DAL classes
```

`DaprClient` is injected directly — ensure it is registered in Program.cs:
```csharp
builder.Services.AddDaprClient();
```

---

### 6.7 Resource Strings — All BLL Messages

All user-visible messages from BLL methods use `MMResource` (never hardcoded English):

| Property | Key | Value |
|----------|-----|-------|
| `MMResource.ValuationRunStarted` | `ValuationRunStarted` | Stock valuation run started. |
| `MMResource.ValuationRunCompleted` | `ValuationRunCompleted` | Stock valuation run completed successfully. |
| `MMResource.ValuationRunFailed` | `ValuationRunFailed` | Stock valuation run failed. |
| `MMResource.ValuationRunAlreadyActive` | `ValuationRunAlreadyActive` | A valuation run is already in progress... |
| `MMResource.PeriodAlreadyLocked` | `PeriodAlreadyLocked` | This period is locked. |
| `MMResource.BackdatedEntryRecostingQueued` | `BackdatedEntryRecostingQueued` | Backdated entry detected. Recosting job queued from {0} to today. |
| `MMResource.InsufficientStockForFifo` | `InsufficientStockForFifo` | Insufficient lot/layer stock to allocate... |
| `MMResource.CostRevaluationApplied` | `CostRevaluationApplied` | Cost revaluation applied successfully. |
| `MMResource.FifoNotSupportedForRevaluation` | `FifoNotSupportedForRevaluation` | Cost revaluation is not supported for FIFO/LIFO items. |
| `MMResource.StockAccountPostingCompleted` | `StockAccountPostingCompleted` | Stock account posting completed successfully. |
| `MMResource.StockAccountPostingFailed` | `StockAccountPostingFailed` | Stock account posting failed. |
| `MMResource.CostLayerNotFound` | `CostLayerNotFound` | No open cost layers found for this item. |
| `MMResource.SnapshotCertified` | `SnapshotCertified` | Period-end snapshot certified successfully. |

---

### 6.8 Caching Policy

All stock valuation endpoints use `CacheKeyLevel.NOT_REQUIRED`. Financial/ledger data is always queried live. There is no cache invalidation needed for these endpoints — by design, they never cache.

---

## 7. For Quality Control (QC)

### 7.1 Test Scenario Matrix

#### TC-001: Perpetual Moving Average — Basic Receipt

| | |
|---|---|
| **Setup** | Item A, WtAvg method. TSTOCKPOSITION: Qty=100, AverageCost=₹50, AverageValue=₹5,000 |
| **Action** | Post GRN: 50 units @ ₹60 |
| **Expected TSTOCKLEDGER** | PostedCost=₹50 (existing avg), PostedValue=₹2,500 |
| **Expected TSTOCKPOSITION** | Qty=150, AverageValue=₹8,000, AverageCost=₹53.33 (8000/150) |
| **Verify** | `SELECT QUANTITY, AVERAGECOST, AVERAGEVALUE FROM TSTOCKPOSITION WHERE ITEMID=@X` |

#### TC-002: Store-Wise Isolation

| | |
|---|---|
| **Setup** | IsStorewiseValuation=0 (per-store). Store A: Cost=₹50. Store B: Cost=₹80 |
| **Action** | Post GRN to Store A only |
| **Expected** | Store B AverageCost unchanged at ₹80 |
| **Negative test** | If IsStorewiseValuation=1 (consolidated), both stores must show same new blended cost |

#### TC-003: Backdated Entry Recosting

| | |
|---|---|
| **Setup** | Today=May 18. TSTOCKBALANCESNAPSHOT certified for April 30. |
| **Action** | Post GRN dated May 5 |
| **Expected** | Dapr event published with FromDate=May 5, ToDate=May 18 |
| **Expected** | TSTOCKVALUATIONRUN row created (RunType=2, Status transitions Queued→Running→Done) |
| **Verify** | `SELECT STATUS, ERRORMESSAGE FROM TSTOCKVALUATIONRUN WHERE RUNTYPE=2 ORDER BY CREATEDON DESC` |
| **Verify** | TSTOCKLEDGER PostedCost for May 5-18 transactions matches recomputed weighted average |

#### TC-004: FIFO Lot Allocation — Exact Consumption

| | |
|---|---|
| **Setup** | Lot L1 (100 units @ ₹50, date Apr 10), Lot L2 (200 units @ ₹60, date Apr 15) |
| **Action** | Issue 150 units (FIFO) |
| **Expected slices** | L1: 100 @ ₹50, L2: 50 @ ₹60 |
| **Expected blended cost** | (100×₹50 + 50×₹60) / 150 = ₹53.33 |
| **Expected TSTOCKLEDGER.PostedCost** | ₹53.33 |
| **Verify TLOTDETAIL** | 2 rows with StockPostType=1, ConsumedQty 100 and 50 |
| **LIFO variant** | L2: 150 @ ₹60; blended = ₹60.00 |

#### TC-005: FIFO Insufficient Stock

| | |
|---|---|
| **Setup** | Total open lot balance = 80 units |
| **Action** | Attempt to issue 100 units |
| **Expected** | Transaction rejected: *"Insufficient lot/layer stock to allocate the full issue quantity."* |
| **Verify** | No TLOTDETAIL rows created; TSTOCKLEDGER not written |

#### TC-006: Cost Layer (Non-Lot FIFO)

| | |
|---|---|
| **Setup** | Item B, FIFO method, not lot-tracked. GRN: 200 units @ ₹100 |
| **Action 1** | Post GRN |
| **Expected** | TCOSTLAYER row: Qty=200, RemainingQty=200, UnitCost=₹100, IsFullyConsumed=0 |
| **Action 2** | Issue 150 units |
| **Expected** | TCOSTLAYER: RemainingQty=50, IsFullyConsumed=0. TCOSTLAYERDETAIL: ConsumedQty=150 |
| **Action 3** | Issue 50 units |
| **Expected** | TCOSTLAYER: RemainingQty=0, IsFullyConsumed=1 |

#### TC-007: Post-GRN Charge Apportionment

| | |
|---|---|
| **Setup** | GRN-100: Item A (value ₹30,000), Item B (value ₹20,000). Total GRN value = ₹50,000 |
| **Action** | Apportion ₹5,000 freight charge |
| **Expected Item A** | Share = ₹5,000 × (30,000/50,000) = ₹3,000. TSTOCKPOSITION.AverageValue += ₹3,000 |
| **Expected Item B** | Share = ₹5,000 × (20,000/50,000) = ₹2,000. TSTOCKPOSITION.AverageValue += ₹2,000 |
| **Verify** | Historical TSTOCKLEDGER rows (issues already made before the charge) are NOT changed |

#### TC-008: Period Lock Protection

| | |
|---|---|
| **Setup** | Period April 2025 locked in TDAYLOCK |
| **Action** | Attempt to run stock valuation for April |
| **Expected** | API returns: *"This period is locked. Stock valuation cannot be performed on a locked period."* |
| **Verify** | No TSTOCKVALUATIONRUN row created |

#### TC-009: Non-Stock Item GL Posting

| | |
|---|---|
| **Setup** | GRN with 1 line: Erection Charge (ISSTOCKED=0), ₹5,000, ACCOUNTID=GL-EXP-001 |
| **Action** | Trigger PostToGL for the GRN |
| **Expected** | TSTOCKACCOUNTPOSTDETAIL row: PostingType=2, GLAccount=GL-EXP-001, PostedValue=₹5,000 |
| **Expected** | Dapr event published: "mm.stockvaluation.gl-posting-required" |
| **Verify** | No TSTOCKLEDGER row exists for this line (confirming the GL gap fix works without a ledger row) |

#### TC-010: Cost Revaluation

| | |
|---|---|
| **Setup** | Item A: TSTOCKPOSITION Qty=200, AverageCost=₹100, AverageValue=₹20,000 |
| **Action** | Create Cost Revaluation: NewCost=₹110 |
| **Expected TSTOCKPOSITION** | AverageCost=₹110, AverageValue=₹22,000 |
| **Expected TSTOCKLEDGER** | StockPostType=2, Qty=0, PostedValue=₹2,000 |
| **Expected GL event** | DR Inventory ₹2,000 / CR Cost Revaluation Reserve ₹2,000 |
| **Negative test** | Attempt revaluation on FIFO item → rejected: *"Cost revaluation is not supported for FIFO/LIFO items."* |

#### TC-011: Valuation Run Concurrency

| | |
|---|---|
| **Action** | Trigger two RunStockValuation requests for same OU/period simultaneously |
| **Expected** | First request: creates TSTOCKVALUATIONRUN row, returns "run started" |
| **Expected** | Second request: returns "A valuation run is already in progress" (no second row created) |

#### TC-012: Opening Snapshot as Recosting Base

| | |
|---|---|
| **Setup** | Certified TSTOCKBALANCESNAPSHOT for Feb 28: Qty=150, Value=₹15,000, Cost=₹100 |
| **Action** | Post backdated GRN for March 15 |
| **Expected** | Recosting reads Feb 28 snapshot as opening (not full history from 2019) |
| **Verify** | `TSTOCKBALANCESNAPSHOT.ISCERTIFIED=1` row is used; `TSTOCKLEDGER` rows before Feb 28 are not re-read |

#### TC-013: IsExcludeFromStandardCost Flag

| | |
|---|---|
| **Setup** | GRN with 2 lines: Line 1 normal (₹100), Line 2 free sample (₹0, IsExcludeFromStandardCost=1) |
| **Verify for stock valuation** | TSTOCKPOSITION.AverageValue includes Line 2 (free receipt DID add stock) |
| **Verify for standard cost** | ProductCost query excludes Line 2 from material rate calculation |
| **Verify** | MITEM.LastPurchaseRate is NOT updated by Line 2 |

---

### 7.2 Regression Tests — Verify Legacy Behaviour Preserved

| Test | What to check |
|------|---------------|
| GRN posting cost stamp | TSTOCKLEDGER.PostedCost = TSTOCKPOSITION.AverageCost at time of posting (not at time of valuation run) |
| Transfer posting | TO-store PostedCost = FROM-store AverageCost; FROM-store AverageValue decremented; TO-store AverageValue NOT changed |
| WIP quantity tracking | TSTOCKPOSITION.WIPQUANTITY increments on WIP-IN (StockPostType=2), decrements on WIP-OUT (StockPostType=3) |
| Shadow quantity | TSTOCKPOSITION.SHADOWQUANTITY changes inversely to GoodQuantity for conditional deliveries |
| Negative stock rejection | Issue that would drive Qty + Shadow < 0 must be blocked by sp_ApplyStockBatch |

---

### 7.3 Performance Benchmarks (Target)

| Operation | Max Acceptable Time |
|-----------|---------------------|
| GRN posting (10 lines) | < 500ms end-to-end |
| Backdated recosting job (30-day period, 500 items) | < 5 minutes |
| Monthly batch valuation (1,000 items, 1 store) | < 15 minutes |
| FIFO lot allocation (single issue, 10 lots) | < 100ms |
| Cost workings fetch (1 item, 1 period) | < 1 second |

---

## 8. For Auditors

### 8.1 Audit Trail Architecture

Every stock movement is permanently recorded and traceable. The system provides four levels of traceability:

| Level | Table | What It Records |
|-------|-------|-----------------|
| **Transaction** | `TSTOCKLEDGER` | Every stock movement with document reference, quantity, PostedCost, PostedValue |
| **Position** | `TSTOCKPOSITION` | Current balance per item/SKU/store (live; changes with every transaction) |
| **Period Snapshot** | `TSTOCKBALANCESNAPSHOT` | Certified closing balance per item/SKU/store at period-end (immutable once IsCertified=1) |
| **GL Reconciliation** | `TSTOCKACCOUNTPOSTDETAIL` | Every GL debit/credit generated, with posting status and error details |

---

### 8.2 How to Verify Stock Value at a Point in Time

```sql
-- Closing stock value for OUID=1, as of April 30, 2025 (certified snapshot)
SELECT ItemId, SKUId, StoreId,
       ClosingQty, ClosingValue, ClosingCost, ValuationMethod, IsCertified
FROM TSTOCKBALANCESNAPSHOT
WHERE OUID = 1 AND SNAPSHOTDATE = '2025-04-30' AND ISCERTIFIED = 1
ORDER BY ITEMID, SKUID, STOREID;
```

**IsCertified=1** means the snapshot was locked as part of the period-close process and cannot be changed by any subsequent transaction or recosting run. It represents the auditable closing position.

---

### 8.3 Tracing a Transaction to GL

For any stock movement, the GL posting trail is:

```
TSTOCKLEDGER.STOCKLEDGERID
    ↓ (linked via ObjectTypeId + ObjectId)
TSTOCKACCOUNTPOSTDETAIL rows
    ↓ (PostingStatus=1=Posted)
Dapr event payload (GLAccount, PostedValue, PostingType)
    ↓
Finance module GL journal entry
```

**Query to verify GL posting for a specific document:**
```sql
SELECT d.PostingType, d.ItemId, d.GLAccount, d.PostedValue,
       d.PostingStatus, d.PostedError, d.CreatedOn
FROM TSTOCKACCOUNTPOSTDETAIL d
WHERE d.ObjectTypeId = @ObjectTypeId AND d.ObjectId = @ObjectId
ORDER BY d.PostingType, d.ItemId;
```

**PostingType values:**
- `0` = StockedItem (standard inventory movement)
- `1` = CapitalItem (asset acquisition — also triggers FAM event)
- `2` = NonStockCharge (erection charges, services — new in GB5)

---

### 8.4 FIFO/LIFO Audit — Layer Consumption Trail

For FIFO/LIFO non-lot items, every quantity consumed from every layer is recorded:

```sql
-- Show all consumption from cost layer 12345
SELECT cld.ConsumedQuantity, cld.UnitCost, cld.ConsumedOn,
       sl.STOCKLEDGERDATE AS IssueDate, mmh.DOCUMENTNUMBER AS IssueDoc
FROM TCOSTLAYERDETAIL cld
JOIN TSTOCKLEDGER sl ON sl.STOCKLEDGERID = cld.ISSUESTOCKLEDGERID
LEFT JOIN TMMHEAD mmh ON mmh.DOCUMENTID = sl.OBJECTID
WHERE cld.COSTLAYERID = 12345
ORDER BY cld.CONSUMEDON;
```

**Key invariant to verify:** The sum of all `TCOSTLAYERDETAIL.CONSUMEDQUANTITY` for a given `COSTLAYERID` must equal `TCOSTLAYER.ORIGINALQUANTITY - TCOSTLAYER.REMAININGQUANTITY`.

---

### 8.5 Valuation Run History

Every batch or recosting run is permanently recorded in `TSTOCKVALUATIONRUN`:

```sql
SELECT OUID, RunType, PeriodFrom, PeriodTo, Status,
       StartedAt, CompletedAt,
       DATEDIFF(SECOND, StartedAt, CompletedAt) AS DurationSeconds,
       ErrorMessage, CreatedById, CreatedOn
FROM TSTOCKVALUATIONRUN
WHERE OUID = @OUID
ORDER BY CreatedOn DESC;
```

**RunType values:** 0=Monthly, 1=Daily, 2=Perpetual-Rerun (backdated entry)  
**Status values:** 0=Queued, 1=Running, 2=Done, 3=Failed

This table provides a complete history of when valuations were run, by whom, covering which period, and whether they succeeded — meeting the audit requirement for documented period-close procedures.

---

### 8.6 Cost Revaluation Audit Trail

Every cost revaluation entry creates:

1. A `TSTOCKLEDGER` row with `STOCKPOSTTYPE=2` (Value Adjustment), `QUANTITY=0`, and a non-zero `POSTEDVALUE`
2. A `TSTOCKACCOUNTPOSTDETAIL` row with the GL debit/credit details
3. Direct update of `TSTOCKPOSITION.AVERAGECOST` and `AVERAGEVALUE`

**Query to find all revaluations in a period:**
```sql
SELECT sl.STOCKLEDGERID, sl.STOCKLEDGERDATE, sl.ITEMID, sl.POSTEDVALUE AS ValueAdjustment,
       mi.ITEMCODE, mi.ITEMNAME
FROM TSTOCKLEDGER sl
JOIN MITEM mi ON mi.ITEMID = sl.ITEMID
JOIN MBIZTRANSACTIONTYPE btt ON btt.BIZTRANSACTIONTYPEID = sl.BIZTRANSACTIONTYPEID
JOIN MBIZTRANSACTIONCLASS btc ON btc.BIZTRANSACTIONCLASSID = btt.BIZTRANSACTIONCLASSID
WHERE sl.OUID = @OUID
  AND sl.STOCKPOSTTYPE = 2  -- Value Adjustment
  AND sl.STOCKLEDGERDATE BETWEEN @FromDate AND @ToDate
ORDER BY sl.STOCKLEDGERDATE;
```

---

### 8.7 Controls Summary

| Control | Implementation |
|---------|---------------|
| **Negative stock prevention** | `sp_ApplyStockBatch` Step 4 raises error if `Qty + Shadow < 0`; transaction is rolled back |
| **Period lock enforcement** | `CHECK_PERIOD_LOCKED` query; locked periods reject valuation runs and backdated entries |
| **Concurrent run prevention** | Unique constraint on `TSTOCKVALUATIONRUN (OUID, RunType, PeriodFrom, PeriodTo)` for active runs |
| **Cost layer integrity** | `TCOSTLAYERDETAIL` records every consumption; remaining qty is updated atomically in the same transaction |
| **Immutable period snapshots** | `IsCertified=1` rows in `TSTOCKBALANCESNAPSHOT` are write-protected by application logic |
| **Idempotent recosting** | Duplicate Dapr events are rejected if a run already exists for the same OU/period/type |
| **SQL injection prevention** | All SQL uses Dapper `@Param` binding; no string concatenation in any QB constant |
| **Multi-tenancy** | Every query includes `OUID` filter; cross-tenant data is architecturally prevented |
| **Audit log** | All significant business events published via `_eventLogPublish.PublishEventLogAsync` |

---

### 8.8 Data Retention

| Table | Retention | Notes |
|-------|-----------|-------|
| `TSTOCKLEDGER` | Permanent | Never deleted — full transaction history |
| `TSTOCKPOSITION` | Live (rolling) | Current balance only — history is in TSTOCKLEDGER |
| `TSTOCKBALANCESNAPSHOT` | Permanent | One certified row per period-end per item/store |
| `TSTOCKVALUATIONRUN` | Permanent | Audit trail of all valuation runs |
| `TSTOCKACCOUNTPOSTDETAIL` | Permanent | GL reconciliation for every posted transaction |
| `TCOSTLAYER` | Until fully consumed + period locked | IsFullyConsumed=1 rows can be archived after period close |
| `TCOSTLAYERDETAIL` | Permanent | Consumption audit for every FIFO/LIFO issue |

---

## 9. Glossary

| Term | Definition |
|------|-----------|
| **AverageCost** | The weighted average unit cost of all stock currently held. Changes with every receipt (perpetual) or at period-end (batch). |
| **AverageValue** | `AverageCost × Quantity` — total monetary value of stock position. |
| **Cost Layer** | A record in `TCOSTLAYER` representing one batch of received stock for a non-lot FIFO/LIFO item. Carries its original receipt cost until fully consumed. |
| **Cost Revaluation** | A document that directly overrides the AverageCost to a user-specified value. Creates a GL adjustment entry. |
| **Dapr** | Distributed Application Runtime — the messaging middleware used for async background jobs (pub/sub). |
| **FIFO** | First In, First Out — issues consume the oldest stock first. |
| **IsCertified** | Flag on `TSTOCKBALANCESNAPSHOT` indicating the snapshot was locked as part of a period-close. Immutable once set. |
| **IsExcludeFromStandardCost** | Line-level flag on transaction detail. Prevents a free/promotional receipt from distorting BOM standard material rates. Stock valuation is not affected. |
| **LIFO** | Last In, First Out — issues consume the newest stock first. Implemented as reversed FIFO ordering. |
| **Lot** | A batch of physically identified stock (e.g., a production batch number or supplier lot). Tracked in `TLOT`. |
| **Make Item** | An item that is manufactured in-house. Its cost comes from a BOM-based cost analysis (`MPRODUCTCOST`). |
| **TSTOCKLEDGER** | The permanent transaction ledger — every stock movement ever made. Never modified after posting. |
| **TSTOCKPOSITION** | The live stock balance — current quantity, value, and cost per item/SKU/store. Updated by every transaction. |
| **TSTOCKVALUATIONRUN** | A record of every valuation run (batch or perpetual rerun). Acts as a distributed mutex and audit log. |
| **Perpetual Valuation** | Cost is updated in real time with every transaction (as opposed to batch/period-end). |
| **PostedCost** | The unit cost stamped on a `TSTOCKLEDGER` row at the time of posting. For receipts: the AverageCost at posting time. For FIFO issues: the blended lot/layer cost. |
| **Shadow Quantity** | Stock that is received under a conditional delivery term (not yet fully accepted). Reduces available stock but is held separately from good quantity. |
| **Standard Cost** | A pre-calculated cost from a BOM analysis, used for make items. Variance between standard and actual is posted to a Cost Variance GL account. |
| **TVP** | Table-Valued Parameter — a SQL Server mechanism to pass a set of rows to a stored procedure in one call. Used by `sp_ApplyStockBatch` for batch stock posting. |
| **Weighted Average** | Total value of stock ÷ total quantity. Recomputed after every receipt. |
| **WIP** | Work in Process — stock consumed in a production order but not yet completed as finished goods. |

---

*Document maintained by: GB5 MM Development Team*  
*For questions: Contact the MM module lead or raise a GitLab issue on the gb5 project.*