Docs
AI Docs
DPR
Revised Plan: Eliminating Duplicate Production Plan TRN IDs

Revised Plan: Eliminating Duplicate Production Plan TRN IDs

This revised plan details the focused approach for securing PlanTrnId (prefixes 'P' and 'X') using the LastDocumentNumbers sequencing table. We will perform database-level pre-seeding and only modify PlanTrnId insertion routines in the repository imports during this round. No changes will be made to PlanNo or other tables.

1. Simple Recovery Model & Transaction Safety

No changes are required for backup/restore recovery models. SQL Server’s recovery model (Simple vs. Full) only affects transaction log maintenance and truncate/backup behaviors. Atomicity, isolation, and row-level locking (ACID properties) are fully guaranteed in the Simple recovery model. Row locks acquired during transaction execution or update operations behave exactly the same way.

2. Database Migration Plan (Pre-Seeding)

To avoid complex dynamic seeding logic in runtime C# code, a new SQL database migration script will be created to ensure the LastDocumentNumbers table is populated.

If existing records are present, we seed using the MAX identifier from PrdnPlan1. Otherwise, the sequence is initialized to 0.

Migration Script: V0_0_0_0_0_14__Seed_LastDocumentNumbers.sql 

IF EXISTS (SELECT 1 FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = 'LastDocumentNumbers')
BEGIN
    -- Seed for 'P' (Production Plan TRN ID) if not exists
    IF NOT EXISTS (SELECT 1 FROM LastDocumentNumbers WHERE DocType = 'P')
    BEGIN
        INSERT INTO LastDocumentNumbers (DocType, LastNumber, Description)
        SELECT 'P', COALESCE(MAX(CAST(RIGHT(PlanTrnId, 6) AS INT)), 0), 'Production Plan TRN ID'
        FROM PrdnPlan1 WHERE LEFT(PlanTrnId, 1) = 'P';
    END

    -- Seed for 'X' (Scrap TRN ID) if not exists
    IF NOT EXISTS (SELECT 1 FROM LastDocumentNumbers WHERE DocType = 'X')
    BEGIN
        INSERT INTO LastDocumentNumbers (DocType, LastNumber, Description)
        SELECT 'X', COALESCE(MAX(CAST(RIGHT(PlanTrnId, 6) AS INT)), 0), 'Scrap TRN ID'
        FROM PrdnPlan1 WHERE LEFT(PlanTrnId, 1) = 'X';
    END

    -- Seed future types with 0 if not exists (preparing table for later rounds)
    IF NOT EXISTS (SELECT 1 FROM LastDocumentNumbers WHERE DocType = 'B')
        INSERT INTO LastDocumentNumbers (DocType, LastNumber, Description) VALUES ('B', 0, 'Bill TRN ID');

    IF NOT EXISTS (SELECT 1 FROM LastDocumentNumbers WHERE DocType = 'J')
        INSERT INTO LastDocumentNumbers (DocType, LastNumber, Description) VALUES ('J', 0, 'Jobwork TRN ID');

    IF NOT EXISTS (SELECT 1 FROM LastDocumentNumbers WHERE DocType = 'Y')
        INSERT INTO LastDocumentNumbers (DocType, LastNumber, Description) VALUES ('Y', 0, 'Y Type TRN ID');
END

3. Runtime C# Code Design

Since sequences are pre-seeded in the database:

  1. No changes are needed to UtilityMethodsRepository.GetNextDocumentNumber or PrdnPlanController.cs (single-record actions already call GetNextDocumentNumber correctly).
  2. Surgical repository updates will be applied to the bulk import routines inside PrdnPlanRepository.cs.

4. Specific Program Changes

Program 1: PrdnPlanRepository.cs

We will modify the sequence generation queries inside ImportSlittingIssue and ImportTransferPlan.

Change A: ImportSlittingIssue

We will replace the current query declarations:

  • Remove maxTrnIdQuery entirely.
  • Update updateTempPlanTrnId to reserve a block of numbers from LastDocumentNumbers and update the temporary table in a single SQL operation:
        var updateTempPlanTrnId = @"
        DECLARE @Count INT;
        SELECT @Count = COUNT(*) FROM #temp;

        DECLARE @UpdatedTable TABLE (LastNumber INT);

        UPDATE LastDocumentNumbers 
        SET LastNumber = LastNumber + @Count 
        OUTPUT INSERTED.LastNumber INTO @UpdatedTable
        WHERE DocType = 'P';

        DECLARE @LastNumber INT;
        SELECT @LastNumber = LastNumber FROM @UpdatedTable;

        WITH A(rowno, tempid) AS
        (
            SELECT ROW_NUMBER() OVER (ORDER BY id) AS Rowno, id AS tempid FROM #temp
        ),
        B(tempid, plantrnid) AS
        (
            SELECT tempid, FORMAT((@LastNumber - @Count) + A.rowno, '000000') AS plantrnid FROM A
        )
        UPDATE #temp SET #temp.plantrnid = CONCAT('P', B.plantrnid) FROM B 
        WHERE #temp.id = B.tempid;";

In the execution logic of ImportSlittingIssue (lines ~1604-1605):

            connection.Execute(updateTempPlanTrnId);

Change B: ImportTransferPlan

  • Remove maxPlanTrnIdQuery entirely.
  • Update updateTempPlanTrnId (same definition as above).
  • Update secondInsert to atomically reserve a second block of IDs for the MovementType = 'R' inserts directly in the query, eliminating the need to pass @maxplantrnid:
        var secondInsert = @"
        DECLARE @Count INT;
        SELECT @Count = COUNT(*) FROM #temp;

        DECLARE @UpdatedTable TABLE (LastNumber INT);

        UPDATE LastDocumentNumbers 
        SET LastNumber = LastNumber + @Count 
        OUTPUT INSERTED.LastNumber INTO @UpdatedTable
        WHERE DocType = 'P';

        DECLARE @LastNumber INT;
        SELECT @LastNumber = LastNumber FROM @UpdatedTable;

        WITH A(rowno, tempid) AS
        (
            SELECT ROW_NUMBER() OVER (ORDER BY id) AS Rowno, id AS tempid 
            FROM #temp
        ),
        B(tempid, plantrnid) AS
        (
            SELECT tempid, FORMAT((@LastNumber - @Count) + A.rowno, '000000') AS plantrnid FROM A
        ),
        C(itemid, gradeid, widthid, coatingid, wattlossid) AS 
        (
            SELECT TOP 1 itemid, gradeid, widthid, coatingid, wattlossid
            FROM prdnplan1 
            WHERE yearplanno = @yearplanno
            ORDER BY id 
        ),
        D(planno, plantrnid, reftrnid, itemid, gradeid, widthid, coatingid, wattlossid, qty, qty1, 
        MovementType, YearPlanNo, EffectiveWidth, noofslits, partyid) AS
        (
            SELECT @planno, CONCAT('P', B.plantrnid) AS plantrnid,
            billtrnid AS reftrnid, C.itemid, C.gradeid, C.widthid, C.coatingid, C.wattlossid, J1.qty, 
            J1.qty1, @MovementType, @yearPlanNo, W.Coilsize AS EffectiveWidth,
            @noofslits, @transferplanto AS partyid
            FROM #temp T
            LEFT JOIN jobwork1 J1 ON T.billtrnid = J1.trnid
            LEFT JOIN DprWidth W ON J1.widthid = W.Widthid
            LEFT JOIN B ON T.id = B.tempid
            LEFT JOIN C ON 1 = 1
        )
        INSERT INTO PrdnPlan1(planno, plantrnid, reftrnid, itemid, gradeid, widthid, coatingid, wattlossid, qty, qty1, 
        MovementType, YearPlanNo, EffectiveWidth, noofslits, partyid)
        SELECT planno, plantrnid, reftrnid, itemid, gradeid, widthid, coatingid, wattlossid, qty, qty1, 
        MovementType, YearPlanNo, EffectiveWidth, noofslits, partyid
        FROM D;";

In the execution logic of ImportTransferPlan (lines ~1972-1994):

            connection.Execute(updateTempPlanTrnId);
            string? nullValue = null;
            connection.Execute(firstInsert, new
            {
                planno = mainData.PlanNo,
                yearPlanNo = mainData.YearPlanNo,
                noofslits = nullValue,
                movementType = "I"
            });
            
            connection.Execute(secondInsert, new
            {
                yearPlanNo = mainData.YearPlanNo,
                planno = mainData.PlanNo,
                movementType = "R",
                noofslits = "1",
                transferPlanTo
            });
effective width divide by zero fixedStock vs Pending Plan Proposal