[Home] [Help]
PACKAGE BODY: APPS.WSH_DELIVERY_LEGS_PVT
Source
1 PACKAGE BODY WSH_DELIVERY_LEGS_PVT as
2 /* $Header: WSHDGTHB.pls 120.2 2007/01/05 01:03:45 jishen noship $ */
3
4 --
5 -- Procedure: Create_Delivery_Leg
6 -- Parameters: All Attributes of a Delivery Leg Record
7 -- Description: This procedure will create a delivery leg. It will
8 -- return to the user the delivery_leg_id. This is a
9 -- table handler style procedure and no additional
10 -- validations are provided.
11 --
12
13 --
14 G_PKG_NAME CONSTANT VARCHAR2(50) := 'WSH_DELIVERY_LEGS_PVT';
15 --
16 PROCEDURE Create_Delivery_Leg (
17 p_delivery_leg_info IN Delivery_Leg_Rec_Type,
18 x_rowid OUT NOCOPY VARCHAR2,
19 x_delivery_leg_id OUT NOCOPY NUMBER,
20 x_return_status OUT NOCOPY VARCHAR2
21 ) IS
22
23 CURSOR get_rowid IS
24 SELECT rowid
25 FROM wsh_delivery_legs
26 WHERE delivery_leg_id = x_delivery_leg_id;
27
28 CURSOR get_next_delivery_leg IS
29 SELECT wsh_delivery_legs_s.nextval
30 FROM sys.dual;
31
32 /* csun 02/25/2002 */
33 l_freight_cost_info WSH_FREIGHT_COSTS_PVT.Freight_Cost_Rec_Type;
34 l_row_id VARCHAR2(30);
35 l_freight_cost_id NUMBER ;
36 l_return_status VARCHAR2(1);
37 l_fte_install_status VARCHAR2(30);
38 l_industry VARCHAR2(30);
39 l_leg_id_tab WSH_UTIL_CORE.id_tab_type;
40
41 others EXCEPTION;
42 WSH_CREATE_FC_ERROR EXCEPTION;
43 mark_reprice_error EXCEPTION;
44
45 l_stop_tab WSH_UTIL_CORE.id_tab_type; -- DBI Project
46 l_dbi_rs VARCHAR2(1); -- DBI Project
47 --
48 l_debug_on BOOLEAN;
49 --
50 l_module_name CONSTANT VARCHAR2(100) := 'wsh.plsql.' || G_PKG_NAME || '.' || 'CREATE_DELIVERY_LEG';
51 --
52 BEGIN
53
54 --
55 --
56 l_debug_on := WSH_DEBUG_INTERFACE.g_debug;
57 --
58 IF l_debug_on IS NULL
59 THEN
60 l_debug_on := WSH_DEBUG_SV.is_debug_enabled;
61 END IF;
62 --
63 IF l_debug_on THEN
64 WSH_DEBUG_SV.push(l_module_name);
65 END IF;
66 --
67 OPEN get_next_delivery_leg;
68 FETCH get_next_delivery_leg INTO x_delivery_leg_id;
69 CLOSE get_next_delivery_leg;
70 IF l_debug_on THEN
71 WSH_DEBUG_SV.log(l_module_name,'x_delivery_leg_id',x_delivery_leg_id);
72 END IF;
73 INSERT INTO wsh_delivery_legs (
74 delivery_leg_id
75 ,delivery_id
76 ,sequence_number
77 ,pick_up_stop_id
78 ,drop_off_stop_id
79 ,gross_weight
80 ,net_weight
81 ,weight_uom_code
82 ,volume
83 ,volume_uom_code
84 ,creation_date
85 ,created_by
86 ,last_update_date
87 ,last_updated_by
88 ,last_update_login
89 ,program_application_id
90 ,program_id
91 ,program_update_date
92 ,request_id
93 ,load_tender_status
94 /* H Integration: datamodel changes wrudge */
95 ,fte_trip_id
96 ,reprice_required
97 ,actual_arrival_date
98 ,actual_departure_date
99 ,actual_receipt_date
100 ,tracking_drilldown_flag
101 ,status_code
102 ,tracking_remarks
103 ,carrier_est_departure_date
104 ,carrier_est_arrival_date
105 ,loading_start_datetime
106 ,loading_end_datetime
107 ,unloading_start_datetime
108 ,unloading_end_datetime
109 ,delivered_quantity
110 ,loaded_quantity
111 ,received_quantity
112 ,origin_stop_id
113 ,destination_stop_id
114 ,parent_delivery_leg_id
115 ) VALUES (
116 x_delivery_leg_id
117 ,p_delivery_leg_info.delivery_id
118 ,nvl(p_delivery_leg_info.sequence_number, -99)
119 ,nvl(p_delivery_leg_info.pick_up_stop_id, -99)
120 ,nvl(p_delivery_leg_info.drop_off_stop_id, -99)
121 ,p_delivery_leg_info.gross_weight
122 ,p_delivery_leg_info.net_weight
123 ,p_delivery_leg_info.weight_uom_code
124 ,p_delivery_leg_info.volume
125 ,p_delivery_leg_info.volume_uom_code
126 ,nvl(p_delivery_leg_info.creation_date, SYSDATE)
127 ,nvl(p_delivery_leg_info.created_by, FND_GLOBAL.USER_ID)
128 ,nvl(p_delivery_leg_info.last_update_date, SYSDATE)
129 ,nvl(p_delivery_leg_info.last_updated_by, FND_GLOBAL.USER_ID)
130 ,nvl(p_delivery_leg_info.last_update_login, FND_GLOBAL.LOGIN_ID)
131 ,p_delivery_leg_info.program_application_id
132 ,p_delivery_leg_info.program_id
133 ,p_delivery_leg_info.program_update_date
134 ,p_delivery_leg_info.request_id
135 ,nvl(p_delivery_leg_info.load_tender_status, 'N')
136 /* H Integration: datamodel changes wrudge */
137 ,p_delivery_leg_info.fte_trip_id
138 ,NVL(p_delivery_leg_info.reprice_required, 'N')
139 ,p_delivery_leg_info.actual_arrival_date
140 ,p_delivery_leg_info.actual_departure_date
141 ,p_delivery_leg_info.actual_receipt_date
142 ,p_delivery_leg_info.tracking_drilldown_flag
143 ,p_delivery_leg_info.status_code
144 ,p_delivery_leg_info.tracking_remarks
145 ,p_delivery_leg_info.carrier_est_departure_date
146 ,p_delivery_leg_info.carrier_est_arrival_date
147 ,p_delivery_leg_info.loading_start_datetime
148 ,p_delivery_leg_info.loading_end_datetime
149 ,p_delivery_leg_info.unloading_start_datetime
150 ,p_delivery_leg_info.unloading_end_datetime
151 ,p_delivery_leg_info.delivered_quantity
152 ,p_delivery_leg_info.loaded_quantity
153 ,p_delivery_leg_info.received_quantity
154 ,p_delivery_leg_info.origin_stop_id
155 ,p_delivery_leg_info.destination_stop_id
156 ,p_delivery_leg_info.parent_delivery_leg_id
157 );
158
159 --
160 -- DBI Project
161 -- Insert into WSH_DELIVERY_LEGS
162 -- Call DBI API after the Insert.
163 -- This API will also check for DBI Installed or not
164 l_stop_tab(1) := p_delivery_leg_info.pick_up_stop_id;
165 l_stop_tab(2) := p_delivery_leg_info.drop_off_stop_id;
166 IF l_debug_on THEN
167 WSH_DEBUG_SV.log(l_module_name,'Calling DBI API.Detail Count -',l_stop_tab.count);
168 END IF;
169 WSH_INTEGRATION.DBI_Update_Trip_Stop_Log
170 (p_stop_id_tab => l_stop_tab,
171 p_dml_type => 'UPDATE',
172 x_return_status => l_dbi_rs);
173
174 IF l_debug_on THEN
175 WSH_DEBUG_SV.log(l_module_name,'Return Status after DBI Call-',l_dbi_rs);
176 END IF;
177 IF l_dbi_rs = WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR THEN
178 x_return_status := l_dbi_rs;
179 -- just pass this return status to caller API
180 IF l_debug_on THEN
181 WSH_DEBUG_SV.log(l_module_name,'DBI API Returned Unexpected error '||x_return_status);
182 WSH_DEBUG_SV.pop(l_module_name);
183 END IF;
184 return;
185 END IF;
186 -- End of Code for DBI Project
187 --
188 OPEN get_rowid;
189 FETCH get_rowid INTO x_rowid;
190 IF l_debug_on THEN
191 WSH_DEBUG_SV.log(l_module_name,'Rows inserted',SQL%ROWCOUNT);
192 WSH_DEBUG_SV.log(l_module_name,'x_rowid',x_rowid);
193 END IF;
194 IF (get_rowid%NOTFOUND) THEN
195 CLOSE get_rowid;
196 RAISE others;
197 ELSE
198 x_return_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
199 END IF;
200
201 CLOSE get_rowid;
202 /* H integration: Pricing integration csun
203 */
204 IF WSH_UTIL_CORE.FTE_Is_Installed = 'Y' THEN
205 l_leg_id_tab(1) := x_delivery_leg_id;
206 --
207 IF l_debug_on THEN
208 WSH_DEBUG_SV.log(l_module_name,'FTE is installed');
209 END IF;
210 --
211 WSH_DELIVERY_LEGS_ACTIONS.Mark_Reprice_Required(
212 p_entity_type => 'DELIVERY_LEG',
213 p_entity_ids => l_leg_id_tab,
214 x_return_status => l_return_status);
215 IF l_return_status = WSH_UTIL_CORE.G_RET_STS_ERROR THEN
216 raise mark_reprice_error;
217 END IF;
218
219 l_freight_cost_info.freight_cost_id := NULL;
220 l_freight_cost_info.freight_cost_type_id := -1 ;
221 l_freight_cost_info.charge_source_code := NULL;
222 l_freight_cost_info.line_type_code := 'SUMMARY';
223 l_freight_cost_info.unit_amount := NULL;
224 l_freight_cost_info.currency_code := NULL;
225 l_freight_cost_info.delivery_leg_id := x_delivery_leg_id;
226 l_freight_cost_info.creation_date := SYSDATE;
227 l_freight_cost_info.created_by := FND_GLOBAL.USER_ID;
228 l_freight_cost_info.last_update_date := SYSDATE;
229 l_freight_cost_info.last_updated_by := FND_GLOBAL.USER_ID;
230 --
231 WSH_FREIGHT_COSTS_PVT.create_freight_cost(
232 p_freight_cost_info => l_freight_cost_info,
233 x_rowid => l_row_id,
234 x_freight_cost_id => l_freight_cost_id,
235 x_return_status => l_return_status );
236 IF l_return_status <> WSH_UTIL_CORE.G_RET_STS_SUCCESS THEN
237 raise WSH_CREATE_FC_ERROR;
238 END IF;
239
240 END IF;
241 --
242 IF l_debug_on THEN
243 WSH_DEBUG_SV.pop(l_module_name);
244 END IF;
245 --
246 EXCEPTION
247
248 WHEN mark_reprice_error THEN
249 FND_MESSAGE.SET_NAME('WSH', 'WSH_REPRICE_REQUIRED_ERR');
250 WSH_UTIL_CORE.add_message(l_return_status,l_module_name);
251 x_return_status := l_return_status;
252 --
253 IF l_debug_on THEN
254 WSH_DEBUG_SV.logmsg(l_module_name,'MARK_REPRICE_ERROR exception has occured.',WSH_DEBUG_SV.C_EXCEP_LEVEL);
255 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:MARK_REPRICE_ERROR');
256 END IF;
257 --
258 WHEN WSH_CREATE_FC_ERROR THEN
259 wsh_util_core.default_handler('WSH_DELIVERY_LEGS_PVT.CREATE_DELIVERY_LEG',l_module_name);
260 x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
261 --
262 IF l_debug_on THEN
263 WSH_DEBUG_SV.logmsg(l_module_name,'WSH_CREATE_FC_ERROR exception has occured.',WSH_DEBUG_SV.C_EXCEP_LEVEL);
264 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:WSH_CREATE_FC_ERROR');
265 END IF;
266 --
267 WHEN others THEN
268 wsh_util_core.default_handler('WSH_DELIVERY_LEGS_PVT.CREATE_DELIVERY_LEG',l_module_name);
269 x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR;
270 --
271 IF l_debug_on THEN
272 WSH_DEBUG_SV.logmsg(l_module_name,'Unexpected error has occured. Oracle error message is '|| SQLERRM,WSH_DEBUG_SV.C_UNEXPEC_ERR_LEVEL);
273 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:OTHERS');
274 END IF;
275 --
276 END Create_Delivery_Leg;
277
278
279 --
280 -- Procedure: Update_Delivery_Leg
281 -- Parameters: All Attributes of a Delivery Leg Record
282 -- Description: This procedure will update attributes of a delivery leg.
283 -- This is a table handler style procedure and no additional
284 -- validations are provided.
285 --
286
287 PROCEDURE Update_Delivery_Leg(
288 p_rowid IN VARCHAR2 := NULL,
289 p_delivery_leg_info IN Delivery_Leg_Rec_Type,
290 x_return_status OUT NOCOPY VARCHAR2
291 ) IS
292
293 CURSOR get_rowid IS
294 SELECT rowid
295 FROM wsh_delivery_legs
296 WHERE delivery_leg_id = p_delivery_leg_info.delivery_leg_id;
297
298 l_rowid VARCHAR2(30);
299
300 --
301 l_debug_on BOOLEAN;
302 --
303 l_module_name CONSTANT VARCHAR2(100) := 'wsh.plsql.' || G_PKG_NAME || '.' || 'UPDATE_DELIVERY_LEG';
304 --
305 BEGIN
306 --
307 --
308 l_debug_on := WSH_DEBUG_INTERFACE.g_debug;
309 --
310 IF l_debug_on IS NULL
311 THEN
312 l_debug_on := WSH_DEBUG_SV.is_debug_enabled;
313 END IF;
314 --
315 IF l_debug_on THEN
316 WSH_DEBUG_SV.push(l_module_name);
317 --
318 WSH_DEBUG_SV.log(l_module_name,'P_ROWID',P_ROWID);
319 END IF;
320 --
321 IF (p_rowid IS NULL) THEN
322 OPEN get_rowid;
323 FETCH get_rowid INTO l_rowid;
324 IF l_debug_on THEN
325 WSH_DEBUG_SV.log(l_module_name,'l_rowid',l_rowid);
326 END IF;
327 IF (get_rowid%NOTFOUND) THEN
328 CLOSE get_rowid;
329 RAISE no_data_found;
330 END IF;
331
332 CLOSE get_rowid;
333 ELSE
334 l_rowid := p_rowid;
335 END IF;
336
337 UPDATE wsh_delivery_legs
338 SET
339 delivery_leg_id = p_delivery_leg_info.delivery_leg_id
340 , delivery_id = p_delivery_leg_info.delivery_id
341 ,sequence_number = p_delivery_leg_info.sequence_number
342 ,pick_up_stop_id = p_delivery_leg_info.pick_up_stop_id
343 ,drop_off_stop_id = p_delivery_leg_info.drop_off_stop_id
344 ,Gross_Weight = p_delivery_leg_info.Gross_Weight
345 ,Net_Weight = p_delivery_leg_info.Net_Weight
346 ,Weight_Uom_Code = p_delivery_leg_info.Weight_Uom_Code
347 ,Volume = p_delivery_leg_info.Volume
348 ,Volume_Uom_Code = p_delivery_leg_info.Volume_Uom_Code
349 ,Last_Update_Date = NVL(p_delivery_leg_info.Last_Update_Date, sysdate)
350 ,Last_Updated_By = NVL(p_delivery_leg_info.Last_Updated_By, fnd_global.user_id)
351 ,Last_Update_Login = p_delivery_leg_info.Last_Update_Login
352 ,Program_Application_Id = p_delivery_leg_info.Program_Application_Id
353 ,Program_Id = p_delivery_leg_info.Program_Id
354 ,Program_Update_Date = p_delivery_leg_info.Program_Update_Date
355 ,Request_Id = p_delivery_leg_info.Request_Id
356 ,Load_Tender_Status = p_delivery_leg_info.Load_Tender_Status
357 /* Changes for the shipping data model Bug#1918342*/
358
359 ,SHIPPER_TITLE = p_delivery_leg_info.SHIPPER_TITLE
360 ,SHIPPER_PHONE = p_delivery_leg_info.SHIPPER_PHONE
361 ,POD_FLAG = p_delivery_leg_info.POD_FLAG
362 ,POD_BY = p_delivery_leg_info.POD_BY
363 ,POD_DATE = p_delivery_leg_info.POD_DATE
364 ,EXPECTED_POD_DATE = p_delivery_leg_info.EXPECTED_POD_DATE
365 ,BOOKING_OFFICE = p_delivery_leg_info.BOOKING_OFFICE
366 ,SHIPPER_EXPORT_REF = p_delivery_leg_info.SHIPPER_EXPORT_REF
367 ,CARRIER_EXPORT_REF = p_delivery_leg_info.CARRIER_EXPORT_REF
368 ,DOC_NOTIFY_PARTY = p_delivery_leg_info.DOC_NOTIFY_PARTY
369 ,AETC_NUMBER = p_delivery_leg_info.AETC_NUMBER
370 ,SHIPPER_SIGNED_BY = p_delivery_leg_info.SHIPPER_SIGNED_BY
371 ,SHIPPER_DATE = p_delivery_leg_info.SHIPPER_DATE
372 ,CARRIER_SIGNED_BY = p_delivery_leg_info.CARRIER_SIGNED_BY
373 ,CARRIER_DATE = p_delivery_leg_info.CARRIER_DATE
374 ,DOC_ISSUE_OFFICE = p_delivery_leg_info.DOC_ISSUE_OFFICE
375 ,DOC_ISSUED_BY = p_delivery_leg_info.DOC_ISSUED_BY
376 ,DOC_DATE_ISSUED = p_delivery_leg_info.DOC_DATE_ISSUED
377 ,SHIPPER_HM_BY = p_delivery_leg_info.SHIPPER_HM_BY
378 ,SHIPPER_HM_DATE = p_delivery_leg_info.SHIPPER_HM_DATE
379 ,CARRIER_HM_BY = p_delivery_leg_info.CARRIER_HM_BY
380 ,CARRIER_HM_DATE = p_delivery_leg_info.CARRIER_HM_DATE
381 ,BOOKING_NUMBER = p_delivery_leg_info.BOOKING_NUMBER
382 ,PORT_OF_LOADING = P_delivery_leg_info.PORT_OF_LOADING
383 ,PORT_OF_DISCHARGE = p_delivery_leg_info.PORT_OF_DISCHARGE
384 ,SERVICE_CONTRACT = p_delivery_leg_info.SERVICE_CONTRACT
385 ,BILL_FREIGHT_TO = p_delivery_leg_info.BILL_FREIGHT_TO
386 /* H Integration: datamodel changes wrudge */
387 ,FTE_TRIP_ID = p_delivery_leg_info.FTE_TRIP_ID
388 ,REPRICE_REQUIRED = p_delivery_leg_info.REPRICE_REQUIRED
389 ,ACTUAL_ARRIVAL_DATE = p_delivery_leg_info.ACTUAL_ARRIVAL_DATE
390 ,ACTUAL_DEPARTURE_DATE = p_delivery_leg_info.ACTUAL_DEPARTURE_DATE
391 ,ACTUAL_RECEIPT_DATE = p_delivery_leg_info.ACTUAL_RECEIPT_DATE
392 ,TRACKING_DRILLDOWN_FLAG = p_delivery_leg_info.TRACKING_DRILLDOWN_FLAG
393 ,STATUS_CODE = p_delivery_leg_info.STATUS_CODE
394 ,TRACKING_REMARKS = p_delivery_leg_info.TRACKING_REMARKS
395 ,CARRIER_EST_DEPARTURE_DATE = p_delivery_leg_info.CARRIER_EST_DEPARTURE_DATE
396 ,CARRIER_EST_ARRIVAL_DATE = p_delivery_leg_info.CARRIER_EST_ARRIVAL_DATE
397 ,LOADING_START_DATETIME = p_delivery_leg_info.LOADING_START_DATETIME
398 ,LOADING_END_DATETIME = p_delivery_leg_info.LOADING_END_DATETIME
399 ,UNLOADING_START_DATETIME = p_delivery_leg_info.UNLOADING_START_DATETIME
400 ,UNLOADING_END_DATETIME = p_delivery_leg_info.UNLOADING_END_DATETIME
401 ,DELIVERED_QUANTITY = p_delivery_leg_info.DELIVERED_QUANTITY
402 ,LOADED_QUANTITY = p_delivery_leg_info.LOADED_QUANTITY
403 ,RECEIVED_QUANTITY = p_delivery_leg_info.RECEIVED_QUANTITY
404 ,ORIGIN_STOP_ID = p_delivery_leg_info.ORIGIN_STOP_ID
405 ,DESTINATION_STOP_ID = p_delivery_leg_info.DESTINATION_STOP_ID
406 ,parent_delivery_leg_id = p_delivery_leg_info.parent_delivery_leg_id
407
408 WHERE rowid = l_rowid;
409 IF l_debug_on THEN
410 WSH_DEBUG_SV.log(l_module_name,'Rows updated',SQL%ROWCOUNT);
411 END IF;
412 IF (SQL%NOTFOUND) THEN
413 RAISE no_data_found;
414 ELSE
415 x_return_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
416 END IF;
417 --
418 IF l_debug_on THEN
419 WSH_DEBUG_SV.pop(l_module_name);
420 END IF;
421 --
422 EXCEPTION
423 WHEN no_data_found THEN
424 FND_MESSAGE.SET_NAME('WSH','WSH_LEG_NOT_FOUND');
425 x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
426 WSH_UTIL_CORE.ADD_MESSAGE(x_return_status,l_module_name);
427 --
428 IF l_debug_on THEN
429 WSH_DEBUG_SV.logmsg(l_module_name,'NO_DATA_FOUND exception has occured.',WSH_DEBUG_SV.C_EXCEP_LEVEL);
430 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:NO_DATA_FOUND');
431 END IF;
432 --
433 WHEN others THEN
434 wsh_util_core.default_handler('WSH_DELIVERY_LEGS_PVT.UPDATE_DELIVERY_LEG',l_module_name);
435 x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR;
436 --
437 IF l_debug_on THEN
438 WSH_DEBUG_SV.logmsg(l_module_name,'Unexpected error has occured. Oracle error message is '|| SQLERRM,WSH_DEBUG_SV.C_UNEXPEC_ERR_LEVEL);
439 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:OTHERS');
440 END IF;
441 --
442 END Update_Delivery_Leg;
443
444
445 --
446 -- Procedure: Delete_Delivery_Leg
447 -- Parameters: All Attributes of a Delivery Leg Record
448 -- Description: This procedure will delete a delivery Leg.
449 -- The order in which it looks at the parameters
450 -- are:
451 -- - p_rowid
452 -- - p_delivery_leg_id
453 -- This is a table handler style procedure and no additional
454 -- validations are provided.
455 --
456
457 PROCEDURE Delete_Delivery_Leg
458 (p_rowid IN VARCHAR2 := NULL,
459 p_delivery_leg_id IN NUMBER := NULL,
460 x_return_status OUT NOCOPY VARCHAR2
461 ) IS
462
463 CURSOR get_del_leg_id_rowid (v_rowid VARCHAR2) IS
464 SELECT delivery_leg_id
465 FROM wsh_delivery_legs
466 WHERE rowid = v_rowid;
467
468 CURSOR check_docs (l_leg_id NUMBER) IS
469 SELECT entity_id
470 FROM wsh_document_instances
471 WHERE entity_id = l_leg_id AND
472 entity_name = 'WSH_DELIVERY_LEGS' AND
473 status <> 'CANCELLED'
474 FOR UPDATE NOWAIT;
475
476 -- DBI Project
477 CURSOR get_stop_ids(v_del_leg_id NUMBER) IS
478 SELECT pick_up_stop_id,drop_off_stop_id
479 FROM wsh_delivery_legs
480 WHERE delivery_leg_id = v_del_leg_id;
481
482 l_stop_tab WSH_UTIL_CORE.id_tab_type;
483 l_dbi_rs VARCHAR2(1);
484 --
485
486 l_delivery_leg_id NUMBER;
487 l_doc_id NUMBER := NULL;
488 l_msg_data VARCHAR2(2000);
489 l_msg_count NUMBER;
490 /* csun 02/25/2002 */
491 l_fte_install_status VARCHAR2(30);
492 l_industry VARCHAR2(30);
493
494 others EXCEPTION;
495
496 --
497 l_debug_on BOOLEAN;
498 --
499 l_module_name CONSTANT VARCHAR2(100) := 'wsh.plsql.' || G_PKG_NAME || '.' || 'DELETE_DELIVERY_LEG';
500 --
501
502 --
503 -- Get stop and trip information for the delivery leg.
504 --
505 CURSOR leg_csr (p_delivery_leg_id IN NUMBER)
506 IS
507 SELECT pick_up_stop_id, drop_off_stop_id, wts1.trip_id,
508 nvl(shipment_direction,'O') shipment_direction,
509 wt.name trip_name,
510 wdl.delivery_id --J-IB-HEALI
511 FROM wsh_delivery_legs wdl,
512 wsh_trip_stops wts1,
513 wsh_new_deliveries wnd,
514 wsh_trips wt
515 WHERE delivery_leg_id = p_delivery_leg_id
516 AND wdl.pick_up_stop_id = wts1.stop_id
517 AND wdl.delivery_id = wnd.delivery_id
518 AND wts1.trip_id = wt.trip_id;
519 --
520 --
521 leg_rec leg_csr%ROWTYPE;
522 --
523 -- Lock trip
524 --
525 CURSOR lock_trip_csr (p_trip_id in number, p_pickup_stop_id IN NUMBER, p_dropoff_stop_id IN NUMBER)
526 IS
527 SELECT NVL(wts1.shipments_type_flag,'O') pu_stop_shipments_type_flag,
528 NVL(wts2.shipments_type_flag,'O') do_stop_shipments_type_flag,
529 NVL(wt.shipments_type_flag,'O') trip_shipments_type_flag,
530 wt.status_Code trip_status_Code
531 FROM wsh_trip_stops wts1,
532 wsh_trip_stops wts2,
533 wsh_trips wt
534 WHERE wt.trip_id = p_trip_id
535 AND wts1.stop_id = p_pickup_stop_id
536 AND wts2.stop_id = p_dropoff_stop_id
537 FOR UPDATE OF wt.shipments_type_flag NOWAIT;
538 --
539 lock_trip_rec lock_trip_csr%ROWTYPE;
540 --
541 CURSOR dlvy_csr (p_stop_id IN NUMBER)
542 IS
543 SELECT 1
544 FROM wsh_delivery_legs wdl,
545 wsh_new_deliveries wnd
546 WHERE wdl.delivery_id = wnd.delivery_id
547 AND wnd.status_code IN ('IT','CL')
548 AND ( wdl.pick_up_stop_id = p_stop_id or wdl.drop_off_stop_id = p_stop_id)
549 AND rownum = 1;
550 --
551 RECORD_LOCKED EXCEPTION;
552 PRAGMA EXCEPTION_INIT(RECORD_LOCKED, -54);
553 --
554 l_pu_stop_shipType_flag_orig VARCHAR2(30);
555 l_do_stop_shipType_flag_orig VARCHAR2(30);
556 --
557 l_num_warnings NUMBER := 0;
558 l_num_errors NUMBER := 0;
559 l_return_status VARCHAR2(10);
560 l_stop_rec WSH_TRIP_STOPS_PVT.TRIP_STOP_REC_TYPE;
561 l_pub_stop_rec WSH_TRIP_STOPS_PUB.TRIP_STOP_PUB_REC_TYPE;
562 l_trip_rec WSH_TRIPS_PVT.TRIP_REC_TYPE;
563 --
564 l_has_mixed_deliveries VARCHAR2(10);
565 l_stop_opened VARCHAR2(10);
566 l_stop_in_rec WSH_TRIP_STOPS_VALIDATIONS.chkClose_in_rec_type;
567 l_leg_complete boolean;
568
569 l_gc3_is_installed VARCHAR2(1); --OTM R12
570
571 BEGIN
572
573 --
574 --
575 l_debug_on := WSH_DEBUG_INTERFACE.g_debug;
576 --
577 IF l_debug_on IS NULL
578 THEN
579 l_debug_on := WSH_DEBUG_SV.is_debug_enabled;
580 END IF;
581 --
582 IF l_debug_on THEN
583 WSH_DEBUG_SV.push(l_module_name);
584 --
585 WSH_DEBUG_SV.log(l_module_name,'P_ROWID',P_ROWID);
586 WSH_DEBUG_SV.log(l_module_name,'P_DELIVERY_LEG_ID',P_DELIVERY_LEG_ID);
587 END IF;
588 --
589 x_return_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
590
591 --OTM R12
592 l_gc3_is_installed := WSH_UTIL_CORE.G_GC3_IS_INSTALLED; -- this is global variable
593
594 IF l_gc3_is_installed IS NULL THEN
595 l_gc3_is_installed := WSH_UTIL_CORE.GC3_IS_INSTALLED; -- this is actual function
596 END IF;
597 --
598
599 IF p_rowid IS NOT NULL THEN
600 OPEN get_del_leg_id_rowid(p_rowid);
601 FETCH get_del_leg_id_rowid INTO l_delivery_leg_id;
602 CLOSE get_del_leg_id_rowid;
603 IF l_debug_on THEN
604 WSH_DEBUG_SV.log(l_module_name,'l_delivery_leg_id',l_delivery_leg_id);
605 END IF;
606 END IF;
607
608 IF l_delivery_leg_id IS NULL THEN
609 l_delivery_leg_id := p_delivery_leg_id;
610 END IF;
611
612 IF l_delivery_leg_id IS NOT NULL THEN
613
614 -- J-IB-NPARIKH-{
615 --
616 --
617 BEGIN
618 --{
619 OPEN leg_csr(l_delivery_leg_id);
620 --
621 FETCH leg_csr INTO leg_rec;
622 --
623 CLOSE leg_csr;
624 --
625 -- Lock the trip
626 --
627 OPEN lock_trip_csr( leg_rec.trip_id, leg_rec.pick_up_stop_id, leg_rec.drop_off_stop_id);
628 --
629 FETCH lock_trip_csr INTO lock_trip_rec;
630 --
631 CLOSE lock_trip_csr;
632 --
633 IF l_debug_on THEN
634 WSH_DEBUG_SV.log(l_module_name,'leg_rec.shipment_direction',leg_rec.shipment_direction);
635 WSH_DEBUG_SV.log(l_module_name,'lock_trip_rec.pu_stop_shipments_type_flag',lock_trip_rec.pu_stop_shipments_type_flag);
636 WSH_DEBUG_SV.log(l_module_name,'lock_trip_rec.do_stop_shipments_type_flag',lock_trip_rec.do_stop_shipments_type_flag);
637 WSH_DEBUG_SV.log(l_module_name,'lock_trip_rec.trip_status_code',lock_trip_rec.trip_status_code);
638
639 END IF;
640 --
641 --
642 l_pu_stop_shipType_flag_orig := lock_trip_rec.pu_stop_shipments_type_flag;
643 l_do_stop_shipType_flag_orig := lock_trip_rec.do_stop_shipments_type_flag;
644 --}
645 EXCEPTION
646 --{
647 WHEN RECORD_LOCKED THEN
648 FND_MESSAGE.SET_NAME('WSH','WSH_TRIP_LOCK_FAILED');
649 FND_MESSAGE.SET_TOKEN('ENTITY_NAME', leg_rec.trip_name);
650 wsh_util_core.add_message(WSH_UTIL_CORE.G_RET_STS_ERROR);
651 RAISE FND_API.G_EXC_ERROR;
652 --}
653 END;
654 --
655 --
656 --
657 -- J-IB-NPARIKH-}
658
659
660 -- Check if documents exist for this delivery leg
661
662 OPEN check_docs(l_delivery_leg_id);
663 FETCH check_docs INTO l_doc_id;
664 CLOSE check_docs;
665 IF l_debug_on THEN
666 WSH_DEBUG_SV.log(l_module_name,'l_doc_id',l_doc_id);
667 END IF;
668 IF (l_doc_id IS NOT NULL) THEN
669 wsh_document_pvt.cancel_all_documents(
670 p_api_version => 1.0,
671 x_return_status => x_return_status,
672 x_msg_count => l_msg_count,
673 x_msg_data => l_msg_data,
674 p_entity_name => 'WSH_DELIVERY_LEGS',
675 p_entity_id => p_delivery_leg_id);
676
677 IF (x_return_status <> WSH_UTIL_CORE.G_RET_STS_SUCCESS) THEN
678 --
679 IF l_debug_on THEN
680 WSH_DEBUG_SV.log(l_module_name,'x_return_status',
681 x_return_status);
682 WSH_DEBUG_SV.pop(l_module_name);
683 END IF;
684 --
685 RETURN;
686 ELSE
687 l_num_warnings := l_num_warnings + 1;
688 FND_MESSAGE.SET_NAME('WSH','WSH_LEG_DOCS_CANCELLED');
689 x_return_status := WSH_UTIL_CORE.G_RET_STS_WARNING;
690 wsh_util_core.add_message(x_return_status,l_module_name);
691 END IF;
692
693 END IF;
694
695 -- DBI Project
696 Open get_stop_ids(l_delivery_leg_id);
697 Fetch get_stop_ids into l_stop_tab(1),l_stop_tab(2);
698 Close get_stop_ids;
699 --
700
701 DELETE FROM wsh_delivery_legs
702 WHERE delivery_leg_id = l_delivery_leg_id;
703
704
705 IF (SQL%NOTFOUND) THEN
706
707 FND_MESSAGE.SET_NAME('WSH','WSH_LEG_NOT_FOUND');
708 x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
709 WSH_UTIL_CORE.ADD_MESSAGE(x_return_status,l_module_name);
710 IF l_debug_on THEN
711 WSH_DEBUG_SV.log(l_module_name,'WSH_LEG_NOT_FOUND');
712 END IF;
713
714 RAISE FND_API.G_EXC_ERROR; -- J-IB-NPARIKH
715 END IF;
716
717
718 --
719 -- DBI Project
720 -- Delete from WSH_DELIVERY_LEGS
721 -- Call DBI API after the Update.
722 -- This API will also check for DBI Installed or not
723
724 IF l_debug_on THEN
725 WSH_DEBUG_SV.log(l_module_name,'Calling DBI API.Detail Count -',l_stop_tab.count);
726 END IF;
727 WSH_INTEGRATION.DBI_Update_Trip_Stop_Log
728 (p_stop_id_tab => l_stop_tab,
729 p_dml_type => 'UPDATE',
730 x_return_status => l_dbi_rs);
731
732 IF l_debug_on THEN
733 WSH_DEBUG_SV.log(l_module_name,'Return Status after DBI Call-',l_dbi_rs);
734 END IF;
735 IF l_dbi_rs = WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR THEN
736 x_return_status := l_dbi_rs;
737 -- just pass this return status to caller API
738 IF l_debug_on THEN
739 WSH_DEBUG_SV.log(l_module_name,'DBI API Returned Unexpected error '||x_return_status);
740 WSH_DEBUG_SV.pop(l_module_name);
741 END IF;
742 return;
743 END IF;
744 -- End of Code for DBI Project
745 --
746
747 /* H integration: Pricing integration csun
748 delete corresponding freight cost record, ignore if
749 the record is not found
750 */
751 IF WSH_UTIL_CORE.FTE_Is_Installed = 'Y'
752 --OTM R12, allow delete when OTM is installed
753 OR l_gc3_is_installed = 'Y'
754 --
755 THEN
756 DELETE FROM wsh_freight_costs
757 WHERE delivery_leg_id = l_delivery_leg_id;
758
759 IF l_debug_on THEN
760 WSH_DEBUG_SV.log(l_module_name,
761 'Rows deleted from wsh_freight_costs',SQL%ROWCOUNT);
762 END IF;
763 END IF;
764 --
765
766 -- J-IB-NPARIKH-{
767 --
768 -- Delivery leg has been deleted.
769 -- Recalculate value of shipments type flag for
770 -- pickup and dropoff stop
771 --
772 IF l_debug_on THEN
773 WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WWSH_TRIP_STOPS_VALIDATIONS.refreshShipmentsTypeFlag',WSH_DEBUG_SV.C_PROC_LEVEL);
774 END IF;
775 --
776 -- Recalculate value of shipments type flag for pickup stop
777 --
778 WSH_TRIP_STOPS_VALIDATIONS.refreshShipmentsTypeFlag
779 (
780 p_trip_id => leg_rec.trip_id,
781 p_stop_id => leg_rec.pick_up_stop_id,
782 p_action => 'UNASSIGN',
783 p_shipment_direction => leg_rec.shipment_direction,
784 x_shipments_type_flag => lock_trip_rec.pu_stop_shipments_type_flag,
785 x_return_status => l_return_status
786 );
787 --
788 wsh_util_core.api_post_call
789 (
790 p_return_status => l_return_status,
791 x_num_warnings => l_num_warnings,
792 x_num_errors => l_num_errors
793 );
794 --
795 --
796 IF lock_trip_rec.pu_stop_shipments_type_flag <> l_pu_stop_shipType_flag_orig
797 THEN
798 --{
799 -- Since pickup stop's shipments type flag
800 -- has changed,
801 -- call FTE API for validations
802 -- and then update trip stop with new value.
803 --
804 /* H integration - call Multi Leg FTE */
805 IF (WSH_UTIL_CORE.FTE_IS_INSTALLED = 'Y') THEN
806 -- Get pvt type record structure for stop
807 --
808 -- Debug Statements
809 --
810 IF l_debug_on THEN
811 WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_TRIP_STOPS_GRP.GET_STOP_DETAILS_PVT',WSH_DEBUG_SV.C_PROC_LEVEL);
812 END IF;
813 --
814 wsh_trip_stops_grp.get_stop_details_pvt
815 (p_stop_id => leg_rec.pick_up_stop_id,
816 x_stop_rec => l_stop_rec,
817 x_return_status => l_return_status);
818 --
819 wsh_util_core.api_post_call
820 (
821 p_return_status => l_return_status,
822 x_num_warnings => l_num_warnings,
823 x_num_errors => l_num_errors
824 );
825 --
826 --
827 IF l_debug_on THEN
828 WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_FTE_INTEGRATION.TRIP_STOP_VALIDATIONS',WSH_DEBUG_SV.C_PROC_LEVEL);
829 END IF;
830 --
831 wsh_fte_integration.trip_stop_validations
832 (p_stop_rec => l_stop_rec,
833 p_trip_rec => l_trip_rec,
834 p_action => 'UPDATE',
835 x_return_status => l_return_status);
836 --
837 wsh_util_core.api_post_call
838 (
839 p_return_status => l_return_status,
840 x_num_warnings => l_num_warnings,
841 x_num_errors => l_num_errors
842 );
843 --
844 END IF;
845
846 /* End of H integration - call Multi Leg FTE */
847 update wsh_trip_stops
848 set shipments_type_flag = lock_trip_rec.pu_stop_shipments_type_flag, -- J-IB-NPARIKH
849 last_update_date = SYSDATE,
850 last_updated_by = FND_GLOBAL.USER_ID,
851 last_update_login = FND_GLOBAL.LOGIN_ID
852 where stop_id = leg_rec.pick_up_stop_id;
853
854 IF lock_trip_rec.pu_stop_shipments_type_flag = 'I'
855 AND l_pu_stop_shipType_flag_orig = 'M'
856 THEN
857 --{
858 -- Display a warning whenever stop changes from mixed to inbound
859 FND_MESSAGE.SET_NAME('WSH','WSH_STOP_CHANGE_WARNING');
860 --
861 IF l_debug_on THEN
862 WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_TRIP_STOPS_PVT.GET_NAME',WSH_DEBUG_SV.C_PROC_LEVEL);
863 END IF;
864 --
865 --tkt calling get_namewith caller as FTE as inbound is available only with FTE
866 FND_MESSAGE.SET_TOKEN('STOP_NAME',wsh_trip_stops_pvt.get_name(leg_rec.pick_up_stop_id, 'FTE'));
867 wsh_util_core.add_message(WSH_UTIL_CORE.G_RET_STS_WARNING,l_module_name);
868 --
869 l_num_warnings := l_num_warnings + 1;
870 --}
871 END IF;
872 --}
873 END IF;
874 --
875 --
876 IF l_debug_on THEN
877 WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WWSH_TRIP_STOPS_VALIDATIONS.refreshShipmentsTypeFlag',WSH_DEBUG_SV.C_PROC_LEVEL);
878 END IF;
879 --
880 -- Recalculate value of shipments type flag for dropoff stop
881 --
882 --
883 WSH_TRIP_STOPS_VALIDATIONS.refreshShipmentsTypeFlag
884 (
885 p_trip_id => leg_rec.trip_id,
886 p_stop_id => leg_rec.drop_off_stop_id,
887 p_action => 'UNASSIGN',
888 p_shipment_direction => leg_rec.shipment_direction,
889 x_shipments_type_flag => lock_trip_rec.do_stop_shipments_type_flag,
890 x_return_status => l_return_status
891 );
892 --
893 IF l_debug_on THEN
894 WSH_DEBUG_SV.log(l_module_name,'refreshShipmentsTypeFlag l_return_status',l_return_status);
895 WSH_DEBUG_SV.log(l_module_name,'do_stop_shipments_type_flag',lock_trip_rec.do_stop_shipments_type_flag);
896 END IF;
897 wsh_util_core.api_post_call
898 (
899 p_return_status => l_return_status,
900 x_num_warnings => l_num_warnings,
901 x_num_errors => l_num_errors
902 );
903 --
904 --
905 IF lock_trip_rec.do_stop_shipments_type_flag <> l_do_stop_shipType_flag_orig
906 THEN
907 --{
908 -- Since dropoff stop's shipments type flag
909 -- has changed,
910 -- call FTE API for validations
911 -- and then update trip stop with new value.
912 --
913 /* H integration - call Multi Leg FTE */
914 IF (WSH_UTIL_CORE.FTE_IS_INSTALLED = 'Y') THEN
915 -- Get pvt type record structure for stop
916 --
917 -- Debug Statements
918 --
919 IF l_debug_on THEN
920 WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_TRIP_STOPS_GRP.GET_STOP_DETAILS_PVT',WSH_DEBUG_SV.C_PROC_LEVEL);
921 END IF;
922 --
923 wsh_trip_stops_grp.get_stop_details_pvt
924 (p_stop_id => leg_rec.drop_off_stop_id,
925 x_stop_rec => l_stop_rec,
926 x_return_status => l_return_status);
927 --
928 wsh_util_core.api_post_call
929 (
930 p_return_status => l_return_status,
931 x_num_warnings => l_num_warnings,
932 x_num_errors => l_num_errors
933 );
934 --
935 --
936 IF l_debug_on THEN
937 WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_FTE_INTEGRATION.TRIP_STOP_VALIDATIONS',WSH_DEBUG_SV.C_PROC_LEVEL);
938 END IF;
939 --
940 wsh_fte_integration.trip_stop_validations
941 (p_stop_rec => l_stop_rec,
942 p_trip_rec => l_trip_rec,
943 p_action => 'UPDATE',
944 x_return_status => l_return_status);
945 --
946 wsh_util_core.api_post_call
947 (
948 p_return_status => l_return_status,
949 x_num_warnings => l_num_warnings,
950 x_num_errors => l_num_errors
951 );
952 --
953 END IF;
954
955 /* End of H integration - call Multi Leg FTE */
956 update wsh_trip_stops
957 set shipments_type_flag = lock_trip_rec.do_stop_shipments_type_flag, -- J-IB-NPARIKH
958 last_update_date = SYSDATE,
959 last_updated_by = FND_GLOBAL.USER_ID,
960 last_update_login = FND_GLOBAL.LOGIN_ID
961 where stop_id = leg_rec.drop_off_stop_id;
962
963 IF lock_trip_rec.do_stop_shipments_type_flag = 'I'
964 AND l_do_stop_shipType_flag_orig = 'M'
965 THEN
966 --{
967 -- Display a warning whenever stop changes from mixed to inbound
968 FND_MESSAGE.SET_NAME('WSH','WSH_STOP_CHANGE_WARNING');
969 --
970 IF l_debug_on THEN
971 WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_TRIP_STOPS_PVT.GET_NAME',WSH_DEBUG_SV.C_PROC_LEVEL);
972 END IF;
973 --
974 --tkt calling get_namewith caller as FTE as inbound is available only with FTE
975 FND_MESSAGE.SET_TOKEN('STOP_NAME',wsh_trip_stops_pvt.get_name(leg_rec.drop_off_stop_id,'FTE'));
976 wsh_util_core.add_message(WSH_UTIL_CORE.G_RET_STS_WARNING,l_module_name);
977 --
978 l_num_warnings := l_num_warnings + 1;
979 --}
980 END IF;
981 --}
982 END IF;
983 --
984 -- If trip was mixed (before delete of delivery leg)
985 -- and pickup or dropoff stop's shipments type flag
986 -- have changed, need to re-evaluate trip's shipment
987 -- type flag.
988 --
989 IF lock_trip_rec.trip_shipments_type_flag = 'M'
990 --AND (
991 -- lock_trip_rec.pu_stop_shipments_type_flag <> l_pu_stop_shipType_flag_orig
992 -- OR lock_trip_rec.do_stop_shipments_type_flag <> l_do_stop_shipType_flag_orig
993 -- )
994 THEN
995 --{
996 IF l_debug_on THEN
997 WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_TRIP_VALIDATIONS.has_mixed_deliveries',WSH_DEBUG_SV.C_PROC_LEVEL);
998 END IF;
999 --
1000 -- Check if trip still has both inbound and outbound deliveries
1001 --
1002 l_has_mixed_deliveries := WSH_TRIP_VALIDATIONS.has_mixed_deliveries
1003 (
1004 p_trip_id => leg_rec.trip_id
1005 );
1006 --
1007 IF l_debug_on THEN
1008 WSH_DEBUG_SV.log(l_module_name,'l_has_mixed_deliveries',l_has_mixed_deliveries);
1009 END IF;
1010 --
1011 IF l_has_mixed_deliveries <> 'Y'
1012 THEN
1013 --{
1014 -- Trip does not have both inbound and outbound deliveries
1015 --
1016 --
1017 IF l_has_mixed_deliveries = 'NI'
1018 THEN
1019 -- trip has only inbound deliveries
1020 --
1021 lock_trip_rec.trip_shipments_type_flag := 'I';
1022 ELSE
1023 -- trip has only outbound deliveries
1024 --
1025 lock_trip_rec.trip_shipments_type_flag := 'O';
1026 END IF;
1027 --
1028 --
1029 UPDATE WSH_TRIPS
1030 SET shipments_type_flag = lock_trip_rec.trip_shipments_type_flag,
1031 last_update_date = SYSDATE,
1032 last_updated_by = FND_GLOBAL.USER_ID,
1033 last_update_login = FND_GLOBAL.LOGIN_ID
1034 WHERE trip_id = leg_rec.trip_id;
1035
1036 -- To keep the shipments_type_flag of all the
1037 -- stops in SYNC with the trip
1038 UPDATE WSH_TRIP_STOPS
1039 SET shipments_type_flag = lock_trip_rec.trip_shipments_type_flag,
1040 last_update_date = SYSDATE,
1041 last_updated_by = FND_GLOBAL.USER_ID,
1042 last_update_login = FND_GLOBAL.LOGIN_ID
1043 WHERE trip_id = leg_rec.trip_id
1044 AND shipments_type_flag <> lock_trip_rec.trip_shipments_type_flag;
1045
1046 IF l_debug_on THEN
1047 WSH_DEBUG_SV.log(l_module_name,' Number of rows updated in WTS are',SQL%ROWCOUNT);
1048 END IF;
1049 --}
1050 END IF;
1051 --}
1052 END IF;
1053
1054 ELSE
1055 IF l_debug_on THEN
1056 WSH_DEBUG_SV.log(l_module_name,'Raise others');
1057 END IF;
1058 raise others;
1059 END IF;
1060 --
1061 --
1062 -- J-IB-HEALI-{
1063 IF l_debug_on THEN
1064 WSH_DEBUG_SV.log(l_module_name,'calling Process_Leg_Sequence delivery_id',leg_rec.delivery_id);
1065 END IF;
1066
1067 WSH_NEW_DELIVERY_ACTIONS.Process_Leg_Sequence
1068 ( p_delivery_id => leg_rec.delivery_id,
1069 p_update_del_flag => 'Y',
1070 p_update_leg_flag => 'N',
1071 x_leg_complete => l_leg_complete,
1072 x_return_status => l_return_status);
1073
1074 IF l_debug_on THEN
1075 WSH_DEBUG_SV.log(l_module_name,'Process_Leg_Sequence l_return_status',l_return_status);
1076 END IF;
1077
1078 wsh_util_core.api_post_call
1079 (
1080 p_return_status => l_return_status,
1081 x_num_warnings => l_num_warnings,
1082 x_num_errors => l_num_errors);
1083
1084 -- J-IB-HEALI-}
1085
1086 IF l_num_errors > 0
1087 THEN
1088 x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
1089 ELSIF l_num_warnings > 0
1090 THEN
1091 x_return_status := WSH_UTIL_CORE.G_RET_STS_WARNING;
1092 ELSE
1093 x_return_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
1094 END IF;
1095 --
1096 --
1097 IF l_debug_on THEN
1098 WSH_DEBUG_SV.pop(l_module_name);
1099 END IF;
1100 --
1101 EXCEPTION
1102 -- J-IB-NPARIKH-{
1103 WHEN FND_API.G_EXC_ERROR THEN
1104
1105 x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
1106 --
1107 IF l_debug_on THEN
1108 WSH_DEBUG_SV.logmsg(l_module_name,'FND_API.G_EXC_ERROR exception has occured ',WSH_DEBUG_SV.C_EXCEP_LEVEL);
1109 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:FND_API.G_EXC_ERROR');
1110 END IF;
1111 --
1112 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1113
1114 x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR ;
1115 --
1116 IF l_debug_on THEN
1117 WSH_DEBUG_SV.logmsg(l_module_name,'Unexpected error has occured. Oracle error message is '|| SQLERRM,WSH_DEBUG_SV.C_UNEXPEC_ERR_LEVEL);
1118 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:FND_API.G_EXC_UNEXPECTED_ERROR');
1119 END IF;
1120 --
1121
1122 -- J-IB-NPARIKH-}
1123
1124 WHEN others THEN
1125 wsh_util_core.default_handler('WSH_DELIVERY_LEGS_PVT.DELETE_DELIVERY_LEG',l_module_name);
1126 x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR;
1127 --
1128 IF l_debug_on THEN
1129 WSH_DEBUG_SV.logmsg(l_module_name,'Unexpected error has occured. Oracle error message is '|| SQLERRM,WSH_DEBUG_SV.C_UNEXPEC_ERR_LEVEL);
1130 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:OTHERS');
1131 END IF;
1132 --
1133 END Delete_Delivery_Leg;
1134
1135
1136 --
1137 -- Procedure: Lock_Delivery_Leg
1138 -- Parameters: All Attributes of a Delivery Leg Record
1139 -- Description: This procedure will lock a delivery leg record. It is
1140 -- specifically designed for use by the form.
1141 --
1142 PROCEDURE Lock_Delivery_Leg (
1143 p_rowid IN VARCHAR2,
1144 p_delivery_leg_info IN Delivery_Leg_Rec_Type
1145 ) IS
1146
1147 CURSOR lock_row IS
1148 SELECT *
1149 FROM wsh_delivery_legs
1150 WHERE rowid = p_rowid
1151 FOR UPDATE OF delivery_leg_id NOWAIT;
1152
1153 Recinfo lock_row%ROWTYPE;
1154
1155 --
1156 l_debug_on BOOLEAN;
1157 --
1158 l_module_name CONSTANT VARCHAR2(100) := 'wsh.plsql.' || G_PKG_NAME || '.' || 'LOCK_DELIVERY_LEG';
1159 --
1160 BEGIN
1161
1162 --
1163 --
1164 l_debug_on := WSH_DEBUG_INTERFACE.g_debug;
1165 --
1166 IF l_debug_on IS NULL
1167 THEN
1168 l_debug_on := WSH_DEBUG_SV.is_debug_enabled;
1169 END IF;
1170 --
1171 IF l_debug_on THEN
1172 WSH_DEBUG_SV.push(l_module_name);
1173 --
1174 WSH_DEBUG_SV.log(l_module_name,'P_ROWID',P_ROWID);
1175 END IF;
1176 --
1177 OPEN lock_row;
1178 FETCH lock_row INTO Recinfo;
1179 IF (lock_row%NOTFOUND) THEN
1180 IF l_debug_on THEN
1181 WSH_DEBUG_SV.log(l_module_name,'FORM_RECORD_DELETED');
1182 END IF;
1183 CLOSE lock_row;
1184 FND_MESSAGE.Set_Name('FND','FORM_RECORD_DELETED');
1185 app_exception.raise_exception;
1186 END IF;
1187 CLOSE lock_row;
1188
1189 IF (
1190 (Recinfo.Delivery_Leg_Id = p_delivery_leg_info.Delivery_Leg_Id)
1191 AND (Recinfo.Delivery_Id = p_delivery_leg_info.Delivery_Id)
1192 AND (Recinfo.Sequence_Number = p_delivery_leg_info.Sequence_Number)
1193 AND (Recinfo.Pick_Up_Stop_Id = p_delivery_leg_info.Pick_Up_Stop_Id)
1194 AND (Recinfo.Drop_Off_Stop_Id = p_delivery_leg_info.Drop_Off_Stop_Id)
1195 AND ( (Recinfo.Gross_Weight = p_delivery_leg_info.Gross_Weight)
1196 OR ( (Recinfo.Gross_Weight IS NULL)
1197 AND (p_delivery_leg_info.Gross_Weight IS NULL)))
1198 AND ( (Recinfo.Net_Weight = p_delivery_leg_info.Net_Weight)
1199 OR ( (Recinfo.Net_Weight IS NULL)
1200 AND (p_delivery_leg_info.Net_Weight IS NULL)))
1201 AND ( (Recinfo.Weight_Uom_Code = p_delivery_leg_info.Weight_Uom_Code)
1202 OR ( (Recinfo.Weight_Uom_Code IS NULL)
1203 AND (p_delivery_leg_info.Weight_Uom_Code IS NULL)))
1204 AND ( (Recinfo.Volume = p_delivery_leg_info.Volume)
1205 OR ( (Recinfo.Volume IS NULL)
1206 AND (p_delivery_leg_info.Volume IS NULL)))
1207 AND ( (Recinfo.Volume_Uom_Code = p_delivery_leg_info.Volume_Uom_Code)
1208 OR ( (Recinfo.Volume_Uom_Code IS NULL)
1209 AND (p_delivery_leg_info.Volume_Uom_Code IS NULL)))
1210 AND ( (Recinfo.Creation_Date = p_delivery_leg_info.Creation_Date)
1211 OR ( (Recinfo.Creation_Date IS NULL)
1212 AND (p_delivery_leg_info.Creation_Date IS NULL)))
1213 AND ( (Recinfo.Created_By = p_delivery_leg_info.Created_By)
1214 OR ( (Recinfo.Created_By IS NULL)
1215 AND (p_delivery_leg_info.Created_By IS NULL)))
1216 AND ( (Recinfo.Last_Update_Date = p_delivery_leg_info.Last_Update_Date)
1217 OR ( (Recinfo.Last_Update_Date IS NULL)
1218 AND (p_delivery_leg_info.Last_Update_Date IS NULL)))
1219 AND ( (Recinfo.Last_Updated_By = p_delivery_leg_info.Last_Updated_By)
1220 OR ( (Recinfo.Last_Updated_By IS NULL)
1221 AND (p_delivery_leg_info.Last_Updated_By IS NULL)))
1222 AND ( (Recinfo.Last_Update_Login = p_delivery_leg_info.Last_Update_Login)
1223 OR ( (Recinfo.Last_Update_Login IS NULL)
1224 AND (p_delivery_leg_info.Last_Update_Login IS NULL)))
1225 AND ( (Recinfo.Program_Application_Id = p_delivery_leg_info.Program_Application_Id)
1226 OR ( (Recinfo.Program_Application_Id IS NULL)
1227 AND (p_delivery_leg_info.Program_Application_Id IS NULL)))
1228 AND ( (Recinfo.Program_Id = p_delivery_leg_info.Program_Id)
1229 OR ( (Recinfo.Program_Id IS NULL)
1230 AND (p_delivery_leg_info.Program_Id IS NULL)))
1231 AND ( (Recinfo.Program_Update_Date = p_delivery_leg_info.Program_Update_Date)
1232 OR ( (Recinfo.Program_Update_Date IS NULL)
1233 AND (p_delivery_leg_info.Program_Update_Date IS NULL)))
1234 AND ( (Recinfo.Load_Tender_Status = p_delivery_leg_info.Load_Tender_Status)
1235 OR ( (Recinfo.Load_Tender_Status IS NULL)
1236 AND (p_delivery_leg_info.Load_Tender_Status IS NULL)))
1237 /*Changes for Shipping Data Model Bug#1918342*/
1238
1239 AND ( (Recinfo.shipper_title= p_delivery_leg_info.shipper_title)
1240 OR ( (Recinfo.shipper_title IS NULL)
1241 AND (p_delivery_leg_info.shipper_title IS NULL)))
1242 AND ( (Recinfo.shipper_phone= p_delivery_leg_info.shipper_phone)
1243 OR ( (Recinfo.shipper_phone IS NULL)
1244 AND (p_delivery_leg_info.shipper_phone IS NULL)))
1245 AND ( (Recinfo.pod_flag = p_delivery_leg_info.pod_flag)
1246 OR ( (Recinfo.pod_flag IS NULL)
1247 AND (p_delivery_leg_info.pod_flag IS NULL)))
1248 AND ( (Recinfo.pod_by = p_delivery_leg_info.pod_by)
1249 OR ( (Recinfo.pod_by IS NULL)
1250 AND (p_delivery_leg_info.pod_by IS NULL)))
1251 AND ( (Recinfo.pod_date = p_delivery_leg_info.pod_date)
1252 OR ( (Recinfo.pod_date IS NULL)
1253 AND (p_delivery_leg_info.pod_date IS NULL)))
1254 AND ( (Recinfo.expected_pod_date = p_delivery_leg_info.expected_pod_date)
1255 OR ( (Recinfo.expected_pod_date IS NULL)
1256 AND (p_delivery_leg_info.expected_pod_date IS NULL)))
1257 AND ( (Recinfo.booking_office = p_delivery_leg_info.booking_office)
1258 OR ( (Recinfo.booking_office IS NULL)
1259 AND (p_delivery_leg_info.booking_office IS NULL)))
1260 AND ( (Recinfo.SHIPPER_EXPORT_REF = p_delivery_leg_info.SHIPPER_EXPORT_REF )
1261 OR ( (Recinfo.SHIPPER_EXPORT_REF IS NULL)
1262 AND (p_delivery_leg_info.SHIPPER_EXPORT_REF IS NULL)))
1263 AND ( (Recinfo.CARRIER_EXPORT_REF = p_delivery_leg_info.CARRIER_EXPORT_REF )
1264 OR ( (Recinfo.CARRIER_EXPORT_REF IS NULL)
1265 AND (p_delivery_leg_info.CARRIER_EXPORT_REF IS NULL)))
1266 AND ( (Recinfo.DOC_NOTIFY_PARTY = p_delivery_leg_info.DOC_NOTIFY_PARTY )
1267 OR ( (Recinfo.DOC_NOTIFY_PARTY IS NULL)
1268 AND (p_delivery_leg_info.DOC_NOTIFY_PARTY IS NULL)))
1269 AND ( (Recinfo.AETC_NUMBER = p_delivery_leg_info.AETC_NUMBER )
1270 OR ( (Recinfo.AETC_NUMBER IS NULL)
1271 AND (p_delivery_leg_info.AETC_NUMBER IS NULL)))
1272 AND ( (Recinfo.SHIPPER_SIGNED_BY = p_delivery_leg_info.SHIPPER_SIGNED_BY )
1273 OR ( (Recinfo.SHIPPER_SIGNED_BY IS NULL)
1274 AND (p_delivery_leg_info.SHIPPER_SIGNED_BY IS NULL)))
1275 AND ( (Recinfo.SHIPPER_DATE = p_delivery_leg_info.SHIPPER_DATE)
1276 OR ( (Recinfo.SHIPPER_DATE IS NULL)
1277 AND (p_delivery_leg_info.SHIPPER_DATE IS NULL)))
1278 AND ( (Recinfo.CARRIER_SIGNED_BY = p_delivery_leg_info.CARRIER_SIGNED_BY )
1279 OR ( (Recinfo.CARRIER_SIGNED_BY IS NULL)
1280 AND (p_delivery_leg_info.CARRIER_SIGNED_BY IS NULL)))
1281 AND ( (Recinfo.CARRIER_DATE = p_delivery_leg_info.CARRIER_DATE )
1282 OR ( (Recinfo.CARRIER_DATE IS NULL)
1283 AND (p_delivery_leg_info.CARRIER_DATE IS NULL)))
1284 AND ( (Recinfo.DOC_ISSUE_OFFICE = p_delivery_leg_info.DOC_ISSUE_OFFICE)
1285 OR ( (Recinfo.DOC_ISSUE_OFFICE IS NULL)
1286 AND (p_delivery_leg_info.DOC_ISSUE_OFFICE IS NULL)))
1287 AND ( (Recinfo.DOC_ISSUED_BY = p_delivery_leg_info.DOC_ISSUED_BY)
1288 OR ( (Recinfo.DOC_ISSUED_BY IS NULL)
1289 AND (p_delivery_leg_info.DOC_ISSUED_BY IS NULL)))
1290 AND ( (Recinfo.DOC_DATE_ISSUED = p_delivery_leg_info.DOC_DATE_ISSUED )
1291 OR ( (Recinfo.DOC_DATE_ISSUED IS NULL)
1292 AND (p_delivery_leg_info.DOC_DATE_ISSUED IS NULL)))
1293 AND ( (Recinfo.SHIPPER_HM_BY = p_delivery_leg_info.SHIPPER_HM_BY )
1294 OR ( (Recinfo.SHIPPER_HM_BY IS NULL)
1295 AND (p_delivery_leg_info.SHIPPER_HM_BY IS NULL)))
1296 AND ( (Recinfo.SHIPPER_HM_DATE = p_delivery_leg_info.SHIPPER_HM_DATE )
1297 OR ( (Recinfo.SHIPPER_HM_DATE IS NULL)
1298 AND (p_delivery_leg_info.SHIPPER_HM_DATE IS NULL)))
1299 AND ( (Recinfo.CARRIER_HM_BY = p_delivery_leg_info.CARRIER_HM_BY )
1300 OR ( (Recinfo.CARRIER_HM_BY IS NULL)
1301 AND (p_delivery_leg_info.CARRIER_HM_BY IS NULL)))
1302 AND ( (Recinfo.CARRIER_HM_DATE = p_delivery_leg_info.CARRIER_HM_DATE )
1303 OR ( (Recinfo.CARRIER_HM_DATE IS NULL)
1304 AND (p_delivery_leg_info.CARRIER_HM_DATE IS NULL)))
1305 AND ( (Recinfo.BOOKING_NUMBER = p_delivery_leg_info.BOOKING_NUMBER )
1306 OR ( (Recinfo.BOOKING_NUMBER IS NULL)
1307 AND (p_delivery_leg_info.BOOKING_NUMBER IS NULL)))
1308 AND ( (Recinfo.PORT_OF_LOADING = p_delivery_leg_info.PORT_OF_LOADING )
1309 OR ( (Recinfo.PORT_OF_LOADING IS NULL)
1310 AND (p_delivery_leg_info.PORT_OF_LOADING IS NULL)))
1311 AND ( (Recinfo.PORT_OF_DISCHARGE = p_delivery_leg_info.PORT_OF_DISCHARGE )
1312 OR ( (Recinfo.PORT_OF_DISCHARGE IS NULL)
1313 AND (p_delivery_leg_info.PORT_OF_DISCHARGE IS NULL)))
1314 AND ( (Recinfo.SERVICE_CONTRACT = p_delivery_leg_info.SERVICE_CONTRACT )
1315 OR ( (Recinfo.SERVICE_CONTRACT IS NULL)
1316 AND (p_delivery_leg_info.SERVICE_CONTRACT IS NULL)))
1317 AND ( (Recinfo.BILL_FREIGHT_TO = p_delivery_leg_info.BILL_FREIGHT_TO )
1318 OR ( (Recinfo.BILL_FREIGHT_TO IS NULL)
1319 AND (p_delivery_leg_info.BILL_FREIGHT_TO IS NULL)))
1320 /* H Integration: datamodel changes wrudge */
1321 AND ( (Recinfo.FTE_TRIP_ID = p_delivery_leg_info.FTE_TRIP_ID )
1322 OR ( (Recinfo.FTE_TRIP_ID IS NULL)
1323 AND (p_delivery_leg_info.FTE_TRIP_ID IS NULL)))
1324 AND ( (Recinfo.REPRICE_REQUIRED = p_delivery_leg_info.REPRICE_REQUIRED )
1325 OR ( (Recinfo.REPRICE_REQUIRED IS NULL)
1326 AND (p_delivery_leg_info.REPRICE_REQUIRED IS NULL)))
1327 AND ( (Recinfo.ACTUAL_ARRIVAL_DATE = p_delivery_leg_info.ACTUAL_ARRIVAL_DATE )
1328 OR ( (Recinfo.ACTUAL_ARRIVAL_DATE IS NULL)
1329 AND (p_delivery_leg_info.ACTUAL_ARRIVAL_DATE IS NULL)))
1330 AND ( (Recinfo.ACTUAL_DEPARTURE_DATE = p_delivery_leg_info.ACTUAL_DEPARTURE_DATE )
1331 OR ( (Recinfo.ACTUAL_DEPARTURE_DATE IS NULL)
1332 AND (p_delivery_leg_info.ACTUAL_DEPARTURE_DATE IS NULL)))
1333 AND ( (Recinfo.ACTUAL_RECEIPT_DATE = p_delivery_leg_info.ACTUAL_RECEIPT_DATE )
1334 OR ( (Recinfo.ACTUAL_RECEIPT_DATE IS NULL)
1335 AND (p_delivery_leg_info.ACTUAL_RECEIPT_DATE IS NULL)))
1336 AND ( (Recinfo.TRACKING_DRILLDOWN_FLAG = p_delivery_leg_info.TRACKING_DRILLDOWN_FLAG )
1337 OR ( (Recinfo.TRACKING_DRILLDOWN_FLAG IS NULL)
1338 AND (p_delivery_leg_info.TRACKING_DRILLDOWN_FLAG IS NULL)))
1339 AND ( (Recinfo.STATUS_CODE = p_delivery_leg_info.STATUS_CODE )
1340 OR ( (Recinfo.STATUS_CODE IS NULL)
1341 AND (p_delivery_leg_info.STATUS_CODE IS NULL)))
1342 AND ( (Recinfo.TRACKING_REMARKS = p_delivery_leg_info.TRACKING_REMARKS )
1343 OR ( (Recinfo.TRACKING_REMARKS IS NULL)
1344 AND (p_delivery_leg_info.TRACKING_REMARKS IS NULL)))
1345 AND ( (Recinfo.CARRIER_EST_DEPARTURE_DATE = p_delivery_leg_info.CARRIER_EST_DEPARTURE_DATE )
1346 OR ( (Recinfo.CARRIER_EST_DEPARTURE_DATE IS NULL)
1347 AND (p_delivery_leg_info.CARRIER_EST_DEPARTURE_DATE IS NULL)))
1348 AND ( (Recinfo.CARRIER_EST_ARRIVAL_DATE = p_delivery_leg_info.CARRIER_EST_ARRIVAL_DATE )
1349 OR ( (Recinfo.CARRIER_EST_ARRIVAL_DATE IS NULL)
1350 AND (p_delivery_leg_info.CARRIER_EST_ARRIVAL_DATE IS NULL)))
1351 AND ( (Recinfo.LOADING_START_DATETIME = p_delivery_leg_info.LOADING_START_DATETIME )
1352 OR ( (Recinfo.LOADING_START_DATETIME IS NULL)
1353 AND (p_delivery_leg_info.LOADING_START_DATETIME IS NULL)))
1354 AND ( (Recinfo.LOADING_END_DATETIME = p_delivery_leg_info.LOADING_END_DATETIME )
1355 OR ( (Recinfo.LOADING_END_DATETIME IS NULL)
1356 AND (p_delivery_leg_info.LOADING_END_DATETIME IS NULL)))
1357 AND ( (Recinfo.UNLOADING_START_DATETIME = p_delivery_leg_info.UNLOADING_START_DATETIME )
1358 OR ( (Recinfo.UNLOADING_START_DATETIME IS NULL)
1359 AND (p_delivery_leg_info.UNLOADING_START_DATETIME IS NULL)))
1360 AND ( (Recinfo.UNLOADING_END_DATETIME = p_delivery_leg_info.UNLOADING_END_DATETIME )
1361 OR ( (Recinfo.UNLOADING_END_DATETIME IS NULL)
1362 AND (p_delivery_leg_info.UNLOADING_END_DATETIME IS NULL)))
1363 AND ( (Recinfo.DELIVERED_QUANTITY = p_delivery_leg_info.DELIVERED_QUANTITY )
1364 OR ( (Recinfo.DELIVERED_QUANTITY IS NULL)
1365 AND (p_delivery_leg_info.DELIVERED_QUANTITY IS NULL)))
1366 AND ( (Recinfo.LOADED_QUANTITY = p_delivery_leg_info.LOADED_QUANTITY )
1367 OR ( (Recinfo.LOADED_QUANTITY IS NULL)
1368 AND (p_delivery_leg_info.LOADED_QUANTITY IS NULL)))
1369 AND ( (Recinfo.RECEIVED_QUANTITY = p_delivery_leg_info.RECEIVED_QUANTITY )
1370 OR ( (Recinfo.RECEIVED_QUANTITY IS NULL)
1371 AND (p_delivery_leg_info.RECEIVED_QUANTITY IS NULL)))
1372 AND ( (Recinfo.ORIGIN_STOP_ID = p_delivery_leg_info.ORIGIN_STOP_ID )
1373 OR ( (Recinfo.ORIGIN_STOP_ID IS NULL)
1374 AND (p_delivery_leg_info.ORIGIN_STOP_ID IS NULL)))
1375 AND ( (Recinfo.DESTINATION_STOP_ID = p_delivery_leg_info.DESTINATION_STOP_ID )
1376 OR ( (Recinfo.DESTINATION_STOP_ID IS NULL)
1377 AND (p_delivery_leg_info.DESTINATION_STOP_ID IS NULL)))
1378 AND ( (Recinfo.parent_delivery_leg_id = p_delivery_leg_info.parent_delivery_leg_id )
1379 OR ( (Recinfo.parent_delivery_leg_id IS NULL)
1380 AND (p_delivery_leg_info.parent_delivery_leg_id IS NULL)))
1381 ) THEN
1382 --
1383 IF l_debug_on THEN
1384 WSH_DEBUG_SV.log(l_module_name,'Nothing has changed');
1385 WSH_DEBUG_SV.pop(l_module_name);
1386 END IF;
1387 --
1388 RETURN;
1389 ELSE
1390 FND_MESSAGE.Set_Name('FND', 'FORM_RECORD_CHANGED');
1391 IF l_debug_on THEN
1392 WSH_DEBUG_SV.log(l_module_name,'FORM_RECORD_CHANGED');
1393 END IF;
1394 app_exception.raise_exception;
1395 END IF;
1396 --
1397 IF l_debug_on THEN
1398 WSH_DEBUG_SV.pop(l_module_name);
1399 END IF;
1400 --
1401 EXCEPTION
1402 WHEN others THEN
1403
1404 -- Is this necessary? Does PL/SQL automatically close a
1405 -- cursor when it goes out of scope?
1406
1407 if (lock_row%ISOPEN) then
1408 close lock_row;
1409 end if;
1410 --
1411 IF l_debug_on THEN
1412 WSH_DEBUG_SV.logmsg(l_module_name,'Unexpected error has occured. Oracle error message is '|| SQLERRM,WSH_DEBUG_SV.C_UNEXPEC_ERR_LEVEL);
1413 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:OTHERS');
1414 END IF;
1415 --
1416 raise;
1417 END Lock_Delivery_Leg;
1418
1419 PROCEDURE Populate_Record (
1420 p_delivery_leg_id IN NUMBER,
1421 x_delivery_leg_info OUT NOCOPY Delivery_Leg_Rec_Type,
1422 x_return_status OUT NOCOPY VARCHAR2) IS
1423
1424 CURSOR leg_record IS
1425 SELECT
1426 DELIVERY_LEG_ID,
1427 DELIVERY_ID,
1428 SEQUENCE_NUMBER,
1429 LOADING_ORDER_FLAG,
1430 PICK_UP_STOP_ID,
1431 DROP_OFF_STOP_ID,
1432 GROSS_WEIGHT,
1433 NET_WEIGHT,
1434 WEIGHT_UOM_CODE,
1435 VOLUME,
1436 VOLUME_UOM_CODE,
1437 CREATION_DATE,
1438 CREATED_BY,
1439 LAST_UPDATE_DATE,
1440 LAST_UPDATED_BY,
1441 LAST_UPDATE_LOGIN,
1442 PROGRAM_APPLICATION_ID,
1443 PROGRAM_ID,
1444 PROGRAM_UPDATE_DATE,
1445 REQUEST_ID,
1446 LOAD_TENDER_STATUS,
1447 /* Changes in the shipping datamodel Bug#1918342*/
1448 SHIPPER_TITLE,
1449 SHIPPER_PHONE,
1450 POD_FLAG,
1451 POD_BY,
1452 POD_DATE,
1453 EXPECTED_POD_DATE,
1454 BOOKING_OFFICE,
1455 SHIPPER_EXPORT_REF,
1456 CARRIER_EXPORT_REF,
1457 DOC_NOTIFY_PARTY,
1458 AETC_NUMBER,
1459 SHIPPER_SIGNED_BY,
1460 SHIPPER_DATE,
1461 CARRIER_SIGNED_BY,
1462 CARRIER_DATE,
1463 DOC_ISSUE_OFFICE,
1464 DOC_ISSUED_BY,
1465 DOC_DATE_ISSUED,
1466 SHIPPER_HM_BY,
1467 SHIPPER_HM_DATE,
1468 CARRIER_HM_BY,
1469 CARRIER_HM_DATE,
1470 BOOKING_NUMBER,
1471 PORT_OF_LOADING,
1472 PORT_OF_DISCHARGE,
1473 SERVICE_CONTRACT,
1474 BILL_FREIGHT_TO,
1475 /* H Integration: datamodel changes wrudge */
1476 FTE_TRIP_ID,
1477 REPRICE_REQUIRED,
1478 ACTUAL_ARRIVAL_DATE,
1479 ACTUAL_DEPARTURE_DATE,
1480 ACTUAL_RECEIPT_DATE,
1481 TRACKING_DRILLDOWN_FLAG,
1482 STATUS_CODE,
1483 TRACKING_REMARKS,
1484 CARRIER_EST_DEPARTURE_DATE,
1485 CARRIER_EST_ARRIVAL_DATE,
1486 LOADING_START_DATETIME,
1487 LOADING_END_DATETIME,
1488 UNLOADING_START_DATETIME,
1489 UNLOADING_END_DATETIME,
1490 DELIVERED_QUANTITY,
1491 LOADED_QUANTITY,
1492 RECEIVED_QUANTITY,
1493 ORIGIN_STOP_ID,
1494 DESTINATION_STOP_ID,
1495 /* Harmonization Project I **heali */
1496 ROWID,
1497 /* K: MDC: sperera */
1498 parent_delivery_leg_id
1499 FROM wsh_delivery_legs
1500 WHERE delivery_leg_id = p_delivery_leg_id;
1501
1502 others EXCEPTION;
1503
1504 --
1505 l_debug_on BOOLEAN;
1506 --
1507 l_module_name CONSTANT VARCHAR2(100) := 'wsh.plsql.' || G_PKG_NAME || '.' || 'POPULATE_RECORD';
1508 --
1509 BEGIN
1510 --
1511 --
1512 l_debug_on := WSH_DEBUG_INTERFACE.g_debug;
1513 --
1514 IF l_debug_on IS NULL
1515 THEN
1516 l_debug_on := WSH_DEBUG_SV.is_debug_enabled;
1517 END IF;
1518 --
1519 IF l_debug_on THEN
1520 WSH_DEBUG_SV.push(l_module_name);
1521 --
1522 WSH_DEBUG_SV.log(l_module_name,'P_DELIVERY_LEG_ID',P_DELIVERY_LEG_ID);
1523 END IF;
1524 --
1525 IF (p_delivery_leg_id IS NULL) THEN
1526 raise others;
1527 END IF;
1528
1529 OPEN leg_record;
1530 FETCH leg_record INTO x_delivery_leg_info;
1531
1532 IF (leg_record%NOTFOUND) THEN
1533 FND_MESSAGE.SET_NAME('WSH','WSH_LEG_NOT_FOUND');
1534 x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
1535 WSH_UTIL_CORE.ADD_MESSAGE(x_return_status,l_module_name);
1536 IF l_debug_on THEN
1537 WSH_DEBUG_SV.log(l_module_name,'WSH_LEG_NOT_FOUND');
1538 END IF;
1539 ELSE
1540 x_return_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
1541 END IF;
1542
1543 CLOSE leg_record;
1544 --
1545 IF l_debug_on THEN
1546 WSH_DEBUG_SV.pop(l_module_name);
1547 END IF;
1548 --
1549 EXCEPTION
1550 WHEN others THEN
1551 wsh_util_core.default_handler('WSH_DELIVERY_LEGS_PVT.POPULATE_RECORD',l_module_name);
1552 x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR;
1553 --
1554 IF l_debug_on THEN
1555 WSH_DEBUG_SV.logmsg(l_module_name,'Unexpected error has occured. Oracle error message is '|| SQLERRM,WSH_DEBUG_SV.C_UNEXPEC_ERR_LEVEL);
1556 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:OTHERS');
1557 END IF;
1558 --
1559 END Populate_Record;
1560
1561
1562 -----------------------------------------------------------------------------
1563 --
1564 -- Procedure: Get_Disabled_List
1565 -- Parameters: stop_id, x_return_status, p_trip_flag
1566 -- Description: Get the disabled columns/fields in a delivery leg
1567 --
1568 -----------------------------------------------------------------------------
1569 PROCEDURE Get_Disabled_List (
1570 p_delivery_leg_id IN NUMBER,
1571 p_parent_entity_id IN NUMBER,
1572 p_list_type IN VARCHAR2,
1573 x_return_status OUT NOCOPY VARCHAR2,
1574 x_disabled_list OUT NOCOPY wsh_util_core.column_tab_type,
1575 x_msg_count OUT NOCOPY NUMBER,
1576 x_msg_data OUT NOCOPY VARCHAR2
1577 ) IS
1578
1579 CURSOR get_delivery_status(x_delivery_id NUMBER) IS
1580 SELECT status_code, planned_flag
1581 FROM wsh_new_deliveries
1582 WHERE delivery_id = x_delivery_id;
1583
1584 CURSOR get_leg_status(x_leg_id NUMBER) IS
1585 SELECT delivery_id, pick_up_stop_id, drop_off_stop_id
1586 FROM wsh_delivery_legs
1587 WHERE delivery_leg_id = x_leg_id;
1588
1589 CURSOR get_stop_status(x_stop_id NUMBER) IS
1590 SELECT status_code
1591 FROM wsh_trip_stops
1592 WHERE stop_id = x_stop_id;
1593
1594 i NUMBER := 0;
1595 dummy_id NUMBER := 0;
1596 l_status_code VARCHAR2(10) := NULL;
1597 l_planned_flag VARCHAR2(10) := NULL;
1598
1599 l_pick_up_stop get_stop_status%ROWTYPE;
1600 l_drop_off_stop get_stop_status%ROWTYPE;
1601 l_delivery_id NUMBER := 0;
1602 l_pick_up_stop_id NUMBER := 0;
1603 l_drop_off_stop_id NUMBER := 0;
1604
1605 l_msg_summary VARCHAR2(2000) := NULL;
1606 l_msg_details VARCHAR2(4000) := NULL;
1607
1608 WSH_DP_NO_ENTITY exception;
1609 WSH_INV_LIST_TYPE exception;
1610 WSH_DP_NO_STOP exception;
1611
1612
1613 --
1614 l_debug_on BOOLEAN;
1615 --
1616 l_module_name CONSTANT VARCHAR2(100) := 'wsh.plsql.' || G_PKG_NAME || '.' || 'GET_DISABLED_LIST';
1617 --
1618 BEGIN
1619 --
1620 --
1621 l_debug_on := WSH_DEBUG_INTERFACE.g_debug;
1622 --
1623 IF l_debug_on IS NULL
1624 THEN
1625 l_debug_on := WSH_DEBUG_SV.is_debug_enabled;
1626 END IF;
1627 --
1628 IF l_debug_on THEN
1629 WSH_DEBUG_SV.push(l_module_name);
1630 --
1631 WSH_DEBUG_SV.log(l_module_name,'P_DELIVERY_LEG_ID',P_DELIVERY_LEG_ID);
1632 WSH_DEBUG_SV.log(l_module_name,'P_PARENT_ENTITY_ID',P_PARENT_ENTITY_ID);
1633 WSH_DEBUG_SV.log(l_module_name,'P_LIST_TYPE',P_LIST_TYPE);
1634 END IF;
1635 --
1636 x_return_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
1637 x_disabled_list.delete;
1638
1639 IF p_list_type <> 'FORM' THEN
1640 raise WSH_INV_LIST_TYPE;
1641 END IF;
1642 IF (p_parent_entity_id is NULL) THEN
1643 FND_MESSAGE.Set_Name('WSH','WSH_API_INVALID_PARAM_VALUE');
1644 x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
1645 --
1646 IF l_debug_on THEN
1647 WSH_DEBUG_SV.pop(l_module_name);
1648 END IF;
1649 --
1650 RETURN;
1651 END IF;
1652
1653 OPEN get_delivery_status(p_parent_entity_id);
1654 FETCH get_delivery_status INTO l_status_code, l_planned_flag;
1655 IF l_debug_on THEN
1656 WSH_DEBUG_SV.log(l_module_name,'l_status_code',l_status_code);
1657 WSH_DEBUG_SV.log(l_module_name,'l_planned_flag',l_planned_flag);
1658 END IF;
1659 IF get_delivery_status%NOTFOUND THEN
1660 CLOSE get_delivery_status;
1661 FND_MESSAGE.Set_Name('WSH','WSH_API_INVALID_PARAM_VALUE');
1662 x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
1663 --
1664 IF l_debug_on THEN
1665 WSH_DEBUG_SV.pop(l_module_name);
1666 END IF;
1667 --
1668 RETURN;
1669 END IF;
1670 CLOSE get_delivery_status;
1671
1672 IF (l_status_code = 'CL') THEN
1673 i:=i+1; x_disabled_list(i) := 'SHIP_METHOD_NAME';
1674 i:=i+1; x_disabled_list(i) := '+BOOKING_NUMBER';
1675 i:=i+1; x_disabled_list(i) := '+SERVICE_CONTRACT';
1676 i:=i+1; x_disabled_list(i) := '+AETC_NUMBER';
1677 --Bugfix#1918342 i:=i+1; x_disabled_list(i) := '+SUPPLIER_CODE';
1678 i:=i+1; x_disabled_list(i) := '+CARRIER_EXPORT_REF';
1679 i:=i+1; x_disabled_list(i) := '+SHIPPER_EXPORT_REF';
1680 i:=i+1; x_disabled_list(i) := '+NOTIFY_PARTY';
1681 i:=i+1; x_disabled_list(i) := '+BILL_FREIGHT_TO';
1682 --Bug#1918342 i:=i+1; x_disabled_list(i) := '+PROBLEM_CONTACT_REF';
1683 i:=i+1; x_disabled_list(i) := '+BOOKING_OFFICE';
1684 i:=i+1; x_disabled_list(i) := '+ISSUING_OFFICE';
1685 i:=i+1; x_disabled_list(i) := '+ISSUING_PERSON';
1686 i:=i+1; x_disabled_list(i) := '+DATE_ISSUED';
1687 i:=i+1; x_disabled_list(i) := '+PORT_OF_LOADING';
1688 i:=i+1; x_disabled_list(i) := '+PORT_OF_DISCHARGE';
1689 /* Commented for shipping Data model Bug#1918342
1690 i:=i+1; x_disabled_list(i) := '+COD_AMOUNT';
1691 i:=i+1; x_disabled_list(i) := '+COD_CURRENCY_CODE';
1692 i:=i+1; x_disabled_list(i) := '+COD_REMIT_TO';
1693 i:=i+1; x_disabled_list(i) := '+COD_CHARGE_PAID_BY';*/
1694 i:=i+1; x_disabled_list(i) := '+SHIPPER_TITLE';
1695 i:=i+1; x_disabled_list(i) := '+SHIPPER_PHONE';
1696 i:=i+1; x_disabled_list(i) := '+SHIPPER_SIGNED_BY';
1697 i:=i+1; x_disabled_list(i) := '+SHIPPER_SIGNED_DATE';
1698 i:=i+1; x_disabled_list(i) := '+CARRIER_SIGNED_BY';
1699 i:=i+1; x_disabled_list(i) := '+CARRIER_SIGNED_DATE';
1700 i:=i+1; x_disabled_list(i) := '+POD_SIGNED_BY';
1701 i:=i+1; x_disabled_list(i) := '+POD_SIGNED_DATE';
1702 --Bug#1918342 i:=i+1; x_disabled_list(i) := '+POD_COMMENTS';
1703 i:=i+1; x_disabled_list(i) := '+SHIPPER_SIGNED_HM_BY';
1704 i:=i+1; x_disabled_list(i) := '+SHIPPER_SIGNED_HM_DATE';
1705 i:=i+1; x_disabled_list(i) := '+CARRIER_SIGNED_HM_BY';
1706 i:=i+1; x_disabled_list(i) := '+CARRIER_SIGNED_HM_DATE';
1707 ELSE
1708 OPEN get_leg_status(p_delivery_leg_id);
1709 FETCH get_leg_status INTO l_delivery_id, l_pick_up_stop_id, l_drop_off_stop_id;
1710 IF l_debug_on THEN
1711 WSH_DEBUG_SV.log(l_module_name,'l_delivery_id',
1712 l_delivery_id);
1713 WSH_DEBUG_SV.log(l_module_name,'l_pick_up_stop_id',
1714 l_pick_up_stop_id);
1715 WSH_DEBUG_SV.log(l_module_name,'l_drop_off_stop_id',
1716 l_drop_off_stop_id);
1717 END IF;
1718 IF get_leg_status%NOTFOUND then
1719 CLOSE get_leg_status;
1720 RAISE WSH_DP_NO_ENTITY;
1721 END IF;
1722 CLOSE get_leg_status;
1723
1724 OPEN get_stop_status(l_pick_up_stop_id);
1725 FETCH get_stop_status INTO l_pick_up_stop;
1726 IF l_debug_on THEN
1727 WSH_DEBUG_SV.log(l_module_name,'status_code',
1728 l_pick_up_stop.status_code);
1729 END IF;
1730 IF get_stop_status%NOTFOUND then
1731 CLOSE get_stop_status;
1732 RAISE WSH_DP_NO_STOP;
1733 END IF;
1734 CLOSE get_stop_status;
1735
1736 OPEN get_stop_status(l_drop_off_stop_id);
1737 FETCH get_stop_status INTO l_drop_off_stop;
1738 IF l_debug_on THEN
1739 WSH_DEBUG_SV.log(l_module_name,'status_code',
1740 l_drop_off_stop.status_code);
1741 END IF;
1742 IF get_stop_status%NOTFOUND then
1743 CLOSE get_stop_status;
1744 RAISE WSH_DP_NO_STOP;
1745 END IF;
1746 CLOSE get_stop_status;
1747
1748 IF (l_pick_up_stop.status_code = 'OP' )
1749 AND ( l_drop_off_stop.status_code = 'OP') THEN
1750 i:=i+1; x_disabled_list(i) := '+SHIP_METHOD_NAME';
1751 ELSIF (l_pick_up_stop.status_code = 'CL') OR
1752 (l_drop_off_stop.status_code in ('AR','CL')) THEN
1753 i:=i+1; x_disabled_list(i) := 'SHIP_METHOD_NAME';
1754 END IF;
1755 END IF;
1756 --
1757 IF l_debug_on THEN
1758 WSH_DEBUG_SV.pop(l_module_name);
1759 END IF;
1760 --
1761 EXCEPTION
1762
1763 WHEN WSH_DP_NO_ENTITY THEN
1764 FND_MESSAGE.SET_NAME('WSH', 'WSH_DP_NO_ENTITY');
1765 WSH_UTIL_CORE.ADD_MESSAGE(FND_API.G_RET_STS_ERROR,l_module_name);
1766 x_return_status := FND_API.G_RET_STS_ERROR;
1767 WSH_UTIL_CORE.get_messages('Y', l_msg_summary, l_msg_details, x_msg_count);
1768 if x_msg_count > 1 then
1769 x_msg_data := l_msg_summary || l_msg_details;
1770 else
1771 x_msg_data := l_msg_summary;
1772 end if;
1773 --
1774 IF l_debug_on THEN
1775 WSH_DEBUG_SV.logmsg(l_module_name,'WSH_DP_NO_ENTITY exception has occured.',WSH_DEBUG_SV.C_EXCEP_LEVEL);
1776 IF x_msg_count > 1 then
1777 WSH_DEBUG_SV.log(l_module_name,'x_msg_data',
1778 SUBSTR(x_msg_data,1,200));
1779 END IF;
1780 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:WSH_DP_NO_ENTITY');
1781 END IF;
1782 --
1783 WHEN WSH_DP_NO_STOP THEN
1784 FND_MESSAGE.SET_NAME('WSH', 'WSH_DP_NO_STOP');
1785 WSH_UTIL_CORE.ADD_MESSAGE(FND_API.G_RET_STS_ERROR,l_module_name);
1786 x_return_status := FND_API.G_RET_STS_ERROR;
1787 WSH_UTIL_CORE.get_messages('Y', l_msg_summary, l_msg_details, x_msg_count);
1788 if x_msg_count > 1 then
1789 x_msg_data := l_msg_summary || l_msg_details;
1790 else
1791 x_msg_data := l_msg_summary;
1792 end if;
1793 --
1794 IF l_debug_on THEN
1795 WSH_DEBUG_SV.logmsg(l_module_name,'WSH_DP_NO_STOP exception has occured.',WSH_DEBUG_SV.C_EXCEP_LEVEL);
1796 IF x_msg_count > 1 then
1797 WSH_DEBUG_SV.log(l_module_name,'x_msg_data',
1798 SUBSTR(x_msg_data,1,200));
1799 END IF;
1800 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:WSH_DP_NO_STOP');
1801 END IF;
1802 --
1803 WHEN WSH_INV_LIST_TYPE THEN
1804 FND_MESSAGE.SET_NAME('WSH', 'WSH_INV_LIST_TYPE');
1805 WSH_UTIL_CORE.ADD_MESSAGE(FND_API.G_RET_STS_ERROR,l_module_name);
1806 x_return_status := FND_API.G_RET_STS_ERROR;
1807 WSH_UTIL_CORE.get_messages('Y', l_msg_summary, l_msg_details, x_msg_count);
1808 if x_msg_count > 1 then
1809 x_msg_data := l_msg_summary || l_msg_details;
1810 else
1811 x_msg_data := l_msg_summary;
1812 end if;
1813 --
1814 IF l_debug_on THEN
1815 WSH_DEBUG_SV.logmsg(l_module_name,'WSH_INV_LIST_TYPE exception has occured.',WSH_DEBUG_SV.C_EXCEP_LEVEL);
1816 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:WSH_INV_LIST_TYPE');
1817 END IF;
1818 --
1819 WHEN OTHERS THEN
1820 IF get_delivery_status%ISOPEN THEN
1821 CLOSE get_delivery_status;
1822 END IF;
1823 IF get_leg_status%ISOPEN THEN
1824 CLOSE get_leg_status;
1825 END IF;
1826 IF get_stop_status%ISOPEN THEN
1827 CLOSE get_stop_status;
1828 END IF;
1829
1830 FND_MESSAGE.Set_Name('WSH','WSH_UNEXPECTED_ERROR');
1831 x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR;
1832
1833 IF l_debug_on THEN
1834 WSH_DEBUG_SV.logmsg(l_module_name,'Unexpected error has occured. Oracle error message is '|| SQLERRM,WSH_DEBUG_SV.C_UNEXPEC_ERR_LEVEL);
1835 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:OTHERS');
1836 END IF;
1837 --
1838 END Get_Disabled_List;
1839
1840 /* ---------------------------------------------------------------------
1841 Procedure: Lock_Dlvy_Leg_No_Compare
1842
1843 Parameters: Delivery_Leg Id DEFAULT NULL
1844 Delivery Id DEFAULT NULL
1845
1846 Description: This procedure is used for obtaining locks of delivery legs
1847 using the delivery_leg_id or the delivery_id.
1848 It is called by delivery's wrapper lock API when the
1849 action is CONFIRM.
1850 This procedure does not compare the attributes. It just
1851 does a SELECT using FOR UPDATE NOWAIT
1852 Created: Harmonization Project. Patchset I
1853 ----------------------------------------------------------------------- */
1854
1855 PROCEDURE Lock_Dlvy_Leg_No_Compare(
1856 p_dlvy_leg_id IN NUMBER, -- default null in spec
1857 p_delivery_id IN NUMBER -- DEFAULT null in spec
1858 )
1859 IS
1860 l_dummy_leg_id NUMBER;
1861 l_del_name VARCHAR2(30);
1862
1863 CURSOR c_lock_dlvy_leg(p_leg_id NUMBER) IS
1864 SELECT delivery_leg_id
1865 FROM wsh_delivery_legs
1866 WHERE delivery_leg_id = p_leg_id
1867 FOR UPDATE NOWAIT;
1868
1869 CURSOR c_lock_legs_of_dlvy(p_dlvy_id NUMBER) IS
1870 SELECT delivery_leg_id
1871 FROM wsh_delivery_legs
1872 WHERE delivery_id = p_dlvy_id
1873 FOR UPDATE NOWAIT;
1874
1875 l_debug_on BOOLEAN;
1876 --
1877 l_module_name CONSTANT VARCHAR2(100) := 'wsh.plsql.' || G_PKG_NAME || '.' || 'LOCK_DLVY_LEG';
1878
1879 BEGIN
1880 --
1881 l_debug_on := WSH_DEBUG_INTERFACE.g_debug;
1882 --
1883 IF l_debug_on IS NULL
1884 THEN
1885 l_debug_on := WSH_DEBUG_SV.is_debug_enabled;
1886 END IF;
1887 --
1888 IF l_debug_on THEN
1889 WSH_DEBUG_SV.push(l_module_name);
1890 WSH_DEBUG_SV.log(l_module_name,'p_dlvy_leg_id', p_dlvy_leg_id);
1891 WSH_DEBUG_SV.log(l_module_name, 'p_delivery_id', p_delivery_id);
1892 END IF;
1893 --
1894 IF p_dlvy_leg_id IS NOT NULL THEN
1895 open c_lock_dlvy_leg(p_dlvy_leg_id);
1896 fetch c_lock_dlvy_leg INTO l_dummy_leg_id;
1897 close c_lock_dlvy_leg;
1898
1899 ELSIF p_delivery_id IS NOT NULL THEN
1900 open c_lock_legs_of_dlvy(p_delivery_id);
1901 close c_lock_legs_of_dlvy;
1902 END IF;
1903
1904 IF l_debug_on THEN
1905 WSH_DEBUG_SV.pop(l_module_name);
1906 END IF;
1907 EXCEPTION
1908 WHEN app_exception.application_exception or app_exception.record_lock_exception THEN
1909 IF p_delivery_id IS NOT NULL THEN
1910 l_del_name := wsh_new_deliveries_pvt.get_name(p_delivery_id);
1911 FND_MESSAGE.SET_NAME('WSH', 'WSH_DLVY_DEL_LEG_LOCK');
1912 FND_MESSAGE.SET_TOKEN('DEL_NAME', l_del_name);
1913 wsh_util_Core.add_message(wsh_util_core.g_ret_sts_error, l_module_name);
1914 IF l_debug_on THEN
1915 wsh_debug_sv.log(l_module_name, 'Could not obtain locks on some or all delivery legs of delivery', p_delivery_id);
1916 END IF;
1917 ELSE
1918 IF l_debug_on THEN
1919 wsh_debug_sv.log(l_module_name, 'Could not obtain lock on delivery leg', p_dlvy_leg_id);
1920 END IF;
1921 END IF;
1922 --
1923 IF l_debug_on THEN
1924 WSH_DEBUG_SV.logmsg(l_module_name,'APP_EXCEPTION.APPLICATION_EXCEPTION exception has occured.',WSH_DEBUG_SV.C_EXCEP_LEVEL);
1925 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:APP_EXCEPTION.APPLICATION_EXCEPTION');
1926 END IF;
1927 --
1928 RAISE;
1929
1930 END Lock_Dlvy_Leg_No_Compare;
1931
1932
1933 END WSH_DELIVERY_LEGS_PVT;
1934