[Home] [Help]
PACKAGE BODY: APPS.RCV_WSH_INTERFACE_PKG
Source
1 PACKAGE BODY RCV_WSH_INTERFACE_PKG AS
2 /* $Header: RCVWSHIB.pls 120.11.12020000.8 2013/03/26 06:59:37 honwei ship $*/
3
4 /*===========================================================================
5
6 Private procedures and functions
7
8 ===========================================================================*/
9 FUNCTION get_uom_from_code
10 ( p_uom_code IN VARCHAR2 )
11 RETURN VARCHAR2 IS
12 l_unit_of_measure mtl_units_of_measure.unit_of_measure%TYPE;
13 BEGIN
14
15 IF (p_uom_code IS NULL) THEN
16 RETURN NULL;
17 END IF;
18
19 SELECT unit_of_measure
20 INTO l_unit_of_measure
21 FROM mtl_units_of_measure
22 WHERE uom_code = p_uom_code;
23
24 RETURN l_unit_of_measure;
25 EXCEPTION
26 WHEN OTHERS THEN
27 IF (g_asn_debug = 'Y') THEN
28 asn_debug.put_line('Unexpected exception in get_uom_from_code : ' || SQLERRM);
29 END IF;
30 raise fnd_api.g_exc_unexpected_error;
31 END get_uom_from_code;
32
33 --RTV2 rtv project phase 2 : start
34 -- Get_return_lpn_id() function should not be used to fetch lpn_id after cancellation
35 -- as wsh_delivery_assignments.parent_delivery_detail_id is cleared after cancellation.
36 FUNCTION get_return_lpn_id
37 ( p_wdd_id IN NUMBER)
38 RETURN NUMBER IS
39 l_lpn_id number;
40 BEGIN
41
42 IF (g_asn_debug = 'Y') THEN
43 asn_debug.put_line('p_wdd_id : ' || p_wdd_id);
44 END IF;
45 IF (p_wdd_id IS NULL) THEN
46 RETURN NULL;
47 END IF;
48
49 SELECT wdd.lpn_id
50 into l_lpn_id
51 FROM wsh_delivery_Details wdd, wsh_delivery_assignments wda
52 WHERE wdd.delivery_detail_id(+) = wda.parent_delivery_detail_id
53 AND wda.delivery_detail_id = p_wdd_id;
54
55 IF (g_asn_debug = 'Y') THEN
56 asn_debug.put_line('l_lpn_id : ' || l_lpn_id);
57 END IF;
58 RETURN l_lpn_id;
59 EXCEPTION
60 WHEN OTHERS THEN
61 IF (g_asn_debug = 'Y') THEN
62 asn_debug.put_line('Unexpected exception in get_return_lpn_id : ' || SQLERRM);
63 END IF;
64 raise fnd_api.g_exc_unexpected_error;
65 END get_return_lpn_id;
66
67 PROCEDURE unmark_wdd_lpn(p_wdd_rec IN wsh_delivery_details%rowtype,
68 p_lpn_id IN NUMBER DEFAULT NULL,
69 x_return_status OUT NOCOPY VARCHAR2,
70 x_msg_count OUT NOCOPY NUMBER,
71 x_msg_data OUT NOCOPY VARCHAR2) IS
72 l_lpn_id NUMBER := NULL;
73 l_message VARCHAR2(2000);
74 BEGIN
75
76 IF (p_lpn_id IS NOT NULL) THEN
77 l_lpn_id := p_lpn_id;
78 END IF;
79
80 IF (l_lpn_id IS NULL) THEN
81 RETURN;
82 END IF;
83
84 wms_return_sv.unmark_returns
85 (x_return_status => x_return_status,
86 x_msg_count => x_msg_count,
87 x_msg_data => x_msg_data,
88 p_rcv_trx_interface_id => p_wdd_rec.source_line_id,
89 p_ret_transaction_type => 'RETURN TO VENDOR',
90 p_lpn_id => l_lpn_id,
91 p_item_id => p_wdd_rec.inventory_item_id,
92 p_item_revision => p_wdd_rec.revision,
93 p_org_id => p_wdd_rec.organization_id,
94 p_lot_number => p_wdd_rec.lot_number);
95
96 IF (nvl(x_msg_count,0) = 0) THEN
97 asn_debug.put_line('unmark lpn successfully');
98 ELSE
99 asn_debug.put_line(' Could not unmark lpn : ----> ' || l_lpn_id);
100 asn_debug.put_line(' Could not unmark for item : ----> ' || p_wdd_rec.inventory_item_id);
101 FOR i IN 1..x_msg_count LOOP
102 l_message := fnd_msg_pub.get(I, 'F');
103 asn_debug.put_line(substr(l_message,1,255));
104 end LOOP;
105 END IF;
106
107 EXCEPTION
108 WHEN OTHERS THEN
109 IF (g_asn_debug = 'Y') THEN
110 asn_debug.put_line('Unexpected exception in unmark_wdd_lpn with lpn_id : '||l_lpn_id||', SQLERRM:' || SQLERRM);
111 END IF;
112 raise fnd_api.g_exc_unexpected_error;
113 END unmark_wdd_lpn;
114
115 PROCEDURE remove_RTV_order
116 ( p_bkup_rti_id IN NUMBER) IS
117 BEGIN
118 DELETE FROM rcv_transactions_interface
119 WHERE interface_transaction_id = p_bkup_rti_id;
120 IF (g_asn_debug = 'Y') THEN
121 asn_debug.put_line('DELETED RTI');
122 END IF;
123 --
124 BEGIN
125 DELETE FROM rcv_lots_interface
126 WHERE interface_transaction_id = p_bkup_rti_id;
127
128 IF (g_asn_debug = 'Y') THEN
129 asn_debug.put_line('DELETED RLI');
130 END IF;
131 EXCEPTION
132 WHEN OTHERS THEN NULL;
133 END;
134 --
135 BEGIN
136 DELETE FROM rcv_serials_interface
137 WHERE interface_transaction_id = p_bkup_rti_id;
138
139 IF (g_asn_debug = 'Y') THEN
140 asn_debug.put_line('DELETED RSI');
141 END IF;
142 EXCEPTION
143 WHEN OTHERS THEN NULL;
144 END;
145 --
146 BEGIN
147 DELETE FROM mtl_transaction_lots_temp
148 WHERE product_transaction_id = p_bkup_rti_id;
149
150 IF (g_asn_debug = 'Y') THEN
151 asn_debug.put_line('DELETED MTLT');
152 END IF;
153 EXCEPTION
154 WHEN OTHERS THEN NULL;
155 END;
156 --
157 BEGIN
158 DELETE FROM mtl_serial_numbers_temp
159 WHERE product_transaction_id = p_bkup_rti_id;
160
161 IF (g_asn_debug = 'Y') THEN
162 asn_debug.put_line('DELETED MSNT');
163 END IF;
164 EXCEPTION
165 WHEN OTHERS THEN NULL;
166 END;
167
168 --
169 EXCEPTION
170 WHEN OTHERS THEN
171 IF (g_asn_debug = 'Y') THEN
172 asn_debug.put_line('Unexpected exception in remove_RTV_order : ' || SQLERRM);
173 END IF;
174 raise fnd_api.g_exc_unexpected_error;
175 END remove_RTV_order;
176 --RTV2 rtv project phase 2 : end
177
178 --
179 PROCEDURE create_return_reservation
180 ( p_wdd_rec IN WSH_GLBL_VAR_STRCT_GRP.Delivery_Details_Rec_Type,
181 p_lpn_id IN NUMBER, ---- RTV2 rtv project phase 2
182 x_return_status OUT NOCOPY VARCHAR2, ---- RTV2 rtv project phase 2
183 x_msg_count OUT NOCOPY NUMBER, ---- RTV2 rtv project phase 2
184 x_msg_data OUT NOCOPY VARCHAR2) IS ---- RTV2 rtv project phase 2
185
186 l_rsv_rec INV_Reservation_GLOBAL.MTL_RESERVATION_REC_TYPE := NULL;
187 l_api_version CONSTANT NUMBER := 1.0;
188 l_api_name CONSTANT VARCHAR2(30) := 'Process_Line';
189 l_dummy_sn INV_Reservation_Global.Serial_Number_Tbl_Type;
190 --l_api_return_status VARCHAR2(1);
191 --l_msg_count NUMBER;
192 --l_msg_data VARCHAR2(2000);
193 l_message VARCHAR2(2000);
194 l_qty_succ_reserved NUMBER;
195 l_org_wide_res_id NUMBER;
196 x_no_violation BOOLEAN;--RTV2 rtv project phase 2
197
198 BEGIN
199 l_rsv_rec.demand_source_type_id := 13;
200 l_rsv_rec.supply_source_type_id := 13;
201 l_rsv_rec.organization_id := p_wdd_rec.organization_id;
202 l_rsv_rec.inventory_item_id := p_wdd_rec.inventory_item_id;
203 l_rsv_rec.demand_source_name := p_wdd_rec.source_header_number;
204 l_rsv_rec.reservation_quantity := p_wdd_rec.src_requested_quantity;
205 l_rsv_rec.reservation_uom_code := p_wdd_rec.src_requested_quantity_uom;
206 l_rsv_rec.revision := p_wdd_rec.revision;
207 l_rsv_rec.subinventory_code := p_wdd_rec.subinventory;
208 l_rsv_rec.locator_id := p_wdd_rec.locator_id;
209 l_rsv_rec.lot_number := p_wdd_rec.lot_number;
210 l_rsv_rec.detailed_quantity := 0;
211 l_rsv_rec.requirement_date := sysdate;
212 --Bug# 10090672
213 l_rsv_rec.ship_ready_flag := 1;
214 l_rsv_rec.staged_flag := 'Y';
215 --RTV2 rtv project phase 2 : start
216 l_rsv_rec.lpn_id := p_lpn_id;
217 IF (g_asn_debug = 'Y') THEN
218 asn_debug.put_line('In create_return_reservation,p_lpn_id : ' || p_lpn_id);
219 END IF;
220 --We need to call clear_quantity_cache, as for multiple lines cases
221 --if one line fails, the qty tree will be cached with incorrect qty
222 inv_quantity_tree_pub.clear_quantity_cache;
223 IF (g_asn_debug = 'Y') THEN
224 asn_debug.put_line('inv_quantity_tree_pub.clear_quantity_cache ');
225 END IF;
226 --RTV2 rtv project phase 2 : end
227
228 INV_Reservation_PUB.Create_Reservation
229 (
230 p_api_version_number => 1.0
231 , p_init_msg_lst => fnd_api.G_TRUE
232 , x_return_status => x_return_status --RTV2 rtv project phase 2
233 , x_msg_count => x_msg_count --RTV2 rtv project phase 2
234 , x_msg_data => x_msg_data --RTV2 rtv project phase 2
235 , p_rsv_rec => l_rsv_rec
236 , p_serial_number => l_dummy_sn
237 , x_serial_number => l_dummy_sn
238 , p_partial_reservation_flag => fnd_api.g_true
239 , p_force_reservation_flag => fnd_api.g_false
240 , p_validation_flag => fnd_api.g_true
241 , x_quantity_reserved => l_qty_succ_reserved
242 , x_reservation_id => l_org_wide_res_id
243 );
244
245 IF (nvl(x_msg_count,0) = 0) THEN
246 asn_debug.put_line('Created reservation successfully');--RTV2 rtv project phase 2
247 ELSE
248 asn_debug.put_line(' Could not reserve for org : ----> ' || p_wdd_rec.organization_id);
249 asn_debug.put_line(' Could not reserve for item : ----> ' || p_wdd_rec.inventory_item_id);
250 FOR i IN 1..x_msg_count LOOP --RTV2 rtv project phase 2
251 l_message := fnd_msg_pub.get(I, 'F');
252 asn_debug.put_line(substr(l_message,1,255));
253 end LOOP;
254 END IF;
255 EXCEPTION
256 WHEN OTHERS THEN
257 IF (g_asn_debug = 'Y') THEN
258 asn_debug.put_line('Unexpected exception in create_return_reservation : ' || SQLERRM);
259 END IF;
260 raise fnd_api.g_exc_unexpected_error;
261 END create_return_reservation;
262 --
263
264 PROCEDURE relieve_return_reservation
265 ( p_wdd_rec IN WSH_DELIVERY_DETAILS%ROWTYPE,
266 p_lpn_id IN NUMBER, ---- RTV2 rtv project phase 2
267 x_return_status OUT NOCOPY VARCHAR2, ---- RTV2 rtv project phase 2
268 x_msg_count OUT NOCOPY NUMBER, ---- RTV2 rtv project phase 2
269 x_msg_data OUT NOCOPY VARCHAR2) IS ---- RTV2 rtv project phase 2
270
271 --l_return_status VARCHAR2(1);
272 --l_msg_count NUMBER;
273 --l_msg_data VARCHAR2(240);
274 l_ship_qty NUMBER;
275 l_user_line VARCHAR2(30);
276 l_demand_class VARCHAR2(30);
277 l_mps_flag NUMBER;
278 l_message VARCHAR2(2000);
279
280 BEGIN
281
282 IF (g_asn_debug = 'Y') THEN
283 asn_debug.put_line('Relieving reservation against WDD : ' || p_wdd_rec.delivery_detail_id);
284 END IF;
285
286 inv_trx_relief_c_pvt.rsv_relief
287 ( x_return_status => x_return_status,-- RTV2 rtv project phase 2
288 x_msg_count => x_msg_count, -- RTV2 rtv project phase 2
289 x_msg_data => x_msg_data, -- RTV2 rtv project phase 2
290 x_ship_qty => l_ship_qty,
291 x_userline => l_user_line,
292 x_demand_class => l_demand_class,
293 x_mps_flag => l_mps_flag,
294 p_organization_id => p_wdd_rec.organization_id,
295 p_inventory_item_id => p_wdd_rec.inventory_item_id,
296 p_subinv => p_wdd_rec.subinventory,
297 p_locator => p_wdd_rec.locator_id,
298 p_lotnumber => p_wdd_rec.lot_number,
299 p_revision => p_wdd_rec.revision,
300 p_dsrc_type => 13,
301 p_header_id => NULL,
302 p_dsrc_name => p_wdd_rec.source_header_number,
303 p_dsrc_line => NULL,
304 p_dsrc_delivery => NULL,
305 p_qty_at_puom => p_wdd_rec.shipped_quantity,
306 p_lpn_id => p_lpn_id); -- RTV2 rtv project phase 2
307
308 IF (nvl(x_msg_count,0) = 0) THEN -- RTV2 rtv project phase 2
309 asn_debug.put_line('Relieved reservation successfully');
310 ELSE
311 asn_debug.put_line(' Could not relieve reservation for org : ----> ' || p_wdd_rec.organization_id);
312 asn_debug.put_line(' Could not relieve reservation for item : ----> ' || p_wdd_rec.inventory_item_id);
313 FOR i IN 1..x_msg_count LOOP -- RTV2 rtv project phase 2
314 l_message := fnd_msg_pub.get(I, 'F');
315 asn_debug.put_line(substr(l_message,1,255));
316 end LOOP;
317 END IF;
318
319 EXCEPTION
320 WHEN OTHERS THEN
321 IF (g_asn_debug = 'Y') THEN
322 asn_debug.put_line('Unexpected exception in relieve_return_reservation : ' || SQLERRM);
323 END IF;
324 raise fnd_api.g_exc_unexpected_error;
325 END relieve_return_reservation;
326
327 -- RTV2 rtv project phase 2 : start
328 PROCEDURE relieve_return_reservation
329 ( p_wdd_rec IN WSH_DELIVERY_DETAILS%ROWTYPE) IS
330
331 l_lpn_id NUMBER;
332 l_return_status VARCHAR2(1);
333 l_msg_count NUMBER;
334 l_msg_data VARCHAR2(240);
335
336 BEGIN
337 l_lpn_id := get_return_lpn_id(p_wdd_rec.delivery_detail_id);
338
339 relieve_return_reservation(p_wdd_rec => p_wdd_rec,
340 p_lpn_id => l_lpn_id,
341 x_return_status => l_return_status,
342 x_msg_count => l_msg_count,
343 x_msg_data => l_msg_data);
344
345 IF (l_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
346 raise fnd_api.g_exc_unexpected_error;
347 END IF;
348 EXCEPTION
349 WHEN OTHERS THEN
350 IF (g_asn_debug = 'Y') THEN
351 asn_debug.put_line('Unexpected exception in relieve_return_reservation : ' || SQLERRM);
352 END IF;
353 raise fnd_api.g_exc_unexpected_error;
354 END relieve_return_reservation;
355 --
356 PROCEDURE rollback_rtp_fail( p_wdd_rec IN wsh_delivery_details%rowtype,
357 p_group_id IN NUMBER) IS
358 l_lpn_id NUMBER;
359 l_new_rti_id NUMBER;
360 l_new_org_id NUMBER;
361 l_wdd_rec WSH_GLBL_VAR_STRCT_GRP.Delivery_Details_Rec_Type;
362 l_wdd_rec2 wsh_delivery_details%rowtype;
363 l_return_status VARCHAR2(1);
364 l_msg_count NUMBER;
365 l_msg_data VARCHAR2(2000);
366 BEGIN
367 SELECT lpn_id,
368 interface_transaction_id,
369 to_organization_id
370 INTO l_lpn_id,
371 l_new_rti_id,
372 l_new_org_id
373 FROM rcv_transactions_interface
374 WHERE group_id = p_group_id
375 AND interface_source_line_id = p_wdd_rec.delivery_detail_id;
376
377 --re-create MR
378 IF (l_new_org_id = p_wdd_rec.organization_id) THEN
379 l_wdd_rec.organization_id := p_wdd_rec.organization_id;
380 l_wdd_rec.inventory_item_id := p_wdd_rec.inventory_item_id;
381 l_wdd_rec.source_header_number := p_wdd_rec.source_header_number;
382 l_wdd_rec.src_requested_quantity := p_wdd_rec.src_requested_quantity;
383 l_wdd_rec.src_requested_quantity_uom := p_wdd_rec.src_requested_quantity_uom;
384 l_wdd_rec.revision := p_wdd_rec.revision;
385 l_wdd_rec.subinventory := p_wdd_rec.subinventory;
386 l_wdd_rec.locator_id := p_wdd_rec.locator_id;
387 l_wdd_rec.lot_number := p_wdd_rec.lot_number;
388 create_return_reservation (p_wdd_rec => l_wdd_rec,
389 p_lpn_id => l_lpn_id,
390 x_return_status => l_return_status,
391 x_msg_count => l_msg_count,
392 x_msg_data => l_msg_data);
393 IF (l_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
394 raise fnd_api.g_exc_unexpected_error;
395 END IF;
396 END IF;
397
398 --unmark against new rti id
399 IF (l_lpn_id IS NOT NULL) THEN
400 l_wdd_rec2.source_line_id := l_new_rti_id;
401 l_wdd_rec2.organization_id := l_new_org_id;
402 l_wdd_rec2.inventory_item_id := p_wdd_rec.inventory_item_id;
403 l_wdd_rec2.revision := p_wdd_rec.revision;
404 l_wdd_rec2.lot_number := p_wdd_rec.lot_number;
405 unmark_wdd_lpn(p_wdd_rec => l_wdd_rec2,
406 p_lpn_id => l_lpn_id,
407 x_return_status => l_return_status,
408 x_msg_count => l_msg_count,
409 x_msg_data => l_msg_data);
410 IF (l_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
411 raise fnd_api.g_exc_unexpected_error;
412 END IF;
413 END IF;
414 EXCEPTION
415 WHEN OTHERS THEN
416 IF (g_asn_debug = 'Y') THEN
417 asn_debug.put_line('Unexpected exception in rollback_rtp_fail : ' || SQLERRM);
418 END IF;
419 raise fnd_api.g_exc_unexpected_error;
420 END;
421 --this procedure is used for RTV in receipt org only.
422 PROCEDURE marklpn_rtp_fail( p_group_id IN NUMBER) IS
423 CURSOR remark_lpns IS
424 SELECT rtv_rti.interface_transaction_id,
425 rtv_rti.transaction_type,
426 rtv_rti.item_id,
427 rtv_rti.item_revision,
428 rtv_rti.use_mtl_serial,
429 rtv_rti.use_mtl_lot,
430 rtv_rti.to_organization_id,
431 new_rti.from_subinventory,
432 new_rti.from_locator_id,
433 new_rti.transfer_lpn_id,
434 new_rti.uom_code,
435 sum(new_rti.quantity) quantity
436 FROM rcv_transactions_interface new_rti,
437 rcv_transactions_interface rtv_rti,
438 wsh_delivery_Details wdd
439 WHERE new_rti.group_id = p_group_id
440 AND new_rti.interface_source_line_id IS NOT NULL
441 AND new_rti.transfer_lpn_id IS NOT NULL
442 AND new_rti.processing_mode_code = 'ONLINE'
443 AND new_rti.interface_source_line_id = wdd.delivery_detail_id
444 AND rtv_rti.interface_transaction_id = wdd.source_line_id
445 AND rtv_rti.group_id = wdd.source_header_id
446 AND rtv_rti.processing_status_code = 'WSH_INTERFACED'
447 AND wdd.source_code = 'RTV'
448 AND wdd.container_flag = 'N'
449 GROUP BY rtv_rti.interface_transaction_id, rtv_rti.transaction_type,new_rti.transfer_lpn_id,
450 rtv_rti.item_id,rtv_rti.item_revision,rtv_rti.use_mtl_serial, rtv_rti.use_mtl_lot,
451 rtv_rti.to_organization_id, new_rti.from_subinventory,new_rti.from_locator_id,
452 new_rti.uom_code;
453 l_return_status VARCHAR2(1);
454 l_msg_count NUMBER;
455 l_msg_data VARCHAR2(2000);
456 l_message VARCHAR2(2000);
457 BEGIN
458 FOR mark_rec IN remark_lpns LOOP
459 --re-mark with master rti id
460 wms_return_sv.MARK_RETURNS(
461 x_return_status => l_return_status,
462 x_msg_count => l_msg_count,
463 x_msg_data => l_msg_data,
464 p_rcv_trx_interface_id => mark_rec.interface_transaction_id,
465 p_ret_transaction_type => mark_rec.transaction_type,
466 p_lpn_id => mark_rec.transfer_lpn_id,
467 p_item_id => mark_rec.item_id,
468 p_item_revision => mark_rec.item_revision,
469 p_quantity => mark_rec.quantity,
470 p_uom => mark_rec.uom_code,
471 p_serial_controlled => mark_rec.use_mtl_serial,
472 p_lot_controlled => mark_rec.use_mtl_lot,
473 p_org_id => mark_rec.to_organization_id,
474 p_subinventory => mark_rec.from_subinventory,
475 p_locator_id => mark_rec.from_locator_id);
476
477 IF (l_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
478 asn_debug.put_line(' Could not remark lpn : ----> ' || mark_rec.transfer_lpn_id);
479 asn_debug.put_line(' Could not remark for item : ----> ' || mark_rec.item_id);
480 FOR i IN 1..l_msg_count LOOP
481 l_message := fnd_msg_pub.get(i, 'F');
482 asn_debug.put_line(substr(l_message,1,255));
483 end LOOP;
484 raise fnd_api.g_exc_unexpected_error;
485 END IF;
486 END LOOP;
487 EXCEPTION
488 WHEN OTHERS THEN
489 IF (g_asn_debug = 'Y') THEN
490 asn_debug.put_line('Unexpected exception in marklpn_rtp_fail : ' || SQLERRM);
491 END IF;
492 raise fnd_api.g_exc_unexpected_error;
493 END;
494 -- RTV2 rtv project phase 2 : end
495 /*===========================================================================
496
497 PROCEDURE NAME: create_delivery_details()
498
499 ===========================================================================*/
500
501 PROCEDURE create_delivery_details
502 ( p_return_org_id IN NUMBER,
503 p_interface_txn_id IN NUMBER,
504 p_use_mtl_lot IN NUMBER,
505 p_use_mtl_serial IN NUMBER,
506 p_ship_to IN NUMBER,
507 p_site_use IN NUMBER
508 ) IS
509
510 CURSOR lot_cursor IS
511 SELECT *
512 FROM mtl_transaction_lots_temp
513 WHERE transaction_temp_id = p_interface_txn_id;
514
515 l_progress VARCHAR2(3);
516 rti_rec RCV_TRANSACTIONS_INTERFACE%ROWTYPE;
517 l_primary_uom VARCHAR2(25);
518 l_price NUMBER;
519 l_price_in_fc NUMBER;
520 l_currency VARCHAR2(3);
521 l_functional_currency VARCHAR2(3);
522 l_currency_conv_type VARCHAR2(30);
523 l_rate NUMBER;
524 l_sob_id NUMBER;
525 l_wdd_tbl WSH_GLBL_VAR_STRCT_GRP.delivery_details_Attr_tbl_Type;
526 l_msg_count NUMBER;
527 l_msg_data VARCHAR2(2000);
528 l_IN_rec WSH_GLBL_VAR_STRCT_GRP.detailInRecType;
529 l_OUT_rec WSH_GLBL_VAR_STRCT_GRP.detailOutRecType;
530 l_return_status VARCHAR2(2000);
531 l_new_temp_id NUMBER;
532 e_wdd_creation_error EXCEPTION;
533 e_location_error EXCEPTION;
534 e_con_wdd_creation_error EXCEPTION; --RTV2 rtv project phase 2
535 e_mr_creation_error EXCEPTION; --RTV2 rtv project phase 2
536 l_subinventory varchar2(30); --RTV2 rtv project phase 2
537 l_locator_id number; --RTV2 rtv project phase 2
538
539
540 BEGIN
541
542 IF (g_asn_debug = 'Y') THEN
543 asn_debug.put_line('Entering create_delivery_details');
544 END IF;
545
546 l_progress := '000';
547 SELECT *
548 INTO rti_rec
549 FROM rcv_transactions_interface
550 WHERE interface_transaction_id = p_interface_txn_id;
551
552 l_progress := '010';
553 l_IN_rec.caller := 'WSH_PUB';
554 l_IN_rec.action_code := 'CREATE';
555
556 -- Load rti data into wdd table structure
557 l_wdd_tbl(1).source_code := 'RTV';
558 l_wdd_tbl(1).source_header_number := rti_rec.group_id;
559 l_wdd_tbl(1).source_header_id := rti_rec.group_id;
560 l_wdd_tbl(1).source_line_id := rti_rec.interface_transaction_id;
561 l_wdd_tbl(1).po_shipment_line_id := rti_rec.shipment_line_id;
562 l_wdd_tbl(1).inventory_item_id := rti_rec.item_id;
563 l_wdd_tbl(1).item_description := rti_rec.item_description;
564 l_wdd_tbl(1).revision := rti_rec.item_revision;
565 l_wdd_tbl(1).original_revision := rti_rec.item_revision;
566 l_wdd_tbl(1).src_requested_quantity := rti_rec.quantity;
567 l_wdd_tbl(1).src_requested_quantity_uom := rti_rec.uom_code;
568 l_wdd_tbl(1).src_requested_quantity2 := rti_rec.secondary_quantity;
569 l_wdd_tbl(1).src_requested_quantity_uom2 := rti_rec.secondary_uom_code;
570 l_wdd_tbl(1).requested_quantity2 := rti_rec.secondary_quantity; -- Bug 12768069
571 l_wdd_tbl(1).requested_quantity_uom2 := rti_rec.secondary_uom_code; -- Bug 12768069
572 -- RTV2 rtv project phase 2 : start
573 -- Since to_lpn may have different sub/loc from the ones on RTIs,
574 -- need to fetch them from to_lpn
575 IF (rti_rec.TRANSFER_LPN_ID IS NOT NULL) THEN
576 select subinventory_code,locator_id
577 into l_subinventory, l_locator_id
578 from wms_license_plate_numbers
579 where lpn_id = rti_rec.TRANSFER_LPN_ID;
580 ELSE
581 l_subinventory := rti_rec.from_subinventory;
582 l_locator_id := rti_rec.from_locator_id;
583 END IF;
584 l_wdd_tbl(1).subinventory := l_subinventory;
585 l_wdd_tbl(1).original_subinventory := l_subinventory;
586 l_wdd_tbl(1).locator_id := l_locator_id;
587 l_wdd_tbl(1).original_locator_id := l_locator_id;
588 -- RTV2 rtv project phase 2 : end
589 l_wdd_tbl(1).date_requested := rti_rec.transaction_date;
590 l_wdd_tbl(1).date_scheduled := rti_rec.transaction_date;
591 l_wdd_tbl(1).created_by := rti_rec.created_by;
592 l_wdd_tbl(1).creation_date := rti_rec.creation_date;
593 l_wdd_tbl(1).last_update_date := rti_rec.last_update_date;
594 l_wdd_tbl(1).last_update_login := rti_rec.last_update_login;
595 l_wdd_tbl(1).last_updated_by := rti_rec.last_updated_by;
596 l_wdd_tbl(1).consignee_flag := 'V';
597 l_wdd_tbl(1).customer_id := rti_rec.vendor_id;
598 l_wdd_tbl(1).organization_id := p_return_org_id;
599 l_wdd_tbl(1).org_id := rti_rec.org_id;
600 l_wdd_tbl(1).released_status := 'X';
601 l_wdd_tbl(1).inv_interfaced_flag := 'N';
602 l_wdd_tbl(1).oe_interfaced_flag := 'X';
603 l_wdd_tbl(1).container_flag := 'N';
604 l_wdd_tbl(1).pickable_flag := 'N';
605 l_wdd_tbl(1).wv_frozen_flag := 'N';
606 l_wdd_tbl(1).ship_to_location_id := p_ship_to;
607 l_wdd_tbl(1).ship_to_site_use_id := p_site_use;
608
609 l_progress := '020';
610 BEGIN
611 SELECT substr (nvl(max(to_number(source_line_number)),0.1)+1, 1, instr(nvl(max(to_number(source_line_number)),0.1)+1,'.')-1) || '.1'
612 INTO l_wdd_tbl(1).source_line_number
613 FROM wsh_delivery_details
614 WHERE source_header_number = to_char(rti_rec.group_id)
615 AND source_code = 'RTV';
616 EXCEPTION
617 WHEN OTHERS THEN
618 l_wdd_tbl(1).source_line_number := '1.1';
619 END;
620
621 l_progress := '030';
622 SELECT invoice_currency_code
623 INTO l_currency
624 FROM ap_supplier_sites_all
625 WHERE vendor_id = rti_rec.vendor_id
626 AND vendor_site_id = rti_rec.vendor_site_id;
627
628 l_progress := '040';
629 wsh_util_core.get_location_id ('ORG', p_return_org_id, l_wdd_tbl(1).ship_from_location_id, l_return_status);
630
631 IF (l_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
632 raise e_location_error;
633 END IF;
634
635 l_progress := '050';
636 l_return_status := NULL;
637 IF (rti_rec.shipment_line_id IS NULL) THEN
638
639 SELECT currency_code, set_of_books_id
640 INTO l_functional_currency, l_sob_id
641 FROM cst_organization_definitions
642 WHERE organization_id = p_return_org_id;
643
644 l_price_in_fc := INV_CYC_LOVS.get_item_cost
645 ( in_org_id => p_return_org_id,
646 in_item_id => rti_rec.item_id,
647 in_locator_id => rti_rec.from_locator_id);
648
649 IF (g_asn_debug = 'Y') THEN
650 asn_debug.put_line('l_functional_currency : ' || l_functional_currency);
651 asn_debug.put_line('l_price_in_fc : ' || l_price_in_fc);
652 END IF;
653
654 IF (l_currency IS NOT NULL AND l_currency <> l_functional_currency) THEN
655 l_progress := '055';
656 fnd_profile.get('IC_CURRENCY_CONVERSION_TYPE', l_currency_conv_type);
657
658 l_rate := po_core_s.get_conversion_rate
659 ( l_sob_id,
660 l_currency,
661 rti_rec.transaction_date,
662 l_currency_conv_type );
663
664 IF (g_asn_debug = 'Y') THEN
665 asn_debug.put_line('l_currency_conv_type : ' || l_currency_conv_type);
666 asn_debug.put_line('l_rate : ' || l_rate);
667 END IF;
668
669 l_price := l_price_in_fc * l_rate ;
670
671 ELSE
672 l_progress := '060';
673 l_currency := l_functional_currency;
674 l_price := l_price_in_fc;
675 END IF;
676
677 ELSE
678 l_progress := '070';
679 l_currency := rti_rec.currency_code;
680
681 SELECT NVL (pll.price_override, pol.unit_price)
682 INTO l_price
683 FROM po_line_locations_all pll,
684 po_lines_all pol
685 WHERE pol.po_line_id = pll.po_line_id
686 AND pol.po_line_id = rti_rec.po_line_id
687 AND pll.line_location_id = rti_rec.po_line_location_id;
688 END IF;
689
690 l_wdd_tbl(1).currency_code := l_currency;
691 l_wdd_tbl(1).unit_price := l_price;
692
693 IF (g_asn_debug = 'Y') THEN
694 asn_debug.put_line('currency : ' || l_wdd_tbl(1).currency_code);
695 asn_debug.put_line('unit_price : ' || l_wdd_tbl(1).unit_price);
696 END IF;
697
698 l_progress := '080';
699
700 SELECT msi.primary_uom_code,
701 msi.primary_unit_of_measure,
702 msi.weight_uom_code,
703 msi.unit_weight,
704 wsh_wv_utils.convert_uom
705 (msi.weight_uom_code,
706 msi.weight_uom_code,
707 (msi.unit_weight * wsh_wv_utils.convert_uom( rti_rec.uom_code,
708 msi.primary_uom_code,
709 rti_rec.quantity,
710 rti_rec.item_id) ),
711 rti_rec.item_id) WEIGHT,
712 msi.volume_uom_code,
713 msi.unit_volume,
714 wsh_wv_utils.convert_uom
715 (msi.volume_uom_code,
716 msi.volume_uom_code,
717 (msi.unit_volume * wsh_wv_utils.convert_uom( rti_rec.uom_code,
718 msi.primary_uom_code,
719 rti_rec.quantity,
720 rti_rec.item_id) ),
721 rti_rec.item_id) VOLUME
722 INTO l_wdd_tbl(1).requested_quantity_uom,
723 l_primary_uom,
724 l_wdd_tbl(1).weight_uom_code,
725 l_wdd_tbl(1).unit_weight,
726 l_wdd_tbl(1).net_weight,
727 l_wdd_tbl(1).volume_uom_code,
728 l_wdd_tbl(1).unit_volume,
729 l_wdd_tbl(1).volume
730 FROM mtl_system_items msi
731 WHERE msi.inventory_item_id = rti_rec.item_id
732 AND msi.organization_id = p_return_org_id;
733
734 l_wdd_tbl(1).gross_weight := l_wdd_tbl(1).net_weight;
735
736 IF (g_asn_debug = 'Y') THEN
737 asn_debug.put_line('l_wdd_tbl(1).requested_quantity_uom : ' || l_wdd_tbl(1).requested_quantity_uom);
738 asn_debug.put_line('l_wdd_tbl(1).weight_uom_code : ' || l_wdd_tbl(1).weight_uom_code);
739 asn_debug.put_line('l_wdd_tbl(1).unit_weight : ' || l_wdd_tbl(1).unit_weight);
740 asn_debug.put_line('l_wdd_tbl(1).net_weight : ' || l_wdd_tbl(1).net_weight);
741 asn_debug.put_line('l_wdd_tbl(1).gross_weight : ' || l_wdd_tbl(1).gross_weight);
742 asn_debug.put_line('l_wdd_tbl(1).volume_uom_code : ' || l_wdd_tbl(1).volume_uom_code);
743 asn_debug.put_line('l_wdd_tbl(1).unit_volume : ' || l_wdd_tbl(1).unit_volume);
744 asn_debug.put_line('l_wdd_tbl(1).volume : ' || l_wdd_tbl(1).volume);
745 END IF;
746
747 l_progress := '090';
748 IF (p_use_mtl_lot <> 2) THEN
749 l_progress := '100';
750 l_return_status := NULL;
751 l_msg_data := NULL;
752 l_msg_count := NULL;
753
754 IF (rti_rec.uom_code <> l_wdd_tbl(1).requested_quantity_uom) THEN
755 po_uom_s.uom_convert
756 ( from_quantity => rti_rec.quantity,
757 from_uom => rti_rec.unit_of_measure,
758 item_id => rti_rec.item_id,
759 to_uom => l_primary_uom,
760 to_quantity => l_wdd_tbl(1).requested_quantity);
761 ELSE
762 l_wdd_tbl(1).requested_quantity := rti_rec.quantity;
763 END IF;
764
765 IF (p_use_mtl_serial in (2,5)) THEN
766 l_wdd_tbl(1).shipped_quantity := l_wdd_tbl(1).requested_quantity;
767 l_wdd_tbl(1).transaction_temp_id := p_interface_txn_id;
768 END IF;
769
770 wsh_interface_grp.create_update_delivery_detail
771 ( p_api_version_number => 1.0,
772 p_init_msg_list => FND_API.G_TRUE,
773 p_commit => NULL,
774 x_return_status => l_return_status,
775 x_msg_count => l_msg_count,
776 x_msg_data => l_msg_data,
777 p_detail_info_tab => l_wdd_tbl,
778 p_IN_rec => l_IN_rec,
779 x_OUT_rec => l_OUT_rec );
780
781 IF (l_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
782 l_progress := '120';
783 raise e_wdd_creation_error;
784 END IF;
785
786 --RTV2 rtv project phase 2 : start
787 --Calling wms api To create container wdd and assignment
788 --We need delivery_detail_id to call wms api and create reservation
789 l_wdd_tbl(1).delivery_detail_id := l_OUT_rec.detail_ids(1);
790 IF (rti_rec.TRANSFER_LPN_ID IS NOT NULL) THEN
791
792 wms_return_sv.Create_Update_Containers_RTV
793 ( x_return_status => l_return_status,
794 x_msg_count => l_msg_count,
795 x_msg_data => l_msg_data,
796 p_interface_txn_id => rti_rec.interface_transaction_id,
797 p_wdd_table => l_wdd_tbl);
798
799 END IF;
800
801 IF (l_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
802 l_progress := '130';
803 raise e_con_wdd_creation_error;
804 END IF;
805
806 l_progress := '150';
807 create_return_reservation (p_wdd_rec => l_wdd_tbl(1),
808 p_lpn_id => rti_rec.transfer_lpn_id,
809 x_return_status => l_return_status,
810 x_msg_count => l_msg_count,
811 x_msg_data => l_msg_data);
812
813 IF (l_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
814 l_progress := '160';
815 raise e_mr_creation_error;
816 END IF;
817 l_wdd_tbl(1).delivery_detail_id := NULL;
818 --RTV2 rtv project phase 2 : end
819
820 ELSE
821 l_progress := '200';
822 FOR lot_rec IN lot_cursor LOOP
823
824 l_wdd_tbl(1).lot_number := lot_rec.lot_number;
825 l_wdd_tbl(1).original_lot_number := lot_rec.lot_number;
826 l_wdd_tbl(1).transaction_temp_id := lot_rec.serial_transaction_temp_id;
827
828 IF (rti_rec.uom_code <> l_wdd_tbl(1).requested_quantity_uom) THEN
829 po_uom_s.uom_convert
830 ( from_quantity => lot_rec.transaction_quantity,
831 from_uom => rti_rec.unit_of_measure,
832 item_id => rti_rec.item_id,
833 to_uom => l_primary_uom,
834 to_quantity => l_wdd_tbl(1).requested_quantity);
835 ELSE
836 l_wdd_tbl(1).requested_quantity := lot_rec.transaction_quantity;
837 END IF;
838
839 IF (p_use_mtl_serial in (2,5)) THEN
840 l_wdd_tbl(1).shipped_quantity := l_wdd_tbl(1).requested_quantity;
841 END IF;
842
843 wsh_interface_grp.create_update_delivery_detail
844 ( p_api_version_number => 1.0,
845 p_init_msg_list => NULL,
846 p_commit => NULL,
847 x_return_status => l_return_status,
848 x_msg_count => l_msg_count,
849 x_msg_data => l_msg_data,
850 p_detail_info_tab => l_wdd_tbl,
851 p_IN_rec => l_IN_rec,
852 x_OUT_rec => l_OUT_rec );
853
854 IF (l_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
855 l_progress := '250';
856 raise e_wdd_creation_error;
857 END IF;
858
859 --RTV2 rtv project phase 2 : start
860 --Calling wms api To create container wdd and assignment
861 --We need delivery_detail_id to call wms api and create reservation
862 l_wdd_tbl(1).delivery_detail_id := l_OUT_rec.detail_ids(1);
863 IF (rti_rec.TRANSFER_LPN_ID IS NOT NULL) THEN
864
865 wms_return_sv.Create_Update_Containers_RTV
866 ( x_return_status => l_return_status,
867 x_msg_count => l_msg_count,
868 x_msg_data => l_msg_data,
869 p_interface_txn_id => rti_rec.interface_transaction_id,
870 p_wdd_table => l_wdd_tbl);
871
872 END IF;
873
874 IF (l_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
875 l_progress := '260';
876 raise e_con_wdd_creation_error;
877 END IF;
878
879 l_progress := '270';
880
881 create_return_reservation (p_wdd_rec => l_wdd_tbl(1),
882 p_lpn_id => rti_rec.TRANSFER_LPN_ID,
883 x_return_status => l_return_status,
884 x_msg_count => l_msg_count,
885 x_msg_data => l_msg_data);
886
887 IF (l_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
888 l_progress := '280';
889 raise e_mr_creation_error;
890 END IF;
891 l_wdd_tbl(1).delivery_detail_id := NULL;
892 --RTV2 rtv project phase 2 : end
893 END LOOP;
894
895 END IF;
896
897 l_progress := '300';
898 IF (g_asn_debug = 'Y') THEN
899 asn_debug.put_line('Leaving create_delivery_details');
900 END IF;
901
902 EXCEPTION
903 WHEN e_location_error THEN
904 IF (g_asn_debug = 'Y') THEN
905 asn_debug.put_line('e_wsh_loc_error in create_delivery_details : ' || l_progress);
906 END IF;
907
908 fnd_msg_pub.count_and_get (p_encoded => 'T',
909 p_count => l_msg_count,
910 p_data => l_msg_data
911 );
912
913 FOR x IN 1 .. l_msg_count LOOP
914 l_msg_data := fnd_msg_pub.get (x, 'F');
915 END LOOP;
916
917 po_message_s.sql_error('rcv_wsh_interface_pkg.create_delivery_details', l_msg_data, sqlcode);
918 raise fnd_api.g_exc_error;
919
920 WHEN e_wdd_creation_error THEN
921 IF (g_asn_debug = 'Y') THEN
922 asn_debug.put_line('e_wdd_creation_error in create_delivery_details : ' || l_progress);
923 END IF;
924 l_msg_data := fnd_msg_pub.get (1, 'F');
925 po_message_s.sql_error('rcv_wsh_interface_pkg.create_delivery_details', l_msg_data, sqlcode);
926 raise fnd_api.g_exc_error;
927
928 --RTV2 rtv project phase 2 : start
929 WHEN e_con_wdd_creation_error THEN
930 IF (g_asn_debug = 'Y') THEN
931 asn_debug.put_line('e_con_wdd_creation_error in create_delivery_details : ' || l_progress);
932 END IF;
933 fnd_msg_pub.count_and_get (p_encoded => 'T',
934 p_count => l_msg_count,
935 p_data => l_msg_data
936 );
937
938 FOR x IN 1 .. l_msg_count LOOP
939 l_msg_data := fnd_msg_pub.get (x, 'F');
940 END LOOP;
941 po_message_s.sql_error('rcv_wsh_interface_pkg.create_delivery_details', l_msg_data, sqlcode);
942 raise fnd_api.g_exc_error;
943
944 WHEN e_mr_creation_error THEN
945 IF (g_asn_debug = 'Y') THEN
946 asn_debug.put_line('e_mr_creation_error in create_delivery_details : ' || l_progress);
947 END IF;
948 fnd_msg_pub.count_and_get (p_encoded => 'T',
949 p_count => l_msg_count,
950 p_data => l_msg_data
951 );
952
953 FOR x IN 1 .. l_msg_count LOOP
954 l_msg_data := fnd_msg_pub.get (x, 'F');
955 END LOOP;
956 po_message_s.sql_error('rcv_wsh_interface_pkg.create_delivery_details', l_msg_data, sqlcode);
957 raise fnd_api.g_exc_error;
958 --RTV2 rtv project phase 2 : end
959
960 WHEN OTHERS THEN
961 IF (g_asn_debug = 'Y') THEN
962 asn_debug.put_line('Unexpected exception in create_delivery_details : ' || SQLERRM);
963 asn_debug.put_line('l_progress : ' || l_progress);
964 END IF;
965 po_message_s.sql_error('rcv_wsh_interface_pkg.create_delivery_details', 'Unexpected exception', sqlcode);
966 raise fnd_api.g_exc_unexpected_error;
967 END create_delivery_details;
968
969 /*===========================================================================
970
971 PROCEDURE NAME: interface_to_rcv()
972
973 ===========================================================================*/
974
975 PROCEDURE interface_to_rcv (p_delivery_id IN NUMBER, p_return_status OUT NOCOPY VARCHAR2) IS
976
977 -- Cursor for picking Returns agaist no document
978 CURSOR wdd_cursor_1 IS
979 SELECT wdd.*
980 FROM wsh_delivery_details wdd,
981 wsh_delivery_assignments wda,
982 rcv_transactions_interface rti
983 WHERE wda.delivery_detail_id = wdd.delivery_detail_id
984 AND wda.delivery_id = p_delivery_id
985 AND wdd.source_code = 'RTV'
986 AND wdd.released_status = 'C'
987 AND wdd.inv_interfaced_flag <> 'Y'
988 AND wdd.container_flag = 'N'
989 AND wdd.source_line_id = rti.interface_transaction_id
990 AND rti.processing_status_code = 'WSH_INTERFACED'
991 AND wdd.organization_id = rti.to_organization_id
992 AND rti.shipment_line_id IS NULL
993 ORDER BY source_line_id, source_line_number;
994
995 -- Cursor for picking Returns made from the Receipt's org
996 CURSOR wdd_cursor_2 IS
997 SELECT wdd.*
998 FROM wsh_delivery_details wdd,
999 wsh_delivery_assignments wda,
1000 rcv_transactions_interface rti
1001 WHERE wda.delivery_detail_id = wdd.delivery_detail_id
1002 AND wda.delivery_id = p_delivery_id
1003 AND wdd.source_code = 'RTV'
1004 AND wdd.released_status = 'C'
1005 AND wdd.inv_interfaced_flag <> 'Y'
1006 AND wdd.container_flag = 'N'
1007 AND wdd.source_line_id = rti.interface_transaction_id
1008 AND rti.processing_status_code = 'WSH_INTERFACED'
1009 AND wdd.organization_id = rti.to_organization_id
1010 AND rti.shipment_line_id IS NOT NULL
1011 AND NOT EXISTS (SELECT 1 from rcv_transactions rt
1012 WHERE rt.transaction_type = 'RETURN TO VENDOR'
1013 AND rt.interface_source_line_id = wdd.delivery_detail_id)
1014 ORDER BY source_line_id, source_line_number;
1015
1016 -- Cursor for picking Returns made from an org different from Receipt's org for Direct org txr
1017 CURSOR wdd_cursor_3 IS
1018 SELECT wdd.*
1019 FROM wsh_delivery_details wdd,
1020 wsh_delivery_assignments wda,
1021 rcv_transactions_interface rti
1022 WHERE wda.delivery_detail_id = wdd.delivery_detail_id
1023 AND wda.delivery_id = p_delivery_id
1024 AND wdd.source_code = 'RTV'
1025 AND wdd.released_status = 'C'
1026 AND wdd.inv_interfaced_flag <> 'Y'
1027 AND wdd.container_flag = 'N'
1028 AND wdd.source_line_id = rti.interface_transaction_id
1029 AND rti.processing_status_code = 'WSH_INTERFACED'
1030 AND wdd.organization_id <> rti.to_organization_id
1031 AND rti.shipment_line_id IS NOT NULL
1032 AND NOT EXISTS (SELECT 1
1033 FROM mtl_material_transactions mmt
1034 WHERE mmt.picking_line_id = wdd.delivery_detail_id)
1035 ORDER BY source_line_id, source_line_number;
1036
1037 -- Cursor for picking Returns made from an org different from Receipt's org which are pending after DIrect org txr.
1038 CURSOR wdd_cursor_4 IS
1039 SELECT wdd.*
1040 FROM wsh_delivery_details wdd,
1041 wsh_delivery_assignments wda,
1042 rcv_transactions_interface rti
1043 WHERE wda.delivery_detail_id = wdd.delivery_detail_id
1044 AND wda.delivery_id = p_delivery_id
1045 AND wdd.source_code = 'RTV'
1046 AND wdd.released_status = 'C'
1047 AND wdd.inv_interfaced_flag <> 'Y'
1048 AND wdd.container_flag = 'N'
1049 AND wdd.source_line_id = rti.interface_transaction_id
1050 AND rti.processing_status_code = 'WSH_INTERFACED'
1051 AND wdd.organization_id <> rti.to_organization_id
1052 AND rti.shipment_line_id IS NOT NULL
1053 AND EXISTS (SELECT 1
1054 FROM mtl_material_transactions mmt
1055 WHERE mmt.picking_line_id = wdd.delivery_detail_id)
1056 AND NOT EXISTS (SELECT 1 from rcv_transactions rt
1057 WHERE rt.transaction_type = 'RETURN TO VENDOR'
1058 AND rt.interface_source_line_id = wdd.delivery_detail_id)
1059 ORDER BY source_line_id, source_line_number;
1060
1061 -- Cursors for picking wdds that should be updated as inv_interfaced.
1062 CURSOR wdd_cursor_5 IS
1063 SELECT wdd.delivery_detail_id
1064 FROM wsh_delivery_details wdd,
1065 wsh_delivery_assignments wda,
1066 rcv_transactions rt
1067 WHERE wda.delivery_detail_id = wdd.delivery_detail_id
1068 AND wda.delivery_id = p_delivery_id
1069 AND wdd.source_code = 'RTV'
1070 AND wdd.released_status = 'C'
1071 AND wdd.inv_interfaced_flag <> 'Y'
1072 AND wdd.container_flag = 'N'
1073 AND wdd.delivery_detail_id = rt.interface_source_line_id
1074 AND rt.transaction_type = 'RETURN TO VENDOR'
1075 FOR UPDATE OF inv_interfaced_flag nowait;
1076
1077 CURSOR wdd_cursor_6 IS
1078 SELECT wdd.delivery_detail_id
1079 FROM wsh_delivery_details wdd,
1080 wsh_delivery_assignments wda,
1081 mtl_material_transactions mmt
1082 WHERE wda.delivery_detail_id = wdd.delivery_detail_id
1083 AND wda.delivery_id = p_delivery_id
1084 AND wdd.source_code = 'RTV'
1085 AND wdd.released_status = 'C'
1086 AND wdd.inv_interfaced_flag <> 'Y'
1087 AND wdd.container_flag = 'N'
1088 AND wdd.delivery_detail_id = mmt.picking_line_id
1089 AND mmt.transaction_type_id = 1005
1090 FOR UPDATE OF inv_interfaced_flag nowait;
1091
1092 l_header_id NUMBER;
1093 l_group_id NUMBER;
1094 l_INVTM_status VARCHAR2(2);
1095 l_RCVTM_status VARCHAR2(2);
1096 l_case_1_status VARCHAR2(1) := 'S';
1097 l_case_2_status VARCHAR2(1) := 'S';
1098 l_case_3_status VARCHAR2(1) := 'S';
1099 l_case_4_status VARCHAR2(1) := 'S';
1100 l_case_5_status VARCHAR2(1) := 'S';
1101 l_return_status VARCHAR2(1) := null;
1102 l_index NUMBER:= 0;
1103 l_detail_rows wsh_util_core.id_tab_type;
1104
1105 BEGIN
1106 IF (g_asn_debug = 'Y') THEN
1107 asn_debug.put_line('Inside interface_to_rcv : p_delivery_id => ' || p_delivery_id);
1108 END IF;
1109
1110 ----------------------------------------- Return agaist no document ---------------------------
1111 BEGIN
1112
1113 SAVEPOINT SP_InvTM;
1114 --
1115 l_header_id := NULL;
1116 l_INVTM_status := NULL;
1117
1118 FOR wdd_rec IN wdd_cursor_1 LOOP
1119 IF ( l_header_id IS NULL) THEN
1120 IF (g_asn_debug = 'Y') THEN
1121 asn_debug.put_line('-------------------------------------------');
1122 asn_debug.put_line(' Processing Returns without receipts');
1123 asn_debug.put_line('-------------------------------------------');
1124 END IF;
1125 SELECT mtl_material_transactions_s.nextval INTO l_header_id FROM DUAL; -- Bug 11831232
1126 END IF;
1127 load_mtl_interfaces ('Issue out', wdd_rec, l_header_id, p_delivery_id);
1128 END LOOP;
1129
1130 IF (l_header_id IS NOT NULL) THEN
1131 process_txn(l_header_id, l_INVTM_status);
1132
1133 IF (l_INVTM_status = 'S') THEN
1134 perform_post_TM_updates ('INV', p_delivery_id);
1135 COMMIT;
1136 IF (g_asn_debug = 'Y') THEN
1137 asn_debug.put_line ('Returns without receipts processed successfully');
1138 END IF;
1139
1140 ELSE
1141 ROLLBACK TO SP_InvTM;
1142 l_case_1_status := l_INVTM_status;
1143 END IF;
1144 END IF;
1145 asn_debug.put_line(' l_case_1_status => ' || l_case_1_status);
1146 --
1147 EXCEPTION
1148 WHEN OTHERS THEN
1149 IF (g_asn_debug = 'Y') THEN
1150 asn_debug.put_line ('Case1 Unexpected exception : ' || SQLERRM);
1151 END IF;
1152 l_case_1_status := 'E';
1153 ROLLBACK TO SP_InvTM;
1154 END;
1155
1156 ----------------------------------------- Return from Receipt org ---------------------------
1157 BEGIN
1158
1159 SAVEPOINT SP_RcvTM;
1160 --
1161 l_group_id := NULL;
1162 l_RCVTM_status := NULL;
1163
1164 FOR wdd_rec IN wdd_cursor_2 LOOP
1165 IF ( l_group_id IS NULL) THEN
1166 IF (g_asn_debug = 'Y') THEN
1167 asn_debug.put_line('-------------------------------------------');
1168 asn_debug.put_line(' Processing Returns in the Receipt org');
1169 asn_debug.put_line('-------------------------------------------');
1170 END IF;
1171 SELECT rcv_interface_groups_s.nextval INTO l_group_id FROM DUAL; -- Bug 11831232
1172 END IF;
1173 load_rcv_interfaces (p_delivery_id, wdd_rec,l_group_id);
1174 relieve_return_reservation (wdd_rec);
1175 END LOOP;
1176
1177 IF (l_group_id IS NOT NULL) THEN
1178 COMMIT;
1179 invoke_rtp (l_group_id, l_RCVTM_status);
1180
1181 IF (l_RCVTM_status = 'S') THEN
1182 perform_post_TM_updates ('RCV', p_delivery_id);
1183 IF (g_asn_debug = 'Y') THEN
1184 asn_debug.put_line ('Returns within receipts org processed successfully');
1185 END IF;
1186 ELSE
1187 --RTV2 rtv project phase 2 : start
1188 FOR wdd_rec IN wdd_cursor_2 LOOP
1189 rollback_rtp_fail(wdd_rec, l_group_id);
1190 END LOOP;
1191 marklpn_rtp_fail(l_group_id);
1192 --RTV2 rtv project phase 2 : end
1193 clean_up_after_rtp (p_delivery_id, l_group_id);
1194 l_case_2_status := l_RCVTM_status;
1195 END IF;
1196 COMMIT;
1197 END IF;
1198 asn_debug.put_line(' l_case_2_status => ' || l_case_2_status);
1199 --
1200 EXCEPTION
1201 WHEN OTHERS THEN
1202 IF (g_asn_debug = 'Y') THEN
1203 asn_debug.put_line ('Case2 Unexpected exception : ' || SQLERRM);
1204 END IF;
1205 l_case_2_status := 'E';
1206 ROLLBACK TO SP_RcvTM;
1207 END;
1208
1209 ----------------------------------------- Direct transfers from Return hub ---------------------------
1210 BEGIN
1211
1212 SAVEPOINT SP_IOT;
1213 --
1214 l_header_id := NULL;
1215 l_INVTM_status := NULL;
1216
1217 FOR wdd_rec IN wdd_cursor_3 LOOP
1218 IF ( l_header_id IS NULL) THEN
1219 IF (g_asn_debug = 'Y') THEN
1220 asn_debug.put_line('-------------------------------------------');
1221 asn_debug.put_line('Processing Direct transfers from Return hub');
1222 asn_debug.put_line('-------------------------------------------');
1223 END IF;
1224 SELECT mtl_material_transactions_s.nextval INTO l_header_id FROM DUAL; -- Bug 11831232
1225 END IF;
1226 load_mtl_interfaces ('Direct Transfer', wdd_rec, l_header_id, p_delivery_id);
1227 END LOOP;
1228
1229 IF (l_header_id IS NOT NULL) THEN
1230 process_txn(l_header_id, l_INVTM_status);
1231
1232 IF (l_INVTM_status = 'S') THEN
1233 COMMIT;
1234 IF (g_asn_debug = 'Y') THEN
1235 asn_debug.put_line ('Direct Transfer processed successfully');
1236 END IF;
1237 ELSE
1238 ROLLBACK TO SP_IOT;
1239 l_case_3_status := l_INVTM_status;
1240 END IF;
1241 END IF;
1242 asn_debug.put_line(' l_case_3_status => ' || l_case_3_status);
1243 --
1244 EXCEPTION
1245 WHEN OTHERS THEN
1246 IF (g_asn_debug = 'Y') THEN
1247 asn_debug.put_line ('Case3 Unexpected exception : ' || SQLERRM);
1248 END IF;
1249 l_case_3_status := 'E';
1250 ROLLBACK;
1251 END;
1252 ----------------------------------------- Returns after Direct txr ---------------------------
1253
1254 BEGIN
1255 SAVEPOINT SP_Rcv;
1256 --
1257 l_group_id := NULL;
1258 l_RCVTM_status := NULL;
1259
1260 FOR wdd_rec IN wdd_cursor_4 LOOP
1261 IF ( l_group_id IS NULL) THEN
1262 IF (g_asn_debug = 'Y') THEN
1263 asn_debug.put_line('-------------------------------------------');
1264 asn_debug.put_line(' Processing Returns after Direct txr');
1265 asn_debug.put_line('-------------------------------------------');
1266 END IF;
1267 SELECT rcv_interface_groups_s.nextval INTO l_group_id FROM DUAL; -- Bug 11831232
1268 END IF;
1269 load_rcv_interfaces (p_delivery_id, wdd_rec,l_group_id);
1270 END LOOP;
1271
1272 IF (l_group_id IS NOT NULL) THEN
1273 COMMIT;
1274 invoke_rtp (l_group_id, l_RCVTM_status);
1275
1276 IF (l_RCVTM_status = 'S') THEN
1277 perform_post_TM_updates ('RCV', p_delivery_id);
1278 IF (g_asn_debug = 'Y') THEN
1279 asn_debug.put_line ('Returns after Direct txr processed successfully');
1280 END IF;
1281 ELSE
1282 --RTV2 rtv project phase 2 : start
1283 FOR wdd_rec IN wdd_cursor_4 LOOP
1284 rollback_rtp_fail(wdd_rec, l_group_id);
1285 END LOOP;
1286 --RTV2 rtv project phase 2 : end
1287 clean_up_after_rtp (p_delivery_id, l_group_id);
1288 l_case_4_status := l_RCVTM_status;
1289 END IF;
1290 COMMIT;
1291 END IF;
1292 asn_debug.put_line(' l_case_4_status => ' || l_case_4_status);
1293 EXCEPTION
1294 WHEN OTHERS THEN
1295 IF (g_asn_debug = 'Y') THEN
1296 asn_debug.put_line ('Case4 Unexpected exception : ' || SQLERRM);
1297 END IF;
1298 l_case_4_status := 'E';
1299 ROLLBACK;
1300 END;
1301 ----------------------------------------- Set WDDs as interfaced ---------------------------
1302
1303 BEGIN
1304 --
1305 IF (g_asn_debug = 'Y') THEN
1306 asn_debug.put_line('-------------------------------------------');
1307 asn_debug.put_line(' Checking for completely processed WDDs');
1308 asn_debug.put_line('-------------------------------------------');
1309 END IF;
1310 --
1311 asn_debug.put_line('Picking RT interfaced WDDs');
1312
1313 FOR wdd_rec IN wdd_cursor_5 LOOP
1314 l_index := l_index + 1;
1315 l_detail_rows(l_index) := (wdd_rec.delivery_detail_id);
1316
1317 IF (g_asn_debug = 'Y') THEN
1318 asn_debug.put_line('l_detail_rows(' || l_index || ') => ' || l_detail_rows(l_index));
1319 END IF;
1320 END LOOP;
1321 --
1322 asn_debug.put_line('Picking MMT interfaced WDDs');
1323
1324 FOR wdd_rec IN wdd_cursor_6 LOOP
1325 l_index := l_index + 1;
1326 l_detail_rows(l_index) := (wdd_rec.delivery_detail_id);
1327
1328 IF (g_asn_debug = 'Y') THEN
1329 asn_debug.put_line('l_detail_rows(' || l_index || ') => ' || l_detail_rows(l_index));
1330 END IF;
1331 END LOOP;
1332 --
1333 IF (l_index > 0) THEN
1334 WSH_INTEGRATION.update_delivery_details
1335 ( p_detail_rows => l_detail_rows,
1336 x_return_status => l_return_status);
1337
1338 IF (g_asn_debug = 'Y') THEN
1339 asn_debug.put_line('l_return_status => ' || l_return_status);
1340 END IF;
1341 END IF;
1342
1343 IF (l_return_status <> WSH_UTIL_CORE.G_RET_STS_SUCCESS) THEN
1344 l_case_5_status := 'E';
1345 ELSE
1346 COMMIT;
1347 END IF;
1348
1349 asn_debug.put_line(' l_case_5_status => ' || l_case_5_status);
1350 --
1351 IF (l_case_1_status = 'S' AND l_case_2_status = 'S' AND l_case_3_status = 'S' AND l_case_4_status = 'S' AND l_case_5_status = 'S') THEN
1352 p_return_status := 'S';
1353 ELSE
1354 p_return_status := 'E';
1355 END IF;
1356
1357 IF (g_asn_debug = 'Y') THEN
1358 asn_debug.put_line('Leaving interface_to_rcv : p_delivery_id => ' || p_delivery_id || ', p_return_status => ' || p_return_status);
1359 END IF;
1360
1361 EXCEPTION
1362 WHEN OTHERS THEN
1363 IF (g_asn_debug = 'Y') THEN
1364 asn_debug.put_line ('Case5 Unexpected exception : ' || SQLERRM);
1365 END IF;
1366 p_return_status := 'E';
1367 ROLLBACK;
1368 END;
1369 --
1370 END interface_to_rcv;
1371
1372 /*===========================================================================
1373
1374 PROCEDURE NAME: invoke_RTP ()
1375
1376 ===========================================================================*/
1377
1378 PROCEDURE invoke_RTP
1379 (p_group_id IN NUMBER,
1380 p_return_status OUT NOCOPY VARCHAR2 ) IS
1381
1382 l_rcv_count NUMBER := 0;
1383 l_timeout NUMBER := 172800;
1384 l_status NUMBER;
1385 l_outcome VARCHAR2(200) := NULL;
1386 l_msg VARCHAR2(200) := NULL;
1387 l_msg01 VARCHAR2(200) := NULL;
1388 l_msg02 VARCHAR2(200) := NULL;
1389 l_msg03 VARCHAR2(200) := NULL;
1390 l_msg04 VARCHAR2(200) := NULL;
1391 l_msg05 VARCHAR2(200) := NULL;
1392 l_msg06 VARCHAR2(200) := NULL;
1393 l_msg07 VARCHAR2(200) := NULL;
1394 l_msg08 VARCHAR2(200) := NULL;
1395 l_msg09 VARCHAR2(200) := NULL;
1396 l_msg10 VARCHAR2(200) := NULL;
1397 l_msg11 VARCHAR2(200) := NULL;
1398 l_msg12 VARCHAR2(200) := NULL;
1399 l_msg13 VARCHAR2(200) := NULL;
1400 l_msg14 VARCHAR2(200) := NULL;
1401 l_msg15 VARCHAR2(200) := NULL;
1402 l_msg16 VARCHAR2(200) := NULL;
1403 l_msg17 VARCHAR2(200) := NULL;
1404 l_msg18 VARCHAR2(200) := NULL;
1405 l_msg19 VARCHAR2(200) := NULL;
1406 l_msg20 VARCHAR2(200) := NULL;
1407 l_str VARCHAR2(4000) := NULL;
1408
1409 BEGIN
1410
1411 asn_debug.put_line('Inside invoke_RTP');
1412
1413 SELECT COUNT(*)
1414 INTO l_rcv_count
1415 FROM rcv_transactions_interface
1416 WHERE group_id = p_group_id;
1417 asn_debug.put_line('RTI record count for group_id ' || p_group_id || ' : ' || l_rcv_count);
1418
1419 l_status := fnd_transaction.synchronous
1420 ( l_timeout, l_outcome, l_msg, 'PO', 'RCVTPO', 'ONLINE', p_group_id,
1421 NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL,
1422 NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL);
1423
1424 SELECT COUNT(*)
1425 INTO l_rcv_count
1426 FROM rcv_transactions
1427 WHERE group_id = p_group_id;
1428 asn_debug.put_line('RT record count for group_id ' || p_group_id || ' : ' || l_rcv_count);
1429
1430 IF (l_status = 0 and (l_outcome NOT IN ('WARNING', 'ERROR'))) THEN
1431 p_return_status := 'S';
1432 IF (g_asn_debug = 'Y') THEN
1433 asn_debug.put_line('RCV transaction processed successfully');
1434 END IF;
1435
1436 ELSIF (l_status = 1) THEN
1437 p_return_status := 'E';
1438 IF (g_asn_debug = 'Y') THEN
1439 asn_debug.put_line('RCVTM timeout!');
1440 END IF;
1441
1442 ELSIF (l_status = 2) THEN
1443 p_return_status := 'E';
1444 IF (g_asn_debug = 'Y') THEN
1445 asn_debug.put_line('RCV Manager not available!');
1446 END IF;
1447
1448 ELSIF (l_status = 3 or (l_outcome IN ('WARNING', 'ERROR'))) THEN
1449 p_return_status := 'E';
1450 IF (g_asn_debug = 'Y') THEN
1451 asn_debug.put_line('RCV Manager failed!');
1452 END IF;
1453 l_status := fnd_transaction.get_values
1454 ( l_msg01, l_msg02, l_msg03, l_msg04, l_msg05,
1455 l_msg06, l_msg07, l_msg08, l_msg09, l_msg10,
1456 l_msg11, l_msg12, l_msg13, l_msg14, l_msg15,
1457 l_msg16, l_msg17, l_msg18, l_msg19, l_msg20 );
1458
1459 l_str := l_msg01;
1460 IF (l_msg02 IS NOT NULL) THEN l_str := l_str || ' ' || l_msg02; END IF;
1461 IF (l_msg03 IS NOT NULL) THEN l_str := l_str || ' ' || l_msg03; END IF;
1462 IF (l_msg04 IS NOT NULL) THEN l_str := l_str || ' ' || l_msg04; END IF;
1463 IF (l_msg05 IS NOT NULL) THEN l_str := l_str || ' ' || l_msg05; END IF;
1464 IF (l_msg06 IS NOT NULL) THEN l_str := l_str || ' ' || l_msg06; END IF;
1465 IF (l_msg07 IS NOT NULL) THEN l_str := l_str || ' ' || l_msg07; END IF;
1466 IF (l_msg08 IS NOT NULL) THEN l_str := l_str || ' ' || l_msg08; END IF;
1467 IF (l_msg09 IS NOT NULL) THEN l_str := l_str || ' ' || l_msg09; END IF;
1468 IF (l_msg10 IS NOT NULL) THEN l_str := l_str || ' ' || l_msg10; END IF;
1469 IF (l_msg11 IS NOT NULL) THEN l_str := l_str || ' ' || l_msg11; END IF;
1470 IF (l_msg12 IS NOT NULL) THEN l_str := l_str || ' ' || l_msg12; END IF;
1471 IF (l_msg13 IS NOT NULL) THEN l_str := l_str || ' ' || l_msg13; END IF;
1472 IF (l_msg14 IS NOT NULL) THEN l_str := l_str || ' ' || l_msg14; END IF;
1473 IF (l_msg15 IS NOT NULL) THEN l_str := l_str || ' ' || l_msg15; END IF;
1474 IF (l_msg16 IS NOT NULL) THEN l_str := l_str || ' ' || l_msg16; END IF;
1475 IF (l_msg17 IS NOT NULL) THEN l_str := l_str || ' ' || l_msg17; END IF;
1476 IF (l_msg18 IS NOT NULL) THEN l_str := l_str || ' ' || l_msg18; END IF;
1477 IF (l_msg19 IS NOT NULL) THEN l_str := l_str || ' ' || l_msg19; END IF;
1478 IF (l_msg20 IS NOT NULL) THEN l_str := l_str || ' ' || l_msg20; END IF;
1479
1480 IF (g_asn_debug = 'Y') THEN
1481 asn_debug.put_line('Error is : ' || l_str);
1482 END IF;
1483 END IF;
1484
1485 asn_debug.put_line('Leaving invoke_RTP');
1486
1487 EXCEPTION
1488 WHEN OTHERS THEN
1489 p_return_status := 'E';
1490 IF (g_asn_debug = 'Y') THEN
1491 asn_debug.put_line ('Case3 Unexpected exception from invoke_RTP: ' || SQLERRM);
1492 END IF;
1493 END invoke_RTP;
1494
1495 /*===========================================================================
1496
1497 PROCEDURE NAME: process_txn ()
1498
1499 ===========================================================================*/
1500
1501 PROCEDURE process_txn
1502 (p_header_id IN NUMBER,
1503 p_return_status OUT NOCOPY VARCHAR2) IS
1504
1505 l_return_status VARCHAR2(2);
1506 l_msg_count NUMBER;
1507 l_msg_data VARCHAR2(2000);
1508 l_trans_count NUMBER;
1509 l_status NUMBER;
1510 e_INVTM_error EXCEPTION;
1511
1512 BEGIN
1513 l_status := inv_txn_manager_pub.process_transactions
1514 ( p_api_version => 1.0,
1515 p_init_msg_list => FND_API.G_TRUE,
1516 p_commit => FND_API.G_FALSE,
1517 p_validation_level => FND_API.G_VALID_LEVEL_FULL,
1518 x_return_status => l_return_status,
1519 x_msg_count => l_msg_count,
1520 x_msg_data => l_msg_data,
1521 x_trans_count => l_trans_count,
1522 p_table => 1,
1523 p_header_id => p_header_id);
1524
1525 IF (l_status <> 0) THEN
1526 raise e_INVTM_error;
1527 END IF;
1528
1529 p_return_status := 'S';
1530 IF (g_asn_debug = 'Y') THEN
1531 asn_debug.put_line('INV transaction processed successfully');
1532 END IF;
1533
1534 EXCEPTION
1535 WHEN e_INVTM_error THEN
1536 IF (g_asn_debug = 'Y') THEN
1537 asn_debug.put_line('INV transaction failed');
1538 asn_debug.put_line('l_msg_count : ' || l_msg_count);
1539 asn_debug.put_line('l_msg_data : ' || l_msg_data);
1540 END IF;
1541 p_return_status := 'E';
1542
1543 WHEN OTHERS THEN
1544 IF (g_asn_debug = 'Y') THEN
1545 asn_debug.put_line ('Unexpected exception in process_txn: ' || SQLERRM);
1546 END IF;
1547 p_return_status := 'E';
1548 END process_txn;
1549
1550 /*===========================================================================
1551
1552 PROCEDURE NAME: load_mtl_interfaces ()
1553
1554 ===========================================================================*/
1555 PROCEDURE load_mtl_interfaces
1556 ( p_txn_desc IN VARCHAR2,
1557 p_wdd_rec IN wsh_delivery_details%rowtype,
1558 p_header_id IN NUMBER,
1559 p_delivery_id IN NUMBER) IS
1560
1561 CURSOR msnt_cursor IS
1562 SELECT *
1563 FROM mtl_serial_numbers_temp
1564 WHERE transaction_temp_id = p_wdd_rec.transaction_temp_id;
1565
1566 l_temp_id NUMBER; -- Bug 11831232
1567 l_deliver_subinv rcv_transactions.subinventory%TYPE := NULL;
1568 l_deliver_locator NUMBER := NULL;
1569 l_receipt_org NUMBER := NULL;
1570 l_account_id NUMBER := NULL;
1571 l_serial_temp_id NUMBER := NULL;
1572 l_ou_id NUMBER;
1573 l_txn_cost NUMBER;
1574 l_functional_currency VARCHAR2(3);
1575 l_return_status VARCHAR2(2);
1576 l_msg_count NUMBER;
1577 l_msg_data VARCHAR2(2000);
1578 l_txn_date date;
1579 l_txn_reference mtl_transactions_interface.transaction_reference%TYPE;
1580 --RTV2 rtv project phase 2 : start
1581 l_transfer_lpn_id NUMBER;
1582 l_lpn_id NUMBER;
1583 l_wms_rec_org VARCHAR2(2);
1584 --RTV2 rtv project phase 2 : end
1585
1586 BEGIN
1587
1588 IF (g_asn_debug = 'Y') THEN
1589 asn_debug.put_line('Entering load_mtl_interfaces');
1590 asn_debug.put_line('p_txn_desc : ' || p_txn_desc);
1591 END IF;
1592
1593 --RTV2 rtv project phase 2 : start
1594 l_lpn_id := get_return_lpn_id(p_wdd_rec.delivery_detail_id);
1595 l_transfer_lpn_id := l_lpn_id;
1596 --
1597 IF (p_txn_desc = 'Direct Transfer') THEN
1598 --
1599 SELECT rt.subinventory, rt.locator_id, rt.organization_id, rti.rma_reference, mp.WMS_ENABLED_FLAG -- Bug 12974284
1600 INTO l_deliver_subinv, l_deliver_locator, l_receipt_org, l_txn_reference, l_wms_rec_org -- Bug 12974284
1601 FROM rcv_transactions rt,
1602 rcv_transactions_interface rti,
1603 mtl_parameters mp
1604 WHERE rt.transaction_id = rti.parent_transaction_id
1605 AND rti.interface_transaction_id = p_wdd_rec.source_line_id
1606 AND rt.transaction_type = 'DELIVER'
1607 AND mp.organization_id = rt.organization_id;
1608
1609 IF (g_asn_debug = 'Y') THEN
1610 asn_debug.put_line('l_deliver_subinv : ' || l_deliver_subinv);
1611 asn_debug.put_line('l_deliver_locator : ' || l_deliver_locator);
1612 asn_debug.put_line('l_receipt_org : ' || l_receipt_org);
1613 asn_debug.put_line('l_wms_rec_org : ' || l_wms_rec_org);
1614 END IF;
1615
1616 IF(nvl(l_wms_rec_org, 'N') = 'N') THEN
1617 l_transfer_lpn_id := NULL;
1618 END IF;
1619 --RTV2 rtv project phase 2 : end
1620
1621 --
1622 ELSIF (p_txn_desc = 'Issue out') THEN
1623
1624 l_transfer_lpn_id := null; --RTV2 rtv project phase 2
1625
1626 SELECT ap_accrual_account
1627 INTO l_account_id
1628 FROM mtl_parameters
1629 WHERE organization_id = p_wdd_rec.organization_id;
1630
1631 IF (g_asn_debug = 'Y') THEN
1632 asn_debug.put_line('l_account_id : ' || l_account_id);
1633 END IF;
1634
1635 IF (l_account_id IS NULL) THEN
1636 raise fnd_api.g_exc_unexpected_error;
1637 END IF;
1638 --
1639 SELECT currency_code, operating_unit
1640 INTO l_functional_currency, l_ou_id
1641 FROM cst_organization_definitions
1642 WHERE organization_id = p_wdd_rec.organization_id;
1643
1644 IF (g_asn_debug = 'Y') THEN
1645 asn_debug.put_line('l_functional_currency : ' || l_functional_currency);
1646 END IF;
1647
1648 IF (p_wdd_rec.currency_code IS NOT NULL AND p_wdd_rec.currency_code <> l_functional_currency) THEN
1649 l_txn_cost := inv_transaction_flow_pub.convert_currency
1650 ( p_org_id => l_ou_id,
1651 p_transfer_price => p_wdd_rec.unit_price,
1652 p_currency_code => p_wdd_rec.currency_code,
1653 p_transaction_date => p_wdd_rec.date_requested,
1654 p_logical_txn => 'N',
1655 x_functional_currency_code => l_functional_currency,
1656 x_return_status => l_return_status,
1657 x_msg_data => l_msg_data,
1658 x_msg_count => l_msg_count);
1659
1660 IF (l_return_status <> FND_API.G_RET_STS_SUCCESS ) THEN
1661 raise fnd_api.g_exc_unexpected_error;
1662 END IF;
1663 END IF;
1664 IF (g_asn_debug = 'Y') THEN
1665 asn_debug.put_line('l_functional_currency : ' || l_functional_currency);
1666 asn_debug.put_line('l_txn_cost : ' || l_txn_cost);
1667 END IF;
1668 --
1669 ELSE
1670 RETURN;
1671 END IF;
1672 --RTV2 rtv project phase 2 : start
1673 IF (g_asn_debug = 'Y') THEN
1674 asn_debug.put_line('l_lpn_id : ' || l_lpn_id);
1675 asn_debug.put_line('l_transfer_lpn_id : ' || l_transfer_lpn_id);
1676 END IF;
1677 --RTV2 rtv project phase 2 : end
1678
1679 --
1680 SELECT wts.actual_departure_date
1681 INTO l_txn_date
1682 FROM wsh_new_deliveries wnd,
1683 wsh_delivery_legs wdl,
1684 wsh_trip_stops wts
1685 WHERE wnd.delivery_id = wdl.delivery_id
1686 AND wdl.pick_up_stop_id = wts.stop_id
1687 AND wnd.initial_pickup_location_id = wts.stop_location_id
1688 AND wnd.delivery_id = p_delivery_id;
1689 --
1690 SELECT rma_reference
1691 INTO l_txn_reference
1692 FROM rcv_transactions_interface
1693 WHERE interface_transaction_id = p_wdd_rec.source_line_id; -- Bug 12974284
1694 --
1695 SELECT mtl_material_transactions_s.nextval INTO l_temp_id FROM DUAL; -- Bug 11831232
1696 --
1697 INSERT INTO mtl_transactions_interface
1698 ( transaction_header_id,
1699 transaction_interface_id,
1700 source_code,
1701 transaction_source_name,
1702 source_header_id,
1703 source_line_id,
1704 picking_line_id,
1705 process_flag,
1706 validation_required,
1707 transaction_mode,
1708 lock_flag,
1709 last_update_date,
1710 last_updated_by,
1711 creation_date,
1712 created_by,
1713 last_update_login,
1714 inventory_item_id,
1715 revision,
1716 transaction_quantity,
1717 transaction_uom,
1718 secondary_transaction_quantity, -- Bug 12768025
1719 secondary_uom_code, -- Bug 12768025
1720 transaction_date,
1721 organization_id,
1722 transfer_organization,
1723 subinventory_code,
1724 transfer_subinventory,
1725 locator_id,
1726 transfer_locator,
1727 transaction_source_type_id,
1728 transaction_type_id,
1729 transaction_action_id,
1730 distribution_account_id,
1731 currency_code,
1732 transaction_cost,
1733 transaction_reference, -- Bug 12974284
1734 lpn_id, -- RTV2 rtv project phase 2
1735 transfer_lpn_id, -- RTV2 rtv project phase 2
1736 content_lpn_id -- RTV2 rtv project phase 2
1737 )
1738 SELECT p_header_id,
1739 l_temp_id,
1740 p_txn_desc,
1741 p_wdd_rec.source_header_number,
1742 p_wdd_rec.source_header_id,
1743 p_wdd_rec.source_line_id,
1744 p_wdd_rec.delivery_detail_id,
1745 1,
1746 2,
1747 3,
1748 2,
1749 sysdate,
1750 p_wdd_rec.last_updated_by,
1751 sysdate,
1752 p_wdd_rec.created_by,
1753 p_wdd_rec.last_update_login,
1754 p_wdd_rec.inventory_item_id,
1755 p_wdd_rec.revision,
1756 decode(p_txn_desc,'Direct Transfer',p_wdd_rec.shipped_quantity, 'Issue out', p_wdd_rec.shipped_quantity * -1),
1757 p_wdd_rec.requested_quantity_uom,
1758 decode(p_txn_desc,'Direct Transfer',p_wdd_rec.shipped_quantity2, 'Issue out', p_wdd_rec.shipped_quantity2 * -1), -- Bug 12768025
1759 p_wdd_rec.requested_quantity_uom2, -- Bug 12768025
1760 l_txn_date,
1761 p_wdd_rec.organization_id,
1762 l_receipt_org,
1763 p_wdd_rec.subinventory,
1764 decode(p_txn_desc,'Direct Transfer', l_deliver_subinv, 'Issue out', NULL),
1765 p_wdd_rec.locator_id,
1766 decode(p_txn_desc,'Direct Transfer', l_deliver_locator, 'Issue out', NULL),
1767 13,
1768 decode(p_txn_desc,'Direct Transfer', 3, 'Issue out', 1005),
1769 decode(p_txn_desc,'Direct Transfer', 3, 'Issue out', 1),
1770 l_account_id,
1771 decode(p_txn_desc,'Direct Transfer', NULL, 'Issue out', l_functional_currency),
1772 l_txn_cost,
1773 l_txn_reference, -- Bug 12974284
1774 l_lpn_id, -- RTV2 rtv project phase 2
1775 l_transfer_lpn_id, -- RTV2 rtv project phase 2
1776 l_transfer_lpn_id -- RTV2 rtv project phase 2
1777 FROM DUAL;
1778
1779 IF (g_asn_debug = 'Y') THEN
1780 asn_debug.put_line('Inserted MTI with transaction_interface_id : ' || l_temp_id);
1781 END IF;
1782
1783 load_lot_serial_interfaces ('INV', p_wdd_rec, l_temp_id);
1784
1785 --RTV2 rtv project phase 2 : start
1786 unmark_wdd_lpn(p_wdd_rec => p_wdd_rec,
1787 p_lpn_id => l_lpn_id,
1788 x_return_status => l_return_status,
1789 x_msg_count => l_msg_count,
1790 x_msg_data => l_msg_data);
1791 IF (l_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
1792 raise fnd_api.g_exc_unexpected_error;
1793 END IF;
1794 --RTV2 rtv project phase 2 : end
1795
1796 IF (g_asn_debug = 'Y') THEN
1797 asn_debug.put_line('Leaving load_mtl_interfaces');
1798 END IF;
1799
1800 EXCEPTION
1801 WHEN OTHERS THEN
1802 IF (g_asn_debug = 'Y') THEN
1803 asn_debug.put_line('Unexpected exception in load_mtl_interfaces : ' || SQLERRM);
1804 raise;
1805 END IF;
1806
1807 END load_mtl_interfaces;
1808
1809 /*===========================================================================
1810
1811 PROCEDURE NAME: load_rcv_interfaces ()
1812
1813 ===========================================================================*/
1814 PROCEDURE load_rcv_interfaces (
1815 p_delivery_id IN NUMBER,
1816 p_wdd_rec IN wsh_delivery_details%rowtype,
1817 p_group_id IN NUMBER) IS
1818
1819 l_rti_id NUMBER; -- Bug 11831232
1820 l_shipped_uom mtl_units_of_measure.unit_of_measure%TYPE;
1821 l_shipped_uom2 mtl_units_of_measure.unit_of_measure%TYPE := NULL;
1822 l_primary_qty NUMBER;
1823 l_marker_flag BOOLEAN := FALSE;
1824 rti_rec rcv_transactions_interface%ROWTYPE;
1825 l_rev_control NUMBER;
1826 l_txn_date date;
1827 l_from_subinventory rcv_transactions_interface.from_subinventory%TYPE;
1828 l_from_locator_id NUMBER;
1829 --RTV2 rtv project phase 2 : start
1830 l_rec_wms_org VARCHAR2(1);
1831 l_return_status VARCHAR2(1);
1832 l_msg_count NUMBER;
1833 l_msg_data VARCHAR2(2000);
1834 e_load_rti_error1 EXCEPTION;
1835 --RTV2 rtv project phase 2 : end
1836
1837 BEGIN
1838
1839 IF (g_asn_debug = 'Y') THEN
1840 asn_debug.put_line('Entering load_rcv_interfaces');
1841 END IF;
1842 --
1843 l_shipped_uom := get_uom_from_code (p_wdd_rec.requested_quantity_uom);
1844 l_shipped_uom2 := get_uom_from_code (p_wdd_rec.requested_quantity_uom2);
1845
1846 IF (g_asn_debug = 'Y') THEN
1847 asn_debug.put_line('l_shipped_uom : ' || l_shipped_uom);
1848 asn_debug.put_line('l_shipped_uom2 : ' || l_shipped_uom2);
1849 END IF;
1850 --
1851 SELECT *
1852 INTO rti_rec
1853 FROM rcv_transactions_interface
1854 WHERE interface_transaction_id = p_wdd_rec.source_line_id;
1855
1856 IF (g_asn_debug = 'Y') THEN
1857 asn_debug.put_line('rti_rec.primary_unit_of_measure : ' || rti_rec.primary_unit_of_measure);
1858 asn_debug.put_line('rti_rec.to_organization_id : ' || rti_rec.to_organization_id);
1859 END IF;
1860
1861 --RTV2 rtv project phase 2 : start
1862 IF (rti_rec.transfer_lpn_id IS NOT NULL) THEN
1863 IF (rti_rec.to_organization_id <> p_wdd_rec.organization_id) THEN
1864 SELECT WMS_ENABLED_FLAG
1865 INTO l_rec_wms_org
1866 FROM mtl_parameters
1867 WHERE organization_id = rti_rec.to_organization_id;
1868
1869 IF (NVL(l_rec_wms_org, 'N') = 'N') THEN
1870 rti_rec.transfer_lpn_id := NULL;
1871 ELSE
1872 --we need to re-fetch lpn from wdd, since we support lpn split.
1873 rti_rec.transfer_lpn_id := get_return_lpn_id(p_wdd_rec.delivery_detail_id);
1874 END IF;
1875 ELSE
1876 --we need to re-fetch lpn from wdd, since we support lpn split.
1877 rti_rec.transfer_lpn_id := get_return_lpn_id(p_wdd_rec.delivery_detail_id);
1878 END IF;
1879 END IF;
1880
1881 rti_rec.lpn_id := rti_rec.transfer_lpn_id;
1882 IF (rti_rec.lpn_id IS NULL) THEN
1883 rti_rec.lpn_group_id := NULL;
1884 END IF;
1885 --RTV2 rtv project phase 2 : end
1886
1887 --
1888 IF (l_shipped_uom <> rti_rec.primary_unit_of_measure) THEN
1889 po_uom_s.uom_convert
1890 ( from_quantity => p_wdd_rec.shipped_quantity,
1891 from_uom => l_shipped_uom,
1892 item_id => p_wdd_rec.inventory_item_id,
1893 to_uom => rti_rec.primary_unit_of_measure,
1894 to_quantity => l_primary_qty);
1895 ELSE
1896 l_primary_qty := p_wdd_rec.shipped_quantity;
1897 END IF;
1898
1899 IF (g_asn_debug = 'Y') THEN
1900 asn_debug.put_line('l_primary_qty : ' || l_primary_qty);
1901 END IF;
1902 --
1903 IF (rti_rec.item_revision IS NOT NULL) THEN
1904 SELECT revision_qty_control_code
1905 INTO l_rev_control
1906 FROM mtl_system_items msi
1907 WHERE msi.organization_id = rti_rec.to_organization_id
1908 AND msi.inventory_item_id = rti_rec.item_id;
1909
1910 IF (g_asn_debug = 'Y') THEN
1911 asn_debug.put_line('l_rev_control : ' || l_rev_control);
1912 END IF;
1913
1914 IF (l_rev_control = 1) THEN
1915 rti_rec.item_revision := NULL;
1916 END IF;
1917 END IF;
1918 --
1919 IF (rti_rec.to_organization_id <> p_wdd_rec.organization_id) THEN
1920 SELECT rt.subinventory, rt.locator_id
1921 INTO l_from_subinventory, l_from_locator_id
1922 FROM rcv_transactions rt
1923 WHERE rt.transaction_id = rti_rec.parent_transaction_id
1924 AND rt.transaction_type = 'DELIVER';
1925 ELSE
1926 --RTV2 rtv project phase 2 : start
1927 IF (rti_rec.transfer_lpn_id IS NOT NULL) THEN
1928 l_from_subinventory := p_wdd_rec.subinventory;
1929 l_from_locator_id := p_wdd_rec.locator_id;
1930 ELSE
1931 l_from_subinventory := rti_rec.from_subinventory;
1932 l_from_locator_id := rti_rec.from_locator_id;
1933 END IF;
1934 --RTV2 rtv project phase 2 : end
1935 END IF;
1936 --
1937 IF (g_asn_debug = 'Y') THEN
1938 asn_debug.put_line('l_from_subinventory : ' || l_from_subinventory);
1939 asn_debug.put_line('l_from_locator_id : ' || l_from_locator_id);
1940 END IF;
1941 --
1942 SELECT wts.actual_departure_date
1943 INTO l_txn_date
1944 FROM wsh_new_deliveries wnd,
1945 wsh_delivery_legs wdl,
1946 wsh_trip_stops wts
1947 WHERE wnd.delivery_id = wdl.delivery_id
1948 AND wdl.pick_up_stop_id = wts.stop_id
1949 AND wnd.initial_pickup_location_id = wts.stop_location_id
1950 AND wnd.delivery_id = p_delivery_id;
1951 --
1952 SELECT rcv_transactions_interface_s.nextval INTO l_rti_id FROM DUAL; -- Bug 11831232
1953 --
1954 INSERT INTO rcv_transactions_interface
1955 ( receipt_source_code,
1956 interface_transaction_id,
1957 interface_source_line_id,
1958 group_id,
1959 last_update_date,
1960 last_updated_by,
1961 created_by,
1962 creation_date,
1963 last_update_login,
1964 source_document_code,
1965 destination_type_code,
1966 transaction_date,
1967 quantity,
1968 unit_of_measure,
1969 secondary_quantity,
1970 secondary_unit_of_measure,
1971 primary_quantity,
1972 primary_unit_of_measure,
1973 uom_code,
1974 shipment_header_id,
1975 shipment_line_id,
1976 substitute_unordered_code,
1977 employee_id,
1978 parent_transaction_id,
1979 inspection_status_code,
1980 inspection_quality_code,
1981 po_header_id,
1982 po_release_id,
1983 po_line_id,
1984 po_line_location_id,
1985 po_distribution_id,
1986 po_revision_num,
1987 po_unit_price,
1988 currency_code,
1989 currency_conversion_rate,
1990 currency_conversion_date,
1991 currency_conversion_type,
1992 routing_header_id,
1993 routing_step_id,
1994 comments,
1995 attribute_category,
1996 attribute1,
1997 attribute2,
1998 attribute3,
1999 attribute4,
2000 attribute5,
2001 attribute6,
2002 attribute7,
2003 attribute8,
2004 attribute9,
2005 attribute10,
2006 attribute11,
2007 attribute12,
2008 attribute13,
2009 attribute14,
2010 attribute15,
2011 transaction_type,
2012 location_id,
2013 processing_status_code,
2014 processing_mode_code,
2015 transaction_status_code,
2016 category_id,
2017 vendor_lot_num,
2018 reason_id,
2019 item_id,
2020 item_revision,
2021 to_organization_id,
2022 deliver_to_location_id,
2023 destination_context,
2024 vendor_id,
2025 deliver_to_person_id,
2026 wip_entity_id,
2027 wip_line_id,
2028 wip_repetitive_schedule_id,
2029 wip_operation_seq_num,
2030 wip_resource_seq_num,
2031 bom_resource_id,
2032 from_organization_id,
2033 receipt_exception_flag,
2034 department_code,
2035 item_description,
2036 movement_id,
2037 use_mtl_lot,
2038 use_mtl_serial,
2039 rma_reference,
2040 ussgl_transaction_code,
2041 government_context,
2042 vendor_site_id,
2043 oe_order_header_id,
2044 oe_order_line_id,
2045 customer_id,
2046 customer_site_id,
2047 create_debit_memo_flag,
2048 lpn_id,
2049 transfer_lpn_id,
2050 lpn_group_id,
2051 from_subinventory,
2052 from_locator_id,
2053 subinventory,
2054 locator_id,
2055 org_id,
2056 lcm_shipment_line_id,
2057 unit_landed_cost,
2058 validation_flag
2059 )
2060 VALUES
2061 ( rti_rec.receipt_source_code,
2062 l_rti_id,
2063 p_wdd_rec.delivery_detail_id,
2064 p_group_id,
2065 sysdate,
2066 rti_rec.last_updated_by,
2067 rti_rec.created_by,
2068 sysdate,
2069 rti_rec.last_update_login,
2070 rti_rec.source_document_code,
2071 rti_rec.destination_type_code,
2072 l_txn_date,
2073 p_wdd_rec.shipped_quantity,
2074 l_shipped_uom,
2075 p_wdd_rec.shipped_quantity2,
2076 l_shipped_uom2,
2077 l_primary_qty,
2078 rti_rec.primary_unit_of_measure,
2079 p_wdd_rec.requested_quantity_uom, -- Bug 14340673
2080 rti_rec.shipment_header_id,
2081 rti_rec.shipment_line_id,
2082 rti_rec.substitute_unordered_code,
2083 rti_rec.employee_id,
2084 rti_rec.parent_transaction_id,
2085 rti_rec.inspection_status_code,
2086 rti_rec.inspection_quality_code,
2087 rti_rec.po_header_id,
2088 rti_rec.po_release_id,
2089 rti_rec.po_line_id,
2090 rti_rec.po_line_location_id,
2091 rti_rec.po_distribution_id,
2092 rti_rec.po_revision_num,
2093 rti_rec.po_unit_price,
2094 rti_rec.currency_code,
2095 rti_rec.currency_conversion_rate,
2096 rti_rec.currency_conversion_date,
2097 rti_rec.currency_conversion_type,
2098 rti_rec.routing_header_id,
2099 rti_rec.routing_step_id,
2100 rti_rec.comments,
2101 rti_rec.attribute_category,
2102 rti_rec.attribute1,
2103 rti_rec.attribute2,
2104 rti_rec.attribute3,
2105 rti_rec.attribute4,
2106 rti_rec.attribute5,
2107 rti_rec.attribute6,
2108 rti_rec.attribute7,
2109 rti_rec.attribute8,
2110 rti_rec.attribute9,
2111 rti_rec.attribute10,
2112 rti_rec.attribute11,
2113 rti_rec.attribute12,
2114 rti_rec.attribute13,
2115 rti_rec.attribute14,
2116 rti_rec.attribute15,
2117 rti_rec.transaction_type,
2118 rti_rec.location_id,
2119 'PENDING',
2120 'ONLINE',
2121 'PENDING',
2122 rti_rec.category_id,
2123 rti_rec.vendor_lot_num,
2124 rti_rec.reason_id,
2125 rti_rec.item_id,
2126 rti_rec.item_revision,
2127 rti_rec.to_organization_id,
2128 rti_rec.deliver_to_location_id,
2129 rti_rec.destination_context,
2130 rti_rec.vendor_id,
2131 rti_rec.deliver_to_person_id,
2132 rti_rec.wip_entity_id,
2133 rti_rec.wip_line_id,
2134 rti_rec.wip_repetitive_schedule_id,
2135 rti_rec.wip_operation_seq_num,
2136 rti_rec.wip_resource_seq_num,
2137 rti_rec.bom_resource_id,
2138 rti_rec.from_organization_id,
2139 rti_rec.receipt_exception_flag,
2140 rti_rec.department_code,
2141 rti_rec.item_description,
2142 rti_rec.movement_id,
2143 rti_rec.use_mtl_lot,
2144 rti_rec.use_mtl_serial,
2145 rti_rec.rma_reference,
2146 rti_rec.ussgl_transaction_code,
2147 rti_rec.government_context,
2148 rti_rec.vendor_site_id,
2149 rti_rec.oe_order_header_id,
2150 rti_rec.oe_order_line_id,
2151 rti_rec.customer_id,
2152 rti_rec.customer_site_id,
2153 rti_rec.create_debit_memo_flag,
2154 rti_rec.lpn_id,
2155 rti_rec.transfer_lpn_id,
2156 rti_rec.lpn_group_id,
2157 l_from_subinventory,
2158 l_from_locator_id,
2159 rti_rec.subinventory,
2160 rti_rec.locator_id,
2161 rti_rec.org_id,
2162 rti_rec.lcm_shipment_line_id,
2163 rti_rec.unit_landed_cost,
2164 'Y'
2165 );
2166
2167 IF (g_asn_debug = 'Y') THEN
2168 asn_debug.put_line('Inserted RTI with transaction_interface_id : ' || l_rti_id);
2169 END IF;
2170 --
2171 load_lot_serial_interfaces ('RCV', p_wdd_rec, l_rti_id);
2172
2173 --RTV2 rtv project phase 2 : start
2174 --We just need to unmark lpn for wdd which has related lpn and RTV is from receipt org.
2175 --As for RTV org different from RCV org, we already unmark lpn before performing direct org xfer.
2176 IF (rti_rec.transfer_lpn_id IS NOT NULL AND rti_rec.to_organization_id = p_wdd_rec.organization_id) THEN
2177 unmark_wdd_lpn(p_wdd_rec => p_wdd_rec,
2178 p_lpn_id => rti_rec.transfer_lpn_id,
2179 x_return_status => l_return_status,
2180 x_msg_count => l_msg_count,
2181 x_msg_data => l_msg_data);
2182 IF (l_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
2183 raise fnd_api.g_exc_unexpected_error;
2184 END IF;
2185 END IF;
2186 --Re-mark lpn for new RTIs
2187 IF( rti_rec.transfer_lpn_id IS NOT NULL) THEN
2188 wms_return_sv.MARK_RETURNS(
2189 x_return_status => l_return_status,
2190 x_msg_count => l_msg_count,
2191 x_msg_data => l_msg_data,
2192 p_rcv_trx_interface_id => l_rti_id,
2193 p_ret_transaction_type => rti_rec.transaction_type,
2194 p_lpn_id => rti_rec.transfer_lpn_id,
2195 p_item_id => rti_rec.item_id,
2196 p_item_revision => rti_rec.item_revision,
2197 p_quantity => p_wdd_rec.shipped_quantity,
2198 p_uom => p_wdd_rec.requested_quantity_uom,
2199 p_serial_controlled => rti_rec.use_mtl_serial,
2200 p_lot_controlled => rti_rec.use_mtl_lot,
2201 p_org_id => rti_rec.to_organization_id,
2202 p_subinventory => l_from_subinventory,
2203 p_locator_id => l_from_locator_id);
2204
2205 IF (l_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
2206 raise fnd_api.g_exc_unexpected_error;
2207 END IF;
2208 END IF;
2209 --RTV2 rtv project phase 2 : end
2210
2211 IF (g_asn_debug = 'Y') THEN
2212 asn_debug.put_line('Leaving load_rcv_interfaces');
2213 END IF;
2214
2215 EXCEPTION
2216 WHEN OTHERS THEN
2217 IF (g_asn_debug = 'Y') THEN
2218 asn_debug.put_line('Unexpected exception in load_rcv_interfaces : ' || SQLERRM);
2219 raise;
2220 END IF;
2221
2222 END load_rcv_interfaces;
2223
2224 /*===========================================================================
2225
2226 PROCEDURE NAME: load_lot_serial_interfaces ()
2227
2228 ===========================================================================*/
2229 PROCEDURE load_lot_serial_interfaces (
2230 p_source IN VARCHAR2,
2231 p_wdd_rec IN wsh_delivery_details%rowtype,
2232 p_parent_id IN NUMBER) IS
2233
2234 CURSOR WSN_cursor IS
2235 SELECT *
2236 FROM wsh_serial_numbers
2237 WHERE delivery_detail_id = p_wdd_rec.delivery_detail_id;
2238
2239 l_temp_id NUMBER;
2240 l_serial_temp_id NUMBER := NULL;
2241 l_prod_txn_id NUMBER := NULL;
2242 l_serial_control NUMBER;
2243 l_lot_control NUMBER;
2244 l_source_allows_lot BOOLEAN := TRUE;
2245 l_source_allows_serial BOOLEAN := TRUE;
2246 l_lot_inserted BOOLEAN := FALSE;
2247 l_serial_inserted BOOLEAN := FALSE;
2248 l_serial_tagged NUMBER := 1;
2249 l_rti_org_id NUMBER;
2250 l_rti_item_id NUMBER;
2251
2252 BEGIN
2253
2254 IF (g_asn_debug = 'Y') THEN
2255 asn_debug.put_line('Entering load_lot_serial_interfaces : Source => ' || p_source || ' ,parent => ' || p_parent_id);
2256 END IF;
2257
2258 IF (p_wdd_rec.lot_number IS NULL AND p_wdd_rec.transaction_temp_id IS NULL) THEN
2259 IF (g_asn_debug = 'Y') THEN
2260 asn_debug.put_line('No lot/serial attached to WDD : ' || p_wdd_rec.delivery_detail_id);
2261 END IF;
2262 RETURN;
2263 END IF;
2264 --
2265 IF (p_source = 'RCV') THEN
2266 SELECT msi.lot_control_code, msi.serial_number_control_code,
2267 rti.to_organization_id, rti.item_id
2268 INTO l_lot_control, l_serial_control, l_rti_org_id, l_rti_item_id
2269 FROM mtl_system_items msi,
2270 rcv_transactions_interface rti
2271 WHERE msi.organization_id = rti.to_organization_id
2272 AND msi.inventory_item_id = rti.item_id
2273 AND rti.interface_transaction_id = p_parent_id;
2274
2275 l_serial_tagged := inv_cache.get_serial_tagged (l_rti_org_id, l_rti_item_id , 36);
2276
2277 IF (g_asn_debug = 'Y') THEN
2278 asn_debug.put_line('l_serial_control : ' || l_serial_control || ' ,l_lot_control : ' || l_lot_control || ', l_serial_tagged : ' || l_serial_tagged);
2279 END IF;
2280
2281 IF (l_lot_control <> 2) THEN
2282 l_source_allows_lot := FALSE;
2283 END IF;
2284
2285 IF (l_serial_control NOT IN (2,5) AND l_serial_tagged <> 2) THEN
2286 l_source_allows_serial := FALSE;
2287 END IF;
2288
2289 l_prod_txn_id := p_parent_id;
2290 END IF;
2291 --
2292 IF (l_source_allows_lot AND p_wdd_rec.lot_number IS NOT NULL ) THEN
2293 IF (p_source = 'INV') THEN
2294 l_temp_id := p_parent_id;
2295 ELSIF (p_source = 'RCV') THEN
2296 SELECT mtl_material_transactions_s.nextval INTO l_temp_id FROM DUAL; -- Bug 11831232
2297 END IF;
2298 --
2299 INSERT INTO mtl_transaction_lots_interface
2300 ( transaction_interface_id,
2301 last_update_date,
2302 last_updated_by,
2303 creation_date,
2304 created_by,
2305 last_update_login,
2306 lot_number,
2307 transaction_quantity,
2308 primary_quantity,
2309 product_code,
2310 product_transaction_id,
2311 attribute_category,
2312 attribute1,
2313 attribute2,
2314 attribute3,
2315 attribute4,
2316 attribute5,
2317 attribute6,
2318 attribute7,
2319 attribute8,
2320 attribute9,
2321 attribute10,
2322 attribute11,
2323 attribute12,
2324 attribute13,
2325 attribute14,
2326 attribute15
2327 )
2328 SELECT l_temp_id,
2329 sysdate,
2330 p_wdd_rec.last_updated_by,
2331 sysdate,
2332 p_wdd_rec.created_by,
2333 p_wdd_rec.last_update_login,
2334 p_wdd_rec.lot_number,
2335 p_wdd_rec.shipped_quantity,
2336 p_wdd_rec.shipped_quantity,
2337 mtlt.product_code,
2338 l_prod_txn_id,
2339 mtlt.attribute_category,
2340 mtlt.attribute1,
2341 mtlt.attribute2,
2342 mtlt.attribute3,
2343 mtlt.attribute4,
2344 mtlt.attribute5,
2345 mtlt.attribute6,
2346 mtlt.attribute7,
2347 mtlt.attribute8,
2348 mtlt.attribute9,
2349 mtlt.attribute10,
2350 mtlt.attribute11,
2351 mtlt.attribute12,
2352 mtlt.attribute13,
2353 mtlt.attribute14,
2354 mtlt.attribute15
2355 FROM mtl_transaction_lots_temp mtlt
2356 WHERE mtlt.transaction_temp_id (+) = p_wdd_rec.source_line_id
2357 AND mtlt.lot_number = p_wdd_rec.lot_number;
2358
2359 l_lot_inserted := TRUE;
2360
2361 IF (g_asn_debug = 'Y') THEN
2362 asn_debug.put_line('Inserted MTLI for Lot# : ' || p_wdd_rec.lot_number);
2363 END IF;
2364
2365 END IF;
2366 --
2367 IF (l_source_allows_serial) THEN
2368 FOR wsn_rec IN WSN_cursor LOOP
2369
2370 IF (l_serial_temp_id IS NULL) THEN
2371 IF (p_source = 'INV') THEN
2372 IF (l_lot_inserted) THEN
2373 SELECT mtl_material_transactions_s.nextval INTO l_serial_temp_id FROM DUAL; -- Bug 11831232
2374 ELSE
2375 l_serial_temp_id := p_parent_id;
2376 END IF;
2377 ELSIF (p_source = 'RCV') THEN
2378 SELECT mtl_material_transactions_s.nextval INTO l_serial_temp_id FROM DUAL; -- Bug 11831232
2379 END IF;
2380 END IF;
2381 --
2382 -- Transfer tagged serial to receipt org if return is done from another org ie, direct txr is completed at this point.
2383 IF (l_serial_tagged = 2 AND l_rti_org_id <> p_wdd_rec.organization_id) THEN
2384 UPDATE mtl_serial_numbers
2385 SET current_organization_id = l_rti_org_id
2386 WHERE inventory_item_id = l_rti_item_id
2387 AND current_organization_id = p_wdd_rec.organization_id
2388 AND serial_number between wsn_rec.fm_serial_number and nvl(wsn_rec.to_serial_number,wsn_rec.fm_serial_number) -- Bug 10120533
2389 AND length(serial_number) = length(wsn_rec.fm_serial_number); -- Bug 10120533
2390 END IF;
2391
2392 -- This will be changed to pick from WSN once shipping patch is ready.
2393 INSERT INTO mtl_serial_numbers_interface
2394 ( transaction_interface_id,
2395 product_code,
2396 product_transaction_id,
2397 last_update_date,
2398 last_updated_by,
2399 creation_date,
2400 created_by,
2401 last_update_login,
2402 fm_serial_number,
2403 to_serial_number,
2404 attribute_category,
2405 attribute1,
2406 attribute2,
2407 attribute3,
2408 attribute4,
2409 attribute5,
2410 attribute6,
2411 attribute7,
2412 attribute8,
2413 attribute9,
2414 attribute10,
2415 attribute11,
2416 attribute12,
2417 attribute13,
2418 attribute14,
2419 attribute15,
2420 serial_attribute_category,
2421 c_attribute1,
2422 c_attribute2,
2423 c_attribute3,
2424 c_attribute4,
2425 c_attribute5,
2426 c_attribute6,
2427 c_attribute7,
2428 c_attribute8,
2429 c_attribute9,
2430 c_attribute10,
2431 c_attribute11,
2432 c_attribute12,
2433 c_attribute13,
2434 c_attribute14,
2435 c_attribute15,
2436 c_attribute16,
2437 c_attribute17,
2438 c_attribute18,
2439 c_attribute19,
2440 c_attribute20,
2441 d_attribute1,
2442 d_attribute2,
2443 d_attribute3,
2444 d_attribute4,
2445 d_attribute5,
2446 d_attribute6,
2447 d_attribute7,
2448 d_attribute8,
2449 d_attribute9,
2450 d_attribute10,
2451 n_attribute1,
2452 n_attribute2,
2453 n_attribute3,
2454 n_attribute4,
2455 n_attribute5,
2456 n_attribute6,
2457 n_attribute7,
2458 n_attribute8,
2459 n_attribute9,
2460 n_attribute10,
2461 territory_code,
2462 time_since_new,
2463 cycles_since_new,
2464 time_since_overhaul,
2465 cycles_since_overhaul,
2466 time_since_repair,
2467 cycles_since_repair,
2468 time_since_visit,
2469 cycles_since_visit,
2470 time_since_mark,
2471 cycles_since_mark,
2472 number_of_repairs
2473 )
2474 SELECT
2475 l_serial_temp_id,
2476 'RCV',
2477 l_prod_txn_id,
2478 sysdate,
2479 wsn_rec.last_updated_by,
2480 sysdate,
2481 wsn_rec.created_by,
2482 wsn_rec.last_update_login,
2483 wsn_rec.fm_serial_number,
2484 wsn_rec.to_serial_number,
2485 wsn_rec.attribute_category,
2486 wsn_rec.attribute1,
2487 wsn_rec.attribute2,
2488 wsn_rec.attribute3,
2489 wsn_rec.attribute4,
2490 wsn_rec.attribute5,
2491 wsn_rec.attribute6,
2492 wsn_rec.attribute7,
2493 wsn_rec.attribute8,
2494 wsn_rec.attribute9,
2495 wsn_rec.attribute10,
2496 wsn_rec.attribute11,
2497 wsn_rec.attribute12,
2498 wsn_rec.attribute13,
2499 wsn_rec.attribute14,
2500 wsn_rec.attribute15,
2501 wsn_rec.serial_attribute_category,
2502 wsn_rec.c_attribute1,
2503 wsn_rec.c_attribute2,
2504 wsn_rec.c_attribute3,
2505 wsn_rec.c_attribute4,
2506 wsn_rec.c_attribute5,
2507 wsn_rec.c_attribute6,
2508 wsn_rec.c_attribute7,
2509 wsn_rec.c_attribute8,
2510 wsn_rec.c_attribute9,
2511 wsn_rec.c_attribute10,
2512 wsn_rec.c_attribute11,
2513 wsn_rec.c_attribute12,
2514 wsn_rec.c_attribute13,
2515 wsn_rec.c_attribute14,
2516 wsn_rec.c_attribute15,
2517 wsn_rec.c_attribute16,
2518 wsn_rec.c_attribute17,
2519 wsn_rec.c_attribute18,
2520 wsn_rec.c_attribute19,
2521 wsn_rec.c_attribute20,
2522 wsn_rec.d_attribute1,
2523 wsn_rec.d_attribute2,
2524 wsn_rec.d_attribute3,
2525 wsn_rec.d_attribute4,
2526 wsn_rec.d_attribute5,
2527 wsn_rec.d_attribute6,
2528 wsn_rec.d_attribute7,
2529 wsn_rec.d_attribute8,
2530 wsn_rec.d_attribute9,
2531 wsn_rec.d_attribute10,
2532 wsn_rec.n_attribute1,
2533 wsn_rec.n_attribute2,
2534 wsn_rec.n_attribute3,
2535 wsn_rec.n_attribute4,
2536 wsn_rec.n_attribute5,
2537 wsn_rec.n_attribute6,
2538 wsn_rec.n_attribute7,
2539 wsn_rec.n_attribute8,
2540 wsn_rec.n_attribute9,
2541 wsn_rec.n_attribute10,
2542 wsn_rec.territory_code,
2543 wsn_rec.time_since_new,
2544 wsn_rec.cycles_since_new,
2545 wsn_rec.time_since_overhaul,
2546 wsn_rec.cycles_since_overhaul,
2547 wsn_rec.time_since_repair,
2548 wsn_rec.cycles_since_repair,
2549 wsn_rec.time_since_visit,
2550 wsn_rec.cycles_since_visit,
2551 wsn_rec.time_since_mark,
2552 wsn_rec.cycles_since_mark,
2553 wsn_rec.number_of_repairs
2554 FROM dual;
2555 --
2556 l_serial_inserted := TRUE;
2557 IF (g_asn_debug = 'Y') THEN
2558 asn_debug.put_line('Inserted MSNI : fm_serial_number : ' || wsn_rec.fm_serial_number || ' and to_serial_number : ' || wsn_rec.to_serial_number );
2559 END IF;
2560
2561 END LOOP;
2562 END IF;
2563 --
2564 IF (l_lot_inserted AND l_serial_inserted) THEN
2565 UPDATE mtl_transaction_lots_interface
2566 SET serial_transaction_temp_id = l_serial_temp_id
2567 WHERE transaction_interface_id = l_temp_id;
2568 END IF;
2569 --
2570 IF (g_asn_debug = 'Y') THEN
2571 asn_debug.put_line('Leaving load_lot_serial_interfaces');
2572 END IF;
2573
2574 EXCEPTION
2575 WHEN OTHERS THEN
2576 IF (g_asn_debug = 'Y') THEN
2577 asn_debug.put_line('Unexpected exception in load_lot_serial_interfaces : ' || SQLERRM);
2578 raise;
2579 END IF;
2580
2581 END load_lot_serial_interfaces;
2582
2583 /*===========================================================================
2584
2585 PROCEDURE NAME: perform_post_TM_updates ()
2586
2587 ===========================================================================*/
2588 PROCEDURE perform_post_TM_updates
2589 ( p_TM_source IN VARCHAR2,
2590 p_delivery_id IN NUMBER) IS
2591
2592 -- Cursor for picking successfully processed return RT lines
2593 CURSOR wdd_rt_cursor IS
2594 SELECT wdd.delivery_detail_id,
2595 wdd.inventory_item_id,
2596 wdd.shipped_quantity,
2597 wdd.requested_quantity_uom shipped_uom_code,
2598 wdd.shipped_quantity2,
2599 wdd.requested_quantity_uom2 shipped_uom_code2,
2600 rti.interface_transaction_id bkup_rti_id,
2601 rti.quantity bkup_rti_quantity,
2602 rti.unit_of_measure bkup_rti_uom,
2603 rti.primary_unit_of_measure bkup_rti_puom,
2604 rti.secondary_unit_of_measure bkup_rti_suom,
2605 rti.source_doc_unit_of_measure bkup_rti_src_uom
2606 FROM wsh_delivery_details wdd,
2607 wsh_delivery_assignments wda,
2608 rcv_transactions rt,
2609 rcv_transactions_interface rti
2610 WHERE wda.delivery_detail_id = wdd.delivery_detail_id
2611 AND wda.delivery_id = p_delivery_id
2612 AND wdd.source_code = 'RTV'
2613 AND wdd.released_status = 'C'
2614 AND wdd.inv_interfaced_flag <> 'Y'
2615 AND wdd.container_flag = 'N'
2616 AND wdd.delivery_detail_id = rt.interface_source_line_id
2617 AND rt.transaction_type = 'RETURN TO VENDOR'
2618 AND wdd.source_line_id = rti.interface_transaction_id
2619 AND rti.processing_status_code = 'WSH_INTERFACED'
2620 ORDER BY bkup_rti_id, delivery_detail_id
2621 FOR UPDATE;
2622
2623
2624 -- Cursor for picking successfully issued out MMT lines
2625 CURSOR wdd_mmt_cursor IS
2626 SELECT wdd.delivery_detail_id,
2627 wdd.inventory_item_id,
2628 wdd.shipped_quantity,
2629 wdd.requested_quantity_uom shipped_uom_code,
2630 wdd.shipped_quantity2,
2631 wdd.requested_quantity_uom2 shipped_uom_code2,
2632 rti.interface_transaction_id bkup_rti_id,
2633 rti.quantity bkup_rti_quantity,
2634 rti.unit_of_measure bkup_rti_uom,
2635 rti.primary_unit_of_measure bkup_rti_puom,
2636 rti.secondary_unit_of_measure bkup_rti_suom,
2637 rti.source_doc_unit_of_measure bkup_rti_src_uom
2638 FROM wsh_delivery_details wdd,
2639 wsh_delivery_assignments wda,
2640 mtl_material_transactions mmt,
2641 rcv_transactions_interface rti
2642 WHERE wda.delivery_detail_id = wdd.delivery_detail_id
2643 AND wda.delivery_id = p_delivery_id
2644 AND wdd.source_code = 'RTV'
2645 AND wdd.released_status = 'C'
2646 AND wdd.inv_interfaced_flag <> 'Y'
2647 AND wdd.container_flag = 'N'
2648 AND wdd.delivery_detail_id = mmt.picking_line_id
2649 AND wdd.source_line_id = rti.interface_transaction_id
2650 AND rti.processing_status_code = 'WSH_INTERFACED'
2651 ORDER BY bkup_rti_id, delivery_detail_id
2652 FOR UPDATE;
2653
2654 pre_rti_id NUMBER := NULL; --RTV project phase 2
2655 pre_rti_qty NUMBER := NULL; --RTV project phase 2
2656 BEGIN
2657 IF (p_TM_source = 'RCV') THEN
2658 asn_debug.put_line('perform_post_TM_updates for returned RTs');
2659 FOR c_rec IN wdd_rt_cursor LOOP
2660 --RTV project phase 2 : start
2661 IF (c_rec.bkup_rti_id = pre_rti_id) THEN
2662 c_rec.bkup_rti_quantity := pre_rti_qty;
2663 END IF;
2664 --RTV project phase 2 : end
2665 adjust_rcv_quantities
2666 ( p_delivery_detail_id => c_rec.delivery_detail_id,
2667 p_item_id => c_rec.inventory_item_id,
2668 p_wdd_shipped_qty => c_rec.shipped_quantity,
2669 p_wdd_shipped_uom_code => c_rec.shipped_uom_code,
2670 p_wdd_shipped_qty2 => c_rec.shipped_quantity2,
2671 p_wdd_shipped_uom_code2 => c_rec.shipped_uom_code2,
2672 p_bkup_rti_id => c_rec.bkup_rti_id,
2673 p_bkup_rti_quantity => c_rec.bkup_rti_quantity,
2674 p_bkup_rti_uom => c_rec.bkup_rti_uom,
2675 p_bkup_rti_puom => c_rec.bkup_rti_puom,
2676 p_bkup_rti_suom => c_rec.bkup_rti_suom,
2677 p_bkup_rti_src_uom => c_rec.bkup_rti_src_uom);
2678 pre_rti_id := c_rec.bkup_rti_id; --RTV project phase 2
2679 pre_rti_qty := c_rec.bkup_rti_quantity; --RTV project phase 2
2680 END LOOP;
2681
2682 ELSIF (p_TM_source = 'INV') THEN
2683 asn_debug.put_line('perform_post_TM_updates for issued out MMTs');
2684 FOR c_rec IN wdd_mmt_cursor LOOP
2685 --RTV project phase 2 : start
2686 IF (c_rec.bkup_rti_id = pre_rti_id) THEN
2687 c_rec.bkup_rti_quantity := pre_rti_qty;
2688 END IF;
2689 --RTV project phase 2 : end
2690 adjust_rcv_quantities
2691 ( p_delivery_detail_id => c_rec.delivery_detail_id,
2692 p_item_id => c_rec.inventory_item_id,
2693 p_wdd_shipped_qty => c_rec.shipped_quantity,
2694 p_wdd_shipped_uom_code => c_rec.shipped_uom_code,
2695 p_wdd_shipped_qty2 => c_rec.shipped_quantity2,
2696 p_wdd_shipped_uom_code2 => c_rec.shipped_uom_code2,
2697 p_bkup_rti_id => c_rec.bkup_rti_id,
2698 p_bkup_rti_quantity => c_rec.bkup_rti_quantity,
2699 p_bkup_rti_uom => c_rec.bkup_rti_uom,
2700 p_bkup_rti_puom => c_rec.bkup_rti_puom,
2701 p_bkup_rti_suom => c_rec.bkup_rti_suom,
2702 p_bkup_rti_src_uom => c_rec.bkup_rti_src_uom);
2703 pre_rti_id := c_rec.bkup_rti_id; --RTV project phase 2
2704 pre_rti_qty := c_rec.bkup_rti_quantity; --RTV project phase 2
2705 END LOOP;
2706 END IF;
2707
2708 EXCEPTION
2709 WHEN OTHERS THEN
2710 IF (g_asn_debug = 'Y') THEN
2711 asn_debug.put_line('Unexpected exception in adjust_lot_data : ' || SQLERRM);
2712 raise;
2713 END IF;
2714 END perform_post_TM_updates;
2715
2716 /*===========================================================================
2717
2718 PROCEDURE NAME: adjust_rcv_quantities ()
2719
2720 ===========================================================================*/
2721 PROCEDURE adjust_rcv_quantities
2722 ( p_delivery_detail_id IN NUMBER ,
2723 p_item_id IN NUMBER ,
2724 p_wdd_shipped_qty IN NUMBER ,
2725 p_wdd_shipped_uom_code IN VARCHAR2 ,
2726 p_wdd_shipped_qty2 IN NUMBER ,
2727 p_wdd_shipped_uom_code2 IN VARCHAR2 ,
2728 p_bkup_rti_id IN NUMBER ,
2729 p_bkup_rti_quantity IN OUT NOCOPY NUMBER , --RTV project phase 2
2730 p_bkup_rti_uom IN VARCHAR2 ,
2731 p_bkup_rti_puom IN VARCHAR2 ,
2732 p_bkup_rti_suom IN VARCHAR2 ,
2733 p_bkup_rti_src_uom IN VARCHAR2 ) IS
2734
2735
2736 l_shipped_uom mtl_units_of_measure.unit_of_measure%TYPE;
2737 l_shipped_sec_uom mtl_units_of_measure.unit_of_measure%TYPE;
2738 l_txn_qty NUMBER;
2739 l_shipped_qty NUMBER;
2740 l_shipped_qty2 NUMBER := 0;
2741 l_primary_qty NUMBER;
2742 l_src_uom_qty NUMBER := NULL;
2743 e_Overship_Error EXCEPTION;
2744 -- RTV project phase 2 : start
2745 e_WMS_post_Error EXCEPTION;
2746 l_return_status VARCHAR2(1);
2747 l_msg_count NUMBER;
2748 l_msg_data VARCHAR2(2000);
2749 l_ship_flag VARCHAR2(10);
2750 -- RTV project phase 2 : end
2751
2752 BEGIN
2753 --
2754 IF (g_asn_debug = 'Y') THEN
2755 asn_debug.put_line('p_delivery_detail_id : ' || p_delivery_detail_id);
2756 asn_debug.put_line('p_item_id : ' || p_item_id);
2757 asn_debug.put_line('p_wdd_shipped_qty : ' || p_wdd_shipped_qty);
2758 asn_debug.put_line('p_wdd_shipped_uom_code : ' || p_wdd_shipped_uom_code);
2759 asn_debug.put_line('p_wdd_shipped_uom_code2 : ' || p_wdd_shipped_uom_code2);
2760 asn_debug.put_line('p_bkup_rti_id : ' || p_bkup_rti_id);
2761 asn_debug.put_line('p_bkup_rti_quantity : ' || p_bkup_rti_quantity);
2762 asn_debug.put_line('p_bkup_rti_uom : ' || p_bkup_rti_uom);
2763 asn_debug.put_line('p_bkup_rti_puom : ' || p_bkup_rti_puom);
2764 asn_debug.put_line('p_bkup_rti_suom : ' || p_bkup_rti_suom);
2765 asn_debug.put_line('p_bkup_rti_src_uom : ' || p_bkup_rti_src_uom);
2766 END IF;
2767 --
2768 l_shipped_uom := get_uom_from_code (p_wdd_shipped_uom_code);
2769 IF (g_asn_debug = 'Y') THEN
2770 asn_debug.put_line('l_shipped_uom : ' || l_shipped_uom);
2771 END IF;
2772 --
2773 IF (l_shipped_uom <> p_bkup_rti_uom) THEN
2774 po_uom_s.uom_convert
2775 ( from_quantity => p_wdd_shipped_qty,
2776 from_uom => l_shipped_uom,
2777 item_id => p_item_id,
2778 to_uom => p_bkup_rti_uom,
2779 to_quantity => l_shipped_qty);
2780 ELSE
2781 l_shipped_qty := p_wdd_shipped_qty;
2782 END IF;
2783
2784 l_txn_qty := p_bkup_rti_quantity - l_shipped_qty;
2785 p_bkup_rti_quantity := l_txn_qty; --RTV project phase 2
2786
2787 IF (g_asn_debug = 'Y') THEN
2788 asn_debug.put_line('l_shipped_qty : ' || l_shipped_qty);
2789 asn_debug.put_line('l_txn_qty : ' || l_txn_qty);
2790 END IF;
2791 --
2792 IF (l_txn_qty > 0) THEN
2793
2794 IF (l_shipped_uom <> p_bkup_rti_puom) THEN
2795 po_uom_s.uom_convert
2796 ( from_quantity => p_wdd_shipped_qty,
2797 from_uom => l_shipped_uom,
2798 item_id => p_item_id,
2799 to_uom => p_bkup_rti_puom,
2800 to_quantity => l_primary_qty);
2801 ELSE
2802 l_primary_qty := p_wdd_shipped_qty;
2803 END IF;
2804 IF (g_asn_debug = 'Y') THEN
2805 asn_debug.put_line('p_bkup_rti_puom : ' || p_bkup_rti_puom);
2806 asn_debug.put_line('l_primary_qty : ' || l_primary_qty);
2807 END IF;
2808 --
2809 l_shipped_sec_uom := get_uom_from_code (p_wdd_shipped_uom_code2);
2810 IF (g_asn_debug = 'Y') THEN
2811 asn_debug.put_line('l_shipped_sec_uom : ' || l_shipped_sec_uom);
2812 END IF;
2813
2814 IF (p_bkup_rti_suom IS NOT NULL) THEN
2815 IF (l_shipped_sec_uom <> p_bkup_rti_suom) THEN
2816 po_uom_s.uom_convert
2817 ( from_quantity => p_wdd_shipped_qty2,
2818 from_uom => l_shipped_sec_uom,
2819 item_id => p_item_id,
2820 to_uom => p_bkup_rti_suom,
2821 to_quantity => l_shipped_qty2);
2822 ELSE
2823 l_shipped_qty2 := p_wdd_shipped_qty2;
2824 END IF;
2825 END IF;
2826
2827 IF (g_asn_debug = 'Y') THEN
2828 asn_debug.put_line('p_bkup_rti_suom : ' || p_bkup_rti_suom);
2829 asn_debug.put_line('l_shipped_qty2 : ' || l_shipped_qty2);
2830 END IF;
2831 --
2832 IF (p_bkup_rti_src_uom IS NOT NULL) THEN
2833 IF (nvl(p_bkup_rti_uom, -99) <> nvl(p_bkup_rti_src_uom,-99)) THEN
2834 po_uom_s.uom_convert
2835 ( from_quantity => l_txn_qty,
2836 from_uom => p_bkup_rti_uom,
2837 item_id => p_item_id,
2838 to_uom => p_bkup_rti_src_uom,
2839 to_quantity => l_src_uom_qty);
2840 ELSE
2841 l_src_uom_qty := l_txn_qty;
2842 END IF;
2843 END IF;
2844
2845 IF (g_asn_debug = 'Y') THEN
2846 asn_debug.put_line('l_src_uom_qty : ' || l_src_uom_qty);
2847 END IF;
2848 --
2849 UPDATE rcv_transactions_interface
2850 SET quantity = l_txn_qty,
2851 secondary_quantity = secondary_quantity - l_shipped_qty2,
2852 primary_quantity = primary_quantity - l_primary_qty,
2853 source_doc_quantity = l_src_uom_qty
2854 WHERE interface_transaction_id = p_bkup_rti_id;
2855 l_ship_flag := 'PARTIAL';
2856
2857 ELSIF (l_txn_qty = 0) THEN
2858 --RTV project phase 2 : start
2859 --move original code into a new private procedure,as cancellation also use that.
2860 remove_RTV_order(p_bkup_rti_id);
2861 l_ship_flag := 'FULL';
2862 --RTV project phase 2 : end
2863 ELSE
2864 raise e_Overship_Error;
2865 END IF;
2866 -- RTV project phase 2 : start
2867 wms_return_sv.perform_post_TM_wms_updates
2868 (x_return_status => l_return_status,
2869 x_msg_count => l_msg_count,
2870 x_msg_data => l_msg_data,
2871 p_rcv_trx_interface_id => p_bkup_rti_id,
2872 p_ship_flag => l_ship_flag);
2873 IF (l_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
2874 raise e_WMS_post_Error;
2875 END IF;
2876 -- RTV project phase 2 : end
2877 --
2878 EXCEPTION
2879 WHEN e_Overship_Error THEN
2880 IF (g_asn_debug = 'Y') THEN
2881 asn_debug.put_line('Overship error');
2882 raise;
2883 END IF;
2884 --RTV project phase 2 : start
2885 WHEN e_WMS_post_Error THEN
2886 IF (g_asn_debug = 'Y') THEN
2887 asn_debug.put_line('Unexpected exception in adjust_lot_data while calling wms_return_sv.perform_post_TM_wms_updates');
2888 raise;
2889 END IF;
2890 --RTV project phase 2 : end
2891 WHEN OTHERS THEN
2892 IF (g_asn_debug = 'Y') THEN
2893 asn_debug.put_line('Unexpected exception in adjust_lot_data : ' || SQLERRM);
2894 raise;
2895 END IF;
2896 END adjust_rcv_quantities;
2897
2898 /*===========================================================================
2899
2900 PROCEDURE NAME: clean_up_after_rtp ()
2901
2902 ===========================================================================*/
2903 PROCEDURE clean_up_after_rtp
2904 ( p_delivery_id IN NUMBER,
2905 p_group_id IN NUMBER) IS
2906
2907 BEGIN
2908
2909 -- Deleting errored RTI/MSNI/MTLI
2910 BEGIN
2911 DELETE FROM mtl_serial_numbers_interface
2912 WHERE product_transaction_id IN
2913 (SELECT interface_transaction_id
2914 FROM rcv_transactions_interface
2915 WHERE group_id = p_group_id
2916 AND processing_mode_code = 'ONLINE');
2917
2918 EXCEPTION
2919 WHEN OTHERS THEN NULL;
2920 END;
2921 asn_debug.put_line('After deleting MSNI : count = ' || sql%rowcount);
2922 --
2923 BEGIN
2924 DELETE FROM mtl_transaction_lots_interface
2925 WHERE product_transaction_id IN
2926 (SELECT interface_transaction_id
2927 FROM rcv_transactions_interface
2928 WHERE group_id = p_group_id
2929 AND processing_mode_code = 'ONLINE');
2930
2931 EXCEPTION
2932 WHEN OTHERS THEN NULL;
2933 END;
2934 asn_debug.put_line('After deleting MTLI : count = ' || sql%rowcount);
2935 --
2936 DELETE FROM rcv_transactions_interface
2937 WHERE group_id = p_group_id
2938 AND processing_mode_code = 'ONLINE';
2939 asn_debug.put_line('After deleting RTI : count = ' || sql%rowcount);
2940 --
2941 EXCEPTION
2942 WHEN OTHERS THEN
2943 IF (g_asn_debug = 'Y') THEN
2944 asn_debug.put_line('Unexpected exception in clean_up_after_rtp : ' || SQLERRM);
2945 raise;
2946 END IF;
2947 END clean_up_after_rtp;
2948
2949 /*===========================================================================
2950
2951 PROCEDURE NAME: cancel_rtv_lines ()
2952
2953 ===========================================================================*/
2954 PROCEDURE cancel_rtv_lines
2955 ( p_rti_id_tbl IN RCV_WSH_INTERFACE_PKG.RTI_id_tbl ) IS
2956
2957 l_changed_attributes WSH_INTERFACE.ChangedAttributeTabType;
2958 l_wdd_cancel_qty NUMBER;
2959 l_return_status VARCHAR2(1);
2960 l_msg_count NUMBER;
2961 l_msg_data VARCHAR2(2000);
2962 l_source_header_id NUMBER;
2963 l_item_id NUMBER;
2964 l_rti_qty NUMBER;
2965 l_rti_uom VARCHAR2(25);
2966 l_rti_puom VARCHAR2(25);
2967 l_rti_suom VARCHAR2(25);
2968 l_rti_src_uom VARCHAR2(25);
2969 l_wdd_uom_code VARCHAR2(3);
2970 l_wdd_uom VARCHAR2(25);
2971 l_rti_cancel_qty NUMBER;
2972 l_rti_new_qty NUMBER;
2973 l_rti_new_pqty NUMBER := NULL;
2974 l_rti_new_sqty NUMBER := NULL;
2975 l_rti_new_src_qty NUMBER := NULL;
2976 e_cancel_error1 EXCEPTION; -- Bug 10089980
2977 e_cancel_error2 EXCEPTION; -- Bug 10089980
2978 e_cancel_MR_error EXCEPTION; -- RTV2 rtv project phase 2
2979 e_cancel_lpn_wdd_error EXCEPTION; -- RTV2 rtv project phase 2
2980 e_cancel_unmark_lpn_error EXCEPTION; -- RTV2 rtv project phase 2
2981 e_cancel_unpack_lpn_error EXCEPTION; -- RTV2 rtv project phase 2
2982
2983 CURSOR cancelled_wdd_cur (p_src_line_id NUMBER) IS
2984 SELECT *
2985 FROM wsh_delivery_details
2986 WHERE source_code = 'RTV'
2987 AND source_line_id = p_src_line_id
2988 AND released_status = 'D'
2989 AND container_flag = 'N';
2990
2991 -- RTV2 rtv project phase 2 : start
2992 CURSOR cancelled_lpn_cur (p_src_line_id NUMBER) IS
2993 SELECT distinct wdd1.lpn_id,
2994 wdd2.delivery_detail_id,
2995 wdd1.delivery_detail_id lpn_wdd_id
2996 FROM wsh_delivery_details wdd1,
2997 wsh_delivery_Details wdd2,
2998 wsh_delivery_assignments wda
2999 WHERE wdd2.source_code = 'RTV'
3000 AND wdd2.source_line_id = p_src_line_id
3001 AND wdd1.container_flag = 'Y'
3002 AND wdd1.lpn_id is not null
3003 AND wdd2.container_flag = 'N'
3004 AND wdd1.delivery_detail_id = wda.parent_delivery_detail_id
3005 AND wdd2.delivery_detail_id = wda.delivery_detail_id
3006 ORDER BY lpn_wdd_id;
3007
3008 TYPE content_wdd_lpns IS TABLE OF cancelled_lpn_cur%ROWTYPE INDEX BY BINARY_INTEGER;
3009 l_wdd_lpns content_wdd_lpns;
3010 l_lpn_id NUMBER := NULL;
3011 l_transfer_lpn_id NUMBER := NULL;
3012 l_count NUMBER := NULL;
3013 l_rtv_order NUMBER := -1;
3014 -- RTV2 rtv project phase 2 : end
3015
3016 BEGIN
3017 IF (g_asn_debug = 'Y') THEN
3018 asn_debug.put_line('Entering cancel_rtv_lines');
3019 END IF;
3020
3021 FOR i IN 1 .. p_rti_id_tbl.COUNT LOOP
3022 wsh_integration.get_cancel_qty_allowed
3023 ( p_source_code => 'RTV',
3024 p_source_line_id => p_rti_id_tbl(i),
3025 x_cancel_qty_allowed => l_wdd_cancel_qty,
3026 x_return_status => l_return_status,
3027 x_msg_count => l_msg_count,
3028 x_msg_data => l_msg_data );
3029
3030 IF l_return_status = 'S' THEN
3031 IF (g_asn_debug = 'Y') THEN
3032 asn_debug.put_line('l_wdd_cancel_qty : ' || l_wdd_cancel_qty);
3033 END IF;
3034
3035 IF l_wdd_cancel_qty > 0 THEN
3036 SELECT item_id,
3037 quantity,
3038 unit_of_measure,
3039 primary_unit_of_measure,
3040 secondary_unit_of_measure,
3041 source_doc_unit_of_measure,
3042 transfer_lpn_id, -- RTV2 rtv project phase 2
3043 group_id -- RTV2 rtv project phase 2
3044 INTO l_item_id,
3045 l_rti_qty,
3046 l_rti_uom,
3047 l_rti_puom,
3048 l_rti_suom,
3049 l_rti_src_uom,
3050 l_transfer_lpn_id, -- RTV2 rtv project phase 2
3051 l_rtv_order -- RTV2 rtv project phase 2
3052 FROM rcv_transactions_interface
3053 WHERE interface_transaction_id = p_rti_id_tbl(i)
3054 AND transaction_type = 'RETURN TO VENDOR';
3055
3056 SELECT max(source_header_id), max(requested_quantity_uom)
3057 INTO l_source_header_id,
3058 l_wdd_uom_code
3059 FROM wsh_delivery_details
3060 WHERE source_line_id = p_rti_id_tbl(i)
3061 AND source_code = 'RTV';
3062
3063 IF (g_asn_debug = 'Y') THEN
3064 asn_debug.put_line('l_item_id : ' || l_item_id);
3065 asn_debug.put_line('l_rti_qty : ' || l_rti_qty );
3066 asn_debug.put_line('l_rti_uom : ' || l_rti_uom);
3067 asn_debug.put_line('l_rti_puom : ' || l_rti_puom);
3068 asn_debug.put_line('l_rti_suom : ' || l_rti_suom);
3069 asn_debug.put_line('l_rti_src_uom : ' || l_rti_src_uom);
3070 asn_debug.put_line('l_source_header_id : ' || l_source_header_id);
3071 asn_debug.put_line('l_wdd_uom_code : ' || l_wdd_uom_code);
3072 END IF;
3073
3074 l_wdd_uom := get_uom_from_code (l_wdd_uom_code);
3075
3076 IF (g_asn_debug = 'Y') THEN
3077 asn_debug.put_line('l_wdd_uom : ' || l_wdd_uom);
3078 END IF;
3079
3080 l_changed_attributes(1).source_code := 'RTV';
3081 l_changed_attributes(1).source_header_id := l_source_header_id;
3082 l_changed_attributes(1).source_line_id := p_rti_id_tbl(i);
3083 l_changed_attributes(1).ordered_quantity := 0;
3084 l_changed_attributes(1).order_quantity_uom := l_wdd_uom_code;
3085 l_changed_attributes(1).shipped_flag := 'N';
3086 l_changed_attributes(1).action_flag := 'U';
3087
3088 --RTV2 rtv project phase 2 : start
3089 --Before cancelling , keep association between content wdd and lpns.
3090 IF (l_transfer_lpn_id IS NOT NULL) THEN
3091 IF (g_asn_debug = 'Y') THEN
3092 asn_debug.put_line('open cursor cancelled_lpn_cur before cancellation. ' );
3093 END IF;
3094 OPEN cancelled_lpn_cur(p_rti_id_tbl(i));
3095 FETCH cancelled_lpn_cur BULK COLLECT INTO l_wdd_lpns;
3096 CLOSE cancelled_lpn_cur;
3097 END IF;
3098 --RTV2 rtv project phase 2 : end
3099
3100
3101 WSH_INTERFACE.Update_Shipping_Attributes
3102 ( p_source_code => 'RTV',
3103 p_changed_attributes => l_changed_attributes,
3104 x_return_status => l_return_status);
3105
3106 IF (l_return_status = 'S') THEN
3107 --
3108 FOR wdd_rec IN cancelled_wdd_cur (p_rti_id_tbl(i)) LOOP
3109 wdd_rec.shipped_quantity := wdd_rec.cancelled_quantity;
3110 wdd_rec.lot_number := wdd_rec.original_lot_number;
3111 wdd_rec.subinventory := wdd_rec.original_subinventory;
3112 wdd_rec.locator_id := wdd_rec.original_locator_id;
3113 wdd_rec.revision := wdd_rec.original_revision;
3114
3115 --RTV2 rtv project phase 2 : start
3116 l_lpn_id := null;
3117 IF(l_transfer_lpn_id IS NOT NULL) THEN
3118 FOR indx IN 1 .. l_wdd_lpns.COUNT LOOP
3119 IF( wdd_rec.delivery_detail_id = l_wdd_lpns(indx).delivery_detail_id ) THEN
3120 l_lpn_id := l_wdd_lpns(indx).lpn_id;
3121 IF (g_asn_debug = 'Y') THEN
3122 asn_debug.put_line('before calling wms_return_sv.unmark_returns for lpn_id:'|| l_lpn_id);
3123 END IF;
3124 --we should pass lpn_id here, since we are not able to fetch lpn_id
3125 --from wsh_delivery_assignments
3126 unmark_wdd_lpn(p_wdd_rec => wdd_rec,
3127 p_lpn_id => l_lpn_id,
3128 x_return_status => l_return_status,
3129 x_msg_count => l_msg_count,
3130 x_msg_data => l_msg_data);
3131 IF (l_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
3132 raise e_cancel_unmark_lpn_error;
3133 END IF;
3134 --check if relevant contaienr wdd is fully cancelled , if yes, delete it.
3135 SELECT count(1)
3136 INTO l_count
3137 FROM wsh_delivery_assignments
3138 WHERE parent_delivery_detail_id = l_wdd_lpns(indx).lpn_wdd_id;
3139 IF( l_count = 0 ) THEN
3140 IF (g_asn_debug = 'Y') THEN
3141 asn_debug.put_line('before deleting container wdd :'||l_wdd_lpns(indx).lpn_wdd_id);
3142 END IF;
3143 wsh_container_actions.delete_containers
3144 (p_container_id => l_wdd_lpns(indx).lpn_wdd_id,
3145 x_return_status => l_return_status);
3146 IF (l_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
3147 raise e_cancel_lpn_wdd_error;
3148 END IF;
3149 END IF;
3150 EXIT;
3151 END IF;
3152 END LOOP;
3153 END IF;
3154 --we should pass lpn_id here, since we are not able to fetch lpn_id
3155 --from wsh_delivery_assignments
3156 relieve_return_reservation(p_wdd_rec => wdd_rec,
3157 p_lpn_id => l_lpn_id,
3158 x_return_status => l_return_status,
3159 x_msg_count => l_msg_count,
3160 x_msg_data => l_msg_data);
3161 IF (l_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
3162 raise e_cancel_MR_error;
3163 END IF;
3164 --RTV2 rtv project phase 2 : end
3165 END LOOP;
3166 IF (g_asn_debug = 'Y') THEN
3167 asn_debug.put_line('After relieve_return_reservation Loop');
3168 END IF;
3169 --
3170 IF (l_rti_uom <> l_wdd_uom) THEN
3171 po_uom_s.uom_convert
3172 ( from_quantity => l_wdd_cancel_qty,
3173 from_uom => l_wdd_uom,
3174 item_id => l_item_id,
3175 to_uom => l_rti_uom,
3176 to_quantity => l_rti_cancel_qty);
3177 ELSE
3178 l_rti_cancel_qty := l_wdd_cancel_qty;
3179 END IF;
3180
3181 l_rti_new_qty := l_rti_qty - l_rti_cancel_qty;
3182 IF (g_asn_debug = 'Y') THEN
3183 asn_debug.put_line('l_rti_new_qty : ' || l_rti_new_qty);
3184 END IF;
3185 --
3186 IF (l_rti_new_qty > 0) THEN
3187 --
3188 IF (l_rti_uom <> l_rti_puom) THEN
3189 po_uom_s.uom_convert
3190 ( from_quantity => l_rti_new_qty,
3191 from_uom => l_rti_uom,
3192 item_id => l_item_id,
3193 to_uom => l_rti_puom,
3194 to_quantity => l_rti_new_pqty);
3195 ELSE
3196 l_rti_new_pqty := l_rti_new_qty;
3197 END IF;
3198
3199 IF (g_asn_debug = 'Y') THEN
3200 asn_debug.put_line('l_rti_new_pqty : ' || l_rti_new_pqty);
3201 END IF;
3202 --
3203 IF (l_rti_suom IS NOT NULL) THEN
3204 IF (l_rti_uom <> l_rti_suom) THEN
3205 po_uom_s.uom_convert
3206 ( from_quantity => l_rti_new_qty,
3207 from_uom => l_rti_uom,
3208 item_id => l_item_id,
3209 to_uom => l_rti_suom,
3210 to_quantity => l_rti_new_sqty);
3211 ELSE
3212 l_rti_new_sqty := l_rti_new_qty;
3213 END IF;
3214 END IF;
3215
3216 IF (g_asn_debug = 'Y') THEN
3217 asn_debug.put_line('l_rti_new_sqty : ' || l_rti_new_sqty);
3218 END IF;
3219 --
3220 IF (l_rti_src_uom IS NOT NULL) THEN
3221
3222 IF (l_rti_uom <> l_rti_src_uom) THEN
3223 po_uom_s.uom_convert
3224 ( from_quantity => l_rti_new_qty,
3225 from_uom => l_rti_uom,
3226 item_id => l_item_id,
3227 to_uom => l_rti_src_uom,
3228 to_quantity => l_rti_new_src_qty);
3229 ELSE
3230 l_rti_new_src_qty := l_rti_new_qty;
3231 END IF;
3232 END IF;
3233
3234 IF (g_asn_debug = 'Y') THEN
3235 asn_debug.put_line('l_rti_new_src_qty : ' || l_rti_new_src_qty);
3236 END IF;
3237 --
3238 UPDATE rcv_transactions_interface
3239 SET quantity = l_rti_new_qty,
3240 primary_quantity = l_rti_new_pqty,
3241 secondary_quantity = l_rti_new_sqty,
3242 source_doc_quantity = l_rti_new_src_qty
3243 WHERE interface_transaction_id = p_rti_id_tbl(i);
3244 ELSE
3245 --RTV project phase 2 : start
3246 --call remove_RTV_order() instead of delete RTI directly, as we also need to handle
3247 --serial and lot interface tables.
3248 --DELETE FROM rcv_transactions_interface
3249 --WHERE interface_transaction_id = p_rti_id_tbl(i);
3250 remove_RTV_order(p_rti_id_tbl(i));
3251 --RTV project phase 2 : end
3252 END IF;
3253 --
3254 ELSE
3255 raise e_cancel_error2; -- Bug 10089980
3256 END IF;
3257 END IF;
3258 ELSE
3259 raise e_cancel_error1; -- Bug 10089980
3260 END IF;
3261
3262 END LOOP;
3263 --RTV project phase 2 : start
3264 IF (l_rtv_order <> -1) THEN
3265 wms_return_sv.cancel_return_order_RTV
3266 (x_return_status => l_return_status,
3267 x_msg_count => l_msg_count,
3268 x_msg_data => l_msg_data,
3269 p_rtv_order => l_rtv_order);
3270 IF (l_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
3271 raise e_cancel_unpack_lpn_error;
3272 END IF;
3273 END IF;
3274 --RTV project phase 2 : end
3275
3276
3277 IF (g_asn_debug = 'Y') THEN
3278 asn_debug.put_line('Leaving cancel_rtv_line');
3279 END IF;
3280
3281 EXCEPTION
3282 -- Bug 10089980 : Start
3283 WHEN e_cancel_error1 THEN
3284 IF (g_asn_debug = 'Y') THEN
3285 asn_debug.put_line('wsh_integration.get_cancel_qty_allowed returned error!');
3286 END IF;
3287 l_msg_data := fnd_msg_pub.get (1, 'F');
3288 po_message_s.sql_error('wsh_integration.get_cancel_qty_allowed', l_msg_data, sqlcode);
3289 raise fnd_api.g_exc_error;
3290
3291 WHEN e_cancel_error2 THEN
3292 IF (g_asn_debug = 'Y') THEN
3293 asn_debug.put_line('wsh_interface.update_shipping_attributes returned error!');
3294 END IF;
3295 fnd_msg_pub.count_and_get (p_encoded => 'T',
3296 p_count => l_msg_count,
3297 p_data => l_msg_data
3298 );
3299
3300 FOR x IN 1 .. l_msg_count LOOP
3301 l_msg_data := fnd_msg_pub.get (x, 'F');
3302 END LOOP;
3303
3304 po_message_s.sql_error('wsh_interface.update_shipping_attributes', l_msg_data, sqlcode);
3305 raise fnd_api.g_exc_error;
3306 -- Bug 10089980 : End
3307 -- RTV project phase 2 : start
3308 WHEN e_cancel_MR_error THEN
3309 IF (g_asn_debug = 'Y') THEN
3310 asn_debug.put_line('inv_trx_relief_c_pvt.rsv_relief returned error!');
3311 END IF;
3312 fnd_msg_pub.count_and_get (p_encoded => 'T',
3313 p_count => l_msg_count,
3314 p_data => l_msg_data
3315 );
3316
3317 FOR x IN 1 .. l_msg_count LOOP
3318 l_msg_data := fnd_msg_pub.get (x, 'F');
3319 END LOOP;
3320
3321 po_message_s.sql_error('rcv_wsh_interface_pkg.cancel_rtv_lines', l_msg_data, sqlcode);
3322 raise fnd_api.g_exc_error;
3323
3324 WHEN e_cancel_unmark_lpn_error THEN
3325 IF (g_asn_debug = 'Y') THEN
3326 asn_debug.put_line('wms_return_sv.unmark_returns returned error!');
3327 END IF;
3328 fnd_msg_pub.count_and_get (p_encoded => 'T',
3329 p_count => l_msg_count,
3330 p_data => l_msg_data
3331 );
3332
3333 FOR x IN 1 .. l_msg_count LOOP
3334 l_msg_data := fnd_msg_pub.get (x, 'F');
3335 END LOOP;
3336
3337 po_message_s.sql_error('rcv_wsh_interface_pkg.cancel_rtv_lines', l_msg_data, sqlcode);
3338 raise fnd_api.g_exc_error;
3339
3340 WHEN e_cancel_unpack_lpn_error THEN
3341 IF (g_asn_debug = 'Y') THEN
3342 asn_debug.put_line('wms_return_sv.cancel_return_order_RTV returned error!');
3343 END IF;
3344 fnd_msg_pub.count_and_get (p_encoded => 'T',
3345 p_count => l_msg_count,
3346 p_data => l_msg_data
3347 );
3348
3349 FOR x IN 1 .. l_msg_count LOOP
3350 l_msg_data := fnd_msg_pub.get (x, 'F');
3351 END LOOP;
3352
3353 po_message_s.sql_error('rcv_wsh_interface_pkg.cancel_rtv_lines', l_msg_data, sqlcode);
3354 raise fnd_api.g_exc_error;
3355
3356 WHEN e_cancel_lpn_wdd_error THEN
3357 IF (g_asn_debug = 'Y') THEN
3358 asn_debug.put_line('whs_container_actions.delete_containers returned error!');
3359 END IF;
3360 l_msg_data := fnd_msg_pub.get (1, 'F');
3361 po_message_s.sql_error('rcv_wsh_interface_pkg.cancel_rtv_lines', l_msg_data, sqlcode);
3362 raise fnd_api.g_exc_error;
3363 -- RTV project phase 2 : end
3364
3365 WHEN OTHERS THEN
3366 IF (g_asn_debug = 'Y') THEN
3367 asn_debug.put_line('Unexpected exception in cancel_rtv_lines : ' || SQLERRM);
3368 END IF;
3369 po_message_s.sql_error('rcv_wsh_interface_pkg.cancel_rtv_lines', 'Unexpected exception', sqlcode);
3370 raise fnd_api.g_exc_unexpected_error;
3371 END cancel_rtv_lines;
3372
3373 END RCV_WSH_INTERFACE_PKG;