[Home] [Help]
PACKAGE BODY: APPS.INV_MISSING_QTY_ACTIONS_ENGINE
Source
1 PACKAGE BODY inv_missing_qty_actions_engine AS
2 /* $Header: INVMQAEB.pls 120.11.12020000.4 2012/12/19 10:25:19 ptian ship $ */
3
4 -- Variables for Debug Messages
5 g_pkg_name CONSTANT VARCHAR2(50) := 'INV_MISSING_QTY_ACTIONS_ENGINE';
6 g_version_printed BOOLEAN := FALSE;
7 g_exception CONSTANT NUMBER := 1;
8 g_error CONSTANT NUMBER := 3;
9 g_info CONSTANT NUMBER := 5;
10
11 PROCEDURE print_debug(p_message VARCHAR2, p_module VARCHAR2, p_level NUMBER) IS
12 BEGIN
13 IF NOT g_version_printed THEN
14 inv_log_util.trace('$Header: INVMQAEB.pls 120.11.12020000.4 2012/12/19 10:25:19 ptian ship $', g_pkg_name);
15 g_version_printed := TRUE;
16 END IF;
17
18 inv_log_util.trace(p_message, g_pkg_name || '.' || p_module, p_level);
19 END print_debug;
20
21 PROCEDURE get_item_controls(
22 x_return_status OUT NOCOPY VARCHAR2
23 , x_lot_control_code OUT NOCOPY NUMBER
24 , x_serial_control_code OUT NOCOPY NUMBER
25 , p_transaction_temp_id NUMBER
26 , p_mo_line_id NUMBER
27 ) IS
28 CURSOR c_item_controls IS
29 SELECT msi.lot_control_code, msi.serial_number_control_code
30 FROM mtl_system_items msi, mtl_material_transactions_temp mmtt
31 WHERE p_transaction_temp_id IS NOT NULL
32 AND mmtt.transaction_temp_id = p_transaction_temp_id
33 AND msi.inventory_item_id = mmtt.inventory_item_id
34 AND msi.organization_id = mmtt.organization_id
35 UNION ALL
36 SELECT msi.lot_control_code, msi.serial_number_control_code
37 FROM mtl_system_items msi, mtl_txn_request_lines mtrl
38 WHERE p_transaction_temp_id IS NULL AND p_mo_line_id IS NOT NULL
39 AND mtrl.line_id = p_mo_line_id
40 AND msi.inventory_item_id = mtrl.inventory_item_id
41 AND msi.organization_id = mtrl.organization_id;
42 BEGIN
43 x_return_status := fnd_api.g_ret_sts_success;
44
45 OPEN c_item_controls;
46 FETCH c_item_controls INTO x_lot_control_code, x_serial_control_code;
47 IF c_item_controls%NOTFOUND THEN
48 x_return_status := fnd_api.g_ret_sts_error;
49 END IF;
50 CLOSE c_item_controls;
51 END get_item_controls;
52
53 PROCEDURE fill_cycle_count_rsv_rec(
54 x_rsv_rec OUT NOCOPY inv_reservation_global.mtl_reservation_rec_type
55 , p_organization_id NUMBER
56 , p_inventory_item_id NUMBER
57 , p_revision VARCHAR2
58 , p_lot_number VARCHAR2
59 , p_subinventory_code VARCHAR2
60 , p_locator_id NUMBER
61 , p_primary_uom_code VARCHAR2
62 --INVCONV kkillams
63 ,p_secondary_uom_code VARCHAR2 DEFAULT NULL
64 --END INVCONV kkillams
65 ) IS
66 BEGIN
67 x_rsv_rec.inventory_item_id := p_inventory_item_id;
68 x_rsv_rec.organization_id := p_organization_id;
69 x_rsv_rec.revision := p_revision;
70 x_rsv_rec.lot_number := p_lot_number;
71 x_rsv_rec.subinventory_code := p_subinventory_code;
72 x_rsv_rec.locator_id := p_locator_id;
73 x_rsv_rec.primary_uom_code := p_primary_uom_code;
74 x_rsv_rec.reservation_uom_code := p_primary_uom_code;
75 x_rsv_rec.supply_source_type_id := inv_reservation_global.g_source_type_inv;
76 x_rsv_rec.demand_source_type_id := inv_reservation_global.g_source_type_cycle_count;
77 x_rsv_rec.demand_source_header_id := -1;
78 x_rsv_rec.demand_source_line_id := -1;
79
80 -- Fill the Required Fields expected by Create Reservations API
81 x_rsv_rec.reservation_id := NULL;
82 x_rsv_rec.reservation_quantity := NULL;
83 x_rsv_rec.primary_reservation_quantity := NULL;
84 x_rsv_rec.detailed_quantity := 0;
85 x_rsv_rec.requirement_date := trunc(SYSDATE);
86 x_rsv_rec.primary_uom_id := NULL;
87 x_rsv_rec.reservation_uom_id := NULL;
88 x_rsv_rec.autodetail_group_id := NULL;
89 x_rsv_rec.external_source_code := NULL;
90 x_rsv_rec.external_source_line_id := NULL;
91 x_rsv_rec.demand_source_delivery := NULL;
92 x_rsv_rec.demand_source_name := NULL;
93 x_rsv_rec.supply_source_header_id := NULL;
94 x_rsv_rec.supply_source_line_id := NULL;
95 x_rsv_rec.supply_source_name := NULL;
96 x_rsv_rec.supply_source_line_detail := NULL;
97 x_rsv_rec.lot_number_id := NULL;
98 x_rsv_rec.subinventory_id := NULL;
99 x_rsv_rec.pick_slip_number := NULL;
100 x_rsv_rec.lpn_id := NULL;
101 x_rsv_rec.attribute_category := NULL;
102 x_rsv_rec.attribute1 := NULL;
103 x_rsv_rec.attribute2 := NULL;
104 x_rsv_rec.attribute3 := NULL;
105 x_rsv_rec.attribute4 := NULL;
106 x_rsv_rec.attribute5 := NULL;
107 x_rsv_rec.attribute6 := NULL;
108 x_rsv_rec.attribute7 := NULL;
109 x_rsv_rec.attribute8 := NULL;
110 x_rsv_rec.attribute9 := NULL;
111 x_rsv_rec.attribute10 := NULL;
112 x_rsv_rec.attribute11 := NULL;
113 x_rsv_rec.attribute12 := NULL;
114 x_rsv_rec.attribute13 := NULL;
115 x_rsv_rec.attribute14 := NULL;
116 x_rsv_rec.attribute15 := NULL;
117 x_rsv_rec.ship_ready_flag := NULL;
118 --INVCONV kkillams
119 x_rsv_rec.secondary_uom_code := p_secondary_uom_code;
120 x_rsv_rec.secondary_reservation_quantity := NULL;
121 x_rsv_rec.secondary_uom_id := NULL;
122 --END INVCONV kkillams
123 END fill_cycle_count_rsv_rec;
124
125
126 PROCEDURE remove_confirmed(
127 x_return_status OUT NOCOPY VARCHAR2
128 , p_transaction_temp_id NUMBER
129 , p_lot_control_code NUMBER
130 , p_serial_control_code NUMBER
131 ) IS
132 l_api_name VARCHAR2(30) := 'REMOVE_CONFIRM';
133 l_debug NUMBER := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'), 0);
134 l_updated_count NUMBER := 0;
135 l_deleted_count NUMBER := 0;
136 BEGIN
137 x_return_status := fnd_api.g_ret_sts_success;
138
139 IF l_debug = 1 THEN
140 print_debug('Updating Temp table to remove Confirmed Lots/Serials', l_api_name, g_info);
141 END IF;
142
143 IF p_lot_control_code = 2 AND p_serial_control_code IN(1, 6) THEN
144 UPDATE mtl_allocations_gtmp mat
145 SET (primary_quantity, transaction_quantity,secondary_quantity)
146 = (SELECT mat.primary_quantity - nvl(SUM(mtlt.primary_quantity),0)
147 , mat.transaction_quantity - nvl(SUM(mtlt.transaction_quantity),0)
148 , DECODE(NVL(mat.secondary_quantity,0) - NVL(SUM(mtlt.secondary_quantity),0)
149 ,0,NULL,
150 NVL(mat.secondary_quantity,0) - NVL(SUM(mtlt.secondary_quantity),0)
151 ) --INVCONV kkillams
152 FROM mtl_transaction_lots_temp mtlt
153 WHERE mtlt.transaction_temp_id = p_transaction_temp_id
154 AND mtlt.lot_number = mat.lot_number)
155
156 WHERE mat.transaction_temp_id = p_transaction_temp_id;
157 l_updated_count := SQL%ROWCOUNT;
158
159 DELETE mtl_allocations_gtmp
160 WHERE transaction_temp_id = p_transaction_temp_id AND primary_quantity <= 0;
161 l_deleted_count := SQL%ROWCOUNT;
162 ELSIF p_lot_control_code = 1 AND p_serial_control_code NOT IN(1, 6) THEN
163 DELETE mtl_allocations_gtmp
164 WHERE transaction_temp_id = p_transaction_temp_id
165 AND serial_number IN( SELECT msn.serial_number
166 FROM mtl_serial_numbers msn
167 WHERE msn.group_mark_id = p_transaction_temp_id);
168 l_deleted_count := SQL%ROWCOUNT;
169 ELSIF p_lot_control_code = 2 AND p_serial_control_code NOT IN(1, 6) THEN
170 DELETE mtl_allocations_gtmp
171 WHERE transaction_temp_id = p_transaction_temp_id
172 AND serial_number IN( SELECT msn.serial_number
173 FROM mtl_transaction_lots_temp mtlt, mtl_serial_numbers msn
174 WHERE mtlt.transaction_temp_id = p_transaction_temp_id
175 AND msn.group_mark_id = mtlt.serial_transaction_temp_id);
176 l_deleted_count := SQL%ROWCOUNT;
177
178 IF SQL%ROWCOUNT = 0 THEN
179 UPDATE mtl_allocations_gtmp mat
180 SET (primary_quantity, transaction_quantity,secondary_quantity)
181 = (SELECT mat.primary_quantity - nvl(SUM(mtlt.primary_quantity),0)
182 , mat.transaction_quantity - nvl(SUM(mtlt.transaction_quantity),0)
183 , DECODE(NVL(mat.secondary_quantity,0) - NVL(SUM(mtlt.secondary_quantity),0)
184 ,0,NULL,
185 NVL(mat.secondary_quantity,0) - NVL(SUM(mtlt.secondary_quantity),0)
186 ) --INVCONV kkillams
187
188 FROM mtl_transaction_lots_temp mtlt
189 WHERE mtlt.transaction_temp_id = p_transaction_temp_id
190 AND mtlt.lot_number = mat.lot_number)
191 WHERE mat.transaction_temp_id = p_transaction_temp_id;
192 l_updated_count := SQL%ROWCOUNT;
193
194 DELETE mtl_allocations_gtmp
195 WHERE transaction_temp_id = p_transaction_temp_id AND primary_quantity <= 0;
196 l_deleted_count := SQL%ROWCOUNT;
197 END IF;
198 END IF;
199
200 IF l_debug = 1 THEN
201 print_debug('Lot Control Code = ' || p_lot_control_code || ' : Serial Control Code = ' || p_serial_control_code, l_api_name, g_info);
202 print_debug('# of Records Updated = ' || l_updated_count, l_api_name, g_info);
203 print_debug('# of Records Deleted = ' || l_deleted_count, l_api_name, g_info);
204 print_debug('Updated Temp Table to contain Unconfirmed Lots/Serials', l_api_name, g_info);
205 END IF;
206 END remove_confirmed;
207
208 PROCEDURE backorder_only(
209 x_return_status OUT NOCOPY VARCHAR2
210 , x_msg_data OUT NOCOPY VARCHAR2
211 , x_msg_count OUT NOCOPY NUMBER
212 , p_transaction_temp_id NUMBER
213 , p_quantity NUMBER
214 --INVCONV KKILLAMS
215 , p_secondary_quantity NUMBER DEFAULT NULL
216 --END INVCONV KKILLAMS
217 ) IS
218 l_debug NUMBER := NVL(fnd_profile.VALUE('INV_DEBUG_TRACE'), 0);
219 l_api_name VARCHAR2(30) := 'BACKORDER_ONLY';
220 l_from_rsv_rec inv_reservation_global.mtl_reservation_rec_type;
221 l_to_rsv_rec inv_reservation_global.mtl_reservation_rec_type;
222 l_dummy_sn inv_reservation_global.serial_number_tbl_type;
223 l_primary_qty NUMBER := p_quantity;
224 l_rsv_primary_qty NUMBER;
225 l_rsv_detailed_qty NUMBER;
226 l_ato_item NUMBER := 0;
227 l_mmtt_primary_qty_sum NUMBER := 0; /*Bug.4539851*/
228 --INVCONV KKILLAMS
229 l_res_secondary_qty mtl_reservations.secondary_reservation_quantity%TYPE;
230 l_sec_secondary_qty mtl_reservations.secondary_detailed_quantity%TYPE;
231 --END INVCONV KKILLAMS
232
233 CURSOR c_mmtt_info IS
234 SELECT mmtt.inventory_item_id
235 , mmtt.transaction_uom
236 , mmtt.reservation_id
237 , msi.primary_uom_code
238 , msi.replenish_to_order_flag
239 , msi.bom_item_type
240 , mmtt.organization_id -- 9758641
241 , mmtt.lot_number -- 9758641
242 , msi.tracking_quantity_ind -- 9758641
243 , mmtt.move_order_line_id -- 9896283
244 , msi.secondary_uom_code --INVCONV kkillams
245 FROM mtl_material_transactions_temp mmtt, mtl_system_items msi
246 WHERE mmtt.transaction_temp_id = p_transaction_temp_id
247 AND msi.inventory_item_id = mmtt.inventory_item_id
248 AND msi.organization_id = mmtt.organization_id;
249
250 l_mmtt_info c_mmtt_info%ROWTYPE;
251
252 CURSOR c_rsv_info IS
253 SELECT primary_reservation_quantity, detailed_quantity
254 ,secondary_reservation_quantity, secondary_detailed_quantity --INVCONV kkillams
255 FROM mtl_reservations
256 WHERE reservation_id = l_mmtt_info.reservation_id;
257
258 /* Fix for Bug#9896283. Added cursor c_sai_info and made various changes to calculate secondary
259 quantities
260 */
261 CURSOR c_sai_info IS
262 SELECT NVL(SUM(transaction_qty), 0) transaction_qty ,
263 NVL(SUM(secondary_transaction_qty), 0) secondary_transaction_qty
264 FROM mtl_available_inventory_temp
265 WHERE transaction_qty <> 0 ;
266
267
268 l_sai_info c_sai_info%ROWTYPE ;
269 l_mmtt_secondary_qty_sum NUMBER := 0 ;
270
271 BEGIN
272 x_return_status := fnd_api.g_ret_sts_success;
273
274 OPEN c_mmtt_info;
275 FETCH c_mmtt_info INTO l_mmtt_info;
276 CLOSE c_mmtt_info;
277
278 IF l_mmtt_info.reservation_id IS NULL THEN
279 RETURN;
280 END IF;
281
282
283 /*Bug:4539851.Getting the sum of primary_quantity of all the allocations for the given
284 reservation_id*/
285
286 /* Fix for Bug#9896283 . Added secondary_transaction_quantity in following sql */
287
288 BEGIN
289 SELECT SUM(ABS(primary_quantity)), SUM(ABS(NVL(secondary_transaction_quantity, 0)))
290 INTO l_mmtt_primary_qty_sum,
291 l_mmtt_secondary_qty_sum
292 FROM mtl_material_transactions_temp
293 WHERE reservation_id= l_mmtt_info.reservation_id;
294 EXCEPTION
295 WHEN OTHERS THEN
296 RAISE fnd_api.g_exc_unexpected_error;
297 END;
298
299 /* Start fix for Bug#9758641. Also consider records from mtl_available_inventory_temp.
300 This is required since user can use select available inventory form to create new allocation
301 and update original allocation. Standard TMO form for sales order pick does not allow user
302 to enter brand new line on allocation block but user can use SAI form to do it. Hence the
303 need
304 */
305 OPEN c_sai_info;
306 FETCH c_sai_info INTO l_sai_info ;
307 IF c_sai_info%NOTFOUND then
308 l_sai_info.transaction_qty := 0 ;
309 l_sai_info.secondary_transaction_qty := 0 ;
310 END IF ;
311 CLOSE c_sai_info ;
312
313 print_debug('Parameter p_secondary_quantity = '|| p_secondary_quantity ,l_api_name,g_info);
314
315 print_debug(' Sum of primary_qty from mtl_available_inventory_temp : ' || l_sai_info.transaction_qty ,l_api_name,g_info);
316 print_debug(' Sum of secondary_qty from mtl_available_inventory_temp : ' || l_sai_info.secondary_transaction_qty ,l_api_name,g_info);
317
318 print_debug('sum of all allocations : '||l_mmtt_primary_qty_sum,l_api_name,g_info);
319 print_debug('sum of all secondary allocations : '||l_mmtt_secondary_qty_sum,l_api_name,g_info);
320
321
322 OPEN c_rsv_info;
323 FETCH c_rsv_info INTO l_rsv_primary_qty, l_rsv_detailed_qty
324 ,l_res_secondary_qty --INCONV KKILLAMS
325 ,l_sec_secondary_qty; --INCONV KKILLAMS
326
327 print_debug('Reservation primary quantity = '|| l_rsv_primary_qty ,l_api_name,g_info);
328 print_debug('Reservation primary detailed quantity = '|| l_rsv_detailed_qty,l_api_name,g_info);
329 print_debug('Reservation secondary quantity = '|| l_res_secondary_qty ,l_api_name,g_info);
330 print_debug('Reservation secondary detailed quantity = '|| l_sec_secondary_qty ,l_api_name,g_info);
331
332 IF c_rsv_info%NOTFOUND THEN
333 CLOSE c_rsv_info;
334 fnd_message.set_name('INV','INV-ROW-NOT-FOUND');
335 fnd_msg_pub.ADD;
336 /*Bug:4700706. When the reservation record is deleted somehow by this time we need not
337 deal with the reservation.So we just return. */
338 RETURN;
339 --RAISE fnd_api.g_exc_unexpected_error;
340 END IF;
341 CLOSE c_rsv_info;
342
343 -- Bug#2621481: For ATO Item, Retain the Reservation Qty
344 IF l_mmtt_info.bom_item_type = 4 AND l_mmtt_info.replenish_to_order_flag = 'Y' THEN
345 l_ato_item := 1;
346 END IF;
347
348 /*Bug:4539851. Removed the following code as we are directly getting the primary quantity
349 from MMTT into l_mmtt_primary_qty_sum. */
350 /*IF l_mmtt_info.transaction_uom <> l_mmtt_info.primary_uom_code THEN
351
352 l_primary_qty :=
353 inv_convert.inv_um_convert(l_mmtt_info.inventory_item_id, NULL, p_quantity,
354 l_mmtt_info.transaction_uom, l_mmtt_info.primary_uom_code, NULL, NULL);
355
356 END IF;
357 */
358
359
360 l_from_rsv_rec.reservation_id := l_mmtt_info.reservation_id;
361 /*Bug:4539851. Changed the logic to calculate l_to_rsv_rec.detailed_quantity
362 by taking minimum of current detailed quantity and the sum of transaction quantity
363 of all the allocations in MMTT of the Move Order line */
364 --l_to_rsv_rec.detailed_quantity := l_rsv_detailed_qty - l_primary_qty;
365
366 l_to_rsv_rec.detailed_quantity := least(l_rsv_detailed_qty , l_mmtt_primary_qty_sum);
367
368 print_debug('Detailed Quantity :'||l_to_rsv_rec.detailed_quantity,l_api_name,g_info);
369
370 IF l_ato_item <> 1 THEN
371 --l_to_rsv_rec.primary_reservation_quantity := l_rsv_primary_qty - l_primary_qty;
372 /*Bug:4539851. Changed the logic to calculate l_to_rsv_rec.primary_reservation_quantity
373 by taking min of current reservation quantity of the MO line and the sum of transaction quantity
374 of all the allocations in MMTT of the Move Order line */
375 --l_to_rsv_rec.primary_reservation_quantity := l_rsv_primary_qty - l_primary_qty;
376
377 l_to_rsv_rec.primary_reservation_quantity := least(l_rsv_primary_qty , l_mmtt_primary_qty_sum);
378
379 print_debug('Primary Reservation Qty:'||l_to_rsv_rec.primary_reservation_quantity,l_api_name,g_info);
380 END IF;
381
382 --INVCONV KKILLAMS
383
384 /* Fix for Bug#9758641. Need to recalculate secondary */
385 /* Fix for Bug#9896283. Populate secondary quantity from existing secondary quantities from reservation and mmtt */
386
387
388 IF l_sai_info.transaction_qty > 0 THEN
389
390 l_to_rsv_rec.detailed_quantity := least(l_rsv_detailed_qty , l_sai_info.transaction_qty );
391 l_to_rsv_rec.secondary_detailed_quantity := l_to_rsv_rec.secondary_reservation_quantity ;
392
393 /* Fix for Bug#12856391. Retain original secondary reservation quantity for ato item */
394 IF l_ato_item <> 1 THEN
395 l_to_rsv_rec.primary_reservation_quantity := least(l_rsv_primary_qty , nvl(l_sai_info.transaction_qty, 0));
396 l_to_rsv_rec.secondary_reservation_quantity := least(nvl(l_res_secondary_qty,0), nvl(l_sai_info.secondary_transaction_qty, 0));
397 END IF ;
398
399 ELSE
400
401 l_to_rsv_rec.detailed_quantity := least(l_rsv_detailed_qty , l_mmtt_primary_qty_sum);
402 l_to_rsv_rec.secondary_detailed_quantity := l_to_rsv_rec.secondary_reservation_quantity ;
403
404 /* Fix for Bug#12856391. Retain original secondary reservation quantity for ato item */
405 IF l_ato_item <> 1 THEN
406 l_to_rsv_rec.primary_reservation_quantity := least(l_rsv_primary_qty , l_mmtt_primary_qty_sum);
407 l_to_rsv_rec.secondary_reservation_quantity := least(nvl(l_res_secondary_qty,0), nvl(l_mmtt_secondary_qty_sum, 0));
408 END IF ;
409
410 END IF ;
411
412 IF l_to_rsv_rec.secondary_detailed_quantity = 0 THEN
413 l_to_rsv_rec.secondary_detailed_quantity := NULL;
414 END IF;
415 IF l_to_rsv_rec.secondary_reservation_quantity = 0 THEN
416 l_to_rsv_rec.secondary_reservation_quantity := NULL;
417 END IF;
418
419
420 print_debug('Final values to update reservation: ',l_api_name,g_info);
421 print_debug('Detailed Quantity :'||l_to_rsv_rec.detailed_quantity,l_api_name,g_info);
422 print_debug('Primary Reservation Qty:'||l_to_rsv_rec.primary_reservation_quantity,l_api_name,g_info);
423 print_debug('Secondary Reservation Qty: '|| l_to_rsv_rec.secondary_reservation_quantity,l_api_name,g_info);
424 print_debug('Secondary Detailed Quantity : '|| l_to_rsv_rec.secondary_detailed_quantity,l_api_name,g_info);
425
426 inv_reservation_pvt.update_reservation(
427 x_return_status => x_return_status
428 , x_msg_count => x_msg_count
429 , x_msg_data => x_msg_data
430 , p_api_version_number => 1.0
431 , p_original_rsv_rec => l_from_rsv_rec
432 , p_to_rsv_rec => l_to_rsv_rec
433 , p_original_serial_number => l_dummy_sn
434 , p_to_serial_number => l_dummy_sn
435 );
436
437 IF x_return_status <> fnd_api.g_ret_sts_success THEN
438 fnd_message.set_name('INV', 'INV_UPD_RSV_FAILED');
439 fnd_msg_pub.ADD;
440 RAISE fnd_api.g_exc_unexpected_error;
441 END IF;
442 EXCEPTION
443 WHEN fnd_api.g_exc_error THEN
444 x_return_status := fnd_api.g_ret_sts_error;
445 fnd_msg_pub.count_and_get(p_count => x_msg_count, p_data => x_msg_data);
446 WHEN fnd_api.g_exc_unexpected_error THEN
447 x_return_status := fnd_api.g_ret_sts_unexp_error;
448 fnd_msg_pub.count_and_get(p_count => x_msg_count, p_data => x_msg_data);
449 WHEN OTHERS THEN
450 x_return_status := fnd_api.g_ret_sts_unexp_error;
451 IF fnd_msg_pub.check_msg_level(fnd_msg_pub.g_msg_lvl_unexp_error) THEN
452 fnd_msg_pub.add_exc_msg(g_pkg_name, 'UNDO_PICK_RELEASE');
453 END IF;
454 END backorder_only;
455
456 PROCEDURE split_allocation(
457 x_return_status OUT NOCOPY VARCHAR2
458 , x_msg_data OUT NOCOPY VARCHAR2
459 , x_msg_count OUT NOCOPY NUMBER
460 , x_new_txn_temp_id OUT NOCOPY NUMBER
461 , p_transaction_temp_id NUMBER
462 , p_split_quantity NUMBER
463 , p_lot_control_code NUMBER
464 , p_serial_control_code NUMBER
465 --INVCONV kkillams
466 , p_split_sec_quantity NUMBER DEFAULT NULL
467 --END INVCONV kkillams
468 ) IS
469 l_api_name VARCHAR2(30) := 'SPLIT_ALLOCATE';
470 l_debug NUMBER := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'), 0);
471
472 l_txn_header_id NUMBER;
473 l_org_id NUMBER;
474 l_item_id NUMBER;
475 l_primary_uom mtl_system_items.primary_uom_code%TYPE;
476 l_txn_uom mtl_system_items.primary_uom_code%TYPE;
477 l_sec_uom_code mtl_system_items.primary_uom_code%TYPE; --INVCONV kkillams
478 l_rem_txn_qty NUMBER;
479 l_rem_pri_qty NUMBER;
480 l_lot_txn_qty NUMBER;
481 l_lot_pri_qty NUMBER;
482 l_serial_txn_temp_id NUMBER;
483 l_insert_count NUMBER;
484 l_update_count NUMBER;
485 l_rem_sec_txn_qty NUMBER; --INVCONV kkillams
486 l_lot_sec_qty NUMBER; --INVCONV kkillams
487
488 CURSOR c_mmtt_info IS
489 SELECT mmtt.transaction_header_id, mmtt.organization_id, mmtt.inventory_item_id, mmtt.transaction_uom, msi.primary_uom_code
490 , msi.secondary_uom_code --INVCONV kkillams
491 FROM mtl_material_transactions_temp mmtt, mtl_system_items msi
492 WHERE mmtt.transaction_temp_id = p_transaction_temp_id
493 AND msi.inventory_item_id = mmtt.inventory_item_id
494 AND msi.organization_id = mmtt.organization_id;
495
496 --Bug Number 3372238 added the group by clause
497 CURSOR c_unconfirmed_lots IS
498 SELECT lot_number, SUM(transaction_quantity) transaction_quantity ,SUM (primary_quantity) primary_quantity
499 ,DECODE (SUM(NVL(secondary_quantity,0)),0,NULL,SUM(NVL(secondary_quantity,0))) secondary_quantity --INVCONV KKILLAMS
500 FROM mtl_allocations_gtmp
501 WHERE transaction_temp_id = p_transaction_temp_id
502 GROUP BY lot_number;
503 BEGIN
504 x_return_status := fnd_api.g_ret_sts_success;
505
506 IF l_debug = 1 THEN
507 print_debug('Splitting the Current Allocation to create a new one for the Remaining Qty', l_api_name, g_info);
508 END IF;
509
510 OPEN c_mmtt_info;
511 FETCH c_mmtt_info INTO l_txn_header_id, l_org_id, l_item_id, l_txn_uom, l_primary_uom,l_sec_uom_code;
512 CLOSE c_mmtt_info;
513
514 -- Converting TxnQty into PrimaryQty
515 l_rem_txn_qty := p_split_quantity;
516 l_rem_pri_qty := inv_convert.inv_um_convert(l_item_id, NULL, l_rem_txn_qty, l_txn_uom, l_primary_uom, NULL, NULL);
517
518 --INVCONV kkillams
519 l_rem_sec_txn_qty := p_split_sec_quantity;
520 --END INVCONV kkillams
521
522 -- Create a new MMTT from old MMTT
523 inv_trx_util_pub.copy_insert_line_trx(
524 x_return_status => x_return_status
525 , x_msg_data => x_msg_data
526 , x_msg_count => x_msg_count
527 , x_new_txn_temp_id => x_new_txn_temp_id
528 , p_transaction_temp_id => p_transaction_temp_id
529 , p_organization_id => l_org_id
530 , p_txn_qty => l_rem_txn_qty
531 , p_primary_qty => l_rem_pri_qty
532 , p_sec_txn_qty => l_rem_sec_txn_qty --INVCONV KKILLAMS
533 );
534
535 IF x_return_status <> fnd_api.g_ret_sts_success THEN
536 IF l_debug = 1 THEN
537 print_debug('Error: Cannot copy the MMTT - Error = ' || x_msg_data, l_api_name, g_error);
538 END IF;
539 RAISE fnd_api.g_exc_unexpected_error;
540 END IF;
541
542 IF l_debug = 1 THEN
543 print_debug('The old Transaction Temp id id = '|| p_transaction_temp_id, l_api_name, g_info);
544 print_debug('Created a new MMTT.The new Transaction Temp IS is = '|| x_new_txn_temp_id, l_api_name, g_info);
545 print_debug('Transaction UOM = ' || l_txn_uom, l_api_name, g_info);
546 print_debug('Primary UOM = ' || l_primary_uom, l_api_name, g_info);
547 print_debug('Transaction Qty = ' || l_rem_txn_qty, l_api_name, g_info);
548 print_debug('Primary Qty = ' || l_rem_pri_qty, l_api_name, g_info);
549 print_debug('Secondary Qty = ' || l_rem_sec_txn_qty, l_api_name, g_info); --INVCONV KKILLAMS
550 END IF;
551
552 -- If Lot Controlled, create Lot Records
553 IF p_lot_control_code = 2 THEN
554 FOR curr_lot IN c_unconfirmed_lots LOOP
555 l_lot_txn_qty := curr_lot.transaction_quantity;
556 l_lot_pri_qty := curr_lot.primary_quantity;
557 l_lot_sec_qty := curr_lot.secondary_quantity; --INVCONV kkillams
558 IF l_debug = 1 THEN
559 print_debug('The lot number from the cursor is '|| curr_lot.lot_number,l_api_name, g_info);
560 print_debug('The transaction quantity is '|| curr_lot.transaction_quantity,l_api_name, g_info);
561 print_debug('The primary quantity is '|| curr_lot.primary_quantity,l_api_name, g_info);
562 print_debug('The remaining quantity is '|| l_rem_txn_qty,l_api_name, g_info);
563 END IF;
564
565 INSERT INTO mtl_transaction_lots_temp(
566 transaction_temp_id
567 , lot_number, transaction_quantity, primary_quantity
568 , serial_transaction_temp_id, group_header_id
569 , last_update_date, last_updated_by, creation_date, created_by
570 ,secondary_quantity --INVCONV kkillams
571 )
572 VALUES(
573 x_new_txn_temp_id
574 , curr_lot.lot_number,least(l_rem_txn_qty, l_lot_txn_qty), least(l_rem_pri_qty, l_lot_pri_qty)
575 , mtl_material_transactions_s.NEXTVAL, l_txn_header_id
576 , SYSDATE, fnd_global.user_id, SYSDATE, fnd_global.user_id
577 , DECODE(least(NVL(l_rem_sec_txn_qty,0), NVL(l_lot_sec_qty,0))
578 ,0,NULL
579 ,least(NVL(l_rem_sec_txn_qty,0), NVL(l_lot_sec_qty,0)))--INVCONV kkillams
580 )
581 RETURNING serial_transaction_temp_id, transaction_quantity, primary_quantity
582 ,secondary_quantity --INVCONV kkillams
583 INTO l_serial_txn_temp_id, l_lot_txn_qty, l_lot_pri_qty
584 ,l_lot_sec_qty; --INVCONV kkillams
585
586 IF l_debug = 1 THEN
587 print_debug('Lot Controlled Item. So Inserting MTLT', l_api_name, g_info);
588 print_debug('Lot Number = ' || curr_lot.lot_number, l_api_name, g_info);
589 print_debug('Lot Transaction Qty = ' || l_lot_txn_qty, l_api_name, g_info);
590 print_debug('Lot Primary Qty = ' || l_lot_pri_qty, l_api_name, g_info);
591 print_debug('Lot Secondary Qty = ' || l_lot_sec_qty, l_api_name, g_info);
592 END IF;
593
594 IF p_serial_control_code NOT IN (1,6) THEN
595 INSERT INTO mtl_serial_numbers_temp(
596 transaction_temp_id
597 , fm_serial_number, to_serial_number, serial_prefix
598 , last_update_date, last_updated_by, creation_date, created_by
599 )
600 SELECT l_serial_txn_temp_id
601 , serial_number, serial_number, 1
602 , SYSDATE, fnd_global.user_id, SYSDATE, fnd_global.user_id
603 FROM mtl_allocations_gtmp
604 WHERE transaction_temp_id = p_transaction_temp_id
605 AND lot_number = curr_lot.lot_number
606 AND ROWNUM <= l_lot_pri_qty;
607 l_insert_count := SQL%ROWCOUNT;
608
609 --Bug #4929806
610 --Need to set line_mark_id also since the user may change allocated serials
611 --after splitting the allocation
612 UPDATE mtl_serial_numbers
613 SET group_mark_id = l_serial_txn_temp_id
614 , line_mark_id = l_serial_txn_temp_id
615 WHERE serial_number IN (SELECT fm_serial_number FROM mtl_serial_numbers_temp
616 WHERE transaction_temp_id = l_serial_txn_temp_id)
617 AND inventory_item_id = l_item_id;
618 l_update_count := SQL%ROWCOUNT;
619
620 IF l_debug = 1 THEN
621 print_debug('Lot and Serial Controlled Item. So Inserting MSNT', l_api_name, g_info);
622 print_debug('# of Serials Inserted into MSNT = ' || l_insert_count, l_api_name, g_info);
623 print_debug('# of Serials Marked in MSN = ' || l_update_count, l_api_name, g_info);
624 END IF;
625 END IF;
626
627 l_rem_txn_qty := l_rem_txn_qty - l_lot_txn_qty;
628 l_rem_pri_qty := l_rem_pri_qty - l_lot_pri_qty;
629 l_rem_sec_txn_qty := NVL(l_rem_sec_txn_qty,0) - NVL(l_lot_sec_qty,0); --INVCONV kkillams
630 EXIT WHEN l_rem_txn_qty <= 0;
631 END LOOP;
632 ELSIF p_serial_control_code NOT IN (1,6) THEN
633 -- If Serial Controlled, create Serial Records
634 INSERT INTO mtl_serial_numbers_temp(
635 transaction_temp_id
636 , fm_serial_number, to_serial_number, serial_prefix
637 , last_update_date, last_updated_by, creation_date, created_by
638 )
639 SELECT x_new_txn_temp_id
640 , serial_number, serial_number, 1
641 , SYSDATE, fnd_global.user_id, SYSDATE, fnd_global.user_id
642 FROM mtl_allocations_gtmp
643 WHERE transaction_temp_id = p_transaction_temp_id
644 AND ROWNUM <= l_rem_pri_qty;
645 l_insert_count := SQL%ROWCOUNT;
646
647 --Bug #4929806
648 --Need to set line_mark_id also since the user may change allocated serials
649 --after splitting the allocation
650 UPDATE mtl_serial_numbers
651 SET group_mark_id = x_new_txn_temp_id
652 , line_mark_id= x_new_txn_temp_id
653 WHERE serial_number IN (SELECT fm_serial_number FROM mtl_serial_numbers_temp
654 WHERE transaction_temp_id = x_new_txn_temp_id)
655 AND inventory_item_id = l_item_id;
656 l_update_count := SQL%ROWCOUNT;
657
658 IF l_debug = 1 THEN
659 print_debug('Serial Controlled Item. So Inserting MSNT', l_api_name, g_info);
660 print_debug('# of Serials Inserted into MSNT = ' || l_insert_count, l_api_name, g_info);
661 print_debug('# of Serials Marked in MSN = ' || l_update_count, l_api_name, g_info);
662 END IF;
663 END IF;
664
665 IF l_debug = 1 THEN
666 print_debug('Created a new Allocation: TxnTempID = ' || x_new_txn_temp_id, l_api_name, g_info);
667 END IF;
668 EXCEPTION
669 WHEN OTHERS THEN
670 x_return_status := fnd_api.g_ret_sts_unexp_error;
671 IF fnd_msg_pub.check_msg_level(fnd_msg_pub.g_msg_lvl_unexp_error) THEN
672 fnd_msg_pub.add_exc_msg(g_pkg_name, l_api_name);
673 END IF;
674 IF l_debug = 1 THEN
675 print_debug('Exception: Unknown Error occurred: SQLCode = ' || SQLCODE, l_api_name, g_exception);
676 END IF;
677 END split_allocation;
678
679 PROCEDURE get_availability(
680 p_cc_rsv_rec inv_reservation_global.mtl_reservation_rec_type
681 --INVCONV kkilams
682 ,p_res_qty OUT NOCOPY NUMBER
683 ,p_sec_qty OUT NOCOPY NUMBER
684 --END INVCONV kkillams
685 ) IS
686 l_return_status VARCHAR2(1);
687 l_msg_data VARCHAR2(2000);
688 l_msg_count NUMBER;
689
690 l_qoh NUMBER;
691 l_rqoh NUMBER;
692 l_qs NUMBER;
693 l_atr NUMBER;
694 l_att NUMBER;
695 l_qr NUMBER;
696 --INVCONV kkilams
697 l_sqoh NUMBER;
698 l_srqoh NUMBER;
699 l_sqs NUMBER;
700 l_satr NUMBER;
701 l_satt NUMBER;
702 l_sqr NUMBER;
703 -- END INVCONV kkilams
704 l_api_name VARCHAR2(30) := 'REPORT_CYC_CNT';
705 l_debug NUMBER := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'), 0);
706 BEGIN
707 inv_quantity_tree_pub.query_quantities(
708 x_return_status => l_return_status
709 , x_msg_count => l_msg_count
710 , x_msg_data => l_msg_data
711 , p_api_version_number => 1.0
712 , p_init_msg_lst => fnd_api.g_false
713 , p_organization_id => p_cc_rsv_rec.organization_id
714 , p_inventory_item_id => p_cc_rsv_rec.inventory_item_id
715 , p_tree_mode => inv_quantity_tree_pub.g_reservation_mode
716 , p_is_revision_control => (p_cc_rsv_rec.revision IS NOT NULL)
717 , p_is_lot_control => (p_cc_rsv_rec.lot_number IS NOT NULL)
718 , p_is_serial_control => FALSE
719 , p_revision => p_cc_rsv_rec.revision
720 , p_lot_number => p_cc_rsv_rec.lot_number
721 , p_lot_expiration_date => SYSDATE
722 , p_subinventory_code => p_cc_rsv_rec.subinventory_code
723 , p_locator_id => p_cc_rsv_rec.locator_id
724 , p_grade_code => NULL
725 , x_qoh => l_qoh
726 , x_rqoh => l_rqoh
727 , x_qr => l_qr
728 , x_qs => l_qs
729 , x_att => l_att
730 , x_atr => l_atr
731 --INVCONV kkilams
732 , x_sqoh => l_sqoh -- invConv change
733 , x_srqoh => l_srqoh -- invConv change
734 , x_sqr => l_sqr -- invConv change
735 , x_sqs => l_sqs -- invConv change
736 , x_satt => l_satt -- invConv change
737 , x_satr => l_satr -- invConv change
738 --END INVCONV kkilams
739 );
740 IF l_return_status <> fnd_api.g_ret_sts_success THEN
741 IF l_debug = 1 THEN
742 print_debug('Error: Querying the Quantity Tree errored out', l_api_name, g_error);
743 END IF;
744 fnd_message.set_name('INV','INV-CANNOT QUERY TREE');
745 fnd_msg_pub.ADD;
746 p_res_qty :=0;
747 p_sec_qty :=0;
748 RETURN;
749 END IF;
750
751 IF l_debug = 1 THEN
752 print_debug('Queried the Quantity Tree', l_api_name, g_info);
753 print_debug(' Onhand = ' || l_qoh, l_api_name, g_info);
754 print_debug(' Availability = ' || l_atr, l_api_name, g_info);
755 print_debug(' Secondary Onhand = ' || l_sqoh, l_api_name, g_info);
756 print_debug(' Secondary Availability = ' || l_satr, l_api_name, g_info);
757 END IF;
758 --INVCONV kkillams
759 p_res_qty := l_atr;
760 p_sec_qty := l_satr;
761 --INVCONV kkillams
762 END get_availability;
763
764 PROCEDURE create_cc_reservations(
765 x_return_status OUT NOCOPY VARCHAR2
766 , p_organization_id IN NUMBER
767 , p_inventory_item_id IN NUMBER
768 , p_reservation_id IN NUMBER
769 , p_revision IN VARCHAR2
770 , p_lot_number IN VARCHAR2
771 , p_subinventory_code IN VARCHAR2
772 , p_locator_id IN NUMBER
773 , p_primary_quantity IN NUMBER
774 , p_primary_uom_code IN VARCHAR2
775 --INVCONV KKILLAMS
776 , p_secondary_quantity IN NUMBER
777 , p_secondary_uom_code IN VARCHAR2
778 --END INVCONV KKILLAMS
779 ) IS
780 l_cc_rsv_rec inv_reservation_global.mtl_reservation_rec_type;
781 l_existing_rsv_rec inv_reservation_global.mtl_reservation_rec_type;
782 l_reservations_tbl inv_reservation_global.mtl_reservation_tbl_type;
783 l_dummy_sn inv_reservation_global.serial_number_tbl_type;
784 l_reservation_count NUMBER;
785 l_update_rsv BOOLEAN := FALSE;
786 l_new_reservation_id NUMBER;
787 l_qty_reserved NUMBER;
788 l_api_error_code NUMBER;
789 l_available_qty NUMBER;
790 l_sec_available_qty NUMBER; --INVCONV KKILLAMS
791 l_sec_qty_reserved NUMBER;
792
793
794 l_msg_data VARCHAR2(2000);
795 l_msg_count NUMBER;
796
797 l_api_name VARCHAR2(30) := 'REPORT_CYC_CNT';
798 l_debug NUMBER := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'), 0);
799 /*Bug#3869184. Added the below 2 variables to hold the primary and
800 secondary reservation quantities of the existing Cycle Count Reservation*/
801 l_existing_cc_res_pri_qty NUMBER := 0;
802 l_existing_cc_res_sec_qty NUMBER := 0;
803
804 l_qoh NUMBER;
805 l_rqoh NUMBER;
806 l_qs NUMBER;
807 l_atr NUMBER;
808 l_att NUMBER;
809 l_qr NUMBER;
810 l_sqoh NUMBER;
811 l_srqoh NUMBER;
812 l_sqs NUMBER;
813 l_satr NUMBER;
814 l_satt NUMBER;
815 l_sqr NUMBER;
816
817 BEGIN
818
819 x_return_status := fnd_api.g_ret_sts_success;
820
821 fill_cycle_count_rsv_rec(
822 x_rsv_rec => l_cc_rsv_rec
823 , p_organization_id => p_organization_id
824 , p_inventory_item_id => p_inventory_item_id
825 , p_revision => p_revision
826 , p_lot_number => p_lot_number
827 , p_subinventory_code => p_subinventory_code
828 , p_locator_id => p_locator_id
829 , p_primary_uom_code => p_primary_uom_code
830 , p_secondary_uom_code => p_secondary_uom_code --INVCONV kkillams
831 );
832
833
834 /* -- For a Lot Controlled Item, MTLT would have been updated and so we need to consider that
835 -- while Querying for the Availability.
836 IF p_lot_number IS NOT NULL THEN
837 l_available_qty := l_available_qty - p_primary_quantity;
838 END IF;*/
839
840 IF l_debug = 1 THEN
841 print_debug('Cycle Count Reservations will be created with...', l_api_name, g_info);
842 print_debug(' Organization ID = ' || p_organization_id, l_api_name, g_info);
843 print_debug(' Inventory ID = ' || p_inventory_item_id, l_api_name, g_info);
844 print_debug(' Revision = ' || p_revision, l_api_name, g_info);
845 print_debug(' Lot Number = ' || p_lot_number, l_api_name, g_info);
846 print_debug(' Subinventory Code = ' || p_subinventory_code, l_api_name, g_info);
847 print_debug(' Locator ID = ' || p_locator_id, l_api_name, g_info);
848 print_debug(' Reservation ID = ' || p_reservation_id, l_api_name, g_info);
849 print_debug(' Reported Missing Qty = ' || p_primary_quantity, l_api_name, g_info);
850 print_debug(' Remaining Available Qty = ' || l_available_qty, l_api_name, g_info);
851 print_debug(' Secondary Remaining Available Qty = ' || p_secondary_quantity, l_api_name, g_info);
852 END IF;
853
854 /* Querying MTR to check for any Reservation with the same values as that of the new
855 Cycle Count Reservation record to be created */
856 inv_reservation_pvt.query_reservation(
857 p_api_version_number => 1.0
858 , p_init_msg_lst => fnd_api.g_false
859 , x_return_status => x_return_status
860 , x_msg_count => l_msg_count
861 , x_msg_data => l_msg_data
862 , p_query_input => l_cc_rsv_rec
863 , x_mtl_reservation_tbl => l_reservations_tbl
864 , x_mtl_reservation_tbl_count => l_reservation_count
865 , x_error_code => l_api_error_code
866 );
867
868 IF x_return_status <> fnd_api.g_ret_sts_success THEN
869 IF l_debug = 1 THEN
870 print_debug('Error: Querying Reservations to check for any existing reservation failed', l_api_name, g_error);
871 END IF;
872 fnd_message.set_name('INV', 'INV_QRY_RSV_FAILED');
873 fnd_msg_pub.ADD;
874 RAISE fnd_api.g_exc_unexpected_error;
875 END IF;
876
877 IF l_debug = 1 THEN
878 print_debug('Number of CC Reservations existing for Item = ' || l_reservation_count, l_api_name, g_info);
879 END IF;
880
881 IF l_reservation_count > 1 THEN
882 IF l_debug = 1 THEN
883 print_debug('Error: Query Reservation returned more than one record', l_api_name, g_error);
884 END IF;
885 fnd_message.set_name('INV', 'INV_NON_UNIQUE_RSV');
886 fnd_msg_pub.ADD;
887 RAISE fnd_api.g_exc_unexpected_error;
888 END IF;
889
890 l_update_rsv := (l_reservation_count = 1);
891
892 -- Create a Cycle Count Reservation for the Quantity reported as Missing.
893 IF l_debug = 1 THEN
894 print_debug('Creating Cycle Count Reservations for the Quantity reported', l_api_name, g_info);
895 END IF;
896 /*Bug#3869184. If there is only one Cycle Count Reservation, capture the primary and secondary
897 reservation quantities corresponding to that reservation in the newly added variables*/
898 If (l_update_rsv AND (p_reservation_id IS NOT NULL)) Then
899 l_existing_cc_res_pri_qty := l_reservations_tbl(1).primary_reservation_quantity;
900 l_existing_cc_res_sec_qty := NVL(l_reservations_tbl(1).secondary_reservation_quantity, 0);
901 IF l_debug = 1 THEN
902 print_debug('l_existing_cc_res_pri_qty:'||l_existing_cc_res_pri_qty, l_api_name, g_info);
903 print_debug('l_existing_cc_res_sec_qty:'||l_existing_cc_res_sec_qty, l_api_name, g_info);
904 END IF;
905
906 End If;
907
908 --Bug 8784069, need to call update_quantities in order to update the quantity
909 --tree with the newly reserved quantity.
910 --Bug 13836909 Calling the update quantities only when we update/txfer the reservation.
911 IF( p_reservation_id is not null or l_update_rsv) THEN
912 inv_quantity_tree_pub.update_quantities(
913 p_api_version_number => 1.0
914 , p_init_msg_lst => fnd_api.g_false
915 , x_return_status => x_return_status
916 , x_msg_count => l_msg_count
917 , x_msg_data => l_msg_data
918 , p_organization_id => p_organization_id
919 , p_inventory_item_id => p_inventory_item_id
920 , p_tree_mode => inv_quantity_tree_pub.g_reservation_mode
921 , p_is_revision_control => (p_revision IS NOT NULL)
922 , p_is_lot_control => (p_lot_number IS NOT NULL)
923 , p_is_serial_control => FALSE
924 , p_demand_source_type_id => inv_reservation_global.g_source_type_cycle_count
925 , p_demand_source_header_id => -1
926 , p_demand_source_line_id => -1
927 , p_demand_source_name => NULL
928 , p_revision => p_revision
929 , p_lot_number => p_lot_number
930 , p_lot_expiration_date => SYSDATE
931 , p_subinventory_code => p_subinventory_code
932 , p_locator_id => p_locator_id
933 , p_primary_quantity => p_primary_quantity
934 , p_secondary_quantity => p_secondary_quantity
935 , p_quantity_type => inv_quantity_tree_pub.g_qr_same_demand
936 , x_qoh => l_qoh
937 , x_rqoh => l_rqoh
938 , x_qr => l_qr
939 , x_qs => l_qs
940 , x_att => l_att
941 , x_atr => l_atr
942 , p_grade_code => NULL
943 , x_sqoh => l_sqoh
944 , x_srqoh => l_srqoh
945 , x_sqr => l_sqr
946 , x_sqs => l_sqs
947 , x_satt => l_satt
948 , x_satr => l_satr
949 );
950
951 IF x_return_status <> fnd_api.g_ret_sts_success THEN
952 IF l_debug = 1 THEN
953 print_debug('Error from update quantity tree', l_api_name, g_info);
954 END IF;
955 RAISE fnd_api.g_exc_unexpected_error;
956 END IF;
957 END IF;
958
959 -- If Reservation already exists, Transfer the existing Reservation. Otherwise Create a new one.
960 IF p_reservation_id IS NOT NULL THEN -- Transfer the Reservation
961 IF l_debug = 1 THEN
962 print_debug('Transferring the existing Reservation to a Cycle Count Reservation', l_api_name, g_info);
963 END IF;
964
965 l_existing_rsv_rec.reservation_id := p_reservation_id;
966 l_cc_rsv_rec.primary_reservation_quantity := p_primary_quantity;
967 --INVCONV kkillams
968 l_cc_rsv_rec.secondary_reservation_quantity := p_secondary_quantity; --INCONV kkillams
969 IF l_cc_rsv_rec.secondary_reservation_quantity = 0 THEN
970 l_cc_rsv_rec.secondary_reservation_quantity := NULL;
971 END IF;
972 --END INVCONV kkillams
973 inv_reservation_pvt.transfer_reservation(
974 x_return_status => x_return_status
975 , x_msg_count => l_msg_count
976 , x_msg_data => l_msg_data
977 , x_reservation_id => l_new_reservation_id
978 , p_api_version_number => 1.0
979 , p_init_msg_lst => fnd_api.g_true
980 , p_original_rsv_rec => l_existing_rsv_rec
981 , p_to_rsv_rec => l_cc_rsv_rec
982 , p_original_serial_number => l_dummy_sn
983 , p_validation_flag => fnd_api.g_true
984 );
985
986 IF x_return_status <> fnd_api.g_ret_sts_success THEN
987 IF l_debug = 1 THEN
988 print_debug('Call to Transfer Reservation API Failed', l_api_name, g_error);
989 END IF;
990 fnd_message.set_name('INV','INV_TRANSFER_RSV_FAILED');
991 fnd_msg_pub.ADD;
992 RAISE fnd_api.g_exc_unexpected_error;
993 END IF;
994
995 -- The Reservation created below will always be updated in the Reservation created now.
996 l_cc_rsv_rec.reservation_id := l_new_reservation_id;
997
998 ELSE -- Create a new Reservation
999 /* Though MMTT doesnt have any Reservation ID, there may be someother record with
1000 the same Reservation parameters. Rather than creating a new reservation, the
1001 existing reservation is updated */
1002 IF l_update_rsv THEN
1003 l_cc_rsv_rec := l_reservations_tbl(1);
1004 l_cc_rsv_rec.primary_reservation_quantity := l_cc_rsv_rec.primary_reservation_quantity + p_primary_quantity;
1005 --INVCONV KKILLAMS
1006 l_cc_rsv_rec.secondary_reservation_quantity := NVL(l_cc_rsv_rec.secondary_reservation_quantity,0) + NVL(p_secondary_quantity,0); --INVCONV kkillams
1007 IF l_cc_rsv_rec.secondary_reservation_quantity = 0 THEN
1008 l_cc_rsv_rec.secondary_reservation_quantity := NULL;
1009 END IF;
1010 --END INVCONV kkillams
1011 l_cc_rsv_rec.reservation_quantity := NULL;
1012 inv_reservation_pvt.update_reservation(
1013 x_return_status => x_return_status
1014 , x_msg_count => l_msg_count
1015 , x_msg_data => l_msg_data
1016 , p_api_version_number => 1.0
1017 , p_init_msg_lst => fnd_api.g_false
1018 , p_original_rsv_rec => l_reservations_tbl(1)
1019 , p_to_rsv_rec => l_cc_rsv_rec
1020 , p_original_serial_number => l_dummy_sn
1021 , p_to_serial_number => l_dummy_sn
1022 , p_validation_flag => fnd_api.g_true
1023 );
1024 IF x_return_status <> fnd_api.g_ret_sts_success THEN
1025 fnd_message.set_name('INV','INV_UPDATE_RSV_FAILED');
1026 fnd_msg_pub.ADD;
1027 RAISE fnd_api.g_exc_unexpected_error;
1028 END IF;
1029 ELSE
1030 l_cc_rsv_rec.primary_reservation_quantity := p_primary_quantity;
1031 --INVCONV kkillams
1032 l_cc_rsv_rec.secondary_reservation_quantity := p_secondary_quantity;
1033 IF l_cc_rsv_rec.secondary_reservation_quantity = 0 THEN
1034 l_cc_rsv_rec.secondary_reservation_quantity := NULL;
1035 END IF;
1036 --END INVCONV kkillams
1037 inv_reservation_pvt.create_reservation(
1038 x_return_status => x_return_status
1039 , x_msg_count => l_msg_count
1040 , x_msg_data => l_msg_data
1041 , x_reservation_id => l_new_reservation_id
1042 , x_quantity_reserved => l_qty_reserved
1043 , x_secondary_quantity_reserved=> l_sec_qty_reserved --INVCONV kkillams
1044 , p_api_version_number => 1.0
1045 , p_init_msg_lst => fnd_api.g_true
1046 , p_rsv_rec => l_cc_rsv_rec
1047 , p_serial_number => l_dummy_sn
1048 , x_serial_number => l_dummy_sn
1049 , p_validation_flag => fnd_api.g_true
1050 , p_partial_reservation_flag => fnd_api.g_false
1051 , p_force_reservation_flag => fnd_api.g_false
1052 );
1053 IF x_return_status <> fnd_api.g_ret_sts_success THEN
1054 fnd_message.set_name('INV','INV_CREATE_RSV_FAILED');
1055 fnd_msg_pub.ADD;
1056 RAISE fnd_api.g_exc_unexpected_error;
1057 END IF;
1058
1059 /* Since a new Reservation is created, the next Reservation created below should be
1060 updated in the Reservation created now */
1061 l_cc_rsv_rec.reservation_id := l_new_reservation_id;
1062 END IF;
1063 END IF;
1064
1065 get_availability(l_cc_rsv_rec,
1066 l_available_qty,
1067 l_sec_available_qty); --INVCONV kkillams
1068
1069 -- Create a Cycle Count Reservation for the remaining Available Quantity.
1070 IF l_available_qty > 0 THEN
1071 IF l_debug = 1 THEN
1072 print_debug('Creating Cycle Count Reservations for the remaining Availability', l_api_name, g_info);
1073 END IF;
1074
1075 l_existing_rsv_rec := l_cc_rsv_rec;
1076
1077 l_cc_rsv_rec.primary_reservation_quantity := l_cc_rsv_rec.primary_reservation_quantity + l_available_qty + l_existing_cc_res_pri_qty; --Bug#3869184
1078 --INVCONV kkillams
1079 l_cc_rsv_rec.secondary_reservation_quantity := NVL(l_cc_rsv_rec.secondary_reservation_quantity,0) + NVL(l_sec_available_qty,0) + l_existing_cc_res_sec_qty; --Bug#3869184
1080 IF l_cc_rsv_rec.secondary_reservation_quantity = 0 THEN
1081 l_cc_rsv_rec.secondary_reservation_quantity := NULL;
1082 END IF;
1083 --END INVCONV kkillams
1084
1085 l_cc_rsv_rec.reservation_quantity := NULL;
1086 inv_reservation_pvt.update_reservation(
1087 x_return_status => x_return_status
1088 , x_msg_count => l_msg_count
1089 , x_msg_data => l_msg_data
1090 , p_api_version_number => 1.0
1091 , p_init_msg_lst => fnd_api.g_false
1092 , p_original_rsv_rec => l_existing_rsv_rec
1093 , p_to_rsv_rec => l_cc_rsv_rec
1094 , p_original_serial_number => l_dummy_sn
1095 , p_to_serial_number => l_dummy_sn
1096 , p_validation_flag => fnd_api.g_true
1097 );
1098 IF x_return_status <> fnd_api.g_ret_sts_success THEN
1099 fnd_message.set_name('INV','INV_UPDATE_RSV_FAILED');
1100 fnd_msg_pub.ADD;
1101 RAISE fnd_api.g_exc_unexpected_error;
1102 END IF;
1103 END IF;
1104 EXCEPTION
1105 WHEN fnd_api.g_exc_error THEN
1106 x_return_status := fnd_api.g_ret_sts_error;
1107 fnd_msg_pub.count_and_get(p_encoded=>fnd_api.g_false, p_data => l_msg_data, p_count => l_msg_count);
1108 IF l_debug = 1 THEN
1109 print_debug('Exception: Expected: Message = ' || l_msg_data, l_api_name, g_exception);
1110 END IF;
1111 WHEN fnd_api.g_exc_unexpected_error THEN
1112 x_return_status := fnd_api.g_ret_sts_unexp_error;
1113 fnd_msg_pub.count_and_get(p_encoded=>fnd_api.g_false, p_data => l_msg_data, p_count => l_msg_count);
1114 IF l_debug = 1 THEN
1115 print_debug('Exception: Unexpected: Message = ' || l_msg_data, l_api_name, g_exception);
1116 END IF;
1117 WHEN OTHERS THEN
1118 x_return_status := fnd_api.g_ret_sts_unexp_error;
1119 IF l_debug = 1 THEN
1120 print_debug('Exception: Others: Message = ' || SQLERRM, l_api_name, g_exception);
1121 END IF;
1122 END create_cc_reservations;
1123
1124 PROCEDURE report_cycle_count(
1125 x_return_status OUT NOCOPY VARCHAR2
1126 , x_msg_data OUT NOCOPY VARCHAR2
1127 , x_msg_count OUT NOCOPY NUMBER
1128 , p_transaction_temp_id NUMBER
1129 , p_missing_quantity NUMBER
1130 , p_lot_control_code NUMBER
1131 , p_sec_missing_quantity NUMBER --INVCONV kkillams
1132 ) IS
1133 l_api_name VARCHAR2(30) := 'REPORT_CYC_CNT';
1134 l_debug NUMBER := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'), 0);
1135
1136 CURSOR c_mmtt_info IS
1137 SELECT mmtt.organization_id
1138 , mmtt.inventory_item_id
1139 , mmtt.reservation_id
1140 , mmtt.revision
1141 , mmtt.subinventory_code
1142 , mmtt.locator_id
1143 , mmtt.transaction_uom
1144 , msi.primary_uom_code
1145 , msi.secondary_uom_code --INVCONV kkillams
1146 FROM mtl_material_transactions_temp mmtt, mtl_system_items msi
1147 WHERE mmtt.transaction_temp_id = p_transaction_temp_id
1148 AND msi.inventory_item_id = mmtt.inventory_item_id
1149 AND msi.organization_id = mmtt.organization_id;
1150
1151 --Bug #3380708 - added the group by clause
1152 CURSOR c_unconfirmed_lots IS
1153 SELECT lot_number
1154 ,SUM(transaction_quantity) transaction_quantity
1155 ,SUM(primary_quantity) primary_quantity
1156 ,DECODE(SUM(NVL(secondary_quantity,0)),0,NULL,SUM(NVL(secondary_quantity,0))) secondary_quantity --INVCONV kkillams
1157 FROM mtl_allocations_gtmp
1158 WHERE transaction_temp_id = p_transaction_temp_id
1159 GROUP BY lot_number;
1160
1161
1162 l_mmtt_info c_mmtt_info%ROWTYPE;
1163 l_primary_missing_qty NUMBER;
1164 l_rem_missing_qty NUMBER;
1165 l_primary_lot_qty NUMBER;
1166
1167 l_secondary_lot_qty NUMBER; --INVCONV KKILLAMS
1168 BEGIN
1169 x_return_status := fnd_api.g_ret_sts_success;
1170
1171 IF l_debug = 1 THEN
1172 print_debug('Creating Cycle Count Reservation to report Missing Material', l_api_name, g_info);
1173 END IF;
1174
1175 OPEN c_mmtt_info;
1176 FETCH c_mmtt_info INTO l_mmtt_info;
1177 IF c_mmtt_info%NOTFOUND THEN
1178 IF l_debug = 1 THEN
1179 print_debug('Error: No Records Found in MMTT for the given query criteria', l_api_name, g_info);
1180 END IF;
1181 END IF;
1182
1183 l_primary_missing_qty := inv_convert.inv_um_convert(l_mmtt_info.inventory_item_id, NULL, p_missing_quantity, l_mmtt_info.transaction_uom, l_mmtt_info.primary_uom_code, NULL, NULL);
1184
1185
1186 IF p_lot_control_code = 1 THEN -- Not a Lot Controlled Item
1187 create_cc_reservations(
1188 x_return_status => x_return_status
1189 , p_organization_id => l_mmtt_info.organization_id
1190 , p_inventory_item_id => l_mmtt_info.inventory_item_id
1191 , p_reservation_id => l_mmtt_info.reservation_id
1192 , p_revision => l_mmtt_info.revision
1193 , p_lot_number => NULL
1194 , p_subinventory_code => l_mmtt_info.subinventory_code
1195 , p_locator_id => l_mmtt_info.locator_id
1196 , p_primary_quantity => l_primary_missing_qty
1197 , p_primary_uom_code => l_mmtt_info.primary_uom_code
1198 --INVCONV kkillams
1199 , p_secondary_quantity => p_sec_missing_quantity
1200 , p_secondary_uom_code => l_mmtt_info.secondary_uom_code
1201 --INVCONV kkillams
1202 );
1203 ELSE
1204 l_rem_missing_qty := l_primary_missing_qty;
1205 FOR curr_lot IN c_unconfirmed_lots LOOP
1206 l_primary_lot_qty := least(curr_lot.primary_quantity, l_primary_missing_qty);
1207 l_secondary_lot_qty := least(NVL(curr_lot.secondary_quantity,0), NVL(p_sec_missing_quantity,0)); --INVCONV
1208 IF l_secondary_lot_qty = 0 THEN
1209 l_secondary_lot_qty := NULL;
1210 END IF;
1211 create_cc_reservations(
1212 x_return_status => x_return_status
1213 , p_organization_id => l_mmtt_info.organization_id
1214 , p_inventory_item_id => l_mmtt_info.inventory_item_id
1215 , p_reservation_id => l_mmtt_info.reservation_id
1216 , p_revision => l_mmtt_info.revision
1217 , p_lot_number => curr_lot.lot_number
1218 , p_subinventory_code => l_mmtt_info.subinventory_code
1219 , p_locator_id => l_mmtt_info.locator_id
1220 , p_primary_quantity => l_primary_lot_qty
1221 , p_primary_uom_code => l_mmtt_info.primary_uom_code
1222 --INVCONV kkillams
1223 , p_secondary_quantity => l_secondary_lot_qty
1224 , p_secondary_uom_code => l_mmtt_info.secondary_uom_code
1225 --INVCONV kkillams
1226 );
1227 l_rem_missing_qty := l_rem_missing_qty - l_primary_lot_qty;
1228 EXIT WHEN l_rem_missing_qty <= 0;
1229 END LOOP;
1230 END IF;
1231 EXCEPTION
1232 WHEN OTHERS THEN
1233 x_return_status := fnd_api.g_ret_sts_unexp_error;
1234 END report_cycle_count;
1235
1236 PROCEDURE populate_tt_lot(
1237 x_return_status OUT NOCOPY VARCHAR2
1238 , p_transaction_temp_id IN NUMBER
1239 , p_mo_line_id NUMBER
1240 ) IS
1241 l_api_name VARCHAR2(30) := 'POPULATE_TABLE';
1242 l_debug NUMBER := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'), 0);
1243 BEGIN
1244 x_return_status := fnd_api.g_ret_sts_success;
1245
1246 IF l_debug = 1 THEN
1247 print_debug('Populating Temp Table for a Lot Ctrl Item', l_api_name, g_info);
1248 END IF;
1249
1250 INSERT INTO mtl_allocations_gtmp(transaction_temp_id, lot_number, transaction_quantity, primary_quantity
1251 ,secondary_quantity) --INVCONV kkillams
1252 SELECT p_transaction_temp_id, mtlt.lot_number, SUM(mtlt.transaction_quantity), SUM(mtlt.primary_quantity)
1253 ,DECODE(SUM(NVL(mtlt.secondary_quantity,0)),0,NULL,SUM(NVL(mtlt.secondary_quantity,0))) --INVCONV kkillams
1254 FROM mtl_transaction_lots_temp mtlt
1255 WHERE p_transaction_temp_id IS NOT NULL
1256 AND mtlt.transaction_temp_id = p_transaction_temp_id
1257 GROUP BY mtlt.lot_number
1258 UNION ALL
1259 SELECT mmtt.transaction_temp_id, mtlt.lot_number, SUM(mtlt.transaction_quantity), SUM(mtlt.primary_quantity)
1260 ,DECODE(SUM(NVL(mtlt.secondary_quantity,0)),0,NULL,SUM(NVL(mtlt.secondary_quantity,0))) --INVCONV kkillams
1261 FROM mtl_material_transactions_temp mmtt, mtl_transaction_lots_temp mtlt
1262 WHERE p_transaction_temp_id IS NULL AND p_mo_line_id IS NOT NULL
1263 AND mmtt.move_order_line_id = p_mo_line_id
1264 AND mtlt.transaction_temp_id = mmtt.transaction_temp_id
1265 GROUP BY mmtt.transaction_temp_id, mtlt.lot_number;
1266
1267 IF l_debug = 1 THEN
1268 print_debug('Allocations Temp Table populated with # of records = ' || SQL%ROWCOUNT, l_api_name, g_info);
1269 END IF;
1270
1271 IF SQL%ROWCOUNT = 0 THEN
1272 IF l_debug = 1 THEN
1273 print_debug('Error: No Records Found for the Given Query Criteria', l_api_name, g_error);
1274 END IF;
1275 --x_return_status := fnd_api.g_ret_sts_error; --FlexiLotAlloc
1276 END IF;
1277
1278 IF l_debug = 1 THEN
1279 print_debug('Populated Temp Table with Lot Information', l_api_name, g_info);
1280 END IF;
1281 EXCEPTION
1282 WHEN OTHERS THEN
1283 x_return_status := fnd_api.g_ret_sts_unexp_error;
1284 IF fnd_msg_pub.check_msg_level(fnd_msg_pub.g_msg_lvl_unexp_error) THEN
1285 fnd_msg_pub.add_exc_msg(g_pkg_name, l_api_name);
1286 END IF;
1287 IF l_debug = 1 THEN
1288 print_debug('Exception: Unknown Error occurred: SQLCode = ' || SQLCODE, l_api_name, g_exception);
1289 END IF;
1290 END populate_tt_lot;
1291
1292 PROCEDURE populate_tt_serial(
1293 x_return_status OUT NOCOPY VARCHAR2
1294 , p_transaction_temp_id IN NUMBER
1295 , p_mo_line_id NUMBER
1296 ) IS
1297 l_api_name VARCHAR2(30) := 'POPULATE_TABLE';
1298 l_debug NUMBER := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'), 0);
1299 BEGIN
1300 x_return_status := fnd_api.g_ret_sts_success;
1301
1302 IF l_debug = 1 THEN
1303 print_debug('Populating Temp Table for a Serial Ctrl Item', l_api_name, g_info);
1304 END IF;
1305
1306 INSERT INTO mtl_allocations_gtmp(transaction_temp_id, serial_number)
1307 SELECT p_transaction_temp_id, msn.serial_number
1308 FROM mtl_serial_numbers msn
1309 WHERE p_transaction_temp_id IS NOT NULL
1310 AND msn.group_mark_id = p_transaction_temp_id
1311 UNION ALL
1312 SELECT mmtt.transaction_temp_id, msn.serial_number
1313 FROM mtl_material_transactions_temp mmtt, mtl_serial_numbers msn
1314 WHERE p_transaction_temp_id IS NULL AND p_mo_line_id IS NOT NULL
1315 AND mmtt.move_order_line_id = p_mo_line_id
1316 AND msn.group_mark_id = mmtt.transaction_temp_id;
1317
1318 IF l_debug = 1 THEN
1319 print_debug('Allocations Temp Table populated with # of records = ' || SQL%ROWCOUNT, l_api_name, g_info);
1320 print_debug('Populated Temp Table with Serial Information', l_api_name, g_info);
1321 END IF;
1322 EXCEPTION
1323 WHEN OTHERS THEN
1324 x_return_status := fnd_api.g_ret_sts_unexp_error;
1325 IF fnd_msg_pub.check_msg_level(fnd_msg_pub.g_msg_lvl_unexp_error) THEN
1326 fnd_msg_pub.add_exc_msg(g_pkg_name, l_api_name);
1327 END IF;
1328 IF l_debug = 1 THEN
1329 print_debug('Exception: Unknown Error occurred: SQLCode = ' || SQLCODE, l_api_name, g_exception);
1330 END IF;
1331 END populate_tt_serial;
1332
1333 PROCEDURE populate_tt_lot_serial(
1334 x_return_status OUT NOCOPY VARCHAR2
1335 , p_transaction_temp_id IN NUMBER
1336 , p_mo_line_id NUMBER
1337 ) IS
1338 l_api_name VARCHAR2(30) := 'POPULATE_TABLE';
1339 l_debug NUMBER := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'), 0);
1340 BEGIN
1341 x_return_status := fnd_api.g_ret_sts_success;
1342
1343 IF l_debug = 1 THEN
1344 print_debug('Populating Temp Table for a Lot and Serial Ctrl Item', l_api_name, g_info);
1345 END IF;
1346
1347 INSERT INTO mtl_allocations_gtmp(transaction_temp_id, lot_number, serial_number, transaction_quantity, primary_quantity)
1348 SELECT p_transaction_temp_id, mtlt.lot_number, msn.serial_number, 1, 1
1349 FROM mtl_transaction_lots_temp mtlt, mtl_serial_numbers msn
1350 WHERE p_transaction_temp_id IS NOT NULL
1351 AND mtlt.transaction_temp_id = p_transaction_temp_id
1352 AND msn.group_mark_id = mtlt.serial_transaction_temp_id
1353 UNION ALL
1354 SELECT mmtt.transaction_temp_id, mtlt.lot_number, msn.serial_number, 1, 1
1355 FROM mtl_material_transactions_temp mmtt, mtl_transaction_lots_temp mtlt, mtl_serial_numbers msn
1356 WHERE p_transaction_temp_id IS NULL and p_mo_line_id IS NOT NULL
1357 AND mmtt.move_order_line_id = p_mo_line_id
1358 AND mtlt.transaction_temp_id = mmtt.transaction_temp_id
1359 AND msn.group_mark_id = mtlt.serial_transaction_temp_id;
1360
1361 IF l_debug = 1 THEN
1362 print_debug('Allocations Temp Table populated with # of records = ' || SQL%ROWCOUNT, l_api_name, g_info);
1363 END IF;
1364
1365 IF SQL%ROWCOUNT = 0 THEN
1366 IF l_debug = 1 THEN
1367 print_debug('No Serial Allocations found. Querying again only for Lot', l_api_name, g_info);
1368 END IF;
1369 populate_tt_lot(x_return_status, p_transaction_temp_id, p_mo_line_id);
1370 END IF;
1371
1372 IF l_debug = 1 THEN
1373 print_debug('Populated Temp Table with Lot and Serial Information', l_api_name, g_info);
1374 END IF;
1375 EXCEPTION
1376 WHEN OTHERS THEN
1377 x_return_status := fnd_api.g_ret_sts_unexp_error;
1378 IF fnd_msg_pub.check_msg_level(fnd_msg_pub.g_msg_lvl_unexp_error) THEN
1379 fnd_msg_pub.add_exc_msg(g_pkg_name, l_api_name);
1380 END IF;
1381 IF l_debug = 1 THEN
1382 print_debug('Exception: Unknown Error occurred: SQLCode = ' || SQLCODE, l_api_name, g_exception);
1383 END IF;
1384 END populate_tt_lot_serial;
1385
1386 PROCEDURE populate_table(
1387 x_return_status OUT NOCOPY VARCHAR2
1388 , x_msg_data OUT NOCOPY VARCHAR2
1389 , x_msg_count OUT NOCOPY NUMBER
1390 , p_transaction_temp_id NUMBER
1391 , p_mo_line_id NUMBER
1392 , p_lot_control_code NUMBER
1393 , p_serial_control_code NUMBER
1394 ) IS
1395 l_api_name VARCHAR2(30) := 'POPULATE_TABLE';
1396 l_debug NUMBER := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'), 0);
1397 l_lot_control_code NUMBER := p_lot_control_code;
1398 l_serial_control_code NUMBER := p_serial_control_code;
1399 BEGIN
1400 x_return_status := fnd_api.g_ret_sts_success;
1401
1402 -- Printing the Input Parameters.
1403 IF l_debug = 1 THEN
1404 print_debug('Populating the Allocations Temp Table with Suggested Lots/Serials', l_api_name, g_info);
1405 print_debug('Transaction Temp ID = ' || p_transaction_temp_id, l_api_name, g_info);
1406 print_debug('Move Order Line ID = ' || p_mo_line_id, l_api_name, g_info);
1407 print_debug('Lot Control Code = ' || p_lot_control_code, l_api_name, g_info);
1408 print_debug('Serial Control Code = ' || p_serial_control_code, l_api_name, g_info);
1409 END IF;
1410
1411 -- Either Transaction Temp ID or Move Order Line ID has to be passed.
1412 IF p_transaction_temp_id IS NULL AND p_mo_line_id IS NULL THEN
1413 IF l_debug = 1 THEN
1414 print_debug('Error: Either TxnTmpID or MOLineID has to be passed', l_api_name, g_error);
1415 END IF;
1416 fnd_message.set_name('INV', 'INV_MISSING_REQUIRED_PARAMETER');
1417 fnd_msg_pub.ADD;
1418 RAISE fnd_api.g_exc_error;
1419 END IF;
1420
1421 -- Determining the Item Controls.
1422 IF p_lot_control_code IS NULL OR p_serial_control_code IS NULL THEN
1423 get_item_controls(
1424 x_return_status => x_return_status
1425 , x_lot_control_code => l_lot_control_code
1426 , x_serial_control_code => l_serial_control_code
1427 , p_transaction_temp_id => p_transaction_temp_id
1428 , p_mo_line_id => p_mo_line_id
1429 );
1430 IF x_return_status <> fnd_api.g_ret_sts_success THEN
1431 IF l_debug = 1 THEN
1432 print_debug('Error: Cannot determine the Item Controls', l_api_name, g_error);
1433 END IF;
1434 fnd_message.set_name('INV','INV_INVALID_ITEM_ORG');
1435 fnd_msg_pub.ADD;
1436 RAISE fnd_api.g_exc_error;
1437 END IF;
1438 END IF;
1439
1440 -- First clear Allocations Temp Table.
1441 DELETE mtl_allocations_gtmp;
1442
1443 IF l_lot_control_code = 2 AND l_serial_control_code IN(1, 6) THEN
1444 populate_tt_lot(x_return_status, p_transaction_temp_id, p_mo_line_id);
1445 ELSIF l_lot_control_code = 1 AND l_serial_control_code NOT IN(1, 6) THEN
1446 populate_tt_serial(x_return_status, p_transaction_temp_id, p_mo_line_id);
1447 ELSIF l_lot_control_code = 2 AND l_serial_control_code NOT IN(1, 6) THEN
1448 populate_tt_lot_serial(x_return_status, p_transaction_temp_id, p_mo_line_id);
1449 END IF;
1450 IF x_return_status <> fnd_api.g_ret_sts_success THEN
1451 IF l_debug = 1 THEN
1452 print_debug('Error: Not able to Populate the Allocations Temp Table', l_api_name, g_error);
1453 END IF;
1454 RAISE fnd_api.g_exc_unexpected_error;
1455 END IF;
1456
1457 IF l_debug = 1 THEN
1458 print_debug('Allocations Temp Table Populated with the Suggested Lots/Serials', l_api_name, g_info);
1459 END IF;
1460 EXCEPTION
1461 WHEN fnd_api.g_exc_error THEN
1462 x_return_status := fnd_api.g_ret_sts_error;
1463 fnd_msg_pub.count_and_get(p_count => x_msg_count, p_data => x_msg_data);
1464 IF l_debug = 1 THEN
1465 print_debug('Exception: Expected Error occurred', l_api_name, g_exception);
1466 END IF;
1467 WHEN fnd_api.g_exc_unexpected_error THEN
1468 x_return_status := fnd_api.g_ret_sts_unexp_error;
1469 fnd_msg_pub.count_and_get(p_count => x_msg_count, p_data => x_msg_data);
1470 IF l_debug = 1 THEN
1471 print_debug('Exception: UnExpected Error occurred', l_api_name, g_exception);
1472 END IF;
1473 WHEN OTHERS THEN
1474 x_return_status := fnd_api.g_ret_sts_unexp_error;
1475 IF fnd_msg_pub.check_msg_level(fnd_msg_pub.g_msg_lvl_unexp_error) THEN
1476 fnd_msg_pub.add_exc_msg(g_pkg_name, l_api_name);
1477 END IF;
1478 IF l_debug = 1 THEN
1479 print_debug('Exception: Unknown Error occurred: SQLCode = ' || SQLCODE, l_api_name, g_exception);
1480 END IF;
1481 END populate_table;
1482
1483 PROCEDURE process_action(
1484 x_return_status OUT NOCOPY VARCHAR2
1485 , x_msg_data OUT NOCOPY VARCHAR2
1486 , x_msg_count OUT NOCOPY NUMBER
1487 , x_new_record_id OUT NOCOPY NUMBER
1488 , p_action NUMBER
1489 , p_transaction_temp_id NUMBER
1490 , p_remaining_quantity NUMBER
1491 , p_remaining_secondary_quantity NUMBER --INVCONV KKILLALMS
1492 , p_lot_control_code NUMBER
1493 , p_serial_control_code NUMBER
1494 ) AS
1495 l_api_name VARCHAR2(30) := 'PROCESS_ACTION';
1496 l_debug NUMBER := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'), 0);
1497 l_lot_control_code NUMBER := p_lot_control_code;
1498 l_serial_control_code NUMBER := p_serial_control_code;
1499 BEGIN
1500 x_return_status := fnd_api.g_ret_sts_success;
1501
1502 -- Printing the Input Parameters.
1503 IF l_debug = 1 THEN
1504 print_debug('Processing Missing Qty Action', l_api_name, g_info);
1505 print_debug('Transaction Temp ID = ' || p_transaction_temp_id, l_api_name, g_info);
1506 print_debug('Remaining Qty = ' || p_remaining_quantity, l_api_name, g_info);
1507 print_debug('Action = ' || p_action, l_api_name, g_info);
1508 print_debug('Lot Control Code = ' || p_lot_control_code, l_api_name, g_info);
1509 print_debug('Serial Control Code = ' || p_serial_control_code, l_api_name, g_info);
1510 END IF;
1511
1512 -- If Missing Qty is Zero then just return.
1513 IF nvl(p_remaining_quantity, 0) = 0 THEN
1514 RETURN;
1515 END IF;
1516
1517 -- Check whether Transaction Temp ID is not null
1518 IF p_transaction_temp_id IS NULL THEN
1519 IF l_debug = 1 THEN
1520 print_debug('Error: Transaction Temp ID cannot be NULL', l_api_name, g_error);
1521 END IF;
1522 RAISE fnd_api.g_exc_error;
1523 END IF;
1524
1525 -- Determining the Item Controls.
1526 IF p_lot_control_code IS NULL OR p_serial_control_code IS NULL THEN
1527 get_item_controls(
1528 x_return_status => x_return_status
1529 , x_lot_control_code => l_lot_control_code
1530 , x_serial_control_code => l_serial_control_code
1531 , p_transaction_temp_id => p_transaction_temp_id
1532 , p_mo_line_id => NULL
1533 );
1534 IF x_return_status <> fnd_api.g_ret_sts_success THEN
1535 IF l_debug = 1 THEN
1536 print_debug('Error: Cannot determine the Item Controls', l_api_name, g_error);
1537 END IF;
1538 fnd_message.set_name('INV','INV_INVALID_ITEM_ORG');
1539 fnd_msg_pub.ADD;
1540 RAISE fnd_api.g_exc_error;
1541 END IF;
1542 END IF;
1543
1544 remove_confirmed(
1545 x_return_status => x_return_status
1546 , p_transaction_temp_id => p_transaction_temp_id
1547 , p_lot_control_code => l_lot_control_code
1548 , p_serial_control_code => l_serial_control_code
1549 );
1550
1551 IF p_action = g_action_backorder THEN
1552 backorder_only(
1553 x_return_status => x_return_status
1554 , x_msg_data => x_msg_data
1555 , x_msg_count => x_msg_count
1556 , p_transaction_temp_id => p_transaction_temp_id
1557 , p_quantity => p_remaining_quantity
1558 , p_secondary_quantity => p_remaining_secondary_quantity --INVCONV kkillams
1559 );
1560 ELSIF p_action = g_action_split_allocation THEN
1561 split_allocation(
1562 x_return_status => x_return_status
1563 , x_msg_data => x_msg_data
1564 , x_msg_count => x_msg_count
1565 , x_new_txn_temp_id => x_new_record_id
1566 , p_transaction_temp_id => p_transaction_temp_id
1567 , p_split_quantity => p_remaining_quantity
1568 , p_lot_control_code => l_lot_control_code
1569 , p_serial_control_code => l_serial_control_code
1570 , p_split_sec_quantity => p_remaining_secondary_quantity --INVCONV kkillams
1571 );
1572 ELSIF p_action = g_action_cycle_count THEN
1573 report_cycle_count(
1574 x_return_status => x_return_status
1575 , x_msg_data => x_msg_data
1576 , x_msg_count => x_msg_count
1577 , p_transaction_temp_id => p_transaction_temp_id
1578 , p_missing_quantity => p_remaining_quantity
1579 , p_lot_control_code => l_lot_control_code
1580 , p_sec_missing_quantity => p_remaining_secondary_quantity --INVCONV kkillams
1581 );
1582 END IF;
1583 IF x_return_status <> fnd_api.g_ret_sts_success THEN
1584 RAISE fnd_api.g_exc_unexpected_error;
1585 END IF;
1586 EXCEPTION
1587 WHEN fnd_api.g_exc_error THEN
1588 x_return_status := fnd_api.g_ret_sts_error;
1589 fnd_msg_pub.count_and_get(p_count => x_msg_count, p_data => x_msg_data);
1590 IF l_debug = 1 THEN
1591 print_debug('Exception: Expected Error occurred', l_api_name, g_exception);
1592 END IF;
1593 WHEN fnd_api.g_exc_unexpected_error THEN
1594 x_return_status := fnd_api.g_ret_sts_unexp_error;
1595 fnd_msg_pub.count_and_get(p_count => x_msg_count, p_data => x_msg_data);
1596 IF l_debug = 1 THEN
1597 print_debug('Exception: Unexpected Error occurred', l_api_name, g_exception);
1598 END IF;
1599 WHEN OTHERS THEN
1600 x_return_status := fnd_api.g_ret_sts_unexp_error;
1601 IF fnd_msg_pub.check_msg_level(fnd_msg_pub.g_msg_lvl_unexp_error) THEN
1602 fnd_msg_pub.add_exc_msg(g_pkg_name, l_api_name);
1603 END IF;
1604 IF l_debug = 1 THEN
1605 print_debug('Exception: Unknown Error occurred: SQLCode = ' || SQLCODE, l_api_name, g_exception);
1606 END IF;
1607 END process_action;
1608
1609 PROCEDURE update_allocation_qty
1610 (
1611 x_return_status OUT NOCOPY VARCHAR2
1612 , x_msg_data OUT NOCOPY VARCHAR2
1613 , x_msg_count OUT NOCOPY NUMBER
1614 , p_transaction_temp_id NUMBER
1615 , p_confirmed_quantity NUMBER
1616 , p_transaction_uom VARCHAR2
1617 --INVCONV kkillams
1618 , p_sec_confirmed_quantity NUMBER
1619 , p_secondary_uom_code VARCHAR2
1620 --INVCONV kkillams
1621 )
1622 IS
1623 l_api_name VARCHAR2(30) := 'UPDATE_ALLOCATION_QTY';
1624 l_debug NUMBER := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'), 0);
1625 l_confirmed_quantity_primary NUMBER;
1626 l_primary_uom VARCHAR2(30);
1627 l_inventory_item_id NUMBER;
1628 l_organization_id NUMBER;
1629
1630 BEGIN
1631 x_return_status := fnd_api.g_ret_sts_success;
1632
1633 IF l_debug = 1 THEN
1634 print_debug('Updating Allocation Qty', l_api_name, g_info);
1635 print_debug('Transaction Temp ID = ' || p_transaction_temp_id, l_api_name, g_info);
1636 print_debug('Confirmed_quantity = ' || p_confirmed_quantity, l_api_name, g_info);
1637 END IF;
1638
1639 SELECT inventory_item_id, organization_id INTO l_inventory_item_id,l_organization_id
1640 FROM mtl_material_transactions_temp WHERE transaction_temp_id = p_transaction_temp_id;
1641
1642 SELECT primary_uom_code INTO l_primary_uom FROM mtl_system_items
1643 WHERE inventory_item_id =l_inventory_item_id
1644 AND organization_id =l_organization_id;
1645
1646
1647 IF l_primary_uom <> p_transaction_uom THEN
1648 l_confirmed_quantity_primary :=
1649 inv_convert.inv_um_convert(
1650 item_id => null
1651 , precision => null
1652 , from_quantity => p_confirmed_quantity
1653 , from_unit => p_transaction_uom
1654 , to_unit => l_primary_uom
1655 , from_name => null
1656 , to_name => null
1657 );
1658 IF ( l_confirmed_quantity_primary < 0 )THEN
1659 fnd_message.set_name('INV','INV_UOM_CONV_ERROR');
1660 fnd_msg_pub.ADD;
1661 RAISE fnd_api.g_exc_error;
1662 END IF;
1663
1664 ELSE
1665 l_confirmed_quantity_primary:=p_confirmed_quantity;
1666 END IF;
1667
1668 UPDATE mtl_material_transactions_temp SET transaction_quantity =p_confirmed_quantity
1669 , primary_quantity= l_confirmed_quantity_primary
1670 --INVCONV kkillams
1671 , secondary_uom_code = p_secondary_uom_code
1672 , secondary_transaction_quantity = p_sec_confirmed_quantity
1673 --END INVCONV kkillams
1674 WHERE transaction_temp_id = p_transaction_temp_id;
1675
1676
1677
1678 EXCEPTION
1679 WHEN fnd_api.g_exc_error THEN
1680 x_return_status := fnd_api.g_ret_sts_error;
1681 fnd_msg_pub.count_and_get(p_count => x_msg_count, p_data => x_msg_data);
1682 IF l_debug = 1 THEN
1683 print_debug('Exception: Expected Error occurred', l_api_name, g_exception);
1684 END IF;
1685 WHEN OTHERS THEN
1686 x_return_status := fnd_api.g_ret_sts_unexp_error;
1687 fnd_msg_pub.count_and_get(p_count => x_msg_count, p_data => x_msg_data);
1688 IF l_debug = 1 THEN
1689 print_debug('Exception: Unknown Error occurred: SQLCode = ' || SQLCODE, l_api_name, g_exception);
1690 END IF;
1691
1692 END update_allocation_qty;
1693
1694 END inv_missing_qty_actions_engine;