[Home] [Help]
PACKAGE BODY: APPS.WSH_SHIP_CONFIRM_ACTIONS2
Source
1 PACKAGE BODY WSH_SHIP_CONFIRM_ACTIONS2 as
2 /* $Header: WSHDDSPB.pls 120.8.12010000.3 2008/11/06 07:10:31 gbhargav 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_bo_mode IN VARCHAR2,
327 p_bo_source IN VARCHAR2 DEFAULT 'SHIP',
328 x_out_rows OUT NOCOPY WSH_UTIL_CORE.Id_Tab_Type,
329 x_cons_flags OUT NOCOPY WSH_UTIL_CORE.Column_Tab_Type, -- Consolidation of BO Delivery Details project
330 x_return_status OUT NOCOPY VARCHAR2 )
331 IS
332 CURSOR c_detail(x_detail_id NUMBER) IS
333 --Changed for BUG#3330869
334 --SELECT *
335 SELECT source_line_id,
336 source_code,
337 picked_quantity,
338 delivery_detail_id,
339 released_status,
340 pickable_flag,
341 organization_id,
342 inventory_item_id,
343 requested_quantity,
344 serial_number,
345 transaction_temp_id,
346 subinventory
347 FROM wsh_delivery_details
348 WHERE delivery_detail_id = x_detail_id AND
349 NVL(container_flag, 'N') = 'N';
350 l_detail_rec c_detail%ROWTYPE;
351
352 l_delivery_id NUMBER;
353 l_return_status VARCHAR2(30);
354 l_exception_exist VARCHAR2(1);
355 l_user_id NUMBER := NULL;
356 l_login_id NUMBER := NULL;
357 l_trohdr_rec INV_MOVE_ORDER_PUB.Trohdr_Rec_Type;
358 l_trohdr_val_rec INV_MOVE_ORDER_PUB.Trohdr_Val_Rec_Type;
359 l_trolin_tbl INV_MOVE_ORDER_PUB.Trolin_Tbl_Type;
360 l_trolin_val_tbl INV_MOVE_ORDER_PUB.Trolin_Val_Tbl_Type;
361 l_cr_hdr_return_status varchar2(30) := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
362 l_cr_hdr_message varchar2(4000) := NULL;
363 l_cr_hdr_msg_count number:= NULL;
364 l_cr_hdr_msg_data varchar2(4000) := NULL;
365 l_cr_ln_return_status varchar2(30) := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
366 l_cr_ln_msg_count number := NULL;
367 l_cr_ln_msg_data varchar2(4000) := NULL;
368 l_cr_ln_message varchar2(4000) := NULL;
369 l_sales_order_id NUMBER := NULL;
370 l_date DATE := SYSDATE;
371
372 -- fabdi start : SHIPPING PIECE 12/09/2000
373
374 l_message_count NUMBER;
375 l_message_data VARCHAR2(3000);
376
377 l_to_subinventory VARCHAR2(10);
378 l_to_locator NUMBER;
379 l_commit VARCHAR2(1) := FND_API.G_FALSE;
380 l_default_to_sub VARCHAR2(10);
381 l_default_to_loc NUMBER;
382 l_message VARCHAR2(2000);
383
384 -- hverddin : Begin of OPM Backorder Changes 30-OCT-00
385
386 l_batch_id NUMBER;
387 l_request_number VARCHAR(30);
388 l_wf_rs VARCHAR2(1); --Pick To POD WF Project
389
390 -- hverddin : End of OPM Backorder Changes 30-OCT-00
391
392
393 -- fabdi end : SHIPPING PIECE 12/09/2000
394
395 i NUMBER;
396 /* Bug#: 2026895 Added the second decode statement */
397
398 /* CURSOR c_assigned(c_delivery_detail_id number) is
399 SELECT decode ( delivery_id , null , 'N' , 'Y' ),
400 decode( delivery_id,null,-9999999,delivery_id)
401 FROM wsh_delivery_assignments_v
402 WHERE delivery_Detail_id = c_delivery_detail_id ; */
403
404 -- bug# 6908504 (replenishment project), Changed the cursor definition. Now it returns planned_flag value from WND table.
405 CURSOR c_assigned(c_delivery_detail_id number) is
406 SELECT wnd.delivery_id,
407 wnd.planned_flag
408 FROM wsh_new_deliveries wnd,
409 wsh_delivery_details wdd,
410 wsh_delivery_assignments_v wda
411 WHERE wdd.delivery_detail_id = c_delivery_detail_id
412 AND wda.delivery_id = wnd.delivery_id
413 AND wda.delivery_detail_id = wdd.delivery_detail_id ;
414
415
416 l_assigned_flag VARCHAR2(1);
417 /*Bug#:2026895*/
418 l_valid_flag BOOLEAN;
419
420 backorder_error EXCEPTION;
421 -- HW 3457369
422 -- HW OPMCONV. Removed local variables
423
424 l_reserved_flag VARCHAR2(1);
425 l_bo_detail_id NUMBER;
426
427 l_bo_qty NUMBER ;
428 l_bo_qty2 NUMBER ;
429 l_unassign_dds WSH_UTIL_CORE.Id_Tab_Type ;
430 l_unassign_dds_tmp WSH_UTIL_CORE.Id_Tab_Type ; -- bug 7460785
431 l_delete_dds WSH_UTIL_CORE.Id_Tab_Type ; -- to delete overpicked delivery lines
432 l_inv_controls_rec WSH_DELIVERY_DETAILS_INV.inv_control_flag_rec;
433
434 invalid_source_code EXCEPTION; /*Bug 2096052- added for OKE */
435 no_backorder_full EXCEPTION; /*Bug 2399729- added for third party warehouse shipment line */
436 l_backorder_all VARCHAR2(1) := 'N'; /* Bug 2399729 */
437 -- additional variables for bug 2056874
438 new_det_wt_vol_failed exception;
439 l_split_weight number;
440 l_split_volume number;
441 -- bug 2056874
442
443 l_num_warn NUMBER := 0;
444 l_num_err NUMBER := 0;
445
446 l_subinventory VARCHAR2(10) := NULL;
447
448 -- bug 2730685: need to know if we are backordering
449 -- an unshipped, overpicked line packed in container
450 l_new_picked_quantity NUMBER;
451
452 -- Newly added variables for Consolidation of BO Delivery Details project
453 --
454 l_global_param_rec WSH_SHIPPING_PARAMS_PVT.Global_Parameters_Rec_Typ;
455 l_line_ids WSH_UTIL_CORE.Id_Tab_Type ;
456 l_detail_ids WSH_UTIL_CORE.Id_Tab_Type ;
457 -- HW 3457369
458 l_detail_ids_OPM WSH_UTIL_CORE.Id_Tab_Type ;
459 l_req_qtys WSH_UTIL_CORE.Id_Tab_Type ;
460 l_bo_qtys WSH_UTIL_CORE.Id_Tab_Type ;
461 l_overpick_qtys WSH_UTIL_CORE.Id_Tab_Type ;
462 -- HW OPM BUG#:3121616 added qty2s
463 l_bo_qty2s WSH_UTIL_CORE.Id_Tab_Type ;
464 -- end of 3121616
465 l_delivery_ids WSH_UTIL_CORE.Id_Tab_Type ;
466 l_cons_dd_ids WSH_UTIL_CORE.Id_Tab_Type ; --Stores the dd_ids returned by Consolidate_Source_Line
467 l_remain_bo_qtys WSH_UTIL_CORE.Id_Tab_Type ; --Stores the remaining bo qty returned by Consolidate_Delivery_details
468 -- HW OPM BUG#:3121616 added qty2s
469 l_remain_bo_qty2s WSH_UTIL_CORE.Id_Tab_Type ; --Stores the remaining bo qty2 returned by Consolidate_Delivery_details
470 -- end of 3121616
471 l_cons_source_line_rec_tab WSH_DELIVERY_DETAILS_ACTIONS.Cons_Source_Line_Rec_Tab;
472 l_dd_rec_tab WSH_DELIVERY_DETAILS_ACTIONS.Cons_Source_Line_Rec_Tab;
473 l_cons_dd_rec_tab WSH_DELIVERY_DETAILS_ACTIONS.Cons_Source_Line_Rec_Tab;
474 k NUMBER;
475
476 l_dd_txn_id NUMBER; --DBI
477 l_txn_return_status VARCHAR2(1); --DBI
478 --
479
480 -- HW 3457369
481 CURSOR find_org_id (l_delivery_detail_id NUMBER ) IS
482 SELECT organization_id from wsh_delivery_details
483 WHERE delivery_detail_id = l_delivery_detail_id
484 AND NVL(container_flag, 'N') = 'N';
485
486 l_organization_id NUMBER;
487 -- end of 3457369
488
489 --Begin OPM Bug 3561937
490 CURSOR get_pref_grade(wdd_id NUMBER) IS
491 SELECT oelines.preferred_grade
492 FROM oe_order_lines_all oelines, wsh_delivery_details wdd
493 WHERE wdd.delivery_detail_id = wdd_id
494 AND wdd.source_code = 'OE'
495 AND wdd.source_line_id = oelines.line_id;
496
497 -- HW OPMCONV changed size of grade to 150
498 l_oeline_pref_grade VARCHAR2(150) := NULL;
499 --End OPM Bug 3561937
500
501 l_detail_tab WSH_UTIL_CORE.id_tab_type; -- DBI Project
502 l_dbi_rs VARCHAR2(1); -- Return Status from DBI API
503
504 --
505 l_debug_on BOOLEAN;
506 --
507 l_module_name CONSTANT VARCHAR2(100) := 'wsh.plsql.' || G_PKG_NAME || '.' || 'BACKORDER';
508 --Bugfix 4070732
509 l_api_session_name CONSTANT VARCHAR2(150) := G_PKG_NAME ||'.' || l_module_name;
510 l_reset_flags BOOLEAN;
511 --
512 -- K LPN CONV. rv
513 l_lpn_in_sync_comm_rec WSH_GLBL_VAR_STRCT_GRP.lpn_sync_comm_in_rec_type;
514 l_lpn_out_sync_comm_rec WSH_GLBL_VAR_STRCT_GRP.lpn_sync_comm_out_rec_type;
515 l_msg_count NUMBER;
516 l_msg_data VARCHAR2(32767);
517 e_return_excp EXCEPTION;
518 -- K LPN CONV. rv
519 l_item_info WSH_DELIVERY_DETAILS_INV.mtl_system_items_rec;
520 --
521 --
522 l_unassign_dd_flag VARCHAR2(1) := 'N';
523 l_planned_flag VARCHAR2(1); -- bug# 6908504 (replenishment project)
524 BEGIN
525 IF WSH_UTIL_CORE.G_START_OF_SESSION_API is null THEN --Bugfix 4070732
526 WSH_UTIL_CORE.G_START_OF_SESSION_API := l_api_session_name;
527 WSH_UTIL_CORE.G_CALL_FTE_LOAD_TENDER_API := FALSE;
528 END IF;
529 --
530 -- Debug Statements
531 --
532 --
533 l_debug_on := WSH_DEBUG_INTERFACE.g_debug;
534 --
535 IF l_debug_on IS NULL
536 THEN
537 l_debug_on := WSH_DEBUG_SV.is_debug_enabled;
538 END IF;
539 --
540 IF l_debug_on THEN
541 WSH_DEBUG_SV.push(l_module_name);
542 --
543 WSH_DEBUG_SV.log(l_module_name,'P_BO_MODE',P_BO_MODE);
544 WSH_DEBUG_SV.log(l_module_name,'P_BO_SOURCE',P_BO_SOURCE);
545 END IF;
546 --
547 SAVEPOINT before_backorder;
548
549 x_return_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
550 l_return_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
551
552 -- Consolidation of BO Delivery Details project
553 -- Begin
554 l_line_ids.DELETE;
555 l_detail_ids := p_detail_ids;
556 l_detail_ids_opm := p_detail_ids;
557 l_req_qtys := p_req_qtys;
558 l_bo_qtys := p_bo_qtys;
559 l_overpick_qtys := p_overpick_qtys; -- Bug#3263952
560 -- HW OPM BUG#:3121616 added qty2s
561 l_bo_qty2s := p_bo_qtys2;
562 -- end of 3121616
563 --
564 -- Debug Statements
565 --
566 IF l_debug_on THEN
567 WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_SHIPPING_PARAMS_PVT.Get_Global_Parameters',WSH_DEBUG_SV.C_PROC_LEVEL);
568 END IF;
569 --
570 WSH_SHIPPING_PARAMS_PVT.Get_Global_Parameters(l_global_param_rec, l_return_status);
571 IF l_return_status IN (WSH_UTIL_CORE.G_RET_STS_ERROR, WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR) THEN
572 wsh_util_core.add_message(l_return_status,'WSH_DELIVERY_DETAILS_ACTIONS.Consolidate_Delivery_Details');
573 x_return_status := l_return_status;
574 RAISE backorder_error;
575 END IF;
576
577 IF l_debug_on THEN
578 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);
579 END IF;
580
581 IF (l_global_param_rec.consolidate_bo_lines = 'Y' AND p_line_ids.COUNT > 1 ) THEN
582 --{
583 -- Get the values into local pl/sql tables
584 FOR i IN p_detail_ids.FIRST .. p_detail_ids.LAST LOOP --{
585 l_dd_rec_tab(l_dd_rec_tab.count+1).delivery_detail_id := p_detail_ids(i);
586 l_dd_rec_tab(l_dd_rec_tab.count).source_line_id := p_line_ids(i);
587 l_dd_rec_tab(l_dd_rec_tab.count).req_qty := p_req_qtys(i);
588 l_dd_rec_tab(l_dd_rec_tab.count).bo_qty := p_bo_qtys(i);
589 l_dd_rec_tab(l_dd_rec_tab.count).overpick_qty := p_overpick_qtys(i); -- Bug#3263952
590 -- HW OPM BUG#:3121616 added qty2s
591 l_dd_rec_tab(l_dd_rec_tab.count).bo_qty2 := p_bo_qtys2(i);
592 -- end of 3121616
593 END LOOP; --}
594 --
595 -- Debug Statements
596 --
597 IF l_debug_on THEN
598 WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_DELIVERY_DETAILS_ACTIONS.Consolidate_Delivery_Details',WSH_DEBUG_SV.C_PROC_LEVEL);
599 END IF;
600 --
601 -- HW OPM BUG#:3121616 Added x_remain_bo_qty2s
602 WSH_DELIVERY_DETAILS_ACTIONS.Consolidate_Delivery_Details(
603 p_delivery_details_tab => l_dd_rec_tab,
604 p_bo_mode => p_bo_mode,
605 x_cons_delivery_details_tab => l_cons_dd_rec_tab,
606 x_remain_bo_qtys => l_remain_bo_qtys,
607 x_remain_bo_qty2s => l_remain_bo_qty2s,
608 x_return_status => l_return_status
609 );
610 IF l_return_status IN (WSH_UTIL_CORE.G_RET_STS_ERROR,
611 WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR) THEN
612 wsh_util_core.add_message(l_return_status,'WSH_DELIVERY_DETAILS_ACTIONS.Consolidate_Delivery_Details');
613 x_return_status := l_return_status;
614 RAISE backorder_error;
615 END IF;
616
617 l_detail_ids.DELETE;
618 l_req_qtys.DELETE;
619 l_bo_qtys.DELETE;
620 -- HW OPM BUG#:3121616 added qty2s
621 l_bo_qty2s.DELETE;
622 -- end of 3121616
623 -- Get the values returned by Consolidate_Delivery_Details, into local pl/sql tables
624 for i IN l_cons_dd_rec_tab.FIRST .. l_cons_dd_rec_tab.LAST LOOP --{
625 l_detail_ids(i) := l_cons_dd_rec_tab(i).delivery_detail_id;
626 l_req_qtys(i) := l_cons_dd_rec_tab(i).req_qty;
627 l_bo_qtys(i) := l_cons_dd_rec_tab(i).bo_qty;
628 l_overpick_qtys(i) := l_cons_dd_rec_tab(i).overpick_qty; -- Bug#3263952
629 -- HW OPM BUG#:3121616 added qty2s
630 l_bo_qty2s(i) := l_cons_dd_rec_tab(i).bo_qty2;
631 -- end of 3121616
632 END LOOP; --}
633 IF l_debug_on THEN
634 WSH_DEBUG_SV.logmsg(l_module_name,'Total No. of delivery details '||p_detail_ids.COUNT,WSH_DEBUG_SV.C_PROC_LEVEL);
635 WSH_DEBUG_SV.logmsg(l_module_name,'No. of delivery details after Consolidation '||l_detail_ids.COUNT,WSH_DEBUG_SV.C_PROC_LEVEL);
636 END IF;
637 END IF; --}, l_global_param_rec.consolidate_bo_lines flag
638 --
639
640 -- Replaced p_detail_ids with l_detail_ids, p_req_qtys with l_req_qtys, p_bo_qtys with l_bo_qtys and
641 -- p_overpick_qtys with l_overpick_qtys, as part of code changes for Consolidation of BO Delivery Details project.
642 FOR i IN 1 .. l_detail_ids.COUNT
643 LOOP --{ loop thru' for each delivery detail id in l_detail_ids
644 /* Bug 2399729, default l_backorder_all to 'N' */
645 l_backorder_all := 'N';
646 -- 1. See if the detail is assigned to a delivery .
647 -- This check introduced because of bug 2041416 , because Backorder API
648 -- can be called now during cycle counting of delivery details which may not
649 -- be assigned to any delivery in the first place.
650
651 /*Bug#:2026895 Added l_delivery_id in the fetch statement*/
652 -- bug# 6908504 (replenishment project): checking the planned flag value.
653 open c_assigned ( l_detail_ids(i) ) ;
654 FETCH c_assigned INTO l_delivery_id,l_planned_flag;
655 CLOSE c_assigned ;
656 --
657 IF l_delivery_id IS NULL THEN
658 --{
659 l_assigned_flag := 'N';
660 l_delivery_id := -99999;
661 l_planned_flag := 'N';
662 ELSE
663 l_assigned_flag := 'Y';
664 --}
665 END IF;
666 --
667 -- Debug Statements
668 --
669 IF l_debug_on THEN
670 WSH_DEBUG_SV.logmsg(l_module_name, 'l_delivery_id: '||l_delivery_id||' ,l_planned_flag: '||l_planned_flag);
671 WSH_DEBUG_SV.logmsg(l_module_name, 'IN BACKORDER ' || I );
672 END IF;
673 --
674
675 -- bug 1672188: check there is a quantity (normal or overpicked) to backorder.
676 IF (NVL(l_bo_qtys(i), 0) <= 0)
677 AND (NVL(l_overpick_qtys(i), 0) <= 0) THEN
678 goto loop_end;
679 END IF;
680
681 -- bug 1672188: split backordered quantity if needed
682 --
683 -- Debug Statements
684 --
685 IF l_debug_on THEN
686 WSH_DEBUG_SV.logmsg(l_module_name, 'CHECKING IF ' || l_bo_qtys ( I ) || ' < ' || l_req_qtys ( I ) );
687 END IF;
688 --
689 IF (l_bo_qtys(i) > 0)
690 AND (l_bo_qtys(i) < l_req_qtys(i)) THEN
691
692 l_bo_qty := l_bo_qtys(i);
693 l_bo_qty2 := p_bo_qtys2(i);
694
695 -- wsh_util_core.println('splitting ' || l_detail_ids(i) );
696 --
697 -- Debug Statements
698 --
699 IF l_debug_on THEN
700 WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_DELIVERY_DETAILS_ACTIONS.SPLIT_DELIVERY_DETAILS',WSH_DEBUG_SV.C_PROC_LEVEL);
701 END IF;
702 --
703 wsh_delivery_details_actions.split_delivery_details(
704 p_from_detail_id => l_detail_ids(i),
705 p_req_quantity => l_bo_qty,
706 x_new_detail_id => l_bo_detail_id,
707 x_return_status => l_return_status ,
708 p_req_quantity2 => l_bo_qty2);
709
710 IF l_debug_on THEN
711 WSH_DEBUG_SV.log(l_module_name,'SPLIT_DELIVERY_DETAILS l_return_status',l_return_status);
712 END IF;
713
714 /* Bug 2308509 */
715 IF l_return_status IN (WSH_UTIL_CORE.G_RET_STS_ERROR,
716 WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR) THEN
717 wsh_util_core.add_message(l_return_status,l_module_name);
718 x_return_status := l_return_status;
719 RAISE backorder_error;
720 ELSIF l_return_status = WSH_UTIL_CORE.G_RET_STS_WARNING THEN
721 l_num_warn := l_num_warn + 1;
722 END IF;
723 /* End of Bug 2308509 */
724
725 ELSE
726 l_bo_detail_id := l_detail_ids(i);
727 /* Bug 2399729, backorder all not allowed for third party warehouse shipment line */
728 l_backorder_all := 'Y';
729
730 END IF;
731
732 /*Bug 5525314 moved up the open cursor c_detail
733 and WSH_DELIVERY_DETAILS_INV.Get_item_information procedure. */
734 /* H integration: 940/945 look up source code before unassigning wrudge*/
735 OPEN c_detail(l_bo_detail_id );
736 FETCH c_detail INTO l_detail_rec;
737 CLOSE c_detail;
738 -- bug 5233115: unable to backorder/cycle-count overpicked
739 -- lot-indivisible item because Inventory does not allow
740 -- partial reservation updates.
741 --
742 -- Solution agreed on by WMS, WSH, and OPM:
743 -- 1. Blow away lot-indivisible reservation, whether
744 -- overpicked or not.
745 -- 2. Option RETAIN_RSV will not be honored. At this time,
746 -- only WMS will pass this option (frontport bug 4721577;
747 -- base bug 4656374) when unloading truck and this option is set.
748 -- WMS will inform the user about this exception (bug 5350778).
749 --
750 -- Note that this issue can affect ATO; see the tracking bug 5350793.
751
752 IF l_detail_rec.released_status = 'Y' THEN
753 -- call this API only if line is staged, so we can test lot
754 -- divisibility.
755
756 WSH_DELIVERY_DETAILS_INV.Get_item_information
757 (
758 p_organization_id => l_detail_rec.organization_id,
759 p_inventory_item_id => l_detail_rec.inventory_item_id,
760 x_mtl_system_items_rec => l_item_info,
761 x_return_status => l_return_status
762 );
763
764 IF l_return_status IN (WSH_UTIL_CORE.G_RET_STS_ERROR,
765 WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR) THEN
766 -- this is unlikely to happen
767 fnd_message.set_name('WSH', 'WSH_INVALID_INV_ITEM');
768 fnd_message.set_token('ITEM_NAME', l_detail_rec.inventory_item_id);
769 wsh_util_core.add_message(l_return_status,l_module_name);
770 x_return_status := l_return_status;
771 RAISE backorder_error;
772 END IF;
773 ELSE
774 l_item_info.lot_divisible_flag := NULL;
775 l_item_info.lot_control_code := NULL;
776 END IF;
777
778 IF l_debug_on THEN
779 wsh_debug_sv.LOG(l_module_name, 'l_item_info.lot_divisible_flag', l_item_info.lot_divisible_flag);
780 wsh_debug_sv.LOG(l_module_name, 'l_item_info.lot_control_code', l_item_info.lot_control_code);
781 wsh_debug_sv.LOG(l_module_name, 'l_detail_rec.released_status', l_detail_rec.released_status);
782 END IF;
783 -- check for overpicking
784 IF (l_overpick_qtys(i) > 0) THEN --Bug 2026099: Removed other conditions
785 -- if we are backordering overpicked quantity,
786 -- and the line has been split or the line is fully overpicked,
787 -- then we need to decrement the picked quantities.
788 -- assumption: action Cycle Count and Ship Confirm ensure
789 -- that shipped_quantity won't be affected by the reduction.
790 -- (Viz., if req_qty = 0, cycle count quantities must be 0.)
791
792 --Bug 5525314
793 --If the item is lot indivisible then over picked qty should be considered.
794 IF (l_item_info.lot_divisible_flag = 'N' AND
795 l_item_info.lot_control_code = 2 ) THEN
796 l_new_picked_quantity := l_detail_rec.picked_quantity ;
797 ELSE
798 UPDATE wsh_delivery_details
799 SET picked_quantity = picked_quantity - l_overpick_qtys(i),
800 picked_quantity2 = picked_quantity2 - p_overpick_qtys2(i)
801 WHERE l_detail_ids(i) = delivery_detail_id
802 RETURNING picked_quantity INTO l_new_picked_quantity;
803
804 --bug 7166138 new picked quantity should be updated in l_detail_rec
805 l_detail_rec.picked_quantity := l_new_picked_quantity;
806
807 END IF;
808 ELSE
809 l_new_picked_quantity := NULL;
810 END IF;
811
812 IF (NVL(l_global_param_rec.consolidate_bo_lines,'N') = 'N') THEN
813 IF (l_bo_qtys(i) > 0) THEN
814 -- make a list for temporary query in STF (e.g., cycle-count).
815 x_out_rows ( x_out_rows.count + 1 ) := l_bo_detail_id ;
816 END IF;
817 END IF;
818 --
819
820 -- bug 2730685: if a purely overpicked but completely unshipped line is packed,
821 -- it needs to be unpacked so that it can be deleted,
822 -- instead of becoming "shipped" with zero req/pick quantity
823 -- Need to check for l_bo_qtys(i) = 0
824 IF ( (l_bo_qtys(i) > 0)
825 OR (l_new_picked_quantity = 0)
826 ) THEN
827
828 /* H integration: 940/945 WSH should remain assigned wrudge */
829 -- bug# 6908504 (replenishment project): Should not un-assign the planned deliveries when called the api
830 -- with p_bo_source as PICK.
831 IF( l_assigned_flag = 'Y'
832 AND l_detail_rec.source_code <> 'WSH'
833 AND ( p_bo_source <> 'PICK' OR ( p_bo_source = 'PICK' AND l_planned_flag <> 'Y')) ) THEN
834 --{
835 --
836 -- Debug Statements
837 --
838 IF l_debug_on THEN
839 WSH_DEBUG_SV.logmsg(l_module_name, ' NEW DD TO UNASSIGN -' || L_BO_DETAIL_ID || ' TO L_UNASSIGN_DDS' );
840 END IF;
841 --
842 l_unassign_dd_flag := 'Y'; --deferred to the end.
843 l_unassign_dds(l_unassign_dds.count + 1 ) := l_bo_detail_id ;
844 --}
845 END IF;
846 --}
847 END IF;
848
849 /*Bug 2096052 for OKE*/
850 IF l_detail_rec.source_code = 'OKE' THEN
851 RAISE invalid_source_code;
852 /* H integration: 945 backordering cancels line wrudge */
853 ELSIF l_detail_rec.source_code = 'WSH' THEN
854 IF l_backorder_all = 'Y' THEN
855 RAISE no_backorder_full;
856 ELSE
857 l_delete_dds( l_delete_dds.count+1 ) := l_bo_detail_id;
858 END IF;
859 END IF;
860
861 /* End of Code added for Bug 2096052*/
862
863 IF l_req_qtys(i) = 0
864 AND l_detail_ids(i) = l_bo_detail_id
865 AND l_detail_rec.picked_quantity = 0 THEN -- Bug 2026260
866 -- If the line is fully overpicked and not split
867 -- and its picked quantity is completely backordered/cycle-counted,
868 -- then mark it as deleted.
869 l_delete_dds( l_delete_dds.count+1 ) := l_detail_ids(i);
870 END IF;
871 -- bug 1672188: unreserve if the detail is reserved
872 l_reserved_flag :=WSH_DELIVERY_DETAILS_INV.LINE_RESERVED(
873 p_detail_id => l_detail_rec.delivery_detail_id,
874 p_source_code => l_detail_rec.source_code,
875 p_released_status => l_detail_rec.released_status,
876 p_pickable_flag => l_detail_rec.pickable_flag,
877 p_organization_id => l_detail_rec.organization_id,
878 p_inventory_item_id => l_detail_rec.inventory_item_id,
879 x_return_status => l_return_status);
880 IF l_debug_on THEN
881 WSH_DEBUG_SV.log(l_module_name,'l_return_status',l_return_status);
882 END IF;
883
884 /* Bug 2308509 */
885 IF l_return_status IN (WSH_UTIL_CORE.G_RET_STS_ERROR,
886 WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR
887 ) THEN
888 fnd_message.set_name('WSH', 'WSH_BO_RESERVED_ERROR');
889 fnd_message.set_token('DETAIL_ID', l_detail_ids(i));
890 wsh_util_core.add_message(l_return_status,l_module_name);
891 x_return_status := l_return_status;
892 RAISE backorder_error;
893 ELSIF l_return_status = WSH_UTIL_CORE.G_RET_STS_WARNING THEN
894 l_num_warn := l_num_warn + 1;
895 END IF;
896 /* End ofBug 2308509 */
897
898 --
899 -- Debug Statements
900 --
901 IF l_debug_on THEN
902 WSH_DEBUG_SV.logmsg(l_module_name, 'RESERVED FLAG = ' || L_RESERVED_FLAG );
903 END IF;
904 --
905 -- wsh_util_core.println('BO Mode = ' || p_bo_mode );
906 -- HW BUG#:2005977
907
908
909 -- HW OPMCONV. Removed branching
910
911 IF l_reserved_flag = 'Y' THEN
912 -- use the original delivery detail to unreserve
913 --
914 -- Debug Statements
915 --
916 IF l_debug_on THEN
917 WSH_DEBUG_SV.logmsg(l_module_name, 'UNRESERVE ' );
918 END IF;
919 --
920
921 -- HW BUG#:2005977. Need to branch
922 -- HW OPMCONV. Removed branching
923 IF l_debug_on THEN
924 WSH_DEBUG_SV.log(l_module_name,'OverPick Qty ',l_overpick_qtys(i));
925 END IF;
926
927
928
929 IF (l_item_info.lot_divisible_flag = 'N' AND
930 l_item_info.lot_control_code = 2 ) THEN --{ lot divisibility
931
932 DECLARE --{ lot indivisible reservation block
933 l_bo_mode VARCHAR2(30);
934 l_qty WSH_DELIVERY_DETAILS.REQUESTED_QUANTITY%TYPE;
935 l_qty2 WSH_DELIVERY_DETAILS.REQUESTED_QUANTITY2%TYPE;
936 BEGIN
937
938 l_bo_mode := p_bo_mode;
939 IF l_bo_mode = 'RETAIN_RSV' THEN
940 l_bo_mode := 'UNRESERVE';
941 END IF;
942
943 -- full lot indivisible reservation should be processed.
944 l_qty := l_bo_qtys(i) + NVL(l_overpick_qtys(i), 0);
945 l_qty2 := l_bo_qty2s(i) + NVL(p_overpick_qtys2(i), 0);
946
947 IF l_debug_on THEN
948 WSH_DEBUG_SV.LOG(l_module_name, 'item is lot indivisible: l_bo_mode', l_bo_mode);
949 WSH_DEBUG_SV.LOG(l_module_name, 'l_qty', l_qty);
950 WSH_DEBUG_SV.LOG(l_module_name, 'l_qty2', l_qty2);
951 END IF;
952 --Bug 5525314,If the item is lot indivisible and backorder Qty =0,
953 --then over pick Qty should not be unreserved
954 IF ((l_bo_qtys(i) > 0 AND l_detail_rec.requested_quantity >0)
955 OR (l_bo_qtys(i) = 0 AND l_detail_rec.requested_quantity = 0))
956 THEN
957 WSH_DELIVERY_DETAILS_ACTIONS.UNRESERVE_DELIVERY_DETAIL(
958 p_delivery_Detail_id => l_detail_ids(i),
959 p_unreserve_mode => l_bo_mode ,
960 p_quantity_to_unreserve => l_qty,
961 p_quantity2_to_unreserve => l_qty2,
962 p_override_retain_ato_rsv => 'Y',
963 x_return_status => l_return_status );
964
965 IF l_debug_on THEN
966 WSH_DEBUG_SV.log(l_module_name,'l_return_status',l_return_status);
967 END IF;
968
969 IF l_return_status IN (WSH_UTIL_CORE.G_RET_STS_ERROR,
970 WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR) THEN
971 fnd_message.set_name('WSH', 'WSH_BO_UNRESERVE_ERROR');
972 fnd_message.set_token('DETAIL_ID', l_detail_ids(i));
973 fnd_message.set_token('QUANTITY', l_qty);
974 wsh_util_core.add_message(l_return_status,l_module_name);
975 x_return_status := l_return_status;
976 RAISE backorder_error;
977 ELSIF l_return_status = WSH_UTIL_CORE.G_RET_STS_WARNING THEN
978 l_num_warn := l_num_warn + 1;
979 END IF;
980 END IF;
981 END; --} lot indivisible reservation block
982
983
984 ELSE -- } lot divisibility {
985 -- item is not lot divisible.
986
987 -- Bug 2824748: Call unreserve_delivery_detail with p_override_retain_ato_rsv 'Y' so that the
988 -- reservations for unused overpick qty are always removed
989 IF (l_overpick_qtys(i) > 0) THEN
990 IF l_debug_on THEN
991 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);
992 END IF;
993 --
994 -- HW OPMCONV - Added Qty2
995
996 --Bug 4721577 Do not retain reservation for overpicked quantities
997 IF p_bo_mode = 'RETAIN_RSV' THEN
998 wsh_delivery_details_actions.unreserve_delivery_detail(
999 p_delivery_Detail_id => l_detail_ids(i),
1000 p_unreserve_mode => 'UNRESERVE' ,
1001 p_quantity_to_unreserve => l_overpick_qtys(i),
1002 p_override_retain_ato_rsv => 'Y',
1003 x_return_status => l_return_status );
1004 ELSE
1005 wsh_delivery_details_actions.unreserve_delivery_detail(
1006 p_delivery_Detail_id => l_detail_ids(i),
1007 p_unreserve_mode => p_bo_mode ,
1008 p_quantity_to_unreserve => l_overpick_qtys(i),
1009 p_quantity2_to_unreserve =>p_overpick_qtys2(i),
1010 p_override_retain_ato_rsv => 'Y',
1011 x_return_status => l_return_status );
1012 END IF;
1013
1014 IF l_debug_on THEN
1015 WSH_DEBUG_SV.log(l_module_name,'l_return_status',l_return_status);
1016 END IF;
1017 IF l_return_status IN (WSH_UTIL_CORE.G_RET_STS_ERROR,
1018 WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR) THEN
1019 fnd_message.set_name('WSH', 'WSH_BO_UNRESERVE_ERROR');
1020 fnd_message.set_token('DETAIL_ID', l_detail_ids(i));
1021 fnd_message.set_token('QUANTITY', l_overpick_qtys(i));
1022 wsh_util_core.add_message(l_return_status,l_module_name);
1023 x_return_status := l_return_status;
1024 RAISE backorder_error;
1025 ELSIF l_return_status = WSH_UTIL_CORE.G_RET_STS_WARNING THEN
1026 l_num_warn := l_num_warn + 1;
1027 END IF;
1028
1029 END IF; -- l_overpick_qtys(i) > 0
1030
1031 IF l_debug_on THEN
1032 WSH_DEBUG_SV.log(l_module_name,'BackOrder Qty ',l_bo_qtys(i));
1033 END IF;
1034
1035 -- Bug 2824748: Call unreserve_delivery_detail with p_override_retain_ato_rsv 'N' so that the
1036 -- reservations for backorder qty are removed depending on the ATO profile option
1037 IF (l_bo_qtys(i) > 0) THEN
1038 IF l_debug_on THEN
1039 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);
1040 END IF;
1041 wsh_delivery_details_actions.unreserve_delivery_detail(
1042 p_delivery_Detail_id => l_detail_ids(i),
1043 p_unreserve_mode => p_bo_mode ,
1044 p_quantity_to_unreserve => l_bo_qtys(i),
1045 p_quantity2_to_unreserve => l_bo_qty2s(i),
1046 p_override_retain_ato_rsv => 'N',
1047 x_return_status => l_return_status );
1048
1049 IF l_debug_on THEN
1050 WSH_DEBUG_SV.log(l_module_name,'l_return_status',l_return_status);
1051 END IF;
1052 IF l_return_status IN (WSH_UTIL_CORE.G_RET_STS_ERROR,
1053 WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR) THEN
1054 fnd_message.set_name('WSH', 'WSH_BO_UNRESERVE_ERROR');
1055 fnd_message.set_token('DETAIL_ID', l_detail_ids(i));
1056 fnd_message.set_token('QUANTITY', l_bo_qtys(i));
1057 wsh_util_core.add_message(l_return_status,l_module_name);
1058 x_return_status := l_return_status;
1059 RAISE backorder_error;
1060 ELSIF l_return_status = WSH_UTIL_CORE.G_RET_STS_WARNING THEN
1061 l_num_warn := l_num_warn + 1;
1062
1063 END IF;
1064 END IF; -- l_bo_qtys(i) > 0
1065
1066 -- CREATE MOVE ORDER HEADER ?!!
1067 -- CREATE MOVE ORDER LINE ?!!
1068
1069 END IF; -- } lot divisiblity
1070
1071 else
1072 --
1073 -- Debug Statements
1074 --
1075 IF l_debug_on THEN
1076 WSH_DEBUG_SV.logmsg(l_module_name, 'NOT RESERVED ' );
1077 END IF;
1078 --
1079 END IF; -- l_reserved_flag = Y'
1080
1081 IF (l_bo_qtys(i) > 0) THEN -- (normal backorder case)
1082 -- removed cursor logic to get order line subinventory since it is already present as original_subinventory
1083 -- unmark serial numbers if the whole line is backordered
1084 IF l_bo_qtys(i) = NVL(l_detail_rec.picked_quantity, l_detail_rec.requested_quantity) AND
1085 (l_detail_rec.serial_number is not null OR l_detail_rec.transaction_temp_id is
1086 not null ) THEN
1087
1088 --
1089 -- Debug Statements
1090 --
1091 IF l_debug_on THEN
1092 WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_DELIVERY_DETAILS_INV.FETCH_INV_CONTROLS',WSH_DEBUG_SV.C_PROC_LEVEL);
1093 END IF;
1094 --
1095 WSH_DELIVERY_DETAILS_INV.Fetch_Inv_Controls(
1096 p_delivery_detail_id => l_detail_rec.delivery_detail_id,
1097 p_inventory_item_id => l_detail_rec.inventory_item_id,
1098 p_organization_id => l_detail_rec.organization_id,
1099 p_subinventory => l_detail_rec.subinventory,
1100 x_inv_controls_rec => l_inv_controls_rec,
1101 x_return_status => l_return_status);
1102
1103 IF l_debug_on THEN
1104 WSH_DEBUG_SV.log(l_module_name,'l_return_status',l_return_status);
1105 END IF;
1106 /* Bug 2308509 */
1107 IF l_return_status IN (WSH_UTIL_CORE.G_RET_STS_ERROR,
1108 WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR) THEN
1109 wsh_util_core.add_message(l_return_status,l_module_name);
1110 x_return_status := l_return_status;
1111 RAISE backorder_error;
1112 ELSIF l_return_status = WSH_UTIL_CORE.G_RET_STS_WARNING THEN
1113 l_num_warn := l_num_warn + 1;
1114 END IF;
1115 /* End of Bug 2308509 */
1116 --
1117 -- Debug Statements
1118 --
1119 IF l_debug_on THEN
1120 WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_DELIVERY_DETAILS_INV.UNMARK_SERIAL_NUMBER',WSH_DEBUG_SV.C_PROC_LEVEL);
1121 END IF;
1122 --
1123 WSH_DELIVERY_DETAILS_INV.Unmark_Serial_Number(
1124 p_delivery_detail_id => l_detail_rec.delivery_detail_id,
1125 p_serial_number_code => l_inv_controls_rec.serial_code,
1126 p_serial_number => l_detail_rec.serial_number,
1127 p_transaction_temp_id => l_detail_rec.transaction_temp_id,
1128 x_return_status => l_return_status);
1129 /* Bug 2308509 */
1130 IF l_debug_on THEN
1131 WSH_DEBUG_SV.log(l_module_name,'l_return_status',l_return_status);
1132 END IF;
1133 IF l_return_status IN (WSH_UTIL_CORE.G_RET_STS_ERROR,
1134 WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR) THEN
1135 wsh_util_core.add_message(l_return_status,l_module_name);
1136 x_return_status := l_return_status;
1137 RAISE backorder_error;
1138 ELSIF l_return_status = WSH_UTIL_CORE.G_RET_STS_WARNING THEN
1139 l_num_warn := l_num_warn + 1;
1140 END IF;
1141 /* End of Bug 2308509 */
1142
1143 UPDATE wsh_delivery_details
1144 SET transaction_temp_id = NULL,
1145 serial_number = NULL,
1146 last_update_date = SYSDATE,
1147 last_updated_by = FND_GLOBAL.USER_ID,
1148 last_update_login = FND_GLOBAL.LOGIN_ID
1149 WHERE delivery_detail_id = l_detail_rec.delivery_detail_id;
1150 END IF;
1151 --
1152 -- Debug Statements
1153 --
1154 IF l_debug_on THEN
1155 WSH_DEBUG_SV.logmsg(l_module_name, 'BACKORDER ...' );
1156 WSH_DEBUG_SV.log(l_module_name,'Current Time is ',SYSDATE);
1157 -- 2807093
1158 WSH_DEBUG_SV.log(l_module_name,'Released Status: ', l_detail_rec.released_status);
1159 END IF;
1160 -- bug 1733849: clear TRACKING_NUMBER when not shipped
1161 -- HW BUG#:1885927 Need to clear lot_number,sublot_number,locator_id,
1162 -- preferred_grade and subinventory if the line is backordered
1163 -- HW BUG#:2005977 Removed the branching so OPM/Discrete
1164 -- can use the same update statement.
1165 -- bug 2320460: cycle-counted lines should not have subinventory
1166 -- cleared.
1167 -- Normally backordered lines should have order line's subinventor
1168
1169 --Begin OPM bug 3561937
1170 -- HW OPMCONV - Removed branching
1171
1172 OPEN get_pref_grade(l_detail_rec.delivery_detail_id);
1173 FETCH get_pref_grade INTO l_oeline_pref_grade;
1174 CLOSE get_pref_grade;
1175 --End OPM bug 3561937
1176
1177 UPDATE wsh_delivery_details
1178 SET move_order_line_id = NULL ,
1179 -- 2807093: For ATO items, it is possible that during Shp.Confirm CTO would have updated the rel.Status to N
1180 -- so checking to see if it is 'N' then it has to remain 'N' otherwise 'B'
1181 released_status = decode(pickable_flag,'Y', decode(released_status, 'N', released_status,'B'),'X'),
1182 cycle_count_quantity = NULL,
1183 cycle_count_quantity2 = NULL,
1184 shipped_quantity = NULL,
1185 shipped_quantity2 = NULL,
1186 picked_quantity = NULL,
1187 picked_quantity2 = NULL,
1188 -- ship_set_id = NULL , code removed per bug 2008156
1189 -- Bug 2444564 : Backordered/Cycle Count lines should be reset to Original Subinventory
1190 subinventory = original_subinventory,
1191 inv_interfaced_flag = decode(pickable_flag, 'Y', nvl(inv_interfaced_flag,'N'), 'X'),
1192 locator_id = NULL,
1193 -- OPM Bug 3561937 replaced NULL with l_oeline_pref_grade
1194 preferred_grade = l_oeline_pref_grade,
1195 -- HW OPMCONV. No need for sublot anymore
1196 -- sublot_number=NULL,
1197 lot_number = NULL , -- Bug 1705057
1198 revision = null ,
1199 batch_id = null , -- Bug 2711490
1200 -- tracking_number = NULL, Bug# 3632485
1201 transaction_id = NULL, --- 2803570
1202 replenishment_status = NULL -- bug# 6908504 (replenishment project), update replenishment status to NULL.
1203 WHERE delivery_detail_id = l_detail_rec.delivery_detail_id AND
1204 NVL(container_flag, 'N') = 'N' ;
1205
1206
1207 WSH_DD_TXNS_PVT.create_dd_txn_from_dd (p_delivery_detail_id => l_detail_rec.delivery_detail_id,
1208 x_dd_txn_id => l_dd_txn_id,
1209 x_return_status =>l_txn_return_status);
1210 IF (l_txn_return_status <> WSH_UTIL_CORE.G_RET_STS_SUCCESS) THEN
1211 x_return_status := l_txn_return_status;
1212 --RETURN;
1213 raise e_return_excp; -- LPN CONV. rv
1214 END IF;
1215
1216
1217 --Raise Event: Pick To Pod Workflow
1218 WSH_WF_STD.Raise_Event(
1219 p_entity_type => 'LINE',
1220 p_entity_id => l_detail_rec.delivery_detail_id ,
1221 p_event => 'oracle.apps.wsh.line.gen.backordered' ,
1222 --p_parameters IN wf_parameter_list_t DEFAULT NULL,
1223 p_organization_id => l_detail_rec.organization_id,
1224 x_return_status => l_wf_rs ) ;
1225 --Error Handling to be done in WSH_WF_STD.Raise_Event itself
1226 IF l_debug_on THEN
1227 WSH_DEBUG_SV.log(l_module_name,'Current Time is ',SYSDATE);
1228 WSH_DEBUG_SV.log(l_module_name,'Delivery Detail Id is ', l_detail_rec.delivery_detail_id );
1229 wsh_debug_sv.log(l_module_name,'Return Status After Calling WSH_WF_STD.Raise_Event',l_wf_rs);
1230 END IF;
1231 --Even if raising of event fails the flow continues.
1232 --IF (l_wf_rs <>WSH_UTIL_CORE.G_RET_STS_SUCCESS ) then
1233 --No Action
1234 --END IF;
1235 --Done Raise Event: Pick To Pod Workflow
1236
1237 END IF; -- l_bo_qtys(i) > 0 (normal backorder case)
1238
1239 -- Consolidation of BO Delivery Details project
1240 IF (l_global_param_rec.consolidate_bo_lines = 'Y' AND p_line_ids.COUNT > 1) THEN
1241 IF l_debug_on THEN
1242 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);
1243 WSH_DEBUG_SV.logmsg(l_module_name,'..delivery detail id: '||l_detail_rec.delivery_detail_id, WSH_DEBUG_SV.C_PROC_LEVEL);
1244 WSH_DEBUG_SV.logmsg(l_module_name,'..delivery detail req qty: '||l_bo_qtys(i), WSH_DEBUG_SV.C_PROC_LEVEL);
1245 WSH_DEBUG_SV.logmsg(l_module_name,'..Consolidate BO quantity '||l_remain_bo_qtys(i), WSH_DEBUG_SV.C_PROC_LEVEL);
1246 -- HW OPM BUG#:3121616 added qty2s
1247 WSH_DEBUG_SV.logmsg(l_module_name,'..Consolidate BO quantity2 '||l_remain_bo_qty2s(i), WSH_DEBUG_SV.C_PROC_LEVEL);
1248 END IF;
1249 -- HW OPM BUG#:3121616 added requested_quantity2
1250 UPDATE WSH_DELIVERY_DETAILS
1251 SET requested_quantity = requested_quantity + l_remain_bo_qtys(i),
1252 requested_quantity2 = requested_quantity2 + l_remain_bo_qty2s(i)
1253 WHERE delivery_detail_id = l_detail_rec.delivery_detail_id;
1254
1255 WSH_DD_TXNS_PVT.create_dd_txn_from_dd(p_delivery_detail_id => l_detail_rec.delivery_detail_id,
1256 x_dd_txn_id => l_dd_txn_id,
1257 x_return_status =>l_txn_return_status);
1258 IF (l_return_status <> WSH_UTIL_CORE.G_RET_STS_SUCCESS) THEN
1259 x_return_status := l_return_status;
1260 raise e_return_excp;
1261 END IF;
1262
1263 l_bo_qtys(i) := l_bo_qtys(i) + l_remain_bo_qtys(i);
1264 l_req_qtys(i) := l_bo_qtys(i);
1265 -- HW OPM BUG#:3121616 added qty2s
1266 l_bo_qty2s(i) := l_bo_qty2s(i) + l_remain_bo_qty2s(i);
1267 -- end of 3121616
1268 END IF;
1269
1270 -- DBI Project, Above 2 Update statements are executed for l_detail_rec.delivery_detail_id
1271 -- Combining the 2 updates and making 1 call to DBI for the updates in WDD table
1272 -- These update released_status/requested_quantity
1273 -- This is different from l_detail_ids which is the set of ids for which LOOP is
1274 -- getting executed
1275 --
1276 -- Update of wsh_delivery_details where requested_quantity/released_status
1277 -- are changed, call DBI API after the update.
1278 -- This API will also check for DBI Installed or not
1279 IF l_debug_on THEN
1280 WSH_DEBUG_SV.log(l_module_name,'Calling DBI API.Detail id-',l_detail_rec.delivery_detail_id);
1281 END IF;
1282 l_detail_tab(1) := l_detail_rec.delivery_detail_id;
1283 WSH_INTEGRATION.DBI_Update_Detail_Log
1284 (p_delivery_detail_id_tab => l_detail_tab,
1285 p_dml_type => 'UPDATE',
1286 x_return_status => l_dbi_rs);
1287
1288 IF l_debug_on THEN
1289 WSH_DEBUG_SV.log(l_module_name,'Return Status after DBI Call-',l_dbi_rs);
1290 END IF;
1291 IF l_dbi_rs = WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR THEN
1292 x_return_status := l_dbi_rs;
1293 -- just pass this return status to caller API, raise as above
1294 -- Added in Above API already,wsh_util_core.add_message(x_return_status,l_module_name);
1295 RAISE backorder_error;
1296 END IF;
1297 -- treat all other return status as Success
1298 -- End of Code for DBI Project
1299 --
1300
1301 -- J: W/V Changes
1302 IF l_bo_qtys(i) > 0 Then -- Bug 3547300
1303 IF l_debug_on THEN
1304 WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_WV_UTILS.DETAIL_WEIGHT_VOLUME',WSH_DEBUG_SV.C_PROC_LEVEL);
1305 END IF;
1306 --
1307 WSH_WV_UTILS.Detail_Weight_Volume(
1308 p_delivery_detail_id => l_detail_rec.delivery_detail_id,
1309 p_update_flag => 'Y',
1310 p_post_process_flag => 'Y',
1311 p_calc_wv_if_frozen => 'Y',
1312 x_net_weight => l_split_weight,
1313 x_volume => l_split_volume,
1314 x_return_status => l_return_status);
1315 IF l_debug_on THEN
1316 WSH_DEBUG_SV.log(l_module_name,'l_return_status',l_return_status);
1317 END IF;
1318 IF l_return_status IN (WSH_UTIL_CORE.G_RET_STS_ERROR, WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR) THEN
1319 wsh_util_core.add_message(l_return_status,l_module_name);
1320 x_return_status := l_return_status;
1321 RAISE new_det_wt_vol_failed;
1322 ELSIF l_return_status = WSH_UTIL_CORE.G_RET_STS_WARNING THEN
1323 l_num_warn := l_num_warn + 1;
1324 END IF;
1325 END IF; -- Bug 3547300
1326
1327 -- bug # 6749200 (replenishment project ) : Moved the backorder consolidation code to inside the loop as the API needs to be called
1328 -- for each record separately.
1329 -- Bug#3317692
1330 -- Consolidation of BO Delivery Details project
1331 -- Get the delivery details with non-zero requested quantity to pass to Consolidate_Source_Line
1332 -- bug# 6908504 (replenishment project): Should not consolidate when the dd is assigned to a planned deliveries
1333 -- and p_bo_source is3/27/2008 PICK.
1334 IF ( (l_req_qtys(i) > 0 AND l_global_param_rec.consolidate_bo_lines = 'Y' )
1335 AND ( p_bo_source <> 'PICK' OR ( p_bo_source = 'PICK' AND l_planned_flag <> 'Y')) ) THEN
1336 --{
1337
1338 -- unassign and unpack empty container Added in bug 7460785
1339 --
1340 -- Debug Statements
1341
1342 IF (l_unassign_dds.COUNT > 0 and l_unassign_dds(l_unassign_dds.COUNT) = l_bo_detail_id ) THEN
1343 --{
1344 l_unassign_dds_tmp(1) := l_bo_detail_id;
1345
1346 IF l_debug_on THEN
1347 WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_DELIVERY_DETAILS_ACTIONS.unassign_unpack_empty_cont',WSH_DEBUG_SV.C_PROC_LEVEL);
1348 END IF;
1349
1350 -- p_validate_flag is passed as 'N', so that backordering does not fail for planned Deliveries.
1351 WSH_DELIVERY_DETAILS_ACTIONS.unassign_unpack_empty_cont
1352 (
1353 p_ids_tobe_unassigned => l_unassign_dds_tmp ,
1354 p_validate_flag => 'N',
1355 x_return_status => l_return_status
1356 );
1357
1358 --
1359 -- Debug Statements
1360 --
1361 IF l_debug_on THEN
1362 WSH_DEBUG_SV.logmsg(l_module_name, 'AFTER UNASSIGN AND UNPACK EMPTY CONTAINER '|| L_RETURN_STATUS );
1363 END IF;
1364
1365 IF (l_return_status <> WSH_UTIL_CORE.G_RET_STS_SUCCESS) THEN
1366 --{
1367 IF l_return_status = WSH_UTIL_CORE.G_RET_STS_WARNING THEN
1368 l_num_warn := l_num_warn + 1;
1369 ELSE
1370 x_return_status := l_return_status;
1371 RAISE backorder_error;
1372 END IF;
1373 --}
1374 END IF;
1375
1376 l_unassign_dds.DELETE(l_unassign_dds.COUNT);
1377 --}
1378 END IF; -- unpack and un-assign the deliveries
1379
1380 -- consolidate logic
1381 l_cons_source_line_rec_tab.delete;
1382 l_cons_dd_ids.delete;
1383 l_cons_source_line_rec_tab(1).delivery_detail_id := l_bo_detail_id;
1384 l_cons_source_line_rec_tab(1).source_line_id := l_detail_rec.source_line_id;
1385 l_cons_source_line_rec_tab(1).delivery_id := l_delivery_id;
1386 l_cons_source_line_rec_tab(1).bo_qty := l_bo_qtys(i);
1387 l_cons_source_line_rec_tab(1).req_qty := l_bo_qtys(i);
1388 -- HW OPM BUG#:3121616 added qty2s
1389 l_cons_source_line_rec_tab(1).bo_qty2 := l_bo_qty2s(i);
1390 l_cons_source_line_rec_tab(1).req_qty2 := l_bo_qty2s(i);
1391 -- end of 3121616
1392 --
1393 -- Debug Statements
1394 --
1395 IF l_debug_on THEN
1396 WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_DELIVERY_DETAILS_ACTIONS.Consolidate_Source_Line',WSH_DEBUG_SV.C_PROC_LEVEL);
1397 END IF;
1398 --
1399 WSH_DELIVERY_DETAILS_ACTIONS.Consolidate_Source_Line(
1400 p_Cons_Source_Line_Rec_Tab => l_cons_source_line_rec_tab,
1401 x_consolidate_ids => l_cons_dd_ids,
1402 x_return_status => l_return_status
1403 );
1404 IF l_return_status IN (WSH_UTIL_CORE.G_RET_STS_ERROR,WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR )THEN
1405 --{
1406 x_return_status := l_return_status;
1407 RAISE backorder_error;
1408 --}
1409 END IF;
1410 IF l_cons_dd_ids.COUNT > 0 THEN
1411 --{
1412 x_out_rows( x_out_rows.COUNT + 1) := l_cons_dd_ids(l_cons_dd_ids.FIRST);
1413 -- Setting the corresponding flag to 'Y', if a delivery detail is consolidated
1414 -- into some other delivery detail.
1415 IF l_cons_source_line_rec_tab(1).delivery_detail_id = l_cons_dd_ids(l_cons_dd_ids.FIRST) THEN
1416 x_cons_flags(x_cons_flags.COUNT+1) := 'N';
1417 ELSE
1418 x_cons_flags(x_cons_flags.COUNT+1) := 'Y';
1419
1420 -- Back order consolidation takes care of un-assigning/unpacking so no need to call separately.
1421 IF ( l_unassign_dd_flag = 'Y' ) THEN
1422 --{
1423 l_unassign_dds.delete(l_unassign_dds.count);
1424 l_unassign_dd_flag := 'N';
1425 --}
1426 END IF;
1427
1428 END IF;
1429 --}
1430 END IF;
1431 --}
1432 END IF;
1433 --
1434 <<loop_end>>
1435 NULL;
1436 END LOOP; --}
1437
1438 -- unassign and unpack empty container
1439 --
1440 -- Debug Statements
1441 --
1442 IF l_debug_on THEN
1443 WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_DELIVERY_DETAILS_ACTIONS.unassign_unpack_empty_cont',WSH_DEBUG_SV.C_PROC_LEVEL);
1444 END IF;
1445 -- p_validate_flag is passed as 'N', so that backordering does not fail for planned Deliveries.
1446 WSH_DELIVERY_DETAILS_ACTIONS.unassign_unpack_empty_cont (
1447 p_ids_tobe_unassigned => l_unassign_dds ,
1448 p_validate_flag => 'N',
1449 x_return_status => l_return_status
1450 );
1451
1452 --
1453 -- Debug Statements
1454 --
1455 IF l_debug_on THEN
1456 WSH_DEBUG_SV.logmsg(l_module_name, 'AFTER UNASSIGN AND UNPACK EMPTY CONTAINER '|| L_RETURN_STATUS );
1457 END IF;
1458 IF (l_return_status <> WSH_UTIL_CORE.G_RET_STS_SUCCESS) THEN
1459 IF l_return_status = WSH_UTIL_CORE.G_RET_STS_WARNING THEN
1460 l_num_warn := l_num_warn + 1;
1461 ELSE
1462 x_return_status := l_return_status;
1463 RAISE backorder_error;
1464 END IF;
1465 END IF;
1466
1467 IF l_delete_dds.count > 0 THEN
1468 --
1469 -- Debug Statements
1470 --
1471 IF l_debug_on THEN
1472 WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_INTERFACE.DELETE_DETAILS',WSH_DEBUG_SV.C_PROC_LEVEL);
1473 END IF;
1474 --
1475 WSH_INTERFACE.Delete_Details(
1476 p_details_id => l_delete_dds,
1477 x_return_status => l_return_status
1478 );
1479 --
1480 -- Debug Statements
1481 --
1482
1483 IF l_debug_on THEN
1484 WSH_DEBUG_SV.logmsg(l_module_name, 'AFTER DELETING DETAIL '|| L_RETURN_STATUS );
1485 END IF;
1486 --
1487 /* Bug 2308509 */
1488 IF l_return_status IN (WSH_UTIL_CORE.G_RET_STS_ERROR,
1489 WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR
1490 )THEN
1491 x_return_status := l_return_status;
1492 RAISE backorder_error;
1493 ELSIF l_return_status = WSH_UTIL_CORE.G_RET_STS_WARNING THEN
1494 l_num_warn := l_num_warn + 1;
1495 END IF;
1496 /* End of Bug 2308509 */
1497 END IF; -- l_delete_dds.count > 0
1498
1499 /*Bug#:2026895*/
1500 if p_bo_mode = 'CYCLE_COUNT' then
1501 --
1502 -- Debug Statements
1503 --
1504 IF l_debug_on THEN
1505 WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_DELIVERY_VALIDATIONS.CHECK_SHIP_SET',WSH_DEBUG_SV.C_PROC_LEVEL);
1506 END IF;
1507 --
1508 wsh_delivery_validations.check_ship_set( l_delivery_id, l_valid_flag, l_return_status);
1509 /* Bug 2308509 */
1510 IF l_return_status IN (WSH_UTIL_CORE.G_RET_STS_ERROR,
1511 WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR
1512 )THEN
1513 x_return_status := l_return_status;
1514 RAISE backorder_error;
1515 ELSIF l_return_status = WSH_UTIL_CORE.G_RET_STS_WARNING THEN
1516 l_num_warn := l_num_warn + 1;
1517 END IF;
1518 /* End of Bug 2308509 */
1519
1520 --
1521 -- K LPN CONV. rv
1522 IF l_debug_on THEN
1523 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);
1524 END IF;
1525 --
1526 --
1527 IF WSH_WMS_LPN_GRP.G_CALLBACK_REQUIRED = 'Y'
1528 THEN
1529 --{
1530
1531 WSH_LPN_SYNC_COMM_PKG.SYNC_LPNS_TO_WMS
1532 (
1533 p_in_rec => l_lpn_in_sync_comm_rec,
1534 x_return_status => l_return_status,
1535 x_out_rec => l_lpn_out_sync_comm_rec
1536 );
1537 --
1538 --
1539 IF l_debug_on THEN
1540 WSH_DEBUG_SV.log(l_module_name,'Return status after calling WSH_LPN_SYNC_COMM_PKG.SYNC_LPNS_TO_WMS',l_return_status);
1541 END IF;
1542 --
1543 --
1544 IF l_return_status IN (WSH_UTIL_CORE.G_RET_STS_ERROR,
1545 WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR
1546 )THEN
1547 x_return_status := l_return_status;
1548 RAISE backorder_error;
1549 ELSIF l_return_status = WSH_UTIL_CORE.G_RET_STS_WARNING THEN
1550 l_num_warn := l_num_warn + 1;
1551 END IF;
1552 --}
1553 END IF;
1554 --
1555 -- K LPN CONV. rv
1556 --
1557
1558
1559 /* Warning Handling for Bug 2308509 */
1560 IF (l_num_warn > 0
1561 AND l_return_status NOT IN (WSH_UTIL_CORE.G_RET_STS_ERROR,
1562 WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR)
1563 )THEN
1564 x_return_status := WSH_UTIL_CORE.G_RET_STS_WARNING;
1565 WSH_UTIL_CORE.ADD_MESSAGE(x_return_status,l_module_name);
1566 -- for handling all the cases
1567 ELSIF l_return_status IN (WSH_UTIL_CORE.G_RET_STS_ERROR,
1568 WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR) THEN
1569 x_return_status := l_return_status;
1570 RAISE backorder_error;
1571 END IF;
1572 /* End of Warning Handling for Bug 2308509 */
1573
1574 IF NOT (l_valid_flag) THEN
1575 FND_MESSAGE.SET_NAME('WSH','WSH_DEL_SHIP_SET_INCOMPLETE');
1576 --
1577 -- Debug Statements
1578 --
1579 IF l_debug_on THEN
1580 WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_NEW_DELIVERIES_PVT.GET_NAME',WSH_DEBUG_SV.C_PROC_LEVEL);
1581 END IF;
1582 --
1583 FND_MESSAGE.SET_TOKEN('DEL_NAME',wsh_new_deliveries_pvt.get_name(l_delivery_id));
1584 x_return_status := WSH_UTIL_CORE.G_RET_STS_WARNING;
1585 WSH_UTIL_CORE.ADD_MESSAGE(x_return_status,l_module_name);
1586 END IF;
1587 end if;
1588
1589
1590 --Bugfix 4070732 {
1591 IF upper(WSH_UTIL_CORE.G_START_OF_SESSION_API) = upper(l_api_session_name) THEN
1592 --{
1593 IF NOT(WSH_UTIL_CORE.G_CALL_FTE_LOAD_TENDER_API) THEN
1594 --{
1595 IF l_debug_on THEN
1596 WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_UTIL_CORE.Process_stops_for_load_tender',WSH_DEBUG_SV.C_PROC_LEVEL);
1597 END IF;
1598
1599 WSH_UTIL_CORE.Process_stops_for_load_tender(p_reset_flags => TRUE,
1600 x_return_status => l_return_status);
1601
1602 IF l_debug_on THEN
1603 WSH_DEBUG_SV.log(l_module_name,'l_return_status',l_return_status);
1604 END IF;
1605
1606 IF l_return_status <> WSH_UTIL_CORE.G_RET_STS_SUCCESS THEN
1607 x_return_status := l_return_status;
1608
1609 IF l_return_status IN (WSH_UTIL_CORE.G_RET_STS_ERROR,
1610 WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR)
1611 THEN
1612 ROLLBACK TO before_backorder;
1613 END IF;
1614 END IF;
1615 --}
1616 END IF;
1617 --}
1618 END IF;
1619 --}
1620 --
1621 -- Debug Statements
1622 --
1623 IF l_debug_on THEN
1624 WSH_DEBUG_SV.pop(l_module_name);
1625 END IF;
1626 --
1627 EXCEPTION
1628
1629 WHEN e_return_excp THEN
1630
1631 --
1632 -- K LPN CONV. rv
1633 --
1634 IF WSH_WMS_LPN_GRP.G_CALLBACK_REQUIRED = 'Y'
1635 THEN
1636 --{
1637 IF l_debug_on THEN
1638 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);
1639 END IF;
1640
1641 WSH_LPN_SYNC_COMM_PKG.SYNC_LPNS_TO_WMS
1642 (
1643 p_in_rec => l_lpn_in_sync_comm_rec,
1644 x_return_status => l_return_status,
1645 x_out_rec => l_lpn_out_sync_comm_rec
1646 );
1647 --
1648 --
1649 IF l_debug_on THEN
1650 WSH_DEBUG_SV.log(l_module_name,'Return status after calling WSH_LPN_SYNC_COMM_PKG.SYNC_LPNS_TO_WMS',l_return_status);
1651 END IF;
1652 --
1653 --
1654 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
1655 x_return_status := l_return_status;
1656 END IF;
1657 --
1658 --}
1659 END IF;
1660 --
1661 -- K LPN CONV. rv
1662 --
1663 IF l_debug_on THEN
1664 WSH_DEBUG_SV.logmsg(l_module_name,'E_RETURN_EXCP exception has occured.',WSH_DEBUG_SV.C_EXCEP_LEVEL);
1665 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:E_RETURN_EXCP');
1666 END IF;
1667 --
1668
1669
1670 WHEN backorder_error THEN
1671 -- code sets messages before raising backorder_error.
1672 ROLLBACK to before_backorder;
1673 --
1674 -- Debug Statements
1675 --
1676 --Bugfix 4070732 {
1677 IF upper(WSH_UTIL_CORE.G_START_OF_SESSION_API) = upper(l_api_session_name) THEN
1678 IF NOT(WSH_UTIL_CORE.G_CALL_FTE_LOAD_TENDER_API) THEN
1679 IF l_debug_on THEN
1680 WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_UTIL_CORE.Reset_stops_for_load_tender',WSH_DEBUG_SV.C_PROC_LEVEL);
1681 END IF;
1682
1683 WSH_UTIL_CORE.Reset_stops_for_load_tender(p_reset_flags => TRUE,
1684 x_return_status => l_return_status);
1685
1686
1687 IF l_debug_on THEN
1688 WSH_DEBUG_SV.log(l_module_name,'l_return_status',l_return_status);
1689 END IF;
1690
1691 IF (l_return_status = WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR) THEN
1692 x_return_status := l_return_status;
1693 END IF;
1694 END IF;
1695 END IF;
1696 --}
1697 IF l_debug_on THEN
1698 WSH_DEBUG_SV.logmsg(l_module_name,'BACKORDER_ERROR exception has occured.',WSH_DEBUG_SV.C_EXCEP_LEVEL);
1699 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:BACKORDER_ERROR');
1700 END IF;
1701 --
1702 RETURN;
1703
1704 -- HW 3457369
1705 -- HW OPMCONV. Removed OPM exception
1706 --
1707
1708 /*Bug 2096052 added exception */
1709 --
1710 -- Debug Statements
1711 --
1712 --
1713 WHEN invalid_source_code THEN
1714 fnd_message.set_name('WSH','WSH_INVALID_SHIP_MODE');
1715 wsh_util_core.add_message(x_return_status,l_module_name);
1716 x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
1717 ROLLBACK to before_backorder ;
1718 /* End of changes for 2096052 */
1719
1720 --Bugfix 4070732 {
1721 IF upper(WSH_UTIL_CORE.G_START_OF_SESSION_API) = upper(l_api_session_name) THEN
1722 IF NOT(WSH_UTIL_CORE.G_CALL_FTE_LOAD_TENDER_API) THEN
1723 IF l_debug_on THEN
1724 WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_UTIL_CORE.Reset_stops_for_load_tender',WSH_DEBUG_SV.C_PROC_LEVEL);
1725 END IF;
1726
1727 WSH_UTIL_CORE.Reset_stops_for_load_tender(p_reset_flags => TRUE,
1728 x_return_status => l_return_status);
1729
1730
1731 IF l_debug_on THEN
1732 WSH_DEBUG_SV.log(l_module_name,'l_return_status',l_return_status);
1733 END IF;
1734
1735 IF (l_return_status = WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR) THEN
1736 x_return_status := l_return_status;
1737 END IF;
1738
1739 END IF;
1740 END IF;
1741 --}
1742 /* Bug 2399729 disallow backorder full for third party warehouse shipment line */
1743 --
1744 -- Debug Statements
1745 --
1746 IF l_debug_on THEN
1747 WSH_DEBUG_SV.logmsg(l_module_name,'INVALID_SOURCE_CODE exception has occured.',WSH_DEBUG_SV.C_EXCEP_LEVEL);
1748 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:INVALID_SOURCE_CODE');
1749 END IF;
1750 --
1751 WHEN no_backorder_full THEN
1752 fnd_message.set_name('WSH','WSH_BKALL_NOT_ALLOW');
1753 x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
1754 wsh_util_core.add_message(x_return_status,l_module_name);
1755 ROLLBACK to before_backorder ;
1756
1757 --Bugfix 4070732 {
1758 IF upper(WSH_UTIL_CORE.G_START_OF_SESSION_API) = upper(l_api_session_name) THEN
1759 IF NOT(WSH_UTIL_CORE.G_CALL_FTE_LOAD_TENDER_API) THEN
1760 IF l_debug_on THEN
1761 WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_UTIL_CORE.Reset_stops_for_load_tender',WSH_DEBUG_SV.C_PROC_LEVEL);
1762 END IF;
1763
1764 WSH_UTIL_CORE.Reset_stops_for_load_tender(p_reset_flags => TRUE,
1765 x_return_status => l_return_status);
1766
1767
1768 IF l_debug_on THEN
1769 WSH_DEBUG_SV.log(l_module_name,'l_return_status',l_return_status);
1770 END IF;
1771
1772 IF (l_return_status = WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR) THEN
1773 x_return_status := l_return_status;
1774 END IF;
1775
1776 END IF;
1777 END IF;
1778 --}
1779
1780 --
1781 -- Debug Statements
1782 --
1783 IF l_debug_on THEN
1784 WSH_DEBUG_SV.logmsg(l_module_name,'NO_BACKORDER_FULL exception has occured.',WSH_DEBUG_SV.C_EXCEP_LEVEL);
1785 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:NO_BACKORDER_FULL');
1786 END IF;
1787 --
1788 WHEN new_det_wt_vol_failed THEN
1789 x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
1790 fnd_message.set_name('WSH', 'WSH_DET_WT_VOL_FAILED');
1791 FND_MESSAGE.SET_TOKEN('DETAIL_ID',l_detail_rec.delivery_detail_id );
1792 wsh_util_core.add_message(x_return_status,l_module_name);
1793
1794 --
1795 -- K LPN CONV. rv
1796 --
1797 IF WSH_WMS_LPN_GRP.G_CALLBACK_REQUIRED = 'Y'
1798 THEN
1799 --{
1800 IF l_debug_on THEN
1801 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);
1802 END IF;
1803
1804 WSH_LPN_SYNC_COMM_PKG.SYNC_LPNS_TO_WMS
1805 (
1806 p_in_rec => l_lpn_in_sync_comm_rec,
1807 x_return_status => l_return_status,
1808 x_out_rec => l_lpn_out_sync_comm_rec
1809 );
1810 --
1811 --
1812 IF l_debug_on THEN
1813 WSH_DEBUG_SV.log(l_module_name,'Return status after calling WSH_LPN_SYNC_COMM_PKG.SYNC_LPNS_TO_WMS',l_return_status);
1814 END IF;
1815 --
1816 --
1817 IF (l_return_status = WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR) THEN
1818 x_return_status := l_return_status;
1819 END IF;
1820 --
1821 --}
1822 END IF;
1823 --
1824 -- K LPN CONV. rv
1825 --
1826 --Bugfix 4070732 {
1827 IF upper(WSH_UTIL_CORE.G_START_OF_SESSION_API) = upper(l_api_session_name) THEN
1828 IF NOT(WSH_UTIL_CORE.G_CALL_FTE_LOAD_TENDER_API) THEN
1829 IF l_debug_on THEN
1830 WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_UTIL_CORE.Reset_stops_for_load_tender',WSH_DEBUG_SV.C_PROC_LEVEL);
1831 END IF;
1832
1833 WSH_UTIL_CORE.Reset_stops_for_load_tender(p_reset_flags => TRUE,
1834 x_return_status => l_return_status);
1835
1836
1837 IF l_debug_on THEN
1838 WSH_DEBUG_SV.log(l_module_name,'l_return_status',l_return_status);
1839 END IF;
1840
1841 IF (l_return_status = WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR) THEN
1842 x_return_status := l_return_status;
1843 END IF;
1844
1845 END IF;
1846 END IF;
1847 --}
1848 --
1849 -- Debug Statements
1850 --
1851 IF l_debug_on THEN
1852 WSH_DEBUG_SV.logmsg(l_module_name,'NEW_DET_WT_VOL_FAILED exception has occured.',WSH_DEBUG_SV.C_EXCEP_LEVEL);
1853 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:NEW_DET_WT_VOL_FAILED');
1854 END IF;
1855 --
1856 WHEN OTHERS THEN
1857 WSH_UTIL_CORE.Default_Handler('WSH_SHIP_CONFIRM_ACTIONS2.Backorder',l_module_name);
1858 x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR;
1859 ROLLBACK to before_backorder ;
1860 --Bugfix 4070732 {
1861 IF upper(WSH_UTIL_CORE.G_START_OF_SESSION_API) = upper(l_api_session_name) THEN
1862 IF NOT(WSH_UTIL_CORE.G_CALL_FTE_LOAD_TENDER_API) THEN
1863 IF l_debug_on THEN
1864 WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_UTIL_CORE.Reset_stops_for_load_tender',WSH_DEBUG_SV.C_PROC_LEVEL);
1865 END IF;
1866
1867 WSH_UTIL_CORE.Reset_stops_for_load_tender(p_reset_flags => TRUE,
1868 x_return_status => l_return_status);
1869
1870
1871 IF l_debug_on THEN
1872 WSH_DEBUG_SV.log(l_module_name,'l_return_status',l_return_status);
1873 END IF;
1874 END IF;
1875 END IF;
1876 --}
1877 --
1878 -- Debug Statements
1879 IF l_debug_on THEN
1880 WSH_DEBUG_SV.logmsg(l_module_name,'Unexpected error has occured. Oracle error message is '|| SQLERRM,WSH_DEBUG_SV.C_UNEXPEC_ERR_LEVEL);
1881 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:OTHERS');
1882 END IF;
1883
1884 RETURN ;
1885
1886 END Backorder;
1887
1888 PROCEDURE check_exception(
1889 p_delivery_detail_id IN NUMBER
1890 , x_exception_exist OUT NOCOPY VARCHAR2
1891 , x_severity_present OUT NOCOPY VARCHAR2
1892 , x_return_status OUT NOCOPY VARCHAR2)
1893 IS
1894
1895 l_not_handled VARCHAR2(30):='NOT_HANDLED';
1896 l_no_action_reqd VARCHAR2(30):='NO_ACTION_REQUIRED';
1897 l_closed VARCHAR2(30):='CLOSED';
1898
1899 CURSOR c_exception IS
1900 SELECT decode(severity,'HIGH','H','MEDIUM','M','L') severity
1901 FROM wsh_exceptions
1902 WHERE delivery_detail_id = p_delivery_detail_id
1903 AND status not in (l_not_handled , l_no_action_reqd , l_closed)
1904 ORDER BY decode(severity,'H',1,'M',2,3);
1905
1906 l_count NUMBER := 0;
1907 --
1908 l_debug_on BOOLEAN;
1909 --
1910 l_module_name CONSTANT VARCHAR2(100) := 'wsh.plsql.' || G_PKG_NAME || '.' || 'CHECK_EXCEPTION';
1911 --
1912 BEGIN
1913 --
1914 -- Debug Statements
1915 --
1916 --
1917 l_debug_on := WSH_DEBUG_INTERFACE.g_debug;
1918 --
1919 IF l_debug_on IS NULL
1920 THEN
1921 l_debug_on := WSH_DEBUG_SV.is_debug_enabled;
1922 END IF;
1923 --
1924 IF l_debug_on THEN
1925 WSH_DEBUG_SV.push(l_module_name);
1926 --
1927 WSH_DEBUG_SV.log(l_module_name,'P_DELIVERY_DETAIL_ID',P_DELIVERY_DETAIL_ID);
1928 END IF;
1929 --
1930 x_return_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
1931
1932 OPEN c_exception;
1933 FETCH c_exception INTO x_severity_present;
1934 IF c_exception%NOTFOUND THEN
1935 x_exception_exist := 'N';
1936 ELSE
1937 x_exception_exist := 'Y';
1938 END IF;
1939 CLOSE c_exception;
1940
1941 IF l_debug_on THEN
1942 WSH_DEBUG_SV.log(l_module_name,'x_exception_exist',x_exception_exist);
1943 WSH_DEBUG_SV.log(l_module_name,'x_severity_present',x_severity_present);
1944 END IF;
1945
1946
1947 --
1948 -- Debug Statements
1949 --
1950 IF l_debug_on THEN
1951 WSH_DEBUG_SV.pop(l_module_name);
1952 END IF;
1953 --
1954 EXCEPTION
1955 WHEN others THEN
1956 x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR;
1957 WSH_UTIL_CORE.default_handler('WSH_SHIP_CONFIRM_ACTIONS2.check_exception',l_module_name);
1958
1959 IF l_debug_on THEN
1960 WSH_DEBUG_SV.logmsg(l_module_name,'Unexpected error has occured. Oracle error message is '|| SQLERRM,WSH_DEBUG_SV.C_UNEXPEC_ERR_LEVEL);
1961 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:OTHERS');
1962 END IF;
1963 --
1964 END check_exception;
1965
1966 --
1967 -- Procedure: Backorder
1968 -- Description: This is a wrapper of the BackOrder procedure already present in the package.
1969 -- This is introduced for Consolidation of BO Delivery Details project.
1970 -- This wrapper avoids the change of the calls made to Backorder api from different apis
1971 -- (as additional parameter is added to the original backorder api).
1972 --
1973 PROCEDURE Backorder(
1974 p_detail_ids IN WSH_UTIL_CORE.Id_Tab_Type,
1975 p_bo_qtys IN WSH_UTIL_CORE.Id_Tab_Type,
1976 p_req_qtys IN WSH_UTIL_CORE.Id_Tab_Type,
1977 p_bo_qtys2 IN WSH_UTIL_CORE.Id_Tab_Type,
1978 p_overpick_qtys IN WSH_UTIL_CORE.Id_Tab_Type,
1979 p_overpick_qtys2 IN WSH_UTIL_CORE.Id_Tab_Type,
1980 p_bo_mode IN VARCHAR2,
1981 p_bo_source IN VARCHAR2 DEFAULT 'SHIP',
1982 x_out_rows OUT NOCOPY WSH_UTIL_CORE.Id_Tab_Type,
1983 x_return_status OUT NOCOPY VARCHAR2 )
1984 IS
1985
1986 l_line_ids WSH_UTIL_CORE.Id_Tab_Type;
1987 l_cons_flags WSH_UTIL_CORE.Column_Tab_Type;
1988 l_return_status VARCHAR2(1);
1989
1990 --
1991 l_debug_on BOOLEAN;
1992 --
1993 l_module_name CONSTANT VARCHAR2(100) := 'wsh.plsql.' || G_PKG_NAME || '.' || 'BACKORDER';
1994 --
1995 BEGIN
1996 --
1997 -- Debug Statements
1998 --
1999 --
2000 l_debug_on := WSH_DEBUG_INTERFACE.g_debug;
2001 --
2002 IF l_debug_on IS NULL
2003 THEN
2004 l_debug_on := WSH_DEBUG_SV.is_debug_enabled;
2005 END IF;
2006 --
2007 IF l_debug_on THEN
2008 WSH_DEBUG_SV.push(l_module_name);
2009 --
2010 WSH_DEBUG_SV.log(l_module_name,'P_BO_MODE',P_BO_MODE);
2011 WSH_DEBUG_SV.log(l_module_name,'P_BO_SOURCE',P_BO_SOURCE);
2012 END IF;
2013 --
2014
2015 --
2016 -- Debug Statements
2017 --
2018 IF l_debug_on THEN
2019 WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_SHIP_CONFIRM_ACTIONS2.BackOrder',WSH_DEBUG_SV.C_PROC_LEVEL);
2020 WSH_DEBUG_SV.logmsg(l_module_name,'......with p_line_ids as NULL', WSH_DEBUG_SV.C_PROC_LEVEL);
2021 END IF;
2022 --
2023 Backorder(
2024 p_detail_ids => p_detail_ids,
2025 p_line_ids => l_line_ids,
2026 p_bo_qtys => p_bo_qtys,
2027 p_req_qtys => p_req_qtys,
2028 p_bo_qtys2 => p_bo_qtys2,
2029 p_overpick_qtys => p_overpick_qtys,
2030 p_overpick_qtys2 => p_overpick_qtys2,
2031 p_bo_mode => p_bo_mode,
2032 p_bo_source => p_bo_source,
2033 x_out_rows => x_out_rows,
2034 x_cons_flags => l_cons_flags,
2035 x_return_status => x_return_status );
2036
2037 IF l_debug_on THEN
2038 WSH_DEBUG_SV.logmsg(l_module_name,'x_return_status '|| x_return_status, WSH_DEBUG_SV.C_PROC_LEVEL);
2039 WSH_DEBUG_SV.pop(l_module_name);
2040 END IF;
2041
2042 END BackOrder;
2043
2044 END WSH_SHIP_CONFIRM_ACTIONS2;