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