[Home] [Help]
PACKAGE BODY: APPS.WSH_FTE_COMP_CONSTRAINT_PKG
Source
1 PACKAGE BODY WSH_FTE_COMP_CONSTRAINT_PKG as
2 /* $Header: WSHFTCCB.pls 120.0 2005/05/26 18:23:00 appldev noship $ */
3
4 -- Global Variables
5 G_PKG_NAME CONSTANT VARCHAR2(30) := 'WSH_FTE_COMP_CONSTRAINT_PKG';
6
7
8 -- Wrapper for calling validate_constraint_dlvy with approp. parameters populated for diff. actions
9 -- For compatibility constraints project
10 -- auto pack, auto pack master do not have any constraints which are implemented in I so they will not be used as of now
11
12
13 PROCEDURE validate_constraint_main
14 ( p_api_version_number IN NUMBER,
15 p_init_msg_list IN VARCHAR2,
16 p_entity_type IN VARCHAR2,
17 p_target_id IN NUMBER,
18 p_action_code IN VARCHAR2,
19 p_del_attr_tab IN WSH_NEW_DELIVERIES_PVT.Delivery_Attr_Tbl_Type ,
20 p_det_attr_tab IN WSH_GLBL_VAR_STRCT_GRP.Delivery_Details_Attr_Tbl_Type,
21 p_trip_attr_tab IN WSH_TRIPS_PVT.Trip_Attr_Tbl_Type,
22 p_stop_attr_tab IN WSH_TRIP_STOPS_PVT.Stop_Attr_Tbl_Type,
23 p_in_ids IN wsh_util_core.id_tab_type,
24 p_pickup_stop_id IN NUMBER DEFAULT NULL,
25 p_pickup_loc_id IN NUMBER DEFAULT NULL,
26 p_pickup_stop_seq IN NUMBER DEFAULT NULL,
27 p_dropoff_stop_id IN NUMBER DEFAULT NULL,
28 p_dropoff_loc_id IN NUMBER DEFAULT NULL,
29 p_dropoff_stop_seq IN NUMBER DEFAULT NULL,
30 p_pickup_arr_date IN DATE DEFAULT NULL,
31 p_pickup_dep_date IN DATE DEFAULT NULL,
32 p_dropoff_arr_date IN DATE DEFAULT NULL,
33 p_dropoff_dep_date IN DATE DEFAULT NULL,
34 x_validate_result OUT NOCOPY VARCHAR2,
35 x_failed_lines OUT NOCOPY failed_line_tab_type,
36 x_line_groups OUT NOCOPY line_group_tab_type,
37 x_group_info OUT NOCOPY cc_group_tab_type,
38 x_fail_ids OUT NOCOPY wsh_util_core.id_tab_type,
39 x_return_status OUT NOCOPY VARCHAR2,
40 x_msg_count OUT NOCOPY NUMBER,
41 x_msg_data OUT NOCOPY VARCHAR2
42 )
43 IS
44
45 l_api_version_number CONSTANT NUMBER := 1.0;
46 l_api_name CONSTANT VARCHAR2(30):= 'validate_constraint_main';
47
48 --
49 l_debug_on BOOLEAN;
50 --
51 l_module_name CONSTANT VARCHAR2(200) := 'wsh.plsql.' || G_PKG_NAME || '.' || 'validate_constraint_main';
52 --
53 l_cc_action_code VARCHAR2(3);
54 l_cc_count NUMBER;
55 l_cc_count_dd NUMBER;
56 l_cc_count_del NUMBER;
57 l_cc_count_stop NUMBER;
58 l_cc_count_trip NUMBER;
59
60 l_cc_delivery_info WSH_FTE_CONSTRAINT_FRAMEWORK.delivery_ccinfo_tab_type;
61 l_cc_dlvy_assigned_lines WSH_FTE_CONSTRAINT_FRAMEWORK.detail_ccinfo_tab_type;
62 l_cc_target_trip WSH_FTE_CONSTRAINT_FRAMEWORK.trip_ccinfo_rec_type;
63 l_cc_target_trip_assign_dels WSH_FTE_CONSTRAINT_FRAMEWORK.delivery_ccinfo_tab_type;
64 l_cc_target_trip_dlvy_lines WSH_FTE_CONSTRAINT_FRAMEWORK.detail_ccinfo_tab_type;
65 l_cc_target_trip_incl_stops WSH_FTE_CONSTRAINT_FRAMEWORK.stop_ccinfo_tab_type;
66
67 l_cc_del_detail_info WSH_FTE_CONSTRAINT_FRAMEWORK.detail_ccinfo_tab_type;
68 l_cc_target_delivery WSH_FTE_CONSTRAINT_FRAMEWORK.delivery_ccinfo_rec_type;
69 l_cc_target_container WSH_FTE_CONSTRAINT_FRAMEWORK.detail_ccinfo_rec_type;
70
71 l_cc_trip_info WSH_FTE_CONSTRAINT_FRAMEWORK.trip_ccinfo_tab_type;
72 l_cc_trip_assigned_dels WSH_FTE_CONSTRAINT_FRAMEWORK.delivery_ccinfo_tab_type;
73 l_cc_trip_dlvy_lines WSH_FTE_CONSTRAINT_FRAMEWORK.detail_ccinfo_tab_type;
74 l_cc_trip_incl_stops WSH_FTE_CONSTRAINT_FRAMEWORK.stop_ccinfo_tab_type;
75
76 l_cc_stop_info WSH_FTE_CONSTRAINT_FRAMEWORK.stop_ccinfo_tab_type;
77 l_cc_parent_trip_info WSH_FTE_CONSTRAINT_FRAMEWORK.trip_ccinfo_tab_type;
78 l_cc_parent_trip_assign_dels WSH_FTE_CONSTRAINT_FRAMEWORK.delivery_ccinfo_tab_type;
79 l_cc_parent_trip_dlvy_lines WSH_FTE_CONSTRAINT_FRAMEWORK.detail_ccinfo_tab_type;
80
81 l_cc_exception_list WSH_UTIL_CORE.Column_Tab_Type;
82 l_id_tab WSH_UTIL_CORE.id_tab_type;
83 l_in_ids WSH_UTIL_CORE.id_tab_type;
84
85 l_index NUMBER := 1;
86 l_index2 NUMBER := 1;
87 j NUMBER;
88 l_initial_pickup_location_id NUMBER;
89 l_ship_from_location_id NUMBER;
90 l_max_line_group_id NUMBER := 0;
91 l_shipping_control wsh_delivery_details.shipping_control%TYPE;
92 l_cc_target_tripstops WSH_FTE_CONSTRAINT_FRAMEWORK.target_tripstop_cc_rec_type;
93 l_delivery_type VARCHAR2(30);
94
95 CURSOR c_shipping_control(l_detail_id NUMBER ) IS
96 SELECT NVL(shipping_control,'BUYER'), ship_from_location_id,
97 line_direction, released_status
98 FROM wsh_delivery_details
99 WHERE delivery_detail_Id = l_detail_id;
100
101 CURSOR c_delivery_shipping_control (l_delivery_id NUMBER) IS
102 SELECT NVL(shipping_control, 'BUYER'),
103 INITIAL_PICKUP_LOCATION_ID,
104 status_code,
105 shipment_direction
106 FROM wsh_new_deliveries
107 WHERE delivery_Id = l_delivery_id;
108
109 cursor c_gettrip(l_deliveryid IN NUMBER) is
110 select wts.TRIP_ID
111 from wsh_trip_stops wts, wsh_delivery_legs wdl
112 where wdl.delivery_id =l_deliveryid AND
113 wdl.pick_up_stop_id = wts.stop_id;
114
115 cursor c_getdeliverytype(l_deliveryid IN NUMBER) is
116 select delivery_type
117 from wsh_new_deliveries
118 WHERE delivery_Id = l_deliveryid;
119
120 cursor c_getdeliveryid(l_deldetailid IN NUMBER) is
121 select wda.delivery_id
122 from wsh_delivery_assignments_v wda
123 where wda.delivery_detail_id=l_deldetailid;
124
125
126 cursor c_get_const_count IS
127 select compatibility_id
128 from wsh_fte_comp_constraints
129 where EFFECTIVE_DATE_FROM <= sysdate
130 and nvl(EFFECTIVE_DATE_TO,sysdate) >= sysdate
131 and rownum = 1;
132
133
134 l_compatibility_id NUMBER:= -999;
135 l_return_status VARCHAR2(1);
136
137 e_handle_supplier_managed EXCEPTION;
138
139 l_msg_details VARCHAR2(4000);
140 l_status_code VARCHAR2(2);
141 l_shipment_direction VARCHAR2(30);
142 l_line_direction VARCHAR2(30);
143 l_released_status VARCHAR2(1);
144
145 BEGIN
146
147 IF NOT FND_API.Compatible_API_Call
148 ( l_api_version_number,
149 p_api_version_number,
150 l_api_name,
151 G_PKG_NAME
152 )
153 THEN
154 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
155 END IF;
156
157 --
158 IF FND_API.to_Boolean(p_init_msg_list) THEN
159 FND_MSG_PUB.initialize;
160 END IF;
161
162 --
163 -- Debug Statements
164 --
165 --
166 l_debug_on := WSH_DEBUG_INTERFACE.g_debug;
167 --
168 IF l_debug_on IS NULL
169 THEN
170 l_debug_on := WSH_DEBUG_SV.is_debug_enabled;
171 END IF;
172 --
173
174 IF l_debug_on THEN
175 wsh_debug_sv.push (l_module_name);
176 wsh_debug_sv.log (l_module_name,'p_action_code',p_action_code);
177 wsh_debug_sv.log (l_module_name,'p_entity_type',p_entity_type);
178 wsh_debug_sv.log (l_module_name,'p_pickup_dep_date',p_pickup_dep_date);
179 wsh_debug_sv.log (l_module_name,'p_pickup_arr_date',p_pickup_arr_date);
180 wsh_debug_sv.log (l_module_name,'p_dropoff_arr_date',p_dropoff_arr_date);
181 wsh_debug_sv.log (l_module_name,'p_dropoff_dep_date',p_dropoff_dep_date);
182 END IF;
183
184 x_return_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
185
186 --#DUM_LOC(S)
187 IF (g_valid_const_cache.valid_const_present IS NULL OR g_valid_const_cache.cache_date <> SYSDATE) THEN
188
189 OPEN c_get_const_count;
190 FETCH c_get_const_count INTO l_compatibility_id;
191
192 IF (c_get_const_count%ROWCOUNT = 0)THEN
193 --Constraint not found
194 g_valid_const_cache.valid_const_present := FALSE;
195 g_valid_const_cache.cache_date := SYSDATE;
196
197 IF l_debug_on THEN
198 wsh_debug_sv.log(l_module_name,'Returning as no constraints are defined ',x_return_status,WSH_DEBUG_SV.C_PROC_LEVEL);
199 wsh_debug_sv.log(l_module_name,'Return Status After Calling validate_constraint_',x_return_status,WSH_DEBUG_SV.C_PROC_LEVEL);
200 WSH_DEBUG_SV.pop(l_module_name);
201 END IF;
202 --
203 CLOSE c_get_const_count;
204 RETURN;
205
206 END IF;
207
208 --Constraint Found;
209 g_valid_const_cache.valid_const_present := TRUE;
210 g_valid_const_cache.cache_date := SYSDATE;
211
212 IF l_debug_on THEN
213 wsh_debug_sv.log(l_module_name,'Atleast one effective constraint defined ');
214 END IF;
215
216 CLOSE c_get_const_count;
217
218 ELSIF (g_valid_const_cache.valid_const_present = FALSE ) THEN
219
220 IF l_debug_on THEN
221 wsh_debug_sv.log(l_module_name,'Returning as no constraints are defined ',x_return_status,WSH_DEBUG_SV.C_PROC_LEVEL);
222 wsh_debug_sv.log(l_module_name,'Return Status After Calling validate_constraint_',x_return_status,WSH_DEBUG_SV.C_PROC_LEVEL);
223 WSH_DEBUG_SV.pop(l_module_name);
224 END IF;
225
226 RETURN;
227
228 ELSE
229
230 IF l_debug_on THEN
231 wsh_debug_sv.log(l_module_name,'Atleast one effective constraint defined ');
232 END IF;
233
234 END IF;
235 --DUM_LOC(E)
236
237 IF p_entity_type='D' THEN
238
239 IF p_action_code = 'UPDATE' THEN
240 l_cc_action_code:='UPD';
241 ELSIF p_action_code = 'CREATE' THEN
242 l_cc_action_code:='CRD';
243 ELSIF p_action_code = 'ASSIGN-TRIP' THEN
244 l_cc_action_code:='ADT';
245 --populate target trip with id
246 l_cc_target_trip.TRIP_ID:=p_target_id;
247 ELSIF p_action_code IN ('AUTO-PACK','AUTO-PACK-MASTER') THEN
248 l_cc_action_code:='PKG';
249 --
250 -- rlanka-Pack J : Treat trip-consolidation just like autocreate-trip
251 -- for entity = 'D'
252 ELSIF p_action_code IN ('AUTOCREATE-TRIP', 'TRIP-CONSOLIDATION') THEN
253 l_cc_action_code:='ACT';
254 ELSIF p_action_code = 'SELECT-CARRIER' THEN
255 l_cc_action_code:='ACS';
256 END IF;
257
258
259 IF l_debug_on THEN
260 wsh_debug_sv.log (l_module_name,'l_cc_action_code',l_cc_action_code);
261 END IF;
262
263 IF l_cc_action_code is not null THEN --{
264 --initialize the delivery record and the lines assigned to it
265
266 IF p_del_attr_tab.COUNT>0 THEN --{
267 FOR i IN p_del_attr_tab.FIRST..p_del_attr_tab.LAST LOOP --{
268 /* only pass the lines that are not managed by supplier
269 to the constraint engine
270 */
271 IF (NVL(p_del_attr_tab(i).shipping_control,'BUYER') = 'SUPPLIER')
272 OR (p_del_attr_tab(i).INITIAL_PICKUP_LOCATION_ID
273 = wsh_util_core.C_NULL_SF_LOCN_ID)
274 OR(( p_action_code = 'ASSIGN-TRIP' )
275 AND ( p_del_attr_tab(i).shipment_direction NOT in ('O','IO'))
276 AND (p_del_attr_tab(i).status_code IN ('IT','CL')))
277 THEN --{
278 l_id_tab(l_index) := p_del_attr_tab(i).delivery_id;
279 l_index := l_index + 1;
280 ELSE --}{
281 --for create stop id is null, so just pass in dummy ids
282 IF l_cc_action_code ='CRD' AND p_del_attr_tab(i).DELIVERY_ID IS NULL THEN
283 l_cc_delivery_info(l_index2).DELIVERY_ID := i;
284 ELSE
285 l_cc_delivery_info(l_index2).DELIVERY_ID := p_del_attr_tab(i).DELIVERY_ID;
286 END IF;
287
288 IF l_cc_action_code='CRD' THEN
289 l_cc_delivery_info(l_index2).exists_in_database :='N';
290 ELSE
291 l_cc_delivery_info(l_index2).exists_in_database :='Y';
292 END IF;
293
294 l_cc_delivery_info(l_index2).NAME := p_del_attr_tab(i).NAME;
295 l_cc_delivery_info(l_index2).PLANNED_FLAG := p_del_attr_tab(i).PLANNED_FLAG;
296 l_cc_delivery_info(l_index2).STATUS_CODE := p_del_attr_tab(i).STATUS_CODE;
297 l_cc_delivery_info(l_index2).INITIAL_PICKUP_DATE := p_del_attr_tab(i).INITIAL_PICKUP_DATE;
298 l_cc_delivery_info(l_index2).INITIAL_PICKUP_LOCATION_ID := p_del_attr_tab(i).INITIAL_PICKUP_LOCATION_ID;
299 l_cc_delivery_info(l_index2).ULTIMATE_DROPOFF_LOCATION_ID := p_del_attr_tab(i).ULTIMATE_DROPOFF_LOCATION_ID;
300 l_cc_delivery_info(l_index2).ULTIMATE_DROPOFF_DATE := p_del_attr_tab(i).ULTIMATE_DROPOFF_DATE;
301 l_cc_delivery_info(l_index2).CUSTOMER_ID := p_del_attr_tab(i).CUSTOMER_ID;
302 l_cc_delivery_info(l_index2).INTMED_SHIP_TO_LOCATION_ID := p_del_attr_tab(i).INTMED_SHIP_TO_LOCATION_ID;
303 l_cc_delivery_info(l_index2).SHIP_METHOD_CODE := p_del_attr_tab(i).SHIP_METHOD_CODE;
304 l_cc_delivery_info(l_index2).CARRIER_ID := p_del_attr_tab(i).CARRIER_ID;
305 l_cc_delivery_info(l_index2).ORGANIZATION_ID := p_del_attr_tab(i).ORGANIZATION_ID;
306 l_cc_delivery_info(l_index2).SERVICE_LEVEL := p_del_attr_tab(i).SERVICE_LEVEL;
307 l_cc_delivery_info(l_index2).MODE_OF_TRANSPORT := p_del_attr_tab(i).MODE_OF_TRANSPORT;
308 l_cc_delivery_info(l_index2).party_id := p_del_attr_tab(i).party_id;
309 l_cc_delivery_info(l_index2).shipment_direction := p_del_attr_tab(i).shipment_direction;
310 l_cc_delivery_info(l_index2).shipping_control := p_del_attr_tab(i).shipping_control;
311 --alksharm
312 OPEN c_getdeliverytype(p_del_attr_tab(i).DELIVERY_ID);
313 FETCH c_getdeliverytype INTO l_delivery_type;
314 IF c_getdeliverytype%NOTFOUND THEN
315 l_delivery_type := NULL;
316 END IF;
317 CLOSE c_getdeliverytype;
318 l_cc_delivery_info(l_index2).delivery_type := l_delivery_type;
319
320
321 -- populate trip id for delivery (if any)
322 -- bsadri why do we need a loop here where we store all
323 -- the values in l_cc_delivery_info(l_index2).TRIP_ID ?
324 FOR c_tripcur in c_gettrip(p_del_attr_tab(i).DELIVERY_ID) LOOP
325 l_cc_delivery_info(l_index2).TRIP_ID:=c_tripcur.TRIP_ID;
326 END LOOP;
327 l_index2 := l_index2 + 1;
328 END IF;--}
329 END LOOP; --}
330 IF l_cc_delivery_info.COUNT = 0 THEN
331 x_return_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
332 RAISE e_handle_supplier_managed;
333 END IF;
334 --
335 -- rlanka-Pack J : Treat trip-consolidation just like autocreate-trip
336 -- for entity = 'D'
337 ELSIF p_action_code IN ('AUTOCREATE-TRIP', 'TRIP-CONSOLIDATION') AND p_in_ids.COUNT > 0 THEN--}{
338
339 /* only pass the lines that are not managed by supplier
340 to the constraint engine
341 */
342
343 j := p_in_ids.first;
344
345 WHILE j IS NOT NULL LOOP
346 OPEN c_delivery_shipping_control(p_in_ids(j));
347 FETCH c_delivery_shipping_control into l_shipping_control,
348 l_initial_pickup_location_id, l_status_code,
349 l_shipment_direction;
350
351 CLOSE c_delivery_shipping_control;
352
353 IF (l_shipping_control = 'SUPPLIER' )
354 OR
355 (l_initial_pickup_location_id = wsh_util_core.C_NULL_SF_LOCN_ID )
356 OR (( l_shipment_direction NOT in ('O','IO'))
357 AND (l_status_code IN ('IT','CL')))
358
359 THEN
360 l_id_tab(l_index) := p_in_ids(j);
361 l_index := l_index + 1;
362 ELSE
363 l_in_ids(l_index2) := p_in_ids(j);
364 l_index2 := l_index2 + 1;
365 END IF;
366 j := p_in_ids.NEXT(j);
367 END LOOP;
368 IF l_in_ids.COUNT = 0 THEN
369 x_return_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
370 RAISE e_handle_supplier_managed;
371 END IF;
372 ELSE --}{
373 l_in_ids := p_in_ids;
374 END IF;--}
375
376 IF l_debug_on THEN
377 wsh_debug_sv.logmsg(l_module_name,'before calling validate_constraint_dlvy',WSH_DEBUG_SV.C_PROC_LEVEL);
378 END IF;
379
380 l_cc_target_tripstops.pickup_stop_id := p_pickup_stop_id;
381 l_cc_target_tripstops.pickup_stop_seq := p_pickup_stop_seq;
382 l_cc_target_tripstops.dropoff_stop_id := p_dropoff_stop_id;
383 l_cc_target_tripstops.dropoff_stop_seq := p_dropoff_stop_seq;
384 l_cc_target_tripstops.pickup_location_id := p_pickup_loc_id;
385 l_cc_target_tripstops.dropoff_location_id := p_dropoff_loc_id;
386 l_cc_target_tripstops.PICKUP_STOP_PA_DATE := p_pickup_arr_date;
387 l_cc_target_tripstops.PICKUP_STOP_PD_DATE := p_pickup_dep_date;
388 l_cc_target_tripstops.DROPOFF_STOP_PA_DATE := p_dropoff_arr_date;
389 l_cc_target_tripstops.DROPOFF_STOP_PD_DATE := p_dropoff_dep_date;
390
391 WSH_FTE_CONSTRAINT_FRAMEWORK.validate_constraint_dlvy(
392 p_init_msg_list => p_init_msg_list,
393 p_action_code => l_cc_action_code,
394 p_exception_list => l_cc_exception_list,
395 p_delivery_info => l_cc_delivery_info,
396 p_in_ids => l_in_ids,
397 p_dlvy_assigned_lines => l_cc_dlvy_assigned_lines,
398 p_target_trip => l_cc_target_trip,
399 p_target_tripstops => l_cc_target_tripstops,
400 p_target_trip_assign_dels => l_cc_target_trip_assign_dels,
401 p_target_trip_dlvy_lines => l_cc_target_trip_dlvy_lines,
402 p_target_trip_incl_stops => l_cc_target_trip_incl_stops,
403 x_validate_result => x_validate_result,
404 x_failed_lines => x_failed_lines,
405 x_line_groups => x_line_groups,
406 x_group_info => x_group_info,
407 x_msg_count => x_msg_count,
408 x_msg_data => x_msg_data,
409 x_return_status => x_return_status);
410
411
412 IF l_debug_on THEN
413 wsh_debug_sv.logmsg(l_module_name,'after calling validate_constraint_dlvy',WSH_DEBUG_SV.C_PROC_LEVEL);
414 END IF;
415 END IF;-- l_cc_action_code is not null}
416 --check for delivery is over
417 ELSIF p_entity_type='L' THEN
418 --following check for update deldetail is not needed as of now
419 IF p_action_code='UPDATE' THEN
420 l_cc_action_code:='UPD';
421 ELSIF p_action_code='AUTOCREATE-DEL' THEN
422 l_cc_action_code:='ACD';
423 ELSIF p_action_code IN ('AUTO-PACK','AUTO-PACK-MASTER') THEN
424 l_cc_action_code:='PKG';
425 ELSIF p_action_code = 'ASSIGN' THEN
426 l_cc_action_code:='ADD';
427 --populate target container/delivery with id
428 l_cc_target_delivery.DELIVERY_ID :=p_target_id;
429 ELSIF p_action_code = 'PACK' THEN
430 l_cc_action_code:='PKG';
431 --populate target container/delivery with id
432 l_cc_target_container.DELIVERY_DETAIL_ID:=p_target_id;
433 ELSIF p_action_code IN ('AUTO-PACK','AUTO-PACK-MASTER') THEN
434 l_cc_action_code:='PKG';
435 ELSIF p_action_code = 'AUTOCREATE-TRIP' THEN
436 l_cc_action_code:='ACT';
437 ELSIF p_action_code = 'AUTOCREATE-DEL' THEN
438 l_cc_action_code:='ACD';
439 END IF;
440
441 IF l_debug_on THEN
442 wsh_debug_sv.log (l_module_name,'l_cc_action_code',l_cc_action_code);
443 END IF;
444
445 IF l_cc_action_code is not null THEN
446 --initialize the delivery detail record
447
448 IF p_det_attr_tab.COUNT>0 THEN --{
449 FOR i IN p_det_attr_tab.FIRST..p_det_attr_tab.LAST LOOP --{
450 IF ((NVL(p_det_attr_tab(i).shipping_control,'BUYER') = 'SUPPLIER')
451 OR (p_det_attr_tab(i).ship_from_location_id =
452 wsh_util_core.C_NULL_SF_LOCN_ID)
453 OR ((p_det_attr_tab(i).line_direction NOT IN ('O','IO'))
454 AND (p_det_attr_tab(i).released_status IN ('C','L','P'))
455 AND (p_action_code = 'AUTOCREATE-DEL')))
456 THEN --{
457 l_id_tab(l_index) := p_det_attr_tab(i).delivery_detail_id;
458 l_index := l_index + 1;
459 ELSE --}{
460 l_cc_del_detail_info(l_index2).DELIVERY_DETAIL_ID := p_det_attr_tab(i).DELIVERY_DETAIL_ID;
461 l_cc_del_detail_info(l_index2).exists_in_database := 'Y';
462 l_cc_del_detail_info(l_index2).CUSTOMER_ID := p_det_attr_tab(i).CUSTOMER_ID;
463 l_cc_del_detail_info(l_index2).INVENTORY_ITEM_ID := p_det_attr_tab(i).INVENTORY_ITEM_ID;
464 l_cc_del_detail_info(l_index2).SHIP_FROM_LOCATION_ID := p_det_attr_tab(i).SHIP_FROM_LOCATION_ID;
465 l_cc_del_detail_info(l_index2).ORGANIZATION_ID := p_det_attr_tab(i).ORGANIZATION_ID;
466 l_cc_del_detail_info(l_index2).SHIP_TO_LOCATION_ID := p_det_attr_tab(i).SHIP_TO_LOCATION_ID;
467 l_cc_del_detail_info(l_index2).INTMED_SHIP_TO_LOCATION_ID := p_det_attr_tab(i).INTMED_SHIP_TO_LOCATION_ID;
468 l_cc_del_detail_info(l_index2).RELEASED_STATUS := p_det_attr_tab(i).RELEASED_STATUS;
469 l_cc_del_detail_info(l_index2).CONTAINER_FLAG := p_det_attr_tab(i).CONTAINER_FLAG;
470 l_cc_del_detail_info(l_index2).DATE_REQUESTED := p_det_attr_tab(i).DATE_REQUESTED;
471 l_cc_del_detail_info(l_index2).DATE_SCHEDULED := p_det_attr_tab(i).DATE_SCHEDULED;
472 l_cc_del_detail_info(l_index2).SHIP_METHOD_CODE := p_det_attr_tab(i).SHIP_METHOD_CODE;
473 l_cc_del_detail_info(l_index2).CARRIER_ID := p_det_attr_tab(i).CARRIER_ID;
474 l_cc_del_detail_info(l_index2).shipping_control := p_det_attr_tab(i).shipping_control;
475 l_cc_del_detail_info(l_index2).party_id := p_det_attr_tab(i).party_id;
476 l_cc_del_detail_info(l_index2).line_direction := p_det_attr_tab(i).line_direction;
477
478 --bsadri why do we need a loop here where we store all
479 -- the values in l_cc_del_detail_info(l_index2).DELIVERY_ID ?
480 --populate delivery id for detail (if any)
481
482 FOR cur_getdeliveryid in c_getdeliveryid(p_det_attr_tab(i).DELIVERY_DETAIL_ID) LOOP
483 l_cc_del_detail_info(l_index2).DELIVERY_ID := cur_getdeliveryid.DELIVERY_ID;
484 END LOOP;
485 l_index2 := l_index2 + 1;
486
487 END IF; --}
488
489 END LOOP; --}
490 IF l_cc_del_detail_info.COUNT = 0 THEN
491 x_return_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
492 RAISE e_handle_supplier_managed;
493 END IF;
494 ELSIF p_action_code = 'AUTOCREATE-DEL' AND p_in_ids.COUNT > 0 THEN--}{
495
496 /* only pass the lines that are not managed by supplier
497 to the constraint engine
498 */
499
500 j := p_in_ids.first;
501 WHILE j IS NOT NULL LOOP
502 OPEN c_shipping_control(p_in_ids(j));
503 FETCH c_shipping_control into l_shipping_control,
504 l_ship_from_location_id,
505 l_line_direction,
506 l_released_status;
507 CLOSE c_shipping_control;
508 IF ((l_shipping_control = 'SUPPLIER')
509 OR (l_ship_from_location_id=wsh_util_core.C_NULL_SF_LOCN_ID)
510 OR ((l_line_direction NOT IN ('O','IO'))
511 AND (l_released_status IN ('C','L','P'))))
512 THEN
513 l_id_tab(l_index) := p_in_ids(j);
514 l_index := l_index + 1;
515 ELSE
516 l_in_ids(l_index2) := p_in_ids(j);
517 l_index2 := l_index2 + 1;
518 END IF;
519 j := p_in_ids.NEXT(j);
520 END LOOP;
521
522 IF l_in_ids.COUNT = 0 THEN
523 x_return_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
524 RAISE e_handle_supplier_managed;
525 END IF;
526 ELSE --}{
527 l_in_ids := p_in_ids;
528 END IF; --}
529
530 IF l_debug_on THEN
531 wsh_debug_sv.logmsg(l_module_name,'before calling validate_constraint_dlvb',WSH_DEBUG_SV.C_PROC_LEVEL);
532 END IF;
533
534 WSH_FTE_CONSTRAINT_FRAMEWORK.validate_constraint_dlvb(
535 p_init_msg_list => p_init_msg_list,
536 p_action_code => l_cc_action_code,
537 p_exception_list => l_cc_exception_list,
538 p_del_detail_info => l_cc_del_detail_info,
539 p_in_ids => l_in_ids,
540 p_target_delivery => l_cc_target_delivery,
541 p_target_container => l_cc_target_container,
542 p_dlvy_assigned_lines => l_cc_dlvy_assigned_lines,
543 x_validate_result => x_validate_result,
544 x_failed_lines => x_failed_lines,
545 x_line_groups => x_line_groups,
546 x_group_info => x_group_info,
547 x_msg_count => x_msg_count,
548 x_msg_data => x_msg_data,
549 x_return_status => x_return_status);
550
551 IF l_debug_on THEN
552 wsh_debug_sv.logmsg(l_module_name,'after calling validate_constraint_dlvb',WSH_DEBUG_SV.C_PROC_LEVEL);
553 END IF;
554
555 END IF;-- l_cc_action_code is not null
556
557 --check for line is over
558 ELSIF p_entity_type='T' THEN
559
560 IF p_action_code ='UPDATE' THEN
561 l_cc_action_code:='UPT';
562 END IF;
563
564 IF l_debug_on THEN
565 wsh_debug_sv.log (l_module_name,'l_cc_action_code',l_cc_action_code);
566 END IF;
567
568 IF l_cc_action_code is not null THEN
569 --initialize the delivery record and the lines assigned to it
570 l_cc_count_stop:=1;
571 l_cc_count_del:=1;
572 l_cc_count_dd:=1;
573
574 IF p_trip_attr_tab.COUNT>0 THEN
575
576 FOR i IN p_trip_attr_tab.FIRST..p_trip_attr_tab.LAST LOOP
577 l_cc_trip_info(i).TRIP_ID := p_trip_attr_tab(i).TRIP_ID;
578 l_cc_trip_info(i).exists_in_database :='Y';
579 l_cc_trip_info(i).NAME := p_trip_attr_tab(i).NAME;
580 l_cc_trip_info(i).PLANNED_FLAG := p_trip_attr_tab(i).PLANNED_FLAG;
581 l_cc_trip_info(i).STATUS_CODE := p_trip_attr_tab(i).STATUS_CODE;
582 l_cc_trip_info(i).VEHICLE_ITEM_ID := p_trip_attr_tab(i).VEHICLE_ITEM_ID;
583 l_cc_trip_info(i).VEHICLE_NUMBER := p_trip_attr_tab(i).VEHICLE_NUMBER;
584 l_cc_trip_info(i).CARRIER_ID := p_trip_attr_tab(i).CARRIER_ID;
585 l_cc_trip_info(i).SHIP_METHOD_CODE := p_trip_attr_tab(i).SHIP_METHOD_CODE;
586 l_cc_trip_info(i).VEHICLE_ORGANIZATION_ID := p_trip_attr_tab(i).VEHICLE_ORGANIZATION_ID;
587 l_cc_trip_info(i).VEHICLE_NUM_PREFIX := p_trip_attr_tab(i).VEHICLE_NUM_PREFIX;
588 l_cc_trip_info(i).SERVICE_LEVEL := p_trip_attr_tab(i).SERVICE_LEVEL;
589 l_cc_trip_info(i).MODE_OF_TRANSPORT := p_trip_attr_tab(i).MODE_OF_TRANSPORT;
590 END LOOP;
591 END IF;
592
593 IF l_debug_on THEN
594 wsh_debug_sv.logmsg(l_module_name,'before calling validate_constraint_trip',WSH_DEBUG_SV.C_PROC_LEVEL);
595 END IF;
596
597 WSH_FTE_CONSTRAINT_FRAMEWORK.validate_constraint_trip(
598 p_init_msg_list => p_init_msg_list,
599 p_action_code => l_cc_action_code,
600 p_exception_list => l_cc_exception_list,
601 p_trip_info => l_cc_trip_info,
602 p_trip_assigned_dels => l_cc_trip_assigned_dels,
603 p_trip_dlvy_lines => l_cc_trip_dlvy_lines,
604 p_trip_incl_stops => l_cc_trip_incl_stops,
605 x_validate_result => x_validate_result,
606 x_fail_trips => x_fail_ids,
607 x_msg_count => x_msg_count,
608 x_msg_data => x_msg_data,
609 x_return_status => x_return_status);
610
611 IF l_debug_on THEN
612 wsh_debug_sv.logmsg(l_module_name,'after calling validate_constraint_trip',WSH_DEBUG_SV.C_PROC_LEVEL);
613 END IF;
614 END IF;-- l_cc_action_code is not null
615
616 --check for trip is over
617 ELSIF p_entity_type='S' THEN
618
619 IF p_action_code ='UPDATE' THEN
620 l_cc_action_code:='UPS';
621 ELSIF p_action_code ='DELETE' THEN
622 l_cc_action_code:='DTS';
623 ELSIF p_action_code ='CREATE' THEN
624 l_cc_action_code:='CTS';
625 END IF;
626
627 IF l_debug_on THEN
628 wsh_debug_sv.log (l_module_name,'l_cc_action_code',l_cc_action_code);
629 END IF;
630
631 IF l_cc_action_code is not null THEN
632 --initialize the delivery record and the lines assigned to it
633 l_cc_count_trip:=1;
634 l_cc_count_del:=1;
635 l_cc_count_dd:=1;
636
637 IF p_stop_attr_tab.COUNT>0 THEN
638 FOR i IN p_stop_attr_tab.FIRST..p_stop_attr_tab.LAST LOOP
639 --for create stop id is null, so just pass in dummy ids
640
641 IF l_cc_action_code ='CTS' AND p_stop_attr_tab(i).STOP_ID IS NULL THEN
642 l_cc_stop_info(i).STOP_ID := i;
643 ELSE
644 l_cc_stop_info(i).STOP_ID := p_stop_attr_tab(i).STOP_ID;
645 END IF;
646
647 --if create, set flag to N
648 IF l_cc_action_code ='CTS' THEN
649 l_cc_stop_info(i).exists_in_database :='N';
650 ELSE
651 l_cc_stop_info(i).exists_in_database :='Y';
652 END IF;
653
654 l_cc_stop_info(i).TRIP_ID := p_stop_attr_tab(i).TRIP_ID;
655 l_cc_stop_info(i).STOP_LOCATION_ID := p_stop_attr_tab(i).STOP_LOCATION_ID;
656 l_cc_stop_info(i).STATUS_CODE := p_stop_attr_tab(i).STATUS_CODE;
657 l_cc_stop_info(i).STOP_SEQUENCE_NUMBER := p_stop_attr_tab(i).STOP_SEQUENCE_NUMBER;
658 l_cc_stop_info(i).PLANNED_ARRIVAL_DATE := p_stop_attr_tab(i).PLANNED_ARRIVAL_DATE;
659 l_cc_stop_info(i).PLANNED_DEPARTURE_DATE := p_stop_attr_tab(i).PLANNED_DEPARTURE_DATE;
660 l_cc_stop_info(i).ACTUAL_ARRIVAL_DATE := p_stop_attr_tab(i).ACTUAL_ARRIVAL_DATE;
661 l_cc_stop_info(i).ACTUAL_DEPARTURE_DATE := p_stop_attr_tab(i).ACTUAL_DEPARTURE_DATE;
662 -- DUM_LOC(Q)
663 -- The Physical Location Id/Stop Id will be passed in using the Input record.
664 l_cc_stop_info(i).PHYSICAL_LOCATION_ID := p_stop_attr_tab(i).PHYSICAL_LOCATION_ID;
665 l_cc_stop_info(i).PHYSICAL_STOP_ID := p_stop_attr_tab(i).PHYSICAL_STOP_ID;
666 -- DUM_LOC(E)
667 END LOOP;
668 ELSIF p_in_ids.COUNT>0 THEN
669 IF l_debug_on THEN
670 wsh_debug_sv.log(l_module_name,'p_in_ids.COUNT passed for stops',p_in_ids.COUNT);
671 END IF;
672 FOR i IN p_in_ids.FIRST..p_in_ids.LAST LOOP
673 l_cc_stop_info(i).STOP_ID := p_in_ids(i);
674 END LOOP;
675 END IF;
676 IF l_debug_on THEN
677 wsh_debug_sv.logmsg(l_module_name,'before calling validate_constraint_stop',WSH_DEBUG_SV.C_PROC_LEVEL);
678 END IF;
679
680 WSH_FTE_CONSTRAINT_FRAMEWORK.validate_constraint_stop(
681 p_init_msg_list => p_init_msg_list,
682 p_action_code => l_cc_action_code,
683 p_exception_list => l_cc_exception_list,
684 p_stop_info => l_cc_stop_info,
685 p_parent_trip_info => l_cc_parent_trip_info,
686 x_validate_result => x_validate_result,
687 x_fail_stops => x_fail_ids,
688 x_msg_count => x_msg_count,
689 x_msg_data => x_msg_data,
690 x_return_status => x_return_status);
691
692 IF l_debug_on THEN
693 wsh_debug_sv.logmsg(l_module_name,'after calling validate_constraint_stop',WSH_DEBUG_SV.C_PROC_LEVEL);
694
695 END IF;
696 END IF;-- l_cc_action_code is not null
697 --check for stop is over
698 END IF;-- entity_type
699
700 IF l_debug_on THEN
701 wsh_debug_sv.log(l_module_name,'l_id_tab.count',l_id_tab.count);
702 END IF;
703 IF l_id_tab.COUNT > 0 THEN
704 RAISE e_handle_supplier_managed;
705 END IF;
706
707 IF l_debug_on THEN
708 wsh_debug_sv.log(l_module_name,'Return Status After Calling validate_constraint_',x_return_status,WSH_DEBUG_SV.C_PROC_LEVEL);
709 WSH_DEBUG_SV.pop(l_module_name);
710 END IF;
711 --
712 --
713
714 --Compatiblity Changes
715
716 EXCEPTION
717 WHEN e_handle_supplier_managed THEN
718 --
719 IF (p_entity_type IN ('L','D')) AND (l_id_tab.COUNT > 0 )
720 AND ( p_action_code NOT IN ('ASSIGN','ASSIGN-TRIP'))
721 THEN --{
722 /* If some of deliveres or delivery details where managed by
723 supplier, and if some of the lines that were not managed by
724 supplier have failed the constraint validation, then return
725 the supplier managed lines as success group
726 */
727
728 --get the highest group_line_id and index
729 l_index := x_line_groups.LAST;
730 IF l_index IS NOT NULL THEN
731 l_max_line_group_id := NVL(x_line_groups(l_index).line_group_id,0)
732 + 1;
733 ELSE
734 l_max_line_group_id := 1;
735 l_index := 0;
736 END IF;
737
738 --enter the supplier managed lines as success records
739 j := l_id_tab.first;
740 WHILE j IS NOT NULL LOOP
741 x_line_groups(l_index + j).line_group_id := l_max_line_group_id;
742 x_line_groups(l_index + j).entity_line_id := l_id_tab(j);
743 j := l_id_tab.NEXT(j);
744 END LOOP;
745
746 END IF; --}
747 IF l_debug_on THEN
748 WSH_DEBUG_SV.log(l_module_name,'x_return_status', x_return_status);
749 WSH_DEBUG_SV.pop(l_module_name);
750 END IF;
751
752 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
753 x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR ;
754 --
755 IF l_debug_on THEN
756 WSH_DEBUG_SV.logmsg(l_module_name,'Unexpected error has occured. Oracle error message is '|| SQLERRM,WSH_DEBUG_SV.C_UNEXPEC_ERR_LEVEL);
757 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:FND_API.G_EXC_UNEXPECTED_ERROR');
758 END IF;
759 --
760 WHEN WSH_UTIL_CORE.G_EXC_WARNING THEN
761 x_return_status := WSH_UTIL_CORE.G_RET_STS_WARNING;
762 --
763 IF l_debug_on THEN
764 WSH_DEBUG_SV.logmsg(l_module_name,'WSH_UTIL_CORE.G_EXC_WARNING exception has occured ',WSH_DEBUG_SV.C_EXCEP_LEVEL);
765 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:WSH_UTIL_CORE.G_EXC_WARNING');
766 END IF;
767
768 WHEN OTHERS THEN
769 x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR ;
770 IF c_get_const_count%ISOPEN THEN
771 CLOSE c_get_const_count;
772 END IF;
773 wsh_util_core.default_handler('validate_constraint_main');
774 --
775 IF l_debug_on THEN
776 WSH_DEBUG_SV.logmsg(l_module_name,'Unexpected error has occured. Oracle error message is '|| SQLERRM,WSH_DEBUG_SV.C_UNEXPEC_ERR_LEVEL);
777 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:FND_API.G_EXC_UNEXPECTED_ERROR');
778 END IF;
779 --
780 END validate_constraint_main;
781
782 -- To be called from UI directly for
783 -- DCE, DST
784 -- For DST (Search trip for a delivery leg) pass the target trip id
785 -- For DCE, pass dleg details
786
787 PROCEDURE validate_constraint_dleg(
788 p_init_msg_list IN VARCHAR2 DEFAULT fnd_api.g_false,
789 p_action_code IN VARCHAR2,
790 p_delivery_leg_id IN NUMBER DEFAULT NULL,
791 p_delivery_id IN NUMBER,
792 p_sequence_num IN NUMBER DEFAULT NULL,
793 p_location1_id IN NUMBER DEFAULT NULL,
794 p_location2_id IN NUMBER DEFAULT NULL,
795 p_stop1_id IN NUMBER DEFAULT NULL,
796 p_stop2_id IN NUMBER DEFAULT NULL,
797 p_date_1 IN DATE DEFAULT NULL,
798 p_date_2 IN DATE DEFAULT NULL,
799 p_target_trip_id IN NUMBER DEFAULT NULL, -- For DST
800 p_carrier_id IN NUMBER DEFAULT NULL, -- Following 3 for DCE
801 p_mode_code IN VARCHAR2 DEFAULT NULL,
802 p_service_level IN VARCHAR2 DEFAULT NULL,
803 x_validate_result OUT NOCOPY VARCHAR2, -- Constraint Validation result : S / F
804 x_msg_count OUT NOCOPY NUMBER, -- Standard FND functionality
805 x_msg_data OUT NOCOPY VARCHAR2, -- Will return message text only if number of messages = 1
806 x_return_status OUT NOCOPY VARCHAR2)
807 IS
808
809 -- TODO Replace with global cursor
810 CURSOR c_get_trip_detail(c_trip_id IN NUMBER) IS
811 SELECT TRIP_ID
812 , 'Y'
813 , NAME
814 , PLANNED_FLAG
815 , STATUS_CODE
816 , VEHICLE_ITEM_ID
817 , VEHICLE_NUMBER
818 , CARRIER_ID
819 , SHIP_METHOD_CODE
820 , VEHICLE_ORGANIZATION_ID
821 , VEHICLE_NUM_PREFIX
822 , SERVICE_LEVEL
823 , MODE_OF_TRANSPORT
824 FROM wsh_trips
825 WHERE trip_id = c_trip_id;
826
827 CURSOR c_get_parent_delivery_leg(c_del_id NUMBER, c_del_leg_id NUMBER) IS
828 SELECT PARENT_DELIVERY_LEG_ID
829 FROM wsh_delivery_legs
830 WHERE DELIVERY_LEG_ID = c_del_leg_id
831 AND DELIVERY_ID = c_del_id;
832
833 l_return_status VARCHAR2(1);
834 l_validate_result VARCHAR2(1) := 'S';
835 l_succ_trips WSH_UTIL_CORE.id_tab_type;
836 l_dummy_succ_lanes WSH_UTIL_CORE.id_tab_type;
837 l_dummy_exception_list WSH_UTIL_CORE.Column_Tab_Type;
838 l_delivery_leg_rec WSH_FTE_CONSTRAINT_FRAMEWORK.dleg_ccinfo_rec_type;
839 l_target_trip WSH_FTE_CONSTRAINT_FRAMEWORK.trip_ccinfo_rec_type;
840 l_target_trip_tab WSH_FTE_CONSTRAINT_FRAMEWORK.trip_ccinfo_tab_type;
841 l_dummy_target_lane WSH_FTE_CONSTRAINT_FRAMEWORK.lane_ccinfo_tab_type;
842 l_parent_delivery_leg_id NUMBER;
843
844 l_debug_on CONSTANT BOOLEAN := WSH_DEBUG_SV.is_debug_enabled;
845 l_module_name CONSTANT VARCHAR2(100) := 'wsh.plsql.' || G_PKG_NAME || '.' || 'validate_constraint_dlegui';
846
847 BEGIN
848
849 x_return_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
850
851 OPEN c_get_parent_delivery_leg(p_delivery_id, p_delivery_leg_id);
852 FETCH c_get_parent_delivery_leg INTO l_parent_delivery_leg_id;
853 IF c_get_parent_delivery_leg %NOTFOUND THEN
854 l_parent_delivery_leg_id := NULL;
855 END IF;
856 CLOSE c_get_parent_delivery_leg;
857
858 IF l_debug_on THEN
859 wsh_debug_sv.push(l_module_name);
860 WSH_DEBUG_SV.logmsg(l_module_name,'p_init_msg_list : '||p_init_msg_list);
861 WSH_DEBUG_SV.logmsg(l_module_name,'p_action_code : '||p_action_code);
862 WSH_DEBUG_SV.logmsg(l_module_name,'p_delivery_leg_id : '||p_delivery_leg_id);
863 WSH_DEBUG_SV.logmsg(l_module_name,'p_delivery_id : '||p_delivery_id);
864 WSH_DEBUG_SV.logmsg(l_module_name,'p_sequence_num : '||p_sequence_num);
865 WSH_DEBUG_SV.logmsg(l_module_name,'p_location1_id : '||p_location1_id);
866 WSH_DEBUG_SV.logmsg(l_module_name,'p_location2_id : '||p_location2_id);
867 WSH_DEBUG_SV.logmsg(l_module_name,'p_stop1_id : '||p_stop1_id);
868 WSH_DEBUG_SV.logmsg(l_module_name,'p_stop2_id : '||p_stop2_id);
869 WSH_DEBUG_SV.logmsg(l_module_name,'p_date_1 : '||p_date_1);
870 WSH_DEBUG_SV.logmsg(l_module_name,'p_date_2 : '||p_date_2);
871 WSH_DEBUG_SV.logmsg(l_module_name,'p_target_trip_id : '||p_target_trip_id);
872 WSH_DEBUG_SV.logmsg(l_module_name,'p_carrier_id : '||p_carrier_id);
873 WSH_DEBUG_SV.logmsg(l_module_name,'p_mode_code : '||p_mode_code);
874 WSH_DEBUG_SV.logmsg(l_module_name,'p_service_level : '||p_service_level);
875 WSH_DEBUG_SV.logmsg(l_module_name,'l_parent_delivery_leg_id : '||l_parent_delivery_leg_id);
876 END IF;
877
878 l_delivery_leg_rec.DELIVERY_LEG_ID := p_delivery_leg_id;
879 l_delivery_leg_rec.DELIVERY_ID := p_delivery_id;
880 l_delivery_leg_rec.SEQUENCE_NUMBER := p_sequence_num;
881 l_delivery_leg_rec.CARRIER_ID := p_carrier_id;
882 l_delivery_leg_rec.SERVICE_LEVEL := p_service_level;
883 l_delivery_leg_rec.MODE_OF_TRANSPORT := p_mode_code;
884 l_delivery_leg_rec.PICK_UP_STOP_ID := p_stop1_id;
885 l_delivery_leg_rec.DROP_OFF_STOP_ID := p_stop2_id;
886 l_delivery_leg_rec.PICKUPSTOP_LOCATION_ID := p_location1_id ;
887 l_delivery_leg_rec.DROPOFFSTOP_LOCATION_ID := p_location2_id;
888 -- AG 10+
889 l_delivery_leg_rec.pickup_stop_pa_date := p_date_1;
890 l_delivery_leg_rec.dropoff_stop_pa_date := p_date_2;
891 -- AG
892 -- How is delivery_type being handled -- no delivery type present in dleg_ccinfo_rec_type
893
894 l_delivery_leg_rec.PARENT_DELIVERY_LEG_ID := l_parent_delivery_leg_id;
895
896 IF l_delivery_leg_rec.DELIVERY_LEG_ID IS NOT NULL THEN
897 l_delivery_leg_rec.exists_in_database := 'Y';
898 ELSE
899 l_delivery_leg_rec.exists_in_database := 'N';
900 END IF;
901
902 IF p_action_code = WSH_FTE_CONSTRAINT_FRAMEWORK.G_DLEG_TRIP_SEARCH THEN
903
904 OPEN c_get_trip_detail(p_target_trip_id);
905 FETCH c_get_trip_detail INTO l_target_trip;
906 CLOSE c_get_trip_detail;
907
908 l_target_trip_tab(1) := l_target_trip;
909
910 END IF;
911
912 WSH_FTE_CONSTRAINT_FRAMEWORK.validate_constraint_dleg(
913 p_init_msg_list => p_init_msg_list,
914 p_action_code => p_action_code,
915 p_exception_list => l_dummy_exception_list,
916 p_delivery_leg_rec => l_delivery_leg_rec,
917 p_target_trip => l_target_trip_tab, -- Either of the next two
918 p_target_lane => l_dummy_target_lane,
919 x_succ_trips => l_succ_trips,
920 x_succ_lanes => l_dummy_succ_lanes,
921 x_validate_result => x_validate_result, -- Constraint Validation result : S / F
922 x_msg_count => x_msg_count, -- Standard FND functionality
923 x_msg_data => x_msg_data, -- Will return message text only if number of messages = 1
924 x_return_status => l_return_status);
925
926 IF l_return_status = WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR THEN
927
928 raise FND_API.G_EXC_UNEXPECTED_ERROR;
929 ELSE
930 x_return_status := l_return_status;
931 END IF;
932
933 --
934 IF l_debug_on THEN
935 WSH_DEBUG_SV.pop(l_module_name);
936 END IF;
937 --
938
939 EXCEPTION
940 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
941 x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR ;
942 --
943 IF l_debug_on THEN
944 WSH_DEBUG_SV.logmsg(l_module_name,'Unexpected error has occured. Oracle error message is '|| SQLERRM,WSH_DEBUG_SV.C_UNEXPEC_ERR_LEVEL);
945 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:FND_API.G_EXC_UNEXPECTED_ERROR');
946 END IF;
947 --
948 WHEN others THEN
949 x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR;
950 --
951 IF l_debug_on THEN
952 WSH_DEBUG_SV.logmsg(l_module_name,'Unexpected error has occured. Oracle error message is '|| SQLERRM,WSH_DEBUG_SV.C_UNEXPEC_ERR_LEVEL);
953 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:FND_API.G_EXC_UNEXPECTED_ERROR');
954 END IF;
955 --
956
957 END validate_constraint_dleg;
958
959 END WSH_FTE_COMP_CONSTRAINT_PKG;
960