The company is also developing the inventory system recently, taking the industry’s more successful Kingdee to analyze and learn, improve their own business level inventory system is the most important warehousing and warehousing, cost and profit calculation

Core process of warehousing operation

Participating Roles

The role of the input and output operation can be seen in the accounting project of Kingdee system. This role is very important and involves the later table structure design

  1. Supplier: the inventory system sends the material out to the supplier, and the supplier supplies to the system for warehousing
  2. Material: The smallest unit involved in the inventory calculation
  3. Warehouse: The same material can appear in multiple warehouses at the same time, but the objects in and out of the warehouse can only select one warehouse at a time

A unit in a unit of measurement that can manage materials

There is also the role of the originator (usually a company originator), where the originator is the author and the table structure is temporarily ignored

For the

Open Warehouse Management -> Purchase in/Sales out, taking purchase in as an example

You can see the fields in the core of the entry sheet have these

  1. Supplier (where these materials are sourced from)
  2. Inbound date
  3. Number (not modifiable, automatically generated)
  4. Incoming materials
  5. Warehousing warehouse
  6. The amount received
  7. A unit of quantity received
  8. The unit price
  9. The amount of

Valuation methods

In kingdee’s new material interface, you can view the pricing method supported by the current system

Currently, the industry is widely used:

  1. Moving average method
  2. The first in first out method of other valuation method is used less at present, interested can go to understand the accounting knowledge is more familiar with the first in first out method, take the first in first out valuation method for example

Table structure design

  1. Warehouse receipt sheet
  2. List of materials associated with warehouse receipt
  3. Balance sheet of batch material inventory amount
  4. Batch material inventory in and out record form
  5. Supplier list
  6. Material list
  7. Warehouse table
  8. The unit table
  9. The inventory table can be seen that the simplest batch inventory table also has 9 tables by the author from simple to complex order to design the table, design a minimum table structure to meet the requirements of warehousing

The unit table

At present, conversion rate is not considered, so it does not participate in the conversion of inventory, so the unit table field is relatively simple SQL as follows:

CREATE TABLE `unit` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `code` varchar(32) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL DEFAULT ' ' COMMENT 'Item code'.`name` varchar(128) NOT NULL DEFAULT ' ' COMMENT 'name',
  PRIMARY KEY (`id`) USING BTREE,
  UNIQUE KEY `name` (`name`) USING BTREE,
  UNIQUE KEY `code` (`code`) USING BTREE COMMENT '`
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='Unit of measure';
Copy the code

Supplier list

In the system, the supplier is the object of warehousing, warehousing materials are bought from the supplier, warehousing materials are returned to the supplier

CREATE TABLE `supply` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `name` varchar(45) NOT NULL DEFAULT ' ' COMMENT 'name',
  PRIMARY KEY (`id`) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='Supplier list';
Copy the code

Warehouse table

The warehouse table is used to identify the warehouse in which the material exists. The same material may exist in multiple warehouses, so the material must be selected to enter the warehouse

CREATE TABLE `warehouse` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT COMMENT 'primary key ID'.`organization_id` bigint(20) unsigned NOT NULL DEFAULT '0' COMMENT 'store id'.`name` varchar(32) NOT NULL DEFAULT ' ' COMMENT 'Warehouse Name',
  PRIMARY KEY (`id`) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='Warehouse Information Sheet';
Copy the code

The inventory table

It is used to record the stock of materials in different warehouses. The quantity of stock needs to be synchronized from batch to inventory table during warehousing and warehousing

CREATE TABLE `inventory` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT COMMENT 'primary key ID'.`warehouse_id` bigint(20) unsigned NOT NULL COMMENT 'warehouse ID'.`material_id` bigint(20) unsigned NOT NULL DEFAULT '0' COMMENT 'material id'.`count` decimal(16.2) NOT NULL DEFAULT '0.00' COMMENT 'Gross inventory'.`lock_count` decimal(16.2) NOT NULL DEFAULT '0.00' COMMENT 'Occupied stock'.`available_count` decimal(16.2) NOT NULL DEFAULT '0.00' COMMENT 'Available stock'.`cost_amount` decimal(16.4) NOT NULL DEFAULT '0.0000' COMMENT 'Total cost'.`updated_at` int(11) unsigned NOT NULL DEFAULT '0' COMMENT 'Update Time'.`created_at` int(11) unsigned NOT NULL DEFAULT '0' COMMENT 'Creation time'.`deleted_at` int(11) unsigned NOT NULL DEFAULT '0' COMMENT 'Delete time',
  PRIMARY KEY (`id`) USING BTREE,
  KEY `idx_group_id_label_id_relation_id_type` (`warehouse_id`.`material_id`) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='Warehouse inventory Table';
Copy the code

Material list

Material is the basic object of inventory operation

CREATE TABLE `material` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `material_code` varchar(45) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL DEFAULT ' ' COMMENT 'Item code'.`name` varchar(45) NOT NULL COMMENT 'name'.`category_id` int(11) unsigned NOT NULL COMMENT 'Category of ownership'.`category_name` varchar(45) NOT NULL COMMENT 'Category name'.`unit_weight` decimal(10.4) unsigned NOT NULL DEFAULT '0.0000' COMMENT 'Unit weight'.`base_unit_id` bigint(20) DEFAULT NULL COMMENT 'Master Unit ID'.`base_unit_name` varchar(64) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT ' ' COMMENT 'Master Unit Name'.`updated_at` int(11) unsigned NOT NULL DEFAULT '0' COMMENT 'Update Date'.`created_at` int(11) unsigned NOT NULL DEFAULT '0' COMMENT 'Creation period'.`deleted_at` int(11) unsigned NOT NULL DEFAULT '0' COMMENT 'Delete time',
  PRIMARY KEY (`id`) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='material';
Copy the code

Warehouse receipt sheet

The document table is used to record the number of inbound and outbound documents, and some meta information of the document, such as the supplier who initiated the document, the operator, the amount of the document, the quantity of materials in the document and so on

CREATE TABLE `in_out` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `type` tinyint(2) unsigned NOT NULL DEFAULT '0' COMMENT '1: Purchase Order 2: Purchase receipt 3: Purchase Return order '.`in_out_code` varchar(32) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL DEFAULT ' ' COMMENT 'number'.`warehouse_id` bigint(20) unsigned NOT NULL DEFAULT '0' COMMENT 'warehouse ID'.`warehouse_name` varchar(64) NOT NULL DEFAULT ' ' COMMENT 'Warehouse name'.`supply_id` bigint(20) unsigned NOT NULL DEFAULT '0' COMMENT 'Supplier ID'.`supply_name` varchar(64) NOT NULL DEFAULT ' ' COMMENT 'Supplier Name'.`address` varchar(45) NOT NULL DEFAULT ' ' COMMENT 'Delivery address'.`count` int(10) unsigned NOT NULL DEFAULT '0' COMMENT 'Quantity of Materials'.`amount_total` decimal(18.8) unsigned NOT NULL DEFAULT '0.00000000' COMMENT 'Total amount of current documents'.`amount_count` decimal(10.2) unsigned NOT NULL DEFAULT '0.00' COMMENT 'Total number of current documents'.`created_at` int(11) unsigned NOT NULL DEFAULT '0' COMMENT 'Creation time'.`updated_at` int(11) unsigned NOT NULL DEFAULT '0' COMMENT 'Update Time'.`deleted_at` int(11) NOT NULL DEFAULT '0' COMMENT 'Delete time',
  PRIMARY KEY (`id`) USING BTREE,
  KEY `idx_group_id_expense_sn` (`in_out_code`) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='Schedule';
Copy the code

Material association table of incoming and outgoing documents

Record the detailed information of the materials in and out of the warehouse, such as the name, code, unit, quantity, unit price, amount, tax and other core fields involved in the inventory calculation

CREATE TABLE `in_out_detail` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT COMMENT 'primary key ID'.`warehouse_id` bigint(20) unsigned NOT NULL DEFAULT '0' COMMENT 'warehouse ID'.`warehouse_name` varchar(62) NOT NULL DEFAULT ' ' COMMENT 'Warehouse name'.`in_out_code` varchar(32) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL DEFAULT ' ' COMMENT 'number'.`in_out_id` bigint(20) unsigned NOT NULL DEFAULT '0' COMMENT 'single ID no..`in_out_type` tinyint(4) DEFAULT NULL COMMENT 'Document Type'.`material_id` bigint(20) unsigned NOT NULL DEFAULT '0' COMMENT 'the correlation ID'.`material_name` varchar(64) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL DEFAULT ' ' COMMENT 'Associated Material Name'.`material_code` varchar(64) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL DEFAULT ' ' COMMENT 'Associated Material SN'.`unit_basic_name` varchar(32) NOT NULL DEFAULT ' ' COMMENT 'Basic Unit Name'.`unit_basic_value` decimal(10.3) NOT NULL DEFAULT '1.000' COMMENT 'Base unit value'.`unit_basic_count` decimal(10.2) unsigned NOT NULL DEFAULT '0.00' COMMENT 'Number of master units'.`unit_basic_price` decimal(14.4) unsigned NOT NULL DEFAULT '0.0000' COMMENT 'Unit price for master unit excluding tax'.`unit_basic_tax_price` decimal(14.4) unsigned NOT NULL DEFAULT '0.0000' COMMENT 'Unit price of master Unit including tax'.`unit_basic_total` decimal(18.8) unsigned NOT NULL DEFAULT '0.00000000' COMMENT 'Master Unit amount excluding tax'.`unit_basic_tax_total` decimal(18.8) NOT NULL DEFAULT '0.00000000' COMMENT 'Amount of Master Unit including tax'.`tax` decimal(10.2) unsigned NOT NULL DEFAULT '0.00' COMMENT 'rate'.`tax_total` decimal(18.8) unsigned NOT NULL DEFAULT '0.00000000' COMMENT 'tax'.`production_at` int(11) unsigned NOT NULL DEFAULT '0' COMMENT 'Production date'.`created_at` int(11) unsigned NOT NULL DEFAULT '0'.`updated_at` int(11) unsigned NOT NULL DEFAULT '0'.`deleted_at` int(11) unsigned NOT NULL DEFAULT '0',
  PRIMARY KEY (`id`) USING BTREE,
  KEY `idx_in_out_id_material_id` (`in_out_id`.`material_id`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=1110 DEFAULT CHARSET=utf8 COMMENT='Document material Association List';
Copy the code

Material lot list

This batch list is to record the current inventory status of all batches of materials. Theoretically, the inventory of a batch of materials should use 0 inventory at most.

CREATE TABLE `batch` (
  `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `code` int(11) DEFAULT NULL COMMENT 'Batch Code'.`warehouse_id` bigint(20) DEFAULT NULL COMMENT 'warehouse id'.`in_type` int(4) DEFAULT NULL COMMENT 'Warehouse entry Type'.`in_id` bigint(20) DEFAULT NULL COMMENT 'ID of warehouse entry'.`material_id` bigint(20) DEFAULT NULL COMMENT 'Association ID of material, Dish and set meal'.`end_tax_price` decimal(14.4) DEFAULT '0.0000' COMMENT 'Current unit price including tax'.`end_price` decimal(14.4) DEFAULT '0.0000' COMMENT 'Current unit price excluding tax'.`end_count` decimal(10.2) DEFAULT '0.00' COMMENT 'Current master unit inventory minus occupancy'.`end_total_amount` decimal(18.8) DEFAULT '0.00000000' COMMENT 'Current total amount excluding tax (this value will change after receipt of documents)'.`end_tax_total_amount` decimal(18.8) DEFAULT '0.00000000' COMMENT 'Current total amount including tax'.`end_tax_total` decimal(18.8) DEFAULT '0.00000000' COMMENT 'Current balance tax'.`cost_tax_price` decimal(14.4) DEFAULT '0.0000' COMMENT 'Original unit price including tax'.`cost_price` decimal(14.4) DEFAULT '0.0000' COMMENT 'Original unit price excluding tax'.`cost_count` decimal(10.2) DEFAULT '0.00' COMMENT 'Original master unit inventory quantity'.`cost_total_amount` decimal(18.8) DEFAULT '0.00000000' COMMENT 'Total amount excluding tax'.`cost_tax_total_amount` decimal(18.8) DEFAULT '0.00000000' COMMENT 'Original total amount including tax'.`cost_tax_total` decimal(18.8) DEFAULT '0.00000000' COMMENT 'Original tax'.`gross_profit` decimal(18.8) DEFAULT '0.00000000' COMMENT 'Profit amount (including tax: in-cost/out-cost)'.`lock_count` decimal(10.2) DEFAULT '0.00' COMMENT 'Occupied/locked number'.`production_at` int(11) DEFAULT NULL COMMENT 'Production date'.`status` tinyint(1) DEFAULT '1' COMMENT 'Frozen 0 unfrozen 1'.`remark` varchar(128) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT 'note'.`updated_at` int(11) unsigned NOT NULL DEFAULT '0' COMMENT 'Update Time'.`created_at` int(11) unsigned NOT NULL DEFAULT '0' COMMENT 'Creation time'.`deleted_at` int(11) unsigned NOT NULL DEFAULT '0' COMMENT 'Delete time',
  PRIMARY KEY (`id`) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='Batch list';
Copy the code

Material batch snapshot table

Record snapshots of changes to the bom for each batch

CREATE TABLE `batch_snapshot` (
  `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `batch_id` bigint(20) DEFAULT NULL.`in_out_detail_type` int(4) DEFAULT NULL COMMENT 'Type of Document Details'.`in_out_detail_id` bigint(20) DEFAULT NULL COMMENT 'Unit price for warehousing'.`in_count` decimal(12.4) DEFAULT '0.0000' COMMENT 'Quantity in storage'.`in_total_amount` decimal(18.8) DEFAULT NULL.`in_tax_total` decimal(18.8) DEFAULT '0.00000000' COMMENT 'tax'.`in_tax_total_amount` decimal(18.8) DEFAULT '0.00000000' COMMENT 'Amount of Warehousing including tax'.`out_price` decimal(14.4) DEFAULT '0.0000' COMMENT 'Unit price for shipment'.`out_count` decimal(10.2) DEFAULT '0.00' COMMENT 'Quantity out'.`out_total_amount` decimal(18.8) DEFAULT '0.00000000' COMMENT 'Amount ex-warehouse before tax'.`out_tax_total` decimal(18.8) DEFAULT '0.00000000' COMMENT 'Outbound tax'.`out_tax_total_amount` decimal(18.8) DEFAULT '0.00000000' COMMENT 'Exportation amount including tax'.`out_cost_tax_price` decimal(14.4) DEFAULT '0.0000' COMMENT 'Unit price of outbound cost (including tax)'.`out_cost_price` decimal(14.4) DEFAULT '0.0000' COMMENT 'Unit price of outbound cost (excluding tax)'.`out_cost_total_amount` decimal(18.8) DEFAULT '0.00000000' COMMENT 'Exportation cost amount including tax'.`out_cost_tax_total_amount` decimal(18.8) DEFAULT '0.00000000' COMMENT 'Exportation cost excluding tax amount'.`out_cost_tax_total` decimal(18.8) DEFAULT '0.00000000' COMMENT 'Export cost tax'.`end_price` decimal(14.4) DEFAULT '0.0000' COMMENT 'Current unit price before tax'.`end_tax_price` decimal(14.4) DEFAULT '0.0000' COMMENT 'Current unit price including tax'.`end_count` decimal(10.2) DEFAULT '0.00' COMMENT 'Current balance'.`end_total_amount` decimal(18.8) DEFAULT '0.00000000' COMMENT 'Current balance before tax'.`end_tax_total_amount` decimal(18.8) unsigned DEFAULT '0000000000.00000000' COMMENT 'Current tax-inclusive balance'.`end_tax_total` decimal(18.8) DEFAULT '0.00000000' COMMENT 'Current balance tax'.`updated_at` int(11) unsigned NOT NULL DEFAULT '0' COMMENT 'Update Time'.`created_at` int(11) unsigned NOT NULL DEFAULT '0' COMMENT 'Creation time'.`deleted_at` int(11) unsigned NOT NULL DEFAULT '0' COMMENT 'Delete time',
  PRIMARY KEY (`id`) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='Batch Snapshot table';
Copy the code

The core algorithm

In the inventory system, as long as the operation that affects the inventory and amount can be counted as the core algorithm of inventory, among which the most important is the valuation method of materials, because every time in and out of the warehouse will use this material valuation method, and different valuation methods will directly affect the cost and profit of inventory

Cost calculation: The cost includes tax, so tax should be included in the cost calculation. The cost of user input into the system does not include tax, so the system needs to calculate the tax by tax point and add it to the cost. 2. The cost of user input to the system is tax-inclusive, so the system needs to subtract the tax point to get the tax-exclusive cost

First-in, first-out (FIFO) algorithm: first-in, first-out algorithm

A couple of points to note

Amounts are stored in mysql as a decimal type, and in Java as a BigDecimal type to indicate that a batch snapshot table only records an inventory snapshot of a batch of material and does not participate in the inventory and cost calculation.

Continuously updated…