For more information about how to analyze LIFO or FIFO problems in the WMS system, see.
For more information about how to analyze LIFO or FIFO problems in the WMS system, see.
The Code is as follows:
--- A problem often encountered in Warehouse Management
1. Brief description of LIFO and FIFO
--- FIFO: First in, First out. First in, First out.
--- LIFO: Last in, First out. Then, First out.
-- For example, goods A: 10 items purchased on December 1, 1st day of this month, with A unit price of 10 yuan/piece, 20 items purchased on the third day, 15 yuan/piece, 10 items purchased on the 10th day, and 8 yuan/piece at the unit price.
-- 35 items will be delivered in 15th day of this month.
-- First-in-first-out based on FIFO, that is, the first purchased inventory is first issued. Therefore, 10 items are first purchased on the first day, and 20 items are then received on the third day, the total cost of the five items purchased on the last 10 days is 10x10 + 20x15 + 5x8 = 440 RMB.
-- According to the LIFO, the inventory is first issued, so 10 items are first purchased on the 10-day basis, and 20 items are then purchased on the 3-day basis, the total cost of the 5 items that are purchased on the last day is 10x8 + 20*15 + 5*10 = 430 RMB.
Ii. Example
The Code is as follows:
--------
Create table stock
(Id int not null primary key,
Articleno varchar (20) not null,
Rcvdate datetime not null,
Qty int not null,
Unitprice money not null
)
Go
----
Insert stock
Select 1, '20140901', '2014-1-1-1 ', 10561122 union
Select 2, '20140901', '2014-2-2 ', 10561122 union
Select 3, '20140901', '2014-3-3 ', 35, 15 union
Select 4, '20140901', '2014-4-4 ', 10561122 union
Select 5, '200', '1970-5-5 ', 10561122 union
Select 6, '20140901', '2014-6-6 ', 10561122 union
Select 7, '123', '1970-7-7', 10561122 union
Select 8, '20180101', '201808-8', 10561122
Go
---- How should we calculate the value of inventory sales if we sell 300 products in?
---- 1 use the current replacement cost. The cost of each product is 8 in 300, that is, the cost value of your 2400 products is.
---- 2 use the current average cost unit price, a total of 420, the total cost is 6530, the average cost per piece is 15.55
---- 1. LIFO (first-in-first-out)
---- 2011-8-8 110*8
---- 2011-7-7 75*17
---- 2011-6-6 65*30
---- 2011-5-5 50*10
----- The total cost is 4605
----- 2. FIFO (first-in-first-out)
---- '2017-1-1 ', 15*10
--- '2014-2-2 ', 25*12
----- '2014-3-3 ', 35*15
----- '2017-4-4', 45*20
----- '2014-5-5', 55*10
----- '2017-6-6 ', 65*30
----- '2017-7-7', 65*17
---- The total cost is 5480
--- Cost View
Create view costLIFO
As
Select unitprice from stock
Where rcvdate = (select MAX (rcvdate) from stock)
Go
Create view costFIFO
As
Select sum (unitprice * qty)/SUM (qty) as unitprice from stock
Go
----- Find the latest date that satisfies the order and has sufficient stock. If you are lucky, the inventory quantity on a day is exactly the same as the number required by the order.
----- The total cost can be returned as the answer. If the order quantity is more than the inventory quantity, nothing is returned. If the inventory quantity in a day is larger than the order quantity
--- Take a look at the current unit price, multiply by the extra quantity, and subtract it.
--- The following queries and views only tell us the inventory value of stock items. Note that these queries and views are not actually delivered from stock.
Create view LIFO
As
Select s1.rcvdate, s1.unitprice, sum (s2.qty) as qty, sum (s2.qty * s2.unitprice) as totalcost
From stock s1, stock s2
Where s2.rcvdate> = s1.rcvdate
Group by s1.rcvdate, s1.unitprice
Go
Select (totalcost-(qty-300) * unitprice) as cost
From lifo as l
Where rcvdate = (select max (rcvdate) from lifo as l2 where qty >=300)
Go
Create view FIFO
As
Select s1.rcvdate, s1.unitprice, sum (s2.qty) as qty, sum (s2.qty * s2.unitprice) as totalcost
From stock s1, stock s2
Where s2.rcvdate <= s1.rcvdate
Group by s1.rcvdate, s1.unitprice
Go
Select (totalcost-(qty-300) * unitprice) as cost
From fifo as l
Where rcvdate = (select min (rcvdate) from lifo as l2 where qty >=300)
--------
Go
-----
----- Update the inventory table in real time after delivery
Create view CurrStock
As
Select s1.rcvdate, SUM (case when s2.rcvdate> s1.rcvdate then s2.qty else 0 end) as PrvQty
, SUM (case when s2.rcvdate <= s1.rcvdate then s2.qty else 0 end) as CurrQty
From stock s1, stock s2
Where s2.rcvdate <= s1.rcvdate
Group by s1.rcvdate, s1.unitprice
Go
Create proc RemoveQty
@ Orderqty int
As
If (@ orderqty> 0)
Begin
Update stock set qty = case when @ orderqty> = (select currqty from CurrStock as c where c. rcvdate = stock. rcvdate)
Then 0
When @ orderqty <(select prvqty from CurrStock c2 where c2.rcvdate = stock. rcvdate)
Then stock. qty
Else (select currqty from CurrStock as c3 where c3.rcvdate = stock. rcvdate)
-@ Orderqty end
End
--
Delete from stock where qty = 0
---
Go
Exec RemoveQty 20
Go
---------------
3. Use the greedy algorithm for order distribution
The Code is as follows:
------- Another question is how to use the goods in the smallest or largest warehouse to satisfy the order. If the warehouse is not in an ordered order, you can select the desired order by clicking the button.
--- Using the smallest warehouse can bring the minimum workload to the order handling workers. Using the largest warehouse can clear more space in the warehouse.
------- For example: for this set of data, you can use the goods in warehouses (, 7) or (,) to meet the order requirements.
---- This is the packing problem. It is a complete NP system problem. In general, this problem is difficult to solve, because we need to try all the combinations, and if there is a large amount of data,
---- Computers are also hard to handle quickly.
--- With the greedy algorithm, this algorithm is often near optimal. The core of this algorithm is to "Bite the biggest bite" until the goal is achieved or exceeded.
---
-- 1. The first trick is to insert some empty dummy warehouses in the table. If you need to select at most n times, n-1 dummy warehouses.
Insert stock
Select-1, '200', '1970-1-1 ', 10561122 union
Select-2, '20180101', '20180101', 10561122
-- Select-3, '2014-1-1-1 ', 1900
----
Go
Create view pickcombos
As
Select distinct (w1.qty + w2.qty + w3.qty) as totalpick
, Case when w1.id <0 then 0 else w1.id end as bin1, w1.qty as qty1,
Case when w2.id <0 then 0 else w2.id end as bin2, w2.qty as qty2
, Case when w3.id <0 then 0 else w3.id end as bin3, w3.qty as qty3
From stock w1, stock w2, stock w3
Where w1.id not in (w2.id, w3.id)
And w2.id not in (w1.id, w3.id)
And w1.qty> = w2.qty
And w2.qty> = w3.qty
----
--- 1. Use stored procedures to find a selection combination that meets or approaches a certain number
--------
Go
Create proc OverPick
@ Pickqty int
As
If (@ pickqty> 0)
Begin
Select @ pickqty, totalpick, bin1, qty1, bin2, qty2, bin3, qty3
From pickcombos
Where totalpick = (select MIN (totalpick) from pickcombos where totalpick >=@ pickqty)
End
Go
Exec overpick180
----------
Select * from stock
Drop table stock
Drop view lifo
Drop view fifo
Drop view costfifo
Drop view costlifo
Drop view CurrStock
Drop proc OverPick
Drop proc RemoveQty
Drop view pickcombos
