ERP & MES integrated data fusion method based on material full-dimension wide table

Abstract

The application discloses an ERP&MES integrated data fusion method based on a material full-dimension wide table, and relates to the technical field of industrial production information integration.The application takes a unique product code as a primary key, constructs a material wide table covering the whole life cycle as a core base, matches TVP standardized interaction, double-layer mistake-proofing verification, AI access reservation capability, is compatible with SQL Server and Gauss DB dual databases, and accurately retrieves fields according to requirements in the core design of each business module.The application realizes homology, unified caliber, efficient operation, safety controllability and intelligent scalability of ERP-MES-finance data, solves problems such as data island, inconsistent caliber, difficult business linkage and high development and maintenance cost in the traditional architecture, supports complex business and intelligent upgrading, and is suitable for integrated management in multiple industrial production fields.The application has been completely applied in actual industrial system ERP&MES, can be long-term and stable in real production environment, and has mature engineering value and industrialization popularization capability.

Description

Technical Field

[0001] This invention relates to the field of industrial production ERP & MES system integration and data fusion technology. Specifically, it relates to a design method that uses a full-dimensional material data table as the core data foundation, and is equipped with standardized interaction, double-layer error prevention, and intelligent expansion capabilities to achieve deep integration and end-to-end connectivity of ERP, MES, and financial data. It is compatible with both SQL Server and GaussDB databases, supports precise field retrieval by various business modules as needed, ensures efficient data operation, and provides underlying architectural support for cross-system business linkage, unified data management and control, and intelligent upgrades. It is suitable for multiple industrial production fields such as metallurgy, machinery, and manufacturing. Background Technology

[0002] In the traditional information architecture of industrial enterprises, ERP focuses on business management such as contracts, orders, inventory, delivery, and financial settlement; MES focuses on production execution, process parameters, equipment data, quality inspection, and process tracking; and financial data is the core control dimension of ERP. All three generally share the following core problems: (1) Data silos: ERP, MES and financial data are stored in separate databases and tables, core related fields are missing, data association relies on complex multi-table queries and interface calls, and true underlying data integration cannot be achieved. Adaptability is limited by the type of production materials. (2) Inefficient operation: Cross-system queries and data updates require multiple cross-database associations of dozens of data tables, and data inconsistencies are likely to occur. The quantity, weight, status, unit price, total price, and freight data of ERP and MES are difficult to match accurately. (3) Business disconnect: Without a unified data base, core operations such as contract modification, production parameter adjustment, and financial settlement cannot be synchronized across the entire chain, and the complex business logic of subsequent cross-system multi-table linkage updates cannot be implemented at all. (4) Decentralized record keeping: There is no unified record keeping system for the entire process. The operators, addresses, and times of operation in production, quality inspection, finance, and delivery are stored in a scattered manner, making data traceability and responsibility determination difficult. (5) Industry perception bias: Existing technology believes that full-dimensional wide tables will inevitably lead to low query efficiency due to the large number of fields, and no technical solution for on-demand retrieval has been designed, making it difficult for the wide table model to be applied in industrial production; (6) Complex architecture: Reports, analysis and traceability require data splicing across databases and tables, which is extremely costly to develop and maintain and cannot support the refined management needs of continuous production of multiple processes; (7) Non-standardized interaction: Cross-module data transmission relies on scattered parameters, which is prone to omissions and errors. There is a lack of a unified data interaction interface, and data consistency is difficult to guarantee. (8) Lack of error prevention mechanism: There is no multi-layer verification during data entry and business execution. Abnormal data can easily contaminate core data, leading to a disconnect in the entire business chain; (9) Insufficient intelligent expansion: The existing architecture does not reserve intelligent access interfaces, making it unable to adapt to subsequent upgrade needs such as AI management and control and automated decision-making. The existing technology lacks an integrated data model that uses a wide table as the core data foundation, is equipped with standardized interaction and error prevention mechanisms, supports intelligent expansion, can achieve deep integration of ERP, MES and finance, can retrieve data on demand to ensure efficiency, has no material type restrictions, and can support subsequent complex business linkages. This has become a core bottleneck restricting the digital upgrade of industrial enterprises. Summary of the Invention

[0003] Purpose of the invention This invention provides an integrated data fusion method for ERP & MES based on a wide table covering all dimensions of materials. It constructs a core data foundation covering contracts, plans, raw materials, production, processes, quality inspection, inventory, delivery, and financial settlement. It is equipped with key capabilities such as standardized interactive table-valued parameters (TVP), double-layer error-proofing verification, and reserved AI access. It unifies ERP business data, MES execution data, and financial control data into a single wide table, supporting each business module to accurately retrieve fields as needed rather than performing full queries, ensuring efficient data operations. This achieves unified data, efficient querying, business linkage, full visibility, standardized interaction, security and controllability, and intelligent scalability. It is not limited by production material type and is adaptable to multiple industrial production fields. Simultaneously, it provides underlying data support for complex business processes such as subsequent contract modifications, cross-system multi-table linked updates, and intelligent management, solving the core industry problem of "no integration without a foundation, no implementation without supporting components."

[0004] Technical solution This invention uses a comprehensive, multi-dimensional material table as the sole data foundation for deep integration of ERP, MES, and finance, supported by three core capabilities: standardized interaction, dual-layer error prevention, and intelligent expansion. Without this foundation and supporting system, cross-system business linkage, multi-table synchronous updates, and intelligent upgrades would be impossible. Furthermore, it is not limited to specific material types and is applicable to the entire industrial production field. The specific technical solution is as follows: (1) Establish a full-dimensional wide table for unique material identification: Using the unique code of finished products (such as finished product roll number, finished product part number, finished product batch number, etc.) as the primary key, ensure that each material unit corresponds to a unique data record, integrate full-dimensional fields of contracts, plans, raw materials, production, processes, quality inspection, inventory, delivery, and financial settlement, covering the entire life cycle of data from raw material entry to finished product delivery and financial settlement, and become the core data foundation of ERP-MES-financial sharing; the association logic between the full-dimensional wide table of this invention and each business module can be intuitively reflected. All business data are uniformly anchored to the wide table through "unique code of finished product + contract hash value", completely breaking the data silos and intuitively verifying the integration mode of "single table as the base".

[0005] (2) Systematically integrate core fields across dimensions: ERP Dimension: Integrates core fields such as contract number, contract hash value, customer name, material material, specifications, order quantity, unit price, total price, and delivery deadline to achieve full storage of sales contract data; MES Dimension: Integrates core fields such as production unit / production line, raw material / finished product parameters, process parameters, quality inspection results, production time, and operators to achieve refined storage of production execution data; Financial dimension: Integrates core fields such as final unit price, original unit price, freight, settlement amount, acceptance amount, settlement order number, settlement date, and settlement completion status to achieve deep binding of financial settlement data with contract and production data; Inventory and shipment dimension: Integrates core fields such as storage location, storage age, shipment number, shipment status, transportation information, and ownership information to achieve full-process storage of logistics and warehousing data; (3) Design a full-process operation traceability field system: For each core business process such as production, process, quality inspection, finance, delivery, and contract modification, design an independent "entry person - entry address - entry time - modification person - modification address - modification time" field system to achieve accurate recording and traceability of the entire process operation behavior of ERP-MES-finance; (4) Dual-track storage of planned and actual data: Simultaneously design fields for planned parameters (planned specifications, planned process indicators, planned performance parameters, etc.) and actual parameters (actual specifications, laboratory process indicators, actual performance parameters, etc.) to achieve real-time comparison of planned and actual values ​​during production, supporting ERP financial control and MES production process optimization; (5) Accurate field retrieval on demand to ensure operational efficiency: Although the wide table integrates all dimensions of fields, each business module (production module, finance module, sales module, quality inspection module) accurately retrieves the specified fields according to its own business needs, instead of using select full query. This avoids the efficiency problem caused by a large number of fields from the query logic, and ensures the efficiency of single table query and update. (6) Dual database compatible architecture design: adopts SQL Server and GaussDB generalized field types (nvarchar, float, datetime, numeric, etc.), primary key index, default value constraint and other architecture design compatible with dual database features, realizes a wide table architecture to be seamlessly deployed in dual database environment; (7) Real-time data synchronization and status control: When any business process changes data (production parameter update, financial unit price adjustment, delivery status change, etc.), the corresponding fields of the wide table are updated in real time. Combined with control fields such as delivery status, sales lock, and settlement completion, the data status is precisely locked and controlled, providing accurate and real-time underlying data for subsequent cross-system linkage operations. (8) Supporting TVP Standardized Interaction System: For each business module such as ERP, MES, finance, and logistics, develop dedicated table value parameters (TVP) as the standard interface for data interaction. The table type fields are fully aligned with the material full-dimensional wide table. All business operations (contract binding, production data entry, financial settlement) are driven by stored procedures to ensure the consistency and security of wide table data and reduce the cost of cross-module data interaction. (9) Construct a two-layer error prevention and verification mechanism: All business operations are built with a two-layer logic of "front-end verification + stored procedure verification", including data non-empty verification (contract number, hash value, operator must be filled in), business rule verification (customer insufficient balance prohibits delivery, duplicate contract number prohibits new addition), and data format verification (amount and date format are standardized) to avoid abnormal data from the source and ensure the accuracy of core base data; (10) Reserved AI intelligent access interface: Reserved AI model configuration fields (model name, interface name, master key, model version number) in the wide table, and develop dedicated stored procedures and table types for AI operations to support operations such as adding, modifying, deleting, and setting default values ​​of AI models, providing extended support for subsequent intelligent production management and control (such as process parameter optimization and quality anomaly early warning); (11) Supporting subsequent complex business linkage: This wide table and its supporting system serve as the core foundation and are a necessary prerequisite for the implementation of complex business such as mandatory contract modification, ERP-MES multi-table linkage update, and intelligent decision-making, so as to achieve the effect of one modification on the whole system.

[0006] 3. Core Innovation Points (1) Construct an integrated system of “base + supporting”: take the full-dimensional wide table as the core base, and support TVP standardized interaction, double-layer error prevention, and AI access reserved capabilities to solve the industry pain point of the traditional architecture “only talks about integration, not implementation”, and make ERP-MES-finance integration from a concept to a complete solution that can be directly deployed; (2) A full-dimensional data base without material type restrictions: It integrates the entire life cycle data into a single wide table, which is not limited to specific materials such as rolls and parts, and is suitable for multiple industrial production fields such as metallurgy, machinery, and manufacturing, solving the core problem of "no true integration without a base"; (3) High-efficiency wide table design with on-demand retrieval: Breaking the industry's perception that "wide tables with many fields are inefficient", the system accurately retrieves specified fields from each module instead of querying the entire table, ensuring that single-table queries and updates are more efficient than traditional multi-table joins. (4) Deep integration of financial and production / contract data: Integrate all dimensions of financial settlement fields into wide tables to achieve a one-to-one correspondence between unit price, freight, settlement amount and contract and production data, and completely solve the problem of inconsistent data standards between ERP and MES; (5) Unified operation traceability system for all processes: Design standardized traceability fields for all core business processes to achieve traceability of operation behavior and delineation of responsibility, while taking into account the needs of ERP compliance control and MES production control; (6) TVP + Standardized Interaction of Stored Procedures: By strongly binding the dedicated table type with the stored procedure, a "standard interface" for data interaction is built to avoid inconsistent data formats across modules and adapt to high-frequency data operations in continuous production of multiple processes; (7) Dual-layer error prevention ensures data accuracy: front-end and stored procedure dual verification covers the entire process of data entry, business execution and status change, avoiding abnormal data from polluting the core base from the source; (8) Scalable intelligent upgrade design: Reserves AI model configuration fields and operation interfaces to support seamless access to subsequent intelligent functions and adapt to the long-term needs of digital upgrade of industrial enterprises; (9) Dual database compatibility and self-controllability: natively supports SQL Server and GaussDB, and can be adapted to domestic database deployment without large-scale modifications, meeting the self-controllability needs of industrial enterprises.

[0007] 4. Beneficial effects (1) Achieve material-free underlying data integration: Wide table + supporting system allows ERP, MES and finance to share the same set of full-dimensional data, completely breaking down data silos. Without this system, subsequent cross-system linkage cannot be implemented. It is also compatible with multiple industrial production fields, achieving "data from the same source and unified standards". (2) Ensure the efficiency of wide tables and break industry perception bias: By accurately retrieving fields as needed, the efficiency problem of full query is avoided, and the response speed of single table operation is far better than traditional multi-table query; (3) Precise matching of financial and production / contract data: The financial settlement field is deeply bound to the production and contract data, improving the accuracy of financial accounting by 100% and completely solving the problem of inconsistent data standards; (4) Standardized interaction reduces implementation costs: TVP serves as a unified data interface, reducing cross-module data interaction errors, and stored procedure-driven operations reduce development and maintenance costs by more than 80%; (5) Double-layer error prevention enhances data security: Abnormal data is avoided from the source, and the accuracy of core base data reaches 100%, avoiding business disruptions caused by data errors; (6) Supports intelligent upgrades and long-term expansion: Reserves AI access interfaces, enabling intelligent function upgrades without reconstructing the architecture, and adapting to the long-term needs of enterprise digital transformation; (7) Full-process operation traceability: The unified traceability system allows all operational behaviors to be accurately traced, improving the efficiency of abnormal problem location by more than 90%; (8) Support the implementation of complex business linkage: Provide underlying support for contract modification, cross-system synchronous updates, intelligent decision-making, etc., so that a modification in one place takes effect throughout the entire system; (9) Dual database compatibility conforms to the trend of independent control: natively supports dual databases, adapts to the needs of domestic deployment, and can meet policy requirements without large-scale reconstruction; (10) Full lifecycle visualization and control: Managers can view the full-chain information of each material unit, including contracts, production, quality inspection, finance, and delivery, in real time through a wide table, so as to achieve refined control from order acceptance to settlement. Detailed Implementation

[0008] Establish a comprehensive material data table and supporting system: Create a comprehensive material data table (such as a production process quality inspection record table) in SQL Server / GaussDB database, using the unique finished product code as the clustered primary key. Integrate comprehensive data fields from ERP, MES, finance, and inventory / shipping. Design standardized trace fields and planned-actual dual-track parameter fields. Simultaneously create dedicated TVP table types for each business module (such as a purchase contract operation table type and an AI operation table type), and develop supporting stored procedures (contract binding, production entry, AI configuration operations, etc.). Data writing and real-time updating at different stages: When a contract is created, the ERP system uses a dedicated TVP and stored procedure for procurement / sales contracts to write core data such as contract number, contract hash value, customer, and unit price into a wide table. After production and quality inspection are completed, the MES system updates data such as process parameters and quality inspection results to the wide table in real time through the dedicated TVP and stored procedures for the production process. During financial settlement, the financial module uses a dedicated settlement TVP and stored procedure to write data such as the final unit price and settlement order number into a wide table, which is then linked to the contract and production data. When materials are received and shipped, the inventory logistics module updates the wide table storage location, shipping status, and transportation information in real time through logistics-specific TVP and stored procedures. When configuring an AI model, the model information is written into the reserved fields of the wide table through the dedicated TVP and stored procedures for AI operations, thus completing the AI ​​access configuration. Each module retrieves fields precisely as needed: the production module retrieves only fields related to production line, process, and quality inspection; the finance module retrieves only fields related to unit price and settlement; and the AI ​​module retrieves only fields related to model configuration and production parameters. All modules use precise field queries rather than full queries to ensure operational efficiency. Full-process operation tracking and error prevention verification: When operators enter / modify data, the system automatically writes the operator, address, and time into the tracking field of the wide table; at the same time, it triggers dual verification by the front end and the stored procedure, and the wide table data can only be updated after the verification passes. Comparison and verification of planned and actual data: The MES system uses wide table planning and actual parameter fields to verify in real time whether the production process meets contract requirements, while the ERP system uses the comparison data to control order fulfillment. AI Model Access and Operation: By configuring AI models through reserved interfaces, the system can accurately retrieve the fields required for training and inference, such as process parameters and quality data, from the wide table, supporting intelligent applications; Implementing complex business processes: Based on wide tables and supporting systems, we developed functions such as mandatory contract modification and cross-system multi-table linkage updates, enabling changes in one place to take effect simultaneously across the entire system. Multi-condition fast filtering and statistics: The front-end interface, reports, and traceability functions directly read wide table data and support fast filtering by conditions such as finished product code, contract number, customer, production time, and settlement status, without the need for cross-database and cross-table concatenation.

Claims

1. A data fusion method for integrated ERP & MES based on a wide table covering all dimensions of materials, characterized in that, An integrated architecture of "wide table base + supporting system" is constructed, with the full-dimensional material wide table as the core base for deep integration of ERP, MES and finance. It is equipped with TVP standardized interaction, double-layer error prevention verification, and reserved AI access capabilities. Without this architecture, cross-system business linkage, multi-table synchronous updates and intelligent upgrades cannot be implemented. Moreover, there are no restrictions on the type of production materials, and it is suitable for multiple industrial production fields, specifically including: Establish a full-dimensional wide table for materials with the unique finished product code as the clustered primary key. The primary key ensures that each material unit corresponds to a unique data record. The wide table integrates fields covering the entire lifecycle of contracts, plans, raw materials, production, processes, quality inspection, inventory, delivery, and financial settlement. At the same time, it designs independent operation trace fields for each core business link, namely "Entry Person - Entry Address - Entry Time - Modification Person - Modification Address - Modification Time". The wide table integrates core fields from ERP, MES, finance, and inventory / shipping across different dimensions. The finance dimension integrates fields such as final unit price, original unit price, freight, acceptance amount, settlement order number, and settlement completion status, enabling deep integration of financial data with contract and production data. The wide table synchronously designs planned and actual parameter fields. Planned parameters include planned specifications, planned process indicators, and planned performance parameters, while actual parameters include measured specifications, laboratory process indicators, and measured performance parameters, enabling real-time comparison between planned and actual values. Each business module can accurately retrieve specified fields from the wide table according to its own needs, and use a non-full query method to ensure the efficiency of wide table operations; After each business process is completed, the ERP, MES, finance, and inventory logistics systems use TVP table types and stored procedures to write / update data to the corresponding fields of the wide table in real time, achieving data consistency and real-time synchronization. Wide tables adopt common field types and architecture design of SQL Server and GaussDB to achieve seamless deployment in dual database environments; All business operations are protected by a two-layer error prevention mechanism of "front-end verification + stored procedure verification" to ensure the accuracy of core base data; The wide table reserves AI model configuration fields, and develops dedicated TVPs and stored procedures for AI operations to support adding, modifying, deleting, and setting default values ​​for AI models; Using the contract hash value as the core associated field, we can achieve "one item, one code; one contract, one identifier; and seamless data flow across the entire chain".

2. The method according to claim 1, characterized in that, The aforementioned wide table and supporting system are necessary prerequisites for the implementation of complex business processes such as mandatory contract modification, multi-table linkage updates in ERP-MES, and intelligent decision-making, enabling changes in one place to take effect simultaneously throughout the entire system.

3. The method according to claim 1, characterized in that, The unique code for the finished product includes unique material identification codes such as finished product roll number, finished product part number, and finished product batch number, without any specific material type restrictions.

4. The method according to claim 1, characterized in that, The TVP table type fields are fully aligned with the wide table, and all business operations are driven by stored procedures, ensuring the standardization and consistency of data interaction.

5. The method according to claim 1, characterized in that, The dual-layer error prevention mechanism includes data non-empty verification, business rule verification, and data format verification, covering the entire process of data entry, business execution, and status change.

6. The method according to claim 1, characterized in that, The AI ​​access reservation includes model configuration fields and dedicated operation interfaces, supporting seamless integration of subsequent intelligent production management and control functions.

7. The method according to any one of claims 1-6, characterized in that, The method is applicable to multiple industrial production fields such as metallurgy, machinery, and manufacturing. The reporting, analysis, and traceability functions can directly read wide table data without the need for cross-database and cross-table concatenation.

8. The method according to any one of claims 1-6, characterized in that, The core of determining infringement is to fully cover the core technical features of "wide table base + TVP interaction + double-layer error prevention + data fusion". Any method that uses this core architecture to achieve the integration of ERP-MES-finance, regardless of whether it adds or removes non-core fields or functions, constitutes infringement.