1 PACKAGE BODY INV_ROI_INTEGRATION_GRP AS
2 /* $Header: INVPROIB.pls 120.9 2011/05/18 09:26:38 nimisra ship $*/
3
4 -- Read the profile option that enables/disables the debug log
5 g_fnd_debug VARCHAR2(1) := NVL(FND_PROFILE.VALUE('AFLOG_ENABLED'),'N') ;
6
7 g_pkg_name CONSTANT VARCHAR2(30) :='INV_ROI_INTEGRATION_GRP' ;
8 g_module_prefix CONSTANT VARCHAR2(40) := 'inv.plsql.' || g_pkg_name || '.' ;
9 l_progress VARCHAR2(3) := '000' ;
10
11
12 /*##########################################################################
13 #
14 # PROCEDURE : print_debug
15 #
16 #
17 # DESCRIPTION : This is a local procedure used to log errors
18 #
19 #
20 #
21 #
22 # MODIFICATION HISTORY
23 # 10-AUG-2004 Punit Kumar Created
24 #
25 #########################################################################*/
26
27 PROCEDURE print_debug(
28 p_err_msg VARCHAR2 ,
29 p_level NUMBER DEFAULT 1
30 )
31 IS
32
33 BEGIN
34 IF (g_debug = 1) THEN
35 inv_mobile_helper_functions.tracelog(
36 p_err_msg => p_err_msg ,
37 p_module => 'INV_ROI_INTEGRATION_GRP' ,
38 p_level => p_level
39 );
40 DBMS_OUTPUT.PUT_LINE(p_err_msg);
41 END IF;
42
43 END print_debug;
44
45 /*##########################################################################
46 #
47 # PROCEDURE : print_stacked_messages
48 #
49 #
50 # DESCRIPTION : This is a local procedure used to log errors
51 #
52 #
53 #
54 #
55 # MODIFICATION HISTORY
56 # 09-SEP-2004 Punit Kumar Created
57 #
58 #########################################################################*/
59
60
61 PROCEDURE print_stacked_messages IS
62 l_error_message VARCHAR2(4000) := '';
63 BEGIN
64 inv_mobile_helper_functions.get_stacked_messages(l_error_message);
65 print_debug('STACKED ERROR MESSAGES : '||l_error_message,1);
66 END print_stacked_messages;
67
68
69 /*##########################################################################
70 #
71 # PROCEDURE INV_VALIDATE_LOT
72 #
73 # DESCRIPTION :
74 #
75 # Additional Validations/defaulting needs to be done on MTL_TRANSACTION_LOTS_TEMP are:
76 # Default origination type to 'Purchasing' , RMA Validations and do separate validations
77 # for new and existing lots.
78 # First the RMA set of validations will be performed so that incase of any error we need
79 # not carry out the remaining set of validations.
80 # For a new lot:
81 # If lot secondary quantity is not null then check item level deviation.
82 # If lot secondary quantity is null then check if item is dual uom cotrolled, if yes then
83 # default the lot secondary quantity.
84 # For an existing lot :
85 # Check lot indivisibility. (Call INV_LOT_API_PUB.validate_lot_indivisible )
86 # If lot secondary quantity is not null then check lot level deviation.
87 # If lot secondary quantity is null then check if item is dual uom cotrolled, if yes then
88 # default the lot secondary quantity .
89 #
90 # DESIGN REFERENCES:
91 # http://files.oraclecorp.com/content/AllPublic/Workspaces/
92 # Inventory%20Convergence-Public/Design/Oracle%20Purchasing/TDD/PO_ROI_TDD.zip
93 #
94 # MODIFICATION HISTORY
95 # 10-AUG-2004 Punit Kumar Created
96 # 08-Feb-2005 Punit Kumar Moved the lot indivisibility check to INV_LOT_API_PUB.PO_CHECK_INDIVISIBILITY.
97 #
98 #########################################################################*/
99
100
101 PROCEDURE INV_VALIDATE_LOT(
102 x_return_status OUT NOCOPY VARCHAR2 ,
103 x_msg_data OUT NOCOPY VARCHAR2 ,
104 x_msg_count OUT NOCOPY NUMBER ,
105 p_api_version IN NUMBER DEFAULT 1.0 ,
106 p_init_msg_lst IN VARCHAR2 := FND_API.G_FALSE ,
107 p_mtlt_rowid IN ROWID ,
108 p_transaction_type_id IN VARCHAR2 ,
109 p_new_lot IN VARCHAR2 ,
110 p_item_id IN NUMBER ,
111 p_to_organization_id IN NUMBER ,
112 p_lot_number IN VARCHAR2 ,
113 p_parent_lot_number IN VARCHAR2 ,
114 p_lot_quantity IN NUMBER ,
115 x_lot_secondary_quantity IN OUT NOCOPY NUMBER , ----bug 4025610
116 p_line_secondary_quantity IN NUMBER ,
117 p_secondary_unit_of_measure IN VARCHAR2 ,
118 p_transaction_unit_of_measure IN VARCHAR2 ,
119 p_source_document_code IN VARCHAR2 ,
120 p_OE_ORDER_HEADER_ID IN NUMBER ,
121 p_OE_ORDER_LINE_ID IN NUMBER ,
122 p_rti_id IN NUMBER ,
123 p_revision IN VARCHAR2 ,
124 p_subinventory_code IN VARCHAR2 ,
125 p_locator_id IN NUMBER ,
126 p_transaction_type IN VARCHAR2 ,
127 p_parent_txn_type IN VARCHAR2,
128 p_lot_primary_qty IN NUMBER -- Bug# 4233182
129 )
130 IS
131
132 l_api_name VARCHAR2(30) := 'INV_VALIDATE_LOT' ;
133 l_api_version CONSTANT NUMBER := 1.0 ;
134 l_return_status VARCHAR2(1) ;
135 l_msg_data VARCHAR2(3000) ;
136 l_msg_count NUMBER ;
137 l_count_lots NUMBER ;
138 l_lot_secondary_quantity NUMBER ;
139 l_enforce_rma_lot_value VARCHAR2(1) ;
140 v_lot_no VARCHAR2(80) ;
141 l_rma_lot_number VARCHAR2(80) ;
142 l_rma_quantity NUMBER ;
143 l_deviation_check NUMBER ;
144 l_count_rma_lots NUMBER ;
145 l_allowed VARCHAR2(1) ;
146 l_allowed_quantity NUMBER ;
147 v_lot_number VARCHAR2(80) ;
148 l_from_unit VARCHAR2(3) :=NULL ;
149 l_to_unit VARCHAR2(3) :=NULL ;
150 L_TRANSACTION_TYPE VARCHAR2(25) :=NULL ;
151 l_parent_txn_type VARCHAR2(25) :=NULL ;
152 l_source_document_code VARCHAR2(25) :=NULL ;
153 l_transaction_type_id NUMBER :=NULL ;
154
155 /*enhancement 4018794. Punit Kumar*/
156 l_lot_cont BOOLEAN ;
157 l_child_lot_cont BOOLEAN ;
158 /*end 4018794*/
159
160 /*enhancement 4019704. Punit Kumar*/
161 l_parent_trx_id NUMBER ;
162 l_pmy_rcv_qty NUMBER ;
163 l_lot_qty NUMBER ;
164 l_pmy_unit_of_meas VARCHAR2(100) ;
165 /*end 4019704*/
166
167 -- Bug# 4233182
168 --l_transaction_type VARCHAR2(25);
169 l_destination_type_code VARCHAR2(25);
170 l_auto_transact_code VARCHAR2(25);
171 l_parent_transaction_id NUMBER;
172 l_parent_transaction_type VARCHAR2(25);
173 l_parent_destination_type_code VARCHAR2(25);
174
175 --bug11734602
176 l_rma_unit_of_measure VARCHAR2(30);
177 l_pri_unit_of_measure VARCHAR2(30);
178
179 -- Bug 5365360
180 INVALID_ITEM EXCEPTION;
181
182 -- Bug 4246448 Added nvl
183 CURSOR Cr_lot_exists_line(v_lot_no VARCHAR2) IS
184 SELECT NVL(SUM(QUANTITY),0)
185 FROM oe_lot_serial_numbers
186 WHERE (line_id = p_oe_order_line_id
187 OR line_set_id IN
188 (SELECT line_set_id
189 FROM oe_order_lines_all
190 WHERE line_id = p_oe_order_line_id
191 AND header_id = p_oe_order_header_id))
192 AND lot_number = v_lot_no;
193
194
195 /* Enhancement #4019704 Punit Kumar 02-Dec-2004
196 fetching the earlier received quantity for that lot.*/
197 /*
198 CURSOR Cr_rcv_qty IS
199 SELECT primary_quantity , primary_unit_of_measure
200 FROM rcv_transactions
201 WHERE transaction_id = l_parent_trx_id ;
202 /*
203 AND transaction_type 'DELIVER'
204 AND source_document_code IN ('PO','RMA')
205 AND organization_id = p_to_organization_id ;
206 */
207
208 /*end #4019704 */
209
210 BEGIN
211
212 -- Standard call to check for call compatibility.
213 IF NOT fnd_api.compatible_api_call(
214 l_api_version,
215 p_api_version,
216 l_api_name,
217 'INV_ROI_INTEGRATION_GRP'
218 ) THEN
219 IF (g_debug = 1) THEN
220 print_debug('FND_API not compatible INV_ROI_INTEGRATION_GRP.INV_VALIDATE_LOT: '||l_progress, 1);
221 END IF;
222 RAISE fnd_api.g_exc_unexpected_error;
223 END IF;
224
225 l_progress := '002';
226
227 -- Initialize message list if p_init_msg_list is set to TRUE.
228 IF fnd_api.to_boolean(p_init_msg_lst) THEN
229 fnd_msg_pub.initialize;
230 END IF;
231
232 --Initialize the return status
233 x_return_status := FND_API.G_RET_STS_SUCCESS;
234
235 /*Defaulting of origination type to 'Purchasing' to be done
236 in INV_RCV_INTEGRATION_PVT.MOVE_LOT_SERIAL_INFO */
237
238 ---SAVEPOINT INV_VAL_LOT1;
239 l_progress := '003';
240
241 l_lot_secondary_quantity :=x_lot_secondary_quantity ;
242 /*
243 IF l_lot_secondary_quantity IS NULL THEN
244
245 -------Checking if the item is dual UOM controlled.
246 IF p_line_secondary_quantity > 0 AND p_secondary_unit_of_measure IS NOT NULL THEN
247 */
248 /* Check total no of lots from MTL_TRANSACTION_LOTS_TEMP against transactuion id
249 and product code (RCV).IF there is only one lot for the receipt line and receipt
250 line qty (p_line_quantity) = lot qty THEN default the secondary qty from line
251 to lot record. */
252 /*
253 BEGIN
254 SELECT count(LOT_NUMBER)
255 INTO l_count_lots
256 FROM MTL_TRANSACTION_LOTS_TEMP
257 WHERE PRODUCT_TRANSACTION_ID =p_rti_id
258 AND PRODUCT_CODE = 'RCV' ;
259
260 IF l_count_lots = 1 THEN
261 l_lot_secondary_quantity := p_line_secondary_quantity;
262
263 UPDATE mtl_transaction_lots_temp
264 SET secondary_quantity = l_lot_secondary_quantity
265 WHERE rowid = p_mtlt_rowid ;
266
267 x_lot_secondary_quantity := l_lot_secondary_quantity ;
268
269 END IF;
270
271 l_progress := '004';
272
273 IF g_debug = 1 THEN
274 print_debug('Checking no of lots:' || l_progress, 1);
275 print_debug('no of lots and lot secondary quantity is : ' || l_count_lots|| ' and '|| x_lot_secondary_quantity, 1);
276 END IF;
277
278 EXCEPTION
279 WHEN OTHERS THEN
280 IF g_debug = 1 THEN
281 print_debug('Lot count/Sec quant updation failed for single lot case: ' || l_progress, 1);
282 END IF;
283 RAISE g_exc_unexpected_error;
284 END;
285
286
287 END IF;-------IF p_line_secondary_quantity IS NOT NULL THEN
288 END IF;----------IF l_lot_secondary_quantity IS NULL THEN
289 */
290 --------------------------------------------RMA Section,Start---------------------------------------
291 ---SAVEPOINT INV_VAL_LOT2;
292 l_progress := '005';
293
294 IF p_source_document_code = 'RMA' THEN
295
296 IF g_debug = 1 THEN
297 print_debug('RMA validation started:' || p_source_document_code, 1);
298 END IF;
299
300 -----Fetch the persmission value from rcv_parameters.
301 BEGIN
302 SELECT ENFORCE_RMA_LOT_NUM
303 INTO l_enforce_rma_lot_value
304 FROM rcv_parameters
305 WHERE organization_id=p_to_organization_id;
306
307
308 IF g_debug = 1 THEN
309 print_debug('ENFORCE_RMA_LOT_NUM value is :' || l_enforce_rma_lot_value || ', ' || l_progress, 1);
310 END IF;
311
312 EXCEPTION
313 WHEN OTHERS THEN
314 IF g_debug = 1 THEN
315 print_debug('ENFORCE_RMA_LOT_NUM fetch failed ' || l_progress, 1);
316 END IF;
317 RAISE g_exc_unexpected_error;
318 END;
319
320 /* l_enforce_rma_lot_value can be 'U' , 'R' or 'W'
321 (for unrestricted, restricted or restricted with warning respectively) */
322
323 l_progress := '006';
324
325 IF l_enforce_rma_lot_value IS NULL THEN
326 l_enforce_rma_lot_value :='U' ;
327 END IF;
328
329 IF l_enforce_rma_lot_value = 'R' THEN
330 /* for ROI , 'U' and 'W' are same as we shall not insert any
331 error in po_interface_errors for 'W' (as we do not ask any question from the user) */
332
333 /*check whether lot is specified in the RMA for that Receipt Line.. */
334
335 IF g_debug = 1 THEN
336 print_debug('Before calling INV_ROI_INTEGRATION_GRP.Inv_Rma_lot_info_exists:' || l_progress, 1);
337 print_debug('p_oe_order_header_id:' || p_oe_order_header_id, 1);
338 print_debug('p_oe_order_line_id:' || p_oe_order_line_id, 1);
339 END IF;
340
341
342 IF (INV_ROI_INTEGRATION_GRP.Inv_Rma_lot_info_exists(
343 x_msg_data =>l_msg_data
344 ,x_msg_count =>l_msg_count
345 ,x_count_rma_lots =>l_count_rma_lots
346 ,p_oe_order_header_id =>p_oe_order_header_id
347 ,p_oe_order_line_id =>p_oe_order_line_id
348 )) THEN
349
350 /* Function returns TRUE => lot is entered on RMA so it has to be validated against user entered lot */
351 v_lot_no :=p_lot_number;
352
353 IF g_debug = 1 THEN
354 print_debug(' INV_ROI_INTEGRATION_GRP.Inv_Rma_lot_info_exists returns TRUE : ' || l_progress, 1);
355 print_debug('l_msg_data:' || l_msg_data, 1);
356 print_debug('l_msg_count:' || l_msg_count, 1);
357 print_debug('l_count_rma_lots:' || l_count_rma_lots, 1);
358 print_debug('p_lot_number:' || p_lot_number, 1);
359 END IF;
360
361
362 IF p_oe_order_line_id IS NOT NULL THEN
363 /* OM allows duplicate entry of lots in their lot serial form.
364 We need to sum up the quantity.
365 Also Cr_lot_exists_line validates the RMA lot against user entered lot */
366
367 -- Bug 4246448 pass p_lot_number instead of v_lot_number to the cursor
368 OPEN Cr_lot_exists_line(p_lot_number);
369 FETCH Cr_lot_exists_line
370 INTO l_rma_quantity;
371 CLOSE Cr_lot_exists_line;
372
373 IF g_debug = 1 THEN
374 print_debug('Matching user entered lot with RMA lot :' || l_progress, 1);
375 print_debug('RMA quantity is :' || l_rma_quantity, 1);
376 END IF;
377
378 /*bug11734602,if l_rma_quantity returned from oe_lot_serial_numbers,
379 since its quantity always be populated with primary qty,considering the code logic
380 in INV_ROI_INTEGRATION_GRP.Inv_Validate_rma_quantity,
381 we should convert the l_rma_quantity into RMA UNIT_OF_MEASURE.*/
382 SELECT unit_of_measure
383 INTO l_rma_unit_of_measure
384 FROM oe_order_lines_all ,mtl_units_of_measure
385 WHERE header_id = p_oe_order_header_id
386 AND line_id = p_oe_order_line_id
387 AND uom_code = order_quantity_uom;
388
389 SELECT PRIMARY_UNIT_OF_MEASURE
390 INTO l_pri_unit_of_measure
391 FROM mtl_system_items_b
392 WHERE INVENTORY_ITEM_ID = p_item_id
393 AND organization_id = p_to_organization_id;
394
395 IF Nvl(l_rma_unit_of_measure,-1) <> Nvl(l_pri_unit_of_measure,-1) THEN
396 l_rma_quantity := INV_CONVERT.inv_um_convert(
397 item_id => p_item_id ,
398 lot_number => p_lot_number ,
399 organization_id => p_to_organization_id ,
400 precision => 5 ,
401 from_quantity => l_rma_quantity ,
402 from_unit => NULL ,
403 to_unit => NULL ,
404 from_name => l_pri_unit_of_measure ,
405 to_name => l_rma_unit_of_measure
406 );
407 END IF;
408 --bug11734602 end
409
410 IF l_rma_quantity = 0 THEN
411 IF g_debug = 1 THEN
412 print_debug('RMA lot does not match with user lot so error out' || l_progress, 4);
413 END IF;
414 FND_MESSAGE.SET_NAME('PO','PO_RMA_LOT_MISMATCH');
415 FND_MESSAGE.SET_TOKEN('PGM_NAME','INV_ROI_INTEGRATION_GRP.Inv_Validate_lot');
416 fnd_msg_pub.ADD;
417 RAISE g_exc_error;
418 END IF;--------IF l_rma_quantity = 0 THEN
419 ELSE ------ IF p_oe_order_line_id IS NOT NULL THEN
420 IF g_debug = 1 THEN
421 print_debug('p_oe_order_line_id is NULL : ' || l_progress, 1);
422 END IF;
423 END IF; -------IF p_oe_order_line_id IS NOT NULL THEN
424
425 /*control will come here only if RMA lot matches with user lot
426 so proceed with quantity validation.*/
427
428 l_progress := '006' ;
429
430 IF g_debug = 1 THEN
431 print_debug('Before calling INV_ROI_INTEGRATION_GRP.Inv_Validate_rma_quantity : ' || l_progress, 1);
432 print_debug('p_item_id: ' || p_item_id, 1);
433 print_debug('p_lot_number: ' || p_lot_number, 1);
434 print_debug('p_oe_order_header_id: ' || p_oe_order_header_id, 1);
435 print_debug('p_oe_order_line_id: ' || p_oe_order_line_id, 1);
436 print_debug('p_rma_quantity: ' || l_rma_quantity, 1);
437 print_debug('p_trx_unit_of_measure: ' || p_transaction_unit_of_measure, 1);
438 print_debug('p_rti_id: ' || p_rti_id, 1);
439 print_debug('p_to_organization_id: ' || p_to_organization_id, 1);
440 END IF;
441
442
443 INV_ROI_INTEGRATION_GRP.Inv_Validate_rma_quantity(
444 x_allowed =>l_allowed ,
445 x_allowed_quantity =>l_allowed_quantity ,
446 x_return_status =>l_return_status ,
447 x_msg_data =>l_msg_data ,
448 x_msg_count =>l_msg_count ,
449 p_api_version =>1.0 ,
450 p_init_msg_list =>FND_API.G_FALSE ,
451 p_item_id =>p_item_id ,
452 p_lot_number =>p_lot_number ,
453 p_oe_order_header_id =>p_OE_ORDER_HEADER_ID ,
454 p_oe_order_line_id =>p_OE_ORDER_LINE_ID ,
455 p_rma_quantity =>l_rma_quantity ,
456 p_trx_unit_of_measure =>p_transaction_unit_of_measure ,
457 p_rti_id =>p_rti_id ,
458 p_to_organization_id =>p_to_organization_id ,
459 p_trx_quantity =>NULL
460 );
461
462 IF g_debug = 1 THEN
463 print_debug('Program INV_ROI_INTEGRATION_GRP.Inv_Validate_rma_quantity return ' || l_return_status ||'and ' || l_progress, 1);
464 print_debug('l_allowed: ' || l_allowed, 1);
465 print_debug('l_allowed_quantity: ' || l_allowed_quantity, 1);
466 print_debug('l_return_status: ' || l_return_status, 1);
467 print_debug('l_msg_data: ' || l_msg_data, 1);
468 print_debug('l_msg_count: ' || l_msg_count, 1);
469 END IF;
470
471 IF l_allowed = 'N' THEN
472 IF g_debug = 1 THEN
473 print_debug('Program INV_ROI_INTEGRATION_GRP.Inv_Validate_rma_quantity has failed
474 as user quantity is greater than the available RMA quantity ' || l_progress, 1);
475 END IF;
476 FND_MESSAGE.SET_NAME('INV','INV_RMA_QUANTITY_VAL_FAILED');
477 FND_MESSAGE.SET_TOKEN('x_allowed_quantity',l_allowed_quantity );
478 FND_MESSAGE.SET_TOKEN('p_lot_number',p_lot_number );
479 fnd_msg_pub.ADD;
480 RAISE g_exc_error;
481 END IF;
482
483 l_progress := '007' ;
484
485 IF l_return_status = fnd_api.g_ret_sts_error THEN
486 IF g_debug = 1 THEN
487 print_debug('Program INV_ROI_INTEGRATION_GRP.Inv_Validate_rma_quantity has failed with a user defined exception '|| l_progress, 1);
488 END IF;
489 FND_MESSAGE.SET_NAME('INV','INV_PROGRAM_ERROR');
490 FND_MESSAGE.SET_TOKEN('PGM_NAME','INV_ROI_INTEGRATION_GRP.Inv_Validate_rma_quantity');
491 fnd_msg_pub.ADD;
492 RAISE g_exc_error;
493
494 l_progress := '008' ;
495
496 ELSIF l_return_status = fnd_api.g_ret_sts_unexp_error THEN
497 IF g_debug = 1 THEN
498 print_debug('Program INV_ROI_INTEGRATION_GRP.Inv_Validate_rma_quantity has failed with a Unexpected exception ' || l_progress , 1);
499 END IF;
500 FND_MESSAGE.SET_NAME('INV','INV_PROGRAM_ERROR');
501 FND_MESSAGE.SET_TOKEN('PGM_NAME','INV_ROI_INTEGRATION_GRP.Inv_Validate_rma_quantity');
502 fnd_msg_pub.ADD;
503 RAISE g_exc_unexpected_error;
504 END IF;
505
506 l_progress :='009';
507
508 ELSE --------IF (INV_ROI_INTEGRATION_GRP.Inv_Rma_lot_info_exists(
509 /* lot is not present on RMA so user can receive into any valid
510 lot or create new lot and no quantity validation required.
511 Take RMA validation as Success*/
512 IF g_debug = 1 THEN
513 print_debug(' INV_ROI_INTEGRATION_GRP.Inv_Rma_lot_info_exists returns FALSE : ' || l_progress, 1);
514 print_debug('l_msg_data: ' || l_msg_data, 1);
515 print_debug('l_msg_count: ' || l_msg_count, 1);
516 print_debug('l_count_rma_lots: ' || l_count_rma_lots, 1);
517 print_debug('lot is not present on RMA so user can receive into any valid
518 lot or create new lot and no quantity validation required.
519 Take RMA validation as Success: ' || l_progress, 1);
520 END IF;
521
522 x_return_status := FND_API.G_RET_STS_SUCCESS;
523 END IF;--------IF (INV_ROI_INTEGRATION_GRP.Inv_Rma_lot_info_exists(
524
525 END IF;--------IF l_enforce_rma_lot_value = 'R' THEN
526
527 IF g_debug = 1 THEN
528 print_debug('RMA validation finished:' || l_progress, 1);
529 END IF;
530
531 END IF;--------------- IF l_source_document_code = 'RMA' THEN
532
533
534
535 ----------------------END OF RMA Section-----Start Lot Validation Logic for an existing Lot-----------------------------------------------
536 ---SAVEPOINT INV_VAL_LOT3;
537 l_progress := '010';
538
539 IF p_new_lot= 'N' THEN
540
541 /*Enhancement 4018794, Punit Kumar*/
542
543 l_lot_cont := FALSE ;
544 l_child_lot_cont := FALSE ;
545
546
547 INV_ROI_INTEGRATION_GRP.Check_Item_Attributes(
548 x_return_status => l_return_status
549 , x_msg_count => l_msg_count
550 , x_msg_data => l_msg_data
551 , x_lot_cont => l_lot_cont
552 , x_child_lot_cont => l_child_lot_cont
553 , p_inventory_item_id => p_item_id
554 , p_organization_id => p_to_organization_id
555 );
556
557 IF g_debug = 1 THEN
558 print_debug('Program Inv_lot_api_pkg.Check_Item_Attributes return ' || l_return_status, 9);
559 END IF;
560
561 IF l_return_status = fnd_api.g_ret_sts_error THEN
562 IF g_debug = 1 THEN
563 print_debug('Program Inv_lot_api_pkg.Check_Item_Attributes has failed with error', 9);
564 END IF;
565 FND_MESSAGE.SET_NAME('INV', 'INV_PROGRAM_ERROR') ;
566 FND_MESSAGE.SET_TOKEN('PGM_NAME','Inv_lot_api_pkg.Check_Item_Attributes');
567 FND_MSG_PUB.ADD;
568 RAISE g_exc_error;
569 END IF;
570
571 IF l_return_status = fnd_api.g_ret_sts_unexp_error THEN
572 IF g_debug = 1 THEN
573 print_debug('Program Inv_lot_api_pkg.Check_Item_Attributes has failed with a Unexpected exception', 9);
574 END IF;
575 FND_MESSAGE.SET_NAME('INV','INV_PROGRAM_ERROR');
576 FND_MESSAGE.SET_TOKEN('PGM_NAME','Inv_lot_api_pkg.Check_Item_Attributes');
577 fnd_msg_pub.ADD;
578 RAISE g_exc_unexpected_error;
579 END IF;
580
581 IF (l_lot_cont = FALSE) THEN
582 IF g_debug = 1 THEN
583 print_debug(' Item is not lot controlled ', 9);
584 END IF;
585 fnd_message.set_name('INV', 'INV_NO_LOT_CONTROL');
586 fnd_msg_pub.ADD;
587 x_return_status := fnd_api.g_ret_sts_error;
588 RAISE g_exc_error;
589 END IF ;
590
591 IF (l_child_lot_cont = FALSE AND p_parent_lot_number IS NOT NULL) THEN
592 IF g_debug = 1 THEN
593 print_debug(' Item is not Child lot controlled ', 9);
594 END IF;
595 fnd_message.set_name('INV', 'INV_ITEM_CLOT_DISABLE_EXP');
596 fnd_msg_pub.ADD;
597 x_return_status := fnd_api.g_ret_sts_error;
598 RAISE g_exc_error;
599 END IF ;
600
601
602 /******************* End enhancement 4018794 ********************/
603
604 -- BEGIN Bug# 4233182
605 IF p_rti_id IS NOT NULL THEN
606 print_debug('Get transaction details ' , 1);
607 SELECT transaction_type, destination_type_code, auto_transact_code, parent_transaction_id
608 INTO l_transaction_type, l_destination_type_code, l_auto_transact_code, l_parent_transaction_id
609 FROM rcv_transactions_interface
610 WHERE interface_transaction_id = p_rti_id;
611 END IF;
612
613 IF l_parent_transaction_id IS NOT NULL OR l_parent_transaction_id <> -1 THEN
614 print_debug('Get parent transaction details ' , 1);
615 SELECT transaction_type, destination_type_code
616 INTO l_parent_transaction_type, l_parent_destination_type_code
617 FROM rcv_transactions WHERE transaction_id = l_parent_transaction_id;
618 END IF;
619
620 IF ( (l_transaction_type = 'DELIVER' AND l_destination_type_code = 'INVENTORY') OR
621 (l_transaction_type = 'RECEIVE' AND l_auto_transact_code = 'DELIVER' AND l_destination_type_code = 'INVENTORY') OR
622 (l_transaction_type = 'CORRECT' AND l_parent_transaction_type = 'DELIVER' AND l_parent_destination_type_code = 'INVENTORY') OR
623 (l_transaction_type = 'RETURN TO VENDOR' AND l_parent_transaction_type = 'DELIVER' AND l_parent_destination_type_code = 'INVENTORY') OR
624 (l_transaction_type = 'RETURN TO CUSTOMER' AND l_parent_transaction_type = 'DELIVER' AND l_parent_destination_type_code = 'INVENTORY') OR
625 (l_transaction_type = 'RETURN TO RECEIVING' AND l_parent_transaction_type = 'DELIVER' AND l_parent_destination_type_code = 'INVENTORY')
626 ) THEN
627 -- its a Inventory transaction
628 -- Call check lot indivisible API.
629
630 print_debug('Calling inv_lot_api_pub.CHECK_LOT_INDIVISIBILITY' , 1);
631 inv_lot_api_pub.CHECK_LOT_INDIVISIBILITY (
632 p_api_version => 1.0
633 , p_init_msg_list => FND_API.G_FALSE
634 , p_commit => FND_API.G_FALSE
635 , p_validation_level => FND_API.G_VALID_LEVEL_FULL
636 , p_rti_id => p_rti_id
637 , p_transaction_type_id => p_transaction_type_id
638 , p_lot_number => p_lot_number
639 , p_lot_quantity => p_lot_primary_qty
640 , p_revision => p_revision
641 , p_qoh => NULL
642 , p_atr => NULL
643 , x_return_status => l_return_status
644 , x_msg_count => l_msg_count
645 , x_msg_data => l_msg_data
646 );
647
648 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
649 print_debug('Lot Indivisiblity check failure in inv_lot_api_pub.CHECK_LOT_INDIVISIBILITY' , 1);
650 RAISE FND_API.G_EXC_ERROR;
651 END IF;
652 END IF;
653
654 -- END Bug# 4233182
655
656 /*Moving the lot indiv call from here to INV_LOT_API_PUB.PO_CHECK_INDIVISIBILITY
657 Punit Kumar, 08-02-2005 */
658
659 /*I
660
661 L_TRANSACTION_TYPE := p_transaction_type ;
662 l_parent_txn_type := p_parent_txn_type ;
663
664
665 /*
666 IF l_transaction_type IN ('TRANSFER','DELIVER','RETURN TO VENDOR',
667 'RETURN TO CUSTOMER','RETURN TO RECEIVING') THEN
668
669 */
670 /*I
671 l_source_document_code :=p_source_document_code;
672
673
674 /*Call lot indivisibility procedure to check if whether in case of indivisible
675 lots we are transacting a quantity less than that specified for that lot.
676
677 Call INV_LOT_API_PUB.validate_lot_indivisible (@ INVPLOTB.pls) */
678 /*I
679 IF g_debug = 1 THEN
680 print_debug('p_transaction_type_id is ' || p_transaction_type_id, 9);
681 print_debug('l_transaction_type is ' || l_transaction_type, 9);
682 print_debug('l_parent_txn_type is ' || l_parent_txn_type, 9);
683 print_debug('l_source_document_code is ' || l_source_document_code, 9);
684
685 END IF;
686
687 /* populating the p_transaction_type_id for various transactions */
688
689 /*
690 IF p_transaction_type_id IS NULL THEN
691
692 -----Deliver transactions
693 IF l_transaction_type ='DELIVER' THEN
694 IF (l_source_document_code = 'PO') THEN
695 l_transaction_type_id := 18;
696 ELSIF (l_source_document_code = 'RMA') THEN
697 l_transaction_type_id := 15;
698 END IF;
699 END IF;
700
701 -----Transfer transactions
702 IF l_parent_txn_type IN ('RECEIVE','ACCEPT','TRANSFER','RETURN TO RECEIVING',
703 'REJECT') THEN
704 IF l_transaction_type='TRANSFER' THEN
705 l_transaction_type_id := 71;
706 END IF;
707 END IF;
708
709 ------Return Transactions
710 IF l_parent_txn_type ='DELIVER' AND l_transaction_type = 'RETURN TO RECEIVING' THEN
711 IF (l_source_document_code = 'PO') THEN
712 l_transaction_type_id := 36;
713 ELSIF (l_source_document_code = 'RMA') THEN
714 l_transaction_type_id := 37;
715 END IF;
716
717 ELSIF l_parent_txn_type IN ('RECEIVE','ACCEPT','TRANSFER','RETURN TO RECEIVING',
718 'REJECT') AND l_transaction_type = 'RETURN TO VENDOR' THEN
719 l_transaction_type_id :=36 ;
720 ELSIF l_parent_txn_type = 'RETURN TO RECEIVING' AND l_transaction_type = 'RETURN TO CUSTOMER' THEN
721 l_transaction_type_id :=37 ;
722 END IF;
723 END IF;
724 */
725 /* end ,populating the p_transaction_type_id for various transactions */
726
727 /*I
728 IF (l_transaction_type IN ('RECEIVE','ACCEPT','REJECT','TRANSFER','DELIVER')) THEN
729
730 IF (l_source_document_code = 'PO') THEN
731 l_transaction_type_id := 18; -- PO
732 ELSIF (l_source_document_code = 'RMA') THEN
733 l_transaction_type_id := 15; -- RMA
734 --ELSIF (l_source_document_code = 'INVENTORY') THEN
735 -- l_transaction_type_id := 12; -- Inter Org Intransit
736 ELSE
737 l_transaction_type_id := 61; -- Internal Req
738 END IF;
739
740 ELSIF (l_transaction_type IN ('RETURN TO RECEIVING','RETURN TO VENDOR','RETURN TO CUSTOMER')) THEN
741 IF (l_source_document_code = 'PO') THEN
742 l_transaction_type_id := 36; -- PO
743 ELSIF (l_source_document_code = 'RMA') THEN
744 l_transaction_type_id := 37; -- RMA
745 END IF;
746 END IF;
747
748 /*fix for p_trx_id not null and l_trx_id null*/
749 /*I
750 IF l_transaction_type_id IS NULL AND p_transaction_type_id IS NOT NULL THEN
751 l_transaction_type_id := p_transaction_type_id ;
752 END IF;
753 /*end fix*/
754 /*I
755 IF g_debug = 1 THEN
756 print_debug(' L_TRANSACTION_TYPE: '||L_TRANSACTION_TYPE , 9);
757 print_debug(' l_parent_txn_type: '||l_parent_txn_type , 9);
758 print_debug('l_source_document_code : '|| l_source_document_code, 9);
759 print_debug('l_transaction_type_id : '||l_transaction_type_id , 9);
760 END IF;
761
762 -----IF l_lot_ind_call = 'Y' THEN
763
764 IF NOT (INV_LOT_API_PUB.validate_lot_indivisible(
765 p_api_version =>1.0
766 ,p_init_msg_list =>FND_API.G_FALSE
767 ,p_commit =>FND_API.G_FALSE
768 ,p_validation_level =>FND_API.G_VALID_LEVEL_FULL
769 ,p_transaction_type_id =>l_transaction_type_id
770 ,p_organization_id =>p_to_organization_id
771 ,p_inventory_item_id =>p_item_id
772 ,p_revision =>p_revision
773 ,p_subinventory_code =>p_subinventory_code
774 ,p_locator_id =>p_locator_id
775 ,p_lot_number =>p_lot_number
776 ,p_primary_quantity =>p_lot_quantity------------the primary quantity of the transaction
777 ,p_qoh =>NULL
778 ,p_atr =>NULL
779 ,x_return_status =>l_return_status
780 ,x_msg_count =>l_msg_count
781 ,x_msg_data =>l_msg_data
782 ))THEN
783
784 ------IF qoh and atr are passed as NULL THEN quantity tree is called in the API.
785 IF g_debug = 1 THEN
786 print_debug('Program INV_LOT_API_PUB.validate_lot_indivisible return FALSE ' || l_return_status || 'and '|| l_progress, 9);
787 END IF;
788
789 l_progress := '100' ;
790
791 /* Enhancement #4019704. Allow Return transactions even if lot indivisibility fails but the
792 transaction quantity is equal to the received quantity. Punit Kumar. 02-Dec-2004. */
793 /*I
794 IF ((l_return_status <> FND_API.G_RET_STS_SUCCESS) AND l_transaction_type IN ('RETURN TO RECEIVING','RETURN TO VENDOR','RETURN TO CUSTOMER')) THEN
795
796 l_progress := '101';
797
798 l_lot_qty := p_lot_quantity ;
799
800 ------------------fetching PARENT_TRANSACTION_ID
801 SELECT PARENT_TRANSACTION_ID
802 INTO l_parent_trx_id
803 FROM RCV_TRANSACTIONS_INTERFACE
804 WHERE INTERFACE_TRANSACTION_ID = p_rti_id;
805
806 IF l_parent_trx_id IS NULL THEN
807 IF g_debug = 1 THEN
808 print_debug('parent txn id cannot be null '|| l_progress, 1);
809 END IF;
810 RAISE g_exc_unexpected_error;
811 END IF;
812
813 ------------------Get previously received primary quantity for the Lot.
814 OPEN Cr_rcv_qty ;
815 FETCH Cr_rcv_qty
816 INTO l_pmy_rcv_qty,l_pmy_unit_of_meas;
817 CLOSE Cr_rcv_qty ;
818
819 IF g_debug = 1 THEN
820 print_debug('l_parent_trx_id '|| l_parent_trx_id, 9);
821 print_debug('l_lot_qty '|| l_lot_qty, 9);
822 print_debug('l_pmy_rcv_qty '|| l_pmy_rcv_qty, 9);
823 print_debug('l_pmy_unit_of_meas '|| l_pmy_unit_of_meas, 9);
824 print_debug('p_transaction_unit_of_measure '|| p_transaction_unit_of_measure, 9);
825 END IF;
826
827 IF l_pmy_unit_of_meas <> p_transaction_unit_of_measure THEN
828
829 l_progress := '102';
830
831 /* Convert transaction qty in p_transaction_unit_of_measure to l_pmy_unit_of_meas */
832 /*I
833 l_lot_qty := INV_CONVERT.inv_um_convert(
834 item_id => p_item_id ,
835 lot_number => p_lot_number ,
836 organization_id => p_to_organization_id ,
837 precision => 5 ,
838 from_quantity => l_lot_qty ,
839 from_unit => NULL ,
840 to_unit => NULL ,
841 from_name => p_transaction_unit_of_measure ,
842 to_name => l_pmy_unit_of_meas
843 );
844
845 IF g_debug = 1 THEN
846 print_debug('Program INV_CONVERT.inv_um_convert return: ' || l_progress, 9);
847 print_debug('l_lot_qty: ' || l_lot_qty, 9);
848 END IF;
849
850 l_progress := '103';
851
852 IF l_lot_qty = -99999 THEN
853
854 IF g_debug = 1 THEN
855 print_debug('INV_CONVERT.inv_um_convert has failed '|| l_progress, 1);
856 END IF;
857
858 FND_MESSAGE.SET_NAME('INV','INV_NO_CONVERSION_ERR');
859 FND_MESSAGE.SET_TOKEN('PGM_NAME','INV_CONVERT.inv_um_convert');
860 fnd_msg_pub.ADD;
861
862 RAISE g_exc_unexpected_error;
863 END IF;
864
865 END IF; -----------IF l_pmy_unit_of_meas <> p_transaction_unit_of_measure
866
867 /* If the trx quantity = total received quantity for that parent deliver trx
868 then even though lot indivisibily fails , we shall allow the "Return" trx
869 */
870 /*I
871 IF l_lot_qty = l_pmy_rcv_qty THEN
872
873 IF g_debug = 1 THEN
874 print_debug('l_return_status'|| l_return_status, 9);
875 END IF;
876
877 l_return_status := FND_API.G_RET_STS_SUCCESS ;
878
879 IF g_debug = 1 THEN
880 print_debug('l_return_status'|| l_return_status, 9);
881 print_debug('set return status of validate_lot_indivisible to true'|| l_progress, 9);
882 END IF;
883
884 END IF; ----------IF (l_lot_qty = l_pmy_rcv_qty
885
886 END IF; -------- IF (l_transaction_type IN ('RETURN TO RECEIVING'
887
888 /* end , enhancement */
889 /* I
890 IF l_return_status = fnd_api.g_ret_sts_error THEN
891 IF g_debug = 1 THEN
892 print_debug('Program INV_LOT_API_PUB.validate_lot_indivisible has failed with a user defined exception '|| l_progress, 9);
893 END IF;
894
895 FND_MESSAGE.SET_NAME('INV','INV_PROGRAM_ERROR');
896 FND_MESSAGE.SET_TOKEN('PGM_NAME','INV_LOT_API_PUB.validate_lot_indivisible');
897 fnd_msg_pub.ADD;
898 RAISE g_exc_error;
899
900 ELSIF l_return_status = fnd_api.g_ret_sts_unexp_error THEN
901 l_progress := '012' ;
902 IF g_debug = 1 THEN
903 print_debug('Program INV_LOT_API_PUB.validate_lot_indivisible has failed with a Unexpected exception' || l_progress, 9);
904 END IF;
905
906 FND_MESSAGE.SET_NAME('INV','INV_PROGRAM_ERROR');
907 FND_MESSAGE.SET_TOKEN('PGM_NAME','INV_LOT_API_PUB.validate_lot_indivisible');
908 fnd_msg_pub.ADD;
909 RAISE g_exc_unexpected_error;
910 END IF;------------IF l_return_status = fnd_api.g_ret_sts_error THEN
911
912 END IF; ----IF NOT (INV_LOT_API_PUB.validate_lot_indivisible(
913
914 IF g_debug = 1 THEN
915 print_debug('Program INV_LOT_API_PUB.validate_lot_indivisible return TRUE ' || l_return_status || 'and '|| l_progress, 9);
916 END IF;
917
918 -------- END IF ; ----------IF l_lot_ind_call = 'Y' THEN
919
920 ----END IF; -------IF l_transaction_type IN ('TRANSFER','DELIVER'........
921 I*/
922
923
924 /*Check for secondary_quantity in receipt line (p_line_secondary_quantity), */
925 --IF p_line_secondary_quantity > 0 AND p_secondary_unit_of_measure IS NOT NULL THEN
926 IF nvl(p_line_secondary_quantity,0) <> 0 AND p_secondary_unit_of_measure IS NOT NULL THEN -- 7644869 change check as qty could be a correction and could be negative
927
928 /*item is dual UOM controlled .No need to check it..*/
929
930 IF l_lot_secondary_quantity IS NOT NULL THEN
931 /*Validate the secondary quantity, do lot level deviation check*/
932
933 l_progress := '013' ;
934
935 IF g_debug = 1 THEN
936 print_debug('Before calling INV_CONVERT.Within_deviation ' || l_progress, 1);
937 print_debug('p_quantity ' || p_lot_quantity, 1);
938 print_debug('p_quantity2 ' || l_lot_secondary_quantity, 1);
939 print_debug('p_unit_of_measure1 ' || p_transaction_unit_of_measure, 1);
940 print_debug('p_unit_of_measure2 ' || p_secondary_unit_of_measure, 1);
941 END IF;
942
943 IF NOT (INV_CACHE.set_item_rec(p_to_organization_id, p_item_id)) THEN
944 RAISE INVALID_ITEM;
945 END IF;
946
947 /* Bug 5365360 for a fixed type of item just recompute the lot secondary quantity)*/
948 IF (INV_CACHE.item_rec.secondary_default_ind = 'F') THEN
949 l_lot_secondary_quantity:= INV_CONVERT.inv_um_convert (
950 item_id => p_item_id
951 ,
952 lot_number => p_lot_number
953 ,
954 organization_id => p_to_organization_id
955 ,
956 precision => 5
957 ,
958 from_quantity => p_lot_quantity
959 ,
960 from_unit => l_from_unit ,
961 to_unit => l_to_unit ,
962 from_name => p_transaction_unit_of_measure ,
963 to_name => p_secondary_unit_of_measure
964 );
965 /*update the out variable*/
966 x_lot_secondary_quantity :=l_lot_secondary_quantity ;
967
968 ---update table with the defaulted secondary quantity
969 BEGIN
970 UPDATE mtl_transaction_lots_temp
971 SET secondary_quantity = l_lot_secondary_quantity
972 WHERE rowid = p_mtlt_rowid ;
973 IF g_debug = 1 THEN
974 print_debug('updated MTLT with the defaulted secondary quantity: ' || l_progress, 9);
975 END IF;
976
977 /* No need to update MTLI as rows are deleted from there after moving them to MTLT */
978
979
980 l_progress := '017' ;
981
982 EXCEPTION
983 WHEN OTHERS THEN
984 IF g_debug = 1 THEN
985 print_debug('UPDATE mtl_transaction_lots_temp with not null lot secondary quantity failed for an existing lot' || l_progress, 1);
986 END IF;
987 RAISE g_exc_unexpected_error;
988 END;
989
990
991 ELSE
992
993 l_deviation_check := INV_CONVERT.Within_deviation(
994 p_organization_id => p_to_organization_id ,
995 p_inventory_item_id => p_item_id ,
996 p_lot_number => p_lot_number ,
997 p_precision => 5 ,
998 p_quantity => p_lot_quantity ,--transaction quantity
999 p_uom_code1 => NULL ,
1000 p_quantity2 => l_lot_secondary_quantity ,
1001 p_uom_code2 => NULL ,
1002 p_unit_of_measure1 => p_transaction_unit_of_measure ,
1003 p_unit_of_measure2 => p_secondary_unit_of_measure
1004 );
1005
1006
1007 /*RETURN Number , 1 for True and 0 for False */
1008
1009 IF g_debug = 1 THEN
1010 print_debug('Program INV_CONVERT.Within_deviation return ' || l_progress, 1);
1011 END IF;
1012 END IF;
1013
1014
1015 l_progress := '014';
1016
1017 IF l_deviation_check = 0 THEN
1018 IF g_debug = 1 THEN
1019 print_debug('Program INV_CONVERT.Within_deviation has failed ' || l_progress, 1);
1020 END IF;
1021 FND_MESSAGE.SET_NAME('INV','INV_PROGRAM_ERROR');
1022 FND_MESSAGE.SET_TOKEN('PGM_NAME','INV_CONVERT.Within_deviation return');
1023 fnd_msg_pub.ADD;
1024 RAISE g_exc_unexpected_error;
1025 END IF;
1026
1027 ELSE -----if l_lot_secondary_quantity is NULL THEN default it
1028
1029 l_progress := '015';
1030
1031 IF g_debug = 1 THEN
1032 print_debug('Before calling INV_CONVERT.inv_um_convert ' || l_progress, 1);
1033 print_debug('from_quantity ' || p_lot_quantity, 1);
1034 print_debug('from_unit ' || l_from_unit, 1);
1035 print_debug('to_unit ' || l_to_unit, 1);
1036 print_debug('from_name ' || p_transaction_unit_of_measure, 1);
1037 print_debug('to_name ' || p_secondary_unit_of_measure, 1);
1038 END IF;
1039
1040
1041 l_lot_secondary_quantity:= INV_CONVERT.inv_um_convert (
1042 item_id => p_item_id ,
1043 lot_number => p_lot_number ,
1044 organization_id => p_to_organization_id ,
1045 precision => 5 ,
1046 from_quantity => p_lot_quantity ,
1047 from_unit => l_from_unit ,
1048 to_unit => l_to_unit ,
1049 from_name => p_transaction_unit_of_measure ,
1050 to_name => p_secondary_unit_of_measure
1051 );
1052
1053 IF g_debug = 1 THEN
1054 print_debug('Program INV_CONVERT.inv_um_convert return: ' || l_progress, 9);
1055 print_debug('l_lot_secondary_quantity: ' || l_lot_secondary_quantity, 9);
1056 END IF;
1057
1058 l_progress := '016';
1059
1060 IF l_lot_secondary_quantity = -99999 THEN
1061 IF g_debug = 1 THEN
1062 print_debug('INV_CONVERT.inv_um_convert has failed '|| l_progress, 1);
1063 END IF;
1064 FND_MESSAGE.SET_NAME('INV','INV_NO_CONVERSION_ERR');
1065 FND_MESSAGE.SET_TOKEN('PGM_NAME','INV_CONVERT.inv_um_convert');
1066 fnd_msg_pub.ADD;
1067
1068 RAISE g_exc_unexpected_error;
1069 END IF;
1070
1071 /*update the out variable*/
1072 x_lot_secondary_quantity :=l_lot_secondary_quantity ;
1073
1074 ---update table with the defaulted secondary quantity
1075 BEGIN
1076 UPDATE mtl_transaction_lots_temp
1077 SET secondary_quantity = l_lot_secondary_quantity
1078 WHERE rowid = p_mtlt_rowid ;
1079
1080 IF g_debug = 1 THEN
1081 print_debug('updated MTLT with the defaulted secondary quantity: ' || l_progress, 9);
1082 END IF;
1083
1084 /* No need to update MTLI as rows are deleted from there after moving them to MTLT */
1085
1086
1087 l_progress := '017' ;
1088
1089 EXCEPTION
1090 WHEN OTHERS THEN
1091 IF g_debug = 1 THEN
1092 print_debug('UPDATE mtl_transaction_lots_temp with not null lot secondary quantity failed for an existing lot' || l_progress, 1);
1093 END IF;
1094 RAISE g_exc_unexpected_error;
1095 END;
1096
1097
1098 END IF; ------ IF l_lot_secondary_quantity IS NOT NULL THEN
1099
1100 ELSE ------ IF p_line_secondary_quantity IS NOT NULL THEN
1101
1102 l_progress := '018';
1103 -----blank the lot secondary quantity
1104 l_lot_secondary_quantity :=NULL;
1105
1106 /*update the out variable*/
1107 x_lot_secondary_quantity :=l_lot_secondary_quantity ;
1108
1109 ---update table with NULL secondary quantity
1110 BEGIN
1111 UPDATE mtl_transaction_lots_temp
1112 SET secondary_quantity = l_lot_secondary_quantity
1113 WHERE rowid = p_mtlt_rowid ;
1114
1115 IF g_debug = 1 THEN
1116 print_debug('updated MTLT with NULL secondary quantity: ' || l_progress, 9);
1117 END IF;
1118
1119
1120 EXCEPTION
1121 WHEN OTHERS THEN
1122 IF g_debug = 1 THEN
1123 print_debug('UPDATE mtl_transaction_lots_temp with null lot secondary quantity failed for an existing lot' || l_progress, 1);
1124 END IF;
1125 RAISE g_exc_unexpected_error;
1126 END;
1127
1128 END IF; ---- IF p_line_secondary_quantity IS NOT NULL THEN
1129
1130 /* end of validation for an existing Lot ("If p_new_lot= 'N' THEN").Start Lot validation for a new Lot */
1131
1132 ---SAVEPOINT INV_VAL_LOT4;
1133 l_progress := '019';
1134
1135 ELSIF p_new_lot = 'Y' THEN
1136 --IF p_line_secondary_quantity > 0 AND p_secondary_unit_of_measure IS NOT NULL THEN
1137 IF nvl(p_line_secondary_quantity,0) <> 0 AND p_secondary_unit_of_measure IS NOT NULL THEN -- 7644869 - change check as qty could be negative
1138 ----item is dual UOM controlled .No need to check it..)
1139 IF l_lot_secondary_quantity IS NOT NULL THEN
1140 ---Validate the secondary quantity
1141
1142 IF g_debug = 1 THEN
1143 print_debug('Before calling INV_CONVERT.Within_deviation ' || l_progress, 1);
1144 print_debug('p_quantity ' || p_lot_quantity, 1);
1145 print_debug('p_quantity2 ' || l_lot_secondary_quantity, 1);
1146 print_debug('p_unit_of_measure1 ' || p_transaction_unit_of_measure, 1);
1147 print_debug('p_unit_of_measure2 ' || p_secondary_unit_of_measure, 1);
1148 END IF;
1149
1150 IF NOT (INV_CACHE.set_item_rec(p_to_organization_id, p_item_id)) THEN
1151 RAISE INVALID_ITEM;
1152 END IF;
1153
1154 /* Bug 5365360 for a fixed type of item just recompute the lot secondary quantity)*/
1155 IF (INV_CACHE.item_rec.secondary_default_ind = 'F') THEN
1156 l_lot_secondary_quantity:= INV_CONVERT.inv_um_convert (
1157 item_id => p_item_id ,
1158 lot_number => p_lot_number ,
1159 organization_id => p_to_organization_id ,
1160 precision => 5 ,
1161 from_quantity => p_lot_quantity ,
1162 from_unit => l_from_unit ,
1163 to_unit => l_to_unit ,
1164 from_name => p_transaction_unit_of_measure ,
1165 to_name => p_secondary_unit_of_measure
1166 );
1167 /*update the out variable*/
1168 x_lot_secondary_quantity :=l_lot_secondary_quantity ;
1169
1170 ---update table with the defaulted secondary quantity
1171 BEGIN
1172 UPDATE mtl_transaction_lots_temp
1173 SET secondary_quantity = l_lot_secondary_quantity
1174 WHERE rowid = p_mtlt_rowid ;
1175 IF g_debug = 1 THEN
1176 print_debug('updated MTLT with the defaulted secondary quantity: ' || l_progress, 9);
1177 END IF;
1178
1179 /* No need to update MTLI as rows are deleted from there after moving them to MTLT */
1180
1181
1182 l_progress := '017' ;
1183
1184 EXCEPTION
1185 WHEN OTHERS THEN
1186 IF g_debug = 1 THEN
1187 print_debug('UPDATE mtl_transaction_lots_temp with not null lot secondary quantity failed for an
1188 existing lot' || l_progress, 1);
1189 END IF;
1190 RAISE g_exc_unexpected_error;
1191 END;
1192
1193
1194 ELSE
1195
1196
1197 ---Do item level deviation check
1198 l_deviation_check := INV_CONVERT.Within_deviation(
1199 p_organization_id => p_to_organization_id ,
1200 p_inventory_item_id => p_item_id ,
1201 p_lot_number => p_lot_number ,---new lot number
1202 p_precision => 5 ,
1203 p_quantity => p_lot_quantity ,----transaction quantity
1204 p_uom_code1 => NULL ,
1205 p_quantity2 => l_lot_secondary_quantity ,
1206 p_uom_code2 => NULL ,
1207 p_unit_of_measure1 => p_transaction_unit_of_measure ,
1208 p_unit_of_measure2 => p_secondary_unit_of_measure
1209 );
1210
1211 l_progress := '020';
1212
1213 /*RETURN Number , 1 for True and 0 for False */
1214
1215 IF g_debug = 1 THEN
1216 print_debug('Program INV_CONVERT.Within_deviation return ' || l_progress, 1);
1217 END IF;
1218
1219 IF l_deviation_check = 0 THEN
1220 IF g_debug = 1 THEN
1221 print_debug('Program INV_CONVERT.Within_deviation has failed ' || l_progress, 9);
1222 END IF;
1223 FND_MESSAGE.SET_NAME('INV','INV_PROGRAM_ERROR');
1224 FND_MESSAGE.SET_TOKEN('PGM_NAME','INV_CONVERT.Within_deviation return');
1225 fnd_msg_pub.ADD;
1226 RAISE g_exc_error;
1227 END IF;
1228
1229 END IF;
1230
1231 ELSE -----if l_lot_secondary_quantity is NULL
1232
1233 IF g_debug = 1 THEN
1234 print_debug('Before calling INV_CONVERT.inv_um_convert ' || l_progress, 1);
1235 print_debug('from_quantity ' || p_lot_quantity, 1);
1236 print_debug('from_unit ' || l_from_unit, 1);
1237 print_debug('to_unit ' || l_to_unit, 1);
1238 print_debug('from_name ' || p_transaction_unit_of_measure, 1);
1239 print_debug('to_name ' || p_secondary_unit_of_measure, 1);
1240 END IF;
1241
1242 ---Default the secondary quantity.
1243 l_lot_secondary_quantity:= INV_CONVERT.Inv_um_convert(
1244 item_id => p_item_id ,
1245 lot_number => p_lot_number,----new lot number
1246 organization_id => p_to_organization_id ,
1247 precision => 5 ,
1248 from_quantity => p_lot_quantity ,
1249 from_unit => l_from_unit ,
1250 to_unit => l_to_unit ,
1251 from_name => p_transaction_unit_of_measure ,
1252 to_name => p_secondary_unit_of_measure
1253 );
1254 l_progress :='021';
1255
1256 IF g_debug = 1 THEN
1257 print_debug('Program INV_CONVERT.inv_um_convert return: '||l_progress , 1);
1258 END IF;
1259
1260 IF l_lot_secondary_quantity = -99999 THEN
1261 IF g_debug = 1 THEN
1262 print_debug('INV_CONVERT.inv_um_convert has failed'|| l_progress, 9);
1263 END IF;
1264 FND_MESSAGE.SET_NAME('INV','INV_NO_CONVERSION_ERR');
1265 FND_MESSAGE.SET_TOKEN('PGM_NAME','INV_CONVERT.inv_um_convert');
1266 fnd_msg_pub.ADD;
1267 RAISE g_exc_unexpected_error;
1268 END IF;
1269
1270 l_progress :='022';
1271
1272 /*update the out variable*/
1273 x_lot_secondary_quantity :=l_lot_secondary_quantity ;
1274
1275 ---update table with the defaulted secondary quantity
1276 BEGIN
1277 UPDATE mtl_transaction_lots_temp
1278 SET secondary_quantity = l_lot_secondary_quantity
1279 WHERE rowid = p_mtlt_rowid ;
1280
1281 IF g_debug = 1 THEN
1282 print_debug('updated MTLT with the defaulted secondary quantity: ' || l_progress, 9);
1283 END IF;
1284
1285 /* No need to update MTLI as rows are deleted from there after moving them to MTLT */
1286
1287
1288
1289 EXCEPTION
1290 WHEN OTHERS THEN
1291 IF g_debug = 1 THEN
1292 print_debug('UPDATE mtl_transaction_lots_temp with not null lot secondary quantity failed for a new lot'|| l_progress, 4);
1293 END IF;
1294 RAISE g_exc_unexpected_error;
1295 END;
1296
1297 END IF; ------ IF l_lot_secondary_quantity IS NOT NULL THEN
1298
1299 ELSE ------ IF p_line_secondary_quantity IS NULL THEN
1300 l_progress :='023';
1301 ----blank the lot secondary quantity
1302 l_lot_secondary_quantity :=NULL ;
1303
1304 /*update the out variable*/
1305 x_lot_secondary_quantity := l_lot_secondary_quantity ;
1306
1307 ---update table with NULL secondary quantity
1308 BEGIN
1309 UPDATE mtl_transaction_lots_temp
1310 SET secondary_quantity = l_lot_secondary_quantity
1311 WHERE rowid = p_mtlt_rowid ;
1312
1313 IF g_debug = 1 THEN
1314 print_debug('updated MTLT with NULL secondary quantity: ' || l_progress, 9);
1315 END IF;
1316
1317
1318 IF g_debug = 1 THEN
1319 print_debug('update table with NULL secondary quantity:' || l_progress, 1);
1320 END IF;
1321
1322 EXCEPTION
1323 WHEN OTHERS THEN
1324 IF g_debug = 1 THEN
1325 print_debug('UPDATE mtl_transaction_lots_temp with null lot secondary quantity failed for a new lot'|| l_progress, 1);
1326 END IF;
1327 RAISE g_exc_unexpected_error;
1328 END;
1329
1330 END IF; ------ IF p_line_secondary_quantity IS NOT NULL THEN
1331
1332 print_debug('End of the program inv_roi_integration_grp.inv_validate_lot. Program has completed successfully '|| l_progress, 1);
1333
1334 END IF; ------ IF p_new_lot= 'N' THEN
1335 -------end of validation for a new lot. Start Exception section --------------------------------------
1336
1337 EXCEPTION
1338
1339 WHEN NO_DATA_FOUND THEN
1340 x_return_status := fnd_api.g_ret_sts_error;
1341 fnd_msg_pub.count_and_get(
1342 p_encoded => fnd_api.g_false ,
1343 p_count => x_msg_count ,
1344 p_data => x_msg_data
1345 );
1346 IF( x_msg_count > 1 ) THEN
1347 x_msg_data := fnd_msg_pub.get(
1348 x_msg_count ,
1349 FND_API.G_FALSE
1350 );
1351 END IF ;
1352 IF g_debug = 1 THEN
1353 print_debug('Exitting INV_VALIDATE_LOT - No data found error:'||TO_CHAR(SYSDATE, 'YYYY-MM-DD HH:DD:SS')||':'||l_progress, 1);
1354 -----print_stacked_messages;
1355 END IF;
1356
1357 WHEN INVALID_ITEM THEN
1358 FND_MESSAGE.SET_NAME('INV','INV_INVALID_ITEM');
1359 FND_MSG_PUB.ADD;
1360 x_return_status := fnd_api.g_ret_sts_error;
1361 fnd_msg_pub.count_and_get(
1362 p_encoded => fnd_api.g_false ,
1363 p_count => x_msg_count ,
1364 p_data => x_msg_data
1365 );
1366 IF( x_msg_count > 1 ) THEN
1367 x_msg_data := fnd_msg_pub.get(
1368 x_msg_count ,
1369 FND_API.G_FALSE
1370 );
1371 END IF ;
1372
1373 IF g_debug = 1 THEN
1374 print_debug('Exitting INV_VALIDATE_LOT - Invalid Item:'||TO_CHAR(SYSDATE, 'YYYY-MM-DD HH:DD:SS')||':'||l_progress, 1);
1375
1376 END IF;
1377
1378
1379 WHEN g_exc_error THEN
1380 x_return_status := fnd_api.g_ret_sts_error;
1381 fnd_msg_pub.count_and_get(
1382 p_encoded => fnd_api.g_false ,
1383 p_count => x_msg_count ,
1384 p_data => x_msg_data
1385 );
1386 IF( x_msg_count > 1 ) THEN
1387 x_msg_data := fnd_msg_pub.get(
1388 x_msg_count ,
1389 FND_API.G_FALSE
1390 );
1391 END IF;
1392
1393 IF g_debug = 1 THEN
1394 print_debug('Exitting INV_VALIDATE_LOT - g_exc_error error:'||TO_CHAR(SYSDATE, 'YYYY-MM-DD HH:DD:SS')||':'||l_progress, 1);
1395 -----print_stacked_messages;
1396 END IF;
1397
1398
1399 WHEN g_exc_unexpected_error THEN
1400 x_return_status := fnd_api.g_ret_sts_unexp_error;
1401 fnd_msg_pub.count_and_get(
1402 p_encoded => fnd_api.g_false ,
1403 p_count => x_msg_count ,
1404 p_data => x_msg_data
1405 );
1406 IF( x_msg_count > 1 ) THEN
1407 x_msg_data := fnd_msg_pub.get(
1408 x_msg_count ,
1409 FND_API.G_FALSE
1410 );
1411 END IF ;
1412
1413 IF g_debug = 1 THEN
1414 print_debug('Exitting INV_VALIDATE_LOT - g_exc_unexpected_error error:'||TO_CHAR(SYSDATE, 'YYYY-MM-DD HH:DD:SS')||':'||l_progress, 1);
1415 --------print_stacked_messages;
1416 END IF;
1417
1418 WHEN OTHERS THEN
1419 x_return_status := fnd_api.g_ret_sts_unexp_error;
1420 fnd_msg_pub.count_and_get(
1421 p_encoded => fnd_api.g_false ,
1422 p_count => x_msg_count ,
1423 p_data => x_msg_data
1424 );
1425 IF( x_msg_count > 1 ) THEN
1426 x_msg_data := fnd_msg_pub.get(
1427 x_msg_count ,
1428 FND_API.G_FALSE);
1429 END IF;
1430
1431 IF g_debug = 1 THEN
1432 print_debug('Exitting INV_VALIDATE_LOT - In others error:'||TO_CHAR(SYSDATE, 'YYYY-MM-DD HH:DD:SS')||':'||l_progress, 1);
1433 --------print_stacked_messages;
1434 END IF;
1435
1436
1437 END INV_VALIDATE_LOT;
1438
1439
1440
1441 /*##########################################################################
1442 #
1443 # PROCEDURE INV_New_lot
1444 #
1445 # DESCRIPTION :-
1446 #
1447 # If the shipped lot is new for destination org , then its attributes will be
1448 # picked up from that of shipping org and NOT from the item master of the
1449 # destination org. This shall be done for 'Receive ' transactions and +ve corrections.
1450 #
1451 # Create the new lot and then update MTLT with the new lot attributes.
1452 #
1453 # Create lot specific conversions for :
1454 # Primary UOM and Secondary UOM
1455 #
1456 # DESIGN REFERENCES:
1457 # http://files.oraclecorp.com/content/AllPublic/Workspaces/
1458 # Inventory%20Convergence-Public/Design/Oracle%20Purchasing/TDD/PO_ROI_TDD.zip
1459 #
1460 # MODIFICATION HISTORY
1461 # 10-AUG-2004 Punit Kumar Created
1462 # 01-SEP-2004 Punit Kumar Changed the way l_lot_rec was getting populated
1463 #
1464 #########################################################################*/
1465
1466
1467 PROCEDURE INV_New_lot(
1468 x_return_status OUT NOCOPY VARCHAR2 ,
1469 x_msg_count OUT NOCOPY NUMBER ,
1470 x_msg_data OUT NOCOPY VARCHAR2 ,
1471 p_api_version IN NUMBER DEFAULT 1.0 ,
1472 p_init_msg_lst IN VARCHAR2 := FND_API.G_FALSE ,
1473 p_source_document_code IN VARCHAR2 ,
1474 p_item_id IN NUMBER ,
1475 p_from_organization_id IN NUMBER ,
1476 p_to_organization_id IN NUMBER ,
1477 p_lot_number IN VARCHAR2 ,
1478 p_lot_quantity IN NUMBER ,
1479 p_lot_secondary_quantity IN NUMBER ,
1480 p_line_secondary_quantity IN NUMBER ,
1481 p_primary_unit_of_measure IN VARCHAR2 ,
1482 p_secondary_unit_of_measure IN VARCHAR2 ,
1483 p_uom_code IN VARCHAR2 ,
1484 p_secondary_uom_code IN VARCHAR2 ,
1485 p_reason_id IN NUMBER ,
1486 P_MLN_REC IN mtl_lot_numbers%ROWTYPE ,
1487 p_mtlt_rowid IN ROWID
1488 )
1489
1490 IS
1491 /* copy all values from mtl_lot_numbers for inter org transfer */
1492 CURSOR C_MLN (l_lot_number VARCHAR2,
1493 l_item_id NUMBER,
1494 l_from_organization_id NUMBER
1495 ) IS
1496 SELECT *
1497 FROM mtl_lot_numbers
1498 WHERE lot_number = l_lot_number
1499 AND inventory_item_id=l_item_id
1500 AND organization_id = l_from_organization_id;
1501
1502 ---local variables declaration
1503 l_lot_rec C_MLN%ROWTYPE ;
1504 L_MLN_REC mtl_lot_numbers%ROWTYPE ;
1505 x_lot_rec MTL_LOT_NUMBERS%ROWTYPE ;
1506 p_lot_uom_conv_rec mtl_lot_uom_class_conversions%ROWTYPE ;
1507 l_source NUMBER ;
1508 l_permission_value NUMBER ;
1509 l_conv_info_exists NUMBER ;
1510 l_grade_controlled_flag VARCHAR2(1) ;
1511 l_qty_update_tbl MTL_LOT_UOM_CONV_PUB.quantity_update_rec_type ;
1512 l_primary_uom_class VARCHAR2(10) ;
1513 l_secondary_uom_class VARCHAR2(10) ;
1514 l_api_name VARCHAR2(30) := 'INV_New_lot' ;
1515 l_api_version CONSTANT NUMBER := 1.0 ;
1516 l_progress VARCHAR2(3) := '000' ;
1517 l_return_status VARCHAR2(1) ;
1518 l_msg_data VARCHAR2(3000) ;
1519 l_msg_count NUMBER ;
1520 l_row_id ROWID ;
1521 l_secondary_default_ind VARCHAR2(30) ;
1522 l_sequence NUMBER :=NULL ;
1523 l_from_lot_ctrl NUMBER ; --bug10410657
1524
1525
1526 BEGIN
1527
1528 l_progress := '024';
1529
1530 -- Standard call to check for call compatibility.
1531 IF NOT fnd_api.compatible_api_call(
1532 l_api_version ,
1533 p_api_version ,
1534 l_api_name ,
1535 'INV_ROI_INTEGRATION_GRP'
1536 ) THEN
1537 IF (g_debug = 1) THEN
1538 print_debug('FND_API not compatible INV_ROI_INTEGRATION_GRP.INV_New_lot'|| l_progress, 1);
1539 END IF;
1540 RAISE fnd_api.g_exc_unexpected_error;
1541 END IF;
1542
1543 /* Initialize message list if p_init_msg_list is set to TRUE. */
1544 IF fnd_api.to_boolean(p_init_msg_lst) THEN
1545 fnd_msg_pub.initialize;
1546 END IF;
1547
1548 /*Initialize the return status */
1549 x_return_status := FND_API.G_RET_STS_SUCCESS;
1550
1551 /* Checking for Inter-Org transfer */
1552
1553 IF p_source_document_code IN ( 'INVENTORY','REQ') THEN
1554 /* It is an Inter-Org Transfers.
1555 Default the lot attributes from shipping lot.
1556 Check if the item is grade controlled in the destination org. */
1557
1558 l_progress := '025';
1559 /*SAVEPOINT inv_new_lot_save2; */
1560
1561 BEGIN
1562 SELECT GRADE_CONTROL_FLAG
1563 INTO l_grade_controlled_flag
1564 FROM mtl_system_items_b
1565 WHERE INVENTORY_ITEM_ID = p_item_id
1566 AND ORGANIZATION_ID = p_to_organization_id;
1567
1568 IF g_debug = 1 THEN
1569 print_debug('p_source_document_code is :' || p_source_document_code|| ':' || l_progress, 1);
1570 print_debug('It is an Inter-Org Transfers.Defaulting the lot attributes from shipping lot. :' || l_progress, 1);
1571 print_debug('grade controlled flag in destination org is :' || l_grade_controlled_flag|| ':' || l_progress, 1);
1572 END IF;
1573
1574 EXCEPTION
1575 WHEN OTHERS THEN
1576 IF g_debug = 1 THEN
1577 print_debug('inv_new_lot::SELECT GRADE_CONTROL_FLAG has failed with a Unexpected exception'|| l_progress, 1);
1578 END IF;
1579 RAISE g_exc_unexpected_error;
1580 END;
1581
1582
1583 IF g_debug = 1 THEN
1584 print_debug('p_lot_number:' || p_lot_number, 1);
1585 print_debug('p_item_id:' || p_item_id, 1);
1586 print_debug('p_from_organization_id:' || p_from_organization_id, 1);
1587 END IF;
1588
1589 IF p_lot_number IS NULL OR p_item_id IS NULL OR p_from_organization_id IS NULL THEN
1590 IF g_debug = 1 THEN
1591 print_debug('Any of these 3 parameters as printed above cannot be null:', 1);
1592 END IF;
1593 RAISE g_exc_unexpected_error;
1594 END IF;
1595
1596 ----Populate l_lot_rec (MTL_LOT_NUMBERS%ROWTYPE ) as follows:
1597 BEGIN
1598
1599 OPEN C_MLN(p_lot_number,
1600 p_item_id,
1601 p_from_organization_id);
1602 FETCH C_MLN INTO l_lot_rec;
1603
1604 l_progress :='026';
1605
1606 /* If the lot in destination org for receive/deliver trx is already existing in the
1607 shipping org then the code works works fine.
1608 However if the lot is also new in the shipping org then the above cursor will fail as
1609 mtl_lot-numbers will not have any data.
1610 In this case erroring out as we cannot do an Inter Org transfer from an org with a new lot.
1611 We can only transfer in an existing lot.
1612 Thsi lot can nevertheless be be new in the destination org.
1613
1614 bug10410657 if no datafound, item could be not lot controlled in source org
1615 previously this condition was commented out per above statement.
1616 added logic, error out only if item is lot controlled in source org as well.
1617 if item not lot controlled in source, call INV_LOT_API_PKG.Populate_Lot_Records
1618 to default lot attribute.
1619 */
1620
1621 --/*
1622 IF C_MLN%NOTFOUND THEN
1623
1624 begin
1625 select msi.lot_control_code
1626 into l_from_lot_ctrl
1627 from mtl_system_items msi
1628 where msi.inventory_item_id = p_item_id
1629 and msi.organization_id = p_from_organization_id;
1630 exception
1631 when no_data_found then
1632 if g_debug = 1 then
1633 print_debug('inv_new_lot:Error get lot control in source organization:' || l_progress, 1);
1634 end if;
1635 CLOSE C_MLN;
1636 RAISE g_exc_unexpected_error;
1637 end;
1638
1639 if l_from_lot_ctrl = 2 then
1640 IF g_debug = 1 THEN
1641 print_debug('Inter-Org Xfr::the lot is also new in the shipping org:' || l_progress, 1);
1642 print_debug('Erroring Out as we cannot do an Inter Org transfer from an org with a new lot:' || l_progress, 1);
1643 print_debug('inv_new_lot:We can only transfer in an existing lot:' || l_progress, 1);
1644 END IF;
1645 CLOSE C_MLN;
1646 RAISE g_exc_unexpected_error;
1647
1648 else
1649 --in RG P_MLN_REC does not have item, org, and origination_date populated. if origination_date null, exp_date won't be calculated
1650 L_MLN_REC :=P_MLN_REC;
1651 L_MLN_REC.INVENTORY_ITEM_ID := p_item_id;
1652 L_MLN_REC.ORGANIZATION_ID := p_to_organization_id;
1653 L_MLN_REC.origination_date := sysdate;
1654
1655 if g_debug = 1 then
1656 print_debug('inv_new_lot: Calling INV_LOT_API_PKG.Populate_Lot_Records.', 9);
1657 end if;
1658
1659 INV_LOT_API_PKG.Populate_Lot_Records(
1660 p_lot_rec => L_MLN_REC
1661 , p_copy_lot_attribute_flag => 'N'
1662 , p_source => l_source
1663 , p_api_version => l_api_version
1664 , p_init_msg_list => p_init_msg_lst
1665 , p_commit => fnd_api.g_false
1666 , x_child_lot_rec => l_lot_rec
1667 , x_return_status => l_return_status
1668 , x_msg_count => l_msg_count
1669 , x_msg_data => l_msg_data
1670 );
1671 if g_debug = 1 then
1672 print_debug('Program IINV_LOT_API_PKG.Populate_Lot_Records return ' || x_return_status, 9);
1673 end if;
1674 if l_return_status = fnd_api.g_ret_sts_error then
1675 if g_debug = 1 then
1676 print_debug('Program IINV_LOT_API_PKG.Populate_Lot_Records failed with a user defined exception', 9);
1677 end if;
1678 RAISE g_exc_unexpected_error;
1679 elsif l_return_status = fnd_api.g_ret_sts_unexp_error then
1680 if g_debug = 1 then
1681 print_debug('Program INV_LOT_API_PKG.POPULATE_LOT_RECORDS has failed with a Unexpected exception', 9);
1682 end if;
1683 RAISE g_exc_unexpected_error;
1684 end if;
1685
1686 end if;
1687
1688 ELSE
1689 --*/
1690 IF g_debug = 1 THEN
1691 print_debug('inv_new_lot::fetched all records from mtl_lot_numbers(shipping org values)
1692 for an Inter Org Transfer:' || l_progress, 1);
1693 END IF;
1694 CLOSE C_MLN;
1695 END IF;
1696 --bug10410657
1697
1698
1699 /*override the from_organization_id to to_organization_id and the dates*/
1700 l_lot_rec.organization_id := p_to_organization_id ;
1701 l_lot_rec.creation_date := SYSDATE;
1702 l_lot_rec.last_update_date :=SYSDATE;
1703
1704 /*if the item is not grade controlled in the destination org then nullify the grade code*/
1705 IF l_grade_controlled_flag <> 'Y' THEN
1706 l_lot_rec.grade_code := NULL;
1707 IF g_debug = 1 THEN
1708 print_debug('item is not grade controlled in the destination org so
1709 nullifying the grade code:' || l_lot_rec.grade_code, 1);
1710 END IF;
1711 END IF;
1712
1713 EXCEPTION
1714 WHEN OTHERS THEN
1715 IF g_debug = 1 THEN
1716 print_debug('Populating p_lot_rec with mtl_lot_number (shipping org values) has
1717 failed with a Unexpected exception in INV_ROI_INTEGRATION_GRP.INV_NEW_LOT'|| l_progress, 1);
1718 END IF;
1719 RAISE g_exc_unexpected_error;
1720 END;
1721
1722 ELSE ---------IF p_source_document_code IN ( 'INVENTORY','REQ') THEN
1723
1724 /*Not an Inter-Org Transfers.
1725 Default the lot attributes from destination item master.
1726 Populate p_lot_rec (MTL_LOT_NUMBERS%ROWTYPE ) with the lot record coming from validate_lot_serial_info.
1727 All these parameters are to be used in the serias of API calls starting Create_Inv_Lot */
1728
1729 l_lot_rec := P_MLN_REC ;
1730
1731 /* p_mln_rec is coming from validate_lot_serial_info and is already populated
1732 with MTLT vaules.Assign some of the missing attributes here*/
1733
1734 l_lot_rec.INVENTORY_ITEM_ID := p_item_id ;
1735 l_lot_rec.ORGANIZATION_ID := p_to_organization_id ;
1736
1737 IF g_debug = 1 THEN
1738 print_debug('inv_new_lot::fetched all records from destination org:' || l_progress, 1);
1739 END IF;
1740
1741
1742
1743 END IF;---------IF p_source_document_code IN ( 'INVENTORY','REQ') THEN
1744
1745 l_progress := '027';
1746
1747 IF g_debug = 1 THEN
1748 print_debug('Printing the values of p_lot_rec before calling inv_lot_api_pub.Create_Inv_lot '|| l_progress, 1);
1749 print_debug('l_lot_rec.INVENTORY_ITEM_ID: '|| l_lot_rec.INVENTORY_ITEM_ID||':'||l_progress, 1);
1750 print_debug('l_lot_rec.ORGANIZATION_ID '|| l_lot_rec.ORGANIZATION_ID||':'||l_progress, 1);
1751 print_debug('l_lot_rec.LOT_NUMBER :'|| l_lot_rec.LOT_NUMBER ||':'|| l_progress, 1);
1752 print_debug('l_lot_rec.PARENT_LOT_NUMBER :'|| l_lot_rec.PARENT_LOT_NUMBER ||':'|| l_progress, 1);
1753 print_debug('l_lot_rec.LAST_UPDATE_DATE:'|| l_lot_rec.LAST_UPDATE_DATE||':'|| l_progress, 1);
1754 print_debug('l_lot_rec.LAST_UPDATED_BY:'|| l_lot_rec.LAST_UPDATED_BY||':'|| l_progress, 1);
1755 print_debug('l_lot_rec.CREATION_DATE:'|| l_lot_rec.CREATION_DATE||':'|| l_progress, 1);
1756 print_debug('l_lot_rec.CREATED_BY:'|| l_lot_rec.CREATED_BY||':'|| l_progress, 1);
1757 print_debug('l_lot_rec.LAST_UPDATE_LOGIN:'||l_lot_rec.LAST_UPDATE_LOGIN ||':'|| l_progress, 1);
1758 print_debug('l_lot_rec.EXPIRATION_DATE:'|| l_lot_rec.EXPIRATION_DATE ||':'|| l_progress, 1);
1759 print_debug('l_lot_rec.ATTRIBUTE_CATEGORY:'|| l_lot_rec.ATTRIBUTE_CATEGORY ||':'|| l_progress, 1);
1760 print_debug('l_lot_rec.REQUEST_ID:'|| l_lot_rec.REQUEST_ID||':'|| l_progress, 1);
1761 print_debug('l_lot_rec.PROGRAM_APPLICATION_ID:'|| l_lot_rec.PROGRAM_APPLICATION_ID ||':'|| l_progress, 1);
1762 print_debug('l_lot_rec.PROGRAM_ID:'|| l_lot_rec.PROGRAM_ID ||':'|| l_progress, 1);
1763 print_debug('l_lot_rec.PROGRAM_UPDATE_DATE:'|| l_lot_rec.PROGRAM_UPDATE_DATE ||':'|| l_progress, 1);
1764 print_debug('l_lot_rec.DESCRIPTION:'|| l_lot_rec.DESCRIPTION ||':'|| l_progress, 1);
1765 print_debug('l_lot_rec.VENDOR_NAME:'|| l_lot_rec.VENDOR_NAME ||':'|| l_progress, 1);
1766 print_debug('l_lot_rec.SUPPLIER_LOT_NUMBER :'|| l_lot_rec.SUPPLIER_LOT_NUMBER ||':'|| l_progress, 1);
1767 print_debug('l_lot_rec.GRADE_CODE:'|| l_lot_rec.GRADE_CODE ||':'|| l_progress, 1);
1768 print_debug('l_lot_rec.ORIGINATION_DATE:'|| l_lot_rec.ORIGINATION_DATE ||':'|| l_progress, 1);
1769 print_debug('l_lot_rec.DATE_CODE:'|| l_lot_rec.DATE_CODE ||':'|| l_progress, 1);
1770 print_debug('l_lot_rec.STATUS_ID :'|| l_lot_rec.STATUS_ID ||':'|| l_progress, 1);
1771 print_debug('l_lot_rec.CHANGE_DATE :'|| l_lot_rec.CHANGE_DATE ||':'|| l_progress, 1);
1772 print_debug('l_lot_rec.AGE:'|| l_lot_rec.AGE ||':'|| l_progress, 1);
1773 print_debug('l_lot_rec.RETEST_DATE :'|| l_lot_rec.RETEST_DATE ||':'|| l_progress, 1);
1774 print_debug('l_lot_rec.MATURITY_DATE :'|| l_lot_rec.MATURITY_DATE ||':'|| l_progress, 1);
1775 print_debug('l_lot_rec.LOT_ATTRIBUTE_CATEGORY:'|| l_lot_rec.LOT_ATTRIBUTE_CATEGORY ||':'|| l_progress, 1);
1776 print_debug('l_lot_rec.ITEM_SIZE :'|| l_lot_rec.ITEM_SIZE ||':'|| l_progress, 1);
1777 print_debug('l_lot_rec.COLOR :'|| l_lot_rec.COLOR ||':'|| l_progress, 1);
1778 print_debug('l_lot_rec.VOLUME :'|| l_lot_rec.VOLUME ||':'|| l_progress, 1);
1779 print_debug('l_lot_rec.VOLUME_UOM :'|| l_lot_rec.VOLUME_UOM ||':'|| l_progress, 1);
1780 print_debug('l_lot_rec.PLACE_OF_ORIGIN :'|| l_lot_rec.PLACE_OF_ORIGIN ||':'|| l_progress, 1);
1781 print_debug('l_lot_rec.BEST_BY_DATE :'|| l_lot_rec.BEST_BY_DATE ||':'|| l_progress, 1);
1782 print_debug('l_lot_rec.LENGTH :'|| l_lot_rec.LENGTH ||':'|| l_progress, 1);
1783 print_debug('l_lot_rec.LENGTH_UOM :'|| l_lot_rec.LENGTH_UOM ||':'|| l_progress, 1);
1784 print_debug('l_lot_rec.RECYCLED_CONTENT:'|| l_lot_rec.RECYCLED_CONTENT ||':'|| l_progress, 1);
1785 print_debug('l_lot_rec.THICKNESS :'|| l_lot_rec.THICKNESS ||':'|| l_progress, 1);
1786 print_debug('l_lot_rec.THICKNESS_UOM :'|| l_lot_rec.THICKNESS_UOM ||':'|| l_progress, 1);
1787 print_debug('l_lot_rec.WIDTH:'|| l_lot_rec.WIDTH ||':'|| l_progress, 1);
1788 print_debug('l_lot_rec.WIDTH_UOM :'|| l_lot_rec.WIDTH_UOM ||':'|| l_progress, 1);
1789 print_debug('l_lot_rec.CURL_WRINKLE_FOLD :'||l_lot_rec.CURL_WRINKLE_FOLD ||':'|| l_progress, 1);
1790 print_debug('l_lot_rec.VENDOR_ID:'||l_lot_rec.VENDOR_ID ||':'|| l_progress, 1);
1791 print_debug('l_lot_rec.TERRITORY_CODE :'|| l_lot_rec.TERRITORY_CODE ||':'|| l_progress, 1);
1792 print_debug('l_lot_rec.ORIGINATION_TYPE :'|| l_lot_rec.ORIGINATION_TYPE ||':'|| l_progress, 1);
1793 print_debug('l_lot_rec.EXPIRATION_ACTION_DATE:'|| l_lot_rec.EXPIRATION_ACTION_DATE ||':'|| l_progress, 1);
1794 print_debug('l_lot_rec.EXPIRATION_ACTION_CODE:'|| l_lot_rec.EXPIRATION_ACTION_CODE ||':'|| l_progress, 1);
1795 print_debug('l_lot_rec.HOLD_DATE :'||l_lot_rec.HOLD_DATE ||':'|| l_progress, 1);
1796 END IF;
1797
1798 l_progress := '271';
1799
1800 /* Call Lot Create API INV_LOT_API_PUB.CREATE_INV_LOT to create the new lot.
1801 This shall also validate the attributes before creating the new Lot. */
1802
1803 inv_lot_api_pub.Create_Inv_lot(
1804 x_return_status => l_return_status ,
1805 x_msg_count => l_msg_count ,
1806 x_msg_data => l_msg_data ,
1807 x_row_id => l_row_id ,
1808 x_lot_rec => x_lot_rec ,
1809 p_lot_rec => l_lot_rec ,
1810 p_source => l_source ,
1811 p_api_version => l_api_version ,
1812 p_init_msg_list => fnd_api.g_false ,
1813 p_commit => fnd_api.g_false ,
1814 p_validation_level => fnd_api.g_valid_level_full ,
1815 p_origin_txn_id => p_to_organization_id
1816 );
1817 l_progress := '272';
1818
1819 IF g_debug = 1 THEN
1820 print_debug('Program inv_lot_api_pub.Create_Inv_lot return ' || l_return_status || ':' || l_progress, 1);
1821 print_debug('Printing the values of x_lot_rec after calling inv_lot_api_pub.Create_Inv_lot '|| l_progress, 1);
1822 print_debug('x_lot_rec.INVENTORY_ITEM_ID: '|| x_lot_rec.INVENTORY_ITEM_ID||':'||l_progress, 1);
1823 print_debug('x_lot_rec.ORGANIZATION_ID '|| x_lot_rec.ORGANIZATION_ID||':'||l_progress, 1);
1824 print_debug('x_lot_rec.LOT_NUMBER :'|| x_lot_rec.LOT_NUMBER ||':'|| l_progress, 1);
1825 print_debug('x_lot_rec.LAST_UPDATE_DATE:'|| x_lot_rec.LAST_UPDATE_DATE||':'|| l_progress, 1);
1826 print_debug('x_lot_rec.LAST_UPDATED_BY:'|| x_lot_rec.LAST_UPDATED_BY||':'|| l_progress, 1);
1827 print_debug('x_lot_rec.CREATION_DATE:'|| x_lot_rec.CREATION_DATE||':'|| l_progress, 1);
1828 print_debug('x_lot_rec.CREATED_BY:'|| x_lot_rec.CREATED_BY||':'|| l_progress, 1);
1829 print_debug('x_lot_rec.LAST_UPDATE_LOGIN:'||x_lot_rec.LAST_UPDATE_LOGIN ||':'|| l_progress, 1);
1830 print_debug('x_lot_rec.EXPIRATION_DATE:'|| x_lot_rec.EXPIRATION_DATE ||':'|| l_progress, 1);
1831 print_debug('x_lot_rec.ATTRIBUTE_CATEGORY:'|| x_lot_rec.ATTRIBUTE_CATEGORY ||':'|| l_progress, 1);
1832 print_debug('x_lot_rec.REQUEST_ID:'|| x_lot_rec.REQUEST_ID||':'|| l_progress, 1);
1833 print_debug('x_lot_rec.PROGRAM_APPLICATION_ID:'|| x_lot_rec.PROGRAM_APPLICATION_ID ||':'|| l_progress, 1);
1834 print_debug('x_lot_rec.PROGRAM_ID:'|| x_lot_rec.PROGRAM_ID ||':'|| l_progress, 1);
1835 print_debug('x_lot_rec.PROGRAM_UPDATE_DATE:'|| x_lot_rec.PROGRAM_UPDATE_DATE ||':'|| l_progress, 1);
1836 print_debug('x_lot_rec.DESCRIPTION:'|| x_lot_rec.DESCRIPTION ||':'|| l_progress, 1);
1837 print_debug('x_lot_rec.VENDOR_NAME:'|| x_lot_rec.VENDOR_NAME ||':'|| l_progress, 1);
1838 print_debug('x_lot_rec.SUPPLIER_LOT_NUMBER :'|| x_lot_rec.SUPPLIER_LOT_NUMBER ||':'|| l_progress, 1);
1839 print_debug('x_lot_rec.GRADE_CODE:'|| x_lot_rec.GRADE_CODE ||':'|| l_progress, 1);
1840 print_debug('x_lot_rec.ORIGINATION_DATE:'|| x_lot_rec.ORIGINATION_DATE ||':'|| l_progress, 1);
1841 print_debug('x_lot_rec.DATE_CODE:'|| x_lot_rec.DATE_CODE ||':'|| l_progress, 1);
1842 print_debug('x_lot_rec.STATUS_ID :'|| x_lot_rec.STATUS_ID ||':'|| l_progress, 1);
1843 print_debug('x_lot_rec.CHANGE_DATE :'|| x_lot_rec.CHANGE_DATE ||':'|| l_progress, 1);
1844 print_debug('x_lot_rec.AGE:'|| x_lot_rec.AGE ||':'|| l_progress, 1);
1845 print_debug('x_lot_rec.RETEST_DATE :'|| x_lot_rec.RETEST_DATE ||':'|| l_progress, 1);
1846 print_debug('x_lot_rec.MATURITY_DATE :'|| x_lot_rec.MATURITY_DATE ||':'|| l_progress, 1);
1847 print_debug('x_lot_rec.LOT_ATTRIBUTE_CATEGORY:'|| x_lot_rec.LOT_ATTRIBUTE_CATEGORY ||':'|| l_progress, 1);
1848 print_debug('x_lot_rec.ITEM_SIZE :'|| x_lot_rec.ITEM_SIZE ||':'|| l_progress, 1);
1849 print_debug('x_lot_rec.COLOR :'|| x_lot_rec.COLOR ||':'|| l_progress, 1);
1850 print_debug('x_lot_rec.VOLUME :'|| x_lot_rec.VOLUME ||':'|| l_progress, 1);
1851 print_debug('x_lot_rec.VOLUME_UOM :'|| x_lot_rec.VOLUME_UOM ||':'|| l_progress, 1);
1852 print_debug('x_lot_rec.PLACE_OF_ORIGIN :'|| x_lot_rec.PLACE_OF_ORIGIN ||':'|| l_progress, 1);
1853 print_debug('x_lot_rec.BEST_BY_DATE :'|| x_lot_rec.BEST_BY_DATE ||':'|| l_progress, 1);
1854 print_debug('x_lot_rec.LENGTH :'|| x_lot_rec.LENGTH ||':'|| l_progress, 1);
1855 print_debug('x_lot_rec.LENGTH_UOM :'|| x_lot_rec.LENGTH_UOM ||':'|| l_progress, 1);
1856 print_debug('x_lot_rec.RECYCLED_CONTENT:'|| x_lot_rec.RECYCLED_CONTENT ||':'|| l_progress, 1);
1857 print_debug('x_lot_rec.THICKNESS :'|| x_lot_rec.THICKNESS ||':'|| l_progress, 1);
1858 print_debug('x_lot_rec.THICKNESS_UOM :'|| x_lot_rec.THICKNESS_UOM ||':'|| l_progress, 1);
1859 print_debug('x_lot_rec.WIDTH:'|| x_lot_rec.WIDTH ||':'|| l_progress, 1);
1860 print_debug('x_lot_rec.WIDTH_UOM :'|| x_lot_rec.WIDTH_UOM ||':'|| l_progress, 1);
1861 print_debug('x_lot_rec.CURL_WRINKLE_FOLD :'||x_lot_rec.CURL_WRINKLE_FOLD ||':'|| l_progress, 1);
1862 print_debug('x_lot_rec.VENDOR_ID:'||x_lot_rec.VENDOR_ID ||':'|| l_progress, 1);
1863 print_debug('x_lot_rec.TERRITORY_CODE :'|| x_lot_rec.TERRITORY_CODE ||':'|| l_progress, 1);
1864 print_debug('x_lot_rec.PARENT_LOT_NUMBER :'|| x_lot_rec.PARENT_LOT_NUMBER ||':'|| l_progress, 1);
1865 print_debug('x_lot_rec.ORIGINATION_TYPE :'|| x_lot_rec.ORIGINATION_TYPE ||':'|| l_progress, 1);
1866 print_debug('x_lot_rec.EXPIRATION_ACTION_DATE:'|| x_lot_rec.EXPIRATION_ACTION_DATE ||':'|| l_progress, 1);
1867 print_debug('x_lot_rec.EXPIRATION_ACTION_CODE:'|| x_lot_rec.EXPIRATION_ACTION_CODE ||':'|| l_progress, 1);
1868 print_debug('x_lot_rec.HOLD_DATE :'||x_lot_rec.HOLD_DATE ||':'|| l_progress, 1);
1869 END IF;
1870
1871 IF l_return_status = fnd_api.g_ret_sts_error THEN
1872 IF g_debug = 1 THEN
1873 print_debug('Program inv_lot_api_pub.Create_Inv_lot has failed with a user defined exception : ' || l_progress, 9);
1874 END IF;
1875 FND_MESSAGE.SET_NAME('INV','INV_PROGRAM_ERROR');
1876 FND_MESSAGE.SET_TOKEN('PGM_NAME','inv_lot_api_pub.Create_Inv_lot');
1877 fnd_msg_pub.ADD;
1878 RAISE g_exc_error;
1879
1880 l_progress := '028' ;
1881 ELSIF l_return_status = fnd_api.g_ret_sts_unexp_error THEN
1882 IF g_debug = 1 THEN
1883 print_debug('Program inv_lot_api_pub.Create_Inv_lot has failed with a Unexpected exception :'|| l_progress, 9);
1884 END IF;
1885 FND_MESSAGE.SET_NAME('INV','INV_PROGRAM_ERROR');
1886 FND_MESSAGE.SET_TOKEN('PGM_NAME','inv_lot_api_pub.Create_Inv_lot');
1887 fnd_msg_pub.ADD;
1888 RAISE g_exc_unexpected_error;
1889 END IF;
1890
1891 l_progress := '029';
1892
1893 ----Update MTLT with the out record type parameter x_lot_rec
1894 BEGIN
1895
1896 UPDATE mtl_transaction_lots_temp
1897 SET
1898 lot_expiration_date = x_lot_rec.expiration_date ,
1899 attribute_category = x_lot_rec.attribute_category ,
1900 lot_attribute_category = x_lot_rec.lot_attribute_category ,
1901 grade_code = x_lot_rec.grade_code ,
1902 origination_date = x_lot_rec.origination_date ,
1903 date_code = x_lot_rec.date_code ,
1904 status_id = x_lot_rec.status_id ,
1905 change_date = x_lot_rec.change_date ,
1906 age = x_lot_rec.age ,
1907 retest_date = x_lot_rec.retest_date ,
1908 maturity_date = x_lot_rec.maturity_date ,
1909 item_size = x_lot_rec.item_size ,
1910 color = x_lot_rec.color ,
1911 volume = x_lot_rec.volume ,
1912 volume_uom = x_lot_rec.volume_uom ,
1913 place_of_origin = x_lot_rec.place_of_origin ,
1914 best_by_date = x_lot_rec.best_by_date ,
1915 LENGTH = x_lot_rec.LENGTH ,
1916 length_uom = x_lot_rec.length_uom ,
1917 recycled_content = x_lot_rec.recycled_content ,
1918 thickness = x_lot_rec.thickness ,
1919 thickness_uom = x_lot_rec.thickness_uom ,
1920 width = x_lot_rec.width ,
1921 width_uom = x_lot_rec.width_uom ,
1922 territory_code = x_lot_rec.territory_code ,
1923 supplier_lot_number = x_lot_rec.supplier_lot_number ,
1924 vendor_name = x_lot_rec.vendor_name ,
1925 creation_date = SYSDATE ,
1926 created_by = x_lot_rec.created_by ,
1927 last_update_date = SYSDATE ,
1928 last_updated_by = x_lot_rec.last_updated_by ,
1929 parent_lot_number = x_lot_rec.parent_lot_number ,
1930 origination_type = x_lot_rec.origination_type ,
1931 expiration_action_code = x_lot_rec.expiration_action_code ,
1932 expiration_action_date = x_lot_rec.expiration_action_date ,
1933 hold_date = x_lot_rec.hold_date ,
1934 DESCRIPTION = x_lot_rec.DESCRIPTION ,
1935 CURL_WRINKLE_FOLD = x_lot_rec.CURL_WRINKLE_FOLD ,
1936 VENDOR_ID = x_lot_rec.VENDOR_ID
1937
1938 WHERE ROWID = p_mtlt_rowid ;
1939
1940 /*
1941 Case 1: lot has parent lot:-
1942 a) Diffrent parent lots in an organization can have the same child lot depending
1943 upon the organization_parameter.
1944 b) For a single transaction with multiple lots,rows in MTLT have the same
1945 product_transaction_id.
1946 c) Same lot-parent_lot combination can be repeated in a transaction.
1947
1948 Hence there can be multiple rows in MTLT with the same lot_number and
1949 product_transaction_id.
1950
1951 Case 2: lot doesn't have a parent lot.
1952 a) Same lot can be repeated in a transaction.
1953 b) For a single transaction with multiple lots,rows in MTLT have the same
1954 product_transaction_id.
1955 Hence there can be multiple rows in MTLT with the same lot_number and
1956 product_transaction_id.
1957
1958 Hence we cannot have the combination of lot_number and product_transaction_id to uniqely
1959 identify the row of MTLT for updation.
1960 So we use MTLT.rowid in the where clause above.
1961 */
1962
1963 l_progress := '030';
1964
1965 IF g_debug = 1 THEN
1966 print_debug('Update MTLT with the out record type parameter x_lot_rec:' || l_progress, 1);
1967 END IF;
1968
1969 EXCEPTION
1970 WHEN OTHERS THEN
1971 IF g_debug = 1 THEN
1972 print_debug('Updating MTLT with x_lot_rec(inv_lot_api_pub.Create_Inv_lot) has failed with a Unexpected exception in INV_ROI_INTEGRATION_GRP.INV_NEW_LOT :'|| l_progress, 1);
1973 END IF;
1974 RAISE g_exc_unexpected_error;
1975 END;
1976
1977 --------The records from mtl_transaction_lots_temp finally enter mtl_lot_transactions.
1978 l_progress := '031';
1979
1980 -------Create Lot specific conversion
1981
1982 IF g_debug = 1 THEN
1983 print_debug('p_line_secondary_quantity:' || p_line_secondary_quantity, 1);
1984 print_debug('P_PRIMARY_UNIT_OF_MEASURE:' || P_PRIMARY_UNIT_OF_MEASURE, 1);
1985 print_debug('P_SECONDARY_UNIT_OF_MEASURE:' || P_SECONDARY_UNIT_OF_MEASURE, 1);
1986 END IF;
1987
1988
1989 /* Check to see if the item is dual UOM controlled */
1990
1991
1992
1993 --IF p_line_secondary_quantity > 0 AND p_secondary_unit_of_measure IS NOT NULL THEN
1994 IF nvl(p_line_secondary_quantity,0) <> 0 AND p_secondary_unit_of_measure IS NOT NULL THEN -- 7644869 change check as qty could be negative
1995
1996 IF P_PRIMARY_UNIT_OF_MEASURE IS NOT NULL AND P_SECONDARY_UNIT_OF_MEASURE IS NOT NULL THEN
1997
1998 /*For new lots, lot specific conversion may be created depending upon the
1999 permission parameter value.
2000 Fetch permission parameter value from organization parameter for
2001 creating lot specfic conversion.
2002 */
2003
2004 l_progress := '032';
2005
2006
2007 SELECT CREATE_LOT_UOM_CONVERSION
2008 INTO l_permission_value
2009 FROM mtl_parameters
2010 WHERE Organization_id = p_to_organization_id;
2011
2012
2013 /*The values can be as follows:
2014 Yes - 1
2015 No - 2
2016 User Controlled - 3
2017 ('User Controlled' will give a message to the user to confirm the creation
2018 of a lot specific UOM ( For ROI this will behave as 'Yes') )
2019 */
2020
2021 IF g_debug = 1 THEN
2022 print_debug('permission parameter CREATE_LOT_UOM_CONVERSION value from organization parameter for
2023 creating lot specfic conversion:' || l_permission_value||':'||l_progress, 1);
2024 END IF;
2025
2026 IF l_permission_value IS NULL THEN
2027 IF g_debug = 1 THEN
2028 print_debug('l_permission_value is NULL, value = :' ||l_permission_value , 1);
2029 print_debug('defaulting l_permission_value to 1 :', 1);
2030 END IF;
2031 l_permission_value :=1;
2032 END IF;
2033
2034 IF l_permission_value <> 2 THEN
2035
2036 /* Creating lot specific conversion between Primary and Secondary UOM*/
2037
2038 --fetch the uom classes for primary and secondary unit_of_measures
2039 SELECT distinct(uom_class)
2040 INTO l_primary_uom_class
2041 FROM MTL_UNITS_OF_MEASURE
2042 WHERE unit_of_measure = P_PRIMARY_UNIT_OF_MEASURE;
2043
2044 SELECT distinct(uom_class)
2045 INTO l_secondary_uom_class
2046 FROM MTL_UNITS_OF_MEASURE
2047 WHERE unit_of_measure = p_secondary_unit_of_measure;
2048
2049 /*Check whether the unit of measure conversion is not fixed in the destination org.
2050 Fetch the SECONDARY_DEFAULT_IND from mtl_system_items_b. */
2051
2052 SELECT secondary_default_ind
2053 INTO l_secondary_default_ind
2054 FROM mtl_system_items_b
2055 WHERE inventory_item_id =p_item_id
2056 AND organization_id =p_to_organization_id;
2057
2058 /* The values are D => default
2059 F => Fixed
2060 N => Not Fixed
2061 */
2062 l_progress :='033';
2063
2064 IF g_debug = 1 THEN
2065 print_debug('uom classes for primary and secondary unit_of_measures
2066 and SECONDARY_DEFAULT_IND from mtl_system_items_b are:'|| l_primary_uom_class ||':'||l_secondary_uom_class ||':'||l_secondary_default_ind ||':'||l_progress, 1);
2067 END IF;
2068
2069 /* Check whether UOM class are different and the dual uom is not of fixed type. */
2070 IF (l_primary_uom_class <> l_secondary_uom_class) AND (l_secondary_default_ind <>'F') THEN
2071
2072 l_progress := '034';
2073 --SAVEPOINT inv_new_lot_save6;
2074
2075 /* Populate p_lot_uom_conv_rec record type variable as follows:-
2076 All these parameters are to be used in the series of API calls
2077 starting MTL_LOT_UOM_CONV_PUB.create_lot_uom_conversion */
2078
2079 p_lot_uom_conv_rec.LOT_NUMBER := P_LOT_NUMBER ;
2080 p_lot_uom_conv_rec.ORGANIZATION_ID := P_TO_ORGANIZATION_ID ;
2081 p_lot_uom_conv_rec.INVENTORY_ITEM_ID := P_item_id ;
2082 p_lot_uom_conv_rec.FROM_UNIT_OF_MEASURE := p_primary_unit_of_measure ;
2083 p_lot_uom_conv_rec.FROM_UOM_CODE := p_UOM_CODE ;
2084 p_lot_uom_conv_rec.FROM_UOM_CLASS := l_primary_uom_class ;
2085 p_lot_uom_conv_rec.TO_UNIT_OF_MEASURE := p_secondary_unit_of_measure ;
2086 p_lot_uom_conv_rec.TO_UOM_CODE := p_SECONDARY_UOM_CODE ;
2087 p_lot_uom_conv_rec.TO_UOM_CLASS := l_secondary_uom_class ;
2088 p_lot_uom_conv_rec.disable_date := NULL ;
2089 p_lot_uom_conv_rec.conversion_id := NULL ;
2090 p_lot_uom_conv_rec.event_spec_disp_id := NULL ;
2091 p_lot_uom_conv_rec.created_by := fnd_global.user_id ;
2092 p_lot_uom_conv_rec.creation_date := SYSDATE ;
2093 p_lot_uom_conv_rec.last_updated_by := fnd_global.user_id ;
2094 p_lot_uom_conv_rec.last_update_date := SYSDATE ;
2095 p_lot_uom_conv_rec.last_update_login := fnd_global.login_id ;
2096 p_lot_uom_conv_rec.request_id := NULL ;
2097 p_lot_uom_conv_rec.program_application_id := NULL ;
2098 p_lot_uom_conv_rec.program_id := NULL ;
2099 p_lot_uom_conv_rec.program_update_date := NULL ;
2100
2101 /* In some cases (where 'NULL' is passed above, we won't populate any of the above
2102 parameters with actual values. Conversion_id will actually be returned to us.
2103 The others are for very specific cases that we won't hit in this context.
2104 We may just want to call the public version of the API because it handles
2105 all of these situations and will do the appropriate business rule validations.*/
2106
2107 /* Fetching the conversion rate */
2108 /*
2109 INV_CONVERT.inv_um_conversion(
2110 from_unit =>p_lot_uom_conv_rec.FROM_UOM_CODE,
2111 to_unit =>p_lot_uom_conv_rec.TO_UOM_CODE,
2112 item_id =>p_lot_uom_conv_rec.INVENTORY_ITEM_ID,
2113 lot_number =>p_lot_uom_conv_rec.LOT_NUMBER,
2114 organization_id =>p_lot_uom_conv_rec.ORGANIZATION_ID,
2115 uom_rate =>p_lot_uom_conv_rec.CONVERSION_RATE
2116 );
2117 */
2118 /* Calculating the conversion rate by dividing lot secondary quantity with lot transaction
2119 quantity to retain teh lot specific conversion rate which might be diffrent from the
2120 default conversion rate */
2121
2122 p_lot_uom_conv_rec.CONVERSION_RATE := ( p_lot_quantity / p_lot_secondary_quantity ) ;
2123
2124 IF g_debug = 1 THEN
2125 print_debug('p_lot_secondary_quantity:'||p_lot_secondary_quantity, 1);
2126 print_debug('p_lot_quantity:'||p_lot_quantity, 1);
2127 print_debug('uom_rate:'||p_lot_uom_conv_rec.CONVERSION_RATE, 1);
2128 END IF;
2129
2130 IF g_debug = 1 THEN
2131 print_debug('Before calling MTL_LOT_UOM_CONV_PUB.create_lot_uom_conversion:'||l_progress, 1);
2132 print_debug('p_lot_uom_conv_rec.LOT_NUMBER:'||p_lot_uom_conv_rec.LOT_NUMBER, 1);
2133 print_debug('p_lot_uom_conv_rec.ORGANIZATION_ID:'||p_lot_uom_conv_rec.ORGANIZATION_ID, 1);
2134 print_debug('p_lot_uom_conv_rec.INVENTORY_ITEM_ID:'||p_lot_uom_conv_rec.INVENTORY_ITEM_ID, 1);
2135 print_debug('p_lot_uom_conv_rec.FROM_UNIT_OF_MEASURE:'||p_lot_uom_conv_rec.FROM_UNIT_OF_MEASURE, 1);
2136 print_debug('p_lot_uom_conv_rec.FROM_UOM_CODE:'||p_lot_uom_conv_rec.FROM_UOM_CODE, 1);
2137 print_debug('p_lot_uom_conv_rec.FROM_UOM_CLASS:'||p_lot_uom_conv_rec.FROM_UOM_CLASS, 1);
2138 print_debug('p_lot_uom_conv_rec.TO_UNIT_OF_MEASURE:'||p_lot_uom_conv_rec.TO_UNIT_OF_MEASURE, 1);
2139 print_debug('p_lot_uom_conv_rec.TO_UOM_CODE:'||p_lot_uom_conv_rec.TO_UOM_CODE, 1);
2140 print_debug('p_lot_uom_conv_rec.TO_UOM_CLASS:'||p_lot_uom_conv_rec.TO_UOM_CLASS, 1);
2141 print_debug('p_lot_uom_conv_rec.CONVERSION_RATE:'||p_lot_uom_conv_rec.CONVERSION_RATE, 1);
2142 print_debug('p_lot_uom_conv_rec.disable_date:'||p_lot_uom_conv_rec.disable_date, 1);
2143 print_debug('p_lot_uom_conv_rec.conversion_id:'||p_lot_uom_conv_rec.conversion_id, 1);
2144 print_debug('p_lot_uom_conv_rec.event_spec_disp_id:'||p_lot_uom_conv_rec.event_spec_disp_id, 1);
2145 print_debug('p_lot_uom_conv_rec.created_by:'||p_lot_uom_conv_rec.created_by, 1);
2146 print_debug('p_lot_uom_conv_rec.creation_date:'||p_lot_uom_conv_rec.creation_date, 1);
2147 print_debug('p_lot_uom_conv_rec.last_updated_by:'||p_lot_uom_conv_rec.last_updated_by, 1);
2148 print_debug('p_lot_uom_conv_rec.last_update_date:'||p_lot_uom_conv_rec.last_update_date, 1);
2149 print_debug('p_lot_uom_conv_rec.last_update_login:'||p_lot_uom_conv_rec.last_update_login, 1);
2150 print_debug('p_lot_uom_conv_rec.request_id:'||p_lot_uom_conv_rec.request_id, 1);
2151 print_debug('p_lot_uom_conv_rec.program_application_id:'||p_lot_uom_conv_rec.program_application_id, 1);
2152 print_debug('p_lot_uom_conv_rec.program_id :'|| p_lot_uom_conv_rec.program_id , 1);
2153 print_debug('p_lot_uom_conv_rec.program_update_date:'||p_lot_uom_conv_rec.program_update_date, 1);
2154 print_debug('p_reason_id:'||p_reason_id, 1);
2155 --------print_debug('l_qty_update_tbl:'||l_qty_update_tbl, 1);
2156 END IF;
2157
2158
2159 MTL_LOT_UOM_CONV_PUB.create_lot_uom_conversion(
2160 p_api_version =>1.0 ,
2161 p_init_msg_list =>FND_API.G_FALSE ,
2162 p_commit =>FND_API.G_TRUE ,
2163 p_validation_level =>FND_API.G_VALID_LEVEL_FULL ,
2164 p_action_type =>'I' /*Database action type ('I' for insert or 'U' for update)*/ ,
2165 p_update_type_indicator =>5 ,
2166 p_reason_id =>p_reason_id ,
2167 p_batch_id =>NULL /*(Since we are not updating batch quantities)*/ ,
2168 p_process_data =>'Y', -- Bug 4019726 FND_API.G_TRUE ,
2169 p_lot_uom_conv_rec =>p_lot_uom_conv_rec ,
2170 p_qty_update_tbl =>l_qty_update_tbl ,
2171 x_return_status =>l_return_status ,
2172 x_msg_count =>l_msg_count ,
2173 x_msg_data =>l_msg_data ,
2174 x_sequence =>l_sequence
2175 );
2176
2177 /* p_update_type_indicator Indicates if there is a quantity change associated with the lot uom conversion change and if so,
2178 what kind of change
2179 (0 for Update On-Hand Balances,
2180 1 for Recalculate Batch Primary Quantity,
2181 2 for Recalculate Batch Secondary Quantity,
2182 3 for Recalculate On-Hand Primary Quantity,
2183 4 for Recalculate On-Hand Secondary Quantity,
2184 5 for No Quantity Updates)
2185 */
2186
2187 l_progress := '035';
2188
2189 IF g_debug = 1 THEN
2190 print_debug('Program MTL_LOT_UOM_CONV_PUB.create_lot_uom_conversion return :' ||l_progress ||':'|| l_return_status, 1);
2191 print_debug('x_return_status:'||l_return_status, 1);
2192 print_debug('x_msg_count:'||l_msg_count, 1);
2193 END IF;
2194
2195 IF l_return_status = fnd_api.g_ret_sts_error THEN
2196 IF g_debug = 1 THEN
2197 print_debug('Program MTL_LOT_UOM_CONV_PUB.create_lot_uom_conversion has failed with a user defined exception:'||l_progress, 1);
2198 END IF;
2199 FND_MESSAGE.SET_NAME('INV','INV_PROGRAM_ERROR');
2200 FND_MESSAGE.SET_TOKEN('PGM_NAME','MTL_LOT_UOM_CONV_PUB.create_lot_uom_conversion');
2201 fnd_msg_pub.ADD;
2202 RAISE g_exc_error;
2203
2204 l_progress := '036';
2205
2206 ELSIF l_return_status = fnd_api.g_ret_sts_unexp_error THEN
2207 IF g_debug = 1 THEN
2208 print_debug('Program MTL_LOT_UOM_CONV_PUB.create_lot_uom_conversion has failed with a Unexpected exception :'|| l_progress, 9);
2209 END IF;
2210 FND_MESSAGE.SET_NAME('INV','INV_PROGRAM_ERROR');
2211 FND_MESSAGE.SET_TOKEN('PGM_NAME','MTL_LOT_UOM_CONV_PUB.create_lot_uom_conversion');
2212 fnd_msg_pub.ADD;
2213 RAISE g_exc_unexpected_error;
2214 END IF;
2215
2216
2217 END IF;------- If l_primary_uom_class <> l_secondary_uom_class THEN
2218 END IF;------------IF l_permision_value = 'Y' THEN
2219 END IF;-----IF P_PRIMARY_UNIT_OF_MEASURE IS NOT NULL AND
2220 END IF;-------------- If p_line_secondary_quantity IS NOT NULL THEN
2221
2222 print_debug('End of the program inv_roi_integration_grp.inv_new_lot. Program has completed successfully: '|| l_progress, 9);
2223
2224 EXCEPTION
2225
2226 WHEN NO_DATA_FOUND THEN
2227 x_return_status := fnd_api.g_ret_sts_error;
2228 /*
2229 IF l_progress = '002' THEN
2230 ROLLBACK TO inv_new_lot_save2;
2231 ELSIF l_progress = '006' THEN
2232 ROLLBACK TO inv_new_lot_save6;
2233 END IF;
2234 */
2235 fnd_msg_pub.count_and_get(
2236 p_encoded => fnd_api.g_false ,
2237 p_count => x_msg_count ,
2238 p_data => x_msg_data
2239 );
2240
2241 IF( x_msg_count > 1 ) THEN
2242 x_msg_data := fnd_msg_pub.get(
2243 x_msg_count ,
2244 FND_API.G_FALSE
2245 );
2246 END IF ;
2247 IF g_debug = 1 THEN
2248 print_debug('Exitting INV_NEW_LOT - No data found error:'||TO_CHAR(SYSDATE, 'YYYY-MM-DD HH:DD:SS')||':'||l_progress, 1);
2249 print_stacked_messages;
2250 END IF;
2251
2252 WHEN g_exc_error THEN
2253 x_return_status := fnd_api.g_ret_sts_error;
2254 /*
2255 IF l_progress = '002' THEN
2256 ROLLBACK TO inv_new_lot_save2;
2257 ELSIF l_progress = '006' THEN
2258 ROLLBACK TO inv_new_lot_save6;
2259 END IF;
2260 */
2261 fnd_msg_pub.count_and_get(
2262 p_encoded => fnd_api.g_false ,
2263 p_count => x_msg_count ,
2264 p_data => x_msg_data
2265 );
2266
2267 IF( x_msg_count > 1 ) THEN
2268 x_msg_data := fnd_msg_pub.get(
2269 x_msg_count ,
2270 FND_API.G_FALSE
2271 );
2272 END IF;
2273
2274 IF g_debug = 1 THEN
2275 print_debug('Exitting INV_NEW_LOT - g_exc_error:'||TO_CHAR(SYSDATE, 'YYYY-MM-DD HH:DD:SS')||':'||l_progress, 1);
2276 print_stacked_messages;
2277 END IF;
2278
2279 WHEN g_exc_unexpected_error THEN
2280 x_return_status := fnd_api.g_ret_sts_unexp_error;
2281 /*
2282 IF l_progress = '002' THEN
2283 ROLLBACK TO inv_new_lot_save2;
2284 ELSIF l_progress = '006' THEN
2285 ROLLBACK TO inv_new_lot_save6;
2286 END IF;
2287 */
2288 fnd_msg_pub.count_and_get(
2289 p_encoded => fnd_api.g_false ,
2290 p_count => x_msg_count ,
2291 p_data => x_msg_data
2292 );
2293
2294 IF( x_msg_count > 1 ) THEN
2295 x_msg_data := fnd_msg_pub.get(
2296 x_msg_count ,
2297 FND_API.G_FALSE);
2298 END IF ;
2299
2300 IF g_debug = 1 THEN
2301 print_debug('Exitting INV_NEW_LOT - g_exc_unexpected_error:'||TO_CHAR(SYSDATE, 'YYYY-MM-DD HH:DD:SS')||':'||l_progress, 1);
2302 print_stacked_messages;
2303 END IF;
2304
2305 WHEN OTHERS THEN
2306 x_return_status := fnd_api.g_ret_sts_unexp_error;
2307 /*
2308 IF l_progress = '002' THEN
2309 ROLLBACK TO inv_new_lot_save2;
2310 ELSIF l_progress = '006' THEN
2311 ROLLBACK TO inv_new_lot_save6;
2312 END IF;
2313 */
2314 fnd_msg_pub.count_and_get(
2315 p_encoded => fnd_api.g_false ,
2316 p_count => x_msg_count ,
2317 p_data => x_msg_data
2318 );
2319
2320 IF( x_msg_count > 1 ) THEN
2321 x_msg_data := fnd_msg_pub.get(
2322 x_msg_count ,
2323 FND_API.G_FALSE);
2324 END IF;
2325
2326 IF g_debug = 1 THEN
2327 print_debug('Exitting INV_NEW_LOT - OTHERS error:'||TO_CHAR(SYSDATE, 'YYYY-MM-DD HH:DD:SS')||':'||l_progress, 1);
2328 print_stacked_messages;
2329 END IF;
2330
2331
2332
2333 END INV_New_lot;
2334
2335
2336 /*##########################################################################
2337 #
2338 # PROCEDURE INV_Synch_Quantities
2339 #
2340 # DESCRIPTION :-
2341 #
2342 # 1) For lot controlled items if the receiving UOM class is different from
2343 # the Source Doc (PO) uom class then loop through each record in MTLT and
2344 # convert the lot transaction(primary) quantity of each lot record in
2345 # Receiving unit of measure to source doc (Purchasing) unit of measure
2346 # taking lot specific conversion into consideration.
2347 # Sum it up and update the Source Doc primary quantity with this.
2348 #
2349 # 2) For lot controlled and dual uom controlled items ,loop through each
2350 # record in MTLT and sum the lot_secondary_quantity and update the
2351 # secondary receipt line quantity with this.
2352 #
2353 # DESIGN REFERENCES:
2354 # http://files.oraclecorp.com/content/AllPublic/Workspaces/
2355 # Inventory%20Convergence-Public/Design/Oracle%20Purchasing/TDD/PO_ROI_TDD.zip
2356 #
2357 # MODIFICATION HISTORY
2358 # 13-SEP-2004 Punit Kumar Created
2359 #
2360 #########################################################################*/
2361
2362
2363 PROCEDURE INV_Synch_Quantities(
2364 x_return_status OUT NOCOPY VARCHAR2 ,
2365 x_msg_data OUT NOCOPY VARCHAR2 ,
2366 x_msg_count OUT NOCOPY NUMBER ,
2367 x_sum_sourcedoc_quantity OUT NOCOPY NUMBER ,
2368 x_sum_rti_secondary_quantity OUT NOCOPY NUMBER ,
2369 p_api_version IN NUMBER DEFAULT 1.0 ,
2370 p_init_msg_lst IN VARCHAR2 := FND_API.G_FALSE ,
2371 p_inventory_item_id IN NUMBER ,
2372 p_to_organization_id IN NUMBER ,
2373 p_lot_number IN VARCHAR2 ,
2374 p_transaction_unit_of_measure IN VARCHAR2 ,
2375 p_sourcedoc_unit_of_meaure IN VARCHAR2 ,
2376 p_lot_quantity IN NUMBER ,
2377 p_line_secondary_quantity IN NUMBER ,
2378 p_secondary_unit_of_measure IN VARCHAR2 ,
2379 p_lot_secondary_quantity IN NUMBER
2380 )
2381
2382 IS
2383
2384 /*local variables declaration*/
2385 l_api_name VARCHAR2(30) := 'INV_Synch_Quantities';
2386 l_api_version CONSTANT NUMBER := 1.0;
2387 l_recv_uom_class VARCHAR2(10);
2388 l_sourcedoc_uom_class VARCHAR2(10);
2389 l_rti_sourcedoc_quantity NUMBER :=0 ;
2390 l_sum_sourcedoc_quantity NUMBER :=0 ;
2391 l_sum_rti_secondary_quantity NUMBER :=0 ;
2392
2393
2394 BEGIN
2395
2396 l_progress :='037';
2397
2398 -- Standard call to check for call compatibility.
2399 IF NOT fnd_api.compatible_api_call(
2400 l_api_version,
2401 p_api_version,
2402 l_api_name,
2403 'INV_ROI_INTEGRATION_GRP'
2404 ) THEN
2405 IF (g_debug = 1) THEN
2406 print_debug('FND_API not compatible INV_ROI_INTEGRATION_GRP.INV_Synch_Quantities:' || l_progress, 1);
2407 END IF;
2408 RAISE fnd_api.g_exc_unexpected_error;
2409 END IF;
2410
2411 -- Initialize message list if p_init_msg_list is set to TRUE.
2412 IF fnd_api.to_boolean(p_init_msg_lst) THEN
2413 fnd_msg_pub.initialize;
2414 END IF;
2415
2416 --Initialize the return status
2417 x_return_status := FND_API.G_RET_STS_SUCCESS;
2418
2419 BEGIN
2420 l_progress :='038';
2421
2422 /*Updating source doc quantity*/
2423
2424 SELECT distinct(uom_class)
2425 INTO l_recv_uom_class
2426 FROM MTL_UNITS_OF_MEASURE
2427 WHERE UNIT_OF_MEASURE = p_transaction_unit_of_measure;
2428
2429 SELECT distinct(uom_class)
2430 INTO l_sourcedoc_uom_class
2431 FROM MTL_UNITS_OF_MEASURE
2432 WHERE UNIT_OF_MEASURE = p_sourcedoc_unit_of_meaure;
2433
2434 IF g_debug = 1 THEN
2435 print_debug('Inside inv_synch_quantities :' ||l_progress , 1);
2436 print_debug('l_recv_uom_class :' ||l_recv_uom_class , 1);
2437 print_debug('l_sourcedoc_uom_class :' ||l_sourcedoc_uom_class , 1);
2438 END IF;
2439
2440
2441 EXCEPTION
2442 WHEN OTHERS THEN
2443 IF g_debug = 1 THEN
2444 print_debug('Fetching uom class failed in synch_secondary_quantity:' || l_progress, 1);
2445 END IF;
2446 RAISE fnd_api.g_exc_error;
2447 END;
2448
2449 l_progress:='039';
2450
2451 IF l_recv_uom_class <> l_sourcedoc_uom_class THEN
2452 /*i.e. Receiving UOM and Source Doc (Purchasing)
2453 unit of measures belong to different UOM classes then,
2454 for each record of MTLT for that item...... */
2455
2456 /* Convert p_mtlt_transaction_quantity (MTLT.TRANSACTION_QUANTITY)
2457 in p_rti_unit_of_measure (RTI.UNIT_OF_MEASURE )for each lot of that item into
2458 RTI.SOURCE_DOC_UNIT_OF_MEASURE taking lot specific conversion into consideration */
2459
2460
2461 l_rti_sourcedoc_quantity := INV_CONVERT.inv_um_convert(
2462 item_id => p_inventory_item_id ,
2463 lot_number => p_lot_number ,
2464 organization_id => p_to_organization_id ,
2465 precision => 5 ,
2466 from_quantity => p_lot_quantity ,
2467 from_unit => NULL ,
2468 to_unit => NULL ,
2469 from_name => p_transaction_unit_of_measure ,
2470 to_name => p_sourcedoc_unit_of_meaure
2471 );
2472
2473 l_progress :='040';
2474
2475 IF g_debug = 1 THEN
2476 print_debug('Program INV_CONVERT.inv_um_convert return :' ||l_progress , 1);
2477 print_debug('l_rti_sourcedoc_quantity :' ||l_rti_sourcedoc_quantity , 1);
2478 END IF;
2479
2480 IF l_rti_sourcedoc_quantity = -99999 THEN
2481 IF g_debug = 1 THEN
2482 print_debug('INV_CONVERT.inv_um_convert has failed in inv_synch_quantities:'|| l_progress, 1);
2483 END IF;
2484 FND_MESSAGE.SET_NAME('INV','INV_NO_CONVERSION_ERR');
2485 FND_MESSAGE.SET_TOKEN('PGM_NAME','INV_CONVERT.inv_um_convert');
2486 fnd_msg_pub.ADD;
2487 RAISE g_exc_unexpected_error;
2488 END IF;
2489
2490 l_progress :='041';
2491
2492 /*This procedure is called in a loop. So using x_sum_rti_secondary_quantity and
2493 x_sum_sourcedoc_quantity instead of the corresponding local variables as the
2494 local variables aer initialized to 0 and the sum will repeatedly get initialised to 0
2495 due to multiple calls in the loop*/
2496
2497 x_sum_sourcedoc_quantity := nvl(x_sum_sourcedoc_quantity,0) + nvl(l_rti_sourcedoc_quantity,0);
2498
2499 /*Logic is to get the sum of above quantity here in this procedure and update RTI
2500 at the the end of validate_lot_serial_info (where the loop for all MTLT lots ends).*/
2501
2502 IF g_debug = 1 THEN
2503 print_debug('x_sum_sourcedoc_quantity :' ||x_sum_sourcedoc_quantity , 1);
2504 END IF;
2505
2506 END IF; /*IF l_recv_uom_class <> l_sourcedoc_uom_class THEN */
2507
2508 /*Updating Secondary Quantity in Receipt line*/
2509
2510
2511 --IF p_line_secondary_quantity > 0 AND p_secondary_unit_of_measure IS NOT NULL THEN
2512 IF nvl(p_line_secondary_quantity,0) <> 0 AND p_secondary_unit_of_measure IS NOT NULL THEN -- 7644869 change check as qty could be negative
2513
2514 l_progress :='042';
2515
2516 IF g_debug = 1 THEN
2517 print_debug('p_lot_secondary_quantity :' ||p_lot_secondary_quantity , 1);
2518 print_debug('x_sum_rti_secondary_quantity :' ||x_sum_rti_secondary_quantity , 1);
2519 END IF;
2520
2521
2522 x_sum_rti_secondary_quantity := nvl(x_sum_rti_secondary_quantity,0) + nvl(p_lot_secondary_quantity,0) ;
2523
2524 /*Here also logic is to get the sum of above quantity here in this procedure and update
2525 RTI at the the end of validate_lot_serial_info (where the loop for all MTLT lots ends).*/
2526
2527 IF g_debug = 1 THEN
2528 print_debug('x_sum_rti_secondary_quantity :' ||x_sum_rti_secondary_quantity , 1);
2529 END IF;
2530
2531 END IF;
2532 /*
2533 x_sum_sourcedoc_quantity := l_sum_sourcedoc_quantity;
2534 x_sum_rti_secondary_quantity :=l_sum_rti_secondary_quantity;
2535 */
2536 EXCEPTION
2537
2538 WHEN g_exc_unexpected_error THEN
2539 x_return_status := fnd_api.g_ret_sts_unexp_error;
2540 fnd_msg_pub.count_and_get(
2541 p_encoded => fnd_api.g_false ,
2542 p_count => x_msg_count ,
2543 p_data => x_msg_data
2544 );
2545 IF( x_msg_count > 1 ) THEN
2546 x_msg_data := fnd_msg_pub.get(
2547 x_msg_count ,
2548 FND_API.G_FALSE
2549 );
2550 END IF ;
2551
2552 IF g_debug = 1 THEN
2553 print_debug('Exitting INV_Synch_Quantities - g_exc_unexpected_error :'||TO_CHAR(SYSDATE, 'YYYY-MM-DD HH:DD:SS')||':'||l_progress, 1);
2554 print_stacked_messages;
2555 END IF;
2556
2557 WHEN OTHERS THEN
2558 x_return_status := fnd_api.g_ret_sts_unexp_error;
2559 fnd_msg_pub.count_and_get(
2560 p_encoded => fnd_api.g_false ,
2561 p_count => x_msg_count ,
2562 p_data => x_msg_data
2563 );
2564 IF( x_msg_count > 1 ) THEN
2565 x_msg_data := fnd_msg_pub.get(
2566 x_msg_count ,
2567 FND_API.G_FALSE);
2568 END IF;
2569
2570 IF g_debug = 1 THEN
2571 print_debug('Exitting INV_Synch_Quantities - Others error :'||TO_CHAR(SYSDATE, 'YYYY-MM-DD HH:DD:SS')||':'||l_progress, 1);
2572 print_stacked_messages;
2573 END IF;
2574
2575
2576 END INV_Synch_Quantities;
2577
2578
2579
2580
2581
2582 /*##########################################################################
2583 #
2584 # FUNCTION inv_rma_lot_info_exists
2585 #
2586 #
2587 # DESCRIPTION :- This function checks whether lot exists in the RMA and returns
2588 # 'True' or 'False' accordingly.
2589 #
2590 # DESIGN REFERENCES:
2591 # http://files.oraclecorp.com/content/AllPublic/Workspaces/
2592 # Inventory%20Convergence-Public/Design/Oracle%20Purchasing/TDD/PO_ROI_TDD.zip
2593 #
2594 # MODIFICATION HISTORY
2595 # 10-AUG-2004 Punit Kumar Created
2596 #
2597 #########################################################################*/
2598
2599
2600
2601 FUNCTION inv_rma_lot_info_exists(
2602 x_msg_data OUT NOCOPY VARCHAR2 ,
2603 x_msg_count OUT NOCOPY NUMBER ,
2604 x_count_rma_lots OUT NOCOPY NUMBER ,
2605 p_oe_order_header_id IN VARCHAR2 ,
2606 p_oe_order_line_id IN VARCHAR2
2607 )RETURN BOOLEAN IS
2608
2609
2610 CURSOR Cr_count_lot IS
2611 SELECT count (*)
2612 FROM oe_lot_serial_numbers
2613 WHERE (line_id = p_oe_order_line_id
2614 OR line_set_id IN
2615 (SELECT line_set_id
2616 FROM oe_order_lines_all
2617 WHERE line_id = p_oe_order_line_id
2618 AND header_id = p_oe_order_header_id)) ;
2619
2620 BEGIN
2621 l_progress := '061';
2622
2623 IF p_oe_order_line_id IS NOT NULL THEN
2624
2625 BEGIN
2626 OPEN Cr_count_lot;
2627 FETCH Cr_count_lot
2628 INTO x_count_rma_lots;
2629 CLOSE Cr_count_lot;
2630
2631 EXCEPTION
2632 WHEN OTHERS THEN
2633 IF g_debug = 1 THEN
2634 print_debug('Fetching RMA lot inside inv_roi_integration_grp.inv_rma_lot_info_exists failed:' || l_progress, 1);
2635 END IF;
2636 RAISE fnd_api.g_exc_error;
2637 END;
2638 l_progress :='062';
2639
2640 IF x_count_rma_lots = 0 THEN
2641 IF g_debug = 1 THEN
2642 print_debug('lot is not present in RMA so user can receive into any valid lot:'||l_progress, 1);
2643 END IF;
2644 RETURN FALSE;
2645 ELSE ------- x_count_rma_lots >= 0
2646 IF g_debug = 1 THEN
2647 print_debug('inv_rma_lot_info_exists::lot is present in RMA:'|| l_progress, 1);
2648 END IF;
2649 RETURN TRUE;
2650 END IF;
2651 END IF; -----------IF p_oe_order_line_id IS NOT NULL THEN
2652
2653 EXCEPTION
2654
2655 WHEN g_exc_error THEN
2656 fnd_msg_pub.count_and_get(
2657 p_encoded => fnd_api.g_false,
2658 p_count => x_msg_count,
2659 p_data => x_msg_data);
2660 IF( x_msg_count > 1 ) THEN
2661 x_msg_data := fnd_msg_pub.get(x_msg_count, FND_API.G_FALSE);
2662 END IF ;
2663
2664 IF g_debug = 1 THEN
2665 print_debug('Exitting inv_rma_lot_info_exists - g_exc_error:'||TO_CHAR(SYSDATE, 'YYYY-MM-DD HH:DD:SS')||':'||l_progress, 1);
2666 print_stacked_messages;
2667 END IF;
2668
2669 RETURN FALSE;
2670
2671 WHEN OTHERS THEN
2672 fnd_msg_pub.count_and_get(
2673 p_encoded => fnd_api.g_false,
2674 p_count => x_msg_count,
2675 p_data => x_msg_data);
2676 IF( x_msg_count > 1 ) THEN
2677 x_msg_data := fnd_msg_pub.get(x_msg_count, FND_API.G_FALSE);
2678 END IF ;
2679
2680 IF g_debug = 1 THEN
2681 print_debug('Exitting inv_rma_lot_info_exists - OTHERS error:'||TO_CHAR(SYSDATE, 'YYYY-MM-DD HH:DD:SS')||':'||l_progress, 1);
2682 print_stacked_messages;
2683 END IF;
2684 RETURN FALSE;
2685
2686 END inv_rma_lot_info_exists;
2687
2688
2689
2690
2691
2692 /*##########################################################################
2693 #
2694 # PROCEDURE : Inv_Validate_rma_quantity
2695 #
2696 # DESCRIPTION : This procedure validates the user entered quantity against the RMA
2697 # quantity ( considering the already received quantity) and returns
2698 # the allowed quantity incase the user quantity exceeds the limit.
2699 #
2700 # DESIGN REFERENCES:
2701 # http://files.oraclecorp.com/content/AllPublic/Workspaces/
2702 # Inventory%20Convergence-Public/Design/Oracle%20Purchasing/TDD/PO_ROI_TDD.zip
2703 #
2704 # MODIFICATION HISTORY
2705 # 10-AUG-2004 Punit Kumar Created
2706 #
2707 #########################################################################*/
2708
2709
2710
2711 PROCEDURE Inv_Validate_rma_quantity(
2712 x_allowed OUT NOCOPY VARCHAR2 ,
2713 x_allowed_quantity OUT NOCOPY NUMBER ,
2714 x_return_status OUT NOCOPY VARCHAR2 ,
2715 x_msg_data OUT NOCOPY VARCHAR2 ,
2716 x_msg_count OUT NOCOPY NUMBER ,
2717 p_api_version IN NUMBER DEFAULT 1.0 ,
2718 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE ,
2719 p_item_id IN NUMBER ,
2720 p_lot_number IN VARCHAR2 ,
2721 p_oe_order_header_id IN NUMBER ,
2722 p_oe_order_line_id IN NUMBER ,
2723 p_rma_quantity IN NUMBER ,
2724 p_trx_unit_of_measure IN VARCHAR2 ,
2725 p_rti_id IN NUMBER ,
2726 p_to_organization_id IN NUMBER ,
2727 p_trx_quantity IN NUMBER
2728 )
2729 IS
2730
2731 l_api_name VARCHAR2(30) := 'Inv_Validate_rma_quantity' ;
2732 l_api_version CONSTANT NUMBER := 1.0 ;
2733 l_progress VARCHAR2(3) := '000' ;
2734 l_rma_quantity NUMBER := 0 ;
2735 l_rma_lot_unit_of_measure VARCHAR2(30) ;
2736 l_lot_recv_qty NUMBER :=0 ;
2737 l_line_set_id NUMBER :=0 ;
2738 l_lot_recv_unit_of_measure VARCHAR2(30) ;
2739 l_trx_unit_of_measure VARCHAR2(30) ;
2740 l_trx_quantity NUMBER :=0 ;
2741 l_return_status VARCHAR2(50) ;
2742 l_precision NUMBER := 5 ;
2743 l_from_unit VARCHAR2(3) := NULL ;
2744 l_to_unit VARCHAR2(3) :=NULL ;
2745
2746 -----fetching the received quantity for that lot if line set id is not null
2747 CURSOR Cr_lot_recv_qty_lineset IS
2748 SELECT SUM(mtln.primary_quantity)
2749 FROM mtl_material_transactions mmt ,
2750 mtl_transaction_lot_numbers mtln
2751 WHERE mmt.trx_source_line_id IN
2752 (SELECT line_id
2753 FROM oe_order_lines_all
2754 WHERE line_set_id = l_line_set_id)
2755 AND mmt.transaction_source_type_id = 12
2756 AND mmt.transaction_action_id in (1,27)
2757 AND mmt.transaction_type_id in (15,36,37)
2758 AND mmt.inventory_item_id = p_item_id
2759 AND mmt.organization_id = p_to_organization_id
2760 AND nvl(mtln.lot_number,-1) = nvl(p_lot_number,-1) --bug8786451,lot number missing when fetch the received qty
2761 AND mtln.TRANSACTION_ID = mmt.transaction_id;
2762
2763
2764 ------fetching the received quantity for that lot if line set id is null
2765 CURSOR Cr_lot_recv_qty IS
2766 SELECT SUM(mtln.primary_quantity)
2767 FROM mtl_material_transactions mmt ,
2768 mtl_transaction_lot_numbers mtln
2769 WHERE mmt.trx_source_line_id = p_oe_order_line_id
2770 AND mmt.transaction_source_type_id = 12
2771 and mmt.transaction_action_id in (1,27)
2772 and mmt.transaction_type_id in (15,36,37)
2773 and mmt.inventory_item_id = p_item_id
2774 and mmt.organization_id = p_to_organization_id
2775 and nvl(mtln.lot_number,-1) = nvl(p_lot_number,-1) --bug8786451,lot number missing when fetch the received qty
2776 and mtln.TRANSACTION_ID = mmt.transaction_id;
2777
2778
2779 /*Fetching the total quantity entered by the user against that RMA
2780 to be used only for batch/immediate mode
2781 The below cursor is because the user may receive multiple times into the same lot
2782 and we need to sum up the quantity of all lots to validate against the actual
2783 RMA quantity.*/
2784
2785 CURSOR Cr_user_quantity IS
2786 SELECT SUM(transaction_quantity)
2787 FROM mtl_transaction_lots_temp
2788 WHERE product_code = 'RCV'
2789 AND product_transaction_id = p_rti_id
2790 AND lot_number= p_lot_number;
2791
2792 /*The below cursor willl be used to fetch the RMA quantity for online transactions.*/
2793 CURSOR Cr_rma_qty_online IS
2794 SELECT SUM(QUANTITY)
2795 FROM oe_lot_serial_numbers
2796 WHERE (line_id = p_oe_order_line_id
2797 OR line_set_id IN
2798 (SELECT line_set_id
2799 FROM oe_order_lines_all
2800 WHERE line_id = p_oe_order_line_id
2801 AND header_id = p_oe_order_header_id))
2802 AND lot_number = p_lot_number;
2803
2804
2805
2806 BEGIN
2807 l_progress :='063';
2808
2809 -- Standard call to check for call compatibility.
2810 IF NOT fnd_api.compatible_api_call(
2811 l_api_version,
2812 p_api_version,
2813 l_api_name,
2814 'INV_ROI_INTEGRATION_GRP'
2815 ) THEN
2816 IF (g_debug = 1) THEN
2817 print_debug('FND_API not compatible INV_ROI_INTEGRATION_GRP.Inv_Validate_rma_quantity:'|| l_progress, 1);
2818 END IF;
2819 RAISE fnd_api.g_exc_unexpected_error;
2820 END IF;
2821
2822 -- Initialize message list if p_init_msg_list is set to TRUE.
2823 IF fnd_api.to_boolean(p_init_msg_list) THEN
2824 fnd_msg_pub.initialize;
2825 END IF;
2826
2827 --Initialize the return status
2828 x_return_status := FND_API.G_RET_STS_SUCCESS;
2829
2830
2831 --SAVEPOINT val_rma_quant1;
2832 l_progress := '001';
2833 x_return_status := g_ret_sts_success;
2834
2835 --If any of these parameters are NULL nothing can be done so just returns as if successful.
2836 IF p_item_id IS NULL OR
2837 p_oe_order_header_id IS NULL OR
2838 p_oe_order_line_id IS NULL OR
2839 p_trx_unit_of_measure IS NULL THEN
2840
2841 l_progress :='064';
2842
2843 IF (g_debug = 1) THEN
2844 print_debug('Exitting Inv_Validate_rma_quantity due to null parameter value:'||l_progress, 1);
2845 print_debug('Parameter values p_item_id ,p_oe_order_header_id,p_oe_order_line_id and p_trx_unit_of_measure
2846 are:' || p_item_id || ':'|| p_oe_order_header_id || ':' ||p_oe_order_line_id || ':'||p_trx_unit_of_measure||':'|| l_progress, 1);
2847 END IF;
2848 X_allowed := 'Y';
2849 x_return_status := FND_API.G_RET_STS_SUCCESS;
2850 RETURN;
2851
2852 END IF;
2853 l_trx_unit_of_measure := p_trx_unit_of_measure;--bug11734602,the variable not setted with the right value
2854 --- RMA quantity
2855 IF p_rma_quantity is NULL THEN -----online mode
2856 OPEN Cr_rma_qty_online;
2857 FETCH Cr_rma_qty_online
2858 INTO l_rma_quantity;
2859 CLOSE Cr_rma_qty_online;
2860
2861 IF g_debug = 1 THEN
2862 print_debug('RMA quantity fetched for online mode is :'||l_rma_quantity||':'|| l_progress, 1);
2863 END IF;
2864
2865 ELSE ---batch/immediate mode
2866 l_rma_quantity :=p_rma_quantity;
2867
2868 IF g_debug = 1 THEN
2869 print_debug('RMA quantity given for batch/immediate mode is:'||l_rma_quantity||':'|| l_progress, 1);
2870 END IF;
2871
2872 END IF;
2873
2874
2875 l_progress := '002';
2876
2877 ----Fetch line_set_id
2878
2879 BEGIN
2880 SELECT line_set_id
2881 INTO l_line_set_id
2882 FROM oe_order_lines_all
2883 WHERE line_id = p_oe_order_line_id
2884 AND header_id = p_oe_order_header_id;
2885
2886 l_progress :='065';
2887
2888 EXCEPTION
2889 WHEN OTHERS THEN
2890 IF g_debug = 1 THEN
2891 print_debug('Inv_Validate_rma_quantity ::line set id not found :'|| l_progress , 1);
2892 END IF;
2893 RAISE g_exc_unexpected_error;
2894
2895 END;
2896
2897 ----------Fetch RMA UNIT_OF_MEASURE
2898
2899 SELECT unit_of_measure
2900 INTO l_rma_lot_unit_of_measure
2901 FROM oe_order_lines_all ,mtl_units_of_measure
2902 WHERE header_id = p_oe_order_header_id
2903 AND line_id = p_oe_order_line_id
2904 AND uom_code = order_quantity_uom;
2905
2906
2907 IF g_debug = 1 THEN
2908 print_debug('RMA UNIT_OF_MEASURE is :'|| l_rma_lot_unit_of_measure , 1);
2909 END IF;
2910
2911 l_lot_recv_qty := 0;
2912
2913 ------------------Get user entered quantity
2914 IF p_trx_quantity is NULL THEN -----batch/immediate mode
2915 OPEN Cr_user_quantity;
2916 FETCH Cr_user_quantity
2917 INTO l_trx_quantity;
2918 ClOSE Cr_user_quantity;
2919
2920 IF g_debug = 1 THEN
2921 print_debug('RMA quantity for batch/immediate mode is :'||l_trx_quantity||':'|| l_progress, 1);
2922 END IF;
2923
2924 ELSE --------online mode
2925 l_trx_quantity := p_trx_quantity;
2926
2927 IF g_debug = 1 THEN
2928 print_debug('RMA quantity for online mode is :'||l_trx_quantity||':'|| l_progress, 1);
2929 END IF;
2930
2931 END IF;
2932
2933
2934 ------------------Get previously received quantity for the Lot.
2935 IF l_line_set_id IS NULL THEN
2936 --------------- sales order line is not split
2937 OPEN Cr_lot_recv_qty;
2938 FETCH Cr_lot_recv_qty
2939 INTO l_lot_recv_qty;
2940 CLOSE Cr_lot_recv_qty;
2941
2942 l_progress :='066';
2943
2944 ELSIF l_line_set_id IS NOT NULL THEN
2945 ------------ sales order line is split due to partial receipt of RMA
2946 OPEN Cr_lot_recv_qty_lineset;
2947 FETCH Cr_lot_recv_qty_lineset
2948 INTO l_lot_recv_qty;
2949 Close Cr_lot_recv_qty_lineset;
2950
2951 l_progress :='067';
2952
2953 END IF;
2954
2955 IF g_debug = 1 THEN
2956 print_debug('previously received quantity for the Lot is :' || l_lot_recv_qty||':'||l_progress, 1);
2957 END IF;
2958
2959 --If lot was previously received then take that quantity also into account.
2960 -- fetch Primary unit_of_measure of previously received lot from item master (mtl_system_items_b.)
2961 l_progress := '068';
2962
2963 BEGIN
2964 SELECT PRIMARY_UNIT_OF_MEASURE
2965 INTO l_lot_recv_unit_of_measure
2966 FROM mtl_system_items_b
2967 WHERE INVENTORY_ITEM_ID = p_item_id
2968 AND organization_id = p_to_organization_id;
2969
2970 EXCEPTION
2971 WHEN OTHERS THEN
2972 IF g_debug = 1 THEN
2973 print_debug('Inv_Validate_rma_quantity :: primary unit of measure not found:'||l_progress, 1);
2974 END IF;
2975 RAISE g_exc_unexpected_error;
2976 END;
2977
2978 IF g_debug = 1 THEN
2979 print_debug('primary unit of measure of previously received lot from item master is :' || l_lot_recv_unit_of_measure||':'||l_progress, 1);
2980 END IF;
2981
2982 /* Converting all to a single unit of measure (previously received unit of measure)
2983
2984 IF transaction unit_of_measure is different than previously received primary unit_of_measure then
2985 convert transaction qty to previously received primary unit_of_measure.*/
2986 l_progress := '069';
2987 ---SAVEPOINT val_rma_quant4;
2988
2989 IF l_trx_unit_of_measure <> l_lot_recv_unit_of_measure THEN
2990
2991 IF g_debug = 1 THEN
2992 print_debug('l_trx_unit_of_measure <> l_lot_recv_unit_of_measure :' ||l_progress, 1);
2993 END IF;
2994
2995
2996 l_trx_quantity := INV_CONVERT.inv_um_convert(
2997 item_id => p_item_id ,
2998 lot_number => p_lot_number ,
2999 organization_id => p_to_organization_id ,
3000 precision => l_precision ,
3001 from_quantity => l_trx_quantity ,
3002 from_unit => l_from_unit ,
3003 to_unit => l_to_unit ,
3004 from_name => l_trx_unit_of_measure ,
3005 to_name => l_lot_recv_unit_of_measure
3006 );
3007
3008 IF g_debug = 1 THEN
3009 print_debug('quantity when l_trx_unit_of_measure <> l_lot_recv_unit_of_measure is :'|| l_trx_quantity ,1);
3010 print_debug('Program INV_CONVERT.inv_um_convert return: ' || l_return_status||':'||l_progress, 1);
3011 END IF;
3012
3013
3014 IF l_trx_quantity = -99999 THEN
3015
3016 l_progress := '070';
3017
3018 IF g_debug = 1 THEN
3019 print_debug('Inv_Validate_rma_quantity::INV_CONVERT.inv_um_convert has failed:'||l_progress, 1);
3020 END IF;
3021 FND_MESSAGE.SET_NAME('INV','INV_NO_CONVERSION_ERR');
3022 FND_MESSAGE.SET_TOKEN('PGM_NAME','INV_CONVERT.inv_um_convert');
3023 fnd_msg_pub.ADD;
3024 RAISE g_exc_unexpected_error;
3025 END IF;
3026
3027 END IF;
3028
3029 l_progress := '071';
3030 ---SAVEPOINT val_rma_quant5;
3031
3032 /* IF RMA unit_of_measure is different than previously received primary unit_of_measure
3033 then convert RMA qty to previously received primary unit_of_measure. */
3034 /*bug11734602,if l_rma_quantity returned from oe_lot_serial_numbers,
3035 since its quantity always be populated with primary qty,it should not be converted again.*/
3036 IF nvl(p_rma_quantity,-1) <> -1 and l_rma_lot_unit_of_measure <> l_lot_recv_unit_of_measure THEN
3037
3038 IF g_debug = 1 THEN
3039 print_debug('l_rma_lot_unit_of_measure <> l_lot_recv_unit_of_measure :' ||l_progress, 1);
3040 END IF;
3041
3042 l_rma_quantity := INV_CONVERT.inv_um_convert(
3043 item_id => p_item_id ,
3044 lot_number => p_lot_number ,
3045 organization_id => p_to_organization_id ,
3046 precision => 5 ,
3047 from_quantity => l_rma_quantity ,
3048 from_unit => l_from_unit ,
3049 to_unit => l_to_unit ,
3050 from_name => l_rma_lot_unit_of_measure ,
3051 to_name => l_lot_recv_unit_of_measure
3052 );
3053
3054 IF g_debug = 1 THEN
3055 print_debug('quantity when l_rma_lot_unit_of_measure <> l_lot_recv_unit_of_measure is :'|| l_rma_quantity ,1);
3056 print_debug('Program INV_CONVERT.inv_um_convert return: ' ||l_progress, 1);
3057 END IF;
3058
3059
3060 IF l_rma_quantity = -99999 THEN
3061
3062 l_progress :='072';
3063
3064 IF g_debug = 1 THEN
3065 print_debug('Inv_Validate_rma_quantity::INV_CONVERT.inv_um_convert has failed:'||l_progress, 9);
3066 END IF;
3067 FND_MESSAGE.SET_NAME('INV','INV_NO_CONVERSION_ERR');
3068 FND_MESSAGE.SET_TOKEN('PGM_NAME','INV_CONVERT.inv_um_convert');
3069 fnd_msg_pub.ADD;
3070 RAISE g_exc_unexpected_error;
3071 END IF;
3072
3073 END IF;
3074
3075 -----Quantity Validation Logic:
3076
3077 /*If transaction quantity is greater than the total rma lot quantity minus
3078 the previously received qty for that lot then pass N to the allow flag
3079 along with the allowed qty for that lot in the transaction unit of measure.*/
3080
3081 IF l_trx_quantity > (l_rma_quantity - nvl(l_lot_recv_qty,0)) THEN
3082
3083 l_progress := '073';
3084
3085 X_allowed := 'N';
3086
3087 IF g_debug = 1 THEN
3088 print_debug('l_trx_quantity > (l_rma_quantity - nvl(l_lot_recv_qty,0)) :'|| l_progress ,1);
3089 print_debug('X_allowed: ' ||X_allowed, 1);
3090 END IF;
3091
3092 IF l_trx_unit_of_measure <> l_lot_recv_unit_of_measure THEN
3093
3094 l_progress := '074';
3095
3096 X_allowed_quantity:= INV_CONVERT.inv_um_convert(
3097 item_id => p_item_id ,
3098 lot_number => p_lot_number ,
3099 organization_id => p_to_organization_id ,
3100 precision => 5 ,
3101 from_quantity => (l_rma_quantity - nvl(l_lot_recv_qty,0)) ,
3102 from_unit => NULL ,
3103 to_unit => NULL ,
3104 from_name => l_lot_recv_unit_of_measure ,
3105 to_name => l_trx_unit_of_measure
3106 );
3107 IF g_debug = 1 THEN
3108 print_debug('x_allowed_quantity when l_trx_unit_of_measure <> l_lot_recv_unit_of_measure is :'|| X_allowed_quantity ,1);
3109 print_debug('Program INV_CONVERT.inv_um_convert return ' || l_progress, 1);
3110 END IF;
3111
3112 IF X_allowed_quantity = -99999 THEN
3113 l_progress := '075';
3114
3115 IF g_debug = 1 THEN
3116 print_debug('Inv_Validate_rma_quantity::INV_CONVERT.inv_um_convert has failed:'|| l_progress, 1);
3117 END IF;
3118 FND_MESSAGE.SET_NAME('INV','INV_NO_CONVERSION_ERR');
3119 FND_MESSAGE.SET_TOKEN('PGM_NAME','INV_ROI_INTEGRATION_GRP.INV_VALIDATE_RMA_QUANTITY');
3120 fnd_msg_pub.ADD;
3121 RAISE g_exc_unexpected_error;
3122 END IF;
3123
3124 ELSE
3125 l_progress :='076';
3126 X_allowed_quantity := (l_rma_quantity - nvl(l_lot_recv_qty,0));
3127
3128 IF g_debug = 1 THEN
3129 print_debug('x_allowed_quantity when l_trx_unit_of_measure = l_lot_recv_unit_of_measure is :'|| X_allowed_quantity ,1);
3130 print_debug('l_progress is:' || l_progress, 1);
3131 END IF;
3132 END IF;
3133
3134 l_progress :='077';
3135
3136 ---log error in error stack that quantity validation has failed.
3137 IF g_debug = 1 THEN
3138 print_debug(' RMA Quantity validation has failed ' || l_progress, 1);
3139 END IF;
3140 FND_MESSAGE.SET_NAME('PO','PO_RMA_QUANTITY_VAL_FAILED');
3141 FND_MESSAGE.SET_TOKEN('PGM_NAME','INV_ROI_INTEGRATION_GRP.INV_VALIDATE_RMA_QUANTITY');
3142 FND_MESSAGE.SET_TOKEN('TRX_QUANTITY',l_trx_quantity);
3143 FND_MESSAGE.SET_TOKEN('allowed_quantity',x_allowed_quantity );
3144 FND_MESSAGE.SET_TOKEN('lot_number',p_lot_number );
3145 fnd_msg_pub.ADD;
3146 RAISE g_exc_error;
3147
3148 ELSE ---------IF l_trx_quantity > (l_rma_quantity - nvl(l_lot_recv_qty,0)) THEN
3149 l_progress :='078';
3150 X_allowed := 'Y';
3151 IF g_debug = 1 THEN
3152 print_debug('l_trx_quantity <= (l_rma_quantity - nvl(l_lot_recv_qty,0)) :'|| l_progress ,1);
3153 print_debug('X_allowed: ' ||X_allowed, 1);
3154 print_debug('quantity validation has passed:' || l_progress, 1);
3155 END IF;
3156 END IF;
3157
3158 print_debug('End of the program inv_roi_integration_grp.inv_new_lot. Program has completed successfully :'|| l_progress, 1);
3159
3160 EXCEPTION
3161
3162 WHEN NO_DATA_FOUND THEN
3163 x_return_status := fnd_api.g_ret_sts_error;
3164 fnd_msg_pub.count_and_get(
3165 p_encoded => fnd_api.g_false ,
3166 p_count => x_msg_count ,
3167 p_data => x_msg_data
3168 );
3169
3170 IF( x_msg_count > 1 ) THEN
3171 x_msg_data := fnd_msg_pub.get(
3172 x_msg_count ,
3173 FND_API.G_FALSE
3174 );
3175 END IF ;
3176
3177 IF g_debug = 1 THEN
3178 print_debug('Exitting Inv_Validate_rma_quantity - No data found error:'||TO_CHAR(SYSDATE, 'YYYY-MM-DD HH:DD:SS')||':'||l_progress, 1);
3179 print_stacked_messages;
3180 END IF;
3181
3182 WHEN g_exc_error THEN
3183 x_return_status := fnd_api.g_ret_sts_error;
3184
3185 fnd_msg_pub.count_and_get(
3186 p_encoded => fnd_api.g_false ,
3187 p_count => x_msg_count ,
3188 p_data => x_msg_data
3189 );
3190
3191 IF( x_msg_count > 1 ) THEN
3192 x_msg_data := fnd_msg_pub.get(
3193 x_msg_count ,
3194 FND_API.G_FALSE
3195 );
3196 END IF;
3197 IF g_debug = 1 THEN
3198 print_debug('Exitting Inv_Validate_rma_quantity - g_exc_error:'||TO_CHAR(SYSDATE, 'YYYY-MM-DD HH:DD:SS')||':'||l_progress, 1);
3199 print_stacked_messages;
3200 END IF;
3201
3202 WHEN g_exc_unexpected_error THEN
3203 x_return_status := fnd_api.g_ret_sts_unexp_error;
3204
3205 fnd_msg_pub.count_and_get(
3206 p_encoded => fnd_api.g_false ,
3207 p_count => x_msg_count ,
3208 p_data => x_msg_data
3209 );
3210
3211 IF( x_msg_count > 1 ) THEN
3212 x_msg_data := fnd_msg_pub.get(
3213 x_msg_count ,
3214 FND_API.G_FALSE);
3215 END IF ;
3216 IF g_debug = 1 THEN
3217 print_debug('Exitting Inv_Validate_rma_quantity - g_exc_unexpected_error:'||TO_CHAR(SYSDATE, 'YYYY-MM-DD HH:DD:SS')||':'||l_progress, 1);
3218 print_stacked_messages;
3219 END IF;
3220
3221 WHEN OTHERS THEN
3222 x_return_status := fnd_api.g_ret_sts_unexp_error;
3223
3224 fnd_msg_pub.count_and_get(
3225 p_encoded => fnd_api.g_false ,
3226 p_count => x_msg_count ,
3227 p_data => x_msg_data
3228 );
3229
3230 IF( x_msg_count > 1 ) THEN
3231 x_msg_data := fnd_msg_pub.get(
3232 x_msg_count ,
3233 FND_API.G_FALSE);
3234 END IF;
3235 IF g_debug = 1 THEN
3236 print_debug('Exitting Inv_Validate_rma_quantity - others error:'||TO_CHAR(SYSDATE, 'YYYY-MM-DD HH:DD:SS')||':'||l_progress, 1);
3237 print_stacked_messages;
3238 END IF;
3239
3240 END Inv_Validate_rma_quantity;
3241
3242
3243
3244
3245 /*##########################################################################
3246 #
3247 # PROCEDURE : Check_Item_Attributes
3248 #
3249 # DESCRIPTION : This procedure does lot controlled and child lot controlled
3250 # validations
3251 #
3252 #
3253 # DESIGN REFERENCES:
3254 # http://files.oraclecorp.com/content/AllPublic/Workspaces/
3255 # Inventory%20Convergence-Public/Design/Oracle%20Purchasing/TDD/PO_ROI_TDD.zip
3256 #
3257 # MODIFICATION HISTORY
3258 # 23-NOV-2004 Punit Kumar Created
3259 #
3260 #########################################################################*/
3261
3262
3263 PROCEDURE Check_Item_Attributes(
3264 x_return_status OUT NOCOPY VARCHAR2
3265 , x_msg_count OUT NOCOPY NUMBER
3266 , x_msg_data OUT NOCOPY VARCHAR2
3267 , x_lot_cont OUT NOCOPY BOOLEAN
3268 , x_child_lot_cont OUT NOCOPY BOOLEAN
3269 , p_inventory_item_id IN NUMBER
3270 , p_organization_id IN NUMBER
3271 )
3272 IS
3273
3274
3275 /* Cursor definition to check whether item is a valid and it's lot, child lot controlled */
3276 CURSOR c_chk_msi_attr IS
3277 SELECT lot_control_code,
3278 child_lot_flag
3279 FROM mtl_system_items
3280 WHERE inventory_item_id = p_inventory_item_id
3281 AND organization_id = p_organization_id;
3282
3283 l_chk_msi_attr_rec c_chk_msi_attr%ROWTYPE;
3284
3285 BEGIN
3286
3287 x_return_status := fnd_api.g_ret_sts_success;
3288
3289 /******************* START Item validation ********************/
3290
3291 /* Check item attributes in Mtl_system_items Table */
3292
3293 OPEN c_chk_msi_attr ;
3294 FETCH c_chk_msi_attr INTO l_chk_msi_attr_rec;
3295
3296 IF c_chk_msi_attr%NOTFOUND THEN
3297 CLOSE c_chk_msi_attr;
3298 IF (g_debug = 1) THEN
3299 print_debug('Item not found. Invalid item. Please re-enter.', 9);
3300 END IF;
3301
3302 x_lot_cont := FALSE ;
3303 x_child_lot_cont := FALSE ;
3304 x_return_status := fnd_api.g_ret_sts_error;
3305 fnd_message.set_name('INV', 'INV_INVALID_ITEM');
3306 fnd_msg_pub.ADD;
3307 RAISE fnd_api.g_exc_error;
3308
3309 ELSE
3310
3311 CLOSE c_chk_msi_attr;
3312
3313 /* If not lot controlled then error out */
3314 IF (l_chk_msi_attr_rec.lot_control_code = 1) THEN
3315 x_lot_cont := FALSE ;
3316 IF g_debug = 1 THEN
3317 print_debug('Check_Item_Attributes. Item is not lot controlled ', 9);
3318 END IF;
3319 ELSE
3320 x_lot_cont := TRUE ;
3321 IF g_debug = 1 THEN
3322 print_debug('Check_Item_Attributes. Item is lot controlled ', 9);
3323 END IF;
3324 END IF; /* l_chk_msi_attr_rec.lot_control_code = 1 */
3325
3326 /* If not child lot enabled and p_parent_lot_number IS NOT NULL then error out */
3327 IF (l_chk_msi_attr_rec.child_lot_flag = 'N' ) THEN
3328 x_child_lot_cont := FALSE ;
3329 IF g_debug = 1 THEN
3330 print_debug('Check_Item_Attributes. Item is not child lot enabled ', 9);
3331 END IF;
3332 ELSE
3333 x_child_lot_cont := TRUE ;
3334 IF g_debug = 1 THEN
3335 print_debug('Check_Item_Attributes. Item is child lot enabled ', 9);
3336 END IF;
3337 END IF; /* l_chk_msi_attr_rec.child_lot_flag = 'N' */
3338 END IF;
3339
3340
3341 /******************* End Item validation ********************/
3342 EXCEPTION
3343
3344 WHEN NO_DATA_FOUND THEN
3345 x_return_status := fnd_api.g_ret_sts_error;
3346 fnd_msg_pub.count_and_get(p_encoded => fnd_api.g_false, p_count => x_msg_count, p_data => x_msg_data);
3347 IF( x_msg_count > 1 ) THEN
3348 x_msg_data := fnd_msg_pub.get(x_msg_count, FND_API.G_FALSE);
3349 END IF;
3350 print_debug('In Check_Item_Attributes, No data found ' || SQLERRM, 9);
3351
3352 WHEN fnd_api.g_exc_error THEN
3353 x_return_status := fnd_api.g_ret_sts_error;
3354 fnd_msg_pub.count_and_get(p_encoded => fnd_api.g_false, p_count => x_msg_count, p_data => x_msg_data);
3355 IF( x_msg_count > 1 ) THEN
3356 x_msg_data := fnd_msg_pub.get(x_msg_count, FND_API.G_FALSE);
3357 END IF;
3358 print_debug('In Check_Item_Attributes, g_exc_error ' || SQLERRM, 9);
3359
3360 WHEN fnd_api.g_exc_unexpected_error THEN
3361 x_return_status := fnd_api.g_ret_sts_unexp_error;
3362 fnd_msg_pub.count_and_get(p_encoded => fnd_api.g_false, p_count => x_msg_count, p_data => x_msg_data);
3363 IF( x_msg_count > 1 ) THEN
3364 x_msg_data := fnd_msg_pub.get(x_msg_count, FND_API.G_FALSE);
3365 END IF ;
3366 print_debug('In Check_Item_Attributes, g_exc_unexpected_error ' || SQLERRM, 9);
3367
3368 WHEN OTHERS THEN
3369 x_return_status := fnd_api.g_ret_sts_unexp_error;
3370 fnd_msg_pub.count_and_get(p_encoded => fnd_api.g_false, p_count => x_msg_count, p_data => x_msg_data);
3371 IF( x_msg_count > 1 ) THEN
3372 x_msg_data := fnd_msg_pub.get(x_msg_count, FND_API.G_FALSE);
3373 END IF;
3374 print_debug('In Check_Item_Attributes, Others ' || SQLERRM, 9);
3375
3376 END Check_Item_Attributes;
3377
3378
3379 END INV_ROI_INTEGRATION_GRP ;
3380
[Home] [Help]