A contract dynamic correction integrated method based on unified stored procedure for realizing deep fusion of ERP and MES

Abstract

This invention discloses an integrated method for dynamic contract modification based on a unified stored procedure, achieving deep integration of ERP and MES. Belonging to the field of intelligent manufacturing, it is applicable to continuous production industries such as metallurgy and steel. This invention uses a unified table type as the parameter entry point, integrating eight operations into a single stored procedure to construct a closed-loop process of application-review-execution-lock-off. Through dual-review interlocking and intelligent identification of production status, it prevents tampering with completed products. During modification execution, it achieves one-click synchronization of multiple tables in ERP and MES. Full-process operation traceability, critical data locking, and transaction control ensure security, and it is compatible with SQL Server and GaussDB. This invention breaks down the barriers between dual systems, solving pain points such as data asynchrony, non-closed-loop processes, and chaotic permissions. It has been practically applied and possesses mature engineering and promotional value.

Description

Technical Field

[0001] This invention belongs to the field of intelligent manufacturing and industrial digitalization technology, specifically involving an integrated control method for dynamic contract modification that deeply integrates Enterprise Resource Planning (ERP) and Manufacturing Execution System (MES). It is particularly suitable for industries with continuous multi-process production such as metallurgy and steel. It achieves full-process control of contract modification and cross-system data synchronization through a unified stored procedure, and is compatible with SQL Server and GaussDB databases. Background Technology

[0002] In current industrial production management systems, ERP systems and MES systems generally suffer from architectural, data, and process separation, resulting in a typical dual-system independent operation mode. The ERP system is mainly responsible for sales orders, financial settlement, and shipment management, while the MES system is mainly responsible for production planning, process execution, quality inspection, and raw material inventory control. The two lack a unified underlying data interaction mechanism and process collaboration standard.

[0003] In actual production and operation, it is extremely common for customers to request modifications to contract parameters due to changes in demand after a sales contract has been signed and production has commenced. These modifications involve core information such as product specifications, quantity, unit price, process requirements, and delivery standards. Traditional handling methods have the following prominent problems: The data from the two systems is fragmented, and contract parameter modifications cannot be synchronized between ERP and MES. Manual table-by-table modifications are prone to data inconsistencies and omissions in updates, resulting in discrepancies between production plans and sales contracts, and errors in financial settlement amounts. Contract modification lacks a standardized, closed-loop process control mechanism. Operations such as application, editing, deletion, and review are scattered, and the boundaries of authority are unclear. Even after the review is completed, applications can still be modified or deleted at will, resulting in high business control risks. Without a production status identification mechanism, products that have completed the final production process can still have their parameters modified, which can easily lead to distorted production records, product scrap, and economic losses for the company. The review process lacks a dual verification mechanism for production feasibility and financial security. Modifications can be implemented once a single department approves them, failing to balance production rationality with the protection of corporate interests. The operation process lacks complete traceability, and the application, review, modification, and execution stages cannot be tracked, making it difficult to quickly locate problems and responsibilities after anomalies occur; Most implementation solutions only support a single database, have poor compatibility, cannot meet the needs of enterprises deploying heterogeneous databases, and have high system transformation and migration costs.

[0004] To address the common problems in the aforementioned industries, this invention proposes an integrated method for dynamic contract correction based on the deep integration of ERP and MES using a unified stored procedure. This method constructs a standardized, secure, and traceable end-to-end management and control system, providing an efficient, stable, and scalable approach for the integration of industrial software systems. Summary of the Invention

[0005] Purpose of the invention This invention aims to provide an integrated method for dynamic contract modification based on a unified stored procedure to achieve deep integration of ERP and MES. It solves problems such as data asynchrony, non-closed-loop processes, unclear permissions, lack of production protection, untraceable operations, and poor database compatibility in the traditional dual-system model. It achieves standardized control of the entire contract modification process, one-click data synchronization across systems, intelligent identification of production status, dual-audit security verification, and full-link operation traceability. It demonstrates the technical implementation capabilities of the integrated ERP and MES architecture and provides a referable and replicable system integration solution for industries with multi-process continuous production. Core technology solutions

[0006] This invention integrates eight operation types through a single unified stored procedure, constructing a fully closed-loop control process encompassing application, adjustment, dual review, execution modification, and data locking. This achieves deep integration and synchronized data updates between the ERP and MES systems. The specific technical solution is as follows: The unified parameter entry design defines read-only table type parameters that are compatible with SQL Server and GaussDB databases. It uniformly receives information such as operation type, contract hash value, parameter name, parameter value, operator, operation address, and modification reason, providing a standardized data input interface for the entire operation process.

[0007] The system fully integrates eight operation types: adding applications, modifying applications, deleting applications, production review, production cancellation review, financial review, financial cancellation review, and executing modification operations. It covers all business processes of contract modification from initiation to completion, achieving unified management across all scenarios.

[0008] The process status and permission control mechanism allows new, modified, and deleted applications to be executed only when they are not yet approved. Once either the production or finance department has completed the approval, the relevant operation permissions are immediately locked, prohibiting further modification or deletion. The execution of modification operations is subject to a dual review and interlock condition between production and finance. Execution can only be triggered when both departments have completed the review and have not cancelled the review, ensuring business security and compliance.

[0009] The dual-audit security verification mechanism includes a production audit to determine whether parameter modifications meet production process requirements, and adjustments can only be made for products that have not yet completed final production; and a financial audit to verify key data such as quantity, unit price, and amount, ensuring that modifications do not affect the company's financial security and profit accounting. The dual audits jointly guarantee the rationality of business operations.

[0010] When performing modification operations on the multi-table linkage update of ERP and MES, the entire data tables of the ERP system and MES system are updated synchronously according to the parameter type: the sales order details table, sales delivery information table, and financial settlement data of the ERP system are updated; the core data such as the production plan table, production process quality inspection record table, and hot-rolled coil warehouse information table of the MES system are updated synchronously, so as to realize that a modification in one place will be synchronized throughout the entire system.

[0011] Intelligent production status identification and protection: The system automatically identifies the final production unit based on the product name to determine whether the product has completed all production processes; it only allows updates to data of units that have not yet completed final production, while data of units that have completed final processes is permanently locked and prohibited from any modification, thus preventing tampering with production records and abnormal product scrapping.

[0012] The entire process is traceable and auditable. All operations, including application, review, modification, and execution, are automatically recorded, including the operator, operation address, operation time, original and new parameter values. After the modification is completed, the execution information is recorded and the data is permanently locked, enabling full traceability, problem localization, and clear responsibility.

[0013] The dual-database compatibility design adapts the syntax and mechanisms of SQL Server and GaussDB databases, unifies the core business logic, and only makes lightweight adaptation adjustments to the database-specific syntax, enabling a single core solution to be seamlessly deployed in a dual-database environment, thereby improving system compatibility and scalability.

[0014] The transaction control and exception protection adopt a complete transaction control mechanism. All operations are executed within a unified transaction. If an exception occurs in any step, the system will immediately roll back to ensure data consistency and security. The built-in exception capture and handling module outputs error information in real time, improving system stability and fault tolerance. Key innovation points

[0015] Achieve deep and organic integration of ERP and MES systems, break down data barriers between the two systems through unified stored procedures, and realize cross-system data consistency, synchronous updates, and process collaboration; An integrated process for eight major operation types has been established, forming a closed-loop control system encompassing application, adjustment, review, execution, and locking, with no operational gaps or access control vulnerabilities. Establish a dual audit and interlocking mechanism for production and finance to balance production feasibility and financial security, and avoid business risks from the source of the process. Intelligent identification of the final production unit and production completion status enables refined control that protects produced data and allows for adjustment of unproduced data; Full-process operation tracking and data locking ensure that every step of the operation is traceable and verifiable, thereby improving data security and management standardization; It is natively compatible with both SQL Server and GaussDB databases, and a single logic supports deployment on both platforms, meeting the needs of enterprises for domestic and heterogeneous environments; All functions are integrated into a single stored procedure, which has a clear structure, is easy to maintain, and executes efficiently. It eliminates the need for multi-node and multi-program collaboration, significantly reducing system complexity. Beneficial effects

[0016] It truly achieves integrated and collaborative operation of ERP and MES, solving industry pain points such as data asynchrony and inconsistent processes in traditional dual systems, with a data update accuracy rate of 100%. The entire contract modification process is standardized and under closed-loop management, with clear authority boundaries, safe and controllable operation, and completely eliminates risks such as unauthorized modification, malicious deletion, and duplicate execution. Cross-system data update efficiency has been significantly improved, optimizing the traditional manual multi-step operation into a one-click system execution, with a response speed increase of more than 100 times; The intelligent production status identification and data protection mechanism effectively prevents manufactured products from being modified abnormally, ensures the authenticity and validity of production records, and reduces economic losses for enterprises. The dual review mechanism takes into account customer needs, production safety, and financial interests, thereby improving the level of enterprise contract management and risk control capabilities. The ability to record and trace the entire process facilitates anomaly investigation, responsibility identification, and business review, thereby improving management transparency and standardization. The dual-database compatibility design reduces the cost of enterprise system transformation and migration, supports the deployment of domestic databases, and conforms to the development trend of independent and controllable industrial software. The overall architecture is simple, efficient, stable, and easy to expand, providing a universal and standardized system integration solution for multi-process continuous production industries such as metallurgy and steel. Detailed Implementation Implementation Environment

[0017] This invention is based on SQL Server and GaussDB databases and is deployed in the production management system of multi-process continuous production enterprises. It connects with ERP sales management module, financial settlement module, MES production planning module, process quality inspection module, and raw material inventory module. It is suitable for full-scenario business processing where customers request parameter modifications after the sales contract is issued and production is started. Implementation steps

[0018] Create a unified table type and process control table to define a read-only table type that adapts to both databases as a unified input parameter, including fields such as operation type, contract hash value, parameter name, parameter value, reason for modification, operator, and operation address; establish a contract modification process control table to record information and status of the entire process of application, review, and execution.

[0019] Create a unified stored procedure that is compatible with both SQL Server and GaussDB, set table type input parameters and exception output parameters, and enable transaction control and exception handling mechanisms.

[0020] The eight operation types are implemented in the stored procedure, which respectively implement the functions of adding application, modifying application, deleting application, production review, production cancellation review, financial review, financial cancellation review, and executing modification operation. Each type of operation adds status judgment and permission verification.

[0021] The dual review and status lock settings implement an interlocking logic for review. Modification and deletion are allowed before review, but after review, operation permissions are locked, retaining only the permissions to cancel review and execute modification. Execution of modification requires both reviews to be passed.

[0022] When cross-system multi-table linkage updates are executed, the corresponding data tables in ERP and MES are automatically matched and updated according to the parameter type. Specification parameters are updated synchronously for production plans, process quality inspection, and inventory data, while monetary parameters are updated synchronously for sales orders, financial settlement, and delivery data.

[0023] The final unit identification and production protection system automatically determines the final production unit based on product name characteristics. When updating MES data, it only applies to records that have not completed the final process, and does not modify data that has completed the final production.

[0024] Operation tracking and data locking: All operations are automatically recorded with the operator, address, and time. After the modification is completed, the execution information is marked and the data is permanently locked, preventing any subsequent changes.

[0025] The entire transaction rollback and exception handling process uses transactions to wrap the business logic. In the event of an exception, the system immediately rolls back to ensure that the data is not lost or corrupted. Error information is captured and output for easy investigation and handling. Implementation effect

[0026] In practical applications, this invention enables the entire contract modification process to be automated, standardized, and secure, with real-time data synchronization between ERP and MES, dual verification of production and finance, full traceability of operations, and stable and reliable system. It significantly improves the enterprise's digital management level and risk control capabilities, while being compatible with dual database environments and possessing good scalability and portability. It can provide a mature reference solution for similar scenarios in the industry.

Claims

1. A unified method for dynamic contract modification based on a unified stored procedure to achieve deep integration of ERP and MES, characterized in that, By integrating eight operation types through a single stored procedure, a fully closed-loop process is constructed, encompassing application, adjustment, dual review, execution modification, and data locking. This process is compatible with both SQL Server and GaussDB databases, enabling cross-system data synchronization and full-process security control. Specific steps include: (1) Define read-only table type parameters to adapt to dual databases and uniformly receive operation type, contract hash value, parameter name, parameter value, operator, operation address, and modification reason information; (2) Implement eight major operations: adding application, modifying application, deleting application, production review, production cancellation review, financial review, financial cancellation review, and executing modification operation, covering the entire scenario of contract modification; (3) Establish a process status locking mechanism. New, modified and deleted applications can only be executed in the unapproved state. Once approved by any department, the operation permission is locked. (4) Set up a dual audit and interlock mechanism for production and finance, and modifications can only be made after both audits have been passed. (5) When making modifications, the sales and financial data tables of the ERP system and the production, process and inventory data tables of the MES system are updated synchronously to achieve cross-system integrated data updates; (6) Automatically identify the final production unit, update only the data of the incomplete final process, and permanently lock the data of the completed production; (7) The entire process automatically records the operator, operation address, operation time, original value and new value, and the data is permanently locked after the modification is executed, so as to achieve full traceability; (8) Adopt transaction control and exception handling mechanisms to ensure data security and system stability.

2. The method according to claim 1, characterized in that, Production audits are used to verify whether the modified content conforms to the production process and production status, while financial audits are used to verify whether data such as amount and quantity meet the company's financial security requirements. Together, they constitute a dual security verification system.

3. The method according to claim 1, characterized in that, The ERP system data tables include sales order details, sales delivery information, and financial settlement related tables. The MES system data tables include production plan, production process quality inspection record, and hot-rolled coil warehouse information. Modification operations achieve full-chain synchronous updates.

4. The method according to claim 1, characterized in that, The final production unit is automatically determined by the product name characteristics, thereby distinguishing between completed and incomplete production data and achieving secure protection of production records.

5. The method according to claim 1, characterized in that, All operation permissions are driven by the process status. Unapproved permissions can be modified or deleted, while approved permissions are locked. Once modifications are executed, the permissions are permanently locked, forming a complete closed loop of permission control.

6. The method according to claim 1, characterized in that, The core business logic is unified, and only lightweight syntax adaptation is performed for SQL Server and GaussDB to achieve seamless operation of a single logic across two databases.

7. The method according to claim 1, characterized in that, It is suitable for industries with continuous production processes such as metallurgy and steel, and provides standardized, integrated and highly secure solutions for handling changes in customer requirements after the contract is issued and production is started.