[Home] [Help]
PACKAGE BODY: APPS.WSH_NEW_DELIVERIES_PVT
Source
1 PACKAGE BODY WSH_NEW_DELIVERIES_PVT as
2 /* $Header: WSHDETHB.pls 120.15.12010000.2 2008/10/17 13:33:29 anvarshn ship $ */
3
4 --
5 -- Package internal global variables
6 --
7 g_return_status VARCHAR2(1);
8
9 --
10 -- Package exceptions
11 --
12 wsh_duplicate_name EXCEPTION;
13
14 --
15 -- Procedure: Create_Delivery
16 -- Parameters: p_delivery_info - All Attributes of a Delivery Record
17 -- x_rowid - Rowid of delivery created
18 -- x_delivery_id - Delivery_Id of delivery created
19 -- x_name - Name of delivery created
20 -- x_return_status - Status of procedure call
21 -- Description: This procedure will create a delivery. It will
22 -- return to the use the delivery_id and name (if
23 -- not provided as a parameter.
24 --
25
26
27 --
28 G_PKG_NAME CONSTANT VARCHAR2(50) := 'WSH_NEW_DELIVERIES_PVT';
29 --
30 PROCEDURE Create_Delivery
31 (p_delivery_info IN Delivery_Rec_Type,
32 x_rowid OUT NOCOPY VARCHAR2,
33 x_delivery_id OUT NOCOPY NUMBER,
34 x_name OUT NOCOPY VARCHAR2,
35 x_return_status OUT NOCOPY VARCHAR2
36 ) IS
37
38 CURSOR get_next_delivery IS
39 SELECT wsh_new_deliveries_s.nextval
40 FROM sys.dual;
41
42 CURSOR count_delivery_rows (v_delivery_name VARCHAR2) IS
43 SELECT delivery_id
44 FROM wsh_new_deliveries
45 WHERE name = v_delivery_name;
46
47 l_delivery_name VARCHAR2(30);
48 l_temp_id NUMBER;
49 l_row_check NUMBER;
50 l_message VARCHAR2(30);
51
52 others EXCEPTION;
53
54 l_wh_type VARCHAR2(3);
55 l_return_status VARCHAR2(1);
56 l_ignore_for_planning VARCHAR2(1);
57 --
58
59 l_wf_rs VARCHAR2(1); -- Workflow Changes
60
61
62 l_debug_on BOOLEAN;
63 --
64 l_module_name CONSTANT VARCHAR2(100) := 'wsh.plsql.' || G_PKG_NAME || '.' || 'CREATE_DELIVERY';
65 --
66 BEGIN
67
68 -- initialize parameters
69 --
70 l_debug_on := WSH_DEBUG_INTERFACE.g_debug;
71 --
72 IF l_debug_on IS NULL
73 THEN
74 l_debug_on := WSH_DEBUG_SV.is_debug_enabled;
75 END IF;
76 --
77 IF l_debug_on THEN
78 WSH_DEBUG_SV.push(l_module_name);
79 WSH_DEBUG_SV.log(l_module_name,'p_delivery_info.delivery_id',
80 p_delivery_info.delivery_id);
81 WSH_DEBUG_SV.log(l_module_name,'p_delivery_info.name',
82 p_delivery_info.name);
83 END IF;
84 --
85 x_return_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
86 x_delivery_id := p_delivery_info.delivery_id;
87 x_name := p_delivery_info.name;
88
89 -- get next delivery id
90 IF x_delivery_id IS NULL THEN
91
92 OPEN get_next_delivery;
93 FETCH get_next_delivery INTO x_delivery_id;
94 CLOSE get_next_delivery;
95
96 IF l_debug_on THEN
97 WSH_DEBUG_SV.log(l_module_name,'x_delivery_id',x_delivery_id);
98 END IF;
99 END IF;
100 -- try to generate a new default delivery name
101 IF x_name IS NULL THEN
102 l_delivery_name := wsh_custom_pub.delivery_name(x_delivery_id,p_delivery_info);
103
104 -- shipping default make sure the delivery name is not duplicate
105 IF l_debug_on THEN
106 WSH_DEBUG_SV.log(l_module_name,'l_delivery_name',l_delivery_name);
107 WSH_DEBUG_SV.log(l_module_name,'x_delivery_id',x_delivery_id);
108 END IF;
109 IF ( l_delivery_name = to_char(x_delivery_id) ) THEN
110 l_temp_id := x_delivery_id;
111
112 LOOP
113
114 l_delivery_name := to_char(l_temp_id);
115
116 OPEN count_delivery_rows( l_delivery_name);
117 FETCH count_delivery_rows INTO l_row_check;
118 IF l_debug_on THEN
119 WSH_DEBUG_SV.log(l_module_name,'l_row_check',l_row_check);
120 END IF;
121 IF (count_delivery_rows%NOTFOUND) THEN
122 CLOSE count_delivery_rows;
123 EXIT;
124 END IF;
125
126 CLOSE count_delivery_rows;
127
128 OPEN get_next_delivery;
129 FETCH get_next_delivery INTO l_temp_id;
130 CLOSE get_next_delivery;
131 IF l_debug_on THEN
132 WSH_DEBUG_SV.log(l_module_name,'l_temp_id',l_temp_id);
133 END IF;
134 END LOOP;
135
136 x_delivery_id := l_temp_id;
137
138 END IF;
139
140 x_name := l_delivery_name;
141
142 ELSE
143
144 OPEN count_delivery_rows(x_name);
145 FETCH count_delivery_rows INTO l_row_check;
146 IF l_debug_on THEN
147 WSH_DEBUG_SV.log(l_module_name,'l_row_check',l_row_check);
148 END IF;
149 IF (count_delivery_rows%FOUND) THEN
150 CLOSE count_delivery_rows;
151 RAISE wsh_duplicate_name;
152 END IF;
153
154 CLOSE count_delivery_rows;
155
156 END IF;
157
158 INSERT INTO wsh_new_deliveries
159 (
160 delivery_id
161 ,name
162 ,planned_flag
163 ,status_code
164 ,delivery_type
165 ,loading_sequence
166 ,loading_order_flag
167 ,initial_pickup_date
168 ,initial_pickup_location_id
169 ,organization_id
170 ,ultimate_dropoff_location_id
171 ,ultimate_dropoff_date
172 ,customer_id
173 ,intmed_ship_to_location_id
174 ,pooled_ship_to_location_id
175 ,carrier_id
176 ,ship_method_code
177 ,freight_terms_code
178 ,fob_code
179 ,fob_location_id
180 ,waybill
181 ,dock_code
182 ,acceptance_flag
183 ,accepted_by
184 ,accepted_date
185 ,acknowledged_by
186 ,confirmed_by
187 ,confirm_date
188 ,asn_date_sent
189 ,asn_status_code
190 ,asn_seq_number
191 ,gross_weight
192 ,net_weight
193 ,weight_uom_code
194 ,volume
195 ,volume_uom_code
196 ,additional_shipment_info
197 ,currency_code
198 ,attribute_category
199 ,attribute1
200 ,attribute2
201 ,attribute3
202 ,attribute4
203 ,attribute5
204 ,attribute6
205 ,attribute7
206 ,attribute8
207 ,attribute9
208 ,attribute10
209 ,attribute11
210 ,attribute12
211 ,attribute13
212 ,attribute14
213 ,attribute15
214 ,tp_attribute_category
215 ,tp_attribute1
216 ,tp_attribute2
217 ,tp_attribute3
218 ,tp_attribute4
219 ,tp_attribute5
220 ,tp_attribute6
221 ,tp_attribute7
222 ,tp_attribute8
223 ,tp_attribute9
224 ,tp_attribute10
225 ,tp_attribute11
226 ,tp_attribute12
227 ,tp_attribute13
228 ,tp_attribute14
229 ,tp_attribute15
230 ,global_attribute_category
231 ,global_attribute1
232 ,global_attribute2
233 ,global_attribute3
234 ,global_attribute4
235 ,global_attribute5
236 ,global_attribute6
237 ,global_attribute7
238 ,global_attribute8
239 ,global_attribute9
240 ,global_attribute10
241 ,global_attribute11
242 ,global_attribute12
243 ,global_attribute13
244 ,global_attribute14
245 ,global_attribute15
246 ,global_attribute16
247 ,global_attribute17
248 ,global_attribute18
249 ,global_attribute19
250 ,global_attribute20
251 ,creation_date
252 ,created_by
253 ,last_update_date
254 ,last_updated_by
255 ,last_update_login
256 ,program_application_id
257 ,program_id
258 ,program_update_date
259 ,request_id
260 ,batch_id
261 ,hash_value
262 ,source_header_id
263 ,number_of_lpn
264 /* Changes for Shipping Data Model Bug#1918342*/
265 ,cod_amount
266 ,cod_currency_code
267 ,cod_remit_to
268 ,cod_charge_paid_by
269 ,problem_contact_reference
270 ,port_of_loading
271 ,port_of_discharge
272 ,ftz_number
273 ,routed_export_txn
274 ,entry_number
275 ,routing_instructions
276 ,in_bond_code
277 ,shipping_marks
278 /* H Integration: datamodel changes wrudge */
279 ,service_level
280 ,mode_of_transport
281 ,assigned_to_fte_trips
282 ,auto_sc_exclude_flag
283 ,auto_ap_exclude_flag
284 /* J Inbound Logistics jckwok */
285 ,shipment_direction
286 ,vendor_id
287 ,party_id
288 ,routing_response_id
289 ,rcv_shipment_header_id
290 ,asn_shipment_header_id
291 ,shipping_control
292 /* J TP Release : ttrichy */
293 ,TP_DELIVERY_NUMBER
294 ,EARLIEST_PICKUP_DATE
295 ,LATEST_PICKUP_DATE
296 ,EARLIEST_DROPOFF_DATE
297 ,LATEST_DROPOFF_DATE
298 ,IGNORE_FOR_PLANNING
299 ,TP_PLAN_NAME
300 -- J: W/V Changes
301 ,WV_FROZEN_FLAG
302 ,HASH_STRING
303 ,delivered_date
304 -- bug 3667348
305 ,REASON_OF_TRANSPORT
306 ,DESCRIPTION
307 --OTM R12
308 ,TMS_INTERFACE_FLAG
309 ,TMS_VERSION_NUMBER
310 --
311 )
312 VALUES (
313 x_delivery_id
314 ,x_name
315 ,nvl(p_delivery_info.planned_flag,'N')
316 ,nvl(p_delivery_info.status_code,'OP')
317 ,nvl(p_delivery_info.delivery_type,'STANDARD')
318 ,p_delivery_info.loading_sequence
319 ,p_delivery_info.loading_order_flag
320 ,p_delivery_info.initial_pickup_date
321 ,p_delivery_info.initial_pickup_location_id
322 ,p_delivery_info.organization_id
323 ,p_delivery_info.ultimate_dropoff_location_id
324 ,p_delivery_info.ultimate_dropoff_date
325 ,p_delivery_info.customer_id
326 ,p_delivery_info.intmed_ship_to_location_id
327 ,p_delivery_info.pooled_ship_to_location_id
328 ,p_delivery_info.carrier_id
329 ,p_delivery_info.ship_method_code
330 ,p_delivery_info.freight_terms_code
331 ,p_delivery_info.fob_code
332 ,p_delivery_info.fob_location_id
333 ,p_delivery_info.waybill
334 ,p_delivery_info.dock_code
335 ,p_delivery_info.acceptance_flag
336 ,p_delivery_info.accepted_by
337 ,p_delivery_info.accepted_date
338 ,p_delivery_info.acknowledged_by
339 ,p_delivery_info.confirmed_by
340 ,p_delivery_info.confirm_date
341 ,p_delivery_info.asn_date_sent
342 ,p_delivery_info.asn_status_code
343 ,p_delivery_info.asn_seq_number
344 ,p_delivery_info.gross_weight
345 ,p_delivery_info.net_weight
346 ,p_delivery_info.weight_uom_code
347 ,p_delivery_info.volume
348 ,p_delivery_info.volume_uom_code
349 ,p_delivery_info.additional_shipment_info
350 ,p_delivery_info.currency_code
351 ,p_delivery_info.attribute_category
352 ,p_delivery_info.attribute1
353 ,p_delivery_info.attribute2
354 ,p_delivery_info.attribute3
355 ,p_delivery_info.attribute4
356 ,p_delivery_info.attribute5
357 ,p_delivery_info.attribute6
358 ,p_delivery_info.attribute7
359 ,p_delivery_info.attribute8
360 ,p_delivery_info.attribute9
361 ,p_delivery_info.attribute10
362 ,p_delivery_info.attribute11
363 ,p_delivery_info.attribute12
364 ,p_delivery_info.attribute13
365 ,p_delivery_info.attribute14
366 ,p_delivery_info.attribute15
367 ,p_delivery_info.tp_attribute_category
368 ,p_delivery_info.tp_attribute1
369 ,p_delivery_info.tp_attribute2
370 ,p_delivery_info.tp_attribute3
371 ,p_delivery_info.tp_attribute4
372 ,p_delivery_info.tp_attribute5
373 ,p_delivery_info.tp_attribute6
374 ,p_delivery_info.tp_attribute7
375 ,p_delivery_info.tp_attribute8
376 ,p_delivery_info.tp_attribute9
377 ,p_delivery_info.tp_attribute10
378 ,p_delivery_info.tp_attribute11
379 ,p_delivery_info.tp_attribute12
380 ,p_delivery_info.tp_attribute13
381 ,p_delivery_info.tp_attribute14
382 ,p_delivery_info.tp_attribute15
383 ,p_delivery_info.global_attribute_category
384 ,p_delivery_info.global_attribute1
385 ,p_delivery_info.global_attribute2
386 ,p_delivery_info.global_attribute3
387 ,p_delivery_info.global_attribute4
388 ,p_delivery_info.global_attribute5
389 ,p_delivery_info.global_attribute6
390 ,p_delivery_info.global_attribute7
391 ,p_delivery_info.global_attribute8
392 ,p_delivery_info.global_attribute9
393 ,p_delivery_info.global_attribute10
394 ,p_delivery_info.global_attribute11
395 ,p_delivery_info.global_attribute12
396 ,p_delivery_info.global_attribute13
397 ,p_delivery_info.global_attribute14
398 ,p_delivery_info.global_attribute15
399 ,p_delivery_info.global_attribute16
400 ,p_delivery_info.global_attribute17
401 ,p_delivery_info.global_attribute18
402 ,p_delivery_info.global_attribute19
403 ,p_delivery_info.global_attribute20
404 ,nvl(p_delivery_info.creation_date, SYSDATE)
405 ,nvl(p_delivery_info.created_by,FND_GLOBAL.USER_ID)
406 ,nvl(p_delivery_info.last_update_date, SYSDATE)
407 ,nvl(p_delivery_info.last_updated_by,FND_GLOBAL.USER_ID)
408 ,nvl(p_delivery_info.last_update_login,FND_GLOBAL.LOGIN_ID)
409 ,p_delivery_info.program_application_id
410 ,p_delivery_info.program_id
411 ,p_delivery_info.program_update_date
412 ,p_delivery_info.request_id
413 ,p_delivery_info.batch_id
414 ,p_delivery_info.hash_value
415 ,p_delivery_info.source_header_id
416 ,p_delivery_info.number_of_lpn
417 /* Changes for Shipping Data Model Bug#1918342*/
418 ,p_delivery_info.cod_amount
419 ,p_delivery_info.cod_currency_code
420 ,p_delivery_info.cod_remit_to
421 ,p_delivery_info.cod_charge_paid_by
422 ,p_delivery_info.problem_contact_reference
423 ,p_delivery_info.port_of_loading
424 ,p_delivery_info.port_of_discharge
425 ,p_delivery_info.ftz_number
426 ,p_delivery_info.routed_export_txn
427 ,p_delivery_info.entry_number
428 ,p_delivery_info.routing_instructions
429 ,p_delivery_info.in_bond_code
430 ,p_delivery_info.shipping_marks
431 /* H Integration: datamodel changes wrudge */
432 ,p_delivery_info.service_level
433 ,p_delivery_info.mode_of_transport
434 ,p_delivery_info.assigned_to_fte_trips
435 ,p_delivery_info.auto_sc_exclude_flag
436 ,p_delivery_info.auto_ap_exclude_flag
437 /* J Inbound Logistics jckwok */
438 ,nvl(p_delivery_info.shipment_direction, 'O')
439 ,p_delivery_info.vendor_id
440 ,p_delivery_info.party_id
441 ,p_delivery_info.routing_response_id
442 ,p_delivery_info.rcv_shipment_header_id
443 ,p_delivery_info.asn_shipment_header_id
444 ,p_delivery_info.shipping_control
445 /* J TP Release : ttrichy */
446 ,p_delivery_info.TP_DELIVERY_NUMBER
447 ,p_delivery_info.EARLIEST_PICKUP_DATE
448 ,p_delivery_info.LATEST_PICKUP_DATE
449 ,p_delivery_info.EARLIEST_DROPOFF_DATE
450 ,p_delivery_info.LATEST_DROPOFF_DATE
451 ,nvl(p_delivery_info.ignore_for_planning,'N')
452 ,p_delivery_info.TP_PLAN_NAME
453 -- J: W/V Changes
454 ,nvl(p_delivery_info.wv_frozen_flag, 'N')
455 ,p_delivery_info.hash_string
456 ,p_delivery_info.delivered_date
457 -- bug 3667348
458 ,p_delivery_info.REASON_OF_TRANSPORT
459 ,p_delivery_info.DESCRIPTION
460 -- bug 3667348
461 --OTM R12
462 ,WSH_NEW_DELIVERIES_PVT.C_TMS_NOT_TO_BE_SENT
463 ,1
464 --
465 )
466 RETURNING rowid
467 INTO x_rowid;
468
469
470 --/== Workflow Changes
471 IF l_debug_on THEN
472 WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_WF_STD.RAISE_EVENT',WSH_DEBUG_SV.C_PROC_LEVEL);
473 END IF;
474
475 WSH_WF_STD.RAISE_EVENT(p_entity_type => 'DELIVERY',
476 p_entity_id => x_delivery_id,
477 p_event => 'oracle.apps.wsh.delivery.gen.create',
478 p_organization_id => p_delivery_info.organization_id,
479 x_return_status => l_wf_rs);
480
481 IF l_debug_on THEN
482 WSH_DEBUG_SV.log(l_module_name,'Return status after calling WSH_WF_STD.RAISE_EVENT => ',l_wf_rs);
483 END IF;
484 -- Workflow Changes ==/
485
486 --
487 IF l_debug_on THEN
488 WSH_DEBUG_SV.log(l_module_name,'x_rowid',x_rowid);
489 WSH_DEBUG_SV.log(l_module_name,'x_delivery_id',x_delivery_id);
490 WSH_DEBUG_SV.log(l_module_name,'x_name',x_name);
491 WSH_DEBUG_SV.pop(l_module_name);
492 END IF;
493 --
494 EXCEPTION
495 WHEN wsh_duplicate_name THEN
496 FND_MESSAGE.Set_Name('FND', 'FORM_DUPLICATE_KEY_IN_INDEX');
497 x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
498 WSH_UTIL_CORE.ADD_MESSAGE(x_return_status,l_module_name);
499 --
500 IF l_debug_on THEN
501 WSH_DEBUG_SV.logmsg(l_module_name,'WSH_DUPLICATE_NAME exception has occured.',WSH_DEBUG_SV.C_EXCEP_LEVEL);
502 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:WSH_DUPLICATE_NAME');
503 END IF;
504 --
505 WHEN others THEN
506 wsh_util_core.default_handler('WSH_NEW_DELIVERIES_PVT.CREATE_DELIVERY',l_module_name);
507 x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR;
508 --
509 IF l_debug_on THEN
510 WSH_DEBUG_SV.logmsg(l_module_name,'Unexpected error has occured. Oracle error message is '|| SQLERRM,WSH_DEBUG_SV.C_UNEXPEC_ERR_LEVEL);
511 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:OTHERS');
512 END IF;
513 --
514 END Create_Delivery;
515
516
517 --
518 -- Procedure: Update_Delivery
519 -- Parameters: p_rowid - Rowid for delivery to be updated
520 -- p_delivery_info - All Attributes of a Delivery Record
521 -- x_return_status - Status of procedure call
522 -- Description: This procedure will update attributes of a delivery.
523 --
524
525 PROCEDURE Update_Delivery
526 (p_rowid IN VARCHAR2,
527 p_delivery_info IN Delivery_Rec_Type,
528 x_return_status OUT NOCOPY VARCHAR2
529 ) IS
530
531 -- J: W/V Changes
532 CURSOR get_del_info IS
533 SELECT rowid,
534 gross_weight,
535 net_weight,
536 volume,
537 weight_uom_code,
538 volume_uom_code,
539 wv_frozen_flag,
540 organization_id
541 FROM wsh_new_deliveries
542 WHERE delivery_id = p_delivery_info.delivery_id;
543
544 CURSOR c_iscarriersmcchanged IS
545 SELECT organization_id, name
546 FROM wsh_new_deliveries
547 WHERE delivery_id = p_delivery_info.delivery_id
548 and (carrier_id <> p_delivery_info.carrier_id
549 OR ship_method_code <> p_delivery_info.ship_method_code);
550
551 l_return_status VARCHAR2 (1);
552 l_wh_type VARCHAR2(3);
553
554
555 l_rowid VARCHAR2(30);
556 -- J: W/V Changes
557 l_gross_wt NUMBER;
558 l_net_wt NUMBER;
559 l_volume NUMBER;
560 l_old_gross_wt NUMBER;
561 l_old_net_wt NUMBER;
562 l_old_volume NUMBER;
563 l_weight_uom_code VARCHAR2(3);
564 l_volume_uom_code VARCHAR2(3);
565 l_frozen_flag VARCHAR2(1);
566 l_diff_gross_wt NUMBER;
567 l_diff_net_wt NUMBER;
568 l_diff_vol NUMBER;
569 e_wt_vol_fail EXCEPTION;
570 --
571 l_num_errors NUMBER := 0;
572 l_num_warnings NUMBER := 0;
573 --WF: CMR
574 l_wf_rs VARCHAR2(1);
575 l_del_ids WSH_UTIL_CORE.ID_TAB_TYPE;
576 l_del_old_carrier_ids WSH_UTIL_CORE.ID_TAB_TYPE;
577 l_del_new_carrier_ids WSH_UTIL_CORE.ID_TAB_TYPE;
578 l_organization_id NUMBER;
579 -- Following two variable are added for Bugfix #4587421
580 l_gross_weight NUMBER;
581 l_net_weight NUMBER;
582
583 --OTM R12
584 l_delivery_info_tab WSH_NEW_DELIVERIES_PVT.Delivery_Attr_Tbl_Type;
585 l_delivery_info WSH_NEW_DELIVERIES_PVT.Delivery_Rec_Type;
586 l_new_interface_flag_tab WSH_UTIL_CORE.COLUMN_TAB_TYPE;
587 l_tms_update VARCHAR2(1);
588 l_trip_not_found VARCHAR2(1);
589 l_trip_info_rec WSH_DELIVERY_VALIDATIONS.trip_info_rec_type;
590 l_tms_version_number WSH_NEW_DELIVERIES.TMS_VERSION_NUMBER%TYPE;
591 l_gc3_is_installed VARCHAR2(1);
592 l_sysdate DATE;
593 api_return_fail EXCEPTION;
594 --
595
596 --
597 l_debug_on BOOLEAN;
598 --
599 l_module_name CONSTANT VARCHAR2(100) := 'wsh.plsql.' || G_PKG_NAME || '.' || 'UPDATE_DELIVERY';
600 --
601 BEGIN
602
603 --
604 --
605 l_debug_on := WSH_DEBUG_INTERFACE.g_debug;
606 --
607 IF l_debug_on IS NULL
608 THEN
609 l_debug_on := WSH_DEBUG_SV.is_debug_enabled;
610 END IF;
611 --
612 IF l_debug_on THEN
613 WSH_DEBUG_SV.push(l_module_name);
614 --
615 WSH_DEBUG_SV.log(l_module_name,'P_ROWID',P_ROWID);
616 END IF;
617 --
618 x_return_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
619
620
621 --OTM R12, initialize
622 l_tms_update := 'N';
623 l_new_interface_flag_tab(1) := NULL;
624 l_gc3_is_installed := WSH_UTIL_CORE.G_GC3_IS_INSTALLED; -- this is global variable
625
626 IF l_gc3_is_installed IS NULL THEN
627 l_gc3_is_installed := WSH_UTIL_CORE.GC3_IS_INSTALLED; -- this is actual function
628 END IF;
629 --
630
631 /*CURRENTLY NOT IN USE
632 --WF: CMR
633 l_del_ids(1) := p_delivery_info.delivery_id;
634 */
635 --Bugfix #4587421
636 l_gross_weight := p_delivery_info.gross_weight;
637 l_net_weight := p_delivery_info.net_weight;
638
639 /*WSH_WF_STD.Get_Carrier(p_del_ids => l_del_ids,
640 x_del_old_carrier_ids => l_del_old_carrier_ids,
641 x_return_status => l_wf_rs);
642 */
643 -- J: W/V Changes
644 OPEN get_del_info;
645 FETCH get_del_info INTO l_rowid, l_gross_wt, l_net_wt, l_volume, l_weight_uom_code, l_volume_uom_code, l_frozen_flag, l_organization_id;
646 IF get_del_info%NOTFOUND THEN
647 CLOSE get_del_info;
648 RAISE no_data_found;
649 END IF;
650 CLOSE get_del_info;
651 IF p_rowid IS NOT NULL THEN
652 l_rowid := p_rowid;
653 END IF;
654
655 IF l_weight_uom_code <> p_delivery_info.weight_uom_code THEN
656
657 l_old_gross_wt := WSH_WV_UTILS.Convert_Uom(
658 from_uom => l_weight_uom_code,
659 to_uom => p_delivery_info.weight_uom_code,
660 quantity => l_gross_wt);
661
662 l_old_net_wt := WSH_WV_UTILS.Convert_Uom(
663 from_uom => l_weight_uom_code,
664 to_uom => p_delivery_info.weight_uom_code,
665 quantity => l_net_wt);
666 ELSE
667
668 l_old_gross_wt := l_gross_wt;
669 l_old_net_wt := l_net_wt;
670
671 END IF;
672
673 IF l_volume_uom_code <> p_delivery_info.volume_uom_code THEN
674
675 l_old_volume := WSH_WV_UTILS.Convert_Uom(
676 from_uom => l_volume_uom_code,
677 to_uom => p_delivery_info.volume_uom_code,
678 quantity => l_volume);
679
680 ELSE
681
682 l_old_volume := l_volume;
683
684 END IF;
685 -- Set wv_frozen_flag to Y if W/V info changes
686 IF (NVL(l_old_gross_wt,-99) <> NVL(p_delivery_info.gross_weight,-99)) OR
687 (NVL(l_old_net_wt,-99) <> NVL(p_delivery_info.net_weight,-99)) OR
688 (NVL(l_old_volume,-99) <> NVL(p_delivery_info.volume,-99)) THEN
689 -- Bug 5157444
690 IF l_organization_id <> p_delivery_info.organization_id
691 AND p_delivery_info.net_weight IS NULL
692 AND p_delivery_info.volume IS NULL
693 AND p_delivery_info.gross_weight IS NULL
694 THEN
695 l_frozen_flag := 'N';
696 ELSE
697 l_frozen_flag := 'Y';
698 END IF;
699 END IF;
700
701 --Bugfix #4587421
702 -- If the new gross weight is less than net weight then gross weight should be equal to net weight.
703 -- If Gross weight is Zero/Null then Net weight should be Zero/Null.
704 IF nvl(l_old_gross_wt , -99) = nvl(p_delivery_info.gross_weight, -99)
705 THEN
706 IF p_delivery_info.net_weight is not null
707 and nvl(p_delivery_info.gross_weight, 0) < p_delivery_info.net_weight
708 THEN
709 l_gross_weight := p_delivery_info.net_weight;
710 l_net_weight := p_delivery_info.net_weight;
711 END IF;
712 ELSIF nvl(l_old_gross_wt , -99) <> nvl(p_delivery_info.gross_weight, -99)
713 THEN
714 IF p_delivery_info.net_weight is not null
715 and nvl(p_delivery_info.gross_weight, 0) < p_delivery_info.net_weight
716 THEN
717 l_gross_weight := p_delivery_info.gross_weight;
718 l_net_weight := p_delivery_info.gross_weight;
719 END IF;
720
721 IF p_delivery_info.gross_weight is null
722 THEN
723 l_net_weight := p_delivery_info.gross_weight;
724 END IF;
725 END IF;
726
727 --OTM R12, check for tms update
728 IF l_gc3_is_installed = 'Y' AND
729 NVL(p_delivery_info.ignore_for_planning, 'N') = 'N' THEN
730
731 l_trip_not_found := 'N';
732 l_tms_version_number := 1;
733
734 --get trip information for delivery, no update when trip not OPEN
735 IF l_debug_on THEN
736 WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_DELIVERY_VALIDATIONS.GET_TRIP_INFORMATION',WSH_DEBUG_SV.C_PROC_LEVEL);
737 END IF;
738
739 WSH_DELIVERY_VALIDATIONS.get_trip_information
740 (p_delivery_id => p_delivery_info.delivery_id,
741 x_trip_info_rec => l_trip_info_rec,
742 x_return_status => l_return_status);
743
744 IF l_debug_on THEN
745 WSH_DEBUG_SV.log(l_module_name,'Return status after calling WSH_DELIVERY_VALIDATIONS.GET_TRIP_INFORMATION',l_return_status);
746 END IF;
747
748 IF (l_return_status IN (WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR, WSH_UTIL_CORE.G_RET_STS_ERROR)) THEN
749 IF l_debug_on THEN
750 WSH_DEBUG_SV.logmsg(l_module_name,'Error in WSH_DELIVERY_VALIDATIONS.get_trip_information');
751 END IF;
752 RAISE api_return_fail;
753 END IF;
754
755 IF (l_trip_info_rec.trip_id IS NULL) THEN
756 l_trip_not_found := 'Y';
757 END IF;
758
759 -- only do changes when there's no trip or trip status is OPEN
760 IF (l_trip_info_rec.status_code = 'OP' OR l_trip_not_found = 'Y') THEN
761
762 IF l_debug_on THEN
763 WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_DELIVERY_VALIDATIONS.get_delivery_information',WSH_DEBUG_SV.C_PROC_LEVEL);
764 END IF;
765
766 WSH_DELIVERY_VALIDATIONS.get_delivery_information(
767 p_delivery_id => p_delivery_info.delivery_id,
768 x_delivery_rec => l_delivery_info,
769 x_return_status => l_return_status);
770
771 IF l_debug_on THEN
772 WSH_DEBUG_SV.log(l_module_name,'Return status after calling WSH_DELIVERY_VALIDATIONS.get_delivery_information',l_return_status);
773 END IF;
774
775 IF (l_return_status IN (WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR, WSH_UTIL_CORE.G_RET_STS_ERROR)) THEN
776 IF l_debug_on THEN
777 WSH_DEBUG_SV.logmsg(l_module_name,'Error in WSH_DELIVERY_VALIDATIONS.get_delivery_information');
778 END IF;
779 RAISE api_return_fail;
780 END IF;
781
782 l_sysdate := sysdate;
783
784 --checking the value differences for the critical fields
785 IF ((NVL(l_delivery_info.name, '!@#$%') <> NVL(p_delivery_info.name, '!@#$%'))
786 OR (NVL(l_delivery_info.initial_pickup_location_id, -99) <>
787 NVL(p_delivery_info.initial_pickup_location_id, -99))
788 OR (NVL(l_delivery_info.ultimate_dropoff_location_id, -99) <>
789 NVL(p_delivery_info.ultimate_dropoff_location_id, -99))
790 OR (NVL(l_delivery_info.freight_terms_code, '!@#$%') <>
791 NVL(p_delivery_info.freight_terms_code, '!@#$%'))
792 OR (NVL(l_delivery_info.fob_code, '!@#$%') <> NVL(p_delivery_info.fob_code, '!@#$%'))
793 OR (NVL(l_delivery_info.ship_method_code, '!@#$%') <> NVL(p_delivery_info.ship_method_code, '!@#$%'))
794 OR (NVL(l_delivery_info.carrier_id, -99) <> NVL(p_delivery_info.carrier_id, -99))
795 OR (NVL(l_delivery_info.service_level, '!@#$%') <> NVL(p_delivery_info.service_level, '!@#$%'))
796 OR (NVL(l_delivery_info.mode_of_transport, '!@#$%') <>
797 NVL(p_delivery_info.mode_of_transport, '!@#$%'))
798 OR (NVL(l_delivery_info.earliest_pickup_date, l_sysdate) <>
799 NVL(p_delivery_info.earliest_pickup_date, l_sysdate))
800 OR (NVL(l_delivery_info.latest_pickup_date, l_sysdate) <>
801 NVL(p_delivery_info.latest_pickup_date, l_sysdate))
802 OR (NVL(l_delivery_info.earliest_dropoff_date, l_sysdate) <>
803 NVL(p_delivery_info.earliest_dropoff_date, l_sysdate))
804 OR (NVL(l_delivery_info.latest_dropoff_date, l_sysdate) <>
805 NVL(p_delivery_info.latest_dropoff_date, l_sysdate))) THEN
806
807 IF l_debug_on THEN
808 WSH_DEBUG_SV.log(l_module_name,'p_delivery_info name', p_delivery_info.name);
809 WSH_DEBUG_SV.log(l_module_name,'l_delivery_info name', l_delivery_info.name);
810 WSH_DEBUG_SV.log(l_module_name,'p_delivery_info initial_pickup_location_id', p_delivery_info.initial_pickup_location_id);
811 WSH_DEBUG_SV.log(l_module_name,'l_delivery_info initial_pickup_location_id', l_delivery_info.initial_pickup_location_id);
812 WSH_DEBUG_SV.log(l_module_name,'p_delivery_info ultimate_dropoff_location_id', p_delivery_info.ultimate_dropoff_location_id);
813 WSH_DEBUG_SV.log(l_module_name,'l_delivery_info ultimate_dropoff_location_id', l_delivery_info.ultimate_dropoff_location_id);
814 WSH_DEBUG_SV.log(l_module_name,'p_delivery_info freight_terms_code', p_delivery_info.freight_terms_code);
815 WSH_DEBUG_SV.log(l_module_name,'l_delivery_info freight_terms_code', l_delivery_info.freight_terms_code);
816 WSH_DEBUG_SV.log(l_module_name,'p_delivery_info fob_code', p_delivery_info.fob_code);
817 WSH_DEBUG_SV.log(l_module_name,'l_delivery_info fob_code', l_delivery_info.fob_code);
818 WSH_DEBUG_SV.log(l_module_name,'p_delivery_info ship_method_code', p_delivery_info.ship_method_code);
819 WSH_DEBUG_SV.log(l_module_name,'l_delivery_info ship_method_code', l_delivery_info.ship_method_code);
820 WSH_DEBUG_SV.log(l_module_name,'p_delivery_info carrier_id', p_delivery_info.carrier_id);
821 WSH_DEBUG_SV.log(l_module_name,'l_delivery_info carrier_id', l_delivery_info.carrier_id);
822 WSH_DEBUG_SV.log(l_module_name,'p_delivery_info service_level', p_delivery_info.service_level);
823 WSH_DEBUG_SV.log(l_module_name,'l_delivery_info service_level', l_delivery_info.service_level);
824 WSH_DEBUG_SV.log(l_module_name,'p_delivery_info mode_of_transport', p_delivery_info.mode_of_transport);
825 WSH_DEBUG_SV.log(l_module_name,'l_delivery_info mode_of_transport', l_delivery_info.mode_of_transport);
826 WSH_DEBUG_SV.log(l_module_name,'p_delivery_info earliest_pickup_date', p_delivery_info.earliest_pickup_date);
827 WSH_DEBUG_SV.log(l_module_name,'l_delivery_info earliest_pickup_date', l_delivery_info.earliest_pickup_date);
828 WSH_DEBUG_SV.log(l_module_name,'p_delivery_info latest_pickup_date', p_delivery_info.latest_pickup_date);
829 WSH_DEBUG_SV.log(l_module_name,'l_delivery_info latest_pickup_date', l_delivery_info.latest_pickup_date);
830 WSH_DEBUG_SV.log(l_module_name,'p_delivery_info earliest_dropoff_date', p_delivery_info.earliest_dropoff_date);
831 WSH_DEBUG_SV.log(l_module_name,'l_delivery_info earliest_dropoff_date', l_delivery_info.earliest_dropoff_date);
832 WSH_DEBUG_SV.log(l_module_name,'p_delivery_info latest_dropoff_date', p_delivery_info.latest_dropoff_date);
833 WSH_DEBUG_SV.log(l_module_name,'l_delivery_info latest_dropoff_date', l_delivery_info.latest_dropoff_date);
834 END IF;
835
836 IF (NVL(l_delivery_info.tms_interface_flag,
837 WSH_NEW_DELIVERIES_PVT.C_TMS_NOT_TO_BE_SENT) IN
838 (WSH_NEW_DELIVERIES_PVT.C_TMS_CREATE_IN_PROCESS,
839 WSH_NEW_DELIVERIES_PVT.C_TMS_UPDATE_IN_PROCESS,
840 WSH_NEW_DELIVERIES_PVT.C_TMS_AWAITING_ANSWER,
841 WSH_NEW_DELIVERIES_PVT.C_TMS_ANSWER_RECEIVED)) THEN
842 l_tms_update := 'Y';
843 l_delivery_info_tab(1) := l_delivery_info;
844 l_new_interface_flag_tab(1) := WSH_NEW_DELIVERIES_PVT.C_TMS_UPDATE_REQUIRED;
845 l_tms_version_number := NVL(l_delivery_info.tms_version_number, 1) + 1;
846 ELSE
847 l_tms_update := 'N';
848 END IF;
849 END IF;
850 END IF;
851 END IF;
852 --END OTM R12
853
854 UPDATE wsh_new_deliveries
855 SET
856 delivery_id = p_delivery_info.delivery_id
857 ,name = p_delivery_info.name
858 ,planned_flag = p_delivery_info.planned_flag
859 ,status_code = p_delivery_info.status_code
860 ,delivery_type = p_delivery_info.delivery_type
861 ,loading_sequence = p_delivery_info.loading_sequence
862 ,loading_order_flag = p_delivery_info.loading_order_flag
863 ,initial_pickup_date = p_delivery_info.initial_pickup_date
864 ,initial_pickup_location_id = p_delivery_info.initial_pickup_location_id
865 ,organization_id = p_delivery_info.organization_id
866 ,ultimate_dropoff_location_id = p_delivery_info.ultimate_dropoff_location_id
867 ,ultimate_dropoff_date = p_delivery_info.ultimate_dropoff_date
868 ,customer_id = p_delivery_info.customer_id
869 ,intmed_ship_to_location_id = p_delivery_info.intmed_ship_to_location_id
870 ,pooled_ship_to_location_id = p_delivery_info.pooled_ship_to_location_id
871 ,carrier_id = p_delivery_info.carrier_id
872 ,ship_method_code = p_delivery_info.ship_method_code
873 ,freight_terms_code = p_delivery_info.freight_terms_code
874 ,fob_code = p_delivery_info.fob_code
875 ,fob_location_id = p_delivery_info.fob_location_id
876 ,waybill = p_delivery_info.waybill
877 ,dock_code = p_delivery_info.dock_code
878 ,acceptance_flag = p_delivery_info.acceptance_flag
879 ,accepted_by = p_delivery_info.accepted_by
880 ,accepted_date = p_delivery_info.accepted_date
881 ,acknowledged_by = p_delivery_info.acknowledged_by
882 ,confirmed_by = p_delivery_info.confirmed_by
883 ,confirm_date = p_delivery_info.confirm_date
884 ,asn_date_sent = p_delivery_info.asn_date_sent
885 ,asn_status_code = p_delivery_info.asn_status_code
886 ,asn_seq_number = p_delivery_info.asn_seq_number
887 ,gross_weight = l_gross_weight -- Bugfix #4587421
888 ,net_weight = l_net_weight -- Bugfix #4587421
889 ,weight_uom_code = p_delivery_info.weight_uom_code
890 ,volume = p_delivery_info.volume
891 ,volume_uom_code = p_delivery_info.volume_uom_code
892 ,additional_shipment_info = p_delivery_info.additional_shipment_info
893 ,currency_code = p_delivery_info.currency_code
894 ,attribute_category = p_delivery_info.attribute_category
895 ,attribute1 = p_delivery_info.attribute1
896 ,attribute2 = p_delivery_info.attribute2
897 ,attribute3 = p_delivery_info.attribute3
898 ,attribute4 = p_delivery_info.attribute4
899 ,attribute5 = p_delivery_info.attribute5
900 ,attribute6 = p_delivery_info.attribute6
901 ,attribute7 = p_delivery_info.attribute7
902 ,attribute8 = p_delivery_info.attribute8
903 ,attribute9 = p_delivery_info.attribute9
904 ,attribute10 = p_delivery_info.attribute10
905 ,attribute11 = p_delivery_info.attribute11
906 ,attribute12 = p_delivery_info.attribute12
907 ,attribute13 = p_delivery_info.attribute13
908 ,attribute14 = p_delivery_info.attribute14
909 ,attribute15 = p_delivery_info.attribute15
910 ,tp_attribute_category = p_delivery_info.tp_attribute_category
911 ,tp_attribute1 = p_delivery_info.tp_attribute1
912 ,tp_attribute2 = p_delivery_info.tp_attribute2
913 ,tp_attribute3 = p_delivery_info.tp_attribute3
914 ,tp_attribute4 = p_delivery_info.tp_attribute4
915 ,tp_attribute5 = p_delivery_info.tp_attribute5
916 ,tp_attribute6 = p_delivery_info.tp_attribute6
917 ,tp_attribute7 = p_delivery_info.tp_attribute7
918 ,tp_attribute8 = p_delivery_info.tp_attribute8
919 ,tp_attribute9 = p_delivery_info.tp_attribute9
920 ,tp_attribute10 = p_delivery_info.tp_attribute10
921 ,tp_attribute11 = p_delivery_info.tp_attribute11
922 ,tp_attribute12 = p_delivery_info.tp_attribute12
923 ,tp_attribute13 = p_delivery_info.tp_attribute13
924 ,tp_attribute14 = p_delivery_info.tp_attribute14
925 ,tp_attribute15 = p_delivery_info.tp_attribute15
926 ,global_attribute_category = p_delivery_info.global_attribute_category
927 ,global_attribute1 = p_delivery_info.global_attribute1
928 ,global_attribute2 = p_delivery_info.global_attribute2
929 ,global_attribute3 = p_delivery_info.global_attribute3
930 ,global_attribute4 = p_delivery_info.global_attribute4
931 ,global_attribute5 = p_delivery_info.global_attribute5
932 ,global_attribute6 = p_delivery_info.global_attribute6
933 ,global_attribute7 = p_delivery_info.global_attribute7
934 ,global_attribute8 = p_delivery_info.global_attribute8
935 ,global_attribute9 = p_delivery_info.global_attribute9
936 ,global_attribute10 = p_delivery_info.global_attribute10
937 ,global_attribute11 = p_delivery_info.global_attribute11
938 ,global_attribute12 = p_delivery_info.global_attribute12
939 ,global_attribute13 = p_delivery_info.global_attribute13
940 ,global_attribute14 = p_delivery_info.global_attribute14
941 ,global_attribute15 = p_delivery_info.global_attribute15
942 ,global_attribute16 = p_delivery_info.global_attribute16
943 ,global_attribute17 = p_delivery_info.global_attribute17
944 ,global_attribute18 = p_delivery_info.global_attribute18
945 ,global_attribute19 = p_delivery_info.global_attribute19
946 ,global_attribute20 = p_delivery_info.global_attribute20
947 ,last_update_date = p_delivery_info.last_update_date
948 ,last_updated_by = p_delivery_info.last_updated_by
949 ,last_update_login = p_delivery_info.last_update_login
950 ,program_application_id = p_delivery_info.program_application_id
951 ,program_id = p_delivery_info.program_id
952 ,program_update_date = p_delivery_info.program_update_date
953 ,request_id = p_delivery_info.request_id
954 ,number_of_lpn = p_delivery_info.number_of_lpn
955 /* Changes for the Shipping Data Model Bug#1918342*/
956 ,COD_AMOUNT = p_delivery_info.COD_AMOUNT
957 ,COD_CURRENCY_CODE = p_delivery_info.COD_CURRENCY_CODE
958 ,COD_REMIT_TO = p_delivery_info.COD_REMIT_TO
959 ,COD_CHARGE_PAID_BY = p_delivery_info.COD_CHARGE_PAID_BY
960 ,PROBLEM_CONTACT_REFERENCE = p_delivery_info.PROBLEM_CONTACT_REFERENCE
961 ,PORT_OF_LOADING = p_delivery_info.PORT_OF_LOADING
962 ,PORT_OF_DISCHARGE = p_delivery_info.PORT_OF_DISCHARGE
963 ,FTZ_NUMBER = p_delivery_info.FTZ_NUMBER
964 ,ROUTED_EXPORT_TXN = p_delivery_info.ROUTED_EXPORT_TXN
965 ,ENTRY_NUMBER = p_delivery_info.ENTRY_NUMBER
966 ,ROUTING_INSTRUCTIONS = p_delivery_info.ROUTING_INSTRUCTIONS
967 ,IN_BOND_CODE = p_delivery_info.IN_BOND_CODE
968 ,SHIPPING_MARKS = p_delivery_info.SHIPPING_MARKS
969 /* H Integration: datamodel changes wrudge */
970 ,SERVICE_LEVEL = p_delivery_info.SERVICE_LEVEL
971 ,MODE_OF_TRANSPORT = p_delivery_info.MODE_OF_TRANSPORT
972 ,ASSIGNED_TO_FTE_TRIPS = p_delivery_info.ASSIGNED_TO_FTE_TRIPS
973 ,auto_sc_exclude_flag = p_delivery_info.auto_sc_exclude_flag
974 ,auto_ap_exclude_flag = p_delivery_info.auto_ap_exclude_flag
975 /* J Inbound Logistics new columns jckwok*/
976 ,SHIPMENT_DIRECTION = nvl(p_delivery_info.SHIPMENT_DIRECTION, 'O')
977 ,VENDOR_ID = p_delivery_info.VENDOR_ID
978 ,PARTY_ID = p_delivery_info.PARTY_ID
979 ,ROUTING_RESPONSE_ID = p_delivery_info.ROUTING_RESPONSE_ID
980 ,RCV_SHIPMENT_HEADER_ID = p_delivery_info.RCV_SHIPMENT_HEADER_ID
981 ,ASN_SHIPMENT_HEADER_ID = p_delivery_info.ASN_SHIPMENT_HEADER_ID
982 ,SHIPPING_CONTROL = p_delivery_info.SHIPPING_CONTROL
983 /* J TP Release : ttrichy */
984 ,TP_DELIVERY_NUMBER = p_delivery_info.TP_DELIVERY_NUMBER
985 ,EARLIEST_PICKUP_DATE = p_delivery_info.EARLIEST_PICKUP_DATE
986 ,LATEST_PICKUP_DATE = p_delivery_info.LATEST_PICKUP_DATE
987 ,EARLIEST_DROPOFF_DATE = p_delivery_info.EARLIEST_DROPOFF_DATE
988 ,LATEST_DROPOFF_DATE = p_delivery_info.LATEST_DROPOFF_DATE
989 ,IGNORE_FOR_PLANNING = nvl(p_delivery_info.IGNORE_FOR_PLANNING, 'N')
990 ,TP_PLAN_NAME = p_delivery_info.TP_PLAN_NAME
991 /* J: W/V Changes */
992 ,WV_FROZEN_FLAG = l_frozen_flag
993 ,HASH_VALUE = p_delivery_info.HASH_VALUE
994 ,HASH_STRING = p_delivery_info.HASH_STRING
995 ,delivered_date = p_delivery_info.delivered_date
996 -- bug 3667348
997 ,REASON_OF_TRANSPORT =p_delivery_info.REASON_OF_TRANSPORT
998 ,DESCRIPTION =p_delivery_info.DESCRIPTION
999 -- bug 3667348
1000 --OTM R12
1001 ,TMS_INTERFACE_FLAG = DECODE(l_tms_update,
1002 'Y', l_new_interface_flag_tab(1),
1003 NVL(TMS_INTERFACE_FLAG, WSH_NEW_DELIVERIES_PVT.C_TMS_NOT_TO_BE_SENT))
1004 ,TMS_VERSION_NUMBER = DECODE(l_tms_update,
1005 'Y', l_tms_version_number,
1006 NVL(tms_version_number, 1))
1007 --
1008 WHERE rowid = l_rowid;
1009
1010 IF (SQL%NOTFOUND) THEN
1011 RAISE NO_DATA_FOUND;
1012 END IF;
1013
1014 --
1015 IF l_debug_on THEN
1016 WSH_DEBUG_SV.log(l_module_name,'Rows updated',SQL%ROWCOUNT);
1017 WSH_DEBUG_SV.logmsg(l_module_name,'Org Gross '||l_gross_wt||' New Gross '||p_delivery_info.gross_weight||' Org Net '||l_net_wt||' New Net '||p_delivery_info.net_weight||' Org Vol '||l_volume||' New Vol '||p_delivery_info.volume);
1018
1019 END IF;
1020
1021 --OTM R12
1022 IF (l_gc3_is_installed = 'Y' AND l_tms_update = 'Y') THEN
1023
1024 IF l_debug_on THEN
1025 WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_XC_UTIL.LOG_OTM_EXCEPTION',WSH_DEBUG_SV.C_PROC_LEVEL);
1026 END IF;
1027
1028 WSH_XC_UTIL.log_otm_exception(
1029 p_delivery_info_tab => l_delivery_info_tab,
1030 p_new_interface_flag_tab => l_new_interface_flag_tab,
1031 x_return_status => l_return_status);
1032
1033 IF l_debug_on THEN
1034 WSH_DEBUG_SV.log(l_module_name,'Return status after calling WSH_XC_UTIL.LOG_OTM_EXCEPTION',l_return_status);
1035 END IF;
1036
1037 IF (l_return_status IN (WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR, WSH_UTIL_CORE.G_RET_STS_ERROR)) THEN
1038 IF l_debug_on THEN
1039 WSH_DEBUG_SV.logmsg(l_module_name,'Error in WSH_XC_UTIL.log_otm_exception');
1040 END IF;
1041 RAISE api_return_fail;
1042 END IF;
1043 END IF;
1044 --END OTM R12
1045
1046 /*CURRENTLY NOT IN USE
1047 --WF: CMR
1048 WSH_WF_STD.Get_Carrier(p_del_ids => l_del_ids,
1049 x_del_old_carrier_ids => l_del_new_carrier_ids,
1050 x_return_status => l_wf_rs);
1051 WSH_WF_STD.Assign_Unassign_Carrier(p_delivery_id => p_delivery_info.delivery_id,
1052 p_old_carrier_id => l_del_old_carrier_ids(1),
1053 p_new_carrier_id =>
1054 l_del_new_carrier_ids(1),
1055 x_return_status => l_wf_rs);
1056 */
1057 -- J: W/V Changes
1058 -- If UOM changes then compute the delta (difference between new W/V and old W/V)
1059 IF ( NVL(l_weight_uom_code,'-99') <> NVL(p_delivery_info.weight_uom_code,'-99') AND
1060 l_gross_weight is not null ) THEN -- BugFix #4587421
1061 IF l_debug_on THEN
1062 WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_WV_UTILS.convert_uom',WSH_DEBUG_SV.C_PROC_LEVEL);
1063 END IF;
1064 l_diff_gross_wt := WSH_WV_UTILS.convert_uom(
1065 from_uom => p_delivery_info.weight_uom_code,
1066 to_uom => l_weight_uom_code,
1067 quantity => l_gross_weight) - NVL(l_gross_wt,0); -- BugFix #4587421
1068
1069 IF l_debug_on THEN
1070 WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_WV_UTILS.convert_uom',WSH_DEBUG_SV.C_PROC_LEVEL);
1071 END IF;
1072 l_diff_net_wt := WSH_WV_UTILS.convert_uom(
1073 from_uom => p_delivery_info.weight_uom_code,
1074 to_uom => l_weight_uom_code,
1075 quantity => l_net_weight) - NVL(l_net_wt,0); -- BigFix #4587421
1076 ELSE
1077 l_diff_gross_wt := NVL(l_gross_weight,0) - NVL(l_gross_wt,0); -- BigFix #4587421
1078 l_diff_net_wt := NVL(l_net_weight,0) - NVL(l_net_wt,0); -- BigFix #4587421
1079 END IF;
1080
1081 IF (NVL(l_volume_uom_code,'-99') <> NVL(p_delivery_info.volume_uom_code,'-99') AND
1082 p_delivery_info.volume is not null) THEN
1083 IF l_debug_on THEN
1084 WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_WV_UTILS.convert_uom',WSH_DEBUG_SV.C_PROC_LEVEL);
1085 END IF;
1086 l_diff_vol := WSH_WV_UTILS.convert_uom(
1087 from_uom => p_delivery_info.volume_uom_code,
1088 to_uom => l_volume_uom_code,
1089 quantity => p_delivery_info.volume) - NVL(l_volume,0);
1090 ELSE
1091 l_diff_vol := NVL(p_delivery_info.volume,0) - NVL(l_volume,0);
1092 END IF;
1093
1094 -- Call DEL_WV_Post_Process to adjust W/V on parent entities
1095 IF l_diff_gross_wt is not null OR l_diff_net_wt is not null OR l_diff_vol is not null THEN
1096
1097 --
1098 -- Bug No:4085560 - 11.5.10.1CU:INCORRECT WT SHOWN FOR THE STOP(start)
1099 --
1100 IF (l_weight_uom_code<>p_delivery_info.weight_uom_code) THEN
1101
1102 IF (l_diff_gross_wt IS NOT NULL) THEN
1103 l_diff_gross_wt := WSH_WV_UTILS.convert_uom( from_uom => l_weight_uom_code,
1104 to_uom =>p_delivery_info.weight_uom_code,
1105 quantity => l_diff_gross_wt);
1106 END IF;
1107
1108 IF (l_diff_net_wt IS NOT NULL) THEN
1109 l_diff_net_wt := WSH_WV_UTILS.convert_uom( from_uom => l_weight_uom_code,
1110 to_uom =>p_delivery_info.weight_uom_code,
1111 quantity => l_diff_net_wt);
1112 END IF;
1113 END IF;
1114
1115 IF (l_volume_uom_code<>p_delivery_info.volume_uom_code AND l_diff_vol IS NOT NULL) THEN
1116 l_diff_vol:= WSH_WV_UTILS.convert_uom( from_uom => l_volume_uom_code,
1117 to_uom => p_delivery_info.volume_uom_code,
1118 quantity => l_diff_vol);
1119
1120 END IF;
1121
1122 --
1123 -- Bug No:4085560 - 11.5.10.1CU:INCORRECT WT SHOWN FOR THE STOP(end)
1124 --
1125
1126 IF l_debug_on THEN
1127 WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_WV_UTILS.DEL_WV_Post_Process',WSH_DEBUG_SV.C_PROC_LEVEL);
1128 END IF;
1129
1130 WSH_WV_UTILS.DEL_WV_Post_Process(
1131 p_delivery_id => p_delivery_info.delivery_id,
1132 p_diff_gross_wt => l_diff_gross_wt,
1133 p_diff_net_wt => l_diff_net_wt,
1134 p_diff_volume => l_diff_vol,
1135 x_return_status => l_return_status);
1136
1137 IF l_return_status = WSH_UTIL_CORE.G_RET_STS_ERROR THEN
1138 raise e_wt_vol_fail;
1139 END IF;
1140
1141 END IF;
1142 --
1143
1144 -- "Proration of weight from Delivery to delivery lines" Project(Bug#4254552).
1145 -- Do the Proration only for the 'Standard' Deliveries.
1146
1147 IF NVL(p_delivery_info.delivery_type, 'STANDARD')='STANDARD' AND NVL(p_delivery_info.PRORATE_WT_FLAG,'N')='Y'
1148 AND (nvl(l_diff_gross_wt,0) <> 0 OR nvl(l_diff_net_wt,0) <> 0) THEN --{
1149 --
1150 IF l_debug_on THEN
1151 WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_WV_UTILS.prorate_weight',WSH_DEBUG_SV.C_PROC_LEVEL);
1152 END IF;
1153 --
1154 WSH_WV_UTILS.prorate_weight(
1155 p_entity_type => 'DELIVERY',
1156 p_entity_id => p_delivery_info.delivery_id,
1157 p_old_gross_wt => l_gross_wt,
1158 p_new_gross_wt => l_gross_weight, -- BigFix #4587421
1159 p_old_net_wt => l_net_wt,
1160 p_new_net_wt => l_net_weight, -- BigFix #4587421
1161 p_weight_uom_code => l_weight_uom_code,
1162 x_return_status => l_return_status);
1163 IF l_return_status <> WSH_UTIL_CORE.G_RET_STS_SUCCESS THEN
1164 x_return_status := l_return_status;
1165 --
1166 IF l_debug_on THEN
1167 wsh_debug_sv.log(l_module_name,'Return Status After Calling WSH_WV_UTILS.prorate_weight',l_return_status);
1168 END IF;
1169 --
1170 wsh_util_core.api_post_call(
1171 p_return_status => l_return_status,
1172 x_num_warnings => l_num_warnings,
1173 x_num_errors => l_num_errors);
1174 --
1175 END IF;
1176 END IF; --}
1177
1178 IF l_debug_on THEN
1179 WSH_DEBUG_SV.pop(l_module_name);
1180 END IF;
1181 --
1182
1183 EXCEPTION
1184 --OTM R12
1185 WHEN api_return_fail THEN
1186 x_return_status := l_return_status;
1187 IF l_debug_on THEN
1188 WSH_DEBUG_SV.log(l_module_name, 'x_return_status', x_return_status);
1189 WSH_DEBUG_SV.pop(l_module_name);
1190 END IF;
1191 --END OTM R12
1192 -- J: W/V Changes
1193 WHEN e_wt_vol_fail THEN
1194 FND_MESSAGE.Set_Name('WSH','WSH_DEL_WT_VOL_FAILED');
1195 FND_MESSAGE.Set_Token('DEL_NAME', p_delivery_info.name);
1196 x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
1197 WSH_UTIL_CORE.add_message (x_return_status);
1198 IF l_debug_on THEN
1199 WSH_DEBUG_SV.logmsg(l_module_name,'E_WT_VOL_FAIL exception has occured.',WSH_DEBUG_SV.C_EXCEP_LEVEL);
1200 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:E_WT_VOL_FAIL');
1201 END IF;
1202
1203 WHEN NO_DATA_FOUND THEN
1204 FND_MESSAGE.Set_Name('WSH','WSH_DEL_NOT_FOUND');
1205 x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
1206 WSH_UTIL_CORE.ADD_MESSAGE(x_return_status,l_module_name);
1207 --
1208 IF l_debug_on THEN
1209 WSH_DEBUG_SV.logmsg(l_module_name,'NO_DATA_FOUND exception has occured.',WSH_DEBUG_SV.C_EXCEP_LEVEL);
1210 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:NO_DATA_FOUND');
1211 END IF;
1212 --
1213
1214 --Bug # 3268641
1215 WHEN DUP_VAL_ON_INDEX THEN
1216 FND_MESSAGE.SET_NAME('WSH','WSH_ASSIGN_NEW_DEL');
1217 x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
1218 WSH_UTIL_CORE.ADD_MESSAGE(x_return_status);
1219
1220 IF l_debug_on THEN
1221 WSH_DEBUG_SV.logmsg(l_module_name,'DUP_VAL_ON_INDEX exception has occured.',WSH_DEBUG_SV.C_EXCEP_LEVEL);
1222 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:DUP_VAL_ON_INDEX');
1223 END IF;
1224 --
1225
1226 WHEN others THEN
1227 wsh_util_core.default_handler('WSH_NEW_DELIVERIES_PVT.UPDATE_DELIVERY',l_module_name);
1228 x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR;
1229 IF l_debug_on THEN
1230 WSH_DEBUG_SV.logmsg(l_module_name,'Unexpected error has occured. Oracle error message is '|| SQLERRM,WSH_DEBUG_SV.C_UNEXPEC_ERR_LEVEL);
1231 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:OTHERS');
1232 END IF;
1233 --
1234 END Update_Delivery;
1235
1236
1237 --
1238 -- Procedure: Delete_Delivery
1239 -- Parameters: p_rowid - Rowid for delivery to be Deleted
1240 -- p_delivery_id - Delivery_id of delivery to be deleted
1241 -- x_return_status - Status of procedure call
1242 -- p_validate_flag - calls validate procedure if 'Y'
1243 -- Description: This procedure will delete a delivery.
1244 -- The order in which it looks at the parameters
1245 -- are:
1246 -- - p_rowid
1247 -- - p_delivery_id
1248 --
1249
1250 PROCEDURE Delete_Delivery
1251 (p_rowid IN VARCHAR2 := NULL,
1252 p_delivery_id IN NUMBER := NULL,
1253 x_return_status OUT NOCOPY VARCHAR2,
1254 p_validate_flag IN VARCHAR2 DEFAULT 'Y'
1255 ) IS
1256
1257 CURSOR get_del_id_rowid (v_rowid VARCHAR2) IS
1258 SELECT delivery_id
1259 FROM wsh_new_deliveries
1260 WHERE rowid = v_rowid;
1261
1262 CURSOR get_delivery_legs (v_delivery_id NUMBER) IS
1263 SELECT delivery_leg_id, parent_delivery_leg_id
1264 FROM wsh_delivery_legs
1265 WHERE delivery_id = v_delivery_id;
1266
1267 CURSOR detail_info(v_delivery_id NUMBER) IS
1268 SELECT delivery_detail_id
1269 FROM wsh_delivery_assignments_v
1270 WHERE delivery_id = v_delivery_id
1271 and parent_delivery_detail_id is NULL;
1272
1273
1274
1275
1276 l_delivery_id NUMBER;
1277 l_delivery_leg_id NUMBER;
1278 l_mdc_del_tab WSH_UTIL_CORE.id_tab_type;
1279
1280 cannot_delete_delivery EXCEPTION;
1281 others EXCEPTION;
1282
1283 l_num_warn NUMBER := 0;
1284 l_return_status VARCHAR2(1);
1285
1286 --
1287 l_debug_on BOOLEAN;
1288 --
1289 l_module_name CONSTANT VARCHAR2(100) := 'wsh.plsql.' || G_PKG_NAME || '.' || 'DELETE_DELIVERY';
1290 --
1291 BEGIN
1292
1293 --
1294 --
1295 l_debug_on := WSH_DEBUG_INTERFACE.g_debug;
1296 --
1297 IF l_debug_on IS NULL
1298 THEN
1299 l_debug_on := WSH_DEBUG_SV.is_debug_enabled;
1300 END IF;
1301 --
1302 IF l_debug_on THEN
1303 WSH_DEBUG_SV.push(l_module_name);
1304 --
1305 WSH_DEBUG_SV.log(l_module_name,'P_ROWID',P_ROWID);
1306 WSH_DEBUG_SV.log(l_module_name,'P_DELIVERY_ID',P_DELIVERY_ID);
1307 WSH_DEBUG_SV.log(l_module_name,'P_VALIDATE_FLAG',P_VALIDATE_FLAG);
1308 END IF;
1309 --
1310 x_return_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
1311 g_return_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
1312
1313 l_num_warn := 0;
1314
1315 -- map rowid to a delivery_id
1316 IF p_rowid IS NOT NULL THEN
1317 OPEN get_del_id_rowid(p_rowid);
1318 FETCH get_del_id_rowid INTO l_delivery_id;
1319 CLOSE get_del_id_rowid;
1320 END IF;
1321
1322 IF l_delivery_id IS NULL THEN
1323 l_delivery_id := p_delivery_id;
1324 END IF;
1325
1326 IF l_debug_on THEN
1327 WSH_DEBUG_SV.log(l_module_name,'l_delivery_id',l_delivery_id);
1328 END IF;
1329
1330 IF (p_validate_flag = 'Y') THEN
1331 wsh_delivery_validations.check_delete_delivery(
1332 p_delivery_id => l_delivery_id,
1333 x_return_status => l_return_status);
1334 IF (l_return_status = WSH_UTIL_CORE.G_RET_STS_ERROR) OR (l_return_status = WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR) THEN
1335 RAISE cannot_delete_delivery;
1336 ELSIF l_return_status = WSH_UTIL_CORE.G_RET_STS_WARNING THEN
1337 l_num_warn := l_num_warn + 1;
1338 END IF;
1339 END IF;
1340
1341 SAVEPOINT wsh_before_delivery_delete;
1342
1343 IF l_delivery_id IS NOT NULL THEN --{
1344
1345 -- bug 4416863 detail unassignment is moved before deletion of delivery legs
1346 -- because if deletion of delivery legs is called first, it will break the link between
1347 -- the delivery and trip stop so that w/v is not calculated correctly for the stops.
1348
1349 -- unassign all delivery detials
1350
1351 SAVEPOINT unassign_details;
1352
1353 FOR dt IN detail_info(l_delivery_id) LOOP
1354 wsh_delivery_details_actions.unassign_detail_from_delivery
1355 (p_detail_id => dt.delivery_detail_id,
1356 x_return_status => l_return_status);
1357
1358 IF (l_return_status = WSH_UTIL_CORE.G_RET_STS_ERROR)
1359 OR (l_return_status = WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR) THEN
1360 x_return_status := l_return_status;
1361 ROLLBACK TO SAVEPOINT unassign_details;
1362 FND_MESSAGE.SET_NAME('WSH','WSH_DEL_UNASSIGN_DET_ERROR');
1363 FND_MESSAGE.SET_TOKEN('DEL_NAME',wsh_new_deliveries_pvt.get_name(l_delivery_id));
1364 FND_MESSAGE.SET_TOKEN('DET_NAME',dt.delivery_detail_id);
1365 wsh_util_core.add_message(x_return_status,l_module_name);
1366 --
1367 IF l_debug_on THEN
1368 WSH_DEBUG_SV.log(l_module_name,'x_return_status',
1369 x_return_status);
1370 WSH_DEBUG_SV.pop(l_module_name);
1371 END IF;
1372 --
1373 RETURN;
1374 ELSIF l_return_status = WSH_UTIL_CORE.G_RET_STS_WARNING THEN
1375 l_num_warn := l_num_warn + 1;
1376 END IF;
1377
1378 END LOOP;
1379 -- end bug 4416863
1380
1381 -- delete all Delivery Legs for the delivery
1382
1383 FOR dl IN get_delivery_legs (l_delivery_id) LOOP
1384 IF dl.parent_delivery_leg_id IS NOT NULL THEN
1385 -- MDC: Unassign the delivery from the parent delivery
1386 -- if it exists.
1387 l_mdc_del_tab(1) := l_delivery_id;
1388 WSH_NEW_DELIVERY_ACTIONS.Unassign_Dels_from_Consol_Del(
1389 p_parent_del => NULL,
1390 p_caller => 'WSH_DELETE_DEL',
1391 p_del_tab => l_mdc_del_tab,
1392 x_return_status => l_return_status);
1393 l_mdc_del_tab.delete;
1394 IF (l_return_status = WSH_UTIL_CORE.G_RET_STS_ERROR) OR (l_return_status = WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR) THEN
1395 ROLLBACK TO wsh_before_delivery_delete;
1396 RAISE cannot_delete_delivery;
1397 ELSIF l_return_status = WSH_UTIL_CORE.G_RET_STS_WARNING THEN
1398 l_num_warn := l_num_warn + 1;
1399 END IF;
1400
1401 END IF;
1402
1403 WSH_DELIVERY_LEGS_PVT.Delete_Delivery_Leg(
1404 p_delivery_leg_id => dl.delivery_leg_id,
1405 x_return_status => l_return_status);
1406 IF (l_return_status = WSH_UTIL_CORE.G_RET_STS_ERROR) OR (l_return_status = WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR
1407 ) THEN
1408 ROLLBACK TO wsh_before_delivery_delete;
1409
1410 RAISE cannot_delete_delivery;
1411 ELSIF l_return_status = WSH_UTIL_CORE.G_RET_STS_WARNING THEN
1412 l_num_warn := l_num_warn + 1;
1413 END IF;
1414 END LOOP;
1415
1416 -- delete freight costs associated with delivery (bug 4376236)
1417 DELETE FROM wsh_freight_costs
1418 WHERE delivery_id = l_delivery_id;
1419 IF l_debug_on THEN
1420 WSH_DEBUG_SV.log(l_module_name,
1421 'Freight cost rows deleted', SQL%ROWCOUNT);
1422 END IF;
1423
1424 -- delete the delivery
1425 DELETE FROM wsh_new_deliveries
1426 WHERE delivery_id = l_delivery_id;
1427 --
1428
1429 IF l_debug_on THEN
1430 WSH_DEBUG_SV.log(l_module_name,'Rows deleted',SQL%ROWCOUNT);
1431 END IF;
1432
1433 ELSE
1434 IF l_debug_on THEN
1435 WSH_DEBUG_SV.log(l_module_name,'Raise Others');
1436 END IF;
1437 RAISE others;
1438 END IF; --} l_delivery_id IS NOT NULL
1439 /* Bug 2310456 warning */
1440 IF l_num_warn > 0 THEN
1441 x_return_status := WSH_UTIL_CORE.G_RET_STS_WARNING;
1442 END IF;
1443 --
1444 IF l_debug_on THEN
1445 WSH_DEBUG_SV.log(l_module_name,'x_return_status',x_return_status);
1446 WSH_DEBUG_SV.pop(l_module_name);
1447 END IF;
1448 --
1449 EXCEPTION
1450 WHEN cannot_delete_delivery THEN
1451
1452 IF (get_del_id_rowid%ISOPEN) THEN
1453 CLOSE get_del_id_rowid;
1454 END IF;
1455
1456 IF (get_delivery_legs%ISOPEN) THEN
1457 CLOSE get_delivery_legs;
1458 END IF;
1459
1460 IF (detail_info%ISOPEN) THEN
1461 CLOSE detail_info;
1462 END IF;
1463
1464 FND_MESSAGE.SET_NAME('WSH','WSH_DEL_DELETE_ERROR');
1465 FND_MESSAGE.SET_TOKEN('DEL_NAME',wsh_new_deliveries_pvt.get_name(l_delivery_id));
1466 x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
1467 wsh_util_core.add_message(x_return_status,l_module_name);
1468 --
1469 IF l_debug_on THEN
1470 WSH_DEBUG_SV.logmsg(l_module_name,'CANNOT_DELETE_DELIVERY exception has occured.',WSH_DEBUG_SV.C_EXCEP_LEVEL);
1471 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:CANNOT_DELETE_DELIVERY');
1472 END IF;
1473 --
1474
1475 WHEN others THEN
1476 ROLLBACK TO wsh_before_delivery_delete;
1477
1478 IF (get_del_id_rowid%ISOPEN) THEN
1479 CLOSE get_del_id_rowid;
1480 END IF;
1481
1482 IF (get_delivery_legs%ISOPEN) THEN
1483 CLOSE get_delivery_legs;
1484 END IF;
1485
1486 IF (detail_info%ISOPEN) THEN
1487 CLOSE detail_info;
1488 END IF;
1489
1490 wsh_util_core.default_handler('WSH_NEW_DELIVERIES_PVT.DELETE_DELIVERY',l_module_name);
1491 x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR;
1492 --
1493 IF l_debug_on THEN
1494 WSH_DEBUG_SV.logmsg(l_module_name,'Unexpected error has occured. Oracle error message is '|| SQLERRM,WSH_DEBUG_SV.C_UNEXPEC_ERR_LEVEL);
1495 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:OTHERS');
1496 END IF;
1497 --
1498 END Delete_Delivery;
1499
1500
1501 --
1502 -- Procedure: Lock_Delivery
1503 -- Parameters: p_rowid - Rowid for delivery to be locked
1504 -- p_delivery_info - All Attributes of a Delivery Record
1505 -- x_return_status - Status of procedure call
1506 -- Description: This procedure will lock a delivery record. It is
1507 -- specifically designed for use by the form.
1508 --
1509
1510 PROCEDURE Lock_Delivery
1511 (p_rowid IN VARCHAR2,
1512 p_delivery_info IN Delivery_Rec_Type
1513 ) IS
1514
1515 CURSOR lock_row IS
1516 SELECT *
1517 FROM wsh_new_deliveries
1518 WHERE rowid = p_rowid
1519 FOR UPDATE OF delivery_id NOWAIT;
1520
1521 Recinfo lock_row%ROWTYPE;
1522
1523 --
1524 l_debug_on BOOLEAN;
1525 --
1526 l_module_name CONSTANT VARCHAR2(100) := 'wsh.plsql.' || G_PKG_NAME || '.' || 'LOCK_DELIVERY';
1527 --
1528 BEGIN
1529
1530 --
1531 --
1532 l_debug_on := WSH_DEBUG_INTERFACE.g_debug;
1533 --
1534 IF l_debug_on IS NULL
1535 THEN
1536 l_debug_on := WSH_DEBUG_SV.is_debug_enabled;
1537 END IF;
1538 --
1539 IF l_debug_on THEN
1540 WSH_DEBUG_SV.push(l_module_name);
1541 --
1542 WSH_DEBUG_SV.log(l_module_name,'P_ROWID',P_ROWID);
1543 END IF;
1544 --
1545 OPEN lock_row;
1546 FETCH lock_row INTO Recinfo;
1547
1548 IF (lock_row%NOTFOUND) THEN
1549 CLOSE lock_row;
1550 FND_MESSAGE.Set_Name('FND','FORM_RECORD_DELETED');
1551 IF l_debug_on THEN
1552 WSH_DEBUG_SV.log(l_module_name,'FORM_RECORD_DELETED');
1553 END IF;
1554 app_exception.raise_exception;
1555 END IF;
1556
1557 CLOSE lock_row;
1558
1559 IF (
1560 (Recinfo.delivery_id = p_delivery_info.delivery_id)
1561 AND (Recinfo.name = p_delivery_info.name)
1562 AND (Recinfo.planned_flag = p_delivery_info.planned_flag)
1563 AND (Recinfo.status_code = p_delivery_info.status_code)
1564 AND (Recinfo.delivery_type = p_delivery_info.delivery_type)
1565 AND ( (Recinfo.loading_sequence = p_delivery_info.loading_sequence)
1566 OR ( (Recinfo.loading_sequence IS NULL)
1567 AND (p_delivery_info.loading_sequence IS NULL)))
1568 AND ( (Recinfo.loading_order_flag = p_delivery_info.loading_order_flag)
1569 OR ( (Recinfo.loading_order_flag IS NULL)
1570 AND (p_delivery_info.loading_order_flag IS NULL)))
1571 AND ( (Recinfo.initial_pickup_date = p_delivery_info.initial_pickup_date)
1572 OR ( (Recinfo.initial_pickup_date IS NULL)
1573 AND (p_delivery_info.initial_pickup_date IS NULL)))
1574 AND (Recinfo.initial_pickup_location_id = p_delivery_info.initial_pickup_location_id)
1575 AND ( (Recinfo.organization_id = p_delivery_info.organization_id)
1576 OR ( (Recinfo.organization_id IS NULL)
1577 AND (p_delivery_info.organization_id IS NULL)))
1578 AND (Recinfo.ultimate_dropoff_location_id = p_delivery_info.ultimate_dropoff_location_id)
1579 AND ( (Recinfo.Ultimate_dropoff_date = p_delivery_info.ultimate_dropoff_date)
1580 OR ( (Recinfo.ultimate_dropoff_date IS NULL)
1581 AND (p_delivery_info.ultimate_dropoff_date IS NULL)))
1582 AND ( (Recinfo.customer_id = p_delivery_info.customer_id)
1583 OR ( (Recinfo.customer_id IS NULL)
1584 AND (p_delivery_info.customer_id IS NULL)))
1585 AND ( (Recinfo.intmed_ship_to_location_id = p_delivery_info.intmed_ship_to_location_id)
1586 OR ( (Recinfo.intmed_ship_to_location_id IS NULL)
1587 AND (p_delivery_info.intmed_ship_to_location_id IS NULL)))
1588 AND ( (Recinfo.pooled_ship_to_location_id = p_delivery_info.pooled_ship_to_location_id)
1589 OR ( (Recinfo.pooled_ship_to_location_id IS NULL)
1590 AND (p_delivery_info.pooled_ship_to_location_id IS NULL)))
1591 AND ( (Recinfo.carrier_id = p_delivery_info.carrier_id)
1592 OR ( (Recinfo.carrier_id IS NULL)
1593 AND (p_delivery_info.carrier_id IS NULL)))
1594 AND ( (Recinfo.ship_method_code = p_delivery_info.ship_method_code)
1595 OR ( (Recinfo.ship_method_code IS NULL)
1596 AND (p_delivery_info.ship_method_code IS NULL)))
1597 AND ( (Recinfo.freight_terms_code = p_delivery_info.freight_terms_code)
1598 OR ( (Recinfo.freight_terms_code IS NULL)
1599 AND (p_delivery_info.freight_terms_code IS NULL)))
1600 AND ( (Recinfo.fob_code = p_delivery_info.fob_code)
1601 OR ( (Recinfo.fob_code IS NULL)
1602 AND (p_delivery_info.fob_code IS NULL)))
1603 AND ( (Recinfo.fob_location_id = p_delivery_info.fob_location_id)
1604 OR ( (Recinfo.fob_location_id IS NULL)
1605 AND (p_delivery_info.fob_location_id IS NULL)))
1606 AND ( (recinfo.waybill = p_delivery_info.waybill)
1607 OR ( (recinfo.waybill is NULL)
1608 AND (p_delivery_info.waybill is NULL)))
1609 AND ( (Recinfo.dock_code = p_delivery_info.dock_code)
1610 OR ( (Recinfo.dock_code IS NULL)
1611 AND (p_delivery_info.dock_code IS NULL)))
1612 AND ( (Recinfo.acceptance_flag = p_delivery_info.acceptance_flag)
1613 OR ( (Recinfo.acceptance_flag IS NULL)
1614 AND (p_delivery_info.acceptance_flag IS NULL)))
1615 AND ( (Recinfo.accepted_by = p_delivery_info.accepted_by)
1616 OR ( (Recinfo.accepted_by IS NULL)
1617 AND (p_delivery_info.accepted_by IS NULL)))
1618 AND ( (Recinfo.accepted_date = p_delivery_info.accepted_date)
1619 OR ( (Recinfo.accepted_date IS NULL)
1620 AND (p_delivery_info.accepted_date IS NULL)))
1621 AND ( (recinfo.acknowledged_by = p_delivery_info.acknowledged_by)
1622 OR ( (recinfo.acknowledged_by is NULL)
1623 AND (p_delivery_info.acknowledged_by is NULL)))
1624 AND ( (recinfo.confirmed_by = p_delivery_info.confirmed_by)
1625 OR ( (recinfo.confirmed_by is NULL)
1626 AND (p_delivery_info.confirmed_by is NULL)))
1627 AND ( (recinfo.confirm_date = p_delivery_info.confirm_date)
1628 OR ( (recinfo.confirm_date is NULL)
1629 AND (p_delivery_info.confirm_date is NULL)))
1630 AND ( (recinfo.asn_date_sent = p_delivery_info.asn_date_sent)
1631 OR ( (recinfo.asn_date_sent is NULL)
1632 AND (p_delivery_info.asn_date_sent is NULL)))
1633 AND ( (recinfo.asn_status_code = p_delivery_info.asn_status_code)
1634 OR ( (recinfo.asn_status_code is NULL)
1635 AND (p_delivery_info.asn_status_code is NULL)))
1636 AND ( (recinfo.asn_seq_number = p_delivery_info.asn_seq_number)
1637 OR ( (recinfo.asn_seq_number is NULL)
1638 AND (p_delivery_info.asn_seq_number is NULL)))
1639 AND ( (recinfo.gross_weight = p_delivery_info.gross_weight)
1640 OR ( (recinfo.gross_weight is NULL)
1641 AND (p_delivery_info.gross_weight is NULL)))
1642 AND ( (recinfo.net_weight = p_delivery_info.net_weight)
1643 OR ( (recinfo.net_weight is NULL)
1644 AND (p_delivery_info.net_weight is NULL)))
1645 AND ( (recinfo.weight_uom_code = p_delivery_info.weight_uom_code)
1646 OR ( (recinfo.weight_uom_code is NULL)
1647 AND (p_delivery_info.weight_uom_code is NULL)))
1648 AND ( (recinfo.volume = p_delivery_info.volume)
1649 OR ( (recinfo.volume is NULL)
1650 AND (p_delivery_info.volume is NULL)))
1651 AND ( (recinfo.volume_uom_code = p_delivery_info.volume_uom_code)
1652 OR ( (recinfo.volume_uom_code is NULL)
1653 AND (p_delivery_info.volume_uom_code is NULL)))
1654 AND ( (recinfo.additional_shipment_info = p_delivery_info.additional_shipment_info)
1655 OR ( (recinfo.additional_shipment_info is NULL)
1656 AND (p_delivery_info.additional_shipment_info is NULL)))
1657 AND ( (recinfo.currency_code = p_delivery_info.currency_code)
1658 OR ( (recinfo.currency_code is NULL)
1659 AND (p_delivery_info.currency_code is NULL)))
1660 AND ( (recinfo.attribute_category = p_delivery_info.attribute_category)
1661 OR ( (recinfo.attribute_category is NULL)
1662 AND (p_delivery_info.attribute_category is NULL)))
1663 AND ( (recinfo.attribute1 = p_delivery_info.attribute1)
1664 OR ( (recinfo.attribute1 is NULL)
1665 AND (p_delivery_info.attribute1 is NULL)))
1666 AND ( (recinfo.attribute2 = p_delivery_info.attribute2)
1667 OR ( (recinfo.attribute2 is NULL)
1668 AND (p_delivery_info.attribute2 is NULL)))
1669 AND ( (recinfo.attribute3 = p_delivery_info.attribute3)
1670 OR ( (recinfo.attribute3 is NULL)
1671 AND (p_delivery_info.attribute3 is NULL)))
1672 AND ( (recinfo.attribute4 = p_delivery_info.attribute4)
1673 OR ( (recinfo.attribute4 is NULL)
1674 AND (p_delivery_info.attribute4 is NULL)))
1675 AND ( (recinfo.attribute5 = p_delivery_info.attribute5)
1676 OR ( (recinfo.attribute5 is NULL)
1677 AND (p_delivery_info.attribute5 is NULL)))
1678 AND ( (recinfo.attribute6 = p_delivery_info.attribute6)
1679 OR ( (recinfo.attribute6 is NULL)
1680 AND (p_delivery_info.attribute6 is NULL)))
1681 AND ( (recinfo.attribute7 = p_delivery_info.attribute7)
1682 OR ( (recinfo.attribute7 is NULL)
1683 AND (p_delivery_info.attribute7 is NULL)))
1684 AND ( (recinfo.attribute8 = p_delivery_info.attribute8)
1685 OR ( (recinfo.attribute8 is NULL)
1686 AND (p_delivery_info.attribute8 is NULL)))
1687 AND ( (recinfo.attribute9 = p_delivery_info.attribute9)
1688 OR ( (recinfo.attribute9 is NULL)
1689 AND (p_delivery_info.attribute9 is NULL)))
1690 AND ( (recinfo.attribute10 = p_delivery_info.attribute10)
1691 OR ( (recinfo.attribute10 is NULL)
1692 AND (p_delivery_info.attribute10 is NULL)))
1693 AND ( (recinfo.attribute11 = p_delivery_info.attribute11)
1694 OR ( (recinfo.attribute11 is NULL)
1695 AND (p_delivery_info.attribute11 is NULL)))
1696 AND ( (recinfo.attribute12 = p_delivery_info.attribute12)
1697 OR ( (recinfo.attribute12 is NULL)
1698 AND (p_delivery_info.attribute12 is NULL)))
1699 AND ( (recinfo.attribute13 = p_delivery_info.attribute13)
1700 OR ( (recinfo.attribute13 is NULL)
1701 AND (p_delivery_info.attribute13 is NULL)))
1702 AND ( (recinfo.attribute14 = p_delivery_info.attribute14)
1703 OR ( (recinfo.attribute14 is NULL)
1704 AND (p_delivery_info.attribute14 is NULL)))
1705 AND ( (recinfo.attribute15 = p_delivery_info.attribute15)
1706 OR ( (recinfo.attribute15 is NULL)
1707 AND (p_delivery_info.attribute15 is NULL)))
1708 AND ( (recinfo.tp_attribute_category = p_delivery_info.tp_attribute_category)
1709 OR ( (recinfo.tp_attribute_category is NULL)
1710 AND (p_delivery_info.tp_attribute_category is NULL)))
1711 AND ( (recinfo.tp_attribute1 = p_delivery_info.tp_attribute1)
1712 OR ( (recinfo.tp_attribute1 is NULL)
1713 AND (p_delivery_info.tp_attribute1 is NULL)))
1714 AND ( (recinfo.tp_attribute2 = p_delivery_info.tp_attribute2)
1715 OR ( (recinfo.tp_attribute2 is NULL)
1716 AND (p_delivery_info.tp_attribute2 is NULL)))
1717 AND ( (recinfo.tp_attribute3 = p_delivery_info.tp_attribute3)
1718 OR ( (recinfo.tp_attribute3 is NULL)
1719 AND (p_delivery_info.tp_attribute3 is NULL)))
1720 AND ( (recinfo.tp_attribute4 = p_delivery_info.tp_attribute4)
1721 OR ( (recinfo.tp_attribute4 is NULL)
1722 AND (p_delivery_info.tp_attribute4 is NULL)))
1723 AND ( (recinfo.tp_attribute5 = p_delivery_info.tp_attribute5)
1724 OR ( (recinfo.tp_attribute5 is NULL)
1725 AND (p_delivery_info.tp_attribute5 is NULL)))
1726 AND ( (recinfo.tp_attribute6 = p_delivery_info.tp_attribute6)
1727 OR ( (recinfo.tp_attribute6 is NULL)
1728 AND (p_delivery_info.tp_attribute6 is NULL)))
1729 AND ( (recinfo.tp_attribute7 = p_delivery_info.tp_attribute7)
1730 OR ( (recinfo.tp_attribute7 is NULL)
1731 AND (p_delivery_info.tp_attribute7 is NULL)))
1732 AND ( (recinfo.tp_attribute8 = p_delivery_info.tp_attribute8)
1733 OR ( (recinfo.tp_attribute8 is NULL)
1734 AND (p_delivery_info.tp_attribute8 is NULL)))
1735 AND ( (recinfo.tp_attribute9 = p_delivery_info.tp_attribute9)
1736 OR ( (recinfo.tp_attribute9 is NULL)
1737 AND (p_delivery_info.tp_attribute9 is NULL)))
1738 AND ( (recinfo.tp_attribute10 = p_delivery_info.tp_attribute10)
1739 OR ( (recinfo.tp_attribute10 is NULL)
1740 AND (p_delivery_info.tp_attribute10 is NULL)))
1741 AND ( (recinfo.tp_attribute11 = p_delivery_info.tp_attribute11)
1742 OR ( (recinfo.tp_attribute11 is NULL)
1743 AND (p_delivery_info.tp_attribute11 is NULL)))
1744 AND ( (recinfo.tp_attribute12 = p_delivery_info.tp_attribute12)
1745 OR ( (recinfo.tp_attribute12 is NULL)
1746 AND (p_delivery_info.tp_attribute12 is NULL)))
1747 AND ( (recinfo.tp_attribute13 = p_delivery_info.tp_attribute13)
1748 OR ( (recinfo.tp_attribute13 is NULL)
1749 AND (p_delivery_info.tp_attribute13 is NULL)))
1750 AND ( (recinfo.tp_attribute14 = p_delivery_info.tp_attribute14)
1751 OR ( (recinfo.tp_attribute14 is NULL)
1752 AND (p_delivery_info.tp_attribute14 is NULL)))
1753 AND ( (recinfo.tp_attribute15 = p_delivery_info.tp_attribute15)
1754 OR ( (recinfo.tp_attribute15 is NULL)
1755 AND (p_delivery_info.tp_attribute15 is NULL)))
1756 AND ( (recinfo.global_attribute_category = p_delivery_info.global_attribute_category)
1757 OR ( (recinfo.global_attribute_category is NULL)
1758 AND (p_delivery_info.global_attribute_category is NULL)))
1759 AND ( (recinfo.global_attribute1 = p_delivery_info.global_attribute1)
1760 OR ( (recinfo.global_attribute1 is NULL)
1761 AND (p_delivery_info.global_attribute1 is NULL)))
1762 AND ( (recinfo.global_attribute2 = p_delivery_info.global_attribute2)
1763 OR ( (recinfo.global_attribute2 is NULL)
1764 AND (p_delivery_info.global_attribute2 is NULL)))
1765 AND ( (recinfo.global_attribute3 = p_delivery_info.global_attribute3)
1766 OR ( (recinfo.global_attribute3 is NULL)
1767 AND (p_delivery_info.global_attribute3 is NULL)))
1768 AND ( (recinfo.global_attribute4 = p_delivery_info.global_attribute4)
1769 OR ( (recinfo.global_attribute4 is NULL)
1770 AND (p_delivery_info.global_attribute4 is NULL)))
1771 AND ( (recinfo.global_attribute5 = p_delivery_info.global_attribute5)
1772 OR ( (recinfo.global_attribute5 is NULL)
1773 AND (p_delivery_info.global_attribute5 is NULL)))
1774 AND ( (recinfo.global_attribute6 = p_delivery_info.global_attribute6)
1775 OR ( (recinfo.global_attribute6 is NULL)
1776 AND (p_delivery_info.global_attribute6 is NULL)))
1777 AND ( (recinfo.global_attribute7 = p_delivery_info.global_attribute7)
1778 OR ( (recinfo.global_attribute7 is NULL)
1779 AND (p_delivery_info.global_attribute7 is NULL)))
1780 AND ( (recinfo.global_attribute8 = p_delivery_info.global_attribute8)
1781 OR ( (recinfo.global_attribute8 is NULL)
1782 AND (p_delivery_info.global_attribute8 is NULL)))
1783 AND ( (recinfo.global_attribute9 = p_delivery_info.global_attribute9)
1784 OR ( (recinfo.global_attribute9 is NULL)
1785 AND (p_delivery_info.global_attribute9 is NULL)))
1786 AND ( (recinfo.global_attribute10 = p_delivery_info.global_attribute10)
1787 OR ( (recinfo.global_attribute10 is NULL)
1788 AND (p_delivery_info.global_attribute10 is NULL)))
1789 AND ( (recinfo.global_attribute11 = p_delivery_info.global_attribute11)
1790 OR ( (recinfo.global_attribute11 is NULL)
1791 AND (p_delivery_info.global_attribute11 is NULL)))
1792 AND ( (recinfo.global_attribute12 = p_delivery_info.global_attribute12)
1793 OR ( (recinfo.global_attribute12 is NULL)
1794 AND (p_delivery_info.global_attribute12 is NULL)))
1795 AND ( (recinfo.global_attribute13 = p_delivery_info.global_attribute13)
1796 OR ( (recinfo.global_attribute13 is NULL)
1797 AND (p_delivery_info.global_attribute13 is NULL)))
1798 AND ( (recinfo.global_attribute14 = p_delivery_info.global_attribute14)
1799 OR ( (recinfo.global_attribute14 is NULL)
1800 AND (p_delivery_info.global_attribute14 is NULL)))
1801 AND ( (recinfo.global_attribute15 = p_delivery_info.global_attribute15)
1802 OR ( (recinfo.global_attribute15 is NULL)
1803 AND (p_delivery_info.global_attribute15 is NULL)))
1804 AND ( (recinfo.global_attribute16 = p_delivery_info.global_attribute16)
1805 OR ( (recinfo.global_attribute16 is NULL)
1806 AND (p_delivery_info.global_attribute16 is NULL)))
1807 AND ( (recinfo.global_attribute17 = p_delivery_info.global_attribute17)
1808 OR ( (recinfo.global_attribute17 is NULL)
1809 AND (p_delivery_info.global_attribute17 is NULL)))
1810 AND ( (recinfo.global_attribute18 = p_delivery_info.global_attribute18)
1811 OR ( (recinfo.global_attribute18 is NULL)
1812 AND (p_delivery_info.global_attribute18 is NULL)))
1813 AND ( (recinfo.global_attribute19 = p_delivery_info.global_attribute19)
1814 OR ( (recinfo.global_attribute19 is NULL)
1815 AND (p_delivery_info.global_attribute19 is NULL)))
1816 AND ( (recinfo.global_attribute20 = p_delivery_info.global_attribute20)
1817 OR ( (recinfo.global_attribute20 is NULL)
1818 AND (p_delivery_info.global_attribute20 is NULL)))
1819 AND (recinfo.creation_date = p_delivery_info.creation_date)
1820 AND (recinfo.created_by = p_delivery_info.created_by)
1821 /* Bug 1990178 : Commenting out these three conditions because
1822 the last updated date gets updated in the database , but not
1823 in the form.
1824 AND (recinfo.last_update_date = p_delivery_info.last_update_date)
1825 AND (recinfo.last_updated_by = p_delivery_info.last_updated_by)
1826 AND ( (recinfo.last_update_login = p_delivery_info.last_update_login)
1827 OR ( (recinfo.last_update_login is NULL)
1828 AND (p_delivery_info.last_update_login is NULL)))
1829 */
1830 AND ( (recinfo.program_application_id = p_delivery_info.program_application_id)
1831 OR ( (recinfo.program_application_id is NULL)
1832 AND (p_delivery_info.program_application_id is NULL)))
1833 AND ( (recinfo.program_id = p_delivery_info.program_id)
1834 OR ( (recinfo.program_id is NULL)
1835 AND (p_delivery_info.program_id is NULL)))
1836 AND ( (recinfo.program_update_date = p_delivery_info.program_update_date)
1837 OR ( (recinfo.program_update_date is NULL)
1838 AND (p_delivery_info.program_update_date is NULL)))
1839 AND ( (recinfo.request_id = p_delivery_info.request_id)
1840 OR ( (recinfo.request_id is NULL)
1841 AND (p_delivery_info.request_id is NULL)))
1842 AND ( (recinfo.number_of_lpn = p_delivery_info.number_of_lpn) --bugfix 1426086: added number_of_lpn
1843 OR ( (recinfo.number_of_lpn is NULL)
1844 AND (p_delivery_info.number_of_lpn is NULL)))
1845 /* Changes for the shipping data model bug#1918342*/
1846 AND ( (recinfo.cod_amount= p_delivery_info.cod_amount)
1847 OR ( (recinfo.cod_amount is NULL)
1848 AND (p_delivery_info.cod_amount is NULL)))
1849 AND ( (recinfo.cod_currency_code = p_delivery_info.cod_currency_code)
1850 OR ( (recinfo.cod_currency_code is NULL)
1851 AND (p_delivery_info.cod_currency_code is NULL)))
1852 AND ( (recinfo.cod_remit_to = p_delivery_info.cod_remit_to)
1853 OR ( (recinfo.cod_remit_to is NULL)
1854 AND (p_delivery_info.cod_remit_to is NULL)))
1855 AND ( (recinfo.cod_charge_paid_by = p_delivery_info.cod_charge_paid_by)
1856 OR ( (recinfo.cod_charge_paid_by is NULL)
1857 AND (p_delivery_info.cod_charge_paid_by is NULL)))
1858 AND ( (recinfo.problem_contact_reference = p_delivery_info.problem_contact_reference)
1859 OR ( (recinfo.problem_contact_reference is NULL)
1860 AND (p_delivery_info.problem_contact_reference is NULL)))
1861 AND ( (recinfo.port_of_loading = p_delivery_info.port_of_loading)
1862 OR ( (recinfo.port_of_loading is NULL)
1863 AND (p_delivery_info.port_of_loading is NULL)))
1864 AND ( (recinfo.port_of_discharge = p_delivery_info.port_of_discharge)
1865 OR ( (recinfo.port_of_discharge is NULL)
1866 AND (p_delivery_info.port_of_discharge is NULL)))
1867 AND ( (recinfo.ftz_number = p_delivery_info.ftz_number)
1868 OR ( (recinfo.ftz_number is NULL)
1869 AND (p_delivery_info.ftz_number is NULL)))
1870 AND ( (recinfo.routed_export_txn = p_delivery_info.routed_export_txn)
1871 OR ( (recinfo.routed_export_txn is NULL)
1872 AND (p_delivery_info.routed_export_txn is NULL)))
1873 AND ( (recinfo.entry_number = p_delivery_info.entry_number)
1874 OR ( (recinfo.entry_number is NULL)
1875 AND (p_delivery_info.entry_number is NULL)))
1876 AND ( (recinfo.routing_instructions = p_delivery_info.routing_instructions)
1877 OR ( (recinfo.routing_instructions is NULL)
1878 AND (p_delivery_info.routing_instructions is NULL)))
1879 AND ( (recinfo.in_bond_code = p_delivery_info.in_bond_code)
1880 OR ( (recinfo.in_bond_code is NULL)
1881 AND (p_delivery_info.in_bond_code is NULL)))
1882 AND ( (recinfo.shipping_marks = p_delivery_info.shipping_marks)
1883 OR ( (recinfo.shipping_marks is NULL)
1884 AND (p_delivery_info.shipping_marks is NULL)))
1885 /* H Integration: datamodel changes wrudge */
1886 AND ( (recinfo.service_level = p_delivery_info.service_level)
1887 OR ( (recinfo.service_level is NULL)
1888 AND (p_delivery_info.service_level is NULL)))
1889 AND ( (recinfo.mode_of_transport = p_delivery_info.mode_of_transport)
1890 OR ( (recinfo.mode_of_transport is NULL)
1891 AND (p_delivery_info.mode_of_transport is NULL)))
1892 AND ( (recinfo.assigned_to_fte_trips = p_delivery_info.assigned_to_fte_trips)
1893 OR ( (recinfo.assigned_to_fte_trips is NULL)
1894 AND (p_delivery_info.assigned_to_fte_trips is NULL)))
1895 AND ( (recinfo.auto_sc_exclude_flag = p_delivery_info.auto_sc_exclude_flag)
1896 OR ( (recinfo.auto_sc_exclude_flag is NULL)
1897 AND (p_delivery_info.auto_sc_exclude_flag is NULL)))
1898 AND ( (recinfo.auto_ap_exclude_flag = p_delivery_info.auto_ap_exclude_flag)
1899 OR ( (recinfo.auto_ap_exclude_flag is NULL)
1900 AND (p_delivery_info.auto_ap_exclude_flag is NULL)))
1901 AND ( (nvl(recinfo.shipment_direction, 'O') = nvl(p_delivery_info.shipment_direction,'O'))
1902 OR ( (recinfo.shipment_direction is NULL)
1903 AND (p_delivery_info.shipment_direction is NULL)))
1904 AND ( (recinfo.vendor_id = p_delivery_info.vendor_id)
1905 OR ( (recinfo.vendor_id is NULL)
1906 AND (p_delivery_info.vendor_id is NULL)))
1907 AND ( (recinfo.party_id = p_delivery_info.party_id)
1908 OR ( (recinfo.party_id is NULL)
1909 AND (p_delivery_info.party_id is NULL)))
1910 AND ( (recinfo.routing_response_id = p_delivery_info.routing_response_id)
1911 OR ( (recinfo.routing_response_id is NULL)
1912 AND (p_delivery_info.routing_response_id is NULL)))
1913 AND ( (recinfo.rcv_shipment_header_id = p_delivery_info.rcv_shipment_header_id)
1914 OR ( (recinfo.rcv_shipment_header_id is NULL)
1915 AND (p_delivery_info.rcv_shipment_header_id is NULL)))
1916 AND ( (recinfo.asn_shipment_header_id = p_delivery_info.asn_shipment_header_id)
1917 OR ( (recinfo.asn_shipment_header_id is NULL)
1918 AND (p_delivery_info.asn_shipment_header_id is NULL)))
1919 AND ( (recinfo.shipping_control = p_delivery_info.shipping_control)
1920 OR ( (recinfo.shipping_control is NULL)
1921 AND (p_delivery_info.shipping_control is NULL)))
1922 /* J TP Release : ttrichy */
1923 AND ( (recinfo.TP_DELIVERY_NUMBER = p_delivery_info.TP_DELIVERY_NUMBER)
1924 OR ( (recinfo.TP_DELIVERY_NUMBER is NULL)
1925 AND (p_delivery_info.TP_DELIVERY_NUMBER is NULL)))
1926 AND ( (recinfo.EARLIEST_PICKUP_DATE = p_delivery_info.EARLIEST_PICKUP_DATE)
1927 OR ( (recinfo.EARLIEST_PICKUP_DATE is NULL)
1928 AND (p_delivery_info.EARLIEST_PICKUP_DATE is NULL)))
1929 AND ( (recinfo.LATEST_PICKUP_DATE = p_delivery_info.LATEST_PICKUP_DATE)
1930 OR ( (recinfo.LATEST_PICKUP_DATE is NULL)
1931 AND (p_delivery_info.LATEST_PICKUP_DATE is NULL)))
1932 AND ( (recinfo.EARLIEST_DROPOFF_DATE = p_delivery_info.EARLIEST_DROPOFF_DATE)
1933 OR ( (recinfo.EARLIEST_DROPOFF_DATE is NULL)
1934 AND (p_delivery_info.EARLIEST_DROPOFF_DATE is NULL)))
1935 AND ( (recinfo.LATEST_DROPOFF_DATE = p_delivery_info.LATEST_DROPOFF_DATE)
1936 OR ( (recinfo.LATEST_DROPOFF_DATE is NULL)
1937 AND (p_delivery_info.LATEST_DROPOFF_DATE is NULL)))
1938 AND ( (nvl(recinfo.IGNORE_FOR_PLANNING, 'N') = nvl(p_delivery_info.IGNORE_FOR_PLANNING, 'N')))
1939 AND ( (recinfo.TP_PLAN_NAME = p_delivery_info.TP_PLAN_NAME)
1940 OR ( (recinfo.TP_PLAN_NAME is NULL)
1941 AND (p_delivery_info.TP_PLAN_NAME is NULL)))
1942 -- J: W/V Changes
1943 AND ( (recinfo.wv_frozen_flag = p_delivery_info.wv_frozen_flag)
1944 OR ( (recinfo.wv_frozen_flag is NULL)
1945 AND (p_delivery_info.wv_frozen_flag is NULL)))
1946 AND ( (recinfo.delivered_date = p_delivery_info.delivered_date)
1947 OR ( (recinfo.delivered_date is NULL)
1948 AND (p_delivery_info.delivered_date is NULL)))
1949 -- bug 3667348
1950 AND ( (recinfo.REASON_OF_TRANSPORT = p_delivery_info.REASON_OF_TRANSPORT)
1951 OR ( (recinfo.REASON_OF_TRANSPORT is NULL)
1952 AND (p_delivery_info.REASON_OF_TRANSPORT is NULL)))
1953 AND ( (recinfo.DESCRIPTION = p_delivery_info.DESCRIPTION)
1954 OR ( (recinfo.DESCRIPTION is NULL)
1955 AND (p_delivery_info.DESCRIPTION is NULL)))
1956 -- bug 3667348
1957 --OTM R12
1958 AND ( (recinfo.TMS_INTERFACE_FLAG = p_delivery_info.TMS_INTERFACE_FLAG)
1959 OR ( (recinfo.TMS_INTERFACE_FLAG IS NULL)
1960 AND (p_delivery_info.TMS_INTERFACE_FLAG IS NULL)))
1961 AND ( (recinfo.TMS_VERSION_NUMBER = p_delivery_info.TMS_VERSION_NUMBER)
1962 OR ( (recinfo.TMS_VERSION_NUMBER IS NULL)
1963 AND (p_delivery_info.TMS_VERSION_NUMBER IS NULL)))
1964 --
1965 ) THEN
1966 --
1967 IF l_debug_on THEN
1968 WSH_DEBUG_SV.log(l_module_name,'Nothing has changed');
1969 WSH_DEBUG_SV.pop(l_module_name);
1970 END IF;
1971 --
1972 RETURN;
1973 ELSE
1974 FND_MESSAGE.Set_Name('FND', 'FORM_RECORD_CHANGED');
1975 IF l_debug_on THEN
1976 WSH_DEBUG_SV.log(l_module_name,'FORM_RECORD_CHANGED');
1977 END IF;
1978 app_exception.raise_exception;
1979 END IF;
1980 --
1981 IF l_debug_on THEN
1982 WSH_DEBUG_SV.pop(l_module_name);
1983 END IF;
1984 --
1985 EXCEPTION
1986 WHEN app_exception.application_exception or app_exception.record_lock_exception THEN
1987 if (lock_row%ISOPEN) then
1988 close lock_row;
1989 end if;
1990 --
1991 IF l_debug_on THEN
1992 WSH_DEBUG_SV.logmsg(l_module_name,'APP_EXCEPTION.APPLICATION_EXCEPTION exception has occured.',WSH_DEBUG_SV.C_EXCEP_LEVEL);
1993 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:APP_EXCEPTION.APPLICATION_EXCEPTION');
1994 END IF;
1995 --
1996 RAISE;
1997 --
1998 WHEN others THEN
1999 if (lock_row%ISOPEN) then
2000 close lock_row;
2001 end if;
2002 --
2003 IF l_debug_on THEN
2004 WSH_DEBUG_SV.logmsg(l_module_name,'Unexpected error has occured. Oracle error message is '|| SQLERRM,WSH_DEBUG_SV.C_UNEXPEC_ERR_LEVEL);
2005 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:OTHERS');
2006 END IF;
2007 raise;
2008 --
2009 END Lock_Delivery;
2010
2011 --
2012 -- Procedure: Populate_Record
2013 -- Parameters: p_delivery_id - Id for delivery
2014 -- x_delivery_info - All Attributes of a Delivery Record
2015 -- x_return_status - Status of procedure call
2016 -- Description: This procedure will populate a delivery record.
2017 --
2018
2019 PROCEDURE Populate_Record
2020 (p_delivery_id IN VARCHAR2,
2021 x_delivery_info OUT NOCOPY Delivery_Rec_Type,
2022 x_return_status OUT NOCOPY VARCHAR2
2023 ) IS
2024
2025 CURSOR delivery_record IS
2026 SELECT
2027 DELIVERY_ID,
2028 NAME,
2029 PLANNED_FLAG,
2030 STATUS_CODE,
2031 DELIVERY_TYPE,
2032 LOADING_SEQUENCE,
2033 LOADING_ORDER_FLAG,
2034 INITIAL_PICKUP_DATE,
2035 INITIAL_PICKUP_LOCATION_ID,
2036 ORGANIZATION_ID,
2037 ULTIMATE_DROPOFF_LOCATION_ID,
2038 ULTIMATE_DROPOFF_DATE,
2039 CUSTOMER_ID,
2040 INTMED_SHIP_TO_LOCATION_ID,
2041 POOLED_SHIP_TO_LOCATION_ID,
2042 CARRIER_ID,
2043 SHIP_METHOD_CODE,
2044 FREIGHT_TERMS_CODE,
2045 FOB_CODE,
2046 FOB_LOCATION_ID,
2047 WAYBILL,
2048 DOCK_CODE,
2049 ACCEPTANCE_FLAG,
2050 ACCEPTED_BY,
2051 ACCEPTED_DATE,
2052 ACKNOWLEDGED_BY,
2053 CONFIRMED_BY,
2054 CONFIRM_DATE,
2055 ASN_DATE_SENT,
2056 ASN_STATUS_CODE,
2057 ASN_SEQ_NUMBER,
2058 GROSS_WEIGHT,
2059 NET_WEIGHT,
2060 WEIGHT_UOM_CODE,
2061 VOLUME,
2062 VOLUME_UOM_CODE,
2063 ADDITIONAL_SHIPMENT_INFO,
2064 CURRENCY_CODE,
2065 ATTRIBUTE_CATEGORY,
2066 ATTRIBUTE1,
2067 ATTRIBUTE2,
2068 ATTRIBUTE3,
2069 ATTRIBUTE4,
2070 ATTRIBUTE5,
2071 ATTRIBUTE6,
2072 ATTRIBUTE7,
2073 ATTRIBUTE8,
2074 ATTRIBUTE9,
2075 ATTRIBUTE10,
2076 ATTRIBUTE11,
2077 ATTRIBUTE12,
2078 ATTRIBUTE13,
2079 ATTRIBUTE14,
2080 ATTRIBUTE15,
2081 TP_ATTRIBUTE_CATEGORY,
2082 TP_ATTRIBUTE1,
2083 TP_ATTRIBUTE2,
2084 TP_ATTRIBUTE3,
2085 TP_ATTRIBUTE4,
2086 TP_ATTRIBUTE5,
2087 TP_ATTRIBUTE6,
2088 TP_ATTRIBUTE7,
2089 TP_ATTRIBUTE8,
2090 TP_ATTRIBUTE9,
2091 TP_ATTRIBUTE10,
2092 TP_ATTRIBUTE11,
2093 TP_ATTRIBUTE12,
2094 TP_ATTRIBUTE13,
2095 TP_ATTRIBUTE14,
2096 TP_ATTRIBUTE15,
2097 GLOBAL_ATTRIBUTE_CATEGORY,
2098 GLOBAL_ATTRIBUTE1,
2099 GLOBAL_ATTRIBUTE2,
2100 GLOBAL_ATTRIBUTE3,
2101 GLOBAL_ATTRIBUTE4,
2102 GLOBAL_ATTRIBUTE5,
2103 GLOBAL_ATTRIBUTE6,
2104 GLOBAL_ATTRIBUTE7,
2105 GLOBAL_ATTRIBUTE8,
2106 GLOBAL_ATTRIBUTE9,
2107 GLOBAL_ATTRIBUTE10,
2108 GLOBAL_ATTRIBUTE11,
2109 GLOBAL_ATTRIBUTE12,
2110 GLOBAL_ATTRIBUTE13,
2111 GLOBAL_ATTRIBUTE14,
2112 GLOBAL_ATTRIBUTE15,
2113 GLOBAL_ATTRIBUTE16,
2114 GLOBAL_ATTRIBUTE17,
2115 GLOBAL_ATTRIBUTE18,
2116 GLOBAL_ATTRIBUTE19,
2117 GLOBAL_ATTRIBUTE20,
2118 CREATION_DATE,
2119 CREATED_BY,
2120 LAST_UPDATE_DATE,
2121 LAST_UPDATED_BY,
2122 LAST_UPDATE_LOGIN,
2123 PROGRAM_APPLICATION_ID,
2124 PROGRAM_ID,
2125 PROGRAM_UPDATE_DATE,
2126 REQUEST_ID,
2127 BATCH_ID,
2128 HASH_VALUE,
2129 SOURCE_HEADER_ID,
2130 NUMBER_OF_LPN,
2131 /* Changes for the Shipping Data Model Bug#1918342*/
2132 COD_AMOUNT,
2133 COD_CURRENCY_CODE,
2134 COD_REMIT_TO,
2135 COD_CHARGE_PAID_BY,
2136 PROBLEM_CONTACT_REFERENCE,
2137 PORT_OF_LOADING,
2138 PORT_OF_DISCHARGE,
2139 FTZ_NUMBER,
2140 ROUTED_EXPORT_TXN,
2141 ENTRY_NUMBER,
2142 ROUTING_INSTRUCTIONS,
2143 IN_BOND_CODE,
2144 SHIPPING_MARKS,
2145 /* H Integration: datamodel changes wrudge */
2146 SERVICE_LEVEL,
2147 MODE_OF_TRANSPORT,
2148 ASSIGNED_TO_FTE_TRIPS,
2149 AUTO_SC_EXCLUDE_FLAG,
2150 AUTO_AP_EXCLUDE_FLAG,
2151 AP_BATCH_ID,
2152 ROWID,
2153 NULL,
2154 NULL,
2155 NULL,
2156 NULL,
2157 NULL,
2158 NULL,
2159 NULL,
2160 NULL,
2161 NULL,
2162 NULL,
2163 NULL,
2164 NULL,
2165 NULL,
2166 NULL,
2167 NULL,
2168 /* J Inbound Logistics: New columns jckwok */
2169 SHIPMENT_DIRECTION,
2170 VENDOR_ID,
2171 PARTY_ID,
2172 ROUTING_RESPONSE_ID,
2173 RCV_SHIPMENT_HEADER_ID,
2174 ASN_SHIPMENT_HEADER_ID,
2175 SHIPPING_CONTROL
2176 /* J TP Release : ttrichy */
2177 ,TP_DELIVERY_NUMBER
2178 ,EARLIEST_PICKUP_DATE
2179 ,LATEST_PICKUP_DATE
2180 ,EARLIEST_DROPOFF_DATE
2181 ,LATEST_DROPOFF_DATE
2182 ,nvl(IGNORE_FOR_PLANNING, 'N') ignore_for_planning
2183 ,TP_PLAN_NAME
2184 -- J: W/V Changes
2185 ,WV_FROZEN_FLAG
2186 ,hash_string
2187 ,delivered_date,
2188 null , -- packing_slip
2189 -- bug 3667348
2190 REASON_OF_TRANSPORT,
2191 DESCRIPTION,
2192 -- bug 3667348
2193 'N',--Non Database field added for "Proration of weight from Delivery to delivery lines" Project(Bug#4254552).
2194 --OTM R12
2195 TMS_INTERFACE_FLAG,
2196 TMS_VERSION_NUMBER
2197 --
2198 FROM wsh_new_deliveries
2199 WHERE delivery_id = p_delivery_id;
2200
2201 others EXCEPTION;
2202
2203 --
2204 l_debug_on BOOLEAN;
2205 --
2206 l_module_name CONSTANT VARCHAR2(100) := 'wsh.plsql.' || G_PKG_NAME || '.' || 'POPULATE_RECORD';
2207 --
2208 BEGIN
2209
2210 --
2211 --
2212 l_debug_on := WSH_DEBUG_INTERFACE.g_debug;
2213 --
2214 IF l_debug_on IS NULL
2215 THEN
2216 l_debug_on := WSH_DEBUG_SV.is_debug_enabled;
2217 END IF;
2218 --
2219 IF l_debug_on THEN
2220 WSH_DEBUG_SV.push(l_module_name);
2221 --
2222 WSH_DEBUG_SV.log(l_module_name,'P_DELIVERY_ID',P_DELIVERY_ID);
2223 END IF;
2224 --
2225 IF (p_delivery_id IS NULL) THEN
2226 raise others;
2227 END IF;
2228
2229 OPEN delivery_record;
2230 FETCH delivery_record INTO x_delivery_info;
2231
2232 IF (delivery_record%NOTFOUND) THEN
2233 FND_MESSAGE.SET_NAME('WSH','WSH_DEL_NOT_FOUND');
2234 x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
2235 WSH_UTIL_CORE.ADD_MESSAGE(x_return_status,l_module_name);
2236 IF l_debug_on THEN
2237 WSH_DEBUG_SV.log(l_module_name,'WSH_DEL_NOT_FOUND');
2238 END IF;
2239 ELSE
2240 x_return_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
2241 END IF;
2242
2243 CLOSE delivery_record;
2244 --
2245 IF l_debug_on THEN
2246 WSH_DEBUG_SV.pop(l_module_name);
2247 END IF;
2248 --
2249 EXCEPTION
2250 WHEN others THEN
2251 IF (delivery_record%ISOPEN) THEN
2252 CLOSE delivery_record;
2253 END IF;
2254
2255 wsh_util_core.default_handler('WSH_NEW_DELIVERIES_PVT.POPULATE_RECORD',l_module_name);
2256 x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR;
2257 --
2258 IF l_debug_on THEN
2259 WSH_DEBUG_SV.logmsg(l_module_name,'Unexpected error has occured. Oracle error message is '|| SQLERRM,WSH_DEBUG_SV.C_UNEXPEC_ERR_LEVEL);
2260 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:OTHERS');
2261 END IF;
2262 --
2263 END Populate_Record;
2264
2265
2266 --
2267 -- Function: Get_Name
2268 -- Parameters: p_delivery_id - Id for delivery
2269 -- Description: This procedure will return Delivery Name for a Delivery Id
2270 --
2271
2272 FUNCTION Get_Name
2273 (p_delivery_id IN NUMBER
2274 ) RETURN VARCHAR2 IS
2275
2276 CURSOR get_name IS
2277 SELECT name
2278 FROM wsh_new_deliveries
2279 WHERE delivery_id = p_delivery_id;
2280
2281 x_name VARCHAR2(30);
2282
2283 others EXCEPTION;
2284
2285 --
2286 l_debug_on BOOLEAN;
2287 --
2288 l_module_name CONSTANT VARCHAR2(100) := 'wsh.plsql.' || G_PKG_NAME || '.' || 'GET_NAME';
2289 --
2290 BEGIN
2291
2292 --
2293 --
2294 l_debug_on := WSH_DEBUG_INTERFACE.g_debug;
2295 --
2296 IF l_debug_on IS NULL
2297 THEN
2298 l_debug_on := WSH_DEBUG_SV.is_debug_enabled;
2299 END IF;
2300 --
2301 IF l_debug_on THEN
2302 WSH_DEBUG_SV.push(l_module_name);
2303 --
2304 WSH_DEBUG_SV.log(l_module_name,'P_DELIVERY_ID',P_DELIVERY_ID);
2305 END IF;
2306 --
2307 IF (p_delivery_id IS NULL) THEN
2308 raise others;
2309 END IF;
2310
2311 OPEN get_name;
2312 FETCH get_name INTO x_name;
2313 CLOSE get_name;
2314
2315 IF (x_name IS NULL) THEN
2316 FND_MESSAGE.SET_NAME('WSH','WSH_DEL_NOT_FOUND');
2317 wsh_util_core.add_message(WSH_UTIL_CORE.G_RET_STS_ERROR,l_module_name);
2318 --
2319 IF l_debug_on THEN
2320 WSH_DEBUG_SV.log(l_module_name,'WSH_DEL_NOT_FOUND');
2321 WSH_DEBUG_SV.pop(l_module_name);
2322 END IF;
2323 --
2324 RETURN null;
2325 END IF;
2326
2327 --
2328 IF l_debug_on THEN
2329 WSH_DEBUG_SV.log(l_module_name,'returns ',x_name);
2330 WSH_DEBUG_SV.pop(l_module_name);
2331 END IF;
2332 --
2333 RETURN x_name;
2334
2335 EXCEPTION
2336
2337 WHEN others THEN
2338 wsh_util_core.default_handler('WSH_NEW_DELIVERIES_PVT.GET_NAME',l_module_name);
2339 --
2340 IF l_debug_on THEN
2341 WSH_DEBUG_SV.logmsg(l_module_name,'Unexpected error has occured. Oracle error message is '|| SQLERRM,WSH_DEBUG_SV.C_UNEXPEC_ERR_LEVEL);
2342 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:OTHERS');
2343 END IF;
2344 --
2345 RETURN null;
2346 END Get_Name;
2347
2348
2349 procedure Lock_Delivery(
2350 p_rec_attr_tab IN Delivery_Attr_Tbl_Type,
2351 p_caller IN VARCHAR2,
2352 p_valid_index_tab IN wsh_util_core.id_tab_type,
2353 x_valid_ids_tab OUT NOCOPY wsh_util_core.id_tab_type,
2354 x_return_status OUT NOCOPY VARCHAR2,
2355 p_action IN VARCHAR2 -- Bug fix 2657182
2356 )
2357
2358 IS
2359 --
2360 --
2361 l_index NUMBER := 0;
2362 l_num_errors NUMBER := 0;
2363 --
2364 --
2365 l_debug_on BOOLEAN;
2366 --
2367 l_module_name CONSTANT VARCHAR2(100) := 'wsh.plsql.' || G_PKG_NAME || '.' || 'LOCK_DELIVERY_WRAPPER';
2368 --
2369 BEGIN
2370 --
2371 l_debug_on := WSH_DEBUG_INTERFACE.g_debug;
2372 --
2373 IF l_debug_on IS NULL
2374 THEN
2375 l_debug_on := WSH_DEBUG_SV.is_debug_enabled;
2376 END IF;
2377 --
2378 IF l_debug_on THEN
2379 WSH_DEBUG_SV.push(l_module_name);
2380 WSH_DEBUG_SV.log(l_module_name,'p_caller',p_caller);
2381 WSH_DEBUG_SV.log(l_module_name, 'p_action', p_action);
2382 WSH_DEBUG_SV.log(l_module_name,'Total Number of Delivery Records being locked',p_valid_index_tab.COUNT);
2383 END IF;
2384 --
2385 --
2386 l_index := p_valid_index_tab.FIRST;
2387 --
2388 while l_index is not null loop
2389 begin
2390 --
2391 savepoint lock_delivery_loop;
2392 --
2393 if p_caller = 'WSH_FSTRX' then
2394 lock_delivery(p_rowid => p_rec_attr_tab(l_index).rowid,
2395 p_delivery_info => p_rec_attr_tab(l_index)
2396 );
2397 else
2398 wsh_new_deliveries_pvt.lock_dlvy_no_compare(p_delivery_id => p_rec_attr_tab(l_index).delivery_id);
2399 end if;
2400
2401 -- Bug fix 2657182
2402 -- Need to lock the related entities - lines, containers and delivery legs
2403 -- For actions ship confirm and autopacking
2404 if p_action IN ('CONFIRM', 'AUTO-PACK', 'AUTO-PACK-MASTER') then
2405 IF l_debug_on THEN
2406 WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_DELIVERY_DETAILS_PKG.LOCK_DETAIL',WSH_DEBUG_SV.C_PROC_LEVEL);
2407 END IF;
2408
2409 wsh_delivery_details_pkg.lock_detail_no_compare(
2410 p_delivery_id => p_rec_attr_tab(l_index).delivery_id);
2411
2412 if p_action = 'CONFIRM' then
2413 IF l_debug_on THEN
2414 WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_DELIVERY_LEGS_PVT.LOCK_DLVY_LEG',WSH_DEBUG_SV.C_PROC_LEVEL);
2415 END IF;
2416 wsh_delivery_legs_pvt.lock_dlvy_leg_no_compare(
2417 p_delivery_id => p_rec_attr_tab(l_index).delivery_id);
2418 end if;
2419
2420 end if;
2421 -- End of Bug fix 2657182
2422
2423 IF nvl(p_caller,FND_API.G_MISS_CHAR) NOT IN ('WSH_FSTRX','WSH_TRCON') THEN
2424 x_valid_ids_tab(x_valid_ids_tab.COUNT + 1) := p_rec_attr_tab(l_index).delivery_id;
2425 ELSE
2426 x_valid_ids_tab(x_valid_ids_tab.COUNT + 1) := l_index;
2427 END IF;
2428 --
2429 exception
2430 --
2431 WHEN app_exception.application_exception or app_exception.record_lock_exception THEN
2432 rollback to lock_delivery_loop;
2433 IF nvl(p_caller,FND_API.G_MISS_CHAR) = 'WSH_PUB'
2434 OR nvl(p_caller, '!') like 'FTE%'
2435 OR nvl(p_caller, '!') = 'WSH_TRCON' THEN
2436 FND_MESSAGE.SET_NAME('WSH', 'WSH_DLVY_LOCK_FAILED');
2437 FND_MESSAGE.SET_TOKEN('ENTITY_NAME',p_rec_attr_tab(l_index).name);
2438 wsh_util_core.add_message(wsh_util_core.g_ret_sts_error,l_module_name);
2439 END IF;
2440 l_num_errors := l_num_errors + 1;
2441 --
2442 IF l_debug_on THEN
2443 WSH_DEBUG_SV.log(l_module_name,'Unable to obtain lock on the Delivery Id',p_rec_attr_tab(l_index).delivery_id);
2444 END IF;
2445 --
2446 WHEN others THEN
2447 raise FND_API.G_EXC_UNEXPECTED_ERROR;
2448 end;
2449 --
2450 l_index := p_valid_index_tab.NEXT(l_index);
2451 --
2452 end loop;
2453 --
2454 --
2455 IF p_valid_index_tab.COUNT = 0 THEN
2456 x_return_status := wsh_util_core.g_ret_sts_success;
2457 ELSIF l_num_errors = p_valid_index_tab.COUNT THEN
2458 FND_MESSAGE.SET_NAME('WSH', 'WSH_UI_NOT_PERFORMED');
2459 x_return_status := wsh_util_core.g_ret_sts_error;
2460 wsh_util_core.add_message(x_return_status,l_module_name);
2461 IF l_debug_on THEN
2462 wsh_debug_sv.logmsg(l_module_name, 'WSH_UI_NOT_PERFORMED');
2463 END IF;
2464 RAISE FND_API.G_EXC_ERROR;
2465 ELSIF l_num_errors > 0 THEN
2466 FND_MESSAGE.SET_NAME('WSH', 'WSH_UI_NOT_PROCESSED');
2467 x_return_status := wsh_util_core.g_ret_sts_warning;
2468 wsh_util_core.add_message(x_return_status,l_module_name);
2469 IF l_debug_on THEN
2470 wsh_debug_sv.logmsg(l_module_name, 'WSH_UI_NOT_PROCESSED');
2471 END IF;
2472 raise wsh_util_core.g_exc_warning;
2473 ELSE
2474 x_return_status := wsh_util_core.g_ret_sts_success;
2475 END IF;
2476 --
2477 --
2478 --
2479 IF l_debug_on THEN
2480 WSH_DEBUG_SV.pop(l_module_name);
2481 END IF;
2482 --
2483 EXCEPTION
2484 --
2485 --
2486 WHEN FND_API.G_EXC_ERROR THEN
2487 --
2488 x_return_status := wsh_util_core.g_ret_sts_error;
2489 --
2490 IF l_debug_on THEN
2491 WSH_DEBUG_SV.logmsg(l_module_name,'FND_API.G_EXC_ERROR exception has occured ',WSH_DEBUG_SV.C_EXCEP_LEVEL);
2492 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:FND_API.G_EXC_ERROR');
2493 END IF;
2494 --
2495 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
2496 x_return_status := wsh_util_core.g_ret_sts_unexp_error;
2497 --
2498 IF l_debug_on THEN
2499 WSH_DEBUG_SV.logmsg(l_module_name,'Unexpected error has occured. Oracle error message is '|| SQLERRM,WSH_DEBUG_SV.C_UNEXPEC_ERR_LEVEL);
2500 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:FND_API.G_EXC_UNEXPECTED_ERROR');
2501 END IF;
2502 --
2503 --
2504 WHEN WSH_UTIL_CORE.G_EXC_WARNING THEN
2505 x_return_status := wsh_util_core.g_ret_sts_warning;
2506 --
2507 IF l_debug_on THEN
2508 WSH_DEBUG_SV.logmsg(l_module_name,'WSH_UTIL_CORE.G_EXC_WARNING exception has occured ',WSH_DEBUG_SV.C_EXCEP_LEVEL);
2509 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:WSH_UTIL_CORE.G_EXC_WARNING');
2510 END IF;
2511 --
2512 --
2513 WHEN OTHERS THEN
2514 --
2515 x_return_status := wsh_util_core.g_ret_sts_unexp_error;
2516 --
2517 wsh_util_core.default_handler('WSH_DELIVERY_DETAILS_PKG.LOCK_DELIVERY_WRAPPER',l_module_name);
2518 --
2519 IF l_debug_on THEN
2520 WSH_DEBUG_SV.logmsg(l_module_name,'Unexpected error has occured. Oracle error message is '|| SQLERRM,WSH_DEBUG_SV.C_UNEXPEC_ERR_LEVEL);
2521 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:OTHERS');
2522 END IF;
2523 --
2524 --
2525 END Lock_Delivery;
2526
2527 /* ---------------------------------------------------------------------
2528 Procedure: Lock_Dlvy_No_Compare
2529
2530 Parameters: Delivery Id.
2531
2532 Description: This procedure is used for obtaining locks of deliveries
2533 using only the delivery id. This is called by the
2534 wrapper lock API ,when the p_caller is NOT WSHFSTRX.
2535 This procedure does not compare the attributes. It just
2536 does a SELECT using FOR UPDATE NOWAIT
2537 Created: Harmonization Project. Patchset I
2538 ----------------------------------------------------------------------- */
2539
2540
2541 PROCEDURE Lock_Dlvy_No_Compare(
2542 p_delivery_id IN NUMBER)
2543 IS
2544
2545 CURSOR c_lock_dlvy(p_dlvy_id NUMBER) IS
2546 SELECT wnd.delivery_id
2547 FROM wsh_new_deliveries wnd
2548 WHERE wnd.delivery_id = p_dlvy_id
2549 FOR UPDATE NOWAIT;
2550
2551 l_dummy_dlvy_id NUMBER;
2552
2553 l_debug_on BOOLEAN;
2554 --
2555 l_module_name CONSTANT VARCHAR2(100) := 'wsh.plsql.' || G_PKG_NAME || '.' || 'LOCK_DLVY_NO_COMPARE';
2556
2557 BEGIN
2558 --
2559 l_debug_on := WSH_DEBUG_INTERFACE.g_debug;
2560 --
2561 IF l_debug_on IS NULL
2562 THEN
2563 l_debug_on := WSH_DEBUG_SV.is_debug_enabled;
2564 END IF;
2565 --
2566 IF l_debug_on THEN
2567 WSH_DEBUG_SV.push(l_module_name);
2568 WSH_DEBUG_SV.log(l_module_name, 'p_delivery_id', p_delivery_id);
2569 END IF;
2570 --
2571
2572 IF p_delivery_id IS NOT NULL THEN
2573 OPEN c_lock_dlvy(p_delivery_id);
2574 FETCH c_lock_dlvy INTO l_dummy_dlvy_id;
2575 CLOSE c_lock_dlvy;
2576 END IF;
2577
2578 IF l_debug_on THEN
2579 WSH_DEBUG_SV.pop(l_module_name);
2580 END IF;
2581
2582 EXCEPTION
2583 WHEN app_exception.application_exception or app_exception.record_lock_exception THEN
2584 IF l_debug_on THEN
2585 wsh_debug_sv.log(l_module_name, 'Could not lock delivery', p_delivery_id);
2586 END IF;
2587 IF l_debug_on THEN
2588 WSH_DEBUG_SV.logmsg(l_module_name,'APP_EXCEPTION.APPLICATION_EXCEPTION exception has occured.',WSH_DEBUG_SV.C_EXCEP_LEVEL);
2589 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:APP_EXCEPTION.APPLICATION_EXCEPTION');
2590 END IF;
2591 --
2592 RAISE;
2593
2594 END Lock_Dlvy_No_Compare;
2595
2596
2597 -- J-IB-NPARIKH-{
2598 --
2599 --========================================================================
2600 -- PROCEDURE : clone
2601 --
2602 -- PARAMETERS: p_delivery_rec Delivery record for new delivery (Table handler)
2603 -- p_delivery_id Source Delivery ID
2604 -- p_copy_legs Copy delivery legs as well (Y/N)
2605 -- x_delivery_id ID for new delivery
2606 -- x_rowid RowID for new delivery
2607 -- x_leg_id_tab Table of delivery leg IDs for new delivery.
2608 -- x_return_status Return status of the API
2609 --
2610 --
2611 -- COMMENT : This procedure clones an input delivery.
2612 -- You can override specific attributes by specifying its value in p_delivery_rec
2613 -- for new delivery.
2614 -- IF p_delivery_rec."attribute" is null, it is inherited from source delivery.
2615 -- IF p_delivery_rec."attribute" is G_MISS*, it is set to NULL.
2616 --
2617 -- It also copies delivery legs, depending on value of parameter p_copy_legs.
2618 -- If p_copy_legs = 'Y', set itinerary_complete on cloned delivery same as old del.
2619 -- If p_copy_legs = 'N', set itinerary_complete on cloned delivery to 'N'
2620 --
2621 --========================================================================
2622 --
2623 PROCEDURE clone
2624 (
2625 p_delivery_rec IN Delivery_Rec_Type,
2626 p_delivery_id IN NUMBER,
2627 p_copy_legs IN VARCHAR2 DEFAULT 'N',
2628 x_delivery_id OUT NOCOPY NUMBER,
2629 x_rowid OUT NOCOPY VARCHAR2,
2630 x_leg_id_tab OUT NOCOPY WSH_UTIL_CORE.id_tab_type,
2631 x_return_status OUT NOCOPY VARCHAR2
2632 )
2633 IS
2634 --{
2635 --
2636 -- Generate delivery ID
2637 --
2638 CURSOR get_next_delivery
2639 IS
2640 SELECT wsh_new_deliveries_s.nextval
2641 FROM dual;
2642 --
2643 CURSOR dlvy_csr(p_delivery_id NUMBER)
2644 IS
2645 SELECT rowid
2646 FROM wsh_new_deliveries
2647 WHERE delivery_id = p_delivery_id;
2648 --
2649 CURSOR leg_csr(p_delivery_id NUMBER)
2650 IS
2651 SELECT delivery_leg_id
2652 FROM wsh_delivery_legs
2653 WHERE delivery_id = p_delivery_id;
2654 --
2655 --
2656 CURSOR count_delivery_rows (p_delivery_name VARCHAR2) IS
2657 SELECT delivery_id
2658 FROM wsh_new_deliveries
2659 WHERE name = p_delivery_name;
2660 --
2661 l_delivery_id NUMBER;
2662 l_delivery_name VARCHAR2(30);
2663 l_temp_id NUMBER;
2664 l_row_check NUMBER;
2665 --
2666 --
2667 l_debug_on BOOLEAN;
2668 --
2669 l_module_name CONSTANT VARCHAR2(100) := 'wsh.plsql.' || G_PKG_NAME || '.' || 'clone';
2670 --
2671 --}
2672 BEGIN
2673 --{
2674 --
2675 --
2676 l_debug_on := WSH_DEBUG_INTERFACE.g_debug;
2677 --
2678 IF l_debug_on IS NULL
2679 THEN
2680 l_debug_on := WSH_DEBUG_SV.is_debug_enabled;
2681 END IF;
2682 --
2683 IF l_debug_on THEN
2684 WSH_DEBUG_SV.push(l_module_name);
2685 --
2686 WSH_DEBUG_SV.log(l_module_name,'p_delivery_id',p_delivery_id);
2687 WSH_DEBUG_SV.log(l_module_name,'p_copy_legs',p_copy_legs);
2688 END IF;
2689 --
2690 x_return_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
2691 --
2692 -- Generate delivery ID
2693 --
2694 OPEN get_next_delivery;
2695 FETCH get_next_delivery INTO l_delivery_id;
2696 CLOSE get_next_delivery;
2697 --
2698 --
2699 --
2700 --{
2701 IF p_delivery_rec.name IS NULL THEN
2702 --
2703 -- If delivery name was not passed in, generate it.
2704 --
2705 l_delivery_name := wsh_custom_pub.delivery_name(l_delivery_id,p_delivery_rec);
2706
2707 -- shipping default make sure the delivery name is not duplicate
2708 IF l_debug_on THEN
2709 WSH_DEBUG_SV.log(l_module_name,'l_delivery_name',l_delivery_name);
2710 WSH_DEBUG_SV.log(l_module_name,'l_delivery_id',l_delivery_id);
2711 END IF;
2712 IF ( l_delivery_name = to_char(l_delivery_id) ) THEN
2713 l_temp_id := l_delivery_id;
2714
2715 LOOP
2716
2717 l_delivery_name := to_char(l_temp_id);
2718
2719 OPEN count_delivery_rows( l_delivery_name);
2720 FETCH count_delivery_rows INTO l_row_check;
2721 IF l_debug_on THEN
2722 WSH_DEBUG_SV.log(l_module_name,'l_row_check',l_row_check);
2723 END IF;
2724 IF (count_delivery_rows%NOTFOUND) THEN
2725 CLOSE count_delivery_rows;
2726 EXIT;
2727 END IF;
2728
2729 CLOSE count_delivery_rows;
2730
2731 OPEN get_next_delivery;
2732 FETCH get_next_delivery INTO l_temp_id;
2733 CLOSE get_next_delivery;
2734 IF l_debug_on THEN
2735 WSH_DEBUG_SV.log(l_module_name,'l_temp_id',l_temp_id);
2736 END IF;
2737 END LOOP;
2738
2739 l_delivery_id := l_temp_id;
2740
2741 END IF;
2742
2743 ELSE
2744 l_delivery_name := p_delivery_rec.name;
2745 END IF;
2746 --}
2747 --
2748 --
2749 INSERT INTO WSH_NEW_DELIVERIES
2750 (
2751 DELIVERY_ID,
2752 NAME,
2753 PLANNED_FLAG,
2754 STATUS_CODE,
2755 DELIVERY_TYPE,
2756 LOADING_SEQUENCE,
2757 LOADING_ORDER_FLAG,
2758 INITIAL_PICKUP_DATE,
2759 INITIAL_PICKUP_LOCATION_ID,
2760 ORGANIZATION_ID,
2761 ULTIMATE_DROPOFF_LOCATION_ID,
2762 ULTIMATE_DROPOFF_DATE,
2763 CUSTOMER_ID,
2764 INTMED_SHIP_TO_LOCATION_ID,
2765 POOLED_SHIP_TO_LOCATION_ID,
2766 CARRIER_ID,
2767 SHIP_METHOD_CODE,
2768 FREIGHT_TERMS_CODE,
2769 FOB_CODE,
2770 FOB_LOCATION_ID,
2771 WAYBILL,
2772 DOCK_CODE,
2773 ACCEPTANCE_FLAG,
2774 ACCEPTED_BY,
2775 ACCEPTED_DATE,
2776 ACKNOWLEDGED_BY,
2777 CONFIRMED_BY,
2778 CONFIRM_DATE,
2779 ASN_DATE_SENT,
2780 ASN_STATUS_CODE,
2781 ASN_SEQ_NUMBER,
2782 GROSS_WEIGHT,
2783 NET_WEIGHT,
2784 WEIGHT_UOM_CODE,
2785 VOLUME,
2786 VOLUME_UOM_CODE,
2787 ADDITIONAL_SHIPMENT_INFO,
2788 CURRENCY_CODE,
2789 ATTRIBUTE_CATEGORY,
2790 ATTRIBUTE1,
2791 ATTRIBUTE2,
2792 ATTRIBUTE3,
2793 ATTRIBUTE4,
2794 ATTRIBUTE5,
2795 ATTRIBUTE6,
2796 ATTRIBUTE7,
2797 ATTRIBUTE8,
2798 ATTRIBUTE9,
2799 ATTRIBUTE10,
2800 ATTRIBUTE11,
2801 ATTRIBUTE12,
2802 ATTRIBUTE13,
2803 ATTRIBUTE14,
2804 ATTRIBUTE15,
2805 TP_ATTRIBUTE_CATEGORY,
2806 TP_ATTRIBUTE1,
2807 TP_ATTRIBUTE2,
2808 TP_ATTRIBUTE3,
2809 TP_ATTRIBUTE4,
2810 TP_ATTRIBUTE5,
2811 TP_ATTRIBUTE6,
2812 TP_ATTRIBUTE7,
2813 TP_ATTRIBUTE8,
2814 TP_ATTRIBUTE9,
2815 TP_ATTRIBUTE10,
2816 TP_ATTRIBUTE11,
2817 TP_ATTRIBUTE12,
2818 TP_ATTRIBUTE13,
2819 TP_ATTRIBUTE14,
2820 TP_ATTRIBUTE15,
2821 GLOBAL_ATTRIBUTE_CATEGORY,
2822 GLOBAL_ATTRIBUTE1,
2823 GLOBAL_ATTRIBUTE2,
2824 GLOBAL_ATTRIBUTE3,
2825 GLOBAL_ATTRIBUTE4,
2826 GLOBAL_ATTRIBUTE5,
2827 GLOBAL_ATTRIBUTE6,
2828 GLOBAL_ATTRIBUTE7,
2829 GLOBAL_ATTRIBUTE8,
2830 GLOBAL_ATTRIBUTE9,
2831 GLOBAL_ATTRIBUTE10,
2832 GLOBAL_ATTRIBUTE11,
2833 GLOBAL_ATTRIBUTE12,
2834 GLOBAL_ATTRIBUTE13,
2835 GLOBAL_ATTRIBUTE14,
2836 GLOBAL_ATTRIBUTE15,
2837 GLOBAL_ATTRIBUTE16,
2838 GLOBAL_ATTRIBUTE17,
2839 GLOBAL_ATTRIBUTE18,
2840 GLOBAL_ATTRIBUTE19,
2841 GLOBAL_ATTRIBUTE20,
2842 CREATION_DATE,
2843 CREATED_BY,
2844 LAST_UPDATE_DATE,
2845 LAST_UPDATED_BY,
2846 LAST_UPDATE_LOGIN,
2847 PROGRAM_APPLICATION_ID,
2848 PROGRAM_ID,
2849 PROGRAM_UPDATE_DATE,
2850 REQUEST_ID,
2851 BATCH_ID,
2852 HASH_VALUE,
2853 SOURCE_HEADER_ID,
2854 NUMBER_OF_LPN,
2855 COD_AMOUNT,
2856 COD_CURRENCY_CODE,
2857 COD_REMIT_TO,
2858 COD_CHARGE_PAID_BY,
2859 PROBLEM_CONTACT_REFERENCE,
2860 PORT_OF_LOADING,
2861 PORT_OF_DISCHARGE,
2862 FTZ_NUMBER,
2863 ROUTED_EXPORT_TXN,
2864 ENTRY_NUMBER,
2865 ROUTING_INSTRUCTIONS,
2866 IN_BOND_CODE,
2867 SHIPPING_MARKS,
2868 SERVICE_LEVEL,
2869 MODE_OF_TRANSPORT,
2870 ASSIGNED_TO_FTE_TRIPS,
2871 AUTO_SC_EXCLUDE_FLAG,
2872 AUTO_AP_EXCLUDE_FLAG,
2873 AP_BATCH_ID,
2874 SHIPMENT_DIRECTION,
2875 VENDOR_ID,
2876 PARTY_ID,
2877 ROUTING_RESPONSE_ID,
2878 RCV_SHIPMENT_HEADER_ID,
2879 ASN_SHIPMENT_HEADER_ID,
2880 SHIPPING_CONTROL,
2881 TP_DELIVERY_NUMBER,
2882 EARLIEST_PICKUP_DATE,
2883 LATEST_PICKUP_DATE,
2884 EARLIEST_DROPOFF_DATE,
2885 LATEST_DROPOFF_DATE,
2886 IGNORE_FOR_PLANNING,
2887 TP_PLAN_NAME,
2888 HASH_STRING,
2889 DELIVERED_DATE,
2890 -- bug 3667348
2891 REASON_OF_TRANSPORT,
2892 DESCRIPTION,
2893 -- bug 3667348
2894 ITINERARY_COMPLETE,
2895 --OTM R12
2896 TMS_INTERFACE_FLAG,
2897 TMS_VERSION_NUMBER
2898 --
2899 )
2900 SELECT
2901 l_delivery_id,
2902 --DECODE(p_delivery_rec.NAME,NULL,WND.NAME,FND_API.G_MISS_CHAR,NULL,p_delivery_rec.NAME),
2903 DECODE(l_delivery_NAME,NULL,WND.NAME,FND_API.G_MISS_CHAR,NULL,l_delivery_NAME),
2904 DECODE(p_delivery_rec.PLANNED_FLAG,NULL,WND.PLANNED_FLAG,FND_API.G_MISS_CHAR,NULL,p_delivery_rec.PLANNED_FLAG),
2905 DECODE(p_delivery_rec.STATUS_CODE,NULL,WND.STATUS_CODE,FND_API.G_MISS_CHAR,NULL,p_delivery_rec.STATUS_CODE),
2906 DECODE(p_delivery_rec.DELIVERY_TYPE,NULL,WND.DELIVERY_TYPE,FND_API.G_MISS_CHAR,NULL,p_delivery_rec.DELIVERY_TYPE),
2907 DECODE(p_delivery_rec.LOADING_SEQUENCE,NULL,WND.LOADING_SEQUENCE,FND_API.G_MISS_NUM,NULL,p_delivery_rec.LOADING_SEQUENCE),
2908 DECODE(p_delivery_rec.LOADING_ORDER_FLAG,NULL,WND.LOADING_ORDER_FLAG,FND_API.G_MISS_CHAR,NULL,p_delivery_rec.LOADING_ORDER_FLAG),
2909 DECODE(p_delivery_rec.INITIAL_PICKUP_DATE,NULL,WND.INITIAL_PICKUP_DATE,FND_API.G_MISS_DATE,NULL,p_delivery_rec.INITIAL_PICKUP_DATE),
2910 DECODE(p_delivery_rec.INITIAL_PICKUP_LOCATION_ID,NULL,WND.INITIAL_PICKUP_LOCATION_ID,FND_API.G_MISS_NUM,NULL,p_delivery_rec.INITIAL_PICKUP_LOCATION_ID),
2911 DECODE(p_delivery_rec.ORGANIZATION_ID,NULL,WND.ORGANIZATION_ID,FND_API.G_MISS_NUM,NULL,p_delivery_rec.ORGANIZATION_ID),
2912 DECODE(p_delivery_rec.ULTIMATE_DROPOFF_LOCATION_ID,NULL,WND.ULTIMATE_DROPOFF_LOCATION_ID,FND_API.G_MISS_NUM,NULL,p_delivery_rec.ULTIMATE_DROPOFF_LOCATION_ID),
2913 DECODE(p_delivery_rec.ULTIMATE_DROPOFF_DATE,NULL,WND.ULTIMATE_DROPOFF_DATE,FND_API.G_MISS_DATE,NULL,p_delivery_rec.ULTIMATE_DROPOFF_DATE),
2914 DECODE(p_delivery_rec.CUSTOMER_ID,NULL,WND.CUSTOMER_ID,FND_API.G_MISS_NUM,NULL,p_delivery_rec.CUSTOMER_ID),
2915 DECODE(p_delivery_rec.INTMED_SHIP_TO_LOCATION_ID,NULL,WND.INTMED_SHIP_TO_LOCATION_ID,FND_API.G_MISS_NUM,NULL,p_delivery_rec.INTMED_SHIP_TO_LOCATION_ID),
2916 DECODE(p_delivery_rec.POOLED_SHIP_TO_LOCATION_ID,NULL,WND.POOLED_SHIP_TO_LOCATION_ID,FND_API.G_MISS_NUM,NULL,p_delivery_rec.POOLED_SHIP_TO_LOCATION_ID),
2917 DECODE(p_delivery_rec.CARRIER_ID,NULL,WND.CARRIER_ID,FND_API.G_MISS_NUM,NULL,p_delivery_rec.CARRIER_ID),
2918 DECODE(p_delivery_rec.SHIP_METHOD_CODE,NULL,WND.SHIP_METHOD_CODE,FND_API.G_MISS_CHAR,NULL,p_delivery_rec.SHIP_METHOD_CODE),
2919 DECODE(p_delivery_rec.FREIGHT_TERMS_CODE,NULL,WND.FREIGHT_TERMS_CODE,FND_API.G_MISS_CHAR,NULL,p_delivery_rec.FREIGHT_TERMS_CODE),
2920 DECODE(p_delivery_rec.FOB_CODE,NULL,WND.FOB_CODE,FND_API.G_MISS_CHAR,NULL,p_delivery_rec.FOB_CODE),
2921 DECODE(p_delivery_rec.FOB_LOCATION_ID,NULL,WND.FOB_LOCATION_ID,FND_API.G_MISS_NUM,NULL,p_delivery_rec.FOB_LOCATION_ID),
2922 DECODE(p_delivery_rec.WAYBILL,NULL,WND.WAYBILL,FND_API.G_MISS_CHAR,NULL,p_delivery_rec.WAYBILL),
2923 DECODE(p_delivery_rec.DOCK_CODE,NULL,WND.DOCK_CODE,FND_API.G_MISS_CHAR,NULL,p_delivery_rec.DOCK_CODE),
2924 DECODE(p_delivery_rec.ACCEPTANCE_FLAG,NULL,WND.ACCEPTANCE_FLAG,FND_API.G_MISS_CHAR,NULL,p_delivery_rec.ACCEPTANCE_FLAG),
2925 DECODE(p_delivery_rec.ACCEPTED_BY,NULL,WND.ACCEPTED_BY,FND_API.G_MISS_CHAR,NULL,p_delivery_rec.ACCEPTED_BY),
2926 DECODE(p_delivery_rec.ACCEPTED_DATE,NULL,WND.ACCEPTED_DATE,FND_API.G_MISS_DATE,NULL,p_delivery_rec.ACCEPTED_DATE),
2927 DECODE(p_delivery_rec.ACKNOWLEDGED_BY,NULL,WND.ACKNOWLEDGED_BY,FND_API.G_MISS_CHAR,NULL,p_delivery_rec.ACKNOWLEDGED_BY),
2928 DECODE(p_delivery_rec.CONFIRMED_BY,NULL,WND.CONFIRMED_BY,FND_API.G_MISS_CHAR,NULL,p_delivery_rec.CONFIRMED_BY),
2929 DECODE(p_delivery_rec.CONFIRM_DATE,NULL,WND.CONFIRM_DATE,FND_API.G_MISS_DATE,NULL,p_delivery_rec.CONFIRM_DATE),
2930 DECODE(p_delivery_rec.ASN_DATE_SENT,NULL,WND.ASN_DATE_SENT,FND_API.G_MISS_DATE,NULL,p_delivery_rec.ASN_DATE_SENT),
2931 DECODE(p_delivery_rec.ASN_STATUS_CODE,NULL,WND.ASN_STATUS_CODE,FND_API.G_MISS_CHAR,NULL,p_delivery_rec.ASN_STATUS_CODE),
2932 DECODE(p_delivery_rec.ASN_SEQ_NUMBER,NULL,WND.ASN_SEQ_NUMBER,FND_API.G_MISS_NUM,NULL,p_delivery_rec.ASN_SEQ_NUMBER),
2933 DECODE(p_delivery_rec.GROSS_WEIGHT,NULL,WND.GROSS_WEIGHT,FND_API.G_MISS_NUM,NULL,p_delivery_rec.GROSS_WEIGHT),
2934 DECODE(p_delivery_rec.NET_WEIGHT,NULL,WND.NET_WEIGHT,FND_API.G_MISS_NUM,NULL,p_delivery_rec.NET_WEIGHT),
2935 DECODE(p_delivery_rec.WEIGHT_UOM_CODE,NULL,WND.WEIGHT_UOM_CODE,FND_API.G_MISS_CHAR,NULL,p_delivery_rec.WEIGHT_UOM_CODE),
2936 DECODE(p_delivery_rec.VOLUME,NULL,WND.VOLUME,FND_API.G_MISS_NUM,NULL,p_delivery_rec.VOLUME),
2937 DECODE(p_delivery_rec.VOLUME_UOM_CODE,NULL,WND.VOLUME_UOM_CODE,FND_API.G_MISS_CHAR,NULL,p_delivery_rec.VOLUME_UOM_CODE),
2938 DECODE(p_delivery_rec.ADDITIONAL_SHIPMENT_INFO,NULL,WND.ADDITIONAL_SHIPMENT_INFO,FND_API.G_MISS_CHAR,NULL,p_delivery_rec.ADDITIONAL_SHIPMENT_INFO),
2939 DECODE(p_delivery_rec.CURRENCY_CODE,NULL,WND.CURRENCY_CODE,FND_API.G_MISS_CHAR,NULL,p_delivery_rec.CURRENCY_CODE),
2940 DECODE(p_delivery_rec.ATTRIBUTE_CATEGORY,NULL,WND.ATTRIBUTE_CATEGORY,FND_API.G_MISS_CHAR,NULL,p_delivery_rec.ATTRIBUTE_CATEGORY),
2941 DECODE(p_delivery_rec.ATTRIBUTE1,NULL,WND.ATTRIBUTE1,FND_API.G_MISS_CHAR,NULL,p_delivery_rec.ATTRIBUTE1),
2942 DECODE(p_delivery_rec.ATTRIBUTE2,NULL,WND.ATTRIBUTE2,FND_API.G_MISS_CHAR,NULL,p_delivery_rec.ATTRIBUTE2),
2943 DECODE(p_delivery_rec.ATTRIBUTE3,NULL,WND.ATTRIBUTE3,FND_API.G_MISS_CHAR,NULL,p_delivery_rec.ATTRIBUTE3),
2944 DECODE(p_delivery_rec.ATTRIBUTE4,NULL,WND.ATTRIBUTE4,FND_API.G_MISS_CHAR,NULL,p_delivery_rec.ATTRIBUTE4),
2945 DECODE(p_delivery_rec.ATTRIBUTE5,NULL,WND.ATTRIBUTE5,FND_API.G_MISS_CHAR,NULL,p_delivery_rec.ATTRIBUTE5),
2946 DECODE(p_delivery_rec.ATTRIBUTE6,NULL,WND.ATTRIBUTE6,FND_API.G_MISS_CHAR,NULL,p_delivery_rec.ATTRIBUTE6),
2947 DECODE(p_delivery_rec.ATTRIBUTE7,NULL,WND.ATTRIBUTE7,FND_API.G_MISS_CHAR,NULL,p_delivery_rec.ATTRIBUTE7),
2948 DECODE(p_delivery_rec.ATTRIBUTE8,NULL,WND.ATTRIBUTE8,FND_API.G_MISS_CHAR,NULL,p_delivery_rec.ATTRIBUTE8),
2949 DECODE(p_delivery_rec.ATTRIBUTE9,NULL,WND.ATTRIBUTE9,FND_API.G_MISS_CHAR,NULL,p_delivery_rec.ATTRIBUTE9),
2950 DECODE(p_delivery_rec.ATTRIBUTE10,NULL,WND.ATTRIBUTE10,FND_API.G_MISS_CHAR,NULL,p_delivery_rec.ATTRIBUTE10),
2951 DECODE(p_delivery_rec.ATTRIBUTE11,NULL,WND.ATTRIBUTE11,FND_API.G_MISS_CHAR,NULL,p_delivery_rec.ATTRIBUTE11),
2952 DECODE(p_delivery_rec.ATTRIBUTE12,NULL,WND.ATTRIBUTE12,FND_API.G_MISS_CHAR,NULL,p_delivery_rec.ATTRIBUTE12),
2953 DECODE(p_delivery_rec.ATTRIBUTE13,NULL,WND.ATTRIBUTE13,FND_API.G_MISS_CHAR,NULL,p_delivery_rec.ATTRIBUTE13),
2954 DECODE(p_delivery_rec.ATTRIBUTE14,NULL,WND.ATTRIBUTE14,FND_API.G_MISS_CHAR,NULL,p_delivery_rec.ATTRIBUTE14),
2955 DECODE(p_delivery_rec.ATTRIBUTE15,NULL,WND.ATTRIBUTE15,FND_API.G_MISS_CHAR,NULL,p_delivery_rec.ATTRIBUTE15),
2956 DECODE(p_delivery_rec.TP_ATTRIBUTE_CATEGORY,NULL,WND.TP_ATTRIBUTE_CATEGORY,FND_API.G_MISS_CHAR,NULL,p_delivery_rec.TP_ATTRIBUTE_CATEGORY),
2957 DECODE(p_delivery_rec.TP_ATTRIBUTE1,NULL,WND.TP_ATTRIBUTE1,FND_API.G_MISS_CHAR,NULL,p_delivery_rec.TP_ATTRIBUTE1),
2958 DECODE(p_delivery_rec.TP_ATTRIBUTE2,NULL,WND.TP_ATTRIBUTE2,FND_API.G_MISS_CHAR,NULL,p_delivery_rec.TP_ATTRIBUTE2),
2959 DECODE(p_delivery_rec.TP_ATTRIBUTE3,NULL,WND.TP_ATTRIBUTE3,FND_API.G_MISS_CHAR,NULL,p_delivery_rec.TP_ATTRIBUTE3),
2960 DECODE(p_delivery_rec.TP_ATTRIBUTE4,NULL,WND.TP_ATTRIBUTE4,FND_API.G_MISS_CHAR,NULL,p_delivery_rec.TP_ATTRIBUTE4),
2961 DECODE(p_delivery_rec.TP_ATTRIBUTE5,NULL,WND.TP_ATTRIBUTE5,FND_API.G_MISS_CHAR,NULL,p_delivery_rec.TP_ATTRIBUTE5),
2962 DECODE(p_delivery_rec.TP_ATTRIBUTE6,NULL,WND.TP_ATTRIBUTE6,FND_API.G_MISS_CHAR,NULL,p_delivery_rec.TP_ATTRIBUTE6),
2963 DECODE(p_delivery_rec.TP_ATTRIBUTE7,NULL,WND.TP_ATTRIBUTE7,FND_API.G_MISS_CHAR,NULL,p_delivery_rec.TP_ATTRIBUTE7),
2964 DECODE(p_delivery_rec.TP_ATTRIBUTE8,NULL,WND.TP_ATTRIBUTE8,FND_API.G_MISS_CHAR,NULL,p_delivery_rec.TP_ATTRIBUTE8),
2965 DECODE(p_delivery_rec.TP_ATTRIBUTE9,NULL,WND.TP_ATTRIBUTE9,FND_API.G_MISS_CHAR,NULL,p_delivery_rec.TP_ATTRIBUTE9),
2966 DECODE(p_delivery_rec.TP_ATTRIBUTE10,NULL,WND.TP_ATTRIBUTE10,FND_API.G_MISS_CHAR,NULL,p_delivery_rec.TP_ATTRIBUTE10),
2967 DECODE(p_delivery_rec.TP_ATTRIBUTE11,NULL,WND.TP_ATTRIBUTE11,FND_API.G_MISS_CHAR,NULL,p_delivery_rec.TP_ATTRIBUTE11),
2968 DECODE(p_delivery_rec.TP_ATTRIBUTE12,NULL,WND.TP_ATTRIBUTE12,FND_API.G_MISS_CHAR,NULL,p_delivery_rec.TP_ATTRIBUTE12),
2969 DECODE(p_delivery_rec.TP_ATTRIBUTE13,NULL,WND.TP_ATTRIBUTE13,FND_API.G_MISS_CHAR,NULL,p_delivery_rec.TP_ATTRIBUTE13),
2970 DECODE(p_delivery_rec.TP_ATTRIBUTE14,NULL,WND.TP_ATTRIBUTE14,FND_API.G_MISS_CHAR,NULL,p_delivery_rec.TP_ATTRIBUTE14),
2971 DECODE(p_delivery_rec.TP_ATTRIBUTE15,NULL,WND.TP_ATTRIBUTE15,FND_API.G_MISS_CHAR,NULL,p_delivery_rec.TP_ATTRIBUTE15),
2972 DECODE(p_delivery_rec.GLOBAL_ATTRIBUTE_CATEGORY,NULL,WND.GLOBAL_ATTRIBUTE_CATEGORY,FND_API.G_MISS_CHAR,NULL,p_delivery_rec.GLOBAL_ATTRIBUTE_CATEGORY),
2973 DECODE(p_delivery_rec.GLOBAL_ATTRIBUTE1,NULL,WND.GLOBAL_ATTRIBUTE1,FND_API.G_MISS_CHAR,NULL,p_delivery_rec.GLOBAL_ATTRIBUTE1),
2974 DECODE(p_delivery_rec.GLOBAL_ATTRIBUTE2,NULL,WND.GLOBAL_ATTRIBUTE2,FND_API.G_MISS_CHAR,NULL,p_delivery_rec.GLOBAL_ATTRIBUTE2),
2975 DECODE(p_delivery_rec.GLOBAL_ATTRIBUTE3,NULL,WND.GLOBAL_ATTRIBUTE3,FND_API.G_MISS_CHAR,NULL,p_delivery_rec.GLOBAL_ATTRIBUTE3),
2976 DECODE(p_delivery_rec.GLOBAL_ATTRIBUTE4,NULL,WND.GLOBAL_ATTRIBUTE4,FND_API.G_MISS_CHAR,NULL,p_delivery_rec.GLOBAL_ATTRIBUTE4),
2977 DECODE(p_delivery_rec.GLOBAL_ATTRIBUTE5,NULL,WND.GLOBAL_ATTRIBUTE5,FND_API.G_MISS_CHAR,NULL,p_delivery_rec.GLOBAL_ATTRIBUTE5),
2978 DECODE(p_delivery_rec.GLOBAL_ATTRIBUTE6,NULL,WND.GLOBAL_ATTRIBUTE6,FND_API.G_MISS_CHAR,NULL,p_delivery_rec.GLOBAL_ATTRIBUTE6),
2979 DECODE(p_delivery_rec.GLOBAL_ATTRIBUTE7,NULL,WND.GLOBAL_ATTRIBUTE7,FND_API.G_MISS_CHAR,NULL,p_delivery_rec.GLOBAL_ATTRIBUTE7),
2980 DECODE(p_delivery_rec.GLOBAL_ATTRIBUTE8,NULL,WND.GLOBAL_ATTRIBUTE8,FND_API.G_MISS_CHAR,NULL,p_delivery_rec.GLOBAL_ATTRIBUTE8),
2981 DECODE(p_delivery_rec.GLOBAL_ATTRIBUTE9,NULL,WND.GLOBAL_ATTRIBUTE9,FND_API.G_MISS_CHAR,NULL,p_delivery_rec.GLOBAL_ATTRIBUTE9),
2982 DECODE(p_delivery_rec.GLOBAL_ATTRIBUTE10,NULL,WND.GLOBAL_ATTRIBUTE10,FND_API.G_MISS_CHAR,NULL,p_delivery_rec.GLOBAL_ATTRIBUTE10),
2983 DECODE(p_delivery_rec.GLOBAL_ATTRIBUTE11,NULL,WND.GLOBAL_ATTRIBUTE11,FND_API.G_MISS_CHAR,NULL,p_delivery_rec.GLOBAL_ATTRIBUTE11),
2984 DECODE(p_delivery_rec.GLOBAL_ATTRIBUTE12,NULL,WND.GLOBAL_ATTRIBUTE12,FND_API.G_MISS_CHAR,NULL,p_delivery_rec.GLOBAL_ATTRIBUTE12),
2985 DECODE(p_delivery_rec.GLOBAL_ATTRIBUTE13,NULL,WND.GLOBAL_ATTRIBUTE13,FND_API.G_MISS_CHAR,NULL,p_delivery_rec.GLOBAL_ATTRIBUTE13),
2986 DECODE(p_delivery_rec.GLOBAL_ATTRIBUTE14,NULL,WND.GLOBAL_ATTRIBUTE14,FND_API.G_MISS_CHAR,NULL,p_delivery_rec.GLOBAL_ATTRIBUTE14),
2987 DECODE(p_delivery_rec.GLOBAL_ATTRIBUTE15,NULL,WND.GLOBAL_ATTRIBUTE15,FND_API.G_MISS_CHAR,NULL,p_delivery_rec.GLOBAL_ATTRIBUTE15),
2988 DECODE(p_delivery_rec.GLOBAL_ATTRIBUTE16,NULL,WND.GLOBAL_ATTRIBUTE16,FND_API.G_MISS_CHAR,NULL,p_delivery_rec.GLOBAL_ATTRIBUTE16),
2989 DECODE(p_delivery_rec.GLOBAL_ATTRIBUTE17,NULL,WND.GLOBAL_ATTRIBUTE17,FND_API.G_MISS_CHAR,NULL,p_delivery_rec.GLOBAL_ATTRIBUTE17),
2990 DECODE(p_delivery_rec.GLOBAL_ATTRIBUTE18,NULL,WND.GLOBAL_ATTRIBUTE18,FND_API.G_MISS_CHAR,NULL,p_delivery_rec.GLOBAL_ATTRIBUTE18),
2991 DECODE(p_delivery_rec.GLOBAL_ATTRIBUTE19,NULL,WND.GLOBAL_ATTRIBUTE19,FND_API.G_MISS_CHAR,NULL,p_delivery_rec.GLOBAL_ATTRIBUTE19),
2992 DECODE(p_delivery_rec.GLOBAL_ATTRIBUTE20,NULL,WND.GLOBAL_ATTRIBUTE20,FND_API.G_MISS_CHAR,NULL,p_delivery_rec.GLOBAL_ATTRIBUTE20),
2993 DECODE(p_delivery_rec.CREATION_DATE,NULL,WND.CREATION_DATE,FND_API.G_MISS_DATE,NULL,p_delivery_rec.CREATION_DATE),
2994 DECODE(p_delivery_rec.CREATED_BY,NULL,WND.CREATED_BY,FND_API.G_MISS_NUM,NULL,p_delivery_rec.CREATED_BY),
2995 DECODE(p_delivery_rec.LAST_UPDATE_DATE,NULL,WND.LAST_UPDATE_DATE,FND_API.G_MISS_DATE,NULL,p_delivery_rec.LAST_UPDATE_DATE),
2996 DECODE(p_delivery_rec.LAST_UPDATED_BY,NULL,WND.LAST_UPDATED_BY,FND_API.G_MISS_NUM,NULL,p_delivery_rec.LAST_UPDATED_BY),
2997 DECODE(p_delivery_rec.LAST_UPDATE_LOGIN,NULL,WND.LAST_UPDATE_LOGIN,FND_API.G_MISS_NUM,NULL,p_delivery_rec.LAST_UPDATE_LOGIN),
2998 DECODE(p_delivery_rec.PROGRAM_APPLICATION_ID,NULL,WND.PROGRAM_APPLICATION_ID,FND_API.G_MISS_NUM,NULL,p_delivery_rec.PROGRAM_APPLICATION_ID),
2999 DECODE(p_delivery_rec.PROGRAM_ID,NULL,WND.PROGRAM_ID,FND_API.G_MISS_NUM,NULL,p_delivery_rec.PROGRAM_ID),
3000 DECODE(p_delivery_rec.PROGRAM_UPDATE_DATE,NULL,WND.PROGRAM_UPDATE_DATE,FND_API.G_MISS_DATE,NULL,p_delivery_rec.PROGRAM_UPDATE_DATE),
3001 DECODE(p_delivery_rec.REQUEST_ID,NULL,WND.REQUEST_ID,FND_API.G_MISS_NUM,NULL,p_delivery_rec.REQUEST_ID),
3002 DECODE(p_delivery_rec.BATCH_ID,NULL,WND.BATCH_ID,FND_API.G_MISS_NUM,NULL,p_delivery_rec.BATCH_ID),
3003 DECODE(p_delivery_rec.HASH_VALUE,NULL,WND.HASH_VALUE,FND_API.G_MISS_NUM,NULL,p_delivery_rec.HASH_VALUE),
3004 DECODE(p_delivery_rec.SOURCE_HEADER_ID,NULL,WND.SOURCE_HEADER_ID,FND_API.G_MISS_NUM,NULL,p_delivery_rec.SOURCE_HEADER_ID),
3005 DECODE(p_delivery_rec.NUMBER_OF_LPN,NULL,WND.NUMBER_OF_LPN,FND_API.G_MISS_NUM,NULL,p_delivery_rec.NUMBER_OF_LPN),
3006 DECODE(p_delivery_rec.COD_AMOUNT,NULL,WND.COD_AMOUNT,FND_API.G_MISS_NUM,NULL,p_delivery_rec.COD_AMOUNT),
3007 DECODE(p_delivery_rec.COD_CURRENCY_CODE,NULL,WND.COD_CURRENCY_CODE,FND_API.G_MISS_CHAR,NULL,p_delivery_rec.COD_CURRENCY_CODE),
3008 DECODE(p_delivery_rec.COD_REMIT_TO,NULL,WND.COD_REMIT_TO,FND_API.G_MISS_CHAR,NULL,p_delivery_rec.COD_REMIT_TO),
3009 DECODE(p_delivery_rec.COD_CHARGE_PAID_BY,NULL,WND.COD_CHARGE_PAID_BY,FND_API.G_MISS_CHAR,NULL,p_delivery_rec.COD_CHARGE_PAID_BY),
3010 DECODE(p_delivery_rec.PROBLEM_CONTACT_REFERENCE,NULL,WND.PROBLEM_CONTACT_REFERENCE,FND_API.G_MISS_CHAR,NULL,p_delivery_rec.PROBLEM_CONTACT_REFERENCE),
3011 DECODE(p_delivery_rec.PORT_OF_LOADING,NULL,WND.PORT_OF_LOADING,FND_API.G_MISS_CHAR,NULL,p_delivery_rec.PORT_OF_LOADING),
3012 DECODE(p_delivery_rec.PORT_OF_DISCHARGE,NULL,WND.PORT_OF_DISCHARGE,FND_API.G_MISS_CHAR,NULL,p_delivery_rec.PORT_OF_DISCHARGE),
3013 DECODE(p_delivery_rec.FTZ_NUMBER,NULL,WND.FTZ_NUMBER,FND_API.G_MISS_CHAR,NULL,p_delivery_rec.FTZ_NUMBER),
3014 DECODE(p_delivery_rec.ROUTED_EXPORT_TXN,NULL,WND.ROUTED_EXPORT_TXN,FND_API.G_MISS_CHAR,NULL,p_delivery_rec.ROUTED_EXPORT_TXN),
3015 DECODE(p_delivery_rec.ENTRY_NUMBER,NULL,WND.ENTRY_NUMBER,FND_API.G_MISS_CHAR,NULL,p_delivery_rec.ENTRY_NUMBER),
3016 DECODE(p_delivery_rec.ROUTING_INSTRUCTIONS,NULL,WND.ROUTING_INSTRUCTIONS,FND_API.G_MISS_CHAR,NULL,p_delivery_rec.ROUTING_INSTRUCTIONS),
3017 DECODE(p_delivery_rec.IN_BOND_CODE,NULL,WND.IN_BOND_CODE,FND_API.G_MISS_CHAR,NULL,p_delivery_rec.IN_BOND_CODE),
3018 DECODE(p_delivery_rec.SHIPPING_MARKS,NULL,WND.SHIPPING_MARKS,FND_API.G_MISS_CHAR,NULL,p_delivery_rec.SHIPPING_MARKS),
3019 DECODE(p_delivery_rec.SERVICE_LEVEL,NULL,WND.SERVICE_LEVEL,FND_API.G_MISS_CHAR,NULL,p_delivery_rec.SERVICE_LEVEL),
3020 DECODE(p_delivery_rec.MODE_OF_TRANSPORT,NULL,WND.MODE_OF_TRANSPORT,FND_API.G_MISS_CHAR,NULL,p_delivery_rec.MODE_OF_TRANSPORT),
3021 DECODE(p_delivery_rec.ASSIGNED_TO_FTE_TRIPS,NULL,WND.ASSIGNED_TO_FTE_TRIPS,FND_API.G_MISS_CHAR,NULL,p_delivery_rec.ASSIGNED_TO_FTE_TRIPS),
3022 DECODE(p_delivery_rec.AUTO_SC_EXCLUDE_FLAG,NULL,WND.AUTO_SC_EXCLUDE_FLAG,FND_API.G_MISS_CHAR,NULL,p_delivery_rec.AUTO_SC_EXCLUDE_FLAG),
3023 DECODE(p_delivery_rec.AUTO_AP_EXCLUDE_FLAG,NULL,WND.AUTO_AP_EXCLUDE_FLAG,FND_API.G_MISS_CHAR,NULL,p_delivery_rec.AUTO_AP_EXCLUDE_FLAG),
3024 DECODE(p_delivery_rec.AP_BATCH_ID,NULL,WND.AP_BATCH_ID,FND_API.G_MISS_NUM,NULL,p_delivery_rec.AP_BATCH_ID),
3025 DECODE(p_delivery_rec.SHIPMENT_DIRECTION,NULL,WND.SHIPMENT_DIRECTION,FND_API.G_MISS_CHAR,NULL,p_delivery_rec.SHIPMENT_DIRECTION),
3026 DECODE(p_delivery_rec.VENDOR_ID,NULL,WND.VENDOR_ID,FND_API.G_MISS_NUM,NULL,p_delivery_rec.VENDOR_ID),
3027 DECODE(p_delivery_rec.PARTY_ID,NULL,WND.PARTY_ID,FND_API.G_MISS_NUM,NULL,p_delivery_rec.PARTY_ID),
3028 DECODE(p_delivery_rec.ROUTING_RESPONSE_ID,NULL,WND.ROUTING_RESPONSE_ID,FND_API.G_MISS_NUM,NULL,p_delivery_rec.ROUTING_RESPONSE_ID),
3029 DECODE(p_delivery_rec.RCV_SHIPMENT_HEADER_ID,NULL,WND.RCV_SHIPMENT_HEADER_ID,FND_API.G_MISS_NUM,NULL,p_delivery_rec.RCV_SHIPMENT_HEADER_ID),
3030 DECODE(p_delivery_rec.ASN_SHIPMENT_HEADER_ID,NULL,WND.ASN_SHIPMENT_HEADER_ID,FND_API.G_MISS_NUM,NULL,p_delivery_rec.ASN_SHIPMENT_HEADER_ID),
3031 DECODE(p_delivery_rec.SHIPPING_CONTROL,NULL,WND.SHIPPING_CONTROL,FND_API.G_MISS_CHAR,NULL,p_delivery_rec.SHIPPING_CONTROL),
3032 DECODE(p_delivery_rec.TP_DELIVERY_NUMBER,NULL,WND.TP_DELIVERY_NUMBER,FND_API.G_MISS_NUM,NULL,p_delivery_rec.TP_DELIVERY_NUMBER),
3033 DECODE(p_delivery_rec.EARLIEST_PICKUP_DATE,NULL,WND.EARLIEST_PICKUP_DATE,FND_API.G_MISS_DATE,NULL,p_delivery_rec.EARLIEST_PICKUP_DATE),
3034 DECODE(p_delivery_rec.LATEST_PICKUP_DATE,NULL,WND.LATEST_PICKUP_DATE,FND_API.G_MISS_DATE,NULL,p_delivery_rec.LATEST_PICKUP_DATE),
3035 DECODE(p_delivery_rec.EARLIEST_DROPOFF_DATE,NULL,WND.EARLIEST_DROPOFF_DATE,FND_API.G_MISS_DATE,NULL,p_delivery_rec.EARLIEST_DROPOFF_DATE),
3036 DECODE(p_delivery_rec.LATEST_DROPOFF_DATE,NULL,WND.LATEST_DROPOFF_DATE,FND_API.G_MISS_DATE,NULL,p_delivery_rec.LATEST_DROPOFF_DATE),
3037 DECODE(p_delivery_rec.IGNORE_FOR_PLANNING,NULL,WND.IGNORE_FOR_PLANNING,FND_API.G_MISS_CHAR,NULL,p_delivery_rec.IGNORE_FOR_PLANNING),
3038 DECODE(p_delivery_rec.TP_PLAN_NAME,NULL,WND.TP_PLAN_NAME,FND_API.G_MISS_CHAR,NULL,p_delivery_rec.TP_PLAN_NAME),
3039 DECODE(p_delivery_rec.hash_string,NULL,WND.hash_string,FND_API.G_MISS_CHAR,NULL,p_delivery_rec.hash_string),
3040 DECODE(p_delivery_rec.delivered_date,NULL,WND.delivered_date,FND_API.G_MISS_DATE,NULL,p_delivery_rec.delivered_date),
3041 -- bug 3667348
3042 DECODE(p_delivery_rec.REASON_OF_TRANSPORT,NULL,WND.REASON_OF_TRANSPORT,FND_API.G_MISS_CHAR,NULL,p_delivery_rec.REASON_OF_TRANSPORT),
3043 DECODE(p_delivery_rec.DESCRIPTION,NULL,WND.DESCRIPTION,FND_API.G_MISS_CHAR,NULL,p_delivery_rec.DESCRIPTION),
3044 DECODE(p_copy_legs, 'Y', WND.ITINERARY_COMPLETE, 'N'),
3045 -- bug 3667348
3046 --OTM R12
3047 DECODE(p_delivery_rec.TMS_INTERFACE_FLAG,NULL,WND.TMS_INTERFACE_FLAG,FND_API.G_MISS_CHAR,NULL,p_delivery_rec.TMS_INTERFACE_FLAG),
3048 DECODE(p_delivery_rec.TMS_VERSION_NUMBER,NULL,WND.TMS_VERSION_NUMBER,FND_API.G_MISS_NUM,NULL,p_delivery_rec.TMS_VERSION_NUMBER)
3049 --
3050 FROM WSH_NEW_DELIVERIES WND
3051 WHERE delivery_id = p_delivery_id;
3052 --
3053 --
3054 IF SQL%ROWCOUNT = 0
3055 THEN
3056 FND_MESSAGE.SET_NAME('WSH','WSH_DLVY_NOT_EXIST');
3057 FND_MESSAGE.SET_TOKEN('DELIVERY_ID', p_delivery_id);
3058 WSH_UTIL_CORE.add_message (wsh_util_core.g_ret_sts_error,l_module_name);
3059 --
3060 RAISE FND_API.G_EXC_ERROR;
3061 END IF;
3062 --
3063 --
3064 IF l_debug_on THEN
3065 WSH_DEBUG_SV.log(l_module_name, 'l_delivery_id', l_delivery_id );
3066 END IF;
3067 --
3068 --
3069 OPEN dlvy_csr(l_delivery_id);
3070 FETCH dlvy_csr INTO x_rowid;
3071 CLOSE dlvy_csr;
3072 --
3073 --
3074 IF p_copy_legs = 'Y'
3075 THEN
3076 --{
3077 INSERT INTO wsh_delivery_legs
3078 (
3079 DELIVERY_LEG_ID,
3080 DELIVERY_ID,
3081 SEQUENCE_NUMBER,
3082 LOADING_ORDER_FLAG,
3083 PICK_UP_STOP_ID,
3084 DROP_OFF_STOP_ID,
3085 GROSS_WEIGHT,
3086 NET_WEIGHT,
3087 WEIGHT_UOM_CODE,
3088 VOLUME,
3089 VOLUME_UOM_CODE,
3090 CREATION_DATE,
3091 CREATED_BY,
3092 LAST_UPDATE_DATE,
3093 LAST_UPDATED_BY,
3094 LAST_UPDATE_LOGIN,
3095 PROGRAM_APPLICATION_ID,
3096 PROGRAM_ID,
3097 PROGRAM_UPDATE_DATE,
3098 REQUEST_ID,
3099 LOAD_TENDER_STATUS,
3100 SHIPPER_TITLE,
3101 SHIPPER_PHONE,
3102 POD_FLAG,
3103 POD_BY,
3104 POD_DATE,
3105 EXPECTED_POD_DATE,
3106 BOOKING_OFFICE,
3107 SHIPPER_EXPORT_REF,
3108 CARRIER_EXPORT_REF,
3109 DOC_NOTIFY_PARTY,
3110 AETC_NUMBER,
3111 SHIPPER_SIGNED_BY,
3112 SHIPPER_DATE,
3113 CARRIER_SIGNED_BY,
3114 CARRIER_DATE,
3115 DOC_ISSUE_OFFICE,
3116 DOC_ISSUED_BY,
3117 DOC_DATE_ISSUED,
3118 SHIPPER_HM_BY,
3119 SHIPPER_HM_DATE,
3120 CARRIER_HM_BY,
3121 CARRIER_HM_DATE,
3122 BOOKING_NUMBER,
3123 PORT_OF_LOADING,
3124 PORT_OF_DISCHARGE,
3125 SERVICE_CONTRACT,
3126 BILL_FREIGHT_TO,
3127 FTE_TRIP_ID,
3128 REPRICE_REQUIRED,
3129 ACTUAL_ARRIVAL_DATE,
3130 ACTUAL_DEPARTURE_DATE,
3131 ACTUAL_RECEIPT_DATE,
3132 TRACKING_DRILLDOWN_FLAG,
3133 STATUS_CODE,
3134 TRACKING_REMARKS,
3135 CARRIER_EST_DEPARTURE_DATE,
3136 CARRIER_EST_ARRIVAL_DATE,
3137 LOADING_START_DATETIME,
3138 LOADING_END_DATETIME,
3139 UNLOADING_START_DATETIME,
3140 UNLOADING_END_DATETIME,
3141 DELIVERED_QUANTITY,
3142 LOADED_QUANTITY,
3143 RECEIVED_QUANTITY,
3144 ORIGIN_STOP_ID,
3145 DESTINATION_STOP_ID
3146 )
3147 SELECT
3148 wsh_delivery_legs_s.NEXTVAL,
3149 l_delivery_id,
3150 SEQUENCE_NUMBER,
3151 LOADING_ORDER_FLAG,
3152 PICK_UP_STOP_ID,
3153 DROP_OFF_STOP_ID,
3154 GROSS_WEIGHT,
3155 NET_WEIGHT,
3156 WEIGHT_UOM_CODE,
3157 VOLUME,
3158 VOLUME_UOM_CODE,
3159 CREATION_DATE,
3160 CREATED_BY,
3161 LAST_UPDATE_DATE,
3162 LAST_UPDATED_BY,
3163 LAST_UPDATE_LOGIN,
3164 PROGRAM_APPLICATION_ID,
3165 PROGRAM_ID,
3166 PROGRAM_UPDATE_DATE,
3167 REQUEST_ID,
3168 LOAD_TENDER_STATUS,
3169 SHIPPER_TITLE,
3170 SHIPPER_PHONE,
3171 POD_FLAG,
3172 POD_BY,
3173 POD_DATE,
3174 EXPECTED_POD_DATE,
3175 BOOKING_OFFICE,
3176 SHIPPER_EXPORT_REF,
3177 CARRIER_EXPORT_REF,
3178 DOC_NOTIFY_PARTY,
3179 AETC_NUMBER,
3180 SHIPPER_SIGNED_BY,
3181 SHIPPER_DATE,
3182 CARRIER_SIGNED_BY,
3183 CARRIER_DATE,
3184 DOC_ISSUE_OFFICE,
3185 DOC_ISSUED_BY,
3186 DOC_DATE_ISSUED,
3187 SHIPPER_HM_BY,
3188 SHIPPER_HM_DATE,
3189 CARRIER_HM_BY,
3190 CARRIER_HM_DATE,
3191 BOOKING_NUMBER,
3192 PORT_OF_LOADING,
3193 PORT_OF_DISCHARGE,
3194 SERVICE_CONTRACT,
3195 BILL_FREIGHT_TO,
3196 FTE_TRIP_ID,
3197 REPRICE_REQUIRED,
3198 ACTUAL_ARRIVAL_DATE,
3199 ACTUAL_DEPARTURE_DATE,
3200 ACTUAL_RECEIPT_DATE,
3201 TRACKING_DRILLDOWN_FLAG,
3202 STATUS_CODE,
3203 TRACKING_REMARKS,
3204 CARRIER_EST_DEPARTURE_DATE,
3205 CARRIER_EST_ARRIVAL_DATE,
3206 LOADING_START_DATETIME,
3207 LOADING_END_DATETIME,
3208 UNLOADING_START_DATETIME,
3209 UNLOADING_END_DATETIME,
3210 DELIVERED_QUANTITY,
3211 LOADED_QUANTITY,
3212 RECEIVED_QUANTITY,
3213 ORIGIN_STOP_ID,
3214 DESTINATION_STOP_ID
3215 FROM WSH_DELIVERY_LEGS
3216 WHERE DELIVERY_ID = p_delivery_id;
3217 --
3218 IF l_debug_on THEN
3219 WSH_DEBUG_SV.logmsg(l_module_name, 'Inserted ' || SQL%ROWCOUNT || ' Legs' );
3220 END IF;
3221 --
3222 OPEN leg_csr(l_delivery_id);
3223 FETCH leg_csr BULK COLLECT INTO x_leg_id_tab;
3224 CLOSE leg_csr;
3225 --
3226 --
3227 --}
3228 END IF;
3229 --
3230 x_delivery_id := l_delivery_id;
3231 --
3232 IF l_debug_on THEN
3233 WSH_DEBUG_SV.pop(l_module_name);
3234 END IF;
3235 --
3236 --}
3237 EXCEPTION
3238 WHEN FND_API.G_EXC_ERROR THEN
3239
3240 x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
3241 --
3242 IF l_debug_on THEN
3243 WSH_DEBUG_SV.logmsg(l_module_name,'FND_API.G_EXC_ERROR exception has occured ',WSH_DEBUG_SV.C_EXCEP_LEVEL);
3244 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:FND_API.G_EXC_ERROR');
3245 END IF;
3246 WHEN others THEN
3247 x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR;
3248 wsh_util_core.default_handler('WSH_NEW_DELIVERIES_PVT.CLONE',l_module_name);
3249 --
3250 IF l_debug_on THEN
3251 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:OTHERS');
3252 END IF;
3253 --
3254 END CLONE;
3255 -- J-IB-NPARIKH-}
3256
3257 -- Bug 3292364
3258 -- Procedure: Table_To_Record
3259 -- Parameters: x_delivery_rec: A record of all attributes of a Delivery Record
3260 -- p_delivery_id : delivery_id of the delivery that is to be copied
3261 -- Description: This procedure will copy the attributes of a delivery in wsh_new_deliveries
3262 -- and copy it to a record.
3263
3264 PROCEDURE Table_to_Record (p_delivery_id IN NUMBER,
3265 x_delivery_rec OUT NOCOPY WSH_NEW_DELIVERIES_PVT.Delivery_Rec_Type,
3266 x_return_status OUT NOCOPY VARCHAR2) IS
3267
3268 CURSOR c_tbl_rec (p_delivery_id in NUMBER) IS
3269 SELECT DELIVERY_ID
3270 ,NAME
3271 ,PLANNED_FLAG
3272 ,STATUS_CODE
3273 ,DELIVERY_TYPE
3274 ,LOADING_SEQUENCE
3275 ,LOADING_ORDER_FLAG
3276 ,INITIAL_PICKUP_DATE
3277 ,INITIAL_PICKUP_LOCATION_ID
3278 ,ORGANIZATION_ID
3279 ,ULTIMATE_DROPOFF_LOCATION_ID
3280 ,ULTIMATE_DROPOFF_DATE
3281 ,CUSTOMER_ID
3282 ,INTMED_SHIP_TO_LOCATION_ID
3283 ,POOLED_SHIP_TO_LOCATION_ID
3284 ,CARRIER_ID
3285 ,SHIP_METHOD_CODE
3286 ,FREIGHT_TERMS_CODE
3287 ,FOB_CODE
3288 ,FOB_LOCATION_ID
3289 ,WAYBILL
3290 ,DOCK_CODE
3291 ,ACCEPTANCE_FLAG
3292 ,ACCEPTED_BY
3293 ,ACCEPTED_DATE
3294 ,ACKNOWLEDGED_BY
3295 ,CONFIRMED_BY
3296 ,CONFIRM_DATE
3297 ,ASN_DATE_SENT
3298 ,ASN_STATUS_CODE
3299 ,ASN_SEQ_NUMBER
3300 ,GROSS_WEIGHT
3301 ,NET_WEIGHT
3302 ,WEIGHT_UOM_CODE
3303 ,VOLUME
3304 ,VOLUME_UOM_CODE
3305 ,ADDITIONAL_SHIPMENT_INFO
3306 ,CURRENCY_CODE
3307 ,ATTRIBUTE_CATEGORY
3308 ,ATTRIBUTE1
3309 ,ATTRIBUTE2
3310 ,ATTRIBUTE3
3311 ,ATTRIBUTE4
3312 ,ATTRIBUTE5
3313 ,ATTRIBUTE6
3314 ,ATTRIBUTE7
3315 ,ATTRIBUTE8
3316 ,ATTRIBUTE9
3317 ,ATTRIBUTE10
3318 ,ATTRIBUTE11
3319 ,ATTRIBUTE12
3320 ,ATTRIBUTE13
3321 ,ATTRIBUTE14
3322 ,ATTRIBUTE15
3323 ,TP_ATTRIBUTE_CATEGORY
3324 ,TP_ATTRIBUTE1
3325 ,TP_ATTRIBUTE2
3326 ,TP_ATTRIBUTE3
3327 ,TP_ATTRIBUTE4
3328 ,TP_ATTRIBUTE5
3329 ,TP_ATTRIBUTE6
3330 ,TP_ATTRIBUTE7
3331 ,TP_ATTRIBUTE8
3332 ,TP_ATTRIBUTE9
3333 ,TP_ATTRIBUTE10
3334 ,TP_ATTRIBUTE11
3335 ,TP_ATTRIBUTE12
3336 ,TP_ATTRIBUTE13
3337 ,TP_ATTRIBUTE14
3338 ,TP_ATTRIBUTE15
3339 ,GLOBAL_ATTRIBUTE_CATEGORY
3340 ,GLOBAL_ATTRIBUTE1
3341 ,GLOBAL_ATTRIBUTE2
3342 ,GLOBAL_ATTRIBUTE3
3343 ,GLOBAL_ATTRIBUTE4
3344 ,GLOBAL_ATTRIBUTE5
3345 ,GLOBAL_ATTRIBUTE6
3346 ,GLOBAL_ATTRIBUTE7
3347 ,GLOBAL_ATTRIBUTE8
3348 ,GLOBAL_ATTRIBUTE9
3349 ,GLOBAL_ATTRIBUTE10
3350 ,GLOBAL_ATTRIBUTE11
3351 ,GLOBAL_ATTRIBUTE12
3352 ,GLOBAL_ATTRIBUTE13
3353 ,GLOBAL_ATTRIBUTE14
3354 ,GLOBAL_ATTRIBUTE15
3355 ,GLOBAL_ATTRIBUTE16
3356 ,GLOBAL_ATTRIBUTE17
3357 ,GLOBAL_ATTRIBUTE18
3358 ,GLOBAL_ATTRIBUTE19
3359 ,GLOBAL_ATTRIBUTE20
3360 ,CREATION_DATE
3361 ,CREATED_BY
3362 ,sysdate
3363 ,FND_GLOBAL.USER_ID
3364 ,FND_GLOBAL.LOGIN_ID
3365 ,PROGRAM_APPLICATION_ID
3366 ,PROGRAM_ID
3367 ,PROGRAM_UPDATE_DATE
3368 ,REQUEST_ID
3369 ,BATCH_ID
3370 ,HASH_VALUE
3371 ,SOURCE_HEADER_ID
3372 ,NUMBER_OF_LPN
3373 ,COD_AMOUNT
3374 ,COD_CURRENCY_CODE
3375 ,COD_REMIT_TO
3376 ,COD_CHARGE_PAID_BY
3377 ,PROBLEM_CONTACT_REFERENCE
3378 ,PORT_OF_LOADING
3379 ,PORT_OF_DISCHARGE
3380 ,FTZ_NUMBER
3381 ,ROUTED_EXPORT_TXN
3382 ,ENTRY_NUMBER
3383 ,ROUTING_INSTRUCTIONS
3384 ,IN_BOND_CODE
3385 ,SHIPPING_MARKS
3386 ,SERVICE_LEVEL
3387 ,MODE_OF_TRANSPORT
3388 ,ASSIGNED_TO_FTE_TRIPS
3389 ,AUTO_SC_EXCLUDE_FLAG
3390 ,AUTO_AP_EXCLUDE_FLAG
3391 ,AP_BATCH_ID
3392 -- The following are non database columns in the rec. structure.
3393 ,NULL -- ROWID
3394 ,NULL -- LOADING_ORDER_DESC
3395 ,NULL -- ORGANIZATION_CODE
3396 ,NULL -- ULTIMATE_DROPOFF_LOCATION_CODE
3397 ,NULL -- INITIAL_PICKUP_LOCATION_CODE
3398 ,NULL -- CUSTOMER_NUMBER
3399 ,NULL -- INTMED_SHIP_TO_LOCATION_CODE
3400 ,NULL -- POOLED_SHIP_TO_LOCATION_CODE
3401 ,NULL -- CARRIER_CODE
3402 ,NULL -- SHIP_METHOD_NAME
3403 ,NULL -- FREIGHT_TERMS_NAME
3404 ,NULL -- FOB_NAME
3405 ,NULL -- FOB_LOCATION_CODE
3406 ,NULL -- WEIGHT_UOM_DESC
3407 ,NULL -- VOLUME_UOM_DESC
3408 ,NULL -- CURRENCY_NAME
3409 -- End non database columns in the rec. structure.
3410 ,SHIPMENT_DIRECTION
3411 ,VENDOR_ID
3412 ,PARTY_ID
3413 ,ROUTING_RESPONSE_ID
3414 ,RCV_SHIPMENT_HEADER_ID
3415 ,ASN_SHIPMENT_HEADER_ID
3416 ,SHIPPING_CONTROL
3417 ,TP_DELIVERY_NUMBER
3418 ,EARLIEST_PICKUP_DATE
3419 ,LATEST_PICKUP_DATE
3420 ,EARLIEST_DROPOFF_DATE
3421 ,LATEST_DROPOFF_DATE
3422 ,nvl(IGNORE_FOR_PLANNING, 'N')
3423 ,TP_PLAN_NAME
3424 ,WV_FROZEN_FLAG
3425 ,HASH_STRING
3426 ,DELIVERED_DATE
3427 -- Non database column
3428 ,NULL -- packing_slip
3429 -- bug 3667348
3430 ,REASON_OF_TRANSPORT
3431 ,DESCRIPTION
3432 -- bug 3667348
3433 ,'N'--Non Database field added for "Proration of weight from Delivery to delivery lines" Project(Bug#4254552).
3434 --OTM R12
3435 ,TMS_INTERFACE_FLAG
3436 ,TMS_VERSION_NUMBER
3437 --
3438 FROM wsh_new_deliveries
3439 WHERE delivery_id = p_delivery_id;
3440
3441 l_debug_on BOOLEAN;
3442 --
3443 l_module_name CONSTANT VARCHAR2(100) := 'wsh.plsql.' || G_PKG_NAME || '.' || 'Table_To_Record';
3444
3445
3446 BEGIN
3447
3448 l_debug_on := WSH_DEBUG_INTERFACE.g_debug;
3449 --
3450 IF l_debug_on IS NULL
3451 THEN
3452 l_debug_on := WSH_DEBUG_SV.is_debug_enabled;
3453 END IF;
3454 --
3455 IF l_debug_on THEN
3456 WSH_DEBUG_SV.push(l_module_name);
3457 WSH_DEBUG_SV.log(l_module_name,'p_delivery_id', p_delivery_id);
3458 END IF;
3459 --
3460
3461 OPEN c_tbl_rec (p_delivery_id);
3462 FETCH c_tbl_rec INTO x_delivery_rec;
3463 IF c_tbl_rec%NOTFOUND THEN
3464 --
3465 CLOSE c_tbl_rec;
3466 RAISE no_data_found;
3467 --
3468 END IF;
3469 CLOSE c_tbl_rec;
3470 IF l_debug_on THEN
3471 WSH_DEBUG_SV.pop(l_module_name);
3472 END IF;
3473 x_return_status := wsh_util_core.g_ret_sts_success;
3474
3475 EXCEPTION
3476
3477 WHEN OTHERS THEN
3478 IF c_tbl_rec%ISOPEN THEN
3479 CLOSE c_tbl_rec;
3480 END IF;
3481 --
3482 x_return_status := wsh_util_core.g_ret_sts_unexp_error;
3483 --
3484 wsh_util_core.default_handler('WSH_NEW_DELIVERIES_PVT.Table_to_Record',l_module_name);
3485 --
3486 IF l_debug_on THEN
3487 WSH_DEBUG_SV.logmsg(l_module_name,'Unexpected error has occured. Oracle error message is '|| SQLERRM,WSH_DEBUG_SV.C_UNEXPEC_ERR_LEVEL);
3488 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:OTHERS');
3489 END IF;
3490 --
3491
3492 END Table_to_Record;
3493
3494
3495 --OTM R12
3496 ----------------------------------------------------------
3497 -- PROCEDURE UPDATE_TMS_INTERFACE_FLAG
3498 --
3499 -- parameters: p_delivery_id_tab table of delivery ids to update
3500 -- p_tms_interface_flag_tab table of the interface_flag
3501 -- for the delivery to set to
3502 -- x_return_status return status
3503 --
3504 -- description: This procedure updates the delivery's tms_interface_flag
3505 -- to the according flag in the p_tms_interface_flag_tab.
3506 -- Also calls LOG_OTM_EXCEPTION.
3507 ----------------------------------------------------------
3508 PROCEDURE UPDATE_TMS_INTERFACE_FLAG
3509 (p_delivery_id_tab IN WSH_UTIL_CORE.ID_TAB_TYPE,
3510 p_tms_interface_flag_tab IN WSH_UTIL_CORE.COLUMN_TAB_TYPE,
3511 x_return_status OUT NOCOPY VARCHAR2) IS
3512
3513 l_num_error NUMBER;
3514 l_num_warn NUMBER;
3515 l_return_status VARCHAR2(1);
3516 l_delivery_info_tab WSH_NEW_DELIVERIES_PVT.Delivery_Attr_Tbl_Type;
3517 l_delivery_info WSH_NEW_DELIVERIES_PVT.Delivery_Rec_Type;
3518
3519 l_new_tms_interface_flag_tab WSH_UTIL_CORE.COLUMN_TAB_TYPE;
3520 l_new_tms_version_number_tab WSH_UTIL_CORE.ID_TAB_TYPE;
3521 l_delivery_id_tab WSH_UTIL_CORE.ID_TAB_TYPE;
3522 l_trip_not_found VARCHAR2(1);
3523 l_trip_info_rec WSH_DELIVERY_VALIDATIONS.trip_info_rec_type;
3524 i NUMBER;
3525 l_count NUMBER;
3526 l_gc3_is_installed VARCHAR2(1);
3527 RECORD_LOCKED EXCEPTION;
3528 PRAGMA EXCEPTION_INIT(RECORD_LOCKED, -54);
3529
3530 l_debug_on BOOLEAN;
3531
3532 l_module_name CONSTANT VARCHAR2(100) := 'wsh.plsql.' || G_PKG_NAME || '.' || 'UPDATE_TMS_INTERFACE_FLAG';
3533
3534 BEGIN
3535 --
3536 --
3537 l_debug_on := WSH_DEBUG_INTERFACE.g_debug;
3538 --
3539 IF l_debug_on IS NULL THEN
3540 l_debug_on := WSH_DEBUG_SV.is_debug_enabled;
3541 END IF;
3542 --
3543 IF l_debug_on THEN
3544 WSH_DEBUG_SV.push(l_module_name);
3545 WSH_DEBUG_SV.log(l_module_name,'Delivery ID count', p_delivery_id_tab.COUNT);
3546 WSH_DEBUG_SV.log(l_module_name,'Interface Flag count', p_tms_interface_flag_tab.COUNT);
3547 END IF;
3548 --
3549 x_return_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
3550
3551 l_gc3_is_installed := WSH_UTIL_CORE.G_GC3_IS_INSTALLED; -- this is global variable
3552
3553 IF l_gc3_is_installed IS NULL THEN
3554 l_gc3_is_installed := WSH_UTIL_CORE.GC3_IS_INSTALLED; -- this is actual function
3555 END IF;
3556
3557 l_num_warn := 0;
3558 l_num_error := 0;
3559 i := 0;
3560 l_count := 0;
3561
3562 SAVEPOINT tms_update;
3563
3564 IF (p_delivery_id_tab.COUNT <> p_tms_interface_flag_tab.COUNT) THEN
3565 IF l_debug_on THEN
3566 WSH_DEBUG_SV.logmsg(l_module_name,'Delivery ID and TMS_interface_flag_tab count does not match');
3567 END IF;
3568 RAISE FND_API.G_EXC_ERROR;
3569 END IF;
3570
3571 IF (p_delivery_id_tab.COUNT > 0
3572 AND l_gc3_is_installed = 'Y') THEN
3573
3574 i := p_delivery_id_tab.FIRST;
3575
3576 WHILE i IS NOT NULL LOOP
3577
3578 l_trip_not_found := 'N';
3579
3580 --get trip information for delivery, no update when trip not OPEN
3581 IF l_debug_on THEN
3582 WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_DELIVERY_VALIDATIONS.GET_TRIP_INFORMATION',WSH_DEBUG_SV.C_PROC_LEVEL);
3583 END IF;
3584 WSH_DELIVERY_VALIDATIONS.get_trip_information
3585 (p_delivery_id => p_delivery_id_tab(i),
3586 x_trip_info_rec => l_trip_info_rec,
3587 x_return_status => l_return_status);
3588
3589 IF l_debug_on THEN
3590 WSH_DEBUG_SV.log(l_module_name,'Return status after calling WSH_DELIVERY_VALIDATIONS.GET_TRIP_INFORMATION',l_return_status);
3591 END IF;
3592
3593 IF (l_return_status IN (WSH_UTIL_CORE.G_RET_STS_ERROR, WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR)) THEN
3594 IF l_debug_on THEN
3595 WSH_DEBUG_SV.logmsg(l_module_name,'Delivery ' || p_delivery_id_tab(i) || ' failed during get_trip_information');
3596 END IF;
3597 l_num_error := l_num_error + 1;
3598 EXIT;
3599 ELSIF (l_return_status = WSH_UTIL_CORE.G_RET_STS_WARNING) THEN
3600 l_num_warn := l_num_warn + 1;
3601 END IF;
3602
3603 IF (l_trip_not_found = 'N' AND l_trip_info_rec.trip_id IS NULL) THEN
3604 l_trip_not_found := 'Y';
3605 END IF;
3606
3607 -- only do changes when there's no trip or trip status is OPEN
3608 -- if trip is found closed, should be actual shipment update
3609 --Bug 7408338 Trip in status 'In-Transit' should also get processed.
3610 IF (l_trip_info_rec.status_code IN ('OP','IT','CL') OR l_trip_not_found = 'Y') THEN
3611
3612 IF l_debug_on THEN
3613 WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_DELIVERY_VALIDATIONS.get_delivery_information',WSH_DEBUG_SV.C_PROC_LEVEL);
3614 END IF;
3615
3616 WSH_DELIVERY_VALIDATIONS.get_delivery_information(
3617 p_delivery_id => p_delivery_id_tab(i),
3618 x_delivery_rec => l_delivery_info,
3619 x_return_status => l_return_status);
3620
3621 IF l_debug_on THEN
3622 WSH_DEBUG_SV.log(l_module_name,'Return status after calling WSH_DELIVERY_VALIDATIONS.get_delivery_information',l_return_status);
3623 END IF;
3624
3625 IF (l_return_status IN (WSH_UTIL_CORE.G_RET_STS_ERROR, WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR)) THEN
3626 IF l_debug_on THEN
3627 WSH_DEBUG_SV.logmsg(l_module_name,'Delivery ' || p_delivery_id_tab(i) || ' failed during table_to_record');
3628 END IF;
3629 l_num_error := l_num_error + 1;
3630 EXIT;
3631 ELSIF (l_return_status = WSH_UTIL_CORE.G_RET_STS_WARNING) THEN
3632 l_num_warn := l_num_warn + 1;
3633 END IF;
3634
3635 l_delivery_info_tab(l_delivery_info_tab.COUNT+1) := l_delivery_info;
3636 l_count := l_delivery_info_tab.COUNT;
3637 l_delivery_id_tab(l_count) := l_delivery_info.delivery_id;
3638
3639 -- we only increment the version number on interface_flag when
3640 -- the interface_flag is changed from other status to DR or UR or CR
3641 IF (p_tms_interface_flag_tab(i) IS NULL) THEN
3642 --assume regular update
3643 IF (l_delivery_info.tms_interface_flag IN
3644 (WSH_NEW_DELIVERIES_PVT.C_TMS_CREATE_IN_PROCESS,
3645 WSH_NEW_DELIVERIES_PVT.C_TMS_AWAITING_ANSWER,
3646 WSH_NEW_DELIVERIES_PVT.C_TMS_UPDATE_IN_PROCESS,
3647 WSH_NEW_DELIVERIES_PVT.C_TMS_ANSWER_RECEIVED)) THEN
3648 l_new_tms_interface_flag_tab(l_count) := WSH_NEW_DELIVERIES_PVT.C_TMS_UPDATE_REQUIRED;
3649 l_new_tms_version_number_tab(l_count) := NVL(l_delivery_info.tms_version_number, 1) + 1;
3650 ELSE
3651 l_new_tms_interface_flag_tab(l_count) := NVL(l_delivery_info.tms_interface_flag, WSH_NEW_DELIVERIES_PVT.C_TMS_NOT_TO_BE_SENT);
3652 l_new_tms_version_number_tab(l_count) := NVL(l_delivery_info.tms_version_number, 1);
3653 END IF;
3654 ELSIF (p_tms_interface_flag_tab(i) = WSH_NEW_DELIVERIES_PVT.C_TMS_DELETE_REQUIRED
3655 AND l_delivery_info.tms_interface_flag IN
3656 (WSH_NEW_DELIVERIES_PVT.C_TMS_CREATE_REQUIRED,
3657 WSH_NEW_DELIVERIES_PVT.C_TMS_NOT_TO_BE_SENT)) THEN
3658 --set to NS if previous flag is CR or NS and new flag is DR, CP might already be sent so set to DR
3659 l_new_tms_interface_flag_tab(l_count) := WSH_NEW_DELIVERIES_PVT.C_TMS_NOT_TO_BE_SENT;
3660 l_new_tms_version_number_tab(l_count) := NVL(l_delivery_info.tms_version_number, 1);
3661 ELSIF (p_tms_interface_flag_tab(i) = WSH_NEW_DELIVERIES_PVT.C_TMS_DELETE_REQUIRED
3662 AND l_delivery_info.tms_interface_flag = WSH_NEW_DELIVERIES_PVT.C_TMS_DELETE_IN_PROCESS) THEN
3663 --DP stays in DP
3664 l_new_tms_interface_flag_tab(l_count) := l_delivery_info.tms_interface_flag;
3665 l_new_tms_version_number_tab(l_count) := NVL(l_delivery_info.tms_version_number, 1);
3666 ELSIF (p_tms_interface_flag_tab(i) IN
3667 (WSH_NEW_DELIVERIES_PVT.C_TMS_NOT_TO_BE_SENT,
3668 WSH_NEW_DELIVERIES_PVT.C_TMS_AWAITING_ANSWER,
3669 WSH_NEW_DELIVERIES_PVT.C_TMS_CREATE_IN_PROCESS,
3670 WSH_NEW_DELIVERIES_PVT.C_TMS_UPDATE_IN_PROCESS,
3671 WSH_NEW_DELIVERIES_PVT.C_TMS_DELETE_IN_PROCESS,
3672 WSH_NEW_DELIVERIES_PVT.C_TMS_ANSWER_RECEIVED,
3673 WSH_NEW_DELIVERIES_PVT.C_TMS_COMPLETED)
3674 OR p_tms_interface_flag_tab(i) = l_delivery_info.tms_interface_flag) THEN
3675 --all updates that does not change tms interface flag or are changing to anything besides UR CR DR,
3676 --do not increment the version
3677 l_new_tms_interface_flag_tab(l_count) := p_tms_interface_flag_tab(i);
3678 l_new_tms_version_number_tab(l_count) := NVL(l_delivery_info.tms_version_number, 1);
3679 ELSE
3680 l_new_tms_interface_flag_tab(l_count) := p_tms_interface_flag_tab(i);
3681 l_new_tms_version_number_tab(l_count) := NVL(l_delivery_info.tms_version_number, 1)+1;
3682 END IF;
3683
3684 IF l_debug_on THEN
3685 WSH_DEBUG_SV.log(l_module_name, 'p interface flag for '|| i, p_tms_interface_flag_tab(i));
3686 WSH_DEBUG_SV.log(l_module_name, 'new interface flag for '|| i, l_new_tms_interface_flag_tab(l_count));
3687 WSH_DEBUG_SV.log(l_module_name, 'new version number for '|| i, l_new_tms_version_number_tab(l_count));
3688 END IF;
3689
3690 END IF;
3691
3692 i := p_delivery_id_tab.NEXT(i);
3693 END LOOP;
3694 -- end of while loop
3695
3696 IF l_num_error > 0 THEN
3697 x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
3698 ELSIF l_num_warn > 0 THEN
3699 x_return_status := WSH_UTIL_CORE.G_RET_STS_WARNING;
3700 ELSE
3701 x_return_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
3702 END IF;
3703
3704 IF l_debug_on THEN
3705 WSH_DEBUG_SV.log(l_module_name, 'x_return_status', x_return_status);
3706 END IF;
3707
3708 --proceed with update if not error status
3709 IF (x_return_status IN (WSH_UTIL_CORE.G_RET_STS_SUCCESS, WSH_UTIL_CORE.G_RET_STS_WARNING)
3710 AND (l_delivery_info_tab.COUNT > 0)) THEN
3711
3712 FORALL j IN l_delivery_info_tab.FIRST..l_delivery_info_tab.LAST
3713 UPDATE wsh_new_deliveries
3714 SET
3715 TMS_VERSION_NUMBER = l_new_tms_version_number_tab(j)
3716 ,TMS_INTERFACE_FLAG = l_new_tms_interface_flag_tab(j)
3717 ,last_update_date = SYSDATE
3718 ,last_updated_by = FND_GLOBAL.USER_ID
3719 ,last_update_login = FND_GLOBAL.LOGIN_ID
3720 WHERE DELIVERY_ID = l_delivery_id_tab(j);
3721
3722 IF l_debug_on THEN
3723 WSH_DEBUG_SV.log(l_module_name,'Rows updated',SQL%ROWCOUNT);
3724 WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_XC_UTIL.LOG_OTM_EXCEPTION',WSH_DEBUG_SV.C_PROC_LEVEL);
3725 END IF;
3726
3727 WSH_XC_UTIL.log_otm_exception(
3728 p_delivery_info_tab => l_delivery_info_tab,
3729 p_new_interface_flag_tab => l_new_tms_interface_flag_tab,
3730 x_return_status => l_return_status);
3731
3732 IF l_debug_on THEN
3733 WSH_DEBUG_SV.log(l_module_name,'Return status after calling WSH_XC_UTIL.LOG_OTM_EXCEPTION',l_return_status);
3734 END IF;
3735
3736 IF (l_return_status = WSH_UTIL_CORE.G_RET_STS_ERROR) THEN
3737 IF l_debug_on THEN
3738 WSH_DEBUG_SV.logmsg(l_module_name,'ERROR: WSH_XC_UTIL.log_otm_exception failed');
3739 END IF;
3740 RAISE FND_API.G_EXC_ERROR;
3741 ELSIF (l_return_status = WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR) THEN
3742 IF l_debug_on THEN
3743 WSH_DEBUG_SV.logmsg(l_module_name,'ERROR: WSH_XC_UTIL.log_otm_exception failed unexpectedly');
3744 END IF;
3745 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
3746 ELSIF (l_return_status = WSH_UTIL_CORE.G_RET_STS_WARNING) THEN
3747 --set return status to warning if l_return_status is warning
3748 x_return_status := l_return_status;
3749 END IF;
3750
3751 END IF;
3752
3753 END IF;
3754 -- Debug Statements
3755 --
3756 IF l_debug_on THEN
3757 WSH_DEBUG_SV.log(l_module_name, 'x_return_status', x_return_status);
3758 WSH_DEBUG_SV.pop(l_module_name);
3759 END IF;
3760 --
3761
3762 EXCEPTION
3763 WHEN FND_API.G_EXC_ERROR THEN
3764 ROLLBACK TO tms_update;
3765 x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
3766
3767 IF l_debug_on THEN
3768 WSH_DEBUG_SV.logmsg(l_module_name,'FND_API.G_EXC_ERROR exception has occured ',WSH_DEBUG_SV.C_EXCEP_LEVEL);
3769 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:FND_API.G_EXC_ERROR');
3770 END IF;
3771
3772 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
3773 ROLLBACK TO tms_update;
3774 x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR ;
3775
3776 IF l_debug_on THEN
3777 WSH_DEBUG_SV.logmsg(l_module_name,'Unexpected error has occured. Oracle error message is '|| SQLERRM,WSH_DEBUG_SV.C_UNEXPEC_ERR_LEVEL);
3778 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:FND_API.G_EXC_UNEXPECTED_ERROR');
3779 END IF;
3780
3781 WHEN RECORD_LOCKED THEN
3782 ROLLBACK TO tms_update;
3783 x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
3784 IF l_debug_on THEN
3785 WSH_DEBUG_SV.logmsg(l_module_name,'Record_locked exception has occured. Cannot update delivery tms_interface_flag', WSH_DEBUG_SV.C_EXCEP_LEVEL);
3786 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:RECORD_LOCKED');
3787 END IF;
3788
3789 WHEN others THEN
3790 ROLLBACK TO tms_update;
3791 wsh_util_core.default_handler('WSH_NEW_DELIVERIES_PVT.UPDATE_TMS_INTERFACE_FLAG',l_module_name);
3792 x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR;
3793 IF l_debug_on THEN
3794 WSH_DEBUG_SV.logmsg(l_module_name,'Unexpected error has occured. Oracle error message is '|| SQLERRM,WSH_DEBUG_SV.C_UNEXPEC_ERR_LEVEL);
3795 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:OTHERS');
3796 END IF;
3797
3798 END UPDATE_TMS_INTERFACE_FLAG;
3799 --END OTM R12
3800
3801 END WSH_NEW_DELIVERIES_PVT;