[Home] [Help]
PACKAGE BODY: APPS.WSH_DELIVERY_DETAILS_PKG
Source
1 PACKAGE BODY WSH_DELIVERY_DETAILS_PKG as
2 /* $Header: WSHDDTHB.pls 120.21.12010000.2 2008/08/22 08:02:18 mvudugul ship $ */
3
4
5 --
6 G_PKG_NAME CONSTANT VARCHAR2(50) := 'WSH_DELIVERY_DETAILS_PKG';
7 --
8 --
9 -- Procedure: Create_Delivery_Details
10 -- Parameters: All Attributes of a Delivery Detail Record,
11 -- Row_id out
12 -- Delivery_Detail_id out
13 -- Return_Status out
14 -- Description: This procedure will create a delivery detail.
15 -- It will return to the use the delivery_detail_id
16 -- if not provided as a parameter.
17 --
18
19 PROCEDURE Create_Delivery_Details(
20 p_delivery_details_info IN WSH_GLBL_VAR_STRCT_GRP.Delivery_Details_Rec_Type,
21 x_rowid OUT NOCOPY VARCHAR2,
22 x_delivery_detail_id OUT NOCOPY NUMBER,
23 x_return_status OUT NOCOPY VARCHAR2
24 ) IS
25
26
27 CURSOR C_Del_Detail_Rowid
28 IS SELECT rowid
29 FROM wsh_delivery_details
30 WHERE delivery_detail_id = x_delivery_detail_id;
31
32 l_row_count NUMBER;
33 l_container_name varchar2(50);
34 l_temp_id NUMBER;
35 l_cont_dummy_tab WSH_UTIL_CORE.id_tab_type;
36
37 l_dd_id_tab WSH_UTIL_CORE.id_tab_type;
38
39 others exception;
40
41 -- bug 3022644 - Cursor to check whether container name is already exist
42 CURSOR Get_Exist_Cont(v_cont_name VARCHAR2,v_wms_flag NUMBER) IS
43 SELECT NVL(MAX(1),0) FROM DUAL
44 WHERE EXISTS ( SELECT 1 FROM WSH_DELIVERY_DETAILS
45 WHERE container_name = v_cont_name
46 AND container_flag = 'Y'
47 -- LPN reuse project
48 AND released_status = Decode(v_wms_flag,'Y','X',released_status));
49 l_cont_cnt NUMBER;
50 -- end bug 3022644
51 -- lpn conv
52 l_container_info_rec WSH_GLBL_VAR_STRCT_GRP.ContInfoRectype;
53 --
54 l_debug_on BOOLEAN;
55 --
56 l_module_name CONSTANT VARCHAR2(100) := 'wsh.plsql.' || G_PKG_NAME || '.' || 'CREATE_DELIVERY_DETAILS';
57 --
58 BEGIN
59 --
60 --
61 l_debug_on := WSH_DEBUG_INTERFACE.g_debug;
62 --
63 IF l_debug_on IS NULL
64 THEN
65 l_debug_on := WSH_DEBUG_SV.is_debug_enabled;
66 END IF;
67 --
68 IF l_debug_on THEN
69 WSH_DEBUG_SV.push(l_module_name);
70 WSH_DEBUG_SV.log(l_module_name,'p_delivery_details_info.delivery_detail_id',
71 p_delivery_details_info.delivery_detail_id);
72 WSH_DEBUG_SV.log(l_module_name,'p_delivery_details_info.container_name',
73 p_delivery_details_info.container_name);
74 END IF;
75 --
76 x_return_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
77
78 -- bug 3022644
79 /* bmso take out this code once WMS code is ready*/
80 IF (p_delivery_details_info.container_flag = 'Y') THEN
81 l_container_name := p_delivery_details_info.container_name;
82 IF l_debug_on THEN
83 WSH_DEBUG_SV.logmsg(l_module_name, 'CHECK TO SEE IF CONTAINER_NAME ALREADY EXISTS');
84 END IF;
85 OPEN Get_Exist_Cont(l_container_name,WSH_UTIL_VALIDATE.check_wms_org(p_delivery_details_info.organization_id));
86 FETCH Get_Exist_Cont INTO l_cont_cnt;
87 CLOSE Get_Exist_Cont;
88 IF l_cont_cnt = 1 THEN
89 x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
90 FND_MESSAGE.SET_NAME('WSH','WSH_CONT_NAME_DUPLICATE');
91 FND_MESSAGE.SET_TOKEN('CONT_NAME',l_container_name);
92 WSH_UTIL_CORE.Add_Message(x_return_status);
93 IF l_debug_on THEN
94 WSH_DEBUG_SV.pop(l_module_name);
95 END IF;
96 RETURN;
97 END IF;
98 END IF;
99 -- end bug 3022644
100
101 --
102 IF l_debug_on THEN
103 WSH_DEBUG_SV.logmsg(l_module_name, 'INSIDE PROCEDURE CREATE DELIVERY DETAILS Calling BULK api' );
104 END IF;
105 --
106 --lpn conv
107 l_container_info_rec.lpn_ids(1) := p_delivery_details_info.lpn_id;
108 l_container_info_rec.container_names(1) :=
109 p_delivery_details_info.container_name;
110
111 WSH_DELIVERY_DETAILS_PKG.Create_Delivery_Details_Bulk
112 (p_delivery_details_info => p_delivery_details_info,
113 p_num_of_rec => 1,
114 -- lpn conv
115 p_container_info_rec => l_container_info_rec,
116 x_return_status => x_return_status,
117 x_dd_id_tab => l_dd_id_tab
118 );
119
120 x_delivery_detail_id := l_dd_id_tab(1);
121
122 --
123 IF l_debug_on THEN
124 WSH_DEBUG_SV.logmsg(l_module_name, 'AFTER INSERTING INTO WSH_DELIVERY_DETAILS' );
125 END IF;
126 --
127 OPEN C_Del_Detail_Rowid;
128 FETCH C_Del_Detail_Rowid INTO x_rowid;
129 IF (C_Del_Detail_Rowid%NOTFOUND) THEN
130 CLOSE C_Del_Detail_Rowid;
131 RAISE others;
132 END IF;
133 CLOSE C_Del_Detail_Rowid;
134
135 --
136 IF l_debug_on THEN
137 WSH_DEBUG_SV.pop(l_module_name);
138 END IF;
139 --
140 EXCEPTION
141 WHEN others THEN
142 --
143 x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR;
144 wsh_util_core.default_handler('WSH_DELIVERY_DETAILS_PKG.CREATE_DELIVERY_DETAILS',l_module_name);
145 --
146 IF l_debug_on THEN
147 WSH_DEBUG_SV.logmsg(l_module_name,'Unexpected error has occured. Oracle error message is '|| SQLERRM,WSH_DEBUG_SV.C_UNEXPEC_ERR_LEVEL);
148 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:OTHERS');
149 END IF;
150 --
151 END Create_Delivery_Details;
152
153 /************* BULK OPERATION *****************/
154
155 /***********************************
156 CREATE_DELIVERY_DETAILS_BULK
157 ***********************************/
158 --
159 -- Procedure: Create_Delivery_Details_bulk
160 -- keeping old parameters as they were
161 --
162
163 PROCEDURE Create_Delivery_Details_Bulk(
164 p_delivery_details_info IN WSH_GLBL_VAR_STRCT_GRP.Delivery_Details_Rec_Type,
165 p_num_of_rec IN NUMBER,
166 -- lpn conv
167 p_container_info_rec IN WSH_GLBL_VAR_STRCT_GRP.ContInfoRectype,
168 x_return_status OUT NOCOPY VARCHAR2,
169 x_dd_id_tab OUT NOCOPY WSH_UTIL_CORE.id_tab_type
170 ) IS
171
172 /*
173 CURSOR C_Del_Detail_Rowid
174 IS SELECT rowid
175 FROM wsh_delivery_details
176 WHERE delivery_detail_id = x_delivery_detail_id;
177 */
178
179 /* lpn conv
180 CURSOR C_Del_detail_ID
181 IS
182 SELECT wsh_delivery_details_s.nextval
183 FROM sys.dual;
184
185 */
186
187
188 CURSOR c_get_ship_sets(c_p_set_id NUMBER,
189 c_p_source_code VARCHAR,
190 c_p_source_header_id NUMBER) IS
191 SELECT distinct ignore_for_planning
192 FROM wsh_delivery_details
193 WHERE
194 ship_set_id = c_p_set_id and
195 source_code = c_p_source_code and
196 source_header_id = c_p_source_header_id;
197
198 l_row_count NUMBER;
199 l_container_name varchar2(50);
200 l_ignore_for_planning VARCHAR2(1);
201 l_temp_id NUMBER;
202 l_cont_dummy_tab WSH_UTIL_CORE.id_tab_type;
203
204 -- 2530743
205 l_delivery_details_info WSH_GLBL_VAR_STRCT_GRP.Delivery_Details_Rec_Type;
206 l_dff_attribute WSH_FLEXFIELD_UTILS.FlexfieldAttributeTabType;
207 l_dff_context VARCHAR2(150);
208 l_dff_update_flag VARCHAR2(1);
209 l_dff_ret_status VARCHAR2(1);
210
211 -- Bug# 5603974: Considering Automotive TP DFF also.
212 l_dff_tp_attribute WSH_FLEXFIELD_UTILS.FlexfieldAttributeTabType;
213 l_dff_tp_context VARCHAR2(150);
214 l_dff_tp_update_flag VARCHAR2(1);
215 l_dff_tp_ret_status VARCHAR2(1);
216
217 others exception;
218
219 l_wh_type VARCHAR2(3);
220 l_return_status VARCHAR2(1);
221
222 l_dbi_rs VARCHAR2(1); -- Return Status from DBI API
223 --OTM R12 Org-Specifc
224 l_gc3_is_installed VARCHAR2(1);
225
226 --
227 l_debug_on BOOLEAN;
228 --
229 l_module_name CONSTANT VARCHAR2(100) := 'wsh.plsql.' || G_PKG_NAME || '.' || 'CREATE_DELIVERY_DETAILS_BULK';
230 --
231 BEGIN
232 --
233 --
234 l_debug_on := WSH_DEBUG_INTERFACE.g_debug;
235 --
236 IF l_debug_on IS NULL
237 THEN
238 l_debug_on := WSH_DEBUG_SV.is_debug_enabled;
239 END IF;
240 --
241 IF l_debug_on THEN
242 WSH_DEBUG_SV.push(l_module_name);
243 WSH_DEBUG_SV.log(l_module_name,'p_delivery_details_info.delivery_detail_id',
244 p_delivery_details_info.delivery_detail_id);
245 WSH_DEBUG_SV.log(l_module_name,'p_delivery_details_info.container_name',
246 p_delivery_details_info.container_name);
247 WSH_DEBUG_SV.logmsg(l_module_name, 'INSIDE PROCEDURE CREATE DELIVERY DETAILS_BULK' );
248 END IF;
249 --
250
251 --OTM R12 Start Org-Specific
252 l_gc3_is_installed := WSH_UTIL_CORE.G_GC3_IS_INSTALLED;
253 IF l_gc3_is_installed IS NULL THEN
254 l_gc3_is_installed := WSH_UTIL_CORE.GC3_IS_INSTALLED;
255 END IF;
256 IF l_debug_on THEN
257 wsh_debug_sv.log(l_module_name,'l_gc3_is_installed ',
258 l_gc3_is_installed);
259 END IF;
260 --OTM R12 End
261
262 --lpn conv
263 IF p_delivery_details_info.container_flag = 'Y' THEN
264 IF p_container_info_rec.lpn_ids.COUNT = 0
265 OR p_container_info_rec.container_names.count = 0
266 OR p_container_info_rec.container_names.count <> p_num_of_rec
267 OR p_container_info_rec.lpn_ids.count <> p_num_of_rec THEN
268
269 IF l_debug_on THEN
270 WSH_DEBUG_SV.log(l_module_name,'lpn_ids.count',
271 p_container_info_rec.lpn_ids.COUNT);
272 WSH_DEBUG_SV.log(l_module_name,'container_names.count',
273 p_container_info_rec.container_names.count);
274 END IF;
275 RAISE FND_API.G_EXC_ERROR;
276
277 END IF;
278 END IF;
279
280 x_return_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
281 l_delivery_details_info := p_delivery_details_info;
282
283 -- 2530743 : To get Default DFF values
284
285 WSH_FLEXFIELD_UTILS.Get_DFF_Defaults
286 (p_flexfield_name => 'WSH_DELIVERY_DETAILS',
287 p_default_values => l_dff_attribute,
288 p_default_context => l_dff_context,
289 p_update_flag => l_dff_update_flag,
290 x_return_status => l_dff_ret_status);
291
292 IF l_debug_on THEN
293 WSH_DEBUG_SV.log(l_module_name,' Get_DFF_Defaults,l_return_status',l_dff_tp_ret_status);
294 WSH_DEBUG_SV.log(l_module_name,' l_dff_tp_update_flag',l_dff_tp_update_flag);
295 END IF;
296
297 IF ( l_dff_ret_status = WSH_UTIL_CORE.G_RET_STS_SUCCESS AND l_dff_update_flag = 'Y' ) THEN
298 l_delivery_details_info.attribute_category :=
299 nvl(p_delivery_details_info.attribute_category,l_dff_context );
300 l_delivery_details_info.attribute1 :=
301 nvl(p_delivery_details_info.attribute1, l_dff_attribute(1) );
302 l_delivery_details_info.attribute2 :=
303 nvl(p_delivery_details_info.attribute2, l_dff_attribute( 2) );
304 l_delivery_details_info.attribute3 :=
305 nvl(p_delivery_details_info.attribute3, l_dff_attribute( 3) );
306 l_delivery_details_info.attribute4 :=
307 nvl(p_delivery_details_info.attribute4, l_dff_attribute( 4) );
308 l_delivery_details_info.attribute5 :=
309 nvl(p_delivery_details_info.attribute5, l_dff_attribute( 5) );
310 l_delivery_details_info.attribute6 :=
311 nvl(p_delivery_details_info.attribute6, l_dff_attribute( 6) );
312 l_delivery_details_info.attribute7 :=
313 nvl(p_delivery_details_info.attribute7, l_dff_attribute( 7) );
314 l_delivery_details_info.attribute8 :=
315 nvl(p_delivery_details_info.attribute8, l_dff_attribute( 8) );
316 l_delivery_details_info.attribute9 :=
317 nvl(p_delivery_details_info.attribute9, l_dff_attribute( 9) );
318 l_delivery_details_info.attribute10 :=
319 nvl(p_delivery_details_info.attribute10, l_dff_attribute( 10) );
320 l_delivery_details_info.attribute11 :=
321 nvl(p_delivery_details_info.attribute11, l_dff_attribute( 11) );
322 l_delivery_details_info.attribute12 :=
323 nvl(p_delivery_details_info.attribute12, l_dff_attribute( 12) );
324 l_delivery_details_info.attribute13 :=
325 nvl(p_delivery_details_info.attribute13, l_dff_attribute( 13) );
326 l_delivery_details_info.attribute14 :=
327 nvl(p_delivery_details_info.attribute14, l_dff_attribute( 14) );
328 l_delivery_details_info.attribute15 :=
329 nvl(p_delivery_details_info.attribute15, l_dff_attribute( 15) );
330 END IF;
331
332 -- Bug# 5603974: Considering Automotive TP DFF also.
333 WSH_FLEXFIELD_UTILS.Get_DFF_Defaults
334 (p_flexfield_name => 'WSH_VEA_DELIVERY_DETAILS',
335 p_default_values => l_dff_tp_attribute,
336 p_default_context => l_dff_tp_context,
337 p_update_flag => l_dff_tp_update_flag,
338 x_return_status => l_dff_tp_ret_status);
339
340 IF l_debug_on THEN
341 WSH_DEBUG_SV.log(l_module_name,' Get_DFF_Defaults,l_return_status',l_dff_tp_ret_status);
342 WSH_DEBUG_SV.log(l_module_name,' l_dff_tp_update_flag',l_dff_tp_update_flag);
343 END IF;
344
345 IF ( l_dff_tp_ret_status = WSH_UTIL_CORE.G_RET_STS_SUCCESS AND l_dff_tp_update_flag = 'Y' ) THEN
346 l_delivery_details_info.tp_attribute_category :=
347 nvl(p_delivery_details_info.tp_attribute_category,l_dff_tp_context );
348 l_delivery_details_info.tp_attribute1 :=
349 nvl(p_delivery_details_info.tp_attribute1, l_dff_tp_attribute(1) );
350 l_delivery_details_info.tp_attribute2 :=
351 nvl(p_delivery_details_info.tp_attribute2, l_dff_tp_attribute( 2) );
352 l_delivery_details_info.tp_attribute3 :=
353 nvl(p_delivery_details_info.tp_attribute3, l_dff_tp_attribute( 3) );
354 l_delivery_details_info.tp_attribute4 :=
355 nvl(p_delivery_details_info.tp_attribute4, l_dff_tp_attribute( 4) );
356 l_delivery_details_info.tp_attribute5 :=
357 nvl(p_delivery_details_info.tp_attribute5, l_dff_tp_attribute( 5) );
358 l_delivery_details_info.tp_attribute6 :=
359 nvl(p_delivery_details_info.tp_attribute6, l_dff_tp_attribute( 6) );
360 l_delivery_details_info.tp_attribute7 :=
361 nvl(p_delivery_details_info.tp_attribute7, l_dff_tp_attribute( 7) );
362 l_delivery_details_info.tp_attribute8 :=
363 nvl(p_delivery_details_info.tp_attribute8, l_dff_tp_attribute( 8) );
364 l_delivery_details_info.tp_attribute9 :=
365 nvl(p_delivery_details_info.tp_attribute9, l_dff_tp_attribute( 9) );
366 l_delivery_details_info.tp_attribute10 :=
367 nvl(p_delivery_details_info.tp_attribute10, l_dff_tp_attribute( 10) );
368 l_delivery_details_info.tp_attribute11 :=
369 nvl(p_delivery_details_info.tp_attribute11, l_dff_tp_attribute( 11) );
370 l_delivery_details_info.tp_attribute12 :=
371 nvl(p_delivery_details_info.tp_attribute12, l_dff_tp_attribute( 12) );
372 l_delivery_details_info.tp_attribute13 :=
373 nvl(p_delivery_details_info.tp_attribute13, l_dff_tp_attribute( 13) );
374 l_delivery_details_info.tp_attribute14 :=
375 nvl(p_delivery_details_info.tp_attribute14, l_dff_tp_attribute( 14) );
376 l_delivery_details_info.tp_attribute15 :=
377 nvl(p_delivery_details_info.tp_attribute15, l_dff_tp_attribute( 15) );
378 END IF;
379
380 -- Bug# 5603974: End
381
382 -- For Auto Packing, the container name will always
383 -- be same as delivery detail id
384 -- only check if the delivery detail has container flag as Y
385 -- then use delivery detail id
386
387 /* lpn conv
388 -- get the delivery detail id in loop
389 FOR i in 1..p_num_of_rec
390 LOOP
391 OPEN C_Del_detail_ID ;
392 FETCH C_Del_detail_ID
393 INTO l_cont_dummy_tab(i);
394 CLOSE C_Del_detail_ID;
395
396 END LOOP;
397 */
398 --x_delivery_detail_id := l_cont_dummy_tab(1);
399
400 -- l_cont_dummy_tab will have container instance id
401
402 /*** J changes TP release ****/
403 --OTM R12 Start Org-Specific
404 IF (wsh_util_core.tp_is_installed ='Y' OR l_gc3_is_installed = 'Y' ) THEN --{
405 IF ( l_delivery_details_info.ignore_for_planning IS NULL ) THEN --{
406
407 -- if new detail is being created by split then it will take the original details flag
408 l_wh_type := WSH_EXTERNAL_INTERFACE_SV.Get_Warehouse_Type
409 (p_organization_id => l_delivery_details_info.organization_id,
410 p_carrier_id => l_delivery_details_info.carrier_id,
411 p_ship_method_code => l_delivery_details_info.ship_method_code,
412 p_msg_display => 'N',
413 x_return_status => l_return_status);
414 IF l_debug_on THEN
415 WSH_DEBUG_SV.log(l_module_name,'Get_Warehouse_Type l_wh_type',
416 l_wh_type);
417 WSH_DEBUG_SV.log(l_module_name,'Get_Warehouse_Type l_return_status',
418 l_return_status);
419 END IF;
420 IF (l_return_status in (WSH_UTIL_CORE.G_RET_STS_ERROR,
421 WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR)) THEN
422 RAISE FND_API.G_EXC_ERROR;
423 END IF;
424 IF (nvl(l_wh_type, FND_API.G_MISS_CHAR) IN ('TPW','CMS')) THEN --{
425 l_delivery_details_info.ignore_for_planning:='Y';
426 ELSIF (l_gc3_is_installed = 'Y') THEN
427 WSH_UTIL_VALIDATE.CALC_IGNORE_FOR_PLANNING(
428 p_organization_id => l_delivery_details_info.organization_id
429 ,p_carrier_id => NULL
430 ,p_ship_method_code => NULL
431 ,p_tp_installed => NULL
432 ,p_caller => NULL
433 ,x_ignore_for_planning => l_ignore_for_planning
434 ,x_return_status => l_return_status
435 ,p_otm_installed => 'Y');
436 IF l_debug_on THEN
437 WSH_DEBUG_SV.log(l_module_name,'After call to WSH_UTIL_VALIDATE.CA'
438 ||'LC_IGNORE_FOR_PLANNING l_return_status',l_return_status);
439 WSH_DEBUG_SV.log(l_module_name,'l_ignore_for_planning ',
440 l_ignore_for_planning );
441 END IF;
442 IF (l_return_status in (WSH_UTIL_CORE.G_RET_STS_ERROR,
443 WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR)) THEN
444 RAISE FND_API.G_EXC_ERROR;
445 END IF;
446 l_delivery_details_info.ignore_for_planning := l_ignore_for_planning;
447 ELSE
448 IF l_delivery_details_info.ship_set_id is NOT NULL THEN
449 OPEN c_get_ship_sets(l_delivery_details_info.ship_set_id,
450 l_delivery_details_info.source_code,
451 l_delivery_details_info.source_header_id);
452 FETCH c_get_ship_sets INTO l_delivery_details_info.ignore_for_planning;
453 IF c_get_ship_sets%NOTFOUND OR l_delivery_details_info.ignore_for_planning IS NULL THEN
454 l_delivery_details_info.ignore_for_planning:='N';
455 ELSE
456 FETCH c_get_ship_sets INTO l_ignore_for_planning;
457 IF c_get_ship_sets%FOUND THEN
458 l_delivery_details_info.ignore_for_planning:='N';
459 END IF;
460 END IF;
461 CLOSE c_get_ship_sets;
462 ELSE
463 l_delivery_details_info.ignore_for_planning:='N';
464 END IF;
465 END IF; --}
466 END IF; --}
467 ELSE
468 l_delivery_details_info.ignore_for_planning:='N';
469 END IF; --}
470 IF l_debug_on THEN
471 WSH_DEBUG_SV.log(l_module_name,'l_delivery_details_info.ignore_for_planning',
472 l_delivery_details_info.ignore_for_planning);
473 END IF; --}
474 --OTM R12 End
475
476 /*** J changes TP release ****/
477 --
478 FORALL i in 1..p_num_of_rec
479 INSERT INTO wsh_delivery_details(
480 source_code,
481 source_header_id,
482 delivery_detail_id,
483 source_line_id,
484 customer_id,
485 sold_to_contact_id,
486 inventory_item_id,
487 item_description,
488 hazard_class_id,
489 country_of_origin,
490 classification,
491 ship_from_location_id,
492 ship_to_location_id,
493 ship_to_contact_id,
494 ship_to_site_use_id ,
495 deliver_to_location_id,
496 deliver_to_contact_id,
497 deliver_to_site_use_id ,
498 intmed_ship_to_location_id,
499 intmed_ship_to_contact_id,
500 ship_tolerance_above,
501 ship_tolerance_below,
502 requested_quantity,
503 shipped_quantity,
504 delivered_quantity,
505 requested_quantity_uom,
506 subinventory,
507 revision,
508 lot_number,
509 customer_requested_lot_flag,
510 serial_number,
511 locator_id,
512 date_requested,
513 date_scheduled,
514 master_container_item_id,
515 detail_container_item_id,
516 load_seq_number,
517 ship_method_code,
518 carrier_id,
519 freight_terms_code,
520 shipment_priority_code,
521 fob_code,
522 customer_item_id,
523 dep_plan_required_flag,
524 customer_prod_seq,
525 customer_dock_code,
526 cust_model_serial_number,
527 customer_job,
528 customer_production_line,
529 net_weight,
530 weight_uom_code,
531 volume,
532 volume_uom_code,
533 -- J: W/V Changes
534 unit_weight,
535 unit_volume,
536 filled_volume,
537 wv_frozen_flag,
538 tp_attribute_category,
539 tp_attribute1,
540 tp_attribute2,
541 tp_attribute3,
542 tp_attribute4,
543 tp_attribute5,
544 tp_attribute6,
545 tp_attribute7,
546 tp_attribute8,
547 tp_attribute9,
548 tp_attribute10,
549 tp_attribute11,
550 tp_attribute12,
551 tp_attribute13,
552 tp_attribute14,
553 tp_attribute15,
554 attribute_category, -- bug 1902467
555 attribute1,
556 attribute2,
557 attribute3,
558 attribute4,
559 attribute5,
560 attribute6,
561 attribute7,
562 attribute8,
563 attribute9,
564 attribute10,
565 attribute11,
566 attribute12,
567 attribute13,
568 attribute14,
569 attribute15,
570 created_by,
571 creation_date,
572 last_update_date,
573 last_update_login,
574 last_updated_by,
575 program_application_id,
576 program_id,
577 program_update_date,
578 request_id,
579 mvt_stat_status,
580 organization_id,
581 transaction_temp_id,
582 ship_set_id,
583 arrival_set_id,
584 ship_model_complete_flag,
585 top_model_line_id,
586 hold_code,
587 source_header_number,
588 source_header_type_id,
589 source_header_type_name,
590 cust_po_number,
591 ato_line_id,
592 src_requested_quantity,
593 src_requested_quantity_uom,
594 move_order_line_id,
595 cancelled_quantity,
596 quality_control_quantity,
597 cycle_count_quantity,
598 tracking_number,
599 movement_id,
600 shipping_instructions,
601 packing_instructions,
602 project_id,
603 task_id,
604 org_id,
605 oe_interfaced_flag,
606 split_from_delivery_detail_id,
607 inv_interfaced_flag,
608 source_line_number,
609 inspection_flag,
610 released_status,
611 container_flag,
612 container_type_code,
613 container_name,
614 fill_percent,
615 gross_weight,
616 master_serial_number,
617 maximum_load_weight,
618 maximum_volume,
619 minimum_fill_percent,
620 seal_code,
621 unit_number,
622 unit_price,
623 currency_code,
624 freight_class_cat_id,
625 commodity_code_cat_id,
626 preferred_grade, /* hverddin 26-jun-2000 start OPM changes */
627 src_requested_quantity2,
628 src_requested_quantity_uom2,
629 requested_quantity2,
630 shipped_quantity2,
631 delivered_quantity2,
632 cancelled_quantity2,
633 quality_control_quantity2,
634 cycle_count_quantity2,
635 requested_quantity_uom2,
636 -- HW OPMCONV - No need for sublot_number
637 -- sublot_number /* hverddin 26-jun-2000 end OPM changes */,
638 pickable_flag,
639 original_subinventory,
640 to_serial_number,
641 picked_quantity,
642 picked_quantity2,
643 /* H Integration: datamodel changes wrudge */
644 received_quantity,
645 received_quantity2,
646 source_line_set_id,
647 batch_id,
648 lpn_id,
649 /* J Inbound Logistics: New columns jckwok */
650 vendor_id ,
651 ship_from_site_id ,
652 line_direction ,
653 party_id ,
654 routing_req_id ,
655 shipping_control ,
656 source_blanket_reference_id ,
657 source_blanket_reference_num ,
658 po_shipment_line_id ,
659 po_shipment_line_number ,
660 returned_quantity ,
661 returned_quantity2 ,
662 rcv_shipment_line_id ,
663 source_line_type_code ,
664 supplier_item_number ,
665 /* J TP release : ttrichy*/
666 IGNORE_FOR_PLANNING ,
667 EARLIEST_PICKUP_DATE ,
668 LATEST_PICKUP_DATE ,
669 EARLIEST_DROPOFF_DATE ,
670 LATEST_DROPOFF_DATE ,
671 --DEMAND_SATISFACTION_DATE , --confirm name for this
672 REQUEST_DATE_TYPE_CODE ,
673 tp_delivery_detail_id ,
674 source_document_type_id ,
675 service_level,
676 mode_of_transport,
677 /* J Inbound Logistics: New columns asutar*/
678 po_revision_number,
679 release_revision_number
680 ) VALUES
681 (l_delivery_details_info.source_code,
682 l_delivery_details_info.source_header_id,
683 wsh_delivery_details_s.nextval, --lpn conv
684 decode( l_delivery_details_info.container_flag , 'Y', wsh_delivery_details_s.currval, l_delivery_details_info.source_line_id),
685 l_delivery_details_info.customer_id,
686 l_delivery_details_info.sold_to_contact_id,
687 l_delivery_details_info.inventory_item_id,
688 l_delivery_details_info.item_description,
689 l_delivery_details_info.hazard_class_id,
690 l_delivery_details_info.country_of_origin,
691 l_delivery_details_info.classification,
692 l_delivery_details_info.ship_from_location_id,
693 l_delivery_details_info.ship_to_location_id,
694 l_delivery_details_info.ship_to_contact_id,
695 l_delivery_details_info.ship_to_site_use_id,
696 l_delivery_details_info.deliver_to_location_id,
697 l_delivery_details_info.deliver_to_contact_id,
698 l_delivery_details_info.deliver_to_site_use_id,
699 l_delivery_details_info.intmed_ship_to_location_id,
700 l_delivery_details_info.intmed_ship_to_contact_id,
701 l_delivery_details_info.ship_tolerance_above,
702 l_delivery_details_info.ship_tolerance_below,
703 l_delivery_details_info.requested_quantity,
704 l_delivery_details_info.shipped_quantity,
705 l_delivery_details_info.delivered_quantity,
706 nvl ( l_delivery_details_info.requested_quantity_uom,
707 decode ( l_delivery_details_info.container_flag, 'Y' , 'YY' , 'XX') ) ,
708 l_delivery_details_info.subinventory,
709 l_delivery_details_info.revision,
710 l_delivery_details_info.lot_number,
711 l_delivery_details_info.customer_requested_lot_flag,
712 l_delivery_details_info.serial_number,
713 l_delivery_details_info.locator_id,
714 l_delivery_details_info.date_requested,
715 l_delivery_details_info.date_scheduled,
716 l_delivery_details_info.master_container_item_id,
717 l_delivery_details_info.detail_container_item_id,
718 l_delivery_details_info.load_seq_number,
719 l_delivery_details_info.ship_method_code,
720 l_delivery_details_info.carrier_id,
721 l_delivery_details_info.freight_terms_code,
722 l_delivery_details_info.shipment_priority_code,
723 l_delivery_details_info.fob_code,
724 l_delivery_details_info.customer_item_id,
725 l_delivery_details_info.dep_plan_required_flag,
726 l_delivery_details_info.customer_prod_seq,
727 l_delivery_details_info.customer_dock_code,
728 l_delivery_details_info.cust_model_serial_number,
729 l_delivery_details_info.customer_job,
730 l_delivery_details_info.customer_production_line,
731 l_delivery_details_info.net_weight,
732 l_delivery_details_info.weight_uom_code,
733 l_delivery_details_info.volume,
734 l_delivery_details_info.volume_uom_code,
735 -- J: W/V Changes
736 l_delivery_details_info.unit_weight,
737 l_delivery_details_info.unit_volume,
738 l_delivery_details_info.filled_volume,
739 l_delivery_details_info.wv_frozen_flag,
740 l_delivery_details_info.tp_attribute_category,
741 l_delivery_details_info.tp_attribute1,
742 l_delivery_details_info.tp_attribute2,
743 l_delivery_details_info.tp_attribute3,
744 l_delivery_details_info.tp_attribute4,
745 l_delivery_details_info.tp_attribute5,
746 l_delivery_details_info.tp_attribute6,
747 l_delivery_details_info.tp_attribute7,
748 l_delivery_details_info.tp_attribute8,
749 l_delivery_details_info.tp_attribute9,
750 l_delivery_details_info.tp_attribute10,
751 l_delivery_details_info.tp_attribute11,
752 l_delivery_details_info.tp_attribute12,
753 l_delivery_details_info.tp_attribute13,
754 l_delivery_details_info.tp_attribute14,
755 l_delivery_details_info.tp_attribute15,
756 l_delivery_details_info.attribute_category, -- bug 1902467
757 l_delivery_details_info.attribute1,
758 l_delivery_details_info.attribute2,
759 l_delivery_details_info.attribute3,
760 l_delivery_details_info.attribute4,
761 l_delivery_details_info.attribute5,
762 l_delivery_details_info.attribute6,
763 l_delivery_details_info.attribute7,
764 l_delivery_details_info.attribute8,
765 l_delivery_details_info.attribute9,
766 l_delivery_details_info.attribute10,
767 l_delivery_details_info.attribute11,
768 l_delivery_details_info.attribute12,
769 l_delivery_details_info.attribute13,
770 l_delivery_details_info.attribute14,
771 l_delivery_details_info.attribute15,
772 FND_GLOBAL.USER_ID,
773 SYSDATE,
774 SYSDATE,
775 FND_GLOBAL.USER_ID,
776 FND_GLOBAL.USER_ID,
777 NULL,
778 NULL,
779 NULL,
780 l_delivery_details_info.request_id,
781 l_delivery_details_info.mvt_stat_status,
782 l_delivery_details_info.organization_id,
783 l_delivery_details_info.transaction_temp_id,
784 l_delivery_details_info.ship_set_id,
785 l_delivery_details_info.arrival_set_id,
786 l_delivery_details_info.ship_model_complete_flag,
787 l_delivery_details_info.top_model_line_id,
788 l_delivery_details_info.hold_code,
789 l_delivery_details_info.source_header_number,
790 l_delivery_details_info.source_header_type_id,
791 l_delivery_details_info.source_header_type_name,
792 l_delivery_details_info.cust_po_number,
793 l_delivery_details_info.ato_line_id,
794 l_delivery_details_info.src_requested_quantity,
795 l_delivery_details_info.src_requested_quantity_uom,
796 l_delivery_details_info.move_order_line_id,
797 l_delivery_details_info.cancelled_quantity,
798 l_delivery_details_info.quality_control_quantity,
799 l_delivery_details_info.cycle_count_quantity ,
800 l_delivery_details_info.tracking_number,
801 l_delivery_details_info.movement_id,
802 l_delivery_details_info.shipping_instructions,
803 l_delivery_details_info.packing_instructions,
804 l_delivery_details_info.project_id,
805 l_delivery_details_info.task_id,
806 l_delivery_details_info.org_id,
807 decode(l_delivery_details_info.source_code,'OE','N','X'),
808 l_delivery_details_info.split_from_detail_id,
809 nvl(l_delivery_details_info.inv_interfaced_flag, decode(l_delivery_details_info.pickable_flag,'Y','N','X')),
810 l_delivery_details_info.source_line_number,
811 -- Inspection is required only for 'OKE'
812 decode ( l_delivery_details_info.source_code, 'OKE' , nvl (l_delivery_details_info.inspection_flag , 'N') , 'N' ),
813 l_delivery_details_info.released_status,
814 l_delivery_details_info.container_flag,
815 l_delivery_details_info.container_type_code,
816 decode(l_delivery_details_info.container_flag,'Y',
817 nvl(p_container_info_rec.container_names(i), NVL(l_delivery_details_info.container_name,wsh_delivery_details_s.currval)),
818 l_delivery_details_info.container_name),
819 l_delivery_details_info.fill_percent,
820 l_delivery_details_info.gross_weight,
821 l_delivery_details_info.master_serial_number,
822 l_delivery_details_info.maximum_load_weight,
823 l_delivery_details_info.maximum_volume,
824 l_delivery_details_info.minimum_fill_percent,
825 l_delivery_details_info.seal_code,
826 l_delivery_details_info.unit_number,
827 l_delivery_details_info.unit_price,
828 l_delivery_details_info.currency_code,
829 l_delivery_details_info.freight_class_cat_id,
830 l_delivery_details_info.commodity_code_cat_id,
831 l_delivery_details_info.preferred_grade, /* hverddin start changes */
832 l_delivery_details_info.src_requested_quantity2,
833 l_delivery_details_info.src_requested_quantity_uom2,
834 l_delivery_details_info.requested_quantity2,
835 l_delivery_details_info.shipped_quantity2,
836 l_delivery_details_info.delivered_quantity2,
837 l_delivery_details_info.cancelled_quantity2,
838 l_delivery_details_info.quality_control_quantity2,
839 l_delivery_details_info.cycle_count_quantity2,
840 l_delivery_details_info.requested_quantity_uom2,
841 -- HW OPMCONV - No need for sublot_number
842 -- l_delivery_details_info.sublot_number /* hverddin end changes */,
843 l_delivery_details_info.pickable_flag,
844 l_delivery_details_info.original_subinventory,
845 l_delivery_details_info.to_serial_number,
846 l_delivery_details_info.picked_quantity,
847 l_delivery_details_info.picked_quantity2,
848 /* H Integration: datamodel changes wrudge */
849 l_delivery_details_info.received_quantity,
850 l_delivery_details_info.received_quantity2,
851 l_delivery_details_info.source_line_set_id,
852 l_delivery_details_info.batch_id,
853 --lpn conv
854 decode(l_delivery_details_info.container_flag , 'Y',
855 NVL(p_container_info_rec.lpn_ids(i),l_delivery_details_info.lpn_id),
856 l_delivery_details_info.lpn_id) ,
857 /* J Inbound Logistics: New columns jckwok */
858 l_delivery_details_info.vendor_id ,
859 l_delivery_details_info.ship_from_site_id ,
860 nvl(l_delivery_details_info.line_direction, 'O') ,
861 l_delivery_details_info.party_id ,
862 l_delivery_details_info.routing_req_id ,
863 l_delivery_details_info.shipping_control ,
864 l_delivery_details_info.source_blanket_reference_id ,
865 l_delivery_details_info.source_blanket_reference_num ,
866 l_delivery_details_info.po_shipment_line_id ,
867 l_delivery_details_info.po_shipment_line_number ,
868 l_delivery_details_info.returned_quantity ,
869 l_delivery_details_info.returned_quantity2 ,
870 l_delivery_details_info.rcv_shipment_line_id ,
871 l_delivery_details_info.source_line_type_code ,
872 l_delivery_details_info.supplier_item_number ,
873 /* J TP release : ttrichy */
874 nvl(l_delivery_details_info.IGNORE_FOR_PLANNING, 'N') ,
875 l_delivery_details_info.EARLIEST_PICKUP_DATE ,
876 l_delivery_details_info.LATEST_PICKUP_DATE ,
877 l_delivery_details_info.EARLIEST_DROPOFF_DATE ,
878 l_delivery_details_info.LATEST_DROPOFF_DATE ,
879 --l_delivery_details_info.DEMAND_SATISFACTION_DATE ,
880 l_delivery_details_info.REQUEST_DATE_TYPE_CODE ,
881 l_delivery_details_info.tp_delivery_detail_id ,
882 l_delivery_details_info.source_document_type_id ,
883 l_delivery_details_info.service_level ,
884 l_delivery_details_info.mode_of_transport ,
885 /* J IB : austar*/
886 l_delivery_details_info.po_revision_number,
887 l_delivery_details_info.release_revision_number
888 )
889 RETURNING delivery_detail_id BULK COLLECT INTO x_dd_id_tab;
890
891 --
892 IF l_debug_on THEN
893 WSH_DEBUG_SV.logmsg(l_module_name, 'AFTER INSERTING INTO WSH_DELIVERY_DETAILS');
894 END IF;
895 --
896
897 -- DBI Project
898 -- Insert of wsh_delivery_details, call DBI API after the insert.
899 -- This API will also check for DBI Installed or not
900 IF l_debug_on THEN
901 WSH_DEBUG_SV.log(l_module_name,'Calling DBI API.Detail count-',x_dd_id_tab.count);
902 END IF;
903 WSH_INTEGRATION.DBI_Update_Detail_Log
904 (p_delivery_detail_id_tab => x_dd_id_tab,
905 p_dml_type => 'INSERT',
906 x_return_status => l_dbi_rs);
907
908 IF l_debug_on THEN
909 WSH_DEBUG_SV.log(l_module_name,'Return Status after DBI Call-',l_dbi_rs);
910 END IF;
911 IF l_dbi_rs = WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR THEN
912 x_return_status := l_dbi_rs;
913 -- just pass this return status to caller API
914 END IF;
915 -- End of Code for DBI Project
916
917 --
918 IF l_debug_on THEN
919 WSH_DEBUG_SV.pop(l_module_name);
920 END IF;
921 --
922 EXCEPTION
923 WHEN FND_API.G_EXC_ERROR THEN
924
925 x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
926 --
927 IF l_debug_on THEN
928 WSH_DEBUG_SV.logmsg(l_module_name,'FND_API.G_EXC_ERROR exception has occured ',WSH_DEBUG_SV.C_EXCEP_LEVEL);
929 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:FND_API.G_EXC_ERROR');
930 END IF;
931 --
932 WHEN others THEN
933 --
934 IF l_debug_on THEN
935 WSH_DEBUG_SV.logmsg(l_module_name, 'WHEN OTHERS IN CREATE_DELIVERY_DETAILS_BULK ' || SQLERRM );
936 END IF;
937 --
938 x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR;
939 wsh_util_core.default_handler('WSH_DELIVERY_DETAILS_PKG.CREATE_DELIVERY_DETAILS_BULK',l_module_name);
940 --
941 IF l_debug_on THEN
942 WSH_DEBUG_SV.logmsg(l_module_name,'Unexpected error has occured. Oracle error message is '|| SQLERRM,WSH_DEBUG_SV.C_UNEXPEC_ERR_LEVEL);
943 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:OTHERS');
944 END IF;
945 --
946 END Create_Delivery_Details_Bulk;
947
948 /***********************************
949 CREATE_DD_FROM_OLD_BULK
950 ***********************************/
951
952 -- This API has been called to
953 -- make a Bulk call for creating Delivery Details
954 -- from Split API in WSHDDACB
955
956 -- Example if ordered qty = 13 and container capacity = 5
957 -- line will be split 2 times with qty of 5
958 -- so create 2 new records
959
960 PROCEDURE create_dd_from_old_bulk(
961 p_delivery_detail_rec IN WSH_GLBL_VAR_STRCT_GRP.Delivery_Details_Rec_Type,
962 p_delivery_detail_id IN NUMBER,
963 p_num_of_rec IN NUMBER,
964 x_dd_id_tab OUT NOCOPY WSH_UTIL_CORE.id_tab_type,
965 x_return_status OUT NOCOPY VARCHAR2
966 ) IS
967
968 l_container_name varchar2(50);
969
970 CURSOR C_Del_detail_ID
971 IS
972 SELECT wsh_delivery_details_s.nextval
973 FROM sys.dual;
974
975 l_row_count NUMBER;
976 l_temp_id NUMBER;
977
978 l_dd_id_tab WSH_UTIL_CORE.id_tab_type;
979 l_dd_rec WSH_GLBL_VAR_STRCT_GRP.DELIVERY_DETAILS_REC_TYPE;
980 l_dbi_rs VARCHAR2(1); -- Return Status from DBI API
981
982 others exception;
983
984 --
985 l_debug_on BOOLEAN;
986 --
987 l_module_name CONSTANT VARCHAR2(100) := 'wsh.plsql.' || G_PKG_NAME || '.' || 'CREATE_DD_FROM_OLD_BULK';
988 --
989 BEGIN
990 --
991 --
992 l_debug_on := WSH_DEBUG_INTERFACE.g_debug;
993 --
994 IF l_debug_on IS NULL
995 THEN
996 l_debug_on := WSH_DEBUG_SV.is_debug_enabled;
997 END IF;
998 --
999 IF l_debug_on THEN
1000 WSH_DEBUG_SV.push(l_module_name);
1001 --
1002 WSH_DEBUG_SV.log(l_module_name,'P_DELIVERY_DETAIL_ID',P_DELIVERY_DETAIL_ID);
1003 END IF;
1004 --
1005 x_return_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
1006
1007 --
1008 IF l_debug_on THEN
1009 WSH_DEBUG_SV.logmsg(l_module_name, 'INSIDE PROCEDURE CREATE DD FROM OLD_BULK' );
1010 END IF;
1011 --
1012 FOR i in 1..p_num_of_rec
1013 LOOP
1014 OPEN C_Del_detail_ID ;
1015 FETCH C_Del_detail_ID INTO l_dd_id_tab(i);
1016 CLOSE C_Del_detail_ID;
1017 END LOOP;
1018
1019 /* Note : J TP release : ignore_for_planning will be defaulted from the old record*/
1020 --
1021 IF l_debug_on THEN
1022 WSH_DEBUG_SV.logmsg(l_module_name, 'BEFORE INSERTING INTO WSH_DELIVERY_DETAILS' ||l_dd_id_tab.count );
1023 END IF;
1024 --
1025 FORALL i in 1..p_num_of_rec
1026 INSERT INTO wsh_delivery_details(
1027 source_code,
1028 source_header_id,
1029 source_line_id,
1030 customer_id,
1031 sold_to_contact_id,
1032 inventory_item_id,
1033 item_description,
1034 hazard_class_id,
1035 country_of_origin,
1036 classification,
1037 ship_from_location_id,
1038 ship_to_location_id,
1039 ship_to_contact_id,
1040 ship_to_site_use_id ,
1041 deliver_to_location_id,
1042 deliver_to_contact_id,
1043 deliver_to_site_use_id ,
1044 intmed_ship_to_location_id,
1045 intmed_ship_to_contact_id,
1046 ship_tolerance_above,
1047 ship_tolerance_below,
1048 requested_quantity,
1049 shipped_quantity,
1050 delivered_quantity,
1051 requested_quantity_uom,
1052 subinventory,
1053 revision,
1054 lot_number,
1055 customer_requested_lot_flag,
1056 serial_number,
1057 locator_id,
1058 date_requested,
1059 date_scheduled,
1060 master_container_item_id,
1061 detail_container_item_id,
1062 load_seq_number,
1063 ship_method_code,
1064 carrier_id,
1065 freight_terms_code,
1066 shipment_priority_code,
1067 fob_code,
1068 customer_item_id,
1069 dep_plan_required_flag,
1070 customer_prod_seq,
1071 customer_dock_code,
1072 cust_model_serial_number,
1073 customer_job,
1074 customer_production_line,
1075 net_weight,
1076 weight_uom_code,
1077 volume,
1078 volume_uom_code,
1079 -- J: W/V Changes
1080 unit_weight,
1081 unit_volume,
1082 filled_volume,
1083 wv_frozen_flag,
1084 tp_attribute_category,
1085 tp_attribute1,
1086 tp_attribute2,
1087 tp_attribute3,
1088 tp_attribute4,
1089 tp_attribute5,
1090 tp_attribute6,
1091 tp_attribute7,
1092 tp_attribute8,
1093 tp_attribute9,
1094 tp_attribute10,
1095 tp_attribute11,
1096 tp_attribute12,
1097 tp_attribute13,
1098 tp_attribute14,
1099 tp_attribute15,
1100 attribute_category, -- bug 1902467
1101 attribute1,
1102 attribute2,
1103 attribute3,
1104 attribute4,
1105 attribute5,
1106 attribute6,
1107 attribute7,
1108 attribute8,
1109 attribute9,
1110 attribute10,
1111 attribute11,
1112 attribute12,
1113 attribute13,
1114 attribute14,
1115 attribute15,
1116 created_by,
1117 creation_date,
1118 last_update_date,
1119 last_update_login,
1120 last_updated_by,
1121 program_application_id,
1122 program_id,
1123 program_update_date,
1124 request_id,
1125 mvt_stat_status,
1126 organization_id,
1127 transaction_temp_id,
1128 ship_set_id,
1129 arrival_set_id,
1130 ship_model_complete_flag,
1131 top_model_line_id,
1132 hold_code,
1133 source_header_number,
1134 source_header_type_id,
1135 source_header_type_name,
1136 cust_po_number,
1137 ato_line_id,
1138 src_requested_quantity,
1139 src_requested_quantity_uom,
1140 move_order_line_id,
1141 cancelled_quantity,
1142 quality_control_quantity,
1143 cycle_count_quantity,
1144 tracking_number,
1145 movement_id,
1146 shipping_instructions,
1147 packing_instructions,
1148 project_id,
1149 task_id,
1150 org_id,
1151 oe_interfaced_flag,
1152 split_from_delivery_detail_id,
1153 inv_interfaced_flag,
1154 source_line_number,
1155 inspection_flag,
1156 released_status,
1157 delivery_detail_id,
1158 container_flag,
1159 container_type_code,
1160 container_name,
1161 fill_percent,
1162 gross_weight,
1163 master_serial_number,
1164 maximum_load_weight,
1165 maximum_volume,
1166 minimum_fill_percent,
1167 seal_code,
1168 unit_number,
1169 unit_price,
1170 currency_code,
1171 freight_class_cat_id,
1172 commodity_code_cat_id,
1173 preferred_grade, /* start OPM changes */
1174 src_requested_quantity2,
1175 src_requested_quantity_uom2,
1176 requested_quantity2,
1177 shipped_quantity2,
1178 delivered_quantity2,
1179 cancelled_quantity2,
1180 quality_control_quantity2,
1181 cycle_count_quantity2,
1182 requested_quantity_uom2,
1183 -- HW OPMCONV - No need for sublot_number
1184 -- sublot_number /* end OPM changes */,
1185 pickable_flag,
1186 original_subinventory,
1187 to_serial_number,
1188 picked_quantity,
1189 picked_quantity2,
1190 /* H Integration: datamodel changes wrudge */
1191 received_quantity,
1192 received_quantity2,
1193 source_line_set_id,
1194 batch_id,
1195 transaction_id, ---- 2803570
1196 lpn_id,
1197 /* J Inbound Logistics: New columns jckwok */
1198 vendor_id ,
1199 ship_from_site_id ,
1200 line_direction ,
1201 party_id ,
1202 routing_req_id ,
1203 shipping_control ,
1204 source_blanket_reference_id ,
1205 source_blanket_reference_num ,
1206 po_shipment_line_id ,
1207 po_shipment_line_number ,
1208 returned_quantity ,
1209 returned_quantity2 ,
1210 rcv_shipment_line_id ,
1211 source_line_type_code ,
1212 supplier_item_number ,
1213 /* J TP release : ttrichy*/
1214 IGNORE_FOR_PLANNING ,
1215 EARLIEST_PICKUP_DATE ,
1216 LATEST_PICKUP_DATE ,
1217 EARLIEST_DROPOFF_DATE ,
1218 LATEST_DROPOFF_DATE ,
1219 --DEMAND_SATISFACTION_DATE , --confirm name for this
1220 REQUEST_DATE_TYPE_CODE ,
1221 tp_delivery_detail_id ,
1222 source_document_type_id ,
1223 service_level ,
1224 mode_of_transport ,
1225 /* J IB : asutar*/
1226 po_revision_number,
1227 release_revision_number,
1228 replenishment_status --bug# 6689448 (replenishment project)
1229 )
1230 SELECT
1231 decode(p_delivery_detail_rec.source_code,NULL,wdd.source_code,FND_API.G_MISS_CHAR,NULL,p_delivery_detail_rec.source_code),
1232 decode(p_delivery_detail_rec.source_header_id,NULL,wdd.source_header_id,FND_API.G_MISS_NUM,NULL,p_delivery_detail_rec.source_header_id),
1233 decode(p_delivery_detail_rec.source_line_id,NULL,wdd.source_line_id,FND_API.G_MISS_NUM,NULL,p_delivery_detail_rec.source_line_id),
1234 decode(p_delivery_detail_rec.customer_id,NULL,wdd.customer_id,FND_API.G_MISS_NUM,NULL,p_delivery_detail_rec.customer_id),
1235 decode(p_delivery_detail_rec.sold_to_contact_id,NULL,wdd.sold_to_contact_id,FND_API.G_MISS_NUM,NULL,p_delivery_detail_rec.sold_to_contact_id),
1236 decode(p_delivery_detail_rec.inventory_item_id,NULL,wdd.inventory_item_id,FND_API.G_MISS_NUM,NULL,p_delivery_detail_rec.inventory_item_id),
1237 decode(p_delivery_detail_rec.item_description,NULL,wdd.item_description,FND_API.G_MISS_CHAR,NULL,p_delivery_detail_rec.item_description),
1238 decode(p_delivery_detail_rec.hazard_class_id,NULL,wdd.hazard_class_id,FND_API.G_MISS_NUM,NULL,p_delivery_detail_rec.hazard_class_id),
1239 decode(p_delivery_detail_rec.country_of_origin,NULL,wdd.country_of_origin,FND_API.G_MISS_CHAR,NULL,p_delivery_detail_rec.country_of_origin),
1240 decode(p_delivery_detail_rec.classification,NULL,wdd.classification,FND_API.G_MISS_CHAR,NULL,p_delivery_detail_rec.classification),
1241 decode(p_delivery_detail_rec.ship_from_location_id,NULL,wdd.ship_from_location_id,FND_API.G_MISS_NUM,NULL,p_delivery_detail_rec.ship_from_location_id),
1242 decode(p_delivery_detail_rec.ship_to_location_id,NULL,wdd.ship_to_location_id,FND_API.G_MISS_NUM,NULL,p_delivery_detail_rec.ship_to_location_id),
1243 decode(p_delivery_detail_rec.ship_to_contact_id,NULL,wdd.ship_to_contact_id,FND_API.G_MISS_NUM,NULL,p_delivery_detail_rec.ship_to_contact_id),
1244 decode(p_delivery_detail_rec.ship_to_site_use_id,NULL,wdd.ship_to_site_use_id,FND_API.G_MISS_NUM,NULL,p_delivery_detail_rec.ship_to_site_use_id),
1245 decode(p_delivery_detail_rec.deliver_to_location_id,NULL,wdd.deliver_to_location_id,FND_API.G_MISS_NUM,NULL,p_delivery_detail_rec.deliver_to_location_id),
1246 decode(p_delivery_detail_rec.deliver_to_contact_id,NULL,wdd.deliver_to_contact_id,FND_API.G_MISS_NUM,NULL,p_delivery_detail_rec.deliver_to_contact_id),
1247 decode(p_delivery_detail_rec.deliver_to_site_use_id,NULL,wdd.deliver_to_site_use_id,FND_API.G_MISS_NUM,NULL,p_delivery_detail_rec.deliver_to_site_use_id),
1248 decode(p_delivery_detail_rec.intmed_ship_to_location_id,NULL,wdd.intmed_ship_to_location_id,FND_API.G_MISS_NUM,NULL,p_delivery_detail_rec.intmed_ship_to_location_id),
1249 decode(p_delivery_detail_rec.intmed_ship_to_contact_id,NULL,wdd.intmed_ship_to_contact_id,FND_API.G_MISS_NUM,NULL,p_delivery_detail_rec.intmed_ship_to_contact_id),
1250 decode(p_delivery_detail_rec.ship_tolerance_above,NULL,wdd.ship_tolerance_above,FND_API.G_MISS_NUM,NULL,p_delivery_detail_rec.ship_tolerance_above),
1251 decode(p_delivery_detail_rec.ship_tolerance_below,NULL,wdd.ship_tolerance_below,FND_API.G_MISS_NUM,NULL,p_delivery_detail_rec.ship_tolerance_below),
1252 decode(p_delivery_detail_rec.requested_quantity,NULL,wdd.requested_quantity,FND_API.G_MISS_NUM,NULL,p_delivery_detail_rec.requested_quantity),
1253 decode(p_delivery_detail_rec.shipped_quantity,NULL,wdd.shipped_quantity,FND_API.G_MISS_NUM,NULL,p_delivery_detail_rec.shipped_quantity),
1254 decode(p_delivery_detail_rec.delivered_quantity,NULL,wdd.delivered_quantity,FND_API.G_MISS_NUM,NULL,p_delivery_detail_rec.delivered_quantity),
1255
1256 decode(p_delivery_detail_rec.requested_quantity_uom,NULL,nvl(wdd.requested_quantity_uom,decode(wdd.container_flag, 'Y' , 'YY' , 'XX')),FND_API.G_MISS_CHAR,NULL,p_delivery_detail_rec.requested_quantity_uom),
1257
1258 decode(p_delivery_detail_rec.subinventory,NULL,wdd.subinventory,FND_API.G_MISS_CHAR,NULL,p_delivery_detail_rec.subinventory),
1259 decode(p_delivery_detail_rec.revision,NULL,wdd.revision,FND_API.G_MISS_CHAR,NULL,p_delivery_detail_rec.revision),
1260 decode(p_delivery_detail_rec.lot_number,NULL,wdd.lot_number,FND_API.G_MISS_CHAR,NULL,p_delivery_detail_rec.lot_number),
1261 decode(p_delivery_detail_rec.customer_requested_lot_flag,NULL,wdd.customer_requested_lot_flag,FND_API.G_MISS_CHAR,NULL,p_delivery_detail_rec.customer_requested_lot_flag),
1262 decode(p_delivery_detail_rec.serial_number,NULL,wdd.serial_number,FND_API.G_MISS_CHAR,NULL,p_delivery_detail_rec.serial_number),
1263 decode(p_delivery_detail_rec.locator_id,NULL,wdd.locator_id,FND_API.G_MISS_NUM,NULL,p_delivery_detail_rec.locator_id),
1264 decode(p_delivery_detail_rec.date_requested,NULL,wdd.date_requested,FND_API.G_MISS_DATE,NULL,p_delivery_detail_rec.date_requested),
1265 decode(p_delivery_detail_rec.date_scheduled,NULL,wdd.date_scheduled,FND_API.G_MISS_DATE,NULL,p_delivery_detail_rec.date_scheduled),
1266 decode(p_delivery_detail_rec.master_container_item_id,NULL,wdd.master_container_item_id,FND_API.G_MISS_NUM,NULL,p_delivery_detail_rec.master_container_item_id),
1267 decode(p_delivery_detail_rec.detail_container_item_id,NULL,wdd.detail_container_item_id,FND_API.G_MISS_NUM,NULL,p_delivery_detail_rec.detail_container_item_id),
1268 decode(p_delivery_detail_rec.load_seq_number,NULL,wdd.load_seq_number,FND_API.G_MISS_NUM,NULL,p_delivery_detail_rec.load_seq_number),
1269 decode(p_delivery_detail_rec.ship_method_code,NULL,wdd.ship_method_code,FND_API.G_MISS_CHAR,NULL,p_delivery_detail_rec.ship_method_code),
1270 decode(p_delivery_detail_rec.carrier_id,NULL,wdd.carrier_id,FND_API.G_MISS_NUM,NULL,p_delivery_detail_rec.carrier_id),
1271 decode(p_delivery_detail_rec.freight_terms_code,NULL,wdd.freight_terms_code,FND_API.G_MISS_CHAR,NULL,p_delivery_detail_rec.freight_terms_code),
1272 decode(p_delivery_detail_rec.shipment_priority_code,NULL,wdd.shipment_priority_code,FND_API.G_MISS_CHAR,NULL,p_delivery_detail_rec.shipment_priority_code),
1273 decode(p_delivery_detail_rec.fob_code,NULL,wdd.fob_code,FND_API.G_MISS_CHAR,NULL,p_delivery_detail_rec.fob_code),
1274 decode(p_delivery_detail_rec.customer_item_id,NULL,wdd.customer_item_id,FND_API.G_MISS_NUM,NULL,p_delivery_detail_rec.customer_item_id),
1275 decode(p_delivery_detail_rec.dep_plan_required_flag,NULL,wdd.dep_plan_required_flag,FND_API.G_MISS_CHAR,NULL,p_delivery_detail_rec.dep_plan_required_flag),
1276 decode(p_delivery_detail_rec.customer_prod_seq,NULL,wdd.customer_prod_seq,FND_API.G_MISS_CHAR,NULL,p_delivery_detail_rec.customer_prod_seq),
1277 decode(p_delivery_detail_rec.customer_dock_code,NULL,wdd.customer_dock_code,FND_API.G_MISS_CHAR,NULL,p_delivery_detail_rec.customer_dock_code),
1278 decode(p_delivery_detail_rec.cust_model_serial_number,NULL,wdd.cust_model_serial_number,FND_API.G_MISS_CHAR,NULL,p_delivery_detail_rec.cust_model_serial_number),
1279 decode(p_delivery_detail_rec.customer_job,NULL,wdd.customer_job,FND_API.G_MISS_CHAR,NULL,p_delivery_detail_rec.customer_job),
1280 decode(p_delivery_detail_rec.customer_production_line,NULL,wdd.customer_production_line,FND_API.G_MISS_CHAR,NULL,p_delivery_detail_rec.customer_production_line),
1281 decode(p_delivery_detail_rec.net_weight,NULL,wdd.net_weight,FND_API.G_MISS_NUM,NULL,p_delivery_detail_rec.net_weight),
1282 decode(p_delivery_detail_rec.weight_uom_code,NULL,wdd.weight_uom_code,FND_API.G_MISS_CHAR,NULL,p_delivery_detail_rec.weight_uom_code),
1283 decode(p_delivery_detail_rec.volume,NULL,wdd.volume,FND_API.G_MISS_NUM,NULL,p_delivery_detail_rec.volume),
1284 decode(p_delivery_detail_rec.volume_uom_code,NULL,wdd.volume_uom_code,FND_API.G_MISS_CHAR,NULL,p_delivery_detail_rec.volume_uom_code),
1285 -- J: W/V Changes
1286 decode(p_delivery_detail_rec.unit_weight,NULL,wdd.unit_weight,FND_API.G_MISS_NUM,NULL,p_delivery_detail_rec.unit_weight),
1287 decode(p_delivery_detail_rec.unit_volume,NULL,wdd.unit_volume,FND_API.G_MISS_NUM,NULL,p_delivery_detail_rec.unit_volume),
1288 decode(p_delivery_detail_rec.filled_volume,NULL,wdd.filled_volume,FND_API.G_MISS_NUM,NULL,p_delivery_detail_rec.filled_volume),
1289 decode(p_delivery_detail_rec.wv_frozen_flag,NULL,'N',FND_API.G_MISS_NUM,'N',p_delivery_detail_rec.wv_frozen_flag),
1290
1291 decode(p_delivery_detail_rec.tp_attribute_category,NULL,wdd.tp_attribute_category,FND_API.G_MISS_CHAR,NULL,p_delivery_detail_rec.tp_attribute_category),
1292 decode(p_delivery_detail_rec.tp_attribute1,NULL,wdd.tp_attribute1,FND_API.G_MISS_CHAR,NULL,p_delivery_detail_rec.tp_attribute1),
1293 decode(p_delivery_detail_rec.tp_attribute2,NULL,wdd.tp_attribute2,FND_API.G_MISS_CHAR,NULL,p_delivery_detail_rec.tp_attribute2),
1294 decode(p_delivery_detail_rec.tp_attribute3,NULL,wdd.tp_attribute3,FND_API.G_MISS_CHAR,NULL,p_delivery_detail_rec.tp_attribute3),
1295 decode(p_delivery_detail_rec.tp_attribute4,NULL,wdd.tp_attribute4,FND_API.G_MISS_CHAR,NULL,p_delivery_detail_rec.tp_attribute4),
1296 decode(p_delivery_detail_rec.tp_attribute5,NULL,wdd.tp_attribute5,FND_API.G_MISS_CHAR,NULL,p_delivery_detail_rec.tp_attribute5),
1297 decode(p_delivery_detail_rec.tp_attribute6,NULL,wdd.tp_attribute6,FND_API.G_MISS_CHAR,NULL,p_delivery_detail_rec.tp_attribute6),
1298 decode(p_delivery_detail_rec.tp_attribute7,NULL,wdd.tp_attribute7,FND_API.G_MISS_CHAR,NULL,p_delivery_detail_rec.tp_attribute7),
1299 decode(p_delivery_detail_rec.tp_attribute8,NULL,wdd.tp_attribute8,FND_API.G_MISS_CHAR,NULL,p_delivery_detail_rec.tp_attribute8),
1300 decode(p_delivery_detail_rec.tp_attribute9,NULL,wdd.tp_attribute9,FND_API.G_MISS_CHAR,NULL,p_delivery_detail_rec.tp_attribute9),
1301 decode(p_delivery_detail_rec.tp_attribute10,NULL,wdd.tp_attribute10,FND_API.G_MISS_CHAR,NULL,p_delivery_detail_rec.tp_attribute10),
1302 decode(p_delivery_detail_rec.tp_attribute11,NULL,wdd.tp_attribute11,FND_API.G_MISS_CHAR,NULL,p_delivery_detail_rec.tp_attribute11),
1303 decode(p_delivery_detail_rec.tp_attribute12,NULL,wdd.tp_attribute12,FND_API.G_MISS_CHAR,NULL,p_delivery_detail_rec.tp_attribute12),
1304 decode(p_delivery_detail_rec.tp_attribute13,NULL,wdd.tp_attribute13,FND_API.G_MISS_CHAR,NULL,p_delivery_detail_rec.tp_attribute13),
1305 decode(p_delivery_detail_rec.tp_attribute14,NULL,wdd.tp_attribute14,FND_API.G_MISS_CHAR,NULL,p_delivery_detail_rec.tp_attribute14),
1306 decode(p_delivery_detail_rec.tp_attribute15,NULL,wdd.tp_attribute15,FND_API.G_MISS_CHAR,NULL,p_delivery_detail_rec.tp_attribute15),
1307 decode(p_delivery_detail_rec.attribute_category,NULL,wdd.attribute_category,FND_API.G_MISS_CHAR,NULL,p_delivery_detail_rec.attribute_category), -- bug 1902467
1308 decode(p_delivery_detail_rec.attribute1,NULL,wdd.attribute1,FND_API.G_MISS_CHAR,NULL,p_delivery_detail_rec.attribute1),
1309 decode(p_delivery_detail_rec.attribute2,NULL,wdd.attribute2,FND_API.G_MISS_CHAR,NULL,p_delivery_detail_rec.attribute2),
1310 decode(p_delivery_detail_rec.attribute3,NULL,wdd.attribute3,FND_API.G_MISS_CHAR,NULL,p_delivery_detail_rec.attribute3),
1311 decode(p_delivery_detail_rec.attribute4,NULL,wdd.attribute4,FND_API.G_MISS_CHAR,NULL,p_delivery_detail_rec.attribute4),
1312 decode(p_delivery_detail_rec.attribute5,NULL,wdd.attribute5,FND_API.G_MISS_CHAR,NULL,p_delivery_detail_rec.attribute5),
1313 decode(p_delivery_detail_rec.attribute6,NULL,wdd.attribute6,FND_API.G_MISS_CHAR,NULL,p_delivery_detail_rec.attribute6),
1314 decode(p_delivery_detail_rec.attribute7,NULL,wdd.attribute7,FND_API.G_MISS_CHAR,NULL,p_delivery_detail_rec.attribute7),
1315 decode(p_delivery_detail_rec.attribute8,NULL,wdd.attribute8,FND_API.G_MISS_CHAR,NULL,p_delivery_detail_rec.attribute8),
1316 decode(p_delivery_detail_rec.attribute9,NULL,wdd.attribute9,FND_API.G_MISS_CHAR,NULL,p_delivery_detail_rec.attribute9),
1317 decode(p_delivery_detail_rec.attribute10,NULL,wdd.attribute10,FND_API.G_MISS_CHAR,NULL,p_delivery_detail_rec.attribute10),
1318 decode(p_delivery_detail_rec.attribute11,NULL,wdd.attribute11,FND_API.G_MISS_CHAR,NULL,p_delivery_detail_rec.attribute11),
1319 decode(p_delivery_detail_rec.attribute12,NULL,wdd.attribute12,FND_API.G_MISS_CHAR,NULL,p_delivery_detail_rec.attribute12),
1320 decode(p_delivery_detail_rec.attribute13,NULL,wdd.attribute13,FND_API.G_MISS_CHAR,NULL,p_delivery_detail_rec.attribute13),
1321 decode(p_delivery_detail_rec.attribute14,NULL,wdd.attribute14,FND_API.G_MISS_CHAR,NULL,p_delivery_detail_rec.attribute14),
1322 decode(p_delivery_detail_rec.attribute15,NULL,wdd.attribute15,FND_API.G_MISS_CHAR,NULL,p_delivery_detail_rec.attribute15),
1323 FND_GLOBAL.USER_ID,
1324 SYSDATE,
1325 SYSDATE,
1326 FND_GLOBAL.USER_ID,
1327 FND_GLOBAL.USER_ID,
1328 NULL,
1329 NULL,
1330 NULL,
1331 decode(p_delivery_detail_rec.request_id,NULL,wdd.request_id,FND_API.G_MISS_NUM,NULL,p_delivery_detail_rec.request_id),
1332 decode(p_delivery_detail_rec.mvt_stat_status,NULL,wdd.mvt_stat_status,FND_API.G_MISS_CHAR,NULL,p_delivery_detail_rec.mvt_stat_status),
1333 decode(p_delivery_detail_rec.organization_id,NULL,wdd.organization_id,FND_API.G_MISS_NUM,NULL,p_delivery_detail_rec.organization_id),
1334 decode(p_delivery_detail_rec.transaction_temp_id,NULL,wdd.transaction_temp_id,FND_API.G_MISS_NUM,NULL,p_delivery_detail_rec.transaction_temp_id),
1335 decode(p_delivery_detail_rec.ship_set_id,NULL,wdd.ship_set_id,FND_API.G_MISS_NUM,NULL,p_delivery_detail_rec.ship_set_id),
1336 decode(p_delivery_detail_rec.arrival_set_id,NULL,wdd.arrival_set_id,FND_API.G_MISS_NUM,NULL,p_delivery_detail_rec.arrival_set_id),
1337 decode(p_delivery_detail_rec.ship_model_complete_flag,NULL,wdd.ship_model_complete_flag,FND_API.G_MISS_CHAR,NULL,p_delivery_detail_rec.ship_model_complete_flag),
1338 decode(p_delivery_detail_rec.top_model_line_id,NULL,wdd.top_model_line_id,FND_API.G_MISS_NUM,NULL,p_delivery_detail_rec.top_model_line_id),
1339 decode(p_delivery_detail_rec.hold_code,NULL,wdd.hold_code,FND_API.G_MISS_CHAR,NULL,p_delivery_detail_rec.hold_code),
1340 decode(p_delivery_detail_rec.source_header_number,NULL,wdd.source_header_number,FND_API.G_MISS_CHAR,NULL,p_delivery_detail_rec.source_header_number),
1341 decode(p_delivery_detail_rec.source_header_type_id,NULL,wdd.source_header_type_id,FND_API.G_MISS_NUM,NULL,p_delivery_detail_rec.source_header_type_id),
1342 decode(p_delivery_detail_rec.source_header_type_name,NULL,wdd.source_header_type_name,FND_API.G_MISS_CHAR,NULL,p_delivery_detail_rec.source_header_type_name),
1343 decode(p_delivery_detail_rec.cust_po_number,NULL,wdd.cust_po_number,FND_API.G_MISS_CHAR,NULL,p_delivery_detail_rec.cust_po_number),
1344 decode(p_delivery_detail_rec.ato_line_id,NULL,wdd.ato_line_id,FND_API.G_MISS_NUM,NULL,p_delivery_detail_rec.ato_line_id),
1345 decode(p_delivery_detail_rec.src_requested_quantity,NULL,wdd.src_requested_quantity,FND_API.G_MISS_NUM,NULL,p_delivery_detail_rec.src_requested_quantity),
1346
1347 decode(p_delivery_detail_rec.src_requested_quantity_uom,
1348 NULL, wdd.src_requested_quantity_uom,
1349 FND_API.G_MISS_CHAR, NULL,
1350 p_delivery_detail_rec.src_requested_quantity_uom),
1351 decode(p_delivery_detail_rec.move_order_line_id,NULL,wdd.move_order_line_id,FND_API.G_MISS_NUM,NULL,p_delivery_detail_rec.move_order_line_id),
1352 decode(p_delivery_detail_rec.cancelled_quantity,NULL,wdd.cancelled_quantity,FND_API.G_MISS_NUM,NULL,p_delivery_detail_rec.cancelled_quantity),
1353 decode(p_delivery_detail_rec.quality_control_quantity,NULL,wdd.quality_control_quantity,FND_API.G_MISS_NUM,NULL,p_delivery_detail_rec.quality_control_quantity),
1354 decode(p_delivery_detail_rec.cycle_count_quantity ,NULL,wdd.cycle_count_quantity ,FND_API.G_MISS_NUM,NULL,p_delivery_detail_rec.cycle_count_quantity),
1355 decode(decode(p_delivery_detail_rec.source_code,NULL,wdd.source_code,FND_API.G_MISS_CHAR,NULL,p_delivery_detail_rec.source_code),
1356 'PO',(decode (decode(p_delivery_detail_rec.released_status,NULL,wdd.released_status,FND_API.G_MISS_CHAR,NULL,p_delivery_detail_rec.released_status) ,
1357 'X',null,p_delivery_detail_rec.tracking_number ) ),null), --bugfix 3711663
1358 decode(p_delivery_detail_rec.movement_id,NULL,wdd.movement_id,FND_API.G_MISS_NUM,NULL,p_delivery_detail_rec.movement_id),
1359 decode(p_delivery_detail_rec.shipping_instructions,NULL,wdd.shipping_instructions,FND_API.G_MISS_CHAR,NULL,p_delivery_detail_rec.shipping_instructions),
1360 decode(p_delivery_detail_rec.packing_instructions,NULL,wdd.packing_instructions,FND_API.G_MISS_CHAR,NULL,p_delivery_detail_rec.packing_instructions),
1361 decode(p_delivery_detail_rec.project_id,NULL,wdd.project_id,FND_API.G_MISS_NUM,NULL,p_delivery_detail_rec.project_id),
1362 decode(p_delivery_detail_rec.task_id,NULL,wdd.task_id,FND_API.G_MISS_NUM,NULL,p_delivery_detail_rec.task_id),
1363 decode(p_delivery_detail_rec.org_id,NULL,wdd.org_id,FND_API.G_MISS_NUM,NULL,p_delivery_detail_rec.org_id),
1364
1365 decode(p_delivery_detail_rec.oe_interfaced_flag,NULL,decode(wdd.source_code,'OE','N','X'),FND_API.G_MISS_CHAR,NULL,p_delivery_detail_rec.oe_interfaced_flag),
1366
1367 decode(p_delivery_detail_rec.split_from_detail_id,NULL,wdd.split_from_delivery_detail_id,FND_API.G_MISS_NUM,NULL,p_delivery_detail_rec.split_from_detail_id),
1368
1369 decode(p_delivery_detail_rec.inv_interfaced_flag,NULL,decode(wdd.pickable_flag,'Y','N','X'),FND_API.G_MISS_CHAR,NULL,p_delivery_detail_rec.inv_interfaced_flag),
1370
1371 decode(p_delivery_detail_rec.source_line_number,NULL,wdd.source_line_number,FND_API.G_MISS_CHAR,NULL,p_delivery_detail_rec.source_line_number),
1372 decode(p_delivery_detail_rec.inspection_flag,NULL,decode(wdd.source_code,'OKE',nvl(wdd.inspection_flag,'N'),'N'),FND_API.G_MISS_CHAR,NULL,p_delivery_detail_rec.inspection_flag),
1373
1374 decode(p_delivery_detail_rec.released_status,NULL,wdd.released_status,FND_API.G_MISS_CHAR,NULL,p_delivery_detail_rec.released_status),
1375
1376 l_dd_id_tab(i),
1377
1378 decode(p_delivery_detail_rec.container_flag,NULL,wdd.container_flag,FND_API.G_MISS_CHAR,NULL,p_delivery_detail_rec.container_flag),
1379 decode(p_delivery_detail_rec.container_type_code,NULL,wdd.container_type_code,FND_API.G_MISS_CHAR,NULL,p_delivery_detail_rec.container_type_code),
1380
1381 decode(p_delivery_detail_rec.container_name,NULL,decode(wdd.container_flag,'Y',to_char(l_dd_id_tab(i)),null),FND_API.G_MISS_CHAR,NULL,p_delivery_detail_rec.container_name),
1382
1383 decode(p_delivery_detail_rec.fill_percent,NULL,wdd.fill_percent,FND_API.G_MISS_NUM,NULL,p_delivery_detail_rec.fill_percent),
1384 decode(p_delivery_detail_rec.gross_weight,NULL,wdd.gross_weight,FND_API.G_MISS_NUM,NULL,p_delivery_detail_rec.gross_weight),
1385 decode(p_delivery_detail_rec.master_serial_number,NULL,wdd.master_serial_number,FND_API.G_MISS_CHAR,NULL,p_delivery_detail_rec.master_serial_number),
1386 decode(p_delivery_detail_rec.maximum_load_weight,NULL,wdd.maximum_load_weight,FND_API.G_MISS_NUM,NULL,p_delivery_detail_rec.maximum_load_weight),
1387 decode(p_delivery_detail_rec.maximum_volume,NULL,wdd.maximum_volume,FND_API.G_MISS_NUM,NULL,p_delivery_detail_rec.maximum_volume),
1388 decode(p_delivery_detail_rec.minimum_fill_percent,NULL,wdd.minimum_fill_percent,FND_API.G_MISS_NUM,NULL,p_delivery_detail_rec.minimum_fill_percent),
1389 decode(p_delivery_detail_rec.seal_code,NULL,wdd.seal_code,FND_API.G_MISS_CHAR,NULL,p_delivery_detail_rec.seal_code),
1390 decode(p_delivery_detail_rec.unit_number,NULL,wdd.unit_number,FND_API.G_MISS_CHAR,NULL,p_delivery_detail_rec.unit_number),
1391 decode(p_delivery_detail_rec.unit_price,NULL,wdd.unit_price,FND_API.G_MISS_NUM,NULL,p_delivery_detail_rec.unit_price),
1392 decode(p_delivery_detail_rec.currency_code,NULL,wdd.currency_code,FND_API.G_MISS_CHAR,NULL,p_delivery_detail_rec.currency_code),
1393 decode(p_delivery_detail_rec.freight_class_cat_id,NULL,wdd.freight_class_cat_id,FND_API.G_MISS_NUM,NULL,p_delivery_detail_rec.freight_class_cat_id),
1394 decode(p_delivery_detail_rec.commodity_code_cat_id,NULL,wdd.commodity_code_cat_id,FND_API.G_MISS_NUM,NULL,p_delivery_detail_rec.commodity_code_cat_id),
1395 decode(p_delivery_detail_rec.preferred_grade,NULL,wdd.preferred_grade,FND_API.G_MISS_CHAR,NULL,p_delivery_detail_rec.preferred_grade), /* hverddin start changes */
1396 decode(p_delivery_detail_rec.src_requested_quantity2,NULL,wdd.src_requested_quantity2,FND_API.G_MISS_NUM,NULL,p_delivery_detail_rec.src_requested_quantity2),
1397 decode(p_delivery_detail_rec.src_requested_quantity_uom2,NULL,wdd.src_requested_quantity_uom2,FND_API.G_MISS_CHAR,NULL,p_delivery_detail_rec.src_requested_quantity_uom2),
1398 decode(p_delivery_detail_rec.requested_quantity2,NULL,wdd.requested_quantity2,FND_API.G_MISS_NUM,NULL,p_delivery_detail_rec.requested_quantity2),
1399 decode(p_delivery_detail_rec.shipped_quantity2,NULL,wdd.shipped_quantity2,FND_API.G_MISS_NUM,NULL,p_delivery_detail_rec.shipped_quantity2),
1400 decode(p_delivery_detail_rec.delivered_quantity2,NULL,wdd.delivered_quantity2,FND_API.G_MISS_NUM,NULL,p_delivery_detail_rec.delivered_quantity2),
1401 decode(p_delivery_detail_rec.cancelled_quantity2,NULL,wdd.cancelled_quantity2,FND_API.G_MISS_NUM,NULL,p_delivery_detail_rec.cancelled_quantity2),
1402 decode(p_delivery_detail_rec.quality_control_quantity2,NULL,wdd.quality_control_quantity2,FND_API.G_MISS_NUM,NULL,p_delivery_detail_rec.quality_control_quantity2),
1403 decode(p_delivery_detail_rec.cycle_count_quantity2,NULL,wdd.cycle_count_quantity2,FND_API.G_MISS_NUM,NULL,p_delivery_detail_rec.cycle_count_quantity2),
1404 decode(p_delivery_detail_rec.requested_quantity_uom2,NULL,wdd.requested_quantity_uom2,FND_API.G_MISS_CHAR,NULL,p_delivery_detail_rec.requested_quantity_uom2),
1405 -- HW OPMCONV - No need for sublot_number
1406 -- decode(p_delivery_detail_rec.sublot_number,NULL,wdd.sublot_number,FND_API.G_MISS_CHAR,NULL,p_delivery_detail_rec.sublot_number), /* hverddin end changes */
1407 decode(p_delivery_detail_rec.pickable_flag,NULL,wdd.pickable_flag,FND_API.G_MISS_CHAR,NULL,p_delivery_detail_rec.pickable_flag),
1408 decode(p_delivery_detail_rec.original_subinventory,NULL,wdd.original_subinventory,FND_API.G_MISS_CHAR,NULL,p_delivery_detail_rec.original_subinventory),
1409 decode(p_delivery_detail_rec.to_serial_number,NULL,wdd.to_serial_number,FND_API.G_MISS_CHAR,NULL,p_delivery_detail_rec.to_serial_number),
1410 decode(p_delivery_detail_rec.picked_quantity,NULL,wdd.picked_quantity,FND_API.G_MISS_NUM,NULL,p_delivery_detail_rec.picked_quantity),
1411 decode(p_delivery_detail_rec.picked_quantity2,NULL,wdd.picked_quantity2,FND_API.G_MISS_NUM,NULL,p_delivery_detail_rec.picked_quantity2),
1412 /* H Integration: datamodel changes wrudge */
1413 decode(p_delivery_detail_rec.received_quantity,NULL,wdd.received_quantity,FND_API.G_MISS_NUM,NULL,p_delivery_detail_rec.received_quantity),
1414 decode(p_delivery_detail_rec.received_quantity2,NULL,wdd.received_quantity2,FND_API.G_MISS_NUM,NULL,p_delivery_detail_rec.received_quantity2),
1415 decode(p_delivery_detail_rec.source_line_set_id,NULL,wdd.source_line_set_id,FND_API.G_MISS_NUM,NULL,p_delivery_detail_rec.source_line_set_id),
1416 decode(p_delivery_detail_rec.batch_id,NULL,wdd.batch_id,FND_API.G_MISS_NUM,NULL,p_delivery_detail_rec.batch_id),
1417 decode(p_delivery_detail_rec.transaction_id,NULL,wdd.transaction_id,FND_API.G_MISS_NUM,NULL,p_delivery_detail_rec.transaction_id),
1418 decode(p_delivery_detail_rec.lpn_id,NULL,wdd.lpn_id,FND_API.G_MISS_NUM,NULL,p_delivery_detail_rec.lpn_id),
1419 /* J Inbound Logistics: New columns jckwok */
1420 decode(p_delivery_detail_rec.vendor_id,NULL,wdd.vendor_id,FND_API.G_MISS_NUM,NULL,p_delivery_detail_rec.vendor_id),
1421 decode(p_delivery_detail_rec.ship_from_site_id,NULL,wdd.ship_from_site_id,FND_API.G_MISS_NUM,NULL,p_delivery_detail_rec.ship_from_site_id),
1422 decode(p_delivery_detail_rec.line_direction,NULL,wdd.line_direction,FND_API.G_MISS_CHAR,NULL,p_delivery_detail_rec.line_direction),
1423 decode(p_delivery_detail_rec.party_id,NULL,wdd.party_id,party_id,FND_API.G_MISS_NUM,NULL,p_delivery_detail_rec.party_id),
1424 decode(p_delivery_detail_rec.routing_req_id,NULL,wdd.routing_req_id,FND_API.G_MISS_NUM,NULL,p_delivery_detail_rec.routing_req_id),
1425 decode(p_delivery_detail_rec.shipping_control,NULL,wdd.shipping_control,FND_API.G_MISS_CHAR,NULL,p_delivery_detail_rec.shipping_control),
1426 decode(p_delivery_detail_rec.source_blanket_reference_id,NULL,wdd.source_blanket_reference_id,FND_API.G_MISS_NUM,NULL,p_delivery_detail_rec.source_blanket_reference_id),
1427 decode(p_delivery_detail_rec.source_blanket_reference_num,NULL,wdd.source_blanket_reference_num,FND_API.G_MISS_NUM,NULL,p_delivery_detail_rec.source_blanket_reference_num),
1428 decode(p_delivery_detail_rec.po_shipment_line_id,NULL,wdd.po_shipment_line_id,FND_API.G_MISS_NUM,NULL,p_delivery_detail_rec.po_shipment_line_id),
1429 decode(p_delivery_detail_rec.po_shipment_line_number,NULL,wdd.po_shipment_line_number,FND_API.G_MISS_NUM,NULL,p_delivery_detail_rec.po_shipment_line_number),
1430 decode(p_delivery_detail_rec.returned_quantity,NULL,wdd.returned_quantity,FND_API.G_MISS_NUM,NULL,p_delivery_detail_rec.returned_quantity),
1431 decode(p_delivery_detail_rec.returned_quantity2,NULL,wdd.returned_quantity2,FND_API.G_MISS_NUM,NULL,p_delivery_detail_rec.returned_quantity2),
1432 decode(p_delivery_detail_rec.rcv_shipment_line_id,NULL,wdd.rcv_shipment_line_id,FND_API.G_MISS_NUM,NULL,p_delivery_detail_rec.rcv_shipment_line_id),
1433 decode(p_delivery_detail_rec.source_line_type_code,NULL,wdd.source_line_type_code,FND_API.G_MISS_CHAR,NULL,p_delivery_detail_rec.source_line_type_code),
1434 decode(p_delivery_detail_rec.supplier_item_number,NULL,wdd.supplier_item_number,FND_API.G_MISS_CHAR,NULL,p_delivery_detail_rec.supplier_item_number),
1435 /* J TP release : ttrichy*/
1436 decode(p_delivery_detail_rec.IGNORE_FOR_PLANNING,NULL,wdd.IGNORE_FOR_PLANNING,FND_API.G_MISS_CHAR,NULL,p_delivery_detail_rec.IGNORE_FOR_PLANNING),
1437 decode(p_delivery_detail_rec.EARLIEST_PICKUP_DATE,NULL,wdd.EARLIEST_PICKUP_DATE,FND_API.G_MISS_DATE,NULL,p_delivery_detail_rec.EARLIEST_PICKUP_DATE),
1438 decode(p_delivery_detail_rec.LATEST_PICKUP_DATE,NULL,wdd.LATEST_PICKUP_DATE,FND_API.G_MISS_DATE,NULL,p_delivery_detail_rec.LATEST_PICKUP_DATE),
1439 decode(p_delivery_detail_rec.EARLIEST_DROPOFF_DATE,NULL,wdd.EARLIEST_DROPOFF_DATE,FND_API.G_MISS_DATE,NULL,p_delivery_detail_rec.EARLIEST_DROPOFF_DATE),
1440 decode(p_delivery_detail_rec.LATEST_DROPOFF_DATE,NULL,wdd.LATEST_DROPOFF_DATE,FND_API.G_MISS_DATE,NULL,p_delivery_detail_rec.LATEST_DROPOFF_DATE),
1441 --decode(p_delivery_detail_rec.DEMAND_SATISFACTION_DATE,NULL,wdd.DEMAND_SATISFACTION_DATE,FND_API.G_MISS_DATE,NULL,p_delivery_detail_rec.DEMAND_SATISFACTION_DATE),
1442 decode(p_delivery_detail_rec.REQUEST_DATE_TYPE_CODE,NULL,wdd.REQUEST_DATE_TYPE_CODE,FND_API.G_MISS_CHAR,NULL,p_delivery_detail_rec.REQUEST_DATE_TYPE_CODE),
1443 decode(p_delivery_detail_rec.tp_delivery_detail_id,NULL,wdd.tp_delivery_detail_id,FND_API.G_MISS_CHAR,NULL,p_delivery_detail_rec.tp_delivery_detail_id),
1444 decode(p_delivery_detail_rec.SOURCE_DOCUMENT_TYPE_ID,NULL,wdd.SOURCE_DOCUMENT_TYPE_ID,FND_API.G_MISS_NUM,NULL,p_delivery_detail_rec.SOURCE_DOCUMENT_TYPE_ID),
1445 decode(p_delivery_detail_rec.service_level,NULL,wdd.service_level,FND_API.G_MISS_CHAR,NULL,p_delivery_detail_rec.service_level),
1446 decode(p_delivery_detail_rec.mode_of_transport,NULL,wdd.mode_of_transport,FND_API.G_MISS_CHAR,NULL,p_delivery_detail_rec.mode_of_transport),
1447 /*WSH: IB ASUTAR*/
1448 decode(p_delivery_detail_rec.po_revision_number,NULL,wdd.po_revision_number,FND_API.G_MISS_NUM,NULL,p_delivery_detail_rec.po_revision_number),
1449 decode(p_delivery_detail_rec.release_revision_number,NULL,wdd.release_revision_number,FND_API.G_MISS_NUM,NULL,p_delivery_detail_rec.release_revision_number),
1450 decode(p_delivery_detail_rec.replenishment_status,NULL,wdd.replenishment_status,FND_API.G_MISS_CHAR,NULL,p_delivery_detail_rec.replenishment_status) --bug# 6719369 (replenishment project)
1451 FROM WSH_DELIVERY_DETAILS wdd
1452 WHERE delivery_detail_id = p_delivery_detail_id;
1453
1454 --
1455 IF l_debug_on THEN
1456 WSH_DEBUG_SV.logmsg(l_module_name, 'AFTER INSERTING INTO WSH_DELIVERY_DETAILS' );
1457 END IF;
1458 --
1459
1460 x_dd_id_tab := l_dd_id_tab; -- unable to use RETURNING INTO .. BULK COLLECT above
1461
1462 -- DBI Project
1463 -- Insert of wsh_delivery_details, call DBI API after the insert.
1464 -- This API will also check for DBI Installed or not
1465 IF l_debug_on THEN
1466 WSH_DEBUG_SV.log(l_module_name,'Calling DBI API.Detail count-',x_dd_id_tab.count);
1467 END IF;
1468 WSH_INTEGRATION.DBI_Update_Detail_Log
1469 (p_delivery_detail_id_tab => x_dd_id_tab,
1470 p_dml_type => 'INSERT',
1471 x_return_status => l_dbi_rs);
1472
1473 IF l_debug_on THEN
1474 WSH_DEBUG_SV.log(l_module_name,'Return Status after DBI Call-',l_dbi_rs);
1475 END IF;
1476 IF l_dbi_rs = WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR THEN
1477 x_return_status := l_dbi_rs;
1478 -- just pass this return status to caller API
1479 END IF;
1480 -- End of Code for DBI Project
1481 --
1482 IF l_debug_on THEN
1483 WSH_DEBUG_SV.pop(l_module_name);
1484 END IF;
1485 --
1486
1487 EXCEPTION
1488 WHEN others THEN
1489 --
1490 IF l_debug_on THEN
1491 WSH_DEBUG_SV.logmsg(l_module_name, 'WHEN OTHERS IN CREATE_DD_FROM_OLD_BULK ' || SQLERRM );
1492 END IF;
1493 --
1494 x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR;
1495 wsh_util_core.default_handler('WSH_DELIVERY_DETAILS_PKG.CREATE_DD_FROM_OLD_BULK',l_module_name);
1496 --
1497 IF l_debug_on THEN
1498 WSH_DEBUG_SV.logmsg(l_module_name,'Unexpected error has occured. Oracle error message is '|| SQLERRM,WSH_DEBUG_SV.C_UNEXPEC_ERR_LEVEL);
1499 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:OTHERS');
1500 END IF;
1501 --
1502 END create_dd_from_old_bulk;
1503
1504 /***********************************
1505 CREATE_DD_FROM_OLD_BULK
1506 ***********************************/
1507 -- This API has been called to
1508 -- make a Bulk call for creating Delivery Assignments
1509 -- from Split API in WSHDDACB
1510
1511 --
1512 -- Procedure: Create_Delivery_Assignment_Bulk
1513 -- Parameters: All Attributes of a Delivery Assignment Record,
1514 -- Row_id out
1515 -- Delivery_Assignment_id out
1516 -- Return_Status out
1517 -- Description: This procedure will create a delivery_assignment
1518 -- It will return to the use the delivery_assignment_id
1519 -- if not provided as a parameter.
1520 --
1521
1522 PROCEDURE Create_Deliv_Assignment_bulk(
1523 p_delivery_assignments_info IN Delivery_Assignments_Rec_Type,
1524 p_num_of_rec IN NUMBER,
1525 p_dd_id_tab IN WSH_UTIL_CORE.id_tab_type,
1526 x_da_id_tab OUT NOCOPY WSH_UTIL_CORE.id_tab_type,
1527 x_return_status OUT NOCOPY VARCHAR2
1528 -- added new input parameter
1529 ) IS
1530
1531
1532 CURSOR C_Del_Assign_ID
1533 IS SELECT wsh_delivery_assignments_s.nextval
1534 FROM sys.dual;
1535
1536 l_row_count NUMBER;
1537 l_temp_id NUMBER;
1538
1539 l_da_id_tab WSH_UTIL_CORE.id_tab_type;
1540 l_da_info Delivery_Assignments_Rec_Type;
1541 others exception;
1542
1543 --
1544 l_debug_on BOOLEAN;
1545 --
1546 l_module_name CONSTANT VARCHAR2(100) := 'wsh.plsql.' || G_PKG_NAME || '.' || 'CREATE_DELIV_ASSIGNMENT_BULK';
1547 --
1548 BEGIN
1549 --
1550 --
1551 l_debug_on := WSH_DEBUG_INTERFACE.g_debug;
1552 --
1553 IF l_debug_on IS NULL
1554 THEN
1555 l_debug_on := WSH_DEBUG_SV.is_debug_enabled;
1556 END IF;
1557 --
1558 IF l_debug_on THEN
1559 WSH_DEBUG_SV.push(l_module_name);
1560 END IF;
1561 --
1562 x_return_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
1563
1564 FOR i in 1..p_num_of_rec
1565 LOOP
1566 OPEN C_Del_Assign_ID;
1567 FETCH C_Del_Assign_ID
1568 INTO l_da_id_tab(i);
1569 CLOSE C_Del_Assign_ID;
1570 END LOOP;
1571
1572 --
1573 IF l_debug_on THEN
1574 WSH_DEBUG_SV.logmsg(l_module_name, 'BEFORE DELIVERY ASSIGNMENT BULK INSERT'||l_da_id_tab.count);
1575 END IF;
1576 x_da_id_tab := l_da_id_tab;
1577
1578 FORALL i in 1..p_num_of_rec
1579 INSERT INTO wsh_delivery_assignments
1580 (delivery_id,
1581 parent_delivery_id,
1582 delivery_detail_id,
1583 parent_delivery_detail_id,
1584 creation_date,
1585 created_by,
1586 last_update_date,
1587 last_updated_by,
1588 last_update_login,
1589 program_application_id,
1590 program_id,
1591 program_update_date,
1592 request_id,
1593 active_flag,
1594 type,
1595 delivery_assignment_id
1596 ) VALUES (
1597 p_delivery_assignments_info.delivery_id,
1598 p_delivery_assignments_info.parent_delivery_id,
1599 p_dd_id_tab(i),
1600 p_delivery_assignments_info.parent_delivery_detail_id,
1601 SYSDATE,
1602 FND_GLOBAL.USER_ID,
1603 SYSDATE,
1604 FND_GLOBAL.USER_ID,
1605 FND_GLOBAL.USER_ID,
1606 NULL,
1607 NULL,
1608 NULL,
1609 p_delivery_assignments_info.request_id,
1610 p_delivery_assignments_info.active_flag,
1611 NVL(p_delivery_assignments_info.type, 'S'),
1612 l_da_id_tab(i)
1613 );
1614 --
1615 IF l_debug_on THEN
1616 WSH_DEBUG_SV.pop(l_module_name);
1617 END IF;
1618 --
1619 EXCEPTION
1620 WHEN others THEN
1621 x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR;
1622 wsh_util_core.default_handler('WSH_DELIVERY_DETAILS_PKG.CREATE_DELIV_ASSIGNMENT_BULK',l_module_name);
1623
1624 --
1625 IF l_debug_on THEN
1626 WSH_DEBUG_SV.logmsg(l_module_name,'Unexpected error has occured. Oracle error message is '|| SQLERRM,WSH_DEBUG_SV.C_UNEXPEC_ERR_LEVEL);
1627 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:OTHERS');
1628 END IF;
1629 --
1630 END Create_Deliv_Assignment_bulk;
1631 /************* End of BULK OPERATION *****************/
1632
1633 PROCEDURE create_new_detail_from_old(
1634 p_delivery_detail_rec IN WSH_GLBL_VAR_STRCT_GRP.Delivery_Details_Rec_Type,
1635 p_delivery_detail_id IN NUMBER,
1636 x_row_id OUT NOCOPY VARCHAR2,
1637 x_delivery_detail_id OUT NOCOPY NUMBER,
1638 x_return_status OUT NOCOPY VARCHAR2
1639 ) IS
1640
1641 l_container_name varchar2(50);
1642
1643 CURSOR C_Del_Detail_Rowid
1644 IS SELECT rowid
1645 FROM wsh_delivery_details
1646 WHERE delivery_detail_id = x_delivery_detail_id;
1647
1648 CURSOR C_Del_detail_ID
1649 IS
1650 SELECT wsh_delivery_details_s.nextval
1651 FROM sys.dual;
1652
1653 CURSOR C_Check_Detail_ID
1654 IS SELECT rowid
1655 FROM wsh_delivery_details
1656 WHERE delivery_detail_id = x_delivery_detail_id;
1657
1658 l_row_count NUMBER;
1659 l_temp_id NUMBER;
1660 l_dd_id_tab WSH_UTIL_CORE.id_tab_type;
1661 others exception;
1662
1663 --
1664 l_debug_on BOOLEAN;
1665 --
1666 l_module_name CONSTANT VARCHAR2(100) := 'wsh.plsql.' || G_PKG_NAME || '.' || 'CREATE_NEW_DETAIL_FROM_OLD';
1667 --
1668 BEGIN
1669 --
1670 --
1671 l_debug_on := WSH_DEBUG_INTERFACE.g_debug;
1672 --
1673 IF l_debug_on IS NULL
1674 THEN
1675 l_debug_on := WSH_DEBUG_SV.is_debug_enabled;
1676 END IF;
1677 --
1678 IF l_debug_on THEN
1679 WSH_DEBUG_SV.push(l_module_name);
1680 --
1681 WSH_DEBUG_SV.log(l_module_name,'P_DELIVERY_DETAIL_ID',P_DELIVERY_DETAIL_ID);
1682 WSH_DEBUG_SV.logmsg(l_module_name, 'INSIDE PROCEDURE CREATE NEW DETAIL FROM OLD CALLING BULK API' );
1683 END IF;
1684 --
1685 x_return_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
1686 -- make call to Bulk api from here
1687 WSH_DELIVERY_DETAILS_PKG.create_dd_from_old_bulk
1688 ( p_delivery_detail_rec => p_delivery_detail_rec,
1689 p_delivery_detail_id => p_delivery_detail_id,
1690 p_num_of_rec => 1,
1691 x_dd_id_tab => l_dd_id_tab,
1692 x_return_status => x_return_status
1693 );
1694
1695 x_delivery_detail_id := l_dd_id_tab(1);
1696
1697 --
1698 IF l_debug_on THEN
1699 WSH_DEBUG_SV.logmsg(l_module_name, 'AFTER INSERTING INTO WSH_DELIVERY_DETAILS' );
1700 END IF;
1701 --
1702 OPEN C_Del_Detail_Rowid;
1703 FETCH C_Del_Detail_Rowid INTO x_row_id;
1704 IF (C_Del_Detail_Rowid%NOTFOUND) THEN
1705 CLOSE C_Del_Detail_Rowid;
1706 RAISE others;
1707 END IF;
1708 CLOSE C_Del_Detail_Rowid;
1709
1710 --
1711 IF l_debug_on THEN
1712 WSH_DEBUG_SV.pop(l_module_name);
1713 END IF;
1714 --
1715 EXCEPTION
1716 WHEN others THEN
1717 --
1718 IF l_debug_on THEN
1719 WSH_DEBUG_SV.logmsg(l_module_name, 'WHEN OTHERS IN CREATE_NEW_DETAIL_FROM_OLD ' || SQLERRM );
1720 END IF;
1721 --
1722 x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR;
1723 wsh_util_core.default_handler('WSH_DELIVERY_DETAILS_PKG.CREATE_NEW_DETAIL_FROM_OLD',l_module_name);
1724 --
1725 IF l_debug_on THEN
1726 WSH_DEBUG_SV.logmsg(l_module_name,'Unexpected error has occured. Oracle error message is '|| SQLERRM,WSH_DEBUG_SV.C_UNEXPEC_ERR_LEVEL);
1727 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:OTHERS');
1728 END IF;
1729 --
1730 END create_new_detail_from_old;
1731
1732 --
1733 -- Procedure: Delete_Delivery_Detail
1734 -- Parameters: All Attributes of a Delivery Detail Record
1735 -- Description: This procedure will delete a delivery detail.
1736 -- Since this is a table handler no validations are done at this
1737 -- level. The calling routine should make sure that the delete
1738 -- action is valid for the respective details being deleted.
1739 --
1740
1741 PROCEDURE Delete_Delivery_Details (
1742 p_rowid IN VARCHAR2 := NULL,
1743 p_delivery_detail_id IN NUMBER := NULL,
1744 p_cancel_flag IN VARCHAR2 DEFAULT NULL,
1745 x_return_status OUT NOCOPY VARCHAR2
1746 ) IS
1747
1748
1749 CURSOR get_del_detail(v_rowid VARCHAR2)
1750 IS
1751 SELECT wdd.delivery_detail_id,
1752 wdd.inventory_item_id,
1753 wdd.organization_id,
1754 wdd.subinventory,
1755 wdd.serial_number,
1756 wdd.container_flag, -- bug 4416863
1757 wdd.transaction_temp_id,
1758 wda.delivery_id,
1759 nvl(wdd.ignore_for_planning, 'N'), -- OTM R12
1760 nvl(wdd.gross_weight, 0) -- OTM R12 : packing ECO
1761 FROM wsh_delivery_details wdd, wsh_delivery_assignments wda
1762 WHERE wdd.delivery_detail_id = wda.delivery_detail_id
1763 and wdd.rowid = v_rowid;
1764
1765 CURSOR get_del_detail_by_id(v_delivery_detail_id NUMBER) IS
1766 SELECT wdd.delivery_detail_id,
1767 wdd.inventory_item_id,
1768 wdd.organization_id,
1769 wdd.subinventory,
1770 wdd.serial_number,
1771 wdd.container_flag, -- bug 4416863
1772 wdd.transaction_temp_id,
1773 wda.delivery_id,
1774 nvl(wdd.ignore_for_planning, 'N'), -- OTM R12
1775 nvl(wdd.gross_weight, 0) -- OTM R12 : packing ECO
1776 FROM wsh_delivery_details wdd, wsh_delivery_assignments wda
1777 WHERE wdd.delivery_detail_id = wda.delivery_detail_id
1778 and wdd.delivery_detail_id = v_delivery_detail_id;
1779
1780 l_delivery_detail_id NUMBER := NULL;
1781
1782
1783 l_inventory_item_id NUMBER := NULL;
1784 l_organization_id NUMBER := NULL;
1785 l_subinventory VARCHAR2(10) := NULL;
1786 l_serial_number VARCHAR2(30) := NULL;
1787 l_transaction_temp_id NUMBER := NULL;
1788 l_inv_controls_rec WSH_DELIVERY_DETAILS_INV.inv_control_flag_rec;
1789 l_return_status VARCHAR2(5) := NULL;
1790
1791 l_detail_tab WSH_UTIL_CORE.id_tab_type; -- DBI changes
1792 l_dbi_rs VARCHAR2(1); -- Return Status from DBI API
1793 l_dbi_dml_type VARCHAR2(10);
1794
1795 -- bug 4416863
1796 l_container_flag VARCHAR2(1);
1797 l_delivery_id NUMBER := NULL;
1798 -- end bug
1799
1800 -- OTM R12
1801 l_delivery_id_tab WSH_UTIL_CORE.ID_TAB_TYPE;
1802 l_interface_flag_tab WSH_UTIL_CORE.COLUMN_TAB_TYPE;
1803 l_ignore WSH_DELIVERY_DETAILS.IGNORE_FOR_PLANNING%TYPE;
1804 l_is_delivery_empty VARCHAR2(1);
1805 l_gc3_is_installed VARCHAR2(1);
1806 l_gross_weight WSH_DELIVERY_DETAILS.GROSS_WEIGHT%TYPE; -- packing ECO
1807 l_call_update VARCHAR2(1);
1808 -- end of OTM R12
1809
1810 --
1811 l_debug_on BOOLEAN;
1812 --
1813 l_module_name CONSTANT VARCHAR2(100) := 'wsh.plsql.' || G_PKG_NAME || '.' || 'DELETE_DELIVERY_DETAILS';
1814 --
1815 BEGIN
1816 --
1817 --
1818 l_debug_on := WSH_DEBUG_INTERFACE.g_debug;
1819 --
1820 IF l_debug_on IS NULL THEN
1821 l_debug_on := WSH_DEBUG_SV.is_debug_enabled;
1822 END IF;
1823 --
1824 IF l_debug_on THEN
1825 WSH_DEBUG_SV.push(l_module_name);
1826 --
1827 WSH_DEBUG_SV.log(l_module_name,'P_ROWID',P_ROWID);
1828 WSH_DEBUG_SV.log(l_module_name,'P_DELIVERY_DETAIL_ID',P_DELIVERY_DETAIL_ID);
1829 WSH_DEBUG_SV.log(l_module_name,'P_CANCEL_FLAG',P_CANCEL_FLAG);
1830 END IF;
1831 --
1832 x_return_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
1833
1834 -- OTM R12
1835 l_gc3_is_installed := WSH_UTIL_CORE.G_GC3_IS_INSTALLED;
1836 IF l_gc3_is_installed IS NULL THEN
1837 l_gc3_is_installed := WSH_UTIL_CORE.GC3_IS_INSTALLED;
1838 END IF;
1839
1840 IF l_debug_on THEN
1841 WSH_DEBUG_SV.log(l_module_name,'l_gc3_is_installed',l_gc3_is_installed);
1842 END IF;
1843 -- End of OTM R12
1844
1845 IF p_rowid IS NOT NULL THEN
1846 OPEN get_del_detail(p_rowid);
1847 FETCH get_del_detail
1848 INTO l_delivery_detail_id, l_inventory_item_id,
1849 l_organization_id, l_subinventory,
1850 l_serial_number, l_container_flag, l_transaction_temp_id,
1851 l_delivery_id, l_ignore, -- OTM R12 : l_ignore is added
1852 l_gross_weight; -- OTM R12 : packing ECO
1853 CLOSE get_del_detail;
1854 ELSIF p_delivery_detail_id IS NOT NULL THEN
1855 OPEN get_del_detail_by_id(p_delivery_detail_id);
1856 FETCH get_del_detail_by_id
1857 INTO l_delivery_detail_id, l_inventory_item_id,
1858 l_organization_id, l_subinventory ,
1859 l_serial_number, l_container_flag, l_transaction_temp_id,
1860 l_delivery_id, l_ignore, -- OTM R12 : l_ignore is added
1861 l_gross_weight; -- OTM R12 : packing ECO
1862 CLOSE get_del_detail_by_id;
1863 END IF;
1864 IF l_debug_on THEN
1865 WSH_DEBUG_SV.log(l_module_name,'l_delivery_detail_id',l_delivery_detail_id);
1866 WSH_DEBUG_SV.log(l_module_name,'l_inventory_item_id',l_inventory_item_id);
1867 WSH_DEBUG_SV.log(l_module_name,'l_organization_id',l_organization_id);
1868 WSH_DEBUG_SV.log(l_module_name,'l_subinventory', l_subinventory);
1869 WSH_DEBUG_SV.log(l_module_name,'l_serial_number', l_serial_number);
1870 WSH_DEBUG_SV.log(l_module_name,'l_container_flag', l_container_flag);
1871 WSH_DEBUG_SV.log(l_module_name,'l_transaction_temp_id',l_transaction_temp_id);
1872 WSH_DEBUG_SV.log(l_module_name,'l_delivery_id', l_delivery_id);
1873 WSH_DEBUG_SV.log(l_module_name,'l_ignore', l_ignore);
1874 WSH_DEBUG_SV.log(l_module_name,'l_gross_weight', l_gross_weight);
1875 END IF;
1876
1877 IF l_delivery_detail_id IS NOT NULL THEN
1878 IF l_serial_number IS NOT NULL OR l_transaction_temp_id IS NOT NULL THEN
1879 --
1880 IF l_debug_on THEN
1881 WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_DELIVERY_DETAILS_INV.FETCH_INV_CONTROLS',WSH_DEBUG_SV.C_PROC_LEVEL);
1882 END IF;
1883 --
1884 WSH_DELIVERY_DETAILS_INV.Fetch_Inv_Controls(
1885 p_delivery_detail_id => l_delivery_detail_id,
1886 p_inventory_item_id => l_inventory_item_id,
1887 p_organization_id => l_organization_id,
1888 p_subinventory => l_subinventory,
1889 x_inv_controls_rec => l_inv_controls_rec,
1890 x_return_status => l_return_status);
1891
1892 --
1893 IF l_debug_on THEN
1894 WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_DELIVERY_DETAILS_INV.UNMARK_SERIAL_NUMBER',WSH_DEBUG_SV.C_PROC_LEVEL);
1895 END IF;
1896 --
1897 WSH_DELIVERY_DETAILS_INV.Unmark_Serial_Number(
1898 p_delivery_detail_id => l_delivery_detail_id,
1899 p_serial_number_code => l_inv_controls_rec.serial_code,
1900 p_serial_number => l_serial_number,
1901 p_transaction_temp_id => l_transaction_temp_id,
1902 x_return_status => l_return_status);
1903
1904 END IF;
1905
1906 DELETE FROM wsh_freight_costs
1907 WHERE delivery_detail_id = l_delivery_detail_id;
1908 IF l_debug_on THEN
1909 WSH_DEBUG_SV.log(l_module_name,'Rows deleted',SQL%ROWCOUNT);
1910 END IF;
1911
1912 -- bug 4416863
1913 IF (l_delivery_id is not null) THEN
1914 IF l_debug_on THEN
1915 WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit wsh_delivery_details_actions.unassign_detail_from_delivery', WSH_DEBUG_SV.C_PROC_LEVEL);
1916 END IF;
1917 wsh_delivery_details_actions.unassign_detail_from_delivery (
1918 p_detail_id => l_delivery_detail_id,
1919 x_return_status => l_return_status);
1920 IF (l_return_status = WSH_UTIL_CORE.G_RET_STS_ERROR) OR
1921 (l_return_status = WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR) THEN
1922 --
1923 x_return_status := l_return_status;
1924 WSH_UTIL_CORE.Add_Message(x_return_status);
1925 IF l_debug_on THEN
1926 WSH_DEBUG_SV.log(l_module_name,'Return Status',x_return_status);
1927 WSH_DEBUG_SV.pop(l_module_name);
1928 END IF;
1929 return;
1930 END IF;
1931 END IF;
1932 -- end bug 4416863
1933
1934 IF p_cancel_flag = 'Y' THEN
1935 UPDATE wsh_delivery_details
1936 SET move_order_line_id = NULL ,
1937 released_status = 'D',
1938 requested_quantity = 0,
1939 requested_quantity2 = 0,
1940 src_requested_quantity = 0,
1941 src_requested_quantity2 = 0,
1942 cancelled_quantity = requested_quantity,
1943 cancelled_quantity2 = requested_quantity2,
1944 cycle_count_quantity = NULL,
1945 cycle_count_quantity2 = NULL,
1946 shipped_quantity = NULL,
1947 shipped_quantity2 = NULL,
1948 picked_quantity = NULL,
1949 picked_quantity2 = NULL,
1950 subinventory = NULL,
1951 inv_interfaced_flag = NULL,
1952 oe_interfaced_flag = NULL,
1953 locator_id = NULL,
1954 preferred_grade = NULL,
1955 -- HW OPMCONV - No need for sublot_number
1956 -- sublot_number = NULL,
1957 lot_number = NULL,
1958 revision = null ,
1959 tracking_number = NULL,
1960 -- J: W/V Changes
1961 gross_weight = 0,
1962 net_weight = 0,
1963 volume = 0
1964 WHERE delivery_detail_id = l_delivery_detail_id;
1965 IF l_debug_on THEN
1966 WSH_DEBUG_SV.log(l_module_name,'Rows Updated',SQL%ROWCOUNT);
1967 END IF;
1968 l_dbi_dml_type := 'UPDATE';
1969 ELSE -- else of cancel_flag='Y'
1970 DELETE FROM wsh_delivery_details
1971 WHERE delivery_detail_id = l_delivery_detail_id;
1972 IF l_debug_on THEN
1973 WSH_DEBUG_SV.log(l_module_name,'Rows deleted from wsh_delivery_details',SQL%ROWCOUNT);
1974 END IF;
1975 l_dbi_dml_type := 'DELETE';
1976
1977 DELETE FROM wsh_delivery_assignments
1978 WHERE delivery_detail_id = l_delivery_detail_id;
1979 IF l_debug_on THEN
1980 WSH_DEBUG_SV.log(l_module_name,'Rows deleted from wsh_delivery_assignments',SQL%ROWCOUNT);
1981 END IF;
1982
1983 -- OTM R12
1984 -- packing ECO : l_container_flag = 'N' is removed from the if condition
1985 IF (l_gc3_is_installed = 'Y' AND
1986 l_delivery_id IS NOT NULL AND
1987 l_ignore = 'N') THEN
1988
1989 l_call_update := 'Y';
1990 l_delivery_id_tab(1) := l_delivery_id;
1991 l_is_delivery_empty := WSH_NEW_DELIVERY_ACTIONS.IS_DELIVERY_EMPTY(l_delivery_id);
1992 IF (l_is_delivery_empty = WSH_UTIL_CORE.G_RET_STS_ERROR) THEN
1993 x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
1994 IF l_debug_on THEN
1995 WSH_DEBUG_SV.logmsg(l_module_name,'Error in WSH_NEW_DELIVERY_ACTIONS.IS_DELIVERY_EMPTY');
1996 WSH_DEBUG_SV.pop(l_module_name);
1997 END IF;
1998 RETURN;
1999 ELSIF (l_is_delivery_empty = 'Y') THEN
2000 l_interface_flag_tab(1) := WSH_NEW_DELIVERIES_PVT.C_TMS_DELETE_REQUIRED;
2001 ELSIF (l_is_delivery_empty = 'N') THEN
2002 IF (l_gross_weight = 0) THEN
2003 -- do not update if assign/unassign 0/NULL weight line/LPN to
2004 -- nonempty delivery
2005 l_call_update := 'N';
2006 ELSE
2007 l_interface_flag_tab(1) := NULL;
2008 --update tms interface_flag procedure will take care of the status
2009 END IF;
2010 END IF;
2011
2012 IF l_debug_on THEN
2013 WSH_DEBUG_SV.log(l_module_name, 'l_call_update', l_call_update);
2014 IF (l_call_update = 'Y') THEN
2015 WSH_DEBUG_SV.log(l_module_name, 'l_delivery_id_tab', l_delivery_id_tab(1));
2016 WSH_DEBUG_SV.log(l_module_name, 'l_interface_flag_tab', l_interface_flag_tab(1));
2017 END IF;
2018 END IF;
2019
2020 IF (l_call_update = 'Y') THEN
2021 WSH_NEW_DELIVERIES_PVT.UPDATE_TMS_INTERFACE_FLAG(
2022 p_delivery_id_tab => l_delivery_id_tab,
2023 p_tms_interface_flag_tab => l_interface_flag_tab,
2024 x_return_status => l_return_status);
2025
2026 IF (l_return_status IN (WSH_UTIL_CORE.G_RET_STS_ERROR, WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR)) THEN
2027 --
2028 x_return_status := l_return_status;
2029 IF l_debug_on THEN
2030 WSH_DEBUG_SV.logmsg(l_module_name,'Error in WSH_NEW_DELIVERIES_PVT.UPDATE_TMS_INTERFACE_FLAG');
2031 WSH_DEBUG_SV.log(l_module_name,'l_return_status', l_return_status);
2032 WSH_DEBUG_SV.pop(l_module_name);
2033 END IF;
2034 RETURN;
2035 END IF;
2036 END IF;
2037
2038
2039 END IF;
2040 -- End of OTM R12
2041
2042 END IF;-- end of cancel_flag='Y'
2043
2044 -- DBI Call for either update/delete using l_delivery_detail_id
2045 -- DBI Project
2046 -- Update of wsh_delivery_details where requested_quantity/released_status
2047 -- are changed, call DBI API after the update.
2048 -- This API will also check for DBI Installed or not
2049 IF l_debug_on THEN
2050 WSH_DEBUG_SV.log(l_module_name,'Calling DBI API.Detail id-',l_delivery_detail_id);
2051 END IF;
2052 l_detail_tab(1) := l_delivery_detail_id;
2053 WSH_INTEGRATION.DBI_Update_Detail_Log
2054 (p_delivery_detail_id_tab => l_detail_tab,
2055 p_dml_type => l_dbi_dml_type,
2056 x_return_status => l_dbi_rs);
2057
2058 IF l_debug_on THEN
2059 WSH_DEBUG_SV.log(l_module_name,'Return Status after DBI Call-',l_dbi_rs);
2060 END IF;
2061 IF l_dbi_rs = WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR THEN
2062 x_return_status := l_dbi_rs;
2063 -- just pass this return status to caller API
2064 END IF;
2065 -- End of Code for DBI Project
2066 --
2067
2068 END IF; -- l_delivery_detail_id is not null
2069
2070 --
2071 IF l_debug_on THEN
2072 WSH_DEBUG_SV.pop(l_module_name);
2073 END IF;
2074 --
2075 EXCEPTION
2076 WHEN others THEN
2077 x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR;
2078 wsh_util_core.default_handler('WSH_DELIVERY_DETAILS_PKG.DELETE_DELIVERY_DETAILS',l_module_name);
2079
2080 -- close all cursors at exception
2081 IF (get_del_detail%ISOPEN) THEN
2082 CLOSE get_del_detail;
2083 END IF;
2084
2085 IF (get_del_detail_by_id%ISOPEN) THEN
2086 CLOSE get_del_detail_by_id;
2087 END IF;
2088
2089 --
2090 IF l_debug_on THEN
2091 WSH_DEBUG_SV.logmsg(l_module_name,'Unexpected error has occured. Oracle error message is '|| SQLERRM,WSH_DEBUG_SV.C_UNEXPEC_ERR_LEVEL);
2092 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:OTHERS');
2093 END IF;
2094 --
2095 END Delete_Delivery_Details;
2096
2097
2098 --
2099 -- Procedure: Lock_Delivery_Details
2100 -- Parameters: All Attributes of a Delivery Detail Record
2101 -- Description: This procedure will lock a delivery detail
2102 -- record. It is specifically designed for
2103 -- use by the form.
2104 --
2105
2106 PROCEDURE Lock_Delivery_Details(
2107 p_rowid IN VARCHAR2,
2108 p_delivery_details_info IN WSH_GLBL_VAR_STRCT_GRP.Delivery_Details_Rec_Type)
2109 IS
2110
2111 CURSOR C_LOCK_DELIVERY_DETAIL IS
2112 SELECT *
2113 FROM WSH_DELIVERY_DETAILS
2114 WHERE ROWID = p_rowid
2115 FOR UPDATE OF DELIVERY_DETAIL_ID NOWAIT;
2116 recinfo C_LOCK_DELIVERY_DETAIL%ROWTYPE;
2117
2118 --
2119 l_debug_on BOOLEAN;
2120 --
2121 l_module_name CONSTANT VARCHAR2(100) := 'wsh.plsql.' || G_PKG_NAME || '.' || 'LOCK_DELIVERY_DETAILS';
2122 --
2123 BEGIN
2124
2125 --
2126 --
2127 l_debug_on := WSH_DEBUG_INTERFACE.g_debug;
2128 --
2129 IF l_debug_on IS NULL
2130 THEN
2131 l_debug_on := WSH_DEBUG_SV.is_debug_enabled;
2132 END IF;
2133 --
2134 IF l_debug_on THEN
2135 WSH_DEBUG_SV.push(l_module_name);
2136 --
2137 WSH_DEBUG_SV.log(l_module_name,'P_ROWID',P_ROWID);
2138 END IF;
2139 --
2140 OPEN C_LOCK_DELIVERY_DETAIL;
2141 FETCH C_LOCK_DELIVERY_DETAIL INTO recinfo;
2142 IF (C_LOCK_DELIVERY_DETAIL%NOTFOUND) THEN
2143 CLOSE C_LOCK_DELIVERY_DETAIL;
2144 FND_MESSAGE.Set_Name('FND', 'FORM_RECORD_DELETED');
2145 IF l_debug_on THEN
2146 WSH_DEBUG_SV.log(l_module_name,'FORM_RECORD_DELETED');
2147 END IF;
2148 app_exception.raise_exception;
2149 END IF;
2150 CLOSE C_LOCK_DELIVERY_DETAIL;
2151
2152 if ((recinfo.source_code = p_delivery_details_info.SOURCE_CODE)
2153 and
2154 ((recinfo.source_header_id = p_delivery_details_info.SOURCE_HEADER_ID)
2155 or
2156 ((recinfo.source_header_id is null)
2157 and (p_delivery_details_info.source_header_id is null)))
2158
2159 and
2160 (recinfo.source_line_id = p_delivery_details_info.SOURCE_LINE_ID)
2161 and
2162 ((recinfo.customer_id = p_delivery_details_info.CUSTOMER_ID)
2163 or
2164 ((recinfo.customer_id is null)
2165 and (p_delivery_Details_info.customer_id is NULL)))
2166 and
2167 ((recinfo.sold_to_contact_id = p_delivery_details_info.SOLD_TO_CONTACT_ID)
2168 or
2169 ((recinfo.sold_to_contact_id is null)
2170 and (p_delivery_details_info.SOLD_TO_CONTACT_ID is null)))
2171
2172 and
2173 ((recinfo.inventory_item_id = p_delivery_details_info.INVENTORY_ITEM_ID)
2174 or
2175 ((recinfo.inventory_item_id is null)
2176 and (p_delivery_details_info.INVENTORY_ITEM_ID is null)))
2177 and
2178 ((recinfo.item_description = p_delivery_details_info.ITEM_DESCRIPTION)
2179 or
2180 ((recinfo.item_description is null)
2181 and (p_delivery_details_info.ITEM_DESCRIPTION is null)))
2182
2183 and
2184 ((recinfo.hazard_class_id = p_delivery_details_info.HAZARD_CLASS_ID)
2185 or
2186 ((recinfo.hazard_class_id is null)
2187 and (p_delivery_details_info.hazard_class_id is null)))
2188 and
2189 ((recinfo.country_of_origin = p_delivery_details_info.COUNTRY_OF_ORIGIN)
2190 or
2191 ((recinfo.country_of_origin is null)
2192 and (p_delivery_details_info.COUNTRY_OF_ORIGIN is null)))
2193
2194 and
2195 ((recinfo.classification = p_delivery_details_info.classification)
2196 or
2197 ((recinfo.classification is null)
2198 and (p_delivery_details_info.classification is null)))
2199 and (recinfo.ship_from_location_id = p_delivery_details_info.SHIP_FROM_LOCATION_ID)
2200 and ((recinfo.ship_to_location_id = p_delivery_details_info.SHIP_TO_LOCATION_ID)
2201 or
2202 ((recinfo.ship_to_location_id is null)
2203 and (p_delivery_details_info.SHIP_TO_LOCATION_ID is null)))
2204
2205 and
2206 ((recinfo.ship_to_contact_id = p_delivery_details_info.SHIP_TO_CONTACT_ID)
2207 or
2208 ((recinfo.ship_to_contact_id is null)
2209 and (p_delivery_details_info.SHIP_TO_CONTACT_ID is null)))
2210 and ((recinfo.deliver_to_location_id = p_delivery_details_info.DELIVER_TO_LOCATION_ID)
2211 or
2212 ((recinfo.deliver_to_location_id is null)
2213 and (p_delivery_details_info.DELIVER_TO_LOCATION_ID is null)))
2214
2215 and ((recinfo.deliver_to_contact_id = p_delivery_details_info.DELIVER_TO_CONTACT_ID)
2216 or
2217 ((recinfo.deliver_to_contact_id is null)
2218 and (p_delivery_details_info.DELIVER_TO_CONTACT_ID is null)))
2219
2220
2221 and
2222 ((recinfo.intmed_ship_to_contact_id = p_delivery_details_info.INTMED_SHIP_TO_CONTACT_ID)
2223 or
2224 ((recinfo.intmed_ship_to_contact_id is null)
2225 and (p_delivery_details_info.INTMED_SHIP_TO_CONTACT_ID is null)))
2226 and
2227 ((recinfo.intmed_ship_to_location_id = p_delivery_details_info.INTMED_SHIP_TO_LOCATION_ID)
2228 or
2229 ((recinfo.intmed_ship_to_location_id is null)
2230 and (p_delivery_details_info.INTMED_SHIP_TO_LOCATION_ID is null)))
2231
2232 and
2233 ((recinfo.ship_tolerance_above = p_delivery_details_info.SHIP_TOLERANCE_ABOVE)
2234 or
2235 ((recinfo.ship_tolerance_above is null)
2236 and (p_delivery_details_info.SHIP_TOLERANCE_ABOVE is null)))
2237 and
2238 ((recinfo.ship_tolerance_below = p_delivery_details_info.SHIP_TOLERANCE_BELOW)
2239 or
2240 ((recinfo.ship_tolerance_below is null)
2241 and (p_delivery_details_info.SHIP_TOLERANCE_BELOW is null)))
2242 and
2243 (recinfo.created_by = p_delivery_details_info.CREATED_BY)
2244 and
2245 (recinfo.creation_date = p_delivery_details_info.CREATION_DATE)
2246 and
2247 (recinfo.last_update_date = p_delivery_details_info.LAST_UPDATE_DATE)
2248 and
2249 ((recinfo.last_update_login = p_delivery_details_info.LAST_UPDATE_LOGIN)
2250 or
2251 ((recinfo.last_update_login is null)
2252 and (p_delivery_details_info.LAST_UPDATE_LOGIN is null)))
2253 and
2254 (recinfo.last_updated_by = p_delivery_details_info.LAST_UPDATED_BY)
2255 and
2256 ((recinfo.program_application_id = p_delivery_details_info.PROGRAM_APPLICATION_ID)
2257 or
2258 ((recinfo.program_application_id is null)
2259 and (p_delivery_details_info.PROGRAM_APPLICATION_ID is null)))
2260
2261 and ((recinfo.program_id = p_delivery_details_info.PROGRAM_ID)
2262 or
2263 ((recinfo.program_id is null)
2264 and (p_delivery_details_info.PROGRAM_ID is null)))
2265 and
2266 ((recinfo.program_update_date = p_delivery_details_info.PROGRAM_UPDATE_DATE)
2267 or
2268 ((recinfo.program_update_date is null)
2269 and (p_delivery_details_info.PROGRAM_UPDATE_DATE is null)))
2270 and
2271 ((recinfo.request_id = p_delivery_details_info.REQUEST_ID)
2272 or
2273 ((recinfo.request_id is null)
2274 and (p_delivery_details_info.REQUEST_ID is null)))
2275 and
2276 (recinfo.requested_quantity = p_delivery_details_info.REQUESTED_QUANTITY)
2277 and
2278 ((recinfo.shipped_quantity = p_delivery_details_info.SHIPPED_QUANTITY)
2279 or
2280 ((recinfo.shipped_quantity is null)
2281 and (p_delivery_details_info.SHIPPED_QUANTITY is null)))
2282 and
2283 ((recinfo.delivered_quantity = p_delivery_details_info.DELIVERED_QUANTITY)
2284 or
2285 ((recinfo.delivered_quantity is null)
2286 and (p_delivery_details_info.DELIVERED_QUANTITY is null)))
2287 and
2288 (recinfo.requested_quantity_uom = p_delivery_details_info.REQUESTED_QUANTITY_UOM)
2289 and
2290 ((recinfo.subinventory= p_delivery_details_info.SUBINVENTORY)
2291 or
2292 ((recinfo.subinventory is null)
2293 and (p_delivery_details_info.SUBINVENTORY is null)))
2294 and
2295 ((recinfo.revision = p_delivery_details_info.REVISION)
2296 or
2297 ((recinfo.revision is null)
2298 and (p_delivery_details_info.REVISION is null)))
2299 and
2300 ((recinfo.lot_number = p_delivery_details_info.LOT_NUMBER)
2301 or
2302 ((recinfo.lot_number is null)
2303 and (p_delivery_details_info.LOT_NUMBER is null)))
2304 and
2305 ((recinfo.customer_requested_lot_flag = p_delivery_details_info.CUSTOMER_REQUESTED_LOT_FLAG)
2306 or
2307 ((recinfo.customer_requested_lot_flag is null)
2308 and (p_delivery_details_info.CUSTOMER_REQUESTED_LOT_FLAG is null)))
2309 and
2310 ((recinfo.serial_number = p_delivery_details_info.SERIAL_NUMBER)
2311 or
2312 ((recinfo.serial_number is null)
2313 and (p_delivery_details_info.SERIAL_NUMBER is null)))
2314 and
2315 ((recinfo.locator_id = p_delivery_details_info.locator_ID)
2316 or
2317 ((recinfo.locator_id is null)
2318 and (p_delivery_details_info.locator_ID is null)))
2319 and
2320 ((recinfo.date_requested = p_delivery_details_info.DATE_REQUESTED)
2321 or
2322 ((recinfo.date_requested is null)
2323 and (p_delivery_details_info.DATE_REQUESTED is null)))
2324 and
2325 ((recinfo.date_scheduled = p_delivery_details_info.DATE_SCHEDULED)
2326 or
2327 ((recinfo.date_scheduled is null)
2328 and (p_delivery_details_info.DATE_SCHEDULED is null)))
2329 and
2330 ((recinfo.master_container_item_id = p_delivery_details_info.MASTER_CONTAINER_ITEM_ID)
2331 or
2332 ((recinfo.master_container_item_id is null)
2333 and (p_delivery_details_info.MASTER_CONTAINER_ITEM_ID is null)))
2334 and
2335 ((recinfo.detail_container_item_id = p_delivery_details_info.DETAIL_CONTAINER_ITEM_ID)
2336 or
2337 ((recinfo.detail_container_item_id is null)
2338 and (p_delivery_details_info.DETAIL_CONTAINER_ITEM_ID is null)))
2339 and
2340 ((recinfo.load_seq_number = p_delivery_details_info.LOAD_SEQ_NUMBER)
2341 or
2342 ((recinfo.load_seq_number is null)
2343 and (p_delivery_details_info.LOAD_SEQ_NUMBER is null)))
2344 and
2345 ((recinfo.ship_method_code = p_delivery_details_info.SHIP_METHOD_CODE)
2346 or
2347 ((recinfo.ship_method_code is null)
2348 and (p_delivery_details_info.SHIP_METHOD_CODE is null)))
2349 and
2350 ((recinfo.carrier_id = p_delivery_details_info.CARRIER_ID)
2351 or
2352 ((recinfo.carrier_id is null)
2353 and (p_delivery_details_info.CARRIER_ID is null)))
2354 and
2355 ((recinfo.freight_terms_code = p_delivery_details_info.FREIGHT_TERMS_CODE)
2356 or
2357 ((recinfo.freight_terms_code is null)
2358 and (p_delivery_details_info.FREIGHT_TERMS_CODE is null)))
2359 and
2360 ((recinfo.shipment_priority_code = p_delivery_details_info.SHIPMENT_PRIORITY_CODE)
2361 or
2362 ((recinfo.shipment_priority_code is null)
2363 and (p_delivery_details_info.SHIPMENT_PRIORITY_CODE is null)))
2364 and
2365 ((recinfo.fob_code = p_delivery_details_info.FOB_CODE)
2366 or
2367 ((recinfo.fob_code is null)
2368 and (p_delivery_details_info.FOB_CODE is null)))
2369 and
2370 ((recinfo.customer_item_id = p_delivery_details_info.CUSTOMER_ITEM_ID)
2371 or
2372 ((recinfo.customer_item_id is null)
2373 and (p_delivery_details_info.CUSTOMER_ITEM_ID is null)))
2374 and
2375 ((recinfo.dep_plan_required_flag = p_delivery_details_info.DEP_PLAN_REQUIRED_FLAG)
2376 or
2377 ((recinfo.dep_plan_required_flag is null)
2378 and (p_delivery_details_info.DEP_PLAN_REQUIRED_FLAG is null)))
2379 and
2380 ((recinfo.customer_prod_seq = p_delivery_details_info.CUSTOMER_PROD_SEQ)
2381 or
2382 ((recinfo.customer_prod_seq is null)
2383 and (p_delivery_details_info.CUSTOMER_PROD_SEQ is null)))
2384 and
2385 ((recinfo.customer_dock_code = p_delivery_details_info.CUSTOMER_DOCK_CODE)
2386 or
2387 ((recinfo.customer_dock_code is null)
2388 and (p_delivery_details_info.CUSTOMER_DOCK_CODE is null)))
2389 and
2390 ((recinfo.cust_model_serial_number = p_delivery_details_info.CUST_MODEL_SERIAL_NUMBER)
2391 or
2392 ((recinfo.cust_model_serial_number is null)
2393 and (p_delivery_details_info.CUST_MODEL_SERIAL_NUMBER is null)))
2394 and
2395 ((recinfo.customer_job = p_delivery_details_info.CUSTOMER_JOB)
2396 or
2397 ((recinfo.customer_job is null)
2398 and (p_delivery_details_info.CUSTOMER_JOB is null)))
2399 and
2400 ((recinfo.customer_production_line = p_delivery_details_info.CUSTOMER_PRODUCTION_LINE)
2401 or
2402 ((recinfo.customer_production_line is null)
2403 and (p_delivery_details_info.CUSTOMER_PRODUCTION_LINE is null)))
2404
2405 and
2406 ((recinfo.net_weight = p_delivery_details_info.NET_WEIGHT)
2407 or
2408 ((recinfo.net_weight is null)
2409 and (p_delivery_details_info.NET_WEIGHT is null)))
2410 and
2411 ((recinfo.weight_uom_code = p_delivery_details_info.WEIGHT_UOM_CODE)
2412 or
2413 ((recinfo.weight_uom_code is null)
2414 and (p_delivery_details_info.WEIGHT_UOM_CODE is null)))
2415 and
2416 ((recinfo.volume = p_delivery_details_info.VOLUME)
2417 or
2418 ((recinfo.volume is null)
2419 and (p_delivery_details_info.VOLUME is null)))
2420 and
2421 ((recinfo.volume_uom_code = p_delivery_details_info.VOLUME_UOM_CODE)
2422 or
2423 ((recinfo.volume_uom_code is null)
2424 and (p_delivery_details_info.VOLUME_UOM_CODE is null)))
2425
2426 and
2427 ((recinfo.tp_attribute_category = p_delivery_details_info.TP_ATTRIBUTE_CATEGORY)
2428 or
2429 ((recinfo.tp_attribute_category is null)
2430 and (p_delivery_details_info.TP_ATTRIBUTE_CATEGORY is null)))
2431
2432 and
2433 ((recinfo.tp_attribute1 = p_delivery_details_info.TP_ATTRIBUTE1)
2434 or
2435 ((recinfo.tp_attribute1 is null)
2436 and (p_delivery_details_info.TP_ATTRIBUTE1 is null)))
2437
2438 and
2439 ((recinfo.tp_attribute2 = p_delivery_details_info.TP_ATTRIBUTE2)
2440 or
2441 ((recinfo.tp_attribute2 is null)
2442 and (p_delivery_details_info.TP_ATTRIBUTE2 is null)))
2443 and
2444 ((recinfo.tp_attribute3 = p_delivery_details_info.TP_ATTRIBUTE3)
2445 or
2446 ((recinfo.tp_attribute3 is null)
2447 and (p_delivery_details_info.TP_ATTRIBUTE3 is null)))
2448 and
2449 ((recinfo.tp_attribute4 = p_delivery_details_info.TP_ATTRIBUTE4)
2450 or
2451 ((recinfo.tp_attribute4 is null)
2452 and (p_delivery_details_info.TP_ATTRIBUTE4 is null)))
2453
2454 and
2455 ((recinfo.tp_attribute5 = p_delivery_details_info.TP_ATTRIBUTE5)
2456 or
2457 ((recinfo.tp_attribute5 is null)
2458 and (p_delivery_details_info.TP_ATTRIBUTE5 is null)))
2459 and
2460 ((recinfo.tp_attribute6 = p_delivery_details_info.TP_ATTRIBUTE6)
2461 or
2462 ((recinfo.tp_attribute6 is null)
2463 and (p_delivery_details_info.TP_ATTRIBUTE6 is null)))
2464 and
2465 ((recinfo.tp_attribute7 = p_delivery_details_info.TP_ATTRIBUTE7)
2466 or
2467 ((recinfo.tp_attribute7 is null)
2468 and (p_delivery_details_info.TP_ATTRIBUTE7 is null)))
2469 and
2470 ((recinfo.tp_attribute8 = p_delivery_details_info.TP_ATTRIBUTE8)
2471 or
2472 ((recinfo.tp_attribute8 is null)
2473 and (p_delivery_details_info.TP_ATTRIBUTE8 is null)))
2474 and
2475 ((recinfo.tp_attribute9 = p_delivery_details_info.TP_ATTRIBUTE9)
2476 or
2477 ((recinfo.tp_attribute9 is null)
2478 and (p_delivery_details_info.TP_ATTRIBUTE9 is null)))
2479
2480 and
2481 ((recinfo.tp_attribute10 = p_delivery_details_info.TP_ATTRIBUTE10)
2482 or
2483 ((recinfo.tp_attribute10 is null)
2484 and (p_delivery_details_info.TP_ATTRIBUTE10 is null)))
2485 and
2486 ((recinfo.tp_attribute11 = p_delivery_details_info.TP_ATTRIBUTE11)
2487 or
2488 ((recinfo.tp_attribute11 is null)
2489 and (p_delivery_details_info.TP_ATTRIBUTE11 is null)))
2490 and
2491 ((recinfo.tp_attribute12 = p_delivery_details_info.TP_ATTRIBUTE12)
2492 or
2493 ((recinfo.tp_attribute12 is null)
2494 and (p_delivery_details_info.TP_ATTRIBUTE12 is null)))
2495 and
2496 ((recinfo.tp_attribute13 = p_delivery_details_info.TP_ATTRIBUTE13)
2497 or
2498 ((recinfo.tp_attribute13 is null)
2499 and (p_delivery_details_info.TP_ATTRIBUTE13 is null)))
2500
2501 and
2502 ((recinfo.tp_attribute14 = p_delivery_details_info.TP_ATTRIBUTE14)
2503 or
2504 ((recinfo.tp_attribute14 is null)
2505 and (p_delivery_details_info.TP_ATTRIBUTE14 is null)))
2506
2507
2508 and
2509 ((recinfo.tp_attribute15 = p_delivery_details_info.TP_ATTRIBUTE15)
2510 or
2511 ((recinfo.tp_attribute15 is null)
2512 and (p_delivery_details_info.TP_ATTRIBUTE15 is null)))
2513
2514 and
2515 ((recinfo.attribute_category = p_delivery_details_info.ATTRIBUTE_CATEGORY)
2516 or
2517 ((recinfo.attribute_category is null)
2518 and (p_delivery_details_info.ATTRIBUTE_CATEGORY is null)))
2519
2520 and
2521 ((recinfo.attribute1 = p_delivery_details_info.ATTRIBUTE1)
2522 or
2523 ((recinfo.attribute1 is null)
2524 and (p_delivery_details_info.ATTRIBUTE1 is null)))
2525 and
2526 ((recinfo.attribute_category = p_delivery_details_info.ATTRIBUTE_CATEGORY)
2527 or
2528 ((recinfo.attribute_category is null)
2529 and (p_delivery_details_info.ATTRIBUTE_CATEGORY is null)))
2530 and
2531 ((recinfo.attribute2 = p_delivery_details_info.ATTRIBUTE2)
2532 or
2533 ((recinfo.attribute2 is null)
2534 and (p_delivery_details_info.ATTRIBUTE2 is null)))
2535
2536 and
2537 ((recinfo.attribute3 = p_delivery_details_info.ATTRIBUTE3)
2538 or
2539 ((recinfo.attribute3 is null)
2540 and (p_delivery_details_info.ATTRIBUTE3 is null)))
2541 and
2542 ((recinfo.attribute4 = p_delivery_details_info.ATTRIBUTE4)
2543 or
2544 ((recinfo.attribute4 is null)
2545 and (p_delivery_details_info.ATTRIBUTE4 is null)))
2546
2547 and
2548 ((recinfo.attribute5 = p_delivery_details_info.ATTRIBUTE5)
2549 or
2550 ((recinfo.attribute5 is null)
2551 and (p_delivery_details_info.ATTRIBUTE5 is null)))
2552 and
2553 ((recinfo.attribute6 = p_delivery_details_info.ATTRIBUTE6)
2554 or
2555 ((recinfo.attribute6 is null)
2556 and (p_delivery_details_info.ATTRIBUTE6 is null)))
2557 and
2558 ((recinfo.attribute7 = p_delivery_details_info.ATTRIBUTE7)
2559 or
2560 ((recinfo.attribute7 is null)
2561 and (p_delivery_details_info.ATTRIBUTE7 is null)))
2562 and
2563 ((recinfo.attribute8 = p_delivery_details_info.ATTRIBUTE8)
2564 or
2565 ((recinfo.attribute8 is null)
2566 and (p_delivery_details_info.ATTRIBUTE8 is null)))
2567 and
2568 ((recinfo.attribute9 = p_delivery_details_info.ATTRIBUTE9)
2569 or
2570 ((recinfo.attribute9 is null)
2571 and (p_delivery_details_info.ATTRIBUTE9 is null)))
2572 and
2573 ((recinfo.attribute10 = p_delivery_details_info.ATTRIBUTE10)
2574 or
2575 ((recinfo.attribute10 is null)
2576 and (p_delivery_details_info.ATTRIBUTE10 is null)))
2577
2578 and
2579 ((recinfo.attribute11 = p_delivery_details_info.ATTRIBUTE11)
2580 or
2581 ((recinfo.attribute11 is null)
2582 and (p_delivery_details_info.ATTRIBUTE11 is null)))
2583 and
2584 ((recinfo.attribute12 = p_delivery_details_info.ATTRIBUTE12)
2585 or
2586 ((recinfo.attribute12 is null)
2587 and (p_delivery_details_info.ATTRIBUTE12 is null)))
2588 and
2589 ((recinfo.attribute13 = p_delivery_details_info.ATTRIBUTE13)
2590 or
2591 ((recinfo.attribute13 is null)
2592 and (p_delivery_details_info.ATTRIBUTE13 is null)))
2593 and
2594 ((recinfo.attribute14 = p_delivery_details_info.ATTRIBUTE14)
2595 or
2596 ((recinfo.attribute14 is null)
2597 and (p_delivery_details_info.ATTRIBUTE14 is null)))
2598 and
2599 ((recinfo.attribute15 = p_delivery_details_info.ATTRIBUTE15)
2600 or
2601 ((recinfo.attribute15 is null)
2602 and (p_delivery_details_info.ATTRIBUTE15 is null)))
2603 and
2604 ((recinfo.mvt_stat_status = p_delivery_details_info.mvt_stat_status)
2605 or
2606 ((recinfo.mvt_stat_status is null)
2607 and (p_delivery_details_info.mvt_stat_status is null)))
2608
2609 and
2610 ((recinfo.organization_id = p_delivery_details_info.organization_id)
2611 or
2612 ((recinfo.organization_id is null)
2613 and (p_delivery_details_info.organization_id is null)))
2614 and
2615 ((recinfo.org_id = p_delivery_details_info.org_id)
2616 or
2617 ((recinfo.org_id is null)
2618 and (p_delivery_details_info.org_id is null)))
2619 and
2620 ((recinfo.transaction_temp_id = p_delivery_details_info.transaction_temp_id)
2621 or
2622 ((recinfo.transaction_temp_id is null)
2623 and (p_delivery_details_info.transaction_temp_id is null)))
2624 and
2625 ((recinfo.ship_set_id = p_delivery_details_info.ship_set_id)
2626 or
2627 ((recinfo.ship_set_id is null)
2628 and (p_delivery_details_info.ship_set_id is null)))
2629 and
2630 ((recinfo.arrival_set_id = p_delivery_details_info.arrival_set_id)
2631 or
2632 ((recinfo.arrival_set_id is null)
2633 and (p_delivery_details_info.arrival_set_id is null)))
2634 and
2635 ((recinfo.ship_model_complete_flag = p_delivery_details_info.ship_model_complete_flag)
2636 or
2637 ((recinfo.ship_model_complete_flag is null)
2638 and (p_delivery_details_info.ship_model_complete_flag is null)))
2639 and
2640 ((recinfo.top_model_line_id = p_delivery_details_info.top_model_line_id)
2641 or
2642 ((recinfo.top_model_line_id is null)
2643 and (p_delivery_details_info.top_model_line_id is null)))
2644 and
2645 ((recinfo.hold_code = p_delivery_details_info.hold_code)
2646 or
2647 ((recinfo.hold_code is null)
2648 and (p_delivery_details_info.hold_code is null)))
2649 and
2650 ((recinfo.source_header_number = p_delivery_details_info.source_header_number)
2651 or
2652 ((recinfo.source_header_number is null)
2653 and (p_delivery_details_info.source_header_number is null)))
2654 and
2655 ((recinfo.source_header_type_id = p_delivery_details_info.source_header_type_id)
2656 or
2657 ((recinfo.source_header_type_id is null)
2658 and (p_delivery_details_info.source_header_type_id is null)))
2659
2660 and
2661 ((recinfo.source_header_type_name = p_delivery_details_info.source_header_type_name)
2662 or
2663 ((recinfo.source_header_type_name is null)
2664 and (p_delivery_details_info.source_header_type_name is null)))
2665 and
2666 ((recinfo.cust_po_number = p_delivery_details_info.cust_po_number)
2667 or
2668 ((recinfo.cust_po_number is null)
2669 and (p_delivery_details_info.cust_po_number is null)))
2670
2671 and
2672 ((recinfo.ato_line_id = p_delivery_details_info.ato_line_id)
2673 or
2674 ((recinfo.ato_line_id is null)
2675 and (p_delivery_details_info.ato_line_id is null)))
2676
2677 and
2678 ((recinfo.src_requested_quantity = p_delivery_details_info.src_requested_quantity)
2679 or
2680 ((recinfo.src_requested_quantity is null)
2681 and (p_delivery_details_info.src_requested_quantity is null)))
2682 and
2683 ((recinfo.src_requested_quantity_uom = p_delivery_details_info.src_requested_quantity_uom)
2684 or
2685 ((recinfo.src_requested_quantity_uom is null)
2686 and (p_delivery_details_info.src_requested_quantity_uom is null)))
2687 and
2688 ((recinfo.move_order_line_id = p_delivery_details_info.move_order_line_id)
2689 or
2690 ((recinfo.move_order_line_id is null)
2691 and (p_delivery_details_info.move_order_line_id is null)))
2692
2693 and
2694 ((recinfo.cancelled_quantity = p_delivery_details_info.cancelled_quantity)
2695 or
2696 ((recinfo.cancelled_quantity is null)
2697 and (p_delivery_details_info.cancelled_quantity is null)))
2698 and
2699 ((recinfo.hazard_class_id = p_delivery_details_info.hazard_class_id)
2700 or
2701 ((recinfo.hazard_class_id is null)
2702 and (p_delivery_details_info.hazard_class_id is null)))
2703
2704 and
2705 ((recinfo.quality_control_quantity = p_delivery_details_info.quality_control_quantity)
2706 or
2707 ((recinfo.quality_control_quantity is null)
2708 and (p_delivery_details_info.quality_control_quantity is null)))
2709 and
2710 ((recinfo.cycle_count_quantity = p_delivery_details_info.cycle_count_quantity)
2711 or
2712 ((recinfo.cycle_count_quantity is null)
2713 and (p_delivery_details_info.cycle_count_quantity is null)))
2714 and
2715 ((recinfo.tracking_number = p_delivery_details_info.tracking_number)
2716 or
2717 ((recinfo.tracking_number is null)
2718 and (p_delivery_details_info.tracking_number is null)))
2719
2720 and
2721 ((recinfo.movement_id = p_delivery_details_info.movement_id)
2722 or
2723 ((recinfo.movement_id is null)
2724 and (p_delivery_details_info.movement_id is null)))
2725
2726 and
2727 ((recinfo.shipping_instructions = p_delivery_details_info.shipping_instructions)
2728 or
2729 ((recinfo.shipping_instructions is null)
2730 and (p_delivery_details_info.shipping_instructions is null)))
2731
2732 and
2733 ((recinfo.packing_instructions = p_delivery_details_info.packing_instructions)
2734 or
2735 ((recinfo.packing_instructions is null)
2736 and (p_delivery_details_info.packing_instructions is null)))
2737 and
2738 ((recinfo.project_id = p_delivery_details_info.project_id)
2739 or
2740 ((recinfo.project_id is null)
2741 and (p_delivery_details_info.project_id is null)))
2742 and
2743 ((recinfo.task_id = p_delivery_details_info.task_id)
2744 or
2745 ((recinfo.task_id is null)
2746 and (p_delivery_details_info.task_id is null)))
2747 and
2748 ((recinfo.oe_interfaced_flag = p_delivery_details_info.oe_interfaced_flag)
2749 or
2750 ((recinfo.oe_interfaced_flag is null)
2751 and (p_delivery_details_info.oe_interfaced_flag is null)))
2752
2753 and
2754 ((recinfo.split_from_delivery_detail_id = p_delivery_details_info.split_from_detail_id)
2755 or
2756 ((recinfo.split_from_delivery_detail_id is null)
2757 and (p_delivery_details_info.split_from_detail_id is null)))
2758 and
2759 ((recinfo.inv_interfaced_flag = p_delivery_details_info.inv_interfaced_flag)
2760 or
2761 ((recinfo.inv_interfaced_flag is null)
2762 and (p_delivery_details_info.inv_interfaced_flag is null)))
2763
2764 and
2765 ((recinfo.source_line_number = p_delivery_details_info.source_line_number)
2766 or
2767 ((recinfo.source_line_number is null)
2768 and (p_delivery_details_info.source_line_number is null)))
2769 and
2770 ((recinfo.inspection_flag = p_delivery_details_info.inspection_flag)
2771 or
2772 ((recinfo.inspection_flag is null)
2773 and (p_delivery_details_info.inspection_flag is null)))
2774 and
2775 ((recinfo.released_status = p_delivery_details_info.released_status)
2776 or
2777 ((recinfo.released_status is null)
2778 and (p_delivery_details_info.released_status is null)))
2779
2780
2781 and
2782 (recinfo.delivery_detail_id = p_delivery_details_info.DELIVERY_detail_ID)
2783 and (recinfo.container_flag = p_delivery_details_info.container_flag)
2784 and ((recinfo.container_type_code = p_delivery_details_info.container_type_code)
2785 or
2786 ((recinfo.container_type_code is null)
2787 and (p_delivery_details_info.container_type_code is null)))
2788
2789 and ((recinfo.container_name = p_delivery_details_info.container_name)
2790 or
2791 ((recinfo.container_name is null)
2792 and (p_delivery_details_info.container_name is null)))
2793
2794 and ((recinfo.fill_percent = p_delivery_details_info.fill_percent)
2795 or
2796 ((recinfo.fill_percent is null)
2797 and (p_delivery_details_info.fill_percent is null)))
2798 and ((recinfo.gross_weight = p_delivery_details_info.gross_weight)
2799 or
2800 ((recinfo.gross_weight is null)
2801 and (p_delivery_details_info.gross_weight is null)))
2802 and ((recinfo.master_serial_number = p_delivery_details_info.master_serial_number)
2803 or
2804 ((recinfo.master_serial_number is null)
2805 and (p_delivery_details_info.master_serial_number is null)))
2806 and ((recinfo.maximum_load_weight = p_delivery_details_info.maximum_load_weight)
2807 or
2808 ((recinfo.maximum_load_weight is null)
2809 and (p_delivery_details_info.maximum_load_weight is null)))
2810 and ((recinfo.maximum_volume = p_delivery_details_info.maximum_volume)
2811 or
2812 ((recinfo.maximum_volume is null)
2813 and (p_delivery_details_info.maximum_volume is null)))
2814 and ((recinfo.minimum_fill_percent = p_delivery_details_info.minimum_fill_percent)
2815 or
2816 ((recinfo.minimum_fill_percent is null)
2817 and (p_delivery_details_info.minimum_fill_percent is null)))
2818 and ((recinfo.seal_code = p_delivery_details_info.seal_code)
2819 or
2820 ((recinfo.seal_code is null)
2821 and (p_delivery_details_info.seal_code is null)))
2822 and ((recinfo.unit_number = p_delivery_details_info.unit_number)
2823 or
2824 ((recinfo.unit_number is null)
2825 and (p_delivery_details_info.unit_number is null)))
2826 and ((recinfo.unit_price = p_delivery_details_info.unit_price)
2827 or
2828 ((recinfo.unit_price is null)
2829 and (p_delivery_details_info.unit_price is null)))
2830 and ((recinfo.currency_code = p_delivery_details_info.currency_code)
2831 or
2832 ((recinfo.currency_code is null)
2833 and (p_delivery_details_info.currency_code is null)))
2834 and ((recinfo.freight_class_cat_id = p_delivery_details_info.freight_class_cat_id)
2835 or
2836 ((recinfo.freight_class_cat_id is null)
2837 and (p_delivery_details_info.freight_class_cat_id is null)))
2838 and ((recinfo.commodity_code_cat_id = p_delivery_details_info.commodity_code_cat_id)
2839 or
2840 ((recinfo.commodity_code_cat_id is null)
2841 and (p_delivery_details_info.commodity_code_cat_id is null)))
2842
2843 /* OPM 09/11/00 added OPM attributes */
2844 and
2845 ((recinfo.preferred_grade = p_delivery_details_info.PREFERRED_GRADE)
2846 or
2847 ((recinfo.preferred_grade is null)
2848 and (p_delivery_details_info.PREFERRED_GRADE is null)))
2849 and
2850 ((recinfo.src_requested_quantity2 = p_delivery_details_info.SRC_REQUESTED_QUANTITY2)
2851 or
2852 ((recinfo.src_requested_quantity2 is null)
2853 and (p_delivery_details_info.SRC_REQUESTED_QUANTITY2 is null)))
2854 and
2855 ((recinfo.src_requested_quantity_uom2 = p_delivery_details_info.SRC_REQUESTED_QUANTITY_UOM2)
2856 or
2857 ((recinfo.src_requested_quantity_uom2 is null)
2858 and (p_delivery_details_info.SRC_REQUESTED_QUANTITY_UOM2 is null)))
2859 and
2860 ((recinfo.requested_quantity2 = p_delivery_details_info.REQUESTED_QUANTITY2)
2861 or
2862 ((recinfo.requested_quantity2 is null)
2863 and (p_delivery_details_info.REQUESTED_QUANTITY2 is null)))
2864 and
2865 ((recinfo.shipped_quantity2 = p_delivery_details_info.SHIPPED_QUANTITY2)
2866 or
2867 ((recinfo.shipped_quantity2 is null)
2868 and (p_delivery_details_info.SHIPPED_QUANTITY2 is null)))
2869 and
2870 ((recinfo.delivered_quantity2 = p_delivery_details_info.DELIVERED_QUANTITY2)
2871 or
2872 ((recinfo.delivered_quantity2 is null)
2873 and (p_delivery_details_info.DELIVERED_QUANTITY2 is null)))
2874 and
2875 ((recinfo.cancelled_quantity2 = p_delivery_details_info.CANCELLED_QUANTITY2)
2876 or
2877 ((recinfo.cancelled_quantity2 is null)
2878 and (p_delivery_details_info.CANCELLED_QUANTITY2 is null)))
2879 and
2880 ((recinfo.cycle_count_quantity2 = p_delivery_details_info.CYCLE_COUNT_QUANTITY2)
2881 or
2882 ((recinfo.cycle_count_quantity2 is null)
2883 and (p_delivery_details_info.CYCLE_COUNT_QUANTITY2 is null)))
2884 and
2885 ((recinfo.requested_quantity_uom2 = p_delivery_details_info.REQUESTED_QUANTITY_UOM2)
2886 or
2887 ((recinfo.requested_quantity_uom2 is null)
2888 and (p_delivery_details_info.REQUESTED_QUANTITY_UOM2 is null)))
2889 -- HW OPMCONV - Removed check for sublot
2890 and
2891 ((recinfo.to_serial_number = p_delivery_details_info.TO_SERIAL_NUMBER)
2892 or
2893 ((recinfo.to_serial_number is null)
2894 and (p_delivery_details_info.TO_SERIAL_NUMBER is null)))
2895 and
2896 ((recinfo.picked_quantity = p_delivery_details_info.PICKED_QUANTITY)
2897 or
2898 ((recinfo.picked_quantity is null)
2899 and (p_delivery_details_info.PICKED_QUANTITY is null)))
2900 and
2901 ((recinfo.picked_quantity2 = p_delivery_details_info.PICKED_QUANTITY2)
2902 or
2903 ((recinfo.picked_quantity2 is null)
2904 and (p_delivery_details_info.PICKED_QUANTITY2 is null)))
2905 /* H Integration: datamodel changes wrudge */
2906 and
2907 ((recinfo.received_quantity = p_delivery_details_info.RECEIVED_QUANTITY)
2908 or
2909 ( (recinfo.received_quantity is null)
2910 and (p_delivery_details_info.RECEIVED_QUANTITY is null)))
2911
2912 and
2913 ((recinfo.received_quantity2 = p_delivery_details_info.RECEIVED_QUANTITY2)
2914 or
2915 ( (recinfo.received_quantity2 is null)
2916 and (p_delivery_details_info.RECEIVED_QUANTITY2 is null)))
2917 and
2918 ((recinfo.source_line_set_id = p_delivery_details_info.SOURCE_LINE_SET_ID)
2919 or
2920 ( (recinfo.source_line_set_id is null)
2921 and (p_delivery_details_info.SOURCE_LINE_SET_ID is null)))
2922 and
2923 ((recinfo.received_quantity2 = p_delivery_details_info.RECEIVED_QUANTITY2)
2924 or
2925 ( (recinfo.received_quantity2 is null)
2926 and (p_delivery_details_info.RECEIVED_QUANTITY2 is null)))
2927 and
2928 ((recinfo.source_line_set_id = p_delivery_details_info.SOURCE_LINE_SET_ID)
2929 or
2930 ( (recinfo.source_line_set_id is null)
2931 and (p_delivery_details_info.SOURCE_LINE_SET_ID is null)))
2932 /* J Inbound Logistics: New columns jckwok */
2933 and
2934 ((recinfo.vendor_id = p_delivery_details_info.vendor_id)
2935 or
2936 ( (recinfo.vendor_id is null)
2937 and (p_delivery_details_info.vendor_id is null)))
2938 and
2939 ((recinfo.ship_from_site_id = p_delivery_details_info.ship_from_site_id)
2940 or
2941 ( (recinfo.ship_from_site_id is null)
2942 and (p_delivery_details_info.ship_from_site_id is null)))
2943 and
2944 ((nvl(recinfo.line_direction,'O') = nvl(p_delivery_details_info.line_direction,'O'))
2945 or
2946 ( (recinfo.line_direction is null)
2947 and (p_delivery_details_info.line_direction is null)))
2948 and
2949 ((recinfo.party_id = p_delivery_details_info.party_id)
2950 or
2951 ( (recinfo.party_id is null)
2952 and (p_delivery_details_info.party_id is null)))
2953 and
2954 ((recinfo.routing_req_id = p_delivery_details_info.routing_req_id)
2955 or
2956 ( (recinfo.routing_req_id is null)
2957 and (p_delivery_details_info.routing_req_id is null)))
2958 and
2959 ((recinfo.shipping_control = p_delivery_details_info.shipping_control)
2960 or
2961 ( (recinfo.shipping_control is null)
2962 and (p_delivery_details_info.shipping_control is null)))
2963 and
2964 ((recinfo.source_blanket_reference_id = p_delivery_details_info.source_blanket_reference_id)
2965 or
2966 ( (recinfo.source_blanket_reference_id is null)
2967 and (p_delivery_details_info.source_blanket_reference_id is null)))
2968 and
2969 ((recinfo.source_blanket_reference_num = p_delivery_details_info.source_blanket_reference_num)
2970 or
2971 ( (recinfo.source_blanket_reference_num is null)
2972 and (p_delivery_details_info.source_blanket_reference_num is null)))
2973 and
2974 ((recinfo.po_shipment_line_id = p_delivery_details_info.po_shipment_line_id)
2975 or
2976 ( (recinfo.po_shipment_line_id is null)
2977 and (p_delivery_details_info.po_shipment_line_id is null)))
2978 and
2979 ((recinfo.po_shipment_line_number = p_delivery_details_info.po_shipment_line_number)
2980 or
2981 ( (recinfo.po_shipment_line_number is null)
2982 and (p_delivery_details_info.po_shipment_line_number is null)))
2983 and
2984 ((recinfo.returned_quantity = p_delivery_details_info.returned_quantity)
2985 or
2986 ( (recinfo.returned_quantity is null)
2987 and (p_delivery_details_info.returned_quantity is null)))
2988 and
2989 ((recinfo.returned_quantity2 = p_delivery_details_info.returned_quantity2)
2990 or
2991 ( (recinfo.returned_quantity2 is null)
2992 and (p_delivery_details_info.returned_quantity2 is null)))
2993 and
2994 ((recinfo.rcv_shipment_line_id = p_delivery_details_info.rcv_shipment_line_id)
2995 or
2996 ( (recinfo.rcv_shipment_line_id is null)
2997 and (p_delivery_details_info.rcv_shipment_line_id is null)))
2998 and
2999 ((recinfo.source_line_type_code = p_delivery_details_info.source_line_type_code)
3000 or
3001 ( (recinfo.source_line_type_code is null)
3002 and (p_delivery_details_info.source_line_type_code is null)))
3003 and
3004 ((recinfo.supplier_item_number = p_delivery_details_info.supplier_item_number)
3005 or
3006 ( (recinfo.supplier_item_number is null)
3007 and (p_delivery_details_info.supplier_item_number is null)))
3008 /* J TP release : ttrichy*/
3009 and
3010 ((nvl(recinfo.IGNORE_FOR_PLANNING, 'N') = nvl(p_delivery_details_info.IGNORE_FOR_PLANNING, 'N')))
3011 and
3012 ((recinfo.EARLIEST_PICKUP_DATE = p_delivery_details_info.EARLIEST_PICKUP_DATE)
3013 or
3014 ( (recinfo.EARLIEST_PICKUP_DATE is null)
3015 and (p_delivery_details_info.EARLIEST_PICKUP_DATE is null)))
3016 and
3017 ((recinfo.LATEST_PICKUP_DATE = p_delivery_details_info.LATEST_PICKUP_DATE)
3018 or
3019 ( (recinfo.LATEST_PICKUP_DATE is null)
3020 and (p_delivery_details_info.LATEST_PICKUP_DATE is null)))
3021 and
3022 ((recinfo.EARLIEST_DROPOFF_DATE = p_delivery_details_info.EARLIEST_DROPOFF_DATE)
3023 or
3024 ( (recinfo.EARLIEST_DROPOFF_DATE is null)
3025 and (p_delivery_details_info.EARLIEST_DROPOFF_DATE is null)))
3026 and
3027 ((recinfo.LATEST_DROPOFF_DATE = p_delivery_details_info.LATEST_DROPOFF_DATE)
3028 or
3029 ( (recinfo.LATEST_DROPOFF_DATE is null)
3030 and (p_delivery_details_info.LATEST_DROPOFF_DATE is null)))
3031 and
3032 ((recinfo.REQUEST_DATE_TYPE_CODE = p_delivery_details_info.REQUEST_DATE_TYPE_CODE)
3033 or
3034 ( (recinfo.REQUEST_DATE_TYPE_CODE is null)
3035 and (p_delivery_details_info.REQUEST_DATE_TYPE_CODE is null)))
3036 and
3037 ((recinfo.tp_delivery_detail_id = p_delivery_details_info.tp_delivery_detail_id)
3038 or
3039 ( (recinfo.tp_delivery_detail_id is null)
3040 and (p_delivery_details_info.tp_delivery_detail_id is null)))
3041 and
3042 ((recinfo.source_document_type_id = p_delivery_details_info.source_document_type_id)
3043 or
3044 ( (recinfo.source_document_type_id is null)
3045 and (p_delivery_details_info.source_document_type_id is null)))
3046 -- J: W/V Changes
3047 and
3048 ((recinfo.filled_volume = p_delivery_details_info.FILLED_VOLUME)
3049 or
3050 ((recinfo.filled_volume is null)
3051 and (p_delivery_details_info.FILLED_VOLUME is null)))
3052 /* --- commented as wv frozen flag is not updateable field from UI.
3053 and
3054 ((recinfo.wv_frozen_flag = p_delivery_details_info.WV_FROZEN_FLAG)
3055 or
3056 ((recinfo.wv_frozen_flag is null)
3057 and (p_delivery_details_info.WV_FROZEN_FLAG is null)))
3058 */
3059 -- J IB : asutar
3060 and
3061 ((recinfo.po_revision_number = p_delivery_details_info.po_revision_number)
3062 or
3063 ((recinfo.po_revision_number is null)
3064 and (p_delivery_details_info.po_revision_number is null)))
3065 -- bug#6689448 ( Replenishment Project)
3066 and
3067 ((recinfo.replenishment_status = p_delivery_details_info.replenishment_status)
3068 or
3069 ((recinfo.replenishment_status is null)
3070 and (p_delivery_details_info.replenishment_status is null)))
3071 and
3072 ((recinfo.release_revision_number = p_delivery_details_info.release_revision_number)
3073 or
3074 ((recinfo.release_revision_number is null)
3075 and (p_delivery_details_info.release_revision_number is null)))
3076 ) THEN
3077 --
3078 IF l_debug_on THEN
3079 WSH_DEBUG_SV.log(l_module_name,'Nothing has changed');
3080 WSH_DEBUG_SV.pop(l_module_name);
3081 END IF;
3082 --
3083 RETURN;
3084 ELSE
3085 FND_MESSAGE.Set_Name('FND', 'FORM_RECORD_CHANGED');
3086 app_exception.raise_exception;
3087 END IF;
3088
3089 --
3090 IF l_debug_on THEN
3091 WSH_DEBUG_SV.pop(l_module_name);
3092 END IF;
3093 --
3094 EXCEPTION
3095 WHEN app_exception.application_exception or app_exception.record_lock_exception THEN
3096
3097 -- Is this necessary? Does PL/SQL automatically close a
3098 -- cursor when it goes out of scope?
3099
3100 if (c_lock_delivery_detail%ISOPEN) then
3101 close c_lock_delivery_detail;
3102 end if;
3103
3104 --
3105 IF l_debug_on THEN
3106 WSH_DEBUG_SV.logmsg(l_module_name,'APP_EXCEPTION.APPLICATION_EXCEPTION exception has occured.',WSH_DEBUG_SV.C_EXCEP_LEVEL);
3107 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:APP_EXCEPTION.APPLICATION_EXCEPTION');
3108 END IF;
3109 --
3110 RAISE;
3111
3112 WHEN others THEN
3113
3114 -- Is this necessary? Does PL/SQL automatically close a
3115 -- cursor when it goes out of scope?
3116
3117 if (c_lock_delivery_detail%ISOPEN) then
3118 close c_lock_delivery_detail;
3119 end if;
3120
3121 FND_MESSAGE.SET_NAME('WSH','WSH_UNEXP_ERROR');
3122 FND_MESSAGE.Set_Token('PACKAGE', 'WSH_DELIVERY_DETAILS_PKG.LOCK_DELIVERY_DETAILS');
3123 FND_MESSAGE.Set_Token('ORA_ERROR',sqlcode);
3124 FND_MESSAGE.Set_Token('ORA_TEXT',sqlerrm);
3125
3126 --
3127 IF l_debug_on THEN
3128 WSH_DEBUG_SV.logmsg(l_module_name,'Unexpected error has occured. Oracle error message is '|| SQLERRM,WSH_DEBUG_SV.C_UNEXPEC_ERR_LEVEL);
3129 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:OTHERS');
3130 END IF;
3131 --
3132 RAISE;
3133
3134 --
3135 IF l_debug_on THEN
3136 WSH_DEBUG_SV.pop(l_module_name);
3137 END IF;
3138 --
3139 END Lock_Delivery_Details;
3140
3141
3142 --
3143 -- Procedure: Update_Delivery_Line
3144 -- Parameters: All Attributes of a Delivery Line Record
3145 -- Description: This procedure will update attributes of
3146 -- a delivery line.
3147 --
3148
3149 PROCEDURE Update_Delivery_Details(
3150 p_rowid IN VARCHAR2 := NULL,
3151 p_delivery_details_info IN WSH_GLBL_VAR_STRCT_GRP.Delivery_Details_Rec_Type,
3152 x_return_status OUT NOCOPY VARCHAR2
3153 ) IS
3154
3155 -- J: W/V Changes
3156 CURSOR get_dd_info IS
3157 SELECT rowid,
3158 gross_weight,
3159 net_weight,
3160 volume,
3161 filled_volume,
3162 fill_percent,
3163 unit_weight,
3164 unit_volume,
3165 container_flag,
3166 NVL(wv_frozen_flag,'Y'),
3167 weight_uom_code,
3168 volume_uom_code,
3169 inventory_item_id,
3170 --lpn conv
3171 locator_id,
3172 subinventory,
3173 container_name,
3174 requested_quantity, -- OTM R12
3175 shipped_quantity, -- OTM R12
3176 -- bug# 6719369 (replenishment project):
3177 released_status,
3178 replenishment_status
3179 FROM wsh_delivery_details
3180 WHERE delivery_detail_id = p_delivery_details_info.delivery_detail_id;
3181
3182 -- OTM R12 : packing ECO
3183 CURSOR c_get_delivery_info(p_delivery_detail_id IN NUMBER) IS
3184 SELECT wda.delivery_id
3185 FROM wsh_delivery_assignments wda,
3186 wsh_new_deliveries wnd
3187 WHERE wda.delivery_detail_id = p_delivery_detail_id
3188 AND wda.delivery_id = wnd.delivery_id
3189 AND wnd.tms_interface_flag IN
3190 (WSH_NEW_DELIVERIES_PVT.C_TMS_CREATE_IN_PROCESS,
3191 WSH_NEW_DELIVERIES_PVT.C_TMS_UPDATE_IN_PROCESS,
3192 WSH_NEW_DELIVERIES_PVT.C_TMS_AWAITING_ANSWER,
3193 WSH_NEW_DELIVERIES_PVT.C_TMS_ANSWER_RECEIVED)
3194 AND NVL(wnd.ignore_for_planning, 'N') = 'N';
3195
3196 -- bug # 6749200 (replenishment project) : back order consolidation for dynamic replenishment case.
3197 CURSOR get_bo_dd_info IS
3198 SELECT wdd.source_line_id,
3199 wdd.requested_quantity,
3200 wdd.requested_quantity2,
3201 wda.delivery_id
3202 FROM wsh_delivery_details wdd,
3203 wsh_delivery_assignments wda
3204 WHERE wdd.delivery_detail_id = p_delivery_details_info.delivery_detail_id
3205 AND wdd.delivery_detail_id = wda.delivery_detail_id;
3206
3207 l_cons_source_line_rec_tab WSH_DELIVERY_DETAILS_ACTIONS.Cons_Source_Line_Rec_Tab;
3208 l_cons_dd_ids WSH_UTIL_CORE.Id_Tab_Type ; --Stores the dd_ids returned by Consolidate_Source_Line
3209 l_back_order_consolidation VARCHAR2(1) := 'N';
3210 l_req NUMBER;
3211 l_req2 NUMBER;
3212 l_line_id NUMBER;
3213 l_del_id NUMBER;
3214 l_global_param_rec WSH_SHIPPING_PARAMS_PVT.Global_Parameters_Rec_Typ;
3215 -- 6749200 (replenishment project) : end
3216
3217 l_delivery_id WSH_NEW_DELIVERIES.DELIVERY_ID%TYPE;
3218 l_requested_quantity WSH_DELIVERY_DETAILS.REQUESTED_QUANTITY%TYPE;
3219 l_shipped_quantity WSH_DELIVERY_DETAILS.SHIPPED_QUANTITY%TYPE;
3220 l_delivery_id_tab WSH_UTIL_CORE.ID_TAB_TYPE;
3221 l_interface_flag_tab WSH_UTIL_CORE.COLUMN_TAB_TYPE;
3222 l_gc3_is_installed VARCHAR2(1);
3223 -- End of OTM R12
3224
3225 l_rowid VARCHAR2(30);
3226 others exception;
3227
3228 l_gross_wt NUMBER;
3229 l_net_wt NUMBER;
3230 l_volume NUMBER;
3231 l_filled_volume NUMBER;
3232 l_fill_percent NUMBER;
3233 l_unit_weight NUMBER;
3234 l_unit_volume NUMBER;
3235 l_container_flag VARCHAR2(1);
3236 l_frozen_flag VARCHAR2(1);
3237 l_return_status VARCHAR2(1);
3238 l_tmp_gross_wt NUMBER;
3239 l_tmp_vol NUMBER;
3240 l_tmp_vol_uom_code VARCHAR2(30);
3241 l_tmp_wt_uom_code VARCHAR2(30);
3242 l_new_gross_weight NUMBER;
3243 l_new_net_weight NUMBER;
3244 l_new_volume NUMBER;
3245 l_item_id NUMBER;
3246 l_locator_id NUMBER;
3247 l_subinventory VARCHAR2(20);
3248 l_container_name VARCHAR2(50);
3249 l_param_info WSH_SHIPPING_PARAMS_PVT.Parameter_Rec_Typ;
3250 e_wt_vol_fail EXCEPTION;
3251
3252 l_detail_tab WSH_UTIL_CORE.id_tab_type; -- DBI changes
3253 l_dbi_rs VARCHAR2(1); -- Return Status from DBI API
3254 l_num_errors NUMBER := 0;
3255 l_num_warnings NUMBER := 0;
3256 l_wms_installed VARCHAR2(10);
3257 l_org_type VARCHAR2(3) := 'INV';
3258 l_call_out BOOLEAN := FALSE;
3259 l_CALL_BACK_REQUIRED VARCHAR2(2) := 'N';
3260 l_sync_tmp_rec wsh_glbl_var_strct_grp.sync_tmp_rec_type;
3261 l_cont_fill_pc NUMBER;
3262
3263 CURSOR Get_Cont_Item_Info (v_cont_item_id NUMBER, v_org_id NUMBER) IS
3264 SELECT Description,
3265 Container_Type_Code,
3266 minimum_fill_percent,
3267 maximum_load_weight,
3268 internal_volume,
3269 unit_weight,
3270 unit_volume,
3271 weight_uom_code,
3272 volume_uom_code
3273 FROM MTL_SYSTEM_ITEMS
3274 WHERE inventory_item_id = v_cont_item_id
3275 AND container_item_flag = 'Y'
3276 AND organization_id = v_org_id
3277 AND nvl(vehicle_item_flag,'N') = 'N';
3278 --AND shippable_item_flag = 'Y';
3279
3280 l_item_description MTL_SYSTEM_ITEMS.Description%TYPE;
3281 l_container_type_code MTL_SYSTEM_ITEMS.Container_Type_Code%TYPE;
3282 l_minimum_fill_percent MTL_SYSTEM_ITEMS.minimum_fill_percent%TYPE;
3283 l_maximum_load_weight MTL_SYSTEM_ITEMS.maximum_load_weight%TYPE;
3284 l_maximum_volume MTL_SYSTEM_ITEMS.internal_volume%TYPE;
3285 l_unit_weight_item MTL_SYSTEM_ITEMS.unit_weight%TYPE;
3286 l_unit_volume_item MTL_SYSTEM_ITEMS.unit_volume%TYPE;
3287 l_weight_uom_code_item MTL_SYSTEM_ITEMS.weight_uom_code%TYPE;
3288 l_volume_uom_code_item MTL_SYSTEM_ITEMS.volume_uom_code%TYPE;
3289
3290 -- bug# 6719369 (replenishment project): begin
3291 l_event VARCHAR2(600):= null;
3292 l_dd_released_status VARCHAR2(1);
3293 l_wf_rs VARCHAR2(1);
3294 l_replenishment_status VARCHAR2(1);
3295 -- bug# 6719369 (replenishment project): end
3296
3297 --
3298 l_debug_on BOOLEAN;
3299 --
3300 l_module_name CONSTANT VARCHAR2(100) := 'wsh.plsql.' || G_PKG_NAME || '.' || 'UPDATE_DELIVERY_DETAILS';
3301 --
3302 BEGIN
3303 --
3304 --
3305 SAVEPOINT s_before_update_dd;
3306 l_debug_on := WSH_DEBUG_INTERFACE.g_debug;
3307 --
3308 IF l_debug_on IS NULL
3309 THEN
3310 l_debug_on := WSH_DEBUG_SV.is_debug_enabled;
3311 END IF;
3312 --
3313
3314 IF l_debug_on THEN
3315 WSH_DEBUG_SV.push(l_module_name);
3316 --
3317 WSH_DEBUG_SV.log(l_module_name,'P_ROWID',P_ROWID);
3318 END IF;
3319 --
3320 x_return_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
3321
3322 l_gc3_is_installed := WSH_UTIL_CORE.G_GC3_IS_INSTALLED;
3323 IF l_gc3_is_installed IS NULL THEN
3324 l_gc3_is_installed := WSH_UTIL_CORE.GC3_IS_INSTALLED;
3325 END IF;
3326 IF l_debug_on THEN
3327 wsh_debug_sv.log(l_module_name,'l_gc3_is_installed ',
3328 l_gc3_is_installed);
3329 END IF;
3330
3331 l_item_description := p_delivery_details_info.item_description;
3332 l_container_type_code := p_delivery_details_info.container_type_code;
3333 l_minimum_fill_percent := p_delivery_details_info.minimum_fill_percent;
3334 l_maximum_load_weight := p_delivery_details_info.maximum_load_weight;
3335 l_maximum_volume := p_delivery_details_info.maximum_volume;
3336 l_unit_weight_item := p_delivery_details_info.unit_weight;
3337 l_unit_volume_item := p_delivery_details_info.unit_volume;
3338
3339 -- J: W/V Changes
3340 OPEN get_dd_info;
3341 FETCH get_dd_info INTO l_rowid, l_gross_wt, l_net_wt, l_volume,
3342 l_filled_volume, l_fill_percent, l_unit_weight, l_unit_volume
3343 ,l_container_flag, l_frozen_flag, l_tmp_wt_uom_code
3344 , l_tmp_vol_uom_code, l_item_id , l_locator_id
3345 , l_subinventory, l_container_name
3346 , l_requested_quantity, l_shipped_quantity,l_dd_released_status,l_replenishment_status; -- OTM R12
3347 IF get_dd_info%NOTFOUND THEN
3348 CLOSE get_dd_info;
3349 RAISE no_data_found;
3350 END IF;
3351 CLOSE get_dd_info;
3352 IF p_rowid IS NOT NULL THEN
3353 l_rowid := p_rowid;
3354 END IF;
3355
3356 IF l_debug_on THEN
3357 WSH_DEBUG_SV.logmsg(l_module_name,'l_rowid'||l_rowid||' Cont Flag '||l_container_flag||' Org Gross '||l_gross_wt||' Org Net '||l_net_wt||' Org Vol '||l_volume||' frozen '||l_frozen_flag||' U Wt '||l_unit_weight||' U Vol '||l_unit_volume);
3358 WSH_DEBUG_SV.log(l_module_name,'locator_id',l_locator_id);
3359 WSH_DEBUG_SV.log(l_module_name,'l_subinventory',l_subinventory);
3360 WSH_DEBUG_SV.log(l_module_name,'l_container_name',l_container_name);
3361 WSH_DEBUG_SV.log(l_module_name,'l_item_id',l_item_id);
3362 WSH_DEBUG_SV.log(l_module_name,'passed item_id',
3363 p_delivery_details_info.inventory_item_id);
3364 WSH_DEBUG_SV.log(l_module_name,'l_requested_quantity',l_requested_quantity); -- OTM R12
3365 WSH_DEBUG_SV.log(l_module_name,'l_shipped_quantity',l_shipped_quantity); -- OTM R12
3366 WSH_DEBUG_SV.logmsg(l_module_name,'released_status: '||l_dd_released_status||' replenishment_status: '||l_replenishment_status);
3367 END IF;
3368
3369 -- bug# 6719369 (replenishment project) : raise the business events for the replenishment requested and replenishment completed.
3370 IF ( p_delivery_details_info.released_status = l_dd_released_status
3371 and p_delivery_details_info.replenishment_status IS NOT NULL and l_dd_released_status in ('R','B') ) THEN
3372 --{
3373 IF ( NVL(l_replenishment_status,'R') = 'R' and p_delivery_details_info.replenishment_status = 'C' ) THEN
3374 --{
3375 l_event := 'oracle.apps.wsh.line.gen.replenishmentcompleted';
3376 ELSIF ( l_replenishment_status IS NULL and p_delivery_details_info.replenishment_status = 'R' ) THEN
3377 l_event := 'oracle.apps.wsh.line.gen.replenishmentrequested';
3378 --}
3379 END IF;
3380 --}
3381 END IF;
3382 --
3383 -- bug# 6719369 (replenishment project) : end
3384
3385 -- bug # 6749200 (replenishment project) : back order consolidation for dynamic replenishment case.
3386 IF ( p_delivery_details_info.released_status = l_dd_released_status
3387 and p_delivery_details_info.replenishment_status IS NULL and l_dd_released_status = 'B'
3388 and l_replenishment_status IS NOT NULL) THEN
3389 --{
3390 l_back_order_consolidation := 'Y';
3391 --}
3392 END IF;
3393 --
3394 -- bug# 6719369 (replenishment project) : end
3395
3396 --lpn conv
3397 IF l_container_flag IN ('Y', 'C') THEN --{
3398 l_wms_installed :=
3399 wsh_util_validate.Check_Wms_Org(p_delivery_details_info.organization_id);
3400
3401 IF l_wms_installed = 'Y' THEN --{
3402 l_org_type := 'WMS';
3403 l_frozen_flag := 'N';
3404 --
3405 IF NVL(l_item_id,-1) <> p_delivery_details_info.inventory_item_id
3406 THEN --{
3407 -- If the item id is updated then calculate some wt/vol
3408 -- related fields
3409 OPEN Get_Cont_Item_Info(p_delivery_details_info.inventory_item_id , p_delivery_details_info.organization_id);
3410 FETCH Get_Cont_Item_Info INTO
3411 l_item_description,
3412 l_container_type_code,
3413 l_minimum_fill_percent,
3414 l_maximum_load_weight,
3415 l_maximum_volume,
3416 l_unit_weight_item,
3417 l_unit_volume_item,
3418 l_weight_uom_code_item ,
3419 l_volume_uom_code_item ;
3420
3421 IF Get_Cont_Item_Info%FOUND THEN --{
3422 --
3423 IF l_debug_on THEN
3424 WSH_DEBUG_SV.log(l_module_name,'l_item_description',
3425 l_item_description);
3426 WSH_DEBUG_SV.log(l_module_name,'l_container_type_code',
3427 l_container_type_code);
3428 WSH_DEBUG_SV.log(l_module_name,'l_minimum_fill_percent',
3429 l_minimum_fill_percent);
3430 WSH_DEBUG_SV.log(l_module_name,'l_maximum_load_weight',
3431 l_maximum_load_weight);
3432 WSH_DEBUG_SV.log(l_module_name,'l_maximum_volume',
3433 l_maximum_volume);
3434 WSH_DEBUG_SV.log(l_module_name,'l_unit_weight_item',
3435 l_unit_weight_item);
3436 WSH_DEBUG_SV.log(l_module_name,'l_unit_volume_item',
3437 l_unit_volume_item);
3438 WSH_DEBUG_SV.log(l_module_name,'l_weight_uom_code_item',
3439 l_weight_uom_code_item);
3440 WSH_DEBUG_SV.log(l_module_name,'l_volume_uom_code_item',
3441 l_volume_uom_code_item);
3442 END IF;
3443
3444 IF l_weight_uom_code_item <> l_tmp_wt_uom_code THEN --{
3445 l_maximum_load_weight :=
3446 WSH_WV_UTILS.Convert_Uom_core (
3447 from_uom => l_weight_uom_code_item,
3448 to_uom => l_tmp_wt_uom_code,
3449 quantity => l_maximum_load_weight,
3450 item_id => p_delivery_details_info.inventory_item_id,
3451 x_return_status => l_return_status
3452 );
3453 wsh_util_core.api_post_call
3454 (
3455 p_return_status => l_return_status,
3456 x_num_warnings => l_num_warnings,
3457 x_num_errors => l_num_errors
3458 );
3459
3460
3461 IF l_debug_on THEN
3462 WSH_DEBUG_SV.log(l_module_name,'maximum_load_weight',
3463 l_maximum_load_weight);
3464 END IF;
3465
3466 l_unit_weight_item :=
3467 WSH_WV_UTILS.Convert_Uom_core (
3468 from_uom => l_weight_uom_code_item,
3469 to_uom => l_tmp_wt_uom_code,
3470 quantity => l_unit_weight_item,
3471 item_id => p_delivery_details_info.inventory_item_id,
3472 x_return_status => l_return_status
3473 );
3474 wsh_util_core.api_post_call
3475 (
3476 p_return_status => l_return_status,
3477 x_num_warnings => l_num_warnings,
3478 x_num_errors => l_num_errors
3479 );
3480
3481 IF l_debug_on THEN
3482 WSH_DEBUG_SV.log(l_module_name,'unit_weight',
3483 l_unit_weight_item);
3484 END IF;
3485 END IF; --}
3486 IF l_volume_uom_code_item <> l_tmp_vol_uom_code THEN --{
3487
3488 l_unit_volume_item :=
3489 WSH_WV_UTILS.Convert_Uom_core (
3490 from_uom => l_volume_uom_code_item,
3491 to_uom => l_tmp_vol_uom_code,
3492 quantity => l_unit_volume_item,
3493 item_id => p_delivery_details_info.inventory_item_id,
3494 x_return_status => l_return_status
3495 );
3496 wsh_util_core.api_post_call
3497 (
3498 p_return_status => l_return_status,
3499 x_num_warnings => l_num_warnings,
3500 x_num_errors => l_num_errors
3501 );
3502
3503 IF l_debug_on THEN
3504 WSH_DEBUG_SV.log(l_module_name,'unit_volume',
3505 l_unit_volume_item);
3506 END IF;
3507
3508 l_maximum_volume :=
3509 WSH_WV_UTILS.Convert_Uom_core (
3510 from_uom => l_volume_uom_code_item,
3511 to_uom => l_tmp_vol_uom_code,
3512 quantity => l_maximum_volume,
3513 item_id => p_delivery_details_info.inventory_item_id,
3514 x_return_status => l_return_status
3515 );
3516 wsh_util_core.api_post_call
3517 (
3518 p_return_status => l_return_status,
3519 x_num_warnings => l_num_warnings,
3520 x_num_errors => l_num_errors
3521 );
3522
3523 IF l_debug_on THEN
3524 WSH_DEBUG_SV.log(l_module_name,'maximum_volume',
3525 l_maximum_volume);
3526 END IF;
3527
3528 END IF; --}
3529 --
3530 END IF; --}
3531
3532 CLOSE Get_Cont_Item_Info;
3533
3534 END IF; --}
3535 --
3536 ELSE --}{
3537 l_org_type := 'INV';
3538 END IF;
3539 END IF; --}
3540
3541 -- If W/V changes on container then set wv_frozen_flag to Y
3542 IF l_container_flag IN ('Y', 'C') AND l_frozen_flag = 'N' THEN
3543
3544 IF l_tmp_wt_uom_code <> p_delivery_details_info.weight_uom_code THEN
3545
3546 l_gross_wt := WSH_WV_UTILS.Convert_Uom(
3547 from_uom => l_tmp_wt_uom_code,
3548 to_uom => p_delivery_details_info.weight_uom_code,
3549 quantity => l_gross_wt,
3550 item_id => p_delivery_details_info.inventory_item_id);
3551
3552 l_net_wt := WSH_WV_UTILS.Convert_Uom(
3553 from_uom => l_tmp_wt_uom_code,
3554 to_uom => p_delivery_details_info.weight_uom_code,
3555 quantity => l_net_wt,
3556 item_id => p_delivery_details_info.inventory_item_id);
3557
3558 END IF;
3559
3560 IF l_tmp_vol_uom_code <> p_delivery_details_info.volume_uom_code THEN
3561
3562 l_volume := WSH_WV_UTILS.Convert_Uom(
3563 from_uom => l_tmp_vol_uom_code,
3564 to_uom => p_delivery_details_info.volume_uom_code,
3565 quantity => l_volume,
3566 item_id => p_delivery_details_info.inventory_item_id);
3567
3568 END IF;
3569
3570
3571 IF ((NVL(l_gross_wt,-99) <> NVL(p_delivery_details_info.gross_weight,-99)) OR
3572 (NVL(l_net_wt,-99) <> NVL(p_delivery_details_info.net_weight,-99)) OR
3573 (NVL(l_filled_volume,-99) <> NVL(p_delivery_details_info.filled_volume,-99)) OR
3574 (NVL(l_fill_percent,-99) <> NVL(p_delivery_details_info.fill_percent,-99)) OR
3575 (NVL(l_volume,-99) <> NVL(p_delivery_details_info.volume,-99))) THEN
3576
3577 --lpn conv
3578 IF l_org_type = 'INV' THEN
3579 l_frozen_flag := 'Y';
3580 ELSE
3581 l_frozen_flag := 'N';
3582 END IF;
3583
3584 END IF;
3585 -- lpn conv
3586 IF WSH_WMS_LPN_GRP.G_CALLBACK_REQUIRED = 'Y' THEN --{
3587
3588 l_CALL_BACK_REQUIRED := WSH_WMS_LPN_GRP.G_CALLBACK_REQUIRED;
3589
3590 IF (l_org_type = 'WMS' AND WSH_WMS_LPN_GRP.GK_WMS_UPD_WV)
3591 OR(l_org_type = 'INV' AND WSH_WMS_LPN_GRP.GK_INV_UPD_WV)
3592 THEN --{
3593 IF ((NVL(l_gross_wt,-99)
3594 <> NVL(p_delivery_details_info.gross_weight,-99))
3595 OR
3596 (NVL(l_net_wt,-99)
3597 <> NVL(p_delivery_details_info.net_weight,-99))
3598 OR
3599 (NVL(l_volume,-99) <>
3600 NVL(p_delivery_details_info.volume,-99))
3601 OR
3602 (NVL(l_tmp_wt_uom_code,-99)
3603 <> NVL(p_delivery_details_info.weight_uom_code,-99))
3604 OR
3605 (NVL(l_tmp_vol_uom_code,-99)
3606 <> NVL(p_delivery_details_info.volume_uom_code,-99))
3607 OR
3608 (NVL(l_filled_volume,-99)
3609 <> NVL(p_delivery_details_info.filled_volume,-99)))
3610 THEN --{
3611
3612 l_call_out := TRUE;
3613 WSH_WMS_LPN_GRP.g_update_to_containers := 'Y';
3614
3615 END IF; --}
3616 ELSIF (l_org_type = 'WMS' AND WSH_WMS_LPN_GRP.GK_WMS_UPD_KEY)
3617 OR(l_org_type = 'INV' AND WSH_WMS_LPN_GRP.GK_INV_UPD_KEY)
3618 THEN --}{
3619 --
3620 IF (NVL(l_container_name,-99)
3621 <> NVL(p_delivery_details_info.container_name,-99))
3622 THEN
3623 l_call_out := TRUE;
3624 END IF;
3625 --
3626 END IF; --}
3627 END IF; --}
3628 --
3629 END IF;
3630
3631 -- For non-containers, if the new W/V is same as qty * unit W/V then reset the wv_frozen_flag to N. Else set it to Y
3632 IF l_container_flag = 'N' AND (l_unit_weight is NOT NULL OR l_unit_volume is NOT NULL) THEN
3633 l_tmp_gross_wt := nvl(nvl(p_delivery_details_info.received_quantity, nvl(p_delivery_details_info.shipped_quantity,
3634 NVL(p_delivery_details_info.picked_quantity, p_delivery_details_info.requested_quantity)))
3635 * l_unit_weight,-99);
3636 l_tmp_vol := nvl(nvl(p_delivery_details_info.received_quantity, nvl(p_delivery_details_info.shipped_quantity, NVL(p_delivery_details_info.picked_quantity, p_delivery_details_info.requested_quantity))) * l_unit_volume,-99);
3637
3638 IF l_debug_on THEN
3639 WSH_DEBUG_SV.log(l_module_name,'l_tmp_gross_wt '||l_tmp_gross_wt||' l_tmp_vol '||l_tmp_vol||' New gross '||
3640 nvl(p_delivery_details_info.gross_weight,-99)||' New Net '||nvl(p_delivery_details_info.net_weight,-99)||
3641 ' New Vol '||nvl(p_delivery_details_info.volume,-99));
3642 END IF;
3643
3644 IF l_tmp_wt_uom_code <> p_delivery_details_info.weight_uom_code THEN
3645
3646 l_tmp_gross_wt := WSH_WV_UTILS.Convert_Uom(
3647 from_uom => l_tmp_wt_uom_code,
3648 to_uom => p_delivery_details_info.weight_uom_code,
3649 quantity => l_tmp_gross_wt,
3650 item_id => p_delivery_details_info.inventory_item_id);
3651 END IF;
3652
3653 IF l_tmp_vol_uom_code <> p_delivery_details_info.volume_uom_code THEN
3654
3655 l_tmp_vol := WSH_WV_UTILS.Convert_Uom(
3656 from_uom => l_tmp_vol_uom_code,
3657 to_uom => p_delivery_details_info.volume_uom_code,
3658 quantity => l_tmp_vol,
3659 item_id => p_delivery_details_info.inventory_item_id);
3660
3661 END IF;
3662
3663 IF l_tmp_gross_wt <> nvl(p_delivery_details_info.gross_weight,-99) OR
3664 l_tmp_gross_wt <> nvl(p_delivery_details_info.net_weight,-99) OR
3665 l_tmp_vol <> nvl(p_delivery_details_info.volume,-99) THEN
3666
3667 l_frozen_flag := 'Y';
3668 ELSE
3669 l_frozen_flag := 'N';
3670 END IF;
3671 -- Added ELSIF condition for bug 4509105
3672 ELSIF (l_container_flag = 'N' and ( nvl(l_gross_wt, -99) <> nvl(p_delivery_details_info.gross_weight,-99) OR
3673 nvl(l_net_wt, -99) <> nvl(p_delivery_details_info.net_weight,-99) OR
3674 nvl(l_volume, -99) <> nvl(p_delivery_details_info.volume,-99) ) )
3675 THEN
3676 l_frozen_flag := 'Y';
3677 END IF;
3678
3679 IF l_debug_on THEN
3680 WSH_DEBUG_SV.log(l_module_name,'l_frozen_flag '||l_frozen_flag);
3681 END IF;
3682
3683 -- lpn conv bmso dependency
3684 IF l_call_out THEN
3685 l_sync_tmp_rec.delivery_detail_id :=
3686 p_delivery_details_info.delivery_detail_id;
3687 l_sync_tmp_rec.operation_type := 'UPDATE';
3688 WSH_WMS_SYNC_TMP_PKG.MERGE(
3689 p_sync_tmp_rec => l_sync_tmp_rec,
3690 x_return_status => l_return_status
3691 );
3692
3693 wsh_util_core.api_post_call
3694 (
3695 p_return_status => l_return_status,
3696 x_num_warnings => l_num_warnings,
3697 x_num_errors => l_num_errors
3698 );
3699
3700 END IF;
3701
3702 UPDATE wsh_delivery_details
3703 SET
3704 delivery_detail_id = p_delivery_details_info.delivery_detail_id,
3705 unit_weight = l_unit_weight_item,
3706 unit_volume = l_unit_volume_item,
3707 source_code = p_delivery_details_info.source_code,
3708 source_header_id = p_delivery_details_info.source_header_id,
3709 source_line_id = p_delivery_details_info.source_line_id,
3710 customer_id = p_delivery_details_info.customer_id,
3711 sold_to_contact_id = p_delivery_details_info.sold_to_contact_id,
3712 inventory_item_id = p_delivery_details_info.inventory_item_id,
3713 item_description = l_item_description,
3714 country_of_origin = p_delivery_details_info.country_of_origin,
3715 classification = p_delivery_details_info.classification,
3716 ship_from_location_id = p_delivery_details_info.ship_from_location_id,
3717 ship_to_location_id = p_delivery_details_info.ship_to_location_id,
3718 ship_to_contact_id = p_delivery_details_info.ship_to_contact_id,
3719 deliver_to_location_id = p_delivery_details_info.deliver_to_location_id,
3720 deliver_to_contact_id = p_delivery_details_info.deliver_to_contact_id,
3721 intmed_ship_to_location_id = p_delivery_details_info.intmed_ship_to_location_id,
3722 intmed_ship_to_contact_id = p_delivery_details_info.intmed_ship_to_contact_id,
3723 ship_tolerance_above = p_delivery_details_info.ship_tolerance_above,
3724 ship_tolerance_below = p_delivery_details_info.ship_tolerance_below,
3725 requested_quantity = p_delivery_details_info.requested_quantity,
3726 shipped_quantity = p_delivery_details_info.shipped_quantity,
3727 delivered_quantity = p_delivery_details_info.delivered_quantity,
3728 requested_quantity_uom = p_delivery_details_info.requested_quantity_uom,
3729 subinventory = p_delivery_details_info.subinventory,
3730 revision = p_delivery_details_info.revision,
3731 lot_number = p_delivery_details_info.lot_number,
3732 customer_requested_lot_flag = p_delivery_details_info.customer_requested_lot_flag,
3733 serial_number = p_delivery_details_info.serial_number,
3734 locator_id = p_delivery_details_info.locator_id,
3735 date_requested = p_delivery_details_info.date_requested,
3736 date_scheduled = p_delivery_details_info.date_scheduled,
3737 master_container_item_id = p_delivery_details_info.master_container_item_id,
3738 detail_container_item_id = p_delivery_details_info.detail_container_item_id,
3739 load_seq_number = p_delivery_details_info.load_seq_number,
3740 ship_method_code = p_delivery_details_info.ship_method_code,
3741 carrier_id = p_delivery_details_info.carrier_id,
3742 freight_terms_code = p_delivery_details_info.freight_terms_code,
3743 shipment_priority_code = p_delivery_details_info.shipment_priority_code,
3744 fob_code = p_delivery_details_info.fob_code,
3745 customer_item_id = p_delivery_details_info.customer_item_id,
3746 dep_plan_required_flag = p_delivery_details_info.dep_plan_required_flag,
3747 customer_prod_seq = p_delivery_details_info.customer_prod_seq,
3748 customer_dock_code = p_delivery_details_info.customer_dock_code,
3749 cust_model_serial_number = p_delivery_details_info.cust_model_serial_number,
3750 customer_job = p_delivery_details_info.customer_job,
3751 customer_production_line = p_delivery_details_info.customer_production_line,
3752 net_weight = p_delivery_details_info.net_weight,
3753 weight_uom_code = p_delivery_details_info.weight_uom_code,
3754 volume = p_delivery_details_info.volume,
3755 volume_uom_code = p_delivery_details_info.volume_uom_code,
3756 -- J: W/V Changes
3757 filled_volume = p_delivery_details_info.filled_volume,
3758 tp_attribute_category = p_delivery_details_info.tp_attribute_category,
3759 tp_attribute1 = p_delivery_details_info.tp_attribute1,
3760 tp_attribute2 = p_delivery_details_info.tp_attribute2,
3761 tp_attribute3 = p_delivery_details_info.tp_attribute3,
3762 tp_attribute4 = p_delivery_details_info.tp_attribute4,
3763 tp_attribute5 = p_delivery_details_info.tp_attribute5,
3764 tp_attribute6 = p_delivery_details_info.tp_attribute6,
3765 tp_attribute7 = p_delivery_details_info.tp_attribute7,
3766 tp_attribute8 = p_delivery_details_info.tp_attribute8,
3767 tp_attribute9 = p_delivery_details_info.tp_attribute9,
3768 tp_attribute10 = p_delivery_details_info.tp_attribute10,
3769 tp_attribute11 = p_delivery_details_info.tp_attribute11,
3770 tp_attribute12 = p_delivery_details_info.tp_attribute12,
3771 tp_attribute13 = p_delivery_details_info.tp_attribute13,
3772 tp_attribute14 = p_delivery_details_info.tp_attribute14,
3773 tp_attribute15 = p_delivery_details_info.tp_attribute15,
3774 attribute_category = p_delivery_details_info.attribute_category,
3775 attribute1 = p_delivery_details_info.attribute1,
3776 attribute2 = p_delivery_details_info.attribute2,
3777 attribute3 = p_delivery_details_info.attribute3,
3778 attribute4 = p_delivery_details_info.attribute4,
3779 attribute5 = p_delivery_details_info.attribute5,
3780 attribute6 = p_delivery_details_info.attribute6,
3781 attribute7 = p_delivery_details_info.attribute7,
3782 attribute8 = p_delivery_details_info.attribute8,
3783 attribute9 = p_delivery_details_info.attribute9,
3784 attribute10 = p_delivery_details_info.attribute10,
3785 attribute11 = p_delivery_details_info.attribute11,
3786 attribute12 = p_delivery_details_info.attribute12,
3787 attribute13 = p_delivery_details_info.attribute13,
3788 attribute14 = p_delivery_details_info.attribute14,
3789 attribute15 = p_delivery_details_info.attribute15,
3790 last_update_date = p_delivery_details_info.last_update_date,
3791 last_updated_by = p_delivery_details_info.last_updated_by,
3792 last_update_login = p_delivery_details_info.last_update_login,
3793 program_application_id = p_delivery_details_info.program_application_id,
3794 program_id = p_delivery_details_info.program_id,
3795 program_update_date = p_delivery_details_info.program_update_date,
3796 request_id = p_delivery_details_info.request_id,
3797 mvt_stat_status = p_delivery_details_info.mvt_stat_status,
3798 organization_id = p_delivery_details_info.organization_id,
3799 transaction_temp_id = p_delivery_details_info.transaction_temp_id,
3800 ship_set_id = p_delivery_details_info.ship_set_id,
3801 arrival_set_id = p_delivery_details_info.arrival_set_id,
3802 ship_model_complete_flag = p_delivery_details_info.ship_model_complete_flag,
3803 top_model_line_id = p_delivery_details_info.top_model_line_id,
3804 hold_code = p_delivery_details_info.hold_code,
3805 source_header_number = p_delivery_details_info.source_header_number,
3806 source_header_type_id = p_delivery_details_info.source_header_type_id,
3807 source_header_type_name = p_delivery_details_info.source_header_type_name,
3808 cust_po_number = p_delivery_details_info.cust_po_number,
3809 ato_line_id = p_delivery_details_info.ato_line_id,
3810 src_requested_quantity = p_delivery_details_info.src_requested_quantity,
3811 src_requested_quantity_uom = p_delivery_details_info.src_requested_quantity_uom,
3812 move_order_line_id = p_delivery_details_info.move_order_line_id,
3813 cancelled_quantity = p_delivery_details_info.cancelled_quantity,
3814 hazard_class_id = p_delivery_details_info.hazard_class_id,
3815 quality_control_quantity = p_delivery_details_info.quality_control_quantity,
3816 cycle_count_quantity = p_delivery_details_info.cycle_count_quantity,
3817 tracking_number = p_delivery_details_info.tracking_number,
3818 movement_id = p_delivery_details_info.movement_id,
3819 shipping_instructions = p_delivery_details_info.shipping_instructions,
3820 packing_instructions = p_delivery_details_info.packing_instructions,
3821 project_id = p_delivery_details_info.project_id,
3822 task_id = p_delivery_details_info.task_id,
3823 org_id = p_delivery_details_info.org_id,
3824 oe_interfaced_flag = p_delivery_details_info.oe_interfaced_flag,
3825 split_from_delivery_detail_id = p_delivery_details_info.split_from_detail_id,
3826 inv_interfaced_flag = p_delivery_details_info.inv_interfaced_flag,
3827 source_line_number = p_delivery_details_info.source_line_number,
3828 inspection_flag = p_delivery_details_info.inspection_flag,
3829 released_status = p_delivery_details_info.released_status,
3830 container_flag = p_delivery_details_info.container_flag,
3831 container_type_code = l_container_type_code,
3832 container_name = p_delivery_details_info.container_name,
3833 fill_percent = p_delivery_details_info.fill_percent,
3834 gross_weight = p_delivery_details_info.gross_weight,
3835 master_serial_number = p_delivery_details_info.master_serial_number,
3836 maximum_load_weight = l_maximum_load_weight,
3837 maximum_volume = l_maximum_volume,
3838 minimum_fill_percent = l_minimum_fill_percent,
3839 seal_code = p_delivery_details_info.seal_code,
3840 unit_number = p_delivery_details_info.unit_number,
3841 unit_price = p_delivery_details_info.unit_price,
3842 currency_code = p_delivery_details_info.currency_code,
3843 freight_class_cat_id = p_delivery_details_info.freight_class_cat_id,
3844 commodity_code_cat_id = p_delivery_details_info.commodity_code_cat_id,
3845 preferred_grade = p_delivery_details_info.preferred_grade, /* OPM 09/11/000 start */
3846 src_requested_quantity2 = p_delivery_details_info.src_requested_quantity2,
3847 src_requested_quantity_uom2 = p_delivery_details_info.src_requested_quantity_uom2,
3848 requested_quantity2 = p_delivery_details_info.requested_quantity2,
3849 shipped_quantity2 = p_delivery_details_info.shipped_quantity2,
3850 delivered_quantity2 = p_delivery_details_info.delivered_quantity2,
3851 cancelled_quantity2 = p_delivery_details_info.cancelled_quantity2,
3852 quality_control_quantity2 = p_delivery_details_info.quality_control_quantity2,
3853 cycle_count_quantity2 = p_delivery_details_info.cycle_count_quantity2,
3854 requested_quantity_uom2 = p_delivery_details_info.requested_quantity_uom2,
3855 -- HW OPMCONV - No need for sublot_number
3856 -- sublot_number = p_delivery_details_info.sublot_number,
3857 /* OPM 09/11/00 end */
3858 to_serial_number = p_delivery_details_info.to_serial_number,
3859 picked_quantity = p_delivery_details_info.picked_quantity,
3860 picked_quantity2 = p_delivery_details_info.picked_quantity2,
3861 /* H Integration: datamodel changes wrudge */
3862 received_quantity = p_delivery_details_info.received_quantity,
3863 received_quantity2 = p_delivery_details_info.received_quantity2,
3864 source_line_set_id = p_delivery_details_info.source_line_set_id,
3865 lpn_id = p_delivery_details_info.lpn_id,
3866 /* J Inbound Logistics: New columns jckwok */
3867 vendor_id = p_delivery_details_info.vendor_id ,
3868 ship_from_site_id = p_delivery_details_info.ship_from_site_id ,
3869 line_direction = nvl(p_delivery_details_info.line_direction, 'O') ,
3870 party_id = p_delivery_details_info.party_id ,
3871 routing_req_id = p_delivery_details_info.routing_req_id ,
3872 shipping_control = p_delivery_details_info.shipping_control ,
3873 source_blanket_reference_id = p_delivery_details_info.source_blanket_reference_id ,
3874 source_blanket_reference_num = p_delivery_details_info.source_blanket_reference_num,
3875 po_shipment_line_id = p_delivery_details_info.po_shipment_line_id ,
3876 po_shipment_line_number = p_delivery_details_info.po_shipment_line_number ,
3877 returned_quantity = p_delivery_details_info.returned_quantity ,
3878 returned_quantity2 = p_delivery_details_info.returned_quantity2 ,
3879 rcv_shipment_line_id = p_delivery_details_info.rcv_shipment_line_id ,
3880 source_line_type_code = p_delivery_details_info.source_line_type_code ,
3881 supplier_item_number = p_delivery_details_info.supplier_item_number ,
3882 /* J TP release : ttrichy*/
3883 IGNORE_FOR_PLANNING = nvl(p_delivery_details_info.IGNORE_FOR_PLANNING,'N'),
3884 EARLIEST_PICKUP_DATE = p_delivery_details_info.EARLIEST_PICKUP_DATE ,
3885 LATEST_PICKUP_DATE = p_delivery_details_info.LATEST_PICKUP_DATE ,
3886 EARLIEST_DROPOFF_DATE = p_delivery_details_info.EARLIEST_DROPOFF_DATE ,
3887 LATEST_DROPOFF_DATE = p_delivery_details_info.LATEST_DROPOFF_DATE ,
3888 --DEMAND_SATISFACTION_DATE = p_delivery_details_info.DEMAND_SATISFACTION_DATE , --confirm name for this
3889 REQUEST_DATE_TYPE_CODE = p_delivery_details_info.REQUEST_DATE_TYPE_CODE ,
3890 tp_delivery_detail_id = p_delivery_details_info.tp_delivery_detail_id,
3891 source_document_type_id = p_delivery_details_info.source_document_type_id,
3892 /*J: W/V Changes */
3893 wv_frozen_flag = decode(l_frozen_flag,NULL,wv_frozen_flag,l_frozen_flag),
3894 service_level = p_delivery_details_info.service_level,
3895 mode_of_transport = p_delivery_details_info.mode_of_transport,
3896 /* J IB: asutar*/
3897 po_revision_number = p_delivery_details_info.po_revision_number,
3898 release_revision_number = p_delivery_details_info.release_revision_number,
3899 batch_id = p_delivery_details_info.batch_id, --X-dock
3900 replenishment_status = p_delivery_details_info.replenishment_status --bug# 6689448 (replenishment project)
3901 WHERE rowid = l_rowid;
3902
3903 IF (SQL%NOTFOUND) THEN
3904 RAISE NO_DATA_FOUND;
3905 ELSE
3906 x_return_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
3907 END IF;
3908 --
3909 --
3910 -- DBI Project
3911 -- Update of wsh_delivery_details where requested_quantity/released_status
3912 -- are changed, call DBI API after the update.
3913 -- DBI API checks for DBI Installed also
3914 IF l_debug_on THEN
3915 WSH_DEBUG_SV.log(l_module_name,'Calling DBI API.Detail id-',p_delivery_details_info.delivery_detail_id);
3916 END IF;
3917 l_detail_tab(1) := p_delivery_details_info.delivery_detail_id;
3918 WSH_INTEGRATION.DBI_Update_Detail_Log
3919 (p_delivery_detail_id_tab => l_detail_tab,
3920 p_dml_type => 'UPDATE',
3921 x_return_status => l_dbi_rs);
3922
3923 IF l_debug_on THEN
3924 WSH_DEBUG_SV.log(l_module_name,'Return Status after DBI Call-',l_dbi_rs);
3925 END IF;
3926 -- DBI API can only raise unexpected error, in that case need to
3927 -- pass it to the caller API for roll back of the whole transaction
3928 -- Only need to handle Unexpected error, rest are treated as success
3929 -- Since code is not proceeding, no need to reset x_return_status
3930 IF l_dbi_rs = WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR THEN
3931 x_return_status := l_dbi_rs;
3932 IF l_debug_on THEN
3933 WSH_DEBUG_SV.pop(l_module_name);
3934 END IF;
3935 RETURN;
3936 END IF;
3937 -- End of Code for DBI Project
3938 --
3939
3940 IF l_debug_on THEN
3941 WSH_DEBUG_SV.log(l_module_name,'event is ', l_event);
3942 END IF;
3943
3944 -- bug# 6719369 (replenishment project) : begin : raise the business events.
3945 IF ( l_event IS NOT NULL ) THEN
3946 --{
3947 --Raise Event : Pick To Pod Workflow
3948 WSH_WF_STD.Raise_Event(
3949 p_entity_type => 'LINE',
3950 p_entity_id => p_delivery_details_info.delivery_detail_id ,
3951 p_event => l_event ,
3952 p_organization_id => p_delivery_details_info.organization_id,
3953 x_return_status => l_wf_rs ) ;
3954 IF l_debug_on THEN
3955 WSH_DEBUG_SV.log(l_module_name,'Current Time is ',SYSDATE);
3956 WSH_DEBUG_SV.log(l_module_name,'Delivery Detail Id is ',p_delivery_details_info.delivery_detail_id );
3957 wsh_debug_sv.log(l_module_name,'Return Status After Calling WSH_WF_STD.Raise_Event',l_wf_rs);
3958 END IF;
3959 --Done Raise Event: Pick To Pod Workflow
3960 --}
3961 END IF;
3962 --
3963 -- bug# 6719369 (replenishment project) : end
3964
3965 -- Call DD_WV_Post_Process if W/V Change on detail or container
3966
3967 -- Added condition to check if UOM Codes have been changed
3968 -- for post process
3969 -- Bug 5728048
3970
3971 IF (NVL(l_gross_wt,-99) <> NVL(p_delivery_details_info.gross_weight,-99)) OR
3972 (NVL(l_net_wt,-99) <> NVL(p_delivery_details_info.net_weight,-99)) OR
3973 (NVL(l_volume,-99) <> NVL(p_delivery_details_info.volume,-99)) OR
3974 (NVL(l_tmp_wt_uom_code,'XX') <> NVL(p_delivery_details_info.weight_uom_code,'XX')) OR
3975 (NVL(l_tmp_vol_uom_code,'XX') <> NVL(p_delivery_details_info.volume_uom_code,'XX')) THEN
3976 --{
3977
3978 IF l_debug_on THEN
3979 WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_WV_UTILS.DD_WV_Post_Process',WSH_DEBUG_SV.C_PROC_LEVEL);
3980 END IF;
3981
3982 WSH_WV_UTILS.DD_WV_Post_Process(
3983 p_delivery_detail_id => p_delivery_details_info.delivery_detail_id,
3984 p_diff_gross_wt => NVL(p_delivery_details_info.gross_weight,0) - NVL(l_gross_wt,0),
3985 p_diff_net_wt => NVL(p_delivery_details_info.net_weight,0) - NVL(l_net_wt,0),
3986 p_diff_fill_volume => NVL(p_delivery_details_info.volume,0) - NVL(l_volume,0),
3987 p_diff_volume => NVL(p_delivery_details_info.volume,0) - NVL(l_volume,0),
3988 x_return_status => l_return_status);
3989
3990 IF l_return_status = WSH_UTIL_CORE.G_RET_STS_ERROR THEN
3991 raise e_wt_vol_fail;
3992 END IF;
3993 ELSIF NVL(l_filled_volume,-99) <>
3994 NVL(p_delivery_details_info.filled_volume,-99)
3995 AND p_delivery_details_info.inventory_item_id IS NOT NULL
3996 THEN--}{ lpn conv
3997 WSH_TPA_CONTAINER_PKG.Calc_Cont_Fill_Pc (
3998 p_container_instance_id =>
3999 p_delivery_details_info.delivery_detail_id,
4000 p_update_flag => 'Y',
4001 p_fill_pc_basis => NULL,
4002 x_fill_percent => l_cont_fill_pc,
4003 x_return_status => l_return_status
4004 );
4005 wsh_util_core.api_post_call
4006 (
4007 p_return_status => l_return_status,
4008 x_num_warnings => l_num_warnings,
4009 x_num_errors => l_num_errors
4010 );
4011 END IF; --}
4012
4013
4014 IF l_container_flag IN ('Y', 'C') AND l_org_type = 'WMS' THEN --{ lpn conv
4015 --bmso do we need to worry about if WMS is calling or not
4016 IF NVL(l_subinventory,'-1$1') <>
4017 NVL(p_delivery_details_info.subinventory, '-1$1')
4018 OR NVL(l_locator_id,-1) <>
4019 NVL(p_delivery_details_info.locator_id,-1)
4020 THEN
4021 wsh_container_actions.Update_child_inv_info(
4022 p_container_id =>
4023 p_delivery_details_info.delivery_detail_id,
4024 P_locator_id => p_delivery_details_info.locator_id
4025 ,
4026 P_subinventory => p_delivery_details_info.subinventory,
4027 X_return_status => l_return_status
4028 );
4029 wsh_util_core.api_post_call
4030 (
4031 p_return_status => l_return_status,
4032 x_num_warnings => l_num_warnings,
4033 x_num_errors => l_num_errors
4034 );
4035 END IF;
4036 END IF; --}
4037
4038 IF l_debug_on THEN
4039
4040 WSH_DEBUG_SV.log(l_module_name, 'l_gc3_is_installed', l_gc3_is_installed);
4041 WSH_DEBUG_SV.log(l_module_name, 'l_delivery_details_info.ignore_for_planning', p_delivery_details_info.ignore_for_planning);
4042 WSH_DEBUG_SV.log(l_module_name, 'l_frozen_flag', l_frozen_flag);
4043 WSH_DEBUG_SV.log(l_module_name, 'p_delivery_details_info.requested_quantity', p_delivery_details_info.requested_quantity);
4044 WSH_DEBUG_SV.log(l_module_name, 'l_requested_quantity', l_requested_quantity);
4045 WSH_DEBUG_SV.log(l_module_name, 'p_delivery_details_info.weight_uom_code', p_delivery_details_info.weight_uom_code);
4046 WSH_DEBUG_SV.log(l_module_name, 'l_tmp_wt_uom_code', l_tmp_wt_uom_code);
4047 WSH_DEBUG_SV.log(l_module_name, 'p_delivery_details_info.net_weight', p_delivery_details_info.net_weight);
4048 WSH_DEBUG_SV.log(l_module_name, 'l_net_wt', l_net_wt);
4049 WSH_DEBUG_SV.log(l_module_name, 'p_delivery_details_info.gross_weight', p_delivery_details_info.gross_weight);
4050 WSH_DEBUG_SV.log(l_module_name, 'l_gross_wt', l_gross_wt);
4051 WSH_DEBUG_SV.log(l_module_name, 'p_delivery_details_info.volume', p_delivery_details_info.volume);
4052 WSH_DEBUG_SV.log(l_module_name, 'l_volume', l_volume);
4053 WSH_DEBUG_SV.log(l_module_name, 'p_delivery_details_info.volume_uom_code', p_delivery_details_info.volume_uom_code);
4054 WSH_DEBUG_SV.log(l_module_name, 'l_tmp_vol_uom_code', l_tmp_vol_uom_code);
4055 WSH_DEBUG_SV.log(l_module_name, 'p_delivery_details_info.shipped_quantity', p_delivery_details_info.shipped_quantity);
4056 WSH_DEBUG_SV.log(l_module_name, 'l_shipped_quantity', l_shipped_quantity);
4057 END IF;
4058
4059 -- OTM R12 : packing ECO
4060 IF (l_gc3_is_installed = 'Y' AND
4061 (nvl(p_delivery_details_info.requested_quantity, -1) <>
4062 nvl(l_requested_quantity, -1) OR
4063 nvl(p_delivery_details_info.weight_uom_code, '@@') <>
4064 nvl(l_tmp_wt_uom_code, '@@') OR
4065 nvl(p_delivery_details_info.net_weight, -1) <>
4066 nvl(l_net_wt, -1) OR
4067 nvl(p_delivery_details_info.gross_weight, -1) <>
4068 nvl(l_gross_wt, -1) OR
4069 nvl(p_delivery_details_info.volume, -1) <>
4070 nvl(l_volume, -1) OR -- packing ECO
4071 nvl(p_delivery_details_info.volume_uom_code, '@@') <>
4072 nvl(l_tmp_vol_uom_code, '@@')) AND -- packing ECO
4073 nvl(p_delivery_details_info.IGNORE_FOR_PLANNING,'N') = 'N') THEN
4074
4075 IF NOT (nvl(p_delivery_details_info.requested_quantity, -1) =
4076 nvl(l_requested_quantity, -1) AND
4077 nvl(p_delivery_details_info.weight_uom_code, '@@') =
4078 nvl(l_tmp_wt_uom_code, '@@') AND
4079 nvl(p_delivery_details_info.volume_uom_code, '@@') =
4080 nvl(l_tmp_vol_uom_code, '@@') AND -- packing ECO
4081 nvl(p_delivery_details_info.shipped_quantity, -1) <>
4082 nvl(l_shipped_quantity, -1) AND
4083 nvl(l_frozen_flag, 'N') = 'N') THEN
4084 --weight change is not caused by shipped quantity change
4085
4086 OPEN c_get_delivery_info(p_delivery_details_info.delivery_detail_id);
4087 FETCH c_get_delivery_info INTO l_delivery_id;
4088 IF (c_get_delivery_info%NOTFOUND) THEN
4089 l_delivery_id := NULL;
4090 END IF;
4091 CLOSE c_get_delivery_info;
4092
4093 -- only proceed if tms_interface_flag is in the following
4094 -- which will be set to UPDATE_REQUIRED
4095 IF (l_delivery_id IS NOT NULL) THEN
4096 l_delivery_id_tab(1) := l_delivery_id;
4097 l_interface_flag_tab(1) := WSH_NEW_DELIVERIES_PVT.C_TMS_UPDATE_REQUIRED;
4098
4099 IF l_debug_on THEN
4100 WSH_DEBUG_SV.log(l_module_name, 'l_delivery_id_tab', l_delivery_id_tab(1));
4101 WSH_DEBUG_SV.log(l_module_name, 'l_interface_flag_tab', l_interface_flag_tab(1));
4102 END IF;
4103
4104 WSH_NEW_DELIVERIES_PVT.UPDATE_TMS_INTERFACE_FLAG(
4105 p_delivery_id_tab => l_delivery_id_tab,
4106 p_tms_interface_flag_tab => l_interface_flag_tab,
4107 x_return_status => l_return_status);
4108
4109 IF (l_return_status IN (WSH_UTIL_CORE.G_RET_STS_ERROR, WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR)) THEN
4110 x_return_status := l_return_status;
4111 IF l_debug_on THEN
4112 WSH_DEBUG_SV.logmsg(l_module_name,'Error in WSH_NEW_DELIVERIES_PVT.UPDATE_TMS_INTERFACE_FLAG');
4113 WSH_DEBUG_SV.log(l_module_name,'l_return_status', l_return_status);
4114 WSH_DEBUG_SV.pop(l_module_name);
4115 END IF;
4116 RETURN;
4117 END IF;
4118 END IF; -- if l_delivery_id is not null
4119 END IF; -- IF NOT ...
4120 END IF; -- if g_gc3_is_installed
4121 -- End of OTM R12
4122
4123 --
4124
4125 -- bug # 6749200 (replenishment project) : back order consolidation for dynamic replenishment case.
4126 IF (l_back_order_consolidation = 'Y' ) THEN
4127 --{
4128
4129 IF l_debug_on THEN
4130 WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_WF_STD.Raise_Event',WSH_DEBUG_SV.C_PROC_LEVEL);
4131 END IF;
4132 --Raise Event : backorder business event
4133 WSH_WF_STD.Raise_Event(
4134 p_entity_type => 'LINE',
4135 p_entity_id => p_delivery_details_info.delivery_detail_id ,
4136 p_event => 'oracle.apps.wsh.line.gen.backordered' ,
4137 p_organization_id => p_delivery_details_info.organization_id,
4138 x_return_status => l_wf_rs ) ;
4139
4140 IF l_debug_on THEN
4141 WSH_DEBUG_SV.log(l_module_name,'Current Time is ',SYSDATE);
4142 WSH_DEBUG_SV.log(l_module_name,'Delivery Detail Id is ',p_delivery_details_info.delivery_detail_id );
4143 wsh_debug_sv.log(l_module_name,'Return Status After Calling WSH_WF_STD.Raise_Event',l_wf_rs);
4144 END IF;
4145 --Done Raise Event: backorder business event
4146
4147 -- Debug Statements
4148 --
4149 IF l_debug_on THEN
4150 WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_SHIPPING_PARAMS_PVT.Get_Global_Parameters',WSH_DEBUG_SV.C_PROC_LEVEL);
4151 END IF;
4152 --
4153 WSH_SHIPPING_PARAMS_PVT.Get_Global_Parameters(l_global_param_rec, l_return_status);
4154 IF l_return_status IN (WSH_UTIL_CORE.G_RET_STS_ERROR, WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR) THEN
4155 wsh_util_core.add_message(l_return_status,'WSH_DELIVERY_DETAILS_ACTIONS.Consolidate_Delivery_Details');
4156 x_return_status := l_return_status;
4157 END IF;
4158 IF (l_global_param_rec.consolidate_bo_lines = 'Y') THEN
4159 --{
4160 OPEN get_bo_dd_info;
4161 FETCH get_bo_dd_info INTO l_line_id,l_req,l_req2,l_del_id;
4162 CLOSE get_bo_dd_info;
4163 l_cons_source_line_rec_tab(1).delivery_detail_id := p_delivery_details_info.delivery_detail_id;
4164 l_cons_source_line_rec_tab(1).source_line_id := l_line_id;
4165 l_cons_source_line_rec_tab(1).delivery_id := l_del_id;
4166 l_cons_source_line_rec_tab(1).bo_qty := l_req;
4167 l_cons_source_line_rec_tab(1).req_qty := l_req;
4168 l_cons_source_line_rec_tab(1).bo_qty2 := l_req2;
4169 l_cons_source_line_rec_tab(1).req_qty2 := l_req2;
4170 --
4171 -- Debug Statements
4172 --
4173 IF l_debug_on THEN
4174 WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_DELIVERY_DETAILS_ACTIONS.Consolidate_Source_Line',WSH_DEBUG_SV.C_PROC_LEVEL);
4175 END IF;
4176 --
4177 WSH_DELIVERY_DETAILS_ACTIONS.Consolidate_Source_Line(
4178 p_Cons_Source_Line_Rec_Tab => l_cons_source_line_rec_tab,
4179 x_consolidate_ids => l_cons_dd_ids,
4180 x_return_status => l_return_status);
4181 IF l_return_status IN (WSH_UTIL_CORE.G_RET_STS_ERROR,WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR )THEN
4182 --{
4183 x_return_status := l_return_status;
4184 --}
4185 END IF;
4186 --}
4187 END IF;
4188 --}
4189 END IF;
4190 -- bug # 6749200 (replenishment project) : end
4191
4192 IF l_num_errors > 0
4193 THEN
4194 x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
4195 ELSIF l_num_warnings > 0
4196 THEN
4197 x_return_status := WSH_UTIL_CORE.G_RET_STS_WARNING;
4198 ELSE
4199 x_return_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
4200 END IF;
4201 IF l_debug_on THEN
4202 WSH_DEBUG_SV.pop(l_module_name);
4203 END IF;
4204 --
4205
4206 EXCEPTION
4207 --lpn conv
4208 WHEN FND_API.G_EXC_ERROR THEN
4209 ROLLBACK TO s_before_update_dd;
4210 x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
4211
4212 IF (c_get_delivery_info%ISOPEN) THEN
4213 CLOSE c_get_delivery_info;
4214 END IF;
4215
4216 IF (get_dd_info%ISOPEN) THEN
4217 CLOSE get_dd_info;
4218 END IF;
4219
4220 --
4221 IF l_debug_on THEN
4222 WSH_DEBUG_SV.logmsg(l_module_name,'FND_API.G_EXC_ERROR exception has occured ',WSH_DEBUG_SV.C_EXCEP_LEVEL);
4223 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:FND_API.G_EXC_ERROR');
4224 END IF;
4225 --
4226 WHEN e_wt_vol_fail THEN
4227 ROLLBACK TO s_before_update_dd;
4228 FND_MESSAGE.SET_NAME('WSH','WSH_DET_WT_VOL_FAILED');
4229 FND_MESSAGE.SET_TOKEN('DETAIL_ID',p_delivery_details_info.delivery_detail_id);
4230 x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
4231 WSH_UTIL_CORE.add_message (x_return_status);
4232
4233 IF (get_dd_info%ISOPEN) THEN
4234 CLOSE get_dd_info;
4235 END IF;
4236
4237 IF l_debug_on THEN
4238 WSH_DEBUG_SV.logmsg(l_module_name,'E_WT_VOL_FAIL exception has occured.',WSH_DEBUG_SV.C_EXCEP_LEVEL);
4239 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:E_WT_VOL_FAIL');
4240 END IF;
4241
4242 WHEN others THEN
4243 x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR;
4244 ROLLBACK TO s_before_update_dd;
4245 wsh_util_core.default_handler('WSH_DELIVERY_DETAILS_PKG.UPDATE_DELIVERY_DETAILS',l_module_name);
4246
4247 IF (c_get_delivery_info%ISOPEN) THEN
4248 CLOSE c_get_delivery_info;
4249 END IF;
4250
4251 IF (get_dd_info%ISOPEN) THEN
4252 CLOSE get_dd_info;
4253 END IF;
4254
4255 --
4256 IF l_debug_on THEN
4257 WSH_DEBUG_SV.logmsg(l_module_name,'Unexpected error has occured. Oracle error message is '|| SQLERRM,WSH_DEBUG_SV.C_UNEXPEC_ERR_LEVEL);
4258 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:OTHERS');
4259 END IF;
4260 --
4261 END Update_Delivery_Details;
4262
4263 --
4264 -- Procedure: Create_Delivery_Assignments
4265 -- Parameters: All Attributes of a Delivery Assignment Record,
4266 -- Row_id out
4267 -- Delivery_Assignment_id out
4268 -- Return_Status out
4269 -- Description: This procedure will create a delivery_assignment
4270 -- It will return to the use the delivery_assignment_id
4271 -- if not provided as a parameter.
4272 --
4273
4274 PROCEDURE Create_Delivery_Assignments(
4275 p_delivery_assignments_info IN Delivery_Assignments_Rec_Type,
4276 x_rowid OUT NOCOPY VARCHAR2,
4277 x_delivery_assignment_id OUT NOCOPY NUMBER,
4278 x_return_status OUT NOCOPY VARCHAR2
4279 ) IS
4280
4281
4282 CURSOR C_Del_Assign_Rowid
4283 IS SELECT rowid
4284 FROM wsh_delivery_assignments
4285 WHERE delivery_assignment_id = x_delivery_assignment_id;
4286
4287 CURSOR C_Del_Assign_ID
4288 IS SELECT wsh_delivery_assignments_s.nextval
4289 FROM sys.dual;
4290
4291 CURSOR C_Check_Assignment_ID
4292 IS
4293 SELECT rowid
4294 FROM wsh_delivery_assignments
4295 WHERE delivery_assignment_id = x_delivery_assignment_id;
4296
4297 l_row_count NUMBER;
4298 l_temp_id NUMBER;
4299
4300 others exception;
4301
4302 l_dd_id_tab WSH_UTIL_CORE.id_tab_type;
4303 l_da_id_tab WSH_UTIL_CORE.id_tab_type;
4304
4305 --
4306 l_debug_on BOOLEAN;
4307 --
4308 l_module_name CONSTANT VARCHAR2(100) := 'wsh.plsql.' || G_PKG_NAME || '.' || 'CREATE_DELIVERY_ASSIGNMENTS';
4309 --
4310 BEGIN
4311 --
4312 --
4313 l_debug_on := WSH_DEBUG_INTERFACE.g_debug;
4314 --
4315 IF l_debug_on IS NULL
4316 THEN
4317 l_debug_on := WSH_DEBUG_SV.is_debug_enabled;
4318 END IF;
4319 --
4320 IF l_debug_on THEN
4321 WSH_DEBUG_SV.push(l_module_name);
4322 END IF;
4323 --
4324 x_return_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
4325
4326 -- delivery assignment id should not be populated before hand
4327 /*
4328 IF p_delivery_assignments_info.delivery_assignment_id IS NOT NULL THEN
4329 RETURN;
4330 END IF;
4331 */
4332 l_dd_id_tab(1) := p_delivery_assignments_info.delivery_detail_id;
4333
4334 IF l_debug_on THEN
4335 WSH_DEBUG_SV.logmsg(l_module_name,'Calling Create_Deliv_assignment_Bulk with 1');
4336 END IF;
4337 -- from this API call Bulk api with value of 1
4338
4339 WSH_DELIVERY_DETAILS_PKG.Create_Deliv_Assignment_Bulk
4340 (p_delivery_assignments_info => p_delivery_assignments_info,
4341 p_num_of_rec => 1,
4342 p_dd_id_tab => l_dd_id_tab,
4343 x_da_id_tab => l_da_id_tab,
4344 x_return_status => x_return_status);
4345
4346 x_delivery_assignment_id := l_da_id_tab(1);
4347
4348 OPEN C_Del_Assign_Rowid;
4349 FETCH C_Del_Assign_Rowid INTO x_rowid;
4350 IF (C_Del_Assign_Rowid%NOTFOUND) THEN
4351 CLOSE C_Del_Assign_Rowid;
4352 RAISE others;
4353 ELSE
4354 x_return_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
4355 END IF;
4356 CLOSE C_Del_Assign_Rowid;
4357 --
4358 IF l_debug_on THEN
4359 WSH_DEBUG_SV.pop(l_module_name);
4360 END IF;
4361 --
4362 EXCEPTION
4363 WHEN others THEN
4364 x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR;
4365 wsh_util_core.default_handler('WSH_DELIVERY_DETAILS_PKG.CREATE_DELIVERY_ASSIGNMENTS',l_module_name);
4366
4367 --
4368 IF l_debug_on THEN
4369 WSH_DEBUG_SV.logmsg(l_module_name,'Unexpected error has occured. Oracle error message is '|| SQLERRM,WSH_DEBUG_SV.C_UNEXPEC_ERR_LEVEL);
4370 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:OTHERS');
4371 END IF;
4372 --
4373 END Create_Delivery_Assignments;
4374
4375 --
4376 -- Procedure: Delete_Delivery_Assignments
4377 -- Parameters: All Attributes of a Delivery Assignment Record,
4378 -- Row_id out
4379 -- Delivery_Assignment_id out
4380 -- Return_Status out
4381 -- Description: This procedure will delete a delivery assignment.
4382 -- It will return to the use the delivery_assignment id
4383 -- if not provided as a parameter.
4384 --
4385 -- OTM R12 : This procedure was reviewed during OTM R12 frontport project
4386 -- but not modified since it's not called from anywhere
4387 -- It should be modified properly when it will be in use
4388 -- Refer to TDD for the details of expected changes
4389
4390 PROCEDURE Delete_Delivery_Assignments
4391 (p_rowid IN VARCHAR2,
4392 p_delivery_assignment_id IN NUMBER,
4393 x_return_status OUT NOCOPY VARCHAR2)
4394 IS
4395
4396 CURSOR get_del_assignment_id_rowid (v_rowid VARCHAR2) IS
4397 SELECT delivery_assignment_id
4398 FROM wsh_delivery_assignments
4399 WHERE rowid = v_rowid;
4400
4401 l_delivery_assignment_id NUMBER;
4402 others exception;
4403
4404 --
4405 l_debug_on BOOLEAN;
4406 --
4407 l_module_name CONSTANT VARCHAR2(100) := 'wsh.plsql.' || G_PKG_NAME || '.' || 'DELETE_DELIVERY_ASSIGNMENTS';
4408 --
4409 BEGIN
4410 --
4411 --
4412 l_debug_on := WSH_DEBUG_INTERFACE.g_debug;
4413 --
4414 IF l_debug_on IS NULL
4415 THEN
4416 l_debug_on := WSH_DEBUG_SV.is_debug_enabled;
4417 END IF;
4418 --
4419 IF l_debug_on THEN
4420 WSH_DEBUG_SV.push(l_module_name);
4421 --
4422 WSH_DEBUG_SV.log(l_module_name,'P_ROWID',P_ROWID);
4423 WSH_DEBUG_SV.log(l_module_name,'P_DELIVERY_ASSIGNMENT_ID',P_DELIVERY_ASSIGNMENT_ID);
4424 END IF;
4425 --
4426 x_return_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
4427
4428 IF p_rowid IS NOT NULL THEN
4429 OPEN get_del_assignment_id_rowid(p_rowid);
4430 FETCH get_del_assignment_id_rowid INTO l_delivery_assignment_id;
4431 CLOSE get_del_assignment_id_rowid;
4432 END IF;
4433
4434 IF l_delivery_assignment_id IS NULL THEN
4435 l_delivery_assignment_id := p_delivery_assignment_id;
4436 END IF;
4437
4438 IF p_delivery_assignment_id IS NOT NULL THEN
4439 DELETE FROM wsh_delivery_assignments
4440 WHERE delivery_assignment_id = l_delivery_assignment_id;
4441 x_return_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
4442 ELSE
4443 raise others;
4444 END IF;
4445
4446 --
4447 IF l_debug_on THEN
4448 WSH_DEBUG_SV.pop(l_module_name);
4449 END IF;
4450 --
4451 EXCEPTION
4452 WHEN others THEN
4453 x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR;
4454 wsh_util_core.default_handler('WSH_DELIVERY_DETAILS_PKG_.DELETE_DELIVERY_ASSIGNMENTS',l_module_name);
4455
4456 --
4457 IF l_debug_on THEN
4458 WSH_DEBUG_SV.logmsg(l_module_name,'Unexpected error has occured. Oracle error message is '|| SQLERRM,WSH_DEBUG_SV.C_UNEXPEC_ERR_LEVEL);
4459 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:OTHERS');
4460 END IF;
4461 --
4462 END Delete_Delivery_Assignments;
4463
4464 --
4465 -- Procedure: Update_Delivery_Assignments
4466 -- Parameters:
4467 -- Row_id in
4468 -- Return_Status out
4469 -- Description: This procedure will update a delivery assignment.
4470 --
4471 -- OTM R12 : This procedure was reviewed during OTM R12 frontport project
4472 -- but not modified since it's not called from anywhere
4473 -- It should be modified properly when it will be in use
4474 -- Refer to TDD for the details of expected changes
4475 PROCEDURE Update_Delivery_Assignments(
4476 p_rowid IN VARCHAR2 := NULL,
4477 p_delivery_assignments_info IN Delivery_Assignments_Rec_Type,
4478 x_return_status OUT NOCOPY VARCHAR2
4479 ) IS
4480 CURSOR get_rowid IS
4481 SELECT rowid
4482 FROM wsh_delivery_assignments
4483 WHERE delivery_assignment_id = p_delivery_assignments_info.delivery_assignment_id;
4484
4485 l_rowid VARCHAR2(30);
4486 others exception;
4487
4488 --
4489 l_debug_on BOOLEAN;
4490 --
4491 l_module_name CONSTANT VARCHAR2(100) := 'wsh.plsql.' || G_PKG_NAME || '.' || 'UPDATE_DELIVERY_ASSIGNMENTS';
4492 --
4493 BEGIN
4494 --
4495 --
4496 l_debug_on := WSH_DEBUG_INTERFACE.g_debug;
4497 --
4498 IF l_debug_on IS NULL
4499 THEN
4500 l_debug_on := WSH_DEBUG_SV.is_debug_enabled;
4501 END IF;
4502 --
4503 IF l_debug_on THEN
4504 WSH_DEBUG_SV.push(l_module_name);
4505 --
4506 WSH_DEBUG_SV.log(l_module_name,'P_ROWID',P_ROWID);
4507 END IF;
4508 --
4509 x_return_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
4510
4511 IF (p_rowid IS NULL) THEN
4512 OPEN get_rowid;
4513 FETCH get_rowid INTO l_rowid;
4514
4515 IF (get_rowid%NOTFOUND) THEN
4516 CLOSE get_rowid;
4517 RAISE no_data_found;
4518 END IF;
4519 CLOSE get_rowid;
4520
4521 ELSE
4522 l_rowid := p_rowid;
4523 END IF;
4524
4525 UPDATE wsh_delivery_assignments
4526 SET
4527 delivery_id = p_delivery_assignments_info.delivery_id,
4528 parent_delivery_id = p_delivery_assignments_info.parent_delivery_id,
4529 delivery_detail_id = p_delivery_assignments_info.delivery_detail_id,
4530 parent_delivery_detail_id = p_delivery_assignments_info.parent_delivery_detail_id,
4531 last_update_date = p_delivery_assignments_info.last_update_date,
4532 last_updated_by = p_delivery_assignments_info.last_updated_by,
4533 last_update_login = p_delivery_assignments_info.last_update_login,
4534 program_application_id = p_delivery_assignments_info.program_application_id,
4535 program_id = p_delivery_assignments_info.program_id,
4536 program_update_date = p_delivery_assignments_info.program_update_date,
4537 request_id = p_delivery_assignments_info.request_id,
4538 active_flag = p_delivery_assignments_info.active_flag,
4539 delivery_assignment_id = p_delivery_assignments_info.delivery_assignment_id
4540 WHERE rowid = l_rowid;
4541
4542 IF (SQL%NOTFOUND) THEN
4543 raise others;
4544 ELSE
4545 x_return_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
4546 END IF;
4547
4548 --
4549 IF l_debug_on THEN
4550 WSH_DEBUG_SV.pop(l_module_name);
4551 END IF;
4552 --
4553 EXCEPTION
4554 WHEN others THEN
4555 x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR;
4556 wsh_util_core.default_handler('WSH_DELIVERY_DETAILS_PKG.UPDATE_DELIVERY_ASSIGNMENTS',l_module_name);
4557
4558 --
4559 IF l_debug_on THEN
4560 WSH_DEBUG_SV.logmsg(l_module_name,'Unexpected error has occured. Oracle error message is '|| SQLERRM,WSH_DEBUG_SV.C_UNEXPEC_ERR_LEVEL);
4561 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:OTHERS');
4562 END IF;
4563 --
4564 END Update_Delivery_Assignments;
4565
4566 --
4567 -- Procedure: Lock_Delivery_Assignments
4568 -- Parameters: All Attributes of a Delivery Assignment Record,
4569 -- Row_id in
4570 -- Return_Status out
4571 -- Description: This procedure will lock a delivery assignment.
4572 --
4573
4574 PROCEDURE Lock_Delivery_Assignments (
4575 p_rowid IN VARCHAR2,
4576 p_delivery_assignments_info IN Delivery_Assignments_Rec_Type,
4577 x_return_status OUT NOCOPY VARCHAR2
4578 ) IS
4579
4580 CURSOR lock_row IS
4581 SELECT *
4582 FROM wsh_delivery_assignments
4583 WHERE rowid = p_rowid
4584 FOR UPDATE OF delivery_assignment_id NOWAIT;
4585
4586 Recinfo lock_row%ROWTYPE;
4587 others exception;
4588
4589 --
4590 l_debug_on BOOLEAN;
4591 --
4592 l_module_name CONSTANT VARCHAR2(100) := 'wsh.plsql.' || G_PKG_NAME || '.' || 'LOCK_DELIVERY_ASSIGNMENTS';
4593 --
4594 BEGIN
4595 --
4596 --
4597 l_debug_on := WSH_DEBUG_INTERFACE.g_debug;
4598 --
4599 IF l_debug_on IS NULL
4600 THEN
4601 l_debug_on := WSH_DEBUG_SV.is_debug_enabled;
4602 END IF;
4603 --
4604 IF l_debug_on THEN
4605 WSH_DEBUG_SV.push(l_module_name);
4606 --
4607 WSH_DEBUG_SV.log(l_module_name,'P_ROWID',P_ROWID);
4608 END IF;
4609 --
4610 x_return_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
4611
4612 OPEN lock_row;
4613 FETCH lock_row INTO Recinfo;
4614 IF (lock_row%NOTFOUND) THEN
4615 CLOSE lock_row;
4616 x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
4617 FND_MESSAGE.Set_Name('FND','FORM_RECORD_DELETED');
4618 wsh_util_core.add_message(x_return_status,l_module_name);
4619 --
4620 IF l_debug_on THEN
4621 WSH_DEBUG_SV.pop(l_module_name);
4622 END IF;
4623 --
4624 RETURN;
4625 END IF;
4626 CLOSE lock_row;
4627
4628 IF (
4629 (Recinfo.delivery_assignment_id = p_delivery_assignments_info.delivery_assignment_id)
4630 AND ( (Recinfo.delivery_id = p_delivery_assignments_info.delivery_id)
4631 OR ( (Recinfo.delivery_id IS NULL)
4632 AND (p_delivery_assignments_info.delivery_id IS NULL)))
4633 AND ( (Recinfo.parent_delivery_id = p_delivery_assignments_info.parent_delivery_id)
4634 OR ( (Recinfo.parent_delivery_id IS NULL)
4635 AND (p_delivery_assignments_info.parent_delivery_id IS NULL)))
4636 AND ( (Recinfo.delivery_detail_id = p_delivery_assignments_info.parent_delivery_id)
4637 OR ( (Recinfo.delivery_detail_id IS NULL)
4638 AND (p_delivery_assignments_info.delivery_detail_id IS NULL)))
4639 AND (Recinfo.creation_date = p_delivery_assignments_info.creation_date)
4640 AND (Recinfo.created_by = p_delivery_assignments_info.created_by)
4641 AND (Recinfo.last_update_date = p_delivery_assignments_info.last_update_date)
4642 AND (Recinfo.last_updated_by = p_delivery_assignments_info.last_updated_by)
4643 AND ( (Recinfo.last_update_login = p_delivery_assignments_info.last_update_login)
4644 OR ( (Recinfo.last_update_login IS NULL)
4645 AND (p_delivery_assignments_info.last_update_login IS NULL)))
4646 AND ( (Recinfo.program_application_id = p_delivery_assignments_info.program_application_id)
4647 OR ( (Recinfo.program_application_id IS NULL)
4648 AND (p_delivery_assignments_info.program_application_id IS NULL))))
4649 THEN
4650 x_return_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
4651 --
4652 IF l_debug_on THEN
4653 WSH_DEBUG_SV.pop(l_module_name);
4654 END IF;
4655 --
4656 RETURN;
4657 ELSE
4658 -- x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
4659 FND_MESSAGE.Set_Name('FND', 'FORM_RECORD_CHANGED');
4660 IF l_debug_on THEN
4661 WSH_DEBUG_SV.log(l_module_name,'FORM RECORD CHANGED');
4662 END IF;
4663 app_exception.raise_exception;
4664
4665 END IF;
4666
4667 --
4668 IF l_debug_on THEN
4669 WSH_DEBUG_SV.pop(l_module_name);
4670 END IF;
4671 --
4672 EXCEPTION
4673 WHEN others THEN
4674 wsh_util_core.default_handler('WSH_DELIVERY_DETAILS_PKG.LOCK_DELIVERY_ASSIGNMENTS',l_module_name);
4675 x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR;
4676 --
4677 IF l_debug_on THEN
4678 WSH_DEBUG_SV.logmsg(l_module_name,'Unexpected error has occured. Oracle error message is '|| SQLERRM,WSH_DEBUG_SV.C_UNEXPEC_ERR_LEVEL);
4679 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:OTHERS');
4680 END IF;
4681 --
4682 END Lock_Delivery_Assignments;
4683
4684
4685 procedure Lock_Delivery_Details(
4686 p_rec_attr_tab IN WSH_GLBL_VAR_STRCT_GRP.Delivery_Details_Attr_Tbl_Type,
4687 p_caller IN VARCHAR2,
4688 p_valid_index_tab IN wsh_util_core.id_tab_type,
4689 x_valid_ids_tab OUT NOCOPY wsh_util_core.id_tab_type,
4690 x_return_status OUT NOCOPY VARCHAR2
4691 )
4692
4693 IS
4694 --
4695 --
4696 l_index NUMBER := 0;
4697 l_num_errors NUMBER := 0;
4698 --
4699 l_debug_on BOOLEAN;
4700 --
4701 l_module_name CONSTANT VARCHAR2(100) := 'wsh.plsql.' || G_PKG_NAME || '.' || 'LOCK_DELIVERY_DETAILS_WRAPPER';
4702 --
4703 BEGIN
4704 --
4705 l_debug_on := WSH_DEBUG_INTERFACE.g_debug;
4706 --
4707 IF l_debug_on IS NULL
4708 THEN
4709 l_debug_on := WSH_DEBUG_SV.is_debug_enabled;
4710 END IF;
4711 --
4712 IF l_debug_on THEN
4713 WSH_DEBUG_SV.push(l_module_name);
4714 WSH_DEBUG_SV.log(l_module_name,'p_caller',p_caller);
4715 WSH_DEBUG_SV.log(l_module_name,'Total Number of Delivery Detail Records being locked',p_valid_index_tab.COUNT);
4716 END IF;
4717 --
4718 --
4719 l_index := p_valid_index_tab.FIRST;
4720 --
4721 while l_index is not null loop
4722 begin
4723 --
4724 savepoint lock_delivery_detail_loop;
4725 --
4726 if p_caller = 'WSH_FSTRX' then
4727 lock_delivery_details(p_rowid => p_rec_attr_tab(l_index).rowid,
4728 p_delivery_details_info => p_rec_attr_tab(l_index)
4729 );
4730 else
4731 lock_detail_no_compare(
4732 p_delivery_detail_id => p_rec_attr_tab(l_index).delivery_detail_id);
4733 end if;
4734
4735 IF nvl(p_caller,FND_API.G_MISS_CHAR) <> 'WSH_FSTRX' THEN
4736 x_valid_ids_tab(x_valid_ids_tab.COUNT + 1) := p_rec_attr_tab(l_index).delivery_detail_id;
4737 ELSE
4738 x_valid_ids_tab(x_valid_ids_tab.COUNT + 1) := l_index;
4739 END IF;
4740 --
4741 exception
4742 --
4743 WHEN app_exception.application_exception or app_exception.record_lock_exception THEN
4744 rollback to lock_delivery_detail_loop;
4745 IF nvl(p_caller,'!') = 'WSH_PUB'
4746 OR nvl(p_caller, '!') like 'FTE%' THEN
4747 FND_MESSAGE.SET_NAME('WSH', 'WSH_DLVB_LOCK_FAILED');
4748 FND_MESSAGE.SET_TOKEN('ENTITY_NAME',p_rec_attr_tab(l_index).delivery_detail_id);
4749 wsh_util_core.add_message(wsh_util_core.g_ret_sts_error, l_module_name);
4750 END IF;
4751 l_num_errors := l_num_errors + 1;
4752 --
4753 IF l_debug_on THEN
4754 WSH_DEBUG_SV.log(l_module_name,'Unable to obtain lock on the Delivery Detail Id',p_rec_attr_tab(l_index).delivery_detail_id);
4755 END IF;
4756 --
4757 WHEN others THEN
4758 rollback to lock_delivery_detail_loop;
4759 raise FND_API.G_EXC_UNEXPECTED_ERROR;
4760 end;
4761 --
4762 l_index := p_valid_index_tab.NEXT(l_index);
4763 --
4764 end loop;
4765 --
4766 IF p_valid_index_tab.COUNT = 0 THEN
4767 x_return_status := wsh_util_core.g_ret_sts_success;
4768 ELSIF l_num_errors = p_valid_index_tab.COUNT THEN
4769 FND_MESSAGE.SET_NAME('WSH', 'WSH_UI_NOT_PERFORMED');
4770 x_return_status := wsh_util_core.g_ret_sts_error;
4771 wsh_util_core.add_message(x_return_status,l_module_name);
4772 IF l_debug_on THEN
4773 wsh_debug_sv.logmsg(l_module_name, 'WSH_UI_NOT_PERFORMED');
4774 END IF;
4775 raise fnd_api.g_exc_error;
4776 ELSIF l_num_errors > 0 THEN
4777 FND_MESSAGE.SET_NAME('WSH', 'WSH_UI_NOT_PROCESSED');
4778 x_return_status := wsh_util_core.g_ret_sts_warning;
4779 wsh_util_core.add_message(x_return_status,l_module_name);
4780 IF l_debug_on THEN
4781 wsh_debug_sv.logmsg(l_module_name, 'WSH_UI_NOT_PROCESSED');
4782 END IF;
4783 raise wsh_util_core.g_exc_warning;
4784 ELSE
4785 x_return_status := wsh_util_core.g_ret_sts_success;
4786 END IF;
4787 --
4788 IF l_debug_on THEN
4789 WSH_DEBUG_SV.pop(l_module_name);
4790 END IF;
4791 --
4792 EXCEPTION
4793 --
4794 --
4795 WHEN FND_API.G_EXC_ERROR THEN
4796 --
4797 x_return_status := wsh_util_core.g_ret_sts_error;
4798 --
4799 IF l_debug_on THEN
4800 WSH_DEBUG_SV.logmsg(l_module_name,'FND_API.G_EXC_ERROR exception has occured ',WSH_DEBUG_SV.C_EXCEP_LEVEL);
4801 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:FND_API.G_EXC_ERROR');
4802 END IF;
4803 --
4804 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
4805 x_return_status := wsh_util_core.g_ret_sts_unexp_error;
4806 --
4807 IF l_debug_on THEN
4808 WSH_DEBUG_SV.logmsg(l_module_name,'Unexpected error has occured. Oracle error message is '|| SQLERRM,WSH_DEBUG_SV.C_UNEXPEC_ERR_LEVEL);
4809 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:FND_API.G_EXC_UNEXPECTED_ERROR');
4810 END IF;
4811 --
4812 WHEN WSH_UTIL_CORE.G_EXC_WARNING THEN
4813 x_return_status := wsh_util_core.g_ret_sts_warning;
4814 --
4815 IF l_debug_on THEN
4816 WSH_DEBUG_SV.logmsg(l_module_name,'WSH_UTIL_CORE.G_EXC_WARNING exception has occured ',WSH_DEBUG_SV.C_EXCEP_LEVEL);
4817 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:WSH_UTIL_CORE.G_EXC_WARNING');
4818 END IF;
4819 --
4820 WHEN OTHERS THEN
4821 --
4822 x_return_status := wsh_util_core.g_ret_sts_unexp_error;
4823 --
4824 wsh_util_core.default_handler('WSH_DELIVERY_DETAILS_PKG.LOCK_DELIVERY_DETAILS_WRAPPER',l_module_name);
4825 --
4826 IF l_debug_on THEN
4827 WSH_DEBUG_SV.logmsg(l_module_name,'Unexpected error has occured. Oracle error message is '|| SQLERRM,WSH_DEBUG_SV.C_UNEXPEC_ERR_LEVEL);
4828 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:OTHERS');
4829 END IF;
4830 --
4831 END Lock_Delivery_Details;
4832
4833 /* ---------------------------------------------------------------------
4834 Procedure: Lock_Detail_No_Compare
4835
4836 Parameters: Delivery_Detail Id DEFAULT NULL
4837 Delivery Id DEFAULT NULL
4838
4839 Description: This procedure is used for obtaining locks of lines/lpns
4840 using the delivery_detail_id or the delivery_id.
4841 This is called by delivery detail's wrapper lock API when the p_caller is NOT WSHFSTRX.
4842 It is also called by delivery's wrapper lock API when the
4843 action is CONFIRM, AUTO-PACK or AUTO-PACK-MASTER.
4844 This procedure does not compare the attributes. It just
4845 does a SELECT using FOR UPDATE NOWAIT
4846 Created: Harmonization Project. Patchset I
4847 ----------------------------------------------------------------------- */
4848
4849
4850 PROCEDURE Lock_Detail_No_Compare(
4851 p_delivery_detail_id IN NUMBER, -- default null in spec
4852 p_delivery_id IN NUMBER -- default null in spec
4853 )
4854 IS
4855
4856 CURSOR c_lock_details_of_dlvy(p_dlvy_id NUMBER) IS
4857 SELECT wdd.delivery_detail_id
4858 FROM wsh_delivery_details wdd, wsh_delivery_assignments wda
4859 WHERE wdd.delivery_detail_id = wda.delivery_detail_id
4860 AND wda.delivery_id = p_dlvy_id
4861 FOR UPDATE OF wdd.delivery_detail_id NOWAIT;
4862
4863 CURSOR c_lock_detail(p_detail_id NUMBER) IS
4864 SELECT delivery_detail_id
4865 FROM wsh_delivery_details
4866 WHERE delivery_detail_id = p_delivery_detail_id
4867 FOR UPDATE NOWAIT;
4868
4869 l_dummy_detail_id NUMBER;
4870 l_del_name VARCHAR2(30);
4871
4872 l_debug_on BOOLEAN;
4873 --
4874 l_module_name CONSTANT VARCHAR2(100) := 'wsh.plsql.' || G_PKG_NAME || '.' || 'LOCK_DETAIL_NO_COMPARE';
4875
4876 BEGIN
4877 --
4878 l_debug_on := WSH_DEBUG_INTERFACE.g_debug;
4879 --
4880 IF l_debug_on IS NULL
4881 THEN
4882 l_debug_on := WSH_DEBUG_SV.is_debug_enabled;
4883 END IF;
4884 --
4885 IF l_debug_on THEN
4886 WSH_DEBUG_SV.push(l_module_name);
4887 WSH_DEBUG_SV.log(l_module_name,'p_delivery_detail_id', p_delivery_detail_id);
4888 WSH_DEBUG_SV.log(l_module_name, 'p_delivery_id', p_delivery_id);
4889 END IF;
4890 --
4891
4892 IF p_delivery_Detail_id IS NOT NULL THEN
4893 open c_lock_detail(p_delivery_detail_id);
4894 fetch c_lock_detail into l_dummy_detail_id;
4895 close c_lock_detail;
4896 ELSIF p_delivery_id IS NOT NULL THEN
4897 OPEN c_lock_details_of_dlvy(p_delivery_id);
4898 CLOSE c_lock_details_of_dlvy;
4899 END IF;
4900
4901 IF l_debug_on THEN
4902 WSH_DEBUG_SV.pop(l_module_name);
4903 END IF;
4904 EXCEPTION
4905 WHEN app_exception.application_exception or app_exception.record_lock_exception THEN
4906 IF p_delivery_id IS NOT NULL THEN
4907 l_del_name := wsh_new_deliveries_pvt.get_name(p_delivery_id);
4908 FND_MESSAGE.SET_NAME('WSH', 'WSH_DLVY_LINE_LPN_LOCK');
4909 FND_MESSAGE.SET_TOKEN('DEL_NAME', l_del_name);
4910 wsh_util_core.add_message(wsh_util_core.g_ret_sts_error, l_module_name);
4911 IF l_debug_on THEN
4912 wsh_debug_sv.log(l_module_name, 'Could not obtain lock on some lines or lpns of delivery', p_delivery_id);
4913 END IF;
4914 ELSE
4915 IF l_debug_on THEN
4916 wsh_debug_sv.log(l_module_name, 'Could not obtain lock on del detail', p_delivery_detail_id);
4917 END IF;
4918 END IF;
4919
4920 IF l_debug_on THEN
4921 WSH_DEBUG_SV.logmsg(l_module_name,'APP_EXCEPTION.APPLICATION_EXCEPTION exception has occured.',WSH_DEBUG_SV.C_EXCEP_LEVEL);
4922 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:APP_EXCEPTION.APPLICATION_EXCEPTION');
4923 END IF;
4924 --
4925 RAISE;
4926
4927 END Lock_Detail_No_Compare;
4928
4929 -- Bug 3292364
4930 -- Procedure: Table_To_Record
4931 -- Parameters: x_delivery_detail_rec: A record of all attributes of a Delivery detail Record
4932 -- p_delivery_detail_id : delivery_detail_id of the detail that is to be copied
4933 -- Return_Status,
4934 -- Description: This procedure will copy the attributes of a delivery detail in wsh_delivery_details
4935 -- and copy it to a record.
4936
4937 Procedure Table_To_Record(
4938 p_delivery_detail_id IN NUMBER,
4939 x_delivery_detail_rec OUT NOCOPY WSH_GLBL_VAR_STRCT_GRP.Delivery_Details_Rec_Type,
4940 x_return_status OUT NOCOPY VARCHAR2) IS
4941
4942 CURSOR c_tbl_rec (p_delivery_detail_id IN NUMBER) IS
4943 SELECT delivery_detail_id
4944 ,source_code
4945 ,source_header_id
4946 ,source_line_id
4947 ,customer_id
4948 ,sold_to_contact_id
4949 ,inventory_item_id
4950 ,item_description
4951 ,hazard_class_id
4952 ,country_of_origin
4953 ,classification
4954 ,ship_from_location_id
4955 ,ship_to_location_id
4956 ,ship_to_contact_id
4957 ,ship_to_site_use_id
4958 ,deliver_to_location_id
4959 ,deliver_to_contact_id
4960 ,deliver_to_site_use_id
4961 ,intmed_ship_to_location_id
4962 ,intmed_ship_to_contact_id
4963 ,hold_code
4964 ,ship_tolerance_above
4965 ,ship_tolerance_below
4966 ,requested_quantity
4967 ,shipped_quantity
4968 ,delivered_quantity
4969 ,requested_quantity_uom
4970 ,subinventory
4971 ,revision
4972 ,lot_number
4973 ,customer_requested_lot_flag
4974 ,serial_number
4975 ,locator_id
4976 ,date_requested
4977 ,date_scheduled
4978 ,master_container_item_id
4979 ,detail_container_item_id
4980 ,load_seq_number
4981 ,ship_method_code
4982 ,carrier_id
4983 ,freight_terms_code
4984 ,shipment_priority_code
4985 ,fob_code
4986 ,customer_item_id
4987 ,dep_plan_required_flag
4988 ,customer_prod_seq
4989 ,customer_dock_code
4990 ,cust_model_serial_number
4991 ,customer_job
4992 ,customer_production_line
4993 ,net_weight
4994 ,weight_uom_code
4995 ,volume
4996 ,volume_uom_code
4997 ,tp_attribute_category
4998 ,tp_attribute1
4999 ,tp_attribute2
5000 ,tp_attribute3
5001 ,tp_attribute4
5002 ,tp_attribute5
5003 ,tp_attribute6
5004 ,tp_attribute7
5005 ,tp_attribute8
5006 ,tp_attribute9
5007 ,tp_attribute10
5008 ,tp_attribute11
5009 ,tp_attribute12
5010 ,tp_attribute13
5011 ,tp_attribute14
5012 ,tp_attribute15
5013 ,attribute_category
5014 ,attribute1
5015 ,attribute2
5016 ,attribute3
5017 ,attribute4
5018 ,attribute5
5019 ,attribute6
5020 ,attribute7
5021 ,attribute8
5022 ,attribute9
5023 ,attribute10
5024 ,attribute11
5025 ,attribute12
5026 ,attribute13
5027 ,attribute14
5028 ,attribute15
5029 ,created_by
5030 ,creation_date
5031 ,sysdate
5032 ,FND_GLOBAL.LOGIN_ID
5033 ,FND_GLOBAL.USER_ID
5034 ,program_application_id
5035 ,program_id
5036 ,program_update_date
5037 ,request_id
5038 ,mvt_stat_status
5039 ,NULL -- released_flag
5040 ,organization_id
5041 ,transaction_temp_id
5042 ,ship_set_id
5043 ,arrival_set_id
5044 ,ship_model_complete_flag
5045 ,top_model_line_id
5046 ,source_header_number
5047 ,source_header_type_id
5048 ,source_header_type_name
5049 ,cust_po_number
5050 ,ato_line_id
5051 ,src_requested_quantity
5052 ,src_requested_quantity_uom
5053 ,move_order_line_id
5054 ,cancelled_quantity
5055 ,quality_control_quantity
5056 ,cycle_count_quantity
5057 ,tracking_number
5058 ,movement_id
5059 ,shipping_instructions
5060 ,packing_instructions
5061 ,project_id
5062 ,task_id
5063 ,org_id
5064 ,oe_interfaced_flag
5065 ,split_from_delivery_detail_id
5066 ,inv_interfaced_flag
5067 ,source_line_number
5068 ,inspection_flag
5069 ,released_status
5070 ,container_flag
5071 ,container_type_code
5072 ,container_name
5073 ,fill_percent
5074 ,gross_weight
5075 ,master_serial_number
5076 ,maximum_load_weight
5077 ,maximum_volume
5078 ,minimum_fill_percent
5079 ,seal_code
5080 ,unit_number
5081 ,unit_price
5082 ,currency_code
5083 ,freight_class_cat_id
5084 ,commodity_code_cat_id
5085 ,preferred_grade
5086 ,src_requested_quantity2
5087 ,src_requested_quantity_uom2
5088 ,requested_quantity2
5089 ,shipped_quantity2
5090 ,delivered_quantity2
5091 ,cancelled_quantity2
5092 ,quality_control_quantity2
5093 ,cycle_count_quantity2
5094 ,requested_quantity_uom2
5095 -- HW OPMCONV - No need for sublot_number
5096 -- ,sublot_number
5097 ,lpn_id
5098 ,pickable_flag
5099 ,original_subinventory
5100 ,to_serial_number
5101 ,picked_quantity
5102 ,picked_quantity2
5103 ,received_quantity
5104 ,received_quantity2
5105 ,source_line_set_id
5106 ,batch_id
5107 ,NULL -- ROWID
5108 ,transaction_id
5109 ,vendor_id
5110 ,ship_from_site_id
5111 ,nvl(line_direction, 'O')
5112 ,party_id
5113 ,routing_req_id
5114 ,shipping_control
5115 ,source_blanket_reference_id
5116 ,source_blanket_reference_num
5117 ,po_shipment_line_id
5118 ,po_shipment_line_number
5119 ,returned_quantity
5120 ,returned_quantity2
5121 ,rcv_shipment_line_id
5122 ,source_line_type_code
5123 ,supplier_item_number
5124 ,nvl(ignore_for_planning, 'N')
5125 ,earliest_pickup_date
5126 ,latest_pickup_date
5127 ,earliest_dropoff_date
5128 ,latest_dropoff_date
5129 ,request_date_type_code
5130 ,tp_delivery_detail_id
5131 ,source_document_type_id
5132 ,unit_weight
5133 ,unit_volume
5134 ,filled_volume
5135 ,wv_frozen_flag
5136 ,mode_of_transport
5137 ,service_level
5138 ,po_revision_number
5139 ,release_revision_number
5140 ,replenishment_status --bug# 6689448 (replenishment project)
5141 FROM wsh_delivery_details
5142 WHERE delivery_detail_id = p_delivery_detail_id;
5143
5144 l_debug_on BOOLEAN;
5145 --
5146 l_module_name CONSTANT VARCHAR2(100) := 'wsh.plsql.' || G_PKG_NAME || '.' || 'Table_To_Record';
5147
5148
5149 BEGIN
5150
5151 l_debug_on := WSH_DEBUG_INTERFACE.g_debug;
5152 --
5153 IF l_debug_on IS NULL
5154 THEN
5155 l_debug_on := WSH_DEBUG_SV.is_debug_enabled;
5156 END IF;
5157 --
5158 IF l_debug_on THEN
5159 WSH_DEBUG_SV.push(l_module_name);
5160 WSH_DEBUG_SV.log(l_module_name,'p_delivery_detail_id', p_delivery_detail_id);
5161 END IF;
5162 --
5163
5164 OPEN c_tbl_rec (p_delivery_detail_id);
5165 FETCH c_tbl_rec INTO x_delivery_detail_rec;
5166 IF c_tbl_rec%NOTFOUND THEN
5167 --
5168 CLOSE c_tbl_rec;
5169 RAISE no_data_found;
5170 --
5171 END IF;
5172 CLOSE c_tbl_rec;
5173 IF l_debug_on THEN
5174 WSH_DEBUG_SV.pop(l_module_name);
5175 END IF;
5176 x_return_status := wsh_util_core.g_ret_sts_success;
5177 EXCEPTION
5178
5179 WHEN OTHERS THEN
5180 IF c_tbl_rec%ISOPEN THEN
5181 CLOSE c_tbl_rec;
5182 END IF;
5183 --
5184 x_return_status := wsh_util_core.g_ret_sts_unexp_error;
5185 --
5186 wsh_util_core.default_handler('WSH_DELIVERY_DETAILS_PKG.Table_to_Record',l_module_name);
5187 --
5188 IF l_debug_on THEN
5189 WSH_DEBUG_SV.logmsg(l_module_name,'Unexpected error has occured. Oracle error message is '|| SQLERRM,WSH_DEBUG_SV.C_UNEXPEC_ERR_LEVEL); WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:OTHERS');
5190 END IF;
5191 --
5192
5193 END Table_to_Record;
5194
5195 /* ---------------------------------------------------------------------
5196 Procedure: Lock_WDA_No_Compare
5197
5198 Parameters: Delivery_Detail Id DEFAULT NULL
5199 Delivery Id DEFAULT NULL
5200
5201 Description: This procedure is used for obtaining locks of delivery assignments
5202 using the delivery_detail_id or the delivery_id.
5203 This procedure does not compare the attributes. It just
5204 does a SELECT using FOR UPDATE NOWAIT
5205 Created: K: MDC
5206 ----------------------------------------------------------------------- */
5207
5208
5209 PROCEDURE Lock_WDA_No_Compare(
5210 p_delivery_detail_id IN NUMBER, -- default null in spec
5211 p_delivery_id IN NUMBER -- default null in spec
5212 )
5213 IS
5214
5215 CURSOR c_lock_wda_of_dlvy(p_dlvy_id NUMBER) IS
5216 SELECT delivery_detail_id
5217 FROM wsh_delivery_assignments
5218 WHERE delivery_id = p_dlvy_id
5219 FOR UPDATE NOWAIT;
5220
5221 CURSOR c_lock_wda_of_detail(p_detail_id NUMBER) IS
5222 SELECT delivery_detail_id
5223 FROM wsh_delivery_assignments
5224 WHERE delivery_detail_id = p_delivery_detail_id
5225 FOR UPDATE NOWAIT;
5226
5227 l_dummy_detail_id NUMBER;
5228 l_del_name VARCHAR2(30);
5229
5230 l_debug_on BOOLEAN;
5231 --
5232 l_module_name CONSTANT VARCHAR2(100) := 'wsh.plsql.' || G_PKG_NAME || '.' || 'LOCK_DETAIL_NO_COMPARE';
5233
5234 BEGIN
5235 --
5236 l_debug_on := WSH_DEBUG_INTERFACE.g_debug;
5237 --
5238 IF l_debug_on IS NULL
5239 THEN
5240 l_debug_on := WSH_DEBUG_SV.is_debug_enabled;
5241 END IF;
5242 --
5243 IF l_debug_on THEN
5244 WSH_DEBUG_SV.push(l_module_name);
5245 WSH_DEBUG_SV.log(l_module_name,'p_delivery_detail_id', p_delivery_detail_id);
5246 WSH_DEBUG_SV.log(l_module_name, 'p_delivery_id', p_delivery_id);
5247 END IF;
5248 --
5249
5250 IF p_delivery_Detail_id IS NOT NULL THEN
5251 open c_lock_wda_of_detail(p_delivery_detail_id);
5252 fetch c_lock_wda_of_detail into l_dummy_detail_id;
5253 close c_lock_wda_of_detail;
5254 ELSIF p_delivery_id IS NOT NULL THEN
5255 OPEN c_lock_wda_of_dlvy(p_delivery_id);
5256 FETCH c_lock_wda_of_dlvy INTO l_dummy_detail_id;
5257 CLOSE c_lock_wda_of_dlvy;
5258 END IF;
5259
5260 IF l_debug_on THEN
5261 WSH_DEBUG_SV.pop(l_module_name);
5262 END IF;
5263 EXCEPTION
5264 WHEN app_exception.application_exception or app_exception.record_lock_exception THEN
5265 IF p_delivery_id IS NOT NULL THEN
5266 l_del_name := wsh_new_deliveries_pvt.get_name(p_delivery_id);
5267 FND_MESSAGE.SET_NAME('WSH', 'WSH_DLVY_LINE_LPN_LOCK');
5268 FND_MESSAGE.SET_TOKEN('DEL_NAME', l_del_name);
5269 wsh_util_core.add_message(wsh_util_core.g_ret_sts_error, l_module_name);
5270 IF l_debug_on THEN
5271 wsh_debug_sv.log(l_module_name, 'Could not obtain lock on some lines or lpns of delivery', p_delivery_id);
5272 END IF;
5273 ELSE
5274 IF l_debug_on THEN
5275 wsh_debug_sv.log(l_module_name, 'Could not obtain lock on del detail', p_delivery_detail_id);
5276 END IF;
5277 END IF;
5278
5279 IF l_debug_on THEN
5280 WSH_DEBUG_SV.logmsg(l_module_name,'APP_EXCEPTION.APPLICATION_EXCEPTION exception has occured.',WSH_DEBUG_SV.C_EXCEP_LEVEL);
5281 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:APP_EXCEPTION.APPLICATION_EXCEPTION');
5282 END IF;
5283 --
5284 RAISE;
5285
5286 END LOCK_WDA_NO_COMPARE;
5287
5288 END WSH_DELIVERY_DETAILS_PKG;
5289