# Stock Reservation — Technical Developer & Implementation Guide

**Module:** Materials Management (MM) — GB5 Backend
**Version:** GB5 v1.0
**Implemented:** 2026-06-10
**Author:** GB5 Backend Team

---

## Table of Contents

1. [Architecture Overview](#1-architecture-overview)
2. [Database Schema](#2-database-schema)
3. [API Reference — All Endpoints](#3-api-reference--all-endpoints)
4. [BLL Validation Rules & Error Messages](#4-bll-validation-rules--error-messages)
5. [BLL Operation Flows](#5-bll-operation-flows)
6. [Caching Policy](#6-caching-policy)
7. [Event Types](#7-event-types)
8. [Constants Reference](#8-constants-reference)
9. [File Structure](#9-file-structure)
10. [Implementation & Deployment Guide](#10-implementation--deployment-guide)
11. [Extended Scenarios — Technical Detail](#11-extended-scenarios--technical-detail)
12. [Key Improvements Over Legacy](#12-key-improvements-over-legacy)

---

## 1. Architecture Overview

```
HTTP Request
    ↓
MMSL / FastEndpoints
  GetStockReservation, SaveStockReservation, ... (15 endpoints)
    ↓ LoginDTO + Parameters DTO
MMBLL / StockReservationBLL
  Validation → Business logic → Retry loop → Event publish → Cache invalidate
    ↓ StockReservationDTO / operation-specific DTOs
MMDAL / StockReservationDAL → IQueryExecutor → Dapper
    ↓ SQL (StockReservationQB constants)
SQL Server
  TRESERVATION, TREASSIGNMENTLOG, TSTOCKPOSITION (modified), TPENDINGALLOCATION (existing)
```

**Layer rules:**
- MMSL calls MMBLL only — never MMDAL directly.
- MMBLL contains all validation, retry logic, event publish, cache invalidation.
- MMDAL is pure data access — no business rules.
- All SQL lives in `StockReservationQB.cs` as `public const string` constants.
- DI is automatic via assembly scanning — no `Program.cs` changes needed for new classes.

---

## 2. Database Schema

### TRESERVATION

| Column | Type | Nullable | Default | Notes |
|---|---|---|---|---|
| STOCKRESERVATIONID | INT | NO | — | PK; from AutoNumber |
| TENANTID | INT | NO | — | = loginDTO.ClientId on every row |
| RESERVATIONMODE | TINYINT | NO | 3 | 1=Planning, 2=PostPlanning, 3=Standalone, 4=AutoLinked, 5=MatrixActivation, 6=SubconReturn |
| RESERVATIONSTATUS | TINYINT | NO | 2 | 1=Tentative, 2=Confirmed, 3=Released, 4=Reassigned, 5=FreeTracked, 6=PartiallyFulfilled, 7=FullyFulfilled, 8=SubcontractingOut |
| RESERVATIONSOURCE | TINYINT | NO | 0 | 0=Stock, 1=Document |
| ASSIGNMENTLEVEL | TINYINT | NO | 2 | 1=Project, 2=Document, 3=FG, 4=SubItem, 5=RM |
| OUID | INT | NO | — | Organizational Unit |
| PERIODID | INT | YES | NULL | Financial period |
| RESERVATIONNUMBER | NVARCHAR(50) | YES | NULL | Auto-generated: `SR/YYYYMM/NNNNN` |
| RESERVATIONDATE | DATE | YES | NULL | Must be within open period |
| FOROBJECTHEADERTYPEID | INT | YES | NULL | Document type ID of the demand header |
| FOROBJECTHEADERID | INT | YES | NULL | Document header ID of the demand (e.g., SO header) |
| FOROBJECTTYPEID | INT | YES | NULL | Document type ID of the demand line |
| FOROBJECTID | INT | YES | NULL | Document line ID of the demand |
| FORALLOTEDALLOCATIONID | INT | YES | NULL | Allocation ID on the demand side |
| FORBIZTRANSACTIONTYPEID | INT | YES | NULL | BizTransaction type of the demand |
| FORDATE | DATE | YES | NULL | Required date on the demand |
| FORSLNO | INT | YES | NULL | Serial line number on demand document |
| ITEMID | INT | NO | — | Item being reserved |
| SKUID | INT | YES | NULL | SKU/variant |
| LOTID | INT | YES | NULL | Lot/batch; required when ISLOTLOCKED=1 |
| PACKID | INT | YES | NULL | Pack |
| STOREID | INT | YES | NULL | Store (stock source). NULL when source=Document |
| FROMOBJECTHEADERTYPEID | INT | YES | NULL | Document type ID of supply header |
| FROMOBJECTHEADERID | INT | YES | NULL | Supply document header ID (e.g., PO header) |
| FROMOBJECTTYPEID | INT | YES | NULL | Document type ID of supply line |
| FROMOBJECTID | INT | YES | NULL | Supply document line ID |
| FROMALLOTEDALLOCATIONID | INT | YES | NULL | Allocation ID on supply side (used for pipeline pending) |
| FROMBIZTRANSACTIONTYPEID | INT | YES | NULL | BizTransaction type of supply |
| RESERVEDQUANTITY | DECIMAL(18,4) | NO | 0 | Total quantity reserved |
| ISSUEDQUANTITY | DECIMAL(18,4) | NO | 0 | Quantity already issued/dispatched |
| MRPRUNID | INT | YES | NULL | MRP run that created this row (Planning/PostPlanning mode) |
| MRPRUNDETAILID | INT | YES | NULL | MRP run detail line |
| ISLOTLOCKED | BIT | NO | 0 | 1 = only LOTID above can be issued; no lot substitution |
| ISFREETRACKED | BIT | NO | 0 | 1 = demand is Free-Tracked |
| FREETRACKREASON | NVARCHAR(500) | YES | NULL | |
| FREETRACKEDON | DATETIME2 | YES | NULL | |
| FREETRACKBYID | INT | YES | NULL | |
| ISAUTOLINKED | BIT | NO | 0 | 1 = created by back-to-back PO auto-link |
| LINKEDPOTYPEID | INT | YES | NULL | Entity type of linked PO |
| LINKEDPOID | INT | YES | NULL | Linked PO document ID |
| SUBCONOBJECTHEADERTYPEID | INT | YES | NULL | Job Work document entity type |
| SUBCONOBJECTHEADERID | INT | YES | NULL | Job Work document ID |
| SUBCONLOCATION | NVARCHAR(200) | YES | NULL | Vendor name/location (denormalized) |
| SUBCONSENTOUT | DATETIME2 | YES | NULL | When material left the store |
| SUBCONEXPECTEDRETURN | DATE | YES | NULL | Expected return date |
| ISDELETED | BIT | NO | 0 | Soft delete flag |
| DELETEDON | DATETIME2 | YES | NULL | |
| DELETEDBYID | INT | YES | NULL | |
| DELETEREASON | NVARCHAR(500) | YES | NULL | |
| VERSION | SMALLINT | NO | 1 | Optimistic concurrency — checked on every UPDATE |
| CREATEDBYID | INT | NO | — | |
| CREATEDON | DATETIME2 | NO | GETUTCDATE() | |
| MODIFIEDBYID | INT | NO | — | |
| MODIFIEDON | DATETIME2 | NO | GETUTCDATE() | |

**Check constraint:** `ISSUEDQUANTITY <= RESERVEDQUANTITY` (balance cannot go negative)

**Indexes:**
- `IX_TRESERVATION_TENANT_STATUS` — (TENANTID, RESERVATIONSTATUS, ISDELETED)
- `IX_TRESERVATION_FOR_DEMAND` — (TENANTID, FOROBJECTHEADERID, FOROBJECTTYPEID, FOROBJECTID, ISDELETED)
- `IX_TRESERVATION_FROM_STOCK` — (TENANTID, STOREID, ITEMID, SKUID, ISDELETED)
- `IX_TRESERVATION_ITEM` — (TENANTID, ITEMID, SKUID, RESERVATIONSTATUS, ISDELETED)
- `IX_TRESERVATION_MRP` — (TENANTID, MRPRUNID, ISDELETED)
- `IX_TRESERVATION_SUBCON` — (TENANTID, SUBCONOBJECTHEADERID, RESERVATIONSTATUS, ISDELETED)

---

### TREASSIGNMENTLOG

| Column | Type | Nullable | Notes |
|---|---|---|---|
| REASSIGNMENTLOGID | INT | NO | PK; from AutoNumber (REASSIGNMENTLOG series) |
| TENANTID | INT | NO | |
| ORIGINALRESERVATIONID | INT | NO | → TRESERVATION |
| ORIGFOROBJECTHEADERTYPEID | INT | YES | Captured at time of reassignment |
| ORIGFOROBJECTHEADERID | INT | YES | |
| ORIGFOROBJECTTYPEID | INT | YES | |
| ORIGFOROBJECTID | INT | YES | |
| NEWRESERVATIONID | INT | NO | → TRESERVATION |
| NEWFOROBJECTHEADERTYPEID | INT | YES | |
| NEWFOROBJECTHEADERID | INT | YES | |
| NEWFOROBJECTTYPEID | INT | YES | |
| NEWFOROBJECTID | INT | YES | |
| REASSIGNEDQUANTITY | DECIMAL(18,4) | NO | |
| REASON | NVARCHAR(1000) | NO | Mandatory |
| REASSIGNEDBYID | INT | NO | |
| REASSIGNEDON | DATETIME2 | NO | GETUTCDATE() |

**Indexes:**
- `IX_TREASSIGNLOG_ORIG` — (TENANTID, ORIGINALRESERVATIONID)
- `IX_TREASSIGNLOG_NEW` — (TENANTID, NEWRESERVATIONID)

---

### TSTOCKPOSITION (modified — new columns added)

| Column | Type | Notes |
|---|---|---|
| RESERVEDQUANTITY | DECIMAL(18,4) | Sum of active stock-source reservations for this item/store |
| PIPELINERESERVEDQTY | DECIMAL(18,4) | Sum of active document-source reservations |

Available quantity = `QUANTITY - RESERVEDQUANTITY`

---

### Quantity Rules

```
Stock Source Reservation:
  On Reserve:    TSTOCKPOSITION.RESERVEDQUANTITY += qty
  On Unreserve:  TSTOCKPOSITION.RESERVEDQUANTITY -= qty
  Guard:         INCREMENT query has: AND (QUANTITY - RESERVEDQUANTITY) >= @Quantity
                 Returns 0 rows if insufficient → triggers retry loop

Document Source Reservation:
  On Reserve:    TPENDINGALLOCATION.PENDINGQUANTITY -= qty (supply-side allocation)
  On Unreserve:  TPENDINGALLOCATION.PENDINGQUANTITY += qty

Balance:
  BALANCEQUANTITY = RESERVEDQUANTITY - ISSUEDQUANTITY
  Unreservable    = BALANCEQUANTITY (issued portion is locked)
```

---

## 3. API Reference — All Endpoints

All endpoints are in the `MMSL` project under `EndPoints/StockReservation/`.
Base route: `/StockReservation/`
Auth: `AllowAnonymous` on all (LoginDTO parsed from request header by `BaseEndpoint`)

### GET Endpoints

| Route | Query Params | BLL Method | Cache |
|---|---|---|---|
| `GET /StockReservation/GetStockReservation` | `StockReservationId` (int) | `GetReservationAsync` | `StockReservation:{ClientId}:{Id}` (CLIENT_LEVEL) |
| `GET /StockReservation/GetStockReservationList` | `OUId` (int), `StatusFilter` (byte?), `ItemId` (int?), `Page` (int), `Size` (int) | `GetReservationListAsync` | null (NOT_REQUIRED) |
| `GET /StockReservation/GetStockReservationsByMRPRun` | `MrpRunId` (int) | `GetReservationsByMrpRunAsync` | null (NOT_REQUIRED) |
| `GET /StockReservation/GetAvailabilityForReservation` | `OUId` (int), `ItemId` (int), `SKUId` (int) | `GetAvailabilityForReservationAsync` | null (NOT_REQUIRED — live stock) |
| `GET /StockReservation/GetReassignmentLog` | `StockReservationId` (int) | `GetReassignmentLogAsync` | null (NOT_REQUIRED) |
| `GET /StockReservation/GetSubconTracking` | `OUId` (int) | `GetSubconTrackingReportAsync` | null (NOT_REQUIRED) |

### POST Endpoints

| Route | Request Body | BLL Method | Success Response |
|---|---|---|---|
| `POST /StockReservation/SaveStockReservation` | `StockReservationDTO` | `SaveReservationAsync` | `"Details have been saved successfully with id = {id}"` |
| `POST /StockReservation/UnreserveStockReservation` | `UnreserveDTO` (Id, Version, Reason) | `UnreserveAsync` | `"Details have been deleted successfully"` |
| `POST /StockReservation/FreeTrackDemand` | `StockReservationFreeTrackDTO` | `FreeTrackDemandAsync` | `"Details Saved Successfully"` |
| `POST /StockReservation/ReassignReservation` | `StockReservationReassignDTO` | `ReassignReservationAsync` | `"Details Saved Successfully"` |
| `POST /StockReservation/AutoReserveForMRPRun` | `AutoReserveRequestDTO` | `AutoReserveForMrpRunAsync` | JSON: `AutoReserveResultDTO` |
| `POST /StockReservation/ConfirmTentativeReservations` | Body: `{ MrpRunId }` | `ConfirmTentativeReservationsAsync` | `"Details Updated Successfully"` |
| `POST /StockReservation/ConfirmReservation` | Body: `{ StockReservationId }` | `ConfirmReservationAsync` | `"Details Updated Successfully"` |
| `POST /StockReservation/MarkSubconOut` | `StockReservationSubconDTO` | `MarkSubconOutAsync` | `"Details Saved Successfully"` |
| `POST /StockReservation/MarkSubconReturned` | Body: `{ StockReservationId, Version, ActualReturnedQty }` | `MarkSubconReturnedAsync` | `"Details Updated Successfully"` |

### Key DTO Shapes

**StockReservationDTO (save request):**
```json
{
  "StockReservationId": 0,
  "OUId": 1,
  "ReservationMode": 3,
  "ReservationStatus": 2,
  "ReservationSource": 0,
  "AssignmentLevel": 2,
  "ReservationDate": "2026-06-10",
  "ForObjectHeaderTypeId": 123,
  "ForObjectHeaderId": 456,
  "ForObjectTypeId": 789,
  "ForObjectId": 101,
  "ItemId": 55,
  "SKUId": 1,
  "StoreId": 3,
  "ReservedQuantity": 500.0000
}
```

**UnreserveDTO:**
```json
{
  "StockReservationId": 42,
  "Version": 1,
  "Reason": "Order cancelled",
  "ReleaseQuantity": null
}
```
Set `ReleaseQuantity` to a positive decimal for partial unreserve; leave null for full unreserve.

**StockReservationReassignDTO:**
```json
{
  "OriginalReservationId": 42,
  "ReassignQuantity": 500.0,
  "Reason": "Priority escalation",
  "NewForObjectHeaderTypeId": 123,
  "NewForObjectHeaderId": 456,
  "NewForObjectTypeId": 789,
  "NewForObjectId": 999
}
```

**AutoReserveRequestDTO:**
```json
{
  "MrpRunId": 7,
  "OUId": 1,
  "Mode": 1,
  "FallbackToDocument": false
}
```

**AutoReserveResultDTO (response):**
```json
{
  "MrpRunId": 7,
  "TotalDemands": 50,
  "FullyReservedCount": 42,
  "PartiallyReservedCount": 5,
  "NotReservedCount": 3,
  "Shortages": [
    {
      "ItemId": 55,
      "ItemCode": "BRG-6205",
      "ItemName": "Bearing 6205",
      "ForObjectId": 1001,
      "RequiredQuantity": 80.0,
      "ShortageQuantity": 80.0
    }
  ]
}
```

---

## 4. BLL Validation Rules & Error Messages

All validation throws are in `StockReservationBLL.cs`. These are the exact exception messages returned to the caller.

### SaveReservation

| Condition | Exception Type | Message |
|---|---|---|
| ForObjectHeaderId is null or 0 | ArgumentException | `"ForObjectHeaderId is required."` |
| ForObjectId is null or 0 | ArgumentException | `"ForObjectId is required."` |
| ItemId ≤ 0 | ArgumentException | `"ItemId is required."` |
| ReservedQuantity ≤ 0 | ArgumentException | `"ReservedQuantity must be greater than zero."` |
| ReservationMode = Planning (1) | InvalidOperationException | `"Planning-mode reservations are created by AutoReserve only."` |
| Duplicate active reservation exists | InvalidOperationException | `"An active reservation already exists for this demand and item."` |
| Stock insufficient after MaxRetries (3) | InvalidOperationException | `"Insufficient available quantity."` |

### Unreserve

| Condition | Exception Type | Message |
|---|---|---|
| StockReservationId ≤ 0 | ArgumentException | `"StockReservationId is required."` |
| Reason is empty | ArgumentException | `"Reason is required for unreserve."` |
| Reservation row not found | InvalidOperationException | `"Reservation not found."` |
| ReleaseQuantity ≤ 0 | ArgumentException | `"Release quantity must be greater than zero."` |
| ReleaseQuantity > BalanceQuantity | InvalidOperationException | `"Release quantity exceeds balance (issued portion cannot be unreserved)."` |
| VERSION mismatch (concurrent edit) | InvalidOperationException | `"Concurrent update detected. Please reload and try again."` |

### FreeTrackDemand

| Condition | Exception Type | Message |
|---|---|---|
| ForObjectHeaderId ≤ 0 | ArgumentException | `"ForObjectHeaderId is required."` |
| ItemId ≤ 0 | ArgumentException | `"ItemId is required."` |
| Reason is empty | ArgumentException | `"Reason is required."` |
| Active confirmed reservation exists | InvalidOperationException | `"Unreserve the existing reservation before marking as Free-Tracked."` |

### ReassignReservation

| Condition | Exception Type | Message |
|---|---|---|
| OriginalReservationId ≤ 0 | ArgumentException | `"OriginalReservationId is required."` |
| ReassignQuantity ≤ 0 | ArgumentException | `"ReassignQuantity must be greater than zero."` |
| Reason is empty | ArgumentException | `"Reason is required."` |
| NewForObjectId is null or 0 | ArgumentException | `"New demand target is required."` |
| Original reservation not found | InvalidOperationException | `"Original reservation not found."` |
| Status ≠ Confirmed | InvalidOperationException | `"Only Confirmed reservations can be reassigned."` |
| ReassignQuantity > BalanceQuantity | InvalidOperationException | `"Reassign quantity exceeds balance quantity."` |
| NewForObjectId = original ForObjectId | InvalidOperationException | `"New demand target must differ from original."` |
| VERSION mismatch (concurrent edit) | InvalidOperationException | `"Concurrent update detected. Please reload and try again."` |

### AutoReserveForMrpRun

| Condition | Exception Type | Message |
|---|---|---|
| MrpRunId ≤ 0 | ArgumentException | `"MrpRunId is required."` |

### ConfirmTentativeReservations

| Condition | Exception Type | Message |
|---|---|---|
| MrpRunId ≤ 0 | ArgumentException | `"MrpRunId is required."` |

### ConfirmReservation (single)

| Condition | Exception Type | Message |
|---|---|---|
| StockReservationId ≤ 0 | ArgumentException | `"StockReservationId is required."` |
| Reservation not Tentative or not found | InvalidOperationException | `"Reservation is not in Tentative status or does not exist."` |

### MarkSubconOut

| Condition | Exception Type | Message |
|---|---|---|
| StockReservationId ≤ 0 | ArgumentException | `"StockReservationId is required."` |
| SubconObjectHeaderId ≤ 0 | ArgumentException | `"SubconObjectHeaderId is required."` |
| SubconLocation empty | ArgumentException | `"SubconLocation is required."` |
| VERSION mismatch or not found | InvalidOperationException | `"Concurrent update detected or reservation not found."` |

---

## 5. BLL Operation Flows

### SaveReservation

```
1. Set dto.TenantId = loginDTO.ClientId
2. Validate fields (throw on first failure)
3. CheckDuplicateActive (DAL scalar query)
4. Get AutoNumber for StockReservationId and ReservationNumber
5. for attempt = 0 to MaxRetries-1:
   a. BeginTransaction
   b. InsertReservationAsync
   c. If Source=Stock:  IncrementStockReservedAsync  → returns 0 if qty insufficient
      If Source=Doc:    DecrementPipelinePendingAsync → returns 0 if qty insufficient
   d. If updated=0 and attempt < MaxRetries-1: RollbackAsync, continue
   e. If updated=0 and attempt = MaxRetries-1: throw "Insufficient available quantity."
   f. CommitAsync, break
6. GB5Trace.Step("event-publish")
7. InvalidateReservationCacheAsync (StockReservation + StockPosition keys)
```

### Unreserve (Full or Partial)

```
1. Validate inputs
2. Load existing reservation
3. Compute releaseQty = dto.ReleaseQuantity ?? existing.BalanceQuantity
4. Validate releaseQty ≤ BalanceQuantity
5. BeginTransaction
6. If partial: ReduceReservedQuantityAsync  (VERSION checked — returns 0 on mismatch)
   If full:    ReleaseReservationAsync      (VERSION checked)
7. If updated=0: RollbackAsync, throw concurrency error
8. If Source=Stock: DecrementStockReservedAsync
   If Source=Doc:   IncrementPipelinePendingAsync
9. CommitAsync
10. Event publish + cache invalidate
```

### Reassign

```
1. Validate inputs; load original reservation
2. Validate: Confirmed status, ReassignQty ≤ BalanceQty, NewObjectId ≠ OriginalObjectId
3. BeginTransaction
4. If fullReassign: MarkReassignedAsync (VERSION checked)
   If partial:      ReduceReservedQuantityAsync (VERSION checked)
5. If updated=0: throw concurrency error
6. Clone StockReservationDTO with new FOR side, get new AutoNumber
7. InsertReservationAsync for new reservation
8. Get AutoNumber for ReassignmentLogId
9. InsertReassignmentLogAsync
10. CommitAsync
11. Note: NO TSTOCKPOSITION or TPENDINGALLOCATION changes — supply side unchanged
12. Event publish + cache invalidate
```

### AutoReserveForMrpRun

```
1. Load demand lines for MRP run
2. For each demand line:
   a. GetStockAvailabilityAsync (live stock per store)
   b. Allocate greedily (largest store first) across stores
   c. Accumulate StockReservationDTO batch
   d. When batch ≥ 100: FlushBatchAsync (BulkInsertAsync + stock update loop)
3. Build AutoReserveResultDTO (Fully/Partially/NotReserved counts + Shortages)
4. One event publish per MRP run (not per row)
5. Cache invalidate
```

### MarkSubconOut

```
1. Validate StockReservationId, SubconObjectHeaderId, SubconLocation
2. BeginTransaction
3. MarkSubconOutAsync — sets RESERVATIONSTATUS=8, populates SUBCON* columns
   VERSION checked — returns 0 on mismatch
4. CommitAsync
5. Event publish (SUBCONOUTEVENTTYPEID)
6. Cache invalidate
```

---

## 6. Caching Policy

| Endpoint | GetCacheKey Return | Level |
|---|---|---|
| GetStockReservation (single) | `"StockReservation:{ClientId}:{StockReservationId}"` | CLIENT_LEVEL |
| GetStockReservationList | `null` | NOT_REQUIRED |
| GetStockReservationsByMRPRun | `null` | NOT_REQUIRED |
| GetAvailabilityForReservation | `null` | NOT_REQUIRED (always live) |
| GetReassignmentLog | `null` | NOT_REQUIRED |
| GetSubconTracking | `null` | NOT_REQUIRED |
| All POST endpoints | `null` | NOT_REQUIRED |

**Invalidation** (called after every write in BLL):
```csharp
private async Task InvalidateReservationCacheAsync(LoginDTO loginDTO)
{
    _keyInvalidate.AllInvalidateCache($"StockReservation:{loginDTO.ClientId}");
    _keyInvalidate.AllInvalidateCache($"StockPosition:{loginDTO.ClientId}");
}
```

---

## 7. Event Types

Defined in `GB5Shared/GB5Constant/Constant.cs` under `EventTypeConstant`:

| Constant | ID | Description | Category | Audit | Retention |
|---|---|---|---|---|---|
| `SAVERESERVATIONEVENTTYPEID` | -1799996001 | Stock Reservation Created/Updated | StockReservation | Yes | 365 days |
| `DELETERESERVATIONEVENTTYPEID` | -1799996002 | Stock Reservation Released | StockReservation | Yes | 365 days |
| `SAVEAUTORESERVATIONEVENTTYPEID` | -1799996003 | Auto Reservation from MRP Run | StockReservation | Yes | 365 days |
| `CONFIRMRESERVATIONEVENTTYPEID` | -1799996004 | Reservation Confirmed (post-planning) | StockReservation | Yes | 365 days |
| `SAVEFREETRACKEVNT` | -1799996005 | Demand Free-Tracked | StockReservation | Yes | 365 days |
| `SAVEREASSIGNMENTEVENTTYPEID` | -1799996006 | Reservation Reassigned | StockReservation | Yes | 365 days |
| `AUTOLINKRESERVATIONEVENTTYPEID` | -1799996007 | Auto-Reserved from Linked PO | StockReservation | Yes | 365 days |
| `SUBCONOUTEVENTTYPEID` | -1799996008 | Material Dispatched to Subcontractor | StockReservation | Yes | 365 days |
| `SUBCONRETURNEVENTTYPEID` | -1799996009 | Material Returned from Subcontractor | StockReservation | Yes | 365 days |

All 9 seed rows are in `DB/Migrations/20260609_StockReservation_Schema.sql`.

---

## 8. Constants Reference

Defined in `GB5Shared/GB5Constant/Constant.cs`:

### AUTONUMBERCONSTANT

| Constant | String Value | Used By |
|---|---|---|
| `AUTONUMBERCONSTANT.STOCKRESERVATION` | `"STOCKRESERVATION"` | `SaveReservationAsync`, `FreeTrackDemandAsync`, `ReassignReservationAsync` |
| `AUTONUMBERCONSTANT.REASSIGNMENTLOG` | `"REASSIGNMENTLOG"` | `ReassignReservationAsync` |

### Reservation Number Format

| Operation | Format | Example |
|---|---|---|
| Regular / MRP / Matrix | `SR/{yyyyMM}/{StartNumber:D5}` | `SR/202606/00042` |
| Free-Track | `FT/{yyyyMM}/{StartNumber:D5}` | `FT/202606/00043` |
| Reassigned (new leg) | `SR/{yyyyMM}/{StartNumber:D5}` | `SR/202606/00047` |

### Enum Values

**ReservationMode (byte):**

| Value | Name | When Used |
|---|---|---|
| 1 | Planning | MRP auto-reserve, tentative |
| 2 | PostPlanning | Confirmed from MRP output |
| 3 | Standalone | Manual ad-hoc (default) |
| 4 | AutoLinked | Auto-created from back-to-back PO |
| 5 | MatrixActivation | Auto-created on Matrix activation |
| 6 | SubconReturn | Created on subcontract return |

**ReservationStatus (byte):**

| Value | Name | When Set |
|---|---|---|
| 1 | Tentative | MRP AutoReserve |
| 2 | Confirmed | SaveReservation, ConfirmReservation |
| 3 | Released | UnreserveAsync (full) |
| 4 | Reassigned | ReassignReservationAsync (original side) |
| 5 | FreeTracked | FreeTrackDemandAsync |
| 6 | PartiallyFulfilled | UpdateIssuedQuantityAsync (partial) |
| 7 | FullyFulfilled | UpdateIssuedQuantityAsync (complete) |
| 8 | SubcontractingOut | MarkSubconOutAsync |

**ReservationSource (byte):**

| Value | Name | Notes |
|---|---|---|
| 0 | Stock | Physical stock from TSTOCKPOSITION |
| 1 | Document | Pipeline from TPENDINGALLOCATION |

**AssignmentLevel (byte):**

| Value | Name |
|---|---|
| 1 | Project |
| 2 | Document |
| 3 | FG |
| 4 | SubItem |
| 5 | RM |

---

## 9. File Structure

```
GB5Solution/
├── MM/
│   ├── MMDAL/
│   │   ├── DTO/StockReservation/
│   │   │   ├── StockReservationDTO.cs          — Main entity DTO
│   │   │   ├── StockReservationEnums.cs        — All 4 enums
│   │   │   ├── StockReservationListDTO.cs      — Paged list row
│   │   │   ├── ReservationAvailabilityDTO.cs   — Stock/pipeline availability
│   │   │   ├── StockReservationReassignDTO.cs  — Reassign input + ReassignmentLogDTO
│   │   │   ├── StockReservationFreeTrackDTO.cs — Free-track input
│   │   │   ├── AutoReserveDTO.cs               — AutoReserveRequestDTO + ResultDTO + UnreserveDTO
│   │   │   └── StockReservationSubconDTO.cs    — SubconDTO + SubconReturnDTO + SubconTrackingReportDTO
│   │   ├── Query/StockReservation/
│   │   │   └── StockReservationQB.cs           — All SQL constants
│   │   └── CustomCode/StockReservation/
│   │       ├── IStockReservationDAL.cs         — 20 method signatures
│   │       └── StockReservationDAL.cs          — Dapper implementations
│   ├── MMBLL/StockReservation/
│   │   ├── IStockReservationBLL.cs             — 13 method signatures
│   │   └── StockReservationBLL.cs              — Business logic, retry, events
│   └── MMSL/EndPoints/StockReservation/
│       ├── GetStockReservation.cs
│       ├── GetStockReservationList.cs
│       ├── GetStockReservationsByMRPRun.cs
│       ├── GetAvailabilityForReservation.cs
│       ├── GetReassignmentLog.cs
│       ├── GetSubconTracking.cs
│       ├── SaveStockReservation.cs
│       ├── UnreserveStockReservation.cs
│       ├── FreeTrackDemand.cs
│       ├── ReassignReservation.cs
│       ├── AutoReserveForMRPRun.cs
│       ├── ConfirmTentativeReservations.cs
│       ├── ConfirmReservation.cs
│       ├── MarkSubconOut.cs
│       └── MarkSubconReturned.cs
├── GB5Shared/GB5Constant/Constant.cs           — EventTypeConstant + AUTONUMBERCONSTANT additions
└── DB/Migrations/
    └── 20260609_StockReservation_Schema.sql    — Full schema + indexes + seed data
```

---

## 10. Implementation & Deployment Guide

### Pre-Requisites

| Item | Required State |
|---|---|
| TPENDINGALLOCATION populated (Allocation module) | Live — pipeline reservation reads pending allocation |
| TSTOCKPOSITION populated (Stock module) | Live |
| MRP module live | Required for Planning-mode auto-reservation |
| Auto-number series for STOCKRESERVATION and REASSIGNMENTLOG | Configured before go-live |
| MEVENTTYPE seed rows (9 rows) | Run via migration script |

### Migration Sequence

```sql
-- 1. Run DB migration script
--    DB/Migrations/20260609_StockReservation_Schema.sql
--    Creates: TRESERVATION, TREASSIGNMENTLOG
--    Alters:  TSTOCKPOSITION (adds RESERVEDQUANTITY, PIPELINERESERVEDQTY)
--    Inserts: 9 MEVENTTYPE seed rows

-- 2. Backfill TSTOCKPOSITION.RESERVEDQUANTITY (if migrating from legacy)
UPDATE sp
SET    sp.RESERVEDQUANTITY = ISNULL(agg.TotalReserved, 0)
FROM   TSTOCKPOSITION sp
LEFT JOIN (
    SELECT STOREID, ITEMID, SKUID, SUM(RESERVEDQUANTITY) AS TotalReserved
    FROM   TRESERVATION
    WHERE  RESERVATIONSOURCE = 0   -- Stock
      AND  RESERVATIONSTATUS IN (2, 6, 7, 8)  -- active statuses
      AND  ISDELETED = 0
    GROUP  BY STOREID, ITEMID, SKUID
) agg ON agg.STOREID = sp.STOREID
      AND agg.ITEMID  = sp.ITEMID
      AND agg.SKUID   = sp.SKUID

-- 3. Configure auto-number series
--    Admin → Auto-Number Master → Add: STOCKRESERVATION, REASSIGNMENTLOG

-- 4. Assign roles and access

-- 5. Deploy application build
```

### Rollback Plan

- Migration is **additive only** — new tables, new columns (all defaulted to 0/NULL).
- No changes to existing table columns.
- TRESERVATION and TREASSIGNMENTLOG can be dropped without affecting other modules.
- TSTOCKPOSITION new columns have `DEFAULT 0` — reverting deployment = instant safe rollback.

### Go-Live Checklist

- [ ] DB migration script run on production
- [ ] TSTOCKPOSITION.RESERVEDQUANTITY backfilled (if migrating legacy data)
- [ ] Auto-number series STOCKRESERVATION and REASSIGNMENTLOG configured
- [ ] BizTransactionType reservation natures configured (demand vs supply)
- [ ] Roles assigned to planning/materials/sales users
- [ ] Post-MRP auto-reserve tested on staging with production data copy
- [ ] Reconciliation query verified: TSTOCKPOSITION.RESERVEDQUANTITY = sum of active reservations
- [ ] Multi-tenant isolation verified: reservations from TenantA invisible in TenantB queries

---

## 11. Extended Scenarios — Technical Detail

### Lot/Batch-Level Reservation (Matrix)

- Triggered when a Matrix is activated with `TMATRIXINPUT.LotId` populated.
- Creates `TRESERVATION` with `ISLOTLOCKED=1` and `LOTID=<specific lot>`.
- Issue screen for that Matrix must filter: `WHERE LOTID = TRESERVATION.LOTID` — no other lots selectable.
- Any availability query must filter out lot-locked quantity from general availability.
- Mode: `MatrixActivation` (5).

**Data impact:** `ISLOTLOCKED=1` + `LOTID` populated on TRESERVATION. No other table changes beyond the standard stock reserve.

---

### Back-to-Back / Auto-Linked PO Reservation

- Triggered when a PO is created directly from a SO line and `AutoReserveOnLinkedPO` config is enabled.
- Creates `TRESERVATION` with `ISAUTOLINKED=1`, `LINKEDPOTYPEID`, `LINKEDPOID` populated.
- Source: Document (pipeline). Mode: AutoLinked (4). Status: Confirmed.
- On PO goods receipt: a new stock-source reservation is created atomically, and the pipeline reservation is Released — within the same goods receipt transaction.

**Pipeline-to-Stock conversion on GR:**
```
Goods Receipt of PO-2026-200:
  1. Standard stock position update (TSTOCKPOSITION.QUANTITY += received qty)
  2. Detect TRESERVATION with ISAUTOLINKED=1 and FROMOBJECTHEADERID=PO-2026-200
  3. Create new stock reservation (Source=Stock, Status=Confirmed)
  4. Release old pipeline reservation (Status=Released, ISDELETED=1)
  All within GR transaction.
```

---

### Subcontracting Material Tracking

- On Job Work dispatch: `TRESERVATION.RESERVATIONSTATUS` → 8 (SubcontractingOut).
- Populates: `SUBCONOBJECTHEADERTYPEID`, `SUBCONOBJECTHEADERID`, `SUBCONLOCATION`, `SUBCONSENTOUT`, `SUBCONEXPECTEDRETURN`.
- Material does NOT appear in stock availability while SubcontractingOut.
- On return: `MarkSubconReturnedAsync` — updates `RESERVEDQUANTITY` to actual returned qty, status returns to Confirmed.
- Partial return: split reservation — returned qty → Confirmed, remaining → still SubcontractingOut.
- Subcon Tracking Report: `GET /StockReservation/GetSubconTracking?OUId=1` — shows all active SubcontractingOut rows with overdue flag.

---

## 12. Key Improvements Over Legacy

| Issue | Legacy System | GB5 System |
|---|---|---|
| Quantity data type | `double` — precision errors | `DECIMAL(18,4)` |
| Business logic location | SQL stored procedures | BLL layer — testable, observable |
| Reservation status | Opaque byte, undocumented | 8 semantic enum values |
| Reservation mode | Not tracked | 6 modes (Planning → SubconReturn) |
| Delete behaviour | Hard delete — audit lost | Soft delete — status=Released, ISDELETED=1 |
| Reassignment tracking | None | TREASSIGNMENTLOG full chain |
| Free-track support | None | First-class status (FreeTracked) |
| MRP integration | Manual only | Auto-reserve with Tentative/Confirm flow |
| SQL parameters | String replacement — injection risk | `@ParameterName` Dapper parameterized |
| Observability | None | GB5Trace on every BLL operation |
| Audit log | None | EventLogPublish (Dapr pub/sub) on every write |
| Concurrency | No protection | Optimistic concurrency VERSION field + retry (MaxRetries=3) |
| Lot-level reservation | Not supported | ISLOTLOCKED — only reserved lot can be issued |
| B2B PO auto-reservation | Manual only | Configurable auto-reserve on PO creation from SO |
| Subcontracting tracking | Not tracked | Reservation follows material to/from job work |
| Empty legacy DTOs | 4 empty stub DTOs | All 8 DTOs fully implemented |