A production line ERP-MES fusion whole-process production report intelligent collection and off-spec material statistics method
By integrating ERP and MES into a smart summary method for full-process production reports, the problems of scattered data, untraceable off-label materials, and inconsistent data standards in industrial production lines have been solved. This has enabled integrated summary and accurate identification of production data across the entire industry, improving the level of refinement and intelligence in production management.
Patent Information
- Authority / Receiving Office
- CN · China
- Patent Type
- Applications(China)
- Current Assignee / Owner
- HANDAN DINGSHENG DIGITAL INTELLIGENCE TECHNOLOGY CO LTD
- Filing Date
- 2026-03-18
- Publication Date
- 2026-06-05
AI Technical Summary
Existing technologies cannot achieve deep integration of ERP-MES dual systems in industrial production lines across various industries, automatic aggregation of production data throughout the entire process, accurate identification of substandard materials, unified data standards for multiple business types, and efficient processing of large volumes of data. This results in fragmented production data statistics, system data barriers, inability to track substandard materials, inconsistent data standards, and low report generation efficiency, failing to meet the needs of refined production management.
This paper designs an intelligent summary method for ERP-MES integrated production reports that is applicable to the entire industry. It builds a unified data architecture for ERP-MES integrated production processes that is applicable to the entire industry, sets a universal core identifier and data association mapping across ERP-MES, creates a lightweight temporary table and formulates a simplified step-by-step calculation strategy to achieve real-time collection and synchronization of production data, adopts CTE one-step de-labeling intelligent identification and statistics of materials, generates standardized production reports and supports immersive interactive display.
It achieves integrated intelligent aggregation of production data across all processes in continuous production lines with multiple processes across the industry, accurately identifies substandard materials, unifies data standards for multiple business types, improves the accuracy and efficiency of production data statistics, supports cross-departmental collaborative decision-making, reduces system implementation and maintenance costs, and adapts to the high real-time requirements of large data volumes.
Abstract
Description
Technical Field
[0001] This invention relates to the field of data fusion and production data statistics technology of Industrial Manufacturing Execution System (MES) and Enterprise Resource Planning System (ERP). Specifically, it relates to a method for intelligently summarizing production data, automatically identifying and accurately counting deviated materials, and providing data support for the entire process of production data collection, from ERP order placement, raw material input, processing of each process to finished product warehousing, for multi-process continuous production lines in various industries. It is applicable to the production departments of industrial enterprises in various industries for full-process production data statistics, real-time monitoring of production progress, full life-cycle management of deviated materials, and cross-departmental collaborative decision-making data support for production, operation, and finance, and has universal applicability across all industries. Background Technology
[0002] Multi-process continuous production lines across various industries encompass multiple sequential production processes / processing units, resulting in large volumes of production data, complex material flow chains, and generally involving two core systems: ERP order management and MES production execution. Traditional industrial production line production data statistics models suffer from many common core pain points across industries: Production data is stored in a scattered manner, with production, process, and quality inspection data for each process being split into multiple independent data tables. Data from ERP and MES systems form a natural barrier, lacking a unified data foundation. Manual cross-table and cross-system aggregation is inefficient and prone to data deviation, making it impossible to achieve integrated management and control of production data throughout the entire process. Production data traceability lacks a unified core identifier across systems. ERP order data, MES production data, and material flow data for each process are disconnected, making it impossible to achieve full lifecycle traceability from order placement to finished product warehousing. Production anomalies are difficult to quickly pinpoint the root cause. For materials that are out of contract or lack valid sales contracts, and whose contract numbers do not match, traditional systems cannot accurately locate and count the quantity of out-of-contract materials in each process because there are no identification rules and statistical mechanisms that fit the actual upstream and downstream material flow logic of the production line. This results in a lack of control over out-of-contract materials, loss of production progress control, and inaccurate cost accounting. Order data for various business types, such as spot and non-spot goods, are generally stored in different data tables in the ERP system. The statistical standards are inconsistent, manual matching is difficult, and data chaos is likely to occur, which cannot provide consistent data support for cross-departmental collaboration. Traditional report generation relies on complex nested queries and repeated scanning of large tables, resulting in low data processing efficiency. Furthermore, the high degree of standardization in report templates leads to a disconnect from actual on-site usage needs, requiring manual secondary processing and making it difficult to meet the high real-time and high-practicality report query requirements of industrial production lines. The existing system is difficult to expand and maintain. Adding new processes / processing units requires large-scale modifications to the table structure and statistical logic, resulting in long adaptation cycles and high costs. It cannot quickly respond to the needs of enterprises for production process adjustments and production line upgrades.
[0003] Existing technologies lack a universal production report statistical method that can simultaneously achieve deep data integration between ERP and MES systems in industrial production lines across various industries, automatic aggregation of production data throughout the entire process, accurate identification of substandard materials, unified data standards for multiple business types, and efficient processing of large data volumes. This makes it difficult to meet the actual needs of refined and intelligent production management for industrial enterprises in various industries. Moreover, existing technologies are mostly limited to a single industry or specific production line, resulting in poor versatility, high implementation costs, and an inability to be promoted across the entire industry. Summary of the Invention
[0004] Purpose of the invention This invention aims to overcome the shortcomings of existing technologies and provide a universally applicable method for intelligent aggregation of full-process production reports and statistical analysis of substandard materials in integrated ERP-MES production lines. It breaks down industry and production line limitations, achieving integrated intelligent aggregation of production data across all processes in multi-process continuous production lines across various industries; deep integration of data from both ERP and MES systems; accurate identification and full-node statistical analysis of substandard materials; unified output of data from multiple business types; and efficient processing of large volumes of production data. This solves common industry problems such as fragmented production data statistics, system data silos, untraceable substandard materials, inconsistent data definitions, and low report generation efficiency in traditional industrial production lines. It improves the accuracy and efficiency of production data statistics, providing accurate, comprehensive, unified, and real-time multi-dimensional data support for production management, process optimization, and cross-departmental collaborative decision-making in industrial enterprises across various industries. Simultaneously, it achieves low system implementation costs, low maintenance costs, and high scalability, making it valuable for industry-wide industrial promotion. Technical solution
[0005] A method for intelligent aggregation of end-to-end production reports and de-labeled material statistics in a production line ERP-MES integration includes the following steps: Build an industry-wide universal ERP-MES system that integrates a unified data architecture across all processes. The system is designed to adapt to the general production data statistics logic of multi-process continuous production lines in various industries. It uniformly defines core statistical indicators such as roll count / output, roll count off the contract, roll count into inventory, and weight into inventory for each process, breaking the industry-specific and production line-specific limitations. Using the production process quality inspection record table as the unified data foundation for the entire process, it integrates all-dimensional data such as production performance, process parameters, quality inspection data, raw material identification, finished product identification, contract number, and plan number into a single table, creating a one-stop platform for enterprise production data aggregation. There is no need to develop separate statistical logic for different processes / production lines. At the same time, it achieves seamless integration with the enterprise's existing ERP system and MES system, realizing barrier-free data exchange between the two systems. Adding a new process / processing unit only requires configuring the fuzzy matching rule of the process name to quickly incorporate it into the statistical system without modifying the core table structure and statistical logic, making the system extremely easy to expand.
[0006] Configure a universal core identifier and data association mapping across ERP-MES. The contract number, contract hash value, and plan number are selected as the unique core identifier for the entire industrial production line process. This identifier is linked to auxiliary identifiers such as raw material identifiers, finished product identifiers, finished product storage locations, customer information, and product specifications. This enables the mapping of core data across modules including ERP sales order details, ERP sales order spot details, MES production plan, MES production process quality inspection records, and raw material warehouse information. Based on the raw material / finished product identifiers, precise correlation is achieved across the upstream and downstream material flow links of each process, ensuring the uniqueness of production data, cross-system linkage, and full-process traceability. This system is adaptable to the material identification rules of various industry production lines and possesses universality.
[0007] Create a lightweight temporary table and formulate a minimal step-by-step computation strategy. Lightweight temporary tables such as #Production Department Reports and #Temporary Summary_Process Output are created in the MES system database to serve as a simplified intermediate carrier for production data processing. All redundant temporary tables are eliminated to minimize data storage and processing steps. The complex full-process statistics are broken down into a simplified step-by-step calculation strategy of basic data insertion - process output summary - de-labeled material statistics - report data integration. The temporary table reuse mode is adopted to avoid repeated scanning of large tables and multiple table locks, thereby improving data processing efficiency from the bottom layer.
[0008] Real-time acquisition and synchronization of production data between ERP and MES systems The control systems for each process on the production line upload real-time production data (contract number, plan number, raw material identifier, finished product identifier, processing time, process parameters, finished product weight, finished product storage location, etc.) to the production process quality inspection record table in the MES system. The system periodically synchronizes the order quantity and customer information data from the ERP sales order details table and the ERP sales order spot details table with the product specifications and production plan data from the MES production plan table to the corresponding lightweight temporary table, ensuring the real-time and completeness of cross-system production data and adapting to the data collection frequency and data dimension requirements of production lines in various industries.
[0009] Integrated aggregation of production data across the entire process Data from various temporary tables is aggregated and integrated according to a preset strategy, forming a closed-loop data chain from ERP orders to raw material input, process processing, and finished product warehousing, adapting to the production flow logic of various industries. 5.1 Basic Order Data Integration: In the temporary table of the #Production Department Report, basic data such as customer, product name, contract hash value, plan number, and product specifications are statistically analyzed from the MES production plan table by contract number, realizing the initial implementation of MES production plan data; 5.2 Multi-business type order data matching: Automatically distinguishes multiple business types such as spot goods / non-spot goods based on contract number characteristics. Spot goods business is associated with the ERP sales order spot details table, and non-spot goods business is associated with the ERP sales order details table. The contract hash value is used to achieve accurate matching between ERP order quantity and MES production data, complete the integration of basic data across systems, and adapt to the business type classification rules of various enterprises; 5.3 Summary of Total Production / Rolls: In the Temporary Summary_Temporary Production Table, the number of rolls processed / production and finished product output of each process are calculated from the Production Process Quality Inspection Record Table according to the plan number and process name fuzzy matching rule (LIKE '% process name%'), realizing a one-time summary of data for the entire process. It is linked to the basic data of the Temporary Production Department Report Table to achieve full-chain traceability. 5.4 Finished Product Warehousing Data Statistics: In the temporary table of the #Production Department Report, the number of rolls and weight of finished products entering the warehouse are counted from the production process quality inspection record table according to the plan number and the condition that the finished product warehouse location is not empty. The data of each process is linked to form a preliminary production data of the whole process. 5.5 End-to-end data integration: Batch update the summary data of each temporary table directly to the #Production Department Report Temporary Table, generate the end-to-end production data summary table, realize one-stop summary of enterprise production data, and eliminate the need for intermediate data transfer.
[0010] Intelligent identification and accurate statistics of delabeled materials based on upstream and downstream processes 6.1 Set general rules for judging off-standard materials: If a material meets any of the following conditions, it is judged as off-standard (out of contract) material. This rule is applicable to the judgment of off-standard materials in production lines of various industries. 6.2 CTE One-Step Calculation of Off-Label Material Data: Based on the actual upstream and downstream material flow logic of the production line, the core data of the plan number can be directly read from the temporary table of the #Production Department Report through the CTE common expression. There is no need to create a redundant temporary table for off-label material statistics, and the statistics of off-label materials in each process can be completed in one step. 6.3 Accurate statistics of upstream and downstream links of the process: The raw material warehouse information table is used as the source of upstream raw materials for the front-end process, and the finished product identifier of each process is used as the raw material identifier for the downstream process. The number of rolls that are decoupled from each process is counted in turn. The zero-value fault tolerance is achieved through the fault tolerance function to ensure that there are no null values in the statistical data and adapt to the upstream and downstream flow logic of the production line of various industries. 6.4 Optimization of data for materials with missing labels: The number of missing contracts obtained from statistics is converted to NULL to avoid invalid 0 values interfering with the report display effect, improve the practicality of data, and adapt to the report display needs of various enterprises.
[0011] Merging and unifying the output of data sources of multiple business types Integrate production data sources from multiple business types, including spot and non-spot goods, in the ERP system. Automatic matching and classification statistics of order data across different business types are achieved through contract number characteristics. Standardized statistics are performed on core indicators such as roll count / production volume and inbound weight for each business type according to unified statistical standards, unifying the statistical standards for ERP order quantity and MES production output. The merged production data is directly written to the #Production Department Temporary Report table in a preset format, ensuring complete consistency in data standards across production, operations, and finance departments, eliminating data confusion, and achieving cross-departmental data collaboration.
[0012] Intelligent generation and immersive interactive display of standardized production reports Based on the actual production management needs of industrial enterprises, the system pre-sets standardized production report formats covering ERP order dimensions (customer, contract number, order quantity), MES production dimensions (roll count / output of each process, warehousing data), and de-labeled material dimensions (number of de-labeled rolls of each process), adapting to the production statistics needs of various industries. The system automatically generates corresponding standardized production reports based on the full-process production data in the #Production Department's temporary report table. It supports multi-dimensional filtering and querying by contract number, plan number, process name, etc., and one-click export. The report data can be directly used for on-site production statistics without manual secondary processing. Simultaneously, it achieves an immersive interactive display with master-detail report linkage and one-click drill-down. Clicking on any plan number / process name in the master table automatically displays detailed production data for the corresponding process / stage, achieving seamless penetration of production data from summary to detail, improving the operational efficiency and data query experience for production management personnel.
[0013] Production data full lifecycle traceability and full-process control of delabeled materials It supports penetrating queries through key fields such as contract number, contract hash value, plan number, raw material identifier, and finished product identifier to reach the original ERP order data, MES original production data, and material flow data of each process, realizing full-process reverse traceability from finished product warehousing to ERP order placement, and quickly locating the root cause of production anomalies; at the same time, it supports the status update and processing record entry of delisted materials. When delisted materials are re-bound to contracts, scrapped, or outsourced for processing, their status can be updated in real time, realizing full life cycle management of delisted materials from generation to disposal, and adapting to the material management needs of production lines in various industries.
[0014] Minimalist Temporary Table Cleanup After the report generation and query are completed, the system automatically cleans up lightweight temporary tables such as #Production Department Reports and #Temporary Summary_Process Output, quickly releasing database resources, ensuring long-term stable operation of the system, and improving the overall system performance.
[0015] III. Core Innovation Points It pioneers a unified data infrastructure architecture applicable across the entire industry: breaking down industry and production line limitations, it integrates multi-dimensional data from all processes into a single table centered on the production process quality inspection record table, abandoning the traditional multi-table scattered storage mode, and achieving one-time aggregation of data from all processes without the need for cross-table joins, greatly improving data query and statistical efficiency; adding a new process only requires configuring name matching rules, without modifying the core table structure and logic, making the system maintenance and expansion difficulty the lowest in the industry, and possessing universal adaptability across all industries.
[0016] A universal core identifier fusion method across ERP and MES is proposed: a unique core identifier system of contract number + contract hash value + plan number is created to break down data barriers between ERP and MES systems and realize full-process data linkage and traceability from order placement to finished product warehousing; based on raw material / finished product identifiers, the upstream and downstream material flow links of each process are accurately linked, adapting to the material identifier rules of production lines in various industries, and providing accurate data support for production anomaly analysis in various industries.
[0017] Innovative CTE one-step intelligent identification and statistics mechanism for delabeled materials: It eliminates redundant temporary tables for delabeled material statistics and completes the statistics of delabeled materials in one step through CTE; it formulates judgment rules that are in line with the actual upstream and downstream flow logic of production lines in various industries, and achieves 100% accurate identification and full-node statistics of delabeled materials. With the optimization of converting 0 values to NULL data, it solves the common pain points of traditional systems that cannot locate delabeled materials and whose statistical data is not practical.
[0018] Achieve fully automated and universal adaptation of order data across multiple business types: By leveraging contract number features, achieve fully automated matching and classification statistics of order data across multiple business types, such as spot and non-spot goods, without manual intervention. Unify statistical standards across systems and business types, ensuring complete consistency of data across production, operations, and finance departments, and adapt to the business type classification rules of various enterprises, providing core support for cross-departmental collaborative decision-making across the entire industry.
[0019] Our innovative lightweight temporary table reuse + CTE one-step calculation data processing strategy completely eliminates all redundant intermediate temporary tables, decomposes complex full-process statistics into simplified step-by-step calculations, and combines CTE one-step data processing to minimize the number of large table scans and table locks, thereby improving the system's data processing efficiency and operational stability from the bottom up. Report query response time is controlled in the second level, perfectly adapting to the large data volume, high real-time, and high concurrency operation requirements of various industry production lines.
[0020] Practical report design integrating immersive interaction: Based on standardized reports, it innovatively realizes immersive interactive display with master-detail table linkage and one-click drill-down, achieving seamless penetration of production data from summary to details. Report data can be directly exported and used without manual secondary processing; at the same time, it adopts high-precision numerical data to statistically analyze production data, ensuring data accuracy and meeting the actual use needs of industrial sites in various industries, significantly reducing the workload of production management personnel.
[0021] Create a closed-loop production data and de-labeled material control system: realize integrated aggregation and reverse traceability of production data from ERP order placement to finished product warehousing, and realize full life cycle control of de-labeled materials from generation to disposal, providing accurate data support for refined production management and process optimization for enterprises in various industries, and realizing intelligent upgrade of production management. Beneficial effects
[0022] Universally applicable across industries with extremely high promotional value: It breaks down industry and production line limitations, adapts to multi-process continuous production lines in various industries, and new processes can be quickly included in the statistical system simply by configuring name matching rules. It has the capability for industrial-scale promotion across the entire industry, solving the problems of single-industry adaptability and poor universality of existing technologies.
[0023] Extremely improved statistical efficiency: Based on a unified data platform and CTE one-step calculation strategy, it realizes automated aggregation of production data throughout the entire process and one-click table output, completely replacing the manual cross-system and cross-table aggregation mode. The efficiency of production data statistics is improved by more than 95%, and the report query response time is controlled within seconds, which greatly reduces the workload of production management personnel.
[0024] Precise control of delabeled materials: By using upstream and downstream process judgment rules that are aligned with the actual production line and CTE one-step statistics, the accuracy rate of delabeled material identification is 100%, the quantity of delabeled materials in each process is accurately counted, and the flow nodes of delabeled materials are quickly located. This solves the common industry problems of lack of control over delabeled materials and inaccurate cost accounting, and improves the level of refinement of enterprise production control.
[0025] Maximum cross-system data consistency and traceability: By linking the core identifiers of contract number + contract hash value + plan number, the data barriers between ERP and MES systems are broken down, ensuring the uniqueness, relevance and consistency of production data; it supports reverse penetration traceability of the entire process from finished product warehousing to ERP order placement, quickly locate the root cause of production anomalies, and provide accurate data support for production process optimization.
[0026] Highly efficient processing of large data volumes and strong system stability: The strategy of lightweight temporary table reuse + CTE one-step calculation reduces the number of large table scans and table locking from the bottom layer, greatly improving the system's data processing efficiency and operational stability. It is perfectly adapted to the large data volume, high real-time and high concurrency operation requirements of various industry production lines, ensuring the real-time generation and updating of production reports.
[0027] Ultimate optimization of cross-departmental collaboration capabilities: Enables fully automated matching and unified output of order data across multiple business types, ensuring complete consistency of data across production, operations, and finance departments. Report data can be directly used for production plan adjustments, cost accounting, and business decisions, providing accurate and unified data support for cross-departmental collaboration within the enterprise.
[0028] Low implementation cost and extremely easy maintenance and expansion: Seamlessly integrates with existing ERP and MES systems without requiring large-scale modifications to existing equipment and systems. Low implementation cost and simple operation allow production managers to get started quickly. Adding new processes / production lines only requires configuring name matching rules, without modifying the core table structure and logic. The system maintenance and expansion difficulty is the lowest in the industry.
[0029] Immersive interactive experience, tailored to real-world needs: Reports enable master-detail table linkage and one-click drill-down, providing an immersive interactive display that allows for seamless data flow from summary to detail. The operation is simple and highly efficient. Report data can be directly exported for use without manual secondary processing, perfectly meeting the actual needs of industrial sites.
[0030] Refined and intelligent upgrade of production management: Multi-dimensional and standardized production reports and full life-cycle control of substandard materials enable enterprise management to keep track of the production progress, product output, and abnormal situations of substandard materials in each process of the entire production line in real time; full-process production data traceability can quickly locate abnormal production nodes, providing accurate data support for enterprise process optimization and production process improvement, and realizing the refinement and intelligent upgrade of production management. Detailed Implementation
[0031] 1. Standardized deployment and configuration of the system in the early stage This invention builds a production report intelligent summary and de-labeled material statistics module in the existing MES system of industrial enterprises. It designs a universal production data statistics logic adaptable to multi-process continuous production lines in various industries, and uniformly defines core statistical indicators such as roll count / output and de-labeled roll count for each process. The contract number + contract hash value + plan number are set as the core association fields across ERP and MES, completing the core data association mapping of various modules such as the ERP sales order details table and the MES production process quality inspection record table, adapting to the data storage structure of various enterprises. It formulates universal de-labeled material judgment rules (empty sales contract number / inconsistent with target contract number) and unified statistical caliber. Based on contract number characteristics, it sets automatic matching rules for multiple business types such as spot / non-spot goods, adapting to the business type classification of various enterprises. In the MES database, it creates lightweight temporary tables such as #Production Department Reports and #Temporary Summary_Process Output, defining universal table structure fields (customer, product name, contract number, roll count / output / de-labeled roll count for each process, etc.), with the field value type using numeric (18,3). High-precision data ensures the accuracy of production data statistics and adapts to the data statistics needs of production lines in various industries.
[0032] 2. Real-time acquisition and synchronization of production data between ERP and MES dual systems. The control systems of each process on the production line upload real-time production data (contract number, plan number, raw material identifier, finished product identifier, finished product weight, finished product location, etc.) to the production process quality inspection record table of the MES system in real time, adapting to the data collection frequency and data dimensions of production lines in various industries; the system synchronizes the order quantity and contract hash value data of the ERP sales order details table and spot goods details table with the customer and product specification data of the MES production plan table to the corresponding lightweight temporary table at preset time intervals (preferably 5-10 minutes), ensuring the real-time performance and integrity of cross-system production data.
[0033] 3. Integrated aggregation of production data across the entire process, broken down into steps. Based on a pre-defined, simplified, step-by-step calculation strategy, production data is aggregated in an integrated manner to adapt to the production flow logic of various industries: 3.1 In the temporary table of the #Production Department Report, collect basic data such as customer, product name, contract hash value, and plan number from the MES production plan table by contract number; 3.2 Based on the contract number characteristics, the order quantity in the temporary table of the #Production Department report is updated by updating the order details in the ERP sales order details table of non-spot business and the ERP sales order spot details table of spot business through the contract hash value, thus completing the cross-system basic data fusion; 3.3 In the temporary summary_process output table, the number of rolls / output for each process is calculated from the production process quality inspection record table according to the fuzzy matching rules of plan number and process name, so as to realize the one-time summary of all process data; 3.4 Directly update the number of rolls / production batches for each process in the #Temporary Summary_Process Output Temporary Table to the #Production Department Report Temporary Table. At the same time, count the number of rolls and the weight of rolls entering the warehouse according to the plan number and the condition that the finished product warehouse location is not empty, and update the table to form a summary table of production data for the entire process. 3.5 Clean up the temporary table of #temporary summary_process output in a timely manner, complete the summary of basic production data, and release database resources.
[0034] 4. Intelligent identification and full-process tracking statistics of delabeled materials based on upstream and downstream links of the process. 4.1 CTE One-Step Reading of Core Data: Core data of the plan number can be read directly from the temporary table of the #Production Department Report through the CTE common expression, without the need to create redundant temporary tables for de-labeled materials, reducing data processing steps; 4.2 Accurate statistics of upstream and downstream links of the process: Based on the actual upstream and downstream material flow logic of the production line, the number of rolls that are decoupled from the contract for each process is counted in turn: The raw material warehouse information table is used as the source of upstream raw materials for the front-end process, and the finished product identifier of each process is used as the raw material identifier for the downstream process. Subsequent processes are counted in turn according to this upstream and downstream link. The COALESCE fault tolerance function is used to achieve zero-value fault tolerance and ensure that there are no null values in the statistical data. 4.3 Optimization of data for materials with missing labels: Convert 0 values to NULL for the statistically obtained number of missing contracts, optimize the report display effect, and improve the usability of the data; 4.4 Batch Direct Update to Main Table: Batch update the delabeled material data obtained from CTE statistics directly to the #Production Department Report Temporary Table, completing the seamless integration of delabeled materials with the entire production process data, without the need for intermediate data transfer.
[0035] 5. Merging data sources from multiple business types and standardizing output with unified definitions Integrate production data sources from multiple business types, including spot and non-spot goods, in ERP, and standardize the statistics of core indicators such as roll count / production and inbound weight for each business type according to a unified statistical standard. This ensures that the ERP order quantity and the MES production output are completely consistent. The merged production data is then fixed in the #Production Department Report Temporary Table in a preset general format to ensure that the data standards of the production, operations, and finance departments are consistent, there is no data confusion, and cross-departmental data collaboration is achieved.
[0036] 6. Intelligent generation and immersive interactive display of standardized production reports. The system automatically generates standardized production reports covering ERP order dimensions, MES production dimensions, and destandardized material dimensions based on the full-process production data in the temporary table of the #Production Department report, adapting to the production statistics needs of various industries. The reports are displayed in real time on the front end of the system, supporting multi-dimensional filtering and querying by contract number, plan number, process name, etc., and also providing a one-click export function in Excel / CSV format. The report data can be directly used for on-site production statistics without manual secondary processing. It realizes an immersive interactive display of master and slave table linkage and one-click drill-down. By clicking on any plan number / process name in the master table, the system automatically displays the detailed production data of the corresponding process / stage, realizing seamless penetration of production data from summary to details.
[0037] 7. Full lifecycle traceability of production data and full-process control of delabeled materials. Production managers can directly access ERP original order data and MES original production data for each process by selecting any contract number, plan number, raw material identifier, or finished product identifier in the system. This enables full-process reverse traceability from finished product warehousing to ERP order placement, quickly locating the root cause of production anomalies. When de-labeled materials undergo status changes such as re-binding contracts, scrapping, or outsourcing, managers can enter processing records in the system and update their status in real time, achieving full lifecycle management of de-labeled materials from generation to disposal. Simultaneously, the system supports multi-dimensional statistical analysis based on production data, providing precise data support for enterprise production plan adjustments and process optimization, adapting to the production management needs of enterprises in various industries.
[0038] 8. Minimalist Temporary Table Cleanup After the report generation and query are completed, the system automatically cleans up all lightweight temporary tables, such as the #Production Department report, to quickly release database resources, ensure long-term stable operation of the system, improve overall system performance, and adapt to the database management needs of enterprises in various industries.
Claims
1. A method for intelligent summarization of end-to-end production reports and statistical analysis of de-labeled materials in a production line ERP-MES integrated system, characterized in that, Includes the following steps: (1) Build a unified data architecture for the entire process of ERP-MES that is applicable to the whole industry, design a general production data statistical logic that is compatible with the continuous production lines of multiple processes in various industries, define core statistical indicators in a unified manner, take the production process quality inspection record table as the unified data base for the entire process, and complete the seamless connection configuration with the existing ERP system and MES system. (2) Set the contract number + contract hash value + plan number as the unique core serial identifier across ERP-MES, associate it with auxiliary identifiers such as raw material identifier and finished product identifier, complete the core data association mapping of each business module, and realize the precise association of upstream and downstream material flow links of each process based on raw material identifier / finished product identifier; (3) Create lightweight temporary tables such as #Production Department Report and #Temporary Summary_Process Output, and formulate a simplified step-by-step calculation strategy of basic data insertion - process output summary - de-standardized material statistics - report data integration, and discard all redundant temporary tables; (4) Collect production data of each process in real time and upload it to the production process quality inspection record table. Synchronize the core data of the ERP and MES dual systems to the corresponding lightweight temporary table on a regular basis to ensure the real-time performance and integrity of the data. (5) The data of each temporary table is aggregated step by step to form a closed-loop data link of ERP order - raw material input - process processing - finished product warehousing, and a summary table of production data for the whole process is generated; (6) Preset general-purpose rules for judging off-label materials. Based on the material flow logic of the upstream and downstream processes of the actual production line, the number of off-label rolls in each process is calculated and counted in one step by CTE. Zero-value fault tolerance is achieved through fault tolerance function. Zero-value to NULL optimization is performed on off-label material data. There is no need to create a redundant temporary table for off-label material statistics. (7) Integrate data sources from multiple business types, such as spot and non-spot goods, based on contract number characteristics, and perform standardized statistics according to unified statistical standards to unify the ERP order quantity and MES production output standards, ensuring data consistency across departments; (8) Based on the full-process production data summary table, it automatically generates standardized production reports covering ERP orders, MES production, and destandardized materials. It supports multi-dimensional filtering and querying, one-click export and master-detail table linkage, and one-click drill-down immersive interactive display. The report data does not require manual secondary processing. (9) Supports querying original data through core identifiers and auxiliary identifiers to achieve reverse traceability of production data throughout the entire process, and at the same time completes the status update and full life cycle management of delabeled materials; (10) After the report is generated and queried, all lightweight temporary tables are automatically cleaned up to release database resources.
2. The method according to claim 1, characterized in that, The core statistical indicators mentioned in step (1) include the number of rolls / output for each process, the number of rolls removed from the contract, the number of rolls put into storage, and the weight put into storage. The architecture only needs to configure the fuzzy matching rules for the process name when adding a new process / processing unit. There is no need to modify the table structure and core statistical logic. It has universal adaptability across the industry and extremely simple scalability.
3. The method according to claim 1, characterized in that, The general rule for determining off-label materials in step (6) is: if a material meets any of the following conditions, such as an empty sales contract number or a sales contract number that is inconsistent with the target statistical contract number, it is determined to be off-label material. This rule is applicable to industrial production lines in various industries.
4. The method according to claim 1, characterized in that, The step-by-step integrated summary described in step (5) is as follows: basic data is collected from the MES production plan table, and order data of multiple business types such as spot / non-spot goods in ERP are automatically matched based on the contract number feature. The number of rolls / output of the whole process is counted according to the fuzzy matching rules of plan number and process name. The inbound data is counted according to the non-empty condition of finished product warehouse location. Finally, the data is integrated into the whole process production data summary table by batch direct update of the main table, without the need for intermediate data transfer.
5. The method according to claim 1, characterized in that, The unified statistical caliber mentioned in step (7) is to standardize the core indicators of roll number / production and warehouse weight for each business type, and to achieve accurate matching between ERP order quantity and MES production data through contract hash value, so as to ensure that the data output format of production, operation and finance departments is completely consistent with the statistical results, and to achieve cross-departmental data collaboration.
6. The method according to claim 1, characterized in that, The step (6) of calculating the number of rolls that have been removed from the production line based on the upstream and downstream material flow logic of the actual production line process is as follows: taking the raw material warehouse information table as the upstream raw material source of the front-end process and the finished product identifier of each process as the raw material identifier of the downstream process, the number of rolls that have been removed from the production line process of each process is calculated in turn to adapt to the upstream and downstream flow logic of the production line process of each industry.
7. The method according to any one of claims 1-6, characterized in that, The method is implemented in the MES system through stored procedures. It adopts a high-performance data processing strategy of lightweight temporary table reuse, CTE one-step calculation, and batch direct update of the main table. It eliminates all redundant intermediate temporary tables, avoids repeated scanning of large tables and multiple table locks, and controls the report query response time to the second level. It is suitable for the large data volume, high real-time and high concurrency operation requirements of production lines in various industries.
8. The method according to any one of claims 1-6, characterized in that, All production data are statistically analyzed using the numeric(18,3) high-precision numerical type. The statistical data of destandardized materials are converted to NULL after being processed by converting 0 values to NULL and then included in the standardized production report. The report realizes the immersive interactive display of master-slave table linkage and one-click drill-down. The report data can be directly exported and used without manual secondary processing, which fits the actual use needs of industrial sites.
9. The method according to any one of claims 1-6, characterized in that, The proposed method seamlessly integrates with existing ERP and MES systems of industrial enterprises across various industries, requiring no large-scale modification of existing equipment and systems. It boasts low implementation costs, simple operation, and quick learning curve for production management personnel. It is also compatible with the production data statistics and off-standard material control needs of multi-process continuous production lines in various industries, possessing mature engineering value and the ability to be promoted industrially across the entire industry.
10. The method according to any one of claims 1-6, characterized in that, The method supports full lifecycle management of delabeled materials from generation to disposal, and can update the status of delabeled materials in real time, such as rebinding contracts, scrapping, and outsourcing processing. At the same time, it supports full-process reverse traceability of production data from finished product warehousing to ERP order placement, quickly locating the root cause of production anomalies and providing accurate data support for enterprise process optimization and production management upgrades.