· 7 years ago · Dec 27, 2018, 03:28 PM
1create or replace PROCEDURE Storecases
2
3AS
4
5 v_priority NUMBER := 0;
6
7 v_shpnum NUMBER := 0;
8
9 v_shpdt DATE;
10
11 v_ord VARCHAR2(10) := '';
12
13 v_item VARCHAR2(6) := '';
14
15 v_pieces NUMBER := 0;
16
17 v_cases NUMBER := 0;
18
19 v_innerpicks NUMBER := 0;
20
21 v_casepicks NUMBER := 0;
22
23 v_palletpicks NUMBER := 0;
24
25 v_ErrorMsg VARCHAR2(250) := '' ;
26
27 v_avail NUMBER := 0;
28
29 v_palqty NUMBER := 0;
30
31 v_caseqty NUMBER := 0;
32
33 v_innerqty NUMBER := 0;
34
35 v_ordqty NUMBER := 0;
36
37 v_dec NUMBER := 0;
38
39
40
41-- get distinct priorities with open orderd
42
43 CURSOR c_ordpri IS
44
45 SELECT DISTINCT priority,
46
47 ord_num,
48
49 shp_num,
50
51 sch_shp_dt
52
53 FROM catalyst.OUTORDHDR
54
55 WHERE trunc(sch_shp_dt) > trunc(SYSDATE)
56
57 AND priority <> 99
58
59 AND status = 0
60
61 AND (sch_shp_dt,ord_num) not in (select distinct ship_date, store_order from store_cases)
62
63 ORDER BY priority;
64
65
66
67-- collect order details for specified order
68
69 CURSOR c_orddetail IS
70
71 SELECT DISTINCT od.itm_num itm_num,
72
73 qty_ord,
74
75 NVL(case_qty,0) case_qty,
76
77 NVL(pal_qty,0) pal_qty,
78
79 NVL(ip_qty,0) ip_qty
80
81 FROM catalyst.OUTORDDTL od,
82
83 catalyst.itemconfig ic
84
85 WHERE od.ord_num = v_ord
86
87 AND od.itm_num = ic.itm_num
88
89 AND EXISTS (select 'x' from catalyst.itemloccls where itm_num = od.itm_num and qty_on_hand > 0);
90
91
92
93
94
95BEGIN
96
97
98-- loop through the orders in each wave and calculate by store how many cases will be allocated
99
100-- store results in store_cases table for reporting
101
102FOR v_ordpri IN c_ordpri LOOP
103
104 -- assign field results to variables
105
106 v_ord := v_ordpri.ord_num;
107
108 v_shpnum := v_ordpri.shp_num;
109
110 v_priority := v_ordpri.priority;
111
112 v_shpdt := v_ordpri.sch_shp_dt;
113
114 -- reset accumulators
115
116 v_cases := 0;
117
118 v_pieces := 0;
119
120 --v_innerpicks := 0;
121
122 --v_casepicks := 0;
123
124 --v_palletpicks := 0;
125
126 -- loop through each item for this order and determine who much inventory will be allocated
127
128 -- if any, if allocated calculate inner picks, case picks, and pallet picks for this item
129
130 -- add to accumulators
131
132 FOR v_orddetail IN c_orddetail LOOP
133
134 v_item := v_orddetail.itm_num;
135
136 v_ordqty := v_orddetail.qty_ord;
137
138 v_caseqty := v_orddetail.case_qty;
139
140 --v_innerqty := v_orddetail.ip_qty;
141
142 --v_palqty := v_orddetail.pal_qty;
143
144 --OPEN c_onhand;
145
146 --FETCH c_onhand INTO v_avail;
147
148 --IF ( c_onhand%FOUND ) THEN
149
150
151
152 -- we have enough inventory now figure out the picks
153
154 -- add to accumulators
155
156 v_pieces := v_pieces + v_ordqty;
157
158 IF ( v_caseqty > 0 ) THEN
159
160 v_cases := v_cases + (v_ordqty / v_caseqty);
161
162 END IF;
163
164
165
166 --END IF; -- end check on hand
167
168
169
170 --CLOSE c_onhand;
171
172
173
174
175
176 END LOOP; -- order detail loop
177
178 -- store the results in store_cases table
179
180 -- decrease cases by 8%
181
182 v_dec := v_cases * .35;
183
184 v_cases := v_cases - ROUND(v_dec);
185
186 INSERT INTO STORE_CASES(SHIP_DATE,STORE,CASES,EACHES,PRIORITY,STORE_ORDER)
187
188 VALUES(v_shpdt,v_shpnum,TRUNC(v_cases,2),v_pieces,v_priority,v_ord);
189
190 COMMIT;
191
192END LOOP; -- priority loop
193
194-- all done
195
196EXCEPTION
197
198WHEN OTHERS THEN
199
200 v_ErrorMsg := 'UNKNOWN ERROR CONDITION';
201
202 RAISE;
203
204END;