This is an automated email from the ASF dual-hosted git repository. diveshdut pushed a commit to branch trunk in repository https://gitbox.apache.org/repos/asf/ofbiz-framework.git
commit fc1b597661dc4bb210125080eeb48e88f001667a Author: diveshdut <[email protected]> AuthorDate: Fri Aug 21 12:21:44 2026 +0530 OFBIZ-13502 Add MRP event plan read model Add the read-side event-plan pipeline for grouped MRP event planning. Introduce MrpEventPlanServices.groovy as the service entry point for findMrpEventPlans and findMrpPlanningFacilities. It reads framework REST query options, delegates event loading and row preparation, and returns paged REST-compatible responses. Introduce MrpEventPlanQuery.groovy as the query/read helper. It builds MrpEventView conditions for facility, product, event type, date, search, latest-run fallback, and candidate row limits, then loads the raw events and enrichment data used by the planner rows. Introduce MrpEventPlanBuilder.groovy as the planner row builder. It groups raw MRP events by product and facility, builds visible timelines with running balances, and derives attention metadata such as actionNeeded, attentionTypes, primaryAttentionType, and attentionSummary. Register the event-plan and planning-facility services in services_mrp.xml and add the planner-facing labels used by the event-plan read model. --- .../manufacturing/config/ManufacturingUiLabels.xml | 57 ++ .../manufacturing/servicedef/services_mrp.xml | 49 ++ .../manufacturing/mrp/MrpEventPlanBuilder.groovy | 705 +++++++++++++++++++++ .../manufacturing/mrp/MrpEventPlanQuery.groovy | 420 ++++++++++++ .../manufacturing/mrp/MrpEventPlanServices.groovy | 98 +++ 5 files changed, 1329 insertions(+) diff --git a/applications/manufacturing/config/ManufacturingUiLabels.xml b/applications/manufacturing/config/ManufacturingUiLabels.xml index a2fc697327..5fa7973478 100644 --- a/applications/manufacturing/config/ManufacturingUiLabels.xml +++ b/applications/manufacturing/config/ManufacturingUiLabels.xml @@ -3004,6 +3004,18 @@ <value xml:lang="zh">创建或更新生产资源计划事件（MrpEvent）时出错，参数：${parameters}</value> <value xml:lang="zh-TW">新建或更新生產資源計劃事件(MrpEvent)時出錯,參數:${parameters}</value> </property> + <property key="ManufacturingMrpCurrentBelowSafetyStock"> + <value xml:lang="en">Current below safety stock</value> + </property> + <property key="ManufacturingMrpCurrentBelowSafetyStockGuidance"> + <value xml:lang="en">Review proposed supply because current inventory is below the minimum stock policy.</value> + </property> + <property key="ManufacturingMrpEngineError"> + <value xml:lang="en">MRP engine error</value> + </property> + <property key="ManufacturingMrpEngineErrorGuidance"> + <value xml:lang="en">Review the MRP event ledger error before relying on this plan.</value> + </property> <property key="ManufacturingMrpErrorExplodingProduct"> <value xml:lang="en">An error occurred exploding the product ${productId}</value> <value xml:lang="it">E' successo un'errore durante l'espolosizione del prodotto ${productId}</value> @@ -3027,6 +3039,9 @@ <value xml:lang="zh">运行初始化生产资源计划事件（initMrpEvents）服务时出错：${errorString}</value> <value xml:lang="zh-TW">執行初始化生產資源計劃事件(initMrpEvents)服務時出錯:${errorString}</value> </property> + <property key="ManufacturingMrpEventDateFilterInvalid"> + <value xml:lang="en">Invalid MRP event date filter: ${value}</value> + </property> <property key="ManufacturingMrpEventFindError"> <value xml:lang="en">Problem, we can not find all the items of MrpEvent, for more detail look at the log</value> <value xml:lang="it">Problema, non possaimo trovare tutte le righe di MrpEvent, per più dettagli controllare il log</value> @@ -3043,6 +3058,9 @@ <value xml:lang="zh">初始化生产资源计划事件（MrpEvent）实体（${mrpEventTypeId}）时出问题了</value> <value xml:lang="zh-TW">初始化生產資源計劃事件(MrpEvent)資料實體(${mrpEventTypeId})時出問題了</value> </property> + <property key="ManufacturingMrpEventLedgerSource"> + <value xml:lang="en">OFBiz MRP event ledger</value> + </property> <property key="ManufacturingMrpEventRemoveError"> <value xml:lang="en">Problem, we can not remove the MrpEvent items, for more detail look at the log</value> <value xml:lang="it">Problema, non possaimo rimuovere le righe di MrpEvent, per più dettagli controllare il log</value> @@ -3091,6 +3109,12 @@ <value xml:lang="zh">场所标识（facilityId）和生产场所标识（manufacturingFacilityId）不能为空</value> <value xml:lang="zh-TW">場所標識(facilityId)和生產場所標識(manufacturingFacilityId)不能為空</value> </property> + <property key="ManufacturingMrpForecastAllocation"> + <value xml:lang="en">Forecast allocation</value> + </property> + <property key="ManufacturingMrpForecastAllocationGuidance"> + <value xml:lang="en">Allocate this organization-level forecast to a warehouse or review forecast setup.</value> + </property> <property key="ManufacturingMrpInitialisation"> <value xml:lang="de">MRP-Initialisierung</value> <value xml:lang="en">IEP init.</value> @@ -3160,6 +3184,12 @@ <value xml:lang="zh">生产资源计划日志</value> <value xml:lang="zh-TW">生產資源計劃日誌</value> </property> + <property key="ManufacturingMrpLateProposedSupply"> + <value xml:lang="en">Proposed supply is late</value> + </property> + <property key="ManufacturingMrpLateProposedSupplyGuidance"> + <value xml:lang="en">Review the late event date and source before relying on the plan.</value> + </property> <property key="ManufacturingMrpName"> <value xml:lang="de">MRP-Name</value> <value xml:lang="en">Mrp Name</value> @@ -3174,6 +3204,21 @@ <value xml:lang="zh">生产资源计划名称</value> <value xml:lang="zh-TW">生產資源計劃名稱</value> </property> + <property key="ManufacturingMrpNeedsAttention"> + <value xml:lang="en">Needs attention</value> + </property> + <property key="ManufacturingMrpOnTrack"> + <value xml:lang="en">On track</value> + </property> + <property key="ManufacturingMrpOpen"> + <value xml:lang="en">Open</value> + </property> + <property key="ManufacturingMrpProjectedBelowSafetyStock"> + <value xml:lang="en">Projected below safety stock</value> + </property> + <property key="ManufacturingMrpProjectedBelowSafetyStockGuidance"> + <value xml:lang="en">Review projected inventory because the running balance falls below minimum stock.</value> + </property> <property key="ManufacturingMrpRunDefaultName"> <value xml:lang="en">MRP run</value> </property> @@ -3187,6 +3232,18 @@ <value xml:lang="en">Mrp run scheduled successfully</value> <value xml:lang="fr">Exécution de Mrp programmée avec succès</value> </property> + <property key="ManufacturingMrpSetupIssue"> + <value xml:lang="en">Setup issue</value> + </property> + <property key="ManufacturingMrpSetupIssueGuidance"> + <value xml:lang="en">Review product-facility, BOM, routing, supplier, or inventory setup.</value> + </property> + <property key="ManufacturingMrpStockPolicyTrigger"> + <value xml:lang="en">Stock policy trigger</value> + </property> + <property key="ManufacturingMrpStockPolicyTriggerDescription"> + <value xml:lang="en">Current inventory is below minimum stock; MRP created this marker to trigger replenishment.</value> + </property> <property key="ManufacturingProductionRunTaskIssuedSuccessfully"> <value xml:lang="en">Issued Inventory for Production Run Task [${workEffortId}]</value> </property> diff --git a/applications/manufacturing/servicedef/services_mrp.xml b/applications/manufacturing/servicedef/services_mrp.xml index ec0be0f5ae..3e22af4d1c 100644 --- a/applications/manufacturing/servicedef/services_mrp.xml +++ b/applications/manufacturing/servicedef/services_mrp.xml @@ -135,4 +135,53 @@ under the License. <description>Delete a MrpEventType record</description> <auto-attributes include="pk" mode="IN"/> </service> + <!-- MRP planning read services for event plans, run history, and planning facilities. --> + <service name="findMrpEventPlans" engine="groovy" auth="true" + location="component://manufacturing/src/main/groovy/org/apache/ofbiz/manufacturing/mrp/MrpEventPlanServices.groovy" invoke="findMrpEventPlans"> + <description>Find product-centric MRP event plans with grouped facility timelines and attention metadata.</description> + <permission-service service-name="manufacturingPermissionService" main-action="VIEW"/> + <attribute mode="IN" name="facilityId" optional="true" type="String"/> + <attribute mode="IN" name="facilityGroupId" optional="true" type="String"/> + <attribute mode="IN" name="mrpId" optional="true" type="String"/> + <attribute mode="IN" name="productId" optional="true" type="String"/> + <attribute mode="IN" name="eventDate" optional="true" type="String"/> + <attribute mode="IN" name="dateFrom" optional="true" type="String"/> + <attribute mode="IN" name="dateTo" optional="true" type="String"/> + <attribute mode="IN" name="mrpEventTypeId" optional="true" type="String"/> + <attribute mode="IN" name="query" optional="true" type="String"/> + <attribute mode="IN" name="attentionOnly" optional="true" type="String"/> + <attribute mode="IN" name="attentionType" optional="true" type="String"/> + <attribute mode="IN" name="search" optional="true" type="String"/> + <attribute mode="IN" name="pageIndex" optional="true" type="Integer"/> + <attribute mode="IN" name="pageSize" optional="true" type="Integer"/> + <attribute mode="IN" name="sort" optional="true" type="String"/> + <attribute mode="IN" name="orderBy" optional="true" type="String"/> + <attribute mode="OUT" name="pageIndex" optional="false" type="Integer"/> + <attribute mode="OUT" name="pageSize" optional="false" type="Integer"/> + <attribute mode="OUT" name="totalCount" optional="false" type="Long"/> + <attribute mode="OUT" name="totalPages" optional="false" type="Long"/> + <attribute mode="OUT" name="hasNext" optional="false" type="Boolean"/> + <attribute mode="OUT" name="previousPageCount" optional="true" type="Long"/> + <attribute mode="OUT" name="nextPageCount" optional="true" type="Long"/> + <attribute mode="OUT" name="links" optional="true" type="Map"/> + <attribute mode="OUT" name="eventPlans" optional="false" type="java.util.List"/> + </service> + <service name="findPlanningFacilities" engine="groovy" auth="true" + location="component://manufacturing/src/main/groovy/org/apache/ofbiz/manufacturing/api/ManufacturingLookupServices.groovy" invoke="findManufacturingFacilities"> + <description>Find warehouse and plant facilities available for MRP planning.</description> + <permission-service service-name="manufacturingPermissionService" main-action="VIEW"/> + <attribute mode="IN" name="pageIndex" optional="true" type="Integer"/> + <attribute mode="IN" name="pageSize" optional="true" type="Integer"/> + <attribute mode="IN" name="sort" optional="true" type="String"/> + <attribute mode="IN" name="orderBy" optional="true" type="String"/> + <attribute mode="OUT" name="pageIndex" optional="false" type="Integer"/> + <attribute mode="OUT" name="pageSize" optional="false" type="Integer"/> + <attribute mode="OUT" name="totalCount" optional="false" type="Long"/> + <attribute mode="OUT" name="totalPages" optional="false" type="Long"/> + <attribute mode="OUT" name="hasNext" optional="false" type="Boolean"/> + <attribute mode="OUT" name="previousPageCount" optional="true" type="Long"/> + <attribute mode="OUT" name="nextPageCount" optional="true" type="Long"/> + <attribute mode="OUT" name="links" optional="true" type="Map"/> + <attribute mode="OUT" name="facilities" optional="false" type="java.util.List"/> + </service> </services> diff --git a/applications/manufacturing/src/main/groovy/org/apache/ofbiz/manufacturing/mrp/MrpEventPlanBuilder.groovy b/applications/manufacturing/src/main/groovy/org/apache/ofbiz/manufacturing/mrp/MrpEventPlanBuilder.groovy new file mode 100644 index 0000000000..28288ea3ee --- /dev/null +++ b/applications/manufacturing/src/main/groovy/org/apache/ofbiz/manufacturing/mrp/MrpEventPlanBuilder.groovy @@ -0,0 +1,705 @@ +/* + * Licensed to the Apache Software Foundation (ASF) under one + * or more contributor license agreements. See the NOTICE file + * distributed with this work for additional information + * regarding copyright ownership. The ASF licenses this file + * to you under the Apache License, Version 2.0 (the + * "License"); you may not use this file except in compliance + * with the License. You may obtain a copy of the License at + * + * http://www.apache.org/licenses/LICENSE-2.0 + * + * Unless required by applicable law or agreed to in writing, + * software distributed under the License is distributed on an + * "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY + * KIND, either express or implied. See the License for the + * specific language governing permissions and limitations + * under the License. + */ +package org.apache.ofbiz.manufacturing.mrp + +import java.nio.charset.StandardCharsets +import java.sql.Timestamp + +import org.apache.ofbiz.base.util.UtilProperties +import org.apache.ofbiz.base.util.UtilGenerics +import org.apache.ofbiz.base.util.UtilValidate +import org.apache.ofbiz.base.util.collections.MapComparator +import org.apache.ofbiz.entity.GenericValue +import org.apache.ofbiz.manufacturing.api.ManufacturingServiceUtil + +/* + * Converts raw MrpEventView ledger rows into grouped product/warehouse plan rows. + * + * Pipeline: + * 1. buildWarehouseEventPlanContexts groups raw rows by MRP run, product, and facility. + * 2. buildWarehouseEventPlanContext builds the working context for one product/warehouse group. + * 3. buildRunningBalanceTimeline calculates running inventory balances and timeline metadata. + * 4. buildEventPlanResponseRow shapes the context into the service response row. + * 5. buildAttentionSignals and buildInventoryContext add planner action and inventory facts. + */ +final class MrpEventPlanBuilder { + + static final String MRP_OPEN_STATUS_ID = 'MRP_OPEN' + private static final String RESOURCE = 'ManufacturingUiLabels' + static final Set EVENT_PLAN_SORT_FIELDS = [ + 'productId', + 'productName', + 'facilityId', + 'facilityName', + 'statusId', + 'startingQoh', + 'lowestRunningBalance', + 'minimumStock' + ] as Set + + private static final Map SEVERITY_PRIORITY = [HIGH: 0, MEDIUM: 1].asImmutable() + private static final List SETUP_SIGNAL_KEYWORDS = ['SETUP', 'BOM', 'ROUTING', 'SUPPLIER', 'ERROR'].asImmutable() + + private MrpEventPlanBuilder() { } + + // Event Plan Pipeline + + /* + * Raw MRP events are ledger rows, so group them before presenting planner-ready balances. The same product can have + * demand in WH1 and PLANT1, each with its own ProductFacility minimum stock. Grouping by product/facility prevents + * those balances from being mixed before attention metadata is calculated. + * + * Input shape: + * [ + * [mrpId: '10022', productId: 'GZ-8544', facilityId: 'WebStoreWarehouse', quantity: 18], + * [mrpId: '10022', productId: 'GZ-8544', facilityId: 'WebStoreWarehouse', quantity: -20], + * [mrpId: '10022', productId: 'GZ-8544', facilityId: 'WebStoreWarehouse', quantity: 50] + * ] + * + * Output shape: + * [ + * '10022|GZ-8544|WebStoreWarehouse': [ + * mrpId: '10022', + * productId: 'GZ-8544', + * facilityId: 'WebStoreWarehouse', + * startingQoh: 18, + * timeline: [...], + * hasInitialQoh: true + * ] + * ] + * + * Example grouped plan: + * GZ-8544 at WebStoreWarehouse + * starting QOH: 18 + * demand: -20 + * proposed supply: +50 + * final balance: 48 + * attention: on track / needs attention + */ + static Map buildWarehouseEventPlanContexts(List events, Map eventPlanReferenceData, Locale locale = Locale.ENGLISH) { + return events.groupBy { GenericValue event -> [event.getString('mrpId'), event.getString('productId')].join('|') } + .collectEntries { String productKey, List productEvents -> + List productLevelEvents = productEvents.findAll { GenericValue event -> + UtilValidate.isEmpty(event.getString('facilityId')) + } + productEvents.findAll { GenericValue event -> UtilValidate.isNotEmpty(event.getString('facilityId')) } + .groupBy { GenericValue event -> eventPlanGroupKey(event) } + .collectEntries { String key, List warehouseEvents -> + [(key): buildWarehouseEventPlanContext(key, warehouseEvents + productLevelEvents, + eventPlanReferenceData, locale)] + } + } + } + + /* + * Prepares one product/warehouse plan context from a grouped MRP event stream. The context centralizes ids, display + * names, stock policy, timeline balances, and setup flags so the final service row can be assembled without + * re-querying or recalculating those values. + * + * Input example: + * key = '10022|GZ-8544|WebStoreWarehouse' + * events = [ + * [mrpEventTypeId: 'INITIAL_QOH', quantity: 18], + * [mrpEventTypeId: 'SALES_ORDER', quantity: -20], + * [mrpEventTypeId: 'PROP_PUR_O_RECP', quantity: 50] + * ] + * + * Output example: + * [ + * key: '10022|GZ-8544|WebStoreWarehouse', + * mrpId: '10022', + * productId: 'GZ-8544', + * productName: 'Demo Gizmo', + * facilityId: 'WebStoreWarehouse', + * facilityName: 'Web Store Warehouse', + * minimumStock: 20, + * startingQoh: 18, + * timeline: [[quantity: 18, runningBalance: 18], [quantity: -20, runningBalance: -2]], + * hasInitialQoh: true, + * hasSetupSignal: false + * ] + */ + static Map buildWarehouseEventPlanContext(String key, List events, Map eventPlanReferenceData, Locale locale = Locale.ENGLISH) { + List sortedEvents = events.toSorted({ GenericValue left, GenericValue right -> + compareMrpTimelineEvents(left, right) + } as Comparator) + GenericValue firstEvent = UtilValidate.isNotEmpty(sortedEvents) ? sortedEvents.first() : null + /* + * Product-level forecast rows can have no facility; preserve the grouped key parts as a fallback for context + * ids. + */ + String[] keyParts = key.split('\\|', -1) + String productIdFromKey = keyParts.length > 1 && UtilValidate.isNotEmpty(keyParts[1]) ? keyParts[1] : null + String facilityIdFromKey = keyParts.length > 2 && UtilValidate.isNotEmpty(keyParts[2]) ? keyParts[2] : null + String mrpId = firstEvent?.getString('mrpId') + String productId = firstEvent?.getString('productId') ?: productIdFromKey + String facilityId = firstEvent?.getString('facilityId') ?: facilityIdFromKey + GenericValue product = eventPlanReferenceData.products[productId] + GenericValue facility = eventPlanReferenceData.facilities[facilityId] + GenericValue productFacility = eventPlanReferenceData.productFacilities[productFacilityLookupKey(productId, facilityId)] + BigDecimal minimumStock = productFacility?.getBigDecimal('minimumStock') + BigDecimal reorderQuantity = productFacility?.getBigDecimal('reorderQuantity') + Long productFacilityDaysToShip = productFacility?.getLong('daysToShip') + Long daysToShip = productFacilityDaysToShip != null ? productFacilityDaysToShip : + facility?.getLong('defaultDaysToShip') + List timeline = buildRunningBalanceTimeline(sortedEvents, eventPlanReferenceData, productFacility, locale) + return [ + key: key, + mrpId: mrpId, + productId: productId, + productName: ManufacturingServiceUtil.displayProductName(product), + facilityId: facilityId, + facilityName: ManufacturingServiceUtil.displayFacilityName(facility), + minimumStock: minimumStock, + reorderQuantity: reorderQuantity, + daysToShip: daysToShip, + startingQoh: sortedEvents.find { GenericValue event -> + event.getString('mrpEventTypeId') == 'INITIAL_QOH' + }?.getBigDecimal('quantity'), + timeline: timeline, + events: plannerVisibleTimelineRows(sortedEvents), + hasInitialQoh: sortedEvents.any { GenericValue event -> event.getString('mrpEventTypeId') == 'INITIAL_QOH' }, + hasSetupSignal: sortedEvents.any { GenericValue event -> + String eventDisplayDescription = event.getString('eventName')?.toUpperCase() + event.getString('mrpEventTypeId') == 'ERROR' || + (UtilValidate.isNotEmpty(eventDisplayDescription) && SETUP_SIGNAL_KEYWORDS.any { + eventDisplayDescription.contains(it) + }) + } + ] + } + + // Timeline Preparation + + // Hides internal planning markers from planner-facing evidence while keeping them in balance calculations. + static List plannerVisibleTimelineRows(List events) { + return events.findAll { event -> + String mrpEventTypeId = event instanceof GenericValue ? event.getString('mrpEventTypeId') : + event.mrpEventTypeId + mrpEventTypeId != 'REQUIRED_MRP' + } + } + + /* + * Calculates running balances and timeline metadata for one grouped plan. REQUIRED_MRP markers stay in the + * arithmetic even though plannerVisibleTimelineRows hides them from planner-facing evidence. + * + * Input example: + * events = [ + * [mrpEventTypeId: 'INITIAL_QOH', quantity: 18], + * [mrpEventTypeId: 'SALES_ORDER', quantity: -20], + * [mrpEventTypeId: 'PROP_PUR_O_RECP', quantity: 50] + * ] + * + * Output example: + * [ + * [mrpEventTypeId: 'INITIAL_QOH', quantity: 18, runningBalance: 18, belowMinimumStock: true], + * [mrpEventTypeId: 'SALES_ORDER', quantity: -20, runningBalance: -2, belowMinimumStock: true], + * [mrpEventTypeId: 'PROP_PUR_O_RECP', quantity: 50, runningBalance: 48, belowMinimumStock: false] + * ] + */ + static List buildRunningBalanceTimeline(List events, Map eventPlanReferenceData, GenericValue productFacility, + Locale locale = Locale.ENGLISH) { + BigDecimal runningBalance = BigDecimal.ZERO + BigDecimal minimumStock = productFacility?.getBigDecimal('minimumStock') + return events.collect { GenericValue event -> + String mrpId = event.getString('mrpId') + String mrpEventTypeId = event.getString('mrpEventTypeId') + String productId = event.getString('productId') + String facilityId = event.getString('facilityId') + String eventName = event.getString('eventName') + Timestamp eventDate = event.getTimestamp('eventDate') + BigDecimal quantity = event.getBigDecimal('quantity') ?: BigDecimal.ZERO + runningBalance = runningBalance.add(quantity) + GenericValue eventType = eventPlanReferenceData.eventTypes[mrpEventTypeId] + GenericValue product = eventPlanReferenceData.products[productId] + GenericValue facility = eventPlanReferenceData.facilities[facilityId] + String facilityName = ManufacturingServiceUtil.displayFacilityName(facility) + if (UtilValidate.isEmpty(facilityName) && mrpEventTypeId == 'SALES_FORECAST' && + UtilValidate.isEmpty(facilityId)) { + facilityName = 'Organization-level' + } + GenericValue rowProductFacility = eventPlanReferenceData.productFacilities[productFacilityLookupKey(productId, + facilityId)] + BigDecimal rowMinimumStock = rowProductFacility?.getBigDecimal('minimumStock') ?: minimumStock + Long rowProductFacilityDaysToShip = rowProductFacility?.getLong('daysToShip') + Map requirementReference = extractProposedSupplyRequirementReference(event) + [ + eventId: buildTimelineEventId(event), + mrpId: mrpId, + mrpEventTypeId: mrpEventTypeId, + eventTypeLabel: eventTypeDisplayLabel(mrpEventTypeId, eventType, quantity, locale), + eventDescription: eventDisplayDescription(event, eventType, quantity, locale), + eventDate: eventDate, + quantity: quantity, + runningBalance: runningBalance, + isLate: event.getString('isLate') == 'Y', + sourceId: eventName, + requirementId: requirementReference.requirementId, + requirementDate: requirementReference.requirementDate, + statusId: MRP_OPEN_STATUS_ID, + statusDescription: ManufacturingServiceUtil.displayStatusDescription( + eventPlanReferenceData.statuses[MRP_OPEN_STATUS_ID]) ?: label('ManufacturingMrpOpen', locale), + productId: productId, + productName: ManufacturingServiceUtil.displayProductName(product), + facilityId: facilityId, + facilityName: facilityName, + minimumStock: rowMinimumStock, + reorderQuantity: rowProductFacility?.getBigDecimal('reorderQuantity'), + daysToShip: rowProductFacilityDaysToShip != null ? rowProductFacilityDaysToShip : + facility?.getLong('defaultDaysToShip'), + startingQoh: null, + belowMinimumStock: rowMinimumStock != null && runningBalance < rowMinimumStock + ] + }.with { List rows -> + BigDecimal firstQoh = rows.find { Map row -> row.mrpEventTypeId == 'INITIAL_QOH' }?.quantity + rows.each { Map row -> row.startingQoh = firstQoh } + rows + } + } + + // Response Row Shaping + + /* + * Converts one prepared product/warehouse context into the Event Plans response row consumed by clients. + * + * Input example: + * groupContext = [ + * productId: 'GZ-8544', + * facilityId: 'WebStoreWarehouse', + * startingQoh: 18, + * minimumStock: 20, + * timeline: [[quantity: 18, runningBalance: 18], [quantity: -20, runningBalance: -2]] + * ] + * + * Output example: + * [ + * planId: 'mrp_...', + * productId: 'GZ-8544', + * facilityId: 'WebStoreWarehouse', + * statusDescription: 'Needs attention', + * startingQoh: 18, + * lowestRunningBalance: -2, + * actionNeeded: true, + * attentionTypes: ['BELOW_SAFETY_STOCK'], + * inventoryContext: [projectedBalance: 48, lowestRunningBalance: -2], + * evidence: [...], + * timeline: [...] + * ] + */ + static Map buildEventPlanResponseRow(Map groupContext, Locale locale = Locale.ENGLISH) { + Map inventory = buildInventoryContext(groupContext.timeline.first() ?: [:], groupContext) + List planSignals = buildAttentionSignals(groupContext, locale) + List actionSignals = planSignals.findAll { Map signal -> signal.actionNeeded != false } + Map primarySignal = actionSignals.min { Map signal -> signal.severityPriority } ?: [:] + return [ + planId: buildEventPlanId(groupContext), + mrpId: groupContext.mrpId, + productId: groupContext.productId, + productName: groupContext.productName, + facilityId: groupContext.facilityId, + facilityName: groupContext.facilityName, + statusId: MRP_OPEN_STATUS_ID, + statusDescription: UtilValidate.isNotEmpty(actionSignals) ? label('ManufacturingMrpNeedsAttention', locale) : + label('ManufacturingMrpOnTrack', locale), + minimumStock: groupContext.minimumStock, + reorderQuantity: groupContext.reorderQuantity, + daysToShip: groupContext.daysToShip, + startingQoh: groupContext.startingQoh, + lowestRunningBalance: inventory.lowestRunningBalance, + actionNeeded: UtilValidate.isNotEmpty(actionSignals), + attentionSeverity: primarySignal.severity, + attentionTypes: actionSignals*.type, + primaryAttentionType: primarySignal.type, + attentionSummary: actionSignals*.summary.findAll { it }.join('; '), + inlineGuidance: primarySignal.inlineGuidance, + inventoryContext: inventory, + evidence: plannerVisibleTimelineRows(groupContext.timeline), + timeline: groupContext.timeline + ] + } + + // Planner Attention + + /* + * Produces planner-action chips and guidance from the prepared timeline context. + * + * Input example: + * groupContext = [ + * minimumStock: 20, + * timeline: [[mrpEventTypeId: 'SALES_ORDER', runningBalance: -2, belowMinimumStock: true]] + * ] + * + * Output example: + * [ + * [ + * type: 'BELOW_SAFETY_STOCK', + * severity: 'HIGH', + * actionNeeded: true, + * summary: 'Projected below safety stock', + * inlineGuidance: [ + * recommendation: 'Review projected inventory because the running balance falls below minimum stock.' + * ] + * ] + * ] + */ + static List buildAttentionSignals(Map groupContext, Locale locale = Locale.ENGLISH) { + List signals = [] + if (groupContext.timeline.any { Map row -> row.isLate }) { + signals.add(buildAttentionSignal('LATE_EVENT', 'HIGH', + label('ManufacturingMrpLateProposedSupply', locale), + label('ManufacturingMrpLateProposedSupplyGuidance', locale), locale)) + } + if (groupContext.timeline.any { Map row -> row.mrpEventTypeId == 'ERROR' }) { + signals.add(buildAttentionSignal('ENGINE_ERROR', 'HIGH', + label('ManufacturingMrpEngineError', locale), + label('ManufacturingMrpEngineErrorGuidance', locale), locale)) + } + Map safetyStockEvent = findUnresolvedSafetyStockEvent(groupContext) + if (UtilValidate.isNotEmpty(safetyStockEvent)) { + boolean currentBelowSafetyStock = safetyStockEvent.mrpEventTypeId == 'INITIAL_QOH' + signals.add(buildAttentionSignal('BELOW_SAFETY_STOCK', attentionSeverityFor('BELOW_SAFETY_STOCK', safetyStockEvent), + currentBelowSafetyStock ? label('ManufacturingMrpCurrentBelowSafetyStock', locale) : + label('ManufacturingMrpProjectedBelowSafetyStock', locale), + currentBelowSafetyStock + ? label('ManufacturingMrpCurrentBelowSafetyStockGuidance', locale) + : label('ManufacturingMrpProjectedBelowSafetyStockGuidance', locale), locale)) + } else { + Map recoveredSafetyStockEvent = groupContext.timeline.findAll { Map row -> row.belowMinimumStock } + .min { Map row -> row.runningBalance ?: BigDecimal.ZERO } + if (UtilValidate.isNotEmpty(recoveredSafetyStockEvent)) { + signals.add(buildAttentionSignal('BELOW_SAFETY_STOCK', attentionSeverityFor('BELOW_SAFETY_STOCK', + recoveredSafetyStockEvent), null, null, locale, [actionNeeded: false])) + } + } + // Missing initial QOH usually points to setup data, not a normal inventory movement. + if ((!groupContext.hasInitialQoh && UtilValidate.isNotEmpty(groupContext.facilityId)) || + groupContext.hasSetupSignal) { + signals.add(buildAttentionSignal('SETUP_ISSUE', 'HIGH', + label('ManufacturingMrpSetupIssue', locale), + label('ManufacturingMrpSetupIssueGuidance', locale), locale)) + } + if (groupContext.timeline.any { Map row -> isOrganizationLevelForecast(row) }) { + signals.add(buildAttentionSignal('FORECAST_ALLOCATION', 'MEDIUM', + label('ManufacturingMrpForecastAllocation', locale), + label('ManufacturingMrpForecastAllocationGuidance', locale), locale)) + } + return signals.unique { Map signal -> signal.type } + } + + // Filtering And Sorting + + // Applies MRP-specific filters after framework paging/sort parameters have been parsed. + static boolean matchesEventPlanFilters(Map plan, Map filters) { + if (Boolean.valueOf(filters.attentionOnly?.toString()) && !plan.actionNeeded) { + return false + } + if (UtilValidate.isNotEmpty(filters.attentionType) && !(filters.attentionType in (plan.attentionTypes ?: []))) { + return false + } + String queryText = UtilValidate.isNotEmpty(filters.query) ? filters.query.toString().trim() : + (UtilValidate.isNotEmpty(filters.search) ? filters.search.toString().trim() : null) + return matchesEventPlanSearch(plan, queryText) + } + + // Provides deterministic fallback ordering when no framework sort is requested. + static Comparator<Map<String, Object>> buildEventPlanFallbackComparator() { + return { Map left, Map right -> + new MapComparator(['productId', 'facilityId', 'mrpId']).compare(UtilGenerics.cast(left), + UtilGenerics.cast(right)) + } as Comparator + } + + // Applies the same free-text query to already-prepared rows after grouping and computed fields are available. + static boolean matchesEventPlanSearch(Map row, String queryText) { + if (UtilValidate.isEmpty(queryText)) { + return true + } + String needle = queryText.toUpperCase() + return [row.productId, row.productName, row.facilityId, row.facilityName, row.exceptionType, + row.eventDescription, row.mrpId].find { Object value -> + value?.toString()?.toUpperCase()?.contains(needle) + } != null + } + + // Timeline Ordering And Stable IDs + + // Ensures running balances start with initial QOH before same-run demand/supply events are applied. + static int compareMrpTimelineEvents(GenericValue left, GenericValue right) { + Map leftFields = [ + eventSortBucket: left.getString('mrpEventTypeId') == 'INITIAL_QOH' ? 0 : 1, + eventDate: left.getTimestamp('eventDate'), + mrpEventTypeId: left.getString('mrpEventTypeId'), + eventName: left.getString('eventName') + ] + Map rightFields = [ + eventSortBucket: right.getString('mrpEventTypeId') == 'INITIAL_QOH' ? 0 : 1, + eventDate: right.getTimestamp('eventDate'), + mrpEventTypeId: right.getString('mrpEventTypeId'), + eventName: right.getString('eventName') + ] + return new MapComparator(['eventSortBucket', 'eventDate', 'mrpEventTypeId', 'eventName']).compare( + UtilGenerics.cast(leftFields), UtilGenerics.cast(rightFields)) + } + + // Builds a stable id for computed timeline rows, which do not have a single DB primary key. + static String buildTimelineEventId(Object event) { + Object eventDate = event instanceof GenericValue ? event.getTimestamp('eventDate') : event.eventDate + String eventDateKey = eventDate instanceof Timestamp ? Long.toString(eventDate.time) : eventDate?.toString() + return encodeCompositeId(['event', + event instanceof GenericValue ? event.getString('mrpId') : event.mrpId, + event instanceof GenericValue ? event.getString('productId') : event.productId, + event instanceof GenericValue ? event.getString('facilityId') : event.facilityId, + eventDateKey, + event instanceof GenericValue ? event.getString('mrpEventTypeId') : event.mrpEventTypeId]) + } + + // Builds a stable id for computed product/warehouse plan rows. + static String buildEventPlanId(Map groupContext) { + return encodeCompositeId(['plan', groupContext.mrpId, groupContext.productId, groupContext.facilityId]) + } + + // Creates an in-memory grouping key for mrpId/productId/facilityId. + static String eventPlanGroupKey(Object value) { + if (value instanceof GenericValue) { + return [value.getString('mrpId'), value.getString('productId'), value.getString('facilityId') ?: ''].join('|') + } + return [value.mrpId, value.productId, value.facilityId ?: ''].join('|') + } + + // Creates a lookup key for batched ProductFacility reference rows. + static String productFacilityLookupKey(Object productId, Object facilityId) { + return [productId ?: '', facilityId ?: ''].join('|') + } + + // Internal Attention Helpers + + // Organization-level forecasts need planner allocation before warehouse plans can be trusted. + private static boolean isOrganizationLevelForecast(Map event) { + return event?.mrpEventTypeId == 'SALES_FORECAST' && UtilValidate.isEmpty(event.facilityId) + } + + // Finds the first unresolved balance problem that still needs planner attention. + private static Object findUnresolvedSafetyStockEvent(Map groupContext) { + if (UtilValidate.isEmpty(groupContext?.timeline)) { + return null + } + + Map negativeBalanceEvent = groupContext.timeline + .findAll { Map event -> event.runningBalance != null && event.runningBalance < BigDecimal.ZERO } + .min { Map event -> event.runningBalance ?: BigDecimal.ZERO } + if (UtilValidate.isNotEmpty(negativeBalanceEvent)) { + return negativeBalanceEvent + } + + if (groupContext.minimumStock == null) { + return null + } + + Map finalEvent = finalPlannerVisibleTimelineRow(groupContext.timeline) + if (finalEvent?.runningBalance != null && finalEvent.runningBalance < groupContext.minimumStock) { + return finalEvent + } + + Map firstBelowMinimum = groupContext.timeline.find { Map event -> event.belowMinimumStock } + if (UtilValidate.isEmpty(firstBelowMinimum)) { + return null + } + + if (firstBelowMinimum.mrpEventTypeId != 'INITIAL_QOH') { + return firstBelowMinimum + } + + return hasOnTimeProposedSupplyRecovery(groupContext) ? null : firstBelowMinimum + } + + // Uses the last user-visible event when deciding whether the plan recovers. + private static Map finalPlannerVisibleTimelineRow(List timeline) { + List rows = plannerVisibleTimelineRows(timeline) + return UtilValidate.isNotEmpty(rows) ? rows.last() : timeline.last() + } + + // A below-minimum starting balance is acceptable if on-time proposed supply recovers the plan. + private static boolean hasOnTimeProposedSupplyRecovery(Map groupContext) { + Map finalEvent = finalPlannerVisibleTimelineRow(groupContext.timeline) + if (finalEvent?.runningBalance == null || finalEvent.runningBalance < groupContext.minimumStock) { + return false + } + return groupContext.timeline.any { Map event -> + event.mrpEventTypeId in ['PROP_PUR_O_RECP', 'PROP_MANUF_O_RECP'] && + event.quantity > BigDecimal.ZERO && !event.isLate + } + } + + /* + * Creates the normalized attention-chip payload used by filters, summaries, and inline planner guidance. + * + * Input example: + * type = 'BELOW_SAFETY_STOCK' + * severity = 'HIGH' + * summary = 'Projected below safety stock' + * recommendation = 'Review projected inventory because the running balance falls below minimum stock.' + * fields = [actionNeeded: true] + * + * Output example: + * [ + * type: 'BELOW_SAFETY_STOCK', + * severity: 'HIGH', + * severityPriority: 0, + * summary: 'Projected below safety stock', + * actionNeeded: true, + * inlineGuidance: [ + * recommendation: 'Review projected inventory because the running balance falls below minimum stock.', + * source: 'OFBiz MRP event ledger' + * ] + * ] + */ + private static Map buildAttentionSignal(String type, String severity, String summary, String recommendation, + Locale locale = Locale.ENGLISH, Map fields = [:]) { + Integer severityPriority = SEVERITY_PRIORITY.containsKey(severity) ? SEVERITY_PRIORITY[severity] : 2 + Map signal = [ + type: type, + severity: severity, + severityPriority: severityPriority, + summary: summary, + actionNeeded: fields.actionNeeded != false + ] + if (UtilValidate.isNotEmpty(recommendation)) { + signal.inlineGuidance = [ + recommendation: recommendation, + source: label('ManufacturingMrpEventLedgerSource', locale) + ] + } + return signal + } + + // Internal Row And Label Helpers + + /* + * Collects inventory balance facts separately from display-oriented attention summaries. + * + * Input example: + * summary = [runningBalance: 48] + * groupContext = [ + * productId: 'GZ-8544', + * facilityId: 'WebStoreWarehouse', + * startingQoh: 18, + * minimumStock: 20, + * reorderQuantity: 50, + * timeline: [[runningBalance: 18], [runningBalance: -2, belowMinimumStock: true], [runningBalance: 48]] + * ] + * + * Output example: + * [ + * productId: 'GZ-8544', + * facilityId: 'WebStoreWarehouse', + * startingQoh: 18, + * minimumStock: 20, + * reorderQuantity: 50, + * projectedBreachAmount: 22, + * projectedBalance: 48, + * lowestRunningBalance: -2, + * breachDate: null + * ] + */ + private static Map buildInventoryContext(Map summary, Map groupContext) { + BigDecimal lowestBalance = groupContext.timeline*.runningBalance.findAll { it != null }.min() + Map breach = groupContext.timeline.find { Map row -> row.belowMinimumStock } + BigDecimal projectedBreachAmount = null + if (breach?.runningBalance != null && groupContext.minimumStock != null) { + projectedBreachAmount = groupContext.minimumStock.subtract(breach.runningBalance) + } + return [ + productId: groupContext.productId, + productName: groupContext.productName, + facilityId: groupContext.facilityId, + facilityName: groupContext.facilityName, + startingQoh: groupContext.startingQoh, + minimumStock: groupContext.minimumStock, + reorderQuantity: groupContext.reorderQuantity, + daysToShip: groupContext.daysToShip, + projectedBreachAmount: projectedBreachAmount, + projectedBalance: summary.runningBalance, + lowestRunningBalance: lowestBalance, + breachDate: breach?.eventDate + ] + } + + // Uses MrpEventType.description from seed data, except for the zero-quantity stock policy marker. + private static String eventTypeDisplayLabel(Object mrpEventTypeId, GenericValue eventType, BigDecimal quantity = null, + Locale locale = Locale.ENGLISH) { + if (isStockPolicyTrigger(mrpEventTypeId, quantity)) { + return label('ManufacturingMrpStockPolicyTrigger', locale) + } + String description = eventType?.getString('description') + return UtilValidate.isNotEmpty(description) ? description : mrpEventTypeId + } + + // Explains synthetic stock-policy markers differently from ordinary event descriptions. + private static String eventDisplayDescription(GenericValue event, GenericValue eventType, BigDecimal quantity, + Locale locale = Locale.ENGLISH) { + if (isStockPolicyTrigger(event.getString('mrpEventTypeId'), quantity)) { + return label('ManufacturingMrpStockPolicyTriggerDescription', locale) + } + String eventName = event.getString('eventName') + return UtilValidate.isNotEmpty(eventName) ? eventName : eventType?.getString('description') + } + + // Identifies the internal marker created when current inventory is below minimum stock. + private static boolean isStockPolicyTrigger(Object mrpEventTypeId, BigDecimal quantity) { + return mrpEventTypeId?.toString() == 'REQUIRED_MRP' && quantity != null && quantity == BigDecimal.ZERO + } + + // Extracts the covered requirement id/date from proposed-supply event names generated by the MRP engine. + private static Map extractProposedSupplyRequirementReference(GenericValue event) { + if (!(event?.getString('mrpEventTypeId') in ['PROP_PUR_O_RECP', 'PROP_MANUF_O_RECP'])) { + return [:] + } + String eventName = event.getString('eventName') + if (UtilValidate.isEmpty(eventName)) { + return [:] + } + return (eventName =~ /^\*(\S+)\s+\((.+)\)\*$/).with { matcher -> + matcher.matches() ? [ + requirementId: matcher.group(1), + requirementDate: Timestamp.valueOf(matcher.group(2)) + ] : [:] + } + } + + // Converts attention types into a severity used by the planner chips. + private static String attentionSeverityFor(String exceptionType, Map event) { + if (exceptionType in ['ENGINE_ERROR', 'SETUP_ISSUE', 'LATE_EVENT']) { + return 'HIGH' + } + return exceptionType == 'BELOW_SAFETY_STOCK' && event.runningBalance != null && + event.runningBalance < BigDecimal.ZERO ? 'HIGH' : 'MEDIUM' + } + + // Encodes composite ids so delimiters inside product/facility ids cannot make ambiguous service ids. + private static String encodeCompositeId(List parts) { + String payload = parts.collect { it?.toString() ?: '' }.join('\u001F') + return 'mrp_' + Base64.urlEncoder.withoutPadding().encodeToString(payload.getBytes(StandardCharsets.UTF_8)) + } + + private static String label(String key, Locale locale, Map parameters = [:]) { + return UtilProperties.getMessage(RESOURCE, key, parameters, locale ?: Locale.ENGLISH) + } + +} diff --git a/applications/manufacturing/src/main/groovy/org/apache/ofbiz/manufacturing/mrp/MrpEventPlanQuery.groovy b/applications/manufacturing/src/main/groovy/org/apache/ofbiz/manufacturing/mrp/MrpEventPlanQuery.groovy new file mode 100644 index 0000000000..fb4884731d --- /dev/null +++ b/applications/manufacturing/src/main/groovy/org/apache/ofbiz/manufacturing/mrp/MrpEventPlanQuery.groovy @@ -0,0 +1,420 @@ +/* + * Licensed to the Apache Software Foundation (ASF) under one + * or more contributor license agreements. See the NOTICE file + * distributed with this work for additional information + * regarding copyright ownership. The ASF licenses this file + * to you under the Apache License, Version 2.0 (the + * "License"); you may not use this file except in compliance + * with the License. You may obtain a copy of the License at + * + * http://www.apache.org/licenses/LICENSE-2.0 + * + * Unless required by applicable law or agreed to in writing, + * software distributed under the License is distributed on an + * "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY + * KIND, either express or implied. See the License for the + * specific language governing permissions and limitations + * under the License. + */ +package org.apache.ofbiz.manufacturing.mrp + +import java.sql.Timestamp +import java.time.LocalDate +import java.util.regex.Pattern + +import org.apache.ofbiz.base.util.ObjectType +import org.apache.ofbiz.base.util.UtilProperties +import org.apache.ofbiz.base.util.UtilValidate +import org.apache.ofbiz.entity.Delegator +import org.apache.ofbiz.entity.GenericValue +import org.apache.ofbiz.entity.condition.EntityCondition +import org.apache.ofbiz.entity.condition.EntityOperator +import org.apache.ofbiz.entity.util.EntityQuery +import org.apache.ofbiz.entity.util.EntityUtil + +final class MrpEventPlanQuery { + + private static final Pattern DATE_ONLY_PATTERN = ~/\d{4}-\d{2}-\d{2}/ + private static final String RESOURCE = 'ManufacturingUiLabels' + private static final int MAX_MRP_EVENT_VIEW_ROWS = 5000 + + private MrpEventPlanQuery() { } + + /* + * Calculates how many raw MrpEventView rows can be loaded before grouping them into product/warehouse plans. + * The service may need more raw rows than the requested page size because multiple ledger rows collapse into one + * planner-facing row. + * + * Example: a single plan for GZ-8544 at WebStoreWarehouse may need INITIAL_QOH, SALES_ORDER, and PROP_PUR_O_RECP + * rows to calculate one final running balance. Loading only pageSize raw rows could therefore return too few plans + * after grouping. This method over-fetches enough raw rows for the requested page window, but caps the query so one + * request does not load too many MRP event rows. + * + * The 500-row floor protects very small page sizes. For example, pageSize 1 would otherwise load only 100 raw rows, + * which may not be enough after raw event rows collapse into grouped plan rows. + * + * Input example: + * pageIndex = 2 + * pageSize = 10 + * + * Output example: + * 3000 + */ + static int candidateRowLimitForPlanGrouping(int pageIndex, int pageSize) { + int pageWindow = Math.max(1, pageIndex + 1) + return Math.max(500, Math.min(pageWindow * pageSize * 100, MAX_MRP_EVENT_VIEW_ROWS)) as int + } + + /* + * Detail loader for one selected product/facility plan. Unlike the list loader, this narrows the raw event stream + * to the selected warehouse plus product-level context rows so the detail timeline can explain that one plan. + * + * Product-level rows, such as organization-level forecasts, are retained so the detail view can explain why + * warehouse planning changed. + * + * Input example: + * filters = [mrpId: '10022', productId: 'GZ-8544', facilityId: 'WebStoreWarehouse'] + * + * Output example: + * [ + * MrpEventView[mrpId: '10022', productId: 'GZ-8544', facilityId: 'WebStoreWarehouse'], + * MrpEventView[mrpId: '10022', productId: 'GZ-8544', facilityId: null] + * ] + */ + static List loadSelectedPlanEvents(Delegator delegator, Map filters, int candidateLimit, Locale locale = Locale.ENGLISH) { + Map eventFilters = filters.findAll { String key, Object value -> + UtilValidate.isNotEmpty(value) + } + List events = loadMrpEventViewRows(delegator, eventFilters, candidateLimit, true, locale) + return events.findAll { GenericValue event -> + String facilityId = event.getString('facilityId') + facilityId == filters.facilityId || UtilValidate.isEmpty(facilityId) + } + } + + /* + * List loader for many product/facility plans. Unlike the selected-plan loader, this loads a bounded candidate set + * that can be grouped into multiple planner-facing warehouse rows. + * + * It applies optional facility or facility-group filters before the builder groups rows into product/warehouse + * plans. + * + * Input example: + * filters = [mrpId: '10022', facilityGroupId: 'WH_GROUP'] + * + * Output example: + * [ + * MrpEventView[mrpId: '10022', productId: 'GZ-8544', facilityId: 'WebStoreWarehouse'], + * MrpEventView[mrpId: '10022', productId: 'WG-1111', facilityId: 'PlantOne'] + * ] + */ + static List loadEventPlanListEvents(Delegator delegator, Map filters, int candidateLimit, Locale locale = Locale.ENGLISH) { + Map eventFilters = filters.findAll { String key, Object value -> + UtilValidate.isNotEmpty(value) + } + List events = loadMrpEventViewRows(delegator, eventFilters, candidateLimit, true, locale) + if (UtilValidate.isNotEmpty(filters.facilityId)) { + return events.findAll { GenericValue event -> + String facilityId = event.getString('facilityId') + facilityId == filters.facilityId || UtilValidate.isEmpty(facilityId) + } + } + if (UtilValidate.isNotEmpty(filters.facilityGroupId)) { + Set facilityIds = activeFacilityIdsForGroup(delegator, filters.facilityGroupId) + if (UtilValidate.isEmpty(facilityIds)) { + return [] + } + return events.findAll { GenericValue event -> + String facilityId = event.getString('facilityId') + facilityIds.contains(facilityId) || UtilValidate.isEmpty(facilityId) + } + } + return events + } + + /* + * Executes the bounded MrpEventView query before any product/facility grouping or planner-facing shaping is applied. + * + * Input example: + * filters = [mrpId: '10022', productId: 'GZ-8544'] + * candidateLimit = 1000 + * + * Output example: + * [ + * MrpEventView[mrpEventTypeId: 'INITIAL_QOH', quantity: 18], + * MrpEventView[mrpEventTypeId: 'SALES_ORDER', quantity: -20], + * MrpEventView[mrpEventTypeId: 'PROP_PUR_O_RECP', quantity: 50] + * ] + */ + static List loadMrpEventViewRows(Delegator delegator, Map filters, int candidateLimit, boolean includeProductLevelEvents, + Locale locale = Locale.ENGLISH) { + List conditions = buildMrpEventViewConditions(delegator, filters, includeProductLevelEvents, locale) + /* + * Keep the DB read bounded here as well as at the service layer. Grouping and timeline calculation happen in + * memory, so direct callers must not be able to accidentally load a very large MrpEventView result set. + */ + int queryLimit = Math.max(1, Math.min(candidateLimit, MAX_MRP_EVENT_VIEW_ROWS)) + return EntityQuery.use(delegator).from('MrpEventView') + .select('mrpId', 'productId', 'eventDate', 'mrpEventTypeId', 'facilityId', 'facilityIdTo', 'quantity', + 'eventName', 'isLate', 'billOfMaterialLevel') + .where(EntityCondition.makeCondition(conditions, EntityOperator.AND)) + .orderBy('productId', 'facilityId', 'mrpId', 'eventDate', 'mrpEventTypeId') + .maxRows(queryLimit) + .queryList() + } + + /* + * Loads reference rows needed to turn raw MrpEventView rows into planner-facing rows. Product, Facility, + * ProductFacility, MrpEventType, and StatusItem records are batched here to avoid N+1 queries while preparing each + * grouped plan timeline. + * + * Input example: + * events = [MrpEventView[productId: 'GZ-8544', facilityId: 'WebStoreWarehouse', mrpEventTypeId: 'SALES_ORDER']] + * + * Output example: + * [ + * products: ['GZ-8544': Product[productId: 'GZ-8544']], + * facilities: ['WebStoreWarehouse': Facility[facilityId: 'WebStoreWarehouse']], + * productFacilities: ['GZ-8544|WebStoreWarehouse': ProductFacility[minimumStock: 20]], + * eventTypes: ['SALES_ORDER': MrpEventType[mrpEventTypeId: 'SALES_ORDER']], + * statuses: ['MRP_OPEN': StatusItem[statusId: 'MRP_OPEN']] + * ] + */ + static Map loadEventPlanReferenceData(Delegator delegator, List events, Collection extraFacilityIds = []) { + Set productIds = events*.getString('productId').findAll { UtilValidate.isNotEmpty(it) } as Set + Set facilityIds = (events*.getString('facilityId').findAll { UtilValidate.isNotEmpty(it) } + + extraFacilityIds.findAll { UtilValidate.isNotEmpty(it) }) as Set + Set eventTypeIds = events*.getString('mrpEventTypeId').findAll { UtilValidate.isNotEmpty(it) } as Set + return [ + products: UtilValidate.isNotEmpty(productIds) ? + EntityUtil.lookupById(delegator, 'Product', 'productId', productIds, false) : [:], + facilities: UtilValidate.isNotEmpty(facilityIds) ? + EntityUtil.lookupById(delegator, 'Facility', 'facilityId', facilityIds, false) : [:], + productFacilities: loadProductFacilitiesByKey(delegator, productIds, facilityIds), + eventTypes: UtilValidate.isNotEmpty(eventTypeIds) ? + EntityUtil.lookupById(delegator, 'MrpEventType', 'mrpEventTypeId', eventTypeIds) : [:], + statuses: UtilValidate.isNotEmpty(events) ? + EntityUtil.lookupById(delegator, 'StatusItem', 'statusId', + [MrpEventPlanBuilder.MRP_OPEN_STATUS_ID]) : [:] + ] + } + + /* + * Converts service filters into MrpEventView conditions. When no mrpId is supplied, it defaults to the latest MRP + * run with a durable run log so Event Plans opens on the newest available ledger. + * + * Input example: + * filters = [productId: 'GZ-8544', facilityId: 'WebStoreWarehouse', dateFrom: '2026-08-01'] + * + * Output example: + * [ + * mrpId == latestMrpId, + * productId == 'GZ-8544', + * facilityId == 'WebStoreWarehouse' OR facilityId is product-level, + * eventDate >= Timestamp('2026-08-01 00:00:00') + * ] + */ + static List buildMrpEventViewConditions(Delegator delegator, Map filters, boolean includeProductLevelEvents, + Locale locale = Locale.ENGLISH) { + List conditions = [] + if (UtilValidate.isNotEmpty(filters.mrpId)) { + conditions.add(EntityCondition.makeCondition('mrpId', filters.mrpId)) + } else { + String latestMrpId = latestMrpIdFromRunLog(delegator) + conditions.add(EntityCondition.makeCondition('mrpId', latestMrpId ?: '__NO_MATCHING_MRP__')) + } + if (UtilValidate.isNotEmpty(filters.productId)) { + conditions.add(EntityCondition.makeCondition('productId', filters.productId)) + } + if (UtilValidate.isNotEmpty(filters.facilityId)) { + conditions.add(buildFacilityEventCondition(filters.facilityId, includeProductLevelEvents)) + } + if (UtilValidate.isNotEmpty(filters.facilityGroupId)) { + Set facilityIds = activeFacilityIdsForGroup(delegator, filters.facilityGroupId) + conditions.add(UtilValidate.isNotEmpty(facilityIds) ? buildFacilityEventCondition(facilityIds, + includeProductLevelEvents) : EntityCondition.makeCondition('facilityId', '__NO_MATCHING_FACILITY__')) + } + if (UtilValidate.isNotEmpty(filters.mrpEventTypeId)) { + conditions.add(EntityCondition.makeCondition('mrpEventTypeId', filters.mrpEventTypeId)) + } + if (UtilValidate.isNotEmpty(filters.eventDate)) { + if (DATE_ONLY_PATTERN.matcher(filters.eventDate.toString()).matches()) { + conditions.add(EntityCondition.makeCondition('eventDate', EntityOperator.GREATER_THAN_EQUAL_TO, + parseEventDateBoundary(filters.eventDate, false, locale))) + conditions.add(EntityCondition.makeCondition('eventDate', EntityOperator.LESS_THAN, + parseEventDateBoundary(filters.eventDate, true, locale))) + } else { + conditions.add(EntityCondition.makeCondition('eventDate', parseEventDateBoundary(filters.eventDate, false, locale))) + } + } + Timestamp dateFrom = parseEventDateBoundary(filters.dateFrom, false, locale) + if (dateFrom != null) { + conditions.add(EntityCondition.makeCondition('eventDate', EntityOperator.GREATER_THAN_EQUAL_TO, dateFrom)) + } + Timestamp dateTo = parseEventDateBoundary(filters.dateTo, true, locale) + if (dateTo != null) { + conditions.add(EntityCondition.makeCondition('eventDate', EntityOperator.LESS_THAN, dateTo)) + } + String queryText = UtilValidate.isNotEmpty(filters.query) ? filters.query.toString().trim() : + (UtilValidate.isNotEmpty(filters.search) ? filters.search.toString().trim() : null) + if (UtilValidate.isNotEmpty(queryText)) { + conditions.add(EntityCondition.makeCondition(buildMrpEventSearchConditions(delegator, queryText), + EntityOperator.OR)) + } + return conditions + } + + /* + * Expands Event Plans free-text search across raw MRP event fields plus related product/facility display fields. + * Product and Facility lookups are explicit because MrpEventView stores ids, not display names. + * + * Input example: + * queryText = 'gizmo' + * + * Output example: + * [ + * upperLikeAny(['mrpId', 'productId', 'facilityId', 'mrpEventTypeId', 'eventName'], 'gizmo'), + * productId IN ['GZ-8544'], + * facilityId IN ['WebStoreWarehouse'] + * ] + */ + static List buildMrpEventSearchConditions(Delegator delegator, String queryText) { + List conditions = [ + EntityUtil.upperLikeAny(['mrpId', 'productId', 'facilityId', 'mrpEventTypeId', 'eventName'], queryText) + ] + Set matchingProductIds = EntityUtil.searchIds(delegator, 'Product', 'productId', + ['productId', 'productName', 'internalName'], queryText, 1000) + if (UtilValidate.isNotEmpty(matchingProductIds)) { + conditions.add(EntityCondition.makeCondition('productId', EntityOperator.IN, matchingProductIds as List)) + } + Set matchingFacilityIds = EntityUtil.searchIds(delegator, 'Facility', 'facilityId', + ['facilityId', 'facilityName'], queryText, + [EntityCondition.makeCondition('facilityTypeId', EntityOperator.IN, ['WAREHOUSE', 'PLANT'])], 1000) + if (UtilValidate.isNotEmpty(matchingFacilityIds)) { + conditions.add(EntityCondition.makeCondition('facilityId', EntityOperator.IN, matchingFacilityIds as List)) + } + return conditions + } + + /* + * Finds the latest MRP id from durable run history so the list endpoint has a useful default when no mrpId filter is + * supplied. + * + * Output example: + * '10022' + */ + static String latestMrpIdFromRunLog(Delegator delegator) { + return EntityQuery.use(delegator).from('MrpRunLog') + .select('mrpId') + .where(EntityCondition.makeCondition('mrpId', EntityOperator.NOT_EQUAL, null)) + .orderBy('-startedAt', '-createdStamp') + .maxRows(25) + .queryList() + .find { GenericValue runLog -> UtilValidate.isNotEmpty(runLog.getString('mrpId')) } + ?.getString('mrpId') + } + + /* + * Builds the facility condition used by list/detail queries. Detail views include product-level rows, such as + * organization-level forecasts, alongside warehouse-specific rows. + * + * Input example: + * facilityIdOrIds = 'WebStoreWarehouse' + * includeProductLevelEvents = true + * + * Output example: + * facilityId == 'WebStoreWarehouse' OR facilityId is null OR facilityId == '' + */ + static EntityCondition buildFacilityEventCondition(Object facilityIdOrIds, boolean includeProductLevelEvents) { + EntityCondition facilityCondition = facilityIdOrIds instanceof Collection ? + EntityCondition.makeCondition('facilityId', EntityOperator.IN, facilityIdOrIds as List) : + EntityCondition.makeCondition('facilityId', facilityIdOrIds) + if (!includeProductLevelEvents) { + return facilityCondition + } + return EntityCondition.makeCondition([ + facilityCondition, + EntityCondition.makeCondition('facilityId', null), + EntityCondition.makeCondition('facilityId', '') + ], EntityOperator.OR) + } + + /* + * Expands a facility group filter into active member facilities before the main MrpEventView query is built. + * + * Input example: + * facilityGroupId = 'WH_GROUP' + * + * Output example: + * ['WebStoreWarehouse', 'PlantOne'] as Set + */ + static Set activeFacilityIdsForGroup(Delegator delegator, Object facilityGroupId) { + if (UtilValidate.isEmpty(facilityGroupId)) { + return [] as Set + } + return EntityQuery.use(delegator).from('FacilityGroupMember') + .select('facilityId') + .where(facilityGroupId: facilityGroupId) + .filterByDate() + .queryList() + *.getString('facilityId') + .findAll { UtilValidate.isNotEmpty(it) } as Set + } + + /* + * Reads product-facility stock policy rows keyed by product and warehouse so the builder can calculate safety-stock + * attention without querying inside each grouped plan. + * + * Input example: + * productIds = ['GZ-8544'] + * facilityIds = ['WebStoreWarehouse'] + * + * Output example: + * ['GZ-8544|WebStoreWarehouse': ProductFacility[minimumStock: 20, reorderQuantity: 50]] + */ + private static Map loadProductFacilitiesByKey(Delegator delegator, Collection productIds, Collection facilityIds) { + if (UtilValidate.isEmpty(productIds) || UtilValidate.isEmpty(facilityIds)) { + return [:] + } + return EntityQuery.use(delegator).from('ProductFacility') + .select('productId', 'facilityId', 'minimumStock', 'reorderQuantity', 'daysToShip') + .where(EntityCondition.makeCondition([ + EntityCondition.makeCondition('productId', EntityOperator.IN, productIds as List), + EntityCondition.makeCondition('facilityId', EntityOperator.IN, facilityIds as List) + ], EntityOperator.AND)) + .queryList() + .collectEntries { GenericValue productFacility -> + String key = MrpEventPlanBuilder.productFacilityLookupKey(productFacility.getString('productId'), + productFacility.getString('facilityId')) + [(key): productFacility] + } + } + + /* + * Parses date/timestamp filters for EntityQuery conditions. Date-only end filters use an exclusive next-day boundary + * so the whole selected date is included. + * + * Input example: + * value = '2026-08-01' + * exclusiveEnd = true + * + * Output example: + * Timestamp('2026-08-02 00:00:00') + */ + private static Timestamp parseEventDateBoundary(Object value, boolean exclusiveEnd, Locale locale = Locale.ENGLISH) { + if (UtilValidate.isEmpty(value)) { + return null + } + try { + if (DATE_ONLY_PATTERN.matcher(value.toString()).matches()) { + LocalDate date = LocalDate.parse(value.toString()) + return Timestamp.valueOf((exclusiveEnd ? date.plusDays(1) : date).atStartOfDay()) + } + return (Timestamp) ObjectType.simpleTypeOrObjectConvert(value, 'Timestamp', null, null) + } catch (Exception e) { + throw new IllegalArgumentException(UtilProperties.getMessage(RESOURCE, 'ManufacturingMrpEventDateFilterInvalid', + [value: value], locale ?: Locale.ENGLISH)) + } + } + +} diff --git a/applications/manufacturing/src/main/groovy/org/apache/ofbiz/manufacturing/mrp/MrpEventPlanServices.groovy b/applications/manufacturing/src/main/groovy/org/apache/ofbiz/manufacturing/mrp/MrpEventPlanServices.groovy new file mode 100644 index 0000000000..f833fa337d --- /dev/null +++ b/applications/manufacturing/src/main/groovy/org/apache/ofbiz/manufacturing/mrp/MrpEventPlanServices.groovy @@ -0,0 +1,98 @@ +/* + * Licensed to the Apache Software Foundation (ASF) under one + * or more contributor license agreements. See the NOTICE file + * distributed with this work for additional information + * regarding copyright ownership. The ASF licenses this file + * to you under the Apache License, Version 2.0 (the + * "License"); you may not use this file except in compliance + * with the License. You may obtain a copy of the License at + * + * http://www.apache.org/licenses/LICENSE-2.0 + * + * Unless required by applicable law or agreed to in writing, + * software distributed under the License is distributed on an + * "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY + * KIND, either express or implied. See the License for the + * specific language governing permissions and limitations + * under the License. + */ +package org.apache.ofbiz.manufacturing.mrp + +import org.apache.ofbiz.base.util.UtilValidate +import org.apache.ofbiz.entity.GenericValue +import org.apache.ofbiz.service.ServiceUtil +import org.apache.ofbiz.ws.rs.util.RestApiUtil +import org.apache.ofbiz.ws.rs.util.RestQueryOptions + +/* + * Builds reusable event-plan data from MrpEventView instead of the FindInventoryEventPlan screen query. + * + * Flow: + * 1. Parse framework REST query options and identify list/detail filters. + * 2. Query/load raw MRP event rows and reference data through MrpEventPlanQuery. + * 3. Build planner-facing product/warehouse plan rows through MrpEventPlanBuilder. + * 4. Apply framework-compatible sorting and paging before returning the REST response. + */ +Map findMrpEventPlans() { + Locale serviceLocale = binding.hasVariable('locale') ? locale : Locale.ENGLISH + // 1. Parse REST query options and identify list/detail mode. + RestQueryOptions queryOptions + try { + queryOptions = RestQueryOptions.fromParameters(parameters) + } catch (IllegalArgumentException e) { + return ServiceUtil.returnError(e.message) + } + Map filters = queryOptions.filters + String relativeRequestPath = RestApiUtil.getRelativeRequestPath(binding) + boolean detailRequest = UtilValidate.isNotEmpty(filters.productId) && UtilValidate.isNotEmpty(filters.facilityId) + List events + try { + /* + * 2. Query/load raw MrpEventView rows. The detail flow loads one selected plan; the list flow loads candidates + * for grouping and planner filters. + */ + int candidateRowLimit = MrpEventPlanQuery.candidateRowLimitForPlanGrouping(queryOptions.pageIndex, + queryOptions.pageSize) + events = detailRequest ? MrpEventPlanQuery.loadSelectedPlanEvents(delegator, filters, candidateRowLimit, serviceLocale) : + MrpEventPlanQuery.loadEventPlanListEvents(delegator, filters, candidateRowLimit, serviceLocale) + } catch (IllegalArgumentException e) { + return ServiceUtil.returnError(e.message) + } + if (detailRequest) { + if (UtilValidate.isEmpty(events)) { + return ServiceUtil.returnSuccess() + RestApiUtil.getPagedResult('eventPlans', [], queryOptions, 0L, + relativeRequestPath) + } + // 3a. Build one planner-facing detail row. + Map eventPlanReferenceData = MrpEventPlanQuery.loadEventPlanReferenceData(delegator, events, + [filters.facilityId]) + GenericValue firstEvent = events.first() + Map groupContext = MrpEventPlanBuilder.buildWarehouseEventPlanContext(MrpEventPlanBuilder.eventPlanGroupKey([ + mrpId: firstEvent?.getString('mrpId') ?: filters.mrpId, + productId: filters.productId, + facilityId: filters.facilityId + ]), events, eventPlanReferenceData, serviceLocale) + Map plan = MrpEventPlanBuilder.buildEventPlanResponseRow(groupContext, serviceLocale) + List detailPage = queryOptions.pageIndex == 0 ? [plan] : [] + // 4a. Return detail through the same paged response contract as list. + return ServiceUtil.returnSuccess() + RestApiUtil.getPagedResult('eventPlans', detailPage, queryOptions, 1L, + relativeRequestPath) + } + // 3b. Build planner-facing product/warehouse rows before computed filters and sorting. + Map eventPlanReferenceData = MrpEventPlanQuery.loadEventPlanReferenceData(delegator, events) + Map groupedContexts = MrpEventPlanBuilder.buildWarehouseEventPlanContexts(events, eventPlanReferenceData, serviceLocale) + List eventPlans = groupedContexts.values() + .findAll { Map groupContext -> UtilValidate.isNotEmpty(groupContext.facilityId) } + .collect { Map groupContext -> MrpEventPlanBuilder.buildEventPlanResponseRow(groupContext, serviceLocale) } + .findAll { Map plan -> MrpEventPlanBuilder.matchesEventPlanFilters(plan, filters) } + try { + eventPlans = RestApiUtil.sortMapRows(eventPlans, queryOptions.sort, MrpEventPlanBuilder.EVENT_PLAN_SORT_FIELDS, + MrpEventPlanBuilder.buildEventPlanFallbackComparator()) + } catch (IllegalArgumentException e) { + return ServiceUtil.returnError(e.message) + } + // 4b. Page the built rows using framework REST metadata and links. + List pagedPlans = RestApiUtil.pageList(eventPlans, queryOptions.pageIndex, queryOptions.pageSize) + return ServiceUtil.returnSuccess() + RestApiUtil.getPagedResult('eventPlans', pagedPlans, queryOptions, + eventPlans.size() as long, relativeRequestPath) +}
