namespace MMDAL.Query.MasterSchedule { public class MasterScheduleQB { // ── GET single header ───────────────────────────────────────────── // Index: MMASTERSCHEDULE(MASTERSCHEDULEID, TENANTID) public const string GET_MASTERSCHEDULE = @" SELECT ms.MASTERSCHEDULEID AS MasterScheduleId, ms.MASTERSCHEDULECODE AS MasterScheduleCode, ms.MASTERSCHEDULENAME AS MasterScheduleName, ms.ORGANIZATIONTYPE AS MasterScheduleOrganizationType, ms.OUID AS OUId, ou.OUCODE AS OUCode, ou.OUNAME AS OUName, ms.OUGROUPID AS OUGroupId, ISNULL(oug.OUGROUPCODE,'') AS OUGroupCode, ISNULL(oug.OUGROUPNAME,'') AS OUGroupName, ms.ISINCLUDEINATP AS MasterScheduleIsIncludeInATP, ms.ISTRACKCONVERSION AS MasterScheduleIsTrackConversion, ms.ISAUTORELEASE AS MasterScheduleIsAutoRelease, ms.ISPENDING AS MasterScheduleIsPending, ms.DEMANDTYPE AS MasterScheduleDemandType, ms.VALIDTILLDATE AS MasterScheduleValidTillDate, ms.SORTORDER AS MasterScheduleSortOrder, ms.STATUS AS MasterScheduleStatus, ms.VERSION AS MasterScheduleVersion, ms.CREATEDBYID AS MasterScheduleCreatedById, ms.CREATEDON AS MasterScheduleCreatedOn, ms.MODIFIEDBYID AS MasterScheduleModifiedById, ms.MODIFIEDON AS MasterScheduleModifiedOn, ISNULL(cu.USERNAME,'') AS MasterScheduleCreatedByName, ISNULL(mu.USERNAME,'') AS MasterScheduleModifiedByName, ms.TENANTID AS TenantId FROM MMASTERSCHEDULE ms JOIN MOU ou ON ou.OUID = ms.OUID AND ou.TENANTID = ms.TENANTID LEFT JOIN MOUGROUP oug ON oug.OUGROUPID = ms.OUGROUPID AND oug.TENANTID = ms.TENANTID LEFT JOIN MUSER cu ON cu.USERID = ms.CREATEDBYID LEFT JOIN MUSER mu ON mu.USERID = ms.MODIFIEDBYID WHERE ms.MASTERSCHEDULEID = @MasterScheduleId AND ms.TENANTID = @TenantId;"; // ── GET detail rows for a header ────────────────────────────────── // Index: MMASTERSCHEDULEDETAIL(MASTERSCHEDULEID, TENANTID) public const string GET_MASTERSCHEDULE_DETAILS = @" SELECT msd.MASTERSCHEDULEDETAILID AS MasterScheduleDetailId, msd.MASTERSCHEDULEID AS MasterScheduleId, msd.SLNO AS MasterScheduleDetailSlNo, msd.ITEMID AS ItemId, ISNULL(i.ITEMCODE,'') AS ItemCode, ISNULL(i.ITEMNAME,'') AS ItemName, ISNULL(i.ITEMSHORTNAME,'') AS ItemShortName, msd.SKUID AS SKUId, ISNULL(s.SKUNAME,'') AS SKUName, ISNULL(s.SKUCODE,'') AS SKUCode, msd.SOURCETYPE AS MasterScheduleDetailSourceType, msd.SOURCEOBJECTTYPEID AS SourceObjectTypeId, msd.SOURCEOBJECTID AS MasterScheduleDetailSourceObjectId, msd.DATEFROM AS MasterScheduleDetailDateFrom, msd.DATETO AS MasterScheduleDetailDateTo, msd.DAYS AS MasterScheduleDetailDays, msd.DAILYQUANTITY AS MasterScheduleDetailDailyQuantity, msd.TOTALQUANTITY AS MasterScheduleDetailTotalQuantity, msd.PLANNEDQUANTITY AS MasterScheduleDetailPlannedQuantity, msd.BALANCEQUANTITY AS MasterScheduleDetailBalanceQuantity, msd.PARTYID AS PartyId, ISNULL(p.PARTYCODE,'') AS PartyCode, ISNULL(p.PARTYNAME,'') AS PartyName, msd.ALLOCATIONID AS AllocationId, ISNULL(a.ALLOCATIONNAME,'') AS AllocationName, msd.TASKID AS MasterScheduleDetailTaskId, ISNULL(msd.REMARKS,'') AS MasterScheduleDetailRemarks, msd.RELIEF AS MasterScheduleDetailRelief, ISNULL(msd.REFERENCENUMBER,'') AS MasterScheduleDetailReferenceNumber, msd.REFERENCEDATE AS MasterScheduleDetailReferenceDate, msd.MRPRUNID AS MasterScheduleDetailMRPRunId, msd.POSTEDMRPRUNID AS PostedMRPRunId, ISNULL(msd.PRIORITY, 0) AS Priority, msd.CREATEDBYID AS MasterScheduleDetailCreatedById, msd.CREATEDON AS MasterScheduleDetailCreatedOn, msd.MODIFIEDBYID AS MasterScheduleDetailModifiedById, msd.MODIFIEDON AS MasterScheduleDetailModifiedOn, msd.TENANTID AS TenantId FROM MMASTERSCHEDULEDETAIL msd LEFT JOIN MITEM i ON i.ITEMID = msd.ITEMID AND i.TENANTID = msd.TENANTID LEFT JOIN MSKU s ON s.SKUID = msd.SKUID AND s.TENANTID = msd.TENANTID LEFT JOIN MPARTY p ON p.PARTYID = msd.PARTYID AND p.TENANTID = msd.TENANTID LEFT JOIN MALLOCATION a ON a.ALLOCATIONID = msd.ALLOCATIONID AND a.TENANTID = msd.TENANTID WHERE msd.MASTERSCHEDULEID = @MasterScheduleId AND msd.TENANTID = @TenantId ORDER BY msd.SLNO;"; // ── SAVE header ─────────────────────────────────────────────────── // Index: (none additional — PK on MASTERSCHEDULEID) public const string SAVE_MASTERSCHEDULE = @" INSERT INTO MMASTERSCHEDULE ( MASTERSCHEDULEID, MASTERSCHEDULECODE, MASTERSCHEDULENAME, ORGANIZATIONTYPE, OUID, OUGROUPID, ISINCLUDEINATP, ISTRACKCONVERSION, ISAUTORELEASE, ISPENDING, DEMANDTYPE, VALIDTILLDATE, SORTORDER, STATUS, VERSION, CREATEDBYID, CREATEDON, MODIFIEDBYID, MODIFIEDON, TENANTID ) VALUES ( @MasterScheduleId, @MasterScheduleCode, @MasterScheduleName, @MasterScheduleOrganizationType, @OUId, @OUGroupId, @MasterScheduleIsIncludeInATP, @MasterScheduleIsTrackConversion, @MasterScheduleIsAutoRelease, @MasterScheduleIsPending, @MasterScheduleDemandType, @MasterScheduleValidTillDate, @MasterScheduleSortOrder, @MasterScheduleStatus, @MasterScheduleVersion, @MasterScheduleCreatedById, @MasterScheduleCreatedOn, @MasterScheduleModifiedById, @MasterScheduleModifiedOn, @TenantId );"; // ── UPDATE header ───────────────────────────────────────────────── public const string UPDATE_MASTERSCHEDULE = @" UPDATE MMASTERSCHEDULE SET MASTERSCHEDULECODE = @MasterScheduleCode, MASTERSCHEDULENAME = @MasterScheduleName, ORGANIZATIONTYPE = @MasterScheduleOrganizationType, OUID = @OUId, OUGROUPID = @OUGroupId, ISINCLUDEINATP = @MasterScheduleIsIncludeInATP, ISTRACKCONVERSION = @MasterScheduleIsTrackConversion, ISAUTORELEASE = @MasterScheduleIsAutoRelease, ISPENDING = @MasterScheduleIsPending, DEMANDTYPE = @MasterScheduleDemandType, VALIDTILLDATE = @MasterScheduleValidTillDate, SORTORDER = @MasterScheduleSortOrder, STATUS = @MasterScheduleStatus, VERSION = @MasterScheduleVersion, MODIFIEDBYID = @MasterScheduleModifiedById, MODIFIEDON = @MasterScheduleModifiedOn WHERE MASTERSCHEDULEID = @MasterScheduleId AND TENANTID = @TenantId;"; // ── DELETE header (hard delete) ─────────────────────────────────── public const string DELETE_MASTERSCHEDULE = @" DELETE FROM MMASTERSCHEDULE WHERE MASTERSCHEDULEID <> -1 AND MASTERSCHEDULEID = @MasterScheduleId AND TENANTID = @TenantId;"; // ── SAVE detail row ─────────────────────────────────────────────── // Index: MMASTERSCHEDULEDETAIL(MASTERSCHEDULEID, PRIORITY, ITEMID, TENANTID) public const string SAVE_MASTERSCHEDULEDETAIL = @" INSERT INTO MMASTERSCHEDULEDETAIL ( MASTERSCHEDULEDETAILID, MASTERSCHEDULEID, SLNO, ITEMID, SKUID, SOURCETYPE, SOURCEOBJECTTYPEID, SOURCEOBJECTID, DATEFROM, DATETO, DAYS, DAILYQUANTITY, TOTALQUANTITY, PLANNEDQUANTITY, BALANCEQUANTITY, PARTYID, ALLOCATIONID, TASKID, REMARKS, RELIEF, REFERENCENUMBER, REFERENCEDATE, MRPRUNID, POSTEDMRPRUNID, PRIORITY, CREATEDBYID, CREATEDON, MODIFIEDBYID, MODIFIEDON, TENANTID ) VALUES ( @MasterScheduleDetailId, @MasterScheduleId, @MasterScheduleDetailSlNo, @ItemId, @SKUId, @MasterScheduleDetailSourceType, @SourceObjectTypeId, @MasterScheduleDetailSourceObjectId, @MasterScheduleDetailDateFrom, @MasterScheduleDetailDateTo, @MasterScheduleDetailDays, @MasterScheduleDetailDailyQuantity, @MasterScheduleDetailTotalQuantity, @MasterScheduleDetailPlannedQuantity, @MasterScheduleDetailBalanceQuantity, @PartyId, @AllocationId, @MasterScheduleDetailTaskId, @MasterScheduleDetailRemarks, @MasterScheduleDetailRelief, @MasterScheduleDetailReferenceNumber, @MasterScheduleDetailReferenceDate, @MasterScheduleDetailMRPRunId, @PostedMRPRunId, @Priority, @MasterScheduleDetailCreatedById, @MasterScheduleDetailCreatedOn, @MasterScheduleDetailModifiedById, @MasterScheduleDetailModifiedOn, @TenantId );"; // ── UPDATE single detail row ────────────────────────────────────── public const string UPDATE_MASTERSCHEDULEDETAIL = @" UPDATE MMASTERSCHEDULEDETAIL SET SLNO = @MasterScheduleDetailSlNo, ITEMID = @ItemId, SKUID = @SKUId, SOURCETYPE = @MasterScheduleDetailSourceType, SOURCEOBJECTTYPEID = @SourceObjectTypeId, SOURCEOBJECTID = @MasterScheduleDetailSourceObjectId, DATEFROM = @MasterScheduleDetailDateFrom, DATETO = @MasterScheduleDetailDateTo, DAYS = @MasterScheduleDetailDays, DAILYQUANTITY = @MasterScheduleDetailDailyQuantity, TOTALQUANTITY = @MasterScheduleDetailTotalQuantity, PLANNEDQUANTITY = @MasterScheduleDetailPlannedQuantity, BALANCEQUANTITY = @MasterScheduleDetailBalanceQuantity, PARTYID = @PartyId, ALLOCATIONID = @AllocationId, TASKID = @MasterScheduleDetailTaskId, REMARKS = @MasterScheduleDetailRemarks, RELIEF = @MasterScheduleDetailRelief, REFERENCENUMBER = @MasterScheduleDetailReferenceNumber, REFERENCEDATE = @MasterScheduleDetailReferenceDate, PRIORITY = @Priority, MODIFIEDBYID = @MasterScheduleDetailModifiedById, MODIFIEDON = @MasterScheduleDetailModifiedOn WHERE MASTERSCHEDULEDETAILID = @MasterScheduleDetailId AND TENANTID = @TenantId;"; // ── DELETE all details for a header ─────────────────────────────── public const string DELETE_MASTERSCHEDULEDETAILS = @" DELETE FROM MMASTERSCHEDULEDETAIL WHERE MASTERSCHEDULEID <> -1 AND MASTERSCHEDULEID = @MasterScheduleId AND TENANTID = @TenantId;"; // ── DELETE specific detail row ──────────────────────────────────── public const string DELETE_MASTERSCHEDULEDETAIL = @" DELETE FROM MMASTERSCHEDULEDETAIL WHERE MASTERSCHEDULEDETAILID <> -1 AND MASTERSCHEDULEDETAILID = @MasterScheduleDetailId AND TENANTID = @TenantId;"; // ── Paged list ──────────────────────────────────────────────────── // Index: MMASTERSCHEDULE(OUID, STATUS, TENANTID) public const string GET_MASTERSCHEDULE_LIST = @" SELECT ms.MASTERSCHEDULEID AS MasterScheduleId, ms.MASTERSCHEDULECODE AS MasterScheduleCode, ms.MASTERSCHEDULENAME AS MasterScheduleName, ms.ORGANIZATIONTYPE AS MasterScheduleOrganizationType, ms.OUID AS OUId, ISNULL(ou.OUCODE,'') AS OUCode, ISNULL(ou.OUNAME,'') AS OUName, ms.OUGROUPID AS OUGroupId, ISNULL(oug.OUGROUPNAME,'') AS OUGroupName, ms.ISAUTORELEASE AS MasterScheduleIsAutoRelease, ms.ISPENDING AS MasterScheduleIsPending, ms.DEMANDTYPE AS MasterScheduleDemandType, ms.STATUS AS MasterScheduleStatus, ms.TENANTID AS TenantId FROM MMASTERSCHEDULE ms JOIN MOU ou ON ou.OUID = ms.OUID AND ou.TENANTID = ms.TENANTID LEFT JOIN MOUGROUP oug ON oug.OUGROUPID = ms.OUGROUPID AND oug.TENANTID = ms.TENANTID WHERE ms.MASTERSCHEDULEID <> -1 AND ms.TENANTID = @TenantId ORDER BY ms.MASTERSCHEDULEID DESC OFFSET @PageOffset ROWS FETCH NEXT @PageSize ROWS ONLY;"; public const string GET_MASTERSCHEDULE_LIST_COUNT = @" SELECT COUNT(1) FROM MMASTERSCHEDULE ms WHERE ms.MASTERSCHEDULEID <> -1 AND ms.TENANTID = @TenantId;"; // ── Pick-list (select list) ─────────────────────────────────────── public const string GET_SELECTLIST_MASTERSCHEDULE = @" SELECT TOP 50 ms.MASTERSCHEDULEID AS Id, ms.MASTERSCHEDULECODE AS Code, ms.MASTERSCHEDULENAME AS Name FROM MMASTERSCHEDULE ms WHERE ms.MASTERSCHEDULEID <> -1 AND ms.TENANTID = @TenantId ORDER BY ms.MASTERSCHEDULENAME;"; // ── Load Master Schedule — inserts MMASTERSCHEDULEDETAIL from source ── // SourceType: 0=Forecast, 1=SalesOrder, 2=SalesOrder+Forecast // Step 1: delete existing detail rows for this master schedule // Step 2: INSERT INTO MMASTERSCHEDULEDETAIL from TFORECASTDATA / TMMDETAIL (sales orders) // Index: MMASTERSCHEDULEDETAIL(MASTERSCHEDULEID, TENANTID) public const string LOAD_DELETE_EXISTING_DETAILS = @" DELETE FROM MMASTERSCHEDULEDETAIL WHERE MASTERSCHEDULEID <> -1 AND MASTERSCHEDULEID = @MasterScheduleId AND TENANTID = @TenantId;"; // Load from forecast data (SourceType = 0) // TFORECASTDATA must have: ITEMID, SKUID, DATEFROM, DATETO, DAILYQUANTITY, TOTALQUANTITY, PARTYID, ALLOCATIONID // Index: TFORECASTDATA(MASTERSCHEDULEID, TENANTID) public const string LOAD_FROM_FORECAST = @" INSERT INTO MMASTERSCHEDULEDETAIL ( MASTERSCHEDULEDETAILID, MASTERSCHEDULEID, SLNO, ITEMID, SKUID, SOURCETYPE, SOURCEOBJECTTYPEID, SOURCEOBJECTID, DATEFROM, DATETO, DAYS, DAILYQUANTITY, TOTALQUANTITY, PLANNEDQUANTITY, BALANCEQUANTITY, PARTYID, ALLOCATIONID, TASKID, REMARKS, RELIEF, REFERENCENUMBER, REFERENCEDATE, MRPRUNID, POSTEDMRPRUNID, PRIORITY, CREATEDBYID, CREATEDON, MODIFIEDBYID, MODIFIEDON, TENANTID ) SELECT fd.FORECASTDETAILID, @MasterScheduleId, ROW_NUMBER() OVER (ORDER BY fd.DATEFROM, fd.ITEMID), fd.ITEMID, fd.SKUID, 0, -1, fd.FORECASTDETAILID, fd.DATEFROM, fd.DATETO, DATEDIFF(DAY, fd.DATEFROM, fd.DATETO), fd.DAILYQUANTITY, fd.TOTALQUANTITY, 0, fd.TOTALQUANTITY, ISNULL(fd.PARTYID, -1), ISNULL(fd.ALLOCATIONID, -1), -1, '', 0, '', CAST('1900-01-01' AS DATETIME), -1, -1, 0, @CreatedById, GETUTCDATE(), @ModifiedById, GETUTCDATE(), @TenantId FROM TFORECASTDATA fd WHERE fd.MASTERSCHEDULEID = @MasterScheduleId AND fd.TENANTID = @TenantId;"; // Load from sales order lines (SourceType = 1) // Reads TMMDETAIL (sales order detail) for items with MASTERPLANMETHOD=0 (MRP items) // Index: TMMDETAIL(MASTERSCHEDULEID, TENANTID), MITEM(ITEMID, MASTERPLANMETHOD) public const string LOAD_FROM_SALESORDER = @" INSERT INTO MMASTERSCHEDULEDETAIL ( MASTERSCHEDULEDETAILID, MASTERSCHEDULEID, SLNO, ITEMID, SKUID, SOURCETYPE, SOURCEOBJECTTYPEID, SOURCEOBJECTID, DATEFROM, DATETO, DAYS, DAILYQUANTITY, TOTALQUANTITY, PLANNEDQUANTITY, BALANCEQUANTITY, PARTYID, ALLOCATIONID, TASKID, REMARKS, RELIEF, REFERENCENUMBER, REFERENCEDATE, MRPRUNID, POSTEDMRPRUNID, PRIORITY, CREATEDBYID, CREATEDON, MODIFIEDBYID, MODIFIEDON, TENANTID ) SELECT @StartNumber + ROW_NUMBER() OVER (ORDER BY d.MMHEADERDATE, d.ITEMID) - 1, @MasterScheduleId, ROW_NUMBER() OVER (ORDER BY d.MMHEADERDATE, d.ITEMID), d.ITEMID, d.SKUID, 1, -1, d.MMDETAILID, d.MMHEADERDATE, d.DELIVERYDATE, DATEDIFF(DAY, d.MMHEADERDATE, d.DELIVERYDATE), d.PENDINGQUANTITY, d.PENDINGQUANTITY, 0, d.PENDINGQUANTITY, ISNULL(h.PARTYID, -1), ISNULL(d.ALLOCATIONID, -1), -1, '', 0, h.REFERENCENUMBER, h.REFERENCEDATE, -1, -1, ISNULL(d.PRIORITY, 0), @CreatedById, GETUTCDATE(), @ModifiedById, GETUTCDATE(), @TenantId FROM TMMDETAIL d JOIN TMMHEADER h ON h.MMHEADERID = d.MMHEADERID AND h.TENANTID = d.TENANTID JOIN MITEM i ON i.ITEMID = d.ITEMID AND i.TENANTID = d.TENANTID JOIN MMRP mrp ON mrp.MASTERSCHEDULEID = @MasterScheduleId AND mrp.OUID = h.OUID AND mrp.TENANTID = @TenantId WHERE d.TENANTID = @TenantId AND d.PENDINGQUANTITY > 0 AND d.STATUS IN (1, 2) -- open / partial AND i.MASTERPLANMETHOD = 0;"; //-- MRP planning method // ── SET DEMAND PRIORITY (batch UPDATE) ──────────────────────────── // Called from SetDemandPriority endpoint // Accepts a table-valued param via Dapper multi-exec public const string SET_DEMAND_PRIORITY = @" UPDATE MMASTERSCHEDULEDETAIL SET PRIORITY = @Priority, MODIFIEDBYID = @ModifiedById, MODIFIEDON = GETUTCDATE() WHERE MASTERSCHEDULEDETAILID = @MasterScheduleDetailId AND TENANTID = @TenantId;"; // ── SET ALLOCATION PRIORITY ─────────────────────────────────────── public const string SET_ALLOCATION_PRIORITY = @" UPDATE MALLOCATION SET PRIORITY = @Priority WHERE ALLOCATIONID = @AllocationId AND TENANTID = @TenantId;"; public const string CASCADE_ALLOCATION_PRIORITY_TO_DETAILS = @" UPDATE MMASTERSCHEDULEDETAIL SET PRIORITY = @Priority, MODIFIEDON = GETUTCDATE() WHERE ALLOCATIONID = @AllocationId AND PRIORITY = 0 -- only unset rows AND TENANTID = @TenantId;"; // ── ClearOldSchedule ────────────────────────────────────────────── // ClearType=0: delete all detail rows (hard clear — removes planned demand) public const string CLEAR_SCHEDULE_DELETE_DETAILS = @" DELETE FROM MMASTERSCHEDULEDETAIL WHERE MASTERSCHEDULEID = @masterScheduleId"; // ClearType=1: reset BALANCEQUANTITY to DAILYQUANTITY (re-open demand) public const string CLEAR_SCHEDULE_RESET_BALANCE = @" UPDATE MMASTERSCHEDULEDETAIL SET BALANCEQUANTITY = DAILYQUANTITY, MODIFIEDON = GETUTCDATE() WHERE MASTERSCHEDULEID = @masterScheduleId"; } }