-
Notifications
You must be signed in to change notification settings - Fork 0
/
03_update_mrp_tables_procedure.sql
98 lines (72 loc) · 4 KB
/
03_update_mrp_tables_procedure.sql
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
DELIMITER $$
CREATE PROCEDURE UpdateMRPTables ()
BEGIN
DECLARE done_i INT DEFAULT 0;
DECLARE var_i_id VARCHAR(16);
DECLARE cursor_i CURSOR FOR
SELECT item_id FROM item_period GROUP BY item_id;
DECLARE CONTINUE HANDLER FOR NOT FOUND SET done_i = 1;
OPEN cursor_i;
item_loop: LOOP
FETCH cursor_i INTO var_i_id;
IF done_i = 1 THEN
LEAVE item_loop;
END IF;
itemwise_update_block: BEGIN
DECLARE done_j INT DEFAULT 0;
DECLARE var_j_period INT;
DECLARE var_j_gross INT;
DECLARE var_j_inventory INT;
DECLARE var_j_receipt INT;
DECLARE var_j_release INT;
DECLARE net_requirement INT;
DECLARE receipt_increment INT;
DECLARE cursor_j CURSOR FOR
SELECT period_number, gross_requirement,
projected_inventory, planned_order_receipt,
planned_order_release
FROM item_period
WHERE item_id = var_i_id AND
period_number <> 0;
DECLARE CONTINUE HANDLER FOR NOT FOUND SET done_j = 1;
OPEN cursor_j;
period_loop: LOOP
FETCH cursor_j INTO var_j_period, var_j_gross,
var_j_inventory, var_j_receipt,
var_j_release;
IF done_j = 1 THEN
LEAVE period_loop;
END IF;
SET net_requirement = var_j_gross -
GetProjectedInventory( var_i_id,
var_j_period - 1);
IF net_requirement > 0 THEN
SET receipt_increment = CEILING(net_requirement/
GetLotSize(var_i_id))*
GetLotSize(var_i_id);
UPDATE item_period
SET planned_order_receipt = planned_order_receipt +
receipt_increment,
projected_inventory = projected_inventory +
receipt_increment -
gross_requirement
WHERE item_id = var_i_id AND
period_number = var_j_period;
UPDATE item_period
SET planned_order_release = planned_order_release +
receipt_increment
WHERE item_id = var_i_id AND
period_number = var_j_period - GetLeadTime(var_i_id);
ELSE
UPDATE item_period
SET projected_inventory = -net_requirement
WHERE item_id = var_i_id AND
period_number = var_j_period;
END IF;
END LOOP period_loop;
CLOSE cursor_j;
END itemwise_update_block;
END LOOP item_loop;
CLOSE cursor_i;
END $$
DELIMITER;