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