[Home] [Help]
PACKAGE BODY: APPS.WSH_DELIVERY_LEGS_GRP
Source
1 PACKAGE BODY WSH_DELIVERY_LEGS_GRP as
2 /* $Header: WSHDGGPB.pls 120.9 2007/01/05 00:48:12 anxsharm noship $ */
3
4 --
5 G_PKG_NAME CONSTANT VARCHAR2(50) := 'WSH_DELIVERY_LEGS_GRP';
6 --
7
8 -- forward declaration
9 PROCEDURE Get_Disabled_List (
10 p_del_leg_rec IN WSH_DELIVERY_LEGS_PVT.Delivery_Leg_Rec_Type
11 , p_action IN VARCHAR2 DEFAULT 'UPDATE'
12 , x_del_leg_rec OUT NOCOPY WSH_DELIVERY_LEGS_PVT.Delivery_Leg_Rec_Type
13 , x_return_status OUT NOCOPY VARCHAR2
14 );
15
16 PROCEDURE Delivery_Leg_Action(
17 p_api_version_number IN NUMBER,
18 p_init_msg_list IN VARCHAR2,
19 p_commit IN VARCHAR2,
20 p_rec_attr_tab IN dlvy_leg_tab_type,
21 p_action_prms IN action_parameters_rectype,
22 x_action_out_rec IN OUT NOCOPY action_out_rec_type,
23 x_return_status OUT NOCOPY VARCHAR2,
24 x_msg_count OUT NOCOPY NUMBER,
25 x_msg_data OUT NOCOPY VARCHAR2
26 ) IS
27
28 l_api_version CONSTANT NUMBER := 1.0;
29 l_api_name CONSTANT VARCHAR2(30) := 'delivery_leg_action';
30 l_debug_on BOOLEAN;
31 l_module_name CONSTANT VARCHAR2(100) := 'wsh.plsql.' || G_PKG_NAME || '.' || 'DELIVERY_LEG_ACTION';
32
33 CURSOR get_trip_ship_method(p_trip_id NUMBER) IS
34 SELECT ship_method_code
35 FROM wsh_trips
36 WHERE trip_id=p_trip_id;
37
38 CURSOR get_delivery_info( p_delivery_id IN NUMBER ) is
39 SELECT name,
40 organization_id,
41 status_code,
42 planned_flag,
43 NVL(shipment_direction, 'O'), -- J IB jckwok
44 NVL(ignore_for_planning, 'N'), -- OTM R12, glog proj
45 NVL(tms_interface_flag,WSH_NEW_DELIVERIES_PVT.C_TMS_NOT_TO_BE_SENT) -- OTM R12, glog proj
46 FROM wsh_new_deliveries
47 WHERE delivery_id = p_delivery_id;
48
49 CURSOR get_parent_leg(p_delivery_leg_id in number) is
50 select parent.delivery_leg_id, parent.delivery_id
51 from wsh_Delivery_legs child, wsh_Delivery_legs parent
52 where child.delivery_leg_id = p_delivery_leg_id
53 and child.parent_delivery_leg_id = parent.delivery_leg_id;
54
55 CURSOR c_get_delivery_id (p_delivery_leg_id IN NUMBER) IS
56 SELECT delivery_leg_id, delivery_id from wsh_Delivery_legs
57 WHERE delivery_leg_id = p_delivery_leg_id;
58
59 CURSOR get_delivery_from_leg(p_leg_id in number) IS
60 SELECT delivery_id
61 FROM wsh_delivery_legs
62 WHERE delivery_leg_id = p_leg_id;
63
64 -- Cursor c_bol_doc_set added for bug 4493263
65 CURSOR c_bol_doc_set IS
66 SELECT WRS.Report_Set_Id
67 FROM Wsh_Report_Sets Wrs,
68 Wsh_Report_Set_Lines Wrsl
69 WHERE Wrsl.Report_Set_Id = Wrs.Report_Set_Id
70 AND Wrs.Name = 'Bill of Lading Report';
71
72 l_parent_delivery_leg_id NUMBER;
73 l_parent_delivery_id NUMBER;
74 l_tmp_parent_delivery_id NUMBER;
75
76 l_organization_id NUMBER;
77 l_status_code VARCHAR2(30);
78 l_planned_flag VARCHAR2(30);
79 l_shipment_direction VARCHAR2(30);
80 --
81 l_return_status VARCHAR2(1);
82 l_msg_data VARCHAR2(32767);
83 l_msg_count NUMBER;
84
85 l_ship_method_code VARCHAR2(30);
86 l_bol_number NUMBER;
87
88 l_valid_ids wsh_util_core.id_tab_type;
89 l_error_ids wsh_util_core.id_tab_type;
90 l_valid_index_tab wsh_util_core.id_tab_type;
91 l_dlvy_rec_tab WSH_DELIVERY_VALIDATIONS.dlvy_rec_tab_type;
92 l_delivery_leg_id NUMBER;
93 l_delivery_id NUMBER;
94
95 l_ignore WSH_NEW_DELIVERIES.IGNORE_FOR_PLANNING%TYPE; -- OTM R12,glog proj
96 l_tms_interface_flag WSH_NEW_DELIVERIES.TMS_INTERFACE_FLAG%TYPE; -- OTM R12,glog proj
97
98 -- Added for bug 4493263
99 l_dummy_doc_set_params wsh_document_sets.document_set_tab_type;
100 l_dummy_id_tab wsh_util_core.id_tab_type;
101 l_delivery_id_tab wsh_util_core.id_tab_type;
102 l_doc_set_id NUMBER;
103
104
105 RECORD_LOCKED EXCEPTION;
106 PRAGMA EXCEPTION_INIT(RECORD_LOCKED, -54);
107
108 l_num_errors NUMBER := 0;
109 l_num_warnings NUMBER := 0;
110 l_loop_warnings NUMBER :=0;
111 l_index NUMBER;
112
113 -- PACK J: KVENKATE
114 l_action_prms action_parameters_rectype;
115 l_action_out_rec action_out_rec_type;
116 l_delivery_name VARCHAR2(30);
117 /*3301344*/
118 wsh_create_document_error EXCEPTION;
119 l_trip_name VARCHAR2(30);
120 -- PACK J: KVENKATE
121
122
123 BEGIN
124 --
125 l_debug_on := WSH_DEBUG_INTERFACE.g_debug;
126 --
127 IF l_debug_on IS NULL
128 THEN
129 l_debug_on := WSH_DEBUG_SV.is_debug_enabled;
130 END IF;
131 --
132
133 IF l_debug_on THEN
134 WSH_DEBUG_SV.push(l_module_name);
135 WSH_DEBUG_SV.log(l_module_name,'p_api_version_number',p_api_version_number);
136 WSH_DEBUG_SV.log(l_module_name,'p_init_msg_list',p_init_msg_list);
137 WSH_DEBUG_SV.log(l_module_name,'p_commit',p_commit);
138 WSH_DEBUG_SV.log(l_module_name,'p_action_prms.caller',p_action_prms.caller);
139 WSH_DEBUG_SV.log(l_module_name,'p_action_prms.action_code',p_action_prms.action_code);
140 WSH_DEBUG_SV.log(l_module_name,'x_action_out_rec.x_delivery_id',x_action_out_rec.x_delivery_id);
141 WSH_DEBUG_SV.log(l_module_name,'x_action_out_rec.x_trip_id',x_action_out_rec.x_trip_id);
142 WSH_DEBUG_SV.log(l_module_name,'p_rec_attr_tab.count',p_rec_attr_tab.count);
143 END IF;
144
145 -- OTM R12, there was no initialization to 'S' for x_return_status
146 -- Also, x_return_status was being used to add message earlier,
147 -- changed that to wsh_util_core.g_ret_sts_error below
148 x_return_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
149 l_ignore := NULL;
150 l_tms_interface_flag := NULL;
151
152 IF NOT FND_API.compatible_api_call(l_api_version,p_api_version_number,l_api_name,G_PKG_NAME) THEN
153 IF l_debug_on THEN
154 WSH_DEBUG_SV.log(l_module_name,'Not comatible');
155 END IF;
156 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
157 END IF;
158
159 IF FND_API.to_boolean(p_init_msg_list) THEN
160 FND_MSG_PUB.initialize;
161 END IF;
162
163 IF (p_action_prms.caller IS NULL) THEN
164 FND_MESSAGE.SET_NAME('WSH','WSH_REQUIRED_FIELD_NULL');
165 FND_MESSAGE.SET_TOKEN('FIELD_NAME','p_action_prms.caller');
166 wsh_util_core.add_message(x_return_status,l_module_name);
167 raise fnd_api.g_exc_error;
168 END IF;
169 IF (p_action_prms.action_code IS NULL ) THEN
170 FND_MESSAGE.SET_NAME('WSH','WSH_REQUIRED_FIELD_NULL');
171 FND_MESSAGE.SET_TOKEN('FIELD_NAME','p_action_prms.action_code');
172 wsh_util_core.add_message(x_return_status,l_module_name);
173 raise fnd_api.g_exc_error;
174 END IF;
175
176 IF (nvl(p_action_prms.phase,1) < 1) THEN
177 FND_MESSAGE.SET_NAME('WSH','WSH_REQUIRED_FIELD_NULL');
178 FND_MESSAGE.SET_TOKEN('FIELD_NAME','p_action_prms.phase');
179 wsh_util_core.add_message(x_return_status,l_module_name);
180 raise fnd_api.g_exc_error;
181 END IF;
182
183 IF (x_action_out_rec.x_delivery_id IS NULL) THEN
184 FND_MESSAGE.SET_NAME('WSH','WSH_REQUIRED_FIELD_NULL');
185 FND_MESSAGE.SET_TOKEN('FIELD_NAME','x_action_out_rec.x_delivery_id');
186 wsh_util_core.add_message(x_return_status,l_module_name);
187 raise fnd_api.g_exc_error;
188 END IF;
189
190 IF (x_action_out_rec.x_trip_id IS NULL) THEN
191 FND_MESSAGE.SET_NAME('WSH','WSH_REQUIRED_FIELD_NULL');
192 FND_MESSAGE.SET_TOKEN('FIELD_NAME','x_action_out_rec.x_trip_id');
193 wsh_util_core.add_message(x_return_status,l_module_name);
194 raise fnd_api.g_exc_error;
195 END IF;
196
197 IF ( p_rec_attr_tab.count < 0 ) THEN
198 FND_MESSAGE.SET_NAME('WSH','WSH_REQUIRED_FIELD_NULL');
199 FND_MESSAGE.SET_TOKEN('FIELD_NAME','p_rec_attr_tab.count');
200 wsh_util_core.add_message(x_return_status,l_module_name);
201 raise fnd_api.g_exc_error;
202 END IF;
203
204 WSH_ACTIONS_LEVELS.set_validation_level (
205 p_entity => 'DLEG',
206 p_caller => p_action_prms.caller,
207 p_phase => p_action_prms.phase,
208 p_action => p_action_prms.action_code,
209 x_return_status => l_return_status);
210
211 IF l_debug_on THEN
212 WSH_DEBUG_SV.log(l_module_name,'C_LOCK_RECORDS_LVL',
213 WSH_ACTIONS_LEVELS.g_validation_level_tab(WSH_ACTIONS_LEVELS.C_LOCK_RECORDS_LVL));
214 WSH_DEBUG_SV.log(l_module_name,'C_TRIP_SMC_LVL',
215 WSH_ACTIONS_LEVELS.g_validation_level_tab(WSH_ACTIONS_LEVELS.C_TRIP_SMC_LVL));
216 END IF;
217 WSH_UTIL_CORE.api_post_call(p_return_status =>l_return_status,
218 x_num_warnings =>l_num_warnings,
219 x_num_errors =>l_num_errors);
220
221 l_index := p_rec_attr_tab.FIRST;
222 WHILE l_index IS NOT NULL LOOP
223 BEGIN
224 /* Fix for bug 3337292
225 Since print-bol has a recursive calls to same group API,
226 Rollback done for generate-bol should not affect the rollback for print-bol.
227 Need to have separate savepoints and separate rollbacks based on the action.
228 Now, savepoints are defined under the IF condition for the action_code
229
230 IMPORTANT NOTE: If any new action or savepoint is added, please change the respective
231 code in the Exception block to handle the Rollbacks specific to the new action or new savepoint.
232 */
233
234
235 -- Bug 5532887
236 l_delivery_leg_id:= NULL;
237 l_delivery_id := NULL;
238 l_parent_delivery_leg_id := NULL;
239 l_parent_delivery_id := NULL;
240
241 OPEN get_parent_leg(p_rec_attr_tab(l_index).delivery_leg_id);
242 FETCH get_parent_leg INTO l_parent_delivery_leg_id, l_parent_delivery_id;
243 IF get_parent_leg%FOUND THEN
244 l_delivery_leg_id := l_parent_delivery_leg_id;
245 l_delivery_id := l_parent_delivery_id;
246 ELSE
247 OPEN c_get_delivery_id (p_rec_attr_tab(l_index).delivery_leg_id);
248 FETCH c_get_delivery_id
249 INTO l_parent_delivery_leg_id, l_parent_delivery_id;
250 CLOSE c_get_delivery_id;
251 l_delivery_leg_id := l_parent_delivery_leg_id;
252 l_delivery_id := l_parent_delivery_id;
253 END IF;
254
255 CLOSE get_parent_leg;
256
257 IF l_debug_on THEN
258 WSH_DEBUG_SV.log(l_module_name,'p_rec_attr_tab(l_index).delivery_leg_id',p_rec_attr_tab(l_index).delivery_leg_id);
259 WSH_DEBUG_SV.log(l_module_name,'l_parent_delivery_leg_id',l_parent_delivery_leg_id);
260 WSH_DEBUG_SV.log(l_module_name,'l_delivery_leg_id',l_delivery_leg_id);
261 WSH_DEBUG_SV.log(l_module_name,'l_parent_delivery_id',l_parent_delivery_id);
262 WSH_DEBUG_SV.log(l_module_name,'l_delivery_id',l_delivery_id);
263 END IF;
264
265
266 IF (p_action_prms.action_code = 'GENERATE-BOL') THEN
267
268 --Bug fix 3337292
269 SAVEPOINT action_genbol_sp;
270
271 IF (WSH_ACTIONS_LEVELS.g_validation_level_tab(WSH_ACTIONS_LEVELS.C_LOCK_RECORDS_LVL) = 1) THEN
272
273 WSH_DELIVERY_LEGS_PVT.Lock_Delivery_Leg(
274 p_rowid => p_rec_attr_tab(l_index).rowid,
275 p_delivery_leg_info => p_rec_attr_tab(l_index));
276
277 WSH_UTIL_CORE.api_post_call(p_return_status =>l_return_status,
278 x_num_warnings =>l_loop_warnings,
279 x_num_errors =>l_num_errors);
280 END IF;
281
282 IF (WSH_ACTIONS_LEVELS.g_validation_level_tab(WSH_ACTIONS_LEVELS.C_TRIP_SMC_LVL) = 1) THEN
283 OPEN get_trip_ship_method(x_action_out_rec.x_trip_id);
284 FETCH get_trip_ship_method INTO l_ship_method_code;
285 CLOSE get_trip_ship_method;
286
287 /*3301344 : Get the trip name from trip id */
288 l_trip_name := wsh_trips_pvt.get_name(x_action_out_rec.x_trip_id);
289
290 IF (l_ship_method_code IS NULL ) THEN
291 l_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
292 /*3301344*/
293 IF l_debug_on THEN
294 WSH_DEBUG_SV.log(l_module_name,'l_ship_method_code',l_ship_method_code);
295 END IF;
296
297 FND_MESSAGE.SET_NAME('WSH','WSH_BOL_NULL_SHIP_METHOD_ERROR');
298 FND_MESSAGE.SET_TOKEN('TRIP_NAME',l_trip_name);
299 wsh_util_core.add_message(l_return_status,l_module_name);
300
301 RAISE wsh_create_document_error;
302
303 END IF;
304
305 /*3301344 : Commented out the following code*/
306
307 /* IF l_debug_on THEN
308 WSH_DEBUG_SV.log(l_module_name,'l_ship_method_code',l_ship_method_code);
309 WSH_DEBUG_SV.log(l_module_name,'l_return_status',l_return_status);
310 END IF;
311 WSH_UTIL_CORE.api_post_call(p_return_status =>l_return_status,
312 x_num_warnings =>l_loop_warnings,
313 x_num_errors =>l_num_errors);
314 */
315 END IF;
316
317
318 OPEN get_delivery_info(x_action_out_rec.x_delivery_id);
319 FETCH get_delivery_info
320 INTO l_delivery_name,
321 l_organization_id,
322 l_status_code,
323 l_planned_flag,
324 l_shipment_direction,
325 l_ignore, -- OTM R12, glog proj
326 l_tms_interface_flag; -- OTM R12, glog proj
327 CLOSE get_delivery_info;
328
329 --OTM R12, glog proj, add debug
330 IF l_debug_on THEN
331 WSH_DEBUG_SV.log(l_module_name,'Delivery id',x_action_out_rec.x_delivery_id);
332 WSH_DEBUG_SV.log(l_module_name,'Organization id',l_organization_id);
333 WSH_DEBUG_SV.log(l_module_name,'Status_Code',l_status_code);
334 WSH_DEBUG_SV.log(l_module_name,'Planned_Flag',l_planned_flag);
335 WSH_DEBUG_SV.log(l_module_name,'Shipment Direction',l_shipment_direction);
336 WSH_DEBUG_SV.log(l_module_name,'Ignore for Planning',l_ignore);
337 WSH_DEBUG_SV.log(l_module_name,'Tms Interface Flag',l_tms_interface_flag);
338 END IF;
339
340
341 l_dlvy_rec_tab(1).delivery_id := x_action_out_rec.x_delivery_id;
342 l_dlvy_rec_tab(1).organization_id := l_organization_id;
343 l_dlvy_rec_tab(1).status_code := l_status_code;
344 l_dlvy_rec_tab(1).planned_flag := l_planned_flag;
345 l_dlvy_rec_tab(1).shipment_direction := l_shipment_direction;
346 l_dlvy_rec_tab(1).ignore_for_planning := l_ignore; -- OTM R12, glog proj
347 l_dlvy_rec_tab(1).tms_interface_flag := l_tms_interface_flag; -- OTM R12, glog proj
348
349 WSH_DELIVERY_VALIDATIONS.Is_Action_Enabled(
350 p_dlvy_rec_tab => l_dlvy_rec_tab,
351 p_action => p_action_prms.action_code,
352 p_caller => p_action_prms.caller,
353 x_return_status => l_return_status,
354 x_valid_ids => l_valid_ids,
355 x_error_ids => l_error_ids,
356 x_valid_index_tab => l_valid_index_tab);
357
358 IF l_debug_on THEN
359 WSH_DEBUG_SV.log(l_module_name,'WSH_DELIVERY_VALIDATIONS.Is_Action_Enabled l_return_status',l_return_status);
360 END IF;
361
362 WSH_UTIL_CORE.api_post_call(p_return_status =>l_return_status,
363 x_num_warnings =>l_loop_warnings,
364 x_num_errors =>l_num_errors);
365
366 IF (WSH_ACTIONS_LEVELS.g_validation_level_tab(WSH_ACTIONS_LEVELS.C_BOL_NUM_LVL) = 1) THEN
367 SELECT count(*)
368 INTO l_bol_number
369 FROM WSH_DOCUMENT_INSTANCES wdi,
370 WSH_DELIVERY_LEGS wdl
371 WHERE wdi.entity_id = wdl.delivery_leg_id
372 AND wdi.entity_name = 'WSH_DELIVERY_LEGS'
373 AND wdi.status <> 'CANCELLED'
374 AND wdl.delivery_leg_id = l_delivery_leg_id;--p_rec_attr_tab(l_index).delivery_leg_id;
375
376 IF (l_bol_number > 0 ) THEN
377 l_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
378 END IF;
379
380 IF l_debug_on THEN
381 WSH_DEBUG_SV.log(l_module_name,'l_bol_number',l_bol_number);
382 WSH_DEBUG_SV.log(l_module_name,'l_return_status',l_return_status);
383 END IF;
384 WSH_UTIL_CORE.api_post_call(p_return_status =>l_return_status,
385 x_num_warnings =>l_loop_warnings,
386 x_num_errors =>l_num_errors);
387 END IF;
388
389
390 WSH_BOLS_PVT.Insert_Row(
391 x_return_status => l_return_status,
392 x_msg_count => l_msg_count,
393 x_msg_data => l_msg_data,
394 p_entity_name => 'WSH_DELIVERY_LEGS',
395 x_entity_id => l_delivery_leg_id,
396 p_application_id => 665 ,
397 p_location_id => p_action_prms.p_Pick_Up_Location_Id,
398 p_document_type => 'BOL',
399 p_document_sub_type => p_action_prms.p_Ship_Method,
400 -- p_ledger_id => 1, --LE Uptake
401 x_document_number => x_action_out_rec.x_bol_number,
402 x_trip_id => x_action_out_rec.x_trip_Id,
403 x_trip_name => x_action_out_rec.x_trip_Name,
404 x_delivery_id => x_action_out_rec.x_delivery_Id,
405 p_pick_up_location_Id => p_action_prms.p_Pick_Up_Location_Id,
406 p_drop_off_location_Id => p_action_prms.p_Drop_Off_Location_Id,
407 p_carrier_id => p_action_prms.p_Carrier_Id);
408
409 IF l_debug_on THEN
410 WSH_DEBUG_SV.log(l_module_name,' x_action_out_rec.x_bol_number: '|| x_action_out_rec.x_bol_number);
411 WSH_DEBUG_SV.log(l_module_name,'WSH_BOLS_PVT.Insert_Row l_return_status',l_return_status);
412 END IF;
413 WSH_UTIL_CORE.api_post_call(p_return_status =>l_return_status,
414 x_num_warnings =>l_loop_warnings,
415 x_num_errors =>l_num_errors);
416
417 ELSIF (p_action_prms.action_code = 'PRINT-BOL') THEN
418
419 --Bug fix 3337292
420 SAVEPOINT action_prnbol_sp;
421
422 OPEN get_parent_leg(p_rec_attr_tab(l_index).delivery_leg_id);
423 FETCH get_parent_leg INTO l_parent_delivery_leg_id, l_tmp_parent_delivery_id;
424
425 IF get_parent_leg%FOUND THEN
426 l_delivery_leg_id := l_parent_delivery_leg_id;
427 ELSE
428 l_delivery_leg_id := p_rec_attr_tab(l_index).delivery_leg_id;
429 END IF;
430 CLOSE get_parent_leg;
431
432 SELECT count(*)
433 INTO l_bol_number
434 FROM WSH_DOCUMENT_INSTANCES wdi,
435 WSH_DELIVERY_LEGS wdl
436 WHERE wdi.entity_id = wdl.delivery_leg_id
437 AND wdi.entity_name = 'WSH_DELIVERY_LEGS'
438 AND wdi.status <> 'CANCELLED'
439 AND wdl.delivery_leg_id = l_delivery_leg_id;
440
441 IF l_debug_on THEN
442 wsh_debug_sv.log(l_module_name, 'BOL count for delivery', l_bol_number);
443 wsh_debug_sv.log(l_module_name, 'delivery leg', l_delivery_leg_id);
444 END IF;
445
446 OPEN get_delivery_info(x_action_out_rec.x_delivery_id);
447 FETCH get_delivery_info
448 INTO l_delivery_name,
449 l_organization_id,
450 l_status_code,
451 l_planned_flag,
452 l_shipment_direction,
453 l_ignore, -- OTM R12, glog proj
454 l_tms_interface_flag; -- OTM R12, glog proj
455 CLOSE get_delivery_info;
456
457 --OTM R12, glog proj, add debug
458 IF l_debug_on THEN
459 WSH_DEBUG_SV.log(l_module_name,'Delivery id',x_action_out_rec.x_delivery_id);
460 WSH_DEBUG_SV.log(l_module_name,'Organization id',l_organization_id);
461 WSH_DEBUG_SV.log(l_module_name,'Status_Code',l_status_code);
462 WSH_DEBUG_SV.log(l_module_name,'Planned_Flag',l_planned_flag);
463 WSH_DEBUG_SV.log(l_module_name,'Shipment Direction',l_shipment_direction);
464 WSH_DEBUG_SV.log(l_module_name,'Ignore for Planning',l_ignore);
465 WSH_DEBUG_SV.log(l_module_name,'Tms Interface Flag',l_tms_interface_flag);
466 END IF;
467
468 l_dlvy_rec_tab(1).delivery_id := x_action_out_rec.x_delivery_id;
469 l_dlvy_rec_tab(1).organization_id := l_organization_id;
470 l_dlvy_rec_tab(1).status_code := l_status_code;
471 l_dlvy_rec_tab(1).planned_flag := l_planned_flag;
472 l_dlvy_rec_tab(1).shipment_direction := l_shipment_direction;
473 l_dlvy_rec_tab(1).ignore_for_planning := l_ignore; -- OTM R12, glog proj
474 l_dlvy_rec_tab(1).tms_interface_flag := l_tms_interface_flag; -- OTM R12, glog proj
475
476 IF l_bol_number = 0
477 THEN
478 --{
479 l_action_prms.caller := p_action_prms.caller;
480 l_action_prms.action_code := 'GENERATE-BOL';
481 l_action_prms.phase := p_action_prms.phase;
482 l_action_prms.p_Pick_Up_Location_Id := p_action_prms.p_pick_Up_Location_Id;
483 l_action_prms.p_Ship_Method := p_action_prms.p_Ship_Method;
484 l_action_prms.p_Drop_Off_Location_Id := p_action_prms.p_Drop_Off_Location_Id;
485 l_action_prms.p_Carrier_Id := p_action_prms.p_Carrier_Id;
486
487 l_action_out_rec.x_trip_id := x_action_out_rec.x_trip_id;
488 l_action_out_rec.x_trip_name := x_action_out_rec.x_trip_name;
489 l_action_out_rec.x_delivery_id := x_action_out_rec.x_delivery_id;
490
491 WSH_DELIVERY_LEGS_GRP.DELIVERY_LEG_ACTION(
492 p_api_version_number => p_api_version_number,
493 p_init_msg_list => FND_API.G_FALSE,
494 p_commit => FND_API.G_FALSE,
495 p_rec_attr_tab => p_rec_attr_tab,
496 p_action_prms => l_action_prms,
497 x_action_out_rec => l_action_out_rec,
498 x_return_status => l_return_status,
499 x_msg_count => l_msg_count,
500 x_msg_data => l_msg_data);
501
502 x_action_out_rec := l_action_out_rec;
503
504 WSH_UTIL_CORE.api_post_call(p_return_status =>l_return_status,
505 x_num_warnings =>l_loop_warnings,
506 x_num_errors =>l_num_errors);
507
508 -- }
509 END IF; -- if l_bol_number = 0
510
511 -- Start of bug-fix 4493263
512 OPEN c_bol_doc_set;
513 FETCH c_bol_doc_set into l_doc_set_id;
514 CLOSE c_bol_doc_set;
515
516 IF ( l_doc_set_id IS NOT NULL )
517 THEN
518 l_delivery_id_tab(1) := l_delivery_id;
519 IF l_debug_on THEN
520 wsh_debug_sv.log(l_module_name, 'Calling Print_Document_Sets for delivery', l_delivery_id);
521 END IF;
522
523 WSH_DOCUMENT_SETS.Print_Document_Sets(
524 p_report_set_id => l_doc_set_id,
525 p_organization_id => l_organization_id,
526 p_trip_ids => l_dummy_id_tab,
527 p_stop_ids => l_dummy_id_tab,
528 p_delivery_ids => l_delivery_id_tab,
529 p_document_param_info => l_dummy_doc_set_params,
530 x_return_status => l_return_status);
531
532 wsh_util_core.api_post_call(
533 p_return_status => l_return_status,
534 x_num_warnings => l_loop_warnings,
535 x_num_errors => l_num_errors,
536 p_msg_data => l_msg_data );
537 END IF;
538 -- End of bug-fix 4493263
539 END IF; --'GENERATE-BOL'
540
541 IF l_loop_warnings > 0 THEN
542 l_num_warnings := l_num_warnings + 1;
543 END IF;
544
545
546 x_action_out_rec.valid_id_tab(x_action_out_rec.valid_id_tab.COUNT + 1) := l_delivery_leg_id;
547
548 EXCEPTION
549 /*3301344 : Added the following exception section*/
550
551 WHEN wsh_create_document_error THEN
552 -- OTM R12, glog proj, close all cursors
553 IF get_trip_ship_method%ISOPEN THEN
554 CLOSE get_trip_ship_method;
555 END IF;
556 IF get_delivery_info%ISOPEN THEN
557 CLOSE get_delivery_info;
558 END IF;
559 IF get_parent_leg%ISOPEN THEN
560 CLOSE get_parent_leg;
561 END IF;
562 IF get_delivery_from_leg%ISOPEN THEN
563 CLOSE get_delivery_from_leg;
564 END IF;
565 IF c_bol_doc_set%ISOPEN THEN
566 CLOSE c_bol_doc_set;
567 END IF;
568
569 --Bug fix 3337292
570 IF p_action_prms.action_code = 'GENERATE-BOL' THEN
571 ROLLBACK TO action_genbol_sp;
572 ELSIF p_action_prms.action_code = 'PRINT-BOL' THEN
573 ROLLBACK TO action_prnbol_sp;
574 END IF;
575 x_return_status := wsh_util_core.g_ret_sts_error;
576 --
577 -- Debug Statements
578 --
579 IF l_debug_on THEN
580 WSH_DEBUG_SV.logmsg(l_module_name,'DELIVERY_LEG_ACTION exception has occured.',WSH_DEBUG_SV.C_EXCEP_LEVEL);
581 END IF;
582 --
583 IF l_debug_on THEN
584 WSH_DEBUG_SV.pop(l_module_name);
585 END IF;
586
587 RETURN;
588
589 WHEN fnd_api.g_exc_error THEN
590 -- OTM R12, glog proj, close all cursors
591 IF get_trip_ship_method%ISOPEN THEN
592 CLOSE get_trip_ship_method;
593 END IF;
594 IF get_delivery_info%ISOPEN THEN
595 CLOSE get_delivery_info;
596 END IF;
597 IF get_parent_leg%ISOPEN THEN
598 CLOSE get_parent_leg;
599 END IF;
600 IF get_delivery_from_leg%ISOPEN THEN
601 CLOSE get_delivery_from_leg;
602 END IF;
603 IF c_bol_doc_set%ISOPEN THEN
604 CLOSE c_bol_doc_set;
605 END IF;
606 --Bug fix 3337292
607 IF p_action_prms.action_code = 'GENERATE-BOL' THEN
608 ROLLBACK TO action_genbol_sp;
609 ELSIF p_action_prms.action_code = 'PRINT-BOL' THEN
610 ROLLBACK TO action_prnbol_sp;
611 END IF;
612
613 WHEN fnd_api.g_exc_unexpected_error THEN
614 -- OTM R12, glog proj, close all cursors
615 IF get_trip_ship_method%ISOPEN THEN
616 CLOSE get_trip_ship_method;
617 END IF;
618 IF get_delivery_info%ISOPEN THEN
619 CLOSE get_delivery_info;
620 END IF;
621 IF get_parent_leg%ISOPEN THEN
622 CLOSE get_parent_leg;
623 END IF;
624 IF get_delivery_from_leg%ISOPEN THEN
625 CLOSE get_delivery_from_leg;
626 END IF;
627 IF c_bol_doc_set%ISOPEN THEN
628 CLOSE c_bol_doc_set;
629 END IF;
630 --Bug fix 3337292
631 IF p_action_prms.action_code = 'GENERATE-BOL' THEN
632 ROLLBACK TO action_genbol_sp;
633 ELSIF p_action_prms.action_code = 'PRINT-BOL' THEN
634 ROLLBACK TO action_prnbol_sp;
635 END IF;
636
637 WHEN others THEN
638 -- OTM R12, glog proj, close all cursors
639 IF get_trip_ship_method%ISOPEN THEN
640 CLOSE get_trip_ship_method;
641 END IF;
642 IF get_delivery_info%ISOPEN THEN
643 CLOSE get_delivery_info;
644 END IF;
645 IF get_parent_leg%ISOPEN THEN
646 CLOSE get_parent_leg;
647 END IF;
648 IF get_delivery_from_leg%ISOPEN THEN
649 CLOSE get_delivery_from_leg;
650 END IF;
651 IF c_bol_doc_set%ISOPEN THEN
652 CLOSE c_bol_doc_set;
653 END IF;
654 --Bug fix 3337292
655 IF p_action_prms.action_code = 'GENERATE-BOL' THEN
656 ROLLBACK TO action_genbol_sp;
657 ELSIF p_action_prms.action_code = 'PRINT-BOL' THEN
658 ROLLBACK TO action_prnbol_sp;
659 END IF;
660 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
661
662
663 END;
664 l_index := p_rec_attr_tab.NEXT(l_index);
665 END LOOP;
666
667 IF (l_num_errors = p_rec_attr_tab.count ) THEN
668 raise FND_API.G_EXC_ERROR;
669 ELSIF (l_num_errors > 0 ) THEN
670 raise WSH_UTIL_CORE.G_EXC_WARNING;
671 ELSIF (l_num_warnings > 0 ) THEN
672 raise WSH_UTIL_CORE.G_EXC_WARNING;
673 ELSE
674 x_return_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
675 END IF;
676
677
678 IF FND_API.To_Boolean( p_commit ) THEN
679 COMMIT WORK;
680 END IF;
681
682 FND_MSG_PUB.Count_And_Get
683 ( p_count => x_msg_count,
684 p_data => x_msg_data,
685 p_encoded => FND_API.G_FALSE
686 );
687
688
689 IF l_debug_on THEN
690 WSH_DEBUG_SV.pop(l_module_name);
691 END IF;
692
693 EXCEPTION
694 WHEN RECORD_LOCKED THEN
695 x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
696 FND_MESSAGE.Set_Name('WSH', 'WSH_NO_LOCK');
697 wsh_util_core.add_message(x_return_status,l_module_name);
698 FND_MSG_PUB.Count_And_Get (
699 p_count => x_msg_count,
700 p_data => x_msg_data,
701 p_encoded => FND_API.G_FALSE);
702 IF l_debug_on THEN
703 WSH_DEBUG_SV.log(l_module_name,'Message Count',x_msg_count);
704 WSH_DEBUG_SV.log(l_module_name,'Message Data', x_msg_data);
705 WSH_DEBUG_SV.logmsg(l_module_name,'RECORD_LOCKED exception has occured.',WSH_DEBUG_SV.C_EXCEP_LEVEL);
706 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:RECORD_LOCKED');
707 END IF;
708
709
710 WHEN FND_API.G_EXC_ERROR THEN
711 x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR ;
712 FND_MSG_PUB.Count_And_Get (
713 p_count => x_msg_count,
714 p_data => x_msg_data,
715 p_encoded => FND_API.G_FALSE);
716 IF l_debug_on THEN
717 WSH_DEBUG_SV.log(l_module_name,'Message Count',x_msg_count);
718 WSH_DEBUG_SV.log(l_module_name,'Message Data', x_msg_data);
719 WSH_DEBUG_SV.logmsg(l_module_name,'FND_API.G_EXC_ERROR exception has occured.',WSH_DEBUG_SV.C_EXCEP_LEVEL);
720 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:FND_API.G_EXC_ERROR');
721 END IF;
722
723
724 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
725 x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR ;
726 FND_MSG_PUB.Count_And_Get (
727 p_count => x_msg_count,
728 p_data => x_msg_data,
729 p_encoded => FND_API.G_FALSE);
730 IF l_debug_on THEN
731 WSH_DEBUG_SV.log(l_module_name,'Message Count',x_msg_count);
732 WSH_DEBUG_SV.log(l_module_name,'Message Data', x_msg_data);
733 WSH_DEBUG_SV.logmsg(l_module_name,'FND_API.G_EXC_UNEXPECTED_ERROR exception has occured.',WSH_DEBUG_SV.C_EXCEP_LEVEL);
734 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:FND_API.G_EXC_UNEXPECTED_ERROR');
735 END IF;
736
737
738 WHEN WSH_UTIL_CORE.G_EXC_WARNING THEN
739 x_return_status := WSH_UTIL_CORE.G_RET_STS_WARNING;
740 FND_MSG_PUB.Count_And_Get (
741 p_count => x_msg_count,
742 p_data => x_msg_data,
743 p_encoded => FND_API.G_FALSE);
744 IF l_debug_on THEN
745 WSH_DEBUG_SV.log(l_module_name,'Message Count',x_msg_count);
746 WSH_DEBUG_SV.log(l_module_name,'Message Data', x_msg_data);
747 WSH_DEBUG_SV.logmsg(l_module_name,'WSH_UTIL_CORE.G_EXC_WARNING exception has occured.',WSH_DEBUG_SV.C_EXCEP_LEVEL);
748 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:WSH_UTIL_CORE.G_EXC_WARNING');
749 END IF;
750
751
752 WHEN OTHERS THEN
753 x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR ;
754 wsh_util_core.default_handler ('WSH_DELIVERY_LEGS_GRP.DELIVERY_LEG_ACTION');
755 FND_MSG_PUB.Count_And_Get (
756 p_count => x_msg_count,
757 p_data => x_msg_data,
758 p_encoded => FND_API.G_FALSE);
759 IF l_debug_on THEN
760 WSH_DEBUG_SV.log(l_module_name,'Message Count',x_msg_count);
761 WSH_DEBUG_SV.log(l_module_name,'Message Data', x_msg_data);
762 WSH_DEBUG_SV.logmsg(l_module_name,'Unexpected error has occured. Oracle error message is '||
763 SQLERRM,WSH_DEBUG_SV.C_UNEXPEC_ERR_LEVEL);
764
765 END IF;
766
767
768 END Delivery_Leg_Action;
769 --========================================================================
770 -- PROCEDURE : Update_Delivery_Leg Wrapper API PUBLIC
771 --
772 -- PARAMETERS: p_api_version_number known api version error buffer
773 -- p_init_msg_list FND_API.G_TRUE to reset list
774 -- x_return_status return status
775 -- x_msg_count number of messages in the list
776 -- x_msg_data text of messages
777 -- p_in_rec Record for caller, phase
778 -- and action_code ('UPDATE' )
779 -- p_delivery_leg_tab Table of Attributes for the delivery leg entity
780 -- x_out_rec for future usage.
781 -- VERSION : current version 1.0
782 -- initial version 1.0
783 -- COMMENT : Updates a record in wsh_delivery_legs table with information
784 -- specified in p_delivery_leg_tab. Please note that as per perfomance
785 -- standards, if you need to update a field to null, then use the
786 -- fnd_api.g_miss_(num/char/date) value for that field. If a field
787 -- has a null value, it will not be updated.
788
789 PROCEDURE Update_Delivery_Leg(
790 p_api_version_number IN NUMBER,
791 p_init_msg_list IN VARCHAR2,
792 p_commit IN VARCHAR2,
793 p_delivery_leg_tab IN WSH_DELIVERY_LEGS_GRP.dlvy_leg_tab_type,
794 p_in_rec IN WSH_DELIVERY_LEGS_GRP.action_parameters_rectype,
795 x_out_rec OUT NOCOPY WSH_DELIVERY_LEGS_GRP.action_out_rec_type,
796 x_return_status OUT NOCOPY VARCHAR2,
797 x_msg_count OUT NOCOPY NUMBER,
798 x_msg_data OUT NOCOPY VARCHAR2) IS
799
800 l_api_version_number CONSTANT NUMBER := 1.0;
801 l_api_name CONSTANT VARCHAR2(30) := 'Update_Delivery_Leg';
802 l_debug_on BOOLEAN;
803 l_module_name CONSTANT VARCHAR2(100) := 'wsh.plsql.' || G_PKG_NAME || '.' || 'UPDATE_DELIVERY_LEGS';
804 l_delivery_leg_rec WSH_DELIVERY_LEGS_PVT.Delivery_Leg_Rec_Type;
805
806 l_return_status VARCHAR2(1);
807 l_num_warnings NUMBER;
808 l_num_errors NUMBER;
809
810 cursor c_is_final_leg (p_dleg_id in number) is
811 select d.delivery_id from
812 wsh_new_deliveries d, wsh_delivery_legs l, wsh_trip_stops s
813 where l.delivery_id = d.delivery_id
814 and d.ultimate_dropoff_location_id = s.stop_location_id
815 and l.drop_off_stop_id = s.stop_id
816 and l.delivery_leg_id = p_dleg_id;
817
818 l_delivery_id number;
819
820 RECORD_LOCKED EXCEPTION;
821 PRAGMA EXCEPTION_INIT(RECORD_LOCKED, -54);
822
823
824
825 Begin
826
827 l_debug_on := WSH_DEBUG_INTERFACE.g_debug;
828 --
829 IF l_debug_on IS NULL
830 THEN
831 l_debug_on := WSH_DEBUG_SV.is_debug_enabled;
832 END IF;
833 IF l_debug_on THEN
834 WSH_DEBUG_SV.push(l_module_name);
835 END IF;
836
837 x_return_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
838
839 IF (p_in_rec.action_code= 'UPDATE') THEN
840
841 FOR l_index in p_delivery_leg_tab.FIRST .. p_delivery_leg_tab.LAST LOOP
842
843 IF p_in_rec.caller like 'FTE%' THEN
844
845 IF p_delivery_leg_tab(l_index).POD_DATE is NOT NULL
846 AND p_delivery_leg_tab(l_index).POD_DATE <> FND_API.G_MISS_DATE THEN
847
848
849 -- See if the delivery leg is the last on the delivery.
850
851 OPEN c_is_final_leg(p_delivery_leg_tab(l_index).delivery_leg_id);
852 FETCH c_is_final_leg INTO l_delivery_id;
853 CLOSE c_is_final_leg;
854
855 -- If it is the final leg, sync the POD date with delivery.
856
857 IF l_delivery_id IS NOT NULL THEN
858
859 -- Lock the delivery before update
860
861 wsh_new_deliveries_pvt.lock_dlvy_no_compare(
862 p_delivery_id => l_delivery_id);
863
864
865 UPDATE wsh_new_deliveries
866 SET DELIVERED_DATE = p_delivery_leg_tab(l_index).POD_DATE
867 WHERE delivery_id = l_delivery_id;
868
869 END IF;
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 Get_Disabled_List',WSH_DEBUG_SV.C_PROC_LEVEL);
878 END IF;
879 Get_Disabled_List(p_del_leg_rec => p_delivery_leg_tab(l_index),
880 p_action => 'UPDATE',
881 x_del_leg_rec => l_delivery_leg_rec,
882 x_return_status => x_return_status);
883
884
885 IF x_return_status in (WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR, WSH_UTIL_CORE.G_RET_STS_ERROR) THEN
886 IF l_debug_on THEN
887 WSH_DEBUG_SV.pop(l_module_name);
888 END IF;
889
890 RETURN;
891
892 END IF;
893
894 IF l_debug_on THEN
895 WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_DELIVERY_LEGS_PVT.Update_Delivery_Leg',WSH_DEBUG_SV.C_PROC_LEVEL);
896 END IF;
897 WSH_DELIVERY_LEGS_PVT.Update_Delivery_Leg(
898 p_rowid => NULL,
899 p_delivery_leg_info => l_delivery_leg_rec,
900 x_return_status => l_return_status);
901
902 IF l_debug_on THEN
903 WSH_DEBUG_SV.log(l_module_name,'Update_Delivery_Leg x_return_status',l_return_status);
904 END IF;
905 WSH_UTIL_CORE.api_post_call(
906 p_return_status => l_return_status,
907 x_num_warnings => l_num_warnings,
908 x_num_errors => l_num_errors,
909 p_module_name => l_module_name,
910 p_msg_data => 'WSH_PUB_UPDATE_FAILURE',
911 p_token1 => 'ENTITY',
912 p_value1 => 'Delivery_Leg');
913
914 END LOOP;
915
916 END IF;
917
918 IF l_debug_on THEN
919 WSH_DEBUG_SV.pop(l_module_name);
920 END IF;
921
922
923 EXCEPTION
924
925 WHEN app_exception.application_exception or app_exception.record_lock_exception THEN
926 x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
927 FND_MESSAGE.Set_Name('WSH', 'WSH_NO_LOCK');
928 wsh_util_core.add_message(x_return_status,l_module_name);
929 IF l_debug_on THEN
930 wsh_debug_sv.log(l_module_name, 'Could not lock delivery', l_delivery_id);
931 WSH_DEBUG_SV.logmsg(l_module_name,'APP_EXCEPTION.APPLICATION_EXCEPTION exception has occured.',WSH_DEBUG_SV.C_EXCEP_LEVEL);
932 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:APP_EXCEPTION.APPLICATION_EXCEPTION');
933 END IF;
934 --
935
936 WHEN others THEN
937
938 IF c_is_final_leg%isopen THEN
939 close c_is_final_leg;
940 END IF;
941 wsh_util_core.default_handler('WSH_DELIVERY_LEGS_GRP.Update_Delivery_Leg',l_module_name);
942 x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR;
943 --
944 IF l_debug_on THEN
945 WSH_DEBUG_SV.logmsg(l_module_name,'Unexpected error has occured. Oracle error message is '|| SQLERRM,WSH_DEBUG_SV.C_UNEXPEC_ERR_LEVEL);
946 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:OTHERS');
947 END IF;
948
949 END Update_Delivery_Leg;
950
951
952 PROCEDURE Get_Disabled_List (
953 p_del_leg_rec IN WSH_DELIVERY_LEGS_PVT.Delivery_Leg_Rec_Type
954 , p_action IN VARCHAR2 DEFAULT 'UPDATE'
955 , x_del_leg_rec OUT NOCOPY WSH_DELIVERY_LEGS_PVT.Delivery_Leg_Rec_Type
956 , x_return_status OUT NOCOPY VARCHAR2
957 ) IS
958
959 cursor c_get_leg_record(p_dleg_id in number) IS
960 select
961 DELIVERY_LEG_ID,
962 DELIVERY_ID ,
963 SEQUENCE_NUMBER ,
964 LOADING_ORDER_FLAG ,
965 PICK_UP_STOP_ID ,
966 DROP_OFF_STOP_ID ,
967 GROSS_WEIGHT ,
968 NET_WEIGHT ,
969 WEIGHT_UOM_CODE ,
970 VOLUME ,
971 VOLUME_UOM_CODE ,
972 CREATION_DATE ,
973 CREATED_BY ,
974 LAST_UPDATE_DATE ,
975 LAST_UPDATED_BY ,
976 LAST_UPDATE_LOGIN ,
977 PROGRAM_APPLICATION_ID ,
978 PROGRAM_ID ,
979 PROGRAM_UPDATE_DATE ,
980 REQUEST_ID ,
981 LOAD_TENDER_STATUS ,
982 SHIPPER_TITLE ,
983 SHIPPER_PHONE ,
984 POD_FLAG ,
985 POD_BY ,
986 POD_DATE ,
987 EXPECTED_POD_DATE ,
988 BOOKING_OFFICE ,
989 SHIPPER_EXPORT_REF ,
990 CARRIER_EXPORT_REF ,
991 DOC_NOTIFY_PARTY ,
992 AETC_NUMBER ,
993 SHIPPER_SIGNED_BY ,
994 SHIPPER_DATE ,
995 CARRIER_SIGNED_BY ,
996 CARRIER_DATE ,
997 DOC_ISSUE_OFFICE ,
998 DOC_ISSUED_BY ,
999 DOC_DATE_ISSUED ,
1000 SHIPPER_HM_BY ,
1001 SHIPPER_HM_DATE ,
1002 CARRIER_HM_BY ,
1003 CARRIER_HM_DATE ,
1004 BOOKING_NUMBER ,
1005 PORT_OF_LOADING ,
1006 PORT_OF_DISCHARGE ,
1007 SERVICE_CONTRACT ,
1008 BILL_FREIGHT_TO ,
1009 FTE_TRIP_ID ,
1010 REPRICE_REQUIRED ,
1011 ACTUAL_ARRIVAL_DATE ,
1012 ACTUAL_DEPARTURE_DATE ,
1013 ACTUAL_RECEIPT_DATE ,
1014 TRACKING_DRILLDOWN_FLAG ,
1015 STATUS_CODE ,
1016 TRACKING_REMARKS ,
1017 CARRIER_EST_DEPARTURE_DATE ,
1018 CARRIER_EST_ARRIVAL_DATE ,
1019 LOADING_START_DATETIME ,
1020 LOADING_END_DATETIME ,
1021 UNLOADING_START_DATETIME ,
1022 UNLOADING_END_DATETIME ,
1023 DELIVERED_QUANTITY ,
1024 LOADED_QUANTITY ,
1025 RECEIVED_QUANTITY ,
1026 ORIGIN_STOP_ID ,
1027 DESTINATION_STOP_ID ,
1028 ROWID ,
1029 PARENT_DELIVERY_LEG_ID
1030 FROM wsh_delivery_legs where
1031 delivery_leg_id = p_dleg_id;
1032
1033 l_api_version_number CONSTANT NUMBER := 1.0;
1034 l_api_name CONSTANT VARCHAR2(30) := 'Get_Disabled_List';
1035 l_debug_on BOOLEAN;
1036 l_module_name CONSTANT VARCHAR2(100) := 'wsh.plsql.' || G_PKG_NAME || '.' || 'Get_Disabled_List';
1037 l_dleg_rec WSH_DELIVERY_LEGS_PVT.Delivery_Leg_Rec_Type;
1038
1039 Begin
1040
1041 l_debug_on := WSH_DEBUG_INTERFACE.g_debug;
1042 --
1043 IF l_debug_on IS NULL
1044 THEN
1045 l_debug_on := WSH_DEBUG_SV.is_debug_enabled;
1046 END IF;
1047 IF l_debug_on THEN
1048 WSH_DEBUG_SV.push(l_module_name);
1049 END IF;
1050
1051 x_return_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
1052
1053 OPEN c_get_leg_record(p_del_leg_rec.delivery_leg_id);
1054 FETCH c_get_leg_record INTO l_dleg_rec;
1055 IF c_get_leg_record%NOTFOUND THEN
1056 CLOSE c_get_leg_record;
1057 RAISE NO_DATA_FOUND;
1058 end if;
1059 CLOSE c_get_leg_record;
1060
1061
1062 IF p_del_leg_rec.DELIVERY_ID = FND_API.G_MISS_NUM THEN
1063 l_dleg_rec.DELIVERY_ID := NULL;
1064 ELSIF p_del_leg_rec.DELIVERY_ID IS NOT NULL THEN
1065 l_dleg_rec.DELIVERY_ID := p_del_leg_rec.DELIVERY_ID;
1066 END IF;
1067 IF p_del_leg_rec.SEQUENCE_NUMBER = FND_API.G_MISS_NUM THEN
1068 l_dleg_rec.SEQUENCE_NUMBER := NULL;
1069 ELSIF p_del_leg_rec.SEQUENCE_NUMBER IS NOT NULL THEN
1070 l_dleg_rec.SEQUENCE_NUMBER := p_del_leg_rec.SEQUENCE_NUMBER;
1071 END IF;
1072 IF p_del_leg_rec.LOADING_ORDER_FLAG = FND_API.G_MISS_CHAR THEN
1073 l_dleg_rec.LOADING_ORDER_FLAG := NULL;
1074 ELSIF p_del_leg_rec.LOADING_ORDER_FLAG IS NOT NULL THEN
1075 l_dleg_rec.LOADING_ORDER_FLAG := p_del_leg_rec.LOADING_ORDER_FLAG;
1076 END IF;
1077 IF p_del_leg_rec.PICK_UP_STOP_ID = FND_API.G_MISS_NUM THEN
1078 l_dleg_rec.PICK_UP_STOP_ID := NULL;
1079 ELSIF p_del_leg_rec.PICK_UP_STOP_ID IS NOT NULL THEN
1080 l_dleg_rec.PICK_UP_STOP_ID := p_del_leg_rec.PICK_UP_STOP_ID;
1081 END IF;
1082 IF p_del_leg_rec.DROP_OFF_STOP_ID = FND_API.G_MISS_NUM THEN
1083 l_dleg_rec.DROP_OFF_STOP_ID := NULL;
1084 ELSIF p_del_leg_rec.DROP_OFF_STOP_ID IS NOT NULL THEN
1085 l_dleg_rec.DROP_OFF_STOP_ID := p_del_leg_rec.DROP_OFF_STOP_ID;
1086 END IF;
1087 IF p_del_leg_rec.GROSS_WEIGHT = FND_API.G_MISS_NUM THEN
1088 l_dleg_rec.GROSS_WEIGHT := NULL;
1089 ELSIF p_del_leg_rec.GROSS_WEIGHT IS NOT NULL THEN
1090 l_dleg_rec.GROSS_WEIGHT := p_del_leg_rec.GROSS_WEIGHT;
1091 END IF;
1092 IF p_del_leg_rec.NET_WEIGHT = FND_API.G_MISS_NUM THEN
1093 l_dleg_rec.NET_WEIGHT := NULL;
1094 ELSIF p_del_leg_rec.NET_WEIGHT IS NOT NULL THEN
1095 l_dleg_rec.NET_WEIGHT := p_del_leg_rec.NET_WEIGHT;
1096 END IF;
1097 IF p_del_leg_rec.WEIGHT_UOM_CODE = FND_API.G_MISS_CHAR THEN
1098 l_dleg_rec.WEIGHT_UOM_CODE := NULL;
1099 ELSIF p_del_leg_rec.WEIGHT_UOM_CODE IS NOT NULL THEN
1100 l_dleg_rec.WEIGHT_UOM_CODE := p_del_leg_rec.WEIGHT_UOM_CODE;
1101 END IF;
1102 IF p_del_leg_rec.VOLUME = FND_API.G_MISS_NUM THEN
1103 l_dleg_rec.VOLUME := NULL;
1104 ELSIF p_del_leg_rec.VOLUME IS NOT NULL THEN
1105 l_dleg_rec.VOLUME := p_del_leg_rec.VOLUME;
1106 END IF;
1107 IF p_del_leg_rec.VOLUME_UOM_CODE = FND_API.G_MISS_CHAR THEN
1108 l_dleg_rec.VOLUME_UOM_CODE := NULL;
1109 ELSIF p_del_leg_rec.VOLUME_UOM_CODE IS NOT NULL THEN
1110 l_dleg_rec.VOLUME_UOM_CODE := p_del_leg_rec.VOLUME_UOM_CODE;
1111 END IF;
1112 IF p_del_leg_rec.VOLUME_UOM_CODE = FND_API.G_MISS_NUM THEN
1113 l_dleg_rec.VOLUME_UOM_CODE := NULL;
1114 ELSIF p_del_leg_rec.VOLUME_UOM_CODE IS NOT NULL THEN
1115 l_dleg_rec.VOLUME_UOM_CODE := p_del_leg_rec.VOLUME_UOM_CODE;
1116 END IF;
1117 IF p_del_leg_rec.LAST_UPDATE_DATE = FND_API.G_MISS_DATE THEN
1118 l_dleg_rec.LAST_UPDATE_DATE := NULL;
1119 ELSIF p_del_leg_rec.LAST_UPDATE_DATE IS NOT NULL THEN
1120 l_dleg_rec.LAST_UPDATE_DATE := p_del_leg_rec.LAST_UPDATE_DATE;
1121 END IF;
1122 IF p_del_leg_rec.LAST_UPDATED_BY = FND_API.G_MISS_NUM THEN
1123 l_dleg_rec.LAST_UPDATED_BY := NULL;
1124 ELSIF p_del_leg_rec.LAST_UPDATED_BY IS NOT NULL THEN
1125 l_dleg_rec.LAST_UPDATED_BY := p_del_leg_rec.LAST_UPDATED_BY;
1126 END IF;
1127 IF p_del_leg_rec.LAST_UPDATE_LOGIN = FND_API.G_MISS_NUM THEN
1128 l_dleg_rec.LAST_UPDATE_LOGIN := NULL;
1129 ELSIF p_del_leg_rec.LAST_UPDATE_LOGIN IS NOT NULL THEN
1130 l_dleg_rec.LAST_UPDATE_LOGIN := p_del_leg_rec.LAST_UPDATE_LOGIN;
1131 END IF;
1132 IF p_del_leg_rec.PROGRAM_APPLICATION_ID = FND_API.G_MISS_NUM THEN
1133 l_dleg_rec.PROGRAM_APPLICATION_ID := NULL;
1134 ELSIF p_del_leg_rec.PROGRAM_APPLICATION_ID IS NOT NULL THEN
1135 l_dleg_rec.PROGRAM_APPLICATION_ID := p_del_leg_rec.PROGRAM_APPLICATION_ID;
1136 END IF;
1137 IF p_del_leg_rec.PROGRAM_ID = FND_API.G_MISS_NUM THEN
1138 l_dleg_rec.PROGRAM_ID := NULL;
1139 ELSIF p_del_leg_rec.PROGRAM_ID IS NOT NULL THEN
1140 l_dleg_rec.PROGRAM_ID := p_del_leg_rec.PROGRAM_ID;
1141 END IF;
1142 IF p_del_leg_rec.PROGRAM_UPDATE_DATE = FND_API.G_MISS_DATE THEN
1143 l_dleg_rec.PROGRAM_UPDATE_DATE := NULL;
1144 ELSIF p_del_leg_rec.PROGRAM_UPDATE_DATE IS NOT NULL THEN
1145 l_dleg_rec.PROGRAM_UPDATE_DATE := p_del_leg_rec.PROGRAM_UPDATE_DATE;
1146 END IF;
1147 IF p_del_leg_rec.REQUEST_ID = FND_API.G_MISS_NUM THEN
1148 l_dleg_rec.REQUEST_ID := NULL;
1149 ELSIF p_del_leg_rec.REQUEST_ID IS NOT NULL THEN
1150 l_dleg_rec.REQUEST_ID := p_del_leg_rec.REQUEST_ID;
1151 END IF;
1152 IF p_del_leg_rec.LOAD_TENDER_STATUS = FND_API.G_MISS_CHAR THEN
1153 l_dleg_rec.LOAD_TENDER_STATUS := NULL;
1154 ELSIF p_del_leg_rec.LOAD_TENDER_STATUS IS NOT NULL THEN
1155 l_dleg_rec.LOAD_TENDER_STATUS := p_del_leg_rec.LOAD_TENDER_STATUS;
1156 END IF;
1157 IF p_del_leg_rec.SHIPPER_TITLE = FND_API.G_MISS_CHAR THEN
1158 l_dleg_rec.SHIPPER_TITLE := NULL;
1159 ELSIF p_del_leg_rec.SHIPPER_TITLE IS NOT NULL THEN
1160 l_dleg_rec.SHIPPER_TITLE := p_del_leg_rec.SHIPPER_TITLE;
1161 END IF;
1162 IF p_del_leg_rec.SHIPPER_PHONE = FND_API.G_MISS_CHAR THEN
1163 l_dleg_rec.SHIPPER_PHONE := NULL;
1164 ELSIF p_del_leg_rec.SHIPPER_PHONE IS NOT NULL THEN
1165 l_dleg_rec.SHIPPER_PHONE := p_del_leg_rec.SHIPPER_PHONE;
1166 END IF;
1167 IF p_del_leg_rec.POD_FLAG = FND_API.G_MISS_CHAR THEN
1168 l_dleg_rec.POD_FLAG := NULL;
1169 ELSIF p_del_leg_rec.POD_FLAG IS NOT NULL THEN
1170 l_dleg_rec.POD_FLAG := p_del_leg_rec.POD_FLAG;
1171 END IF;
1172 IF p_del_leg_rec.POD_BY = FND_API.G_MISS_CHAR THEN
1173 l_dleg_rec.POD_BY := NULL;
1174 ELSIF p_del_leg_rec.POD_BY IS NOT NULL THEN
1175 l_dleg_rec.POD_BY := p_del_leg_rec.POD_BY;
1176 END IF;
1177 IF p_del_leg_rec.POD_DATE = FND_API.G_MISS_DATE THEN
1178 l_dleg_rec.POD_DATE := NULL;
1179 ELSIF p_del_leg_rec.POD_DATE IS NOT NULL THEN
1180 l_dleg_rec.POD_DATE := p_del_leg_rec.POD_DATE;
1181 END IF;
1182 IF p_del_leg_rec.EXPECTED_POD_DATE = FND_API.G_MISS_DATE THEN
1183 l_dleg_rec.EXPECTED_POD_DATE := NULL;
1184 ELSIF p_del_leg_rec.EXPECTED_POD_DATE IS NOT NULL THEN
1185 l_dleg_rec.EXPECTED_POD_DATE := p_del_leg_rec.EXPECTED_POD_DATE;
1186 END IF;
1187 IF p_del_leg_rec.BOOKING_OFFICE = FND_API.G_MISS_CHAR THEN
1188 l_dleg_rec.BOOKING_OFFICE := NULL;
1189 ELSIF p_del_leg_rec.BOOKING_OFFICE IS NOT NULL THEN
1190 l_dleg_rec.BOOKING_OFFICE := p_del_leg_rec.BOOKING_OFFICE;
1191 END IF;
1192 IF p_del_leg_rec.SHIPPER_EXPORT_REF = FND_API.G_MISS_CHAR THEN
1193 l_dleg_rec.SHIPPER_EXPORT_REF := NULL;
1194 ELSIF p_del_leg_rec.SHIPPER_EXPORT_REF IS NOT NULL THEN
1195 l_dleg_rec.SHIPPER_EXPORT_REF := p_del_leg_rec.SHIPPER_EXPORT_REF;
1196 END IF;
1197 IF p_del_leg_rec.CARRIER_EXPORT_REF = FND_API.G_MISS_CHAR THEN
1198 l_dleg_rec.CARRIER_EXPORT_REF := NULL;
1199 ELSIF p_del_leg_rec.CARRIER_EXPORT_REF IS NOT NULL THEN
1200 l_dleg_rec.CARRIER_EXPORT_REF := p_del_leg_rec.CARRIER_EXPORT_REF;
1201 END IF;
1202 IF p_del_leg_rec.DOC_NOTIFY_PARTY = FND_API.G_MISS_CHAR THEN
1203 l_dleg_rec.DOC_NOTIFY_PARTY := NULL;
1204 ELSIF p_del_leg_rec.DOC_NOTIFY_PARTY IS NOT NULL THEN
1205 l_dleg_rec.DOC_NOTIFY_PARTY := p_del_leg_rec.DOC_NOTIFY_PARTY;
1206 END IF;
1207 IF p_del_leg_rec.AETC_NUMBER = FND_API.G_MISS_CHAR THEN
1208 l_dleg_rec.AETC_NUMBER := NULL;
1209 ELSIF p_del_leg_rec.AETC_NUMBER IS NOT NULL THEN
1210 l_dleg_rec.AETC_NUMBER := p_del_leg_rec.AETC_NUMBER;
1211 END IF;
1212 IF p_del_leg_rec.SHIPPER_SIGNED_BY = FND_API.G_MISS_CHAR THEN
1213 l_dleg_rec.SHIPPER_SIGNED_BY := NULL;
1214 ELSIF p_del_leg_rec.SHIPPER_SIGNED_BY IS NOT NULL THEN
1215 l_dleg_rec.SHIPPER_SIGNED_BY := p_del_leg_rec.SHIPPER_SIGNED_BY;
1216 END IF;
1217 IF p_del_leg_rec.SHIPPER_DATE = FND_API.G_MISS_DATE THEN
1218 l_dleg_rec.SHIPPER_DATE := NULL;
1219 ELSIF p_del_leg_rec.SHIPPER_DATE IS NOT NULL THEN
1220 l_dleg_rec.SHIPPER_DATE := p_del_leg_rec.SHIPPER_DATE;
1221 END IF;
1222 IF p_del_leg_rec.CARRIER_SIGNED_BY = FND_API.G_MISS_CHAR THEN
1223 l_dleg_rec.CARRIER_SIGNED_BY := NULL;
1224 ELSIF p_del_leg_rec.CARRIER_SIGNED_BY IS NOT NULL THEN
1225 l_dleg_rec.CARRIER_SIGNED_BY := p_del_leg_rec.CARRIER_SIGNED_BY;
1226 END IF;
1227 IF p_del_leg_rec.CARRIER_DATE = FND_API.G_MISS_DATE THEN
1228 l_dleg_rec.CARRIER_DATE := NULL;
1229 ELSIF p_del_leg_rec.CARRIER_DATE IS NOT NULL THEN
1230 l_dleg_rec.CARRIER_DATE := p_del_leg_rec.CARRIER_DATE;
1231 END IF;
1232 IF p_del_leg_rec.DOC_ISSUE_OFFICE = FND_API.G_MISS_CHAR THEN
1233 l_dleg_rec.DOC_ISSUE_OFFICE := NULL;
1234 ELSIF p_del_leg_rec.DOC_ISSUE_OFFICE IS NOT NULL THEN
1235 l_dleg_rec.DOC_ISSUE_OFFICE := p_del_leg_rec.DOC_ISSUE_OFFICE;
1236 END IF;
1237 IF p_del_leg_rec.DOC_ISSUED_BY = FND_API.G_MISS_CHAR THEN
1238 l_dleg_rec.DOC_ISSUED_BY := NULL;
1239 ELSIF p_del_leg_rec.DOC_ISSUED_BY IS NOT NULL THEN
1240 l_dleg_rec.DOC_ISSUED_BY := p_del_leg_rec.DOC_ISSUED_BY;
1241 END IF;
1242 IF p_del_leg_rec.DOC_DATE_ISSUED = FND_API.G_MISS_DATE THEN
1243 l_dleg_rec.DOC_DATE_ISSUED := NULL;
1244 ELSIF p_del_leg_rec.DOC_DATE_ISSUED IS NOT NULL THEN
1245 l_dleg_rec.DOC_DATE_ISSUED := p_del_leg_rec.DOC_ISSUED_BY;
1246 END IF;
1247 IF p_del_leg_rec.SHIPPER_HM_BY = FND_API.G_MISS_CHAR THEN
1248 l_dleg_rec.SHIPPER_HM_BY := NULL;
1249 ELSIF p_del_leg_rec.SHIPPER_HM_BY IS NOT NULL THEN
1250 l_dleg_rec.SHIPPER_HM_BY := p_del_leg_rec.SHIPPER_HM_BY;
1251 END IF;
1252 IF p_del_leg_rec.SHIPPER_HM_DATE = FND_API.G_MISS_DATE THEN
1253 l_dleg_rec.SHIPPER_HM_DATE := NULL;
1254 ELSIF p_del_leg_rec.SHIPPER_HM_DATE IS NOT NULL THEN
1255 l_dleg_rec.SHIPPER_HM_DATE := p_del_leg_rec.SHIPPER_HM_DATE;
1256 END IF;
1257 IF p_del_leg_rec.CARRIER_HM_BY = FND_API.G_MISS_CHAR THEN
1258 l_dleg_rec.CARRIER_HM_BY := NULL;
1259 ELSIF p_del_leg_rec.CARRIER_HM_BY IS NOT NULL THEN
1260 l_dleg_rec.CARRIER_HM_BY := p_del_leg_rec.CARRIER_HM_BY;
1261 END IF;
1262 IF p_del_leg_rec.CARRIER_HM_DATE = FND_API.G_MISS_DATE THEN
1263 l_dleg_rec.CARRIER_HM_DATE := NULL;
1264 ELSIF p_del_leg_rec.CARRIER_HM_DATE IS NOT NULL THEN
1265 l_dleg_rec.CARRIER_HM_DATE := p_del_leg_rec.CARRIER_HM_DATE;
1266 END IF;
1267 IF p_del_leg_rec.BOOKING_NUMBER = FND_API.G_MISS_CHAR THEN
1268 l_dleg_rec.BOOKING_NUMBER := NULL;
1269 ELSIF p_del_leg_rec.BOOKING_NUMBER IS NOT NULL THEN
1270 l_dleg_rec.BOOKING_NUMBER := p_del_leg_rec.BOOKING_NUMBER;
1271 END IF;
1272 IF p_del_leg_rec.PORT_OF_LOADING = FND_API.G_MISS_CHAR THEN
1273 l_dleg_rec.PORT_OF_LOADING := NULL;
1274 ELSIF p_del_leg_rec.PORT_OF_LOADING IS NOT NULL THEN
1275 l_dleg_rec.PORT_OF_LOADING := p_del_leg_rec.PORT_OF_LOADING;
1276 END IF;
1277 IF p_del_leg_rec.PORT_OF_DISCHARGE = FND_API.G_MISS_CHAR THEN
1278 l_dleg_rec.PORT_OF_DISCHARGE := NULL;
1279 ELSIF p_del_leg_rec.PORT_OF_DISCHARGE IS NOT NULL THEN
1280 l_dleg_rec.PORT_OF_DISCHARGE := p_del_leg_rec.PORT_OF_DISCHARGE;
1281 END IF;
1282 IF p_del_leg_rec.SERVICE_CONTRACT = FND_API.G_MISS_CHAR THEN
1283 l_dleg_rec.SERVICE_CONTRACT := NULL;
1284 ELSIF p_del_leg_rec.SERVICE_CONTRACT IS NOT NULL THEN
1285 l_dleg_rec.SERVICE_CONTRACT := p_del_leg_rec.SERVICE_CONTRACT;
1286 END IF;
1287 IF p_del_leg_rec.BILL_FREIGHT_TO = FND_API.G_MISS_CHAR THEN
1288 l_dleg_rec.BILL_FREIGHT_TO := NULL;
1289 ELSIF p_del_leg_rec.BILL_FREIGHT_TO IS NOT NULL THEN
1290 l_dleg_rec.BILL_FREIGHT_TO := p_del_leg_rec.BILL_FREIGHT_TO;
1291 END IF;
1292 IF p_del_leg_rec.FTE_TRIP_ID = FND_API.G_MISS_NUM THEN
1293 l_dleg_rec.FTE_TRIP_ID := NULL;
1294 ELSIF p_del_leg_rec.FTE_TRIP_ID IS NOT NULL THEN
1295 l_dleg_rec.FTE_TRIP_ID := p_del_leg_rec.FTE_TRIP_ID;
1296 END IF;
1297 IF p_del_leg_rec.REPRICE_REQUIRED = FND_API.G_MISS_CHAR THEN
1298 l_dleg_rec.REPRICE_REQUIRED := NULL;
1299 ELSIF p_del_leg_rec.REPRICE_REQUIRED IS NOT NULL THEN
1300 l_dleg_rec.REPRICE_REQUIRED := p_del_leg_rec.REPRICE_REQUIRED;
1301 END IF;
1302 IF p_del_leg_rec.ACTUAL_ARRIVAL_DATE = FND_API.G_MISS_DATE THEN
1303 l_dleg_rec.ACTUAL_ARRIVAL_DATE := NULL;
1304 ELSIF p_del_leg_rec.ACTUAL_ARRIVAL_DATE IS NOT NULL THEN
1305 l_dleg_rec.ACTUAL_ARRIVAL_DATE := p_del_leg_rec.ACTUAL_ARRIVAL_DATE;
1306 END IF;
1307 IF p_del_leg_rec.ACTUAL_DEPARTURE_DATE = FND_API.G_MISS_DATE THEN
1308 l_dleg_rec.ACTUAL_DEPARTURE_DATE := NULL;
1309 ELSIF p_del_leg_rec.ACTUAL_DEPARTURE_DATE IS NOT NULL THEN
1310 l_dleg_rec.ACTUAL_DEPARTURE_DATE := p_del_leg_rec.ACTUAL_DEPARTURE_DATE;
1311 END IF;
1312 IF p_del_leg_rec.ACTUAL_RECEIPT_DATE = FND_API.G_MISS_DATE THEN
1313 l_dleg_rec.ACTUAL_RECEIPT_DATE := NULL;
1314 ELSIF p_del_leg_rec.ACTUAL_RECEIPT_DATE IS NOT NULL THEN
1315 l_dleg_rec.ACTUAL_RECEIPT_DATE := p_del_leg_rec.ACTUAL_RECEIPT_DATE;
1316 END IF;
1317 IF p_del_leg_rec.TRACKING_DRILLDOWN_FLAG = FND_API.G_MISS_CHAR THEN
1318 l_dleg_rec.TRACKING_DRILLDOWN_FLAG := NULL;
1319 ELSIF p_del_leg_rec.TRACKING_DRILLDOWN_FLAG IS NOT NULL THEN
1320 l_dleg_rec.TRACKING_DRILLDOWN_FLAG := p_del_leg_rec.TRACKING_DRILLDOWN_FLAG;
1321 END IF;
1322 IF p_del_leg_rec.STATUS_CODE = FND_API.G_MISS_CHAR THEN
1323 l_dleg_rec.STATUS_CODE := NULL;
1324 ELSIF p_del_leg_rec.STATUS_CODE IS NOT NULL THEN
1325 l_dleg_rec.STATUS_CODE := p_del_leg_rec.STATUS_CODE;
1326 END IF;
1327 IF p_del_leg_rec.TRACKING_REMARKS = FND_API.G_MISS_CHAR THEN
1328 l_dleg_rec.TRACKING_REMARKS := NULL;
1329 ELSIF p_del_leg_rec.TRACKING_REMARKS IS NOT NULL THEN
1330 l_dleg_rec.TRACKING_REMARKS := p_del_leg_rec.TRACKING_REMARKS;
1331 END IF;
1332 IF p_del_leg_rec.CARRIER_EST_DEPARTURE_DATE = FND_API.G_MISS_DATE THEN
1333 l_dleg_rec.CARRIER_EST_DEPARTURE_DATE := NULL;
1334 ELSIF p_del_leg_rec.CARRIER_EST_DEPARTURE_DATE IS NOT NULL THEN
1335 l_dleg_rec.CARRIER_EST_DEPARTURE_DATE := p_del_leg_rec.CARRIER_EST_DEPARTURE_DATE;
1336 END IF;
1337 IF p_del_leg_rec.CARRIER_EST_ARRIVAL_DATE = FND_API.G_MISS_DATE THEN
1338 l_dleg_rec.CARRIER_EST_ARRIVAL_DATE := NULL;
1339 ELSIF p_del_leg_rec.CARRIER_EST_ARRIVAL_DATE IS NOT NULL THEN
1340 l_dleg_rec.CARRIER_EST_ARRIVAL_DATE := p_del_leg_rec.CARRIER_EST_ARRIVAL_DATE;
1341 END IF;
1342 IF p_del_leg_rec.LOADING_START_DATETIME = FND_API.G_MISS_DATE THEN
1343 l_dleg_rec.LOADING_START_DATETIME := NULL;
1344 ELSIF p_del_leg_rec.LOADING_START_DATETIME IS NOT NULL THEN
1345 l_dleg_rec.LOADING_START_DATETIME := p_del_leg_rec.LOADING_START_DATETIME;
1346 END IF;
1347 IF p_del_leg_rec.LOADING_END_DATETIME = FND_API.G_MISS_DATE THEN
1348 l_dleg_rec.LOADING_END_DATETIME := NULL;
1349 ELSIF p_del_leg_rec.LOADING_END_DATETIME IS NOT NULL THEN
1350 l_dleg_rec.LOADING_END_DATETIME := p_del_leg_rec.LOADING_END_DATETIME;
1351 END IF;
1352 IF p_del_leg_rec.UNLOADING_START_DATETIME = FND_API.G_MISS_DATE THEN
1353 l_dleg_rec.UNLOADING_START_DATETIME := NULL;
1354 ELSIF p_del_leg_rec.UNLOADING_START_DATETIME IS NOT NULL THEN
1355 l_dleg_rec.UNLOADING_START_DATETIME := p_del_leg_rec.UNLOADING_START_DATETIME;
1356 END IF;
1357 IF p_del_leg_rec.UNLOADING_END_DATETIME = FND_API.G_MISS_DATE THEN
1358 l_dleg_rec.UNLOADING_END_DATETIME := NULL;
1359 ELSIF p_del_leg_rec.UNLOADING_END_DATETIME IS NOT NULL THEN
1360 l_dleg_rec.UNLOADING_END_DATETIME := p_del_leg_rec.UNLOADING_END_DATETIME;
1361 END IF;
1362 IF p_del_leg_rec.DELIVERED_QUANTITY = FND_API.G_MISS_NUM THEN
1363 l_dleg_rec.DELIVERED_QUANTITY := NULL;
1364 ELSIF p_del_leg_rec.DELIVERED_QUANTITY IS NOT NULL THEN
1365 l_dleg_rec.DELIVERED_QUANTITY := p_del_leg_rec.DELIVERED_QUANTITY;
1366 END IF;
1367 IF p_del_leg_rec.LOADED_QUANTITY = FND_API.G_MISS_NUM THEN
1368 l_dleg_rec.LOADED_QUANTITY := NULL;
1369 ELSIF p_del_leg_rec.LOADED_QUANTITY IS NOT NULL THEN
1370 l_dleg_rec.LOADED_QUANTITY := p_del_leg_rec.LOADED_QUANTITY;
1371 END IF;
1372 IF p_del_leg_rec.RECEIVED_QUANTITY = FND_API.G_MISS_NUM THEN
1373 l_dleg_rec.RECEIVED_QUANTITY := NULL;
1374 ELSIF p_del_leg_rec.RECEIVED_QUANTITY IS NOT NULL THEN
1375 l_dleg_rec.RECEIVED_QUANTITY := p_del_leg_rec.RECEIVED_QUANTITY;
1376 END IF;
1377 IF p_del_leg_rec.ORIGIN_STOP_ID = FND_API.G_MISS_NUM THEN
1378 l_dleg_rec.ORIGIN_STOP_ID := NULL;
1379 ELSIF p_del_leg_rec.ORIGIN_STOP_ID IS NOT NULL THEN
1380 l_dleg_rec.ORIGIN_STOP_ID := p_del_leg_rec.ORIGIN_STOP_ID;
1381 END IF;
1382 IF p_del_leg_rec.DESTINATION_STOP_ID = FND_API.G_MISS_NUM THEN
1383 l_dleg_rec.DESTINATION_STOP_ID := NULL;
1384 ELSIF p_del_leg_rec.DESTINATION_STOP_ID IS NOT NULL THEN
1385 l_dleg_rec.DESTINATION_STOP_ID := p_del_leg_rec.DESTINATION_STOP_ID;
1386 END IF;
1387 IF p_del_leg_rec.parent_delivery_leg_id = FND_API.G_MISS_NUM THEN
1388 l_dleg_rec.parent_delivery_leg_id := NULL;
1389 ELSIF p_del_leg_rec.parent_delivery_leg_id IS NOT NULL THEN
1390 l_dleg_rec.parent_delivery_leg_id := p_del_leg_rec.parent_delivery_leg_id;
1391 END IF;
1392
1393
1394 x_del_leg_rec := l_dleg_rec;
1395
1396 IF l_debug_on THEN
1397 WSH_DEBUG_SV.pop(l_module_name);
1398 END IF;
1399
1400 EXCEPTION
1401
1402 WHEN others THEN
1403 wsh_util_core.default_handler('WSH_DELIVERY_LEGS_GRP.get_disabled_list',l_module_name);
1404 x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR;
1405 --
1406 IF c_get_leg_record%isopen THEN
1407 CLOSE c_get_leg_record;
1408 END IF;
1409 IF l_debug_on THEN
1410 WSH_DEBUG_SV.logmsg(l_module_name,'Unexpected error has occured. Oracle error message is '|| SQLERRM,WSH_DEBUG_SV.C_UNEXPEC_ERR_LEVEL);
1411 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:OTHERS');
1412 END IF;
1413
1414 END get_disabled_list;
1415
1416 END WSH_DELIVERY_LEGS_GRP;