1 PACKAGE BODY WSH_DELIVERY_LEGS_ACTIONS as
2 /* $Header: WSHDGACB.pls 120.13 2008/03/18 11:53:24 jnpinto noship $ */
3
4 --
5 G_PKG_NAME CONSTANT VARCHAR2(50) := 'WSH_DELIVERY_LEGS_ACTIONS';
6 --
7 g_wms_installed WSH_UTIL_CORE.Column_Tab_Type;
8
9 CURSOR C_IS_FIRST_LEG(p_trip_id in number, p_delivery_id in number, p_pickup_loc_id in number) is
10 select delivery_leg_id
11 from wsh_delivery_legs l, wsh_trip_stops s
12 where s.trip_id = p_trip_id
13 and l.delivery_id = p_delivery_id
14 and s.stop_id = l.pick_up_stop_id
15 and s.stop_location_id = p_pickup_loc_id;
16
17 -- Forward Declaration
18 FUNCTION Check_Rate_Trip_Contents(p_trip_id IN NUMBER,
19 x_return_status OUT nocopy VARCHAR2)
20 RETURN VARCHAR2;
21
22
23 PROCEDURE Assign_Deliveries
24 (p_del_rows IN wsh_util_core.id_tab_type,
25 p_trip_id IN NUMBER := NULL,
26 p_pickup_stop_id IN NUMBER := NULL,
27 p_pickup_stop_seq IN NUMBER := NULL,
28 p_dropoff_stop_id IN NUMBER := NULL,
29 p_dropoff_stop_seq IN NUMBER := NULL,
30 p_pickup_location_id IN NUMBER := NULL,
31 p_dropoff_location_id IN NUMBER := NULL,
32 p_create_flag IN VARCHAR2 := NULL,
33 x_leg_rows OUT NOCOPY wsh_util_core.id_tab_type,
34 x_return_status OUT NOCOPY VARCHAR2,
35 p_caller IN VARCHAR2,
36 p_pickup_arr_date IN DATE := to_date(NULL),
37 p_pickup_dep_date IN DATE := to_date(NULL),
38 p_dropoff_arr_date IN DATE := to_date(NULL),
39 p_dropoff_dep_date IN DATE := to_date(NULL),
40 p_sc_pickup_date IN DATE DEFAULT NULL,
41 p_sc_dropoff_date IN DATE DEFAULT NULL
42 ) IS
43
44 CURSOR stop_exists (l_trip_stop_id IN NUMBER) IS
45 SELECT status_code ,
46 NVL(SHIPMENTS_TYPE_FLAG,'O') SHIPMENTS_TYPE_FLAG, -- J-IB-NPARIKH
47 stop_location_id
48 FROM wsh_trip_stops
49 WHERE stop_id = l_trip_stop_id;
50
51 CURSOR delivery_info (del_id IN NUMBER) IS
52 SELECT initial_pickup_location_id,
53 initial_pickup_date,
54 ultimate_dropoff_location_id,
55 ultimate_dropoff_date,
56 organization_id,
57 status_code,
58 nvl(shipment_direction,'O') shipment_direction, -- J-IB-NPARIKH
59 -- J: W/V Changes
60 gross_weight,
61 net_weight,
62 volume,
63 mode_of_transport,
64 freight_terms_code,
65 name,
66 customer_id
67 FROM wsh_new_deliveries
68 WHERE delivery_id = del_id;
69
70 /* J TP Release : dels can be assigned to planned trips as long as they don't create new stops */
71 cursor get_trip_status(c_trip_id in NUMBER) is
72 select status_code, planned_flag,
73 NVL(SHIPMENTS_TYPE_FLAG,'O') SHIPMENTS_TYPE_FLAG, -- J-IB-NPARIKH
74 mode_of_transport
75 from wsh_trips
76 where trip_id = c_trip_id
77 FOR UPDATE NOWAIT;
78
79 l_plannedflag VARCHAR2(1);
80
81 CURSOR leg_exists ( del_id IN NUMBER) IS
82 SELECT dg.delivery_leg_id,
83 st1.stop_location_id,
84 st2.stop_location_id
85 FROM wsh_trip_stops st1,
86 wsh_trip_stops st2,
87 wsh_delivery_legs dg
88 WHERE st1.stop_id = dg.pick_up_stop_id AND
89 st2.stop_id = dg.drop_off_stop_id AND
90 st1.trip_id = p_trip_id AND
91 st2.trip_id = p_trip_id AND
92 dg.delivery_id = del_id
93 FOR UPDATE NOWAIT;
94 /* H integration added sequence number logic */
95
96
97 e_lock_error EXCEPTION;
98 pragma EXCEPTION_INIT(e_lock_error,-54);
99
100 --bug 4266758
101 --This cursor is opened (instead of get_stop cursor)
102 --When the following conditions are met
103 --a) The caller is form.
104 --b) The action is assign to trip
105 --c) The mod is PAD
106 --d) In Assign delivery to trip window, the "New" check box is checked.
107
108 CURSOR get_stop_new (l_location_id IN NUMBER,
109 l_PLANNED_ARRIVAL_DATE DATE,
110 l_PLANNED_DEPARTURE_DATE DATE
111 ) IS
112 SELECT stop_id, stop_sequence_number,
113 NVL(SHIPMENTS_TYPE_FLAG,'O') SHIPMENTS_TYPE_FLAG -- J-IB-NPARIKH
114 FROM wsh_trip_stops
115 WHERE stop_location_id = l_location_id AND
116 trip_id = p_trip_id AND
117 status_code <> 'CL'
118 AND PLANNED_ARRIVAL_DATE = NVL(l_PLANNED_ARRIVAL_DATE,PLANNED_ARRIVAL_DATE)
119 AND PLANNED_DEPARTURE_DATE = NVL(l_PLANNED_DEPARTURE_DATE,PLANNED_DEPARTURE_DATE)
120 FOR UPDATE NOWAIT;
121
122 CURSOR get_stop (l_location_id IN NUMBER, l_stop_sequence IN NUMBER
123 ) IS
124 SELECT stop_id, stop_sequence_number,
125 NVL(SHIPMENTS_TYPE_FLAG,'O') SHIPMENTS_TYPE_FLAG -- J-IB-NPARIKH
126 FROM wsh_trip_stops
127 WHERE stop_location_id = l_location_id AND
128 trip_id = p_trip_id AND
129 stop_sequence_number = nvl(l_stop_sequence,stop_sequence_number) AND
130 status_code <> 'CL'
131 FOR UPDATE NOWAIT;
132
133 CURSOR max_leg_seq_number ( del_id IN NUMBER) IS
134 SELECT max(dg.sequence_number)
135 FROM wsh_delivery_legs dg
136 WHERE dg.delivery_id = del_id;
137
138 CURSOR get_sequence (l_stop_id NUMBER) IS
139 SELECT stop_sequence_number
140 FROM wsh_trip_stops
141 WHERE stop_id = l_stop_id;
142
143 CURSOR c_check_dummystops(p_stop_id IN NUMBER, p_trip_id IN NUMBER) IS
144 SELECT 'Y'
145 FROM wsh_trip_stops wts1, wsh_trip_stops wts2
146 WHERE wts1.trip_id=p_trip_id
147 AND wts2.trip_id=p_trip_id
148 AND wts1.stop_id<>wts2.stop_id
149 AND wts1.stop_id=p_stop_id
150 AND ((wts2.physical_location_id=wts1.stop_location_id AND wts2.physical_stop_id IS NULL)
151 OR (wts1.physical_location_id=wts2.stop_location_id AND wts1.physical_stop_id IS NULL)
152 );
153
154 -- c_get_seq_numbers will derive the sequence numbers
155 -- for all stop locations populated in wsh_tmp
156 CURSOR c_get_seq_numbers is
157 select id,rownum*10
158 from(
159 select id
160 from wsh_tmp
161 order by to_date(column1,'DD-MM-RRRR HH24:MI:SS'),flag desc
162 );
163
164 -- Get Previous stops W/V converted to c_wt_uom/c_vol_uom
165 CURSOR c_get_prev_seq_wv(p_trip_id NUMBER, p_stop_seq NUMBER, p_wt_uom VARCHAR2, p_vol_uom VARCHAR2) IS
166 select wsh_wv_utils.convert_uom( WEIGHT_UOM_CODE, p_wt_uom, DEPARTURE_GROSS_WEIGHT, null) gross_weight,
167 wsh_wv_utils.convert_uom( WEIGHT_UOM_CODE, p_wt_uom, DEPARTURE_NET_WEIGHT, null) net_weight,
168 wsh_wv_utils.convert_uom( VOLUME_UOM_CODE, p_vol_uom, DEPARTURE_VOLUME, null) volume
169 from wsh_trip_stops
170 where trip_id = p_trip_id
171 and stop_sequence_number < p_stop_seq
172 order by stop_sequence_number desc;
173
174 CURSOR c_next_seq_exists (p_trip_id NUMBER, p_stop_seq NUMBER) IS
175 select 'x'
176 from wsh_trip_stops
177 where trip_id = p_trip_id
178 and stop_sequence_number > p_stop_seq;
179
180 l_customer_id NUMBER;
181 l_del_rows wsh_util_core.id_tab_type;
182 l_pickup_location_id_tbl wsh_util_core.id_tab_type;
183 l_pickup_date_tbl wsh_util_core.date_Tab_Type;
184 l_dropoff_location_id_tbl wsh_util_core.id_tab_type;
185 l_dropoff_date_tbl wsh_util_core.date_Tab_Type;
186 l_organization_id_tbl wsh_util_core.id_tab_type;
187 l_status_code_tbl wsh_util_core.column_Tab_Type;
188 l_shipment_direction_tbl wsh_util_core.column_Tab_Type;
189 l_gross_weight_tbl wsh_util_core.id_tab_type;
190 l_net_weight_tbl wsh_util_core.id_tab_type;
191 l_volume_tbl wsh_util_core.id_tab_type;
192 l_mode_of_transport_tbl wsh_util_core.column_tab_type;
193 l_freight_terms_code_tbl wsh_util_core.column_tab_type;
194 l_name_tbl wsh_util_core.column_tab_type;
195
196 l_id wsh_util_core.id_tab_type;
197 l_seq wsh_util_core.id_tab_type;
198 l_pregen_seq VARCHAR2(1);
199 l_sysdate DATE;
200 l_tmp_date DATE := NULL;
201 l_gross_weight NUMBER;
202 l_net_weight NUMBER;
203 l_volume NUMBER;
204 l_dummy VARCHAR2(1);
205 l_next_seq_exists BOOLEAN;
206
207 l_check_dummystops VARCHAR2(1);
208
209 l_count NUMBER := 0;
210
211
212 --2709662
213 l_trip_stop_info wsh_trip_stops_pvt.trip_stop_rec_type;
214 l_delivery_leg_info wsh_delivery_legs_pvt.delivery_leg_rec_type;
215 l_rowid VARCHAR2(30);
216 l_pickup_stop_id NUMBER;
217 l_dropoff_stop_id NUMBER;
218 l_pickup_location_id NUMBER;
219 l_del_pu_location_id NUMBER;
220 l_old_pickup_loc_id NUMBER;
221 l_pickup_date DATE;
222 l_dropoff_location_id NUMBER;
223 l_old_dropoff_loc_id NUMBER;
224 l_dropoff_date DATE;
225 l_org_id NUMBER;
226 l_leg_id NUMBER;
227 l_leg_seq_number NUMBER;
228 i BINARY_INTEGER;
229 l_flag VARCHAR2(2);
230 l_default_wt_uom VARCHAR2(3);
231 l_default_vol_uom VARCHAR2(3);
232 l_num_error NUMBER := 0;
233 l_num_warn NUMBER := 0;
234 l_trip_status VARCHAR2(2);
235 l_delivery_status VARCHAR2(2);
236 -- J: W/V Changes
237 l_org_gross_wt NUMBER;
238 l_org_net_wt NUMBER;
239 l_org_vol NUMBER;
240 l_trip_mode_of_transport VARCHAR2(30);
241 l_delivery_mode_of_transport VARCHAR2(30);
242 l_dummy_leg_id NUMBER;
243 l_dlvy_trip_tbl WMS_SHIPPING_INTERFACE_GRP.g_dlvy_trip_tbl;
244 l_msg_count NUMBER;
245 l_msg_data VARCHAR2(2000);
246
247 bad_trip_stop EXCEPTION;
248 invalid_trip EXCEPTION;
249 mark_reprice_error EXCEPTION;
250 rate_trip_contents_fail EXCEPTION;
251
252 /* new variables for stop sequence number */
253 l_stop_sequence_number NUMBER;
254 l_pickup_stop_seq NUMBER;
255 l_dropoff_stop_seq NUMBER;
256 l_new_flag VARCHAR2(30);
257
258 /* H integration for Multi Leg */
259 l_stop_rec WSH_TRIP_STOPS_PVT.TRIP_STOP_REC_TYPE;
260 l_pub_stop_rec WSH_TRIP_STOPS_PUB.TRIP_STOP_PUB_REC_TYPE;
261 l_trip_rec WSH_TRIPS_PVT.TRIP_REC_TYPE;
262 l_pub_trip_rec WSH_TRIPS_PUB.TRIP_PUB_REC_TYPE;
263 l_return_status VARCHAR2(30);
264
265 l_pickup_seq NUMBER;
266 l_dropoff_seq NUMBER;
267 l_stop_tab WSH_UTIL_CORE.id_tab_type; -- DBI Project
268 l_dbi_rs VARCHAR2(1); -- DBI Project
269 --
270 l_debug_on BOOLEAN;
271 --
272 l_module_name CONSTANT VARCHAR2(100) := 'wsh.plsql.' || G_PKG_NAME || '.' || 'ASSIGN_DELIVERIES';
273 --
274 l_shipment_direction VARCHAR2(30);
275 l_trip_shipments_type_flag VARCHAR2(30);
276 l_trip_shipments_type_flag_new VARCHAR2(30);
277 l_stop_shipments_type_flag VARCHAR2(30);
278 l_pu_stop_shipments_type_flag VARCHAR2(30);
279 l_do_stop_shipments_type_flag VARCHAR2(30);
280 l_pu_stop_shipType_flag_orig VARCHAR2(30);
281 l_do_stop_shipType_flag_orig VARCHAR2(30);
282 l_mixed_stops BOOLEAN := FALSE;
283 l_ob_to_ib_stop BOOLEAN := FALSE;
284 --
285 l_shipping_control VARCHAR2(30);
286 l_routing_response_id NUMBER;
287 l_routing_request_flag VARCHAR2(30);
288 l_chkStop_status_code VARCHAR2(30);
289 l_has_mixed_deliveries VARCHAR2(10);
290 l_stop_opened VARCHAR2(10);
291 l_stop_in_rec WSH_TRIP_STOPS_VALIDATIONS.chkClose_in_rec_type;
292 l_leg_complete boolean;
293
294 l_exception_name varchar2(30);
295 l_msg varchar2(2000);
296 l_exception_msg_count NUMBER;
297 l_exception_msg_data varchar2(2000);
298 l_dummy_exception_id NUMBER;
299 l_rate_trip_dels VARCHAR2(1);
300 l_rate_del VARCHAR2(1);
301 l_dummy_del NUMBER;
302 l_freight_terms_code VARCHAR2(30);
303
304 -- 3516052
305 l_pickup_arr_date DATE;
306 l_pickup_dep_date DATE;
307 l_dropoff_arr_date DATE;
308 l_dropoff_dep_date DATE;
309 l_trip_ids wsh_util_core.id_tab_type;
310 l_dummy_trip_ids wsh_util_core.id_tab_type;
311 l_phys_trip_dropoff_loc_id NUMBER;
312 l_trip_pickup_loc_id NUMBER;
313 l_trip_dropoff_loc_id NUMBER;
314 l_delivery_name wsh_new_deliveries.name%TYPE;
315
316 l_stop_seq_mode NUMBER; --SSN Frontport
317
318 -- K LPN CONV. rv
319 l_lpn_in_sync_comm_rec WSH_GLBL_VAR_STRCT_GRP.lpn_sync_comm_in_rec_type;
320 l_lpn_out_sync_comm_rec WSH_GLBL_VAR_STRCT_GRP.lpn_sync_comm_out_rec_type;
321 e_return_excp EXCEPTION;
322 -- K LPN CONV. rv
323
324 --
325 -- Bug 5336308
326 -- Added these two exceptions and cleaned up code to not use GOTO statements
327 -- Also removed savepoint before_Create_stop and corresponding rollbacks
328 -- since they were redundant.
329 --
330 e_InvalidDelExcep EXCEPTION;
331 e_CommonExcep EXCEPTION;
332 --
333 BEGIN
334 -- check if trip is not closed
335 --
336 -- Debug Statements
337 --
338 --
339 l_debug_on := WSH_DEBUG_INTERFACE.g_debug;
340 --
341 IF l_debug_on IS NULL
342 THEN
343 l_debug_on := WSH_DEBUG_SV.is_debug_enabled;
344 END IF;
345
346 IF l_debug_on THEN
347 WSH_DEBUG_SV.push(l_module_name);
348 --
349 WSH_DEBUG_SV.log(l_module_name,'P_TRIP_ID',P_TRIP_ID);
350 WSH_DEBUG_SV.log(l_module_name,'P_PICKUP_STOP_ID',P_PICKUP_STOP_ID);
351 WSH_DEBUG_SV.log(l_module_name,'P_PICKUP_STOP_SEQ',P_PICKUP_STOP_SEQ);
352 WSH_DEBUG_SV.log(l_module_name,'P_DROPOFF_STOP_ID',P_DROPOFF_STOP_ID);
353 WSH_DEBUG_SV.log(l_module_name,'P_DROPOFF_STOP_SEQ',P_DROPOFF_STOP_SEQ);
354 WSH_DEBUG_SV.log(l_module_name,'P_PICKUP_LOCATION_ID',P_PICKUP_LOCATION_ID);
355 WSH_DEBUG_SV.log(l_module_name,'P_DROPOFF_LOCATION_ID',P_DROPOFF_LOCATION_ID);
356 WSH_DEBUG_SV.log(l_module_name,'P_CREATE_FLAG',P_CREATE_FLAG);
357 WSH_DEBUG_SV.log(l_module_name,'P_PICKUP_ARR_DATE',P_PICKUP_ARR_DATE);
358 WSH_DEBUG_SV.log(l_module_name,'P_PICKUP_DEP_DATE',P_PICKUP_DEP_DATE);
359 WSH_DEBUG_SV.log(l_module_name,'P_DROPOFF_ARR_DATE',P_DROPOFF_ARR_DATE);
360 WSH_DEBUG_SV.log(l_module_name,'P_DROPOFF_DEP_DATE',P_DROPOFF_DEP_DATE);
361 WSH_DEBUG_SV.log(l_module_name,'P_CALLER',P_CALLER);
362 WSH_DEBUG_SV.log(l_module_name,'p_sc_pickup_date',p_sc_pickup_date);
363 WSH_DEBUG_SV.log(l_module_name,'p_sc_dropoff_date',p_sc_dropoff_date);
364 END IF;
365 --
366 l_stop_seq_mode := WSH_TRIPS_ACTIONS.GET_STOP_SEQ_MODE;
367 --
368 open get_trip_status(p_trip_id);
369 fetch get_trip_status into l_trip_status, l_plannedflag, l_trip_shipments_type_flag, l_trip_mode_of_transport;
370 close get_trip_status;
371 --
372 --
373 IF l_debug_on THEN
374 WSH_DEBUG_SV.log(l_module_name,'l_trip_shipments_type_flag',l_trip_shipments_type_flag);
375 END IF;
376 --
377 IF l_trip_status = 'CL'
378 THEN
379 RAISE Invalid_Trip;
380 END IF;
381 --
382 /* J TP Release : dels can be assigned to planned trips as long as they don't create new stops */
383 -- Check if the pickup and dropoff stops exist and are not closed
384
385 IF (p_pickup_stop_id IS NOT NULL) THEN
386
387 OPEN stop_exists (p_pickup_stop_id);
388 FETCH stop_exists INTO l_flag, l_pu_stop_shipments_type_flag, l_trip_pickup_loc_id;
389
390 IF (stop_exists%NOTFOUND) THEN
391 l_flag := 'XX';
392 RAISE bad_trip_stop; -- J-IB-NPARIKH
393 END IF;
394
395 CLOSE stop_exists;
396
397 IF (l_flag NOT IN ('OP', 'AR'))
398 THEN
399 RAISE bad_trip_stop;
400 END IF;
401
402 l_pickup_stop_id := p_pickup_stop_id;
403 l_pu_stop_shipType_flag_orig := l_pu_stop_shipments_type_flag;
404 END IF;
405
406 IF (p_dropoff_stop_id IS NOT NULL) THEN
407
408 OPEN stop_exists (p_dropoff_stop_id);
409 FETCH stop_exists INTO l_flag, l_do_stop_shipments_type_flag, l_trip_dropoff_loc_id;
410
411 IF (stop_exists%NOTFOUND) THEN
412 l_flag := 'XX';
413 RAISE bad_trip_stop; -- J-IB-NPARIKH
414 END IF;
415
416 CLOSE stop_exists;
417
418 IF (l_flag NOT IN ('OP', 'AR'))
419 THEN
420 RAISE bad_trip_stop;
421 END IF;
422
423 l_dropoff_stop_id := p_dropoff_stop_id;
424 l_do_stop_shipType_flag_orig := l_do_stop_shipments_type_flag;
425 END IF;
426
427 --a) Check if pickup is being passed as same as dropoff - if yes, error
428 IF p_pickup_location_id IS NOT NULL THEN
429 l_trip_pickup_loc_id := p_pickup_location_id;
430 END IF;
431 IF p_dropoff_location_id IS NOT NULL THEN
432 l_trip_dropoff_loc_id := p_dropoff_location_id;
433 END IF;
434
435 --get the physical locations
436 IF (p_dropoff_stop_id IS NOT NULL OR p_dropoff_location_id IS NOT NULL) THEN
437 WSH_LOCATIONS_PKG.Convert_internal_cust_location(
438 p_internal_cust_location_id => l_trip_dropoff_loc_id,
439 x_internal_org_location_id => l_phys_trip_dropoff_loc_id,
440 x_return_status => l_return_status);
441 IF l_return_status in (WSH_UTIL_CORE.G_RET_STS_ERROR, WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR) THEN
442 x_return_status:=l_return_status;
443 --RETURN;
444 raise e_return_excp; -- LPN CONV. rv
445 END IF;
446 IF l_debug_on THEN
447 WSH_DEBUG_SV.log(l_module_name,'l_phys_trip_dropoff_loc_id',l_phys_trip_dropoff_loc_id);
448 END IF;
449 END IF;
450
451 IF (WSH_UTIL_CORE.FTE_IS_INSTALLED = 'Y') THEN
452
453 l_rate_trip_dels := Check_Rate_Trip_Contents(p_trip_id => p_trip_id,
454 x_return_status => l_return_status);
455
456 IF l_return_status in (WSH_UTIL_CORE.G_RET_STS_ERROR, WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR) THEN
457
458 RAISE rate_trip_contents_fail;
459
460 END IF;
461
462 END IF;
463
464 -- Delivery Validations
465 FOR i IN 1..p_del_rows.count LOOP --{
466
467 OPEN delivery_info (p_del_rows(i));
468 FETCH delivery_info INTO l_pickup_location_id, l_pickup_date, l_dropoff_location_id,
469 l_dropoff_date, l_org_id, l_delivery_status, l_shipment_direction,
470 l_org_gross_wt, l_org_net_wt, l_org_vol, l_delivery_mode_of_transport,
471 l_freight_terms_code, l_delivery_name, l_customer_id;
472 CLOSE delivery_info;
473
474 IF l_debug_on THEN
475 wsh_debug_sv.log(l_module_name, 'Del '||p_del_rows(i)||' Status '||l_delivery_status);
476 END IF;
477
478 -- Check if the delivery is in a valid status.
479 IF l_delivery_status in ('CA', 'CL') THEN
480 goto skip_delivery;
481 END IF;
482
483 --if trip's dropoff is specified as internal, cannot have deliveries with ultimate dropoffs
484 --which are not the same internal loc being assigned to the trip
485 IF l_trip_dropoff_loc_id IS NOT NULL AND
486 l_phys_trip_dropoff_loc_id IS NOT NULL AND
487 l_dropoff_location_id <> l_trip_dropoff_loc_id THEN
488 FND_MESSAGE.SET_NAME('WSH','WSH_DEL_DROP_NOTINT');
489 FND_MESSAGE.SET_TOKEN('DEL_NAME',l_delivery_name);
490 wsh_util_core.add_message(WSH_UTIL_CORE.G_RET_STS_ERROR,l_module_name);
491 goto skip_delivery;
492 END IF;
493
494 l_del_rows(l_del_rows.COUNT+1) := p_del_rows(i);
495 l_pickup_location_id_tbl(l_del_rows.COUNT) := l_pickup_location_id;
496 l_pickup_date_tbl(l_del_rows.COUNT) := l_pickup_date;
497 l_dropoff_location_id_tbl (l_del_rows.COUNT) := l_dropoff_location_id;
498 l_dropoff_date_tbl(l_del_rows.COUNT) := l_dropoff_date;
499 l_organization_id_tbl(l_del_rows.COUNT) := l_org_id;
500 l_status_code_tbl(l_del_rows.COUNT) := l_delivery_status;
501 l_shipment_direction_tbl(l_del_rows.COUNT) := l_shipment_direction;
502 l_gross_weight_tbl(l_del_rows.COUNT) := l_org_gross_wt;
503 l_net_weight_tbl(l_del_rows.COUNT) := l_org_net_wt;
504 l_volume_tbl(l_del_rows.COUNT) := l_org_vol;
505 l_mode_of_transport_tbl(l_del_rows.COUNT) := l_delivery_mode_of_transport;
506 l_freight_terms_code_tbl(l_del_rows.COUNT) := l_freight_terms_code;
507 l_name_tbl(l_del_rows.COUNT) := l_delivery_name;
508
509 goto loop_end1;
510
511 <<skip_delivery>>
512
513 FND_MESSAGE.SET_NAME('WSH','WSH_DEL_ASSIGN_ERROR');
514 FND_MESSAGE.SET_TOKEN('DEL_NAME',l_delivery_name);
515 FND_MESSAGE.SET_TOKEN('TRIP_NAME',wsh_trips_pvt.get_name(p_trip_id));
516 wsh_util_core.add_message(WSH_UTIL_CORE.G_RET_STS_ERROR);
517 l_num_error := l_num_error + 1;
518
519 <<loop_end1>>
520 null;
521
522 END LOOP; --}
523
524 l_pregen_seq := 'N';
525 IF (p_caller = 'AUTOCREATE_TRIP' ) THEN --{
526
527 -- The following logic pre-determines the PAD and sequence numbers for each distinct location(pickup/dropoff)
528 IF l_del_rows.COUNT > 1 THEN --{
529
530
531 IF l_debug_on THEN
532 wsh_debug_sv.log(l_module_name, 'Pre determining the sequence numbers/PAD');
533 END IF;
534
535 l_pregen_seq := 'Y';
536 l_sysdate := sysdate;
537
538 DELETE FROM WSH_TMP;
539
540 IF (p_sc_pickup_date IS NOT NULL) OR (p_sc_dropoff_date IS NOT NULL) THEN --{
541 FORALL i in l_del_rows.FIRST..l_del_rows.LAST
542 INSERT INTO wsh_tmp(
543 ID,
544 FLAG,
545 COLUMN1)
546 SELECT l_pickup_location_id_tbl(i), 'P', to_char(nvl(p_sc_pickup_date,l_sysdate), 'DD-MM-RRRR HH24:MI:SS')
547 FROM dual
548 WHERE NOT EXISTS (
549 select 'x'
550 from wsh_tmp wt1
551 where wt1.id = l_pickup_location_id_tbl(i)
552 and wt1.flag='P'
553 and wt1.column1 = to_char(nvl(p_sc_pickup_date,l_sysdate), 'DD-MM-RRRR HH24:MI:SS'))
554 UNION
555 SELECT l_dropoff_location_id_tbl(i), 'D', to_char(nvl(p_sc_dropoff_date,l_sysdate), 'DD-MM-RRRR HH24:MI:SS')
556 FROM dual
557 WHERE NOT EXISTS (
558 select 'x'
559 from wsh_tmp wt1
560 where wt1.id = l_dropoff_location_id_tbl(i)
561 and wt1.flag='D'
562 and wt1.column1 = to_char(nvl(p_sc_dropoff_date,l_sysdate), 'DD-MM-RRRR HH24:MI:SS'));
563 ELSE
564 FORALL i in l_del_rows.FIRST..l_del_rows.LAST
565 INSERT INTO wsh_tmp(
566 ID,
567 FLAG,
568 COLUMN1)
569 SELECT l_pickup_location_id_tbl(i), 'P', to_char(nvl(l_pickup_date_tbl(i),l_sysdate), 'DD-MM-RRRR HH24:MI:SS')
570 FROM dual
571 WHERE NOT EXISTS (
572 select 'x'
573 from wsh_tmp wt1
574 where wt1.id = l_pickup_location_id_tbl(i)
575 and wt1.flag='P'
576 and wt1.column1 = to_char(nvl(l_pickup_date_tbl(i),l_sysdate), 'DD-MM-RRRR HH24:MI:SS'))
577 UNION
578 SELECT l_dropoff_location_id_tbl(i), 'D', to_char(nvl(l_dropoff_date_tbl(i),l_sysdate), 'DD-MM-RRRR HH24:MI:SS')
579 FROM dual
580 WHERE NOT EXISTS (
581 select 'x'
582 from wsh_tmp wt1
583 where wt1.id = l_dropoff_location_id_tbl(i)
584 and wt1.flag='D'
585 and wt1.column1 = to_char(nvl(l_dropoff_date_tbl(i),l_sysdate), 'DD-MM-RRRR HH24:MI:SS'));
586 END IF; --}
587
588 -- For pickups the PAD is min of all initial_pickup_date
589 DELETE FROM wsh_tmp wt1
590 WHERE wt1.flag = 'P'
591 AND exists (
592 select 'x'
593 from wsh_tmp wt2
594 where wt1.id = wt2.id
595 and wt2.flag = 'P'
596 and to_date(wt2.column1,'DD-MM-RRRR HH24:MI:SS') < to_date(wt1.column1,'DD-MM-RRRR HH24:MI:SS'));
597
598 -- For dropoffs the PAD is max of all ultimate_dropoff_date
599 DELETE FROM wsh_tmp wt1
600 WHERE wt1.flag = 'D'
601 AND exists (
602 select 'x'
603 from wsh_tmp wt2
604 where wt1.id = wt2.id
605 and wt2.flag = 'D'
606 and to_date(wt2.column1,'DD-MM-RRRR HH24:MI:SS') > to_date(wt1.column1,'DD-MM-RRRR HH24:MI:SS'));
607
608 OPEN c_get_seq_numbers;
609 LOOP
610 FETCH c_get_seq_numbers BULK COLLECT INTO l_id,l_seq LIMIT 1000;
611
612 IF l_id.COUNT > 0 THEN
613 FORALL i in l_id.FIRST..l_id.LAST
614 UPDATE wsh_tmp
615 set column2 = l_seq(i)
616 WHERE id = l_id(i);
617 END IF;
618
619 IF l_debug_on THEN
620 FOR i in l_id.FIRST..l_id.LAST LOOP
621 wsh_debug_sv.log(l_module_name, 'l_id '||l_id(i)||' l_seq '||l_seq(i));
622 END LOOP;
623 END IF;
624
625 EXIT WHEN c_get_seq_numbers%NOTFOUND;
626 END LOOP;
627 CLOSE c_get_seq_numbers;
628
629 END IF; --}
630
631 END IF; --}
632
633 FOR i IN 1..l_del_rows.count LOOP
634 --(
635 -- Bug 5336308
636 -- Added BEGIN-END block within this loop, added exception handlers to replace use of GOTO logic
637 --
638 BEGIN
639 --(
640 -- Initialize cursor return variables for each loop count
641 SAVEPOINT ASSIGN_DEL_TO_TRIP;
642
643 l_leg_id := NULL;
644 l_leg_seq_number := NULL;
645 l_pickup_location_id := NULL;
646 l_dropoff_location_id := NULL;
647 l_tmp_date := NULL;
648
649 -- Fetch delivery information including initial_pickup_location,
650 -- ultimate_dropoff_location, dates and organization_id
651
652 l_pickup_location_id := l_pickup_location_id_tbl(i);
653 l_pickup_date := l_pickup_date_tbl(i);
654 l_dropoff_location_id := l_dropoff_location_id_tbl(i);
655 l_dropoff_date := l_dropoff_date_tbl(i);
656 l_org_id := l_organization_id_tbl(i);
657 l_delivery_status := l_status_code_tbl(i);
658 l_shipment_direction := l_shipment_direction_tbl(i);
659 l_org_gross_wt := l_gross_weight_tbl(i);
660 l_org_net_wt := l_net_weight_tbl(i);
661 l_org_vol := l_volume_tbl(i);
662 l_delivery_mode_of_transport := l_mode_of_transport_tbl(i);
663 l_freight_terms_code := l_freight_terms_code_tbl(i);
664 l_delivery_name := l_name_tbl(i);
665
666 l_del_pu_location_id := l_pickup_location_id;
667
668 -- If the pick up date and drop off date are defaulted from the
669 -- ship confirm API then use these dates. Bug 3913206
670 --
671 IF (p_sc_pickup_date IS NOT NULL) OR (p_sc_dropoff_date IS NOT NULL)
672 THEN --{
673 l_pickup_date := p_sc_pickup_date;
674 l_dropoff_date := p_sc_dropoff_date;
675 END IF; --}
676 --
677
678 IF l_debug_on THEN
679 wsh_debug_sv.log(l_module_name, 'Processing Del '||l_del_rows(i)||' Shipment direction '|| l_shipment_direction);
680 END IF;
681 --
682
683 IF (WSH_UTIL_CORE.FTE_IS_INSTALLED = 'Y') THEN
684 IF (NVL(l_rate_trip_dels, 'X') <> 'M') THEN
685 -- IF l_rate_trip_dels = 'M', then trip is already mixed, exceptions have been logged.
686
687 l_rate_del := Check_Rate_Delivery(p_delivery_id => NULL,
688 p_freight_terms_code => l_freight_terms_code,
689 p_shipment_direction => l_shipment_direction,
690 x_return_status => l_return_status);
691
692 IF l_return_status in (WSH_UTIL_CORE.G_RET_STS_ERROR, WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR) THEN
693 RAISE e_InvalidDelExcep; -- Bug 5336308
694 END IF;
695
696 IF (l_rate_del <> l_rate_trip_dels) AND (l_rate_trip_dels <> 'M') THEN
697 -- We need to check for l_rate_trip_dels = 'M' again because it could have been populated in
698 -- the above call.
699
700
701 -- Raise Warning
702 FND_MESSAGE.Set_Name('WSH', 'WSH_RATE_MIXED_TRIP_WARN');
703 wsh_util_core.add_message(WSH_UTIL_CORE.G_RET_STS_WARNING, l_module_name);
704 l_num_warn := l_num_warn + 1;
705
706 -- Log Exception
707
708 l_exception_name := 'WSH_RATE_MIXED_TRIP';
709 l_msg := FND_MESSAGE.Get_String('WSH', 'WSH_RATE_MIXED_TRIP_EXC');
710
711
712 wsh_xc_util.log_exception(
713 p_api_version => 1.0,
714 x_return_status => l_return_status,
715 x_msg_count => l_exception_msg_count,
716 x_msg_data => l_exception_msg_data,
717 x_exception_id => l_dummy_exception_id ,
718 p_logged_at_location_id => l_pickup_location_id,
719 p_exception_location_id => l_pickup_location_id,
720 p_logging_entity => 'SHIPPER',
721 p_logging_entity_id => FND_GLOBAL.USER_ID,
722 p_exception_name => l_exception_name,
723 p_message => l_msg,
724 p_trip_id => p_trip_id
725 );
726
727 IF l_return_status <> WSH_UTIL_CORE.G_RET_STS_SUCCESS THEN
728 l_num_warn := l_num_warn + 1;
729 RAISE e_InvalidDelExcep; -- Bug 5336308
730 END IF;
731
732 END IF;
733 END IF;
734
735 END IF;
736 --
737 -- Get default UOMs for the shipping organization. Do not check
738 -- return status as these are not mandatory parameters.
739
740 --
741 -- Debug Statements
742 --
743 IF l_debug_on THEN
744 WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_WV_UTILS.GET_DEFAULT_UOMS',WSH_DEBUG_SV.C_PROC_LEVEL);
745 END IF;
746 --
747 wsh_wv_utils.get_default_uoms(l_org_id, l_default_wt_uom, l_default_vol_uom, x_return_status);
748
749 -- Check if delivery leg exists for the delivery/trip combination
750 -- Skip this delivery if leg exists
751
752 OPEN leg_exists (l_del_rows(i));
753 FETCH leg_exists INTO l_leg_id, l_old_pickup_loc_id, l_old_dropoff_loc_id;
754 CLOSE leg_exists;
755
756 IF (l_old_pickup_loc_id = nvl(p_pickup_location_id, l_pickup_location_id)) AND
757 (l_old_dropoff_loc_id = nvl(p_dropoff_location_id, l_dropoff_location_id)) THEN
758 RAISE e_CommonExcep; -- Bug 5336308
759 END IF;
760
761 --TD: Check if trip stop can be created for this trip (trip should not be planned)
762 -- If create flag is set and no stops exist then check if stops
763 -- need to be created
764
765 IF p_pickup_location_id IS NOT NULL THEN
766 l_pickup_location_id := p_pickup_location_id;
767 END IF;
768 IF p_dropoff_location_id IS NOT NULL THEN
769 l_dropoff_location_id := p_dropoff_location_id;
770 END IF;
771
772 IF (p_create_flag = 'Y')AND(p_pickup_stop_id IS NULL) THEN
773
774 l_pickup_stop_id := NULL;
775 /* the new Pickup takes precedence over delivery pickup location */
776 IF (p_pickup_location_id IS NOT NULL) THEN
777 l_pickup_location_id := p_pickup_location_id;
778 END IF;
779
780 IF p_caller = 'WSH_FSTRXASSIGNTRIP' AND p_pickup_stop_seq IS NULL THEN
781 OPEN get_stop_new (l_pickup_location_id,P_PICKUP_ARR_DATE,P_PICKUP_DEP_DATE);
782 FETCH get_stop_new INTO l_pickup_stop_id, l_pickup_stop_seq, l_stop_shipments_type_flag;
783 CLOSE get_stop_new;
784 ELSE
785 OPEN get_stop (l_pickup_location_id,p_pickup_stop_seq);
786 FETCH get_stop INTO l_pickup_stop_id, l_pickup_stop_seq, l_stop_shipments_type_flag;
787 CLOSE get_stop;
788 END IF;
789
790 IF l_debug_on THEN
791 WSH_DEBUG_SV.log(l_module_name,'l_pickup_stop_id-'||l_pickup_stop_id);
792 WSH_DEBUG_SV.log(l_module_name,'l_pickup_location_id-'||l_pickup_location_id);
793 WSH_DEBUG_SV.log(l_module_name,'pickup_stop_seq-'||p_pickup_stop_seq);
794 WSH_DEBUG_SV.log(l_module_name,'l_stop_shipments_type_flag',l_stop_shipments_type_flag);
795 END IF;
796 /* H integration , for stop sequence number */
797 IF p_pickup_location_id IS NOT NULL THEN
798 l_new_flag := 'PICKUP';
799 l_pickup_stop_seq := p_pickup_stop_seq;
800 ELSE
801 l_new_flag := 'DELIVERY';
802 l_pickup_stop_seq := NULL;
803 END IF;
804
805 --4106241 : Call get_new_sequence_number only if l_pickup_stop_seq and l_pickup_stop_id are null.
806 If l_pickup_stop_seq is NULL and l_pickup_stop_id is NULL THEN
807 IF l_pregen_seq = 'N' THEN
808 --
809 --
810 -- Debug Statements
811 --
812 IF l_debug_on THEN
813 WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_TRIP_STOPS_VALIDATIONS.GET_NEW_SEQUENCE_NUMBER',WSH_DEBUG_SV.C_PROC_LEVEL);
814 END IF;
815 --
816 WSH_TRIP_STOPS_VALIDATIONS.get_new_sequence_number
817 (x_stop_sequence_number => l_pickup_stop_seq,
818 p_trip_id => p_trip_id,
819 p_status_code => 'OP',
820 p_stop_id => null, -- as of now no validations
821 p_new_flag => l_new_flag,
822 x_return_status => l_return_status
823 );
824 IF l_return_status <> WSH_UTIL_CORE.G_RET_STS_SUCCESS THEN
825 x_return_status := l_return_status;
826 raise e_return_excp; -- LPN CONV. rv
827 END IF;
828 ELSE
829 BEGIN
830 select column2, to_date(column1,'DD-MM-RRRR HH24:MI:SS')
831 into l_pickup_stop_seq,l_tmp_date
832 from wsh_tmp
833 where id = l_pickup_location_id
834 and flag = 'P';
835 EXCEPTION
836 WHEN NO_DATA_FOUND THEN
837 x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
838 raise e_return_excp; -- LPN CONV. rv
839 WHEN OTHERS THEN
840 x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
841 raise e_return_excp; -- LPN CONV. rv
842 END;
843 END IF; --}
844 --
845 END IF;
846 /* End of H integration , for stop sequence number */
847
848 IF (l_pickup_stop_id IS NULL) THEN
849 l_trip_stop_info.trip_id := p_trip_id;
850 l_trip_stop_info.status_code := 'OP';
851 --l_trip_stop_info.stop_sequence_number := -99;
852 l_trip_stop_info.stop_sequence_number := l_pickup_stop_seq;
853 l_trip_stop_info.stop_location_id := l_pickup_location_id;
854 -- Bug 3349133
855 IF l_pregen_seq = 'N' THEN
856 l_trip_stop_info.planned_arrival_date := NVL(l_pickup_date, sysdate);
857 ELSE
858 l_trip_stop_info.planned_arrival_date := l_tmp_date;
859 END IF;
860 l_trip_stop_info.planned_departure_date := l_trip_stop_info.planned_arrival_date;
861 l_trip_stop_info.weight_uom_code := l_default_wt_uom;
862 l_trip_stop_info.volume_uom_code := l_default_vol_uom;
863
864 -- Default the W/V from previous stop(sequence)
865 l_gross_weight := NULL;
866 l_net_weight := NULL;
867 l_volume := NULL;
868
869 OPEN c_get_prev_seq_wv(p_trip_id, l_pickup_stop_seq, l_default_wt_uom, l_default_vol_uom);
870 FETCH c_get_prev_seq_wv into l_gross_weight,l_net_weight,l_volume;
871 CLOSE c_get_prev_seq_wv;
872
873 IF l_debug_on THEN
874 WSH_DEBUG_SV.log(l_module_name,'Prev stop Gross '||l_gross_weight||' Net '||l_net_weight||' Vol '||l_volume);
875 END IF;
876
877 l_trip_stop_info.departure_gross_weight := l_gross_weight;
878 l_trip_stop_info.departure_net_weight := l_net_weight;
879 l_trip_stop_info.departure_volume := l_volume;
880
881 -- J-IB-NPARIKH-{
882 --
883 -- For a new stop, calculate value of shipments type flag
884 -- using shipment direction of the delivery being assigned.
885 --
886 IF l_shipment_direction IN ('O','IO')
887 THEN
888 l_trip_stop_info.shipments_type_flag := 'O';
889 ELSE
890 l_trip_stop_info.shipments_type_flag := 'I';
891 END IF;
892 --
893 IF l_debug_on THEN
894 WSH_DEBUG_SV.log(l_module_name,'l_trip_stop_info.shipments_type_flag',l_trip_stop_info.shipments_type_flag);
895 END IF;
896 --
897 -- J-IB-NPARIKH-}
898
899 IF p_pickup_location_id IS NOT NULL THEN
900 IF l_debug_on THEN
901 WSH_DEBUG_SV.log(l_module_name,'updating dates for pickup loc which is getting created',p_pickup_location_id );
902 END IF;
903 l_trip_stop_info.planned_arrival_date := p_pickup_arr_date;
904 l_trip_stop_info.planned_departure_date := p_pickup_dep_date;
905 END IF;
906
907
908 /* H integration - call Multi Leg FTE */
909 IF (WSH_UTIL_CORE.FTE_IS_INSTALLED = 'Y') THEN
910 --
911 -- Debug Statements
912 --
913 IF l_debug_on THEN
914 WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_FTE_INTEGRATION.TRIP_STOP_VALIDATIONS',WSH_DEBUG_SV.C_PROC_LEVEL);
915 END IF;
916 --
917 wsh_fte_integration.trip_stop_validations
918 (p_stop_rec => l_trip_stop_info,
919 p_trip_rec => l_trip_rec,
920 p_action => 'ADD',
921 x_return_status => l_return_status);
922
923 IF l_return_status <> WSH_UTIL_CORE.G_RET_STS_SUCCESS THEN
924 IF l_return_status = WSH_UTIL_CORE.G_RET_STS_WARNING THEN
925 l_num_warn := l_num_warn + 1;
926 ELSE
927 x_return_status := l_return_status;
928 --
929 -- Debug Statements
930 --
931 --IF l_debug_on THEN
932 --WSH_DEBUG_SV.pop(l_module_name);
933 --END IF;
934 --
935 --RETURN;
936 raise e_return_excp; -- LPN CONV. rv
937 END IF;
938 END IF;
939
940 END IF;
941
942 /* End of H integration - call Multi Leg FTE */
943 --
944 -- Debug Statements
945 --
946 IF l_debug_on THEN
947 WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_TRIP_STOPS_PVT.CREATE_TRIP_STOP',WSH_DEBUG_SV.C_PROC_LEVEL);
948 END IF;
949 --
950 --
951
952 wsh_trip_stops_pvt.create_trip_stop(l_trip_stop_info,l_rowid,l_pickup_stop_id, x_return_status);
953
954 IF (x_return_status <> WSH_UTIL_CORE.G_RET_STS_SUCCESS) THEN
955 RAISE e_InvalidDelExcep; -- Bug 5336308
956 END IF;
957
958 --For a Routing Firm trip, when action is assign trip, check if the new stop
959 --created is a dummy stop which already has physical stop in trip or if the
960 --new stop is the physical stop and dummy is already present in the trip. Only
961 --in these 2 cases, we allow stop creation for Routing Firm trip. Else, this
962 --delivery cannot be assigned to the trip as this entails creation of a new stop
963 --This has to be done after create stop as linking happens only at creation time
964 IF l_plannedflag='Y' AND p_caller like '%ASSIGNTRIP%' THEN
965 OPEN c_check_dummystops(l_pickup_stop_id, p_trip_id);
966 FETCH c_check_dummystops INTO l_check_dummystops;
967 IF c_check_dummystops%NOTFOUND THEN
968 CLOSE c_check_dummystops;
969 IF l_debug_on THEN
970 WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit wsh_trips_pvt.get_name',WSH_DEBUG_SV.C_PROC_LEVEL);
971 END IF;
972 FND_MESSAGE.SET_NAME('WSH','WSH_FIRMTRIP_NO_NEW_STOP');
973 FND_MESSAGE.SET_TOKEN('TRIP_NAME',wsh_trips_pvt.get_name(p_trip_id));
974 wsh_util_core.add_message(WSH_UTIL_CORE.G_RET_STS_ERROR, l_module_name);
975 RAISE e_InvalidDelExcep; -- Bug 5336308
976 END IF;
977 CLOSE c_check_dummystops;
978 END IF;
979
980 IF( WSH_UTIL_CORE.FTE_Is_Installed = 'Y') THEN
981 l_trip_mode_of_transport := NVL(l_trip_mode_of_transport, l_delivery_mode_of_transport);
982 WSH_UTIL_VALIDATE.Validate_Trip_MultiStops (
983 p_trip_id => p_trip_id,
984 p_mode_of_transport => l_trip_mode_of_transport,
985 x_return_status => x_return_status);
986 IF l_debug_on THEN
987 WSH_DEBUG_SV.log(l_module_name,'WSH_UTIL_VALIDATE.Validate_MultiStops x_return_status',x_return_status);
988 END IF;
989
990 IF x_return_status IN (WSH_UTIL_CORE.G_RET_STS_ERROR, WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR) THEN
991 RAISE e_InvalidDelExcep; -- Bug 5336308
992 END IF;
993
994 END IF;
995 -- 3516052 keep the pickup arrival date and departure date
996 l_pickup_arr_date := l_trip_stop_info.planned_arrival_date;
997 l_pickup_dep_date := l_trip_stop_info.planned_departure_date;
998 l_pickup_stop_seq := l_trip_stop_info.stop_sequence_number; --SSN Frontport
999
1000 ELSE
1001 -- Get pvt type record structure for stop
1002 --
1003 -- Debug Statements
1004 --
1005 IF l_debug_on THEN
1006 WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_TRIP_STOPS_GRP.GET_STOP_DETAILS_PVT',WSH_DEBUG_SV.C_PROC_LEVEL);
1007 END IF;
1008 --
1009 wsh_trip_stops_grp.get_stop_details_pvt
1010 (p_stop_id => l_pickup_stop_id,
1011 x_stop_rec => l_stop_rec,
1012 x_return_status => l_return_status);
1013
1014 IF l_return_status <> WSH_UTIL_CORE.G_RET_STS_SUCCESS THEN
1015 x_return_status := l_return_status;
1016 --
1017 -- Debug Statements
1018 --
1019 --IF l_debug_on THEN
1020 --WSH_DEBUG_SV.pop(l_module_name);
1021 --END IF;
1022 --
1023 --RETURN;
1024 raise e_return_excp; -- LPN CONV. rv
1025 END IF;
1026
1027 -- 3516052 keep the pickup arrival date and deaprture date
1028 l_pickup_arr_date := l_stop_rec.planned_arrival_date;
1029 l_pickup_dep_date := l_stop_rec.planned_departure_date;
1030
1031 --get pickup time, if > l_pickup_date, then update pickup stop
1032 --with the l_pickup_date so that the earliet time is set
1033 /* H integration - call Multi Leg FTE */
1034 IF (WSH_UTIL_CORE.FTE_IS_INSTALLED = 'Y') THEN
1035
1036 --
1037 -- Debug Statements
1038 --
1039 IF l_debug_on THEN
1040 WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_FTE_INTEGRATION.TRIP_STOP_VALIDATIONS',WSH_DEBUG_SV.C_PROC_LEVEL);
1041 END IF;
1042 --
1043 wsh_fte_integration.trip_stop_validations
1044 (p_stop_rec => l_stop_rec,
1045 p_trip_rec => l_trip_rec,
1046 p_action => 'UPDATE',
1047 x_return_status => l_return_status);
1048 IF l_return_status <> WSH_UTIL_CORE.G_RET_STS_SUCCESS THEN
1049 IF l_return_status = WSH_UTIL_CORE.G_RET_STS_WARNING THEN
1050 l_num_warn := l_num_warn + 1;
1051 ELSE
1052 x_return_status := l_return_status;
1053 --
1054 -- Debug Statements
1055 --
1056 --IF l_debug_on THEN
1057 --WSH_DEBUG_SV.pop(l_module_name);
1058 --END IF;
1059 --
1060 --RETURN;
1061 raise e_return_excp; -- LPN CONV. rv
1062 END IF;
1063 END IF;
1064
1065 END IF;
1066
1067 -- J-IB-NPARIKH-{
1068 --
1069 --
1070 -- For an existing stop, calculate value of shipments type flag
1071 -- by calling WSH_TRIP_STOPS_VALIDATIONS.refreshShipmentsTypeFlag
1072 --
1073 IF l_debug_on THEN
1074 WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WWSH_TRIP_STOPS_VALIDATIONS.refreshShipmentsTypeFlag',WSH_DEBUG_SV.C_PROC_LEVEL);
1075 END IF;
1076 --
1077 WSH_TRIP_STOPS_VALIDATIONS.refreshShipmentsTypeFlag
1078 (
1079 p_trip_id => p_trip_id,
1080 p_stop_id => l_pickup_stop_id,
1081 p_action => 'ASSIGN',
1082 p_shipment_direction => l_shipment_direction,
1083 x_shipments_type_flag => l_stop_shipments_type_flag,
1084 x_return_status => l_return_status
1085 );
1086 --
1087 wsh_util_core.api_post_call
1088 (
1089 p_return_status => l_return_status,
1090 x_num_warnings => l_num_warn,
1091 x_num_errors => l_num_error
1092 );
1093 --
1094 -- J-IB-NPARIKH-}
1095
1096 /* End of H integration - call Multi Leg FTE */
1097 update wsh_trip_stops
1098 set planned_departure_date=least(nvl(l_pickup_date,sysdate),planned_departure_date),
1099 planned_arrival_date=least(nvl(l_pickup_date,sysdate),planned_arrival_date),
1100 shipments_type_flag = l_stop_shipments_type_flag , -- J-IB-NPARIKH
1101 last_update_date = SYSDATE, -- J-IB-NPARIKH
1102 last_updated_by = FND_GLOBAL.USER_ID, -- J-IB-NPARIKH
1103 last_update_login = FND_GLOBAL.LOGIN_ID -- J-IB-NPARIKH
1104 where stop_id=l_pickup_stop_id;
1105
1106 --
1107 -- DBI Project
1108 -- Updating WSH_TRIP_STOPS.
1109 -- Call DBI API after the Update.
1110 -- This API will also check for DBI Installed or not
1111 IF l_debug_on THEN
1112 WSH_DEBUG_SV.log(l_module_name,'Calling DBI API.Stop id -',l_pickup_stop_id);
1113 END IF;
1114 l_stop_tab(1) := l_pickup_stop_id;
1115 WSH_INTEGRATION.DBI_Update_Trip_Stop_Log
1116 (p_stop_id_tab => l_stop_tab,
1117 p_dml_type => 'UPDATE',
1118 x_return_status => l_dbi_rs);
1119
1120 IF l_debug_on THEN
1121 WSH_DEBUG_SV.log(l_module_name,'Return Status after DBI Call-',l_dbi_rs);
1122 END IF;
1123 IF l_dbi_rs = WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR THEN
1124 x_return_status := l_dbi_rs;
1125 rollback to assign_del_to_trip;
1126 -- just pass this return status to caller API
1127 IF l_debug_on THEN
1128 WSH_DEBUG_SV.log(l_module_name,'DBI API Returned Unexpected error '||x_return_status);
1129 --WSH_DEBUG_SV.pop(l_module_name);
1130 END IF;
1131 --return;
1132 raise e_return_excp; -- LPN CONV. rv
1133 END IF;
1134 -- End of Code for DBI Project
1135 --
1136 IF p_caller like '%ASSIGNTRIP%' THEN
1137 l_trip_ids(1):=p_trip_id;
1138 WSH_TRIPS_ACTIONS.Handle_Internal_Stops
1139 ( p_trip_ids => l_trip_ids,
1140 p_caller => p_caller,
1141 x_success_trip_ids => l_dummy_trip_ids,
1142 x_return_status => l_return_status);
1143
1144 IF l_debug_on THEN
1145 WSH_DEBUG_SV.log(l_module_name,'Handle_Internal_Stops after updating stop l_return_status',l_return_status);
1146 END IF;
1147
1148 wsh_util_core.api_post_call
1149 (
1150 p_return_status => l_return_status,
1151 x_num_warnings => l_num_warn,
1152 x_num_errors => l_num_error
1153 );
1154 END IF;
1155 END IF;
1156
1157 END IF;
1158
1159 IF (p_create_flag = 'Y')AND(p_dropoff_stop_id IS NULL) THEN
1160
1161 l_dropoff_stop_id := NULL;
1162 l_dropoff_stop_seq := NULL;
1163
1164 IF (p_dropoff_location_id IS NOT NULL) THEN
1165 l_dropoff_location_id := p_dropoff_location_id;
1166 END IF;
1167
1168 IF p_caller = 'WSH_FSTRXASSIGNTRIP' AND p_dropoff_stop_seq IS NULL THEN
1169 OPEN get_stop_new (l_dropoff_location_id,P_DROPOFF_ARR_DATE,P_DROPOFF_DEP_DATE);
1170 FETCH get_stop_new INTO l_dropoff_stop_id, l_dropoff_stop_seq,l_stop_shipments_type_flag;
1171 CLOSE get_stop_new;
1172 ELSE
1173 OPEN get_stop (l_dropoff_location_id,p_dropoff_stop_seq);
1174 FETCH get_stop INTO l_dropoff_stop_id, l_dropoff_stop_seq,l_stop_shipments_type_flag;
1175 CLOSE get_stop;
1176 END IF;
1177
1178 IF l_debug_on THEN
1179 WSH_DEBUG_SV.log(l_module_name,'l_dropoff_stop_id-'||l_dropoff_stop_id);
1180 WSH_DEBUG_SV.log(l_module_name,'l_dropoff_location_id-'||l_dropoff_location_id);
1181 WSH_DEBUG_SV.log(l_module_name,'dropoff_stop_seq-'||p_dropoff_stop_seq);
1182 WSH_DEBUG_SV.log(l_module_name,'l_stop_shipments_type_flag'||l_stop_shipments_type_flag);
1183 END IF;
1184
1185 -- bug 2784197
1186 open get_sequence(l_pickup_stop_id);
1187 fetch get_sequence into l_pickup_stop_seq;
1188 close get_sequence;
1189 IF l_debug_on THEN
1190 WSH_DEBUG_SV.log(l_module_name,'l_pickup_stop_seq',l_pickup_stop_seq);
1191 WSH_DEBUG_SV.log(l_module_name,'l_dropoff_stop_seq',l_dropoff_stop_seq);
1192 END IF;
1193 IF nvl(l_dropoff_stop_seq, 0 ) = 0
1194 OR nvl(l_dropoff_stop_seq, 0 ) < nvl(l_pickup_stop_seq,0) THEN
1195 l_dropoff_stop_id := NULL;
1196 END IF;
1197 -- bug 2784197
1198
1199 /* H integration , for stop sequence number */
1200 IF p_dropoff_location_id IS NOT NULL THEN
1201 l_new_flag := 'DROPOFF';
1202 l_dropoff_stop_seq := p_dropoff_stop_seq;
1203 ELSE
1204 l_new_flag := 'DELIVERY';
1205 l_dropoff_stop_seq := null;
1206 END IF;
1207
1208 --
1209 -- Debug Statements
1210 --
1211 --4106241 : Call the get_new_sequence_number API only if l_dropoff_stop_seq and l_dropoff_stop_id are null.
1212 IF l_debug_on THEN
1213 WSH_DEBUG_SV.log(l_module_name,'l_dropoff_stop_seq before api call is:',l_dropoff_stop_seq);
1214 END IF;
1215 IF l_dropoff_stop_seq is NULL and l_dropoff_stop_id is NULL THEN
1216
1217 IF l_pregen_seq = 'N' THEN --{
1218 IF l_debug_on THEN
1219 WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_TRIP_STOPS_VALIDATIONS.GET_NEW_SEQUENCE_NUMBER',WSH_DEBUG_SV.C_PROC_LEVEL);
1220 END IF;
1221 --
1222
1223 WSH_TRIP_STOPS_VALIDATIONS.get_new_sequence_number
1224 (x_stop_sequence_number => l_dropoff_stop_seq,
1225 p_trip_id => p_trip_id,
1226 p_status_code => 'OP',
1227 p_stop_id => null, -- as of now no validations
1228 p_new_flag => l_new_flag,
1229 x_return_status => l_return_status
1230 );
1231
1232 IF l_return_status <> WSH_UTIL_CORE.G_RET_STS_SUCCESS THEN
1233 x_return_status := l_return_status;
1234 raise e_return_excp; -- LPN CONV. rv
1235 END IF;
1236 ELSE
1237 BEGIN
1238 select column2,to_date(column1,'DD-MM-RRRR HH24:MI:SS')
1239 into l_dropoff_stop_seq,l_tmp_date
1240 from wsh_tmp
1241 where id = l_dropoff_location_id
1242 and flag = 'D';
1243 EXCEPTION
1244 WHEN NO_DATA_FOUND THEN
1245 x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
1246 raise e_return_excp; -- LPN CONV. rv
1247 WHEN OTHERS THEN
1248 x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
1249 raise e_return_excp; -- LPN CONV. rv
1250 END;
1251 END IF; --}
1252
1253 END IF;
1254 /* End of H integration , for stop sequence number */
1255
1256 IF (l_dropoff_stop_id IS NULL) THEN
1257 l_trip_stop_info.trip_id := p_trip_id;
1258 l_trip_stop_info.status_code := 'OP';
1259 --l_trip_stop_info.stop_sequence_number := -99;
1260 l_trip_stop_info.stop_sequence_number := l_dropoff_stop_seq;
1261 l_trip_stop_info.stop_location_id := l_dropoff_location_id;
1262 IF l_pregen_seq = 'N' THEN
1263 l_trip_stop_info.planned_arrival_date := greatest(NVL(l_pickup_date, sysdate) + WSH_TRIPS_ACTIONS.C_TEN_MINUTES, NVL(l_dropoff_date, sysdate));
1264 ELSE
1265 l_trip_stop_info.planned_arrival_date := l_tmp_date;
1266 END IF;
1267 -- bug 3349133, the planned arrival date of pickup stop and dropoff stop is 10 minutes apart
1268 l_trip_stop_info.planned_departure_date := l_trip_stop_info.planned_arrival_date;
1269 l_trip_stop_info.weight_uom_code := l_default_wt_uom;
1270 l_trip_stop_info.volume_uom_code := l_default_vol_uom;
1271
1272 -- Need to clear out all the w/v value from previous stop record
1273 l_trip_stop_info.departure_gross_weight := null;
1274 l_trip_stop_info.departure_net_weight := null;
1275 l_trip_stop_info.departure_volume := null;
1276
1277 -- Default the W/V from previous stop(sequence) only if the current sequence is not the last one at this point
1278 l_next_seq_exists := TRUE;
1279
1280 OPEN c_next_seq_exists(p_trip_id, l_dropoff_stop_seq);
1281 FETCH c_next_seq_exists INTO l_dummy;
1282 IF c_next_seq_exists%NOTFOUND THEN
1283 l_next_seq_exists := FALSE;
1284 END IF;
1285 CLOSE c_next_seq_exists;
1286
1287 IF l_next_seq_exists THEN
1288 l_gross_weight := NULL;
1289 l_net_weight := NULL;
1290 l_volume := NULL;
1291
1292 OPEN c_get_prev_seq_wv(p_trip_id, l_dropoff_stop_seq, l_default_wt_uom, l_default_vol_uom);
1293 FETCH c_get_prev_seq_wv into l_gross_weight,l_net_weight,l_volume;
1294 CLOSE c_get_prev_seq_wv;
1295
1296 IF l_debug_on THEN
1297 WSH_DEBUG_SV.log(l_module_name,'Prev stop Gross '||l_gross_weight||' Net '||l_net_weight||' Vol '||l_volume);
1298 END IF;
1299
1300 l_trip_stop_info.departure_gross_weight := l_gross_weight;
1301 l_trip_stop_info.departure_net_weight := l_net_weight;
1302 l_trip_stop_info.departure_volume := l_volume;
1303 END IF;
1304
1305 -- J-IB-NPARIKH-{
1306 --
1307 -- For a new stop, calculate value of shipments type flag
1308 -- using shipment direction of the delivery being assigned.
1309 --
1310 IF l_shipment_direction IN ('O','IO')
1311 THEN
1312 l_trip_stop_info.shipments_type_flag := 'O';
1313 ELSE
1314 l_trip_stop_info.shipments_type_flag := 'I';
1315 END IF;
1316 --
1317 IF l_debug_on THEN
1318 WSH_DEBUG_SV.log(l_module_name,'l_trip_stop_info.shipments_type_flag',l_trip_stop_info.shipments_type_flag);
1319 END IF;
1320 --
1321 -- J-IB-NPARIKH-}
1322
1323 IF p_dropoff_location_id IS NOT NULL THEN
1324 IF l_debug_on THEN
1325 WSH_DEBUG_SV.log(l_module_name,'updating dates for dropoff loc which us getting created',p_dropoff_location_id);
1326 END IF;
1327 l_trip_stop_info.planned_arrival_date := p_dropoff_arr_date;
1328 l_trip_stop_info.planned_departure_date := p_dropoff_dep_date;
1329 END IF;
1330
1331
1332 /* H integration - call Multi Leg FTE */
1333 IF (WSH_UTIL_CORE.FTE_IS_INSTALLED = 'Y') THEN
1334 --
1335 -- Debug Statements
1336 --
1337 IF l_debug_on THEN
1338 WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_FTE_INTEGRATION.TRIP_STOP_VALIDATIONS',WSH_DEBUG_SV.C_PROC_LEVEL);
1339 END IF;
1340 --
1341 wsh_fte_integration.trip_stop_validations
1342 (p_stop_rec => l_trip_stop_info,
1343 p_trip_rec => l_trip_rec,
1344 p_action => 'ADD',
1345 x_return_status => l_return_status);
1346
1347 IF l_return_status <> WSH_UTIL_CORE.G_RET_STS_SUCCESS THEN
1348 IF l_return_status = WSH_UTIL_CORE.G_RET_STS_WARNING THEN
1349 l_num_warn := l_num_warn + 1;
1350 ELSE
1351 x_return_status := l_return_status;
1352 --
1353 -- Debug Statements
1354 --
1355 --IF l_debug_on THEN
1356 --WSH_DEBUG_SV.pop(l_module_name);
1357 --END IF;
1358 --
1359 --RETURN;
1360 raise e_return_excp; -- LPN CONV. rv
1361 END IF;
1362 END IF;
1363
1364 END IF;
1365 /* End of H integration - call Multi Leg FTE */
1366 --
1367 -- Debug Statements
1368 --
1369 IF l_debug_on THEN
1370 WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_TRIP_STOPS_PVT.CREATE_TRIP_STOP',WSH_DEBUG_SV.C_PROC_LEVEL);
1371 END IF;
1372 --
1373 wsh_trip_stops_pvt.create_trip_stop(l_trip_stop_info,l_rowid,l_dropoff_stop_id, x_return_status);
1374
1375 IF (x_return_status <> WSH_UTIL_CORE.G_RET_STS_SUCCESS) THEN
1376 RAISE e_InvalidDelExcep; -- Bug 5336308
1377 END IF;
1378
1379 --For a Routing Firm trip, when action is assign trip, check if the new stop
1380 --created is a dummy stop which already has physical stop in trip or if the
1381 --new stop is the physical stop and dummy is already present in the trip. Only
1382 --in these 2 cases, we allow stop creation for Routing Firm trip. Else, this
1383 --delivery cannot be assigned to the trip as this entails creation of a new stop
1384 --This has to be done after create stop as linking happens only at creation time
1385 IF l_plannedflag='Y' AND p_caller like '%ASSIGNTRIP%' THEN
1386
1387 OPEN c_check_dummystops(l_dropoff_stop_id,p_trip_id);
1388 FETCH c_check_dummystops INTO l_check_dummystops;
1389 IF c_check_dummystops%NOTFOUND THEN
1390 CLOSE c_check_dummystops;
1391 IF l_debug_on THEN
1392 WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit wsh_trips_pvt.get_name',WSH_DEBUG_SV.C_PROC_LEVEL);
1393 END IF;
1394 FND_MESSAGE.SET_NAME('WSH','WSH_FIRMTRIP_NO_NEW_STOP');
1395 FND_MESSAGE.SET_TOKEN('TRIP_NAME',wsh_trips_pvt.get_name(p_trip_id));
1396 wsh_util_core.add_message(WSH_UTIL_CORE.G_RET_STS_ERROR, l_module_name);
1397 RAISE e_InvalidDelExcep; -- Bug 5336308
1398 END IF;
1399 CLOSE c_check_dummystops;
1400 END IF;
1401
1402 IF( WSH_UTIL_CORE.FTE_Is_Installed = 'Y') THEN
1403 l_trip_mode_of_transport := NVL(l_trip_mode_of_transport, l_delivery_mode_of_transport);
1404 WSH_UTIL_VALIDATE.Validate_Trip_MultiStops (
1405 p_trip_id => p_trip_id,
1406 p_mode_of_transport => l_trip_mode_of_transport,
1407 x_return_status => x_return_status);
1408 IF l_debug_on THEN
1409 WSH_DEBUG_SV.log(l_module_name,'WSH_UTIL_VALIDATE.Validate_MultiStops x_return_status',x_return_status);
1410 END IF;
1411
1412 IF x_return_status IN (WSH_UTIL_CORE.G_RET_STS_ERROR, WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR) THEN
1413 RAISE e_InvalidDelExcep; -- Bug 5336308
1414 END IF;
1415
1416 END IF;
1417 -- 3516052 keep the pickup arrival date and deaprture date
1418 l_dropoff_arr_date := l_trip_stop_info.planned_arrival_date;
1419 l_dropoff_dep_date := l_trip_stop_info.planned_departure_date;
1420 l_dropoff_stop_seq := l_trip_stop_info.stop_sequence_number; --SSN Frontport
1421
1422 ELSE
1423 -- 3516052
1424 -- Get pvt type record structure for stop
1425 --
1426 -- Debug Statements
1427 --
1428 IF l_debug_on THEN
1429 WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_TRIP_STOPS_GRP.GET_STOP_DETAILS_PVT',WSH_DEBUG_SV.C_PROC_LEVEL);
1430 END IF;
1431 --
1432 wsh_trip_stops_grp.get_stop_details_pvt
1433 (p_stop_id => l_dropoff_stop_id,
1434 x_stop_rec => l_stop_rec,
1435 x_return_status => l_return_status);
1436
1437 IF l_return_status <> WSH_UTIL_CORE.G_RET_STS_SUCCESS THEN
1438 x_return_status := l_return_status;
1439 --
1440 -- Debug Statements
1441 --
1442 --IF l_debug_on THEN
1443 --WSH_DEBUG_SV.pop(l_module_name);
1444 --END IF;
1445 --
1446 --RETURN;
1447 raise e_return_excp; -- LPN CONV. rv
1448 END IF;
1449
1450 -- 3516052 keep the dropoff arrival date and deaprture date
1451 l_dropoff_arr_date := l_stop_rec.planned_arrival_date;
1452 l_dropoff_dep_date := l_stop_rec.planned_departure_date;
1453
1454
1455 /* H integration - call Multi Leg FTE */
1456 IF (WSH_UTIL_CORE.FTE_IS_INSTALLED = 'Y') THEN
1457
1458 --
1459 -- Debug Statements
1460 --
1461 IF l_debug_on THEN
1462 WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_FTE_INTEGRATION.TRIP_STOP_VALIDATIONS',WSH_DEBUG_SV.C_PROC_LEVEL);
1463 END IF;
1464 --
1465 wsh_fte_integration.trip_stop_validations
1466 (p_stop_rec => l_stop_rec,
1467 p_trip_rec => l_trip_rec,
1468 p_action => 'UPDATE',
1469 x_return_status => l_return_status);
1470 IF l_return_status <> WSH_UTIL_CORE.G_RET_STS_SUCCESS THEN
1471 IF l_return_status = WSH_UTIL_CORE.G_RET_STS_WARNING THEN
1472 l_num_warn := l_num_warn + 1;
1473 ELSE
1474 x_return_status := l_return_status;
1475 --
1476 -- Debug Statements
1477 --
1478 --IF l_debug_on THEN
1479 --WSH_DEBUG_SV.pop(l_module_name);
1480 --END IF;
1481 --
1482 --RETURN;
1483 raise e_return_excp; -- LPN CONV. rv
1484 END IF;
1485 END IF;
1486
1487 END IF;
1488
1489 -- J-IB-NPARIKH-{
1490 --
1491 --
1492 -- For an existing stop, calculate value of shipments type flag
1493 -- by calling WSH_TRIP_STOPS_VALIDATIONS.refreshShipmentsTypeFlag
1494 --
1495 IF l_debug_on THEN
1496 WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WWSH_TRIP_STOPS_VALIDATIONS.refreshShipmentsTypeFlag',WSH_DEBUG_SV.C_PROC_LEVEL);
1497 END IF;
1498 --
1499 WSH_TRIP_STOPS_VALIDATIONS.refreshShipmentsTypeFlag
1500 (
1501 p_trip_id => p_trip_id,
1502 p_stop_id => l_dropoff_stop_id,
1503 p_action => 'ASSIGN',
1504 p_shipment_direction => l_shipment_direction,
1505 x_shipments_type_flag => l_stop_shipments_type_flag,
1506 x_return_status => l_return_status
1507 );
1508 --
1509 wsh_util_core.api_post_call
1510 (
1511 p_return_status => l_return_status,
1512 x_num_warnings => l_num_warn,
1513 x_num_errors => l_num_error
1514 );
1515 --
1516 -- J-IB-NPARIKH-}
1517
1518 /* End of H integration - call Multi Leg FTE */
1519 update wsh_trip_stops
1520 set shipments_type_flag = l_stop_shipments_type_flag , -- J-IB-NPARIKH
1521 last_update_date = SYSDATE,
1522 last_updated_by = FND_GLOBAL.USER_ID,
1523 last_update_login = FND_GLOBAL.LOGIN_ID
1524 where stop_id=l_dropoff_stop_id;
1525 END IF;
1526
1527 END IF;
1528
1529 --
1530 IF l_debug_on THEN
1531 WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_DELIVERY_VALIDATIONS.CHK_DUP_PICKUP_DROPOFF_LOCNS',WSH_DEBUG_SV.C_PROC_LEVEL);
1532 END IF;
1533 --
1534 wsh_delivery_validations.chk_dup_pickup_dropoff_locns
1535 (p_delivery_id => l_del_rows(i),
1536 p_pickup_location_id => l_pickup_location_id,
1537 p_dropoff_location_id => l_dropoff_location_id,
1538 x_return_status => l_return_status);
1539 IF l_return_status IN (wsh_util_core.g_ret_sts_error, wsh_util_core.g_ret_sts_unexp_error) THEN
1540 l_num_error := l_num_error + 1;
1541 --
1542 IF l_debug_on THEN
1543 WSH_DEBUG_SV.logmsg(l_module_name,'Rolling back to the begining of the loop',WSH_DEBUG_SV.C_PROC_LEVEL);
1544 WSH_DEBUG_SV.log(l_module_name,'l_num_error',l_num_error);
1545 END IF;
1546 --
1547 RAISE e_CommonExcep; -- Bug 5336308
1548 --
1549 END IF;
1550 --2709662
1551 IF l_debug_on THEN
1552 WSH_DEBUG_SV.log(l_module_name,'l_dropoff_stop_id', l_dropoff_stop_id);
1553 WSH_DEBUG_SV.log(l_module_name,'l_pickup_stop_id', l_pickup_stop_id);
1554 END IF;
1555
1556 IF l_pregen_seq = 'N' THEN --{
1557 --
1558 -- SSN change
1559 -- The date validation is to be triggered only if profile is PAD
1560 -- The SSN validation is to be triggered otherwise.
1561 IF (l_stop_seq_mode = WSH_INTERFACE_GRP.G_STOP_SEQ_MODE_PAD) AND
1562 (l_pickup_stop_id IS NOT NULL) AND (l_dropoff_stop_id IS NOT NULL)
1563 THEN
1564
1565 IF l_debug_on THEN
1566 WSH_DEBUG_SV.log(l_module_name,'l_pickup_arr_date', fnd_date.date_to_displaydt(l_pickup_arr_date));
1567 WSH_DEBUG_SV.log(l_module_name,'l_pickup_dep_date', fnd_date.date_to_displaydt(l_pickup_dep_date));
1568 WSH_DEBUG_SV.log(l_module_name,'l_dropoff_arr_date', fnd_date.date_to_displaydt (l_dropoff_arr_date));
1569 WSH_DEBUG_SV.log(l_module_name,'l_dropoff_dep_date', fnd_date.date_to_displaydt (l_dropoff_dep_date));
1570 END IF;
1571 -- bug 3516052
1572 -- bug 4036204: We relax the restriction so that p_pickup_dep_date = p_dropoff_arr_date
1573 -- as long as p_pickup_arr_date >= p_dropoff_arr_date
1574 IF (p_pickup_dep_date > p_dropoff_arr_date) OR
1575 ((p_pickup_dep_date = p_dropoff_arr_date) AND (p_pickup_arr_date >= p_dropoff_arr_date))
1576 THEN
1577 FND_MESSAGE.Set_Name('WSH', 'WSH_INVALID_PLANNED_DATE');
1578 FND_MESSAGE.SET_TOKEN('PICKUP_DATE', fnd_date.date_to_displaydt(l_pickup_dep_date));
1579 FND_MESSAGE.SET_TOKEN('DROPOFF_DATE', fnd_date.date_to_displaydt(l_dropoff_arr_date));
1580 wsh_util_core.add_message(WSH_UTIL_CORE.G_RET_STS_ERROR, l_module_name);
1581 l_num_error := l_num_error + 1;
1582 RAISE e_CommonExcep; -- Bug 5336308
1583 END IF;
1584
1585 ELSIF (l_stop_seq_mode = WSH_INTERFACE_GRP.G_STOP_SEQ_MODE_SSN) AND
1586 (l_pickup_stop_id IS NOT NULL) AND (l_dropoff_stop_id IS NOT NULL) THEN
1587
1588 -- check that pick up SSN is before drop off SSN.
1589
1590 IF l_debug_on THEN
1591 WSH_DEBUG_SV.log(l_module_name,'l_pickup_stop_seq', l_pickup_stop_seq);
1592 WSH_DEBUG_SV.log(l_module_name,'l_dropoff_stop_seq', l_dropoff_stop_seq);
1593 END IF;
1594
1595 IF l_pickup_stop_seq >= l_dropoff_stop_seq THEN
1596 FND_MESSAGE.Set_Name('WSH', 'WSH_PICKUP_STOP_SEQUENCE');
1597 wsh_util_core.add_message(WSH_UTIL_CORE.G_RET_STS_ERROR, l_module_name);
1598 l_num_error := l_num_error + 1;
1599 RAISE e_CommonExcep; -- Bug 5336308
1600 END IF;
1601
1602 END IF; -- end of mode check and date/SSN validation
1603 END IF;
1604
1605 -- If a delivery leg exists then only update is possible. If it does
1606 -- not exist create a new delivery leg
1607
1608 IF (l_leg_id IS NOT NULL) THEN
1609
1610 --TD: Check if delivery can be unassigned from old trip stops
1611 --TD: and assigned to new trip stops
1612
1613 IF (l_pickup_stop_id IS NOT NULL) THEN
1614
1615 update wsh_delivery_legs
1616 set pick_up_stop_id = l_pickup_stop_id
1617 where delivery_leg_id = l_leg_id;
1618
1619 IF (SQL%NOTFOUND) THEN
1620 FND_MESSAGE.SET_NAME('WSH','WSH_LEG_NOT_FOUND');
1621 x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
1622 wsh_util_core.add_message(x_return_status);
1623 RAISE e_CommonExcep; -- Bug 5336308
1624 END IF;
1625
1626 --
1627 -- DBI Project
1628 -- Updating WSH_TRIP_STOPS.
1629 -- Call DBI API after the Update.
1630 -- This API will also check for DBI Installed or not
1631 IF l_debug_on THEN
1632 WSH_DEBUG_SV.log(l_module_name,'Calling DBI API.Stop id -',l_pickup_stop_id);
1633 END IF;
1634 l_stop_tab.delete;
1635 l_stop_tab(1) := l_pickup_stop_id;
1636 WSH_INTEGRATION.DBI_Update_Trip_Stop_Log
1637 (p_stop_id_tab => l_stop_tab,
1638 p_dml_type => 'UPDATE',
1639 x_return_status => l_dbi_rs);
1640
1641 IF l_debug_on THEN
1642 WSH_DEBUG_SV.log(l_module_name,'Return Status after DBI Call-',l_dbi_rs);
1643 END IF;
1644 IF l_dbi_rs = WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR THEN
1645 x_return_status := l_dbi_rs;
1646 rollback to assign_del_to_trip;
1647 -- just pass this return status to caller API
1648 IF l_debug_on THEN
1649 WSH_DEBUG_SV.log(l_module_name,'DBI API Returned Unexpected error '||x_return_status);
1650 --WSH_DEBUG_SV.pop(l_module_name);
1651 END IF;
1652 --return;
1653 raise e_return_excp; -- LPN CONV. rv
1654 END IF;
1655 -- End of Code for DBI Project
1656 --
1657
1658
1659 END IF;
1660
1661 IF (l_dropoff_stop_id IS NOT NULL) THEN
1662
1663 update wsh_delivery_legs
1664 set drop_off_stop_id = l_dropoff_stop_id
1665 where delivery_leg_id = l_leg_id;
1666
1667 IF (SQL%NOTFOUND) THEN
1668 FND_MESSAGE.SET_NAME('WSH','WSH_LEG_NOT_FOUND');
1669 x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
1670 wsh_util_core.add_message(x_return_status);
1671 RAISE e_CommonExcep; -- Bug 5336308
1672 END IF;
1673
1674 --
1675 -- DBI Project
1676 -- Updating WSH_TRIP_STOPS.
1677 -- Call DBI API after the Update.
1678 -- This API will also check for DBI Installed or not
1679 IF l_debug_on THEN
1680 WSH_DEBUG_SV.log(l_module_name,'Calling DBI API.Stop id -',l_dropoff_stop_id);
1681 END IF;
1682 l_stop_tab.delete;
1683 l_stop_tab(1) := l_dropoff_stop_id;
1684 WSH_INTEGRATION.DBI_Update_Trip_Stop_Log
1685 (p_stop_id_tab => l_stop_tab,
1686 p_dml_type => 'UPDATE',
1687 x_return_status => l_dbi_rs);
1688
1689 IF l_debug_on THEN
1690 WSH_DEBUG_SV.log(l_module_name,'Return Status after DBI Call-',l_dbi_rs);
1691 END IF;
1692 IF l_dbi_rs = WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR THEN
1693 x_return_status := l_dbi_rs;
1694 rollback to assign_del_to_trip;
1695 -- just pass this return status to caller API
1696 IF l_debug_on THEN
1697 WSH_DEBUG_SV.log(l_module_name,'DBI API Returned Unexpected error '||x_return_status);
1698 --WSH_DEBUG_SV.pop(l_module_name);
1699 END IF;
1700 --return;
1701 raise e_return_excp; -- LPN CONV. rv
1702 END IF;
1703 -- End of Code for DBI Project
1704 --
1705
1706
1707 END IF;
1708
1709
1710 ELSE
1711
1712 l_delivery_leg_info.delivery_id := l_del_rows(i);
1713 l_delivery_leg_info.pick_up_stop_id := l_pickup_stop_id;
1714 l_delivery_leg_info.drop_off_stop_id := l_dropoff_stop_id;
1715
1716
1717 OPEN max_leg_seq_number (l_del_rows(i));
1718 FETCH max_leg_seq_number INTO l_leg_seq_number;
1719 CLOSE max_leg_seq_number;
1720
1721 IF (l_leg_seq_number IS NULL) THEN
1722 l_delivery_leg_info.sequence_number := 10;
1723 ELSE
1724 l_delivery_leg_info.sequence_number := l_leg_seq_number + 10;
1725 END IF;
1726
1727 -- dbms_output.put_line('creating delivery leg....');
1728
1729 --
1730 -- Debug Statements
1731 --
1732 IF l_debug_on THEN
1733 WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_DELIVERY_LEGS_PVT.CREATE_DELIVERY_LEG',WSH_DEBUG_SV.C_PROC_LEVEL);
1734 END IF;
1735 --
1736 wsh_delivery_legs_pvt.create_delivery_leg(l_delivery_leg_info, l_rowid, l_leg_id, x_return_status);
1737
1738 IF (x_return_status <> WSH_UTIL_CORE.G_RET_STS_SUCCESS) THEN
1739 RAISE e_InvalidDelExcep; -- Bug 5336308
1740 END IF;
1741
1742 -- J: W/V Changes
1743 WSH_WV_UTILS.Del_WV_Post_Process(
1744 p_delivery_id => l_del_rows(i),
1745 p_diff_gross_wt => l_org_gross_wt,
1746 p_diff_net_wt => l_org_net_wt,
1747 p_diff_volume => l_org_vol,
1748 p_leg_id => l_leg_id,
1749 x_return_status => l_return_status);
1750
1751 IF (l_return_status <> WSH_UTIL_CORE.G_RET_STS_SUCCESS) THEN
1752 RAISE e_InvalidDelExcep; -- Bug 5336308
1753 END IF;
1754
1755 -- dbms_output.put_line('created delivery leg#'||l_leg_id);
1756
1757 END IF;
1758
1759 x_leg_rows(x_leg_rows.count+1) := l_leg_id;
1760
1761 -- J-IB-NPARIKH-{
1762 --
1763 IF p_pickup_stop_id IS NOT NULL
1764 THEN
1765 --{
1766 IF l_debug_on THEN
1767 WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WWSH_TRIP_STOPS_VALIDATIONS.refreshShipmentsTypeFlag',WSH_DEBUG_SV.C_PROC_LEVEL);
1768 END IF;
1769 --
1770 --
1771 -- For an existing stop, calculate value of shipments type flag
1772 -- by calling WSH_TRIP_STOPS_VALIDATIONS.refreshShipmentsTypeFlag
1773 --
1774 WSH_TRIP_STOPS_VALIDATIONS.refreshShipmentsTypeFlag
1775 (
1776 p_trip_id => p_trip_id,
1777 p_stop_id => p_pickup_stop_id,
1778 p_action => 'ASSIGN',
1779 p_shipment_direction => l_shipment_direction,
1780 x_shipments_type_flag => l_pu_stop_shipments_type_flag,
1781 x_return_status => l_return_status
1782 );
1783 --
1784 wsh_util_core.api_post_call
1785 (
1786 p_return_status => l_return_status,
1787 x_num_warnings => l_num_warn,
1788 x_num_errors => l_num_error
1789 );
1790
1791 IF p_pickup_location_id IS NOT NULL THEN
1792 update wsh_trip_stops
1793 set planned_departure_date=p_pickup_dep_date,
1794 planned_arrival_date=p_pickup_arr_date,
1795 last_update_date = SYSDATE,
1796 last_updated_by = FND_GLOBAL.USER_ID,
1797 last_update_login = FND_GLOBAL.LOGIN_ID
1798 where stop_id=p_pickup_stop_id;
1799
1800 --
1801 -- DBI Project
1802 -- Updating WSH_TRIP_STOPS.
1803 -- Call DBI API after the Update.
1804 -- This API will also check for DBI Installed or not
1805 IF l_debug_on THEN
1806 WSH_DEBUG_SV.log(l_module_name,'Calling DBI API.Stop id -',p_pickup_stop_id);
1807 END IF;
1808 l_stop_tab.delete;
1809 l_stop_tab(1) := p_pickup_stop_id;
1810 WSH_INTEGRATION.DBI_Update_Trip_Stop_Log
1811 (p_stop_id_tab => l_stop_tab,
1812 p_dml_type => 'UPDATE',
1813 x_return_status => l_dbi_rs);
1814
1815 IF l_debug_on THEN
1816 WSH_DEBUG_SV.log(l_module_name,'Return Status after DBI Call-',l_dbi_rs);
1817 END IF;
1818 IF l_dbi_rs = WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR THEN
1819 x_return_status := l_dbi_rs;
1820 rollback to assign_del_to_trip;
1821 -- just pass this return status to caller API
1822 IF l_debug_on THEN
1823 WSH_DEBUG_SV.log(l_module_name,'DBI API Returned Unexpected error '||l_dbi_rs);
1824 --WSH_DEBUG_SV.pop(l_module_name);
1825 END IF;
1826 --return;
1827 raise e_return_excp; -- LPN CONV. rv
1828 END IF;
1829 -- End of Code for DBI Project
1830 --
1831
1832 END IF;
1833 --}
1834 END IF;
1835 --
1836 IF p_dropoff_stop_id IS NOT NULL
1837 THEN
1838 --{
1839 IF l_debug_on THEN
1840 WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WWSH_TRIP_STOPS_VALIDATIONS.refreshShipmentsTypeFlag',WSH_DEBUG_SV.C_PROC_LEVEL);
1841 END IF;
1842 --
1843 --
1844 -- For an existing stop, calculate value of shipments type flag
1845 -- by calling WSH_TRIP_STOPS_VALIDATIONS.refreshShipmentsTypeFlag
1846 --
1847 WSH_TRIP_STOPS_VALIDATIONS.refreshShipmentsTypeFlag
1848 (
1849 p_trip_id => p_trip_id,
1850 p_stop_id => p_dropoff_stop_id,
1851 p_action => 'ASSIGN',
1852 p_shipment_direction => l_shipment_direction,
1853 x_shipments_type_flag => l_do_stop_shipments_type_flag,
1854 x_return_status => l_return_status
1855 );
1856 --
1857 wsh_util_core.api_post_call
1858 (
1859 p_return_status => l_return_status,
1860 x_num_warnings => l_num_warn,
1861 x_num_errors => l_num_error
1862 );
1863 --
1864 --
1865 IF l_debug_on THEN
1866 WSH_DEBUG_SV.log(l_module_name,'l_do_stop_shipments_type_flag',l_do_stop_shipments_type_flag);
1867 END IF;
1868 IF p_dropoff_location_id IS NOT NULL THEN
1869 update wsh_trip_stops
1870 set planned_departure_date=p_dropoff_dep_date,
1871 planned_arrival_date=p_dropoff_arr_date,
1872 last_update_date = SYSDATE,
1873 last_updated_by = FND_GLOBAL.USER_ID,
1874 last_update_login = FND_GLOBAL.LOGIN_ID
1875 where stop_id=p_dropoff_stop_id;
1876
1877 --
1878 -- DBI Project
1879 -- Updating WSH_TRIP_STOPS.
1880 -- Call DBI API after the Update.
1881 -- This API will also check for DBI Installed or not
1882 IF l_debug_on THEN
1883 WSH_DEBUG_SV.log(l_module_name,'Calling DBI API.Stop id -',p_dropoff_stop_id);
1884 END IF;
1885 l_stop_tab.delete;
1886 l_stop_tab(1) := p_dropoff_stop_id;
1887 WSH_INTEGRATION.DBI_Update_Trip_Stop_Log
1888 (p_stop_id_tab => l_stop_tab,
1889 p_dml_type => 'UPDATE',
1890 x_return_status => l_dbi_rs);
1891
1892 IF l_debug_on THEN
1893 WSH_DEBUG_SV.log(l_module_name,'Return Status after DBI Call-',l_dbi_rs);
1894 END IF;
1895 IF l_dbi_rs = WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR THEN
1896 x_return_status := l_dbi_rs;
1897 rollback to assign_del_to_trip;
1898 -- just pass this return status to caller API
1899 IF l_debug_on THEN
1900 WSH_DEBUG_SV.log(l_module_name,'DBI API Returned Unexpected error '||x_return_status);
1901 --WSH_DEBUG_SV.pop(l_module_name);
1902 END IF;
1903 --return;
1904 raise e_return_excp; -- LPN CONV. rv
1905 END IF;
1906 -- End of Code for DBI Project
1907 --
1908
1909 END IF;
1910 --}
1911 END IF;
1912 --
1913 -- J-IB-NPARIKH-}
1914
1915
1916 -- Bug 3584924: Call WMS if this is the first leg of the delivery.
1917
1918 IF NOT g_wms_installed.exists(l_org_id) THEN
1919 g_wms_installed(l_org_id) := wsh_util_validate.check_wms_org(l_org_id);
1920 END IF;
1921
1922 IF g_wms_installed(l_org_id) = 'Y' THEN
1923 -- Check if it is the first leg of the delivery.
1924 l_dummy_leg_id := NULL;
1925 OPEN C_IS_FIRST_LEG(p_trip_id, l_del_rows(i), l_del_pu_location_id);
1926 FETCH C_IS_FIRST_LEG INTO l_dummy_leg_id;
1927 IF C_IS_FIRST_LEG%NOTFOUND THEN
1928 l_dummy_leg_id := NULL;
1929 END IF;
1930 CLOSE C_IS_FIRST_LEG;
1931 IF l_dummy_leg_id IS NOT NULL THEN
1932 -- Call wms to check if assignment is valid
1933 l_dlvy_trip_tbl(1).delivery_id := l_del_rows(i);
1934 l_dlvy_trip_tbl(1).trip_id := p_trip_id;
1935 IF l_debug_on THEN
1936 WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WMS_SHIPPING_INTERFACE_GRP.Process_Delivery_Trip',WSH_DEBUG_SV.C_PROC_LEVEL);
1937 WSH_DEBUG_SV.log(l_module_name,'trip_id',p_trip_id);
1938 WSH_DEBUG_SV.log(l_module_name,'del_id',l_del_rows(i));
1939 END IF;
1940 WMS_SHIPPING_INTERFACE_GRP.Process_Delivery_Trip(
1941 p_api_version => 1.0,
1942 p_init_msg_list => wms_shipping_interface_grp.g_false,
1943 p_commit => wms_shipping_interface_grp.g_false,
1944 p_validation_level => wms_shipping_interface_grp.g_full_validation,
1945 p_action => wms_shipping_interface_grp.g_action_assign_dlvy_trip,
1946 p_dlvy_trip_tbl => l_dlvy_trip_tbl,
1947 x_return_status => l_return_status,
1948 x_msg_count => l_msg_count,
1949 x_msg_data => l_msg_data);
1950
1951 IF l_dlvy_trip_tbl(1).r_message_type IN (WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR, WSH_UTIL_CORE.G_RET_STS_ERROR)
1952 OR l_return_status IN (WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR, WSH_UTIL_CORE.G_RET_STS_ERROR ) THEN
1953 FND_MESSAGE.SET_NAME(l_dlvy_trip_tbl(1).r_message_appl,l_dlvy_trip_tbl(1).r_message_code);
1954 IF l_dlvy_trip_tbl(1).r_message_token IS NOT NULL THEN
1955 FND_MESSAGE.SET_TOKEN(l_dlvy_trip_tbl(1).r_message_token_name, l_dlvy_trip_tbl(1).r_message_token);
1956 END IF;
1957 WSH_UTIL_CORE.ADD_MESSAGE(l_dlvy_trip_tbl(1).r_message_type);
1958 RAISE e_InvalidDelExcep; -- Bug 5336308
1959 END IF;
1960 END IF;
1961
1962 END IF;
1963 --
1964 EXCEPTION
1965 --
1966 -- Bug 5336308 : Added these exception handlers as replacement for GOTOs
1967 --
1968 WHEN e_InvalidDelExcep THEN
1969 --
1970 ROLLBACK TO ASSIGN_DEL_TO_TRIP;
1971 FND_MESSAGE.SET_NAME('WSH','WSH_DEL_ASSIGN_ERROR');
1972 IF l_debug_on THEN
1973 WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_NEW_DELIVERIES_PVT.GET_NAME',WSH_DEBUG_SV.C_PROC_LEVEL);
1974 END IF;
1975 --
1976 FND_MESSAGE.SET_TOKEN('DEL_NAME',wsh_new_deliveries_pvt.get_name(l_del_rows(i)));
1977 -- Bug 3439165
1978 FND_MESSAGE.SET_TOKEN('TRIP_NAME',wsh_trips_pvt.get_name(p_trip_id));
1979 wsh_util_core.add_message(WSH_UTIL_CORE.G_RET_STS_ERROR);
1980 l_num_error := l_num_error + 1;
1981 --
1982 WHEN e_CommonExcep THEN
1983 --
1984 ROLLBACK TO ASSIGN_DEL_TO_TRIP;
1985 --)
1986 END;
1987 --)
1988 END LOOP;
1989
1990 IF p_caller like '%ASSIGNTRIP%' THEN
1991 l_trip_ids(1):=p_trip_id;
1992 WSH_TRIPS_ACTIONS.Handle_Internal_Stops
1993 ( p_trip_ids => l_trip_ids,
1994 p_caller => p_caller,
1995 x_success_trip_ids => l_dummy_trip_ids,
1996 x_return_status => l_return_status);
1997
1998 IF l_debug_on THEN
1999 WSH_DEBUG_SV.log(l_module_name,'Handle_Internal_Stops l_return_status',l_return_status);
2000 END IF;
2001
2002 wsh_util_core.api_post_call
2003 (
2004 p_return_status => l_return_status,
2005 x_num_warnings => l_num_warn,
2006 x_num_errors => l_num_error
2007 );
2008 END IF;
2009
2010 -- J-IB-NPARIKH-{
2011 --
2012 IF p_pickup_stop_id IS NOT NULL
2013 AND l_pu_stop_shipments_type_flag <> l_pu_stop_shipType_flag_orig
2014 THEN
2015 --{
2016 -- If shipments type flag has changed due to assignment
2017 -- of delivery, update pickup stop with new value.
2018 --
2019 /* H integration - call Multi Leg FTE */
2020 IF (WSH_UTIL_CORE.FTE_IS_INSTALLED = 'Y') THEN
2021 -- Get pvt type record structure for stop
2022 --
2023 -- Debug Statements
2024 --
2025 IF l_debug_on THEN
2026 WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_TRIP_STOPS_GRP.GET_STOP_DETAILS_PVT',WSH_DEBUG_SV.C_PROC_LEVEL);
2027 END IF;
2028 --
2029 wsh_trip_stops_grp.get_stop_details_pvt
2030 (p_stop_id => p_pickup_stop_id,
2031 x_stop_rec => l_stop_rec,
2032 x_return_status => l_return_status);
2033
2034 IF l_return_status <> WSH_UTIL_CORE.G_RET_STS_SUCCESS THEN
2035 x_return_status := l_return_status;
2036 --
2037 -- Debug Statements
2038 --
2039 --IF l_debug_on THEN
2040 --WSH_DEBUG_SV.pop(l_module_name);
2041 --END IF;
2042 --
2043 --RETURN;
2044 raise e_return_excp; -- LPN CONV. rv
2045 END IF;
2046
2047 --
2048 -- Debug Statements
2049 --
2050 IF l_debug_on THEN
2051 WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_FTE_INTEGRATION.TRIP_STOP_VALIDATIONS',WSH_DEBUG_SV.C_PROC_LEVEL);
2052 END IF;
2053 --
2054 wsh_fte_integration.trip_stop_validations
2055 (p_stop_rec => l_stop_rec,
2056 p_trip_rec => l_trip_rec,
2057 p_action => 'UPDATE',
2058 x_return_status => l_return_status);
2059 IF l_return_status <> WSH_UTIL_CORE.G_RET_STS_SUCCESS THEN
2060 IF l_return_status = WSH_UTIL_CORE.G_RET_STS_WARNING THEN
2061 l_num_warn := l_num_warn + 1;
2062 ELSE
2063 x_return_status := l_return_status;
2064 --
2065 -- Debug Statements
2066 --
2067 --IF l_debug_on THEN
2068 --WSH_DEBUG_SV.pop(l_module_name);
2069 --END IF;
2070 --
2071 --RETURN;
2072 raise e_return_excp; -- LPN CONV. rv
2073 END IF;
2074 END IF;
2075
2076 END IF;
2077
2078
2079 IF l_debug_on THEN
2080 WSH_DEBUG_SV.log(l_module_name,'l_pu_stop_shipments_type_flag',l_pu_stop_shipments_type_flag);
2081 WSH_DEBUG_SV.log(l_module_name,'l_pu_stop_shipType_flag_orig',l_pu_stop_shipType_flag_orig);
2082 END IF;
2083 --
2084 -- J-IB-NPARIKH-}
2085
2086 /* End of H integration - call Multi Leg FTE */
2087 update wsh_trip_stops
2088 set shipments_type_flag = l_pu_stop_shipments_type_flag, -- J-IB-NPARIKH
2089 last_update_date = SYSDATE,
2090 last_updated_by = FND_GLOBAL.USER_ID,
2091 last_update_login = FND_GLOBAL.LOGIN_ID
2092 where stop_id=l_pickup_stop_id;
2093 --}
2094 END IF;
2095 --
2096 --
2097 IF p_dropoff_stop_id IS NOT NULL
2098 AND l_do_stop_shipments_type_flag <> l_do_stop_shipType_flag_orig
2099 THEN
2100 --{
2101 -- If shipments type flag has changed due to assignment
2102 -- of delivery, update dropoff stop with new value.
2103 --
2104 /* H integration - call Multi Leg FTE */
2105 IF (WSH_UTIL_CORE.FTE_IS_INSTALLED = 'Y') THEN
2106 -- Get pvt type record structure for stop
2107 --
2108 -- Debug Statements
2109 --
2110 IF l_debug_on THEN
2111 WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_TRIP_STOPS_GRP.GET_STOP_DETAILS_PVT',WSH_DEBUG_SV.C_PROC_LEVEL);
2112 END IF;
2113 --
2114 wsh_trip_stops_grp.get_stop_details_pvt
2115 (p_stop_id => p_dropoff_stop_id,
2116 x_stop_rec => l_stop_rec,
2117 x_return_status => l_return_status);
2118
2119 IF l_return_status <> WSH_UTIL_CORE.G_RET_STS_SUCCESS THEN
2120 x_return_status := l_return_status;
2121 --
2122 -- Debug Statements
2123 --
2124 --IF l_debug_on THEN
2125 --WSH_DEBUG_SV.pop(l_module_name);
2126 --END IF;
2127 --
2128 --RETURN;
2129 raise e_return_excp; -- LPN CONV. rv
2130 END IF;
2131
2132 --
2133 -- Debug Statements
2134 --
2135 IF l_debug_on THEN
2136 WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_FTE_INTEGRATION.TRIP_STOP_VALIDATIONS',WSH_DEBUG_SV.C_PROC_LEVEL);
2137 END IF;
2138 --
2139 wsh_fte_integration.trip_stop_validations
2140 (p_stop_rec => l_stop_rec,
2141 p_trip_rec => l_trip_rec,
2142 p_action => 'UPDATE',
2143 x_return_status => l_return_status);
2144 IF l_return_status <> WSH_UTIL_CORE.G_RET_STS_SUCCESS THEN
2145 IF l_return_status = WSH_UTIL_CORE.G_RET_STS_WARNING THEN
2146 l_num_warn := l_num_warn + 1;
2147 ELSE
2148 x_return_status := l_return_status;
2149 --
2150 -- Debug Statements
2151 --
2152 --IF l_debug_on THEN
2153 --WSH_DEBUG_SV.pop(l_module_name);
2154 --END IF;
2155 --
2156 --RETURN;
2157 raise e_return_excp; -- LPN CONV. rv
2158 END IF;
2159 END IF;
2160
2161 END IF;
2162
2163
2164 IF l_debug_on THEN
2165 WSH_DEBUG_SV.log(l_module_name,'l_do_stop_shipments_type_flag',l_do_stop_shipments_type_flag);
2166 WSH_DEBUG_SV.log(l_module_name,'l_do_stop_shipType_flag_orig',l_do_stop_shipType_flag_orig);
2167 END IF;
2168 --
2169
2170 /* End of H integration - call Multi Leg FTE */
2171 update wsh_trip_stops
2172 set shipments_type_flag = l_do_stop_shipments_type_flag, -- J-IB-NPARIKH
2173 last_update_date = SYSDATE,
2174 last_updated_by = FND_GLOBAL.USER_ID,
2175 last_update_login = FND_GLOBAL.LOGIN_ID
2176 where stop_id=l_dropoff_stop_id;
2177 --}
2178 END IF;
2179 --
2180 --
2181 --
2182 -- J-IB-NPARIKH-{
2183 --
2184 -- Determine new value of shipments type flag for the trip
2185 -- as a result of assignment operation
2186 --
2187 IF l_trip_shipments_type_flag IN ('I','O')
2188 THEN
2189 --{
2190 --
2191 -- If trip already has mixed deliveries, no further updates are required.
2192 -- Hence, this code is restricted only to trip shipments type flag I/O
2193 --
2194 IF l_debug_on THEN
2195 WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_TRIP_VALIDATIONS.has_mixed_deliveries',WSH_DEBUG_SV.C_PROC_LEVEL);
2196 END IF;
2197 --
2198 l_has_mixed_deliveries := WSH_TRIP_VALIDATIONS.has_mixed_deliveries
2199 (
2200 p_trip_id => p_trip_id
2201 );
2202 --
2203 IF l_debug_on THEN
2204 WSH_DEBUG_SV.log(l_module_name,'l_has_mixed_deliveries',l_has_mixed_deliveries);
2205 END IF;
2206 --
2207 --
2208 IF l_has_mixed_deliveries = 'Y'
2209 THEN
2210 l_trip_shipments_type_flag_new := 'M';
2211 ELSIF l_has_mixed_deliveries = 'NI'
2212 THEN
2213 l_trip_shipments_type_flag_new := 'I';
2214 ELSIF l_has_mixed_deliveries = 'NO'
2215 THEN
2216 l_trip_shipments_type_flag_new := 'O';
2217 ELSE
2218 l_trip_shipments_type_flag_new := l_trip_shipments_type_flag;
2219 END IF;
2220 --
2221 IF l_debug_on THEN
2222 WSH_DEBUG_SV.log(l_module_name,'l_trip_shipments_type_flag',l_trip_shipments_type_flag);
2223 WSH_DEBUG_SV.log(l_module_name,'l_trip_shipments_type_flag_new',l_trip_shipments_type_flag_new);
2224 END IF;
2225 --
2226 --
2227 --
2228 --IF l_has_mixed_deliveries = 'Y'
2229 IF l_trip_shipments_type_flag_new <> l_trip_shipments_type_flag
2230 THEN
2231 UPDATE WSH_TRIPS
2232 SET shipments_type_flag = l_trip_shipments_type_flag_new, -- 'M',
2233 last_update_date = SYSDATE,
2234 last_updated_by = FND_GLOBAL.USER_ID,
2235 last_update_login = FND_GLOBAL.LOGIN_ID
2236 WHERE trip_id = p_trip_id;
2237
2238 IF (l_trip_shipments_type_flag_new <> 'M') THEN
2239
2240 UPDATE WSH_TRIP_STOPS
2241 SET shipments_type_flag = l_trip_shipments_type_flag_new,
2242 last_update_date = SYSDATE,
2243 last_updated_by = FND_GLOBAL.USER_ID,
2244 last_update_login = FND_GLOBAL.LOGIN_ID
2245 WHERE trip_id = p_trip_id
2246 and shipments_type_flag <> l_trip_shipments_type_flag_new;
2247
2248 IF l_debug_on THEN
2249 WSH_DEBUG_SV.log(l_module_name,'SQL%ROWCOUNT After updating wts',SQL%ROWCOUNT);
2250 END IF;
2251
2252 END IF;
2253 END IF;
2254 --}
2255 END IF;
2256 --
2257 --
2258 -- J-IB-NPARIKH-}
2259
2260
2261 Mark_Reprice_Required(
2262 p_entity_type => 'DELIVERY_LEG',
2263 p_entity_ids => x_leg_rows,
2264 x_return_status => l_return_status);
2265
2266 IF l_debug_on THEN
2267 WSH_DEBUG_SV.log(l_module_name,'l_return_status',l_return_status);
2268 END IF;
2269
2270 IF l_return_status IN (WSH_UTIL_CORE.G_RET_STS_ERROR,WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR) THEN
2271 raise mark_reprice_error;
2272 ELSIF l_return_status = WSH_UTIL_CORE.G_RET_STS_WARNING THEN
2273 l_num_warn := l_num_warn + 1;
2274 END IF;
2275
2276 --
2277 --
2278
2279 --J-IB-HEALI {
2280 FOR i IN 1..p_del_rows.count LOOP
2281 IF l_debug_on THEN
2282 WSH_DEBUG_SV.log(l_module_name,'delivery_id',p_del_rows(i));
2283 END IF;
2284
2285 WSH_NEW_DELIVERY_ACTIONS.Process_Leg_Sequence
2286 ( p_delivery_id => p_del_rows(i),
2287 p_update_del_flag => 'Y',
2288 p_update_leg_flag => 'N',
2289 x_leg_complete => l_leg_complete,
2290 x_return_status => l_return_status);
2291
2292 IF l_debug_on THEN
2293 WSH_DEBUG_SV.log(l_module_name,'Process_Leg_Sequence l_return_status',l_return_status);
2294 END IF;
2295
2296 wsh_util_core.api_post_call
2297 (
2298 p_return_status => l_return_status,
2299 x_num_warnings => l_num_warn,
2300 x_num_errors => l_num_error
2301 );
2302 END LOOP;
2303 --J-IB-HEALI }
2304
2305 --
2306 -- K LPN CONV. rv
2307 --
2308 IF WSH_WMS_LPN_GRP.G_CALLBACK_REQUIRED = 'Y'
2309 THEN
2310 --{
2311 IF l_debug_on THEN
2312 WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_LPN_SYNC_COMM_PKG.SYNC_LPNS_TO_WMS',WSH_DEBUG_SV.C_PROC_LEVEL);
2313 END IF;
2314
2315 WSH_LPN_SYNC_COMM_PKG.SYNC_LPNS_TO_WMS
2316 (
2317 p_in_rec => l_lpn_in_sync_comm_rec,
2318 x_return_status => l_return_status,
2319 x_out_rec => l_lpn_out_sync_comm_rec
2320 );
2321 --
2322 --
2323 IF l_debug_on THEN
2324 WSH_DEBUG_SV.log(l_module_name,'Return status after calling WSH_LPN_SYNC_COMM_PKG.SYNC_LPNS_TO_WMS',l_return_status);
2325 END IF;
2326 --
2327 --
2328 WSH_UTIL_CORE.API_POST_CALL
2329 (
2330 p_return_status => l_return_status,
2331 x_num_warnings => l_num_warn,
2332 x_num_errors => l_num_error,
2333 p_raise_error_flag => false
2334 );
2335 --}
2336 END IF;
2337 --
2338 -- K LPN CONV. rv
2339 --
2340 IF (l_num_error > 0) THEN
2341 IF (p_del_rows.count > 1) THEN
2342
2343 FND_MESSAGE.SET_NAME('WSH','WSH_DEL_ASSIGN_SUMMARY');
2344 FND_MESSAGE.SET_TOKEN('NUM_ERROR',l_num_error);
2345 FND_MESSAGE.SET_TOKEN('NUM_SUCCESS',p_del_rows.count-l_num_error);
2346 FND_MESSAGE.SET_TOKEN('TRIP_NAME',wsh_trips_pvt.get_name(p_trip_id));
2347
2348 IF (l_num_error = p_del_rows.count) THEN
2349 x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
2350 ELSE
2351 x_return_status := WSH_UTIL_CORE.G_RET_STS_WARNING;
2352 END IF;
2353
2354 wsh_util_core.add_message(x_return_status);
2355 ELSE
2356 x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
2357 END IF;
2358
2359 ELSE
2360 /* H integration more changes */
2361 IF l_num_warn > 0 THEN
2362 x_return_status := WSH_UTIL_CORE.G_RET_STS_WARNING;
2363 ELSE
2364 x_return_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
2365 END IF;
2366 END IF;
2367
2368
2369
2370 --
2371 -- Debug Statements
2372 --
2373 IF l_debug_on THEN
2374 WSH_DEBUG_SV.log(l_module_name,'X_LEG_ROWS.COUNT'||x_leg_rows.count);
2375 WSH_DEBUG_SV.pop(l_module_name);
2376 END IF;
2377 --
2378 EXCEPTION
2379 WHEN e_return_excp THEN
2380
2381 --
2382 -- K LPN CONV. rv
2383 --
2384 IF WSH_WMS_LPN_GRP.G_CALLBACK_REQUIRED = 'Y'
2385 THEN
2386 --{
2387 IF l_debug_on THEN
2388 WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_LPN_SYNC_COMM_PKG.SYNC_LPNS_TO_WMS',WSH_DEBUG_SV.C_PROC_LEVEL);
2389 END IF;
2390
2391 WSH_LPN_SYNC_COMM_PKG.SYNC_LPNS_TO_WMS
2392 (
2393 p_in_rec => l_lpn_in_sync_comm_rec,
2394 x_return_status => l_return_status,
2395 x_out_rec => l_lpn_out_sync_comm_rec
2396 );
2397 --
2398 --
2399 IF l_debug_on THEN
2400 WSH_DEBUG_SV.log(l_module_name,'Return status after calling WSH_LPN_SYNC_COMM_PKG.SYNC_LPNS_TO_WMS',l_return_status);
2401 END IF;
2402 --
2403 --
2404 IF (l_return_status IN (WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR,WSH_UTIL_CORE.G_RET_STS_ERROR) and x_return_status <> WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR) THEN
2405 x_return_status := l_return_status;
2406 END IF;
2407 --
2408 --}
2409 END IF;
2410 --
2411 -- K LPN CONV. rv
2412 --
2413 --
2414 IF l_debug_on THEN
2415 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:E_RETURN_EXCP');
2416 END IF;
2417 --
2418 -- J-IB-NPARIKH-{
2419 --
2420 WHEN FND_API.G_EXC_ERROR THEN
2421 IF c_is_first_leg%isopen THEN
2422 close c_is_first_leg;
2423 END IF;
2424 IF c_get_seq_numbers%ISOPEN THEN
2425 close c_get_seq_numbers;
2426 END IF;
2427 IF c_get_prev_seq_wv%ISOPEN THEN
2428 close c_get_prev_seq_wv;
2429 END IF;
2430 IF c_next_seq_exists%ISOPEN THEN
2431 close c_next_seq_exists;
2432 END IF;
2433 x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
2434 --
2435 -- K LPN CONV. rv
2436 --
2437 IF WSH_WMS_LPN_GRP.G_CALLBACK_REQUIRED = 'Y'
2438 THEN
2439 --{
2440 IF l_debug_on THEN
2441 WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_LPN_SYNC_COMM_PKG.SYNC_LPNS_TO_WMS',WSH_DEBUG_SV.C_PROC_LEVEL);
2442 END IF;
2443
2444 WSH_LPN_SYNC_COMM_PKG.SYNC_LPNS_TO_WMS
2445 (
2446 p_in_rec => l_lpn_in_sync_comm_rec,
2447 x_return_status => l_return_status,
2448 x_out_rec => l_lpn_out_sync_comm_rec
2449 );
2450 --
2451 --
2452 IF l_debug_on THEN
2453 WSH_DEBUG_SV.log(l_module_name,'Return status after calling WSH_LPN_SYNC_COMM_PKG.SYNC_LPNS_TO_WMS',l_return_status);
2454 END IF;
2455 --
2456 --
2457 IF (l_return_status = WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR) THEN
2458 x_return_status := l_return_status;
2459 END IF;
2460 --
2461 --}
2462 END IF;
2463 --
2464 -- K LPN CONV. rv
2465 --
2466 --
2467 IF l_debug_on THEN
2468 WSH_DEBUG_SV.logmsg(l_module_name,'FND_API.G_EXC_ERROR exception has occured ',WSH_DEBUG_SV.C_EXCEP_LEVEL);
2469 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:FND_API.G_EXC_ERROR');
2470 END IF;
2471 --
2472 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
2473 IF c_is_first_leg%isopen THEN
2474 close c_is_first_leg;
2475 END IF;
2476 IF c_get_seq_numbers%ISOPEN THEN
2477 close c_get_seq_numbers;
2478 END IF;
2479 IF c_get_prev_seq_wv%ISOPEN THEN
2480 close c_get_prev_seq_wv;
2481 END IF;
2482 IF c_next_seq_exists%ISOPEN THEN
2483 close c_next_seq_exists;
2484 END IF;
2485 x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR ;
2486 --
2487 IF l_debug_on THEN
2488 WSH_DEBUG_SV.logmsg(l_module_name,'Unexpected error has occured. Oracle error message is '|| SQLERRM,WSH_DEBUG_SV.C_UNEXPEC_ERR_LEVEL);
2489 END IF;
2490 --
2491 -- K LPN CONV. rv
2492 --
2493 IF WSH_WMS_LPN_GRP.G_CALLBACK_REQUIRED = 'Y'
2494 THEN
2495 --{
2496 IF l_debug_on THEN
2497 WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_LPN_SYNC_COMM_PKG.SYNC_LPNS_TO_WMS',WSH_DEBUG_SV.C_PROC_LEVEL);
2498 END IF;
2499
2500 WSH_LPN_SYNC_COMM_PKG.SYNC_LPNS_TO_WMS
2501 (
2502 p_in_rec => l_lpn_in_sync_comm_rec,
2503 x_return_status => l_return_status,
2504 x_out_rec => l_lpn_out_sync_comm_rec
2505 );
2506 --
2507 --
2508 IF l_debug_on THEN
2509 WSH_DEBUG_SV.log(l_module_name,'Return status after calling WSH_LPN_SYNC_COMM_PKG.SYNC_LPNS_TO_WMS',l_return_status);
2510 END IF;
2511 --
2512 --
2513 --}
2514 END IF;
2515 --
2516 -- K LPN CONV. rv
2517 --
2518 IF l_debug_on THEN
2519 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:FND_API.G_EXC_UNEXPECTED_ERROR');
2520 END IF;
2521 --
2522 -- J-IB-NPARIKH-}
2523 WHEN mark_reprice_error THEN
2524 IF c_is_first_leg%isopen THEN
2525 close c_is_first_leg;
2526 END IF;
2527 IF c_get_seq_numbers%ISOPEN THEN
2528 close c_get_seq_numbers;
2529 END IF;
2530 IF c_get_prev_seq_wv%ISOPEN THEN
2531 close c_get_prev_seq_wv;
2532 END IF;
2533 IF c_next_seq_exists%ISOPEN THEN
2534 close c_next_seq_exists;
2535 END IF;
2536 FND_MESSAGE.SET_NAME('WSH', 'WSH_REPRICE_REQUIRED_ERR');
2537 WSH_UTIL_CORE.add_message(l_return_status);
2538 x_return_status := l_return_status;
2539 --
2540 --
2541 -- K LPN CONV. rv
2542 --
2543 IF WSH_WMS_LPN_GRP.G_CALLBACK_REQUIRED = 'Y'
2544 THEN
2545 --{
2546 IF l_debug_on THEN
2547 WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_LPN_SYNC_COMM_PKG.SYNC_LPNS_TO_WMS',WSH_DEBUG_SV.C_PROC_LEVEL);
2548 END IF;
2549
2550 WSH_LPN_SYNC_COMM_PKG.SYNC_LPNS_TO_WMS
2551 (
2552 p_in_rec => l_lpn_in_sync_comm_rec,
2553 x_return_status => l_return_status,
2554 x_out_rec => l_lpn_out_sync_comm_rec
2555 );
2556 --
2557 --
2558 IF l_debug_on THEN
2559 WSH_DEBUG_SV.log(l_module_name,'Return status after calling WSH_LPN_SYNC_COMM_PKG.SYNC_LPNS_TO_WMS',l_return_status);
2560 END IF;
2561 --
2562 --
2563 IF (l_return_status = WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR) THEN
2564 x_return_status := l_return_status;
2565 END IF;
2566 --
2567 --}
2568 END IF;
2569 --
2570 -- K LPN CONV. rv
2571 --
2572 -- Debug Statements
2573 --
2574 IF l_debug_on THEN
2575 WSH_DEBUG_SV.logmsg(l_module_name,'MARK_REPRICE_ERROR exception has occured.',WSH_DEBUG_SV.C_EXCEP_LEVEL);
2576 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:MARK_REPRICE_ERROR');
2577 END IF;
2578 --
2579 WHEN Invalid_Trip THEN
2580 IF c_is_first_leg%isopen THEN
2581 close c_is_first_leg;
2582 END IF;
2583 IF c_get_seq_numbers%ISOPEN THEN
2584 close c_get_seq_numbers;
2585 END IF;
2586 IF c_get_prev_seq_wv%ISOPEN THEN
2587 close c_get_prev_seq_wv;
2588 END IF;
2589 IF c_next_seq_exists%ISOPEN THEN
2590 close c_next_seq_exists;
2591 END IF;
2592 FND_MESSAGE.SET_NAME('WSH','WSH_INVALID_TRIP');
2593 x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
2594 wsh_util_core.add_message(x_return_status);
2595 --
2596 -- K LPN CONV. rv
2597 --
2598 IF WSH_WMS_LPN_GRP.G_CALLBACK_REQUIRED = 'Y'
2599 THEN
2600 --{
2601 IF l_debug_on THEN
2602 WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_LPN_SYNC_COMM_PKG.SYNC_LPNS_TO_WMS',WSH_DEBUG_SV.C_PROC_LEVEL);
2603 END IF;
2604
2605 WSH_LPN_SYNC_COMM_PKG.SYNC_LPNS_TO_WMS
2606 (
2607 p_in_rec => l_lpn_in_sync_comm_rec,
2608 x_return_status => l_return_status,
2609 x_out_rec => l_lpn_out_sync_comm_rec
2610 );
2611 --
2612 --
2613 IF l_debug_on THEN
2614 WSH_DEBUG_SV.log(l_module_name,'Return status after calling WSH_LPN_SYNC_COMM_PKG.SYNC_LPNS_TO_WMS',l_return_status);
2615 END IF;
2616 --
2617 --
2618 IF (l_return_status = WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR) THEN
2619 x_return_status := l_return_status;
2620 END IF;
2621 --
2622 --}
2623 END IF;
2624 --
2625 -- K LPN CONV. rv
2626 --
2627 --
2628 -- Debug Statements
2629 --
2630 IF l_debug_on THEN
2631 WSH_DEBUG_SV.logmsg(l_module_name,'INVALID_TRIP exception has occured.',WSH_DEBUG_SV.C_EXCEP_LEVEL);
2632 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:INVALID_TRIP');
2633 END IF;
2634 --
2635 WHEN bad_trip_stop THEN
2636 IF c_is_first_leg%isopen THEN
2637 close c_is_first_leg;
2638 END IF;
2639 IF c_get_seq_numbers%ISOPEN THEN
2640 close c_get_seq_numbers;
2641 END IF;
2642 IF c_get_prev_seq_wv%ISOPEN THEN
2643 close c_get_prev_seq_wv;
2644 END IF;
2645 IF c_next_seq_exists%ISOPEN THEN
2646 close c_next_seq_exists;
2647 END IF;
2648 FND_MESSAGE.SET_NAME('WSH','WSH_STOP_NOT_FOUND');
2649 x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
2650 wsh_util_core.add_message(x_return_status);
2651 --
2652 --
2653 -- K LPN CONV. rv
2654 --
2655 IF WSH_WMS_LPN_GRP.G_CALLBACK_REQUIRED = 'Y'
2656 THEN
2657 --{
2658 IF l_debug_on THEN
2659 WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_LPN_SYNC_COMM_PKG.SYNC_LPNS_TO_WMS',WSH_DEBUG_SV.C_PROC_LEVEL);
2660 END IF;
2661
2662 WSH_LPN_SYNC_COMM_PKG.SYNC_LPNS_TO_WMS
2663 (
2664 p_in_rec => l_lpn_in_sync_comm_rec,
2665 x_return_status => l_return_status,
2666 x_out_rec => l_lpn_out_sync_comm_rec
2667 );
2668 --
2669 --
2670 IF l_debug_on THEN
2671 WSH_DEBUG_SV.log(l_module_name,'Return status after calling WSH_LPN_SYNC_COMM_PKG.SYNC_LPNS_TO_WMS',l_return_status);
2672 END IF;
2673 --
2674 --
2675 IF (l_return_status = WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR) THEN
2676 x_return_status := l_return_status;
2677 END IF;
2678 --
2679 --}
2680 END IF;
2681 --
2682 -- K LPN CONV. rv
2683 --
2684 -- Debug Statements
2685 --
2686 IF l_debug_on THEN
2687 WSH_DEBUG_SV.logmsg(l_module_name,'BAD_TRIP_STOP exception has occured.',WSH_DEBUG_SV.C_EXCEP_LEVEL);
2688 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:BAD_TRIP_STOP');
2689 END IF;
2690 --
2691 WHEN rate_trip_contents_fail THEN
2692 IF c_is_first_leg%isopen THEN
2693 close c_is_first_leg;
2694 END IF;
2695 IF c_get_seq_numbers%ISOPEN THEN
2696 close c_get_seq_numbers;
2697 END IF;
2698 IF c_get_prev_seq_wv%ISOPEN THEN
2699 close c_get_prev_seq_wv;
2700 END IF;
2701 IF c_next_seq_exists%ISOPEN THEN
2702 close c_next_seq_exists;
2703 END IF;
2704 x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
2705 FND_MESSAGE.Set_Name('WSH', 'WSH_RATE_TRIP_CONTENTS_FAIL');
2706 WSH_UTIL_CORE.Add_Message(x_return_status);
2707 --
2708 -- K LPN CONV. rv
2709 --
2710 IF WSH_WMS_LPN_GRP.G_CALLBACK_REQUIRED = 'Y'
2711 THEN
2712 --{
2713 IF l_debug_on THEN
2714 WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_LPN_SYNC_COMM_PKG.SYNC_LPNS_TO_WMS',WSH_DEBUG_SV.C_PROC_LEVEL);
2715 END IF;
2716
2717 WSH_LPN_SYNC_COMM_PKG.SYNC_LPNS_TO_WMS
2718 (
2719 p_in_rec => l_lpn_in_sync_comm_rec,
2720 x_return_status => l_return_status,
2721 x_out_rec => l_lpn_out_sync_comm_rec
2722 );
2723 --
2724 --
2725 IF l_debug_on THEN
2726 WSH_DEBUG_SV.log(l_module_name,'Return status after calling WSH_LPN_SYNC_COMM_PKG.SYNC_LPNS_TO_WMS',l_return_status);
2727 END IF;
2728 --
2729 --
2730 IF (l_return_status = WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR) THEN
2731 x_return_status := l_return_status;
2732 END IF;
2733 --
2734 --}
2735 END IF;
2736 --
2737 -- K LPN CONV. rv
2738 --
2739 IF l_debug_on THEN
2740 WSH_DEBUG_SV.logmsg(l_module_name,'rate_trip_contents_fail exception has occured.',WSH_DEBUG_SV.C_EXCEP_LEVEL);
2741 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:rate_trip_contents_fail');
2742 END IF;
2743
2744 WHEN e_lock_error THEN
2745 IF get_stop_new%ISOPEN THEN
2746 close get_stop_new;
2747 ELSIF get_stop%ISOPEN THEN
2748 close get_stop;
2749 END IF;
2750 IF get_trip_status%ISOPEN THEN
2751 close get_trip_status;
2752 ELSIF stop_exists%ISOPEN THEN
2753 close stop_exists;
2754 END IF;
2755
2756 x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
2757
2758 FND_MESSAGE.Set_Name('WSH', 'WSH_NO_LOCK');
2759 wsh_util_core.add_message(WSH_UTIL_CORE.G_RET_STS_ERROR, l_module_name);
2760 IF l_debug_on THEN
2761 WSH_DEBUG_SV.logmsg(l_module_name,'e_lock_error' ,WSH_DEBUG_SV.C_PROC_LEVEL);
2762 END IF;
2763 --
2764 -- K LPN CONV. rv
2765 --
2766 IF WSH_WMS_LPN_GRP.G_CALLBACK_REQUIRED = 'Y'
2767 THEN
2768 --{
2769 IF l_debug_on THEN
2770 WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_LPN_SYNC_COMM_PKG.SYNC_LPNS_TO_WMS',WSH_DEBUG_SV.C_PROC_LEVEL);
2771 END IF;
2772
2773 WSH_LPN_SYNC_COMM_PKG.SYNC_LPNS_TO_WMS
2774 (
2775 p_in_rec => l_lpn_in_sync_comm_rec,
2776 x_return_status => l_return_status,
2777 x_out_rec => l_lpn_out_sync_comm_rec
2778 );
2779 --
2780 --
2781 IF l_debug_on THEN
2782 WSH_DEBUG_SV.log(l_module_name,'Return status after calling WSH_LPN_SYNC_COMM_PKG.SYNC_LPNS_TO_WMS',l_return_status);
2783 END IF;
2784 --
2785 --
2786 --}
2787 END IF;
2788 --
2789 -- K LPN CONV. rv
2790 --
2791 --
2792 -- Debug Statements
2793 --
2794 IF l_debug_on THEN
2795 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:E_LOCK_ERROR');
2796 END IF;
2797 --
2798 WHEN others THEN
2799 IF get_trip_status%ISOPEN THEN
2800 close get_trip_status;
2801 ELSIF stop_exists%ISOPEN THEN
2802 close stop_exists;
2803 END IF;
2804 IF c_is_first_leg%isopen THEN
2805 close c_is_first_leg;
2806 END IF;
2807 IF c_get_seq_numbers%ISOPEN THEN
2808 close c_get_seq_numbers;
2809 END IF;
2810 IF c_get_prev_seq_wv%ISOPEN THEN
2811 close c_get_prev_seq_wv;
2812 END IF;
2813 IF c_next_seq_exists%ISOPEN THEN
2814 close c_next_seq_exists;
2815 END IF;
2816 wsh_util_core.default_handler('WSH_DELIVERY_LEGS_ACTIONS.ASSIGN_DELIVERIES');
2817 x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR;
2818
2819 IF l_debug_on THEN
2820 WSH_DEBUG_SV.logmsg(l_module_name,'Unexpected error has occured. Oracle error message is '|| SQLERRM,WSH_DEBUG_SV.C_UNEXPEC_ERR_LEVEL);
2821 END IF;
2822 --
2823 -- K LPN CONV. rv
2824 --
2825 IF WSH_WMS_LPN_GRP.G_CALLBACK_REQUIRED = 'Y'
2826 THEN
2827 --{
2828 IF l_debug_on THEN
2829 WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_LPN_SYNC_COMM_PKG.SYNC_LPNS_TO_WMS',WSH_DEBUG_SV.C_PROC_LEVEL);
2830 END IF;
2831
2832 WSH_LPN_SYNC_COMM_PKG.SYNC_LPNS_TO_WMS
2833 (
2834 p_in_rec => l_lpn_in_sync_comm_rec,
2835 x_return_status => l_return_status,
2836 x_out_rec => l_lpn_out_sync_comm_rec
2837 );
2838 --
2839 --
2840 IF l_debug_on THEN
2841 WSH_DEBUG_SV.log(l_module_name,'Return status after calling WSH_LPN_SYNC_COMM_PKG.SYNC_LPNS_TO_WMS',l_return_status);
2842 END IF;
2843 --
2844 --
2845 --}
2846 END IF;
2847 --
2848 -- K LPN CONV. rv
2849 --
2850 --
2851 -- Debug Statements
2852 --
2853 IF l_debug_on THEN
2854 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:OTHERS');
2855 END IF;
2856 --
2857 END Assign_Deliveries;
2858
2859
2860 PROCEDURE Unassign_Deliveries
2861 (p_del_rows IN wsh_util_core.id_tab_type,
2862 p_trip_id IN NUMBER := NULL,
2863 p_pickup_stop_id IN NUMBER := NULL,
2864 p_dropoff_stop_id IN NUMBER := NULL,
2865 x_return_status OUT NOCOPY VARCHAR2
2866 ) IS
2867
2868 -- Note: dont merge next two cursors due to full table scan problem
2869
2870 CURSOR del_pickup_exists(p_del_id IN NUMBER) IS
2871 SELECT delivery_leg_id
2872 FROM wsh_delivery_legs
2873 WHERE pick_up_stop_id = p_pickup_stop_id
2874 FOR UPDATE NOWAIT;
2875
2876 CURSOR del_dropoff_exists(p_del_id IN NUMBER) IS
2877 SELECT delivery_leg_id
2878 FROM wsh_delivery_legs
2879 WHERE drop_off_stop_id = p_dropoff_stop_id
2880 FOR UPDATE NOWAIT;
2881
2882 CURSOR del_trip_exists( p_del_id IN NUMBER) IS
2883 SELECT dg.delivery_leg_id, st1.stop_location_id
2884 FROM wsh_trip_stops st1,
2885 wsh_trip_stops st2,
2886 wsh_delivery_legs dg
2887 WHERE st1.stop_id = dg.pick_up_stop_id AND
2888 st2.stop_id = dg.drop_off_stop_id AND
2889 st1.trip_id = p_trip_id AND
2890 st2.trip_id = p_trip_id AND
2891 dg.delivery_id = p_del_id
2892 FOR UPDATE NOWAIT;
2893
2894 cursor get_del_status(c_del_id IN NUMBER) is
2895 select status_code,
2896 nvl(shipment_direction,'O') shipment_direction
2897 from wsh_new_deliveries
2898 where delivery_id = c_del_id;
2899
2900 -- KLR
2901 /*
2902 cursor get_del_status(c_del_id IN NUMBER) is
2903 select status_code,
2904 nvl(shipment_direction,'O') shipment_direction
2905 from wsh_new_deliveries
2906 where delivery_id = c_del_id;
2907 */
2908 cursor get_del_info(c_del_id IN NUMBER) is
2909 select status_code,
2910 nvl(shipment_direction,'O') shipment_direction,
2911 gross_weight,
2912 net_weight,
2913 volume, initial_pickup_location_id,
2914 organization_id
2915 from wsh_new_deliveries
2916 where delivery_id = c_del_id;
2917
2918 -- Bug 3875780
2919 CURSOR c_trip_stops(p_trip_id IN NUMBER) IS
2920 SELECT stop_id
2921 FROM wsh_trip_stops
2922 WHERE trip_id = p_trip_id;
2923
2924
2925 CURSOR c_trip_empty IS
2926 SELECT count(*)
2927 FROM wsh_trip_stops a,wsh_delivery_legs b
2928 WHERE a.stop_id = b.pick_up_stop_id
2929 AND a.trip_id = p_trip_id
2930 AND rownum = 1 ;
2931
2932 l_cnt number;
2933 l_trip_empty BOOLEAN;
2934 --
2935
2936 l_del_for_update_load_seq VARCHAR2(1); -- bug 6700792:OTM Dock Door App Sched Proj
2937
2938 l_del_status VARCHAR2(2);
2939 l_gross_wt number;
2940 l_net_wt number;
2941 l_vol number;
2942 l_del_pu_location_id NUMBER;
2943 l_stop_pu_location_id NUMBER;
2944 l_org_id NUMBER;
2945 l_dummy_leg_id NUMBER;
2946 l_dlvy_trip_tbl WMS_SHIPPING_INTERFACE_GRP.g_dlvy_trip_tbl;
2947 l_msg_count NUMBER;
2948 l_msg_data VARCHAR2(2000);
2949 i NUMBER;
2950 l_del_leg_id NUMBER;
2951 l_return_status VARCHAR2(1);
2952 l_ret_status VARCHAR2(1);
2953 l_num_error NUMBER := 0;
2954 l_num_warning NUMBER := 0;
2955
2956 cannot_unassign EXCEPTION;
2957 lock_error EXCEPTION;
2958 pragma EXCEPTION_INIT(lock_error,-54);
2959
2960 --
2961 l_debug_on BOOLEAN;
2962 --
2963 l_module_name CONSTANT VARCHAR2(100) := 'wsh.plsql.' || G_PKG_NAME || '.' || 'UNASSIGN_DELIVERIES';
2964 --
2965 --
2966 l_shipment_direction VARCHAR2(30);
2967 l_shipping_control VARCHAR2(30);
2968 l_routing_response_id NUMBER;
2969 l_routing_request_flag VARCHAR2(30);
2970 -- K LPN CONV. rv
2971 l_lpn_in_sync_comm_rec WSH_GLBL_VAR_STRCT_GRP.lpn_sync_comm_in_rec_type;
2972 l_lpn_out_sync_comm_rec WSH_GLBL_VAR_STRCT_GRP.lpn_sync_comm_out_rec_type;
2973 e_return_excp EXCEPTION;
2974 -- K LPN CONV. rv
2975 BEGIN
2976
2977 --
2978 -- Debug Statements
2979 --
2980 --
2981 l_debug_on := WSH_DEBUG_INTERFACE.g_debug;
2982 --
2983 IF l_debug_on IS NULL
2984 THEN
2985 l_debug_on := WSH_DEBUG_SV.is_debug_enabled;
2986 END IF;
2987
2988 IF l_debug_on THEN
2989 WSH_DEBUG_SV.push(l_module_name);
2990 --
2991 WSH_DEBUG_SV.log(l_module_name,'P_TRIP_ID',P_TRIP_ID);
2992 WSH_DEBUG_SV.log(l_module_name,'P_PICKUP_STOP_ID',P_PICKUP_STOP_ID);
2993 WSH_DEBUG_SV.log(l_module_name,'P_DROPOFF_STOP_ID',P_DROPOFF_STOP_ID);
2994 END IF;
2995 --
2996 IF (p_trip_id IS NULL) AND (p_pickup_stop_id IS NULL) AND
2997 (p_dropoff_stop_id IS NULL) THEN
2998 RAISE cannot_unassign;
2999 END IF;
3000
3001 FOR i IN 1..p_del_rows.count LOOP
3002
3003 l_return_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
3004 l_del_leg_id := NULL;
3005
3006 -- bug 6700792: OTM Dock Door App Sched Proj
3007 -- l_del_for_update_load_seq will verrify whether the delivery was assigned to a planned trip from OTM.
3008 l_del_for_update_load_seq := 'N';
3009
3010 --Bug 6884545 Start
3011 --Handling No data found
3012 BEGIN
3013
3014 SELECT 'Y'
3015 INTO l_del_for_update_load_seq
3016 FROM wsh_delivery_legs wdl,
3017 wsh_trip_stops wts,
3018 wsh_trips wt
3019 WHERE wdl.delivery_id = p_del_rows(i)
3020 AND wdl.drop_off_stop_id = wts.stop_id
3021 AND wts.trip_id = wt.trip_id
3022 AND wt.ignore_for_planning = 'N'
3023 AND rownum = 1; --Bug 6884545
3024
3025 EXCEPTION
3026 WHEN NO_DATA_FOUND THEN
3027 l_del_for_update_load_seq := 'N';
3028
3029 END; --Bug 6884545 End
3030
3031 -- J: W/V Changes
3032 open get_del_info(p_del_rows(i));
3033 fetch get_del_info into l_del_status, l_shipment_direction, l_gross_wt, l_net_wt, l_vol, l_del_pu_location_id, l_org_id;
3034 close get_del_info;
3035
3036 --IF l_del_status in ('CL', 'CA', 'SR', 'SC') THEN -- sperera 940/945
3037 -- Bug 2307456, allow this action for status of SR and SC
3038
3039 -- Check if the delivery is in a valid status.
3040 IF l_del_status IN ( 'CA') THEN
3041 FND_MESSAGE.SET_NAME('WSH','WSH_DEL_UNASSIGN_ERROR');
3042 l_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
3043 wsh_util_core.add_message(l_return_status);
3044 END IF;
3045 --
3046
3047 IF l_del_status = 'CL'
3048 THEN
3049 FND_MESSAGE.SET_NAME('WSH','WSH_DEL_UNASSIGN_ERROR');
3050 l_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
3051 wsh_util_core.add_message(l_return_status);
3052 END IF;
3053 --
3054
3055 l_stop_pu_location_id := NULL;
3056
3057 IF (p_pickup_stop_id IS NOT NULL) THEN
3058
3059 OPEN del_pickup_exists(p_del_rows(i));
3060 FETCH del_pickup_exists INTO l_del_leg_id;
3061 CLOSE del_pickup_exists;
3062
3063 IF (l_del_leg_id IS NULL) THEN
3064 FND_MESSAGE.SET_NAME('WSH','WSH_STOP_NOT_FOUND');
3065 l_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
3066 wsh_util_core.add_message(l_return_status);
3067 END IF;
3068
3069 ELSIF (p_dropoff_stop_id IS NOT NULL) THEN
3070
3071 OPEN del_dropoff_exists(p_del_rows(i));
3072 FETCH del_dropoff_exists INTO l_del_leg_id;
3073 CLOSE del_dropoff_exists;
3074
3075 IF (l_del_leg_id IS NULL) THEN
3076 FND_MESSAGE.SET_NAME('WSH','WSH_STOP_NOT_FOUND');
3077 l_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
3078 wsh_util_core.add_message(l_return_status);
3079 END IF;
3080
3081 ELSIF (p_trip_id IS NOT NULL) THEN
3082 OPEN del_trip_exists(p_del_rows(i));
3083 FETCH del_trip_exists INTO l_del_leg_id, l_stop_pu_location_id;
3084 CLOSE del_trip_exists;
3085
3086 IF (l_del_leg_id IS NULL) THEN
3087 FND_MESSAGE.SET_NAME('WSH','WSH_TRIP_NOT_FOUND');
3088 l_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
3089 wsh_util_core.add_message(l_return_status);
3090 END IF;
3091
3092 END IF;
3093
3094 -- Bug 3584924
3095 IF (l_return_status = WSH_UTIL_CORE.G_RET_STS_SUCCESS) THEN
3096 IF NOT g_wms_installed.exists(l_org_id) THEN
3097 g_wms_installed(l_org_id) := wsh_util_validate.check_wms_org(l_org_id);
3098 END IF;
3099
3100 IF g_wms_installed(l_org_id) = 'Y' THEN
3101 -- Check if it is the first leg of the delivery.
3102 l_dummy_leg_id := NULL;
3103 IF l_stop_pu_location_id IS NOT NULL THEN
3104 IF l_stop_pu_location_id = l_del_pu_location_id THEN
3105 l_dummy_leg_id := l_del_leg_id;
3106 END IF;
3107 ELSE
3108 OPEN C_IS_FIRST_LEG(p_trip_id, p_del_rows(i), l_del_pu_location_id);
3109 FETCH C_IS_FIRST_LEG INTO l_dummy_leg_id;
3110 IF C_IS_FIRST_LEG%NOTFOUND THEN
3111 l_dummy_leg_id := NULL;
3112 END IF;
3113 CLOSE C_IS_FIRST_LEG;
3114 END IF;
3115 IF l_dummy_leg_id IS NOT NULL THEN
3116 -- Call wms to check if unassignment is valid
3117 l_dlvy_trip_tbl(1).delivery_id := p_del_rows(i);
3118 l_dlvy_trip_tbl(1).trip_id := p_trip_id;
3119 IF l_debug_on THEN
3120 WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WMS_SHIPPING_INTERFACE_GRP.Process_Delivery_Trip',WSH_DEBUG_SV.C_PROC_LEVEL);
3121 WSH_DEBUG_SV.log(l_module_name,'trip_id',p_trip_id);
3122 WSH_DEBUG_SV.log(l_module_name,'del_id',p_del_rows(i));
3123 END IF;
3124 WMS_SHIPPING_INTERFACE_GRP.Process_Delivery_Trip(
3125 p_api_version => 1.0,
3126 p_init_msg_list => wms_shipping_interface_grp.g_false,
3127 p_commit => wms_shipping_interface_grp.g_false,
3128 p_validation_level => wms_shipping_interface_grp.g_full_validation,
3129 p_action => wms_shipping_interface_grp.g_action_unassign_dlvy_trip,
3130 p_dlvy_trip_tbl => l_dlvy_trip_tbl,
3131 x_return_status => l_return_status,
3132 x_msg_count => l_msg_count,
3133 x_msg_data => l_msg_data);
3134 IF l_dlvy_trip_tbl(1).r_message_type IN (WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR, WSH_UTIL_CORE.G_RET_STS_ERROR)
3135 OR l_return_status IN (WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR, WSH_UTIL_CORE.G_RET_STS_ERROR ) THEN
3136 FND_MESSAGE.SET_NAME(l_dlvy_trip_tbl(1).r_message_appl,l_dlvy_trip_tbl(1).r_message_code);
3137 IF l_dlvy_trip_tbl(1).r_message_token IS NOT NULL THEN
3138 FND_MESSAGE.SET_TOKEN(l_dlvy_trip_tbl(1).r_message_token_name, l_dlvy_trip_tbl(1).r_message_token);
3139 END IF;
3140 WSH_UTIL_CORE.ADD_MESSAGE(l_dlvy_trip_tbl(1).r_message_type);
3141 l_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
3142 l_num_error := l_num_error + 1;
3143 END IF;
3144 END IF;
3145
3146 END IF;
3147 END IF;
3148 -- J: W/V Changes
3149 IF (l_return_status = WSH_UTIL_CORE.G_RET_STS_SUCCESS) THEN
3150
3151 IF l_debug_on THEN
3152 WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_WV_UTILS.Del_WV_Post_Process',WSH_DEBUG_SV.C_PROC_LEVEL);
3153 END IF;
3154 WSH_WV_UTILS.Del_WV_Post_Process(
3155 p_delivery_id => p_del_rows(i),
3156 p_diff_gross_wt => -1 * l_gross_wt,
3157 p_diff_net_wt => -1 * l_net_wt,
3158 p_diff_volume => -1 * l_vol,
3159 p_check_for_empty => 'Y',
3160 p_leg_id => l_del_leg_id,
3161 x_return_status => l_return_status);
3162 END IF;
3163
3164 IF (l_return_status <> WSH_UTIL_CORE.G_RET_STS_SUCCESS) THEN
3165 IF (l_return_status = WSH_UTIL_CORE.G_RET_STS_WARNING) THEN
3166 l_num_warning := l_num_warning + 1;
3167 ELSE
3168 l_num_error := l_num_error + 1;
3169 END IF;
3170 END IF;
3171
3172
3173 IF (l_return_status = WSH_UTIL_CORE.G_RET_STS_SUCCESS) THEN
3174
3175 --
3176 -- Debug Statements
3177 --
3178 IF l_debug_on THEN
3179 WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_DELIVERY_LEGS_PVT.DELETE_DELIVERY_LEG',WSH_DEBUG_SV.C_PROC_LEVEL);
3180 END IF;
3181 --
3182 wsh_delivery_legs_pvt.delete_delivery_leg(NULL,
3183 l_del_leg_id, l_return_status);
3184
3185 END IF;
3186
3187 IF (l_return_status <> WSH_UTIL_CORE.G_RET_STS_SUCCESS) THEN
3188 IF (l_return_status = WSH_UTIL_CORE.G_RET_STS_WARNING) THEN
3189 l_num_warning := l_num_warning + 1;
3190 ELSE
3191 l_num_error := l_num_error + 1;
3192 END IF;
3193 END IF;
3194
3195 -- bug 6700792: OTM Dock Door Appt Sched Proj
3196 -- NULL out the Load Sequencing Number for WMS enabled OTM org while unassigning delivery from trip.
3197 IF (l_del_for_update_load_seq = 'Y' AND g_wms_installed(l_org_id) = 'Y') THEN
3198
3199 DECLARE
3200
3201 CURSOR c_lock_delivery_details (c_delivery_id IN number) IS
3202 SELECT wdd.delivery_detail_id
3203 FROM wsh_delivery_assignments wda,
3204 wsh_delivery_details wdd
3205 WHERE wda.delivery_id = c_delivery_id
3206 AND wda.delivery_detail_id = wdd.delivery_detail_id
3207 FOR UPDATE OF wdd.load_seq_number NOWAIT;
3208
3209 l_del_det_tab WSH_UTIL_CORE.ID_TAB_TYPE;
3210
3211 BEGIN
3212
3213 l_del_det_tab.DELETE;
3214
3215 OPEN c_lock_delivery_details (p_del_rows(i));
3216 FETCH c_lock_delivery_details BULK COLLECT INTO l_del_det_tab;
3217 IF l_del_det_tab.COUNT > 0 THEN
3218 IF l_debug_on THEN
3219 WSH_DEBUG_SV.log(l_module_name, 'Updating Deliveries and Details With Loading Sequence as NULL');
3220 END IF;
3221
3222 UPDATE wsh_new_deliveries
3223 SET loading_sequence = NULL,
3224 last_update_date = SYSDATE,
3225 last_updated_by = FND_GLOBAL.USER_ID,
3226 last_update_login = FND_GLOBAL.LOGIN_ID
3227 WHERE delivery_id = p_del_rows(i);
3228
3229 FORALL i in l_del_det_tab.FIRST..l_del_det_tab.LAST
3230 UPDATE wsh_delivery_details
3231 SET load_seq_number = NULL,
3232 last_update_date = SYSDATE,
3233 last_updated_by = FND_GLOBAL.USER_ID,
3234 last_update_login = FND_GLOBAL.LOGIN_ID
3235 WHERE delivery_detail_id = l_del_det_tab(i);
3236
3237 END IF;
3238
3239 CLOSE c_lock_delivery_details;
3240
3241 EXCEPTION
3242 WHEN OTHERS THEN
3243 l_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
3244 IF c_lock_delivery_details%ISOPEN then
3245 CLOSE c_lock_delivery_details;
3246 END IF;
3247 IF l_debug_on THEN
3248 WSH_DEBUG_SV.log(l_module_name, 'Unable to lock Deliveries/Details for delivery '||p_del_rows(i));
3249 WSH_DEBUG_SV.log(l_module_name, 'l_return_status', l_return_status);
3250 END IF;
3251 RAISE cannot_unassign;
3252 END;
3253 END IF;
3254 --
3255
3256 END LOOP;
3257
3258 -- bug 3875780
3259 IF WSH_UTIL_CORE.FTE_IS_INSTALLED = 'Y' THEN
3260 l_trip_empty := FALSE;
3261 OPEN c_trip_empty ;
3262 FETCH c_trip_empty into l_cnt;
3263 CLOSE c_trip_empty;
3264
3265 IF l_cnt = 0 THEN
3266 l_trip_empty := TRUE;
3267 END IF;
3268 IF l_trip_empty THEN
3269
3270 FOR rec IN c_trip_stops(p_trip_id) LOOP
3271 WSH_TRIPS_ACTIONS.Fte_Load_Tender(
3272 p_stop_id => rec.stop_id,
3273 p_gross_weight => null,
3274 p_net_weight => null,
3275 p_volume => null,
3276 p_fill_percent => null,
3277 x_return_status => l_return_status);
3278
3279 IF l_return_status in (WSH_UTIL_CORE.G_RET_STS_ERROR, WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR) THEN
3280 x_return_status := l_return_status;
3281 --RETURN;
3282 raise e_return_excp; -- LPN CONV. rv
3283 ELSIF l_return_status = WSH_UTIL_CORE.G_RET_STS_WARNING THEN
3284 -- we need to return warning if FTE gives warning here.
3285 l_num_warning := l_num_warning + 1;
3286 END IF;
3287 END LOOP;
3288
3289 END IF;
3290 END IF;
3291 --
3292 -- K LPN CONV. rv
3293 --
3294 IF WSH_WMS_LPN_GRP.G_CALLBACK_REQUIRED = 'Y'
3295 THEN
3296 --{
3297 IF l_debug_on THEN
3298 WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_LPN_SYNC_COMM_PKG.SYNC_LPNS_TO_WMS',WSH_DEBUG_SV.C_PROC_LEVEL);
3299 END IF;
3300
3301 WSH_LPN_SYNC_COMM_PKG.SYNC_LPNS_TO_WMS
3302 (
3303 p_in_rec => l_lpn_in_sync_comm_rec,
3304 x_return_status => l_return_status,
3305 x_out_rec => l_lpn_out_sync_comm_rec
3306 );
3307 --
3308 --
3309 IF l_debug_on THEN
3310 WSH_DEBUG_SV.log(l_module_name,'Return status after calling WSH_LPN_SYNC_COMM_PKG.SYNC_LPNS_TO_WMS',l_return_status);
3311 END IF;
3312 --
3313 --
3314 WSH_UTIL_CORE.API_POST_CALL
3315 (
3316 p_return_status => l_return_status,
3317 x_num_warnings => l_num_warning,
3318 x_num_errors => l_num_error,
3319 p_raise_error_flag => false
3320 );
3321 --}
3322 END IF;
3323 --
3324 -- K LPN CONV. rv
3325 --
3326
3327 IF (l_num_error >= p_del_rows.count) THEN
3328 x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
3329 ELSIF (l_num_warning > 0 OR l_num_error >0)THEN
3330 x_return_status := WSH_UTIL_CORE.G_RET_STS_WARNING;
3331 ELSE
3332 x_return_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
3333 END IF;
3334
3335 --
3336 -- Debug Statements
3337 --
3338 IF l_debug_on THEN
3339 WSH_DEBUG_SV.pop(l_module_name);
3340 END IF;
3341 --
3342 EXCEPTION
3343 WHEN e_return_excp THEN
3344
3345 --
3346 -- K LPN CONV. rv
3347 --
3348 IF WSH_WMS_LPN_GRP.G_CALLBACK_REQUIRED = 'Y'
3349 THEN
3350 --{
3351 IF l_debug_on THEN
3352 WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_LPN_SYNC_COMM_PKG.SYNC_LPNS_TO_WMS',WSH_DEBUG_SV.C_PROC_LEVEL);
3353 END IF;
3354
3355 WSH_LPN_SYNC_COMM_PKG.SYNC_LPNS_TO_WMS
3356 (
3357 p_in_rec => l_lpn_in_sync_comm_rec,
3358 x_return_status => l_return_status,
3359 x_out_rec => l_lpn_out_sync_comm_rec
3360 );
3361 --
3362 --
3363 IF l_debug_on THEN
3364 WSH_DEBUG_SV.log(l_module_name,'Return status after calling WSH_LPN_SYNC_COMM_PKG.SYNC_LPNS_TO_WMS',l_return_status);
3365 END IF;
3366 --
3367 --
3368 IF (l_return_status IN (WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR,WSH_UTIL_CORE.G_RET_STS_ERROR) and x_return_status <> WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR) THEN
3369 x_return_status := l_return_status;
3370 END IF;
3371 --
3372 --}
3373 END IF;
3374 --
3375 -- K LPN CONV. rv
3376 --
3377 --
3378 IF l_debug_on THEN
3379 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:E_RETURN_EXCP');
3380 END IF;
3381 --
3382 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
3383
3384 x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR ;
3385 IF l_debug_on THEN
3386 WSH_DEBUG_SV.logmsg(l_module_name,'Unexpected error has occured. Oracle error message is '|| SQLERRM,WSH_DEBUG_SV.C_UNEXPEC_ERR_LEVEL);
3387 END IF;
3388 --
3389 -- K LPN CONV. rv
3390 --
3391 IF WSH_WMS_LPN_GRP.G_CALLBACK_REQUIRED = 'Y'
3392 THEN
3393 --{
3394 IF l_debug_on THEN
3395 WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_LPN_SYNC_COMM_PKG.SYNC_LPNS_TO_WMS',WSH_DEBUG_SV.C_PROC_LEVEL);
3396 END IF;
3397
3398 WSH_LPN_SYNC_COMM_PKG.SYNC_LPNS_TO_WMS
3399 (
3400 p_in_rec => l_lpn_in_sync_comm_rec,
3401 x_return_status => l_return_status,
3402 x_out_rec => l_lpn_out_sync_comm_rec
3403 );
3404 --
3405 --
3406 IF l_debug_on THEN
3407 WSH_DEBUG_SV.log(l_module_name,'Return status after calling WSH_LPN_SYNC_COMM_PKG.SYNC_LPNS_TO_WMS',l_return_status);
3408 END IF;
3409 --
3410 --
3411 --}
3412 END IF;
3413 --
3414 -- K LPN CONV. rv
3415 --
3416 --
3417 IF c_is_first_leg%isopen THEN
3418 close c_is_first_leg;
3419 END IF;
3420 IF l_debug_on THEN
3421 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:FND_API.G_EXC_UNEXPECTED_ERROR');
3422 END IF;
3423 --
3424
3425 WHEN lock_error THEN
3426 IF c_is_first_leg%isopen THEN
3427 close c_is_first_leg;
3428 END IF;
3429 FND_MESSAGE.SET_NAME('FND','FORM_UNABLE_TO_RESERVE_RECORD');
3430 x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
3431 wsh_util_core.add_message(x_return_status);
3432 --
3433 -- K LPN CONV. rv
3434 --
3435 IF WSH_WMS_LPN_GRP.G_CALLBACK_REQUIRED = 'Y'
3436 THEN
3437 --{
3438 IF l_debug_on THEN
3439 WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_LPN_SYNC_COMM_PKG.SYNC_LPNS_TO_WMS',WSH_DEBUG_SV.C_PROC_LEVEL);
3440 END IF;
3441
3442 WSH_LPN_SYNC_COMM_PKG.SYNC_LPNS_TO_WMS
3443 (
3444 p_in_rec => l_lpn_in_sync_comm_rec,
3445 x_return_status => l_return_status,
3446 x_out_rec => l_lpn_out_sync_comm_rec
3447 );
3448 --
3449 --
3450 IF l_debug_on THEN
3451 WSH_DEBUG_SV.log(l_module_name,'Return status after calling WSH_LPN_SYNC_COMM_PKG.SYNC_LPNS_TO_WMS',l_return_status);
3452 END IF;
3453 --
3454 --
3455 IF (l_return_status = WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR) THEN
3456 x_return_status := l_return_status;
3457 END IF;
3458 --
3459 --}
3460 END IF;
3461 --
3462 -- K LPN CONV. rv
3463 --
3464 --
3465 -- Debug Statements
3466 --
3467 IF l_debug_on THEN
3468 WSH_DEBUG_SV.logmsg(l_module_name,'LOCK_ERROR exception has occured.',WSH_DEBUG_SV.C_EXCEP_LEVEL);
3469 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:LOCK_ERROR');
3470 END IF;
3471 --
3472 WHEN cannot_unassign THEN
3473 IF c_is_first_leg%isopen THEN
3474 close c_is_first_leg;
3475 END IF;
3476 FND_MESSAGE.SET_NAME('WSH','WSH_DEL_UNASSIGN_ERROR');
3477 x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
3478 wsh_util_core.add_message(x_return_status);
3479 --
3480 -- K LPN CONV. rv
3481 --
3482 IF WSH_WMS_LPN_GRP.G_CALLBACK_REQUIRED = 'Y'
3483 THEN
3484 --{
3485 IF l_debug_on THEN
3486 WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_LPN_SYNC_COMM_PKG.SYNC_LPNS_TO_WMS',WSH_DEBUG_SV.C_PROC_LEVEL);
3487 END IF;
3488
3489 WSH_LPN_SYNC_COMM_PKG.SYNC_LPNS_TO_WMS
3490 (
3491 p_in_rec => l_lpn_in_sync_comm_rec,
3492 x_return_status => l_return_status,
3493 x_out_rec => l_lpn_out_sync_comm_rec
3494 );
3495 --
3496 --
3497 IF l_debug_on THEN
3498 WSH_DEBUG_SV.log(l_module_name,'Return status after calling WSH_LPN_SYNC_COMM_PKG.SYNC_LPNS_TO_WMS',l_return_status);
3499 END IF;
3500 --
3501 --
3502 IF (l_return_status = WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR) THEN
3503 x_return_status := l_return_status;
3504 END IF;
3505 --
3506 --}
3507 END IF;
3508 --
3509 -- K LPN CONV. rv
3510 --
3511 --
3512 -- Debug Statements
3513 --
3514 IF l_debug_on THEN
3515 WSH_DEBUG_SV.logmsg(l_module_name,'CANNOT_UNASSIGN exception has occured.',WSH_DEBUG_SV.C_EXCEP_LEVEL);
3516 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:CANNOT_UNASSIGN');
3517 END IF;
3518 --
3519 WHEN others THEN
3520 iF c_is_first_leg%isopen THEN
3521 close c_is_first_leg;
3522 END IF;
3523 wsh_util_core.default_handler('WSH_DELIVERY_LEGS_ACTIONS.UNASSIGN_DELIVERIES');
3524 x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR;
3525 IF l_debug_on THEN
3526 WSH_DEBUG_SV.logmsg(l_module_name,'Unexpected error has occured. Oracle error message is '|| SQLERRM,WSH_DEBUG_SV.C_UNEXPEC_ERR_LEVEL);
3527 END IF;
3528 --
3529 -- K LPN CONV. rv
3530 --
3531 IF WSH_WMS_LPN_GRP.G_CALLBACK_REQUIRED = 'Y'
3532 THEN
3533 --{
3534 IF l_debug_on THEN
3535 WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_LPN_SYNC_COMM_PKG.SYNC_LPNS_TO_WMS',WSH_DEBUG_SV.C_PROC_LEVEL);
3536 END IF;
3537
3538 WSH_LPN_SYNC_COMM_PKG.SYNC_LPNS_TO_WMS
3539 (
3540 p_in_rec => l_lpn_in_sync_comm_rec,
3541 x_return_status => l_return_status,
3542 x_out_rec => l_lpn_out_sync_comm_rec
3543 );
3544 --
3545 --
3546 IF l_debug_on THEN
3547 WSH_DEBUG_SV.log(l_module_name,'Return status after calling WSH_LPN_SYNC_COMM_PKG.SYNC_LPNS_TO_WMS',l_return_status);
3548 END IF;
3549 --
3550 --
3551 --}
3552 END IF;
3553 --
3554 -- K LPN CONV. rv
3555 --
3556
3557 --
3558 -- Debug Statements
3559 --
3560 IF l_debug_on THEN
3561 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:OTHERS');
3562 END IF;
3563 --
3564 END Unassign_Deliveries;
3565
3566
3567 /* H integration: Pricing integration csun
3568 */
3569 --
3570 -- Name Mark_Reprice_Required
3571 -- Purpose This Procedure will set REPRICE_REQUIRED of
3572 -- delivery leg record
3573 --
3574 -- Input Arguments
3575 -- p_entity_type: entity type, valid values are
3576 -- 'DELIVERY_DETAIL', 'DELIVERY', 'TRIP',
3577 -- 'DELIVERY_LEG'
3578 -- p_entity_id : the entity id of the entity type
3579 --
3580 --TL Rating adding 'STOP' for call from create,update, delete stop
3581 --when trip mode is 'TRUCK'
3582
3583 PROCEDURE Mark_Reprice_Required(
3584 p_entity_type IN VARCHAR2,
3585 p_entity_ids IN WSH_UTIL_CORE.id_tab_type,
3586 p_consolidation_change IN VARCHAR2 DEFAULT 'N',
3587 x_return_status OUT NOCOPY VARCHAR2) IS
3588
3589 cursor get_trip_from_det( c_delivery_detail_id NUMBER) is
3590 SELECT dlg.delivery_leg_id, trip.trip_id, trip.consolidation_allowed,
3591 trip.lane_id, trip.mode_of_transport
3592 FROM wsh_delivery_legs dlg,
3593 wsh_delivery_assignments_v da,
3594 wsh_trips trip,
3595 wsh_trip_stops st
3596 WHERE da.delivery_detail_id = c_delivery_detail_id AND
3597 da.delivery_id = dlg.delivery_id AND
3598 dlg.pick_up_stop_id = st.stop_id AND
3599 st.trip_id = trip.trip_id;
3600
3601 cursor get_trip_from_del( c_delivery_id NUMBER) is
3602 SELECT dlg.delivery_leg_id, trip.trip_id, trip.consolidation_allowed,
3603 trip.lane_id, trip.mode_of_transport
3604 FROM wsh_delivery_legs dlg,
3605 wsh_trip_stops stop,
3606 wsh_trips trip
3607 WHERE dlg.delivery_id = c_delivery_id and
3608 dlg.pick_up_stop_id = stop.stop_id and
3609 stop.trip_id = trip.trip_id;
3610
3611 cursor get_trip_from_leg( c_leg_id NUMBER) is
3612 SELECT dlg.delivery_leg_id, trip.trip_id, trip.consolidation_allowed,
3613 trip.lane_id, trip.mode_of_transport
3614 FROM wsh_delivery_legs dlg,
3615 wsh_trip_stops stop,
3616 wsh_trips trip
3617 WHERE dlg.delivery_leg_id = c_leg_id AND
3618 dlg.pick_up_stop_id = stop.stop_id AND
3619 stop.trip_id = trip.trip_id;
3620
3621 --gets all legs for the trip the stop belongs to
3622 cursor get_trip_from_stop(c_stop_id NUMBER) is
3623 SELECT dlg.delivery_leg_id, trip.trip_id, trip.consolidation_allowed,
3624 trip.lane_id, trip.mode_of_transport
3625 FROM wsh_trip_stops stop,
3626 wsh_trips trip,
3627 wsh_delivery_legs dlg,
3628 wsh_trip_stops ts
3629 WHERE stop.stop_id = c_stop_id AND
3630 stop.trip_id = trip.trip_id AND
3631 ts.trip_id=trip.trip_id AND
3632 dlg.pick_up_stop_id = ts.stop_id;
3633
3634 cursor get_legs_from_trip( c_trip_id NUMBER) is
3635 SELECT dlg.delivery_leg_id, trip.trip_id, trip.consolidation_allowed,
3636 trip.lane_id, trip.mode_of_transport
3637 FROM wsh_trip_stops ts,
3638 wsh_delivery_legs dlg,
3639 wsh_trips trip
3640 WHERE ts.trip_id = c_trip_id AND
3641 dlg.pick_up_stop_id = ts.stop_id AND
3642 ts.trip_id = trip.trip_id;
3643
3644 cursor legs_priced_cur( c_trip_id NUMBER) is
3645 select 1
3646 from wsh_freight_costs wfc,
3647 wsh_trip_stops wts,
3648 wsh_delivery_legs wdl
3649 where wts.trip_id = c_trip_id
3650 and wdl.pick_up_stop_id = wts.stop_id
3651 and wfc.delivery_leg_id = wdl.delivery_leg_id
3652 and wfc.line_type_code = 'SUMMARY'
3653 and wfc.delivery_detail_id is null
3654 and NVL(wfc.total_amount,0) > 0;
3655
3656
3657 cursor leg_price_cur( c_dleg_id NUMBER) is
3658 select NVL(wfc.total_amount,0) price
3659 from wsh_freight_costs wfc
3660 where wfc.delivery_leg_id = c_dleg_id
3661 and wfc.line_type_code = 'SUMMARY'
3662 and wfc.delivery_detail_id is null
3663 and NVL(wfc.total_amount,0) > 0;
3664
3665 cursor c_lock_delivery_leg(c_delivery_leg_id NUMBER) is
3666 SELECT delivery_leg_id, status_code, reprice_required, parent_delivery_leg_id
3667 FROM wsh_delivery_legs
3668 WHERE delivery_leg_id = c_delivery_leg_id FOR UPDATE OF reprice_required NOWAIT;
3669
3670 -- Bug 4451383
3671 cursor get_trip_details ( c_trip_id NUMBER) is
3672 SELECT mode_of_transport, lane_id
3673 --SELECT consolidation_allowed, lane_id
3674 FROM wsh_trips
3675 WHERE trip_id = c_trip_id;
3676
3677 l_lock_delivery_leg_rec c_lock_delivery_leg%ROWTYPE;
3678
3679 l_del_leg_tab WSH_UTIL_CORE.Id_Tab_Type;
3680
3681 l_final_leg_tab WSH_UTIL_CORE.Id_Tab_Type;
3682 l_parent_leg_tab WSH_UTIL_CORE.Id_Tab_Type;
3683
3684 j NUMBER := 0;
3685 k NUMBER := 0;
3686
3687 l_duplicate NUMBER := 0;
3688
3689 invalid_parameter EXCEPTION;
3690
3691 l_at_least_one_leg_priced BOOLEAN;
3692 l_price NUMBER := 0;
3693
3694 -- Bug 4451383
3695 l_consolidation_allowed wsh_trips.consolidation_allowed%type;
3696 l_mode_of_transport wsh_trips.mode_of_transport%type;
3697 l_lane_id wsh_trips.lane_id%type;
3698
3699 delivery_leg_locked exception ;
3700 -- PRAGMA EXCEPTION_INIT(delivery_leg_locked, -54);
3701
3702 --for mode=TRUCK
3703 C_TRUCK VARCHAR2(5):='TRUCK';
3704
3705 --
3706 l_debug_on BOOLEAN;
3707 --
3708 l_module_name CONSTANT VARCHAR2(100) := 'wsh.plsql.' || G_PKG_NAME || '.' || 'MARK_REPRICE_REQUIRED';
3709 --
3710 BEGIN
3711 --
3712 -- Debug Statements
3713 --
3714 --
3715 l_debug_on := WSH_DEBUG_INTERFACE.g_debug;
3716 --
3717 IF l_debug_on IS NULL
3718 THEN
3719 l_debug_on := WSH_DEBUG_SV.is_debug_enabled;
3720 END IF;
3721
3722 IF l_debug_on THEN
3723 WSH_DEBUG_SV.push(l_module_name);
3724 --
3725 WSH_DEBUG_SV.log(l_module_name,'P_ENTITY_TYPE',P_ENTITY_TYPE);
3726 WSH_DEBUG_SV.log(l_module_name,'P_CONSOLIDATION_CHANGE',P_CONSOLIDATION_CHANGE);
3727 END IF;
3728 --
3729 x_return_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
3730
3731 --bug 3413328 - consolidation_allowed flag has been removed now from UI
3732 --removed checking for consolidation_allowed
3733
3734 IF WSH_UTIL_CORE.FTE_Is_Installed = 'Y' AND p_entity_ids.count > 0 THEN
3735 l_del_leg_tab.delete;
3736 l_final_leg_tab.delete;
3737
3738 IF p_entity_type = 'DELIVERY_DETAIL' THEN
3739 FOR i IN 1 .. p_entity_ids.count LOOP
3740 FOR l_leg IN get_trip_from_det(p_entity_ids(i)) LOOP
3741 l_at_least_one_leg_priced := FALSE;
3742 l_price := 0;
3743 --
3744 FOR leg_price_rec IN leg_price_cur(l_leg.delivery_leg_id) LOOP
3745 l_price := l_price+leg_price_rec.price;
3746 END LOOP;
3747 --
3748 IF l_price > 0 THEN
3749 j := j + 1;
3750 l_del_leg_tab(j) := l_leg.delivery_leg_id;
3751 l_at_least_one_leg_priced := TRUE;
3752 END IF;
3753 --
3754 IF (l_leg.lane_id is not null)
3755 --TL Rating
3756 OR l_leg.mode_of_transport=C_TRUCK THEN
3757
3758 IF NOT(l_at_least_one_leg_priced) THEN
3759 FOR legs_priced_rec IN legs_priced_cur( l_leg.trip_id ) LOOP
3760 l_at_least_one_leg_priced := TRUE;
3761 END LOOP;
3762 END IF;
3763 --
3764 IF l_at_least_one_leg_priced THEN
3765 FOR l_other IN get_legs_from_trip( l_leg.trip_id ) LOOP
3766 j := j + 1;
3767 l_del_leg_tab(j) := l_other.delivery_leg_id;
3768 END LOOP;
3769 END IF;
3770 END IF;
3771 END LOOP;
3772 END LOOP;
3773
3774 ELSIF p_entity_type = 'DELIVERY' THEN
3775 FOR i IN 1 .. p_entity_ids.count LOOP
3776 FOR l_leg IN get_trip_from_del(p_entity_ids(i)) LOOP
3777 l_at_least_one_leg_priced := FALSE;
3778 l_price := 0;
3779 --
3780 FOR leg_price_rec IN leg_price_cur(l_leg.delivery_leg_id) LOOP
3781 l_price := l_price+leg_price_rec.price;
3782 END LOOP;
3783 --
3784 IF l_price > 0 THEN
3785 j := j + 1;
3786 l_del_leg_tab(j) := l_leg.delivery_leg_id;
3787 l_at_least_one_leg_priced := TRUE;
3788 END IF;
3789
3790 IF (l_leg.lane_id is not null)
3791 --TL Rating
3792 OR l_leg.mode_of_transport=C_TRUCK THEN
3793
3794 IF NOT(l_at_least_one_leg_priced) THEN
3795 FOR legs_priced_rec IN legs_priced_cur( l_leg.trip_id ) LOOP
3796 l_at_least_one_leg_priced := TRUE;
3797 END LOOP;
3798 END IF;
3799 --
3800 IF l_at_least_one_leg_priced THEN
3801 FOR l_other IN get_legs_from_trip( l_leg.trip_id ) LOOP
3802 j := j + 1;
3803 l_del_leg_tab(j) := l_other.delivery_leg_id;
3804 END LOOP;
3805 END IF;
3806 END IF;
3807
3808 END LOOP;
3809
3810 END LOOP;
3811
3812 ELSIF p_entity_type = 'DELIVERY_LEG' THEN
3813 FOR i IN 1 .. p_entity_ids.count LOOP
3814 FOR l_leg IN get_trip_from_leg(p_entity_ids(i)) LOOP
3815 --j := j + 1;
3816 --l_del_leg_tab(j) := l_leg.delivery_leg_id;
3817 l_at_least_one_leg_priced := FALSE;
3818 --
3819 --- Added the following condition for the Bug 4451383
3820 IF (l_leg.lane_id is not null
3821 OR l_leg.mode_of_transport=C_TRUCK
3822 )
3823 THEN
3824
3825 IF l_debug_on THEN
3826 WSH_DEBUG_SV.logmsg(l_module_name,'legs_priced_cur is being opened for dlegs');
3827 END IF;
3828
3829 FOR legs_priced_rec IN legs_priced_cur( l_leg.trip_id ) LOOP
3830 l_at_least_one_leg_priced := TRUE;
3831 END LOOP;
3832 END IF;
3833 --
3834 --
3835 IF ((l_leg.lane_id is not null)
3836 --TL Rating
3837 OR l_leg.mode_of_transport=C_TRUCK) AND l_at_least_one_leg_priced THEN
3838 FOR l_other IN get_legs_from_trip( l_leg.trip_id ) LOOP
3839 j := j + 1;
3840 l_del_leg_tab(j) := l_other.delivery_leg_id;
3841 --
3842 END LOOP;
3843 END IF;
3844 END LOOP;
3845 END LOOP;
3846 ELSIF p_entity_type = 'STOP' THEN
3847 FOR i IN 1 .. p_entity_ids.count LOOP
3848 FOR l_leg IN get_trip_from_stop(p_entity_ids(i)) LOOP
3849 l_at_least_one_leg_priced := FALSE;
3850
3851 --- Added the following condition for the Bug 4451383
3852 IF (l_leg.lane_id is not null
3853 OR l_leg.mode_of_transport=C_TRUCK
3854 )
3855 THEN
3856
3857 IF l_debug_on THEN
3858 WSH_DEBUG_SV.logmsg(l_module_name,'legs_priced_cur is being opened for stops');
3859 END IF;
3860
3861 FOR legs_priced_rec IN legs_priced_cur( l_leg.trip_id ) LOOP
3862 l_at_least_one_leg_priced := TRUE;
3863 END LOOP;
3864 END IF;
3865
3866 IF ((l_leg.lane_id is not null)
3867 --TL Rating
3868 OR l_leg.mode_of_transport=C_TRUCK) AND l_at_least_one_leg_priced THEN
3869 FOR l_other IN get_legs_from_trip( l_leg.trip_id ) LOOP
3870 j := j + 1;
3871 l_final_leg_tab(j) := l_other.delivery_leg_id;
3872 END LOOP;
3873 END IF;
3874 END LOOP;
3875 END LOOP;
3876
3877 ELSIF p_entity_type = 'TRIP' THEN
3878 FOR i IN 1 .. p_entity_ids.count LOOP
3879 l_at_least_one_leg_priced := FALSE;
3880 -- Bug 4451383 new cursor to get trip details
3881 open get_trip_details(p_entity_ids(i) );
3882 fetch get_trip_details into l_mode_of_transport,l_lane_id;
3883 close get_trip_details;
3884
3885 IF ( l_mode_of_transport = C_TRUCK OR
3886 l_lane_id is not null) THEN
3887
3888 IF l_debug_on THEN
3889 WSH_DEBUG_SV.logmsg(l_module_name,'legs_priced_cur is being opened for trips');
3890 END IF;
3891
3892 FOR legs_priced_rec IN legs_priced_cur( p_entity_ids(i) ) LOOP
3893 l_at_least_one_leg_priced := TRUE;
3894 END LOOP;
3895 END IF;
3896 --
3897 --
3898 IF l_at_least_one_leg_priced THEN
3899 FOR l_other IN get_legs_from_trip(p_entity_ids(i)) LOOP
3900 IF (l_other.lane_id is not null
3901 --TL Rating
3902 OR l_other.mode_of_transport=C_TRUCK) THEN
3903 j := j + 1;
3904 l_final_leg_tab(j) := l_other.delivery_leg_id;
3905 END IF;
3906 END LOOP;
3907 END IF;
3908 END LOOP;
3909
3910 ELSE
3911 RAISE invalid_parameter;
3912 END IF;
3913
3914 -- remove duplicate delivery leg ids
3915 IF p_entity_type NOT IN ('TRIP', 'STOP') THEN
3916 FOR i in 1 .. l_del_leg_tab.count LOOP
3917 IF i = 1 THEN
3918 l_final_leg_tab(1) := l_del_leg_tab(i);
3919 ELSE
3920 l_duplicate := 0;
3921 FOR k in 1 .. l_final_leg_tab.count LOOP
3922 IF l_del_leg_tab(i) = l_final_leg_tab(k) THEN
3923 l_duplicate := 1;
3924 exit;
3925 END IF;
3926 END LOOP;
3927 IF l_duplicate = 0 THEN
3928 l_final_leg_tab(l_final_leg_tab.count+1) := l_del_leg_tab(i);
3929 END IF;
3930 END IF;
3931 END LOOP;
3932 END IF;
3933
3934 FOR i in 1 .. l_final_leg_tab.count LOOP
3935 OPEN c_lock_delivery_leg(l_final_leg_tab(i));
3936 FETCH c_lock_delivery_leg INTO l_lock_delivery_leg_rec;
3937 IF c_lock_delivery_leg%FOUND THEN
3938 IF l_lock_delivery_leg_rec.reprice_required <> 'Y' THEN
3939 UPDATE wsh_delivery_legs
3940 SET reprice_required = 'Y'
3941 WHERE CURRENT OF c_lock_delivery_leg;
3942 IF l_lock_delivery_leg_rec.parent_delivery_leg_id IS NOT NULL THEN
3943 BEGIN
3944 WSH_DELIVERY_LEGS_PVT.lock_dlvy_leg_no_compare(p_dlvy_leg_id => l_lock_delivery_leg_rec.parent_delivery_leg_id);
3945 EXCEPTION
3946 WHEN OTHERS THEN
3947 IF c_lock_delivery_leg%ISOPEN THEN
3948 CLOSE c_lock_delivery_leg;
3949 END IF;
3950 x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
3951 FND_MESSAGE.Set_Name('WSH', 'WSH_DEL_LEG_LOCKED');
3952 WSH_UTIL_CORE.Add_Message(x_return_status);
3953 IF l_debug_on THEN
3954 WSH_DEBUG_SV.logmsg(l_module_name,'DELIVERY_LEG_LOCKED exception has occured.',WSH_DEBUG_SV.C_EXCEP_LEVEL);
3955 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:DELIVERY_LEG_LOCKED');
3956 END IF;
3957 RETURN;
3958 END;
3959 UPDATE wsh_delivery_legs
3960 SET reprice_required = 'Y'
3961 WHERE delivery_leg_id = l_lock_delivery_leg_rec.parent_delivery_leg_id
3962 AND NVL(reprice_required, 'N') <> 'Y';
3963 END IF;
3964 END IF;
3965 END IF;
3966 CLOSE c_lock_delivery_leg;
3967 END LOOP;
3968
3969 END IF; -- fte is installed and p_entity_ids is not empty
3970
3971 --
3972 -- Debug Statements
3973 --
3974 IF l_debug_on THEN
3975 WSH_DEBUG_SV.pop(l_module_name);
3976 END IF;
3977 --
3978 EXCEPTION
3979
3980 WHEN invalid_parameter THEN
3981 x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
3982 FND_MESSAGE.Set_Name('WSH', 'WSH_PUB_INVALID_PARAMETER');
3983 FND_MESSAGE.Set_Token('PARAMETER', 'P_ENTITY_TYPE');
3984 WSH_UTIL_CORE.Add_Message(x_return_status);
3985
3986 --
3987 -- Debug Statements
3988 --
3989 IF l_debug_on THEN
3990 WSH_DEBUG_SV.logmsg(l_module_name,'INVALID_PARAMETER exception has occured.',WSH_DEBUG_SV.C_EXCEP_LEVEL);
3991 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:INVALID_PARAMETER');
3992 END IF;
3993 --
3994 WHEN delivery_leg_locked THEN
3995 CLOSE c_lock_delivery_leg;
3996 x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
3997 FND_MESSAGE.Set_Name('WSH', 'WSH_DEL_LEG_LOCKED');
3998 WSH_UTIL_CORE.Add_Message(x_return_status);
3999
4000 --
4001 -- Debug Statements
4002 --
4003 IF l_debug_on THEN
4004 WSH_DEBUG_SV.logmsg(l_module_name,'DELIVERY_LEG_LOCKED exception has occured.',WSH_DEBUG_SV.C_EXCEP_LEVEL);
4005 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:DELIVERY_LEG_LOCKED');
4006 END IF;
4007 --
4008 WHEN others THEN
4009 wsh_util_core.default_handler('WSH_DELIVERY_LEGS_ACTIONS.Mark_Reprice_Required');
4010 x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR;
4011
4012 --
4013 -- Debug Statements
4014 --
4015 IF l_debug_on THEN
4016 WSH_DEBUG_SV.logmsg(l_module_name,'Unexpected error has occured. Oracle error message is '|| SQLERRM,WSH_DEBUG_SV.C_UNEXPEC_ERR_LEVEL);
4017 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:OTHERS');
4018 END IF;
4019 --
4020 END Mark_Reprice_Required;
4021
4022 -- FUNCTION: Check_Rate_Delivery
4023 -- PARAMETERS: p_delivery_id, p_freight_terms_code, p_shipment_direction
4024 -- DESCRIPTION: This API will take in a delivery id or the shipment direction and freight code
4025 -- of a delivery. It will return values of 'Y' or 'N' depending on whether the delivery
4026 -- needs to be rated or does not need to be rated based on the global parameter values.
4027
4028
4029 FUNCTION Check_Rate_Delivery (p_delivery_id IN NUMBER,
4030 p_freight_terms_code VARCHAR2,
4031 p_shipment_direction VARCHAR2,
4032 x_return_status out nocopy VARCHAR2)
4033 RETURN VARCHAR2 IS
4034
4035
4036 CURSOR c_del_info(p_del_id in number) is
4037 select freight_terms_code, nvl(shipment_direction, 'O')
4038 from wsh_new_deliveries where delivery_id = p_del_id;
4039
4040
4041 l_rate_delivery VARCHAR2(1);
4042 l_global_params WSH_SHIPPING_PARAMS_PVT.Global_Parameters_Rec_Typ;
4043 l_shipment_direction VARCHAR2(10);
4044 l_freight_terms_code VARCHAR2(30);
4045 l_debug_on BOOLEAN;
4046 l_module_name CONSTANT VARCHAR2(100) := 'wsh.plsql.' || G_PKG_NAME || '.' || 'Check_Rate_Delivery';
4047 invalid_global_params EXCEPTION;
4048 invalid_delivery EXCEPTION;
4049
4050
4051 BEGIN
4052
4053 x_return_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
4054 --
4055 l_debug_on := WSH_DEBUG_INTERFACE.g_debug;
4056 --
4057 IF l_debug_on IS NULL
4058 THEN
4059 l_debug_on := WSH_DEBUG_SV.is_debug_enabled;
4060 END IF;
4061 --
4062 IF l_debug_on THEN
4063 WSH_DEBUG_SV.push(l_module_name);
4064 WSH_DEBUG_SV.log(l_module_name,'p_delivery_id',p_delivery_id);
4065 END IF;
4066
4067
4068 WSH_SHIPPING_PARAMS_PVT.Get_Global_Parameters(
4069 x_param_info => l_global_params,
4070 x_return_status => x_return_status);
4071
4072 IF x_return_status IN (WSH_UTIL_CORE.G_RET_STS_ERROR, WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR) THEN
4073
4074 RAISE invalid_global_params;
4075
4076 END IF;
4077
4078
4079 IF p_shipment_direction IS NULL THEN
4080
4081 OPEN c_del_info(p_delivery_id);
4082 FETCH c_del_info INTO l_freight_terms_code, l_shipment_direction;
4083 IF c_del_info%NOTFOUND THEN
4084 RAISE invalid_delivery;
4085 END IF;
4086 CLOSE c_del_info;
4087
4088
4089 ELSE
4090
4091 l_freight_terms_code := p_freight_terms_code;
4092 l_shipment_direction := p_shipment_direction;
4093
4094 END IF;
4095
4096
4097
4098 IF l_shipment_direction in ('IO', 'O') THEN
4099
4100
4101
4102 IF l_freight_terms_code = l_global_params.skip_rate_ob_dels_fgt_term THEN
4103
4104 l_rate_delivery := 'N';
4105
4106 ELSE
4107
4108 l_rate_delivery := 'Y';
4109
4110 END IF;
4111
4112
4113 ELSIF l_shipment_direction = 'I' THEN
4114
4115 IF l_freight_terms_code = l_global_params.rate_ib_dels_fgt_term THEN
4116
4117 l_rate_delivery := 'Y';
4118
4119 ELSE
4120
4121 l_rate_delivery := 'N';
4122
4123 END IF;
4124
4125
4126 ELSIF l_shipment_direction = 'D' THEN
4127
4128 IF l_freight_terms_code = l_global_params.rate_ds_dels_fgt_term_id THEN
4129
4130 l_rate_delivery := 'Y';
4131
4132 ELSE
4133
4134 l_rate_delivery := 'N';
4135
4136 END IF;
4137
4138 END IF;
4139
4140 IF l_debug_on THEN
4141 WSH_DEBUG_SV.pop(l_module_name);
4142 END IF;
4143 RETURN l_rate_delivery;
4144
4145 EXCEPTION
4146
4147 WHEN invalid_delivery THEN
4148 x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
4149 FND_MESSAGE.Set_Name('WSH', 'WSH_INVALID_DELIVERY');
4150 WSH_UTIL_CORE.Add_Message(x_return_status);
4151 IF l_debug_on THEN
4152 WSH_DEBUG_SV.logmsg(l_module_name,'INVALID_DELIVERY exception has occured.',WSH_DEBUG_SV.C_EXCEP_LEVEL);
4153 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:INVALID_DELIVERY');
4154 END IF;
4155
4156 WHEN invalid_global_params THEN
4157 x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
4158 FND_MESSAGE.Set_Name('WSH', 'WSH_INVALID_GLOBAL_PARAMETER');
4159 WSH_UTIL_CORE.Add_Message(x_return_status);
4160 IF l_debug_on THEN
4161 WSH_DEBUG_SV.logmsg(l_module_name,'INVALID_PARAMETER exception has occured.',WSH_DEBUG_SV.C_EXCEP_LEVEL);
4162 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:INVALID_PARAMETER');
4163 END IF;
4164
4165 WHEN others THEN
4166 wsh_util_core.default_handler('WSH_TRIP_VALIDATIONS.Check_Rate_Content_Dels');
4167 x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR;
4168 IF l_debug_on THEN
4169 WSH_DEBUG_SV.logmsg(l_module_name,'Unexpected error has occured. Oracle error message is '|| SQLERRM,WSH_DEBUG_SV.C_UNEXPEC_ERR_LEVEL);
4170 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:OTHERS');
4171 END IF;
4172
4173 END Check_Rate_Delivery;
4174
4175 -- FUNCTION: Check_Rate_Check_Rate_Trip_Contents
4176 -- PARAMETERS: p_trip_id
4177 -- DESCRIPTION: This API will take in a trip_id, and it will return values of 'Y' or 'N'
4178 -- depending on whether the content deliveries need to be rated or do not need
4179 -- to be rated, or mixed based on the global parameter values.
4180
4181 FUNCTION Check_Rate_Trip_Contents(p_trip_id IN NUMBER,
4182 x_return_status OUT nocopy VARCHAR2)
4183 RETURN VARCHAR2 IS
4184
4185 CURSOR C_TRIP_DEL_RATE(p_trip_id IN NUMBER, p_freight_terms_code_o IN VARCHAR2,
4186 p_freight_terms_code_i IN VARCHAR2, p_freight_terms_code_d VARCHAR2) IS
4187 select wnd.delivery_id
4188 FROM wsh_new_deliveries wnd,
4189 wsh_trip_stops wts,
4190 wsh_delivery_legs wdl
4191 WHERE wts.stop_id = wdl.pick_up_stop_id AND
4192 wts.trip_id = p_trip_id AND
4193 wdl.delivery_id = wnd.delivery_id AND
4194 wnd.freight_terms_code <> p_freight_terms_code_o and
4195 NVL(wnd.shipment_direction, 'O') IN ('O', 'IO')
4196 and rownum = 1
4197 UNION
4198 select wnd.delivery_id
4199 FROM wsh_new_deliveries wnd,
4200 wsh_trip_stops wts,
4201 wsh_delivery_legs wdl
4202 WHERE wts.stop_id = wdl.pick_up_stop_id AND
4203 wts.trip_id = p_trip_id AND
4204 wdl.delivery_id = wnd.delivery_id AND
4205 wnd.freight_terms_code <> p_freight_terms_code_i and
4206 wnd.shipment_direction = 'I'
4207 and rownum = 1
4208 UNION
4209 select wnd.delivery_id
4210 FROM wsh_new_deliveries wnd,
4211 wsh_trip_stops wts,
4212 wsh_delivery_legs wdl
4213 WHERE wts.stop_id = wdl.pick_up_stop_id AND
4214 wts.trip_id = p_trip_id AND
4215 wdl.delivery_id = wnd.delivery_id AND
4216 wnd.freight_terms_code <> p_freight_terms_code_d and
4217 wnd.shipment_direction = 'D'
4218 and rownum = 1;
4219
4220 CURSOR C_TRIP_DEL_NORATE(p_trip_id IN NUMBER, p_freight_terms_code_o IN VARCHAR2,
4221 p_freight_terms_code_i IN VARCHAR2, p_freight_terms_code_d VARCHAR2) IS
4222 select wnd.delivery_id
4223 FROM wsh_new_deliveries wnd,
4224 wsh_trip_stops wts,
4225 wsh_delivery_legs wdl
4226 WHERE wts.stop_id = wdl.pick_up_stop_id AND
4227 wts.trip_id = p_trip_id AND
4228 wdl.delivery_id = wnd.delivery_id AND
4229 wnd.freight_terms_code = p_freight_terms_code_o and
4230 NVL(wnd.shipment_direction, 'O') IN ('O', 'IO')
4231 and rownum = 1
4232 UNION
4233 select wnd.delivery_id
4234 FROM wsh_new_deliveries wnd,
4235 wsh_trip_stops wts,
4236 wsh_delivery_legs wdl
4237 WHERE wts.stop_id = wdl.pick_up_stop_id AND
4238 wts.trip_id = p_trip_id AND
4239 wdl.delivery_id = wnd.delivery_id AND
4240 wnd.freight_terms_code <> p_freight_terms_code_i and
4241 wnd.shipment_direction = 'I'
4242 and rownum = 1
4243 UNION
4244 select wnd.delivery_id
4245 FROM wsh_new_deliveries wnd,
4246 wsh_trip_stops wts,
4247 wsh_delivery_legs wdl
4248 WHERE wts.stop_id = wdl.pick_up_stop_id AND
4249 wts.trip_id = p_trip_id AND
4250 wdl.delivery_id = wnd.delivery_id AND
4251 wnd.freight_terms_code <> p_freight_terms_code_d and
4252 wnd.shipment_direction = 'D'
4253 and rownum = 1;
4254
4255
4256
4257 l_rate_trip_dels VARCHAR2(1);
4258 l_global_params WSH_SHIPPING_PARAMS_PVT.Global_Parameters_Rec_Typ;
4259 l_debug_on BOOLEAN;
4260 l_dummy_del NUMBER;
4261 l_module_name CONSTANT VARCHAR2(100) := 'wsh.plsql.' || G_PKG_NAME || '.' || 'Check_Rate_Delivery';
4262
4263 invalid_global_params EXCEPTION;
4264 invalid_trip EXCEPTION;
4265
4266 BEGIN
4267
4268 x_return_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
4269 --
4270 l_debug_on := WSH_DEBUG_INTERFACE.g_debug;
4271 --
4272 IF l_debug_on IS NULL
4273 THEN
4274 l_debug_on := WSH_DEBUG_SV.is_debug_enabled;
4275 END IF;
4276 --
4277 IF l_debug_on THEN
4278 WSH_DEBUG_SV.push(l_module_name);
4279 WSH_DEBUG_SV.log(l_module_name,'p_trip_id',p_trip_id);
4280 END IF;
4281
4282
4283 IF p_trip_id is NULL THEN
4284
4285 RAISE invalid_trip;
4286
4287 ELSE
4288
4289
4290 WSH_SHIPPING_PARAMS_PVT.Get_Global_Parameters(
4291 x_param_info => l_global_params,
4292 x_return_status => x_return_status);
4293
4294 IF x_return_status in (WSH_UTIL_CORE.G_RET_STS_ERROR, WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR) THEN
4295 RAISE invalid_global_params;
4296 END IF;
4297
4298 OPEN C_TRIP_DEL_RATE(p_trip_id,
4299 l_global_params.skip_rate_ob_dels_fgt_term,
4300 l_global_params.rate_ib_dels_fgt_term,
4301 l_global_params.rate_ds_dels_fgt_term_id);
4302 FETCH C_TRIP_DEL_RATE INTO l_dummy_del;
4303 IF C_TRIP_DEL_RATE%FOUND THEN
4304 l_rate_trip_dels := 'Y';
4305 IF l_debug_on THEN
4306 WSH_DEBUG_SV.log(l_module_name,'l_rate_trip_dels 1',l_rate_trip_dels);
4307 END IF;
4308 END IF;
4309 CLOSE C_TRIP_DEL_RATE;
4310
4311
4312 OPEN C_TRIP_DEL_NORATE(p_trip_id,
4313 l_global_params.skip_rate_ob_dels_fgt_term,
4314 l_global_params.rate_ib_dels_fgt_term,
4315 l_global_params.rate_ds_dels_fgt_term_id);
4316 FETCH C_TRIP_DEL_NORATE INTO l_dummy_del;
4317 IF C_TRIP_DEL_NORATE%FOUND THEN
4318 IF l_rate_trip_dels = 'Y' THEN
4319 l_rate_trip_dels := 'M';
4320 IF l_debug_on THEN
4321 WSH_DEBUG_SV.log(l_module_name,'l_rate_trip_dels 2',l_rate_trip_dels);
4322 END IF;
4323 ELSE
4324 l_rate_trip_dels := 'N';
4325 IF l_debug_on THEN
4326 WSH_DEBUG_SV.log(l_module_name,'l_rate_trip_dels 3',l_rate_trip_dels);
4327 END IF;
4328 END IF;
4329 END IF;
4330 CLOSE C_TRIP_DEL_NORATE;
4331
4332 END IF;
4333
4334 IF l_debug_on THEN
4335 WSH_DEBUG_SV.log(l_module_name,'l_rate_trip_dels',l_rate_trip_dels);
4336 WSH_DEBUG_SV.pop(l_module_name);
4337 END IF;
4338 RETURN l_rate_trip_dels;
4339
4340 EXCEPTION
4341
4342 WHEN invalid_trip THEN
4343 x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
4344 FND_MESSAGE.Set_Name('WSH', 'WSH_INVALID_TRIP');
4345 WSH_UTIL_CORE.Add_Message(x_return_status);
4346 IF l_debug_on THEN
4347 WSH_DEBUG_SV.logmsg(l_module_name,'INVALID_DELIVERY exception has occured.',WSH_DEBUG_SV.C_EXCEP_LEVEL);
4348 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:INVALID_DELIVERY');
4349 END IF;
4350
4351 WHEN invalid_global_params THEN
4352 x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
4353 FND_MESSAGE.Set_Name('WSH', 'WSH_INVALID_GLOBAL_PARAMETER');
4354 WSH_UTIL_CORE.Add_Message(x_return_status);
4355 IF l_debug_on THEN
4356 WSH_DEBUG_SV.logmsg(l_module_name,'INVALID_PARAMETER exception has occured.',WSH_DEBUG_SV.C_EXCEP_LEVEL);
4357 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:INVALID_PARAMETER');
4358 END IF;
4359
4360 WHEN others THEN
4361 IF c_trip_del_norate%isopen THEN
4362 close c_trip_del_norate;
4363 END IF;
4364 IF c_trip_del_rate%isopen THEN
4365 close c_trip_del_rate;
4366 END IF;
4367 wsh_util_core.default_handler('Check_Rate_Content_Dels');
4368 x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR;
4369 IF l_debug_on THEN
4370 WSH_DEBUG_SV.logmsg(l_module_name,'Unexpected error has occured. Oracle error message is '|| SQLERRM,WSH_DEBUG_SV.C_UNEXPEC_ERR_LEVEL);
4371 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:OTHERS');
4372 END IF;
4373
4374 END Check_Rate_Trip_Contents;
4375
4376
4377
4378 END WSH_DELIVERY_LEGS_ACTIONS;
[Home] [Help]