namespace MMDAL.Query.MRPRun { // Index required: TMRPACTION (MRPRUNID, TENANTID, PLANSTATUS) // Index required: TMRPACTION (PRIORITY, PLANSTATUS, TENANTID) — for priority release filtering // Index required: TMRPRUNDETAILS (MRPRUNID, ITEMID, TENANTID) public static class MRPActionQB { public const string GET_MRPACTION = @" SELECT a.MRPACTIONID AS MRPActionId, a.MRPRUNID AS MRPRunId, a.SLNO AS MRPActionSlNo, a.PLANITEMID AS PlanItemId, i.ITEMCODE AS PlanItemCode, i.ITEMNAME AS PlanItemName, i.ITEMSHORTNAME AS PlanItemShortName, --i.MAKEORBUY AS PlanItemMakeOrBuy, i.LOTSIZE AS LotSize, a.PLANSKUID AS PlanSKUId, sk.SKUCODE AS PlanSKUCode, sk.SKUNAME AS PlanSKUName, a.PLANTYPE AS MRPActionPlanType, a.OBJECTID AS MRPActionObjectId, a.PLANNEDSTARTDATE AS MRPActionPlannedStartDate, a.PLANNEDORDERDATE AS MRPActionPlannedOrderDate, a.PLANNEDDUEDATE AS MRPActionPlannedDueDate, a.PLANNEDQUANTITY AS MRPActionPlannedQuantity, a.ORIGINALPLANNEDQUANTITY AS MRPActionOriginalPlannedQuantity, a.INPROCESSQUANTITY AS MRPActionInProcessQuantity, a.IMPLEMENTEDQUANTITY AS MRPActionImplementedQuantity, a.COMPRESSIONDAYS AS MRPActionCompressionDays, a.ISFIRMPLAN AS MRPActionIsFirmPlan, a.FIRMDATE AS MRPActionFirmDate, a.FIRMQUANTITY AS MRPActionFirmQuantity, a.PLANNEDACTION AS MRPActionPlannedAction, a.REMARKS AS MRPActionRemarks, a.PLANSTATUS AS MRPActionPlanStatus, a.STOREID AS StoreId, st.STORECODE AS StoreCode, st.STORENAME AS StoreName, a.ALLOCATIONID AS AllocationId, a.TASKID AS MRPActionTaskId, a.RELEASETYPE AS MRPActionReleaseType, a.RELEASEBIZTRANSACTIONTYPEID AS ReleaseBizTransactionTypeId, btt.BIZTRANSACTIONTYPECODE AS ReleaseBizTransactionTypeCode, btt.BIZTRANSACTIONTYPENAME AS ReleaseBizTransactionTypeName, a.RELEASEID AS MRPActionReleaseId, --a.ISALLOCATIONBASEDRELEASE AS IsAllocationBasedRelease, a.PARTYID AS PartyId, pt.PARTYCODE AS PartyCode, pt.PARTYNAME AS PartyName, a.PARTYBRANCHID AS PartyBranchId, pb.PARTYBRANCHCODE AS PartyBranchCode, pb.PARTYBRANCHNAME AS PartyBranchName, pb.PARTYBRANCHSHORTNAME AS PartyBranchShortName, a.ALTERNATEBOMID AS AlternateBOMId, a.ALTERNATEROUTINGID AS AlternateRoutingId, a.MRPRUNDETAILID AS MRPRunDetailId, a.NESTINGPLANID AS MRPActionNestingPlanId, a.PRIORITY AS Priority, a.SALESORDERID AS SalesOrderId, a.RUNSCOPE AS RunScope --a.TENANTID AS TenantId FROM TMRPACTION a JOIN MITEM i ON i.ITEMID = a.PLANITEMID LEFT JOIN MSKU sk ON sk.SKUID = a.PLANSKUID LEFT JOIN MSTORE st ON st.STOREID = a.STOREID LEFT JOIN MPARTY pt ON pt.PARTYID = a.PARTYID LEFT JOIN MPARTYBRANCH pb ON pb.PARTYBRANCHID = a.PARTYBRANCHID LEFT JOIN MBIZTRANSACTIONTYPE btt ON btt.BIZTRANSACTIONTYPEID = a.RELEASEBIZTRANSACTIONTYPEID WHERE a.MRPACTIONID = @mrpActionId"; public const string GET_MRPACTION_LIST = @" SELECT a.MRPACTIONID AS MRPActionId, a.MRPRUNID AS MRPRunId, a.SLNO AS MRPActionSlNo, a.PLANITEMID AS PlanItemId, i.ITEMCODE AS PlanItemCode, i.ITEMNAME AS PlanItemName, a.PLANTYPE AS MRPActionPlanType, a.PLANNEDSTARTDATE AS MRPActionPlannedStartDate, a.PLANNEDORDERDATE AS MRPActionPlannedOrderDate, a.PLANNEDDUEDATE AS MRPActionPlannedDueDate, a.PLANNEDQUANTITY AS MRPActionPlannedQuantity, a.ORIGINALPLANNEDQUANTITY AS MRPActionOriginalPlannedQuantity, a.FIRMQUANTITY AS MRPActionFirmQuantity, a.PLANSTATUS AS MRPActionPlanStatus, a.RELEASETYPE AS MRPActionReleaseType, a.RELEASEID AS MRPActionReleaseId, a.PARTYID AS PartyId, pt.PARTYNAME AS PartyName, a.ALLOCATIONID AS AllocationId, a.SALESORDERID AS SalesOrderId, a.PRIORITY AS Priority, a.RUNSCOPE AS RunScope FROM TMRPACTION a JOIN MITEM i ON i.ITEMID = a.PLANITEMID LEFT JOIN MPARTY pt ON pt.PARTYID = a.PARTYID WHERE a.MRPRUNID = @MrpRunId ORDER BY a.SLNO"; // Workbench: joins TMRPRUNDETAILS + TMRPACTION for the planning grid view // Index required: TMRPRUNDETAILS (MRPRUNID, PERIODDATE, TENANTID) public const string GET_WORKBENCH = @" SELECT rd.MRPRUNDETAILID AS MrpRunDetailId, rd.MRPRUNID AS MrpRunId, rd.PLANITEMID AS PlanItemId, i.ITEMCODE AS PlanItemCode, i.ITEMNAME AS PlanItemName, i.ITEMSHORTNAME AS PlanItemShortName, i.ISMAKEORBUY AS PlanItemMakeOrBuy, i.LOTSIZE AS PlanItemLotSize, rd.PLANSKUID AS PlanSKUId, sk.SKUCODE AS PlanSKUCode, sk.SKUNAME AS PlanSKUName, rd.LOWLEVELCODE AS LowLevelCode, rd.SLNO AS SlNo, rd.REQUIREDPERIOD AS RequiredPeriod, rd.LEADTIME AS LeadTime, rd.TOTALGROSSREQUIREMENT AS TotalGrossRequirement, rd.INDEPENDENTPLANDEMAND AS IndependentPlanDemand, rd.INDEPENDENTCUSTOMERORDER AS IndependentCustomerOrder, rd.DEPENDENTPLANDEMAND AS DependentPlanDemand, rd.DEPENDENTCUSTOMERORDER AS DependentCustomerOrder, rd.ALLOCATION AS Allocation, rd.SCHEDULEDRECEIPTS AS ScheduledReceipts, rd.PROJECTEDSOH AS ProjectedSOH, rd.SOH AS SOH, rd.WIP AS WIP, rd.NETREQUIREMENT AS NetRequirement, rd.PLANNEDORDERRECEIPTS AS PlannedOrderReceipts, rd.PLANNEDORDERRELEASES AS PlannedOrderReleases, a.MRPACTIONID AS MrpActionId, a.PLANTYPE AS PlanType, a.PLANSTATUS AS PlanStatus, a.PLANNEDQUANTITY AS PlannedQuantity, a.ORIGINALPLANNEDQUANTITY AS OriginalPlannedQuantity, a.FIRMQUANTITY AS FirmQuantity, a.INPROCESSQUANTITY AS InProcessQuantity, a.IMPLEMENTEDQUANTITY AS ImplementedQuantity, a.PLANNEDACTION AS PlannedAction, a.ISFIRMPLAN AS IsFirmPlan, a.PLANNEDSTARTDATE AS PlannedStartDate, a.PLANNEDORDERDATE AS PlannedOrderDate, a.PLANNEDDUEDATE AS PlannedDueDate, a.FIRMDATE AS FirmDate, a.COMPRESSIONDAYS AS CompressionDays, a.NESTINGPLANID AS NestingPlanId, a.REMARKS AS Remarks, a.RELEASETYPE AS ReleaseType, a.RELEASEBIZTRANSACTIONTYPEID AS ReleaseBizTransactionTypeId, a.RELEASEID AS ReleaseId, --a.ISALLOCATIONBASEDRELEASE AS IsAllocationBasedRelease, a.PARTYID AS PartyId, pt.PARTYNAME AS PartyName, a.PARTYBRANCHID AS PartyBranchId, pb.PARTYBRANCHNAME AS PartyBranchName, a.STOREID AS StoreId, st.STORENAME AS StoreName, a.ALTERNATEBOMID AS AlternateBOMId, a.ALTERNATEROUTINGID AS AlternateRoutingId, a.PRIORITY AS Priority, a.ALLOCATIONID AS AllocationId, a.SALESORDERID AS SalesOrderId, a.RUNSCOPE AS RunScope, rd.TENANTID AS TenantId, tr.MRPID AS MrpId, ISNULL(m.MRPCODE, '') AS MrpCode, ISNULL(m.MRPNAME, '') AS MrpName, m.MASTERSCHEDULEID AS MasterScheduleId, ISNULL(ms.MASTERSCHEDULECODE, '') AS MasterScheduleCode, ISNULL(ms.MASTERSCHEDULENAME, '') AS MasterScheduleName FROM TMRPRUNDETAILS rd LEFT JOIN TMRPACTION a ON a.MRPRUNDETAILID = rd.MRPRUNDETAILID AND a.TENANTID = rd.TENANTID JOIN MITEM i ON i.ITEMID = rd.PLANITEMID LEFT JOIN MSKU sk ON sk.SKUID = rd.PLANSKUID LEFT JOIN MPARTY pt ON pt.PARTYID = a.PARTYID LEFT JOIN MPARTYBRANCH pb ON pb.PARTYBRANCHID = a.PARTYBRANCHID LEFT JOIN MSTORE st ON st.STOREID = a.STOREID LEFT JOIN TMRPRUN tr ON tr.MRPRUNID = rd.MRPRUNID LEFT JOIN MMRP m ON m.MRPID = tr.MRPID LEFT JOIN MMASTERSCHEDULE ms ON ms.MASTERSCHEDULEID = m.MASTERSCHEDULEID WHERE rd.MRPRUNID = @MrpRunId AND rd.TENANTID = @TenantId {0} ORDER BY a.PRIORITY ASC, rd.LOWLEVELCODE ASC, rd.REQUIREDPERIOD ASC"; // Workbench item-wise: aggregated view per item across all periods public const string GET_WORKBENCH_ITEM_WISE = @" SELECT rd.PLANITEMID AS PlanItemId, i.ITEMCODE AS PlanItemCode, i.ITEMNAME AS PlanItemName, rd.LOWLEVELCODE AS LowLevelCode, SUM(rd.GROSSREQUIREMENT) AS TotalGrossRequirement, SUM(rd.NETREQUIREMENT) AS NetRequirement, SUM(rd.PLANNEDORDERRECEIPTS) AS PlannedOrderReceipts, SUM(rd.SOH) AS SOH, SUM(a.PLANNEDQUANTITY) AS PlannedQuantity, MIN(a.PRIORITY) AS Priority FROM TMRPRUNDETAILS rd LEFT JOIN TMRPACTION a ON a.MRPRUNDETAILID = rd.MRPRUNDETAILID AND a.TENANTID = rd.TENANTID JOIN MITEM i ON i.ITEMID = rd.PLANITEMID WHERE rd.MRPRUNID = @MrpRunId AND rd.TENANTID = @TenantId {0} GROUP BY rd.PLANITEMID, i.ITEMCODE, i.ITEMNAME, rd.LOWLEVELCODE ORDER BY MIN(ISNULL(a.PRIORITY, 999)) ASC, rd.LOWLEVELCODE ASC"; public const string SAVE_MRPACTION = @" INSERT INTO TMRPACTION ( MRPRUNID, SLNO, PLANITEMID, PLANSKUID, PLANTYPE, OBJECTID, PLANNEDSTARTDATE, PLANNEDORDERDATE, PLANNEDDUEDATE, PLANNEDQUANTITY, ORIGINALPLANNEDQUANTITY, INPROCESSQUANTITY, IMPLEMENTEDQUANTITY, COMPRESSIONDAYS, ISFIRMPLAN, FIRMDATE, FIRMQUANTITY, PLANNEDACTION, REMARKS, PLANSTATUS, STOREID, ALLOCATIONID, TASKID, RELEASETYPE, RELEASEBIZTRANSACTIONTYPEID, RELEASEID, --ISALLOCATIONBASEDRELEASE, PARTYID, PARTYBRANCHID, ALTERNATEBOMID, ALTERNATEROUTINGID, MRPRUNDETAILID, NESTINGPLANID, PRIORITY, SALESORDERID, RUNSCOPE -- TENANTID, CREATEDBYID, CREATEDON, MODIFIEDBYID, MODIFIEDON ) VALUES ( @MRPRunId, @MRPActionSlNo, @PlanItemId, @PlanSKUId, @MRPActionPlanType, @MRPActionObjectId, @MRPActionPlannedStartDate, @MRPActionPlannedOrderDate, @MRPActionPlannedDueDate, @MRPActionPlannedQuantity, @MRPActionOriginalPlannedQuantity, @MRPActionInProcessQuantity, @MRPActionImplementedQuantity, @MRPActionCompressionDays, @MRPActionIsFirmPlan, @MRPActionFirmDate, @MRPActionFirmQuantity, @MRPActionPlannedAction, @MRPActionRemarks, @MRPActionPlanStatus, @StoreId, @AllocationId, @MRPActionTaskId, @MRPActionReleaseType, @ReleaseBizTransactionTypeId, @MRPActionReleaseId, --@IsAllocationBasedRelease, @PartyId, @PartyBranchId, @AlternateBOMId, @AlternateRoutingId, @MRPRunDetailId, @MRPActionNestingPlanId, @Priority, @SalesOrderId, @RunScope --@TenantId, @CreatedById, GETUTCDATE(), @ModifiedById, GETUTCDATE() )"; public const string UPDATE_MRPACTION = @" UPDATE TMRPACTION SET ISFIRMPLAN = @MRPActionIsFirmPlan, FIRMDATE = @MRPActionFirmDate, FIRMQUANTITY = @MRPActionFirmQuantity, PLANSTATUS = @MRPActionPlanStatus, STOREID = @StoreId, PARTYID = @PartyId, PARTYBRANCHID = @PartyBranchId, ALTERNATEBOMID = @AlternateBOMId, ALTERNATEROUTINGID = @AlternateRoutingId, REMARKS = @MRPActionRemarks, MODIFIEDBYID = @ModifiedById, MODIFIEDON = GETUTCDATE() WHERE MRPACTIONID = @MRPActionId AND TENANTID = @TenantId"; public const string UPDATE_MRPACTION_RELEASE = @" UPDATE TMRPACTION SET PLANSTATUS = 2, RELEASETYPE = @MRPActionReleaseType, RELEASEBIZTRANSACTIONTYPEID = @ReleaseBizTransactionTypeId, RELEASEID = @MRPActionReleaseId --ISALLOCATIONBASEDRELEASE = @IsAllocationBasedRelease, --MODIFIEDBYID = @ModifiedById, --MODIFIEDON = GETUTCDATE() WHERE MRPACTIONID = @MRPActionId"; public const string COUNT_WORKBENCH = @" SELECT COUNT(1) FROM TMRPRUNDETAILS rd LEFT JOIN TMRPACTION a ON a.MRPRUNDETAILID = rd.MRPRUNDETAILID AND a.TENANTID = rd.TENANTID JOIN MITEM i ON i.ITEMID = rd.PLANITEMID WHERE rd.MRPRUNID = @MrpRunId AND rd.TENANTID = @TenantId {0}"; // Restricts a Make-item workbench to parent items only (GB4 parity — // MRPRun.svc.cs GetWorkBenchDetail's OnlyParent branch). Parent items are those // originally demanded in the master schedule (not exploded BOM components). public const string GET_WORKBENCH_PARENT_ITEMS = @" SELECT DISTINCT msd.ITEMID AS ItemId FROM MMASTERSCHEDULEDETAIL msd WHERE msd.MASTERSCHEDULEID = @MasterScheduleId AND msd.MRPRUNID = @MrpRunId AND msd.SOURCETYPE IN (0, 1, 5) AND ISNULL(msd.REMARKS, '') NOT LIKE '%BOM%' AND msd.TENANTID = @TenantId"; // Latest standard cost per item for the run's items (GB4 parity — GET_RATE_FROM_MRPACTION). // One row per item (not per action/period) — joined in by ItemId at the DAL layer. public const string GET_WORKBENCH_RATE = @" SELECT pc.ITEMID AS ItemId, pc.TOTALCOST AS MaterialTotalCost FROM MPRODUCTCOST pc JOIN ( SELECT ITEMID, MAX(PRODUCTCOSTID) AS PRODUCTCOSTID FROM MPRODUCTCOST WHERE OUID = @OUId AND TYPE = 0 AND ITEMID IN (SELECT DISTINCT PLANITEMID FROM TMRPRUNDETAILS WHERE MRPRUNID = @MrpRunId AND TENANTID = @TenantId) GROUP BY ITEMID ) latest ON latest.PRODUCTCOSTID = pc.PRODUCTCOSTID"; // Stock/purchase UOM names for the run's items (GB4 parity — // GET_MRP_STOCK_AND_PURCHASE_UOM_WITH_QUANTITY). The legacy PurchaseQuantity // shape-based conversion (density/weight driven) is not carried over: it depended on // TMRPRUNDETAILPART.FINALWEIGHT/FINALREQUIREMENT, which GB5's TMRPRUNDETAILPART // (BOM-explosion tracking table) does not store. public const string GET_WORKBENCH_UOM = @" SELECT i.ITEMID AS ItemId, i.STOCKUOMID AS StockUOMId, su.UOMNAME AS StockUOMName, i.PURCHASEUOMID AS PurchaseUOMId, pu.UOMNAME AS PurchaseUOMName FROM MITEM i LEFT JOIN MUOM su ON su.UOMID = i.STOCKUOMID LEFT JOIN MUOM pu ON pu.UOMID = i.PURCHASEUOMID WHERE i.ITEMID IN (SELECT DISTINCT PLANITEMID FROM TMRPRUNDETAILS WHERE MRPRUNID = @MrpRunId AND TENANTID = @TenantId)"; // Shared dynamic WHERE + params builder for both the detailed and item-wise // workbench queries. All values go through DynamicParameters — never string-concatenated. private static (string WhereStr, Dapper.DynamicParameters Params) BuildWorkbenchFilters( MMDAL.DTO.MRPRun.WorkBenchCriteriaDTO c, int tenantId) { var where = new System.Text.StringBuilder(); var p = new Dapper.DynamicParameters(); p.Add("MrpRunId", c.MrpRunId); p.Add("TenantId", tenantId); if (c.AllocationId.HasValue) { where.Append(" AND a.ALLOCATIONID = @AllocationId"); p.Add("AllocationId", c.AllocationId.Value); } if (c.SalesOrderId.HasValue) { where.Append(" AND a.SALESORDERID = @SalesOrderId"); p.Add("SalesOrderId", c.SalesOrderId.Value); } if (c.MinPriority.HasValue) { where.Append(" AND (a.PRIORITY = 0 OR a.PRIORITY >= @MinPriority)"); p.Add("MinPriority", c.MinPriority.Value); } if (c.MaxPriority.HasValue) { where.Append(" AND (a.PRIORITY = 0 OR a.PRIORITY <= @MaxPriority)"); p.Add("MaxPriority", c.MaxPriority.Value); } if (c.PlanStatusIn is { Length: > 0 }) { where.Append(" AND a.PLANSTATUS IN @PlanStatusIn"); p.Add("PlanStatusIn", c.PlanStatusIn); } else if (c.PlanStatus.HasValue) { where.Append(" AND a.PLANSTATUS = @PlanStatus"); p.Add("PlanStatus", c.PlanStatus.Value); } if (c.PlanType.HasValue) { where.Append(" AND a.PLANTYPE = @PlanType"); p.Add("PlanType", c.PlanType.Value); } if (c.FromItem.HasValue) { where.Append(" AND rd.PLANITEMID >= @FromItem"); p.Add("FromItem", c.FromItem.Value); } if (c.ToItem.HasValue) { where.Append(" AND rd.PLANITEMID <= @ToItem"); p.Add("ToItem", c.ToItem.Value); } if (c.ReleaseType.HasValue) { where.Append(" AND a.RELEASETYPE = @ReleaseType"); p.Add("ReleaseType", c.ReleaseType.Value); } if (c.FromDate != default) { where.Append(" AND rd.REQUIREDPERIOD >= @FromDate"); p.Add("FromDate", c.FromDate.Date); } if (c.ToDate != default) { where.Append(" AND rd.REQUIREDPERIOD <= @ToDate"); p.Add("ToDate", c.ToDate.Date); } if (c.ItemId.HasValue) { where.Append(" AND rd.PLANITEMID = @ItemId"); p.Add("ItemId", c.ItemId.Value); } if (c.CategoryId.HasValue) { where.Append(" AND i.CATEGORYID = @CategoryId"); p.Add("CategoryId", c.CategoryId.Value); } if (c.SubCategoryId.HasValue) { where.Append(" AND i.SUBCATEGORYID = @SubCategoryId"); p.Add("SubCategoryId", c.SubCategoryId.Value); } if (c.ItemGroupId.HasValue) { where.Append(" AND i.ITEMGROUPID = @ItemGroupId"); p.Add("ItemGroupId", c.ItemGroupId.Value); } if (c.ItemBrandId.HasValue) { where.Append(" AND i.ITEMBRANDID = @ItemBrandId"); p.Add("ItemBrandId", c.ItemBrandId.Value); } if (c.ModelId.HasValue) { where.Append(" AND i.MODELID = @ModelId"); p.Add("ModelId", c.ModelId.Value); } if (c.PartyId.HasValue) { where.Append(" AND a.PARTYID = @PartyId"); p.Add("PartyId", c.PartyId.Value); } if (c.MakeOrBuy.HasValue) { where.Append(" AND i.ISMAKEORBUY = @MakeOrBuy"); p.Add("MakeOrBuy", c.MakeOrBuy.Value); } return (where.ToString(), p); } // Builds parameterised workbench data + count queries with dynamic WHERE + paging. // applyPaging=false is used for the OnlyParent path (GB4 parity): parent-item // restriction is applied in memory after the full filtered set is fetched, so // DB-side paging would truncate before that filter runs. public static (string DataSql, string CountSql, Dapper.DynamicParameters Params) BuildWorkbench( MMDAL.DTO.MRPRun.WorkBenchCriteriaDTO c, int tenantId, bool applyPaging = true) { var (whereStr, p) = BuildWorkbenchFilters(c, tenantId); var dataSql = string.Format(GET_WORKBENCH, whereStr); if (applyPaging) { var pageSize = c.PageSize > 0 ? c.PageSize : 50; p.Add("Offset", c.PageOffset); p.Add("PageSize", pageSize); dataSql += "\nOFFSET @Offset ROWS FETCH NEXT @PageSize ROWS ONLY"; } var countSql = string.Format(COUNT_WORKBENCH, whereStr); return (dataSql, countSql, p); } // Same filter set as BuildWorkbench, applied to the aggregated item-wise view. // No paging — GetWorkbenchItemWise returns the full aggregated set. public static (string DataSql, Dapper.DynamicParameters Params) BuildWorkbenchItemWise( MMDAL.DTO.MRPRun.WorkBenchCriteriaDTO c, int tenantId) { var (whereStr, p) = BuildWorkbenchFilters(c, tenantId); var dataSql = string.Format(GET_WORKBENCH_ITEM_WISE, whereStr); return (dataSql, p); } } }