Unified identification-based cross-library ERP-MES fusion single product cost precise accounting method

Abstract

The application discloses a kind of based on unified identification's cross-library ERP-MES fusion single product cost accurate accounting method, it is related to industrial informatization, cross-database data fusion, production financial integration and fine cost accounting technical field.The application is directly cross-library joint calculation by constructing the global unified unique identification system throughout raw material, procurement, production, sales whole process under the premise of without data synchronization, intermediate library, ETL tool and interface service, realizes MES production library, raw material management library, ERP procurement library, ERP sales library;Adopt lightweight temporary table and extremely simple step-by-step batch update mechanism, based on finished product output proportion realizes single product level raw material cost automatic accurate allocation, completes raw material cost, sales income, additional cost whole process automation accounting and whole link traceability.The application solves traditional multi-system data island, cross-library development complex, data synchronization delay, cost accounting extensive, code redundancy, maintenance cost is high, poor generalization and other technical pain points from architecture root, has no invasion access, real-time calculation, accounting precision, high performance, easy maintenance, cross-industry generalization and other outstanding advantages, can be widely applied to the ERP and MES depth fusion and fine cost control scene of various manufacturing enterprises.

Description

Technical Field

[0001] This invention relates to industrial manufacturing informatization, multi-system data fusion, database architecture optimization, enterprise resource planning (ERP), manufacturing execution system (MES), and refined financial cost accounting. In particular, it relates to a single-item cost accounting method based on a globally unified identifier that can achieve direct multi-database calculation without data synchronization. Background Technology

[0002] Currently, manufacturing enterprises generally adopt a separate deployment model for ERP and MES systems and databases, resulting in severe data silos caused by the isolation of production data, raw material inventory data, procurement data, and sales data. Enterprises face the following difficult-to-solve technical problems when implementing integrated production and finance and cost accounting: Cross-system and cross-database data interaction relies on database links, data synchronization, intermediate libraries, scheduled tasks, interface services, etc., which involves a large amount of development work, complex architecture, poor stability, and data is prone to delays, loss and inconsistency.

[0003] Each system has its own independently designed coding rules, lacking a globally unified and unique identifier that runs through the entire process of raw material input, production and processing, procurement contracts, and sales orders. Data association relies on complex mapping relationships, which is prone to errors and difficult to trace.

[0004] Traditional cost accounting often involves fuzzy calculations based on batches, which cannot achieve accurate accounting at the single-item level. The raw material cost allocation method is crude, lacks scientific basis, and is prone to cost distortion.

[0005] The cost accounting logic is complex, with lengthy code, deep nesting, and extensive use of complex join queries, resulting in low operating efficiency, easy table locking, and difficulties in maintenance and expansion.

[0006] Existing cost accounting solutions are often tightly bound to specific industries and processes, lacking versatility and making it difficult to quickly replicate and promote them across multiple industries and scenarios.

[0007] Currently, there is a lack of integrated production and financial cost accounting methods on the market that are simple in architecture, non-intrusive in deployment, allow direct cross-database calculations, have unified global identification, extremely simple code, accurate accounting, and are applicable to all industries. Summary of the Invention

[0008] Purpose of the invention To address the shortcomings of existing technologies, this invention provides a method for accurate cost accounting of single items in a cross-database ERP-MES system based on a unified identifier. This method enables real-time data fusion and cost calculation with direct connection to multiple databases, zero synchronization, zero interfaces, and zero intermediate databases. It improves accounting accuracy through a scientific single-item-level cost allocation algorithm, eliminates data silos at the architectural level, reduces development and maintenance costs, and improves system operating efficiency and data consistency. Technical solution

[0009] A method for accurate single-item cost accounting across ERP-MES systems based on unified identifiers includes the following steps: Establish a globally unified and unique identifier system across databases Construct a unified and unique identifier that runs through the entire business process, including a unique identifier for finished products, a unique identifier for raw materials, a unique identifier for purchase contracts, a second-level hash identifier for purchase contracts, a unique identifier for sales contracts, and a unique identifier for circulation batches. The above identifiers are kept consistent in the MES production database, raw material management database, ERP purchase database, and ERP sales database, and can be directly associated across databases without the need for conversion, mapping, or configuration of corresponding relationships.

[0010] Cross-database direct connection data collection and basic data fusion Based on a globally unified and unique identifier, it can directly perform cross-database joint queries on MES production data tables, raw material inventory data tables, ERP purchase unit price data tables, and ERP sales order data tables without relying on database links, data synchronization tools, intermediate tables, or scheduled tasks, thereby achieving real-time aggregation and integration of production, raw material, procurement, and sales data.

[0011] Lightweight temporary table construction Lightweight temporary data tables are created as data computing carriers to temporarily store basic data collected across databases, intermediate data on cost allocation, and financial accounting results. This reduces redundant scanning of large tables, improves computing efficiency, and avoids the risk of table locking.

[0012] Multi-source data integrated and written at one time By using a single join query, production data, sales data, and parameter filtering data can be matched and written in batches in one go, efficiently aggregating basic data across databases into a temporary table, simplifying code logic and improving data processing efficiency.

[0013] Update and improve data information in stages and batches. The system adopts a step-by-step, orderly batch update method to sequentially supplement raw material information and purchase price information; each update is based on a unique identifier, is logically independent and does not interfere with each other, and is easy to maintain and expand.

[0014] Precise allocation of raw material costs per item based on output share The total output is statistically analyzed by grouping according to the unique identifier of the raw materials. Based on the actual output ratio of each product, the raw material usage and raw material cost to be allocated to each product are automatically calculated. This enables accurate cost allocation in scenarios where the same raw material is processed into multiple finished products, making cost accounting scientific, traceable, and dispute-free.

[0015] Financial indicators are calculated automatically. Based on the established basic data and allocation results, the system automatically calculates key financial indicators such as total raw material costs, total sales revenue of finished products, and additional expenses, without any manual intervention throughout the process.

[0016] Multi-dimensional output and end-to-end data traceability The system automatically sorts and outputs accounting results according to process, production time, and serial number; it supports reverse lookup of original data from various systems through finished product identifiers, raw material identifiers, and contract identifiers, enabling full-process traceability from finished products to raw materials, procurement, and sales.

[0017] Automatic cleanup of temporary data After the calculation is completed, the temporary data table is automatically cleaned up to release database resources and ensure that the system runs efficiently and stably.

[0018] Cross-ERP-MES multi-database direct connection computing, zero synchronization, zero interfaces, zero intermediate libraries, solving the data silo problem from the root of the architecture.

[0019] A globally unified and unique identifier is used throughout the entire process, allowing for direct cross-database association without the need for mapping, conversion, or adaptation.

[0020] With a minimalist architecture and minimalist code, the complete accounting logic can be completed with a few statements, without complex nesting or redundant calculations.

[0021] The single-item-level raw material cost precise allocation algorithm is scientific, reasonable, quantifiable, and verifiable, completely solving the problem of the traditional extensive allocation method.

[0022] Non-intrusive deployment, no modification to the original system, no impact on existing business, fast access, and extremely low implementation cost.

[0023] It is applicable to all industries, without relying on specific processes, equipment, or industry scenarios, and is suitable for all categories of manufacturing fields. Beneficial effects

[0024] The development cost of cross-database data fusion is reduced by more than 90%, and the architecture is simple and stable.

[0025] Data is consistent in real time, with no delay, no loss, and no duplication.

[0026] Cost accounting is accurate down to the individual item level, and the allocation is scientific and reliable, which can effectively support business decisions.

[0027] The code is extremely simple, runs efficiently, does not lock tables, and has an extremely low failure rate.

[0028] Maintenance costs are close to zero, and expansion is convenient and quick in the future.

[0029] It is applicable to the entire manufacturing industry and can be quickly replicated and promoted on a large scale.

[0030] Achieve deep integration of production and financial data, bridging the last mile between business and finance. Detailed Implementation

[0031] Establish and unify globally unique identification rules to ensure that raw material, production, procurement, and sales data remain consistent and interconnected across all databases.

[0032] Filtering conditions such as start date, end date, process or unit name are passed in through table-valued parameters.

[0033] Create a lightweight temporary table to hold all the fields required for the calculation.

[0034] By performing a cross-database join query, MES production data, filtering parameters, and ERP sales data are written to a temporary table all at once.

[0035] Update the raw material weight and the secondary hash value of the purchase contract based on the raw material inventory data associated with the circulation batch identifier.

[0036] Based on the secondary hash value of the procurement contract, the unit price data of the procurement contract is associated with the final unit price of the raw materials, and the final unit price of the raw materials is backfilled.

[0037] The total output weight is calculated by grouping raw materials according to their unique identifiers, and the weight and cost of raw materials for each item are automatically allocated based on the output percentage.

[0038] It automatically calculates financial indicators such as the total price of raw materials, the total price of finished products, and freight costs.

[0039] The final accounting results are output in sorted order by process number, process name, and production time, and full-chain traceability query is supported.

[0040] After the calculation is completed, the temporary table will be automatically deleted to release database resources.

Claims

1. A method for accurate single-item cost accounting across ERP-MES systems based on unified identifiers, characterized in that: include: (1) Establish a unified and unique identification system that spans across warehouses, covering raw materials, procurement, production, and sales; (2) Based on a unified identifier, achieve direct connection and joint calculation of MES production warehouse, raw material management warehouse, ERP procurement warehouse and ERP sales warehouse; (3) Lightweight temporary tables and simplified step-by-step batch updates are used to complete the fusion of multi-source data; (4) Accurate allocation of raw material costs per grade based on the proportion of finished product output; (5) Automatically calculate financial indicators throughout the entire process and output traceable multi-dimensional accounting results.

2. The method according to claim 1, characterized in that, Cross-database computation eliminates the need for data synchronization, intermediate libraries, ETL tools, interface services, or database links.

3. The method according to claim 1, characterized in that, The globally unified and unique identifiers include the unique identifiers for finished products, raw materials, purchase contract hashes, sales contracts, and circulation batches, which can be directly linked between multiple systems and databases.

4. The method according to claim 1, characterized in that, Cost allocation is automatically calculated based on the actual output ratio of each individual product, enabling precise cost accounting at the individual product level.

5. The method according to claim 1, characterized in that, The overall logic is extremely simple, with no complex nesting, high performance, easy maintenance, non-intrusive deployment, and universal applicability across the entire manufacturing industry.