[Home] [Help]
PACKAGE BODY: APPS.WSH_FTE_COMP_CONSTRAINT_GRP
Source
1 PACKAGE BODY WSH_FTE_COMP_CONSTRAINT_GRP as
2 /* $Header: WSHFTGPB.pls 120.6 2006/05/04 23:17:59 alksharm ship $ */
3
4 -- Global Variables
5
6 -- p_action_code can be G_ACTION_CREATE or G_ACTION_UPDATE
7
8 --========================================================================
9 -- PROCEDURE : validate_constraint To be called by external module
10 -- Currently called by WMS only
11 --
12 -- PARAMETERS: p_api_version_number Application standard : known API version number
13 -- p_init_msg_list FND_API.G_TRUE to reset list
14 -- p_commit Application standard : to commit work
15 -- p_entity_tab Table of entity records to validate
16 -- Currently supports Delivery and Trip
17 -- x_msg_count Number of messages in the list
21 -- A. Update/Manually Create a delivery (UPD)
18 -- x_msg_data Text of messages
19 -- x_return_status Return status
20 -- COMMENT : This procedure is used by external modules perform following actions
22 -- B. Update/Manually Create a Trip
23 --========================================================================
24
25 PROCEDURE validate_constraint(
26 p_api_version_number IN NUMBER,
27 p_init_msg_list IN VARCHAR2,
28 p_commit IN VARCHAR2 DEFAULT FND_API.G_FALSE,
29 p_entity_tab IN OUT NOCOPY wshfte_ccin_tab_type,
30 x_msg_count OUT NOCOPY NUMBER,
31 x_msg_data OUT NOCOPY VARCHAR2,
32 x_return_status OUT NOCOPY VARCHAR2)
33 IS
34
35 i NUMBER := 0;
36 k NUMBER := 0;
37 l NUMBER := 0;
38 l_return_status VARCHAR2(1);
39 l_api_version_number CONSTANT NUMBER := 1.0;
40 l_api_name CONSTANT VARCHAR2(30):= 'validate_constraint';
41
42 l_validate_result VARCHAR2(1) := 'S'; -- Constraint Validation result : S / F
43 l_action_code VARCHAR2(30) := NULL;
44 l_dummy_exception_list WSH_UTIL_CORE.column_tab_type;
45
46 l_dummy_in_ids WSH_UTIL_CORE.id_tab_type; -- Either of the next two
47 l_delivery_info WSH_FTE_CONSTRAINT_FRAMEWORK.delivery_ccinfo_tab_type;
48 l_delivery_rec WSH_FTE_CONSTRAINT_FRAMEWORK.delivery_ccinfo_rec_type;
49 l_dlvy_assigned_lines WSH_FTE_CONSTRAINT_FRAMEWORK.detail_ccinfo_tab_type;
50 l_target_trip WSH_FTE_CONSTRAINT_FRAMEWORK.trip_ccinfo_rec_type;
51 l_target_tripstops WSH_FTE_CONSTRAINT_FRAMEWORK.target_tripstop_cc_rec_type;
52 l_target_trip_assign_dels WSH_FTE_CONSTRAINT_FRAMEWORK.delivery_ccinfo_tab_type;
53 l_target_trip_dlvy_lines WSH_FTE_CONSTRAINT_FRAMEWORK.detail_ccinfo_tab_type;
54 l_target_trip_incl_stops WSH_FTE_CONSTRAINT_FRAMEWORK.stop_ccinfo_tab_type;
55 l_line_groups WSH_FTE_COMP_CONSTRAINT_PKG.line_group_tab_type;
56 l_group_info WSH_FTE_COMP_CONSTRAINT_PKG.cc_group_tab_type;
57 l_failed_lines WSH_FTE_COMP_CONSTRAINT_PKG.failed_line_tab_type;
58
59 l_trip_info WSH_FTE_CONSTRAINT_FRAMEWORK.trip_ccinfo_tab_type;
60 l_trip_rec WSH_FTE_CONSTRAINT_FRAMEWORK.trip_ccinfo_rec_type;
61 l_trip_assigned_dels WSH_FTE_CONSTRAINT_FRAMEWORK.delivery_ccinfo_tab_type;
62 l_trip_dlvy_lines WSH_FTE_CONSTRAINT_FRAMEWORK.detail_ccinfo_tab_type;
63 l_trip_incl_stops WSH_FTE_CONSTRAINT_FRAMEWORK.stop_ccinfo_tab_type;
64 l_fail_trips WSH_UTIL_CORE.id_tab_type;
65
66 l_debug_on CONSTANT BOOLEAN := WSH_DEBUG_SV.is_debug_enabled;
67 l_module_name CONSTANT VARCHAR2(100) := 'wsh.plsql.' || g_package_name || '.' || 'validate_constraint';
68
69 BEGIN
70
71 x_return_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
72
73 IF l_debug_on THEN
74 wsh_debug_sv.push (l_module_name);
75 END IF;
76
77 ---------------------------------------
78
79 IF NOT FND_API.Compatible_API_Call
80 ( l_api_version_number,
81 p_api_version_number,
82 l_api_name,
83 g_package_name
84 )
85 THEN
86 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
87 END IF;
88
89 IF FND_API.to_Boolean( p_init_msg_list ) THEN
90 FND_MSG_PUB.initialize;
91 END IF;
92
93 -----------------------------------------
94
95 i := p_entity_tab.FIRST;
96 IF i IS NOT NULL THEN
97 LOOP
98
99 -- Defaulted to Success
100 p_entity_tab(i).x_validate_status := 'S';
101
102 IF l_debug_on THEN
103 wsh_debug_sv.logmsg(l_module_name,'entity type : '||p_entity_tab(i).p_entity_type);
104 wsh_debug_sv.logmsg(l_module_name,'entity id : '||p_entity_tab(i).p_entity_id);
105 wsh_debug_sv.logmsg(l_module_name,'action code : '||p_entity_tab(i).p_action_code);
106 END IF;
107
108 IF p_entity_tab(i).p_action_code <> G_ACTION_UPDATE THEN
109
110 GOTO entity_nextpass;
111 END IF;
112
113 IF p_entity_tab(i).p_entity_type = G_TRIP THEN
114
115 -- Populate l_trip_rec
116
117 l_trip_info(i).TRIP_ID := p_entity_tab(i).p_entity_id;
118 l_trip_info(i).exists_in_database :='Y';
119 l_trip_info(i).PLANNED_FLAG := p_entity_tab(i).p_PLANNED_FLAG;
120 l_trip_info(i).STATUS_CODE := p_entity_tab(i).p_STATUS_CODE;
121 l_trip_info(i).VEHICLE_ITEM_ID := p_entity_tab(i).p_VEH_ITEM_ID;
122 l_trip_info(i).CARRIER_ID := p_entity_tab(i).p_CARRIER_ID;
123 l_trip_info(i).SHIP_METHOD_CODE := p_entity_tab(i).p_SHIPMETHOD_CODE;
124 l_trip_info(i).VEHICLE_ORGANIZATION_ID := p_entity_tab(i).p_organization_id;
125 l_trip_info(i).SERVICE_LEVEL := p_entity_tab(i).p_SERVICE_LEVEL;
126 l_trip_info(i).MODE_OF_TRANSPORT := p_entity_tab(i).p_MODE_CODE;
127
128
129 ELSIF p_entity_tab(i).p_entity_type = G_DELIVERY THEN
130
131 -- Populate l_delivery_rec
132
133 l_delivery_info(i).DELIVERY_ID := p_entity_tab(i).p_entity_id;
134 l_delivery_info(i).exists_in_database := 'Y';
135 l_delivery_info(i).PLANNED_FLAG := p_entity_tab(i).p_PLANNED_FLAG;
136 l_delivery_info(i).STATUS_CODE := p_entity_tab(i).p_STATUS_CODE;
140 l_delivery_info(i).INTMED_SHIP_TO_LOCATION_ID := p_entity_tab(i).p_INTMED_LOCATION_ID;
137 l_delivery_info(i).INITIAL_PICKUP_LOCATION_ID := p_entity_tab(i).p_SHIP_FROM_LOCATION_ID;
138 l_delivery_info(i).ULTIMATE_DROPOFF_LOCATION_ID := p_entity_tab(i).p_SHIP_TO_LOCATION_ID;
139 l_delivery_info(i).CUSTOMER_ID := p_entity_tab(i).p_CUSTOMER_ID;
141 l_delivery_info(i).SHIP_METHOD_CODE := p_entity_tab(i).p_SHIPMETHOD_CODE;
142 l_delivery_info(i).CARRIER_ID := p_entity_tab(i).p_CARRIER_ID;
143 l_delivery_info(i).ORGANIZATION_ID := p_entity_tab(i).p_ORGANIZATION_ID;
144 l_delivery_info(i).SERVICE_LEVEL := p_entity_tab(i).p_SERVICE_LEVEL;
145 l_delivery_info(i).MODE_OF_TRANSPORT := p_entity_tab(i).p_MODE_CODE;
146
147
148 END IF;
149
150 <<entity_nextpass>>
151
152 EXIT WHEN i = p_entity_tab.LAST;
153 i := p_entity_tab.NEXT(i);
154
155 END LOOP;
156 END IF;
157
158 IF l_trip_info.COUNT > 0 THEN
159
160 l_action_code := WSH_FTE_CONSTRAINT_FRAMEWORK.G_UPDATE_TRIP;
161
162 WSH_FTE_CONSTRAINT_FRAMEWORK.validate_constraint_trip(
163 p_init_msg_list => p_init_msg_list,
164 p_action_code => l_action_code,
165 p_exception_list => l_dummy_exception_list,
166 p_trip_info => l_trip_info,
167 p_trip_assigned_dels => l_trip_assigned_dels,
168 p_trip_dlvy_lines => l_trip_dlvy_lines,
169 p_trip_incl_stops => l_trip_incl_stops,
170 x_fail_trips => l_fail_trips,
171 x_validate_result => l_validate_result,
172 x_msg_count => x_msg_count,
173 x_msg_data => x_msg_data,
174 x_return_status => l_return_status);
175
176
177 IF l_return_status = WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR THEN
178 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
179 ELSE
180 x_return_status := l_return_status;
181 IF l_debug_on THEN
182 wsh_debug_sv.logmsg(l_module_name,'failed line count : '||l_fail_trips.COUNT);
183 END IF;
184
185 IF l_return_status = WSH_UTIL_CORE.G_RET_STS_ERROR THEN
186
187 l:=p_entity_tab.FIRST;
188 IF l IS NOT NULL THEN
189 LOOP
190
191 -- LOOP over l_fail_trips
192 k:=l_fail_trips.FIRST;
193 IF k IS NOT NULL THEN
194 LOOP
195
196 IF p_entity_tab(l).p_entity_id = l_fail_trips(k) THEN
197 p_entity_tab(l).x_validate_status := 'F';
198 IF l_debug_on THEN
199 wsh_debug_sv.logmsg(l_module_name,'failed line : '||l_fail_trips(k)||' found for entity : '||p_entity_tab(l).p_entity_id);
200 END IF;
201 EXIT;
202 END IF;
203
204 EXIT WHEN k=l_fail_trips.LAST;
205 k:=l_fail_trips.NEXT(k);
206 END LOOP;
207 END IF;
208
209 EXIT WHEN l=p_entity_tab.LAST;
210 l:=p_entity_tab.NEXT(l);
211 END LOOP;
212 END IF;
213
214 END IF;
215 END IF;
216 END IF; -- l_trip_info
217
218 IF l_delivery_info.COUNT > 0 THEN
219
220 l_action_code := WSH_FTE_CONSTRAINT_FRAMEWORK.G_UPDATE_DLVY;
221
222 WSH_FTE_CONSTRAINT_FRAMEWORK.validate_constraint_dlvy(
223 p_init_msg_list => p_init_msg_list,
224 p_action_code => l_action_code,
225 p_exception_list => l_dummy_exception_list,
226 p_in_ids => l_dummy_in_ids,
227 p_delivery_info => l_delivery_info,
228 p_dlvy_assigned_lines => l_dlvy_assigned_lines,
229 p_target_trip => l_target_trip,
230 p_target_tripstops => l_target_tripstops,
231 p_target_trip_assign_dels => l_target_trip_assign_dels,
232 p_target_trip_dlvy_lines => l_target_trip_dlvy_lines,
233 p_target_trip_incl_stops => l_target_trip_incl_stops,
234 x_validate_result => l_validate_result,
235 x_line_groups => l_line_groups,
236 x_group_info => l_group_info,
237 x_failed_lines => l_failed_lines,
238 x_msg_count => x_msg_count,
239 x_msg_data => x_msg_data,
240 x_return_status => l_return_status);
241
242
243 IF l_return_status = WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR THEN
244 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
245 ELSE
246 x_return_status := l_return_status;
247
248 IF l_return_status = WSH_UTIL_CORE.G_RET_STS_ERROR THEN
249
250 IF l_debug_on THEN
251 wsh_debug_sv.logmsg(l_module_name,'failed line count : '||l_failed_lines.COUNT);
252 END IF;
253
254 l:=p_entity_tab.FIRST;
255 IF l IS NOT NULL THEN
256 LOOP
257
261 LOOP
258 -- LOOP over l_failed_lines
259 k:=l_failed_lines.FIRST;
260 IF k IS NOT NULL THEN
262
263 IF p_entity_tab(l).p_entity_id = l_failed_lines(k).entity_line_id THEN
264 p_entity_tab(l).x_validate_status := 'F';
265 IF l_debug_on THEN
266 wsh_debug_sv.logmsg(l_module_name,'failed line : '||l_failed_lines(k).entity_line_id||' found for entity : '||p_entity_tab(l).p_entity_id);
267 END IF;
268
269 EXIT;
270 END IF;
271
272 EXIT WHEN k=l_failed_lines.LAST;
273 k:=l_failed_lines.NEXT(k);
274 END LOOP;
275 END IF;
276
277 EXIT WHEN l=p_entity_tab.LAST;
278 l:=p_entity_tab.NEXT(l);
279 END LOOP;
280 END IF;
281
282 END IF;
283 END IF;
284
285 END IF;
286
287 --
288 IF l_debug_on THEN
289 WSH_DEBUG_SV.pop(l_module_name);
290 END IF;
291 --
292 EXCEPTION
293 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
294 x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR ;
295 --
296 IF l_debug_on THEN
297 WSH_DEBUG_SV.logmsg(l_module_name,'Unexpected error has occured. Oracle error message is '|| SQLERRM,WSH_DEBUG_SV.C_UNEXPEC_ERR_LEVEL);
298 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:FND_API.G_EXC_UNEXPECTED_ERROR');
299 END IF;
300 --
301 WHEN others THEN
302 WSH_UTIL_CORE.default_handler('WSH_FTE_COMP_CONSTRAINT_GRP.validate_constraint');
303 x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR;
304 --
305 IF l_debug_on THEN
306 WSH_DEBUG_SV.logmsg(l_module_name,'Unexpected error has occured. Oracle error message is '|| SQLERRM,WSH_DEBUG_SV.C_UNEXPEC_ERR_LEVEL);
307 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:FND_API.G_EXC_UNEXPECTED_ERROR');
308 END IF;
309 --
310
311 END validate_constraint;
312
313 --***************************************************************************--
314 --========================================================================
315 -- PROCEDURE : is_valid_consol
316 --
317 -- PARAMETERS: p_init_msg_list FND_API.G_TRUE to reset list
318 -- p_input_delivery_id_tab Table of delivery records to process
319 --
320 -- p_target_consol_delivery_id target consol delivery
321 -- x_deconsolidation_location deconsolidation location
322 -- x_msg_count Number of messages in the list
323 -- x_msg_data Text of messages
324 -- x_return_status Return status
325 -- COMMENT : This procedure is to find if a set of deliveries can be assigned to a consol delivery.
326 -- This procedure is called from WMS.
327 --
328 --========================================================================
329 PROCEDURE is_valid_consol( p_init_msg_list IN VARCHAR2 DEFAULT fnd_api.g_false,
330 p_input_delivery_id_tab IN WSH_UTIL_CORE.id_tab_type,
331 p_target_consol_delivery_id IN NUMBER,
332 p_caller IN VARCHAR2 DEFAULT NULL,
333 x_deconsolidation_location OUT NOCOPY NUMBER,
334 x_return_status OUT NOCOPY VARCHAR2,
335 x_msg_count OUT NOCOPY NUMBER,
336 x_msg_data OUT NOCOPY VARCHAR2
337 )
338 IS
339
340 l_cc_action_code VARCHAR2(3);
341 l_validate_result VARCHAR2(1) := 'S';
342 l_line_groups WSH_FTE_COMP_CONSTRAINT_PKG.line_group_tab_type;
343 l_group_info WSH_FTE_COMP_CONSTRAINT_PKG.cc_group_tab_type;
344 l_failed_lines WSH_FTE_COMP_CONSTRAINT_PKG.failed_line_tab_type;
345 l_msg_count NUMBER;
346 l_msg_data VARCHAR2(2000) := NULL;
347 l_return_status VARCHAR2(1);
348 l_in_ids WSH_UTIL_CORE.id_tab_type;
349 l_deconsol_location NUMBER;
350 l_prev_deconsol_location NUMBER;
351 l_consol_drop_off_loc NUMBER;
352 l_consol_trip_id NUMBER;
353 l_prev_consolidation_del NUMBER;
354 l_pre_consolidation_del NUMBER;
355 l_target_trip WSH_FTE_CONSTRAINT_FRAMEWORK.trip_ccinfo_rec_type;
356 l_target_trip_stop WSH_FTE_CONSTRAINT_FRAMEWORK.target_tripstop_cc_rec_type;
357 d_itr NUMBER;
358 l_cc_exception_list WSH_UTIL_CORE.Column_Tab_Type;
359 x_output_id_tab WSH_FTE_CONSTRAINT_FRAMEWORK.deconsol_output_tab_type;
360
361 l_cc_delivery_info WSH_FTE_CONSTRAINT_FRAMEWORK.delivery_ccinfo_tab_type;
362 l_cc_dlvy_assigned_lines WSH_FTE_CONSTRAINT_FRAMEWORK.detail_ccinfo_tab_type;
363 l_cc_target_trip_assign_dels WSH_FTE_CONSTRAINT_FRAMEWORK.delivery_ccinfo_tab_type;
364 l_cc_target_trip_dlvy_lines WSH_FTE_CONSTRAINT_FRAMEWORK.detail_ccinfo_tab_type;
365 l_cc_target_trip_incl_stops WSH_FTE_CONSTRAINT_FRAMEWORK.stop_ccinfo_tab_type;
366 l_delivery_info WSH_FTE_CONSTRAINT_FRAMEWORK.delivery_ccinfo_tab_type;
367
368 l_module_name CONSTANT VARCHAR2(100) := 'wsh.plsql.' || g_package_name || '.' || 'is_valid_consol';
369 l_debug_on CONSTANT BOOLEAN := WSH_DEBUG_SV.is_debug_enabled;
370
371 CURSOR c_get_dlvy_drop_off_location(c_delivery_id IN NUMBER) IS
372 SELECT
376
373 wnd.ULTIMATE_DROPOFF_LOCATION_ID
374 FROM wsh_new_deliveries wnd
375 WHERE wnd.delivery_id = c_delivery_id;
377 CURSOR c_get_consol_dlvy(c_delivery_id IN NUMBER) IS
378 SELECT
379 wda.parent_delivery_id
380 FROM wsh_delivery_assignments wda
381 WHERE wda.delivery_id = c_delivery_id
382 AND TYPE= 'C';
383
384 CURSOR c_get_dlvy_trip_id(c_delivery_id IN NUMBER) IS
385 SELECT
386 wts.TRIP_ID, 'Y'as EXISTS_IN_DATABASE, wt. NAME, wt.PLANNED_FLAG, wt.STATUS_CODE,
387 wt.VEHICLE_ITEM_ID, wt.VEHICLE_NUMBER, wt.CARRIER_ID, wt.SHIP_METHOD_CODE,
388 wt.VEHICLE_ORGANIZATION_ID, wt.VEHICLE_NUM_PREFIX, wt.SERVICE_LEVEL, wt.MODE_OF_TRANSPORT
389 FROM wsh_trip_stops wts, wsh_delivery_legs wdl, wsh_trips wt
390 WHERE wdl.delivery_id = c_delivery_id
391 AND wdl.pick_up_stop_id = wts.stop_id
392 AND wts.trip_id = wt.trip_id;
393
394
395 BEGIN
396 --{
397 x_return_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
398
399 IF l_debug_on THEN
400 wsh_debug_sv.push (l_module_name);
401 END IF;
402
403 IF l_debug_on THEN
404 WSH_DEBUG_SV.logmsg(l_module_name,'p_target_consol_delivery_id' || p_target_consol_delivery_id);
405 END IF;
406
407 l_deconsol_location := NULL;
408
409 IF WSH_FTE_CONSTRAINT_FRAMEWORK.g_is_fte_installed = 'Y' THEN
410 --
411 -- check if deliveries do not have any conflicting constraints so that
412 -- they can be put in one group
413 --
414 l_cc_action_code := WSH_FTE_CONSTRAINT_FRAMEWORK.G_AUTOCRT_MDC;
415
416 WSH_FTE_CONSTRAINT_FRAMEWORK.validate_constraint_dlvy(
417 p_init_msg_list => p_init_msg_list,
418 p_action_code => l_cc_action_code,
419 p_exception_list => l_cc_exception_list,
420 p_delivery_info => l_cc_delivery_info,
421 p_in_ids => p_input_delivery_id_tab,
422 p_dlvy_assigned_lines => l_cc_dlvy_assigned_lines,
423 p_target_trip => l_target_trip,
424 p_target_tripstops => l_target_trip_stop,
425 p_target_trip_assign_dels => l_cc_target_trip_assign_dels,
426 p_target_trip_dlvy_lines => l_cc_target_trip_dlvy_lines,
427 p_target_trip_incl_stops => l_cc_target_trip_incl_stops,
428 x_validate_result => l_validate_result,
429 x_failed_lines => l_failed_lines,
430 x_line_groups => l_line_groups,
431 x_group_info => l_group_info,
432 x_msg_count => l_msg_count,
433 x_msg_data => l_msg_data,
434 x_return_status => l_return_status);
435
436 IF l_debug_on THEN
437 WSH_DEBUG_SV.logmsg(l_module_name,'l_return_status ' || l_return_status);
438 WSH_DEBUG_SV.logmsg(l_module_name,'l_group_info.COUNT ' || l_group_info.COUNT);
439 WSH_DEBUG_SV.logmsg(l_module_name,'l_failed_lines.COUNT ' || l_failed_lines.COUNT);
440 END IF;
441 --
442 -- If more than one group formed, error out
443 --
444 -- AG number of groups is record structure l_group_info not l_line_groups
445 --IF l_line_groups.COUNT > 1 OR l_failed_lines.COUNT > 0 OR (l_return_status <> WSH_UTIL_CORE.G_RET_STS_SUCCESS
446 IF l_group_info.COUNT > 1 OR l_failed_lines.COUNT > 0 OR (l_return_status <> WSH_UTIL_CORE.G_RET_STS_SUCCESS
447 AND l_return_status <> WSH_UTIL_CORE.G_RET_STS_WARNING) THEN
448 -- AG
449 -- Treat l_return_status = WARNING as success
450
451 x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
452 FND_MESSAGE.SET_NAME('WSH','WSH_CONSOL_VALIDATION_ERROR');
453 FND_MSG_PUB.ADD;
454 IF l_debug_on THEN
455 wsh_debug_sv.pop (l_module_name);
456 END IF;
457 RETURN;
458 END IF;
459
460 END IF; -- FTE is installed -- Bug 5194370
461
462 --{
463
464 --
465 -- loop through deliveries in order to find deconsolidation location for each delivery
466 --
467
468 d_itr := p_input_delivery_id_tab.FIRST;
469
470 IF d_itr IS NOT NULL THEN
471 --{
472 LOOP
473 --{
474 l_in_ids(1) := p_input_delivery_id_tab(d_itr);
475
476 IF l_debug_on THEN
477 WSH_DEBUG_SV.logmsg(l_module_name,'p_input_delivery_id_tab ' || p_input_delivery_id_tab(d_itr));
478 END IF;
479
480 -- AG
481 -- Still has not implemented latest comments
482 -- skattama
483
484 OPEN c_get_consol_dlvy(l_in_ids(1));
485 FETCH c_get_consol_dlvy INTO l_pre_consolidation_del;
486 CLOSE c_get_consol_dlvy;
487 IF l_pre_consolidation_del IS NOT NULL THEN
488 IF l_prev_consolidation_del IS NULL THEN
489 l_prev_consolidation_del := l_pre_consolidation_del;
490 ELSIF l_prev_consolidation_del <> l_pre_consolidation_del OR l_pre_consolidation_del <> p_target_consol_delivery_id THEN
491 x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
492 FND_MESSAGE.SET_NAME('WSH','WSH_MULTICONSOL_ERROR');
493 FND_MSG_PUB.ADD;
494 IF l_debug_on THEN
495 wsh_debug_sv.pop (l_module_name);
496 END IF;
497 RETURN;
498 END IF;
499 END IF;
500 -- skattama
504 p_delivery_info => l_delivery_info,
501
502 WSH_FTE_CONSTRAINT_FRAMEWORK.validate_constraint_deconsol(
503 p_init_msg_list => p_init_msg_list,
505 p_in_ids => l_in_ids,
506 p_caller => p_caller,
507 x_output_id_tab => x_output_id_tab,
508 x_return_status => l_return_status,
509 x_msg_count => l_msg_count,
510 x_msg_data => l_msg_data);
511
512 --
513 -- If deconsolidation location found for any of the
514 -- delivery is different, error out
515 --
516
517 IF x_output_id_tab(1).validation_status = WSH_UTIL_CORE.G_RET_STS_SUCCESS THEN
518
519 IF l_delivery_info.COUNT > 0 THEN
520 l_delivery_info.DELETE;
521 END IF;
522
523 l_deconsol_location := x_output_id_tab(x_output_id_tab.FIRST).deconsol_location;
524
525 IF l_prev_deconsol_location IS NULL THEN
526 l_prev_deconsol_location := l_deconsol_location;
527 END IF;
528
529 IF l_deconsol_location <> l_prev_deconsol_location THEN
530 x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
531 FND_MESSAGE.SET_NAME('WSH','WSH_CONSOL_VALIDATION_ERROR');
532 FND_MSG_PUB.ADD;
533 IF l_debug_on THEN
534 wsh_debug_sv.pop (l_module_name);
535 END IF;
536 RETURN;
537 END IF;
538 ELSE
539 x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
540 FND_MESSAGE.SET_NAME('WSH','WSH_CONSOL_VALIDATION_ERROR');
541 FND_MSG_PUB.ADD;
542 IF l_debug_on THEN
543 wsh_debug_sv.pop (l_module_name);
544 END IF;
545 RETURN;
546 END IF;
547 EXIT WHEN d_itr = p_input_delivery_id_tab.LAST;
548 d_itr := p_input_delivery_id_tab.NEXT(d_itr);
549 l_prev_deconsol_location := l_deconsol_location;
550 --}
551 END LOOP;
552 --}
553 END IF;
554
555 IF l_debug_on THEN
556 WSH_DEBUG_SV.logmsg(l_module_name,'l_deconsol_location' || l_deconsol_location);
557 END IF;
558
559 -- AG
560 IF l_deconsol_location IS NOT NULL AND p_target_consol_delivery_id IS NOT NULL THEN
561 --IF p_target_consol_delivery_id IS NOT NULL THEN
562 --{
563
564 -- get ultimate drop off location for target consol
565
566 OPEN c_get_dlvy_drop_off_location(p_target_consol_delivery_id);
567 FETCH c_get_dlvy_drop_off_location INTO l_consol_drop_off_loc;
568 CLOSE c_get_dlvy_drop_off_location;
569
570 --
571 -- if drop off location for consol is same as that of deconsol location of
572 -- input delivery, check if deliveries do not have any conflicting constraints with
573 -- deliveries in target consol
574 --
575
576 -- AG
577 IF l_consol_drop_off_loc = l_deconsol_location THEN
578 --{
579 IF WSH_FTE_CONSTRAINT_FRAMEWORK.g_is_fte_installed = 'Y' THEN -- Bug 5194370
580 OPEN c_get_dlvy_trip_id(p_target_consol_delivery_id);
581 FETCH c_get_dlvy_trip_id INTO l_target_trip;
582 CLOSE c_get_dlvy_trip_id;
583
584 l_target_trip_stop.dropoff_location_id := l_deconsol_location;
585
586 l_cc_action_code := WSH_FTE_CONSTRAINT_FRAMEWORK.G_ASSIGN_DLVY_TRIP;
587
588 WSH_FTE_CONSTRAINT_FRAMEWORK.validate_constraint_dlvy(
589 p_init_msg_list => p_init_msg_list,
590 p_action_code => l_cc_action_code,
591 p_exception_list => l_cc_exception_list,
592 p_delivery_info => l_cc_delivery_info,
593 p_in_ids => p_input_delivery_id_tab,
594 p_dlvy_assigned_lines => l_cc_dlvy_assigned_lines,
595 p_target_trip => l_target_trip,
596 p_target_tripstops => l_target_trip_stop,
597 p_target_trip_assign_dels => l_cc_target_trip_assign_dels,
598 p_target_trip_dlvy_lines => l_cc_target_trip_dlvy_lines,
599 p_target_trip_incl_stops => l_cc_target_trip_incl_stops,
600 x_validate_result => l_validate_result,
601 x_failed_lines => l_failed_lines,
602 x_line_groups => l_line_groups,
603 x_group_info => l_group_info,
604 x_msg_count => l_msg_count,
605 x_msg_data => l_msg_data,
606 x_return_status => l_return_status);
607
608 -- AG
609 -- Treat warning as Success
610
611 IF l_return_status = WSH_UTIL_CORE.G_RET_STS_SUCCESS OR l_return_status = WSH_UTIL_CORE.G_RET_STS_WARNING THEN
612 x_return_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
613 x_deconsolidation_location := l_deconsol_location;
614 ELSE
615 x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
616 FND_MESSAGE.SET_NAME('WSH','WSH_CONSOL_VALIDATION_ERROR');
617 FND_MSG_PUB.ADD;
618 IF l_debug_on THEN
619 wsh_debug_sv.pop (l_module_name);
620 END IF;
621 RETURN;
622 END IF;
623 ELSE -- Bug 5194370
624 x_return_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
625 x_deconsolidation_location := l_deconsol_location;
626 END IF;
627
628 --}
629 ELSE
630 x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
631 FND_MESSAGE.SET_NAME('WSH','WSH_CONSOL_VALIDATION_ERROR');
632 FND_MSG_PUB.ADD;
633 IF l_debug_on THEN
634 wsh_debug_sv.pop (l_module_name);
635 END IF;
636 RETURN;
637 END IF;
638 --}
639 --ELSE
640 -- AG
641 -- If input deliveries do not get back any deconsol_location above
642 -- should they rather be allowed to be assigned to p_target_consol_delivery_id
643 ELSIF p_target_consol_delivery_id IS NOT NULL THEN
644 x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
645 FND_MESSAGE.SET_NAME('WSH','WSH_CONSOL_VALIDATION_ERROR');
646 FND_MSG_PUB.ADD;
647 IF l_debug_on THEN
648 wsh_debug_sv.pop (l_module_name);
649 END IF;
650 RETURN;
651 ELSIF l_deconsol_location IS NOT NULL THEN
652 x_deconsolidation_location := l_deconsol_location;
653 END IF;
654
655 FND_MSG_PUB.Count_And_Get (
656 p_count => x_msg_count,
657 p_data => x_msg_data ,
658 p_encoded => FND_API.G_FALSE );
659
660 IF l_debug_on THEN
661 wsh_debug_sv.log (l_module_name,'No. of messages stacked : ',to_char(x_msg_count));
662 wsh_debug_sv.pop (l_module_name);
663 END IF;
664
665 EXCEPTION
666
667 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
668 x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR ;
669 --
670 IF l_debug_on THEN
671 WSH_DEBUG_SV.logmsg(l_module_name,'Unexpected error has occured. Oracle error message is '|| SQLERRM,WSH_DEBUG_SV.C_UNEXPEC_ERR_LEVEL);
672 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:FND_API.G_EXC_UNEXPECTED_ERROR');
673 END IF;
674
675 WHEN OTHERS THEN
676 IF c_get_dlvy_drop_off_location%ISOPEN THEN
677 CLOSE c_get_dlvy_drop_off_location;
678 END IF;
679 IF c_get_dlvy_trip_id%ISOPEN THEN
680 CLOSE c_get_dlvy_trip_id;
681 END IF;
682 WSH_UTIL_CORE.default_handler('WSH_FTE_COMP_CONSTRAINT_GRP.is_valid_consol');
683 x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR ;
684 --
685 IF l_debug_on THEN
686 WSH_DEBUG_SV.logmsg(l_module_name,'Unexpected error has occured. Oracle error message is '|| SQLERRM,WSH_DEBUG_SV.C_UNEXPEC_ERR_LEVEL);
687 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:FND_API.G_EXC_UNEXPECTED_ERROR');
688 END IF;
689 --}
690 END is_valid_consol;
691
692 END WSH_FTE_COMP_CONSTRAINT_GRP;
693