[Home] [Help]
PACKAGE BODY: APPS.WSH_DELIVERY_AUTOCREATE
Source
1 PACKAGE BODY WSH_DELIVERY_AUTOCREATE as
2 /* $Header: WSHDEAUB.pls 120.26.12010000.2 2008/08/21 15:44:56 gbhargav ship $ */
3
4 g_hash_base NUMBER := 1;
5 g_hash_size NUMBER := power(2, 25);
6
7
8 G_PKG_NAME CONSTANT VARCHAR2(50) := 'WSH_DELIVERY_AUTOCREATE';
9 -----------------------------------------------------------------------------
10 --
11 -- Procedure: Get_Group_By_Attr
12 -- Parameters: p_organization_id, x_group_by_flags, x_return_status
13 -- Description: Gets group by attributes for the delivery organization
14 -- and stores this in a temporary table for future comparison
15 -- p_delivery_id - Delivery ID
16 -- x_group_by_flags - group by attributes record
17 --
18 -----------------------------------------------------------------------------
19
20 PROCEDURE get_group_by_attr (
21 p_organization_id IN NUMBER,
22 x_group_by_flags OUT NOCOPY group_by_flags_rec_type,
23 x_return_status OUT NOCOPY VARCHAR2,
24 p_group_by_header_flag IN VARCHAR2 DEFAULT 'N') IS
25
26 default_group_by_info group_by_flags_rec_type;
27
28 l_autocreate_del_orders_flag varchar2(1);
29 l_error_code number := NULL;
30 l_error_text varchar2(2000) := NULL;
31 l_shipping_params WSH_SHIPPING_PARAMS_PVT.Parameter_Rec_Typ;
32
33 --
34 l_debug_on BOOLEAN;
35 --
36 l_module_name CONSTANT VARCHAR2(100) := 'wsh.plsql.' || G_PKG_NAME || '.' || 'GET_GROUP_BY_ATTR';
37
38 BEGIN
39 --
40 -- Debug Statements
41 --
42 --
43 l_debug_on := WSH_DEBUG_INTERFACE.g_debug;
44 --
45 IF l_debug_on IS NULL
46 THEN
47 l_debug_on := WSH_DEBUG_SV.is_debug_enabled;
48 END IF;
49 --
50 IF l_debug_on THEN
51 WSH_DEBUG_SV.push(l_module_name);
52 --
53 WSH_DEBUG_SV.log(l_module_name,'P_ORGANIZATION_ID',P_ORGANIZATION_ID);
54 END IF;
55 --
56 x_return_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
57
58 IF (p_organization_id IS NULL) THEN
59 group_by_info := default_group_by_info;
60 --
61 -- Debug Statements
62 --
63 IF l_debug_on THEN
64 WSH_DEBUG_SV.pop(l_module_name);
65 END IF;
66 --
67 RETURN;
68 END IF;
69
70 IF group_by_info_tab.exists(p_organization_id) THEN
71 group_by_info := group_by_info_tab(p_organization_id);
72 -- Bug 3575807
73 -- The grouping flags are cached. If assignment is done in the
74 -- same session as autocreate, we will use the flag for the groupby header
75 -- that we used for autocreate, however this flag is not applicable
76 -- to assignment, only to autocreate deliveries.
77 IF p_group_by_header_flag = 'N' THEN
78 group_by_info.header := 'N';
79 END IF;
80 --
81 -- Debug Statements
82 --
83 --
84 x_group_by_flags := group_by_info;
85 IF l_debug_on THEN
86 WSH_DEBUG_SV.logmsg(l_module_name, 'group_by_info.customer ' || group_by_info.customer );
87 WSH_DEBUG_SV.logmsg(l_module_name, 'group_by_info.intmed ' || group_by_info.intmed );
88 WSH_DEBUG_SV.logmsg(l_module_name, 'group_by_info.fob ' || group_by_info.fob );
89 WSH_DEBUG_SV.logmsg(l_module_name, 'group_by_info.freight_terms ' || group_by_info.freight_terms );
90 WSH_DEBUG_SV.logmsg(l_module_name, 'group_by_info.ship_method ' || group_by_info.ship_method );
91 WSH_DEBUG_SV.logmsg(l_module_name, 'group_by_info.carrier ' || group_by_info.carrier );
92 WSH_DEBUG_SV.logmsg(l_module_name, 'group_by_info.header ' || group_by_info.header );
93 WSH_DEBUG_SV.pop(l_module_name);
94 END IF;
95 RETURN;
96 END IF;
97
98 WSH_SHIPPING_PARAMS_PVT.Get(p_organization_id => p_organization_id,
99 x_param_info => l_shipping_params,
100 x_return_status => x_return_status);
101
102
103 IF (x_return_status <> WSH_UTIL_CORE.G_RET_STS_SUCCESS) THEN
104
105 FND_MESSAGE.SET_NAME('WSH','WSH_SHP_NOT_FOUND');
106 --
107 -- Debug Statements
108 --
109 IF l_debug_on THEN
110 WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_UTIL_CORE.GET_ORG_NAME',WSH_DEBUG_SV.C_PROC_LEVEL);
111 END IF;
112 --
113 FND_MESSAGE.SET_TOKEN('ORG_NAME',wsh_util_core.get_org_name(p_organization_id));
114 x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
115 wsh_util_core.add_message(x_return_status);
116 --
117 -- Debug Statements
118 --
119 IF l_debug_on THEN
120 WSH_DEBUG_SV.pop(l_module_name);
121 END IF;
122 --
123 RETURN;
124 END IF;
125
126 group_by_info.customer := l_shipping_params.GROUP_BY_CUSTOMER_FLAG;
127 group_by_info.intmed := l_shipping_params.GROUP_BY_INTMED_SHIP_TO_FLAG;
128 group_by_info.fob := l_shipping_params.GROUP_BY_FOB_FLAG;
129 group_by_info.freight_terms := l_shipping_params.GROUP_BY_FREIGHT_TERMS_FLAG;
130 group_by_info.ship_method := l_shipping_params.GROUP_BY_SHIP_METHOD_FLAG;
131 group_by_info.carrier := l_shipping_params.GROUP_BY_CARRIER_FLAG;
132 group_by_info.header := NVL(p_group_by_header_flag, l_shipping_params.AUTOCREATE_DEL_ORDERS_FLAG);
133
134 group_by_info_tab(p_organization_id) := group_by_info;
135 group_by_info_tab(p_organization_id).header := l_shipping_params.AUTOCREATE_DEL_ORDERS_FLAG;
136
137 x_group_by_flags := group_by_info;
138
139 IF l_debug_on THEN
140 WSH_DEBUG_SV.logmsg(l_module_name, 'group_by_info.customer ' || group_by_info.customer );
141 WSH_DEBUG_SV.logmsg(l_module_name, 'group_by_info.intmed ' || group_by_info.intmed );
142 WSH_DEBUG_SV.logmsg(l_module_name, 'group_by_info.fob ' || group_by_info.fob );
143 WSH_DEBUG_SV.logmsg(l_module_name, 'group_by_info.freight_terms ' || group_by_info.freight_terms );
144 WSH_DEBUG_SV.logmsg(l_module_name, 'group_by_info.ship_method ' || group_by_info.ship_method );
145 WSH_DEBUG_SV.logmsg(l_module_name, 'group_by_info.carrier ' || group_by_info.carrier );
146 WSH_DEBUG_SV.logmsg(l_module_name, 'group_by_info.header ' || group_by_info.header );
147 END IF;
148 x_return_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
149
150 --
151 -- Debug Statements
152 --
153 IF l_debug_on THEN
154 WSH_DEBUG_SV.pop(l_module_name);
155 END IF;
156 --
157 EXCEPTION
158 WHEN Others THEN
159 l_error_code := SQLCODE;
160 l_error_text := SQLERRM;
161 --
162 -- Debug Statements
163 --
164 IF l_debug_on THEN
165 WSH_DEBUG_SV.logmsg(l_module_name, 'THE UNEXPECTED ERROR FROM GET_GROUP_BY_ATTR IS ' || L_ERROR_TEXT );
166 END IF;
167 --
168 wsh_util_core.default_handler('WSH_DELIVERY_AUTOCREATE.GET_GROUP_BY_ATTR');
169 x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR;
170
171 --
172 -- Debug Statements
173 --
174 IF l_debug_on THEN
175 WSH_DEBUG_SV.logmsg(l_module_name,'Unexpected error has occured. Oracle error message is '|| SQLERRM,WSH_DEBUG_SV.C_UNEXPEC_ERR_LEVEL);
176 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:OTHERS');
177 END IF;
178 --
179 END get_group_by_attr;
180
181
182
183 -- Create_Hash: This API will create a hash_string and generate corresponding hash value based on the
184 -- grouping attributes of the input records. It will not append the ship method
185 -- code or its components to the hash string.
186 -- p_grouping_attributes: record of attributes or entity that needs hash generated.
187
188 Procedure Create_Hash(p_grouping_attributes IN OUT NOCOPY grp_attr_tab_type,
189 p_group_by_header IN varchar2,
190 p_action_code IN varchar2,
191 x_return_status OUT NOCOPY VARCHAR2) IS
192
193
194 --bmso
195 CURSOR c_detail_group (p_dd_id IN NUMBER) IS
196 SELECT NULL,
197 NULL,
198 wdd.delivery_detail_id,
199 NULL,
200 wdd.released_status,
201 NULL,
202 wdd.ship_to_location_id,
203 wdd.ship_from_location_id,
204 wdd.customer_id,
205 wdd.intmed_ship_to_location_id,
206 wdd.fob_code,
207 wdd.freight_terms_code,
208 wdd.ship_method_code,
209 wdd.carrier_id,
210 wdd.source_header_id,
211 wdd.deliver_to_location_id,
212 wdd.organization_id,
213 wdd.date_scheduled,
214 wdd.date_requested,
215 wda.delivery_id,
216 NVL(wdd.ignore_for_planning, 'N') ignore_for_planning, --J TP Release
217 NVL(wdd.line_direction,'O') line_direction, -- J-IB-NPARIKH
218 wdd.shipping_control, -- J-IB-NPARIKH
219 wdd.vendor_id, -- J-IB-NPARIKH
220 wdd.party_id, -- J-IB-NPARIKH
221 wdd.mode_of_transport,
222 wdd.service_level,
223 wdd.lpn_id,
224 wdd.inventory_item_id,
225 wdd.source_code,
226 wdd.container_flag,
227 NULL,
228 NULL,
229 NULL -- X-dock, is_xdocked_flag
230 FROM wsh_delivery_details wdd,
231 wsh_delivery_assignments_v wda
232 WHERE wdd.delivery_detail_id = p_dd_id AND
233 wdd.released_status <> 'D'AND
234 wda.delivery_detail_id = wdd.delivery_detail_id;
235
236 CURSOR c_delivery_group (p_del_id IN NUMBER) IS
237 SELECT NULL,
238 NULL,
239 wnd.delivery_id,
240 NULL,
241 wnd.status_code,
242 NULL,
243 wnd.ultimate_dropoff_location_id,
244 wnd.initial_pickup_location_id,
245 wnd.customer_id,
246 wnd.intmed_ship_to_location_id,
247 wnd.fob_code,
248 wnd.freight_terms_code,
249 wnd.ship_method_code,
250 wnd.carrier_id,
251 wnd.source_header_id,
252 NULL,
253 wnd.organization_id,
254 wnd.initial_pickup_date,
255 wnd.ultimate_dropoff_date,
256 wnd.delivery_id,
257 NVL(wnd.ignore_for_planning, 'N') ignore_for_planning, --J TP Release
258 NVL(wnd.shipment_direction,'O') line_direction, -- J-IB-NPARIKH
259 wnd.shipping_control, -- J-IB-NPARIKH
260 wnd.vendor_id, -- J-IB-NPARIKH
261 wnd.party_id, -- J-IB-NPARIKH
262 wnd.mode_of_transport,
263 wnd.service_level,
264 NULL,
265 NULL,
266 NULL,
267 NULL,
268 NULL,
269 NULL,
270 NULL -- X-dock, is_xdocked_flag
271 FROM wsh_new_deliveries wnd
272 WHERE wnd.delivery_id = p_del_id;
273
274 i NUMBER;
275 l_grouping_attributes grp_attr_rec_type;
276 l1_hash_string VARCHAR2(1000) := NULL;
277 l1_hash_value NUMBER;
278 l_group_by_header VARCHAR2(1);
279
280 l_debug_on BOOLEAN;
281 l_module_name CONSTANT VARCHAR2(100) := 'wsh.plsql.' || G_PKG_NAME || '.' || 'CREATE_HASH';
282 BEGIN
283
284 l_debug_on := WSH_DEBUG_INTERFACE.g_debug;
285 --
286 IF l_debug_on IS NULL
287 THEN
288 l_debug_on := WSH_DEBUG_SV.is_debug_enabled;
289 END IF;
290 --
291 IF l_debug_on THEN
292 WSH_DEBUG_SV.push(l_module_name);
293 WSH_DEBUG_SV.logmsg(l_module_name, 'attributes count: '|| p_grouping_attributes.count);
294 END IF;
295
296 i := p_grouping_attributes.FIRST;
297 WHILE i is NOT NULL LOOP
298
299 IF l_debug_on THEN
300 WSH_DEBUG_SV.logmsg(l_module_name, 'entity: '|| p_grouping_attributes(i).entity_type);
301 WSH_DEBUG_SV.logmsg(l_module_name, 'entity_id: '|| p_grouping_attributes(i).entity_id);
302 WSH_DEBUG_SV.logmsg(l_module_name, 'index: '||i);
303 END IF;
304
305 IF p_grouping_attributes(i).ship_to_location_id IS NULL OR p_grouping_attributes(i).ship_from_location_id IS NULL THEN
306
307 IF p_grouping_attributes(i).entity_type = 'DELIVERY'
308 AND p_grouping_attributes(i).entity_id IS NOT NULL THEN
309
310 IF l_debug_on THEN
311 WSH_DEBUG_SV.logmsg(l_module_name, 'Before Calling c_delivery_group');
312 END IF;
313
314 OPEN c_delivery_group(p_grouping_attributes(i).entity_id);
315 FETCH c_delivery_group INTO l_grouping_attributes;
316 CLOSE c_delivery_group;
317
318 ELSIF p_grouping_attributes(i).entity_type = 'DELIVERY_DETAIL'
319 AND p_grouping_attributes(i).entity_id IS NOT NULL THEN
320
321 IF l_debug_on THEN
322 WSH_DEBUG_SV.logmsg(l_module_name, 'Before Calling c_detail_group');
323 END IF;
324
325
326 OPEN c_detail_group(p_grouping_attributes(i).entity_id);
327 FETCH c_detail_group INTO l_grouping_attributes;
328 CLOSE c_detail_group;
329
330 END IF;
331
332 l_grouping_attributes.batch_id := p_grouping_attributes(i).batch_id;
333
334 ELSE
335
336 l_grouping_attributes := p_grouping_attributes(i);
337 l_grouping_attributes.line_direction := NVL(p_grouping_attributes(i).line_direction, 'O');
338 l_grouping_attributes.ignore_for_planning := NVL(p_grouping_attributes(i).ignore_for_planning, 'N');
339 END IF;
340
341
342 IF l_debug_on THEN
343 WSH_DEBUG_SV.logmsg(l_module_name, 'group_by_info.header ' || p_group_by_header );
344 END IF;
345
346 IF p_action_code = 'AUTOCREATE_DELIVERIES' THEN
347
348 l_group_by_header := p_group_by_header;
349
350 ELSE
351
352 l_group_by_header := 'N';
353
354 END IF;
355 IF l_debug_on THEN
356 WSH_DEBUG_SV.logmsg(l_module_name, 'group_by_info.header ' || l_group_by_header );
357 END IF;
358
359 get_group_by_attr (
360 p_organization_id => l_grouping_attributes.organization_id,
361 x_group_by_flags => group_by_info,
362 x_return_status => x_return_status,
363 p_group_by_header_flag => l_group_by_header);
364
365 IF l_debug_on THEN
366 WSH_DEBUG_SV.logmsg(l_module_name, 'group_by_info.header ' || group_by_info.header );
367 END IF;
368
369 --
370 -- Line direction is also a mandatory grouping attribute.
371 --
372 --
373 l1_hash_string := to_char(l_grouping_attributes.ship_to_location_id) ||'-'|| -- These three are always
374 -- in the grouping rule
375 to_char(l_grouping_attributes.ship_from_location_id) ||'-'||
376 to_char(l_grouping_attributes.organization_id)
377 --bug fix 3286811
378 ||'-'|| (l_grouping_attributes.ignore_for_planning); --J TP Release
379
380
381
382 --
383 -- J-IB-NPARIKH-{
384 --
385 -- Adding other mandatory grouping attributes which are applicable only for inbound
386 -- Attributes are:Shipping control, vendor id, party id, drop-ship customer id
387 --
388 -- For outbound, adding constants for these attributes as they are not applicable
389 --
390 IF l_grouping_attributes.line_direction IN ('O','IO')
391 THEN
392 --{
393 l1_hash_string := l1_hash_string
394 || '-' || '!!!' -- Shipping control
395 || '-' || '1' -- Vendor ID
396 || '-' || '1' -- Party ID
397 || '-' || '1'; -- Drop-ship customer ID
398 --}
399 ELSE
400 --{
401 l1_hash_string := l1_hash_string || '-' || l_grouping_attributes.shipping_control;
402 l1_hash_string := l1_hash_string || '-' || to_char(l_grouping_attributes.vendor_id);
403 l1_hash_string := l1_hash_string || '-' || to_char(l_grouping_attributes.party_id);
404 --
405 p_grouping_attributes(i).shipping_control := l_grouping_attributes.shipping_control;
406 p_grouping_attributes(i).vendor_id := l_grouping_attributes.vendor_id;
407 p_grouping_attributes(i).party_id := l_grouping_attributes.party_id;
408 --
409 IF l_grouping_attributes.line_direction = 'D'
410 THEN
411 --{
412 l1_hash_string := l1_hash_string
413 || '-' || to_char(l_grouping_attributes.customer_id);
414 p_grouping_attributes(i).customer_id := l_grouping_attributes.customer_id;
415 --}
416 ELSE
417 --{
418 l1_hash_string := l1_hash_string || '-' || '1';
419 --}
420 END IF;
421
422 --}
423 END IF;
424 --
425
426
427 -- J-IB-NPARIKH-}
428
429 p_grouping_attributes(i).ship_to_location_id := l_grouping_attributes.ship_to_location_id;
430 p_grouping_attributes(i).ship_from_location_id := l_grouping_attributes.ship_from_location_id;
431 p_grouping_attributes(i).organization_id := l_grouping_attributes.organization_id;
432 p_grouping_attributes(i).ignore_for_planning := l_grouping_attributes.ignore_for_planning;
433 p_grouping_attributes(i).line_direction := l_grouping_attributes.line_direction; -- J-IB-NPARIKH
434
435
436 IF l_grouping_attributes.line_direction IN ('O','IO') -- J-IB-NPARIKH
437 THEN
438 IF l_debug_on THEN
439 WSH_DEBUG_SV.logmsg(l_module_name, 'group_by_info.customer ' || group_by_info.customer );
440 WSH_DEBUG_SV.logmsg(l_module_name, 'group_by_info.intmed ' || group_by_info.intmed );
441 WSH_DEBUG_SV.logmsg(l_module_name, 'group_by_info.fob ' || group_by_info.fob );
442 WSH_DEBUG_SV.logmsg(l_module_name, 'group_by_info.freight_terms ' || group_by_info.freight_terms );
443 WSH_DEBUG_SV.logmsg(l_module_name, 'group_by_info.ship_method ' || group_by_info.ship_method );
444 WSH_DEBUG_SV.logmsg(l_module_name, 'group_by_info.carrier ' || group_by_info.carrier );
445 WSH_DEBUG_SV.logmsg(l_module_name, 'group_by_info.customer ' || group_by_info.header );
446 END IF;
447 --{
448 IF (group_by_info.customer = 'Y') THEN
449 l1_hash_string := l1_hash_string ||'-'||to_char(l_grouping_attributes.customer_id);
450 p_grouping_attributes(i).customer_id := l_grouping_attributes.customer_id;
451 ELSE
452 p_grouping_attributes(i).customer_id := NULL;
453 END IF;
454 IF (group_by_info.intmed = 'Y') THEN
455 l1_hash_string := l1_hash_string ||'-'||to_char(l_grouping_attributes.intmed_ship_to_location_id);
456 p_grouping_attributes(i).intmed_ship_to_location_id := l_grouping_attributes.intmed_ship_to_location_id;
457 ELSE
458 p_grouping_attributes(i).intmed_ship_to_location_id := NULL;
459 END IF;
460 IF (group_by_info.fob = 'Y') THEN
461 l1_hash_string := l1_hash_string ||'-'||l_grouping_attributes.fob_code;
462 p_grouping_attributes(i).fob_code := l_grouping_attributes.fob_code;
463 ELSE
464 p_grouping_attributes(i).fob_code := NULL;
465 END IF;
466 IF (group_by_info.freight_terms = 'Y') THEN
467 l1_hash_string := l1_hash_string ||'-'||l_grouping_attributes.freight_terms_code;
468 p_grouping_attributes(i).freight_terms_code := l_grouping_attributes.freight_terms_code;
469 ELSE
470 p_grouping_attributes(i).freight_terms_code := NULL;
471 END IF;
472 IF (group_by_info.ship_method = 'Y') THEN
473 p_grouping_attributes(i).carrier_id := l_grouping_attributes.carrier_id;
474 p_grouping_attributes(i).mode_of_transport := l_grouping_attributes.mode_of_transport;
475 p_grouping_attributes(i).service_level := l_grouping_attributes.service_level;
476 p_grouping_attributes(i).ship_method_code := l_grouping_attributes.ship_method_code;
477 ELSE
478 p_grouping_attributes(i).carrier_id := NULL;
479 p_grouping_attributes(i).mode_of_transport := NULL;
480 p_grouping_attributes(i).service_level := NULL;
481 p_grouping_attributes(i).ship_method_code := NULL;
482 END IF;
483 --}
484 END IF;
485
486 IF (NVL(p_group_by_header, group_by_info.header) = 'Y'
487 AND l_grouping_attributes.line_direction IN ('O','IO') ) THEN
488 IF l_debug_on THEN
489 WSH_DEBUG_SV.logmsg(l_module_name, 'group_by_info.header ' || NVL(p_group_by_header, group_by_info.header) );
490 END IF;
491 p_grouping_attributes(i).source_header_id := l_grouping_attributes.source_header_id;
492 ELSE
493 p_grouping_attributes(i).source_header_id := NULL;
494 END IF;
495 p_grouping_attributes(i).delivery_id := l_grouping_attributes.delivery_id;
496
497 IF p_action_code = 'AUTOCREATE_DELIVERIES' THEN
498
499 -- By default these will not be included in the hash string. They are checked
500 -- though for each match of hash whether the criteria from wsh_tpa matches
501
502 p_grouping_attributes(i).deliver_to_location_id := l_grouping_attributes.deliver_to_location_id;
503 p_grouping_attributes(i).delivery_id := l_grouping_attributes.delivery_id;
504 p_grouping_attributes(i).date_scheduled := l_grouping_attributes.date_scheduled;
505 p_grouping_attributes(i).date_requested := l_grouping_attributes.date_requested;
506
507
508 /*
509 IF l_grouping_attributes.status_code = 'C' -- J-IB-NPARIKH
510 THEN
511 --{
512 p_grouping_attributes(i).status_code := 'IT'; -- J-IB-NPARIKH
513 p_grouping_attributes(i).planned_flag := 'F'; -- J-IB-NPARIKH
514 --}
515 ELSIF l_grouping_attributes.status_code = 'L' -- J-IB-NPARIKH
516 THEN
517 --{
518 p_grouping_attributes(i).status_code := 'CL'; -- J-IB-NPARIKH
519 p_grouping_attributes(i).planned_flag := 'F'; -- J-IB-NPARIKH
520 --}
521 ELSE
522 --{
523 */
524 p_grouping_attributes(i).status_code := 'OP';
525 p_grouping_attributes(i).planned_flag := 'N';
526 --}
527 --END IF;
528
529 END IF;
530
531
532
533
534 p_grouping_attributes(i).l1_hash_string := l1_hash_string;
535
536
537 l1_hash_value := dbms_utility.get_hash_value(
538 name => l1_hash_string,
539 base => g_hash_base,
540 hash_size =>g_hash_size );
541
542
543 p_grouping_attributes(i).l1_hash_value := l1_hash_value;
544 --
545 -- Debug Statements
546
547 --
548 IF l_debug_on THEN
549 WSH_DEBUG_SV.logmsg(l_module_name, 'L1_HASH_STRING '||L1_HASH_STRING ||' VALUE '||L1_HASH_VALUE );
550 END IF;
551
552
553 --
554 -- Debug Statements
555 --
556
557 IF i = p_grouping_attributes.last THEN
558
559 exit;
560
561 END IF;
562
563 i := p_grouping_attributes.next(i);
564
565
566 END LOOP;
567
568 x_return_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
569
570 IF l_debug_on THEN
571 WSH_DEBUG_SV.pop(l_module_name);
572 END IF;
573
574 EXCEPTION
575
576 WHEN Others THEN
577 WSH_UTIL_CORE.Default_Handler('WSH_DELIVERY_AUTOCREATE.Create_Hash');
578 x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR;
579 --
580 -- Debug Statements
581 --
582 IF l_debug_on THEN
583 WSH_DEBUG_SV.logmsg(l_module_name,'Unexpected error has occured. Oracle error message is '|| SQLERRM,WSH_DEBUG_SV.C_UNEXPEC_ERR_LEVEL);
584 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:OTHERS');
585 END IF;
586
587
588
589 END Create_Hash;
590
591 -- This API will create a hash based on the grouping attributes of the delivery
592 -- and update the hash_value and hash string in wsh_new_deliveries.
593 -- p_delivery_id : Delivery that needs hash value and string to be updated.
594
595 Procedure Create_Update_Hash(p_delivery_rec IN OUT NOCOPY WSH_NEW_DELIVERIES_PVT.Delivery_Rec_Type,
596 x_return_status OUT NOCOPY varchar2) IS
597
598 l_grp_attr_tab grp_attr_tab_type;
599 l_tmp_grp_attr_tab grp_attr_tab_type;
600
601 FAIL_CREATE_HASH EXCEPTION;
602 l_debug_on BOOLEAN;
603 l_module_name CONSTANT VARCHAR2(100) := 'wsh.plsql.' || G_PKG_NAME || '.' || 'CREATE_UPDATE_HASH';
604 BEGIN
605
606 l_debug_on := WSH_DEBUG_INTERFACE.g_debug;
607 --
608 IF l_debug_on IS NULL
609 THEN
610 l_debug_on := WSH_DEBUG_SV.is_debug_enabled;
611 END IF;
612 --
613 IF l_debug_on THEN
614 WSH_DEBUG_SV.push(l_module_name);
615 END IF;
616 x_return_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
617
618 l_grp_attr_tab(1).ship_to_location_id := p_delivery_rec.ultimate_dropoff_location_id;
619 l_grp_attr_tab(1).ship_from_location_id := p_delivery_rec.initial_pickup_location_id;
620 l_grp_attr_tab(1).customer_id := p_delivery_rec.customer_id;
621 l_grp_attr_tab(1).intmed_ship_to_location_id := p_delivery_rec.intmed_ship_to_location_id;
622 l_grp_attr_tab(1).fob_code := p_delivery_rec.fob_code;
623 l_grp_attr_tab(1).freight_terms_code := p_delivery_rec.freight_terms_code;
624 l_grp_attr_tab(1).ship_method_code := p_delivery_rec.ship_method_code;
625 l_grp_attr_tab(1).carrier_id := p_delivery_rec.carrier_id;
626 l_grp_attr_tab(1).source_header_id := p_delivery_rec.source_header_id;
627 l_grp_attr_tab(1).organization_id := p_delivery_rec.organization_id;
628 l_grp_attr_tab(1).date_scheduled := p_delivery_rec.initial_pickup_date;
629 l_grp_attr_tab(1).date_requested := p_delivery_rec.ultimate_dropoff_date;
630 l_grp_attr_tab(1).ignore_for_planning := p_delivery_rec.ignore_for_planning;
631 l_grp_attr_tab(1).line_direction := p_delivery_rec.shipment_direction;
632 l_grp_attr_tab(1).shipping_control := p_delivery_rec.shipping_control;
633 l_grp_attr_tab(1).vendor_id := p_delivery_rec.vendor_id;
634 l_grp_attr_tab(1).party_id := p_delivery_rec.party_id;
635
636 Create_Hash(p_grouping_attributes => l_grp_attr_tab,
637 p_group_by_header => 'N',
638 p_action_code => NULL,
639 x_return_status => x_return_status);
640
641
642 IF x_return_status <> WSH_UTIL_CORE.G_RET_STS_SUCCESS THEN
643
644 RAISE FAIL_CREATE_HASH;
645
646 END IF;
647
648 p_delivery_rec.hash_value := l_grp_attr_tab(1).l1_hash_value;
649
650 p_delivery_rec.hash_string := l_grp_attr_tab(1).l1_hash_string;
651
652
653 IF l_debug_on THEN
654 WSH_DEBUG_SV.pop(l_module_name);
655 END IF;
656
657 EXCEPTION
658
659 WHEN FAIL_CREATE_HASH THEN
660 x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
661 FND_MESSAGE.SET_NAME('WSH','WSH_FAIL_CREATE_HASH');
662 WSH_UTIL_CORE.Add_Message(x_return_status);
663 --
664 -- Debug Statements
665 --
666 IF l_debug_on THEN
667 WSH_DEBUG_SV.logmsg(l_module_name,'FAIL_CREATE_GROUP exception has occured.',WSH_DEBUG_SV.C_EXCEP_LEVEL);
668 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:FAIL_CREATE_HASH');
669 END IF;
670 --
671
672 WHEN Others THEN
673 WSH_UTIL_CORE.Default_Handler('WSH_DELIVERY_AUTOCREATE.Create_Update_Hash');
674 x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR;
675 --
676 -- Debug Statements
677 --
678 IF l_debug_on THEN
679
680 WSH_DEBUG_SV.logmsg(l_module_name,'OTHERS exception has occured.',WSH_DEBUG_SV.C_EXCEP_LEVEL);
681 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:OTHERS');
682 END IF;
683 --
684
685
686 END Create_Update_Hash;
687
688
689 -- Create_Groupings: This API will group the attribute records together and update
690 -- the group_id of each record. Records in the same group will
691 -- share the same group_id.
692 -- p_attr_tab: Table of attributes to be grouped.
693
694
695 PROCEDURE Create_Groupings(p_attr_tab IN OUT NOCOPY grp_attr_tab_type,
696 p_group_tab IN OUT NOCOPY grp_attr_tab_type,
697 p_check_one_group varchar2,
698 p_action_code varchar2 DEFAULT NULL,
699 x_return_status out NOCOPY varchar2) IS
700
701 i NUMBER;
702 l_hashval_exists BOOLEAN;
703 l_group_index NUMBER;
704
705 MULTIPLE_GROUPS EXCEPTION;
706
707 --
708 l_debug_on BOOLEAN;
709 --
710 l_module_name CONSTANT VARCHAR2(100) := 'wsh.plsql.' || G_PKG_NAME || '.' || 'Create_Groupings';
711 --
712 BEGIN
713 --
714 -- Debug Statements
715 --
716 --
717 l_debug_on := WSH_DEBUG_INTERFACE.g_debug;
718 --
719 IF l_debug_on IS NULL
720 THEN
721 l_debug_on := WSH_DEBUG_SV.is_debug_enabled;
722 END IF;
723 --
724 IF l_debug_on THEN
725 WSH_DEBUG_SV.push(l_module_name);
726 END IF;
727 --
728 x_return_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
729
730 i := p_attr_tab.FIRST;
731 WHILE i is NOT NULL LOOP
732
733 -- X-dock
734 IF p_attr_tab(i).is_xdocked_flag = 'Y' THEN
735 -- Add this record to the group.
736 p_attr_tab(i).group_id := null;
737 goto end_of_loop;
738 END IF;
739 -- End of X-dock
740
741
742 IF NOT (NVL(p_action_code, 'X') = 'AUTOCREATE_DELIVERIES' AND p_attr_tab(i).delivery_id IS NOT NULL) THEN
743
744 l_hashval_exists := FALSE;
745 l_group_index := p_attr_tab(i).l1_hash_value;
746
747 WHILE NOT l_hashval_exists LOOP
748
749 IF p_group_tab.exists(l_group_index) THEN
750
751 -- Bugfix 5031971
752 -- 1. Replaced OR with AND for NULL condition
753 -- 2. Added code for Ship Method Code
754 IF (p_group_tab(l_group_index).l1_hash_string = p_attr_tab(i).l1_hash_string)
755 AND (((p_group_tab(l_group_index).carrier_id IS NULL) AND (p_attr_tab(i).carrier_id IS NULL))
756 OR (p_attr_tab(i).carrier_id = p_group_tab(l_group_index).carrier_id))
757 AND (((p_group_tab(l_group_index).ship_method_code IS NULL) AND (p_attr_tab(i).ship_method_code IS NULL))
758 OR (p_attr_tab(i).ship_method_code = p_group_tab(l_group_index).ship_method_code))
759 AND (((p_group_tab(l_group_index).service_level IS NULL) AND (p_attr_tab(i).service_level IS NULL))
760 OR (p_attr_tab(i).service_level = p_group_tab(l_group_index).service_level))
761 AND (((p_group_tab(l_group_index).mode_of_transport IS NULL) AND (p_attr_tab(i).mode_of_transport IS NULL))
762 OR (p_attr_tab(i).mode_of_transport = p_group_tab(l_group_index).mode_of_transport))
763 AND (((p_group_tab(l_group_index).delivery_id IS NULL)
764 OR (p_attr_tab(i).delivery_id IS NULL))
765 OR (p_attr_tab(i).delivery_id = p_group_tab(l_group_index).delivery_id))
766 AND (p_attr_tab(i).source_header_id is NULL
767 OR (p_attr_tab(i).source_header_id = p_group_tab(l_group_index).source_header_id))
768 AND (NVL(p_attr_tab(i).batch_id,-1) = NVL(p_group_tab(l_group_index).batch_id, -1))
769 THEN
770
771 -- Hash value exists, hash string/attributes match. Use this group.
772
773 l_hashval_exists := TRUE;
774
775 IF p_group_tab(l_group_index).carrier_id IS NULL
776 AND p_attr_tab(i).carrier_id IS NOT NULL
777 THEN
778
779 p_group_tab(l_group_index).carrier_id := p_attr_tab(i).carrier_id;
780
781 END IF;
782
783 IF p_group_tab(l_group_index).service_level IS NULL
784 AND p_attr_tab(i).service_level IS NOT NULL
785 THEN
786
787 p_group_tab(l_group_index).service_level := p_attr_tab(i).service_level;
788
789
790 END IF;
791
792 IF p_group_tab(l_group_index).mode_of_transport IS NULL
793 AND p_attr_tab(i).mode_of_transport IS NOT NULL
794 THEN
795
796 p_group_tab(l_group_index).mode_of_transport := p_attr_tab(i).mode_of_transport;
797
798 END IF;
799 IF p_group_tab(l_group_index).ship_method_code IS NULL
800 AND p_attr_tab(i).ship_method_code IS NOT NULL
801 THEN
802
803 p_group_tab(l_group_index).ship_method_code := p_attr_tab(i).ship_method_code;
804
805 END IF;
806
807
808 IF p_group_tab(l_group_index).delivery_id IS NULL
809 AND p_attr_tab(i).delivery_id IS NOT NULL
810 THEN
811
812 p_group_tab(l_group_index).delivery_id := p_attr_tab(i).delivery_id;
813
814 END IF;
815
816 IF p_group_tab(l_group_index).source_header_id IS NULL
817 AND p_attr_tab(i).source_header_id IS NOT NULL
818 THEN
819
820 p_group_tab(l_group_index).source_header_id := p_attr_tab(i).source_header_id;
821
822 END IF;
823
824
825 p_group_tab(l_group_index).date_scheduled := LEAST(p_attr_tab(i).date_scheduled, p_group_tab(l_group_index).date_scheduled);
826 p_group_tab(l_group_index).date_requested := GREATEST(LEAST(p_attr_tab(i).date_requested, p_group_tab(l_group_index).date_requested), p_group_tab(l_group_index).date_scheduled);
827
828
829 -- Add this record to the group.
830 p_attr_tab(i).group_id := p_group_tab(l_group_index).group_id;
831
832 IF l_debug_on THEN
833 WSH_DEBUG_SV.logmsg(l_module_name, 'Group: Hash String: '||p_group_tab(l_group_index).l1_hash_value);
834 WSH_DEBUG_SV.logmsg(l_module_name, 'service_level: '||p_group_tab(l_group_index).service_level);
835 WSH_DEBUG_SV.logmsg(l_module_name, 'mode_of_transport: '||p_group_tab(l_group_index).mode_of_transport);
836 WSH_DEBUG_SV.logmsg(l_module_name, 'ship_method_code: '||p_group_tab(l_group_index).ship_method_code);
837 WSH_DEBUG_SV.logmsg(l_module_name, 'carrier_id: '||p_group_tab(l_group_index).carrier_id);
838 WSH_DEBUG_SV.logmsg(l_module_name, 'delivery_id: '||p_group_tab(l_group_index).delivery_id);
839 WSH_DEBUG_SV.logmsg(l_module_name, 'source_header_id: '||p_group_tab(l_group_index).source_header_id);
840 WSH_DEBUG_SV.logmsg(l_module_name, 'group_id: '||p_group_tab(l_group_index).group_id);
841 WSH_DEBUG_SV.logmsg(l_module_name, 'date_scheduled: '||p_group_tab(l_group_index).date_scheduled);
842 WSH_DEBUG_SV.logmsg(l_module_name, 'date_requested: '||p_group_tab(l_group_index).date_requested);
843 END IF;
844
845 ELSE
846
847 -- Index exists but the hash strings/attributes do not match.
848 IF l_debug_on THEN
849 WSH_DEBUG_SV.logmsg(l_module_name, 'Attribute: Hash String: '||p_attr_tab(i).l1_hash_value);
850 WSH_DEBUG_SV.logmsg(l_module_name, 'service_level: '||p_attr_tab(i).service_level);
851 WSH_DEBUG_SV.logmsg(l_module_name, 'mode_of_transport: '||p_attr_tab(i).mode_of_transport);
852 WSH_DEBUG_SV.logmsg(l_module_name, 'ship_method_code: '||p_attr_tab(i).ship_method_code);
853 WSH_DEBUG_SV.logmsg(l_module_name, 'carrier_id: '||p_attr_tab(i).carrier_id);
854 WSH_DEBUG_SV.logmsg(l_module_name, 'delivery_id: '||p_attr_tab(i).delivery_id);
855 WSH_DEBUG_SV.logmsg(l_module_name, 'source_header_id: '||p_attr_tab(i).source_header_id);
856 WSH_DEBUG_SV.logmsg(l_module_name, 'batch_id: '||p_attr_tab(i).batch_id);
857 WSH_DEBUG_SV.logmsg(l_module_name, 'group_id: '||p_attr_tab(i).group_id);
858 END IF;
859
860 IF p_check_one_group = 'Y' THEN
861
862 -- We have more than one group, error out.
863
864 RAISE MULTIPLE_GROUPS;
865
866 ELSE
867
868 -- Bump up the index and continue looping.
869
870 l_group_index := l_group_index + 1;
871 l_hashval_exists := FALSE;
872
873 END IF;
874
875 END IF;
876
877 ELSE
878
879
880 -- Index does not exist. This is a new group.
881
882 IF p_check_one_group = 'Y' and p_group_tab.count <> 0 THEN
883
884 -- We have more than one group, error out.
885
886 RAISE MULTIPLE_GROUPS;
887
888 END IF;
889
890 -- Use this hash value to create a new index in the group table
891 -- and create a new group with these attributes.
892
893 p_group_tab(l_group_index) := p_attr_tab(i);
894
895 p_group_tab(l_group_index).date_requested := GREATEST(p_attr_tab(i).date_requested, p_attr_tab(i).date_scheduled);
896 -- Generate a new group id and add this record to the group.
897 --bug 7171766 created new recycle sequence
898 select WSH_MATCH_GROUP_S.nextval into p_group_tab(l_group_index).group_id from dual;
899 p_attr_tab(i).group_id := p_group_tab(l_group_index).group_id;
900
901 IF l_debug_on THEN
902 WSH_DEBUG_SV.logmsg(l_module_name, 'Group: Hash String: '||p_group_tab(l_group_index).l1_hash_value);
903 WSH_DEBUG_SV.logmsg(l_module_name, 'service_level: '||p_group_tab(l_group_index).service_level);
904 WSH_DEBUG_SV.logmsg(l_module_name, 'mode_of_transport: '||p_group_tab(l_group_index).mode_of_transport);
905 WSH_DEBUG_SV.logmsg(l_module_name, 'ship_method_code: '||p_group_tab(l_group_index).ship_method_code);
906 WSH_DEBUG_SV.logmsg(l_module_name, 'carrier_id: '||p_group_tab(l_group_index).carrier_id);
907 WSH_DEBUG_SV.logmsg(l_module_name, 'delivery_id: '||p_group_tab(l_group_index).delivery_id);
908 WSH_DEBUG_SV.logmsg(l_module_name, 'source_header_id: '||p_group_tab(l_group_index).source_header_id);
909 WSH_DEBUG_SV.logmsg(l_module_name, 'group_id: '||p_group_tab(l_group_index).group_id);
910 WSH_DEBUG_SV.logmsg(l_module_name, 'date_requested: '||p_group_tab(l_group_index).date_requested);
911 WSH_DEBUG_SV.logmsg(l_module_name, 'date_scheduled: '||p_group_tab(l_group_index).date_scheduled);
912 END IF;
913
914 l_hashval_exists := TRUE;
915
916 END IF;
917
918 END LOOP;
919
920
921 END IF;
922 IF i = p_attr_tab.last THEN
923
924 exit;
925
926 END IF;
927
928 -- Marker added for X-dock changes related to cartonization
929 <<end_of_loop>>
930 i := p_attr_tab.next(i);
931
932
933
934 END LOOP;
935
936 --
937 -- Debug Statements
938 --
939 IF l_debug_on THEN
940 WSH_DEBUG_SV.pop(l_module_name);
941 END IF;
942 --
943
944
945 EXCEPTION
946 WHEN MULTIPLE_GROUPS THEN
947 x_return_status := WSH_UTIL_CORE.G_RET_STS_WARNING;
948 FND_MESSAGE.SET_NAME('WSH','WSH_MULTIPLE_GROUPS');
949 WSH_UTIL_CORE.Add_Message(x_return_status);
950 --
951 -- Debug Statements
952 --
953 IF l_debug_on THEN
954 WSH_DEBUG_SV.logmsg(l_module_name,'MULTIPE_GROUPS exception has occured.',WSH_DEBUG_SV.C_EXCEP_LEVEL);
955 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:WSH_MULTIPE_GROUPS');
956 END IF;
957 --
958
959 WHEN Others THEN
960 WSH_UTIL_CORE.Default_Handler('WSH_DELIVERY_AUTOCREATE.Create_Groups');
961 x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR;
962 --
963 -- Debug Statements
964 --
965 IF l_debug_on THEN
966 WSH_DEBUG_SV.logmsg(l_module_name,'Unexpected error has occured. Oracle error message is '|| SQLERRM,WSH_DEBUG_SV.
967 C_UNEXPEC_ERR_LEVEL);
968 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:OTHERS');
969 END IF;
970
971 END;
972
973
974 -- Find_Matching_Groups: This API will find entities (deliveries/containers) that
975 -- match the grouping criteria of the input table of entities.
976 -- p_attr_tab: Table of entities or record of grouping criteria that need to be matched.
977 -- p_action_rec: Record of specific actions and their corresponding parameters.
978 -- check_single_grp_only: ('Y', 'N') will check only of the records can be
979 -- grouped together.
980 -- output_entity_type: ('DLVY', 'CONT') the entity type that the input records
981 -- need to be matched with.
982 -- output_format_type: Format of the output.
983 -- 'ID_TAB': table of id's of the matched entities
984 -- 'TEMP_TAB': The output will be inserted into wsh_temp (wsh_temp
985 -- needs to be cleared after this API has been used).
986 -- 'SQL_STRING': Will return a SQL query to find the matching entities
987 -- as a string and values of the variables that will
988 -- need to be bound to the string.
989 -- p_target_rec: Entity or grouping attributes that need to be matched with (if necessary)
990 -- x_matched_entities: table of ids of the matched entities
991 -- x_out_rec: Record of output values based on the actions and output format.
992 -- query_string: String to query for matching entities. The following
993 -- will have to be bound to the string before executing the query.
994 -- bind_hash_value
995 -- bind_hash_string
996 -- bind_batch_id
997 -- bind_carrier_id
998 -- bind_mode_of_transport
999 -- bind_service_level
1000 -- x_return_status: 'S', 'E', 'U'.
1001
1002 procedure Find_Matching_Groups(p_attr_tab IN OUT NOCOPY grp_attr_tab_type,
1003 p_action_rec IN action_rec_type,
1004 p_target_rec IN grp_attr_rec_type,
1005 p_group_tab IN OUT NOCOPY grp_attr_tab_type,
1006 x_matched_entities OUT NOCOPY wsh_util_core.id_tab_type,
1007 x_out_rec OUT NOCOPY out_rec_type,
1008 x_return_status OUT NOCOPY varchar2) IS
1009
1010 --Bug 5241742 Added organization_id in where clause for indexing
1011 --Bug 6074966 added Ship Method Code in all cursor for matching deliveries
1012 --bug#6467751: Added new parameter p_ship_method_grp_flag for all cursors which
1013 -- basically compares the ship method value only when ship method is part of delivery grouping.
1014 cursor c_matching_deliveries(p_hash_value in number,
1015 p_hash_string in varchar2,
1016 p_carrier_id in number,
1017 p_mode_of_transport in varchar2,
1018 p_service_level in varchar2,
1019 p_ship_method_code in varchar2,
1020 p_organization_id in number ,
1021 p_ship_method_grp_flag in varchar2 ) is
1022 select delivery_id
1023 from wsh_new_deliveries wnd
1024 where wnd.hash_value = p_hash_value
1025 and wnd.hash_string = p_hash_string
1026 and wnd.organization_id = p_organization_id
1027 and (NVL(wnd.planned_flag, 'N') = 'N')
1028 and NVL(NVL(wnd.carrier_id, p_carrier_id),-1)
1029 = NVL(NVL(p_carrier_id, wnd.carrier_id), -1)
1030 and NVL(NVL(wnd.service_level, p_service_level), -1)
1031 = NVL(NVL(p_service_level, wnd.service_level), -1)
1032 and NVL(NVL(wnd.mode_of_transport, p_mode_of_transport), -1)
1033 = NVL(NVL(p_mode_of_transport, wnd.mode_of_transport), -1)
1034 and NVL(wnd.ship_method_code,-1) = NVL(decode(p_ship_method_grp_flag,'Y',p_ship_method_code,wnd.ship_method_code),-1)
1035 and wnd.status_code in ('OP', 'SA');
1036
1037 -- 5167826 (frontported from 11.5.10 performance bug 5029788)
1038 -- new cursor to use indexes on organization_id and customer_id
1039 -- keep this in sync with cursor c_matching_deliveries above.
1040 cursor c_matching_dels_new(p_hash_value in number,
1041 p_hash_string in varchar2,
1042 p_carrier_id in number,
1043 p_mode_of_transport in varchar2,
1044 p_service_level in varchar2,
1045 p_ship_method_code in varchar2,
1046 p_organization_id in number,
1047 p_customer_id in number ,
1048 p_ship_method_grp_flag in varchar2) is
1049 select delivery_id
1050 from wsh_new_deliveries wnd
1051 where wnd.hash_value = p_hash_value
1052 and wnd.hash_string = p_hash_string
1053 and wnd.organization_id = p_organization_id
1054 and wnd.customer_id = p_customer_id
1055 and (NVL(wnd.planned_flag, 'N') = 'N')
1056 and NVL(NVL(wnd.carrier_id, p_carrier_id),-1)
1057 = NVL(NVL(p_carrier_id, wnd.carrier_id), -1)
1058 and NVL(NVL(wnd.service_level, p_service_level), -1)
1059 = NVL(NVL(p_service_level, wnd.service_level), -1)
1060 and NVL(NVL(wnd.mode_of_transport, p_mode_of_transport), -1)
1061 = NVL(NVL(p_mode_of_transport, wnd.mode_of_transport), -1)
1062 and NVL(wnd.ship_method_code,-1) = NVL(decode(p_ship_method_grp_flag,'Y',p_ship_method_code,wnd.ship_method_code),-1)
1063 and wnd.status_code in ('OP', 'SA');
1064
1065 cursor c_matching_batch(p_hash_value in number,
1066 p_hash_string in varchar2,
1067 p_batch_id in number,
1068 p_header_id number,
1069 p_carrier_id in number,
1070 p_mode_of_transport in varchar2,
1071 p_service_level in varchar2 ,
1072 p_ship_method_code in varchar2,
1073 p_ship_method_grp_flag in varchar2
1074 ) is
1075 select delivery_id
1076 from wsh_new_deliveries wnd
1077 where wnd.hash_value = p_hash_value
1078 and wnd.hash_string = p_hash_string
1079 and wnd.batch_id = p_batch_id
1080 and (NVL(wnd.planned_flag, 'N') = 'N')
1081 and NVL(wnd.source_header_id, -1) = NVL(p_header_id, -1)
1082 and NVL(NVL(wnd.carrier_id, p_carrier_id), -1) = NVL(NVL(p_carrier_id, wnd.carrier_id), -1)
1083 and NVL(NVL(wnd.service_level, p_service_level), -1) = NVL(NVL(p_service_level, wnd.service_level), -1)
1084 and NVL(NVL(wnd.mode_of_transport, p_mode_of_transport), -1) = NVL(NVL(p_mode_of_transport, wnd.mode_of_transport), -1)
1085 and NVL(wnd.ship_method_code,-1) = NVL(decode(p_ship_method_grp_flag,'Y',p_ship_method_code,wnd.ship_method_code),-1)
1086 and wnd.status_code in ('OP', 'SA');
1087
1088 --BUG 3383843
1089 CURSOR c_dlvy_for_cont(p_organization_id NUMBER, p_ship_from_loc_id NUMBER) IS
1090 select delivery_id
1091 from wsh_new_deliveries d
1092 where d.status_code IN ('OP','SA')
1093 and d.planned_flag = 'N'
1094 and NVL(p_ship_from_loc_id, nvl(d.initial_pickup_location_id, -1))
1095 = nvl(d.initial_pickup_location_id, -1)
1096 and NVL(p_organization_id, nvl(d.organization_id, -1))
1097 = nvl(d.organization_id, -1);
1098
1099 CURSOR c_check_lpn(p_delivery_detail_id IN NUMBER) IS
1100 SELECT container_flag, organization_id, ship_from_location_id, customer_id
1101 FROM wsh_delivery_details
1102 WHERE delivery_detail_id = p_delivery_detail_id;
1103
1104 l_container_flag VARCHAR2(1);
1105 l_organization_id NUMBER;
1106 l_ship_from_loc_id NUMBER;
1107 l_empty_container VARCHAR2(30) := 'N';
1108 l_return_status VARCHAR2(30);
1109 --BUG 3383843
1110
1111 l_batch_id NUMBER;
1112 l_header_id NUMBER;
1113 l_carrier_id NUMBER;
1114 l_service_level VARCHAR2(30);
1115 l_mode_of_transport VARCHAR2(30);
1116 l_ship_method_code VARCHAR2(30); --bug 6074966
1117 l_hash_value NUMBER;
1118 l_hash_string varchar2(1000);
1119 l_matched_entities wsh_util_core.id_tab_type;
1120 l_tmp_attr_tab grp_attr_tab_type;
1121 l_check_one_group varchar2(1);
1122 l_query_string varchar2(4000);
1123
1124 l_customer_id NUMBER;
1125
1126 DELIVERY_NOT_MATCH EXCEPTION;
1127 FAIL_CREATE_GROUP EXCEPTION;
1128 FAIL_CREATE_HASH EXCEPTION;
1129 INVALID_ACTION EXCEPTION;
1130 l_debug_on BOOLEAN;
1131 l_module_name CONSTANT VARCHAR2(100) := 'wsh.plsql.' || G_PKG_NAME || '.' || 'Find_Matching_Groups';
1132
1133 BEGIN
1134 l_debug_on := WSH_DEBUG_INTERFACE.g_debug;
1135 --
1136 IF l_debug_on IS NULL
1137 THEN
1138 l_debug_on := WSH_DEBUG_SV.is_debug_enabled;
1139 END IF;
1140 --
1141 IF l_debug_on THEN
1142 WSH_DEBUG_SV.push(l_module_name);
1143 WSH_DEBUG_SV.log(l_module_name, 'p_action_rec.action', p_action_rec.action);
1144 WSH_DEBUG_SV.log(l_module_name, 'p_action_rec.output_format_type', p_action_rec.output_format_type);
1145 WSH_DEBUG_SV.log(l_module_name, 'p_attr_tab count' , p_attr_tab.count);
1146 WSH_DEBUG_SV.log(l_module_name, 'p_group_tab count', p_group_tab.count);
1147 WSH_DEBUG_SV.log(l_module_name, 'p_target_rec.entity_type', p_target_rec.entity_type);
1148 END IF;
1149
1150
1151 -- Need to validate the input action.
1152
1153 IF p_action_rec.action NOT IN ('MATCH_GROUPS', 'CREATE_GROUPS', 'AUTOCREATE_DELIVERIES') THEN
1154
1155 RAISE INVALID_ACTION;
1156
1157 END IF;
1158
1159 x_return_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
1160
1161 IF p_action_rec.action = 'MATCH_GROUPS' AND p_target_rec.entity_id is NOT NULL THEN
1162
1163 p_attr_tab(p_attr_tab.FIRST - 1).entity_id := p_target_rec.entity_id;
1164 p_attr_tab(p_attr_tab.FIRST).entity_type := p_target_rec.entity_type;
1165
1166 END IF;
1167
1168 --BUG 3383843
1169 --For calls from Group API to find matching groups, empty containers need to be handled separately
1170 --First check if the line is a container. If yes, check if container is empty.
1171 IF p_action_rec.action = 'MATCH_GROUPS'
1172 AND p_attr_tab.count > 0
1173 THEN
1174 --{
1175 IF p_attr_tab(p_attr_tab.FIRST).entity_type = 'DELIVERY_DETAIL'
1176 THEN
1177 --{
1178 IF l_debug_on THEN
1179 wsh_debug_sv.log(l_module_name, 'entity id', p_attr_tab(p_attr_tab.FIRST).entity_id);
1180 END IF;
1181
1182 OPEN c_check_lpn(p_attr_tab(p_attr_tab.FIRST).entity_id);
1183 FETCH c_check_lpn INTO l_container_flag, l_organization_id,
1184 l_ship_from_loc_id, l_customer_id;
1185 CLOSE c_check_lpn;
1186
1187 IF l_debug_on THEN
1188 wsh_debug_sv.log(l_Module_name, 'Container Flag', l_container_flag);
1189 wsh_debug_sv.log(l_Module_name , 'l_organization_id', l_organization_id);
1190 wsh_debug_sv.log(l_Module_Name, 'l_ship_from_loc_id', l_ship_from_loc_id);
1191 wsh_debug_sv.log(l_Module_Name, 'l_customer_id ', l_customer_id);
1192 END IF;
1193
1194 IF nvl(l_container_flag, 'N') = 'Y'
1195 THEN
1196 --{
1197 WSH_CONTAINER_UTILITIES.Is_Empty (
1198 p_container_instance_id => p_attr_tab(p_attr_tab.FIRST).entity_id,
1199 x_empty_flag => l_empty_container,
1200 x_return_status => x_return_status);
1201
1202 IF x_return_status <> wsh_util_core.g_ret_sts_success THEN
1203 RAISE fnd_api.g_exc_error;
1204 END IF;
1205
1206 IF l_debug_on THEN
1207 wsh_debug_sv.log(l_module_name, 'l_empty_container', l_empty_container);
1208 END IF;
1209 --}
1210 END IF;
1211 --}
1212 END IF;
1213 --}
1214 END IF;
1215 --BUG 3383843
1216
1217
1218 IF l_debug_on THEN
1219 WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit Create_Hash',WSH_DEBUG_SV.C_PROC_LEVEL);
1220 END IF;
1221 Create_Hash(p_grouping_attributes => p_attr_tab,
1222 p_group_by_header => p_action_rec.group_by_header_flag,
1223 p_action_code => p_action_rec.action,
1224 x_return_status => x_return_status);
1225
1226 IF x_return_status <> WSH_UTIL_CORE.G_RET_STS_SUCCESS THEN
1227
1228 RAISE FAIL_CREATE_HASH;
1229
1230 END IF;
1231
1232 IF p_action_rec.check_single_grp = 'Y' THEN
1233
1234 l_check_one_group := 'Y';
1235
1236 ELSE
1237
1238 l_check_one_group := 'N';
1239
1240 END IF;
1241 IF l_debug_on THEN
1242 WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit Create_Groupings',WSH_DEBUG_SV.C_PROC_LEVEL);
1243 END IF;
1244
1245 Create_Groupings(p_attr_tab => p_attr_tab,
1246 p_group_tab => p_group_tab,
1247 p_check_one_group => l_check_one_group,
1248 p_action_code => p_action_rec.action,
1249 x_return_status => x_return_status);
1250
1251 IF x_return_status <> WSH_UTIL_CORE.G_RET_STS_SUCCESS THEN
1252
1253 IF x_return_status = WSH_UTIL_CORE.G_RET_STS_WARNING THEN
1254
1255 IF p_action_rec.check_single_grp = 'Y' THEN
1256
1257 -- We need to check only if the records can be grouped together.
1258
1259 x_out_rec.single_group := 'N';
1260
1261 IF l_debug_on THEN
1262 WSH_DEBUG_SV.pop(l_module_name);
1263 END IF;
1264
1265 RETURN;
1266
1267 END IF;
1268
1269 ELSE
1270
1271 RAISE FAIL_CREATE_GROUP;
1272
1273 END IF;
1274
1275 END IF;
1276
1277 IF p_action_rec.check_single_grp = 'Y' THEN
1278
1279 x_out_rec.single_group := 'Y';
1280
1281 END IF;
1282
1283
1284 IF p_target_rec.entity_type = 'DELIVERY' AND p_target_rec.entity_id IS NULL THEN
1285 --BUG 3383843
1286 --If line is an empty container, need to use a select that does not use hash values
1287 IF nvl(l_container_flag, 'N') = 'Y'
1288 AND nvl(l_empty_container, 'N') = 'Y'
1289 THEN
1290 OPEN c_dlvy_for_cont(l_organization_id, l_ship_from_loc_id);
1291 FETCH c_dlvy_for_cont BULK COLLECT INTO l_matched_entities;
1292 CLOSE c_dlvy_for_cont;
1293 --
1294 IF l_debug_on THEN
1295 wsh_debug_sv.log(l_module_name, 'Empty container match count=' || l_matched_entities.count);
1296 END IF;
1297 ELSE
1298 -- Find all matching deliveries
1299 -- Populate the following variables used to find matching deliveries.
1300 -- We assume that all the records can go into one group, so we can use
1301 -- the value of one record for common attributes.
1302
1303 l_hash_value := p_group_tab(p_group_tab.FIRST).l1_hash_value;
1304 l_hash_string := p_group_tab(p_group_tab.FIRST).l1_hash_string;
1305 l_batch_id := p_group_tab(p_group_tab.FIRST).batch_id;
1306 l_header_id := p_group_tab(p_group_tab.FIRST).source_header_id;
1307 l_carrier_id := p_group_tab(l_hash_value).carrier_id;
1308 l_service_level := p_group_tab(l_hash_value).service_level;
1309 l_mode_of_transport := p_group_tab(l_hash_value).mode_of_transport;
1310 l_ship_method_code := p_group_tab(l_hash_value).ship_method_code; --bugfix 6074966
1311
1312 --Bug 5241742 setting l_customer_id = NULL if customer is not a part of grouping criteria.
1313 get_group_by_attr (
1314 p_organization_id => l_organization_id,
1315 x_group_by_flags => group_by_info,
1316 x_return_status => x_return_status
1317 );
1318 IF l_debug_on THEN
1319 wsh_debug_sv.log(l_Module_name , 'group by ship method ', group_by_info.ship_method);
1320 END IF;
1321
1322 IF group_by_info.customer = 'N' THEN
1323 l_customer_id := NULL;
1324 END IF;
1325
1326 IF l_batch_id IS NOT NULL THEN
1327
1328 OPEN c_matching_batch(p_hash_value => l_hash_value,
1329 p_hash_string => l_hash_string,
1330 p_batch_id => l_batch_id,
1331 p_header_id => l_header_id,
1332 p_carrier_id => l_carrier_id,
1333 p_mode_of_transport => l_mode_of_transport,
1334 p_service_level => l_service_level ,
1335 p_ship_method_code => l_ship_method_code ,
1336 p_ship_method_grp_flag=>group_by_info.ship_method);
1337
1338 FETCH c_matching_batch BULK COLLECT INTO l_matched_entities;
1339 CLOSE c_matching_batch;
1340
1341 ELSE
1342 IF (l_organization_id is NOT NULL and l_customer_id is NOT NULL) THEN
1343 OPEN c_matching_dels_new(
1344 p_hash_value => l_hash_value,
1345 p_hash_string => l_hash_string,
1346 p_carrier_id => l_carrier_id,
1347 p_mode_of_transport => l_mode_of_transport,
1348 p_service_level => l_service_level,
1349 p_ship_method_code => l_ship_method_code,
1350 p_organization_id => l_organization_id,
1351 p_customer_id => l_customer_id,
1352 p_ship_method_grp_flag=>group_by_info.ship_method);
1353
1354 FETCH c_matching_dels_new BULK COLLECT INTO l_matched_entities;
1355 CLOSE c_matching_dels_new;
1356 ELSE
1357 OPEN c_matching_deliveries(
1358 p_hash_value => l_hash_value,
1359 p_hash_string => l_hash_string,
1360 p_organization_id => l_organization_id,
1361 p_carrier_id => l_carrier_id,
1362 p_mode_of_transport => l_mode_of_transport,
1363 p_service_level => l_service_level ,
1364 p_ship_method_code => l_ship_method_code,
1365 p_ship_method_grp_flag=>group_by_info.ship_method);
1366
1367 FETCH c_matching_deliveries BULK COLLECT INTO l_matched_entities;
1368 CLOSE c_matching_deliveries;
1369 END IF;
1370 END IF;
1371 END IF;
1372
1373 IF p_action_rec.output_format_type = 'TEMP_TAB' THEN
1374 -- Insert into wsh_tmp
1375
1376 delete from wsh_tmp;
1377
1378 FORALL i IN 1..l_matched_entities.count
1379 INSERT INTO wsh_tmp (id) VALUES(l_matched_entities(i));
1380
1381 x_return_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
1382
1383 IF l_debug_on THEN
1384 WSH_DEBUG_SV.pop(l_module_name);
1385 END IF;
1386
1387 RETURN;
1388
1389 ELSIF p_action_rec.output_format_type = 'ID_TAB' THEN
1390 -- Insert into PL/SQL tble
1391
1392 x_matched_entities := l_matched_entities;
1393
1394
1395 x_return_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
1396
1397 IF l_debug_on THEN
1398 WSH_DEBUG_SV.pop(l_module_name);
1399 END IF;
1400
1401 RETURN;
1402
1403 ELSIF p_action_rec.output_format_type = 'SQL_STRING' THEN
1404 -- Return the string and the variables that need to be bound.
1405
1406 l_query_string := 'select delivery_id '||
1407 'from wsh_new_deliveries wnd '||
1408 'where wnd.hash_value = :p_hash_value '||
1409 'and wnd.hash_string = :p_hash_string '||
1410 'and wnd.status_code = ''OP'' ';
1411
1412 IF l_batch_id is NOT NULL THEN
1413 l_query_string := l_query_string || ' and wnd.batch_id = :p_batch_id ';
1414 END IF;
1415 IF l_header_id is NOT NULL THEN
1416 l_query_string := l_query_string || ' and wnd.source_header_id = :p_header_id ';
1417 END IF;
1418 IF l_carrier_id is NOT NULL THEN
1419 l_query_string := l_query_string || ' and NVL(wnd.carrier_id, :p_carrier_id) = :p_carrier_id ';
1420 END IF;
1421 IF l_service_level is NOT NULL THEN
1422 l_query_string := l_query_string || ' and NVL(wnd.service_level, :p_service_level) = :p_service_level ';
1423 END IF;
1424 IF l_mode_of_transport is NOT NULL THEN
1425 l_query_string := l_query_string || ' and NVL(wnd.mode_of_transport, :p_mode_of_transport) = :p_mode_of_transport ';
1426 END IF;
1427 --bug#6467751: Need to consider the ship method value only when ship method is part of delivery grouping.
1428 IF (group_by_info.ship_method = 'Y') THEN
1429 --{
1430 IF l_ship_method_code is NOT NULL THEN
1431 l_query_string := l_query_string || ' and wnd.ship_method_code = :p_ship_method_code ';
1432 ELSE
1433 l_query_string := l_query_string || ' and wnd.ship_method_code IS NULL ';
1434 END IF;
1435 --}
1436 END IF;
1437
1438 x_out_rec.query_string := l_query_string;
1439
1440 x_out_rec.bind_hash_value := l_hash_value;
1441 x_out_rec.bind_hash_string := l_hash_string;
1442 x_out_rec.bind_batch_id := l_batch_id;
1443 x_out_rec.bind_header_id := l_header_id;
1444 x_out_rec.bind_carrier_id := l_carrier_id;
1445 x_out_rec.bind_service_level := l_service_level;
1446 x_out_rec.bind_mode_of_transport := l_mode_of_transport;
1447 x_out_rec.bind_ship_method_code := l_ship_method_code;
1448
1449 x_return_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
1450
1451 IF l_debug_on THEN
1452 WSH_DEBUG_SV.pop(l_module_name);
1453 END IF;
1454
1455 RETURN;
1456
1457
1458 END IF;
1459
1460 END IF;
1461
1462
1463 IF l_debug_on THEN
1464 WSH_DEBUG_SV.pop(l_module_name);
1465 END IF;
1466
1467 EXCEPTION
1468 WHEN fnd_api.g_exc_error THEN
1469 x_return_status := fnd_api.g_ret_sts_error;
1470 --
1471 IF l_debug_on THEN
1472 wsh_debug_sv.pop(l_module_name, 'EXCEPTION:FND_API.G_EXC_ERROR');
1473 END IF;
1474 --
1475 WHEN DELIVERY_NOT_MATCH THEN
1476 x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
1477 FND_MESSAGE.SET_NAME('WSH','WSH_DELIVERY_NOT_MATCH');
1478 WSH_UTIL_CORE.Add_Message(x_return_status);
1479 --
1480 -- Debug Statements
1481 --
1482 IF l_debug_on THEN
1483 WSH_DEBUG_SV.logmsg(l_module_name,'DELIVERY_NOT_MATCH exception has occured.',WSH_DEBUG_SV.C_EXCEP_LEVEL);
1484 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:DELIVERY_NOT_MATCH');
1485 END IF;
1486 --
1487 WHEN FAIL_CREATE_GROUP THEN
1488 x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
1489 FND_MESSAGE.SET_NAME('WSH','WSH_FAIL_CREATE_GROUP');
1490 WSH_UTIL_CORE.Add_Message(x_return_status);
1491 --
1492 -- Debug Statements
1493 --
1494 IF l_debug_on THEN
1495 WSH_DEBUG_SV.logmsg(l_module_name,'MULTIPE_GROUPS exception has occured.',WSH_DEBUG_SV.C_EXCEP_LEVEL);
1496 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:FAIL_CREATE_GROUP');
1497 END IF;
1498 --
1499 WHEN FAIL_CREATE_HASH THEN
1500 x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
1501 FND_MESSAGE.SET_NAME('WSH','WSH_FAIL_CREATE_HASH');
1502 WSH_UTIL_CORE.Add_Message(x_return_status);
1503 --
1504 -- Debug Statements
1505 --
1506 IF l_debug_on THEN
1507 WSH_DEBUG_SV.logmsg(l_module_name,'FAIL_CREATE_GROUP exception has occured.',WSH_DEBUG_SV.C_EXCEP_LEVEL);
1508
1509 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:FAIL_CREATE_HASH');
1510 END IF;
1511 --
1512
1513 WHEN Others THEN
1514 WSH_UTIL_CORE.Default_Handler('WSH_DELIVERY_AUTOCREATE.Find_Matching_Groups');
1515 x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR;
1516
1517 IF c_matching_deliveries%ISOPEN THEN
1518 CLOSE c_matching_deliveries;
1519 END IF;
1520 IF c_matching_dels_new%ISOPEN THEN
1521 CLOSE c_matching_dels_new;
1522 END IF;
1523
1524 --
1525 -- Debug Statements
1526 --
1527 IF l_debug_on THEN
1528 WSH_DEBUG_SV.logmsg(l_module_name,'Unexpected error has occured. Oracle error message is '|| SQLERRM,WSH_DEBUG_SV.C_UNEXPEC_ERR_LEVEL);
1529 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:OTHERS');
1530 END IF;
1531
1532 END;
1533
1534
1535
1536
1537
1538
1539
1540
1541 -----------------------------------------------------------------------------
1542 --
1543 -- Function: Check_Sch_Date_Match
1544 -- Parameters: p_delivery_id, p_del_date, p_detail_date
1545 -- Description: Checks if scheduled date on line matches initial pickup date on delivery
1546 -- FOR THE PRESENT, FUNCTION SIMPLY RETURNS TRUE
1547 --
1548 -----------------------------------------------------------------------------
1549
1550 --
1551 --
1552 FUNCTION Check_Sch_Date_Match ( p_delivery_id IN NUMBER,
1553 p_del_date IN DATE,
1554 p_detail_date IN DATE) RETURN BOOLEAN IS
1555 --
1556 l_debug_on BOOLEAN;
1557 --
1558 l_module_name CONSTANT VARCHAR2(100) := 'wsh.plsql.' || G_PKG_NAME || '.' || 'CHECK_SCH_DATE_MATCH';
1559 --
1560 BEGIN
1561
1562 --
1563 -- Debug Statements
1564 --
1565 --
1566 l_debug_on := WSH_DEBUG_INTERFACE.g_debug;
1567 --
1568 IF l_debug_on IS NULL
1569 THEN
1570 l_debug_on := WSH_DEBUG_SV.is_debug_enabled;
1571 END IF;
1572 --
1573 IF l_debug_on THEN
1574 WSH_DEBUG_SV.push(l_module_name);
1575 --
1576 WSH_DEBUG_SV.log(l_module_name,'P_DELIVERY_ID',P_DELIVERY_ID);
1577 WSH_DEBUG_SV.log(l_module_name,'P_DEL_DATE',P_DEL_DATE);
1578 WSH_DEBUG_SV.log(l_module_name,'P_DETAIL_DATE',P_DETAIL_DATE);
1579 END IF;
1580 --
1581 --
1582 -- Debug Statements
1583 --
1584 IF l_debug_on THEN
1585 WSH_DEBUG_SV.pop(l_module_name);
1586 END IF;
1587 --
1588 RETURN TRUE;
1589
1590 END Check_Sch_Date_Match;
1591
1592 -----------------------------------------------------------------------------
1593 --
1594 -- Function: Check_Req_Date_Match
1595 -- arameters: p_delivery_id, p_del_date, p_detail_date
1596 -- Description: Checks if requested date on line matches ultimate dropoff date on delivery
1597 -- FOR THE PRESENT, FUNCTION SIMPLY RETURNS TRUE
1598 --
1599 -----------------------------------------------------------------------------
1600
1601 FUNCTION Check_Req_Date_Match ( p_delivery_id IN NUMBER,
1602 p_del_date IN DATE,
1603 p_detail_date IN DATE) RETURN BOOLEAN IS
1604 --
1605 l_debug_on BOOLEAN;
1606 --
1607 l_module_name CONSTANT VARCHAR2(100) := 'wsh.plsql.' || G_PKG_NAME || '.' || 'CHECK_REQ_DATE_MATCH';
1608 --
1609 BEGIN
1610
1611 --
1612 -- Debug Statements
1613 --
1614 --
1615 l_debug_on := WSH_DEBUG_INTERFACE.g_debug;
1616 --
1617 IF l_debug_on IS NULL
1618 THEN
1619 l_debug_on := WSH_DEBUG_SV.is_debug_enabled;
1620 END IF;
1621 --
1622 IF l_debug_on THEN
1623 WSH_DEBUG_SV.push(l_module_name);
1624 --
1625 WSH_DEBUG_SV.log(l_module_name,'P_DELIVERY_ID',P_DELIVERY_ID);
1626 WSH_DEBUG_SV.log(l_module_name,'P_DEL_DATE',P_DEL_DATE);
1627 WSH_DEBUG_SV.log(l_module_name,'P_DETAIL_DATE',P_DETAIL_DATE);
1628 END IF;
1629 --
1630 --
1631 -- Debug Statements
1632 --
1633 IF l_debug_on THEN
1634 WSH_DEBUG_SV.pop(l_module_name);
1635 END IF;
1636 --
1637 RETURN TRUE;
1638
1639 END Check_Req_Date_Match;
1640
1641 -----------------------------------------------------------------------------
1642 --
1643 -- Procedure: Autonomous_Create_Delivery
1644 -- Parameters: p_delivery_info, x_rowid, x_delivery_id, x_delivery_name, x_return_status
1645 -- Description: Local API for Autononmous Transaction for Creating Delivery in Parallel Pick
1646 -- Release worker processes.
1647 --
1648 -----------------------------------------------------------------------------
1649
1650 PROCEDURE Autonomous_Create_Delivery (
1651 p_delivery_info IN wsh_new_deliveries_pvt.delivery_rec_type,
1652 x_rowid OUT NOCOPY VARCHAR2,
1653 x_delivery_id OUT NOCOPY NUMBER,
1654 x_delivery_name OUT NOCOPY VARCHAR2,
1655 x_return_status OUT NOCOPY VARCHAR2)
1656 IS
1657
1658 PRAGMA AUTONOMOUS_TRANSACTION;
1659
1660 others EXCEPTION;
1661 l_debug_on BOOLEAN;
1662 l_module_name CONSTANT VARCHAR2(100) := 'wsh.plsql.' || G_PKG_NAME || '.' || 'AUTONOMOUS_CREATE_DELIVERY';
1663
1664 BEGIN
1665
1666 l_debug_on := WSH_DEBUG_INTERFACE.g_debug;
1667 IF l_debug_on IS NULL THEN
1668 l_debug_on := WSH_DEBUG_SV.is_debug_enabled;
1669 END IF;
1670
1671 x_return_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
1672
1673 IF l_debug_on THEN
1674 WSH_DEBUG_SV.push(l_module_name);
1675 WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_NEW_DELIVERIES_PVT.CREATE_DELIVERY',WSH_DEBUG_SV.C_PROC_LEVEL);
1676 END IF;
1677 WSH_NEW_DELIVERIES_PVT.Create_Delivery(p_delivery_info, x_rowid, x_delivery_id, x_delivery_name, x_return_status);
1678 IF (x_return_status <> WSH_UTIL_CORE.G_RET_STS_SUCCESS) THEN
1679 IF l_debug_on THEN
1680 WSH_DEBUG_SV.logmsg(l_module_name, 'ERROR IN WSH_NEW_DELIVERIES_PVT.CREATE_DELIVERY ');
1681 END IF;
1682 IF (x_return_status = WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR) THEN
1683 IF l_debug_on THEN
1684 WSH_DEBUG_SV.logmsg(l_module_name,'PROC WSH_NEW_DELIVERIES_PVT.CREATE_DELIVERY RETURNED UNEXPECTED ERROR');
1685 END IF;
1686 RAISE OTHERS;
1687 ELSE
1688 IF l_debug_on THEN
1689 WSH_DEBUG_SV.logmsg(l_module_name, 'PROC WSH_NEW_DELIVERIES_PVT.CREATE_DELIVERY RETURNED ERROR');
1690 WSH_DEBUG_SV.pop(l_module_name);
1691 END IF;
1692 ROLLBACK;
1693 RETURN;
1694 END IF;
1695 END IF;
1696
1697 IF l_debug_on THEN
1698 WSH_DEBUG_SV.logmsg(l_module_name, 'CREATED DELIVERY # '||X_DELIVERY_NAME );
1699 END IF;
1700
1701 IF l_debug_on THEN
1702 WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_FLEXFIELD_UTILS.WRITE_DFF_ATTRIBUTES',WSH_DEBUG_SV.C_PROC_LEVEL);
1703 END IF;
1704 wsh_flexfield_utils.WRITE_DFF_ATTRIBUTES
1705 (p_table_name => 'WSH_NEW_DELIVERIES',
1706 p_primary_id => x_delivery_id,
1707 x_return_status => x_return_status);
1708
1709 IF (x_return_status <> WSH_UTIL_CORE.G_RET_STS_SUCCESS) THEN
1710 IF l_debug_on THEN
1711 WSH_DEBUG_SV.logmsg(l_module_name, 'PROC WSH_FLEXFIELD_UTILS.WRITE_DFF_ATTRIBUTES RETURNED ERROR' );
1712 WSH_DEBUG_SV.pop(l_module_name);
1713 END IF;
1714 ROLLBACK;
1715 RETURN;
1716 END IF;
1717
1718 IF l_debug_on THEN
1719 WSH_DEBUG_SV.log(l_module_name,'Autonomous Return status ', x_return_status);
1720 END IF;
1721
1722 IF (x_return_status = WSH_UTIL_CORE.G_RET_STS_SUCCESS) THEN
1723 COMMIT;
1724 ELSE
1725 ROLLBACK;
1726 END IF;
1727
1728 IF l_debug_on THEN
1729 WSH_DEBUG_SV.pop(l_module_name);
1730 END IF;
1731
1732 EXCEPTION
1733 WHEN OTHERS THEN
1734 wsh_util_core.default_handler('WSH_DELIVERY_AUTOCREATE.AUTONOMOUS_CREATE_DELIVERY');
1735 x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR;
1736 IF l_debug_on THEN
1737 WSH_DEBUG_SV.logmsg(l_module_name,'Unexpected error has occured. Oracle error message is '|| SQLERRM,WSH_DEBUG_SV.C_UNEXPEC_ERR_LEVEL);
1738 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:OTHERS');
1739 END IF;
1740 ROLLBACK;
1741 END Autonomous_Create_Delivery;
1742
1743 --------------------------------------------------------------------------
1744 --
1745 -- Procedure: Autocreate_deliveries
1746 -- Parameters: p_line_rows, p_line_info_rows, p_init_flag,
1747 -- p_use_header_flag, p_max_detail_commit, p_del_rows
1748 -- Description: Used to automatically create deliveries
1749 -- p_line_rows - Table of delivery detail ids
1750 -- p_init_flag - 'Y' initializes the table of deliveries
1751 -- p_pick_release_flag - 'Y' means use header_id for grouping
1752 -- p_container_flag - 'Y' means call Autopack routine
1753 -- p_check_flag - 'Y' means delivery details will be
1754 -- grouped without creating deliveries
1755 -- p_generate_carton_group_id - 'Y' means api called for generate
1756 -- carton group id only
1757 -- p_max_detail_commit - Commits data after delivery detail
1758 -- count reaches this value - No Longer Used
1759 -- x_del_rows - Created delivery ids
1760 -- p_grouping_rows - returns group ids for each detail,
1761 -- when p_check_flag is set to 'Y'
1762 -- x_return_status - Status of execution
1763 --------------------------------------------------------------------------
1764
1765 PROCEDURE autocreate_deliveries(
1766 p_line_rows IN wsh_util_core.id_tab_type,
1767 p_init_flag IN VARCHAR2,
1768 p_pick_release_flag IN VARCHAR2,
1769 p_container_flag IN VARCHAR2 := 'N',
1770 p_check_flag IN VARCHAR2 := 'N',
1771 p_caller IN VARCHAR2 DEFAULT NULL,
1772 p_generate_carton_group_id IN VARCHAR2 := 'N',
1773 p_max_detail_commit IN NUMBER := 1000,
1774 x_del_rows OUT NOCOPY wsh_util_core.id_tab_type,
1775 x_grouping_rows OUT NOCOPY wsh_util_core.id_tab_type,
1776 x_return_status OUT NOCOPY VARCHAR2 ) IS
1777
1778
1779 /* Bug 3206620 : cursor to get the container flag of the delivery detail*/
1780 cursor c_cont (p_entity_id NUMBER) is
1781 SELECT container_flag
1782 FROM wsh_delivery_details
1783 WHERE delivery_detail_id = p_entity_id;
1784
1785 cursor c_matching_delivery(p_hash_value in number,
1786 p_hash_string in varchar2,
1787 p_batch_id in number,
1788 p_header_id number,
1789 p_carrier_id in number,
1790 p_mode_of_transport in varchar2,
1791 p_service_level in varchar2) is
1792 select delivery_id, name, rowid
1793 from wsh_new_deliveries wnd
1794 where wnd.hash_value = p_hash_value
1795 and wnd.hash_string = p_hash_string
1796 and wnd.batch_id = p_batch_id
1797 and (NVL(wnd.planned_flag, 'N') = 'N')
1798 and NVL(wnd.source_header_id, -1) = NVL(p_header_id, -1)
1799 and NVL(NVL(wnd.carrier_id, p_carrier_id), -1) = NVL(NVL(p_carrier_id, wnd.carrier_id), -1)
1800 and NVL(NVL(wnd.service_level, p_service_level), -1) = NVL(NVL(p_service_level, wnd.service_level), -1)
1801 and NVL(NVL(wnd.mode_of_transport, p_mode_of_transport), -1) = NVL(NVL(p_mode_of_transport, wnd.mode_of_transport), -1)
1802 and wnd.status_code in ('OP', 'SA');
1803
1804 l_group_info grp_attr_tab_type;
1805 l_delivery_info wsh_new_deliveries_pvt.delivery_rec_type;
1806 l_rowid VARCHAR2(30);
1807 l_delivery_id NUMBER;
1808 l_delivery_name wsh_new_deliveries.name%TYPE;
1809 l_weight_uom_code VARCHAR2(10);
1810 l_volume_uom_code VARCHAR2(10);
1811
1812 l_assigned_flag VARCHAR2(1) := 'N';
1813
1814 l_return_status VARCHAR2(1);
1815 l_dummy VARCHAR2(1);
1816
1817 i NUMBER;
1818
1819 --bug 1613019
1820 l_line_lpn_id varchar2(30);
1821 --bug 1613019
1822
1823 l_error_code number := NULL;
1824 l_error_text varchar2(2000) := NULL;
1825
1826 l_caller VARCHAR2(2000) := 'WSH_AUTO_CREATE_DEL';
1827
1828 --
1829 -- BUG : 2286739
1830 l_check_fte_inst VARCHAR2(1) := 'N'; -- used to check is FTE is installed or not
1831
1832 -- bug 2691385
1833 l_detail_is_empty_cont VARCHAR2(1) := 'N';
1834 l_param_info WSH_SHIPPING_PARAMS_PVT.Parameter_Rec_Typ;
1835
1836 l_attr_tab wsh_delivery_autocreate.grp_attr_tab_type;
1837 l_action_rec wsh_delivery_autocreate.action_rec_type;
1838 l_target_rec wsh_delivery_autocreate.grp_attr_rec_type;
1839 l_matched_entities wsh_util_core.id_tab_type;
1840 l_out_rec wsh_delivery_autocreate.out_rec_type;
1841 l_del_select_carrier wsh_util_core.id_tab_type;
1842 l_del_rate wsh_util_core.id_tab_type;
1843 l_del_rate_location wsh_util_core.id_tab_type;
1844
1845 l_delivery_tab wsh_util_core.id_tab_type;
1846 l_delivery_rows wsh_util_core.id_tab_type;
1847 j NUMBER;
1848
1849 l_exception_id NUMBER;
1850 l_exception_message VARCHAR2(2000);
1851 l_in_param_rec WSH_FTE_INTEGRATION.rate_del_in_param_rec;
1852 l_out_param_rec WSH_FTE_INTEGRATION.rate_del_out_param_rec;
1853 l_log_itm_exc VARCHAR2(1);
1854
1855 l_msg_count NUMBER;
1856 l_msg_data VARCHAR2(2000);
1857
1858 l_group_index NUMBER; -- frontport 5415196
1859 l_notfound BOOLEAN;
1860
1861 cannot_autocreate_del EXCEPTION;
1862 others EXCEPTION;
1863
1864 e_return_excp EXCEPTION; -- LPN CONV. rv
1865
1866 l_warn_num NUMBER := 0;
1867
1868 -- LPN CONV. rv
1869 l_error_num NUMBER := 0;
1870 l_lpn_in_sync_comm_rec WSH_GLBL_VAR_STRCT_GRP.lpn_sync_comm_in_rec_type;
1871 l_lpn_out_sync_comm_rec WSH_GLBL_VAR_STRCT_GRP.lpn_sync_comm_out_rec_type;
1872 -- LPN CONV. rv
1873
1874 l_lock_handle VARCHAR2(100);
1875 l_lock_status NUMBER;
1876 --
1877 l_debug_on BOOLEAN;
1878 --
1879 l_module_name CONSTANT VARCHAR2(100) := 'wsh.plsql.' || G_PKG_NAME || '.' || 'AUTOCREATE_DELIVERIES';
1880 --
1881 BEGIN
1882
1883 /*
1884 p_max_detail_commit is not longer used but is retained as parameter
1885 because of dependency issues
1886 */
1887
1888 l_debug_on := WSH_DEBUG_INTERFACE.g_debug;
1889 --
1890 IF l_debug_on IS NULL THEN
1891 l_debug_on := WSH_DEBUG_SV.is_debug_enabled;
1892 END IF;
1893 --
1894 IF l_debug_on THEN
1895 WSH_DEBUG_SV.push(l_module_name);
1896 --
1897 WSH_DEBUG_SV.log(l_module_name,'P_INIT_FLAG', P_INIT_FLAG);
1898 WSH_DEBUG_SV.log(l_module_name,'P_PICK_RELEASE_FLAG',P_PICK_RELEASE_FLAG);
1899 WSH_DEBUG_SV.log(l_module_name,'P_CONTAINER_FLAG', P_CONTAINER_FLAG);
1900 WSH_DEBUG_SV.log(l_module_name,'P_CHECK_FLAG', P_CHECK_FLAG);
1901 WSH_DEBUG_SV.log(l_module_name,'P_MAX_DETAIL_COMMIT',P_MAX_DETAIL_COMMIT);
1902 WSH_DEBUG_SV.log(l_module_name,'P_CALLER', P_CALLER);
1903 END IF;
1904 --
1905 x_return_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
1906
1907
1908 IF (p_line_rows.count = 0) THEN
1909 IF l_debug_on THEN
1910 WSH_DEBUG_SV.logmsg(l_module_name, 'RAISING EXCEPTION WHEN OTHERS BECAUSE P_LINE_ROWS.COUNT IS 0' );
1911 END IF;
1912 --
1913 raise others;
1914 END IF;
1915
1916 IF l_debug_on THEN
1917 WSH_DEBUG_SV.logmsg(l_module_name, 'INITIALIZING AUTO_DEL_IDS...' );
1918 END IF;
1919 --
1920
1921 FOR i IN 1..p_line_rows.count LOOP
1922 IF l_debug_on THEN
1923 WSH_DEBUG_SV.logmsg(l_module_name, '**** PROCESSING DELIVERY DETAIL ID '||P_LINE_ROWS ( I ) ||' ****' );
1924 END IF;
1925 --
1926 l_attr_tab(i).entity_id := p_line_rows(i);
1927 l_attr_tab(i).entity_type := 'DELIVERY_DETAIL';
1928 END LOOP;
1929
1930 l_action_rec.action := 'AUTOCREATE_DELIVERIES';
1931
1932 l_action_rec.group_by_header_flag := p_pick_release_flag;
1933
1934 IF l_debug_on THEN
1935 WSH_DEBUG_SV.logmsg(l_module_name, 'Calling Find_Matching_Groups' );
1936 END IF;
1937 Find_Matching_Groups(p_attr_tab => l_attr_tab,
1938 p_action_rec => l_action_rec,
1939 p_target_rec => l_target_rec,
1940 p_group_tab => l_group_info,
1941 x_matched_entities => l_matched_entities,
1942 x_out_rec => l_out_rec,
1943 x_return_status => x_return_status);
1944
1945 IF x_return_status <> WSH_UTIL_CORE.G_RET_STS_SUCCESS THEN
1946 raise e_return_excp;
1947 END IF;
1948
1949 IF l_debug_on THEN
1950 WSH_DEBUG_SV.log(l_module_name, 'l_group_info.COUNT', l_group_info.COUNT);
1951 END IF;
1952
1953 j := 0;
1954
1955 l_del_select_carrier.delete;
1956 l_del_rate.delete;
1957 l_del_rate_location.delete;
1958
1959 -- frontport 5415196: removed WHILE loop; inside code is relocated below.
1960 -- We no longer loop through the groups to create a delivery for each group.
1961 -- Instead, we will create deliveries as needed when looping through
1962 -- the details.
1963
1964 FOR i in 1..l_attr_tab.count LOOP
1965
1966 IF (l_attr_tab(i).ship_to_location_id is NULL ) THEN --{
1967 -- identify the record with null ship-to location,
1968 -- set an appropriate message, and immediately return
1969 -- without autocreating any delivery.
1970
1971 l_line_lpn_id := to_char(l_attr_tab(i).entity_id);
1972 /* Bug 3206620 */
1973 OPEN c_cont(l_attr_tab(i).entity_id);
1974 FETCH c_cont into l_attr_tab(i).container_flag;
1975 CLOSE c_cont;
1976
1977 IF l_debug_on THEN
1978 WSH_DEBUG_SV.log(l_module_name,'container flag is',l_attr_tab(i).container_flag);
1979 END IF;
1980
1981 IF l_attr_tab(i).container_flag = 'Y' THEN --{
1982 WSH_CONTAINER_UTILITIES.Is_Empty (p_container_instance_id => l_attr_tab(i).entity_id,
1983 x_empty_flag => l_detail_is_empty_cont,
1984 x_return_status => l_return_status);
1985
1986 IF (l_return_status IN(WSH_UTIL_CORE.G_RET_STS_ERROR,WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR)) THEN
1987 x_return_status := l_return_status;
1988 wsh_util_core.add_message(x_return_status);
1989 IF l_debug_on THEN
1990 WSH_DEBUG_SV.logmsg(l_module_name,'Error returned from WSH_CONTAINER_UTILITIES.Is_Empty');
1991 END IF;
1992 raise e_return_excp; -- LPN CONV. rv
1993 END IF;
1994
1995 IF l_debug_on THEN
1996 WSH_DEBUG_SV.log(l_module_name, 'l_detail_is_empty_cont',l_detail_is_empty_cont );
1997 END IF;
1998
1999 IF l_detail_is_empty_cont = 'Y' then
2000 FND_MESSAGE.SET_NAME('WSH','WSH_CONT_EMPTY');
2001 x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
2002 wsh_util_core.add_message(x_return_status);
2003 IF l_debug_on THEN
2004 WSH_DEBUG_SV.logmsg(l_module_name, 'container empty,autocreate delivery not allowed');
2005 WSH_DEBUG_SV.pop(l_module_name);
2006 END IF;
2007 raise e_return_excp; -- LPN CONV. rv
2008 ELSE
2009 FND_MESSAGE.SET_NAME('WSH','WSH_ULT_DROPOFF_LOC_ID_NOT_FND');
2010 FND_MESSAGE.SET_TOKEN('LINE_LPN_ID',l_line_lpn_id);
2011 x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
2012 wsh_util_core.add_message(x_return_status);
2013 IF l_debug_on THEN
2014 WSH_DEBUG_SV.logmsg(l_module_name, 'SHIPTO LOCATION NOT FOUND FOR '||P_LINE_ROWS ( I ) );
2015 END IF;
2016 raise e_return_excp; -- LPN CONV. rv
2017 END IF;
2018 ELSE
2019 FND_MESSAGE.SET_NAME('WSH','WSH_ULT_DROPOFF_LOC_ID_NOT_FND');
2020 FND_MESSAGE.SET_TOKEN('LINE_LPN_ID',l_line_lpn_id);
2021 x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
2022 wsh_util_core.add_message(x_return_status);
2023 IF l_debug_on THEN
2024 WSH_DEBUG_SV.logmsg(l_module_name, 'SHIPTO LOCATION NOT FOUND FOR '||P_LINE_ROWS ( I ) );
2025 END IF;
2026 raise e_return_excp; -- LPN CONV. rv
2027 END IF;
2028 -- the code will not continue; the above IFs and ELSEs
2029 -- will have raised the exception because of null ship-to location
2030 END IF; --}
2031
2032 IF l_attr_tab(i).delivery_id IS NOT NULL THEN
2033
2034 -- Use this flag to set a warning message at the end of the procedure
2035 IF l_debug_on THEN
2036 WSH_DEBUG_SV.logmsg(l_module_name, 'THE LINE IS ASSIGNED TO DELIVERY '||l_attr_tab(i).DELIVERY_ID );
2037 END IF;
2038 --
2039 FND_MESSAGE.SET_NAME('WSH', 'WSH_DET_ASSIGNED_DEL');
2040 FND_MESSAGE.SET_TOKEN('DET_NAME', l_attr_tab(i).entity_id);
2041 FND_MESSAGE.SET_TOKEN('DEL_NAME', WSH_NEW_DELIVERIES_PVT.get_name(l_attr_tab(i).DELIVERY_ID));
2042 wsh_util_core.add_message(WSH_UTIL_CORE.G_RET_STS_ERROR, l_module_name);
2043
2044 l_assigned_flag := 'Y';
2045
2046 ELSE --{ not assigned
2047
2048 -- 5415196 start (code relocated from the WHILE loop deleted above)
2049 -- use the hash value as an index for tracking deliveries to be
2050 -- created.
2051 -- if pick release option is set to autocreate deliveries within
2052 -- orders, one hash value can be associated with more than 1 group
2053 -- which is based on source_header_id.
2054
2055 l_group_index := l_attr_tab(i).l1_hash_value;
2056
2057 -- Hash Values match but Attributes do not match. Need to find the correct Group
2058 WHILE l_attr_tab(i).group_id <> l_group_info(l_group_index).group_id LOOP
2059 l_group_index := l_group_index + 1;
2060 END LOOP;
2061
2062 IF l_debug_on THEN
2063 WSH_DEBUG_SV.log(l_module_name, 'l_group_info(' || l_group_index || ').source_header_id', l_group_info(l_group_index).source_header_id);
2064 WSH_DEBUG_SV.log(l_module_name, 'l_attr_tab(' || i || ').source_header_id', l_attr_tab(i).source_header_id);
2065 WSH_DEBUG_SV.log(l_module_name, 'l_group_info(' || l_group_index || ').group_id', l_group_info(l_group_index).group_id);
2066 WSH_DEBUG_SV.log(l_module_name, 'l_attr_tab(=).group_id', l_attr_tab(i).group_id);
2067 END IF;
2068
2069 IF l_attr_tab(i).group_id IS NOT NULL
2070 AND NOT (l_delivery_tab.exists(l_attr_tab(i).group_id)) THEN --{
2071 -- we need to create a new delivery or, if in parallel pick,
2072 -- use the matching delivery autocreated within the batch.
2073 --
2074
2075 -- frontport 5415196: with pick release option to autocreate dels
2076 -- within orders, one hash value can represent multiple order groups;
2077 -- therefore, use the detail's source header to stamp the delivery.
2078 -- value will be NULL if not grouping by orders.
2079 l_delivery_info.source_header_id := l_attr_tab(i).source_header_id;
2080
2081 l_delivery_info.delivery_type := 'STANDARD';
2082 l_delivery_info.ultimate_dropoff_location_id := l_group_info(l_group_index).ship_to_location_id;
2083 l_delivery_info.initial_pickup_location_id := l_group_info(l_group_index).ship_from_location_id;
2084 l_delivery_info.organization_id := l_group_info(l_group_index).organization_id;
2085 l_delivery_info.ignore_for_planning := l_group_info(l_group_index).ignore_for_planning;
2086 l_delivery_info.shipment_direction := l_group_info(l_group_index).line_direction;
2087 l_delivery_info.customer_id := l_group_info(l_group_index).customer_id;
2088 l_delivery_info.fob_code := l_group_info(l_group_index).fob_code;
2089 l_delivery_info.freight_terms_code := l_group_info(l_group_index).freight_terms_code;
2090 l_delivery_info.intmed_ship_to_location_id := l_group_info(l_group_index).intmed_ship_to_location_id;
2091 l_delivery_info.ship_method_code := l_group_info(l_group_index).ship_method_code;
2092 l_delivery_info.carrier_id := l_group_info(l_group_index).carrier_id;
2093 l_delivery_info.initial_pickup_date := l_group_info(l_group_index).date_scheduled;
2094 l_delivery_info.ultimate_dropoff_date := l_group_info(l_group_index).date_requested;
2095 l_delivery_info.shipping_control := l_group_info(l_group_index).shipping_control;
2096 l_delivery_info.vendor_id := l_group_info(l_group_index).vendor_id;
2097 l_delivery_info.party_id := l_group_info(l_group_index).party_id;
2098 l_delivery_info.mode_of_transport := l_group_info(l_group_index).mode_of_transport;
2099 l_delivery_info.service_level := l_group_info(l_group_index).service_level;
2100 l_delivery_info.status_code := l_group_info(l_group_index).status_code;
2101 l_delivery_info.batch_id := wsh_pick_list.g_batch_id;
2102 l_delivery_info.hash_value := l_group_info(l_group_index).l1_hash_value;
2103 l_delivery_info.hash_string := l_group_info(l_group_index).l1_hash_string;
2104
2105 IF l_debug_on THEN
2106 WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_WV_UTILS.GET_DEFAULT_UOMS',WSH_DEBUG_SV.C_PROC_LEVEL);
2107 END IF;
2108
2109 wsh_wv_utils.get_default_uoms(l_group_info(l_group_index).organization_id, l_weight_uom_code, l_volume_uom_code, l_dummy);
2110
2111 l_delivery_info.weight_uom_code := l_weight_uom_code;
2112 l_delivery_info.volume_uom_code := l_volume_uom_code;
2113
2114 -- 5415196: frontport reconciled with parallel pick release
2115 -- and LPN convergence
2116
2117 IF WSH_PICK_LIST.G_BATCH_ID IS NOT NULL
2118 AND WSH_PICK_LIST.G_PICK_REL_PARALLEL THEN --{
2119 IF l_debug_on THEN
2120 WSH_DEBUG_SV.logmsg(l_module_name,'Acquiring lock on :'||l_delivery_info.hash_value||'-'||l_delivery_info.batch_id);
2121 END IF;
2122 DBMS_LOCK.Allocate_Unique(lockname => l_delivery_info.hash_value||'-'||l_delivery_info.batch_id,
2123 lockhandle => l_lock_handle);
2124 l_lock_status := DBMS_LOCK.Request(lockhandle => l_lock_handle,
2125 lockmode => 6);
2126 IF l_lock_status = 0 THEN --{
2127 -- Successfully locked, so check if a delivery
2128 -- has been created in between acquiring the lock
2129 IF l_debug_on THEN
2130 WSH_DEBUG_SV.log(l_module_name,'p_header_id', l_delivery_info.source_header_id);
2131 END IF;
2132
2133 OPEN c_matching_delivery(
2134 p_hash_value => l_delivery_info.hash_value,
2135 p_hash_string => l_delivery_info.hash_string,
2136 p_batch_id => l_delivery_info.batch_id,
2137 p_header_id => l_delivery_info.source_header_id,
2138 p_carrier_id => l_delivery_info.carrier_id,
2139 p_mode_of_transport => l_delivery_info.mode_of_transport,
2140 p_service_level => l_delivery_info.service_level);
2141
2142 FETCH c_matching_delivery INTO l_delivery_id,
2143 l_delivery_name,
2144 l_rowid;
2145 l_notfound := c_matching_delivery%NOTFOUND;
2146 CLOSE c_matching_delivery;
2147
2148 IF l_notfound THEN --{
2149 IF l_debug_on THEN
2150 WSH_DEBUG_SV.logmsg(l_module_name,'Matching delivery is not found, so create a new delivery ');
2151 WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit Autonomous_Create_Delivery',WSH_DEBUG_SV.C_PROC_LEVEL);
2152 END IF;
2153 Autonomous_Create_Delivery(l_delivery_info, l_rowid,
2154 l_delivery_id, l_delivery_name, x_return_status);
2155 IF l_debug_on THEN
2156 WSH_DEBUG_SV.log(l_module_name,'Return from Autonomous_Create_Delivery, Return status', x_return_status);
2157 END IF;
2158 l_lock_status := DBMS_LOCK.Release(l_lock_handle);
2159 l_lock_handle := NULL;
2160
2161 IF x_return_status <> WSH_UTIL_CORE.G_RET_STS_SUCCESS THEN
2162 IF (x_return_status = WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR) THEN
2163 raise others;
2164 ELSE
2165 IF l_debug_on THEN
2166 WSH_DEBUG_SV.logmsg(l_module_name, 'PROC WSH_DELIVERY_AUTOCREATE.Autonomous_Create_Delivery RETURNED ERROR');
2167 END IF;
2168 raise e_return_excp;
2169 END IF;
2170 ELSE
2171 l_group_info(l_group_index).delivery_id := l_delivery_id;
2172 END IF;
2173 --}
2174 ELSE --{
2175 l_lock_status := DBMS_LOCK.Release(l_lock_handle);
2176 l_lock_handle := NULL;
2177 IF l_debug_on THEN
2178 WSH_DEBUG_SV.logmsg(l_module_name,'Matching delivery '||l_delivery_name||' already exists , so skip creating a new delivery ');
2179 END IF;
2180
2181 l_group_info(l_group_index).delivery_id := l_delivery_id;
2182 GOTO SKIP_ITM_EXISTING_DEL;
2183 --}
2184 END IF;
2185 --}
2186 ELSE
2187 -- Any other problems in acquiring the lock,
2188 -- raise error and return
2189 -- This can happen only if there's a timeout issue
2190 -- or unexpected error
2191 x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
2192 IF l_debug_on THEN
2193 WSH_DEBUG_SV.logmsg(l_module_name, 'Error when trying to acquire User Lock, Lock Status :'||l_lock_status );
2194 END IF;
2195 raise e_return_excp;
2196 END IF; --}
2197 --}
2198 ELSE
2199 --{ non-parallel-pick case
2200 IF l_debug_on THEN
2201 WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_NEW_DELIVERIES_PVT.CREATE_DELIVERY',WSH_DEBUG_SV.C_PROC_LEVEL);
2202 END IF;
2203 wsh_new_deliveries_pvt.create_delivery(l_delivery_info, l_rowid, l_delivery_id, l_delivery_name, x_return_status);
2204 IF (x_return_status <> WSH_UTIL_CORE.G_RET_STS_SUCCESS) THEN
2205 IF l_debug_on THEN
2206 WSH_DEBUG_SV.logmsg(l_module_name, 'ERROR IN WSH_NEW_DELIVERIES_PVT.CREATE_DELIVERY FOR '||P_LINE_ROWS ( I ) );
2207 END IF;
2208 IF (x_return_status = WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR) THEN
2209 IF l_debug_on THEN
2210 WSH_DEBUG_SV.logmsg(l_module_name,'PROC WSH_NEW_DELIVERIES_PVT.CREATE_DELIVERY RETURNED UNEXPECTED ERROR');
2211 END IF;
2212 raise others;
2213 ELSE
2214 IF l_debug_on THEN
2215 WSH_DEBUG_SV.logmsg(l_module_name, 'PROC WSH_NEW_DELIVERIES_PVT.CREATE_DELIVERY RETURNED ERROR');
2216 END IF;
2217 raise e_return_excp;
2218 END IF;
2219 END IF;
2220
2221 IF l_debug_on THEN
2222 WSH_DEBUG_SV.logmsg(l_module_name, 'CREATED DELIVERY # '||L_DELIVERY_NAME );
2223 END IF;
2224
2225 l_group_info(l_group_index).delivery_id := l_delivery_id;
2226
2227 IF l_debug_on THEN
2228 WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_FLEXFIELD_UTILS.WRITE_DFF_ATTRIBUTES',WSH_DEBUG_SV.C_PROC_LEVEL);
2229 END IF;
2230 wsh_flexfield_utils.WRITE_DFF_ATTRIBUTES
2231 (p_table_name => 'WSH_NEW_DELIVERIES',
2232 p_primary_id => l_delivery_id,
2233 x_return_status => x_return_status);
2234
2235 IF (x_return_status <> WSH_UTIL_CORE.G_RET_STS_SUCCESS) THEN
2236 IF l_debug_on THEN
2237 WSH_DEBUG_SV.logmsg(l_module_name, 'PROC WSH_FLEXFIELD_UTILS.WRITE_DFF_ATTRIBUTES RETURNED ERROR' );
2238 END IF;
2239 raise e_return_excp;
2240 END IF;
2241
2242 --}
2243 END IF;
2244
2245 -- following code is common for parallel and non-parallel cases
2246 -- of creating a new delivery, up to the label SKIP_ITM_EXISTING_DEL.
2247
2248 IF l_debug_on THEN
2249 WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_SHIPPING_PARAMS_PVT.Get',WSH_DEBUG_SV.C_PROC_LEVEL);
2250 END IF;
2251
2252 WSH_SHIPPING_PARAMS_PVT.Get(
2253 p_organization_id => l_delivery_info.organization_id,
2254 x_param_info => l_param_info,
2255 x_return_status => l_return_status
2256 );
2257
2258 -- Pack J: Bug fix 3043993. KVENKATE
2259 -- Add message if return status is not success
2260 -- Only modification is to add message. Since there was no code
2261 -- to exit or return after
2262 -- call to the above procedure, leaving that behavior the same.
2263
2264 IF l_return_status <> WSH_UTIL_CORE.G_RET_STS_SUCCESS THEN
2265 FND_MESSAGE.SET_NAME('WSH', 'WSH_PARAM_NOT_DEFINED');
2266 FND_MESSAGE.SET_TOKEN('ORGANIZATION_CODE',
2267 wsh_util_core.get_org_name(l_delivery_info.organization_id));
2268 wsh_util_core.add_message(l_return_status,l_module_name);
2269 END IF;
2270
2271 --
2272 -- ITM Check is required only for outbound lines
2273 --
2274 IF l_param_info.export_screening_flag IN ('C', 'A')
2275 AND l_delivery_info.shipment_direction in ('O','IO') -- J-IB-NPARIKH
2276 THEN --{ ITM check
2277
2278 -- Pack J: ITM integration. If ITM screening is required
2279 -- at shipping param level,
2280 -- call Check_ITM_Required to see if the delivery criteria
2281 -- requires ITM screening and log exception.
2282
2283 IF l_debug_on THEN
2284 WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_DELIVERY_VALIDATIONS.Check_ITM_Required',WSH_DEBUG_SV.C_PROC_LEVEL);
2285 END IF;
2286
2287 l_log_itm_exc := WSH_DELIVERY_VALIDATIONS.Check_ITM_Required
2288 (p_delivery_id => l_delivery_id,
2289 x_return_status => l_return_status);
2290 IF l_debug_on THEN
2291 wsh_debug_sv.log(l_module_name,'Return Status After Calling WSH_DELIVERY_VALIDATIONS.Check_ITM_Required',l_return_status);
2292 END IF;
2293
2294 IF (l_return_status IN (WSH_UTIL_CORE.G_RET_STS_ERROR,WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR)) THEN
2295 x_return_status := l_return_status;
2296 raise e_return_excp; -- LPN CONV. rv
2297 END IF;
2298
2299 IF l_log_itm_exc = 'Y' THEN
2300 IF l_debug_on THEN
2301 WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_DELIVERY_VALIDATIONS.Log_ITM_Exception',WSH_DEBUG_SV.C_PROC_LEVEL);
2302 END IF;
2303 WSH_DELIVERY_VALIDATIONS.Log_ITM_Exception (
2304 p_delivery_id => l_delivery_id,
2305 p_action_type => 'CREATE_DELIVERY',
2306 p_ship_from_location_id => l_delivery_info.initial_pickup_location_id,
2307 x_return_status => l_return_status);
2308 IF l_debug_on THEN
2309 wsh_debug_sv.log(l_module_name,'Return Status After Calling WSH_DELIVERY_VALIDATIONS.Log_ITM_Exception',l_return_status);
2310 END IF;
2311
2312 IF (l_return_status IN (WSH_UTIL_CORE.G_RET_STS_ERROR,WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR)) THEN
2313 x_return_status := l_return_status;
2314 raise e_return_excp; -- LPN CONV. rv
2315 END IF;
2316 END IF;
2317
2318 END IF; --}
2319
2320 -- R12 ECO bug 4467032
2321 -- if p_caller is FTE_LINE_TO_TRIP,
2322 -- do not execute Apply Routing Guide
2323 -- do not execute Rating
2324
2325 -- Hiding project
2326 -- Line level autocreate trip -> no routing, no rating
2327
2328 IF (NVL(l_param_info.AUTO_APPLY_ROUTING_RULES, 'N') = 'D' AND
2329 (( p_caller <> 'WSH_AUTO_CREATE_DEL_TRIP' AND
2330 p_caller <> 'FTE_LINE_TO_TRIP') OR p_caller IS NULL)) THEN
2331 -- auto apply routing rule at delivery creation
2332 l_del_select_carrier(l_del_select_carrier.count+1) := l_delivery_id;
2333 IF l_debug_on THEN
2334 wsh_debug_sv.log(l_module_name,'calling routing_guide',p_caller);
2335 END IF;
2336 END IF;
2337
2338 -- Hiding project
2339 IF ( l_param_info.auto_calc_fgt_rate_cr_del = 'Y'
2340 AND ( ( p_caller <> 'WSH_AUTO_CREATE_DEL_TRIP'
2341 AND p_caller <> 'FTE_LINE_TO_TRIP')
2342 OR p_caller IS NULL)
2343 ) THEN
2344 -- auto rate deliveries at delivery creation
2345 l_del_rate(l_del_rate.count+1) := l_delivery_id;
2346 l_del_rate_location(l_del_rate_location.count+1) := l_delivery_info.initial_pickup_location_id;
2347 END IF;
2348
2349 <<SKIP_ITM_EXISTING_DEL>>
2350
2351 l_delivery_tab(l_attr_tab(i).group_id) := l_delivery_id;
2352
2353 j := j + 1;
2354 l_delivery_rows(j) := l_delivery_id;
2355
2356 END IF; --}
2357 -- 5415196 end
2358
2359 wsh_delivery_details_actions.assign_detail_to_delivery(
2360 p_detail_id => l_attr_tab(i).entity_id,
2361 p_delivery_id => l_delivery_tab(l_attr_tab(i).group_id),
2362 x_return_status => x_return_status,
2363 p_caller => 'AUTOCREATE'); --bug 5100229
2364
2365
2366 IF (x_return_status <> WSH_UTIL_CORE.G_RET_STS_SUCCESS) THEN
2367 IF l_debug_on THEN
2368 WSH_DEBUG_SV.logmsg(l_module_name, 'ERROR IN WSH_DELIVERY_DETAILS_ACTIONS.ASSIGN_DETAIL_TO_DELIVERY FOR '||P_LINE_ROWS ( I ) || ' TO '||L_DELIVERY_ID );
2369 END IF;
2370 --
2371 IF (x_return_status = WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR) THEN
2372 IF l_debug_on THEN
2373 WSH_DEBUG_SV.logmsg(l_module_name, 'PROC WSH_DELIVERY_DETAILS_ACTIONS.ASSIGN_DETAIL_TO_DELIVERY RETURNED UNEXPECTED ERROR' );
2374 END IF;
2375 --
2376 raise others;
2377 ELSE
2378 IF l_debug_on THEN
2379 WSH_DEBUG_SV.logmsg(l_module_name, 'PROC WSH_DELIVERY_DETAILS_ACTIONS.ASSIGN_DETAIL_TO_DELIVERY RETURNED ERROR' );
2380 END IF;
2381 raise e_return_excp; -- LPN CONV. rv
2382 END IF;
2383
2384 END IF; --}
2385
2386 END IF; --}
2387
2388 x_grouping_rows(i) := l_attr_tab(i).group_id;
2389
2390 END LOOP;
2391
2392
2393 IF (l_assigned_flag = 'Y') THEN
2394 IF (l_return_status = WSH_UTIL_CORE.G_RET_STS_SUCCESS) THEN
2395 x_return_status := WSH_UTIL_CORE.G_RET_STS_WARNING;
2396 ELSE
2397 x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
2398 END IF;
2399 END IF;
2400
2401 IF (l_group_info.count = 0) THEN
2402 IF l_debug_on THEN
2403 WSH_DEBUG_SV.logmsg(l_module_name, 'NO DELIVERIES ARE CREATED IN THIS CALL. RAISING EXCEPTION' );
2404 END IF;
2405 --
2406 raise cannot_autocreate_del;
2407 END IF;
2408
2409 -- Bug 4658241
2410 wsh_tp_release.calculate_cont_del_tpdates(
2411 p_entity => 'DLVY',
2412 p_entity_ids => l_delivery_rows,
2413 x_return_status => l_return_status);
2414 IF l_return_status = WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR THEN
2415 raise others;
2416 ELSIF l_return_status in (WSH_UTIL_CORE.G_RET_STS_ERROR , WSH_UTIL_CORE.G_RET_STS_WARNING) THEN
2417 l_warn_num := l_warn_num + 1;
2418 END IF;
2419 -- Bug 4658241 end
2420
2421 -- PATCHSET H Change FOR FTE INtegration With CARRIER_SELECTION
2422 -- [AAB]
2423 -- [03/22/2002]
2424 --
2425 --
2426 -- <<< START OF NEW CODE >>> ********************************
2427 --
2428 -- This is a code addition for Patchset H to perform carrier
2429 -- selection if installed and the shipping parameter is on
2430 -- NOTE: for pick released auto create deliveries, the carrier selection check is
2431 -- performed in the Pick release procedure
2432 --
2433 -- [AAB][04/04/2002]
2434 -- [BUG: 2301717] added check to IF statement below to ensure that
2435 -- a table of deliveries is populated with at least one delivery so
2436 -- that processing can be done correctly
2437 --
2438 l_check_fte_inst := WSH_UTIL_CORE.FTE_Is_Installed;
2439 IF (l_check_fte_inst = 'Y') THEN
2440
2441 IF ((WSH_PICK_LIST.G_BATCH_ID is null) AND
2442 (l_delivery_rows.COUNT > 0) AND l_del_select_carrier.count > 0 ) THEN
2443 --
2444 -- no batch Id so this is not from pick release
2445 -- so lets try it
2446 --
2447
2448 IF p_caller = 'WSH_AUTO_CREATE_DEL_TRIP' THEN
2449 l_caller := p_caller;
2450 END IF;
2451
2452 IF l_debug_on THEN
2453 WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_NEW_DELIVERY_ACTIONS.PROCESS_CARRIER_SELECTION',WSH_DEBUG_SV.C_PROC_LEVEL);
2454 END IF;
2455 --
2456
2457 WSH_NEW_DELIVERY_ACTIONS.PROCESS_CARRIER_SELECTION(p_delivery_id_tab => l_del_select_carrier,
2458 p_batch_id => null,
2459 p_form_flag => 'N',
2460 p_caller => l_caller,
2461 x_return_message => l_error_text,
2462 x_return_status => l_return_status);
2463
2464 IF l_return_status <> WSH_UTIL_CORE.G_RET_STS_SUCCESS THEN
2465 IF (l_return_status = WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR) THEN
2466 raise others;
2467 ELSE
2468 IF l_debug_on THEN
2469 WSH_DEBUG_SV.log(l_module_name, 'Return status from WSH_NEW_DELIVERY_ACTIONS.Process_Carrier_Selection', l_return_status );
2470 END IF;
2471 --
2472 END IF;
2473 l_warn_num := l_warn_num + 1;
2474 END IF;
2475 END IF;
2476
2477 -- <<< END OF NEW CODE >>> ********************************
2478 --
2479 -- End of FTE Integration for Carrier Selection - PATCHSET H
2480 --
2481 --
2482
2483 -- Pack J: Added Rate delivery for autocreate deliveries.
2484 -- Bug 3714834: Since autocreate del at pick release does rating
2485 -- do not rate if called by pick release.
2486
2487 IF (l_del_rate.count > 0) AND (WSH_PICK_LIST.G_BATCH_ID is null) THEN
2488
2489 l_in_param_rec.delivery_id_list := l_del_rate;
2490 l_in_param_rec.action := 'RATE';
2491 l_in_param_rec.seq_tender_flag := 'Y'; -- R12 Select Carrier
2492
2493 WSH_FTE_INTEGRATION.Rate_Delivery(
2494 p_api_version => 1.0,
2495 p_init_msg_list => FND_API.G_FALSE,
2496 p_commit => FND_API.G_FALSE,
2497 p_in_param_rec => l_in_param_rec,
2498 x_out_param_rec => l_out_param_rec,
2499 x_return_status => l_return_status,
2500 x_msg_count => l_msg_count,
2501 x_msg_data => l_msg_data);
2502 --
2503 IF l_debug_on THEN
2504 wsh_debug_sv.log(l_module_name,'Return Status from WSH_FTE_INTEGRATION.Rate_Delivery' ,l_return_status);
2505 END IF;
2506
2507 IF l_return_status <> WSH_UTIL_CORE.G_RET_STS_SUCCESS THEN
2508 IF l_return_status = WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR THEN
2509 raise others;
2510 ELSE
2511 i := l_out_param_rec.failed_delivery_id_list.FIRST;
2512 WHILE i is not NULL LOOP
2513
2514 FND_MESSAGE.SET_NAME('WSH', 'WSH_RATE_CREATE_DEL');
2515 FND_MESSAGE.SET_TOKEN('DELIVERY_ID' , to_char(l_out_param_rec.failed_delivery_id_list(i)));
2516 wsh_util_core.add_message(WSH_UTIL_CORE.G_RET_STS_WARNING);
2517
2518 FND_MESSAGE.SET_NAME('WSH', 'WSH_RATE_CREATE_DEL');
2519 FND_MESSAGE.SET_TOKEN('DELIVERY_ID' , to_char(l_out_param_rec.failed_delivery_id_list(i)));
2520 l_exception_message := FND_MESSAGE.Get;
2521 l_exception_id := NULL;
2522
2523 wsh_xc_util.log_exception(
2524 p_api_version => 1.0,
2525 x_return_status => l_return_status,
2526 x_msg_count => l_msg_count,
2527 x_msg_data => l_msg_data,
2528 x_exception_id => l_exception_id,
2529 p_exception_location_id => l_del_rate_location(i),
2530 p_logged_at_location_id => l_del_rate_location(i),
2531 p_logging_entity => 'SHIPPER',
2532 p_logging_entity_id => FND_GLOBAL.USER_ID,
2533 p_exception_name => 'WSH_RATE_CREATE_DEL',
2534 p_message => substrb(l_exception_message,1,2000),
2535 p_delivery_id => l_out_param_rec.failed_delivery_id_list(i));
2536 i := l_out_param_rec.failed_delivery_id_list.next(i);
2537 END LOOP;
2538 l_warn_num := l_warn_num + 1;
2539 END IF;
2540 END IF;
2541 END IF;
2542 END IF;
2543
2544 -- LPN CONV. rv
2545 --
2546 IF WSH_WMS_LPN_GRP.G_CALLBACK_REQUIRED = 'Y' THEN
2547 --{
2548
2549 IF l_debug_on THEN
2550 WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_LPN_SYNC_COMM_PKG.SYNC_LPNS_TO_WMS',WSH_DEBUG_SV.C_PROC_LEVEL);
2551 END IF;
2552 --
2553 WSH_LPN_SYNC_COMM_PKG.SYNC_LPNS_TO_WMS
2554 (
2555 p_in_rec => l_lpn_in_sync_comm_rec,
2556 x_return_status => l_return_status,
2557 x_out_rec => l_lpn_out_sync_comm_rec
2558 );
2559 --
2560 IF l_debug_on THEN
2561 WSH_DEBUG_SV.log(l_module_name, 'Return status after calling WSH_LPN_SYNC_COMM_PKG.SYNC_LPNS_TO_WMS', l_return_status);
2562 END IF;
2563 --
2564 WSH_UTIL_CORE.API_POST_CALL
2565 (
2566 p_return_status => l_return_status,
2567 x_num_warnings => l_warn_num,
2568 x_num_errors => l_error_num,
2569 p_raise_error_flag => false
2570 );
2571 --}
2572 END IF;
2573 -- LPN CONV. rv
2574 --
2575
2576 x_del_rows := l_delivery_rows;
2577 IF l_error_num > 0 THEN -- LPN CONV. rv
2578 x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
2579 ELSIF x_return_status = WSH_UTIL_CORE.G_RET_STS_SUCCESS
2580 AND l_warn_num > 0 THEN
2581 x_return_status := WSH_UTIL_CORE.G_RET_STS_WARNING ;
2582 END IF;
2583
2584 IF l_debug_on THEN
2585 WSH_DEBUG_SV.log(l_module_name, 'Return status from WSH_DELIVERY_AUTOCREATE.autocreate_deliveries', x_return_status);
2586 WSH_DEBUG_SV.pop(l_module_name);
2587 END IF;
2588 --
2589
2590 EXCEPTION
2591
2592 -- LPN CONV. rv
2593 WHEN e_return_excp THEN
2594 --
2595 FND_MESSAGE.SET_NAME('WSH','WSH_AUTOCREATE_DEL_ERROR');
2596 x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
2597 wsh_util_core.add_message(x_return_status);
2598 IF WSH_WMS_LPN_GRP.G_CALLBACK_REQUIRED = 'Y' THEN
2599 --{
2600 --
2601 IF l_debug_on THEN
2602 WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_LPN_SYNC_COMM_PKG.SYNC_LPNS_TO_WMS',WSH_DEBUG_SV.C_PROC_LEVEL);
2603 END IF;
2604 --
2605 WSH_LPN_SYNC_COMM_PKG.SYNC_LPNS_TO_WMS
2606 (
2607 p_in_rec => l_lpn_in_sync_comm_rec,
2608 x_return_status => l_return_status,
2609 x_out_rec => l_lpn_out_sync_comm_rec
2610 );
2611 --
2612 IF l_debug_on THEN
2613 WSH_DEBUG_SV.log(l_module_name, 'Return status after calling WSH_LPN_SYNC_COMM_PKG.SYNC_LPNS_TO_WMS', l_return_status);
2614 WSH_DEBUG_SV.pop(l_module_name);
2615 END IF;
2616 IF (l_return_status = WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR) THEN
2617 x_return_status := l_return_status;
2618 END IF;
2619 --}
2620 END IF;
2621 --
2622 -- LPN CONV. rv
2623 --
2624 IF c_cont%ISOPEN THEN
2625 CLOSE c_cont;
2626 END IF;
2627 IF c_matching_delivery%ISOPEN THEN
2628 CLOSE c_matching_delivery;
2629 END IF;
2630 IF l_lock_handle IS NOT NULL THEN
2631 l_lock_status := DBMS_LOCK.Release(l_lock_handle);
2632 END IF;
2633
2634 WHEN cannot_autocreate_del THEN
2635 FND_MESSAGE.SET_NAME('WSH','WSH_AUTOCREATE_DEL_ERROR');
2636 x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
2637 wsh_util_core.add_message(x_return_status);
2638 --
2639 -- LPN CONV. rv
2640 IF WSH_WMS_LPN_GRP.G_CALLBACK_REQUIRED = 'Y' THEN
2641 --{
2642 IF l_debug_on THEN
2643 WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_LPN_SYNC_COMM_PKG.SYNC_LPNS_TO_WMS',WSH_DEBUG_SV.C_PROC_LEVEL);
2644 END IF;
2645 --
2646 WSH_LPN_SYNC_COMM_PKG.SYNC_LPNS_TO_WMS
2647 (
2648 p_in_rec => l_lpn_in_sync_comm_rec,
2649 x_return_status => l_return_status,
2650 x_out_rec => l_lpn_out_sync_comm_rec
2651 );
2652 --
2653 IF l_debug_on THEN
2654 WSH_DEBUG_SV.log(l_module_name, 'Return status after calling WSH_LPN_SYNC_COMM_PKG.SYNC_LPNS_TO_WMS', l_return_status);
2655 END IF;
2656 IF (l_return_status = WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR) THEN
2657 x_return_status := l_return_status;
2658 END IF;
2659 --
2660 --}
2661 END IF;
2662 -- LPN CONV. rv
2663 IF l_debug_on THEN
2664 WSH_DEBUG_SV.logmsg(l_module_name, 'EXCEPTION CANNOT_AUTOCREATE_DEL RAISED' );
2665 END IF;
2666 --
2667 IF l_debug_on THEN
2668 WSH_DEBUG_SV.logmsg(l_module_name,'CANNOT_AUTOCREATE_DEL exception has occured.',WSH_DEBUG_SV.C_EXCEP_LEVEL);
2669 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:CANNOT_AUTOCREATE_DEL');
2670 END IF;
2671 --
2672 IF c_cont%ISOPEN THEN
2673 CLOSE c_cont;
2674 END IF;
2675 IF c_matching_delivery%ISOPEN THEN
2676 CLOSE c_matching_delivery;
2677 END IF;
2678 IF l_lock_handle IS NOT NULL THEN
2679 l_lock_status := DBMS_LOCK.Release(l_lock_handle);
2680 END IF;
2681
2682 WHEN Others THEN
2683
2684 IF c_cont%ISOPEN THEN
2685 CLOSE c_cont;
2686 END IF;
2687 IF c_matching_delivery%ISOPEN THEN
2688 CLOSE c_matching_delivery;
2689 END IF;
2690 IF l_lock_handle IS NOT NULL THEN
2691 l_lock_status := DBMS_LOCK.Release(l_lock_handle);
2692 END IF;
2693
2694 l_error_code := SQLCODE;
2695 l_error_text := SQLERRM;
2696 --
2697 IF WSH_WMS_LPN_GRP.G_CALLBACK_REQUIRED = 'Y' THEN
2698 --{
2699 IF l_debug_on THEN
2700 WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_LPN_SYNC_COMM_PKG.SYNC_LPNS_TO_WMS',WSH_DEBUG_SV.C_PROC_LEVEL);
2701 END IF;
2702 --
2703 WSH_LPN_SYNC_COMM_PKG.SYNC_LPNS_TO_WMS
2704 (
2705 p_in_rec => l_lpn_in_sync_comm_rec,
2706 x_return_status => l_return_status,
2707 x_out_rec => l_lpn_out_sync_comm_rec
2708 );
2709 --
2710 IF l_debug_on THEN
2711 WSH_DEBUG_SV.log(l_module_name, 'Return status after calling WSH_LPN_SYNC_COMM_PKG.SYNC_LPNS_TO_WMS', l_return_status);
2712 END IF;
2713 --}
2714 END IF;
2715 --
2716 -- LPN CONV. rv
2717 --
2718 IF l_debug_on THEN
2719 WSH_DEBUG_SV.logmsg(l_module_name, 'THE UNEXPECTED ERROR FROM WSH_DELIVERY_AUTOCREATE.AUTOCREATE_DELIVERIES IS ' || L_ERROR_TEXT );
2720 END IF;
2721 --
2722 wsh_util_core.default_handler('WSH_DELIVERY_AUTOCREATE.AUTOCREATE_DELIVERIES');
2723 x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR;
2724
2725 IF l_debug_on THEN
2726 WSH_DEBUG_SV.pop(l_module_name);
2727 END IF;
2728 --
2729 END autocreate_deliveries;
2730
2731 --------------------------------------------------------------------------
2732 --
2733 -- Procedure: Autocreate_del_across_orgs
2734 -- Parameters: p_line_rows, p_line_info_rows, p_init_flag,
2735 -- p_use_header_flag, p_max_detail_commit, p_del_rows
2736 -- Description: Used to automatically create deliveries across orgs
2737 -- p_line_rows - Table of delivery detail ids
2738 -- p_org_rows - a table of organization_ids. If this
2739 -- - table is not available to pass
2740 -- - then pass a dummy value in. the table
2741 -- - will get regenerated when calling
2742 -- - WSH_DELIVERY_AUTOCREATE.autocreate_del_across_orgs
2743 -- p_container_flag - 'Y' means call Autopack routine
2744 -- p_check_flag - 'Y' means delivery details will be
2745 -- grouped without creating deliveries
2746 -- p_max_detail_commit - Commits data after delivery detail
2747 -- count reaches this value
2748 -- p_del_rows - Created delivery ids
2749 -- p_grouping_rows - returns group ids for each detail,
2750 -- when p_check_flag is set to 'Y'
2751 -- x_return_status - Status of execution
2752 --------------------------------------------------------------------------
2753
2754 PROCEDURE autocreate_del_across_orgs(
2755 p_line_rows IN wsh_util_core.id_tab_type,
2756 p_org_rows IN wsh_util_core.id_tab_type,
2757 p_container_flag IN VARCHAR2 := 'N',
2758 p_check_flag IN VARCHAR2 := 'N',
2759 p_caller IN VARCHAR2 DEFAULT NULL,
2760 p_max_detail_commit IN NUMBER := 1000,
2761 p_group_by_header_flag IN VARCHAR2 DEFAULT NULL,
2762 x_del_rows OUT NOCOPY wsh_util_core.id_tab_type,
2763 x_grouping_rows OUT NOCOPY wsh_util_core.id_tab_type,
2764 x_return_status OUT NOCOPY VARCHAR2 ) IS
2765
2766 lower_bound NUMBER;
2767 upper_bound NUMBER;
2768 j NUMBER;
2769
2770 TYPE line_org_rec_type IS RECORD(
2771 line_id NUMBER,
2772 warehouse_id NUMBER);
2773 TYPE line_org_type IS TABLE OF line_org_rec_type INDEX BY BINARY_INTEGER;
2774 l_line_warehouse_ids line_org_type;
2775 t_line_warehouse_id line_org_rec_type;
2776
2777 l_param_info WSH_SHIPPING_PARAMS_PVT.Parameter_Rec_Typ;
2778
2779 temp_ids wsh_util_core.id_tab_type;
2780 curr_warehouse_id NUMBER;
2781
2782 l_count NUMBER;
2783 l_prev_count NUMBER;
2784 delcount NUMBER;
2785 l_del_rows wsh_util_core.id_tab_type;
2786 l_return_status VARCHAR2(1) := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
2787 l_warn_num NUMBER := 0;
2788 --BUG 3379499
2789 l_err_num NUMBER := 0;
2790 l_ac_dlvy_count NUMBER := 0;
2791
2792 Others EXCEPTION;
2793
2794 --
2795 l_debug_on BOOLEAN;
2796 --
2797 l_module_name CONSTANT VARCHAR2(100) := 'wsh.plsql.' || G_PKG_NAME || '.' || 'AUTOCREATE_DEL_ACROSS_ORGS';
2798 --
2799 BEGIN
2800
2801 --
2802 -- Debug Statements
2803 --
2804 --
2805 l_debug_on := WSH_DEBUG_INTERFACE.g_debug;
2806 --
2807 IF l_debug_on IS NULL
2808 THEN
2809 l_debug_on := WSH_DEBUG_SV.is_debug_enabled;
2810 END IF;
2811 --
2812 IF l_debug_on THEN
2813 WSH_DEBUG_SV.push(l_module_name);
2814 --
2815 WSH_DEBUG_SV.log(l_module_name,'P_CONTAINER_FLAG',P_CONTAINER_FLAG);
2816 WSH_DEBUG_SV.log(l_module_name,'P_CHECK_FLAG',P_CHECK_FLAG);
2817 WSH_DEBUG_SV.log(l_module_name,'P_MAX_DETAIL_COMMIT',P_MAX_DETAIL_COMMIT);
2818 WSH_DEBUG_SV.log(l_module_name,'P_CALLER',P_CALLER);
2819 END IF;
2820 --
2821 x_return_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
2822
2823 IF (p_line_rows.count = 0) THEN
2824 x_return_status := WSH_UTIL_CORE.G_RET_STS_WARNING;
2825 fnd_message.SET_name('WSH', 'WSH_NOT_ELIGIBLE_DELIVERIES');
2826 wsh_util_core.add_message(x_return_status);
2827 --
2828 -- Debug Statements
2829 --
2830 IF l_debug_on THEN
2831 WSH_DEBUG_SV.pop(l_module_name);
2832 END IF;
2833 --
2834 RETURN;
2835 END IF;
2836
2837 IF (p_org_rows.count <> 0) AND (p_line_rows.count = p_org_rows.count) THEN
2838 FOR i in 1..p_line_rows.count LOOP
2839 l_line_warehouse_ids(i).warehouse_id := p_org_rows(i);
2840 l_line_warehouse_ids(i).line_id := p_line_rows(i);
2841 END LOOP;
2842 ELSE
2843 FOR i in 1..p_line_rows.count LOOP
2844 SELECT organization_id
2845 INTO l_line_warehouse_ids(i).warehouse_id
2846 FROM wsh_delivery_details
2847 WHERE delivery_detail_id = p_line_rows(i);
2848 l_line_warehouse_ids(i).line_id := p_line_rows(i);
2849 END LOOP;
2850 END IF;
2851
2852 -- Sorting the table l_line_warehouse_ids according to the warehouse_id.
2853 lower_bound := 1;
2854 upper_bound := l_line_warehouse_ids.count;
2855 FOR i IN (lower_bound + 1)..upper_bound LOOP
2856 t_line_warehouse_id := l_line_warehouse_ids(i);
2857 j := i-1;
2858 -- Shift elements down until insertion point found
2859 WHILE ((j >= lower_bound) AND (l_line_warehouse_ids(j).warehouse_id > t_line_warehouse_id.warehouse_id)) LOOP
2860 l_line_warehouse_ids(j+1) := l_line_warehouse_ids(j);
2861 j := j-1;
2862 END LOOP;
2863 -- insert
2864 l_line_warehouse_ids(j+1) := t_line_warehouse_id;
2865 END LOOP;
2866
2867 -- Looping through l_line_warehouse_ids and grouping delivery_detail_id's with same warehouse id
2868 -- Call autocreate_deliveries to create delivery.
2869 curr_warehouse_id := l_line_warehouse_ids(1).warehouse_id;
2870 FOR i in 1..l_line_warehouse_ids.count LOOP
2871 IF ( curr_warehouse_id <> l_line_warehouse_ids(i).warehouse_id ) THEN
2872 WSH_SHIPPING_PARAMS_PVT.Get(
2873 p_organization_id => curr_warehouse_id,
2874 x_param_info => l_param_info,
2875 x_return_status => x_return_status
2876 );
2877
2878 /* Pack J: Bug fix 3043993. KVENKATE
2879 Add message if return status is not success
2880 Only modification is to add message. Since there was no code to exit or return after
2881 call to the above procedure, leaving that behavior the same.
2882 */
2883
2884 IF x_return_status <> WSH_UTIL_CORE.G_RET_STS_SUCCESS THEN
2885 FND_MESSAGE.SET_NAME('WSH', 'WSH_PARAM_NOT_DEFINED');
2886 FND_MESSAGE.SET_TOKEN('ORGANIZATION_CODE',
2887 wsh_util_core.get_org_name(curr_warehouse_id));
2888 wsh_util_core.add_message(x_return_status,l_module_name);
2889 END IF;
2890
2891
2892 -- Process Deliveries change, use set up from shipping parameter only if
2893 -- p_group_by_header_flag is NULL
2894 IF p_group_by_header_flag in ('Y', 'N') THEN
2895 l_param_info.autocreate_del_orders_flag := p_group_by_header_flag;
2896 END IF;
2897
2898 l_del_rows.delete;
2899
2900 autocreate_deliveries(
2901 p_line_rows => temp_ids,
2902 p_init_flag => 'N',
2903 p_pick_release_flag => l_param_info.autocreate_del_orders_flag,
2904 p_container_flag => p_container_flag,
2905 p_check_flag => p_check_flag,
2906 p_caller => p_caller,
2907 p_max_detail_commit => p_max_detail_commit,
2908 x_del_rows => l_del_rows,
2909 x_grouping_rows => x_grouping_rows,
2910 x_return_status => l_return_status);
2911
2912 --BUG 3379499
2913 --Keep count of calls to autocreate_deliveries
2914 l_ac_dlvy_count := l_ac_dlvy_count + 1;
2915
2916 --bug 3348614
2917 l_count:=l_del_rows.COUNT;
2918 l_prev_count:=x_del_rows.COUNT;
2919
2920 FOR delcount IN 1..l_count LOOP
2921 x_del_rows(l_prev_count+delcount):=l_del_rows(delcount);
2922 END LOOP;
2923
2924 IF l_return_status <> WSH_UTIL_CORE.G_RET_STS_SUCCESS THEN
2925 IF l_return_status = WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR THEN
2926 raise others;
2927 ELSIF l_return_status = WSH_UTIL_CORE.G_RET_STS_ERROR THEN
2928 FND_MESSAGE.SET_NAME('WSH', 'WSH_AUTOCREATE_DEL_ORG_ERR');
2929 FND_MESSAGE.SET_TOKEN('ORGANIZATION_CODE',
2930 wsh_util_core.get_org_name(curr_warehouse_id));
2931 wsh_util_core.add_message(WSH_UTIL_CORE.G_RET_STS_ERROR,l_module_name);
2932 --BUG 3379499
2933 --Keep count of errors
2934 l_err_num := l_err_num + 1;
2935 ELSIF l_return_status = WSH_UTIL_CORE.G_RET_STS_WARNING THEN
2936 FND_MESSAGE.SET_NAME('WSH', 'WSH_AUTOCREATE_DEL_ORG_WRN');
2937 FND_MESSAGE.SET_TOKEN('ORGANIZATION_CODE',
2938 wsh_util_core.get_org_name(curr_warehouse_id));
2939 wsh_util_core.add_message(WSH_UTIL_CORE.G_RET_STS_WARNING,l_module_name);
2940 l_warn_num := l_warn_num + 1;
2941 END IF;
2942 END IF;
2943 -- Bug 4658241
2944 /*wsh_tp_release.calculate_cont_del_tpdates(
2945 p_entity => 'DLVY',
2946 p_entity_ids => x_del_rows,
2947 x_return_status => l_return_status);
2948 IF l_return_status = WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR THEN
2949 raise others;
2950 ELSIF l_return_status in (WSH_UTIL_CORE.G_RET_STS_ERROR , WSH_UTIL_CORE.G_RET_STS_WARNING) THEN
2951 l_warn_num := l_warn_num + 1;
2952 END IF; */
2953 -- Bug 4658241 end
2954 temp_ids.delete;
2955 curr_warehouse_id := l_line_warehouse_ids(i).warehouse_id;
2956 END IF;
2957 temp_ids(temp_ids.count + 1) := l_line_warehouse_ids(i).line_id;
2958 END LOOP;
2959 -- Handling the case when it is the last warehouse group or the only warehouse group in the table
2960 IF temp_ids.count > 0 THEN
2961 WSH_SHIPPING_PARAMS_PVT.Get(
2962 p_organization_id => curr_warehouse_id,
2963 x_param_info => l_param_info,
2964 x_return_status => l_return_status
2965 );
2966
2967 /* Pack J: Bug fix 3043993. KVENKATE
2968 Add message if return status is not success
2969 Only modification is to add message. Since there was no code to exit or return after
2970 call to the above procedure, leaving that behavior the same.
2971 */
2972
2973 IF l_return_status <> WSH_UTIL_CORE.G_RET_STS_SUCCESS THEN
2974 FND_MESSAGE.SET_NAME('WSH', 'WSH_PARAM_NOT_DEFINED');
2975 FND_MESSAGE.SET_TOKEN('ORGANIZATION_CODE',
2976 wsh_util_core.get_org_name(curr_warehouse_id));
2977 wsh_util_core.add_message(WSH_UTIL_CORE.G_RET_STS_WARNING,l_module_name);
2978 l_warn_num := l_warn_num + 1;
2979 END IF;
2980
2981 -- Process Deliveries change, use set up from shipping parameter only if
2982 -- p_group_by_header_flag is NULL
2983 IF p_group_by_header_flag in ('Y', 'N') THEN
2984 l_param_info.autocreate_del_orders_flag := p_group_by_header_flag;
2985 END IF;
2986
2987 l_del_rows.delete;
2988 autocreate_deliveries(
2989 p_line_rows => temp_ids,
2990 p_init_flag => 'Y',
2991 p_pick_release_flag => l_param_info.autocreate_del_orders_flag,
2992 p_container_flag => p_container_flag,
2993 p_check_flag => p_check_flag,
2994 p_caller => p_caller,
2995 p_max_detail_commit => p_max_detail_commit,
2996 x_del_rows => l_del_rows,
2997 x_grouping_rows => x_grouping_rows,
2998 x_return_status => l_return_status);
2999 --BUG 3379499
3000 --Keep count of calls to autocreate_deliveries
3001 l_ac_dlvy_count := l_ac_dlvy_count + 1;
3002
3003 --bug 3348614
3004 l_count:=l_del_rows.COUNT;
3005 l_prev_count:=x_del_rows.COUNT;
3006
3007 FOR delcount IN 1..l_count LOOP
3008 x_del_rows(l_prev_count+delcount):=l_del_rows(delcount);
3009 END LOOP;
3010
3011 IF l_return_status <> WSH_UTIL_CORE.G_RET_STS_SUCCESS THEN
3012 IF l_return_status = WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR THEN
3013 raise others;
3014 ELSIF l_return_status = WSH_UTIL_CORE.G_RET_STS_ERROR THEN
3015 FND_MESSAGE.SET_NAME('WSH', 'WSH_AUTOCREATE_DEL_ORG_ERR');
3016 FND_MESSAGE.SET_TOKEN('ORGANIZATION_CODE',
3017 wsh_util_core.get_org_name(curr_warehouse_id));
3018 wsh_util_core.add_message(WSH_UTIL_CORE.G_RET_STS_ERROR,l_module_name);
3019 --BUG 3379499
3020 --Keep count of errors
3021 l_err_num := l_err_num + 1;
3022 ELSIF l_return_status = WSH_UTIL_CORE.G_RET_STS_WARNING THEN
3023 FND_MESSAGE.SET_NAME('WSH', 'WSH_AUTOCREATE_DEL_ORG_WRN');
3024 FND_MESSAGE.SET_TOKEN('ORGANIZATION_CODE',
3025 wsh_util_core.get_org_name(curr_warehouse_id));
3026 wsh_util_core.add_message(WSH_UTIL_CORE.G_RET_STS_WARNING,l_module_name);
3027 l_warn_num := l_warn_num + 1;
3028 END IF;
3029 END IF;
3030
3031 temp_ids.delete;
3032 -- Bug 4658241
3033 /*wsh_tp_release.calculate_cont_del_tpdates(
3034 p_entity => 'DLVY',
3035 p_entity_ids => x_del_rows,
3036 x_return_status => l_return_status);
3037 IF l_return_status = WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR THEN
3038 raise others;
3039 ELSIF l_return_status in (WSH_UTIL_CORE.G_RET_STS_ERROR , WSH_UTIL_CORE.G_RET_STS_WARNING) THEN
3040 l_warn_num := l_warn_num + 1;
3041 END IF; */
3042 -- Bug 4658241 end
3043 END IF;
3044
3045 --BUG 3379499
3046 --Handle return status using l_err_num and l_warn_num
3047 IF l_debug_on THEN
3048 wsh_debug_sv.log(l_module_name, 'l_ac_dlvy_count', l_ac_dlvy_count);
3049 wsh_debug_sv.log(l_module_name, 'l_err_num', l_err_num);
3050 wsh_debug_sv.log(l_module_name, 'l_warn_num', l_warn_num);
3051 END IF;
3052 IF l_err_num > 0 THEN
3053 IF l_err_num < l_ac_dlvy_count
3054 THEN
3055 x_return_status := WSH_UTIL_CORE.G_RET_STS_WARNING;
3056 ELSE
3057 x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
3058 END IF;
3059 ELSIF l_warn_num > 0 THEN
3060 x_return_status := WSH_UTIL_CORE.G_RET_STS_WARNING;
3061 END IF;
3062
3063 --
3064 -- Debug Statements
3065 --
3066 IF l_debug_on THEN
3067 WSH_DEBUG_SV.log(l_module_name,'x_del_rows ',x_del_rows.COUNT);
3068 WSH_DEBUG_SV.pop(l_module_name);
3069 END IF;
3070
3071
3072
3073 --
3074 EXCEPTION
3075 WHEN Others THEN
3076
3077 wsh_util_core.default_handler('WSH_DELIVERY_AUTOCREATE.AUTOCREATE_DEL_ACROSS_ORGS');
3078 x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR;
3079 --
3080 -- Debug Statements
3081 --
3082 IF l_debug_on THEN
3083 WSH_DEBUG_SV.logmsg(l_module_name,'Unexpected error has occured. Oracle error message is '|| SQLERRM,WSH_DEBUG_SV.C_UNEXPEC_ERR_LEVEL);
3084 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:OTHERS');
3085 END IF;
3086 --
3087 END autocreate_del_across_orgs;
3088
3089 --------------------------------------------------------------------------
3090 --
3091 -- Procedure: Delete_Empty_Deliveries
3092 -- Parameters: p_batch_id
3093 --
3094 -- Description: Used to Delete Empty Deliveries existing after Pick Release
3095 -- p_batch_id - Pick Release Batch Id
3096 -- x_return_status - Status of execution
3097 --------------------------------------------------------------------------
3098
3099 PROCEDURE Delete_Empty_Deliveries(p_batch_id IN NUMBER,
3100 x_return_status OUT NOCOPY VARCHAR2 ) IS
3101 --
3102 l_debug_on BOOLEAN;
3103 --
3104 l_module_name CONSTANT VARCHAR2(100) := 'wsh.plsql.' || G_PKG_NAME || '.' || 'DELETE_EMPTY_DELIVERIES';
3105
3106 l_gc3_is_installed VARCHAR2(1); --OTM R12
3107
3108 BEGIN
3109 --
3110 -- Debug Statements
3111 --
3112 --
3113 l_debug_on := WSH_DEBUG_INTERFACE.g_debug;
3114 --
3115 IF l_debug_on IS NULL
3116 THEN
3117 l_debug_on := WSH_DEBUG_SV.is_debug_enabled;
3118 END IF;
3119 --
3120 IF l_debug_on THEN
3121 WSH_DEBUG_SV.push(l_module_name);
3122 --
3123 WSH_DEBUG_SV.log(l_module_name,'P_BATCH_ID',P_BATCH_ID);
3124 END IF;
3125 --
3126 --
3127 -- Debug Statements
3128 --
3129 IF l_debug_on THEN
3130 WSH_DEBUG_SV.logmsg(l_module_name, 'DELETING EMPTY DELIVERIES FOR BATCH '||P_BATCH_ID );
3131 END IF;
3132 --
3133 --OTM R12
3134 l_gc3_is_installed := WSH_UTIL_CORE.G_GC3_IS_INSTALLED; -- this is global variable
3135
3136 IF l_gc3_is_installed IS NULL THEN
3137 l_gc3_is_installed := WSH_UTIL_CORE.GC3_IS_INSTALLED; -- this is actual function
3138 END IF; --
3139 --
3140
3141 IF (p_batch_id is NOT NULL and p_batch_id > 0) THEN
3142 IF l_debug_on IS NULL THEN
3143 WSH_DEBUG_SV.log(l_module_name,'Current Time is ',SYSDATE);
3144 END IF;
3145
3146 --OTM R12, allow delete of 'NS' deliveries
3147 IF (l_gc3_is_installed = 'Y') THEN
3148 DELETE FROM wsh_new_deliveries wnd
3149 WHERE batch_id = p_batch_id
3150 AND NVL(TMS_INTERFACE_FLAG, WSH_NEW_DELIVERIES_PVT.C_TMS_NOT_TO_BE_SENT)
3151 = WSH_NEW_DELIVERIES_PVT.C_TMS_NOT_TO_BE_SENT
3152 AND NOT EXISTS (
3153 SELECT 'x'
3154 FROM wsh_delivery_assignments wda
3155 WHERE wda.delivery_id = wnd.delivery_id
3156 AND wda.delivery_id IS NOT NULL);
3157 --END OTM R12
3158 ELSE
3159 DELETE FROM wsh_new_deliveries wnd
3160 WHERE batch_id = p_batch_id
3161 AND NOT EXISTS (
3162 SELECT 'x'
3163 FROM wsh_delivery_assignments wda
3164 WHERE wda.delivery_id = wnd.delivery_id
3165 AND wda.delivery_id IS NOT NULL);
3166 END IF;
3167 --
3168 -- Debug Statements
3169 --
3170 IF l_debug_on THEN
3171 WSH_DEBUG_SV.logmsg(l_module_name, 'DELETED '||SQL%ROWCOUNT||' EMPTY DELIVERIES AFTER PICK RELEASE' );
3172 WSH_DEBUG_SV.log(l_module_name,'Current Time is ',SYSDATE);
3173 END IF;
3174 --
3175
3176 END IF;
3177 x_return_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
3178
3179 --
3180 -- Debug Statements
3181 --
3182 IF l_debug_on THEN
3183 WSH_DEBUG_SV.pop(l_module_name);
3184 END IF;
3185 --
3186 EXCEPTION
3187 WHEN OTHERS THEN
3188 wsh_util_core.default_handler('WSH_DELIVERY_AUTOCREATE.DELETE_EMPTY_DELIVERIES');
3189 x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR;
3190 --
3191 -- Debug Statements
3192 --
3193 IF l_debug_on THEN
3194 WSH_DEBUG_SV.logmsg(l_module_name,'Unexpected error has occured. Oracle error message is '|| SQLERRM,WSH_DEBUG_SV.C_UNEXPEC_ERR_LEVEL);
3195 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:OTHERS');
3196 END IF;
3197 --
3198 END DELETE_EMPTY_DELIVERIES;
3199
3200 --------------------------------------------------------------------------
3201 --
3202 -- Procedure: unassign_empty_containers
3203 -- Parameters: p_delivery_id
3204 --
3205 -- Description: Used to unassign empty containers from delivery after Pick Release
3206 -- p_delivery_ids - table index by delivery ids
3207 -- x_return_status - Status of execution
3208 --------------------------------------------------------------------------
3209
3210 PROCEDURE unassign_empty_containers(
3211 p_delivery_ids IN WSH_PICK_LIST.unassign_delivery_id_type,
3212 x_return_status OUT NOCOPY VARCHAR2 ) IS
3213
3214
3215 -- LPN CONV. rv
3216 l_wms_org VARCHAR2(10) := 'N';
3217 l_sync_tmp_wms_recTbl wsh_glbl_var_strct_grp.sync_tmp_recTbl_type;
3218 l_sync_tmp_inv_recTbl wsh_glbl_var_strct_grp.sync_tmp_recTbl_type;
3219
3220 l_cnt_wms_counter NUMBER;
3221 l_cnt_inv_counter NUMBER;
3222 l_delivery_id NUMBER;
3223 l_parent_detail_id NUMBER;
3224 l_return_status VARCHAR2(10);
3225 l_num_warnings NUMBER :=0;
3226 l_num_errors NUMBER :=0;
3227 l_msg_count NUMBER;
3228 l_msg_data VARCHAR2(32767);
3229 l_index NUMBER;
3230
3231 l_del_det_id_tbl wsh_util_core.id_tab_type;
3232 l_organization_id_tbl wsh_util_core.id_tab_type;
3233 l_line_direction_tbl wsh_util_core.column_tab_type;
3234
3235 l_lpn_in_sync_comm_rec WSH_GLBL_VAR_STRCT_GRP.lpn_sync_comm_in_rec_type;
3236 l_lpn_out_sync_comm_rec WSH_GLBL_VAR_STRCT_GRP.lpn_sync_comm_out_rec_type;
3237 l_operation_type VARCHAR2(100);
3238
3239 CURSOR l_detail_assgn_info_csr (p_detail_id IN NUMBER) is
3240 SELECT delivery_id,
3241 parent_delivery_detail_id
3242 FROM wsh_delivery_assignments_v
3243 where delivery_detail_id = p_detail_id;
3244 -- LPN CONV. rv
3245
3246 -- bug 4416863
3247 l_gross_weight_tbl wsh_util_core.id_tab_type;
3248 l_net_weight_tbl wsh_util_core.id_tab_type;
3249 l_volume_tbl wsh_util_core.id_tab_type;
3250 l_filled_volume_tbl wsh_util_core.id_tab_type;
3251
3252 --
3253 l_debug_on BOOLEAN;
3254 --
3255 l_module_name CONSTANT VARCHAR2(100) := 'wsh.plsql.' || G_PKG_NAME || '.' || 'UNASSIGN_EMPTY_CONTAINERS';
3256 --
3257 BEGIN
3258
3259 --
3260 -- Debug Statements
3261 --
3262 --
3263 l_debug_on := WSH_DEBUG_INTERFACE.g_debug;
3264 --
3265 IF l_debug_on IS NULL
3266 THEN
3267 l_debug_on := WSH_DEBUG_SV.is_debug_enabled;
3268 END IF;
3269 --
3270 IF l_debug_on THEN
3271 WSH_DEBUG_SV.push(l_module_name);
3272 END IF;
3273 --
3274 IF (p_delivery_ids.count > 0) THEN
3275 --
3276 -- Debug Statements
3277 --
3278 IF l_debug_on THEN
3279 WSH_DEBUG_SV.logmsg(l_module_name, 'UNASSIGNING EMPTY CONTAINERS FROM DELIVERIES' );
3280 END IF;
3281 --
3282 FOR i in p_delivery_ids.FIRST .. p_delivery_ids.LAST LOOP
3283 --
3284 -- Debug Statements
3285 --
3286 IF l_debug_on THEN
3287 WSH_DEBUG_SV.logmsg(l_module_name, I );
3288 END IF;
3289 --
3290 END LOOP;
3291 END IF;
3292 IF l_debug_on THEN
3293 WSH_DEBUG_SV.log(l_module_name,'Current Time is ',SYSDATE);
3294 END IF;
3295 IF (p_delivery_ids.count > 0) THEN
3296 -- Bug 2543667 : Grouping Attributes of LPN are retained after backordering at Pick Release
3297 -- Empty Containers should not have any grouping attributes
3298 FORALL l_counter in INDICES OF p_delivery_ids
3299 UPDATE wsh_delivery_details wdd
3300 SET wdd.customer_id = NULL,
3301 wdd.ship_to_location_id = NULL,
3302 wdd.intmed_ship_to_location_id = NULL,
3303 wdd.fob_code = NULL,
3304 wdd.freight_terms_code = NULL,
3305 wdd.ship_method_code = NULL,
3306 wdd.deliver_to_location_id = NULL
3307 WHERE wdd.delivery_detail_id in (
3308 SELECT wda.delivery_detail_id
3309 FROM wsh_delivery_assignments_v wda
3310 WHERE wda.delivery_id = p_delivery_ids(l_counter)
3311 AND wda.delivery_id IS NOT NULL
3312 AND wda.delivery_detail_id not in (
3313 SELECT wda1.delivery_detail_id
3314 FROM wsh_delivery_assignments_v wda1
3315 START WITH wda1.delivery_detail_id in (
3316 SELECT wda2.delivery_detail_id
3317 FROM wsh_delivery_details wdd1 ,
3318 wsh_delivery_assignments_v wda2
3319 WHERE wda2.delivery_id = p_delivery_ids(l_counter)
3320 AND wda2.delivery_detail_id = wdd1.delivery_detail_id
3321 AND wdd1.container_flag = 'N')
3322 CONNECT BY wda1.delivery_detail_id = prior wda1.parent_delivery_detail_id))
3323 AND wdd.container_flag = 'Y'
3324 RETURNING delivery_detail_id, organization_id, line_direction, gross_weight,
3325 net_weight, volume, filled_volume BULK COLLECT into l_del_det_id_tbl,
3326 l_organization_id_tbl, l_line_direction_tbl, l_gross_weight_tbl,
3327 l_net_weight_tbl, l_volume_tbl, l_filled_volume_tbl; -- LPN CONV. rv
3328
3329 --
3330 -- Debug Statements
3331 --
3332 IF l_debug_on THEN
3333 WSH_DEBUG_SV.logmsg(l_module_name, 'UPDATED '||SQL%ROWCOUNT||' RECORDS IN WSH_DELIVERY_DETAILS' );
3334 END IF;
3335 --
3336 -- LPN CONV. rv
3337 IF WSH_WMS_LPN_GRP.G_CALLBACK_REQUIRED = 'Y'
3338 AND l_del_det_id_tbl.count > 0
3339 THEN
3340 --{
3341 --
3342 l_index := l_del_det_id_tbl.first;
3343 l_cnt_wms_counter := 1;
3344 l_cnt_inv_counter := 1;
3345
3346 WHILE (l_index is not null)
3347 LOOP
3348 --{
3349 l_delivery_id := NULL;
3350 l_parent_detail_id := NULL;
3351 --
3352 open l_detail_assgn_info_csr(l_del_det_id_tbl(l_index));
3353 fetch l_detail_assgn_info_csr into l_delivery_id, l_parent_detail_id;
3354 close l_detail_assgn_info_csr;
3355
3356 l_wms_org := wsh_util_validate.check_wms_org(l_organization_id_tbl(l_index));
3357 --
3358 IF (l_wms_org = 'Y' and nvl(l_line_direction_tbl(l_index), 'O') in ('O', 'IO')) THEN
3359 l_sync_tmp_wms_recTbl.delivery_detail_id_tbl(l_cnt_wms_counter) := l_del_det_id_tbl(l_index);
3360 l_sync_tmp_wms_recTbl.delivery_id_tbl(l_cnt_wms_counter) := l_delivery_id;
3361 l_sync_tmp_wms_recTbl.parent_detail_id_tbl(l_cnt_wms_counter) := l_parent_detail_id;
3362 l_sync_tmp_wms_recTbl.operation_type_tbl(l_cnt_wms_counter) := 'UPDATE';
3363 l_cnt_wms_counter := l_cnt_wms_counter + 1;
3364
3365 ELSIF (l_wms_org = 'N' and nvl(l_line_direction_tbl(l_index), 'O') in ('O', 'IO')) THEN
3366 l_sync_tmp_inv_recTbl.delivery_detail_id_tbl(l_cnt_inv_counter) := l_del_det_id_tbl(l_index);
3367 l_sync_tmp_inv_recTbl.delivery_id_tbl(l_cnt_inv_counter) := l_delivery_id;
3368 l_sync_tmp_inv_recTbl.parent_detail_id_tbl(l_cnt_inv_counter) := l_parent_detail_id;
3369 l_sync_tmp_inv_recTbl.operation_type_tbl(l_cnt_inv_counter) := 'UPDATE';
3370 l_cnt_inv_counter := l_cnt_inv_counter + 1;
3371 END IF;
3372
3373 l_index := l_del_det_id_tbl.next(l_index);
3374 --}
3375 END LOOP;
3376 --
3377 --}
3378 END IF;
3379
3380 -- LPN CONV. rv
3381 --
3382 IF l_debug_on THEN
3383 wsh_debug_sv.LOG(l_module_name, 'Count of l_sync_tmp_wms_recTbl', l_sync_tmp_wms_recTbl.delivery_detail_id_tbl.count);
3384 wsh_debug_sv.LOG(l_module_name, 'Count of l_sync_tmp_inv_recTbl', l_sync_tmp_inv_recTbl.delivery_detail_id_tbl.count);
3385 END IF;
3386 --
3387 --
3388 IF WSH_WMS_LPN_GRP.G_CALLBACK_REQUIRED = 'Y'
3389 THEN
3390 --{
3391 --
3392 l_operation_type := 'UPDATE';
3393 --
3394 IF ( WSH_WMS_LPN_GRP.GK_WMS_UPD_GRP
3395 OR WSH_WMS_LPN_GRP.GK_WMS_UPD_WV
3396 OR WSH_WMS_LPN_GRP.GK_WMS_UPD_FILL
3397 )
3398 AND l_sync_tmp_wms_recTbl.delivery_detail_id_tbl.count > 0
3399 THEN
3400 --{
3401 --
3402 IF l_debug_on THEN
3403 WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_WMS_SYNC_TMP_PKG.MERGE_BULK',WSH_DEBUG_SV.C_PROC_LEVEL);
3404 END IF;
3405 --
3406 WSH_WMS_SYNC_TMP_PKG.MERGE_BULK
3407 (
3408 p_sync_tmp_recTbl => l_sync_tmp_wms_recTbl,
3409 x_return_status => l_return_status,
3410 p_operation_type => l_operation_type
3411 );
3412 --
3413 IF l_debug_on THEN
3414 wsh_debug_sv.log(l_module_name, 'Return status after the call to WSH_WMS_SYNC_TMP_PKG.MERGE_BULK is ', l_return_status);
3415 END IF;
3416 --
3417 WSH_UTIL_CORE.API_POST_CALL
3418 (
3419 p_return_status => l_return_status,
3420 x_num_warnings => l_num_warnings,
3421 x_num_errors => l_num_errors
3422 );
3423 --}
3424 ELSIF ( WSH_WMS_LPN_GRP.GK_INV_UPD_GRP
3425 OR WSH_WMS_LPN_GRP.GK_INV_UPD_WV
3426 OR WSH_WMS_LPN_GRP.GK_INV_UPD_FILL
3427 )
3428 AND l_sync_tmp_inv_recTbl.delivery_detail_id_tbl.count > 0
3429 THEN
3430 --{
3431 --
3432 IF l_debug_on THEN
3433 WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_WMS_SYNC_TMP_PKG.MERGE_BULK',WSH_DEBUG_SV.C_PROC_LEVEL);
3434 END IF;
3435 --
3436 WSH_WMS_SYNC_TMP_PKG.MERGE_BULK
3437 (
3438 p_sync_tmp_recTbl => l_sync_tmp_inv_recTbl,
3439 x_return_status => l_return_status,
3440 p_operation_type => l_operation_type
3441 );
3442
3443 --
3444 IF l_debug_on THEN
3445 wsh_debug_sv.log(l_module_name, 'Return status after the call to WSH_WMS_SYNC_TMP_PKG.MERGE_BULK is ', l_return_status);
3446 END IF;
3447 --
3448 WSH_UTIL_CORE.API_POST_CALL
3449 (
3450 p_return_status => l_return_status,
3451 x_num_warnings => l_num_warnings,
3452 x_num_errors => l_num_errors
3453 );
3454 --}
3455 END IF;
3456 --}
3457 END IF;
3458 --
3459 -- Now, we need to again call the merge APIs for 'PRIOR' for the same
3460 -- set of delivery detail ids
3461 --
3462 IF WSH_WMS_LPN_GRP.G_CALLBACK_REQUIRED = 'Y'
3463 THEN
3464 --{
3465 --
3466 l_operation_type := 'PRIOR';
3467 l_sync_tmp_inv_recTbl.operation_type_tbl(1) := 'PRIOR';
3468 --
3469 IF ( WSH_WMS_LPN_GRP.GK_WMS_UNPACK
3470 OR WSH_WMS_LPN_GRP.GK_WMS_UNASSIGN_DLVY
3471 )
3472 AND l_sync_tmp_wms_recTbl.delivery_detail_id_tbl.count > 0
3473 THEN
3474 --{
3475 --
3476 IF l_debug_on THEN
3477 WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_WMS_SYNC_TMP_PKG.MERGE_BULK',WSH_DEBUG_SV.C_PROC_LEVEL);
3478 END IF;
3479 --
3480 WSH_WMS_SYNC_TMP_PKG.MERGE_BULK
3481 (
3482 p_sync_tmp_recTbl => l_sync_tmp_wms_recTbl,
3483 x_return_status => l_return_status,
3484 p_operation_type => l_operation_type
3485 );
3486 --
3487 IF l_debug_on THEN
3488 wsh_debug_sv.log(l_module_name, 'Return status after the call to WSH_WMS_SYNC_TMP_PKG.MERGE_BULK is ', l_return_status);
3489 END IF;
3490 --
3491 WSH_UTIL_CORE.API_POST_CALL
3492 (
3493 p_return_status => l_return_status,
3494 x_num_warnings => l_num_warnings,
3495 x_num_errors => l_num_errors
3496 );
3497 --}
3498 ELSIF ( WSH_WMS_LPN_GRP.GK_INV_UNPACK
3499 OR WSH_WMS_LPN_GRP.GK_INV_UNASSIGN_DLVY
3500 )
3501 AND l_sync_tmp_inv_recTbl.delivery_detail_id_tbl.count > 0
3502 THEN
3503 --{
3504 --
3505 IF l_debug_on THEN
3506 WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_WMS_SYNC_TMP_PKG.MERGE_BULK',WSH_DEBUG_SV.C_PROC_LEVEL);
3507 END IF;
3508 --
3509 WSH_WMS_SYNC_TMP_PKG.MERGE_BULK
3510 (
3511 p_sync_tmp_recTbl => l_sync_tmp_inv_recTbl,
3512 x_return_status => l_return_status,
3513 p_operation_type => l_operation_type
3514 );
3515
3516 --
3517 IF l_debug_on THEN
3518 wsh_debug_sv.log(l_module_name, 'Return status after the call to WSH_WMS_SYNC_TMP_PKG.MERGE_BULK is ', l_return_status);
3519 END IF;
3520 --
3521 WSH_UTIL_CORE.API_POST_CALL
3522 (
3523 p_return_status => l_return_status,
3524 x_num_warnings => l_num_warnings,
3525 x_num_errors => l_num_errors
3526 );
3527 --}
3528 END IF;
3529 --}
3530 END IF;
3531 -- LPN CONV. rv
3532
3533 -- bug 4416863
3534 --
3535 -- Bug 5548080 : Check that the table actually has records in it before attempting to loop
3536 --
3537 IF l_del_det_id_tbl.COUNT > 0 THEN
3538 --{
3539 IF l_debug_on THEN
3540 wsh_debug_sv.log(l_module_name, 'l_del_det_id_tbl.COUNT', l_del_det_id_tbl.COUNT);
3541 END IF;
3542 --
3543 FOR l_counter in l_del_det_id_tbl.FIRST .. l_del_det_id_tbl.LAST LOOP
3544 IF l_debug_on THEN
3545 WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_WV_UTILS.DD_WV_Post_Process',WSH_DEBUG_SV.C_PROC_LEVEL);
3546 END IF;
3547
3548 WSH_WV_UTILS.DD_WV_Post_Process(
3549 p_delivery_detail_id => l_del_det_id_tbl(l_counter),
3550 p_diff_gross_wt => -1 * nvl(l_gross_weight_tbl(l_counter), 0),
3551 p_diff_net_wt => -1 * nvl(l_net_weight_tbl(l_counter), 0),
3552 p_diff_volume => -1 * nvl(l_volume_tbl(l_counter), 0),
3553 p_diff_fill_volume => -1 * nvl(l_filled_volume_tbl(l_counter), 0),
3554 x_return_status => l_return_status);
3555
3556 IF (l_return_status <> WSH_UTIL_CORE.G_RET_STS_SUCCESS) THEN
3557 --
3558 x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
3559 WSH_UTIL_CORE.Add_Message(x_return_status);
3560 IF l_debug_on THEN
3561 WSH_DEBUG_SV.log(l_module_name,'Return Status',x_return_status);
3562 WSH_DEBUG_SV.pop(l_module_name);
3563 END IF;
3564 return;
3565 END IF;
3566 END LOOP;
3567 --}
3568 END IF; --end Bug 5548080
3569 -- end bug 4416863
3570
3571 -- Now it is a simple update
3572 -- LPN CONV. rv
3573
3574 FORALL l_counter in indices of l_del_det_id_tbl
3575 UPDATE WSH_DELIVERY_ASSIGNMENTS_V
3576 SET DELIVERY_ID = NULL,
3577 PARENT_DELIVERY_DETAIL_ID = NULL
3578 WHERE DELIVERY_DETAIL_ID = l_del_det_id_tbl(l_counter);
3579
3580 -- MDC: Delete the consol record, if exists.
3581 WSH_DELIVERY_DETAILS_ACTIONS.Delete_Consol_Record(
3582 p_detail_id_tab => l_del_det_id_tbl,
3583 x_return_status => x_return_status);
3584
3585
3586
3587 /*
3588 FORALL l_counter in p_delivery_ids.FIRST .. p_delivery_ids.LAST
3589 UPDATE wsh_delivery_assignments wda
3590 SET wda.parent_delivery_detail_id = null,
3591 wda.delivery_id = null
3592 WHERE wda.delivery_id = p_delivery_ids(l_counter)
3593 AND wda.delivery_id IS NOT NULL
3594 AND wda.delivery_detail_id not in(
3595 SELECT wda1.delivery_detail_id
3596 FROM wsh_delivery_assignments wda1
3597 START WITH wda1.delivery_detail_id in (
3598 SELECT wda2.delivery_detail_id
3599 FROM wsh_delivery_details wdd ,
3600 wsh_delivery_assignments wda2
3601 WHERE wda2.delivery_id = wda.delivery_id
3602 AND wda2.delivery_detail_id = wdd.delivery_detail_id
3603 AND wdd.container_flag = 'N')
3604 CONNECT BY wda1.delivery_detail_id = prior wda1.parent_delivery_detail_id);
3605 */
3606 -- LPN CONV. rv
3607 --
3608 -- Debug Statements
3609 --
3610 IF l_debug_on THEN
3611 WSH_DEBUG_SV.logmsg(l_module_name, 'UPDATED '||SQL%ROWCOUNT||' RECORDS IN WSH_DELIVERY_ASSIGNMENTS' );
3612 END IF;
3613 --
3614 END IF;
3615 IF l_debug_on THEN
3616 WSH_DEBUG_SV.log(l_module_name,'Current Time is ',SYSDATE);
3617 END IF;
3618
3619 -- LPN CONV. rv
3620 --
3621 IF WSH_WMS_LPN_GRP.G_CALLBACK_REQUIRED = 'Y'
3622 THEN
3623 --{
3624
3625 IF l_debug_on THEN
3626 WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_LPN_SYNC_COMM_PKG.SYNC_LPNS_TO_WMS',WSH_DEBUG_SV.C_PROC_LEVEL);
3627 END IF;
3628 --
3629 WSH_LPN_SYNC_COMM_PKG.SYNC_LPNS_TO_WMS
3630 (
3631 p_in_rec => l_lpn_in_sync_comm_rec,
3632 x_return_status => l_return_status,
3633 x_out_rec => l_lpn_out_sync_comm_rec
3634 );
3635 --
3636 IF l_debug_on THEN
3637 WSH_DEBUG_SV.log(l_module_name, 'Return status after calling WSH_LPN_SYNC_COMM_PKG.SYNC_LPNS_TO_WMS', l_return_status);
3638 END IF;
3639 --
3640 WSH_UTIL_CORE.API_POST_CALL
3641 (
3642 p_return_status => l_return_status,
3643 x_num_warnings => l_num_warnings,
3644 x_num_errors => l_num_errors,
3645 p_raise_error_flag => false
3646 );
3647 --}
3648 END IF;
3649 --
3650 --
3651 --
3652 --
3653 IF l_num_errors > 0 THEN
3654 x_return_status := wsh_util_core.g_ret_sts_error;
3655 ELSIF l_num_warnings > 0 THEN
3656 x_return_status := wsh_util_core.g_ret_sts_warning;
3657 ELSE
3658 x_return_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
3659 END IF;
3660 -- LPN CONV. rv
3661 --
3662 -- Debug Statements
3663 --
3664 IF l_debug_on THEN
3665 WSH_DEBUG_SV.logmsg(l_module_name, 'END OF API FOR UNASSIGNING EMPTY CONTAINERS FROM DELIVERIES' );
3666 END IF;
3667 --
3668 --x_return_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS; LPN CONV. rv
3669
3670 --
3671 -- Debug Statements
3672 --
3673 IF l_debug_on THEN
3674 WSH_DEBUG_SV.pop(l_module_name);
3675 END IF;
3676 --
3677 EXCEPTION
3678 WHEN FND_API.G_EXC_ERROR THEN
3679 --
3680 x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
3681 --
3682 IF l_debug_on THEN
3683 WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_LPN_SYNC_COMM_PKG.SYNC_LPNS_TO_WMS FROM FND_API.G_EXC_ERROR',WSH_DEBUG_SV.C_PROC_LEVEL);
3684 END IF;
3685 --
3686 IF WSH_WMS_LPN_GRP.G_CALLBACK_REQUIRED = 'Y'
3687 THEN
3688 --{
3689 IF l_debug_on THEN
3690 WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_LPN_SYNC_COMM_PKG.SYNC_LPNS_TO_WMS',WSH_DEBUG_SV.C_PROC_LEVEL);
3691 END IF;
3692 --
3693 WSH_LPN_SYNC_COMM_PKG.SYNC_LPNS_TO_WMS
3694 (
3695 p_in_rec => l_lpn_in_sync_comm_rec,
3696 x_return_status => l_return_status,
3697 x_out_rec => l_lpn_out_sync_comm_rec
3698 );
3699 --
3700 IF l_debug_on THEN
3701 WSH_DEBUG_SV.log(l_module_name, 'Return status after calling WSH_LPN_SYNC_COMM_PKG.SYNC_LPNS_TO_WMS', l_return_status);
3702 END IF;
3703 IF (l_return_status = WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR) THEN
3704 x_return_status := l_return_status;
3705 END IF;
3706 --}
3707 END IF;
3708 --
3709 -- LPN CONV. rv
3710 --
3711 IF l_debug_on THEN
3712 WSH_DEBUG_SV.logmsg(l_module_name,'FND_API.G_EXC_ERROR exception has occured ',WSH_DEBUG_SV.C_EXCEP_LEVEL);
3713 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:FND_API.G_EXC_ERROR');
3714 END IF;
3715 --
3716 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
3717 --
3718 x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR;
3719 --
3720 IF l_debug_on THEN
3721 WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_LPN_SYNC_COMM_PKG.SYNC_LPNS_TO_WMS FROM FND_API.G_EXC_UNEXPECTED_ERROR',WSH_DEBUG_SV.C_PROC_LEVEL);
3722 END IF;
3723 --
3724 IF WSH_WMS_LPN_GRP.G_CALLBACK_REQUIRED = 'Y'
3725 THEN
3726 --{
3727
3728 IF l_debug_on THEN
3729 WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_LPN_SYNC_COMM_PKG.SYNC_LPNS_TO_WMS',WSH_DEBUG_SV.C_PROC_LEVEL);
3730 END IF;
3731 --
3732
3733 WSH_LPN_SYNC_COMM_PKG.SYNC_LPNS_TO_WMS
3734 (
3735 p_in_rec => l_lpn_in_sync_comm_rec,
3736 x_return_status => l_return_status,
3737 x_out_rec => l_lpn_out_sync_comm_rec
3738 );
3739 --
3740 IF l_debug_on THEN
3741 WSH_DEBUG_SV.log(l_module_name, 'Return status after calling WSH_LPN_SYNC_COMM_PKG.SYNC_LPNS_TO_WMS', l_return_status);
3742 END IF;
3743 IF (l_return_status = WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR) THEN
3744 x_return_status := l_return_status;
3745 END IF;
3746 --}
3747 END IF;
3748 --
3749 -- LPN CONV. rv
3750 --
3751 IF l_debug_on THEN
3752 WSH_DEBUG_SV.logmsg(l_module_name,'FND_API.G_EXC_UNEXPECTED_ERROR exception has occured. Oracle error message is '|| SQLERRM,WSH_DEBUG_SV.C_UNEXPEC_ERR_LEVEL);
3753 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:FND_API.G_EXC_UNEXPECTED_ERROR');
3754 END IF;
3755 --
3756 WHEN OTHERS THEN
3757 --
3758 IF l_debug_on THEN
3759 WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_LPN_SYNC_COMM_PKG.SYNC_LPNS_TO_WMS FROM WHEN OTHERS',WSH_DEBUG_SV.C_PROC_LEVEL);
3760 END IF;
3761 --
3762 IF WSH_WMS_LPN_GRP.G_CALLBACK_REQUIRED = 'Y'
3763 THEN
3764 --{
3765 IF l_debug_on THEN
3766 WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_LPN_SYNC_COMM_PKG.SYNC_LPNS_TO_WMS',WSH_DEBUG_SV.C_PROC_LEVEL);
3767 END IF;
3768 --
3769
3770 WSH_LPN_SYNC_COMM_PKG.SYNC_LPNS_TO_WMS
3771 (
3772 p_in_rec => l_lpn_in_sync_comm_rec,
3773 x_return_status => l_return_status,
3774 x_out_rec => l_lpn_out_sync_comm_rec
3775 );
3776 --
3777 IF l_debug_on THEN
3778 WSH_DEBUG_SV.log(l_module_name, 'Return status after calling WSH_LPN_SYNC_COMM_PKG.SYNC_LPNS_TO_WMS', l_return_status);
3779 END IF;
3780 --}
3781 END IF;
3782 --
3783 -- LPN CONV. rv
3784 --
3785 wsh_util_core.default_handler('WSH_DELIVERY_AUTOCREATE.unassign_empty_containers');
3786 x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR;
3787 --
3788 -- Debug Statements
3789 --
3790 IF l_debug_on THEN
3791 WSH_DEBUG_SV.logmsg(l_module_name,'Unexpected error has occured. Oracle error message is '|| SQLERRM,WSH_DEBUG_SV.C_UNEXPEC_ERR_LEVEL);
3792 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:OTHERS');
3793 END IF;
3794 --
3795 END unassign_empty_containers;
3796
3797 PROCEDURE Reset_WSH_TMP IS
3798
3799 BEGIN
3800
3801 delete from wsh_tmp;
3802
3803 END Reset_WSH_TMP;
3804
3805 /**________________________________________________________________________
3806 --
3807 -- Name:
3808 -- Autocreate_Consol_Del
3809 --
3810 -- Purpose:
3811 -- This API takes in a table of child deliveries and delivery attributes,
3812 -- and creates a consolidation delivery. It currently assumes that
3813 -- all the child deliveries can be grouped together and assigned to
3814 -- a single parent delivery when called by the WSH CONSOL SRS.
3815 -- Parameters:
3816 -- p_del_attributes_tab: Table of deliveries and attributes that need to
3817 -- have parent delivery autocreated.
3818 -- p_caller: Calling entity/action
3819 -- x_parent_del_tab: Delivery ids of the newly created parent deliveries.
3820 -- x_return_status: status.
3821 **/
3822
3823 PROCEDURE Autocreate_Consol_Delivery(
3824 p_del_attributes_tab IN WSH_NEW_DELIVERIES_PVT.Delivery_Attr_Tbl_Type,
3825 p_caller IN VARCHAR2,
3826 p_trip_prefix IN VARCHAR2,
3827 x_parent_del_id OUT NOCOPY NUMBER,
3828 x_parent_trip_id OUT NOCOPY NUMBER,
3829 x_return_status OUT NOCOPY VARCHAR2) IS
3830
3831
3832 CURSOR c_trip_info(p_delivery_id IN NUMBER) IS
3833 SELECT s1.trip_id,
3834 NVL(d.ignore_for_planning, 'N') --OTM R12, delivery ignore same as trip's
3835 FROM wsh_delivery_legs l, wsh_trip_stops s1, wsh_new_deliveries d
3836 WHERE l.delivery_id = p_delivery_id
3837 AND d.delivery_id = l.delivery_id
3838 AND s1.stop_id = l.pick_up_stop_id
3839 AND s1.stop_location_id = d.initial_pickup_location_id;
3840
3841 CURSOR c_empty_stops(p_trip_id NUMBER) IS
3842 SELECT wts.stop_id
3843 FROM wsh_trip_stops wts
3844 WHERE wts.trip_id = p_trip_id
3845 AND NOT EXISTS (
3846 SELECT wdl.delivery_leg_id
3847 FROM wsh_delivery_legs wdl
3848 WHERE wdl.pick_up_stop_id = wts.stop_id
3849 OR wdl.drop_off_stop_id = wts.stop_id
3850 AND rownum = 1);
3851
3852
3853 l_trip_id NUMBER;
3854 l_trip_id_temp NUMBER;
3855 -- l_trip_info_rec_tab is table of c_trip_info%rowtype index by binary_integer;
3856
3857 l_del_attributes WSH_NEW_DELIVERIES_PVT.Delivery_Rec_Type;
3858 l_del_tab WSH_UTIL_CORE.id_tab_type;
3859 l_trip_del_tab WSH_UTIL_CORE.id_tab_type;
3860 l_intermediate_loc_tab WSH_UTIL_CORE.id_tab_type;
3861 l_pickup_stop_id NUMBER;
3862 l_dropoff_stop_id NUMBER;
3863 l_caller VARCHAR2(30);
3864 l_delivery_leg_id_dummy NUMBER;
3865 l_intermediate_loc_id NUMBER;
3866 l_msg_count NUMBER;
3867 l_msg_data VARCHAR2(2000);
3868 i NUMBER;
3869 j NUMBER := 0;
3870 k NUMBER := 0;
3871 l_weight_uom_code VARCHAR2(3);
3872 l_volume_uom_code VARCHAR2(3);
3873 l_dummy VARCHAR2(1);
3874 l_rowid VARCHAR2(30);
3875 l_delivery_id NUMBER;
3876 l_delivery_name VARCHAR2(30);
3877 l_delivery_id_tab WSH_UTIL_CORE.id_tab_type;
3878 l_valid_trip VARCHAR2(1);
3879 l_trip_name_tab wsh_util_core.Column_Tab_Type;
3880 l_trip_id_tab wsh_util_core.id_tab_type;
3881 l_empty_stops_tab wsh_util_core.id_tab_type;
3882 l_transit_time NUMBER;
3883 l_deconsol_do_date DATE;
3884 l_num_warnings NUMBER := 0;
3885 l_num_errors NUMBER := 0;
3886 l_return_status VARCHAR2(30);
3887
3888 WSH_INVALID_TRIPS EXCEPTION;
3889
3890 --OTM R12, changes for MDC
3891 l_non_trip_del_tab WSH_UTIL_CORE.id_tab_type;
3892 l_non_trip_del_count NUMBER;
3893 l_trip_ignore VARCHAR2(1);
3894 l_trip_ignore_temp VARCHAR2(1);
3895 l_gc3_is_installed VARCHAR2(1);
3896 l_otm_trip_tab WSH_UTIL_CORE.id_tab_type;
3897 --END OTM R12
3898
3899 l_module_name CONSTANT VARCHAR2(100) := 'wsh.plsql.' || G_PKG_NAME || '.' || 'Autocreate_Consol_Delivery';
3900 l_debug_on BOOLEAN;
3901
3902 BEGIN
3903
3904
3905 l_debug_on := WSH_DEBUG_INTERFACE.g_debug;
3906 --
3907 IF l_debug_on IS NULL
3908 THEN
3909 l_debug_on := WSH_DEBUG_SV.is_debug_enabled;
3910 END IF;
3911 --
3912 IF l_debug_on THEN
3913 wsh_debug_sv.push (l_module_name);
3914 WSH_DEBUG_SV.log(l_module_name,'p_caller', p_caller);
3915 WSH_DEBUG_SV.log(l_module_name,'p_trip_prefix', p_trip_prefix);
3916 END IF;
3917
3918 --OTM R12, initialize
3919 l_non_trip_del_count := 1;
3920 l_gc3_is_installed := WSH_UTIL_CORE.G_GC3_IS_INSTALLED; -- this is global variable
3921
3922 IF l_gc3_is_installed IS NULL THEN
3923 l_gc3_is_installed := WSH_UTIL_CORE.GC3_IS_INSTALLED; -- this is actual function
3924 END IF;
3925 --END OTM R12
3926
3927 -- If the caller is consolidation SRS program then
3928 -- we can assume that all the child deliveries can
3929 -- be grouped into one parent delivery.
3930 IF p_caller <> 'WSH_CONSOL_SRS' THEN
3931
3932 -- check if the deliveries are attached to common trips.
3933 -- If there is a common trip we will assign the consol delivery to that trip.
3934
3935 i := p_del_attributes_tab.FIRST;
3936 WHILE i is not NULL LOOP
3937 j := j+1;
3938 l_del_tab(j) := p_del_attributes_tab(i).delivery_id;
3939
3940 OPEN c_trip_info(p_del_attributes_tab(i).delivery_id);
3941 FETCH c_trip_info INTO l_trip_id_temp, l_trip_ignore_temp;
3942
3943 IF (c_trip_info%FOUND) THEN
3944 IF l_trip_id IS NULL THEN
3945 l_trip_id := l_trip_id_temp;
3946 l_trip_ignore := l_trip_ignore_temp; --OTM R12, saving the ignore for planning status
3947 ELSIF l_trip_id <> l_trip_id_temp THEN
3948 CLOSE c_trip_info;
3949 RAISE WSH_INVALID_TRIPS;
3950 END IF;
3951 k := k+1;
3952 l_trip_del_tab(k) := p_del_attributes_tab(i).delivery_id;
3953 IF l_debug_on THEN
3954 WSH_DEBUG_SV.log(l_module_name,'l_trip_del_tab: '||k,l_trip_del_tab(k));
3955 END IF;
3956 END IF;
3957
3958 --OTM R12, get the non trip deliveries for ignore for planning action
3959 IF (c_trip_info%NOTFOUND OR l_trip_id_temp IS NULL) THEN
3960 l_non_trip_del_tab(l_non_trip_del_count) := p_del_attributes_tab(i).delivery_id;
3961
3962 IF l_debug_on THEN
3963 WSH_DEBUG_SV.log(l_module_name,'l_non_trip_del_tab: '||l_non_trip_del_count,l_non_trip_del_tab(l_non_trip_del_count));
3964 END IF;
3965
3966 l_non_trip_del_count := l_non_trip_del_count + 1;
3967 END IF;
3968 --END OTM R12
3969
3970 CLOSE c_trip_info;
3971
3972 i := p_del_attributes_tab.next(i);
3973 END LOOP;
3974
3975 -- Check if we can consolidate the deliveries together
3976
3977 IF l_debug_on THEN
3978 WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_FTE_COMP_CONSTRAINT_GRP.is_valid_consol',WSH_DEBUG_SV.C_PROC_LEVEL);
3979 END IF;
3980
3981 WSH_FTE_COMP_CONSTRAINT_GRP.is_valid_consol(
3982 p_init_msg_list => fnd_api.g_false,
3983 p_input_delivery_id_tab => l_del_tab,
3984 p_target_consol_delivery_id => NULL,
3985 x_deconsolidation_location => l_intermediate_loc_id,
3986 x_return_status => l_return_status,
3987 x_msg_count => l_msg_count,
3988 x_msg_data => l_msg_data
3989 );
3990
3991 wsh_util_core.api_post_call
3992 (
3993 p_return_status => l_return_status,
3994 x_num_warnings => l_num_warnings,
3995 x_num_errors => l_num_errors
3996 );
3997
3998 l_del_tab.delete;
3999 IF l_debug_on THEN
4000 WSH_DEBUG_SV.log(l_module_name,'l_intermediate_loc_id', l_intermediate_loc_id);
4001 END IF;
4002
4003
4004
4005 END IF;
4006
4007
4008 IF l_debug_on THEN
4009 WSH_DEBUG_SV.log(l_module_name,'l_trip_id', l_trip_id);
4010 WSH_DEBUG_SV.log(l_module_name,'l_trip_ignore', l_trip_ignore);
4011 END IF;
4012
4013 --OTM R12, changing the trip and deliveries to ignore for planning
4014 IF (l_gc3_is_installed = 'Y'
4015 AND l_trip_id IS NOT NULL
4016 AND l_trip_ignore = 'N') THEN
4017
4018 l_otm_trip_tab(1) := l_trip_id;
4019
4020 IF l_debug_on THEN
4021 WSH_DEBUG_SV.logmsg(l_module_name,'WSH_TP_RELEASE.change_ignoreplan_status',WSH_DEBUG_SV.C_PROC_LEVEL);
4022 END IF;
4023
4024 WSH_TP_RELEASE.change_ignoreplan_status
4025 (p_entity => 'TRIP',
4026 p_in_ids => l_otm_trip_tab,
4027 p_action_code => 'IGNORE_PLAN',
4028 x_return_status => l_return_status);
4029
4030 wsh_util_core.api_post_call
4031 (
4032 p_return_status => l_return_status,
4033 x_num_warnings => l_num_warnings,
4034 x_num_errors => l_num_errors
4035 );
4036
4037 --now call for the deliveries
4038 IF (l_non_trip_del_tab.COUNT > 0) THEN
4039 IF l_debug_on THEN
4040 WSH_DEBUG_SV.logmsg(l_module_name,'WSH_TP_RELEASE.change_ignoreplan_status',WSH_DEBUG_SV.C_PROC_LEVEL);
4041 END IF;
4042
4043 WSH_TP_RELEASE.change_ignoreplan_status
4044 (p_entity => 'DLVY',
4045 p_in_ids => l_non_trip_del_tab,
4046 p_action_code => 'IGNORE_PLAN',
4047 x_return_status => l_return_status);
4048
4049 wsh_util_core.api_post_call
4050 (
4051 p_return_status => l_return_status,
4052 x_num_warnings => l_num_warnings,
4053 x_num_errors => l_num_errors
4054 );
4055 END IF;
4056 END IF;
4057 --OTM R12
4058
4059 i := p_del_attributes_tab.FIRST;
4060 l_del_attributes.initial_pickup_date := p_del_attributes_tab(p_del_attributes_tab.FIRST).initial_pickup_date;
4061 l_del_attributes.ultimate_dropoff_date := p_del_attributes_tab(p_del_attributes_tab.FIRST).ultimate_dropoff_date;
4062 WHILE i is not NULL LOOP
4063 IF l_debug_on THEN
4064 WSH_DEBUG_SV.log(l_module_name,'delivery: '|| i, p_del_attributes_tab(i).delivery_id);
4065 END IF;
4066 l_del_attributes.initial_pickup_date := GREATEST(l_del_attributes.initial_pickup_date, p_del_attributes_tab(i).initial_pickup_date);
4067 l_deconsol_do_date := GREATEST(LEAST(l_del_attributes.ultimate_dropoff_date,
4068 p_del_attributes_tab(i).ultimate_dropoff_date),
4069 l_del_attributes.initial_pickup_date);
4070 IF l_deconsol_do_date = l_del_attributes.initial_pickup_date THEN
4071 l_deconsol_do_date := l_deconsol_do_date + 1/144;
4072 END IF;
4073
4074
4075 i := p_del_attributes_tab.next(i);
4076 END LOOP;
4077
4078
4079 IF p_caller = 'WSH_CONSOL_SRS' THEN
4080 l_del_attributes.ultimate_dropoff_location_id := p_del_attributes_tab(p_del_attributes_tab.FIRST).intmed_ship_to_location_id;
4081 l_del_attributes.customer_id := p_del_attributes_tab(p_del_attributes_tab.FIRST).customer_id;
4082 l_del_attributes.fob_code := p_del_attributes_tab(p_del_attributes_tab.FIRST).fob_code;
4083 l_del_attributes.freight_terms_code := p_del_attributes_tab(p_del_attributes_tab.FIRST).freight_terms_code;
4084 l_del_attributes.ship_method_code := p_del_attributes_tab(p_del_attributes_tab.FIRST).ship_method_code;
4085 l_del_attributes.carrier_id := p_del_attributes_tab(p_del_attributes_tab.FIRST).carrier_id;
4086 l_del_attributes.mode_of_transport := p_del_attributes_tab(p_del_attributes_tab.FIRST).mode_of_transport;
4087 l_del_attributes.service_level := p_del_attributes_tab(p_del_attributes_tab.FIRST).service_level;
4088 ELSE
4089 l_del_attributes.ultimate_dropoff_location_id := l_intermediate_loc_id;
4090 END IF;
4091
4092 l_del_attributes.intmed_ship_to_location_id := NULL;
4093 l_del_attributes.delivery_id := NULL;
4094 l_del_attributes.name := NULL;
4095 l_del_attributes.delivery_type := 'CONSOLIDATION';
4096 l_del_attributes.shipment_direction := 'O';
4097 l_del_attributes.organization_id := p_del_attributes_tab(p_del_attributes_tab.FIRST).organization_id;
4098 l_del_attributes.initial_pickup_location_id := p_del_attributes_tab(p_del_attributes_tab.FIRST).initial_pickup_location_id;
4099 --OTM R12, when OTM is installed, the ignore for planning will be Y
4100 IF (l_gc3_is_installed = 'Y'
4101 AND l_trip_id IS NOT NULL
4102 AND l_trip_ignore = 'N') THEN
4103 l_del_attributes.ignore_for_planning := 'Y';
4104 ELSE
4105 l_del_attributes.ignore_for_planning := p_del_attributes_tab(p_del_attributes_tab.FIRST).ignore_for_planning;
4106 END IF;
4107 --END OTM R12
4108
4109 l_del_attributes.status_code := 'OP';
4110
4111 l_transit_time := NULL;
4112 IF l_del_attributes.ship_method_code IS NOT NULL THEN
4113
4114 FTE_LANE_SEARCH.Get_Transit_Time(
4115 p_ship_from_loc_id => l_del_attributes.initial_pickup_location_id,
4116 p_ship_to_site_id => l_del_attributes.ultimate_dropoff_location_id,
4117 p_carrier_id => l_del_attributes.carrier_id,
4118 p_service_code => l_del_attributes.service_level,
4119 p_mode_code => l_del_attributes.mode_of_transport,
4120 p_from => 'FTE',
4121 x_transit_time => l_transit_time,
4122 x_return_status => l_return_status);
4123
4124 END IF;
4125 IF l_transit_time IS NOT NULL
4126 AND (l_deconsol_do_date > l_del_attributes.initial_pickup_date + l_transit_time) THEN
4127 IF l_debug_on THEN
4128 WSH_DEBUG_SV.log(l_module_name,'l_transit_time',l_transit_time);
4129 END IF;
4130
4131 l_del_attributes.ultimate_dropoff_date := l_del_attributes.initial_pickup_date + l_transit_time;
4132 IF l_debug_on THEN
4133 WSH_DEBUG_SV.log(l_module_name,'ultimate_dropoff_date',l_del_attributes.ultimate_dropoff_date);
4134 END IF;
4135
4136 ELSE
4137 IF l_debug_on THEN
4138 WSH_DEBUG_SV.log(l_module_name,'l_transit_time',l_transit_time);
4139 END IF;
4140
4141 l_del_attributes.ultimate_dropoff_date := l_del_attributes.initial_pickup_date +
4142 ((l_deconsol_do_date - l_del_attributes.initial_pickup_date)/2);
4143
4144 IF l_debug_on THEN
4145 WSH_DEBUG_SV.log(l_module_name,'initial_pickup_date',l_del_attributes.initial_pickup_date);
4146 WSH_DEBUG_SV.log(l_module_name,'l_deconsol_do_date',l_deconsol_do_date);
4147 WSH_DEBUG_SV.log(l_module_name,'ultimate_dropoff_date',l_del_attributes.ultimate_dropoff_date);
4148 END IF;
4149 END IF;
4150
4151 IF l_debug_on THEN
4152 WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit wsh_wv_utils.get_default_uoms',WSH_DEBUG_SV.C_PROC_LEVEL);
4153 END IF;
4154 wsh_wv_utils.get_default_uoms(l_del_attributes.organization_id, l_weight_uom_code, l_volume_uom_code, l_dummy);
4155
4156 l_del_attributes.weight_uom_code := l_weight_uom_code;
4157 l_del_attributes.volume_uom_code := l_volume_uom_code;
4158
4159 IF l_debug_on THEN
4160 WSH_DEBUG_SV.log(l_module_name,'l_weight_uom_code',l_weight_uom_code);
4161 WSH_DEBUG_SV.log(l_module_name,'l_volume_uom_code',l_volume_uom_code);
4162 END IF;
4163
4164 -- Create the delivery
4165
4166 IF l_debug_on THEN
4167 WSH_DEBUG_SV.logmsg(l_module_name,'wsh_new_deliveries_pvt.create_delivery',WSH_DEBUG_SV.C_PROC_LEVEL);
4168 END IF;
4169
4170 wsh_new_deliveries_pvt.create_delivery(l_del_attributes, l_rowid, l_delivery_id, l_delivery_name, l_return_status);
4171
4172 wsh_util_core.api_post_call
4173 (
4174 p_return_status => l_return_status,
4175 x_num_warnings => l_num_warnings,
4176 x_num_errors => l_num_errors
4177 );
4178
4179 l_delivery_id_tab(1) := l_delivery_id;
4180
4181 -- If there is a common trip, unassign the child deliveries from the trip, as the dropoff locations differ.
4182 -- they will get reassigned to the trip at the pickup and deconsol point.
4183 -- we then assign the consol delivery to the trip
4184
4185 IF l_trip_id IS NOT NULL THEN
4186
4187 IF l_debug_on THEN
4188 WSH_DEBUG_SV.logmsg(l_module_name,'WSH_TRIPS_ACTIONS.Unassign_Trip',WSH_DEBUG_SV.C_PROC_LEVEL);
4189 END IF;
4190 WSH_TRIPS_ACTIONS.Unassign_Trip(p_del_rows => l_trip_del_tab,
4191 p_trip_id => l_trip_id,
4192 x_return_status => l_return_status);
4193
4194 wsh_util_core.api_post_call
4195 (
4196 p_return_status => l_return_status,
4197 x_num_warnings => l_num_warnings,
4198 x_num_errors => l_num_errors
4199 );
4200
4201 -- Delete the empty stops on the trip.
4202 OPEN c_empty_stops(l_trip_id);
4203 FETCH c_empty_stops BULK COLLECT INTO l_empty_stops_tab;
4204 CLOSE c_empty_stops;
4205
4206 IF l_empty_stops_tab.count > 0 THEN
4207 IF l_debug_on THEN
4208 FOR i in 1 .. l_empty_stops_tab.count LOOP
4209 WSH_DEBUG_SV.log(l_module_name,'empty stop '||i,l_empty_stops_tab(i));
4210 END LOOP;
4211 END IF;
4212
4213 WSH_UTIL_CORE.Delete(p_type => 'STOP',
4214 p_rows => l_empty_stops_tab,
4215 x_return_status => l_return_status);
4216
4217 wsh_util_core.api_post_call
4218 (
4219 p_return_status => l_return_status,
4220 x_num_warnings => l_num_warnings,
4221 x_num_errors => l_num_errors
4222 );
4223
4224 END IF;
4225
4226 IF l_debug_on THEN
4227 WSH_DEBUG_SV.logmsg(l_module_name,'WSH_TRIPS_ACTIONS.assign_Trip',WSH_DEBUG_SV.C_PROC_LEVEL);
4228 END IF;
4229 WSH_TRIPS_ACTIONS.assign_trip(
4230 p_del_rows => l_delivery_id_tab,
4231 p_trip_id => l_trip_id,
4232 p_pickup_location_id => l_del_attributes.initial_pickup_location_id,
4233 p_dropoff_location_id => l_del_attributes.ultimate_dropoff_location_id,
4234 p_pickup_arr_date => l_del_attributes.initial_pickup_date,
4235 p_pickup_dep_date => l_del_attributes.initial_pickup_date,
4236 p_dropoff_arr_date => l_del_attributes.ultimate_dropoff_date,
4237 p_dropoff_dep_date => l_del_attributes.ultimate_dropoff_date,
4238 x_return_status => l_return_status,
4239 p_caller => p_caller);
4240
4241 wsh_util_core.api_post_call
4242 (
4243 p_return_status => l_return_status,
4244 x_num_warnings => l_num_warnings,
4245 x_num_errors => l_num_errors
4246 );
4247 ELSE
4248
4249 -- Autocreate trip for consol del
4250 l_trip_name_tab(1) := p_trip_prefix;
4251
4252 IF l_debug_on THEN
4253 WSH_DEBUG_SV.logmsg(l_module_name,'WSH_TRIPS_ACTIONS.autocreate_trip_multi',WSH_DEBUG_SV.C_PROC_LEVEL);
4254 END IF;
4255 WSH_TRIPS_ACTIONS.autocreate_trip_multi(
4256 p_del_rows => l_delivery_id_tab,
4257 x_trip_ids => l_trip_id_tab,
4258 x_trip_names => l_trip_name_tab,
4259 x_return_status => l_return_status);
4260
4261 wsh_util_core.api_post_call
4262 (
4263 p_return_status => l_return_status,
4264 x_num_warnings => l_num_warnings,
4265 x_num_errors => l_num_errors
4266 );
4267 l_trip_id := l_trip_id_tab(1);
4268 END IF;
4269
4270 -- Now assign the child deliveries to the parent.
4271 -- This would also assign the child delivery to the
4272 -- trip if it is already not assigned.
4273
4274 -- Set the p_caller:
4275
4276 IF p_caller like 'WSH%' THEN
4277 l_caller := 'WSH_AUTOCREATE_CONSOL';
4278 ELSIF p_caller like 'WMS%' THEN
4279 l_caller := 'WMS_AUTOCREATE_CONSOL';
4280 END IF;
4281
4282
4283 IF l_debug_on THEN
4284 WSH_DEBUG_SV.logmsg(l_module_name,'WSH_NEW_DELIVERY_ACTIONS.Assign_Del_to_Consol_Del',WSH_DEBUG_SV.C_PROC_LEVEL);
4285 END IF;
4286 WSH_NEW_DELIVERY_ACTIONS.Assign_Del_to_Consol_Del(
4287 p_del_tab => p_del_attributes_tab,
4288 p_parent_del_id => l_delivery_id,
4289 p_caller => l_caller,
4290 x_return_status => l_return_status);
4291
4292 wsh_util_core.api_post_call
4293 (
4294 p_return_status => l_return_status,
4295 x_num_warnings => l_num_warnings,
4296 x_num_errors => l_num_errors
4297 );
4298
4299 IF p_trip_prefix IS NOT NULL THEN
4300 update wsh_trips set
4301 name = p_trip_prefix ||'-'|| name
4302 where trip_id = l_trip_id_tab(1);
4303 END IF;
4304
4305
4306 IF l_num_errors > 0
4307 THEN
4308 x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
4309 ELSIF l_num_warnings > 0
4310 THEN
4311 x_return_status := WSH_UTIL_CORE.G_RET_STS_WARNING;
4312 ELSE
4313 x_return_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
4314 END IF;
4315
4316 x_parent_del_id := l_delivery_id;
4317 x_parent_trip_id := l_trip_id;
4318 --
4319 IF l_debug_on THEN
4320 wsh_debug_sv.pop(l_module_name);
4321 END IF;
4322 EXCEPTION
4323 WHEN FND_API.G_EXC_ERROR THEN
4324 x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
4325 --
4326 -- Debug Statements
4327 --
4328 IF l_debug_on THEN
4329 WSH_DEBUG_SV.logmsg(l_module_name,'FND_API.G_EXC_ERROR exception has occured.',WSH_DEBUG_SV.C_EXCEP_LEVEL);
4330 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:FND_API.G_EXC_ERROR');
4331 END IF;
4332 --
4333 WHEN WSH_INVALID_TRIPS THEN
4334 x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
4335 FND_MESSAGE.SET_NAME('WSH','WSH_INVALID_TRIPS');
4336 WSH_UTIL_CORE.Add_Message(x_return_status);
4337 --
4338 -- Debug Statements
4339 --
4340 IF l_debug_on THEN
4341 WSH_DEBUG_SV.logmsg(l_module_name,'WSH_INVALID_TRIPS exception has occured.',WSH_DEBUG_SV.C_EXCEP_LEVEL);
4342
4343 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:WSH_INVALID_TRIPS');
4344 END IF;
4345 --
4346
4347 WHEN OTHERS THEN
4348 wsh_util_core.default_handler('WSH_DELIVERY_AUTOCREATE.Autocreate_Consol_Delivery',l_module_name);
4349 --
4350 IF l_debug_on THEN
4351 wsh_debug_sv.pop(l_module_name, 'EXCEPTION:OTHERS');
4352 END IF;
4353
4354 END Autocreate_Consol_Delivery;
4355
4356 END WSH_DELIVERY_AUTOCREATE;
4357