[Home] [Help]
PACKAGE BODY: APPS.WMS_XDOCK_EXCEPTION
Source
1 PACKAGE BODY WMS_XDOCK_EXCEPTION AS
2 /* $Header: WMSXDEXB.pls 120.4 2005/10/21 14:37:19 gayu noship $*/
3
4 PROCEDURE print_debug(p_err_msg VARCHAR2,
5 p_level NUMBER := 4)
6 IS
7 l_debug number := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
8 BEGIN
9 inv_mobile_helper_functions.tracelog
10 (p_err_msg => p_err_msg,
11 p_module => 'WMS_XDOCK_EXCPT',
12 p_level => p_level);
13
14 END;
15
16 PROCEDURE find_exception
17 ( x_errbuf OUT nocopy VARCHAR2
18 ,x_retcode OUT nocopy NUMBER
19 ,p_org_id IN NUMBER
20 ,p_look_ahead_time IN NUMBER )
21 IS
25 l_return_status VARCHAR2(30);
22 l_debug NUMBER := NVL(fnd_profile.VALUE('INV_DEBUG_TRACE'), 0);
23 l_progress VARCHAR2(10) := '10';
24 l_module_name VARCHAR2(30) := 'FIND_EXCEPTION';
26 l_msg_count NUMBER;
27 l_msg_data VARCHAR2(10000);
28
29 l_now DATE;
30 l_dock_start_time DATE;
31 l_dock_mean_time DATE;
32 l_dock_end_time DATE;
33 l_expected_supply_time DATE;
34 l_expected_demand_time DATE;
35 l_buffer_interval INTERVAL DAY TO SECOND;
36 l_op_interval INTERVAL DAY TO SECOND;
37 l_xdock_window_interval INTERVAL DAY TO SECOND;
38 l_exception_code VARCHAR2(150);
39 l_update_flag VARCHAR2(1);
40 l_dummy_sn inv_reservation_global.serial_number_tbl_type;
41
42 l_new_reservation inv_reservation_global.mtl_reservation_rec_type;
43 l_xdock_criteria_rec wms_crossdock_criteria%ROWTYPE;
44 l_num_exception_thrown NUMBER;
45 l_status BOOLEAN;
46 BEGIN
47
48 l_progress := '001';
49 l_now := Sysdate;
50 l_num_exception_thrown := 0;
51 x_retcode := 0;
52
53 IF (l_debug = 1) THEN
54 print_debug('Entering find_exception...');
55 print_debug('Current time is ' || l_now);
56 print_debug(' p_org_id => '||p_org_id);
57 print_debug(' p_look_ahead_time =>'||p_look_ahead_time);
58 END IF;
59
60
61 --Query 1: All unstaged crossdock peggins whose corresponding
62 -- expected_supply_date<= l_now + p_look_ahead_time window AND
63 -- 2: All crossdock peggings with exceptions
64
65 l_progress := '002';
66 FOR l_xdock_pegging_rec IN (SELECT reservation_id
67 , crossdock_criteria_id
68 , supply_source_type_id
69 , supply_source_header_id
70 , supply_source_line_id
71 , supply_source_line_detail
72 , demand_source_type_id
73 , demand_source_header_id
74 , demand_source_line_id
75 , demand_source_line_detail
76 , supply_receipt_date
77 , exception_code
78 , demand_ship_date
79 FROM mtl_reservations
80 WHERE Nvl(crossdock_flag,'N') = 'Y'
81 AND Nvl(staged_flag, 'N') = 'N' --???
82 AND supply_source_type_id <> 13 --??? not inventory
83 AND (supply_receipt_date <= l_now + p_look_ahead_time
84 OR
85 exception_code IS NOT NULL)
86 AND organization_id = p_org_id) LOOP
87
88 l_update_flag := 'N';
89 l_exception_code := NULL;
90 l_new_reservation.demand_ship_date := fnd_api.g_miss_date;
91 l_new_reservation.supply_receipt_date := fnd_api.g_miss_date;
92
93 l_progress := '003';
94 BEGIN
95 IF (l_debug = 1) THEN
96 print_debug('Looking at rsv:'||l_xdock_pegging_rec.reservation_id);
97 print_debug(' crossdock_criteria_id:'||l_xdock_pegging_rec.crossdock_criteria_id);
98 print_debug(' supply_source_type_id:'||l_xdock_pegging_rec.supply_source_type_id);
99 print_debug(' supply_source_header_id:'||l_xdock_pegging_rec.supply_source_header_id);
100 print_debug(' supply_source_line_id:'||l_xdock_pegging_rec.supply_source_line_id);
101 print_debug(' supply_source_line_detail:'||l_xdock_pegging_rec.supply_source_line_detail);
102 print_debug(' demand_source_type_id:'||l_xdock_pegging_rec.demand_source_type_id);
103 print_debug(' demand_source_header_id:'||l_xdock_pegging_rec.demand_source_header_id);
104 print_debug(' demand_source_line_id:'||l_xdock_pegging_rec.demand_source_line_id);
105 print_debug(' demand_source_line_detail:'||l_xdock_pegging_rec.demand_source_line_detail);
106 print_debug(' supply_receipt_date:'||l_xdock_pegging_rec.supply_receipt_date);
107 print_debug(' demand_ship_date:'||l_xdock_pegging_rec.demand_ship_date);
108 print_debug(' exception_code:'||l_xdock_pegging_rec.exception_code);
109 END IF;
110
111 l_progress := '004';
112 -- Get BT, OPT, CW by crossdock criteria ???
113 l_xdock_criteria_rec := wms_xdock_pegging_pub.get_crossdock_criteria(l_xdock_pegging_rec.crossdock_criteria_id);
114
115 l_buffer_interval := numtodsinterval(Nvl(l_xdock_criteria_rec.buffer_interval,0),
116 Nvl(l_xdock_criteria_rec.buffer_uom,'HOUR'));
117 l_op_interval := numtodsinterval(Nvl(l_xdock_criteria_rec.processing_interval ,0),
118 Nvl(l_xdock_criteria_rec.processing_uom,'HOUR'));
119 l_xdock_window_interval := numtodsinterval(Nvl(l_xdock_criteria_rec.window_interval ,0),
120 Nvl(l_xdock_criteria_rec.window_uom,'HOUR'));
121
122 IF (l_debug = 1) THEN
123 print_debug('l_buffer_interval => '||l_buffer_interval);
124 print_debug('l_op_interval => ' || l_op_interval);
125 print_debug('l_xdock_window_interval => ' || l_xdock_window_interval);
126 END IF;
127
128 -- Call get_expected_time to get the actual expected_supply_date
129 l_progress := '007';
130 IF (l_xdock_pegging_rec.supply_source_type_id = 27) THEN
131 --For reservations in RCV, the expected supply time will
132 --always be now
133 l_expected_supply_time := l_now;
134 ELSE
135 wms_xdock_pegging_pub.get_expected_time
136 ( p_source_type_id => l_xdock_pegging_rec.supply_source_type_id
137 ,p_source_header_id => l_xdock_pegging_rec.supply_source_header_id
138 ,p_source_line_id => l_xdock_pegging_rec.supply_source_line_id
139 ,p_source_line_detail_id => l_xdock_pegging_rec.supply_source_line_detail
140 ,p_supply_or_demand => 1 --SUPPLY
141 ,p_crossdock_criterion_id => l_xdock_pegging_rec.crossdock_criteria_id --???
142 ,x_return_status => l_return_status
143 ,x_msg_count => l_msg_count
144 ,x_msg_data => l_msg_data
145 ,x_dock_start_time => l_dock_start_time
146 ,x_dock_mean_time => l_dock_mean_time
147 ,x_dock_end_time => l_dock_end_time
148 ,x_expected_time => l_expected_supply_time);
149 END IF;
150 l_progress := '008';
151
152 IF (l_return_status <> fnd_api.g_ret_sts_success) THEN
153 IF (l_debug = 1) THEN
154 print_debug('Error getting expected supply time!');
155 END IF;
156 l_progress := '009';
157 RAISE fnd_api.g_exc_unexpected_error;
158 END IF;
159
160 -- Call get_expected_time to get the actual expected_demand_date
161 l_progress := '010';
162 wms_xdock_pegging_pub.get_expected_time
163 ( p_source_type_id => l_xdock_pegging_rec.demand_source_type_id
164 ,p_source_header_id => l_xdock_pegging_rec.demand_source_header_id
165 ,p_source_line_id => l_xdock_pegging_rec.demand_source_line_id
166 ,p_source_line_detail_id => l_xdock_pegging_rec.demand_source_line_detail
167 ,p_supply_or_demand => 2 --demand
168 ,p_crossdock_criterion_id => l_xdock_pegging_rec.crossdock_criteria_id --???
169 ,x_return_status => l_return_status
170 ,x_msg_count => l_msg_count
171 ,x_msg_data => l_msg_data
172 ,x_dock_start_time => l_dock_start_time
173 ,x_dock_mean_time => l_dock_mean_time
174 ,x_dock_end_time => l_dock_end_time
175 ,x_expected_time => l_expected_demand_time);
176 l_progress := '011';
177
178 IF (l_return_status <> fnd_api.g_ret_sts_success) THEN
179 IF (l_debug = 1) THEN
180 print_debug('Error getting expected demand time!');
181 END IF;
182 l_progress := '012';
183 RAISE fnd_api.g_exc_unexpected_error;
184 END IF;
185
186 IF (l_debug = 1) THEN
187 print_debug('l_xdock_pegging_rec.supply_receipt_date:'||
188 To_char(l_xdock_pegging_rec.supply_receipt_date,'yyyy/mm/dd hh:mi:ss'));
189 print_debug('l_expected_supply_time:'||To_char(l_expected_supply_time,'yyyy/mm/dd hh:mi:ss'));
190 print_debug('l_xdock_pegging_rec.demand_ship_date:'||To_char(l_xdock_pegging_rec.demand_ship_date,'yyyy/mm/dd hh:mi:ss'));
191 print_debug('l_expected_demand_time:'||To_char(l_expected_demand_time,'yyyy/mm/dd hh:mi:ss'));
192 END IF;
193
194 --{{
195 --Create a peg, manuipulate document so that actual expected
196 --receipt time is different from the supply_receipt_date of the peg.
197 --After exception program is run, make sure that the
198 --supply_receipt_date is updated }}
199 IF (l_expected_supply_time <> l_xdock_pegging_rec.supply_receipt_date) THEN
200 -- needs to update supply_date on reservation
201 -- ??? Need to update demand time also ???
202 l_new_reservation.supply_receipt_date := l_expected_supply_time;
203 l_update_flag := 'Y';
204 END IF;
205
206 IF (l_expected_demand_time <> l_xdock_pegging_rec.demand_ship_date) THEN
207 -- needs to update demand on reservation
208 l_new_reservation.demand_ship_date := l_expected_demand_time;
209 l_update_flag := 'Y';
210 END IF;
211
212 --Start checking for exceptions
213
214 -- Exception happens under following situations:
215 -- l_expected_supply_time - EST
216 -- l_expected_demand_time - EDT
217 -- l_now - NOW
218 -- exception_code - EC
219 --
220 -- if EST < NOW then
221 -- if EDT < NOW then
222
223 -- if EDT > EST then
224 -- if EDT <= EST + BT then EC = LE
225 -- elsif EDT <= EST + BT + OPT then EC = LW
226 -- elsif EDT > EST + BT + OPT + XDW then EC = LW
227 -- else -- this means that demand was scheduled to be shipped anytime today.
228 -- if NOW > EST + XDW then EC = LW
229
230 -- elsif EDT <= NOW + BT then EC = LE
231 -- elsif EDT <= NOW + BT + OPT then EC = LW
232 -- elsif EDT > EST + BT + OPT + XDW then EC = LW
233 -- else
234 -- if EDT < EST then
235 -- null
236 -- elsif EDT <= EST + BT then EC = SE
237 -- elsif EDT <= EST + BT + OPT then EC = SW
238 -- elsif EDT > EST + BT + OPT + XDW then EC = SW
239
240 IF (l_expected_supply_time < l_now) THEN
241 IF (l_expected_demand_time < l_now) THEN
242 IF (l_expected_demand_time > l_expected_supply_time) THEN
243 IF (l_expected_demand_time<=l_expected_supply_time+l_buffer_interval) THEN
244 l_exception_code := 'LE';
245 ELSIF (l_expected_demand_time<=l_expected_supply_time+l_buffer_interval+l_op_interval) THEN
246 l_exception_code := 'LW';
247 ELSIF (l_expected_demand_time>l_expected_supply_time+l_buffer_interval+l_op_interval+l_xdock_window_interval) THEN
248 l_exception_code := 'LW';
249 ELSE
250 l_exception_code := NULL;
251 END IF;
252 ELSE
253 IF (l_now >l_expected_supply_time + l_xdock_window_interval )THEN
254 l_exception_code := 'LW';
255 END IF;
256 END IF;
257 --For the following three cases, treat EST as NOW
258 ELSIF l_expected_demand_time <= (l_now + l_buffer_interval) THEN
259 l_exception_code := 'LE';
260 ELSIF l_expected_demand_time <= (l_now + l_buffer_interval + l_op_interval) THEN
261 l_exception_code := 'LW';
262 ELSIF l_expected_demand_time > (l_expected_supply_time + l_buffer_interval + l_op_interval + l_xdock_window_interval) THEN
263 l_exception_code := 'LW';
264 ELSE
265 l_exception_code := NULL;
266 END IF;
267 ELSE
268 IF (l_expected_demand_time < l_expected_supply_time) THEN
269 l_exception_code := NULL;
270 ELSIF (l_expected_demand_time <= l_expected_supply_time + l_buffer_interval) THEN
271 --Zone 1: EDT <= EST + BT. Xdock not feasible
272 l_exception_code := 'SE';
273 ELSIF (l_expected_demand_time <= l_expected_supply_time + l_buffer_interval + l_op_interval) THEN
274 --Zone 2: EDT <= EST + BT + OPT. Manual expedite needed
275 l_exception_code := 'SW';
276 ELSIF (l_expected_demand_time > l_buffer_interval + l_op_interval + l_xdock_window_interval) THEN
277 --Zone 4: EDT > EST + BT + OPT + Xdock Window. Unnecessary wait
278 l_exception_code := 'SW';
279 ELSE
280 --Zone 4: No exception
281 l_exception_code := NULL;
282 END IF;
283 END IF;
284
285 IF (l_debug = 1) THEN
286 print_debug('l_update_flag: '||l_update_flag);
287 print_debug('l_exception_code: '||l_exception_code);
288 END IF;
289
290 IF (Nvl(l_xdock_pegging_rec.exception_code,'@@@') <> Nvl(l_exception_code,'@@@')) THEN
291 l_update_flag := 'Y';
292 END IF;
293
294 IF (l_update_flag = 'Y') THEN
295
296 BEGIN
297 UPDATE mtl_reservations
298 SET exception_code = l_exception_code
299 , supply_receipt_date = Decode(l_new_reservation.supply_receipt_date,
300 fnd_api.g_miss_date,
301 supply_receipt_date,
302 l_new_reservation.supply_receipt_date)
303 , demand_ship_date = Decode(l_new_reservation.demand_ship_date,
304 fnd_api.g_miss_date,
305 demand_ship_date,
306 l_new_reservation.demand_ship_date)
307 WHERE reservation_id = l_xdock_pegging_rec.reservation_id;
308 EXCEPTION
309 WHEN OTHERS THEN
310 IF (l_debug = 1) THEN
311 print_debug('Exception occurred at progress: '||l_progress);
312 print_debug('SQLCODE:'||SQLCODE||' SQLERRM:'||SQLERRM);
313 END IF;
314 RAISE fnd_api.g_exc_unexpected_error;
315 END ;
316
317 l_progress := '015';
318
319 END IF;
320 l_progress := '016';
321 EXCEPTION
322 WHEN OTHERS THEN
323 IF (l_debug = 1) THEN
324 print_debug('Exception occurred at progress: '||l_progress);
325 print_debug('SQLCODE:'||SQLCODE||' SQLERRM:'||SQLERRM);
326 END IF;
327 l_num_exception_thrown := l_num_exception_thrown + 1;
328 END;
329 END LOOP;
330
331 l_status := wms_xdock_pegging_pub.clear_crossdock_cache;
332
333 IF (l_num_exception_thrown > 0) THEN
334 x_retcode := 1; --Return a warning
335 END IF;
336
337 IF (l_debug = 1) THEN
338 print_debug('Number of exception thrown:'||l_num_exception_thrown);
339 print_debug('x_retcode: '||x_retcode);
340 print_debug('Successfully exiting find_exceptions!');
341 END IF;
342 EXCEPTION
343 WHEN OTHERS THEN
344 IF (l_debug = 1) THEN
345 print_debug('Exception occurred at progress: '||l_progress);
346 print_debug('SQLCODE:'||SQLCODE||' SQLERRM:'||SQLERRM);
347 END IF;
348 l_status := wms_xdock_pegging_pub.clear_crossdock_cache;
349 x_errbuf := 'U';--????
350 x_retcode := 2; --Return an error
351 END find_exception;
352 END wms_xdock_exception;