[Home] [Help]
PACKAGE BODY: APPS.INV_RCV_RESERVATION_UTIL
Source
1 PACKAGE BODY INV_RCV_RESERVATION_UTIL AS
2 /* $Header: INVRUTLB.pls 120.33.12020000.4 2012/12/19 14:08:22 ssingams ship $*/
3
4 g_source_type_oe NUMBER := inv_reservation_global.g_source_type_oe;
5 g_source_type_po NUMBER := inv_reservation_global.g_source_type_po;
6 g_source_type_asn NUMBER := inv_reservation_global.g_source_type_asn;
7 g_source_type_internal_req NUMBER := inv_reservation_global.g_source_type_internal_req;
8 g_source_type_internal_ord NUMBER := inv_reservation_global.g_source_type_internal_ord;
9 g_source_type_in_transit NUMBER := inv_reservation_global.g_source_type_intransit;
10 g_source_type_inv NUMBER := inv_reservation_global.g_source_type_inv;
11 g_source_type_rcv NUMBER := inv_reservation_global.g_source_type_rcv;
12 g_source_type_wip NUMBER := inv_reservation_global.g_source_type_wip;
13
14 g_query_demand_ship_date_desc NUMBER := inv_reservation_global.g_query_demand_ship_date_desc;
15 g_query_demand_ship_date_asc NUMBER := inv_reservation_global.g_query_demand_ship_date_asc ;
16
17 PROCEDURE print_debug(p_err_msg VARCHAR2
18 ,p_module IN VARCHAR2
19 ,p_level NUMBER DEFAULT 4)
20 IS
21 l_debug NUMBER;
22 BEGIN
23 l_debug := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
24 inv_mobile_helper_functions.tracelog
25 (p_err_msg => p_err_msg
26 ,p_module => 'INV_RCV_RESERVATION_UTIL.'||p_module
27 ,p_level => p_level);
28 END;
29
30 PROCEDURE update_wdd
31 (x_return_status OUT NOCOPY VARCHAR2
32 ,x_msg_count OUT NOCOPY NUMBER
33 ,x_msg_data OUT NOCOPY VARCHAR2
34 ,p_wdd_id IN NUMBER
35 ,p_released_status IN VARCHAR2
36 ,p_mol_id IN NUMBER
37 ) IS
38
39 --l_detail_info_tab wsh_glbl_var_strct_grp.delivery_details_attr_tbl_type;
40 --l_in_rec wsh_glbl_var_strct_grp.detailinrectype;
41 --l_out_Rec wsh_glbl_var_strct_grp.detailoutrectype;
42
43 l_detail_info_tab WSH_INTERFACE_EXT_GRP.delivery_details_Attr_tbl_Type;
44 l_in_rec WSH_INTERFACE_EXT_GRP.detailInRecType;
45 l_out_rec WSH_INTERFACE_EXT_GRP.detailOutRecType;
46
47
48 l_return_status VARCHAR2(1);
49 l_msg_count NUMBER;
50 l_msg_data VARCHAR2(2000);
51
52 l_debug NUMBER;
53 l_progress VARCHAR2(10);
54 l_module_name VARCHAR2(30);
55
56 BEGIN
57
58 l_debug := NVL(fnd_profile.VALUE('INV_DEBUG_TRACE'), 0);
59 l_progress := '10';
60 l_module_name := 'UPDATE_WDD';
61
62 l_progress := '@@@';
63
64 IF (l_debug = 1) THEN
65 print_debug('Entering update_wdd...',l_module_name,4);
66 print_debug(' p_wdd_id => ' ||p_wdd_id,l_module_name,4);
67 print_debug(' p_mol_id => ' ||p_mol_id,l_module_name,4);
68 print_debug(' p_released_status => ' ||p_released_status,l_module_name,4);
69 END IF;
70
71 x_return_status := fnd_api.g_ret_sts_success;
72
73 l_progress := '@@@';
74
75 l_detail_info_tab(1).delivery_detail_id := p_wdd_id;
76
77 IF (p_released_status IS NOT NULL) THEN
78 l_progress := '@@@';
79 l_detail_info_tab(1).released_status := p_released_status;
80 ELSE
81 l_progress := '@@@';
82 --If we don't populate released_status, shipping's API
83 --will update it to NULL. So pass 'S' here since
84 --when p_released_status is null, we will always be calling
85 --update_wdd for WDD that have status 'S'
86 l_detail_info_tab(1).released_status := 'S';
87 END IF;
88
89 IF (p_mol_id IS NOT NULL) THEN
90 l_progress := '@@@';
91 l_detail_info_tab(1).move_order_line_id := p_mol_id;
92 END IF;
93
94 l_progress := '@@@';
95 l_in_rec.caller := 'WMS_XDOCK_INVRUTLB';
96 l_in_rec.action_code := 'UPDATE';
97
98 IF (l_debug = 1) THEN
99 print_debug('Calling wsh_interface_ext_grp.create_update_delivery_detail',l_module_name,4);
100 END IF;
101
102 l_progress := '@@@';
103 wsh_interface_ext_grp.create_update_delivery_detail
104 (p_api_version_number => 1.0,
105 p_init_msg_list => fnd_api.g_false,
106 p_commit => fnd_api.g_false,
107 x_return_status => l_return_status,
108 x_msg_count => l_msg_count,
109 x_msg_data => l_msg_data,
110 p_detail_info_tab => l_detail_info_tab,
111 p_in_rec => l_in_rec,
112 x_out_rec => l_out_rec);
113 l_progress := '@@@';
114
115 IF (l_debug = 1) THEN
116 print_debug('Returned from wsh_interface_ext_grp.create_update_delivery_detail',l_module_name,4);
117 print_debug('l_return_status =>'||l_return_status,l_module_name,4);
118 END IF;
119
120 IF (l_return_status <> fnd_api.g_ret_sts_success) THEN
121 l_progress := '@@@';
122 IF (l_debug = 1) THEN
123 print_debug('wsh_interface_ext_grp.create_update_delivery_detail returned with error',l_module_name,4);
124 print_debug('SQLCODE:'||SQLCODE||' SQLERRM:'||SQLERRM,l_module_name,4);
125 print_debug('Raising Exception!!!',l_module_name,4);
126 END IF;
127 l_progress := '@@@';
128 RAISE fnd_api.g_exc_unexpected_error;
129 ELSE
130 IF (l_debug = 1) THEN
131 print_debug('wsh_interface_ext_grp.create_update_delivery_detail returned with success',l_module_name,4);
132 END IF;
133 l_progress := '@@@';
134 END IF;
135
136 IF (l_debug = 1) THEN
137 print_debug('Exitting update_wdd with the following values:',l_module_name,4);
138 print_debug('x_return_status => '||x_return_status,l_module_name,4);
139 print_debug('x_msg_count => '||x_msg_count,l_module_name,4);
140 print_debug('x_msg_data => '||x_msg_data,l_module_name,4);
141 END IF;
142 EXCEPTION
143 WHEN OTHERS THEN
144 IF (l_debug = 1) THEN
145 print_debug('Exception occurred at progress:'||l_progress,l_module_name,4);
146 print_debug('SQLCODE:'||SQLCODE||' SQLERRM:'||SQLERRM,l_module_name,4);
147 END IF;
148 x_return_status := fnd_api.g_ret_sts_error;
149 END update_wdd;
150
151 PROCEDURE split_wdd
152 (x_return_status OUT NOCOPY VARCHAR2
153 ,x_msg_count OUT NOCOPY NUMBER
154 ,x_msg_data OUT NOCOPY VARCHAR2
155 ,x_new_wdd_id OUT NOCOPY NUMBER
156 ,p_wdd_id IN NUMBER
157 ,p_new_mol_id IN NUMBER
158 ,p_qty_to_splt IN NUMBER
159 ) IS
160 l_action_prms wsh_glbl_var_strct_grp.dd_action_parameters_rec_type;
161 l_action_out_rec wsh_glbl_var_strct_grp.dd_action_out_rec_type;
162 l_detail_ids wsh_util_core.id_tab_type;
163 l_tmp NUMBER;
164
165 l_return_status VARCHAR2(1);
166 l_msg_count NUMBER;
167 l_msg_data VARCHAR2(2000);
168 l_debug NUMBER;
169 l_progress VARCHAR2(10);
170 l_module_name VARCHAR2(30);
171 BEGIN
172
173 l_debug := NVL(fnd_profile.VALUE('INV_DEBUG_TRACE'), 0);
174 l_progress := '10';
175 l_module_name := 'SPLIT_WDD';
176 x_return_status := fnd_api.g_ret_sts_success;
177
178 IF (l_debug = 1) THEN
179 print_debug('Entering split_wdd...',l_module_name,4);
180 print_debug(' p_wdd_id => ' ||p_wdd_id,l_module_name,4);
181 print_debug(' p_new_mol_id => ' ||p_new_mol_id,l_module_name,4);
182 print_debug(' p_qty_to_splt => ' ||p_qty_to_splt,l_module_name,4);
183 END IF;
184
185 l_progress := '@@@';
186
187 l_detail_ids(1) := p_wdd_id;
188 l_action_prms.caller := 'WMS_XDOCK_INVRUTLB';
189 l_action_prms.action_code := 'SPLIT-LINE';
190 l_action_prms.split_quantity := p_qty_to_splt;
191
192 l_progress := '@@@';
193
194 IF (l_debug = 1) THEN
195 print_debug('Calling wsh_interface_grp.delivery_detail_action',l_module_name,4);
196 END IF;
197
198 wsh_interface_grp.delivery_detail_action
199 (p_api_version_number => 1.0,
200 p_init_msg_list => fnd_api.g_false,
201 p_commit => fnd_api.g_false,
202 x_return_status => l_return_status,
203 x_msg_count => l_msg_count,
204 x_msg_data => l_msg_data,
205 p_detail_id_tab => l_detail_ids,
206 p_action_prms => l_action_prms ,
207 x_action_out_rec => l_action_out_rec);
208
209 l_progress := '@@@';
210
211 IF (l_debug = 1) THEN
212 print_debug('Returned from wsh_interface_grp.delivery_detail_action',l_module_name,4);
213 print_debug('l_return_status =>'||l_return_status,l_module_name,4);
214 END IF;
215
216 IF (l_return_status <> fnd_api.g_ret_sts_success) THEN
217 IF (l_debug = 1) THEN
218 print_debug('wsh_interface_grp.delivery_detail_action returned with error',l_module_name,4);
219 print_debug('SQLCODE:'||SQLCODE||' SQLERRM:'||SQLERRM,l_module_name,4);
220 print_debug('Raising Exception!!!',l_module_name,4);
221 END IF;
222 l_progress := '@@@';
223 RAISE fnd_api.g_exc_unexpected_error;
224 ELSE
225 l_progress := '@@@';
226
227 l_tmp := l_action_out_rec.result_id_tab.first;
228 x_new_wdd_id := l_action_out_rec.result_id_tab(l_tmp);
229
230 IF (l_debug = 1) THEN
231 print_debug('wsh_interface_grp.delivery_detail_action returned with success',l_module_name,4);
232 print_debug('l_action_out_rec.split_quantity => '||l_action_out_rec.split_quantity,l_module_name,4);
233 print_debug('l_action_out_rec.split_quantity2 => '||l_action_out_rec.split_quantity2,l_module_name,4);
234 print_debug('l_tmp => '||l_tmp,l_module_name,4);
235 print_debug('x_new_detail_id => '||l_action_out_rec.result_id_tab(l_tmp),l_module_name,4);
236 END IF;
237 END IF;
238
239 l_progress := '@@@';
240
241 IF (p_new_mol_id IS NOT NULL) THEN
242 IF (l_debug = 1) THEN
243 print_debug('Calling update_wdd',l_module_name,4);
244 END IF;
245
246 l_progress := '@@@';
247 update_wdd
248 (x_return_status => l_return_status
249 ,x_msg_count => l_msg_count
250 ,x_msg_data => l_msg_data
251 ,p_wdd_id => x_new_wdd_id
252 ,p_released_status => NULL
253 ,p_mol_id => p_new_mol_id);
254 l_progress := '@@@';
255
256 IF (l_debug = 1) THEN
257 print_debug('Returned from update_wdd',l_module_name,4);
258 print_debug('l_return_status =>'||l_return_status,l_module_name,4);
259 END IF;
260
261 IF (l_return_status <> fnd_api.g_ret_sts_success) THEN
262 l_progress := '@@@';
263 IF (l_debug = 1) THEN
264 print_debug('update_wdd returned with error',l_module_name,4);
265 print_debug('SQLCODE:'||SQLCODE||' SQLERRM:'||SQLERRM,l_module_name,4);
266 print_debug('Raising Exception!!!',l_module_name,4);
267 END IF;
268 l_progress := '@@@';
269 RAISE fnd_api.g_exc_unexpected_error;
270 ELSE
271 IF (l_debug = 1) THEN
272 print_debug('update_wdd returned with success',l_module_name,4);
273 END IF;
274 l_progress := '@@@';
275 END IF;
276 END IF;
277
278 IF (l_debug = 1) THEN
279 print_debug('Exitting split_wdd with the following values:',l_module_name,4);
280 print_debug('x_return_status => '||x_return_status,l_module_name,4);
281 print_debug('x_msg_count => '||x_msg_count,l_module_name,4);
282 print_debug('x_msg_data => '||x_msg_data,l_module_name,4);
283 print_debug('x_new_wdd_id => '||x_new_wdd_id,l_module_name,4);
284 END IF;
285 EXCEPTION
286 WHEN OTHERS THEN
287 IF (l_debug = 1) THEN
288 print_debug('Exception occurred at progress: '||l_progress,l_module_name,4);
289 print_debug('SQLCODE:'||SQLCODE||' SQLERRM:'||SQLERRM,l_module_name,4);
290 END IF;
291 x_return_status := fnd_api.g_ret_sts_error;
292 END split_wdd;
293
294 PROCEDURE query_reservation
295 (p_query_input IN inv_reservation_global.mtl_reservation_rec_type
296 ,p_sort_by_req_date IN NUMBER
297 ,x_rsv_results OUT nocopy inv_reservation_global.mtl_reservation_tbl_type
298 ,x_return_status OUT nocopy VARCHAR2
299 ) IS
300
301 l_rsv_results_count NUMBER;
302 l_error_code NUMBER;
303 l_return_status VARCHAR2(1);
304 l_msg_count NUMBER;
305 l_msg_data VARCHAR2(2000);
306
307 l_debug NUMBER;
308 l_progress VARCHAR2(10);
309 l_module_name VARCHAR2(30);
310 BEGIN
311
312 l_debug := NVL(fnd_profile.VALUE('INV_DEBUG_TRACE'), 0);
313 l_progress := '10';
314 l_module_name := 'QUERY_RESERVATION';
315 x_return_status := fnd_api.g_ret_sts_success;
316
317 IF NOT wms_install.check_install(l_return_status,
318 l_msg_count,
319 l_msg_data,
320 p_query_input.organization_id) THEN
321 IF (l_debug = 1) THEN
322 print_debug('This is not a WMS org. No need to deal with reservation here.',l_module_name,4);
323 END IF;
324 RETURN;
325 END IF;
326
327 IF (l_debug = 1) THEN
328 print_debug('Calling inv_reservation_pub.query_reservation',l_module_name,4);
329 END IF;
330
331 inv_reservation_pub.query_reservation
332 (p_api_version_number => 1.0
333 , x_return_status => l_return_status
334 , x_msg_count => l_msg_count
335 , x_msg_data => l_msg_data
336 , p_query_input => p_query_input
337 , p_lock_records => fnd_api.g_true --???
338 , p_sort_by_req_date => p_sort_by_req_date
339 , x_mtl_reservation_tbl => x_rsv_results
340 , x_mtl_reservation_tbl_count => l_rsv_results_count
341 , x_error_code => l_error_code
342 );
343
344 IF (l_debug = 1) THEN
345 print_debug('Returned from inv_reservation_pub.query_reservation',l_module_name,4);
346 print_debug('x_return_status: '||l_return_status,l_module_name,4);
347 END IF;
348
349 IF (l_return_status <> fnd_api.g_ret_sts_success) THEN
350 IF (l_debug = 1) THEN
351 print_debug('x_error_code: '||l_error_code,l_module_name,4);
352 print_debug('x_msg_data: '||l_msg_data,l_module_name,4);
353 print_debug('x_msg_count: '||l_msg_count,l_module_name,4);
354 print_debug('SQLCODE:'||SQLCODE||' SQLERRM:'||SQLERRM,l_module_name,4);
355 print_debug('Raising Exception!!!',l_module_name,4);
356 END IF;
357 l_progress := '@@@';
358 RAISE fnd_api.g_exc_unexpected_error;
359 END IF;
360
361 l_progress := '@@@';
362
363 IF (l_debug = 1) THEN
364 print_debug('x_rsv_results.COUNT: '||x_rsv_results.COUNT,l_module_name,4);
365 print_debug('l_rsv_results_count: '||l_rsv_results_count,l_module_name,4);
366 print_debug('Exiting query_reservation with success',l_module_name,4);
367 END IF;
368
369 EXCEPTION
370 WHEN OTHERS THEN
371 IF (l_debug = 1) THEN
372 print_debug('Exception occurred at progress: '||l_progress,l_module_name,4);
373 print_debug('SQLCODE:'||SQLCODE||' SQLERRM:'||SQLERRM,l_module_name,4);
374 print_debug('Exiting query_reservation with error',l_module_name,4);
375 END IF;
376 x_return_status := fnd_api.g_ret_sts_error;
377 END query_reservation;
378
379 PROCEDURE transfer_reservation
380 (p_original_rsv_rec IN inv_reservation_global.mtl_reservation_rec_type
381 ,p_to_rsv_rec IN inv_reservation_global.mtl_reservation_rec_type
382 ,x_new_rsv_id OUT nocopy NUMBER
383 ,x_return_status OUT nocopy VARCHAR2)
384 IS
385 l_dummy_serial inv_reservation_global.serial_number_tbl_type;
386 l_return_status VARCHAR2(1);
387 l_msg_count NUMBER;
388 l_msg_data VARCHAR2(2000);
389
390 l_debug NUMBER;
391 l_progress VARCHAR2(10);
392 l_module_name VARCHAR2(30);
393 BEGIN
394 l_debug := NVL(fnd_profile.VALUE('INV_DEBUG_TRACE'), 0);
395 l_progress := '10';
396 l_module_name := 'TRANSFER_RESERVATIONS';
397
398 x_return_status := fnd_api.g_ret_sts_success;
399
400 IF NOT wms_install.check_install(l_return_status,
401 l_msg_count,
402 l_msg_data,
403 p_original_rsv_rec.organization_id) THEN
404 IF (l_debug = 1) THEN
405 print_debug('This is not a WMS org. No need to deal with reservation here.',l_module_name,4);
406 END IF;
407 RETURN;
408 END IF;
409
410 IF (l_debug = 1) THEN
411 print_debug('Calling inv_reservation_pub.transfer_reservation',l_module_name,4);
412 END IF;
413
414 inv_reservation_pub.transfer_reservation
415 (p_api_version_number => 1.0
416 ,x_return_status => l_return_status
417 ,x_msg_count => l_msg_count
418 ,x_msg_data => l_msg_data
419 ,p_original_rsv_rec => p_original_rsv_rec
420 ,p_to_rsv_rec => p_to_rsv_rec
421 ,p_original_serial_number => l_dummy_serial
422 ,p_to_serial_number => l_dummy_serial
423 ,p_validation_flag => fnd_api.g_false --??
424 ,x_to_reservation_id => x_new_rsv_id);
425
426 IF (l_debug = 1) THEN
427 print_debug('Returned from inv_reservation_pub.transfer_reservation',l_module_name,4);
428 print_debug('x_return_status =>'||l_return_status,l_module_name,4);
429 END IF;
430
431 IF (l_return_status <> fnd_api.g_ret_sts_success) THEN
432 IF (l_debug = 1) THEN
433 print_debug('x_msg_data: '||l_msg_data,l_module_name,4);
434 print_debug('x_msg_count: '||l_msg_count,l_module_name,4);
435 print_debug('SQLCODE:'||SQLCODE||' SQLERRM:'||SQLERRM,l_module_name,4);
436 print_debug('Raising Exception!!!',l_module_name,4);
437 END IF;
438 l_progress := '@@@';
439 RAISE fnd_api.g_exc_unexpected_error;
440 END IF;
441
442 l_progress := '@@@';
443
444 IF (l_debug = 1) THEN
445 print_debug('x_new_rsv_id = '||x_new_rsv_id,l_module_name,4);
446 END IF;
447 EXCEPTION
448 WHEN OTHERS THEN
449 IF (l_debug = 1) THEN
450 print_debug('Exception occurred at progress: '||l_progress,l_module_name,4);
451 print_debug('SQLCODE:'||SQLCODE||' SQLERRM:'||SQLERRM,l_module_name,4);
452 print_debug('Exiting transfer_reservation with error',l_module_name,4);
453 END IF;
454 x_return_status := fnd_api.g_ret_sts_error;
455 END transfer_reservation;
456
457 PROCEDURE relieve_reservation
458 (p_rsv_rec IN inv_reservation_global.mtl_reservation_rec_type
459 ,p_prim_qty_to_relieve IN NUMBER
460 ,x_return_status OUT nocopy VARCHAR2
461 )
462 IS
463 l_dummy_serial inv_reservation_global.serial_number_tbl_type;
464 l_tmp_prim_relieved_qty NUMBER;
465 l_tmp_prim_remain_qty NUMBER;
466
467 l_return_status VARCHAR2(1);
468 l_msg_count NUMBER;
469 l_msg_data VARCHAR2(2000);
470
471 l_debug NUMBER;
472 l_progress VARCHAR2(10);
473 l_module_name VARCHAR2(30);
474
475 BEGIN
476
477 l_debug := NVL(fnd_profile.VALUE('INV_DEBUG_TRACE'), 0);
478 l_progress := '10';
479 l_module_name := 'RELIEVE_RESERVATIONS';
480
481 x_return_status := fnd_api.g_ret_sts_success;
482
483 IF NOT wms_install.check_install(l_return_status,
484 l_msg_count,
485 l_msg_data,
486 p_rsv_rec.organization_id) THEN
487 IF (l_debug = 1) THEN
488 print_debug('This is not a WMS org. No need to deal with reservation here.',l_module_name,4);
489 END IF;
490 RETURN;
491 END IF;
492
493 IF (l_debug = 1) THEN
494 print_debug('Calling inv_reservation_pub.relieve_reservation',l_module_name,4);
495 print_debug(' p_relieve_all => '||fnd_api.g_false,l_module_name,4);
496 print_debug(' p_primary_relieved_quantity => '||p_prim_qty_to_relieve,l_module_name,4);
497 END IF;
498
499 inv_reservation_pub.relieve_reservation
500 (p_api_version_number => 1.0
501 , x_return_status => l_return_status
502 , x_msg_count => l_msg_count
503 , x_msg_data => l_msg_data
504 , p_rsv_rec => p_rsv_rec
505 , p_primary_relieved_quantity=> p_prim_qty_to_relieve
506 , p_original_serial_number => l_dummy_serial
507 , x_primary_relieved_quantity=> l_tmp_prim_relieved_qty
508 , x_primary_remain_quantity => l_tmp_prim_remain_qty
509 , p_relieve_all => fnd_api.g_false
510 );
511
512 IF (l_debug = 1) THEN
513 print_debug('Returned from inv_reservation_pub.query_reservation',l_module_name,4);
514 print_debug('x_return_status: '||l_return_status,l_module_name,4);
515 END IF;
516
517 IF (l_return_status <> fnd_api.g_ret_sts_success) THEN
518 IF (l_debug = 1) THEN
519 print_debug('x_msg_data: '||l_msg_data,l_module_name,4);
520 print_debug('x_msg_count: '||l_msg_count,l_module_name,4);
521 print_debug('SQLCODE:'||SQLCODE||' SQLERRM:'||SQLERRM,l_module_name,4);
522 print_debug('Raising Exception!!!',l_module_name,4);
523 END IF;
524 l_progress := '@@@';
525 RAISE fnd_api.g_exc_unexpected_error;
526 END IF;
527
528 l_progress := '@@@';
529
530 IF (l_debug = 1) THEN
531
532 print_debug('l_tmp_prim_relieved_qty: '||l_tmp_prim_relieved_qty,4);
533 print_debug('l_tmp_prim_remain_qty: '||l_tmp_prim_remain_qty,4);
534 print_debug('Exiting relieve_reservation with success',l_module_name,4);
535 END IF;
536 EXCEPTION
537 WHEN OTHERS THEN
538 IF (l_debug = 1) THEN
539 print_debug('Exception occurred at progress:'||l_progress,l_module_name,4);
540 print_debug('SQLCODE:'||SQLCODE||' SQLERRM:'||SQLERRM,l_module_name,4);
541 print_debug('Exiting relieve_reservation with success',l_module_name,4);
542 END IF;
543 x_return_status := fnd_api.g_ret_sts_error;
544 END relieve_reservation;
545
546 PROCEDURE delete_reservation
547 (p_rsv_rec IN inv_reservation_global.mtl_reservation_rec_type
548 ,x_return_status OUT nocopy VARCHAR2)
549 IS
550 l_dummy_serial inv_reservation_global.serial_number_tbl_type;
551 l_return_status VARCHAR2(1);
552 l_msg_count NUMBER;
553 l_msg_data VARCHAR2(2000);
554
555 l_debug NUMBER;
556 l_progress VARCHAR2(10);
557 l_module_name VARCHAR2(30);
558
559 BEGIN
560
561 l_debug := NVL(fnd_profile.VALUE('INV_DEBUG_TRACE'), 0);
562 l_progress := '10';
563 l_module_name := 'DELETE_RESERVATION';
564
565 IF NOT wms_install.check_install(l_return_status,
566 l_msg_count,
567 l_msg_data,
568 p_rsv_rec.organization_id) THEN
569 IF (l_debug = 1) THEN
570 print_debug('This is not a WMS org. No need to deal with reservation here.',l_module_name,4);
571 END IF;
572 RETURN;
573 END IF;
574
575 IF (l_debug = 1) THEN
576 print_debug('Calling inv_reservation_pub.delete_reservation',l_module_name,4);
577 END IF;
578
579 inv_reservation_pub.delete_reservation
580 (p_api_version_number => 1.0
581 , x_return_status => l_return_status
582 , x_msg_count => l_msg_count
583 , x_msg_data => l_msg_data
584 , p_rsv_rec => p_rsv_rec
585 , p_serial_number => l_dummy_serial
586 );
587
588 IF (l_debug = 1) THEN
589 print_debug('Returned from inv_reservation_pub.delete_reservation',l_module_name,4);
590 print_debug('x_return_status =>'||l_return_status,l_module_name,4);
591 END IF;
592
593 IF (l_return_status <> fnd_api.g_ret_sts_success) THEN
594 IF (l_debug = 1) THEN
595 print_debug('x_msg_data: '||l_msg_data,l_module_name,4);
596 print_debug('x_msg_count: '||l_msg_count,l_module_name,4);
597 print_debug('SQLCODE:'||SQLCODE||' SQLERRM:'||SQLERRM,l_module_name,4);
598 print_debug('Raising Exception!!!',l_module_name,4);
599 END IF;
600 l_progress := '@@@';
601 RAISE fnd_api.g_exc_unexpected_error;
602 END IF;
603
604 l_progress := '@@@';
605
606 IF (l_debug = 1) THEN
607 print_debug('Exiting delete_reservation with success',l_module_name,4);
608 END IF;
609 EXCEPTION
610 WHEN OTHERS THEN
611 IF (l_debug = 1) THEN
612 print_debug('Exception occurred at progress: '||l_progress,l_module_name,4);
613 print_debug('SQLCODE:'||SQLCODE||' SQLERRM:'||SQLERRM,l_module_name,4);
614 print_debug('Exiting delete_reservation with error',l_module_name,4);
615 END IF;
616 x_return_status := fnd_api.g_ret_sts_error;
617 END delete_reservation;
618
619 PROCEDURE reduce_reservation
620 (p_mtl_rsv_rec IN inv_reservation_global.mtl_maintain_rsv_rec_type
621 ,x_return_status OUT nocopy VARCHAR2
622 )
623 IS
624 l_qty_modified NUMBER;
625
626 l_return_status VARCHAR2(1);
627 l_msg_count NUMBER;
628 l_msg_data VARCHAR2(2000);
629
630 l_debug NUMBER;
631 l_progress VARCHAR2(10);
632 l_module_name VARCHAR2(30);
633
634 BEGIN
635
636 l_debug := NVL(fnd_profile.VALUE('INV_DEBUG_TRACE'), 0);
637 l_progress := '10';
638 l_module_name := 'REDUCE_RESERVATION';
639
640 IF NOT wms_install.check_install(l_return_status,
641 l_msg_count,
642 l_msg_data,
643 p_mtl_rsv_rec.organization_id) THEN
644 IF (l_debug = 1) THEN
645 print_debug('This is not a WMS org. No need to deal with reservation here.',l_module_name,4);
646 END IF;
647 RETURN;
648 END IF;
649
650 IF (l_debug = 1) THEN
651 print_debug('Calling inv_maintain_reservations_pub.reduce_reservation',l_module_name,4);
652 END IF;
653 inv_maintain_reservation_pub.reduce_reservation
654 (x_return_status => l_return_status
655 , x_msg_count => l_msg_count
656 , x_msg_data => l_msg_data
657 , x_quantity_modified => l_qty_modified
658 , p_mtl_maintain_rsv_rec => p_mtl_rsv_rec
659 , p_delete_flag => fnd_api.g_true
660 , p_sort_by_criteria => g_query_demand_ship_date_desc --???
661 );
662 IF (l_debug = 1) THEN
663 print_debug('Returned from inv_maintain_reservations_pub.reduce_reservation',l_module_name,4);
664 print_debug('x_return_status =>'||l_return_status,l_module_name,4);
665 END IF;
666
667 IF (l_return_status <> fnd_api.g_ret_sts_success) THEN
668 IF (l_debug = 1) THEN
669 print_debug('x_msg_data: '||l_msg_data,l_module_name,4);
670 print_debug('x_msg_count: '||l_msg_count,l_module_name,4);
671 print_debug('SQLCODE:'||SQLCODE||' SQLERRM:'||SQLERRM,l_module_name,4);
672 print_debug('Raising Exception!!!',l_module_name,4);
673 END IF;
674 l_progress := '@@@';
675 RAISE fnd_api.g_exc_unexpected_error;
676 END IF;
677
678 l_progress := '@@@';
679
680 IF (l_debug = 1) THEN
681 print_debug('inv_reservation_pub.transfer_reservation returned with success',l_module_name,4);
682 print_debug('l_qty_modified = '||l_qty_modified,l_module_name,4);
683 END IF;
684 EXCEPTION
685 WHEN OTHERS THEN
686 IF (l_debug = 1) THEN
687 print_debug('Exception occurred at progress: '||l_progress,l_module_name,4);
688 print_debug('SQLCODE:'||SQLCODE||' SQLERRM:'||SQLERRM,l_module_name,4);
689 print_debug('Exiting reduce_reservation with error',l_module_name,4);
690 END IF;
691 x_return_status := fnd_api.g_ret_sts_error;
692 END reduce_reservation;
693
694 --This will be called from maintain_rsv_receive
695 PROCEDURE set_mol_wdd_tbl
696 (p_orig_rcpt_rec IN inv_rcv_integration_pvt.cas_mol_rec_type
697 ,p_cas_mol_rec_tb IN OUT nocopy inv_rcv_integration_pvt.cas_mol_rec_tb_tp
698 ,p_prim_qty IN NUMBER
699 ,p_wdd_id IN NUMBER
700 ,p_crossdock_type IN NUMBER
701 ) IS
702
703 l_new_index NUMBER;
704 l_txn_qty NUMBER;
705 l_sec_qty NUMBER;
706
707 l_return_status VARCHAR2(1);
708 l_msg_count NUMBER;
709 l_msg_data VARCHAR2(2000);
710
711 l_debug NUMBER;
712 l_progress VARCHAR2(10);
713 l_module_name VARCHAR2(30);
714
715 BEGIN
716
717 l_debug := NVL(fnd_profile.VALUE('INV_DEBUG_TRACE'), 0);
718 l_progress := '10';
719 l_module_name := 'SET_MOL_WDD_TBL';
720
721
722 IF (l_debug = 1) THEN
723 print_debug('Entering set_mol_wdd_tbl...',l_module_name,4);
724 print_debug(' p_prim_qty => '||p_prim_qty,l_module_name,4);
725 print_debug(' p_orig_rcpt_rec.uom_code => '||p_orig_rcpt_rec.uom_code,l_module_name,4);
726 print_debug(' p_orig_rcpt_rec.primary_uom_code => '||p_orig_rcpt_rec.primary_uom_code,l_module_name,4);
727 Print_Debug(' p_orig_rcpt_rec.secondary_quantity => '||P_Orig_Rcpt_Rec.Secondary_Quantity ,L_Module_Name,4);
728 print_debug(' p_orig_rcpt_rec.quantity => '||p_orig_rcpt_rec.quantity ,l_module_name,4);
729 END IF;
730 --Bug10036172-Avoid conversion if we know the txn qty already -- Bug 13938576 added conditon for negative correction as well
731 If ((Nvl(P_Orig_Rcpt_Rec.Quantity,0) > 0 OR ((p_orig_rcpt_rec.transaction_type IN ('CORRECT')
732 AND p_orig_rcpt_rec.primary_qty < 0
733 AND p_orig_rcpt_rec.parent_txn_type = 'DELIVER')))
734 and ( p_prim_qty = round(Inv_Rcv_Cache.Convert_Qty(P_Orig_Rcpt_Rec.Inventory_Item_Id ,
735 P_Orig_Rcpt_Rec.Quantity ,
736 p_orig_rcpt_rec.uom_code,
737 P_Orig_Rcpt_Rec.Primary_Uom_Code
738 ),5) ))
739 then
740 l_txn_qty := p_orig_rcpt_rec.quantity;
741 Else
742 IF p_orig_rcpt_rec.uom_code <> p_orig_rcpt_rec.primary_uom_code THEN
743 L_Txn_Qty := Inv_Rcv_Cache.Convert_Qty(P_Orig_Rcpt_Rec.Inventory_Item_Id ,
744 p_prim_qty ,
745 p_orig_rcpt_rec.primary_uom_code,
746 p_orig_rcpt_rec.uom_code);
747 Else
748 L_Txn_Qty := P_Prim_Qty;
749 END IF;
750 END IF;
751
752 If (L_Debug = 1) Then
753 Print_Debug('l_txn_qty:'||L_Txn_Qty,L_Module_Name,4);
754 end if;
755
756 IF((p_orig_rcpt_rec.transaction_type IN ('CORRECT')
757 AND p_orig_rcpt_rec.primary_qty < 0
758 AND p_orig_rcpt_rec.parent_txn_type = 'DELIVER')) then
759
760 If ((Trunc(l_txn_qty,0) - l_txn_qty)<0.00005) THEN
761 print_debug('Decimal dust case. l_txn_qty = ' || l_txn_qty, 4);
762 l_txn_qty := Trunc(l_txn_qty,0);
763 END If;
764
765 else
766 If (l_txn_qty - Trunc(l_txn_qty,0)<0.00005) THEN
767 print_debug('Decimal dust case. l_txn_qty = ' || l_txn_qty, 4);
768 l_txn_qty := Trunc(l_txn_qty,0);
769 end If;
770 END IF;
771 -- bug 11887302,12539886,Bug 13938576 changes end .Added conditon for negative correction as well
772
773 IF (Nvl(p_orig_rcpt_rec.secondary_quantity,0) <> 0) THEN
774 l_sec_qty := Round((p_prim_qty*p_orig_rcpt_rec.secondary_quantity)/p_orig_rcpt_rec.primary_qty,
775 inv_rcv_cache.g_conversion_precision);
776 END IF;
777
778 IF p_cas_mol_rec_tb.exists(Nvl(p_wdd_id,-1)) THEN
779 p_cas_mol_rec_tb(Nvl(p_wdd_id,-1)).primary_qty := p_cas_mol_rec_tb(Nvl(p_wdd_id,-1)).primary_qty+p_prim_qty;
780 p_cas_mol_rec_tb(Nvl(p_wdd_id,-1)).quantity := p_cas_mol_rec_tb(Nvl(p_wdd_id,-1)).quantity+l_txn_qty;
781 p_cas_mol_rec_tb(Nvl(p_wdd_id,-1)).secondary_quantity := p_cas_mol_rec_tb(Nvl(p_wdd_id,-1)).secondary_quantity+l_sec_qty;
782 ELSE
783 p_cas_mol_rec_tb(Nvl(p_wdd_id,-1)) := p_orig_rcpt_rec;
784 p_cas_mol_rec_tb(Nvl(p_wdd_id,-1)).primary_qty := p_prim_qty;
785 p_cas_mol_rec_tb(Nvl(p_wdd_id,-1)).quantity := l_txn_qty;
786 p_cas_mol_rec_tb(Nvl(p_wdd_id,-1)).backorder_delivery_detail_id := p_wdd_id;
787 p_cas_mol_rec_tb(Nvl(p_wdd_id,-1)).crossdock_type := p_crossdock_type;
788 p_cas_mol_rec_tb(Nvl(p_wdd_id,-1)).secondary_quantity := l_sec_qty;
789 END IF;
790 EXCEPTION
791 WHEN OTHERS THEN
792 IF (l_debug = 1) THEN
793 print_debug('Error in set_mol_wdd_tbl...',l_module_name,4);
794 END IF;
795 RAISE fnd_api.g_exc_unexpected_error;
796 END set_mol_wdd_tbl;
797
798 PROCEDURE maintain_rsv_import_asn
799 (x_return_status OUT NOCOPY VARCHAR2
800 ,x_msg_count OUT NOCOPY NUMBER
801 ,x_msg_data OUT NOCOPY VARCHAR2
802 ,p_cas_mol_rec_tb IN inv_rcv_integration_pvt.cas_mol_rec_tb_tp
803 ) IS
804 l_rsv_query_rec inv_reservation_global.mtl_reservation_rec_type;
805 l_rsv_results_tbl inv_reservation_global.mtl_reservation_tbl_type;
806 l_rsv_update_rec inv_reservation_global.mtl_reservation_rec_type;
807 l_rsv_results_count NUMBER;
808 l_remaining_prim_qty NUMBER;
809 l_reservation_id NUMBER;
810 l_new_wdd_id NUMBER;
811 l_dummy_serial VARCHAR2(30);
812 l_dummy NUMBER;
813
814 l_return_status VARCHAR2(1);
815 l_error_code NUMBER;
816 l_msg_count NUMBER;
817 l_msg_data VARCHAR2(2000);
818
819 l_debug NUMBER;
820 l_progress VARCHAR2(10);
821 l_module_name VARCHAR2(30);
822
823 BEGIN
824
825 --{{
826 --********** PROCEDURE maintain_rsv_import_asn *********}}
827
828 l_debug := NVL(fnd_profile.VALUE('INV_DEBUG_TRACE'), 0);
829 l_progress := '10';
830 l_module_name := 'maintain_rsv_import_asn';
831 x_return_status := fnd_api.g_ret_sts_success;
832
833 IF (l_debug = 1) THEN
834 print_debug('Entering maintain_rsv_import_asn...',l_module_name,4);
835 print_debug(' p_cas_mol_rec_tb(1).po_header_id => '||p_cas_mol_rec_tb(1).po_header_id,l_module_name,4);
836 print_debug(' p_cas_mol_rec_tb(1).po_line_location_id => '||p_cas_mol_rec_tb(1).po_line_location_id,l_module_name,4);
837 print_debug(' p_cas_mol_rec_tb(1).inventory_item_id => '||p_cas_mol_rec_tb(1).inventory_item_id,l_module_name,4);
838 print_debug(' p_cas_mol_rec_tb(1).organization_id => '||p_cas_mol_rec_tb(1).organization_id,l_module_name,4);
839 print_debug(' p_cas_mol_rec_tb(1).primary_qty => '||p_cas_mol_rec_tb(1).primary_qty,l_module_name,4);
840 print_debug(' p_cas_mol_rec_tb(1).shipment_line_id => '||p_cas_mol_rec_tb(1).shipment_line_id,l_module_name,4);
841 print_debug(' p_cas_mol_rec_tb(1).project_id => '||p_cas_mol_rec_tb(1).project_id,l_module_name,4);
842 print_debug(' p_cas_mol_rec_tb(1).task_id => '||p_cas_mol_rec_tb(1).task_id,l_module_name,4);
843 END IF;
844
845 --{{
846 --Test import asn for the following cases:
847 --1. Import an ASN whose document matches to a manual reservation
848 --2. Import an ASN whose document matches to a xdock reservation
849 --3. Import an ASN whose document does not match to any reservations
850 --4. Import an ASN whose document matches to mixed reservations types }}
851
852 --1.0 Query reservation for the particular PO
853 l_rsv_query_rec.supply_source_type_id := g_source_type_po;
854 l_rsv_query_rec.supply_source_header_id := p_cas_mol_rec_tb(1).po_header_id;
855 l_rsv_query_rec.supply_source_line_id := p_cas_mol_rec_tb(1).po_line_location_id;
856 l_rsv_query_rec.inventory_item_id := p_cas_mol_rec_tb(1).inventory_item_id;
857 l_rsv_query_rec.organization_id := p_cas_mol_rec_tb(1).organization_id;
858
859 IF (p_cas_mol_rec_tb(1).project_id IS NOT NULL) THEN
860 l_rsv_query_rec.project_id := p_cas_mol_rec_tb(1).project_id;
861 IF (p_cas_mol_rec_tb(1).task_id IS NOT NULL) THEN
862 l_rsv_query_rec.task_id := p_cas_mol_rec_tb(1).task_id;
863 END IF;
864 END IF ;
865
866 l_progress := '@@@';
867 BEGIN
868 SELECT 1
869 INTO l_dummy
870 FROM po_line_locations_all
871 WHERE line_location_id = p_cas_mol_rec_tb(1).po_line_location_id
872 FOR UPDATE NOWAIT;
873 EXCEPTION
874 WHEN OTHERS THEN
875 IF (l_debug = 1) THEN
876 print_debug('Some other exception occurred!',l_module_name,4);
877 print_debug('SQLCODE:'||SQLCODE||' SQLERRM:'||SQLERRM,l_module_name,4);
878 END IF;
879 l_progress := '@@@';
880 RAISE fnd_api.g_exc_unexpected_error;
881 END;
882 l_progress := '@@@';
883
884 IF (l_debug = 1) THEN
885 print_debug('Calling query_reservation',l_module_name,4);
886 END IF;
887
888 --2.0 Query reservation
889 l_progress := '@@@';
890 query_reservation
891 (p_query_input => l_rsv_query_rec
892 ,p_sort_by_req_date => g_query_demand_ship_date_asc
893 ,x_rsv_results => l_rsv_results_tbl
894 ,x_return_status => l_return_status
895 );
896 l_progress := '@@@';
897
898 IF (l_debug = 1) THEN
899 print_debug('Returned from query_reservation',l_module_name,4);
900 print_debug('x_return_status: '||l_return_status,l_module_name,4);
901 END IF;
902
903 IF (l_return_status <> fnd_api.g_ret_sts_success) THEN
904 l_progress := '@@@';
905 RAISE fnd_api.g_exc_unexpected_error;
906 END IF;
907
908 l_remaining_prim_qty := p_cas_mol_rec_tb(1).primary_qty;
909
910 IF (l_debug = 1) THEN
911 print_debug('l_remaining_prim_qty = ' ||l_remaining_prim_qty,l_module_name,4);
912 print_debug('l_rsv_results_tbl.COUNT = ' ||l_rsv_results_tbl.COUNT,l_module_name,4);
913 END IF;
914
915 FOR i IN 1..l_rsv_results_tbl.COUNT LOOP
916 EXIT WHEN l_remaining_prim_qty <= 0;
917
918 IF (l_debug = 1) THEN
919 print_debug('l_remaining_prim_qty:'||l_remaining_prim_qty||
920 ' i:'||i||
921 ' rsv_id:'||l_rsv_results_tbl(i).reservation_id||
922 ' prim_qty:'||l_rsv_results_tbl(i).primary_reservation_quantity||
923 ' rsv_qty:'||l_rsv_results_tbl(i).reservation_quantity||
924 ' uom_code:'||l_rsv_results_tbl(i).reservation_uom_code||
925 ' orig_supply_src_type_id:'||l_rsv_results_tbl(i).orig_supply_source_type_id||
926 ' demand_src_line_detail:'||l_rsv_results_tbl(i).demand_source_line_detail||
927 ' ext_src_code:'||l_rsv_results_tbl(i).external_source_code
928 ,l_module_name,4);
929 END IF;
930
931 l_rsv_update_rec := l_rsv_results_tbl(i);
932
933 IF (l_rsv_results_tbl(i).primary_reservation_quantity > l_remaining_prim_qty) THEN
934 -- Reservation has more than enough to satisfy remaining qty, so split
935
936 IF (l_debug = 1) THEN
937 print_debug('l_rsv_results_tbl(i).primary_reservation_quantity > l_remaining_prim_qty',
938 l_module_name,4);
939 END IF;
940
941 l_rsv_update_rec.primary_reservation_quantity := l_remaining_prim_qty;
942 l_rsv_update_rec.reservation_quantity := inv_rcv_cache.convert_qty(p_cas_mol_rec_tb(1).inventory_item_id
943 ,l_remaining_prim_qty
944 ,l_rsv_results_tbl(i).primary_uom_code
945 ,l_rsv_results_tbl(i).reservation_uom_code);
946
947 IF (l_rsv_results_tbl(i).demand_source_line_detail IS NOT NULL) THEN
948 IF (l_debug = 1) THEN
949 print_debug('Calling split_wdd',l_module_name,4);
950 END IF;
951
952 l_progress := '@@@';
953 split_wdd
954 (x_return_status => l_return_status
955 ,x_msg_count => l_msg_count
956 ,x_msg_data => l_msg_data
957 ,x_new_wdd_id => l_new_wdd_id
958 ,p_wdd_id => l_rsv_results_tbl(i).demand_source_line_detail
959 ,p_new_mol_id => NULL
960 ,p_qty_to_splt => l_remaining_prim_qty);
961 l_progress := '@@@';
962
963 IF (l_debug = 1) THEN
964 print_debug('Returned from split_wdd',l_module_name,4);
965 print_debug('l_return_status =>'||l_return_status,l_module_name,4);
966 END IF;
967
968 IF (l_return_status <> fnd_api.g_ret_sts_success) THEN
969 l_progress := '@@@';
970 RAISE fnd_api.g_exc_unexpected_error;
971 END IF;
972
973 l_rsv_update_rec.demand_source_line_detail := l_new_wdd_id;
974 END IF;
975
976 l_remaining_prim_qty := 0;
977
978 ELSE
979 IF (l_debug = 1) THEN
980 print_debug('l_rsv_results_tbl(i).primary_reservation_quantity <= l_remaining_prim_qty',
981 l_module_name,4);
982 END IF;
983 l_remaining_prim_qty := l_remaining_prim_qty - l_rsv_results_tbl(i).primary_reservation_quantity;
984 END IF; --END IF (l_rsv_results_tbl(i).primary_reservation_quantity > l_remaining_prim_qty)
985
986 --Transfer reservation to ASN
987 l_rsv_update_rec.supply_source_type_id := g_source_type_asn;
988 l_rsv_update_rec.supply_source_line_detail := p_cas_mol_rec_tb(1).shipment_line_id;
989
990 IF (l_debug = 1) THEN
991 print_debug('Calling transfer_reservation...',l_module_name,4);
992 END IF;
993
994 transfer_reservation
995 (p_original_rsv_rec => l_rsv_results_tbl(i)
996 ,p_to_rsv_rec => l_rsv_update_rec
997 ,x_new_rsv_id => l_reservation_id
998 ,x_return_status => l_return_status);
999
1000 IF (l_debug = 1) THEN
1001 print_debug('Returned from transfer_reservation',l_module_name,4);
1002 print_debug('x_return_status: '||l_return_status,l_module_name,4);
1003 END IF;
1004
1005 IF (l_return_status <> fnd_api.g_ret_sts_success) THEN
1006 l_progress := '@@@';
1007 RAISE fnd_api.g_exc_unexpected_error;
1008 END IF;
1009
1010 IF (l_debug = 1) THEN
1011 print_debug('l_reservation_id: '||l_reservation_id,l_module_name,4);
1012 END IF;
1013
1014 END LOOP;
1015
1016 IF (l_debug = 1) THEN
1017 print_debug('Exitting maintain_rsv_import_asn with the following values:',l_module_name,4);
1018 print_debug('x_return_status => '||x_return_status,l_module_name,4);
1019 print_debug('x_msg_count => '||x_msg_count,l_module_name,4);
1020 print_debug('x_msg_data => '||x_msg_data,l_module_name,4);
1021 END IF;
1022
1023 --{{
1024 --********** END PROCEDURE maintain_rsv_import_asn *********}}
1025 EXCEPTION
1026 WHEN OTHERS THEN
1027 IF (l_debug = 1) THEN
1028 print_debug('Exception occurred at progress: '||l_progress,l_module_name,4);
1029 print_debug('SQLCODE:'||SQLCODE||' SQLERRM:'||SQLERRM,l_module_name,4);
1030 END IF;
1031 x_return_status := fnd_api.g_ret_sts_error;
1032 END maintain_rsv_import_asn;
1033
1034 PROCEDURE maintain_rsv_cancel_asn
1035 (x_return_status OUT NOCOPY VARCHAR2
1036 ,x_msg_count OUT NOCOPY NUMBER
1037 ,x_msg_data OUT NOCOPY VARCHAR2
1038 ,p_cas_mol_rec_tb IN inv_rcv_integration_pvt.cas_mol_rec_tb_tp
1039 ) IS
1040 l_rsv_query_rec inv_reservation_global.mtl_reservation_rec_type;
1041 l_rsv_results_tbl inv_reservation_global.mtl_reservation_tbl_type;
1042 l_rsv_update_rec inv_reservation_global.mtl_reservation_rec_type;
1043 l_reservation_id NUMBER;
1044 l_dummy NUMBER;
1045
1046 l_return_status VARCHAR2(1);
1047 l_error_code NUMBER;
1048 l_msg_count NUMBER;
1049 l_msg_data VARCHAR2(2000);
1050
1051 l_debug NUMBER;
1052 l_progress VARCHAR2(10);
1053 l_module_name VARCHAR2(30);
1054
1055 BEGIN
1056
1057 --{{
1058 --********** PROCEDURE maintain_rsv_cancel_asn *********}}
1059
1060 l_debug := NVL(fnd_profile.VALUE('INV_DEBUG_TRACE'), 0);
1061 l_progress := '10';
1062 l_module_name := 'MAINTAIN_RSV_CANCEL_ASN';
1063 x_return_status := fnd_api.g_ret_sts_success;
1064
1065 IF (l_debug = 1) THEN
1066 print_debug('Entering maintain_rsv_cancel_asn...',l_module_name,4);
1067 print_debug(' p_cas_mol_rec_tb(1).po_header_id => '||p_cas_mol_rec_tb(1).po_header_id,l_module_name,4);
1068 print_debug(' p_cas_mol_rec_tb(1).po_line_location_id => '||p_cas_mol_rec_tb(1).po_line_location_id,l_module_name,4);
1069 print_debug(' p_cas_mol_rec_tb(1).inventory_item_id => '||p_cas_mol_rec_tb(1).inventory_item_id,l_module_name,4);
1070 print_debug(' p_cas_mol_rec_tb(1).organization_id => '||p_cas_mol_rec_tb(1).organization_id,l_module_name,4);
1071 print_debug(' p_cas_mol_rec_tb(1).primary_qty => '||p_cas_mol_rec_tb(1).primary_qty,l_module_name,4);
1072 print_debug(' p_cas_mol_rec_tb(1).shipment_line_id => '||p_cas_mol_rec_tb(1).shipment_line_id,l_module_name,4);
1073 print_debug(' p_cas_mol_rec_tb(1).project_id => '||p_cas_mol_rec_tb(1).project_id,l_module_name,4);
1074 print_debug(' p_cas_mol_rec_tb(1).task_id => '||p_cas_mol_rec_tb(1).task_id,l_module_name,4);
1075 END IF;
1076
1077 --{{
1078 --Test 1) Cancelling an ASN that can be matched to a reservation whose original
1079 -- source type is 'ASN'
1080 -- 2) Cancelling an ASN that can be matched to a reservation who original
1081 -- source type is not 'ASN' (it would probably be PO)
1082 -- 3) Cancelling an ASN that does to match to any reservation}}
1083
1084 l_rsv_query_rec.supply_source_type_id := g_source_type_asn;
1085 l_rsv_query_rec.supply_source_header_id := p_cas_mol_rec_tb(1).po_header_id;
1086 l_rsv_query_rec.supply_source_line_id := p_cas_mol_rec_tb(1).po_line_location_id;
1087 l_rsv_query_rec.supply_source_line_detail := p_cas_mol_rec_tb(1).shipment_line_id;
1088 l_rsv_query_rec.inventory_item_id := p_cas_mol_rec_tb(1).inventory_item_id;
1089 l_rsv_query_rec.organization_id := p_cas_mol_rec_tb(1).organization_id;
1090
1091 IF (p_cas_mol_rec_tb(1).project_id IS NOT NULL) THEN
1092 l_rsv_query_rec.project_id := p_cas_mol_rec_tb(1).project_id;
1093 IF (p_cas_mol_rec_tb(1).task_id IS NOT NULL) THEN
1094 l_rsv_query_rec.task_id := p_cas_mol_rec_tb(1).task_id;
1095 END IF;
1096 END IF ;
1097
1098 l_progress := '@@@';
1099 BEGIN
1100 SELECT 1
1101 INTO l_dummy
1102 FROM rcv_shipment_lines
1103 WHERE shipment_line_id = p_cas_mol_rec_tb(1).shipment_line_id
1104 FOR UPDATE NOWAIT;
1105 EXCEPTION
1106 WHEN OTHERS THEN
1107 IF (l_debug = 1) THEN
1108 print_debug('Some other exception occurred!',l_module_name,4);
1109 print_debug('SQLCODE:'||SQLCODE||' SQLERRM:'||SQLERRM,l_module_name,4);
1110 END IF;
1111 l_progress := '@@@';
1112 RAISE fnd_api.g_exc_unexpected_error;
1113 END;
1114 l_progress := '@@@';
1115
1116 IF (l_debug = 1) THEN
1117 print_debug('Calling query_reservation:',l_module_name,4);
1118 END IF;
1119
1120 l_progress := '###';
1121 query_reservation
1122 (p_query_input => l_rsv_query_rec
1123 ,p_sort_by_req_date => g_query_demand_ship_date_asc
1124 ,x_rsv_results => l_rsv_results_tbl
1125 ,x_return_status => l_return_status
1126 );
1127 l_progress := '###';
1128
1129 IF (l_return_status <> fnd_api.g_ret_sts_success) THEN
1130 l_progress := '@@@';
1131 RAISE fnd_api.g_exc_unexpected_error;
1132 END IF;
1133
1134 IF (l_debug = 1) THEN
1135 print_debug('l_rsv_results_tbl.COUNT = ' ||l_rsv_results_tbl.COUNT,l_module_name,4);
1136 END IF;
1137
1138 FOR i IN 1..l_rsv_results_tbl.COUNT LOOP
1139 IF (l_debug = 1) THEN
1140 print_debug(' i:'||i||
1141 ' rsv_id:'||l_rsv_results_tbl(i).reservation_id||
1142 ' prim_qty:'||l_rsv_results_tbl(i).primary_reservation_quantity||
1143 ' rsv_qty:'||l_rsv_results_tbl(i).reservation_quantity||
1144 ' uom_code:'||l_rsv_results_tbl(i).reservation_uom_code||
1145 ' orig_supply_src_type_id:'||l_rsv_results_tbl(i).orig_supply_source_type_id||
1146 ' demand_src_line_detail:'||l_rsv_results_tbl(i).demand_source_line_detail||
1147 ' ext_src_code:'||l_rsv_results_tbl(i).external_source_code||
1148 ' orig_supply_src_code:'||l_rsv_results_tbl(i).orig_supply_source_type_id
1149 ,l_module_name,4);
1150 END IF;
1151
1152 IF (l_rsv_results_tbl(i).orig_supply_source_type_id = g_source_type_asn) THEN
1153
1154 IF (l_debug = 1) THEN
1155 print_debug('Calling delete_reservation...',l_module_name,4);
1156 END IF;
1157
1158
1159 delete_reservation
1160 (p_rsv_rec => l_rsv_results_tbl(i)
1161 ,x_return_status => l_return_status
1162 );
1163
1164 IF (l_debug = 1) THEN
1165 print_debug('Returned from delete_reservation',l_module_name,4);
1166 print_debug('x_return_status: '||l_return_status,l_module_name,4);
1167 END IF;
1168
1169 IF (l_return_status <> fnd_api.g_ret_sts_success) THEN
1170 IF (l_debug = 1) THEN
1171 print_debug('SQLCODE:'||SQLCODE||' SQLERRM:'||SQLERRM,l_module_name,4);
1172 print_debug('Raising Exception!!!',l_module_name,4);
1173 END IF;
1174 l_progress := '@@@';
1175 RAISE fnd_api.g_exc_unexpected_error;
1176 END IF;
1177 ELSE --IF this is a manual rsv transferred from PO
1178 l_rsv_update_rec := l_rsv_results_tbl(i);
1179 l_rsv_update_rec.supply_source_type_id := g_source_type_po;
1180 l_rsv_update_rec.supply_source_line_detail := NULL;
1181
1182 IF (l_debug = 1) THEN
1183 print_debug('Calling transfer_reservation...',l_module_name,4);
1184 END IF;
1185
1186 transfer_reservation
1187 (p_original_rsv_rec => l_rsv_results_tbl(i)
1188 ,p_to_rsv_rec => l_rsv_update_rec
1189 ,x_new_rsv_id => l_reservation_id
1190 ,x_return_status => l_return_status);
1191
1192 IF (l_debug = 1) THEN
1193 print_debug('Returned from transfer_reservation',l_module_name,4);
1194 print_debug('x_return_status: '||l_return_status,l_module_name,4);
1195 END IF;
1196
1197 IF (l_return_status <> fnd_api.g_ret_sts_success) THEN
1198 l_progress := '@@@';
1199 RAISE fnd_api.g_exc_unexpected_error;
1200 END IF;
1201
1202 IF (l_debug = 1) THEN
1203 print_debug('l_reservation_id: '||l_reservation_id,l_module_name,4);
1204 END IF;
1205 END IF;--END l_rsv_results_tbl(i).external_source_code = 'XDOCK'
1206 END LOOP;
1207
1208 IF (l_debug = 1) THEN
1209 print_debug('Exitting maintain_rsv_cancel_asn with the following values:',l_module_name,4);
1210 print_debug('x_return_status => '||x_return_status,l_module_name,4);
1211 print_debug('x_msg_count => '||x_msg_count,l_module_name,4);
1212 print_debug('x_msg_data => '||x_msg_data,l_module_name,4);
1213 END IF;
1214
1215 --{{
1216 --********** END PROCEDURE maintain_rsv_cancel_asn *********}}
1217
1218 EXCEPTION
1219 WHEN OTHERS THEN
1220 IF (l_debug = 1) THEN
1221 print_debug('Exception occurred at progress: '||l_progress,l_module_name,4);
1222 print_debug('SQLCODE:'||SQLCODE||' SQLERRM:'||SQLERRM,l_module_name,4);
1223 END IF;
1224 x_return_status := fnd_api.g_ret_sts_error;
1225 END maintain_rsv_cancel_asn;
1226
1227 PROCEDURE maintain_rsv_receive
1228 (x_return_status OUT NOCOPY VARCHAR2
1229 ,x_msg_count OUT NOCOPY NUMBER
1230 ,x_msg_data OUT NOCOPY VARCHAR2
1231 ,p_cas_mol_rec_tb IN inv_rcv_integration_pvt.cas_mol_rec_tb_tp
1232 ,x_cas_mol_rec_tb OUT nocopy inv_rcv_integration_pvt.cas_mol_rec_tb_tp
1233 ) IS
1234 l_rsv_query_rec inv_reservation_global.mtl_reservation_rec_type;
1235 l_rsv_results_tbl inv_reservation_global.mtl_reservation_tbl_type;
1236 l_rsv_update_rec inv_reservation_global.mtl_reservation_rec_type;
1237 l_shipment_header_id NUMBER;
1238 l_requisition_header_id NUMBER;
1239 l_rsv_results_count NUMBER;
1240 l_remaining_prim_qty NUMBER;
1241 l_new_rsv_id NUMBER;
1242 l_new_wdd_id NUMBER;
1243 l_dummy NUMBER;
1244 l_primary_qty NUMBER;
1245
1246 l_return_status VARCHAR2(1);
1247 l_error_code NUMBER;
1248 l_msg_count NUMBER;
1249 l_msg_data VARCHAR2(2000);
1250
1251 l_debug NUMBER;
1252 l_progress VARCHAR2(10);
1253 l_module_name VARCHAR2(30);
1254
1255 -- Bug 9468018
1256 l_secondary_qty NUMBER;
1257 l_remaining_sec_qty NUMBER;
1258
1259 BEGIN
1260
1261 --{{
1262 --********** PROCEDURE maintain_rsv_receive *********}}
1263
1264 l_debug := NVL(fnd_profile.VALUE('INV_DEBUG_TRACE'), 0);
1265 l_progress := '10';
1266 l_module_name := 'MAINTAIN_RSV_RECEIVE';
1267 x_return_status := fnd_api.g_ret_sts_success;
1268
1269 IF (l_debug = 1) THEN
1270 print_debug('Entering maintain_rsv_receive...',l_module_name,4);
1271 print_debug(' p_cas_mol_rec_tb(1).inventory_item_id => '||p_cas_mol_rec_tb(1).inventory_item_id,l_module_name,4);
1272 print_debug(' p_cas_mol_rec_tb(1).organization_id => '||p_cas_mol_rec_tb(1).organization_id,l_module_name,4);
1273 print_debug(' p_cas_mol_rec_tb(1).primary_qty => '||p_cas_mol_rec_tb(1).primary_qty,l_module_name,4);
1274 print_debug(' p_cas_mol_rec_tb(1).secondary_quantity => '||p_cas_mol_rec_tb(1).secondary_quantity,l_module_name,4);
1275 print_debug(' p_cas_mol_rec_tb(1).po_header_id => '||p_cas_mol_rec_tb(1).po_header_id,l_module_name,4);
1276 print_debug(' p_cas_mol_rec_tb(1).po_line_location_id => '||p_cas_mol_rec_tb(1).po_line_location_id,l_module_name,4);
1277 print_debug(' p_cas_mol_rec_tb(1).shipment_line_id => '||p_cas_mol_rec_tb(1).shipment_line_id,l_module_name,4);
1278 print_debug(' p_cas_mol_rec_tb(1).requisition_line_id => '||p_cas_mol_rec_tb(1).requisition_line_id,l_module_name,4);
1279 print_debug(' p_cas_mol_rec_tb(1).auto_transact_code => '||p_cas_mol_rec_tb(1).auto_transact_code,l_module_name,4);
1280 print_debug(' p_cas_mol_rec_tb(1).project_id => '||p_cas_mol_rec_tb(1).project_id,l_module_name,4);
1281 print_debug(' p_cas_mol_rec_tb(1).task_id => '||p_cas_mol_rec_tb(1).task_id,l_module_name,4);
1282 END IF;
1283
1284 -- Bug 9468018
1285 l_primary_qty := abs(p_cas_mol_rec_tb(1).primary_qty);
1286 l_secondary_qty := abs(p_cas_mol_rec_tb(1).secondary_quantity);
1287
1288 --1.0 Set up query criteria
1289
1290 --{{
1291 --Test ASN recept and PO receipt. Make sure that ASN receipt
1292 --would not pick up PO reservation, and vice versa }}
1293 IF p_cas_mol_rec_tb(1).po_line_location_id IS NOT NULL THEN
1294 IF p_cas_mol_rec_tb(1).asn_line_flag = 'Y' THEN
1295 IF (l_debug = 1) THEN
1296 print_debug('This is an ASN receipt',l_module_name,4);
1297 END IF;
1298
1299 l_rsv_query_rec.supply_source_type_id := g_source_type_asn;
1300 l_rsv_query_rec.supply_source_header_id := p_cas_mol_rec_tb(1).po_header_id;
1301 l_rsv_query_rec.supply_source_line_id := p_cas_mol_rec_tb(1).po_line_location_id;
1302 l_rsv_query_rec.supply_source_line_detail := p_cas_mol_rec_tb(1).shipment_line_id;
1303
1304 l_progress := '###';
1305 BEGIN
1306 SELECT 1
1307 INTO l_dummy
1308 FROM rcv_shipment_lines
1309 WHERE shipment_line_id = p_cas_mol_rec_tb(1).shipment_line_id
1310 FOR UPDATE NOWAIT;
1311 EXCEPTION
1312 WHEN OTHERS THEN
1313 IF (l_debug = 1) THEN
1314 print_debug('Some other exception occurred! Raising Exception!',l_module_name,4);
1315 print_debug('SQLCODE:'||SQLCODE||' SQLERRM:'||SQLERRM,l_module_name,4);
1316 END IF;
1317 l_progress := '###';
1318 RAISE fnd_api.g_exc_unexpected_error;
1319 END;
1320 l_progress := '###';
1321 ELSE --asn_line_flag = 'Y' THEN
1322 IF (l_debug = 1) THEN
1323 print_debug('This is a PO receipt',l_module_name,4);
1324 END IF;
1325
1326 l_rsv_query_rec.supply_source_type_id := g_source_type_po;
1327 l_rsv_query_rec.supply_source_header_id := p_cas_mol_rec_tb(1).po_header_id;
1328 l_rsv_query_rec.supply_source_line_id := p_cas_mol_rec_tb(1).po_line_location_id;
1329
1330 l_progress := '###';
1331 BEGIN
1332 SELECT 1
1333 INTO l_dummy
1334 FROM po_line_locations_all
1335 WHERE line_location_id = p_cas_mol_rec_tb(1).po_line_location_id
1336 FOR UPDATE NOWAIT;
1337 EXCEPTION
1338 WHEN OTHERS THEN
1339 IF (l_debug = 1) THEN
1340 print_debug('Some other exception occurred! Raising Exception!',l_module_name,4);
1341 print_debug('SQLCODE:'||SQLCODE||' SQLERRM:'||SQLERRM,l_module_name,4);
1342 END IF;
1343 l_progress := '###';
1344 RAISE fnd_api.g_exc_unexpected_error;
1345 END;
1346 l_progress := '###';
1347 END IF;
1348
1349 --{{
1350 --Test REQ receipt }}
1351 ELSIF p_cas_mol_rec_tb(1).requisition_line_id IS NOT NULL THEN -- INTREQ
1352 l_progress := '###';
1353 BEGIN
1354 SELECT requisition_header_id
1355 INTO l_requisition_header_id
1356 FROM po_requisition_lines_all
1357 WHERE requisition_line_id = p_cas_mol_rec_tb(1).requisition_line_id
1358 FOR UPDATE NOWAIT;
1359
1360 l_progress := '###';
1361 SELECT 1
1362 INTO l_dummy
1363 FROM rcv_shipment_lines
1364 WHERE requisition_line_id = p_cas_mol_rec_tb(1).requisition_line_id
1365 AND shipment_line_id = p_cas_mol_rec_tb(1).shipment_line_id
1366
1367 FOR UPDATE NOWAIT;
1368 EXCEPTION
1369 WHEN OTHERS THEN
1370 IF (l_debug = 1) THEN
1371 print_debug('Some other exception occurred! Raising Exception!',l_module_name,4);
1372 print_debug('SQLCODE:'||SQLCODE||' SQLERRM:'||SQLERRM,l_module_name,4);
1373 END IF;
1374 RAISE fnd_api.g_exc_unexpected_error;
1375 END;
1376 l_progress := '###';
1377
1378 l_rsv_query_rec.supply_source_type_id := g_source_type_internal_req;
1379 l_rsv_query_rec.supply_source_header_id := l_requisition_header_id;
1380 l_rsv_query_rec.supply_source_line_id := p_cas_mol_rec_tb(1).requisition_line_id;
1381
1382 --{{
1383 --Test Intrasit Shipment receipt. Also test receipt of INTREQ
1384 --through the intrasit Shipment option}}
1385 ELSIF p_cas_mol_rec_tb(1).shipment_line_id IS NOT NULL THEN --INTSHIP
1386
1387 l_progress := '###';
1388 BEGIN
1389 SELECT shipment_header_id
1390 INTO l_shipment_header_id
1391 FROM rcv_shipment_lines
1392 WHERE shipment_line_id = p_cas_mol_rec_tb(1).shipment_line_id
1393 FOR UPDATE NOWAIT;
1394 EXCEPTION
1395 WHEN OTHERS THEN
1396 IF (l_debug = 1) THEN
1397 print_debug('Some other exception occurred! Raising Exception!',l_module_name,4);
1398 print_debug('SQLCODE:'||SQLCODE||' SQLERRM:'||SQLERRM,l_module_name,4);
1399 END IF;
1400 l_progress := '###';
1401 RAISE fnd_api.g_exc_unexpected_error;
1402 END;
1403 l_progress := '###';
1404
1405 l_rsv_query_rec.supply_source_type_id := g_source_type_in_transit;
1406 l_rsv_query_rec.supply_source_header_id := l_shipment_header_id;
1407 l_rsv_query_rec.supply_source_line_id := p_cas_mol_rec_tb(1).shipment_line_id;
1408
1409 --{{
1410 --Test RMA receipt}}
1411 ELSE --RMA
1412 IF (l_debug = 1) THEN
1413 print_debug('This is an RMA receipt. No need to query reservations',l_module_name,4);
1414 END IF;
1415
1416 l_progress := '###';
1417 set_mol_wdd_tbl(p_cas_mol_rec_tb(1),
1418 x_cas_mol_rec_tb,
1419 l_primary_qty,
1420 NULL,
1421 NULL);
1422 l_progress := '###';
1423
1424 RETURN;
1425 END IF;
1426
1427 l_rsv_query_rec.inventory_item_id := p_cas_mol_rec_tb(1).inventory_item_id;
1428 l_rsv_query_rec.organization_id := p_cas_mol_rec_tb(1).organization_id;
1429
1430 IF (p_cas_mol_rec_tb(1).project_id IS NOT NULL) THEN
1431 l_rsv_query_rec.project_id := p_cas_mol_rec_tb(1).project_id;
1432 IF (p_cas_mol_rec_tb(1).task_id IS NOT NULL) THEN
1433 l_rsv_query_rec.task_id := p_cas_mol_rec_tb(1).task_id;
1434 END IF;
1435 END IF ;
1436
1437 --2.0 Query reservation
1438 IF (l_debug = 1) THEN
1439 print_debug('Calling query_reservation:',l_module_name,4);
1440 END IF;
1441
1442 l_progress := '###';
1443 query_reservation
1444 (p_query_input => l_rsv_query_rec
1445 ,p_sort_by_req_date => g_query_demand_ship_date_asc
1446 ,x_rsv_results => l_rsv_results_tbl
1447 ,x_return_status => l_return_status
1448 );
1449 l_progress := '###';
1450
1451 IF (l_return_status <> fnd_api.g_ret_sts_success) THEN
1452 l_progress := '@@@';
1453 RAISE fnd_api.g_exc_unexpected_error;
1454 END IF;
1455
1456 --3.0 Process reservations
1457 l_remaining_prim_qty := l_primary_qty;
1458 -- Bug 9468018
1459 l_remaining_sec_qty := l_secondary_qty;
1460
1461 IF (l_debug = 1) THEN
1462 print_debug('l_remaining_prim_qty = ' ||l_remaining_prim_qty,l_module_name,4);
1463 print_debug('l_remaining_sec_qty = ' ||l_remaining_sec_qty,l_module_name,4);
1464 print_debug('l_rsv_results_tbl.COUNT = ' ||l_rsv_results_tbl.COUNT,l_module_name,4);
1465 END IF;
1466
1467
1468 --{{
1469 --Create multiple reservations for the same document and item.
1470 --Make sure that the results returned are in the correct order according
1471 --to the demand ship date}}
1472 FOR i IN 1..l_rsv_results_tbl.COUNT LOOP
1473 EXIT WHEN l_remaining_prim_qty <= 0;
1474
1475 IF (l_debug = 1) THEN
1476 print_debug('l_remaining_prim_qty:'||l_remaining_prim_qty||
1477 'l_remaining_sec_qty:'||l_remaining_sec_qty||
1478 ' i:'||i||
1479 ' rsv_id:'||l_rsv_results_tbl(i).reservation_id||
1480 ' prim_qty:'||l_rsv_results_tbl(i).primary_reservation_quantity||
1481 ' rsv_qty:'||l_rsv_results_tbl(i).reservation_quantity||
1482 ' sec_rsv_qty:'||l_rsv_results_tbl(i).secondary_reservation_quantity||
1483 ' uom_code:'||l_rsv_results_tbl(i).reservation_uom_code||
1484 ' orig_supply_src_type_id:'||l_rsv_results_tbl(i).orig_supply_source_type_id||
1485 ' demand_src_line_detail:'||l_rsv_results_tbl(i).demand_source_line_detail||
1486 ' ext_src_code:'||l_rsv_results_tbl(i).external_source_code
1487 ,l_module_name,4);
1488 END IF;
1489
1490 l_rsv_update_rec := l_rsv_results_tbl(i);
1491
1492 --MANEESH: For direct receipt and the case where reservation is
1493 --modifyed BY pegging engine, delete the reservation, which should
1494 --also update WDD accordingly
1495 --{{
1496 --Create a reservations, have it modified by pegging engine, then
1497 --Perform direct receipt. Make sure that reservations are
1498 --relieved/deleted appropriately }}
1499 IF Nvl(p_cas_mol_rec_tb(1).auto_transact_code,'RECEIVE') = 'DELIVER' AND l_rsv_results_tbl(i).external_source_code = 'XDOCK' THEN
1500 IF (l_rsv_results_tbl(i).primary_reservation_quantity >= l_remaining_prim_qty) THEN
1501 l_rsv_update_rec.primary_reservation_quantity := l_remaining_prim_qty;
1502 l_rsv_update_rec.secondary_reservation_quantity := l_remaining_sec_qty; -- Bug: 9846574
1503 l_remaining_prim_qty := 0;
1504 ELSE
1505 l_rsv_update_rec.primary_reservation_quantity := l_rsv_results_tbl(i).primary_reservation_quantity;
1506 l_rsv_update_rec.secondary_reservation_quantity := l_rsv_results_tbl(i).secondary_reservation_quantity; -- Bug: 9846574
1507 l_remaining_prim_qty := l_remaining_prim_qty - l_rsv_results_tbl(i).primary_reservation_quantity;
1508 l_remaining_sec_qty := l_remaining_sec_qty - l_rsv_results_tbl(i).secondary_reservation_quantity; -- Bug: 9846574
1509 END IF;
1510
1511 IF (l_debug = 1) THEN
1512 print_debug('Calling relieve_reservation...',l_module_name,4);
1513 print_debug(' l_rsv_update_rec.primary_reservation_quantity => '||l_rsv_update_rec.primary_reservation_quantity,l_module_name,4);
1514 print_debug(' l_rsv_update_rec.secondary_reservation_quantity => '||l_rsv_update_rec.secondary_reservation_quantity,l_module_name,4); -- Bug: 9846574
1515 END IF;
1516
1517 --Relieve/delete reservation
1518 l_progress := '###';
1519 relieve_reservation
1520 (p_rsv_rec => l_rsv_results_tbl(i)
1521 ,p_prim_qty_to_relieve => l_rsv_update_rec.primary_reservation_quantity
1522 ,x_return_status => l_return_status
1523 );
1524 l_progress := '###';
1525
1526 IF (l_debug = 1) THEN
1527 print_debug('Returned from delete_reservation',l_module_name,4);
1528 print_debug('x_return_status: '||l_return_status,l_module_name,4);
1529 END IF;
1530
1531 IF (l_return_status <> fnd_api.g_ret_sts_success) THEN
1532 l_progress := '@@@';
1533 RAISE fnd_api.g_exc_unexpected_error;
1534 END IF;
1535
1536
1537 --{{
1538 --Test the following cases:
1539 --1. standard Receipt that will match to a manual reservations
1540 --2. standard receipt that will match to a xdock reservations
1541 --3. direct receipt that will match to a manual reservations whose
1542 -- ext_src_code is not crossdock
1543 --4. direct receipt that will match to a xdock reservation whose
1544 -- ext_src_code is not crossdock}}
1545 ELSE --Reservation is not modified by pegging engine
1546
1547 --{{
1548 --Test cases in which reservations/WDD need to be split}}
1549 IF (l_rsv_results_tbl(i).primary_reservation_quantity > l_remaining_prim_qty) THEN
1550 -- Reservation has more than enough to satisfy remaining qty, so split
1551
1552 IF (l_debug = 1) THEN
1553 print_debug('l_rsv_results_tbl(i).primary_reservation_quantity > l_remaining_prim_qty',
1554 l_module_name,4);
1555 END IF;
1556
1557 l_rsv_update_rec.primary_reservation_quantity := l_remaining_prim_qty;
1558 l_rsv_update_rec.secondary_reservation_quantity := l_remaining_sec_qty; -- Bug: 9846574
1559 l_rsv_update_rec.reservation_quantity := inv_rcv_cache.convert_qty(p_cas_mol_rec_tb(1).inventory_item_id
1560 ,l_remaining_prim_qty
1561 ,l_rsv_results_tbl(i).primary_uom_code
1562 ,l_rsv_results_tbl(i).reservation_uom_code);
1563 -- Bug 9468018
1564 l_rsv_update_rec.secondary_reservation_quantity := l_remaining_sec_qty;
1565
1566 IF (l_debug = 1) THEN
1567 print_debug('l_rsv_update_rec quantites:' , l_module_name,4);
1568 print_debug('l_rsv_update_rec.primary_reservation_quantity = ' ||l_rsv_update_rec.primary_reservation_quantity,l_module_name,4);
1569 print_debug('l_rsv_update_rec.secondary_reservation_quantity = ' ||l_rsv_update_rec.secondary_reservation_quantity,l_module_name,4);
1570 END IF;
1571
1572 IF (l_rsv_results_tbl(i).demand_source_line_detail IS NOT NULL) THEN
1573 IF (l_debug = 1) THEN
1574 print_debug('Calling split_wdd...',l_module_name,4);
1575 END IF;
1576
1577 l_progress := '@@@';
1578 split_wdd
1579 (x_return_status => l_return_status
1580 ,x_msg_count => l_msg_count
1581 ,x_msg_data => l_msg_data
1582 ,x_new_wdd_id => l_new_wdd_id
1583 ,p_wdd_id => l_rsv_results_tbl(i).demand_source_line_detail
1584 ,p_new_mol_id => NULL
1585 ,p_qty_to_splt => l_remaining_prim_qty);
1586 l_progress := '@@@';
1587
1588 IF (l_debug = 1) THEN
1589 print_debug('Returned from split_wdd',l_module_name,4);
1590 print_debug('x_return_status: '||l_return_status,l_module_name,4);
1591 END IF;
1592
1593 IF (l_return_status <> fnd_api.g_ret_sts_success) THEN
1594 l_progress := '@@@';
1595 RAISE fnd_api.g_exc_unexpected_error;
1596 END IF;
1597
1598 IF (l_debug = 1) THEN
1599 print_debug('l_new_wdd_id: '||l_new_wdd_id,l_module_name,4);
1600 END IF;
1601
1602 --Depending on the routing, I will transfer/update released
1603 --status later
1604 l_rsv_update_rec.demand_source_line_detail := l_new_wdd_id;
1605
1606 END IF;
1607
1608 l_remaining_prim_qty := 0;
1609 l_remaining_sec_qty := 0; -- Bug: 9846574
1610
1611 ELSE --l_rsv_results_tbl(i).primary_reservation_quantity <= l_remaining_prim_qty THEN
1612 IF (l_debug = 1) THEN
1613 print_debug('l_rsv_results_tbl(i).primary_reservation_quantity <= l_remaining_prim_qty',
1614 l_module_name,4);
1615 END IF;
1616 l_remaining_prim_qty := l_remaining_prim_qty - l_rsv_results_tbl(i).primary_reservation_quantity;
1617 l_remaining_sec_qty := l_remaining_sec_qty - l_rsv_results_tbl(i).secondary_reservation_quantity; -- Bug: 9846574
1618
1619 -- Bug 9468018
1620 l_remaining_sec_qty := l_remaining_sec_qty - l_rsv_results_tbl(i).secondary_reservation_quantity;
1621
1622 IF (l_debug = 1) THEN
1623 print_debug('Updated remaining quantites:' , l_module_name,4);
1624 print_debug('l_remaining_prim_qty = ' ||l_remaining_prim_qty,l_module_name,4);
1625 print_debug('l_remaining_sec_qty = ' ||l_remaining_sec_qty,l_module_name,4);
1626 END IF;
1627
1628 END IF; --END IF (l_rsv_results_tbl(i).primary_reservation_quantity > l_remaining_prim_qty)
1629
1630 IF Nvl(p_cas_mol_rec_tb(1).auto_transact_code,'RECEIVE') = 'DELIVER' THEN
1631 --Direct Receipt and the reservation has not been modified by the
1632 --xdock pegging engine
1633 l_rsv_update_rec.supply_source_type_id := g_source_type_inv;
1634 l_rsv_update_rec.demand_source_line_detail := NULL;
1635 ELSE
1636 --Standard/Inspection routing receipt
1637 l_rsv_update_rec.supply_source_type_id := g_source_type_rcv;
1638 END IF;
1639
1640 --Null out supply source info
1641 l_rsv_update_rec.supply_source_header_id := NULL;
1642 l_rsv_update_rec.supply_source_line_id := NULL;
1643 l_rsv_update_rec.supply_source_line_detail := NULL;
1644
1645 IF Nvl(p_cas_mol_rec_tb(1).auto_transact_code,'RECEIVE') = 'DELIVER' AND l_rsv_results_tbl(i).demand_source_line_detail IS NOT NULL THEN
1646
1647 IF (l_debug = 1) THEN
1648 print_debug('Calling update_wdd...',l_module_name,4);
1649 END IF;
1650
1651 l_progress := '@@@';
1652 update_wdd
1653 (x_return_status => l_return_status
1654 ,x_msg_count => l_msg_count
1655 ,x_msg_data => l_msg_data
1656 ,p_wdd_id => l_rsv_update_rec.demand_source_line_detail
1657 ,p_released_status => 'R' --Ready to released
1658 ,p_mol_id => NULL
1659 );
1660 l_progress := '@@@';
1661
1662 IF (l_return_status <> fnd_api.g_ret_sts_success) THEN
1663 l_progress := '@@@';
1664 RAISE fnd_api.g_exc_unexpected_error;
1665 END IF;
1666
1667 l_rsv_update_rec.demand_source_line_detail := NULL;
1668 END IF;
1669
1670 IF (l_debug = 1) THEN
1671 print_debug('Calling transfer_reservation...',l_module_name,4);
1672 END IF;
1673 -- 10396979 addition
1674 l_rsv_update_rec.subinventory_code := p_cas_mol_rec_tb(1).subinventory_code;
1675 l_rsv_update_rec.locator_id := p_cas_mol_rec_tb(1).locator_id;
1676 l_rsv_update_rec.lot_number := p_cas_mol_rec_tb(1).lot_number;
1677
1678 print_debug('Added the value for subinventory '||l_rsv_update_rec.subinventory_code,l_module_name,4);
1679 print_debug('Added the value for locator '||l_rsv_update_rec.locator_id,l_module_name,4);
1680 print_debug('Added the value for lot_number '||l_rsv_update_rec.lot_number,l_module_name,4);
1681
1682 -- 10396979 addition
1683 -- 13784450 addition
1684 l_rsv_update_rec.lpn_id := p_cas_mol_rec_tb(1).lpn_id;
1685 print_debug('Added the value for lpn_id '||l_rsv_update_rec.lpn_id,l_module_name,4);
1686 -- 13784450 addition
1687
1688 l_progress := '@@@';
1689 transfer_reservation
1690 (p_original_rsv_rec => l_rsv_results_tbl(i)
1691 ,p_to_rsv_rec => l_rsv_update_rec
1692 ,x_new_rsv_id => l_new_rsv_id
1693 ,x_return_status => l_return_status);
1694 l_progress := '@@@';
1695
1696 IF (l_return_status <> fnd_api.g_ret_sts_success) THEN
1697 l_progress := '@@@';
1698 RAISE fnd_api.g_exc_unexpected_error;
1699 END IF;
1700
1701 IF (l_debug = 1) THEN
1702 print_debug('l_new_rsv_id: '||l_new_rsv_id,l_module_name,4);
1703 END IF;
1704 END IF;--IF p_cas_mol_rec_tb(1).auto_transact_code = 'DELIVER' AND l_rsv_results_tbl(i).external_source_code = 'XDOCK' THEN
1705
1706 --For standard/inspection routing receipt, populate x_cas_mol_rec_tb
1707 --so that maintain_mo_cons API can create MOL with the specific WDD
1708 IF (Nvl(p_cas_mol_rec_tb(1).auto_transact_code,'RECEIVE') <> 'DELIVER') THEN
1709 IF l_rsv_update_rec.demand_source_type_id IN (g_source_type_internal_ord,g_source_type_oe) THEN
1710 set_mol_wdd_tbl(p_cas_mol_rec_tb(1),
1711 x_cas_mol_rec_tb,
1712 l_rsv_update_rec.primary_reservation_quantity,
1713 l_rsv_update_rec.demand_source_line_detail,
1714 1
1715 );
1716 /* Bug 5244500 : If source type is INV, then cross dock type should be set
1717 to null.*/
1718 ELSIF (l_rsv_update_rec.demand_source_type_id = g_source_type_inv ) THEN
1719 set_mol_wdd_tbl(p_cas_mol_rec_tb(1),
1720 x_cas_mol_rec_tb,
1721 l_rsv_update_rec.primary_reservation_quantity,
1722 l_rsv_update_rec.demand_source_line_detail,
1723 null
1724 );
1725 ELSE
1726 set_mol_wdd_tbl(p_cas_mol_rec_tb(1),
1727 x_cas_mol_rec_tb,
1728 l_rsv_update_rec.primary_reservation_quantity,
1729 NVL(l_rsv_update_rec.demand_source_line_detail,l_rsv_update_rec.demand_source_header_id), --bug 9706800, for opm, the backorder_delivery_detail_id on MOL should be stamped with entity_id (demand_source_header_id)
1730 2
1731 );
1732 END IF;
1733 END IF;
1734 END LOOP;
1735
1736 --For standard/inspection routing receipt, populate x_cas_mol_rec_tb
1737 --so that MOL will be created for the the quantity with no reservation
1738
1739 --??? For + Corr of Receive, auto_transact_code is passed NULL. Assume
1740 --that NULL is same as RECEIVE. Is it the right assumption???
1741 IF (Nvl(p_cas_mol_rec_tb(1).auto_transact_code,'RECEIVE') <> 'DELIVER' AND l_remaining_prim_qty > 0) THEN
1742 set_mol_wdd_tbl(p_cas_mol_rec_tb(1),
1743 x_cas_mol_rec_tb,
1744 l_remaining_prim_qty,
1745 NULL,
1746 NULL);
1747 END IF;
1748
1749 IF (l_debug = 1) THEN
1750 print_debug('Exitting maintain_rsv_receive with the following values:',l_module_name,4);
1751 print_debug('x_return_status => '||x_return_status,l_module_name,4);
1752 print_debug('x_msg_count => '||x_msg_count,l_module_name,4);
1753 print_debug('x_msg_data => '||x_msg_data,l_module_name,4);
1754 END IF;
1755
1756 --{{
1757 --********** END PROCEDURE maintain_rsv_receive *********}}
1758
1759 EXCEPTION
1760 WHEN OTHERS THEN
1761 x_return_status := fnd_api.g_ret_sts_error;
1762 IF (l_debug = 1) THEN
1763 print_debug('Exception occurred at progress: '||l_progress,l_module_name,4);
1764 print_debug('SQLCODE:'||SQLCODE||' SQLERRM:'||SQLERRM,l_module_name,4);
1765 print_debug('Exiting maintain_rsv_receive with error: '||l_progress,l_module_name,4);
1766 END IF;
1767 END maintain_rsv_receive;
1768
1769 PROCEDURE maintain_rsv_deliver
1770 (x_return_status OUT NOCOPY VARCHAR2
1771 ,x_msg_count OUT NOCOPY NUMBER
1772 ,x_msg_data OUT NOCOPY VARCHAR2
1773 ,p_cas_mol_rec_tb IN inv_rcv_integration_pvt.cas_mol_rec_tb_tp
1774 ) IS
1775 l_rsv_reduce_rec inv_reservation_global.mtl_maintain_rsv_rec_type;
1776 l_rsv_query_rec inv_reservation_global.mtl_reservation_rec_type;
1777 l_rsv_results_tbl inv_reservation_global.mtl_reservation_tbl_type;
1778 l_rsv_update_rec inv_reservation_global.mtl_reservation_rec_type;
1779 l_remaining_prim_qty NUMBER;
1780 l_new_rsv_id NUMBER;
1781 l_new_wdd_id NUMBER;
1782 l_dummy NUMBER;
1783 l_doc_type NUMBER;
1784
1785 l_return_status VARCHAR2(1);
1786 l_error_code NUMBER;
1787 l_msg_count NUMBER;
1788 l_msg_data VARCHAR2(2000);
1789
1790 l_debug NUMBER;
1791 l_progress VARCHAR2(10);
1792 l_module_name VARCHAR2(30);
1793 l_reference VARCHAR2(240); --Bug 8641693
1794 l_reference_id NUMBER; --Bug 8641693
1795 l_orig_supply_source_line_id NUMBER; --Bug 8641693
1796 --Adding for Bug 14038720
1797 l_lpn_id NUMBER ;
1798 l_demand_source_line_id NUMBER;
1799 l_lot_number VARCHAR2(80);
1800
1801
1802 BEGIN
1803
1804 --{{
1805 --********** PROCEDURE maintain_rsv_deliver *********}}
1806
1807 l_debug := NVL(fnd_profile.VALUE('INV_DEBUG_TRACE'), 0);
1808 l_progress := '10';
1809 l_module_name := 'MAINTAIN_RSV_DELIVER';
1810 x_return_status := fnd_api.g_ret_sts_success;
1811
1812 IF (l_debug = 1) THEN
1813 print_debug('Entering maintain_rsv_deliver...',l_module_name,4);
1814 print_debug(' p_cas_mol_rec_tb(1).line_id => '||p_cas_mol_rec_tb(1).line_id,l_module_name,4);
1815 print_debug(' p_cas_mol_rec_tb(1).inventory_item_id => '||p_cas_mol_rec_tb(1).inventory_item_id,l_module_name,4);
1816 print_debug(' p_cas_mol_rec_tb(1).organization_id => '||p_cas_mol_rec_tb(1).organization_id,l_module_name,4);
1817 print_debug(' p_cas_mol_rec_tb(1).primary_qty => '||p_cas_mol_rec_tb(1).primary_qty,l_module_name,4);
1818 print_debug(' p_cas_mol_rec_tb(1).project_id => '||p_cas_mol_rec_tb(1).project_id,l_module_name,4);
1819 print_debug(' p_cas_mol_rec_tb(1).task_id => '||p_cas_mol_rec_tb(1).task_id,l_module_name,4);
1820 print_debug(' p_cas_mol_rec_tb(1).bdd_id => '||p_cas_mol_rec_tb(1).backorder_delivery_detail_id,l_module_name,4);
1821 print_debug(' p_cas_mol_rec_tb(1).crossdock_type => '||p_cas_mol_rec_tb(1).crossdock_type,l_module_name,4);
1822 print_debug(' p_cas_mol_rec_tb(1).wip_supply_type => '||p_cas_mol_rec_tb(1).wip_supply_type,l_module_name,4);
1823 print_debug(' p_cas_mol_rec_tb(1).subinventory_code => '||p_cas_mol_rec_tb(1).subinventory_code,l_module_name,4);
1824 print_debug(' p_cas_mol_rec_tb(1).locator_id => '||p_cas_mol_rec_tb(1).locator_id,l_module_name,4);
1825 print_debug(' p_cas_mol_rec_tb(1).lot_number => '||p_cas_mol_rec_tb(1).lot_number,l_module_name,4);
1826 print_debug(' p_cas_mol_rec_tb(1).item_revision => '||p_cas_mol_rec_tb(1).item_revision,l_module_name,4);
1827 END IF;
1828
1829 --{{
1830 --Try to lock the MOL row from SQLPLUS, and see if transaction would
1831 --fail at this point}}
1832 l_progress := '@@@';
1833 BEGIN
1834 SELECT 1
1835 INTO l_dummy
1836 FROM mtl_txn_request_lines
1837 WHERE line_id = p_cas_mol_rec_tb(1).line_id
1838 FOR UPDATE NOWAIT;
1839 EXCEPTION
1840 WHEN OTHERS THEN
1841 IF (l_debug = 1) THEN
1842 print_debug('Some other exception occurred!',l_module_name,4);
1843 print_debug('SQLCODE:'||SQLCODE||' SQLERRM:'||SQLERRM,l_module_name,4);
1844 END IF;
1845 l_progress := '@@@';
1846 RAISE fnd_api.g_exc_unexpected_error;
1847 END;
1848
1849 l_progress := '@@@';
1850
1851 --{{
1852 --Test WIP Push xdock}}
1853 IF p_cas_mol_rec_tb(1).crossdock_type = 2 AND p_cas_mol_rec_tb(1).wip_supply_type = 1 THEN
1854 IF (l_debug = 1) THEN
1855 print_debug('No Need to Handle Reservations for crossdock to a WIP push demand',l_module_name,4);
1856 END IF;
1857
1858 --Bug 5249929 - No need to call reduce reservations for WIP demands
1859 --as we do not support wip as a demand in reservations
1860
1861 --l_rsv_reduce_rec.action := 1; --Supply
1862 --l_rsv_reduce_rec.inventory_item_id := p_cas_mol_rec_tb(1).inventory_item_id;
1863 --l_rsv_reduce_rec.organization_id := p_cas_mol_rec_tb(1).organization_id;
1864 --l_rsv_reduce_rec.supply_source_type_id := g_source_type_rcv;
1865 --l_rsv_reduce_rec.demand_source_type_id := g_source_type_wip;
1866 --l_rsv_reduce_rec.demand_source_header_id := p_cas_mol_rec_tb(1).backorder_delivery_detail_id;--???
1867
1868 --???
1869 /*IF (p_cas_mol_rec_tb(1).project_id IS NOT NULL) THEN
1870 l_rsv_reduce_rec.project_id := p_cas_mol_rec_tb(1).project_id;
1871 IF (p_cas_mol_rec_tb(1).task_id IS NOT NULL) THEN
1872 l_rsv_reduce_rec.task_id := p_cas_mol_rec_tb(1).task_id;
1873 END IF;
1874 END IF ;
1875 */
1876
1877 --{{
1878 --Make sure that the reservations are reduced appropriately}}
1879 --l_progress := '@@@';
1880 --reduce_reservation
1881 --(p_mtl_rsv_rec => l_rsv_reduce_rec
1882 --,x_return_status => l_return_status
1883 --);
1884 --l_progress := '@@@';
1885
1886 --IF (l_debug = 1) THEN
1887 -- print_debug('Returned from update_wdd',l_module_name,4);
1888 -- print_debug('x_return_status: '||l_return_status,l_module_name,4);
1889 --END IF;
1890
1891 --IF (l_return_status <> fnd_api.g_ret_sts_success) THEN
1892 -- l_progress := '@@@';
1893 -- RAISE fnd_api.g_exc_unexpected_error;
1894 --END IF;
1895
1896 --{{
1897 --Test SO Xdock, WIP Pull or non-crossdock cases}}
1898 ELSE
1899 IF (l_debug = 1) THEN
1900 print_debug('SO Xdock/WIP-Pull/Non-xdock',l_module_name,4);
1901 END IF;
1902
1903 l_rsv_query_rec.inventory_item_id := p_cas_mol_rec_tb(1).inventory_item_id;
1904 l_rsv_query_rec.organization_id := p_cas_mol_rec_tb(1).organization_id;
1905 l_rsv_query_rec.supply_source_type_id := g_source_type_rcv;
1906
1907
1908 IF (p_cas_mol_rec_tb(1).project_id IS NOT NULL) THEN
1909 l_rsv_query_rec.project_id := p_cas_mol_rec_tb(1).project_id;
1910 IF (p_cas_mol_rec_tb(1).task_id IS NOT NULL) THEN
1911 l_rsv_query_rec.task_id := p_cas_mol_rec_tb(1).task_id;
1912 END IF;
1913 END IF ;
1914
1915 IF p_cas_mol_rec_tb(1).crossdock_type = 2 THEN --WIP
1916 --{{
1917 --Test deliver of WIP crossdock PULL. Make sure that mr.backorder_delivery_detail_id corresponds
1918 --to mol.backorder_delivery_detail_id }}
1919 IF (l_debug = 1) THEN
1920 print_debug('This is a WIP pull xdock',4);
1921 END IF;
1922 l_rsv_query_rec.demand_source_type_id := g_source_type_wip;
1923 l_rsv_query_rec.demand_source_header_id := p_cas_mol_rec_tb(1).backorder_delivery_detail_id;--???
1924 ELSIF p_cas_mol_rec_tb(1).crossdock_type = 1
1925 AND p_cas_mol_rec_tb(1).backorder_delivery_detail_id IS NOT NULL THEN --SO
1926 IF (l_debug = 1) THEN
1927 print_debug('This is a SO xdock',4);
1928 END IF;
1929
1930 --{{
1931 --Test deliver of a SO (for both Internal Order and Sale order.
1932 --When processing results for SO xdock. Make sure that
1933 --1) There is only 1 result returned
1934 --2) The appropriate wdd are updated to STAGED status
1935 --3) The appropriate reservation is transferred to INVENTORY (Check source_type columns)}}
1936
1937 BEGIN
1938 SELECT Nvl(source_document_type_id, -1)
1939 INTO l_doc_type
1940 FROM wsh_delivery_details
1941 WHERE delivery_detail_id = p_cas_mol_rec_tb(1).backorder_delivery_detail_id;
1942 EXCEPTION
1943 WHEN OTHERS THEN
1944 IF (l_debug = 1) THEN
1945 print_debug('Error retrieving doc type for SO',l_module_name,4);
1946 END IF;
1947 RAISE fnd_api.g_exc_unexpected_error;
1948 END;
1949
1950 IF (l_debug = 1) THEN
1951 print_debug('l_doc_type:'||l_doc_type,4);
1952 END IF;
1953
1954 IF l_doc_type = 10 THEN
1955 l_rsv_query_rec.demand_source_type_id := g_source_type_internal_ord;
1956 ELSE
1957 l_rsv_query_rec.demand_source_type_id := g_source_type_oe;
1958 END if;
1959 l_rsv_query_rec.demand_source_line_detail := p_cas_mol_rec_tb(1).backorder_delivery_detail_id;
1960 ELSE -- Non-crossdock cases
1961 IF (l_debug = 1) THEN
1962 print_debug('This is a non-xdock case',4);
1963 END IF;
1964
1965
1966
1967 --Bug 8641693
1968 l_reference_id := NULL;
1969 l_reference := NULL;
1970 l_orig_supply_source_line_id := NULL;
1971
1972 BEGIN
1973
1974 SELECT reference, reference_id ,lpn_id,lot_number
1975 INTO l_reference, l_reference_id ,l_lpn_id,l_lot_number
1976 FROM mtl_txn_request_lines
1977 WHERE line_id = p_cas_mol_rec_tb(1).line_id AND reference_type_code = 4;
1978
1979 print_debug('l_reference ::'|| l_reference ,l_module_name,4);
1980 print_debug('l_reference_id ::' || l_reference_id,l_module_name,4);
1981 print_debug('Move order lpn_id ::' || l_lpn_id,l_module_name,4);
1982
1983 --below code is only for reference:
1984
1985 IF (l_reference IS NOT NULL AND l_lpn_id IS NOT NULL ) THEN
1986
1987 IF (l_reference = 'PO_LINE_LOCATION_ID') THEN
1988
1989 SELECT prha.interface_source_line_id INTO l_demand_source_line_id
1990 FROM po_requisition_lines_all prla ,mtl_txn_request_lines mtrl ,
1991 po_requisition_headers_all prha , po_line_locations_all plla
1992 WHERE plla.line_location_id=prla.line_location_id
1993 AND plla.line_location_id = mtrl.reference_id
1994 AND prha.requisition_header_id = prla.requisition_header_id
1995 AND mtrl.line_id=p_cas_mol_rec_tb(1).line_id;
1996
1997
1998 print_debug('Demand Source line_id ::'|| l_demand_source_line_id ,l_module_name,4);
1999
2000 END IF;
2001
2002 END IF ;
2003 --Testing for 14038720
2004
2005 IF (l_lpn_id IS NOT NULL )THEN
2006
2007 l_rsv_query_rec.lpn_id := l_lpn_id ;
2008
2009 END IF ;
2010
2011 IF (l_lot_number IS NOT NULL ) THEN
2012
2013 l_rsv_query_rec.lot_number := l_lot_number ;
2014
2015 END IF ;
2016
2017
2018 EXCEPTION
2019 WHEN OTHERS THEN
2020
2021 IF (l_debug = 1) THEN
2022 print_debug('Some other exception occurred in getting the reference id',l_module_name,4);
2023 print_debug('SQLCODE:'||SQLCODE||' SQLERRM:'||SQLERRM,l_module_name,4);
2024 END IF;
2025 END;
2026
2027 --{{
2028 --Deliver of non-crossdock cases. Make sure that only manual
2029 --reservations are picked up.
2030 --When processing results for non-crossdock cases. Make sure that
2031 --reservations are transfered to Inventory properly. }}
2032 l_rsv_query_rec.demand_source_line_detail := NULL;
2033 END IF;
2034
2035 IF (l_debug = 1) THEN
2036 print_debug('Calling query_reservations...',l_module_name,4);
2037 END IF;
2038
2039 l_progress := '@@@';
2040 query_reservation
2041 (p_query_input => l_rsv_query_rec
2042 ,p_sort_by_req_date => g_query_demand_ship_date_asc
2043 ,x_rsv_results => l_rsv_results_tbl
2044 ,x_return_status => l_return_status
2045 );
2046 l_progress := '@@@';
2047
2048 IF (l_return_status <> fnd_api.g_ret_sts_success) THEN
2049 l_progress := '@@@';
2050 RAISE fnd_api.g_exc_unexpected_error;
2051 END IF;
2052
2053 --14038720
2054 IF (l_lpn_id IS NOT NULL AND l_rsv_results_tbl.Count = 0) THEN
2055
2056 l_rsv_query_rec.lpn_id := NULL;
2057 l_rsv_query_rec.lot_number := NULL ;
2058
2059 IF (l_debug = 1) THEN
2060 print_debug('Calling query_reservations. if Query reservation returned no records,when queried with LPN..',l_module_name,4);
2061 END IF;
2062
2063 l_progress := '@@@';
2064 query_reservation
2065 (p_query_input => l_rsv_query_rec
2066 ,p_sort_by_req_date => g_query_demand_ship_date_asc
2067 ,x_rsv_results => l_rsv_results_tbl
2068 ,x_return_status => l_return_status
2069 );
2070
2071 IF (l_return_status <> fnd_api.g_ret_sts_success) THEN
2072 l_progress := '@@@';
2073 RAISE fnd_api.g_exc_unexpected_error;
2074 END IF;
2075
2076
2077 END IF ;
2078
2079
2080
2081
2082 l_remaining_prim_qty := p_cas_mol_rec_tb(1).primary_qty;
2083
2084 IF (l_debug = 1) THEN
2085 print_debug('l_remaining_prim_qty = ' ||l_remaining_prim_qty,l_module_name,4);
2086 print_debug('l_rsv_results_tbl.COUNT = ' ||l_rsv_results_tbl.COUNT,l_module_name,4);
2087 END IF;
2088
2089 --{{
2090 --LOOP results. Make sure the results are correct according to
2091 --the criteria entered (checking ordering also}}
2092 FOR i IN 1..l_rsv_results_tbl.COUNT LOOP
2093 EXIT WHEN l_remaining_prim_qty <= 0;
2094 IF l_rsv_results_tbl(i).orig_supply_source_line_id IS NOT NULL THEN
2095 print_debug('l_rsv_results_tbl(i).orig_supply_source_line_id is ' || l_rsv_results_tbl(i).orig_supply_source_line_id ,l_module_name,4);
2096 END IF;
2097
2098 IF (l_orig_supply_source_line_id IS NOT NULL AND l_rsv_results_tbl(i).orig_supply_source_line_id IS NOT NULL
2099 AND (l_orig_supply_source_line_id <> l_rsv_results_tbl(i).orig_supply_source_line_id) ) THEN
2100 print_debug('l_orig_supply_source_line_id' || l_orig_supply_source_line_id ,l_module_name,4);
2101 print_debug('l_rsv_results_tbl(i).orig_supply_source_line_id'|| l_rsv_results_tbl(i).orig_supply_source_line_id ,l_module_name,4);
2102 -- CONTINUE;
2103 GOTO ENDOFTHELOOP;
2104 END IF;
2105
2106 --{{
2107 --Make sure that l_remaining_prim_qty are updated properly}}
2108 IF (l_debug = 1) THEN
2109 print_debug('l_remaining_prim_qty:'||l_remaining_prim_qty||
2110 ' i:'||i||
2111 ' rsv_id:'||l_rsv_results_tbl(i).reservation_id||
2112 ' prim_qty:'||l_rsv_results_tbl(i).primary_reservation_quantity||
2113 ' rsv_qty:'||l_rsv_results_tbl(i).reservation_quantity||
2114 ' uom_code:'||l_rsv_results_tbl(i).reservation_uom_code||
2115 ' orig_supply_src_type_id:'||l_rsv_results_tbl(i).orig_supply_source_type_id||
2116 ' demand_src_line_detail:'||l_rsv_results_tbl(i).demand_source_line_detail||
2117 ' ext_src_code:'||l_rsv_results_tbl(i).external_source_code
2118 ,l_module_name,4);
2119 END IF;
2120
2121 l_rsv_update_rec := l_rsv_results_tbl(i);
2122
2123 IF (l_rsv_results_tbl(i).primary_reservation_quantity > l_remaining_prim_qty) THEN
2124 IF (l_debug = 1) THEN
2125 print_debug('l_rsv_results_tbl(i).primary_reservation_quantity > l_remaining_prim_qty',
2126 l_module_name,4);
2127 END IF;
2128
2129 l_rsv_update_rec.primary_reservation_quantity := l_remaining_prim_qty;
2130 l_rsv_update_rec.reservation_quantity := inv_rcv_cache.convert_qty
2131 (p_cas_mol_rec_tb(1).inventory_item_id
2132 ,l_remaining_prim_qty
2133 ,l_rsv_results_tbl(i).primary_uom_code
2134 ,l_rsv_results_tbl(i).reservation_uom_code);
2135
2136 IF (l_rsv_results_tbl(i).demand_source_line_detail IS NOT NULL) THEN
2137 IF (l_debug = 1) THEN
2138 print_debug('Calling split_wdd',l_module_name,4);
2139 END IF;
2140
2141 l_progress := '@@@';
2142 split_wdd
2143 (x_return_status => l_return_status
2144 ,x_msg_count => l_msg_count
2145 ,x_msg_data => l_msg_data
2146 ,x_new_wdd_id => l_new_wdd_id
2147 ,p_wdd_id => l_rsv_results_tbl(i).demand_source_line_detail
2148 ,p_new_mol_id => NULL
2149 ,p_qty_to_splt => l_remaining_prim_qty);
2150 l_progress := '@@@';
2151
2152 IF (l_debug = 1) THEN
2153 print_debug('Returned from split_wdd',l_module_name,4);
2154 print_debug('l_return_status =>'||l_return_status,l_module_name,4);
2155 END IF;
2156
2157 IF (l_return_status <> fnd_api.g_ret_sts_success) THEN
2158 l_progress := '@@@';
2159 RAISE fnd_api.g_exc_unexpected_error;
2160 END IF;
2161
2162 l_rsv_update_rec.demand_source_line_detail := l_new_wdd_id;
2163 END IF;
2164
2165 l_remaining_prim_qty := 0;
2166
2167 ELSE --l_rsv_results_tbl(i).primary_reservation_quantity <= l_remaining_prim_qty
2168 IF (l_debug = 1) THEN
2169 print_debug('l_rsv_results_tbl(i).primary_reservation_quantity <= l_remaining_prim_qty',
2170 l_module_name,4);
2171 END IF;
2172 l_remaining_prim_qty := l_remaining_prim_qty - l_rsv_results_tbl(i).primary_reservation_quantity;
2173 END IF;
2174
2175 -- Lei's complete_crossdock API will update wdd
2176
2177 l_rsv_update_rec.supply_source_type_id := g_source_type_inv;
2178 l_rsv_update_rec.supply_source_header_id := NULL;
2179 l_rsv_update_rec.supply_source_line_id := NULL;
2180 l_rsv_update_rec.supply_source_line_detail := NULL;
2181 l_rsv_update_rec.demand_source_line_detail := NULL;
2182 --8833953 added sub/loc
2183 l_rsv_update_rec.subinventory_code := p_cas_mol_rec_tb(1).subinventory_code;
2184 l_rsv_update_rec.locator_id := p_cas_mol_rec_tb(1).locator_id;
2185
2186
2187 --6/30/05: Also update lpn/sub/loc info for xdock scenario
2188 ---- IF l_rsv_results_tbl(i).demand_source_line_detail IS NOT NULL THEN --Bug 8641693
2189 l_rsv_update_rec.lpn_id := p_cas_mol_rec_tb(1).lpn_id;
2190 l_rsv_update_rec.subinventory_code := p_cas_mol_rec_tb(1).subinventory_code;
2191 l_rsv_update_rec.locator_id := p_cas_mol_rec_tb(1).locator_id;
2192 l_rsv_update_rec.lot_number := p_cas_mol_rec_tb(1).lot_number;
2193 l_rsv_update_rec.revision := p_cas_mol_rec_tb(1).item_revision;
2194 IF l_rsv_results_tbl(i).demand_source_line_detail IS NOT NULL THEN
2195 l_rsv_update_rec.staged_flag := 'Y';
2196 l_rsv_update_rec.crossdock_flag := NULL;
2197 END IF;
2198
2199 IF (l_debug = 1) THEN
2200 print_debug('Calling transfer_reservation...',l_module_name,4);
2201 END IF;
2202
2203 l_progress := '@@@';
2204 transfer_reservation
2205 (p_original_rsv_rec => l_rsv_results_tbl(i)
2206 ,p_to_rsv_rec => l_rsv_update_rec
2207 ,x_new_rsv_id => l_new_rsv_id
2208 ,x_return_status => l_return_status);
2209 l_progress := '@@@';
2210
2211 IF (l_debug = 1) THEN
2212 print_debug('Returned from transfer_reservation',l_module_name,4);
2213 print_debug('x_return_status: '||l_return_status,l_module_name,4);
2214 END IF;
2215
2216 IF (l_return_status <> fnd_api.g_ret_sts_success) THEN
2217 l_progress := '@@@';
2218 RAISE fnd_api.g_exc_unexpected_error;
2219 END IF;
2220
2221 IF (l_debug = 1) THEN
2222 print_debug('l_new_rsv_id: '||l_new_rsv_id,l_module_name,4);
2223 END IF;
2224 <<ENDOFTHELOOP>> NULL;
2225 END LOOP;
2226 END IF;--IF p_cas_mol_rec_tb(1).crossdock_type = 2 AND p_cas_mol_rec_tb(1).wip_supply_type = 1 THEN
2227
2228 IF (l_debug = 1) THEN
2229 print_debug('Exitting maintain_rsv_deliver with the following values:',l_module_name,4);
2230 print_debug('x_return_status => '||x_return_status,l_module_name,4);
2231 print_debug('x_msg_count => '||x_msg_count,l_module_name,4);
2232 print_debug('x_msg_data => '||x_msg_data,l_module_name,4);
2233 END IF;
2234
2235 --{{
2236 --********** END PROCEDURE maintain_rsv_deliver *********}}
2237 EXCEPTION
2238 WHEN OTHERS THEN
2239 IF (l_debug = 1) THEN
2240 print_debug('Exception occurred at progress: '||l_progress,l_module_name,4);
2241 print_debug('SQLCODE:'||SQLCODE||' SQLERRM:'||SQLERRM,l_module_name,4);
2242 END IF;
2243 x_return_status := fnd_api.g_ret_sts_error;
2244 END maintain_rsv_deliver;
2245
2246 PROCEDURE maintain_rsv_returns
2247 (x_return_status OUT NOCOPY VARCHAR2
2248 ,x_msg_count OUT NOCOPY NUMBER
2249 ,x_msg_data OUT NOCOPY VARCHAR2
2250 ,p_cas_mol_rec_tb IN inv_rcv_integration_pvt.cas_mol_rec_tb_tp
2251 ) IS
2252 l_rsv_query_rec inv_reservation_global.mtl_reservation_rec_type;
2253 l_rsv_update_rec inv_reservation_global.mtl_reservation_rec_type;
2254 l_remaining_prim_qty NUMBER;
2255 l_shipment_header_id NUMBER;
2256 l_requisition_header_id NUMBER;
2257 l_supply_source_type_id NUMBER;
2258 l_supply_source_header_id NUMBER;
2259 l_supply_source_line_id NUMBER;
2260 l_supply_source_line_detail NUMBER;
2261 l_avail_qty_to_reserve NUMBER;
2262 l_avail_qty NUMBER;
2263 l_available_rcv_qty NUMBER;
2264 l_qty_with_no_wdd NUMBER;
2265 l_deal_with_reservation NUMBER;
2266 l_new_rsv_id NUMBER;
2267 TYPE varchar25_tb IS TABLE OF VARCHAR2(25) INDEX BY BINARY_INTEGER;
2268 l_pt_txn_types varchar25_tb;
2269 l_inspect_status NUMBER;
2270 l_loose_qty_to_splt NUMBER;
2271 l_receipt_source_code VARCHAR2(25);
2272 l_mo_line_id NUMBER;
2273 l_mo_split_tb inv_rcv_integration_apis.mo_in_tb_tp;
2274 l_dummy NUMBER;
2275 l_qty_to_close NUMBER;
2276 l_tmp_line_id NUMBER;
2277 l_primary_qty NUMBER;
2278
2279 l_return_status VARCHAR2(1);
2280 l_error_code NUMBER;
2281 l_msg_count NUMBER;
2282 l_msg_data VARCHAR2(2000);
2283
2284 l_debug NUMBER;
2285 l_progress VARCHAR2(10);
2286 l_module_name VARCHAR2(30);
2287
2288 BEGIN
2289
2290 l_debug := NVL(fnd_profile.VALUE('INV_DEBUG_TRACE'), 0);
2291 l_progress := '10';
2292 l_module_name := 'MAINTAIN_RSV_RETURNS';
2293
2294 --For - Corr, primary_qty will come as a negative number. Take the abs
2295 --value here
2296
2297 l_primary_qty := abs(p_cas_mol_rec_tb(1).primary_qty);
2298
2299 IF (l_debug = 1) THEN
2300 print_debug('Entering maintain_rsv_returns...',l_module_name,4);
2301 print_debug(' p_cas_mol_rec_tb(1).organization_id => '||p_cas_mol_rec_tb(1).organization_id,l_module_name,4);
2302 print_debug(' p_cas_mol_rec_tb(1).inventory_item_id => '||p_cas_mol_rec_tb(1).inventory_item_id,l_module_name,4);
2303 print_debug(' p_cas_mol_rec_tb(1).lpn_id => '||p_cas_mol_rec_tb(1).lpn_id,l_module_name,4);
2304 print_debug(' p_cas_mol_rec_tb(1).from_subinventory_code => '||p_cas_mol_rec_tb(1).from_subinventory_code,l_module_name,4);
2305 print_debug(' p_cas_mol_rec_tb(1).from_locator_id => '||p_cas_mol_rec_tb(1).from_locator_id,l_module_name,4);
2306 print_debug(' p_cas_mol_rec_tb(1).item_revision => '||p_cas_mol_rec_tb(1).item_revision,l_module_name,4);
2307 print_debug(' p_cas_mol_rec_tb(1).lot_number => '||p_cas_mol_rec_tb(1).lot_number,l_module_name,4);
2308 print_debug(' p_cas_mol_rec_tb(1).primary_qty => '||l_primary_qty,l_module_name,4);
2309 print_debug(' p_cas_mol_rec_tb(1).project_id => '||p_cas_mol_rec_tb(1).project_id,l_module_name,4);
2310 print_debug(' p_cas_mol_rec_tb(1).task_id => '||p_cas_mol_rec_tb(1).task_id,l_module_name,4);
2311 print_debug(' p_cas_mol_rec_tb(1).shipment_line_id => '||p_cas_mol_rec_tb(1).shipment_line_id,l_module_name,4);
2312 print_debug(' p_cas_mol_rec_tb(1).inspection_status => '||p_cas_mol_rec_tb(1).inspection_status,l_module_name,4);
2313 print_debug(' p_cas_mol_rec_tb(1).asn_line_flag => '||p_cas_mol_rec_tb(1).asn_line_flag,l_module_name,4);
2314 END IF;
2315
2316 x_return_status := fnd_api.g_ret_sts_success;
2317
2318 --2.0 Query availability in RCV
2319 IF (l_debug = 1) THEN
2320 print_debug('Calling inv_reservation_avail_pvt.available_supply_to_reserve',l_module_name,4);
2321 END IF;
2322
2323 inv_reservation_avail_pvt.available_supply_to_reserve
2324 (x_return_status => l_return_status
2325 , x_msg_count => l_msg_count
2326 , x_msg_data => l_msg_data
2327 , p_organization_id => p_cas_mol_rec_tb(1).organization_id
2328 , p_item_id => p_cas_mol_rec_tb(1).inventory_item_id
2329 , p_revision => p_cas_mol_rec_tb(1).item_revision
2330 , p_lot_number => p_cas_mol_rec_tb(1).lot_number
2331 , p_supply_source_type_id => g_source_type_rcv
2332 , p_supply_source_header_id => NULL
2333 , p_supply_source_line_id => NULL
2334 , p_supply_source_line_detail => NULL
2335 , p_project_id => p_cas_mol_rec_tb(1).project_id
2336 , p_task_id => p_cas_mol_rec_tb(1).task_id
2337 , x_qty_available_to_reserve => l_avail_qty_to_reserve
2338 , x_qty_available => l_avail_qty
2339 );
2340
2341 IF (l_debug = 1) THEN
2342 print_debug('After calling inv_reservation_avail_pvt.available_supply_to_reserve',l_module_name,4);
2343 print_debug('l_avail_qty_to_reserve: ' || l_avail_qty_to_reserve,l_module_name,4);
2344 END IF;
2345
2346 l_available_rcv_qty := l_avail_qty_to_reserve + l_primary_qty;
2347
2348 IF (l_debug = 1) THEN
2349 print_debug('l_available_rcv_qty: ' || l_available_rcv_qty,l_module_name,4);
2350 END IF;
2351
2352 --3.0 Determine whether there is a need to deal with reservations
2353 IF l_available_rcv_qty > l_primary_qty THEN
2354
2355 --3.1 If there is enough total available quantity, we still need
2356 -- to check if the LPN has available MOL quantity that has no wdd stamped
2357 BEGIN
2358 SELECT SUM(primary_quantity)
2359 INTO l_qty_with_no_wdd
2360 FROM mtl_txn_request_lines mtrl
2361 WHERE nvl(mtrl.lpn_id,-999)=nvl(p_cas_mol_rec_tb(1).lpn_id,-999)
2362 AND nvl(mtrl.from_subinventory_code,'&&&')=nvl(p_cas_mol_rec_tb(1).from_subinventory_code,'&&&')--???
2363 AND nvl(mtrl.from_locator_id,-999)=nvl(p_cas_mol_rec_tb(1).from_locator_id,-999)--???
2364 AND mtrl.organization_id = p_cas_mol_rec_tb(1).organization_id
2365 AND mtrl.inventory_item_id = p_cas_mol_rec_tb(1).inventory_item_id
2366 AND Nvl(mtrl.lot_number,'*&*') = Nvl(p_cas_mol_rec_tb(1).lot_number,'*&*')
2367 AND nvl(mtrl.revision,'&&&') = nvl(p_cas_mol_rec_tb(1).item_revision,'&&&')
2368 AND mtrl.line_status = 7
2369 AND Nvl(mtrl.inspection_status,-1) = Nvl(p_cas_mol_rec_tb(1).inspection_status,-1)
2370 AND (NVL(mtrl.project_id, -999) = NVL(p_cas_mol_rec_tb(1).project_id, -999)
2371 or p_cas_mol_rec_tb(1).lpn_id is null) -- Bug 6618890 --Bug#8627996
2372 AND (NVL(mtrl.task_id, -999) = NVL(p_cas_mol_rec_tb(1).task_id, -999)
2373 or p_cas_mol_rec_tb(1).lpn_id is null) -- Bug 6618890 --Bug#8627996
2374 AND (mtrl.quantity - Nvl(mtrl.quantity_delivered,0)) > 0
2375 AND mtrl.backorder_delivery_detail_id IS NULL
2376 AND exists (SELECT 1
2377 FROM mtl_txn_request_headers mtrh
2378 WHERE mtrh.move_order_type = inv_globals.g_move_order_put_away
2379 AND mtrh.header_id = mtrl.header_id);
2380 EXCEPTION
2381 WHEN OTHERS THEN
2382 RAISE fnd_api.g_exc_unexpected_error;
2383 END;
2384
2385 IF (l_debug = 1) THEN
2386 print_debug('l_qty_qith_no_wdd : ' || l_qty_with_no_wdd ,l_module_name,4);
2387 END IF;
2388
2389 IF (l_qty_with_no_wdd >= l_primary_qty) THEN
2390 --3.1.1 Can simply split MOL. No need to deal with reservations
2391 l_deal_with_reservation := 0;
2392 ELSE
2393 --3.1.2 Must deal with wdd. So need to deal with reservations
2394 l_deal_with_reservation := 1;
2395 END IF;
2396 ELSE
2397 --3.2 total available qty is less the txn qty. So must deal with reservations
2398 l_deal_with_reservation := 1;
2399 END IF;--END IF l_available_rcv_qty > l_primary_qty THEN
2400
2401 IF (l_debug = 1) THEN
2402 print_debug('L_DEAL_WITH_RESERVATION := ' || l_deal_with_reservation ,l_module_name,4);
2403 END IF;
2404
2405 l_remaining_prim_qty := 0;
2406 l_loose_qty_to_splt := 0;
2407
2408 IF (l_deal_with_reservation = 0) THEN
2409 --4.1 Jump directly to split mo
2410 l_loose_qty_to_splt := l_primary_qty;
2411 IF (l_debug = 1) THEN
2412 print_debug('LOOSE QTY TO SPLI = ' || l_loose_qty_to_splt ,l_module_name,4);
2413 END IF;
2414 ELSE -- l_deal_with_reservation = 1
2415
2416 --4.2.2 Query reservations
2417 l_remaining_prim_qty := l_primary_qty;
2418 l_loose_qty_to_splt := 0;
2419
2420 IF (l_debug = 1) THEN
2421 print_debug('Before Opening the cusror on l_rsv_results ' ,l_module_name,4);
2422 END IF;
2423
2424 FOR l_rsv_results IN (SELECT
2425 reservation_id
2426 , primary_uom_code
2427 , primary_reservation_quantity
2428 , reservation_uom_code
2429 , demand_source_line_detail
2430 FROM mtl_reservations mr
2431 WHERE mr.supply_source_type_id = inv_reservation_global.g_source_type_rcv
2432 AND mr.organization_id = p_cas_mol_rec_tb(1).organization_id
2433 AND mr.inventory_item_id = p_cas_mol_rec_tb(1).inventory_item_id
2434 AND ((mr.demand_source_line_detail IS NOT NULL
2435 AND mr.demand_source_line_detail
2436 IN (SELECT mol.backorder_delivery_detail_id
2437 FROM mtl_txn_request_lines mol
2438 WHERE mol.organization_id = p_cas_mol_rec_tb(1).organization_id
2439 AND mol.inventory_item_id = p_cas_mol_rec_tb(1).inventory_item_id
2440 AND NVL(mol.revision, '@@@') = NVL(p_cas_mol_rec_tb(1).item_revision, '@@@')
2441 AND (NVL(mol.project_id, -999) = NVL(p_cas_mol_rec_tb(1).project_id, -999)
2442 or p_cas_mol_rec_tb(1).lpn_id is null) -- Bug 6618890 --Bug#8627996
2443 AND (NVL(mol.task_id, -999) = NVL(p_cas_mol_rec_tb(1).task_id, -999)
2444 or p_cas_mol_rec_tb(1).lpn_id is null) -- Bug 6618890 --Bug#8627996
2445 AND MOL.CROSSDOCK_TYPE = 1 --RESERVATION COULD BE FOR WIP ALSO???
2446 AND NVL(mol.lpn_id, -999) = NVL(p_cas_mol_rec_tb(1).lpn_id, -999)
2447 AND nvl(mol.inspection_status,-1) = Nvl(p_cas_mol_rec_tb(1).inspection_status,-1)
2448 AND Nvl(mol.lot_number,'&^+') = Nvl(p_cas_mol_rec_tb(1).lot_number,'&^+')
2449 AND mol.line_status = 7
2450 AND (mol.quantity-Nvl(mol.quantity_delivered,0))>0
2451 AND exists (SELECT 1
2452 FROM mtl_txn_request_headers mtrh
2453 WHERE mtrh.move_order_type = inv_globals.g_move_order_put_away
2454 AND mtrh.header_id = mol.header_id)
2455 )) OR
2456 (mr.demand_source_line_detail IS NULL
2457 AND exists (SELECT mol.backorder_delivery_detail_id
2458 FROM mtl_txn_request_lines mol
2459 WHERE mol.organization_id = p_cas_mol_rec_tb(1).organization_id
2460 AND mol.inventory_item_id = p_cas_mol_rec_tb(1).inventory_item_id
2461 AND NVL(mol.revision, '@@@') = NVL(p_cas_mol_rec_tb(1).item_revision, '@@@')
2462 AND (NVL(mol.project_id, -999) = NVL(p_cas_mol_rec_tb(1).project_id, -999)
2463 or p_cas_mol_rec_tb(1).lpn_id is null) -- Bug 6618890 --Bug#8627996
2464 AND (NVL(mol.task_id, -999) = NVL(p_cas_mol_rec_tb(1).task_id, -999)
2465 or p_cas_mol_rec_tb(1).lpn_id is null) -- Bug 6618890 --Bug#8627996
2466 AND NVL(mol.lpn_id, -999) = NVL(p_cas_mol_rec_tb(1).lpn_id, -999)
2467 AND mol.backorder_delivery_detail_id IS NULL
2468 AND nvl(mol.inspection_status,-1) = Nvl(p_cas_mol_rec_tb(1).inspection_status,-1)
2469 AND Nvl(mol.lot_number,'&#+') = Nvl(p_cas_mol_rec_tb(1).lot_number,'&#+')
2470 AND mol.line_status = 7
2471 AND (mol.quantity-Nvl(mol.quantity_delivered,0))>0
2472 AND exists (SELECT 1
2473 FROM mtl_txn_request_headers mtrh
2474 WHERE mtrh.move_order_type = inv_globals.g_move_order_put_away
2475 AND mtrh.header_id = mol.header_id)
2476 )
2477 )
2478 )
2479 ORDER BY NVL(MR.DEMAND_SHIP_DATE, REQUIREMENT_DATE)) LOOP
2480 EXIT WHEN l_remaining_prim_qty <= 0;
2481
2482 IF (l_debug = 1) THEN
2483 print_debug('Looping through l_rsv_results ' ,l_module_name,4);
2484 END IF;
2485
2486 IF l_rsv_results.demand_source_line_detail IS NOT NULL THEN
2487
2488 IF (l_debug = 1) THEN
2489 print_debug('l_rsv_results.demand_source_line_detail := '|| l_rsv_results.demand_source_line_detail ,l_module_name,4);
2490 END IF;
2491
2492 BEGIN
2493 SELECT line_id
2494 INTO l_mo_line_id
2495 FROM mtl_txn_request_lines mol
2496 WHERE backorder_delivery_detail_id = l_rsv_results.demand_source_line_detail
2497 AND mol.organization_id = p_cas_mol_rec_tb(1).organization_id
2498 AND mol.inventory_item_id = p_cas_mol_rec_tb(1).inventory_item_id
2499 AND NVL(mol.revision, '@@@') = NVL(p_cas_mol_rec_tb(1).item_revision, '@@@')
2500 AND (NVL(mol.project_id, -999) = NVL(p_cas_mol_rec_tb(1).project_id , -999)
2501 or p_cas_mol_rec_tb(1).lpn_id is null) -- Bug 6618890 --Bug#8627996
2502 AND (NVL(mol.task_id, -999) = NVL(p_cas_mol_rec_tb(1).task_id, -999)
2503 or p_cas_mol_rec_tb(1).lpn_id is null) -- Bug 6618890 --Bug#8627996
2504 AND mol.crossdock_type = 1
2505 AND NVL(mol.lpn_id, -999) = NVL(p_cas_mol_rec_tb(1).lpn_id, -999)
2506 AND exists (SELECT 1
2507 FROM mtl_txn_request_headers mtrh
2508 WHERE mtrh.move_order_type = inv_globals.g_move_order_put_away
2509 AND mtrh.header_id = mol.header_id);
2510 EXCEPTION
2511 WHEN OTHERS THEN
2512 IF (l_debug = 1) THEN
2513 print_debug('Some other exception occurred!',l_module_name,4);
2514 print_debug('SQLCODE:'||SQLCODE||' SQLERRM:'||SQLERRM,l_module_name,4);
2515 END IF;
2516 RAISE fnd_api.g_exc_unexpected_error;
2517 END;
2518
2519 IF (l_rsv_results.primary_reservation_quantity > l_remaining_prim_qty) THEN
2520 IF (l_debug = 1) THEN
2521 print_debug('l_rsv_results.primary_reservation_quantity > l_remaining_prim_qty',
2522 l_module_name,4);
2523 print_debug('Calling split_mo...',l_module_name,4);
2524 END IF;
2525
2526 l_mo_split_tb(1).prim_qty := l_remaining_prim_qty;
2527 l_mo_split_tb(1).line_id := NULL;
2528
2529 inv_rcv_integration_apis.split_mo
2530 (p_orig_mol_id => l_mo_line_id,
2531 p_mo_splt_tb => l_mo_split_tb,
2532 x_return_status => l_return_status,
2533 x_msg_count => l_msg_count,
2534 x_msg_data => l_msg_data);
2535
2536 IF (l_debug = 1) THEN
2537 print_debug('Returned from split_mo',l_module_name,4);
2538 print_debug('x_return_status: '||l_return_status,l_module_name,4);
2539 END IF;
2540
2541 IF (l_return_status <> fnd_api.g_ret_sts_success) THEN
2542 IF (l_debug = 1) THEN
2543 print_debug('SQLCODE:'||SQLCODE||' SQLERRM:'||SQLERRM,l_module_name,4);
2544 print_debug('Raising Exception!!!',l_module_name,4);
2545 END IF;
2546 l_progress := '@@@';
2547 RAISE fnd_api.g_exc_unexpected_error;
2548 END IF;
2549
2550 l_tmp_line_id := l_mo_split_tb(1).line_id;
2551
2552 /* Lei's cancel operation will take care of this
2553 l_rsv_query_rec.reservation_id := l_mo_split_tb(1).reservation_id;
2554 l_rsv_query_rec.demand_source_line_detail := l_mo_split_tb(1).wdd_id;
2555
2556 IF (l_debug = 1) THEN
2557 print_debug('Calling relieve_reservation...',l_module_name,4);
2558 END IF;
2559
2560 relieve_reservation
2561 (p_rsv_rec => l_rsv_query_rec
2562 ,p_prim_qty_to_relieve => l_remaining_prim_qty
2563 ,x_return_status => l_return_status
2564 );
2565
2566 IF (l_debug = 1) THEN
2567 print_debug('Returned from relieve_reservation',l_module_name,4);
2568 print_debug('x_return_status: '||l_return_status,l_module_name,4);
2569 END IF;
2570
2571 IF (l_return_status <> fnd_api.g_ret_sts_success) THEN
2572 IF (l_debug = 1) THEN
2573 print_debug('SQLCODE:'||SQLCODE||' SQLERRM:'||SQLERRM,l_module_name,4);
2574 print_debug('Raising Exception!!!',l_module_name,4);
2575 END IF;
2576 l_progress := '@@@';
2577 RAISE fnd_api.g_exc_unexpected_error;
2578 END IF;
2579
2580 IF (l_debug = 1) THEN
2581 print_debug('Calling update_wdd...',l_module_name,4);
2582 END IF;
2583
2584 update_wdd
2585 (x_return_status => l_return_status
2586 ,x_msg_count => l_msg_count
2587 ,x_msg_data => l_msg_data
2588 ,p_wdd_id => l_mo_split_tb(1).wdd_id
2589 ,p_released_status => 'R'
2590 ,p_mol_id => NULL
2591 );
2592
2593 IF (l_debug = 1) THEN
2594 print_debug('Returned from update_wdd',l_module_name,4);
2595 print_debug('x_return_status: '||l_return_status,l_module_name,4);
2596 END IF;
2597
2598 IF (l_return_status <> fnd_api.g_ret_sts_success) THEN
2599 IF (l_debug = 1) THEN
2600 print_debug('x_msg_count: '||l_msg_count,l_module_name,4);
2601 print_debug('x_msg_data: '||l_msg_data,l_module_name,4);
2602 print_debug('SQLCODE:'||SQLCODE||' SQLERRM:'||SQLERRM,l_module_name,4);
2603 print_debug('Raising Exception!!!',l_module_name,4);
2604 END IF;
2605 l_progress := '@@@';
2606 RAISE fnd_api.g_exc_unexpected_error;
2607 END IF;
2608 */
2609
2610
2611 l_remaining_prim_qty := 0;
2612
2613 ELSE
2614 /* Lei's cancel operation will take care of this
2615 l_rsv_query_rec.reservation_id := l_rsv_results.reservation_id;
2616
2617 IF (l_debug = 1) THEN
2618 print_debug('l_rsv_results(i).primary_reservation_quantity <= l_remaining_prim_qty',
2619 l_module_name,4);
2620 print_debug('Calling delete_reservation...',l_module_name,4);
2621 END IF;
2622
2623 delete_reservation
2624 (p_rsv_rec => l_rsv_query_rec
2625 ,x_return_status => l_return_status
2626 );
2627
2628 IF (l_debug = 1) THEN
2629 print_debug('Returned from delete_reservation',l_module_name,4);
2630 print_debug('x_return_status: '||l_return_status,l_module_name,4);
2631 END IF;
2632
2633 IF (l_return_status <> fnd_api.g_ret_sts_success) THEN
2634 IF (l_debug = 1) THEN
2635 print_debug('SQLCODE:'||SQLCODE||' SQLERRM:'||SQLERRM,l_module_name,4);
2636 print_debug('Raising Exception!!!',l_module_name,4);
2637 END IF;
2638 l_progress := '@@@';
2639 RAISE fnd_api.g_exc_unexpected_error;
2640 END IF;
2641
2642 IF (l_debug = 1) THEN
2643 print_debug('Calling update_wdd...',l_module_name,4);
2644 END IF;
2645
2646 update_wdd
2647 (x_return_status => l_return_status
2648 ,x_msg_count => l_msg_count
2649 ,x_msg_data => l_msg_data
2650 ,p_wdd_id => l_rsv_results.demand_source_line_detail
2651 ,p_released_status => 'R'
2652 ,p_mol_id => NULL
2653 );
2654
2655 IF (l_debug = 1) THEN
2656 print_debug('Returned from update_wdd',l_module_name,4);
2657 print_debug('x_return_status: '||l_return_status,l_module_name,4);
2658 END IF;
2659
2660 IF (l_return_status <> fnd_api.g_ret_sts_success) THEN
2661 IF (l_debug = 1) THEN
2662 print_debug('x_msg_count: '||l_msg_count,l_module_name,4);
2663 print_debug('x_msg_data: '||l_msg_data,l_module_name,4);
2664 print_debug('SQLCODE:'||SQLCODE||' SQLERRM:'||SQLERRM,l_module_name,4);
2665 print_debug('Raising Exception!!!',l_module_name,4);
2666 END IF;
2667 l_progress := '@@@';
2668 RAISE fnd_api.g_exc_unexpected_error;
2669 END IF;
2670
2671 */
2672 l_tmp_line_id := l_mo_line_id;
2673 l_remaining_prim_qty := l_remaining_prim_qty - l_rsv_results.primary_reservation_quantity;
2674 END IF;
2675
2676 UPDATE mtl_txn_request_lines
2677 SET quantity = Nvl(quantity_delivered,0)
2678 , primary_quantity = ((primary_quantity*Nvl(quantity_delivered,0))/quantity)
2679 , quantity_detailed = Decode(quantity_detailed,NULL,quantity_detailed,quantity_delivered)
2680 -- OPMConvergence
2681 , secondary_quantity = Nvl(secondary_quantity_delivered,0)
2682 , secondary_quantity_detailed = Decode(secondary_quantity_detailed,NULL,secondary_quantity_detailed,secondary_quantity_delivered)
2683 -- OPMConvergence
2684 , line_status = 5
2685 , wms_process_flag = 1
2686 WHERE line_id = l_tmp_line_id;
2687
2688 inv_rcv_integration_pvt.call_atf_api(x_return_status => l_return_status,
2689 x_msg_data => l_msg_data,
2690 x_msg_count => l_msg_count,
2691 x_error_code => l_error_code,
2692 p_source_task_id => NULL,
2693 p_activity_type_id => 1,
2694 p_operation_type_id => NULL,
2695 p_mol_id => l_tmp_line_id,
2696 p_atf_api_name => inv_rcv_integration_pvt.g_atf_api_cancel);
2697
2698
2699 ELSE --l_rsv_result_tbl(i).demand_source_line_detail IS NULL THEN
2700
2701 IF (l_debug = 1) THEN
2702 print_debug('l_rsv_results.demand_source_line_detail is null ',l_module_name,4);
2703 END IF;
2704
2705 IF (l_rsv_results.primary_reservation_quantity > l_remaining_prim_qty) THEN
2706 l_loose_qty_to_splt := l_loose_qty_to_splt + l_remaining_prim_qty;
2707 l_rsv_update_rec.primary_reservation_quantity := l_remaining_prim_qty;
2708 l_rsv_update_rec.reservation_quantity := inv_rcv_cache.convert_qty(p_cas_mol_rec_tb(1).inventory_item_id
2709 ,l_remaining_prim_qty
2710 ,l_rsv_results.primary_uom_code
2711 ,l_rsv_results.reservation_uom_code);
2712 l_remaining_prim_qty := 0;
2713 ELSE
2714 l_remaining_prim_qty := l_remaining_prim_qty - l_rsv_results.primary_reservation_quantity;
2715 l_loose_qty_to_splt := l_loose_qty_to_splt - l_rsv_results.primary_reservation_quantity;
2716 --close entire line
2717 END IF;
2718
2719 BEGIN
2720 SELECT rsh.receipt_source_code
2721 INTO l_receipt_source_code
2722 FROM rcv_shipment_headers rsh, rcv_shipment_lines rsl
2723 WHERE rsl.shipment_line_id = p_cas_mol_rec_tb(1).shipment_line_id
2724 AND rsl.shipment_header_id = rsh.shipment_header_id;
2725 EXCEPTION
2726 WHEN no_data_found THEN
2727 IF (l_debug = 1) THEN
2728 print_debug(' NO_DATA_FOUND Exception thrown when retrieving receipt_source_cod!',l_module_name,4);
2729 RAISE fnd_api.g_exc_unexpected_error;
2730 END IF;
2731 WHEN OTHERS THEN
2732 IF (l_debug = 1) THEN
2733 print_debug(' Other exceptions thrown when retrieving receipt_source_cod!',l_module_name,4);
2734 END IF;
2735 RAISE fnd_api.g_exc_unexpected_error;
2736 END;
2737
2738 IF (l_debug = 1) THEN
2739 print_debug('l_remaining_prim_qty= '|| l_remaining_prim_qty,l_module_name,4);
2740 print_debug('l_loose_qty_to_splt = '|| l_loose_qty_to_splt ,l_module_name,4);
2741 print_debug('l_rsv_update_rec.primary_reservation_quantity = '||l_rsv_update_rec.primary_reservation_quantity ,l_module_name,4);
2742 print_debug('l_rsv_update_rec.reservation_quantity = '||l_rsv_update_rec.reservation_quantity ,l_module_name,4);
2743 print_debug('l_receipt_source_code = '||l_receipt_source_code,l_module_name,4);
2744 END IF;
2745
2746 IF (l_receipt_source_code = 'VENDOR' AND p_cas_mol_rec_tb(1).asn_line_flag = 'Y') THEN
2747 l_rsv_update_rec.supply_source_type_id := g_source_type_asn;
2748 l_rsv_update_rec.supply_source_header_id := p_cas_mol_rec_tb(1).po_header_id;
2749 l_rsv_update_rec.supply_source_line_id := p_cas_mol_rec_tb(1).po_line_location_id;
2750 l_rsv_update_rec.supply_source_line_detail := p_cas_mol_rec_tb(1).shipment_line_id;
2751 ELSIF (l_receipt_source_code = 'VENDOR' AND p_cas_mol_rec_tb(1).asn_line_flag = 'N') THEN
2752 l_rsv_update_rec.supply_source_type_id := inv_reservation_global.g_source_type_po;
2753 l_rsv_update_rec.supply_source_header_id := p_cas_mol_rec_tb(1).po_header_id;
2754 l_rsv_update_rec.supply_source_line_id := p_cas_mol_rec_tb(1).po_line_location_id;
2755 l_rsv_update_rec.supply_source_line_detail := NULL;
2756 ELSIF (l_receipt_source_code = 'INTERNAL ORDER') THEN
2757 l_rsv_update_rec.supply_source_type_id :=
2758 inv_reservation_global.g_source_type_internal_req;
2759 l_rsv_update_rec.supply_source_header_id := l_requisition_header_id;
2760 l_rsv_update_rec.supply_source_line_id := p_cas_mol_rec_tb(1).requisition_line_id;
2761 l_rsv_update_rec.supply_source_line_detail := NULL;
2762 ELSIF (l_receipt_source_code = 'INVENTORY') THEN
2763 BEGIN
2764 SELECT shipment_header_id
2765 INTO l_shipment_header_id
2766 FROM rcv_shipment_lines
2767 WHERE shipment_line_id = p_cas_mol_rec_tb(1).shipment_line_id
2768 FOR UPDATE NOWAIT;
2769 EXCEPTION
2770 WHEN OTHERS THEN
2771 IF (l_debug = 1) THEN
2772 print_debug('Some other exception occurred in getting shipment details',l_module_name,4);
2773 print_debug('SQLCODE:'||SQLCODE||' SQLERRM:'||SQLERRM,l_module_name,4);
2774 END IF;
2775 RAISE fnd_api.g_exc_unexpected_error;
2776 END;
2777 l_rsv_update_rec.supply_source_type_id := g_source_type_in_transit;
2778 l_rsv_update_rec.supply_source_header_id := l_shipment_header_id;
2779 l_rsv_update_rec.supply_source_line_id := l_shipment_header_id;
2780 l_rsv_update_rec.supply_source_line_detail := NULL;
2781 ELSIF (l_receipt_source_code = 'CUSTOMER') THEN
2782 IF (l_debug = 1) THEN
2783 print_debug('RMA lines. Should not reach here!',l_module_name,4);
2784 END IF;
2785 RAISE fnd_api.g_exc_unexpected_error;
2786 END IF;
2787
2788 IF (l_debug = 1) THEN
2789 print_debug('Calling transfer_reservation...',l_module_name,4);
2790 END IF;
2791
2792 l_rsv_query_rec.reservation_id := l_rsv_results.reservation_id;
2793 l_rsv_query_rec.organization_id := p_cas_mol_rec_tb(1).organization_id;
2794
2795 transfer_reservation
2796 (p_original_rsv_rec => l_rsv_query_rec
2797 ,p_to_rsv_rec => l_rsv_update_rec
2798 ,x_new_rsv_id => l_new_rsv_id
2799 ,x_return_status => l_return_status);
2800
2801 IF (l_debug = 1) THEN
2802 print_debug('Returned from transfer_reservation',l_module_name,4);
2803 print_debug('x_return_status: '||l_return_status,l_module_name,4);
2804 END IF;
2805
2806 IF (l_return_status <> fnd_api.g_ret_sts_success) THEN
2807 IF (l_debug = 1) THEN
2808 print_debug('x_msg_data: '||l_msg_data,l_module_name,4);
2809 print_debug('x_msg_count: '||l_msg_count,l_module_name,4);
2810 print_debug('SQLCODE:'||SQLCODE||' SQLERRM:'||SQLERRM,l_module_name,4);
2811 print_debug('Raising Exception!!!',l_module_name,4);
2812 END IF;
2813 l_progress := '@@@';
2814 RAISE fnd_api.g_exc_unexpected_error;
2815 END IF;
2816
2817 IF (l_debug = 1) THEN
2818 print_debug('l_new_rsv_id: '||l_new_rsv_id,l_module_name,4);
2819 END IF;
2820 END IF;--IF l_rsv_result_tbl(i).demand_source_line_detail IS NOT NULL THEN
2821 END LOOP;--FOR i IN 1..l_rsv_results.COUNT LOOP
2822 END IF;--IF (l_deal_with_reservation = 0) THEN
2823
2824 IF (l_debug = 1) THEN
2825 print_debug('l_loose_qty_to_splt:'||l_loose_qty_to_splt||' l_remaining_prim_qty:'||l_remaining_prim_qty,l_module_name,4);
2826 END IF;
2827
2828 IF l_loose_qty_to_splt + l_remaining_prim_qty > 0 THEN
2829
2830 IF (l_debug = 1) THEN
2831 print_debug('Before getting l_qty_to_close' ,l_module_name,4);
2832 END IF;
2833
2834 l_qty_to_close := l_loose_qty_to_splt + l_remaining_prim_qty;
2835
2836 IF (l_debug = 1) THEN
2837 print_debug('l_qty_to_close = ' || l_qty_to_close ,l_module_name,4);
2838 print_debug('Before Opening MOL cursor' ,l_module_name,4);
2839 print_debug('p_cas_mol_rec_tb(1).organization_id = '|| p_cas_mol_rec_tb(1).organization_id ,l_module_name,4);
2840 print_debug('p_cas_mol_rec_tb(1).lpn_id = '|| p_cas_mol_rec_tb(1).lpn_id ,l_module_name,4);
2841 print_debug('p_cas_mol_rec_tb(1).inventory_item_id = '|| p_cas_mol_rec_tb(1).inventory_item_id ,l_module_name,4);
2842 print_debug('p_cas_mol_rec_tb(1).item_revision = '|| p_cas_mol_rec_tb(1).item_revision ,l_module_name,4);
2843 print_debug('p_cas_mol_rec_tb(1).inspection_status = '|| p_cas_mol_rec_tb(1).inspection_status ,l_module_name,4);
2844 print_debug('p_cas_mol_rec_tb(1).project_id = '|| p_cas_mol_rec_tb(1).project_id ,l_module_name,4);
2845 print_debug('p_cas_mol_rec_tb(1).task_id = '|| p_cas_mol_rec_tb(1).task_id ,l_module_name,4);
2846 print_debug('p_cas_mol_rec_tb(1).lot_number = '|| p_cas_mol_rec_tb(1).lot_number ,l_module_name,4);
2847 END IF;
2848
2849 FOR l_mol_rec IN (SELECT mtrl.line_id
2850 , mtrl.primary_quantity
2851 FROM mtl_txn_request_lines mtrl
2852 WHERE mtrl.line_status = 7
2853 AND (mtrl.quantity-Nvl(mtrl.quantity_delivered,0)) > 0
2854 -- AND mtrl.backorder_delivery_detail_id IS NULL --Bug#6040524
2855 AND mtrl.organization_id = p_cas_mol_rec_tb(1).organization_id
2856 -- Bug 4508608
2857 -- hadling of non lpn cases are done properly
2858 -- AND mtrl.lpn_id = p_cas_mol_rec_tb(1).lpn_id
2859 AND nvl(mtrl.lpn_id, -999) = nvl(p_cas_mol_rec_tb(1).lpn_id, -999)
2860 AND mtrl.inventory_item_id = p_cas_mol_rec_tb(1).inventory_item_id
2861 AND Nvl(mtrl.revision,'%^$') = Nvl(p_cas_mol_rec_tb(1).item_revision,'%^$')
2862 AND Nvl(mtrl.inspection_status,-1) = Nvl(p_cas_mol_rec_tb(1).inspection_status,-1)
2863 AND (NVL(mtrl.project_id, -999) = NVL(p_cas_mol_rec_tb(1).project_id, -999)
2864 OR p_cas_mol_rec_tb(1).lpn_id IS NULL) -- Bug 6618890 --Bug#8627996
2865 AND (NVL(mtrl.task_id, -999) = NVL(p_cas_mol_rec_tb(1).task_id, -999)
2866 OR p_cas_mol_rec_tb(1).lpn_id IS NULL) -- Bug 6618890 --Bug#8627996
2867 AND Nvl(mtrl.lot_number,'&*_') = Nvl(p_cas_mol_rec_tb(1).lot_number,'&*_')
2868 AND exists (SELECT 1
2869 FROM mtl_txn_request_headers mtrh
2870 WHERE mtrh.move_order_type = inv_globals.g_move_order_put_away
2871 AND mtrh.header_id = mtrl.header_id)
2872 )
2873 LOOP
2874 IF (l_debug = 1) THEN
2875 print_debug('MOL found: '|| l_mol_rec.line_id||' QTY: '||l_mol_rec.primary_quantity,l_module_name,4);
2876 END IF;
2877
2878 IF l_qty_to_close < l_mol_rec.primary_quantity THEN
2879 IF (l_debug = 1) THEN
2880 print_debug('Calling split_mo...',l_module_name,4);
2881 END IF;
2882
2883 l_mo_split_tb(1).prim_qty := l_qty_to_close;
2884 l_mo_split_tb(1).line_id := NULL;
2885
2886 inv_rcv_integration_apis.split_mo
2887 (p_orig_mol_id => l_mol_rec.line_id,
2888 p_mo_splt_tb => l_mo_split_tb,
2889 x_return_status => l_return_status,
2890 x_msg_count => l_msg_count,
2891 x_msg_data => l_msg_data);
2892
2893 IF (l_debug = 1) THEN
2894 print_debug('Returned from split_mo',l_module_name,4);
2895 print_debug('x_return_status: '||l_return_status,l_module_name,4);
2896 END IF;
2897
2898 IF (l_return_status <> fnd_api.g_ret_sts_success) THEN
2899 IF (l_debug = 1) THEN
2900 print_debug('SQLCODE:'||SQLCODE||' SQLERRM:'||SQLERRM,l_module_name,4);
2901 print_debug('Raising Exception!!!',l_module_name,4);
2902 END IF;
2903 l_progress := '@@@';
2904 RAISE fnd_api.g_exc_unexpected_error;
2905 END IF;
2906
2907 l_tmp_line_id := l_mo_split_tb(1).line_id;
2908 l_qty_to_close := l_qty_to_close - l_mol_rec.primary_quantity;
2909 ELSE
2910 l_tmp_line_id := l_mol_rec.line_id;
2911 l_qty_to_close := 0;
2912 END IF;
2913
2914 IF (l_debug = 1) THEN
2915 print_debug('Call cancel ATF',l_module_name,9);
2916 END IF;
2917
2918 inv_rcv_integration_pvt.call_atf_api(x_return_status => l_return_status,
2919 x_msg_data => l_msg_data,
2920 x_msg_count => l_msg_count,
2921 x_error_code => l_error_code,
2922 p_source_task_id => NULL,
2923 p_activity_type_id => 1,
2924 p_operation_type_id => NULL,
2925 p_mol_id => l_tmp_line_id,
2926 p_atf_api_name => inv_rcv_integration_pvt.g_atf_api_cancel);
2927
2928 IF (l_debug = 1) THEN
2929 print_debug('Closing MOL '||l_tmp_line_id,l_module_name,9);
2930 END IF;
2931
2932 UPDATE mtl_txn_request_lines
2933 SET quantity = Nvl(quantity_delivered,0)
2934 , primary_quantity = ((primary_quantity*Nvl(quantity_delivered,0))/quantity)
2935 , quantity_detailed = Decode(quantity_detailed,NULL,quantity_detailed,quantity_delivered)
2936 -- OPMConvergence
2937 , secondary_quantity = Nvl(secondary_quantity_delivered,0)
2938 , secondary_quantity_detailed = Decode(secondary_quantity_detailed,NULL,secondary_quantity_detailed,secondary_quantity_delivered)
2939 -- OPMConvergence
2940 , line_status = 5
2941 , wms_process_flag = 1
2942 WHERE line_id = l_tmp_line_id;
2943
2944 IF (l_qty_to_close <= 0) THEN
2945 IF (l_debug = 1) THEN
2946 print_debug('Exiting from the MOL Loop' ,l_module_name,9);
2947 END IF;
2948 EXIT;
2949 END if;
2950 END LOOP;
2951
2952 IF (l_qty_to_close > 0) THEN
2953 IF (l_debug = 1) THEN
2954 print_debug('l_qty_to_close > 0. Could not find matching move order for the qty !',l_module_name,4);
2955 END IF;
2956 RAISE fnd_api.g_exc_unexpected_error;
2957 END IF;
2958 END IF;
2959
2960 IF (l_debug = 1) THEN
2961 print_debug('Exitting maintain_rsv_returns with the following values:',l_module_name,4);
2962 print_debug('x_return_status => '||x_return_status,l_module_name,4);
2963 print_debug('x_msg_count => '||x_msg_count,l_module_name,4);
2964 print_debug('x_msg_data => '||x_msg_data,l_module_name,4);
2965 END IF;
2966
2967 EXCEPTION
2968 WHEN OTHERS THEN
2969 IF (l_debug = 1) THEN
2970 print_debug('Exception occurred at progress: '||l_progress,l_module_name,4);
2971 print_debug('SQLCODE:'||SQLCODE||' SQLERRM:'||SQLERRM,l_module_name,4);
2972 END IF;
2973 x_return_status := fnd_api.g_ret_sts_error;
2974 END maintain_rsv_returns;
2975
2976 PROCEDURE split_close_mo_for_ret_corr
2977 (x_return_status OUT NOCOPY VARCHAR2
2978 ,x_msg_count OUT NOCOPY NUMBER
2979 ,x_msg_data OUT NOCOPY VARCHAR2
2980 ,p_cas_mol_tb IN inv_rcv_integration_pvt.cas_mol_rec_tb_tp
2981 ) IS
2982 CURSOR c_mol_no_mmtt (p_org_id NUMBER,
2983 p_item NUMBER,
2984 p_lpn NUMBER,
2985 p_lot VARCHAR2,
2986 p_rev VARCHAR2,
2987 p_from_sub varchar2,
2988 p_from_locator_id NUMBER,
2989 -- p_cost_group_id NUMBER, ????
2990 p_project_id NUMBER,
2991 p_task_id NUMBER,
2992 p_inspection_status NUMBER ,
2993 p_uom_code varchar2
2994 ) IS
2995 --Bug 5231114:Added the condition on transaction_source_type_id and
2996 -- transaction_action_id for the following combinations:13/12 and 4/27
2997 SELECT DISTINCT mtrl.line_id
2998 , Decode(p_uom_code,mtrl.uom_code,1,2) UOM_ORDERING
2999 , Decode(mmtt.transaction_source_type_id||'#'||mmtt.transaction_action_id,'1#27',1,
3000 '7#12',1,'12#27',1,'13#12',1,'4#27',1,null) transaction_temp_id
3001 , mtrl.wms_process_flag
3002 , (mtrl.quantity - Nvl(mtrl.quantity_delivered, 0)) quantity
3003 , mtrl.primary_quantity
3004 , mtrl.uom_code
3005 , mtrl.lpn_id
3006 , mtrl.inventory_item_id
3007 , mtrl.lot_number
3008 -- OPMConvergence
3009 , (mtrl.secondary_quantity - Nvl(mtrl.secondary_quantity_delivered, 0)) secondary_quantity_2
3010 , mtrl.secondary_quantity
3011 , mtrl.secondary_uom_code
3012 -- OPMConvergence
3013 , mtrl.crossdock_type
3014 , mtrl.backorder_delivery_detail_id
3015 , mmtt.wip_supply_type
3016 , mtrl.reference
3017 , mtrl.reference_type_code
3018 , mtrl.reference_id
3019 FROM mtl_txn_request_lines mtrl
3020 , mtl_material_transactions_temp mmtt
3021 WHERE mtrl.organization_id = p_org_id
3022 AND nvl(mtrl.from_subinventory_code,'@$#_') = nvl(p_from_sub,'@$#_')
3023 AND nvl(mtrl.from_locator_id,-1) = nvl(p_from_locator_id,-1)
3024 AND (nvl(mtrl.project_id,-1) = nvl(p_project_id,-1)
3025 or p_lpn is null) -- Bug 6618890 --Bug#8627996
3026 AND (nvl(mtrl.task_id,-1) = nvl(p_task_id,-1)
3027 or p_lpn is null) -- Bug 6618890 --Bug#8627996
3028 AND Nvl(inspection_status,-1) = Nvl(p_inspection_status,-1)
3029 AND mtrl.inventory_item_id = p_item
3030 AND Nvl(mtrl.revision, Nvl(p_rev, '@@@@')) = Nvl(p_rev, '@@@@')
3031 AND Nvl(mtrl.lpn_id, -1) = Nvl(p_lpn, -1)
3032 AND Nvl(mtrl.lot_number, Nvl(p_lot,'@$#_')) = Nvl(p_lot, '@$#_')
3033 AND (mtrl.quantity - Nvl(mtrl.quantity_delivered, 0)) > 0
3034 AND mmtt.move_order_line_id (+) = mtrl.line_id
3035 AND mmtt.organization_id (+) = mtrl.organization_id
3036 AND exists (SELECT 1
3037 FROM mtl_txn_request_headers mtrh
3038 WHERE mtrh.move_order_type = inv_globals.g_move_order_put_away
3039 AND mtrh.header_id = mtrl.header_id)
3040 --only pick up lines that are NOT loaded
3041 AND (mmtt.transaction_temp_id IS NULL
3042 OR
3043 (mmtt.transaction_temp_id IS NOT NULL
3044 --Bug 5231114:Added the condition on transaction_source_type_id and
3045 --transaction_action_id for the following combinations:13/12 and 4/27.
3046 AND ((mmtt.transaction_source_type_id = 1 AND mmtt.transaction_action_id = 27)
3047 OR (mmtt.transaction_source_type_id = 7 AND mmtt.transaction_action_id = 12)
3048 OR (mmtt.transaction_source_type_id = 12 AND mmtt.transaction_action_id = 27)
3049 OR (mmtt.transaction_source_type_id = 13 AND mmtt.transaction_action_id = 12)
3050 OR (mmtt.transaction_source_type_id = 4 AND mmtt.transaction_action_id = 27))
3051 AND NOT exists (SELECT 1
3052 FROM wms_dispatched_tasks wdt
3053 WHERE wdt.transaction_temp_id = mmtt.transaction_temp_id
3054 AND wdt.status IN (3, 4) -- dispached or loaded
3055 AND wdt.task_type = 2 -- putaway
3056 )
3057 )
3058 )
3059 ORDER BY 2 DESC, Nvl(transaction_temp_id, -1) ASC ;
3060
3061 l_mol_rec c_mol_no_mmtt%ROWTYPE;
3062
3063 l_remaining_primary_quantity NUMBER;
3064 l_dummy VARCHAR2(1);
3065 l_mol_qty_in_puom NUMBER;
3066 l_error_code NUMBER;
3067 l_mo_split_tb inv_rcv_integration_apis.mo_in_tb_tp;
3068
3069 l_debug NUMBER;
3070 l_progress VARCHAR2(10);
3071 l_module_name VARCHAR2(30);
3072
3073 BEGIN
3074
3075 l_debug := NVL(fnd_profile.VALUE('INV_DEBUG_TRACE'), 0);
3076
3077 IF (l_debug = 1) THEN
3078 print_debug('Entering split_close_mo_for_ret_corr...',l_module_name,4);
3079 print_debug(' p_cas_mol_tb(1).organization_id => '||p_cas_mol_tb(1).organization_id,l_module_name,4);
3080 print_debug(' p_cas_mol_tb(1).inventory_item_id => '||p_cas_mol_tb(1).inventory_item_id,l_module_name,4);
3081 print_debug(' p_cas_mol_tb(1).lpn_id => '||p_cas_mol_tb(1).lpn_id,l_module_name,4);
3082 print_debug(' p_cas_mol_tb(1).lot_number => '||p_cas_mol_tb(1).lot_number,l_module_name,4);
3083 print_debug(' p_cas_mol_tb(1).item_revision => '||p_cas_mol_tb(1).item_revision,l_module_name,4);
3084 print_debug(' p_cas_mol_tb(1).from_subinventory_code => '||p_cas_mol_tb(1).from_subinventory_code,l_module_name,4);
3085 print_debug(' p_cas_mol_tb(1).from_locator_id => '||p_cas_mol_tb(1).from_locator_id,l_module_name,4);
3086 print_debug(' p_cas_mol_tb(1).project_id => '||p_cas_mol_tb(1).project_id,l_module_name,4);
3087 print_debug(' p_cas_mol_tb(1).task_id => '||p_cas_mol_tb(1).task_id,l_module_name,4);
3088 print_debug(' p_cas_mol_tb(1).inspection_status => '||p_cas_mol_tb(1).inspection_status,l_module_name,4);
3089 print_debug(' p_cas_mol_tb(1).uom_code => '||p_cas_mol_tb(1).uom_code,l_module_name,4);
3090 END IF;
3091
3092 l_progress := '10';
3093 l_module_name := 'SET_CLOSE_MO_FOR_RET_CORR';
3094 x_return_status := fnd_api.g_ret_sts_success;
3095
3096 OPEN c_mol_no_mmtt(p_cas_mol_tb(1).organization_id
3097 ,p_cas_mol_tb(1).inventory_item_id
3098 ,p_cas_mol_tb(1).lpn_id
3099 ,p_cas_mol_tb(1).lot_number
3100 ,p_cas_mol_tb(1).item_revision
3101 ,p_cas_mol_tb(1).from_subinventory_code
3102 ,p_cas_mol_tb(1).from_locator_id
3103 ,p_cas_mol_tb(1).project_id
3104 ,p_cas_mol_tb(1).task_id
3105 ,p_cas_mol_tb(1).inspection_status
3106 ,p_cas_mol_tb(1).uom_code
3107 );
3108
3109 l_remaining_primary_quantity := Abs(p_cas_mol_tb(1).primary_qty);
3110
3111 IF (l_debug = 1) THEN
3112 print_debug('Remaining Primary Quantity:'||l_remaining_primary_quantity,l_module_name,4);
3113 END IF;
3114
3115 LOOP
3116 FETCH c_mol_no_mmtt INTO l_mol_rec;
3117 EXIT WHEN c_mol_no_mmtt%notfound;
3118
3119
3120 /* per Karun's request, this query has been moved into the cursor above
3121 IF (l_mol_rec.transaction_temp_id IS NOT NULL) THEN
3122 BEGIN
3123 SELECT '1'
3124 INTO l_dummy
3125 FROM dual
3126 WHERE exists
3127 (SELECT 1
3128 FROM wms_dispatched_tasks wdt
3129 , mtl_material_transactions_temp mmtt
3130 WHERE mmtt.move_order_line_id = l_mol_rec.line_id
3131 AND ((transaction_source_type_id = 1 AND transaction_action_id = 27)
3132 OR (transaction_source_type_id = 7 AND transaction_action_id = 12)
3133 OR (transaction_source_type_id = 12 AND transaction_action_id = 27))
3134 AND wdt.transaction_temp_id = mmtt.transaction_temp_id
3135 AND wdt.status IN (3, 4) -- dispached or loaded
3136 AND wdt.task_type = 2 -- putaway
3137 );
3138
3139 RAISE fnd_api.g_exc_error;
3140 EXCEPTION
3141 WHEN no_data_found THEN
3142 NULL;
3143 END;
3144 END IF;
3145 */
3146
3147
3148 l_mol_qty_in_puom := inv_rcv_cache.convert_qty(l_mol_rec.inventory_item_id,
3149 l_mol_rec.quantity,
3150 l_mol_rec.uom_code,
3151 p_cas_mol_tb(1).primary_uom_code);
3152
3153 IF l_mol_qty_in_puom <= l_remaining_primary_quantity THEN
3154 --update the mol
3155 UPDATE mtl_txn_request_lines
3156 SET quantity = Nvl(quantity_delivered,0)
3157 , primary_quantity = ((primary_quantity*Nvl(quantity_delivered,0))/quantity)
3158 , quantity_detailed = Decode(quantity_detailed,NULL,quantity_detailed,quantity_delivered)
3159 -- OPMConvergence
3160 , secondary_quantity = Nvl(secondary_quantity_delivered,0)
3161 , secondary_quantity_detailed = Decode(secondary_quantity_detailed,NULL,secondary_quantity_detailed,secondary_quantity_delivered)
3162 -- OPMConvergence
3163 , line_status = 5
3164 , wms_process_flag = 1
3165 WHERE line_id = l_mol_rec.line_id;
3166
3167 -- Call cancel operation plan
3168 IF (l_debug = 1) THEN
3169 print_debug('calling call_atf_api:'||l_mol_rec.line_id,l_module_name,4);
3170 END IF;
3171
3172 inv_rcv_integration_pvt.call_atf_api(x_return_status => x_return_status,
3173 x_msg_data => x_msg_data,
3174 x_msg_count => x_msg_count,
3175 x_error_code => l_error_code,
3176 p_source_task_id => NULL,
3177 p_activity_type_id => 1,
3178 p_operation_type_id => NULL,
3179 p_mol_id => l_mol_rec.line_id,
3180 p_atf_api_name => inv_rcv_integration_pvt.g_atf_api_cancel);
3181
3182 IF (x_return_status <> fnd_api.g_ret_sts_success) THEN
3183 IF (l_debug = 1) THEN
3184 print_debug('call_atf_api failed:'||l_mol_rec.line_id,l_module_name,4);
3185 END IF;
3186 --raise error
3187 RAISE fnd_api.g_exc_error;
3188 END IF;
3189
3190 l_remaining_primary_quantity := l_remaining_primary_quantity -
3191 l_mol_qty_in_puom;
3192 ELSE
3193 -- Call split_mo and then update the new line to quantity
3194 -- = 0 and then
3195 -- Call cancel operation plan for new line
3196
3197 IF (l_debug = 1) THEN
3198 print_debug('CALLING SPLIT_MO:'||l_remaining_primary_quantity,l_module_name,4);
3199 END IF;
3200
3201 l_mo_split_tb(1).prim_qty := l_remaining_primary_quantity;
3202 l_mo_split_tb(1).line_id := NULL;
3203
3204 inv_rcv_integration_apis.split_mo
3205 (p_orig_mol_id => l_mol_rec.line_id,
3206 p_mo_splt_tb => l_mo_split_tb,
3207 x_return_status => x_return_status,
3208 x_msg_count => x_msg_count,
3209 x_msg_data => x_msg_data);
3210
3211 IF (x_return_status <> fnd_api.g_ret_sts_success) THEN
3212 RAISE fnd_api.g_exc_error;
3213 END IF;
3214
3215
3216 IF (l_debug = 1) THEN
3217 print_debug('NEW LINE ID:'||l_mo_split_tb(1).line_id,l_module_name,4);
3218 END IF;
3219
3220 --update the new line for return
3221 UPDATE mtl_txn_request_lines
3222 SET quantity = Nvl(quantity_delivered,0)
3223 , primary_quantity = ((primary_quantity*Nvl(quantity_delivered,0))/quantity)
3224 , quantity_detailed = Decode(quantity_detailed,NULL,quantity_detailed,quantity_delivered)
3225 -- OPMConvergence
3226 , secondary_quantity = Nvl(secondary_quantity_delivered,0)
3227 , secondary_quantity_detailed = Decode(secondary_quantity_detailed,NULL,secondary_quantity_detailed,secondary_quantity_delivered)
3228 -- OPMConvergence
3229 , line_status = 5
3230 , wms_process_flag = 1
3231 WHERE line_id = l_mo_split_tb(1).line_id;
3232
3233 -- Call cancel operation plan for the new line
3234 inv_rcv_integration_pvt.call_atf_api(x_return_status => x_return_status,
3235 x_msg_data => x_msg_data,
3236 x_msg_count => x_msg_count,
3237 x_error_code => l_error_code,
3238 p_source_task_id => NULL,
3239 p_activity_type_id => 1,
3240 p_operation_type_id => NULL,
3241 p_mol_id => l_mo_split_tb(1).line_id,
3242 p_atf_api_name => inv_rcv_integration_pvt.g_atf_api_cancel);
3243
3244 IF (x_return_status <> fnd_api.g_ret_sts_success) THEN
3245 IF (l_debug = 1) THEN
3246 print_debug('call_atf_api failed:'||l_mo_split_tb(1).line_id,l_module_name,4);
3247 END IF;
3248 --raise error
3249 RAISE fnd_api.g_exc_error;
3250 END IF;
3251
3252 --update the old line for wms process flag
3253 UPDATE mtl_txn_request_lines
3254 SET wms_process_flag = 1
3255 WHERE line_id = l_mol_rec.line_id;
3256
3257 l_remaining_primary_quantity := 0;
3258 END IF;
3259
3260 IF (l_remaining_primary_quantity = 0) THEN
3261 EXIT;
3262 END IF;
3263
3264 END LOOP;
3265
3266 IF l_remaining_primary_quantity > 0 THEN
3267 --raise error
3268 IF (l_debug = 1) THEN
3269 print_debug('Quantity Still Remaining!!! WHY???:'||l_remaining_primary_quantity,l_module_name,4);
3270 END IF;
3271 RAISE fnd_api.g_exc_error;
3272 END IF;
3273 EXCEPTION
3274 WHEN OTHERS THEN
3275 IF (l_debug = 1) THEN
3276 print_debug('Exception occurred at progress:'||l_progress,l_module_name,4);
3277 print_debug('SQLCODE:'||SQLCODE||' SQLERRM:'||SQLERRM,l_module_name,4);
3278 END IF;
3279 x_return_status := fnd_api.g_ret_sts_error;
3280 END split_close_mo_for_ret_corr;
3281
3282 PROCEDURE maintain_reservations
3283 (x_return_status OUT NOCOPY VARCHAR2
3284 ,x_msg_count OUT NOCOPY NUMBER
3285 ,x_msg_data OUT NOCOPY VARCHAR2
3286 ,x_mol_tb OUT NOCOPY inv_rcv_integration_pvt.cas_mol_rec_tb_tp
3287 ,p_cas_mol_tb IN inv_rcv_integration_pvt.cas_mol_rec_tb_tp
3288 )
3289 IS
3290 l_debug NUMBER;
3291 l_progress VARCHAR2(10);
3292 l_module_name VARCHAR2(30);
3293 BEGIN
3294 l_debug := NVL(fnd_profile.VALUE('INV_DEBUG_TRACE'), 0);
3295
3296 IF (l_debug = 1) THEN
3297 print_debug('Entering maintain_reservations...',l_module_name,4);
3298 print_debug(' p_cas_mol_tb(1).transaction_type => ' ||p_cas_mol_tb(1).transaction_type,l_module_name,4);
3299 END IF;
3300
3301 l_progress := '10';
3302 l_module_name := 'MAINTAIN_RESERVATIONS';
3303 x_return_status := fnd_api.g_ret_sts_success;
3304
3305
3306
3307 --{{
3308 --********** PROCEDURE maintain_reservations *********
3309 --Make sure that the following transaction are tested, and
3310 --the correct private APIs are called }}
3311
3312 --{{
3313 --Test Receipt, Match, positive correction on receipt}}
3314 IF (p_cas_mol_tb(1).transaction_type IN ('RECEIVE','MATCH')
3315 OR
3316 (p_cas_mol_tb(1).transaction_type = 'CORRECT'
3317 AND p_cas_mol_tb(1).primary_qty > 0
3318 AND p_cas_mol_tb(1).parent_txn_type IN ('RECEIVE'))
3319 OR
3320 (p_cas_mol_tb(1).transaction_type = 'CORRECT'
3321 AND p_cas_mol_tb(1).primary_qty < 0
3322 AND p_cas_mol_tb(1).parent_txn_type IN ('RETURN TO VENDOR','RETURN TO CUSTOMER'))
3323 ) THEN
3324 maintain_rsv_receive
3325 (x_return_status => x_return_status
3326 ,x_msg_count => x_msg_count
3327 ,x_msg_data => x_msg_data
3328 ,x_cas_mol_rec_tb => x_mol_tb
3329 ,p_cas_mol_rec_tb => p_cas_mol_tb);
3330
3331 --{{
3332 --Test deliver and positive correction of deliver}}
3333 ELSIF (p_cas_mol_tb(1).transaction_type = 'DELIVER'
3334 OR
3335 (p_cas_mol_tb(1).transaction_type = 'CORRECT'
3336 AND p_cas_mol_tb(1).primary_qty > 0
3337 AND p_cas_mol_tb(1).parent_txn_type IN ('DELIVER'))) THEN
3338 maintain_rsv_deliver
3339 (x_return_status => x_return_status
3340 ,x_msg_count => x_msg_count
3341 ,x_msg_data => x_msg_data
3342 ,p_cas_mol_rec_tb => p_cas_mol_tb);
3343
3344 --{{
3345 --Test cancelling an ASN that has reservation tied to it}}
3346 ELSIF p_cas_mol_tb(1).transaction_type = 'CANCEL' THEN
3347 maintain_rsv_cancel_asn
3348 (x_return_status => x_return_status
3349 ,x_msg_count => x_msg_count
3350 ,x_msg_data => x_msg_data
3351 ,p_cas_mol_rec_tb => p_cas_mol_tb);
3352
3353 --{{
3354 --Test import ASN of a PO against which a reservation is created}}
3355 ELSIF p_cas_mol_tb(1).transaction_type = 'SHIP' AND p_cas_mol_tb(1).source_document_code = 'PO' THEN
3356 maintain_rsv_import_asn
3357 (x_return_status => x_return_status
3358 ,x_msg_count => x_msg_count
3359 ,x_msg_data => x_msg_data
3360 ,p_cas_mol_rec_tb => p_cas_mol_tb);
3361
3362 --{{
3363 --Test 1. Negative correction of Receipt
3364 -- 2. Positive correction of RTV and RTC
3365 -- 3. RTV and RTC
3366 --MOL should be reduced from receiving
3367 ELSIF ((p_cas_mol_tb(1).transaction_type IN ('CORRECT')
3368 AND p_cas_mol_tb(1).primary_qty < 0
3369 AND p_cas_mol_tb(1).parent_txn_type IN ('RECEIVE'))
3370 OR
3371 (p_cas_mol_tb(1).transaction_type IN ('CORRECT')
3372 AND p_cas_mol_tb(1).primary_qty > 0
3373 AND p_cas_mol_tb(1).parent_txn_type IN ('RETURN TO VENDOR','RETURN TO CUSTOMER')
3374 AND Nvl(p_cas_mol_tb(1).grand_parent_txn_type,'#@#') <> 'DELIVER')
3375 OR
3376 (p_cas_mol_tb(1).transaction_type IN ('RETURN TO VENDOR','RETURN TO CUSTOMER')
3377 AND p_cas_mol_tb(1).parent_txn_type <> 'DELIVER')) THEN
3378
3379 IF (l_debug = 1) THEN
3380 print_debug('- Corr of Receive/+ Corr of RTV and RTC/RTV and RTC. Calling maintain_rsv_returns',l_module_name,4);
3381 END IF;
3382
3383 maintain_rsv_returns
3384 (x_return_status => x_return_status
3385 ,x_msg_count => x_msg_count
3386 ,x_msg_data => x_msg_data
3387 ,p_cas_mol_rec_tb => p_cas_mol_tb);
3388
3389
3390 --{{
3391 --Test 1. Negative corr of inspect and transfer
3392 -- 2. Positive corr of inspect and transfer
3393 --MOL should be transfered within receiving
3394 ELSIF ((p_cas_mol_tb(1).transaction_type IN ('CORRECT')
3395 AND p_cas_mol_tb(1).primary_qty < 0
3396 AND p_cas_mol_tb(1).parent_txn_type IN ('ACCEPT','REJECT','TRANSFER'))
3397 OR
3398 (p_cas_mol_tb(1).transaction_type IN ('CORRECT')
3399 AND p_cas_mol_tb(1).primary_qty > 0
3400 AND p_cas_mol_tb(1).parent_txn_type IN ('ACCEPT','REJECT','TRANSFER'))) THEN
3401
3402 IF (l_debug = 1) THEN
3403 print_debug('-/+ Corr of Inspect and Transfer. Need to close and create MOL',l_module_name,4);
3404 END IF;
3405
3406 split_close_mo_for_ret_corr(x_return_status => x_return_status
3407 ,x_msg_count => x_msg_count
3408 ,x_msg_data => x_msg_data
3409 ,p_cas_mol_tb => p_cas_mol_tb);
3410
3411 IF (x_return_status <> fnd_api.g_ret_sts_success) THEN
3412 IF (l_debug = 1) THEN
3413 print_debug('split_mo_ret_corr failed for this process ',l_module_name,4);
3414 END IF;
3415 --raise error
3416 RAISE fnd_api.g_exc_error;
3417 END IF;
3418
3419 set_mol_wdd_tbl(p_cas_mol_tb(1),
3420 x_mol_tb,
3421 p_cas_mol_tb(1).primary_qty,
3422 NULL,
3423 NULL);
3424
3425
3426 --{{
3427 --Test negative correction of RTC
3428 --Materials should be reduced into receiving}}
3429 ELSIF (p_cas_mol_tb(1).transaction_type IN ('CORRECT')
3430 AND p_cas_mol_tb(1).primary_qty < 0
3431 AND p_cas_mol_tb(1).parent_txn_type IN ('RETURN TO RECEIVING')) THEN
3432
3433 IF (l_debug = 1) THEN
3434 print_debug('- Corr of RTR. Need to close MOL',l_module_name,4);
3435 END IF;
3436
3437 split_close_mo_for_ret_corr(x_return_status => x_return_status
3438 ,x_msg_count => x_msg_count
3439 ,x_msg_data => x_msg_data
3440 ,p_cas_mol_tb => p_cas_mol_tb);
3441
3442 IF (x_return_status <> fnd_api.g_ret_sts_success) THEN
3443 IF (l_debug = 1) THEN
3444 print_debug('split_mo_ret_corr failed for this process ',l_module_name,4);
3445 END IF;
3446 --raise error
3447 RAISE fnd_api.g_exc_error;
3448 END IF;
3449
3450
3451 --{{
3452 --Test 1. negative correction of deliver
3453 -- 2. positive correction of RTR
3454 -- 3. RTR
3455 --Materials should be added to receiving }}
3456 ELSIF ((p_cas_mol_tb(1).transaction_type IN ('CORRECT')
3457 AND p_cas_mol_tb(1).primary_qty < 0
3458 AND p_cas_mol_tb(1).parent_txn_type = 'DELIVER')
3459 OR
3460 (p_cas_mol_tb(1).transaction_type IN ('CORRECT')
3461 AND p_cas_mol_tb(1).primary_qty > 0
3462 AND p_cas_mol_tb(1).parent_txn_type IN ('RETURN TO RECEIVING'))
3463 OR
3464 p_cas_mol_tb(1).transaction_type = 'RETURN TO RECEIVING') THEN
3465
3466 IF (l_debug = 1) THEN
3467 print_debug('- Corr of deliver/+ Corr of RTR/RTR. Need to create MOL',l_module_name,4);
3468 END IF;
3469
3470 set_mol_wdd_tbl(p_cas_mol_tb(1),
3471 x_mol_tb,
3472 p_cas_mol_tb(1).primary_qty,
3473 NULL,
3474 NULL);
3475 END IF;
3476
3477 --{{
3478 --********** END PROCEDURE maintain_reservations *********}}
3479
3480 EXCEPTION
3481 WHEN OTHERS THEN
3482 IF (l_debug = 1) THEN
3483 print_debug('Exception occurred at progress:'||l_progress,l_module_name,4);
3484 print_debug('SQLCODE:'||SQLCODE||' SQLERRM:'||SQLERRM,l_module_name,4);
3485 END IF;
3486 x_return_status := fnd_api.g_ret_sts_error;
3487 END maintain_reservations;
3488 END inv_rcv_reservation_util;
3489