Stock vs Pending Plan Report Proposal (Refined Parameter & Issue-Line Flow)

-- CTE 1: Physical Closing Stock per Item & All Parameters
WITH CTE_PhysicalStock AS (
    SELECT 
        itemid,
        COALESCE(gradeid, '000000') AS gradeid,
        COALESCE(widthid, '000000') AS widthid,
        COALESCE(coatingid, '000000') AS coatingid,
        COALESCE(wattlossid, '000000') AS wattlossid,
        COALESCE(airgapid, '000000') AS airgapid,
        SUM(qty) AS BalanceStockQty,
        SUM(qty1) AS BalanceStockNos
    FROM #DprInventory  -- Generated from physical inventory ledger
    GROUP BY itemid, gradeid, widthid, coatingid, wattlossid, airgapid
),

-- CTE 2: All Active Receipt Lines (MovementType = 'R')
CTE_PlanReceipts AS (
    SELECT 
        P1.PlanTrnId,
        P1.YearPlanNo,
        P1.Qty AS PlanQty,
        P1.Qty1 AS PlanQty1
    FROM PrdnPlan1 P1
    INNER JOIN PrdnPlan P ON P1.YearPlanNo = P.YearPlanNo
    WHERE P1.MovementType = 'R' 
      AND LEFT(P1.PlanTrnId, 1) != 'X'  -- Exclude scrap
      AND NOT EXISTS (SELECT 1 FROM DprShortClose SC WHERE SC.trnid = P1.PlanTrnId)
),

-- CTE 3: Calculate Pending Quantity per PlanTrnId
CTE_PlanTrnPending AS (
    SELECT 
        R.PlanTrnId,
        R.YearPlanNo,
        (R.PlanQty - COALESCE(SUM(L.LinkedQty), 0)) AS PendingQty,
        (R.PlanQty1 - COALESCE(SUM(L.LinkedQty1), 0)) AS PendingQty1
    FROM CTE_PlanReceipts R
    LEFT JOIN LinkPlanToPrdn L ON R.PlanTrnId = L.PlanTrnId
    GROUP BY R.PlanTrnId, R.YearPlanNo, R.PlanQty, R.PlanQty1
),

-- CTE 4: Sum Pending Quantity grouped STRICTLY by YearPlanNo
CTE_YearPlanPending AS (
    SELECT 
        YearPlanNo,
        SUM(PendingQty) AS YearPlanPendingQty,
        SUM(PendingQty1) AS YearPlanPendingQty1
    FROM CTE_PlanTrnPending
    GROUP BY YearPlanNo
    HAVING SUM(PendingQty) > 0
),

-- CTE 5: Bind Issue Line ('I') Parameters & PlanNo to each YearPlanNo
CTE_PlanIssueHeader AS (
    SELECT 
        YP.YearPlanNo,
        P.PlanNo,
        P1.ItemId,
        COALESCE(P1.GradeId, '000000') AS GradeId,
        COALESCE(P1.WidthId, '000000') AS WidthId,
        COALESCE(P1.CoatingId, '000000') AS CoatingId,
        COALESCE(P1.WattLossId, '000000') AS WattLossId,
        COALESCE(P1.AirGapId, '000000') AS AirGapId,
        YP.YearPlanPendingQty,
        YP.YearPlanPendingQty1
    FROM CTE_YearPlanPending YP
    INNER JOIN PrdnPlan P ON YP.YearPlanNo = P.YearPlanNo
    INNER JOIN PrdnPlan1 P1 ON YP.YearPlanNo = P1.YearPlanNo AND P1.MovementType = 'I'
),

-- CTE 6: Aggregate by Parameters and concatenate Plan Numbers into Remark using STRING_AGG
CTE_ParamPendingSummary AS (
    SELECT 
        ItemId, GradeId, WidthId, CoatingId, WattLossId, AirGapId,
        SUM(YearPlanPendingQty) AS TotalPendingPlanQty,
        SUM(YearPlanPendingQty1) AS TotalPendingPlanNos,
        STRING_AGG(RIGHT(PlanNo, 6), ', ') WITHIN GROUP (ORDER BY PlanNo) AS AssociatedPlans
    FROM CTE_PlanIssueHeader
    GROUP BY ItemId, GradeId, WidthId, CoatingId, WattLossId, AirGapId
)

-- Final SELECT: Combine Stock & Plan Pending quantities via FULL OUTER JOIN
SELECT 
    I.ItemName,
    G.GradeName,
    W.WidthName,
    C.CoatingName,
    WL.WattLossName,
    AG.AirGapName,
    COALESCE(S.BalanceStockQty, 0) AS BalanceStockQty,
    COALESCE(S.BalanceStockNos, 0) AS BalanceStockNos,
    COALESCE(P.TotalPendingPlanQty, 0) AS PendingPlanQty,
    COALESCE(P.TotalPendingPlanNos, 0) AS PendingPlanNos,
    (COALESCE(S.BalanceStockQty, 0) - COALESCE(P.TotalPendingPlanQty, 0)) AS NetAvailableQty,
    COALESCE(P.AssociatedPlans, '') AS PendingPlanNoRemark
FROM CTE_PhysicalStock S
FULL OUTER JOIN CTE_ParamPendingSummary P 
    ON S.ItemId = P.ItemId 
   AND S.GradeId = P.GradeId 
   AND S.WidthId = P.WidthId 
   AND S.CoatingId = P.CoatingId 
   AND S.WattLossId = P.WattLossId 
   AND S.AirGapId = P.AirGapId
LEFT JOIN DprItem I     ON COALESCE(S.ItemId, P.ItemId) = I.ItemId
LEFT JOIN DprGrade G    ON COALESCE(S.GradeId, P.GradeId) = G.GradeId
LEFT JOIN DprWidth W    ON COALESCE(S.WidthId, P.WidthId) = W.WidthId
LEFT JOIN DprCoating C  ON COALESCE(S.CoatingId, P.CoatingId) = C.CoatingId
LEFT JOIN DprWattLoss WL ON COALESCE(S.WattLossId, P.WattLossId) = WL.WattLossId
LEFT JOIN DprAirGap AG  ON COALESCE(S.AirGapId, P.AirGapId) = AG.AirGapId
ORDER BY I.ItemName, G.GradeName, W.WidthName;