[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.2.12000000.4 2007/08/03 13:17:22 adeshmuk 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;
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
425 BEGIN
426
427 x_return_status := FND_API.G_RET_STS_SUCCESS;
428
429
430 SAVEPOINT PROCESS_CONVERSION_DATA;
431
432 /*==================================================
433 Insert or update mtl_lot_uom_class_conversions.
434 ==================================================*/
435
436 IF (p_action_type = 'I') THEN
437 l_old_conversion_rate := NULL;
438 mtl_lot_uom_conv_pkg.insert_row(
439 p_lot_uom_conv_rec.conversion_id,
440 p_lot_uom_conv_rec.lot_number,
441 p_lot_uom_conv_rec.organization_id,
442 p_lot_uom_conv_rec.inventory_item_id,
443 p_lot_uom_conv_rec.from_unit_of_measure,
444 p_lot_uom_conv_rec.from_uom_code,
445 p_lot_uom_conv_rec.from_uom_class,
446 p_lot_uom_conv_rec.to_unit_of_measure,
447 p_lot_uom_conv_rec.to_uom_code,
448 p_lot_uom_conv_rec.to_uom_class,
449 p_lot_uom_conv_rec.conversion_rate,
450 p_lot_uom_conv_rec.disable_date,
451 p_lot_uom_conv_rec.event_spec_disp_id,
452 p_lot_uom_conv_rec.created_by,
453 p_lot_uom_conv_rec.creation_date,
454 p_lot_uom_conv_rec.last_updated_by,
455 p_lot_uom_conv_rec.last_update_date,
456 p_lot_uom_conv_rec.last_update_login,
457 p_lot_uom_conv_rec.request_id,
458 p_lot_uom_conv_rec.program_application_id,
459 p_lot_uom_conv_rec.program_id,
460 p_lot_uom_conv_rec.program_update_date,
461 x_return_status,
462 x_msg_count,
463 x_msg_data
464 );
465 ELSE
466
467 /*===============================================
468 Get existing conversion rate before updating.
469 ===============================================*/
470
471 OPEN get_old_conv_rate;
472 FETCH get_old_conv_rate INTO l_old_conversion_rate, l_conv_id;
473 IF (get_old_conv_rate%NOTFOUND) THEN
474 CLOSE get_old_conv_rate;
475 RAISE CONV_GET_ERR;
476 END IF;
477 CLOSE get_old_conv_rate;
478
479 IF (p_lot_uom_conv_rec.conversion_id IS NULL) THEN
480 p_lot_uom_conv_rec.conversion_id := l_conv_id;
481 END IF;
482
483 mtl_lot_uom_conv_pkg.update_row(
484 p_lot_uom_conv_rec.conversion_id,
485 p_lot_uom_conv_rec.lot_number,
486 p_lot_uom_conv_rec.organization_id,
487 p_lot_uom_conv_rec.inventory_item_id,
488 p_lot_uom_conv_rec.from_unit_of_measure,
489 p_lot_uom_conv_rec.from_uom_code,
490 p_lot_uom_conv_rec.from_uom_class,
491 p_lot_uom_conv_rec.to_unit_of_measure,
492 p_lot_uom_conv_rec.to_uom_code,
493 p_lot_uom_conv_rec.to_uom_class,
494 p_lot_uom_conv_rec.conversion_rate,
495 p_lot_uom_conv_rec.disable_date,
496 p_lot_uom_conv_rec.event_spec_disp_id,
497 p_lot_uom_conv_rec.last_updated_by,
498 p_lot_uom_conv_rec.last_update_date,
499 p_lot_uom_conv_rec.last_update_login,
500 p_lot_uom_conv_rec.request_id,
501 p_lot_uom_conv_rec.program_application_id,
502 p_lot_uom_conv_rec.program_id,
503 p_lot_uom_conv_rec.program_update_date,
504 x_return_status,
505 x_msg_count,
506 x_msg_data
507 );
508 END IF;
509
510 IF (x_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
511 RAISE INSERT_ERROR;
512 END IF;
513
514
515 -- Bug 5533886
516 -- Recalculate batch primary/secondary logic.
517 OPEN get_uom_codes;
518 FETCH get_uom_codes INTO l_primary_uom, l_secondary_uom;
519 CLOSE get_uom_codes;
520
521 gme_common_pvt.g_move_to_temp := fnd_api.g_false;
522
523 gme_common_pvt.g_setup_done := gme_common_pvt.setup (p_lot_uom_conv_rec.organization_id);
524 gme_common_pvt.set_who;
525
526
527 -- Bug 5533886
528 IF (p_update_type_indicator IN ('1','2')) THEN
529 OPEN get_batch_transactions;
530 LOOP
531
532 FETCH get_batch_transactions INTO x_batch_txns;
533 EXIT WHEN GET_BATCH_TRANSACTIONS%NOTFOUND;
534 txn_ind := 1;
535
536
537 OPEN get_lot_transactions(x_batch_txns.transaction_id);
538 FETCH get_lot_transactions BULK COLLECT INTO l_lot_transactions_tbl;
539 CLOSE get_lot_transactions;
540
541 SELECT uom_class
542 INTO l_transaction_uom_class
543 FROM mtl_units_of_measure_vl
544 WHERE uom_code = x_batch_txns.transaction_uom;
545
546 i:=1;
547 l_batch_txn_qty := 0;
548 FOR i in 1..l_lot_transactions_tbl.count
549 LOOP
550 IF (p_update_type_indicator = 1) THEN
551 l_old_primary_quantity := l_lot_transactions_tbl(i).primary_quantity;
552
553 IF ( l_transaction_uom_class = p_lot_uom_conv_rec.from_uom_class) THEN
554 x_trans_qty := inv_convert.inv_um_convert(
555 item_id => p_lot_uom_conv_rec.inventory_item_id,
556 lot_number => p_lot_uom_conv_rec.lot_number,
557 organization_id => p_lot_uom_conv_rec.organization_id,
558 precision => 5,
559 from_quantity => l_lot_transactions_tbl(i).secondary_transaction_quantity,
560 from_unit => x_batch_txns.secondary_uom_code,
561 to_unit => x_batch_txns.transaction_uom,
562 from_name => NULL,
563 to_name => NULL
564 );
565
566 IF x_trans_qty = -99999 THEN
567 RAISE UM_CONVERT_ERROR;
568 END IF;
569 l_lot_transactions_tbl(i).transaction_quantity := x_trans_qty;
570
571 END IF;
572 x_calc_qty := inv_convert.inv_um_convert(
573 item_id => p_lot_uom_conv_rec.inventory_item_id,
574 lot_number => p_lot_uom_conv_rec.lot_number,
575 organization_id => p_lot_uom_conv_rec.organization_id,
576 precision => 5,
577 from_quantity => l_lot_transactions_tbl(i).secondary_transaction_quantity,
578 from_unit => x_batch_txns.secondary_uom_code,
579 to_unit => l_primary_uom,
580 from_name => NULL,
581 to_name => NULL
582 );
583 IF x_calc_qty = -99999 THEN
584 RAISE UM_CONVERT_ERROR;
585 END IF;
586 l_lot_transactions_tbl(i).primary_quantity := x_calc_qty;
587 p_found := 0;
588
589 FOR j in 1..p_qty_update_tbl.count
590 LOOP
591 IF (p_qty_update_tbl(j).subinventory_code = x_batch_txns.subinventory_code AND
592 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
593
594 -- Bug 6317236
595 -- Commenting this code as the logic is now moved to the form.
596
597 /* p_qty_update_tbl(j).transaction_primary_qty := p_qty_update_tbl(j).transaction_primary_qty -
598 (l_lot_transactions_tbl(i).primary_quantity - l_old_primary_quantity) ;
599 */
600 p_found := 1;
601 END IF;
602 END LOOP;
603
604 IF p_found = 0 THEN
605 new_ind := p_qty_update_tbl.count + 1;
606 p_qty_update_tbl(new_ind).organization_id := p_lot_uom_conv_rec.organization_id;
607 p_qty_update_tbl(new_ind).subinventory_code := x_batch_txns.subinventory_code;
608 p_qty_update_tbl(new_ind).locator_id := nvl(x_batch_txns.locator_id,p_qty_update_tbl(j).locator_id);
609 p_qty_update_tbl(new_ind).old_primary_qty := 0;
610 p_qty_update_tbl(new_ind).old_secondary_qty := 0;
611 p_qty_update_tbl(new_ind).new_primary_qty := -1 * l_lot_transactions_tbl(i).primary_quantity ;
612 p_qty_update_tbl(new_ind).transaction_primary_qty := -1 *
613 l_lot_transactions_tbl(i).primary_quantity;
614 p_qty_update_tbl(new_ind).transaction_update_flag := 1;
615
616 END IF;
617
618 ELSIF (p_update_type_indicator = 2) THEN
619 l_old_secondary_quantity := l_lot_transactions_tbl(i).secondary_transaction_quantity;
620 IF (l_transaction_uom_class = p_lot_uom_conv_rec.to_uom_class) THEN
621 x_trans_qty2 := inv_convert.inv_um_convert(
622 item_id => p_lot_uom_conv_rec.inventory_item_id,
623 lot_number => p_lot_uom_conv_rec.lot_number,
624 organization_id => p_lot_uom_conv_rec.organization_id,
625 precision => 5,
626 from_quantity => l_lot_transactions_tbl(i).primary_quantity,
627 from_unit => l_primary_uom,
628 to_unit => x_batch_txns.transaction_uom,
629 from_name => NULL,
630 to_name => NULL
631 );
632
633 IF x_trans_qty2 = -99999 THEN
634 RAISE UM_CONVERT_ERROR;
635 END IF;
636 l_lot_transactions_tbl(i).transaction_quantity := x_trans_qty2;
637 END IF; -- transaction_uom = primary_uom
638 x_calc_qty2 := inv_convert.inv_um_convert(
639 item_id => p_lot_uom_conv_rec.inventory_item_id,
640 lot_number => p_lot_uom_conv_rec.lot_number,
641 organization_id => p_lot_uom_conv_rec.organization_id,
642 precision => 5,
643 from_quantity => l_lot_transactions_tbl(i).primary_quantity,
644 from_unit => l_primary_uom,
645 to_unit => x_batch_txns.secondary_uom_code,
646 from_name => NULL,
647 to_name => NULL
648 );
649 IF x_calc_qty2 = -99999 THEN
650 RAISE UM_CONVERT_ERROR;
651 END IF;
652 l_lot_transactions_tbl(i).secondary_transaction_quantity := x_calc_qty2;
653 p_found := 0;
654 FOR j in 1..p_qty_update_tbl.count
655 LOOP
656 IF (p_qty_update_tbl(j).subinventory_code = x_batch_txns.subinventory_code AND
657 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
658
659 -- Bug 6317236
660 -- Commenting this code as the logic is now moved to the form.
661
662 /* p_qty_update_tbl(j).transaction_secondary_qty := p_qty_update_tbl(j).transaction_secondary_qty -
663 (l_lot_transactions_tbl(i).secondary_transaction_quantity - l_old_secondary_quantity) ;
664 */
665 p_found := 1;
666 END IF;
667 END LOOP;
668 IF p_found = 0 THEN
669 new_ind := p_qty_update_tbl.count + 1;
670 p_qty_update_tbl(new_ind).organization_id := p_lot_uom_conv_rec.organization_id;
671 p_qty_update_tbl(new_ind).subinventory_code := x_batch_txns.subinventory_code;
672 p_qty_update_tbl(new_ind).locator_id := x_batch_txns.locator_id;
673 p_qty_update_tbl(new_ind).old_primary_qty := 0;
674 p_qty_update_tbl(new_ind).old_secondary_qty := 0;
675 p_qty_update_tbl(new_ind).new_secondary_qty := -1 *
676 l_lot_transactions_tbl(i).secondary_transaction_quantity ;
677 p_qty_update_tbl(new_ind).transaction_secondary_qty := -1 *
678 l_lot_transactions_tbl(i).secondary_transaction_quantity; p_qty_update_tbl(new_ind).transaction_update_flag := 1;
679 END IF;
680 END IF;
681 l_batch_txn_qty := l_batch_txn_qty + l_lot_transactions_tbl(i).transaction_quantity;
682
683 END LOOP; -- l_lot_transactions_tbl
684
685 x_batch_txns.transaction_quantity := l_batch_txn_qty;
686 gme_transactions_pvt.update_material_txn
687 (p_mmt_rec => x_batch_txns
688 ,p_mmln_tbl => l_lot_transactions_tbl
689 ,x_return_status => l_return_status);
690
691 IF l_return_status <> fnd_api.g_ret_sts_success THEN
692 RAISE BATCH_UPDATE_ERROR;
693 END IF;
694
695 l_batch_updated := 1;
696 END LOOP; -- Batch transactions
697 CLOSE get_batch_transactions;
698
699 END IF; -- p_update_type_indicator in 1,2
700
701 /*==================================================
702 Insert Audit Record.
703 tempy check on conversion date.
704 tempy - event spec disp id.
705 ==================================================*/
706
707 OPEN GET_AUDIT_SEQ;
708 FETCH GET_AUDIT_SEQ INTO l_audit_seq;
709 CLOSE GET_AUDIT_SEQ;
710
711 p_lot_uom_conv_rec.created_by := p_lot_uom_conv_rec.last_updated_by;
712 p_lot_uom_conv_rec.creation_date := p_lot_uom_conv_rec.last_update_date;
713
714 mtl_lot_conv_audit_pkg.insert_row (
715 l_audit_seq,
716 p_lot_uom_conv_rec.conversion_id,
717 SYSDATE,
718 p_update_type_indicator,
719 p_batch_id,
720 p_reason_id,
721 l_old_conversion_rate,
722 p_lot_uom_conv_rec.conversion_rate,
723 p_lot_uom_conv_rec.event_spec_disp_id,
724 p_lot_uom_conv_rec.created_by,
725 p_lot_uom_conv_rec.creation_date,
726 p_lot_uom_conv_rec.last_updated_by,
727 p_lot_uom_conv_rec.last_update_date,
728 p_lot_uom_conv_rec.last_update_login,
729 x_return_status,
730 x_msg_count,
731 x_msg_data);
732
733
734 IF (x_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
735 RAISE INSERT_ERROR;
736 END IF;
737
738 -- SCHANDRU INVERES START
739 IF(g_eres_enabled = 'Y') THEN
740 IF (p_action_type = 'I') then
741 l_update_type := 'LOT-CONV-INSERT';
742 ELSE
743 L_UPDATE_TYPE := 'LOT-CONV-UPDATE';
744 END IF;
745 Insert into MTL_UOM_CONVERSIONS_ERES_GTMP (
746 CONVERSION_TYPE,
747 INVENTORY_ITEM_ID,
748 FROM_UOM_CODE,
749 FROM_UOM_CLASS,
750 TO_UOM_CODE,
751 TO_UOM_CLASS,
752 LOT_NUMBER,
753 CONVERSION_ID,
754 CONV_AUDIT_ID)
755 VALUES
756 (L_UPDATE_TYPE,
757 p_lot_uom_conv_rec.inventory_item_id,
758 p_lot_uom_conv_rec.from_uom_code,
759 p_lot_uom_conv_rec.from_uom_class,
760 p_lot_uom_conv_rec.to_uom_code,
761 p_lot_uom_conv_rec.to_uom_class,
762 p_lot_uom_conv_rec.lot_number,
763 p_lot_uom_conv_rec.conversion_id,
764 l_audit_seq);
765 END IF;
766
767 --SCHANDRU INVERES END
768
769
770 /*======
771 =======================================
772 Loop through detail records.
773 Insert transaction if necessary and insert
774 audit detail record.
775 =============================================*/
776
777
778
779 l_ind := 1;
780 LOOP
781
782 IF (p_qty_update_tbl.EXISTS(l_ind)) THEN
783
784 IF (p_qty_update_tbl(l_ind).transaction_update_flag = '1') THEN
785 IF (p_qty_update_tbl(l_ind).transaction_primary_qty = 0 AND
786 NVL(p_qty_update_tbl(l_ind).transaction_secondary_qty,0) = 0) THEN
787 GOTO BYPASS;
788 END IF;
789
790 IF (l_head_seq IS NULL) THEN
791 OPEN GET_TEMP_SEQ;
792 FETCH GET_TEMP_SEQ into l_head_seq;
793 IF (GET_TEMP_SEQ%NOTFOUND) THEN
794 CLOSE GET_TEMP_SEQ;
795 RAISE GENERIC_ERROR;
796 END IF;
797 CLOSE GET_TEMP_SEQ;
798 x_sequence := l_head_seq;
799 END IF;
800
801 /*===========================================
802 Check for open period and get period id.
803 ===========================================*/
804
805 INVTTMTX.TDATECHK(
806 org_id => p_lot_uom_conv_rec.organization_id,
807 transaction_date => SYSDATE,
808 period_id => l_period_id,
809 open_past_period => l_open_past_period);
810
811 IF (l_period_id = 0) THEN
812 RAISE OPEN_PERIOD_ERROR;
813
814 ELSIF (l_period_id = -1) THEN
815 RAISE GENERIC_ERROR;
816 END IF;
817
818 /*===========================
819 Set Tranaction Type ID
820 ===========================*/
821
822 IF (p_qty_update_tbl(l_ind).transaction_primary_qty = 0) THEN
823 IF (p_qty_update_tbl(l_ind).transaction_secondary_qty >= 0) THEN
824 /*====================
825 BUG#4320911
826 ====================*/
827 l_transaction_type_id := 1004;
828 l_transaction_action_id := 27;
829 ELSE
830 l_transaction_type_id := 97;
831 l_transaction_action_id := 1;
832 END IF;
833 ELSIF (p_qty_update_tbl(l_ind).transaction_primary_qty >= 0) THEN
834 l_transaction_type_id := 1004;
835 l_transaction_action_id := 27;
836 ELSE -- negative
837 l_transaction_type_id := 97;
838 l_transaction_action_id := 1;
839 END IF;
840
841 /*===========================================
842 Get primary and secondary uom code.
843 ===========================================*/
844
845 OPEN get_uom_codes;
846 FETCH get_uom_codes INTO l_primary_uom, l_secondary_uom;
847 CLOSE get_uom_codes;
848
849 -- tempy add error handling.
850
851
852
853 OPEN GET_TEMP_SEQ;
854 FETCH GET_TEMP_SEQ into l_temp_seq;
855 IF (GET_TEMP_SEQ%NOTFOUND) THEN
856 CLOSE GET_TEMP_SEQ;
857 RAISE GENERIC_ERROR;
858 END IF;
859 CLOSE GET_TEMP_SEQ;
860
861
862 /*=============================
863 Insert to Temp Table.
864 =============================*/
865
866 INSERT INTO MTL_MATERIAL_TRANSACTIONS_TEMP (
867 transaction_header_id,
868 transaction_temp_id,
869 transaction_type_id,
870 transaction_action_id,
871 transaction_source_type_id,
872 acct_period_id,
873 organization_id,
874 inventory_item_id,
875 primary_quantity,
876 transaction_quantity,
877 transaction_uom,
878 secondary_transaction_quantity,
879 secondary_uom_code,
880 transaction_date,
881 process_flag,
882 lock_flag,
883 revision,
884 lot_number,
885 subinventory_code,
886 locator_id,
887 lpn_id,
888 last_update_date,
889 last_updated_by,
890 created_by,
891 creation_date)
892 VALUES (
893 l_head_seq,
894 l_temp_seq,
895 l_transaction_type_id,
896 l_transaction_action_id,
897 13,
898 l_period_id,
899 p_lot_uom_conv_rec.organization_id,
900 p_lot_uom_conv_rec.inventory_item_id,
901 p_qty_update_tbl(l_ind).transaction_primary_qty,
902 p_qty_update_tbl(l_ind).transaction_primary_qty,
903 l_primary_uom,
904 p_qty_update_tbl(l_ind).transaction_secondary_qty,
905 l_secondary_uom,
906 SYSDATE,
907 'Y',
908 2,
909 p_qty_update_tbl(l_ind).revision,
910 p_lot_uom_conv_rec.lot_number,
911 p_qty_update_tbl(l_ind).subinventory_code,
912 p_qty_update_tbl(l_ind).locator_id,
913 p_qty_update_tbl(l_ind).lpn_id,
914 SYSDATE,
915 p_lot_uom_conv_rec.last_updated_by,
916 p_lot_uom_conv_rec.last_updated_by,
917 SYSDATE
918 );
919
920
921 /*=============================
922 Insert to Lot Temp Table.
923 =============================*/
924 --Bug#5228919
925 OPEN c_get_attr;
926 FETCH c_get_attr INTO l_lot_rec;
927 CLOSE c_get_attr;
928
929 INSERT INTO MTL_TRANSACTION_LOTS_TEMP (
930 transaction_temp_id,
931 lot_number,
932 primary_quantity,
933 transaction_quantity,
934 secondary_quantity,
935 secondary_unit_of_measure,
936 last_update_date,
937 last_updated_by,
938 created_by,
939 creation_date,
940 --Bug#5228919 Begin
941 reason_id,
942 grade_code,
943 maturity_date,
944 origination_date,
945 retest_date,
946 supplier_lot_number,
947 attribute_category,
948 lot_attribute_category,
949 attribute1,
950 attribute2,
951 attribute3,
952 attribute4,
953 attribute5,
954 attribute6,
955 attribute7,
956 attribute8,
957 attribute9,
958 attribute10,
959 attribute11,
960 attribute12,
961 attribute13,
962 attribute14,
963 attribute15,
964 c_attribute1,
965 c_attribute2,
966 c_attribute3,
967 c_attribute4,
968 c_attribute5,
969 c_attribute6,
970 c_attribute7,
971 c_attribute8,
972 c_attribute9,
973 c_attribute10,
974 c_attribute11,
975 c_attribute12,
976 c_attribute13,
977 c_attribute14,
978 c_attribute15,
979 c_attribute16,
980 c_attribute17,
981 c_attribute18,
982 c_attribute19,
983 c_attribute20,
984 d_attribute1,
985 d_attribute2,
986 d_attribute3,
987 d_attribute4,
988 d_attribute5,
989 d_attribute6,
990 d_attribute7,
991 d_attribute8,
992 d_attribute9,
993 d_attribute10,
994 n_attribute1,
995 n_attribute2,
996 n_attribute3,
997 n_attribute4,
998 n_attribute5,
999 n_attribute6,
1000 n_attribute7,
1001 n_attribute8,
1002 n_attribute9,
1003 n_attribute10 ) --Bug#5228919 End
1004 VALUES (
1005 l_temp_seq,
1006 p_lot_uom_conv_rec.lot_number,
1007 ABS(p_qty_update_tbl(l_ind).transaction_primary_qty),
1008 ABS(p_qty_update_tbl(l_ind).transaction_primary_qty),
1009 ABS(p_qty_update_tbl(l_ind).transaction_secondary_qty),
1010 l_secondary_uom,
1011 SYSDATE,
1012 p_lot_uom_conv_rec.last_updated_by,
1013 p_lot_uom_conv_rec.last_updated_by,
1014 SYSDATE,
1015 --Bug#5228919 Begin
1016 p_reason_id,
1017 l_lot_rec.grade_code,
1018 l_lot_rec.maturity_date,
1019 l_lot_rec.origination_date,
1020 l_lot_rec.retest_date,
1021 l_lot_rec.supplier_lot_number,
1022 l_lot_rec.attribute_category,
1023 l_lot_rec.lot_attribute_category,
1024 l_lot_rec.attribute1,
1025 l_lot_rec.attribute2,
1026 l_lot_rec.attribute3,
1027 l_lot_rec.attribute4,
1028 l_lot_rec.attribute5,
1029 l_lot_rec.attribute6,
1030 l_lot_rec.attribute7,
1031 l_lot_rec.attribute8,
1032 l_lot_rec.attribute9,
1033 l_lot_rec.attribute10,
1034 l_lot_rec.attribute11,
1035 l_lot_rec.attribute12,
1036 l_lot_rec.attribute13,
1037 l_lot_rec.attribute14,
1038 l_lot_rec.attribute15,
1039 l_lot_rec.c_attribute1,
1040 l_lot_rec.c_attribute2,
1041 l_lot_rec.c_attribute3,
1042 l_lot_rec.c_attribute4,
1043 l_lot_rec.c_attribute5,
1044 l_lot_rec.c_attribute6,
1045 l_lot_rec.c_attribute7,
1046 l_lot_rec.c_attribute8,
1047 l_lot_rec.c_attribute9,
1048 l_lot_rec.c_attribute10,
1049 l_lot_rec.c_attribute11,
1050 l_lot_rec.c_attribute12,
1051 l_lot_rec.c_attribute13,
1052 l_lot_rec.c_attribute14,
1053 l_lot_rec.c_attribute15,
1054 l_lot_rec.c_attribute16,
1055 l_lot_rec.c_attribute17,
1056 l_lot_rec.c_attribute18,
1057 l_lot_rec.c_attribute19,
1058 l_lot_rec.c_attribute20,
1059 l_lot_rec.d_attribute1,
1060 l_lot_rec.d_attribute2,
1061 l_lot_rec.d_attribute3,
1062 l_lot_rec.d_attribute4,
1063 l_lot_rec.d_attribute5,
1064 l_lot_rec.d_attribute6,
1065 l_lot_rec.d_attribute7,
1066 l_lot_rec.d_attribute8,
1067 l_lot_rec.d_attribute9,
1068 l_lot_rec.d_attribute10,
1069 l_lot_rec.n_attribute1,
1070 l_lot_rec.n_attribute2,
1071 l_lot_rec.n_attribute3,
1072 l_lot_rec.n_attribute4,
1073 l_lot_rec.n_attribute5,
1074 l_lot_rec.n_attribute6,
1075 l_lot_rec.n_attribute7,
1076 l_lot_rec.n_attribute8,
1077 l_lot_rec.n_attribute9,
1078 l_lot_rec.n_attribute10
1079 --Bug#5228919 End
1080 );
1081
1082 END IF; -- endif for transaction needed
1083
1084
1085 /*======================================
1086 Insert a audit detail record whether
1087 there was a transaction or not.
1088 ======================================*/
1089
1090 <<BYPASS>>
1091 OPEN GET_AUD_DET_SEQ;
1092 FETCH GET_AUD_DET_SEQ INTO l_aud_det_seq;
1093 IF (GET_AUD_DET_SEQ%NOTFOUND) then
1094 CLOSE GET_AUD_DET_SEQ;
1095 RAISE GENERIC_ERROR;
1096 END IF;
1097 CLOSE GET_AUD_DET_SEQ;
1098
1099 MTL_LOT_CONV_AUD_DET_PKG.INSERT_ROW(
1100 X_CONV_AUDIT_DETAIL_ID => l_aud_det_seq,
1101 X_CONV_AUDIT_ID => l_audit_seq,
1102 X_REVISION => p_qty_update_tbl(l_ind).revision,
1103 X_ORGANIZATION_ID => p_qty_update_tbl(l_ind).organization_id,
1104 X_SUBINVENTORY_CODE => p_qty_update_tbl(l_ind).subinventory_code,
1105 X_LPN_ID => p_qty_update_tbl(l_ind).lpn_id,
1106 X_LOCATOR_ID => p_qty_update_tbl(l_ind).locator_id,
1107 X_OLD_PRIMARY_QTY => p_qty_update_tbl(l_ind).old_primary_qty,
1108 X_OLD_SECONDARY_QTY => p_qty_update_tbl(l_ind).old_secondary_qty,
1109 X_NEW_PRIMARY_QTY => p_qty_update_tbl(l_ind).new_primary_qty,
1110 X_NEW_SECONDARY_QTY => p_qty_update_tbl(l_ind).new_secondary_qty,
1111 X_TRANSACTION_PRIMARY_QTY => p_qty_update_tbl(l_ind).transaction_primary_qty,
1112 X_TRANSACTION_SECONDARY_QTY => p_qty_update_tbl(l_ind).transaction_secondary_qty,
1113 X_TRANSACTION_UPDATE_FLAG => p_qty_update_tbl(l_ind).transaction_update_flag,
1114 X_CREATED_BY => p_lot_uom_conv_rec.created_by,
1115 X_CREATION_DATE => p_lot_uom_conv_rec.creation_date,
1116 X_LAST_UPDATED_BY => p_lot_uom_conv_rec.last_updated_by,
1117 X_LAST_UPDATE_DATE => p_lot_uom_conv_rec.last_update_date,
1118 X_LAST_UPDATE_LOGIN => p_lot_uom_conv_rec.last_update_login,
1119 x_return_status => x_return_status,
1120 x_msg_count => x_msg_count,
1121 x_msg_data => x_msg_data);
1122 l_ind := l_ind + 1;
1123 ELSE
1124 EXIT;
1125 END IF; -- rec dont exist
1126
1127
1128 END LOOP;
1129
1130 -- Bug 5533886
1131 -- Call save batch api to save the updated material transactions.
1132 gme_api_pub.save_batch (
1133 X_return_status => l_return_status,
1134 p_header_id => gme_common_pvt.get_txn_header_id,
1135 p_table => 1,
1136 p_commit => 'F',
1137 p_clear_qty_cache => 'T');
1138
1139 IF (l_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
1140 RAISE BATCH_SAVE_ERROR;
1141 END IF;
1142
1143
1144 EXCEPTION
1145
1146 WHEN GENERIC_ERROR THEN
1147 ROLLBACK;
1148 FND_MSG_PUB.Add;
1149 x_return_status := FND_API.G_RET_STS_ERROR;
1150
1151 WHEN CONV_GET_ERR THEN
1152 ROLLBACK;
1153 FND_MESSAGE.SET_NAME('INV','INV_LOTC_SQL_ERROR');
1154 FND_MESSAGE.SET_TOKEN('ERRCODE',SQLCODE);
1155 FND_MESSAGE.SET_TOKEN('ERRM',SQLERRM(SQLCODE));
1156 FND_MSG_PUB.Add;
1157 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1158
1159
1160 WHEN INSERT_ERROR THEN
1161 ROLLBACK;
1162 FND_MESSAGE.SET_NAME('INV','INV_LOTC_SQL_ERROR');
1163 FND_MESSAGE.SET_TOKEN('ERRCODE',SQLCODE);
1164 FND_MESSAGE.SET_TOKEN('ERRM',SQLERRM(SQLCODE));
1165 FND_MSG_PUB.Add;
1166 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1167
1168
1169 WHEN OPEN_PERIOD_ERROR THEN
1170 ROLLBACK;
1171 FND_MESSAGE.SET_NAME('INV','INV_NO_OPEN_PERIOD');
1172 FND_MSG_PUB.Add;
1173 x_return_status := FND_API.G_RET_STS_ERROR;
1174
1175 -- Bug 5533886
1176 -- Added next 3 exceptions.
1177 WHEN UM_CONVERT_ERROR THEN
1178 FND_MESSAGE.SET_NAME('INV','INV_NO_CONVERSION_ERR');
1179 fnd_msg_pub.ADD;
1180 x_return_status := FND_API.G_RET_STS_ERROR;
1181
1182 WHEN BATCH_UPDATE_ERROR THEN
1183 x_return_status := l_return_status;
1184
1185 WHEN BATCH_SAVE_ERROR THEN
1186 x_return_status := l_return_status;
1187
1188 WHEN OTHERS THEN
1189
1190 IF (SQLCODE IS NOT NULL) THEN
1191 FND_MESSAGE.SET_NAME('INV','INV_LOTC_SQL_ERROR');
1192 FND_MESSAGE.SET_TOKEN('ERRCODE',SQLCODE);
1193 FND_MESSAGE.SET_TOKEN('ERRM',SQLERRM(SQLCODE));
1194 ELSE
1195 FND_MESSAGE.SET_NAME('INV','INV_BUSRULES_GENERIC_ERR');
1196 END IF;
1197 FND_MSG_PUB.Add;
1198 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1199
1200 ROLLBACK;
1201 IF (SQLCODE IS NOT NULL) THEN
1202 FND_MESSAGE.SET_NAME('INV','INV_LOTC_SQL_ERROR');
1203 FND_MESSAGE.SET_TOKEN('ERRCODE',SQLCODE);
1204 FND_MESSAGE.SET_TOKEN('ERRM',SQLERRM(SQLCODE));
1205 ELSE
1206 FND_MESSAGE.SET_NAME('INV','INV_BUSRULES_GENERIC_ERR');
1207 END IF;
1208 FND_MSG_PUB.Add;
1209 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1210
1211
1212 END process_conversion_data;
1213
1214
1215 /*===========================================================================
1216 -- PROCEDURE
1217 -- copy_lot_uom_conversions
1218 --
1219 -- DESCRIPTION:
1220 -- This PL/SQL procedure will copy lot uom conversions from one lot to
1221 -- another.
1222 --
1223 -- PARAMETERS:
1224 -- p_inventory_item_id IN NUMBER - Item id.
1225 -- p_from_organization_id IN NUMBER - Id of org to be copied from.
1226 -- p_from_lot_number IN VARCHAR2 - lot number to be copied from.
1227 -- p_to_organization_id IN NUMBER - Id of org to be copied to.
1228 -- p_to_lot_number IN VARCHAR2 - lot number to copy conversions to.
1229 -- p_user_id IN NUMBER - userid to use on the created records.
1230 -- p_creation_date IN DATE - create date to use on the created records.
1231 -- p_commit IN VARCHAR2 - Commit flag
1232 -- x_msg_count OUT NUMBER - Message count
1233 -- x_msg_data OUT VARCHAR2 - If an error, send back the approriate message.
1234 -- x_return_status OUT VARCHAR2 - 'S'uccess, 'E'rror, 'U'nexpected Error
1235 --
1236 -- SYNOPSIS:
1237 -- Copy lot uom conversions from one lot to another.
1238 --
1239 -- HISTORY
1240 -- Joe DiIorio 01-Sept-2004 Created.
1241 --
1242 --=========================================================================== */
1243
1244 PROCEDURE copy_lot_uom_conversions
1245 ( p_inventory_item_id IN NUMBER,
1246 p_from_organization_id IN NUMBER,
1247 p_from_lot_number IN VARCHAR2,
1248 p_to_organization_id IN NUMBER,
1249 p_to_lot_number IN VARCHAR2,
1250 p_user_id IN NUMBER,
1251 p_creation_date IN DATE,
1252 p_commit IN VARCHAR2,
1253 x_return_status OUT NOCOPY VARCHAR2,
1254 x_msg_count OUT NOCOPY NUMBER,
1255 x_msg_data OUT NOCOPY VARCHAR2
1256 )
1257
1258
1259 IS
1260
1261 NO_CONVERSIONS_FOUND EXCEPTION;
1262 CONVERSION_INSERT_ERROR EXCEPTION;
1263
1264 /*======================================
1265 Cursor to Retrieve Conversions to
1266 be Copied.
1267 ======================================*/
1268
1269 CURSOR c_get_conversions IS
1270 SELECT *
1271 FROM mtl_lot_uom_class_conversions
1272 WHERE nvl(disable_date, trunc(sysdate)+1) > trunc(sysdate)
1273 AND organization_id = p_from_organization_id
1274 AND lot_number = p_from_lot_number
1275 AND inventory_item_id = p_inventory_item_id;
1276
1277 l_lot_uom_conv_rec mtl_lot_uom_class_conversions%ROWTYPE;
1278
1279 /*======================================
1280 Cursor to Retrieve Reason_id.
1281 --tempy do not use until this is loaded
1282 -- do we need to load?
1283 -- and translation issue is worked out.
1284 ======================================*/
1285
1286 CURSOR c_get_reason_id IS
1287 SELECT reason_id
1288 FROM mtl_transaction_reasons
1289 WHERE reason_name = 'Copy Lot Conversions';
1290
1291 l_reason_id NUMBER;
1292 l_return_status VARCHAR2(240);
1293 l_error_message VARCHAR2(2000);
1294 l_msg_count NUMBER;
1295 l_qty_update_tbl mtl_lot_uom_conv_pub.quantity_update_rec_type;
1296
1297 /*=========================================
1298 Cursor to check if conversion exists.
1299 =========================================*/
1300
1301 CURSOR c_check_exists IS
1302 SELECT 1
1303 FROM mtl_lot_uom_class_conversions
1304 WHERE organization_id = p_to_organization_id
1305 AND lot_number = p_to_lot_number
1306 AND inventory_item_id = p_inventory_item_id
1307 AND from_uom_code = l_lot_uom_conv_rec.from_uom_code
1308 AND to_uom_code = l_lot_uom_conv_rec.to_uom_code;
1309
1310 l_exists_cnt NUMBER;
1311 l_seq NUMBER;
1312 l_creation_date DATE;
1313 l_user_id NUMBER;
1314
1315 BEGIN
1316
1317 IF (p_user_id IS NULL) THEN
1318 l_user_id := FND_GLOBAL.USER_ID;
1319 END IF;
1320 IF (p_creation_date IS NULL) THEN
1321 l_creation_date := SYSDATE;
1322 END IF;
1323
1324 x_return_status := FND_API.G_RET_STS_SUCCESS;
1325 OPEN c_get_conversions;
1326 FETCH c_get_conversions INTO l_lot_uom_conv_rec;
1327 IF (c_get_conversions%NOTFOUND) THEN
1328 CLOSE c_get_conversions;
1329 RETURN;
1330 ELSE
1331 SAVEPOINT COPY_CONVERSION;
1332 WHILE c_get_conversions%FOUND LOOP
1333 /*============================================
1334 Insert Reason Logic here. tempy
1335 ============================================*/
1336
1337 l_lot_uom_conv_rec.lot_number := p_to_lot_number;
1338 l_lot_uom_conv_rec.organization_id := p_to_organization_id;
1339 /*============================================
1340 If user id passed in use it for the who
1341 columns. Otherwise use existing who info.
1342 ============================================*/
1343 IF (p_user_id IS NOT NULL) THEN
1344 l_lot_uom_conv_rec.created_by := p_user_id;
1345 l_lot_uom_conv_rec.last_updated_by := p_user_id;
1346 ELSE
1347 l_lot_uom_conv_rec.created_by := l_user_id;
1348 l_lot_uom_conv_rec.last_updated_by := l_user_id;
1349 END IF;
1350 IF (p_creation_date IS NULL) THEN
1351 l_lot_uom_conv_rec.creation_date := l_creation_date;
1352 l_lot_uom_conv_rec.last_update_date := l_lot_uom_conv_rec.creation_date;
1353 ELSE
1354 l_lot_uom_conv_rec.creation_date := p_creation_date;
1355 l_lot_uom_conv_rec.last_update_date := p_creation_date;
1356 END IF;
1357
1358 /*==============================================
1359 Null out the conversion id.
1360 ==============================================*/
1361 l_lot_uom_conv_rec.conversion_id := NULL;
1362 /*==============================================
1363 Insert the new conversion and audit record.
1364 if conversion does not exist already.
1365 ==============================================*/
1366 l_exists_cnt := 0;
1367 OPEN c_check_exists;
1368 FETCH c_check_exists INTO l_exists_cnt;
1369 CLOSE c_check_exists;
1370 IF (l_exists_cnt = 0) THEN
1371 process_conversion_data(
1372 'I',5,l_reason_id,NULL,l_lot_uom_conv_rec,
1373 l_qty_update_tbl,l_return_status,l_msg_count,l_error_message,l_seq);
1374 IF (l_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
1375 CLOSE c_get_conversions;
1376 RAISE CONVERSION_INSERT_ERROR;
1377 END IF;
1378 END IF;
1379 FETCH c_get_conversions INTO l_lot_uom_conv_rec;
1380
1381 END LOOP;
1382
1383
1384 END IF; -- conversion cursor
1385
1386 CLOSE c_get_conversions;
1387
1388 /*=============================
1389 Issue commit if required.
1390 ============================*/
1391 IF (p_commit = FND_API.G_TRUE) THEN
1392 COMMIT;
1393 END IF;
1394
1395 x_return_status := FND_API.G_RET_STS_SUCCESS;
1396
1397 EXCEPTION
1398
1399 WHEN NO_CONVERSIONS_FOUND THEN
1400 FND_MESSAGE.SET_NAME('INV','INV_LOTC_NO_CONV_FOUND');
1401 FND_MSG_PUB.Add;
1402 x_return_status := FND_API.G_RET_STS_ERROR;
1403
1404 WHEN CONVERSION_INSERT_ERROR THEN
1405 ROLLBACK TO COPY_CONVERSION;
1406 FND_MESSAGE.SET_NAME('INV','INV_LOTC_CONV_INSERT_ERROR');
1407 FND_MSG_PUB.Add;
1408 IF (SQLCODE IS NOT NULL) THEN
1409 FND_MESSAGE.SET_NAME('INV','INV_LOTC_SQL_ERROR');
1410 FND_MESSAGE.SET_TOKEN('ERRCODE',SQLCODE);
1411 FND_MESSAGE.SET_TOKEN('ERRM',SQLERRM(SQLCODE));
1412 FND_MSG_PUB.Add;
1413 END IF;
1414 x_return_status := FND_API.G_RET_STS_ERROR;
1415
1416 WHEN OTHERS THEN
1417 ROLLBACK TO COPY_CONVERSION;
1418 IF (SQLCODE IS NOT NULL) THEN
1419 FND_MESSAGE.SET_NAME('INV','INV_LOTC_SQL_ERROR');
1420 FND_MESSAGE.SET_TOKEN('ERRCODE',SQLCODE);
1421 FND_MESSAGE.SET_TOKEN('ERRM',SQLERRM(SQLCODE));
1422 ELSE
1423 FND_MESSAGE.SET_NAME('INV','INV_BUSRULES_GENERIC_ERR');
1424 END IF;
1425 FND_MSG_PUB.Add;
1426 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1427
1428 END copy_lot_uom_conversions;
1429
1430
1431 /*===========================================================================
1432 -- FUNCTION:
1433 -- validate_onhand_equals_avail
1434 --
1435 -- DESCRIPTION:
1436 -- This function validates that no changes have been made to the quantity tree.
1437 -- Checks if any reservations exist.
1438 --
1439 -- PARAMETERS:
1440 -- p_organization_id IN NUMBER - organization id
1441 -- p_inventory_item_id IN NUMBER - item id
1442 -- p_lot_number IN VARCHAR2 - lot number
1443 -- p_header_id IN NUMBER - header id of current transaction
1444 -- return OUT NUMBER - G_TRUE or G_FALSE
1445 --
1446 -- SYNOPSIS:
1447 -- Check reservations.
1448 --
1449 -- HISTORY
1450 -- Joe DiIorio 01-Sept-2004 Created.
1451 --
1452 --=========================================================================== */
1453
1454
1455 FUNCTION validate_onhand_equals_avail (
1456 p_organization_id IN NUMBER,
1457 p_inventory_item_id IN NUMBER,
1458 p_lot_number IN VARCHAR2,
1459 p_header_id IN NUMBER)
1460 return NUMBER IS
1461
1462 /*============================================
1463 Cursor to check against interface table.
1464 ============================================*/
1465
1466 CURSOR c_check_mti IS
1467 SELECT 1
1468 FROM mtl_transactions_interface
1469 WHERE source_lot_number = p_lot_number
1470 AND inventory_item_id = p_inventory_item_id
1471 AND organization_id = p_organization_id
1472 AND transaction_type_id NOT IN (95,1004);
1473
1474 /*========================
1475 BUG#4320911
1476 Type from 96 to 1004.
1477 ========================*/
1478 CURSOR c_check_mti_head IS
1479 SELECT 1
1480 FROM mtl_transactions_interface
1481 WHERE source_lot_number = p_lot_number
1482 AND inventory_item_id = p_inventory_item_id
1483 AND organization_id = p_organization_id
1484 AND transaction_type_id NOT IN (95,1004)
1485 AND transaction_header_id <> p_header_id;
1486
1487
1488 /*============================================
1489 Cursor to check against temp transaction
1490 table.
1491 ============================================*/
1492
1493 CURSOR c_check_mmtt IS
1494 SELECT 1
1495 FROM mtl_material_transactions_temp
1496 WHERE lot_number = p_lot_number
1497 AND inventory_item_id = p_inventory_item_id
1498 AND organization_id = p_organization_id;
1499
1500 CURSOR c_check_mmtt_head IS
1501 SELECT 1
1502 FROM mtl_material_transactions_temp
1503 WHERE lot_number = p_lot_number
1504 AND inventory_item_id = p_inventory_item_id
1505 AND organization_id = p_organization_id
1506 AND transaction_header_id <> p_header_id
1507 AND process_flag <> 'E';
1508
1509 l_count NUMBER := 0;
1510
1511 EXISTING_RESERVATIONS EXCEPTION;
1512
1513
1514 BEGIN
1515
1516 /*============================================
1517 Cursor to check against interface table.
1518 ============================================*/
1519
1520 IF (p_header_id IS NULL) THEN
1521 OPEN c_check_mti;
1522 FETCH c_check_mti INTO l_count;
1523 CLOSE c_check_mti;
1524 IF (l_count > 0) THEN
1525 RAISE EXISTING_RESERVATIONS;
1526 END IF;
1527 ELSE
1528 OPEN c_check_mti_head;
1529 FETCH c_check_mti_head INTO l_count;
1530 CLOSE c_check_mti_head;
1531 IF (l_count > 0) THEN
1532 RAISE EXISTING_RESERVATIONS;
1533 END IF;
1534 END IF;
1535
1536 /*============================================
1537 Cursor to check against temp transaction
1538 table.
1539 ============================================*/
1540
1541 IF (p_header_id IS NULL) THEN
1542 OPEN c_check_mmtt;
1543 FETCH c_check_mmtt INTO l_count;
1544 CLOSE c_check_mmtt;
1545 IF (l_count > 0) THEN
1546 RAISE EXISTING_RESERVATIONS;
1547 END IF;
1548 ELSE
1549 OPEN c_check_mmtt_head;
1550 FETCH c_check_mmtt_head INTO l_count;
1551 CLOSE c_check_mmtt_head;
1552 IF (l_count > 0) THEN
1553 RAISE EXISTING_RESERVATIONS;
1554 END IF;
1555 END IF;
1556
1557
1558 RETURN G_TRUE;
1559
1560
1561 EXCEPTION
1562
1563 WHEN EXISTING_RESERVATIONS THEN
1564 FND_MESSAGE.SET_NAME('INV','INV_LOT_RESERVATIONS_EXIST');
1565 FND_MSG_PUB.Add;
1566 RETURN G_FALSE;
1567
1568
1569 WHEN OTHERS THEN
1570 RETURN G_FALSE;
1571
1572 END validate_onhand_equals_avail;
1573
1574
1575 END;