using Google.Protobuf.WellKnownTypes; using MMDAL.DTO.Bajaj; using MMDAL.Query.MachineType; using MMDAL.Query.Material; using MMDAL.Query.NestingPlan; using MMDAL.Query.Routing; using MMDAL.Query.WorkCenter; using System.Diagnostics; using static NPOI.HSSF.Util.HSSFColor; namespace MMDAL.Query.Indent { // Indexes recommended: // TINDENT: IX_TINDENT_OUID_STATUS (OUID, STATUS, TENANTID) // TINDENTDETAIL: IX_TINDENTDETAIL_INDENT (INDENTID, STATUS, TENANTID) // TINDENTDETAIL: IX_TINDENTDETAIL_ITEM (ITEMID, TENANTID) // TINDENTMATERIAL: IX_TINDENTMATERIAL_DETAIL (INDENTDETAILID, TENANTID) // TINDENTRESOURCE: IX_TINDENTRESOURCE_DETAIL (INDENTDETAILID, TENANTID) public static class IndentQB { // ── Picklist (existing — keep as-is) ───────────────────────────────── public const string GET_SELECTLIST_INDENT_SQL = @" WITH Paged AS ( SELECT i.INDENTID AS Id, i.INDENTNUMBER AS IndentNumber, i.INDENTDATE AS IndentDate, i.REFERENCENUMBER AS ReferenceNumber, i.INDENTDATE AS ReferenceDate, ROW_NUMBER() OVER (ORDER BY i.INDENTID DESC) AS RowNum FROM TINDENT i WHERE i.STATUS = 1 AND i.INDENTID <> -1 ) SELECT Id, IndentNumber, IndentDate, ReferenceNumber, ReferenceDate FROM Paged WHERE (@firstnumber = -1 AND @maxresult = -1) OR (RowNum BETWEEN @firstnumber AND @maxresult);"; public const string GET_SELECTLIST_INDENT_PG = @" WITH Paged AS ( SELECT i.INDENTID AS ""Id"", i.INDENTNUMBER AS ""IndentNumber"", i.INDENTDATE AS ""IndentDate"", i.REFERENCENUMBER AS ""ReferenceNumber"", i.INDENTDATE AS ""ReferenceDate"", ROW_NUMBER() OVER (ORDER BY i.INDENTID DESC) AS ""RowNum"" FROM TINDENT i WHERE i.STATUS = 1 AND i.INDENTID <> -1 ) SELECT ""Id"", ""IndentNumber"", ""IndentDate"", ""ReferenceNumber"", ""ReferenceDate"" FROM Paged WHERE (:firstnumber = -1 AND :maxresult = -1) OR (""RowNum"" BETWEEN :firstnumber AND :maxresult);"; public const string GET_SELECTLIST_INDENT_ORACLE = @";"; // ── AmendmentSelectList (paged, dual dialect) ──────────────────────── // GB4 parity: Indent.svc/AmendmentSelectList — IndentBLL.AmendmentSelectList → // IndentQueryBuilder.GET_WORKORDER_AMENDMENT_PICKLIST (active indents joined across // NestingPlan/MMDetail/MMHead/SKU/Item/Process, scoped to the caller's OU + Period). public const string GET_INDENT_AMENDMENT_PICKLIST_SQL = @" WITH Paged AS ( SELECT i.INDENTID AS Id, i.INDENTNUMBER AS IndentNumber, i.INDENTDATE AS IndentDate, i.REFERENCENUMBER AS ReferenceNumber, i.INDENTDATE AS ReferenceDate, d.NESTINGPLANID AS NestingPlanId, np.NESTINGPLANCODE AS NestingPlanCode, d.INDENTDETAILID AS IndentDetailId, md.DOCUMENTDETAILID AS MMDetailId, mh.DOCUMENTNUMBER AS DocumentNumber, d.SKUID AS SKUId, sku.SKUCODE AS SKUCode, sku.SKUNAME AS SKUName, d.ITEMID AS ItemId, item.ITEMCODE AS ItemCode, item.ITEMNAME AS ItemName, i.PROCESSID AS ProcessId, pr.PROCESSCODE AS ProcessCode, pr.PROCESSNAME AS ProcessName, d.PLANNEDQUANTITY AS PlannedQuantity, ROW_NUMBER() OVER (ORDER BY i.INDENTID DESC) AS RowNum FROM TINDENT i JOIN TINDENTDETAIL d ON i.INDENTID = d.INDENTID JOIN TNESTINGPLAN np ON d.NESTINGPLANID = np.NESTINGPLANID JOIN TMMDETAIL md ON d.MMDETAILDOCUMENTDETAILID = md.DOCUMENTDETAILID JOIN TMMHEAD mh ON md.DOCUMENTID = mh.DOCUMENTID JOIN MSKU sku ON d.SKUID = sku.SKUID JOIN MITEM item ON d.ITEMID = item.ITEMID JOIN MPROCESS pr ON i.PROCESSID = pr.PROCESSID WHERE i.STATUS = 1 AND i.OUID = @ouid AND i.PERIODID = @periodid AND (@indentid IS NULL OR CAST(i.INDENTID AS VARCHAR(50)) LIKE @indentid) AND (@indentnumber IS NULL OR i.INDENTNUMBER LIKE @indentnumber) AND (@nature IS NULL OR CAST(i.NATURE AS VARCHAR(50)) LIKE @nature) AND (@inchargeid IS NULL OR CAST(i.RECEIVEDBYID AS VARCHAR(50)) LIKE @inchargeid) AND (@processid IS NULL OR CAST(i.PROCESSID AS VARCHAR(50)) LIKE @processid) AND (@biztransactiontypeid IS NULL OR CAST(i.BIZTRANSACTIONTYPEID AS VARCHAR(50)) LIKE @biztransactiontypeid) AND (@nestingplanid IS NULL OR CAST(d.NESTINGPLANID AS VARCHAR(50)) LIKE @nestingplanid) AND (@itemid IS NULL OR CAST(d.ITEMID AS VARCHAR(50)) LIKE @itemid) AND (@indentdetailid IS NULL OR CAST(d.INDENTDETAILID AS VARCHAR(50)) LIKE @indentdetailid) AND (@releasestatus IS NULL OR CAST(d.RELEASESTATUS AS VARCHAR(50)) LIKE @releasestatus) ) SELECT Id, IndentNumber, IndentDate, ReferenceNumber, ReferenceDate, NestingPlanId, NestingPlanCode, IndentDetailId, MMDetailId, DocumentNumber, SKUId, SKUCode, SKUName, ItemId, ItemCode, ItemName, ProcessId, ProcessCode, ProcessName, PlannedQuantity FROM Paged WHERE (@firstnumber = -1 AND @maxresult = -1) OR (RowNum BETWEEN @firstnumber AND @maxresult);"; public const string GET_INDENT_AMENDMENT_PICKLIST_PG = @" WITH Paged AS ( SELECT i.""INDENTID"" AS ""Id"", i.""INDENTNUMBER"" AS ""IndentNumber"", i.""INDENTDATE"" AS ""IndentDate"", i.""REFERENCENUMBER"" AS ""ReferenceNumber"", i.""INDENTDATE"" AS ""ReferenceDate"", d.""NESTINGPLANID"" AS ""NestingPlanId"", np.""NESTINGPLANCODE"" AS ""NestingPlanCode"", d.""INDENTDETAILID"" AS ""IndentDetailId"", md.""DOCUMENTDETAILID"" AS ""MMDetailId"", mh.""DOCUMENTNUMBER"" AS ""DocumentNumber"", d.""SKUID"" AS ""SKUId"", sku.""SKUCODE"" AS ""SKUCode"", sku.""SKUNAME"" AS ""SKUName"", d.""ITEMID"" AS ""ItemId"", item.""ITEMCODE"" AS ""ItemCode"", item.""ITEMNAME"" AS ""ItemName"", i.""PROCESSID"" AS ""ProcessId"", pr.""PROCESSCODE"" AS ""ProcessCode"", pr.""PROCESSNAME"" AS ""ProcessName"", d.""PLANNEDQUANTITY"" AS ""PlannedQuantity"", ROW_NUMBER() OVER (ORDER BY i.""INDENTID"" DESC) AS ""RowNum"" FROM ""TINDENT"" i JOIN ""TINDENTDETAIL"" d ON i.""INDENTID"" = d.""INDENTID"" JOIN ""TNESTINGPLAN"" np ON d.""NESTINGPLANID"" = np.""NESTINGPLANID"" JOIN ""TMMDETAIL"" md ON d.""MMDETAILDOCUMENTDETAILID"" = md.""DOCUMENTDETAILID"" JOIN ""TMMHEAD"" mh ON md.""DOCUMENTID"" = mh.""DOCUMENTID"" JOIN ""MSKU"" sku ON d.""SKUID"" = sku.""SKUID"" JOIN ""MITEM"" item ON d.""ITEMID"" = item.""ITEMID"" JOIN ""MPROCESS"" pr ON i.""PROCESSID"" = pr.""PROCESSID"" WHERE i.""STATUS"" = 1 AND i.""OUID"" = :ouid AND i.""PERIODID"" = :periodid AND (:indentid IS NULL OR CAST(i.""INDENTID"" AS VARCHAR(50)) LIKE :indentid) AND (:indentnumber IS NULL OR i.""INDENTNUMBER"" LIKE :indentnumber) AND (:nature IS NULL OR CAST(i.""NATURE"" AS VARCHAR(50)) LIKE :nature) AND (:inchargeid IS NULL OR CAST(i.""RECEIVEDBYID"" AS VARCHAR(50)) LIKE :inchargeid) AND (:processid IS NULL OR CAST(i.""PROCESSID"" AS VARCHAR(50)) LIKE :processid) AND (:biztransactiontypeid IS NULL OR CAST(i.""BIZTRANSACTIONTYPEID"" AS VARCHAR(50)) LIKE :biztransactiontypeid) AND (:nestingplanid IS NULL OR CAST(d.""NESTINGPLANID"" AS VARCHAR(50)) LIKE :nestingplanid) AND (:itemid IS NULL OR CAST(d.""ITEMID"" AS VARCHAR(50)) LIKE :itemid) AND (:indentdetailid IS NULL OR CAST(d.""INDENTDETAILID"" AS VARCHAR(50)) LIKE :indentdetailid) AND (:releasestatus IS NULL OR CAST(d.""RELEASESTATUS"" AS VARCHAR(50)) LIKE :releasestatus) ) SELECT ""Id"", ""IndentNumber"", ""IndentDate"", ""ReferenceNumber"", ""ReferenceDate"", ""NestingPlanId"", ""NestingPlanCode"", ""IndentDetailId"", ""MMDetailId"", ""DocumentNumber"", ""SKUId"", ""SKUCode"", ""SKUName"", ""ItemId"", ""ItemCode"", ""ItemName"", ""ProcessId"", ""ProcessCode"", ""ProcessName"", ""PlannedQuantity"" FROM Paged WHERE (:firstnumber = -1 AND :maxresult = -1) OR (""RowNum"" BETWEEN :firstnumber AND :maxresult);"; // ── Production/CombineIndentSelectList (paged, dual dialect) ───────── // GB4 parity: ProductionBLL.GetCombineIndentSelectList → ProductionQueryBuilder.GET_INDENT_COMBINE_INDENT_SELECT_LIST public const string GET_COMBINEINDENT_SELECTLIST_SQL = @" WITH Paged AS ( SELECT DISTINCT A.COMBINEINDENTID AS CombineIndentId, B.INDENTNUMBER AS IndentNumber, B.AMENDMENTSLNO AS SlNo, ROW_NUMBER() OVER (ORDER BY B.INDENTNUMBER) AS RowNum FROM TINDENT A INNER JOIN TINDENT B ON A.COMBINEINDENTID = B.INDENTID WHERE 1 = 1 AND (@biztransactiontypeid IS NULL OR CAST(A.BIZTRANSACTIONTYPEID AS VARCHAR(50)) LIKE @biztransactiontypeid) AND (@combineindentid IS NULL OR CAST(A.COMBINEINDENTID AS VARCHAR(50)) LIKE @combineindentid) AND (@indentnumber IS NULL OR B.INDENTNUMBER LIKE @indentnumber) AND (@processid IS NULL OR CAST(A.PROCESSID AS VARCHAR(50)) LIKE @processid) AND (@workcenterid IS NULL OR CAST(A.WORKCENTERID AS VARCHAR(50)) LIKE @workcenterid) ) SELECT CombineIndentId, IndentNumber, SlNo FROM Paged WHERE (@firstnumber = -1 AND @maxresult = -1) OR (RowNum BETWEEN @firstnumber AND @maxresult);"; public const string GET_COMBINEINDENT_SELECTLIST_PG = @" WITH Paged AS ( SELECT DISTINCT A.""COMBINEINDENTID"" AS ""CombineIndentId"", B.""INDENTNUMBER"" AS ""IndentNumber"", B.""AMENDMENTSLNO"" AS ""SlNo"", ROW_NUMBER() OVER (ORDER BY B.""INDENTNUMBER"") AS ""RowNum"" FROM ""TINDENT"" A INNER JOIN ""TINDENT"" B ON A.""COMBINEINDENTID"" = B.""INDENTID"" WHERE 1 = 1 AND (:biztransactiontypeid IS NULL OR CAST(A.""BIZTRANSACTIONTYPEID"" AS VARCHAR(50)) LIKE :biztransactiontypeid) AND (:combineindentid IS NULL OR CAST(A.""COMBINEINDENTID"" AS VARCHAR(50)) LIKE :combineindentid) AND (:indentnumber IS NULL OR B.""INDENTNUMBER"" LIKE :indentnumber) AND (:processid IS NULL OR CAST(A.""PROCESSID"" AS VARCHAR(50)) LIKE :processid) AND (:workcenterid IS NULL OR CAST(A.""WORKCENTERID"" AS VARCHAR(50)) LIKE :workcenterid) ) SELECT ""CombineIndentId"", ""IndentNumber"", ""SlNo"" FROM Paged WHERE (:firstnumber = -1 AND :maxresult = -1) OR (""RowNum"" BETWEEN :firstnumber AND :maxresult);"; // ── Production/IndentReportSelectList (paged, dual dialect) ────────── // GB4 parity: ProductionBLL.GetIndentReportSelectList → ProductionQueryBuilder.GET_INDENT_REPORT_SELECT_LIST // GB4 scopes this to the caller's WorkPeriodId (":periodid"). public const string GET_INDENTREPORT_SELECTLIST_SQL = @" WITH Paged AS ( SELECT A.INDENTID AS IndentId, A.COMBINEINDENTID AS CombineIndentId, A.INDENTNUMBER AS IndentNumber, A.AMENDMENTSLNO AS SlNo, ROW_NUMBER() OVER (ORDER BY A.INDENTID DESC) AS RowNum FROM TINDENT A WHERE A.PERIODID = @periodid ) SELECT IndentId, CombineIndentId, IndentNumber, SlNo FROM Paged WHERE (@firstnumber = -1 AND @maxresult = -1) OR (RowNum BETWEEN @firstnumber AND @maxresult);"; public const string GET_INDENTREPORT_SELECTLIST_PG = @" WITH Paged AS ( SELECT A.""INDENTID"" AS ""IndentId"", A.""COMBINEINDENTID"" AS ""CombineIndentId"", A.""INDENTNUMBER"" AS ""IndentNumber"", A.""AMENDMENTSLNO"" AS ""SlNo"", ROW_NUMBER() OVER (ORDER BY A.""INDENTID"" DESC) AS ""RowNum"" FROM ""TINDENT"" A WHERE A.""PERIODID"" = :periodid ) SELECT ""IndentId"", ""CombineIndentId"", ""IndentNumber"", ""SlNo"" FROM Paged WHERE (:firstnumber = -1 AND :maxresult = -1) OR (""RowNum"" BETWEEN :firstnumber AND :maxresult);"; public const string GET_SELECTLIST_INDENT_MYSQL = @";"; // ── Get single indent header ────────────────────────────────────────── public const string GET_INDENT_SQL = @"SELECT I.INDENTID AS IndentId, I.COMBINEINDENTID AS CombineIndentId, I.AMENDMENTSLNO AS IndentAmendmentSlNo, I.BIZTRANSACTIONTYPEID AS BizTransactionTypeId, BTT.BIZTRANSACTIONTYPECODE AS BizTransactionTypeCode, BTT.BIZTRANSACTIONTYPENAME AS BizTransactionTypeName, BTT.BIZTRANSACTIONCLASSID AS BizTransactionTypeBIZTransactionTypeClassId, BTTC.BIZTRANSACTIONCLASSNAME AS BizTransactionTypeBIZTransactionTypeClassName, BTT.NOGENERATIONTYPE AS BizTransactionTypeNOGenerationType, I.OUID AS OUId, OU.ORGANIZATIONUNITCODE AS OUCode, OU.ORGANIZATIONUNITNAME AS OUName, I.PERIODID AS PeriodId, P.PERIODCODE AS PeriodCode, P.PERIODNAME AS PeriodName, P.FROMDATE AS PeriodFromDate, P.TODATE AS PeriodToDate, I.INDENTDATE AS IndentIndentDate, I.INDENTNUMBER AS IndentIndentNumber, I.REFERENCENUMBER AS IndentReferenceNumber, I.REFERENCEDATE AS IndentReferenceDate, I.INDENTNATURE AS IndentNature, I.PROCESSID AS ProcessId, PR.PROCESSCODE AS ProcessCode, PR.PROCESSNAME AS ProcessName, I.INDENTTYPE AS IndentIndentType, I.PARTYBRANCHID AS PartyBranchId, PB.PARTYBRANCHCODE AS PartyBranchCode, PB.PARTYBRANCHNAME AS PartyBranchName, PB.PARTYID AS PartyBranchPartyBranchPartyId, PA.PARTYNAME AS PartyBranchPartyBranchPartyName, I.WORKCENTERID AS WorkCenterId, WC.WORKCENTERCODE AS WorkCenterCode, WC.WORKCENTERNAME AS WorkCenterName, WC.DEPARTMENTID AS WorkCenterDepartmentId, D.DEPARTMENTCODE AS WorkCenterDepartmentCode, D.DEPARTMENTNAME AS WorkCenterDepartmentName, WC.WORKINGMINUTES AS WorkCenterWorkingMinutes, WC.STARTTIME AS WorkCenterStartTime, WC.CALENDARID AS WorkCenterCalendarId, CAL.CALENDARCODE AS WorkCenterCalendarCode, CAL.CALENDARNAME AS WorkCenterCalendarName, I.PLANNEDNUMBEROFMACHINES AS IndentPlannedNumberOfMachine, I.ISBATCHREQUIRED AS IndentIsBatchRequired, I.EXPECTEDFIRSTDELIVERYDATE AS IndentExpectedFirstDeliveryDate, I.EXPECTEDCOMPLETIONDATE AS IndentExpectedCompletionDate, I.RECEIVEDBYID AS ReceivedbyId, U.USERCODE AS ReceivedbyCode, U.USERNAME AS ReceivedbyName, I.CHANGEREASONID AS ChangeReasonId, CR.GCMCODE AS ChangeReasonCode, CR.GCMNAME AS ChangeReasonName, I.RECEIVEDON AS IndentReceivedOn, I.REMARKS AS IndentRemarks, I.CHANGEREMARKS AS IndentChangeRemarks, I.CREATEDBYID AS IndentCreatedById, UC.USERNAME AS IndentCreatedByName, I.CREATEDON AS IndentCreatedOn, I.MODIFIEDBYID AS IndentModifiedById, UM.USERNAME AS IndentModifiedByName, I.MODIFIEDON AS IndentModifiedOn, I.STATUS AS IndentStatus, I.VERSION AS IndentVersion FROM TINDENT I LEFT JOIN MBIZTRANSACTIONTYPE BTT ON I.BIZTRANSACTIONTYPEID = BTT.BIZTRANSACTIONTYPEID LEFT JOIN MBIZTRANSACTIONCLASS BTTC ON BTT.BIZTRANSACTIONCLASSID = BTTC.BIZTRANSACTIONCLASSID LEFT JOIN MORGANIZATIONUNIT OU ON I.OUID = OU.OUID LEFT JOIN MPERIOD P ON I.PERIODID = P.PERIODID LEFT JOIN MPROCESS PR ON I.PROCESSID = PR.PROCESSID LEFT JOIN MPARTYBRANCH PB ON I.PARTYBRANCHID = PB.PARTYBRANCHID LEFT JOIN MPARTY PA ON PB.PARTYID = PA.PARTYID LEFT JOIN MWORKCENTER WC ON I.WORKCENTERID = WC.WORKCENTERID LEFT JOIN MDEPARTMENT D ON WC.DEPARTMENTID = D.DEPARTMENTID LEFT JOIN MCALENDAR CAL ON WC.CALENDARID = CAL.CALENDARID LEFT JOIN MUSER U ON I.RECEIVEDBYID = U.USERID LEFT JOIN MGCM CR ON I.CHANGEREASONID = CR.GCMID LEFT JOIN MUSER UC ON I.CREATEDBYID = UC.USERID LEFT JOIN MUSER UM ON I.MODIFIEDBYID = UM.USERID WHERE I.INDENTID = @IndentId AND i.STATUS <> 2;"; public const string GET_INDENT_PG = @" SELECT i.INDENTID AS ""IndentId"", i.BIZTRANSACTIONTYPEID AS ""BizTransactionTypeId"", bt.BIZTRANSACTIONTYPENAME AS ""BizTransactionTypeName"", i.OUID, ou.ORGANIZATIONUNITNAME AS ""OUName"", i.PERIODID, p.PERIODNAME AS ""PeriodName"", i.INDENTDATE, i.INDENTNUMBER, i.REFERENCENUMBER, i.INDENTNATURE, i.PROCESSID, pr.PROCESSNAME AS ""ProcessName"", i.INDENTTYPE, i.PARTYBRANCHID, pb.PARTYBRANCHNAME AS ""PartyBranchName"", i.WORKCENTERID, wc.WORKCENTERNAME AS ""WorkCenterName"", i.EXPECTEDCOMPLETIONDATE, COALESCE(i.REMARKS, '') AS ""Remarks"", i.STATUS FROM TINDENT i JOIN MBIZTRANSACTIONTYPE bt ON bt.BIZTRANSACTIONTYPEID = i.BIZTRANSACTIONTYPEID JOIN MORGANIZATIONUNIT ou ON ou.OUID = i.OUID WHERE i.INDENTID = :IndentId AND i.STATUS <> 2;"; // ── Get indent details for a header ────────────────────────────────── public const string GET_INDENT_DETAILS_SQL = @"SELECT TD.INDENTDETAILID AS IndentDetailId, TD.INDENTID AS IndentId, TI.INDENTNUMBER AS IndentIndentNumber, TI.INDENTDATE AS IndentIndentDate, TI.BIZTRANSACTIONTYPEID AS IndentBizTransactionTypeId, BTT.BIZTRANSACTIONTYPENAME AS IndentBizTransactionTypeName, BTT.BIZTRANSACTIONCLASSID AS IndentBizTransactionTypeBIZTransactionTypeClassId, BTTC.BIZTRANSACTIONCLASSNAME AS IndentBizTransactionTypeBIZTransactionTypeClassName, TD.SLNO AS IndentDetailSlNo, TD.LINKTYPEID AS LinkTypeId, E.ENTITYCODE AS LinkTypeCode, E.ENTITYNAME AS LinkTypeName, MMD.DOCUMENTDETAILID AS MMDetailId, MMD.DOCUMENTID AS MMDetailDocumentId, MMDOC.DOCUMENTNUMBER AS MMDetailDocumentNumber, MMD.ITEMID AS MMDetailItemId, MI.ITEMNAME AS MMDetailItemName, MMD.TRANSACTIONACTUALQUANTITY AS MMDetailTransactionActualQuantity, MMDOC.PARTYID AS MMDetailDocumentPartyId, MP.PARTYNAME AS MMDetailDocumentPartyName, MMDOC.MERCHANDISERID AS MMDetailDocumentMerchandiserId, MER.USERNAME AS MMDetailDocumentMerchandiserName, MMDOC.BIZTRANSACTIONTYPEID AS MMDetailDocumentBizTransactionTypeId, BTT.SELECTIONTYPE AS MMDetailDocumentBizTransactionTypeSelectionType, MMDOC.DEPARTMENTID AS MMDetailDocumentDepartmentId, D.DEPARTMENTNAME AS MMDetailDocumentDepartmentName, MMDOC.ALLOCATIONID AS MMDetailDocumentAllocationId, AL.ALLOCATIONNAME AS MMDetailDocumentAllocationName, MMDOC.FROMSTOREID AS MMDetailDocumentFromStoreId, FS.STORENAME AS MMDetailDocumentFromStoreName, MMDOC.TOSTOREID AS MMDetailDocumentToStoreId, TS.STORENAME AS MMDetailDocumentToStoreName, TD.INDENTDETAILDOCUMENTID AS IndentDetailDocumentId, IDD.INDENTID AS IndentDetailDocumentIndentId, TI2.INDENTNUMBER AS IndentDetailDocumentIndentIndentNumber, IDD.PLANNEDQUANTITY AS IndentDetailDocumentPlannedQuantity, IDD.ITEMID AS IndentDetailDocumentMMDetailItemId, MI2.ITEMNAME AS IndentDetailDocumentMMDetailItemName, TD.PROCESSGROUPID AS ProcessGroupId, PG.PROCESSGROUPNUMBER AS ProcessGroupProcessGroupNumber, PG.TOTALGROUPOUTPUTQUANTITY AS ProcessGroupTotalGroupOutputQuantity, TD.WORKPLANDETAILID AS WorkPlanDetailId, TD.SKUID AS SKUId, SKU.SKUNAME AS SKUName, SKU.COLORID AS SKUColorId, C.COLORNAME AS SKUColorName, SKU.COUNTID AS SKUCountId, CNT.COUNTNAME AS SKUCountName, SKU.NETWEIGHT AS SKUNetWeight, TD.CALLID AS CallId, TC.CALLNUMBER AS CallNumber, TD.TASKID AS TaskId, TT.TASKNUMBER AS TaskNumber FROM TINDENTDETAIL TD INNER JOIN TINDENT TI ON TD.INDENTID = TI.INDENTID LEFT JOIN MBIZTRANSACTIONTYPE BTT ON TI.BIZTRANSACTIONTYPEID = BTT.BIZTRANSACTIONTYPEID LEFT JOIN MBIZTRANSACTIONCLASS BTTC ON BTT.BIZTRANSACTIONCLASSID = BTTC.BIZTRANSACTIONCLASSID LEFT JOIN MENTITY E ON TD.LINKTYPEID = E.ENTITYID LEFT JOIN TMMDETAIL MMD ON TD.MMDETAILDOCUMENTDETAILID = MMD.DOCUMENTDETAILID LEFT JOIN TMMHEAD MMDOC ON MMD.DOCUMENTID = MMDOC.DOCUMENTID LEFT JOIN MITEM MI ON MMD.ITEMID = MI.ITEMID LEFT JOIN MPARTY MP ON MMDOC.PARTYID = MP.PARTYID LEFT JOIN MUSER MER ON MMDOC.MERCHANDISERID = MER.USERID LEFT JOIN MDEPARTMENT D ON MMDOC.DEPARTMENTID = D.DEPARTMENTID LEFT JOIN MALLOCATION AL ON MMDOC.ALLOCATIONID = AL.ALLOCATIONID LEFT JOIN MSTORE FS ON MMDOC.FROMSTOREID = FS.STOREID LEFT JOIN MSTORE TS ON MMDOC.TOSTOREID = TS.STOREID LEFT JOIN TINDENTDETAIL IDD ON TD.INDENTDETAILDOCUMENTID = IDD.INDENTDETAILID LEFT JOIN TINDENT TI2 ON IDD.INDENTID = TI2.INDENTID LEFT JOIN MITEM MI2 ON IDD.ITEMID = MI2.ITEMID LEFT JOIN TPROCESSGROUP PG ON TD.PROCESSGROUPID = PG.PROCESSGROUPID LEFT JOIN MSKU SKU ON TD.SKUID = SKU.SKUID LEFT JOIN MCOLOR C ON SKU.COLORID = C.COLORID LEFT JOIN MCOUNT CNT ON SKU.COUNTID = CNT.COUNTID LEFT JOIN TCALL TC ON TD.CALLID = TC.CALLID LEFT JOIN TTASK TT ON TD.TASKID = TT.TASKID WHERE TD.INDENTID = @IndentId AND TD.RELEASESTATUS <> 2 ORDER BY TD.SLNO;"; public const string GET_INDENT_DETAILS_PG = @" SELECT id.INDENTDETAILID AS ""IndentDetailId"", id.INDENTID, id.SLNO, id.ITEMID, i.ITEMCODE, i.ITEMNAME, COALESCE(id.SKUID, -1) AS ""SkuId"", COALESCE(s.SKUCODE, '') AS ""SkuCode"", COALESCE(id.ROUTINGID, -1) AS ""RoutingId"", COALESCE(r.ROUTINGNAME, '') AS ""RoutingName"", COALESCE(id.ROUTINGDETAILID, -1) AS ""RoutingDetailId"", COALESCE(id.BOMID, -1) AS ""BomId"", id.INDENTDETAILQUANTITY, id.PRIORITY AS ""IndentDetailPriority"", id.RELEASEDATE AS ""IndentDetailReleaseDate"", id.PARTDIMID, CONCAT_WS(' / ', dim.D1, dim.D2, dim.D3) AS ""DimensionLabel"", id.ALLOCATIONID, al.ALLOCATIONNAME, id.SALESORDERID, id.RELEASESTATUS, id.STATUS FROM TINDENTDETAIL id JOIN MITEM i ON i.ITEMID = id.ITEMID LEFT JOIN MSKU s ON s.SKUID = id.SKUID LEFT JOIN MROUTING r ON r.ROUTINGID = id.ROUTINGID LEFT JOIN MPARTDIMENSION dim ON dim.PARTDIMID = id.PARTDIMID LEFT JOIN MALLOCATION al ON al.ALLOCATIONID = id.ALLOCATIONID WHERE id.INDENTID = :IndentId AND id.STATUS <> 2 ORDER BY id.SLNO;"; // ── Paged indent list ───────────────────────────────────────────────── public const string GET_INDENT_LIST_SQL = @" SELECT i.INDENTID AS IndentId, i.INDENTNUMBER, i.INDENTDATE, i.REFERENCENUMBER, bt.BIZTRANSACTIONTYPENAME AS BizTransactionTypeName, ou.ORGANIZATIONUNITNAME, i.STATUS, COUNT(id.INDENTDETAILID) AS DetailCount FROM TINDENT i JOIN MBIZTRANSACTIONTYPE bt ON bt.BIZTRANSACTIONTYPEID = i.BIZTRANSACTIONTYPEID JOIN MORGANIZATIONUNIT ou ON ou.OUID = i.OUID LEFT JOIN TINDENTDETAIL id ON id.INDENTID = i.INDENTID WHERE i.STATUS <> 2 AND i.OUID = @OUId GROUP BY i.INDENTID, i.INDENTNUMBER, i.INDENTDATE, i.REFERENCENUMBER, bt.BIZTRANSACTIONTYPENAME, ou.ORGANIZATIONUNITNAME, i.STATUS ORDER BY i.INDENTID DESC OFFSET @FirstNumber ROWS FETCH NEXT @MaxResult ROWS ONLY;"; public const string GET_INDENT_LIST_PG = @"SELECT i.INDENTID AS ""IndentId"", i.INDENTNUMBER, i.INDENTDATE, i.REFERENCENUMBER, bt.BIZTRANSACTIONTYPENAME AS ""BizTransactionTypeName"", ou.ORGANIZATIONUNITNAME, i.STATUS, COUNT(id.INDENTDETAILID) AS ""DetailCount"" FROM TINDENT i JOIN MBIZTRANSACTIONTYPE bt ON bt.BIZTRANSACTIONTYPEID = i.BIZTRANSACTIONTYPEID JOIN MORGANIZATIONUNIT ou ON ou.OUID = i.OUID LEFT JOIN TINDENTDETAIL id ON id.INDENTID = i.INDENTID WHERE i.STATUS <> 2 AND i.OUID = :OUId GROUP BY i.INDENTID, i.INDENTNUMBER, i.INDENTDATE, i.REFERENCENUMBER, bt.BIZTRANSACTIONTYPENAME, ou.ORGANIZATIONUNITNAME, i.STATUS ORDER BY i.INDENTID DESC LIMIT :MaxResult OFFSET :FirstNumber;"; // ── Save indent header ──────────────────────────────────────────────── public const string SAVE_INDENT_SQL = @"INSERT INTO TINDENT ( INDENTID, BIZTRANSACTIONTYPEID, OUID, PERIODID, INDENTDATE, INDENTNUMBER, REFERENCEDATE, REFERENCENUMBER, NATURE, PROCESSID, PARTYBRANCHID, WORKCENTERID, PLANNEDNUMBEROFMACHINES, ISBATCHREQUIRED, EXPECTEDFIRSTDELIVERYDATE, EXPECTEDCOMPLETIONDATE, RECEIVEDBYID, RECEIVEDON, REMARKS, CHANGEREASONID, CHANGEREMARKS, INDENTTYPE, STATUS, VERSION, CREATEDBYID, CREATEDON, MODIFIEDBYID, MODIFIEDON, COMBINEINDENTID, AMENDMENTSLNO, INDENTNATURE, MATRIXID ) VALUES ( @IndentId, @BizTransactionTypeId, @OUId, @PeriodId, @IndentDate, @IndentNumber, @ReferenceDate, @ReferenceNumber, @Nature, @ProcessId, @PartyBranchId, @WorkCenterId, @PlannedNumberOfMachines, @IsBatchRequired, @ExpectedFirstDeliveryDate, @ExpectedCompletionDate, @ReceivedById, @ReceivedOn, @Remarks, @ChangeReasonId, @ChangeRemarks, @IndentType, @Status, @Version, @CreatedById, @CreatedOn, @ModifiedById, @ModifiedOn, @CombineIndentId, @AmendmentSlNo, @IndentNature, @MatrixId);"; public const string SAVE_INDENT_PG = @"INSERT INTO TINDENT ( INDENTID, BIZTRANSACTIONTYPEID, OUID, PERIODID, INDENTDATE, INDENTNUMBER, REFERENCEDATE, REFERENCENUMBER, NATURE, PROCESSID, PARTYBRANCHID, WORKCENTERID, PLANNEDNUMBEROFMACHINES, ISBATCHREQUIRED, EXPECTEDFIRSTDELIVERYDATE, EXPECTEDCOMPLETIONDATE, RECEIVEDBYID, RECEIVEDON, REMARKS, CHANGEREASONID, CHANGEREMARKS, INDENTTYPE, STATUS, VERSION, CREATEDBYID, CREATEDON, MODIFIEDBYID, MODIFIEDON, COMBINEINDENTID, AMENDMENTSLNO, INDENTNATURE, MATRIXID ) VALUES ( :IndentId, :BizTransactionTypeId, :OUId, :PeriodId, :IndentDate, :IndentNumber, :ReferenceDate, :ReferenceNumber, :Nature, :ProcessId, :PartyBranchId, :WorkCenterId, :PlannedNumberOfMachines, :IsBatchRequired, :ExpectedFirstDeliveryDate, :ExpectedCompletionDate, :ReceivedById, :ReceivedOn, :Remarks, :ChangeReasonId, :ChangeRemarks, :IndentType, :Status, :Version, :CreatedById, :CreatedOn, :ModifiedById, :ModifiedOn, :CombineIndentId, :AmendmentSlNo, :IndentNature, :MatrixId );"; public const string UPDATE_INDENT_SQL = @"UPDATE TINDENT SET BIZTRANSACTIONTYPEID = @BizTransactionTypeId, OUID = @OUId, PERIODID = @PeriodId, INDENTDATE = @IndentDate, INDENTNUMBER = @IndentNumber, REFERENCEDATE = @ReferenceDate, REFERENCENUMBER = @ReferenceNumber, NATURE = @Nature, PROCESSID = @ProcessId, PARTYBRANCHID = @PartyBranchId, WORKCENTERID = @WorkCenterId, PLANNEDNUMBEROFMACHINES = @PlannedNumberOfMachines, ISBATCHREQUIRED = @IsBatchRequired, EXPECTEDFIRSTDELIVERYDATE = @ExpectedFirstDeliveryDate, EXPECTEDCOMPLETIONDATE = @ExpectedCompletionDate, RECEIVEDBYID = @ReceivedById, RECEIVEDON = @ReceivedOn, REMARKS = @Remarks, CHANGEREASONID = @ChangeReasonId, CHANGEREMARKS = @ChangeRemarks, INDENTTYPE = @IndentType, STATUS = @Status, VERSION = @Version, MODIFIEDBYID = @ModifiedById, MODIFIEDON = @ModifiedOn, COMBINEINDENTID = @CombineIndentId, AMENDMENTSLNO = @AmendmentSlNo, INDENTNATURE = @IndentNature, MATRIXID = @MatrixId WHERE INDENTID = @IndentId;"; public const string UPDATE_INDENT_PG = @"UPDATE TINDENT SET BIZTRANSACTIONTYPEID = :BizTransactionTypeId, OUID = :OUId, PERIODID = :PeriodId, INDENTDATE = :IndentDate, INDENTNUMBER = :IndentNumber, REFERENCEDATE = :ReferenceDate, REFERENCENUMBER = :ReferenceNumber, NATURE = :Nature, PROCESSID = :ProcessId, PARTYBRANCHID = :PartyBranchId, WORKCENTERID = :WorkCenterId, PLANNEDNUMBEROFMACHINES = :PlannedNumberOfMachines, ISBATCHREQUIRED = :IsBatchRequired, EXPECTEDFIRSTDELIVERYDATE = :ExpectedFirstDeliveryDate, EXPECTEDCOMPLETIONDATE = :ExpectedCompletionDate, RECEIVEDBYID = :ReceivedById, RECEIVEDON = :ReceivedOn, REMARKS = :Remarks, CHANGEREASONID = :ChangeReasonId, CHANGEREMARKS = :ChangeRemarks, INDENTTYPE = :IndentType, STATUS = :Status, VERSION = :Version, MODIFIEDBYID = :ModifiedById, MODIFIEDON = :ModifiedOn, COMBINEINDENTID = :CombineIndentId, AMENDMENTSLNO = :AmendmentSlNo, INDENTNATURE = :IndentNature, MATRIXID = :MatrixId WHERE INDENTID = :IndentId;"; // ── Soft-delete header ──────────────────────────────────────────────── public const string SOFT_DELETE_INDENT_SQL = @" UPDATE TINDENT SET STATUS = 2 WHERE INDENTID = @IndentId;"; public const string SOFT_DELETE_INDENT_PG = @" UPDATE TINDENT SET STATUS = 2 WHERE INDENTID = :IndentId;"; // ── Delete child rows then detail rows (three separate statements — no multi-statement batch) ── public const string DELETE_INDENT_RESOURCES_SQL = @" DELETE FROM TINDENTRESOURCE WHERE INDENTDETAILID IN (SELECT INDENTDETAILID FROM TINDENTDETAIL WHERE INDENTID = @IndentId);"; public const string DELETE_INDENT_RESOURCES_PG = @" DELETE FROM TINDENTRESOURCE WHERE INDENTDETAILID IN (SELECT INDENTDETAILID FROM TINDENTDETAIL WHERE INDENTID = :IndentId);"; public const string DELETE_INDENT_MATERIALS_SQL = @" DELETE FROM TINDENTMATERIAL WHERE INDENTDETAILID IN (SELECT INDENTDETAILID FROM TINDENTDETAIL WHERE INDENTID = @IndentId);"; public const string DELETE_INDENT_MATERIALS_PG = @" DELETE FROM TINDENTMATERIAL WHERE INDENTDETAILID IN (SELECT INDENTDETAILID FROM TINDENTDETAIL WHERE INDENTID = :IndentId);"; public const string DELETE_INDENT_DETAILS_SQL = @" DELETE FROM TINDENTDETAIL WHERE INDENTID = @IndentId;"; public const string DELETE_INDENT_DETAILS_PG = @" DELETE FROM TINDENTDETAIL WHERE INDENTID = :IndentId;"; // ── BulkInsert detail rows ──────────────────────────────────────────── public const string BULK_INSERT_INDENT_DETAIL_SQL = @"INSERT INTO TINDENTDETAIL ( INDENTDETAILID, INDENTID, SLNO, LINKTYPEID, MMDETAILDOCUMENTDETAILID, INDENTDETAILDOCUMENTID, PROCESSGROUPID, WORKPLANDETAILID, SKUID, ROUTINGID, PLANNEDQUANTITY, RATE, PRIORITY, REMARKS, INDENTBEAMTYPE, REVISEDWIDTH, MACHINETYPEID, MACHINEID, BEAMLOTID, METERSPERPIECE, WEAVEPICK, WEAVEWIDTH, ITEMID, RELEASEDATE, RELEASESTATUS, NESTINGPLANID, ALLOCATIONID, CALLID, TASKID, GENERATEALLOCATIONID, ROUTINGDETAILID, BOMID, INDENTDETAILQUANTITY, ALLOTEDGOODQUANTITY, ALLOTEDREWORKQUANTITY, NEXTLEVELQUANTITY, PERQUANTITY ) VALUES ( @IndentDetailId, @IndentId, @SlNo, @LinkTypeId, @MMDetailDocumentDetailId, @IndentDetailDocumentId, @ProcessGroupId, @WorkPlanDetailId, @SkuId, @RoutingId, @PlannedQuantity, @Rate, @Priority, @Remarks, @IndentBeamType, @RevisedWidth, @MachineTypeId, @MachineId, @BeamLotId, @MetersPerPiece, @WeavePick, @WeaveWidth, @ItemId, @IndentDetailReleaseDate, @ReleaseStatus, @NestingPlanId, @AllocationId, @CallId, @TaskId, @GenerateAllocationId, @RoutingDetailId, @BomId, @IndentDetailQuantity, @AllotedGoodQuantity, @AllotedReworkQuantity, @NextLevelQuantity, @PerQuantity );"; public const string BULK_INSERT_INDENT_DETAIL_PG = @"INSERT INTO TINDENTDETAIL ( INDENTDETAILID, INDENTID, SLNO, LINKTYPEID, MMDETAILDOCUMENTDETAILID, INDENTDETAILDOCUMENTID, PROCESSGROUPID, WORKPLANDETAILID, SKUID, ROUTINGID, PLANNEDQUANTITY, RATE, PRIORITY, REMARKS, INDENTBEAMTYPE, REVISEDWIDTH, MACHINETYPEID, MACHINEID, BEAMLOTID, METERSPERPIECE, WEAVEPICK, WEAVEWIDTH, ITEMID, RELEASEDATE, RELEASESTATUS, NESTINGPLANID, ALLOCATIONID, CALLID, TASKID, GENERATEALLOCATIONID, ROUTINGDETAILID, BOMID, INDENTDETAILQUANTITY, ALLOTEDGOODQUANTITY, ALLOTEDREWORKQUANTITY, NEXTLEVELQUANTITY, PERQUANTITY ) VALUES ( :IndentDetailId, :IndentId, :SlNo, :LinkTypeId, :MMDetailDocumentDetailId, :IndentDetailDocumentId, :ProcessGroupId, :WorkPlanDetailId, :SkuId, :RoutingId, :PlannedQuantity, :Rate, :Priority, :Remarks, :IndentBeamType, :RevisedWidth, :MachineTypeId, :MachineId, :BeamLotId, :MetersPerPiece, :WeavePick, :WeaveWidth, :ItemId, :IndentDetailReleaseDate, :ReleaseStatus, :NestingPlanId, :AllocationId, :CallId, :TaskId, :GenerateAllocationId, :RoutingDetailId, :BomId, :IndentDetailQuantity, :AllotedGoodQuantity, :AllotedReworkQuantity, :NextLevelQuantity, :PerQuantity );"; // ── BulkInsert material rows ────────────────────────────────────────── public const string BULK_INSERT_INDENT_MATERIAL_SQL = @"INSERT INTO TINDENTMATERIAL ( INDENTMATERIALID, INDENTDETAILID, SLNO, PROCESSID, LEVEL, PROCESSFORITEMID, PROCESSFORQTY, MATERIALSLNO, ITEMID, TYPE, SKUID, REQUIREDQUANTITY, MATERIALREQUIREDPER, TOTALQUANTITY, WASTEPERCENTAGE, NETQUANTITY, UOMID, REMARKS, ADJUSTQUANTITY, FINALQUANTITY, TYPEOFDATA, ENDS, REJECTEDQUANTITY, REWORKQUANTITY, OTHERQUANTITY ) VALUES ( @IndentMaterialId, @IndentDetailId, @SlNo, @ProcessId, @Level, @ProcessForItemId, @ProcessForQty, @MaterialSlNo, @ItemId, @Type, @SkuId, @RequiredQuantity, @MaterialRequiredPer, @TotalQuantity, @WastePercentage, @NetQuantity, @UOMId, @Remarks, @AdjustQuantity, @FinalQuantity, @TypeOfData, @Ends, @RejectedQuantity, @ReworkQuantity, @OtherQuantity );"; public const string BULK_INSERT_INDENT_MATERIAL_PG = @"INSERT INTO TINDENTMATERIAL ( INDENTMATERIALID, INDENTDETAILID, SLNO, PROCESSID, LEVEL, PROCESSFORITEMID, PROCESSFORQTY, MATERIALSLNO, ITEMID, TYPE, SKUID, REQUIREDQUANTITY, MATERIALREQUIREDPER, TOTALQUANTITY, WASTEPERCENTAGE, NETQUANTITY, UOMID, REMARKS, ADJUSTQUANTITY, FINALQUANTITY, TYPEOFDATA, ENDS, REJECTEDQUANTITY, REWORKQUANTITY, OTHERQUANTITY ) VALUES ( :IndentMaterialId, :IndentDetailId, :SlNo, :ProcessId, :Level, :ProcessForItemId, :ProcessForQty, :MaterialSlNo, :ItemId, :Type, :SkuId, :RequiredQuantity, :MaterialRequiredPer, :TotalQuantity, :WastePercentage, :NetQuantity, :UOMId, :Remarks, :AdjustQuantity, :FinalQuantity, :TypeOfData, :Ends, :RejectedQuantity, :ReworkQuantity, :OtherQuantity );"; // ── BulkInsert resource rows ────────────────────────────────────────── public const string BULK_INSERT_INDENT_RESOURCE_SQL = @"INSERT INTO TINDENTRESOURCE ( INDENTRESOURCEID, INDENTDETAILID, SLNO, BILLOFRESOURCEID, RESOURCETYPEID, MACHINEID, EMPLOYEEID, PLANDURATION, PLANSTARTDATE, PLANSTARTTIME, PLANENDDATE, PLANENDTIME, RESOURCEID, CALENDARDETAILID, PLANNEDQUANTITY, PERQUANTITY, TIMEUOM, REMARKS ) VALUES ( @IndentResourceId, @IndentDetailId, @SlNo, @BillOfResourceId, @ResourceTypeId, @MachineId, @EmployeeId, @PlanDuration, @PlanStartDate, @PlanStartTime, @PlanEndDate, @PlanEndTime, @ResourceId, @CalendarDetailId, @PlannedQuantity, @PerQuantity, @TimeUOM, @Remarks );"; public const string BULK_INSERT_INDENT_RESOURCE_PG = @"INSERT INTO TINDENTRESOURCE ( INDENTRESOURCEID, INDENTDETAILID, SLNO, BILLOFRESOURCEID, RESOURCETYPEID, MACHINEID, EMPLOYEEID, PLANDURATION, PLANSTARTDATE, PLANSTARTTIME, PLANENDDATE, PLANENDTIME, RESOURCEID, CALENDARDETAILID, PLANNEDQUANTITY, PERQUANTITY, TIMEUOM, REMARKS ) VALUES ( :IndentResourceId, :IndentDetailId, :SlNo, :BillOfResourceId, :ResourceTypeId, :MachineId, :EmployeeId, :PlanDuration, :PlanStartDate, :PlanStartTime, :PlanEndDate, :PlanEndTime, :ResourceId, :CalendarDetailId, :PlannedQuantity, :PerQuantity, :TimeUOM, :Remarks );"; // ── Phase 4: Routing detail resources ──────────────────────────────── // Load MROUTINGVERSIONDETAIL resources for a batch of RoutingDetailIds. public const string GET_ROUTING_DETAIL_RESOURCES_SQL = @" SELECT rvd.ROUTINGDETAILID, ISNULL(bor.WORKCENTERID,-1) AS WorkCenterId, ISNULL(bor.RESOURCEID,-1) AS ResourceId, ISNULL(bor.RESOURCETYPEID,-1) AS ResourceTypeId, ISNULL( CASE bor.TIMEUOM WHEN 0 THEN (bor.SETUPTIME/60.0)+(bor.CYCLETIME/60.0) WHEN 1 THEN bor.SETUPTIME+bor.CYCLETIME WHEN 2 THEN (bor.SETUPTIME*60.0)+(bor.CYCLETIME*60.0) ELSE bor.SETUPTIME+bor.CYCLETIME END, rvd.FIXEDLEADTIME ) AS Duration FROM MROUTINGVERSIONDETAIL rvd OUTER APPLY ( SELECT TOP 1 RESOURCEID, RESOURCETYPEID, WORKCENTERID, SETUPTIME, CYCLETIME, TIMEUOM FROM MBILLOFRESOURCE WHERE ROUTINGDETAILID = rvd.ROUTINGDETAILID AND TYPE = 4 ORDER BY SLNO ) bor WHERE rvd.ROUTINGDETAILID IN @RoutingDetailIds;"; public const string GET_ROUTING_DETAIL_RESOURCES_PG = @" SELECT rvd.ROUTINGDETAILID, COALESCE(rvd.WORKCENTERID, -1) AS ""WorkCenterId"", COALESCE(bor.MACHINEID, -1) AS ""MachineId"", COALESCE(bor.MACHINETYPEID, -1) AS ""MachineTypeId"", COALESCE( CASE bor.TIMEUOM WHEN 0 THEN (bor.SETUPTIME / 60.0) + (bor.CYCLETIME / 60.0) WHEN 1 THEN bor.SETUPTIME + bor.CYCLETIME WHEN 2 THEN (bor.SETUPTIME * 60.0) + (bor.CYCLETIME * 60.0) ELSE bor.SETUPTIME + bor.CYCLETIME END, rvd.FIXEDLEADTIME ) AS ""Duration"" FROM MROUTINGVERSIONDETAIL rvd LEFT JOIN LATERAL ( SELECT RESOURCEID, RESOURCETYPEID, SETUPTIME, CYCLETIME, TIMEUOM FROM MBILLOFRESOURCE WHERE ROUTINGDETAILID = rvd.ROUTINGDETAILID AND TYPE = 4 ORDER BY SLNO LIMIT 1 ) bor ON TRUE WHERE rvd.ROUTINGDETAILID = ANY(:RoutingDetailIds);"; // ── Phase 2: SO line for WO generation ─────────────────────────────── // PendingQty via TPENDINGALLOCATION — not a direct column on TMMDETAIL. public const string GET_SO_LINE_FOR_WO_SQL = @" SELECT d.DOCUMENTDETAILID AS MMDetailId, d.DOCUMENTID AS MMHeaderId, d.ITEMID, i.ITEMCODE, i.ITEMNAME, ISNULL(d.SKUID, -1) AS SkuId, d.TRANSACTIONQUANTITY, ISNULL(pa.PENDINGQUANTITY, 0) AS PendingQty, ISNULL(h.ALLOCATIONID, -1) AS AllocationId, h.PARTYID, h.OUID, h.DOCUMENTNUMBER AS SONumber, h.DOCUMENTDATE AS SODate FROM TMMDETAIL d JOIN TMMHEAD h ON h.DOCUMENTID = d.DOCUMENTID JOIN MITEM i ON i.ITEMID = d.ITEMID LEFT JOIN TPENDINGALLOCATION pa ON pa.OBJECTID = d.DOCUMENTDETAILID AND pa.OBJECTHEADERID = d.DOCUMENTID AND pa.OBJECTTYPEID = -1899997529 AND pa.OBJECTHEADERTYPEID = -1899997569 AND pa.PENDINGQUANTITY > 0 WHERE d.DOCUMENTDETAILID = @MMDetailId AND h.STATUS <> 2;"; public const string GET_SO_LINE_FOR_WO_PG = @"SELECT d.DOCUMENTDETAILID AS ""MMDetailId"", d.DOCUMENTID AS ""MMHeaderId"", d.ITEMID, i.ITEMCODE, i.ITEMNAME, COALESCE(d.SKUID, -1) AS ""SkuId"", d.TRANSACTIONQUANTITY, COALESCE(pa.PENDINGQUANTITY, 0) AS ""PendingQty"", COALESCE(h.ALLOCATIONID, -1) AS ""AllocationId"", h.PARTYID, h.OUID, h.DOCUMENTNUMBER AS ""SONumber"", h.DOCUMENTDATE AS ""SODate"" FROM TMMDETAIL d JOIN TMMHEAD h ON h.DOCUMENTID = d.DOCUMENTID JOIN MITEM i ON i.ITEMID = d.ITEMID LEFT JOIN TPENDINGALLOCATION pa ON pa.OBJECTID = d.DOCUMENTDETAILID AND pa.OBJECTHEADERID = d.DOCUMENTID AND pa.OBJECTTYPEID = -1899997529 AND pa.OBJECTHEADERTYPEID = -1899997569 AND pa.PENDINGQUANTITY > 0 WHERE d.DOCUMENTDETAILID = :MMDetailId AND h.STATUS <> 2;"; // ── Phase 3: Production plan line for WO generation ────────────────── public const string GET_PRODUCTION_PLAN_LINE_FOR_WO_SQL = @" SELECT ppd.PRODUCTIONPLANDETAILID AS ProductionPlanDetailId, ppd.PRODUCTIONPLANID, ppd.ITEMID, i.ITEMCODE, i.ITEMNAME, ISNULL(ppd.SKUID, -1) AS SkuId, ppd.TOTALBATCHQUANTITY, pp.OUID, pp.PRODUCTIONPLANDATE FROM TPRODUCTIONPLANDETAIL ppd JOIN TPRODUCTIONPLAN pp ON pp.PRODUCTIONPLANID = ppd.PRODUCTIONPLANID JOIN MITEM i ON i.ITEMID = ppd.ITEMID WHERE ppd.PRODUCTIONPLANDETAILID = @ProductionPlanDetailId AND pp.STATUS <> 2;"; public const string GET_PRODUCTION_PLAN_LINE_FOR_WO_PG = @"SELECT ppd.PRODUCTIONPLANDETAILID AS ""ProductionPlanDetailId"", ppd.PRODUCTIONPLANID, ppd.ITEMID, i.ITEMCODE, i.ITEMNAME, COALESCE(ppd.SKUID, -1) AS ""SkuId"", ppd.TOTALBATCHQUANTITY, pp.OUID, pp.PRODUCTIONPLANDATE FROM TPRODUCTIONPLANDETAIL ppd JOIN TPRODUCTIONPLAN pp ON pp.PRODUCTIONPLANID = ppd.PRODUCTIONPLANID JOIN MITEM i ON i.ITEMID = ppd.ITEMID WHERE ppd.PRODUCTIONPLANDETAILID = :ProductionPlanDetailId AND pp.STATUS <> 2;"; // ── Phase 6: Get item default routing ID ───────────────────────────── public const string GET_ITEM_DEFAULT_ROUTING_SQL = @" SELECT i.ROUTINGID FROM MITEM i WHERE i.ITEMID = @ItemId;"; public const string GET_ITEM_DEFAULT_ROUTING_PG = @" SELECT i.ROUTINGID AS ""RoutingId"" FROM MITEM i WHERE i.ITEMID = :ItemId;"; // ── Phase 2 (Production): WO Lifecycle ─────────────────────────────── public const string GET_INDENT_DETAIL_BY_ID_SQL = @" SELECT id.INDENTDETAILID, id.INDENTID, id.SLNO, id.ITEMID, id.SKUID, id.ROUTINGID, id.ROUTINGDETAILID, id.BOMID, id.MACHINEID, id.MACHINETYPEID, id.NESTINGPLANID, id.INDENTDETAILQUANTITY, id.PRIORITY, id.RELEASEDATE, id.ALLOCATIONID, id.RELEASESTATUS FROM TINDENTDETAIL id WHERE id.INDENTDETAILID = @IndentDetailId;"; public const string GET_INDENT_DETAIL_BY_ID_PG = @" SELECT id.INDENTDETAILID AS ""IndentDetailId"", id.INDENTID AS ""IndentId"", id.SLNO AS ""SlNo"", id.ITEMID AS ""ItemId"", COALESCE(id.SKUID, -1) AS ""SkuId"", COALESCE(id.ROUTINGID, -1) AS ""RoutingId"", COALESCE(id.ROUTINGDETAILID, -1) AS ""RoutingDetailId"", COALESCE(id.BOMID, -1) AS ""BomId"", COALESCE(id.MACHINEID, -1) AS ""MachineId"", COALESCE(id.MACHINETYPEID, -1) AS ""MachineTypeId"", COALESCE(id.NESTINGPLANID, -1) AS ""NestingPlanId"", id.INDENTDETAILQUANTITY AS ""IndentDetailQuantity"", id.PRIORITY AS ""IndentDetailPriority"", id.RELEASEDATE AS ""IndentDetailReleaseDate"", COALESCE(id.ALLOCATIONID, -1) AS ""AllocationId"", id.RELEASESTATUS AS ""ReleaseStatus"" FROM TINDENTDETAIL id WHERE id.INDENTDETAILID = :IndentDetailId;"; public const string UPDATE_INDENT_DETAIL_QTY_SQL = @" UPDATE TINDENTDETAIL SET INDENTDETAILQUANTITY = @NewQuantity WHERE INDENTDETAILID = @IndentDetailId;"; public const string UPDATE_INDENT_DETAIL_QTY_PG = @" UPDATE TINDENTDETAIL SET INDENTDETAILQUANTITY = :NewQuantity WHERE INDENTDETAILID = :IndentDetailId;"; public const string CLOSE_WORK_ORDER_SQL = @" UPDATE TINDENTDETAIL SET RELEASESTATUS = 6 WHERE INDENTDETAILID = @IndentDetailId;"; public const string CLOSE_WORK_ORDER_PG = @" UPDATE TINDENTDETAIL SET RELEASESTATUS = 6 WHERE INDENTDETAILID = :IndentDetailId;"; public const string SHORT_CLOSE_WORK_ORDER_SQL = @" UPDATE TINDENTDETAIL SET RELEASESTATUS = 7, INDENTDETAILQUANTITY = @ClosedQuantity WHERE INDENTDETAILID = @IndentDetailId;"; public const string SHORT_CLOSE_WORK_ORDER_PG = @" UPDATE TINDENTDETAIL SET RELEASESTATUS = 7, INDENTDETAILQUANTITY = :ClosedQuantity WHERE INDENTDETAILID = :IndentDetailId;"; public const string GET_WO_BOM_FEASIBILITY_SQL = @"SELECT bi.ITEMID, ISNULL(i.ITEMNAME, '') AS ItemName, bi.SKUID, bi.UOMID, ISNULL(u.UNITNAME, '') AS UomName, bi.QUANTITY * id.INDENTDETAILQUANTITY AS RequiredQuantity, ISNULL(sp.QUANTITY, 0) AS AvailableQuantity, CASE WHEN ISNULL(sp.QUANTITY, 0) >= (bi.QUANTITY * id.INDENTDETAILQUANTITY) THEN 1 ELSE 0 END AS IsFeasible, CASE WHEN ISNULL(sp.QUANTITY, 0) < (bi.QUANTITY * id.INDENTDETAILQUANTITY) THEN (bi.QUANTITY * id.INDENTDETAILQUANTITY) - ISNULL(sp.QUANTITY, 0) ELSE 0 END AS ShortfallQuantity FROM TINDENTDETAIL id INNER JOIN MBOMDETAIL bi ON bi.BOMID = id.BOMID INNER JOIN MITEM i ON i.ITEMID = bi.ITEMID LEFT JOIN MMEASUREUNIT u ON u.MEASUREUNITID = bi.UOMID LEFT JOIN TSTOCKPOSITION sp ON sp.ITEMID = bi.ITEMID AND sp.SKUID = bi.SKUID AND sp.STOREID = @StoreId WHERE id.INDENTDETAILID = @IndentDetailId;"; public const string GET_WO_BOM_FEASIBILITY_PG = @"SELECT bi.ITEMID AS ""ItemId"", COALESCE(i.ITEMNAME, '') AS ""ItemName"", COALESCE(bi.SKUID, -1) AS ""SkuId"", bi.UOMID AS ""UomId"", COALESCE(u.UNITNAME, '') AS ""UomName"", (bi.QUANTITY * id.INDENTDETAILQUANTITY) AS ""RequiredQuantity"", COALESCE(sp.QUANTITY, 0) AS ""AvailableQuantity"", CASE WHEN COALESCE(sp.QUANTITY, 0) >= (bi.QUANTITY * id.INDENTDETAILQUANTITY) THEN 1 ELSE 0 END AS ""IsFeasible"", CASE WHEN COALESCE(sp.QUANTITY, 0) < (bi.QUANTITY * id.INDENTDETAILQUANTITY) THEN (bi.QUANTITY * id.INDENTDETAILQUANTITY) - COALESCE(sp.QUANTITY, 0) ELSE 0 END AS ""ShortfallQuantity"" FROM TINDENTDETAIL id INNER JOIN MBOMDETAIL bi ON bi.BOMID = id.BOMID INNER JOIN MITEM i ON i.ITEMID = bi.ITEMID LEFT JOIN MMEASUREUNIT u ON u.MEASUREUNITID = bi.UOMID LEFT JOIN TSTOCKPOSITION sp ON sp.ITEMID = bi.ITEMID AND sp.SKUID = bi.SKUID AND sp.STOREID = :StoreId WHERE id.INDENTDETAILID = :IndentDetailId;"; public const string GET_SUPERVISOR_KPI_SQL = @" SELECT SUM(CASE WHEN RELEASESTATUS = 0 THEN 1 ELSE 0 END) AS Planned, SUM(CASE WHEN RELEASESTATUS = 1 THEN 1 ELSE 0 END) AS Released, SUM(CASE WHEN RELEASESTATUS = 2 THEN 1 ELSE 0 END) AS InProgress, SUM(CASE WHEN RELEASESTATUS = 3 THEN 1 ELSE 0 END) AS PendingResource, SUM(CASE WHEN RELEASESTATUS = 4 THEN 1 ELSE 0 END) AS PendingRelease, SUM(CASE WHEN RELEASESTATUS = 5 THEN 1 ELSE 0 END) AS Complete, SUM(CASE WHEN RELEASESTATUS = 6 THEN 1 ELSE 0 END) AS Closed, SUM(CASE WHEN RELEASESTATUS = 7 THEN 1 ELSE 0 END) AS ShortClosed, SUM(CASE WHEN RELEASESTATUS = 8 THEN 1 ELSE 0 END) AS Cancelled, COUNT(1) AS Total FROM TINDENTDETAIL id JOIN TINDENT i ON i.INDENTID = id.INDENTID WHERE i.OUID = @OUId;"; public const string GET_SUPERVISOR_KPI_PG = @" SELECT SUM(CASE WHEN RELEASESTATUS = 0 THEN 1 ELSE 0 END) AS ""Planned"", SUM(CASE WHEN RELEASESTATUS = 1 THEN 1 ELSE 0 END) AS ""Released"", SUM(CASE WHEN RELEASESTATUS = 2 THEN 1 ELSE 0 END) AS ""InProgress"", SUM(CASE WHEN RELEASESTATUS = 3 THEN 1 ELSE 0 END) AS ""PendingResource"", SUM(CASE WHEN RELEASESTATUS = 4 THEN 1 ELSE 0 END) AS ""PendingRelease"", SUM(CASE WHEN RELEASESTATUS = 5 THEN 1 ELSE 0 END) AS ""Complete"", SUM(CASE WHEN RELEASESTATUS = 6 THEN 1 ELSE 0 END) AS ""Closed"", SUM(CASE WHEN RELEASESTATUS = 7 THEN 1 ELSE 0 END) AS ""ShortClosed"", SUM(CASE WHEN RELEASESTATUS = 8 THEN 1 ELSE 0 END) AS ""Cancelled"", COUNT(1) AS ""Total"" FROM TINDENTDETAIL id JOIN TINDENT i ON i.INDENTID = id.INDENTID AND i.TENANTID = id.TENANTID WHERE i.OUID = :OUId;"; public const string GET_INDENT_BY_ID = @"SELECT -- ===================================== -- INDENT HEADER -- ===================================== I.INDENTID AS IndentId, I.BIZTRANSACTIONTYPEID AS BizTransactionTypeId, BTT.BIZTRANSACTIONTYPECODE AS BizTransactionTypeCode, BTT.BIZTRANSACTIONTYPENAME AS BizTransactionTypeName, I.OUID AS OUId, OU.ORGANIZATIONUNITCODE AS OUCode, OU.ORGANIZATIONUNITNAME AS OUName, I.PERIODID AS PeriodId, P.PERIODCODE AS PeriodCode, P.PERIODNAME AS PeriodName, I.INDENTDATE AS IndentDate, I.INDENTNUMBER AS IndentNumber, I.REFERENCENUMBER AS ReferenceNumber, I.INDENTNATURE AS IndentNature, I.PROCESSID AS ProcessId, PR.PROCESSCODE AS ProcessCode, PR.PROCESSNAME AS ProcessName, I.INDENTTYPE AS IndentType, I.PARTYBRANCHID AS PartyBranchId, PB.PARTYBRANCHCODE AS PartyBranchCode, PB.PARTYBRANCHNAME AS PartyBranchName, I.WORKCENTERID AS WorkCenterId, WC.WORKCENTERCODE AS WorkCenterCode, WC.WORKCENTERNAME AS WorkCenterName, I.EXPECTEDCOMPLETIONDATE AS ExpectedCompletionDate, I.REMARKS AS Remarks, -- ===================================== -- DETAIL(splitOn #1) -- ===================================== D.INDENTDETAILID AS IndentDetailId, D.INDENTID AS DetailIndentId, D.SLNO AS SlNo, D.ITEMID AS ItemId, D.SKUID AS SkuId, D.ROUTINGID AS RoutingId, D.ROUTINGDETAILID AS RoutingDetailId, D.BOMID AS BomId, D.MACHINEID AS MachineId, D.MACHINETYPEID AS MachineTypeId, D.NESTINGPLANID AS NestingPlanId, D.INDENTDETAILQUANTITY AS IndentDetailQuantity, D.PRIORITY AS IndentDetailPriority, D.RELEASEDATE AS IndentDetailReleaseDate, D.ALLOCATIONID AS AllocationId, -- ===================================== -- MATERIAL (splitOn #2) -- ===================================== M.INDENTMATERIALID AS IndentMaterialId, M.INDENTDETAILID AS MaterialIndentDetailId, M.ITEMID AS MaterialItemId, M.FINALQUANTITY AS FinalQuantity, M.UOMID AS UOMId, -- ===================================== -- RESOURCE (splitOn #3) -- ===================================== R.INDENTRESOURCEID AS IndentResourceId, R.INDENTDETAILID AS ResourceIndentDetailId, R.RESOURCEID AS WorkCenterId, R.MACHINEID AS ResourceMachineId, R.RESOURCETYPEID AS MachineTypeId, R.PLANDURATION AS Duration, -- ===================================== -- DUMMY (splitOn #4) -- ===================================== 1 AS DummyId FROM TINDENT I -- Header Masters LEFT JOIN MBIZTRANSACTIONTYPE BTT ON BTT.BIZTRANSACTIONTYPEID = I.BIZTRANSACTIONTYPEID LEFT JOIN MORGANIZATIONUNIT OU ON OU.OUID = I.OUID LEFT JOIN MPERIOD P ON P.PERIODID = I.PERIODID LEFT JOIN MPROCESS PR ON PR.PROCESSID = I.PROCESSID LEFT JOIN MPARTYBRANCH PB ON PB.PARTYBRANCHID = I.PARTYBRANCHID LEFT JOIN MWORKCENTER WC ON WC.WORKCENTERID = I.WORKCENTERID LEFT JOIN MEMPLOYEE EMP ON EMP.EMPLOYEEID = I.RECEIVEDBYID LEFT JOIN MGCM CR ON CR.GCMID = I.CHANGEREASONID LEFT JOIN MUSER UC ON UC.USERID = I.CREATEDBYID LEFT JOIN MUSER UM ON UM.USERID = I.MODIFIEDBYID -- Detail LEFT JOIN TINDENTDETAIL D ON D.INDENTID = I.INDENTID LEFT JOIN MENTITY LT ON LT.ENTITYID = D.LINKTYPEID LEFT JOIN TMMDETAIL MMD ON MMD.DOCUMENTDETAILID = D.MMDETAILDOCUMENTDETAILID LEFT JOIN MITEM MMDITEM ON MMDITEM.ITEMID = MMD.ITEMID LEFT JOIN MSKU SKU ON SKU.SKUID = D.SKUID LEFT JOIN MPROCESS DPR ON DPR.PROCESSID = D.PROCESSGROUPID LEFT JOIN TTASK TT ON TT.TASKID = D.TASKID LEFT JOIN TCALL TC ON TC.CALLID = D.CALLID LEFT JOIN MITEM DITEM ON DITEM.ITEMID = D.ITEMID -- Material LEFT JOIN TINDENTMATERIAL M ON M.INDENTDETAILID = D.INDENTDETAILID LEFT JOIN MPROCESS MPR ON MPR.PROCESSID = M.PROCESSID LEFT JOIN MITEM PFI ON PFI.ITEMID = M.PROCESSFORITEMID LEFT JOIN MITEM MITEM ON MITEM.ITEMID = M.ITEMID LEFT JOIN MUOM U ON U.UOMID = M.UOMID -- Resource LEFT JOIN TINDENTRESOURCE R ON R.INDENTDETAILID = D.INDENTDETAILID WHERE I.INDENTID = @IndentId ORDER BY I.INDENTID, D.INDENTDETAILID, M.INDENTMATERIALID, R.INDENTRESOURCEID;"; public const string SAVE_INDENT = @"INSERT INTO TINDENT ( INDENTID, BIZTRANSACTIONTYPEID, OUID, PERIODID, INDENTDATE, INDENTNUMBER, REFERENCEDATE, REFERENCENUMBER, NATURE, PROCESSID, PARTYBRANCHID, WORKCENTERID, PLANNEDNUMBEROFMACHINES, ISBATCHREQUIRED, EXPECTEDFIRSTDELIVERYDATE, EXPECTEDCOMPLETIONDATE, RECEIVEDBYID, RECEIVEDON, REMARKS, CHANGEREASONID, CHANGEREMARKS, INDENTTYPE, STATUS, VERSION, CREATEDBYID, CREATEDON, MODIFIEDBYID, MODIFIEDON, COMBINEINDENTID, AMENDMENTSLNO, INDENTNATURE, MATRIXID ) VALUES ( @IndentId, @BizTransactionTypeId, @OUId, @PeriodId, @IndentDate, @IndentNumber, @ReferenceDate, @ReferenceNumber, @Nature, @ProcessId, @PartyBranchId, @WorkCenterId, @PlannedNumberOfMachines, @IsBatchRequired, @ExpectedFirstDeliveryDate, @ExpectedCompletionDate, @ReceivedById, @ReceivedOn, @Remarks, @ChangeReasonId, @ChangeRemarks, @IndentType, @Status, @Version, @CreatedById, @CreatedOn, @ModifiedById, @ModifiedOn, @CombineIndentId, @AmendmentSlNo, @IndentNature, @MatrixId );"; public const string SAVE_INDENT_DETAIL = @"INSERT INTO TINDENTDETAIL ( INDENTDETAILID, INDENTID, SLNO, LINKTYPEID, MMDETAILDOCUMENTDETAILID, INDENTDETAILDOCUMENTID, PROCESSGROUPID, WORKPLANDETAILID, SKUID, ROUTINGID, PLANNEDQUANTITY, RATE, PRIORITY, REMARKS, INDENTBEAMTYPE, REVISEDWIDTH, MACHINETYPEID, MACHINEID, BEAMLOTID, METERSPERPIECE, WEAVEPICK, WEAVEWIDTH, ITEMID, RELEASEDATE, RELEASESTATUS, NESTINGPLANID, ALLOCATIONID, CALLID, TASKID, GENERATEALLOCATIONID, ROUTINGDETAILID, BOMID, INDENTDETAILQUANTITY, ALLOTEDGOODQUANTITY, ALLOTEDREWORKQUANTITY, NEXTLEVELQUANTITY, PERQUANTITY ) VALUES ( @IndentDetailId, @IndentId, @SlNo, @LinkTypeId, @MMDetailDocumentDetailId, @IndentDetailDocumentId, @ProcessGroupId, @WorkPlanDetailId, @SkuId, @RoutingId, @PlannedQuantity, @Rate, @Priority, @Remarks, @IndentBeamType, @RevisedWidth, @MachineTypeId, @MachineId, @BeamLotId, @MetersPerPiece, @WeavePick, @WeaveWidth, @ItemId, @IndentDetailReleaseDate, @ReleaseStatus, @NestingPlanId, @AllocationId, @CallId, @TaskId, @GenerateAllocationId, @RoutingDetailId, @BomId, @IndentDetailQuantity, @AllotedGoodQuantity, @AllotedReworkQuantity, @NextLevelQuantity, @PerQuantity );"; public const string SAVE_INDENT_MATERIAL = @"INSERT INTO MMINDENTMATERIAL ( INDENTMATERIALID, INDENTDETAILID, SLNO, PROCESSID, LEVEL, PROCESSFORITEMID, PROCESSFORQTY, MATERIALSLNO, ITEMID, TYPE, SKUID, REQUIREDQUANTITY, MATERIALREQUIREDPER, TOTALQUANTITY, WASTEPERCENTAGE, NETQUANTITY, UOMID, REMARKS, ADJUSTQUANTITY, FINALQUANTITY, TYPEOFDATA, ENDS, REJECTEDQUANTITY, REWORKQUANTITY, OTHERQUANTITY ) VALUES ( @IndentMaterialId, @IndentDetailId, @SlNo, @ProcessId, @Level, @ProcessForItemId, @ProcessForQty, @MaterialSlNo, @ItemId, @Type, @SkuId, @RequiredQuantity, @MaterialRequiredPer, @TotalQuantity, @WastePercentage, @NetQuantity, @UOMId, @Remarks, @AdjustQuantity, @FinalQuantity, @TypeOfData, @Ends, @RejectedQuantity, @ReworkQuantity, @OtherQuantity );"; public const string UPDATE_INDENT = @"UPDATE TINDENT SET BIZTRANSACTIONTYPEID = @BizTransactionTypeId, OUID = @OUId, PERIODID = @PeriodId, INDENTDATE = @IndentDate, INDENTNUMBER = @IndentNumber, REFERENCEDATE = @ReferenceDate, REFERENCENUMBER = @ReferenceNumber, NATURE = @Nature, PROCESSID = @ProcessId, PARTYBRANCHID = @PartyBranchId, WORKCENTERID = @WorkCenterId, PLANNEDNUMBEROFMACHINES = @PlannedNumberOfMachines, ISBATCHREQUIRED = @IsBatchRequired, EXPECTEDFIRSTDELIVERYDATE = @ExpectedFirstDeliveryDate, EXPECTEDCOMPLETIONDATE = @ExpectedCompletionDate, RECEIVEDBYID = @ReceivedById, RECEIVEDON = @ReceivedOn, REMARKS = @Remarks, CHANGEREASONID = @ChangeReasonId, CHANGEREMARKS = @ChangeRemarks, INDENTTYPE = @IndentType, STATUS = @Status, VERSION = @Version, MODIFIEDBYID = @ModifiedById, MODIFIEDON = @ModifiedOn, COMBINEINDENTID = @CombineIndentId, AMENDMENTSLNO = @AmendmentSlNo, INDENTNATURE = @IndentNature, MATRIXID = @MatrixId WHERE INDENTID = @IndentId;"; public const string UPDATE_INDENT_DETAIL = @"UPDATE TINDENTDETAIL SET INDENTID = @IndentId, SLNO = @SlNo, LINKTYPEID = @LinkTypeId, MMDETAILDOCUMENTDETAILID = @MMDetailDocumentDetailId, INDENTDETAILDOCUMENTID = @IndentDetailDocumentId, PROCESSGROUPID = @ProcessGroupId, WORKPLANDETAILID = @WorkPlanDetailId, SKUID = @SkuId, ROUTINGID = @RoutingId, PLANNEDQUANTITY = @PlannedQuantity, RATE = @Rate, PRIORITY = @Priority, REMARKS = @Remarks, INDENTBEAMTYPE = @IndentBeamType, REVISEDWIDTH = @RevisedWidth, MACHINETYPEID = @MachineTypeId, MACHINEID = @MachineId, BEAMLOTID = @BeamLotId, METERSPERPIECE = @MetersPerPiece, WEAVEPICK = @WeavePick, WEAVEWIDTH = @WeaveWidth, ITEMID = @ItemId, RELEASEDATE = @IndentDetailReleaseDate, RELEASESTATUS = @ReleaseStatus, NESTINGPLANID = @NestingPlanId, ALLOCATIONID = @AllocationId, CALLID = @CallId, TASKID = @TaskId, GENERATEALLOCATIONID = @GenerateAllocationId, ROUTINGDETAILID = @RoutingDetailId, BOMID = @BomId, INDENTDETAILQUANTITY = @IndentDetailQuantity, ALLOTEDGOODQUANTITY = @AllotedGoodQuantity, ALLOTEDREWORKQUANTITY = @AllotedReworkQuantity, NEXTLEVELQUANTITY = @NextLevelQuantity, PERQUANTITY = @PerQuantity WHERE INDENTDETAILID = @IndentDetailId;"; public const string UPDATE_INDENT_MATERIAL = @"UPDATE TINDENTDETAIL SET INDENTID = @IndentId, SLNO = @SlNo, LINKTYPEID = @LinkTypeId, MMDETAILDOCUMENTDETAILID = @MMDetailDocumentDetailId, INDENTDETAILDOCUMENTID = @IndentDetailDocumentId, PROCESSGROUPID = @ProcessGroupId, WORKPLANDETAILID = @WorkPlanDetailId, SKUID = @SkuId, ROUTINGID = @RoutingId, PLANNEDQUANTITY = @PlannedQuantity, RATE = @Rate, PRIORITY = @Priority, REMARKS = @Remarks, INDENTBEAMTYPE = @IndentBeamType, REVISEDWIDTH = @RevisedWidth, MACHINETYPEID = @MachineTypeId, MACHINEID = @MachineId, BEAMLOTID = @BeamLotId, METERSPERPIECE = @MetersPerPiece, WEAVEPICK = @WeavePick, WEAVEWIDTH = @WeaveWidth, ITEMID = @ItemId, RELEASEDATE = @IndentDetailReleaseDate, RELEASESTATUS = @ReleaseStatus, NESTINGPLANID = @NestingPlanId, ALLOCATIONID = @AllocationId, CALLID = @CallId, TASKID = @TaskId, GENERATEALLOCATIONID = @GenerateAllocationId, ROUTINGDETAILID = @RoutingDetailId, BOMID = @BomId, INDENTDETAILQUANTITY = @IndentDetailQuantity, ALLOTEDGOODQUANTITY = @AllotedGoodQuantity, ALLOTEDREWORKQUANTITY = @AllotedReworkQuantity, NEXTLEVELQUANTITY = @NextLevelQuantity, PERQUANTITY = @PerQuantity WHERE INDENTDETAILID = @IndentDetailId"; public const string GET_LIST_OF_INDENT = @"SELECT -- ============================================================ -- INDENT -- ============================================================ I.INDENTID AS IndentId, I.COMBINEINDENTID AS CombineIndentId, I.AMENDMENTSLNO AS IndentAmendmentSlNo, I.BIZTRANSACTIONTYPEID AS BizTransactionTypeId, BTT.BIZTRANSACTIONTYPECODE AS BizTransactionTypeCode, BTT.BIZTRANSACTIONTYPENAME AS BizTransactionTypeName, BTT.BIZTRANSACTIONCLASSID AS BizTransactionTypeBIZTransactionTypeClassId, BTTC.BIZTRANSACTIONCLASSNAME AS BizTransactionTypeBIZTransactionTypeClassName, BTT.NOGENERATIONTYPE AS BizTransactionTypeNOGenerationType, I.OUID AS OUId, OU.ORGANIZATIONUNITCODE AS OUCode, OU.ORGANIZATIONUNITNAME AS OUName, I.PERIODID AS PeriodId, P.PERIODCODE AS PeriodCode, P.PERIODNAME AS PeriodName, P.FROMDATE AS PeriodFromDate, P.TODATE AS PeriodToDate, I.INDENTDATE AS IndentIndentDate, I.INDENTNUMBER AS IndentIndentNumber, I.REFERENCENUMBER AS IndentReferenceNumber, I.REFERENCEDATE AS IndentReferenceDate, I.INDENTNATURE AS IndentNature, I.PROCESSID AS ProcessId, PR.PROCESSCODE AS ProcessCode, PR.PROCESSNAME AS ProcessName, I.INDENTTYPE AS IndentIndentType, I.PARTYBRANCHID AS PartyBranchId, PB.PARTYBRANCHCODE AS PartyBranchCode, PB.PARTYBRANCHNAME AS PartyBranchName, PB.PARTYID AS PartyBranchPartyBranchPartyId, PA.PARTYNAME AS PartyBranchPartyBranchPartyName, I.WORKCENTERID AS WorkCenterId, WC.WORKCENTERCODE AS WorkCenterCode, WC.WORKCENTERNAME AS WorkCenterName, WC.DEPARTMENTID AS WorkCenterDepartmentId, D.DEPARTMENTCODE AS WorkCenterDepartmentCode, D.DEPARTMENTNAME AS WorkCenterDepartmentName, WC.WORKINGMINUTES AS WorkCenterWorkingMinutes, WC.STARTTIME AS WorkCenterStartTime, WC.CALENDARID AS WorkCenterCalendarId, CAL.CALENDARCODE AS WorkCenterCalendarCode, CAL.CALENDARNAME AS WorkCenterCalendarName, I.PLANNEDNUMBEROFMACHINES AS IndentPlannedNumberOfMachine, I.ISBATCHREQUIRED AS IndentIsBatchRequired, I.EXPECTEDFIRSTDELIVERYDATE AS IndentExpectedFirstDeliveryDate, I.EXPECTEDCOMPLETIONDATE AS IndentExpectedCompletionDate, I.RECEIVEDBYID AS ReceivedbyId, U.USERCODE AS ReceivedbyCode, U.USERNAME AS ReceivedbyName, I.CHANGEREASONID AS ChangeReasonId, CR.GCMCODE AS ChangeReasonCode, CR.GCMNAME AS ChangeReasonName, I.RECEIVEDON AS IndentReceivedOn, I.REMARKS AS IndentRemarks, I.CHANGEREMARKS AS IndentChangeRemarks, I.CREATEDBYID AS IndentCreatedById, UC.USERNAME AS IndentCreatedByName, I.CREATEDON AS IndentCreatedOn, I.MODIFIEDBYID AS IndentModifiedById, UM.USERNAME AS IndentModifiedByName, I.MODIFIEDON AS IndentModifiedOn, I.STATUS AS IndentStatus, I.VERSION AS IndentVersion, -- ============================================================ -- INDENT DETAIL -- ============================================================ ID.INDENTDETAILID AS IndentDetailId, ID.INDENTID AS DetailIndentId, ID.LINKTYPEID AS LinkTypeId, ENT.ENTITYNAME AS LinkTypeName, ID.MMDETAILDOCUMENTDETAILID AS MMDetailDocumentDetailId, ID.ITEMID AS DetailItemId, IDITEM.ITEMCODE AS DetailItemCode, IDITEM.ITEMNAME AS DetailItemName, ID.WORKPLANDETAILID AS WorkPlanDetailId, ID.NESTINGPLANID AS NestingPlanId, ID.ALLOCATIONID AS AllocationId, AL.ALLOCATIONNAME AS AllocationName, ID.BOMID AS BomId, ID.PRIORITY AS Priority, ID.INDENTBEAMTYPE AS IndentBeamType, ID.RELEASESTATUS AS ReleaseStatus, ID.GENERATEALLOCATIONID AS GenerateAllocationId, -- ============================================================ -- INDENT MATERIAL -- ============================================================ IM.INDENTMATERIALID AS IndentMaterialId, IM.INDENTDETAILID AS MaterialIndentDetailId, IM.SLNO AS IndentMaterialSlNo, IM.PROCESSID AS MaterialProcessId, IMP.PROCESSCODE AS MaterialProcessCode, IMP.PROCESSNAME AS MaterialProcessName, IM.LEVEL AS IndentMaterialLevel, IM.PROCESSFORITEMID AS ProcessForItemId, PFI.ITEMCODE AS ProcessForItemCode, PFI.ITEMNAME AS ProcessForItemName, IM.PROCESSFORQTY AS IndentMaterialProcessForQty, IM.MATERIALSLNO AS IndentMaterialMaterialSlNo, IM.ITEMID AS MaterialItemId, MI.ITEMCODE AS MaterialItemCode, MI.ITEMNAME AS MaterialItemName, IM.TYPE AS IndentMaterialType, IM.SKUID AS SKUId, SKU.SKUNAME AS SKUName, IM.REQUIREDQUANTITY AS IndentMaterialRequiredQuantity, IM.MATERIALREQUIREDPER AS IndentMaterialMaterialRequiredPer, IM.TOTALQUANTITY AS IndentMaterialTotalQuantity, IM.WASTEPERCENTAGE AS IndentMaterialWastePercentage, IM.NETQUANTITY AS IndentMaterialNetQuantity, IM.UOMID AS UOMId, UOM.UOMCODE AS UOMCode, UOM.UOMNAME AS UOMName, IM.REMARKS AS IndentMaterialRemarks, IM.ADJUSTQUANTITY AS IndentMaterialAdjustQuantity, IM.FINALQUANTITY AS IndentMaterialFinalQuantity, IM.TYPEOFDATA AS IndentMaterialTypeOfData, IM.ENDS AS IndentMaterialEnds, IM.REJECTEDQUANTITY AS IndentMaterialRejectedQuantity, IM.REWORKQUANTITY AS IndentMaterialReworkQuantity, IM.OTHERQUANTITY AS IndentMaterialOtherQuantity, -- ============================================================ -- INDENT RESOURCE -- ============================================================ IR.INDENTRESOURCEID AS IndentResourceId, IR.INDENTDETAILID AS ResourceIndentDetailId, IR.SLNO AS IndentResourceSlNo, IR.BILLOFRESOURCEID AS BillofResourceId, IR.RESOURCETYPEID AS ResourceTypeId, RT.RESOURCETYPECODE AS ResourceTypeCode, RT.RESOURCETYPENAME AS ResourceTypeName, RT.MACHINETYPEID AS ResourceTypeMachineTypeId, MT.MACHINETYPECODE AS ResourceTypeMachineTypeCode, MT.MACHINETYPENAME AS ResourceTypeMachineTypeName, RT.EMPLOYEETYPEID AS ResourceTypeEmployeeTypeId, ET.GCMCODE AS ResourceTypeEmployeeTypeCode, ET.GCMNAME AS ResourceTypeEmployeeTypeName, RT.RESOURCENATURE AS ResourceTypeResourceNature, IR.MACHINEID AS MachineId, M.MACHINECODE AS MachineCode, M.MACHINENAME AS MachineName, IR.EMPLOYEEID AS EmployeeId, E.EMPLOYEECODE AS EmployeeCode, E.EMPLOYEENAME AS EmployeeName, IR.PLANDURATION AS IndentResourcePlanDuration, IR.PLANSTARTDATE AS IndentResourcePlanStartDate, IR.PLANSTARTTIME AS IndentResourcePlanStartTime, IR.PLANENDDATE AS IndentResourcePlanEndDate, IR.PLANENDTIME AS IndentResourcePlanEndTime, IR.RESOURCEID AS ResourceId, R.RESOURCECODE AS ResourceCode, R.RESOURCENAME AS ResourceName, IR.CALENDARDETAILID AS CalendarDetailId, CD.DETAILTYPE AS CalendarDetailType, IR.PLANNEDQUANTITY AS IndentResourcePlannedQuantity, IR.PERQUANTITY AS IndentResourcePERQuantity, IR.TIMEUOM AS IndentResourceTimeUOM, IR.REMARKS AS IndentResourceRemarks, -- ============================================================ -- DUMMY MAPPING -- Required because QueryMultiMapAsync has 5 mapped types -- ============================================================ CAST(NULL AS INT) AS DummyId FROM TINDENT I -- ============================================================ -- INDENT MASTER JOINS -- ============================================================ LEFT JOIN MBIZTRANSACTIONTYPE BTT ON I.BIZTRANSACTIONTYPEID = BTT.BIZTRANSACTIONTYPEID LEFT JOIN MBIZTRANSACTIONCLASS BTTC ON BTT.BIZTRANSACTIONCLASSID = BTTC.BIZTRANSACTIONCLASSID LEFT JOIN MORGANIZATIONUNIT OU ON I.OUID = OU.OUID LEFT JOIN MPERIOD P ON I.PERIODID = P.PERIODID LEFT JOIN MPROCESS PR ON I.PROCESSID = PR.PROCESSID LEFT JOIN MPARTYBRANCH PB ON I.PARTYBRANCHID = PB.PARTYBRANCHID LEFT JOIN MPARTY PA ON PB.PARTYID = PA.PARTYID LEFT JOIN MWORKCENTER WC ON I.WORKCENTERID = WC.WORKCENTERID LEFT JOIN MDEPARTMENT D ON WC.DEPARTMENTID = D.DEPARTMENTID LEFT JOIN MCALENDAR CAL ON WC.CALENDARID = CAL.CALENDARID LEFT JOIN MUSER U ON I.RECEIVEDBYID = U.USERID LEFT JOIN MGCM CR ON I.CHANGEREASONID = CR.GCMID LEFT JOIN MUSER UC ON I.CREATEDBYID = UC.USERID LEFT JOIN MUSER UM ON I.MODIFIEDBYID = UM.USERID -- ============================================================ -- INDENT DETAIL -- ============================================================ LEFT JOIN TINDENTDETAIL ID ON I.INDENTID = ID.INDENTID LEFT JOIN MENTITY ENT ON ENT.ENTITYID = ID.LINKTYPEID LEFT JOIN MITEM IDITEM ON IDITEM.ITEMID = ID.ITEMID LEFT JOIN MALLOCATION AL ON AL.ALLOCATIONID = ID.ALLOCATIONID -- ============================================================ -- INDENT MATERIAL -- ============================================================ LEFT JOIN TINDENTMATERIAL IM ON ID.INDENTDETAILID = IM.INDENTDETAILID LEFT JOIN MPROCESS IMP ON IMP.PROCESSID = IM.PROCESSID LEFT JOIN MITEM PFI ON PFI.ITEMID = IM.PROCESSFORITEMID LEFT JOIN MITEM MI ON MI.ITEMID = IM.ITEMID LEFT JOIN MSKU SKU ON SKU.SKUID = IM.SKUID LEFT JOIN MUOM UOM ON UOM.UOMID = IM.UOMID -- ============================================================ -- INDENT RESOURCE -- ============================================================ LEFT JOIN TINDENTRESOURCE IR ON ID.INDENTDETAILID = IR.INDENTDETAILID LEFT JOIN MBILLOFRESOURCE BOR ON BOR.BILLOFRESOURCEID = IR.BILLOFRESOURCEID LEFT JOIN MRESOURCETYPE RT ON RT.RESOURCETYPEID = IR.RESOURCETYPEID LEFT JOIN MMACHINETYPE MT ON MT.MACHINETYPEID = RT.MACHINETYPEID LEFT JOIN MGCM ET ON ET.GCMID = RT.EMPLOYEETYPEID LEFT JOIN MMACHINE M ON M.MACHINEID = IR.MACHINEID LEFT JOIN MEMPLOYEE E ON E.EMPLOYEEID = IR.EMPLOYEEID LEFT JOIN MRESOURCE R ON R.RESOURCEID = IR.RESOURCEID LEFT JOIN MCALENDARDETAIL CD ON CD.CALENDARDETAILID = IR.CALENDARDETAILID ORDER BY I.INDENTID, ID.INDENTDETAILID, IM.SLNO, IM.MATERIALSLNO, IR.SLNO;"; // ── Phase 7: Project-wise shortage report (GB4 port: IndentQueryBuilder.PROJECT_WISE_SHORTAGE) ── // // Indexes recommended: // TINDENTMATERIAL: IX_TINDENTMATERIAL_ITEM_SKU (ITEMID, SKUID, INDENTDETAILID, TENANTID) // TINDENTDETAIL: IX_TINDENTDETAIL_ALLOCATION (ALLOCATIONID, ITEMID, NESTINGPLANID, TENANTID) // TPENDINGALLOCATION: IX_TPENDINGALLOC_OBJECT (OBJECTID, OBJECTTYPEID, OBJECTHEADERID, OBJECTHEADERTYPEID) // TSTOCKPOSITION: IX_TSTOCKPOSITION_ITEM_SKU (ITEMID, SKUID, STOREID, OUID) // TMMDETAIL: IX_TMMDETAIL_ITEM (ITEMID, DOCUMENTID) // MITEMSTOCK: IX_MITEMSTOCK_ITEM_SKU_STORE (ITEMID, SKUID, STOREID) // // Adaptation notes (see task spec — do not "fix" back to GB4's anti-patterns): // • GB4's #projectlist temp table (populated from dbo.comma2row) is replaced with a // STRING_SPLIT-based parameter (@ProjectItemIds, comma-separated int list) — no temp // tables, no dynamic SQL string concatenation of criteria values. // • GB4's `VpendingAllocationDetail` / `vpendingallocationdetail` VIEW does not exist in // GB5. Verified via MMDAL/Query/Allocation/AllocationQB.cs (which already queries // TPENDINGALLOCATION directly in this codebase) that the base TPENDINGALLOCATION table // exposes the exact columns the GB4 view exposed: OBJECTID, OBJECTTYPEID, OBJECTHEADERID, // OBJECTHEADERTYPEID, PENDINGQUANTITY, ALLOCATIONNATURE. Ported to query TPENDINGALLOCATION // directly — this substitution is verified, not guessed. // • GB4's pl.SlNo (temp-table insert order) has no STRING_SPLIT equivalent — dropped from // ORDER BY per task instructions; ProjectWiseShortageDTO.SlNo is left at its default (0). // • All optional filters (OUId, WorkCenterId, ProcessId, AllocationId, IndentId, SKUId, Type) // are true bound parameters using an "always-bound sentinel" pattern — e.g. // "(@OUId = -1 OR a.OUID = @OUId)" — rather than conditionally splicing SQL text per request. // GB4 also supported InchargeId/PlannerId/BuyerId/IsMakeOrBuy/BIZTransactionId filters; // these were intentionally NOT ported (out of scope per the task's explicit field list). // // FLAGGED AS UNVERIFIED — could not confirm against docs/GB5-Schema-Registry.md or any // existing QB file in this codebase (ported verbatim from GB4; verify against live schema): // MSTORE.ISPLANNING, MBIZTRANSACTIONTYPE.STOREID // private const string PROJECT_WISE_SHORTAGE_INNER_SQL = @" SELECT b.ALLOCATIONID AS AllocationId, m.ALLOCATIONNAME AS AllocationName, m.DESCRIPTION AS AllocationDescription, b.ITEMID AS OutputItemId, CASE WHEN b.NESTINGPLANID <> -1 THEN np.NESTINGPLANCODE ELSE z.ITEMCODE END AS OutputItemCode, CASE WHEN b.NESTINGPLANID <> -1 THEN np.REMARKS ELSE z.ITEMNAME END AS OutputItemName, a.INDENTID AS IndentId, a.INDENTNUMBER AS WorkOrderNumber, a.INDENTDATE AS WorkOrderDate, a.EXPECTEDFIRSTDELIVERYDATE AS FirstDeliveryDate, d.ITEMID AS ItemId, d.ITEMCODE AS ItemCode, d.ITEMNAME AS ItemName, e.SKUID AS SKUId, e.SKUCODE AS SKUCode, e.SKUNAME AS SKUName, CASE WHEN d.ISMAKEORBUY = 0 THEN 'MAKE' ELSE 'BUY' END AS ISMakeOrBuy, ABS(c.FINALQUANTITY) AS RequiredQuantity, ABS(CASE WHEN vv.PENDINGQUANTITY IS NULL THEN c.FINALQUANTITY ELSE (c.FINALQUANTITY - vv.PENDINGQUANTITY) END) AS IssuedQuantity, ISNULL(vv.PENDINGQUANTITY, 0) AS PendingQuantity, ISNULL(SUBSTK.STOCKQUANTITY, 0) AS StockQuantity, ISNULL(totalpending.PENDINGQUANTITY, 0) AS TotalPending, ISNULL(po.POPending, 0) AS POPending, vpallocdetail.PENDINGQUANTITY AS PendingWorkOrder, ISNULL(totalpending.PENDINGQUANTITY, 0) - ISNULL(SUBSTK.STOCKQUANTITY, 0) AS Shortage, f.UOMID AS UOMId, f.UOMCODE AS UOMCode, f.UOMNAME AS UOMName, ISNULL(l.BINNUMBER, '') AS BINNumber, g.WORKCENTERID AS WorkCenterId, g.WORKCENTERNAME AS WorkCenterName, h.PROCESSID AS ProcessId, h.PROCESSCODE AS ProcessCode, h.PROCESSNAME AS ProcessName FROM TINDENT a CROSS JOIN (SELECT CAST(value AS INT) AS ITEMID FROM STRING_SPLIT(@ProjectItemIds, ',')) PL LEFT OUTER JOIN TINDENTDETAIL b ON a.INDENTID = b.INDENTID LEFT OUTER JOIN TINDENTMATERIAL c ON b.INDENTDETAILID = c.INDENTDETAILID LEFT OUTER JOIN TPENDINGALLOCATION vpallocdetail ON c.INDENTDETAILID = vpallocdetail.OBJECTID AND b.RELEASESTATUS NOT IN (4,5,7) AND vpallocdetail.OBJECTTYPEID = -1899997529 AND vpallocdetail.PENDINGQUANTITY <> 0 AND vpallocdetail.ALLOCATIONNATURE IN (1) LEFT OUTER JOIN ( SELECT sp.OUID, sp.ITEMID, sp.SKUID, SUM(sp.QUANTITY) AS STOCKQUANTITY FROM TSTOCKPOSITION sp JOIN MSTORE ms ON sp.STOREID = ms.STOREID WHERE ms.ISPLANNING = 0 GROUP BY sp.OUID, sp.ITEMID, sp.SKUID ) SUBSTK ON SUBSTK.ITEMID = c.ITEMID AND SUBSTK.SKUID = c.SKUID AND SUBSTK.OUID = a.OUID LEFT OUTER JOIN MBIZTRANSACTIONTYPE k ON k.BIZTRANSACTIONTYPEID = a.BIZTRANSACTIONTYPEID LEFT OUTER JOIN MITEMSTOCK l ON c.SKUID = l.SKUID AND c.ITEMID = l.ITEMID AND l.STOREID = k.STOREID LEFT OUTER JOIN ( SELECT d2.INDENTMATERIALID, d2.ITEMID, d2.SKUID, a2.PENDINGQUANTITY AS PENDINGQUANTITY FROM TPENDINGALLOCATION a2 JOIN TINDENT b2 ON a2.OBJECTHEADERID = b2.INDENTID JOIN TINDENTDETAIL c2 ON b2.INDENTID = c2.INDENTID JOIN TINDENTMATERIAL d2 ON c2.INDENTDETAILID = d2.INDENTDETAILID WHERE a2.OBJECTID = d2.INDENTMATERIALID AND a2.OBJECTTYPEID = -1899997494 AND b2.STATUS IN (0,1) AND d2.TYPEOFDATA = 0 AND (@PeriodFromDate IS NULL OR b2.EXPECTEDFIRSTDELIVERYDATE >= @PeriodFromDate) AND (@PeriodToDate IS NULL OR b2.EXPECTEDFIRSTDELIVERYDATE <= @PeriodToDate) ) vv ON c.INDENTMATERIALID = vv.INDENTMATERIALID AND c.ITEMID = vv.ITEMID AND c.SKUID = vv.SKUID LEFT OUTER JOIN ( SELECT d3.ITEMID, d3.SKUID, SUM(a3.PENDINGQUANTITY) AS PENDINGQUANTITY FROM TPENDINGALLOCATION a3 JOIN TINDENT b3 ON a3.OBJECTHEADERID = b3.INDENTID JOIN TINDENTDETAIL c3 ON b3.INDENTID = c3.INDENTID JOIN TINDENTMATERIAL d3 ON c3.INDENTDETAILID = d3.INDENTDETAILID WHERE a3.OBJECTID = d3.INDENTMATERIALID AND a3.OBJECTTYPEID = -1899997494 AND b3.STATUS IN (0,1) AND d3.TYPEOFDATA = 0 AND (@PeriodFromDate IS NULL OR b3.EXPECTEDFIRSTDELIVERYDATE >= @PeriodFromDate) AND (@PeriodToDate IS NULL OR b3.EXPECTEDFIRSTDELIVERYDATE <= @PeriodToDate) GROUP BY d3.ITEMID, d3.SKUID ) totalpending ON c.ITEMID = totalpending.ITEMID AND c.SKUID = totalpending.SKUID LEFT OUTER JOIN ( SELECT mmdetail.ITEMID AS ITEMID, SUM(pendingallocation.PENDINGQUANTITY) AS POPending FROM TMMHEAD mmhead JOIN MBIZTRANSACTIONTYPE biztransactiontype ON mmhead.BIZTRANSACTIONTYPEID = biztransactiontype.BIZTRANSACTIONTYPEID JOIN TMMDETAIL mmdetail ON mmhead.DOCUMENTID = mmdetail.DOCUMENTID JOIN TPENDINGALLOCATION pendingallocation ON pendingallocation.OBJECTID = mmdetail.DOCUMENTDETAILID AND pendingallocation.OBJECTHEADERID = mmdetail.DOCUMENTID WHERE pendingallocation.OBJECTHEADERTYPEID = -1899997952 AND pendingallocation.OBJECTTYPEID = -1899997854 AND pendingallocation.ALLOCATIONNATURE = 1 AND biztransactiontype.BIZTRANSACTIONCLASSID IN (-1499999975) AND pendingallocation.PENDINGQUANTITY > 0 AND mmhead.STATUS IN (0,1) GROUP BY mmdetail.ITEMID ) po ON po.ITEMID = c.ITEMID INNER JOIN MITEM d ON c.ITEMID = d.ITEMID INNER JOIN MSKU e ON c.SKUID = e.SKUID INNER JOIN MUOM f ON c.UOMID = f.UOMID INNER JOIN MWORKCENTER g ON a.WORKCENTERID = g.WORKCENTERID INNER JOIN MPROCESS h ON a.PROCESSID = h.PROCESSID INNER JOIN MITEM z ON b.ITEMID = z.ITEMID INNER JOIN TNESTINGPLAN np ON b.NESTINGPLANID = np.NESTINGPLANID INNER JOIN MALLOCATION m ON b.ALLOCATIONID = m.ALLOCATIONID WHERE b.RELEASESTATUS NOT IN (4,5,7) AND c.TYPEOFDATA = 0 AND c.TYPE = 0 AND a.STATUS IN (0,1) AND ISNULL(vv.PENDINGQUANTITY,0) <> 0 AND d.ITEMORTEMPLATE = 0 AND b.ITEMID = PL.ITEMID AND (@OUId = -1 OR a.OUID = @OUId) AND (@WorkCenterId = -1 OR a.WORKCENTERID = @WorkCenterId) AND (@ProcessId = -1 OR a.PROCESSID = @ProcessId) AND (@AllocationId = -1 OR b.ALLOCATIONID = @AllocationId) AND (@IndentId = -1 OR a.INDENTID = @IndentId) AND (@SKUId = -1 OR e.SKUID = @SKUId) AND (@Type = 1 OR ISNULL(SUBSTK.STOCKQUANTITY,0) < ISNULL(totalpending.PENDINGQUANTITY,0))"; public const string PROJECT_WISE_SHORTAGE_SQL = @" WITH ShortageBase AS (" + PROJECT_WISE_SHORTAGE_INNER_SQL + @" ), Paged AS ( SELECT *, ROW_NUMBER() OVER (ORDER BY ItemId, IndentId) AS RowNum FROM ShortageBase ) SELECT * FROM Paged WHERE (@firstnumber = -1 AND @maxresult = -1) OR (RowNum BETWEEN @firstnumber AND @maxresult);"; public const string PROJECT_WISE_SHORTAGE_COUNT_SQL = @" SELECT COUNT(*) FROM (" + PROJECT_WISE_SHORTAGE_INNER_SQL + @" ) x;"; private const string PROJECT_WISE_SHORTAGE_INNER_PG = @" SELECT b.ALLOCATIONID AS ""AllocationId"", m.ALLOCATIONNAME AS ""AllocationName"", m.DESCRIPTION AS ""AllocationDescription"", b.ITEMID AS ""OutputItemId"", CASE WHEN b.NESTINGPLANID <> -1 THEN np.NESTINGPLANCODE ELSE z.ITEMCODE END AS ""OutputItemCode"", CASE WHEN b.NESTINGPLANID <> -1 THEN np.REMARKS ELSE z.ITEMNAME END AS ""OutputItemName"", a.INDENTID AS ""IndentId"", a.INDENTNUMBER AS ""WorkOrderNumber"", a.INDENTDATE AS ""WorkOrderDate"", a.EXPECTEDFIRSTDELIVERYDATE AS ""FirstDeliveryDate"", d.ITEMID AS ""ItemId"", d.ITEMCODE AS ""ItemCode"", d.ITEMNAME AS ""ItemName"", e.SKUID AS ""SKUId"", e.SKUCODE AS ""SKUCode"", e.SKUNAME AS ""SKUName"", CASE WHEN d.ISMAKEORBUY = 0 THEN 'MAKE' ELSE 'BUY' END AS ""ISMakeOrBuy"", ABS(c.FINALQUANTITY) AS ""RequiredQuantity"", ABS(CASE WHEN vv.PENDINGQUANTITY IS NULL THEN c.FINALQUANTITY ELSE (c.FINALQUANTITY - vv.PENDINGQUANTITY) END) AS ""IssuedQuantity"", COALESCE(vv.PENDINGQUANTITY, 0) AS ""PendingQuantity"", COALESCE(SUBSTK.STOCKQUANTITY, 0) AS ""StockQuantity"", COALESCE(totalpending.PENDINGQUANTITY, 0) AS ""TotalPending"", COALESCE(po.POPending, 0) AS ""POPending"", vpallocdetail.PENDINGQUANTITY AS ""PendingWorkOrder"", COALESCE(totalpending.PENDINGQUANTITY, 0) - COALESCE(SUBSTK.STOCKQUANTITY, 0) AS ""Shortage"", f.UOMID AS ""UOMId"", f.UOMCODE AS ""UOMCode"", f.UOMNAME AS ""UOMName"", COALESCE(l.BINNUMBER, '') AS ""BINNumber"", g.WORKCENTERID AS ""WorkCenterId"", g.WORKCENTERNAME AS ""WorkCenterName"", h.PROCESSID AS ""ProcessId"", h.PROCESSCODE AS ""ProcessCode"", h.PROCESSNAME AS ""ProcessName"" FROM TINDENT a CROSS JOIN (SELECT unnest(string_to_array(:ProjectItemIds, ','))::int AS ITEMID) PL LEFT OUTER JOIN TINDENTDETAIL b ON a.INDENTID = b.INDENTID LEFT OUTER JOIN TINDENTMATERIAL c ON b.INDENTDETAILID = c.INDENTDETAILID LEFT OUTER JOIN TPENDINGALLOCATION vpallocdetail ON c.INDENTDETAILID = vpallocdetail.OBJECTID AND b.RELEASESTATUS NOT IN (4,5,7) AND vpallocdetail.OBJECTTYPEID = -1899997529 AND vpallocdetail.PENDINGQUANTITY <> 0 AND vpallocdetail.ALLOCATIONNATURE IN (1) LEFT OUTER JOIN ( SELECT sp.OUID, sp.ITEMID, sp.SKUID, SUM(sp.QUANTITY) AS STOCKQUANTITY FROM TSTOCKPOSITION sp JOIN MSTORE ms ON sp.STOREID = ms.STOREID WHERE ms.ISPLANNING = 0 GROUP BY sp.OUID, sp.ITEMID, sp.SKUID ) SUBSTK ON SUBSTK.ITEMID = c.ITEMID AND SUBSTK.SKUID = c.SKUID AND SUBSTK.OUID = a.OUID LEFT OUTER JOIN MBIZTRANSACTIONTYPE k ON k.BIZTRANSACTIONTYPEID = a.BIZTRANSACTIONTYPEID LEFT OUTER JOIN MITEMSTOCK l ON c.SKUID = l.SKUID AND c.ITEMID = l.ITEMID AND l.STOREID = k.STOREID LEFT OUTER JOIN ( SELECT d2.INDENTMATERIALID, d2.ITEMID, d2.SKUID, a2.PENDINGQUANTITY AS PENDINGQUANTITY FROM TPENDINGALLOCATION a2 JOIN TINDENT b2 ON a2.OBJECTHEADERID = b2.INDENTID JOIN TINDENTDETAIL c2 ON b2.INDENTID = c2.INDENTID JOIN TINDENTMATERIAL d2 ON c2.INDENTDETAILID = d2.INDENTDETAILID WHERE a2.OBJECTID = d2.INDENTMATERIALID AND a2.OBJECTTYPEID = -1899997494 AND b2.STATUS IN (0,1) AND d2.TYPEOFDATA = 0 AND (:PeriodFromDate IS NULL OR b2.EXPECTEDFIRSTDELIVERYDATE >= :PeriodFromDate) AND (:PeriodToDate IS NULL OR b2.EXPECTEDFIRSTDELIVERYDATE <= :PeriodToDate) ) vv ON c.INDENTMATERIALID = vv.INDENTMATERIALID AND c.ITEMID = vv.ITEMID AND c.SKUID = vv.SKUID LEFT OUTER JOIN ( SELECT d3.ITEMID, d3.SKUID, SUM(a3.PENDINGQUANTITY) AS PENDINGQUANTITY FROM TPENDINGALLOCATION a3 JOIN TINDENT b3 ON a3.OBJECTHEADERID = b3.INDENTID JOIN TINDENTDETAIL c3 ON b3.INDENTID = c3.INDENTID JOIN TINDENTMATERIAL d3 ON c3.INDENTDETAILID = d3.INDENTDETAILID WHERE a3.OBJECTID = d3.INDENTMATERIALID AND a3.OBJECTTYPEID = -1899997494 AND b3.STATUS IN (0,1) AND d3.TYPEOFDATA = 0 AND (:PeriodFromDate IS NULL OR b3.EXPECTEDFIRSTDELIVERYDATE >= :PeriodFromDate) AND (:PeriodToDate IS NULL OR b3.EXPECTEDFIRSTDELIVERYDATE <= :PeriodToDate) GROUP BY d3.ITEMID, d3.SKUID ) totalpending ON c.ITEMID = totalpending.ITEMID AND c.SKUID = totalpending.SKUID LEFT OUTER JOIN ( SELECT mmdetail.ITEMID AS ITEMID, SUM(pendingallocation.PENDINGQUANTITY) AS POPending FROM TMMHEAD mmhead JOIN MBIZTRANSACTIONTYPE biztransactiontype ON mmhead.BIZTRANSACTIONTYPEID = biztransactiontype.BIZTRANSACTIONTYPEID JOIN TMMDETAIL mmdetail ON mmhead.DOCUMENTID = mmdetail.DOCUMENTID JOIN TPENDINGALLOCATION pendingallocation ON pendingallocation.OBJECTID = mmdetail.DOCUMENTDETAILID AND pendingallocation.OBJECTHEADERID = mmdetail.DOCUMENTID WHERE pendingallocation.OBJECTHEADERTYPEID = -1899997952 AND pendingallocation.OBJECTTYPEID = -1899997854 AND pendingallocation.ALLOCATIONNATURE = 1 AND biztransactiontype.BIZTRANSACTIONCLASSID IN (-1499999975) AND pendingallocation.PENDINGQUANTITY > 0 AND mmhead.STATUS IN (0,1) GROUP BY mmdetail.ITEMID ) po ON po.ITEMID = c.ITEMID INNER JOIN MITEM d ON c.ITEMID = d.ITEMID INNER JOIN MSKU e ON c.SKUID = e.SKUID INNER JOIN MUOM f ON c.UOMID = f.UOMID INNER JOIN MWORKCENTER g ON a.WORKCENTERID = g.WORKCENTERID INNER JOIN MPROCESS h ON a.PROCESSID = h.PROCESSID INNER JOIN MITEM z ON b.ITEMID = z.ITEMID INNER JOIN TNESTINGPLAN np ON b.NESTINGPLANID = np.NESTINGPLANID INNER JOIN MALLOCATION m ON b.ALLOCATIONID = m.ALLOCATIONID WHERE b.RELEASESTATUS NOT IN (4,5,7) AND c.TYPEOFDATA = 0 AND c.TYPE = 0 AND a.STATUS IN (0,1) AND COALESCE(vv.PENDINGQUANTITY,0) <> 0 AND d.ITEMORTEMPLATE = 0 AND b.ITEMID = PL.ITEMID AND (:OUId = -1 OR a.OUID = :OUId) AND (:WorkCenterId = -1 OR a.WORKCENTERID = :WorkCenterId) AND (:ProcessId = -1 OR a.PROCESSID = :ProcessId) AND (:AllocationId = -1 OR b.ALLOCATIONID = :AllocationId) AND (:IndentId = -1 OR a.INDENTID = :IndentId) AND (:SKUId = -1 OR e.SKUID = :SKUId) AND (:Type = 1 OR COALESCE(SUBSTK.STOCKQUANTITY,0) < COALESCE(totalpending.PENDINGQUANTITY,0))"; public const string PROJECT_WISE_SHORTAGE_PG = @" WITH ""ShortageBase"" AS (" + PROJECT_WISE_SHORTAGE_INNER_PG + @" ), ""Paged"" AS ( SELECT *, ROW_NUMBER() OVER (ORDER BY ""ItemId"", ""IndentId"") AS ""RowNum"" FROM ""ShortageBase"" ) SELECT * FROM ""Paged"" WHERE (:firstnumber = -1 AND :maxresult = -1) OR (""RowNum"" BETWEEN :firstnumber AND :maxresult);"; public const string PROJECT_WISE_SHORTAGE_COUNT_PG = @" SELECT COUNT(*) FROM (" + PROJECT_WISE_SHORTAGE_INNER_PG + @" ) x;"; // ── Phase 7: Indent reference-number picklist (GB4 port: IndentQueryBuilder.INDENT_REFERENCE_NUMBER_SELECTLIST) ── // Indexes recommended: // TINDENT: IX_TINDENT_REFNUMBER (REFERENCENUMBER, TENANTID) public const string INDENT_REFERENCE_NUMBER_SELECTLIST_SQL = @" WITH Distinct1 AS ( SELECT DISTINCT a.REFERENCENUMBER AS ReferenceNumber, a.REFERENCEDATE AS ReferenceDate FROM TINDENT a WHERE a.REFERENCENUMBER <> '' ), Paged AS ( SELECT ReferenceNumber, ReferenceDate, ROW_NUMBER() OVER (ORDER BY ReferenceNumber) AS RowNum FROM Distinct1 ) SELECT ReferenceNumber, ReferenceDate FROM Paged WHERE (@firstnumber = -1 AND @maxresult = -1) OR (RowNum BETWEEN @firstnumber AND @maxresult);"; public const string INDENT_REFERENCE_NUMBER_SELECTLIST_PG = @" WITH ""Distinct1"" AS ( SELECT DISTINCT a.REFERENCENUMBER AS ""ReferenceNumber"", a.REFERENCEDATE AS ""ReferenceDate"" FROM TINDENT a WHERE a.REFERENCENUMBER <> '' ), ""Paged"" AS ( SELECT ""ReferenceNumber"", ""ReferenceDate"", ROW_NUMBER() OVER (ORDER BY ""ReferenceNumber"") AS ""RowNum"" FROM ""Distinct1"" ) SELECT ""ReferenceNumber"", ""ReferenceDate"" FROM ""Paged"" WHERE (:firstnumber = -1 AND :maxresult = -1) OR (""RowNum"" BETWEEN :firstnumber AND :maxresult);"; } }