1 PACKAGE BODY WSH_DELIVERY_SPLITTER_PKG as
2 /* $Header: WSHDESPB.pls 120.4.12010000.2 2008/08/04 12:30:29 suppal ship $ */
3
4 --
5 G_PKG_NAME CONSTANT VARCHAR2(50) := 'WSH_DELIVERY_SPLITTER_PKG';
6 --
7
8 --OTM R12
9 ---------------------------------------------------------------------
10 -- PROCEDURE LOG_DEL_SPLITTER_EXCEPTION
11 --
12 -- parameters: p_delivery_rec record of delivery information containing
13 -- id, gross weight, weight uom,
14 -- pickup loc, org id
15 -- p_content_weight delivery's content's weight sum
16 -- p_new_delivery_name new delivery's name
17 -- p_exception_message the exception message to log
18 -- this is included for complicated exception message
19 -- p_exception_name name of the exception to log
20 -- x_return_status return status of the procedure
21 --
22 -- description: This procedure logs the exceptions for delivery splitting.
23 -- The exception messge parameter is checked first.
24 -- If not NULL then it's used, else generate the message
25 -- depend on the exception name.
26 -- Prereq: this procedure will raise unexpected error if delivery or exception
27 -- name passed in is NULL, so it's caller's responsibility. For
28 -- DEL_SPLIT and DEL_SPLIT_LARGE, p_exception_message must be provided,
29 -- else the procedure will return with unexpected error.
30 ---------------------------------------------------------------------
31 PROCEDURE LOG_DEL_SPLITTER_EXCEPTION
32 (p_delivery_rec IN WSH_ENTITY_INFO_REC,
33 p_content_weight IN NUMBER,
34 p_weight_limit IN NUMBER,
35 p_new_delivery_name IN VARCHAR2 DEFAULT NULL,
36 p_new_delivery_id IN NUMBER DEFAULT NULL,
37 p_exception_message IN VARCHAR2 DEFAULT NULL,
38 p_exception_name IN VARCHAR2,
39 x_return_status OUT NOCOPY VARCHAR2) IS
40
41 l_exception_id WSH_EXCEPTIONS.EXCEPTION_ID%TYPE;
42 l_exception_message WSH_EXCEPTIONS.MESSAGE%TYPE;
43 l_num_error NUMBER;
44 l_num_warn NUMBER;
45 l_return_status VARCHAR2(1);
46 l_msg_count NUMBER;
47 l_msg_data VARCHAR2(32767);
48 l_delivery_id WSH_NEW_DELIVERIES.DELIVERY_ID%TYPE;
49 unexp_error EXCEPTION;
50
51 l_debug_on BOOLEAN;
52 --
53 l_module_name CONSTANT VARCHAR2(100) := 'wsh.plsql.' || g_pkg_name ||
54 '.' || 'LOG_DEL_SPLITTER_EXCEPTION';
55
56 BEGIN
57
58 --
59 l_debug_on := wsh_debug_interface.g_debug;
60 --
61 IF l_debug_on IS NULL THEN
62 l_debug_on := wsh_debug_sv.is_debug_enabled;
63 END IF;
64 --
65 IF l_debug_on THEN
66 wsh_debug_sv.push(l_module_name);
67 wsh_debug_sv.log(l_module_name, 'p_delivery_rec name',
68 p_delivery_rec.entity_name);
69 wsh_debug_sv.log(l_module_name, 'p_delivery_rec id',
70 p_delivery_rec.entity_id);
71 wsh_debug_sv.log(l_module_name, 'p_content_weight',
72 p_content_weight);
73 wsh_debug_sv.log(l_module_name, 'p_weight_limit',
74 p_weight_limit);
75 wsh_debug_sv.log(l_module_name, 'p_new_delivery_name',
76 p_new_delivery_name);
77 wsh_debug_sv.log(l_module_name, 'p_new_delivery_id',
78 p_new_delivery_id);
79 wsh_debug_sv.log(l_module_name, 'p_exception_message',
80 p_exception_message);
81 wsh_debug_sv.log(l_module_name, 'p_exception_name',
82 p_exception_name);
83
84 END IF;
85
86 x_return_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
87 l_num_error := 0;
88 l_num_warn := 0;
89
90 IF (p_exception_name IS NULL OR p_delivery_rec.entity_id IS NULL) THEN
91 IF l_debug_on THEN
92 WSH_DEBUG_SV.logmsg(l_module_name, 'exception name or delivery id is NULL');
93 END IF;
94 RAISE unexp_error;
95 END IF;
96
97 IF (p_new_delivery_id IS NOT NULL) THEN
98 l_delivery_id := p_new_delivery_id;
99 ELSE
100 l_delivery_id := p_delivery_rec.entity_id;
101 END IF;
102
103 IF (p_exception_message IS NOT NULL) THEN
104 l_exception_message := p_exception_message;
105 ELSE
106 IF (p_exception_name = 'WSH_OTM_DEL_OVERSIZED') THEN
107 FND_MESSAGE.SET_NAME('WSH', 'WSH_OTM_DEL_OVERSIZED');
108 FND_MESSAGE.SET_TOKEN('DELIVERY_NAME',
109 p_delivery_rec.entity_name);
110 FND_MESSAGE.SET_TOKEN('GROSS_WEIGHT',
111 p_delivery_rec.gross_weight);
112 FND_MESSAGE.SET_TOKEN('WEIGHT_LIMIT',
113 p_weight_limit);
114 FND_MESSAGE.SET_TOKEN('DEL_DET_WEIGHT',
115 p_content_weight);
116 ELSIF (p_exception_name = 'WSH_OTM_DEL_SPLIT_FAIL') THEN
117 FND_MESSAGE.SET_NAME('WSH', 'WSH_OTM_DEL_SPLIT_FAILED');
118 FND_MESSAGE.SET_TOKEN('DELIVERY_NAME',
119 p_delivery_rec.entity_name);
120 FND_MESSAGE.SET_TOKEN('GROSS_WEIGHT',
121 p_delivery_rec.gross_weight);
122 FND_MESSAGE.SET_TOKEN('WEIGHT_LIMIT',
123 p_weight_limit);
124 FND_MESSAGE.SET_TOKEN('DEL_DET_WEIGHT',
125 p_content_weight);
126 ELSIF (p_exception_name = 'WSH_OTM_DEL_SPLIT_CHILD') THEN
127 FND_MESSAGE.SET_NAME('WSH', 'WSH_OTM_DELIVERY_SPLIT_CHILD');
128 FND_MESSAGE.SET_TOKEN('CHILD_DELIVERY_NAME',
129 p_new_delivery_name);
130 FND_MESSAGE.SET_TOKEN('PARENT_DELIVERY_NAME',
131 p_delivery_rec.entity_name);
132 ELSIF (p_exception_name = 'WSH_OTM_DEL_LOCK_FAIL') THEN
133 FND_MESSAGE.SET_NAME('WSH', 'WSH_NO_LOCK');
134 ELSIF (p_exception_name IN ('WSH_OTM_DEL_SPLIT_LARGE', 'WSH_OTM_DEL_SPLIT')) THEN
135 --the caller API (which is in this file since this is internal api) should provide the
136 --message for these two exceptions due to complication, this is not expected
137 IF l_debug_on THEN
138 WSH_DEBUG_SV.logmsg(l_module_name, 'exception message is not provided for WSH_OTM_DEL_SPLIT_LARGE or WSH_OTM_DEL_SPLIT');
139 END IF;
140 RAISE unexp_error;
141 END IF;
142
143 l_exception_message := FND_MESSAGE.Get;
144
145 END IF;
146
147 IF (l_exception_message IS NULL) THEN
148 IF l_debug_on THEN
149 WSH_DEBUG_SV.logmsg(l_module_name, 'exception message is NULL');
150 END IF;
151 RAISE unexp_error;
152 END IF;
153
154 l_exception_id := NULL;
155
156 IF l_debug_on THEN
157 WSH_DEBUG_SV.logmsg(l_module_name,
158 'Calling program unit WSH_XC_UTIL.LOG_EXCEPTION',
159 WSH_DEBUG_SV.C_PROC_LEVEL);
160 END IF;
161
162 WSH_XC_UTIL.log_exception(
163 p_api_version => 1.0,
164 x_return_status => l_return_status,
165 x_msg_count => l_msg_count,
166 x_msg_data => l_msg_data,
167 x_exception_id => l_exception_id,
168 p_exception_location_id => p_delivery_rec.init_pickup_loc_id,
169 p_logged_at_location_id => p_delivery_rec.init_pickup_loc_id,
170 p_logging_entity => 'SHIPPER',
171 p_logging_entity_id => FND_GLOBAL.USER_ID,
172 p_exception_name => p_exception_name,
173 p_delivery_id => l_delivery_id,
174 p_message => SUBSTRB(l_exception_message,1,2000));
175
176 IF l_debug_on THEN
177 WSH_DEBUG_SV.logmsg(l_module_name, 'return status from WSH_XC_UTIL.log_exception: ' || l_return_status);
178 END IF;
179
180 IF (l_return_status IN (WSH_UTIL_CORE.G_RET_STS_ERROR, WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR)) THEN
181 l_num_error := l_num_error+1;
182 END IF;
183
184 IF (l_return_status = WSH_UTIL_CORE.G_RET_STS_WARNING) THEN
185 l_num_warn := l_num_warn+1;
186 END IF;
187
188 IF l_num_error > 0 THEN
189 x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
190 ELSIF l_num_warn > 0 THEN
191 x_return_status := WSH_UTIL_CORE.G_RET_STS_WARNING;
192 ELSE
193 x_return_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
194 END IF;
195
196 IF l_debug_on THEN
197 WSH_DEBUG_SV.log(l_module_name, 'l_num_error', l_num_error);
198 WSH_DEBUG_SV.log(l_module_name, 'l_num_warn', l_num_warn);
199 WSH_DEBUG_SV.log(l_module_name, 'x_return_status', x_return_status);
200 WSH_DEBUG_SV.pop(l_module_name);
201 END IF;
202
203 EXCEPTION
204 WHEN OTHERS THEN
205 x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR;
206 --
207
208 WSH_UTIL_CORE.default_handler(
209 'WSH_DELIVERY_SPLITTER_PKG.LOG_DEL_SPLITTER_EXCEPTION', l_module_name);
210 IF l_debug_on THEN
211 WSH_DEBUG_SV.logmsg(l_module_name,
212 'Unexpected error has occured. Oracle error message is '||
213 SQLERRM,WSH_DEBUG_SV.C_UNEXPEC_ERR_LEVEL);
214 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:OTHERS');
215 END IF;
216
217 END LOG_DEL_SPLITTER_EXCEPTION;
218
219 ---------------------------------------------------------------------
220 -- PROCEDURE DELIVERY_SPLITTER
221 --
222 -- parameters: p_delivery_tab table of delivery information containing
223 -- id, gross weight, weight uom,
224 -- pickup loc, org id
225 -- p_autosplit_flag variable for the autosplit option,
226 -- defaulted NULL, if Y then we will split
227 -- delivery if size is too large
228 -- x_accepted_del_id table of delivery id that is accepted to
229 -- be sent to GC3
230 -- x_rejected_del_id table of delivery id that is rejected
231 -- because of oversize
232 -- x_return_status return status of the procedure
233 --
234 -- description: this procedure takes in a table of delivery information and
235 -- split the deliveries if the gross weight exceeds limit.
236 -- The splitting is done only if autosplit flag is Y, delivery
237 -- is not assigned to a trip, delivery lines are not pick
238 -- released,delivery's not content firm, delivery's each content
239 -- is not larger than the limit, and the sum of delivery's content
240 -- matches the delivery weight total.
241 ---------------------------------------------------------------------
242 PROCEDURE Delivery_Splitter
243 (p_delivery_tab IN WSH_ENTITY_INFO_TAB,
244 p_autosplit_flag IN VARCHAR2 DEFAULT NULL,
245 x_accepted_del_id OUT NOCOPY WSH_OTM_ID_TAB,
246 x_rejected_del_id OUT NOCOPY WSH_OTM_ID_TAB,
247 x_return_status OUT NOCOPY VARCHAR2) IS
248
249 -- this will get top most delivery detail ID, gross weight,
250 -- and UOM, order by converted gross weight and ignoring null or 0
251 -- weight details
252 CURSOR c_get_delivery_line_and_cont(p_delivery_id IN NUMBER,
253 p_uom IN VARCHAR2) IS
254 SELECT decode(WDD.WEIGHT_UOM_CODE,
255 p_uom, WDD.GROSS_WEIGHT,
256 WSH_WV_UTILS.convert_uom(
257 wdd.weight_uom_code,
258 p_uom,
259 WDD.GROSS_WEIGHT)) gross_weight_converted,
260 WDA.DELIVERY_DETAIL_ID
261 FROM WSH_DELIVERY_DETAILS WDD, WSH_DELIVERY_ASSIGNMENTS WDA
262 WHERE WDA.DELIVERY_ID = p_delivery_id
263 AND wda.delivery_detail_id = wdd.delivery_detail_id
264 AND WDA.PARENT_DELIVERY_DETAIL_ID IS NULL
265 AND WDD.WEIGHT_UOM_CODE IS NOT NULL
266 AND WDD.GROSS_WEIGHT <> 0
267 ORDER BY gross_weight_converted ASC;
268
269 -- this will get all delivery details' (non LPN) released status
270 CURSOR c_get_delivery_line(p_delivery_id IN NUMBER) IS
271 SELECT WDD.RELEASED_STATUS
272 FROM WSH_DELIVERY_DETAILS WDD, WSH_DELIVERY_ASSIGNMENTS WDA
273 WHERE WDA.DELIVERY_ID = p_delivery_id
274 AND WDA.DELIVERY_DETAIL_ID = WDD.DELIVERY_DETAIL_ID
275 AND WDD.CONTAINER_FLAG = 'N'
276 AND WDD.RELEASED_STATUS NOT IN ('R', 'B', 'X');
277
278 -- check if delivery is assigned to a trip
279 CURSOR c_is_delivery_assigned(p_delivery_id IN NUMBER) IS
280 SELECT 1
281 FROM WSH_DELIVERY_LEGS
282 WHERE delivery_id = p_delivery_id
283 AND rownum = 1;
284
285 l_current_weight WSH_NEW_DELIVERIES.GROSS_WEIGHT%TYPE; -- variable used to track delivery weight
286 l_content_weight WSH_NEW_DELIVERIES.GROSS_WEIGHT%TYPE; -- variable used to track total content weight
287
288 -- variable used to track delivery weight for new delivery
289 l_new_weight WSH_NEW_DELIVERIES.GROSS_WEIGHT%TYPE;
290 l_line_and_container_id WSH_UTIL_CORE.ID_TAB_TYPE;
291 l_line_and_container_weight WSH_UTIL_CORE.ID_TAB_TYPE;
292 l_line_status WSH_UTIL_CORE.COLUMN_TAB_TYPE;
293
294 -- used to keep the list of delivery ids for the splitted delivery
295 l_curr_del_split_list WSH_UTIL_CORE.ID_TAB_TYPE;
296 l_curr_del_split_name WSH_UTIL_CORE.COLUMN_TAB_TYPE;
297 l_count NUMBER;
298 -- used to keep the list of delivery ids for the new delivery creation
299 l_current_ids WSH_UTIL_CORE.ID_TAB_TYPE;
300
301 l_temp_ids WSH_UTIL_CORE.ID_TAB_TYPE;
302 l_parameter_info WSH_SHIPPING_PARAMS_PVT.PARAMETER_REC_TYP;
303 l_global_param_info WSH_SHIPPING_PARAMS_PVT.GLOBAL_PARAMETERS_REC_TYP;
304 l_new_delivery_name WSH_NEW_DELIVERIES.NAME%TYPE; -- new delivery id
305 l_action_prms WSH_GLBL_VAR_STRCT_GRP.dd_action_parameters_rec_type;
306 l_temp_num NUMBER;
307
308 l_num_error NUMBER;
309 l_num_warn NUMBER;
310 l_return_status VARCHAR2(1);
311 l_msg_count NUMBER;
312 l_msg_data VARCHAR2(32767);
313
314 l_delivery_info WSH_NEW_DELIVERIES_PVT.DELIVERY_REC_TYPE;
315 i NUMBER;
316 j NUMBER;
317 k NUMBER;
318 z NUMBER;
319 l_exception_name WSH_EXCEPTIONS.EXCEPTION_NAME%TYPE;
320
321 l_debug_on BOOLEAN;
322 --
323 l_module_name CONSTANT VARCHAR2(100) := 'wsh.plsql.' || g_pkg_name ||
324 '.' || 'DELIVERY_SPLITTER';
325 --
326 BEGIN
327
328 --
329 l_debug_on := wsh_debug_interface.g_debug;
330 --
331 IF l_debug_on IS NULL THEN
332 l_debug_on := wsh_debug_sv.is_debug_enabled;
333 END IF;
334 --
335 IF l_debug_on THEN
336 wsh_debug_sv.push(l_module_name);
337 wsh_debug_sv.log(l_module_name, 'P_DELIVERY_TAB.COUNT:',
338 p_delivery_tab.count);
339 wsh_debug_sv.log(l_module_name, 'p_autosplit_flag',
340 p_autosplit_flag);
341 END IF;
342 --
343 x_return_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
344 --
345
346 --intialize
347 l_temp_num := 0;
348 l_num_error := 0;
349 l_num_warn := 0;
350 l_count := 0;
351 l_new_delivery_name := NULL;
352 x_rejected_del_id := WSH_OTM_ID_TAB();
353 x_accepted_del_id := WSH_OTM_ID_TAB();
354 l_exception_name := NULL;
355
356 IF (p_delivery_tab.COUNT = 0) THEN
357 IF l_debug_on THEN
358 WSH_DEBUG_SV.logmsg(l_module_name,'there is no delivery to split');
359 WSH_DEBUG_SV.pop(l_module_name);
360 END IF;
361 RETURN;
362 END IF;
363
364 l_action_prms.caller := 'WSH_DELIVERY_SPLITTER';
365
366 IF l_debug_on THEN
367 WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_SHIPPING_PARAMS_PVT.Get_Global_Parameters',WSH_DEBUG_SV.C_PROC_LEVEL);
368 END IF;
369
370 WSH_SHIPPING_PARAMS_PVT.get_global_parameters(
371 x_param_info => l_global_param_info,
372 x_return_status => l_return_status);
373
374 IF l_debug_on THEN
375 WSH_DEBUG_SV.logmsg(l_module_name, 'return status from WSH_SHIPPING_PARAMS_PVT.Get_Global_Parameters: ' || l_return_status);
376 END IF;
377
378 IF l_return_status = WSH_UTIL_CORE.G_RET_STS_WARNING THEN
379 l_num_warn := l_num_warn + 1;
380 ELSIF (l_return_status IN (WSH_UTIL_CORE.G_RET_STS_ERROR,
381 WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR)) THEN
382 FND_MESSAGE.Set_Name('WSH', 'WSH_INVALID_GLOBAL_PARAMETER');
383 wsh_util_core.add_message(l_return_status,l_module_name);
384
385 x_return_status := l_return_status;
386 --
387 IF l_debug_on THEN
388 WSH_DEBUG_SV.pop(l_module_name);
389 END IF;
390 RETURN;
391 END IF;
392
393 i := p_delivery_tab.FIRST;
394
395 --looping over all the deliveries to process each one for splitting
396 WHILE i IS NOT NULL LOOP
397
398 IF l_debug_on THEN
399 wsh_debug_sv.log(l_module_name, 'P_DELIVERY_TAB.entity_id',
400 p_delivery_tab(i).entity_id);
401 wsh_debug_sv.log(l_module_name, 'p_delivery_tab.entity_name',
402 p_delivery_tab(i).entity_name);
403 wsh_debug_sv.log(l_module_name, 'p_delivery_tab.item_id',
404 p_delivery_tab(i).item_id);
405 wsh_debug_sv.log(l_module_name, 'p_delivery_tab.net_weight',
406 p_delivery_tab(i).net_weight);
407 wsh_debug_sv.log(l_module_name, 'p_delivery_tab.gross_weight',
408 p_delivery_tab(i).gross_weight);
409 wsh_debug_sv.log(l_module_name, 'p_delivery_tab.organization_id',
410 p_delivery_tab(i).organization_id);
411 wsh_debug_sv.log(l_module_name, 'p_delivery_tab.weight_uom',
412 p_delivery_tab(i).weight_uom);
413 wsh_debug_sv.log(l_module_name, 'p_delivery_tab.quantity',
414 p_delivery_tab(i).quantity);
415 wsh_debug_sv.log(l_module_name, 'p_delivery_tab.init_pickup_loc_id',
416 p_delivery_tab(i).init_pickup_loc_id);
417 wsh_debug_sv.log(l_module_name, 'p_delivery_tab.content_firm',
418 p_delivery_tab(i).content_firm);
419 END IF;
420
421
422 --the locking procedure fails when delivery does not have details, and only
423 --case the delivery has no details is when it is DELETE_REQUIRED.
424 --so adding this first query to bypass all the DELETE_REQUIRED deliveries
425
426 --get delivery information
427 IF l_debug_on THEN
428 WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_DELIVERY_VALIDATIONS.get_delivery_information',WSH_DEBUG_SV.C_PROC_LEVEL);
429 END IF;
430
431 WSH_DELIVERY_VALIDATIONS.get_delivery_information(p_delivery_id => p_delivery_tab(i).entity_id,
432 x_delivery_rec => l_delivery_info,
433 x_return_status => l_return_status);
434
435 IF l_debug_on THEN
436 WSH_DEBUG_SV.logmsg(l_module_name, 'return status from WSH_DELIVERY_VALIDATIONS.get_delivery_information: ' || l_return_status);
437 END IF;
438
439 IF (l_return_status IN (WSH_UTIL_CORE.G_RET_STS_ERROR,
440 WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR)) THEN
441
442 --reject failed delivery
443 IF l_debug_on THEN
444 wsh_debug_sv.logmsg(l_module_name, 'Error: cannot get delivery record');
445 END IF;
446
447 l_num_error := l_num_error + 1;
448 x_rejected_del_id.EXTEND;
449 x_rejected_del_id(x_rejected_del_id.COUNT) := p_delivery_tab(i).entity_id;
450 GOTO loop_end;
451 ELSIF l_return_status = WSH_UTIL_CORE.G_RET_STS_WARNING THEN
452 l_num_warn := l_num_warn + 1;
453 END IF;
454
455 --accept all delete required or ignore for planning deliveries, no need to split
456 --Bug 7148914 added OR condition so that same flow is followed for delivery with tms_interface_flag 'DP'
457 IF (l_delivery_info.ignore_for_planning = 'Y' OR
458 l_delivery_info.tms_interface_flag = WSH_NEW_DELIVERIES_PVT.C_TMS_DELETE_REQUIRED OR
459 l_delivery_info.tms_interface_flag = WSH_NEW_DELIVERIES_PVT.C_TMS_DELETE_IN_PROCESS ) THEN
460
461
462 IF l_debug_on THEN
463 wsh_debug_sv.logmsg(l_module_name, 'accepted delivery ' || p_delivery_tab(i).entity_id || ' because ignore for planning, DR or DP status');
464 END IF;
465 x_accepted_del_id.EXTEND;
466 x_accepted_del_id(x_accepted_del_id.COUNT) := p_delivery_tab(i).entity_id;
467
468 GOTO loop_end;
469
470 END IF;
471
472 --now proceed with the lock
473 SAVEPOINT delivery_record;
474
475 --LOCK THE DELIVERY AND IT'S CONTENT
476 IF l_debug_on THEN
477 WSH_DEBUG_SV.logmsg(l_module_name,
478 'Calling program unit WSH_INTERFACE_COMMON_ACTIONS.LOCK_DELIVERY_AND_DETAILS',
479 WSH_DEBUG_SV.C_PROC_LEVEL);
480 END IF;
481
482 WSH_INTERFACE_COMMON_ACTIONS.lock_delivery_and_details(
483 p_delivery_id => p_delivery_tab(i).entity_id,
484 x_return_status => l_return_status);
485
486 IF l_debug_on THEN
487 wsh_debug_sv.log (l_module_name,
488 'Return status from lock delivery and details',
489 l_return_status);
490 END IF;
491
492 --only locking one delivery, should have no warning status for l_return_status
493 IF (l_return_status <> WSH_UTIL_CORE.G_RET_STS_SUCCESS) THEN
494
495 IF l_debug_on THEN
496 wsh_debug_sv.logmsg(l_module_name,
497 'Unable to lock the delivery');
498 END IF;
499
500 l_num_error := l_num_error+1;
501
502 IF l_debug_on THEN
503 WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit log_del_splitter_exception',WSH_DEBUG_SV.C_PROC_LEVEL);
504 END IF;
505
506 log_del_splitter_exception
507 (p_delivery_rec => p_delivery_tab(i),
508 p_content_weight => l_content_weight,
509 p_weight_limit => l_parameter_info.MAX_GROSS_WEIGHT,
510 p_exception_name => 'WSH_OTM_DEL_LOCK_FAIL',
511 x_return_status => l_return_status);
512
513 IF l_debug_on THEN
514 WSH_DEBUG_SV.logmsg(l_module_name, 'return status from log_del_splitter_exception: ' || l_return_status);
515 END IF;
516
517 x_rejected_del_id.EXTEND;
518 x_rejected_del_id(x_rejected_del_id.COUNT) := p_delivery_tab(i).entity_id;
519 GOTO loop_end;
520
521 END IF;
522
523 --get delivery information again to ensure it's latest
524 IF l_debug_on THEN
525 WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_DELIVERY_VALIDATIONS.get_delivery_information',WSH_DEBUG_SV.C_PROC_LEVEL);
526 END IF;
527
528 WSH_DELIVERY_VALIDATIONS.get_delivery_information(p_delivery_id => p_delivery_tab(i).entity_id,
529 x_delivery_rec => l_delivery_info,
530 x_return_status => l_return_status);
531
532 IF l_debug_on THEN
533 WSH_DEBUG_SV.logmsg(l_module_name, 'return status from WSH_DELIVERY_VALIDATIONS.get_delivery_information: ' || l_return_status);
534 END IF;
535
536 IF (l_return_status IN (WSH_UTIL_CORE.G_RET_STS_ERROR,
537 WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR)) THEN
538
539 ROLLBACK TO delivery_record;
540
541 --reject failed delivery
542 IF l_debug_on THEN
543 wsh_debug_sv.logmsg(l_module_name, 'Error: cannot get delivery record');
544 END IF;
545
546 l_num_error := l_num_error + 1;
547 x_rejected_del_id.EXTEND;
548 x_rejected_del_id(x_rejected_del_id.COUNT) := p_delivery_tab(i).entity_id;
549 GOTO loop_end;
550 ELSIF l_return_status = WSH_UTIL_CORE.G_RET_STS_WARNING THEN
551 l_num_warn := l_num_warn + 1;
552 END IF;
553
554 IF l_debug_on THEN
555 WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_SHIPPING_PARAMS_PVT.Get',WSH_DEBUG_SV.C_PROC_LEVEL);
556 END IF;
557
558 --get shipping parameters
559 WSH_SHIPPING_PARAMS_PVT.get(
560 p_organization_id => p_delivery_tab(i).organization_id,
561 x_param_info => l_parameter_info,
562 x_return_status => l_return_status);
563
564 IF l_debug_on THEN
565 WSH_DEBUG_SV.logmsg(l_module_name, 'return status from WSH_SHIPPING_PARAMS_PVT.Get: ' || l_return_status);
566 END IF;
567
568 IF l_return_status = WSH_UTIL_CORE.G_RET_STS_WARNING THEN
569 l_num_warn := l_num_warn + 1;
570 ELSIF (l_return_status IN (WSH_UTIL_CORE.G_RET_STS_ERROR,
571 WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR)) THEN
572
573 ROLLBACK TO delivery_record;
574 FND_MESSAGE.Set_Name('WSH', 'WSH_PARAM_NOT_DEFINED');
575 FND_MESSAGE.Set_Token('ORGANIZATION_CODE',
576 wsh_util_core.get_org_name(p_delivery_tab(i).organization_id));
577 wsh_util_core.add_message(l_return_status,l_module_name);
578 l_num_error := l_num_error+1;
579
580 IF l_debug_on THEN
581 wsh_debug_sv.logmsg(l_module_name, 'Error: cannot get shipping parameter info');
582 END IF;
583
584 x_rejected_del_id.EXTEND;
585 x_rejected_del_id(x_rejected_del_id.COUNT) := p_delivery_tab(i).entity_id;
586
587 GOTO loop_end;
588 END IF;
589
590 IF l_debug_on THEN
591 wsh_debug_sv.log(l_module_name,
592 'Max Gross Weight',
593 l_parameter_info.max_gross_weight);
594 wsh_debug_sv.log(l_module_name,
595 'Global UOM',
596 l_global_param_info.GU_WEIGHT_UOM);
597 wsh_debug_sv.log(l_module_name,
598 'delivery gross weight',
599 p_delivery_tab(i).gross_weight);
600 END IF;
601
602 --if no limit specified in shipping parameter, accept it all
603 IF (l_parameter_info.MAX_GROSS_WEIGHT IS NULL) THEN
604
605 IF l_debug_on THEN
606 wsh_debug_sv.logmsg(l_module_name,
607 'Gross Weight IS NULL');
608 END IF;
609
610 x_accepted_del_id.EXTEND;
611 x_accepted_del_id(x_accepted_del_id.COUNT) := p_delivery_tab(i).entity_id;
612 COMMIT; --release lock
613
614 GOTO loop_end;
615 END IF;
616
617 --if delivery weight uom is not setup, then cannot proceed
618 IF (p_delivery_tab(i).weight_uom IS NULL OR l_global_param_info.GU_WEIGHT_UOM IS NULL) THEN
619 ROLLBACK TO delivery_record;
620
621 IF l_debug_on THEN
622 wsh_debug_sv.logmsg(l_module_name,
623 'Weight UOM IS NULL');
624 END IF;
625
626 IF l_debug_on THEN
627 WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit log_del_splitter_exception',WSH_DEBUG_SV.C_PROC_LEVEL);
628 END IF;
629
630 log_del_splitter_exception
631 (p_delivery_rec => p_delivery_tab(i),
632 p_content_weight => 0, --passing 0 here because delivery UOM is NULL, dunno what weight would be.
633 p_weight_limit => l_parameter_info.MAX_GROSS_WEIGHT,
634 p_exception_name => 'WSH_OTM_DEL_SPLIT_FAIL',
635 x_return_status => l_return_status);
636
637 IF l_debug_on THEN
638 WSH_DEBUG_SV.logmsg(l_module_name, 'return status from log_del_splitter_exception: ' || l_return_status);
639 END IF;
640
641 IF (l_return_status IN (WSH_UTIL_CORE.G_RET_STS_ERROR, WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR)) THEN
642 l_num_error := l_num_error+1;
643 ELSIF (l_return_status = WSH_UTIL_CORE.G_RET_STS_WARNING) THEN
644 l_num_warn := l_num_warn+1;
645 END IF;
646
647 x_rejected_del_id.EXTEND;
648 x_rejected_del_id(x_rejected_del_id.COUNT) := p_delivery_tab(i).entity_id;
649 GOTO loop_end;
650
651 END IF;
652
653 --convert the max gross weight to delivery's weight UOM
654 IF (l_global_param_info.GU_WEIGHT_UOM <> p_delivery_tab(i).weight_uom) THEN
655 l_parameter_info.MAX_GROSS_WEIGHT := WSH_WV_UTILS.convert_uom(
656 from_uom => l_global_param_info.GU_WEIGHT_UOM,
657 to_uom => p_delivery_tab(i).weight_uom,
658 quantity => l_parameter_info.MAX_GROSS_WEIGHT);
659 END IF;
660
661 l_content_weight := 0;
662
663 --checking if the delivery content weight sum is over the limit
664 IF (NVL(l_delivery_info.wv_frozen_flag, 'N') = 'N') THEN
665 --if delivery is not frozen, then weight is same
666 l_content_weight := NVL(p_delivery_tab(i).gross_weight, 0);
667 END IF;
668
669 --if frozen weight or delivery is oversized then need to get the detail weights
670 IF (NVL(p_delivery_tab(i).gross_weight, 0) > l_parameter_info.MAX_GROSS_WEIGHT
671 OR l_delivery_info.wv_frozen_flag = 'Y') THEN
672
673 l_line_and_container_weight.DELETE;
674 l_line_and_container_id.DELETE;
675
676 OPEN c_get_delivery_line_and_cont(p_delivery_tab(i).entity_id, p_delivery_tab(i).weight_uom);
677 FETCH c_get_delivery_line_and_cont
678 BULK COLLECT INTO l_line_and_container_weight,
679 l_line_and_container_id;
680
681 IF l_debug_on THEN
682 wsh_debug_sv.log(l_module_name,
683 'l_line_and_container_weight.count:',
684 l_line_and_container_weight.count);
685 wsh_debug_sv.log(l_module_name,
686 'l_line_and_container_id.count:',
687 l_line_and_container_id.count);
688 wsh_debug_sv.log(l_module_name,
689 'wv_frozen_flag:',
690 l_delivery_info.wv_frozen_flag);
691 END IF;
692
693 --if delivery weight is frozen, need to sum up the content weight
694 IF (l_delivery_info.wv_frozen_flag = 'Y'
695 AND l_line_and_container_weight.COUNT > 0) THEN
696
697 z := l_line_and_container_weight.FIRST;
698
699 --looping over the detail weights to sum it up.
700 WHILE z IS NOT NULL LOOP
701
702 l_content_weight := l_content_weight + l_line_and_container_weight(z);
703
704 IF l_debug_on THEN
705 wsh_debug_sv.log(l_module_name, 'z', z);
706 wsh_debug_sv.log(l_module_name, 'l_content_weight', l_content_weight);
707 END IF;
708
709 z:= l_line_and_container_weight.NEXT(z);
710
711 END LOOP;
712 END IF;
713 CLOSE c_get_delivery_line_and_cont;
714
715 IF l_debug_on THEN
716 wsh_debug_sv.log(l_module_name,
717 'Converted Max Gross Weight',
718 l_parameter_info.max_gross_weight);
719 wsh_debug_sv.log(l_module_name,
720 'delivery content weight sum',
721 l_content_weight);
722 END IF;
723 END IF;
724
725 --accept all under sized deliveries
726 IF (NVL(p_delivery_tab(i).gross_weight, 0) <= l_parameter_info.MAX_GROSS_WEIGHT
727 AND l_content_weight <= l_parameter_info.MAX_GROSS_WEIGHT) THEN
728
729 x_accepted_del_id.EXTEND;
730 x_accepted_del_id(x_accepted_del_id.COUNT) := p_delivery_tab(i).entity_id;
731 COMMIT; --release lock
732 GOTO loop_end;
733 END IF;
734
735 --if split flag is off but oversized, reject it.
736 IF (p_autosplit_flag IS NULL OR p_autosplit_flag = 'N') THEN
737
738 ROLLBACK TO delivery_record;
739
740 IF l_debug_on THEN
741 wsh_debug_sv.logmsg(l_module_name,
742 'Autosplit flag is off but delivery weight or delivery detail weight sum is over the weight limits');
743 END IF;
744
745 IF l_debug_on THEN
746 WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit log_del_splitter_exception',WSH_DEBUG_SV.C_PROC_LEVEL);
747 END IF;
748
749 log_del_splitter_exception
750 (p_delivery_rec => p_delivery_tab(i),
751 p_content_weight => l_content_weight,
752 p_weight_limit => l_parameter_info.MAX_GROSS_WEIGHT,
753 p_exception_name => 'WSH_OTM_DEL_OVERSIZED',
754 x_return_status => l_return_status);
755
756 IF l_debug_on THEN
757 WSH_DEBUG_SV.logmsg(l_module_name, 'return status from log_del_splitter_exception: ' || l_return_status);
758 END IF;
759
760 IF (l_return_status IN (WSH_UTIL_CORE.G_RET_STS_ERROR, WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR)) THEN
761 l_num_error := l_num_error+1;
762 ELSIF (l_return_status = WSH_UTIL_CORE.G_RET_STS_WARNING) THEN
763 l_num_warn := l_num_warn+1;
764 END IF;
765
766 x_rejected_del_id.EXTEND;
767 x_rejected_del_id(x_rejected_del_id.COUNT) := p_delivery_tab(i).entity_id;
768
769 GOTO loop_end;
770 END IF;
771
772 --split flag Yes but delivery weight and detail weight sum doesn't match and either delivery weight or detail
773 --weight sum is over the limit, reject
774 IF ((NVL(p_delivery_tab(i).gross_weight, 0) <= l_parameter_info.MAX_GROSS_WEIGHT
775 AND l_content_weight > l_parameter_info.MAX_GROSS_WEIGHT)
776 OR (NVL(p_delivery_tab(i).gross_weight, 0) > l_parameter_info.MAX_GROSS_WEIGHT
777 AND l_content_weight <> NVL(p_delivery_tab(i).gross_weight, 0))) THEN
778
779 ROLLBACK TO delivery_record;
780
781 IF l_debug_on THEN
782 wsh_debug_sv.logmsg(l_module_name,
783 'Content Weight sum does not equal delivery gross weight');
784 END IF;
785
786 IF l_debug_on THEN
787 WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit log_del_splitter_exception',WSH_DEBUG_SV.C_PROC_LEVEL);
788 END IF;
789
790 log_del_splitter_exception
791 (p_delivery_rec => p_delivery_tab(i),
792 p_content_weight => l_content_weight,
793 p_weight_limit => l_parameter_info.MAX_GROSS_WEIGHT,
794 p_exception_name => 'WSH_OTM_DEL_SPLIT_FAIL',
795 x_return_status => l_return_status);
796
797 IF l_debug_on THEN
798 WSH_DEBUG_SV.logmsg(l_module_name, 'return status from log_del_splitter_exception: ' || l_return_status);
799 END IF;
800
801 IF (l_return_status IN (WSH_UTIL_CORE.G_RET_STS_ERROR, WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR)) THEN
802 l_num_error := l_num_error+1;
803 ELSIF (l_return_status = WSH_UTIL_CORE.G_RET_STS_WARNING) THEN
804 l_num_warn := l_num_warn+1;
805 END IF;
806
807 x_rejected_del_id.EXTEND;
808 x_rejected_del_id(x_rejected_del_id.COUNT) := p_delivery_tab(i).entity_id;
809 GOTO loop_end;
810 END IF;
811
812 --if delivery is content firm, cannot split
813 IF (p_delivery_tab(i).content_firm IN ('F', 'Y')) THEN
814
815 ROLLBACK TO delivery_record;
816
817 IF l_debug_on THEN
818 wsh_debug_sv.logmsg(l_module_name,
819 'Delivery is content firm');
820 END IF;
821
822 IF l_debug_on THEN
823 WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit log_del_splitter_exception',WSH_DEBUG_SV.C_PROC_LEVEL);
824 END IF;
825
826 log_del_splitter_exception
827 (p_delivery_rec => p_delivery_tab(i),
828 p_content_weight => l_content_weight,
829 p_weight_limit => l_parameter_info.MAX_GROSS_WEIGHT,
830 p_exception_name => 'WSH_OTM_DEL_SPLIT_FAIL',
831 x_return_status => l_return_status);
832
833 IF l_debug_on THEN
834 WSH_DEBUG_SV.logmsg(l_module_name, 'return status from log_del_splitter_exception: ' || l_return_status);
835 END IF;
836
837 IF (l_return_status IN (WSH_UTIL_CORE.G_RET_STS_ERROR, WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR)) THEN
838 l_num_error := l_num_error+1;
839 ELSIF (l_return_status = WSH_UTIL_CORE.G_RET_STS_WARNING) THEN
840 l_num_warn := l_num_warn+1;
841 END IF;
842
843 x_rejected_del_id.EXTEND;
844 x_rejected_del_id(x_rejected_del_id.COUNT) := p_delivery_tab(i).entity_id;
845 GOTO loop_end;
846
847 END IF;
848
849 --if one detail/LPN (entity that has no parent but the delivery) is larger than the limit, can't split
850 IF l_line_and_container_weight(l_line_and_container_weight.LAST) > l_parameter_info.MAX_GROSS_WEIGHT THEN
851
852 ROLLBACK TO delivery_record;
853
854 IF l_debug_on THEN
855 wsh_debug_sv.logmsg(l_module_name,
856 'Delivery line weight greater than the gross weight limit');
857 END IF;
858
859 IF l_debug_on THEN
860 WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit log_del_splitter_exception',WSH_DEBUG_SV.C_PROC_LEVEL);
861 END IF;
862
863 log_del_splitter_exception
864 (p_delivery_rec => p_delivery_tab(i),
865 p_content_weight => l_content_weight,
866 p_weight_limit => l_parameter_info.MAX_GROSS_WEIGHT,
867 p_exception_name => 'WSH_OTM_DEL_SPLIT_FAIL',
868 x_return_status => l_return_status);
869
870 IF l_debug_on THEN
871 WSH_DEBUG_SV.logmsg(l_module_name, 'return status from log_del_splitter_exception: ' || l_return_status);
872 END IF;
873
874 IF (l_return_status IN (WSH_UTIL_CORE.G_RET_STS_ERROR, WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR)) THEN
875 l_num_error := l_num_error+1;
876 ELSIF (l_return_status = WSH_UTIL_CORE.G_RET_STS_WARNING) THEN
877 l_num_warn := l_num_warn+1;
878 END IF;
879
880 x_rejected_del_id.EXTEND;
881 x_rejected_del_id(x_rejected_del_id.COUNT) := p_delivery_tab(i).entity_id;
882 GOTO loop_end;
883
884 END IF;
885
886 OPEN c_is_delivery_assigned(p_delivery_tab(i).entity_id);
887 FETCH c_is_delivery_assigned INTO l_temp_num;
888 CLOSE c_is_delivery_assigned;
889
890 --If delivery has trip, no split
891 IF (l_temp_num IS NOT NULL AND l_temp_num > 0) THEN
892
893 ROLLBACK TO delivery_record;
894
895 IF l_debug_on THEN
896 wsh_debug_sv.logmsg(l_module_name,
897 'Delivery is assigned to trip');
898 END IF;
899
900 IF l_debug_on THEN
901 WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit log_del_splitter_exception',WSH_DEBUG_SV.C_PROC_LEVEL);
902 END IF;
903
904 log_del_splitter_exception
905 (p_delivery_rec => p_delivery_tab(i),
906 p_content_weight => l_content_weight,
907 p_weight_limit => l_parameter_info.MAX_GROSS_WEIGHT,
908 p_exception_name => 'WSH_OTM_DEL_SPLIT_FAIL',
909 x_return_status => l_return_status);
910
911 IF l_debug_on THEN
912 WSH_DEBUG_SV.logmsg(l_module_name, 'return status from log_del_splitter_exception: ' || l_return_status);
913 END IF;
914
915 IF (l_return_status IN (WSH_UTIL_CORE.G_RET_STS_ERROR, WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR)) THEN
916 l_num_error := l_num_error+1;
917 ELSIF (l_return_status = WSH_UTIL_CORE.G_RET_STS_WARNING) THEN
918 l_num_warn := l_num_warn+1;
919 END IF;
920
921 x_rejected_del_id.EXTEND;
922 x_rejected_del_id(x_rejected_del_id.COUNT) := p_delivery_tab(i).entity_id;
923 GOTO loop_end;
924
925 END IF;
926
927 --delivery with pick released/pick confirmed delivery lines cannot be split
928 l_line_status.DELETE;
929 OPEN c_get_delivery_line(p_delivery_tab(i).entity_id);
930 FETCH c_get_delivery_line BULK COLLECT INTO l_line_status;
931 CLOSE c_get_delivery_line;
932
933 IF l_debug_on THEN
934 wsh_debug_sv.log(l_module_name, 'l_line_status count', l_line_status.COUNT);
935 END IF;
936
937 IF (l_line_status.COUNT > 0) THEN
938
939 ROLLBACK TO delivery_record;
940
941 IF l_debug_on THEN
942 wsh_debug_sv.logmsg(l_module_name,
943 'Delivery line is pick released or pick confirmed.');
944 END IF;
945
946 IF l_debug_on THEN
947 WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit log_del_splitter_exception',WSH_DEBUG_SV.C_PROC_LEVEL);
948 END IF;
949
950 log_del_splitter_exception
951 (p_delivery_rec => p_delivery_tab(i),
952 p_content_weight => l_content_weight,
953 p_weight_limit => l_parameter_info.MAX_GROSS_WEIGHT,
954 p_exception_name => 'WSH_OTM_DEL_SPLIT_FAIL',
955 x_return_status => l_return_status);
956
957 IF l_debug_on THEN
958 WSH_DEBUG_SV.logmsg(l_module_name, 'return status from log_del_splitter_exception: ' || l_return_status);
959 END IF;
960
961 IF (l_return_status IN (WSH_UTIL_CORE.G_RET_STS_ERROR, WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR)) THEN
962 l_num_error := l_num_error+1;
963 ELSIF (l_return_status = WSH_UTIL_CORE.G_RET_STS_WARNING) THEN
964 l_num_warn := l_num_warn+1;
965 END IF;
966
967 x_rejected_del_id.EXTEND;
968 x_rejected_del_id(x_rejected_del_id.COUNT) := p_delivery_tab(i).entity_id;
969 GOTO loop_end;
970
971 END IF;
972
973 l_current_weight := 0;
974
975 --Splitting the delivery by delivery detail weights
976 --looping over all the top most detail/LPNs in the delivery
977 j := l_line_and_container_id.FIRST;
978 WHILE j IS NOT NULL LOOP
979
980 --try to fill up as many detail/LPN as possible within the weight limit.
981 IF (l_current_weight + l_line_and_container_weight(j)) <= l_parameter_info.MAX_GROSS_WEIGHT THEN
982
983 l_current_weight := l_current_weight + l_line_and_container_weight(j);
984
985 ELSE
986
987 --unassign all the delivery lines that's outside the limit
988 l_temp_ids.DELETE;
989
990 --looping over all the top most detail/LPNs from z to the end of the list
991 --to create a list of ids to unassign from delivery
992 z := j;
993 WHILE z IS NOT NULL LOOP
994
995 l_temp_ids(l_temp_ids.COUNT+1) := l_line_and_container_id(z);
996 z:= l_line_and_container_id.NEXT(z);
997
998 END LOOP;
999
1000 IF l_debug_on THEN
1001 WSH_DEBUG_SV.logmsg(l_module_name,
1002 'Calling program unit WSH_DELIVERY_DETAILS_ACTIONS.Unassign_multiple_details',
1003 WSH_DEBUG_SV.C_PROC_LEVEL);
1004 END IF;
1005
1006 WSH_DELIVERY_DETAILS_ACTIONS.unassign_multiple_details(
1007 p_rec_of_detail_ids => l_temp_ids,
1008 p_from_delivery => 'Y',
1009 p_from_container => 'N',
1010 p_action_prms => l_action_prms,
1011 x_return_status => l_return_status);
1012
1013 IF l_debug_on THEN
1014 WSH_DEBUG_SV.logmsg(l_module_name, 'return status from WSH_DELIVERY_DETAILS_ACTIONS.UNASSIGN_MULTIPLE_DETAILS: ' || l_return_status);
1015 END IF;
1016
1017 IF (l_return_status IN (WSH_UTIL_CORE.G_RET_STS_ERROR, WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR)) THEN
1018
1019 ROLLBACK TO delivery_record;
1020
1021 l_num_error := l_num_error+1;
1022
1023 --reject failed delivery
1024 IF l_debug_on THEN
1025 wsh_debug_sv.logmsg(l_module_name, 'Error during unassign delivery details');
1026 END IF;
1027
1028 IF l_debug_on THEN
1029 WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit log_del_splitter_exception',WSH_DEBUG_SV.C_PROC_LEVEL);
1030 END IF;
1031
1032 log_del_splitter_exception
1033 (p_delivery_rec => p_delivery_tab(i),
1034 p_content_weight => l_content_weight,
1035 p_weight_limit => l_parameter_info.MAX_GROSS_WEIGHT,
1036 p_exception_name => 'WSH_OTM_DEL_SPLIT_FAIL',
1037 x_return_status => l_return_status);
1038
1039 IF l_debug_on THEN
1040 WSH_DEBUG_SV.logmsg(l_module_name, 'return status from log_del_splitter_exception: ' || l_return_status);
1041 END IF;
1042
1043 x_rejected_del_id.EXTEND;
1044 x_rejected_del_id(x_rejected_del_id.COUNT) := p_delivery_tab(i).entity_id;
1045 GOTO loop_end;
1046
1047 ELSIF (l_return_status = WSH_UTIL_CORE.G_RET_STS_WARNING) THEN
1048 l_num_warn := l_num_warn+1;
1049 END IF;
1050
1051 l_new_weight := 0;
1052 l_curr_del_split_list(1) := p_delivery_tab(i).entity_id;
1053 l_curr_del_split_name(1) := p_delivery_tab(i).entity_name;
1054
1055 l_current_ids.DELETE;
1056
1057 --now loop through the list of top most details/LPNs from j to the end to figure out
1058 --which details/LPNs can be put into new deliveries
1059 k := j;
1060 WHILE k IS NOT NULL LOOP
1061
1062 --assign as many details/LPNs as possible under the weight limit for the new delivery
1063 IF (l_new_weight + l_line_and_container_weight(k)) <= l_parameter_info.MAX_GROSS_WEIGHT THEN
1064
1065 l_new_weight := l_new_weight + l_line_and_container_weight(k);
1066 l_current_ids(l_current_ids.COUNT+1) := l_line_and_container_id(k);
1067
1068 ELSE
1069 --if no more can be added to the new delivery, create the delivery based on the list l_current_ids
1070
1071 l_temp_ids.DELETE;
1072
1073 IF l_debug_on THEN
1074 WSH_DEBUG_SV.logmsg(l_module_name,
1075 'Calling program unit WSH_DELIVERY_DETAILS_GRP.AUTOCREATE_DELIVERIES',
1076 WSH_DEBUG_SV.C_PROC_LEVEL);
1077 END IF;
1078
1079 WSH_DELIVERY_DETAILS_GRP.autocreate_deliveries(
1080 p_api_version_number => 1.0,
1081 p_init_msg_list => FND_API.G_FALSE,
1082 p_commit => FND_API.G_FALSE,
1083 p_caller => l_action_prms.caller,
1084 x_return_status => l_return_status ,
1085 x_msg_count => l_msg_count,
1086 x_msg_data => l_msg_data,
1087 p_line_rows => l_current_ids,
1088 p_group_by_header_flag => 'N',
1089 x_del_rows => l_temp_ids);
1090
1091 IF l_debug_on THEN
1092 WSH_DEBUG_SV.logmsg(l_module_name, 'return status from WSH_DELIVERY_DETAILS_GRP.AUTOCREATE_DELIVERIES: ' || l_return_status);
1093 END IF;
1094
1095 IF (l_return_status IN (WSH_UTIL_CORE.G_RET_STS_ERROR, WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR)) THEN
1096
1097 ROLLBACK TO delivery_record;
1098
1099 l_num_error := l_num_error+1;
1100 --reject failed delivery
1101 IF l_debug_on THEN
1102 wsh_debug_sv.logmsg(l_module_name, 'Error during autocreate delivery');
1103 END IF;
1104
1105 IF l_debug_on THEN
1106 WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit log_del_splitter_exception',WSH_DEBUG_SV.C_PROC_LEVEL);
1107 END IF;
1108
1109 log_del_splitter_exception
1110 (p_delivery_rec => p_delivery_tab(i),
1111 p_content_weight => l_content_weight,
1112 p_weight_limit => l_parameter_info.MAX_GROSS_WEIGHT,
1113 p_exception_name => 'WSH_OTM_DEL_SPLIT_FAIL',
1114 x_return_status => l_return_status);
1115
1116 IF l_debug_on THEN
1117 WSH_DEBUG_SV.logmsg(l_module_name, 'return status from log_del_splitter_exception: ' || l_return_status);
1118 END IF;
1119
1120 x_rejected_del_id.EXTEND;
1121 x_rejected_del_id(x_rejected_del_id.COUNT) := p_delivery_tab(i).entity_id;
1122 GOTO loop_end;
1123
1124 ELSIF (l_return_status = WSH_UTIL_CORE.G_RET_STS_WARNING) THEN
1125 l_num_warn := l_num_warn+1;
1126 END IF;
1127
1128 z := l_temp_ids.FIRST;
1129
1130 WHILE z IS NOT NULL LOOP
1131
1132 l_new_delivery_name := WSH_NEW_DELIVERIES_PVT.get_name(l_temp_ids(z)); --newly created delivery
1133
1134 --logging tms exception for the new delivery
1135 IF l_debug_on THEN
1136 WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit log_del_splitter_exception',WSH_DEBUG_SV.C_PROC_LEVEL);
1137 END IF;
1138
1139 log_del_splitter_exception
1140 (p_delivery_rec => p_delivery_tab(i),
1141 p_content_weight => NULL,
1142 p_weight_limit => NULL,
1143 p_new_delivery_name => l_new_delivery_name,
1144 p_new_delivery_id => l_temp_ids(z),
1145 p_exception_name => 'WSH_OTM_DEL_SPLIT_CHILD',
1146 x_return_status => l_return_status);
1147
1148 IF l_debug_on THEN
1149 WSH_DEBUG_SV.logmsg(l_module_name, 'return status from log_del_splitter_exception: ' || l_return_status);
1150 END IF;
1151
1152 IF (l_return_status IN (WSH_UTIL_CORE.G_RET_STS_ERROR, WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR)) THEN
1153
1154 ROLLBACK TO delivery_record;
1155 IF l_debug_on THEN
1156 wsh_debug_sv.logmsg(l_module_name, 'Error during log delivery exception');
1157 END IF;
1158
1159 l_num_error := l_num_error+1;
1160 x_rejected_del_id.EXTEND;
1161 x_rejected_del_id(x_rejected_del_id.COUNT) := p_delivery_tab(i).entity_id;
1162 GOTO loop_end;
1163 ELSIF (l_return_status = WSH_UTIL_CORE.G_RET_STS_WARNING) THEN
1164 l_num_warn := l_num_warn+1;
1165 END IF;
1166
1167 l_curr_del_split_list(l_curr_del_split_list.COUNT+1) := l_temp_ids(z);
1168 l_curr_del_split_name(l_curr_del_split_name.COUNT+1) := l_new_delivery_name;
1169
1170 z := l_temp_ids.NEXT(z);
1171
1172 END LOOP; --l_temp_ids
1173
1174 l_new_weight := l_line_and_container_weight(k);
1175 l_current_ids.DELETE;
1176 l_current_ids(l_current_ids.COUNT+1) := l_line_and_container_id(k);
1177
1178 END IF;
1179 k:= l_line_and_container_id.NEXT(k);
1180
1181 END LOOP; -- FOR k in j..l_line_and_container_id.last
1182
1183 --create the last delivery after all the list gone over and new deliveries created, at least 1 more
1184 --line/LPN should be left under the limit.
1185 l_temp_ids.DELETE;
1186
1187 IF l_debug_on THEN
1188 WSH_DEBUG_SV.logmsg(l_module_name,
1189 'Calling program unit WSH_DELIVERY_DETAILS_GRP.AUTOCREATE_DELIVERIES',
1190 WSH_DEBUG_SV.C_PROC_LEVEL);
1191 END IF;
1192
1193 WSH_DELIVERY_DETAILS_GRP.autocreate_deliveries(
1194 p_api_version_number => 1.0,
1195 p_init_msg_list => FND_API.G_FALSE,
1196 p_commit => FND_API.G_FALSE,
1197 p_caller => l_action_prms.caller,
1198 x_return_status => l_return_status ,
1199 x_msg_count => l_msg_count,
1200 x_msg_data => l_msg_data,
1201 p_line_rows => l_current_ids,
1202 p_group_by_header_flag => 'N',
1203 x_del_rows => l_temp_ids);
1204
1205 IF l_debug_on THEN
1206 WSH_DEBUG_SV.logmsg(l_module_name, 'return status from WSH_DELIVERY_DETAILS_GRP.AUTOCREATE_DELIVERIES: ' || l_return_status);
1207 END IF;
1208
1209 IF (l_return_status IN (WSH_UTIL_CORE.G_RET_STS_ERROR, WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR)) THEN
1210
1211 ROLLBACK TO delivery_record;
1212
1213 l_num_error := l_num_error+1;
1214
1215 --reject failed delivery
1216 IF l_debug_on THEN
1217 wsh_debug_sv.logmsg(l_module_name, 'Error during autocreate delivery');
1218 END IF;
1219
1220 IF l_debug_on THEN
1221 WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit log_del_splitter_exception',WSH_DEBUG_SV.C_PROC_LEVEL);
1222 END IF;
1223
1224 log_del_splitter_exception
1225 (p_delivery_rec => p_delivery_tab(i),
1226 p_content_weight => l_content_weight,
1227 p_weight_limit => l_parameter_info.MAX_GROSS_WEIGHT,
1228 p_exception_name => 'WSH_OTM_DEL_SPLIT_FAIL',
1229 x_return_status => l_return_status);
1230
1231 IF l_debug_on THEN
1232 WSH_DEBUG_SV.logmsg(l_module_name, 'return status from log_del_splitter_exception: ' || l_return_status);
1233 END IF;
1234
1235 x_rejected_del_id.EXTEND;
1236 x_rejected_del_id(x_rejected_del_id.COUNT) := p_delivery_tab(i).entity_id;
1237 GOTO loop_end;
1238
1239 ELSIF (l_return_status = WSH_UTIL_CORE.G_RET_STS_WARNING) THEN
1240 l_num_warn := l_num_warn+1;
1241 END IF;
1242
1243 z := l_temp_ids.FIRST;
1244
1245 WHILE z IS NOT NULL LOOP
1246
1247 l_new_delivery_name := WSH_NEW_DELIVERIES_PVT.get_name(l_temp_ids(z)); --newly created delivery
1248
1249 --log tms exceptions for the new delivery
1250 IF l_debug_on THEN
1251 WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit log_del_splitter_exception',WSH_DEBUG_SV.C_PROC_LEVEL);
1252 END IF;
1253
1254 log_del_splitter_exception
1255 (p_delivery_rec => p_delivery_tab(i),
1256 p_content_weight => NULL,
1257 p_weight_limit => NULL,
1258 p_new_delivery_name => l_new_delivery_name,
1259 p_new_delivery_id => l_temp_ids(z),
1260 p_exception_name => 'WSH_OTM_DEL_SPLIT_CHILD',
1261 x_return_status => l_return_status);
1262
1263 IF l_debug_on THEN
1264 WSH_DEBUG_SV.logmsg(l_module_name, 'return status from log_del_splitter_exception: ' || l_return_status);
1265 END IF;
1266
1267 IF (l_return_status IN (WSH_UTIL_CORE.G_RET_STS_ERROR, WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR)) THEN
1268
1269 ROLLBACK TO delivery_record;
1270
1271 IF l_debug_on THEN
1272 wsh_debug_sv.logmsg(l_module_name, 'Error during log delivery exception');
1273 END IF;
1274
1275 l_num_error := l_num_error+1;
1276 x_rejected_del_id.EXTEND;
1277 x_rejected_del_id(x_rejected_del_id.COUNT) := p_delivery_tab(i).entity_id;
1278 GOTO loop_end;
1279 ELSIF (l_return_status = WSH_UTIL_CORE.G_RET_STS_WARNING) THEN
1280 l_num_warn := l_num_warn+1;
1281 END IF;
1282
1283 l_curr_del_split_list(l_curr_del_split_list.COUNT+1) := l_temp_ids(z);
1284 l_curr_del_split_name(l_curr_del_split_name.COUNT+1) := l_new_delivery_name;
1285
1286 z := l_temp_ids.NEXT(z);
1287
1288 END LOOP; --l_temp_ids
1289
1290 l_count := l_curr_del_split_list.COUNT;
1291
1292 IF l_debug_on THEN
1293 WSH_DEBUG_SV.logmsg(l_module_name,
1294 l_curr_del_split_name(1) || ' is split into '
1295 || l_count || ' deliveries.');
1296
1297 z := l_curr_del_split_list.FIRST;
1298
1299 WHILE z IS NOT NULL LOOP
1300
1301 WSH_DEBUG_SV.log(l_module_name, 'Delivery ' || z, l_curr_del_split_list(z));
1302 WSH_DEBUG_SV.log(l_module_name, 'Delivery Name ' || z, l_curr_del_split_name(z));
1303 z:= l_curr_del_split_list.NEXT(z);
1304
1305 END LOOP;
1306 END IF;
1307
1308 --after the delivery is split, need to log exception on delivery to inform the user
1309 --generating exception message based on number of deliveries split.
1310 IF (l_count > 6) THEN
1311 l_exception_name := 'WSH_OTM_DEL_SPLIT_LARGE';
1312 FND_MESSAGE.SET_NAME('WSH', 'WSH_OTM_DELIVERY_SPLIT_LARGE');
1313 ELSE
1314 l_exception_name := 'WSH_OTM_DEL_SPLIT';
1315 FND_MESSAGE.SET_NAME('WSH', 'WSH_OTM_DELIVERY_SPLIT');
1316 END IF;
1317
1318 FND_MESSAGE.SET_TOKEN('PARENT_DELIVERY_NAME', l_curr_del_split_name(1));
1319
1320 --setting child delivery name 1-5 in the message
1321 FOR z IN 2..6 LOOP
1322 IF (l_count >= z) THEN
1323 FND_MESSAGE.SET_TOKEN('CHILD_DELIVERY_NAME' || to_char(z-1), l_curr_del_split_name(z));
1324 ELSE
1325 FND_MESSAGE.SET_TOKEN('CHILD_DELIVERY_NAME' || to_char(z-1), NULL);
1326 END IF;
1327 END LOOP;
1328
1329 IF l_debug_on THEN
1330 WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit log_del_splitter_exception',WSH_DEBUG_SV.C_PROC_LEVEL);
1331 END IF;
1332
1333 log_del_splitter_exception
1334 (p_delivery_rec => p_delivery_tab(i),
1335 p_content_weight => NULL,
1336 p_weight_limit => NULL,
1337 p_exception_message => FND_MESSAGE.get,
1338 p_exception_name => l_exception_name,
1339 x_return_status => l_return_status);
1340
1341 IF l_debug_on THEN
1342 WSH_DEBUG_SV.logmsg(l_module_name, 'return status from log_del_splitter_exception: ' || l_return_status);
1343 END IF;
1344
1345 IF (l_return_status IN (WSH_UTIL_CORE.G_RET_STS_ERROR, WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR)) THEN
1346
1347 ROLLBACK TO delivery_record;
1348
1349 --reject failed delivery
1350 IF l_debug_on THEN
1351 wsh_debug_sv.logmsg(l_module_name, 'Error during log delivery exception');
1352 END IF;
1353
1354 l_num_error := l_num_error+1;
1355 x_rejected_del_id.EXTEND;
1356 x_rejected_del_id(x_rejected_del_id.COUNT) := p_delivery_tab(i).entity_id;
1357 GOTO loop_end;
1358 ELSIF (l_return_status = WSH_UTIL_CORE.G_RET_STS_WARNING) THEN
1359 l_num_warn := l_num_warn+1;
1360 END IF;
1361
1362 z := l_curr_del_split_list.FIRST;
1363
1364 WHILE z IS NOT NULL LOOP
1365
1366 x_accepted_del_id.EXTEND;
1367 x_accepted_del_id(x_accepted_del_id.COUNT) := l_curr_del_split_list(z);
1368
1369 z:= l_curr_del_split_list.NEXT(z);
1370
1371 END LOOP;
1372
1373 l_curr_del_split_list.DELETE;
1374 l_curr_del_split_name.DELETE;
1375 l_current_ids.DELETE; --remove current autocreate ids
1376
1377 EXIT; --end current delivery loop;
1378
1379 END IF;
1380 j:= l_line_and_container_id.NEXT(j);
1381
1382 END LOOP; --FOR j
1383 COMMIT; --release lock
1384 i:= p_delivery_tab.NEXT(i);
1385 GOTO next;
1386
1387 <<loop_end>>
1388 i:= p_delivery_tab.NEXT(i);
1389
1390 <<next>>
1391 NULL;
1392 END LOOP;
1393
1394 IF (l_num_error > 0 AND l_num_error = p_delivery_tab.COUNT) THEN
1395 x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
1396 ELSIF (l_num_error > 0 OR l_num_warn > 0) THEN
1397 x_return_status := WSH_UTIL_CORE.G_RET_STS_WARNING;
1398 ELSE
1399 x_return_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
1400 END IF;
1401 --
1402 -- Debug Statements
1403 --
1404 IF l_debug_on THEN
1405 WSH_DEBUG_SV.log(l_module_name, 'l_num_error', l_num_error);
1406 WSH_DEBUG_SV.log(l_module_name, 'l_num_warn', l_num_warn);
1407 WSH_DEBUG_SV.log(l_module_name, 'x_return_status', x_return_status);
1408 WSH_DEBUG_SV.pop(l_module_name);
1409 END IF;
1410 --
1411 EXCEPTION
1412 --
1413 WHEN FND_API.G_EXC_ERROR THEN
1414
1415 ROLLBACK TO delivery_record;
1416 x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
1417 --
1418 IF (c_get_delivery_line_and_cont%ISOPEN) THEN
1419 CLOSE c_get_delivery_line_and_cont;
1420 END IF;
1421 --
1422 IF (c_is_delivery_assigned%ISOPEN) THEN
1423 CLOSE c_is_delivery_assigned;
1424 END IF;
1425 --
1426 IF c_get_delivery_line%ISOPEN THEN
1427 CLOSE c_get_delivery_line;
1428 END IF;
1429 --
1430 IF l_debug_on THEN
1431 WSH_DEBUG_SV.logmsg(l_module_name,
1432 'FND_API.G_EXC_ERROR exception has occured ',
1433 WSH_DEBUG_SV.C_EXCEP_LEVEL);
1434 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:FND_API.G_EXC_ERROR');
1435 END IF;
1436 --
1437 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1438 --
1439 ROLLBACK TO delivery_record;
1440 x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR ;
1441 --
1442 IF (c_get_delivery_line_and_cont%ISOPEN) THEN
1443 CLOSE c_get_delivery_line_and_cont;
1444 END IF;
1445 --
1446 IF (c_is_delivery_assigned%ISOPEN) THEN
1447 CLOSE c_is_delivery_assigned;
1448 END IF;
1449 --
1450 IF c_get_delivery_line%ISOPEN THEN
1451 CLOSE c_get_delivery_line;
1452 END IF;
1453 --
1454 IF l_debug_on THEN
1455 WSH_DEBUG_SV.logmsg(l_module_name,
1456 'Unexpected error has occured. Oracle error message is '||
1457 SQLERRM,WSH_DEBUG_SV.C_UNEXPEC_ERR_LEVEL);
1458 WSH_DEBUG_SV.pop(l_module_name,
1459 'EXCEPTION:FND_API.G_EXC_UNEXPECTED_ERROR');
1460 END IF;
1461 --
1462
1463 WHEN NO_DATA_FOUND THEN
1464
1465 ROLLBACK TO delivery_record;
1466 FND_MESSAGE.Set_Name('WSH','WSH_DEL_NOT_FOUND');
1467 x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
1468 --
1469 IF (c_get_delivery_line_and_cont%ISOPEN) THEN
1470 CLOSE c_get_delivery_line_and_cont;
1471 END IF;
1472 --
1473 IF (c_is_delivery_assigned%ISOPEN) THEN
1474 CLOSE c_is_delivery_assigned;
1475 END IF;
1476 --
1477 IF c_get_delivery_line%ISOPEN THEN
1478 CLOSE c_get_delivery_line;
1479 END IF;
1480 --
1481 WSH_UTIL_CORE.ADD_MESSAGE(x_return_status,l_module_name);
1482 --
1483 IF l_debug_on THEN
1484 WSH_DEBUG_SV.logmsg(l_module_name,
1485 'NO_DATA_FOUND exception has occured.',
1486 WSH_DEBUG_SV.C_EXCEP_LEVEL);
1487 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:NO_DATA_FOUND');
1488 END IF;
1489 --
1490
1491 WHEN OTHERS THEN
1492
1493 ROLLBACK TO delivery_record;
1494 x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR;
1495 --
1496 IF (c_get_delivery_line_and_cont%ISOPEN) THEN
1497 CLOSE c_get_delivery_line_and_cont;
1498 END IF;
1499 --
1500 IF (c_is_delivery_assigned%ISOPEN) THEN
1501 CLOSE c_is_delivery_assigned;
1502 END IF;
1503 --
1504 IF c_get_delivery_line%ISOPEN THEN
1505 CLOSE c_get_delivery_line;
1506 END IF;
1507 --
1508 WSH_UTIL_CORE.DEFAULT_HANDLER(
1509 'WSH_DELIVERY_SPLITTER_PKG.DELIVERY_SPLITTER', l_module_name);
1510 IF l_debug_on THEN
1511 WSH_DEBUG_SV.logmsg(l_module_name,
1512 'Unexpected error has occured. Oracle error message is '||
1513 SQLERRM,WSH_DEBUG_SV.C_UNEXPEC_ERR_LEVEL);
1514 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:OTHERS');
1515 END IF;
1516
1517 END DELIVERY_SPLITTER;
1518
1519
1520 END WSH_DELIVERY_SPLITTER_PKG;
[Home] [Help]