[Home] [Help]
PACKAGE BODY: APPS.WSH_SHIP_CONFIRM_ACTIONS2
Source
1 PACKAGE BODY WSH_SHIP_CONFIRM_ACTIONS2 as
2 /* $Header: WSHDDSPB.pls 120.14.12020000.6 2013/04/19 13:26:19 skanduku ship $ */
3
4 --
5 --Function: part_of_ship_set
6 --Parameters: p_source_line_id
7 --Description: This function returns a boolean number that indicates
8 -- if the order line is part of a ship set
9 --
10 G_PKG_NAME CONSTANT VARCHAR2(50) := 'WSH_SHIP_CONFIRM_ACTIONS2';
11 --
12 FUNCTION Part_Of_Ship_Set(p_source_line_id number) RETURN BOOLEAN is
13 l_count number;
14 --
15 l_debug_on BOOLEAN;
16 --
17 l_module_name CONSTANT VARCHAR2(100) := 'wsh.plsql.' || G_PKG_NAME || '.' || 'PART_OF_SHIP_SET';
18 --
19 BEGIN
20 --
21 -- Debug Statements
22 --
23 --
24 l_debug_on := WSH_DEBUG_INTERFACE.g_debug;
25 --
26 IF l_debug_on IS NULL
27 THEN
28 l_debug_on := WSH_DEBUG_SV.is_debug_enabled;
29 END IF;
30 --
31 IF l_debug_on THEN
32 WSH_DEBUG_SV.push(l_module_name);
33 --
34 WSH_DEBUG_SV.log(l_module_name,'P_SOURCE_LINE_ID',P_SOURCE_LINE_ID);
35 END IF;
36 --
37 select count(*) into l_count
38 from wsh_delivery_details
39 where ship_set_id is not null
40 and source_line_id = p_source_line_id
41 and NVL(line_direction, 'O') IN ('O', 'IO') -- J Inbound Logistics Changes jckwok
42 and NVL(container_flag,'N') = 'N';
43
44 if (l_count > 0) then
45 --
46 -- Debug Statements
47 --
48 IF l_debug_on THEN
49 WSH_DEBUG_SV.pop(l_module_name,'RETURN-TRUE');
50 END IF;
51 --
52 return TRUE;
53 else
54 IF l_debug_on THEN
55 WSH_DEBUG_SV.pop(l_module_name,'RETURN-TRUE');
56 END IF;
57 return FALSE;
58 end if;
59 END part_of_ship_set;
60
61 procedure print_reservations ( p_sales_order_id in number )is
62 cursor c_reservations ( c_sales_order_id number ) is
63 select
64 RES.RESERVATION_ID RESERV_ID,
65 decode(RES.SHIP_READY_FLAG,1,'1=Released',2,'2=Submitted',to_char(RES.SHIP_READY_FLAG))
66 SHIP_READY,
67 RES.DEMAND_SOURCE_HEADER_ID DS_HEADER_ID,
68 RES.DEMAND_SOURCE_LINE_ID DS_LINE_ID,
69 RES.DEMAND_SOURCE_DELIVERY DS_DELIVERY,
70 to_char(LIN.line_number)||
71 '.'||to_char(LIN.shipment_number) ||
72 decode(LIN.option_number,NULL,NULL,'.'||to_char(LIN.option_number)) LINE,
73 RES.INVENTORY_ITEM_ID ITEM_ID,
74 RES.PRIMARY_RESERVATION_QUANTITY RES_QTY,
75 RES.DETAILED_QUANTITY DET_QTY,
76 RES.REQUIREMENT_DATE REQUIRD_D,
77 RES.DEMAND_SOURCE_TYPE_ID DS_TYPE,
78 RES.ORGANIZATION_ID ORG_ID,
79 RES.SUBINVENTORY_CODE SUBINV,
80 RES.SUPPLY_SOURCE_HEADER_ID SS_HEADER_ID,
81 RES.SUPPLY_SOURCE_LINE_DETAIL SS_SOURCE_LINE_DET,
82 RES.SUPPLY_SOURCE_LINE_ID SS_SOURCE_LINE,
83 RES.AUTODETAIL_GROUP_ID AUTODET_GRP_ID,
84 RES.AUTO_DETAILED AUTODET
85 from
86 MTL_RESERVATIONS RES,
87 OE_ORDER_LINES_ALL LIN --R12:MOAC use base table
88 where
89 RES.DEMAND_SOURCE_HEADER_ID = c_sales_order_id
90 and RES.DEMAND_SOURCE_TYPE_ID in (2,8,9,21,22)
91 and RES.DEMAND_SOURCE_LINE_ID = LIN.LINE_ID(+)
92 order by
93 NVL(LIN.TOP_MODEL_LINE_ID, LIN.LINE_ID),
94 NVL(LIN.ATO_LINE_ID, LIN.LINE_ID),
95 NVL(LIN.SORT_ORDER, '0000'),
96 NVL(LIN.LINK_TO_LINE_ID, LIN.LINE_ID),
97 NVL(LIN.SOURCE_DOCUMENT_LINE_ID, LIN.LINE_ID),
98 LIN.LINE_ID,
99 RES.RESERVATION_ID;
100
101 l_reservations c_reservations%ROWTYPE;
102
103 --
104 l_debug_on BOOLEAN;
105 --
106 l_module_name CONSTANT VARCHAR2(100) := 'wsh.plsql.' || G_PKG_NAME || '.' || 'PRINT_RESERVATIONS';
107 --
108 begin
109 --
110 -- Debug Statements
111 --
112 --
113 l_debug_on := WSH_DEBUG_INTERFACE.g_debug;
114 --
115 IF l_debug_on IS NULL
116 THEN
117 l_debug_on := WSH_DEBUG_SV.is_debug_enabled;
118 END IF;
119 --
120 IF l_debug_on THEN
121 WSH_DEBUG_SV.push(l_module_name);
122 --
123 WSH_DEBUG_SV.log(l_module_name,'P_SALES_ORDER_ID',P_SALES_ORDER_ID);
124 END IF;
125 --
126 open c_reservations ( p_sales_order_id );
127 loop
128 fetch c_reservations into l_reservations ;
129 --
130 -- Debug Statements
131 --
132 IF l_debug_on THEN
133 WSH_DEBUG_SV.logmsg(l_module_name, 'RESERVATIONS FOR SALES_ORDER_ID ' || TO_CHAR ( P_SALES_ORDER_ID ) );
134 WSH_DEBUG_SV.logmsg(l_module_name, '================================================' );
135 WSH_DEBUG_SV.logmsg(l_module_name, 'RESERV_ID : ' || TO_CHAR ( L_RESERVATIONS.RESERV_ID ) );
136 WSH_DEBUG_SV.logmsg(l_module_name, 'SHIP_READY : ' || L_RESERVATIONS.SHIP_READY );
137 WSH_DEBUG_SV.logmsg(l_module_name, 'DS_HEADER_ID : ' || TO_CHAR ( L_RESERVATIONS.DS_HEADER_ID ) );
138 WSH_DEBUG_SV.logmsg(l_module_name, 'DS_LINE_ID : ' || TO_CHAR ( L_RESERVATIONS.DS_LINE_ID ) );
139 WSH_DEBUG_SV.logmsg(l_module_name, 'DS_DELIVERY: ' || TO_CHAR ( L_RESERVATIONS.DS_DELIVERY ) );
140 WSH_DEBUG_SV.logmsg(l_module_name, 'LINE : ' || L_RESERVATIONS.LINE );
141 WSH_DEBUG_SV.logmsg(l_module_name, 'ITEM_ID : ' || TO_CHAR ( L_RESERVATIONS.ITEM_ID ) );
142 WSH_DEBUG_SV.logmsg(l_module_name, 'RES_QTY : ' || TO_CHAR ( L_RESERVATIONS.RES_QTY ) );
143 WSH_DEBUG_SV.logmsg(l_module_name, 'DET_QTY : ' || TO_CHAR ( L_RESERVATIONS.DET_QTY ) );
144 WSH_DEBUG_SV.logmsg(l_module_name, 'REQUIRD_D : ' || L_RESERVATIONS.REQUIRD_D );
145 WSH_DEBUG_SV.logmsg(l_module_name, 'DS_TYPE : ' || TO_CHAR ( L_RESERVATIONS.DS_TYPE ) );
146 WSH_DEBUG_SV.logmsg(l_module_name, 'ORG_ID : ' || TO_CHAR ( L_RESERVATIONS.ORG_ID ) );
147 WSH_DEBUG_SV.logmsg(l_module_name, 'SUBINV : ' || L_RESERVATIONS.SUBINV );
148 WSH_DEBUG_SV.logmsg(l_module_name, 'SS_HEADER_ID : ' || TO_CHAR ( L_RESERVATIONS.SS_HEADER_ID ) );
149 WSH_DEBUG_SV.logmsg(l_module_name, 'SS_SOURCE_LINE_DET- ' || TO_CHAR ( L_RESERVATIONS.SS_SOURCE_LINE_DET ) );
150 WSH_DEBUG_SV.logmsg(l_module_name, 'SS_SOURCE_LINE : ' || TO_CHAR ( L_RESERVATIONS.SS_SOURCE_LINE ) );
151 WSH_DEBUG_SV.logmsg(l_module_name, 'AUTODET_GRP_ID : ' || TO_CHAR ( L_RESERVATIONS.AUTODET_GRP_ID ) );
152 WSH_DEBUG_SV.logmsg(l_module_name, 'AUTODET : ' || TO_CHAR ( L_RESERVATIONS.AUTODET ) );
153 END IF;
154
155 EXIT WHEN c_reservations%NOTFOUND;
156 end loop ;
157 --
158 -- Debug Statements
159 --
160 IF l_debug_on THEN
161 WSH_DEBUG_SV.pop(l_module_name);
162 END IF;
163 --
164 end print_reservations ;
165
166 --Procedure: Get_Line_Total_Shp_Qty
167 --Parameters: p_source_line_id
168 -- p_delivery_id
169 -- x_line_shp_qty
170 -- x_return_status
171 --Description: This procedure calculates the total shippped quantity
172 -- for a order line/source line in a specified delivery
173 -- OPM KYH 12/SEP/00 START - add x_line_shp_qty2 to out params
174 -- ===========================================================
175 PROCEDURE Get_Line_Total_Shp_Qty(
176 p_stop_id in number ,
177 p_source_line_id in number,
178 x_line_shp_qty out NOCOPY number ,
179 x_line_shp_qty2 out NOCOPY number,
180 x_return_status out NOCOPY varchar2) is
181 cursor c_shipped_rec is
182 SELECT shipped_quantity
183 ,shipped_quantity2 -- OPM KYH 12/SEP/00
184 , dd.source_line_id
185 , dl.status_code
186 FROM wsh_delivery_Details dd,
187 wsh_delivery_assignments_v da ,
188 wsh_delivery_legs dg,
189 wsh_new_deliveries dl,
190 wsh_trip_stops st
191 WHERE st.stop_id = dg.pick_up_stop_id AND
192 st.stop_id = p_stop_id AND
193 st.stop_location_id = dl.initial_pickup_location_id AND
194 dg.delivery_id = dl.delivery_id AND
195 dl.delivery_id = da.delivery_id AND
196 da.delivery_id IS NOT NULL AND
197 da.delivery_detail_id = dd.delivery_detail_id
198 and nvl ( dd.oe_interfaced_flag , 'N' ) <> 'Y'
199 and nvl ( dd.inv_interfaced_flag, 'N' ) IN ( 'Y','X')
200 and dd.source_line_id = p_source_line_id
201 and dd.source_code = 'OE'
202 and dd.container_flag = 'N' ;
203 shipped_rec c_shipped_rec%ROWTYPE;
204
205 NOT_ASSIGNED_TO_DEL_ERROR exception;
206 l_counter number;
207 l_error_code number;
208 l_error_text varchar2(2000);
209 --
210 l_debug_on BOOLEAN;
211 --
212 l_module_name CONSTANT VARCHAR2(100) := 'wsh.plsql.' || G_PKG_NAME || '.' || 'GET_LINE_TOTAL_SHP_QTY';
213 --
214 begin
215 --
216 -- Debug Statements
217 --
218 --
219 l_debug_on := WSH_DEBUG_INTERFACE.g_debug;
220 --
221 IF l_debug_on IS NULL
222 THEN
223 l_debug_on := WSH_DEBUG_SV.is_debug_enabled;
224 END IF;
225 --
226 IF l_debug_on THEN
227 WSH_DEBUG_SV.push(l_module_name);
228 --
229 WSH_DEBUG_SV.log(l_module_name,'P_STOP_ID',P_STOP_ID);
230 WSH_DEBUG_SV.log(l_module_name,'P_SOURCE_LINE_ID',P_SOURCE_LINE_ID);
231 END IF;
232 --
233 WSH_UTIL_CORE.Enable_Concurrent_Log_Print;
234 x_return_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
235 x_line_shp_qty := 0;
236 x_line_shp_qty2 := 0; -- OPM KYH 12/SEP/00
237 open c_shipped_rec;
238 LOOP
239 fetch c_shipped_rec into shipped_rec;
240 exit when c_shipped_rec%NOTFOUND;
241 l_counter := l_counter + 1;
242 x_line_shp_qty := x_line_shp_qty + shipped_rec.shipped_quantity;
243 -- OPM KYH 12/SEP/00 calculate qty2 for dual control scenarios
244 -- ===========================================================
245 if shipped_rec.shipped_quantity2 is NOT NULL THEN
246 x_line_shp_qty2 := x_line_shp_qty2 + shipped_rec.shipped_quantity2;
247 ELSE
248 x_line_shp_qty2 := NULL;
249 end if;
250 -- OPM KYH 12/SEP/00 END of CHANGES
251 END LOOP;
252 if (l_counter = 0) then
253 raise NOT_ASSIGNED_TO_DEL_ERROR;
254 end if;
255 --
256 -- Debug Statements
257 --
258 IF l_debug_on THEN
259 WSH_DEBUG_SV.pop(l_module_name);
260 END IF;
261 --
262 EXCEPTION
263 when NOT_ASSIGNED_TO_DEL_ERROR then
264 x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
265 FND_MESSAGE.SET_NAME('WSH', 'WSH_DET_DETAIL_NOT_ASSIGNED');
266 WSH_UTIL_CORE.ADD_MESSAGE(x_return_status,l_module_name);
267 --
268 -- Debug Statements
269 --
270 IF l_debug_on THEN
271 WSH_DEBUG_SV.logmsg(l_module_name,'NOT_ASSIGNED_TO_DEL_ERROR exception has occured.',WSH_DEBUG_SV.C_EXCEP_LEVEL);
272 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:NOT_ASSIGNED_TO_DEL_ERROR');
273 END IF;
274 --
275 when others then
276 x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR;
277 l_error_code := SQLCODE;
278 l_error_text := SQLERRM;
279 --
280 WSH_UTIL_CORE.default_handler('WSH_SHIP_CONFIRM_ACTIONS2.Ship_zero_quantity',l_module_name);
281 --
282 -- Debug Statements
283 --
284 IF l_debug_on THEN
285 WSH_DEBUG_SV.logmsg(l_module_name,'Unexpected error has occured. Oracle error message is '|| SQLERRM,WSH_DEBUG_SV.C_UNEXPEC_ERR_LEVEL);
286 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:OTHERS');
287 END IF;
288 --
289 END Get_Line_Total_Shp_Qty;
290
291 -- THIS PROCEDURE IS OBSOLETE
292
293 PROCEDURE Ship_Zero_Quantity(
294 p_source_line_id IN NUMBER
295 , x_return_status OUT NOCOPY VARCHAR2
296 )
297 IS
298
299 BEGIN
300 x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
301
302 END Ship_Zero_Quantity;
303
304 /*
305 split each p_detail_ids into a new records with p_bo_qtys as requested_quantity.
306 The new dd_id is marked as 'B' and stored in x_out_rows
307 All the enteries in x_out_rows which are already assigned to a Delivery are stored
308 in l_unassign_dds and unassigned and unpacked. If the container becomes empty because of unassign
309 then the container is also unpacked/unassigned. This logic is embedded in unassign_unpack_empty_cont.
310 */
311 -- bug# 6908504 (replenishment project):
312 -- Added a new parameter p_bo_source. This parameter can have two values 'SHIP' and 'PICK'.
313 -- For all replenishment functionality cases please call the API with p_bo_source code as 'PICK'.
314 -- This particulary parameter has been added to mimic the back order funcationaly which is available in
315 -- WSH_USA_INV_PVT.backorder_delivery_detail while pick releasing. Default value for this parameter
316 -- is 'SHIP' ( no change in the existing functionality when called without this parameter value).
317
318 PROCEDURE Backorder(
319 p_detail_ids IN WSH_UTIL_CORE.Id_Tab_Type,
320 p_line_ids IN WSH_UTIL_CORE.Id_Tab_Type , -- Consolidation of BO Delivery Details project
321 p_bo_qtys IN WSH_UTIL_CORE.Id_Tab_Type,
322 p_req_qtys IN WSH_UTIL_CORE.Id_Tab_Type,
323 p_bo_qtys2 IN WSH_UTIL_CORE.Id_Tab_Type,
324 p_overpick_qtys IN WSH_UTIL_CORE.Id_Tab_Type,
325 p_overpick_qtys2 IN WSH_UTIL_CORE.Id_Tab_Type,
326 p_sources IN WSH_UTIL_CORE.Column_Tab_Type, -- RTV BO Cons Changes
327 p_bo_mode IN VARCHAR2,
328 p_bo_source IN VARCHAR2 DEFAULT 'SHIP',
329 x_out_rows OUT NOCOPY WSH_UTIL_CORE.Id_Tab_Type,
330 x_cons_flags OUT NOCOPY WSH_UTIL_CORE.Column_Tab_Type, -- Consolidation of BO Delivery Details project
331 x_return_status OUT NOCOPY VARCHAR2 )
332 IS
333 CURSOR c_detail(x_detail_id NUMBER) IS
334 --Changed for BUG#3330869
335 --SELECT *
336 SELECT source_line_id,
337 source_code,
338 picked_quantity,
339 delivery_detail_id,
340 released_status,
341 pickable_flag,
342 organization_id,
343 inventory_item_id,
344 requested_quantity,
345 -- muom
346 picked_quantity2,
347 requested_quantity2,
348 serial_number,
349 transaction_temp_id,
350 subinventory,
351 client_id -- LSP PROJECT : Required to check whether order is for LSP
352 FROM wsh_delivery_details
353 WHERE delivery_detail_id = x_detail_id AND
354 NVL(container_flag, 'N') = 'N';
355 l_detail_rec c_detail%ROWTYPE;
356
357 l_delivery_id NUMBER;
358 l_return_status VARCHAR2(30);
359 l_exception_exist VARCHAR2(1);
360 l_user_id NUMBER := NULL;
361 l_login_id NUMBER := NULL;
362 l_trohdr_rec INV_MOVE_ORDER_PUB.Trohdr_Rec_Type;
363 l_trohdr_val_rec INV_MOVE_ORDER_PUB.Trohdr_Val_Rec_Type;
364 l_trolin_tbl INV_MOVE_ORDER_PUB.Trolin_Tbl_Type;
365 l_trolin_val_tbl INV_MOVE_ORDER_PUB.Trolin_Val_Tbl_Type;
366 l_cr_hdr_return_status varchar2(30) := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
367 l_cr_hdr_message varchar2(4000) := NULL;
368 l_cr_hdr_msg_count number:= NULL;
369 l_cr_hdr_msg_data varchar2(4000) := NULL;
370 l_cr_ln_return_status varchar2(30) := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
371 l_cr_ln_msg_count number := NULL;
372 l_cr_ln_msg_data varchar2(4000) := NULL;
373 l_cr_ln_message varchar2(4000) := NULL;
374 l_sales_order_id NUMBER := NULL;
375 l_date DATE := SYSDATE;
376
377 --Added for Standalone project Changes
378 l_standalone_mode VARCHAR2(1);
379
380 -- fabdi start : SHIPPING PIECE 12/09/2000
381
382 l_message_count NUMBER;
383 l_message_data VARCHAR2(3000);
384
385 l_to_subinventory VARCHAR2(10);
386 l_to_locator NUMBER;
387 l_commit VARCHAR2(1) := FND_API.G_FALSE;
388 l_default_to_sub VARCHAR2(10);
389 l_default_to_loc NUMBER;
390 l_message VARCHAR2(2000);
391
392 -- hverddin : Begin of OPM Backorder Changes 30-OCT-00
393
394 l_batch_id NUMBER;
395 l_request_number VARCHAR(30);
396 l_wf_rs VARCHAR2(1); --Pick To POD WF Project
397
398 -- hverddin : End of OPM Backorder Changes 30-OCT-00
399
400
401 -- fabdi end : SHIPPING PIECE 12/09/2000
402
403 i NUMBER;
404 /* Bug#: 2026895 Added the second decode statement */
405
406 /* CURSOR c_assigned(c_delivery_detail_id number) is
407 SELECT decode ( delivery_id , null , 'N' , 'Y' ),
408 decode( delivery_id,null,-9999999,delivery_id)
409 FROM wsh_delivery_assignments_v
410 WHERE delivery_Detail_id = c_delivery_detail_id ; */
411
412 -- bug# 6908504 (replenishment project), Changed the cursor definition. Now it returns planned_flag value from WND table.
413 -- muom
414 CURSOR c_assigned(c_delivery_detail_id number) is
415 SELECT wnd.delivery_id,
416 wnd.planned_flag,
417 wdd.source_code,
418 wdd.source_line_id
419 FROM wsh_new_deliveries wnd,
420 wsh_delivery_details wdd,
421 wsh_delivery_assignments_v wda
422 WHERE wdd.delivery_detail_id = c_delivery_detail_id
423 AND wda.delivery_id = wnd.delivery_id (+)
424 AND wda.delivery_detail_id = wdd.delivery_detail_id ;
425
426
427 l_assigned_flag VARCHAR2(1);
428 /*Bug#:2026895*/
429 l_valid_flag BOOLEAN;
430
431 backorder_error EXCEPTION;
432 -- HW 3457369
433 -- HW OPMCONV. Removed local variables
434
435 l_reserved_flag VARCHAR2(1);
436 l_bo_detail_id NUMBER;
437
438 l_bo_qty NUMBER ;
439 l_bo_qty2 NUMBER ;
440 l_unassign_dds WSH_UTIL_CORE.Id_Tab_Type ;
441 l_unassign_dds_tmp WSH_UTIL_CORE.Id_Tab_Type ; -- bug 7460785
442 l_delete_dds WSH_UTIL_CORE.Id_Tab_Type ; -- to delete overpicked delivery lines
443 l_inv_controls_rec WSH_DELIVERY_DETAILS_INV.inv_control_flag_rec;
444
445 invalid_source_code EXCEPTION; /*Bug 2096052- added for OKE */
446 no_backorder_full EXCEPTION; /*Bug 2399729- added for third party warehouse shipment line */
447 l_backorder_all VARCHAR2(1) := 'N'; /* Bug 2399729 */
448 -- additional variables for bug 2056874
449 new_det_wt_vol_failed exception;
450 l_split_weight number;
451 l_split_volume number;
452 -- bug 2056874
453
454 l_num_warn NUMBER := 0;
455 l_num_err NUMBER := 0;
456
457 l_subinventory VARCHAR2(10) := NULL;
458
459 -- bug 2730685: need to know if we are backordering
460 -- an unshipped, overpicked line packed in container
461 l_new_picked_quantity NUMBER;
462 -- muom
463 l_new_picked_quantity2 NUMBER;
464 l_fulfillment_base WSH_UTIL_CORE.Column_Tab_Type;
465
466 -- Newly added variables for Consolidation of BO Delivery Details project
467 --
468 l_global_param_rec WSH_SHIPPING_PARAMS_PVT.Global_Parameters_Rec_Typ;
469 l_line_ids WSH_UTIL_CORE.Id_Tab_Type ;
470 l_detail_ids WSH_UTIL_CORE.Id_Tab_Type ;
471 -- HW 3457369
472 l_detail_ids_OPM WSH_UTIL_CORE.Id_Tab_Type ;
473 l_req_qtys WSH_UTIL_CORE.Id_Tab_Type ;
474 l_bo_qtys WSH_UTIL_CORE.Id_Tab_Type ;
475 l_overpick_qtys WSH_UTIL_CORE.Id_Tab_Type ;
476 -- HW OPM BUG#:3121616 added qty2s
477 -- muom
478 l_req_qty2s WSH_UTIL_CORE.Id_Tab_Type ;
479 l_overpick_qty2s WSH_UTIL_CORE.Id_Tab_Type ;
480 l_bo_qty2s WSH_UTIL_CORE.Id_Tab_Type ;
481 -- end of 3121616
482 l_delivery_ids WSH_UTIL_CORE.Id_Tab_Type ;
483 l_cons_dd_ids WSH_UTIL_CORE.Id_Tab_Type ; --Stores the dd_ids returned by Consolidate_Source_Line
484 l_remain_bo_qtys WSH_UTIL_CORE.Id_Tab_Type ; --Stores the remaining bo qty returned by Consolidate_Delivery_details
485 -- HW OPM BUG#:3121616 added qty2s
486 l_remain_bo_qty2s WSH_UTIL_CORE.Id_Tab_Type ; --Stores the remaining bo qty2 returned by Consolidate_Delivery_details
487 -- end of 3121616
488 l_cons_source_line_rec_tab WSH_DELIVERY_DETAILS_ACTIONS.Cons_Source_Line_Rec_Tab;
489 l_dd_rec_tab WSH_DELIVERY_DETAILS_ACTIONS.Cons_Source_Line_Rec_Tab;
490 l_cons_dd_rec_tab WSH_DELIVERY_DETAILS_ACTIONS.Cons_Source_Line_Rec_Tab;
491 k NUMBER;
492
493 l_dd_txn_id NUMBER; --DBI
494 l_txn_return_status VARCHAR2(1); --DBI
495 --
496
497 -- HW 3457369
498 CURSOR find_org_id (l_delivery_detail_id NUMBER ) IS
499 SELECT organization_id from wsh_delivery_details
500 WHERE delivery_detail_id = l_delivery_detail_id
501 AND NVL(container_flag, 'N') = 'N';
502
503 l_organization_id NUMBER;
504 -- end of 3457369
505
506 --Begin OPM Bug 3561937
507 CURSOR get_pref_grade(wdd_id NUMBER) IS
508 SELECT oelines.preferred_grade
509 FROM oe_order_lines_all oelines, wsh_delivery_details wdd
510 WHERE wdd.delivery_detail_id = wdd_id
511 AND wdd.source_code = 'OE'
512 AND wdd.source_line_id = oelines.line_id;
513
514 -- HW OPMCONV changed size of grade to 150
515 l_oeline_pref_grade VARCHAR2(150) := NULL;
516 --End OPM Bug 3561937
517
518 l_detail_tab WSH_UTIL_CORE.id_tab_type; -- DBI Project
519 l_dbi_rs VARCHAR2(1); -- Return Status from DBI API
520
521 --
522 l_debug_on BOOLEAN;
523 --
524 l_module_name CONSTANT VARCHAR2(100) := 'wsh.plsql.' || G_PKG_NAME || '.' || 'BACKORDER';
525 --Bugfix 4070732
526 l_api_session_name CONSTANT VARCHAR2(150) := G_PKG_NAME ||'.' || l_module_name;
527 l_reset_flags BOOLEAN;
528 --
529 -- K LPN CONV. rv
530 l_lpn_in_sync_comm_rec WSH_GLBL_VAR_STRCT_GRP.lpn_sync_comm_in_rec_type;
531 l_lpn_out_sync_comm_rec WSH_GLBL_VAR_STRCT_GRP.lpn_sync_comm_out_rec_type;
532 l_msg_count NUMBER;
533 l_msg_data VARCHAR2(32767);
534 e_return_excp EXCEPTION;
535 -- K LPN CONV. rv
536 l_item_info WSH_DELIVERY_DETAILS_INV.mtl_system_items_rec;
537 --
538 --
539 l_unassign_dd_flag VARCHAR2(1) := 'N';
540 l_planned_flag VARCHAR2(1); -- bug# 6908504 (replenishment project)
541 -- muom
542 l_source_code VARCHAR2(30);
543 l_source_line_id NUMBER;
544 BEGIN
545 IF WSH_UTIL_CORE.G_START_OF_SESSION_API is null THEN --Bugfix 4070732
546 WSH_UTIL_CORE.G_START_OF_SESSION_API := l_api_session_name;
547 WSH_UTIL_CORE.G_CALL_FTE_LOAD_TENDER_API := FALSE;
548 END IF;
549 --
550 -- Debug Statements
551 --
552 --
553 l_debug_on := WSH_DEBUG_INTERFACE.g_debug;
554 --
555 IF l_debug_on IS NULL
556 THEN
557 l_debug_on := WSH_DEBUG_SV.is_debug_enabled;
558 END IF;
559 --
560 -- Standalone project Changes Start
561 l_standalone_mode := WMS_DEPLOY.wms_deployment_mode;
562 -- Standalone project Changes End
563 IF l_debug_on THEN
564 WSH_DEBUG_SV.push(l_module_name);
565 --
566 WSH_DEBUG_SV.log(l_module_name,'P_BO_MODE',P_BO_MODE);
567 WSH_DEBUG_SV.log(l_module_name,'P_BO_SOURCE',P_BO_SOURCE);
568 WSH_DEBUG_SV.log(l_module_name,'l_standalone_mode',l_standalone_mode);
569 END IF;
570 --
571 SAVEPOINT before_backorder;
572
573 x_return_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
574 l_return_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
575
576 -- Consolidation of BO Delivery Details project
577 -- Begin
578 l_line_ids.DELETE;
579 l_detail_ids := p_detail_ids;
580 l_detail_ids_opm := p_detail_ids;
581 l_req_qtys := p_req_qtys;
582 l_bo_qtys := p_bo_qtys;
583 l_overpick_qtys := p_overpick_qtys; -- Bug#3263952
584 -- HW OPM BUG#:3121616 added qty2s
585 -- muom
586 l_req_qty2s := p_bo_qtys2;
587 l_overpick_qty2s := p_overpick_qtys2;
588 l_bo_qty2s := p_bo_qtys2;
589 -- end of 3121616
590 --
591 -- Debug Statements
592 --
593 IF l_debug_on THEN
594 WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_SHIPPING_PARAMS_PVT.Get_Global_Parameters',WSH_DEBUG_SV.C_PROC_LEVEL);
595 END IF;
596 --
597 WSH_SHIPPING_PARAMS_PVT.Get_Global_Parameters(l_global_param_rec, l_return_status);
598 IF l_return_status IN (WSH_UTIL_CORE.G_RET_STS_ERROR, WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR) THEN
599 wsh_util_core.add_message(l_return_status,'WSH_DELIVERY_DETAILS_ACTIONS.Consolidate_Delivery_Details');
600 x_return_status := l_return_status;
601 RAISE backorder_error;
602 END IF;
603
604 IF l_debug_on THEN
605 WSH_DEBUG_SV.logmsg(l_module_name,'Back Order Consolidation Flag is set as '||l_global_param_rec.consolidate_bo_lines, WSH_DEBUG_SV.C_PROC_LEVEL);
606 END IF;
607
608 IF (l_global_param_rec.consolidate_bo_lines = 'Y' AND p_line_ids.COUNT > 1 ) THEN
609 --{
610 -- Get the values into local pl/sql tables
611 FOR i IN p_detail_ids.FIRST .. p_detail_ids.LAST LOOP --{
612 l_dd_rec_tab(l_dd_rec_tab.count+1).delivery_detail_id := p_detail_ids(i);
613 l_dd_rec_tab(l_dd_rec_tab.count).source_line_id := p_line_ids(i);
614 l_dd_rec_tab(l_dd_rec_tab.count).req_qty := p_req_qtys(i);
615 l_dd_rec_tab(l_dd_rec_tab.count).bo_qty := p_bo_qtys(i);
616 l_dd_rec_tab(l_dd_rec_tab.count).overpick_qty := p_overpick_qtys(i); -- Bug#3263952
617 -- muom
618 l_dd_rec_tab(l_dd_rec_tab.count).req_qty2 := p_bo_qtys2(i);
619 l_dd_rec_tab(l_dd_rec_tab.count).overpick_qty2 := p_overpick_qtys2(i);
620 -- HW OPM BUG#:3121616 added qty2s
621 l_dd_rec_tab(l_dd_rec_tab.count).bo_qty2 := p_bo_qtys2(i);
622 -- RTV BO Cons Changes
623 l_dd_rec_tab(l_dd_rec_tab.count).source := p_sources(i);
624
625 -- muom
626 IF p_sources(i) is not null and p_line_ids(i) is not null THEN
627 l_dd_rec_tab(l_dd_rec_tab.count).fulfillment_base := WSH_UTIL_CORE.Get_Line_Fulfillment_Base(p_sources(i), p_line_ids(i));
628 ELSE
629 l_dd_rec_tab(l_dd_rec_tab.count).fulfillment_base := 'P';
630 END IF;
631 IF l_debug_on THEN
632 WSH_DEBUG_SV.logmsg(l_module_name,'** Delivery Detail Id '||p_detail_ids(i)||' Fulfillment Base '||l_dd_rec_tab(l_dd_rec_tab.count).fulfillment_base);
633 END IF;
634 -- end of 3121616
635 END LOOP; --}
636 --
637 -- Debug Statements
638 --
639 IF l_debug_on THEN
640 WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_DELIVERY_DETAILS_ACTIONS.Consolidate_Delivery_Details',WSH_DEBUG_SV.C_PROC_LEVEL);
641 END IF;
642 --
643 -- HW OPM BUG#:3121616 Added x_remain_bo_qty2s
644 WSH_DELIVERY_DETAILS_ACTIONS.Consolidate_Delivery_Details(
645 p_delivery_details_tab => l_dd_rec_tab,
646 p_bo_mode => p_bo_mode,
647 x_cons_delivery_details_tab => l_cons_dd_rec_tab,
648 x_remain_bo_qtys => l_remain_bo_qtys,
649 x_remain_bo_qty2s => l_remain_bo_qty2s,
650 x_return_status => l_return_status
651 );
652 IF l_return_status IN (WSH_UTIL_CORE.G_RET_STS_ERROR,
653 WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR) THEN
654 wsh_util_core.add_message(l_return_status,'WSH_DELIVERY_DETAILS_ACTIONS.Consolidate_Delivery_Details');
655 x_return_status := l_return_status;
656 RAISE backorder_error;
657 END IF;
658
659 l_detail_ids.DELETE;
660 l_req_qtys.DELETE;
661 l_bo_qtys.DELETE;
662 -- muom
663 l_req_qty2s.DELETE;
664 l_bo_qty2s.DELETE;
665
666 -- Get the values returned by Consolidate_Delivery_Details, into local pl/sql tables
667 for i IN l_cons_dd_rec_tab.FIRST .. l_cons_dd_rec_tab.LAST LOOP --{
668 l_detail_ids(i) := l_cons_dd_rec_tab(i).delivery_detail_id;
669 l_req_qtys(i) := l_cons_dd_rec_tab(i).req_qty;
670 l_bo_qtys(i) := l_cons_dd_rec_tab(i).bo_qty;
671 l_overpick_qtys(i) := l_cons_dd_rec_tab(i).overpick_qty; -- Bug#3263952
672 -- HW OPM BUG#:3121616 added qty2s
673 -- muom
674 l_req_qty2s(i) := l_cons_dd_rec_tab(i).req_qty2;
675 l_overpick_qty2s(i) := l_cons_dd_rec_tab(i).overpick_qty2;
676 l_bo_qty2s(i) := l_cons_dd_rec_tab(i).bo_qty2;
677 -- muom
678 l_fulfillment_base(i) := l_cons_dd_rec_tab(i).fulfillment_base;
679 IF l_debug_on THEN
680 WSH_DEBUG_SV.logmsg(l_module_name,'-- Delivery Detail Id '||l_detail_ids(i)||' Fulfillment Base '||l_fulfillment_base(i));
681 END IF;
682 -- end of 3121616
683 END LOOP; --}
684 IF l_debug_on THEN
685 WSH_DEBUG_SV.logmsg(l_module_name,'Total No. of delivery details '||p_detail_ids.COUNT,WSH_DEBUG_SV.C_PROC_LEVEL);
686 WSH_DEBUG_SV.logmsg(l_module_name,'No. of delivery details after Consolidation '||l_detail_ids.COUNT,WSH_DEBUG_SV.C_PROC_LEVEL);
687 END IF;
688 END IF; --}, l_global_param_rec.consolidate_bo_lines flag
689 --
690
691 -- Replaced p_detail_ids with l_detail_ids, p_req_qtys with l_req_qtys, p_bo_qtys with l_bo_qtys and
692 -- p_overpick_qtys with l_overpick_qtys, as part of code changes for Consolidation of BO Delivery Details project.
693 FOR i IN 1 .. l_detail_ids.COUNT
694 LOOP --{ loop thru' for each delivery detail id in l_detail_ids
695 -- muom
696 IF l_debug_on THEN
697 WSH_DEBUG_SV.logmsg(l_module_name,'Processing Detail '||l_detail_ids(i));
698 WSH_DEBUG_SV.logmsg(l_module_name,'l_req_qtys(i) '||l_req_qtys(i)||' l_bo_qtys(i) '||l_bo_qtys(i)||' l_overpick_qtys(i) '||l_overpick_qtys(i));
699 WSH_DEBUG_SV.logmsg(l_module_name,'l_req_qty2s(i) '||l_req_qty2s(i)||' l_bo_qty2s(i) '||l_bo_qty2s(i)||' l_overpick_qty2s(i) '||l_overpick_qty2s(i));
700 END IF;
701 /* Bug 2399729, default l_backorder_all to 'N' */
702 l_backorder_all := 'N';
703 -- 1. See if the detail is assigned to a delivery .
704 -- This check introduced because of bug 2041416 , because Backorder API
705 -- can be called now during cycle counting of delivery details which may not
706 -- be assigned to any delivery in the first place.
707
708 l_delivery_id := NULL; -- bug# 8365722 : Need to initialize the deliery_id to null
709 l_source_code := NULL;
710 l_source_line_id := NULL;
711
712 /*Bug#:2026895 Added l_delivery_id in the fetch statement*/
713 -- bug# 6908504 (replenishment project): checking the planned flag value.
714 open c_assigned ( l_detail_ids(i) ) ;
715 FETCH c_assigned INTO l_delivery_id,l_planned_flag, l_source_code, l_source_line_id; -- muom
716 CLOSE c_assigned ;
717 --
718 IF l_delivery_id IS NULL THEN
719 --{
720 l_assigned_flag := 'N';
721 l_delivery_id := -99999;
722 l_planned_flag := 'N';
723 ELSE
724 l_assigned_flag := 'Y';
725 --}
726 END IF;
727
728 --
729 -- Debug Statements
730 --
731 IF l_debug_on THEN
732 WSH_DEBUG_SV.logmsg(l_module_name, 'l_delivery_id: '||l_delivery_id||' ,l_planned_flag: '||l_planned_flag);
733 WSH_DEBUG_SV.logmsg(l_module_name, 'IN BACKORDER ' || I );
734 END IF;
735 --
736
737 -- muom: l_fulfillment_base will not be populated if the details are not consolidated
738 IF (NOT l_fulfillment_base.exists(i)) THEN
739 l_fulfillment_base(i) := WSH_UTIL_CORE.Get_Line_Fulfillment_Base(l_source_code, l_source_line_id);
740 END IF;
741 IF l_debug_on THEN
742 WSH_DEBUG_SV.logmsg(l_module_name,'Fulfillment Base is '||l_fulfillment_base(i));
743 END IF;
744
745
746 -- bug 1672188: check there is a quantity (normal or overpicked) to backorder.
747 -- muom
748 IF (l_fulfillment_base(i) = 'P' and (NVL(l_bo_qtys(i), 0) <= 0) AND (NVL(l_overpick_qtys(i), 0) <= 0)) OR
749 (l_fulfillment_base(i) = 'S' and (NVL(l_bo_qty2s(i), 0) <= 0) AND (NVL(l_overpick_qty2s(i), 0) <= 0)) THEN
750 goto loop_end;
751 END IF;
752
753 -- bug 1672188: split backordered quantity if needed
754 --
755 -- Debug Statements
756 --
757 IF l_debug_on THEN
758 WSH_DEBUG_SV.logmsg(l_module_name, 'CHECKING IF ' || l_bo_qtys ( I ) || ' < ' || l_req_qtys ( I ) );
759 -- muom
760 WSH_DEBUG_SV.logmsg(l_module_name, 'CHECKING IF ' || l_bo_qty2s ( I ) || ' < ' || l_req_qty2s ( I ) );
761 END IF;
762 --
763 -- muom
764 IF (l_fulfillment_base(i) = 'P' and (l_bo_qtys(i) > 0) AND (l_bo_qtys(i) < l_req_qtys(i))) OR
765 (l_fulfillment_base(i) = 'S' and (l_bo_qty2s(i) > 0) AND (l_bo_qty2s(i) < l_req_qty2s(i))) THEN
766
767 l_bo_qty := l_bo_qtys(i);
768 l_bo_qty2 := p_bo_qtys2(i);
769
770 -- wsh_util_core.println('splitting ' || l_detail_ids(i) );
771 --
772 -- Debug Statements
773 --
774 IF l_debug_on THEN
775 WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_DELIVERY_DETAILS_ACTIONS.SPLIT_DELIVERY_DETAILS',WSH_DEBUG_SV.C_PROC_LEVEL);
776 END IF;
777 --
778 wsh_delivery_details_actions.split_delivery_details(
779 p_from_detail_id => l_detail_ids(i),
780 p_req_quantity => l_bo_qty,
781 x_new_detail_id => l_bo_detail_id,
782 x_return_status => l_return_status ,
783 p_req_quantity2 => l_bo_qty2);
784
785 IF l_debug_on THEN
786 WSH_DEBUG_SV.log(l_module_name,'SPLIT_DELIVERY_DETAILS l_return_status',l_return_status);
787 END IF;
788
789 /* Bug 2308509 */
790 IF l_return_status IN (WSH_UTIL_CORE.G_RET_STS_ERROR,
791 WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR) THEN
792 wsh_util_core.add_message(l_return_status,l_module_name);
793 x_return_status := l_return_status;
794 RAISE backorder_error;
795 ELSIF l_return_status = WSH_UTIL_CORE.G_RET_STS_WARNING THEN
796 l_num_warn := l_num_warn + 1;
797 END IF;
798 /* End of Bug 2308509 */
799
800 ELSE
801 l_bo_detail_id := l_detail_ids(i);
802 /* Bug 2399729, backorder all not allowed for third party warehouse shipment line */
803 l_backorder_all := 'Y';
804
805 END IF;
806
807 /*Bug 5525314 moved up the open cursor c_detail
808 and WSH_DELIVERY_DETAILS_INV.Get_item_information procedure. */
809 /* H integration: 940/945 look up source code before unassigning wrudge*/
810 OPEN c_detail(l_bo_detail_id );
811 FETCH c_detail INTO l_detail_rec;
812 CLOSE c_detail;
813 -- bug 5233115: unable to backorder/cycle-count overpicked
814 -- lot-indivisible item because Inventory does not allow
815 -- partial reservation updates.
816 --
817 -- Solution agreed on by WMS, WSH, and OPM:
818 -- 1. Blow away lot-indivisible reservation, whether
819 -- overpicked or not.
820 -- 2. Option RETAIN_RSV will not be honored. At this time,
821 -- only WMS will pass this option (frontport bug 4721577;
822 -- base bug 4656374) when unloading truck and this option is set.
823 -- WMS will inform the user about this exception (bug 5350778).
824 --
825 -- Note that this issue can affect ATO; see the tracking bug 5350793.
826
827 IF l_detail_rec.released_status = 'Y' THEN
828 -- call this API only if line is staged, so we can test lot
829 -- divisibility.
830
831 WSH_DELIVERY_DETAILS_INV.Get_item_information
832 (
833 p_organization_id => l_detail_rec.organization_id,
834 p_inventory_item_id => l_detail_rec.inventory_item_id,
835 x_mtl_system_items_rec => l_item_info,
836 x_return_status => l_return_status
837 );
838
839 IF l_return_status IN (WSH_UTIL_CORE.G_RET_STS_ERROR,
840 WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR) THEN
841 -- this is unlikely to happen
842 fnd_message.set_name('WSH', 'WSH_INVALID_INV_ITEM');
843 fnd_message.set_token('ITEM_NAME', l_detail_rec.inventory_item_id);
844 wsh_util_core.add_message(l_return_status,l_module_name);
845 x_return_status := l_return_status;
846 RAISE backorder_error;
847 END IF;
848 ELSE
849 l_item_info.lot_divisible_flag := NULL;
850 l_item_info.lot_control_code := NULL;
851 END IF;
852
853 IF l_debug_on THEN
854 wsh_debug_sv.LOG(l_module_name, 'l_item_info.lot_divisible_flag', l_item_info.lot_divisible_flag);
855 wsh_debug_sv.LOG(l_module_name, 'l_item_info.lot_control_code', l_item_info.lot_control_code);
856 wsh_debug_sv.LOG(l_module_name, 'l_detail_rec.released_status', l_detail_rec.released_status);
857 END IF;
858 -- check for overpicking
859 -- muom
860 IF (l_fulfillment_base(i) = 'P' and l_overpick_qtys(i) > 0) OR
861 (l_fulfillment_base(i) = 'S' and l_overpick_qty2s(i) > 0) THEN --Bug 2026099: Removed other conditions
862 -- if we are backordering overpicked quantity,
863 -- and the line has been split or the line is fully overpicked,
864 -- then we need to decrement the picked quantities.
865 -- assumption: action Cycle Count and Ship Confirm ensure
866 -- that shipped_quantity won't be affected by the reduction.
867 -- (Viz., if req_qty = 0, cycle count quantities must be 0.)
868
869 --Bug 5525314
870 --If the item is lot indivisible then over picked qty should be considered.
871 IF (l_item_info.lot_divisible_flag = 'N' AND
872 l_item_info.lot_control_code = 2 ) THEN
873 -- muom
874 IF l_debug_on THEN
875 WSH_DEBUG_SV.logmsg(l_module_name, ' *** Item is Lot Indivisible');
876 END IF;
877 IF (l_fulfillment_base(i) = 'P' and l_req_qtys(i) = 0) OR (l_fulfillment_base(i) = 'S' and l_req_qty2s(i) = 0) THEN
878 -- If detail has 0 requested qty (or secondary qty when fulfillment base is secondary), Then
879 -- the following variables need to be set to 0 for the detail to get deleted
880 l_new_picked_quantity := 0;
881 l_new_picked_quantity2 := 0;
882 l_detail_rec.picked_quantity := 0;
883 l_detail_rec.picked_quantity2 := 0;
884 ELSE
885 l_new_picked_quantity := l_detail_rec.picked_quantity ;
886 l_new_picked_quantity2 := l_detail_rec.picked_quantity2 ;
887 END IF;
888 ELSE
889 UPDATE wsh_delivery_details
890 SET picked_quantity = picked_quantity - l_overpick_qtys(i),
891 picked_quantity2 = picked_quantity2 - l_overpick_qty2s(i) -- muom
892 WHERE l_detail_ids(i) = delivery_detail_id
893 -- muom
894 RETURNING picked_quantity, picked_quantity2 INTO l_new_picked_quantity, l_new_picked_quantity2;
895
896 --bug 7166138 new picked quantity should be updated in l_detail_rec
897 l_detail_rec.picked_quantity := l_new_picked_quantity;
898 -- muom
899 l_detail_rec.picked_quantity2 := l_new_picked_quantity2;
900
901 END IF;
902 ELSE
903 l_new_picked_quantity := NULL;
904 -- muom
905 l_new_picked_quantity2 := NULL;
906 END IF;
907
908 IF (NVL(l_global_param_rec.consolidate_bo_lines,'N') = 'N') THEN
909 -- muom
910 IF (l_fulfillment_base(i) = 'P' and l_bo_qtys(i) > 0) OR
911 (l_fulfillment_base(i) = 'S' and l_bo_qty2s(i) > 0) THEN
912 -- make a list for temporary query in STF (e.g., cycle-count).
913 x_out_rows ( x_out_rows.count + 1 ) := l_bo_detail_id ;
914 END IF;
915 END IF;
916 --
917 IF l_debug_on THEN
918 WSH_DEBUG_SV.logmsg(l_module_name, ' *** l_new_picked_quantity '||l_new_picked_quantity||' l_new_picked_quantity2 '||l_new_picked_quantity2);
919 END IF;
920
921 -- bug 2730685: if a purely overpicked but completely unshipped line is packed,
922 -- it needs to be unpacked so that it can be deleted,
923 -- instead of becoming "shipped" with zero req/pick quantity
924 -- Need to check for l_bo_qtys(i) = 0
925 -- muom
926 IF ( l_fulfillment_base(i) = 'P' and ((l_bo_qtys(i) > 0) OR (l_new_picked_quantity = 0))) OR
927 ( l_fulfillment_base(i) = 'S' and ((l_bo_qty2s(i) > 0) OR (l_new_picked_quantity2 = 0))) THEN
928
929 /* H integration: 940/945 WSH should remain assigned wrudge */
930 -- bug# 6908504 (replenishment project): Should not un-assign the planned deliveries when called the api
931 -- with p_bo_source as PICK.
932 IF( l_assigned_flag = 'Y'
933 AND l_detail_rec.source_code <> 'WSH'
934 AND ( p_bo_source <> 'PICK' OR ( p_bo_source = 'PICK' AND l_planned_flag <> 'Y')) ) THEN
935 --{
936 --
937 -- Debug Statements
938 --
939 IF l_debug_on THEN
940 WSH_DEBUG_SV.logmsg(l_module_name, ' NEW DD TO UNASSIGN -' || L_BO_DETAIL_ID || ' TO L_UNASSIGN_DDS' );
941 END IF;
942 --
943 l_unassign_dd_flag := 'Y'; --deferred to the end.
944 l_unassign_dds(l_unassign_dds.count + 1 ) := l_bo_detail_id ;
945 --}
946 END IF;
947 --}
948 END IF;
949
950 /*Bug 2096052 for OKE*/
951 IF l_detail_rec.source_code = 'OKE' THEN
952 RAISE invalid_source_code;
953 /* H integration: 945 backordering cancels line wrudge */
954 ELSIF l_detail_rec.source_code = 'WSH' THEN
955 IF l_backorder_all = 'Y' THEN
956 RAISE no_backorder_full;
957 ELSE
958 l_delete_dds( l_delete_dds.count+1 ) := l_bo_detail_id;
959 END IF;
960 END IF;
961
962 /* End of Code added for Bug 2096052*/
963
964 -- muom
965 IF l_debug_on THEN
966 WSH_DEBUG_SV.logmsg(l_module_name, ' *** l_detail_rec.picked_quantity '||l_detail_rec.picked_quantity||' l_detail_rec.picked_quantity2 '||l_detail_rec.picked_quantity2);
967 END IF;
968 IF ((l_detail_ids(i) = l_bo_detail_id) AND
969 ((l_fulfillment_base(i) = 'P' and l_req_qtys(i) = 0 AND l_detail_rec.picked_quantity = 0) OR -- Bug 2026260
970 (l_fulfillment_base(i) = 'S' and l_req_qty2s(i) = 0 AND l_detail_rec.picked_quantity2 = 0))) THEN
971 -- If the line is fully overpicked and not split
972 -- and its picked quantity is completely backordered/cycle-counted,
973 -- then mark it as deleted.
974 l_delete_dds( l_delete_dds.count+1 ) := l_detail_ids(i);
975 END IF;
976 -- bug 1672188: unreserve if the detail is reserved
977 l_reserved_flag :=WSH_DELIVERY_DETAILS_INV.LINE_RESERVED(
978 p_detail_id => l_detail_rec.delivery_detail_id,
979 p_source_code => l_detail_rec.source_code,
980 p_released_status => l_detail_rec.released_status,
981 p_pickable_flag => l_detail_rec.pickable_flag,
982 p_organization_id => l_detail_rec.organization_id,
983 p_inventory_item_id => l_detail_rec.inventory_item_id,
984 x_return_status => l_return_status);
985 IF l_debug_on THEN
986 WSH_DEBUG_SV.log(l_module_name,'l_return_status',l_return_status);
987 END IF;
988
989 /* Bug 2308509 */
990 IF l_return_status IN (WSH_UTIL_CORE.G_RET_STS_ERROR,
991 WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR
992 ) THEN
993 fnd_message.set_name('WSH', 'WSH_BO_RESERVED_ERROR');
994 fnd_message.set_token('DETAIL_ID', l_detail_ids(i));
995 wsh_util_core.add_message(l_return_status,l_module_name);
996 x_return_status := l_return_status;
997 RAISE backorder_error;
998 ELSIF l_return_status = WSH_UTIL_CORE.G_RET_STS_WARNING THEN
999 l_num_warn := l_num_warn + 1;
1000 END IF;
1001 /* End ofBug 2308509 */
1002
1003 --
1004 -- Debug Statements
1005 --
1006 IF l_debug_on THEN
1007 WSH_DEBUG_SV.logmsg(l_module_name, 'RESERVED FLAG = ' || L_RESERVED_FLAG );
1008 END IF;
1009 --
1010 -- wsh_util_core.println('BO Mode = ' || p_bo_mode );
1011 -- HW BUG#:2005977
1012
1013
1014 -- HW OPMCONV. Removed branching
1015
1016 IF l_reserved_flag = 'Y' THEN
1017 -- use the original delivery detail to unreserve
1018 --
1019 -- Debug Statements
1020 --
1021 IF l_debug_on THEN
1022 WSH_DEBUG_SV.logmsg(l_module_name, 'UNRESERVE ' );
1023 END IF;
1024 --
1025
1026 IF l_debug_on THEN
1027 -- muom
1028 WSH_DEBUG_SV.logmsg(l_module_name,'OverPick Qty '||l_overpick_qtys(i)|| ' OverPick Qty2 '||l_overpick_qty2s(i));
1029 END IF;
1030
1031
1032
1033 IF (l_item_info.lot_divisible_flag = 'N' AND
1034 l_item_info.lot_control_code = 2 ) THEN --{ lot divisibility
1035
1036 DECLARE --{ lot indivisible reservation block
1037 l_bo_mode VARCHAR2(30);
1038 l_qty WSH_DELIVERY_DETAILS.REQUESTED_QUANTITY%TYPE;
1039 l_qty2 WSH_DELIVERY_DETAILS.REQUESTED_QUANTITY2%TYPE;
1040 BEGIN
1041
1042 l_bo_mode := p_bo_mode;
1043 IF l_bo_mode = 'RETAIN_RSV' THEN
1044 l_bo_mode := 'UNRESERVE';
1045 END IF;
1046
1047 -- full lot indivisible reservation should be processed.
1048 l_qty := l_bo_qtys(i) + NVL(l_overpick_qtys(i), 0);
1049 -- muom: Changed from p_overpick_qtys2 to l_overpick_qty2s as the index on p_overpick_qtys2
1050 -- will no longer match if the details are consolidated
1051 l_qty2 := l_bo_qty2s(i) + NVL(l_overpick_qty2s(i), 0);
1052
1053 IF l_debug_on THEN
1054 WSH_DEBUG_SV.LOG(l_module_name, 'item is lot indivisible: l_bo_mode', l_bo_mode);
1055 WSH_DEBUG_SV.LOG(l_module_name, 'l_qty', l_qty);
1056 WSH_DEBUG_SV.LOG(l_module_name, 'l_qty2', l_qty2);
1057 END IF;
1058 --Bug 5525314,If the item is lot indivisible and backorder Qty =0,
1059 --then over pick Qty should not be unreserved
1060 -- muom
1061 IF (l_fulfillment_base(i) = 'P' AND ((l_bo_qtys(i) > 0 AND l_detail_rec.requested_quantity >0) OR (l_bo_qtys(i) = 0 AND l_detail_rec.requested_quantity = 0))) OR
1062 (l_fulfillment_base(i) = 'S' AND ((l_bo_qty2s(i) > 0 AND l_detail_rec.requested_quantity2 >0) OR (l_bo_qty2s(i) = 0 AND l_detail_rec.requested_quantity2 = 0)))
1063 THEN
1064 WSH_DELIVERY_DETAILS_ACTIONS.UNRESERVE_DELIVERY_DETAIL(
1065 p_delivery_Detail_id => l_detail_ids(i),
1066 p_unreserve_mode => l_bo_mode ,
1067 p_quantity_to_unreserve => l_qty,
1068 p_quantity2_to_unreserve => l_qty2,
1069 p_override_retain_ato_rsv => 'Y',
1070 x_return_status => l_return_status );
1071
1072 IF l_debug_on THEN
1073 WSH_DEBUG_SV.log(l_module_name,'l_return_status',l_return_status);
1074 END IF;
1075
1076 IF l_return_status IN (WSH_UTIL_CORE.G_RET_STS_ERROR,
1077 WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR) THEN
1078 fnd_message.set_name('WSH', 'WSH_BO_UNRESERVE_ERROR');
1079 fnd_message.set_token('DETAIL_ID', l_detail_ids(i));
1080 -- muom
1081 IF l_fulfillment_base(i) = 'P' THEN
1082 fnd_message.set_token('QUANTITY', l_qty);
1083 ELSE
1084 fnd_message.set_token('QUANTITY', l_qty2);
1085 END IF;
1086 wsh_util_core.add_message(l_return_status,l_module_name);
1087 x_return_status := l_return_status;
1088 RAISE backorder_error;
1089 ELSIF l_return_status = WSH_UTIL_CORE.G_RET_STS_WARNING THEN
1090 l_num_warn := l_num_warn + 1;
1091 END IF;
1092 END IF;
1093 END; --} lot indivisible reservation block
1094
1095
1096 ELSE -- } lot divisibility {
1097 -- item is not lot divisible.
1098
1099 -- Bug 2824748: Call unreserve_delivery_detail with p_override_retain_ato_rsv 'Y' so that the
1100 -- reservations for unused overpick qty are always removed
1101 IF (l_fulfillment_base(i) = 'P' and l_overpick_qtys(i) > 0) OR (l_fulfillment_base(i) = 'S' and l_overpick_qty2s(i) > 0) THEN
1102 IF l_debug_on THEN
1103 WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_DELIVERY_DETAILS_ACTIONS.UNRESERVE_DELIVERY_DETAIL FOR OVERPICK QTY',WSH_DEBUG_SV.C_PROC_LEVEL);
1104 END IF;
1105 --
1106 -- HW OPMCONV - Added Qty2
1107
1108 --Bug 4721577 Do not retain reservation for overpicked quantities
1109 IF p_bo_mode = 'RETAIN_RSV' THEN
1110 -- muom
1111 IF l_fulfillment_base(i) = 'P' THEN
1112 wsh_delivery_details_actions.unreserve_delivery_detail(
1113 p_delivery_Detail_id => l_detail_ids(i),
1114 p_unreserve_mode => 'UNRESERVE' ,
1115 p_quantity_to_unreserve => l_overpick_qtys(i),
1116 p_override_retain_ato_rsv => 'Y',
1117 x_return_status => l_return_status );
1118 ELSE
1119 wsh_delivery_details_actions.unreserve_delivery_detail(
1120 p_delivery_Detail_id => l_detail_ids(i),
1121 p_unreserve_mode => 'UNRESERVE' ,
1122 p_quantity_to_unreserve => l_overpick_qtys(i),
1123 p_quantity2_to_unreserve => l_overpick_qty2s(i),
1124 p_override_retain_ato_rsv => 'Y',
1125 x_return_status => l_return_status );
1126 END IF;
1127 ELSE
1128 wsh_delivery_details_actions.unreserve_delivery_detail(
1129 p_delivery_Detail_id => l_detail_ids(i),
1130 p_unreserve_mode => p_bo_mode ,
1131 p_quantity_to_unreserve => l_overpick_qtys(i),
1132 -- muom: Changed from p_overpick_qtys2 to l_overpick_qty2s as the index on p_overpick_qtys2
1133 -- will no longer match if the details are consolidated
1134 p_quantity2_to_unreserve =>l_overpick_qty2s(i),
1135 p_override_retain_ato_rsv => 'Y',
1136 x_return_status => l_return_status );
1137 END IF;
1138
1139 IF l_debug_on THEN
1140 WSH_DEBUG_SV.log(l_module_name,'l_return_status',l_return_status);
1141 END IF;
1142 IF l_return_status IN (WSH_UTIL_CORE.G_RET_STS_ERROR,
1143 WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR) THEN
1144 fnd_message.set_name('WSH', 'WSH_BO_UNRESERVE_ERROR');
1145 fnd_message.set_token('DETAIL_ID', l_detail_ids(i));
1146 -- muom
1147 IF l_fulfillment_base(i) = 'P' THEN
1148 fnd_message.set_token('QUANTITY', l_overpick_qtys(i));
1149 ELSE
1150 fnd_message.set_token('QUANTITY', l_overpick_qty2s(i));
1151 END IF;
1152 wsh_util_core.add_message(l_return_status,l_module_name);
1153 x_return_status := l_return_status;
1154 RAISE backorder_error;
1155 ELSIF l_return_status = WSH_UTIL_CORE.G_RET_STS_WARNING THEN
1156 l_num_warn := l_num_warn + 1;
1157 END IF;
1158
1159 END IF; -- l_overpick_qtys(i) > 0
1160
1161 IF l_debug_on THEN
1162 -- muom
1163 WSH_DEBUG_SV.logmsg(l_module_name,'BackOrder Qty '||l_bo_qtys(i)||' BackOrder Qty2 '||l_bo_qty2s(i));
1164 END IF;
1165
1166 -- Bug 2824748: Call unreserve_delivery_detail with p_override_retain_ato_rsv 'N' so that the
1167 -- reservations for backorder qty are removed depending on the ATO profile option
1168 -- muom
1169 IF (l_fulfillment_base(i) = 'P' and l_bo_qtys(i) > 0) OR (l_fulfillment_base(i) = 'S' and l_bo_qty2s(i) > 0) THEN
1170 IF l_debug_on THEN
1171 WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_DELIVERY_DETAILS_ACTIONS.UNRESERVE_DELIVERY_DETAIL FOR BO QTY',WSH_DEBUG_SV.C_PROC_LEVEL);
1172 END IF;
1173 if (l_fulfillment_base(i) = 'S' and l_overpick_qty2s(i) <=0 and l_overpick_qtys(i)>0 ) then
1174
1175 wsh_delivery_details_actions.unreserve_delivery_detail(
1176 p_delivery_Detail_id => l_detail_ids(i),
1177 p_unreserve_mode => p_bo_mode ,
1178 p_quantity_to_unreserve => l_bo_qtys(i)+l_overpick_qtys(i),
1179 p_quantity2_to_unreserve => l_bo_qty2s(i),
1180 p_override_retain_ato_rsv => 'N',
1181 x_return_status => l_return_status );
1182 else
1183 wsh_delivery_details_actions.unreserve_delivery_detail(
1184 p_delivery_Detail_id => l_detail_ids(i),
1185 p_unreserve_mode => p_bo_mode ,
1186 p_quantity_to_unreserve => l_bo_qtys(i),
1187 p_quantity2_to_unreserve => l_bo_qty2s(i),
1188 p_override_retain_ato_rsv => 'N',
1189 x_return_status => l_return_status );
1190 end if;
1191 IF l_debug_on THEN
1192 WSH_DEBUG_SV.log(l_module_name,'l_return_status',l_return_status);
1193 END IF;
1194 IF l_return_status IN (WSH_UTIL_CORE.G_RET_STS_ERROR,
1195 WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR) THEN
1196 fnd_message.set_name('WSH', 'WSH_BO_UNRESERVE_ERROR');
1197 fnd_message.set_token('DETAIL_ID', l_detail_ids(i));
1198 -- muom
1199 IF l_fulfillment_base(i) = 'P' THEN
1200 fnd_message.set_token('QUANTITY', l_bo_qtys(i));
1201 ELSE
1202 fnd_message.set_token('QUANTITY', l_bo_qty2s(i));
1203 END IF;
1204 wsh_util_core.add_message(l_return_status,l_module_name);
1205 x_return_status := l_return_status;
1206 RAISE backorder_error;
1207 ELSIF l_return_status = WSH_UTIL_CORE.G_RET_STS_WARNING THEN
1208 l_num_warn := l_num_warn + 1;
1209
1210 END IF;
1211 END IF; -- l_bo_qtys(i) > 0
1212
1213 -- CREATE MOVE ORDER HEADER ?!!
1214 -- CREATE MOVE ORDER LINE ?!!
1215
1216 END IF; -- } lot divisiblity
1217
1218 else
1219 --
1220 -- Debug Statements
1221 --
1222 IF l_debug_on THEN
1223 WSH_DEBUG_SV.logmsg(l_module_name, 'NOT RESERVED ' );
1224 END IF;
1225 --
1226 END IF; -- l_reserved_flag = Y'
1227
1228 IF (l_bo_qtys(i) > 0) THEN -- (normal backorder case)
1229 -- removed cursor logic to get order line subinventory since it is already present as original_subinventory
1230 -- unmark serial numbers if the whole line is backordered
1231 IF l_bo_qtys(i) = NVL(l_detail_rec.picked_quantity, l_detail_rec.requested_quantity) AND
1232 (l_detail_rec.serial_number is not null OR l_detail_rec.transaction_temp_id is
1233 not null ) THEN
1234
1235 --
1236 -- Debug Statements
1237 --
1238 IF l_debug_on THEN
1239 WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_DELIVERY_DETAILS_INV.FETCH_INV_CONTROLS',WSH_DEBUG_SV.C_PROC_LEVEL);
1240 END IF;
1241 --
1242 WSH_DELIVERY_DETAILS_INV.Fetch_Inv_Controls(
1243 p_delivery_detail_id => l_detail_rec.delivery_detail_id,
1244 p_inventory_item_id => l_detail_rec.inventory_item_id,
1245 p_organization_id => l_detail_rec.organization_id,
1246 p_subinventory => l_detail_rec.subinventory,
1247 x_inv_controls_rec => l_inv_controls_rec,
1248 x_return_status => l_return_status);
1249
1250 IF l_debug_on THEN
1251 WSH_DEBUG_SV.log(l_module_name,'l_return_status',l_return_status);
1252 END IF;
1253 /* Bug 2308509 */
1254 IF l_return_status IN (WSH_UTIL_CORE.G_RET_STS_ERROR,
1255 WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR) THEN
1256 wsh_util_core.add_message(l_return_status,l_module_name);
1257 x_return_status := l_return_status;
1258 RAISE backorder_error;
1259 ELSIF l_return_status = WSH_UTIL_CORE.G_RET_STS_WARNING THEN
1260 l_num_warn := l_num_warn + 1;
1261 END IF;
1262 /* End of Bug 2308509 */
1263 --
1264 -- Debug Statements
1265 --
1266 IF l_debug_on THEN
1267 WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_DELIVERY_DETAILS_INV.UNMARK_SERIAL_NUMBER',WSH_DEBUG_SV.C_PROC_LEVEL);
1268 END IF;
1269 --
1270 WSH_DELIVERY_DETAILS_INV.Unmark_Serial_Number(
1271 p_delivery_detail_id => l_detail_rec.delivery_detail_id,
1272 p_serial_number_code => l_inv_controls_rec.serial_code,
1273 p_serial_number => l_detail_rec.serial_number,
1274 p_transaction_temp_id => l_detail_rec.transaction_temp_id,
1275 x_return_status => l_return_status);
1276 /* Bug 2308509 */
1277 IF l_debug_on THEN
1278 WSH_DEBUG_SV.log(l_module_name,'l_return_status',l_return_status);
1279 END IF;
1280 IF l_return_status IN (WSH_UTIL_CORE.G_RET_STS_ERROR,
1281 WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR) THEN
1282 wsh_util_core.add_message(l_return_status,l_module_name);
1283 x_return_status := l_return_status;
1284 RAISE backorder_error;
1285 ELSIF l_return_status = WSH_UTIL_CORE.G_RET_STS_WARNING THEN
1286 l_num_warn := l_num_warn + 1;
1287 END IF;
1288 /* End of Bug 2308509 */
1289
1290 UPDATE wsh_delivery_details
1291 SET transaction_temp_id = NULL,
1292 serial_number = NULL,
1293 last_update_date = SYSDATE,
1294 last_updated_by = FND_GLOBAL.USER_ID,
1295 last_update_login = FND_GLOBAL.LOGIN_ID
1296 WHERE delivery_detail_id = l_detail_rec.delivery_detail_id;
1297 END IF;
1298 --
1299 -- Debug Statements
1300 --
1301 IF l_debug_on THEN
1302 WSH_DEBUG_SV.logmsg(l_module_name, 'BACKORDER ...' );
1303 WSH_DEBUG_SV.log(l_module_name,'Current Time is ',SYSDATE);
1304 -- 2807093
1305 WSH_DEBUG_SV.log(l_module_name,'Released Status: ', l_detail_rec.released_status);
1306 END IF;
1307 -- bug 1733849: clear TRACKING_NUMBER when not shipped
1308 -- HW BUG#:1885927 Need to clear lot_number,sublot_number,locator_id,
1309 -- preferred_grade and subinventory if the line is backordered
1310 -- HW BUG#:2005977 Removed the branching so OPM/Discrete
1311 -- can use the same update statement.
1312 -- bug 2320460: cycle-counted lines should not have subinventory
1313 -- cleared.
1314 -- Normally backordered lines should have order line's subinventor
1315
1316 --Begin OPM bug 3561937
1317 -- HW OPMCONV - Removed branching
1318
1319 OPEN get_pref_grade(l_detail_rec.delivery_detail_id);
1320 FETCH get_pref_grade INTO l_oeline_pref_grade;
1321 CLOSE get_pref_grade;
1322 --End OPM bug 3561937
1323 -- LSP PROJECT : locator_id,lot_number,revision should be populated with original values
1324 -- in case of distributed mode as well as LSP mode with clientId on WDD.
1325 IF (l_standalone_mode = 'D' OR (l_standalone_mode = 'L' and l_detail_rec.client_id IS NOT NULL))
1326 THEN
1327 l_standalone_mode := 'D';
1328 END IF;
1329 -- LSP PROJECT : end
1330 --
1331 --RTV changes
1332 IF l_detail_rec.source_code IN ('OE','WSH','OKE') THEN
1333 UPDATE wsh_delivery_details
1334 SET move_order_line_id = NULL ,
1335 -- 2807093: For ATO items, it is possible that during Shp.Confirm CTO would have updated the rel.Status to N
1336 -- so checking to see if it is 'N' then it has to remain 'N' otherwise 'B'
1337 released_status = decode(pickable_flag,'Y', decode(released_status, 'N', released_status,'B'),'X'),
1338 cycle_count_quantity = NULL,
1339 cycle_count_quantity2 = NULL,
1340 shipped_quantity = NULL,
1341 shipped_quantity2 = NULL,
1342 picked_quantity = NULL,
1343 picked_quantity2 = NULL,
1344 -- ship_set_id = NULL , code removed per bug 2008156
1345 -- Bug 2444564 : Backordered/Cycle Count lines should be reset to Original Subinventory
1346 subinventory = original_subinventory,
1347 inv_interfaced_flag = decode(pickable_flag, 'Y', nvl(inv_interfaced_flag,'N'), 'X'),
1348 --Standalone project Changes
1349 locator_id = decode(l_standalone_mode, 'D', original_locator_id, NULL),
1350 -- OPM Bug 3561937 replaced NULL with l_oeline_pref_grade
1351 preferred_grade = l_oeline_pref_grade,
1352 -- HW OPMCONV. No need for sublot anymore
1353 -- sublot_number=NULL,
1354 --Standalone project Changes Starts
1355 lot_number = decode(l_standalone_mode, 'D', original_lot_number, NULL) , -- Bug 1705057
1356 revision = decode(l_standalone_mode, 'D', original_revision, NULL) ,
1357 --Standalone project Changes Ends
1358 batch_id = null , -- Bug 2711490
1359 -- tracking_number = NULL, Bug# 3632485
1360 transaction_id = NULL, --- 2803570
1361 replenishment_status = NULL -- bug# 6908504 (replenishment project), update replenishment status to NULL.
1362 WHERE delivery_detail_id = l_detail_rec.delivery_detail_id AND
1363 NVL(container_flag, 'N') = 'N' ;
1364 ELSE
1365 UPDATE wsh_delivery_details
1366 SET move_order_line_id = NULL ,
1367 released_status ='X',
1368 cycle_count_quantity = NULL,
1369 cycle_count_quantity2 = NULL,
1370 shipped_quantity = NULL,
1371 shipped_quantity2 = NULL,
1372 picked_quantity = NULL,
1373 picked_quantity2 = NULL,
1374 subinventory = original_subinventory,
1375 inv_interfaced_flag = decode(source_code,'RTV','N','X'),
1376 locator_id = original_locator_id,
1377 preferred_grade = l_oeline_pref_grade,
1378 lot_number = original_lot_number,
1379 revision = original_revision,
1380 batch_id = null ,
1381 transaction_id = NULL,
1382 replenishment_status = NULL
1383 WHERE delivery_detail_id = l_detail_rec.delivery_detail_id AND
1384 NVL(container_flag, 'N') = 'N' ;
1385 END IF;
1386
1387 WSH_DD_TXNS_PVT.create_dd_txn_from_dd (p_delivery_detail_id => l_detail_rec.delivery_detail_id,
1388 x_dd_txn_id => l_dd_txn_id,
1389 x_return_status =>l_txn_return_status);
1390 IF (l_txn_return_status <> WSH_UTIL_CORE.G_RET_STS_SUCCESS) THEN
1391 x_return_status := l_txn_return_status;
1392 --RETURN;
1393 raise e_return_excp; -- LPN CONV. rv
1394 END IF;
1395
1396
1397 --Raise Event: Pick To Pod Workflow
1398 WSH_WF_STD.Raise_Event(
1399 p_entity_type => 'LINE',
1400 p_entity_id => l_detail_rec.delivery_detail_id ,
1401 p_event => 'oracle.apps.wsh.line.gen.backordered' ,
1402 --p_parameters IN wf_parameter_list_t DEFAULT NULL,
1403 p_organization_id => l_detail_rec.organization_id,
1404 x_return_status => l_wf_rs ) ;
1405 --Error Handling to be done in WSH_WF_STD.Raise_Event itself
1406 IF l_debug_on THEN
1407 WSH_DEBUG_SV.log(l_module_name,'Current Time is ',SYSDATE);
1408 WSH_DEBUG_SV.log(l_module_name,'Delivery Detail Id is ', l_detail_rec.delivery_detail_id );
1409 wsh_debug_sv.log(l_module_name,'Return Status After Calling WSH_WF_STD.Raise_Event',l_wf_rs);
1410 END IF;
1411 --Even if raising of event fails the flow continues.
1412 --IF (l_wf_rs <>WSH_UTIL_CORE.G_RET_STS_SUCCESS ) then
1413 --No Action
1414 --END IF;
1415 --Done Raise Event: Pick To Pod Workflow
1416
1417 END IF; -- l_bo_qtys(i) > 0 (normal backorder case)
1418
1419 -- Consolidation of BO Delivery Details project
1420 IF (l_global_param_rec.consolidate_bo_lines = 'Y' AND p_line_ids.COUNT > 1) THEN
1421 IF l_debug_on THEN
1422 WSH_DEBUG_SV.logmsg(l_module_name,'Adding the Consolidated BO Qty to the Requested Qty of the BO delivery detail ',WSH_DEBUG_SV.C_PROC_LEVEL);
1423 WSH_DEBUG_SV.logmsg(l_module_name,'..delivery detail id: '||l_detail_rec.delivery_detail_id, WSH_DEBUG_SV.C_PROC_LEVEL);
1424 WSH_DEBUG_SV.logmsg(l_module_name,'..delivery detail req qty: '||l_bo_qtys(i), WSH_DEBUG_SV.C_PROC_LEVEL);
1425 -- muom
1426 WSH_DEBUG_SV.logmsg(l_module_name,'..delivery detail req qty2: '||l_bo_qty2s(i), WSH_DEBUG_SV.C_PROC_LEVEL);
1427 WSH_DEBUG_SV.logmsg(l_module_name,'..Consolidate BO quantity '||l_remain_bo_qtys(i), WSH_DEBUG_SV.C_PROC_LEVEL);
1428 -- HW OPM BUG#:3121616 added qty2s
1429 WSH_DEBUG_SV.logmsg(l_module_name,'..Consolidate BO quantity2 '||l_remain_bo_qty2s(i), WSH_DEBUG_SV.C_PROC_LEVEL);
1430 END IF;
1431 -- HW OPM BUG#:3121616 added requested_quantity2
1432 UPDATE WSH_DELIVERY_DETAILS
1433 SET requested_quantity = requested_quantity + l_remain_bo_qtys(i),
1434 requested_quantity2 = requested_quantity2 + l_remain_bo_qty2s(i)
1435 WHERE delivery_detail_id = l_detail_rec.delivery_detail_id;
1436
1437 WSH_DD_TXNS_PVT.create_dd_txn_from_dd(p_delivery_detail_id => l_detail_rec.delivery_detail_id,
1438 x_dd_txn_id => l_dd_txn_id,
1439 x_return_status =>l_txn_return_status);
1440 IF (l_return_status <> WSH_UTIL_CORE.G_RET_STS_SUCCESS) THEN
1441 x_return_status := l_return_status;
1442 raise e_return_excp;
1443 END IF;
1444
1445 l_bo_qtys(i) := l_bo_qtys(i) + l_remain_bo_qtys(i);
1446 l_req_qtys(i) := l_bo_qtys(i);
1447 -- HW OPM BUG#:3121616 added qty2s
1448 l_bo_qty2s(i) := l_bo_qty2s(i) + l_remain_bo_qty2s(i);
1449 -- muom
1450 l_req_qty2s(i) := l_bo_qty2s(i);
1451 -- end of 3121616
1452 END IF;
1453
1454 -- DBI Project, Above 2 Update statements are executed for l_detail_rec.delivery_detail_id
1455 -- Combining the 2 updates and making 1 call to DBI for the updates in WDD table
1456 -- These update released_status/requested_quantity
1457 -- This is different from l_detail_ids which is the set of ids for which LOOP is
1458 -- getting executed
1459 --
1460 -- Update of wsh_delivery_details where requested_quantity/released_status
1461 -- are changed, call DBI API after the update.
1462 -- This API will also check for DBI Installed or not
1463 IF l_debug_on THEN
1464 WSH_DEBUG_SV.log(l_module_name,'Calling DBI API.Detail id-',l_detail_rec.delivery_detail_id);
1465 END IF;
1466 l_detail_tab(1) := l_detail_rec.delivery_detail_id;
1467 WSH_INTEGRATION.DBI_Update_Detail_Log
1468 (p_delivery_detail_id_tab => l_detail_tab,
1469 p_dml_type => 'UPDATE',
1470 x_return_status => l_dbi_rs);
1471
1472 IF l_debug_on THEN
1473 WSH_DEBUG_SV.log(l_module_name,'Return Status after DBI Call-',l_dbi_rs);
1474 END IF;
1475 IF l_dbi_rs = WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR THEN
1476 x_return_status := l_dbi_rs;
1477 -- just pass this return status to caller API, raise as above
1478 -- Added in Above API already,wsh_util_core.add_message(x_return_status,l_module_name);
1479 RAISE backorder_error;
1480 END IF;
1481 -- treat all other return status as Success
1482 -- End of Code for DBI Project
1483 --
1484
1485 -- J: W/V Changes
1486 IF l_bo_qtys(i) > 0 Then -- Bug 3547300
1487 IF l_debug_on THEN
1488 WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_WV_UTILS.DETAIL_WEIGHT_VOLUME',WSH_DEBUG_SV.C_PROC_LEVEL);
1489 END IF;
1490 --
1491 WSH_WV_UTILS.Detail_Weight_Volume(
1492 p_delivery_detail_id => l_detail_rec.delivery_detail_id,
1493 p_update_flag => 'Y',
1494 p_post_process_flag => 'Y',
1495 p_calc_wv_if_frozen => 'Y',
1496 x_net_weight => l_split_weight,
1497 x_volume => l_split_volume,
1498 x_return_status => l_return_status);
1499 IF l_debug_on THEN
1500 WSH_DEBUG_SV.log(l_module_name,'l_return_status',l_return_status);
1501 END IF;
1502 IF l_return_status IN (WSH_UTIL_CORE.G_RET_STS_ERROR, WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR) THEN
1503 wsh_util_core.add_message(l_return_status,l_module_name);
1504 x_return_status := l_return_status;
1505 RAISE new_det_wt_vol_failed;
1506 ELSIF l_return_status = WSH_UTIL_CORE.G_RET_STS_WARNING THEN
1507 l_num_warn := l_num_warn + 1;
1508 END IF;
1509 END IF; -- Bug 3547300
1510
1511 -- bug # 6749200 (replenishment project ) : Moved the backorder consolidation code to inside the loop as the API needs to be called
1512 -- for each record separately.
1513 -- Bug#3317692
1514 -- Consolidation of BO Delivery Details project
1515 -- Get the delivery details with non-zero requested quantity to pass to Consolidate_Source_Line
1516 -- bug# 6908504 (replenishment project): Should not consolidate when the dd is assigned to a planned deliveries
1517 -- and p_bo_source is3/27/2008 PICK.
1518 -- muom
1519 IF ( ((l_fulfillment_base(i) = 'P' and l_req_qtys(i) > 0) OR (l_fulfillment_base(i) = 'S' and l_req_qty2s(i) > 0))
1520 AND l_global_param_rec.consolidate_bo_lines = 'Y'
1521 AND ( p_bo_source <> 'PICK' OR ( p_bo_source = 'PICK' AND l_planned_flag <> 'Y')) ) THEN
1522 --{
1523
1524 -- unassign and unpack empty container Added in bug 7460785
1525 --
1526 -- Debug Statements
1527
1528 IF (l_unassign_dds.COUNT > 0 and l_unassign_dds(l_unassign_dds.COUNT) = l_bo_detail_id ) THEN
1529 --{
1530 l_unassign_dds_tmp(1) := l_bo_detail_id;
1531
1532 IF l_debug_on THEN
1533 WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_DELIVERY_DETAILS_ACTIONS.unassign_unpack_empty_cont',WSH_DEBUG_SV.C_PROC_LEVEL);
1534 END IF;
1535
1536 -- p_validate_flag is passed as 'N', so that backordering does not fail for planned Deliveries.
1537 WSH_DELIVERY_DETAILS_ACTIONS.unassign_unpack_empty_cont
1538 (
1539 p_ids_tobe_unassigned => l_unassign_dds_tmp ,
1540 p_validate_flag => 'N',
1541 x_return_status => l_return_status
1542 );
1543
1544 --
1545 -- Debug Statements
1546 --
1547 IF l_debug_on THEN
1548 WSH_DEBUG_SV.logmsg(l_module_name, 'AFTER UNASSIGN AND UNPACK EMPTY CONTAINER '|| L_RETURN_STATUS );
1549 END IF;
1550
1551 IF (l_return_status <> WSH_UTIL_CORE.G_RET_STS_SUCCESS) THEN
1552 --{
1553 IF l_return_status = WSH_UTIL_CORE.G_RET_STS_WARNING THEN
1554 l_num_warn := l_num_warn + 1;
1555 ELSE
1556 x_return_status := l_return_status;
1557 RAISE backorder_error;
1558 END IF;
1559 --}
1560 END IF;
1561
1562 l_unassign_dds.DELETE(l_unassign_dds.COUNT);
1563 --}
1564 END IF; -- unpack and un-assign the deliveries
1565
1566 -- consolidate logic
1567 l_cons_source_line_rec_tab.delete;
1568 l_cons_dd_ids.delete;
1569 l_cons_source_line_rec_tab(1).delivery_detail_id := l_bo_detail_id;
1570 l_cons_source_line_rec_tab(1).source_line_id := l_detail_rec.source_line_id;
1571 l_cons_source_line_rec_tab(1).delivery_id := l_delivery_id;
1572 l_cons_source_line_rec_tab(1).bo_qty := l_bo_qtys(i);
1573 l_cons_source_line_rec_tab(1).req_qty := l_bo_qtys(i);
1574 -- HW OPM BUG#:3121616 added qty2s
1575 l_cons_source_line_rec_tab(1).bo_qty2 := l_bo_qty2s(i);
1576 l_cons_source_line_rec_tab(1).req_qty2 := l_bo_qty2s(i);
1577 -- muom
1578 l_cons_source_line_rec_tab(1).fulfillment_base := l_fulfillment_base(i);
1579 -- end of 3121616
1580 --
1581 -- Debug Statements
1582 --
1583 IF l_debug_on THEN
1584 WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_DELIVERY_DETAILS_ACTIONS.Consolidate_Source_Line',WSH_DEBUG_SV.C_PROC_LEVEL);
1585 END IF;
1586 --
1587 WSH_DELIVERY_DETAILS_ACTIONS.Consolidate_Source_Line(
1588 p_Cons_Source_Line_Rec_Tab => l_cons_source_line_rec_tab,
1589 x_consolidate_ids => l_cons_dd_ids,
1590 x_return_status => l_return_status
1591 );
1592 IF l_return_status IN (WSH_UTIL_CORE.G_RET_STS_ERROR,WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR )THEN
1593 --{
1594 x_return_status := l_return_status;
1595 RAISE backorder_error;
1596 --}
1597 END IF;
1598 IF l_cons_dd_ids.COUNT > 0 THEN
1599 --{
1600 x_out_rows( x_out_rows.COUNT + 1) := l_cons_dd_ids(l_cons_dd_ids.FIRST);
1601 -- Setting the corresponding flag to 'Y', if a delivery detail is consolidated
1602 -- into some other delivery detail.
1603 IF l_cons_source_line_rec_tab(1).delivery_detail_id = l_cons_dd_ids(l_cons_dd_ids.FIRST) THEN
1604 x_cons_flags(x_cons_flags.COUNT+1) := 'N';
1605 ELSE
1606 x_cons_flags(x_cons_flags.COUNT+1) := 'Y';
1607
1608 -- Back order consolidation takes care of un-assigning/unpacking so no need to call separately.
1609 IF ( l_unassign_dd_flag = 'Y' ) THEN
1610 --{
1611 l_unassign_dds.delete(l_unassign_dds.count);
1612 l_unassign_dd_flag := 'N';
1613 --}
1614 END IF;
1615
1616 END IF;
1617 --}
1618 END IF;
1619 --}
1620 END IF;
1621 --
1622 <<loop_end>>
1623 NULL;
1624 END LOOP; --}
1625
1626 -- unassign and unpack empty container
1627 --
1628 -- Debug Statements
1629 --
1630 IF l_debug_on THEN
1631 WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_DELIVERY_DETAILS_ACTIONS.unassign_unpack_empty_cont',WSH_DEBUG_SV.C_PROC_LEVEL);
1632 END IF;
1633 -- p_validate_flag is passed as 'N', so that backordering does not fail for planned Deliveries.
1634 WSH_DELIVERY_DETAILS_ACTIONS.unassign_unpack_empty_cont (
1635 p_ids_tobe_unassigned => l_unassign_dds ,
1636 p_validate_flag => 'N',
1637 x_return_status => l_return_status
1638 );
1639
1640 --
1641 -- Debug Statements
1642 --
1643 IF l_debug_on THEN
1644 WSH_DEBUG_SV.logmsg(l_module_name, 'AFTER UNASSIGN AND UNPACK EMPTY CONTAINER '|| L_RETURN_STATUS );
1645 END IF;
1646 IF (l_return_status <> WSH_UTIL_CORE.G_RET_STS_SUCCESS) THEN
1647 IF l_return_status = WSH_UTIL_CORE.G_RET_STS_WARNING THEN
1648 l_num_warn := l_num_warn + 1;
1649 ELSE
1650 x_return_status := l_return_status;
1651 RAISE backorder_error;
1652 END IF;
1653 END IF;
1654
1655 IF l_delete_dds.count > 0 THEN
1656 --
1657 -- Debug Statements
1658 --
1659 IF l_debug_on THEN
1660 WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_INTERFACE.DELETE_DETAILS',WSH_DEBUG_SV.C_PROC_LEVEL);
1661 END IF;
1662 --
1663 WSH_INTERFACE.Delete_Details(
1664 p_details_id => l_delete_dds,
1665 x_return_status => l_return_status
1666 );
1667 --
1668 -- Debug Statements
1669 --
1670
1671 IF l_debug_on THEN
1672 WSH_DEBUG_SV.logmsg(l_module_name, 'AFTER DELETING DETAIL '|| L_RETURN_STATUS );
1673 END IF;
1674 --
1675 /* Bug 2308509 */
1676 IF l_return_status IN (WSH_UTIL_CORE.G_RET_STS_ERROR,
1677 WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR
1678 )THEN
1679 x_return_status := l_return_status;
1680 RAISE backorder_error;
1681 ELSIF l_return_status = WSH_UTIL_CORE.G_RET_STS_WARNING THEN
1682 l_num_warn := l_num_warn + 1;
1683 END IF;
1684 /* End of Bug 2308509 */
1685 END IF; -- l_delete_dds.count > 0
1686
1687 /*Bug#:2026895*/
1688 if p_bo_mode = 'CYCLE_COUNT' then
1689 --
1690 -- Debug Statements
1691 --
1692 IF l_debug_on THEN
1693 WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_DELIVERY_VALIDATIONS.CHECK_SHIP_SET',WSH_DEBUG_SV.C_PROC_LEVEL);
1694 END IF;
1695 --
1696 wsh_delivery_validations.check_ship_set( l_delivery_id, l_valid_flag, l_return_status);
1697 /* Bug 2308509 */
1698 IF l_return_status IN (WSH_UTIL_CORE.G_RET_STS_ERROR,
1699 WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR
1700 )THEN
1701 x_return_status := l_return_status;
1702 RAISE backorder_error;
1703 ELSIF l_return_status = WSH_UTIL_CORE.G_RET_STS_WARNING THEN
1704 l_num_warn := l_num_warn + 1;
1705 END IF;
1706 /* End of Bug 2308509 */
1707
1708 --
1709 -- K LPN CONV. rv
1710 IF l_debug_on THEN
1711 WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_LPN_SYNC_COMM_PKG.SYNC_LPNS_TO_WMS',WSH_DEBUG_SV.C_PROC_LEVEL);
1712 END IF;
1713 --
1714 --
1715 IF WSH_WMS_LPN_GRP.G_CALLBACK_REQUIRED = 'Y'
1716 THEN
1717 --{
1718
1719 WSH_LPN_SYNC_COMM_PKG.SYNC_LPNS_TO_WMS
1720 (
1721 p_in_rec => l_lpn_in_sync_comm_rec,
1722 x_return_status => l_return_status,
1723 x_out_rec => l_lpn_out_sync_comm_rec
1724 );
1725 --
1726 --
1727 IF l_debug_on THEN
1728 WSH_DEBUG_SV.log(l_module_name,'Return status after calling WSH_LPN_SYNC_COMM_PKG.SYNC_LPNS_TO_WMS',l_return_status);
1729 END IF;
1730 --
1731 --
1732 IF l_return_status IN (WSH_UTIL_CORE.G_RET_STS_ERROR,
1733 WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR
1734 )THEN
1735 x_return_status := l_return_status;
1736 RAISE backorder_error;
1737 ELSIF l_return_status = WSH_UTIL_CORE.G_RET_STS_WARNING THEN
1738 l_num_warn := l_num_warn + 1;
1739 END IF;
1740 --}
1741 END IF;
1742 --
1743 -- K LPN CONV. rv
1744 --
1745
1746
1747 /* Warning Handling for Bug 2308509 */
1748 IF (l_num_warn > 0
1749 AND l_return_status NOT IN (WSH_UTIL_CORE.G_RET_STS_ERROR,
1750 WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR)
1751 )THEN
1752 x_return_status := WSH_UTIL_CORE.G_RET_STS_WARNING;
1753 WSH_UTIL_CORE.ADD_MESSAGE(x_return_status,l_module_name);
1754 -- for handling all the cases
1755 ELSIF l_return_status IN (WSH_UTIL_CORE.G_RET_STS_ERROR,
1756 WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR) THEN
1757 x_return_status := l_return_status;
1758 RAISE backorder_error;
1759 END IF;
1760 /* End of Warning Handling for Bug 2308509 */
1761
1762 IF NOT (l_valid_flag) THEN
1763 FND_MESSAGE.SET_NAME('WSH','WSH_DEL_SHIP_SET_INCOMPLETE');
1764 --
1765 -- Debug Statements
1766 --
1767 IF l_debug_on THEN
1768 WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_NEW_DELIVERIES_PVT.GET_NAME',WSH_DEBUG_SV.C_PROC_LEVEL);
1769 END IF;
1770 --
1771 FND_MESSAGE.SET_TOKEN('DEL_NAME',wsh_new_deliveries_pvt.get_name(l_delivery_id));
1772 x_return_status := WSH_UTIL_CORE.G_RET_STS_WARNING;
1773 WSH_UTIL_CORE.ADD_MESSAGE(x_return_status,l_module_name);
1774 END IF;
1775 end if;
1776
1777
1778 --Bugfix 4070732 {
1779 IF upper(WSH_UTIL_CORE.G_START_OF_SESSION_API) = upper(l_api_session_name) THEN
1780 --{
1781 IF NOT(WSH_UTIL_CORE.G_CALL_FTE_LOAD_TENDER_API) THEN
1782 --{
1783 IF l_debug_on THEN
1784 WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_UTIL_CORE.Process_stops_for_load_tender',WSH_DEBUG_SV.C_PROC_LEVEL);
1785 END IF;
1786
1787 WSH_UTIL_CORE.Process_stops_for_load_tender(p_reset_flags => TRUE,
1788 x_return_status => l_return_status);
1789
1790 IF l_debug_on THEN
1791 WSH_DEBUG_SV.log(l_module_name,'l_return_status',l_return_status);
1792 END IF;
1793
1794 IF l_return_status <> WSH_UTIL_CORE.G_RET_STS_SUCCESS THEN
1795 x_return_status := l_return_status;
1796
1797 IF l_return_status IN (WSH_UTIL_CORE.G_RET_STS_ERROR,
1798 WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR)
1799 THEN
1800 ROLLBACK TO before_backorder;
1801 END IF;
1802 END IF;
1803 --}
1804 END IF;
1805 --}
1806 END IF;
1807 --}
1808 --
1809 -- Debug Statements
1810 --
1811 IF l_debug_on THEN
1812 WSH_DEBUG_SV.pop(l_module_name);
1813 END IF;
1814 --
1815 EXCEPTION
1816
1817 WHEN e_return_excp THEN
1818
1819 --
1820 -- K LPN CONV. rv
1821 --
1822 IF WSH_WMS_LPN_GRP.G_CALLBACK_REQUIRED = 'Y'
1823 THEN
1824 --{
1825 IF l_debug_on THEN
1826 WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_LPN_SYNC_COMM_PKG.SYNC_LPNS_TO_WMS',WSH_DEBUG_SV.C_PROC_LEVEL);
1827 END IF;
1828
1829 WSH_LPN_SYNC_COMM_PKG.SYNC_LPNS_TO_WMS
1830 (
1831 p_in_rec => l_lpn_in_sync_comm_rec,
1832 x_return_status => l_return_status,
1833 x_out_rec => l_lpn_out_sync_comm_rec
1834 );
1835 --
1836 --
1837 IF l_debug_on THEN
1838 WSH_DEBUG_SV.log(l_module_name,'Return status after calling WSH_LPN_SYNC_COMM_PKG.SYNC_LPNS_TO_WMS',l_return_status);
1839 END IF;
1840 --
1841 --
1842 IF (l_return_status IN (WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR,WSH_UTIL_CORE.G_RET_STS_ERROR) AND x_return_status <> WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR) THEN
1843 x_return_status := l_return_status;
1844 END IF;
1845 --
1846 --}
1847 END IF;
1848 --
1849 -- K LPN CONV. rv
1850 --
1851 IF l_debug_on THEN
1852 WSH_DEBUG_SV.logmsg(l_module_name,'E_RETURN_EXCP exception has occured.',WSH_DEBUG_SV.C_EXCEP_LEVEL);
1853 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:E_RETURN_EXCP');
1854 END IF;
1855 --
1856
1857
1858 WHEN backorder_error THEN
1859 -- code sets messages before raising backorder_error.
1860 ROLLBACK to before_backorder;
1861 --
1862 -- Debug Statements
1863 --
1864 --Bugfix 4070732 {
1865 IF upper(WSH_UTIL_CORE.G_START_OF_SESSION_API) = upper(l_api_session_name) THEN
1866 IF NOT(WSH_UTIL_CORE.G_CALL_FTE_LOAD_TENDER_API) THEN
1867 IF l_debug_on THEN
1868 WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_UTIL_CORE.Reset_stops_for_load_tender',WSH_DEBUG_SV.C_PROC_LEVEL);
1869 END IF;
1870
1871 WSH_UTIL_CORE.Reset_stops_for_load_tender(p_reset_flags => TRUE,
1872 x_return_status => l_return_status);
1873
1874
1875 IF l_debug_on THEN
1876 WSH_DEBUG_SV.log(l_module_name,'l_return_status',l_return_status);
1877 END IF;
1878
1879 IF (l_return_status = WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR) THEN
1880 x_return_status := l_return_status;
1881 END IF;
1882 END IF;
1883 END IF;
1884 --}
1885 IF l_debug_on THEN
1886 WSH_DEBUG_SV.logmsg(l_module_name,'BACKORDER_ERROR exception has occured.',WSH_DEBUG_SV.C_EXCEP_LEVEL);
1887 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:BACKORDER_ERROR');
1888 END IF;
1889 --
1890 RETURN;
1891
1892 -- HW 3457369
1893 -- HW OPMCONV. Removed OPM exception
1894 --
1895
1896 /*Bug 2096052 added exception */
1897 --
1898 -- Debug Statements
1899 --
1900 --
1901 WHEN invalid_source_code THEN
1902 fnd_message.set_name('WSH','WSH_INVALID_SHIP_MODE');
1903 wsh_util_core.add_message(x_return_status,l_module_name);
1904 x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
1905 ROLLBACK to before_backorder ;
1906 /* End of changes for 2096052 */
1907
1908 --Bugfix 4070732 {
1909 IF upper(WSH_UTIL_CORE.G_START_OF_SESSION_API) = upper(l_api_session_name) THEN
1910 IF NOT(WSH_UTIL_CORE.G_CALL_FTE_LOAD_TENDER_API) THEN
1911 IF l_debug_on THEN
1912 WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_UTIL_CORE.Reset_stops_for_load_tender',WSH_DEBUG_SV.C_PROC_LEVEL);
1913 END IF;
1914
1915 WSH_UTIL_CORE.Reset_stops_for_load_tender(p_reset_flags => TRUE,
1916 x_return_status => l_return_status);
1917
1918
1919 IF l_debug_on THEN
1920 WSH_DEBUG_SV.log(l_module_name,'l_return_status',l_return_status);
1921 END IF;
1922
1923 IF (l_return_status = WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR) THEN
1924 x_return_status := l_return_status;
1925 END IF;
1926
1927 END IF;
1928 END IF;
1929 --}
1930 /* Bug 2399729 disallow backorder full for third party warehouse shipment line */
1931 --
1932 -- Debug Statements
1933 --
1934 IF l_debug_on THEN
1935 WSH_DEBUG_SV.logmsg(l_module_name,'INVALID_SOURCE_CODE exception has occured.',WSH_DEBUG_SV.C_EXCEP_LEVEL);
1936 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:INVALID_SOURCE_CODE');
1937 END IF;
1938 --
1939 WHEN no_backorder_full THEN
1940 fnd_message.set_name('WSH','WSH_BKALL_NOT_ALLOW');
1941 x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
1942 wsh_util_core.add_message(x_return_status,l_module_name);
1943 ROLLBACK to before_backorder ;
1944
1945 --Bugfix 4070732 {
1946 IF upper(WSH_UTIL_CORE.G_START_OF_SESSION_API) = upper(l_api_session_name) THEN
1947 IF NOT(WSH_UTIL_CORE.G_CALL_FTE_LOAD_TENDER_API) THEN
1948 IF l_debug_on THEN
1949 WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_UTIL_CORE.Reset_stops_for_load_tender',WSH_DEBUG_SV.C_PROC_LEVEL);
1950 END IF;
1951
1952 WSH_UTIL_CORE.Reset_stops_for_load_tender(p_reset_flags => TRUE,
1953 x_return_status => l_return_status);
1954
1955
1956 IF l_debug_on THEN
1957 WSH_DEBUG_SV.log(l_module_name,'l_return_status',l_return_status);
1958 END IF;
1959
1960 IF (l_return_status = WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR) THEN
1961 x_return_status := l_return_status;
1962 END IF;
1963
1964 END IF;
1965 END IF;
1966 --}
1967
1968 --
1969 -- Debug Statements
1970 --
1971 IF l_debug_on THEN
1972 WSH_DEBUG_SV.logmsg(l_module_name,'NO_BACKORDER_FULL exception has occured.',WSH_DEBUG_SV.C_EXCEP_LEVEL);
1973 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:NO_BACKORDER_FULL');
1974 END IF;
1975 --
1976 WHEN new_det_wt_vol_failed THEN
1977 x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
1978 fnd_message.set_name('WSH', 'WSH_DET_WT_VOL_FAILED');
1979 FND_MESSAGE.SET_TOKEN('DETAIL_ID',l_detail_rec.delivery_detail_id );
1980 wsh_util_core.add_message(x_return_status,l_module_name);
1981
1982 --
1983 -- K LPN CONV. rv
1984 --
1985 IF WSH_WMS_LPN_GRP.G_CALLBACK_REQUIRED = 'Y'
1986 THEN
1987 --{
1988 IF l_debug_on THEN
1989 WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_LPN_SYNC_COMM_PKG.SYNC_LPNS_TO_WMS',WSH_DEBUG_SV.C_PROC_LEVEL);
1990 END IF;
1991
1992 WSH_LPN_SYNC_COMM_PKG.SYNC_LPNS_TO_WMS
1993 (
1994 p_in_rec => l_lpn_in_sync_comm_rec,
1995 x_return_status => l_return_status,
1996 x_out_rec => l_lpn_out_sync_comm_rec
1997 );
1998 --
1999 --
2000 IF l_debug_on THEN
2001 WSH_DEBUG_SV.log(l_module_name,'Return status after calling WSH_LPN_SYNC_COMM_PKG.SYNC_LPNS_TO_WMS',l_return_status);
2002 END IF;
2003 --
2004 --
2005 IF (l_return_status = WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR) THEN
2006 x_return_status := l_return_status;
2007 END IF;
2008 --
2009 --}
2010 END IF;
2011 --
2012 -- K LPN CONV. rv
2013 --
2014 --Bugfix 4070732 {
2015 IF upper(WSH_UTIL_CORE.G_START_OF_SESSION_API) = upper(l_api_session_name) THEN
2016 IF NOT(WSH_UTIL_CORE.G_CALL_FTE_LOAD_TENDER_API) THEN
2017 IF l_debug_on THEN
2018 WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_UTIL_CORE.Reset_stops_for_load_tender',WSH_DEBUG_SV.C_PROC_LEVEL);
2019 END IF;
2020
2021 WSH_UTIL_CORE.Reset_stops_for_load_tender(p_reset_flags => TRUE,
2022 x_return_status => l_return_status);
2023
2024
2025 IF l_debug_on THEN
2026 WSH_DEBUG_SV.log(l_module_name,'l_return_status',l_return_status);
2027 END IF;
2028
2029 IF (l_return_status = WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR) THEN
2030 x_return_status := l_return_status;
2031 END IF;
2032
2033 END IF;
2034 END IF;
2035 --}
2036 --
2037 -- Debug Statements
2038 --
2039 IF l_debug_on THEN
2040 WSH_DEBUG_SV.logmsg(l_module_name,'NEW_DET_WT_VOL_FAILED exception has occured.',WSH_DEBUG_SV.C_EXCEP_LEVEL);
2041 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:NEW_DET_WT_VOL_FAILED');
2042 END IF;
2043 --
2044 WHEN OTHERS THEN
2045 WSH_UTIL_CORE.Default_Handler('WSH_SHIP_CONFIRM_ACTIONS2.Backorder',l_module_name);
2046 x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR;
2047 ROLLBACK to before_backorder ;
2048 --Bugfix 4070732 {
2049 IF upper(WSH_UTIL_CORE.G_START_OF_SESSION_API) = upper(l_api_session_name) THEN
2050 IF NOT(WSH_UTIL_CORE.G_CALL_FTE_LOAD_TENDER_API) THEN
2051 IF l_debug_on THEN
2052 WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_UTIL_CORE.Reset_stops_for_load_tender',WSH_DEBUG_SV.C_PROC_LEVEL);
2053 END IF;
2054
2055 WSH_UTIL_CORE.Reset_stops_for_load_tender(p_reset_flags => TRUE,
2056 x_return_status => l_return_status);
2057
2058
2059 IF l_debug_on THEN
2060 WSH_DEBUG_SV.log(l_module_name,'l_return_status',l_return_status);
2061 END IF;
2062 END IF;
2063 END IF;
2064 --}
2065 --
2066 -- Debug Statements
2067 IF l_debug_on THEN
2068 WSH_DEBUG_SV.logmsg(l_module_name,'Unexpected error has occured. Oracle error message is '|| SQLERRM,WSH_DEBUG_SV.C_UNEXPEC_ERR_LEVEL);
2069 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:OTHERS');
2070 END IF;
2071
2072 RETURN ;
2073
2074 END Backorder;
2075
2076 PROCEDURE check_exception(
2077 p_delivery_detail_id IN NUMBER
2078 , x_exception_exist OUT NOCOPY VARCHAR2
2079 , x_severity_present OUT NOCOPY VARCHAR2
2080 , x_return_status OUT NOCOPY VARCHAR2)
2081 IS
2082
2083 l_not_handled VARCHAR2(30):='NOT_HANDLED';
2084 l_no_action_reqd VARCHAR2(30):='NO_ACTION_REQUIRED';
2085 l_closed VARCHAR2(30):='CLOSED';
2086
2087 CURSOR c_exception IS
2088 SELECT decode(severity,'HIGH','H','MEDIUM','M','L') severity
2089 FROM wsh_exceptions
2090 WHERE delivery_detail_id = p_delivery_detail_id
2091 AND status not in (l_not_handled , l_no_action_reqd , l_closed)
2092 ORDER BY decode(severity,'H',1,'M',2,3);
2093
2094 l_count NUMBER := 0;
2095 --
2096 l_debug_on BOOLEAN;
2097 --
2098 l_module_name CONSTANT VARCHAR2(100) := 'wsh.plsql.' || G_PKG_NAME || '.' || 'CHECK_EXCEPTION';
2099 --
2100 BEGIN
2101 --
2102 -- Debug Statements
2103 --
2104 --
2105 l_debug_on := WSH_DEBUG_INTERFACE.g_debug;
2106 --
2107 IF l_debug_on IS NULL
2108 THEN
2109 l_debug_on := WSH_DEBUG_SV.is_debug_enabled;
2110 END IF;
2111 --
2112 IF l_debug_on THEN
2113 WSH_DEBUG_SV.push(l_module_name);
2114 --
2115 WSH_DEBUG_SV.log(l_module_name,'P_DELIVERY_DETAIL_ID',P_DELIVERY_DETAIL_ID);
2116 END IF;
2117 --
2118 x_return_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
2119
2120 OPEN c_exception;
2121 FETCH c_exception INTO x_severity_present;
2122 IF c_exception%NOTFOUND THEN
2123 x_exception_exist := 'N';
2124 ELSE
2125 x_exception_exist := 'Y';
2126 END IF;
2127 CLOSE c_exception;
2128
2129 IF l_debug_on THEN
2130 WSH_DEBUG_SV.log(l_module_name,'x_exception_exist',x_exception_exist);
2131 WSH_DEBUG_SV.log(l_module_name,'x_severity_present',x_severity_present);
2132 END IF;
2133
2134
2135 --
2136 -- Debug Statements
2137 --
2138 IF l_debug_on THEN
2139 WSH_DEBUG_SV.pop(l_module_name);
2140 END IF;
2141 --
2142 EXCEPTION
2143 WHEN others THEN
2144 x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR;
2145 WSH_UTIL_CORE.default_handler('WSH_SHIP_CONFIRM_ACTIONS2.check_exception',l_module_name);
2146
2147 IF l_debug_on THEN
2148 WSH_DEBUG_SV.logmsg(l_module_name,'Unexpected error has occured. Oracle error message is '|| SQLERRM,WSH_DEBUG_SV.C_UNEXPEC_ERR_LEVEL);
2149 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:OTHERS');
2150 END IF;
2151 --
2152 END check_exception;
2153
2154 --
2155 -- Procedure: Backorder
2156 -- Description: This is a wrapper of the BackOrder procedure already present in the package.
2157 -- This is introduced for Consolidation of BO Delivery Details project.
2158 -- This wrapper avoids the change of the calls made to Backorder api from different apis
2159 -- (as additional parameter is added to the original backorder api).
2160 --
2161 PROCEDURE Backorder(
2162 p_detail_ids IN WSH_UTIL_CORE.Id_Tab_Type,
2163 p_bo_qtys IN WSH_UTIL_CORE.Id_Tab_Type,
2164 p_req_qtys IN WSH_UTIL_CORE.Id_Tab_Type,
2165 p_bo_qtys2 IN WSH_UTIL_CORE.Id_Tab_Type,
2166 p_overpick_qtys IN WSH_UTIL_CORE.Id_Tab_Type,
2167 p_overpick_qtys2 IN WSH_UTIL_CORE.Id_Tab_Type,
2168 p_bo_mode IN VARCHAR2,
2169 p_bo_source IN VARCHAR2 DEFAULT 'SHIP',
2170 x_out_rows OUT NOCOPY WSH_UTIL_CORE.Id_Tab_Type,
2171 x_return_status OUT NOCOPY VARCHAR2 )
2172 IS
2173
2174 l_line_ids WSH_UTIL_CORE.Id_Tab_Type;
2175 l_cons_flags WSH_UTIL_CORE.Column_Tab_Type;
2176 l_sources WSH_UTIL_CORE.Column_Tab_Type; -- RTV BO Cons Changes
2177 l_return_status VARCHAR2(1);
2178
2179 --
2180 l_debug_on BOOLEAN;
2181 --
2182 l_module_name CONSTANT VARCHAR2(100) := 'wsh.plsql.' || G_PKG_NAME || '.' || 'BACKORDER';
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_BO_MODE',P_BO_MODE);
2200 WSH_DEBUG_SV.log(l_module_name,'P_BO_SOURCE',P_BO_SOURCE);
2201 END IF;
2202 --
2203
2204 --
2205 -- Debug Statements
2206 --
2207 IF l_debug_on THEN
2208 WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_SHIP_CONFIRM_ACTIONS2.BackOrder',WSH_DEBUG_SV.C_PROC_LEVEL);
2209 WSH_DEBUG_SV.logmsg(l_module_name,'......with p_line_ids as NULL', WSH_DEBUG_SV.C_PROC_LEVEL);
2210 END IF;
2211 --
2212 Backorder(
2213 p_detail_ids => p_detail_ids,
2214 p_line_ids => l_line_ids,
2215 p_bo_qtys => p_bo_qtys,
2216 p_req_qtys => p_req_qtys,
2217 p_bo_qtys2 => p_bo_qtys2,
2218 p_overpick_qtys => p_overpick_qtys,
2219 p_overpick_qtys2 => p_overpick_qtys2,
2220 p_sources => l_sources, -- RTV BO Cons Changes
2221 p_bo_mode => p_bo_mode,
2222 p_bo_source => p_bo_source,
2223 x_out_rows => x_out_rows,
2224 x_cons_flags => l_cons_flags,
2225 x_return_status => x_return_status );
2226
2227 IF l_debug_on THEN
2228 WSH_DEBUG_SV.logmsg(l_module_name,'x_return_status '|| x_return_status, WSH_DEBUG_SV.C_PROC_LEVEL);
2229 WSH_DEBUG_SV.pop(l_module_name);
2230 END IF;
2231
2232 END BackOrder;
2233
2234 END WSH_SHIP_CONFIRM_ACTIONS2;