Phase 13: Substitute/Denial Tracking

Duration: 1.5 days Goal: Track substitutes, denials, and forced sales for demand analysis

---

Overview

When URCs indent items:

• Substituted: Customer accepts alternative item
• Denied: Item not in stock, customer leaves empty-handed
• Forced Sale: Slow-moving item pushed to customer

This data feeds into PRGO demand forecasting.

---

1. Invoice Item Enhancement

class InvoiceItem(BaseDocType):
    """Enhanced invoice item with S/D/F tracking."""

    # Existing fields...
    item: str
    indented_qty: Decimal  # What customer asked for
    issued_qty: Decimal  # What was given

    # S/D/F Classification
    classification: str | None = None  # Substitute, Denial, ForcedSale, Normal

    # For Substitutes
    original_item: str | None = None  # What they originally wanted

    # Calculated
    denied_qty: Decimal = Decimal("0")  # indented - issued (if no substitute)
    substituted_qty: Decimal = Decimal("0")  # if substitute given
    forced_qty: Decimal = Decimal("0")  # if forced sale

    class Meta:
        table_name = "invoice_item"

---

2. S/D/F Summary DocType

class MSTCSummary(BaseDocType):
    """Monthly Stock Taking Card summary for PRGO."""

    depot: str
    item: str
    period: str  # "2024-01"

    # Stock
    opening_stock: Decimal = Decimal("0")
    closing_stock: Decimal = Decimal("0")
    qty_received: Decimal = Decimal("0")

    # Sales
    qty_indented: Decimal = Decimal("0")  # Total demand
    qty_sold: Decimal = Decimal("0")  # Actual sales

    # S/D/F
    qty_substituted: Decimal = Decimal("0")
    qty_denied: Decimal = Decimal("0")
    qty_forced_sale: Decimal = Decimal("0")

    # Inter-depot
    qty_idt_in: Decimal = Decimal("0")  # Inter-depot transfer in
    qty_idt_out: Decimal = Decimal("0")

    # Other
    qty_other_receipt: Decimal = Decimal("0")
    qty_other_issue: Decimal = Decimal("0")

    class Meta:
        table_name = "mstc_summary"
        unique_together = [("depot", "item", "period")]

---

3. MSTC Aggregation Service

class MSTCAggregator:
    """Aggregate invoice data into MSTC summaries."""

    async def generate_monthly_summary(
        self,
        depot: str,
        period: str,  # "2024-01"
    ) -> list[MSTCSummary]:
        """Generate MSTC from invoice data."""

        start_date = date.fromisoformat(f"{period}-01")
        end_date = (start_date.replace(day=28) + timedelta(days=4)).replace(day=1) - timedelta(days=1)

        # Get all items sold at this depot
        items = await self.get_depot_items(depot)

        summaries = []
        for item in items:
            # Opening stock (closing of previous month)
            prev_summary = await self.get_previous(depot, item, period)
            opening = prev_summary.closing_stock if prev_summary else Decimal("0")

            # Aggregate invoice items
            invoice_data = await self.aggregate_invoices(depot, item, start_date, end_date)

            # Get stock movements
            stock_data = await self.aggregate_stock_entries(depot, item, start_date, end_date)

            # Calculate closing
            closing = (
                opening
                + stock_data["received"]
                + stock_data["idt_in"]
                - invoice_data["sold"]
                - stock_data["idt_out"]
            )

            summary = MSTCSummary(
                depot=depot,
                item=item,
                period=period,
                opening_stock=opening,
                closing_stock=closing,
                qty_received=stock_data["received"],
                qty_indented=invoice_data["indented"],
                qty_sold=invoice_data["sold"],
                qty_substituted=invoice_data["substituted"],
                qty_denied=invoice_data["denied"],
                qty_forced_sale=invoice_data["forced"],
                qty_idt_in=stock_data["idt_in"],
                qty_idt_out=stock_data["idt_out"],
            )
            await self.summary_repo.upsert(summary)
            summaries.append(summary)

        return summaries

    async def aggregate_invoices(
        self,
        depot: str,
        item: str,
        start_date: date,
        end_date: date,
    ) -> dict:
        """Sum invoice item data."""
        result = await self.db.fetch_one("""
            SELECT
                SUM(indented_qty) as indented,
                SUM(issued_qty) as sold,
                SUM(CASE WHEN classification = 'Substitute' THEN issued_qty ELSE 0 END) as substituted,
                SUM(CASE WHEN classification = 'Denial' THEN indented_qty - issued_qty ELSE 0 END) as denied,
                SUM(CASE WHEN classification = 'ForcedSale' THEN issued_qty ELSE 0 END) as forced
            FROM invoice_item ii
            JOIN invoice i ON ii.parent = i.id
            WHERE i.depot = $1 AND ii.item = $2
              AND i.invoice_date BETWEEN $3 AND $4
        """, depot, item, start_date, end_date)
        return dict(result)

---

4. Denial Analysis Report

class DenialAnalysisReport:
    """Analyze denial patterns for demand planning."""

    async def generate(
        self,
        depot: str | None,
        from_period: str,
        to_period: str,
    ) -> list[dict]:
        """
        Denial to Offtake Ratio = Denied / (Sold + Denied)

        High ratio = stock problem
        """
        return await self.db.fetch_all("""
            SELECT
                item,
                depot,
                SUM(qty_sold) as total_sold,
                SUM(qty_denied) as total_denied,
                SUM(qty_denied) / NULLIF(SUM(qty_sold + qty_denied), 0) * 100 as denial_ratio
            FROM mstc_summary
            WHERE period BETWEEN $1 AND $2
              AND ($3 IS NULL OR depot = $3)
            GROUP BY item, depot
            HAVING SUM(qty_denied) > 0
            ORDER BY denial_ratio DESC
        """, from_period, to_period, depot)

---

5. API Endpoints

Endpoint	Method	Description
/api/v1/mstc	GET	List MSTC summaries
/api/v1/mstc/generate	POST	Generate for depot/period
/api/v1/reports/denial-analysis	GET	Denial analysis report
/api/v1/reports/substitution-analysis	GET	Substitution patterns

---

6. Verification Checklist

• Invoice captures S/D/F classification
• MSTC aggregation runs correctly
• Denial ratio calculation correct
• PRGO uses MSTC data
• FSN analysis includes S/D/F