[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.4 2006/07/13 11:48:27 pmadadi noship $ */
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.4 2006/07/13 11:48:27 pmadadi noship $', 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
229 --INVCONV KKILLAMS
230 l_res_secondary_qty mtl_reservations.secondary_reservation_quantity%TYPE;
231 l_sec_secondary_qty mtl_reservations.secondary_detailed_quantity%TYPE;
232 --END INVCONV KKILLAMS
233
234 CURSOR c_mmtt_info IS
235 SELECT mmtt.inventory_item_id
236 , mmtt.transaction_uom
237 , mmtt.reservation_id
238 , msi.primary_uom_code
239 , msi.replenish_to_order_flag
240 , msi.bom_item_type
241 , msi.secondary_uom_code --INVCONV kkillams
242 FROM mtl_material_transactions_temp mmtt, mtl_system_items msi
243 WHERE mmtt.transaction_temp_id = p_transaction_temp_id
244 AND msi.inventory_item_id = mmtt.inventory_item_id
245 AND msi.organization_id = mmtt.organization_id;
246
247 l_mmtt_info c_mmtt_info%ROWTYPE;
248
249 CURSOR c_rsv_info IS
250 SELECT primary_reservation_quantity, detailed_quantity
251 ,secondary_reservation_quantity, secondary_detailed_quantity --INVCONV kkillams
252 FROM mtl_reservations
253 WHERE reservation_id = l_mmtt_info.reservation_id;
254
255 BEGIN
256 x_return_status := fnd_api.g_ret_sts_success;
257
258 OPEN c_mmtt_info;
259 FETCH c_mmtt_info INTO l_mmtt_info;
260 CLOSE c_mmtt_info;
261
262 IF l_mmtt_info.reservation_id IS NULL THEN
263 RETURN;
264 END IF;
265
266 /*Bug:4539851.Getting the sum of primary_quantity of all the allocations for the given
267 reservation_id*/
268 BEGIN
269 SELECT SUM(ABS(primary_quantity))
270 INTO l_mmtt_primary_qty_sum
271 FROM mtl_material_transactions_temp
272 WHERE reservation_id= l_mmtt_info.reservation_id;
273 EXCEPTION
274 WHEN OTHERS THEN
275 RAISE fnd_api.g_exc_unexpected_error;
276 END;
277
278 print_debug('sum of all allocations ='||l_mmtt_primary_qty_sum,l_api_name,g_info);
279
280
281 OPEN c_rsv_info;
282 FETCH c_rsv_info INTO l_rsv_primary_qty, l_rsv_detailed_qty
283 ,l_res_secondary_qty --INCONV KKILLAMS
284 ,l_sec_secondary_qty; --INCONV KKILLAMS
285 IF c_rsv_info%NOTFOUND THEN
286 CLOSE c_rsv_info;
287 fnd_message.set_name('INV','INV-ROW-NOT-FOUND');
288 fnd_msg_pub.ADD;
289 /*Bug:4700706. When the reservation record is deleted somehow by this time we need not
290 deal with the reservation.So we just return. */
291 RETURN;
292 --RAISE fnd_api.g_exc_unexpected_error;
293 END IF;
294 CLOSE c_rsv_info;
295
296 -- Bug#2621481: For ATO Item, Retain the Reservation Qty
297 IF l_mmtt_info.bom_item_type = 4 AND l_mmtt_info.replenish_to_order_flag = 'Y' THEN
298 l_ato_item := 1;
299 END IF;
300
301 /*Bug:4539851. Removed the following code as we are directly getting the primary quantity
302 from MMTT into l_mmtt_primary_qty_sum. */
303 /*IF l_mmtt_info.transaction_uom <> l_mmtt_info.primary_uom_code THEN
304
305 l_primary_qty :=
306 inv_convert.inv_um_convert(l_mmtt_info.inventory_item_id, NULL, p_quantity,
307 l_mmtt_info.transaction_uom, l_mmtt_info.primary_uom_code, NULL, NULL);
308
309 END IF;
310 */
311
312
313 l_from_rsv_rec.reservation_id := l_mmtt_info.reservation_id;
314 /*Bug:4539851. Changed the logic to calculate l_to_rsv_rec.detailed_quantity
315 by taking minimum of current detailed quantity and the sum of transaction quantity
316 of all the allocations in MMTT of the Move Order line */
317 --l_to_rsv_rec.detailed_quantity := l_rsv_detailed_qty - l_primary_qty;
318 l_to_rsv_rec.detailed_quantity := least(l_rsv_detailed_qty , l_mmtt_primary_qty_sum);
319 print_debug('Detailed Quantity :'||l_to_rsv_rec.detailed_quantity,l_api_name,g_info);
320
321 IF l_ato_item <> 1 THEN
322 --l_to_rsv_rec.primary_reservation_quantity := l_rsv_primary_qty - l_primary_qty;
323 /*Bug:4539851. Changed the logic to calculate l_to_rsv_rec.primary_reservation_quantity
324 by taking min of current reservation quantity of the MO line and the sum of transaction quantity
325 of all the allocations in MMTT of the Move Order line */
326 --l_to_rsv_rec.primary_reservation_quantity := l_rsv_primary_qty - l_primary_qty;
327 l_to_rsv_rec.primary_reservation_quantity := least(l_rsv_primary_qty , l_mmtt_primary_qty_sum);
328 print_debug('Primary Reservation Qty:'||l_to_rsv_rec.primary_reservation_quantity,l_api_name,g_info);
329 END IF;
330
331 --INVCONV KKILLAMS
332 l_to_rsv_rec.secondary_detailed_quantity := NVL(l_sec_secondary_qty,0) - NVL(p_secondary_quantity,0);
333 l_to_rsv_rec.secondary_reservation_quantity := NVL(l_res_secondary_qty,0) - NVL(p_secondary_quantity,0);
334 IF l_to_rsv_rec.secondary_detailed_quantity = 0 THEN
335 l_to_rsv_rec.secondary_detailed_quantity := NULL;
336 END IF;
337 IF l_to_rsv_rec.secondary_reservation_quantity = 0 THEN
338 l_to_rsv_rec.secondary_reservation_quantity := NULL;
339 END IF;
340 --END INVCONV KKILLAMS
341
342
343 inv_reservation_pvt.update_reservation(
344 x_return_status => x_return_status
345 , x_msg_count => x_msg_count
346 , x_msg_data => x_msg_data
347 , p_api_version_number => 1.0
348 , p_original_rsv_rec => l_from_rsv_rec
349 , p_to_rsv_rec => l_to_rsv_rec
350 , p_original_serial_number => l_dummy_sn
351 , p_to_serial_number => l_dummy_sn
352 );
353
354 IF x_return_status <> fnd_api.g_ret_sts_success THEN
355 fnd_message.set_name('INV', 'INV_UPD_RSV_FAILED');
356 fnd_msg_pub.ADD;
357 RAISE fnd_api.g_exc_unexpected_error;
358 END IF;
359 EXCEPTION
360 WHEN fnd_api.g_exc_error THEN
361 x_return_status := fnd_api.g_ret_sts_error;
362 fnd_msg_pub.count_and_get(p_count => x_msg_count, p_data => x_msg_data);
363 WHEN fnd_api.g_exc_unexpected_error THEN
364 x_return_status := fnd_api.g_ret_sts_unexp_error;
365 fnd_msg_pub.count_and_get(p_count => x_msg_count, p_data => x_msg_data);
366 WHEN OTHERS THEN
367 x_return_status := fnd_api.g_ret_sts_unexp_error;
368 IF fnd_msg_pub.check_msg_level(fnd_msg_pub.g_msg_lvl_unexp_error) THEN
369 fnd_msg_pub.add_exc_msg(g_pkg_name, 'UNDO_PICK_RELEASE');
370 END IF;
371 END backorder_only;
372
373 PROCEDURE split_allocation(
374 x_return_status OUT NOCOPY VARCHAR2
375 , x_msg_data OUT NOCOPY VARCHAR2
376 , x_msg_count OUT NOCOPY NUMBER
377 , x_new_txn_temp_id OUT NOCOPY NUMBER
378 , p_transaction_temp_id NUMBER
379 , p_split_quantity NUMBER
380 , p_lot_control_code NUMBER
381 , p_serial_control_code NUMBER
382 --INVCONV kkillams
383 , p_split_sec_quantity NUMBER DEFAULT NULL
384 --END INVCONV kkillams
385 ) IS
386 l_api_name VARCHAR2(30) := 'SPLIT_ALLOCATE';
387 l_debug NUMBER := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'), 0);
388
389 l_txn_header_id NUMBER;
390 l_org_id NUMBER;
391 l_item_id NUMBER;
392 l_primary_uom mtl_system_items.primary_uom_code%TYPE;
393 l_txn_uom mtl_system_items.primary_uom_code%TYPE;
394 l_sec_uom_code mtl_system_items.primary_uom_code%TYPE; --INVCONV kkillams
395 l_rem_txn_qty NUMBER;
396 l_rem_pri_qty NUMBER;
397 l_lot_txn_qty NUMBER;
398 l_lot_pri_qty NUMBER;
399 l_serial_txn_temp_id NUMBER;
400 l_insert_count NUMBER;
401 l_update_count NUMBER;
402 l_rem_sec_txn_qty NUMBER; --INVCONV kkillams
403 l_lot_sec_qty NUMBER; --INVCONV kkillams
404
405 CURSOR c_mmtt_info IS
406 SELECT mmtt.transaction_header_id, mmtt.organization_id, mmtt.inventory_item_id, mmtt.transaction_uom, msi.primary_uom_code
407 , msi.secondary_uom_code --INVCONV kkillams
408 FROM mtl_material_transactions_temp mmtt, mtl_system_items msi
409 WHERE mmtt.transaction_temp_id = p_transaction_temp_id
410 AND msi.inventory_item_id = mmtt.inventory_item_id
411 AND msi.organization_id = mmtt.organization_id;
412
413 --Bug Number 3372238 added the group by clause
414 CURSOR c_unconfirmed_lots IS
415 SELECT lot_number, SUM(transaction_quantity) transaction_quantity ,SUM (primary_quantity) primary_quantity
416 ,DECODE (SUM(NVL(secondary_quantity,0)),0,NULL,SUM(NVL(secondary_quantity,0))) secondary_quantity --INVCONV KKILLAMS
417 FROM mtl_allocations_gtmp
418 WHERE transaction_temp_id = p_transaction_temp_id
419 GROUP BY lot_number;
420 BEGIN
421 x_return_status := fnd_api.g_ret_sts_success;
422
423 IF l_debug = 1 THEN
424 print_debug('Splitting the Current Allocation to create a new one for the Remaining Qty', l_api_name, g_info);
425 END IF;
426
427 OPEN c_mmtt_info;
428 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;
429 CLOSE c_mmtt_info;
430
431 -- Converting TxnQty into PrimaryQty
432 l_rem_txn_qty := p_split_quantity;
433 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);
434
435 --INVCONV kkillams
436 l_rem_sec_txn_qty := p_split_sec_quantity;
437 --END INVCONV kkillams
438
439 -- Create a new MMTT from old MMTT
440 inv_trx_util_pub.copy_insert_line_trx(
441 x_return_status => x_return_status
442 , x_msg_data => x_msg_data
443 , x_msg_count => x_msg_count
444 , x_new_txn_temp_id => x_new_txn_temp_id
445 , p_transaction_temp_id => p_transaction_temp_id
446 , p_organization_id => l_org_id
447 , p_txn_qty => l_rem_txn_qty
448 , p_primary_qty => l_rem_pri_qty
449 , p_sec_txn_qty => l_rem_sec_txn_qty --INVCONV KKILLAMS
450 );
451
452 IF x_return_status <> fnd_api.g_ret_sts_success THEN
453 IF l_debug = 1 THEN
454 print_debug('Error: Cannot copy the MMTT - Error = ' || x_msg_data, l_api_name, g_error);
455 END IF;
456 RAISE fnd_api.g_exc_unexpected_error;
457 END IF;
458
459 IF l_debug = 1 THEN
460 print_debug('The old Transaction Temp id id = '|| p_transaction_temp_id, l_api_name, g_info);
461 print_debug('Created a new MMTT.The new Transaction Temp IS is = '|| x_new_txn_temp_id, l_api_name, g_info);
462 print_debug('Transaction UOM = ' || l_txn_uom, l_api_name, g_info);
463 print_debug('Primary UOM = ' || l_primary_uom, l_api_name, g_info);
464 print_debug('Transaction Qty = ' || l_rem_txn_qty, l_api_name, g_info);
465 print_debug('Primary Qty = ' || l_rem_pri_qty, l_api_name, g_info);
466 print_debug('Secondary Qty = ' || l_rem_sec_txn_qty, l_api_name, g_info); --INVCONV KKILLAMS
467 END IF;
468
469 -- If Lot Controlled, create Lot Records
470 IF p_lot_control_code = 2 THEN
471 FOR curr_lot IN c_unconfirmed_lots LOOP
472 l_lot_txn_qty := curr_lot.transaction_quantity;
473 l_lot_pri_qty := curr_lot.primary_quantity;
474 l_lot_sec_qty := curr_lot.secondary_quantity; --INVCONV kkillams
475 IF l_debug = 1 THEN
476 print_debug('The lot number from the cursor is '|| curr_lot.lot_number,l_api_name, g_info);
477 print_debug('The transaction quantity is '|| curr_lot.transaction_quantity,l_api_name, g_info);
478 print_debug('The primary quantity is '|| curr_lot.primary_quantity,l_api_name, g_info);
479 print_debug('The remaining quantity is '|| l_rem_txn_qty,l_api_name, g_info);
480 END IF;
481
482 INSERT INTO mtl_transaction_lots_temp(
483 transaction_temp_id
484 , lot_number, transaction_quantity, primary_quantity
485 , serial_transaction_temp_id, group_header_id
486 , last_update_date, last_updated_by, creation_date, created_by
487 ,secondary_quantity --INVCONV kkillams
488 )
489 VALUES(
490 x_new_txn_temp_id
491 , curr_lot.lot_number,least(l_rem_txn_qty, l_lot_txn_qty), least(l_rem_pri_qty, l_lot_pri_qty)
492 , mtl_material_transactions_s.NEXTVAL, l_txn_header_id
493 , SYSDATE, fnd_global.user_id, SYSDATE, fnd_global.user_id
494 , DECODE(least(NVL(l_rem_sec_txn_qty,0), NVL(l_lot_sec_qty,0))
495 ,0,NULL
496 ,least(NVL(l_rem_sec_txn_qty,0), NVL(l_lot_sec_qty,0)))--INVCONV kkillams
497 )
498 RETURNING serial_transaction_temp_id, transaction_quantity, primary_quantity
499 ,secondary_quantity --INVCONV kkillams
500 INTO l_serial_txn_temp_id, l_lot_txn_qty, l_lot_pri_qty
501 ,l_lot_sec_qty; --INVCONV kkillams
502
503 IF l_debug = 1 THEN
504 print_debug('Lot Controlled Item. So Inserting MTLT', l_api_name, g_info);
505 print_debug('Lot Number = ' || curr_lot.lot_number, l_api_name, g_info);
506 print_debug('Lot Transaction Qty = ' || l_lot_txn_qty, l_api_name, g_info);
507 print_debug('Lot Primary Qty = ' || l_lot_pri_qty, l_api_name, g_info);
508 print_debug('Lot Secondary Qty = ' || l_lot_sec_qty, l_api_name, g_info);
509 END IF;
510
511 IF p_serial_control_code NOT IN (1,6) THEN
512 INSERT INTO mtl_serial_numbers_temp(
513 transaction_temp_id
514 , fm_serial_number, to_serial_number, serial_prefix
515 , last_update_date, last_updated_by, creation_date, created_by
516 )
517 SELECT l_serial_txn_temp_id
518 , serial_number, serial_number, 1
519 , SYSDATE, fnd_global.user_id, SYSDATE, fnd_global.user_id
520 FROM mtl_allocations_gtmp
521 WHERE transaction_temp_id = p_transaction_temp_id
522 AND lot_number = curr_lot.lot_number
523 AND ROWNUM <= l_lot_pri_qty;
524 l_insert_count := SQL%ROWCOUNT;
525
526 --Bug #4929806
527 --Need to set line_mark_id also since the user may change allocated serials
528 --after splitting the allocation
529 UPDATE mtl_serial_numbers
530 SET group_mark_id = l_serial_txn_temp_id
531 , line_mark_id = l_serial_txn_temp_id
532 WHERE serial_number IN (SELECT fm_serial_number FROM mtl_serial_numbers_temp
533 WHERE transaction_temp_id = l_serial_txn_temp_id)
534 AND inventory_item_id = l_item_id;
535 l_update_count := SQL%ROWCOUNT;
536
537 IF l_debug = 1 THEN
538 print_debug('Lot and Serial Controlled Item. So Inserting MSNT', l_api_name, g_info);
539 print_debug('# of Serials Inserted into MSNT = ' || l_insert_count, l_api_name, g_info);
540 print_debug('# of Serials Marked in MSN = ' || l_update_count, l_api_name, g_info);
541 END IF;
542 END IF;
543
544 l_rem_txn_qty := l_rem_txn_qty - l_lot_txn_qty;
545 l_rem_pri_qty := l_rem_pri_qty - l_lot_pri_qty;
546 l_rem_sec_txn_qty := NVL(l_rem_sec_txn_qty,0) - NVL(l_lot_sec_qty,0); --INVCONV kkillams
547 EXIT WHEN l_rem_txn_qty <= 0;
548 END LOOP;
549 ELSIF p_serial_control_code NOT IN (1,6) THEN
550 -- If Serial Controlled, create Serial Records
551 INSERT INTO mtl_serial_numbers_temp(
552 transaction_temp_id
553 , fm_serial_number, to_serial_number, serial_prefix
554 , last_update_date, last_updated_by, creation_date, created_by
555 )
556 SELECT x_new_txn_temp_id
557 , serial_number, serial_number, 1
558 , SYSDATE, fnd_global.user_id, SYSDATE, fnd_global.user_id
559 FROM mtl_allocations_gtmp
560 WHERE transaction_temp_id = p_transaction_temp_id
561 AND ROWNUM <= l_rem_pri_qty;
562 l_insert_count := SQL%ROWCOUNT;
563
564 --Bug #4929806
565 --Need to set line_mark_id also since the user may change allocated serials
566 --after splitting the allocation
567 UPDATE mtl_serial_numbers
568 SET group_mark_id = x_new_txn_temp_id
569 , line_mark_id= x_new_txn_temp_id
570 WHERE serial_number IN (SELECT fm_serial_number FROM mtl_serial_numbers_temp
571 WHERE transaction_temp_id = x_new_txn_temp_id)
572 AND inventory_item_id = l_item_id;
573 l_update_count := SQL%ROWCOUNT;
574
575 IF l_debug = 1 THEN
576 print_debug('Serial Controlled Item. So Inserting MSNT', l_api_name, g_info);
577 print_debug('# of Serials Inserted into MSNT = ' || l_insert_count, l_api_name, g_info);
578 print_debug('# of Serials Marked in MSN = ' || l_update_count, l_api_name, g_info);
579 END IF;
580 END IF;
581
582 IF l_debug = 1 THEN
583 print_debug('Created a new Allocation: TxnTempID = ' || x_new_txn_temp_id, l_api_name, g_info);
584 END IF;
585 EXCEPTION
586 WHEN OTHERS THEN
587 x_return_status := fnd_api.g_ret_sts_unexp_error;
588 IF fnd_msg_pub.check_msg_level(fnd_msg_pub.g_msg_lvl_unexp_error) THEN
589 fnd_msg_pub.add_exc_msg(g_pkg_name, l_api_name);
590 END IF;
591 IF l_debug = 1 THEN
592 print_debug('Exception: Unknown Error occurred: SQLCode = ' || SQLCODE, l_api_name, g_exception);
593 END IF;
594 END split_allocation;
595
596 PROCEDURE get_availability(
597 p_cc_rsv_rec inv_reservation_global.mtl_reservation_rec_type
598 --INVCONV kkilams
599 ,p_res_qty OUT NOCOPY NUMBER
600 ,p_sec_qty OUT NOCOPY NUMBER
601 --END INVCONV kkillams
602 ) IS
603 l_return_status VARCHAR2(1);
604 l_msg_data VARCHAR2(2000);
605 l_msg_count NUMBER;
606
607 l_qoh NUMBER;
608 l_rqoh NUMBER;
609 l_qs NUMBER;
610 l_atr NUMBER;
611 l_att NUMBER;
612 l_qr NUMBER;
613 --INVCONV kkilams
614 l_sqoh NUMBER;
615 l_srqoh NUMBER;
616 l_sqs NUMBER;
617 l_satr NUMBER;
618 l_satt NUMBER;
619 l_sqr NUMBER;
620 -- END INVCONV kkilams
621 l_api_name VARCHAR2(30) := 'REPORT_CYC_CNT';
622 l_debug NUMBER := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'), 0);
623 BEGIN
624 inv_quantity_tree_pub.query_quantities(
625 x_return_status => l_return_status
626 , x_msg_count => l_msg_count
627 , x_msg_data => l_msg_data
628 , p_api_version_number => 1.0
629 , p_init_msg_lst => fnd_api.g_false
630 , p_organization_id => p_cc_rsv_rec.organization_id
631 , p_inventory_item_id => p_cc_rsv_rec.inventory_item_id
632 , p_tree_mode => inv_quantity_tree_pub.g_reservation_mode
633 , p_is_revision_control => (p_cc_rsv_rec.revision IS NOT NULL)
634 , p_is_lot_control => (p_cc_rsv_rec.lot_number IS NOT NULL)
635 , p_is_serial_control => FALSE
636 , p_revision => p_cc_rsv_rec.revision
637 , p_lot_number => p_cc_rsv_rec.lot_number
638 , p_lot_expiration_date => SYSDATE
639 , p_subinventory_code => p_cc_rsv_rec.subinventory_code
640 , p_locator_id => p_cc_rsv_rec.locator_id
641 , p_grade_code => NULL
642 , x_qoh => l_qoh
643 , x_rqoh => l_rqoh
644 , x_qr => l_qr
645 , x_qs => l_qs
646 , x_att => l_att
647 , x_atr => l_atr
648 --INVCONV kkilams
649 , x_sqoh => l_sqoh -- invConv change
650 , x_srqoh => l_srqoh -- invConv change
651 , x_sqr => l_sqr -- invConv change
652 , x_sqs => l_sqs -- invConv change
653 , x_satt => l_satt -- invConv change
654 , x_satr => l_satr -- invConv change
655 --END INVCONV kkilams
656 );
657 IF l_return_status <> fnd_api.g_ret_sts_success THEN
658 IF l_debug = 1 THEN
659 print_debug('Error: Querying the Quantity Tree errored out', l_api_name, g_error);
660 END IF;
661 fnd_message.set_name('INV','INV-CANNOT QUERY TREE');
662 fnd_msg_pub.ADD;
663 p_res_qty :=0;
664 p_sec_qty :=0;
665 RETURN;
666 END IF;
667
668 IF l_debug = 1 THEN
669 print_debug('Queried the Quantity Tree', l_api_name, g_info);
670 print_debug(' Onhand = ' || l_qoh, l_api_name, g_info);
671 print_debug(' Availability = ' || l_atr, l_api_name, g_info);
672 print_debug(' Secondary Onhand = ' || l_sqoh, l_api_name, g_info);
673 print_debug(' Secondary Availability = ' || l_satr, l_api_name, g_info);
674 END IF;
675 --INVCONV kkillams
676 p_res_qty := l_atr;
677 p_sec_qty := l_satr;
678 --INVCONV kkillams
679 END get_availability;
680
681 PROCEDURE create_cc_reservations(
682 x_return_status OUT NOCOPY VARCHAR2
683 , p_organization_id IN NUMBER
684 , p_inventory_item_id IN NUMBER
685 , p_reservation_id IN NUMBER
686 , p_revision IN VARCHAR2
687 , p_lot_number IN VARCHAR2
688 , p_subinventory_code IN VARCHAR2
689 , p_locator_id IN NUMBER
690 , p_primary_quantity IN NUMBER
691 , p_primary_uom_code IN VARCHAR2
692 --INVCONV KKILLAMS
693 , p_secondary_quantity IN NUMBER
694 , p_secondary_uom_code IN VARCHAR2
695 --END INVCONV KKILLAMS
696 ) IS
697 l_cc_rsv_rec inv_reservation_global.mtl_reservation_rec_type;
698 l_existing_rsv_rec inv_reservation_global.mtl_reservation_rec_type;
699 l_reservations_tbl inv_reservation_global.mtl_reservation_tbl_type;
700 l_dummy_sn inv_reservation_global.serial_number_tbl_type;
701 l_reservation_count NUMBER;
702 l_update_rsv BOOLEAN := FALSE;
703 l_new_reservation_id NUMBER;
704 l_qty_reserved NUMBER;
705 l_api_error_code NUMBER;
706 l_available_qty NUMBER;
707 l_sec_available_qty NUMBER; --INVCONV KKILLAMS
708 l_sec_qty_reserved NUMBER;
709
710
711 l_msg_data VARCHAR2(2000);
712 l_msg_count NUMBER;
713
714 l_api_name VARCHAR2(30) := 'REPORT_CYC_CNT';
715 l_debug NUMBER := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'), 0);
716 /*Bug#3869184. Added the below 2 variables to hold the primary and
717 secondary reservation quantities of the existing Cycle Count Reservation*/
718 l_existing_cc_res_pri_qty NUMBER := 0;
719 l_existing_cc_res_sec_qty NUMBER := 0;
720
721 BEGIN
722
723 x_return_status := fnd_api.g_ret_sts_success;
724
725 fill_cycle_count_rsv_rec(
726 x_rsv_rec => l_cc_rsv_rec
727 , p_organization_id => p_organization_id
728 , p_inventory_item_id => p_inventory_item_id
729 , p_revision => p_revision
730 , p_lot_number => p_lot_number
731 , p_subinventory_code => p_subinventory_code
732 , p_locator_id => p_locator_id
733 , p_primary_uom_code => p_primary_uom_code
734 , p_secondary_uom_code => p_secondary_uom_code --INVCONV kkillams
735 );
736
737
738 /* -- For a Lot Controlled Item, MTLT would have been updated and so we need to consider that
739 -- while Querying for the Availability.
740 IF p_lot_number IS NOT NULL THEN
741 l_available_qty := l_available_qty - p_primary_quantity;
742 END IF;*/
743
744 IF l_debug = 1 THEN
745 print_debug('Cycle Count Reservations will be created with...', l_api_name, g_info);
746 print_debug(' Organization ID = ' || p_organization_id, l_api_name, g_info);
747 print_debug(' Inventory ID = ' || p_inventory_item_id, l_api_name, g_info);
748 print_debug(' Revision = ' || p_revision, l_api_name, g_info);
749 print_debug(' Lot Number = ' || p_lot_number, l_api_name, g_info);
750 print_debug(' Subinventory Code = ' || p_subinventory_code, l_api_name, g_info);
751 print_debug(' Locator ID = ' || p_locator_id, l_api_name, g_info);
752 print_debug(' Reservation ID = ' || p_reservation_id, l_api_name, g_info);
753 print_debug(' Reported Missing Qty = ' || p_primary_quantity, l_api_name, g_info);
754 print_debug(' Remaining Available Qty = ' || l_available_qty, l_api_name, g_info);
755 print_debug(' Secondary Remaining Available Qty = ' || p_secondary_quantity, l_api_name, g_info);
756 END IF;
757
758 /* Querying MTR to check for any Reservation with the same values as that of the new
759 Cycle Count Reservation record to be created */
760 inv_reservation_pvt.query_reservation(
761 p_api_version_number => 1.0
762 , p_init_msg_lst => fnd_api.g_false
763 , x_return_status => x_return_status
764 , x_msg_count => l_msg_count
765 , x_msg_data => l_msg_data
766 , p_query_input => l_cc_rsv_rec
767 , x_mtl_reservation_tbl => l_reservations_tbl
768 , x_mtl_reservation_tbl_count => l_reservation_count
769 , x_error_code => l_api_error_code
770 );
771
772 IF x_return_status <> fnd_api.g_ret_sts_success THEN
773 IF l_debug = 1 THEN
774 print_debug('Error: Querying Reservations to check for any existing reservation failed', l_api_name, g_error);
775 END IF;
776 fnd_message.set_name('INV', 'INV_QRY_RSV_FAILED');
777 fnd_msg_pub.ADD;
778 RAISE fnd_api.g_exc_unexpected_error;
779 END IF;
780
781 IF l_debug = 1 THEN
782 print_debug('Number of CC Reservations existing for Item = ' || l_reservation_count, l_api_name, g_info);
783 END IF;
784
785 IF l_reservation_count > 1 THEN
786 IF l_debug = 1 THEN
787 print_debug('Error: Query Reservation returned more than one record', l_api_name, g_error);
788 END IF;
789 fnd_message.set_name('INV', 'INV_NON_UNIQUE_RSV');
790 fnd_msg_pub.ADD;
791 RAISE fnd_api.g_exc_unexpected_error;
792 END IF;
793
794 l_update_rsv := (l_reservation_count = 1);
795
796 -- Create a Cycle Count Reservation for the Quantity reported as Missing.
797 IF l_debug = 1 THEN
798 print_debug('Creating Cycle Count Reservations for the Quantity reported', l_api_name, g_info);
799 END IF;
800 /*Bug#3869184. If there is only one Cycle Count Reservation, capture the primary and secondary
801 reservation quantities corresponding to that reservation in the newly added variables*/
802 If (l_update_rsv AND (p_reservation_id IS NOT NULL)) Then
803 l_existing_cc_res_pri_qty := l_reservations_tbl(1).primary_reservation_quantity;
804 l_existing_cc_res_sec_qty := NVL(l_reservations_tbl(1).secondary_reservation_quantity, 0);
805 IF l_debug = 1 THEN
806 print_debug('l_existing_cc_res_pri_qty:'||l_existing_cc_res_pri_qty, l_api_name, g_info);
807 print_debug('l_existing_cc_res_sec_qty:'||l_existing_cc_res_sec_qty, l_api_name, g_info);
808 END IF;
809
810 End If;
811
812 -- If Reservation already exists, Transfer the existing Reservation. Otherwise Create a new one.
813 IF p_reservation_id IS NOT NULL THEN -- Transfer the Reservation
814 IF l_debug = 1 THEN
815 print_debug('Transferring the existing Reservation to a Cycle Count Reservation', l_api_name, g_info);
816 END IF;
817
818 l_existing_rsv_rec.reservation_id := p_reservation_id;
819 l_cc_rsv_rec.primary_reservation_quantity := p_primary_quantity;
820 --INVCONV kkillams
821 l_cc_rsv_rec.secondary_reservation_quantity := p_secondary_quantity; --INCONV kkillams
822 IF l_cc_rsv_rec.secondary_reservation_quantity = 0 THEN
823 l_cc_rsv_rec.secondary_reservation_quantity := NULL;
824 END IF;
825 --END INVCONV kkillams
826 inv_reservation_pvt.transfer_reservation(
827 x_return_status => x_return_status
828 , x_msg_count => l_msg_count
829 , x_msg_data => l_msg_data
830 , x_reservation_id => l_new_reservation_id
831 , p_api_version_number => 1.0
832 , p_init_msg_lst => fnd_api.g_true
833 , p_original_rsv_rec => l_existing_rsv_rec
834 , p_to_rsv_rec => l_cc_rsv_rec
835 , p_original_serial_number => l_dummy_sn
836 , p_validation_flag => fnd_api.g_true
837 );
838
839 IF x_return_status <> fnd_api.g_ret_sts_success THEN
840 IF l_debug = 1 THEN
841 print_debug('Call to Transfer Reservation API Failed', l_api_name, g_error);
842 END IF;
843 fnd_message.set_name('INV','INV_TRANSFER_RSV_FAILED');
844 fnd_msg_pub.ADD;
845 RAISE fnd_api.g_exc_unexpected_error;
846 END IF;
847
848 -- The Reservation created below will always be updated in the Reservation created now.
849 l_cc_rsv_rec.reservation_id := l_new_reservation_id;
850
851 ELSE -- Create a new Reservation
852 /* Though MMTT doesnt have any Reservation ID, there may be someother record with
853 the same Reservation parameters. Rather than creating a new reservation, the
854 existing reservation is updated */
855 IF l_update_rsv THEN
856 l_cc_rsv_rec := l_reservations_tbl(1);
857 l_cc_rsv_rec.primary_reservation_quantity := l_cc_rsv_rec.primary_reservation_quantity + p_primary_quantity;
858 --INVCONV KKILLAMS
859 l_cc_rsv_rec.secondary_reservation_quantity := NVL(l_cc_rsv_rec.secondary_reservation_quantity,0) + NVL(p_secondary_quantity,0); --INVCONV kkillams
860 IF l_cc_rsv_rec.secondary_reservation_quantity = 0 THEN
861 l_cc_rsv_rec.secondary_reservation_quantity := NULL;
862 END IF;
863 --END INVCONV kkillams
864 l_cc_rsv_rec.reservation_quantity := NULL;
865 inv_reservation_pvt.update_reservation(
866 x_return_status => x_return_status
867 , x_msg_count => l_msg_count
868 , x_msg_data => l_msg_data
869 , p_api_version_number => 1.0
870 , p_init_msg_lst => fnd_api.g_false
871 , p_original_rsv_rec => l_reservations_tbl(1)
872 , p_to_rsv_rec => l_cc_rsv_rec
873 , p_original_serial_number => l_dummy_sn
874 , p_to_serial_number => l_dummy_sn
875 , p_validation_flag => fnd_api.g_true
876 );
877 IF x_return_status <> fnd_api.g_ret_sts_success THEN
878 fnd_message.set_name('INV','INV_UPDATE_RSV_FAILED');
879 fnd_msg_pub.ADD;
880 RAISE fnd_api.g_exc_unexpected_error;
881 END IF;
882 ELSE
883 l_cc_rsv_rec.primary_reservation_quantity := p_primary_quantity;
884 --INVCONV kkillams
885 l_cc_rsv_rec.secondary_reservation_quantity := p_secondary_quantity;
886 IF l_cc_rsv_rec.secondary_reservation_quantity = 0 THEN
887 l_cc_rsv_rec.secondary_reservation_quantity := NULL;
888 END IF;
889 --END INVCONV kkillams
890 inv_reservation_pvt.create_reservation(
891 x_return_status => x_return_status
892 , x_msg_count => l_msg_count
893 , x_msg_data => l_msg_data
894 , x_reservation_id => l_new_reservation_id
895 , x_quantity_reserved => l_qty_reserved
896 , x_secondary_quantity_reserved=> l_sec_qty_reserved --INVCONV kkillams
897 , p_api_version_number => 1.0
898 , p_init_msg_lst => fnd_api.g_true
899 , p_rsv_rec => l_cc_rsv_rec
900 , p_serial_number => l_dummy_sn
901 , x_serial_number => l_dummy_sn
902 , p_validation_flag => fnd_api.g_true
903 , p_partial_reservation_flag => fnd_api.g_false
904 , p_force_reservation_flag => fnd_api.g_false
905 );
906 IF x_return_status <> fnd_api.g_ret_sts_success THEN
907 fnd_message.set_name('INV','INV_CREATE_RSV_FAILED');
908 fnd_msg_pub.ADD;
909 RAISE fnd_api.g_exc_unexpected_error;
910 END IF;
911
912 /* Since a new Reservation is created, the next Reservation created below should be
913 updated in the Reservation created now */
914 l_cc_rsv_rec.reservation_id := l_new_reservation_id;
915 END IF;
916 END IF;
917
918 get_availability(l_cc_rsv_rec,
919 l_available_qty,
920 l_sec_available_qty); --INVCONV kkillams
921
922 -- Create a Cycle Count Reservation for the remaining Available Quantity.
923 IF l_available_qty > 0 THEN
924 IF l_debug = 1 THEN
925 print_debug('Creating Cycle Count Reservations for the remaining Availability', l_api_name, g_info);
926 END IF;
927
928 l_existing_rsv_rec := l_cc_rsv_rec;
929
930 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
931 --INVCONV kkillams
932 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
933 IF l_cc_rsv_rec.secondary_reservation_quantity = 0 THEN
934 l_cc_rsv_rec.secondary_reservation_quantity := NULL;
935 END IF;
936 --END INVCONV kkillams
937
938 l_cc_rsv_rec.reservation_quantity := NULL;
939 inv_reservation_pvt.update_reservation(
940 x_return_status => x_return_status
941 , x_msg_count => l_msg_count
942 , x_msg_data => l_msg_data
943 , p_api_version_number => 1.0
944 , p_init_msg_lst => fnd_api.g_false
945 , p_original_rsv_rec => l_existing_rsv_rec
946 , p_to_rsv_rec => l_cc_rsv_rec
947 , p_original_serial_number => l_dummy_sn
948 , p_to_serial_number => l_dummy_sn
949 , p_validation_flag => fnd_api.g_true
950 );
951 IF x_return_status <> fnd_api.g_ret_sts_success THEN
952 fnd_message.set_name('INV','INV_UPDATE_RSV_FAILED');
953 fnd_msg_pub.ADD;
954 RAISE fnd_api.g_exc_unexpected_error;
955 END IF;
956 END IF;
957 EXCEPTION
958 WHEN fnd_api.g_exc_error THEN
959 x_return_status := fnd_api.g_ret_sts_error;
960 fnd_msg_pub.count_and_get(p_encoded=>fnd_api.g_false, p_data => l_msg_data, p_count => l_msg_count);
961 IF l_debug = 1 THEN
962 print_debug('Exception: Expected: Message = ' || l_msg_data, l_api_name, g_exception);
963 END IF;
964 WHEN fnd_api.g_exc_unexpected_error THEN
965 x_return_status := fnd_api.g_ret_sts_unexp_error;
966 fnd_msg_pub.count_and_get(p_encoded=>fnd_api.g_false, p_data => l_msg_data, p_count => l_msg_count);
967 IF l_debug = 1 THEN
968 print_debug('Exception: Unexpected: Message = ' || l_msg_data, l_api_name, g_exception);
969 END IF;
970 WHEN OTHERS THEN
971 x_return_status := fnd_api.g_ret_sts_unexp_error;
972 IF l_debug = 1 THEN
973 print_debug('Exception: Others: Message = ' || SQLERRM, l_api_name, g_exception);
974 END IF;
975 END create_cc_reservations;
976
977 PROCEDURE report_cycle_count(
978 x_return_status OUT NOCOPY VARCHAR2
979 , x_msg_data OUT NOCOPY VARCHAR2
980 , x_msg_count OUT NOCOPY NUMBER
981 , p_transaction_temp_id NUMBER
982 , p_missing_quantity NUMBER
983 , p_lot_control_code NUMBER
984 , p_sec_missing_quantity NUMBER --INVCONV kkillams
985 ) IS
986 l_api_name VARCHAR2(30) := 'REPORT_CYC_CNT';
987 l_debug NUMBER := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'), 0);
988
989 CURSOR c_mmtt_info IS
990 SELECT mmtt.organization_id
991 , mmtt.inventory_item_id
992 , mmtt.reservation_id
993 , mmtt.revision
994 , mmtt.subinventory_code
995 , mmtt.locator_id
996 , mmtt.transaction_uom
997 , msi.primary_uom_code
998 , msi.secondary_uom_code --INVCONV kkillams
999 FROM mtl_material_transactions_temp mmtt, mtl_system_items msi
1000 WHERE mmtt.transaction_temp_id = p_transaction_temp_id
1001 AND msi.inventory_item_id = mmtt.inventory_item_id
1002 AND msi.organization_id = mmtt.organization_id;
1003
1004 --Bug #3380708 - added the group by clause
1005 CURSOR c_unconfirmed_lots IS
1006 SELECT lot_number
1007 ,SUM(transaction_quantity) transaction_quantity
1008 ,SUM(primary_quantity) primary_quantity
1009 ,DECODE(SUM(NVL(secondary_quantity,0)),0,NULL,SUM(NVL(secondary_quantity,0))) secondary_quantity --INVCONV kkillams
1010 FROM mtl_allocations_gtmp
1011 WHERE transaction_temp_id = p_transaction_temp_id
1012 GROUP BY lot_number;
1013
1014
1015 l_mmtt_info c_mmtt_info%ROWTYPE;
1016 l_primary_missing_qty NUMBER;
1017 l_rem_missing_qty NUMBER;
1018 l_primary_lot_qty NUMBER;
1019
1020 l_secondary_lot_qty NUMBER; --INVCONV KKILLAMS
1021 BEGIN
1022 x_return_status := fnd_api.g_ret_sts_success;
1023
1024 IF l_debug = 1 THEN
1025 print_debug('Creating Cycle Count Reservation to report Missing Material', l_api_name, g_info);
1026 END IF;
1027
1028 OPEN c_mmtt_info;
1029 FETCH c_mmtt_info INTO l_mmtt_info;
1030 IF c_mmtt_info%NOTFOUND THEN
1031 IF l_debug = 1 THEN
1032 print_debug('Error: No Records Found in MMTT for the given query criteria', l_api_name, g_info);
1033 END IF;
1034 END IF;
1035
1036 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);
1037
1038
1039 IF p_lot_control_code = 1 THEN -- Not a Lot Controlled Item
1040 create_cc_reservations(
1041 x_return_status => x_return_status
1042 , p_organization_id => l_mmtt_info.organization_id
1043 , p_inventory_item_id => l_mmtt_info.inventory_item_id
1044 , p_reservation_id => l_mmtt_info.reservation_id
1045 , p_revision => l_mmtt_info.revision
1046 , p_lot_number => NULL
1047 , p_subinventory_code => l_mmtt_info.subinventory_code
1048 , p_locator_id => l_mmtt_info.locator_id
1049 , p_primary_quantity => l_primary_missing_qty
1050 , p_primary_uom_code => l_mmtt_info.primary_uom_code
1051 --INVCONV kkillams
1052 , p_secondary_quantity => p_sec_missing_quantity
1053 , p_secondary_uom_code => l_mmtt_info.secondary_uom_code
1054 --INVCONV kkillams
1055 );
1056 ELSE
1057 l_rem_missing_qty := l_primary_missing_qty;
1058 FOR curr_lot IN c_unconfirmed_lots LOOP
1059 l_primary_lot_qty := least(curr_lot.primary_quantity, l_primary_missing_qty);
1060 l_secondary_lot_qty := least(NVL(curr_lot.secondary_quantity,0), NVL(p_sec_missing_quantity,0)); --INVCONV
1061 IF l_secondary_lot_qty = 0 THEN
1062 l_secondary_lot_qty := NULL;
1063 END IF;
1064 create_cc_reservations(
1065 x_return_status => x_return_status
1066 , p_organization_id => l_mmtt_info.organization_id
1067 , p_inventory_item_id => l_mmtt_info.inventory_item_id
1068 , p_reservation_id => l_mmtt_info.reservation_id
1069 , p_revision => l_mmtt_info.revision
1070 , p_lot_number => curr_lot.lot_number
1071 , p_subinventory_code => l_mmtt_info.subinventory_code
1072 , p_locator_id => l_mmtt_info.locator_id
1073 , p_primary_quantity => l_primary_lot_qty
1074 , p_primary_uom_code => l_mmtt_info.primary_uom_code
1075 --INVCONV kkillams
1076 , p_secondary_quantity => l_secondary_lot_qty
1077 , p_secondary_uom_code => l_mmtt_info.secondary_uom_code
1078 --INVCONV kkillams
1079 );
1080 l_rem_missing_qty := l_rem_missing_qty - l_primary_lot_qty;
1081 EXIT WHEN l_rem_missing_qty <= 0;
1082 END LOOP;
1083 END IF;
1084 EXCEPTION
1085 WHEN OTHERS THEN
1086 x_return_status := fnd_api.g_ret_sts_unexp_error;
1087 END report_cycle_count;
1088
1089 PROCEDURE populate_tt_lot(
1090 x_return_status OUT NOCOPY VARCHAR2
1091 , p_transaction_temp_id IN NUMBER
1092 , p_mo_line_id NUMBER
1093 ) IS
1094 l_api_name VARCHAR2(30) := 'POPULATE_TABLE';
1095 l_debug NUMBER := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'), 0);
1096 BEGIN
1097 x_return_status := fnd_api.g_ret_sts_success;
1098
1099 IF l_debug = 1 THEN
1100 print_debug('Populating Temp Table for a Lot Ctrl Item', l_api_name, g_info);
1101 END IF;
1102
1103 INSERT INTO mtl_allocations_gtmp(transaction_temp_id, lot_number, transaction_quantity, primary_quantity
1104 ,secondary_quantity) --INVCONV kkillams
1105 SELECT p_transaction_temp_id, mtlt.lot_number, SUM(mtlt.transaction_quantity), SUM(mtlt.primary_quantity)
1106 ,DECODE(SUM(NVL(mtlt.secondary_quantity,0)),0,NULL,SUM(NVL(mtlt.secondary_quantity,0))) --INVCONV kkillams
1107 FROM mtl_transaction_lots_temp mtlt
1108 WHERE p_transaction_temp_id IS NOT NULL
1109 AND mtlt.transaction_temp_id = p_transaction_temp_id
1110 GROUP BY mtlt.lot_number
1111 UNION ALL
1112 SELECT mmtt.transaction_temp_id, mtlt.lot_number, SUM(mtlt.transaction_quantity), SUM(mtlt.primary_quantity)
1113 ,DECODE(SUM(NVL(mtlt.secondary_quantity,0)),0,NULL,SUM(NVL(mtlt.secondary_quantity,0))) --INVCONV kkillams
1114 FROM mtl_material_transactions_temp mmtt, mtl_transaction_lots_temp mtlt
1115 WHERE p_transaction_temp_id IS NULL AND p_mo_line_id IS NOT NULL
1116 AND mmtt.move_order_line_id = p_mo_line_id
1117 AND mtlt.transaction_temp_id = mmtt.transaction_temp_id
1118 GROUP BY mmtt.transaction_temp_id, mtlt.lot_number;
1119
1120 IF l_debug = 1 THEN
1121 print_debug('Allocations Temp Table populated with # of records = ' || SQL%ROWCOUNT, l_api_name, g_info);
1122 END IF;
1123
1124 IF SQL%ROWCOUNT = 0 THEN
1125 IF l_debug = 1 THEN
1126 print_debug('Error: No Records Found for the Given Query Criteria', l_api_name, g_error);
1127 END IF;
1128 x_return_status := fnd_api.g_ret_sts_error;
1129 END IF;
1130
1131 IF l_debug = 1 THEN
1132 print_debug('Populated Temp Table with Lot Information', l_api_name, g_info);
1133 END IF;
1134 EXCEPTION
1135 WHEN OTHERS THEN
1136 x_return_status := fnd_api.g_ret_sts_unexp_error;
1137 IF fnd_msg_pub.check_msg_level(fnd_msg_pub.g_msg_lvl_unexp_error) THEN
1138 fnd_msg_pub.add_exc_msg(g_pkg_name, l_api_name);
1139 END IF;
1140 IF l_debug = 1 THEN
1141 print_debug('Exception: Unknown Error occurred: SQLCode = ' || SQLCODE, l_api_name, g_exception);
1142 END IF;
1143 END populate_tt_lot;
1144
1145 PROCEDURE populate_tt_serial(
1146 x_return_status OUT NOCOPY VARCHAR2
1147 , p_transaction_temp_id IN NUMBER
1148 , p_mo_line_id NUMBER
1149 ) IS
1150 l_api_name VARCHAR2(30) := 'POPULATE_TABLE';
1151 l_debug NUMBER := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'), 0);
1152 BEGIN
1153 x_return_status := fnd_api.g_ret_sts_success;
1154
1155 IF l_debug = 1 THEN
1156 print_debug('Populating Temp Table for a Serial Ctrl Item', l_api_name, g_info);
1157 END IF;
1158
1159 INSERT INTO mtl_allocations_gtmp(transaction_temp_id, serial_number)
1160 SELECT p_transaction_temp_id, msn.serial_number
1161 FROM mtl_serial_numbers msn
1162 WHERE p_transaction_temp_id IS NOT NULL
1163 AND msn.group_mark_id = p_transaction_temp_id
1164 UNION ALL
1165 SELECT mmtt.transaction_temp_id, msn.serial_number
1166 FROM mtl_material_transactions_temp mmtt, mtl_serial_numbers msn
1167 WHERE p_transaction_temp_id IS NULL AND p_mo_line_id IS NOT NULL
1168 AND mmtt.move_order_line_id = p_mo_line_id
1169 AND msn.group_mark_id = mmtt.transaction_temp_id;
1170
1171 IF l_debug = 1 THEN
1172 print_debug('Allocations Temp Table populated with # of records = ' || SQL%ROWCOUNT, l_api_name, g_info);
1173 print_debug('Populated Temp Table with Serial Information', l_api_name, g_info);
1174 END IF;
1175 EXCEPTION
1176 WHEN OTHERS THEN
1177 x_return_status := fnd_api.g_ret_sts_unexp_error;
1178 IF fnd_msg_pub.check_msg_level(fnd_msg_pub.g_msg_lvl_unexp_error) THEN
1179 fnd_msg_pub.add_exc_msg(g_pkg_name, l_api_name);
1180 END IF;
1181 IF l_debug = 1 THEN
1182 print_debug('Exception: Unknown Error occurred: SQLCode = ' || SQLCODE, l_api_name, g_exception);
1183 END IF;
1184 END populate_tt_serial;
1185
1186 PROCEDURE populate_tt_lot_serial(
1187 x_return_status OUT NOCOPY VARCHAR2
1188 , p_transaction_temp_id IN NUMBER
1189 , p_mo_line_id NUMBER
1190 ) IS
1191 l_api_name VARCHAR2(30) := 'POPULATE_TABLE';
1192 l_debug NUMBER := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'), 0);
1193 BEGIN
1194 x_return_status := fnd_api.g_ret_sts_success;
1195
1196 IF l_debug = 1 THEN
1197 print_debug('Populating Temp Table for a Lot and Serial Ctrl Item', l_api_name, g_info);
1198 END IF;
1199
1200 INSERT INTO mtl_allocations_gtmp(transaction_temp_id, lot_number, serial_number, transaction_quantity, primary_quantity)
1201 SELECT p_transaction_temp_id, mtlt.lot_number, msn.serial_number, 1, 1
1202 FROM mtl_transaction_lots_temp mtlt, mtl_serial_numbers msn
1203 WHERE p_transaction_temp_id IS NOT NULL
1204 AND mtlt.transaction_temp_id = p_transaction_temp_id
1205 AND msn.group_mark_id = mtlt.serial_transaction_temp_id
1206 UNION ALL
1207 SELECT mmtt.transaction_temp_id, mtlt.lot_number, msn.serial_number, 1, 1
1208 FROM mtl_material_transactions_temp mmtt, mtl_transaction_lots_temp mtlt, mtl_serial_numbers msn
1209 WHERE p_transaction_temp_id IS NULL and p_mo_line_id IS NOT NULL
1210 AND mmtt.move_order_line_id = p_mo_line_id
1211 AND mtlt.transaction_temp_id = mmtt.transaction_temp_id
1212 AND msn.group_mark_id = mtlt.serial_transaction_temp_id;
1213
1214 IF l_debug = 1 THEN
1215 print_debug('Allocations Temp Table populated with # of records = ' || SQL%ROWCOUNT, l_api_name, g_info);
1216 END IF;
1217
1218 IF SQL%ROWCOUNT = 0 THEN
1219 IF l_debug = 1 THEN
1220 print_debug('No Serial Allocations found. Querying again only for Lot', l_api_name, g_info);
1221 END IF;
1222 populate_tt_lot(x_return_status, p_transaction_temp_id, p_mo_line_id);
1223 END IF;
1224
1225 IF l_debug = 1 THEN
1226 print_debug('Populated Temp Table with Lot and Serial Information', l_api_name, g_info);
1227 END IF;
1228 EXCEPTION
1229 WHEN OTHERS THEN
1230 x_return_status := fnd_api.g_ret_sts_unexp_error;
1231 IF fnd_msg_pub.check_msg_level(fnd_msg_pub.g_msg_lvl_unexp_error) THEN
1232 fnd_msg_pub.add_exc_msg(g_pkg_name, l_api_name);
1233 END IF;
1234 IF l_debug = 1 THEN
1235 print_debug('Exception: Unknown Error occurred: SQLCode = ' || SQLCODE, l_api_name, g_exception);
1236 END IF;
1237 END populate_tt_lot_serial;
1238
1239 PROCEDURE populate_table(
1240 x_return_status OUT NOCOPY VARCHAR2
1241 , x_msg_data OUT NOCOPY VARCHAR2
1242 , x_msg_count OUT NOCOPY NUMBER
1243 , p_transaction_temp_id NUMBER
1244 , p_mo_line_id NUMBER
1245 , p_lot_control_code NUMBER
1246 , p_serial_control_code NUMBER
1247 ) IS
1248 l_api_name VARCHAR2(30) := 'POPULATE_TABLE';
1249 l_debug NUMBER := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'), 0);
1250 l_lot_control_code NUMBER := p_lot_control_code;
1251 l_serial_control_code NUMBER := p_serial_control_code;
1252 BEGIN
1253 x_return_status := fnd_api.g_ret_sts_success;
1254
1255 -- Printing the Input Parameters.
1256 IF l_debug = 1 THEN
1257 print_debug('Populating the Allocations Temp Table with Suggested Lots/Serials', l_api_name, g_info);
1258 print_debug('Transaction Temp ID = ' || p_transaction_temp_id, l_api_name, g_info);
1259 print_debug('Move Order Line ID = ' || p_mo_line_id, l_api_name, g_info);
1260 print_debug('Lot Control Code = ' || p_lot_control_code, l_api_name, g_info);
1261 print_debug('Serial Control Code = ' || p_serial_control_code, l_api_name, g_info);
1262 END IF;
1263
1264 -- Either Transaction Temp ID or Move Order Line ID has to be passed.
1265 IF p_transaction_temp_id IS NULL AND p_mo_line_id IS NULL THEN
1266 IF l_debug = 1 THEN
1267 print_debug('Error: Either TxnTmpID or MOLineID has to be passed', l_api_name, g_error);
1268 END IF;
1269 fnd_message.set_name('INV', 'INV_MISSING_REQUIRED_PARAMETER');
1270 fnd_msg_pub.ADD;
1271 RAISE fnd_api.g_exc_error;
1272 END IF;
1273
1274 -- Determining the Item Controls.
1275 IF p_lot_control_code IS NULL OR p_serial_control_code IS NULL THEN
1276 get_item_controls(
1277 x_return_status => x_return_status
1278 , x_lot_control_code => l_lot_control_code
1279 , x_serial_control_code => l_serial_control_code
1280 , p_transaction_temp_id => p_transaction_temp_id
1281 , p_mo_line_id => p_mo_line_id
1282 );
1283 IF x_return_status <> fnd_api.g_ret_sts_success THEN
1284 IF l_debug = 1 THEN
1285 print_debug('Error: Cannot determine the Item Controls', l_api_name, g_error);
1286 END IF;
1287 fnd_message.set_name('INV','INV_INVALID_ITEM_ORG');
1288 fnd_msg_pub.ADD;
1289 RAISE fnd_api.g_exc_error;
1290 END IF;
1291 END IF;
1292
1293 -- First clear Allocations Temp Table.
1294 DELETE mtl_allocations_gtmp;
1295
1296 IF l_lot_control_code = 2 AND l_serial_control_code IN(1, 6) THEN
1297 populate_tt_lot(x_return_status, p_transaction_temp_id, p_mo_line_id);
1298 ELSIF l_lot_control_code = 1 AND l_serial_control_code NOT IN(1, 6) THEN
1299 populate_tt_serial(x_return_status, p_transaction_temp_id, p_mo_line_id);
1300 ELSIF l_lot_control_code = 2 AND l_serial_control_code NOT IN(1, 6) THEN
1301 populate_tt_lot_serial(x_return_status, p_transaction_temp_id, p_mo_line_id);
1302 END IF;
1303 IF x_return_status <> fnd_api.g_ret_sts_success THEN
1304 IF l_debug = 1 THEN
1305 print_debug('Error: Not able to Populate the Allocations Temp Table', l_api_name, g_error);
1306 END IF;
1307 RAISE fnd_api.g_exc_unexpected_error;
1308 END IF;
1309
1310 IF l_debug = 1 THEN
1311 print_debug('Allocations Temp Table Populated with the Suggested Lots/Serials', l_api_name, g_info);
1312 END IF;
1313 EXCEPTION
1314 WHEN fnd_api.g_exc_error THEN
1315 x_return_status := fnd_api.g_ret_sts_error;
1316 fnd_msg_pub.count_and_get(p_count => x_msg_count, p_data => x_msg_data);
1317 IF l_debug = 1 THEN
1318 print_debug('Exception: Expected Error occurred', l_api_name, g_exception);
1319 END IF;
1320 WHEN fnd_api.g_exc_unexpected_error THEN
1321 x_return_status := fnd_api.g_ret_sts_unexp_error;
1322 fnd_msg_pub.count_and_get(p_count => x_msg_count, p_data => x_msg_data);
1323 IF l_debug = 1 THEN
1324 print_debug('Exception: UnExpected Error occurred', l_api_name, g_exception);
1325 END IF;
1326 WHEN OTHERS THEN
1327 x_return_status := fnd_api.g_ret_sts_unexp_error;
1328 IF fnd_msg_pub.check_msg_level(fnd_msg_pub.g_msg_lvl_unexp_error) THEN
1329 fnd_msg_pub.add_exc_msg(g_pkg_name, l_api_name);
1330 END IF;
1331 IF l_debug = 1 THEN
1332 print_debug('Exception: Unknown Error occurred: SQLCode = ' || SQLCODE, l_api_name, g_exception);
1333 END IF;
1334 END populate_table;
1335
1336 PROCEDURE process_action(
1337 x_return_status OUT NOCOPY VARCHAR2
1338 , x_msg_data OUT NOCOPY VARCHAR2
1339 , x_msg_count OUT NOCOPY NUMBER
1340 , x_new_record_id OUT NOCOPY NUMBER
1341 , p_action NUMBER
1342 , p_transaction_temp_id NUMBER
1343 , p_remaining_quantity NUMBER
1344 , p_remaining_secondary_quantity NUMBER --INVCONV KKILLALMS
1345 , p_lot_control_code NUMBER
1346 , p_serial_control_code NUMBER
1347 ) AS
1348 l_api_name VARCHAR2(30) := 'PROCESS_ACTION';
1349 l_debug NUMBER := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'), 0);
1350 l_lot_control_code NUMBER := p_lot_control_code;
1351 l_serial_control_code NUMBER := p_serial_control_code;
1352 BEGIN
1353 x_return_status := fnd_api.g_ret_sts_success;
1354
1355 -- Printing the Input Parameters.
1356 IF l_debug = 1 THEN
1357 print_debug('Processing Missing Qty Action', l_api_name, g_info);
1358 print_debug('Transaction Temp ID = ' || p_transaction_temp_id, l_api_name, g_info);
1359 print_debug('Remaining Qty = ' || p_remaining_quantity, l_api_name, g_info);
1360 print_debug('Action = ' || p_action, l_api_name, g_info);
1361 print_debug('Lot Control Code = ' || p_lot_control_code, l_api_name, g_info);
1362 print_debug('Serial Control Code = ' || p_serial_control_code, l_api_name, g_info);
1363 END IF;
1364
1365 -- If Missing Qty is Zero then just return.
1366 IF nvl(p_remaining_quantity, 0) = 0 THEN
1367 RETURN;
1368 END IF;
1369
1370 -- Check whether Transaction Temp ID is not null
1371 IF p_transaction_temp_id IS NULL THEN
1372 IF l_debug = 1 THEN
1373 print_debug('Error: Transaction Temp ID cannot be NULL', l_api_name, g_error);
1374 END IF;
1375 RAISE fnd_api.g_exc_error;
1376 END IF;
1377
1378 -- Determining the Item Controls.
1379 IF p_lot_control_code IS NULL OR p_serial_control_code IS NULL THEN
1380 get_item_controls(
1381 x_return_status => x_return_status
1382 , x_lot_control_code => l_lot_control_code
1383 , x_serial_control_code => l_serial_control_code
1384 , p_transaction_temp_id => p_transaction_temp_id
1385 , p_mo_line_id => NULL
1386 );
1387 IF x_return_status <> fnd_api.g_ret_sts_success THEN
1388 IF l_debug = 1 THEN
1389 print_debug('Error: Cannot determine the Item Controls', l_api_name, g_error);
1390 END IF;
1391 fnd_message.set_name('INV','INV_INVALID_ITEM_ORG');
1392 fnd_msg_pub.ADD;
1393 RAISE fnd_api.g_exc_error;
1394 END IF;
1395 END IF;
1396
1397 remove_confirmed(
1398 x_return_status => x_return_status
1399 , p_transaction_temp_id => p_transaction_temp_id
1400 , p_lot_control_code => l_lot_control_code
1401 , p_serial_control_code => l_serial_control_code
1402 );
1403
1404 IF p_action = g_action_backorder THEN
1405 backorder_only(
1406 x_return_status => x_return_status
1407 , x_msg_data => x_msg_data
1408 , x_msg_count => x_msg_count
1409 , p_transaction_temp_id => p_transaction_temp_id
1410 , p_quantity => p_remaining_quantity
1411 , p_secondary_quantity => p_remaining_secondary_quantity --INVCONV kkillams
1412 );
1413 ELSIF p_action = g_action_split_allocation THEN
1414 split_allocation(
1415 x_return_status => x_return_status
1416 , x_msg_data => x_msg_data
1417 , x_msg_count => x_msg_count
1418 , x_new_txn_temp_id => x_new_record_id
1419 , p_transaction_temp_id => p_transaction_temp_id
1420 , p_split_quantity => p_remaining_quantity
1421 , p_lot_control_code => l_lot_control_code
1422 , p_serial_control_code => l_serial_control_code
1423 , p_split_sec_quantity => p_remaining_secondary_quantity --INVCONV kkillams
1424 );
1425 ELSIF p_action = g_action_cycle_count THEN
1426 report_cycle_count(
1427 x_return_status => x_return_status
1428 , x_msg_data => x_msg_data
1429 , x_msg_count => x_msg_count
1430 , p_transaction_temp_id => p_transaction_temp_id
1431 , p_missing_quantity => p_remaining_quantity
1432 , p_lot_control_code => l_lot_control_code
1433 , p_sec_missing_quantity => p_remaining_secondary_quantity --INVCONV kkillams
1434 );
1435 END IF;
1436 IF x_return_status <> fnd_api.g_ret_sts_success THEN
1437 RAISE fnd_api.g_exc_unexpected_error;
1438 END IF;
1439 EXCEPTION
1440 WHEN fnd_api.g_exc_error THEN
1441 x_return_status := fnd_api.g_ret_sts_error;
1442 fnd_msg_pub.count_and_get(p_count => x_msg_count, p_data => x_msg_data);
1443 IF l_debug = 1 THEN
1444 print_debug('Exception: Expected Error occurred', l_api_name, g_exception);
1445 END IF;
1446 WHEN fnd_api.g_exc_unexpected_error THEN
1447 x_return_status := fnd_api.g_ret_sts_unexp_error;
1448 fnd_msg_pub.count_and_get(p_count => x_msg_count, p_data => x_msg_data);
1449 IF l_debug = 1 THEN
1450 print_debug('Exception: Unexpected Error occurred', l_api_name, g_exception);
1451 END IF;
1452 WHEN OTHERS THEN
1453 x_return_status := fnd_api.g_ret_sts_unexp_error;
1454 IF fnd_msg_pub.check_msg_level(fnd_msg_pub.g_msg_lvl_unexp_error) THEN
1455 fnd_msg_pub.add_exc_msg(g_pkg_name, l_api_name);
1456 END IF;
1457 IF l_debug = 1 THEN
1458 print_debug('Exception: Unknown Error occurred: SQLCode = ' || SQLCODE, l_api_name, g_exception);
1459 END IF;
1460 END process_action;
1461
1462 PROCEDURE update_allocation_qty
1463 (
1464 x_return_status OUT NOCOPY VARCHAR2
1465 , x_msg_data OUT NOCOPY VARCHAR2
1466 , x_msg_count OUT NOCOPY NUMBER
1467 , p_transaction_temp_id NUMBER
1468 , p_confirmed_quantity NUMBER
1469 , p_transaction_uom VARCHAR2
1470 --INVCONV kkillams
1471 , p_sec_confirmed_quantity NUMBER
1472 , p_secondary_uom_code VARCHAR2
1473 --INVCONV kkillams
1474 )
1475 IS
1476 l_api_name VARCHAR2(30) := 'UPDATE_ALLOCATION_QTY';
1477 l_debug NUMBER := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'), 0);
1478 l_confirmed_quantity_primary NUMBER;
1479 l_primary_uom VARCHAR2(30);
1480 l_inventory_item_id NUMBER;
1481 l_organization_id NUMBER;
1482
1483 BEGIN
1484 x_return_status := fnd_api.g_ret_sts_success;
1485
1486 IF l_debug = 1 THEN
1487 print_debug('Updating Allocation Qty', l_api_name, g_info);
1488 print_debug('Transaction Temp ID = ' || p_transaction_temp_id, l_api_name, g_info);
1489 print_debug('Confirmed_quantity = ' || p_confirmed_quantity, l_api_name, g_info);
1490 END IF;
1491
1492 SELECT inventory_item_id, organization_id INTO l_inventory_item_id,l_organization_id
1493 FROM mtl_material_transactions_temp WHERE transaction_temp_id = p_transaction_temp_id;
1494
1495 SELECT primary_uom_code INTO l_primary_uom FROM mtl_system_items
1496 WHERE inventory_item_id =l_inventory_item_id
1497 AND organization_id =l_organization_id;
1498
1499
1500 IF l_primary_uom <> p_transaction_uom THEN
1501 l_confirmed_quantity_primary :=
1502 inv_convert.inv_um_convert(
1503 item_id => null
1504 , precision => null
1505 , from_quantity => p_confirmed_quantity
1506 , from_unit => p_transaction_uom
1507 , to_unit => l_primary_uom
1508 , from_name => null
1509 , to_name => null
1510 );
1511 IF ( l_confirmed_quantity_primary < 0 )THEN
1512 fnd_message.set_name('INV','INV_UOM_CONV_ERROR');
1513 fnd_msg_pub.ADD;
1514 RAISE fnd_api.g_exc_error;
1515 END IF;
1516
1517 ELSE
1518 l_confirmed_quantity_primary:=p_confirmed_quantity;
1519 END IF;
1520
1521 UPDATE mtl_material_transactions_temp SET transaction_quantity =p_confirmed_quantity
1522 , primary_quantity= l_confirmed_quantity_primary
1523 --INVCONV kkillams
1524 , secondary_uom_code = p_secondary_uom_code
1525 , secondary_transaction_quantity = p_sec_confirmed_quantity
1526 --END INVCONV kkillams
1527 WHERE transaction_temp_id = p_transaction_temp_id;
1528
1529
1530
1531 EXCEPTION
1532 WHEN fnd_api.g_exc_error THEN
1533 x_return_status := fnd_api.g_ret_sts_error;
1534 fnd_msg_pub.count_and_get(p_count => x_msg_count, p_data => x_msg_data);
1535 IF l_debug = 1 THEN
1536 print_debug('Exception: Expected Error occurred', l_api_name, g_exception);
1537 END IF;
1538 WHEN OTHERS THEN
1539 x_return_status := fnd_api.g_ret_sts_unexp_error;
1540 fnd_msg_pub.count_and_get(p_count => x_msg_count, p_data => x_msg_data);
1541 IF l_debug = 1 THEN
1542 print_debug('Exception: Unknown Error occurred: SQLCode = ' || SQLCODE, l_api_name, g_exception);
1543 END IF;
1544
1545 END update_allocation_qty;
1546
1547 END inv_missing_qty_actions_engine;