[Home] [Help]
PACKAGE BODY: APPS.WSH_TRIP_CONSOLIDATION
Source
1 PACKAGE BODY WSH_TRIP_CONSOLIDATION as
2 /* $Header: WSHTRCOB.pls 120.1 2007/01/05 00:18:12 anxsharm noship $ */
3
4 /**
5 * This is a private procedure that loops through the input table of
6 * delivery records and selects only those whose indexes exist in
7 * p_IndexTab table
8 **/
9 PROCEDURE BuildDeliveryTable(p_IndexTab IN WSH_UTIL_CORE.ID_TAB_TYPE,
10 p_DeliveryTab IN WSH_NEW_DELIVERIES_PVT.Delivery_Attr_Tbl_Type,
11 x_deliveryTab IN OUT NOCOPY WSH_NEW_DELIVERIES_PVT.Delivery_Attr_Tbl_Type) IS
12 --
13 l_debugOn BOOLEAN;
14 l_moduleName CONSTANT VARCHAR2(100) := 'wsh.plsql.' || G_PKG_NAME || '.BuildDeliveryTable';
15 --
16 BEGIN
17 --
18 l_debugOn := WSH_DEBUG_INTERFACE.g_debug;
19 --
20 IF l_debugOn IS NULL THEN
21 l_debugOn := wsh_debug_sv.is_debug_enabled;
22 END IF;
23 --
24 IF l_debugOn THEN
25 wsh_debug_sv.push(l_moduleName);
26 wsh_debug_sv.log(l_moduleName, 'p_IndexTab.COUNT', p_IndexTab.COUNT);
27 wsh_debug_sv.log(l_moduleName, 'p_DeliveryTab.COUNT', p_DeliveryTab.COUNT);
28 END IF;
29 --
30 FOR j IN p_IndexTab.FIRST..p_IndexTab.LAST LOOP
31 --
32 IF l_debugOn THEN
33 wsh_debug_sv.log(l_moduleName, 'Index', p_IndexTab(j));
34 wsh_Debug_sv.log(l_moduleName, 'Delivery at this Index',
35 p_deliveryTab(p_IndexTab(j)).delivery_id);
36 END IF;
37 --
38 x_DeliveryTab(x_DeliveryTab.COUNT+1) := p_DeliveryTab(p_IndexTab(j));
39 --
40 END LOOP;
41 --
42 IF l_debugOn THEN
43 wsh_debug_sv.log(l_moduleName, 'x_DeliveryTab.COUNT', x_DeliveryTab.COUNT);
44 wsh_debug_sv.pop(l_moduleName);
45 END IF;
46 --
47 EXCEPTION
48 --
49 WHEN OTHERS THEN
50 --
51 wsh_util_core.default_handler('WSH_TRIP_CONSOLIDATION.BuildDeliveryTable');
52 --
53 IF l_debugOn THEN
54 wsh_debug_sv.log(l_moduleName, 'Unexpected error',
55 SUBSTRB(SQLERRM, 1, 200));
56 wsh_debug_sv.pop(l_moduleName);
57 END IF;
58 --
59 RAISE;
60 --
61 END BuildDeliveryTable;
62
63
64 PROCEDURE Create_Consolidated_Trips(
65 p_deliv_status IN VARCHAR2,
66 p_pickup_start IN DATE,
67 p_pickup_end IN DATE,
68 p_dropoff_start IN DATE,
69 p_dropoff_end IN DATE,
70 p_ship_from_org_id IN NUMBER,
71 p_customer_id IN NUMBER,
72 p_ship_to_location IN VARCHAR2,
73 p_ship_method_code IN VARCHAR2,
74 p_grp_ship_method IN VARCHAR2,
75 p_grp_ship_from IN VARCHAR2,
76 p_max_num_deliveries IN NUMBER,
77 x_TotDeliveries OUT NOCOPY NUMBER,
78 x_SuccessDeliv OUT NOCOPY NUMBER,
79 x_Trips OUT NOCOPY NUMBER,
80 x_return_status OUT NOCOPY VARCHAR2) IS
81 --
82 v_Query VARCHAR2(32767);
83 v_RetSts VARCHAR2(1);
84 i NUMBER;
85 v_DelivRec t_DelivRec;
86 e_HashError EXCEPTION;
87 c_Deliv t_Cursor_ref;
88 l_DelTab WSH_NEW_DELIVERIES_PVT.Delivery_Attr_Tbl_Type;
89 l_DeliveryTab WSH_NEW_DELIVERIES_PVT.Delivery_Attr_Tbl_Type;
90 v_DelRec WSH_NEW_DELIVERIES_PVT.Delivery_Rec_Type;
91 l_num_warnings NUMBER;
92 l_num_errors NUMBER;
93 v_HashTab HashTable;
94 v_DelivTab DelivTable;
95 l_action_prms WSH_DELIVERIES_GRP.action_parameters_rectype;
96 l_defaults_rec wsh_deliveries_grp.default_parameters_rectype;
97 l_deliv_out_rec WSH_DELIVERIES_GRP.Delivery_Action_Out_Rec_Type;
98 l_useDeliv VARCHAR2(1);
99 l_msg_count NUMBER;
100 l_msg_data VARCHAR2(32767);
101 --
102 l_debugOn BOOLEAN;
103 l_moduleName CONSTANT VARCHAR2(100) := 'wsh.plsql.' || G_PKG_NAME || '.Create_Consolidated_Trips';
104 --
105 BEGIN
106 --
107 l_debugOn := WSH_DEBUG_INTERFACE.g_debug;
108 --
109 IF l_debugOn IS NULL THEN
110 l_debugOn := wsh_debug_sv.is_debug_enabled;
111 END IF;
112 --
113 IF l_debugOn THEN
114 wsh_debug_sv.push(l_moduleName);
115 wsh_debug_sv.log(l_moduleName, 'p_grp_ship_from', p_grp_ship_from);
116 wsh_debug_sv.log(l_moduleName, 'p_grp_ship_method', p_grp_ship_method);
117 wsh_debug_sv.log(l_moduleName, 'p_max_num_deliveries', p_max_num_deliveries);
118 END IF;
119 --
120 BuildQuery(
121 p_deliv_status => p_deliv_status,
122 p_pickup_start => p_pickup_start,
123 p_pickup_end => p_pickup_end,
124 p_dropoff_start => p_dropoff_start,
125 p_dropoff_end => p_dropoff_end,
126 p_ship_from_org_id => p_ship_from_org_id,
127 p_customer_id => p_customer_id,
128 p_ship_to_location => p_ship_to_location,
129 p_ship_method_code => p_ship_method_code,
130 x_Query => v_Query,
131 x_return_status => v_RetSts);
132 --
133 IF l_debugOn THEN
134 wsh_debug_sv.logmsg(l_moduleName, 'After build query');
135 END IF;
136 --
137 wsh_util_core.api_post_call(
138 p_return_status => v_RetSts,
139 x_num_warnings => l_num_warnings,
140 x_num_errors => l_num_errors);
141 --
142 WSH_UTIL_CORE.OpenDynamicCursor(c_Deliv, v_Query, g_BindVarTab);
143 --
144 i := 1;
145 --
146 WHILE FetchDelivery(c_Deliv, v_delivRec) LOOP
147 --{
148 CreateAssignHashValue(
149 p_grp_ship_from => p_grp_ship_from,
150 p_grp_ship_method => p_grp_ship_method,
151 x_del_rec => v_delivRec,
152 x_HashTable => v_HashTab,
153 x_RetSts => v_RetSts,
154 x_UseDeliv => l_useDeliv);
155 --
156 wsh_util_core.api_post_call(
157 p_return_status => v_RetSts,
158 x_num_warnings => l_num_warnings,
159 x_num_errors => l_num_errors);
160 --
161 IF v_RetSts = WSH_UTIL_CORE.g_RET_STS_SUCCESS AND l_useDeliv = 'Y' THEN
162 v_DelivTab(i) := v_DelivRec;
163 i := i + 1;
164 END IF;
165 --}
166 END LOOP;
167 --
168 IF l_debugOn THEN
169 wsh_debug_sv.log(l_moduleName, 'Total number of deliveries selected', v_DelivTab.COUNT);
170 wsh_debug_sv.log(l_moduleName, 'Total number of unique hash values', v_HashTab.COUNT);
171 END IF;
172 --
173 i := v_HashTab.FIRST;
174 WHILE i IS NOT NULL LOOP
175 --{
176 IF l_debugOn THEN
177 wsh_debug_sv.logmsg(l_moduleName, '=========================================================');
178 wsh_debug_sv.log(l_moduleName, 'v_HashTab(' || i || ').hashString', v_HashTab(i).hashString);
179 END IF;
180 --
181 l_DelTab.DELETE;
182 --
183 FOR j IN 1..v_DelivTab.COUNT LOOP
184 --{
185 IF v_DelivTab(j).hash_value = i THEN
186 --
187 BuildDelivRec(v_DelivTab(j), v_DelRec, v_RetSts);
188 --
189 wsh_util_core.api_post_call(
190 p_return_status => v_RetSts,
191 x_num_warnings => l_num_warnings,
192 x_num_errors => l_num_errors);
193 --
194 l_DelTab(l_DelTab.COUNT + 1) := v_DelRec;
195 --
196 END IF;
197 --}
198 END LOOP;
199 --
200 IF l_debugOn THEN
201 wsh_debug_sv.log(l_moduleName, 'number of deliveries in this batch', l_DelTab.COUNT);
202 END IF;
203 --
204 IF l_DelTab.COUNT > 0 THEN
205 --{
206 BEGIN
207 --{
208 SAVEPOINT BEFORE_DELGRP_API;
209 --
210 l_action_prms.caller := 'WSH_TRCON';
211 l_action_prms.action_code := 'TRIP-CONSOLIDATION';
212 l_action_prms.phase := 1;
213 l_action_prms.maxDelivs := p_max_num_deliveries;
214 --
215 WSH_DELIVERIES_GRP.Delivery_Action(
216 p_api_version_number => 1.0,
217 p_init_msg_list => FND_API.G_FALSE,
218 p_commit => FND_API.G_FALSE,
219 p_action_prms => l_action_prms,
220 p_rec_attr_tab => l_DelTab,
221 x_delivery_out_rec => l_deliv_out_rec,
222 x_defaults_rec => l_defaults_rec,
223 x_return_status => v_RetSts,
224 x_msg_count => l_msg_count,
225 x_msg_data => l_msg_data);
226 --
227 wsh_util_core.api_post_call(
228 p_return_status => v_RetSts,
229 x_num_warnings => l_num_warnings,
230 x_num_errors => l_num_errors);
231 --
232 IF v_RetSts = WSH_UTIL_CORE.G_RET_STS_WARNING THEN
233 --{
234 IF l_debugOn THEN
235 wsh_debug_sv.logmsg(l_moduleName, 'Delivery_Action - warning');
236 wsh_debug_sv.log(l_moduleName, 'l_deliv_out_rec.valid_ids_tab.COUNT',
237 l_deliv_out_rec.valid_ids_tab.COUNT);
238 END IF;
239 --
240 IF l_deliv_out_rec.valid_ids_tab.COUNT > 0 AND
241 l_action_prms.phase = 1 THEN
242 --{
243 l_deliveryTab.DELETE;
244 BuildDeliveryTable(p_IndexTab => l_deliv_out_rec.valid_ids_tab,
245 p_deliveryTab => l_DelTab,
246 x_DeliveryTab => l_DeliveryTab);
247 l_action_prms.phase := 2;
248 --
249 IF l_debugOn THEN
250 wsh_debug_sv.log(l_moduleName, 'l_deliveryTab.COUNT',
251 l_deliveryTab.COUNT);
252 wsh_debug_sv.logmsg(l_moduleName, 'Second call to Delivery_Action');
253 --
254 END IF;
255 --
256 WSH_DELIVERIES_GRP.Delivery_Action
257 (
258 p_api_version_number => 1.0,
259 p_init_msg_list => FND_API.G_FALSE,
260 p_commit => FND_API.G_FALSE,
261 p_action_prms => l_action_prms,
262 p_rec_attr_tab => l_DeliveryTab,
263 x_delivery_out_rec => l_deliv_out_rec,
264 x_defaults_rec => l_defaults_rec,
265 x_return_status => v_RetSts,
266 x_msg_count => l_msg_count,
267 x_msg_data => l_msg_data
268 );
269 --
270 wsh_util_core.api_post_call(
271 p_return_status => v_RetSts,
272 x_num_warnings => l_num_warnings,
273 x_num_errors => l_num_errors);
274 --}
275 END IF;
276 --}
277 END IF;
278 --
279 EXCEPTION
280 --
281 WHEN FND_API.G_EXC_ERROR THEN
282 --
283 ROLLBACK TO BEFORE_DELGRP_API;
284 --
285 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
286 --
287 ROLLBACK TO BEFORE_DELGRP_API;
288 --
289 --}
290 END;
291 --}
292 ELSE
293 --
294 IF l_debugOn THEN
295 wsh_debug_sv.logmsg(l_moduleName, 'No deliveries selected for this hashValue');
296 END IF;
297 --
298 END IF;
299 --
300 i := v_HashTab.NEXT(i);
301 --}
302 END LOOP;
303 --
304 x_TotDeliveries := v_DelivTab.COUNT;
305 x_SuccessDeliv := g_SuccDelivs;
306 x_Trips := g_Trips;
307 --
308 IF NVL(x_SuccessDeliv,0) = NVL(x_TotDeliveries,0) THEN
309 x_return_status := WSH_UTIL_CORE.g_RET_STS_SUCCESS;
310 ELSIF NVL(x_SuccessDeliv,0) = 0 THEN
311 x_return_status := WSH_UTIL_CORE.g_RET_STS_ERROR;
312 ELSE
313 x_return_status := WSH_UTIL_CORE.g_RET_STS_WARNING;
314 END IF;
315 --
316 IF l_debugOn THEN
317 wsh_Debug_sv.log(l_moduleName, 'Total deliveries', NVL(x_TotDeliveries, 0));
318 wsh_debug_Sv.log(l_moduleName, 'number of Successful deliveries', NVL(x_SuccessDeliv, 0));
319 wsh_debug_sv.log(l_moduleName, 'number of Trips', NVL(x_Trips, 0));
320 wsh_debug_sv.log(l_moduleName, 'Return Status', x_return_status);
321 wsh_debug_sv.pop(l_moduleName);
322 END IF;
323 --
324 EXCEPTION
325 --
326 WHEN FND_API.G_EXC_ERROR THEN
327 --
328 x_TotDeliveries := v_DelivTab.COUNT;
329 x_SuccessDeliv := NVL(g_SuccDelivs, 0);
330 x_Trips := NVL(g_Trips, 0);
331 --
332 IF x_SuccessDeliv = x_TotDeliveries THEN
333 x_return_status := WSH_UTIL_CORE.g_RET_STS_SUCCESS;
334 ELSIF x_SuccessDeliv = 0 THEN
335 x_return_status := WSH_UTIL_CORE.g_RET_STS_ERROR;
336 ELSE
337 x_return_status := WSH_UTIL_CORE.g_RET_STS_WARNING;
338 END IF;
339 --
340 IF l_debugOn THEN
341 wsh_debug_sv.pop(l_moduleName || ' - FND_API.G_EXC_ERROR');
342 END IF;
343 --
344 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
345 --
346 x_TotDeliveries := v_DelivTab.COUNT;
347 x_SuccessDeliv := NVL(g_SuccDelivs, 0);
348 x_Trips := NVL(g_Trips, 0);
349 --
350 IF x_SuccessDeliv = x_TotDeliveries THEN
351 x_return_status := WSH_UTIL_CORE.g_RET_STS_SUCCESS;
352 ELSIF x_SuccessDeliv = 0 THEN
353 x_return_status := WSH_UTIL_CORE.g_RET_STS_ERROR;
354 ELSE
355 x_return_status := WSH_UTIL_CORE.g_RET_STS_WARNING;
356 END IF;
357 --
358 IF l_debugOn THEN
359 wsh_debug_sv.pop(l_moduleName || ' - FND_API.G_EXC_UNEXP_ERROR');
360 END IF;
361 --
362 WHEN OTHERS THEN
363 --
364 x_TotDeliveries := v_DelivTab.COUNT;
365 x_SuccessDeliv := NVL(g_SuccDelivs, 0);
366 x_Trips := NVL(g_Trips, 0);
367 --
368 IF x_SuccessDeliv = x_TotDeliveries THEN
369 x_return_status := WSH_UTIL_CORE.g_RET_STS_SUCCESS;
370 ELSIF x_SuccessDeliv = 0 THEN
371 x_return_status := WSH_UTIL_CORE.g_RET_STS_ERROR;
372 ELSE
373 x_return_status := WSH_UTIL_CORE.g_RET_STS_WARNING;
374 END IF;
375 --
376 wsh_util_core.default_handler('WSH_TRIP_CONSOLIDATION.CREATE_CONSOLIDATED_TRIPS');
377 --
378 IF l_debugOn THEN
379 wsh_debug_sv.pop(l_moduleName || ' - Unknown error - ' || SQLERRM);
380 END IF;
381 --
382 RAISE;
383 --
384 END Create_Consolidated_Trips;
385
386
387 PROCEDURE BuildQuery(p_deliv_status IN VARCHAR2,
388 p_pickup_start IN DATE,
389 p_pickup_end IN DATE,
390 p_dropoff_start IN DATE,
391 p_dropoff_end IN DATE,
392 p_ship_from_org_id IN NUMBER,
393 p_customer_id IN NUMBER,
394 p_ship_to_location IN NUMBER,
395 p_ship_method_code IN VARCHAR2,
396 x_query OUT NOCOPY VARCHAR2,
397 x_return_status OUT NOCOPY VARCHAR2) IS
398 --
399 l_debugOn BOOLEAN;
400 l_moduleName CONSTANT VARCHAR2(2000) := 'wsh.plsql.' || G_PKG_NAME || '.BuildQuery';
401 v_Select VARCHAR2(1000);
402 v_From VARCHAR2(1000);
403 v_Where VARCHAR2(1000);
404 v_OrderBy VARCHAR2(1000);
405 l_msg_count NUMBER;
406 l_msg_data NUMBER;
407 --
408
409 l_gc3_is_installed VARCHAR2(1); -- OTM R12, glog proj
410
411 BEGIN
412 --
413 l_debugOn := WSH_DEBUG_INTERFACE.g_debug;
414 --
415 IF l_debugOn IS NULL THEN
416 l_debugOn := wsh_debug_sv.is_debug_enabled;
417 END IF;
418 --
419 IF l_debugOn THEN
420 wsh_debug_sv.push(l_moduleName);
421 wsh_debug_sv.log(l_moduleName, 'p_deliv_status', p_deliv_status);
422 wsh_debug_sv.log(l_moduleName, 'p_customer_id', p_customer_id);
423 wsh_debug_sv.log(l_moduleName, 'p_pickup_start', p_pickup_start);
424 wsh_debug_sv.log(l_moduleName, 'p_pickup_end', p_pickup_end);
425 wsh_debug_sv.log(l_moduleName, 'p_dropoff_start', p_dropoff_start);
426 wsh_debug_sv.log(l_moduleName, 'p_dropoff_end', p_dropoff_end);
427 wsh_debug_sv.log(l_moduleName, 'p_ship_from_org_id', p_ship_from_org_id);
428 wsh_debug_sv.log(l_moduleName, 'p_ship_to_location', p_ship_to_location);
429 wsh_debug_sv.log(l_moduleName, 'p_ship_method_code', p_ship_method_code);
430 END IF;
431 --
432 v_select := 'SELECT wnd.DELIVERY_ID, wnd.ORGANIZATION_ID, wnd.STATUS_CODE, wnd.PLANNED_FLAG, ' ||
433 ' wnd.NAME, wnd.INITIAL_PICKUP_DATE, wnd.INITIAL_PICKUP_LOCATION_ID, ' ||
434 ' wnd.ULTIMATE_DROPOFF_LOCATION_ID, wnd.ULTIMATE_DROPOFF_DATE, wnd.CUSTOMER_ID, ' ||
435 ' wnd.INTMED_SHIP_TO_LOCATION_ID, wnd.SHIP_METHOD_CODE, wnd.DELIVERY_TYPE, ' ||
436 ' wnd.CARRIER_ID, wnd.SERVICE_LEVEL, wnd.MODE_OF_TRANSPORT, ' ||
437 ' wnd.SHIPMENT_DIRECTION, wnd.PARTY_ID, wnd.SHIPPING_CONTROL, ' ||
438 ' NVL(wnd.ignore_for_planning, ''N''), NULL ';
439 --
440 v_From := ' FROM wsh_new_deliveries wnd ';
441 --
442 v_where := ' WHERE wnd.shipment_direction IN (''O'', ''IO'') AND ' ||
443 ' NOT EXISTS (SELECT 1 FROM wsh_delivery_legs ' ||
444 ' WHERE delivery_id = wnd.delivery_id) ';
445 --
446 IF p_customer_id IS NOT NULL THEN
447 --
448 v_where := v_Where || ' AND wnd.customer_id = :x_CustomerID';
449 g_BindVarTab(g_BindVarTab.COUNT + 1) := p_customer_id;
450 --
451 END IF;
452 --
453 IF p_pickup_start IS NOT NULL THEN
454 --
455 v_Where := v_Where || ' AND to_char(wnd.initial_pickup_date, ''DD/MM/YYYY HH24:MI:SS'') >= :x_PickupStart';
456 g_BindVarTab(g_BindVarTab.COUNT + 1) := to_char(p_pickup_start, 'DD/MM/YYYY HH24:MI:SS');
457 --
458 END IF;
459 --
460 IF p_pickup_end IS NOT NULL THEN
461 --
462 v_Where := v_Where || ' AND to_char(wnd.initial_pickup_date, ''DD/MM/YYYY HH24:MI:SS'') <= :x_PickupEnd';
463 g_BindVarTab(g_BindVarTab.COUNT + 1) := to_char(p_pickup_end, 'DD/MM/YYYY HH24:MI:SS');
464 --
465 END IF;
466 --
467 IF p_dropoff_start IS NOT NULL THEN
468 --
469 v_Where := v_Where || ' AND to_char(wnd.ultimate_dropoff_date,''DD/MM/YYYY HH24:MI:SS'') >= :x_DropOffStart';
470 g_BindVarTab(g_BindVarTab.COUNT + 1) := to_char(p_dropoff_start, 'DD/MM/YYYY HH24:MI:SS');
471 --
472 END IF;
473 --
474 IF p_dropoff_end IS NOT NULL THEN
475 --
476 v_Where := v_Where || ' AND to_char(wnd.ultimate_dropoff_date, ''DD/MM/YYYY HH24:MI:SS'') <= :x_DropOffEnd';
477 g_BindVarTab(g_BindVarTab.COUNT + 1) := to_char(p_dropoff_end, 'DD/MM/YYYY HH24:MI:SS');
478 --
479 END IF;
480 --
481 IF p_ship_from_org_id IS NOT NULL THEN
482 --
483 v_where := v_where || ' AND wnd.organization_id = :x_SFOrgid';
484 g_BindVarTab(g_BindVarTab.COUNT + 1) := p_ship_from_org_id;
485 --
486 END IF;
487 --
488 IF p_ship_to_location IS NOT NULL THEN
489 --
490 v_where := v_where || ' AND wnd.ultimate_dropoff_location_id = :x_shiptoLoc';
491 g_BindVarTab(g_BindVarTab.COUNT + 1) := p_ship_to_location;
492 --
493 END IF;
494 --
495 IF p_ship_method_code IS NOT NULL THEN
496 --
497 v_where := v_where || ' AND wnd.ship_method_code = :x_ShipMdcode';
498 g_BindVarTab(g_BindVarTab.COUNT + 1) := p_ship_method_code;
499 --
500 END IF;
501 --
502 IF p_deliv_status IS NOT NULL THEN
503 --
504 IF p_deliv_status = 'BOTH' THEN
505 --
506 v_where := v_where || ' AND (wnd.status_code = ''CO'' OR
507 wnd.status_code = ''OP'')';
508 --
509 ELSE
510 --
511 v_where := v_where || ' AND wnd.status_code = :x_delivStatus';
512 g_BindVarTab(g_BindVarTab.COUNT + 1) := p_deliv_status;
513 --
514 END IF;
515 --
516 END IF;
517 --
518
519 --OTM R12, glog proj, use Global Variable
520 l_gc3_is_installed := WSH_UTIL_CORE.G_GC3_IS_INSTALLED;
521
522 -- If null, call the function
523 IF l_gc3_is_installed IS NULL THEN
524 l_gc3_is_installed := WSH_UTIL_CORE.GC3_IS_INSTALLED;
525 END IF;
526 -- end of OTM R12, glog proj
527
528 IF l_gc3_is_installed = 'Y' THEN
529 -- Process Delivery should only work for Ignore for Planning Delivery
530 v_where := v_where || ' AND (wnd.ignore_for_planning = :x_ignore_for_planning)';
531 g_BindVarTab(g_BindVarTab.COUNT + 1) := 'Y';
532 END IF;
533 -- end of OTM R12, glog project
534 --
535
536 v_OrderBy := ' ORDER BY wnd.status_code, wnd.organization_id, wnd.ship_method_code';
537 --
538 x_Query := v_select || v_from || v_where || v_OrderBy;
539 x_return_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
540 --
541 IF l_debugOn THEN
542 wsh_debug_sv.log(l_moduleName, 'v_Select', v_Select);
543 wsh_debug_sv.log(l_moduleName, 'v_From', v_From);
544 wsh_debug_sv.log(l_moduleName, 'v_Where', v_Where);
545 wsh_debug_sv.log(l_moduleName, 'v_OrderBy', v_OrderBy);
546 wsh_debug_sv.log(l_moduleName, 'x_Query', x_Query);
547 wsh_debug_sv.log(l_moduleName, 'g_BindVarTab.COUNT', g_BindVarTab.COUNT);
548 wsh_debug_sv.pop(l_moduleName);
549 END IF;
550 --
551 EXCEPTION
552 --
553 WHEN OTHERS THEN
554 --
555 x_Query := NULL;
556 x_return_status := WSH_UTIL_CORE.g_RET_STS_UNEXP_ERROR;
557 --
558 wsh_util_core.default_handler('WSH_TRIP_CONSOLIDATION.CREATE_CONSOLIDATED_TRIPS');
559 --
560 IF l_debugOn THEN
561 wsh_debug_sv.log(l_moduleName, 'Unexpected error', SUBSTRB(SQLERRM, 1, 200));
562 wsh_debug_sv.pop(l_moduleName);
563 END IF;
564 --
565 RAISE;
566 --
567 END BuildQuery;
568
569
570 PROCEDURE CreateAssignHashValue(p_grp_ship_from IN VARCHAR2,
571 p_grp_ship_method IN VARCHAR2,
572 x_del_rec IN OUT NOCOPY t_DelivRec,
573 x_HashTable IN OUT NOCOPY HashTable,
574 x_RetSts OUT NOCOPY VARCHAR2,
575 x_UseDeliv OUT NOCOPY VARCHAR2) IS
576 --
577 l_debugOn BOOLEAN;
578 l_moduleName CONSTANT VARCHAR2(2000) := 'wsh.plsql.' || G_PKG_NAME || '.CreateAssignHashValue';
579 --
580 l_orgType VARCHAR2(10);
581 l_hashStr VARCHAR2(100);
582 l_hashVal NUMBER;
583 l_index NUMBER;
584 b_exists BOOLEAN;
585 l_exists BOOLEAN;
586 i NUMBER;
587 l_separator VARCHAR2(1);
588 l_num_warnings NUMBER;
589 l_num_errors NUMBER;
590 v_RetSts VARCHAR2(1);
591 l_hashSize NUMBER;
592 --
593 BEGIN
594 --
595 l_debugOn := WSH_DEBUG_INTERFACE.g_debug;
596 x_RetSts := WSH_UTIL_CORE.g_RET_STS_SUCCESS;
597 x_UseDeliv := 'Y';
598 b_exists := FALSE;
599 l_exists := FALSE;
600 l_separator := '-';
601 --
602 IF l_debugOn IS NULL THEN
603 l_debugOn := wsh_debug_sv.is_debug_enabled;
604 END IF;
605 --
606 IF l_debugOn THEN
607 wsh_debug_sv.push(l_moduleName);
608 wsh_debug_sv.log(l_moduleName, 'Delivery ID', x_del_rec.delivery_id);
609 wsh_debug_sv.log(l_moduleName, 'Organization ID',
610 x_del_rec.organization_id);
611 wsh_debug_sv.log(l_moduleName, 'Ignore For Planning',
612 x_del_rec.ignore_for_planning);
613 END IF;
614 --
615 l_orgType := WSH_UTIL_VALIDATE.Get_Org_Type(
616 p_organization_id => x_del_rec.organization_id,
617 p_delivery_id => x_del_rec.delivery_id,
618 x_return_status => v_RetSts);
619 --
620 IF l_debugOn THEN
621 wsh_debug_sv.log(l_moduleName, 'Org Type', l_orgType);
622 END IF;
623 --
624 wsh_util_core.api_post_call(
625 p_return_status => v_RetSts,
626 x_num_warnings => l_num_warnings,
627 x_num_errors => l_num_errors);
628 --
629 IF l_orgType IS NULL THEN
630 l_orgType := 'STD';
631 END IF;
632 --
633 -- Deliveries that belong to TPW/CMS orgs
634 -- are not eligible for trip consolidation
635 --
636 IF (l_orgType LIKE '%TPW%' OR l_orgType LIKE '%CMS%') THEN
637 x_UseDeliv := 'N';
638 END IF;
639 --
640 l_hashStr := l_orgType || l_separator || x_del_rec.status_code ||
641 l_separator || x_del_rec.ignore_for_planning;
642 --
643 IF p_grp_ship_from = 'Y' THEN
644 l_hashStr := l_hashStr || l_separator || x_del_rec.organization_id;
645 END IF;
646 --
647 IF p_grp_ship_method = 'Y' THEN
648 l_hashStr := l_hashStr || l_separator || x_del_rec.ship_method_code;
649 END IF;
650 --
651 l_hashVal := dbms_utility.get_hash_value(name => l_hashStr,
652 base => g_hashBase,
653 hash_size => g_hashSize);
654 --
655 IF l_debugOn THEN
656 wsh_debug_sv.log(l_moduleName, 'l_hashStr', l_hashStr);
657 wsh_debug_sv.log(l_moduleName, 'l_hashVal', l_hashVal);
658 END IF;
659 --
660 IF x_HashTable.EXISTS(l_hashVal) THEN
661 --{
662 IF l_hashStr = x_HashTable(l_hashVal).HashString THEN
663 --
664 b_exists := TRUE;
665 x_del_rec.hash_value := l_hashVal;
666 --
667 ELSE /* hash collision */
668 --{
669 IF l_debugOn THEN
670 wsh_debug_sv.logmsg(l_moduleName, 'Hash collision, so regenerating hash value');
671 END IF;
672 --
673 l_hashSize := g_HashSize;
674 --
675 WHILE NOT l_exists LOOP
676 --{
677 l_hashSize := l_hashSize + 1;
678 l_hashVal := dbms_utility.get_hash_value(name => l_hashStr,
679 base => g_hashBase,
680 hash_size => l_hashSize);
681 --
682 IF l_debugOn THEN
683 wsh_debug_sv.log(l_moduleName, 'l_hashStr', l_hashStr);
684 wsh_debug_sv.log(l_moduleName, 'l_hashVal', l_hashVal);
685 END IF;
686 --
687 IF x_HashTable.EXISTS(l_hashVal) THEN
688 --{
689 IF l_hashStr = x_HashTable(l_hashVal).HashString THEN
690 --
691 l_exists := TRUE;
692 b_exists := TRUE;
693 x_del_rec.hash_value := l_hashVal;
694 EXIT;
695 --
696 END IF;
697 --}
698 ELSE
699 --
700 b_exists := FALSE;
701 x_del_rec.hash_value := l_hashVal;
702 EXIT;
703 --
704 END IF;
705 --}
706 END LOOP;
707 --}
708 END IF;
709 --}
710 ELSE /* first entry in hash table */
711 --{
712 x_del_rec.hash_value := l_hashVal;
713 --}
714 END IF;
715 --
716 IF NOT b_exists THEN
717 x_HashTable(l_hashVal).hashString := l_hashStr;
718 END IF;
719 --
720 IF l_debugOn THEN
721 wsh_debug_sv.log(l_moduleName, 'Delivery Hash Value', x_del_rec.hash_value);
722 wsh_debug_sv.log(l_moduleName, 'Include delivery for trip creation', x_UseDeliv);
723 wsh_debug_sv.pop(l_moduleName);
724 END IF;
725 --
726 EXCEPTION
727 --
728 WHEN OTHERS THEN
729 --
730 x_RetSts := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR;
731 --
732 wsh_util_core.default_handler('WSH_TRIP_CONSOLIDATION.CreateAssignHashValue');
733 --
734 IF l_debugOn THEN
735 wsh_debug_sv.log(l_moduleName, 'Unexpected Error', SUBSTRB(SQLERRM,1,200));
736 wsh_debug_sv.pop(l_moduleName);
737 END IF;
738 --
739 RAISE;
740 --
741 END CreateAssignHashValue;
742
743
744 FUNCTION FetchDelivery(p_Deliv_ref IN t_Cursor_ref,
745 x_deliv_rec IN OUT NOCOPY t_DelivRec) RETURN BOOLEAN IS
746 --
747 l_debugOn BOOLEAN;
748 l_moduleName CONSTANT VARCHAR2(2000) := 'wsh.plsql.' || G_PKG_NAME || '.FetchDelivery';
749 --
750 BEGIN
751 --
752 l_debugOn := WSH_DEBUG_INTERFACE.g_debug;
753 --
754 IF l_debugOn IS NULL THEN
755 l_debugOn := wsh_debug_sv.is_debug_enabled;
756 END IF;
757 --
758 IF l_debugOn THEN
759 wsh_debug_sv.push(l_moduleName);
760 END IF;
761 --
762 FETCH p_Deliv_ref INTO
763 x_deliv_rec.delivery_id, x_deliv_rec.organization_id,
764 x_deliv_rec.status_code, x_deliv_rec.planned_flag, x_deliv_rec.name,
765 x_deliv_rec.initial_pickup_date, x_deliv_rec.initial_pickup_location_id,
766 x_deliv_rec.ultimate_dropoff_location_id, x_deliv_rec.ultimate_dropoff_date,
767 x_deliv_rec.customer_id, x_deliv_rec.intmed_ship_to_location_id,
768 x_deliv_rec.ship_method_code, x_deliv_rec.delivery_type,
769 x_deliv_rec.carrier_id, x_deliv_rec.service_level,
770 x_deliv_rec.mode_of_transport, x_deliv_rec.shipment_direction,
771 x_deliv_rec.party_id, x_deliv_rec.shipping_control,
772 x_deliv_rec.ignore_for_planning, x_deliv_rec.hash_value;
773 --
774 IF p_Deliv_ref%NOTFOUND THEN
775 --
776 IF l_debugOn THEN
777 wsh_debug_sv.pop(l_moduleName || ' - FALSE');
778 END IF;
779 RETURN FALSE;
780 --
781 ELSE
782 --
783 IF l_debugOn THEN
784 wsh_debug_sv.logmsg(l_moduleName, 'Fetching delivery ' || x_deliv_rec.delivery_id);
785 wsh_debug_sv.pop(l_moduleName || ' - TRUE');
786 END IF;
787 RETURN TRUE;
788 --
789 END IF;
790 --
791 EXCEPTION
792 --
793 WHEN OTHERS THEN
794 --
795 wsh_util_core.default_handler('WSH_TRIP_CONSOLIDATION.FetchDelivery');
796 --
797 IF l_debugOn THEN
798 wsh_debug_sv.logmsg(l_moduleName, 'Unexpected Error in FetchDelivery');
799 wsh_debug_sv.pop(l_moduleName);
800 END IF;
801 --
802 RAISE;
803 --
804 END FetchDelivery;
805
806
807
808 PROCEDURE BuildDelivRec(p_DelivRec IN t_DelivRec,
809 x_DelivRec IN OUT NOCOPY WSH_NEW_DELIVERIES_PVT.Delivery_Rec_Type,
810 x_RetSts OUT NOCOPY VARCHAR2) IS
811 --
812 l_debugOn BOOLEAN;
813 l_moduleName CONSTANT VARCHAR2(2000) := 'wsh.plsql.' || G_PKG_NAME || '.BuildDelivRec';
814 --
815 BEGIN
816 --
817 l_debugOn := WSH_DEBUG_INTERFACE.g_debug;
818 x_RetSts := WSH_UTIL_CORE.g_RET_STS_SUCCESS;
819 --
820 IF l_debugOn IS NULL THEN
821 l_debugOn := wsh_debug_sv.is_debug_enabled;
822 END IF;
823 --
824 IF l_debugOn THEN
825 wsh_debug_sv.push(l_moduleName);
826 END IF;
827 --
828 x_DelivRec.delivery_id := p_DelivRec.delivery_id;
829 x_DelivRec.organization_id := p_DelivRec.organization_id;
830 x_DelivRec.status_code := p_DelivRec.status_code;
831 x_DelivRec.planned_flag := p_DelivRec.planned_flag;
832 x_DelivRec.name := p_DelivRec.name;
833 x_DelivRec.initial_pickup_date := p_DelivRec.initial_pickup_date;
834 x_DelivRec.initial_pickup_location_id := p_DelivRec.initial_pickup_location_id;
835 x_DelivRec.ultimate_dropoff_location_id := p_DelivRec.ultimate_dropoff_location_id;
836 x_DelivRec.ultimate_dropoff_date := p_DelivRec.ultimate_dropoff_date;
837 x_DelivRec.customer_id := p_DelivRec.customer_id;
838 x_DelivRec.intmed_ship_to_location_id := p_DelivRec.intmed_ship_to_location_id;
839 x_DelivRec.ship_method_code := p_DelivRec.ship_method_code;
840 x_DelivRec.delivery_type := p_DelivRec.delivery_type;
841 x_DelivRec.carrier_id := p_DelivRec.carrier_id;
842 x_DelivRec.service_level := p_DelivRec.service_level;
843 x_DelivRec.mode_of_transport := p_DelivRec.mode_of_transport;
844 x_DelivRec.shipment_direction := p_DelivRec.shipment_direction;
845 x_DelivRec.party_id := p_DelivRec.party_id;
846 x_DelivRec.shipping_control := p_DelivRec.shipping_control;
847 x_DelivRec.ignore_for_planning := p_DelivRec.ignore_for_planning;
848 --
849 IF l_debugOn THEN
850 wsh_debug_sv.pop(l_moduleName);
851 END IF;
852 --
853 EXCEPTION
854 --
855 WHEN OTHERS THEN
856 --
857 x_RetSts := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR;
858 --
859 wsh_util_core.default_handler('WSH_TRIP_CONSOLIDATION.BuildDelivRec');
860 --
861 IF l_debugOn THEN
862 wsh_debug_sv.log(l_moduleName, 'Unexpected error', SUBSTRB(SQLERRM,1,200));
863 wsh_debug_sv.pop(l_moduleName);
864 END IF;
865 --
866 RAISE;
867 --
868 END BuildDelivRec;
869
870
871
872 PROCEDURE GroupDelivsIntoTrips(p_DelivGrpRec IN t_DelivGrpRec,
873 x_delOutRec OUT NOCOPY WSH_DELIVERIES_GRP.Delivery_Action_Out_Rec_Type,
874 x_return_status OUT NOCOPY VARCHAR2) IS
875 --
876 l_debugOn BOOLEAN;
877 l_moduleName CONSTANT VARCHAR2(2000) := 'wsh.plsql.' || G_PKG_NAME || '.GroupDelivsIntoTrips';
878 i NUMBER;
879 j NUMBER;
880 k NUMBER;
881 m NUMBER;
882 l_return_status VARCHAR2(1);
883 l_trip_name_tab wsh_util_core.column_tab_type;
884 l_num_warnings NUMBER;
885 l_num_errors NUMBER;
886 l_delivery_tab WSH_UTIL_CORE.ID_TAB_TYPE;
887 l_trip_tab WSH_UTIL_CORE.ID_TAB_TYPE;
888 v_Index NUMBER;
889 v_DelCount NUMBER;
890 v_TotalDelCount NUMBER;
891 v_numCalls NUMBER;
892 --
893 CURSOR c_DeliveryCount(p_tripId IN NUMBER) IS
894 SELECT count(DISTINCT delivery_id)
895 FROM wsh_trips wt, wsh_trip_stops wts1,
896 wsh_trip_stops wts2, wsh_delivery_legs wdl
897 WHERE wt.trip_id = p_tripId
898 AND wts1.stop_id = wdl.pick_up_stop_id
899 AND wts2.stop_id = wdl.drop_off_stop_id
900 AND wts1.trip_id = wt.trip_id
901 AND wts2.trip_id = wt.trip_id;
902 --
903 BEGIN
904 --
905 l_debugOn := WSH_DEBUG_INTERFACE.g_debug;
906 x_return_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
907 v_DelCount := 0;
908 v_TotalDelCount := 0;
909 v_numCalls := 0;
910 l_num_errors := 0;
911 l_num_warnings := 0;
912 --
913 IF l_debugOn IS NULL THEN
914 l_debugOn := wsh_debug_sv.is_debug_enabled;
915 END IF;
916 --
917 IF l_debugOn THEN
918 wsh_debug_sv.push(l_moduleName);
919 wsh_debug_sv.log(l_moduleName, 'p_delIDTab.COUNT', p_DelivGrpRec.deliv_IDTab.COUNT);
920 wsh_debug_sv.log(l_moduleName, 'p_max_Delivs', p_DelivGrpRec.max_Delivs);
921 --
922 wsh_debug_sv.logmsg(l_moduleName,
923 '===========Complete Set of Input Deliveries===========');
924 v_Index := p_DelivGrpRec.deliv_IDTab.FIRST;
925 WHILE v_Index IS NOT NULL LOOP
926 --
927 wsh_debug_sv.log(l_moduleName, 'Index', v_Index);
928 wsh_debug_sv.log(l_moduleName, 'Delivery Id',
929 p_DelivGrpRec.deliv_IDTab(v_Index));
930 v_Index := p_DelivGrpRec.deliv_IDTab.NEXT(v_Index);
931 --
932 END LOOP;
933 --
934 END IF;
935 --
936 i := p_DelivGrpRec.deliv_IDTab.FIRST;
937 j := 1;
938 k := 1;
939 --
940 WHILE i IS NOT NULL LOOP
941 --{
942 l_delivery_tab(j) := p_DelivGrpRec.deliv_IDTab(i);
943 --
944 IF (l_delivery_tab.COUNT = p_DelivGrpRec.max_Delivs) OR
945 (i = p_DelivGrpRec.deliv_IDTab.COUNT) THEN
946 --{
947 IF l_debugOn THEN
948 --{
949 wsh_debug_sv.log(l_moduleName, '# of delivs. passed to AutoCreate_trip_multi',l_delivery_tab.COUNT);
950 wsh_debug_sv.logmsg(l_moduleName,
951 '========Deliveries passed to Autocreate Trip==========');
952 --
953 v_Index := l_delivery_tab.FIRST;
954 WHILE v_Index IS NOT NULL LOOP
955 --
956 wsh_debug_sv.log(l_moduleName, 'Index', v_Index);
957 wsh_debug_sv.log(l_moduleName, 'Delivery Id', l_delivery_tab(v_Index));
958 v_Index := l_delivery_tab.NEXT(v_Index);
959 --
960 END LOOP;
961 --}
962 END IF;
963 --
964 BEGIN
965 --{
966 v_TotalDelCount := 0;
967 v_numCalls := v_numCalls + 1;
968 SAVEPOINT Before_Autocreate_Trip;
969 --
970 WSH_TRIPS_ACTIONS.AutoCreate_Trip_multi(
971 p_del_rows => l_delivery_tab,
972 x_trip_ids => l_trip_tab,
973 x_trip_names => l_trip_name_tab,
974 x_return_status => l_return_status);
975 --
976 wsh_util_core.api_post_call(
977 p_return_status => l_return_status,
978 x_num_warnings => l_num_warnings,
979 x_num_errors => l_num_errors);
980 --
981 FOR m IN l_trip_tab.FIRST..l_trip_tab.LAST LOOP
982 --{
983 x_delOutRec.result_id_tab(k):=l_trip_tab(m);
984 --
985 IF l_debugOn THEN
986 wsh_debug_sv.log(l_moduleName, 'Index k', k);
987 wsh_debug_sv.log(l_moduleName, 'Trip ID', x_delOutRec.result_id_tab(k));
988 END IF;
989 --
990 IF l_return_status = WSH_UTIL_CORE.G_RET_STS_WARNING THEN
991 --{
992 v_DelCount := 0;
993 OPEN c_DeliveryCount(x_delOutRec.result_id_tab(k));
994 FETCH c_DeliveryCount INTO v_DelCount;
995 CLOSE c_DeliveryCount;
996 --
997 IF l_debugOn THEN
998 wsh_debug_sv.log(l_moduleName, 'v_DelCount', v_DelCount);
999 END IF;
1000 --}
1001 ELSE
1002 v_DelCount := l_delivery_tab.COUNT;
1003 END IF;
1004 --
1005 v_TotalDelCount := v_TotalDelCount + v_DelCount;
1006 --
1007 k:=k+1;
1008 --}
1009 END LOOP;
1010 --
1011 IF l_debugOn THEN
1012 wsh_debug_sv.log(l_moduleName,'v_TotalDelCount', v_TotalDelCount);
1013 END IF;
1014 --
1015 j := 0;
1016 g_SuccDelivs := NVL(g_SuccDelivs,0) + v_TotalDelCount;
1017 g_Trips := NVL(g_Trips,0) + l_trip_tab.COUNT;
1018 x_DelOutRec.num_success_Delivs := NVL(x_DelOutRec.num_success_Delivs,0) +
1019 v_TotalDelCount;
1020 l_delivery_tab.DELETE;
1021 --
1022 IF l_debugOn THEN
1023 wsh_debug_sv.log(l_moduleName, '# of trips until now', g_Trips);
1024 wsh_debug_sv.log(l_moduleName, '# of successful delivs until now', g_SuccDelivs);
1025 wsh_debug_sv.log(l_moduleName, '# of successful delivs in this batch', x_DelOutRec.num_success_Delivs);
1026 END IF;
1027 --}
1028 EXCEPTION
1029 --{
1030 WHEN FND_API.G_EXC_ERROR THEN
1031 --
1032 j := 0;
1033 l_delivery_tab.DELETE;
1034 ROLLBACK TO Before_Autocreate_Trip;
1035 --
1036 IF l_debugOn THEN
1037 wsh_debug_sv.logmsg(l_moduleName, 'Expected Error for this set of deliveries');
1038 END IF;
1039 --
1040 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1041 --
1042 j := 0;
1043 l_delivery_tab.DELETE;
1044 ROLLBACK TO Before_Autocreate_Trip;
1045 --
1046 IF l_debugOn THEN
1047 wsh_debug_sv.logmsg(l_moduleName, 'Unexpected Error for this set of deliveries');
1048 END IF;
1049 --}
1050 END;
1051 --}
1052 END IF;
1053 --
1054 i := p_DelivGrpRec.deliv_IDTab.NEXT(i);
1055 j := j + 1;
1056 --}
1057 END LOOP;
1058 --
1059 IF l_debugOn THEN
1060 wsh_debug_Sv.log(l_moduleName, '# of calls to Autocreate Trip', v_numCalls);
1061 wsh_debug_sv.log(l_moduleName, '# errors from these calls', l_num_errors);
1062 wsh_debug_sv.log(l_moduleName, '# warnings from these calls', l_num_warnings);
1063 END IF;
1064 --
1065 IF l_num_errors > 0 AND l_num_errors = v_numCalls THEN
1066 x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
1067 ELSIF ((l_num_errors > 0 AND l_num_errors < v_numCalls) OR
1068 l_num_warnings > 0) THEN
1069 x_return_status := WSH_UTIL_CORE.G_RET_STS_WARNING;
1070 ELSE
1071 x_return_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
1072 END IF;
1073 --
1074 IF l_debugOn THEN
1075 wsh_debug_sv.log(l_moduleName, 'x_return_status', x_return_status);
1076 wsh_debug_sv.pop(l_moduleName);
1077 END IF;
1078 --
1079 EXCEPTION
1080 --
1081 WHEN FND_API.G_EXC_ERROR THEN
1082 --
1083 x_Return_Status := WSH_UTIL_CORE.G_RET_STS_ERROR;
1084 --
1085 IF l_debugOn THEN
1086 wsh_debug_sv.pop(l_moduleName || ' - FND_API.G_EXC_ERROR');
1087 END IF;
1088 --
1089 RAISE;
1090 --
1091 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1092 --
1093 x_Return_Status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR;
1094 --
1095 IF l_debugOn THEN
1096 wsh_debug_sv.pop(l_moduleName || ' - FND_API.G_EXC_UNEXPECTED_ERROR');
1097 END IF;
1098 --
1099 RAISE;
1100 --
1101 WHEN OTHERS THEN
1102 --
1103 x_Return_Status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR;
1104 --
1105 wsh_util_core.default_handler('WSH_TRIP_CONSOLIDATION.GroupDelivsIntoTrips');
1106 --
1107 IF l_debugOn THEN
1108 wsh_Debug_sv.logmsg(l_moduleName, 'Unexpected error in GroupDelivsIntoTrips' || SQLERRM);
1109 wsh_debug_sv.pop(l_moduleName);
1110 END IF;
1111 --
1112 RAISE;
1113 --
1114 END GroupDelivsIntoTrips;
1115
1116
1117
1118 END WSH_TRIP_CONSOLIDATION;