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