Data chain construction method and system for supporting product material cost accounting based on project number

Abstract

This invention belongs to the fields of Enterprise Resource Planning (ERP) systems and cost accounting, specifically involving a data chain construction method and system for supporting product material cost accounting based on project numbers. The method includes steps S1: generating product project numbers to be delivered based on orders; S2: constructing an order-production plan relationship model; S3: constructing a production plan-purchase order relationship model; S4: constructing a purchase order-inventory relationship model; S5: constructing a production plan-material requisition form relationship model; S6: constructing an order-delivery note relationship model; and S7: constructing an order-settlement note relationship model. This invention connects key data breakpoints to support full-process material cost accounting, accurately tracking each direct material cost quota and actual cost expenditure, ultimately achieving refined cost control and improving decision-making quality.

Description

Technical Field

[0001] This invention belongs to the fields of Enterprise Resource Planning (ERP) systems and cost accounting, specifically relating to a data chain construction method and system for supporting product material cost accounting based on project number. Background Technology

[0002] As sales prices, material consumption, and market pricing become more standardized among manufacturing enterprises, cost accounting has become an important way for enterprises to control costs and improve efficiency.

[0003] Generally, the direct material cost accounting of a product relies on the integrity and correlation of the data chain across multiple modules of an Enterprise Resource Planning (ERP) system, including sales orders, production plans, procurement management, inventory management, and cost management. However, in practical applications, as the scope of business operations expands with enterprise development, ERP systems often suffer from relatively independent business modules, inconsistent business data standards, and data breakpoints in the upstream and downstream of the supply chain. This makes it impossible for the entire ERP system to provide accurate and consistent business data to modules such as procurement management, inventory management, and cost management based on orders, delivery plans, and production plans, leading to difficulties in calculating the standard planned cost, procurement cost, and actual cost of product materials. To solve this technical problem, this invention provides an ERP data chain construction method based on project numbers, which can achieve the connection of data breakpoints at low cost and high efficiency, providing technical support for product material cost accounting. Summary of the Invention

[0004] The technical problem solved by this invention is that existing ERP systems generally suffer from relatively independent business modules, differences in business data standards, and data breakpoints in the upstream and downstream of the supply chain. This makes it impossible for the entire ERP system to provide accurate and consistent business data to modules such as procurement management, inventory management, and cost management based on orders, delivery plans, and production plans, resulting in difficulties in calculating the quota planned cost, procurement cost, and actual cost of product materials.

[0005] The purpose of this invention is to propose a data chain construction method and system based on ERP system to support product material cost accounting based on project number, connect key data breakpoints, support the implementation of full-process material cost accounting, accurately track each direct material cost quota and actual cost expenditure, and ultimately achieve refined cost control and improve decision-making quality.

[0006] The technical solution of this invention:

[0007] A method for constructing a data chain based on project number to support product material cost accounting includes the following steps:

[0008] Step S1: Generate the product item number to be delivered based on the order;

[0009] Step S2: Construct an order-production plan relationship model;

[0010] Step S3: Construct the production plan-purchase order relationship model;

[0011] Step S4: Construct the purchase order-inventory relationship model;

[0012] Step S5: Construct the production plan-material requisition relationship model;

[0013] Step S6: Construct the order-delivery note relationship schema;

[0014] Step S7: Construct the order-settlement statement relationship pattern.

[0015] Furthermore, in step S1, the product item number is determined by a certain type of product to be delivered under a specific order. Based on this step, the product item number to be delivered is generated, and the product to be delivered in the order is associated with and bound to the product item number to establish an order-product basic relationship model. Based on this relationship model, steps S2-S7 are carried out.

[0016] Further, in step S2, an order-production plan relationship model is constructed: based on the order number, order name, order time, delivery time, and total order price, a production plan is created, determining the production plan number, plan name, plan time, and delivery time. The project numbers of the products to be delivered in the order are then associated and bound, generating the order-production plan relationship model. Based on the order-product basic relationship model and the order-production plan relationship model, a quota planned cost-relational algebra model R1 is constructed, where:

[0017]

[0018] in, This indicates an order-production plan connection operation.

[0019] This indicates a product-production planning connection operation. This represents a projection operation, where π is the concatenation operator, and π is the projection operator. The standard planned cost of product materials is calculated using the standard planned cost-relational algebra model R1.

[0020] Furthermore, in step S3, a production plan-purchase order relationship model is constructed: based on the production plan number, plan name, plan time and delivery time in the production plan, a purchase order is created, and the purchase order number, purchase name, purchase time, expected arrival time and purchase materials are determined. The project number of the product to be delivered in the production plan is associated and bound, and the production plan-purchase order relationship model is generated.

[0021] Furthermore, in step S4, a purchase order-inventory relationship model is constructed: based on the purchase order number, purchase name, purchase time, expected arrival time and purchased materials in the purchase order, inventory is created when the goods actually arrive, the material number, material name, warehousing time, arrival time and warehouse are determined, and the item number of the product to be delivered in the purchase list is associated and bound to generate the purchase order-inventory relationship model.

[0022] Furthermore, based on the production plan-purchase order relationship model, the purchase order-inventory relationship model, and the quota plan cost-relational algebra model R1, a purchase cost-relational algebra model R2 is constructed, where:

[0023]

[0024] This indicates a purchase order-inventory connection operation. This indicates a product-inventory join operation, which calculates the product procurement cost using the procurement cost-relational algebra model R2.

[0025] Further, in step S5, a production plan-material requisition relationship model is constructed: based on the production plan number, plan name, plan time, and delivery time of the production plan, a material requisition form is created when materials are actually requisitioned in production, determining the material requisition number, material requisition name, requisition time, affiliated plan, and affiliated warehouse, thus generating the production plan-material requisition relationship model; based on the quota planned cost-relational algebra model R1 and the production plan-material requisition relationship model, an actual cost-relational algebra model R3 is constructed, whereby...

[0026]

[0027] SUM(unit price * quantity issued) represents the sum of the products of the quantity of materials issued and the corresponding unit price. This indicates the connection operation between the production plan and the material requisition form.

[0028] This indicates a product-material requisition link operation, which calculates the actual cost of the product using the actual cost-relational algebra model R3.

[0029] Furthermore, in step S6, an order-delivery note relationship model is constructed: based on the products to be delivered in the order, a delivery note is created when the goods are actually delivered, and the delivery number, delivery time, order to which it belongs, and delivered products are determined to generate the order-delivery note relationship model.

[0030] Furthermore, in step S7, an order-settlement document relationship model is constructed: based on the products to be delivered in the order, a settlement document is created after the delivery note is created, and the settlement number, settlement time, order to which it belongs, and total settlement price are determined to generate the order-settlement document relationship model.

[0031] Furthermore, based on the constructed order-delivery note relationship pattern and order-settlement note relationship pattern, a revenue-relational algebraic model S is constructed, where:

[0032] This represents the connection operation of order-delivery note-settlement note-purchase order, and calculates the actual revenue of the product through the revenue-relational algebra model S.

[0033] A data chain construction system based on project number to support product material cost accounting, applied to the method described, includes:

[0034] Project Number Generation Unit: Configured to generate project numbers for products to be delivered based on orders;

[0035] Module A: Configured to build the order-production plan relationship pattern;

[0036] Module B: Configured to build the production plan-purchase order relationship schema;

[0037] Module C: Configured to build the purchase order-inventory relationship pattern;

[0038] Module D: Configured to build the production plan-material requisition relationship schema;

[0039] Module E: Configured to build the order-delivery note relationship schema;

[0040] Module F: is configured to build the order-settlement relationship pattern.

[0041] Beneficial effects

[0042] (1) A standardized UML entity-relationship model was established for the entire supply chain process from order to delivery, and the upstream and downstream business data entities of the supplier were logically linked.

[0043] (2) The proposed relational data chain construction method based on project number is not constrained by the type of database management system and can be independently implemented in any database product.

[0044] (3) The established relational algebra model is not sensitive to structured data query language and can be encoded and applied at the physical layer of any database product.

[0045] (4) It realizes the connection of data breakpoints of various business modules in the ERP system, and quickly realizes the reconstruction of historical business data and the connection of new business data according to the project number attribute, reducing the manual data association operation across business modules.

[0046] (5) The established relational algebra model can clearly track the quota, procurement and actual cost expenditure of product materials, confirm the actual income, avoid unnecessary waste and overspending, and achieve precise cost control. Attached Figure Description

[0047] Figure 1 It is a data chain construction process based on project number to support product material cost accounting;

[0048] Figure 2 It is a standardized UML entity-relationship model diagram from order to delivery;

[0049] Figure 3 It is a relational data chain diagram based on project number;

[0050] Figure 4 This is a schematic diagram illustrating an implementation example of a data chain construction method that supports material cost accounting for aviation products based on project numbers. Detailed Implementation

[0051] The present invention will now be described in detail with reference to specific embodiments. These embodiments will help those skilled in the art to further understand the present invention, but do not limit the invention in any way. It should be noted that those skilled in the art can make several changes and improvements without departing from the concept of the present invention. These all fall within the protection scope of the present invention.

[0052] Example 1: This invention proposes a data chain construction method for supporting product material cost accounting based on project number, including the following steps:

[0053] Step S1: Generate the product item number to be delivered based on the order;

[0054] Step S2: Construct an order-production plan relationship model;

[0055] Step S3: Construct the production plan-purchase order relationship model;

[0056] Step S4: Construct the purchase order-inventory relationship model;

[0057] Step S5: Construct the production plan-material requisition relationship model;

[0058] Step S6: Construct the order-delivery note relationship schema;

[0059] Step S7: Construct the order-settlement statement relationship pattern.

[0060] In one embodiment of the present invention, in step S1, the product item number is determined by a certain type of product to be delivered under a specific order. The product item number to be delivered is generated according to this step, and the product to be delivered in the order is associated with and bound to the product item number to establish an order-product basic relationship pattern. Based on this relationship pattern, steps S2-S7 are carried out.

[0061] In one embodiment of the present invention, in step S2, an order-production plan relationship model is constructed: based on the order number, order name, order time, delivery time, and total order price, a production plan is created, the production plan number, plan name, plan time, and delivery time are determined, and the item number of the product to be delivered in the order is associated and bound, thereby generating an order-production plan relationship model. Based on the order-product basic relationship model and the order-production plan relationship model, a quota planned cost-relational algebra model R1 is constructed, wherein:

[0062]

[0063] in, This indicates an order-production plan connection operation.

[0064] This indicates a product-production planning connection operation. This represents a projection operation, where π is the concatenation operator, and π is the projection operator. The standard planned cost of product materials is calculated using the standard planned cost-relational algebra model R1.

[0065] In one embodiment of the present invention, in step S3, a production plan-purchase order relationship model is constructed: a purchase order is created based on the production plan number, plan name, plan time and delivery time in the production plan, the purchase order number, purchase name, purchase time, expected arrival time and purchase materials are determined, and the project number of the product to be delivered in the production plan is associated and bound to generate the production plan-purchase order relationship model.

[0066] In one embodiment of the present invention, in step S4, a purchase order-inventory relationship model is constructed: based on the purchase order number, purchase name, purchase time, expected arrival time and purchased materials in the purchase order, inventory is created when the goods actually arrive, the material number, material name, warehousing time, arrival time and warehouse are determined, and the item number of the product to be delivered in the purchase list is associated and bound to generate the purchase order-inventory relationship model.

[0067] In one embodiment of the present invention, a procurement cost-relationship algebraic model R2 is constructed based on the production plan-purchase order relationship model, the purchase order-inventory relationship model, and the quota plan cost-relationship algebraic model R1, wherein:

[0068]

[0069] This indicates a purchase order-inventory connection operation. This indicates a product-inventory join operation, which calculates the product procurement cost using the procurement cost-relational algebra model R2.

[0070] In one embodiment of the present invention, step S5 involves constructing a production plan-material requisition form relationship model: based on the production plan number, plan name, plan time, and delivery time of the production plan, a material requisition form is created during actual production requisition, determining the material requisition number, material requisition name, requisition time, associated plan, and associated warehouse, thus generating the production plan-material requisition form relationship model; based on the quota plan cost-relational algebra model R1 and the production plan-material requisition form relationship model, an actual cost-relational algebra model R3 is constructed, wherein...

[0071]

[0072] SUM(unit price * quantity issued) represents the sum of the products of the quantity of materials issued and the corresponding unit price. This indicates the connection operation between the production plan and the material requisition form. This indicates a product-material requisition link operation, which calculates the actual cost of the product using the actual cost-relational algebra model R3.

[0073] In one embodiment of the present invention, in step S6, an order-delivery note relationship pattern is constructed: based on the products to be delivered in the order, a delivery note is created when the goods are actually delivered, and the delivery number, delivery time, order to which it belongs and the delivered products are determined, thereby generating the order-delivery note relationship pattern.

[0074] In one embodiment of the present invention, in step S7, an order-settlement order relationship pattern is constructed: based on the products to be delivered in the order, a settlement order is created after the delivery note is created, and the settlement number, settlement time, order to which it belongs, and total settlement price are determined to generate the order-settlement order relationship pattern.

[0075] In one embodiment of the present invention, a revenue-relational algebraic model S is constructed based on the construction of an order-delivery note relationship pattern and an order-settlement note relationship pattern, wherein:

[0076]

[0077] This represents the connection operation of order-delivery note-settlement note-purchase order, and calculates the actual revenue of the product through the revenue-relational algebra model S.

[0078] Example 2:

[0079] This embodiment is implemented based on the technical solution of the present invention. Because the number of data entities involved in relational algebra operations is too large to list entirely, this embodiment only demonstrates a simplified process of aviation products from order to delivery. Figure 4 As shown in the figure. This embodiment provides a detailed process for relational algebra querying the actual material cost accounting of an enterprise's aviation products. However, the scenarios and scope of protection for product material cost accounting that this invention can handle are not limited to the following implementation case. This implementation case omits the design and modification process of the standardized UML entity-relationship model and the relational data chain based on the project number, as well as the initial state before generating the project number, and declassifies the business data of the instance objects in advance. However, this invention is applicable to... Figure 1 This shows all the content in the entire data chain construction process.

[0080] This embodiment uses the implementation background of material cost accounting for aviation products as an example to demonstrate the construction process of the ER model and the connection process of data breakpoints. Objects #0 to #1.1 correspond to E0: Product and E1: Order in the standardized UML entity-relationship model, respectively; object #2 corresponds to E2: Production Plan; objects #3, #3.1, and #3.2 correspond to E3: Purchase Order; objects #4.1 and #4.2 correspond to E4: Inventory; objects #5.1 and #5.2 correspond to E5: Material Requisition Form; object #6 corresponds to E6: Delivery Order; and object #7 corresponds to E7: Settlement Form.

[0081] Based on the ER model to which this business belongs, the database physical layer table structure, attributes, and fields were modified and populated, and the relational schema was constructed. The specific implementation process is as follows:

[0082] (1) Step S1: Generate the P2D project number for aviation products, establish the order-product basic relationship model, and add the P2D project number attribute as a foreign key in the #1.1 production letter / revenue contract list record.

[0083] (2) Step S2: Based on the production approval number / revenue contract internal number, planned completion time, and provisional total order price information in the production approval / revenue contract list, create a production plan, determine the production list ID, production plan number, plan name, planned start time, and planned completion time, associate and bind the project number of the product to be delivered in the order, generate the order-production plan relationship model, and add the P2D project number attribute as a foreign key in the #2 production plan list table record. Based on the order-product basic relationship model and the order-production plan relationship model, calculate the standard planned cost of the two auxiliary controller products X-2 with project number 11201 according to the standard planned cost-relational algebra model R1:

[0084]

[0085] (3) Step S3: Based on the production list ID, production plan number, plan name, planned start time and planned completion time in the production plan list #2, create the procurement requirement #3, determine the external purchase contract list ID, external purchase contract number, expected arrival time and purchased materials of lists #3.1 and #3.2, add the P2D project number attribute as a foreign key to the records in the two tables, and generate the production plan-purchase order relationship pattern.

[0086] (4) Step S4: Based on the purchase contract list ID, purchase contract number, estimated arrival time, and purchased materials in the purchase lists #3.1 and #3.2, create an inbound order upon actual arrival. Determine the inbound list ID, inbound order number, material name, material model, and arrival time for the warehouse list #4. Add the P2D item number attribute as a foreign key to the records in tables #4.1 and #4.2 to generate the purchase order-inventory relationship model. Based on the production plan-purchase order relationship model, the purchase order-inventory relationship model, and the quota plan cost-relational algebra model R1, calculate the purchase cost of the two auxiliary controller products X-2 with item number 11201 according to the purchase cost-relational algebra model R2:

[0087]

[0088] (5) Step S5: Based on the production list ID, production plan number, plan name, planned start time, planned completion time, and associated #0 product quota BOM of the #2 production plan list, create the #5 material requisition list when actual production is required. Determine the material requisition list ID, outbound slip number, production list ID, inbound list ID, material model, outbound quantity, and unit price. Confirm the actual production consumption. Add the P2D project number and production plan list ID attributes as foreign keys to the #5.1 and #5.2 material requisition list records to generate the production plan-material requisition relationship schema. Based on the production plan-material requisition relationship schema and the quota plan cost-relational algebra model R1, calculate the actual cost of the two auxiliary controller products X-2 with project number 11201 according to the actual cost-relational algebra model R3:

[0089]

[0090] (6) Step S6. Based on the products to be delivered in the production commission letter / revenue contract list in #1.1, create the #6 delivery list when actually delivering the goods, determine the delivery note ID, delivery time, production commission letter approval number / revenue contract internal number and product model, add the production commission letter approval number / revenue contract internal number attribute as a foreign key to the record in table #6, and generate the order-delivery note relationship schema.

[0091] (7) Step S7. Based on the products to be delivered in the #1.1 Production Letter / Revenue Contract List, create the #7 Settlement List after updating the #6 Delivery List. Determine the Settlement Order ID, Settlement Time, Production Letter Approval Number / Revenue Contract Internal Number, Product Model, and Total Settlement Price. Add the Production Letter Approval Number / Revenue Contract Internal Number attribute as a foreign key to the records in Table #7, generating the order-settlement order relationship schema. Based on the order-delivery order relationship schema and the order-settlement order relationship schema, calculate the actual revenue of the two sets of auxiliary controller products X-2 with project number 11201 according to the actual revenue-relational algebra model S:

[0092]

[0093] Example 3: A data chain construction system for supporting product material cost accounting based on project number is provided, including:

[0094] Project Number Generation Unit: Configured to generate project numbers for products to be delivered based on orders;

[0095] Module A: Configured to build the order-production plan relationship pattern;

[0096] Module B: Configured to build the production plan-purchase order relationship schema;

[0097] Module C: Configured to build the purchase order-inventory relationship pattern;

[0098] Module D: Configured to build the production plan-material requisition relationship schema;

[0099] Module E: Configured to build the order-delivery note relationship schema;

[0100] Module F: is configured to build the order-settlement relationship pattern.

[0101] Specific embodiments of the present invention have been described above. It should be understood that the present invention is not limited to the specific embodiments described above, and those skilled in the art can make various changes or modifications within the scope of the claims, which do not affect the essence of the present invention. Unless otherwise specified, the embodiments and features described in this application can be arbitrarily combined with each other.

Claims

1. A data chain construction method for supporting product material cost accounting based on item numbers, characterized by, Includes the following steps: Step S1: Generate the product item number to be delivered based on the order; Step S2: Construct the order-production plan relationship model. Specifically, based on the order number, order name, order time, delivery time, and total order price, create a production plan, determine the production plan number, plan name, plan time, and delivery time, and associate and bind the item numbers of the products to be delivered in the order. This generates the order-production plan relationship model. Based on the order-product basic relationship model and the order-production plan relationship model, construct the quota planned cost-relational algebra model R1, where: wherein, represents an order-production plan join operation, represents a product-production plan join operation, represents a projection operation, wherein is a join operator, is a projection operator, which calculates the normative plan cost of product materials by the normative plan cost-relation algebra model R1; Step S3: Construct the production plan-purchase order relationship model; Step S4: Construct the purchase order-inventory relationship model; Step S5: constructing a production plan-material requisition relationship mode, specifically, creating a material requisition according to the production plan number, plan name, plan time and delivery time of the production plan, determining the material requisition number, material requisition name, requisition time, belonging plan and belonging warehouse, and generating a production plan-material requisition relationship mode; constructing an actual cost-relationship algebra model according to the quota plan cost-relationship algebra model R1 and the production plan-material requisition relationship mode R 3, wherein This represents the sum of the products of the quantity of materials requisitioned and the corresponding unit price. This indicates the connection operation between the production plan and the material requisition form. This indicates the product-material requisition link operation, using the actual cost-relational algebra model. R 3. Calculate the actual cost of the product; Step S6: Construct the order-delivery note relationship schema; Step S7: Construct the order-settlement statement relationship pattern.

2. The data chain construction method for supporting product material cost accounting based on project number as described in claim 1, characterized in that, In step S1, the product item number is determined by a certain type of product to be delivered under a specific order. Based on this step, the product item number to be delivered is generated, and the product to be delivered in the order is associated with and bound to the product item number to establish an order-product basic relationship model. Based on this relationship model, steps S2-S7 are carried out.

3. The data chain construction method for supporting product material cost accounting based on project number as described in claim 2, characterized in that, In step S3, the production plan-purchase order relationship model is constructed: based on the production plan number, plan name, plan time and delivery time in the production plan, a purchase order is created, the purchase order number, purchase name, purchase time, expected delivery time and purchase materials are determined, and the project number of the product to be delivered in the production plan is associated and bound to generate the production plan-purchase order relationship model; In step S4, the purchase order-inventory relationship model is constructed: based on the purchase order number, purchase name, purchase time, expected arrival time and purchased materials in the purchase order, inventory is created when the goods actually arrive. The material number, material name, warehousing time, arrival time and warehouse are determined, and the item number of the product to be delivered in the purchase list is associated and bound to generate the purchase order-inventory relationship model.

4. The data chain construction method for supporting product material cost accounting based on project number as described in claim 3, characterized in that, Based on the production plan-purchase order relationship model, the purchase order-inventory relationship model, and the quota planned cost-relational algebra model R1, a purchase cost-relational algebra model R2 is constructed, where: This indicates a purchase order-inventory connection operation. This indicates a product-inventory join operation, which calculates the product procurement cost using the procurement cost-relational algebra model R2.

5. The data chain construction method for supporting product material cost accounting based on project number as described in claim 4, characterized in that, In step S6, the order-delivery note relationship model is constructed: based on the products to be delivered in the order, a delivery note is created when the goods are actually delivered, and the delivery number, delivery time, order to which it belongs and delivered products are determined, thus generating the order-delivery note relationship model.

6. The data chain construction method for supporting product material cost accounting based on project number as described in claim 5, characterized in that, In step S7, the order-settlement document relationship model is constructed: based on the products to be delivered in the order, a settlement document is created after the delivery note is created, and the settlement number, settlement time, order to which it belongs, and total settlement price are determined to generate the order-settlement document relationship model.

7. The data chain construction method for supporting product material cost accounting based on project number as described in claim 6, characterized in that, Based on the order-delivery note relationship pattern and the order-settlement note relationship pattern, a revenue-relational algebraic model S is constructed, where: This represents the connection operation of order-delivery note-settlement note-purchase order, and calculates the actual revenue of the product through the revenue-relational algebra model S.

8. A data chain construction system for supporting product material cost accounting based on project number, applied to the method described in claim 7, characterized in that, include: Project Number Generation Unit: Configured to generate project numbers for products to be delivered based on orders; Module A: Configured to build the order-production plan relationship pattern; Module B: Configured to build the production plan-purchase order relationship schema; Module C: Configured to build the purchase order-inventory relationship pattern; Module D: Configured to build the production plan-material requisition relationship schema; Module E: Configured to build the order-delivery note relationship schema; Module F: is configured to build the order-settlement relationship pattern.

Images