1 PACKAGE BODY WSH_IB_UI_RECON_GRP as
2 /* $Header: WSHURGPB.pls 120.3 2006/04/12 23:56:04 jnpinto noship $ */
3
4 --
5 G_PKG_NAME CONSTANT VARCHAR2(50) := 'WSH_IB_UI_RECON_GRP';
6 --
7 --===================
8 -- PROCEDURES
9 --===================
10
11 --========================================================================
12 -- PROCEDURE : Revert_Trips This procedure is called only from
13 -- the Inbound Reconciliation UI
14 -- when the user performs the revert
15 -- matching of a matched or
16 -- partially matched receipt.
17 --
18 -- PARAMETERS: p_shipment_header_id Shipment Header Id of the transaction
19 -- p_transaction_type transaction type (ASN or RECEIPT)
20 -- x_return_status return status of the API
21 --========================================================================
22 procedure revert_trips(
23 p_shipment_header_id IN NUMBER,
24 p_transaction_type IN NUMBER,
25 x_return_status OUT NOCOPY VARCHAR2)
26 IS
27 --{
28 cursor l_trips_csr(p_shpmt_header_id NUMBER) is
29 select distinct wt.trip_id
30 from wsh_trips wt,
31 wsh_trip_stops wts,
32 wsh_delivery_legs wdl,
33 wsh_new_deliveries wnd
34 where wnd.RCV_SHIPMENT_HEADER_ID = p_shpmt_header_id
35 and wnd.delivery_id = wdl.delivery_id
36 and wdl.pick_up_stop_id = wts.stop_id
37 and wts.trip_id = wt.trip_id;
38
39 l_trip_id_tab wsh_util_core.id_tab_type;
40 l_return_status VARCHAR2(1);
41 l_num_errors NUMBER := 0;
42 l_num_warnings NUMBER := 0;
43 i NUMBER;
44 --}
45 --
46 l_debug_on BOOLEAN;
47 --
48 l_module_name CONSTANT VARCHAR2(100) := 'wsh.plsql.' || G_PKG_NAME || '.' || 'REVERT_TRIPS';
49 --
50 BEGIN
51 --{
52 --
53 l_debug_on := WSH_DEBUG_INTERFACE.g_debug;
54 --
55 IF l_debug_on IS NULL
56 THEN
57 l_debug_on := WSH_DEBUG_SV.is_debug_enabled;
58 END IF;
59 --
60 --
61 -- Debug Statements
62 --
63 IF l_debug_on THEN
64 WSH_DEBUG_SV.push(l_module_name);
65 --
66 WSH_DEBUG_SV.log(l_module_name,'P_SHIPMENT_HEADER_ID',P_SHIPMENT_HEADER_ID);
67 WSH_DEBUG_SV.log(l_module_name,'P_TRANSACTION_TYPE',P_TRANSACTION_TYPE);
68 END IF;
69 --
70 SAVEPOINT REVERT_TRIPS_GRP;
71 x_return_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
72 open l_trips_csr(p_shipment_header_id);
73 fetch l_trips_csr bulk collect into l_trip_id_tab;
74 close l_trips_csr;
75
76 IF l_trip_id_tab.COUNT > 0 THEN
77 --{
78 FORALL i in l_trip_id_tab.FIRST..l_trip_id_tab.LAST
79 update wsh_trips
80 set status_code = 'IT'
81 where trip_id = l_trip_id_tab(i);
82 --}
83 END IF;
84 --}
85 --
86 -- Debug Statements
87 --
88 IF l_debug_on THEN
89 WSH_DEBUG_SV.pop(l_module_name);
90 END IF;
91 --
92 EXCEPTION
93 --{
94 WHEN FND_API.G_EXC_ERROR THEN
95 ROLLBACK TO REVERT_TRIPS_GRP;
96 x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
97 --
98 -- Debug Statements
99 --
100 IF l_debug_on THEN
101 WSH_DEBUG_SV.logmsg(l_module_name,'FND_API.G_EXC_ERROR exception has occured.',WSH_DEBUG_SV.C_EXCEP_LEVEL);
102 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:FND_API.G_EXC_ERROR');
103 END IF;
104 --
105 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
106 ROLLBACK TO REVERT_TRIPS_GRP;
107 x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR ;
108 --
109 -- Debug Statements
110 --
111 IF l_debug_on THEN
112 WSH_DEBUG_SV.logmsg(l_module_name,'FND_API.G_EXC_UNEXPECTED_ERROR exception has occured.',WSH_DEBUG_SV.C_EXCEP_LEVEL);
113 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:FND_API.G_EXC_UNEXPECTED_ERROR');
114 END IF;
115 --
116 WHEN WSH_UTIL_CORE.G_EXC_WARNING THEN
117 x_return_status := WSH_UTIL_CORE.G_RET_STS_WARNING;
118 --
119 -- Debug Statements
120 --
121 IF l_debug_on THEN
122 WSH_DEBUG_SV.logmsg(l_module_name,'WSH_UTIL_CORE.G_EXC_WARNING exception has occured.',WSH_DEBUG_SV.C_EXCEP_LEVEL);
123 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:WSH_UTIL_CORE.G_EXC_WARNING');
124 END IF;
125 --
126 WHEN OTHERS THEN
127 ROLLBACK TO REVERT_TRIPS_GRP;
128 x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR ;
129 wsh_util_core.default_handler('WSH_IB_UI_RECON_GRP.REVERT_TRIPS', l_module_name);
130 --}
131 --
132 -- Debug Statements
133 --
134 IF l_debug_on THEN
135 WSH_DEBUG_SV.logmsg(l_module_name,'Unexpected error has occured. Oracle error message is '|| SQLERRM,WSH_DEBUG_SV.C_UNEXPEC_ERR_LEVEL);
136 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:OTHERS');
137 END IF;
138 --
139 END revert_trips;
140 --
141 --========================================================================
142 -- PROCEDURE : Revert_Deliveries This procedure is called only from
143 -- the Inbound Reconciliation UI
144 -- when the user performs the revert
145 -- matching of a matched or
146 -- partially matched receipt.
147 --
148 -- PARAMETERS: p_delivery_id_tab Table of delivery Ids to be reverted
149 -- p_status_code_tab Table of Status Code values to updated
150 -- on the deliveries stored in
151 -- p_delivery_id_tab.
152 -- x_return_status Return status of the API
153
154 -- COMMENT : This procedure is called by the revert_matching which is called
155 -- only from the Inbound Reconciliation UI when the user performs
156 -- the revert matching of a matched or partially matched receipt.
157 -- Here we update the status of the delivery based on the input
158 -- table p_status_code_tab (because if the receipt is against ASN
159 -- then the status should be 'IT' and NOT 'OP' This decision is
160 -- made in revert_details).
161 -- Following are the logical steps in this API -
162 -- 1. Initially we update the status of all the deliveries based on
163 -- p_delivery_id_tab and p_status_code_tab and also
164 -- update rcv_shipment_header_id to NULL.
165 -- 2. Then we call Mark_Reprice_Required to set the reprice_flag
166 -- on the delivery legs. (This we need to do because the
167 -- received quantities on the lines will be nullified and
168 -- we need to re-rate the deliveries)
169 -- 3. We call WSH_TP_RELEASE.calculate_cont_del_tpdates
170 -- so that we recalculate the TP dates on all the
171 -- deliveries.
172 -- 4. Finally, we also call WSH_INBOUND_UTIL_PKG.setTripStopStatus
173 -- to set the statuses of the corresponding trips and stops
174 -- corresponding to the new status of the deliveries.
175 --========================================================================
176 PROCEDURE revert_deliveries(
177 p_delivery_id_tab IN wsh_util_core.id_tab_type,
178 p_status_code_tab IN wsh_util_core.column_tab_type,
179 x_return_status OUT NOCOPY VARCHAR2)
180 IS
181 --{
182 -- This cursor is not used anymore. Was added in the beginning of coding.
183 cursor l_parent_txn_sts_csr(p_shipment_header_id NUMBER) is
184 select 'Y'
185 from wsh_inbound_txn_history wth1,
186 wsh_inbound_txn_history wth2
187 where wth1.shipment_header_id = p_shipment_header_id
188 and wth1.transaction_type = 'ASN'
189 and wth2.parent_shipment_header_id = wth1.shipment_header_id;
190
191 l_parent_txn_exists VARCHAR2(1);
192 l_del_sts VARCHAR2(10);
193 l_return_status VARCHAR2(1);
194 l_num_errors NUMBER := 0;
195 l_num_warnings NUMBER := 0;
196 l_organization_id Number;
197 l_wf_rs VARCHAR2(1); --Pick To POD Wf Project
198
199 l_delivery_id_tab wsh_util_core.id_tab_type;
200
201 --}
202 --
203 l_debug_on BOOLEAN;
204 --
205 l_module_name CONSTANT VARCHAR2(100) := 'wsh.plsql.' || G_PKG_NAME || '.' || 'REVERT_DELIVERIES';
206 --
207 BEGIN
208 --{
209 --
210 l_debug_on := WSH_DEBUG_INTERFACE.g_debug;
211 --
212 IF l_debug_on IS NULL
213 THEN
214 l_debug_on := WSH_DEBUG_SV.is_debug_enabled;
215 END IF;
216 --
217 --
218 -- Debug Statements
219 --
220 IF l_debug_on THEN
221 WSH_DEBUG_SV.push(l_module_name);
222 --
223 --WSH_DEBUG_SV.log(l_module_name,'P_SHIPMENT_HEADER_ID',P_SHIPMENT_HEADER_ID);
224 --WSH_DEBUG_SV.log(l_module_name,'P_TRANSACTION_TYPE',P_TRANSACTION_TYPE);
225 END IF;
226 --
227 SAVEPOINT REVERT_DELIVERIES_GRP;
228 x_return_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
229 /*
230 open l_parent_txn_sts_csr(p_shipment_header_id);
231 fetch l_parent_txn_sts_csr into l_parent_txn_exists;
232 close l_parent_txn_sts_csr;
233 IF nvl(l_parent_txn_exists,'N') = 'Y' THEN
234 l_del_sts := 'IT';
235 ELSE
236 l_del_sts := 'OP';
237 END IF;
238
239
240 */
241
242 IF l_debug_on THEN
243 WSH_DEBUG_SV.log(l_module_name,'l_delivery_id_tab count',l_delivery_id_tab.count);
244 END IF;
245
246 IF (p_delivery_id_tab.count > 0 ) THEN
247 --{
248 FOR i in p_delivery_id_tab.FIRST..p_delivery_id_tab.LAST LOOP
249 update wsh_new_deliveries
250 set rcv_shipment_header_id = NULL,
251 status_code = p_status_code_tab(i)
252 where delivery_id = p_delivery_id_tab(i)
253 RETURNING organization_id into l_organization_id ; --Added for Pick To POD WF
254 /* CURRENTLY NOT IN USE
255 --Raise Event: Pick To Pod Workflow
256 WSH_WF_STD.Raise_Event(
257 p_entity_type => 'DELIVERY',
258 p_entity_id => p_delivery_id_tab(i),
259 p_event => 'oracle.apps.fte.delivery.ib.receiptreverted' ,
260 p_organization_id => l_organization_id,
261 x_return_status => l_wf_rs ) ;
262 --Error Handling to be done in WSH_WF_STD.Raise_Event itself
263 IF l_debug_on THEN
264 WSH_DEBUG_SV.log(l_module_name,'Current Time is ',SYSDATE);
265 WSH_DEBUG_SV.log(l_module_name,'Delivery ID is ', p_delivery_id_tab(i));
266 WSH_DEBUG_SV.log(l_module_name,'Return Status After Calling WSH_WF_STD.Raise_Event',l_wf_rs);
267 END IF;
268 --Done Raise Event: Pick To Pod Workflow
269 */
270 END LOOP;
271
272 -- Commented the below code because we are calling detail_weight_volume
273 -- and that in turn would calculate the weight and volume of the
274 -- delivery ( and trip and trip stops if present).
275 /*
276 WSH_WV_UTILS.Delivery_Weight_Volume(
277 p_del_rows => p_delivery_id_tab,
278 p_update_flag => 'Y',
279 p_calc_wv_if_frozen => 'N',
280 x_return_status => l_return_status);
281 IF l_debug_on THEN
282 WSH_DEBUG_SV.log(l_module_name,'Return STatus after calling Delivery_Weight_Volume',l_return_status);
283 END IF;
284
285 wsh_util_core.api_post_call(
286 p_return_status => l_return_status,
287 x_num_warnings => l_num_warnings,
288 x_num_errors => l_num_errors);
289 */
290
291 WSH_DELIVERY_LEGS_ACTIONS.Mark_Reprice_Required(
292 p_entity_type => 'DELIVERY',
293 p_entity_ids => p_delivery_id_tab,
294 x_return_status => l_return_status);
295 --
296 IF l_debug_on THEN
297 WSH_DEBUG_SV.log(l_module_name,'Return STatus after calling Mark_Reprice_Required',l_return_status);
298 END IF;
299 --
300 wsh_util_core.api_post_call(
301 p_return_status => l_return_status,
302 x_num_warnings => l_num_warnings,
303 x_num_errors => l_num_errors);
304 --
305 WSH_TP_RELEASE.calculate_cont_del_tpdates(
306 p_entity => 'DLVY',
307 p_entity_ids => p_delivery_id_tab,
308 x_return_status => l_return_status);
309 --
310 IF l_debug_on THEN
311 WSH_DEBUG_SV.log(l_module_name,'Return Status after calling calculate_cont_del_tpdates',l_return_status);
312 END IF;
313 --
314 wsh_util_core.api_post_call(
315 p_return_status => l_return_status,
316 x_num_warnings => l_num_warnings,
317 x_num_errors => l_num_errors);
318 --
319 WSH_INBOUND_UTIL_PKG.setTripStopStatus(
320 p_transaction_code => 'RECEIPT',
321 p_action_code => 'CANCEL',
322 p_delivery_id_tab => p_delivery_id_tab,
323 x_return_status => l_return_status);
324
325 IF l_debug_on THEN
326 WSH_DEBUG_SV.log(l_module_name,'Return STatus after calling setTripStopStatus',l_return_status);
327 END IF;
328 wsh_util_core.api_post_call(
329 p_return_status => l_return_status,
330 x_num_warnings => l_num_warnings,
331 x_num_errors => l_num_errors);
332 --
333 --}
334 END IF;
335
336 IF l_num_errors > 0 THEN
337 RAISE FND_API.G_EXC_ERROR;
338 ELSIF l_num_warnings > 0 THEN
339 RAISE WSH_UTIL_CORE.G_EXC_WARNING;
340 ELSE
341 x_return_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
342 END IF;
343 --}
344 --
345 -- Debug Statements
346 --
347 IF l_debug_on THEN
348 WSH_DEBUG_SV.pop(l_module_name);
349 END IF;
350 --
351 EXCEPTION
352 --{
353 WHEN FND_API.G_EXC_ERROR THEN
354 ROLLBACK TO REVERT_DELIVERIES_GRP;
355 x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
356 --
357 -- Debug Statements
358 --
359 IF l_debug_on THEN
360 WSH_DEBUG_SV.logmsg(l_module_name,'FND_API.G_EXC_ERROR exception has occured.',WSH_DEBUG_SV.C_EXCEP_LEVEL);
361 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:FND_API.G_EXC_ERROR');
362 END IF;
363 --
364 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
365 ROLLBACK TO REVERT_DELIVERIES_GRP;
366 x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR ;
367 --
368 -- Debug Statements
369 --
370 IF l_debug_on THEN
371 WSH_DEBUG_SV.logmsg(l_module_name,'FND_API.G_EXC_UNEXPECTED_ERROR exception has occured.',WSH_DEBUG_SV.C_EXCEP_LEVEL);
372 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:FND_API.G_EXC_UNEXPECTED_ERROR');
373 END IF;
374 --
375 WHEN WSH_UTIL_CORE.G_EXC_WARNING THEN
376 x_return_status := WSH_UTIL_CORE.G_RET_STS_WARNING;
377 --
378 -- Debug Statements
379 --
380 IF l_debug_on THEN
381 WSH_DEBUG_SV.logmsg(l_module_name,'WSH_UTIL_CORE.G_EXC_WARNING exception has occured.',WSH_DEBUG_SV.C_EXCEP_LEVEL);
382 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:WSH_UTIL_CORE.G_EXC_WARNING');
383 END IF;
384 --
385 WHEN OTHERS THEN
386 ROLLBACK TO REVERT_DELIVERIES_GRP;
387 x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR ;
388 wsh_util_core.default_handler('WSH_IB_UI_RECON_GRP.REVERT_DELIVERIES', l_module_name);
389 --}
390 --
391 -- Debug Statements
392 --
393 IF l_debug_on THEN
394 WSH_DEBUG_SV.logmsg(l_module_name,'Unexpected error has occured. Oracle error message is '|| SQLERRM,WSH_DEBUG_SV.C_UNEXPEC_ERR_LEVEL);
395 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:OTHERS');
396 END IF;
397 --
398 END revert_deliveries;
399 --
400 --========================================================================
401 -- PROCEDURE : Revert_Details This procedure is called only from
402 -- the Inbound Reconciliation UI
403 -- when the user performs the revert
404 -- matching of a matched or
405 -- partially matched receipt.
406 --
407 -- PARAMETERS: p_shipment_header_id Shipment Header Id of the transaction
408 -- p_transaction_type transaction type (ASN or RECEIPT)
409 -- x_return_status return status of the API
410 -- COMMENT : This procedure is called by the revert_matching which is called
411 -- only from the Inbound Reconciliation UI when the user performs
412 -- the revert matching of a matched or partially matched receipt.
413 -- Here we update the released status, returned_quantity,
414 -- received_quantity of the delivery details based on the input
415 -- p_shipment_header_id (Secondary quantities are also taken care).
416 -- The following are the logic flow steps in this API -
417 -- 1. Initially we get all the delivery details associated to
418 -- receipt using the shipment_header_id into PL/SQL tables.
419 -- 2. Then, in a loop for each distinct po_line_location_id, we compare the
420 -- total returned quantity of the closed lines with the total
421 -- total requested quantity of the either open lines or lines
422 -- that are closed but have NULL received quantity.
423 -- (This is needed because, when we are reverting the
424 -- delivery details associated a receipt, we need to reduce
425 -- requested quantity on open delivery details by a quantity
426 -- equal the total returned quantity on the closed delivery details).
427 -- > If the total returned quantity of the closed details
428 -- > is equal to the total requested quantity of the
429 -- > open delivery details, then we delete all the open
430 -- > delivery details.
431 --
432 -- > If the total returned quantity of the closed details
433 -- > is greater than the total requested quantity of the
434 -- > open delivery details, then we raise an exception
435 -- > as reverting cannot be completed successfully.
436 --
437 -- > If the total returned quantity of the closed details
438 -- > is less than the total requested quantity of the
439 -- > open delivery details, then we call the API
440 -- > WSH_RCV_CORR_RTV_TXN_PKG.process_remaining_req_quantity
441 -- > with the action_code 'REVERT_MATCH'
442 -- > and passing a negative value of the total returned quantity
443 -- > for that po_line_location_id.
444 --
445 -- > While looping through we also assign the appropriate values
446 -- to the respective out parameters.
447 -- 3. After the loop, since we are nullifying the received quantities
448 -- on all these delivery details, we need to re-calculate the wt-vol
449 -- of the delivery details, therefore, we call the API
450 -- WSH_WV_UTILS.Detail_Weight_Volume.
451 -- 4. At the end, we loop through the delivery ids in l_delivery_id_tab to
452 -- to remove the duplicate delivery ids. Please refer to that
453 -- part of the code for more detailed comments.
454 --========================================================================
455 procedure revert_details(
456 p_shipment_header_id IN NUMBER,
457 p_transaction_type IN VARCHAR2,
458 x_dd_list OUT NOCOPY WSH_PO_CMG_PVT.dd_list_type,
459 x_delivery_id_tab OUT NOCOPY WSH_UTIL_CORE.id_tab_type,
460 x_status_code_tab OUT NOCOPY WSH_UTIL_CORE.column_tab_type,
461 x_unassign_det_id_tab OUT NOCOPY WSH_UTIL_CORE.id_tab_type,
462 x_msg_count OUT NOCOPY NUMBER,
463 x_msg_data OUT NOCOPY NUMBER,
464 x_return_status OUT NOCOPY VARCHAR2)
465 IS
466 --{
467
468 -- This is not used anymore. Was added in the beginning.
469 cursor l_parent_txn_sts_csr(p_shipment_header_id NUMBER) is
470 select 'Y'
471 from wsh_inbound_txn_history wth1,
472 wsh_inbound_txn_history wth2
473 where wth1.shipment_header_id = p_shipment_header_id
474 and wth1.transaction_type = 'ASN'
475 and wth2.parent_shipment_header_id = wth1.shipment_header_id;
476
477 -- This is used to obtain all the delivery details based on the
478 -- rcv_shipment_header_id from wsh_new_deliveries.
479 -- These will be the delivery details that we need to revert.
480 cursor l_del_details_csr(p_shipment_header_id NUMBER) is
481 select wdd.source_line_id,
482 wdd.po_shipment_line_id,
483 wdd.delivery_detail_id,
484 wdd.ship_from_location_id,
485 wdd.rcv_shipment_line_id,
486 wdd.released_status,
487 wnd.delivery_id,
488 wnd.asn_shipment_header_id,
489 wnd.rcv_shipment_header_id,
490 wnd.status_code,
491 wdd.picked_quantity,
492 wdd.shipped_quantity
493 from wsh_delivery_details wdd,
494 wsh_delivery_assignments_v wda,
495 wsh_new_deliveries wnd
496 where wnd.rcv_shipment_header_id = p_shipment_header_id
497 and wnd.delivery_id = wda.delivery_id
498 and wda.delivery_detail_id = wdd.delivery_detail_id
499 and wdd.source_code = 'PO'
500 order by wdd.source_line_id, wdd.po_shipment_line_id;
501
502 -- This cursor is used to obtain the total returned quantity
503 -- on the matched delivery details for a po_line_location_id.
504 cursor l_tot_ret_qty_csr(p_source_line_id NUMBER,
505 p_po_line_loc_id NUMBER) is
506 select sum(returned_quantity),
507 sum(returned_quantity2)
508 from wsh_delivery_details wdd
509 where source_line_id = p_source_line_id
510 and po_shipment_line_id = p_po_line_loc_id
511 and released_status = 'L'
512 and source_code = 'PO';
513
514 -- This cursor is used to obtain the total requested quantity
515 -- on all the open/closed delivery details which were NOT
516 -- matched against the receipt being reverted for a po_line_location_id.
517 cursor l_tot_req_qty_csr(p_source_line_id NUMBER,
518 p_po_line_loc_id NUMBER) is
519 select sum(requested_quantity), sum(requested_quantity2)
520 from wsh_delivery_details wdd
521 where source_line_id = p_source_line_id
522 and po_shipment_line_id = p_po_line_loc_id
523 and ( released_status = 'X'
524 or
525 (released_status = 'L' and received_quantity is null)
526 )
527 and source_code = 'PO'
528 and routing_req_id is null;
529
530 -- This is not used anymore. Was added in the beginning.
531 cursor l_packed_del_lines_csr(p_source_line_id NUMBER,
532 p_po_line_loc_id NUMBER) is
533 select wdd.delivery_detail_id
534 from wsh_delivery_details wdd,
535 wsh_delivery_assignments_v wda
536 where wdd.source_line_id = p_source_line_id
537 and wdd.container_flag = 'N'
538 and wdd.source_code = 'PO'
539 and wda.delivery_detail_id = wdd.delivery_detail_id
540 and wda.parent_delivery_detail_id is not null;
541
542 -- This is not used anymore. Was added in the beginning.
543 cursor l_del_det_id_csr(p_shipment_header_id NUMBER) is
544 select wdd.po_shipment_line_id,
545 wdd.delivery_detail_id
546 from wsh_delivery_details wdd,
547 wsh_delivery_assignments_v wda,
548 wsh_new_deliveries wnd
549 where wnd.rcv_shipment_header_id = p_shipment_header_id
550 and wnd.delivery_id = wda.delivery_id
551 and wda.delivery_detail_id = wdd.delivery_detail_id
552 and wdd.source_code = 'PO';
553
554 -- This cursor checks whether atleast one record in wsh_delivery_details
555 -- is already purged. If yes, we do not allow reverting of the transaction.
556 cursor l_purge_details_csr (p_source_line_id IN NUMBER,
557 p_po_shipment_line_id IN NUMBER) is
558 select 'Y'
559 from wsh_delivery_details
560 where source_line_id = p_source_line_id
561 and po_shipment_line_id = p_po_shipment_line_id
562 and source_code = 'PO'
563 and released_status = 'P'
564 and rownum = 1;
565
566 l_purged_details_flag VARCHAR2(1);
567 l_rel_sts VARCHAR2(1);
568 l_parent_txn_exists VARCHAR2(1);
569 l_shipment_line_id NUMBER;
570 l_source_line_id_tab wsh_util_core.id_tab_type;
571 l_po_line_loc_id_tab wsh_util_core.id_tab_type;
572 l_del_det_id_tab wsh_util_core.id_tab_type;
573 l_ship_from_loc_id_tab wsh_util_core.id_tab_type;
574 l_rcv_shipment_line_id_tab wsh_util_core.id_tab_type;
575 l_released_status_tab wsh_util_core.column_tab_type;
576 l_asn_shipment_header_id_tab wsh_util_core.id_tab_type;
577 l_rcv_shipment_header_id_tab wsh_util_core.id_tab_type;
578 l_packed_details_tab wsh_util_core.id_tab_type;
579 l_picked_quantity_tab wsh_util_core.id_tab_type;
580 l_shipped_quantity_tab wsh_util_core.id_tab_type;
581 l_sum_returned_quantity NUMBER;
582 l_sum_returned_quantity2 NUMBER;
583 l_sum_req_qty NUMBER;
584 l_sum_req_qty2 NUMBER;
585 l_accept_rcv_lpn_flag VARCHAR2(32767);
586 l_prev_po_line_loc_id NUMBER := -9999;
587 l_unassign_det_id_tab wsh_util_core.id_tab_type;
588 l_delivery_id_tab wsh_util_core.id_tab_type;
589 l_status_code_tab wsh_util_core.column_tab_type;
590
591 l_rcv_rtv_rec WSH_RCV_CORR_RTV_TXN_PKG.rem_req_qty_rec_type;
592 l_det_action_prms WSH_GLBL_VAR_STRCT_GRP.dd_action_parameters_rec_type;
593 l_det_action_out_rec WSH_GLBL_VAR_STRCT_GRP.dd_action_out_rec_type;
594 l_line_rec OE_WSH_BULK_GRP.Line_rec_type;
595
596 l_return_status VARCHAR2(1);
597 i NUMBER;
598 l_del_index NUMBER;
599 l_num_errors NUMBER := 0;
600 l_num_warnings NUMBER := 0;
601 l_msg_count NUMBER;
602 l_msg_data VARCHAR2(32767);
603
604 l_pr_rem_in_rec WSH_RCV_CORR_RTV_TXN_PKG.action_in_rec_type;
605
606 l_del_cache_tbl wsh_util_core.key_value_tab_type;
607 l_del_ext_cache_tbl wsh_util_core.key_value_tab_type;
608
609 l_detail_tab WSH_UTIL_CORE.id_tab_type; -- DBI Project
610 l_dbi_rs VARCHAR2(1); -- DBI Project
611
612 l_cnt_det_id_tab wsh_util_core.id_tab_type;
613
614 cursor l_delete_det_csr (p_source_line_id IN NUMBER,
615 p_po_shpmt_line_id IN NUMBER) is
616 select delivery_detail_id
617 from wsh_delivery_details
618 where source_line_id = p_source_line_id
619 and po_shipment_line_id = p_po_shpmt_line_id
620 and ( released_status = 'X'
621 or
622 (released_status = 'L' and received_quantity is null)
623 )
624 and routing_req_id is null
625 for update of delivery_detail_id nowait;
626
627 l_delete_det_tbl wsh_util_core.id_tab_type;
628
629 DD_LOCKED exception;
630 PRAGMA EXCEPTION_INIT(DD_LOCKED, -54);
631 --}
632 --
633 l_debug_on BOOLEAN;
634 --
635 l_module_name CONSTANT VARCHAR2(100) := 'wsh.plsql.' || G_PKG_NAME || '.' || 'REVERT_DETAILS';
636 --
637 BEGIN
638 --{
639 --
640 l_debug_on := WSH_DEBUG_INTERFACE.g_debug;
641 --
642 IF l_debug_on IS NULL
643 THEN
644 l_debug_on := WSH_DEBUG_SV.is_debug_enabled;
645 END IF;
646 --
647 --
648 -- Debug Statements
649 --
650 IF l_debug_on THEN
651 WSH_DEBUG_SV.push(l_module_name);
652 --
653 WSH_DEBUG_SV.log(l_module_name,'P_SHIPMENT_HEADER_ID',P_SHIPMENT_HEADER_ID);
654 WSH_DEBUG_SV.log(l_module_name,'P_TRANSACTION_TYPE',P_TRANSACTION_TYPE);
655 END IF;
656 --
657 SAVEPOINT REVERT_DETAILS_GRP;
658 x_return_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
659 /*
660 open l_parent_txn_sts_csr(p_shipment_header_id);
661 fetch l_parent_txn_sts_csr into l_parent_txn_exists;
662 close l_parent_txn_sts_csr;
663 IF nvl(l_parent_txn_exists,'N') = 'Y' THEN
664 --{
665 l_rel_sts := 'C';
666 --}
667 ELSE
668 --{
669 l_rel_sts := 'X';
670 l_shipment_line_id := NULL;
671 --}
672 END IF;
673 IF l_debug_on THEN
674 WSH_DEBUG_SV.log(l_module_name,'l_rel_sts',l_rel_sts);
675 END IF;
676 */
677 open l_del_details_csr(p_shipment_header_id);
678 fetch l_del_details_csr bulk collect into l_source_line_id_tab,
679 l_po_line_loc_id_tab,
680 l_del_det_id_tab,
681 l_ship_from_loc_id_tab,
682 l_rcv_shipment_line_id_tab,
683 l_released_status_tab,
684 l_delivery_id_tab,
685 l_asn_shipment_header_id_tab,
686 l_rcv_shipment_header_id_tab,
687 l_status_code_tab,
688 l_picked_quantity_tab,
689 l_shipped_quantity_tab;
690 close l_del_details_csr;
691 IF l_debug_on THEN
692 WSH_DEBUG_SV.logmsg(l_module_name,'After fetching the cursor');
693 WSH_DEBUG_SV.log(l_module_name,'l_po_line_loc_id_tab count is', l_po_line_loc_id_tab.count);
694 END IF;
695 IF l_po_line_loc_id_tab.count > 0 THEN
696 --{
697 i := l_po_line_loc_id_tab.first;
698 while i IS NOT NULL LOOP
699 --{
700 IF (l_po_line_loc_id_tab(i) <> l_prev_po_line_loc_id) THEN
701 --{
702 open l_purge_details_csr(l_source_line_id_tab(i), l_po_line_loc_id_tab(i));
703 fetch l_purge_details_csr into l_purged_details_flag;
704 close l_purge_details_csr;
705 IF (nvl(l_purged_details_flag, 'N') = 'Y') THEN
706 --{
707 FND_MESSAGE.SET_NAME('WSH','WSH_IB_DETAILS_PURGED');
708 x_return_status := wsh_util_core.g_ret_sts_error;
709 wsh_util_core.add_message(x_return_status, l_module_name);
710 RAISE FND_API.G_EXC_ERROR;
711 --}
712 END IF;
713 l_sum_returned_quantity := 0;
714 l_sum_returned_quantity2 := 0;
715 l_sum_req_qty := 0;
716 IF l_debug_on THEN
717 WSH_DEBUG_SV.logmsg(l_module_name,'After initializing the quantities');
718 END IF;
719 open l_tot_ret_qty_csr(l_source_line_id_tab(i),
720 l_po_line_loc_id_tab(i));
721 fetch l_tot_ret_qty_csr into l_sum_returned_quantity,
722 l_sum_returned_quantity2;
723 close l_tot_ret_qty_csr;
724 IF l_debug_on THEN
725 WSH_DEBUG_SV.log(l_module_name,'l_sum_returned_quantity',l_sum_returned_quantity);
726 WSH_DEBUG_SV.log(l_module_name,'l_sum_returned_quantity2',l_sum_returned_quantity2);
727 END IF;
728 IF (nvl(l_sum_returned_quantity,0) > 0 ) THEN
729 --{
730 open l_tot_req_qty_csr(l_source_line_id_tab(i),
731 l_po_line_loc_id_tab(i));
732 fetch l_tot_req_qty_csr into l_sum_req_qty, l_sum_req_qty2;
733 close l_tot_req_qty_csr;
734 IF l_debug_on THEN
735 WSH_DEBUG_SV.log(l_module_name,'l_sum_req_qty',l_sum_req_qty);
736 WSH_DEBUG_SV.log(l_module_name,'l_sum_req_qty2',l_sum_req_qty2);
737 END IF;
738 IF nvl (l_sum_req_qty, 0) < l_sum_returned_quantity THEN
739 --{
740 FND_MESSAGE.SET_NAME('WSH','WSH_IB_INSUFF_LINES_REVERT');
741 x_return_status := wsh_util_core.g_ret_sts_error;
742 wsh_util_core.add_message(x_return_status, l_module_name);
743 RAISE FND_API.G_EXC_ERROR;
744 --}
745 ELSIF l_sum_req_qty = l_sum_returned_quantity THEN
746 --{
747 -- This cursor is for locking all the delivery details for
748 -- corresponding po_line_location_id that we are going
749 -- to delete.
750 open l_delete_det_csr(l_source_line_id_tab(i),
751 l_po_line_loc_id_tab(i));
752 fetch l_delete_det_csr bulk collect into l_delete_det_tbl;
753 close l_delete_det_csr;
754 IF (l_delete_det_tbl.count > 0) THEN
755 --{
756 -- We can delete all the open lines for the po line location.
757 -- first deleting the assignments
758 FORALL i IN l_delete_det_tbl.FIRST..l_delete_det_tbl.LAST
759 DELETE wsh_delivery_assignments_v
760 WHERE delivery_detail_id = l_delete_det_tbl(i);
761
762 IF l_debug_on THEN
763 WSH_DEBUG_SV.log(l_module_name,'SQL%ROWCOUNT After deleting LPNs from WDA',SQL%ROWCOUNT);
764 END IF;
765
766 --Deleting the rows in WSH_DELIVERY_DETAILS corresponding to the selected LPNs.
767 FORALL i IN l_delete_det_tbl.FIRST..l_delete_det_tbl.LAST
768 DELETE WSH_DELIVERY_DETAILS
769 WHERE delivery_detail_id = l_delete_det_tbl(i);
770
771 IF l_debug_on THEN
772 WSH_DEBUG_SV.log(l_module_name,'SQL%ROWCOUNT After deleting LPNs from WDD',SQL%ROWCOUNT);
773 END IF;
774 --}
775 END IF;
776 l_delete_det_tbl.delete;
777 /*
778 delete from wsh_delivery_assignments_v
779 where delivery_detail_id in
780 (select delivery_detail_id
781 from wsh_delivery_details
782 where source_line_id = l_source_line_id_tab(i)
783 and po_shipment_line_id = l_po_line_loc_id_tab(i)
784 and ( released_status = 'X'
785 or
786 (released_status = 'L' and received_quantity is null)
787 )
788 and routing_req_id is null);
789
790 delete from wsh_delivery_details
791 where source_line_id = l_source_line_id_tab(i)
792 and po_shipment_line_id = l_po_line_loc_id_tab(i)
793 and ( released_status = 'X'
794 or
795 (released_status = 'L' and received_quantity is null)
796 )
797 and routing_req_id is null;
798 */
799 --}
800 ELSIF l_sum_req_qty > l_sum_returned_quantity THEN
801 --{
802 l_rcv_rtv_rec.po_line_id := l_source_line_id_tab(i);
803 l_rcv_rtv_rec.po_line_location_id := l_po_line_loc_id_tab(i);
804 l_rcv_rtv_rec.requested_quantity := -l_sum_returned_quantity;
805 IF (nvl(l_sum_returned_quantity2,0) > 0 ) THEN
806 l_rcv_rtv_rec.requested_quantity2 := -l_sum_returned_quantity2;
807 END IF;
808 --
809 -- Debug Statements
810 --
811 IF l_debug_on THEN
812 WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_RCV_CORR_RTV_TXN_PKG.PROCESS_REMAINING_REQ_QUANTITY',WSH_DEBUG_SV.C_PROC_LEVEL);
813 END IF;
814 --
815 l_pr_rem_in_rec.action_code := 'REVERT_MATCH';
816 WSH_RCV_CORR_RTV_TXN_PKG.process_remaining_req_quantity (
817 p_rem_req_qty_rec => l_rcv_rtv_rec,
818 p_in_rec => l_pr_rem_in_rec,
819 x_return_status => l_return_status);
820 --
821 -- Debug Statements
822 --
823 IF l_debug_on THEN
824 WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_UTIL_CORE.API_POST_CALL',WSH_DEBUG_SV.C_PROC_LEVEL);
825 END IF;
826 --
827 wsh_util_core.api_post_call(
828 p_return_status => l_return_status,
829 x_num_warnings => l_num_warnings,
830 x_num_errors => l_num_errors);
831 --}
832 END IF;
833 --}
834 END IF;
835
836 -- This update statement is added so that we can re-open the delivery
837 -- details that are closed just because PO may be closed. But we need to
838 -- re-open them as we do not know which delivery details will be used
839 -- when matching the receipt once again.
840 update wsh_delivery_details
841 set released_status = 'X'
842 where released_status = 'L'
843 and source_code = 'PO'
844 and po_shipment_line_id = l_po_line_loc_id_tab(i)
845 and source_line_id = l_source_line_id_tab(i)
846 and ship_from_location_id = -1
847 and rcv_shipment_line_id is null
848 RETURNING delivery_detail_id BULK COLLECT INTO l_detail_tab;
849
850 IF l_debug_on THEN
851 WSH_DEBUG_SV.log(l_module_name,'SQL%ROWCOUNT After updating wdds',SQL%ROWCOUNT);
852 END IF;
853
854 --
855 -- DBI Project
856 -- Update of wsh_delivery_details where released_status
857 -- are changed, call DBI API after the update.
858 -- This API will also check for DBI Installed or not
859 IF l_debug_on THEN
860 WSH_DEBUG_SV.log(l_module_name,'Calling DBI API. delivery details l_detail_tab count :',l_detail_tab.COUNT);
861 END IF;
862 WSH_INTEGRATION.DBI_Update_Detail_Log
863 (p_delivery_detail_id_tab => l_detail_tab,
864 p_dml_type => 'UPDATE',
865 x_return_status => l_dbi_rs);
866
867 IF l_debug_on THEN
868 WSH_DEBUG_SV.log(l_module_name,'Return Status after DBI Call-',l_dbi_rs);
869 END IF;
870 IF l_dbi_rs = WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR THEN
871 x_return_status := l_dbi_rs;
872 Rollback to REVERT_DETAILS_GRP;
873 -- just pass this return status to caller API
874 IF l_debug_on THEN
875 WSH_DEBUG_SV.logmsg(l_module_name,'DBI API Returned Unexpected error '||x_return_status);
876 WSH_DEBUG_SV.pop(l_module_name);
877 END IF;
878 return;
879 END IF;
880 -- End of Code for DBI Project
881 --
882
883 --}
884 END IF;
885 IF (l_asn_shipment_header_id_tab(i) IS NULL) OR
886 (l_asn_shipment_header_id_tab(i) <> nvl(l_rcv_shipment_header_id_tab(i),-9999)) THEN
887 --{
888 l_rcv_shipment_line_id_tab(i) := NULL;
889 --}
890 END IF;
891 -- else we need to leave the rcv shipment line id as it is.
892 IF (l_asn_shipment_header_id_tab(i) IS NULL) THEN
893 --{
894 l_released_status_tab(i) := 'X';
895
896 -- x_dd_list needs to be populated to finally call Update_Attributes
897 -- because if the delivery details were not matched against ASN, then we
898 -- need to reopen them and update them with the latest attributes of PO.
899 x_dd_list.po_shipment_line_id.extend;
900 x_dd_list.delivery_detail_id.extend;
901 x_dd_list.po_shipment_line_id(x_dd_list.po_shipment_line_id.count) := l_po_line_loc_id_tab(i);
902 x_dd_list.delivery_detail_id(x_dd_list.delivery_detail_id.count) := l_del_det_id_tab(i);
903 l_status_code_tab(i) := 'OP';
904 --}
905 ELSE
906 --{
907 l_status_code_tab(i) := 'IT';
908 l_released_status_tab(i) := 'C';
909 --}
910 END IF;
911 -- This is for unassigning all the delivery details
912 -- that have ship from location id as -1 and
913 -- are still assigned to the deliveries
914 IF (
915 ( nvl(l_ship_from_loc_id_tab(i), -1) = -1
916 and nvl(l_shipped_quantity_tab(i),0) = 0
917 )
918 OR
919 (
920 nvl(l_shipped_quantity_tab(i),nvl(l_picked_quantity_tab(i),0)) = 0
921 )
922 )
923 THEN
924 x_unassign_det_id_tab(x_unassign_det_id_tab.count + 1) := l_del_det_id_tab(i);
925 END IF;
926 l_prev_po_line_loc_id := l_po_line_loc_id_tab(i);
927 i := l_po_line_loc_id_tab.next(i);
928 --}
929 END LOOP;
930
931 IF l_debug_on THEN
932 WSH_DEBUG_SV.logmsg(l_module_name,'Before doing the update');
933 END IF;
934 FORALL i IN l_del_det_id_tab.FIRST..l_del_det_id_tab.LAST
935 update wsh_delivery_details
936 set returned_quantity = NULL,
937 returned_quantity2 = NULL,
938 received_quantity =NULL,
939 received_quantity2 = NULL,
940 released_status = l_released_status_tab(i),
941 rcv_shipment_line_id = l_rcv_shipment_line_id_tab(i),
942 last_update_date = sysdate,
943 last_updated_by = fnd_global.user_id,
944 last_update_login = fnd_global.user_id
945 where delivery_detail_id = l_del_det_id_tab(i)
946 and source_code = 'PO';
947
948 IF l_debug_on THEN
949 WSH_DEBUG_SV.logmsg(l_module_name,'After doing the update');
950 END IF;
951
952 IF l_debug_on THEN
953 WSH_DEBUG_SV.logmsg(l_module_name,'After doing the update');
954 WSH_DEBUG_SV.log(l_module_name,'l_num_errors', l_num_errors);
955 WSH_DEBUG_SV.log(l_module_name,'l_num_warnings', l_num_warnings);
956 END IF;
957 --
958 -- DBI Project
959 -- Update of wsh_delivery_details where released_status
960 -- are changed, call DBI API after the update.
961 -- This API will also check for DBI Installed or not
962 IF l_debug_on THEN
963 WSH_DEBUG_SV.log(l_module_name,'Calling DBI API. delivery details l_del_det_id_tab count :',l_del_det_id_tab.COUNT);
964 END IF;
965 WSH_INTEGRATION.DBI_Update_Detail_Log
966 (p_delivery_detail_id_tab => l_del_det_id_tab,
967 p_dml_type => 'UPDATE',
968 x_return_status => l_dbi_rs);
969
970 IF l_debug_on THEN
971 WSH_DEBUG_SV.log(l_module_name,'Return Status after DBI Call-',l_dbi_rs);
972 END IF;
973 IF l_dbi_rs = WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR THEN
974 x_return_status := l_dbi_rs ;
975 Rollback to REVERT_DETAILS_GRP;
976 -- just pass this return status to caller API
977 IF l_debug_on THEN
978 WSH_DEBUG_SV.logmsg(l_module_name,'DBI API Returned Unexpected error '||x_return_status);
979 WSH_DEBUG_SV.pop(l_module_name);
980 END IF;
981 return;
982 END IF;
983 -- End of Code for DBI Project
984 --
985 -- Updating the released_status for the lpns if any are present
986 --
987 update wsh_delivery_details
988 set returned_quantity = NULL,
989 returned_quantity2 = NULL,
990 received_quantity =NULL,
991 received_quantity2 = NULL,
992 released_status = 'C',
993 last_update_date = sysdate,
994 last_updated_by = fnd_global.user_id,
995 last_update_login = fnd_global.user_id
996 where delivery_detail_id in (select wda.delivery_detail_id
997 from wsh_delivery_assignments_v wda,
998 wsh_new_deliveries wnd
999 where wnd.rcv_shipment_header_id = p_shipment_header_id
1000 and wda.delivery_id = wnd.delivery_id)
1001 and container_flag = 'Y'
1002 returning delivery_detail_id
1003 bulk collect into l_cnt_det_id_tab;
1004 --
1005 IF l_debug_on THEN
1006 WSH_DEBUG_SV.log(l_module_name,'l_cnt_det_id_tab count', l_cnt_det_id_tab.count);
1007 END IF;
1008 --
1009 IF (l_cnt_det_id_tab.count > 0 ) THEN
1010 --{
1011 --
1012 -- DBI Project
1013 -- Update of wsh_delivery_details where released_status
1014 -- are changed, call DBI API after the update.
1015 -- This API will also check for DBI Installed or not
1016 IF l_debug_on THEN
1017 WSH_DEBUG_SV.log(l_module_name,'Calling DBI API. delivery details l_del_det_id_tab count :',l_del_det_id_tab.COUNT);
1018 END IF;
1019 WSH_INTEGRATION.DBI_Update_Detail_Log
1020 (p_delivery_detail_id_tab => l_cnt_det_id_tab,
1021 p_dml_type => 'UPDATE',
1022 x_return_status => l_dbi_rs);
1023
1024 IF l_debug_on THEN
1025 WSH_DEBUG_SV.log(l_module_name,'Return Status after DBI Call-',l_dbi_rs);
1026 END IF;
1027 IF l_dbi_rs = WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR THEN
1028 x_return_status := l_dbi_rs ;
1029 Rollback to REVERT_DETAILS_GRP;
1030 -- just pass this return status to caller API
1031 IF l_debug_on THEN
1032 WSH_DEBUG_SV.logmsg(l_module_name,'DBI API Returned Unexpected error '||x_return_status);
1033 WSH_DEBUG_SV.pop(l_module_name);
1034 END IF;
1035 return;
1036 END IF;
1037 -- End of Code for DBI Project
1038 --
1039 --}
1040 END IF;
1041
1042 IF (l_del_det_id_tab.count > 0 ) THEN
1043 --{
1044 --
1045 -- Debug Statements
1046 --
1047 IF l_debug_on THEN
1048 WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_WV_UTILS.DETAIL_WEIGHT_VOLUME',WSH_DEBUG_SV.C_PROC_LEVEL);
1049 END IF;
1050 --
1051 WSH_WV_UTILS.Detail_Weight_Volume(
1052 p_detail_rows => l_del_det_id_tab,
1053 p_override_flag => 'Y',
1054 p_calc_wv_if_frozen => 'N',
1055 x_return_status => l_return_status);
1056 --
1057 -- Debug Statements
1058 --
1059 IF l_debug_on THEN
1060 WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_UTIL_CORE.API_POST_CALL',WSH_DEBUG_SV.C_PROC_LEVEL);
1061 END IF;
1062 --
1063 wsh_util_core.api_post_call(
1064 p_return_status => l_return_status,
1065 x_num_warnings => l_num_warnings,
1066 x_num_errors => l_num_errors);
1067
1068 --}
1069 END IF;
1070 --}
1071 END IF;
1072
1073
1074 -- We need to do this because we obtain the l_delivery_id_tab
1075 -- from the l_del_details_csr cursor and this cursor is at the line level.
1076 -- Therefore we can have multiple records in l_delivery_id_tab storing
1077 -- the same delivery_id. And if we pass the same table to revert_deliveries
1078 -- then the APIs Mark_Reprice_Required... will try to process the same
1079 -- delivery multiple times which is not good. Therefore, we are
1080 -- using the caching mechanism to identify unique delivery ids and pass
1081 -- them as out parameters so that they can be used in revert_deliveries.
1082
1083 l_del_index := l_delivery_id_tab.first;
1084 while l_del_index IS NOT NULL LOOP
1085 --{
1086 --
1087 -- Debug Statements
1088 --
1089 IF l_debug_on THEN
1090 WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_UTIL_CORE.GET_CACHED_VALUE',WSH_DEBUG_SV.C_PROC_LEVEL);
1091 END IF;
1092 --
1093 wsh_util_core.get_cached_value(
1094 p_cache_tbl => l_del_cache_tbl,
1095 p_cache_ext_tbl => l_del_ext_cache_tbl,
1096 p_value => l_delivery_id_tab(l_del_index),
1097 p_key => l_delivery_id_tab(l_del_index),
1098 p_action => 'GET',
1099 x_return_status => l_return_status);
1100
1101 IF l_return_status IN (wsh_util_core.g_ret_sts_error, wsh_util_core.g_ret_sts_unexp_error) THEN
1102 RAISE FND_API.G_EXC_ERROR;
1103 END IF;
1104
1105 IF (l_return_status = wsh_util_core.g_ret_sts_warning) THEN
1106 --{
1107 --
1108 -- Debug Statements
1109 --
1110 IF l_debug_on THEN
1111 WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_UTIL_CORE.GET_CACHED_VALUE',WSH_DEBUG_SV.C_PROC_LEVEL);
1112 WSH_DEBUG_SV.log(l_module_name,'Delivery Id is ',l_delivery_id_tab(l_del_index));
1113 END IF;
1114 --
1115 wsh_util_core.get_cached_value(
1116 p_cache_tbl => l_del_cache_tbl,
1117 p_cache_ext_tbl => l_del_ext_cache_tbl,
1118 p_value => l_delivery_id_tab(l_del_index),
1119 p_key => l_delivery_id_tab(l_del_index),
1120 p_action => 'PUT',
1121 x_return_status => l_return_status);
1122
1123 --
1124 -- Debug Statements
1125 --
1126 IF l_debug_on THEN
1127 WSH_DEBUG_SV.log(l_module_name,'Return Status after calling wsh_util_core.get_cached_value for put is',l_return_status);
1128 WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_UTIL_CORE.API_POST_CALL',WSH_DEBUG_SV.C_PROC_LEVEL);
1129 END IF;
1130 --
1131 wsh_util_core.api_post_call(
1132 p_return_status => l_return_status,
1133 x_num_warnings => l_num_warnings,
1134 x_num_errors => l_num_errors);
1135
1136 x_delivery_id_tab(x_delivery_id_tab.count+1) := l_delivery_id_tab(l_del_index);
1137 x_status_code_tab(x_status_code_tab.count+1) := l_status_code_tab(l_del_index);
1138 IF l_debug_on THEN
1139 WSH_DEBUG_SV.logmsg(l_module_name,'After assigning the value');
1140 END IF;
1141 --}
1142 END IF;
1143 l_del_index := l_delivery_id_tab.next(l_del_index);
1144 --}
1145 END LOOP;
1146 --
1147 IF l_num_errors > 0 THEN
1148 RAISE FND_API.G_EXC_ERROR;
1149 ELSIF l_num_warnings > 0 THEN
1150 RAISE WSH_UTIL_CORE.G_EXC_WARNING;
1151 ELSE
1152 x_return_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
1153 END IF;
1154 --
1155 IF l_debug_on THEN
1156 WSH_DEBUG_SV.logmsg(l_module_name,'Before calling count and get');
1157 END IF;
1158 FND_MSG_PUB.Count_And_Get
1159 (
1160 p_count => x_msg_count,
1161 p_data => x_msg_data,
1162 p_encoded => FND_API.G_FALSE
1163 );
1164 --
1165 --}
1166 --
1167 -- Debug Statements
1168 --
1169 IF l_debug_on THEN
1170 IF l_debug_on THEN
1171 WSH_DEBUG_SV.logmsg(l_module_name,'End of the procedure');
1172 END IF;
1173 WSH_DEBUG_SV.pop(l_module_name);
1174 END IF;
1175 --
1176 EXCEPTION
1177 --{
1178 WHEN DD_LOCKED THEN
1179 ROLLBACK TO REVERT_DETAILS_GRP;
1180 x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
1181 -- need to change the message
1182 FND_MESSAGE.SET_NAME('WSH','WSH_DELIVERY_LINES_LOCKED');
1183 wsh_util_core.add_message(x_return_status,l_module_name);
1184 IF l_debug_on THEN
1185 WSH_DEBUG_SV.logmsg(l_module_name,'RECORD_LOCKED exception has occured.',WSH_DEBUG_SV.C_EXCEP_LEVEL);
1186 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:DD_LOCKED');
1187 END IF;
1188 --
1189 WHEN FND_API.G_EXC_ERROR THEN
1190 ROLLBACK TO REVERT_DETAILS_GRP;
1191 x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
1192 --
1193 FND_MSG_PUB.Count_And_Get
1194 (
1195 p_count => x_msg_count,
1196 p_data => x_msg_data,
1197 p_encoded => FND_API.G_FALSE
1198 );
1199 --
1200 --
1201 -- Debug Statements
1202 --
1203 IF l_debug_on THEN
1204 WSH_DEBUG_SV.logmsg(l_module_name,'FND_API.G_EXC_ERROR exception has occured.',WSH_DEBUG_SV.C_EXCEP_LEVEL);
1205 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:FND_API.G_EXC_ERROR');
1206 END IF;
1207 --
1208 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1209 ROLLBACK TO REVERT_DETAILS_GRP;
1210 x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR ;
1211 --
1212 FND_MSG_PUB.Count_And_Get
1213 (
1214 p_count => x_msg_count,
1215 p_data => x_msg_data,
1216 p_encoded => FND_API.G_FALSE
1217 );
1218 --
1219 --
1220 -- Debug Statements
1221 --
1222 IF l_debug_on THEN
1223 WSH_DEBUG_SV.logmsg(l_module_name,'FND_API.G_EXC_UNEXPECTED_ERROR exception has occured.',WSH_DEBUG_SV.C_EXCEP_LEVEL);
1224 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:FND_API.G_EXC_UNEXPECTED_ERROR');
1225 END IF;
1226 --
1227 WHEN WSH_UTIL_CORE.G_EXC_WARNING THEN
1228 x_return_status := WSH_UTIL_CORE.G_RET_STS_WARNING;
1229 --
1230 FND_MSG_PUB.Count_And_Get
1231 (
1232 p_count => x_msg_count,
1233 p_data => x_msg_data,
1234 p_encoded => FND_API.G_FALSE
1235 );
1236 --
1237 --
1238 -- Debug Statements
1239 --
1240 IF l_debug_on THEN
1241 WSH_DEBUG_SV.logmsg(l_module_name,'WSH_UTIL_CORE.G_EXC_WARNING exception has occured.',WSH_DEBUG_SV.C_EXCEP_LEVEL);
1242 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:WSH_UTIL_CORE.G_EXC_WARNING');
1243 END IF;
1244 --
1245 WHEN OTHERS THEN
1246 ROLLBACK TO REVERT_DETAILS_GRP;
1247 x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR ;
1248 wsh_util_core.default_handler('WSH_IB_UI_RECON_GRP.REVERT_DETAILS', l_module_name);
1249 --
1250 FND_MSG_PUB.Count_And_Get
1251 (
1252 p_count => x_msg_count,
1253 p_data => x_msg_data,
1254 p_encoded => FND_API.G_FALSE
1255 );
1256 --
1257 --}
1258 --
1259 -- Debug Statements
1260 --
1261 IF l_debug_on THEN
1262 WSH_DEBUG_SV.logmsg(l_module_name,'Unexpected error has occured. Oracle error message is '|| SQLERRM,WSH_DEBUG_SV.C_UNEXPEC_ERR_LEVEL);
1263 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:OTHERS');
1264 END IF;
1265 --
1266 END revert_details;
1267 --
1268 --========================================================================
1269 -- PROCEDURE : Get_Cum_Rcvd_Rtv_Quantities --
1270 -- This procedure is called only from
1271 -- the Inbound Reconciliation UI to
1272 -- query the rcv shipment lines.
1273 --
1274 -- PARAMETERS: p_delivery_id_tab Table of delivery Ids to be reverted
1275 -- p_status_code_tab Table of Status Code values to updated
1276 -- on the deliveries stored in
1277 -- p_delivery_id_tab.
1278 -- x_return_status Return status of the API
1279
1280 -- COMMENT : This procedure is defined to obtain the cumulative received and
1281 -- returned quantities for a rcv shipment line.
1282 -- In this procedure we get the quantity for each transaction type
1283 -- from rcv_fte_transactions_v for the input p_shipment_line_id
1284 -- and then sum up the received correction quantities, to the
1285 -- received quantities and sum up the returned correction quantities
1286 -- to the returned quantities passing the final received quantity
1287 -- and the returned quantity for a shipment_line_id in rcv_fte_lines_v
1288 -- Please refer to the example in the Appendix.1 under the dld
1289 -- for corrections and rtv to get the description of what this API
1290 -- does.
1291 --========================================================================
1292 PROCEDURE get_cum_rcvd_rtv_quantities(
1293 p_shipment_line_id IN NUMBER,
1294 --p_max_rcv_txn_id IN NUMBER,
1295 x_prim_rcvd_qty OUT NOCOPY NUMBER,
1296 x_prim_ret_qty OUT NOCOPY NUMBER,
1297 x_primary_uom_code OUT NOCOPY VARCHAR2,
1298 x_sec_rcvd_qty OUT NOCOPY NUMBER,
1299 x_sec_ret_qty OUT NOCOPY NUMBER,
1300 x_secondary_uom_code OUT NOCOPY VARCHAR2,
1301 x_delete_rec_flag OUT NOCOPY VARCHAR2,
1302 x_max_rcv_txn_id OUT NOCOPY NUMBER,
1303 x_return_status OUT NOCOPY VARCHAR2
1304 )
1305 IS
1306 --{
1307 --
1308 cursor l_max_txn_id_csr (p_shipment_line_id IN NUMBER) is
1309 select max(transaction_id)
1310 from rcv_fte_transactions_v
1311 where shipment_line_id = p_shipment_line_id;
1312 --
1313 l_max_txn_id NUMBER;
1314 --
1315 --
1316 cursor l_get_rcvd_qty_csr (p_shipment_line_id IN NUMBER) is
1317 select transaction_id,
1318 parent_transaction_id,
1319 primary_quantity,
1320 primary_uom_code,
1321 secondary_quantity,
1322 secondary_uom_code,
1323 DECODE(transaction_type,'MATCH','RECEIPT','UNORDERED','RECEIPT','MATCHED','RECEIPT', 'RECEIVE','RECEIPT', transaction_type) transaction_type,
1324 DECODE(parent_transaction_type, 'MATCH','RECEIPT','UNORDERED','RECEIPT','MATCHED','RECEIPT', 'RECEIVE','RECEIPT', parent_transaction_type) parent_transaction_type
1325 from rcv_fte_transactions_v
1326 where shipment_line_id = p_shipment_line_id
1327 order by nvl(parent_transaction_id, transaction_id), transaction_id;
1328
1329 -- The order by is very important in the above cursor.
1330 -- This way we are getting all the records that belong to transaction type
1331 -- at one place.
1332 -- valid transaction types are 'RECEIVE', 'CORRECT', 'RETURN TO VENDOR'.
1333 --
1334 --
1335 l_parent_txn_id NUMBER;
1336 l_prim_rcvd_qty NUMBER;
1337 l_prim_rcvd_corr_qty NUMBER;
1338 l_prim_ret_qty NUMBER;
1339 l_prim_rtv_corr_qty NUMBER;
1340 l_sec_rcvd_qty NUMBER;
1341 l_sec_rcvd_corr_qty NUMBER;
1342 l_sec_ret_qty NUMBER;
1343 l_sec_rtv_corr_qty NUMBER;
1344 l_primary_uom_code VARCHAR2(32767);
1345 l_secondary_uom_code VARCHAR2(32767);
1346
1347 l_txn_id_tab wsh_util_core.id_tab_type;
1348 l_parent_txn_id_tab wsh_util_core.id_tab_type;
1349 l_prim_qty_tab wsh_util_core.id_tab_type;
1350 l_sec_qty_tab wsh_util_core.id_tab_type;
1351 l_prim_uom_code_tab wsh_util_core.column_tab_type;
1352 l_sec_uom_code_tab wsh_util_core.column_tab_type;
1353 l_txn_type_tab wsh_util_core.column_tab_type;
1354 l_parent_txn_type_tab wsh_util_core.column_tab_type;
1355
1356 l_index NUMBER;
1357
1358 c_receipt CONSTANT VARCHAR2(32767) := 'RECEIPT';
1359 c_correct CONSTANT VARCHAR2(32767) := 'CORRECT';
1360 c_rtv CONSTANT VARCHAR2(32767) := 'RETURN TO VENDOR';
1361 --
1362 --
1363 --}
1364 --
1365 l_debug_on BOOLEAN;
1366 --
1367 l_module_name CONSTANT VARCHAR2(100) := 'wsh.plsql.' || G_PKG_NAME || '.' || 'GET_CUM_RCVD_RTV_QUANTITIES';
1368 --
1369 BEGIN
1370 --{
1371 --
1372 --
1373 l_debug_on := WSH_DEBUG_INTERFACE.g_debug;
1374 --
1375 IF l_debug_on IS NULL
1376 THEN
1377 l_debug_on := WSH_DEBUG_SV.is_debug_enabled;
1378 END IF;
1379 --
1380 --
1381 -- Debug Statements
1382 --
1383 IF l_debug_on THEN
1384 WSH_DEBUG_SV.push(l_module_name);
1385 --
1386 WSH_DEBUG_SV.log(l_module_name,'P_SHIPMENT_LINE_ID',P_SHIPMENT_LINE_ID);
1387 --WSH_DEBUG_SV.log(l_module_name,'P_MAX_RCV_TXN_ID',P_MAX_RCV_TXN_ID);
1388 END IF;
1389 --
1390 x_return_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
1391 --
1392 --
1393 open l_max_txn_id_csr(p_shipment_line_id);
1394 fetch l_max_txn_id_csr into l_max_txn_id;
1395 close l_max_txn_id_csr;
1396
1397 IF l_debug_on THEN
1398 WSH_DEBUG_SV.log(l_module_name,'l_max_txn_id',l_max_txn_id);
1399 END IF;
1400 IF l_debug_on THEN
1401 WSH_DEBUG_SV.logmsg(l_module_name,'Before opening the cursor l_get_rcvd_qty_csr');
1402 END IF;
1403 open l_get_rcvd_qty_csr(p_shipment_line_id);
1404 fetch l_get_rcvd_qty_csr bulk collect into
1405 l_txn_id_tab,
1406 l_parent_txn_id_tab,
1407 l_prim_qty_tab,
1408 l_prim_uom_code_tab,
1409 l_sec_qty_tab,
1410 l_sec_uom_code_tab,
1411 l_txn_type_tab,
1412 l_parent_txn_type_tab;
1413 close l_get_rcvd_qty_csr;
1414 IF l_debug_on THEN
1415 WSH_DEBUG_SV.logmsg(l_module_name,'After fetching the cursor l_get_rcvd_qty_csr');
1416 WSH_DEBUG_SV.log(l_module_name,'rcv txns records count is',l_txn_id_tab.count);
1417 END IF;
1418 x_delete_rec_flag := 'N';
1419
1420 IF l_debug_on THEN
1421 WSH_DEBUG_SV.log(l_module_name,'x_delete_rec_flag',x_delete_rec_flag);
1422 END IF;
1423 l_index := l_prim_qty_tab.first;
1424 IF ( nvl(l_index, 0) > 0 ) THEN
1425 --{
1426 l_primary_uom_code := l_prim_uom_code_tab(l_index);
1427 l_secondary_uom_code := l_sec_uom_code_tab(l_index);
1428 --}
1429 END IF;
1430
1431 while l_index is not null loop
1432 --{
1433 IF l_debug_on THEN
1434 WSH_DEBUG_SV.log(l_module_name,'transaction type',l_txn_type_tab(l_index));
1435 WSH_DEBUG_SV.log(l_module_name,'parent transaction type',l_parent_txn_type_tab(l_index));
1436 END IF;
1437 IF (l_txn_type_tab(l_index) = c_receipt) THEN
1438 --{
1439 l_prim_rcvd_qty := nvl(l_prim_rcvd_qty,0) + nvl(l_prim_qty_tab(l_index),0);
1440 l_sec_rcvd_qty := nvl(l_sec_rcvd_qty,0) + nvl(l_sec_qty_tab(l_index),0);
1441 IF l_debug_on THEN
1442 WSH_DEBUG_SV.log(l_module_name,'l_prim_rcvd_qty',l_prim_rcvd_qty);
1443 END IF;
1444 --}
1445 ELSIF (l_txn_type_tab(l_index) = c_rtv and l_parent_txn_type_tab(l_index) = c_receipt) THEN
1446 --{
1447 l_prim_ret_qty := nvl(l_prim_ret_qty,0) + nvl(l_prim_qty_tab(l_index),0);
1448 l_sec_ret_qty := nvl(l_sec_ret_qty,0) + nvl(l_sec_qty_tab(l_index), 0);
1449 IF l_debug_on THEN
1450 WSH_DEBUG_SV.log(l_module_name,'l_prim_ret_qty',l_prim_ret_qty);
1451 END IF;
1452 --}
1453 ELSIF (l_txn_type_tab(l_index) = c_correct and l_parent_txn_type_tab(l_index) = c_receipt) THEN
1454 --{
1455 l_prim_rcvd_corr_qty := nvl(l_prim_rcvd_corr_qty, 0) + nvl(l_prim_qty_tab(l_index),0);
1456 l_sec_rcvd_corr_qty := nvl(l_sec_rcvd_corr_qty,0) + nvl(l_sec_qty_tab(l_index), 0);
1457 IF l_debug_on THEN
1458 WSH_DEBUG_SV.log(l_module_name,'l_prim_rcvd_corr_qty',l_prim_rcvd_corr_qty);
1459 END IF;
1460 --}
1461 ELSIF (l_txn_type_tab(l_index) = c_correct and l_parent_txn_type_tab(l_index) = c_rtv) THEN
1462 --{
1463 l_prim_rtv_corr_qty := nvl(l_prim_rtv_corr_qty, 0) + nvl(l_prim_qty_tab(l_index),0);
1464 l_sec_rtv_corr_qty := nvl(l_sec_rtv_corr_qty,0) + nvl(l_sec_qty_tab(l_index), 0);
1465 IF l_debug_on THEN
1466 WSH_DEBUG_SV.log(l_module_name,'l_prim_rtv_corr_qty',l_prim_rtv_corr_qty);
1467 END IF;
1468 --}
1469 END IF;
1470 l_index := l_prim_qty_tab.next(l_index);
1471 --}
1472 end loop;
1473
1474 -- Finally adding up all the quantities and setting the out parameters.
1475 x_prim_rcvd_qty := nvl(l_prim_rcvd_qty,0) + nvl(l_prim_rcvd_corr_qty,0);
1476 x_sec_rcvd_qty := nvl(l_sec_rcvd_qty,0) + nvl(l_sec_rcvd_corr_qty,0);
1477 x_prim_ret_qty := nvl(l_prim_ret_qty,0) + nvl(l_prim_rtv_corr_qty,0);
1478 x_sec_ret_qty := nvl(l_sec_ret_qty,0) + nvl(l_sec_rtv_corr_qty,0);
1479 x_primary_uom_code := l_primary_uom_code;
1480 x_secondary_uom_code := l_secondary_uom_code;
1481 x_max_rcv_txn_id := l_max_txn_id;
1482 --}
1483 --
1484 -- Debug Statements
1485 --
1486 IF l_debug_on THEN
1487 WSH_DEBUG_SV.log(l_module_name,'x_max_rcv_txn_id',x_max_rcv_txn_id);
1488 WSH_DEBUG_SV.pop(l_module_name);
1489 END IF;
1490 --
1491 EXCEPTION
1492 --{
1493 WHEN FND_API.G_EXC_ERROR THEN
1494 x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
1495 --
1496 -- Debug Statements
1497 --
1498 IF l_debug_on THEN
1499 WSH_DEBUG_SV.logmsg(l_module_name,'FND_API.G_EXC_ERROR exception has occured.',WSH_DEBUG_SV.C_EXCEP_LEVEL);
1500 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:FND_API.G_EXC_ERROR');
1501 END IF;
1502 --
1503 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1504 x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR ;
1505 --
1506 -- Debug Statements
1507 --
1508 IF l_debug_on THEN
1509 WSH_DEBUG_SV.logmsg(l_module_name,'FND_API.G_EXC_UNEXPECTED_ERROR exception has occured.',WSH_DEBUG_SV.C_EXCEP_LEVEL);
1510 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:FND_API.G_EXC_UNEXPECTED_ERROR');
1511 END IF;
1512 --
1513 WHEN WSH_UTIL_CORE.G_EXC_WARNING THEN
1514 x_return_status := WSH_UTIL_CORE.G_RET_STS_WARNING;
1515 --
1516 -- Debug Statements
1517 --
1518 IF l_debug_on THEN
1519 WSH_DEBUG_SV.logmsg(l_module_name,'WSH_UTIL_CORE.G_EXC_WARNING exception has occured.',WSH_DEBUG_SV.C_EXCEP_LEVEL);
1520 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:WSH_UTIL_CORE.G_EXC_WARNING');
1521 END IF;
1522 --
1523 WHEN OTHERS THEN
1524 x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR ;
1525 wsh_util_core.default_handler('WSH_IB_UI_RECON_GRP.GET_CUM_RCVD_RTV_QUANTITIES', l_module_name);
1526 --}
1527 --
1528 -- Debug Statements
1529 --
1530 IF l_debug_on THEN
1531 WSH_DEBUG_SV.logmsg(l_module_name,'Unexpected error has occured. Oracle error message is '|| SQLERRM,WSH_DEBUG_SV.C_UNEXPEC_ERR_LEVEL);
1532 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:OTHERS');
1533 END IF;
1534 --
1535 END get_cum_rcvd_rtv_quantities;
1536
1537 --========================================================================
1538 -- PROCEDURE : Get_Shipment_Lines This procedure is called only from
1539 -- the Inbound Reconciliation UI
1540 --
1541 -- PARAMETERS: p_api_version known api version error number
1542 -- p_init_msg_list FND_API.G_TRUE to reset list
1543 -- p_shipment_header_id Shipment Header Id of the transaction
1544 -- p_transaction_type transaction type (ASN or RECEIPT)
1545 -- p_view_only_flag Used to decide wether to query all
1546 -- rcv shipment lines or only the ones
1547 -- that user is matching.
1548 -- It gets a value of "Y" if user is
1549 -- reverting a transaction or viewing a
1550 -- matched transaction. Otherwise it gets
1551 -- "N".
1552 -- x_shpmt_lines_out_rec This is a record of tables
1553 -- to store the rcv shipment lines
1554 -- information that needs to be displayed.
1555 -- x_max_rcv_txn_id Not used anymore
1556 -- x_msg_count number of messages in the list
1557 -- x_msg_data text of messages
1558 -- x_return_status return status of the API
1559
1560 -- VERSION : current version 1.0
1561 -- initial version 1.0
1562 -- COMMENT : This procedure is used to query the rcv shipment lines
1563 -- from rcv_fte_lines_v
1564 -- based on the transaction type and other input parameters
1565 -- mentioned. We are using a procedure to do this instead
1566 -- of doing a direct query because we need to get the
1567 -- cumulative quantities of the received quantity and
1568 -- returned quantity for the receipt transaction.
1569 -- We finally return this information in a object of tables
1570 -- to the UI.
1571 -- Please refer to the file WSHITYPS.sql for the defn. of
1572 -- WSH_IB_SHPMT_LINE_REC_TYPE.
1573 -- Following the logical flow of this API -
1574 -- 1. If the transaction type is 'RECEIPT', then we call the API
1575 -- get_cum_rcvd_rtv_quantities to the cumulative received quantities
1576 -- and returned quantities for each shipment line id for that receipt
1577 -- 2. Then, if the uom of the quantitities for each line in rcv_shipment_lines
1578 -- is different from the UOM on the corresponding delivery details
1579 -- (i.e. delivery details belonging to the same po_line_location_id)
1580 -- , then we call convert_quantity to convert the quantity on
1581 -- rcv shipment_lines to the UOM present on the delivery details.
1582 --
1583 --========================================================================
1584
1585 PROCEDURE get_shipment_lines(
1586 p_api_version_number IN NUMBER,
1587 p_init_msg_list IN VARCHAR2,
1588 p_commit IN VARCHAR2,
1589 p_shipment_header_id IN NUMBER,
1590 p_transaction_type IN VARCHAR2,
1591 --p_max_rcv_txn_id IN NUMBER,
1592 p_view_only_flag IN VARCHAR2,
1593 x_shpmt_lines_out_rec OUT NOCOPY WSH_IB_SHPMT_LINE_REC_TYPE,
1594 x_max_rcv_txn_id OUT NOCOPY NUMBER,
1595 x_msg_count OUT NOCOPY NUMBER,
1596 x_msg_data OUT NOCOPY VARCHAR2,
1597 x_return_status OUT NOCOPY VARCHAR2)
1598 IS
1599 --{
1600 l_api_version_number CONSTANT NUMBER := 1.0;
1601 l_api_name CONSTANT VARCHAR2(30):= 'get_shipment_lines';
1602
1603 -- This cursor is used to query all the shipment lines for a given
1604 -- shipment_header_id from rcv_fte_lines_v. This cursor is used
1605 -- for the following cases -
1606 -- 1. When the Transaction Type is ASN or
1607 -- 2. When the Transaction Type is Receipt and the transaction
1608 -- is completely pending or
1609 -- 2. When this API is called from the Review Page to only view the
1610 -- data.
1611 cursor l_all_shipment_line_csr (p_shipment_header_id IN NUMBER) is
1612 select rfl.shipment_line_id,
1613 rfl.item_id,
1614 rfl.item_revision,
1615 rfl.item_description,
1616 rfl.truck_num,
1617 rfl.quantity_shipped,
1618 rfl.quantity_received,
1619 null quantity_returned,
1620 rfl.uom_code,
1621 rfl.unit_of_measure,
1622 rfl.po_release_id,
1623 rfl.packing_slip,
1624 rfl.container_num,
1625 rfl.line_num,
1626 rfl.shipment_unit_price,
1627 rfl.secondary_quantity_shipped,
1628 rfl.secondary_quantity_received,
1629 null secondary_quantity_returned,
1630 rfl.secondary_uom_code,
1631 rfl.secondary_unit_of_measure,
1632 rfl.po_header_id,
1633 rfl.po_line_id,
1634 rfl.po_line_location_id,
1635 rfl.po_segment1 po_order_num,
1636 rfl.po_ship_to_location_id,
1637 rfl.po_shipment_num,
1638 rfl.po_line_number,
1639 msiv.concatenated_segments item_number,
1640 porel.release_num,
1641 rfl.asn_line_flag,
1642 rfl.revision_num po_revision_num,
1643 porel.revision_num rel_revision_num
1644 from rcv_fte_lines_v rfl,
1645 mtl_system_items_vl msiv,
1646 po_releases_all porel,
1647 rcv_shipment_lines rsl
1648 where rfl.shipment_header_id = p_shipment_header_id
1649 and rsl.shipment_header_id = p_shipment_header_id
1650 and rsl.shipment_line_id = rfl.shipment_line_id
1651 and rfl.item_id = msiv.inventory_item_id(+)
1652 and rfl.organization_id = msiv.organization_id(+)
1653 and rfl.po_release_id = porel.po_release_id(+)
1654 order by rfl.po_line_id,
1655 rfl.po_line_location_id,
1656 rfl.shipment_line_id;
1657
1658 -- This cursor is used to query partial set of shipment lines for a given
1659 -- shipment_header_id from rcv_fte_lines_v. This cursor is used
1660 -- when the status of the receipt transaction is MATCHED_AND_CHILD_PENDING
1661 -- and user is trying to the match the pending transactions for the receipt.
1662 -- We query only those lines for which there are records present in
1663 -- wsh_inbound_txn_history for that shipment_header_id.
1664 cursor l_partial_shipment_line_csr (p_shipment_header_id IN NUMBER) is
1665 select distinct rfl.shipment_line_id,
1666 rfl.item_id,
1667 rfl.item_revision,
1668 rfl.item_description,
1669 rfl.truck_num,
1670 rfl.quantity_shipped,
1671 rfl.quantity_received,
1672 null quantity_returned,
1673 rfl.uom_code,
1674 rfl.unit_of_measure,
1675 rfl.po_release_id,
1676 rfl.packing_slip,
1677 rfl.container_num,
1678 rfl.line_num,
1679 rfl.shipment_unit_price,
1680 rfl.secondary_quantity_shipped,
1681 rfl.secondary_quantity_received,
1682 null secondary_quantity_returned,
1683 rfl.secondary_uom_code,
1684 rfl.secondary_unit_of_measure,
1685 rfl.po_header_id,
1686 rfl.po_line_id,
1687 rfl.po_line_location_id,
1688 rfl.po_segment1 po_order_num,
1689 rfl.po_ship_to_location_id,
1690 rfl.po_shipment_num,
1691 rfl.po_line_number,
1692 msiv.concatenated_segments item_number,
1693 porel.release_num,
1694 rfl.asn_line_flag,
1695 rfl.revision_num po_revision_num,
1696 porel.revision_num rel_revision_num
1697 from rcv_fte_lines_v rfl,
1698 mtl_system_items_vl msiv,
1699 po_releases_all porel,
1700 wsh_inbound_txn_history wth,
1701 rcv_shipment_lines rsl
1702 where rfl.shipment_header_id = p_shipment_header_id
1703 and rsl.shipment_header_id = p_shipment_header_id
1704 and rsl.shipment_line_id = rfl.shipment_line_id
1705 and rfl.item_id = msiv.inventory_item_id(+)
1706 and rfl.organization_id = msiv.organization_id(+)
1707 and rfl.po_release_id = porel.po_release_id(+)
1708 and rfl.shipment_line_id = wth.shipment_line_id
1709 and wth.shipment_header_id = p_shipment_header_id
1710 and wth.transaction_type IN ('RECEIPT_CORRECTION','RTV', 'RTV_CORRECTION','RTV_CORRECTION_NEGATIVE','RTV_CORRECTION_POSITIVE','RECEIPT_ADD', 'RECEIPT_CORRECTION_NEGATIVE', 'RECEIPT_CORRECTION_POSITIVE')
1711 order by rfl.po_line_id,
1712 rfl.po_line_location_id,
1713 rfl.shipment_line_id;
1714
1715 l_unit_of_measure VARCHAR2(32767);
1716 l_sec_unit_of_measure VARCHAR2(32767);
1717
1718 --Cursor to check the status of the transaction.
1719 cursor l_txn_status_csr (p_shipment_header_id IN NUMBER) is
1720 select 'Y'
1721 from wsh_inbound_txn_history
1722 where shipment_header_id = p_shipment_header_id
1723 and transaction_type = 'RECEIPT'
1724 and status like 'MATCHED%';
1725
1726 shpmt_line_rec l_partial_shipment_line_csr%ROWTYPE;
1727
1728 -- This cursor is used to get the UOM on the delivery details
1729 -- for the corresponding po_line_location_id
1730 cursor l_get_del_det_item_csr(p_po_line_location_id IN NUMBER,
1731 p_po_line_id IN NUMBER) is
1732 select wdd.inventory_item_id,
1733 wdd.requested_quantity_uom,
1734 wdd.organization_id,
1735 muom.unit_of_measure,
1736 wdd.src_requested_quantity,
1737 wdd.src_requested_quantity2
1738 from wsh_delivery_details wdd,
1739 mtl_units_of_measure muom
1740 where wdd.source_line_id = p_po_line_id
1741 and wdd.po_shipment_line_id = p_po_line_location_id
1742 and wdd.source_code = 'PO'
1743 and wdd.requested_quantity_uom = muom.uom_code
1744 and rownum =1;
1745
1746 l_src_requested_qty NUMBER;
1747 l_src_requested_qty2 NUMBER;
1748
1749 -- This cursor is used to derive pass a unique transaction type number
1750 -- for each shipment line. This is important because
1751 -- this transaction type number is used in the UI to change
1752 -- query conditions on the delivery details.
1753 cursor l_txn_type_num_csr(p_shipment_header_id IN NUMBER, p_shipment_line_id IN NUMBER) is
1754 select DISTINCT DECODE(transaction_type, 'RECEIPT_ADD',1, 'RECEIPT_CORRECTION_POSITIVE',2,'RECEIPT_CORRECTION_NEGATIVE', 3, 'RTV',4, 'RTV_CORRECTION_NEGATIVE',5, 'RTV_CORRECTION_POSITIVE',6,7) txn_type
1755 from wsh_inbound_txn_history
1756 where shipment_line_id = p_shipment_line_id
1757 and shipment_header_id = p_shipment_header_id
1758 and transaction_type IN ('RECEIPT_ADD', 'RECEIPT_CORRECTION_POSITIVE', 'RECEIPT_CORRECTION_NEGATIVE', 'RTV', 'RTV_CORRECTION_NEGATIVE', 'RTV_CORRECTION_POSITIVE')
1759 order by txn_type;
1760
1761 -- This cursor is used to check whether there is atleast one record in
1762 -- rcv_transactions table for the corresponding shipment_line_id.
1763 cursor l_chk_receipt_txn_csr(p_shipment_line_id IN NUMBER) is
1764 select 'X'
1765 from rcv_transactions
1766 where shipment_line_id = p_shipment_line_id
1767 and transaction_type in ('RECEIVE', 'MATCH')
1768 and rownum=1;
1769
1770 l_rcv_txn_rec_exists_flag VARCHAR2(1);
1771
1772 cursor l_lock_txn_hist_csr (p_shipment_header_id IN NUMBER,
1773 p_transaction_type IN VARCHAR2) is
1774 select 'X'
1775 from wsh_inbound_txn_history
1776 where shipment_header_id = p_shipment_header_id
1777 and transaction_type = p_transaction_type
1778 FOR UPDATE OF STATUS NOWAIT;
1779
1780 cursor l_max_with_txn_id_csr (p_shipment_header_id IN NUMBER, p_shipment_line_id IN NUMBER) is
1781 select max(transaction_id)
1782 from wsh_inbound_txn_history
1783 where shipment_line_id = p_shipment_line_id
1784 and shipment_header_id = p_shipment_header_id;
1785
1786 l_lock_history_temp VARCHAR2(1);
1787 l_lock_obtained BOOLEAN;
1788
1789 -- Cursor to check whether there was the receipt was recorded
1790 -- against an ASN or not
1791 cursor l_parent_txn_csr (p_shipment_header_id IN NUMBER) is
1792 select 'Y'
1793 from wsh_inbound_txn_history
1794 where shipment_header_id = p_shipment_header_id
1795 and transaction_type = 'ASN';
1796
1797 l_parent_txn_csr_opened BOOLEAN := FALSE;
1798 l_rcpt_against_asn_flag VARCHAR2(10) := 'N';
1799
1800 -- Cursor to check if there are already matched delivery
1801 -- details for the add_to_receipt transaction_type.
1802 cursor l_matched_det_exist_csr (p_source_header_id IN NUMBER,
1803 p_source_line_id IN NUMBER,
1804 p_po_shpmt_line_id IN NUMBER,
1805 p_rcv_shpmt_line_id IN NUMBER) is
1806
1807 select 'Y'
1808 from wsh_delivery_details
1809 where source_header_id = p_source_header_id
1810 and source_line_id = p_source_line_id
1811 and po_shipment_line_id = p_po_shpmt_line_id
1812 and rcv_shipment_line_id = p_rcv_shpmt_line_id
1813 and rownum = 1;
1814
1815 l_matched_details_exist_flag VARCHAR2(10) := 'N';
1816 --
1817 --
1818 l_txn_type VARCHAR2(32767) := p_transaction_type;
1819 l_dd_item_id NUMBER;
1820 l_dd_uom_code VARCHAR2(32767);
1821 l_dd_organization_id NUMBER;
1822 l_prim_rcvd_qty NUMBER;
1823 l_prim_ret_qty NUMBER;
1824 l_sec_rcvd_qty NUMBER;
1825 l_sec_ret_qty NUMBER;
1826 l_prim_rcv_uom_code VARCHAR2(32767);
1827 l_sec_rcv_uom_code VARCHAR2(32767);
1828 l_delete_rec_flag VARCHAR2(1);
1829 l_index NUMBER;
1830 l_return_status VARCHAR2(1);
1831 l_num_errors NUMBER := 0;
1832 l_num_warnings NUMBER := 0;
1833 l_pkg_name VARCHAR2(32767);
1834 l_max_rcv_txn_id NUMBER;
1835 l_status_matched_flag VARCHAR2(1);
1836 l_txn_type_number NUMBER;
1837 e_next_record EXCEPTION;
1838
1839 record_locked exception;
1840 PRAGMA EXCEPTION_INIT(record_locked, -54);
1841 --
1842 --
1843 --}
1844 --
1845 l_debug_on BOOLEAN;
1846 --
1847 l_module_name CONSTANT VARCHAR2(100) := 'wsh.plsql.' || G_PKG_NAME || '.' || 'GET_SHIPMENT_LINES';
1848 --
1849 BEGIN
1850 --{
1851 --
1852 --
1853 l_debug_on := WSH_DEBUG_INTERFACE.g_debug;
1854 --
1855 IF l_debug_on IS NULL
1856 THEN
1857 l_debug_on := WSH_DEBUG_SV.is_debug_enabled;
1858 END IF;
1859 --
1860 --
1861 -- Debug Statements
1862 --
1863 IF l_debug_on THEN
1864 WSH_DEBUG_SV.push(l_module_name);
1865 --
1866 WSH_DEBUG_SV.log(l_module_name,'P_API_VERSION_NUMBER',P_API_VERSION_NUMBER);
1867 WSH_DEBUG_SV.log(l_module_name,'P_INIT_MSG_LIST',P_INIT_MSG_LIST);
1868 WSH_DEBUG_SV.log(l_module_name,'P_COMMIT',P_COMMIT);
1869 WSH_DEBUG_SV.log(l_module_name,'P_SHIPMENT_HEADER_ID',P_SHIPMENT_HEADER_ID);
1870 WSH_DEBUG_SV.log(l_module_name,'P_TRANSACTION_TYPE',P_TRANSACTION_TYPE);
1871 --WSH_DEBUG_SV.log(l_module_name,'P_MAX_RCV_TXN_ID',P_MAX_RCV_TXN_ID);
1872 END IF;
1873 --
1874 IF FND_API.to_Boolean(p_init_msg_list) THEN
1875 FND_MSG_PUB.initialize;
1876 END IF;
1877 --
1878 IF NOT FND_API.Compatible_API_Call
1879 ( l_api_version_number,
1880 p_api_version_number,
1881 l_api_name,
1882 l_pkg_name
1883 )
1884 THEN
1885 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1886 END IF;
1887 --
1888 -- setting the return status
1889 x_return_status := wsh_util_core.g_ret_sts_success;
1890 -- instantiating the table.
1891
1892 -- Locking the transaction history to make sure that no other process
1893 -- works on this transaction
1894
1895 SAVEPOINT lock_txn_history_sp;
1896 open l_lock_txn_hist_csr(p_shipment_header_id, p_transaction_type);
1897 fetch l_lock_txn_hist_csr into l_lock_history_temp;
1898 l_lock_obtained := l_lock_txn_hist_csr%FOUND;
1899 close l_lock_txn_hist_csr;
1900 IF (NOT l_lock_obtained) THEN
1901 --{
1902 FND_MESSAGE.SET_NAME('WSH','WSH_IB_TXN_UPDATE_ERROR');
1903 wsh_util_core.add_message(WSH_UTIL_CORE.G_RET_STS_ERROR,l_module_name);
1904 RAISE FND_API.G_EXC_ERROR;
1905 --}
1906 END IF;
1907
1908 --
1909 x_shpmt_lines_out_rec := WSH_IB_SHPMT_LINE_REC_TYPE(WSH_NUM_TBL_TYPE(),
1910 WSH_NUM_TBL_TYPE(),
1911 WSH_VAR_TBL_TYPE(),
1912 WSH_VAR_TBL_TYPE(),
1913 WSH_VAR_TBL_TYPE(),
1914 WSH_NUM_TBL_TYPE(),
1915 WSH_NUM_TBL_TYPE(),
1916 WSH_NUM_TBL_TYPE(),
1917 WSH_VAR_TBL_TYPE(),
1918 WSH_VAR_TBL_TYPE(),
1919 WSH_NUM_TBL_TYPE(),
1920 WSH_VAR_TBL_TYPE(),
1921 WSH_VAR_TBL_TYPE(),
1922 WSH_NUM_TBL_TYPE(),
1923 WSH_VAR_TBL_TYPE(),
1924 WSH_NUM_TBL_TYPE(),
1925 WSH_NUM_TBL_TYPE(),
1926 WSH_NUM_TBL_TYPE(),
1927 WSH_VAR_TBL_TYPE(),
1928 WSH_VAR_TBL_TYPE(),
1929 WSH_VAR_TBL_TYPE(),
1930 WSH_NUM_TBL_TYPE(),
1931 WSH_NUM_TBL_TYPE(),
1932 WSH_NUM_TBL_TYPE(),
1933 WSH_VAR_TBL_TYPE(),
1934 WSH_NUM_TBL_TYPE(),
1935 WSH_NUM_TBL_TYPE(),
1936 WSH_NUM_TBL_TYPE(),
1937 WSH_NUM_TBL_TYPE(),
1938 WSH_NUM_TBL_TYPE(),
1939 WSH_NUM_TBL_TYPE());
1940 --
1941 IF l_txn_type = 'RECEIPT' THEN
1942 --{
1943 open l_txn_status_csr(p_shipment_header_id);
1944 fetch l_txn_status_csr into l_status_matched_flag;
1945 close l_txn_status_csr;
1946 --}
1947 ElSE
1948 --{
1949 l_status_matched_flag := 'N';
1950 --}
1951 END IF;
1952 IF l_debug_on THEN
1953 WSH_DEBUG_SV.log(l_module_name,'l_status_matched_flag',nvl(l_status_matched_flag,'N'));
1954 WSH_DEBUG_SV.log(l_module_name,'p_view_only_flag',nvl(p_view_only_flag,'N'));
1955 END IF;
1956 -- This condition is to make sure that the transaction status is matched and its
1957 -- child txns are pending and that the user trying to match the pending transaction.
1958 IF (nvl(l_status_matched_flag, 'N') = 'Y' AND nvl(p_view_only_flag,'N') <> 'Y') THEN
1959 --{
1960 IF l_debug_on THEN
1961 WSH_DEBUG_SV.logmsg(l_module_name,'opening l_partial_shipment_line_csr');
1962 END IF;
1963 open l_partial_shipment_line_csr(p_shipment_header_id);
1964 --}
1965 ELSE
1966 --{
1967 -- otherwise we query all the shipment lines belonging to the header.
1968 IF l_debug_on THEN
1969 WSH_DEBUG_SV.logmsg(l_module_name,'opening l_all_shipment_line_csr');
1970 END IF;
1971 open l_all_shipment_line_csr(p_shipment_header_id);
1972 --}
1973 END IF;
1974 --FOR shpmt_line_rec IN l_shipment_line_csr(p_shipment_header_id)
1975 LOOP
1976 --{
1977 BEGIN
1978 --{
1979 --initializing the variables for every record in the loop
1980 l_txn_type_number := 0;
1981 l_rcv_txn_rec_exists_flag := NULL;
1982
1983 --
1984 IF (nvl(l_status_matched_flag, 'N') = 'Y' AND nvl(p_view_only_flag,'N') <> 'Y') THEN
1985 --{
1986 -- we need to fetch this data only for receipts that are partially matched
1987 fetch l_partial_shipment_line_csr into shpmt_line_rec;
1988 -- We need to pass the transaction type number to the UI because,
1989 -- depending what child transactions are pending for each shipment line
1990 EXIT WHEN l_partial_shipment_line_csr%NOTFOUND;
1991 -- the query on the delivery details changes.
1992 open l_txn_type_num_csr(p_shipment_header_id,shpmt_line_rec.shipment_line_id);
1993 fetch l_txn_type_num_csr into l_txn_type_number;
1994 close l_txn_type_num_csr;
1995 IF l_debug_on THEN
1996 WSH_DEBUG_SV.log(l_module_name,'l_txn_type_number',l_txn_type_number);
1997 END IF;
1998 -- The below condition has been added because, if the receipt
1999 -- is against an ASN and that the transaction_type is
2000 -- RECEIPT_ADD and if there is already a record in
2001 -- wdd matched against that shipment_line_id, then
2002 -- we need to treat it as RECEIPT_CORRECTION_POSITIVE.
2003 IF (nvl(l_txn_type_number,-9999) = 1) THEN
2004 --{
2005 -- need to reset the flag for every shpmt line
2006 l_matched_details_exist_flag := 'N';
2007
2008 IF (NOT l_parent_txn_csr_opened) THEN
2009 --{
2010 open l_parent_txn_csr(p_shipment_header_id);
2011 fetch l_parent_txn_csr into l_rcpt_against_asn_flag;
2012 close l_parent_txn_csr;
2013 l_parent_txn_csr_opened := TRUE;
2014 --}
2015 END IF;
2016 IF (nvl(l_rcpt_against_asn_flag,'N') = 'Y') THEN
2017 --{
2018 open l_matched_det_exist_csr(
2019 shpmt_line_rec.po_header_id,
2020 shpmt_line_rec.po_line_id,
2021 shpmt_line_rec.po_line_location_id,
2022 shpmt_line_rec.shipment_line_id);
2023 fetch l_matched_det_exist_csr into l_matched_details_exist_flag;
2024 close l_matched_det_exist_csr;
2025 IF (nvl(l_matched_details_exist_flag,'N') = 'Y') THEN
2026 --{
2027 -- changing the transaction type to 'Rcpt Correction Positive'
2028 l_txn_type_number := 2;
2029 --}
2030 END IF;
2031 --}
2032 END IF;
2033 --}
2034 END IF;
2035 IF l_debug_on THEN
2036 WSH_DEBUG_SV.log(l_module_name,'l_txn_type_number after the check',l_txn_type_number);
2037 END IF;
2038 --}
2039 ELSE
2040 --{
2041 fetch l_all_shipment_line_csr into shpmt_line_rec;
2042 EXIT WHEN l_all_shipment_line_csr%NOTFOUND;
2043 --}
2044 END IF;
2045 --
2046 IF (nvl(shpmt_line_rec.asn_line_flag,'N') = 'Y' AND p_transaction_type = 'RECEIPT') THEN
2047 --{
2048 -- This is very important check. This tells us whether the record in
2049 -- rcv_shipment_lines actually belongs to the receipt or if that shipment
2050 -- line was created as a part of the ASN transaction.
2051 open l_chk_receipt_txn_csr(shpmt_line_rec.shipment_line_id);
2052 fetch l_chk_receipt_txn_csr into l_rcv_txn_rec_exists_flag;
2053 close l_chk_receipt_txn_csr;
2054
2055 IF l_rcv_txn_rec_exists_flag IS NULL THEN
2056 raise e_next_record;
2057 END IF;
2058 --}
2059 END IF;
2060 --
2061 l_dd_uom_code := NULL;
2062 --
2063 --
2064 -- We need to get the cumulative received and returned quantities only for
2065 -- transaction type 'RECEIPT'. It is not required for ASN.
2066 IF l_txn_type = 'RECEIPT' THEN
2067 --{
2068 get_cum_rcvd_rtv_quantities(
2069 p_shipment_line_id => shpmt_line_rec.shipment_line_id,
2070 --p_max_rcv_txn_id => p_max_rcv_txn_id,
2071 x_prim_rcvd_qty => l_prim_rcvd_qty,
2072 x_prim_ret_qty => l_prim_ret_qty,
2073 x_primary_uom_code => l_prim_rcv_uom_code,
2074 x_sec_rcvd_qty => l_sec_rcvd_qty,
2075 x_sec_ret_qty => l_sec_ret_qty,
2076 x_secondary_uom_code => l_sec_rcv_uom_code,
2077 x_delete_rec_flag => l_delete_rec_flag,
2078 x_max_rcv_txn_id => l_max_rcv_txn_id,
2079 x_return_status => l_return_status
2080 );
2081 --
2082 --
2083 -- Debug Statements
2084 --
2085 IF l_debug_on THEN
2086 WSH_DEBUG_SV.log(l_module_name,'l_prim_rcvd_qty', l_prim_rcvd_qty);
2087 WSH_DEBUG_SV.log(l_module_name,'l_prim_ret_qty', l_prim_ret_qty);
2088 WSH_DEBUG_SV.log(l_module_name,'l_prim_rcv_uom_code', l_prim_rcv_uom_code);
2089 WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_UTIL_CORE.API_POST_CALL',WSH_DEBUG_SV.C_PROC_LEVEL);
2090 END IF;
2091 --
2092 wsh_util_core.api_post_call(
2093 p_return_status => l_return_status,
2094 x_num_warnings => l_num_warnings,
2095 x_num_errors => l_num_errors);
2096 --}
2097 END IF;
2098 --
2099 -- Initializing the out record with all the columns except
2100 -- the quantities and UOMs.
2101 --
2102 --IF nvl(l_delete_rec_flag, 'N') = 'N' THEN
2103 --{
2104 IF (l_max_rcv_txn_id IS NOT NULL) THEN
2105 --{
2106 IF (x_max_rcv_txn_id IS NULL) THEN
2107 --{
2108 x_max_rcv_txn_id := l_max_rcv_txn_id;
2109 --}
2110 ELSIF (x_max_rcv_txn_id < l_max_rcv_txn_id) THEN
2111 --{
2112 x_max_rcv_txn_id := l_max_rcv_txn_id;
2113 --}
2114 END IF;
2115 --}
2116 END IF;
2117 x_shpmt_lines_out_rec.shipment_line_id_tab.extend;
2118 x_shpmt_lines_out_rec.item_id_tab.extend;
2119 x_shpmt_lines_out_rec.item_revision_tab.extend;
2120 x_shpmt_lines_out_rec.item_description_tab.extend;
2121 x_shpmt_lines_out_rec.truck_num_tab.extend;
2122 x_shpmt_lines_out_rec.primary_qty_shipped_tab.extend;
2123 x_shpmt_lines_out_rec.primary_qty_received_tab.extend;
2124 x_shpmt_lines_out_rec.primary_qty_returned_tab.extend;
2125 x_shpmt_lines_out_rec.primary_uom_code_tab.extend;
2126 x_shpmt_lines_out_rec.primary_unit_of_measure_tab.extend;
2127 x_shpmt_lines_out_rec.po_release_id_tab.extend;
2128 x_shpmt_lines_out_rec.packing_slip_tab.extend;
2129 x_shpmt_lines_out_rec.container_num_tab.extend;
2130 x_shpmt_lines_out_rec.rcv_line_num_tab.extend;
2131 x_shpmt_lines_out_rec.shipment_unit_price_tab.extend;
2132 x_shpmt_lines_out_rec.secondary_qty_shipped_tab.extend;
2133 x_shpmt_lines_out_rec.secondary_qty_received_tab.extend;
2134 x_shpmt_lines_out_rec.secondary_qty_returned_tab.extend;
2135 x_shpmt_lines_out_rec.secondary_uom_code_tab.extend;
2136 x_shpmt_lines_out_rec.secondary_unit_of_measure_tab.extend;
2137 x_shpmt_lines_out_rec.item_number_tab.extend;
2138 x_shpmt_lines_out_rec.po_header_id_tab.extend;
2139 x_shpmt_lines_out_rec.po_line_id_tab.extend;
2140 x_shpmt_lines_out_rec.po_line_location_id_tab.extend;
2141 x_shpmt_lines_out_rec.po_order_num_tab.extend;
2142 x_shpmt_lines_out_rec.po_ship_to_location_id_tab.extend;
2143 x_shpmt_lines_out_rec.po_line_location_num_tab.extend;
2144 x_shpmt_lines_out_rec.po_line_num_tab.extend;
2145 x_shpmt_lines_out_rec.po_revision_num_tab.extend;
2146 x_shpmt_lines_out_rec.txn_type_number_tab.extend;
2147 x_shpmt_lines_out_rec.max_txn_id_tab.extend;
2148 --
2149 l_index := x_shpmt_lines_out_rec.shipment_line_id_tab.count;
2150 IF l_debug_on THEN
2151 WSH_DEBUG_SV.logmsg(l_module_name,'Before assigning the values');
2152 WSH_DEBUG_SV.log(l_module_name,'l_index',l_index);
2153 END IF;
2154 open l_max_with_txn_id_csr(p_shipment_header_id, shpmt_line_rec.shipment_line_id);
2155 fetch l_max_with_txn_id_csr into x_shpmt_lines_out_rec.max_txn_id_tab(l_index);
2156 close l_max_with_txn_id_csr;
2157 IF l_debug_on THEN
2158 WSH_DEBUG_SV.log(l_module_name,'x_shpmt_lines_out_rec.max_txn_id_tab('|| l_index||')',x_shpmt_lines_out_rec.max_txn_id_tab(l_index));
2159 END IF;
2160 x_shpmt_lines_out_rec.shipment_line_id_tab(l_index) := shpmt_line_rec.shipment_line_id;
2161 x_shpmt_lines_out_rec.item_id_tab(l_index) := shpmt_line_rec.item_id;
2162 x_shpmt_lines_out_rec.item_revision_tab(l_index) := shpmt_line_rec.item_revision;
2163 x_shpmt_lines_out_rec.item_description_tab(l_index) := shpmt_line_rec.item_description;
2164 x_shpmt_lines_out_rec.truck_num_tab(l_index) := shpmt_line_rec.truck_num;
2165 x_shpmt_lines_out_rec.po_release_id_tab(l_index) := shpmt_line_rec.po_release_id;
2166 x_shpmt_lines_out_rec.packing_slip_tab(l_index) := shpmt_line_rec.packing_slip;
2167 x_shpmt_lines_out_rec.container_num_tab(l_index) := shpmt_line_rec.container_num;
2168 x_shpmt_lines_out_rec.rcv_line_num_tab(l_index) := shpmt_line_rec.line_num;
2169 x_shpmt_lines_out_rec.shipment_unit_price_tab(l_index) := shpmt_line_rec.shipment_unit_price;
2170 x_shpmt_lines_out_rec.item_number_tab(l_index) := shpmt_line_rec.item_number;
2171 x_shpmt_lines_out_rec.po_header_id_tab(l_index) := shpmt_line_rec.po_header_id;
2172 x_shpmt_lines_out_rec.po_line_id_tab(l_index) := shpmt_line_rec.po_line_id;
2173 x_shpmt_lines_out_rec.po_line_location_id_tab(l_index) := shpmt_line_rec.po_line_location_id;
2174 IF (shpmt_line_rec.release_num is not null ) THEN
2175 --{
2176 x_shpmt_lines_out_rec.po_order_num_tab(l_index) := shpmt_line_rec.po_order_num || '-' || shpmt_line_rec.release_num;
2177 --}
2178 ELSE
2179 --{
2180 x_shpmt_lines_out_rec.po_order_num_tab(l_index) := shpmt_line_rec.po_order_num;
2181 --}
2182 END IF;
2183 x_shpmt_lines_out_rec.po_ship_to_location_id_tab(l_index) := shpmt_line_rec.po_ship_to_location_id;
2184 x_shpmt_lines_out_rec.po_line_location_num_tab(l_index) := shpmt_line_rec.po_shipment_num;
2185 x_shpmt_lines_out_rec.po_line_num_tab(l_index) := shpmt_line_rec.po_line_number;
2186 x_shpmt_lines_out_rec.po_revision_num_tab(l_index) := nvl(shpmt_line_rec.rel_revision_num, shpmt_line_rec.po_revision_num);
2187 x_shpmt_lines_out_rec.txn_type_number_tab(l_index) := l_txn_type_number;
2188 IF l_debug_on THEN
2189 WSH_DEBUG_SV.logmsg(l_module_name,'After assigning the values');
2190 WSH_DEBUG_SV.log(l_module_name,'l_txn_type_number', l_txn_type_number);
2191 END IF;
2192 --
2193 --
2194 open l_get_del_det_item_csr(shpmt_line_rec.po_line_location_id,
2195 shpmt_line_rec.po_line_id);
2196 fetch l_get_del_det_item_csr into l_dd_item_id, l_dd_uom_code, l_dd_organization_id, l_unit_of_measure,l_src_requested_qty,l_src_requested_qty2;
2197 close l_get_del_det_item_csr;
2198
2199 IF l_debug_on THEN
2200 WSH_DEBUG_SV.log(l_module_name,'l_dd_uom_code', l_dd_uom_code);
2201 WSH_DEBUG_SV.log(l_module_name,'l_dd_item_id', l_dd_item_id);
2202 WSH_DEBUG_SV.log(l_module_name,'l_src_requested_qty', l_src_requested_qty);
2203 WSH_DEBUG_SV.log(l_module_name,'l_src_requested_qty2', l_src_requested_qty2);
2204 END IF;
2205 IF l_txn_type = 'RECEIPT' THEN
2206 --{
2207 IF l_dd_uom_code = l_prim_rcv_uom_code THEN
2208 --{
2209 x_shpmt_lines_out_rec.primary_qty_received_tab(l_index) := l_prim_rcvd_qty;
2210 x_shpmt_lines_out_rec.primary_qty_returned_tab(l_index) := l_prim_ret_qty;
2211 x_shpmt_lines_out_rec.primary_uom_code_tab(l_index) := l_dd_uom_code;
2212 x_shpmt_lines_out_rec.secondary_qty_received_tab(l_index) := l_sec_rcvd_qty;
2213 x_shpmt_lines_out_rec.secondary_qty_returned_tab(l_index) := l_sec_ret_qty;
2214 x_shpmt_lines_out_rec.secondary_uom_code_tab(l_index) := l_sec_rcv_uom_code;
2215 IF l_debug_on THEN
2216 WSH_DEBUG_SV.log(l_module_name,'l_unit_of_measure', l_unit_of_measure);
2217 END IF;
2218 x_shpmt_lines_out_rec.primary_unit_of_measure_tab(l_index):= l_unit_of_measure;
2219 x_shpmt_lines_out_rec.secondary_unit_of_measure_tab(l_index):= shpmt_line_rec.secondary_unit_of_measure;
2220 --}
2221 ELSE
2222 --{
2223 --
2224 -- Debug Statements
2225 --
2226 IF l_debug_on THEN
2227 WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_INBOUND_UTIL_PKG.CONVERT_QUANTITY For received quantity',WSH_DEBUG_SV.C_PROC_LEVEL);
2228 END IF;
2229 --
2230 WSH_INBOUND_UTIL_PKG.convert_quantity(
2231 p_inv_item_id => l_dd_item_id,
2232 p_organization_id => l_dd_organization_id,
2233 p_primary_uom_code => l_dd_uom_code,
2234 p_quantity => l_prim_rcvd_qty,
2235 p_qty_uom_code => l_prim_rcv_uom_code,
2236 x_conv_qty => x_shpmt_lines_out_rec.primary_qty_received_tab(l_index),
2237 x_return_status => l_return_status);
2238 --
2239 -- Debug Statements
2240 --
2241 IF l_debug_on THEN
2242 WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_UTIL_CORE.API_POST_CALL',WSH_DEBUG_SV.C_PROC_LEVEL);
2243 END IF;
2244 --
2245 wsh_util_core.api_post_call(
2246 p_return_status => l_return_status,
2247 x_num_warnings => l_num_warnings,
2248 x_num_errors => l_num_errors);
2249 --
2250 -- Debug Statements
2251 --
2252 IF l_debug_on THEN
2253 WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_INBOUND_UTIL_PKG.CONVERT_QUANTITY For returned quantity',WSH_DEBUG_SV.C_PROC_LEVEL);
2254 END IF;
2255 --
2256 WSH_INBOUND_UTIL_PKG.convert_quantity(
2257 p_inv_item_id => l_dd_item_id,
2258 p_organization_id => l_dd_organization_id,
2259 p_primary_uom_code => l_dd_uom_code,
2260 p_quantity => l_prim_ret_qty,
2261 p_qty_uom_code => l_prim_rcv_uom_code,
2262 x_conv_qty => x_shpmt_lines_out_rec.primary_qty_returned_tab(l_index),
2263 x_return_status => l_return_status);
2264 --
2265 -- Debug Statements
2266 --
2267 IF l_debug_on THEN
2268 WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_UTIL_CORE.API_POST_CALL',WSH_DEBUG_SV.C_PROC_LEVEL);
2269 END IF;
2270 --
2271 wsh_util_core.api_post_call(
2272 p_return_status => l_return_status,
2273 x_num_warnings => l_num_warnings,
2274 x_num_errors => l_num_errors);
2275
2276 x_shpmt_lines_out_rec.primary_uom_code_tab(l_index) := l_dd_uom_code;
2277 x_shpmt_lines_out_rec.primary_unit_of_measure_tab(l_index):= l_unit_of_measure;
2278 x_shpmt_lines_out_rec.secondary_unit_of_measure_tab(l_index):= shpmt_line_rec.secondary_unit_of_measure;
2279 --}
2280 END IF;
2281 --}
2282 ELSE
2283 --{
2284 IF l_dd_uom_code = shpmt_line_rec.uom_code THEN
2285 --{
2286 x_shpmt_lines_out_rec.primary_qty_shipped_tab(l_index) := shpmt_line_rec.quantity_shipped;
2287 x_shpmt_lines_out_rec.primary_uom_code_tab(l_index) := shpmt_line_rec.uom_code;
2288 x_shpmt_lines_out_rec.secondary_qty_shipped_tab(l_index) := shpmt_line_rec.secondary_quantity_shipped;
2289 x_shpmt_lines_out_rec.secondary_uom_code_tab(l_index) := shpmt_line_rec.secondary_uom_code;
2290 x_shpmt_lines_out_rec.primary_unit_of_measure_tab(l_index):= l_unit_of_measure;
2291 x_shpmt_lines_out_rec.secondary_unit_of_measure_tab(l_index):= shpmt_line_rec.secondary_unit_of_measure;
2292 --}
2293 ELSE
2294 --{
2295 --
2296 -- Debug Statements
2297 --
2298 IF l_debug_on THEN
2299 WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_INBOUND_UTIL_PKG.CONVERT_QUANTITY For shipped quantity',WSH_DEBUG_SV.C_PROC_LEVEL);
2300 END IF;
2301 --
2302 WSH_INBOUND_UTIL_PKG.convert_quantity(
2303 p_inv_item_id => l_dd_item_id,
2304 p_organization_id => l_dd_organization_id,
2305 p_primary_uom_code => l_dd_uom_code,
2306 p_quantity => shpmt_line_rec.quantity_shipped,
2307 p_qty_uom_code => shpmt_line_rec.uom_code,
2308 x_conv_qty => x_shpmt_lines_out_rec.primary_qty_shipped_tab(l_index),
2309 x_return_status => l_return_status);
2310 --
2311 -- Debug Statements
2312 --
2313 IF l_debug_on THEN
2314 WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_UTIL_CORE.API_POST_CALL',WSH_DEBUG_SV.C_PROC_LEVEL);
2315 END IF;
2316 --
2317 wsh_util_core.api_post_call(
2318 p_return_status => l_return_status,
2319 x_num_warnings => l_num_warnings,
2320 x_num_errors => l_num_errors);
2321 --
2322 x_shpmt_lines_out_rec.primary_uom_code_tab(l_index) := l_dd_uom_code;
2323 x_shpmt_lines_out_rec.primary_unit_of_measure_tab(l_index):= l_unit_of_measure;
2324 x_shpmt_lines_out_rec.secondary_unit_of_measure_tab(l_index):= shpmt_line_rec.secondary_unit_of_measure;
2325 x_shpmt_lines_out_rec.primary_uom_code_tab(l_index):= shpmt_line_rec.uom_code;
2326 --}
2327 END IF;
2328 IF (x_shpmt_lines_out_rec.secondary_qty_shipped_tab(l_index) IS NULL
2329 AND l_src_requested_qty2 IS NOT NULL) THEN
2330 --{
2331 -- HW OPMCONV - No need to use OPM precision. Use current INV which is 5
2332 x_shpmt_lines_out_rec.secondary_qty_shipped_tab(l_index) :=
2333 ROUND (
2334 l_src_requested_qty2 * x_shpmt_lines_out_rec.primary_qty_shipped_tab(l_index)/l_src_requested_qty, WSH_UTIL_CORE.C_MAX_DECIMAL_DIGITS_INV
2335 );
2336 l_src_requested_qty2 := NULL;
2337 --}
2338 END IF;
2339 IF l_debug_on THEN
2340 WSH_DEBUG_SV.log(l_module_name,'sec shipped quantity', x_shpmt_lines_out_rec.secondary_qty_shipped_tab(l_index));
2341 END IF;
2342 --}
2343 END IF;
2344 --}
2345 --END IF;
2346 --}
2347 EXCEPTION
2348 --{
2349 WHEN e_next_record THEN
2350 null;
2351 WHEN OTHERS THEN
2352 raise;
2353 --}
2354 END;
2355 --}
2356 END LOOP;
2357 IF l_all_shipment_line_csr%ISOPEN THEN
2358 close l_all_shipment_line_csr;
2359 END IF;
2360 IF l_partial_shipment_line_csr%ISOPEN THEN
2361 close l_partial_shipment_line_csr;
2362 END IF;
2363
2364 -- We need to rollback to release the lock on the transaction history
2365 rollback to lock_txn_history_sp;
2366
2367 IF x_shpmt_lines_out_rec.shipment_line_id_tab.count = 0 THEN
2368 FND_MESSAGE.SET_NAME('WSH','NO_DATA_FOUND');
2369 x_return_status := wsh_util_core.g_ret_sts_error;
2370 wsh_util_core.add_message(x_return_status, l_module_name);
2371 RAISE FND_API.G_EXC_ERROR;
2372 END IF;
2373 --
2374 FND_MSG_PUB.Count_And_Get
2375 (
2376 p_count => x_msg_count,
2377 p_data => x_msg_data,
2378 p_encoded => FND_API.G_FALSE
2379 );
2380 --IF (x_msg_count IS NULL ) THEN
2381 -- x_msg_count := 0;
2382 -- END IF;
2383 --
2384 --
2385 -- Debug Statements
2386 --
2387 IF l_debug_on THEN
2388 wsh_debug_sv.log(l_module_name, 'x_max_rcv_txn_id',x_max_rcv_txn_id);
2389 --wsh_debug_sv.log(l_module_name, 'Number of records in x_shpmt_lines_out_rec',x_shpmt_lines_out_rec.shipment_line_id_tab.count);
2390 --wsh_debug_sv.log(l_module_name, 'Shipment line id 1 is',x_shpmt_lines_out_rec.shipment_line_id_tab(1));
2391 --wsh_debug_sv.log(l_module_name, 'Received Qty 1 is',x_shpmt_lines_out_rec.primary_qty_received_tab(1));
2392 --wsh_debug_sv.log(l_module_name, 'Received Qty 2 is',x_shpmt_lines_out_rec.primary_qty_received_tab(2));
2393 --wsh_debug_sv.log(l_module_name, 'Returned Qty 1 is',x_shpmt_lines_out_rec.primary_qty_returned_tab(1));
2394 --wsh_debug_sv.log(l_module_name, 'Returned Qty 2 is',x_shpmt_lines_out_rec.primary_qty_returned_tab(2));
2395 --wsh_debug_sv.log(l_module_name, 'Uom Code is',x_shpmt_lines_out_rec.primary_uom_code_tab(1));
2396 --wsh_debug_sv.log(l_module_name, 'Shipment line id 2 is',x_shpmt_lines_out_rec.shipment_line_id_tab(2));
2397 WSH_DEBUG_SV.pop(l_module_name);
2398 END IF;
2399 --
2400 --}
2401 EXCEPTION
2402 --{
2403 WHEN RECORD_LOCKED THEN
2404 ROLLBACK TO SAVEPOINT lock_txn_history_sp;
2405 x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
2406 FND_MESSAGE.SET_NAME('WSH','WSH_IB_TXN_LOCK_ERROR');
2407 wsh_util_core.add_message(x_return_status,l_module_name);
2408 IF l_debug_on THEN
2409 WSH_DEBUG_SV.logmsg(l_module_name,'RECORD_LOCKED exception has occured.',WSH_DEBUG_SV.C_EXCEP_LEVEL);
2410 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:FND_API.G_EXC_ERROR');
2411 END IF;
2412 --
2413 WHEN FND_API.G_EXC_ERROR THEN
2414 x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
2415 --
2416 FND_MSG_PUB.Count_And_Get
2417 (
2418 p_count => x_msg_count,
2419 p_data => x_msg_data,
2420 p_encoded => FND_API.G_FALSE
2421 );
2422 --
2423 --
2424 -- Debug Statements
2425 --
2426 IF l_debug_on THEN
2427 WSH_DEBUG_SV.logmsg(l_module_name,'FND_API.G_EXC_ERROR exception has occured.',WSH_DEBUG_SV.C_EXCEP_LEVEL);
2428 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:FND_API.G_EXC_ERROR');
2429 END IF;
2430 --
2431 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
2432 x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR ;
2433 --
2434 FND_MSG_PUB.Count_And_Get
2435 (
2436 p_count => x_msg_count,
2437 p_data => x_msg_data,
2438 p_encoded => FND_API.G_FALSE
2439 );
2440 --
2441 --
2442 -- Debug Statements
2443 --
2444 IF l_debug_on THEN
2445 WSH_DEBUG_SV.logmsg(l_module_name,'FND_API.G_EXC_UNEXPECTED_ERROR exception has occured.',WSH_DEBUG_SV.C_EXCEP_LEVEL);
2446 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:FND_API.G_EXC_UNEXPECTED_ERROR');
2447 END IF;
2448 --
2449 WHEN WSH_UTIL_CORE.G_EXC_WARNING THEN
2450 x_return_status := WSH_UTIL_CORE.G_RET_STS_WARNING;
2451 --
2452 FND_MSG_PUB.Count_And_Get
2453 (
2454 p_count => x_msg_count,
2455 p_data => x_msg_data,
2456 p_encoded => FND_API.G_FALSE
2457 );
2458 --
2459 --
2460 -- Debug Statements
2461 --
2462 IF l_debug_on THEN
2463 WSH_DEBUG_SV.logmsg(l_module_name,'WSH_UTIL_CORE.G_EXC_WARNING exception has occured.',WSH_DEBUG_SV.C_EXCEP_LEVEL);
2464 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:WSH_UTIL_CORE.G_EXC_WARNING');
2465 END IF;
2466 --
2467 WHEN OTHERS THEN
2468 x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR ;
2469 wsh_util_core.default_handler('WSH_IB_UI_RECON_GRP.GET_SHIPMENT_LINES', l_module_name);
2470 --
2471 FND_MSG_PUB.Count_And_Get
2472 (
2473 p_count => x_msg_count,
2474 p_data => x_msg_data,
2475 p_encoded => FND_API.G_FALSE
2476 );
2477 --
2478 --}
2479 --
2480 -- Debug Statements
2481 --
2482 IF l_debug_on THEN
2483 WSH_DEBUG_SV.logmsg(l_module_name,'Unexpected error has occured. Oracle error message is '|| SQLERRM,WSH_DEBUG_SV.C_UNEXPEC_ERR_LEVEL);
2484 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:OTHERS');
2485 END IF;
2486 --
2487 END get_shipment_lines;
2488
2489 --========================================================================
2490 -- PROCEDURE : Revert_Matching This procedure is called only from
2491 -- the Inbound Reconciliation UI to
2492 -- revert a matched ASN or a Receipt
2493 --
2494 -- PARAMETERS: p_api_version known api version error number
2495 -- p_init_msg_list FND_API.G_TRUE to reset list
2496 -- p_shipment_header_id Shipment Header Id of the transaction
2497 -- p_transaction_type transaction type (ASN or RECEIPT)
2498 -- p_object_version_number current object version of the
2499 -- transaction record in
2500 -- wsh_inbound_txn_history
2501 -- x_msg_count number of messages in the list
2502 -- x_msg_data text of messages
2503 -- x_return_status return status of the API
2504
2505 -- VERSION : current version 1.0
2506 -- initial version 1.0
2507 -- COMMENT : This procedure is used to revert a matched transaction (ASN or
2508 -- RECEIPT)
2509 -- The following is the flow of this procedure -
2510 -- 1. If the txn type is 'ASN', then we directly call
2511 -- WSH_ASN_RECEIPT_PVT.Cancel_ASN with the action_code
2512 -- as 'REVERT_ASN'.
2513 -- 2. If the txn type is 'RECEIPT', then we do the following -
2514 -- > Call Revert_Details to revert the delivery details
2515 -- > Call Revert_Deliveries to rever the deliveries
2516 -- > If the revert_details API returns any records of delivery
2517 -- > details that need to be unassigned from the delivery, then
2518 -- > we call WSH_DELIVERY_DETAILS_ACTIONS.
2519 -- > Unassign_Multiple_Details. We need to do this only for
2520 -- > delivery details that ship_from_location_id as -1.
2521 -- > Then we call WSH_PO_CMG_PVT.Reapprove_PO as the
2522 -- > lines that have been re-opened need to be updated with the
2523 -- > latest information from po shipment lines.
2524 -- 3. Finally we call WSH_INBOUND_TXN_HISTORY_PKG.post_process
2525 -- to set the status of the transaction history record appropriately.
2526 --========================================================================
2527 PROCEDURE revert_matching(
2528 p_api_version_number IN NUMBER,
2529 p_init_msg_list IN VARCHAR2,
2530 p_commit IN VARCHAR2,
2531 p_shipment_header_id IN NUMBER,
2532 p_transaction_type IN VARCHAR2,
2533 p_object_version_number IN NUMBER,
2534 x_msg_count OUT NOCOPY NUMBER,
2535 x_msg_data OUT NOCOPY VARCHAR2,
2536 x_return_status OUT NOCOPY VARCHAR2)
2537 IS
2538 --{
2539 l_api_version_number CONSTANT NUMBER := 1.0;
2540 l_api_name CONSTANT VARCHAR2(30):= 'revert_matching';
2541
2542 -- This cursor is used to check whether there is a receipt transaction
2543 -- that is matched. We do not want to allow reverting of ASN if the
2544 -- receipt is already matched or partially matched.
2545 cursor l_child_txn_sts_csr(p_shipment_header_id NUMBER) is
2546 select status, receipt_number
2547 from wsh_inbound_txn_history
2548 where shipment_header_id = p_shipment_header_id
2549 and transaction_type = 'RECEIPT';
2550
2551 l_child_txn_sts VARCHAR2(32767);
2552 l_receipt_num VARCHAR2(32767);
2553 l_action_prms WSH_BULK_TYPES_GRP.action_parameters_rectype;
2554 l_cancel_asn_action_prms WSH_GLBL_VAR_STRCT_GRP.dd_action_parameters_rec_type;
2555 l_line_rec OE_WSH_BULK_GRP.Line_rec_type;
2556 l_out_rec WSH_BULK_TYPES_GRP.Bulk_process_out_rec_type;
2557 l_return_status VARCHAR2(1);
2558
2559 l_dd_list WSH_PO_CMG_PVT.dd_list_type;
2560 l_delivery_id_tab wsh_util_core.id_tab_type;
2561 l_status_code_tab wsh_util_core.column_tab_type;
2562 l_unassign_det_id_tab wsh_util_core.id_tab_type;
2563 l_po_action_prms WSH_BULK_TYPES_GRP.action_parameters_rectype;
2564 l_dd_id_unassigned wsh_util_core.id_tab_type;
2565 l_wt_vol_dd_id wsh_util_core.id_tab_type;
2566 l_unassign_action_prms WSH_GLBL_VAR_STRCT_GRP.dd_action_parameters_rec_type;
2567
2568 l_num_errors NUMBER := 0;
2569 l_num_warnings NUMBER := 0;
2570 l_msg_count NUMBER;
2571 l_msg_data VARCHAR2(32767);
2572 l_pkg_name VARCHAR2(32767);
2573
2574 cursor l_parent_txn_exists_csr(p_shipment_header_id NUMBER) is
2575 select 'Y'
2576 from wsh_inbound_txn_history
2577 where shipment_header_id = p_shipment_header_id
2578 and transaction_type = 'ASN';
2579
2580 l_parent_txn_exists_flag VARCHAR2(10) := 'N';
2581
2582 --
2583 l_debugfile varchar2(2000);
2584 --
2585 l_debug_on BOOLEAN;
2586 --
2587 l_module_name CONSTANT VARCHAR2(100) := 'wsh.plsql.' || G_PKG_NAME || '.' || 'REVERT_MATCHING';
2588 --
2589 --Bugfix 4070732
2590 l_api_session_name CONSTANT VARCHAR2(150) := G_PKG_NAME ||'.' || l_module_name;
2591 l_reset_flags BOOLEAN;
2592 --}
2593 BEGIN
2594 --{
2595 IF WSH_UTIL_CORE.G_START_OF_SESSION_API is null THEN --Bugfix 4070732
2596 WSH_UTIL_CORE.G_START_OF_SESSION_API := l_api_session_name;
2597 WSH_UTIL_CORE.G_CALL_FTE_LOAD_TENDER_API := FALSE;
2598 END IF;
2599
2600 --
2601 --
2602 l_debug_on := WSH_DEBUG_INTERFACE.g_debug;
2603 --
2604 IF l_debug_on IS NULL
2605 THEN
2606 l_debug_on := WSH_DEBUG_SV.is_debug_enabled;
2607 END IF;
2608 --
2609 --
2610 -- Debug Statements
2611 --
2612 IF l_debug_on THEN
2613 WSH_DEBUG_SV.push(l_module_name);
2614 --
2615 WSH_DEBUG_SV.log(l_module_name,'P_API_VERSION_NUMBER',P_API_VERSION_NUMBER);
2616 WSH_DEBUG_SV.log(l_module_name,'P_INIT_MSG_LIST',P_INIT_MSG_LIST);
2617 WSH_DEBUG_SV.log(l_module_name,'P_COMMIT',P_COMMIT);
2618 WSH_DEBUG_SV.log(l_module_name,'P_SHIPMENT_HEADER_ID',P_SHIPMENT_HEADER_ID);
2619 WSH_DEBUG_SV.log(l_module_name,'P_TRANSACTION_TYPE',P_TRANSACTION_TYPE);
2620 WSH_DEBUG_SV.log(l_module_name,'P_OBJECT_VERSION_NUMBER',P_OBJECT_VERSION_NUMBER);
2621 END IF;
2622 --
2623 IF FND_API.to_Boolean(p_init_msg_list) THEN
2624 FND_MSG_PUB.initialize;
2625 END IF;
2626 --
2627 SAVEPOINT revert_matching_grp;
2628 x_return_status := wsh_util_core.g_ret_sts_success;
2629 --
2630 IF NOT FND_API.Compatible_API_Call
2631 ( l_api_version_number,
2632 p_api_version_number,
2633 l_api_name,
2634 l_pkg_name
2635 )
2636 THEN
2637 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2638 END IF;
2639 IF l_debug_on THEN
2640 WSH_DEBUG_SV.logmsg(l_module_name,'After checking Compatible_API_Call');
2641 END IF;
2642 --
2643 IF l_debug_on THEN
2644 WSH_DEBUG_SV.logmsg(l_module_name,'After initializing the message list');
2645 END IF;
2646 -- { IB-Phase-2
2647 -- For manual matching of Receipts, rating of Trips has to be done asynchronously.
2648 WSH_INBOUND_UTIL_PKG.G_ASN_RECEIPT_MATCH_TYPE := 'MANUAL';
2649 -- } IB-Phase-2
2650
2651 IF p_transaction_type = 'ASN' THEN
2652 --{
2653 -- This is an additional check to take care of concurrency issues
2654 -- to make sure that there is no receipt that is already matched
2655 -- while we are reverting an ASN.
2656 open l_child_txn_sts_csr(p_shipment_header_id);
2657 fetch l_child_txn_sts_csr into l_child_txn_sts, l_receipt_num;
2658 close l_child_txn_sts_csr;
2659 IF nvl(l_child_txn_sts,'@@') like 'MATCHED%' THEN
2660 --{
2661 FND_MESSAGE.SET_NAME('WSH','WSH_IB_RECEIPT_MATCHED_ERR');
2662 FND_MESSAGE.SET_TOKEN('RECEIPT_NUM',l_receipt_num);
2663 x_return_status := wsh_util_core.g_ret_sts_error;
2664 wsh_util_core.add_message(x_return_status, l_module_name);
2665 RAISE FND_API.G_EXC_ERROR;
2666 --}
2667 ELSE
2668 --{
2669 l_cancel_asn_action_prms.action_code := 'REVERT_ASN';
2670 --
2671 -- Debug Statements
2672 --
2673 IF l_debug_on THEN
2674 WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_ASN_RECEIPT_PVT.CANCEL_ASN',WSH_DEBUG_SV.C_PROC_LEVEL);
2675 END IF;
2676 --
2677 WSH_ASN_RECEIPT_PVT.Cancel_ASN(
2678 p_header_id => p_shipment_header_id,
2679 -- p_line_rec => l_line_rec,
2680 p_action_prms => l_cancel_asn_action_prms,
2681 x_return_status => l_return_status);
2682 --
2683 --
2684 -- Debug Statements
2685 --
2686 IF l_debug_on THEN
2687 WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_UTIL_CORE.API_POST_CALL',WSH_DEBUG_SV.C_PROC_LEVEL);
2688 END IF;
2689 --
2690 wsh_util_core.api_post_call(
2691 p_return_status => l_return_status,
2692 x_num_warnings => l_num_warnings,
2693 x_num_errors => l_num_errors);
2694 --}
2695 END IF;
2696 --}
2697 ELSIF p_transaction_type = 'RECEIPT' THEN
2698 --{
2699 -- Need to call Nikhil's API
2700 -- setTripStopStatus
2701 --revert_trips(
2702 -- p_shipment_header_id => p_shipment_header_id,
2703 -- p_transaction_type => p_transaction_type,
2704 -- x_return_status => l_return_status);
2705
2706
2707 revert_details(
2708 p_shipment_header_id => p_shipment_header_id,
2709 p_transaction_type => p_transaction_type,
2710 x_dd_list => l_dd_list,
2711 x_delivery_id_tab => l_delivery_id_tab,
2712 x_status_code_tab => l_status_code_tab,
2713 x_unassign_det_id_tab => l_unassign_det_id_tab,
2714 x_msg_count => l_msg_count,
2715 x_msg_data => l_msg_data,
2716 x_return_status => l_return_status);
2717 --
2718 -- Debug Statements
2719 --
2720 IF l_debug_on THEN
2721 WSH_DEBUG_SV.log(l_module_name,'return status is ', l_return_status);
2722 WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_UTIL_CORE.API_POST_CALL',WSH_DEBUG_SV.C_PROC_LEVEL);
2723 END IF;
2724 --
2725 wsh_util_core.api_post_call(
2726 p_return_status => l_return_status,
2727 x_num_warnings => l_num_warnings,
2728 x_num_errors => l_num_errors);
2729
2730 revert_deliveries(
2731 p_delivery_id_tab => l_delivery_id_tab,
2732 p_status_code_tab => l_status_code_tab,
2733 x_return_status => l_return_status);
2734 --
2735 -- Debug Statements
2736 --
2737 IF l_debug_on THEN
2738 WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_UTIL_CORE.API_POST_CALL',WSH_DEBUG_SV.C_PROC_LEVEL);
2739 END IF;
2740 --
2741 wsh_util_core.api_post_call(
2742 p_return_status => l_return_status,
2743 x_num_warnings => l_num_warnings,
2744 x_num_errors => l_num_errors);
2745
2746 IF ( l_unassign_det_id_tab.count > 0 ) THEN
2747 --{
2748 open l_parent_txn_exists_csr(p_shipment_header_id);
2749 fetch l_parent_txn_exists_csr into l_parent_txn_exists_flag;
2750 close l_parent_txn_exists_csr;
2751 IF (nvl(l_parent_txn_exists_flag,'N') = 'Y') THEN
2752 --{
2753 l_unassign_action_prms.caller := wsh_util_core.C_IB_RECEIPT_PREFIX;
2754 --}
2755 ELSE
2756 --{
2757 l_unassign_action_prms.caller := wsh_util_core.C_IB_ASN_PREFIX;
2758 --}
2759 END IF;
2760 --
2761 -- Debug Statements
2762 --
2763 IF l_debug_on THEN
2764 WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_DELIVERY_DETAILS_ACTIONS.UNASSIGN_MULTIPLE_DETAILS',WSH_DEBUG_SV.C_PROC_LEVEL);
2765 END IF;
2766 --
2767 --l_unassign_action_prms.caller := wsh_util_core.C_IB_RECEIPT_PREFIX;
2768 WSH_DELIVERY_DETAILS_ACTIONS.Unassign_Multiple_Details(
2769 P_REC_OF_DETAIL_IDS => l_unassign_det_id_tab,
2770 P_FROM_delivery => 'Y',
2771 P_FROM_container => 'N',
2772 x_return_status => l_return_status,
2773 p_validate_flag => 'Y',
2774 p_action_prms => l_unassign_action_prms);
2775 --
2776 -- Debug Statements
2777 --
2778 IF l_debug_on THEN
2779 WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_UTIL_CORE.API_POST_CALL',WSH_DEBUG_SV.C_PROC_LEVEL);
2780 END IF;
2781 --
2782 wsh_util_core.api_post_call(
2783 p_return_status => l_return_status,
2784 x_num_warnings => l_num_warnings,
2785 x_num_errors => l_num_errors);
2786 --}
2787 END IF;
2788
2789 IF (l_dd_list.delivery_detail_id.count > 0 ) THEN
2790 --{
2791 --
2792 -- Debug Statements
2793 --
2794 IF l_debug_on THEN
2795 WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_PO_CMG_PVT.REAPPROVE_PO',WSH_DEBUG_SV.C_PROC_LEVEL);
2796 END IF;
2797 l_po_action_prms.action_code := 'CANCEL_ASN';
2798 /*
2799 WSH_PO_CMG_PVT.Update_Attributes(
2800 p_line_rec => l_line_rec,
2801 p_action_prms => l_po_action_prms,
2802 p_dd_list => l_dd_list,
2803 p_dd_id_unassigned => l_dd_id_unassigned,
2804 p_wt_vol_dd_id => l_wt_vol_dd_id,
2805 x_return_status => l_return_status);
2806 */
2807 -- It was found that calling Reapprove_PO is better than calling Update_Attributes
2808 -- because, it will also take care of other processing
2809 -- like calculate wt-vol, unassign etc.
2810 WSH_PO_CMG_PVT.Reapprove_PO(
2811 p_line_rec => l_line_rec,
2812 p_action_prms => l_po_action_prms,
2813 p_dd_list => l_dd_list,
2814 x_return_status => l_return_status);
2815
2816 --
2817 -- Debug Statements
2818 --
2819 IF l_debug_on THEN
2820 WSH_DEBUG_SV.log(l_module_name,'Return Status after calling Reapprove_PO is ', l_return_status);
2821 WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_UTIL_CORE.API_POST_CALL',WSH_DEBUG_SV.C_PROC_LEVEL);
2822 END IF;
2823 --
2824 wsh_util_core.api_post_call(
2825 p_return_status => l_return_status,
2826 x_num_warnings => l_num_warnings,
2827 x_num_errors => l_num_errors);
2828 --}
2829 END IF;
2830 --}
2831 END IF;
2832 --
2833 --
2834 IF l_debug_on THEN
2835 WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_INBOUND_TXN_HISTORY_PKG.POST_PROCESS',WSH_DEBUG_SV.C_PROC_LEVEL);
2836 END IF;
2837 --
2838 --
2839 WSH_INBOUND_TXN_HISTORY_PKG.post_process(
2840 p_shipment_header_id => p_shipment_header_id,
2841 p_max_rcv_txn_id => NULL,
2842 p_action_code => 'REVERT',
2843 p_txn_type => p_transaction_type,
2844 p_object_version_number => p_object_version_number,
2845 x_return_status => l_return_status);
2846 --
2847 --
2848 IF l_debug_on THEN
2849 WSH_DEBUG_SV.log(l_module_name,'Return Status after calling WSH_INBOUND_TXN_HISTORY_PKG.post_process is ', l_return_status);
2850 WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_UTIL_CORE.API_POST_CALL',WSH_DEBUG_SV.C_PROC_LEVEL);
2851 END IF;
2852 --
2853 --
2854 wsh_util_core.api_post_call(
2855 p_return_status => l_return_status,
2856 x_num_warnings => l_num_warnings,
2857 x_num_errors => l_num_errors);
2858 --
2859 IF l_debug_on THEN
2860 --{
2861 WSH_DEBUG_SV.log(l_module_name,'l_num_warnings', l_num_warnings);
2862 WSH_DEBUG_SV.log(l_module_name,'l_num_errors', l_num_errors);
2863
2864 fnd_profile.get('WSH_DEBUG_LOG_DIRECTORY',l_debugfile);
2865 l_debugfile := l_debugfile||'/'||WSH_DEBUG_SV.g_file;
2866
2867 FND_MESSAGE.SET_NAME('WSH','WSH_DEBUG_FILE_NAME');
2868 FND_MESSAGE.SET_TOKEN('DEBUG_FILE_NAME',l_debugfile);
2869 x_return_status := wsh_util_core.g_ret_sts_success;
2870 wsh_util_core.add_message(x_return_status, l_module_name);
2871 --}
2872 END IF;
2873 --
2874
2875 IF l_num_errors > 0 THEN
2876 RAISE FND_API.G_EXC_ERROR;
2877 ELSIF l_num_warnings > 0 THEN
2878 RAISE WSH_UTIL_CORE.G_EXC_WARNING;
2879 ELSE
2880 x_return_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
2881 END IF;
2882
2883 -- Standard check of p_commit.
2884 IF FND_API.To_Boolean( p_commit ) THEN
2885
2886 --bug 4070732
2887 IF NOT(WSH_UTIL_CORE.G_CALL_FTE_LOAD_TENDER_API) THEN
2888 --{
2889
2890 l_reset_flags := FALSE;
2891
2892 IF l_debug_on THEN
2893 WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_UTIL_CORE.Process_stops_for_load_tender',WSH_DEBUG_SV.C_PROC_LEVEL);
2894 END IF;
2895
2896 WSH_UTIL_CORE.Process_stops_for_load_tender(p_reset_flags => l_reset_flags,
2897 x_return_status => l_return_status);
2898
2899 IF l_debug_on THEN
2900 WSH_DEBUG_SV.log(l_module_name,'l_return_status',l_return_status);
2901 END IF;
2902
2903 IF l_return_status = WSH_UTIL_CORE.G_RET_STS_ERROR THEN
2904 RAISE FND_API.G_EXC_ERROR;
2905 ELSIF l_return_status = WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR THEN
2906 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2907 ELSIF l_return_status = WSH_UTIL_CORE.G_RET_STS_WARNING THEN
2908 RAISE WSH_UTIL_CORE.G_EXC_WARNING;
2909 END IF;
2910
2911 --}
2912 END IF;
2913 --bug 4070732
2914
2915 COMMIT WORK;
2916 END IF;
2917 --
2918 --bug 4070732
2919 IF upper(WSH_UTIL_CORE.G_START_OF_SESSION_API) = upper(l_api_session_name) THEN
2920 --{
2921 IF NOT(WSH_UTIL_CORE.G_CALL_FTE_LOAD_TENDER_API) THEN
2922 --{
2923
2924 IF FND_API.To_Boolean( p_commit ) THEN
2925
2926 IF l_debug_on THEN
2927 WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_UTIL_CORE.reset_stops_for_load_tender',WSH_DEBUG_SV.C_PROC_LEVEL);
2928 END IF;
2929
2930 WSH_UTIL_CORE.reset_stops_for_load_tender(p_reset_flags => TRUE,
2931 x_return_status => l_return_status);
2932
2933 ELSE
2934
2935 IF l_debug_on THEN
2936 WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_UTIL_CORE.Process_stops_for_load_tender',WSH_DEBUG_SV.C_PROC_LEVEL);
2937 END IF;
2938
2939 WSH_UTIL_CORE.Process_stops_for_load_tender(p_reset_flags => TRUE,
2940 x_return_status => l_return_status);
2941
2942 END IF;
2943
2944 IF l_debug_on THEN
2945 WSH_DEBUG_SV.log(l_module_name,'l_return_status',l_return_status);
2946 END IF;
2947
2948 IF l_return_status IN (WSH_UTIL_CORE.G_RET_STS_ERROR,
2949 WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR,
2950 WSH_UTIL_CORE.G_RET_STS_WARNING) THEN
2951 x_return_status := l_return_status;
2952 END IF;
2953
2954 IF l_return_status IN (WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR,
2955 WSH_UTIL_CORE.G_RET_STS_ERROR) THEN
2956 IF NOT FND_API.To_Boolean( p_commit ) THEN
2957 ROLLBACK TO REVERT_MATCHING_GRP;
2958 END IF;
2959 END IF;
2960
2961 --}
2962 END IF;
2963 --}
2964 END IF;
2965
2966 --bug 4070732
2967 --
2968 FND_MSG_PUB.Count_And_Get
2969 (
2970 p_count => x_msg_count,
2971 p_data => x_msg_data,
2972 p_encoded => FND_API.G_FALSE
2973 );
2974 --
2975
2976 --}
2977 --
2978 -- Debug Statements
2979 --
2980 IF l_debug_on THEN
2981 WSH_DEBUG_SV.pop(l_module_name);
2982 END IF;
2983 --
2984 EXCEPTION
2985 --{
2986 WHEN FND_API.G_EXC_ERROR THEN
2987 ROLLBACK TO REVERT_MATCHING_GRP;
2988 x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
2989 --
2990 --
2991 --
2992 -- Start code for Bugfix 4070732
2993 --
2994 IF upper(WSH_UTIL_CORE.G_START_OF_SESSION_API) = upper(l_api_session_name) THEN
2995 --{
2996 IF NOT(WSH_UTIL_CORE.G_CALL_FTE_LOAD_TENDER_API) THEN
2997 --{
2998 IF l_debug_on THEN
2999 WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_UTIL_CORE.Reset_stops_for_load_tender',WSH_DEBUG_SV.C_PROC_LEVEL);
3000 END IF;
3001
3002 WSH_UTIL_CORE.Reset_stops_for_load_tender(p_reset_flags => TRUE,
3003 x_return_status => l_return_status);
3004
3005
3006 IF l_debug_on THEN
3007 WSH_DEBUG_SV.log(l_module_name,'l_return_status',l_return_status);
3008 END IF;
3009
3010 IF (l_return_status = WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR) THEN
3011 x_return_status := l_return_status;
3012 END IF;
3013 --}
3014 END IF;
3015 --}
3016 END IF;
3017 --
3018 -- End of Code Bugfix 4070732
3019 --
3020 FND_MSG_PUB.Count_And_Get
3021 (
3022 p_count => x_msg_count,
3023 p_data => x_msg_data,
3024 p_encoded => FND_API.G_FALSE
3025 );
3026 --
3027 --
3028 -- Debug Statements
3029 --
3030 IF l_debug_on THEN
3031 WSH_DEBUG_SV.logmsg(l_module_name,'FND_API.G_EXC_ERROR exception has occured.',WSH_DEBUG_SV.C_EXCEP_LEVEL);
3032 fnd_profile.get('WSH_DEBUG_LOG_DIRECTORY',l_debugfile);
3033 l_debugfile := l_debugfile||'/'||WSH_DEBUG_SV.g_file;
3034
3035 FND_MESSAGE.SET_NAME('WSH','WSH_DEBUG_FILE_NAME');
3036 FND_MESSAGE.SET_TOKEN('DEBUG_FILE_NAME',l_debugfile);
3037 wsh_util_core.add_message(x_return_status, l_module_name);
3038
3039 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:FND_API.G_EXC_ERROR');
3040 END IF;
3041 --
3042 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
3043 ROLLBACK TO REVERT_MATCHING_GRP;
3044 x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR ;
3045 --
3046
3047 --
3048 --
3049 -- Start code for Bugfix 4070732
3050 --
3051 IF upper(WSH_UTIL_CORE.G_START_OF_SESSION_API) = upper(l_api_session_name) THEN
3052 --{
3053 IF NOT(WSH_UTIL_CORE.G_CALL_FTE_LOAD_TENDER_API) THEN
3054 --{
3055 IF l_debug_on THEN
3056 WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_UTIL_CORE.Reset_stops_for_load_tender',WSH_DEBUG_SV.C_PROC_LEVEL);
3057 END IF;
3058
3059 WSH_UTIL_CORE.Reset_stops_for_load_tender(p_reset_flags => TRUE,
3060 x_return_status => l_return_status);
3061
3062
3063 IF l_debug_on THEN
3064 WSH_DEBUG_SV.log(l_module_name,'l_return_status',l_return_status);
3065 END IF;
3066
3067 IF (l_return_status = WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR) THEN
3068 x_return_status := l_return_status;
3069 END IF;
3070 --}
3071 END IF;
3072 --}
3073 END IF;
3074 --
3075 -- End of Code Bugfix 4070732
3076 FND_MSG_PUB.Count_And_Get
3077 (
3078 p_count => x_msg_count,
3079 p_data => x_msg_data,
3080 p_encoded => FND_API.G_FALSE
3081 );
3082 --
3083 -- Debug Statements
3084 --
3085 IF l_debug_on THEN
3086 WSH_DEBUG_SV.logmsg(l_module_name,'FND_API.G_EXC_UNEXPECTED_ERROR exception has occured.',WSH_DEBUG_SV.C_EXCEP_LEVEL);
3087 fnd_profile.get('WSH_DEBUG_LOG_DIRECTORY',l_debugfile);
3088 l_debugfile := l_debugfile||'/'||WSH_DEBUG_SV.g_file;
3089
3090 FND_MESSAGE.SET_NAME('WSH','WSH_DEBUG_FILE_NAME');
3091 FND_MESSAGE.SET_TOKEN('DEBUG_FILE_NAME',l_debugfile);
3092 wsh_util_core.add_message(x_return_status, l_module_name);
3093
3094 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:FND_API.G_EXC_UNEXPECTED_ERROR');
3095 END IF;
3096 --
3097 WHEN WSH_UTIL_CORE.G_EXC_WARNING THEN
3098 x_return_status := WSH_UTIL_CORE.G_RET_STS_WARNING;
3099 --
3100 --
3101 --
3102 -- Start code for Bugfix 4070732
3103 --
3104 IF upper(WSH_UTIL_CORE.G_START_OF_SESSION_API) = upper(l_api_session_name) THEN
3105 --{
3106 IF NOT(WSH_UTIL_CORE.G_CALL_FTE_LOAD_TENDER_API) THEN
3107 --{
3108 IF l_debug_on THEN
3109 WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_UTIL_CORE.Process_stops_for_load_tender',WSH_DEBUG_SV.C_PROC_LEVEL);
3110 END IF;
3111
3112 WSH_UTIL_CORE.Process_stops_for_load_tender(p_reset_flags => TRUE,
3113 x_return_status => l_return_status);
3114
3115
3116 IF l_debug_on THEN
3117 WSH_DEBUG_SV.log(l_module_name,'l_return_status',l_return_status);
3118 END IF;
3119
3120 IF l_return_status IN (WSH_UTIL_CORE.G_RET_STS_ERROR,
3121 WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR,
3122 WSH_UTIL_CORE.G_RET_STS_WARNING) THEN
3123 x_return_status := l_return_status;
3124 END IF;
3125 IF l_return_status IN (WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR,
3126 WSH_UTIL_CORE.G_RET_STS_ERROR) THEN
3127 ROLLBACK TO REVERT_MATCHING_GRP;
3128 END IF;
3129
3130 --}
3131 END IF;
3132 --}
3133 END IF;
3134 --
3135 -- End of Code Bugfix 4070732
3136 FND_MSG_PUB.Count_And_Get
3137 (
3138 p_count => x_msg_count,
3139 p_data => x_msg_data,
3140 p_encoded => FND_API.G_FALSE
3141 );
3142 --
3143 -- Debug Statements
3144 --
3145 IF l_debug_on THEN
3146 WSH_DEBUG_SV.logmsg(l_module_name,'WSH_UTIL_CORE.G_EXC_WARNING exception has occured.',WSH_DEBUG_SV.C_EXCEP_LEVEL);
3147 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:WSH_UTIL_CORE.G_EXC_WARNING');
3148 END IF;
3149 --
3150 WHEN OTHERS THEN
3151 ROLLBACK TO REVERT_MATCHING_GRP;
3152 x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR ;
3153 wsh_util_core.default_handler('WSH_IB_UI_RECON_GRP.REVERT_MATCHING', l_module_name);
3154 --
3155 --
3156 --
3157 --
3158 -- Start code for Bugfix 4070732
3159 --
3160 IF upper(WSH_UTIL_CORE.G_START_OF_SESSION_API) = upper(l_api_session_name) THEN
3161 --{
3162 IF NOT(WSH_UTIL_CORE.G_CALL_FTE_LOAD_TENDER_API) THEN
3163 --{
3164 IF l_debug_on THEN
3165 WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_UTIL_CORE.Reset_stops_for_load_tender',WSH_DEBUG_SV.C_PROC_LEVEL);
3166 END IF;
3167
3168 WSH_UTIL_CORE.Reset_stops_for_load_tender(p_reset_flags => TRUE,
3169 x_return_status => l_return_status);
3170
3171
3172 IF l_debug_on THEN
3173 WSH_DEBUG_SV.log(l_module_name,'l_return_status',l_return_status);
3174 END IF;
3175
3176 IF (l_return_status = WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR) THEN
3177 x_return_status := l_return_status;
3178 END IF;
3179 --}
3180 END IF;
3181 --}
3182 END IF;
3183 --
3184 -- End of Code Bugfix 4070732
3185 --
3186 --}
3187 FND_MSG_PUB.Count_And_Get
3188 (
3189 p_count => x_msg_count,
3190 p_data => x_msg_data,
3191 p_encoded => FND_API.G_FALSE
3192 );
3193 --
3194 -- Debug Statements
3195 --
3196 IF l_debug_on THEN
3197 WSH_DEBUG_SV.logmsg(l_module_name,'Unexpected error has occured. Oracle error message is '|| SQLERRM,WSH_DEBUG_SV.C_UNEXPEC_ERR_LEVEL);
3198
3199 fnd_profile.get('WSH_DEBUG_LOG_DIRECTORY',l_debugfile);
3200 l_debugfile := l_debugfile||'/'||WSH_DEBUG_SV.g_file;
3201
3202 FND_MESSAGE.SET_NAME('WSH','WSH_DEBUG_FILE_NAME');
3203 FND_MESSAGE.SET_TOKEN('DEBUG_FILE_NAME',l_debugfile);
3204 wsh_util_core.add_message(x_return_status, l_module_name);
3205
3206 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:OTHERS');
3207 END IF;
3208 --
3209 END revert_matching;
3210
3211
3212
3213 --========================================================================
3214 -- PROCEDURE : Match_Shipments This procedure is called only from
3215 -- the Inbound Reconciliation UI to
3216 -- match a pending ASN or a pending
3217 -- Receipt or partially matched Receipt.
3218 --
3219 -- PARAMETERS: p_api_version known api version error number
3220 -- p_init_msg_list FND_API.G_TRUE to reset list
3221 -- p_shipment_header_id Shipment Header Id of the transaction
3222 -- p_transaction_type transaction type (ASN or RECEIPT)
3223 -- p_max_rcv_txn_id Not used any more.
3224 -- p_process_asn_rcv_flag Flag to decide whether to call
3225 -- WSH_ASN_RECEIPT_PVT.Process_Matched_Txns
3226 -- or not to match the ASN or Receipt.
3227 -- p_process_corr_rtv_flag Flag to decide whether to call
3228 -- WSH_RCV_CORR_RTV_TXN_PKG.
3229 -- process_corrections_and_rtv or not
3230 -- match the corrections, rtv transactions.
3231 -- p_object_version_number current object version of the
3232 -- transaction record in
3233 -- wsh_inbound_txn_history
3234 -- p_shipment_line_id_tab table of shipment line ids. If
3235 -- this table contains any ids, we need
3236 -- to delete all those records from
3237 -- wsh_inbound_txn_history.
3238 -- p_max_txn_id_tab table of max transaction ids for
3239 -- each shipment line id in
3240 -- wsh_inbound_txn_history.
3241 -- x_msg_count number of messages in the list
3242 -- x_msg_data text of messages
3243 -- x_return_status return status of the API
3244
3245 -- VERSION : current version 1.0
3246 -- initial version 1.0
3247 -- COMMENT : This procedure is used to match pending transaction (ASN or
3248 -- RECEIPT)
3249 -- The following is flow of the procedure -
3250 -- 1. We check for the flags p_process_asn_rcv_flag and
3251 -- p_process_corr_rtv_flag. If the p_process_asn_rcv_flag
3252 -- is set to 'Y', then we call
3253 -- WSH_ASN_RECEIPT_PVT.Process_Matched_Txns to match the
3254 -- receipt or ASN. If the p_process_corr_rtv_flag is set to 'Y',
3255 -- then we call WSH_RCV_CORR_RTV_TXN_PKG.process_corrections_and_rtv
3256 -- to match the child transactions for the receipt.
3257 -- 2. Then we call WSH_INBOUND_TXN_HISTORY_PKG.post_process API
3258 -- to set the status of the transaction history record.
3259 -- appropriately
3260 -- 3. The APIs Process_Matched_Txns and process_corrections_and_rtv
3261 -- return the output parameter records that tell us whether
3262 -- the corresponding po shipment lines have been cancelled or closed.
3263 -- If the record count is greater than zero, then we call
3264 -- WSH_ASN_RECEIPT_PVT.cancel_close_pending_txns to cancel or
3265 -- close those respective lines.
3266 --========================================================================
3267 PROCEDURE match_shipments(
3268 p_api_version_number IN NUMBER,
3269 p_init_msg_list IN VARCHAR2,
3270 p_commit IN VARCHAR2,
3271 p_shipment_header_id IN NUMBER,
3272 p_max_rcv_txn_id IN NUMBER,
3273 p_transaction_type IN VARCHAR2,
3274 p_process_asn_rcv_flag IN VARCHAR2,
3275 p_process_corr_rtv_flag IN VARCHAR2,
3276 p_object_version_number IN NUMBER,
3277 p_shipment_line_id_tab IN WSH_NUM_TBL_TYPE,
3278 p_max_txn_id_tab IN WSH_NUM_TBL_TYPE,
3279 x_msg_count OUT NOCOPY NUMBER,
3280 x_msg_data OUT NOCOPY VARCHAR2,
3281 x_return_status OUT NOCOPY VARCHAR2)
3282 IS
3283 --{
3284 l_api_version_number CONSTANT NUMBER := 1.0;
3285 l_api_name CONSTANT VARCHAR2(30):= 'match_shipments';
3286 l_asn_rcv_action_prms WSH_GLBL_VAR_STRCT_GRP.dd_action_parameters_rec_type;
3287 l_corr_rtv_action_prms WSH_BULK_TYPES_GRP.action_parameters_rectype;
3288 l_line_rec OE_WSH_BULK_GRP.Line_rec_type;
3289 l_out_rec WSH_BULK_TYPES_GRP.Bulk_process_out_rec_type;
3290 l_return_status VARCHAR2(1);
3291 l_num_errors NUMBER := 0;
3292 l_num_warnings NUMBER := 0;
3293 i NUMBER;
3294 l_pkg_name VARCHAR2(32767);
3295 l_msg_count NUMBER;
3296 l_msg_data VARCHAR2(32767);
3297 l_process_asn_rcv_count NUMBER := 0;
3298 l_pr_corr_rtv_count NUMBER := 0;
3299 l_corr_rtv_out_rec WSH_RCV_CORR_RTV_TXN_PKG.corr_rtv_out_rec_type;
3300
3301 -- This cursor is used to obtain the matched delivery details from
3302 -- the global temporary table wsh_inbound_del_lines_temp.
3303 -- Please refer to the file WSHILTMP.sql for details about this table
3304 -- definition.
3305 cursor l_inbound_del_det_temp_csr is
3306 select delivery_detail_id,
3307 delivery_id,
3308 shipment_line_id,
3309 child_index,
3310 requested_quantity,
3311 shipped_quantity,
3312 received_quantity,
3313 returned_quantity,
3314 requested_quantity_db,
3315 shipped_quantity_db,
3316 received_quantity_db,
3317 returned_quantity_db,
3318 requested_quantity2,
3319 shipped_quantity2,
3320 received_quantity2,
3321 returned_quantity2,
3322 requested_quantity2_db,
3323 shipped_quantity2_db,
3324 received_quantity2_db,
3325 returned_quantity2_db,
3326 shipment_line_id_db,
3327 ship_from_location_id,
3328 po_shipment_line_id,
3329 source_line_id,
3330 process_corr_rtv_flag,
3331 process_asn_rcv_flag,
3332 requested_quantity_uom,
3333 requested_quantity_uom2,
3334 source_header_id,
3335 released_status,
3336 parent_delivery_detail_id,
3337 picked_quantity,
3338 picked_quantity2,
3339 picked_quantity picked_quantity_db,
3340 picked_quantity2 picked_quantity2_db,
3341 dd_last_update_date
3342 from wsh_inbound_del_lines_temp;
3343
3344 -- { IB-Phase-2
3345 cursor l_get_ship_from_of_header_csr is
3346 select ship_from_location_id
3347 from wsh_inbound_txn_history
3348 where shipment_header_id = p_shipment_header_id
3349 AND transaction_type IN ('ASN','RECEIPT');
3350 -- } IB-Phase-2
3351
3352 l_header_ship_from_loc_id NUMBER; -- IB-Phase-2
3353 l_ib_del_det_rec asn_rcv_del_det_rec_type;
3354 l_ib_det_count NUMBER := 0;
3355
3356 l_asn_rcv_po_cancel_rec OE_WSH_BULK_GRP.line_rec_type;
3357 l_asn_rcv_po_close_rec OE_WSH_BULK_GRP.line_rec_type;
3358 l_rtv_corr_po_cancel_rec OE_WSH_BULK_GRP.line_rec_type;
3359 l_rtv_corr_po_close_rec OE_WSH_BULK_GRP.line_rec_type;
3360
3361 l_max_rcv_txn_id NUMBER;
3362 --
3363 l_debugfile varchar2(2000);
3364 --
3365 --
3366 l_debug_on BOOLEAN;
3367 --
3368 l_module_name CONSTANT VARCHAR2(100) := 'wsh.plsql.' || G_PKG_NAME || '.' || 'MATCH_SHIPMENTS';
3369 --Bugfix 4070732
3370 l_api_session_name CONSTANT VARCHAR2(150) := G_PKG_NAME ||'.' || l_module_name;
3371 l_reset_flags BOOLEAN;
3372
3373 --
3374 --}
3375 BEGIN
3376 IF WSH_UTIL_CORE.G_START_OF_SESSION_API is null THEN --Bugfix 4070732
3377 WSH_UTIL_CORE.G_START_OF_SESSION_API := l_api_session_name;
3378 WSH_UTIL_CORE.G_CALL_FTE_LOAD_TENDER_API := FALSE;
3379 END IF;
3380
3381 --{
3382 --
3383 l_debug_on := WSH_DEBUG_INTERFACE.g_debug;
3384 --
3385 IF l_debug_on IS NULL
3386 THEN
3387 l_debug_on := WSH_DEBUG_SV.is_debug_enabled;
3388 END IF;
3389 --
3390 --
3391 -- Debug Statements
3392 --
3393 IF l_debug_on THEN
3394 WSH_DEBUG_SV.push(l_module_name);
3395 --
3396 WSH_DEBUG_SV.log(l_module_name,'P_API_VERSION_NUMBER',P_API_VERSION_NUMBER);
3397 WSH_DEBUG_SV.log(l_module_name,'P_INIT_MSG_LIST',P_INIT_MSG_LIST);
3398 WSH_DEBUG_SV.log(l_module_name,'P_COMMIT',P_COMMIT);
3399 WSH_DEBUG_SV.log(l_module_name,'P_SHIPMENT_HEADER_ID',P_SHIPMENT_HEADER_ID);
3400 WSH_DEBUG_SV.log(l_module_name,'P_MAX_RCV_TXN_ID',P_MAX_RCV_TXN_ID);
3401 WSH_DEBUG_SV.log(l_module_name,'P_TRANSACTION_TYPE',P_TRANSACTION_TYPE);
3402 WSH_DEBUG_SV.log(l_module_name,'P_PROCESS_ASN_RCV_FLAG',P_PROCESS_ASN_RCV_FLAG);
3403 WSH_DEBUG_SV.log(l_module_name,'P_PROCESS_CORR_RTV_FLAG',P_PROCESS_CORR_RTV_FLAG);
3404 WSH_DEBUG_SV.log(l_module_name,'p_shipment_line_id_tab.count',p_shipment_line_id_tab.count);
3405 WSH_DEBUG_SV.log(l_module_name,'p_max_txn_id_tab.count',p_max_txn_id_tab.count);
3406 WSH_DEBUG_SV.log(l_module_name,'p_shipment_line_id_tab.count',p_shipment_line_id_tab.count);
3407 END IF;
3408 --
3409 IF FND_API.to_Boolean(p_init_msg_list) THEN
3410 FND_MSG_PUB.initialize;
3411 END IF;
3412 --
3413 SAVEPOINT MATCH_SHIPMENTS_GRP;
3414 --
3415 IF NOT FND_API.Compatible_API_Call
3416 ( l_api_version_number,
3417 p_api_version_number,
3418 l_api_name,
3419 l_pkg_name
3420 )
3421 THEN
3422 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
3423 END IF;
3424 --
3425 -- { IB-Phase-2
3426 -- For manual matching of Receipts, rating of Trips has to be done asynchronously.
3427 WSH_INBOUND_UTIL_PKG.G_ASN_RECEIPT_MATCH_TYPE := 'MANUAL';
3428 -- } IB-Phase-2
3429
3430 -- If the Header has ShipFromLocation, then all the lines under the header should have
3431 -- the same ShipFromLocation. The update which is done in the following lines can be
3432 -- avoided under the following scenario
3433 -- 1. Debug is on
3434 -- 2. by using the while loop which is now being used to print the values of l_ib_del_det_rec
3435 -- after being populated by the cursor l_inbound_del_det_temp_csr.
3436 -- In such a scenario, instead of the update, the value of l_header_ship_from_loc_id can
3437 -- be copied to l_ib_del_det_rec.ship_from_location_id_tab, for performance improvements.
3438 -- { IB-Phase-2
3439 open l_get_ship_from_of_header_csr;
3440 fetch l_get_ship_from_of_header_csr into l_header_ship_from_loc_id;
3441 close l_get_ship_from_of_header_csr;
3442 IF l_debug_on THEN
3443 WSH_DEBUG_SV.log(l_module_name,'l_header_ship_from_loc_id',l_header_ship_from_loc_id);
3444 END IF;
3445
3446
3447 IF l_header_ship_from_loc_id IS NOT NULL
3448 THEN
3449 update wsh_inbound_del_lines_temp
3450 set ship_from_location_id = l_header_ship_from_loc_id
3451 where ship_from_location_id is null
3452 or ship_from_location_id = WSH_UTIL_CORE.C_NULL_SF_LOCN_ID;
3453 END IF;
3454 -- } IB-Phase-2
3455
3456 open l_inbound_del_det_temp_csr;
3457 fetch l_inbound_del_det_temp_csr bulk collect into
3458 l_ib_del_det_rec.del_detail_id_tab,
3459 l_ib_del_det_rec.delivery_id_tab,
3460 l_ib_del_det_rec.shipment_line_id_tab,
3461 l_ib_del_det_rec.child_index_tab,
3462 l_ib_del_det_rec.requested_qty_tab,
3463 l_ib_del_det_rec.shipped_qty_tab,
3464 l_ib_del_det_rec.received_qty_tab,
3465 l_ib_del_det_rec.returned_qty_tab,
3466 l_ib_del_det_rec.requested_qty_db_tab,
3467 l_ib_del_det_rec.shipped_qty_db_tab,
3468 l_ib_del_det_rec.received_qty_db_tab,
3469 l_ib_del_det_rec.returned_qty_db_tab,
3470 l_ib_del_det_rec.requested_qty2_tab,
3471 l_ib_del_det_rec.shipped_qty2_tab,
3472 l_ib_del_det_rec.received_qty2_tab,
3473 l_ib_del_det_rec.returned_qty2_tab,
3474 l_ib_del_det_rec.requested_qty2_db_tab,
3475 l_ib_del_det_rec.shipped_qty2_db_tab,
3476 l_ib_del_det_rec.received_qty2_db_tab,
3477 l_ib_del_det_rec.returned_qty2_db_tab,
3478 l_ib_del_det_rec.shipment_line_id_db_tab,
3479 l_ib_del_det_rec.ship_from_location_id_tab,
3480 l_ib_del_det_rec.po_line_location_id_tab,
3481 l_ib_del_det_rec.po_line_id_tab,
3482 l_ib_del_det_rec.process_corr_rtv_flag_tab,
3483 l_ib_del_det_rec.process_asn_rcv_flag_tab,
3484 l_ib_del_det_rec.requested_qty_uom_tab,
3485 l_ib_del_det_rec.requested_qty_uom2_tab,
3486 l_ib_del_det_rec.po_header_id_tab,
3487 l_ib_del_det_rec.released_status_tab,
3488 l_ib_del_det_rec.parent_delivery_detail_id_tab,
3489 l_ib_del_det_rec.picked_qty_tab,
3490 l_ib_del_det_rec.picked_qty2_tab,
3491 l_ib_del_det_rec.picked_qty_db_tab,
3492 l_ib_del_det_rec.picked_qty2_db_tab,
3493 l_ib_del_det_rec.last_update_date_tab;
3494 close l_inbound_del_det_temp_csr;
3495
3496 l_ib_det_count := l_ib_del_det_rec.del_detail_id_tab.count;
3497
3498 IF l_debug_on THEN
3499 WSH_DEBUG_SV.log(l_module_name,'l_ib_det_count',l_ib_det_count);
3500 i := l_ib_del_det_rec.del_detail_id_tab.first;
3501 WHILE i is not null LOOP
3502 --{
3503 WSH_DEBUG_SV.log(l_module_name,'process_asn_rcv_flag_tab(' || i || ')',l_ib_del_det_rec.process_asn_rcv_flag_tab(i));
3504 WSH_DEBUG_SV.log(l_module_name,'process_corr_rtv_flag(' || i || ')',l_ib_del_det_rec.process_corr_rtv_flag_tab(i));
3505 WSH_DEBUG_SV.log(l_module_name,'shipment_line_id(' || i || ')',l_ib_del_det_rec.shipment_line_id_tab(i));
3506 WSH_DEBUG_SV.log(l_module_name,'po_line_location_id(' || i || ')',l_ib_del_det_rec.po_line_location_id_tab(i));
3507 WSH_DEBUG_SV.log(l_module_name,'delivery_detail_id(' || i || ')',l_ib_del_det_rec.del_detail_id_tab(i));
3508 WSH_DEBUG_SV.log(l_module_name,'parent_delivery_detail_id(' || i || ')',l_ib_del_det_rec.parent_delivery_detail_id_tab(i));
3509 WSH_DEBUG_SV.log(l_module_name,'requested_qty_db_tab(' || i || ')',l_ib_del_det_rec.requested_qty_db_tab(i));
3510 WSH_DEBUG_SV.log(l_module_name,'requested_qty_tab(' || i || ')',l_ib_del_det_rec.requested_qty_tab(i));
3511 WSH_DEBUG_SV.log(l_module_name,'shipped_qty_tab(' || i || ')',l_ib_del_det_rec.shipped_qty_tab(i));
3512 WSH_DEBUG_SV.log(l_module_name,'received_qty_tab(' || i || ')',l_ib_del_det_rec.received_qty_tab(i));
3513 WSH_DEBUG_SV.log(l_module_name,'returned_qty_tab(' || i || ')',l_ib_del_det_rec.returned_qty_tab(i));
3514 WSH_DEBUG_SV.log(l_module_name,'child_index_tab(' || i || ')',l_ib_del_det_rec.child_index_tab(i));
3515 WSH_DEBUG_SV.log(l_module_name,'requested_qty_uom_tab(' || i || ')',l_ib_del_det_rec.requested_qty_uom_tab(i));
3516 WSH_DEBUG_SV.log(l_module_name,'ship_from_location_id_tab(' || i || ')',l_ib_del_det_rec.ship_from_location_id_tab(i));
3517 WSH_DEBUG_SV.log(l_module_name,'released_status_tab(' || i || ')',l_ib_del_det_rec.released_status_tab(i));
3518 WSH_DEBUG_SV.log(l_module_name,'delivery_id_tab(' || i || ')',l_ib_del_det_rec.delivery_id_tab(i));
3519 WSH_DEBUG_SV.log(l_module_name,'requested_qty2_db_tab(' || i || ')',l_ib_del_det_rec.requested_qty2_db_tab(i));
3520 WSH_DEBUG_SV.log(l_module_name,'requested_qty2_tab(' || i || ')',l_ib_del_det_rec.requested_qty2_tab(i));
3521 WSH_DEBUG_SV.log(l_module_name,'shipped_qty2_tab(' || i || ')',l_ib_del_det_rec.shipped_qty2_tab(i));
3522 WSH_DEBUG_SV.log(l_module_name,'received_qty2_tab(' || i || ')',l_ib_del_det_rec.received_qty2_tab(i));
3523 WSH_DEBUG_SV.log(l_module_name,'returned_qty2_tab(' || i || ')',l_ib_del_det_rec.returned_qty2_tab(i));
3524 WSH_DEBUG_SV.log(l_module_name,'picked_qty_tab(' || i || ')',l_ib_del_det_rec.picked_qty_tab(i));
3525 WSH_DEBUG_SV.log(l_module_name,'picked_qty2_tab(' || i || ')',l_ib_del_det_rec.picked_qty2_tab(i));
3526 WSH_DEBUG_SV.log(l_module_name,'last_update_date_tab(' || i || ')',l_ib_del_det_rec.last_update_date_tab(i));
3527 i := l_ib_del_det_rec.del_detail_id_tab.NEXT(i);
3528 --}
3529 END LOOP;
3530 END IF;
3531 --need to extend these columns as these are not fetched from the db
3532 IF l_ib_det_count > 0 THEN
3533 l_ib_del_det_rec.shpmt_line_id_idx_tab.extend(l_ib_det_count);
3534 l_ib_del_det_rec.trip_id_tab.extend(l_ib_det_count);
3535 END IF;
3536 l_ib_del_det_rec.shipment_header_id := p_shipment_header_id;
3537 l_ib_del_det_rec.max_transaction_id := p_max_rcv_txn_id;
3538 l_ib_del_det_rec.transaction_type := p_transaction_type;
3539 l_ib_del_det_rec.object_version_number := p_object_version_number;
3540 IF nvl(p_process_asn_rcv_flag, 'N') = 'Y' THEN
3541 --{
3542 --l_action_prms.shipment_header_id := l_ib_del_det_rec.shipment_header_id;
3543 l_asn_rcv_action_prms.action_code := p_transaction_type;
3544 l_asn_rcv_action_prms.caller := 'WSH_IB_UI_MATCH';
3545 WSH_ASN_RECEIPT_PVT.Process_Matched_Txns(
3546 p_dd_rec => l_ib_del_det_rec,
3547 p_line_rec => l_line_rec,
3548 p_action_prms => l_asn_rcv_action_prms,
3549 p_shipment_header_id => l_ib_del_det_rec.shipment_header_id,
3550 p_max_txn_id => l_ib_del_det_rec.max_transaction_id,
3551 x_po_cancel_rec => l_asn_rcv_po_cancel_rec,
3552 x_po_close_rec => l_asn_rcv_po_close_rec,
3553 x_return_status => l_return_status);
3554 --
3555 -- Debug Statements
3556 --
3557 IF l_debug_on THEN
3558 WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_UTIL_CORE.API_POST_CALL',WSH_DEBUG_SV.C_PROC_LEVEL);
3559 END IF;
3560 --
3561 wsh_util_core.api_post_call(
3562 p_return_status => l_return_status,
3563 x_num_warnings => l_num_warnings,
3564 x_num_errors => l_num_errors);
3565 --}
3566 END IF;
3567 IF nvl(p_process_corr_rtv_flag, 'N') = 'Y' THEN
3568 --{
3569 --
3570 -- Debug Statements
3571 --
3572 IF l_debug_on THEN
3573 WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_RCV_CORR_RTV_TXN_PKG.PROCESS_CORRECTIONS_AND_RTV',WSH_DEBUG_SV.C_PROC_LEVEL);
3574 END IF;
3575 --
3576 l_corr_rtv_action_prms.action_code := p_transaction_type;
3577 WSH_RCV_CORR_RTV_TXN_PKG.process_corrections_and_rtv (
3578 p_rtv_corr_in_rec => l_line_rec,
3579 p_matched_detail_rec => l_ib_del_det_rec,
3580 p_action_prms => l_corr_rtv_action_prms,
3581 p_rtv_corr_out_rec => l_corr_rtv_out_rec,
3582 x_po_cancel_rec => l_rtv_corr_po_cancel_rec,
3583 x_po_close_rec => l_rtv_corr_po_close_rec,
3584 x_msg_data => l_msg_data,
3585 x_msg_count => l_msg_count,
3586 x_return_status => l_return_status);
3587
3588 --
3589 -- Debug Statements
3590 --
3591 IF l_debug_on THEN
3592 WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_UTIL_CORE.API_POST_CALL',WSH_DEBUG_SV.C_PROC_LEVEL);
3593 END IF;
3594 --
3595 wsh_util_core.api_post_call(
3596 p_return_status => l_return_status,
3597 x_num_warnings => l_num_warnings,
3598 x_num_errors => l_num_errors,
3599 p_msg_data => l_msg_data);
3600 --}
3601 END IF;
3602 --
3603 --
3604 -- Debug Statements
3605 --
3606 IF l_debug_on THEN
3607 WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_INBOUND_TXN_HISTORY_PKG.POST_PROCESS',WSH_DEBUG_SV.C_PROC_LEVEL);
3608 WSH_DEBUG_SV.log(l_module_name,'p_shipment_line_id_tab.count', p_shipment_line_id_tab.count);
3609 END IF;
3610 --
3611 -- This was added as a part of the changes to support matching of
3612 -- partially matched transactions.
3613 -- The information stored in p_max_txn_id_tab is passed from the get_shipment_lines
3614 -- to the UI and then from UI to the match_shipments so that we know exactly
3615 -- what records need to be deleted from wsh_inbound_txn_history
3616 -- when matching a partially matched transaction.
3617 IF (p_shipment_line_id_tab.count > 0) THEN
3618 --{
3619 l_max_rcv_txn_id := -1;
3620 -- can use bulk delete
3621 FOR i in p_shipment_line_id_tab.FIRST..p_shipment_line_id_tab.LAST LOOP
3622 --{
3623 IF l_debug_on THEN
3624 WSH_DEBUG_SV.log(l_module_name,'p_shipment_line_id_tab(i)', p_shipment_line_id_tab(i));
3625 WSH_DEBUG_SV.log(l_module_name,'p_max_txn_id_tab(i)', p_max_txn_id_tab(i));
3626 END IF;
3627 delete from wsh_inbound_txn_history
3628 where shipment_line_id = p_shipment_line_id_tab(i)
3629 and transaction_type NOT IN ('ASN','RECEIPT')
3630 and transaction_id <= p_max_txn_id_tab(i);
3631 --}
3632 END LOOP;
3633 --}
3634 ELSE
3635 --{
3636 l_max_rcv_txn_id := p_max_rcv_txn_id;
3637 --}
3638 END IF;
3639 --
3640 WSH_INBOUND_TXN_HISTORY_PKG.post_process(
3641 p_shipment_header_id => p_shipment_header_id,
3642 p_max_rcv_txn_id => l_max_rcv_txn_id,
3643 p_action_code => 'MATCHED',
3644 p_txn_type => p_transaction_type,
3645 p_object_version_number => p_object_version_number,
3646 x_return_status => l_return_status);
3647 --
3648 -- Debug Statements
3649 --
3650 IF l_debug_on THEN
3651 WSH_DEBUG_SV.log(l_module_name,'Return Status after calling WSH_INBOUND_TXN_HISTORY_PKG.post_process is ', l_return_status);
3652 WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_UTIL_CORE.API_POST_CALL',WSH_DEBUG_SV.C_PROC_LEVEL);
3653 END IF;
3654 --
3655 wsh_util_core.api_post_call(
3656 p_return_status => l_return_status,
3657 x_num_warnings => l_num_warnings,
3658 x_num_errors => l_num_errors);
3659
3660 --x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
3661 IF (l_asn_rcv_po_cancel_rec.line_id.COUNT > 0 OR l_asn_rcv_po_close_rec.line_id.COUNT > 0 ) THEN
3662 --{
3663 WSH_ASN_RECEIPT_PVT.cancel_close_pending_txns(
3664 p_po_cancel_rec => l_asn_rcv_po_cancel_rec,
3665 p_po_close_rec => l_asn_rcv_po_close_rec,
3666 x_return_status => l_return_status);
3667 --
3668 -- Debug Statements
3669 --
3670 IF l_debug_on THEN
3671 WSH_DEBUG_SV.log(l_module_name,'Return Status after calling cancel_close_pending_txns 1 is ', l_return_status);
3672 WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_UTIL_CORE.API_POST_CALL',WSH_DEBUG_SV.C_PROC_LEVEL);
3673 END IF;
3674 --
3675 wsh_util_core.api_post_call(
3676 p_return_status => l_return_status,
3677 x_num_warnings => l_num_warnings,
3678 x_num_errors => l_num_errors);
3679 --}
3680 END IF;
3681 --
3682 IF (l_rtv_corr_po_cancel_rec.line_id.COUNT > 0 OR l_rtv_corr_po_close_rec.line_id.COUNT > 0 ) THEN
3683 --{
3684 WSH_ASN_RECEIPT_PVT.cancel_close_pending_txns(
3685 p_po_cancel_rec => l_rtv_corr_po_cancel_rec,
3686 p_po_close_rec => l_rtv_corr_po_close_rec,
3687 x_return_status => l_return_status);
3688 --
3689 -- Debug Statements
3690 --
3691 IF l_debug_on THEN
3692 WSH_DEBUG_SV.log(l_module_name,'Return Status after calling cancel_close_pending_txns 2 is ', l_return_status);
3693 WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_UTIL_CORE.API_POST_CALL',WSH_DEBUG_SV.C_PROC_LEVEL);
3694 END IF;
3695 --
3696 wsh_util_core.api_post_call(
3697 p_return_status => l_return_status,
3698 x_num_warnings => l_num_warnings,
3699 x_num_errors => l_num_errors);
3700 --}
3701 END IF;
3702
3703 IF l_debug_on THEN
3704 --{
3705 fnd_profile.get('WSH_DEBUG_LOG_DIRECTORY',l_debugfile);
3706 l_debugfile := l_debugfile||'/'||WSH_DEBUG_SV.g_file;
3707
3708 FND_MESSAGE.SET_NAME('WSH','WSH_DEBUG_FILE_NAME');
3709 FND_MESSAGE.SET_TOKEN('DEBUG_FILE_NAME',l_debugfile);
3710 x_return_status := wsh_util_core.g_ret_sts_success;
3711 wsh_util_core.add_message(x_return_status, l_module_name);
3712
3713 --}
3714 END IF;
3715 --
3716
3717 IF l_num_warnings > 0 THEN
3718 RAISE WSH_UTIL_CORE.G_EXC_WARNING;
3719 ELSE
3720 x_return_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
3721 END IF;
3722 -- Standard check of p_commit.
3723 IF FND_API.To_Boolean( p_commit ) THEN
3724
3725 --bug 4070732
3726 IF NOT(WSH_UTIL_CORE.G_CALL_FTE_LOAD_TENDER_API) THEN
3727 --{
3728
3729 l_reset_flags := FALSE;
3730
3731 IF l_debug_on THEN
3732 WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_UTIL_CORE.Process_stops_for_load_tender',WSH_DEBUG_SV.C_PROC_LEVEL);
3733 END IF;
3734
3735 WSH_UTIL_CORE.Process_stops_for_load_tender(p_reset_flags => l_reset_flags,
3736 x_return_status => l_return_status);
3737
3738 IF l_debug_on THEN
3739 WSH_DEBUG_SV.log(l_module_name,'l_return_status',l_return_status);
3740 END IF;
3741
3742 IF l_return_status = WSH_UTIL_CORE.G_RET_STS_ERROR THEN
3743 RAISE FND_API.G_EXC_ERROR;
3744 ELSIF l_return_status = WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR THEN
3745 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
3746 ELSIF l_return_status = WSH_UTIL_CORE.G_RET_STS_WARNING THEN
3747 RAISE WSH_UTIL_CORE.G_EXC_WARNING;
3748 END IF;
3749
3750 --}
3751 END IF;
3752 --bug 4070732
3753
3754 COMMIT WORK;
3755 END IF;
3756 --
3757 --bug 4070732
3758 IF upper(WSH_UTIL_CORE.G_START_OF_SESSION_API) = upper(l_api_session_name) THEN
3759 --{
3760 IF NOT(WSH_UTIL_CORE.G_CALL_FTE_LOAD_TENDER_API) THEN
3761 --{
3762
3763 IF FND_API.To_Boolean( p_commit ) THEN
3764
3765 IF l_debug_on THEN
3766 WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_UTIL_CORE.reset_stops_for_load_tender',WSH_DEBUG_SV.C_PROC_LEVEL);
3767 END IF;
3768
3769 WSH_UTIL_CORE.reset_stops_for_load_tender(p_reset_flags => TRUE,
3770 x_return_status => l_return_status);
3771
3772
3773 ELSE
3774
3775
3776 IF l_debug_on THEN
3777 WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_UTIL_CORE.Process_stops_for_load_tender',WSH_DEBUG_SV.C_PROC_LEVEL);
3778 END IF;
3779
3780 WSH_UTIL_CORE.Process_stops_for_load_tender(p_reset_flags => TRUE,
3781 x_return_status => l_return_status);
3782
3783 END IF;
3784
3785 IF l_debug_on THEN
3786 WSH_DEBUG_SV.log(l_module_name,'l_return_status',l_return_status);
3787 END IF;
3788
3789 IF l_return_status IN (WSH_UTIL_CORE.G_RET_STS_ERROR,
3790 WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR,
3791 WSH_UTIL_CORE.G_RET_STS_WARNING) THEN
3792 x_return_status := l_return_status;
3793 END IF;
3794
3795 IF l_return_status IN (WSH_UTIL_CORE.G_RET_STS_ERROR,
3796 WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR) THEN
3797 IF NOT FND_API.To_Boolean( p_commit ) THEN
3798 ROLLBACK TO MATCH_SHIPMENTS_GRP;
3799 END IF;
3800 END IF;
3801
3802 --}
3803 END IF;
3804 --}
3805 END IF;
3806
3807 --bug 4070732
3808 --
3809 FND_MSG_PUB.Count_And_Get
3810 (
3811 p_count => x_msg_count,
3812 p_data => x_msg_data,
3813 p_encoded => FND_API.G_FALSE
3814 );
3815 --
3816 --}
3817 --
3818 -- Debug Statements
3819 --
3820 IF l_debug_on THEN
3821 WSH_DEBUG_SV.pop(l_module_name);
3822 END IF;
3823 --
3824 EXCEPTION
3825 --{
3826 WHEN FND_API.G_EXC_ERROR THEN
3827 ROLLBACK TO MATCH_SHIPMENTS_GRP;
3828 x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
3829 --
3830 --
3831 -- Start code for Bugfix 4070732
3832 --
3833 IF upper(WSH_UTIL_CORE.G_START_OF_SESSION_API) = upper(l_api_session_name) THEN
3834 --{
3835 IF NOT(WSH_UTIL_CORE.G_CALL_FTE_LOAD_TENDER_API) THEN
3836 --{
3837 IF l_debug_on THEN
3838 WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_UTIL_CORE.Reset_stops_for_load_tender',WSH_DEBUG_SV.C_PROC_LEVEL);
3839 END IF;
3840
3841 WSH_UTIL_CORE.Reset_stops_for_load_tender(p_reset_flags => TRUE,
3842 x_return_status => l_return_status);
3843
3844
3845 IF l_debug_on THEN
3846 WSH_DEBUG_SV.log(l_module_name,'l_return_status',l_return_status);
3847 END IF;
3848
3849 IF (l_return_status = WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR) THEN
3850 x_return_status := l_return_status;
3851 END IF;
3852 --}
3853 END IF;
3854 --}
3855 END IF;
3856 --
3857 -- End of Code Bugfix 4070732
3858 --
3859 FND_MSG_PUB.Count_And_Get
3860 (
3861 p_count => x_msg_count,
3862 p_data => x_msg_data,
3863 p_encoded => FND_API.G_FALSE
3864 );
3865 --
3866 -- Debug Statements
3867 --
3868 IF l_debug_on THEN
3869 WSH_DEBUG_SV.logmsg(l_module_name,'FND_API.G_EXC_ERROR exception has occured.',WSH_DEBUG_SV.C_EXCEP_LEVEL);
3870 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:FND_API.G_EXC_ERROR');
3871 END IF;
3872 --
3873 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
3874 ROLLBACK TO MATCH_SHIPMENTS_GRP;
3875 x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR ;
3876 --
3877 --
3878 -- Start code for Bugfix 4070732
3879 --
3880 IF upper(WSH_UTIL_CORE.G_START_OF_SESSION_API) = upper(l_api_session_name) THEN
3881 --{
3882 IF NOT(WSH_UTIL_CORE.G_CALL_FTE_LOAD_TENDER_API) THEN
3883 --{
3884 IF l_debug_on THEN
3885 WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_UTIL_CORE.Reset_stops_for_load_tender',WSH_DEBUG_SV.C_PROC_LEVEL);
3886 END IF;
3887
3888 WSH_UTIL_CORE.Reset_stops_for_load_tender(p_reset_flags => TRUE,
3889 x_return_status => l_return_status);
3890
3891
3892 IF l_debug_on THEN
3893 WSH_DEBUG_SV.log(l_module_name,'l_return_status',l_return_status);
3894 END IF;
3895
3896 IF (l_return_status = WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR) THEN
3897 x_return_status := l_return_status;
3898 END IF;
3899 --}
3900 END IF;
3901 --}
3902 END IF;
3903 --
3904 -- End of Code Bugfix 4070732
3905 --
3906 FND_MSG_PUB.Count_And_Get
3907 (
3908 p_count => x_msg_count,
3909 p_data => x_msg_data,
3910 p_encoded => FND_API.G_FALSE
3911 );
3912 -- Debug Statements
3913 --
3914 IF l_debug_on THEN
3915 WSH_DEBUG_SV.logmsg(l_module_name,'FND_API.G_EXC_UNEXPECTED_ERROR exception has occured.',WSH_DEBUG_SV.C_EXCEP_LEVEL);
3916
3917 fnd_profile.get('WSH_DEBUG_LOG_DIRECTORY',l_debugfile);
3918 l_debugfile := l_debugfile||'/'||WSH_DEBUG_SV.g_file;
3919
3920 FND_MESSAGE.SET_NAME('WSH','WSH_DEBUG_FILE_NAME');
3921 FND_MESSAGE.SET_TOKEN('DEBUG_FILE_NAME',l_debugfile);
3922 wsh_util_core.add_message(x_return_status, l_module_name);
3923
3924 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:FND_API.G_EXC_UNEXPECTED_ERROR');
3925 END IF;
3926 --
3927 WHEN WSH_UTIL_CORE.G_EXC_WARNING THEN
3928 x_return_status := WSH_UTIL_CORE.G_RET_STS_WARNING;
3929 --
3930 --
3931 --
3932 -- Start code for Bugfix 4070732
3933 --
3934 IF upper(WSH_UTIL_CORE.G_START_OF_SESSION_API) = upper(l_api_session_name) THEN
3935 --{
3936 IF NOT(WSH_UTIL_CORE.G_CALL_FTE_LOAD_TENDER_API) THEN
3937 --{
3938 IF l_debug_on THEN
3939 WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_UTIL_CORE.Process_stops_for_load_tender',WSH_DEBUG_SV.C_PROC_LEVEL);
3940 END IF;
3941
3942 WSH_UTIL_CORE.Process_stops_for_load_tender(p_reset_flags => TRUE,
3943 x_return_status => l_return_status);
3944
3945
3946 IF l_debug_on THEN
3947 WSH_DEBUG_SV.log(l_module_name,'l_return_status',l_return_status);
3948 END IF;
3949
3950 IF l_return_status IN (WSH_UTIL_CORE.G_RET_STS_ERROR,
3951 WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR,
3952 WSH_UTIL_CORE.G_RET_STS_WARNING) THEN
3953 x_return_status := l_return_status;
3954 END IF;
3955 IF l_return_status IN (WSH_UTIL_CORE.G_RET_STS_ERROR,
3956 WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR) THEN
3957 ROLLBACK TO MATCH_SHIPMENTS_GRP;
3958 END IF;
3959
3960
3961 --}
3962 END IF;
3963 --}
3964 END IF;
3965 --
3966 -- End of Code Bugfix 4070732
3967 --
3968 FND_MSG_PUB.Count_And_Get
3969 (
3970 p_count => x_msg_count,
3971 p_data => x_msg_data,
3972 p_encoded => FND_API.G_FALSE
3973 );
3974 -- Debug Statements
3975 --
3976 IF l_debug_on THEN
3977 WSH_DEBUG_SV.logmsg(l_module_name,'WSH_UTIL_CORE.G_EXC_WARNING exception has occured.',WSH_DEBUG_SV.C_EXCEP_LEVEL);
3978 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:WSH_UTIL_CORE.G_EXC_WARNING');
3979 END IF;
3980 --
3981 WHEN OTHERS THEN
3982 ROLLBACK TO MATCH_SHIPMENTS_GRP;
3983 x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR ;
3984 wsh_util_core.default_handler('WSH_IB_UI_RECON_GRP.MATCH_SHIPMENTS', l_module_name);
3985 --
3986 --
3987 -- Start code for Bugfix 4070732
3988 --
3989 IF upper(WSH_UTIL_CORE.G_START_OF_SESSION_API) = upper(l_api_session_name) THEN
3990 --{
3991 IF NOT(WSH_UTIL_CORE.G_CALL_FTE_LOAD_TENDER_API) THEN
3992 --{
3993 IF l_debug_on THEN
3994 WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_UTIL_CORE.Reset_stops_for_load_tender',WSH_DEBUG_SV.C_PROC_LEVEL);
3995 END IF;
3996
3997 WSH_UTIL_CORE.Reset_stops_for_load_tender(p_reset_flags => TRUE,
3998 x_return_status => l_return_status);
3999
4000
4001 IF l_debug_on THEN
4002 WSH_DEBUG_SV.log(l_module_name,'l_return_status',l_return_status);
4003 END IF;
4004
4005 IF (l_return_status = WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR) THEN
4006 x_return_status := l_return_status;
4007 END IF;
4008 --}
4009 END IF;
4010 --}
4011 END IF;
4012 --
4013 -- End of Code Bugfix 4070732
4014 --
4015 --}
4016 FND_MSG_PUB.Count_And_Get
4017 (
4018 p_count => x_msg_count,
4019 p_data => x_msg_data,
4020 p_encoded => FND_API.G_FALSE
4021 );
4022 --
4023 -- Debug Statements
4024 --
4025 IF l_debug_on THEN
4026 WSH_DEBUG_SV.logmsg(l_module_name,'Unexpected error has occured. Oracle error message is '|| SQLERRM,WSH_DEBUG_SV.C_UNEXPEC_ERR_LEVEL);
4027 fnd_profile.get('WSH_DEBUG_LOG_DIRECTORY',l_debugfile);
4028 l_debugfile := l_debugfile||'/'||WSH_DEBUG_SV.g_file;
4029
4030 FND_MESSAGE.SET_NAME('WSH','WSH_DEBUG_FILE_NAME');
4031 FND_MESSAGE.SET_TOKEN('DEBUG_FILE_NAME',l_debugfile);
4032 wsh_util_core.add_message(x_return_status, l_module_name);
4033 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:OTHERS');
4034 END IF;
4035 --
4036 END match_shipments;
4037
4038 END WSH_IB_UI_RECON_GRP;
[Home] [Help]