1 PACKAGE BODY inv_rsv_detail_stage_pvt AS
2 /* $Header: INVRSDSB.pls 120.0.12010000.2 2010/03/11 09:41:32 viiyer noship $ */
3
4 g_pkg_name CONSTANT VARCHAR2(30) := 'INV_RSV_DETAIL_STAGE_PVT';
5 g_version_printed BOOLEAN := FALSE;
6 g_debug NUMBER;
7
8 -- procedure to print a message to dbms_output
9 -- disable by default since dbms_output.put_line is not allowed
10 PROCEDURE debug_print(p_message IN VARCHAR2, p_level IN NUMBER := 9) IS
11 BEGIN
12 inv_log_util.TRACE(p_message, 'INV_RSV_DETAIL_STAGE_PVT', p_level);
13 END debug_print;
14
15
16 -- Function
17 -- is_reservation_allowed
18 --
19 -- Description
20 -- This function will check whether the reservation is allowed for the given
21 -- SKU or not
22 --
23 -- Input Paramters
24 -- l_rsv_rec Contains info to be used to process the
25 -- reservation with the SKU
26 -- Return Value
27 -- l_res_type = 1 if reservations are allowed
28 -- = 2 if reservations are not allowed
29
30
31 FUNCTION is_reservation_allowed
32 (
33 l_rsv_rec IN inv_reservation_global.mtl_reservation_rec_type
34 )
35 RETURN NUMBER
36 IS
37 l_default_org_status_id NUMBER;
38 l_return_status_id NUMBER;
39 l_res_type NUMBER := 1;
40 l_debug NUMBER;
41 l_sub_reservable_type NUMBER := 1;
42 l_loc_reservable_type NUMBER := 1;
43 l_lot_reservable_type NUMBER := 1;
44
45 BEGIN
46 l_debug := g_debug;
47 IF (l_debug = 1) THEN
48 debug_print('Checking whether the reservations are allowed or not ');
49 END IF;
50
51 -- Check whether the onhand is reservable or not
52 IF inv_cache.set_org_rec(l_rsv_rec.organization_id) THEN
53 l_default_org_status_id := inv_cache.org_rec.default_status_id;
54 END IF;
55
56 IF (l_debug = 1) THEN
57 debug_print('Default Org status id is :' || l_default_org_status_id );
58 END IF;
59
60
61 IF l_default_org_status_id IS NULL THEN
62
63 IF (l_debug = 1) THEN
64 debug_print('Organization is not onhand material status enabled');
65 debug_print('Checking the reservable status for individual SKU');
66 END IF;
67
68 -- Checking whether the subinventory is reservable or not
69 -- If not reservable then no detailed/staged reservations would be created
70 IF l_rsv_rec.subinventory_code IS NOT NULL AND l_rsv_rec.organization_id IS NOT NULL THEN
71 BEGIN
72 SELECT NVL(reservable_type, 1)
73 INTO l_sub_reservable_type
74 FROM mtl_secondary_inventories
75 WHERE organization_id = l_rsv_rec.organization_id
76 AND secondary_inventory_name = l_rsv_rec.subinventory_code;
77 EXCEPTION
78 WHEN OTHERS THEN
79 IF (l_debug = 1) THEN
80 debug_print('Exception Occurred while checking reservable type for subinventory : '
81 || l_rsv_rec.subinventory_code || ' Organization Id :'
82 || l_rsv_rec.organization_id );
83 END IF;
84 RAISE fnd_api.g_exc_unexpected_error;
85 END;
86 END IF;
87
88 -- Checking whether the locator is reservable or not
89 -- If not reservable then no detailed/staged reservations would be created
90 IF l_rsv_rec.locator_id IS NOT NULL AND l_rsv_rec.organization_id IS NOT NULL THEN
91 BEGIN
92 SELECT NVL(reservable_type, 1)
93 INTO l_loc_reservable_type
94 FROM mtl_item_locations
95 WHERE organization_id = l_rsv_rec.organization_id
96 AND inventory_location_id = l_rsv_rec.locator_id;
97 EXCEPTION
98 WHEN OTHERS THEN
99 IF (l_debug = 1) THEN
100 debug_print('Exception Occurred while checking reservable type for locator : '
101 || l_rsv_rec.locator_id || ' Organization Id :'
102 || l_rsv_rec.organization_id );
103 END IF;
104 RAISE fnd_api.g_exc_unexpected_error;
105 END;
106 END IF;
107
108 -- Checking whether the lot is reservable or not
109 -- If not reservable then no detailed/staged reservations would be created
110 IF l_rsv_rec.organization_id IS NOT NULL
111 AND l_rsv_rec.inventory_item_id IS NOT NULL
112 AND l_rsv_rec.lot_number IS NOT NULL THEN
113
114 BEGIN
115 SELECT NVL(reservable_type, 1)
116 INTO l_lot_reservable_type
117 FROM mtl_lot_numbers
118 WHERE inventory_item_id = l_rsv_rec.inventory_item_id
119 AND organization_id = l_rsv_rec.organization_id
120 AND lot_number = l_rsv_rec.lot_number;
121 EXCEPTION
122 WHEN OTHERS THEN
123 IF (l_debug = 1) THEN
124 debug_print('Exception Occurred while checking reservable type for lot : '
125 || l_rsv_rec.lot_number || ' Organization Id :'
126 || l_rsv_rec.organization_id || ' Invenotry Item Id :'
127 || l_rsv_rec.inventory_item_id);
128 END IF;
129 RAISE fnd_api.g_exc_unexpected_error;
130 END;
131 END IF;
132
133 IF (l_debug = 1) THEN
134 debug_print('For Inventory Item Id : ' || l_rsv_rec.inventory_item_id || ' Organization Id : ' || l_rsv_rec.organization_id);
135 debug_print('Reservable type for Subinventory : ' || l_rsv_rec.subinventory_code || ' is :' || l_sub_reservable_type);
136 debug_print('Reservable type for Locator : ' || l_rsv_rec.locator_id || ' is :' || l_loc_reservable_type);
137 debug_print('Reservable type for Lot Number : ' || l_rsv_rec.lot_number || ' is :' || l_lot_reservable_type);
138 END IF;
139
140 IF l_sub_reservable_type = 1 AND l_loc_reservable_type = 1 AND l_lot_reservable_type = 1 THEN
141 l_res_type := 1;
142 ELSE
143 l_res_type := 2;
144 END IF;
145
146 ELSE -- IF l_default_org_status_id IS NULL THEN
147
148 -- Org is onhand material status enabled
149 -- check for the status id and get the reservable_type from mtl_material_statuses_b
150 IF (l_debug = 1) THEN
151 debug_print('Organization is onhand material status enabled');
152 debug_print('Before getting the onhand status');
153 END IF;
154
155 --calling function to get the MOQD status
156 l_return_status_id := INV_MATERIAL_STATUS_GRP.get_default_status
157 (p_organization_id => l_rsv_rec.organization_id,
158 p_inventory_item_id => l_rsv_rec.inventory_item_id,
159 p_sub_code => l_rsv_rec.subinventory_code,
160 p_loc_id => l_rsv_rec.locator_id,
161 p_lot_number => l_rsv_rec.lot_number,
162 p_lpn_id => l_rsv_rec.lpn_id,
163 p_transaction_action_id => NULL,
164 p_src_status_id => NULL
165 );
166
167
168 IF (l_debug = 1) THEN
169 debug_print('Status id :' || l_return_status_id);
170 debug_print('Before getting the onhand status');
171 END IF;
172
173 IF l_return_status_id IS NULL THEN
174 l_res_type :=1 ; -- reservable type is YES
175 ELSE
176 BEGIN
177 SELECT reservable_type
178 INTO l_res_type
179 FROM mtl_material_statuses_b
180 WHERE status_id = l_return_status_id;
181 EXCEPTION
182 WHEN OTHERS THEN
183 IF (l_debug = 1) THEN
184 debug_print('Exception occurred while querying mtl_material_statuses_b :' || l_return_status_id);
185 END IF;
186 l_res_type := 1 ; -- reservable type is set to YES in case of exception
187 END;
188 END IF;
189 END IF; -- IF l_default_org_status_id IS NULL THEN
190
191 IF (l_debug = 1) THEN
192 debug_print('Reservable type is :' || l_res_type);
193 END IF;
194
195 return l_res_type;
196
197 EXCEPTION
198 WHEN OTHERS THEN
199 IF (l_debug = 1) THEN
200 debug_print('Exception in is_reservation_allowed');
201 END IF;
202 RETURN l_res_type;
203 END;
204
205 --
206 -- Procedure
207 -- Sort_Reservation
208 -- Description
209 -- Sorts the reservation table in the following order.
210 -- The table will contain reservations corresponding to one
211 -- demand source line
212 -- 1. Exact SKU match. Rev, Lot, Sub, Loc
213 -- 2. Partial SKU match. Rev, Lot, Sub. Locator unmatched
214 -- 3. Partial SKU match. Rev, Lot, Sub & Locator unmatched
215 -- 4. Partial SKU match. Rev. Lot, Sub & Locator unmatched
216 -- 5. No SKU match. Org/High level reservation
217 -- 6 Remaining reservations in the same order i.e increasing order of detailing
218 -- Requirement
219 -- Process reservation will process the reservation one by one
220 -- starting with the first record from the output rsv table
221 -- In order to pick the exact match or closest match, we need
222 -- to sort the output rsv table in the above manner
223
224 PROCEDURE Sort_Reservation
225 ( p_mtl_reservation IN OUT NOCOPY inv_reservation_global.mtl_reservation_tbl_type ,
226 p_rsv_rec IN inv_reservation_global.mtl_reservation_rec_type ,
227 x_return_status OUT NOCOPY VARCHAR2 ,
228 x_msg_count OUT NOCOPY NUMBER ,
229 x_msg_data OUT NOCOPY VARCHAR2
230 )
231 IS
232 l_mtl_reservation inv_reservation_global.mtl_reservation_tbl_type;
233 x_mtl_reservation inv_reservation_global.mtl_reservation_tbl_type;
234 l_rsv_index NUMBER := 0;
235 l_return_status VARCHAR2(1) := fnd_api.g_ret_sts_success;
236 l_debug NUMBER;
237
238 BEGIN
239
240 l_mtl_reservation := p_mtl_reservation;
241 l_debug := g_debug;
242
243 IF (l_debug = 1) THEN
244 debug_print('Inside Sort_Reservations. Original reservation count: ' || l_mtl_reservation.Count);
245 END IF;
246 -- Loop through the full rsv table to check for record wherein the full SKU is matching
247 -- If found, put the matching row into a different variable
248 IF l_mtl_reservation.Count > 0 THEN
249 FOR i IN l_mtl_reservation.first..l_mtl_reservation.last
250 LOOP
251
252 IF nvl(l_mtl_reservation(i).revision , '@@@') = nvl(p_rsv_rec.revision, '@@@') AND
253 nvl(l_mtl_reservation(i).lot_number, '@@@') = nvl(p_rsv_rec.lot_number, '@@@') AND
254 nvl(l_mtl_reservation(i).subinventory_code,'@@@') = nvl(p_rsv_rec.subinventory_code,'@@@') AND
255 nvl(l_mtl_reservation(i).locator_id, -999) = nvl(p_rsv_rec.locator_id, -999)
256 THEN
257 l_rsv_index := l_rsv_index + 1;
258 x_mtl_reservation(l_rsv_index) := l_mtl_reservation(i);
259 l_mtl_reservation.DELETE(i);
260 END IF;
261 END LOOP;
262 END IF;
263
264 IF (l_debug = 1) THEN
265 debug_print('Exact match. Rev, Lot, Sub Loc : ' || l_rsv_index);
266 END IF;
267
268 -- Loop through the full rsv table to check for record wherein the partial SKU is matching
269 -- rev, lot, sub matching
270 -- If found, put the matching row into a different variable
271 IF l_mtl_reservation.Count > 0 THEN
272 FOR i IN l_mtl_reservation.first..l_mtl_reservation.last
273 LOOP
274
275 IF nvl(l_mtl_reservation(i).revision , '@@@') = nvl(p_rsv_rec.revision, '@@@') AND
276 nvl(l_mtl_reservation(i).lot_number, '@@@') = nvl(p_rsv_rec.lot_number, '@@@') AND
277 nvl(l_mtl_reservation(i).subinventory_code,'@@@') = nvl(p_rsv_rec.subinventory_code,'@@@')
278 THEN
279 l_rsv_index := l_rsv_index + 1;
280 x_mtl_reservation(l_rsv_index) := l_mtl_reservation(i);
281 l_mtl_reservation.DELETE(i);
282 END IF;
283 END LOOP;
284 END IF;
285
286 IF (l_debug = 1) THEN
287 debug_print('Partial match. Rev, Lot, Sub : ' || l_rsv_index);
288 END IF;
289
290 -- Loop through the full rsv table to check for record wherein the partial SKU is matching
291 -- rev & lot matching
292 -- If found, put the matching row into a different variable
293 IF l_mtl_reservation.Count > 0 THEN
294 FOR i IN l_mtl_reservation.first..l_mtl_reservation.last
295 LOOP
296
297 IF nvl(l_mtl_reservation(i).revision , '@@@') = nvl(p_rsv_rec.revision , '@@@') AND
298 nvl(l_mtl_reservation(i).lot_number, '@@@') = nvl(p_rsv_rec.lot_number, '@@@')
299 THEN
300 l_rsv_index := l_rsv_index + 1;
301 x_mtl_reservation(l_rsv_index) := l_mtl_reservation(i);
302 l_mtl_reservation.DELETE(i);
303 END IF;
304 END LOOP;
305 END IF;
306
307 IF (l_debug = 1) THEN
308 debug_print('Partial match. Rev, Lot : ' || l_rsv_index);
309 END IF;
310
311 -- Loop through the full rsv table to check for record wherein the partial SKU is matching
312 -- only rev matching\
313 -- If found, put the matching row into a different variable
314 IF l_mtl_reservation.Count > 0 THEN
315 FOR i IN l_mtl_reservation.first..l_mtl_reservation.last
316 LOOP
317 IF nvl(l_mtl_reservation(i).revision , '@@@') = nvl(p_rsv_rec.revision, '@@@')
318 THEN
319 l_rsv_index := l_rsv_index + 1;
320 x_mtl_reservation(l_rsv_index) := l_mtl_reservation(i);
321 l_mtl_reservation.DELETE(i);
322 END IF;
323 END LOOP;
324 END IF;
325
326 IF (l_debug = 1) THEN
327 debug_print('Partial match. Only Rev : ' || l_rsv_index);
328 END IF;
329
330 -- Loop through the full rsv table to check for records not matching with the SKU at all
331 -- These would be the high/org level reservations
332 -- If found, put the matching row into a different variable
333 IF l_mtl_reservation.Count > 0 THEN
334 FOR i IN l_mtl_reservation.first..l_mtl_reservation.last
335 LOOP
336 IF l_mtl_reservation(i).revision IS NULL AND l_mtl_reservation(i).lot_number IS NULL AND
337 l_mtl_reservation(i).subinventory_code IS NULL AND l_mtl_reservation(i).locator_id IS NULL THEN
338 l_rsv_index := l_rsv_index + 1;
339 x_mtl_reservation(l_rsv_index) := l_mtl_reservation(i);
340 l_mtl_reservation.DELETE(i);
341 END IF;
342 END LOOP;
343 END IF;
344
345 IF (l_debug = 1) THEN
346 debug_print('No Match. High level reservation : ' || l_rsv_index);
347 END IF;
348
349 -- Put the remaining reservations in the same order
350 -- The default order is increasing order of detailing
351 IF l_mtl_reservation.Count > 0 THEN
352 FOR i IN l_mtl_reservation.first..l_mtl_reservation.last
353 LOOP
354 l_rsv_index := l_rsv_index + 1;
355 x_mtl_reservation(l_rsv_index) := l_mtl_reservation(i);
356 l_mtl_reservation.DELETE(i);
357 END LOOP;
358 END IF;
359
360 IF (l_debug = 1) THEN
361 debug_print('No Match. Detailed rsv for other sku : ' || l_rsv_index);
362 END IF;
363
364 p_mtl_reservation := x_mtl_reservation;
365
366 IF (l_debug = 1) THEN
367 debug_print('Done with reservations sorting. Sorted reservation count: ' || l_mtl_reservation.Count);
368 END IF;
369
370 x_return_status := l_return_status;
371 --
372 EXCEPTION
373 WHEN fnd_api.g_exc_error THEN
374 x_return_status := fnd_api.g_ret_sts_error;
375 IF (l_debug = 1) THEN
376 debug_print('Error occurred in Sort Reservation : ' || x_return_status);
377 END IF;
378 --
379 WHEN fnd_api.g_exc_unexpected_error THEN
380 x_return_status := fnd_api.g_ret_sts_unexp_error;
381 IF (l_debug = 1) THEN
382 debug_print('Exception occurred in Sort Reservation : ' || x_return_status);
383 END IF;
384
385 WHEN OTHERS THEN
386 x_return_status := fnd_api.g_ret_sts_unexp_error;
387 IF (l_debug = 1) THEN
388 debug_print('Exception occurred in Sort Reservation : ' || x_return_status);
389 END IF;
390
391 END Sort_Reservation;
392
393 --
394 -- Procedure
395 -- Get_atr
396 -- Description
397 -- Used to get the atr for the sku passed in rsv rec
398
399 PROCEDURE Get_atr
400 ( p_rsv_rec IN inv_reservation_global.mtl_reservation_rec_type ,
401 x_atr OUT NOCOPY NUMBER ,
402 x_return_status OUT NOCOPY VARCHAR2 ,
403 x_msg_count OUT NOCOPY NUMBER ,
404 x_msg_data OUT NOCOPY VARCHAR2
405 )
406 IS
407 l_mtl_reservation inv_reservation_global.mtl_reservation_tbl_type;
408 x_mtl_reservation inv_reservation_global.mtl_reservation_tbl_type;
409 l_rsv_index NUMBER := 0;
410 l_return_status VARCHAR2(1) := fnd_api.g_ret_sts_success;
411 l_debug NUMBER;
412 l_rev_control BOOLEAN;
413 l_lot_control BOOLEAN;
414 l_ser_control BOOLEAN;
415 l_tree_id INTEGER;
416 l_msg_count NUMBER;
417 l_msg_data VARCHAR2(240);
418 l_qoh NUMBER;
419 l_rqoh NUMBER;
420 l_qr NUMBER;
421 l_qs NUMBER;
422 l_att NUMBER;
423 l_atr NUMBER;
424 l_sqoh NUMBER;
425 l_srqoh NUMBER;
426 l_sqr NUMBER;
427 l_sqs NUMBER;
428 l_satt NUMBER;
429 l_satr NUMBER;
430
431 BEGIN
432 l_debug := g_debug;
433
434 IF (l_debug = 1) THEN
435 debug_print('Inside Get atr ');
436 END IF;
437
438 IF INV_CACHE.item_rec.revision_qty_control_code = inv_reservation_global.g_revision_control_yes THEN
439 l_rev_control := TRUE;
440 ELSE
441 l_rev_control := FALSE;
442 END IF;
443
444 IF INV_CACHE.item_rec.lot_control_code = inv_reservation_global.g_lot_control_yes THEN
445 l_lot_control := TRUE;
446 ELSE
447 l_lot_control := FALSE;
448 END IF;
449
450 IF INV_CACHE.item_rec.serial_number_control_code <> inv_reservation_global.g_serial_control_predefined THEN
451 l_ser_control := TRUE;
452 ELSE
453 l_ser_control := FALSE;
454 END IF;
455
456 IF (l_debug = 1) THEN
457 debug_print('Creating tree to get the atr for the given sku ');
458 END IF;
459
460 inv_quantity_tree_pvt.create_tree (
461 p_api_version_number => 1.0
462 , p_init_msg_lst => fnd_api.g_true
463 , x_return_status => l_return_status
464 , x_msg_count => x_msg_count
465 , x_msg_data => x_msg_data
466 , p_organization_id => p_rsv_rec.organization_id
467 , p_inventory_item_id => p_rsv_rec.inventory_item_id
468 , p_tree_mode => inv_quantity_tree_pvt.g_reservation_mode
469 , p_is_revision_control => l_rev_control
470 , p_is_lot_control => l_lot_control
471 , p_is_serial_control => l_ser_control
472 , p_asset_sub_only => FALSE
473 , p_include_suggestion => TRUE
474 , p_demand_source_type_id => p_rsv_rec.demand_source_type_id
475 , p_demand_source_header_id => p_rsv_rec.demand_source_header_id
476 , p_demand_source_line_id => p_rsv_rec.demand_source_line_id
477 , p_demand_source_name => p_rsv_rec.demand_source_name
478 , p_demand_source_delivery => p_rsv_rec.demand_source_delivery
479 , p_lot_expiration_date => SYSDATE
480 , x_tree_id => l_tree_id
481 );
482
483 IF (l_debug = 1) THEN
484 debug_print('After create tree in process reservations ' || l_return_status || ' Tree Id: ' || l_tree_id);
485 END IF;
486
487 IF l_return_status = fnd_api.g_ret_sts_error THEN
488 RAISE fnd_api.g_exc_error;
489 END IF;
490
491 IF l_return_status = fnd_api.g_ret_sts_unexp_error THEN
492 RAISE fnd_api.g_exc_unexpected_error;
493 END IF;
494
495 inv_quantity_tree_pvt.query_tree(
496 p_api_version_number => 1.0
497 , p_init_msg_lst => fnd_api.g_true
498 , x_return_status => l_return_status
499 , x_msg_count => l_msg_count
500 , x_msg_data => l_msg_data
501 , p_tree_id => l_tree_id
502 , p_revision => p_rsv_rec.revision
503 , p_lot_number => p_rsv_rec.lot_number
504 , p_subinventory_code => p_rsv_rec.subinventory_code
505 , p_locator_id => p_rsv_rec.locator_id
506 , x_qoh => l_qoh
507 , x_rqoh => l_rqoh
508 , x_qr => l_qr
509 , x_qs => l_qs
510 , x_att => l_att
511 , x_atr => l_atr
512 , x_sqoh => l_sqoh
513 , x_srqoh => l_srqoh
514 , x_sqr => l_sqr
515 , x_sqs => l_sqs
516 , x_satt => l_satt
517 , x_satr => l_satr
518 , p_lpn_id => p_rsv_rec.lpn_id
519 );
520
521 IF (l_debug = 1) THEN
522 debug_print('After query tree in process reservations ' || l_return_status);
523 END IF;
524
525
526 IF l_return_status = fnd_api.g_ret_sts_error THEN
527 RAISE fnd_api.g_exc_error;
528 END IF;
529
530 IF l_return_status = fnd_api.g_ret_sts_unexp_error THEN
531 RAISE fnd_api.g_exc_unexpected_error;
532 END IF;
533
534 IF (l_debug = 1) THEN
535 debug_print('l_atr ' || l_atr);
536 debug_print('l_att ' || l_att);
537 debug_print('l_qoh ' || l_qoh);
538 debug_print('l_rqoh ' || l_rqoh);
539 debug_print('l_qr ' || l_qr);
540 debug_print('l_qs ' || l_qs);
541 debug_print('l_satr ' || l_satr);
542 debug_print('l_satt ' || l_satt);
543 debug_print('l_sqoh ' || l_sqoh);
544 debug_print('l_srqoh '|| l_srqoh);
545 debug_print('l_sqr ' || l_sqr);
546 debug_print('l_sqs ' || l_sqs);
547 END IF;
548
549 x_atr := l_atr;
550
551 EXCEPTION
552 WHEN fnd_api.g_exc_error THEN
553 x_return_status := fnd_api.g_ret_sts_error;
554 IF (l_debug = 1) THEN
555 debug_print('Error occurred in Get atr : ' || x_return_status);
556 END IF;
557 --
558 WHEN fnd_api.g_exc_unexpected_error THEN
559 x_return_status := fnd_api.g_ret_sts_unexp_error;
560 IF (l_debug = 1) THEN
561 debug_print('Exception occurred in Get atr : ' || x_return_status);
562 END IF;
563
564 WHEN OTHERS THEN
565 x_return_status := fnd_api.g_ret_sts_unexp_error;
566 IF (l_debug = 1) THEN
567 debug_print('Exception occurred in Get atr : ' || x_return_status);
568 END IF;
569 END Get_atr;
570
571
572 -- Procedure
573 -- process_reservation
574 --
575 -- Description
576 -- This api will detail and stage an org level or detailed reservation
577 --
578 -- Input Paramters
579 -- p_api_version_number API version number (current version is 1.0)
580 --
581 -- p_init_msg_lst Whether initialize the error message list or
582 -- not.
583 -- Should be fnd_api.g_false or fnd_api.g_true
584 --
585 -- p_rsv_rec Contains info to be used to process the
586 -- reservation
587 --
588 -- p_serial_number Contains serial numbers to be staged
589 --
590 -- p_rsv_status 'DETAIL' or 'STAGE'
591 -- IF DETAIL then the reservation would be detailed
592 -- to the sku passed
593 -- IF STAGE then the reservation would be
594 -- detailed and then staged
595 --
596 -- Output Parameters
597 -- x_return_status = fnd_api.g_ret_sts_success, if succeeded
598 -- = fnd_api.g_ret_sts_exc_error, if an expected
599 -- error occurred
600 -- = fnd_api.g_ret_sts_unexp_error, if
601 -- an unexpected error occurred
602 --
603 -- x_msg_count Number of error message in the error message
604 -- list
605 --
606 -- x_msg_data If the number of error message in the error
607 -- message list is one, the error message
608 -- is in this output parameter
609 --
610
611 PROCEDURE Process_Reservation
612 ( p_api_version_number IN NUMBER ,
613 p_init_msg_lst IN VARCHAR2 DEFAULT fnd_api.g_false ,
614 p_rsv_rec IN inv_reservation_global.mtl_reservation_rec_type ,
615 p_serial_number IN inv_reservation_global.serial_number_tbl_type ,
616 p_rsv_status IN VARCHAR2,
617 x_return_status OUT NOCOPY VARCHAR2 ,
618 x_msg_count OUT NOCOPY NUMBER ,
619 x_msg_data OUT NOCOPY VARCHAR2
620 )
621 IS
622
623 l_return_status VARCHAR2(1) := fnd_api.g_ret_sts_success;
624 l_msg_count NUMBER;
625 l_msg_data VARCHAR2(2000);
626 l_query_input inv_reservation_global.mtl_reservation_rec_type;
627 l_query_det_rsv inv_reservation_global.mtl_reservation_rec_type;
628 l_to_rsv_record inv_reservation_global.mtl_reservation_rec_type;
629 l_original_rsv_record inv_reservation_global.mtl_reservation_rec_type;
630 l_rsv_rec inv_reservation_global.mtl_reservation_rec_type;
631 l_rsv_rec1 inv_reservation_global.mtl_reservation_rec_type;
632 l_create_rsv_rec inv_reservation_global.mtl_reservation_rec_type;
633 l_mtl_reservation_staged inv_reservation_global.mtl_reservation_rec_type;
634 l_mtl_reservation_det_qty inv_reservation_global.mtl_reservation_rec_type;
635 l_mtl_reservation inv_reservation_global.mtl_reservation_tbl_type;
636 l_mtl_reservation_non_staged inv_reservation_global.mtl_reservation_tbl_type;
637 l_mtl_reservation_detailed inv_reservation_global.mtl_reservation_tbl_type;
638 l_dummy_sn inv_reservation_global.serial_number_tbl_type;
639 l_serial_number inv_reservation_global.serial_number_tbl_type;
640 l_serial_number1 inv_reservation_global.serial_number_tbl_type;
641 l_mtl_rsv_non_staged_count NUMBER := 0;
642 l_mtl_rsv_detailed_count NUMBER := 0;
643 l_mtl_reservation_count NUMBER := 0;
644 l_error_code NUMBER;
645 l_primary_reservation_qty NUMBER := 0;
646 l_secondary_reservation_qty NUMBER := 0;
647 l_remaining_reservation_qty NUMBER := 0;
648 l_new_rsv_quantity NUMBER := 0;
649 l_new_prim_rsv_quantity NUMBER := 0;
650 l_staged_flag VARCHAR2(1);
651 l_return_value BOOLEAN;
652 l_det_res_id NUMBER;
653 l_det_res_qty NUMBER;
654 l_primary_uom VARCHAR2(3);
655 l_secondary_uom VARCHAR2(3);
656 l_revision_control_code NUMBER;
657 l_lot_control_code NUMBER;
658 l_serial_number_control_code NUMBER;
659 l_rsv_index NUMBER := 0;
660 l_debug NUMBER;
661 --l_sub_reservable_type NUMBER := 1;
662 --l_loc_reservable_type NUMBER := 1;
663 --l_lot_reservable_type NUMBER := 1;
664 l_msnt_seq NUMBER := 0;
665 l_default_org_status_id NUMBER ;
666 l_reservation_allowed NUMBER ;
667 l_atr INTEGER ;
668
669 BEGIN
670
671 SAVEPOINT process_reservation_ds;
672
673 IF (g_debug IS NULL) THEN
674 g_debug := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
675 END IF;
676
677 l_debug := g_debug;
678
679 IF (l_debug = 1) THEN
680 debug_print('Inside Process_Reservations');
681 END IF;
682
683 IF (l_debug = 1) THEN
684 debug_print('Printing the input reservation record passed');
685 END IF;
686
687 --Assign the input record to a local variable for modifications
688 l_rsv_rec := p_rsv_rec;
689 l_rsv_rec1 := p_rsv_rec;
690 l_serial_number := p_serial_number;
691
692 --Print the input reservation record passed
693 inv_reservation_pvt.print_rsv_rec( p_rsv_rec => l_rsv_rec);
694
695 IF (l_debug = 1) THEN
696 debug_print('Converting all the missing fields in the input reservation record to NULL');
697 END IF;
698 --Convert all the missing fileds in the input rsv record to NULL
699 inv_reservation_pvt.convert_missing_to_null ( p_rsv_rec => l_rsv_rec1, x_rsv_rec => l_rsv_rec);
700 IF l_return_status <> fnd_api.g_ret_sts_success THEN
701 IF (l_debug = 1) THEN
702 debug_print(' return error from inv_reservation_pvt.convert_missing_to_null '||l_return_status);
703 END IF;
704 RAISE fnd_api.g_exc_unexpected_error;
705 END IF;
706
707 --Print the input reservation record passed
708 inv_reservation_pvt.print_rsv_rec( p_rsv_rec => l_rsv_rec);
709
710 IF (l_debug = 1) THEN
711 debug_print('Handling prim, sec, rsv uom and qty');
712 END IF;
713 --Handle uom conversion
714 inv_reservation_pvt.convert_quantity( x_return_status => l_return_status, px_rsv_rec => l_rsv_rec);
715 IF l_return_status <> fnd_api.g_ret_sts_success THEN
716 IF (l_debug = 1) THEN
717 debug_print(' return error from inv_reservation_pvt.convert_quantity '||l_return_status);
718 END IF;
719 RAISE fnd_api.g_exc_unexpected_error;
720 END IF;
721
722 --Query reservations only on the basis of demand source line id from the input.
723 --This will fetch all reservation records for the respective demand source line
724 --which is processed later
725 l_query_input.demand_source_line_id := l_rsv_rec.demand_source_line_id;
726 l_query_input.demand_source_header_id := l_rsv_rec.demand_source_header_id;
727 l_query_input.inventory_item_Id := l_rsv_rec.inventory_item_Id;
728 l_query_input.organization_id := l_rsv_rec.organization_id;
729 l_query_input.supply_source_type_id := inv_reservation_global.g_source_type_inv;
730 l_query_input.demand_source_type_id := l_rsv_rec.demand_source_type_id;
731
732
733 IF (l_debug = 1) THEN
734 debug_print('Demand Source line id :' || l_rsv_rec.demand_source_line_id);
735 debug_print('Reservation Status passed is : ' || p_rsv_status);
736 END IF;
737
738 --l_query_input.ship_ready_flag := NULL;
739 --l_query_input.staged_flag := NULL;
740
741 inv_reservation_pub.query_reservation(
742 p_api_version_number => 1.0
743 , p_init_msg_lst => fnd_api.g_true
744 , x_return_status => l_return_status
745 , x_msg_count => l_msg_count
746 , x_msg_data => l_msg_data
747 , p_query_input => l_query_input
748 , x_mtl_reservation_tbl => l_mtl_reservation
749 , x_mtl_reservation_tbl_count => l_mtl_reservation_count
750 , x_error_code => l_error_code
751 );
752
753 -- Return an error if the query reservations call failed
754 IF l_return_status <> fnd_api.g_ret_sts_success THEN
755 IF (l_debug = 1) THEN
756 debug_print(' return error from query reservation: '||l_return_status);
757 END IF;
758 fnd_message.set_name('INV', 'INV_QRY_RSV_FAILED');
759 fnd_msg_pub.ADD;
760 RAISE fnd_api.g_exc_unexpected_error;
761 END IF;
762
763 IF l_mtl_reservation_count > 0 THEN
764 --Calculate the total primary reservation qty by summing up all the
765 --non staged reservations for the demand source line
766 --save all the non staged rsv records in a different variable
767 FOR i IN l_mtl_reservation.first..l_mtl_reservation.last
768 LOOP
769 l_staged_flag := l_mtl_reservation(i).staged_flag;
770 IF NVL(l_staged_flag, 'N') = 'N' THEN
771 l_rsv_index := l_rsv_index + 1;
772 l_mtl_reservation_non_staged(l_rsv_index) := l_mtl_reservation(i);
773 l_primary_reservation_qty := l_primary_reservation_qty + l_mtl_reservation(i).primary_reservation_quantity;
774 l_secondary_reservation_qty := l_secondary_reservation_qty + l_mtl_reservation(i).secondary_reservation_quantity;
775 END IF;
776 END LOOP;
777
778 l_mtl_rsv_non_staged_count := l_mtl_reservation_non_staged.Count;
779
780 IF (l_debug = 1) THEN
781 debug_print('No. of unstaged reservations : '|| l_mtl_rsv_non_staged_count);
782 debug_print('Primary reservation qty : ' || l_primary_reservation_qty);
783 debug_print('Secondary reservation qty : ' || l_secondary_reservation_qty);
784 END IF;
785
786 ELSE
787 IF (l_debug = 1) THEN
788 debug_print('No reservations exists for the demand source line : '|| l_rsv_rec.demand_source_line_id);
789 debug_print('Return without processing any reservations');
790 END IF;
791 x_return_status := fnd_api.g_ret_sts_success;
792 RETURN;
793 END IF;
794
795 IF l_mtl_rsv_non_staged_count <= 0 THEN
796 IF (l_debug = 1) THEN
797 debug_print('No Unstaged reservations exists for the demand source line : '|| l_rsv_rec.demand_source_line_id);
798 debug_print('Return without processing any reservations');
799 END IF;
800 x_return_status := fnd_api.g_ret_sts_success;
801 RETURN;
802 END IF;
803
804 -- set the item cache
805 l_return_value := INV_CACHE.set_item_rec (l_rsv_rec.organization_id, l_rsv_rec.inventory_item_id);
806 If NOT l_return_value THEN
807 IF (l_debug = 1) THEN
808 debug_print('Exception occurred while setting the item cache');
809 END IF;
810 RAISE fnd_api.g_exc_unexpected_error;
811 End If;
812
813 l_primary_uom := INV_CACHE.item_rec.primary_uom_code;
814 l_secondary_uom := INV_CACHE.item_rec.secondary_uom_code;
815 l_revision_control_code := INV_CACHE.item_rec.revision_qty_control_code;
816 l_lot_control_code := INV_CACHE.item_rec.lot_control_code;
817 l_serial_number_control_code := INV_CACHE.item_rec.serial_number_control_code;
818
819 IF (l_debug = 1) THEN
820 debug_print('Printing values from the item cache');
821 debug_print('l_primary_uom : '|| l_primary_uom);
822 debug_print('l_secondary_uom : '|| l_secondary_uom);
823 debug_print('l_revision_control_code : '|| l_revision_control_code);
824 debug_print('l_lot_control_code : '|| l_lot_control_code);
825 debug_print('l_serial_number_control_code : '|| l_serial_number_control_code);
826 END IF;
827
828 -- set the rev passed as null if item is not rev controlled
829 IF Nvl(l_revision_control_code, 1) = 1 THEN
830 l_rsv_rec.revision := NULL;
831 END IF;
832
833 -- set the lot passed as null if item is not lot controlled
834 IF l_lot_control_code = 1 THEN
835 l_rsv_rec.lot_number := NULL;
836 END IF;
837
838 -- set the serial passed as null if item is not serial controlled
839 IF l_serial_number_control_code = 1 THEN
840 l_serial_number := l_dummy_sn;
841 END IF;
842
843 --sort reservations only if the non staged reservations count
844 --is more than 1
845 IF l_mtl_rsv_non_staged_count > 1 THEN
846
847 -- Reservation table needs to be sorted as per the order
848 -- mentioned in sort_reservation helper procedure
849 inv_rsv_detail_stage_pvt.Sort_Reservation (
850 p_mtl_reservation => l_mtl_reservation_non_staged,
851 p_rsv_rec => l_rsv_rec,
852 x_return_status => l_return_status,
853 x_msg_count => l_msg_count,
854 x_msg_data => l_msg_data
855 );
856
857 IF l_return_status <> fnd_api.g_ret_sts_success THEN
858 IF (l_debug = 1) THEN
859 debug_print(' return error from sort reservation: '||l_return_status);
860 END IF;
861 RAISE fnd_api.g_exc_unexpected_error;
862 END IF;
863
864 END IF;
865
866 -- Call to check whether reservations are allowed or not allowed
867 -- This checks whether the input sku is reservable or not
868 l_reservation_allowed := is_reservation_allowed (l_rsv_rec);
869
870 IF (l_debug = 1) THEN
871 IF l_reservation_allowed = 1 THEN
872 debug_print('Reservations are allowed');
873 ELSE
874 debug_print('Reservations are not allowed');
875 END IF;
876 END IF;
877
878 -- If p_rsv_status is passed as DETAIL then detail the reservation with the SKU passed
879 -- If p_rsv_status is passed as STAGE then detail as well as stage the reservation with the SKU passed
880 -- If the prim rsv qty passed is >= total prim reserved qty then delete all the non staged reservations
881 -- against the demand source line and create a new detailed reservation with the new prim rsv qty and sku
882 -- If the prim rsv qty passed is < total prim reserved qty then we will have to split the reservations
883 -- Splitting of reservations is done in 2 steps
884 -- For example, we have 2 reservations (r1, r2) records against a demand source line with qty 6 and 4 resp
885 -- So total prim rsv qty = 10 and qty to be detailed or staged is 5
886 -- reduce 5 from r1 (update r1 to 1) and create a new detailed rsv with 5 qty and sku passed
887 -- or delete r2 with 4 qty, reduce r1 by 4 qty and create a new detailed rsv with 5 qty and sku passed
888
889 IF p_rsv_status IN ('DETAIL','STAGE') THEN
890
891 IF (l_debug = 1) THEN
892 debug_print('l_rsv_rec.primary_reservation_quantity : '||l_rsv_rec.primary_reservation_quantity);
893 debug_print('l_primary_reservation_qty : '||l_primary_reservation_qty);
894 END IF;
895
896 IF l_rsv_rec.primary_reservation_quantity >= l_primary_reservation_qty THEN
897 IF (l_debug = 1) THEN
898 debug_print('l_rsv_rec.primary_reservation_quantity >= l_primary_reservation_qty ');
899 END IF;
900
901 FOR i IN l_mtl_reservation_non_staged.first..l_mtl_reservation_non_staged.last
902 LOOP
903 IF (l_debug = 1) THEN
904 debug_print('Deleting reservation - reservation id :'||l_mtl_reservation_non_staged(i).reservation_id);
905 END IF;
906
907 inv_reservation_pub.delete_reservation
908 ( p_api_version_number => 1.0
909 , p_init_msg_lst => fnd_api.g_false
910 , x_return_status => l_return_status
911 , x_msg_count => l_msg_count
912 , x_msg_data => l_msg_data
913 , p_rsv_rec => l_mtl_reservation_non_staged(i)
914 , p_serial_number => l_dummy_sn
915 );
916
917 -- Return an error if the delete reservations call failed
918 IF l_return_status <> fnd_api.g_ret_sts_success THEN
919 IF (l_debug = 1) THEN
920 debug_print(' return error from delete reservation: '||l_return_status);
921 END IF;
922 RAISE fnd_api.g_exc_unexpected_error;
923 END IF;
924 END LOOP;
925
926 ELSE -- l_rsv_rec.reservation_quantity < l_primary_reservation_qty
927 IF (l_debug = 1) THEN
928 debug_print('l_rsv_rec.primary_reservation_quantity < l_primary_reservation_qty ');
929 debug_print('l_remaining_reservation_qty :' || l_remaining_reservation_qty);
930 END IF;
931
932 l_remaining_reservation_qty := l_rsv_rec.primary_reservation_quantity;
933
934 FOR i IN l_mtl_reservation_non_staged.first..l_mtl_reservation_non_staged.last
935 LOOP
936
937 IF (l_debug = 1) THEN
938 debug_print('l_mtl_reservation_non_staged(i).primary_reservation_quantity :'
939 || l_mtl_reservation_non_staged(i).primary_reservation_quantity);
940 debug_print('l_remaining_reservation_qty :' || l_remaining_reservation_qty);
941 END IF;
942
943 IF l_remaining_reservation_qty >= l_mtl_reservation_non_staged(i).primary_reservation_quantity THEN
944 IF (l_debug = 1) THEN
945 debug_print('l_remaining_reservation_qty >= l_mtl_reservation_non_staged(i).primary_reservation_quantity');
946 debug_print('Deleting reservation - reservation id :' || l_mtl_reservation_non_staged(i).reservation_id);
947 END IF;
948
949 inv_reservation_pub.delete_reservation
950 ( p_api_version_number => 1.0
951 , p_init_msg_lst => fnd_api.g_false
952 , x_return_status => l_return_status
953 , x_msg_count => l_msg_count
954 , x_msg_data => l_msg_data
955 , p_rsv_rec => l_mtl_reservation_non_staged(i)
956 , p_serial_number => l_dummy_sn
957 );
958
959 -- Return an error if the delete reservations call failed
960 IF l_return_status <> fnd_api.g_ret_sts_success THEN
961 IF (l_debug = 1) THEN
962 debug_print(' return error from delete reservation: '||l_return_status);
963 END IF;
964 RAISE fnd_api.g_exc_unexpected_error;
965 END IF;
966
967
968 ELSE --IF l_remaining_reservation_qty < l_mtl_reservation(i).reservation_quantity
969
970 IF (l_debug = 1) THEN
971 debug_print('l_remaining_reservation_qty < l_mtl_reservation(i).reservation_quantity');
972 END IF;
973
974 l_new_prim_rsv_quantity := l_mtl_reservation_non_staged(i).primary_reservation_quantity - l_remaining_reservation_qty;
975 --l_new_sec_rsv_quantity := l_mtl_reservation_non_staged(i).secondary_reservation_quantity - l_remaining_reservation_qty2;
976
977 l_original_rsv_record := l_mtl_reservation_non_staged(i);
978 l_to_rsv_record := l_mtl_reservation_non_staged(i);
979 l_to_rsv_record.primary_reservation_quantity := l_new_prim_rsv_quantity;
980 --l_to_rsv_record.reservation_quantity := l_new_rsv_quantity;
981 --l_to_rsv_record.secondary_reservation_quantity := l_new_sec_rsv_quantity;
982
983 IF (l_debug = 1) THEN
984 debug_print('Handling rsv qty and sec rsv qty for the reservation record which is to be updated');
985 END IF;
986
987 inv_reservation_pvt.convert_quantity( x_return_status => l_return_status, px_rsv_rec => l_to_rsv_record);
988 IF l_return_status <> fnd_api.g_ret_sts_success THEN
989 IF (l_debug = 1) THEN
990 debug_print(' return error from inv_reservation_pvt.convert_quantity '||l_return_status);
991 END IF;
992 RAISE fnd_api.g_exc_unexpected_error;
993 END IF;
994
995
996 IF (l_debug = 1) THEN
997 debug_print('Update reservation - reservation id : ' || l_mtl_reservation_non_staged(i).reservation_id);
998 debug_print('Old primary Qty : ' || l_original_rsv_record.primary_reservation_quantity);
999 debug_print('New primary Qty : ' || l_to_rsv_record.primary_reservation_quantity);
1000 debug_print('Updating reservation');
1001 END IF;
1002
1003 -- This update will always reduce the primary qty.
1004 -- Unmarking of the serial in case of serial controlled items is handled by the api
1005 inv_reservation_pub.update_reservation(
1006 p_api_version_number => 1.0
1007 , p_init_msg_lst => fnd_api.g_false
1008 , x_return_status => l_return_status
1009 , x_msg_count => l_msg_count
1010 , x_msg_data => l_msg_data
1011 , p_original_rsv_rec => l_original_rsv_record
1012 , p_to_rsv_rec => l_to_rsv_record
1013 , p_original_serial_number => l_dummy_sn
1014 , p_to_serial_number => l_dummy_sn
1015 , p_validation_flag => fnd_api.g_true
1016 , p_over_reservation_flag => 2
1017 );
1018
1019 IF l_return_status <> fnd_api.g_ret_sts_success THEN
1020 IF (l_debug = 1) THEN
1021 debug_print('return error from update reservation: '||l_return_status);
1022 END IF;
1023 fnd_message.set_name('INV', 'INV_UPD_RSV_FAILED');
1024 fnd_msg_pub.ADD;
1025 RAISE fnd_api.g_exc_unexpected_error;
1026 END IF;
1027 END IF; --IF l_remaining_reservation_qty >= l_mtl_reservation_non_staged(i).reservation_quantity
1028
1029 l_remaining_reservation_qty := l_remaining_reservation_qty - l_mtl_reservation_non_staged(i).primary_reservation_quantity;
1030 --l_remaining_reservation_qty2 := l_remaining_reservation_qty2 - l_mtl_reservation_non_staged(i).secondary_reservation_quantity;
1031
1032 IF (l_debug = 1) THEN
1033 debug_print('l_remaining_reservation_qty :'||l_remaining_reservation_qty);
1034 END IF;
1035 EXIT WHEN l_remaining_reservation_qty <= 0 ;
1036 END LOOP;
1037
1038 END IF; --IF l_rsv_rec.reservation_quantity >= l_primary_reservation_qty
1039
1040 -- Check the atr for the sku passed for detailing/staging the reservation
1041 inv_rsv_detail_stage_pvt.Get_atr(
1042 p_rsv_rec => l_rsv_rec
1043 , x_atr => l_atr
1044 , x_return_status => l_return_status
1045 , x_msg_count => l_msg_count
1046 , x_msg_data => l_msg_data
1047 );
1048
1049 IF l_return_status <> fnd_api.g_ret_sts_success THEN
1050 IF (l_debug = 1) THEN
1051 debug_print(' return error from inv_rsv_detail_stage_pvt.Get_atr '||l_return_status);
1052 END IF;
1053 RAISE fnd_api.g_exc_unexpected_error;
1054 ELSE
1055 IF (l_debug = 1) THEN
1056 debug_print(' Atr for the given sku is : '||l_atr);
1057 END IF;
1058 END IF;
1059
1060
1061 -- Create a new detailed reservation in any case : over picking / split reservation scenarios
1062 -- Detailed/ Staged level reservations will not be created if Subinventory or Locator or Lot is non reservable
1063 -- or atr for the given sku is <= 0
1064
1065 IF l_reservation_allowed = 1 AND l_atr > 0 THEN
1066 l_create_rsv_rec.reservation_id := NULL;
1067 l_create_rsv_rec.requirement_date := Nvl(l_rsv_rec.requirement_date, SYSDATE);
1068 l_create_rsv_rec.organization_id := l_rsv_rec.organization_id;
1069 l_create_rsv_rec.inventory_item_id := l_rsv_rec.inventory_item_id;
1070 l_create_rsv_rec.demand_source_type_id := l_rsv_rec.demand_source_type_id;
1071 l_create_rsv_rec.demand_source_name := l_rsv_rec.demand_source_name;
1072 l_create_rsv_rec.demand_source_header_id := l_rsv_rec.demand_source_header_id;
1073 l_create_rsv_rec.demand_source_line_id := l_rsv_rec.demand_source_line_id;
1074 l_create_rsv_rec.demand_source_delivery := NULL;
1075 l_create_rsv_rec.primary_uom_code := l_rsv_rec.primary_uom_code;
1076 l_create_rsv_rec.secondary_uom_code := l_rsv_rec.secondary_uom_code;
1077 l_create_rsv_rec.primary_uom_id := NULL;
1078 l_create_rsv_rec.secondary_uom_id := NULL;
1079 l_create_rsv_rec.reservation_uom_code := l_rsv_rec.reservation_uom_code;
1080 l_create_rsv_rec.reservation_uom_id := NULL;
1081 l_create_rsv_rec.reservation_quantity := l_rsv_rec.reservation_quantity;
1082 l_create_rsv_rec.primary_reservation_quantity := l_rsv_rec.primary_reservation_quantity;
1083 l_create_rsv_rec.secondary_reservation_quantity := l_rsv_rec.secondary_reservation_quantity;
1084 l_create_rsv_rec.autodetail_group_id := NULL;
1085 l_create_rsv_rec.external_source_code := NULL;
1086 l_create_rsv_rec.external_source_line_id := NULL;
1087 l_create_rsv_rec.supply_source_type_id := inv_reservation_global.g_source_type_inv;
1088 l_create_rsv_rec.supply_source_header_id := NULL;
1089 l_create_rsv_rec.supply_source_line_id := NULL;
1090 l_create_rsv_rec.supply_source_name := NULL;
1091 l_create_rsv_rec.supply_source_line_detail := NULL;
1092 l_create_rsv_rec.revision := l_rsv_rec.revision;
1093 l_create_rsv_rec.subinventory_code := l_rsv_rec.subinventory_code;
1094 l_create_rsv_rec.subinventory_id := NULL;
1095 l_create_rsv_rec.locator_id := l_rsv_rec.locator_id ;
1096 l_create_rsv_rec.lot_number := l_rsv_rec.lot_number;
1097 l_create_rsv_rec.lpn_id := l_rsv_rec.lpn_id;
1098 l_create_rsv_rec.lot_number_id := NULL;
1099 l_create_rsv_rec.pick_slip_number := NULL;
1100 l_create_rsv_rec.attribute_category := NULL;
1101 l_create_rsv_rec.attribute1 := NULL;
1102 l_create_rsv_rec.attribute2 := NULL;
1103 l_create_rsv_rec.attribute3 := NULL;
1104 l_create_rsv_rec.attribute4 := NULL;
1105 l_create_rsv_rec.attribute5 := NULL;
1106 l_create_rsv_rec.attribute6 := NULL;
1107 l_create_rsv_rec.attribute7 := NULL;
1108 l_create_rsv_rec.attribute8 := NULL;
1109 l_create_rsv_rec.attribute9 := NULL;
1110 l_create_rsv_rec.attribute10 := NULL;
1111 l_create_rsv_rec.attribute11 := NULL;
1112 l_create_rsv_rec.attribute12 := NULL;
1113 l_create_rsv_rec.attribute13 := NULL;
1114 l_create_rsv_rec.attribute14 := NULL;
1115 l_create_rsv_rec.attribute15 := NULL;
1116 l_create_rsv_rec.ship_ready_flag := NULL;
1117 l_create_rsv_rec.staged_flag := NULL;
1118 l_create_rsv_rec.detailed_quantity := NULL; --l_rsv_rec.primary_reservation_quantity;
1119 l_create_rsv_rec.secondary_detailed_quantity := NULL; --l_rsv_rec.secondary_reservation_quantity;
1120
1121 IF (l_debug = 1) THEN
1122 debug_print('Calling create resrevation for creating the detailed reservation ');
1123 END IF;
1124
1125 inv_reservation_pub.create_reservation(
1126 p_api_version_number => 1.0
1127 , p_init_msg_lst => fnd_api.g_false
1128 , x_return_status => l_return_status
1129 , x_msg_count => l_msg_count
1130 , x_msg_data => l_msg_data
1131 , p_rsv_rec => l_create_rsv_rec
1132 , p_serial_number => l_serial_number
1133 , x_serial_number => l_serial_number1
1134 , p_partial_reservation_flag => fnd_api.g_true
1135 , p_force_reservation_flag => fnd_api.g_false
1136 , p_validation_flag => fnd_api.g_true
1137 , x_quantity_reserved => l_det_res_qty
1138 , x_reservation_id => l_det_res_id
1139 , p_over_reservation_flag => 2
1140 );
1141
1142 -- Return an error if the query reservation call failed
1143 IF l_return_status <> fnd_api.g_ret_sts_success THEN
1144 IF (l_debug = 1) THEN
1145 debug_print('return error from query reservation: '||l_return_status);
1146 END IF;
1147 fnd_message.set_name('INV', 'INV_QRY_RSV_FAILED');
1148 fnd_msg_pub.ADD;
1149 RAISE fnd_api.g_exc_unexpected_error;
1150 END IF;
1151
1152 IF (l_debug = 1) THEN
1153 debug_print('Detailed reservation created. Reservation Id : ' || l_det_res_id || ' and qty :' || l_det_res_qty);
1154 END IF;
1155
1156 l_query_det_rsv.reservation_id := l_det_res_id;
1157
1158 IF (l_debug = 1) THEN
1159 debug_print('Querying reservation before updating the detailed qty ');
1160 END IF;
1161 -- Query for the one reservation created for updating the det qty
1162 inv_reservation_pub.query_reservation(
1163 p_api_version_number => 1.0
1164 , p_init_msg_lst => fnd_api.g_true
1165 , x_return_status => l_return_status
1166 , x_msg_count => l_msg_count
1167 , x_msg_data => l_msg_data
1168 , p_query_input => l_query_det_rsv
1169 , x_mtl_reservation_tbl => l_mtl_reservation_detailed
1170 , x_mtl_reservation_tbl_count => l_mtl_rsv_detailed_count
1171 , x_error_code => l_error_code
1172 );
1173
1174 -- Return an error if the query reservations call failed
1175 IF l_return_status <> fnd_api.g_ret_sts_success THEN
1176 IF (l_debug = 1) THEN
1177 debug_print(' return error from query reservation: '||l_return_status);
1178 END IF;
1179 fnd_message.set_name('INV', 'INV_QRY_RSV_FAILED');
1180 fnd_msg_pub.ADD;
1181 RAISE fnd_api.g_exc_unexpected_error;
1182 END IF;
1183
1184 l_mtl_reservation_det_qty := l_mtl_reservation_detailed(1) ;
1185 l_mtl_reservation_det_qty.detailed_quantity := l_det_res_qty; --l_rsv_rec.primary_reservation_quantity;
1186 l_mtl_reservation_det_qty.secondary_detailed_quantity := NULL; --l_rsv_rec.secondary_reservation_quantity;
1187
1188 IF (l_debug = 1) THEN
1189 debug_print('Updating the detailed qty and sec detailed qty fields ');
1190 END IF;
1191
1192 --This update is meant only to update the det qty and sec det qty
1193 inv_reservation_pub.update_reservation(
1194 p_api_version_number => 1.0
1195 , p_init_msg_lst => fnd_api.g_false
1196 , x_return_status => l_return_status
1197 , x_msg_count => l_msg_count
1198 , x_msg_data => l_msg_data
1199 , p_original_rsv_rec => l_mtl_reservation_detailed(1)
1200 , p_to_rsv_rec => l_mtl_reservation_det_qty
1201 , p_original_serial_number => l_dummy_sn
1202 , p_to_serial_number => l_dummy_sn
1203 , p_validation_flag => fnd_api.g_true
1204 , p_over_reservation_flag => 2
1205 );
1206
1207 IF l_return_status <> fnd_api.g_ret_sts_success THEN
1208 IF (l_debug = 1) THEN
1209 debug_print('return error from update reservation: '||l_return_status);
1210 END IF;
1211 fnd_message.set_name('INV', 'INV_UPD_RSV_FAILED');
1212 fnd_msg_pub.ADD;
1213 RAISE fnd_api.g_exc_unexpected_error;
1214 END IF;
1215
1216 IF (l_debug = 1) THEN
1217 debug_print('Reservation successfully detailed ');
1218 END IF;
1219
1220 END IF; -- IF l_reservation_allowed = 1 AND l_atr > 0
1221
1222 END IF; -- detail,stage
1223
1224 --If rsv status is 'STAGE' then detail the reservation to the sku passed and then stage it
1225 IF p_rsv_status = 'STAGE' THEN
1226
1227 IF (l_debug = 1) THEN
1228 debug_print('Staging the detailed reservation created');
1229 END IF;
1230
1231 -- l_det_res_id would be null for non reservable sub, lot or loc
1232 -- and for cases wherein the atr is <= 0
1233 IF l_det_res_id IS NOT NULL THEN
1234 l_query_det_rsv.reservation_id := l_det_res_id;
1235
1236 IF (l_debug = 1) THEN
1237 debug_print('Querying reservation before staging ');
1238 END IF;
1239 -- Query for the one detailed reservation created
1240 inv_reservation_pub.query_reservation(
1241 p_api_version_number => 1.0
1242 , p_init_msg_lst => fnd_api.g_true
1243 , x_return_status => l_return_status
1244 , x_msg_count => l_msg_count
1245 , x_msg_data => l_msg_data
1246 , p_query_input => l_query_det_rsv
1247 , x_mtl_reservation_tbl => l_mtl_reservation_detailed
1248 , x_mtl_reservation_tbl_count => l_mtl_rsv_detailed_count
1249 , x_error_code => l_error_code
1250 );
1251
1252 -- Return an error if the query reservations call failed
1253 IF l_return_status <> fnd_api.g_ret_sts_success THEN
1254 IF (l_debug = 1) THEN
1255 debug_print(' return error from query reservation: '||l_return_status);
1256 END IF;
1257 fnd_message.set_name('INV', 'INV_QRY_RSV_FAILED');
1258 fnd_msg_pub.ADD;
1259 RAISE fnd_api.g_exc_unexpected_error;
1260 END IF;
1261
1262 l_mtl_reservation_staged := l_mtl_reservation_detailed(1) ;
1263 l_mtl_reservation_staged.staged_flag := 'Y';
1264 l_mtl_reservation_staged.ship_ready_flag := 1;
1265 l_mtl_reservation_staged.detailed_quantity := 0;
1266 l_mtl_reservation_staged.secondary_detailed_quantity := 0;
1267
1268 IF (l_debug = 1) THEN
1269 debug_print('Updating the staging fields ');
1270 END IF;
1271
1272 inv_reservation_pub.update_reservation(
1273 p_api_version_number => 1.0
1274 , p_init_msg_lst => fnd_api.g_false
1275 , x_return_status => l_return_status
1276 , x_msg_count => l_msg_count
1277 , x_msg_data => l_msg_data
1278 , p_original_rsv_rec => l_mtl_reservation_detailed(1)
1279 , p_to_rsv_rec => l_mtl_reservation_staged
1280 , p_original_serial_number => l_dummy_sn
1281 , p_to_serial_number => l_dummy_sn
1282 , p_validation_flag => fnd_api.g_true
1283 , p_over_reservation_flag => 2
1284 );
1285
1286 IF l_return_status <> fnd_api.g_ret_sts_success THEN
1287 IF (l_debug = 1) THEN
1288 debug_print('return error from update reservation: '||l_return_status);
1289 END IF;
1290 fnd_message.set_name('INV', 'INV_UPD_RSV_FAILED');
1291 fnd_msg_pub.ADD;
1292 RAISE fnd_api.g_exc_unexpected_error;
1293 END IF;
1294
1295 END IF; --IF l_det_res_id IS NOT NULL THEN
1296
1297 END IF; --IF p_rsv_status = 'STAGE' THEN
1298
1299 x_return_status := l_return_status;
1300
1301 EXCEPTION
1302
1303 WHEN fnd_api.g_exc_error THEN
1304 ROLLBACK TO process_reservation_ds;
1305 x_return_status := fnd_api.g_ret_sts_error;
1306 IF (l_debug = 1) THEN
1307 debug_print('Error occurred in Process Reservation : '||l_return_status);
1308 END IF;
1309 fnd_msg_pub.count_and_get(p_count => x_msg_count, p_data => x_msg_data);
1310
1311
1312 WHEN fnd_api.g_exc_unexpected_error THEN
1313 ROLLBACK TO process_reservation_ds;
1314 x_return_status := fnd_api.g_ret_sts_unexp_error;
1315 IF (l_debug = 1) THEN
1316 debug_print('Unexpected error occurred in Process Reservation : '||l_return_status);
1317 END IF;
1318 fnd_msg_pub.count_and_get(p_count => x_msg_count, p_data => x_msg_data);
1319
1320 WHEN OTHERS THEN
1321 ROLLBACK TO process_reservation_ds;
1322 x_return_status := fnd_api.g_ret_sts_unexp_error;
1323 IF (l_debug = 1) THEN
1324 debug_print('Unexpected error occurred in Process Reservation : '||l_return_status);
1325 END IF;
1326 fnd_msg_pub.count_and_get(p_count => x_msg_count, p_data => x_msg_data);
1327
1328 END process_reservation;
1329
1330 END inv_rsv_detail_stage_pvt;
[Home] [Help]