1 PACKAGE BODY WSH_DELIVERY_DETAILS_UTILITIES as
2 /* $Header: WSHDDUTB.pls 120.7 2008/06/11 08:27:34 brana noship $ */
3
4
5 G_PKG_NAME CONSTANT VARCHAR2(50) := 'WSH_DELIVERY_DETAILS_UTILITIES';
6
7
8 -- ------------------------------------------------------------------------------------
9 -- Start of comments
10 -- API name : Auto_Assign_Deliveries
11 -- Type: private, called by group API
12 -- Prereqs : None
13 -- Preconditions: Given a list of delivery details with same grouping
14 -- attributes.
15 --
16 -- Function: This procedure groups the delivery details respecting FTE
17 -- compibility constraints then assign them to deliveries with matching
18 -- grouping attributes
19 --
20 -- Input Parameters :
21 -- p_line_rows : table of delivery details
22 -- p_group_by_header :'Y': within an order, 'N': across orders
23 -- p_check_fte_compatibility : 'Y'- check fte compatibility (called from Process Deliveries)
24 -- : 'N'- skip fte compatibility (called from Pick Release since the
25 -- compatibility has been checked already)
26 -- Output Parameters:
27 --
28 -- x_assigned_rows : table of delivery details and delivery id it assigned to
29 -- x_unassigned_rows : table of delivery detail IDs that are not appended
30 -- x_return_status : return status
31 -- ------------------------------------------------------------------------------------
32 PROCEDURE Auto_Assign_Deliveries(
33 p_line_rows IN OUT NOCOPY wsh_delivery_autocreate.grp_attr_tab_type,
34 p_group_by_header IN VARCHAR2,
35 p_check_fte_compatibility IN VARCHAR2,
36 x_assigned_rows OUT NOCOPY WSH_DELIVERY_DETAILS_UTILITIES.delivery_assignment_rec_tbl,
37 x_unassigned_rows OUT NOCOPY WSH_UTIL_CORE.Id_Tab_Type,
38 x_appended_del_tbl OUT NOCOPY WSH_UTIL_CORE.Id_Tab_Type,
39 x_return_status OUT NOCOPY VARCHAR2) IS
40
41
42
43 --dummy tables for calling validate_constraint_main
44 l_cc_del_attr_tab WSH_NEW_DELIVERIES_PVT.Delivery_Attr_Tbl_Type;
45 l_cc_det_attr_tab WSH_GLBL_VAR_STRCT_GRP.Delivery_Details_Attr_Tbl_Type;
46 l_cc_trip_attr_tab WSH_TRIPS_PVT.Trip_Attr_Tbl_Type;
47 l_cc_stop_attr_tab WSH_TRIP_STOPS_PVT.Stop_Attr_Tbl_Type;
48 l_cc_in_ids WSH_UTIL_CORE.id_tab_type;
49 l_cc_fail_ids WSH_UTIL_CORE.id_tab_type;
50 l_cc_validate_result VARCHAR2(1);
51 l_cc_failed_records WSH_FTE_COMP_CONSTRAINT_PKG.failed_line_tab_type;
52 l_cc_line_groups WSH_FTE_COMP_CONSTRAINT_PKG.line_group_tab_type;
53 l_cc_group_info WSH_FTE_COMP_CONSTRAINT_PKG.cc_group_tab_type;
54
55 l_msg_count NUMBER := 0;
56 l_msg_data VARCHAR2(2000);
57 l_return_status VARCHAR2(1);
58
59 l_debug_on BOOLEAN;
60 l_module_name CONSTANT VARCHAR2(100) := 'wsh.plsql.' || G_PKG_NAME || '.' || 'AUTO_ASSIGN_DELIVERIES';
61
62 l_exception_message VARCHAR2(2000);
63
64 l_action_prms WSH_GLBL_VAR_STRCT_GRP.dd_action_parameters_rec_type;
65 l_defaults WSH_GLBL_VAR_STRCT_GRP.dd_default_parameters_rec_type;
66 l_action_out_rec WSH_GLBL_VAR_STRCT_GRP.dd_action_out_rec_type;
67 l_details_in_cc_group WSH_GLBL_VAR_STRCT_GRP.delivery_details_Attr_tbl_Type;
68
69
70 l_attr_tab WSH_DELIVERY_AUTOCREATE.grp_attr_tab_type;
71 l_group_info WSH_DELIVERY_AUTOCREATE.grp_attr_tab_type;
72 l_action_rec WSH_DELIVERY_AUTOCREATE.action_rec_type;
73 l_target_rec WSH_DELIVERY_AUTOCREATE.grp_attr_rec_type;
74 l_matched_entities WSH_UTIL_CORE.id_tab_type;
75 l_out_rec WSH_DELIVERY_AUTOCREATE.out_rec_type;
76
77
78 l_index NUMBER;
79 l_current_line NUMBER;
80
81 l_cc_groupExists BOOLEAN;
82 l_delivery_done BOOLEAN;
83 l_multiple_sub_groups BOOLEAN;
84 l_cc_group_ids WSH_UTIL_CORE.id_tab_type;
85
86 l_get_autocreate_del_criteria VARCHAR2(1);
87 l_warning_num NUMBER := 0;
88 i NUMBER;
89 j NUMBER;
90 k NUMBER;
91 log_exception_err EXCEPTION;
92
93 l_sc_FINAL VARCHAR2(6000);
94 l_sc_SELECT VARCHAR2(2000);
95 l_sc_FROM VARCHAR2(2000);
96 l_sc_WHERE VARCHAR2(2000);
97 v_CursorID NUMBER := 0;
98 l_sub_str VARCHAR2(2000);
99 l_str_length NUMBER := 0;
100 l_delivery_id NUMBER := 0;
101 l_exception_id NUMBER := NULL;
102 v_ignore INTEGER;
103 l_group_id NUMBER := 0;
104 l_date_scheduled DATE := NULL;
105 l_date_requested DATE := NULL;
106
107 -- OTM R12 : update delivery
108 -- select d.ignore_for_planning is added
109
110 CURSOR c_get_deliveries IS
111 SELECT t.id, d.ignore_for_planning
112 FROM wsh_tmp t,
113 wsh_new_deliveries d
114 WHERE d.delivery_id = t.id
115 AND NOT EXISTS (
116 SELECT WDA.delivery_detail_id
117 FROM wsh_delivery_assignments_v WDA,
118 wsh_delivery_details WDD
119 WHERE WDA.delivery_detail_id = WDD.delivery_detail_id
120 AND WDA.delivery_id = t.id
121 AND WDA.delivery_id is not NULL
122 AND WDD.source_code <> 'OE'
123 AND wdd.container_flag = 'N')
124 AND NOT EXISTS (
125 -- deliveries in consolidations are ineligible
126 SELECT 1
127 FROM WSH_DELIVERY_LEGS WDL
128 WHERE WDL.delivery_id = t.id
129 AND WDL.parent_delivery_leg_id IS NOT NULL
130 )
131 ORDER BY d.creation_date;
132
133
134 -- OTM R12 : update delivery
135 l_delivery_info_tab WSH_NEW_DELIVERIES_PVT.Delivery_Attr_Tbl_Type;
136 l_delivery_info WSH_NEW_DELIVERIES_PVT.Delivery_Rec_Type;
137 l_new_interface_flag_tab WSH_UTIL_CORE.COLUMN_TAB_TYPE;
138 l_tms_update VARCHAR2(1);
139 l_trip_not_found VARCHAR2(1);
140 l_trip_info_rec WSH_DELIVERY_VALIDATIONS.trip_info_rec_type;
141 l_tms_version_number WSH_NEW_DELIVERIES.TMS_VERSION_NUMBER%TYPE;
142 l_ignore_for_planning WSH_NEW_DELIVERIES.IGNORE_FOR_PLANNING%TYPE;
143 l_otm_return_status VARCHAR2(1); -- not to overwrite l_return_status
144 l_gc3_is_installed VARCHAR2(1);
145 -- End of OTM R12 : update delivery
146
147 BEGIN
148
149 l_debug_on := WSH_DEBUG_INTERFACE.g_debug;
150 --
151 IF l_debug_on IS NULL THEN
152 l_debug_on := WSH_DEBUG_SV.is_debug_enabled;
153 END IF;
154 --
155 IF l_debug_on THEN
156 WSH_DEBUG_SV.push(l_module_name);
157 WSH_DEBUG_SV.log(l_module_name,'p_group_by_header', p_group_by_header);
158 WSH_DEBUG_SV.log(l_module_name,'p_check_fte_compatibility', p_check_fte_compatibility);
159 END IF;
160
161
162
163 x_return_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
164 -- OTM R12
165 l_gc3_is_installed := WSH_UTIL_CORE.G_GC3_IS_INSTALLED;
166
167 IF (l_gc3_is_installed IS NULL) THEN
168 l_gc3_is_installed := WSH_UTIL_CORE.GC3_IS_INSTALLED;
169 END IF;
170 -- End of OTM R12
171
172
173 l_cc_in_ids.delete;
174
175
176 IF l_debug_on THEN
177 i := p_line_rows.FIRST;
178 WHILE i is not NULL LOOP
179 WSH_DEBUG_SV.log(l_module_name,' p_line_rows('||i||').entity_id', p_line_rows(i).entity_id);
180 i := p_line_rows.next(i);
181 END LOOP;
182 END IF;
183
184
185
186 WHILE p_line_rows.count > 0 LOOP
187
188 l_current_line := p_line_rows.FIRST;
189
190 l_sc_FINAL := NULL;
191 l_sc_SELECT := NULL;
192 l_sc_FROM := NULL;
193 l_sc_WHERE := NULL;
194 v_CursorID := 0;
195
196
197 -- put the rows with same group_id in l_cc_in_ids
198 i := p_line_rows.FIRST;
199 WHILE i is not NULL LOOP
200
201 IF p_line_rows(l_current_line).group_id = p_line_rows(i).group_id THEN
202 l_cc_in_ids(l_cc_in_ids.count+1) := p_line_rows(i).entity_id;
203
204 IF l_debug_on THEN
205 WSH_DEBUG_SV.logmsg(l_module_name,' Get delivery detail: '||p_line_rows(i).entity_id );
206 END IF;
207
208 END IF;
209 i := p_line_rows.next(i);
210 END LOOP;
211
212 l_multiple_sub_groups := FALSE;
213 l_cc_line_groups.delete;
214
215
216 -- construct
217 IF wsh_util_core.fte_is_installed = 'Y'
218 AND p_check_fte_compatibility = FND_API.G_TRUE
219 AND l_cc_in_ids.count > 1 THEN
220 IF l_debug_on THEN
221 WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_FTE_COMP_CONSTRAINT_PKG.validate_constraint_main');
222 END IF;
223 WSH_FTE_COMP_CONSTRAINT_PKG.validate_constraint_main(
224 p_api_version_number => 1.0,
225 p_init_msg_list => FND_API.G_FALSE,
226 p_entity_type => 'L',
227 p_target_id => null,
228 p_action_code => 'AUTOCREATE-DEL',
229 p_del_attr_tab => l_cc_del_attr_tab,
230 p_det_attr_tab => l_cc_det_attr_tab,
231 p_trip_attr_tab => l_cc_trip_attr_tab,
232 p_stop_attr_tab => l_cc_stop_attr_tab,
233 p_in_ids => l_cc_in_ids,
234 x_fail_ids => l_cc_fail_ids,
235 x_validate_result => l_cc_validate_result,
236 x_failed_lines => l_cc_failed_records,
237 -- passed out delivery detail id and the group id
238 x_line_groups => l_cc_line_groups,
239 -- passed out group information
240 x_group_info => l_cc_group_info,
241 x_msg_count => l_msg_count,
242 x_msg_data => l_msg_data,
243 x_return_status => l_return_status);
244
245 -- handle return status
246
247 -- x_fail_ids and x_failed_lines are not populated for AUTOCREATE-DEL
248 l_cc_group_ids.delete;
249
250 IF l_cc_line_groups.COUNT > 0 AND l_return_status = wsh_util_core.g_ret_sts_error THEN
251
252 i := l_cc_line_groups.FIRST;
253 WHILE i is NOT NULL LOOP
254
255 -- build l_cc_group_ids to hold the sub-group IDs
256 l_cc_groupExists := FALSE ;
257
258 IF l_debug_on THEN
259 WSH_DEBUG_SV.logmsg(l_module_name,'l_cc_line_groups('|| i ||').entity_line_id: '||l_cc_line_groups(i).entity_line_id);
260 WSH_DEBUG_SV.logmsg(l_module_name,'l_cc_line_groups('|| i ||').line_group_id: '||l_cc_line_groups(i).line_group_id);
261 END IF;
262
263 IF l_cc_group_ids.COUNT > 0 THEN
264 j := l_cc_group_ids.FIRST;
265 WHILE j is not NULL LOOP
266 IF l_cc_group_ids(j) = l_cc_line_groups(i).line_group_id THEN
267 l_cc_groupExists := TRUE;
268 exit;
269 END IF;
270 j := l_cc_group_ids.next(j);
271 END LOOP;
272 END IF;
273 IF (NOT(l_cc_groupExists)) THEN
274 l_cc_group_ids(l_cc_group_ids.count+1) := l_cc_line_groups(i).line_group_id;
275 IF l_debug_on THEN
276 WSH_DEBUG_SV.logmsg(l_module_name,'Add Sub-group '||l_cc_line_groups(i).line_group_id||' to table l_cc_group_ids' );
277 END IF;
278 END IF;
279
280 -- update p_line_rows with new sub_group ids
281 k := p_line_rows.FIRST;
282 WHILE k is not NULL LOOP
283 IF p_line_rows(k).entity_id = l_cc_line_groups(i).entity_line_id THEN
284 -- correct the group_id with constraint group_id
285 p_line_rows(k).group_id := l_cc_line_groups(i).line_group_id;
286
287 IF l_debug_on THEN
288 WSH_DEBUG_SV.logmsg(l_module_name,'change group id of p_line_rows('||k||').entity_id '|| p_line_rows(k).entity_id||' to ' ||l_cc_line_groups(i).line_group_id);
289 END IF;
290
291 exit;
292 END IF;
293 k := p_line_rows.next(k);
294 END LOOP;
295
296 i := l_cc_line_groups.next(i);
297 END LOOP;
298
299 l_multiple_sub_groups := TRUE;
300
301 END IF;
302
303 END IF; /* FTE is installed */
304
305 -- No sub-group, l_cc_group_ids has only one record
306 IF NOT l_multiple_sub_groups THEN
307 l_cc_group_ids(l_cc_group_ids.count+1) := p_line_rows(l_current_line).group_id;
308 END IF;
309
310 l_group_id := p_line_rows(l_current_line).group_id;
311
312 l_action_rec.action := 'MATCH_GROUPS';
313 l_action_rec.group_by_header_flag := p_group_by_header;
314 l_action_rec.output_format_type := 'TEMP_TAB';
315 l_target_rec.entity_type := 'DELIVERY';
316
317 l_group_info.delete;
318 l_attr_tab.delete;
319 l_attr_tab(1) := p_line_rows(l_current_line);
320 l_attr_tab(1).entity_type := 'DELIVERY_DETAIL';
321
322 WSH_Delivery_Autocreate.Reset_WSH_TMP;
323
324 wsh_delivery_autocreate.Find_Matching_Groups(
325 p_attr_tab => l_attr_tab,
326 p_action_rec => l_action_rec,
327 p_target_rec => l_target_rec,
328 p_group_tab => l_group_info,
329 x_matched_entities => l_matched_entities,
330 x_out_rec => l_out_rec,
331 x_return_status => x_return_status);
332
333 -- execute the cursor
334 OPEN c_get_deliveries;
335 -- fetching the rows
336 LOOP
337 -- OTM R12 : update delivery, l_ignore_for_planning is added to cursor
338 FETCH c_get_deliveries INTO l_delivery_id, l_ignore_for_planning;
339 IF c_get_deliveries%NOTFOUND THEN
340 CLOSE c_get_deliveries;
341 EXIT;
342 END IF;
343 -- store column value in local variables
344
345 IF l_debug_on THEN
346 WSH_DEBUG_SV.log(l_module_name,' Fetched candidate delivery '|| to_char(l_delivery_id));
347 WSH_DEBUG_SV.log(l_module_name,' l_ignore_for_planning'||l_ignore_for_planning); -- OTM R12
348 END IF;
349
350
351 -- l_delivery_done indicastes if it needs to exit out of the sub-group loop
352 -- If the assignment happens for the delivery, it needs to exist in order
353 -- to advance to next delivery
354
355 l_delivery_done := FALSE;
356
357 -- loop through each sub group and try to assign the delivery details in the
358 -- sub-group to the delivery
359
360 l_index := l_cc_group_ids.FIRST;
361 WHILE l_index is not null AND not l_delivery_done LOOP
362
363 -- loop through each sub-group
364 l_details_in_cc_group.delete;
365
366 IF l_debug_on THEN
367 i := l_cc_group_ids.FIRST;
368 WHILE i is not NULL LOOP
369 WSH_DEBUG_SV.log(l_module_name,' l_cc_group_ids('||i||') ', l_cc_group_ids(i));
370 i := l_cc_group_ids.next(i);
371 END LOOP;
372 END IF;
373
374
375 i := p_line_rows.FIRST;
376 WHILE i is not NULL LOOP
377 IF p_line_rows(i).group_id = l_cc_group_ids(l_index) THEN
378 l_details_in_cc_group(l_details_in_cc_group.count+1).delivery_detail_id := p_line_rows(i).entity_id;
379 --
380 l_details_in_cc_group(l_details_in_cc_group.count).released_status := p_line_rows(i).status_code;
381 l_details_in_cc_group(l_details_in_cc_group.count).organization_id := p_line_rows(i).organization_id;
382 l_details_in_cc_group(l_details_in_cc_group.count).container_flag := p_line_rows(i).container_flag;
383 l_details_in_cc_group(l_details_in_cc_group.count).source_code := p_line_rows(i).source_code;
384 l_details_in_cc_group(l_details_in_cc_group.count).lpn_id := p_line_rows(i).lpn_id;
385 l_details_in_cc_group(l_details_in_cc_group.count).customer_id := p_line_rows(i).customer_id;
386 l_details_in_cc_group(l_details_in_cc_group.count).inventory_item_id := p_line_rows(i).inventory_item_id;
387 l_details_in_cc_group(l_details_in_cc_group.count).ship_from_location_id := p_line_rows(i).ship_from_location_id;
388 l_details_in_cc_group(l_details_in_cc_group.count).ship_to_location_id := p_line_rows(i).ship_to_location_id;
389 l_details_in_cc_group(l_details_in_cc_group.count).intmed_ship_to_location_id := p_line_rows(i).intmed_ship_to_location_id;
390 l_details_in_cc_group(l_details_in_cc_group.count).date_requested := p_line_rows(i).date_requested;
391 l_details_in_cc_group(l_details_in_cc_group.count).date_scheduled := p_line_rows(i).date_scheduled;
392 l_details_in_cc_group(l_details_in_cc_group.count).ship_method_code := p_line_rows(i).ship_method_code;
393 l_details_in_cc_group(l_details_in_cc_group.count).carrier_id := p_line_rows(i).carrier_id;
394 l_details_in_cc_group(l_details_in_cc_group.count).shipping_control := p_line_rows(i).shipping_control;
395 l_details_in_cc_group(l_details_in_cc_group.count).party_id := p_line_rows(i).party_id;
396 l_details_in_cc_group(l_details_in_cc_group.count).line_direction := p_line_rows(i).line_direction;
397
398 --
399 END IF;
400 i := p_line_rows.next(i);
401 END LOOP;
402
403 -- reset date_scheduled and date_requested
404 l_date_scheduled := NULL;
405 l_date_requested := NULL;
406
407 l_action_prms.action_code := 'ASSIGN';
408 l_action_prms.delivery_id := l_delivery_id;
409 l_action_prms.caller := 'WSH_DLMG';
410
411
412 -- assign delivery details of this sub-group to the delivery
413 WSH_DELIVERY_DETAILS_GRP.Delivery_Detail_Action(
414 p_api_version_number => 1.0,
415 p_init_msg_list => FND_API.G_FALSE,
416 p_commit => FND_API.G_FALSE,
417 x_return_status => l_return_status,
418 x_msg_count => l_msg_count,
419 x_msg_data => l_msg_data,
420 -- Procedure specific Parameters
421 p_rec_attr_tab => l_details_in_cc_group,
422 p_action_prms => l_action_prms,
423 x_defaults => l_defaults,
424 x_action_out_rec => l_action_out_rec);
425
426 IF l_debug_on THEN
427 WSH_DEBUG_SV.log(l_module_name,'Return status from WSH_DELIVERY_DETAILS_GRP.Delivery_Detail_Action '|| l_return_status);
428 END IF;
429
430 -- OTM R12 : update delivery
431 l_tms_update := 'N';
432 l_new_interface_flag_tab(1) := NULL;
433
434 -- delivery update will be done only for SUCCESS, WARNING case
435 IF (l_return_status in (WSH_UTIL_CORE.G_RET_STS_SUCCESS,
436 WSH_UTIL_CORE.G_RET_STS_WARNING) AND
437 l_gc3_is_installed = 'Y' AND
438 nvl(l_ignore_for_planning, 'N') = 'N') THEN
439 -- l_otm_return_status is used here not to change l_return_status
440 -- from the above API call
441 l_otm_return_status := NULL;
442 l_trip_not_found := 'N';
443
444 --get trip information for delivery, no update when trip not OPEN
445 WSH_DELIVERY_VALIDATIONS.get_trip_information
446 (p_delivery_id => l_delivery_id,
447 x_trip_info_rec => l_trip_info_rec,
448 x_return_status => l_otm_return_status);
449
450 IF (l_otm_return_status IN (WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR, WSH_UTIL_CORE.G_RET_STS_ERROR)) THEN
451 x_return_status := l_otm_return_status;
452 IF l_debug_on THEN
453 WSH_DEBUG_SV.logmsg(l_module_name,'Error in WSH_DELIVERY_VALIDATIONS.get_trip_information');
454 WSH_DEBUG_SV.pop(l_module_name);
455 END IF;
456 RETURN;
457 END IF;
458
459 IF (l_trip_info_rec.trip_id IS NULL) THEN
460 l_trip_not_found := 'Y';
461 END IF;
462
463 -- only do changes when there's no trip or trip status is OPEN
464 IF (l_trip_info_rec.status_code = 'OP' OR
465 l_trip_not_found = 'Y') THEN
466
467 WSH_DELIVERY_VALIDATIONS.get_delivery_information(
468 p_delivery_id => l_delivery_id,
469 x_delivery_rec => l_delivery_info,
470 x_return_status => l_otm_return_status);
471
472 IF (l_otm_return_status IN (WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR, WSH_UTIL_CORE.G_RET_STS_ERROR)) THEN
473 x_return_status := l_otm_return_status;
474 IF l_debug_on THEN
475 WSH_DEBUG_SV.logmsg(l_module_name,'Error in WSH_DELIVERY_VALIDATIONS.get_delivery_information');
476 WSH_DEBUG_SV.pop(l_module_name);
477 END IF;
478 RETURN;
479 END IF;
480
481
482 -- checking that fob code, freight terms code, ship method code,
483 -- carrier id, service level,or mode of transport is changed
484 -- and delivery is include for planning, then update is required
485 IF (nvl(l_delivery_info.fob_code, '@@') <>
486 NVL(l_delivery_info.fob_code,
487 nvl(l_attr_tab(1).fob_code, '@@')) OR
488 nvl(l_delivery_info.freight_terms_code, '@@') <>
489 NVL(l_delivery_info.freight_terms_code,
490 nvl(l_attr_tab(1).freight_terms_code, '@@')) OR
491 nvl(l_delivery_info.ship_method_code, '@@') <>
492 NVL(l_delivery_info.ship_method_code,
493 nvl(l_attr_tab(1).ship_method_code, '@@')) OR
494 nvl(l_delivery_info.carrier_id, -1) <>
495 NVL(l_delivery_info.carrier_id,
496 nvl(l_attr_tab(1).carrier_id, -1)) OR
497 nvl(l_delivery_info.service_level, '@@') <>
498 NVL(l_delivery_info.service_level,
499 nvl(l_attr_tab(1).service_level, '@@')) OR
500 nvl(l_delivery_info.mode_of_transport, '@@') <>
501 NVL(l_delivery_info.mode_of_transport,
502 nvl(l_attr_tab(1).mode_of_transport, '@@'))) THEN
503 IF (l_delivery_info.tms_interface_flag NOT IN
504 (WSH_NEW_DELIVERIES_PVT.C_TMS_NOT_TO_BE_SENT,
505 WSH_NEW_DELIVERIES_PVT.C_TMS_CREATE_REQUIRED,
506 WSH_NEW_DELIVERIES_PVT.C_TMS_DELETE_REQUIRED,
507 WSH_NEW_DELIVERIES_PVT.C_TMS_DELETE_IN_PROCESS,
508 WSH_NEW_DELIVERIES_PVT.C_TMS_UPDATE_REQUIRED)) THEN
509 l_tms_update := 'Y';
510 l_delivery_info_tab(1) := l_delivery_info;
511 l_new_interface_flag_tab(1) := WSH_NEW_DELIVERIES_PVT.C_TMS_UPDATE_REQUIRED;
512 l_tms_version_number := nvl(l_delivery_info.tms_version_number, 1) + 1;
513 END IF;
514 END IF; -- checking the value differences
515 END IF; -- IF ((l_trip_not_found = 'N' AND
516 END IF; -- IF (l_gc3_is_installed = 'Y'
517
518 -- End of OTM R12 : update delivery
519
520 -- If there are some delivery details assigned to the delivery,
521 -- we populate the assigned table and delete it from the main table
522
523 IF l_return_status = WSH_UTIL_CORE.G_RET_STS_SUCCESS THEN
524
525 IF l_debug_on THEN
526 i := l_action_out_rec.result_id_tab.FIRST;
527 WHILE i is not NULL LOOP
528 WSH_DEBUG_SV.log(l_module_name, 'l_action_out_rec.result_id_tab('||i||')', l_action_out_rec.result_id_tab(i));
529 i := l_action_out_rec.result_id_tab.next(i);
530 END LOOP;
531 END IF;
532
533
534 -- this delivery is used, go to next delivery
535 l_delivery_done := TRUE;
536
537 i := l_details_in_cc_group.FIRST;
538 WHILE i is not NULL LOOP
539
540 -- log_exception if Append Deliveries is turned on
541 IF l_attr_tab(1).batch_id is NULL THEN
542
543 FND_MESSAGE.SET_NAME('WSH', 'WSH_DEL_APPENDED');
544 FND_MESSAGE.SET_TOKEN('DELIVERY_DETAIL_ID' , to_char(l_details_in_cc_group(i).delivery_detail_id));
545 FND_MESSAGE.SET_TOKEN('DELIVERY_ID' , to_char(l_delivery_id));
546 l_exception_message := FND_MESSAGE.Get;
547
548 l_exception_id := NULL;
549
550 wsh_xc_util.log_exception(
551 p_api_version => 1.0,
552 x_return_status => l_return_status,
553 x_msg_count => l_msg_count,
554 x_msg_data => l_msg_data,
555 x_exception_id => l_exception_id,
556 p_exception_location_id => l_details_in_cc_group(i).ship_from_location_id,
557 p_logged_at_location_id => l_details_in_cc_group(i).ship_from_location_id,
558 p_logging_entity => 'SHIPPER',
559 p_logging_entity_id => FND_GLOBAL.USER_ID,
560 p_exception_name => 'WSH_DELIVERY_APPENDED',
561 p_message => substrb(l_exception_message,1,2000),
562 p_delivery_id => l_delivery_id,
563 p_delivery_detail_id => l_details_in_cc_group(i).delivery_detail_id);
564
565
566 IF l_return_status in ( WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR , WSH_UTIL_CORE.G_RET_STS_ERROR ) THEN
567 IF l_debug_on THEN
568 WSH_DEBUG_SV.log(l_module_name,' log_exception failed ');
569 END IF;
570 raise log_exception_err;
571 END IF;
572
573 END IF;
574
575 -- delete the processed record in p_line_rows
576 j := p_line_rows.FIRST;
577 WHILE j is not NULL LOOP
578 IF p_line_rows(j).entity_id = l_details_in_cc_group(i).delivery_detail_id THEN
579 x_assigned_rows(x_assigned_rows.count+1).delivery_detail_id := p_line_rows(j).entity_id;
580 x_assigned_rows(x_assigned_rows.count).delivery_id := l_delivery_id;
581
582 IF l_debug_on THEN
583 WSH_DEBUG_SV.logmsg(l_module_name,'Added delivery_detail_id '||p_line_rows(j).entity_id||' to x_assigned_rows' );
584 END IF;
585 l_date_scheduled := least(NVL(l_date_scheduled,p_line_rows(j).date_scheduled), p_line_rows(j).date_scheduled);
586 l_date_requested := least(NVL(l_date_requested,p_line_rows(j).date_requested), p_line_rows(j).date_requested);
587 p_line_rows.delete(j);
588 exit;
589 END IF;
590 j := p_line_rows.next(j);
591 END LOOP;
592
593 i := l_details_in_cc_group.next(i);
594 END LOOP;
595
596 -- update appended delivery
597 IF l_debug_on THEN
598 WSH_DEBUG_SV.logmsg(l_module_name, 'UPDATING WSH_NEW_DELIVERIES ATTRIBUTES' );
599 WSH_DEBUG_SV.log(l_module_name, 'intmed_ship_to_location_id', l_attr_tab(1).intmed_ship_to_location_id );
600 WSH_DEBUG_SV.log(l_module_name, 'fob_code', l_attr_tab(1).fob_code );
601 WSH_DEBUG_SV.log(l_module_name, 'freight_terms_code', l_attr_tab(1).freight_terms_code );
602 WSH_DEBUG_SV.log(l_module_name, 'carrier_id', l_attr_tab(1).carrier_id );
603 WSH_DEBUG_SV.log(l_module_name, 'date_scheduled', l_date_scheduled);
604 WSH_DEBUG_SV.log(l_module_name, 'date_requested', l_date_requested);
605 WSH_DEBUG_SV.log(l_module_name, 'service_level', l_attr_tab(1).service_level );
606 WSH_DEBUG_SV.log(l_module_name, 'mode_of_transport', l_attr_tab(1).mode_of_transport);
607 WSH_DEBUG_SV.log(l_module_name, 'source_header_id', l_attr_tab(1).source_header_id);
608
609 -- OTM R12 : update delivery
610 WSH_DEBUG_SV.log(l_module_name, 'l_gc3_is_installed', l_gc3_is_installed);
611 WSH_DEBUG_SV.log(l_module_name, 'l_tms_update', l_tms_update);
612 IF (l_tms_update = 'Y') THEN
613 WSH_DEBUG_SV.log(l_module_name, 'l_new_interface_flag_tab', l_new_interface_flag_tab(1));
614 WSH_DEBUG_SV.log(l_module_name, 'l_tms_version_number', l_tms_version_number);
615 END IF;
616 -- End of OTM R12 : update delivery
617 END IF;
618
619 UPDATE wsh_new_deliveries
620 SET intmed_ship_to_location_id = NVL(intmed_ship_to_location_id,l_attr_tab(1).intmed_ship_to_location_id),
621 fob_code = NVL(fob_code,l_attr_tab(1).fob_code),
622 freight_terms_code = NVL(freight_terms_code,l_attr_tab(1).freight_terms_code),
623 ship_method_code = NVL(ship_method_code,l_attr_tab(1).ship_method_code),
624 carrier_id = NVL(carrier_id,l_attr_tab(1).carrier_id),
625 initial_pickup_date = least(initial_pickup_date, l_date_scheduled),
626 -- bug 2466054 - switch between date_scheduled and date_requested
627 ultimate_dropoff_date = greatest(least(initial_pickup_date, l_date_scheduled),
628 least(ultimate_dropoff_date,l_date_requested)),
629 service_level = NVL(service_level,l_attr_tab(1).service_level),
630 mode_of_transport = NVL(mode_of_transport,l_attr_tab(1).mode_of_transport),
631 source_header_id = NVL(source_header_id, l_attr_tab(1).source_header_id),
632 last_update_date = SYSDATE,
633 last_updated_by = FND_GLOBAL.user_id,
634 last_update_login = FND_GLOBAL.login_id,
635 -- OTM R12
636 TMS_INTERFACE_FLAG = decode(l_tms_update, 'Y', l_new_interface_flag_tab(1), nvl(TMS_INTERFACE_FLAG, WSH_NEW_DELIVERIES_PVT.C_TMS_NOT_TO_BE_SENT)),
637 TMS_VERSION_NUMBER = decode(l_tms_update, 'Y', l_tms_version_number, nvl(tms_version_number, 1))
638 -- End of OTM R12
639 WHERE delivery_id = l_delivery_id;
640
641 -- OTM R12 : update delivery
642 IF (l_gc3_is_installed = 'Y' AND l_tms_update = 'Y') THEN
643 WSH_XC_UTIL.LOG_OTM_EXCEPTION(
644 p_delivery_info_tab => l_delivery_info_tab,
645 p_new_interface_flag_tab => l_new_interface_flag_tab,
646 x_return_status => l_otm_return_status);
647 IF (l_otm_return_status IN (WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR, WSH_UTIL_CORE.G_RET_STS_ERROR)) THEN
648 x_return_status := l_otm_return_status;
649 IF l_debug_on THEN
650 WSH_DEBUG_SV.logmsg(l_module_name,'Error in WSH_XC_UTIL.log_otm_exception');
651 WSH_DEBUG_SV.pop(l_module_name);
652 END IF;
653 RETURN;
654 END IF;
655 END IF;
656 -- End of OTM R12 : update delivery
657
658 x_appended_del_tbl(x_appended_del_tbl.count+1) := l_delivery_id;
659 -- delete the processed record in l_cc_group_ids
660 i := l_cc_group_ids.FIRST;
661 WHILE i is not NULL LOOP
662 IF l_cc_group_ids(i) = l_group_id THEN
663 l_cc_group_ids.delete(i);
664 END IF;
665 i := l_cc_group_ids.next(i);
666 END LOOP;
667
668 ELSIF l_return_status = WSH_UTIL_CORE.G_RET_STS_WARNING /* some of them are successfully
669 assigned */ THEN
670
671 l_delivery_done := TRUE;
672 IF l_action_out_rec.result_id_tab.count > 0 THEN
673 i := l_action_out_rec.result_id_tab.FIRST;
674 WHILE i is not NULL LOOP
675
676 IF l_debug_on THEN
677 WSH_DEBUG_SV.log(l_module_name, 'l_action_out_rec.result_id_tab('||i||')', l_action_out_rec.result_id_tab(i));
678 END IF;
679
680 IF l_attr_tab(1).batch_id is NULL THEN
681
682 -- do not log exception when appending within a PR release
683 FND_MESSAGE.SET_NAME('WSH', 'WSH_DEL_APPENDED');
684 FND_MESSAGE.SET_TOKEN('DELIVERY_DETAIL_ID' , to_char(l_action_out_rec.result_id_tab(i)));
685 FND_MESSAGE.SET_TOKEN('DELIVERY_ID' , to_char(l_delivery_id));
686 l_exception_message := FND_MESSAGE.Get;
687
688 l_exception_id := NULL;
689
690 wsh_xc_util.log_exception(
691 p_api_version => 1.0,
692 x_return_status => l_return_status,
693 x_msg_count => l_msg_count,
694 x_msg_data => l_msg_data,
695 x_exception_id => l_exception_id,
696 p_exception_handling => 'NO_ACTION_REQUIRED',
697 p_exception_location_id => l_details_in_cc_group(l_details_in_cc_group.FIRST).ship_from_location_id,
698 p_logged_at_location_id => l_details_in_cc_group(l_details_in_cc_group.FIRST).ship_from_location_id,
699 p_logging_entity => 'SHIPPER',
700 p_logging_entity_id => FND_GLOBAL.USER_ID,
701 p_exception_name => 'WSH_DELIVERY_APPENDED',
702 p_message => substrb(l_exception_message,1,2000),
703 p_delivery_id => l_delivery_id,
704 p_delivery_detail_id => l_action_out_rec.result_id_tab(i));
705
706 IF l_return_status in ( WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR , WSH_UTIL_CORE.G_RET_STS_ERROR ) THEN
707 IF l_debug_on THEN
708 WSH_DEBUG_SV.log(l_module_name,' log_exception failed ');
709 END IF;
710 raise log_exception_err;
711 END IF;
712
713 END IF;
714
715 -- delete the processed record in p_line_rows
716 j := p_line_rows.FIRST;
717 WHILE j is not NULL LOOP
718 IF p_line_rows(j).entity_id = l_action_out_rec.result_id_tab(i) THEN
719 x_assigned_rows(x_assigned_rows.count+1).delivery_detail_id := p_line_rows(j).entity_id;
720 x_assigned_rows(x_assigned_rows.count).delivery_id := l_delivery_id;
721
722 IF l_debug_on THEN
723 WSH_DEBUG_SV.logmsg(l_module_name,'Added delivery_detail_id '||p_line_rows(j).entity_id||' to x_assigned_rows' );
724 END IF;
725 -- get the date_scheduled and date_requrested
726 l_date_scheduled := least(NVL(l_date_scheduled,p_line_rows(j).date_scheduled), p_line_rows(j).date_scheduled);
727 l_date_requested := least(NVL(l_date_requested,p_line_rows(j).date_requested), p_line_rows(j).date_requested);
728 p_line_rows.delete(j);
729 exit;
730 END IF;
731 j := p_line_rows.next(j);
732 END LOOP;
733
734 -- delete the assigned delivery details in l_details_in_cc_group
735 -- Has gap, need to modify later
736 k := l_details_in_cc_group.FIRST;
737 WHILE k is not null LOOP
738 IF l_details_in_cc_group(k).delivery_detail_id = l_action_out_rec.result_id_tab(i) THEN
739 l_details_in_cc_group.delete(k);
740 exit;
741 END IF;
742 k := l_details_in_cc_group.next(k);
743 END LOOP;
744
745 i := l_action_out_rec.result_id_tab.next(i);
746 END LOOP;
747
748 IF l_debug_on THEN
749 WSH_DEBUG_SV.logmsg(l_module_name, 'UPDATING WSH_NEW_DELIVERIES ATTRIBUTES' );
750 WSH_DEBUG_SV.log(l_module_name, 'intmed_ship_to_location_id', l_attr_tab(1).intmed_ship_to_location_id );
751 WSH_DEBUG_SV.log(l_module_name, 'fob_code', l_attr_tab(1).fob_code );
752 WSH_DEBUG_SV.log(l_module_name, 'freight_terms_code', l_attr_tab(1).freight_terms_code );
753 WSH_DEBUG_SV.log(l_module_name, 'carrier_id', l_attr_tab(1).carrier_id );
754 WSH_DEBUG_SV.log(l_module_name, 'date_scheduled', l_date_scheduled);
755 WSH_DEBUG_SV.log(l_module_name, 'date_requested', l_date_requested);
756 WSH_DEBUG_SV.log(l_module_name, 'service_level', l_attr_tab(1).service_level );
757 WSH_DEBUG_SV.log(l_module_name, 'mode_of_transport', l_attr_tab(1).mode_of_transport);
758 WSH_DEBUG_SV.log(l_module_name, 'source_header_id', l_attr_tab(1).source_header_id);
759 -- OTM R12 : update delivery
760 WSH_DEBUG_SV.log(l_module_name, 'l_gc3_is_installed', l_gc3_is_installed);
761 WSH_DEBUG_SV.log(l_module_name, 'l_tms_update', l_tms_update);
762 IF (l_tms_update = 'Y') THEN
763 WSH_DEBUG_SV.log(l_module_name, 'l_new_interface_flag_tab', l_new_interface_flag_tab(1));
764 WSH_DEBUG_SV.log(l_module_name, 'l_tms_version_number', l_tms_version_number);
765 END IF;
766 -- End of OTM R12 : update delivery
767 END IF;
768
769 UPDATE wsh_new_deliveries
770 SET intmed_ship_to_location_id = NVL(intmed_ship_to_location_id,l_attr_tab(1).intmed_ship_to_location_id),
771 fob_code = NVL(fob_code,l_attr_tab(1).fob_code),
772 freight_terms_code = NVL(freight_terms_code,l_attr_tab(1).freight_terms_code),
773 ship_method_code = NVL(ship_method_code,l_attr_tab(1).ship_method_code),
774 carrier_id = NVL(carrier_id,l_attr_tab(1).carrier_id),
775 initial_pickup_date = least(initial_pickup_date,l_date_scheduled),
776 -- bug 2466054 - switch between date_scheduled and date_requested
777 ultimate_dropoff_date = greatest(least(initial_pickup_date,l_date_scheduled),
778 least(ultimate_dropoff_date,l_date_requested)),
779 service_level = NVL(service_level,l_attr_tab(1).service_level),
780 mode_of_transport = NVL(mode_of_transport,l_attr_tab(1).mode_of_transport),
781 source_header_id = NVL(source_header_id, l_attr_tab(1).source_header_id),
782 last_update_date = SYSDATE,
783 last_updated_by = FND_GLOBAL.user_id,
784 last_update_login = FND_GLOBAL.login_id,
785 -- OTM R12
786 TMS_INTERFACE_FLAG = decode(l_tms_update, 'Y', l_new_interface_flag_tab(1), nvl(TMS_INTERFACE_FLAG, WSH_NEW_DELIVERIES_PVT.C_TMS_NOT_TO_BE_SENT)),
787 TMS_VERSION_NUMBER = decode(l_tms_update, 'Y', l_tms_version_number, nvl(tms_version_number, 1))
788 -- End of OTM R12
789 WHERE delivery_id = l_delivery_id;
790
791 -- OTM R12 : update delivery
792 IF (l_gc3_is_installed = 'Y' AND l_tms_update = 'Y') THEN
793 WSH_XC_UTIL.LOG_OTM_EXCEPTION(
794 p_delivery_info_tab => l_delivery_info_tab,
795 p_new_interface_flag_tab => l_new_interface_flag_tab,
796 x_return_status => l_otm_return_status);
797 IF (l_otm_return_status IN (WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR, WSH_UTIL_CORE.G_RET_STS_ERROR)) THEN
798 x_return_status := l_otm_return_status;
799 IF l_debug_on THEN
800 WSH_DEBUG_SV.logmsg(l_module_name,'Error in WSH_XC_UTIL.log_otm_exception');
801 WSH_DEBUG_SV.pop(l_module_name);
802 END IF;
803 RETURN;
804 END IF;
805 END IF;
806 -- End of OTM R12 : update delivery
807
808 x_appended_del_tbl(x_appended_del_tbl.count+1) := l_delivery_id;
809
810 END IF;
811
812 -- all of the delivery lines get assigned, but rate delivery or carrier selection
813 -- are not successful
814
815 IF l_action_out_rec.result_id_tab.count >= l_details_in_cc_group.count THEN
816 -- delete the processed record in l_cc_group_ids
817 i := l_cc_group_ids.FIRST;
818 WHILE i is not NULL LOOP
819 IF l_cc_group_ids(i) = l_group_id THEN
820 l_cc_group_ids.delete(i);
821 END IF;
822 i := l_cc_group_ids.next(i);
823 END LOOP;
824 END IF;
825 ELSIF l_return_status = WSH_UTIL_CORE.G_RET_STS_ERROR THEN
826 -- none of the delivery detail are assigned, it means that the delivery
827 -- do not match the constraint requirement, we have to try another delivery
828
829 NULL;
830
831 ELSIF l_return_status = WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR THEN
832 -- searous error happens, skip to next sub-group
833
834 i := p_line_rows.FIRST;
835 WHILE i is not NULL LOOP
836 IF p_line_rows(i).group_id = l_group_id THEN
837 x_unassigned_rows(x_unassigned_rows.count+1):= p_line_rows(i).entity_id;
838 IF l_debug_on THEN
839 WSH_DEBUG_SV.logmsg(l_module_name,'Added delivery_detail_id '||p_line_rows(i).entity_id||' to x_unassigned_rows' );
840 END IF;
841 p_line_rows.delete(i);
842 END IF;
843 i := p_line_rows.next(i);
844 END LOOP;
845
846 -- delete the processed record in l_cc_group_ids
847 i := l_cc_group_ids.FIRST;
848
849 WHILE i is not NULL LOOP
850 IF l_cc_group_ids(i) = l_group_id THEN
851 l_cc_group_ids.delete(i);
852 exit;
853 END IF;
854 i := l_cc_group_ids.next(i);
855 END LOOP;
856
857 END IF; -- check the result of assignment
858
859
860 l_index := l_cc_group_ids.next(l_index) ;
861 END LOOP; -- loop through each sub-group
862
863 -- exit delivery loop
864 IF l_cc_group_ids.count = 0 THEN
865 CLOSE c_get_deliveries;
866 EXIT;
867 END IF;
868
869 END LOOP; -- fetch delivery loop
870
871 -- OTM R12, if the cursor is still open then close the cursor
872 IF c_get_deliveries%ISOPEN THEN
873 CLOSE c_get_deliveries;
874 END IF;
875
876
877 IF l_cc_group_ids.count > 0 THEN
878 -- have exhausted all the candidate deliveies, move the delivery lines to
879 -- unassigned table
880 i := l_cc_group_ids.FIRST;
881 WHILE i is not NULL LOOP
882
883 -- this table has gaps
884 IF l_debug_on THEN
885 WSH_DEBUG_SV.log(l_module_name,'p_line_rows count', p_line_rows.count);
886 END IF;
887 j := p_line_rows.FIRST;
888 WHILE j is not null LOOP
889
890 IF p_line_rows(j).group_id = l_cc_group_ids(i) THEN
891 x_unassigned_rows(x_unassigned_rows.count+1):= p_line_rows(j).entity_id;
892 IF l_debug_on THEN
893 WSH_DEBUG_SV.logmsg(l_module_name,'Added delivery_detail_id '||p_line_rows(j).entity_id||' to x_unassigned_rows' );
894 END IF;
895 p_line_rows.delete(j);
896 END IF;
897
898 j := p_line_rows.NEXT(j);
899 END LOOP;
900 l_cc_group_ids.delete(i);
901 i := l_cc_group_ids.NEXT(i);
902 END LOOP;
903
904 END IF ;
905
906 END LOOP; -- p_line_rows loop
907
908
909 IF l_debug_on THEN
910 i := x_unassigned_rows.FIRST;
911 WHILE i is not NULL LOOP
912 WSH_DEBUG_SV.log(l_module_name,'x_unassigned_rows('||i||')' , x_unassigned_rows(i));
913 i := x_unassigned_rows.next(i);
914 END LOOP;
915 END IF;
916
917
918 IF x_unassigned_rows.count > 0 OR l_warning_num > 0 THEN
919 x_return_status := WSH_UTIL_CORE.G_RET_STS_WARNING;
920 END IF;
921
922 IF l_debug_on THEN
923 WSH_DEBUG_SV.pop(l_module_name, 'Return Status: '||x_return_status);
924 END IF;
925
926
927
928 EXCEPTION
929
930 WHEN log_exception_err THEN
931
932 -- OTM R12 : if the cursor is still open then close the cursor
933 IF c_get_deliveries%ISOPEN THEN
934 CLOSE c_get_deliveries;
935 END IF;
936
937 x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
938 WSH_UTIL_CORE.Add_Message(x_return_status,l_module_name);
939 IF l_debug_on THEN
940 WSH_DEBUG_SV.pop(l_module_name, 'Log_exception returns error ');
941 END IF;
942
943
944 WHEN others THEN
945
946 -- if the cursor is still open then close the cursor
947 IF c_get_deliveries%ISOPEN THEN
948 CLOSE c_get_deliveries;
949 END IF;
950 wsh_util_core.default_handler('WSH_DELIVERY_DETAILS_UTILITIES.Auto_Assign_Deliveries');
951 x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR;
952 IF l_debug_on THEN
953 WSH_DEBUG_SV.pop(l_module_name);
954 END IF;
955
956
957 END Auto_Assign_Deliveries;
958
959
960 -- ----------------------------------------------------------------------------------
961 -- Start of comments
962 -- API name : Append_to_Deliveries
963 -- Type: private, called by group API
964 -- Prereqs : None
965 -- Function: Assign delivery lines to existing deliveries respecting
966 -- grouping rules , across order options and FTE compatibility constraints.
967 -- This procedure only append OE lines per design
968 -- This procedure is called from Pick Release process or Process Deliveries SRS
969 -- Input Parameters :
970 -- p_delivery_detail_tbl : table of delivery details
971 -- p_append_flag : NULL or 'Y' - check the appending_limit in shipping parameters for
972 -- the organization, append delivery details only if
973 -- appending_limit is not 'No'
974 --
975 -- 'N' - do not append delivery details, in this case
976 -- x_unappended_det_tbl is the same as p_delivery_detail_tbl
977 -- p_group_by_header : 'Y' - use source_header_id in grouping delivery details
978 -- 'N' - do not use source_header_id in grouping delivery details
979 -- NULL - get the value from shipping_parameters to decide if
980 -- source_header_id should be used in grouping delivery details
981 --
982 -- p_commit FND_API.G_TRUE - commit
983 -- FND_API.G_FALSE - do not commit
984 --
985 -- p_lock_rows FND_API.G_TRUE - lock rows before append
986 -- FND_API.G_FALSE - do not lock rows befor append, usually the
987 -- caller procedure already locks the rows
988 --
989 -- p_check_fte_compatibility FND_API.G_TRUE - needs to check compatibility when grouping
990 -- the delivery lines
991 -- FND_API.G_FALSE - do not needs to check compatibility when
992 -- the delivery lines, usually it has been
993 -- checked by the caller procedure
994 --
995 -- x_appended_det_tbl : table of delivery details and delivery id it successfully appended
996 --
997 -- x_unappended_det_tbl : table of delivery detail IDs that are not appended
998 --
999 -- x_appended_del_tbl : table of deliveries that got appended
1000 --
1001 -- x_return_status : Success: mean calling problem can continue with next step
1002 -- : Error or Unexpected Error: the autocreate delivery
1003 -- process should not continue
1004 --
1005 -- ----------------------------------------------------------------------------------
1006
1007 PROCEDURE Append_to_Deliveries(
1008 p_delivery_detail_tbl IN WSH_UTIL_CORE.Id_Tab_Type,
1009 p_append_flag IN VARCHAR2,
1010 p_group_by_header IN VARCHAR2,
1011 p_commit IN VARCHAR2,
1012 p_lock_rows IN VARCHAR2,
1013 p_check_fte_compatibility IN VARCHAR2,
1014 x_appended_det_tbl OUT NOCOPY WSH_DELIVERY_DETAILS_UTILITIES.delivery_assignment_rec_tbl,
1015 x_unappended_det_tbl OUT NOCOPY WSH_UTIL_CORE.Id_Tab_Type,
1016 x_appended_del_tbl OUT NOCOPY WSH_UTIL_CORE.Id_Tab_Type,
1017 x_return_status OUT NOCOPY VARCHAR2) IS
1018
1019 CURSOR get_line_details_check(c_detail_id NUMBER) IS
1020 SELECT wdd.delivery_detail_id,
1021 wdd.ship_to_location_id,
1022 wdd.ship_from_location_id,
1023 wdd.customer_id,
1024 wdd.intmed_ship_to_location_id,
1025 wdd.fob_code,
1026 wdd.freight_terms_code,
1027 wdd.ship_method_code,
1028 wdd.carrier_id,
1029 wdd.source_header_id,
1030 wdd.deliver_to_location_id,
1031 wdd.organization_id,
1032 wdd.date_scheduled,
1033 wdd.date_requested,
1034 wdd.released_status,
1035 wdd.container_flag,
1036 wdd.shipping_control,
1037 wdd.party_id,
1038 wdd.line_direction,
1039 wdd.inventory_item_id,
1040 wdd.source_code,
1041 wdd.lpn_id,
1042 wsp.appending_limit,
1043 wdd.ignore_for_planning --bugfix 7164767
1044 FROM wsh_delivery_details wdd,
1045 wsh_delivery_assignments_v wda,
1046 wsh_shipping_parameters wsp
1047 WHERE wdd.delivery_detail_id = c_detail_id
1048 AND wda.delivery_detail_id = wdd.delivery_detail_id
1049 AND wda.delivery_id is NULL
1050 AND NVL(wdd.line_direction, 'O') in ('O', 'IO')
1051 AND wdd.source_code = 'OE'
1052 AND wdd.container_flag = 'N'
1053 AND wsp.organization_id = wdd.organization_id;
1054 -- AND wsp.appending_limit <> 'N' ;
1055
1056 l_warning_num NUMBER := 0;
1057 l_return_status VARCHAR2(1) ;
1058
1059 l_detail_info WSH_DELIVERY_AUTOCREATE.grp_attr_tab_type;
1060
1061 l_unassigned_rows WSH_UTIL_CORE.Id_Tab_Type;
1062 l_assgined_rows WSH_DELIVERY_DETAILS_UTILITIES.delivery_assignment_rec_tbl;
1063 l_index NUMBER;
1064 l_delivery_detail_id NUMBER;
1065
1066 l_group_info WSH_DELIVERY_AUTOCREATE.grp_attr_tab_type;
1067 l_action_rec WSH_DELIVERY_AUTOCREATE.action_rec_type;
1068 l_target_rec WSH_DELIVERY_AUTOCREATE.grp_attr_rec_type;
1069 l_matched_entities WSH_UTIL_CORE.id_tab_type;
1070 l_out_rec WSH_DELIVERY_AUTOCREATE.out_rec_type;
1071
1072 l_debug_on BOOLEAN;
1073 l_module_name CONSTANT VARCHAR2(100) := 'wsh.plsql.' || g_pkg_name || '.' || 'Append_to_Deliveries';
1074 Auto_Assign_Deliveries_ERR EXCEPTION;
1075 Group_Delivery_Details_ERR EXCEPTION;
1076 l_appending_limit VARCHAR2(1);
1077 i NUMBER;
1078
1079 BEGIN
1080
1081 SAVEPOINT START_OF_APPEND_DELIVERIES;
1082 l_debug_on := WSH_DEBUG_INTERFACE.g_debug;
1083 --
1084 IF l_debug_on IS NULL THEN
1085 l_debug_on := WSH_DEBUG_SV.is_debug_enabled;
1086 END IF;
1087 IF l_debug_on THEN
1088 WSH_DEBUG_SV.push(l_module_name);
1089 WSH_DEBUG_SV.log(l_module_name, 'p_append_flag', p_append_flag);
1090 WSH_DEBUG_SV.log(l_module_name, 'p_group_by_header', p_group_by_header);
1091 WSH_DEBUG_SV.log(l_module_name, 'p_commit', p_commit);
1092 WSH_DEBUG_SV.log(l_module_name, 'p_lock_rows', p_lock_rows);
1093 WSH_DEBUG_SV.log(l_module_name, 'p_check_fte_compatibility', p_check_fte_compatibility);
1094 WSH_DEBUG_SV.log(l_module_name, 'WSH_PICK_LIST.G_BATCH_ID', WSH_PICK_LIST.G_BATCH_ID);
1095
1096 END IF;
1097
1098 x_return_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
1099 x_unappended_det_tbl.delete; -- unappended list
1100 x_appended_det_tbl.delete; -- successfully appended list
1101 x_appended_del_tbl.delete; -- appended deliveries
1102
1103
1104 -- need to check append_limit in shipping parameters
1105 l_index := p_delivery_detail_tbl.FIRST;
1106 WHILE l_index is NOT NULL LOOP
1107 IF l_debug_on THEN
1108 WSH_DEBUG_SV.log(l_module_name,'p_delivery_detail_tbl('||l_index||'): ',p_delivery_detail_tbl(l_index));
1109 END IF;
1110
1111
1112 OPEN get_line_details_check(p_delivery_detail_tbl(l_index));
1113 FETCH get_line_details_check INTO l_detail_info(l_index).entity_id,
1114 l_detail_info(l_index).ship_to_location_id,
1115 l_detail_info(l_index).ship_from_location_id,
1116 l_detail_info(l_index).customer_id,
1117 l_detail_info(l_index).intmed_ship_to_location_id,
1118 l_detail_info(l_index).fob_code,
1119 l_detail_info(l_index).freight_terms_code,
1120 l_detail_info(l_index).ship_method_code,
1121 l_detail_info(l_index).carrier_id,
1122 l_detail_info(l_index).source_header_id,
1123 l_detail_info(l_index).deliver_to_location_id,
1124 l_detail_info(l_index).organization_id,
1125 l_detail_info(l_index).date_scheduled,
1126 l_detail_info(l_index).date_requested,
1127 l_detail_info(l_index).status_code,
1128 l_detail_info(l_index).container_flag,
1129 l_detail_info(l_index).shipping_control,
1130 l_detail_info(l_index).party_id,
1131 l_detail_info(l_index).line_direction,
1132 l_detail_info(l_index).inventory_item_id,
1133 l_detail_info(l_index).source_code,
1134 l_detail_info(l_index).lpn_id,
1135 l_appending_limit,
1136 l_detail_info(l_index).ignore_for_planning; --bugfix 7164767
1137
1138
1139 IF get_line_details_check%NOTFOUND THEN
1140 x_unappended_det_tbl(x_unappended_det_tbl.count+1) := p_delivery_detail_tbl(l_index);
1141 l_detail_info.delete(l_index);
1142 CLOSE get_line_details_check;
1143 goto loop_end;
1144 ELSE
1145 CLOSE get_line_details_check;
1146 -- these are the lines that will be grouped
1147 IF p_lock_rows = FND_API.G_TRUE THEN
1148 -- lock the delivery detail record
1149 select delivery_detail_id into l_delivery_detail_id from wsh_delivery_details where
1150 delivery_detail_id = p_delivery_detail_tbl(l_index) for update nowait;
1151 END IF;
1152 IF NVL(p_append_flag, l_appending_limit) = 'N' and WSH_PICK_LIST.G_BATCH_ID is NULL THEN
1153 x_unappended_det_tbl(x_unappended_det_tbl.count+1) := l_detail_info(l_index).entity_id;
1154 l_detail_info.delete(l_index);
1155 ELSIF NVL(p_append_flag, l_appending_limit) = 'N' and WSH_PICK_LIST.G_BATCH_ID is NOT NULL THEN
1156 l_detail_info(l_index).batch_id := WSH_PICK_LIST.G_BATCH_ID;
1157
1158 END IF;
1159 END IF;
1160 <<loop_end>>
1161 l_index := p_delivery_detail_tbl.next(l_index);
1162 END LOOP;
1163
1164
1165
1166 IF l_debug_on THEN
1167 i := l_detail_info.FIRST;
1168 WHILE i is not NULL LOOP
1169 WSH_DEBUG_SV.log(l_module_name,' l_detail_info('||i||').entity_id', l_detail_info(i).entity_id);
1170 i := l_detail_info.next(i);
1171 END LOOP;
1172 END IF;
1173
1174
1175
1176 IF l_detail_info.count > 0 THEN
1177 l_unassigned_rows.delete;
1178
1179 IF l_debug_on THEN
1180 WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit Group_Delivery_Details');
1181 END IF;
1182
1183 l_action_rec.action := 'CREATE_GROUPS';
1184 l_action_rec.group_by_header_flag := p_group_by_header;
1185
1186 WSH_DELIVERY_AUTOCREATE.Find_Matching_Groups(
1187 p_attr_tab => l_detail_info,
1188 p_action_rec => l_action_rec,
1189 p_target_rec => l_target_rec,
1190 p_group_tab => l_group_info,
1191 x_matched_entities => l_matched_entities,
1192 x_out_rec => l_out_rec,
1193 x_return_status => l_return_status);
1194
1195 IF l_debug_on THEN
1196 WSH_DEBUG_SV.logmsg(l_module_name,' after calling Find_Matching_Groups');
1197 i := l_detail_info.FIRST;
1198 WHILE i is not NULL LOOP
1199 WSH_DEBUG_SV.log(l_module_name,' l_detail_info('||i||').entity_id', l_detail_info(i).entity_id);
1200 i := l_detail_info.next(i);
1201 END LOOP;
1202
1203 END IF;
1204
1205 IF l_return_status = WSH_UTIL_CORE.G_RET_STS_WARNING THEN
1206 l_warning_num := l_warning_num + 1;
1207 ELSIF (l_return_status = WSH_UTIL_CORE.G_RET_STS_ERROR OR
1208 l_return_status = WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR) THEN
1209 raise Group_Delivery_Details_ERR;
1210 END IF;
1211
1212 IF l_debug_on THEN
1213 WSH_DEBUG_SV.logmsg(l_module_name,'Return status from wsh_delivery_autocreate.Find_Matching_Groups: '|| l_return_status );
1214 WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit Auto_Assign_Deliveries');
1215 END IF;
1216
1217 Auto_Assign_Deliveries(
1218 p_line_rows => l_detail_info,
1219 p_group_by_header => p_group_by_header,
1220 p_check_fte_compatibility => p_check_fte_compatibility,
1221 x_assigned_rows => x_appended_det_tbl,
1222 x_unassigned_rows => l_unassigned_rows,
1223 x_appended_del_tbl => x_appended_del_tbl,
1224 x_return_status => l_return_status);
1225
1226 IF l_debug_on THEN
1227 WSH_DEBUG_SV.logmsg(l_module_name,'Return status from Auto_Assign_Deliveries: '|| l_return_status );
1228 END IF;
1229
1230 IF l_return_status = WSH_UTIL_CORE.G_RET_STS_WARNING THEN
1231 l_warning_num := l_warning_num + 1;
1232 IF l_unassigned_rows.count > 0 THEN
1233 l_index := l_unassigned_rows.FIRST;
1234 WHILE l_index IS NOT NULL LOOP
1235 x_unappended_det_tbl(x_unappended_det_tbl.count+1) := l_unassigned_rows(l_index);
1236 l_index := l_unassigned_rows.next(l_index);
1237 END LOOP;
1238 END IF;
1239 IF l_debug_on THEN
1240 WSH_DEBUG_SV.log(l_module_name,'x_unappended_det_tbl.count', x_unappended_det_tbl.count);
1241 END IF;
1242 ELSIF l_return_status = WSH_UTIL_CORE.G_RET_STS_ERROR OR
1243 l_return_status = WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR THEN
1244 raise Auto_Assign_Deliveries_ERR;
1245 END IF;
1246
1247
1248 IF p_commit = FND_API.G_TRUE THEN
1249 commit;
1250 END IF;
1251
1252 END IF;
1253
1254 IF x_unappended_det_tbl.count > 0 OR l_warning_num > 0 THEN
1255 x_return_status := WSH_UTIL_CORE.G_RET_STS_WARNING;
1256 END IF;
1257
1258 IF l_debug_on THEN
1259 wsh_debug_sv.pop(l_module_name);
1260 END IF;
1261
1262 EXCEPTION
1263
1264 WHEN Group_Delivery_Details_ERR THEN
1265 ROLLBACK TO START_OF_APPEND_DELIVERIES;
1266 x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
1267 FND_MESSAGE.SET_NAME('WSH', 'WSH_GRP_DETAILS_ERR');
1268 wsh_util_core.add_message(x_return_status, l_module_name);
1269
1270 IF l_debug_on THEN
1271 wsh_debug_sv.logmsg(l_module_name, 'Group_Delivery_Details failed');
1272 wsh_debug_sv.pop(l_module_name, 'EXCEPTION:Group_Delivery_Details_ERR');
1273 END IF;
1274
1275 WHEN Auto_Assign_Deliveries_ERR THEN
1276 ROLLBACK TO START_OF_APPEND_DELIVERIES;
1277 x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
1278 FND_MESSAGE.SET_NAME('WSH', 'WSH_AUTO_ASSIGN_ERR');
1279 wsh_util_core.add_message(x_return_status, l_module_name);
1280
1281 IF l_debug_on THEN
1282 wsh_debug_sv.logmsg(l_module_name, 'Auto_Assign_Deliveries failed');
1283 wsh_debug_sv.pop(l_module_name, 'EXCEPTION:Auto_Assign_Deliveries_ERR');
1284 END IF;
1285
1286 WHEN Others THEN
1287 ROLLBACK TO START_OF_APPEND_DELIVERIES;
1288 -- release all the locks
1289 IF get_line_details_check%ISOPEN THEN
1290 CLOSE get_line_details_check;
1291 END IF;
1292
1293 x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR;
1294 wsh_util_core.default_handler('WSH_DELIVERY_DETAILS_UTILITIES.Append_to_Deliveries');
1295 IF l_debug_on THEN
1296 wsh_debug_sv.log(l_module_name, 'Unexpected error has occured. Oracle error message is ' || SQLERRM);
1297 wsh_debug_sv.pop(l_module_name, 'EXCEPTION:OTHERS');
1298 END IF;
1299
1300 END Append_to_Deliveries;
1301
1302
1303
1304 END WSH_DELIVERY_DETAILS_UTILITIES;
1305
1306
[Home] [Help]