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