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