Five-level linkage penetration query method and system for sales execution data in an industrial MES

Abstract

The application discloses a five-level linkage penetration query method and system for sales execution data in an industrial MES, and relates to the technical field of industrial manufacturing execution system (MES) data query and analysis. The application builds a five-level linkage penetration architecture from macroscopic aggregation to microscopic physical material details, designs an automatic transmission mechanism for upper-level screening parameters, constructs a unified data accounting model with temporary tables as the pivot, realizes unique association of full-link data through contract hash values, and introduces database transaction control to ensure stable operation. The application solves the problems of scattered statistics, drilling difficulty, inefficient traceability and different caliber of traditional industrial enterprise sales execution data, realizes cross-module and multi-level full-link linkage penetration query, improves data traceability efficiency by more than 90%, unifies the accounting caliber of multiple modules, provides precise data support for fine management and efficient decision-making of enterprises, and is suitable for the sales execution data management scene of industrial enterprise ERP / MES systems. The application has been completely applied in actual industrial ERP systems, can run stably in real production environment for a long time, and has mature engineering value and industrialization popularization ability.

Description

Technical Field

[0001] This invention relates to data query and analysis technology for Industrial Manufacturing Execution Systems (MES), specifically to a multi-level linkage and drill-down data query solution for the entire process of industrial material sales, production, inventory, and financial settlement. It is applicable to the full-link traceability and multi-dimensional statistical analysis of sales execution data in industrial enterprise ERP / MES systems. Background Technology

[0002] Traditional industrial enterprises often store sales execution data across multiple independent system modules, such as order management, production execution, inventory control, and financial settlement. Data aggregation for each module relies on manual summarization of separate reports. This model suffers from several industry pain points: fragmented data hierarchies prevent drill-down from macro-level operational data to micro-level physical inventory details; cross-module data verification relies on manual table-by-table matching, resulting in extremely low efficiency and susceptibility to data discrepancies; and difficulties in quickly tracing the entire data chain and pinpointing the root cause of issues like order execution anomalies or financial reconciliation discrepancies hinder the effective linkage of sales, production, and financial data across modules, failing to provide management with accurate and comprehensive decision-making support. Current technology lacks a comprehensive query solution capable of enabling cross-module, multi-level, and end-to-end linkage and penetration of sales execution data, failing to meet the needs of industrial enterprises for refined management and efficient decision-making. Summary of the Invention

[0003] Purpose of the invention This invention aims to overcome the shortcomings of existing technologies and provide a five-level linkage and penetration query method and system for sales execution data in industrial MES. It realizes full-link layer-by-layer drill-down and linkage query from macro summary data to micro physical material details, solves the problems of scattered data statistics, difficult drill-down, inefficient traceability, and inconsistent standards in traditional data statistics, improves the query efficiency and statistical accuracy of sales execution data, and provides data support for enterprise business decision-making and refined management. Technical solution

[0004] A five-level interconnected data drilling architecture is established: This system creates a macro-to-micro data drilling framework to achieve layer-by-layer detailed traceability of sales execution data. Level 1: Sales execution data is summarized and statistically analyzed by product category and production / sales month / year, displaying core operational data across the entire plant. Level 2: Based on the Level 1 summary, detailed breakdowns are performed by customer dimension, displaying the order execution status of each customer. Level 3: Based on the Level 2 customer details, precise traceability of single contract execution is performed by contract number, displaying the entire contract execution process data. Level 4: Based on Level 3 contract traceability, fine-grained data calculations are performed by core material dimensions such as contract hash value, product specifications, material, and process parameters, displaying the execution details of different specifications of materials under the same contract. Level 5: Through front-end control interaction events, users can directly drill down to query the physical material details under the corresponding dimension, displaying specific information for each material.

[0005] Multi-level parameter automatic transfer mechanism: The function of automatically passing the filter conditions of the upper-level report to the lower level means that the lower-level query interface does not need to repeatedly enter the filter parameters, but directly inherits the query conditions of the upper level, realizing the efficient query mode of "point-and-drill, one-click exploration", which greatly improves the operation efficiency of cross-level data drilling.

[0006] Unified data accounting model construction: Using temporary tables in the database as the data processing hub, it integrates raw data from multiple modules such as orders, production, inventory, and finance, and uniformly completes the fully automatic calculation of core indicators such as order volume, material input, warehousing volume, shipment volume, inventory balance, settlement quantity, unsettled amount, and unsettled quantity, ensuring that the data accounting standards of each level and module are consistent.

[0007] Contract hash value full-link association: The contract hash value is used as the unique association identifier for sales execution data, and order information, raw material binding information, production and processing data, finished product warehousing data, delivery and logistics data, and financial settlement data are accurately bound together to achieve unique association of data in each module and avoid problems such as mixed calculation of multiple specifications of materials under the same contract and data cross-entry.

[0008] Transaction security and stable execution mechanism: Database transaction control technology is used throughout the process. All data query and accounting operations are included in transaction management. If any abnormality occurs in any link, the system will automatically execute transaction rollback to ensure the consistency of data query process and the stability of report display, meeting the high reliability operation requirements of industrial production site.

[0009] It pioneered a five-level linkage and penetration architecture for sales execution data, enabling full-link, layer-by-layer drilling down from macro-level summaries of the entire plant to physical details of individual materials, filling the technological gap in the industrial production field where sales execution data lacked a systematic penetration query.

[0010] The design incorporates a mechanism that automatically passes filtering parameters from higher levels to lower levels, enabling highly efficient "click-to-drill" operations and completely solving the tedious problem of repeatedly entering parameters for traditional cross-level queries.

[0011] By using temporary tables as a hub, a unified data accounting model is built to achieve integrated automatic accounting of data from multiple modules, ensuring consistency in statistical standards across all dimensions and eliminating data deviations.

[0012] By using the contract hash value as a unique identifier across the entire chain, precise binding of order, production, inventory, and financial data is achieved, avoiding mixed calculations and cross-accounting for materials of different specifications under the same contract, and ensuring the accuracy of data association.

[0013] A database transaction management mechanism is introduced to ensure the stability of the query process and data consistency, adapting to the high reliability and stability requirements of industrial production sites. Beneficial effects

[0014] It enables five-level full-link penetration query of industrial material sales execution data, from macro-level summary of the entire plant to detailed physical material list, completely solving the industry pain points of traditional data hierarchy fragmentation and drilling difficulties, and improving data traceability efficiency by more than 90%.

[0015] It unifies the data accounting standards of multiple modules, realizes integrated automatic statistics of order, production, inventory and financial data, reduces manual intervention, eliminates data errors caused by manual aggregation, and improves the efficiency of enterprise operation and financial reconciliation.

[0016] The multi-level parameter automatic transmission mechanism greatly simplifies the operation process, reduces the workload of operators, enables rapid query and accurate positioning of sales execution data, and provides real-time and accurate data support for enterprise management decision-making.

[0017] The architecture is unified and logically clear, making it easy to maintain, expand, and migrate to a domestic database platform, thus meeting the long-term needs of industrial enterprises for digital upgrading.

[0018] The contract hash value end-to-end association enables precise binding of data across modules, avoiding data mixing and cross-referencing, ensuring the integrity and accuracy of sales execution data, and meeting the requirements of refined enterprise management. Detailed Implementation

[0019] System initial configuration: Build a five-level linkage and penetration query module for sales execution data in the industrial MES system, configure the relationship of five-level data drill-down, and define the statistical indicators and display fields for each level; establish a unified data accounting temporary table to integrate the core data fields of modules such as orders, production, inventory, and finance; use the contract hash value as the core association field to complete the association mapping of data from each module.

[0020] Five-level data drill-down configuration: Level 1 configuration summarizes core data such as sales order volume, production completion volume, shipment volume, and inventory balance for all product categories across the factory, based on product category and year / month dimensions; Level 2 breaks down the data by customer name, displaying order execution details for each customer across different product categories; Level 3 traces the data by contract number, displaying order signing information, production progress, shipment status, and settlement status for each contract; Level 4 further breaks down the data by material dimensions such as contract hash value, product specifications, material, and process parameters, displaying specific execution data for different specifications of materials under the same contract; Level 5 configuration links the data to the physical material details table, enabling drill-down queries of detailed data such as material number, weight, warehousing time, and shipment time.

[0021] Parameter passing mechanism settings: Set up an automatic parameter passing function in the system front-end query interface. When the operator selects a product category + year and month summary data in the first-level interface, the system automatically passes the product category and year and month as filter parameters to the second-level customer details interface; subsequent selection operations at each level will automatically pass the upper-level filter parameters to the next level, realizing one-click drill-down without duplicate entry.

[0022] Unified data accounting execution: The system periodically synchronizes the raw data from the order management, production execution, inventory control, and financial settlement modules to a temporary table. Through preset accounting logic, it automatically calculates core indicators such as order volume, material input, warehousing volume, shipment volume, and settlement quantity at each level, ensuring that the data at each level is updated in real time and consistent in terms of caliber.

[0023] Transaction control and query execution: After the operator initiates a sales execution data query request at the system front end, the system automatically starts a database transaction and retrieves data layer by layer according to the five-level penetration architecture. If network abnormalities or data reading errors occur during the query process, the system immediately executes transaction rollback to restore the state before the query. After the query is completed normally, the transaction is committed and the data at each level is displayed on the front end interface. The operator can drill down layer by layer by clicking to reach the details of physical materials.

[0024] Data Display and Traceability: The system front end displays sales execution data in modules according to a five-level architecture, and data at each level supports export and printing functions; when problems such as order execution anomalies or financial reconciliation discrepancies occur, operators can use the five-level penetration query to quickly locate specific customers, specific contracts, specific specifications of materials, or even specific physical materials from macro data, realizing full-chain data traceability and problem root cause location.

Claims

1. A five-level linked penetration query method for sales execution data in an industrial MES, characterized in that, include: (1) Build a five-level data drill-down system from macro to micro. The first level is the summary of the core operating data of the whole plant, including product category and production / sales month and year. The second level is the order execution details at the customer level. The third level is the single contract execution traceability at the contract number level. The fourth level is the fine-grained execution details at the contract hash value and material core level. The fifth level is the penetrating query of physical material details. (2) Set up an automatic passing mechanism for the filtering conditions of the upper-level report to the lower-level report. The lower-level query interface directly inherits the filtering parameters of the upper-level report, realizing one-click drill-down without duplicate input. (3) Using temporary tables in the database as the data processing hub, integrate the original data from multiple modules such as orders, production, inventory, and finance, unify the calculation of core indicators, and ensure that the data standards of each level and module are consistent; (4) The contract hash value is used as a unique association identifier to accurately bind the data of the entire process of orders, production, inventory and finance, so as to avoid the mixing of multiple specifications of materials under the same contract and data cross-accounting. (5) All data query and accounting operations are included in database transaction management. In case of an anomaly, transaction rollback is automatically performed to ensure data consistency and report display stability.

2. The method according to claim 1, characterized in that, The five-level data drill-down system enables detailed traceability from macro-level aggregated data across the entire plant to individual physical material details, with each level of data supporting export and printing functions.

3. The method according to claim 1, characterized in that, The core dimensions of the material include product specifications, material, and process parameters, which can be flexibly expanded according to the characteristics of different types of materials in industrial production.

4. The method according to claim 1, characterized in that, The core indicators automatically calculated by the unified data accounting model include order volume, material input volume, warehousing volume, shipment volume, inventory balance, settled quantity, unsettled amount, and unsettled quantity.

5. A five-level linkage penetration query system for sales execution data in an industrial MES that implements the method of any one of claims 1-4, characterized in that, It includes a five-level penetration architecture module, an automatic parameter transfer module, a unified accounting module, a hash value association module, and a transaction management module. These modules work together to achieve full-link drill-down, accurate accounting, and stable querying of sales execution data. All database operations use standard SQL basic syntax and are compatible with SQL Server and GaussDB platforms.