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