[Home] [Help]
PACKAGE BODY: APPS.INV_RCV_RESERVATION_UTIL
Source
1 PACKAGE BODY INV_RCV_RESERVATION_UTIL AS
2 /* $Header: INVRUTLB.pls 120.20 2007/12/19 22:15:13 musinha 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 END IF;
729
730 l_txn_qty := inv_rcv_cache.convert_qty(p_orig_rcpt_rec.inventory_item_id
731 ,p_prim_qty
732 ,p_orig_rcpt_rec.primary_uom_code
733 ,p_orig_rcpt_rec.uom_code);
734
735 IF (Nvl(p_orig_rcpt_rec.secondary_quantity,0) <> 0) THEN
736 l_sec_qty := Round((p_prim_qty*p_orig_rcpt_rec.secondary_quantity)/p_orig_rcpt_rec.primary_qty,
737 inv_rcv_cache.g_conversion_precision);
738 END IF;
739
740 IF p_cas_mol_rec_tb.exists(Nvl(p_wdd_id,-1)) THEN
741 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;
742 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;
743 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;
744 ELSE
745 p_cas_mol_rec_tb(Nvl(p_wdd_id,-1)) := p_orig_rcpt_rec;
746 p_cas_mol_rec_tb(Nvl(p_wdd_id,-1)).primary_qty := p_prim_qty;
747 p_cas_mol_rec_tb(Nvl(p_wdd_id,-1)).quantity := l_txn_qty;
748 p_cas_mol_rec_tb(Nvl(p_wdd_id,-1)).backorder_delivery_detail_id := p_wdd_id;
749 p_cas_mol_rec_tb(Nvl(p_wdd_id,-1)).crossdock_type := p_crossdock_type;
750 p_cas_mol_rec_tb(Nvl(p_wdd_id,-1)).secondary_quantity := l_sec_qty;
751 END IF;
752 EXCEPTION
753 WHEN OTHERS THEN
754 IF (l_debug = 1) THEN
755 print_debug('Error in set_mol_wdd_tbl...',l_module_name,4);
756 END IF;
757 RAISE fnd_api.g_exc_unexpected_error;
758 END set_mol_wdd_tbl;
759
760 PROCEDURE maintain_rsv_import_asn
761 (x_return_status OUT NOCOPY VARCHAR2
762 ,x_msg_count OUT NOCOPY NUMBER
763 ,x_msg_data OUT NOCOPY VARCHAR2
764 ,p_cas_mol_rec_tb IN inv_rcv_integration_pvt.cas_mol_rec_tb_tp
765 ) IS
766 l_rsv_query_rec inv_reservation_global.mtl_reservation_rec_type;
767 l_rsv_results_tbl inv_reservation_global.mtl_reservation_tbl_type;
768 l_rsv_update_rec inv_reservation_global.mtl_reservation_rec_type;
769 l_rsv_results_count NUMBER;
770 l_remaining_prim_qty NUMBER;
771 l_reservation_id NUMBER;
772 l_new_wdd_id NUMBER;
773 l_dummy_serial VARCHAR2(30);
774 l_dummy NUMBER;
775
776 l_return_status VARCHAR2(1);
777 l_error_code NUMBER;
778 l_msg_count NUMBER;
779 l_msg_data VARCHAR2(2000);
780
781 l_debug NUMBER;
782 l_progress VARCHAR2(10);
783 l_module_name VARCHAR2(30);
784
785 BEGIN
786
787 --{{
788 --********** PROCEDURE maintain_rsv_import_asn *********}}
789
790 l_debug := NVL(fnd_profile.VALUE('INV_DEBUG_TRACE'), 0);
791 l_progress := '10';
792 l_module_name := 'maintain_rsv_import_asn';
793 x_return_status := fnd_api.g_ret_sts_success;
794
795 IF (l_debug = 1) THEN
796 print_debug('Entering maintain_rsv_import_asn...',l_module_name,4);
797 print_debug(' p_cas_mol_rec_tb(1).po_header_id => '||p_cas_mol_rec_tb(1).po_header_id,l_module_name,4);
798 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);
799 print_debug(' p_cas_mol_rec_tb(1).inventory_item_id => '||p_cas_mol_rec_tb(1).inventory_item_id,l_module_name,4);
800 print_debug(' p_cas_mol_rec_tb(1).organization_id => '||p_cas_mol_rec_tb(1).organization_id,l_module_name,4);
801 print_debug(' p_cas_mol_rec_tb(1).primary_qty => '||p_cas_mol_rec_tb(1).primary_qty,l_module_name,4);
802 print_debug(' p_cas_mol_rec_tb(1).shipment_line_id => '||p_cas_mol_rec_tb(1).shipment_line_id,l_module_name,4);
803 print_debug(' p_cas_mol_rec_tb(1).project_id => '||p_cas_mol_rec_tb(1).project_id,l_module_name,4);
804 print_debug(' p_cas_mol_rec_tb(1).task_id => '||p_cas_mol_rec_tb(1).task_id,l_module_name,4);
805 END IF;
806
807 --{{
808 --Test import asn for the following cases:
809 --1. Import an ASN whose document matches to a manual reservation
810 --2. Import an ASN whose document matches to a xdock reservation
811 --3. Import an ASN whose document does not match to any reservations
812 --4. Import an ASN whose document matches to mixed reservations types }}
813
814 --1.0 Query reservation for the particular PO
815 l_rsv_query_rec.supply_source_type_id := g_source_type_po;
816 l_rsv_query_rec.supply_source_header_id := p_cas_mol_rec_tb(1).po_header_id;
817 l_rsv_query_rec.supply_source_line_id := p_cas_mol_rec_tb(1).po_line_location_id;
818 l_rsv_query_rec.inventory_item_id := p_cas_mol_rec_tb(1).inventory_item_id;
819 l_rsv_query_rec.organization_id := p_cas_mol_rec_tb(1).organization_id;
820
821 IF (p_cas_mol_rec_tb(1).project_id IS NOT NULL) THEN
822 l_rsv_query_rec.project_id := p_cas_mol_rec_tb(1).project_id;
823 IF (p_cas_mol_rec_tb(1).task_id IS NOT NULL) THEN
824 l_rsv_query_rec.task_id := p_cas_mol_rec_tb(1).task_id;
825 END IF;
826 END IF ;
827
828 l_progress := '@@@';
829 BEGIN
830 SELECT 1
831 INTO l_dummy
832 FROM po_line_locations_all
833 WHERE line_location_id = p_cas_mol_rec_tb(1).po_line_location_id
834 FOR UPDATE NOWAIT;
835 EXCEPTION
836 WHEN OTHERS THEN
837 IF (l_debug = 1) THEN
838 print_debug('Some other exception occurred!',l_module_name,4);
839 print_debug('SQLCODE:'||SQLCODE||' SQLERRM:'||SQLERRM,l_module_name,4);
840 END IF;
841 l_progress := '@@@';
842 RAISE fnd_api.g_exc_unexpected_error;
843 END;
844 l_progress := '@@@';
845
846 IF (l_debug = 1) THEN
847 print_debug('Calling query_reservation',l_module_name,4);
848 END IF;
849
850 --2.0 Query reservation
851 l_progress := '@@@';
852 query_reservation
853 (p_query_input => l_rsv_query_rec
854 ,p_sort_by_req_date => g_query_demand_ship_date_asc
855 ,x_rsv_results => l_rsv_results_tbl
856 ,x_return_status => l_return_status
857 );
858 l_progress := '@@@';
859
860 IF (l_debug = 1) THEN
861 print_debug('Returned from query_reservation',l_module_name,4);
862 print_debug('x_return_status: '||l_return_status,l_module_name,4);
863 END IF;
864
865 IF (l_return_status <> fnd_api.g_ret_sts_success) THEN
866 l_progress := '@@@';
867 RAISE fnd_api.g_exc_unexpected_error;
868 END IF;
869
870 l_remaining_prim_qty := p_cas_mol_rec_tb(1).primary_qty;
871
872 IF (l_debug = 1) THEN
873 print_debug('l_remaining_prim_qty = ' ||l_remaining_prim_qty,l_module_name,4);
874 print_debug('l_rsv_results_tbl.COUNT = ' ||l_rsv_results_tbl.COUNT,l_module_name,4);
875 END IF;
876
877 FOR i IN 1..l_rsv_results_tbl.COUNT LOOP
878 EXIT WHEN l_remaining_prim_qty <= 0;
879
880 IF (l_debug = 1) THEN
881 print_debug('l_remaining_prim_qty:'||l_remaining_prim_qty||
882 ' i:'||i||
883 ' rsv_id:'||l_rsv_results_tbl(i).reservation_id||
884 ' prim_qty:'||l_rsv_results_tbl(i).primary_reservation_quantity||
885 ' rsv_qty:'||l_rsv_results_tbl(i).reservation_quantity||
886 ' uom_code:'||l_rsv_results_tbl(i).reservation_uom_code||
887 ' orig_supply_src_type_id:'||l_rsv_results_tbl(i).orig_supply_source_type_id||
888 ' demand_src_line_detail:'||l_rsv_results_tbl(i).demand_source_line_detail||
889 ' ext_src_code:'||l_rsv_results_tbl(i).external_source_code
890 ,l_module_name,4);
891 END IF;
892
893 l_rsv_update_rec := l_rsv_results_tbl(i);
894
895 IF (l_rsv_results_tbl(i).primary_reservation_quantity > l_remaining_prim_qty) THEN
896 -- Reservation has more than enough to satisfy remaining qty, so split
897
898 IF (l_debug = 1) THEN
899 print_debug('l_rsv_results_tbl(i).primary_reservation_quantity > l_remaining_prim_qty',
900 l_module_name,4);
901 END IF;
902
903 l_rsv_update_rec.primary_reservation_quantity := l_remaining_prim_qty;
904 l_rsv_update_rec.reservation_quantity := inv_rcv_cache.convert_qty(p_cas_mol_rec_tb(1).inventory_item_id
905 ,l_remaining_prim_qty
906 ,l_rsv_results_tbl(i).primary_uom_code
907 ,l_rsv_results_tbl(i).reservation_uom_code);
908
909 IF (l_rsv_results_tbl(i).demand_source_line_detail IS NOT NULL) THEN
910 IF (l_debug = 1) THEN
911 print_debug('Calling split_wdd',l_module_name,4);
912 END IF;
913
914 l_progress := '@@@';
915 split_wdd
916 (x_return_status => l_return_status
917 ,x_msg_count => l_msg_count
918 ,x_msg_data => l_msg_data
919 ,x_new_wdd_id => l_new_wdd_id
920 ,p_wdd_id => l_rsv_results_tbl(i).demand_source_line_detail
921 ,p_new_mol_id => NULL
922 ,p_qty_to_splt => l_remaining_prim_qty);
923 l_progress := '@@@';
924
925 IF (l_debug = 1) THEN
926 print_debug('Returned from split_wdd',l_module_name,4);
927 print_debug('l_return_status =>'||l_return_status,l_module_name,4);
928 END IF;
929
930 IF (l_return_status <> fnd_api.g_ret_sts_success) THEN
931 l_progress := '@@@';
932 RAISE fnd_api.g_exc_unexpected_error;
933 END IF;
934
935 l_rsv_update_rec.demand_source_line_detail := l_new_wdd_id;
936 END IF;
937
938 l_remaining_prim_qty := 0;
939
940 ELSE
941 IF (l_debug = 1) THEN
942 print_debug('l_rsv_results_tbl(i).primary_reservation_quantity <= l_remaining_prim_qty',
943 l_module_name,4);
944 END IF;
945 l_remaining_prim_qty := l_remaining_prim_qty - l_rsv_results_tbl(i).primary_reservation_quantity;
946 END IF; --END IF (l_rsv_results_tbl(i).primary_reservation_quantity > l_remaining_prim_qty)
947
948 --Transfer reservation to ASN
949 l_rsv_update_rec.supply_source_type_id := g_source_type_asn;
950 l_rsv_update_rec.supply_source_line_detail := p_cas_mol_rec_tb(1).shipment_line_id;
951
952 IF (l_debug = 1) THEN
953 print_debug('Calling transfer_reservation...',l_module_name,4);
954 END IF;
955
956 transfer_reservation
957 (p_original_rsv_rec => l_rsv_results_tbl(i)
958 ,p_to_rsv_rec => l_rsv_update_rec
959 ,x_new_rsv_id => l_reservation_id
960 ,x_return_status => l_return_status);
961
962 IF (l_debug = 1) THEN
963 print_debug('Returned from transfer_reservation',l_module_name,4);
964 print_debug('x_return_status: '||l_return_status,l_module_name,4);
965 END IF;
966
967 IF (l_return_status <> fnd_api.g_ret_sts_success) THEN
968 l_progress := '@@@';
969 RAISE fnd_api.g_exc_unexpected_error;
970 END IF;
971
972 IF (l_debug = 1) THEN
973 print_debug('l_reservation_id: '||l_reservation_id,l_module_name,4);
974 END IF;
975
976 END LOOP;
977
978 IF (l_debug = 1) THEN
979 print_debug('Exitting maintain_rsv_import_asn with the following values:',l_module_name,4);
980 print_debug('x_return_status => '||x_return_status,l_module_name,4);
981 print_debug('x_msg_count => '||x_msg_count,l_module_name,4);
982 print_debug('x_msg_data => '||x_msg_data,l_module_name,4);
983 END IF;
984
985 --{{
986 --********** END PROCEDURE maintain_rsv_import_asn *********}}
987 EXCEPTION
988 WHEN OTHERS THEN
989 IF (l_debug = 1) THEN
990 print_debug('Exception occurred at progress: '||l_progress,l_module_name,4);
991 print_debug('SQLCODE:'||SQLCODE||' SQLERRM:'||SQLERRM,l_module_name,4);
992 END IF;
993 x_return_status := fnd_api.g_ret_sts_error;
994 END maintain_rsv_import_asn;
995
996 PROCEDURE maintain_rsv_cancel_asn
997 (x_return_status OUT NOCOPY VARCHAR2
998 ,x_msg_count OUT NOCOPY NUMBER
999 ,x_msg_data OUT NOCOPY VARCHAR2
1000 ,p_cas_mol_rec_tb IN inv_rcv_integration_pvt.cas_mol_rec_tb_tp
1001 ) IS
1002 l_rsv_query_rec inv_reservation_global.mtl_reservation_rec_type;
1003 l_rsv_results_tbl inv_reservation_global.mtl_reservation_tbl_type;
1004 l_rsv_update_rec inv_reservation_global.mtl_reservation_rec_type;
1005 l_reservation_id NUMBER;
1006 l_dummy NUMBER;
1007
1008 l_return_status VARCHAR2(1);
1009 l_error_code NUMBER;
1010 l_msg_count NUMBER;
1011 l_msg_data VARCHAR2(2000);
1012
1013 l_debug NUMBER;
1014 l_progress VARCHAR2(10);
1015 l_module_name VARCHAR2(30);
1016
1017 BEGIN
1018
1019 --{{
1020 --********** PROCEDURE maintain_rsv_cancel_asn *********}}
1021
1022 l_debug := NVL(fnd_profile.VALUE('INV_DEBUG_TRACE'), 0);
1023 l_progress := '10';
1024 l_module_name := 'MAINTAIN_RSV_CANCEL_ASN';
1025 x_return_status := fnd_api.g_ret_sts_success;
1026
1027 IF (l_debug = 1) THEN
1028 print_debug('Entering maintain_rsv_cancel_asn...',l_module_name,4);
1029 print_debug(' p_cas_mol_rec_tb(1).po_header_id => '||p_cas_mol_rec_tb(1).po_header_id,l_module_name,4);
1030 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);
1031 print_debug(' p_cas_mol_rec_tb(1).inventory_item_id => '||p_cas_mol_rec_tb(1).inventory_item_id,l_module_name,4);
1032 print_debug(' p_cas_mol_rec_tb(1).organization_id => '||p_cas_mol_rec_tb(1).organization_id,l_module_name,4);
1033 print_debug(' p_cas_mol_rec_tb(1).primary_qty => '||p_cas_mol_rec_tb(1).primary_qty,l_module_name,4);
1034 print_debug(' p_cas_mol_rec_tb(1).shipment_line_id => '||p_cas_mol_rec_tb(1).shipment_line_id,l_module_name,4);
1035 print_debug(' p_cas_mol_rec_tb(1).project_id => '||p_cas_mol_rec_tb(1).project_id,l_module_name,4);
1036 print_debug(' p_cas_mol_rec_tb(1).task_id => '||p_cas_mol_rec_tb(1).task_id,l_module_name,4);
1037 END IF;
1038
1039 --{{
1040 --Test 1) Cancelling an ASN that can be matched to a reservation whose original
1041 -- source type is 'ASN'
1042 -- 2) Cancelling an ASN that can be matched to a reservation who original
1043 -- source type is not 'ASN' (it would probably be PO)
1044 -- 3) Cancelling an ASN that does to match to any reservation}}
1045
1046 l_rsv_query_rec.supply_source_type_id := g_source_type_asn;
1047 l_rsv_query_rec.supply_source_header_id := p_cas_mol_rec_tb(1).po_header_id;
1048 l_rsv_query_rec.supply_source_line_id := p_cas_mol_rec_tb(1).po_line_location_id;
1049 l_rsv_query_rec.supply_source_line_detail := p_cas_mol_rec_tb(1).shipment_line_id;
1050 l_rsv_query_rec.inventory_item_id := p_cas_mol_rec_tb(1).inventory_item_id;
1051 l_rsv_query_rec.organization_id := p_cas_mol_rec_tb(1).organization_id;
1052
1053 IF (p_cas_mol_rec_tb(1).project_id IS NOT NULL) THEN
1054 l_rsv_query_rec.project_id := p_cas_mol_rec_tb(1).project_id;
1055 IF (p_cas_mol_rec_tb(1).task_id IS NOT NULL) THEN
1056 l_rsv_query_rec.task_id := p_cas_mol_rec_tb(1).task_id;
1057 END IF;
1058 END IF ;
1059
1060 l_progress := '@@@';
1061 BEGIN
1062 SELECT 1
1063 INTO l_dummy
1064 FROM rcv_shipment_lines
1065 WHERE shipment_line_id = p_cas_mol_rec_tb(1).shipment_line_id
1066 FOR UPDATE NOWAIT;
1067 EXCEPTION
1068 WHEN OTHERS THEN
1069 IF (l_debug = 1) THEN
1070 print_debug('Some other exception occurred!',l_module_name,4);
1071 print_debug('SQLCODE:'||SQLCODE||' SQLERRM:'||SQLERRM,l_module_name,4);
1072 END IF;
1073 l_progress := '@@@';
1074 RAISE fnd_api.g_exc_unexpected_error;
1075 END;
1076 l_progress := '@@@';
1077
1078 IF (l_debug = 1) THEN
1079 print_debug('Calling query_reservation:',l_module_name,4);
1080 END IF;
1081
1082 l_progress := '###';
1083 query_reservation
1084 (p_query_input => l_rsv_query_rec
1085 ,p_sort_by_req_date => g_query_demand_ship_date_asc
1086 ,x_rsv_results => l_rsv_results_tbl
1087 ,x_return_status => l_return_status
1088 );
1089 l_progress := '###';
1090
1091 IF (l_return_status <> fnd_api.g_ret_sts_success) THEN
1092 l_progress := '@@@';
1093 RAISE fnd_api.g_exc_unexpected_error;
1094 END IF;
1095
1096 IF (l_debug = 1) THEN
1097 print_debug('l_rsv_results_tbl.COUNT = ' ||l_rsv_results_tbl.COUNT,l_module_name,4);
1098 END IF;
1099
1100 FOR i IN 1..l_rsv_results_tbl.COUNT LOOP
1101 IF (l_debug = 1) THEN
1102 print_debug(' i:'||i||
1103 ' rsv_id:'||l_rsv_results_tbl(i).reservation_id||
1104 ' prim_qty:'||l_rsv_results_tbl(i).primary_reservation_quantity||
1105 ' rsv_qty:'||l_rsv_results_tbl(i).reservation_quantity||
1106 ' uom_code:'||l_rsv_results_tbl(i).reservation_uom_code||
1107 ' orig_supply_src_type_id:'||l_rsv_results_tbl(i).orig_supply_source_type_id||
1108 ' demand_src_line_detail:'||l_rsv_results_tbl(i).demand_source_line_detail||
1109 ' ext_src_code:'||l_rsv_results_tbl(i).external_source_code||
1110 ' orig_supply_src_code:'||l_rsv_results_tbl(i).orig_supply_source_type_id
1111 ,l_module_name,4);
1112 END IF;
1113
1114 IF (l_rsv_results_tbl(i).orig_supply_source_type_id = g_source_type_asn) THEN
1115
1116 IF (l_debug = 1) THEN
1117 print_debug('Calling delete_reservation...',l_module_name,4);
1118 END IF;
1119
1120
1121 delete_reservation
1122 (p_rsv_rec => l_rsv_results_tbl(i)
1123 ,x_return_status => l_return_status
1124 );
1125
1126 IF (l_debug = 1) THEN
1127 print_debug('Returned from delete_reservation',l_module_name,4);
1128 print_debug('x_return_status: '||l_return_status,l_module_name,4);
1129 END IF;
1130
1131 IF (l_return_status <> fnd_api.g_ret_sts_success) THEN
1132 IF (l_debug = 1) THEN
1133 print_debug('SQLCODE:'||SQLCODE||' SQLERRM:'||SQLERRM,l_module_name,4);
1134 print_debug('Raising Exception!!!',l_module_name,4);
1135 END IF;
1136 l_progress := '@@@';
1137 RAISE fnd_api.g_exc_unexpected_error;
1138 END IF;
1139 ELSE --IF this is a manual rsv transferred from PO
1140 l_rsv_update_rec := l_rsv_results_tbl(i);
1141 l_rsv_update_rec.supply_source_type_id := g_source_type_po;
1142 l_rsv_update_rec.supply_source_line_detail := NULL;
1143
1144 IF (l_debug = 1) THEN
1145 print_debug('Calling transfer_reservation...',l_module_name,4);
1146 END IF;
1147
1148 transfer_reservation
1149 (p_original_rsv_rec => l_rsv_results_tbl(i)
1150 ,p_to_rsv_rec => l_rsv_update_rec
1151 ,x_new_rsv_id => l_reservation_id
1152 ,x_return_status => l_return_status);
1153
1154 IF (l_debug = 1) THEN
1155 print_debug('Returned from transfer_reservation',l_module_name,4);
1156 print_debug('x_return_status: '||l_return_status,l_module_name,4);
1157 END IF;
1158
1159 IF (l_return_status <> fnd_api.g_ret_sts_success) THEN
1160 l_progress := '@@@';
1161 RAISE fnd_api.g_exc_unexpected_error;
1162 END IF;
1163
1164 IF (l_debug = 1) THEN
1165 print_debug('l_reservation_id: '||l_reservation_id,l_module_name,4);
1166 END IF;
1167 END IF;--END l_rsv_results_tbl(i).external_source_code = 'XDOCK'
1168 END LOOP;
1169
1170 IF (l_debug = 1) THEN
1171 print_debug('Exitting maintain_rsv_cancel_asn with the following values:',l_module_name,4);
1172 print_debug('x_return_status => '||x_return_status,l_module_name,4);
1173 print_debug('x_msg_count => '||x_msg_count,l_module_name,4);
1174 print_debug('x_msg_data => '||x_msg_data,l_module_name,4);
1175 END IF;
1176
1177 --{{
1178 --********** END PROCEDURE maintain_rsv_cancel_asn *********}}
1179
1180 EXCEPTION
1181 WHEN OTHERS THEN
1182 IF (l_debug = 1) THEN
1183 print_debug('Exception occurred at progress: '||l_progress,l_module_name,4);
1184 print_debug('SQLCODE:'||SQLCODE||' SQLERRM:'||SQLERRM,l_module_name,4);
1185 END IF;
1186 x_return_status := fnd_api.g_ret_sts_error;
1187 END maintain_rsv_cancel_asn;
1188
1189 PROCEDURE maintain_rsv_receive
1190 (x_return_status OUT NOCOPY VARCHAR2
1191 ,x_msg_count OUT NOCOPY NUMBER
1192 ,x_msg_data OUT NOCOPY VARCHAR2
1193 ,p_cas_mol_rec_tb IN inv_rcv_integration_pvt.cas_mol_rec_tb_tp
1194 ,x_cas_mol_rec_tb OUT nocopy inv_rcv_integration_pvt.cas_mol_rec_tb_tp
1195 ) IS
1196 l_rsv_query_rec inv_reservation_global.mtl_reservation_rec_type;
1197 l_rsv_results_tbl inv_reservation_global.mtl_reservation_tbl_type;
1198 l_rsv_update_rec inv_reservation_global.mtl_reservation_rec_type;
1199 l_shipment_header_id NUMBER;
1200 l_requisition_header_id NUMBER;
1201 l_rsv_results_count NUMBER;
1202 l_remaining_prim_qty NUMBER;
1203 l_new_rsv_id NUMBER;
1204 l_new_wdd_id NUMBER;
1205 l_dummy NUMBER;
1206 l_primary_qty NUMBER;
1207
1208 l_return_status VARCHAR2(1);
1209 l_error_code NUMBER;
1210 l_msg_count NUMBER;
1211 l_msg_data VARCHAR2(2000);
1212
1213 l_debug NUMBER;
1214 l_progress VARCHAR2(10);
1215 l_module_name VARCHAR2(30);
1216
1217 BEGIN
1218
1219 --{{
1220 --********** PROCEDURE maintain_rsv_receive *********}}
1221
1222 l_debug := NVL(fnd_profile.VALUE('INV_DEBUG_TRACE'), 0);
1223 l_progress := '10';
1224 l_module_name := 'MAINTAIN_RSV_RECEIVE';
1225 x_return_status := fnd_api.g_ret_sts_success;
1226
1227 IF (l_debug = 1) THEN
1228 print_debug('Entering maintain_rsv_receive...',l_module_name,4);
1229 print_debug(' p_cas_mol_rec_tb(1).inventory_item_id => '||p_cas_mol_rec_tb(1).inventory_item_id,l_module_name,4);
1230 print_debug(' p_cas_mol_rec_tb(1).organization_id => '||p_cas_mol_rec_tb(1).organization_id,l_module_name,4);
1231 print_debug(' p_cas_mol_rec_tb(1).primary_qty => '||p_cas_mol_rec_tb(1).primary_qty,l_module_name,4);
1232 print_debug(' p_cas_mol_rec_tb(1).po_header_id => '||p_cas_mol_rec_tb(1).po_header_id,l_module_name,4);
1233 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);
1234 print_debug(' p_cas_mol_rec_tb(1).shipment_line_id => '||p_cas_mol_rec_tb(1).shipment_line_id,l_module_name,4);
1235 print_debug(' p_cas_mol_rec_tb(1).requisition_line_id => '||p_cas_mol_rec_tb(1).requisition_line_id,l_module_name,4);
1236 print_debug(' p_cas_mol_rec_tb(1).auto_transact_code => '||p_cas_mol_rec_tb(1).auto_transact_code,l_module_name,4);
1237 print_debug(' p_cas_mol_rec_tb(1).project_id => '||p_cas_mol_rec_tb(1).project_id,l_module_name,4);
1238 print_debug(' p_cas_mol_rec_tb(1).task_id => '||p_cas_mol_rec_tb(1).task_id,l_module_name,4);
1239 END IF;
1240
1241 l_primary_qty := abs(p_cas_mol_rec_tb(1).primary_qty);
1242
1243 --1.0 Set up query criteria
1244
1245 --{{
1246 --Test ASN recept and PO receipt. Make sure that ASN receipt
1247 --would not pick up PO reservation, and vice versa }}
1248 IF p_cas_mol_rec_tb(1).po_line_location_id IS NOT NULL THEN
1249 IF p_cas_mol_rec_tb(1).asn_line_flag = 'Y' THEN
1250 IF (l_debug = 1) THEN
1251 print_debug('This is an ASN receipt',l_module_name,4);
1252 END IF;
1253
1254 l_rsv_query_rec.supply_source_type_id := g_source_type_asn;
1255 l_rsv_query_rec.supply_source_header_id := p_cas_mol_rec_tb(1).po_header_id;
1256 l_rsv_query_rec.supply_source_line_id := p_cas_mol_rec_tb(1).po_line_location_id;
1257 l_rsv_query_rec.supply_source_line_detail := p_cas_mol_rec_tb(1).shipment_line_id;
1258
1259 l_progress := '###';
1260 BEGIN
1261 SELECT 1
1262 INTO l_dummy
1263 FROM rcv_shipment_lines
1264 WHERE shipment_line_id = p_cas_mol_rec_tb(1).shipment_line_id
1265 FOR UPDATE NOWAIT;
1266 EXCEPTION
1267 WHEN OTHERS THEN
1268 IF (l_debug = 1) THEN
1269 print_debug('Some other exception occurred! Raising Exception!',l_module_name,4);
1270 print_debug('SQLCODE:'||SQLCODE||' SQLERRM:'||SQLERRM,l_module_name,4);
1271 END IF;
1272 l_progress := '###';
1273 RAISE fnd_api.g_exc_unexpected_error;
1274 END;
1275 l_progress := '###';
1276 ELSE --asn_line_flag = 'Y' THEN
1277 IF (l_debug = 1) THEN
1278 print_debug('This is a PO receipt',l_module_name,4);
1279 END IF;
1280
1281 l_rsv_query_rec.supply_source_type_id := g_source_type_po;
1282 l_rsv_query_rec.supply_source_header_id := p_cas_mol_rec_tb(1).po_header_id;
1283 l_rsv_query_rec.supply_source_line_id := p_cas_mol_rec_tb(1).po_line_location_id;
1284
1285 l_progress := '###';
1286 BEGIN
1287 SELECT 1
1288 INTO l_dummy
1289 FROM po_line_locations_all
1290 WHERE line_location_id = p_cas_mol_rec_tb(1).po_line_location_id
1291 FOR UPDATE NOWAIT;
1292 EXCEPTION
1293 WHEN OTHERS THEN
1294 IF (l_debug = 1) THEN
1295 print_debug('Some other exception occurred! Raising Exception!',l_module_name,4);
1296 print_debug('SQLCODE:'||SQLCODE||' SQLERRM:'||SQLERRM,l_module_name,4);
1297 END IF;
1298 l_progress := '###';
1299 RAISE fnd_api.g_exc_unexpected_error;
1300 END;
1301 l_progress := '###';
1302 END IF;
1303
1304 --{{
1305 --Test REQ receipt }}
1306 ELSIF p_cas_mol_rec_tb(1).requisition_line_id IS NOT NULL THEN -- INTREQ
1307 l_progress := '###';
1308 BEGIN
1309 SELECT requisition_header_id
1310 INTO l_requisition_header_id
1311 FROM po_requisition_lines_all
1312 WHERE requisition_line_id = p_cas_mol_rec_tb(1).requisition_line_id
1313 FOR UPDATE NOWAIT;
1314
1315 l_progress := '###';
1316 SELECT 1
1317 INTO l_dummy
1318 FROM rcv_shipment_lines
1319 WHERE requisition_line_id = p_cas_mol_rec_tb(1).requisition_line_id
1320 AND shipment_line_id = p_cas_mol_rec_tb(1).shipment_line_id
1321
1322 FOR UPDATE NOWAIT;
1323 EXCEPTION
1324 WHEN OTHERS THEN
1325 IF (l_debug = 1) THEN
1326 print_debug('Some other exception occurred! Raising Exception!',l_module_name,4);
1327 print_debug('SQLCODE:'||SQLCODE||' SQLERRM:'||SQLERRM,l_module_name,4);
1328 END IF;
1329 RAISE fnd_api.g_exc_unexpected_error;
1330 END;
1331 l_progress := '###';
1332
1333 l_rsv_query_rec.supply_source_type_id := g_source_type_internal_req;
1334 l_rsv_query_rec.supply_source_header_id := l_requisition_header_id;
1335 l_rsv_query_rec.supply_source_line_id := p_cas_mol_rec_tb(1).requisition_line_id;
1336
1337 --{{
1338 --Test Intrasit Shipment receipt. Also test receipt of INTREQ
1339 --through the intrasit Shipment option}}
1340 ELSIF p_cas_mol_rec_tb(1).shipment_line_id IS NOT NULL THEN --INTSHIP
1341
1342 l_progress := '###';
1343 BEGIN
1344 SELECT shipment_header_id
1345 INTO l_shipment_header_id
1346 FROM rcv_shipment_lines
1347 WHERE shipment_line_id = p_cas_mol_rec_tb(1).shipment_line_id
1348 FOR UPDATE NOWAIT;
1349 EXCEPTION
1350 WHEN OTHERS THEN
1351 IF (l_debug = 1) THEN
1352 print_debug('Some other exception occurred! Raising Exception!',l_module_name,4);
1353 print_debug('SQLCODE:'||SQLCODE||' SQLERRM:'||SQLERRM,l_module_name,4);
1354 END IF;
1355 l_progress := '###';
1356 RAISE fnd_api.g_exc_unexpected_error;
1357 END;
1358 l_progress := '###';
1359
1360 l_rsv_query_rec.supply_source_type_id := g_source_type_in_transit;
1361 l_rsv_query_rec.supply_source_header_id := l_shipment_header_id;
1362 l_rsv_query_rec.supply_source_line_id := p_cas_mol_rec_tb(1).shipment_line_id;
1363
1364 --{{
1365 --Test RMA receipt}}
1366 ELSE --RMA
1367 IF (l_debug = 1) THEN
1368 print_debug('This is an RMA receipt. No need to query reservations',l_module_name,4);
1369 END IF;
1370
1371 l_progress := '###';
1372 set_mol_wdd_tbl(p_cas_mol_rec_tb(1),
1373 x_cas_mol_rec_tb,
1374 l_primary_qty,
1375 NULL,
1376 NULL);
1377 l_progress := '###';
1378
1379 RETURN;
1380 END IF;
1381
1382 l_rsv_query_rec.inventory_item_id := p_cas_mol_rec_tb(1).inventory_item_id;
1383 l_rsv_query_rec.organization_id := p_cas_mol_rec_tb(1).organization_id;
1384
1385 IF (p_cas_mol_rec_tb(1).project_id IS NOT NULL) THEN
1386 l_rsv_query_rec.project_id := p_cas_mol_rec_tb(1).project_id;
1387 IF (p_cas_mol_rec_tb(1).task_id IS NOT NULL) THEN
1388 l_rsv_query_rec.task_id := p_cas_mol_rec_tb(1).task_id;
1389 END IF;
1390 END IF ;
1391
1392 --2.0 Query reservation
1393 IF (l_debug = 1) THEN
1394 print_debug('Calling query_reservation:',l_module_name,4);
1395 END IF;
1396
1397 l_progress := '###';
1398 query_reservation
1399 (p_query_input => l_rsv_query_rec
1400 ,p_sort_by_req_date => g_query_demand_ship_date_asc
1401 ,x_rsv_results => l_rsv_results_tbl
1402 ,x_return_status => l_return_status
1403 );
1404 l_progress := '###';
1405
1406 IF (l_return_status <> fnd_api.g_ret_sts_success) THEN
1407 l_progress := '@@@';
1408 RAISE fnd_api.g_exc_unexpected_error;
1409 END IF;
1410
1411 --3.0 Process reservations
1412 l_remaining_prim_qty := l_primary_qty;
1413
1414 IF (l_debug = 1) THEN
1415 print_debug('l_remaining_prim_qty = ' ||l_remaining_prim_qty,l_module_name,4);
1416 print_debug('l_rsv_results_tbl.COUNT = ' ||l_rsv_results_tbl.COUNT,l_module_name,4);
1417 END IF;
1418
1419
1420 --{{
1421 --Create multiple reservations for the same document and item.
1422 --Make sure that the results returned are in the correct order according
1423 --to the demand ship date}}
1424 FOR i IN 1..l_rsv_results_tbl.COUNT LOOP
1425 EXIT WHEN l_remaining_prim_qty <= 0;
1426
1427 IF (l_debug = 1) THEN
1428 print_debug('l_remaining_prim_qty:'||l_remaining_prim_qty||
1429 ' i:'||i||
1430 ' rsv_id:'||l_rsv_results_tbl(i).reservation_id||
1431 ' prim_qty:'||l_rsv_results_tbl(i).primary_reservation_quantity||
1432 ' rsv_qty:'||l_rsv_results_tbl(i).reservation_quantity||
1433 ' uom_code:'||l_rsv_results_tbl(i).reservation_uom_code||
1434 ' orig_supply_src_type_id:'||l_rsv_results_tbl(i).orig_supply_source_type_id||
1435 ' demand_src_line_detail:'||l_rsv_results_tbl(i).demand_source_line_detail||
1436 ' ext_src_code:'||l_rsv_results_tbl(i).external_source_code
1437 ,l_module_name,4);
1438 END IF;
1439
1440 l_rsv_update_rec := l_rsv_results_tbl(i);
1441
1442 --MANEESH: For direct receipt and the case where reservation is
1443 --modifyed BY pegging engine, delete the reservation, which should
1444 --also update WDD accordingly
1445 --{{
1446 --Create a reservations, have it modified by pegging engine, then
1447 --Perform direct receipt. Make sure that reservations are
1448 --relieved/deleted appropriately }}
1449 IF Nvl(p_cas_mol_rec_tb(1).auto_transact_code,'RECEIVE') = 'DELIVER' AND l_rsv_results_tbl(i).external_source_code = 'XDOCK' THEN
1450 IF (l_rsv_results_tbl(i).primary_reservation_quantity >= l_remaining_prim_qty) THEN
1451 l_rsv_update_rec.primary_reservation_quantity := l_remaining_prim_qty;
1452 l_remaining_prim_qty := 0;
1453 ELSE
1454 l_rsv_update_rec.primary_reservation_quantity := l_rsv_results_tbl(i).primary_reservation_quantity;
1455 l_remaining_prim_qty := l_remaining_prim_qty - l_rsv_results_tbl(i).primary_reservation_quantity;
1456 END IF;
1457
1458 IF (l_debug = 1) THEN
1459 print_debug('Calling relieve_reservation...',l_module_name,4);
1460 print_debug(' l_rsv_update_rec.primary_reservation_quantity => '||l_rsv_update_rec.primary_reservation_quantity,l_module_name,4);
1461 END IF;
1462
1463 --Relieve/delete reservation
1464 l_progress := '###';
1465 relieve_reservation
1466 (p_rsv_rec => l_rsv_results_tbl(i)
1467 ,p_prim_qty_to_relieve => l_rsv_update_rec.primary_reservation_quantity
1468 ,x_return_status => l_return_status
1469 );
1470 l_progress := '###';
1471
1472 IF (l_debug = 1) THEN
1473 print_debug('Returned from delete_reservation',l_module_name,4);
1474 print_debug('x_return_status: '||l_return_status,l_module_name,4);
1475 END IF;
1476
1477 IF (l_return_status <> fnd_api.g_ret_sts_success) THEN
1478 l_progress := '@@@';
1479 RAISE fnd_api.g_exc_unexpected_error;
1480 END IF;
1481
1482
1483 --{{
1484 --Test the following cases:
1485 --1. standard Receipt that will match to a manual reservations
1486 --2. standard receipt that will match to a xdock reservations
1487 --3. direct receipt that will match to a manual reservations whose
1488 -- ext_src_code is not crossdock
1489 --4. direct receipt that will match to a xdock reservation whose
1490 -- ext_src_code is not crossdock}}
1491 ELSE --Reservation is not modified by pegging engine
1492
1493 --{{
1494 --Test cases in which reservations/WDD need to be split}}
1495 IF (l_rsv_results_tbl(i).primary_reservation_quantity > l_remaining_prim_qty) THEN
1496 -- Reservation has more than enough to satisfy remaining qty, so split
1497
1498 IF (l_debug = 1) THEN
1499 print_debug('l_rsv_results_tbl(i).primary_reservation_quantity > l_remaining_prim_qty',
1500 l_module_name,4);
1501 END IF;
1502
1503 l_rsv_update_rec.primary_reservation_quantity := l_remaining_prim_qty;
1504 l_rsv_update_rec.reservation_quantity := inv_rcv_cache.convert_qty(p_cas_mol_rec_tb(1).inventory_item_id
1505 ,l_remaining_prim_qty
1506 ,l_rsv_results_tbl(i).primary_uom_code
1507 ,l_rsv_results_tbl(i).reservation_uom_code);
1508
1509 IF (l_rsv_results_tbl(i).demand_source_line_detail IS NOT NULL) THEN
1510 IF (l_debug = 1) THEN
1511 print_debug('Calling split_wdd...',l_module_name,4);
1512 END IF;
1513
1514 l_progress := '@@@';
1515 split_wdd
1516 (x_return_status => l_return_status
1517 ,x_msg_count => l_msg_count
1518 ,x_msg_data => l_msg_data
1519 ,x_new_wdd_id => l_new_wdd_id
1520 ,p_wdd_id => l_rsv_results_tbl(i).demand_source_line_detail
1521 ,p_new_mol_id => NULL
1522 ,p_qty_to_splt => l_remaining_prim_qty);
1523 l_progress := '@@@';
1524
1525 IF (l_debug = 1) THEN
1526 print_debug('Returned from split_wdd',l_module_name,4);
1527 print_debug('x_return_status: '||l_return_status,l_module_name,4);
1528 END IF;
1529
1530 IF (l_return_status <> fnd_api.g_ret_sts_success) THEN
1531 l_progress := '@@@';
1532 RAISE fnd_api.g_exc_unexpected_error;
1533 END IF;
1534
1535 IF (l_debug = 1) THEN
1536 print_debug('l_new_wdd_id: '||l_new_wdd_id,l_module_name,4);
1537 END IF;
1538
1539 --Depending on the routing, I will transfer/update released
1540 --status later
1541 l_rsv_update_rec.demand_source_line_detail := l_new_wdd_id;
1542
1543 END IF;
1544
1545 l_remaining_prim_qty := 0;
1546
1547 ELSE --l_rsv_results_tbl(i).primary_reservation_quantity <= l_remaining_prim_qty THEN
1548 IF (l_debug = 1) THEN
1549 print_debug('l_rsv_results_tbl(i).primary_reservation_quantity <= l_remaining_prim_qty',
1550 l_module_name,4);
1551 END IF;
1552 l_remaining_prim_qty := l_remaining_prim_qty - l_rsv_results_tbl(i).primary_reservation_quantity;
1553 END IF; --END IF (l_rsv_results_tbl(i).primary_reservation_quantity > l_remaining_prim_qty)
1554
1555 IF Nvl(p_cas_mol_rec_tb(1).auto_transact_code,'RECEIVE') = 'DELIVER' THEN
1556 --Direct Receipt and the reservation has not been modified by the
1557 --xdock pegging engine
1558 l_rsv_update_rec.supply_source_type_id := g_source_type_inv;
1559 l_rsv_update_rec.demand_source_line_detail := NULL;
1560 ELSE
1561 --Standard/Inspection routing receipt
1562 l_rsv_update_rec.supply_source_type_id := g_source_type_rcv;
1563 END IF;
1564
1565 --Null out supply source info
1566 l_rsv_update_rec.supply_source_header_id := NULL;
1567 l_rsv_update_rec.supply_source_line_id := NULL;
1568 l_rsv_update_rec.supply_source_line_detail := NULL;
1569
1570 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
1571
1572 IF (l_debug = 1) THEN
1573 print_debug('Calling update_wdd...',l_module_name,4);
1574 END IF;
1575
1576 l_progress := '@@@';
1577 update_wdd
1578 (x_return_status => l_return_status
1579 ,x_msg_count => l_msg_count
1580 ,x_msg_data => l_msg_data
1581 ,p_wdd_id => l_rsv_update_rec.demand_source_line_detail
1582 ,p_released_status => 'R' --Ready to released
1583 ,p_mol_id => NULL
1584 );
1585 l_progress := '@@@';
1586
1587 IF (l_return_status <> fnd_api.g_ret_sts_success) THEN
1588 l_progress := '@@@';
1589 RAISE fnd_api.g_exc_unexpected_error;
1590 END IF;
1591
1592 l_rsv_update_rec.demand_source_line_detail := NULL;
1593 END IF;
1594
1595 IF (l_debug = 1) THEN
1596 print_debug('Calling transfer_reservation...',l_module_name,4);
1597 END IF;
1598
1599 l_progress := '@@@';
1600 transfer_reservation
1601 (p_original_rsv_rec => l_rsv_results_tbl(i)
1602 ,p_to_rsv_rec => l_rsv_update_rec
1603 ,x_new_rsv_id => l_new_rsv_id
1604 ,x_return_status => l_return_status);
1605 l_progress := '@@@';
1606
1607 IF (l_return_status <> fnd_api.g_ret_sts_success) THEN
1608 l_progress := '@@@';
1609 RAISE fnd_api.g_exc_unexpected_error;
1610 END IF;
1611
1612 IF (l_debug = 1) THEN
1613 print_debug('l_new_rsv_id: '||l_new_rsv_id,l_module_name,4);
1614 END IF;
1615 END IF;--IF p_cas_mol_rec_tb(1).auto_transact_code = 'DELIVER' AND l_rsv_results_tbl(i).external_source_code = 'XDOCK' THEN
1616
1617 --For standard/inspection routing receipt, populate x_cas_mol_rec_tb
1618 --so that maintain_mo_cons API can create MOL with the specific WDD
1619 IF (Nvl(p_cas_mol_rec_tb(1).auto_transact_code,'RECEIVE') <> 'DELIVER') THEN
1620 IF l_rsv_update_rec.demand_source_type_id IN (g_source_type_internal_ord,g_source_type_oe) THEN
1621 set_mol_wdd_tbl(p_cas_mol_rec_tb(1),
1622 x_cas_mol_rec_tb,
1623 l_rsv_update_rec.primary_reservation_quantity,
1624 l_rsv_update_rec.demand_source_line_detail,
1625 1
1626 );
1627 /* Bug 5244500 : If source type is INV, then cross dock type should be set
1628 to null.*/
1629 ELSIF (l_rsv_update_rec.demand_source_type_id = g_source_type_inv ) THEN
1630 set_mol_wdd_tbl(p_cas_mol_rec_tb(1),
1631 x_cas_mol_rec_tb,
1632 l_rsv_update_rec.primary_reservation_quantity,
1633 l_rsv_update_rec.demand_source_line_detail,
1634 null
1635 );
1636 ELSE
1637 set_mol_wdd_tbl(p_cas_mol_rec_tb(1),
1638 x_cas_mol_rec_tb,
1639 l_rsv_update_rec.primary_reservation_quantity,
1640 l_rsv_update_rec.demand_source_line_detail,
1641 2
1642 );
1643 END IF;
1644 END IF;
1645 END LOOP;
1646
1647 --For standard/inspection routing receipt, populate x_cas_mol_rec_tb
1648 --so that MOL will be created for the the quantity with no reservation
1649
1650 --??? For + Corr of Receive, auto_transact_code is passed NULL. Assume
1651 --that NULL is same as RECEIVE. Is it the right assumption???
1652 IF (Nvl(p_cas_mol_rec_tb(1).auto_transact_code,'RECEIVE') <> 'DELIVER' AND l_remaining_prim_qty > 0) THEN
1653 set_mol_wdd_tbl(p_cas_mol_rec_tb(1),
1654 x_cas_mol_rec_tb,
1655 l_remaining_prim_qty,
1656 NULL,
1657 NULL);
1658 END IF;
1659
1660 IF (l_debug = 1) THEN
1661 print_debug('Exitting maintain_rsv_receive with the following values:',l_module_name,4);
1662 print_debug('x_return_status => '||x_return_status,l_module_name,4);
1663 print_debug('x_msg_count => '||x_msg_count,l_module_name,4);
1664 print_debug('x_msg_data => '||x_msg_data,l_module_name,4);
1665 END IF;
1666
1667 --{{
1668 --********** END PROCEDURE maintain_rsv_receive *********}}
1669
1670 EXCEPTION
1671 WHEN OTHERS THEN
1672 x_return_status := fnd_api.g_ret_sts_error;
1673 IF (l_debug = 1) THEN
1674 print_debug('Exception occurred at progress: '||l_progress,l_module_name,4);
1675 print_debug('SQLCODE:'||SQLCODE||' SQLERRM:'||SQLERRM,l_module_name,4);
1676 print_debug('Exiting maintain_rsv_receive with error: '||l_progress,l_module_name,4);
1677 END IF;
1678 END maintain_rsv_receive;
1679
1680 PROCEDURE maintain_rsv_deliver
1681 (x_return_status OUT NOCOPY VARCHAR2
1682 ,x_msg_count OUT NOCOPY NUMBER
1683 ,x_msg_data OUT NOCOPY VARCHAR2
1684 ,p_cas_mol_rec_tb IN inv_rcv_integration_pvt.cas_mol_rec_tb_tp
1685 ) IS
1686 l_rsv_reduce_rec inv_reservation_global.mtl_maintain_rsv_rec_type;
1687 l_rsv_query_rec inv_reservation_global.mtl_reservation_rec_type;
1688 l_rsv_results_tbl inv_reservation_global.mtl_reservation_tbl_type;
1689 l_rsv_update_rec inv_reservation_global.mtl_reservation_rec_type;
1690 l_remaining_prim_qty NUMBER;
1691 l_new_rsv_id NUMBER;
1692 l_new_wdd_id NUMBER;
1693 l_dummy NUMBER;
1694 l_doc_type NUMBER;
1695
1696 l_return_status VARCHAR2(1);
1697 l_error_code NUMBER;
1698 l_msg_count NUMBER;
1699 l_msg_data VARCHAR2(2000);
1700
1701 l_debug NUMBER;
1702 l_progress VARCHAR2(10);
1703 l_module_name VARCHAR2(30);
1704
1705 BEGIN
1706
1707 --{{
1708 --********** PROCEDURE maintain_rsv_deliver *********}}
1709
1710 l_debug := NVL(fnd_profile.VALUE('INV_DEBUG_TRACE'), 0);
1711 l_progress := '10';
1712 l_module_name := 'MAINTAIN_RSV_DELIVER';
1713 x_return_status := fnd_api.g_ret_sts_success;
1714
1715 IF (l_debug = 1) THEN
1716 print_debug('Entering maintain_rsv_deliver...',l_module_name,4);
1717 print_debug(' p_cas_mol_rec_tb(1).line_id => '||p_cas_mol_rec_tb(1).line_id,l_module_name,4);
1718 print_debug(' p_cas_mol_rec_tb(1).inventory_item_id => '||p_cas_mol_rec_tb(1).inventory_item_id,l_module_name,4);
1719 print_debug(' p_cas_mol_rec_tb(1).organization_id => '||p_cas_mol_rec_tb(1).organization_id,l_module_name,4);
1720 print_debug(' p_cas_mol_rec_tb(1).primary_qty => '||p_cas_mol_rec_tb(1).primary_qty,l_module_name,4);
1721 print_debug(' p_cas_mol_rec_tb(1).project_id => '||p_cas_mol_rec_tb(1).project_id,l_module_name,4);
1722 print_debug(' p_cas_mol_rec_tb(1).task_id => '||p_cas_mol_rec_tb(1).task_id,l_module_name,4);
1723 print_debug(' p_cas_mol_rec_tb(1).bdd_id => '||p_cas_mol_rec_tb(1).backorder_delivery_detail_id,l_module_name,4);
1724 print_debug(' p_cas_mol_rec_tb(1).crossdock_type => '||p_cas_mol_rec_tb(1).crossdock_type,l_module_name,4);
1725 print_debug(' p_cas_mol_rec_tb(1).wip_supply_type => '||p_cas_mol_rec_tb(1).wip_supply_type,l_module_name,4);
1726 print_debug(' p_cas_mol_rec_tb(1).subinventory_code => '||p_cas_mol_rec_tb(1).subinventory_code,l_module_name,4);
1727 print_debug(' p_cas_mol_rec_tb(1).locator_id => '||p_cas_mol_rec_tb(1).locator_id,l_module_name,4);
1728 print_debug(' p_cas_mol_rec_tb(1).lot_number => '||p_cas_mol_rec_tb(1).lot_number,l_module_name,4);
1729 print_debug(' p_cas_mol_rec_tb(1).item_revision => '||p_cas_mol_rec_tb(1).item_revision,l_module_name,4);
1730 END IF;
1731
1732 --{{
1733 --Try to lock the MOL row from SQLPLUS, and see if transaction would
1734 --fail at this point}}
1735 l_progress := '@@@';
1736 BEGIN
1737 SELECT 1
1738 INTO l_dummy
1739 FROM mtl_txn_request_lines
1740 WHERE line_id = p_cas_mol_rec_tb(1).line_id
1741 FOR UPDATE NOWAIT;
1742 EXCEPTION
1743 WHEN OTHERS THEN
1744 IF (l_debug = 1) THEN
1745 print_debug('Some other exception occurred!',l_module_name,4);
1746 print_debug('SQLCODE:'||SQLCODE||' SQLERRM:'||SQLERRM,l_module_name,4);
1747 END IF;
1748 l_progress := '@@@';
1749 RAISE fnd_api.g_exc_unexpected_error;
1750 END;
1751
1752 l_progress := '@@@';
1753
1754 --{{
1755 --Test WIP Push xdock}}
1756 IF p_cas_mol_rec_tb(1).crossdock_type = 2 AND p_cas_mol_rec_tb(1).wip_supply_type = 1 THEN
1757 IF (l_debug = 1) THEN
1758 print_debug('No Need to Handle Reservations for crossdock to a WIP push demand',l_module_name,4);
1759 END IF;
1760
1761 --Bug 5249929 - No need to call reduce reservations for WIP demands
1762 --as we do not support wip as a demand in reservations
1763
1764 --l_rsv_reduce_rec.action := 1; --Supply
1765 --l_rsv_reduce_rec.inventory_item_id := p_cas_mol_rec_tb(1).inventory_item_id;
1766 --l_rsv_reduce_rec.organization_id := p_cas_mol_rec_tb(1).organization_id;
1767 --l_rsv_reduce_rec.supply_source_type_id := g_source_type_rcv;
1768 --l_rsv_reduce_rec.demand_source_type_id := g_source_type_wip;
1769 --l_rsv_reduce_rec.demand_source_header_id := p_cas_mol_rec_tb(1).backorder_delivery_detail_id;--???
1770
1771 --???
1772 /*IF (p_cas_mol_rec_tb(1).project_id IS NOT NULL) THEN
1773 l_rsv_reduce_rec.project_id := p_cas_mol_rec_tb(1).project_id;
1774 IF (p_cas_mol_rec_tb(1).task_id IS NOT NULL) THEN
1775 l_rsv_reduce_rec.task_id := p_cas_mol_rec_tb(1).task_id;
1776 END IF;
1777 END IF ;
1778 */
1779
1780 --{{
1781 --Make sure that the reservations are reduced appropriately}}
1782 --l_progress := '@@@';
1783 --reduce_reservation
1784 --(p_mtl_rsv_rec => l_rsv_reduce_rec
1785 --,x_return_status => l_return_status
1786 --);
1787 --l_progress := '@@@';
1788
1789 --IF (l_debug = 1) THEN
1790 -- print_debug('Returned from update_wdd',l_module_name,4);
1791 -- print_debug('x_return_status: '||l_return_status,l_module_name,4);
1792 --END IF;
1793
1794 --IF (l_return_status <> fnd_api.g_ret_sts_success) THEN
1795 -- l_progress := '@@@';
1796 -- RAISE fnd_api.g_exc_unexpected_error;
1797 --END IF;
1798
1799 --{{
1800 --Test SO Xdock, WIP Pull or non-crossdock cases}}
1801 ELSE
1802 IF (l_debug = 1) THEN
1803 print_debug('SO Xdock/WIP-Pull/Non-xdock',l_module_name,4);
1804 END IF;
1805
1806 l_rsv_query_rec.inventory_item_id := p_cas_mol_rec_tb(1).inventory_item_id;
1807 l_rsv_query_rec.organization_id := p_cas_mol_rec_tb(1).organization_id;
1808 l_rsv_query_rec.supply_source_type_id := g_source_type_rcv;
1809
1810 IF (p_cas_mol_rec_tb(1).project_id IS NOT NULL) THEN
1811 l_rsv_query_rec.project_id := p_cas_mol_rec_tb(1).project_id;
1812 IF (p_cas_mol_rec_tb(1).task_id IS NOT NULL) THEN
1813 l_rsv_query_rec.task_id := p_cas_mol_rec_tb(1).task_id;
1814 END IF;
1815 END IF ;
1816
1817 IF p_cas_mol_rec_tb(1).crossdock_type = 2 THEN --WIP
1818 --{{
1819 --Test deliver of WIP crossdock PULL. Make sure that mr.backorder_delivery_detail_id corresponds
1820 --to mol.backorder_delivery_detail_id }}
1821 IF (l_debug = 1) THEN
1822 print_debug('This is a WIP pull xdock',4);
1823 END IF;
1824 l_rsv_query_rec.demand_source_type_id := g_source_type_wip;
1825 l_rsv_query_rec.demand_source_header_id := p_cas_mol_rec_tb(1).backorder_delivery_detail_id;--???
1826 ELSIF p_cas_mol_rec_tb(1).crossdock_type = 1
1827 AND p_cas_mol_rec_tb(1).backorder_delivery_detail_id IS NOT NULL THEN --SO
1828 IF (l_debug = 1) THEN
1829 print_debug('This is a SO xdock',4);
1830 END IF;
1831
1832 --{{
1833 --Test deliver of a SO (for both Internal Order and Sale order.
1834 --When processing results for SO xdock. Make sure that
1835 --1) There is only 1 result returned
1836 --2) The appropriate wdd are updated to STAGED status
1837 --3) The appropriate reservation is transferred to INVENTORY (Check source_type columns)}}
1838
1839 BEGIN
1840 SELECT Nvl(source_document_type_id, -1)
1841 INTO l_doc_type
1842 FROM wsh_delivery_details
1843 WHERE delivery_detail_id = p_cas_mol_rec_tb(1).backorder_delivery_detail_id;
1844 EXCEPTION
1845 WHEN OTHERS THEN
1846 IF (l_debug = 1) THEN
1847 print_debug('Error retrieving doc type for SO',l_module_name,4);
1848 END IF;
1849 RAISE fnd_api.g_exc_unexpected_error;
1850 END;
1851
1852 IF (l_debug = 1) THEN
1853 print_debug('l_doc_type:'||l_doc_type,4);
1854 END IF;
1855
1856 IF l_doc_type = 10 THEN
1857 l_rsv_query_rec.demand_source_type_id := g_source_type_internal_ord;
1858 ELSE
1859 l_rsv_query_rec.demand_source_type_id := g_source_type_oe;
1860 END if;
1861 l_rsv_query_rec.demand_source_line_detail := p_cas_mol_rec_tb(1).backorder_delivery_detail_id;
1862 ELSE -- Non-crossdock cases
1863 IF (l_debug = 1) THEN
1864 print_debug('This is a non-xdock case',4);
1865 END IF;
1866
1867 --{{
1868 --Deliver of non-crossdock cases. Make sure that only manual
1869 --reservations are picked up.
1870 --When processing results for non-crossdock cases. Make sure that
1871 --reservations are transfered to Inventory properly. }}
1872 l_rsv_query_rec.demand_source_line_detail := NULL;
1873 END IF;
1874
1875 IF (l_debug = 1) THEN
1876 print_debug('Calling query_reservations...',l_module_name,4);
1877 END IF;
1878
1879 l_progress := '@@@';
1880 query_reservation
1881 (p_query_input => l_rsv_query_rec
1882 ,p_sort_by_req_date => g_query_demand_ship_date_asc
1883 ,x_rsv_results => l_rsv_results_tbl
1884 ,x_return_status => l_return_status
1885 );
1886 l_progress := '@@@';
1887
1888 IF (l_return_status <> fnd_api.g_ret_sts_success) THEN
1889 l_progress := '@@@';
1890 RAISE fnd_api.g_exc_unexpected_error;
1891 END IF;
1892
1893 l_remaining_prim_qty := p_cas_mol_rec_tb(1).primary_qty;
1894
1895 IF (l_debug = 1) THEN
1896 print_debug('l_remaining_prim_qty = ' ||l_remaining_prim_qty,l_module_name,4);
1897 print_debug('l_rsv_results_tbl.COUNT = ' ||l_rsv_results_tbl.COUNT,l_module_name,4);
1898 END IF;
1899
1900 --{{
1901 --LOOP results. Make sure the results are correct according to
1902 --the criteria entered (checking ordering also}}
1903 FOR i IN 1..l_rsv_results_tbl.COUNT LOOP
1904 EXIT WHEN l_remaining_prim_qty <= 0;
1905
1906 --{{
1907 --Make sure that l_remaining_prim_qty are updated properly}}
1908 IF (l_debug = 1) THEN
1909 print_debug('l_remaining_prim_qty:'||l_remaining_prim_qty||
1910 ' i:'||i||
1911 ' rsv_id:'||l_rsv_results_tbl(i).reservation_id||
1912 ' prim_qty:'||l_rsv_results_tbl(i).primary_reservation_quantity||
1913 ' rsv_qty:'||l_rsv_results_tbl(i).reservation_quantity||
1914 ' uom_code:'||l_rsv_results_tbl(i).reservation_uom_code||
1915 ' orig_supply_src_type_id:'||l_rsv_results_tbl(i).orig_supply_source_type_id||
1916 ' demand_src_line_detail:'||l_rsv_results_tbl(i).demand_source_line_detail||
1917 ' ext_src_code:'||l_rsv_results_tbl(i).external_source_code
1918 ,l_module_name,4);
1919 END IF;
1920
1921 l_rsv_update_rec := l_rsv_results_tbl(i);
1922
1923 IF (l_rsv_results_tbl(i).primary_reservation_quantity > l_remaining_prim_qty) THEN
1924 IF (l_debug = 1) THEN
1925 print_debug('l_rsv_results_tbl(i).primary_reservation_quantity > l_remaining_prim_qty',
1926 l_module_name,4);
1927 END IF;
1928
1929 l_rsv_update_rec.primary_reservation_quantity := l_remaining_prim_qty;
1930 l_rsv_update_rec.reservation_quantity := inv_rcv_cache.convert_qty
1931 (p_cas_mol_rec_tb(1).inventory_item_id
1932 ,l_remaining_prim_qty
1933 ,l_rsv_results_tbl(i).primary_uom_code
1934 ,l_rsv_results_tbl(i).reservation_uom_code);
1935
1936 IF (l_rsv_results_tbl(i).demand_source_line_detail IS NOT NULL) THEN
1937 IF (l_debug = 1) THEN
1938 print_debug('Calling split_wdd',l_module_name,4);
1939 END IF;
1940
1941 l_progress := '@@@';
1942 split_wdd
1943 (x_return_status => l_return_status
1944 ,x_msg_count => l_msg_count
1945 ,x_msg_data => l_msg_data
1946 ,x_new_wdd_id => l_new_wdd_id
1947 ,p_wdd_id => l_rsv_results_tbl(i).demand_source_line_detail
1948 ,p_new_mol_id => NULL
1949 ,p_qty_to_splt => l_remaining_prim_qty);
1950 l_progress := '@@@';
1951
1952 IF (l_debug = 1) THEN
1953 print_debug('Returned from split_wdd',l_module_name,4);
1954 print_debug('l_return_status =>'||l_return_status,l_module_name,4);
1955 END IF;
1956
1957 IF (l_return_status <> fnd_api.g_ret_sts_success) THEN
1958 l_progress := '@@@';
1959 RAISE fnd_api.g_exc_unexpected_error;
1960 END IF;
1961
1962 l_rsv_update_rec.demand_source_line_detail := l_new_wdd_id;
1963 END IF;
1964
1965 l_remaining_prim_qty := 0;
1966
1967 ELSE --l_rsv_results_tbl(i).primary_reservation_quantity <= l_remaining_prim_qty
1968 IF (l_debug = 1) THEN
1969 print_debug('l_rsv_results_tbl(i).primary_reservation_quantity <= l_remaining_prim_qty',
1970 l_module_name,4);
1971 END IF;
1972 l_remaining_prim_qty := l_remaining_prim_qty - l_rsv_results_tbl(i).primary_reservation_quantity;
1973 END IF;
1974
1975 -- Lei's complete_crossdock API will update wdd
1976
1977 l_rsv_update_rec.supply_source_type_id := g_source_type_inv;
1978 l_rsv_update_rec.supply_source_header_id := NULL;
1979 l_rsv_update_rec.supply_source_line_id := NULL;
1980 l_rsv_update_rec.supply_source_line_detail := NULL;
1981 l_rsv_update_rec.demand_source_line_detail := NULL;
1982
1983 --6/30/05: Also update lpn/sub/loc info for xdock scenario
1984 IF l_rsv_results_tbl(i).demand_source_line_detail IS NOT NULL THEN
1985 l_rsv_update_rec.lpn_id := p_cas_mol_rec_tb(1).lpn_id;
1986 l_rsv_update_rec.subinventory_code := p_cas_mol_rec_tb(1).subinventory_code;
1987 l_rsv_update_rec.locator_id := p_cas_mol_rec_tb(1).locator_id;
1988 l_rsv_update_rec.lot_number := p_cas_mol_rec_tb(1).lot_number;
1989 l_rsv_update_rec.revision := p_cas_mol_rec_tb(1).item_revision;
1990 l_rsv_update_rec.staged_flag := 'Y';
1991 l_rsv_update_rec.crossdock_flag := NULL;
1992 END IF;
1993
1994 IF (l_debug = 1) THEN
1995 print_debug('Calling transfer_reservation...',l_module_name,4);
1996 END IF;
1997
1998 l_progress := '@@@';
1999 transfer_reservation
2000 (p_original_rsv_rec => l_rsv_results_tbl(i)
2001 ,p_to_rsv_rec => l_rsv_update_rec
2002 ,x_new_rsv_id => l_new_rsv_id
2003 ,x_return_status => l_return_status);
2004 l_progress := '@@@';
2005
2006 IF (l_debug = 1) THEN
2007 print_debug('Returned from transfer_reservation',l_module_name,4);
2008 print_debug('x_return_status: '||l_return_status,l_module_name,4);
2009 END IF;
2010
2011 IF (l_return_status <> fnd_api.g_ret_sts_success) THEN
2012 l_progress := '@@@';
2013 RAISE fnd_api.g_exc_unexpected_error;
2014 END IF;
2015
2016 IF (l_debug = 1) THEN
2017 print_debug('l_new_rsv_id: '||l_new_rsv_id,l_module_name,4);
2018 END IF;
2019 END LOOP;
2020 END IF;--IF p_cas_mol_rec_tb(1).crossdock_type = 2 AND p_cas_mol_rec_tb(1).wip_supply_type = 1 THEN
2021
2022 IF (l_debug = 1) THEN
2023 print_debug('Exitting maintain_rsv_deliver with the following values:',l_module_name,4);
2024 print_debug('x_return_status => '||x_return_status,l_module_name,4);
2025 print_debug('x_msg_count => '||x_msg_count,l_module_name,4);
2026 print_debug('x_msg_data => '||x_msg_data,l_module_name,4);
2027 END IF;
2028
2029 --{{
2030 --********** END PROCEDURE maintain_rsv_deliver *********}}
2031 EXCEPTION
2032 WHEN OTHERS THEN
2033 IF (l_debug = 1) THEN
2034 print_debug('Exception occurred at progress: '||l_progress,l_module_name,4);
2035 print_debug('SQLCODE:'||SQLCODE||' SQLERRM:'||SQLERRM,l_module_name,4);
2036 END IF;
2037 x_return_status := fnd_api.g_ret_sts_error;
2038 END maintain_rsv_deliver;
2039
2040 PROCEDURE maintain_rsv_returns
2041 (x_return_status OUT NOCOPY VARCHAR2
2042 ,x_msg_count OUT NOCOPY NUMBER
2043 ,x_msg_data OUT NOCOPY VARCHAR2
2044 ,p_cas_mol_rec_tb IN inv_rcv_integration_pvt.cas_mol_rec_tb_tp
2045 ) IS
2046 l_rsv_query_rec inv_reservation_global.mtl_reservation_rec_type;
2047 l_rsv_update_rec inv_reservation_global.mtl_reservation_rec_type;
2048 l_remaining_prim_qty NUMBER;
2049 l_shipment_header_id NUMBER;
2050 l_requisition_header_id NUMBER;
2051 l_supply_source_type_id NUMBER;
2052 l_supply_source_header_id NUMBER;
2053 l_supply_source_line_id NUMBER;
2054 l_supply_source_line_detail NUMBER;
2055 l_avail_qty_to_reserve NUMBER;
2056 l_avail_qty NUMBER;
2057 l_available_rcv_qty NUMBER;
2058 l_qty_with_no_wdd NUMBER;
2059 l_deal_with_reservation NUMBER;
2060 l_new_rsv_id NUMBER;
2061 TYPE varchar25_tb IS TABLE OF VARCHAR2(25) INDEX BY BINARY_INTEGER;
2062 l_pt_txn_types varchar25_tb;
2063 l_inspect_status NUMBER;
2064 l_loose_qty_to_splt NUMBER;
2065 l_receipt_source_code VARCHAR2(25);
2066 l_mo_line_id NUMBER;
2067 l_mo_split_tb inv_rcv_integration_apis.mo_in_tb_tp;
2068 l_dummy NUMBER;
2069 l_qty_to_close NUMBER;
2070 l_tmp_line_id NUMBER;
2071 l_primary_qty NUMBER;
2072
2073 l_return_status VARCHAR2(1);
2074 l_error_code NUMBER;
2075 l_msg_count NUMBER;
2076 l_msg_data VARCHAR2(2000);
2077
2078 l_debug NUMBER;
2079 l_progress VARCHAR2(10);
2080 l_module_name VARCHAR2(30);
2081
2082 BEGIN
2083
2084 l_debug := NVL(fnd_profile.VALUE('INV_DEBUG_TRACE'), 0);
2085 l_progress := '10';
2086 l_module_name := 'MAINTAIN_RSV_RETURNS';
2087
2088 --For - Corr, primary_qty will come as a negative number. Take the abs
2089 --value here
2090
2091 l_primary_qty := abs(p_cas_mol_rec_tb(1).primary_qty);
2092
2093 IF (l_debug = 1) THEN
2094 print_debug('Entering maintain_rsv_returns...',l_module_name,4);
2095 print_debug(' p_cas_mol_rec_tb(1).organization_id => '||p_cas_mol_rec_tb(1).organization_id,l_module_name,4);
2096 print_debug(' p_cas_mol_rec_tb(1).inventory_item_id => '||p_cas_mol_rec_tb(1).inventory_item_id,l_module_name,4);
2097 print_debug(' p_cas_mol_rec_tb(1).lpn_id => '||p_cas_mol_rec_tb(1).lpn_id,l_module_name,4);
2098 print_debug(' p_cas_mol_rec_tb(1).from_subinventory_code => '||p_cas_mol_rec_tb(1).from_subinventory_code,l_module_name,4);
2099 print_debug(' p_cas_mol_rec_tb(1).from_locator_id => '||p_cas_mol_rec_tb(1).from_locator_id,l_module_name,4);
2100 print_debug(' p_cas_mol_rec_tb(1).item_revision => '||p_cas_mol_rec_tb(1).item_revision,l_module_name,4);
2101 print_debug(' p_cas_mol_rec_tb(1).lot_number => '||p_cas_mol_rec_tb(1).lot_number,l_module_name,4);
2102 print_debug(' p_cas_mol_rec_tb(1).primary_qty => '||l_primary_qty,l_module_name,4);
2103 print_debug(' p_cas_mol_rec_tb(1).project_id => '||p_cas_mol_rec_tb(1).project_id,l_module_name,4);
2104 print_debug(' p_cas_mol_rec_tb(1).task_id => '||p_cas_mol_rec_tb(1).task_id,l_module_name,4);
2105 print_debug(' p_cas_mol_rec_tb(1).shipment_line_id => '||p_cas_mol_rec_tb(1).shipment_line_id,l_module_name,4);
2106 print_debug(' p_cas_mol_rec_tb(1).inspection_status => '||p_cas_mol_rec_tb(1).inspection_status,l_module_name,4);
2107 print_debug(' p_cas_mol_rec_tb(1).asn_line_flag => '||p_cas_mol_rec_tb(1).asn_line_flag,l_module_name,4);
2108 END IF;
2109
2110 x_return_status := fnd_api.g_ret_sts_success;
2111
2112 --2.0 Query availability in RCV
2113 IF (l_debug = 1) THEN
2114 print_debug('Calling inv_reservation_avail_pvt.available_supply_to_reserve',l_module_name,4);
2115 END IF;
2116
2117 inv_reservation_avail_pvt.available_supply_to_reserve
2118 (x_return_status => l_return_status
2119 , x_msg_count => l_msg_count
2120 , x_msg_data => l_msg_data
2121 , p_organization_id => p_cas_mol_rec_tb(1).organization_id
2122 , p_item_id => p_cas_mol_rec_tb(1).inventory_item_id
2123 , p_revision => p_cas_mol_rec_tb(1).item_revision
2124 , p_lot_number => p_cas_mol_rec_tb(1).lot_number
2125 , p_supply_source_type_id => g_source_type_rcv
2126 , p_supply_source_header_id => NULL
2127 , p_supply_source_line_id => NULL
2128 , p_supply_source_line_detail => NULL
2129 , p_project_id => p_cas_mol_rec_tb(1).project_id
2130 , p_task_id => p_cas_mol_rec_tb(1).task_id
2131 , x_qty_available_to_reserve => l_avail_qty_to_reserve
2132 , x_qty_available => l_avail_qty
2133 );
2134
2135 IF (l_debug = 1) THEN
2136 print_debug('After calling inv_reservation_avail_pvt.available_supply_to_reserve',l_module_name,4);
2137 print_debug('l_avail_qty_to_reserve: ' || l_avail_qty_to_reserve,l_module_name,4);
2138 END IF;
2139
2140 l_available_rcv_qty := l_avail_qty_to_reserve + l_primary_qty;
2141
2142 IF (l_debug = 1) THEN
2143 print_debug('l_available_rcv_qty: ' || l_available_rcv_qty,l_module_name,4);
2144 END IF;
2145
2146 --3.0 Determine whether there is a need to deal with reservations
2147 IF l_available_rcv_qty > l_primary_qty THEN
2148
2149 --3.1 If there is enough total available quantity, we still need
2150 -- to check if the LPN has available MOL quantity that has no wdd stamped
2151 BEGIN
2152 SELECT SUM(primary_quantity)
2153 INTO l_qty_with_no_wdd
2154 FROM mtl_txn_request_lines mtrl
2155 WHERE nvl(mtrl.lpn_id,-999)=nvl(p_cas_mol_rec_tb(1).lpn_id,-999)
2156 AND nvl(mtrl.from_subinventory_code,'&&&')=nvl(p_cas_mol_rec_tb(1).from_subinventory_code,'&&&')--???
2157 AND nvl(mtrl.from_locator_id,-999)=nvl(p_cas_mol_rec_tb(1).from_locator_id,-999)--???
2158 AND mtrl.organization_id = p_cas_mol_rec_tb(1).organization_id
2159 AND mtrl.inventory_item_id = p_cas_mol_rec_tb(1).inventory_item_id
2160 AND Nvl(mtrl.lot_number,'*&*') = Nvl(p_cas_mol_rec_tb(1).lot_number,'*&*')
2161 AND nvl(mtrl.revision,'&&&') = nvl(p_cas_mol_rec_tb(1).item_revision,'&&&')
2162 AND mtrl.line_status = 7
2163 AND Nvl(mtrl.inspection_status,-1) = Nvl(p_cas_mol_rec_tb(1).inspection_status,-1)
2164 AND (NVL(mtrl.project_id, -999) = NVL(p_cas_mol_rec_tb(1).project_id, -999)
2165 or p_cas_mol_rec_tb(1).project_id is null) -- Bug 6618890
2166 AND (NVL(mtrl.task_id, -999) = NVL(p_cas_mol_rec_tb(1).task_id, -999)
2167 or p_cas_mol_rec_tb(1).task_id is null) -- Bug 6618890
2168 AND (mtrl.quantity - Nvl(mtrl.quantity_delivered,0)) > 0
2169 AND mtrl.backorder_delivery_detail_id IS NULL
2170 AND exists (SELECT 1
2171 FROM mtl_txn_request_headers mtrh
2172 WHERE mtrh.move_order_type = inv_globals.g_move_order_put_away
2173 AND mtrh.header_id = mtrl.header_id);
2174 EXCEPTION
2175 WHEN OTHERS THEN
2176 RAISE fnd_api.g_exc_unexpected_error;
2177 END;
2178
2179 IF (l_debug = 1) THEN
2180 print_debug('l_qty_qith_no_wdd : ' || l_qty_with_no_wdd ,l_module_name,4);
2181 END IF;
2182
2183 IF (l_qty_with_no_wdd >= l_primary_qty) THEN
2184 --3.1.1 Can simply split MOL. No need to deal with reservations
2185 l_deal_with_reservation := 0;
2186 ELSE
2187 --3.1.2 Must deal with wdd. So need to deal with reservations
2188 l_deal_with_reservation := 1;
2189 END IF;
2190 ELSE
2191 --3.2 total available qty is less the txn qty. So must deal with reservations
2192 l_deal_with_reservation := 1;
2193 END IF;--END IF l_available_rcv_qty > l_primary_qty THEN
2194
2195 IF (l_debug = 1) THEN
2196 print_debug('L_DEAL_WITH_RESERVATION := ' || l_deal_with_reservation ,l_module_name,4);
2197 END IF;
2198
2199 l_remaining_prim_qty := 0;
2200 l_loose_qty_to_splt := 0;
2201
2202 IF (l_deal_with_reservation = 0) THEN
2203 --4.1 Jump directly to split mo
2204 l_loose_qty_to_splt := l_primary_qty;
2205 IF (l_debug = 1) THEN
2206 print_debug('LOOSE QTY TO SPLI = ' || l_loose_qty_to_splt ,l_module_name,4);
2207 END IF;
2208 ELSE -- l_deal_with_reservation = 1
2209
2210 --4.2.2 Query reservations
2211 l_remaining_prim_qty := l_primary_qty;
2212 l_loose_qty_to_splt := 0;
2213
2214 IF (l_debug = 1) THEN
2215 print_debug('Before Opening the cusror on l_rsv_results ' ,l_module_name,4);
2216 END IF;
2217
2218 FOR l_rsv_results IN (SELECT
2219 reservation_id
2220 , primary_uom_code
2221 , primary_reservation_quantity
2222 , reservation_uom_code
2223 , demand_source_line_detail
2224 FROM mtl_reservations mr
2225 WHERE mr.supply_source_type_id = inv_reservation_global.g_source_type_rcv
2226 AND mr.organization_id = p_cas_mol_rec_tb(1).organization_id
2227 AND mr.inventory_item_id = p_cas_mol_rec_tb(1).inventory_item_id
2228 AND ((mr.demand_source_line_detail IS NOT NULL
2229 AND mr.demand_source_line_detail
2230 IN (SELECT mol.backorder_delivery_detail_id
2231 FROM mtl_txn_request_lines mol
2232 WHERE mol.organization_id = p_cas_mol_rec_tb(1).organization_id
2233 AND mol.inventory_item_id = p_cas_mol_rec_tb(1).inventory_item_id
2234 AND NVL(mol.revision, '@@@') = NVL(p_cas_mol_rec_tb(1).item_revision, '@@@')
2235 AND (NVL(mol.project_id, -999) = NVL(p_cas_mol_rec_tb(1).project_id, -999)
2236 or p_cas_mol_rec_tb(1).project_id is null) -- Bug 6618890
2237 AND (NVL(mol.task_id, -999) = NVL(p_cas_mol_rec_tb(1).task_id, -999)
2238 or p_cas_mol_rec_tb(1).task_id is null) -- Bug 6618890
2239 AND MOL.CROSSDOCK_TYPE = 1 --RESERVATION COULD BE FOR WIP ALSO???
2240 AND NVL(mol.lpn_id, -999) = NVL(p_cas_mol_rec_tb(1).lpn_id, -999)
2241 AND nvl(mol.inspection_status,-1) = Nvl(p_cas_mol_rec_tb(1).inspection_status,-1)
2242 AND Nvl(mol.lot_number,'&^+') = Nvl(p_cas_mol_rec_tb(1).lot_number,'&^+')
2243 AND mol.line_status = 7
2244 AND (mol.quantity-Nvl(mol.quantity_delivered,0))>0
2245 AND exists (SELECT 1
2246 FROM mtl_txn_request_headers mtrh
2247 WHERE mtrh.move_order_type = inv_globals.g_move_order_put_away
2248 AND mtrh.header_id = mol.header_id)
2249 )) OR
2250 (mr.demand_source_line_detail IS NULL
2251 AND exists (SELECT mol.backorder_delivery_detail_id
2252 FROM mtl_txn_request_lines mol
2253 WHERE mol.organization_id = p_cas_mol_rec_tb(1).organization_id
2254 AND mol.inventory_item_id = p_cas_mol_rec_tb(1).inventory_item_id
2255 AND NVL(mol.revision, '@@@') = NVL(p_cas_mol_rec_tb(1).item_revision, '@@@')
2256 AND (NVL(mol.project_id, -999) = NVL(p_cas_mol_rec_tb(1).project_id, -999)
2257 or p_cas_mol_rec_tb(1).project_id is null) -- Bug 6618890
2258 AND (NVL(mol.task_id, -999) = NVL(p_cas_mol_rec_tb(1).task_id, -999)
2259 or p_cas_mol_rec_tb(1).task_id is null) -- Bug 6618890
2260 AND NVL(mol.lpn_id, -999) = NVL(p_cas_mol_rec_tb(1).lpn_id, -999)
2261 AND mol.backorder_delivery_detail_id IS NULL
2262 AND nvl(mol.inspection_status,-1) = Nvl(p_cas_mol_rec_tb(1).inspection_status,-1)
2263 AND Nvl(mol.lot_number,'&#+') = Nvl(p_cas_mol_rec_tb(1).lot_number,'&#+')
2264 AND mol.line_status = 7
2265 AND (mol.quantity-Nvl(mol.quantity_delivered,0))>0
2266 AND exists (SELECT 1
2267 FROM mtl_txn_request_headers mtrh
2268 WHERE mtrh.move_order_type = inv_globals.g_move_order_put_away
2269 AND mtrh.header_id = mol.header_id)
2270 )
2271 )
2272 )
2273 ORDER BY NVL(MR.DEMAND_SHIP_DATE, REQUIREMENT_DATE)) LOOP
2274 EXIT WHEN l_remaining_prim_qty <= 0;
2275
2276 IF (l_debug = 1) THEN
2277 print_debug('Looping through l_rsv_results ' ,l_module_name,4);
2278 END IF;
2279
2280 IF l_rsv_results.demand_source_line_detail IS NOT NULL THEN
2281
2282 IF (l_debug = 1) THEN
2283 print_debug('l_rsv_results.demand_source_line_detail := '|| l_rsv_results.demand_source_line_detail ,l_module_name,4);
2284 END IF;
2285
2286 BEGIN
2287 SELECT line_id
2288 INTO l_mo_line_id
2289 FROM mtl_txn_request_lines mol
2290 WHERE backorder_delivery_detail_id = l_rsv_results.demand_source_line_detail
2291 AND mol.organization_id = p_cas_mol_rec_tb(1).organization_id
2292 AND mol.inventory_item_id = p_cas_mol_rec_tb(1).inventory_item_id
2293 AND NVL(mol.revision, '@@@') = NVL(p_cas_mol_rec_tb(1).item_revision, '@@@')
2294 AND (NVL(mol.project_id, -999) = NVL(p_cas_mol_rec_tb(1).project_id , -999)
2295 or p_cas_mol_rec_tb(1).project_id is null) -- Bug 6618890
2296 AND (NVL(mol.task_id, -999) = NVL(p_cas_mol_rec_tb(1).task_id, -999)
2297 or p_cas_mol_rec_tb(1).task_id is null) -- Bug 6618890
2298 AND mol.crossdock_type = 1
2299 AND NVL(mol.lpn_id, -999) = NVL(p_cas_mol_rec_tb(1).lpn_id, -999)
2300 AND exists (SELECT 1
2301 FROM mtl_txn_request_headers mtrh
2302 WHERE mtrh.move_order_type = inv_globals.g_move_order_put_away
2303 AND mtrh.header_id = mol.header_id);
2304 EXCEPTION
2305 WHEN OTHERS THEN
2306 IF (l_debug = 1) THEN
2307 print_debug('Some other exception occurred!',l_module_name,4);
2308 print_debug('SQLCODE:'||SQLCODE||' SQLERRM:'||SQLERRM,l_module_name,4);
2309 END IF;
2310 RAISE fnd_api.g_exc_unexpected_error;
2311 END;
2312
2313 IF (l_rsv_results.primary_reservation_quantity > l_remaining_prim_qty) THEN
2314 IF (l_debug = 1) THEN
2315 print_debug('l_rsv_results.primary_reservation_quantity > l_remaining_prim_qty',
2316 l_module_name,4);
2317 print_debug('Calling split_mo...',l_module_name,4);
2318 END IF;
2319
2320 l_mo_split_tb(1).prim_qty := l_remaining_prim_qty;
2321 l_mo_split_tb(1).line_id := NULL;
2322
2323 inv_rcv_integration_apis.split_mo
2324 (p_orig_mol_id => l_mo_line_id,
2325 p_mo_splt_tb => l_mo_split_tb,
2326 x_return_status => l_return_status,
2327 x_msg_count => l_msg_count,
2328 x_msg_data => l_msg_data);
2329
2330 IF (l_debug = 1) THEN
2331 print_debug('Returned from split_mo',l_module_name,4);
2332 print_debug('x_return_status: '||l_return_status,l_module_name,4);
2333 END IF;
2334
2335 IF (l_return_status <> fnd_api.g_ret_sts_success) THEN
2336 IF (l_debug = 1) THEN
2337 print_debug('SQLCODE:'||SQLCODE||' SQLERRM:'||SQLERRM,l_module_name,4);
2338 print_debug('Raising Exception!!!',l_module_name,4);
2339 END IF;
2340 l_progress := '@@@';
2341 RAISE fnd_api.g_exc_unexpected_error;
2342 END IF;
2343
2344 l_tmp_line_id := l_mo_split_tb(1).line_id;
2345
2346 /* Lei's cancel operation will take care of this
2347 l_rsv_query_rec.reservation_id := l_mo_split_tb(1).reservation_id;
2348 l_rsv_query_rec.demand_source_line_detail := l_mo_split_tb(1).wdd_id;
2349
2350 IF (l_debug = 1) THEN
2351 print_debug('Calling relieve_reservation...',l_module_name,4);
2352 END IF;
2353
2354 relieve_reservation
2355 (p_rsv_rec => l_rsv_query_rec
2356 ,p_prim_qty_to_relieve => l_remaining_prim_qty
2357 ,x_return_status => l_return_status
2358 );
2359
2360 IF (l_debug = 1) THEN
2361 print_debug('Returned from relieve_reservation',l_module_name,4);
2362 print_debug('x_return_status: '||l_return_status,l_module_name,4);
2363 END IF;
2364
2365 IF (l_return_status <> fnd_api.g_ret_sts_success) THEN
2366 IF (l_debug = 1) THEN
2367 print_debug('SQLCODE:'||SQLCODE||' SQLERRM:'||SQLERRM,l_module_name,4);
2368 print_debug('Raising Exception!!!',l_module_name,4);
2369 END IF;
2370 l_progress := '@@@';
2371 RAISE fnd_api.g_exc_unexpected_error;
2372 END IF;
2373
2374 IF (l_debug = 1) THEN
2375 print_debug('Calling update_wdd...',l_module_name,4);
2376 END IF;
2377
2378 update_wdd
2379 (x_return_status => l_return_status
2380 ,x_msg_count => l_msg_count
2381 ,x_msg_data => l_msg_data
2382 ,p_wdd_id => l_mo_split_tb(1).wdd_id
2383 ,p_released_status => 'R'
2384 ,p_mol_id => NULL
2385 );
2386
2387 IF (l_debug = 1) THEN
2388 print_debug('Returned from update_wdd',l_module_name,4);
2389 print_debug('x_return_status: '||l_return_status,l_module_name,4);
2390 END IF;
2391
2392 IF (l_return_status <> fnd_api.g_ret_sts_success) THEN
2393 IF (l_debug = 1) THEN
2394 print_debug('x_msg_count: '||l_msg_count,l_module_name,4);
2395 print_debug('x_msg_data: '||l_msg_data,l_module_name,4);
2396 print_debug('SQLCODE:'||SQLCODE||' SQLERRM:'||SQLERRM,l_module_name,4);
2397 print_debug('Raising Exception!!!',l_module_name,4);
2398 END IF;
2399 l_progress := '@@@';
2400 RAISE fnd_api.g_exc_unexpected_error;
2401 END IF;
2402 */
2403
2404
2405 l_remaining_prim_qty := 0;
2406
2407 ELSE
2408 /* Lei's cancel operation will take care of this
2409 l_rsv_query_rec.reservation_id := l_rsv_results.reservation_id;
2410
2411 IF (l_debug = 1) THEN
2412 print_debug('l_rsv_results(i).primary_reservation_quantity <= l_remaining_prim_qty',
2413 l_module_name,4);
2414 print_debug('Calling delete_reservation...',l_module_name,4);
2415 END IF;
2416
2417 delete_reservation
2418 (p_rsv_rec => l_rsv_query_rec
2419 ,x_return_status => l_return_status
2420 );
2421
2422 IF (l_debug = 1) THEN
2423 print_debug('Returned from delete_reservation',l_module_name,4);
2424 print_debug('x_return_status: '||l_return_status,l_module_name,4);
2425 END IF;
2426
2427 IF (l_return_status <> fnd_api.g_ret_sts_success) THEN
2428 IF (l_debug = 1) THEN
2429 print_debug('SQLCODE:'||SQLCODE||' SQLERRM:'||SQLERRM,l_module_name,4);
2430 print_debug('Raising Exception!!!',l_module_name,4);
2431 END IF;
2432 l_progress := '@@@';
2433 RAISE fnd_api.g_exc_unexpected_error;
2434 END IF;
2435
2436 IF (l_debug = 1) THEN
2437 print_debug('Calling update_wdd...',l_module_name,4);
2438 END IF;
2439
2440 update_wdd
2441 (x_return_status => l_return_status
2442 ,x_msg_count => l_msg_count
2443 ,x_msg_data => l_msg_data
2444 ,p_wdd_id => l_rsv_results.demand_source_line_detail
2445 ,p_released_status => 'R'
2446 ,p_mol_id => NULL
2447 );
2448
2449 IF (l_debug = 1) THEN
2450 print_debug('Returned from update_wdd',l_module_name,4);
2451 print_debug('x_return_status: '||l_return_status,l_module_name,4);
2452 END IF;
2453
2454 IF (l_return_status <> fnd_api.g_ret_sts_success) THEN
2455 IF (l_debug = 1) THEN
2456 print_debug('x_msg_count: '||l_msg_count,l_module_name,4);
2457 print_debug('x_msg_data: '||l_msg_data,l_module_name,4);
2458 print_debug('SQLCODE:'||SQLCODE||' SQLERRM:'||SQLERRM,l_module_name,4);
2459 print_debug('Raising Exception!!!',l_module_name,4);
2460 END IF;
2461 l_progress := '@@@';
2462 RAISE fnd_api.g_exc_unexpected_error;
2463 END IF;
2464
2465 */
2466 l_tmp_line_id := l_mo_line_id;
2467 l_remaining_prim_qty := l_remaining_prim_qty - l_rsv_results.primary_reservation_quantity;
2468 END IF;
2469
2470 UPDATE mtl_txn_request_lines
2471 SET quantity = Nvl(quantity_delivered,0)
2472 , primary_quantity = ((primary_quantity*Nvl(quantity_delivered,0))/quantity)
2473 , quantity_detailed = Decode(quantity_detailed,NULL,quantity_detailed,quantity_delivered)
2474 -- OPMConvergence
2475 , secondary_quantity = Nvl(secondary_quantity_delivered,0)
2476 , secondary_quantity_detailed = Decode(secondary_quantity_detailed,NULL,secondary_quantity_detailed,secondary_quantity_delivered)
2477 -- OPMConvergence
2478 , line_status = 5
2479 , wms_process_flag = 1
2480 WHERE line_id = l_tmp_line_id;
2481
2482 inv_rcv_integration_pvt.call_atf_api(x_return_status => l_return_status,
2483 x_msg_data => l_msg_data,
2484 x_msg_count => l_msg_count,
2485 x_error_code => l_error_code,
2486 p_source_task_id => NULL,
2487 p_activity_type_id => 1,
2488 p_operation_type_id => NULL,
2489 p_mol_id => l_tmp_line_id,
2490 p_atf_api_name => inv_rcv_integration_pvt.g_atf_api_cancel);
2491
2492
2493 ELSE --l_rsv_result_tbl(i).demand_source_line_detail IS NULL THEN
2494
2495 IF (l_debug = 1) THEN
2496 print_debug('l_rsv_results.demand_source_line_detail is null ',l_module_name,4);
2497 END IF;
2498
2499 IF (l_rsv_results.primary_reservation_quantity > l_remaining_prim_qty) THEN
2500 l_loose_qty_to_splt := l_loose_qty_to_splt + l_remaining_prim_qty;
2501 l_rsv_update_rec.primary_reservation_quantity := l_remaining_prim_qty;
2502 l_rsv_update_rec.reservation_quantity := inv_rcv_cache.convert_qty(p_cas_mol_rec_tb(1).inventory_item_id
2503 ,l_remaining_prim_qty
2504 ,l_rsv_results.primary_uom_code
2505 ,l_rsv_results.reservation_uom_code);
2506 l_remaining_prim_qty := 0;
2507 ELSE
2508 l_remaining_prim_qty := l_remaining_prim_qty - l_rsv_results.primary_reservation_quantity;
2509 l_loose_qty_to_splt := l_loose_qty_to_splt - l_rsv_results.primary_reservation_quantity;
2510 --close entire line
2511 END IF;
2512
2513 BEGIN
2514 SELECT rsh.receipt_source_code
2515 INTO l_receipt_source_code
2516 FROM rcv_shipment_headers rsh, rcv_shipment_lines rsl
2517 WHERE rsl.shipment_line_id = p_cas_mol_rec_tb(1).shipment_line_id
2518 AND rsl.shipment_header_id = rsh.shipment_header_id;
2519 EXCEPTION
2520 WHEN no_data_found THEN
2521 IF (l_debug = 1) THEN
2522 print_debug(' NO_DATA_FOUND Exception thrown when retrieving receipt_source_cod!',l_module_name,4);
2523 RAISE fnd_api.g_exc_unexpected_error;
2524 END IF;
2525 WHEN OTHERS THEN
2526 IF (l_debug = 1) THEN
2527 print_debug(' Other exceptions thrown when retrieving receipt_source_cod!',l_module_name,4);
2528 END IF;
2529 RAISE fnd_api.g_exc_unexpected_error;
2530 END;
2531
2532 IF (l_debug = 1) THEN
2533 print_debug('l_remaining_prim_qty= '|| l_remaining_prim_qty,l_module_name,4);
2534 print_debug('l_loose_qty_to_splt = '|| l_loose_qty_to_splt ,l_module_name,4);
2535 print_debug('l_rsv_update_rec.primary_reservation_quantity = '||l_rsv_update_rec.primary_reservation_quantity ,l_module_name,4);
2536 print_debug('l_rsv_update_rec.reservation_quantity = '||l_rsv_update_rec.reservation_quantity ,l_module_name,4);
2537 print_debug('l_receipt_source_code = '||l_receipt_source_code,l_module_name,4);
2538 END IF;
2539
2540 IF (l_receipt_source_code = 'VENDOR' AND p_cas_mol_rec_tb(1).asn_line_flag = 'Y') THEN
2541 l_rsv_update_rec.supply_source_type_id := g_source_type_asn;
2542 l_rsv_update_rec.supply_source_header_id := p_cas_mol_rec_tb(1).po_header_id;
2543 l_rsv_update_rec.supply_source_line_id := p_cas_mol_rec_tb(1).po_line_location_id;
2544 l_rsv_update_rec.supply_source_line_detail := p_cas_mol_rec_tb(1).shipment_line_id;
2545 ELSIF (l_receipt_source_code = 'VENDOR' AND p_cas_mol_rec_tb(1).asn_line_flag = 'N') THEN
2546 l_rsv_update_rec.supply_source_type_id := inv_reservation_global.g_source_type_po;
2547 l_rsv_update_rec.supply_source_header_id := p_cas_mol_rec_tb(1).po_header_id;
2548 l_rsv_update_rec.supply_source_line_id := p_cas_mol_rec_tb(1).po_line_location_id;
2549 l_rsv_update_rec.supply_source_line_detail := NULL;
2550 ELSIF (l_receipt_source_code = 'INTERNAL ORDER') THEN
2551 l_rsv_update_rec.supply_source_type_id :=
2552 inv_reservation_global.g_source_type_internal_req;
2553 l_rsv_update_rec.supply_source_header_id := l_requisition_header_id;
2554 l_rsv_update_rec.supply_source_line_id := p_cas_mol_rec_tb(1).requisition_line_id;
2555 l_rsv_update_rec.supply_source_line_detail := NULL;
2556 ELSIF (l_receipt_source_code = 'INVENTORY') THEN
2557 BEGIN
2558 SELECT shipment_header_id
2559 INTO l_shipment_header_id
2560 FROM rcv_shipment_lines
2561 WHERE shipment_line_id = p_cas_mol_rec_tb(1).shipment_line_id
2562 FOR UPDATE NOWAIT;
2563 EXCEPTION
2564 WHEN OTHERS THEN
2565 IF (l_debug = 1) THEN
2566 print_debug('Some other exception occurred in getting shipment details',l_module_name,4);
2567 print_debug('SQLCODE:'||SQLCODE||' SQLERRM:'||SQLERRM,l_module_name,4);
2568 END IF;
2569 RAISE fnd_api.g_exc_unexpected_error;
2570 END;
2571 l_rsv_update_rec.supply_source_type_id := g_source_type_in_transit;
2572 l_rsv_update_rec.supply_source_header_id := l_shipment_header_id;
2573 l_rsv_update_rec.supply_source_line_id := l_shipment_header_id;
2574 l_rsv_update_rec.supply_source_line_detail := NULL;
2575 ELSIF (l_receipt_source_code = 'CUSTOMER') THEN
2576 IF (l_debug = 1) THEN
2577 print_debug('RMA lines. Should not reach here!',l_module_name,4);
2578 END IF;
2579 RAISE fnd_api.g_exc_unexpected_error;
2580 END IF;
2581
2582 IF (l_debug = 1) THEN
2583 print_debug('Calling transfer_reservation...',l_module_name,4);
2584 END IF;
2585
2586 l_rsv_query_rec.reservation_id := l_rsv_results.reservation_id;
2587 l_rsv_query_rec.organization_id := p_cas_mol_rec_tb(1).organization_id;
2588
2589 transfer_reservation
2590 (p_original_rsv_rec => l_rsv_query_rec
2591 ,p_to_rsv_rec => l_rsv_update_rec
2592 ,x_new_rsv_id => l_new_rsv_id
2593 ,x_return_status => l_return_status);
2594
2595 IF (l_debug = 1) THEN
2596 print_debug('Returned from transfer_reservation',l_module_name,4);
2597 print_debug('x_return_status: '||l_return_status,l_module_name,4);
2598 END IF;
2599
2600 IF (l_return_status <> fnd_api.g_ret_sts_success) THEN
2601 IF (l_debug = 1) THEN
2602 print_debug('x_msg_data: '||l_msg_data,l_module_name,4);
2603 print_debug('x_msg_count: '||l_msg_count,l_module_name,4);
2604 print_debug('SQLCODE:'||SQLCODE||' SQLERRM:'||SQLERRM,l_module_name,4);
2605 print_debug('Raising Exception!!!',l_module_name,4);
2606 END IF;
2607 l_progress := '@@@';
2608 RAISE fnd_api.g_exc_unexpected_error;
2609 END IF;
2610
2611 IF (l_debug = 1) THEN
2612 print_debug('l_new_rsv_id: '||l_new_rsv_id,l_module_name,4);
2613 END IF;
2614 END IF;--IF l_rsv_result_tbl(i).demand_source_line_detail IS NOT NULL THEN
2615 END LOOP;--FOR i IN 1..l_rsv_results.COUNT LOOP
2616 END IF;--IF (l_deal_with_reservation = 0) THEN
2617
2618 IF (l_debug = 1) THEN
2619 print_debug('l_loose_qty_to_splt:'||l_loose_qty_to_splt||' l_remaining_prim_qty:'||l_remaining_prim_qty,l_module_name,4);
2620 END IF;
2621
2622 IF l_loose_qty_to_splt + l_remaining_prim_qty > 0 THEN
2623
2624 IF (l_debug = 1) THEN
2625 print_debug('Before getting l_qty_to_close' ,l_module_name,4);
2626 END IF;
2627
2628 l_qty_to_close := l_loose_qty_to_splt + l_remaining_prim_qty;
2629
2630 IF (l_debug = 1) THEN
2631 print_debug('l_qty_to_close = ' || l_qty_to_close ,l_module_name,4);
2632 print_debug('Before Opening MOL cursor' ,l_module_name,4);
2633 print_debug('p_cas_mol_rec_tb(1).organization_id = '|| p_cas_mol_rec_tb(1).organization_id ,l_module_name,4);
2634 print_debug('p_cas_mol_rec_tb(1).lpn_id = '|| p_cas_mol_rec_tb(1).lpn_id ,l_module_name,4);
2635 print_debug('p_cas_mol_rec_tb(1).inventory_item_id = '|| p_cas_mol_rec_tb(1).inventory_item_id ,l_module_name,4);
2636 print_debug('p_cas_mol_rec_tb(1).item_revision = '|| p_cas_mol_rec_tb(1).item_revision ,l_module_name,4);
2637 print_debug('p_cas_mol_rec_tb(1).inspection_status = '|| p_cas_mol_rec_tb(1).inspection_status ,l_module_name,4);
2638 print_debug('p_cas_mol_rec_tb(1).project_id = '|| p_cas_mol_rec_tb(1).project_id ,l_module_name,4);
2639 print_debug('p_cas_mol_rec_tb(1).task_id = '|| p_cas_mol_rec_tb(1).task_id ,l_module_name,4);
2640 print_debug('p_cas_mol_rec_tb(1).lot_number = '|| p_cas_mol_rec_tb(1).lot_number ,l_module_name,4);
2641 END IF;
2642
2643 FOR l_mol_rec IN (SELECT mtrl.line_id
2644 , mtrl.primary_quantity
2645 FROM mtl_txn_request_lines mtrl
2646 WHERE mtrl.line_status = 7
2647 AND (mtrl.quantity-Nvl(mtrl.quantity_delivered,0)) > 0
2648 -- AND mtrl.backorder_delivery_detail_id IS NULL --Bug#6040524
2649 AND mtrl.organization_id = p_cas_mol_rec_tb(1).organization_id
2650 -- Bug 4508608
2651 -- hadling of non lpn cases are done properly
2652 -- AND mtrl.lpn_id = p_cas_mol_rec_tb(1).lpn_id
2653 AND nvl(mtrl.lpn_id, -999) = nvl(p_cas_mol_rec_tb(1).lpn_id, -999)
2654 AND mtrl.inventory_item_id = p_cas_mol_rec_tb(1).inventory_item_id
2655 AND Nvl(mtrl.revision,'%^$') = Nvl(p_cas_mol_rec_tb(1).item_revision,'%^$')
2656 AND Nvl(mtrl.inspection_status,-1) = Nvl(p_cas_mol_rec_tb(1).inspection_status,-1)
2657 AND (NVL(mtrl.project_id, -999) = NVL(p_cas_mol_rec_tb(1).project_id, -999)
2658 OR p_cas_mol_rec_tb(1).project_id IS NULL) -- Bug 6618890
2659 AND (NVL(mtrl.task_id, -999) = NVL(p_cas_mol_rec_tb(1).task_id, -999)
2660 OR p_cas_mol_rec_tb(1).task_id IS NULL) -- Bug 6618890
2661 AND Nvl(mtrl.lot_number,'&*_') = Nvl(p_cas_mol_rec_tb(1).lot_number,'&*_')
2662 AND exists (SELECT 1
2663 FROM mtl_txn_request_headers mtrh
2664 WHERE mtrh.move_order_type = inv_globals.g_move_order_put_away
2665 AND mtrh.header_id = mtrl.header_id)
2666 )
2667 LOOP
2668 IF (l_debug = 1) THEN
2669 print_debug('MOL found: '|| l_mol_rec.line_id||' QTY: '||l_mol_rec.primary_quantity,l_module_name,4);
2670 END IF;
2671
2672 IF l_qty_to_close < l_mol_rec.primary_quantity THEN
2673 IF (l_debug = 1) THEN
2674 print_debug('Calling split_mo...',l_module_name,4);
2675 END IF;
2676
2677 l_mo_split_tb(1).prim_qty := l_qty_to_close;
2678 l_mo_split_tb(1).line_id := NULL;
2679
2680 inv_rcv_integration_apis.split_mo
2681 (p_orig_mol_id => l_mol_rec.line_id,
2682 p_mo_splt_tb => l_mo_split_tb,
2683 x_return_status => l_return_status,
2684 x_msg_count => l_msg_count,
2685 x_msg_data => l_msg_data);
2686
2687 IF (l_debug = 1) THEN
2688 print_debug('Returned from split_mo',l_module_name,4);
2689 print_debug('x_return_status: '||l_return_status,l_module_name,4);
2690 END IF;
2691
2692 IF (l_return_status <> fnd_api.g_ret_sts_success) THEN
2693 IF (l_debug = 1) THEN
2694 print_debug('SQLCODE:'||SQLCODE||' SQLERRM:'||SQLERRM,l_module_name,4);
2695 print_debug('Raising Exception!!!',l_module_name,4);
2696 END IF;
2697 l_progress := '@@@';
2698 RAISE fnd_api.g_exc_unexpected_error;
2699 END IF;
2700
2701 l_tmp_line_id := l_mo_split_tb(1).line_id;
2702 l_qty_to_close := l_qty_to_close - l_mol_rec.primary_quantity;
2703 ELSE
2704 l_tmp_line_id := l_mol_rec.line_id;
2705 l_qty_to_close := 0;
2706 END IF;
2707
2708 IF (l_debug = 1) THEN
2709 print_debug('Call cancel ATF',l_module_name,9);
2710 END IF;
2711
2712 inv_rcv_integration_pvt.call_atf_api(x_return_status => l_return_status,
2713 x_msg_data => l_msg_data,
2714 x_msg_count => l_msg_count,
2715 x_error_code => l_error_code,
2716 p_source_task_id => NULL,
2717 p_activity_type_id => 1,
2718 p_operation_type_id => NULL,
2719 p_mol_id => l_tmp_line_id,
2720 p_atf_api_name => inv_rcv_integration_pvt.g_atf_api_cancel);
2721
2722 IF (l_debug = 1) THEN
2723 print_debug('Closing MOL '||l_tmp_line_id,l_module_name,9);
2724 END IF;
2725
2726 UPDATE mtl_txn_request_lines
2727 SET quantity = Nvl(quantity_delivered,0)
2728 , primary_quantity = ((primary_quantity*Nvl(quantity_delivered,0))/quantity)
2729 , quantity_detailed = Decode(quantity_detailed,NULL,quantity_detailed,quantity_delivered)
2730 -- OPMConvergence
2731 , secondary_quantity = Nvl(secondary_quantity_delivered,0)
2732 , secondary_quantity_detailed = Decode(secondary_quantity_detailed,NULL,secondary_quantity_detailed,secondary_quantity_delivered)
2733 -- OPMConvergence
2734 , line_status = 5
2735 , wms_process_flag = 1
2736 WHERE line_id = l_tmp_line_id;
2737
2738 IF (l_qty_to_close <= 0) THEN
2739 IF (l_debug = 1) THEN
2740 print_debug('Exiting from the MOL Loop' ,l_module_name,9);
2741 END IF;
2742 EXIT;
2743 END if;
2744 END LOOP;
2745
2746 IF (l_qty_to_close > 0) THEN
2747 IF (l_debug = 1) THEN
2748 print_debug('l_qty_to_close > 0. Could not find matching move order for the qty !',l_module_name,4);
2749 END IF;
2750 RAISE fnd_api.g_exc_unexpected_error;
2751 END IF;
2752 END IF;
2753
2754 IF (l_debug = 1) THEN
2755 print_debug('Exitting maintain_rsv_returns with the following values:',l_module_name,4);
2756 print_debug('x_return_status => '||x_return_status,l_module_name,4);
2757 print_debug('x_msg_count => '||x_msg_count,l_module_name,4);
2758 print_debug('x_msg_data => '||x_msg_data,l_module_name,4);
2759 END IF;
2760
2761 EXCEPTION
2762 WHEN OTHERS THEN
2763 IF (l_debug = 1) THEN
2764 print_debug('Exception occurred at progress: '||l_progress,l_module_name,4);
2765 print_debug('SQLCODE:'||SQLCODE||' SQLERRM:'||SQLERRM,l_module_name,4);
2766 END IF;
2767 x_return_status := fnd_api.g_ret_sts_error;
2768 END maintain_rsv_returns;
2769
2770 PROCEDURE split_close_mo_for_ret_corr
2771 (x_return_status OUT NOCOPY VARCHAR2
2772 ,x_msg_count OUT NOCOPY NUMBER
2773 ,x_msg_data OUT NOCOPY VARCHAR2
2774 ,p_cas_mol_tb IN inv_rcv_integration_pvt.cas_mol_rec_tb_tp
2775 ) IS
2776 CURSOR c_mol_no_mmtt (p_org_id NUMBER,
2777 p_item NUMBER,
2778 p_lpn NUMBER,
2779 p_lot VARCHAR2,
2780 p_rev VARCHAR2,
2781 p_from_sub varchar2,
2782 p_from_locator_id NUMBER,
2783 -- p_cost_group_id NUMBER, ????
2784 p_project_id NUMBER,
2785 p_task_id NUMBER,
2786 p_inspection_status NUMBER ,
2787 p_uom_code varchar2
2788 ) IS
2789 --Bug 5231114:Added the condition on transaction_source_type_id and
2790 -- transaction_action_id for the following combinations:13/12 and 4/27
2791 SELECT DISTINCT mtrl.line_id
2792 , Decode(p_uom_code,mtrl.uom_code,1,2) UOM_ORDERING
2793 , Decode(mmtt.transaction_source_type_id||'#'||mmtt.transaction_action_id,'1#27',1,
2794 '7#12',1,'12#27',1,'13#12',1,'4#27',1,null) transaction_temp_id
2795 , mtrl.wms_process_flag
2796 , (mtrl.quantity - Nvl(mtrl.quantity_delivered, 0)) quantity
2797 , mtrl.primary_quantity
2798 , mtrl.uom_code
2799 , mtrl.lpn_id
2800 , mtrl.inventory_item_id
2801 , mtrl.lot_number
2802 -- OPMConvergence
2803 , (mtrl.secondary_quantity - Nvl(mtrl.secondary_quantity_delivered, 0)) secondary_quantity_2
2804 , mtrl.secondary_quantity
2805 , mtrl.secondary_uom_code
2806 -- OPMConvergence
2807 , mtrl.crossdock_type
2808 , mtrl.backorder_delivery_detail_id
2809 , mmtt.wip_supply_type
2810 , mtrl.reference
2811 , mtrl.reference_type_code
2812 , mtrl.reference_id
2813 FROM mtl_txn_request_lines mtrl
2814 , mtl_material_transactions_temp mmtt
2815 WHERE mtrl.organization_id = p_org_id
2816 AND nvl(mtrl.from_subinventory_code,'@$#_') = nvl(p_from_sub,'@$#_')
2817 AND nvl(mtrl.from_locator_id,-1) = nvl(p_from_locator_id,-1)
2818 AND (nvl(mtrl.project_id,-1) = nvl(p_project_id,-1)
2819 or p_project_id is null) -- Bug 6618890
2820 AND (nvl(mtrl.task_id,-1) = nvl(p_task_id,-1)
2821 or p_task_id is null) -- Bug 6618890
2822 AND Nvl(inspection_status,-1) = Nvl(p_inspection_status,-1)
2823 AND mtrl.inventory_item_id = p_item
2824 AND Nvl(mtrl.revision, Nvl(p_rev, '@@@@')) = Nvl(p_rev, '@@@@')
2825 AND Nvl(mtrl.lpn_id, -1) = Nvl(p_lpn, -1)
2826 AND Nvl(mtrl.lot_number, Nvl(p_lot,'@$#_')) = Nvl(p_lot, '@$#_')
2827 AND (mtrl.quantity - Nvl(mtrl.quantity_delivered, 0)) > 0
2828 AND mmtt.move_order_line_id (+) = mtrl.line_id
2829 AND mmtt.organization_id (+) = mtrl.organization_id
2830 AND exists (SELECT 1
2831 FROM mtl_txn_request_headers mtrh
2832 WHERE mtrh.move_order_type = inv_globals.g_move_order_put_away
2833 AND mtrh.header_id = mtrl.header_id)
2834 --only pick up lines that are NOT loaded
2835 AND (mmtt.transaction_temp_id IS NULL
2836 OR
2837 (mmtt.transaction_temp_id IS NOT NULL
2838 --Bug 5231114:Added the condition on transaction_source_type_id and
2839 --transaction_action_id for the following combinations:13/12 and 4/27.
2840 AND ((mmtt.transaction_source_type_id = 1 AND mmtt.transaction_action_id = 27)
2841 OR (mmtt.transaction_source_type_id = 7 AND mmtt.transaction_action_id = 12)
2842 OR (mmtt.transaction_source_type_id = 12 AND mmtt.transaction_action_id = 27)
2843 OR (mmtt.transaction_source_type_id = 13 AND mmtt.transaction_action_id = 12)
2844 OR (mmtt.transaction_source_type_id = 4 AND mmtt.transaction_action_id = 27))
2845 AND NOT exists (SELECT 1
2846 FROM wms_dispatched_tasks wdt
2847 WHERE wdt.transaction_temp_id = mmtt.transaction_temp_id
2848 AND wdt.status IN (3, 4) -- dispached or loaded
2849 AND wdt.task_type = 2 -- putaway
2850 )
2851 )
2852 )
2853 ORDER BY 2 DESC, Nvl(transaction_temp_id, -1) ASC ;
2854
2855 l_mol_rec c_mol_no_mmtt%ROWTYPE;
2856
2857 l_remaining_primary_quantity NUMBER;
2858 l_dummy VARCHAR2(1);
2859 l_mol_qty_in_puom NUMBER;
2860 l_error_code NUMBER;
2861 l_mo_split_tb inv_rcv_integration_apis.mo_in_tb_tp;
2862
2863 l_debug NUMBER;
2864 l_progress VARCHAR2(10);
2865 l_module_name VARCHAR2(30);
2866
2867 BEGIN
2868
2869 l_debug := NVL(fnd_profile.VALUE('INV_DEBUG_TRACE'), 0);
2870
2871 IF (l_debug = 1) THEN
2872 print_debug('Entering split_close_mo_for_ret_corr...',l_module_name,4);
2873 print_debug(' p_cas_mol_tb(1).organization_id => '||p_cas_mol_tb(1).organization_id,l_module_name,4);
2874 print_debug(' p_cas_mol_tb(1).inventory_item_id => '||p_cas_mol_tb(1).inventory_item_id,l_module_name,4);
2875 print_debug(' p_cas_mol_tb(1).lpn_id => '||p_cas_mol_tb(1).lpn_id,l_module_name,4);
2876 print_debug(' p_cas_mol_tb(1).lot_number => '||p_cas_mol_tb(1).lot_number,l_module_name,4);
2877 print_debug(' p_cas_mol_tb(1).item_revision => '||p_cas_mol_tb(1).item_revision,l_module_name,4);
2878 print_debug(' p_cas_mol_tb(1).from_subinventory_code => '||p_cas_mol_tb(1).from_subinventory_code,l_module_name,4);
2879 print_debug(' p_cas_mol_tb(1).from_locator_id => '||p_cas_mol_tb(1).from_locator_id,l_module_name,4);
2880 print_debug(' p_cas_mol_tb(1).project_id => '||p_cas_mol_tb(1).project_id,l_module_name,4);
2881 print_debug(' p_cas_mol_tb(1).task_id => '||p_cas_mol_tb(1).task_id,l_module_name,4);
2882 print_debug(' p_cas_mol_tb(1).inspection_status => '||p_cas_mol_tb(1).inspection_status,l_module_name,4);
2883 print_debug(' p_cas_mol_tb(1).uom_code => '||p_cas_mol_tb(1).uom_code,l_module_name,4);
2884 END IF;
2885
2886 l_progress := '10';
2887 l_module_name := 'SET_CLOSE_MO_FOR_RET_CORR';
2888 x_return_status := fnd_api.g_ret_sts_success;
2889
2890 OPEN c_mol_no_mmtt(p_cas_mol_tb(1).organization_id
2891 ,p_cas_mol_tb(1).inventory_item_id
2892 ,p_cas_mol_tb(1).lpn_id
2893 ,p_cas_mol_tb(1).lot_number
2894 ,p_cas_mol_tb(1).item_revision
2895 ,p_cas_mol_tb(1).from_subinventory_code
2896 ,p_cas_mol_tb(1).from_locator_id
2897 ,p_cas_mol_tb(1).project_id
2898 ,p_cas_mol_tb(1).task_id
2899 ,p_cas_mol_tb(1).inspection_status
2900 ,p_cas_mol_tb(1).uom_code
2901 );
2902
2903 l_remaining_primary_quantity := Abs(p_cas_mol_tb(1).primary_qty);
2904
2905 IF (l_debug = 1) THEN
2906 print_debug('Remaining Primary Quantity:'||l_remaining_primary_quantity,l_module_name,4);
2907 END IF;
2908
2909 LOOP
2910 FETCH c_mol_no_mmtt INTO l_mol_rec;
2911 EXIT WHEN c_mol_no_mmtt%notfound;
2912
2913
2914 /* per Karun's request, this query has been moved into the cursor above
2915 IF (l_mol_rec.transaction_temp_id IS NOT NULL) THEN
2916 BEGIN
2917 SELECT '1'
2918 INTO l_dummy
2919 FROM dual
2920 WHERE exists
2921 (SELECT 1
2922 FROM wms_dispatched_tasks wdt
2923 , mtl_material_transactions_temp mmtt
2924 WHERE mmtt.move_order_line_id = l_mol_rec.line_id
2925 AND ((transaction_source_type_id = 1 AND transaction_action_id = 27)
2926 OR (transaction_source_type_id = 7 AND transaction_action_id = 12)
2927 OR (transaction_source_type_id = 12 AND transaction_action_id = 27))
2928 AND wdt.transaction_temp_id = mmtt.transaction_temp_id
2929 AND wdt.status IN (3, 4) -- dispached or loaded
2930 AND wdt.task_type = 2 -- putaway
2931 );
2932
2933 RAISE fnd_api.g_exc_error;
2934 EXCEPTION
2935 WHEN no_data_found THEN
2936 NULL;
2937 END;
2938 END IF;
2939 */
2940
2941
2942 l_mol_qty_in_puom := inv_rcv_cache.convert_qty(l_mol_rec.inventory_item_id,
2943 l_mol_rec.quantity,
2944 l_mol_rec.uom_code,
2945 p_cas_mol_tb(1).primary_uom_code);
2946
2947 IF l_mol_qty_in_puom <= l_remaining_primary_quantity THEN
2948 --update the mol
2949 UPDATE mtl_txn_request_lines
2950 SET quantity = Nvl(quantity_delivered,0)
2951 , primary_quantity = ((primary_quantity*Nvl(quantity_delivered,0))/quantity)
2952 , quantity_detailed = Decode(quantity_detailed,NULL,quantity_detailed,quantity_delivered)
2953 -- OPMConvergence
2954 , secondary_quantity = Nvl(secondary_quantity_delivered,0)
2955 , secondary_quantity_detailed = Decode(secondary_quantity_detailed,NULL,secondary_quantity_detailed,secondary_quantity_delivered)
2956 -- OPMConvergence
2957 , line_status = 5
2958 , wms_process_flag = 1
2959 WHERE line_id = l_mol_rec.line_id;
2960
2961 -- Call cancel operation plan
2962 IF (l_debug = 1) THEN
2963 print_debug('calling call_atf_api:'||l_mol_rec.line_id,l_module_name,4);
2964 END IF;
2965
2966 inv_rcv_integration_pvt.call_atf_api(x_return_status => x_return_status,
2967 x_msg_data => x_msg_data,
2968 x_msg_count => x_msg_count,
2969 x_error_code => l_error_code,
2970 p_source_task_id => NULL,
2971 p_activity_type_id => 1,
2972 p_operation_type_id => NULL,
2973 p_mol_id => l_mol_rec.line_id,
2974 p_atf_api_name => inv_rcv_integration_pvt.g_atf_api_cancel);
2975
2976 IF (x_return_status <> fnd_api.g_ret_sts_success) THEN
2977 IF (l_debug = 1) THEN
2978 print_debug('call_atf_api failed:'||l_mol_rec.line_id,l_module_name,4);
2979 END IF;
2980 --raise error
2981 RAISE fnd_api.g_exc_error;
2982 END IF;
2983
2984 l_remaining_primary_quantity := l_remaining_primary_quantity -
2985 l_mol_qty_in_puom;
2986 ELSE
2987 -- Call split_mo and then update the new line to quantity
2988 -- = 0 and then
2989 -- Call cancel operation plan for new line
2990
2991 IF (l_debug = 1) THEN
2992 print_debug('CALLING SPLIT_MO:'||l_remaining_primary_quantity,l_module_name,4);
2993 END IF;
2994
2995 l_mo_split_tb(1).prim_qty := l_remaining_primary_quantity;
2996 l_mo_split_tb(1).line_id := NULL;
2997
2998 inv_rcv_integration_apis.split_mo
2999 (p_orig_mol_id => l_mol_rec.line_id,
3000 p_mo_splt_tb => l_mo_split_tb,
3001 x_return_status => x_return_status,
3002 x_msg_count => x_msg_count,
3003 x_msg_data => x_msg_data);
3004
3005 IF (x_return_status <> fnd_api.g_ret_sts_success) THEN
3006 RAISE fnd_api.g_exc_error;
3007 END IF;
3008
3009
3010 IF (l_debug = 1) THEN
3011 print_debug('NEW LINE ID:'||l_mo_split_tb(1).line_id,l_module_name,4);
3012 END IF;
3013
3014 --update the new line for return
3015 UPDATE mtl_txn_request_lines
3016 SET quantity = Nvl(quantity_delivered,0)
3017 , primary_quantity = ((primary_quantity*Nvl(quantity_delivered,0))/quantity)
3018 , quantity_detailed = Decode(quantity_detailed,NULL,quantity_detailed,quantity_delivered)
3019 -- OPMConvergence
3020 , secondary_quantity = Nvl(secondary_quantity_delivered,0)
3021 , secondary_quantity_detailed = Decode(secondary_quantity_detailed,NULL,secondary_quantity_detailed,secondary_quantity_delivered)
3022 -- OPMConvergence
3023 , line_status = 5
3024 , wms_process_flag = 1
3025 WHERE line_id = l_mo_split_tb(1).line_id;
3026
3027 -- Call cancel operation plan for the new line
3028 inv_rcv_integration_pvt.call_atf_api(x_return_status => x_return_status,
3029 x_msg_data => x_msg_data,
3030 x_msg_count => x_msg_count,
3031 x_error_code => l_error_code,
3032 p_source_task_id => NULL,
3033 p_activity_type_id => 1,
3034 p_operation_type_id => NULL,
3035 p_mol_id => l_mo_split_tb(1).line_id,
3036 p_atf_api_name => inv_rcv_integration_pvt.g_atf_api_cancel);
3037
3038 IF (x_return_status <> fnd_api.g_ret_sts_success) THEN
3039 IF (l_debug = 1) THEN
3040 print_debug('call_atf_api failed:'||l_mo_split_tb(1).line_id,l_module_name,4);
3041 END IF;
3042 --raise error
3043 RAISE fnd_api.g_exc_error;
3044 END IF;
3045
3046 --update the old line for wms process flag
3047 UPDATE mtl_txn_request_lines
3048 SET wms_process_flag = 1
3049 WHERE line_id = l_mol_rec.line_id;
3050
3051 l_remaining_primary_quantity := 0;
3052 END IF;
3053
3054 IF (l_remaining_primary_quantity = 0) THEN
3055 EXIT;
3056 END IF;
3057
3058 END LOOP;
3059
3060 IF l_remaining_primary_quantity > 0 THEN
3061 --raise error
3062 IF (l_debug = 1) THEN
3063 print_debug('Quantity Still Remaining!!! WHY???:'||l_remaining_primary_quantity,l_module_name,4);
3064 END IF;
3065 RAISE fnd_api.g_exc_error;
3066 END IF;
3067 EXCEPTION
3068 WHEN OTHERS THEN
3069 IF (l_debug = 1) THEN
3070 print_debug('Exception occurred at progress:'||l_progress,l_module_name,4);
3071 print_debug('SQLCODE:'||SQLCODE||' SQLERRM:'||SQLERRM,l_module_name,4);
3072 END IF;
3073 x_return_status := fnd_api.g_ret_sts_error;
3074 END split_close_mo_for_ret_corr;
3075
3076 PROCEDURE maintain_reservations
3077 (x_return_status OUT NOCOPY VARCHAR2
3078 ,x_msg_count OUT NOCOPY NUMBER
3079 ,x_msg_data OUT NOCOPY VARCHAR2
3080 ,x_mol_tb OUT NOCOPY inv_rcv_integration_pvt.cas_mol_rec_tb_tp
3081 ,p_cas_mol_tb IN inv_rcv_integration_pvt.cas_mol_rec_tb_tp
3082 )
3083 IS
3084 l_debug NUMBER;
3085 l_progress VARCHAR2(10);
3086 l_module_name VARCHAR2(30);
3087 BEGIN
3088 l_debug := NVL(fnd_profile.VALUE('INV_DEBUG_TRACE'), 0);
3089
3090 IF (l_debug = 1) THEN
3091 print_debug('Entering maintain_reservations...',l_module_name,4);
3092 print_debug(' p_cas_mol_tb(1).transaction_type => ' ||p_cas_mol_tb(1).transaction_type,l_module_name,4);
3093 END IF;
3094
3095 l_progress := '10';
3096 l_module_name := 'MAINTAIN_RESERVATIONS';
3097 x_return_status := fnd_api.g_ret_sts_success;
3098
3099
3100
3101 --{{
3102 --********** PROCEDURE maintain_reservations *********
3103 --Make sure that the following transaction are tested, and
3104 --the correct private APIs are called }}
3105
3106 --{{
3107 --Test Receipt, Match, positive correction on receipt}}
3108 IF (p_cas_mol_tb(1).transaction_type IN ('RECEIVE','MATCH')
3109 OR
3110 (p_cas_mol_tb(1).transaction_type = 'CORRECT'
3111 AND p_cas_mol_tb(1).primary_qty > 0
3112 AND p_cas_mol_tb(1).parent_txn_type IN ('RECEIVE'))
3113 OR
3114 (p_cas_mol_tb(1).transaction_type = 'CORRECT'
3115 AND p_cas_mol_tb(1).primary_qty < 0
3116 AND p_cas_mol_tb(1).parent_txn_type IN ('RETURN TO VENDOR','RETURN TO CUSTOMER'))
3117 ) THEN
3118 maintain_rsv_receive
3119 (x_return_status => x_return_status
3120 ,x_msg_count => x_msg_count
3121 ,x_msg_data => x_msg_data
3122 ,x_cas_mol_rec_tb => x_mol_tb
3123 ,p_cas_mol_rec_tb => p_cas_mol_tb);
3124
3125 --{{
3126 --Test deliver and positive correction of deliver}}
3127 ELSIF (p_cas_mol_tb(1).transaction_type = 'DELIVER'
3128 OR
3129 (p_cas_mol_tb(1).transaction_type = 'CORRECT'
3130 AND p_cas_mol_tb(1).primary_qty > 0
3131 AND p_cas_mol_tb(1).parent_txn_type IN ('DELIVER'))) THEN
3132 maintain_rsv_deliver
3133 (x_return_status => x_return_status
3134 ,x_msg_count => x_msg_count
3135 ,x_msg_data => x_msg_data
3136 ,p_cas_mol_rec_tb => p_cas_mol_tb);
3137
3138 --{{
3139 --Test cancelling an ASN that has reservation tied to it}}
3140 ELSIF p_cas_mol_tb(1).transaction_type = 'CANCEL' THEN
3141 maintain_rsv_cancel_asn
3142 (x_return_status => x_return_status
3143 ,x_msg_count => x_msg_count
3144 ,x_msg_data => x_msg_data
3145 ,p_cas_mol_rec_tb => p_cas_mol_tb);
3146
3147 --{{
3148 --Test import ASN of a PO against which a reservation is created}}
3149 ELSIF p_cas_mol_tb(1).transaction_type = 'SHIP' AND p_cas_mol_tb(1).source_document_code = 'PO' THEN
3150 maintain_rsv_import_asn
3151 (x_return_status => x_return_status
3152 ,x_msg_count => x_msg_count
3153 ,x_msg_data => x_msg_data
3154 ,p_cas_mol_rec_tb => p_cas_mol_tb);
3155
3156 --{{
3157 --Test 1. Negative correction of Receipt
3158 -- 2. Positive correction of RTV and RTC
3159 -- 3. RTV and RTC
3160 --MOL should be reduced from receiving
3161 ELSIF ((p_cas_mol_tb(1).transaction_type IN ('CORRECT')
3162 AND p_cas_mol_tb(1).primary_qty < 0
3163 AND p_cas_mol_tb(1).parent_txn_type IN ('RECEIVE'))
3164 OR
3165 (p_cas_mol_tb(1).transaction_type IN ('CORRECT')
3166 AND p_cas_mol_tb(1).primary_qty > 0
3167 AND p_cas_mol_tb(1).parent_txn_type IN ('RETURN TO VENDOR','RETURN TO CUSTOMER')
3168 AND Nvl(p_cas_mol_tb(1).grand_parent_txn_type,'#@#') <> 'DELIVER')
3169 OR
3170 (p_cas_mol_tb(1).transaction_type IN ('RETURN TO VENDOR','RETURN TO CUSTOMER')
3171 AND p_cas_mol_tb(1).parent_txn_type <> 'DELIVER')) THEN
3172
3173 IF (l_debug = 1) THEN
3174 print_debug('- Corr of Receive/+ Corr of RTV and RTC/RTV and RTC. Calling maintain_rsv_returns',l_module_name,4);
3175 END IF;
3176
3177 maintain_rsv_returns
3178 (x_return_status => x_return_status
3179 ,x_msg_count => x_msg_count
3180 ,x_msg_data => x_msg_data
3181 ,p_cas_mol_rec_tb => p_cas_mol_tb);
3182
3183
3184 --{{
3185 --Test 1. Negative corr of inspect and transfer
3186 -- 2. Positive corr of inspect and transfer
3187 --MOL should be transfered within receiving
3188 ELSIF ((p_cas_mol_tb(1).transaction_type IN ('CORRECT')
3189 AND p_cas_mol_tb(1).primary_qty < 0
3190 AND p_cas_mol_tb(1).parent_txn_type IN ('ACCEPT','REJECT','TRANSFER'))
3191 OR
3192 (p_cas_mol_tb(1).transaction_type IN ('CORRECT')
3193 AND p_cas_mol_tb(1).primary_qty > 0
3194 AND p_cas_mol_tb(1).parent_txn_type IN ('ACCEPT','REJECT','TRANSFER'))) THEN
3195
3196 IF (l_debug = 1) THEN
3197 print_debug('-/+ Corr of Inspect and Transfer. Need to close and create MOL',l_module_name,4);
3198 END IF;
3199
3200 split_close_mo_for_ret_corr(x_return_status => x_return_status
3201 ,x_msg_count => x_msg_count
3202 ,x_msg_data => x_msg_data
3203 ,p_cas_mol_tb => p_cas_mol_tb);
3204
3205 IF (x_return_status <> fnd_api.g_ret_sts_success) THEN
3206 IF (l_debug = 1) THEN
3207 print_debug('split_mo_ret_corr failed for this process ',l_module_name,4);
3208 END IF;
3209 --raise error
3210 RAISE fnd_api.g_exc_error;
3211 END IF;
3212
3213 set_mol_wdd_tbl(p_cas_mol_tb(1),
3214 x_mol_tb,
3215 p_cas_mol_tb(1).primary_qty,
3216 NULL,
3217 NULL);
3218
3219
3220 --{{
3221 --Test negative correction of RTC
3222 --Materials should be reduced into receiving}}
3223 ELSIF (p_cas_mol_tb(1).transaction_type IN ('CORRECT')
3224 AND p_cas_mol_tb(1).primary_qty < 0
3225 AND p_cas_mol_tb(1).parent_txn_type IN ('RETURN TO RECEIVING')) THEN
3226
3227 IF (l_debug = 1) THEN
3228 print_debug('- Corr of RTR. Need to close MOL',l_module_name,4);
3229 END IF;
3230
3231 split_close_mo_for_ret_corr(x_return_status => x_return_status
3232 ,x_msg_count => x_msg_count
3233 ,x_msg_data => x_msg_data
3234 ,p_cas_mol_tb => p_cas_mol_tb);
3235
3236 IF (x_return_status <> fnd_api.g_ret_sts_success) THEN
3237 IF (l_debug = 1) THEN
3238 print_debug('split_mo_ret_corr failed for this process ',l_module_name,4);
3239 END IF;
3240 --raise error
3241 RAISE fnd_api.g_exc_error;
3242 END IF;
3243
3244
3245 --{{
3246 --Test 1. negative correction of deliver
3247 -- 2. positive correction of RTR
3248 -- 3. RTR
3249 --Materials should be added to receiving }}
3250 ELSIF ((p_cas_mol_tb(1).transaction_type IN ('CORRECT')
3251 AND p_cas_mol_tb(1).primary_qty < 0
3252 AND p_cas_mol_tb(1).parent_txn_type = 'DELIVER')
3253 OR
3254 (p_cas_mol_tb(1).transaction_type IN ('CORRECT')
3255 AND p_cas_mol_tb(1).primary_qty > 0
3256 AND p_cas_mol_tb(1).parent_txn_type IN ('RETURN TO RECEIVING'))
3257 OR
3258 p_cas_mol_tb(1).transaction_type = 'RETURN TO RECEIVING') THEN
3259
3260 IF (l_debug = 1) THEN
3261 print_debug('- Corr of deliver/+ Corr of RTR/RTR. Need to create MOL',l_module_name,4);
3262 END IF;
3263
3264 set_mol_wdd_tbl(p_cas_mol_tb(1),
3265 x_mol_tb,
3266 p_cas_mol_tb(1).primary_qty,
3267 NULL,
3268 NULL);
3269 END IF;
3270
3271 --{{
3272 --********** END PROCEDURE maintain_reservations *********}}
3273
3274 EXCEPTION
3275 WHEN OTHERS THEN
3276 IF (l_debug = 1) THEN
3277 print_debug('Exception occurred at progress:'||l_progress,l_module_name,4);
3278 print_debug('SQLCODE:'||SQLCODE||' SQLERRM:'||SQLERRM,l_module_name,4);
3279 END IF;
3280 x_return_status := fnd_api.g_ret_sts_error;
3281 END maintain_reservations;
3282 END inv_rcv_reservation_util;
3283