[Home] [Help]
PACKAGE BODY: APPS.WSH_USA_QUANTITY_PVT
Source
1 PACKAGE BODY WSH_USA_QUANTITY_PVT as
2 /* $Header: WSHUSAQB.pls 120.11 2008/01/08 19:57:47 mvudugul noship $ */
3
4
5 --G_PACKAGE_NAME CONSTANT VARCHAR2(50) := 'WSH_USA_QUANTITY_PVT';
6
7 -- Forward declaration for the procedures to be used in update_ordered_quantity
8
9 PROCEDURE log_exception(
10 p_ship_from_location_id IN NUMBER,
11 p_delivery_id IN NUMBER DEFAULT NULL,
12 p_delivery_detail_id IN NUMBER,
13 p_parent_delivery_detail_id IN NUMBER DEFAULT NULL,
14 p_delivery_assignment_id IN NUMBER,
15 p_inventory_item_id IN NUMBER,
16 p_reason IN VARCHAR2,
17 x_return_status OUT NOCOPY VARCHAR2);
18
19 PROCEDURE Handle_Overpick_cancelled(
20 p_source_line_id IN NUMBER,
21 p_source_code IN VARCHAR2,
22 p_context IN VARCHAR2,
23 x_return_status OUT NOCOPY VARCHAR2);
24
25 -- HW OPMCONV - Removed p_process_flag parameter
26 PROCEDURE cancel_quantity(
27 p_source_code IN VARCHAR2,
28 p_source_header_id IN NUMBER, -- New
29 p_source_line_id IN NUMBER, -- New
30 p_delivery_detail_id IN NUMBER,
31 p_parent_delivery_detail_id IN NUMBER DEFAULT NULL,
32 p_serial_number IN VARCHAR2,
33 p_transaction_temp_id IN NUMBER,
34 p_released_status IN VARCHAR2,
35 p_move_order_line_id IN NUMBER,
36 p_organization_id IN NUMBER,
37 p_inventory_item_id IN NUMBER ,
38 p_subinventory IN VARCHAR2 ,
39 p_revision IN VARCHAR2 ,
40 p_lot_number IN VARCHAR2 ,
41 p_locator_id IN NUMBER ,
42 p_ordered_quantity IN NUMBER,
43 p_requested_quantity IN NUMBER,
44 p_requested_quantity2 IN NUMBER,
45 p_picked_quantity IN NUMBER DEFAULT NULL,
46 p_picked_quantity2 IN NUMBER DEFAULT NULL,
47 p_shipped_quantity IN NUMBER,
48 p_shipped_quantity2 IN NUMBER,
49 p_changed_detail_quantity IN NUMBER,
50 p_changed_detail_quantity2 IN NUMBER,
51 p_ship_tolerance_above IN NUMBER,
52 p_serial_quantity IN NUMBER,
53 p_replenishment_status IN VARCHAR2 DEFAULT NULL, --bug# 6689448 (replenishment project)
54 x_return_status OUT NOCOPY VARCHAR2);
55
56 -- Forward declaration for the procedures end
57
58
59 PROCEDURE Update_Ordered_Quantity(
60 p_changed_attribute IN WSH_INTERFACE.ChangedAttributeRecType
61 , p_source_code IN VARCHAR2
62 , p_action_flag IN VARCHAR2
63 , p_wms_flag IN VARCHAR2 DEFAULT 'N'
64 , p_context IN VARCHAR2 DEFAULT NULL
65 -- determines context of quantity update:
66 -- 'OVERPICK' = overpick normalization (bug 2942655 / 2936559)
67 -- NULL = normal order line quantity update
68 , x_return_status OUT NOCOPY VARCHAR2
69 )
70 IS
71
72 -- R12, X-dock
73 -- If 'Released to Warehouse' lines have to be deleted from WSH, first line with NULL MOL should be
74 -- reduced/deleted and then either of details from Inventory or X-dock can be picked
75 -- Add nvl(move_order_line_id,0) asc to ORDER BY clause
76 CURSOR C_Old_Line is
77 SELECT wdd.delivery_detail_id,
78 wdd.serial_number,
79 wdd.transaction_temp_id,
80 wdd.source_line_id,
81 wdd.pickable_flag,
82 wdd.move_order_line_id,
83 wdd.ship_from_location_id,
84 wdd.organization_id,
85 wdd.inventory_item_id,
86 wdd.subinventory,
87 wdd.revision,
88 wdd.locator_id,
89 wdd.lot_number,
90 wdd.released_status,
91 wdd.requested_quantity,
92 wdd.picked_quantity,
93 wdd.cancelled_quantity,
94 wdd.shipped_quantity,
95 wdd.requested_quantity2,
96 wdd.picked_quantity2,
97 wdd.cancelled_quantity2,
98 wdd.shipped_quantity2,
99 wdd.ship_tolerance_above,
100 wda.parent_delivery_detail_id,
101 wda.delivery_assignment_id,
102 wnd.planned_flag,
103 wnd.delivery_id,
104 nvl(wnd.status_code,'NO') status_code,
105 0 serial_quantity,
106 --OTM R12
107 wdd.weight_uom_code,
108 wdd.requested_quantity_uom,
109 --
110 wnd.ignore_for_planning, -- OTM R12 : update requested quantity change
111 wnd.tms_interface_flag, -- OTM R12 : update requested quantity change
112 wdd.replenishment_status --bug# 6689448 (replenishment project)
113 FROM wsh_delivery_details wdd,
114 wsh_new_deliveries wnd,
115 wsh_delivery_assignments_v wda
116 WHERE wdd.delivery_detail_id = wda.delivery_detail_id
117 AND wda.delivery_id = wnd.delivery_id (+)
118 AND wdd.source_line_id = p_changed_attribute.source_line_id
119 AND wdd.source_code = p_source_code
120 AND wdd.delivery_detail_id = decode (p_changed_attribute.delivery_detail_id,
121 FND_API.G_MISS_NUM , wdd.delivery_detail_id ,
122 p_changed_attribute.delivery_detail_id)
123 AND wdd.container_flag = 'N'
124 AND wdd.released_status <> 'D' -- New
125 AND DECODE(p_context, 'OVERPICK',wdd.requested_quantity,1) > 0 -- bug 2941581. Skip the 0 requested quantities for overpick normalization.
126 ORDER BY decode(nvl(wnd.status_code,'NO'),'NO',1,'OP',2,10),
127 decode(wda.parent_delivery_detail_id,NULL,1,10),
128 decode(wnd.planned_flag,'N',1,'Y',2,'F',3,10),
129 decode(wdd.released_status,'N',1,'R',2,'X',3,'B',4,'S',5,'Y',6,10),
130 nvl(wdd.move_order_line_id,0) asc,
131 nvl(wdd.requested_quantity,0) asc, -- This will make sure that maximum number of details are
132 wdd.delivery_detail_id; -- accounted for
133
134 CURSOR C_Old_Line_Ser is
135 SELECT wdd.delivery_detail_id,
136 wdd.serial_number,
137 wdd.transaction_temp_id,
138 wdd.source_line_id,
139 wdd.pickable_flag,
140 wdd.move_order_line_id,
141 wdd.ship_from_location_id,
142 wdd.organization_id,
143 wdd.inventory_item_id,
144 wdd.subinventory,
145 wdd.revision,
146 wdd.locator_id,
147 wdd.lot_number,
148 wdd.released_status,
149 wdd.requested_quantity,
150 wdd.picked_quantity,
151 wdd.cancelled_quantity,
152 wdd.shipped_quantity,
153 wdd.requested_quantity2,
154 wdd.picked_quantity2,
155 wdd.cancelled_quantity2,
156 wdd.shipped_quantity2,
157 wdd.ship_tolerance_above,
158 wda.parent_delivery_detail_id,
159 wda.delivery_assignment_id,
160 wnd.planned_flag,
161 wnd.delivery_id,
162 nvl(wnd.status_code,'NO') status_code,
163 sum(to_number(msnt.serial_prefix)) serial_quantity,
164 --OTM R12
165 wdd.weight_uom_code,
166 wdd.requested_quantity_uom,
167 --
168 wnd.ignore_for_planning, -- OTM R12 : update requested quantity change
169 wnd.tms_interface_flag, -- OTM R12 : update requested quantity change
170 wdd.replenishment_status --bug# 6689448 (replenishment project)
171 FROM wsh_delivery_details wdd,
172 wsh_new_deliveries wnd,
173 wsh_delivery_assignments_v wda,
174 mtl_serial_numbers_temp msnt
175 WHERE wdd.delivery_detail_id = wda.delivery_detail_id
176 AND wda.delivery_id = wnd.delivery_id (+)
177 AND wdd.source_line_id = p_changed_attribute.source_line_id
178 AND wdd.source_code = p_source_code
179 AND wdd.delivery_detail_id = decode (p_changed_attribute.delivery_detail_id,
180 FND_API.G_MISS_NUM , wdd.delivery_detail_id ,
181 p_changed_attribute.delivery_detail_id)
182 AND wdd.container_flag = 'N'
183 AND wdd.released_status <> 'D'
184 AND wdd.transaction_temp_id = msnt.transaction_temp_id(+)
185 AND DECODE(p_context, 'OVERPICK',wdd.requested_quantity,1) > 0 -- bug 2941581. Skip the 0 requested quantities for overpick normalization.
186 GROUP BY wdd.delivery_detail_id,
187 wdd.serial_number,
188 wdd.transaction_temp_id,
189 wdd.source_line_id,
190 wdd.pickable_flag,
191 wdd.move_order_line_id,
192 wdd.ship_from_location_id,
193 wdd.organization_id,
194 wdd.inventory_item_id,
195 wdd.subinventory,
196 wdd.revision,
197 wdd.locator_id,
198 wdd.lot_number,
199 wdd.released_status,
200 wdd.requested_quantity,
201 wdd.picked_quantity,
202 wdd.cancelled_quantity,
203 wdd.shipped_quantity,
204 wdd.requested_quantity2,
205 wdd.picked_quantity2,
206 wdd.cancelled_quantity2,
207 wdd.shipped_quantity2,
208 wdd.ship_tolerance_above,
209 wda.parent_delivery_detail_id,
210 wda.delivery_assignment_id,
211 wnd.planned_flag,
212 wnd.delivery_id,
213 nvl(wnd.status_code,'NO'),
214 --OTM R12
215 wdd.weight_uom_code,
216 wdd.requested_quantity_uom,
217 --
218 wnd.ignore_for_planning, -- OTM R12 : update requested quantity change
219 wnd.tms_interface_flag, -- OTM R12 : update requested quantity change
220 wdd.replenishment_status -- bug# 6689448 (replenishment project)
221 ORDER BY decode(nvl(wnd.status_code,'NO'),'NO',1,'OP',2,10),
222 decode(wda.parent_delivery_detail_id,NULL,1,10),
223 decode(wnd.planned_flag,'N',1,'Y',2,'F',3,10),
224 decode(wdd.released_status,'N',1,'R',2,'X',3,'B',4,'S',5,'Y',6,10),
225 nvl(wdd.move_order_line_id,0) asc,
226 nvl(wdd.requested_quantity,0) - decode(sum(to_number(msnt.serial_prefix)),NULL,decode(wdd.serial_number,NULL,0,1),
227 sum(to_number(msnt.serial_prefix))) desc,
228 nvl(wdd.requested_quantity,0) asc, -- This will make sure that maximum number of details are
229 wdd.delivery_detail_id; -- accounted for
230
231 CURSOR C_sum_req_quantity is
232 SELECT sum(nvl(requested_quantity,0)),sum(nvl(requested_quantity2,0))
233 FROM wsh_delivery_details
234 WHERE source_line_id = p_changed_attribute.source_line_id
235 AND source_code = p_source_code
236 GROUP BY source_line_id;
237
238 CURSOR C_get_others is
239 SELECT organization_id,inventory_item_id,requested_quantity_uom,requested_quantity_uom2
240 FROM wsh_delivery_details
241 WHERE source_line_id = p_changed_attribute.source_line_id
242 AND source_code = p_source_code
243 AND rownum < 2;
244
245 CURSOR c_is_ser_control (c_item_id NUMBER, c_organization_id NUMBER) is
246 select serial_number_control_code
247 from mtl_system_items
248 where inventory_item_id = c_item_id
249 and organization_id = c_organization_id ;
250
251 CURSOR c_shipping_parameters(c_organization_id NUMBER) IS
252 SELECT freight_class_cat_set_id, commodity_code_cat_set_id, enforce_ship_set_and_smc --
253 FROM wsh_shipping_parameters
254 WHERE organization_id = c_organization_id;
255
256 l_ship_parameters c_shipping_parameters%ROWTYPE;
257
258 l_ship_set_id NUMBER;
259
260 CURSOR c_get_ship_set_name(c_set_id IN NUMBER) is
261 SELECT set_name
262 FROM oe_sets
263 WHERE set_id = c_set_id;
264
265 l_ship_set_name VARCHAR2(30);
266
267 CURSOR c_check_smc_model_change (c_top_model_line_id NUMBER,
268 c_p_source_header_id NUMBER) IS
269 SELECT top_model_line_id FROM
270 wsh_delivery_details WHERE
271 top_model_line_id = c_top_model_line_id AND
272 source_code = 'OE' AND
273 ship_model_complete_flag = 'Y' AND
274 source_header_id = c_p_source_header_id AND
275 released_status IN ('S', 'Y', 'C','I') AND
276 rownum =1;
277
278 --bug#6407943.
279 CURSOR C_item_details(c_organization_id NUMBER,c_item_id NUMBER) IS
280 SELECT primary_uom_code
281 from mtl_system_items
282 where inventory_item_id = c_item_id
283 and organization_id = c_organization_id ;
284
285 l_primary_uom VARCHAR2(3);
286 --bug#6407943.
287
288 l_top_model_line_id NUMBER;
289
290 old_delivery_detail_rec C_Old_line%ROWTYPE;
291
292 l_changed_line_quantity NUMBER := 0;
293 l_changed_line_quantity2 NUMBER := 0;
294 l_changed_detail_quantity NUMBER := 0;
295 l_changed_detail_quantity2 NUMBER := 0;
296 l_ready_release_change_qty NUMBER := 0;
297 l_ready_release_change_qty2 NUMBER := 0;
298 l_original_ordered_quantity NUMBER := 0;
299 l_new_ordered_quantity NUMBER := 0;
300 l_valid_update_quantity NUMBER := 0;
301 l_organization_id NUMBER := 0;
302 l_inventory_item_id NUMBER := 0;
303 l_src_requested_quantity_uom VARCHAR2(3);
304 l_requested_quantity_uom VARCHAR2(3);
305 l_requested_quantity_uom2 VARCHAR2(3);
306
307 l_exception_return_status VARCHAR2(30);
308 l_exception_msg_count NUMBER;
309 l_exception_msg_data VARCHAR2(4000) := NULL;
310 l_exception_location_id NUMBER;
311 l_exception_error_message VARCHAR2(2000) := NULL;
312 l_exception_assignment_id NUMBER;
313
314 l_delivery_detail_rec wsh_glbl_var_strct_grp.delivery_details_rec_type;
315 l_delivery_assignments_info WSH_DELIVERY_DETAILS_PKG.Delivery_Assignments_Rec_TYPE;
316 l_dummy_delivery_detail_id NUMBER;
317 l_dummy_rowid VARCHAR2(30);
318 l_dummy_id NUMBER;
319
320 l_return_status VARCHAR2(30);
321 l_rs_ignored VARCHAR2(30);
322 l_api_version_number NUMBER := 1.0;
323 l_msg_count NUMBER;
324 l_msg_data VARCHAR2(3000);
325 l_check_move_header NUMBER;
326 l_message VARCHAR2(2000);
327 l_ship_status VARCHAR2(100);
328 l_released_status VARCHAR2(100);
329 l_reason VARCHAR2(50);
330
331 l_changed_requested_quantity NUMBER;
332 l_new_requested_quantity NUMBER;
333 l_msg VARCHAR2(2000):=NULL;
334 l_counter NUMBER := 0;
335
336 -- HW OPMCONV - Removed
337 l_new_requested_quantity2 NUMBER(19,9);
338 l_commit VARCHAR2(1);
339 -- HW OPMCONV - Removed format of 19,9
340 l_transfer_qty NUMBER;
341 l_Changed_Quantity2 NUMBER(19,9);
342 -- HW OPMCONV - Removed format of 19,9
343 l_original_ordered_quantity2 NUMBER;
344 l_new_quantity2 NUMBER(19,9);
345 l_rsv_array INV_RESERVATION_GLOBAL.mtl_reservation_tbl_type;
346 l_size NUMBER;
347
348 l_net_weight NUMBER;
349 l_volume NUMBER;
350
351 l_ser_control NUMBER;
352
353 create_detail_failure EXCEPTION;
354 create_assignment_failure EXCEPTION;
355 reject_update EXCEPTION;
356 reject_delete EXCEPTION;
357
358 l_details_id WSH_UTIL_CORE.Id_Tab_Type;
359 delete_detail_failure EXCEPTION;
360 invalid_smc_model_change EXCEPTION;
361 invalid_ship_set EXCEPTION;
362
363 /* H projects: pricing integration csun */
364 i NUMBER := 0;
365 l_del_tab WSH_UTIL_CORE.Id_Tab_Type;
366 mark_reprice_error EXCEPTION;
367 -- deliveryMerge
368 Adjust_Planned_Flag_Err EXCEPTION;
369
370 l_detail_tab WSH_UTIL_CORE.id_tab_type; -- DBI Project
371 l_dbi_rs VARCHAR2(1); -- Return Status from DBI API
372
373 --OTM R12
374 l_split_delivery_detail_tab WSH_ENTITY_INFO_TAB;
375 l_split_delivery_detail_rec WSH_ENTITY_INFO_REC;
376 l_item_quantity_uom_tab WSH_UTIL_CORE.COLUMN_TAB_TYPE;
377 l_gc3_is_installed VARCHAR2(1);
378
379 -- OTM R12 : update requested quantity change
380 l_delivery_id_tab WSH_UTIL_CORE.ID_TAB_TYPE;
381 l_interface_flag_tab WSH_UTIL_CORE.COLUMN_TAB_TYPE;
382 l_call_update VARCHAR2(1);
383 -- End of OTM R12 : update requested quantity change
384 --
385 -- 5870774
386 l_oke_cancel_qty_allowed NUMBER;
387 l_src_cancel_qty_allowed NUMBER;
388
389 --
390 l_debug_on BOOLEAN;
391 --
392 l_test NUMBER;
393
394 l_module_name CONSTANT VARCHAR2(100) := 'wsh.plsql.' || G_PACKAGE_NAME || '.' || 'UPDATE_ORDERED_QUANTITY';
395 --
396 BEGIN
397
398 --
399 -- Debug Statements
400 --
401 --
402 l_debug_on := WSH_DEBUG_INTERFACE.g_debug;
403 --
404 IF l_debug_on IS NULL
405 THEN
406 l_debug_on := WSH_DEBUG_SV.is_debug_enabled;
407 END IF;
408 --
409 IF l_debug_on THEN
410 WSH_DEBUG_SV.push(l_module_name);
411 WSH_DEBUG_SV.log(l_module_name,'P_SOURCE_CODE',P_SOURCE_CODE);
412 WSH_DEBUG_SV.log(l_module_name,'P_ACTION_FLAG',P_ACTION_FLAG);
413 WSH_DEBUG_SV.log(l_module_name,'P_WMS_FLAG',P_WMS_FLAG);
414 WSH_DEBUG_SV.log(l_module_name,'P_CONTEXT',P_CONTEXT);
415 WSH_DEBUG_SV.log(l_module_name,'source_header_id',p_changed_attribute.source_header_id);
416 WSH_DEBUG_SV.log(l_module_name,'original_source_line_id',p_changed_attribute.original_source_line_id);
417 WSH_DEBUG_SV.log(l_module_name,'source_line_id',p_changed_attribute.source_line_id);
418 WSH_DEBUG_SV.log(l_module_name,'delivery_detail_id',p_changed_attribute.delivery_detail_id);
419 WSH_DEBUG_SV.log(l_module_name,'ordered_quantity',p_changed_attribute.ordered_quantity);
420 WSH_DEBUG_SV.log(l_module_name,'order_quantity_uom',p_changed_attribute.order_quantity_uom);
421 WSH_DEBUG_SV.log(l_module_name,'ordered_quantity2',p_changed_attribute.ordered_quantity2);
422 END IF;
423 --
424 --
425 -- Debug Statements
426 --
427 IF l_debug_on THEN
428 WSH_DEBUG_SV.logmsg(l_module_name, 'IN WSH_USA_QUANTITY_PVT.UPDATE_ORDERED_QUANTITY, ACTION = '|| P_ACTION_FLAG );
429 END IF;
430 --
431
432 x_return_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
433
434 --OTM R12 initialize
435 l_gc3_is_installed := WSH_UTIL_CORE.G_GC3_IS_INSTALLED;
436
437 IF (l_gc3_is_installed IS NULL) THEN
438 l_gc3_is_installed := WSH_UTIL_CORE.GC3_IS_INSTALLED;
439 END IF;
440
441 IF (l_gc3_is_installed = 'Y') THEN
442 l_split_delivery_detail_tab := WSH_ENTITY_INFO_TAB();
443 l_split_delivery_detail_tab.EXTEND;
444 END IF;
445 --
446
447 OPEN C_sum_req_quantity;
448 FETCH C_sum_req_quantity INTO l_original_ordered_quantity,l_original_ordered_quantity2;
449 CLOSE C_sum_req_quantity;
450
451 OPEN C_get_others;
452 FETCH C_get_others INTO l_organization_id,l_inventory_item_id,l_requested_quantity_uom,l_requested_quantity_uom2;
453 CLOSE C_get_others;
454 --bug#6407943: begin.
455 -- There is a possibility of having quantity change on delivery lines when there is a change in org
456 -- value on sales order line and item's primary uom is different in old and new orgs.
457
458 IF ( (p_changed_attribute.ship_from_org_id <> FND_API.G_MISS_NUM)
459 AND (p_changed_attribute.ship_from_org_id <> l_organization_id) )
460 and (p_changed_attribute.inventory_item_id = FND_API.G_MISS_NUM ) THEN
461 --{
462 IF l_debug_on THEN
463 WSH_DEBUG_SV.log(l_module_name,'inside primary UOM change checking.');
464 END IF;
465 OPEN C_item_details(p_changed_attribute.ship_from_org_id,l_inventory_item_id);
466 FETCH C_item_details INTO l_primary_uom;
467 CLOSE C_item_details;
468 IF l_debug_on THEN
469 WSH_DEBUG_SV.log(l_module_name,'l_primary_uom',l_primary_uom);
470 WSH_DEBUG_SV.log(l_module_name,'l_requested_quantity_uom',l_requested_quantity_uom);
471 END IF;
472 IF (l_primary_uom <> l_requested_quantity_uom) THEN
473 l_requested_quantity_uom := l_primary_uom; --overrite the requested qty uom.
474 END IF;
475 --}
476 END IF;
477 --bug#6407943: end
478
479 OPEN c_shipping_parameters(l_organization_id);
480 FETCH c_shipping_parameters INTO l_ship_parameters;
481 IF ( c_shipping_parameters%NOTFOUND ) THEN
482 IF l_debug_on THEN
483 WSH_DEBUG_SV.log(l_module_name,'Shipping Parameters notfound for warehuse:'||l_organization_id);
484 END IF;
485 WSH_INTERFACE.PrintMsg(txt=>'Shipping Parameters notfound for warehouse:'||l_organization_id);
486 END IF;
487 CLOSE c_shipping_parameters;
488
489 --
490 -- Debug Statements
491 --
492 --HW OPMCONV - Removed checking for process org and code forking
493
494 IF (NVL(p_changed_attribute.ordered_quantity, 0) <> 0) THEN
495 --
496 -- Debug Statements
497 --
498 IF l_debug_on THEN
499 WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_WV_UTILS.CONVERT_UOM',WSH_DEBUG_SV.C_PROC_LEVEL);
500 END IF;
501 --
502 l_new_ordered_quantity := WSH_WV_UTILS.Convert_Uom(
503 p_changed_attribute.order_quantity_uom,
504 l_requested_quantity_uom, -- Converting UOM using any detail
505 p_changed_attribute.ordered_quantity,
506 l_inventory_item_id);
507 ELSE
508 l_new_ordered_quantity := p_changed_attribute.ordered_quantity; -- In req quantity unit
509 -- 5870774 for OKE
510 if (p_source_code = 'OKE') then
511 -- Then Cancel the entire Cancellable Qty.
512 --
513 begin
514 select sum(wdd.requested_quantity)
515 into l_oke_cancel_qty_allowed
516 from wsh_delivery_details wdd
517 where
518 wdd.source_line_id = p_changed_attribute.source_line_id
519 and wdd.source_code = p_source_code
520 and not exists (select 'x' from
521 wsh_delivery_assignments wda,
522 wsh_new_deliveries wnd
523 where wda.delivery_detail_id = wdd.delivery_detail_id
524 and wda.delivery_id = wnd.delivery_id
525 and wnd.status_code in ('CL', 'IT', 'CO'));
526 --
527 exception
528 WHEN NO_DATA_FOUND THEN
529 --
530 l_oke_cancel_qty_allowed := 0;
531 -- Debug Statements
532 --
533 IF l_debug_on THEN
534 WSH_DEBUG_SV.logmsg(l_module_name,'NO_DATA_FOUND exception has occured.',WSH_DEBUG_SV.C_EXCEP_LEVEL);
535 END IF;
536 --
537 end;
538
539 --
540 l_new_ordered_quantity := l_original_ordered_quantity - l_oke_cancel_qty_allowed; -- In req quantity unit
541 --
542 IF l_debug_on THEN
543 WSH_DEBUG_SV.logmsg(l_module_name, 'New Ordered Qty: '|| l_new_ordered_quantity);
544 WSH_DEBUG_SV.logmsg(l_module_name, 'OKE Req Qty. Cancellable : '|| l_oke_cancel_qty_allowed);
545 WSH_DEBUG_SV.logmsg(l_module_name, 'Orig. Req Qty. Cancellable : '|| l_original_ordered_quantity);
546 END IF;
547 --
548 end if; -- OKE
549 --
550 END IF;
551
552 l_changed_line_quantity := l_new_ordered_quantity - l_original_ordered_quantity; -- In req quantity unit
553 l_changed_detail_quantity := ABS(l_changed_line_quantity);
554 --
555 -- Debug Statements
556 --
557 IF l_debug_on THEN
558 WSH_DEBUG_SV.logmsg(l_module_name, 'CHANGE LINE QTY = '|| L_CHANGED_LINE_QUANTITY );
559 END IF;
560 --
561 -- HW OPMCONV - Removed code forking
562
563 if ( p_changed_attribute.ordered_quantity2 = FND_API.G_MISS_NUM ) then
564 l_new_requested_quantity2 := null ;
565 else
566 l_new_requested_quantity2 := p_changed_attribute.ordered_quantity2 ;
567 end if ;
568
569
570 IF l_debug_on THEN
571 WSH_DEBUG_SV.logmsg(l_module_name, 'l_original_ordered_quantity2 = '||l_original_ordered_quantity2 );
572 WSH_DEBUG_SV.logmsg(l_module_name, 'p_changed_attribute.ordered_quantity2 = '||p_changed_attribute.ordered_quantity2 );
573 END IF;
574
575 l_changed_line_quantity2 := l_new_requested_quantity2 - l_original_ordered_quantity2; -- In req quantity unit
576
577 l_changed_detail_quantity2 := ABS(l_changed_line_quantity2);
578
579 IF l_debug_on THEN
580 WSH_DEBUG_SV.logmsg(l_module_name, 'NEW REQUESTED_QUANTITY2 = '||L_CHANGED_LINE_QUANTITY2 );
581 END IF;
582 --
583 --
584 -- 5870774: Update the new SRC requested qty. on the Wdds with Line_id = source_line_id
585 -- otherwise all the Non_Cancelled Lines will show src_requested qty = 0, after call to UpdateRecords in WSHUSAAB.pls
586 -- In WSHUSAAB.pls, we do not Re-update these lines Already Updated here for OKE and ord.qty=0 condition
587 -- Note, the wdd record being cancelled will get Updated with src req. qty = 0 in this Package, so it is out of context below
588 if (p_source_code = 'OKE' and p_changed_attribute.ordered_quantity = 0) THEN
589 if ( p_changed_attribute.order_quantity_uom <> l_requested_quantity_uom ) then
590 WSH_INTEGRATION.Get_Cancel_Qty_Allowed
591 ( p_source_code => p_source_code,
592 p_source_line_id => p_changed_attribute.source_line_id,
593 x_cancel_qty_allowed => l_src_cancel_qty_allowed,
594 x_return_status => l_return_status,
595 x_msg_count => l_msg_count,
596 x_msg_data => l_msg_data
597 );
598 IF l_debug_on THEN
599 WSH_DEBUG_SV.log(l_module_name,' l_return_status : ',l_return_status);
600 WSH_DEBUG_SV.log(l_module_name,' l_src_cancel_qty_allowed : ',l_src_cancel_qty_allowed);
601 END IF;
602 else
603 l_src_cancel_qty_allowed := l_oke_cancel_qty_allowed;
604 end if;
605 --
606 UPDATE WSH_DELIVERY_DETAILS
607 set src_requested_quantity = (src_requested_quantity - l_src_cancel_qty_allowed),
608 last_update_date = SYSDATE,
609 last_updated_by = FND_GLOBAL.USER_ID,
610 last_update_login = FND_GLOBAL.LOGIN_ID
611 where source_code = p_source_code
612 and source_line_id = p_changed_attribute.source_line_id;
613 --
614 IF l_debug_on THEN
615 WSH_DEBUG_SV.log(l_module_name,' After Update- l_src_cancel_qty_allowed : ',l_src_cancel_qty_allowed);
616 END IF;
617 end if; -- OKE
618
619 -- Check if Item is Serial Controlled
620 --Bug 6669284
621 --If the source is OKE and inventory item id is null skip serial control validation
622 IF (p_source_code <> 'OKE' OR (p_source_code = 'OKE'AND l_inventory_item_id IS NOT NULL)) THEN
623 OPEN c_is_ser_control(l_inventory_item_id, l_organization_id);
624 FETCH c_is_ser_control INTO l_ser_control;
625 IF c_is_ser_control%NOTFOUND THEN
626 IF l_debug_on THEN
627 WSH_DEBUG_SV.logmsg(l_module_name, 'No records found in mtl_system_items for Item Id '||L_INVENTORY_ITEM_ID||' AND
628 organization '||L_ORGANIZATION_ID );
629 END IF;
630 raise no_data_found ;
631 END IF;
632 CLOSE c_is_ser_control;
633 END IF;
634
635 SAVEPOINT startloop;
636
637 IF l_ser_control = 1 THEN
638 --
639 -- Debug Statements
640 --
641 IF l_debug_on THEN
642 WSH_DEBUG_SV.logmsg(l_module_name, 'OPEN C_OLD_LINE CURSOR');
643 END IF;
644 --
645 OPEN C_Old_line;
646 ELSE
647 --
648 -- Debug Statements
649 --
650 IF l_debug_on THEN
651 WSH_DEBUG_SV.logmsg(l_module_name, 'OPEN C_OLD_LINE_SER CURSOR');
652 END IF;
653 --
654 OPEN C_Old_line_ser;
655 END IF;
656 LOOP
657 IF l_ser_control = 1 THEN
658 FETCH C_Old_line INTO old_delivery_detail_rec;
659 EXIT WHEN C_Old_line%NOTFOUND;
660 ELSE
661 FETCH C_Old_line_ser INTO old_delivery_detail_rec;
662 EXIT WHEN C_Old_line_ser%NOTFOUND;
663 END IF;
664
665 --
666 -- Debug Statements
667 --
668 IF l_debug_on THEN
669 WSH_DEBUG_SV.log(l_module_name, 'IN C_OLD_LINE LOOP : DEL DET', OLD_DELIVERY_DETAIL_REC.DELIVERY_DETAIL_ID );
670 WSH_DEBUG_SV.log(l_module_name, 'L_READY_RELEASE_CHANGE_QTY', L_READY_RELEASE_CHANGE_QTY );
671 WSH_DEBUG_SV.log(l_module_name, 'L_CHANGED_DETAIL_QUANTITY', L_CHANGED_DETAIL_QUANTITY );
672 WSH_DEBUG_SV.log(l_module_name, 'l_changed_detail_quantity2', l_changed_detail_quantity2);
673 END IF;
674 --
675 IF (p_action_flag = 'D') THEN
676
677 /* csun: the delete details will release the reservation and delete
678 delivery detail line, freight cost, delivery assignment entry
679 */
680 IF old_delivery_detail_rec.delivery_id is not NULL THEN
681 i := i+1;
682 l_del_tab(i) := old_delivery_detail_rec.delivery_id;
683 END IF;
684
685 IF (old_delivery_detail_rec.status_code = 'NO') AND --sperera 940/945
686 (old_delivery_detail_rec.parent_delivery_detail_id IS NULL) THEN
687
688 l_details_id.delete;
689 l_details_id(1) := old_delivery_detail_rec.delivery_detail_id;
690
691 --bug# 6689448 (replenishment project) (Begin) : added the code to call WMS api for replenshment requested delivery
692 -- detail lines with zeqo qty so that WMS deletes the replenishment record.
693 IF (old_delivery_detail_rec.replenishment_status = 'R' and old_delivery_detail_rec.released_status in ('R','B')) THEN
694 --{
695 IF l_debug_on THEN
696 WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WMS_REPLENISHMENT_PUB.UPDATE_DELIVERY_DETAIL' ,WSH_DEBUG_SV.C_PROC_LEVEL);
697 END IF;
698 WMS_REPLENISHMENT_PUB.UPDATE_DELIVERY_DETAIL (
699 p_delivery_detail_id => old_delivery_detail_rec.delivery_detail_id,
700 p_primary_quantity => 0, --- WMS will delete the records from replenishment table.
701 x_return_status => x_return_status);
702 IF x_return_status <> WSH_UTIL_CORE.G_RET_STS_SUCCESS THEN
703 --{
704 IF l_debug_on THEN
705 WSH_DEBUG_SV.logmsg(l_module_name, 'UNEXPECTED ERROR FROM WMS_REPLENISHMENT_PUB.UPDATE_DELIVERY_DETAIL');
706 WSH_DEBUG_SV.pop(l_module_name);
707 END IF;
708 RETURN;
709 --}
710 END IF;
711 --}
712 END IF;
713 --bug# 6689448 (replenishment project):end
714
715 WSH_INTERFACE.Delete_Details(
716 p_details_id => l_details_id ,
717 x_return_status => l_return_status);
718
719 IF l_debug_on THEN
720 WSH_DEBUG_SV.log(l_module_name,'WSH_INTERFACE.DELETE_DETAILS l_return_status',l_return_status);
721 END IF;
722
723 IF l_return_status = WSH_UTIL_CORE.G_RET_STS_ERROR OR
724 l_return_status = WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR THEN
725 raise delete_detail_failure;
726 END IF;
727
728
729 ELSE
730 l_released_status := 'line is assigned to a delivery, or line is packed';
731 -- If there exists any detail which is packed or in a planned or closed delivery or
732 -- with released status other than N/R/X
733 -- rollback the changes and reject the request
734 ROLLBACK TO SAVEPOINT startloop;
735 --
736 -- Debug Statements
737 --
738 IF l_debug_on THEN
739 WSH_DEBUG_SV.logmsg(l_module_name, 'REJECT REQUEST AS SOME DELIVERY LINES ARE '||L_RELEASED_STATUS );
740 END IF;
741 --
742 FND_MESSAGE.Set_Name('WSH', 'WSH_REJECT_DELETE_QUANTITY');
743 RAISE reject_delete;
744 END IF;
745
746 ELSIF (p_action_flag = 'U') THEN
747
748
749 /* Start updating quantities according to the rules */
750
751 IF (l_changed_line_quantity > 0)
752 OR ( (l_changed_line_quantity2 > 0) and (l_changed_line_quantity >= 0) ) -- OPM 2187389 need this to fix if qty2 increases but does not work for qty2 decrease
753
754 THEN
755 IF l_debug_on THEN
756 WSH_DEBUG_SV.logmsg(l_module_name, 'L_CHANGED_LINE_QUANTITY > 0' );
757 WSH_DEBUG_SV.logmsg(l_module_name, ' OR L_CHANGED_LINE_QUANTITY2 > 0' );
758 END IF;
759
760 IF l_ship_parameters.enforce_ship_set_and_smc = 'Y' THEN
761 --
762 IF l_debug_on THEN
763 WSH_DEBUG_SV.logmsg(l_module_name, 'CHECKING SHIP SETS' );
764 END IF;
765 --
766 OPEN c_check_smc_model_change(p_changed_attribute.top_model_line_id,p_changed_attribute.source_header_id);
767 FETCH c_check_smc_model_change INTO l_top_model_line_id;
768 IF c_check_smc_model_change%FOUND THEN
769 IF l_debug_on THEN
770 WSH_DEBUG_SV.logmsg(l_module_name, 'INVALID CHANGES IN SMC MODEL' );
771 END IF;
772 -- raise exception.
773 CLOSE c_check_smc_model_change;
774 RAISE invalid_smc_model_change;
775 END IF;
776 CLOSE c_check_smc_model_change;
777 END IF;
778 /* H Projects: pricing integration csun, added the delivery id here */
779 IF old_delivery_detail_rec.delivery_id is not NULL THEN
780 i := i+1;
781 l_del_tab(i) := old_delivery_detail_rec.delivery_id;
782 END IF;
783
784 -- Case I : Delvry Stat = OP/Unassigned Packed = N Planned = N Released Status = N/R/X
785 --bug# 6689448 (replenishment project): Should not allow to add the qty on replenishment requested and replenishment completed details.
786 IF ( ((old_delivery_detail_rec.status_code IN ('OP', 'SA', 'NO'))) AND -- sperera 940/945
787 (old_delivery_detail_rec.parent_delivery_detail_id IS NULL) AND
788 (NVL(old_delivery_detail_rec.planned_flag,'N') = 'N') AND
789 ( NVL(old_delivery_detail_rec.released_status,'N') = 'N' or
790 (NVL(old_delivery_detail_rec.released_status,'N') = 'R' AND old_delivery_detail_rec.replenishment_status is NULL) or
791 NVL(old_delivery_detail_rec.released_status,'N') = 'X' )) THEN
792 --
793 --
794 IF l_debug_on THEN
795 WSH_DEBUG_SV.log(l_module_name, 'Updating wsh_delivery_details', old_delivery_detail_rec.delivery_detail_id);
796 WSH_DEBUG_SV.logmsg(l_module_name, 'Old value of requ_qty is ', old_delivery_detail_rec.requested_quantity );
797 WSH_DEBUG_SV.logmsg(l_module_name, 'Old value of requ_qty2 is ' ,old_delivery_detail_rec.requested_quantity2 );
798 END IF;
799
800 UPDATE wsh_delivery_details SET
801 requested_quantity = old_delivery_detail_rec.requested_quantity + l_changed_detail_quantity,
802 requested_quantity2 = old_delivery_detail_rec.requested_quantity2 + l_changed_line_quantity2, -- OPM B2187389
803 last_update_date = SYSDATE,
804 last_updated_by = FND_GLOBAL.USER_ID,
805 last_update_login = FND_GLOBAL.LOGIN_ID
806 WHERE source_line_id = old_delivery_detail_rec.source_line_id
807 AND delivery_detail_id = old_delivery_detail_rec.delivery_detail_id;
808
809 -- DBI Project
810 -- Update of wsh_delivery_details where requested_quantity/released_status
811 -- are changed, call DBI API after the update.
812 -- This API will also check for DBI Installed or not
813 IF l_debug_on THEN
814 WSH_DEBUG_SV.log(l_module_name,'Calling DBI API.Detail id-',old_delivery_detail_rec.delivery_detail_id);
815 END IF;
816 l_detail_tab(1) := old_delivery_detail_rec.delivery_detail_id;
817 WSH_INTEGRATION.DBI_Update_Detail_Log
818 (p_delivery_detail_id_tab => l_detail_tab,
819 p_dml_type => 'UPDATE',
820 x_return_status => l_dbi_rs);
821
822 IF l_debug_on THEN
823 WSH_DEBUG_SV.log(l_module_name,'Return Status after DBI Call-',l_dbi_rs);
824 END IF;
825 IF l_dbi_rs = WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR THEN
826 x_return_status := l_dbi_rs;
827 -- just pass this return status to caller API
828 IF l_debug_on THEN
829 WSH_DEBUG_SV.pop(l_module_name,'DBI API Returned Unexpected error '||x_return_status);
830 WSH_DEBUG_SV.pop(l_module_name);
831 END IF;
832 return;
833 END IF;
834 -- End of Code for DBI Project
835 --
836
837 -- J: W/V Changes
838 IF l_debug_on THEN
839 WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_WV_UTILS.Detail_Weight_Volume',WSH_DEBUG_SV.C_PROC_LEVEL);
840 END IF;
841 WSH_WV_UTILS.Detail_Weight_Volume(
842 p_delivery_detail_id => old_delivery_detail_rec.delivery_detail_id,
843 p_update_flag => 'Y',
844 p_post_process_flag => 'Y',
845 p_calc_wv_if_frozen => 'N',
846 x_net_weight => l_net_weight,
847 x_volume => l_volume,
848 x_return_status => l_rs_ignored);
849
850 IF l_rs_ignored = WSH_UTIL_CORE.G_RET_STS_ERROR THEN
851 x_return_status := l_rs_ignored;
852 IF l_debug_on THEN
853 WSH_DEBUG_SV.pop(l_module_name,'Detail_Weight_Volume returned '||l_return_status);
854 WSH_DEBUG_SV.pop(l_module_name);
855 END IF;
856 return;
857 END IF;
858 -- End of W/V Changes
859
860 --OTM R12, calling delivery detail splitter one record at a time here.
861 --in this API, changed detail quantity will always be positive since if you decrease the
862 --quantity, it will go to cancel_quantity API.
863 --no need to validate l_changed_detail_quantity for NULL, should not be NULL from
864 --above code.
865 IF (l_changed_detail_quantity > 0 AND l_gc3_is_installed = 'Y') THEN
866
867 IF l_debug_on THEN
868 WSH_DEBUG_SV.log(l_module_name,'delivery detail id',old_delivery_detail_rec.delivery_detail_id);
869 WSH_DEBUG_SV.log(l_module_name,'inventory item id',old_delivery_detail_rec.inventory_item_id);
870 WSH_DEBUG_SV.log(l_module_name,'net weight',l_net_weight);
871 WSH_DEBUG_SV.log(l_module_name,'organization id',old_delivery_detail_rec.organization_id);
872 WSH_DEBUG_SV.log(l_module_name,'weight uom code',old_delivery_detail_rec.weight_uom_code);
873 WSH_DEBUG_SV.log(l_module_name,'requested quantity',old_delivery_detail_rec.requested_quantity + l_changed_detail_quantity);
874 WSH_DEBUG_SV.log(l_module_name,'ship from location id',old_delivery_detail_rec.ship_from_location_id);
875 WSH_DEBUG_SV.log(l_module_name,'requested quantity uom',old_delivery_detail_rec.requested_quantity_uom);
876 END IF;
877
878 --prepare table of delivery detail information to call splitter
879 l_split_delivery_detail_tab(1) := WSH_ENTITY_INFO_REC(
880 old_delivery_detail_rec.delivery_detail_id,
881 NULL,
882 old_delivery_detail_rec.inventory_item_id,
883 l_net_weight,
884 0,
885 old_delivery_detail_rec.organization_id,
886 old_delivery_detail_rec.weight_uom_code,
887 old_delivery_detail_rec.requested_quantity + l_changed_detail_quantity,
888 old_delivery_detail_rec.ship_from_location_id,
889 NULL);
890 l_item_quantity_uom_tab(1) := old_delivery_detail_rec.requested_quantity_uom;
891
892 IF l_debug_on THEN
893 WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_DELIVERY_DETAILS_SPLITTER.tms_delivery_detail_split',WSH_DEBUG_SV.C_PROC_LEVEL);
894 END IF;
895
896 WSH_DELIVERY_DETAILS_SPLITTER.tms_delivery_detail_split(
897 p_detail_tab => l_split_delivery_detail_tab,
898 p_item_quantity_uom_tab => l_item_quantity_uom_tab,
899 x_return_status => l_return_status);
900
901 IF l_debug_on THEN
902 WSH_DEBUG_SV.logmsg(l_module_name, 'return status from WSH_DELIVERY_DETAILS_SPLITTER.tms_delivery_detail_split: ' || l_return_status);
903 END IF;
904
905 -- we will not fail based on l_return_status here because
906 -- we do not want to stop the flow
907 -- if the detail doesn't split, it will be caught later in
908 -- delivery splitting and will have exception on the detail
909 IF (l_return_status <> WSH_UTIL_CORE.G_RET_STS_SUCCESS) THEN
910 IF l_debug_on THEN
911 WSH_DEBUG_SV.logmsg(l_module_name, 'Delivery detail split failed for ' || old_delivery_detail_rec.delivery_detail_id );
912 END IF;
913 END IF;
914 --END splitter call
915
916 -- OTM R12 : update requested quantity change
917 l_call_update := 'Y';
918
919 IF (old_delivery_detail_rec.delivery_id IS NOT NULL AND
920 nvl(old_delivery_detail_rec.ignore_for_planning, 'N') = 'N') THEN
921 l_delivery_id_tab.DELETE;
922 l_interface_flag_tab.DELETE;
923 l_delivery_id_tab(1) := old_delivery_detail_rec.delivery_id;
924
925 -- calculate the interface flag to be updated
926 IF old_delivery_detail_rec.tms_interface_flag IN
927 (WSH_NEW_DELIVERIES_PVT.C_TMS_ANSWER_RECEIVED,
928 WSH_NEW_DELIVERIES_PVT.C_TMS_AWAITING_ANSWER,
929 WSH_NEW_DELIVERIES_PVT.C_TMS_CREATE_IN_PROCESS,
930 WSH_NEW_DELIVERIES_PVT.C_TMS_UPDATE_IN_PROCESS) THEN
931 l_interface_flag_tab(1) := WSH_NEW_DELIVERIES_PVT.C_TMS_UPDATE_REQUIRED;
932 IF l_debug_on THEN
933 WSH_DEBUG_SV.log(l_module_name, 'l_delivery_id_tab', l_delivery_id_tab(1));
934 WSH_DEBUG_SV.log(l_module_name, 'l_interface_flag_tab', l_interface_flag_tab(1));
935 END IF;
936 ELSE
937 l_call_update := 'N';
938 END IF;
939
940
941 IF (l_call_update = 'Y') THEN
942 WSH_NEW_DELIVERIES_PVT.UPDATE_TMS_INTERFACE_FLAG(
943 p_delivery_id_tab => l_delivery_id_tab,
944 p_tms_interface_flag_tab => l_interface_flag_tab,
945 x_return_status => l_return_status);
946
947 IF (l_return_status IN (WSH_UTIL_CORE.G_RET_STS_ERROR, WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR)) THEN
948 x_return_status := l_return_status;
949 IF l_debug_on THEN
950 WSH_DEBUG_SV.logmsg(l_module_name,'Error in WSH_NEW_DELIVERIES_PVT.UPDATE_TMS_INTERFACE_FLAG');
951 WSH_DEBUG_SV.pop(l_module_name);
952 END IF;
953 RETURN;
954 END IF;
955 END IF;
956 END IF;
957 -- End of OTM R12 : update requested quantity change
958 END IF;
959 --END OTM R12
960
961
962 -- Case II : Everything other than Case I
963
964 ELSE
965
966 IF l_debug_on THEN
967 WSH_DEBUG_SV.logmsg(l_module_name, 'Need to create a new delivery detail');
968 WSH_DEBUG_SV.log(l_module_name, 'l_changed_detail_quantity', l_changed_detail_quantity);
969 WSH_DEBUG_SV.log(l_module_name, 'l_changed_detail_quantity2', l_changed_detail_quantity2);
970 END IF;
971 IF (NVL(old_delivery_detail_rec.released_status,'N') <> 'D') THEN -- This is to avoid 'D' lines
972
973 l_delivery_detail_rec.requested_quantity := l_changed_detail_quantity;
974 --
975 -- Bug 2754311
976 --
977 -- HW OPMCONV - Removed code forking and fixed the condition
978 IF ( old_delivery_detail_rec.requested_quantity2 <> FND_API.G_MISS_NUM
979 OR old_delivery_detail_rec.requested_quantity2 IS NOT NULL ) THEN
980 l_delivery_detail_rec.requested_quantity2 := l_changed_detail_quantity2;
981 IF l_debug_on THEN
982 WSH_DEBUG_SV.log(l_module_name,'l_changed_detail_quantity2', l_changed_detail_quantity2);
983 WSH_DEBUG_SV.log(l_module_name,'New_DD.req_qty2', l_delivery_detail_rec.requested_quantity2);
984 END IF;
985 ELSE
986 l_delivery_Detail_rec.requested_quantity2 := FND_API.G_MISS_NUM;
987 IF l_debug_on THEN
988 WSH_DEBUG_SV.log(l_module_name,'IN ELSE AND l_changed_detail_quantity2', l_changed_detail_quantity2);
989 END IF;
990 END IF;
991 --
992 l_delivery_detail_rec.picked_quantity := FND_API.G_MISS_NUM;
993 l_delivery_detail_rec.picked_quantity2 := FND_API.G_MISS_NUM;
994 l_delivery_detail_rec.cancelled_quantity := FND_API.G_MISS_NUM;
995 l_delivery_detail_rec.cancelled_quantity2 := FND_API.G_MISS_NUM;
996 l_delivery_detail_rec.move_order_line_id := FND_API.G_MISS_NUM;
997 l_delivery_detail_rec.shipped_quantity := FND_API.G_MISS_NUM;
998 l_delivery_detail_rec.shipped_quantity2 := FND_API.G_MISS_NUM;
999 l_delivery_detail_rec.delivered_quantity := FND_API.G_MISS_NUM;
1000 l_delivery_detail_rec.delivered_quantity2 := FND_API.G_MISS_NUM;
1001 l_delivery_detail_rec.quality_control_quantity := FND_API.G_MISS_NUM;
1002 l_delivery_detail_rec.quality_control_quantity2 := FND_API.G_MISS_NUM;
1003 l_delivery_detail_rec.cycle_count_quantity := FND_API.G_MISS_NUM;
1004 l_delivery_detail_rec.cycle_count_quantity2 := FND_API.G_MISS_NUM;
1005 l_delivery_detail_rec.subinventory := FND_API.G_MISS_CHAR;
1006 l_delivery_detail_rec.revision := FND_API.G_MISS_CHAR;
1007 l_delivery_detail_rec.lot_number := FND_API.G_MISS_CHAR;
1008 l_delivery_detail_rec.locator_id := FND_API.G_MISS_NUM;
1009
1010 l_delivery_detail_rec.master_serial_number := FND_API.G_MISS_CHAR;
1011 l_delivery_detail_rec.serial_number := FND_API.G_MISS_CHAR;
1012 l_delivery_detail_rec.to_serial_number := FND_API.G_MISS_CHAR;
1013 l_delivery_detail_rec.transaction_temp_id := FND_API.G_MISS_NUM;
1014 l_delivery_detail_rec.transaction_id := FND_API.G_MISS_NUM; -- 2803570
1015 -- bug # 6719369 (replenishment project)
1016 l_delivery_detail_rec.replenishment_status := FND_API.G_MISS_CHAR;
1017
1018
1019 --
1020 -- Debug Statements
1021 --
1022 IF l_debug_on THEN
1023 WSH_DEBUG_SV.logmsg(l_module_name, 'UPDATED MOVE ORDER LINE TO NULL FOR NEW DD '||L_DUMMY_DELIVERY_DETAIL_ID );
1024 END IF;
1025 --
1026
1027 IF (NVL(old_delivery_detail_rec.released_status, 'N') <> 'N') THEN
1028 IF (old_delivery_detail_rec.pickable_flag = 'N') THEN
1029 l_delivery_detail_rec.released_status := 'X';
1030 ELSE
1031 l_delivery_detail_rec.released_status := 'R';
1032 END IF;
1033 END IF;
1034
1035 l_dummy_delivery_detail_id := null;
1036 --
1037 WSH_DELIVERY_DETAILS_PKG.create_new_detail_from_old(
1038 p_delivery_detail_rec => l_delivery_detail_rec,
1039 p_delivery_detail_id => old_delivery_detail_rec.delivery_detail_id,
1040 x_row_id => l_dummy_rowid,
1041 x_delivery_detail_id => l_dummy_delivery_detail_id,
1042 x_return_status => l_return_status);
1043
1044 IF l_debug_on THEN
1045 WSH_DEBUG_SV.log(l_module_name,'create_new_detail_from_old l_dummy_rowid,
1046 l_dummy_delivery_detail_id,l_return_status',l_dummy_rowid||','||
1047 l_dummy_delivery_detail_id||','||l_return_status);
1048 END IF;
1049
1050
1051
1052 IF (l_return_status <> WSH_UTIL_CORE.G_RET_STS_SUCCESS) THEN
1053 RAISE create_detail_failure;
1054 END IF;
1055 --
1056 -- Debug Statements
1057 --
1058 IF l_debug_on THEN
1059 WSH_DEBUG_SV.log(l_module_name,'CREATED DETAIL ID',L_DUMMY_DELIVERY_DETAIL_ID);
1060 WSH_DEBUG_SV.log(l_module_name,'New_DD.req_qty2', l_delivery_detail_rec.requested_quantity2);
1061 END IF;
1062 --
1063
1064
1065 -- for each delivery detail, there is at least one delivery
1066 -- assignment
1067
1068 l_delivery_assignments_info.delivery_detail_id := l_dummy_delivery_detail_id;
1069 l_delivery_assignments_info.delivery_id := NULL;
1070 l_delivery_assignments_info.parent_delivery_detail_id := NULL;
1071
1072 WSH_DELIVERY_DETAILS_PKG.Create_delivery_assignments(
1073 p_delivery_assignments_info => l_delivery_assignments_info,
1074 x_rowid => l_dummy_rowid,
1075 x_delivery_assignment_id => l_dummy_id,
1076 x_return_status => l_return_status);
1077
1078 IF l_debug_on THEN
1079 WSH_DEBUG_SV.log(l_module_name,'Create_delivery_assignments l_dummy_rowid,
1080 l_dummy_id,l_return_status',l_dummy_rowid||','||
1081 l_dummy_id||','||l_return_status);
1082 END IF;
1083
1084 IF (l_return_status <> WSH_UTIL_CORE.G_RET_STS_SUCCESS) THEN
1085 RAISE create_assignment_failure;
1086 END IF;
1087
1088 -- Bug 2349276 recalculate wt/vol if qty. changes - ignore return_status
1089 WSH_WV_UTILS.Detail_Weight_Volume
1090 (p_delivery_detail_id => l_dummy_delivery_detail_id,
1091 p_update_flag => 'Y',
1092 x_net_weight => l_net_weight,
1093 x_volume => l_volume,
1094 x_return_status => l_rs_ignored);
1095 IF l_debug_on THEN
1096 WSH_DEBUG_SV.log(l_module_name,'Detail_Weight_Volume l_net_weight,l_volume,
1097 l_rs_ignored',l_net_weight||','||l_volume||','||l_rs_ignored);
1098 END IF;
1099 -- J: W/V Changes
1100 IF l_rs_ignored = WSH_UTIL_CORE.G_RET_STS_ERROR THEN
1101 x_return_status := l_rs_ignored;
1102 IF l_debug_on THEN
1103 WSH_DEBUG_SV.pop(l_module_name,'Detail_Weight_Volume returned '||l_return_status);
1104 WSH_DEBUG_SV.pop(l_module_name);
1105 END IF;
1106 return;
1107 END IF;
1108 -- End of W/V Changes
1109
1110 --OTM R12, call to delivery detail splitter, process one record at a time here.
1111 IF (l_gc3_is_installed = 'Y') THEN
1112
1113 IF l_debug_on THEN
1114 WSH_DEBUG_SV.log(l_module_name,'delivery detail id',l_dummy_delivery_detail_id);
1115 WSH_DEBUG_SV.log(l_module_name,'inventory item id',old_delivery_detail_rec.inventory_item_id);
1116 WSH_DEBUG_SV.log(l_module_name,'net weight',l_net_weight);
1117 WSH_DEBUG_SV.log(l_module_name,'organization id',old_delivery_detail_rec.organization_id);
1118 WSH_DEBUG_SV.log(l_module_name,'weight uom code',old_delivery_detail_rec.weight_uom_code);
1119 WSH_DEBUG_SV.log(l_module_name,'requested quantity',l_changed_detail_quantity);
1120 WSH_DEBUG_SV.log(l_module_name,'ship from location id',old_delivery_detail_rec.ship_from_location_id);
1121 WSH_DEBUG_SV.log(l_module_name,'requested quantity uom',old_delivery_detail_rec.requested_quantity_uom);
1122 END IF;
1123
1124 --prepare table of delivery detail information to call splitter
1125 l_split_delivery_detail_tab(1) := WSH_ENTITY_INFO_REC(
1126 l_dummy_delivery_detail_id,
1127 NULL,
1128 old_delivery_detail_rec.inventory_item_id,
1129 l_net_weight,
1130 0,
1131 old_delivery_detail_rec.organization_id,
1132 old_delivery_detail_rec.weight_uom_code,
1133 l_changed_detail_quantity,
1134 old_delivery_detail_rec.ship_from_location_id,
1135 NULL);
1136 l_item_quantity_uom_tab(1) := old_delivery_detail_rec.requested_quantity_uom;
1137
1138 IF l_debug_on THEN
1139 WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_DELIVERY_DETAILS_SPLITTER.tms_delivery_detail_split',WSH_DEBUG_SV.C_PROC_LEVEL);
1140 END IF;
1141
1142 WSH_DELIVERY_DETAILS_SPLITTER.tms_delivery_detail_split(
1143 p_detail_tab => l_split_delivery_detail_tab,
1144 p_item_quantity_uom_tab => l_item_quantity_uom_tab,
1145 x_return_status => l_return_status);
1146
1147 IF l_debug_on THEN
1148 WSH_DEBUG_SV.logmsg(l_module_name, 'return status from WSH_DELIVERY_DETAILS_SPLITTER.tms_delivery_detail_split: ' || l_return_status);
1149 END IF;
1150
1151 -- we will not fail based on l_return_status here because
1152 -- we do not want to stop the flow
1153 -- if the detail doesn't split, it will be caught later in
1154 -- delivery splitting and will have exception on the detail
1155 IF (l_return_status <> WSH_UTIL_CORE.G_RET_STS_SUCCESS) THEN
1156 IF l_debug_on THEN
1157 WSH_DEBUG_SV.logmsg(l_module_name, 'Delivery detail split failed for ' || l_dummy_delivery_detail_id );
1158 END IF;
1159 END IF;
1160
1161 END IF;
1162 --END OTM R12
1163
1164
1165 END IF; -- Released status Not D
1166 END IF; -- Case I / Case II
1167
1168 /* LG for OPM need to update the inv*/
1169 --HW OPMCONV - Removed code forking
1170
1171 EXIT; -- Exit delivery details loop as for increase in order line quantity the first detail obtained for the
1172 -- source line will be sufficient to define the course of action
1173
1174 -- Decrease in order quantity
1175
1176 ELSIF (l_changed_line_quantity < 0) THEN
1177 --
1178 -- Debug Statements
1179 --
1180 IF l_debug_on THEN
1181 WSH_DEBUG_SV.logmsg(l_module_name, 'L_CHANGED_LINE_QUANTITY < 0 ' );
1182 END IF;
1183 --
1184
1185 /* H Projects: pricing integration csun, added the delivery id here */
1186 IF old_delivery_detail_rec.delivery_id is not NULL THEN
1187 i := i+1;
1188 l_del_tab(i) := old_delivery_detail_rec.delivery_id;
1189 END IF;
1190
1191
1192 IF ((old_delivery_detail_rec.status_code IN ('OP', 'NO', 'SA')) ) THEN -- Include unassigned lines here
1193 -- sperera 940/945
1194
1195
1196 -- Case I : Delvry Stat = OP Packed = N Planned = N
1197
1198 IF (old_delivery_detail_rec.parent_delivery_detail_id IS NULL) -- Packed = N
1199 AND ( NVL(old_delivery_detail_rec.planned_flag,'N') = 'N') -- Planned = N
1200 -- Released Status = N/R/B/X/S
1201
1202 AND (NVL(old_delivery_detail_rec.released_status,'N') = 'N' or
1203 NVL(old_delivery_detail_rec.released_status,'N') = 'R' or
1204 NVL(old_delivery_detail_rec.released_status,'N') = 'X' or
1205 NVL(old_delivery_detail_rec.released_status,'N') = 'B' or
1206 NVL(old_delivery_detail_rec.released_status,'N') = 'S') THEN
1207 -- Bug 2531155: quantity changes can cause data corruption in WMS
1208 -- Bug 2779304: allow full cancellation if released to warehouse in WMS
1209 -- X-dock lines with MOL null can be processed, but not if progressed
1210 -- to Released to warehouse.
1211 IF NVL(old_delivery_detail_rec.released_status,'N') = 'S'
1212 AND old_delivery_detail_rec.move_order_line_id IS NOT NULL
1213 AND p_wms_flag = 'Y'
1214 AND p_changed_attribute.ordered_quantity > 0 THEN
1215 ROLLBACK TO SAVEPOINT startloop;
1216 FND_MESSAGE.SET_NAME('WSH', 'WSH_WMS_UPDATE_NOT_ALLOWED');
1217 RAISE reject_update;
1218 END IF;
1219 cancel_quantity(
1220 p_inventory_item_id => NULL,
1221 p_subinventory => NULL,
1222 p_revision=> NULL,
1223 p_lot_number => NULL,
1224 p_locator_id => NULL,
1225 -- HW OPMCONV - Removed populating process_flag
1226
1227 p_source_code => p_source_code,
1228 p_source_header_id => p_changed_attribute.source_header_id,
1229 p_source_line_id => old_delivery_detail_rec.source_line_id,
1230 p_delivery_detail_id => old_delivery_detail_rec.delivery_detail_id,
1231 p_serial_number => old_delivery_detail_rec.serial_number,
1232 p_transaction_temp_id => old_delivery_detail_rec.transaction_temp_id,
1233 p_released_status => old_delivery_detail_rec.released_status,
1234 p_move_order_line_id => old_delivery_detail_rec.move_order_line_id,
1235 p_organization_id => old_delivery_detail_rec.organization_id,
1236 p_ordered_quantity => p_changed_attribute.ordered_quantity,
1237 p_requested_quantity => old_delivery_detail_rec.requested_quantity,
1238 p_requested_quantity2 => old_delivery_detail_rec.requested_quantity2,
1239 p_picked_quantity => old_delivery_detail_rec.picked_quantity,
1240 p_picked_quantity2 => old_delivery_detail_rec.picked_quantity2,
1241 p_shipped_quantity => old_delivery_detail_rec.shipped_quantity,
1242 p_shipped_quantity2 => old_delivery_detail_rec.shipped_quantity2,
1243 p_changed_detail_quantity => l_changed_detail_quantity,
1244 p_changed_detail_quantity2 => l_changed_line_quantity2, -- OPM B2187389,
1245 p_ship_tolerance_above => old_delivery_detail_rec.ship_tolerance_above,
1246 p_serial_quantity => old_delivery_detail_rec.serial_quantity,
1247 p_replenishment_status => old_delivery_detail_rec.replenishment_status, --bug# 6689448 (replenishment project)
1248 x_return_status => l_return_status);
1249
1250 IF l_debug_on THEN
1251 WSH_DEBUG_SV.log(l_module_name, 'l_return_status',l_return_status);
1252 END IF;
1253
1254 IF (l_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
1255 --
1256 -- Debug Statements
1257 --
1258 IF l_debug_on THEN
1259 WSH_DEBUG_SV.logmsg(l_module_name, 'CANCELLING QUANTITY FAILED FOR DETAIL '||OLD_DELIVERY_DETAIL_REC.DELIVERY_DETAIL_ID );
1260 END IF;
1261 --
1262 x_return_status := l_return_status;
1263 IF (l_return_status = FND_API.G_RET_STS_ERROR) THEN
1264 WSH_UTIL_CORE.add_message (x_return_status,l_module_name);
1265 ELSE
1266 wsh_util_core.default_handler('WSH_USA_QUANTITY_PVT.cancel_quantity',l_module_name);
1267 END IF;
1268 --
1269 -- Debug Statements
1270 --
1271 IF l_debug_on THEN
1272 WSH_DEBUG_SV.pop(l_module_name);
1273 END IF;
1274 --
1275 return;
1276 END IF;
1277
1278 IF old_delivery_detail_rec.released_status in ('N','R','B') THEN
1279 IF l_debug_on THEN
1280 WSH_DEBUG_SV.logmsg(l_module_name,'L_READY_RELEASE_CHANGE_QTY :'||L_READY_RELEASE_CHANGE_QTY );
1281 WSH_DEBUG_SV.logmsg(l_module_name, 'L_CHANGED_DETAIL_QUANTITY :'||L_CHANGED_DETAIL_QUANTITY );
1282 END IF;
1283
1284
1285 l_ready_release_change_qty := l_ready_release_change_qty + least(l_changed_detail_quantity, old_delivery_detail_rec.requested_quantity) ;
1286
1287 IF l_debug_on THEN
1288 WSH_DEBUG_SV.logmsg(l_module_name,'REQUESTED_QTY:'||OLD_DELIVERY_DETAIL_REC.REQUESTED_QUANTITY);
1289 WSH_DEBUG_SV.logmsg(l_module_name,'L_READY_RELEASE_CHANGE_QTY :'||L_READY_RELEASE_CHANGE_QTY );
1290 WSH_DEBUG_SV.logmsg(l_module_name,'BEFORE L_READY_RELEASE_CHANGE_QTY2 :'||L_READY_RELEASE_CHANGE_QTY2 );
1291 WSH_DEBUG_SV.logmsg(l_module_name,'BEFORE old_delivery_detail_rec.requested_quantity2 :'||old_delivery_detail_rec.requested_quantity2 );
1292 END IF;
1293
1294 -- HW OPMCONV - Calculate Qty2 similar to Qty1
1295 l_ready_release_change_qty2 := l_ready_release_change_qty2 + least(l_changed_line_quantity2, old_delivery_detail_rec.requested_quantity2) ;
1296 IF l_debug_on THEN
1297 WSH_DEBUG_SV.logmsg(l_module_name,'REQUESTED_QTY2:'||OLD_DELIVERY_DETAIL_REC.REQUESTED_QUANTITY2);
1298 WSH_DEBUG_SV.logmsg(l_module_name,'IN ONE L_READY_RELEASE_CHANGE_QTY2 :'||L_READY_RELEASE_CHANGE_QTY2 );
1299 WSH_DEBUG_SV.logmsg(l_module_name,'IN ONE l_changed_line_quantity2 :'||l_changed_line_quantity2 );
1300 END IF;
1301 END IF;
1302
1303 -- when we have consumed the quantity to cancel, exit the loop.
1304 IF (old_delivery_detail_rec.requested_quantity >= ABS(l_changed_detail_quantity)) THEN
1305 Handle_Overpick_cancelled(
1306 p_source_line_id => p_changed_attribute.source_line_id,
1307 p_source_code => p_source_code,
1308 p_context => p_context,
1309 x_return_status => l_return_status);
1310 IF l_debug_on THEN
1311 WSH_DEBUG_SV.log(l_module_name,'Handle_Overpick_cancelled l_return_status',l_return_status);
1312 END IF;
1313 IF (l_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
1314 --
1315 -- Debug Statements
1316 --
1317 IF l_debug_on THEN
1318 WSH_DEBUG_SV.logmsg(l_module_name, 'UPDATING OVERPICKED LINES API FAILED ' );
1319 END IF;
1320 --
1321 x_return_status := l_return_status;
1322 --
1323 -- Debug Statements
1324 --
1325 IF l_debug_on THEN
1326 WSH_DEBUG_SV.pop(l_module_name);
1327 END IF;
1328 --
1329 return;
1330 END IF;
1331 EXIT;
1332 END IF;
1333
1334
1335 l_changed_detail_quantity := l_changed_detail_quantity - old_delivery_detail_rec.requested_quantity;
1336 l_changed_detail_quantity2 := l_changed_detail_quantity2 - old_delivery_detail_rec.requested_quantity2;
1337
1338 IF l_debug_on THEN
1339 WSH_DEBUG_SV.logmsg(l_module_name, 'L_CHANGED_DETAIL_QUANTITY :'||L_CHANGED_DETAIL_QUANTITY );
1340 WSH_DEBUG_SV.logmsg(l_module_name, 'L_CHANGED_DETAIL_QUANTITY2 :'||L_CHANGED_DETAIL_QUANTITY2 );
1341 END IF;
1342 --
1343
1344 ELSE -- Everything else
1345 -- WMS check for released to warehouse or staged lines
1346 -- bug 2779304: if released to warehouse, disallow partial cancellation
1347 -- and allow full cancellation
1348 IF p_wms_flag = 'Y'
1349 AND ( NVL(old_delivery_detail_rec.released_status,'N') = 'Y'
1350 OR ( NVL(old_delivery_detail_rec.released_status,'N') = 'S'
1351 AND p_changed_attribute.ordered_quantity > 0)) THEN
1352 ROLLBACK TO SAVEPOINT startloop;
1353 FND_MESSAGE.SET_NAME('WSH', 'WSH_WMS_UPDATE_NOT_ALLOWED');
1354 RAISE reject_update;
1355 END IF;
1356
1357 IF (old_delivery_detail_rec.parent_delivery_detail_id IS NOT NULL) THEN -- Packed = Y
1358 l_reason := 'WSH_CANCELLED_PACKED';
1359 ELSIF ( NVL(old_delivery_detail_rec.planned_flag,'N') IN ('Y','F')) THEN -- Planned =Y
1360 l_reason := 'WSH_CANCELLED_PLANNED';
1361 ELSIF (NVL(old_delivery_detail_rec.released_status,'N') = 'Y') THEN -- Released Status = Y :
1362 l_reason := 'WSH_CANCELLED_RELEASED';
1363 END IF;
1364 --
1365 -- Debug Statements
1366 --
1367 IF l_debug_on THEN
1368 WSH_DEBUG_SV.logmsg(l_module_name, 'LOGGING EXCEPTION FOR DETAIL: '|| OLD_DELIVERY_DETAIL_REC.DELIVERY_DETAIL_ID );
1369 END IF;
1370 --
1371 log_exception(
1372 p_ship_from_location_id => old_delivery_detail_rec.ship_from_location_id,
1373 p_delivery_id => old_delivery_detail_rec.delivery_id,
1374 p_delivery_detail_id => old_delivery_detail_rec.delivery_detail_id,
1375 p_parent_delivery_detail_id => old_delivery_detail_rec.parent_delivery_detail_id,
1376 p_delivery_assignment_id => old_delivery_detail_rec.delivery_assignment_id,
1377 p_inventory_item_id => old_delivery_detail_rec.inventory_item_id,
1378 p_reason => l_reason,
1379 x_return_status => l_return_status);
1380 IF l_debug_on THEN
1381 WSH_DEBUG_SV.log(l_module_name,'log_exception l_return_status',l_return_status);
1382 END IF;
1383
1384 IF (l_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
1385 --
1386 -- Debug Statements
1387 --
1388 IF l_debug_on THEN
1389 WSH_DEBUG_SV.logmsg(l_module_name, 'LOGGING EXCEPTION FAILED WITH '||L_RETURN_STATUS );
1390 END IF;
1391 --
1392 x_return_status := l_return_status;
1393 IF (l_return_status IN (FND_API.G_RET_STS_ERROR,WSH_UTIL_CORE.G_RET_STS_WARNING)) THEN
1394 FND_MESSAGE.SET_NAME('WSH', 'Error in logging exception');
1395 WSH_UTIL_CORE.add_message (x_return_status,l_module_name);
1396 ELSE
1397 wsh_util_core.default_handler('WSH_USA_QUANTITY_PVT.log_exception',l_module_name);
1398 END IF;
1399 --
1400 -- Debug Statements
1401 --
1402 IF l_debug_on THEN
1403 WSH_DEBUG_SV.pop(l_module_name);
1404 END IF;
1405 --
1406 return;
1407 END IF;
1408 --
1409 -- Debug Statements
1410 --
1411 IF l_debug_on THEN
1412 WSH_DEBUG_SV.logmsg(l_module_name, 'CALLING CANCEL_QUANTITY FOR DETAIL '||OLD_DELIVERY_DETAIL_REC.DELIVERY_DETAIL_ID );
1413 END IF;
1414 --
1415
1416 -- HW OPMCONV - Removed l_process_flag parameter
1417 cancel_quantity(
1418 p_source_code => p_source_code,
1419 p_source_header_id => p_changed_attribute.source_header_id,
1420 p_source_line_id => old_delivery_detail_rec.source_line_id,
1421 p_delivery_detail_id => old_delivery_detail_rec.delivery_detail_id,
1422 p_parent_delivery_detail_id => old_delivery_detail_rec.parent_delivery_detail_id,
1423 p_serial_number => old_delivery_detail_rec.serial_number,
1424 p_transaction_temp_id => old_delivery_detail_rec.transaction_temp_id,
1425 p_released_status => old_delivery_detail_rec.released_status,
1426 p_move_order_line_id => old_delivery_detail_rec.move_order_line_id,
1427 p_organization_id => old_delivery_detail_rec.organization_id,
1428 p_inventory_item_id => old_delivery_detail_rec.inventory_item_id,
1429 p_subinventory => old_delivery_detail_rec.subinventory,
1430 p_revision => old_delivery_detail_rec.revision,
1431 p_lot_number => old_delivery_detail_rec.lot_number,
1432 p_locator_id => old_delivery_detail_rec.locator_id,
1433 p_ordered_quantity => p_changed_attribute.ordered_quantity,
1434 p_requested_quantity => old_delivery_detail_rec.requested_quantity,
1435 p_requested_quantity2 => old_delivery_detail_rec.requested_quantity2,
1436 p_picked_quantity => old_delivery_detail_rec.picked_quantity,
1437 p_picked_quantity2 => old_delivery_detail_rec.picked_quantity2,
1438 p_shipped_quantity => old_delivery_detail_rec.shipped_quantity,
1439 p_shipped_quantity2 => old_delivery_detail_rec.shipped_quantity2,
1440 p_changed_detail_quantity => l_changed_detail_quantity,
1441 p_changed_detail_quantity2 => l_changed_line_quantity2, -- OPM B2187389
1442 p_ship_tolerance_above => old_delivery_detail_rec.ship_tolerance_above,
1443 p_serial_quantity => old_delivery_detail_rec.serial_quantity,
1444 p_replenishment_status => old_delivery_detail_rec.replenishment_status, --bug# 6689448 (replenishment project)
1445 x_return_status => l_return_status);
1446 IF l_debug_on THEN
1447 WSH_DEBUG_SV.log(l_module_name,'cancel_quantity l_return_status',l_return_status);
1448 END IF;
1449 IF (l_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
1450 --
1451 -- Debug Statements
1452 --
1453 IF l_debug_on THEN
1454 WSH_DEBUG_SV.logmsg(l_module_name, 'CANCELLING QUANTITY FAILED FOR DETAIL '||OLD_DELIVERY_DETAIL_REC.DELIVERY_DETAIL_ID );
1455 END IF;
1456 --
1457 x_return_status := l_return_status;
1458 IF (l_return_status = FND_API.G_RET_STS_ERROR) THEN
1459 WSH_UTIL_CORE.add_message (x_return_status,l_module_name);
1460 ELSE
1461 wsh_util_core.default_handler('WSH_USA_QUANTITY_PVT.cancel_quantity',l_module_name);
1462 END IF;
1463 --
1464 -- Debug Statements
1465 --
1466 IF l_debug_on THEN
1467 WSH_DEBUG_SV.pop(l_module_name);
1468 END IF;
1469 --
1470 return;
1471 END IF;
1472
1473 IF old_delivery_detail_rec.released_status in ('N','R','B') THEN
1474 l_ready_release_change_qty := l_ready_release_change_qty + LEAST(l_changed_detail_quantity, old_delivery_detail_rec.requested_quantity) ;
1475 --HW OPMCONV Let's treat Qty2 similar to Qty1
1476 l_ready_release_change_qty2 := l_ready_release_change_qty2 + LEAST(l_changed_detail_quantity2,old_delivery_detail_rec.requested_quantity2) ;
1477 IF l_debug_on THEN
1478 WSH_DEBUG_SV.logmsg(l_module_name,'REQUESTED_QTY2:'||OLD_DELIVERY_DETAIL_REC.REQUESTED_QUANTITY2);
1479 WSH_DEBUG_SV.logmsg(l_module_name,'IN TWO L_READY_RELEASE_CHANGE_QTY2 :'||L_READY_RELEASE_CHANGE_QTY2 );
1480 END IF;
1481
1482 END IF;
1483
1484 -- when we have consumed the quantity to cancel, exit the loop.
1485 IF (old_delivery_detail_rec.requested_quantity >= ABS(l_changed_detail_quantity)) THEN
1486
1487 Handle_Overpick_cancelled(
1488 p_source_line_id => p_changed_attribute.source_line_id,
1489 p_source_code => p_source_code,
1490 p_context => p_context,
1491 x_return_status => l_return_status);
1492 IF l_debug_on THEN
1493 WSH_DEBUG_SV.log(l_module_name,'Handle_Overpick_cancelled l_return_status',l_return_status);
1494 END IF;
1495
1496 IF (l_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
1497 --
1498 -- Debug Statements
1499 --
1500 IF l_debug_on THEN
1501 WSH_DEBUG_SV.logmsg(l_module_name, 'UPDATING OVERPICKED LINES API FAILED ' );
1502 END IF;
1503 --
1504 x_return_status := l_return_status;
1505 --
1506 -- Debug Statements
1507 --
1508 IF l_debug_on THEN
1509 WSH_DEBUG_SV.pop(l_module_name);
1510 END IF;
1511 --
1512 return;
1513 END IF;
1514 EXIT;
1515 END IF;
1516
1517 l_changed_detail_quantity := l_changed_detail_quantity - old_delivery_detail_rec.requested_quantity;
1518 l_changed_detail_quantity2 := l_changed_detail_quantity2 - old_delivery_detail_rec.requested_quantity2;
1519 END IF;
1520 ELSIF (old_delivery_detail_rec.status_code = 'CO' or
1521 old_delivery_detail_rec.status_code = 'IT' or
1522 old_delivery_detail_rec.status_code = 'CL' or
1523 old_delivery_detail_rec.status_code = 'SC' or -- sperera 940/945
1524 old_delivery_detail_rec.status_code = 'SR') THEN
1525 l_ship_status := 'confirmed, in-transit or closed';
1526 l_valid_update_quantity := ABS(l_changed_line_quantity) - ABS(l_changed_detail_quantity);
1527 ROLLBACK TO SAVEPOINT startloop;
1528
1529 --
1530 -- Debug Statements
1531 --
1532 IF l_debug_on THEN
1533 WSH_DEBUG_SV.logmsg(l_module_name, 'REJECT REQUEST AS DELIVERY IS '||L_SHIP_STATUS );
1534 WSH_DEBUG_SV.logmsg(l_module_name, 'VALID UPDATE QUANTITY '||L_VALID_UPDATE_QUANTITY );
1535 END IF;
1536
1537 IF l_valid_update_quantity > 0 THEN
1538 -- Throw message saying the quantity that can be cancelled
1539 FND_MESSAGE.Set_Name('WSH', 'WSH_VALID_UPDATE_QUANTITY');
1540 FND_MESSAGE.Set_Token('UPDATE_QUANTITY',l_valid_update_quantity);
1541 ELSE
1542 FND_MESSAGE.Set_Name('WSH', 'WSH_REJECT_UPDATE_QUANTITY');
1543 END IF;
1544 RAISE reject_update;
1545 END IF; -- Delivery Status
1546 -- END IF; -- Changed Quantity moved lower down OPM B2187389
1547 ELSIF (l_changed_line_quantity2 < 0) and (l_changed_line_quantity = 0) THEN -- OPM B2187389 - decrease on qty2
1548 --
1549 -- Debug Statements
1550 --
1551 IF l_debug_on THEN
1552 WSH_DEBUG_SV.logmsg(l_module_name, 'OPM L_CHANGED_LINE_QUANTITY2 < 0 AND L_CHANGED_LINE_QUANTITY = 0 ' );
1553 END IF;
1554 --
1555
1556
1557 update wsh_delivery_details set
1558 requested_quantity2 = old_delivery_detail_rec.requested_quantity2 - ABS(l_changed_detail_quantity2),
1559 last_update_date = SYSDATE,
1560 last_updated_by = FND_GLOBAL.USER_ID,
1561 last_update_login = FND_GLOBAL.LOGIN_ID
1562 where delivery_detail_id = old_delivery_detail_rec.delivery_detail_id;
1563
1564
1565 END IF; -- Changed Quantity -- OPM B2187389
1566
1567 END IF; -- Action Flag = U/D
1568
1569 --
1570 -- Debug Statements
1571 --
1572 IF l_debug_on THEN
1573 WSH_DEBUG_SV.logmsg(l_module_name, 'DONE C_OLD_LINE LOOP FOR : DEL DET: '|| OLD_DELIVERY_DETAIL_REC.DELIVERY_DETAIL_ID );
1574 WSH_DEBUG_SV.logmsg(l_module_name, 'L_READY_RELEASE_CHANGE_QTY :'||L_READY_RELEASE_CHANGE_QTY );
1575 WSH_DEBUG_SV.logmsg(l_module_name, 'L_CHANGED_DETAIL_QUANTITY :'||L_CHANGED_DETAIL_QUANTITY );
1576 END IF;
1577
1578 END LOOP;
1579 IF l_ser_control = 1 THEN
1580 CLOSE C_Old_line;
1581 ELSE
1582 CLOSE C_Old_line_ser;
1583 END IF;
1584
1585
1586 IF l_changed_line_quantity <> 0 THEN
1587 -- delete or update all the move order lines
1588
1589 IF p_changed_attribute.ordered_quantity = 0 THEN
1590
1591 WSH_USA_INV_PVT.query_reservations (
1592 p_source_code => p_source_code,
1593 p_source_header_id => p_changed_attribute.source_header_id,
1594 p_source_line_id => p_changed_attribute.source_line_id,
1595 p_organization_id => old_delivery_detail_rec.organization_id,
1596 p_lock_records => fnd_api.g_true,
1597 p_cancel_order_mode => inv_reservation_global.g_cancel_order_yes,
1598 p_delivery_detail_id => null, -- X-dock
1599 x_mtl_reservation_tbl => l_rsv_array,
1600 x_mtl_reservation_tbl_count => l_size,
1601 x_return_status => l_return_status);
1602
1603 IF l_debug_on THEN
1604 WSH_DEBUG_SV.log(l_module_name,'query_reservations l_return_status',l_return_status);
1605 END IF;
1606
1607 FOR l_counter in 1..l_size
1608 LOOP
1609
1610 --
1611 -- Debug Statements
1612 --
1613 IF l_debug_on THEN
1614 WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_USA_INV_PVT.DELETE_RESERVATION',WSH_DEBUG_SV.C_PROC_LEVEL);
1615 END IF;
1616 --
1617 WSH_USA_INV_PVT.delete_reservation( -- For that source header and line id
1618 p_query_input => l_rsv_array(l_counter),
1619 x_return_status => l_return_status);
1620
1621 IF l_debug_on THEN
1622 WSH_DEBUG_SV.log(l_module_name,'delete_reservation l_return_status',l_return_status);
1623 END IF;
1624
1625 IF (l_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
1626 --
1627 -- Debug Statements
1628 --
1629 IF l_debug_on THEN
1630 WSH_DEBUG_SV.logmsg(l_module_name, 'DELETE RESERVATION FAILED FOR SOURCE LINE '||P_CHANGED_ATTRIBUTE.SOURCE_LINE_ID );
1631 END IF;
1632 --
1633 x_return_status := FND_API.G_RET_STS_ERROR;
1634 --IF l_return_status = FND_API.G_RET_STS_ERROR THEN
1635 WSH_UTIL_CORE.add_message (x_return_status,l_module_name);
1636 --ELSIF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
1637 --WSH_UTIL_CORE.default_handler('WSH_USA_INV_PVT.delete_reservation',l_module_name);
1638 --END IF;
1639 --
1640 -- Debug Statements
1641 --
1642 IF l_debug_on THEN
1643 WSH_DEBUG_SV.pop(l_module_name);
1644 END IF;
1645 --
1646 RETURN;
1647 END IF;
1648 END LOOP;
1649
1650 ELSE
1651
1652 -- Cancel reservation for non S,Y,X delivery lines
1653 -- No longer calls INV s process_move_order_line and process_move_order_header
1654 -- Need to call only if l_ready_release_change_qty > 0
1655 IF l_ready_release_change_qty > 0 THEN
1656 IF l_debug_on THEN
1657 WSH_DEBUG_SV.logmsg(l_module_name, 'IN CANCELLING NON STAGED RESERVATION FOR SOURCE '||P_CHANGED_ATTRIBUTE.SOURCE_LINE_ID );
1658 END IF;
1659 --
1660 WSH_USA_INV_PVT.cancel_nonstaged_reservation (
1661 p_source_code => p_source_code,
1662 p_source_header_id => p_changed_attribute.source_header_id,
1663 p_source_line_id => p_changed_attribute.source_line_id,
1664 p_delivery_detail_id => old_delivery_detail_rec.delivery_detail_id, --Bug3012297
1665 p_organization_id => old_delivery_detail_rec.organization_id,
1666 p_cancellation_quantity => l_ready_release_change_qty,
1667 p_cancellation_quantity2 => l_ready_release_change_qty2, -- OPM may need to change this
1668 x_return_status => l_return_status);
1669
1670 IF l_debug_on THEN
1671 WSH_DEBUG_SV.log(l_module_name,'cancel_nonstaged_reservation l_return_status',l_return_status);
1672 END IF;
1673
1674 IF (l_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
1675 --
1676 -- Debug Statements
1677 --
1678 IF l_debug_on THEN
1679 WSH_DEBUG_SV.logmsg(l_module_name, 'CANCELLING NON STAGED RESERVATION FAILED FOR SOURCE LINE '||P_CHANGED_ATTRIBUTE.SOURCE_LINE_ID );
1680 END IF;
1681 --
1682 x_return_status := FND_API.G_RET_STS_ERROR;
1683 --IF l_return_status = FND_API.G_RET_STS_ERROR THEN
1684 WSH_UTIL_CORE.add_message (x_return_status,l_module_name);
1685 --ELSIF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
1686 --WSH_UTIL_CORE.default_handler('WSH_USA_INV_PVT.cancel_nonstaged_reservation',l_module_name);
1687 --END IF;
1688 --
1689 -- Debug Statements
1690 --
1691 IF l_debug_on THEN
1692 WSH_DEBUG_SV.pop(l_module_name);
1693 END IF;
1694 --
1695 RETURN;
1696 END IF;
1697 END IF; -- l_ready_release_change_qty > 0
1698
1699 END IF; -- p_changed_attribute.ordered_quantity = 0
1700 END IF; -- l_changed_line_quantity <> 0
1701
1702 IF l_del_tab.count > 0 THEN
1703
1704 /* H integration: Pricing integration csun
1705 when plan a delivery
1706 */
1707 --
1708 -- Debug Statements
1709 --
1710 IF l_debug_on THEN
1711 WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_DELIVERY_LEGS_ACTIONS.MARK_REPRICE_REQUIRED',WSH_DEBUG_SV.C_PROC_LEVEL);
1712 END IF;
1713 --
1714 WSH_DELIVERY_LEGS_ACTIONS.Mark_Reprice_Required(
1715 p_entity_type => 'DELIVERY',
1716 p_entity_ids => l_del_tab,
1717 x_return_status => l_return_status);
1718 IF l_debug_on THEN
1719 WSH_DEBUG_SV.log(l_module_name,'Mark_Reprice_Required l_return_status',l_return_status);
1720 END IF;
1721
1722 IF l_return_status <> WSH_UTIL_CORE.G_RET_STS_SUCCESS THEN
1723 raise mark_reprice_error;
1724 END IF;
1725
1726 -- deliveryMerge
1727 IF l_debug_on THEN
1728 WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_NEW_DELIVERY_ACTIONS.Adjust_Planned_Flag',WSH_DEBUG_SV.C_PROC_LEVEL);
1729 END IF;
1730 WSH_NEW_DELIVERY_ACTIONS.Adjust_Planned_Flag(
1731 p_delivery_ids => l_del_tab,
1732 p_caller => 'WSH_DLMG',
1733 p_force_appending_limit => 'N',
1734 p_call_lcss => 'Y',
1735 p_event => NULL,
1736 x_return_status => l_return_status);
1737 IF l_debug_on THEN
1738 WSH_DEBUG_SV.log(l_module_name,'Adjust_Planned_Flag l_return_status',l_return_status);
1739 END IF;
1740
1741 IF l_return_status <> WSH_UTIL_CORE.G_RET_STS_SUCCESS THEN
1742 raise Adjust_Planned_Flag_Err;
1743 END IF;
1744
1745
1746 END IF;
1747
1748 IF l_debug_on THEN
1749 WSH_DEBUG_SV.pop(l_module_name);
1750 END IF;
1751 EXCEPTION
1752 WHEN reject_update THEN
1753 x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
1754 WSH_UTIL_CORE.add_message (x_return_status,l_module_name);
1755 IF l_debug_on THEN
1756 WSH_DEBUG_SV.logmsg(l_module_name,'REJECT_UPDATE exception has occured.',WSH_DEBUG_SV.C_EXCEP_LEVEL);
1757 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:REJECT_UPDATE');
1758 END IF;
1759 --
1760 WHEN reject_delete THEN
1761 x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
1762 WSH_UTIL_CORE.add_message (x_return_status,l_module_name);
1763 IF l_debug_on THEN
1764 WSH_DEBUG_SV.logmsg(l_module_name,'REJECT_DELETE exception has occured.',WSH_DEBUG_SV.C_EXCEP_LEVEL);
1765 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:REJECT_DELETE');
1766 END IF;
1767 WHEN create_assignment_failure THEN
1768 FND_MESSAGE.Set_Name('WSH', 'WSH_DET_CREATE_AS_FAILED');
1769 x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
1770 WSH_UTIL_CORE.add_message (x_return_status,l_module_name);
1771 IF l_debug_on THEN
1772 WSH_DEBUG_SV.logmsg(l_module_name,'CREATE_ASSIGNMENT_FAILURE exception has occured.',WSH_DEBUG_SV.C_EXCEP_LEVEL);
1773 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:CREATE_ASSIGNMENT_FAILURE');
1774 END IF;
1775 --
1776 WHEN create_detail_failure THEN
1777 FND_MESSAGE.Set_Name('WSH', 'WSH_DET_CREATE_DET_FAILED');
1778 x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
1779 WSH_UTIL_CORE.add_message (x_return_status,l_module_name);
1780
1781 IF l_debug_on THEN
1782 WSH_DEBUG_SV.logmsg(l_module_name,'CREATE_DETAIL_FAILURE exception has occured.',WSH_DEBUG_SV.C_EXCEP_LEVEL);
1783 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:CREATE_DETAIL_FAILURE');
1784 END IF;
1785 WHEN delete_detail_failure THEN
1786 FND_MESSAGE.Set_Name('WSH', 'WSH_DET_DELETE_DET_FAILED');
1787 x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
1788 WSH_UTIL_CORE.add_message (x_return_status,l_module_name);
1789
1790 IF l_debug_on THEN
1791 WSH_DEBUG_SV.logmsg(l_module_name,'DELETE_DETAIL_FAILURE exception has occured.',WSH_DEBUG_SV.C_EXCEP_LEVEL);
1792 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:DELETE_DETAIL_FAILURE');
1793 END IF;
1794 WHEN mark_reprice_error THEN
1795 FND_MESSAGE.Set_Name('WSH', 'WSH_REPRICE_REQUIRED_ERR');
1796 x_return_status := l_return_status;
1797 WSH_UTIL_CORE.add_message (x_return_status,l_module_name);
1798
1799 IF l_debug_on THEN
1800 WSH_DEBUG_SV.logmsg(l_module_name,'MARK_REPRICE_ERROR exception has occured.',WSH_DEBUG_SV.C_EXCEP_LEVEL);
1801 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:MARK_REPRICE_ERROR');
1802 END IF;
1803 --
1804 WHEN invalid_ship_set THEN
1805 IF l_debug_on THEN
1806 WSH_DEBUG_SV.logmsg(l_module_name, 'EXCEPTION: INVALID SHIP SET' );
1807 END IF;
1808 fnd_message.set_name('WSH', 'WSH_INVALID_SET_CHANGE');
1809 fnd_message.set_token('SHIP_SET',l_ship_set_name);
1810 fnd_message.set_token('LINE_NUMBER',p_changed_attribute.line_number);
1811 x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
1812 WSH_UTIL_CORE.add_message (x_return_status,l_module_name);
1813 WHEN invalid_smc_model_change THEN
1814 fnd_message.set_name('WSH', 'WSH_INVALID_SMC_CHANGE');
1815 fnd_message.set_token('LINE_NUMBER',l_top_model_line_id);
1816 x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
1817 WSH_UTIL_CORE.add_message (x_return_status,l_module_name);
1818 IF l_debug_on THEN
1819 WSH_DEBUG_SV.logmsg(l_module_name,'DELETE_DETAIL_FAILURE exception has occured.',WSH_DEBUG_SV.C_EXCEP_LEVEL);
1820 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:DELETE_DETAIL_FAILURE');
1821 END IF;
1822
1823
1824
1825 WHEN Adjust_Planned_Flag_Err THEN
1826 FND_MESSAGE.SET_NAME('WSH', 'WSH_ADJUST_PLANNED_FLAG_ERR');
1827 WSH_UTIL_CORE.add_message(l_return_status,l_module_name);
1828 x_return_status := l_return_status;
1829
1830 IF l_debug_on THEN
1831 WSH_DEBUG_SV.logmsg(l_module_name,'Adjust_Planned_Flag_Err exception has occured.',WSH_DEBUG_SV.C_EXCEP_LEVEL);
1832 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:Adjust_Planned_Flag_Err');
1833 END IF;
1834
1835
1836 WHEN others THEN
1837
1838 IF (c_old_line%ISOPEN) THEN
1839 CLOSE c_old_line;
1840 END IF;
1841
1842 IF (c_old_line_ser%ISOPEN) THEN
1843 CLOSE c_old_line_ser;
1844 END IF;
1845
1846 x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR;
1847 wsh_util_core.default_handler('WSH_USA_QUANTITY_PVT.Update_Ordered_Quantity',l_module_name);
1848 IF l_debug_on THEN
1849 WSH_DEBUG_SV.logmsg(l_module_name,'Unexpected error has occured. Oracle error message is '|| SQLERRM,WSH_DEBUG_SV.C_UNEXPEC_ERR_LEVEL);
1850 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:OTHERS');
1851 END IF;
1852 --
1853 END Update_Ordered_Quantity;
1854
1855
1856 PROCEDURE Handle_Overpick_cancelled(
1857 p_source_line_id IN NUMBER,
1858 p_source_code IN VARCHAR2,
1859 p_context IN VARCHAR2,
1860 x_return_status OUT NOCOPY VARCHAR2)
1861 IS
1862 CURSOR C_overpick is
1863 SELECT wdd.delivery_detail_id
1864 ,wdd.released_status
1865 ,wdd.move_order_line_id
1866 ,wdd.serial_number,transaction_temp_id
1867 ,wdd.organization_id
1868 ,wda.delivery_id
1869 ,wdd.inventory_item_id
1870 ,wdd.subinventory
1871 ,wdd.revision
1872 ,wdd.lot_number
1873 ,wdd.locator_id
1874 ,wdd.source_header_id
1875 ,wdd.picked_quantity
1876 ,wdd.picked_quantity2
1877 FROM wsh_delivery_details wdd,
1878 wsh_delivery_assignments_v wda
1879 WHERE wdd.source_line_id = p_source_line_id
1880 AND wdd.source_code = p_source_code
1881 AND wdd.released_status IN ('S','Y')
1882 AND wdd.requested_quantity = 0
1883 AND wdd.picked_quantity > 0
1884 AND wda.delivery_detail_id = wdd.delivery_detail_id;
1885
1886 l_released_status VARCHAR2(1);
1887 l_move_order_line_id NUMBER;
1888 l_delivery_detail_id NUMBER;
1889 l_serial_number VARCHAR2(30);
1890 l_txn_temp_id NUMBER;
1891 l_organization_id NUMBER;
1892 l_delivery_id NUMBER;
1893
1894 l_source_header_id WSH_DELIVERY_DETAILS.SOURCE_HEADER_ID%TYPE;
1895 l_inventory_item_id WSH_DELIVERY_DETAILS.INVENTORY_ITEM_ID%TYPE;
1896 l_subinventory WSH_DELIVERY_DETAILS.SUBINVENTORY%TYPE;
1897 l_revision WSH_DELIVERY_DETAILS.REVISION%TYPE;
1898 l_lot_number WSH_DELIVERY_DETAILS.LOT_NUMBER%TYPE;
1899 l_locator_id WSH_DELIVERY_DETAILS.LOCATOR_ID%TYPE;
1900 l_picked_quantity WSH_DELIVERY_DETAILS.PICKED_QUANTITY%TYPE;
1901 l_picked_quantity2 WSH_DELIVERY_DETAILS.PICKED_QUANTITY2%TYPE;
1902 l_delivery_detail_split_rec WSH_USA_INV_PVT.DeliveryDetailInvRecType;
1903 l_cancel_quantity NUMBER;
1904 l_cancel_quantity2 NUMBER;
1905
1906 l_msg_count NUMBER;
1907 l_msg_data VARCHAR2(240);
1908 l_error_text VARCHAR2(6000);
1909
1910 l_num_warn NUMBER := 0;
1911
1912 l_detail_tab WSH_UTIL_CORE.id_tab_type; -- DBI Project
1913 l_dbi_rs VARCHAR2(1); -- Return Status from DBI API
1914
1915 --
1916 l_debug_on BOOLEAN;
1917 --
1918 l_module_name CONSTANT VARCHAR2(100) := 'wsh.plsql.' || G_PACKAGE_NAME || '.' || 'HANDLE_OVERPICK_CANCELLED';
1919 --
1920 BEGIN
1921 --
1922 -- Debug Statements
1923 --
1924 --
1925 l_debug_on := WSH_DEBUG_INTERFACE.g_debug;
1926 --
1927 IF l_debug_on IS NULL
1928 THEN
1929 l_debug_on := WSH_DEBUG_SV.is_debug_enabled;
1930 END IF;
1931 --
1932 IF l_debug_on THEN
1933 WSH_DEBUG_SV.push(l_module_name);
1934 --
1935 WSH_DEBUG_SV.log(l_module_name,'P_SOURCE_LINE_ID',P_SOURCE_LINE_ID);
1936 WSH_DEBUG_SV.log(l_module_name,'P_SOURCE_CODE',P_SOURCE_CODE);
1937 WSH_DEBUG_SV.log(l_module_name,'P_CONTEXT',P_CONTEXT);
1938 END IF;
1939 --
1940 x_return_status := FND_API.G_RET_STS_SUCCESS;
1941
1942 -- bug 2942655 / 2936559
1943 -- overpick normalization should skip the lines pending overpick
1944 -- because their requested quantities (0) are not taken.
1945 -- This allows the picker to fully stage the quantities entered.
1946 IF (p_context = 'OVERPICK') THEN
1947 IF l_debug_on THEN
1948 WSH_DEBUG_SV.log(l_module_name,'Returning because of overpick normalization');
1949 WSH_DEBUG_SV.pop(l_module_name);
1950 END IF;
1951 RETURN;
1952 END IF;
1953
1954
1955 OPEN C_overpick;
1956 LOOP
1957 FETCH C_overpick INTO l_delivery_detail_id,l_released_status,
1958 l_move_order_line_id,l_serial_number,
1959 l_txn_temp_id,l_organization_id, l_delivery_id,
1960 l_inventory_item_id,
1961 l_subinventory,
1962 l_revision,
1963 l_lot_number,
1964 l_locator_id,
1965 l_source_header_id,
1966 l_picked_quantity,
1967 l_picked_quantity2;
1968 EXIT WHEN C_overpick%NOTFOUND;
1969
1970 -- Bug 2896572
1971 IF l_debug_on THEN
1972 WSH_DEBUG_SV.log(l_module_name,'Dd id-'||l_delivery_detail_id);
1973 WSH_DEBUG_SV.log(l_module_name,'Rel Status-'||l_released_status);
1974 WSH_DEBUG_SV.log(l_module_name,'Serial Number-'||l_serial_number);
1975 END IF;
1976
1977 IF l_released_status = 'Y' THEN
1978 l_delivery_detail_split_rec.delivery_detail_id := l_delivery_detail_id;
1979 l_delivery_detail_split_rec.released_status := l_released_status;
1980 l_delivery_detail_split_rec.move_order_line_id := l_move_order_line_id;
1981 l_delivery_detail_split_rec.organization_id := l_organization_id;
1982 l_delivery_detail_split_rec.inventory_item_id := l_inventory_item_id;
1983 l_delivery_detail_split_rec.subinventory := l_subinventory;
1984 l_delivery_detail_split_rec.revision := l_revision;
1985 l_delivery_detail_split_rec.lot_number := l_lot_number;
1986 l_delivery_detail_split_rec.locator_id := l_locator_id;
1987 -- Cancel Quantity is not used in this API,so passing as non-zero value
1988 l_cancel_quantity := l_picked_quantity;
1989 l_cancel_quantity2 := l_picked_quantity2;
1990
1991 WSH_USA_INV_PVT.cancel_staged_reservation(
1992 p_source_code => p_source_code,
1993 p_source_header_id => l_source_header_id,
1994 p_source_line_id => p_source_line_id,
1995 p_delivery_detail_split_rec => l_delivery_detail_split_rec,
1996 p_cancellation_quantity => l_cancel_quantity,
1997 p_cancellation_quantity2 => l_cancel_quantity2,
1998 x_return_status => x_return_status);
1999
2000 IF l_debug_on THEN
2001 WSH_DEBUG_SV.log(l_module_name,'WSH_USA_INV_PVT.CANCEL_STAGED_RESERVATION',x_return_status);
2002 END IF;
2003 IF x_return_status IN (fnd_api.g_ret_sts_error,
2004 fnd_api.g_ret_sts_unexp_error) THEN
2005 IF l_debug_on THEN
2006 WSH_DEBUG_SV.logmsg(l_module_name, 'ERROR IN WSH_USA_INV_PVT.CANCEL_STAGED_RESERVATION ') ;
2007 END IF;
2008
2009 x_return_status := FND_API.G_RET_STS_ERROR;
2010 WSH_UTIL_CORE.add_message (x_return_status,l_module_name);
2011
2012 IF l_debug_on THEN
2013 WSH_DEBUG_SV.pop(l_module_name);
2014 END IF;
2015 return;
2016
2017 END IF;
2018 -- End of Bug 2896572
2019
2020 ELSIF l_released_status = 'S' THEN
2021 --
2022 -- Debug Statements
2023 --
2024 -- HW OPMCONV - Removed code forking
2025
2026 IF l_debug_on THEN
2027 WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit INV_MO_CANCEL_PVT.CANCEL_MOVE_ORDER_LINE',WSH_DEBUG_SV.C_PROC_LEVEL);
2028 END IF;
2029 --
2030 INV_MO_Cancel_PVT.Cancel_Move_Order_Line(
2031 x_return_status => x_return_status
2032 ,x_msg_count => l_msg_count
2033 ,x_msg_data => l_msg_data
2034 ,p_line_id => l_move_order_line_id
2035 ,p_delete_reservations => 'Y'
2036 ,p_txn_source_line_id => p_source_line_id
2037 ,p_delivery_detail_id => l_delivery_detail_id -- X-dock
2038 );
2039 IF l_debug_on THEN
2040 WSH_DEBUG_SV.log(l_module_name,'Cancel_Move_Order_Line x_return_status',x_return_status);
2041 END IF;
2042 --HW OPMCONV - Removed branching
2043 IF x_return_status = fnd_api.g_ret_sts_success THEN
2044 --
2045 -- Debug Statements
2046 --
2047 IF l_debug_on THEN
2048 WSH_DEBUG_SV.logmsg(l_module_name, 'CANCELLED MOLINE '||L_MOVE_ORDER_LINE_ID );
2049 END IF;
2050 --
2051 ELSE
2052 --
2053 -- Debug Statements
2054 --
2055 IF l_debug_on THEN
2056 WSH_DEBUG_SV.logmsg(l_module_name, 'ERROR IN INV_MO_CANCEL_PVT.CANCEL_MOVE_ORDER_LINE ' );
2057 END IF;
2058 --
2059 FND_MESSAGE.Set_Name('WSH', 'WSH_CANCEL_MO_LINE');
2060 IF l_msg_count = 1 THEN
2061 --
2062 -- Debug Statements
2063 --
2064 IF l_debug_on THEN
2065 WSH_DEBUG_SV.logmsg(l_module_name, 'ERROR: '|| L_MSG_DATA );
2066 END IF;
2067 --
2068 FND_MESSAGE.Set_Token('ERROR_TEXT',l_msg_data);
2069 ELSE
2070 FOR l_index IN 1..l_msg_count LOOP
2071 l_msg_data := fnd_msg_pub.get;
2072 --
2073 -- Debug Statements
2074 --
2075 IF l_debug_on THEN
2076 WSH_DEBUG_SV.logmsg(l_module_name, 'ERROR: '|| L_MSG_DATA );
2077 END IF;
2078 --
2079 l_error_text := l_error_text || l_msg_data;
2080 END LOOP;
2081 FND_MESSAGE.Set_Token('ERROR_TEXT',l_error_text);
2082 l_error_text := '';
2083 END IF;
2084 x_return_status := FND_API.G_RET_STS_ERROR;
2085 WSH_UTIL_CORE.add_message (x_return_status,l_module_name);
2086 --
2087 -- Debug Statements
2088 --
2089 IF l_debug_on THEN
2090 WSH_DEBUG_SV.pop(l_module_name);
2091 END IF;
2092 --
2093 return;
2094 END IF;
2095 END IF;
2096 --
2097 -- Debug Statements
2098 --
2099 --HW OPMCONV - Removed branching
2100
2101 WSH_USA_INV_PVT.update_serial_numbers(
2102 p_delivery_detail_id => l_delivery_detail_id,
2103 p_serial_number => l_serial_number,
2104 p_transaction_temp_id => l_txn_temp_id,
2105 x_return_status => x_return_status);
2106 IF l_debug_on THEN
2107 WSH_DEBUG_SV.log(l_module_name,'update_serial_numbers x_return_status',x_return_status);
2108 END IF;
2109 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
2110 x_return_status := FND_API.G_RET_STS_ERROR;
2111 WSH_UTIL_CORE.add_message (x_return_status,l_module_name);
2112 --
2113 -- Debug Statements
2114 --
2115 IF l_debug_on THEN
2116 WSH_DEBUG_SV.pop(l_module_name);
2117 END IF;
2118 --
2119 return;
2120 END IF;
2121
2122 IF l_delivery_id IS NOT NULL THEN
2123
2124 --
2125 -- Debug Statements
2126 --
2127 IF l_debug_on THEN
2128 WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_DELIVERY_DETAILS_ACTIONS.UNASSIGN_DETAIL_FROM_DELIVERY',WSH_DEBUG_SV.C_PROC_LEVEL);
2129 END IF;
2130 --
2131 wsh_delivery_details_actions.Unassign_Detail_from_delivery
2132 (l_delivery_detail_id, x_return_status);
2133 IF l_debug_on THEN
2134 WSH_DEBUG_SV.log(l_module_name,'Unassign_Detail_from_delivery x_return_status',x_return_status);
2135 END IF;
2136
2137 IF x_return_status = WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR THEN
2138 WSH_UTIL_CORE.add_message (x_return_status,l_module_name);
2139 --
2140 -- Debug Statements
2141 --
2142 IF l_debug_on THEN
2143 WSH_DEBUG_SV.pop(l_module_name);
2144 END IF;
2145 --
2146 return;
2147 END IF;
2148
2149 END IF;
2150 END LOOP;
2151 CLOSE C_overpick;
2152
2153 UPDATE wsh_delivery_details set
2154 requested_quantity = 0,
2155 requested_quantity2 = 0,
2156 picked_quantity = NULL,
2157 picked_quantity2 = NULL,
2158 src_requested_quantity = 0,
2159 src_requested_quantity2 = 0,
2160 shipped_quantity = 0,
2161 shipped_quantity2 = 0,
2162 delivered_quantity = 0,
2163 delivered_quantity2 = 0,
2164 quality_control_quantity = 0,
2165 quality_control_quantity2 = 0,
2166 cycle_count_quantity = 0,
2167 cycle_count_quantity2 = 0,
2168 released_status = 'D',
2169 subinventory = NULL,
2170 revision = NULL,
2171 lot_number = NULL,
2172 locator_id = NULL,
2173 cancelled_quantity = 0,
2174 cancelled_quantity2 = 0,
2175 last_update_date = SYSDATE,
2176 last_updated_by = FND_GLOBAL.USER_ID,
2177 last_update_login = FND_GLOBAL.LOGIN_ID
2178 WHERE source_line_id = p_source_line_id
2179 AND source_code = p_source_code
2180 AND released_status in ('S','Y')
2181 AND requested_quantity = 0
2182 AND picked_quantity > 0
2183 RETURNING delivery_detail_id BULK COLLECT INTO l_detail_tab; -- Added for DBI Project
2184 --
2185 -- DBI Project
2186 -- Update of wsh_delivery_details where requested_quantity/released_status
2187 -- are changed, call DBI API after the update.
2188 -- DBI API will check if DBI is installed
2189 IF l_debug_on THEN
2190 WSH_DEBUG_SV.log(l_module_name,'Calling DBI API.Detail Count-',l_detail_tab.count);
2191 END IF;
2192 WSH_INTEGRATION.DBI_Update_Detail_Log
2193 (p_delivery_detail_id_tab => l_detail_tab,
2194 p_dml_type => 'UPDATE',
2195 x_return_status => l_dbi_rs);
2196
2197 IF l_debug_on THEN
2198 WSH_DEBUG_SV.log(l_module_name,'Return Status after DBI Call-',l_dbi_rs);
2199 END IF;
2200 -- Only Handle Unexpected error
2201 IF l_dbi_rs = WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR THEN
2202 x_return_status := l_dbi_rs;
2203 --
2204 IF l_debug_on THEN
2205 WSH_DEBUG_SV.pop(l_module_name);
2206 END IF;
2207 --
2208 RETURN;
2209 END IF;
2210 -- End of Code for DBI Project
2211 --
2212
2213 /* Bug 2310456 warning handling */
2214 IF l_num_warn > 0 THEN
2215 --x_return_status := WSH_UTIL_CORE.G_RET_STS_WARNING;
2216 -- in this API we treat warning as success as of now
2217 x_return_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
2218 ELSE
2219 x_return_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
2220 END IF;
2221
2222 --
2223 -- Debug Statements
2224 --
2225 IF l_debug_on THEN
2226 WSH_DEBUG_SV.pop(l_module_name);
2227 END IF;
2228 --
2229 EXCEPTION
2230
2231 WHEN NO_DATA_FOUND THEN
2232 --
2233 -- Debug Statements
2234 --
2235 IF l_debug_on THEN
2236 WSH_DEBUG_SV.logmsg(l_module_name, 'NO OVERPICKED LINES REMAINING TO BE CANCELLED' );
2237 END IF;
2238 --
2239 --
2240 -- Debug Statements
2241 --
2242 IF l_debug_on THEN
2243 WSH_DEBUG_SV.logmsg(l_module_name,'NO_DATA_FOUND exception has occured.',WSH_DEBUG_SV.C_EXCEP_LEVEL);
2244 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:NO_DATA_FOUND');
2245 END IF;
2246 --
2247 WHEN OTHERS THEN
2248 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2249 WSH_UTIL_CORE.default_handler('WSH_INTERFACE.Handle_Overpick_cancelled',l_module_name);
2250
2251 --
2252 -- Debug Statements
2253 --
2254 IF l_debug_on THEN
2255 WSH_DEBUG_SV.pop(l_module_name);
2256 END IF;
2257 --
2258 END Handle_Overpick_cancelled;
2259
2260
2261 PROCEDURE log_exception(
2262 p_ship_from_location_id IN NUMBER,
2263 p_delivery_id IN NUMBER DEFAULT NULL,
2264 p_delivery_detail_id IN NUMBER,
2265 p_parent_delivery_detail_id IN NUMBER DEFAULT NULL,
2266 p_delivery_assignment_id IN NUMBER,
2267 p_inventory_item_id IN NUMBER,
2268 p_reason IN VARCHAR2,
2269 x_return_status OUT NOCOPY VARCHAR2)
2270 IS
2271 l_exception_location_id NUMBER;
2272 l_exception_error_message VARCHAR2(2000) := NULL;
2273 l_exception_msg_count NUMBER;
2274 l_dummy_exception_id NUMBER;
2275 l_delivery_assignment_id NUMBER;
2276 l_delivery_detail_id NUMBER;
2277 l_delivery_id NUMBER;
2278 l_exception_msg_data VARCHAR2(4000) := NULL;
2279 l_msg VARCHAR2(2000):=NULL;
2280
2281 logging_exception_failure EXCEPTION;
2282
2283 --
2284 l_debug_on BOOLEAN;
2285 --
2286 l_module_name CONSTANT VARCHAR2(100) := 'wsh.plsql.' || G_PACKAGE_NAME || '.' || 'LOG_EXCEPTION';
2287 --
2288 BEGIN
2289 --
2290 l_debug_on := WSH_DEBUG_INTERFACE.g_debug;
2291 --
2292 IF l_debug_on IS NULL
2293 THEN
2294 l_debug_on := WSH_DEBUG_SV.is_debug_enabled;
2295 END IF;
2296 --
2297 IF l_debug_on THEN
2298 WSH_DEBUG_SV.push(l_module_name);
2299 WSH_DEBUG_SV.log(l_module_name,'P_SHIP_FROM_LOCATION_ID',P_SHIP_FROM_LOCATION_ID);
2300 WSH_DEBUG_SV.log(l_module_name,'P_DELIVERY_ID',P_DELIVERY_ID);
2301 WSH_DEBUG_SV.log(l_module_name,'P_DELIVERY_DETAIL_ID',P_DELIVERY_DETAIL_ID);
2302 WSH_DEBUG_SV.log(l_module_name,'P_PARENT_DELIVERY_DETAIL_ID',P_PARENT_DELIVERY_DETAIL_ID);
2303 WSH_DEBUG_SV.log(l_module_name,'P_DELIVERY_ASSIGNMENT_ID',P_DELIVERY_ASSIGNMENT_ID);
2304 WSH_DEBUG_SV.log(l_module_name,'P_INVENTORY_ITEM_ID',P_INVENTORY_ITEM_ID);
2305 WSH_DEBUG_SV.log(l_module_name,'P_REASON',P_REASON);
2306 END IF;
2307
2308 x_return_status := FND_API.G_RET_STS_SUCCESS;
2309
2310 -- Get p_reason here which is passed as the message name
2311 FND_MESSAGE.SET_NAME('WSH',p_reason);
2312 FND_MESSAGE.SET_TOKEN('DELIVERY_DETAIL_ID',p_delivery_detail_id);
2313 IF p_reason = 'WSH_CANCELLED_PLANNED' THEN
2314 FND_MESSAGE.SET_TOKEN('DELIVERY_ID',p_delivery_id);
2315 END IF;
2316
2317 l_msg := FND_MESSAGE.GET;
2318 l_exception_location_id := p_ship_from_location_id;
2319
2320 IF p_reason = 'WSH_CANCELLED_PACKED' THEN
2321 l_delivery_id := NULL;
2322 l_delivery_assignment_id := NULL;
2323 ELSE
2324 -- Bug 3302632
2325 -- In the case of cancelling a line in a planned
2326 -- delivery, log bug against delivery.
2327 l_delivery_id := p_delivery_id;
2328 l_delivery_assignment_id := p_delivery_assignment_id;
2329 END IF;
2330
2331 WSH_XC_UTIL.log_exception(
2332 p_api_version => 1.0,
2333 x_return_status => x_return_status,
2334 x_msg_count => l_exception_msg_count,
2335 x_msg_data => l_exception_msg_data,
2336 x_exception_id => l_dummy_exception_id ,
2337 p_logged_at_location_id => l_exception_location_id,
2338 p_exception_location_id => l_exception_location_id,
2339 p_logging_entity => 'SHIPPER',
2340 p_logging_entity_id => FND_GLOBAL.USER_ID,
2341 p_exception_name => 'WSH_CHANGED_QUANTITY',
2342 p_message => l_msg,
2343 p_delivery_detail_id => nvl(p_parent_delivery_detail_id,p_delivery_detail_id),
2344 p_delivery_assignment_id => l_delivery_assignment_id,
2345 p_delivery_id => l_delivery_id,
2346 p_inventory_item_id => p_inventory_item_id,
2347 p_error_message => l_exception_error_message
2348 );
2349
2350 IF l_debug_on THEN
2351 WSH_DEBUG_SV.log(l_module_name,'x_return_status',x_return_status);
2352 WSH_DEBUG_SV.logmsg(l_module_name, 'EXCEPTION ID: '|| L_DUMMY_EXCEPTION_ID );
2353 WSH_DEBUG_SV.logmsg(l_module_name, 'EXCEPTION MSG DATA: '|| L_EXCEPTION_MSG_DATA );
2354 END IF;
2355
2356 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
2357 RAISE logging_exception_failure;
2358 END IF;
2359
2360 IF l_debug_on THEN
2361 WSH_DEBUG_SV.pop(l_module_name);
2362 END IF;
2363 --
2364 EXCEPTION
2365 WHEN logging_exception_failure THEN
2366 x_return_status := FND_API.G_RET_STS_ERROR;
2367 WSH_UTIL_CORE.add_message (x_return_status,l_module_name);
2368 --
2369 -- Debug Statements
2370 --
2371 IF l_debug_on THEN
2372 WSH_DEBUG_SV.logmsg(l_module_name,'LOGGING_EXCEPTION_FAILURE exception has occured.',WSH_DEBUG_SV.C_EXCEP_LEVEL);
2373 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:LOGGING_EXCEPTION_FAILURE');
2374 END IF;
2375 --
2376 WHEN others THEN
2377 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2378 --
2379 -- Debug Statements
2380 --
2381 IF l_debug_on THEN
2382 WSH_DEBUG_SV.logmsg(l_module_name,'Unexpected error has occured. Oracle error message is '|| SQLERRM,WSH_DEBUG_SV.C_UNEXPEC_ERR_LEVEL);
2383 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:OTHERS');
2384 END IF;
2385 --
2386 END log_exception;
2387
2388
2389 -- HW OPMCONV - Removed l_process_flag parameter
2390
2391 PROCEDURE cancel_quantity(
2392 p_source_code IN VARCHAR2,
2393 p_source_header_id IN NUMBER, -- New
2394 p_source_line_id IN NUMBER,
2395 p_delivery_detail_id IN NUMBER,
2396 p_parent_delivery_detail_id IN NUMBER DEFAULT NULL,
2397 p_serial_number IN VARCHAR2,
2398 p_transaction_temp_id IN NUMBER,
2399 p_released_status IN VARCHAR2,
2400 p_move_order_line_id IN NUMBER,
2401 p_organization_id IN NUMBER,
2402 p_inventory_item_id IN NUMBER ,
2403 p_subinventory IN VARCHAR2 ,
2404 p_revision IN VARCHAR2 ,
2405 p_lot_number IN VARCHAR2 ,
2406 p_locator_id IN NUMBER ,
2407 p_ordered_quantity IN NUMBER,
2408 p_requested_quantity IN NUMBER,
2409 p_requested_quantity2 IN NUMBER,
2410 p_picked_quantity IN NUMBER DEFAULT NULL,
2411 p_picked_quantity2 IN NUMBER DEFAULT NULL,
2412 p_shipped_quantity IN NUMBER,
2413 p_shipped_quantity2 IN NUMBER,
2414 p_changed_detail_quantity IN NUMBER,
2415 p_changed_detail_quantity2 IN NUMBER,
2416 p_ship_tolerance_above IN NUMBER,
2417 p_serial_quantity IN NUMBER,
2418 p_replenishment_status IN VARCHAR2 DEFAULT NULL, --bug# 6689448 (replenishment project)
2419 x_return_status OUT NOCOPY VARCHAR2)
2420 IS
2421
2422 -- Bug fix 2864546. Added planned_flag in the select
2423 CURSOR c_check_del_assign(p_del_det IN NUMBER) is
2424 SELECT wda.delivery_id, wnd.planned_flag,
2425 wnd.ignore_for_planning, -- OTM R12 : cancel quantity
2426 wnd.tms_interface_flag -- OTM R12 : cancel quantity
2427 FROM wsh_delivery_assignments_v wda,
2428 wsh_new_deliveries wnd
2429 WHERE wda.delivery_id is not null
2430 AND wda.delivery_detail_id = p_del_det
2431 AND wda.delivery_id = wnd.delivery_id;
2432
2433 l_delivery_detail_split_rec WSH_USA_INV_PVT.DeliveryDetailInvRecType;
2434 l_picked_quantity NUMBER;
2435 l_picked_quantity2 NUMBER;
2436 l_excess_picked_quantity NUMBER;
2437 -- HW OPMCONV - Added new variables
2438 l_excess_picked_quantity2 NUMBER;
2439 l_change_reservation_quantity NUMBER;
2440 l_change_reservation_quantity2 NUMBER;
2441 l_delivery_id NUMBER;
2442 l_msg_count NUMBER;
2443 l_msg_data VARCHAR2(240);
2444 l_error_text VARCHAR2(6000);
2445
2446 /*Bug 2136603 - added variables */
2447 l_gross NUMBER;
2448 l_net NUMBER;
2449 l_volume NUMBER;
2450 l_fill NUMBER;
2451 l_cont_name VARCHAR2(30);
2452 l_num_warn NUMBER := 0;
2453
2454 l_serial_quantity NUMBER := 0;
2455
2456 l_detail_tab WSH_UTIL_CORE.id_tab_type; -- DBI Project
2457 l_dbi_rs VARCHAR2(1); -- Return Status from DBI API
2458
2459 /*2729687 Added l_details_id and l_return_status */
2460
2461 l_details_id WSH_UTIL_CORE.Id_Tab_Type;
2462
2463 l_return_status VARCHAR2(30);
2464
2465 l_planned_flag VARCHAR2(1); -- Bug fix 2864546
2466
2467 -- OTM R12 : cancel quantity
2468 l_ignore WSH_NEW_DELIVERIES.IGNORE_FOR_PLANNING%TYPE;
2469 l_tms_interface_flag WSH_NEW_DELIVERIES.TMS_INTERFACE_FLAG%TYPE;
2470 l_delivery_id_tab WSH_UTIL_CORE.ID_TAB_TYPE;
2471 l_interface_flag_tab WSH_UTIL_CORE.COLUMN_TAB_TYPE;
2472 l_call_update VARCHAR2(1);
2473 l_del_assigned VARCHAR2(1);
2474 l_gc3_is_installed VARCHAR2(1);
2475 -- OTM R12 : cancel quantity
2476
2477 l_debug_on BOOLEAN;
2478 --
2479 l_module_name CONSTANT VARCHAR2(100) := 'wsh.plsql.' || G_PACKAGE_NAME || '.' || 'CANCEL_QUANTITY';
2480 --
2481 BEGIN
2482
2483 --
2484 -- Debug Statements
2485 --
2486 --
2487 l_debug_on := WSH_DEBUG_INTERFACE.g_debug;
2488 --
2489 IF l_debug_on IS NULL
2490 THEN
2491 l_debug_on := WSH_DEBUG_SV.is_debug_enabled;
2492 END IF;
2493 --
2494 IF l_debug_on THEN
2495 WSH_DEBUG_SV.push(l_module_name);
2496 --
2497 WSH_DEBUG_SV.log(l_module_name,'P_SOURCE_CODE',P_SOURCE_CODE);
2498 WSH_DEBUG_SV.log(l_module_name,'P_SOURCE_HEADER_ID',P_SOURCE_HEADER_ID);
2499 WSH_DEBUG_SV.log(l_module_name,'P_SOURCE_LINE_ID',P_SOURCE_LINE_ID);
2500 WSH_DEBUG_SV.log(l_module_name,'P_DELIVERY_DETAIL_ID',P_DELIVERY_DETAIL_ID);
2501 WSH_DEBUG_SV.log(l_module_name,'P_PARENT_DELIVERY_DETAIL_ID',P_PARENT_DELIVERY_DETAIL_ID);
2502 WSH_DEBUG_SV.log(l_module_name,'P_SERIAL_NUMBER',P_SERIAL_NUMBER);
2503 WSH_DEBUG_SV.log(l_module_name,'P_TRANSACTION_TEMP_ID',P_TRANSACTION_TEMP_ID);
2504 WSH_DEBUG_SV.log(l_module_name,'P_RELEASED_STATUS',P_RELEASED_STATUS);
2505 WSH_DEBUG_SV.log(l_module_name,'P_MOVE_ORDER_LINE_ID',P_MOVE_ORDER_LINE_ID);
2506 WSH_DEBUG_SV.log(l_module_name,'P_ORGANIZATION_ID',P_ORGANIZATION_ID);
2507 WSH_DEBUG_SV.log(l_module_name,'P_INVENTORY_ITEM_ID',P_INVENTORY_ITEM_ID);
2508 WSH_DEBUG_SV.log(l_module_name,'P_SUBINVENTORY',P_SUBINVENTORY);
2509 WSH_DEBUG_SV.log(l_module_name,'P_REVISION',P_REVISION);
2510 WSH_DEBUG_SV.log(l_module_name,'P_LOT_NUMBER',P_LOT_NUMBER);
2511 WSH_DEBUG_SV.log(l_module_name,'P_LOCATOR_ID',P_LOCATOR_ID);
2512 WSH_DEBUG_SV.log(l_module_name,'P_ORDERED_QUANTITY',P_ORDERED_QUANTITY);
2513 WSH_DEBUG_SV.log(l_module_name,'P_REQUESTED_QUANTITY',P_REQUESTED_QUANTITY);
2514 WSH_DEBUG_SV.log(l_module_name,'P_REQUESTED_QUANTITY2',P_REQUESTED_QUANTITY2);
2515 WSH_DEBUG_SV.log(l_module_name,'P_PICKED_QUANTITY',P_PICKED_QUANTITY);
2516 WSH_DEBUG_SV.log(l_module_name,'P_PICKED_QUANTITY2',P_PICKED_QUANTITY2);
2517 WSH_DEBUG_SV.log(l_module_name,'P_SHIPPED_QUANTITY',P_SHIPPED_QUANTITY);
2518 WSH_DEBUG_SV.log(l_module_name,'P_SHIPPED_QUANTITY2',P_SHIPPED_QUANTITY2);
2519 WSH_DEBUG_SV.log(l_module_name,'P_CHANGED_DETAIL_QUANTITY',P_CHANGED_DETAIL_QUANTITY);
2520 WSH_DEBUG_SV.log(l_module_name,'P_CHANGED_DETAIL_QUANTITY2',P_CHANGED_DETAIL_QUANTITY2);
2521 WSH_DEBUG_SV.log(l_module_name,'P_SHIP_TOLERANCE_ABOVE',P_SHIP_TOLERANCE_ABOVE);
2522 WSH_DEBUG_SV.log(l_module_name,'P_SERIAL_QUANTITY',P_SERIAL_QUANTITY);
2523 END IF;
2524 --
2525 --
2526 -- Debug Statements
2527 --
2528 IF l_debug_on THEN
2529 WSH_DEBUG_SV.logmsg(l_module_name, 'IN WSH_USA_QUANTITY_PVT.CANCEL_QUANTITY ' );
2530 END IF;
2531 --
2532 x_return_status := FND_API.G_RET_STS_SUCCESS;
2533
2534 -- OTM R12
2535 l_gc3_is_installed := WSH_UTIL_CORE.G_GC3_IS_INSTALLED;
2536
2537 IF (l_gc3_is_installed IS NULL) THEN
2538 l_gc3_is_installed := WSH_UTIL_CORE.GC3_IS_INSTALLED;
2539 END IF;
2540 -- End of OTM R12
2541
2542 -- Overpicking creates extra reservations.
2543 -- Therefore, when quantity is reduced or cancelled,
2544 -- we need to purge the extra reservations.
2545
2546 l_excess_picked_quantity := p_picked_quantity - p_requested_quantity;
2547 -- HW OPMCONV - Calculate l_excess_picked_quantity2
2548 l_excess_picked_quantity2 := p_picked_quantity2 - p_requested_quantity2;
2549
2550 -- HW OPMCONV - Remove checking for process
2551 IF (l_excess_picked_quantity > 0) THEN
2552
2553 -- HW OPMCONV - Pass l_excess_picked_quantity2
2554
2555 wsh_delivery_details_actions.unreserve_delivery_detail(
2556 p_delivery_Detail_id => p_delivery_detail_id,
2557 p_unreserve_mode => 'UNRESERVE',
2558 p_quantity_to_unreserve => l_excess_picked_quantity,
2559 p_quantity2_to_unreserve => l_excess_picked_quantity2,
2560 p_override_retain_ato_rsv => 'Y',
2561 x_return_status => x_return_status );
2562
2563 IF l_debug_on THEN
2564 WSH_DEBUG_SV.log(l_module_name,'unreserve_delivery_detail x_return_status',x_return_status);
2565 END IF;
2566 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
2567 x_return_status := FND_API.G_RET_STS_ERROR;
2568 WSH_UTIL_CORE.add_message (x_return_status,l_module_name);
2569 --
2570 -- Debug Statements
2571 --
2572 IF l_debug_on THEN
2573 WSH_DEBUG_SV.pop(l_module_name);
2574 END IF;
2575 --
2576 return;
2577 END IF;
2578 END IF;
2579
2580 IF (p_requested_quantity > ABS(p_changed_detail_quantity) ) THEN
2581 l_change_reservation_quantity := p_changed_detail_quantity;
2582 l_change_reservation_quantity2 := p_changed_detail_quantity2;
2583
2584 IF (p_released_status = 'Y') THEN
2585 l_picked_quantity := p_requested_quantity - p_changed_detail_quantity;
2586 l_picked_quantity2 := p_requested_quantity2 - ABS(p_changed_detail_quantity2); -- OPM Bug 5648794
2587 -- Bug 5648794 ABS is required due to code changes for Bug fix 2187389 send changed_line_quantity
2588 -- changed_line_quantity Unlike p_changed_detail_quantity could be positive or negative.
2589 -- Picked quantity2 can only be reduced never increased.
2590 ELSE
2591 l_picked_quantity := p_picked_quantity;
2592 l_picked_quantity2 := p_picked_quantity2;
2593 END IF;
2594
2595 IF ( (p_requested_quantity - ABS(p_changed_detail_quantity)) >= nvl(p_shipped_quantity,0) ) THEN
2596 IF l_debug_on THEN
2597 WSH_DEBUG_SV.logmsg(l_module_name, 'CASE 1 ' );
2598 END IF;
2599 UPDATE wsh_delivery_details SET
2600 requested_quantity = p_requested_quantity - ABS(p_changed_detail_quantity),
2601 requested_quantity2 = p_requested_quantity2 - ABS(p_changed_detail_quantity2),
2602 -- HW OPMCONV - Update Qty2 similar to Qty1
2603 -- requested_quantity2 = p_requested_quantity2 + p_changed_detail_quantity2, -- OPM B2187389
2604 picked_quantity = l_picked_quantity,
2605 picked_quantity2 = l_picked_quantity2,
2606 cycle_count_quantity = decode(cycle_count_quantity,null,null,0,0,(p_requested_quantity - nvl(p_shipped_quantity,0)) - ABS(p_changed_detail_quantity)),
2607 cycle_count_quantity2 = decode(cycle_count_quantity2,null,null,0,0,(p_requested_quantity2 - nvl(p_shipped_quantity2,0)) - ABS(p_changed_detail_quantity2)),
2608 cancelled_quantity = nvl(cancelled_quantity,0) + ABS(p_changed_detail_quantity),
2609 cancelled_quantity2 = nvl(cancelled_quantity2,0) + ABS(p_changed_detail_quantity2),
2610 last_update_date = SYSDATE,
2611 last_updated_by = FND_GLOBAL.USER_ID,
2612 last_update_login = FND_GLOBAL.LOGIN_ID
2613 where delivery_detail_id = p_delivery_detail_id;
2614 ELSE
2615 UPDATE wsh_delivery_details SET
2616 requested_quantity = p_requested_quantity - ABS(p_changed_detail_quantity),
2617 requested_quantity2 = p_requested_quantity2 - ABS(p_changed_detail_quantity2),
2618 -- HW OPMCONV - Update Qty2 similar to Qty1
2619 -- requested_quantity2 = p_requested_quantity2 + p_changed_detail_quantity2, -- OPM B2187389
2620 picked_quantity = l_picked_quantity,
2621 picked_quantity2 = l_picked_quantity2,
2622 shipped_quantity = p_requested_quantity - ABS(p_changed_detail_quantity),
2623 shipped_quantity2 = p_requested_quantity2 - ABS(p_changed_detail_quantity2),
2624 cycle_count_quantity = decode(cycle_count_quantity,null,null,0),
2625 cycle_count_quantity2 = decode(cycle_count_quantity2,null,null,0),
2626 cancelled_quantity = nvl(cancelled_quantity,0) + ABS(p_changed_detail_quantity),
2627 cancelled_quantity2 = nvl(cancelled_quantity2,0) + ABS(p_changed_detail_quantity2),
2628 last_update_date = SYSDATE,
2629 last_updated_by = FND_GLOBAL.USER_ID,
2630 last_update_login = FND_GLOBAL.LOGIN_ID
2631 WHERE delivery_detail_id = p_delivery_detail_id;
2632 --
2633 END IF; -- end of comparison between requested - detail with shipped quantity
2634
2635 --bug# 6689448 (replenishment project) (Begin) : added the code to call WMS api for replenshment requested
2636 -- delivery detail lines to decrease the qty from replenishment tables.
2637 IF (p_replenishment_status = 'R' and p_released_status in ('R','B')) THEN
2638 --{
2639 IF l_debug_on THEN
2640 WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WMS_REPLENISHMENT_PUB.UPDATE_DELIVERY_DETAIL' ,WSH_DEBUG_SV.C_PROC_LEVEL);
2641 END IF;
2642 WMS_REPLENISHMENT_PUB.UPDATE_DELIVERY_DETAIL (
2643 p_delivery_detail_id => p_delivery_detail_id,
2644 p_primary_quantity => (p_requested_quantity - ABS(p_changed_detail_quantity)),
2645 x_return_status => x_return_status);
2646 IF x_return_status <> WSH_UTIL_CORE.G_RET_STS_SUCCESS THEN
2647 --{
2648 IF l_debug_on THEN
2649 WSH_DEBUG_SV.logmsg(l_module_name, 'UNEXPECTED ERROR FROM WMS_REPLENISHMENT_PUB.UPDATE_DELIVERY_DETAIL');
2650 WSH_DEBUG_SV.pop(l_module_name);
2651 END IF;
2652 RETURN;
2653 --}
2654 END IF;
2655 --}
2656 END IF;
2657 --bug# 6689448 (replenishment project):end
2658
2659 -- OTM R12 : cancel quantity
2660 IF (nvl(p_changed_detail_quantity, 0) <> 0 AND l_gc3_is_installed = 'Y') THEN
2661 l_call_update := 'Y';
2662 l_ignore := 'Y';
2663 l_del_assigned := 'N';
2664
2665 OPEN c_check_del_assign(p_delivery_detail_id);
2666 FETCH c_check_del_assign INTO l_delivery_id, l_planned_flag, l_ignore, l_tms_interface_flag;
2667
2668 IF (c_check_del_assign%FOUND AND nvl(l_ignore, 'N') = 'N') THEN
2669 l_del_assigned := 'Y';
2670 END IF;
2671
2672 CLOSE c_check_del_assign;
2673
2674 IF (l_del_assigned = 'Y') THEN
2675 l_delivery_id_tab.DELETE;
2676 l_interface_flag_tab.DELETE;
2677
2678 l_delivery_id_tab(1) := l_delivery_id;
2679
2680 --calculate the interface flag to be updated
2681 IF l_tms_interface_flag IN
2682 (WSH_NEW_DELIVERIES_PVT.C_TMS_ANSWER_RECEIVED,
2683 WSH_NEW_DELIVERIES_PVT.C_TMS_AWAITING_ANSWER,
2684 WSH_NEW_DELIVERIES_PVT.C_TMS_CREATE_IN_PROCESS,
2685 WSH_NEW_DELIVERIES_PVT.C_TMS_UPDATE_IN_PROCESS) THEN
2686 l_interface_flag_tab(1) := WSH_NEW_DELIVERIES_PVT.C_TMS_UPDATE_REQUIRED;
2687 ELSE
2688 l_call_update := 'N';
2689 END IF;
2690
2691 IF (l_call_update = 'Y') THEN
2692 WSH_NEW_DELIVERIES_PVT.UPDATE_TMS_INTERFACE_FLAG(
2693 p_delivery_id_tab => l_delivery_id_tab,
2694 p_tms_interface_flag_tab => l_interface_flag_tab,
2695 x_return_status => l_return_status);
2696
2697 IF (l_return_status IN (WSH_UTIL_CORE.G_RET_STS_ERROR, WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR)) THEN
2698
2699 x_return_status := l_return_status;
2700 return;
2701 END IF;
2702 END IF;
2703 END IF;
2704 END IF;
2705 -- End of OTM R12 : cancel quantity
2706
2707 -- DBI Project
2708 -- Update of wsh_delivery_details where requested_quantity/released_status
2709 -- are changed, call DBI API after the update.
2710 -- Either of the above 2 updates will be executed for same delivery detail id
2711 -- hence have one call to DBI wrapper and not 2,this will cover for both
2712 -- This API will also check for DBI Installed or not
2713 IF l_debug_on THEN
2714 WSH_DEBUG_SV.log(l_module_name,'Calling DBI API.Detail id-',p_delivery_detail_id);
2715 END IF;
2716 l_detail_tab(1) := p_delivery_detail_id;
2717 WSH_INTEGRATION.DBI_Update_Detail_Log
2718 (p_delivery_detail_id_tab => l_detail_tab,
2719 p_dml_type => 'UPDATE',
2720 x_return_status => l_dbi_rs);
2721
2722 IF l_debug_on THEN
2723 WSH_DEBUG_SV.log(l_module_name,'Return Status after DBI Call-',l_dbi_rs);
2724 END IF;
2725 IF l_dbi_rs = WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR THEN
2726 x_return_status := l_dbi_rs;
2727 -- just pass this return status to caller API
2728 IF l_debug_on THEN
2729 WSH_DEBUG_SV.pop(l_module_name,'DBI API Returned Unexpected error '||x_return_status);
2730 WSH_DEBUG_SV.pop(l_module_name);
2731 END IF;
2732 return;
2733 END IF;
2734 -- End of Code for DBI Project
2735 --
2736
2737 -- HW OPMCONV - Added the following check
2738 -- If Qty1 and Qty2 are increased on sales order
2739 -- Update Qty2 correctly
2740 IF ( ( (p_requested_quantity2 - ABS(p_changed_detail_quantity2)) >= nvl(p_shipped_quantity2,0)
2741 AND (p_requested_quantity - ABS(p_changed_detail_quantity)) >= nvl(p_shipped_quantity,0)) ) THEN
2742 IF l_debug_on THEN
2743 WSH_DEBUG_SV.logmsg(l_module_name, 'CASE 2 ' );
2744 END IF;
2745 UPDATE wsh_delivery_details SET
2746 requested_quantity2 = p_requested_quantity2 + p_changed_detail_quantity2 -- OPM B2187389
2747 WHERE delivery_detail_id = p_delivery_detail_id;
2748 END IF;
2749
2750 -- J: W/V Changes
2751 IF l_debug_on THEN
2752 WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_WV_UTILS.Detail_Weight_Volume',WSH_DEBUG_SV.C_PROC_LEVEL);
2753 END IF;
2754 WSH_WV_UTILS.Detail_Weight_Volume(
2755 p_delivery_detail_id => p_delivery_detail_id,
2756 p_update_flag => 'Y',
2757 p_post_process_flag => 'Y',
2758 p_calc_wv_if_frozen => 'N',
2759 x_net_weight => l_net,
2760 x_volume => l_volume,
2761 x_return_status => l_return_status);
2762
2763 IF l_return_status = WSH_UTIL_CORE.G_RET_STS_ERROR THEN
2764 x_return_status := l_return_status;
2765 IF l_debug_on THEN
2766 WSH_DEBUG_SV.pop(l_module_name,'Detail_Weight_Volume returned '||l_return_status);
2767 WSH_DEBUG_SV.pop(l_module_name);
2768 END IF;
2769 return;
2770 END IF;
2771
2772 IF p_released_status = 'S' THEN
2773 --{
2774 -- HW OPMCONV - Removed code forking
2775 -- bug 2186091: it is better to always reduce move order
2776 -- quantity than to allow subsequent overpicking.
2777 -- That way, default pick confirm will match the
2778 -- updated ordered quantity.
2779 -- INV has bug 2168209 to investigate allowing
2780 -- overpicking after reducing move order quantity.
2781
2782 IF l_debug_on THEN
2783 WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit INV_MO_CANCEL_PVT.REDUCE_MOVE_ORDER_QUANTITY',WSH_DEBUG_SV.C_PROC_LEVEL);
2784
2785 WSH_DEBUG_SV.push(l_module_name);
2786 --
2787 WSH_DEBUG_SV.log(l_module_name,'p_changed_detail_quantity',p_changed_detail_quantity);
2788 WSH_DEBUG_SV.log(l_module_name,'p_changed_detail_quantity2',ABS(p_changed_detail_quantity2));
2789 END IF;
2790 --
2791 -- HW OPMCONV - Pass Qty2
2792 INV_MO_Cancel_PVT.Reduce_Move_Order_Quantity(
2793 x_return_status => x_return_status,
2794 x_msg_count => l_msg_count,
2795 x_msg_data => l_msg_data,
2796 p_line_id => p_move_order_line_id,
2797 p_reduction_quantity => p_changed_detail_quantity,
2798 p_sec_reduction_quantity => ABS(p_changed_detail_quantity2),
2799 p_txn_source_line_id => p_source_line_id,
2800 p_delivery_detail_id => p_delivery_detail_id -- X-dock changes
2801 );
2802
2803 IF l_debug_on THEN
2804 WSH_DEBUG_SV.log(l_module_name,'Reduce_Move_Order_Quantity x_return_status',x_return_status);
2805 END IF;
2806 IF x_return_status = fnd_api.g_ret_sts_success THEN
2807 --
2808 -- Debug Statements
2809 --
2810 IF l_debug_on THEN
2811 WSH_DEBUG_SV.logmsg(l_module_name, 'REDUCED QUANTITY FOR MOLINE '||P_MOVE_ORDER_LINE_ID ||' BY '|| P_CHANGED_DETAIL_QUANTITY );
2812 WSH_DEBUG_SV.logmsg(l_module_name, 'ERROR IN INV_MO_CANCEL_PVT.REDUCE_MOVE_ORDER_QUANTITY ' );
2813 END IF;
2814 --
2815 ELSE
2816 FND_MESSAGE.Set_Name('WSH', 'WSH_REDUCE_MO_QUANTITY');
2817 IF l_msg_count = 1 THEN
2818 --
2819 -- Debug Statements
2820 --
2821 IF l_debug_on THEN
2822 WSH_DEBUG_SV.logmsg(l_module_name, 'ERROR: '|| L_MSG_DATA );
2823 END IF;
2824 --
2825 FND_MESSAGE.Set_Token('ERROR_TEXT',l_msg_data);
2826 ELSE
2827 FOR l_index IN 1..l_msg_count LOOP
2828 l_msg_data := fnd_msg_pub.get;
2829 --
2830 -- Debug Statements
2831 --
2832 IF l_debug_on THEN
2833 WSH_DEBUG_SV.logmsg(l_module_name, 'ERROR: '|| L_MSG_DATA );
2834 END IF;
2835 --
2836 l_error_text := l_error_text || l_msg_data;
2837 END LOOP;
2838 FND_MESSAGE.Set_Token('ERROR_TEXT',l_error_text);
2839 l_error_text := '';
2840 END IF;
2841 x_return_status := FND_API.G_RET_STS_ERROR;
2842 WSH_UTIL_CORE.add_message (x_return_status,l_module_name);
2843 --
2844 -- Debug Statements
2845 --
2846 IF l_debug_on THEN
2847 WSH_DEBUG_SV.pop(l_module_name);
2848 END IF;
2849 --
2850 return;
2851 END IF; -- end of return_status handling
2852 --HW OPMCONV - Removed branching
2853 --}
2854 END IF;
2855
2856 ELSE
2857 l_change_reservation_quantity := p_requested_quantity;
2858 l_change_reservation_quantity2 := p_requested_quantity2;
2859
2860 update wsh_delivery_details set
2861 requested_quantity = 0,
2862 requested_quantity2 = 0,
2863 picked_quantity = NULL,
2864 picked_quantity2 = NULL,
2865 src_requested_quantity = 0,
2866 src_requested_quantity2 = 0,
2867 shipped_quantity = 0,
2868 shipped_quantity2 = 0,
2869 delivered_quantity = 0,
2870 delivered_quantity2 = 0,
2871 quality_control_quantity = 0,
2872 quality_control_quantity2 = 0,
2873 cycle_count_quantity = 0,
2874 cycle_count_quantity2 = 0,
2875 released_status = 'D',
2876 subinventory = NULL,
2877 revision = NULL,
2878 lot_number = NULL,
2879 locator_id = NULL,
2880 cancelled_quantity = nvl(cancelled_quantity,0) + p_requested_quantity,
2881 cancelled_quantity2 = nvl(cancelled_quantity2,0) + p_requested_quantity2,
2882 last_update_date = SYSDATE,
2883 last_updated_by = FND_GLOBAL.USER_ID,
2884 last_update_login = FND_GLOBAL.LOGIN_ID
2885 where delivery_detail_id = p_delivery_detail_id;
2886
2887 --bug# 6689448 (replenishment project) (Begin) : added the code to call WMS api for replenshment requested delivery
2888 -- detail lines with zeqo qty so that WMS deletes the replenishment record.
2889 IF (p_replenishment_status = 'R' and p_released_status in ('R','B')) THEN
2890 --{
2891 IF l_debug_on THEN
2892 WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WMS_REPLENISHMENT_PUB.UPDATE_DELIVERY_DETAIL' ,WSH_DEBUG_SV.C_PROC_LEVEL);
2893 END IF;
2894 WMS_REPLENISHMENT_PUB.UPDATE_DELIVERY_DETAIL (
2895 p_delivery_detail_id => p_delivery_detail_id,
2896 p_primary_quantity => 0,
2897 x_return_status => x_return_status);
2898 IF x_return_status <> WSH_UTIL_CORE.G_RET_STS_SUCCESS THEN
2899 --{
2900 IF l_debug_on THEN
2901 WSH_DEBUG_SV.logmsg(l_module_name, 'UNEXPECTED ERROR FROM WMS_REPLENISHMENT_PUB.UPDATE_DELIVERY_DETAIL');
2902 WSH_DEBUG_SV.pop(l_module_name);
2903 END IF;
2904 RETURN;
2905 --}
2906 END IF;
2907 --}
2908 END IF;
2909 --bug# 6689448 (replenishment project):end
2910
2911
2912 -- DBI Project
2913 -- Update of wsh_delivery_details where requested_quantity/released_status
2914 -- are changed, call DBI API after the update.
2915 -- This API will also check for DBI Installed or not
2916 IF l_debug_on THEN
2917 WSH_DEBUG_SV.log(l_module_name,'Calling DBI API.Detail id-',p_delivery_detail_id);
2918 END IF;
2919 l_detail_tab(1) := p_delivery_detail_id;
2920 WSH_INTEGRATION.DBI_Update_Detail_Log
2921 (p_delivery_detail_id_tab => l_detail_tab,
2922 p_dml_type => 'UPDATE',
2923 x_return_status => l_dbi_rs);
2924
2925 IF l_debug_on THEN
2926 WSH_DEBUG_SV.log(l_module_name,'Return Status after DBI Call-',l_dbi_rs);
2927 END IF;
2928 IF l_dbi_rs = WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR THEN
2929 x_return_status := l_dbi_rs;
2930 -- just pass this return status to caller API
2931 IF l_debug_on THEN
2932 WSH_DEBUG_SV.pop(l_module_name,'DBI API Returned Unexpected error '||x_return_status);
2933 WSH_DEBUG_SV.pop(l_module_name);
2934 END IF;
2935 return;
2936 END IF;
2937 -- End of Code for DBI Project
2938 --
2939
2940 -- J: W/V Changes
2941 IF l_debug_on THEN
2942 WSH_DEBUG_SV.log(l_module_name,'Calling Detail_Weight_Volume to reset W/V for cancelled dd');
2943 END IF;
2944 WSH_WV_UTILS.Detail_Weight_Volume(
2945 p_delivery_detail_id => p_delivery_detail_id,
2946 p_update_flag => 'Y',
2947 p_post_process_flag => 'Y',
2948 x_net_weight => l_net,
2949 x_volume => l_volume,
2950 x_return_status => l_return_status);
2951 IF l_return_status = WSH_UTIL_CORE.G_RET_STS_ERROR THEN
2952 x_return_status := l_return_status;
2953 IF l_debug_on THEN
2954 WSH_DEBUG_SV.pop(l_module_name,'Detail_Weight_Volume returned '||l_return_status);
2955 WSH_DEBUG_SV.pop(l_module_name);
2956 END IF;
2957 return;
2958 END IF;
2959
2960 open c_check_del_assign(p_delivery_detail_id);
2961 FETCH c_check_del_assign INTO l_delivery_id, l_planned_flag, l_ignore, l_tms_interface_flag; -- OTM R12 : cancel quantity, cursor is changed
2962
2963 IF c_check_del_assign%found THEN
2964 --
2965 /*Start of bug 2729687*/
2966 -- Bug fix 2864546. Need to call unassign procedure only when the
2967 -- delivery is not planned
2968 -- Per discussion with PM, the cancelled lines should be left
2969 -- assigned to the delivery if the delivery is planned
2970 IF nvl(l_planned_flag, 'N') = 'N' THEN
2971
2972 l_details_id(1) := p_delivery_detail_id;
2973 WSH_DELIVERY_DETAILS_ACTIONS.unassign_unpack_empty_cont (
2974 p_ids_tobe_unassigned => l_details_id ,
2975 p_validate_flag => 'Y',
2976 x_return_status => x_return_status);
2977 IF l_debug_on THEN
2978 WSH_DEBUG_SV.log(l_module_name,'Unassign_Unpack_Empty_cont x_return_status',x_return_status);
2979 END IF;
2980
2981 IF (x_return_status IN (WSH_UTIL_CORE.G_RET_STS_ERROR, WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR)) THEN
2982 IF l_debug_on THEN
2983 WSH_DEBUG_SV.pop(l_module_name);
2984 END IF;
2985 return;
2986 END IF;
2987
2988 END IF; --nvl(l_planned_flag, 'N') = 'N'
2989 END IF;
2990
2991 /* End of bug 2729687*/
2992
2993 close c_check_del_assign;
2994
2995
2996 IF p_parent_delivery_detail_id IS NOT NULL THEN
2997
2998 WSH_DELIVERY_DETAILS_ACTIONS.Unassign_Detail_from_Cont(p_delivery_detail_id,x_return_status);
2999 IF l_debug_on THEN
3000 WSH_DEBUG_SV.log(l_module_name,'Unassign_Detail_from_Cont x_return_status',x_return_status);
3001 END IF;
3002 IF x_return_status = WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR THEN
3003 WSH_UTIL_CORE.add_message (x_return_status,l_module_name);
3004 --
3005 -- Debug Statements
3006 --
3007 IF l_debug_on THEN
3008 WSH_DEBUG_SV.pop(l_module_name);
3009 END IF;
3010 --
3011 return;
3012 END IF;
3013
3014 END IF;
3015
3016 IF p_released_status = 'S' THEN
3017 IF p_ordered_quantity <> 0 THEN
3018 --
3019 -- HW OPMCONV - Removded code forking
3020 --
3021 -- Debug Statements
3022 --
3023 IF l_debug_on THEN
3024 WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit INV_MO_CANCEL_PVT.CANCEL_MOVE_ORDER_LINE',WSH_DEBUG_SV.C_PROC_LEVEL);
3025 END IF;
3026 --
3027 INV_MO_Cancel_PVT.Cancel_Move_Order_Line(
3028 x_return_status => x_return_status
3029 ,x_msg_count => l_msg_count
3030 ,x_msg_data => l_msg_data
3031 ,p_line_id => p_move_order_line_id
3032 ,p_delete_reservations => 'Y'
3033 ,p_txn_source_line_id => p_source_line_id
3034 ,p_delivery_detail_id => p_delivery_detail_id --X-dock
3035 );
3036 IF l_debug_on THEN
3037 WSH_DEBUG_SV.log(l_module_name,'INV_MO_Cancel_PVT.Cancel_Move_Order_Line x_return_status',x_return_status);
3038 END IF;
3039 --HW OPMCONV. Removed code forking
3040 IF x_return_status = fnd_api.g_ret_sts_success THEN
3041 --
3042 -- Debug Statements
3043 --
3044 IF l_debug_on THEN
3045 WSH_DEBUG_SV.logmsg(l_module_name, 'CANCELLED MOLINE '||P_MOVE_ORDER_LINE_ID );
3046 END IF;
3047 --
3048 ELSE
3049 --
3050 -- Debug Statements
3051 --
3052 IF l_debug_on THEN
3053 WSH_DEBUG_SV.logmsg(l_module_name, 'ERROR IN INV_MO_CANCEL_PVT.CANCEL_MOVE_ORDER_LINE ' );
3054 END IF;
3055 --
3056 FND_MESSAGE.Set_Name('WSH', 'WSH_CANCEL_MO_LINE');
3057 IF l_msg_count = 1 THEN
3058 --
3059 -- Debug Statements
3060 --
3061 IF l_debug_on THEN
3062 WSH_DEBUG_SV.logmsg(l_module_name, 'ERROR: '|| L_MSG_DATA );
3063 END IF;
3064 --
3065 FND_MESSAGE.Set_Token('ERROR_TEXT',l_msg_data);
3066 ELSE
3067 FOR l_index IN 1..l_msg_count LOOP
3068 l_msg_data := fnd_msg_pub.get;
3069 --
3070 -- Debug Statements
3071 --
3072 IF l_debug_on THEN
3073 WSH_DEBUG_SV.logmsg(l_module_name, 'ERROR: '|| L_MSG_DATA );
3074 END IF;
3075 --
3076 l_error_text := l_error_text || l_msg_data;
3077 END LOOP;
3078 FND_MESSAGE.Set_Token('ERROR_TEXT',l_error_text);
3079 l_error_text := '';
3080 END IF;
3081 x_return_status := FND_API.G_RET_STS_ERROR;
3082 WSH_UTIL_CORE.add_message (x_return_status,l_module_name);
3083 --
3084 -- Debug Statements
3085 --
3086 IF l_debug_on THEN
3087 WSH_DEBUG_SV.pop(l_module_name);
3088 END IF;
3089 --
3090 return;
3091 END IF;
3092 ELSE
3093
3094 -- HW OPMCONV - Removed forking the code
3095 --
3096 -- Debug Statements
3097 --
3098 IF l_debug_on THEN
3099 WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit INV_MO_CANCEL_PVT.CANCEL_MOVE_ORDER_LINE',WSH_DEBUG_SV.C_PROC_LEVEL);
3100 END IF;
3101 --
3102 INV_MO_Cancel_PVT.Cancel_Move_Order_Line(
3103 x_return_status => x_return_status
3104 ,x_msg_count => l_msg_count
3105 ,x_msg_data => l_msg_data
3106 ,p_line_id => p_move_order_line_id
3107 ,p_delete_reservations => 'N'
3108 ,p_txn_source_line_id => p_source_line_id
3109 ,p_delivery_detail_id => p_delivery_detail_id -- X-dock
3110 );
3111
3112 IF l_debug_on THEN
3113 WSH_DEBUG_SV.log(l_module_name,'INV_MO_Cancel_PVT.Cancel_Move_Order_Line x_return_status',x_return_status);
3114 END IF;
3115 --HW OPMCONV - Removed code forking
3116
3117 IF x_return_status = fnd_api.g_ret_sts_success THEN
3118 --
3119 -- Debug Statements
3120 --
3121 IF l_debug_on THEN
3122 WSH_DEBUG_SV.logmsg(l_module_name, 'CANCELLED MOLINE '||P_MOVE_ORDER_LINE_ID );
3123 END IF;
3124 --
3125 ELSE
3126 --
3127 -- Debug Statements
3128 --
3129 IF l_debug_on THEN
3130 WSH_DEBUG_SV.logmsg(l_module_name, 'ERROR IN INV_MO_CANCEL_PVT.CANCEL_MOVE_ORDER_LINE ' );
3131 END IF;
3132 --
3133 FND_MESSAGE.Set_Name('WSH', 'WSH_CANCEL_MO_LINE');
3134 IF l_msg_count = 1 THEN
3135 --
3136 -- Debug Statements
3137 --
3138 IF l_debug_on THEN
3139 WSH_DEBUG_SV.logmsg(l_module_name, 'ERROR: '|| L_MSG_DATA );
3140 END IF;
3141 --
3142 FND_MESSAGE.Set_Token('ERROR_TEXT',l_msg_data);
3143 ELSE
3144 FOR l_index IN 1..l_msg_count LOOP
3145 l_msg_data := fnd_msg_pub.get;
3146 --
3147 -- Debug Statements
3148 --
3149 IF l_debug_on THEN
3150 WSH_DEBUG_SV.logmsg(l_module_name, 'ERROR: '|| L_MSG_DATA );
3151 END IF;
3152 --
3153 l_error_text := l_error_text || l_msg_data;
3154 END LOOP;
3155 FND_MESSAGE.Set_Token('ERROR_TEXT',l_error_text);
3156 END IF;
3157 x_return_status := FND_API.G_RET_STS_ERROR;
3158 WSH_UTIL_CORE.add_message (x_return_status,l_module_name);
3159 --
3160 -- Debug Statements
3161 --
3162 IF l_debug_on THEN
3163 WSH_DEBUG_SV.pop(l_module_name);
3164 END IF;
3165 --
3166 return;
3167 END IF;
3168 END IF;
3169 END IF;
3170 END IF;
3171 IF p_released_status = 'Y' THEN
3172 IF p_ordered_quantity <> 0 THEN
3173
3174 l_delivery_detail_split_rec.delivery_detail_id := p_delivery_detail_id;
3175 l_delivery_detail_split_rec.released_status := p_released_status;
3176 l_delivery_detail_split_rec.move_order_line_id := p_move_order_line_id;
3177 l_delivery_detail_split_rec.organization_id := p_organization_id;
3178 l_delivery_detail_split_rec.inventory_item_id := p_inventory_item_id;
3179 l_delivery_detail_split_rec.subinventory := p_subinventory;
3180 l_delivery_detail_split_rec.revision := p_revision;
3181 l_delivery_detail_split_rec.lot_number := p_lot_number;
3182 l_delivery_detail_split_rec.locator_id := p_locator_id;
3183
3184 WSH_USA_INV_PVT.cancel_staged_reservation(
3185 p_source_code => p_source_code,
3186 p_source_header_id => p_source_header_id,
3187 p_source_line_id => p_source_line_id,
3188 p_delivery_detail_split_rec => l_delivery_detail_split_rec, -- New need to build up
3189 p_cancellation_quantity => l_change_reservation_quantity,
3190 p_cancellation_quantity2 => l_change_reservation_quantity2,
3191 x_return_status => x_return_status);
3192 IF l_debug_on THEN
3193 WSH_DEBUG_SV.log(l_module_name,'WSH_USA_INV_PVT.CANCEL_STAGED_RESERVATION',x_return_status);
3194 END IF;
3195
3196 IF x_return_status = fnd_api.g_ret_sts_success THEN
3197 --
3198 -- Debug Statements
3199 --
3200 IF l_debug_on THEN
3201 WSH_DEBUG_SV.logmsg(l_module_name, 'CANCELLED STAGED RESERVATION FOR DETAIL '||P_DELIVERY_DETAIL_ID ||'BY QTY '|| L_CHANGE_RESERVATION_QUANTITY );
3202 END IF;
3203 --
3204 ELSE
3205 --
3206 -- Debug Statements
3207 --
3208 IF l_debug_on THEN
3209 WSH_DEBUG_SV.logmsg(l_module_name, 'ERROR IN WSH_USA_INV_PVT.CANCEL_STAGED_RESERVATION ' );
3210 END IF;
3211 --
3212 x_return_status := FND_API.G_RET_STS_ERROR;
3213 WSH_UTIL_CORE.add_message (x_return_status,l_module_name);
3214 --
3215 -- Debug Statements
3216 --
3217 IF l_debug_on THEN
3218 WSH_DEBUG_SV.pop(l_module_name);
3219 END IF;
3220 --
3221 return;
3222 END IF;
3223 END IF;
3224 END IF;
3225
3226 -- Debug Statements
3227
3228 x_return_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS; --Bug# 3542353
3229 --
3230 -- HW OPMCONV - Removed code forking
3231
3232 IF p_ordered_quantity <> 0 THEN
3233
3234 l_serial_quantity := nvl(p_serial_quantity,0);
3235 --
3236 -- Debug Statements
3237 --
3238 IF l_debug_on THEN
3239 WSH_DEBUG_SV.logmsg(l_module_name, 'Serial Numbers deletion check -
3240 transaction_temp_id, serial_number, serial_quantity : '||
3241 p_transaction_temp_id||', '||p_serial_number||', '||l_serial_quantity);
3242 END IF;
3243 --
3244 IF ( p_transaction_temp_id IS NOT NULL ) AND ( l_serial_quantity > 0 ) AND
3245 ( l_serial_quantity > (p_requested_quantity - ABS(p_changed_detail_quantity)) ) THEN
3246
3247 IF (p_requested_quantity - ABS(p_changed_detail_quantity)) > 0 THEN
3248 l_serial_quantity := l_serial_quantity - (p_requested_quantity - ABS(p_changed_detail_quantity));
3249 END IF;
3250 --
3251 -- Debug Statements
3252 --
3253 IF l_debug_on THEN
3254 WSH_DEBUG_SV.logmsg(l_module_name, l_serial_quantity||' Serial Number(s) have to be deleted for dd '
3255 ||p_delivery_detail_id);
3256 END IF;
3257 --
3258 FND_MESSAGE.SET_NAME('WSH','WSH_STAGED_SERIAL_EXISTS');
3259 FND_MESSAGE.SET_TOKEN('SERIAL_QUANTITY',l_serial_quantity);
3260 FND_MESSAGE.SET_TOKEN('DELIVERY_DETAIL_ID',p_delivery_detail_id);
3261 x_return_status := FND_API.G_RET_STS_ERROR;
3262 return;
3263
3264 ELSIF (p_serial_number IS NOT NULL ) AND (p_requested_quantity - ABS(p_changed_detail_quantity) <= 0) THEN
3265 l_serial_quantity := 1;
3266 --
3267 -- Debug Statements
3268 --
3269 IF l_debug_on THEN
3270 WSH_DEBUG_SV.logmsg(l_module_name, l_serial_quantity||' Serial Number(s) have to be deleted for dd '
3271 ||p_delivery_detail_id);
3272 END IF;
3273 --
3274 FND_MESSAGE.SET_NAME('WSH','WSH_STAGED_SERIAL_EXISTS');
3275 FND_MESSAGE.SET_TOKEN('SERIAL_QUANTITY',l_serial_quantity);
3276 FND_MESSAGE.SET_TOKEN('DELIVERY_DETAIL_ID',p_delivery_detail_id);
3277 x_return_status := FND_API.G_RET_STS_ERROR;
3278 return;
3279 END IF;
3280
3281 ELSIF p_ordered_quantity = 0 AND (p_transaction_temp_id IS NOT NULL OR p_serial_number IS NOT NULL ) THEN
3282 --
3283 IF l_debug_on THEN
3284 WSH_DEBUG_SV.logmsg(l_module_name,'Complete Order Qty Cancelation - all serial numbers will be unmarked',WSH_DEBUG_SV.C_PROC_LEVEL);
3285 WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_USA_INV_PVT.UPDATE_SERIAL_NUMBERS',WSH_DEBUG_SV.C_PROC_LEVEL);
3286 END IF;
3287 --
3288 WSH_USA_INV_PVT.update_serial_numbers(
3289 p_delivery_detail_id => p_delivery_detail_id,
3290 p_serial_number => p_serial_number,
3291 p_transaction_temp_id => p_transaction_temp_id,
3292 x_return_status => x_return_status);
3293 IF l_debug_on THEN
3294 WSH_DEBUG_SV.log(l_module_name,'Update_Serial_Numbers x_return_status',x_return_status);
3295 END IF;
3296 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
3297 x_return_status := FND_API.G_RET_STS_ERROR;
3298 WSH_UTIL_CORE.add_message (x_return_status,l_module_name);
3299 --
3300 -- Debug Statements
3301 --
3302 IF l_debug_on THEN
3303 WSH_DEBUG_SV.pop(l_module_name);
3304 END IF;
3305 --
3306 RETURN;
3307 END IF;
3308 END IF;
3309
3310 /* Bug 2310456 warning handling */
3311
3312 IF x_return_status <> WSH_UTIL_CORE.G_RET_STS_SUCCESS THEN
3313 --
3314 -- Debug Statements
3315 --
3316 IF l_debug_on THEN
3317 WSH_DEBUG_SV.logmsg(l_module_name, 'ERROR IN WSH_USA_INV_PVT.UPDATE_SERIAL_NUMBERS ' );
3318 END IF;
3319 --
3320 x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
3321 WSH_UTIL_CORE.add_message (x_return_status,l_module_name);
3322 --
3323 -- Debug Statements
3324 --
3325 IF l_debug_on THEN
3326 WSH_DEBUG_SV.pop(l_module_name);
3327 END IF;
3328 --
3329 return;
3330 ELSIF l_num_warn > 0 THEN
3331 -- in this API we treat warning as success as of now
3332 x_return_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
3333 --x_return_status := WSH_UTIL_CORE.G_RET_STS_WARNING;
3334 --
3335 -- Debug Statements
3336 --
3337 IF l_debug_on THEN
3338 WSH_DEBUG_SV.pop(l_module_name);
3339 END IF;
3340 --
3341 return;
3342 END IF;
3343 --
3344 -- Debug Statements
3345 --
3346 IF l_debug_on THEN
3347 WSH_DEBUG_SV.pop(l_module_name);
3348 END IF;
3349 --
3350 EXCEPTION
3351 WHEN others THEN
3352 IF c_check_del_assign%ISOPEN THEN
3353 CLOSE c_check_del_assign;
3354 END IF;
3355 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
3356 --
3357 -- Debug Statements
3358 --
3359 IF l_debug_on THEN
3360 WSH_DEBUG_SV.logmsg(l_module_name, SQLCODE || ' : ' || SQLERRM );
3361 END IF;
3362 --
3363 wsh_util_core.default_handler('WSH_USA_QUANTITY_PVT.CANCEL_QUANTITY',l_module_name);
3364 --
3365 -- Debug Statements
3366 --
3367 IF l_debug_on THEN
3368 WSH_DEBUG_SV.logmsg(l_module_name,'Unexpected error has occured. Oracle error message is '|| SQLERRM,WSH_DEBUG_SV.C_UNEXPEC_ERR_LEVEL);
3369 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:OTHERS');
3370 END IF;
3371 --
3372 END cancel_quantity;
3373
3374
3375 END WSH_USA_QUANTITY_PVT;