1 PACKAGE BODY WSH_RCV_CORR_RTV_TXN_PKG as
2 /* $Header: WSHRCRVB.pls 120.0 2005/05/26 18:03:27 appldev noship $ */
3
4
5 --===================
6 -- GLOBAL VARIABLES
7 --===================
8 --
9 G_PKG_NAME CONSTANT VARCHAR2(50) := 'WSH_RCV_CORR_RTV_TXN_PKG';
10 --
11 --
12 -- the following variables are defined for caching the delivery id
13 g_del_cache_tbl wsh_util_core.key_value_tab_type;
14 g_del_ext_cache_tbl wsh_util_core.key_value_tab_type;
15
16 -- the following variables are defined for caching the lpns that need to
17 -- deleted
18 g_unassgin_lpn_cache_tbl wsh_util_core.key_value_tab_type;
19 g_unassgin_lpn_ext_cache_tbl wsh_util_core.key_value_tab_type;
20 --
21 --
22 --===================
23 -- PROCEDURES
24 --===================
25 --========================================================================
26 -- PROCEDURE : Process_Rcv This procedure is called from
27 -- process_corrections_and_rtv
28 -- to handle the receipt corrections
29 -- (both positive and negative).
30 --
31 -- PARAMETERS: x_matched_detail_rec Record that contains the info about
32 -- all the matched delivery details.
33 -- p_matched_detail_index Index of x_matched_detail_rec on
34 -- which we need to process
35 -- the corrections.
36 -- x_update_det_rec Record that we finally use to update
37 -- wsh_delivery_details after processing
38 -- the corrections.
39 -- p_update_det_rec_idx Index of x_update_det_rec on
40 -- which we need to process
41 -- the corrections.
42 -- x_rem_req_qty_rec Record to collect the remaining
43 -- requested quantity after processing
44 -- each record in the x_matched_detail_rec.
45 -- x_unassign_det_tbl Table that is used to unassign the
46 -- delivery details from deliveries when
47 -- received quantity is completely
48 -- nullified on them.
49 -- x_delivery_id_tab Table of delivery ids that need to be
50 -- passed back to
51 -- process_corrections_and_rtv which will
52 -- be used to re-calculate weight-vol,
53 -- Mark_Reprice_Required,rerateDeliveries.
54 -- x_wv_detail_tab Table of delivery details for which we
55 -- need to re-calculate wt-vol as a result
56 -- of updating the received quantities.
57 -- x_unassigned_lpn_id_tab Table of lpns for that will be deleted
58 -- if they do not contain any delivery
59 -- lines within them.
60 -- x_wv_recalc_del_id_tab Table of deliveries for which
61 -- the wv_frozen_flag is set to 'Y'
62 -- for the corresponding lines.
63 -- x_return_status Return status of the API.
64
65 -- COMMENT : This procedure is used to mainly assign the updated received
66 -- quantities from x_matched_detail_rec for each delivery detail
67 -- to x_update_det_rec correspondingly as this x_update_det_rec
68 -- is finally used to perform a bulk update on wsh_delivery_details
69 -- in the procedure process_corrections_and_rtv.
70 --
71 --========================================================================
72
73 PROCEDURE process_rcv (
74 x_matched_detail_rec IN OUT NOCOPY WSH_IB_UI_RECON_GRP.asn_rcv_del_det_rec_type,
75 p_matched_detail_index IN NUMBER,
76 x_update_det_rec IN OUT NOCOPY update_detail_rec_type,
77 p_update_det_rec_idx IN NUMBER,
78 x_rem_req_qty_rec IN OUT NOCOPY rem_req_qty_rec_type,
79 x_unassign_det_tbl IN OUT NOCOPY wsh_util_core.id_tab_type,
80 x_po_line_loc_tbl IN OUT NOCOPY wsh_util_core.id_tab_type,
81 x_delivery_id_tab IN OUT NOCOPY wsh_util_core.id_tab_type,
82 x_wv_detail_tab IN OUT NOCOPY wsh_util_core.id_tab_type,
83 x_unassigned_lpn_id_tab IN OUT NOCOPY wsh_util_core.id_tab_type,
84 x_wv_recalc_del_id_tab IN OUT NOCOPY wsh_util_core.id_tab_type,
85 x_return_status OUT NOCOPY VARCHAR2)
86 IS
87 --{
88
89 l_child_index NUMBER;
90 l_det_id_tab wsh_util_core.id_tab_type;
91 l_det_action_prms WSH_GLBL_VAR_STRCT_GRP.dd_action_parameters_rec_type;
92 l_det_action_out_rec WSH_GLBL_VAR_STRCT_GRP.DD_ACTION_OUT_REC_TYPE;
93 l_return_status VARCHAR2(1);
94 l_num_errors NUMBER;
95 l_msg_data VARCHAR2(32767);
96 l_msg_count NUMBER;
97 l_num_warnings NUMBER;
98
99 l_del_update_req_flag VARCHAR2(1);
100 l_delivery_id NUMBER;
101 --
102 -- This cursor is used to obtain the delivery_id for the
103 -- delivery detail which is being processed.
104 -- Finally these deliveries will be used to recalculate the wt-vol,
105 -- and rerating etc.
106 cursor l_delivery_id_csr(p_del_det_id NUMBER) is
107 select wnd.delivery_id
108 from wsh_delivery_assignments_v wda,
109 wsh_new_deliveries wnd
110 where wda.delivery_detail_id = p_del_det_id
111 and wda.delivery_id = wnd.delivery_id;
112
113 --
114 -- cursor to get the lpns for the lines.
115 cursor l_unassigned_lpns_csr(p_delivery_detail_id IN NUMBER) is
116 select parent_delivery_detail_id
117 from wsh_delivery_assignments_v
118 where delivery_detail_id = p_delivery_detail_id;
119
120 l_unassigned_lpn_id NUMBER;
121
122 cursor l_del_det_wv_flag_csr(p_delivery_detail_id IN NUMBER) is
123 select gross_weight,
124 net_weight,
125 volume,
126 NVL(wv_frozen_flag,'Y') wv_frozen_flag
127 from wsh_delivery_details
128 where delivery_detail_id = p_delivery_detail_id;
129
130 l_qty_ratio NUMBER;
131 l_det_gr_weight NUMBER;
132 l_det_net_weight NUMBER;
133 l_det_volume NUMBER;
134 l_det_wv_frozen_flag VARCHAR2(10) := 'N';
135 --
136 --}
137 --
138 l_debug_on BOOLEAN;
139 --
140 l_module_name CONSTANT VARCHAR2(100) := 'wsh.plsql.' || G_PKG_NAME || '.' || 'PROCESS_RCV';
141 --
142 BEGIN
143 --{
144 --
145 l_debug_on := WSH_DEBUG_INTERFACE.g_debug;
146 --
147 IF l_debug_on IS NULL
148 THEN
149 l_debug_on := WSH_DEBUG_SV.is_debug_enabled;
150 END IF;
151 --
152 --
153 -- Debug Statements
154 --
155 IF l_debug_on THEN
156 WSH_DEBUG_SV.push(l_module_name);
157 --
158 WSH_DEBUG_SV.log(l_module_name,'P_MATCHED_DETAIL_INDEX',P_MATCHED_DETAIL_INDEX);
159
160 WSH_DEBUG_SV.log(l_module_name,'P_UPDATE_DET_REC_IDX',P_UPDATE_DET_REC_IDX);
161 WSH_DEBUG_SV.log(l_module_name,'recevied quantity db',x_matched_detail_rec.received_qty_db_tab(p_matched_detail_index));
162 WSH_DEBUG_SV.log(l_module_name,'recevied quantity ',x_matched_detail_rec.received_qty_tab(p_matched_detail_index));
163 WSH_DEBUG_SV.log(l_module_name,'update recs recevied quantity ',x_update_det_rec.received_qty_tab(p_update_det_rec_idx));
164 WSH_DEBUG_SV.log(l_module_name,'update recs record_changed_flag ',x_update_det_rec.record_changed_flag_tab(p_update_det_rec_idx));
165 END IF;
166 --
167 SAVEPOINT PROCESS_RCV;
168 x_return_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
169 IF x_matched_detail_rec.received_qty_tab(p_matched_detail_index) < 0 THEN
170 --{
171 FND_MESSAGE.SET_NAME('WSH','WSH_UI_NEGATIVE_QTY');
172 x_return_status := wsh_util_core.g_ret_sts_error;
173 wsh_util_core.add_message(x_return_status, l_module_name);
174 RAISE FND_API.G_EXC_ERROR;
175 --}
176 END IF;
177 IF nvl(x_matched_detail_rec.received_qty_db_tab(p_matched_detail_index),0) > nvl(x_matched_detail_rec.received_qty_tab(p_matched_detail_index),0) THEN
178 --{
179 -- This means that the user has reduced the received quantity on the delivery
180 -- detail (equivalent to negative correction).
181 IF nvl(x_matched_detail_rec.received_qty_tab(p_matched_detail_index),0) = 0 THEN
182 --{
183 -- This means that the delivery detail's received quantity is nulled by the user
184 -- which tells us that we need to unassign this detail its the delivery.
185 IF l_debug_on THEN
186 WSH_DEBUG_SV.logmsg(l_module_name,'This means complete negative correction of the original quantity received');
187 WSH_DEBUG_SV.log(l_module_name,'delivery detail id ', x_matched_detail_rec.del_detail_id_tab(p_matched_detail_index));
188 WSH_DEBUG_SV.log(l_module_name,'po line loc id ', x_matched_detail_rec.po_line_location_id_tab(p_matched_detail_index));
189 END IF;
190 x_unassign_det_tbl(x_unassign_det_tbl.count + 1) := x_matched_detail_rec.del_detail_id_tab(p_matched_detail_index);
191 x_po_line_loc_tbl(x_po_line_loc_tbl.count + 1) := x_matched_detail_rec.po_line_location_id_tab(p_matched_detail_index);
192
193 -- need to delete the corresponding lpns also if they are not
194 -- containing any other delivery lines.
195 open l_unassigned_lpns_csr(x_matched_detail_rec.del_detail_id_tab(p_matched_detail_index));
196 fetch l_unassigned_lpns_csr into l_unassigned_lpn_id;
197 close l_unassigned_lpns_csr;
198 IF l_debug_on THEN
199 WSH_DEBUG_SV.log(l_module_name,'delivery line is',x_matched_detail_rec.del_detail_id_tab(p_matched_detail_index));
200 WSH_DEBUG_SV.log(l_module_name,'l_unassigned_lpn_id',l_unassigned_lpn_id);
201 END IF;
202
203 IF (l_unassigned_lpn_id IS NOT NULL) THEN
204 --{
205 --
206 -- Debug Statements
207 --
208 IF l_debug_on THEN
209 WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_UTIL_CORE.GET_CACHED_VALUE',WSH_DEBUG_SV.C_PROC_LEVEL);
210 END IF;
211 --
212 wsh_util_core.get_cached_value(
213 p_cache_tbl => g_unassgin_lpn_cache_tbl,
214 p_cache_ext_tbl => g_unassgin_lpn_ext_cache_tbl,
215 p_value => l_unassigned_lpn_id,
216 p_key => l_unassigned_lpn_id,
217 p_action => 'GET',
218 x_return_status => l_return_status);
219
220 IF l_return_status IN (wsh_util_core.g_ret_sts_error, wsh_util_core.g_ret_sts_unexp_error) THEN
221 RAISE FND_API.G_EXC_ERROR;
222 END IF;
223
224 IF (l_return_status = wsh_util_core.g_ret_sts_warning) THEN
225 --{
226 --
227 -- Debug Statements
228 --
229 IF l_debug_on THEN
230 WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_UTIL_CORE.GET_CACHED_VALUE',WSH_DEBUG_SV.C_PROC_LEVEL);
231 END IF;
232 --
233 wsh_util_core.get_cached_value(
234 p_cache_tbl => g_unassgin_lpn_cache_tbl,
235 p_cache_ext_tbl => g_unassgin_lpn_ext_cache_tbl,
236 p_value => l_unassigned_lpn_id,
237 p_key => l_unassigned_lpn_id,
238 p_action => 'PUT',
239 x_return_status => l_return_status);
240
241 --
242 -- Debug Statements
243 --
244 IF l_debug_on THEN
245 WSH_DEBUG_SV.log(l_module_name,'Return Status after calling wsh_util_core.get_cached_value for put is',l_return_status);
246 WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_UTIL_CORE.API_POST_CALL',WSH_DEBUG_SV.C_PROC_LEVEL);
247 END IF;
248 --
249 wsh_util_core.api_post_call(
250 p_return_status => l_return_status,
251 x_num_warnings => l_num_warnings,
252 x_num_errors => l_num_errors);
253
254 x_unassigned_lpn_id_tab(x_unassigned_lpn_id_tab.count + 1) := l_unassigned_lpn_id;
255 IF l_debug_on THEN
256 WSH_DEBUG_SV.logmsg(l_module_name,'After assigning the value');
257 END IF;
258 --}
259 END IF;
260 --}
261 END IF;
262
263 /* commented this code because this is already taken care of by
264 unassign_multilple_details
265 x_update_det_rec.received_qty_tab(p_matched_detail_index) := NULL;
266 x_update_det_rec.received_qty2_tab(p_matched_detail_index) := NULL;
267 x_update_det_rec.returned_qty_tab(p_matched_detail_index) := NULL;
268 x_update_det_rec.returned_qty2_tab(p_matched_detail_index) := NULL;
269 x_update_det_rec.shipment_line_id_tab(p_matched_detail_index) := NULL;
270 x_update_det_rec.released_sts_tab(p_matched_detail_index) := 'X';
271 */
272 --}
273 ELSE
274 --{
275 IF l_debug_on THEN
276 WSH_DEBUG_SV.logmsg(l_module_name,'Inside the Else part of IF condition');
277 END IF;
278 x_update_det_rec.received_qty_tab(p_update_det_rec_idx) := x_matched_detail_rec.received_qty_tab(p_matched_detail_index);
279
280 x_update_det_rec.requested_qty_tab(p_update_det_rec_idx) := least(x_matched_detail_rec.requested_qty_tab(p_matched_detail_index),
281 x_matched_detail_rec.received_qty_tab(p_matched_detail_index));
282
283 x_update_det_rec.received_qty2_tab(p_update_det_rec_idx) := x_matched_detail_rec.received_qty2_tab(p_matched_detail_index);
284
285 x_update_det_rec.requested_qty2_tab(p_update_det_rec_idx) := least(x_matched_detail_rec.requested_qty2_tab(p_matched_detail_index),
286 x_matched_detail_rec.received_qty2_tab(p_matched_detail_index));
287
288 l_child_index := x_matched_detail_rec.child_index_tab(p_matched_detail_index);
289 /*
290 IF ( l_child_index IS NOT NULL ) THEN
291 --{
292 x_matched_detail_rec.process_corr_rtv_flag_tab(l_child_index) := 'N';
293 */
294 --}
295 IF ( l_child_index IS NULL ) THEN
296 --{
297 x_rem_req_qty_rec.requested_quantity := nvl(x_rem_req_qty_rec.requested_quantity,0) +
298 /*
299 (x_matched_detail_rec.received_qty_db_tab(p_matched_detail_index) -
300 x_matched_detail_rec.received_qty_tab(p_matched_detail_index));
301 */
302 greatest((least(x_matched_detail_rec.received_qty_db_tab(p_matched_detail_index), x_matched_detail_rec.requested_qty_db_tab(p_matched_detail_index))-x_matched_detail_rec.received_qty_tab(p_matched_detail_index)),0);
303
304 x_rem_req_qty_rec.requested_quantity_uom := x_matched_detail_rec.requested_qty_uom_tab(p_matched_detail_index);
305
306 IF x_matched_detail_rec.requested_qty2_tab(p_matched_detail_index) IS NOT NULL THEN
307 --{
308 x_rem_req_qty_rec.requested_quantity2 := nvl(x_rem_req_qty_rec.requested_quantity2,0) +
309 /*
310 (x_matched_detail_rec.received_qty2_db_tab(p_matched_detail_index) -
311 x_matched_detail_rec.received_qty2_tab(p_matched_detail_index));
312 */
313 greatest((least(x_matched_detail_rec.received_qty2_db_tab(p_matched_detail_index), x_matched_detail_rec.requested_qty2_db_tab(p_matched_detail_index))-x_matched_detail_rec.received_qty2_tab(p_matched_detail_index)),0);
314
315 x_rem_req_qty_rec.requested_quantity2_uom:= x_matched_detail_rec.requested_qty_uom2_tab(p_matched_detail_index);
316 --}
317 END IF;
318 --}
319 END IF;
320
321 --}
322 END IF;
323 x_update_det_rec.record_changed_flag_tab(p_update_det_rec_idx) := 'Y';
324 l_del_update_req_flag := 'Y';
325 --}
326 ELSIF nvl(x_matched_detail_rec.received_qty_db_tab(p_matched_detail_index),0) < nvl(x_matched_detail_rec.received_qty_tab(p_matched_detail_index),0) THEN
327 --{
328 -- This means that the user has increased the received quantity on the delivery
329 -- detail (equivalent to positive correction).
330 IF l_debug_on THEN
331 WSH_DEBUG_SV.logmsg(l_module_name,'Inside the Else IF condition');
332 END IF;
333 x_update_det_rec.received_qty_tab(p_update_det_rec_idx) := x_matched_detail_rec.received_qty_tab(p_matched_detail_index);
334
335 x_update_det_rec.received_qty2_tab(p_update_det_rec_idx) := x_matched_detail_rec.received_qty2_tab(p_matched_detail_index);
336
337 x_update_det_rec.record_changed_flag_tab(p_update_det_rec_idx) := 'Y';
338 l_del_update_req_flag := 'Y';
339 --}
340 END IF;
341 --
342 --
343 IF nvl(l_del_update_req_flag, 'N') = 'Y' THEN
344 --{
345 IF nvl(x_update_det_rec.received_qty_tab(p_update_det_rec_idx),0) = 0 THEN
346 --{
347 x_update_det_rec.received_qty_tab(p_update_det_rec_idx) := null;
348 --}
349 END IF;
350 --
351 -- This is added so that we calculate the weight and volume of the del details
352 -- only for receipt corrections.
353 x_wv_detail_tab(x_wv_detail_tab.count + 1) := x_matched_detail_rec.del_detail_id_tab(p_matched_detail_index);
354 --
355 IF nvl(x_update_det_rec.received_qty2_tab(p_update_det_rec_idx),0) = 0 THEN
356 --{
357 x_update_det_rec.received_qty2_tab(p_update_det_rec_idx) := null;
358 --}
359 END IF;
360
361 open l_del_det_wv_flag_csr(x_matched_detail_rec.del_detail_id_tab(p_matched_detail_index));
362 fetch l_del_det_wv_flag_csr into
363 l_det_gr_weight,
364 l_det_net_weight,
365 l_det_volume,
366 l_det_wv_frozen_flag;
367 close l_del_det_wv_flag_csr;
368 IF (l_det_wv_frozen_flag = 'Y') THEN
369 --{
370 l_qty_ratio := x_matched_detail_rec.received_qty_tab(p_matched_detail_index)/x_matched_detail_rec.received_qty_db_tab(p_matched_detail_index);
371 x_update_det_rec.wv_changed_flag_tab(p_update_det_rec_idx) := 'Y';
372 x_update_det_rec.gross_weight_tab(p_update_det_rec_idx) := ROUND(l_qty_ratio*l_det_gr_weight,5);
373 x_update_det_rec.net_weight_tab(p_update_det_rec_idx) := ROUND(l_qty_ratio*l_det_net_weight,5);
374 x_update_det_rec.volume_tab(p_update_det_rec_idx) := ROUND(l_qty_ratio*l_det_volume,5);
375 --}
376 END IF;
377 open l_delivery_id_csr(x_matched_detail_rec.del_detail_id_tab(p_matched_detail_index));
378 fetch l_delivery_id_csr into l_delivery_id;
379 close l_delivery_id_csr;
380 --
381 -- Debug Statements
382 --
383 IF l_debug_on THEN
384 WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_UTIL_CORE.GET_CACHED_VALUE',WSH_DEBUG_SV.C_PROC_LEVEL);
385 END IF;
386 --
387 wsh_util_core.get_cached_value(
388 p_cache_tbl => g_del_cache_tbl,
389 p_cache_ext_tbl => g_del_ext_cache_tbl,
390 p_value => l_delivery_id,
391 p_key => l_delivery_id,
392 p_action => 'GET',
393 x_return_status => l_return_status);
394
395 IF l_return_status IN (wsh_util_core.g_ret_sts_error, wsh_util_core.g_ret_sts_unexp_error) THEN
396 RAISE FND_API.G_EXC_ERROR;
397 END IF;
398
399 IF (l_return_status = wsh_util_core.g_ret_sts_warning) THEN
400 --{
401 --
402 -- Debug Statements
403 --
404 IF l_debug_on THEN
405 WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_UTIL_CORE.GET_CACHED_VALUE',WSH_DEBUG_SV.C_PROC_LEVEL);
406 END IF;
407 --
408 wsh_util_core.get_cached_value(
409 p_cache_tbl => g_del_cache_tbl,
410 p_cache_ext_tbl => g_del_ext_cache_tbl,
411 p_value => l_delivery_id,
412 p_key => l_delivery_id,
413 p_action => 'PUT',
414 x_return_status => l_return_status);
415
416 --
417 -- Debug Statements
418 --
419 IF l_debug_on THEN
420 WSH_DEBUG_SV.log(l_module_name,'Return Status after calling wsh_util_core.get_cached_value for put is',l_return_status);
421 WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_UTIL_CORE.API_POST_CALL',WSH_DEBUG_SV.C_PROC_LEVEL);
422 END IF;
423 --
424 wsh_util_core.api_post_call(
425 p_return_status => l_return_status,
426 x_num_warnings => l_num_warnings,
427 x_num_errors => l_num_errors);
428
429 x_delivery_id_tab(x_delivery_id_tab.count+1) := l_delivery_id;
430 IF l_debug_on THEN
431 WSH_DEBUG_SV.logmsg(l_module_name,'After assigning the value');
432 END IF;
433 IF (l_det_wv_frozen_flag = 'Y') THEN
434 x_wv_recalc_del_id_tab(x_wv_recalc_del_id_tab.count+1) := l_delivery_id;
435 END IF;
436 --}
437 END IF;
438 --}
439 END IF;
440 --}
441 --
442 -- Debug Statements
443 --
444 IF l_debug_on THEN
445 WSH_DEBUG_SV.log(l_module_name,'At the end - update recs recevied quantity ',x_update_det_rec.received_qty_tab(p_update_det_rec_idx));
446 WSH_DEBUG_SV.log(l_module_name,'At the end - update recs record_changed_flag ',x_update_det_rec.record_changed_flag_tab(p_update_det_rec_idx));
447 WSH_DEBUG_SV.pop(l_module_name);
448 END IF;
449 --
450 EXCEPTION
451 --{
452 WHEN FND_API.G_EXC_ERROR THEN
453 ROLLBACK TO PROCESS_RCV;
454 x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
455 --
456 -- Debug Statements
457 --
458 IF l_debug_on THEN
459 WSH_DEBUG_SV.logmsg(l_module_name,'FND_API.G_EXC_ERROR exception has occured.',WSH_DEBUG_SV.C_EXCEP_LEVEL);
460 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:FND_API.G_EXC_ERROR');
461 END IF;
462 --
463 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
464 ROLLBACK TO PROCESS_RCV;
465 x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR ;
466 --
467 -- Debug Statements
468 --
469 IF l_debug_on THEN
470 WSH_DEBUG_SV.logmsg(l_module_name,'FND_API.G_EXC_UNEXPECTED_ERROR exception has occured.',WSH_DEBUG_SV.C_EXCEP_LEVEL);
471 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:FND_API.G_EXC_UNEXPECTED_ERROR');
472 END IF;
473 --
474 WHEN WSH_UTIL_CORE.G_EXC_WARNING THEN
475 x_return_status := WSH_UTIL_CORE.G_RET_STS_WARNING;
476 --
477 -- Debug Statements
478 --
479 IF l_debug_on THEN
480 WSH_DEBUG_SV.logmsg(l_module_name,'WSH_UTIL_CORE.G_EXC_WARNING exception has occured.',WSH_DEBUG_SV.C_EXCEP_LEVEL);
481 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:WSH_UTIL_CORE.G_EXC_WARNING');
482 END IF;
483 --
484 WHEN OTHERS THEN
485 ROLLBACK TO PROCESS_RCV;
486 x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR ;
487 wsh_util_core.default_handler('WSH_IB_UI_RECON_GRP.PROCESS_RCV');
488 --}
489 --
490 -- Debug Statements
491 --
492 IF l_debug_on THEN
493 WSH_DEBUG_SV.logmsg(l_module_name,'Unexpected error has occured. Oracle error message is '|| SQLERRM,WSH_DEBUG_SV.C_UNEXPEC_ERR_LEVEL);
494 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:OTHERS');
495 END IF;
496 --
497 END process_rcv;
498
499 --========================================================================
500 -- PROCEDURE : Process_Rtv This procedure is called from
501 -- process_corrections_and_rtv
502 -- to handle the rtv and rtv corrections
503 -- (both positive and negative).
504 --
505 -- PARAMETERS: x_matched_detail_rec Record that contains the info about
506 -- all the matched delivery details.
507 -- p_matched_detail_index Index of x_matched_detail_rec on
508 -- which we need to process
509 -- the rtv quantities.
510 -- x_update_det_rec Record that we finally use to update
511 -- wsh_delivery_details after processing
512 -- the rtv quantities.
513 -- p_update_det_rec_idx Index of x_update_det_rec on
514 -- which we need to process
515 -- the rtv quantities.
516 -- x_rem_req_qty_rec Record to collect the remaining
517 -- requested quantity after processing
518 -- each record in the x_matched_detail_rec.
519 -- x_return_status Return status of the API.
520
521 -- COMMENT : This procedure is used to mainly assign the updated returned
522 -- quantities from x_matched_detail_rec for each delivery detail
523 -- to x_update_det_rec correspondingly as this x_update_det_rec
524 -- is finally used to perform a bulk update on wsh_delivery_details
525 -- in the procedure process_corrections_and_rtv.
526 --
527 --========================================================================
528
529 PROCEDURE process_rtv (
530 x_matched_detail_rec in out NOCOPY WSH_IB_UI_RECON_GRP.asn_rcv_del_det_rec_type,
531 p_matched_detail_index IN NUMBER,
532 x_update_det_rec IN OUT NOCOPY update_detail_rec_type,
533 p_update_det_rec_idx IN NUMBER,
534 x_rem_req_qty_rec IN OUT NOCOPY rem_req_qty_rec_type,
535 x_return_status OUT NOCOPY VARCHAR2)
536 IS
537 --{
538
539 l_num_errors NUMBER;
540 l_num_warnings NUMBER;
541 l_msg_data VARCHAR2(32767);
542 l_msg_count NUMBER;
543 l_return_status VARCHAR2(1);
544 --}
545 --
546 l_debug_on BOOLEAN;
547 --
548 l_module_name CONSTANT VARCHAR2(100) := 'wsh.plsql.' || G_PKG_NAME || '.' || 'PROCESS_RTV';
549 --
550 BEGIN
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 --
561 -- Debug Statements
562 --
563 IF l_debug_on THEN
564 WSH_DEBUG_SV.push(l_module_name);
565 --
566 WSH_DEBUG_SV.log(l_module_name,'P_MATCHED_DETAIL_INDEX',P_MATCHED_DETAIL_INDEX);
567 WSH_DEBUG_SV.log(l_module_name,'P_UPDATE_DET_REC_IDX',P_UPDATE_DET_REC_IDX);
568 WSH_DEBUG_SV.log(l_module_name,'returned_qty',x_matched_detail_rec.returned_qty_tab(p_matched_detail_index));
569 WSH_DEBUG_SV.log(l_module_name,'returned_qty_db',x_matched_detail_rec.returned_qty_db_tab(p_matched_detail_index));
570 END IF;
571 --
572 x_return_status := wsh_util_core.g_ret_sts_success;
573 IF x_matched_detail_rec.returned_qty_tab(p_matched_detail_index) < 0 THEN
574 --{
575 FND_MESSAGE.SET_NAME('WSH','WSH_UI_NEGATIVE_QTY');
576 x_return_status := wsh_util_core.g_ret_sts_error;
577 wsh_util_core.add_message(x_return_status, l_module_name);
578 RAISE FND_API.G_EXC_ERROR;
579 --}
580 END IF;
581 IF nvl(x_matched_detail_rec.returned_qty_db_tab(p_matched_detail_index),0) <> nvl(x_matched_detail_rec.returned_qty_tab(p_matched_detail_index),0) THEN
582 --{
583 x_update_det_rec.returned_qty_tab(p_update_det_rec_idx) := x_matched_detail_rec.returned_qty_tab(p_matched_detail_index);
584
585 x_update_det_rec.returned_qty2_tab(p_update_det_rec_idx) := x_matched_detail_rec.returned_qty2_tab(p_matched_detail_index);
586
587 x_rem_req_qty_rec.requested_quantity := nvl(x_rem_req_qty_rec.requested_quantity,0) + (nvl(x_matched_detail_rec.returned_qty_tab(p_matched_detail_index),0) - nvl(x_matched_detail_rec.returned_qty_db_tab(p_matched_detail_index),0));
588 x_rem_req_qty_rec.requested_quantity_uom := x_matched_detail_rec.requested_qty_uom_tab(p_matched_detail_index);
589
590 IF x_matched_detail_rec.requested_qty2_tab(p_matched_detail_index) IS NOT NULL THEN
591 --{
592 x_rem_req_qty_rec.requested_quantity2 := nvl(x_rem_req_qty_rec.requested_quantity2,0) +
593 (nvl(x_matched_detail_rec.returned_qty2_tab(p_matched_detail_index),0) -
594 nvl(x_matched_detail_rec.returned_qty2_db_tab(p_matched_detail_index),0));
595 x_rem_req_qty_rec.requested_quantity2_uom:= x_matched_detail_rec.requested_qty_uom2_tab(p_matched_detail_index);
596 --}
597 END IF;
598 IF (nvl(x_update_det_rec.returned_qty_tab(p_update_det_rec_idx), 0) = 0) THEN
599 --{
600 x_update_det_rec.returned_qty_tab(p_update_det_rec_idx) := null;
601 --}
602 END IF;
603 --
604 IF (nvl(x_update_det_rec.returned_qty2_tab(p_update_det_rec_idx), 0) = 0) THEN
605 --{
606 x_update_det_rec.returned_qty2_tab(p_update_det_rec_idx) := null;
607 --}
608 END IF;
609 x_update_det_rec.record_changed_flag_tab(p_update_det_rec_idx) := 'Y';
610 --}
611 END IF;
612
613 --}
614 --
615 -- Debug Statements
616 --
617 IF l_debug_on THEN
618 WSH_DEBUG_SV.log(l_module_name,'remaining req quantity',x_rem_req_qty_rec.requested_quantity);
619 WSH_DEBUG_SV.pop(l_module_name);
620 END IF;
621 --
622 EXCEPTION
623 --{
624 WHEN FND_API.G_EXC_ERROR THEN
625 x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
626 --
627 -- Debug Statements
628 --
629 IF l_debug_on THEN
630 WSH_DEBUG_SV.logmsg(l_module_name,'FND_API.G_EXC_ERROR exception has occured.',WSH_DEBUG_SV.C_EXCEP_LEVEL);
631 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:FND_API.G_EXC_ERROR');
632 END IF;
633 --
634 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
635 x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR ;
636 --
637 -- Debug Statements
638 --
639 IF l_debug_on THEN
640 WSH_DEBUG_SV.logmsg(l_module_name,'FND_API.G_EXC_UNEXPECTED_ERROR exception has occured.',WSH_DEBUG_SV.C_EXCEP_LEVEL);
641 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:FND_API.G_EXC_UNEXPECTED_ERROR');
642 END IF;
643 --
644 WHEN WSH_UTIL_CORE.G_EXC_WARNING THEN
645 x_return_status := WSH_UTIL_CORE.G_RET_STS_WARNING;
646 --
647 -- Debug Statements
648 --
649 IF l_debug_on THEN
650 WSH_DEBUG_SV.logmsg(l_module_name,'WSH_UTIL_CORE.G_EXC_WARNING exception has occured.',WSH_DEBUG_SV.C_EXCEP_LEVEL);
651 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:WSH_UTIL_CORE.G_EXC_WARNING');
652 END IF;
653 --
654 WHEN OTHERS THEN
655 x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR ;
656 wsh_util_core.default_handler('WSH_IB_UI_RECON_GRP.PROCESS_RTV');
657 --}
658 --
659 -- Debug Statements
660 --
661 IF l_debug_on THEN
662 WSH_DEBUG_SV.logmsg(l_module_name,'Unexpected error has occured. Oracle error message is '|| SQLERRM,WSH_DEBUG_SV.C_UNEXPEC_ERR_LEVEL);
663 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:OTHERS');
664 END IF;
665 --
666 END process_rtv;
667
668 --========================================================================
669 -- PROCEDURE : Process_Corrections_And_Rtv --
670 -- This procedure is called from
671 -- both Inbound Reconciliation UI and
672 -- Matching Algorithm to match the
673 -- Corrections and RTV transactions.
674 --
675 -- PARAMETERS: p_api_version Known api version error number
676 -- p_init_msg_list FND_API.G_TRUE to reset list
677 -- p_rtv_corr_in_rec global line rec type (not used)
678 -- p_matched_detail_rec record of matched delivery details
679 -- p_action_prms action parameters record type
680 -- p_rtv_corr_out_rec output record of the API (not used)
681 -- x_po_cancel_rec output record of cancelled po lines
682 -- x_po_close_rec output record of closed po lines
683 -- x_msg_data text of messages
684 -- x_msg_count number of messages in the list
685 -- x_return_status return status of the API
686
687 -- VERSION : current version 1.0
688 -- initial version 1.0
689 -- COMMENT : This procedure is used to match the child transactions
690 -- (Receipt Corrections, RTV, and RTV corrections) of a Receipt.
691 -- This procedure is the main procedure to handle the correctios
692 -- and rtvs for receipt transactions.
693 -- The following is the flow of this procedure -
694 -- 1. We initially loop through the p_matched_detail_rec
695 -- and update the l_update_det_rec with the
696 -- new received quantities and returned quantities for each
697 -- delivery detail by calling Process_Rcv and Process_Rtv.
698 -- 2. For each record in p_matched_detail_rec, we also calculate
699 -- the remaining requested quantity and acculumate the quantity
700 -- until there is a change in the po_line_location_id and then
701 -- if this quantity <> 0 for the corresponding
702 -- po_line_location_id, then we call the procedure
703 -- Process_remaining_req_quantity.
704 -- 3. Then we perform a bulk update on wsh_delivery_details
705 -- to update the new received and returned quantities
706 -- 4. If there are any delivery details for which there was
707 -- a complete receipt correction (i.e. received quantity
708 -- becomes null or zero), then we call
709 -- WSH_DELIVERY_DETAILS_ACTIONS.Unassign_Multiple_Details
710 -- to unassign those lines from the delivery.
711 -- 5. If there were any lines for which there was a receipt
712 -- correction, we call WSH_WV_UTILS.Detail_Weight_Volume
713 -- to re-calculate the wt-vol of the lines.
714 -- 6. Similary if there were any lines for which there was a
715 -- receipt correction, then we get the corresponding deliveries
716 -- and then call the APIs
717 -- WSH_DELIVERY_LEGS_ACTIONS.Mark_Reprice_Required
718 -- and WSH_INBOUND_UTIL_PKG.reRateDeliveries to set the
719 -- reprice_required flag and to re-rate the deliveries.
720 -- 7. Then we loop through the p_matched_detail_rec
721 -- and for each po_line_location_id we call the API
722 -- PO_FTE_INTEGRATION_GRP.po_status_check to check the line's
723 -- status and assign the line to x_po_close_rec
724 -- or x_po_cancel_rec depending on the status of the line
725 -- and pass them as out parameters to the calling procedure
726 -- so the calling program either call cancel_po or close_po
727 -- accordingly.
728 -- 8. Then we handle the return status.
729 --========================================================================
730
731 PROCEDURE process_corrections_and_rtv (
732 p_rtv_corr_in_rec IN OE_WSH_BULK_GRP.Line_rec_type,
733 p_matched_detail_rec IN OUT NOCOPY WSH_IB_UI_RECON_GRP.asn_rcv_del_det_rec_type,
734 p_action_prms IN WSH_BULK_TYPES_GRP.action_parameters_rectype,
735 p_rtv_corr_out_rec OUT NOCOPY corr_rtv_out_rec_type,
736 x_po_cancel_rec OUT NOCOPY OE_WSH_BULK_GRP.line_rec_type,
737 x_po_close_rec OUT NOCOPY OE_WSH_BULK_GRP.line_rec_type,
738 x_msg_data OUT NOCOPY VARCHAR2,
739 x_msg_count OUT NOCOPY NUMBER,
740 x_return_status OUT NOCOPY VARCHAR2)
741 IS
742 --{
743
744 l_index NUMBER;
745 l_update_det_rec update_detail_rec_type;
746 l_update_det_rec_idx NUMBER := -1;
747 l_rem_req_qty_rec rem_req_qty_rec_type;
748
749 l_prev_po_line_loc_id NUMBER;
750 l_prev_po_line_id NUMBER;
751
752 l_delivery_id_tab wsh_util_core.id_tab_type;
753 l_fob_code_tab wsh_util_core.Column_tab_type;
754 l_fr_terms_code_tab wsh_util_core.Column_tab_type;
755
756 l_return_status VARCHAR2(1);
757 l_msg_data VARCHAR2(32767);
758 l_msg_count NUMBER;
759 l_num_errors NUMBER;
760 l_num_warnings NUMBER;
761 l_delivery_id NUMBER;
762
763 l_del_action_prms WSH_DELIVERIES_GRP.action_parameters_rectype;
764 l_del_action_out_rec WSH_DELIVERIES_GRP.Delivery_Action_Out_Rec_Type;
765
766
767 -- This is not used any more. Was added in the beginning.
768 cursor l_del_det_fob_chk_csr(p_delivery_id NUMBER) is
769 select 'N'
770 from wsh_delivery_assignments_v wda,
771 wsh_delivery_details wdd1,
772 wsh_delivery_details wdd2
773 where wda.delivery_id = p_delivery_id
774 and wda.delivery_detail_id = wdd1.delivery_detail_id
775 and wda.delivery_detail_id = wdd2.delivery_detail_id
776 and (wdd1.fob_code is null
777 or wdd2.fob_code is null
778 or wdd1.fob_code <> wdd2.fob_code)
779 and rownum =1;
780
781 -- This is not used any more. Was added in the beginning.
782 cursor l_del_det_fr_terms_chk_csr(p_delivery_id NUMBER) is
783 select 'N'
784 from wsh_delivery_assignments_v wda,
785 wsh_delivery_details wdd1,
786 wsh_delivery_details wdd2
787 where wda.delivery_id = p_delivery_id
788 and wda.delivery_detail_id = wdd1.delivery_detail_id
789 and wda.delivery_detail_id = wdd2.delivery_detail_id
790 and (wdd1.freight_terms_code is null
791 or wdd2.freight_terms_code is null
792 or wdd1.freight_terms_code <> wdd2.freight_terms_code)
793 and rownum =1;
794
795 -- This is not used any more. Was added in the beginning.
796 cursor l_del_det_fr_term_fob_csr (p_delivery_id NUMBER) is
797 select wdd.freight_terms_code, wdd.fob_code
798 from wsh_delivery_assignments_v wda,
799 wsh_delivery_details wdd
800 where wda.delivery_id = p_delivery_id
801 and wda.delivery_detail_id = wdd.delivery_detail_id
802 and rownum =1;
803
804 l_distinct_fob_found VARCHAR2(1) := 'Y';
805 l_distinct_fr_terms_found VARCHAR2(1) := 'Y';
806
807 l_fob_code VARCHAR2(32767);
808 l_fr_terms_code VARCHAR2(32767);
809
810 l_fob_fr_terms_csr_open_flag VARCHAR2(1) := 'N';
811 l_fob_fr_terms_changed_tab wsh_util_core.Column_tab_type;
812 i NUMBER;
813
814
815 l_unassign_det_tbl wsh_util_core.id_tab_type;
816 l_po_line_loc_tbl wsh_util_core.id_tab_type;
817 l_wv_detail_tab wsh_util_core.id_tab_type;
818
819 -- cursor to check the lpns obtained from process_rcv
820 -- still have any delivery lines in them or not.
821 -- If they do not have any delivery lines, then
822 -- they are eligible to be deleted.
823 cursor l_chk_lpn_empty_csr(p_del_det_id IN NUMBER) is
824 select 'N'
825 from wsh_delivery_assignments_v
826 where parent_delivery_detail_id = p_del_det_id
827 and rownum=1;
828
829 l_lpn_empty_flag VARCHAR2(10);
830 l_unassigned_lpn_id_tab wsh_util_core.id_tab_type;
831 l_delete_lpn_id_tab wsh_util_core.id_tab_type;
832 l_wv_recalc_del_id_tab wsh_util_core.id_tab_type;
833
834 l_line_rec OE_WSH_BULK_GRP.line_rec_type;
835 -- the following variables are defined for caching the po line location id
836 l_po_line_loc_cache_tbl wsh_util_core.key_value_tab_type;
837 l_po_line_loc_ext_cache_tbl wsh_util_core.key_value_tab_type;
838
839 --l_cancel_line_rec OE_WSH_BULK_GRP.line_rec_type;
840 --l_close_line_rec OE_WSH_BULK_GRP.line_rec_type;
841 l_action_prms WSH_BULK_TYPES_GRP.action_parameters_rectype;
842 l_po_status_rec PO_STATUS_REC_TYPE;
843 l_unassign_action_prms wsh_glbl_var_strct_grp.dd_action_parameters_rec_type;
844 l_pr_rem_in_rec WSH_RCV_CORR_RTV_TXN_PKG.action_in_rec_type;
845
846 l_po_action_prms WSH_BULK_TYPES_GRP.action_parameters_rectype;
847 l_dd_list WSH_PO_CMG_PVT.dd_list_type;
848
849 -- Variable to store the last record to be processed in p_matched_detail_rec
850 l_last_valid_det_index NUMBER;
851 --}
852 --
853 l_dbi_rs VARCHAR2(1); -- Return Status from DBI API
854 l_debug_on BOOLEAN;
855 --
856 l_module_name CONSTANT VARCHAR2(100) := 'wsh.plsql.' || G_PKG_NAME || '.' || 'PROCESS_CORRECTIONS_AND_RTV';
857 --
858 l_rcv_qty NUMBER;
859 BEGIN
860 --{
861 --
862 l_debug_on := WSH_DEBUG_INTERFACE.g_debug;
863 --
864 IF l_debug_on IS NULL
865 THEN
866 l_debug_on := WSH_DEBUG_SV.is_debug_enabled;
867 END IF;
868 --
869 --
870 -- Debug Statements
871 --
872 IF l_debug_on THEN
873 WSH_DEBUG_SV.push(l_module_name);
874 END IF;
875 --
876 SAVEPOINT process_corrections_and_rtv;
877 x_return_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
878
879 --l_index := p_matched_detail_rec.del_detail_id_tab.first;
880 --l_prev_po_line_loc_id := p_matched_detail_rec.po_line_location_id_tab(l_index);
881 l_rem_req_qty_rec.requested_quantity := 0;
882
883 l_update_det_rec_idx := l_update_det_rec.del_det_id_tab.count;
884
885 g_del_cache_tbl.delete;
886 g_del_ext_cache_tbl.delete;
887
888 for l_index in p_matched_detail_rec.del_detail_id_tab.first..p_matched_detail_rec.del_detail_id_tab.last loop
889 --{
890 IF (p_matched_detail_rec.process_corr_rtv_flag_tab(l_index) = 'Y') THEN
891 --{
892 -- remember the l_update_det_rec_idx was initialized with -1
893 l_update_det_rec_idx := l_update_det_rec_idx + 1;
894
895 l_update_det_rec.del_det_id_tab(l_update_det_rec_idx) := p_matched_detail_rec.del_detail_id_tab(l_index);
896
897 l_update_det_rec.requested_qty_tab(l_update_det_rec_idx) := p_matched_detail_rec.requested_qty_tab(l_index);
898
899 l_update_det_rec.shipped_qty_tab(l_update_det_rec_idx) := p_matched_detail_rec.shipped_qty_tab(l_index);
900
901 l_update_det_rec.received_qty_tab(l_update_det_rec_idx) := p_matched_detail_rec.received_qty_tab(l_index);
902
903 l_update_det_rec.returned_qty_tab(l_update_det_rec_idx) := p_matched_detail_rec.returned_qty_tab(l_index);
904
905 l_update_det_rec.requested_qty2_tab(l_update_det_rec_idx) := p_matched_detail_rec.requested_qty2_tab(l_index);
906
907 l_update_det_rec.shipped_qty2_tab(l_update_det_rec_idx) := p_matched_detail_rec.shipped_qty2_tab(l_index);
908
909 l_update_det_rec.received_qty2_tab(l_update_det_rec_idx) := p_matched_detail_rec.received_qty2_tab(l_index);
910
911 l_update_det_rec.returned_qty2_tab(l_update_det_rec_idx) := p_matched_detail_rec.returned_qty2_tab(l_index);
912 l_update_det_rec.shipment_line_id_tab(l_update_det_rec_idx) := p_matched_detail_rec.shipment_line_id_tab(l_index);
913 --l_update_det_rec.released_sts_tab(l_update_det_rec_idx) := null;
914 l_update_det_rec.record_changed_flag_tab(l_update_det_rec_idx) := 'N';
915 l_update_det_rec.wv_changed_flag_tab(l_update_det_rec_idx) := 'N';
916 l_update_det_rec.net_weight_tab(l_update_det_rec_idx) := null;
917 l_update_det_rec.gross_weight_tab(l_update_det_rec_idx) := null;
918 l_update_det_rec.volume_tab(l_update_det_rec_idx) := null;
919
920 IF l_debug_on THEN
921 WSH_DEBUG_SV.log(l_module_name,'requested qty(' || l_update_det_rec_idx|| ')', l_update_det_rec.requested_qty_tab(l_update_det_rec_idx));
922 WSH_DEBUG_SV.log(l_module_name,'received qty(' || l_update_det_rec_idx|| ')', l_update_det_rec.received_qty_tab(l_update_det_rec_idx));
923 WSH_DEBUG_SV.log(l_module_name,'returned qty(' || l_update_det_rec_idx|| ')', l_update_det_rec.returned_qty_tab(l_update_det_rec_idx));
924 WSH_DEBUG_SV.log(l_module_name,'Shipped qty(' || l_update_det_rec_idx|| ')', l_update_det_rec.shipped_qty_tab(l_update_det_rec_idx));
925 END IF;
926
927 -- Here we compare if the requested quantity for a given
928 -- po_line_location_id is not equal to zero or not. If yes,
929 -- then we also make sure that we have accumalated the requested
930 -- quantity for that po_line_location_id completely and we verify
931 -- that by checking if the po_line_location_id has changed or not.
932 -- If yes, then we need to process that quantity to be applied
933 -- onto the open delivery details.
934 IF (l_rem_req_qty_rec.requested_quantity <>0) AND nvl(l_prev_po_line_loc_id,-9999) <>nvl(p_matched_detail_rec.po_line_location_id_tab(l_index), -9999) THEN
935 --{
936 l_rem_req_qty_rec.po_line_location_id := l_prev_po_line_loc_id;
937 l_rem_req_qty_rec.po_line_id := l_prev_po_line_id;
938 Process_remaining_req_quantity (
939 p_rem_req_qty_rec => l_rem_req_qty_rec,
940 p_in_rec => l_pr_rem_in_rec,
941 x_return_status => l_return_status);
942 --
943 -- Debug Statements
944 --
945 IF l_debug_on THEN
946 WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_UTIL_CORE.API_POST_CALL',WSH_DEBUG_SV.C_PROC_LEVEL);
947 END IF;
948 --
949 wsh_util_core.api_post_call(
950 p_return_status => l_return_status,
951 x_num_warnings => l_num_warnings,
952 x_num_errors => l_num_errors);
953
954 -- Initializing it back to 0 so that the subsequent process should not get affected.
955 l_rem_req_qty_rec.requested_quantity := 0;
956 l_rem_req_qty_rec.requested_quantity2 := 0;
957 --}
958 END IF;
959
960
961 IF nvl(p_action_prms.action_code, 'RECEIPT') IN ('RECEIPT_CORRECTION', 'RECEIPT_CORRECTION_NEGATIVE', 'RECEIPT_CORRECTION_POSITIVE', 'RECEIPT') THEN
962 --{
963 process_rcv (
964 x_matched_detail_rec => p_matched_detail_rec,
965 p_matched_detail_index => l_index,
966 x_update_det_rec => l_update_det_rec,
967 p_update_det_rec_idx => l_update_det_rec_idx,
968 x_rem_req_qty_rec => l_rem_req_qty_rec,
969 x_unassign_det_tbl => l_unassign_det_tbl,
970 x_po_line_loc_tbl => l_po_line_loc_tbl,
971 x_delivery_id_tab => l_delivery_id_tab,
972 x_wv_detail_tab => l_wv_detail_tab,
973 x_unassigned_lpn_id_tab => l_unassigned_lpn_id_tab,
974 x_wv_recalc_del_id_tab => l_wv_recalc_del_id_tab,
975 x_return_status => l_return_status);
976
977 --
978 -- Debug Statements
979 --
980 IF l_debug_on THEN
981 WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_UTIL_CORE.API_POST_CALL',WSH_DEBUG_SV.C_PROC_LEVEL);
982 END IF;
983 --
984 wsh_util_core.api_post_call(
985 p_return_status => l_return_status,
986 x_num_warnings => l_num_warnings,
987 x_num_errors => l_num_errors);
988 --}
989 END IF;
990
991 IF nvl(p_action_prms.action_code, 'RECEIPT') IN ( 'RTV_CORRECTION', 'RTV_CORRECTION_NEGATIVE','RTV_CORRECTION_POSITIVE', 'RTV','RECEIPT') THEN
992 --{
993 process_rtv (
994 x_matched_detail_rec => p_matched_detail_rec,
995 p_matched_detail_index => l_index,
996 x_update_det_rec => l_update_det_rec,
997 p_update_det_rec_idx => l_update_det_rec_idx,
998 x_rem_req_qty_rec => l_rem_req_qty_rec,
999 x_return_status => l_return_status);
1000
1001 --
1002 -- Debug Statements
1003 --
1004 IF l_debug_on THEN
1005 WSH_DEBUG_SV.log(l_module_name,'Return Status',l_return_status);
1006 WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_UTIL_CORE.API_POST_CALL',WSH_DEBUG_SV.C_PROC_LEVEL);
1007 END IF;
1008 --
1009 wsh_util_core.api_post_call(
1010 p_return_status => l_return_status,
1011 x_num_warnings => l_num_warnings,
1012 x_num_errors => l_num_errors);
1013 --}
1014 END IF;
1015
1016 IF l_debug_on THEN
1017 WSH_DEBUG_SV.logmsg(l_module_name,'Before assigning the value to l_prev_po_line_loc_id');
1018 END IF;
1019 l_prev_po_line_loc_id := p_matched_detail_rec.po_line_location_id_tab(l_index);
1020 l_prev_po_line_id := p_matched_detail_rec.po_line_id_tab(l_index);
1021 IF l_debug_on THEN
1022 WSH_DEBUG_SV.logmsg(l_module_name,'After assigning the value to l_prev_po_line_loc_id');
1023 WSH_DEBUG_SV.log(l_module_name,'l_index', l_index);
1024 END IF;
1025 l_last_valid_det_index := l_index;
1026 --}
1027 END IF;
1028
1029 --l_index := p_matched_detail_rec.del_detail_id_tab.next(l_index);
1030 --}
1031 end loop;
1032
1033 IF (l_rem_req_qty_rec.requested_quantity <>0) THEN
1034 --{
1035 l_rem_req_qty_rec.po_line_location_id := p_matched_detail_rec.po_line_location_id_tab(l_last_valid_det_index);
1036 l_rem_req_qty_rec.po_line_id := p_matched_detail_rec.po_line_id_tab(l_last_valid_det_index);
1037 Process_remaining_req_quantity (
1038 p_rem_req_qty_rec => l_rem_req_qty_rec,
1039 p_in_rec => l_pr_rem_in_rec,
1040 x_return_status => l_return_status);
1041 --
1042 -- Debug Statements
1043 --
1044 IF l_debug_on THEN
1045 WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_UTIL_CORE.API_POST_CALL',WSH_DEBUG_SV.C_PROC_LEVEL);
1046 END IF;
1047 --
1048 wsh_util_core.api_post_call(
1049 p_return_status => l_return_status,
1050 x_num_warnings => l_num_warnings,
1051 x_num_errors => l_num_errors);
1052 l_rem_req_qty_rec.requested_quantity := 0;
1053 l_rem_req_qty_rec.requested_quantity2 := 0;
1054 --}
1055 END IF;
1056 IF l_debug_on THEN
1057 WSH_DEBUG_SV.logmsg(l_module_name,'Before doing a bulk update');
1058 WSH_DEBUG_SV.log(l_module_name,'received qty before the update', l_update_det_rec.received_qty_tab(1));
1059 WSH_DEBUG_SV.log(l_module_name,'update rec count is', l_update_det_rec.received_qty_tab.count);
1060 WSH_DEBUG_SV.log(l_module_name,'record_changed_flag', l_update_det_rec.record_changed_flag_tab(1));
1061 WSH_DEBUG_SV.log(l_module_name,'del_detid', l_update_det_rec.del_det_id_tab(1));
1062 WSH_DEBUG_SV.log(l_module_name,'shipped_qty_tab', l_update_det_rec.shipped_qty_tab(1));
1063 WSH_DEBUG_SV.log(l_module_name,'shipped_qty2_tab', l_update_det_rec.shipped_qty2_tab(1));
1064 WSH_DEBUG_SV.log(l_module_name,'received_qty_tab', l_update_det_rec.received_qty_tab(1));
1065 WSH_DEBUG_SV.log(l_module_name,'returned_qty_tab', l_update_det_rec.returned_qty_tab(1));
1066 WSH_DEBUG_SV.log(l_module_name,'shipment_line_id_tab', l_update_det_rec.shipment_line_id_tab(1));
1067 WSH_DEBUG_SV.log(l_module_name,'wv_changed_flag_tab', l_update_det_rec.wv_changed_flag_tab(1));
1068 WSH_DEBUG_SV.log(l_module_name,'net_weight_tab', l_update_det_rec.net_weight_tab(1));
1069 WSH_DEBUG_SV.log(l_module_name,'gross_weight_tab', l_update_det_rec.gross_weight_tab(1));
1070 WSH_DEBUG_SV.log(l_module_name,'volume_tab', l_update_det_rec.volume_tab(1));
1071 END IF;
1072 forall i in l_update_det_rec.del_det_id_tab.first..l_update_det_rec.del_det_id_tab.last
1073 update wsh_delivery_details
1074 set requested_quantity = l_update_det_rec.requested_qty_tab(i),
1075 requested_quantity2 = l_update_det_rec.requested_qty2_tab(i),
1076 shipped_quantity = l_update_det_rec.shipped_qty_tab(i),
1077 shipped_quantity2 = l_update_det_rec.shipped_qty2_tab(i),
1078 received_quantity = l_update_det_rec.received_qty_tab(i),
1079 received_quantity2 = l_update_det_rec.received_qty2_tab(i),
1080 returned_quantity = l_update_det_rec.returned_qty_tab(i),
1081 returned_quantity2 = l_update_det_rec.returned_qty2_tab(i),
1082 rcv_shipment_line_id = l_update_det_rec.shipment_line_id_tab(i),
1083 net_weight = decode(l_update_det_rec.wv_changed_flag_tab(i),
1084 'Y',
1085 l_update_det_rec.net_weight_tab(i),
1086 net_weight
1087 ),
1088 gross_weight = decode(l_update_det_rec.wv_changed_flag_tab(i),
1089 'Y',
1090 l_update_det_rec.gross_weight_tab(i),
1091 gross_weight
1092 ),
1093 volume = decode(l_update_det_rec.wv_changed_flag_tab(i),
1094 'Y',
1095 l_update_det_rec.volume_tab(i),
1096 volume
1097 ),
1098 last_update_date = sysdate,
1099 last_updated_by = fnd_global.user_id,
1100 last_update_login = fnd_global.user_id
1101 where l_update_det_rec.record_changed_flag_tab(i) = 'Y'
1102 and delivery_detail_id = l_update_det_rec.del_det_id_tab(i);
1103
1104
1105 IF l_debug_on THEN
1106 WSH_DEBUG_SV.logmsg(l_module_name,'After the bulk update');
1107 WSH_DEBUG_SV.log(l_module_name,'Number of Records updated', SQL%ROWCOUNT);
1108 END IF;
1109
1110 --
1111 -- DBI Project
1112 -- Update of wsh_delivery_details where requested_quantity/released_status
1113 -- are changed, call DBI API after the update.
1114 -- DBI API will check if DBI is installed
1115 IF l_debug_on THEN
1116 WSH_DEBUG_SV.log(l_module_name,'Calling DBI API.Detail Count-',l_update_det_rec.del_det_id_tab.count);
1117 END IF;
1118 WSH_INTEGRATION.DBI_Update_Detail_Log
1119 (p_delivery_detail_id_tab => l_update_det_rec.del_det_id_tab,
1120 p_dml_type => 'UPDATE',
1121 x_return_status => l_dbi_rs);
1122
1123 IF l_debug_on THEN
1124 WSH_DEBUG_SV.log(l_module_name,'Return Status after DBI Call-',l_dbi_rs);
1125 END IF;
1126 -- Only Handle Unexpected error
1127 IF l_dbi_rs = WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR THEN
1128 x_return_status := l_dbi_rs;
1129 ROLLBACK TO process_corrections_and_rtv;
1130 --
1131 IF l_debug_on THEN
1132 WSH_DEBUG_SV.pop(l_module_name);
1133 END IF;
1134 --
1135 RETURN;
1136 END IF;
1137 -- End of Code for DBI Project
1138 --
1139
1140 -- The l_unassign_det_tbl contains the list of delivery details
1141 -- for which there was a complete receipt negative correction
1142 -- and therefore, we need to unassign these delivery details
1143 -- from their respective deliveries.
1144
1145 IF ( l_unassign_det_tbl.count > 0 ) THEN
1146 --{
1147 --
1148 -- Debug Statements
1149 --
1150 IF l_debug_on THEN
1151 WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_DELIVERY_DETAILS_ACTIONS.UNASSIGN_MULTIPLE_DETAILS',WSH_DEBUG_SV.C_PROC_LEVEL);
1152 END IF;
1153 --
1154 l_unassign_action_prms.caller := wsh_util_core.C_IB_RECEIPT_PREFIX;
1155 WSH_DELIVERY_DETAILS_ACTIONS.Unassign_Multiple_Details(
1156 P_REC_OF_DETAIL_IDS => l_unassign_det_tbl,
1157 P_FROM_delivery => 'Y',
1158 P_FROM_container => 'N',
1159 x_return_status => l_return_status,
1160 p_validate_flag => 'Y',
1161 p_action_prms => l_unassign_action_prms);
1162
1163
1164
1165
1166
1167 --
1168 -- Debug Statements
1169 --
1170 IF l_debug_on THEN
1171 WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_UTIL_CORE.API_POST_CALL',WSH_DEBUG_SV.C_PROC_LEVEL);
1172 END IF;
1173 --
1174 wsh_util_core.api_post_call(
1175 p_return_status => l_return_status,
1176 x_num_warnings => l_num_warnings,
1177 x_num_errors => l_num_errors);
1178
1179
1180 -- Need to go through the l_unassigned_lpn_id_tab and delete them
1181 -- if they do not have any delivery lines associated.
1182 IF (l_unassigned_lpn_id_tab.count > 0) THEN
1183 --{
1184 i := l_unassigned_lpn_id_tab.first;
1185 WHILE i IS NOT NULL LOOP
1186 --{
1187 l_lpn_empty_flag := null;
1188 open l_chk_lpn_empty_csr(l_unassigned_lpn_id_tab(i));
1189 fetch l_chk_lpn_empty_csr into l_lpn_empty_flag;
1190 close l_chk_lpn_empty_csr;
1191 IF nvl(l_lpn_empty_flag,'Y') = 'Y' THEN -- delete the lpn
1192 --{
1193 l_delete_lpn_id_tab(l_delete_lpn_id_tab.count + 1) := l_unassigned_lpn_id_tab(i);
1194 --}
1195 END IF;
1196 i := l_unassigned_lpn_id_tab.NEXT(i);
1197 --}
1198 END LOOP;
1199
1200 IF (l_delete_lpn_id_tab.count > 0) THEN
1201 --{
1202 FORALL i IN l_delete_lpn_id_tab.FIRST..l_delete_lpn_id_tab.LAST
1203 DELETE wsh_delivery_assignments_v
1204 WHERE delivery_detail_id = l_delete_lpn_id_tab(i);
1205
1206 IF l_debug_on THEN
1207 WSH_DEBUG_SV.log(l_module_name,'SQL%ROWCOUNT After deleting LPNs from WDA',SQL%ROWCOUNT);
1208 END IF;
1209
1210 --Deleting the rows in WSH_DELIVERY_DETAILS corresponding to the selected LPNs.
1211 FORALL i IN l_delete_lpn_id_tab.FIRST..l_delete_lpn_id_tab.LAST
1212 DELETE WSH_DELIVERY_DETAILS
1213 WHERE delivery_detail_id = l_delete_lpn_id_tab(i);
1214
1215 IF l_debug_on THEN
1216 WSH_DEBUG_SV.log(l_module_name,'SQL%ROWCOUNT After deleting LPNs from WDD',SQL%ROWCOUNT);
1217 END IF;
1218 --}
1219 END IF;
1220
1221 --}
1222 END IF;
1223 -- since the delivery details in l_unassign_det_tbl are being re-opened and unassigned,
1224 -- we need to update these lines with the latest attributes from PO.
1225 -- l_dd_list needs to be populated to finally call Reapprove_PO.
1226
1227 FOR i in l_unassign_det_tbl.FIRST..l_unassign_det_tbl.LAST LOOP
1228 --{
1229 l_dd_list.po_shipment_line_id.extend;
1230 l_dd_list.delivery_detail_id.extend;
1231 l_dd_list.po_shipment_line_id(l_dd_list.po_shipment_line_id.count) := l_po_line_loc_tbl(i);
1232 l_dd_list.delivery_detail_id(l_dd_list.delivery_detail_id.count) := l_unassign_det_tbl(i);
1233 --}
1234 END LOOP;
1235 IF l_debug_on THEN
1236 WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_PO_CMG_PVT.REAPPROVE_PO',WSH_DEBUG_SV.C_PROC_LEVEL);
1237 WSH_DEBUG_SV.log(l_module_name,'l_dd_list.COUNT',l_dd_list.po_shipment_line_id.COUNT);
1238 END IF;
1239 l_po_action_prms.action_code := 'CANCEL_ASN';
1240 l_po_action_prms.caller := 'WSH_RCV_CORR_RTV';
1241 IF (l_dd_list.po_shipment_line_id.COUNT > 0 ) THEN
1242 --{
1243 WSH_PO_CMG_PVT.Reapprove_PO(
1244 p_line_rec => l_line_rec,
1245 p_action_prms => l_po_action_prms,
1246 p_dd_list => l_dd_list,
1247 x_return_status => l_return_status);
1248
1249 --
1250 -- Debug Statements
1251 --
1252 IF l_debug_on THEN
1253 WSH_DEBUG_SV.log(l_module_name,'Return Status after calling Reapprove_PO is ', l_return_status);
1254 WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_UTIL_CORE.API_POST_CALL',WSH_DEBUG_SV.C_PROC_LEVEL);
1255 END IF;
1256 --
1257 wsh_util_core.api_post_call(
1258 p_return_status => l_return_status,
1259 x_num_warnings => l_num_warnings,
1260 x_num_errors => l_num_errors);
1261 --
1262 --}
1263 END IF;
1264 --}
1265 END IF;
1266 IF (l_wv_detail_tab.count > 0 ) THEN
1267 --{
1268 --
1269 -- Debug Statements
1270 --
1271 IF l_debug_on THEN
1272 WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_WV_UTILS.DETAIL_WEIGHT_VOLUME',WSH_DEBUG_SV.C_PROC_LEVEL);
1273 END IF;
1274 --
1275 WSH_WV_UTILS.Detail_Weight_Volume(
1276 p_detail_rows => l_wv_detail_tab,
1277 p_override_flag => 'Y',
1278 p_calc_wv_if_frozen => 'N',
1279 x_return_status => l_return_status);
1280 --
1281 -- Debug Statements
1282 --
1283 IF l_debug_on THEN
1284 WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_UTIL_CORE.API_POST_CALL',WSH_DEBUG_SV.C_PROC_LEVEL);
1285 END IF;
1286 --
1287 wsh_util_core.api_post_call(
1288 p_return_status => l_return_status,
1289 x_num_warnings => l_num_warnings,
1290 x_num_errors => l_num_errors);
1291
1292 --}
1293 END IF;
1294
1295 IF(l_wv_recalc_del_id_tab.count > 0) THEN
1296 --{
1297 --
1298 -- Debug Statements
1299 --
1300 IF l_debug_on THEN
1301 WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_WV_UTILS.DELIVERY_WEIGHT_VOLUME',WSH_DEBUG_SV.C_PROC_LEVEL);
1302 END IF;
1303 --
1304 WSH_WV_UTILS.Delivery_Weight_Volume(
1305 p_del_rows => l_wv_recalc_del_id_tab,
1306 p_update_flag => 'Y',
1307 p_calc_wv_if_frozen => 'N',
1308 x_return_status => l_return_status);
1309
1310 --
1311 -- Debug Statements
1312 --
1313 IF l_debug_on THEN
1314 WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_UTIL_CORE.API_POST_CALL',WSH_DEBUG_SV.C_PROC_LEVEL);
1315 END IF;
1316 --
1317 wsh_util_core.api_post_call(
1318 p_return_status => l_return_status,
1319 x_num_warnings => l_num_warnings,
1320 x_num_errors => l_num_errors);
1321 --}
1322 END IF;
1323
1324 IF (l_delivery_id_tab.count > 0 ) THEN
1325 --{
1326 -- Commented the below code because we are calling detail_weight_volume
1327 -- and that in turn would calculate the weight and volume of the
1328 -- delivery ( and trip and trip stops if present).
1329 /*
1330 --
1331 -- Debug Statements
1332 --
1333 IF l_debug_on THEN
1334 WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_WV_UTILS.DELIVERY_WEIGHT_VOLUME',WSH_DEBUG_SV.C_PROC_LEVEL);
1335 END IF;
1336 --
1337 WSH_WV_UTILS.Delivery_Weight_Volume(
1338 p_del_rows => l_delivery_id_tab,
1339 p_update_flag => 'Y',
1340 p_calc_wv_if_frozen => 'N',
1341 x_return_status => l_return_status);
1342
1343 --
1344 -- Debug Statements
1345 --
1346 IF l_debug_on THEN
1347 WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_UTIL_CORE.API_POST_CALL',WSH_DEBUG_SV.C_PROC_LEVEL);
1348 END IF;
1349 --
1350 wsh_util_core.api_post_call(
1351 p_return_status => l_return_status,
1352 x_num_warnings => l_num_warnings,
1353 x_num_errors => l_num_errors);
1354 */
1355
1356
1357 --
1358 -- Debug Statements
1359 --
1360 IF l_debug_on THEN
1361 WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_DELIVERY_LEGS_ACTIONS.MARK_REPRICE_REQUIRED',WSH_DEBUG_SV.C_PROC_LEVEL);
1362 END IF;
1363 --
1364 WSH_DELIVERY_LEGS_ACTIONS.Mark_Reprice_Required(
1365 p_entity_type => 'DELIVERY',
1366 p_entity_ids => l_delivery_id_tab,
1367 x_return_status => l_return_status);
1368
1369 --
1370 -- Debug Statements
1371 --
1372 IF l_debug_on THEN
1373 WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_UTIL_CORE.API_POST_CALL',WSH_DEBUG_SV.C_PROC_LEVEL);
1374 END IF;
1375 --
1376 wsh_util_core.api_post_call(
1377 p_return_status => l_return_status,
1378 x_num_warnings => l_num_warnings,
1379 x_num_errors => l_num_errors);
1380
1381 --
1382 -- Debug Statements
1383 --
1384 IF l_debug_on THEN
1385 WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_INBOUND_UTIL_PKG.RERATEDELIVERIES',WSH_DEBUG_SV.C_PROC_LEVEL);
1386 END IF;
1387 --
1388 WSH_INBOUND_UTIL_PKG.reRateDeliveries(
1389 p_delivery_id_tab => l_delivery_id_tab,
1390 x_return_status => l_return_status);
1391 --
1392 -- Debug Statements
1393 --
1394 IF l_debug_on THEN
1395 WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_UTIL_CORE.API_POST_CALL',WSH_DEBUG_SV.C_PROC_LEVEL);
1396 END IF;
1397 --
1398 wsh_util_core.api_post_call(
1399 p_return_status => l_return_status,
1400 x_num_warnings => l_num_warnings,
1401 x_num_errors => l_num_errors);
1402 --}
1403 END IF;
1404
1405 for l_index in p_matched_detail_rec.del_detail_id_tab.first..p_matched_detail_rec.del_detail_id_tab.last loop
1406 --{
1407 --
1408 -- Debug Statements
1409 --
1410 IF l_debug_on THEN
1411 WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_UTIL_CORE.GET_CACHED_VALUE',WSH_DEBUG_SV.C_PROC_LEVEL);
1412 END IF;
1413 --
1414 wsh_util_core.get_cached_value(
1415 p_cache_tbl => l_po_line_loc_cache_tbl,
1416 p_cache_ext_tbl => l_po_line_loc_ext_cache_tbl,
1417 p_value => p_matched_detail_rec.po_line_location_id_tab(l_index),
1418 p_key => p_matched_detail_rec.po_line_location_id_tab(l_index),
1419 p_action => 'GET',
1420 x_return_status => l_return_status);
1421
1422 IF l_return_status IN (wsh_util_core.g_ret_sts_error, wsh_util_core.g_ret_sts_unexp_error) THEN
1423 RAISE FND_API.G_EXC_ERROR;
1424 END IF;
1425
1426 IF (l_return_status = wsh_util_core.g_ret_sts_warning) THEN
1427 --{
1428 --
1429 -- Debug Statements
1430 --
1431 IF l_debug_on THEN
1432 WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_UTIL_CORE.GET_CACHED_VALUE',WSH_DEBUG_SV.C_PROC_LEVEL);
1433 END IF;
1434 --
1435 wsh_util_core.get_cached_value(
1436 p_cache_tbl => l_po_line_loc_cache_tbl,
1437 p_cache_ext_tbl => l_po_line_loc_ext_cache_tbl,
1438 p_value => p_matched_detail_rec.po_line_location_id_tab(l_index),
1439 p_key => p_matched_detail_rec.po_line_location_id_tab(l_index),
1440 p_action => 'PUT',
1441 x_return_status => l_return_status);
1442 --
1443 -- Debug Statements
1444 --
1445 IF l_debug_on THEN
1446 WSH_DEBUG_SV.log(l_module_name,'Return Status after calling wsh_util_core.get_cached_value for put is',l_return_status);
1447 WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_UTIL_CORE.API_POST_CALL',WSH_DEBUG_SV.C_PROC_LEVEL);
1448 END IF;
1449 --
1450 wsh_util_core.api_post_call(
1451 p_return_status => l_return_status,
1452 x_num_warnings => l_num_warnings,
1453 x_num_errors => l_num_errors);
1454
1455 IF (PO_CODE_RELEASE_GRP.Current_Release >= PO_CODE_RELEASE_GRP.PRC_11i_Family_Pack_J) THEN
1456 --{
1457 PO_FTE_INTEGRATION_GRP.po_status_check (
1458 p_api_version => 1.0,
1459 p_header_id => p_matched_detail_rec.po_header_id_tab(l_index),
1460 p_release_id => NULL,
1461 p_document_type => NULL,
1462 p_document_subtype => NULL,
1463 p_document_num => NULL,
1464 p_vendor_order_num => NULL,
1465 p_line_id => p_matched_detail_rec.po_line_id_tab(l_index),
1466 p_line_location_id => p_matched_detail_rec.po_line_location_id_tab(l_index),
1467 p_distribution_id => NULL,
1468 p_mode => 'GET_STATUS',
1469 p_lock_flag => 'N',
1470 x_po_status_rec => l_po_status_rec,
1471 x_return_status => l_return_status);
1472 IF l_debug_on THEN
1473 WSH_DEBUG_SV.log(l_module_name,'l_index', l_index);
1474 WSH_DEBUG_SV.log(l_module_name,'return_status is', l_return_status);
1475 WSH_DEBUG_SV.log(l_module_name,'count of l_po_status_rec.closed_code', l_po_status_rec.closed_code.count);
1476 END IF;
1477 wsh_util_core.api_post_call(
1478 p_return_status => l_return_status,
1479 x_num_warnings => l_num_warnings,
1480 x_num_errors => l_num_errors);
1481 IF l_po_status_rec.closed_code.count > 0 THEN
1482 --{
1483 IF (nvl(l_po_status_rec.closed_code(l_po_status_rec.closed_code.first),'N') IN ('CLOSED', 'CLOSED FOR RECEIVING', 'FINALLY CLOSED') AND nvl(l_po_status_rec.cancel_flag(l_po_status_rec.cancel_flag.first), 'N') <> 'Y') THEN
1484 --{
1485 x_po_close_rec.po_shipment_line_id.extend;
1486 x_po_close_rec.line_id.extend;
1487 x_po_close_rec.header_id.extend;
1488 x_po_close_rec.source_blanket_reference_id.extend;
1489 x_po_close_rec.line_id(x_po_close_rec.line_id.count) := p_matched_detail_rec.po_line_id_tab(l_index);
1490 x_po_close_rec.po_shipment_line_id(x_po_close_rec.line_id.count) := p_matched_detail_rec.po_line_location_id_tab(l_index);
1491 x_po_close_rec.header_id(x_po_close_rec.line_id.count) := p_matched_detail_rec.po_header_id_tab(l_index);
1492 --}
1493 ELSIF (nvl(l_po_status_rec.cancel_flag(l_po_status_rec.cancel_flag.first), 'N') = 'Y') THEN
1494 --{
1495 x_po_cancel_rec.po_shipment_line_id.extend;
1496 x_po_cancel_rec.line_id.extend;
1497 x_po_cancel_rec.header_id.extend;
1498 x_po_cancel_rec.source_blanket_reference_id.extend;
1499 x_po_cancel_rec.line_id(x_po_cancel_rec.line_id.count) := p_matched_detail_rec.po_line_id_tab(l_index);
1500 x_po_cancel_rec.po_shipment_line_id(x_po_cancel_rec.line_id.count) := p_matched_detail_rec.po_line_location_id_tab(l_index);
1501 x_po_cancel_rec.header_id(x_po_cancel_rec.line_id.count) := p_matched_detail_rec.po_header_id_tab(l_index);
1502 --}
1503 END IF;
1504 --}
1505 END IF;
1506 --}
1507 END IF;
1508 IF l_debug_on THEN
1509 WSH_DEBUG_SV.logmsg(l_module_name,'After checking for status');
1510 END IF;
1511 --}
1512 END IF;
1513
1514 --}
1515 end loop;
1516 --
1517
1518 IF l_num_warnings > 0 THEN
1519 RAISE WSH_UTIL_CORE.G_EXC_WARNING;
1520 ELSE
1521 x_return_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
1522 END IF;
1523 --}
1524 --
1525 -- Debug Statements
1526 --
1527 IF l_debug_on THEN
1528 WSH_DEBUG_SV.log(l_module_name,'Return Status after calling Post_Process',l_return_status);
1529 WSH_DEBUG_SV.pop(l_module_name);
1530 END IF;
1531 --
1532 EXCEPTION
1533 --{
1534 WHEN FND_API.G_EXC_ERROR THEN
1535 ROLLBACK TO process_corrections_and_rtv;
1536 x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
1537 --
1538 -- Debug Statements
1539 --
1540 IF l_debug_on THEN
1541 WSH_DEBUG_SV.logmsg(l_module_name,'FND_API.G_EXC_ERROR exception has occured.',WSH_DEBUG_SV.C_EXCEP_LEVEL);
1542 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:FND_API.G_EXC_ERROR');
1543 END IF;
1544 --
1545 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1546 ROLLBACK TO process_corrections_and_rtv;
1547 x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR ;
1548 --
1549 -- Debug Statements
1550 --
1551 IF l_debug_on THEN
1552 WSH_DEBUG_SV.logmsg(l_module_name,'FND_API.G_EXC_UNEXPECTED_ERROR exception has occured.',WSH_DEBUG_SV.C_EXCEP_LEVEL);
1553 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:FND_API.G_EXC_UNEXPECTED_ERROR');
1554 END IF;
1555 --
1556 WHEN WSH_UTIL_CORE.G_EXC_WARNING THEN
1557 x_return_status := WSH_UTIL_CORE.G_RET_STS_WARNING;
1558 --
1559 -- Debug Statements
1560 --
1561 IF l_debug_on THEN
1562 WSH_DEBUG_SV.logmsg(l_module_name,'WSH_UTIL_CORE.G_EXC_WARNING exception has occured.',WSH_DEBUG_SV.C_EXCEP_LEVEL);
1563 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:WSH_UTIL_CORE.G_EXC_WARNING');
1564 END IF;
1565 --
1566 WHEN OTHERS THEN
1567 ROLLBACK TO process_corrections_and_rtv;
1568 x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR ;
1569 wsh_util_core.default_handler('WSH_IB_UI_RECON_GRP.PROCESS_CORRECTIONS_AND_RTV');
1570 --}
1571 --
1572 -- Debug Statements
1573 --
1574 IF l_debug_on THEN
1575 WSH_DEBUG_SV.logmsg(l_module_name,'Unexpected error has occured. Oracle error message is '|| SQLERRM,WSH_DEBUG_SV.C_UNEXPEC_ERR_LEVEL);
1576 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:OTHERS');
1577 END IF;
1578 --
1579 END process_corrections_and_rtv;
1580
1581 --========================================================================
1582 -- PROCEDURE : Process_Remaining_Req_Quantity --
1583 -- This procedure is called from
1584 -- process_corrections_and_rtv
1585 -- and from revert_details to handle
1586 -- the remaining requested quantity
1587 -- that needs to be adjusted on open
1588 -- delivery details.
1589 --
1590 -- PARAMETERS: p_rem_req_qty_rec Record that stores the remaining
1591 -- requested quantity after performing the
1592 -- matching or after performing the revert.
1593 -- p_in_rec Input record to pass the action code
1594 -- (possible values are "MATCH" and
1595 -- "REVERT_MATCH").
1596 -- x_return_status Return status of the API
1597
1598 -- VERSION : current version 1.0
1599 -- initial version 1.0
1600 -- COMMENT : This procedure is used to handle the remaining requested
1601 -- quantity to be update on open delivery details.
1602 -- The following is flow of this procedure -
1603 -- 1. If the p_in_rec.action_code is 'MATCH', then we all the open
1604 -- delivery details for which routing_req_id is null
1605 -- else if p_in_rec.action_code is 'REVERT_MATCH', then
1606 -- we get all the open delivery details and also the closed
1607 -- lines for which the received_quantity is null (these lines
1608 -- are probably closed as po might have been closed).
1609 -- Then we collect all these lines into a table.
1610 -- 2. If the remaining requested quantity is < 0, then
1611 -- we loop through all the delivery details and for delivery
1612 -- we compare its req. qty with the remaining req. qty.
1613 -- if the abs(remaining req. qty) > curr detail's req. qty
1614 -- we delete the delivery detail and the delivery assignment
1615 -- and decrement the remaining req. qty accordingly and we
1616 -- we repeat this until the abs(remaining req. qty) becomes
1617 -- less the del detail's req. qty. Then we just update
1618 -- that delivery detail with req. qty = (detail's req. qty
1619 -- - abs(remaining req. qty.).
1620 -- 3. If the remaining requested quantity is > 0, then
1621 -- we call the WSH_INBOUND_UTIL_PKG.get_po_rcv_attributes
1622 -- to get all the latest attributes of PO and then
1623 -- call reapprove_po to either update or create new delivery
1624 -- lines.
1625 -- Please refer to Appendix. 7 wsh_inbound_corr_rtv_revert_ui.rtf
1626 -- for the examples to explain the cases handled by this API.
1627 --========================================================================
1628
1629 PROCEDURE process_remaining_req_quantity (
1630 p_rem_req_qty_rec IN rem_req_qty_rec_type,
1631 p_in_rec IN action_in_rec_type,
1632 x_return_status OUT NOCOPY VARCHAR2)
1633 IS
1634 --{
1635 l_new_req_qty NUMBER := p_rem_req_qty_rec.requested_quantity;
1636 l_new_req_qty2 NUMBER := p_rem_req_qty_rec.requested_quantity2;
1637 l_update_rec update_detail_rec_type;
1638 l_update_del_det_id NUMBER;
1639 l_update_del_det_id_tab wsh_util_core.id_tab_type;
1640 l_update_del_det_req_qty NUMBER;
1641 l_update_del_det_req_qty2 NUMBER;
1642 l_record_found BOOLEAN := TRUE;
1643 l_num_errors NUMBER;
1644 l_msg_data VARCHAR2(32767);
1645 l_return_status VARCHAR2(1);
1646 l_msg_count NUMBER;
1647 l_num_warnings NUMBER;
1648 i NUMBER;
1649 l_line_rec_index NUMBER;
1650 l_req_qty_uom VARCHAR2(30);
1651 l_ordered_quantity NUMBER;
1652 l_ordered_quantity2 NUMBER;
1653 l_action_prms WSH_BULK_TYPES_GRP.action_parameters_rectype;
1654 l_line_rec OE_WSH_BULK_GRP.line_rec_type;
1655 l_out_rec WSH_BULK_TYPES_GRP.Bulk_process_out_rec_type;
1656 l_header_ids wsh_util_core.id_tab_type;
1657 l_dd_list WSH_PO_CMG_PVT.dd_list_type;
1658
1659 l_po_line_rec PO_FTE_INTEGRATION_GRP.po_release_rec_type;
1660
1661 -- This cursor is used to get the open delivery details
1662 -- for which routing request is not sent.
1663 cursor l_open_del_det_csr(p_po_line_loc_id NUMBER,
1664 p_source_line_id IN NUMBER) is
1665 select delivery_detail_id,
1666 requested_quantity,
1667 requested_quantity2
1668 from wsh_delivery_details
1669 where source_line_id = p_source_line_id
1670 and po_shipment_line_id = p_po_line_loc_id
1671 and released_status = 'X'
1672 and routing_req_id is null
1673 and source_code = 'PO'
1674 order by requested_quantity desc;
1675 --for update of requested_quantity nowait;
1676
1677 -- This cursor is used to get the open and closed delivery details
1678 -- for which routing request is not sent.
1679 cursor l_all_del_det_csr(p_po_line_loc_id NUMBER,
1680 p_source_line_id IN NUMBER) is
1681 select delivery_detail_id,
1682 requested_quantity,
1683 requested_quantity2
1684 from wsh_delivery_details
1685 where source_line_id = p_source_line_id
1686 and po_shipment_line_id = p_po_line_loc_id
1687 and ( released_status = 'X'
1688 or
1689 ( released_status = 'L'
1690 and received_quantity is null
1691 )
1692 )
1693 and routing_req_id is null
1694 and source_code = 'PO'
1695 order by requested_quantity desc,
1696 decode (released_status,
1697 'X',1,
1698 'L',2);
1699 --for update of requested_quantity nowait;
1700
1701 -- This cursor is used to just get the uom stored on wsh_delivery_details
1702 -- This is not used anymore as the input record structure already has the UOMs
1703 -- passed.
1704 cursor l_del_det_uom_csr(p_po_line_loc_id NUMBER,
1705 p_source_line_id IN NUMBER) is
1706 select requested_quantity_uom
1707 from wsh_delivery_details
1708 where source_line_id = p_source_line_id
1709 and source_code = 'PO'
1710 and po_shipment_line_id = p_po_line_loc_id;
1711
1712 -- For negative rtv corrections, even after updating the open or
1713 -- closed lines without routing request, if we are still left with
1714 -- some negative quantity, then, we query even the lines that had
1715 -- routing request, lines that were matched against ASN and lines
1716 -- that were matched against receipt and reduce their requested
1717 -- quantities to fulfill the returned quantity correction.
1718 -- This cursor servers this purpose.
1719 cursor l_rem_ret_qty_csr(p_po_line_loc_id NUMBER,
1720 p_source_line_id IN NUMBER) is
1721 select delivery_detail_id,
1722 requested_quantity,
1723 requested_quantity2,
1724 'N' record_changed_flag
1725 from wsh_delivery_details
1726 where source_line_id = p_source_line_id
1727 and po_shipment_line_id = p_po_line_loc_id
1728 and released_status in ('X', 'C', 'L')
1729 and source_code = 'PO'
1730 and requested_quantity > 0
1731 order by
1732 decode (released_status,
1733 'X',1,
1734 'C',2,
1735 'L',3),
1736 delivery_detail_id desc
1737 for update of requested_quantity nowait;
1738 --
1739 --
1740 DD_LOCKED exception;
1741 PRAGMA EXCEPTION_INIT(DD_LOCKED, -54);
1742
1743 --}
1744 l_dbi_rs VARCHAR2(1); -- Return Status from DBI API
1745 --
1746 l_debug_on BOOLEAN;
1747 --
1748 l_module_name CONSTANT VARCHAR2(100) := 'wsh.plsql.' || G_PKG_NAME || '.' || 'PROCESS_REMAINING_REQ_QUANTITY';
1749 --
1750 BEGIN
1751 --{
1752 --
1753 l_debug_on := WSH_DEBUG_INTERFACE.g_debug;
1754 --
1755 IF l_debug_on IS NULL
1756 THEN
1757 l_debug_on := WSH_DEBUG_SV.is_debug_enabled;
1758 END IF;
1759 --
1760 --
1761 -- Debug Statements
1762 --
1763 IF l_debug_on THEN
1764 WSH_DEBUG_SV.push(l_module_name);
1765 WSH_DEBUG_SV.log(l_module_name,'Remaining Requested Quantity is', p_rem_req_qty_rec.requested_quantity);
1766 WSH_DEBUG_SV.log(l_module_name,'po line location id', p_rem_req_qty_rec.po_line_location_id);
1767 WSH_DEBUG_SV.log(l_module_name,'po line id', p_rem_req_qty_rec.po_line_id);
1768 END IF;
1769 --
1770 SAVEPOINT PR_REM_REQ_QTY;
1771 x_return_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
1772 IF nvl(p_in_rec.action_code, 'MATCH') <> 'REVERT_MATCH' THEN
1773 --{
1774 open l_open_del_det_csr(p_rem_req_qty_rec.po_line_location_id,
1775 p_rem_req_qty_rec.po_line_id);
1776
1777 fetch l_open_del_det_csr bulk collect into l_update_rec.del_det_id_tab,
1778 l_update_rec.requested_qty_tab,
1779 l_update_rec.requested_qty2_tab;
1780 close l_open_del_det_csr;
1781 --}
1782 ELSE
1783 --{
1784 open l_all_del_det_csr(p_rem_req_qty_rec.po_line_location_id,
1785 p_rem_req_qty_rec.po_line_id);
1786
1787 fetch l_all_del_det_csr bulk collect into l_update_rec.del_det_id_tab,
1788 l_update_rec.requested_qty_tab,
1789 l_update_rec.requested_qty2_tab;
1790 close l_all_del_det_csr;
1791 --}
1792 END IF;
1793
1794 IF l_debug_on THEN
1795 WSH_DEBUG_SV.log(l_module_name,'count of update rec tables is', l_update_rec.del_det_id_tab.COUNT);
1796 WSH_DEBUG_SV.log(l_module_name,'l_new_req_qty', l_new_req_qty);
1797 END IF;
1798 IF (l_update_rec.del_det_id_tab.COUNT < 1 ) THEN
1799 --{
1800 l_record_found := FALSE;
1801 --}
1802 END IF;
1803 IF ( l_new_req_qty > 0 ) THEN
1804 --{
1805 -- call PO's API to obtain all the attributes for the po_line_location_id
1806 --
1807 -- Debug Statements
1808 --
1809 IF l_debug_on THEN
1810 WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_INBOUND_UTIL_PKG.GET_PO_RCV_ATTRIBUTES',WSH_DEBUG_SV.C_PROC_LEVEL);
1811 END IF;
1812 --
1813 WSH_INBOUND_UTIL_PKG.get_po_rcv_attributes(
1814 p_po_line_location_id => p_rem_req_qty_rec.po_line_location_id,
1815 p_rcv_shipment_line_id => NULL,
1816 x_line_rec => l_line_rec,
1817 x_return_status => l_return_status);
1818 --
1819 -- Debug Statements
1820 --
1821 IF l_debug_on THEN
1822 WSH_DEBUG_SV.log(l_module_name,'return status after calling get_po_rcv_attributes', l_return_status);
1823 WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_UTIL_CORE.API_POST_CALL',WSH_DEBUG_SV.C_PROC_LEVEL);
1824 END IF;
1825 --
1826 wsh_util_core.api_post_call(
1827 p_return_status => l_return_status,
1828 x_num_warnings => l_num_warnings,
1829 x_num_errors => l_num_errors);
1830 IF l_debug_on THEN
1831 WSH_DEBUG_SV.log(l_module_name,'Before assigning the consolidate quantity', l_new_req_qty);
1832 WSH_DEBUG_SV.log(l_module_name,'l_new_req_qty2', l_new_req_qty2);
1833 WSH_DEBUG_SV.log(l_module_name,'req qty uom', p_rem_req_qty_rec.requested_quantity_uom);
1834 WSH_DEBUG_SV.log(l_module_name,'req qty uom 2', p_rem_req_qty_rec.requested_quantity2_uom);
1835 END IF;
1836 l_line_rec.consolidate_quantity.extend;
1837 l_line_rec.consolidate_quantity(1) := l_new_req_qty;
1838 l_line_rec.requested_quantity_uom(1) := p_rem_req_qty_rec.requested_quantity_uom;
1839 l_line_rec.requested_quantity2(1) := l_new_req_qty2;
1840 l_line_rec.requested_quantity_uom2(1) := p_rem_req_qty_rec.requested_quantity2_uom;
1841 l_action_prms.action_code := 'RECEIPT';
1842 l_action_prms.caller := 'WSH_RCV_CORR_RTV';
1843 --
1844 -- Debug Statements
1845 --
1846 IF l_debug_on THEN
1847 WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_PO_CMG_PVT.REAPPROVE_PO',WSH_DEBUG_SV.C_PROC_LEVEL);
1848 END IF;
1849 --
1850 WSH_PO_CMG_PVT.reapprove_po(
1851 p_line_rec => l_line_rec,
1852 p_action_prms => l_action_prms,
1853 p_dd_list => l_dd_list,
1854 x_return_status => l_return_status);
1855 --
1856 IF l_debug_on THEN
1857 WSH_DEBUG_SV.log(l_module_name,'Return Status after calling reapprove_po is', l_return_status);
1858 WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_UTIL_CORE.API_POST_CALL',WSH_DEBUG_SV.C_PROC_LEVEL);
1859 END IF;
1860 --
1861 wsh_util_core.api_post_call(
1862 p_return_status => l_return_status,
1863 x_num_warnings => l_num_warnings,
1864 x_num_errors => l_num_errors);
1865 --}
1866 ELSIF ( l_new_req_qty < 0 ) THEN
1867 --{
1868 i := l_update_rec.del_det_id_tab.first;
1869 WHILE i is not null AND l_new_req_qty < 0 LOOP
1870 --{
1871
1872 IF l_debug_on THEN
1873 WSH_DEBUG_SV.log(l_module_name,'i is', i);
1874 WSH_DEBUG_SV.log(l_module_name,'Delivery Detail Id', l_update_rec.del_det_id_tab(i));
1875 WSH_DEBUG_SV.log(l_module_name,'l_new_req_qty', l_new_req_qty);
1876 WSH_DEBUG_SV.log(l_module_name,'Delivery Detail Req Qty ', l_update_rec.requested_qty_tab(i));
1877 WSH_DEBUG_SV.log(l_module_name,'l_new_req_qty2', l_new_req_qty2);
1878 WSH_DEBUG_SV.log(l_module_name,'Delivery Detail Req Qty 2', l_update_rec.requested_qty2_tab(i));
1879 END IF;
1880
1881 l_update_rec.record_changed_flag_tab (i) := 'N';
1882 IF l_update_rec.requested_qty_tab(i) > abs(l_new_req_qty) THEN
1883 --{
1884 l_update_rec.requested_qty_tab(i) := l_update_rec.requested_qty_tab(i) + l_new_req_qty;
1885 IF (nvl(l_new_req_qty2,0) <> 0 ) THEN
1886 --{
1887 l_update_rec.requested_qty2_tab(i) := nvl(l_update_rec.requested_qty2_tab(i),0) +
1888 l_new_req_qty2;
1889
1890 IF (l_update_rec.requested_qty2_tab(i) < 0) THEN
1891 --{
1892 l_update_rec.requested_qty2_tab(i) := 0;
1893 --}
1894 END IF;
1895 --}
1896 END IF;
1897
1898 l_update_rec.record_changed_flag_tab (i) := 'Y';
1899 l_new_req_qty := 0; -- so that we can go out the loop as we already
1900 -- updated the remaining quantity on the lines.
1901 l_update_del_det_id := l_update_rec.del_det_id_tab(i);
1902 l_update_del_det_req_qty := l_update_rec.requested_qty_tab(i);
1903 l_update_del_det_req_qty2 := l_update_rec.requested_qty2_tab(i);
1904 --}
1905 ELSE -- l_update_rec.requested_qty_tab(i) <= abs(l_new_req_qty)
1906 --{
1907 --
1908 -- Debug Statements
1909 --
1910 IF l_debug_on THEN
1911 WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_DELIVERY_DETAILS_PKG.LOCK_DETAIL_NO_COMPARE',WSH_DEBUG_SV.C_PROC_LEVEL);
1912 END IF;
1913 --
1914 wsh_delivery_details_pkg.Lock_Detail_No_Compare(
1915 p_delivery_detail_id => l_update_rec.del_det_id_tab(i));
1916
1917 IF l_debug_on THEN
1918 WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_DELIVERY_DETAILS_PKG.DELETE_DELIVERY_DETAILS',WSH_DEBUG_SV.C_PROC_LEVEL);
1919 END IF;
1920
1921 wsh_delivery_details_pkg.delete_delivery_details(
1922 p_delivery_detail_id => l_update_rec.del_det_id_tab(i),
1923 x_return_status => l_return_status);
1924 --
1925 -- Debug Statements
1926 --
1927 IF l_debug_on THEN
1928 WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_UTIL_CORE.API_POST_CALL',WSH_DEBUG_SV.C_PROC_LEVEL);
1929 END IF;
1930 --
1931 wsh_util_core.api_post_call(
1932 p_return_status => l_return_status,
1933 x_num_warnings => l_num_warnings,
1934 x_num_errors => l_num_errors);
1935
1936 l_new_req_qty := l_new_req_qty + l_update_rec.requested_qty_tab(i);
1937 IF (l_update_rec.requested_qty2_tab(i) IS NOT NULL) THEN
1938 --{
1939 l_new_req_qty2 := l_new_req_qty2 + l_update_rec.requested_qty2_tab(i);
1940 IF (l_new_req_qty2 > 0) THEN
1941 --{
1942 l_new_req_qty2 := 0;
1943 --}
1944 END IF;
1945 --}
1946 END IF;
1947
1948 --}
1949 END IF;
1950 IF l_debug_on THEN
1951 WSH_DEBUG_SV.log(l_module_name,'l_new_req_qty at the end of processing the current record ', l_new_req_qty);
1952 WSH_DEBUG_SV.log(l_module_name,'Record Changed flag for the current record is', l_update_rec.record_changed_flag_tab(i));
1953 END IF;
1954
1955 i := l_update_rec.del_det_id_tab.NEXT(i);
1956 /*
1957 IF NOT (l_update_rec.del_det_id_tab(i).EXISTS) THEN
1958 --{
1959 l_new_req_qty := 0; -- so that we can exit the loop.
1960 --}
1961 END IF;
1962 */
1963 --}
1964 END LOOP;
1965 IF l_debug_on THEN
1966 WSH_DEBUG_SV.logmsg(l_module_name,'Before performing the update');
1967 WSH_DEBUG_SV.log(l_module_name,'Delivery Detail Id is ', l_update_del_det_id);
1968 END IF;
1969
1970 IF l_update_del_det_id IS NOT NULL THEN
1971 --{
1972 IF l_debug_on THEN
1973 WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_DELIVERY_DETAILS_PKG.LOCK_DETAIL_NO_COMPARE',WSH_DEBUG_SV.C_PROC_LEVEL);
1974 END IF;
1975 --
1976 wsh_delivery_details_pkg.Lock_Detail_No_Compare(
1977 p_delivery_detail_id => l_update_del_det_id);
1978 -- we need to update atmost one record.
1979 update wsh_delivery_details
1980 set requested_quantity = l_update_del_det_req_qty,
1981 requested_quantity2 = nvl(l_update_del_det_req_qty2,requested_quantity2),
1982 last_update_date = sysdate,
1983 last_updated_by = fnd_global.user_id,
1984 last_update_login = fnd_global.user_id
1985 where delivery_detail_id = l_update_del_det_id;
1986 IF l_debug_on THEN
1987 WSH_DEBUG_SV.logmsg(l_module_name,'After the update');
1988 END IF;
1989 --
1990 l_update_del_det_id_tab(1) := l_update_del_det_id;
1991
1992 --
1993 -- DBI Project
1994 -- Update of wsh_delivery_details where requested_quantity/released_status
1995 -- are changed, call DBI API after the update.
1996 -- DBI API will check if DBI is installed
1997 IF l_debug_on THEN
1998 WSH_DEBUG_SV.log(l_module_name,'Calling DBI API.Detail Count-', l_update_del_det_id_tab.count);
1999 END IF;
2000 WSH_INTEGRATION.DBI_Update_Detail_Log
2001 (p_delivery_detail_id_tab => l_update_del_det_id_tab,
2002 p_dml_type => 'UPDATE',
2003 x_return_status => l_dbi_rs);
2004
2005 IF l_debug_on THEN
2006 WSH_DEBUG_SV.log(l_module_name,'Return Status after DBI Call-',l_dbi_rs);
2007 END IF;
2008 -- Only Handle Unexpected error
2009 IF l_dbi_rs = WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR THEN
2010 --
2011 x_return_status := l_dbi_rs;
2012 ROLLBACK TO PR_REM_REQ_QTY;
2013 IF l_debug_on THEN
2014 WSH_DEBUG_SV.pop(l_module_name);
2015 END IF;
2016 --
2017 RETURN;
2018 END IF;
2019 -- End of Code for DBI Project
2020 --
2021 --
2022 -- Debug Statements
2023 --
2024 IF l_debug_on THEN
2025 WSH_DEBUG_SV.log(l_module_name,'Need to recalculate weight and volume for del detail --- ',l_update_del_det_id);
2026 WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_WV_UTILS.DETAIL_WEIGHT_VOLUME',WSH_DEBUG_SV.C_PROC_LEVEL);
2027 END IF;
2028 --
2029 WSH_WV_UTILS.Detail_Weight_Volume(
2030 p_detail_rows => l_update_del_det_id_tab,
2031 p_override_flag => 'Y',
2032 p_calc_wv_if_frozen => 'N',
2033 x_return_status => l_return_status);
2034 --
2035 -- Debug Statements
2036 --
2037 IF l_debug_on THEN
2038 WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_UTIL_CORE.API_POST_CALL',WSH_DEBUG_SV.C_PROC_LEVEL);
2039 END IF;
2040 --
2041 wsh_util_core.api_post_call(
2042 p_return_status => l_return_status,
2043 x_num_warnings => l_num_warnings,
2044 x_num_errors => l_num_errors);
2045 --
2046 -- Nullifying the value so that it is not used again.
2047 l_update_del_det_id := NULL;
2048
2049 --}
2050 END IF;
2051 --
2052 --
2053 -- This logic is added for rtv negative correction
2054 -- where when we have some more left over quantity that still
2055 -- needs to be corrected on the other delivery lines.
2056 IF nvl(p_in_rec.action_code, 'MATCH') <> 'REVERT_MATCH'
2057 AND l_new_req_qty < 0 THEN
2058 --{
2059 l_update_rec.del_det_id_tab.delete;
2060 l_update_rec.requested_qty_tab.delete;
2061 l_update_rec.requested_qty2_tab.delete;
2062 open l_rem_ret_qty_csr(p_rem_req_qty_rec.po_line_location_id,
2063 p_rem_req_qty_rec.po_line_id);
2064
2065 fetch l_rem_ret_qty_csr bulk collect into l_update_rec.del_det_id_tab,
2066 l_update_rec.requested_qty_tab,
2067 l_update_rec.requested_qty2_tab,
2068 l_update_rec.record_changed_flag_tab;
2069 close l_rem_ret_qty_csr;
2070 IF l_debug_on THEN
2071 WSH_DEBUG_SV.log(l_module_name,'Need to recalculate weight and volume for del detail --- ',l_update_del_det_id);
2072 WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_WV_UTILS.DETAIL_WEIGHT_VOLUME',WSH_DEBUG_SV.C_PROC_LEVEL);
2073 END IF;
2074 -- This loop is exact repetition (except for the deletes) of the above loop
2075 -- but since this is just to take care of a corner
2076 -- case, we are repeating the code.
2077 -- This must be cleaned up in next release.
2078 i := l_update_rec.del_det_id_tab.first;
2079 WHILE i is not null AND l_new_req_qty < 0 LOOP
2080 --{
2081
2082 IF l_debug_on THEN
2083 WSH_DEBUG_SV.log(l_module_name,'i is', i);
2084 WSH_DEBUG_SV.log(l_module_name,'Delivery Detail Id ('||i||')', l_update_rec.del_det_id_tab(i));
2085 WSH_DEBUG_SV.log(l_module_name,'l_new_req_qty', l_new_req_qty);
2086 WSH_DEBUG_SV.log(l_module_name,'Delivery Detail Req Qty ('||i||')', l_update_rec.requested_qty_tab(i));
2087 WSH_DEBUG_SV.log(l_module_name,'l_new_req_qty2', l_new_req_qty2);
2088 WSH_DEBUG_SV.log(l_module_name,'Delivery Detail Req Qty 2 ('||i||')', l_update_rec.requested_qty2_tab(i));
2089 END IF;
2090
2091 IF l_update_rec.requested_qty_tab(i) > abs(l_new_req_qty) THEN
2092 --{
2093 l_update_rec.requested_qty_tab(i) := l_update_rec.requested_qty_tab(i) + l_new_req_qty;
2094 l_update_rec.record_changed_flag_tab(i) := 'Y';
2095 IF (nvl(l_new_req_qty2,0) <> 0 ) THEN
2096 --{
2097 l_update_rec.requested_qty2_tab(i) := nvl(l_update_rec.requested_qty2_tab(i),0) +
2098 l_new_req_qty2;
2099
2100 IF (l_update_rec.requested_qty2_tab(i) < 0) THEN
2101 --{
2102 l_update_rec.requested_qty2_tab(i) := 0;
2103 --}
2104 END IF;
2105 --}
2106 END IF;
2107
2108 l_new_req_qty := 0; -- so that we can go out the loop as we already
2109 -- updated the remaining quantity on the lines.
2110 l_update_del_det_id := l_update_rec.del_det_id_tab(i);
2111 l_update_del_det_req_qty := l_update_rec.requested_qty_tab(i);
2112 l_update_del_det_req_qty2 := l_update_rec.requested_qty2_tab(i);
2113 --}
2114 ELSE -- l_update_rec.requested_qty_tab(i) <= abs(l_new_req_qty)
2115 --{
2116 l_new_req_qty := l_new_req_qty + l_update_rec.requested_qty_tab(i);
2117 l_update_rec.requested_qty_tab(i) := 0;
2118 l_update_rec.record_changed_flag_tab(i) := 'Y';
2119
2120 IF (l_update_rec.requested_qty2_tab(i) IS NOT NULL) THEN
2121 --{
2122 l_new_req_qty2 := l_new_req_qty2 + l_update_rec.requested_qty2_tab(i);
2123 l_update_rec.requested_qty2_tab(i) := 0;
2124 IF (l_new_req_qty2 > 0) THEN
2125 --{
2126 l_new_req_qty2 := 0;
2127 --}
2128 END IF;
2129 --}
2130 END IF;
2131
2132 --}
2133 END IF;
2134 IF l_debug_on THEN
2135 WSH_DEBUG_SV.log(l_module_name,'l_new_req_qty at the end of processing the current record ', l_new_req_qty);
2136 END IF;
2137
2138 i := l_update_rec.del_det_id_tab.NEXT(i);
2139 --}
2140 END LOOP;
2141 IF ( l_update_rec.del_det_id_tab.COUNT > 0 ) THEN
2142 --{
2143 --
2144 --
2145 FORALL i IN l_update_rec.del_det_id_tab.FIRST..l_update_rec.del_det_id_tab.LAST
2146 update wsh_delivery_details
2147 set requested_quantity = l_update_rec.requested_qty_tab(i),
2148 requested_quantity2 = l_update_rec.requested_qty2_tab(i),
2149 last_update_date = sysdate,
2150 last_updated_by = fnd_global.user_id,
2151 last_update_login = fnd_global.user_id
2152 where delivery_detail_id = l_update_rec.del_det_id_tab(i)
2153 and nvl(l_update_rec.record_changed_flag_tab(i), 'N') = 'Y';
2154 --
2155 --
2156 IF l_debug_on THEN
2157 WSH_DEBUG_SV.logmsg(l_module_name,'After the bulk update');
2158 WSH_DEBUG_SV.log(l_module_name,'Number of Records updated', SQL%ROWCOUNT);
2159 END IF;
2160 --
2161 -- DBI Project
2162 -- Update of wsh_delivery_details where requested_quantity/released_status
2163 -- are changed, call DBI API after the update.
2164 -- DBI API will check if DBI is installed
2165 IF l_debug_on THEN
2166 WSH_DEBUG_SV.log(l_module_name,'Calling DBI API.Detail Count-', l_update_rec.del_det_id_tab.count);
2167 END IF;
2168 WSH_INTEGRATION.DBI_Update_Detail_Log
2169 (p_delivery_detail_id_tab => l_update_rec.del_det_id_tab,
2170 p_dml_type => 'UPDATE',
2171 x_return_status => l_dbi_rs);
2172
2173 IF l_debug_on THEN
2174 WSH_DEBUG_SV.log(l_module_name,'Return Status after DBI Call-',l_dbi_rs);
2175 END IF;
2176 -- Only Handle Unexpected error
2177 IF l_dbi_rs = WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR THEN
2178 x_return_status := l_dbi_rs;
2179 --
2180 ROLLBACK TO PR_REM_REQ_QTY;
2181 IF l_debug_on THEN
2182 WSH_DEBUG_SV.pop(l_module_name);
2183 END IF;
2184 --
2185 RETURN;
2186 END IF;
2187 -- End of Code for DBI Project
2188 --
2189 --}
2190 END IF;
2191 --}
2192 END IF;
2193 --
2194 --
2195 --}
2196 END IF;
2197 --
2198 IF l_num_warnings > 0 THEN
2199 RAISE WSH_UTIL_CORE.G_EXC_WARNING;
2200 ELSE
2201 x_return_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
2202 END IF;
2203 --}
2204 --
2205 -- Debug Statements
2206 --
2207 IF l_debug_on THEN
2208 WSH_DEBUG_SV.pop(l_module_name);
2209 END IF;
2210 --
2211 EXCEPTION
2212 --{
2213 WHEN DD_LOCKED THEN
2214 ROLLBACK TO PR_REM_REQ_QTY;
2215 x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
2216 -- need to change the message
2217 FND_MESSAGE.SET_NAME('WSH','WSH_DELIVERY_LINES_LOCKED');
2218 wsh_util_core.add_message(x_return_status,l_module_name);
2219 IF l_debug_on THEN
2220 WSH_DEBUG_SV.logmsg(l_module_name,'RECORD_LOCKED exception has occured.',WSH_DEBUG_SV.C_EXCEP_LEVEL);
2221 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:FND_API.G_EXC_ERROR');
2222 END IF;
2223 --
2224 WHEN FND_API.G_EXC_ERROR THEN
2225 ROLLBACK TO PR_REM_REQ_QTY;
2226 x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
2227 --
2228 -- Debug Statements
2229 --
2230 IF l_debug_on THEN
2231 WSH_DEBUG_SV.logmsg(l_module_name,'FND_API.G_EXC_ERROR exception has occured.',WSH_DEBUG_SV.C_EXCEP_LEVEL);
2232 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:FND_API.G_EXC_ERROR');
2233 END IF;
2234 --
2235 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
2236 ROLLBACK TO PR_REM_REQ_QTY;
2237 x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR ;
2238 --
2239 -- Debug Statements
2240 --
2241 IF l_debug_on THEN
2242 WSH_DEBUG_SV.logmsg(l_module_name,'FND_API.G_EXC_UNEXPECTED_ERROR exception has occured.',WSH_DEBUG_SV.C_EXCEP_LEVEL);
2243 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:FND_API.G_EXC_UNEXPECTED_ERROR');
2244 END IF;
2245 --
2246 WHEN WSH_UTIL_CORE.G_EXC_WARNING THEN
2247 x_return_status := WSH_UTIL_CORE.G_RET_STS_WARNING;
2248 --
2249 -- Debug Statements
2250 --
2251 IF l_debug_on THEN
2252 WSH_DEBUG_SV.logmsg(l_module_name,'WSH_UTIL_CORE.G_EXC_WARNING exception has occured.',WSH_DEBUG_SV.C_EXCEP_LEVEL);
2253 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:WSH_UTIL_CORE.G_EXC_WARNING');
2254 END IF;
2255 --
2256 WHEN OTHERS THEN
2257 ROLLBACK TO PR_REM_REQ_QTY;
2258 x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR ;
2259 wsh_util_core.default_handler('WSH_IB_UI_RECON_GRP.PROCESS_REMAINING_REQ_QUANTITY');
2260 --}
2261 --
2262 -- Debug Statements
2263 --
2264 IF l_debug_on THEN
2265 WSH_DEBUG_SV.logmsg(l_module_name,'Unexpected error has occured. Oracle error message is '|| SQLERRM,WSH_DEBUG_SV.C_UNEXPEC_ERR_LEVEL);
2266 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:OTHERS');
2267 END IF;
2268 --
2269 END process_remaining_req_quantity;
2270
2271 END WSH_RCV_CORR_RTV_TXN_PKG;
[Home] [Help]