A method and system for fully automated management of procurement settlement based on transaction and table-value parameters

Abstract

This invention discloses a fully automated management method and system for procurement settlement based on transactions and table-value parameters, relating to the field of industrial digital management. This invention achieves batch contract settlement, batch unit price modification, batch invoicing and reversal through table-value parameters. It employs database transactions to ensure synchronous updates of the main purchase order table, detail table, settlement table, final unit price table, and warehouse physical inventory table. Any failed operation automatically rolls back, avoiding data inconsistency. The system automatically arranges settlement order numbers, implements closed-loop order status flow, binds contract and raw material roll hashes for traceability, differentiates and tracks final unit price entry and modification, automatically rolls back invoicing and reversal status, and batch refreshes global receiving information. It also records the operator, operation time, and operation address throughout the process. This invention is applicable to integrated management of procurement, settlement, unit price, and invoicing in industrial enterprises such as metallurgy and new materials, solving the technical pain points of low efficiency, data chaos, difficulty in traceability, and susceptibility to errors in traditional manual operations. It features high stability, strong traceability, closed-loop business, and suitability for large-scale industrial applications. The technical solution described in this invention has been officially implemented in actual industrial production environments, operating stably and reliably.

Description

Technical Field

[0001] This invention relates to the field of digital management technology for industrial enterprises, specifically to an integrated management method and system for procurement settlement, final unit price management, invoicing and reversal, applicable to the metallurgical and new materials industries, based on batch transmission of table-valued parameters, database transaction control, status linkage updates and full-link data traceability. Background Technology

[0002] The following technical problems are commonly found in the procurement, settlement, and invoicing management of existing industrial enterprises: Procurement settlement, unit price maintenance, and invoicing are mostly handled manually item by item, lacking a batch processing mechanism, resulting in low execution efficiency. Purchase orders, order details, settlement statements, warehouse data, and unit price data are updated in a scattered manner without unified transaction control, which can easily lead to problems such as partial updates, data inconsistencies, and dirty data. Settlement order numbers are mostly manually generated, which are prone to duplication and non-standardization, and lack an automatic mechanism for arranging them according to date rules; The final unit price entry and modification are not recorded separately, making it impossible to achieve complete traceability of entry, modification, time, and personnel. Invoicing and cancellation operations cannot be strongly linked to order status, which can easily lead to erroneous invoicing, erroneous cancellation, and status inconsistency. The lack of a unique identifier linking the contract and the physical raw materials makes it impossible to achieve full-chain traceability of contracts, raw materials, unit prices, settlements, and invoicing. The logic of each functional module is scattered, the code reuse rate is low, maintenance is difficult, and the system stability is poor.

[0003] To address the aforementioned issues, current conventional systems only implement simple data addition, deletion, modification, and query functions, and have not formed a complete, closed-loop, transaction-secure, batch-enabled, and traceable integrated solution. Therefore, there are obvious technical pain points in the industry. Summary of the Invention

[0004] Purpose of the invention This invention overcomes the shortcomings of existing technologies, such as low procurement settlement efficiency, inconsistent data, poor traceability, chaotic status, and lack of transaction security. It provides a procurement settlement full-link automated management method and system based on transaction and table value parameters, realizing batch operation, transaction security, status closure, automatic numbering, unit price traceability, integrated invoicing and reversal, and full-process traceability. Technical solution

[0005] (1) Overall architecture This invention consists of a front-end interface layer, a common function layer, a table-valued parameter transmission layer, a database stored procedure layer, and a multi-table linked data layer. It uniformly adopts table-valued parameters (TVP) for batch data transmission, and all key business operations are executed within transactions to ensure atomicity and consistency.

[0006] (2) Rules for generating automatic settlement order numbers The system automatically generates a settlement order number based on the current date, following the rule of yyyyMMdd + two serial numbers. If there is already a settlement order for the day, the largest order number is used and incremented automatically; otherwise, it starts from 01, ensuring that the order number is unique, standardized, and traceable.

[0007] (3) Batch settlement operation based on table value parameters The front end encapsulates the selected contract number, operator, operation address, remarks, and operation type into a fixed-structure data table, and passes it to the stored procedure all at once through table value parameters to realize batch addition of settlement, batch deletion of settlement, and batch modification of remarks.

[0008] (4) Transactional multi-table synchronous update Database transactions are enabled when adding new settlements, deleting settlements, issuing invoices, reversing transactions, or modifying unit prices. Simultaneously update the purchase order master table, order details table, settlement information table, final unit price table, and warehouse table; If any step fails, the entire process is rolled back to ensure absolute data consistency.

[0009] (5) Standardized closed-loop flow of order status The status is strictly controlled according to the following process: Purchase submission → Settlement completed → Invoicing completed After the invoice was cancelled, the status reverted to: Settlement Completed After deleting the settlement, the status reverts to: Procurement Submission (6) End-to-end traceability based on contract secondary hash value Using the contract's secondary hash value as a unique identifier, the purchase contract, raw material roll number, final unit price, settlement record, and warehouse data are bound together to achieve full traceability from contract to physical roll.

[0010] (7) Intelligent differentiation and traceability of final unit price entry and modification The system automatically identifies: First time entering unit price: Record the person who entered the data, the address entered the data, and the time entered the data; Subsequent modifications to the unit price: retain the original entered information, and additionally record the person who made the modification, the address where the modification was made, and the time of modification; Simultaneously, the total price is automatically calculated as: Total Price = Final Unit Price × Incoming Material Weight, and the data is updated to the warehouse information.

[0011] (8) Integrated transaction control for invoicing and reversal Invoice issuance process: Set the status to "Invoice issuance completed" and record the invoice issuer, time, and address; Reversal operation: Revert the status to "Settlement Completed" and clear the invoice information; The master table and the detail table are updated synchronously, and transactions guarantee consistency.

[0012] (9) One-click batch update of all purchase contract receipt information By passing in the "all" flag, the stored procedure is invoked to refresh the receipt weight, receipt amount, and payment-related data of all contracts in batches, thereby achieving global data consistency.

[0013] (10) Full-process operation record keeping All operations are automatically recorded: The operator, operation address, entry time, modification time, invoicing time, and cancellation time must meet the requirements for auditing and traceability. Implementation

[0014] This implementation provides a fully automated management method for procurement settlement based on transactions and table-valued parameters, applied to an industrial MES system. The system front-end is developed using a Windows client, and the back-end uses an SQL Server database. It uses table-valued parameters to transmit data in batches, database transactions to ensure data consistency, unique hash identifiers to achieve full-chain traceability of contracts and materials, and unified status control for procurement settlement, final unit price, invoicing, and reversal.

[0015] The system pre-configures custom table types as batch transmission parameters, establishes relevant data tables such as purchase orders, settlements, unit prices, warehouses, and invoicing, and uniformly uses stored procedures to process core business processes.

[0016] During procurement settlement operations, the front-end encapsulates the selected contract information, operators, and operation types into a data table, which is then passed to the back-end stored procedure via table-value parameters. The stored procedure automatically generates settlement order numbers arranged by date and serial number within the transaction, and simultaneously updates the main procurement order table, detail table, and settlement information table, bringing the order status to "settlement complete." If an execution exception occurs, the entire process is rolled back to ensure data consistency.

[0017] When maintaining the final unit price, the system associates the corresponding material information with the unique hash identifier of the contract, and supports batch setting of the final unit price. The system distinguishes between the initial entry and subsequent modifications, recording information such as the person who entered the information, the person who modified it, and the time, and automatically recalculates the total price based on the unit price and weight, and synchronously updates the unit price table and the warehouse material table.

[0018] When performing invoicing and reversal operations, the system synchronously updates the status of the order master table and the detail table within the transaction according to the current operation type: when invoicing, it is marked as invoicing completed and the invoicing information is recorded; when reversing, the status is rolled back to settlement completed and the invoicing information is cleared.

[0019] After each settlement, unit price modification, invoicing, and reversal operation is completed, the system automatically refreshes relevant information such as purchase contract receipts and amounts in batches to keep the data synchronized across the entire system.

[0020] The system automatically records the operator, operation address, and operation time throughout the entire business process, forming a complete operation trace, which facilitates subsequent traceability and auditing.

[0021] This implementation method solves the problems of error-proneness, low efficiency, inconsistent data, and difficulty in traceability in traditional procurement and settlement by using batch processing, transaction security, closed-loop status, full-link traceability, and automatic record keeping. It is suitable for integrated management of procurement and settlement in industrial enterprises such as metallurgy and building materials.

Claims

1. A method for fully automated management of the entire procurement settlement chain based on transaction and table-value parameters, characterized in that, Includes the following steps: S1. Construct a front-end batch selection interface, encapsulate the selected contract information, operator, operation address, and operation type into a data table, and pass it to the stored procedure through table value parameters; S2. In the stored procedure, start a database transaction to synchronously update the purchase order master table, purchase order details table, and purchase settlement information table; S3. Automatically generate a unique settlement order number in the format of yyyyMMdd+two sequential serial number based on the current date; S4. Set the closed-loop flow rules for order status: Purchase submission → Settlement completion → Invoicing completion, after cancellation, rollback to settlement completion, after order deletion, rollback to purchase submission; S5. By using the contract's secondary hash value, the purchase contract is uniquely bound to the raw material roll number, final unit price, and warehouse data to achieve full-chain traceability; S6. Record the initial entry and subsequent modifications of the final unit price separately, and automatically calculate the total price. S7. Invoicing and reversal are updated using transaction linkage, which synchronously modifies the status of the main table and the detail table as well as the invoicing information. S8. Batch refresh the receiving information of all purchase contracts by passing in the "All" flag; S9. All operations are automatically recorded, including the operator, the address, and the time, creating a complete workflow trace.

2. The method according to claim 1, characterized in that, In step S2, adding settlement, deleting settlement, and modifying remarks are all executed within the same transaction. If an exception occurs, all operations are rolled back to ensure data atomicity.

3. The method according to claim 1, characterized in that, In step S3, the settlement order number is automatically incremented based on the existing records for the day, and starts from 01 if there are no records, to ensure that the order number is not repeated and the format is consistent.

4. The method according to claim 1, characterized in that, In step S5, the contract's secondary hash value serves as a unique association key, permeating the contract, raw material rolls, settlement sheets, final unit prices, and warehouse data, enabling full traceability with a unique code for each roll.

5. The method according to claim 1, characterized in that, In step S6, when the final unit price is entered for the first time, the person who entered the price, the address where the price was entered, and the time of entry are recorded; when the price is modified again, only the person who modified the price, the address where the price was modified, and the time of modification are updated, and the total price is automatically recalculated.

6. The method according to claim 1, characterized in that, In step S7, the invoicing operation sets the status to invoicing completed and records the invoicing information; the cancellation operation restores the status to settlement completed and clears the invoicing field.

7. The method according to claim 1, characterized in that, All batch operations use table-valued parameter transmission, supporting the processing of multiple contracts and multiple raw material rolls at once, thus improving processing efficiency.

8. The method according to claim 1, characterized in that, In step S8, the global receipt information is refreshed by using the keyword "all", and the receipt weight, receipt amount and payment related data are updated uniformly.

9. A fully automated procurement settlement management system based on transaction and table-value parameters, characterized in that, include: Front-end batch operation module, table value parameter transmission module, transaction control stored procedure module, status closed-loop management module, hash traceability module, unit price intelligent traceability module, invoice cancellation module, global refresh module, operation traceability module; The system is used to perform the method according to any one of claims 1 to 8.