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

Abstract

The invention belongs to the field of enterprise resource planning (ERP) systems and cost accounting, and particularly relates to a data chain construction method and system for supporting product material cost accounting based on project numbers, and the method comprises the steps: S1, generating a to-be-delivered product project number according to an order; s2, constructing an order-production plan relation mode; s3, constructing a production plan-purchase order relation mode; s4, constructing a purchase order-inventory relation mode; s5, constructing a production plan-material picking list relation mode; s6, constructing an order-delivery order relation mode; step S7, constructing an order-settlement order relation mode; according to the method, key data breakpoints are connected in series to support full-process material cost accounting, each direct material cost quota and actual cost expenditure are accurately tracked, finally, refined cost control is achieved, and decision-making quality is improved.

Description

Technical Field

[0001] The present invention belongs to the field of enterprise resource planning (ERP) system and cost accounting, and in particular relates to a data chain construction method and system for supporting product material cost accounting based on project numbers. Background Art

[0002] As the sales prices, material consumption and market pricing of manufacturing companies tend to be unified, cost accounting has become an important way for companies to control costs and improve efficiency.

[0003] Generally, the direct material cost accounting of products relies on the data chain integrity and relevance of multiple modules such as sales orders, production plans, procurement management, inventory management, and cost management of the enterprise resource planning (ERP) system. However, in the actual application process, the business scope of enterprise management gradually expands with the development of the enterprise. The ERP system generally has relatively independent business module systems, differences in business data specifications, and data breakpoints in the upstream and downstream of the supply chain, which makes it impossible for the entire ERP system to provide accurate and consistent business data to procurement management, inventory management, and cost management modules based on orders, delivery plans, and production plans, resulting in difficulties in the quota planning cost, procurement cost, and actual cost accounting of product materials. In order to solve this technical problem, the present invention provides an ERP data chain construction method based on project numbers, which is used to realize the series connection of data breakpoints at low cost and high efficiency, and provide technical support for product material cost accounting business. Summary of the invention

[0004] The technical problem solved by the present invention is as follows: the present invention is used to solve the problems that the existing ERP system generally has relatively independent business module systems, differences in business data specifications, and data breakpoints in the upstream and downstream of the supply chain, which 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 the present invention is to propose a data chain construction method and system based on the ERP system to support product material cost accounting based on project numbers, connect key data breakpoints in series to support the 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 the present invention:

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

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

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

[0010] Step S3: construct a production plan-purchase order relationship model;

[0011] Step S4: construct a purchase order-inventory relationship model;

[0012] Step S5: construct a production plan-material picking list relationship model;

[0013] Step S6: constructing an order-delivery order relationship model;

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

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

[0016] Further, in step S2, an order-production plan relationship model is constructed: according to the order number, order name, order time, delivery time, and order total price information, a production plan is created, the production plan number, plan name, plan time, and delivery time are determined, the project number of the product to be delivered in the bound order is associated, and an order-production plan relationship model is generated. Based on the order-product basic relationship model and the order-production plan relationship model, a quota plan cost-relational algebra model R1 is constructed, where:

[0017]

[0018] in, Indicates the order-production plan connection operation,

[0019] Represents the product-production plan connection operation, represents a projection operation, where is a connection operator, π is a projection operator, and the standard planned cost of product materials is calculated through the standard planned cost-relational algebra model R1.

[0020] Further, in step S3, a production plan-purchase order relationship model is constructed: according to the production plan number, plan name, planned time and delivery time in the production plan, a purchase order is created, the purchase order number, purchase name, purchase time, estimated arrival time and purchase material are determined, and the project number of the product to be delivered in the production plan is associated and bound to generate a production plan-purchase order relationship model.

[0021] Further, in step S4, a purchase order-inventory relationship model is constructed: according to the purchase order number, purchase name, purchase time, estimated arrival time and purchase material in the purchase order, inventory is created when the goods actually arrive, the material number, material name, warehousing time, storage time and warehouse are determined, and the project number of the product to be delivered in the purchase list is associated and bound to generate a purchase order-inventory relationship model.

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

[0023]

[0024] Indicates the purchase order-inventory connection operation. It represents the product-inventory connection operation, and calculates the product purchase cost through the purchase cost-relational algebra model R2.

[0025] Further, in step S5, a production plan-material picking list relationship model is constructed: according to the production plan number, plan name, plan time and delivery time of the production plan, a material picking list is created during actual production picking, and the material picking number, material picking name, picking time, plan to which it belongs and warehouse to which it belongs are determined to generate a production plan-material picking list relationship model; according to the quota plan cost-relational algebra model R1 and the production plan-material picking list relationship model, an actual cost-relational algebra model R3 is constructed, wherein

[0026]

[0027] SUM(unit price*received quantity) means the sum of the product of the received quantity and the corresponding unit price. Indicates the production plan-material picking list connection operation.

[0028] It represents the product-picking list connection operation, and calculates the actual cost of the product through the actual cost-relational algebra model R3.

[0029] Further, in step S6, an order-delivery note relationship model is constructed: according to the products to be delivered of the order, a delivery note is created when the actual delivery is shipped, the delivery number, delivery time, order and delivery products are determined, and the order-delivery note relationship model is generated.

[0030] Further, in step S7, an order-settlement order relationship model is constructed: according to the products to be delivered of the order, a settlement order is created after the delivery order is created, and the settlement number, settlement time, order and settlement total price are determined to generate an order-settlement order relationship model.

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

[0032] It represents the order-delivery order-settlement order-purchase order connection operation, 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, comprising:

[0034] An item number generating unit: configured to generate an item number of a product to be delivered according to an order;

[0035] Building module A: is configured to build an order-production plan relationship model;

[0036] Building module B: configured to build a production plan-purchase order relationship model;

[0037] Building module C: configured to build a purchase order-inventory relationship model;

[0038] Building module D: is configured to build a production plan-material picking list relationship model;

[0039] Building module E: configured to build an order-delivery order relationship model;

[0040] Building module F: configured to build an order-settlement order relationship model.

[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 suppliers were logically associated.

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

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

[0045] (4) The data breakpoints of each business module of the ERP system are connected in series, and historical business data can be quickly reconstructed and new business data can be connected based on the project number attributes, thus reducing the manual association of data across business modules.

[0046] (5) The established relational algebra model can clearly track the quota, procurement and actual cost expenditure of product materials, confirm actual income, avoid unnecessary waste and overspending, and achieve accurate cost control. BRIEF DESCRIPTION OF THE DRAWINGS

[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 It is a schematic diagram of an implementation example of building a data chain based on project numbers to support material cost accounting for aviation products. DETAILED DESCRIPTION

[0051] The present invention is described in detail below in conjunction with specific embodiments. The following embodiments will help those skilled in the art to further understand the present invention, but are not intended to limit the present invention in any form. It should be noted that, for those of ordinary skill in the art, several changes and improvements can also be made without departing from the concept of the present invention. These all belong to the protection scope of the present invention.

[0052] In embodiment 1, the present invention proposes a data chain construction method based on project number to support product material cost accounting, comprising the following steps:

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

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

[0055] Step S3: construct a production plan-purchase order relationship model;

[0056] Step S4: construct a purchase order-inventory relationship model;

[0057] Step S5: construct a production plan-material picking list relationship model;

[0058] Step S6: constructing an order-delivery order relationship model;

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

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

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

[0062]

[0063] in, Indicates the order-production plan connection operation,

[0064] Represents the product-production plan connection operation, represents a projection operation, where is a connection operator, π is a projection operator, and the standard planned cost of product materials is calculated through 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 material are determined, the project number of the product to be delivered in the production plan is associated and bound, and a production plan-purchase order relationship model is generated.

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

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

[0068]

[0069] Indicates the purchase order-inventory connection operation. It represents the product-inventory connection operation, and calculates the product purchase cost through the purchase cost-relational algebra model R2.

[0070] In one embodiment of the present invention, in step S5, a production plan-material picking list relationship model is constructed: according to the production plan number, plan name, plan time and delivery time of the production plan, a material picking list is created during actual production picking, and the material picking number, material picking name, picking time, belonging plan and belonging warehouse are determined to generate a production plan-material picking list relationship model; according to the quota plan cost-relational algebra model R1 and the production plan-material picking list relationship model, an actual cost-relational algebra model R3 is constructed, wherein

[0071]

[0072] SUM(unit price*received quantity) means the sum of the product of the received quantity and the corresponding unit price. Indicates the production plan-material picking list connection operation. It represents the product-picking list connection operation, and calculates the actual cost of the product through the actual cost-relational algebra model R3.

[0073] In one embodiment of the present invention, in step S6, an order-delivery note relationship model is constructed: according to the products to be delivered of the order, a delivery note is created when the actual delivery is shipped, the delivery number, delivery time, order and delivery products are determined, and the order-delivery note relationship model is generated.

[0074] In one embodiment of the present invention, in step S7, an order-settlement order relationship model is constructed: according to the products to be delivered of the order, a settlement order is created after the delivery order is created, the settlement number, settlement time, order and settlement total price are determined, and the order-settlement order relationship model is generated.

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

[0076]

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

[0078] Embodiment 2:

[0079] This embodiment is implemented based on the technical solution of the present invention. Since the number of data entities involved in the relational algebra operation process is too large to be fully listed, this implementation case is only demonstrated based on the simplified process from order to delivery of aviation products, such as Figure 4 As shown. This embodiment provides a detailed process of relational algebra query for the actual material cost accounting of a certain enterprise's aviation products, but the scenarios and protection scope of product material cost accounting that can be handled by the present invention are not limited to the following implementation cases. This implementation case omits the design and transformation 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 the project number is generated, and the business data of the instance object is decrypted in advance, but the present invention is applicable to Figure 1 Shown is all the content in the entire data chain construction process.

[0080] This embodiment takes the implementation background of aviation product material cost accounting 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; 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 picking list; object #6 corresponds to E6: delivery order; object #7 corresponds to E7: settlement order.

[0081] According to the ER model of the business, the physical layer table structure, attributes, and fields of the database are transformed and filled, and the relational model is constructed. The specific implementation process is as follows:

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

[0083] (2) Step S2, based on the commissioning letter approval number / income contract internal number, planned completion time, and order provisional total price information in the commissioning letter / income 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, the quota plan cost of the two sets of auxiliary controller products X-2 with project number 11201 is calculated according to the quota plan cost-relational algebra model R1 as follows:

[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 #3 procurement demand, determine the outsourcing contract list ID, outsourcing contract number, expected arrival time and purchased materials in the #3.1 and #3.2 lists, add the P2D project number attribute as a foreign key in the records of the two tables, and generate the production plan-purchase order relationship model.

[0086] (4) Step S4, based on the outsourcing contract list ID, outsourcing contract number, expected arrival time and purchased materials in the purchase list #3.1 and #3.2, create a warehouse entry order when the goods actually arrive, determine the warehouse entry list ID, warehouse entry number, material name, material model, and arrival time of the #4 warehouse list, add the P2D project number attribute as a foreign key in the #4.1 and #4.2 table records, and generate a 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, the purchase cost of two sets of auxiliary controller products X-2 with project number 11201 is calculated 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 and planned completion time of the #2 production plan list and the associated #0 product quota BOM, create a #5 material picking list during actual production, determine the material picking list ID, outbound order number, production list ID, inbound list ID, material model, outbound quantity, unit price, confirm the actual production consumption, add the P2D project number and production plan list ID attributes as foreign keys in the #5.1 and #5.2 material picking list table records, and generate a production plan-material picking list relationship model. Based on the production plan-material picking list relationship model and the quota plan cost-relational algebra model R1, the actual cost of the two sets of auxiliary controller products X-2 with project number 11201 is calculated according to the actual cost-relational algebra model R3:

[0089]

[0090] (6) Step S6. Create a delivery list #6 based on the products to be delivered in the production letter / revenue contract list #1.1 when the actual delivery is made, determine the delivery note ID, delivery time, production letter approval number / revenue contract internal number and product model, add the production letter approval number / revenue contract internal number attribute as a foreign key in the #6 table record, and generate an order-delivery note relationship model.

[0091] (7) Step S7. According to the products to be delivered in the list of #1.1 commissioning letter / income contract, create #7 settlement list after updating the #6 delivery list, determine the settlement list ID, settlement time, commissioning letter approval number / income contract internal number, product model and settlement total price, add the commissioning letter approval number / income contract internal number attribute as a foreign key in the #7 table record, and generate the order-settlement list relationship model. Based on the order-delivery list relationship model and the order-settlement list relationship model, the actual revenue of the two sets of auxiliary controller products X-2 with project number 11201 is calculated according to the actual revenue-relational algebra model S as follows:

[0092]

[0093] Embodiment 3: A data chain construction system for supporting product material cost accounting based on project numbers is provided, comprising:

[0094] An item number generating unit: configured to generate an item number of a product to be delivered according to an order;

[0095] Building module A: is configured to build an order-production plan relationship model;

[0096] Building module B: configured to build a production plan-purchase order relationship model;

[0097] Building module C: configured to build a purchase order-inventory relationship model;

[0098] Building module D: is configured to build a production plan-material picking list relationship model;

[0099] Building module E: configured to build an order-delivery order relationship model;

[0100] Building module F: configured to build an order-settlement order relationship model.

[0101] The above describes the specific embodiments of the present invention. It should be understood that the present invention is not limited to the above specific embodiments, and those skilled in the art can make various changes or modifications within the scope of the claims, which does not affect the essence of the present invention. In the absence of conflict, the embodiments of the present application and the features in the embodiments can be combined with each other arbitrarily.

Claims

1. A data chain construction method based on project number to support product material cost accounting, characterized in that: The following steps are involved: Step S1: Generate the item number of the product to be delivered according to the order; Step S2: constructing an order-production plan relationship model; Step S3: construct a production plan-purchase order relationship model; Step S4: construct a purchase order-inventory relationship model; Step S5: construct a production plan-material picking list relationship model; Step S6: constructing an order-delivery order relationship model; Step S7: Construct the order-settlement order relationship model.

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

3. The data chain construction method based on project number to support product material cost accounting according to claim 2 is characterized in that: In step S2, an order-production plan relationship model is constructed: according to the order number, order name, order time, delivery time, and order total price information, a production plan is created, the production plan number, plan name, plan time, and delivery time are determined, the project number of the product to be delivered in the bound order is associated, and an order-production plan relationship model is generated. Based on the order-product basic relationship model and the order-production plan relationship model, a quota plan cost-relational algebra model R1 is constructed, where: in, Indicates the order-production plan connection operation, Represents the product-production plan connection operation, represents a projection operation, where is a connection operator, π is a projection operator, and the standard planned cost of product materials is calculated through the standard planned cost-relational algebra model R1.

4. The data chain construction method based on project number to support product material cost accounting according to claim 3 is characterized in that: In step S3, a production plan-purchase order relationship model is constructed: according to 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 arrival time and purchase material are determined, and the project number of the product to be delivered in the production plan is associated and bound to generate a production plan-purchase order relationship model; In step S4, a purchase order-inventory relationship model is constructed: according to the purchase order number, purchase name, purchase time, estimated arrival time and purchase material in the purchase order, inventory is created when the goods actually arrive, the material number, material name, warehousing time, storage time and warehouse are determined, and the project number of the product to be delivered in the purchase list is associated and bound to generate a purchase order-inventory relationship model.

5. The data chain construction method based on project number to support product material cost accounting according to claim 4 is characterized in that: Based on the production plan-purchase order relationship model, the purchase order-inventory relationship model and the quota plan cost-relational algebra model R1, the purchase cost-relational algebra model R2 is constructed, where: Indicates the purchase order-inventory connection operation. It represents the product-inventory connection operation, and calculates the product purchase cost through the purchase cost-relational algebra model R2.

6. The method for constructing a data chain based on project number to support product material cost accounting according to claim 5, characterized in that: In step S5, a production plan-material picking list relationship model is constructed: according to the production plan number, plan name, planned time and delivery time of the production plan, a material picking list is created during actual production picking, and the material picking number, material picking name, picking time, plan to which it belongs and warehouse to which it belongs are determined to generate a production plan-material picking list relationship model; according to the quota plan cost-relational algebra model R1 and the production plan-material picking list relationship model, an actual cost-relational algebra model R3 is constructed, wherein SUM (unit price * quantity to be collected) means the sum of the product of the quantity to be collected and the corresponding unit price, Ω2 Plan number = Belonging planΩ5 means production plan-material picking list connection operation, Item No. = Delivery Product Item No. Plan number = belonging plan Ω5 represents the product-material picking list connection operation, and the actual cost of the product is calculated through the actual cost-relational algebraic model R3.

7. The method for constructing a data chain based on project number to support product material cost accounting according to claim 6, characterized in that: In step S6, an order-delivery order relationship model is constructed: according to the products to be delivered of the order, a delivery order is created when the actual delivery is shipped, and the delivery number, delivery time, order and delivery products are determined to generate an order-delivery order relationship model.

8. The method for constructing a data chain based on project number to support product material cost accounting according to claim 7, characterized in that: In step S7, an order-settlement order relationship model is constructed: according to the products to be delivered of the order, a settlement order is created after the delivery order is created, and the settlement number, settlement time, order and settlement total price are determined to generate an order-settlement order relationship model.

9. The method for constructing a data chain based on project number to support product material cost accounting according to claim 8, characterized in that: Based on the order-delivery order relationship model and the order-settlement order relationship model, the revenue-relational algebra model S is constructed, where: It represents the order-delivery order-settlement order-purchase order connection operation, and calculates the actual revenue of the product through the revenue-relational algebra model S.

10. A data chain construction system based on project number to support product material cost accounting, applied to the method of claim 9, characterized in that: include: An item number generating unit: configured to generate an item number of a product to be delivered according to an order; Building module A: is configured to build an order-production plan relationship model; Building module B: configured to build a production plan-purchase order relationship model; Building module C: configured to build a purchase order-inventory relationship model; Building module D: is configured to build a production plan-material picking list relationship model; Building module E: configured to build an order-delivery order relationship model; Building module F: configured to build an order-settlement order relationship model.

Images