[Home] [Help]
PACKAGE BODY: APPS.MTL_LOT_UOM_CONV_PVT
Source
1 package body MTL_LOT_UOM_CONV_PVT as
2 /* $Header: INVVLUCB.pls 120.9.12020000.3 2013/01/31 19:10:32 avrose ship $ */
3 g_debug VARCHAR2 (5) := fnd_profile.VALUE ('AFLOG_LEVEL');
4 g_pkg_name CONSTANT VARCHAR2 (30) := 'MTL_LOT_UOM_CONV_PVT';
5
6 /*===========================================================================
7 -- FUNCTION:
8 -- validate_update_type
9 --
10 -- DESCRIPTION:
11 -- This function validates the lookup type value provided for INV_UPDATE_TYPE
12 -- for lot specific uom conversions.
13 --
14 -- PARAMETERS:
15 -- p_update_type IN VARCHAR2 - Type value to be validated.
16 --
17 -- return OUT NUMBER - G_TRUE or G_FALSE
18 --
19 -- SYNOPSIS:
20 -- Validate type against mfg_lookups table.
21 --
22 -- HISTORY
23 -- Joe DiIorio 01-Sept-2004 Created.
24 --
25 --=========================================================================== */
26
27 FUNCTION validate_update_type (
28 p_update_type IN VARCHAR2)
29 return NUMBER
30
31 IS
32
33 CURSOR c_val_update_type
34 IS
35 SELECT 1
36 FROM mfg_lookups
37 WHERE lookup_code = p_update_type AND
38 lookup_type = 'INV_UPDATE_TYPE';
39
40 l_count NUMBER := 0;
41
42 BEGIN
43
44 /*=========================================
45 Validate type.
46 =========================================*/
47
48 l_count := 0;
49 OPEN c_val_update_type;
50 FETCH c_val_update_type INTO l_count;
51 IF (c_val_update_type%NOTFOUND) THEN
52 CLOSE c_val_update_type;
53 RAISE NO_DATA_FOUND;
54 END IF;
55 CLOSE c_val_update_type;
56
57 return G_TRUE;
58
59
60 EXCEPTION
61
62 WHEN NO_DATA_FOUND THEN
63 FND_MESSAGE.SET_NAME('INV','INV_LOTC_UPDATETYPE_INVALID');
64 FND_MSG_PUB.Add;
65 RETURN G_FALSE;
66
67 WHEN OTHERS THEN
68 IF (SQLCODE IS NOT NULL) THEN
69 FND_MESSAGE.SET_NAME('INV','INV_LOTC_SQL_ERROR');
70 FND_MESSAGE.SET_TOKEN('ERRCODE',SQLCODE);
71 FND_MESSAGE.SET_TOKEN('ERRM',SQLERRM(SQLCODE));
72 FND_MSG_PUB.Add;
73 END IF;
74 RETURN G_FALSE;
75
76 END VALIDATE_UPDATE_TYPE;
77
78
79
80 /*===========================================================================
81 -- FUNCTION:
82 -- validate_lot_conversion_rules
83 --
84 -- DESCRIPTION:
85 -- This function validates the business rules related to lot specific uom
86 -- conversions.
87 --
88 -- PARAMETERS:
89 -- p_organization_id IN NUMBER - organization surrogate id
90 -- p_inventory_item_id IN NUMBER - item surrogate id
91 -- p_lot_number IN VARCHAR2 - lot number
92 -- p_from_uom_code IN VARCHAR2 - from uom code
93 -- p_to_uom_code IN VARCHAR2 - to uom code
94 -- p_quantity_updates IN VARCHAR2 - indicates if quantity change made 'T' or not 'F'.
95 -- p_update_type IN VARCHAR2 - indicates type of quantity update
96 -- 0 = update onhand balances
97 -- 1 = recalculate batch primary quantity
98 -- 2 = recalculate batch secondary quantity
99 -- 3 = recalculate onhand primary quantity
100 -- 4 = recalculate onhand secondary quantity
101 -- 5 = no quantity updates
102 -- p_header_id IN NUMBER - Header id of in-progress transaction.
103 --
104 -- SYNOPSIS:
105 -- Validate business rules.
106 --
107 -- HISTORY
108 -- Joe DiIorio 01-Sept-2004 Created.
109 --
110 --=========================================================================== */
111
112 FUNCTION validate_lot_conversion_rules
113 ( p_organization_id IN NUMBER
114 , p_inventory_item_id IN NUMBER
115 , p_lot_number IN VARCHAR2
116 , p_from_uom_code IN VARCHAR2
117 , p_to_uom_code IN VARCHAR2
118 , p_quantity_updates IN NUMBER
119 , p_update_type IN VARCHAR2
120 , p_header_id IN NUMBER DEFAULT NULL
121 )
122 return NUMBER
123
124 IS
125
126
127 CURSOR get_uom_class (p_uom_code VARCHAR2) IS
128 SELECT uom_class
129 FROM mtl_units_of_measure
130 WHERE uom_code = p_uom_code;
131
132 l_from_class MTL_UNITS_OF_MEASURE.UOM_CLASS%TYPE;
133 l_to_class MTL_UNITS_OF_MEASURE.UOM_CLASS%TYPE;
134 l_ret NUMBER;
135 l_revision NUMBER;
136
137 l_header_id NUMBER := NULL;
138
139 BEGIN
140
141 /*============================================
142 Call to get cache values if item is not
143 already cached.
144 ==========================================*/
145
146 IF NOT (INV_CACHE.set_item_rec(p_organization_id, p_inventory_item_id)) THEN
147 RETURN G_FALSE;
148 END IF;
149
150 /*============================================
151 Compare From/To UOM. Cannot be the same.
152 ============================================*/
153
154 IF (p_from_uom_code = p_to_uom_code) THEN
155 FND_MESSAGE.SET_NAME('INV','INV_LOTC_CROSS_UOM_ERROR');
156 FND_MSG_PUB.ADD;
157 RETURN G_FALSE;
158 END IF;
159
160 /*============================================
161 Compare From/To Class. Cannot be the same.
162 Get both classes
163 ============================================*/
164
165 OPEN get_uom_class (p_from_uom_code);
166 FETCH get_uom_class INTO l_from_class;
167 IF (get_uom_class%NOTFOUND) THEN
168 CLOSE get_uom_class;
169 FND_MESSAGE.SET_NAME('INV','INV_FROM_CLASS_ERR');
170 FND_MSG_PUB.ADD;
171 RETURN G_FALSE;
172 END IF;
173 CLOSE get_uom_class;
174
175 OPEN get_uom_class (p_to_uom_code);
176 FETCH get_uom_class INTO l_to_class;
177 IF (get_uom_class%NOTFOUND) THEN
178 CLOSE get_uom_class;
179 FND_MESSAGE.SET_NAME('INV','INV_TO_CLASS_ERR');
180 FND_MSG_PUB.ADD;
181 RETURN G_FALSE;
182 END IF;
183 CLOSE get_uom_class;
184
185
186 IF (l_from_class = l_to_class) THEN
187 FND_MESSAGE.SET_NAME('INV','INV_CLASS_EQUAL_ERR');
188 FND_MSG_PUB.ADD;
189 RETURN G_FALSE;
190 END IF;
191
192 /*========================================
193 Check if Item is serially controlled.
194 ========================================*/
195
196 IF (INV_CACHE.item_rec.serial_number_control_code <> 1) THEN
197 FND_MESSAGE.SET_NAME('INV','INV_LOT_SERIAL_SUPPORT');
198 FND_MSG_PUB.ADD;
199 RETURN G_FALSE;
200 END IF;
201
202 /*========================================
203 Check if Item is lot controlled.
204 (1 no control 2 full)
205 ========================================*/
206
207 IF (INV_CACHE.item_rec.lot_control_code <> 2) THEN
208 FND_MESSAGE.SET_NAME('INV','INV_NOTLOTCTL');
209 FND_MSG_PUB.ADD;
210 RETURN G_FALSE;
211 END IF;
212
213 /*========================================
214 Check if onhand availability problem.
215 ========================================*/
216
217 l_revision := INV_CACHE.item_rec.revision_qty_control_code;
218
219
220 l_header_id := p_header_id;
221
222 l_ret := validate_onhand_equals_avail(
223 p_organization_id,
224 p_inventory_item_id,
225 p_lot_number,
226 l_header_id);
227
228 IF (l_ret = G_FALSE) THEN
229 RETURN G_FALSE;
230 END IF;
231
232 RETURN G_TRUE;
233
234
235 EXCEPTION
236
237 WHEN OTHERS THEN
238 IF (SQLCODE IS NOT NULL) THEN
239 FND_MESSAGE.SET_NAME('INV','INV_LOTC_SQL_ERROR');
240 FND_MESSAGE.SET_TOKEN('ERRCODE',SQLCODE);
241 FND_MESSAGE.SET_TOKEN('ERRM',SQLERRM(SQLCODE));
242 ELSE
243 FND_MESSAGE.SET_NAME('INV','INV_BUSRULES_GENERIC_ERR');
244 END IF;
245 FND_MSG_PUB.ADD;
246 RETURN G_FALSE;
247
248
249 END validate_lot_conversion_rules;
250
251 /*===========================================================================
252 -- PROCEDURE
253 -- process_conversion_data
254 --
255 -- DESCRIPTION:
256 -- This PL/SQL procedure is used to insert or update a lot specific uom
257 -- conversion. It will create the audit records and create adjustment
258 -- transactions if necessary.
259 --
260 -- PARAMETERS:
261 -- p_action_type IN VARCHAR2 - I for insert, U for update.
262 -- p_update_type_indicator IN NUMBER - indicates type of quantity update
263 -- 0 = update onhand balances
264 -- 1 = recalculate batch primary quantity
265 -- 2 = recalculate batch secondary quantity
266 -- 3 = recalculate onhand primary quantity
267 -- 4 = recalculate onhand secondary quantity
268 -- 5 = no quantity updates
269 -- p_reason_id IN NUMBER - Id for Reason Code.
270 -- p_batch_id IN NUMBER - Id for Batch Number
271 -- p_lot_uom_conv_rec IN - row containing lot conversion record data.
272 -- p_qty_update_tbl IN - table containing onhand balance update data.
273 -- x_msg_count OUT NUMBER - Message count
274 -- x_msg_data OUT VARCHAR2 - If an error, send back the approriate message.
275 -- x_return_status OUT VARCHAR2 - 'S'uccess, 'E'rror, 'U'nexpected Error
276 -- x_sequence OUT VARCHAR2 - Header id from tm manager.
277 --
278 -- SYNOPSIS:
279 -- Create/update lot specific uom conversion and the supporting audit data.
280 --
281 -- HISTORY
282 -- Joe DiIorio 01-Sept-2004 Created.
283 -- SivakumarG 25-May-2006 Bug#5228919
284 -- Code added to insert lot attributes in mtlt, so that Lot transactions will show lot attributes
285 -- when we do Lot UOM conversion.
286 -- Archana Mundhe 27-Mar-2007 Bug 5533886
287 -- Added code to update batch transactions when update type is recalculate batch primary or secondary.
288 --=============================================================================================== */
289
290 PROCEDURE process_conversion_data
291 ( p_action_type IN VARCHAR2
292 , p_update_type_indicator IN NUMBER DEFAULT 5
293 , p_reason_id IN NUMBER
294 , p_batch_id IN NUMBER
295 , p_lot_uom_conv_rec IN OUT NOCOPY mtl_lot_uom_class_conversions%ROWTYPE
296 , p_qty_update_tbl IN OUT NOCOPY mtl_lot_uom_conv_pub.quantity_update_rec_type
297 , x_return_status OUT NOCOPY VARCHAR2
298 , x_msg_count OUT NOCOPY NUMBER
299 , x_msg_data OUT NOCOPY VARCHAR2
300 , x_sequence OUT NOCOPY NUMBER
301 )
302
303 IS
304
305 GENERIC_ERROR EXCEPTION;
306 INSERT_ERROR EXCEPTION;
307 OPEN_PERIOD_ERROR EXCEPTION;
308
309 -- Bug 5533886
310 -- Added below 3 exceptions.
311 BATCH_UPDATE_ERROR EXCEPTION;
312 BATCH_SAVE_ERROR EXCEPTION;
313 UM_CONVERT_ERROR EXCEPTION;
314
315 l_old_conversion_rate NUMBER;
316 l_update_type varchar2(20);
317
318
319 CURSOR get_old_conv_Rate IS
320 SELECT conversion_rate, conversion_id
321 FROM mtl_lot_uom_class_conversions
322 WHERE organization_id = p_lot_uom_conv_rec.organization_id AND
323 inventory_item_id = p_lot_uom_conv_rec.inventory_item_id AND
324 lot_number = p_lot_uom_conv_rec.lot_number AND
325 from_uom_code = p_lot_uom_conv_rec.from_uom_code AND
326 to_uom_code = p_lot_uom_conv_rec.to_uom_code;
327 l_conv_id NUMBER;
328 CURSOR GET_AUDIT_SEQ IS
329 SELECT MTL_CONV_AUDIT_ID_S.NEXTVAL
330 FROM FND_DUAL;
331
332 l_audit_seq NUMBER;
333
334
335 CURSOR GET_AUD_DET_SEQ
336 IS
337 SELECT MTL_CONV_AUDIT_DETAIL_ID_S.NEXTVAL
338 FROM FND_DUAL;
339
340 l_aud_det_seq NUMBER;
341
342
343 l_ind NUMBER;
344
345 CURSOR GET_TEMP_SEQ
346 IS
347 SELECT MTL_MATERIAL_TRANSACTIONS_S.NEXTVAL
348 FROM FND_DUAL;
349
350 l_temp_seq NUMBER;
351 l_head_seq NUMBER := NULL;
352 l_transaction_type_id NUMBER;
353 l_transaction_action_id NUMBER;
354 l_period_id NUMBER;
355 l_open_past_period BOOLEAN;
356 l_api_name CONSTANT VARCHAR2 (30) := 'process_conversion_data';
357 CONV_GET_ERR EXCEPTION;
358
359 CURSOR get_uom_codes IS
360 SELECT primary_uom_code, secondary_uom_code
361 FROM MTL_SYSTEM_ITEMS
362 WHERE
363 organization_id = p_lot_uom_conv_rec.organization_id AND
364 inventory_item_id = p_lot_uom_conv_rec.inventory_item_id;
365
366 l_primary_uom MTL_SYSTEM_ITEMS.PRIMARY_UOM_CODE%TYPE;
367 l_secondary_uom MTL_SYSTEM_ITEMS.SECONDARY_UOM_CODE%TYPE;
368
369 /* Bug#5228919 added the following cursor to get the lot attributes
370 from mtl_lot_numbers table */
371 CURSOR c_get_attr IS
372 SELECT mln.*
373 FROM mtl_lot_numbers mln
374 WHERE organization_id = p_lot_uom_conv_rec.organization_id
375 AND inventory_item_id = p_lot_uom_conv_rec.inventory_item_id
376 AND lot_number = p_lot_uom_conv_rec.lot_number;
377
378 l_lot_rec mtl_lot_numbers%ROWTYPE;
379
380 -- Bug 5533886
381 Cursor get_batch_transactions IS
382 SELECT *
383 FROM mtl_material_transactions mmt
384 WHERE transaction_source_id = p_batch_id
385 AND transaction_source_type_id = 5 -- gme_common_pvt.g_txn_source_type
386 AND NOT EXISTS ( SELECT transaction_id1
387 FROM gme_transaction_pairs
388 WHERE transaction_id1 = mmt.transaction_id
389 AND pair_type = 1) --gme_common_pvt.g_pairs_reversal_type
390 AND inventory_item_id = p_lot_uom_conv_rec.inventory_item_id
391 AND organization_id = p_lot_uom_conv_rec.organization_id
392 AND EXISTS (select 1
393 From mtl_transaction_lot_numbers
394 Where transaction_id = mmt.transaction_id
395 And lot_number = p_lot_uom_conv_rec.lot_number);
396
397
398 Cursor get_lot_transactions (v_transaction_id NUMBER) IS
399 SELECT *
400 FROM mtl_transaction_lot_numbers
401 WHERE transaction_id = v_transaction_id;
402 --AND lot_number = p_lot_uom_conv_rec.lot_number; Bug#9744632
403
404 x_batch_txns mtl_material_transactions%ROWTYPE;
405 x_lot_txns mtl_transaction_lot_numbers%ROWTYPE;
406 l_lot_transactions_tbl GME_COMMON_PVT.mtl_trans_lots_num_tbl;
407 i NUMBER;
408 j NUMBER;
409 txn_ind NUMBER;
410 new_ind NUMBER;
411 p_found NUMBER;
412 x_calc_qty NUMBER;
413 x_calc_qty2 NUMBER;
414 x_trans_qty NUMBER;
415 x_trans_qty2 NUMBER;
416 l_return_status VARCHAR2(10);
417 l_primary_quantity NUMBER;
418 l_batch_updated NUMBER := 0;
419 l_secondary_quantity NUMBER;
420 l_old_primary_quantity NUMBER;
421 l_old_secondary_quantity NUMBER;
422 l_batch_txn_qty NUMBER;
423 l_transaction_uom_class MTL_UNITS_OF_MEASURE.UOM_CLASS%TYPE;
424 l_catch_weight_acc_id Number ; --MUOM Fulfillment Project
425
426 BEGIN
427
428 x_return_status := FND_API.G_RET_STS_SUCCESS;
429
430
431 SAVEPOINT PROCESS_CONVERSION_DATA;
432
433 /*==================================================
434 Insert or update mtl_lot_uom_class_conversions.
435 ==================================================*/
436
437 IF (p_action_type = 'I') THEN
438 l_old_conversion_rate := NULL;
439 mtl_lot_uom_conv_pkg.insert_row(
440 p_lot_uom_conv_rec.conversion_id,
441 p_lot_uom_conv_rec.lot_number,
442 p_lot_uom_conv_rec.organization_id,
443 p_lot_uom_conv_rec.inventory_item_id,
444 p_lot_uom_conv_rec.from_unit_of_measure,
445 p_lot_uom_conv_rec.from_uom_code,
446 p_lot_uom_conv_rec.from_uom_class,
447 p_lot_uom_conv_rec.to_unit_of_measure,
448 p_lot_uom_conv_rec.to_uom_code,
449 p_lot_uom_conv_rec.to_uom_class,
450 p_lot_uom_conv_rec.conversion_rate,
451 p_lot_uom_conv_rec.disable_date,
452 p_lot_uom_conv_rec.event_spec_disp_id,
453 p_lot_uom_conv_rec.created_by,
454 p_lot_uom_conv_rec.creation_date,
455 p_lot_uom_conv_rec.last_updated_by,
456 p_lot_uom_conv_rec.last_update_date,
457 p_lot_uom_conv_rec.last_update_login,
458 p_lot_uom_conv_rec.request_id,
459 p_lot_uom_conv_rec.program_application_id,
460 p_lot_uom_conv_rec.program_id,
461 p_lot_uom_conv_rec.program_update_date,
462 x_return_status,
463 x_msg_count,
464 x_msg_data
465 );
466 ELSE
467
468 /*===============================================
469 Get existing conversion rate before updating.
470 ===============================================*/
471
472 OPEN get_old_conv_rate;
473 FETCH get_old_conv_rate INTO l_old_conversion_rate, l_conv_id;
474 IF (get_old_conv_rate%NOTFOUND) THEN
475 CLOSE get_old_conv_rate;
476 RAISE CONV_GET_ERR;
477 END IF;
478 CLOSE get_old_conv_rate;
479
480 IF (p_lot_uom_conv_rec.conversion_id IS NULL) THEN
481 p_lot_uom_conv_rec.conversion_id := l_conv_id;
482 END IF;
483
484 mtl_lot_uom_conv_pkg.update_row(
485 p_lot_uom_conv_rec.conversion_id,
486 p_lot_uom_conv_rec.lot_number,
487 p_lot_uom_conv_rec.organization_id,
488 p_lot_uom_conv_rec.inventory_item_id,
489 p_lot_uom_conv_rec.from_unit_of_measure,
490 p_lot_uom_conv_rec.from_uom_code,
491 p_lot_uom_conv_rec.from_uom_class,
492 p_lot_uom_conv_rec.to_unit_of_measure,
493 p_lot_uom_conv_rec.to_uom_code,
494 p_lot_uom_conv_rec.to_uom_class,
495 p_lot_uom_conv_rec.conversion_rate,
496 p_lot_uom_conv_rec.disable_date,
497 p_lot_uom_conv_rec.event_spec_disp_id,
498 p_lot_uom_conv_rec.last_updated_by,
499 p_lot_uom_conv_rec.last_update_date,
500 p_lot_uom_conv_rec.last_update_login,
501 p_lot_uom_conv_rec.request_id,
502 p_lot_uom_conv_rec.program_application_id,
503 p_lot_uom_conv_rec.program_id,
504 p_lot_uom_conv_rec.program_update_date,
505 x_return_status,
506 x_msg_count,
507 x_msg_data
508 );
509 END IF;
510
511 IF (x_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
512 RAISE INSERT_ERROR;
513 END IF;
514
515
516 -- Bug 5533886
517 -- Recalculate batch primary/secondary logic.
518 OPEN get_uom_codes;
519 FETCH get_uom_codes INTO l_primary_uom, l_secondary_uom;
520 CLOSE get_uom_codes;
521
522 gme_common_pvt.g_move_to_temp := fnd_api.g_false;
523
524 gme_common_pvt.g_setup_done := gme_common_pvt.setup (p_lot_uom_conv_rec.organization_id);
525 gme_common_pvt.set_who;
526
527
528 -- Bug 5533886
529 IF (p_update_type_indicator IN ('1','2')) THEN
530 OPEN get_batch_transactions;
531 LOOP
532
533 FETCH get_batch_transactions INTO x_batch_txns;
534 EXIT WHEN GET_BATCH_TRANSACTIONS%NOTFOUND;
535 txn_ind := 1;
536
537
538 OPEN get_lot_transactions(x_batch_txns.transaction_id);
539 FETCH get_lot_transactions BULK COLLECT INTO l_lot_transactions_tbl;
540 CLOSE get_lot_transactions;
541
542 SELECT uom_class
543 INTO l_transaction_uom_class
544 FROM mtl_units_of_measure_vl
545 WHERE uom_code = x_batch_txns.transaction_uom;
546
547 i:=1;
548 l_batch_txn_qty := 0;
549 FOR i in 1..l_lot_transactions_tbl.count
550 LOOP
551 --Bug#9744632 Added the below IF condition
552 IF l_lot_transactions_tbl(i).lot_number = p_lot_uom_conv_rec.lot_number THEN
553
554 IF (p_update_type_indicator = 1) THEN
555 l_old_primary_quantity := l_lot_transactions_tbl(i).primary_quantity;
556
557 IF ( l_transaction_uom_class = p_lot_uom_conv_rec.from_uom_class) THEN
558 x_trans_qty := inv_convert.inv_um_convert(
559 item_id => p_lot_uom_conv_rec.inventory_item_id,
560 lot_number => p_lot_uom_conv_rec.lot_number,
561 organization_id => p_lot_uom_conv_rec.organization_id,
562 precision => 5,
563 from_quantity => l_lot_transactions_tbl(i).secondary_transaction_quantity,
564 from_unit => x_batch_txns.secondary_uom_code,
565 to_unit => x_batch_txns.transaction_uom,
566 from_name => NULL,
567 to_name => NULL
568 );
569
570 IF x_trans_qty = -99999 THEN
571 RAISE UM_CONVERT_ERROR;
572 END IF;
573 l_lot_transactions_tbl(i).transaction_quantity := x_trans_qty;
574
575 END IF;
576 x_calc_qty := inv_convert.inv_um_convert(
577 item_id => p_lot_uom_conv_rec.inventory_item_id,
578 lot_number => p_lot_uom_conv_rec.lot_number,
579 organization_id => p_lot_uom_conv_rec.organization_id,
580 precision => 5,
581 from_quantity => l_lot_transactions_tbl(i).secondary_transaction_quantity,
582 from_unit => x_batch_txns.secondary_uom_code,
583 to_unit => l_primary_uom,
584 from_name => NULL,
585 to_name => NULL
586 );
587 IF x_calc_qty = -99999 THEN
588 RAISE UM_CONVERT_ERROR;
589 END IF;
590 l_lot_transactions_tbl(i).primary_quantity := x_calc_qty;
591 p_found := 0;
592
593 FOR j in 1..p_qty_update_tbl.count
594 LOOP
595 IF (p_qty_update_tbl(j).subinventory_code = x_batch_txns.subinventory_code AND
596 nvl(p_qty_update_tbl(j).locator_id,-1) = nvl(x_batch_txns.locator_id,nvl(p_qty_update_tbl(j).locator_id,-1)) ) THEN
597
598 -- Bug 6317236
599 -- Commenting this code as the logic is now moved to the form.
600
601 /* p_qty_update_tbl(j).transaction_primary_qty := p_qty_update_tbl(j).transaction_primary_qty -
602 (l_lot_transactions_tbl(i).primary_quantity - l_old_primary_quantity) ;
603 */
604 p_found := 1;
605 END IF;
606 END LOOP;
607
608 IF p_found = 0 THEN
609 new_ind := p_qty_update_tbl.count + 1;
610 p_qty_update_tbl(new_ind).organization_id := p_lot_uom_conv_rec.organization_id;
611 p_qty_update_tbl(new_ind).subinventory_code := x_batch_txns.subinventory_code;
612 p_qty_update_tbl(new_ind).locator_id := nvl(x_batch_txns.locator_id,p_qty_update_tbl(j).locator_id);
613 p_qty_update_tbl(new_ind).old_primary_qty := 0;
614 p_qty_update_tbl(new_ind).old_secondary_qty := 0;
615 p_qty_update_tbl(new_ind).new_primary_qty := -1 * l_lot_transactions_tbl(i).primary_quantity ;
616 p_qty_update_tbl(new_ind).transaction_primary_qty := -1 *
617 l_lot_transactions_tbl(i).primary_quantity;
618 p_qty_update_tbl(new_ind).transaction_update_flag := 1;
619
620 END IF;
621
622 ELSIF (p_update_type_indicator = 2) THEN
623 l_old_secondary_quantity := l_lot_transactions_tbl(i).secondary_transaction_quantity;
624 IF (l_transaction_uom_class = p_lot_uom_conv_rec.to_uom_class) THEN
625 x_trans_qty2 := inv_convert.inv_um_convert(
626 item_id => p_lot_uom_conv_rec.inventory_item_id,
627 lot_number => p_lot_uom_conv_rec.lot_number,
628 organization_id => p_lot_uom_conv_rec.organization_id,
629 precision => 5,
630 from_quantity => l_lot_transactions_tbl(i).primary_quantity,
631 from_unit => l_primary_uom,
632 to_unit => x_batch_txns.transaction_uom,
633 from_name => NULL,
634 to_name => NULL
635 );
636
637 IF x_trans_qty2 = -99999 THEN
638 RAISE UM_CONVERT_ERROR;
639 END IF;
640 l_lot_transactions_tbl(i).transaction_quantity := x_trans_qty2;
641 END IF; -- transaction_uom = primary_uom
642 x_calc_qty2 := inv_convert.inv_um_convert(
643 item_id => p_lot_uom_conv_rec.inventory_item_id,
644 lot_number => p_lot_uom_conv_rec.lot_number,
645 organization_id => p_lot_uom_conv_rec.organization_id,
646 precision => 5,
647 from_quantity => l_lot_transactions_tbl(i).primary_quantity,
648 from_unit => l_primary_uom,
649 to_unit => x_batch_txns.secondary_uom_code,
650 from_name => NULL,
651 to_name => NULL
652 );
653 IF x_calc_qty2 = -99999 THEN
654 RAISE UM_CONVERT_ERROR;
655 END IF;
656 l_lot_transactions_tbl(i).secondary_transaction_quantity := x_calc_qty2;
657 p_found := 0;
658 FOR j in 1..p_qty_update_tbl.count
659 LOOP
660 IF (p_qty_update_tbl(j).subinventory_code = x_batch_txns.subinventory_code AND
661 nvl(p_qty_update_tbl(j).locator_id,-1) = nvl(x_batch_txns.locator_id,nvl(p_qty_update_tbl(j).locator_id,-1)) ) THEN
662
663 -- Bug 6317236
664 -- Commenting this code as the logic is now moved to the form.
665
666 /* p_qty_update_tbl(j).transaction_secondary_qty := p_qty_update_tbl(j).transaction_secondary_qty -
667 (l_lot_transactions_tbl(i).secondary_transaction_quantity - l_old_secondary_quantity) ;
668 */
669 p_found := 1;
670 END IF;
671 END LOOP;
672 IF p_found = 0 THEN
673 new_ind := p_qty_update_tbl.count + 1;
674 p_qty_update_tbl(new_ind).organization_id := p_lot_uom_conv_rec.organization_id;
675 p_qty_update_tbl(new_ind).subinventory_code := x_batch_txns.subinventory_code;
676 p_qty_update_tbl(new_ind).locator_id := x_batch_txns.locator_id;
677 p_qty_update_tbl(new_ind).old_primary_qty := 0;
678 p_qty_update_tbl(new_ind).old_secondary_qty := 0;
679 p_qty_update_tbl(new_ind).new_secondary_qty := -1 *
680 l_lot_transactions_tbl(i).secondary_transaction_quantity ;
681 p_qty_update_tbl(new_ind).transaction_secondary_qty := -1 *
682 l_lot_transactions_tbl(i).secondary_transaction_quantity; p_qty_update_tbl(new_ind).transaction_update_flag := 1;
683 END IF;
684 END IF;
685 END IF;
686 l_batch_txn_qty := l_batch_txn_qty + l_lot_transactions_tbl(i).transaction_quantity;
687
688 END LOOP; -- l_lot_transactions_tbl
689
690 x_batch_txns.transaction_quantity := l_batch_txn_qty;
691 gme_transactions_pvt.update_material_txn
692 (p_mmt_rec => x_batch_txns
693 ,p_mmln_tbl => l_lot_transactions_tbl
694 ,x_return_status => l_return_status);
695
696 IF l_return_status <> fnd_api.g_ret_sts_success THEN
697 RAISE BATCH_UPDATE_ERROR;
698 END IF;
699
700 l_batch_updated := 1;
701 END LOOP; -- Batch transactions
702 CLOSE get_batch_transactions;
703
704 END IF; -- p_update_type_indicator in 1,2
705
706 /*==================================================
707 Insert Audit Record.
708 tempy check on conversion date.
709 tempy - event spec disp id.
710 ==================================================*/
711
712 OPEN GET_AUDIT_SEQ;
713 FETCH GET_AUDIT_SEQ INTO l_audit_seq;
714 CLOSE GET_AUDIT_SEQ;
715
716 p_lot_uom_conv_rec.created_by := p_lot_uom_conv_rec.last_updated_by;
717 p_lot_uom_conv_rec.creation_date := p_lot_uom_conv_rec.last_update_date;
718
719 mtl_lot_conv_audit_pkg.insert_row (
720 l_audit_seq,
721 p_lot_uom_conv_rec.conversion_id,
722 SYSDATE,
723 p_update_type_indicator,
724 p_batch_id,
725 p_reason_id,
726 l_old_conversion_rate,
727 p_lot_uom_conv_rec.conversion_rate,
728 p_lot_uom_conv_rec.event_spec_disp_id,
729 p_lot_uom_conv_rec.created_by,
730 p_lot_uom_conv_rec.creation_date,
731 p_lot_uom_conv_rec.last_updated_by,
732 p_lot_uom_conv_rec.last_update_date,
733 p_lot_uom_conv_rec.last_update_login,
734 x_return_status,
735 x_msg_count,
736 x_msg_data);
737
738
739 IF (x_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
740 RAISE INSERT_ERROR;
741 END IF;
742
743 -- SCHANDRU INVERES START
744 IF(g_eres_enabled = 'Y') THEN
745 IF (p_action_type = 'I') then
746 l_update_type := 'LOT-CONV-INSERT';
747 ELSE
748 L_UPDATE_TYPE := 'LOT-CONV-UPDATE';
749 END IF;
750 Insert into MTL_UOM_CONVERSIONS_ERES_GTMP (
751 CONVERSION_TYPE,
752 INVENTORY_ITEM_ID,
753 FROM_UOM_CODE,
754 FROM_UOM_CLASS,
755 TO_UOM_CODE,
756 TO_UOM_CLASS,
757 LOT_NUMBER,
758 CONVERSION_ID,
759 CONV_AUDIT_ID)
760 VALUES
761 (L_UPDATE_TYPE,
762 p_lot_uom_conv_rec.inventory_item_id,
763 p_lot_uom_conv_rec.from_uom_code,
764 p_lot_uom_conv_rec.from_uom_class,
765 p_lot_uom_conv_rec.to_uom_code,
766 p_lot_uom_conv_rec.to_uom_class,
767 p_lot_uom_conv_rec.lot_number,
768 p_lot_uom_conv_rec.conversion_id,
769 l_audit_seq);
770 END IF;
771
772 --SCHANDRU INVERES END
773
774
775 /*======
776 =======================================
777 Loop through detail records.
778 Insert transaction if necessary and insert
779 audit detail record.
780 =============================================*/
781
782
783
784 l_ind := 1;
785 LOOP
786
787 IF (p_qty_update_tbl.EXISTS(l_ind)) THEN
788
789 IF (p_qty_update_tbl(l_ind).transaction_update_flag = '1') THEN
790 IF (p_qty_update_tbl(l_ind).transaction_primary_qty = 0 AND
791 NVL(p_qty_update_tbl(l_ind).transaction_secondary_qty,0) = 0) THEN
792 GOTO BYPASS;
793 END IF;
794
795 IF (l_head_seq IS NULL) THEN
796 OPEN GET_TEMP_SEQ;
797 FETCH GET_TEMP_SEQ into l_head_seq;
798 IF (GET_TEMP_SEQ%NOTFOUND) THEN
799 CLOSE GET_TEMP_SEQ;
800 RAISE GENERIC_ERROR;
801 END IF;
802 CLOSE GET_TEMP_SEQ;
803 x_sequence := l_head_seq;
804 END IF;
805
806 /*===========================================
807 Check for open period and get period id.
808 ===========================================*/
809
810 INVTTMTX.TDATECHK(
811 org_id => p_lot_uom_conv_rec.organization_id,
812 transaction_date => SYSDATE,
813 period_id => l_period_id,
814 open_past_period => l_open_past_period);
815
816 IF (l_period_id = 0) THEN
817 RAISE OPEN_PERIOD_ERROR;
818
819 ELSIF (l_period_id = -1) THEN
820 RAISE GENERIC_ERROR;
821 END IF;
822
823 /*===========================
824 Set Tranaction Type ID
825 ===========================*/
826
827 IF (p_qty_update_tbl(l_ind).transaction_primary_qty = 0) THEN
828 IF (p_qty_update_tbl(l_ind).transaction_secondary_qty >= 0) THEN
829 /*====================
830 BUG#4320911
831 ====================*/
832 l_transaction_type_id := 1004;
833 l_transaction_action_id := 27;
834 ELSE
835 l_transaction_type_id := 97;
836 l_transaction_action_id := 1;
837 END IF;
838 ELSIF (p_qty_update_tbl(l_ind).transaction_primary_qty >= 0) THEN
839 l_transaction_type_id := 1004;
840 l_transaction_action_id := 27;
841 ELSE -- negative
842 l_transaction_type_id := 97;
843 l_transaction_action_id := 1;
844 END IF;
845
846 /*===========================================
847 Get primary and secondary uom code.
848 ===========================================*/
849
850 OPEN get_uom_codes;
851 FETCH get_uom_codes INTO l_primary_uom, l_secondary_uom;
852 CLOSE get_uom_codes;
853
854 -- tempy add error handling.
855
856
857
858 OPEN GET_TEMP_SEQ;
859 FETCH GET_TEMP_SEQ into l_temp_seq;
860 IF (GET_TEMP_SEQ%NOTFOUND) THEN
861 CLOSE GET_TEMP_SEQ;
862 RAISE GENERIC_ERROR;
863 END IF;
864 CLOSE GET_TEMP_SEQ;
865 -- MUOM Fulfillment Project
866 Begin
867 Select Cat_Wt_Account Into L_Catch_Weight_Acc_Id
868 From Mtl_Parameters
869 Where Organization_Id=P_Lot_Uom_Conv_Rec.Organization_Id;
870 EXCEPTION
871 When No_Data_Found Then
872 L_Catch_Weight_Acc_Id:=NULL;
873 End;
874
875
876 /*=============================
877 Insert to Temp Table.
878 =============================*/
879
880 INSERT INTO MTL_MATERIAL_TRANSACTIONS_TEMP (
881 transaction_header_id,
882 transaction_temp_id,
883 transaction_type_id,
884 transaction_action_id,
885 transaction_source_type_id,
886 acct_period_id,
887 organization_id,
888 inventory_item_id,
889 primary_quantity,
890 transaction_quantity,
891 transaction_uom,
892 secondary_transaction_quantity,
893 secondary_uom_code,
894 transaction_date,
895 process_flag,
896 lock_flag,
897 revision,
898 lot_number,
899 subinventory_code,
900 locator_id,
901 lpn_id,
902 reason_id, --Bug15927020
903 last_update_date,
904 last_updated_by,
905 created_by,
906 creation_date,
907 DISTRIBUTION_ACCOUNT_ID)--MUOM Fulfillment Project
908 VALUES (
909 l_head_seq,
910 l_temp_seq,
911 l_transaction_type_id,
912 l_transaction_action_id,
913 13,
914 l_period_id,
915 p_lot_uom_conv_rec.organization_id,
916 p_lot_uom_conv_rec.inventory_item_id,
917 p_qty_update_tbl(l_ind).transaction_primary_qty,
918 p_qty_update_tbl(l_ind).transaction_primary_qty,
919 l_primary_uom,
920 p_qty_update_tbl(l_ind).transaction_secondary_qty,
921 l_secondary_uom,
922 SYSDATE,
923 'Y',
924 2,
925 p_qty_update_tbl(l_ind).revision,
926 p_lot_uom_conv_rec.lot_number,
927 p_qty_update_tbl(l_ind).subinventory_code,
928 p_qty_update_tbl(l_ind).locator_id,
929 p_qty_update_tbl(l_ind).lpn_id,
930 p_reason_id, --Bug15927020
931 SYSDATE,
932 p_lot_uom_conv_rec.last_updated_by,
933 p_lot_uom_conv_rec.last_updated_by,
934 SYSDATE,
935 L_Catch_Weight_Acc_Id --MUOM Fulfillment Project
936 );
937
938
939 /*=============================
940 Insert to Lot Temp Table.
941 =============================*/
942 --Bug#5228919
943 OPEN c_get_attr;
944 FETCH c_get_attr INTO l_lot_rec;
945 CLOSE c_get_attr;
946
947 INSERT INTO MTL_TRANSACTION_LOTS_TEMP (
948 transaction_temp_id,
949 lot_number,
950 primary_quantity,
951 transaction_quantity,
952 secondary_quantity,
953 secondary_unit_of_measure,
954 last_update_date,
955 last_updated_by,
956 created_by,
957 creation_date,
958 --Bug#5228919 Begin
959 reason_id,
960 grade_code,
961 maturity_date,
962 origination_date,
963 retest_date,
964 supplier_lot_number,
965 attribute_category,
966 lot_attribute_category,
967 attribute1,
968 attribute2,
969 attribute3,
970 attribute4,
971 attribute5,
972 attribute6,
973 attribute7,
974 attribute8,
975 attribute9,
976 attribute10,
977 attribute11,
978 attribute12,
979 attribute13,
980 attribute14,
981 attribute15,
982 c_attribute1,
983 c_attribute2,
984 c_attribute3,
985 c_attribute4,
986 c_attribute5,
987 c_attribute6,
988 c_attribute7,
989 c_attribute8,
990 c_attribute9,
991 c_attribute10,
992 c_attribute11,
993 c_attribute12,
994 c_attribute13,
995 c_attribute14,
996 c_attribute15,
997 c_attribute16,
998 c_attribute17,
999 c_attribute18,
1000 c_attribute19,
1001 c_attribute20,
1002 d_attribute1,
1003 d_attribute2,
1004 d_attribute3,
1005 d_attribute4,
1006 d_attribute5,
1007 d_attribute6,
1008 d_attribute7,
1009 d_attribute8,
1010 d_attribute9,
1011 d_attribute10,
1012 n_attribute1,
1013 n_attribute2,
1014 n_attribute3,
1015 n_attribute4,
1016 n_attribute5,
1017 n_attribute6,
1018 n_attribute7,
1019 n_attribute8,
1020 n_attribute9,
1021 n_attribute10 ) --Bug#5228919 End
1022 VALUES (
1023 l_temp_seq,
1024 p_lot_uom_conv_rec.lot_number,
1025 ABS(p_qty_update_tbl(l_ind).transaction_primary_qty),
1026 ABS(p_qty_update_tbl(l_ind).transaction_primary_qty),
1027 ABS(p_qty_update_tbl(l_ind).transaction_secondary_qty),
1028 l_secondary_uom,
1029 SYSDATE,
1030 p_lot_uom_conv_rec.last_updated_by,
1031 p_lot_uom_conv_rec.last_updated_by,
1032 SYSDATE,
1033 --Bug#5228919 Begin
1034 p_reason_id,
1035 l_lot_rec.grade_code,
1036 l_lot_rec.maturity_date,
1037 l_lot_rec.origination_date,
1038 l_lot_rec.retest_date,
1039 l_lot_rec.supplier_lot_number,
1040 l_lot_rec.attribute_category,
1041 l_lot_rec.lot_attribute_category,
1042 l_lot_rec.attribute1,
1043 l_lot_rec.attribute2,
1044 l_lot_rec.attribute3,
1045 l_lot_rec.attribute4,
1046 l_lot_rec.attribute5,
1047 l_lot_rec.attribute6,
1048 l_lot_rec.attribute7,
1049 l_lot_rec.attribute8,
1050 l_lot_rec.attribute9,
1051 l_lot_rec.attribute10,
1052 l_lot_rec.attribute11,
1053 l_lot_rec.attribute12,
1054 l_lot_rec.attribute13,
1055 l_lot_rec.attribute14,
1056 l_lot_rec.attribute15,
1057 l_lot_rec.c_attribute1,
1058 l_lot_rec.c_attribute2,
1059 l_lot_rec.c_attribute3,
1060 l_lot_rec.c_attribute4,
1061 l_lot_rec.c_attribute5,
1062 l_lot_rec.c_attribute6,
1063 l_lot_rec.c_attribute7,
1064 l_lot_rec.c_attribute8,
1065 l_lot_rec.c_attribute9,
1066 l_lot_rec.c_attribute10,
1067 l_lot_rec.c_attribute11,
1068 l_lot_rec.c_attribute12,
1069 l_lot_rec.c_attribute13,
1070 l_lot_rec.c_attribute14,
1071 l_lot_rec.c_attribute15,
1072 l_lot_rec.c_attribute16,
1073 l_lot_rec.c_attribute17,
1074 l_lot_rec.c_attribute18,
1075 l_lot_rec.c_attribute19,
1076 l_lot_rec.c_attribute20,
1077 l_lot_rec.d_attribute1,
1078 l_lot_rec.d_attribute2,
1079 l_lot_rec.d_attribute3,
1080 l_lot_rec.d_attribute4,
1081 l_lot_rec.d_attribute5,
1082 l_lot_rec.d_attribute6,
1083 l_lot_rec.d_attribute7,
1084 l_lot_rec.d_attribute8,
1085 l_lot_rec.d_attribute9,
1086 l_lot_rec.d_attribute10,
1087 l_lot_rec.n_attribute1,
1088 l_lot_rec.n_attribute2,
1089 l_lot_rec.n_attribute3,
1090 l_lot_rec.n_attribute4,
1091 l_lot_rec.n_attribute5,
1092 l_lot_rec.n_attribute6,
1093 l_lot_rec.n_attribute7,
1094 l_lot_rec.n_attribute8,
1095 l_lot_rec.n_attribute9,
1096 l_lot_rec.n_attribute10
1097 --Bug#5228919 End
1098 );
1099
1100 END IF; -- endif for transaction needed
1101
1102
1103 /*======================================
1104 Insert a audit detail record whether
1105 there was a transaction or not.
1106 ======================================*/
1107
1108 <<BYPASS>>
1109 OPEN GET_AUD_DET_SEQ;
1110 FETCH GET_AUD_DET_SEQ INTO l_aud_det_seq;
1111 IF (GET_AUD_DET_SEQ%NOTFOUND) then
1112 CLOSE GET_AUD_DET_SEQ;
1113 RAISE GENERIC_ERROR;
1114 END IF;
1115 CLOSE GET_AUD_DET_SEQ;
1116
1117 MTL_LOT_CONV_AUD_DET_PKG.INSERT_ROW(
1118 X_CONV_AUDIT_DETAIL_ID => l_aud_det_seq,
1119 X_CONV_AUDIT_ID => l_audit_seq,
1120 X_REVISION => p_qty_update_tbl(l_ind).revision,
1121 X_ORGANIZATION_ID => p_qty_update_tbl(l_ind).organization_id,
1122 X_SUBINVENTORY_CODE => p_qty_update_tbl(l_ind).subinventory_code,
1123 X_LPN_ID => p_qty_update_tbl(l_ind).lpn_id,
1124 X_LOCATOR_ID => p_qty_update_tbl(l_ind).locator_id,
1125 X_OLD_PRIMARY_QTY => p_qty_update_tbl(l_ind).old_primary_qty,
1126 X_OLD_SECONDARY_QTY => p_qty_update_tbl(l_ind).old_secondary_qty,
1127 X_NEW_PRIMARY_QTY => p_qty_update_tbl(l_ind).new_primary_qty,
1128 X_NEW_SECONDARY_QTY => p_qty_update_tbl(l_ind).new_secondary_qty,
1129 X_TRANSACTION_PRIMARY_QTY => p_qty_update_tbl(l_ind).transaction_primary_qty,
1130 X_TRANSACTION_SECONDARY_QTY => p_qty_update_tbl(l_ind).transaction_secondary_qty,
1131 X_TRANSACTION_UPDATE_FLAG => p_qty_update_tbl(l_ind).transaction_update_flag,
1132 X_CREATED_BY => p_lot_uom_conv_rec.created_by,
1133 X_CREATION_DATE => p_lot_uom_conv_rec.creation_date,
1134 X_LAST_UPDATED_BY => p_lot_uom_conv_rec.last_updated_by,
1135 X_LAST_UPDATE_DATE => p_lot_uom_conv_rec.last_update_date,
1136 X_LAST_UPDATE_LOGIN => p_lot_uom_conv_rec.last_update_login,
1137 x_return_status => x_return_status,
1138 x_msg_count => x_msg_count,
1139 x_msg_data => x_msg_data);
1140 l_ind := l_ind + 1;
1141 ELSE
1142 EXIT;
1143 END IF; -- rec dont exist
1144
1145
1146 END LOOP;
1147
1148 -- Bug 5533886
1149 -- Call save batch api to save the updated material transactions.
1150 gme_api_pub.save_batch (
1151 X_return_status => l_return_status,
1152 p_header_id => gme_common_pvt.get_txn_header_id,
1153 p_table => 1,
1154 p_commit => 'F',
1155 p_clear_qty_cache => 'T');
1156
1157 IF (l_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
1158 RAISE BATCH_SAVE_ERROR;
1159 END IF;
1160
1161
1162 EXCEPTION
1163
1164 WHEN GENERIC_ERROR THEN
1165 ROLLBACK;
1166 FND_MSG_PUB.Add;
1167 x_return_status := FND_API.G_RET_STS_ERROR;
1168
1169 WHEN CONV_GET_ERR THEN
1170 ROLLBACK;
1171 FND_MESSAGE.SET_NAME('INV','INV_LOTC_SQL_ERROR');
1172 FND_MESSAGE.SET_TOKEN('ERRCODE',SQLCODE);
1173 FND_MESSAGE.SET_TOKEN('ERRM',SQLERRM(SQLCODE));
1174 FND_MSG_PUB.Add;
1175 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1176
1177
1178 WHEN INSERT_ERROR THEN
1179 ROLLBACK;
1180 FND_MESSAGE.SET_NAME('INV','INV_LOTC_SQL_ERROR');
1181 FND_MESSAGE.SET_TOKEN('ERRCODE',SQLCODE);
1182 FND_MESSAGE.SET_TOKEN('ERRM',SQLERRM(SQLCODE));
1183 FND_MSG_PUB.Add;
1184 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1185
1186
1187 WHEN OPEN_PERIOD_ERROR THEN
1188 ROLLBACK;
1189 FND_MESSAGE.SET_NAME('INV','INV_NO_OPEN_PERIOD');
1190 FND_MSG_PUB.Add;
1191 x_return_status := FND_API.G_RET_STS_ERROR;
1192
1193 -- Bug 5533886
1194 -- Added next 3 exceptions.
1195 WHEN UM_CONVERT_ERROR THEN
1196 FND_MESSAGE.SET_NAME('INV','INV_NO_CONVERSION_ERR');
1197 fnd_msg_pub.ADD;
1198 x_return_status := FND_API.G_RET_STS_ERROR;
1199
1200 WHEN BATCH_UPDATE_ERROR THEN
1201 x_return_status := l_return_status;
1202
1203 WHEN BATCH_SAVE_ERROR THEN
1204 x_return_status := l_return_status;
1205
1206 WHEN OTHERS THEN
1207
1208 IF (SQLCODE IS NOT NULL) THEN
1209 FND_MESSAGE.SET_NAME('INV','INV_LOTC_SQL_ERROR');
1210 FND_MESSAGE.SET_TOKEN('ERRCODE',SQLCODE);
1211 FND_MESSAGE.SET_TOKEN('ERRM',SQLERRM(SQLCODE));
1212 ELSE
1213 FND_MESSAGE.SET_NAME('INV','INV_BUSRULES_GENERIC_ERR');
1214 END IF;
1215 FND_MSG_PUB.Add;
1216 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1217
1218 ROLLBACK;
1219 IF (SQLCODE IS NOT NULL) THEN
1220 FND_MESSAGE.SET_NAME('INV','INV_LOTC_SQL_ERROR');
1221 FND_MESSAGE.SET_TOKEN('ERRCODE',SQLCODE);
1222 FND_MESSAGE.SET_TOKEN('ERRM',SQLERRM(SQLCODE));
1223 ELSE
1224 FND_MESSAGE.SET_NAME('INV','INV_BUSRULES_GENERIC_ERR');
1225 END IF;
1226 FND_MSG_PUB.Add;
1227 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1228
1229
1230 END process_conversion_data;
1231
1232
1233 /*===========================================================================
1234 -- PROCEDURE
1235 -- copy_lot_uom_conversions
1236 --
1237 -- DESCRIPTION:
1238 -- This PL/SQL procedure will copy lot uom conversions from one lot to
1239 -- another.
1240 --
1241 -- PARAMETERS:
1242 -- p_inventory_item_id IN NUMBER - Item id.
1243 -- p_from_organization_id IN NUMBER - Id of org to be copied from.
1244 -- p_from_lot_number IN VARCHAR2 - lot number to be copied from.
1245 -- p_to_organization_id IN NUMBER - Id of org to be copied to.
1246 -- p_to_lot_number IN VARCHAR2 - lot number to copy conversions to.
1247 -- p_user_id IN NUMBER - userid to use on the created records.
1248 -- p_creation_date IN DATE - create date to use on the created records.
1249 -- p_commit IN VARCHAR2 - Commit flag
1250 -- x_msg_count OUT NUMBER - Message count
1251 -- x_msg_data OUT VARCHAR2 - If an error, send back the approriate message.
1252 -- x_return_status OUT VARCHAR2 - 'S'uccess, 'E'rror, 'U'nexpected Error
1253 --
1254 -- SYNOPSIS:
1255 -- Copy lot uom conversions from one lot to another.
1256 --
1257 -- HISTORY
1258 -- Joe DiIorio 01-Sept-2004 Created.
1259 --
1260 --=========================================================================== */
1261
1262 PROCEDURE copy_lot_uom_conversions
1263 ( p_inventory_item_id IN NUMBER,
1264 p_from_organization_id IN NUMBER,
1265 p_from_lot_number IN VARCHAR2,
1266 p_to_organization_id IN NUMBER,
1267 p_to_lot_number IN VARCHAR2,
1268 p_user_id IN NUMBER,
1269 p_creation_date IN DATE,
1270 p_commit IN VARCHAR2,
1271 x_return_status OUT NOCOPY VARCHAR2,
1272 x_msg_count OUT NOCOPY NUMBER,
1273 x_msg_data OUT NOCOPY VARCHAR2
1274 )
1275
1276
1277 IS
1278
1279 NO_CONVERSIONS_FOUND EXCEPTION;
1280 CONVERSION_INSERT_ERROR EXCEPTION;
1281
1282 /*======================================
1283 Cursor to Retrieve Conversions to
1284 be Copied.
1285 ======================================*/
1286
1287 CURSOR c_get_conversions IS
1288 SELECT *
1289 FROM mtl_lot_uom_class_conversions
1290 WHERE nvl(disable_date, trunc(sysdate)+1) > trunc(sysdate)
1291 AND organization_id = p_from_organization_id
1292 AND lot_number = p_from_lot_number
1293 AND inventory_item_id = p_inventory_item_id;
1294
1295 l_lot_uom_conv_rec mtl_lot_uom_class_conversions%ROWTYPE;
1296
1297 /*======================================
1298 Cursor to Retrieve Reason_id.
1299 --tempy do not use until this is loaded
1300 -- do we need to load?
1301 -- and translation issue is worked out.
1302 ======================================*/
1303
1304 CURSOR c_get_reason_id IS
1305 SELECT reason_id
1306 FROM mtl_transaction_reasons
1307 WHERE reason_name = 'Copy Lot Conversions';
1308
1309 l_reason_id NUMBER;
1310 l_return_status VARCHAR2(240);
1311 l_error_message VARCHAR2(2000);
1312 l_msg_count NUMBER;
1313 l_qty_update_tbl mtl_lot_uom_conv_pub.quantity_update_rec_type;
1314
1315 /*=========================================
1316 Cursor to check if conversion exists.
1317 =========================================*/
1318
1319 CURSOR c_check_exists IS
1320 SELECT 1
1321 FROM mtl_lot_uom_class_conversions
1322 WHERE organization_id = p_to_organization_id
1323 AND lot_number = p_to_lot_number
1324 AND inventory_item_id = p_inventory_item_id
1325 AND from_uom_code = l_lot_uom_conv_rec.from_uom_code
1326 AND to_uom_code = l_lot_uom_conv_rec.to_uom_code;
1327
1328 l_exists_cnt NUMBER;
1329 l_seq NUMBER;
1330 l_creation_date DATE;
1331 l_user_id NUMBER;
1332
1333 BEGIN
1334
1335 IF (p_user_id IS NULL) THEN
1336 l_user_id := FND_GLOBAL.USER_ID;
1337 END IF;
1338 IF (p_creation_date IS NULL) THEN
1339 l_creation_date := SYSDATE;
1340 END IF;
1341
1342 x_return_status := FND_API.G_RET_STS_SUCCESS;
1343 OPEN c_get_conversions;
1344 FETCH c_get_conversions INTO l_lot_uom_conv_rec;
1345 IF (c_get_conversions%NOTFOUND) THEN
1346 CLOSE c_get_conversions;
1347 RETURN;
1348 ELSE
1349 SAVEPOINT COPY_CONVERSION;
1350 WHILE c_get_conversions%FOUND LOOP
1351 /*============================================
1352 Insert Reason Logic here. tempy
1353 ============================================*/
1354
1355 l_lot_uom_conv_rec.lot_number := p_to_lot_number;
1356 l_lot_uom_conv_rec.organization_id := p_to_organization_id;
1357 /*============================================
1358 If user id passed in use it for the who
1359 columns. Otherwise use existing who info.
1360 ============================================*/
1361 IF (p_user_id IS NOT NULL) THEN
1362 l_lot_uom_conv_rec.created_by := p_user_id;
1363 l_lot_uom_conv_rec.last_updated_by := p_user_id;
1364 ELSE
1365 l_lot_uom_conv_rec.created_by := l_user_id;
1366 l_lot_uom_conv_rec.last_updated_by := l_user_id;
1367 END IF;
1368 IF (p_creation_date IS NULL) THEN
1369 l_lot_uom_conv_rec.creation_date := l_creation_date;
1370 l_lot_uom_conv_rec.last_update_date := l_lot_uom_conv_rec.creation_date;
1371 ELSE
1372 l_lot_uom_conv_rec.creation_date := p_creation_date;
1373 l_lot_uom_conv_rec.last_update_date := p_creation_date;
1374 END IF;
1375
1376 /*==============================================
1377 Null out the conversion id.
1378 ==============================================*/
1379 l_lot_uom_conv_rec.conversion_id := NULL;
1380 /*==============================================
1381 Insert the new conversion and audit record.
1382 if conversion does not exist already.
1383 ==============================================*/
1384 l_exists_cnt := 0;
1385 OPEN c_check_exists;
1386 FETCH c_check_exists INTO l_exists_cnt;
1387 CLOSE c_check_exists;
1388 IF (l_exists_cnt = 0) THEN
1389 process_conversion_data(
1390 'I',5,l_reason_id,NULL,l_lot_uom_conv_rec,
1391 l_qty_update_tbl,l_return_status,l_msg_count,l_error_message,l_seq);
1392 IF (l_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
1393 CLOSE c_get_conversions;
1394 RAISE CONVERSION_INSERT_ERROR;
1395 END IF;
1396 END IF;
1397 FETCH c_get_conversions INTO l_lot_uom_conv_rec;
1398
1399 END LOOP;
1400
1401
1402 END IF; -- conversion cursor
1403
1404 CLOSE c_get_conversions;
1405
1406 /*=============================
1407 Issue commit if required.
1408 ============================*/
1409 IF (p_commit = FND_API.G_TRUE) THEN
1410 COMMIT;
1411 END IF;
1412
1413 x_return_status := FND_API.G_RET_STS_SUCCESS;
1414
1415 EXCEPTION
1416
1417 WHEN NO_CONVERSIONS_FOUND THEN
1418 FND_MESSAGE.SET_NAME('INV','INV_LOTC_NO_CONV_FOUND');
1419 FND_MSG_PUB.Add;
1420 x_return_status := FND_API.G_RET_STS_ERROR;
1421
1422 WHEN CONVERSION_INSERT_ERROR THEN
1423 ROLLBACK TO COPY_CONVERSION;
1424 FND_MESSAGE.SET_NAME('INV','INV_LOTC_CONV_INSERT_ERROR');
1425 FND_MSG_PUB.Add;
1426 IF (SQLCODE IS NOT NULL) THEN
1427 FND_MESSAGE.SET_NAME('INV','INV_LOTC_SQL_ERROR');
1428 FND_MESSAGE.SET_TOKEN('ERRCODE',SQLCODE);
1429 FND_MESSAGE.SET_TOKEN('ERRM',SQLERRM(SQLCODE));
1430 FND_MSG_PUB.Add;
1431 END IF;
1432 x_return_status := FND_API.G_RET_STS_ERROR;
1433
1434 WHEN OTHERS THEN
1435 ROLLBACK TO COPY_CONVERSION;
1436 IF (SQLCODE IS NOT NULL) THEN
1437 FND_MESSAGE.SET_NAME('INV','INV_LOTC_SQL_ERROR');
1438 FND_MESSAGE.SET_TOKEN('ERRCODE',SQLCODE);
1439 FND_MESSAGE.SET_TOKEN('ERRM',SQLERRM(SQLCODE));
1440 ELSE
1441 FND_MESSAGE.SET_NAME('INV','INV_BUSRULES_GENERIC_ERR');
1442 END IF;
1443 FND_MSG_PUB.Add;
1444 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1445
1446 END copy_lot_uom_conversions;
1447
1448
1449 /*===========================================================================
1450 -- FUNCTION:
1451 -- validate_onhand_equals_avail
1452 --
1453 -- DESCRIPTION:
1454 -- This function validates that no changes have been made to the quantity tree.
1455 -- Checks if any reservations exist.
1456 --
1457 -- PARAMETERS:
1458 -- p_organization_id IN NUMBER - organization id
1459 -- p_inventory_item_id IN NUMBER - item id
1460 -- p_lot_number IN VARCHAR2 - lot number
1461 -- p_header_id IN NUMBER - header id of current transaction
1462 -- return OUT NUMBER - G_TRUE or G_FALSE
1463 --
1464 -- SYNOPSIS:
1465 -- Check reservations.
1466 --
1467 -- HISTORY
1468 -- Joe DiIorio 01-Sept-2004 Created.
1469 --
1470 --=========================================================================== */
1471
1472
1473 FUNCTION validate_onhand_equals_avail (
1474 p_organization_id IN NUMBER,
1475 p_inventory_item_id IN NUMBER,
1476 p_lot_number IN VARCHAR2,
1477 p_header_id IN NUMBER)
1478 return NUMBER IS
1479
1480 /*============================================
1481 Cursor to check against interface table.
1482 ============================================*/
1483
1484 CURSOR c_check_mti IS
1485 SELECT 1
1486 FROM mtl_transactions_interface
1487 WHERE source_lot_number = p_lot_number
1488 AND inventory_item_id = p_inventory_item_id
1489 AND organization_id = p_organization_id
1490 AND transaction_type_id NOT IN (95,1004);
1491
1492 /*========================
1493 BUG#4320911
1494 Type from 96 to 1004.
1495 ========================*/
1496 CURSOR c_check_mti_head IS
1497 SELECT 1
1498 FROM mtl_transactions_interface
1499 WHERE source_lot_number = p_lot_number
1500 AND inventory_item_id = p_inventory_item_id
1501 AND organization_id = p_organization_id
1502 AND transaction_type_id NOT IN (95,1004)
1503 AND transaction_header_id <> p_header_id;
1504
1505
1506 /*============================================
1507 Cursor to check against temp transaction
1508 table.
1509 ============================================*/
1510
1511 CURSOR c_check_mmtt IS
1512 SELECT 1
1513 FROM mtl_material_transactions_temp
1514 WHERE lot_number = p_lot_number
1515 AND inventory_item_id = p_inventory_item_id
1516 AND organization_id = p_organization_id;
1517
1518 CURSOR c_check_mmtt_head IS
1519 SELECT 1
1520 FROM mtl_material_transactions_temp
1521 WHERE lot_number = p_lot_number
1522 AND inventory_item_id = p_inventory_item_id
1523 AND organization_id = p_organization_id
1524 AND transaction_header_id <> p_header_id
1525 AND process_flag <> 'E';
1526
1527 l_count NUMBER := 0;
1528
1529 EXISTING_RESERVATIONS EXCEPTION;
1530
1531
1532 BEGIN
1533
1534 /*============================================
1535 Cursor to check against interface table.
1536 ============================================*/
1537
1538 IF (p_header_id IS NULL) THEN
1539 OPEN c_check_mti;
1540 FETCH c_check_mti INTO l_count;
1541 CLOSE c_check_mti;
1542 IF (l_count > 0) THEN
1543 RAISE EXISTING_RESERVATIONS;
1544 END IF;
1545 ELSE
1546 OPEN c_check_mti_head;
1547 FETCH c_check_mti_head INTO l_count;
1548 CLOSE c_check_mti_head;
1549 IF (l_count > 0) THEN
1550 RAISE EXISTING_RESERVATIONS;
1551 END IF;
1552 END IF;
1553
1554 /*============================================
1555 Cursor to check against temp transaction
1556 table.
1557 ============================================*/
1558
1559 IF (p_header_id IS NULL) THEN
1560 OPEN c_check_mmtt;
1561 FETCH c_check_mmtt INTO l_count;
1562 CLOSE c_check_mmtt;
1563 IF (l_count > 0) THEN
1564 RAISE EXISTING_RESERVATIONS;
1565 END IF;
1566 ELSE
1567 OPEN c_check_mmtt_head;
1568 FETCH c_check_mmtt_head INTO l_count;
1569 CLOSE c_check_mmtt_head;
1570 IF (l_count > 0) THEN
1571 RAISE EXISTING_RESERVATIONS;
1572 END IF;
1573 END IF;
1574
1575
1576 RETURN G_TRUE;
1577
1578
1579 EXCEPTION
1580
1581 WHEN EXISTING_RESERVATIONS THEN
1582 FND_MESSAGE.SET_NAME('INV','INV_LOT_RESERVATIONS_EXIST');
1583 FND_MSG_PUB.Add;
1584 RETURN G_FALSE;
1585
1586
1587 WHEN OTHERS THEN
1588 RETURN G_FALSE;
1589
1590 END validate_onhand_equals_avail;
1591
1592
1593 END;