[Home] [Help]
PACKAGE BODY: APPS.GML_OPM_ROI_GRP
Source
1 PACKAGE BODY GML_OPM_ROI_GRP AS
2 /* $Header: GMLGROIB.pls 115.9 2004/06/03 20:23:28 mchandak ship $*/
3
4 g_asn_debug VARCHAR2(1):= NVL(FND_PROFILE.VALUE('PO_RVCTP_ENABLE_TRACE'),'N');
5 g_opm_restrict_rma_lots VARCHAR2(100) ;
6 g_opm_rma_lot_info_exists BOOLEAN ;
7
8 PROCEDURE print_debug(p_err_msg VARCHAR2, p_level NUMBER) IS
9 BEGIN
10 inv_mobile_helper_functions.tracelog(p_err_msg => p_err_msg, p_module => 'GML_OPM_ROI_GRP', p_level => p_level);
11 END print_debug;
12
13 PROCEDURE insert_errors(p_rti_id IN NUMBER,
14 p_group_id IN NUMBER,
15 p_header_interface_id IN NUMBER,
16 p_column_name IN VARCHAR2,
17 p_table_name IN VARCHAR2,
18 p_mesg_owner IN VARCHAR2,
19 p_Error_Message IN VARCHAR2,
20 p_Error_Message_name IN VARCHAR2,
21 p_TokenName1 IN VARCHAR2 DEFAULT NULL,
22 p_TokenValue1 IN VARCHAR2 DEFAULT NULL,
23 p_TokenName2 IN VARCHAR2 DEFAULT NULL,
24 p_TokenValue2 IN VARCHAR2 DEFAULT NULL,
25 p_TokenName3 IN VARCHAR2 DEFAULT NULL,
26 p_TokenValue3 IN VARCHAR2 DEFAULT NULL,
27 p_TokenName4 IN VARCHAR2 DEFAULT NULL,
28 p_TokenValue4 IN VARCHAR2 DEFAULT NULL
29 )
30
31 IS
32
33 PRAGMA AUTONOMOUS_TRANSACTION;
34
35 l_interface_type VARCHAR2(25) := 'RCV-856';
36 l_error_message po_interface_errors.error_message%TYPE ;
37 l_group_id NUMBER;
38 l_header_interface_id NUMBER;
39 BEGIN
40
41 l_error_message := p_error_message;
42
43 IF l_error_message IS NULL THEN
44
45 IF p_mesg_owner IS NOT NULL AND p_error_message_name IS NOT NULL THEN
46
47 fnd_message.set_name(p_mesg_owner, p_error_message_name);
48
49 IF (p_TokenName1 IS NOT NULL AND p_TokenValue1 IS NOT NULL) THEN
50 fnd_message.set_token(p_TokenName1, p_TokenValue1);
51 END IF;
52 IF (p_TokenName2 IS NOT NULL AND p_TokenValue2 IS NOT NULL) THEN
53 fnd_message.set_token(p_TokenName2, p_TokenValue2);
54 END IF;
55 IF (p_TokenName3 IS NOT NULL AND p_TokenValue3 IS NOT NULL) THEN
56 fnd_message.set_token(p_TokenName3, p_TokenValue3);
57 END IF;
58 IF (p_TokenName4 IS NOT NULL AND p_TokenValue4 IS NOT NULL) THEN
59 fnd_message.set_token(p_TokenName4, p_TokenValue4);
60 END IF;
61
62 l_error_message := Fnd_message.get;
63
64 END IF;
65
66 END IF;
67
68 IF p_group_id IS NULL THEN
69 SELECT group_id,header_interface_id
70 INTO l_group_id,l_header_interface_id
71 FROM rcv_transactions_interface
72 WHERE interface_transaction_id = p_rti_id ;
73
74 END IF;
75
76 INSERT INTO po_interface_errors(Interface_Type,
77 Interface_Transaction_Id,
78 column_name,
79 table_name,
80 error_message,
81 Error_Message_name,
82 processing_date,
83 Creation_Date,
84 Created_By,
85 Last_Update_Date,
86 Last_Updated_by,
87 Last_Update_Login,
88 Interface_Header_ID,
89 Interface_Line_Id,
90 Interface_Distribution_Id,
91 Request_Id,
92 Program_Application_id,
93 Program_Id,
94 Program_Update_date,
95 BATCH_ID)
96 VALUES
97 (l_interface_type,
98 po_interface_errors_s.NEXTVAL,
99 p_column_name,
100 p_table_name,
101 l_error_message,
102 p_Error_Message_name,
103 SYSDATE,
104 SYSDATE,
105 fnd_global.user_id,
106 SYSDATE,
107 fnd_global.user_id,
108 fnd_global.login_id,
109 NVL(p_header_interface_id,l_header_interface_id),
110 p_rti_id,
111 null,
112 fnd_global.conc_request_id,
113 fnd_global.prog_appl_id,
114 fnd_global.conc_program_id,
115 SYSDATE,
116 nvl(p_group_id,l_group_id));
117
118 -- Have to commit at the end of a successful autonomous transaction
119 COMMIT;
120
121 EXCEPTION
122 WHEN OTHERS THEN
123 IF (g_asn_debug = 'Y') THEN
124 asn_debug.put_line('Unhandled exception in gml_opm_roi_grp.insert_errors=>'||substr(sqlerrm,1,200));
125 END IF;
126 END insert_errors;
127
128
129
130 -- This procedure validates the onhand quantity for the returns and negative corrections
131 -- to deliver transactions. If the return quantity or the correction quantity is more than
132 -- what is avaliable for that item,warehouse,location combination then raise an error
133
134 PROCEDURE validate_quantity_onhand(p_lot_id IN NUMBER,
135 p_opm_item_id IN NUMBER,
136 p_organization_id IN NUMBER,
137 p_locator_id IN NUMBER,
138 p_primary_quantity IN NUMBER,
139 p_rti_id IN NUMBER,
140 x_return_status OUT NOCOPY VARCHAR2) IS
141
142 l_loct_onhand NUMBER := 0;
143 l_default_location VARCHAR2(16);
144
145 Cursor Cr_qoh_loct_not_null Is
146 Select sum(loct_onhand)
147 From ic_loct_inv ilv,ic_loct_mst ilm,ic_whse_mst iwm
148 Where ilv.item_id = p_opm_item_id
149 and ilv.whse_code = iwm.whse_code
150 and ilv.location = ilm.location
151 and iwm.mtl_organization_id = p_organization_id
152 and ilm.inventory_location_id = p_locator_id
153 and ilv.lot_id = nvl(p_lot_id,ilv.lot_id)
154 having sum(loct_onhand) > 0;
155
156 Cursor Cr_qoh_loct_null Is
157 Select sum(loct_onhand)
158 From ic_loct_inv ilv,ic_whse_mst iwm
159 Where ilv.item_id = p_opm_item_id
160 and ilv.whse_code = iwm.whse_code
161 and ilv.location = l_default_location
162 and iwm.mtl_organization_id = p_organization_id
163 and ilv.lot_id = nvl(p_lot_id,ilv.lot_id)
164 having sum(loct_onhand)> 0;
165
166 /*Bug 3117947*/
167 Cursor Cr_check_noninv IS
168 Select noninv_ind
169 From ic_item_mst
170 Where item_id = p_opm_item_id;
171
172 l_noninv_ind NUMBER :=0;
173 l_table_name VARCHAR2(35);
174 l_progress VARCHAR2(4) := '010';
175
176 --Bug# 3664014
177 V_allow_neg_inv NUMBER;
178
179 BEGIN
180
181 x_return_status := fnd_api.g_ret_sts_success;
182
183 IF (p_lot_id IS NULL) AND (g_asn_debug = 'Y') THEN
184 asn_debug.put_line('lotid=>'||p_lot_id||'item id=>'||p_opm_item_id||'orgid=>'||p_organization_id||'locatorid=>'||p_locator_id||'qty=>'||p_primary_quantity);
185 END IF;
186
187
188 IF p_opm_item_id IS NULL THEN
189 RETURN;
190 END IF;
191
192 --Fetch the GMI Allow negative inv profile option
193 --Bug# 3664014
194 V_allow_neg_inv := nvl(fnd_profile.value('IC$ALLOWNEGINV'),0);
195
196 IF (p_lot_id IS NULL) AND (g_asn_debug = 'Y') THEN
197 asn_debug.put_line('-veinv profile=>'||v_allow_neg_inv);
198 END IF;
199
200
201 --Negative inv is allowed so return.
202 IF V_allow_neg_inv = 1 THEN
203 RETURN;
204 END IF;
205
206 Open Cr_check_noninv;
207 Fetch Cr_check_noninv into l_noninv_ind;
208 IF Cr_check_noninv%NOTFOUND THEN
209 Close Cr_check_noninv;
210 RETURN;
211 END IF;
212 Close Cr_check_noninv;
213
214 l_progress := '020';
215
216
217 IF l_noninv_ind = 1 THEN
218 RETURN;
219 END IF;
220
221 IF p_locator_id is not null THEN
222 Open Cr_qoh_loct_not_null;
223 Fetch Cr_qoh_loct_not_null into l_loct_onhand;
224
225 IF (p_lot_id IS NULL) AND (g_asn_debug = 'Y') THEN
226 asn_debug.put_line('inventory onhand for locator=>'||l_loct_onhand);
227 END IF;
228
229 IF (Cr_qoh_loct_not_null%NOTFOUND) OR (abs(p_primary_quantity) > nvl(l_loct_onhand,0))
230 THEN
231 Close Cr_qoh_loct_not_null;
232 --If do not allow negative inv then give error
233 --Bug# 3664014
234 IF V_allow_neg_inv = 0 THEN
235 FND_MESSAGE.SET_NAME('GMI','IC_INVQTYNEG');
236 FND_MSG_PUB.Add;
237 x_return_status := fnd_api.g_ret_sts_error;
238 RETURN ;
239 --If set to 2 then give warning.
240 ELSIF V_allow_neg_inv = 2 THEN
241 FND_MESSAGE.SET_NAME('GMI','IC_WARNINVQTYNEG');
242 FND_MSG_PUB.Add;
243 x_return_status := 'W';
244 RETURN ;
245 END IF;
246 END IF;
247 Close Cr_qoh_loct_not_null;
248 ELSE
249 l_default_location := fnd_profile.value('IC$DEFAULT_LOCT');
250
251 Open Cr_qoh_loct_null;
252 Fetch Cr_qoh_loct_null into l_loct_onhand;
253
254 IF (p_lot_id IS NULL) AND (g_asn_debug = 'Y') THEN
255 asn_debug.put_line('inventory onhand for default locator=>'||l_loct_onhand);
256 END IF;
257
258 IF (Cr_qoh_loct_null%NOTFOUND) OR (abs(p_primary_quantity) > nvl(l_loct_onhand,0))
259 THEN
260 Close Cr_qoh_loct_null;
261 -- bug# 3664014
262 IF V_allow_neg_inv = 0 THEN
263 FND_MESSAGE.SET_NAME('GMI','IC_INVQTYNEG');
264 FND_MSG_PUB.Add;
265 x_return_status := fnd_api.g_ret_sts_error;
266 RETURN ;
267 --If set to 2 then give warning.
268 ELSIF V_allow_neg_inv = 2 THEN
269 FND_MESSAGE.SET_NAME('GMI','IC_WARNINVQTYNEG');
270 FND_MSG_PUB.Add;
271 x_return_status := 'W';
272 RETURN ;
273 END IF;
274 END IF;
275 Close Cr_qoh_loct_null;
276 END IF;
277
278 EXCEPTION
279 WHEN OTHERS THEN
280 x_return_status := fnd_api.g_ret_sts_unexp_error;
281 IF p_lot_id IS NULL THEN
282 l_table_name := 'RCV_TRANSACTIONS_INTERFACE';
283 ELSE
284 l_table_name := 'MTL_TRANSACTION_LOTS_INTERFACE';
285 END IF;
286
287 insert_errors( p_rti_id => p_rti_id,
288 p_group_id => NULL,
289 p_header_interface_id => NULL,
290 p_column_name => 'PRIMARY_QUANTITY',
291 p_table_name => l_table_name,
292 p_error_message => 'UNHANDLED EXCEPTION IN GML_OPM_ROI_GRP.VALIDATE_QUANTITY_ONHAND :' || l_progress||'-' ||
293 substr(sqlerrm,1,1000),
294 p_mesg_owner => NULL,
295 p_Error_Message_name => NULL);
296
297 IF p_lot_id IS NULL THEN
298 IF (g_asn_debug = 'Y') THEN
299 asn_debug.put_line('Unhandled exception in validate_quantity_onhand=>'||l_progress||'-'||substr(sqlerrm,1,200));
300 END IF;
301 ELSE
302 IF NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0) = 1 THEN
303 print_debug('VALIDATE_QUANTITY_ONHAND:UNHANDLED EXCEPTION'||l_progress||'-'||substr(sqlerrm,1,100), 1);
304 END IF;
305 END IF;
306 END validate_quantity_onhand;
307
308
309 PROCEDURE validate_opm_parameters(x_opm_record IN OUT NOCOPY gml_opm_roi_grp.opm_record_type) IS
310 l_item_um2 VARCHAR2(4);
311 l_return_status VARCHAR2(1);
312 l_msg_count BINARY_INTEGER;
313 l_msg_data VARCHAR2(2000);
314 l_progress VARCHAR2(4) := '010' ;
315 l_locator_id NUMBER;
316 l_opm_item_id NUMBER ;
317
318 BEGIN
319
320 IF (g_asn_debug = 'Y') THEN
321 asn_debug.put_line('Validating Process Item'||l_progress);
322 END IF;
323
324 -- bug# 3664014
325 -- added some more parameters in x_opm_record type.
326 -- validate_opm_parameters API was reading these extra parameters from
327 -- rcv_transactions_interface table which does hold the latest data. All the latest data
328 -- are in the cascaded table. Removed the select from RTI table.
329
330 l_progress := '020';
331
332 IF x_opm_record.receipt_source_code IN ('VENDOR','INTERNAL ORDER','CUSTOMER') THEN
333 BEGIN
334 IF x_opm_record.item_num is not null THEN
335 select item_um2,item_id into l_item_um2,l_opm_item_id from ic_item_mst
336 where item_no = x_opm_record.item_num ;
337 ELSE
338 select iim.item_no , iim.item_um2 , iim.item_id into x_opm_record.item_num,l_item_um2 , l_opm_item_id
339 from mtl_system_items_b msi,ic_item_mst iim
340 where msi.inventory_item_id = x_opm_record.item_id
341 and msi.organization_id = x_opm_record.to_organization_id
342 and msi.segment1 = iim.item_no ;
343 END IF;
344
345 EXCEPTION WHEN NO_DATA_FOUND THEN
346 -- In case of RMA,discrete item under process org is a valid transaction and it falls
347 -- under Discrete.To classify a given RMA transaction as OPM RMA transaction,item and
348 -- organization both should be process.
349 IF x_opm_record.receipt_source_code = 'CUSTOMER' THEN
350 x_opm_record.secondary_uom_code := NULL;
351 x_opm_record.secondary_unit_of_measure := NULL;
352 x_opm_record.secondary_quantity := NULL;
353 x_opm_record.qc_grade := NULL ;
354 RETURN ;
355 ELSE
356 x_opm_record.error_record.error_status := 'F' ;
357 x_opm_record.error_record.error_message := 'GML_OPM_ITEM_NOT_EXIST';
358
359 insert_errors(p_rti_id => x_opm_record.rti_id,
360 p_group_id => x_opm_record.group_id,
361 p_header_interface_id => x_opm_record.header_interface_id,
362 p_column_name => 'ITEM_ID',
363 p_table_name => 'RCV_TRANSACTIONS_INTERFACE',
364 p_error_message => NULL,
365 p_mesg_owner => 'GML',
366 p_Error_Message_name => 'GML_OPM_ITEM_NOT_EXIST' );
367 RETURN ;
368 END IF;
369 END ;
370
371 l_progress := '030' ;
372
373 --If its a negative correction or a return to a deliver transaction then
374 --validate the onhand quantities in OPM.
375
376 IF ((x_opm_record.transaction_type = 'CORRECT' AND x_opm_record.quantity < 0)
377 OR (x_opm_record.transaction_type IN ('RETURN TO RECEIVING','RETURN TO VENDOR','RETURN TO CUSTOMER'))
378 )
379 AND ( x_opm_record.destination_type_code = 'INVENTORY') THEN
380
381 IF (g_asn_debug = 'Y') THEN
382 asn_debug.put_line('validation primary qty against onhand'||l_progress);
383 END IF;
384
385 l_locator_id := x_opm_record.from_locator_id ;
386
387 validate_quantity_onhand(p_lot_id => NULL,
388 p_opm_item_id => l_opm_item_id,
389 p_organization_id => x_opm_record.to_organization_id,
390 p_locator_id => l_locator_id,
391 p_primary_quantity => x_opm_record.primary_quantity,
392 p_rti_id => x_opm_record.rti_id,
393 x_return_status => l_return_status );
394
395 IF (g_asn_debug = 'Y') THEN
396 asn_debug.put_line('validate_quantity_onhand done'||l_return_status);
397 END IF;
398
399 IF l_return_status <> 'S' THEN
400 -- bug 3664014 insert into po_interface_error even if its warning.
401 IF l_return_status <> 'U' THEN
402 insert_errors(p_rti_id => x_opm_record.rti_id,
403 p_group_id => x_opm_record.group_id,
404 p_header_interface_id => x_opm_record.header_interface_id,
405 p_column_name => 'PRIMARY_QUANTITY',
406 p_table_name => 'RCV_TRANSACTIONS_INTERFACE',
407 p_error_message => FND_MSG_PUB.GET(p_msg_index => FND_MSG_PUB.G_LAST,p_encoded => 'F'),
408 p_mesg_owner => NULL,
409 p_Error_Message_name => NULL );
410 END IF;
411
412 IF l_return_status <> 'W' THEN
413 x_opm_record.error_record.error_status := 'F' ;
414 IF l_return_status <> 'U' THEN
415 x_opm_record.error_record.error_message := 'IC_INVQTYNEG';
416 END IF;
417 RETURN;
418 ELSE
419 l_return_status := 'S' ;
420 END IF;
421 END IF;
422 END IF;
423
424 l_progress := '040' ;
425
426 IF l_item_um2 IS NOT NULL THEN
427 IF (x_opm_record.secondary_unit_of_measure IS NULL
428 And x_opm_record.secondary_uom_code IS NOT NULL ) THEN
429 BEGIN
430 SELECT muom.unit_of_measure INTO x_opm_record.secondary_unit_of_measure
431 FROM mtl_units_of_measure muom
432 WHERE muom.uom_code = x_opm_record.secondary_uom_code ;
433
434 EXCEPTION WHEN NO_DATA_FOUND THEN
435 x_opm_record.error_record.error_status := 'F' ;
436 x_opm_record.error_record.error_message := 'PO_PDOI_INVALID_UOM_CODE';
437 RETURN;
438 END ;
439 END IF;
440
441 IF (g_asn_debug = 'Y') THEN
442 asn_debug.put_line('Validating Secondary Qty' || l_progress);
443 END IF;
444
445 -- if secondary quantity is present, validate it else calculate it.
446 -- if secondary_unit_of_measure is not null,validate it else derive it.
447
448 l_progress := '050' ;
449
450 GML_ValidateDerive_GRP.Secondary_Qty
451 ( p_api_version => '1.0'
452 , p_init_msg_list => 'F'
453 , p_validate_ind => 'Y'
454 , p_item_no => x_opm_record.item_num
455 , p_unit_of_measure => x_opm_record.unit_of_measure
456 , p_secondary_unit_of_measure => x_opm_record.secondary_unit_of_measure
457 , p_quantity => x_opm_record.quantity
458 , p_secondary_quantity => x_opm_record.secondary_quantity
459 , x_return_status => l_return_status
460 , x_msg_count => l_msg_count
461 , x_msg_data => l_msg_data ) ;
462
463 IF (g_asn_debug = 'Y') THEN
464 asn_debug.put_line('Status of Validating Secondary Qty' || l_return_status);
465 asn_debug.put_line('secondary unit' || x_opm_record.secondary_unit_of_measure);
466 asn_debug.put_line('secondary qty' || x_opm_record.secondary_quantity);
467 END IF;
468
469 IF l_return_status <> 'S' THEN
470 x_opm_record.error_record.error_status := 'F';
471 x_opm_record.error_record.error_message := 'GML_SEC_QTY_VAL_FAILED' ;
472
473 IF l_msg_data IS NULL THEN
474 l_msg_data := FND_MSG_PUB.GET(p_msg_index => FND_MSG_PUB.G_LAST,p_encoded => 'F');
475 END IF ;
476
477 insert_errors(p_rti_id => x_opm_record.rti_id,
478 p_group_id => x_opm_record.group_id,
479 p_header_interface_id => x_opm_record.header_interface_id,
480 p_column_name => 'SECONDARY_QUANTITY',
481 p_table_name => 'RCV_TRANSACTIONS_INTERFACE',
482 p_error_message => l_msg_data,
483 p_mesg_owner => NULL,
484 p_Error_Message_name => NULL );
485
486 RETURN;
487 END IF;
488
489 l_progress := '060' ;
490
491 SELECT muom.uom_code INTO x_opm_record.secondary_uom_code
492 FROM mtl_units_of_measure muom
493 WHERE muom.unit_of_measure = x_opm_record.secondary_unit_of_measure ;
494 ELSE
495 x_opm_record.secondary_uom_code := NULL;
496 x_opm_record.secondary_unit_of_measure := NULL;
497 x_opm_record.secondary_quantity := NULL;
498
499 END IF; -- IF l_item_um2 IS NOT NULL THEN
500 END IF; -- IF x_opm_record.receipt_source_code
501
502 EXCEPTION WHEN OTHERS THEN
503 x_opm_record.error_record.error_status := 'U' ;
504 insert_errors( p_rti_id => x_opm_record.rti_id,
505 p_group_id => x_opm_record.group_id,
506 p_header_interface_id => x_opm_record.header_interface_id,
507 p_column_name => 'OPM_COLUMNS',
508 p_table_name => 'RCV_TRANSACTIONS_INTERFACE',
509 p_error_message => 'UNHANDLED EXCEPTION IN VALIDATE_OPM_PARAMETERS :' || l_progress||'-' ||
510 substr(sqlerrm,1,1000),
511 p_mesg_owner => NULL,
512 p_Error_Message_name => NULL);
513 IF (g_asn_debug = 'Y') THEN
514 asn_debug.put_line('Unhandled exception in validate_opm_parameters=>'|| l_progress||'-'||substr(sqlerrm,1,200));
515 END IF;
516
517 END validate_opm_parameters;
518
519 PROCEDURE validate_lot_attributes(p_lot_attribute_rec IN OUT NOCOPY gml_opm_roi_grp.lot_attributes_rec_type,
520 p_inventory_item_id IN NUMBER,
521 p_organization_id IN NUMBER,
522 p_trans_date IN DATE,
523 p_rti_id IN NUMBER,
524 x_return_status IN OUT NOCOPY VARCHAR2 ) IS
525
526 l_temp VARCHAR2(1);
527 l_inv_debug NUMBER := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
528 l_progress VARCHAR2(4) := '010' ;
529
530 CURSOR Cur_lot_ctrl_lot IS
531 SELECT lot_id,inactive_ind,delete_mark
532 FROM ic_lots_mst
533 WHERE lot_no = p_lot_attribute_rec.lot_no
534 AND item_id = p_lot_attribute_rec.opm_item_id ;
535
536
537 CURSOR Cur_sublot_ctrl_lot IS
538 SELECT lot_id,inactive_ind,delete_mark
539 FROM ic_lots_mst
540 WHERE lot_no = p_lot_attribute_rec.lot_no
541 AND item_id = p_lot_attribute_rec.opm_item_id
542 AND sublot_no = p_lot_attribute_rec.sublot_no ;
543
544
545 l_sublot_ctl BINARY_INTEGER;
546 l_inactive_ind BINARY_INTEGER;
547 l_delete_mark BINARY_INTEGER;
548 l_shelf_life_code NUMBER;
549 l_shelf_life NUMBER;
550 l_expire_date DATE ;
551
552 BEGIN
553
554 x_return_status := fnd_api.g_ret_sts_success;
555
556 g_default_lot := nvl(FND_PROFILE.VALUE('IC$DEFAULT_LOT'),'0') ;
557
558 IF l_inv_debug = 1 THEN
559 print_debug('Default Lot=>'||g_default_lot, 1);
560 END IF;
561
562 IF p_lot_attribute_rec.lot_no = G_DEFAULT_LOT THEN
563 FND_MESSAGE.SET_NAME('GMI','IC_INVALID_LOT');
564 FND_MSG_PUB.Add;
565 x_return_status := fnd_api.g_ret_sts_error;
566 RETURN ;
567 END IF;
568
569 SELECT sublot_ctl,shelf_life
570 INTO l_sublot_ctl,l_shelf_life
571 FROM ic_item_mst_b
572 WHERE item_id = p_lot_attribute_rec.opm_item_id ;
573
574
575 IF p_lot_attribute_rec.sublot_no IS NOT NULL AND l_sublot_ctl = 0 THEN
576 FND_MESSAGE.SET_NAME('GMI','IC_INVALID_LOT/SUBLOT');
577 FND_MSG_PUB.Add;
578 x_return_status := fnd_api.g_ret_sts_error;
579 RETURN ;
580 END IF;
581
582 l_progress := '020' ;
583
584 IF p_lot_attribute_rec.reason_code IS NOT NULL THEN
585 BEGIN
586 select 'x' into l_temp
587 from sy_reas_cds
588 where reason_code = p_lot_attribute_rec.reason_code
589 and delete_mark = 0 ;
590
591 EXCEPTION WHEN OTHERS THEN
592 FND_MESSAGE.SET_NAME('GMI','IC_API_INVALID_REASON_CODE');
593 FND_MESSAGE.SET_TOKEN('REASON_CODE',p_lot_attribute_rec.reason_code);
594 FND_MSG_PUB.Add;
595 x_return_status := fnd_api.g_ret_sts_error;
596 RETURN ;
597 END ;
598 END IF;
599
600 l_progress := '030' ;
601
602 IF p_lot_attribute_rec.new_lot = 'N' THEN
603 IF p_lot_attribute_rec.sublot_no IS NULL THEN
604 OPEN Cur_lot_ctrl_lot ;
605 FETCH Cur_lot_ctrl_lot INTO p_lot_attribute_rec.lot_id,l_inactive_ind,l_delete_mark;
606 IF Cur_lot_ctrl_lot%NOTFOUND THEN
607 CLOSE Cur_lot_ctrl_lot;
608 FND_MESSAGE.SET_NAME('GMI','IC_INVALID_LOT');
609 FND_MSG_PUB.Add;
610 x_return_status := fnd_api.g_ret_sts_error;
611 RETURN ;
612 END IF;
613 CLOSE Cur_lot_ctrl_lot ;
614 ELSE
615 OPEN Cur_sublot_ctrl_lot ;
616 FETCH Cur_sublot_ctrl_lot INTO p_lot_attribute_rec.lot_id,l_inactive_ind,l_delete_mark;
617 IF Cur_sublot_ctrl_lot%NOTFOUND THEN
618 CLOSE Cur_sublot_ctrl_lot;
619 FND_MESSAGE.SET_NAME('GMI','IC_INVALID_LOT');
620 FND_MSG_PUB.Add;
621 x_return_status := fnd_api.g_ret_sts_error;
622 RETURN ;
623 END IF;
624 CLOSE Cur_sublot_ctrl_lot ;
625 END IF;
626
627 l_progress := '040' ;
628
629 IF l_inactive_ind = 1 THEN
630 FND_MESSAGE.SET_NAME('GML','PO_INACTIVE_LOT');
631 FND_MSG_PUB.Add;
632 x_return_status := fnd_api.g_ret_sts_error;
633 RETURN ;
634 ELSIF l_delete_mark = 1 THEN
635 FND_MESSAGE.SET_NAME('GML', 'PO_LOT_DELETED');
636 FND_MSG_PUB.Add;
637 x_return_status := fnd_api.g_ret_sts_error;
638 RETURN ;
639 END IF;
640
641 ELSE -- LOT IS NEW
642 -- validate expiration date.
643 SELECT shelf_life_code INTO l_shelf_life_code
644 FROM mtl_system_items_b
645 WHERE inventory_item_id = p_inventory_item_id
646 AND organization_id = p_organization_id ;
647
648 l_progress := '050' ;
649
650 IF l_shelf_life_code = 4 and p_lot_attribute_rec.expiration_date IS NULL THEN
651 FND_MESSAGE.SET_NAME('GMI','IC_INVALID_EXPIRE_DATE');
652 FND_MSG_PUB.Add;
653 x_return_status := fnd_api.g_ret_sts_error;
654 RETURN ;
655 ELSIF l_shelf_life_code = 2 THEN
656
657 SELECT p_trans_date + l_shelf_life into l_expire_date FROM DUAL ;
658
659 -- if expiration date specified by user is different than system calculated date
660 -- enter record into error table saying expiration date will be over-written.
661
662 IF p_lot_attribute_rec.expiration_date <> l_expire_date THEN
663
664 insert_errors( p_rti_id => p_rti_id,
665 p_group_id => null,
666 p_header_interface_id => null,
667 p_column_name => 'EXPIRATION_DATE',
668 p_table_name => 'MTL_TRANSACTION_LOTS_INTERFACE',
669 p_error_message => null,
670 p_mesg_owner => 'GML',
671 p_Error_Message_name => 'GML_OVERRIDE_EXP_DATE',
672 p_TokenName1 => 'LOT_NO',
673 p_Tokenvalue1 => p_lot_attribute_rec.lot_no,
674 p_TokenName2 => 'SUBLOT_NO',
675 p_Tokenvalue2 => p_lot_attribute_rec.sublot_no);
676 END IF;
677 p_lot_attribute_rec.expiration_date := l_expire_date ;
678
679 ELSIF l_shelf_life_code = 1 and p_lot_attribute_rec.expiration_date IS NOT NULL THEN
680 p_lot_attribute_rec.expiration_date := NULL ;
681 insert_errors( p_rti_id => p_rti_id,
682 p_group_id => null,
683 p_header_interface_id => null,
684 p_column_name => 'EXPIRATION_DATE',
685 p_table_name => 'MTL_TRANSACTION_LOTS_INTERFACE',
686 p_error_message => null,
687 p_mesg_owner => 'GML',
688 p_Error_Message_name => 'GML_OVERRIDE_EXP_DATE',
689 p_TokenName1 => 'LOT_NO',
690 p_Tokenvalue1 => p_lot_attribute_rec.lot_no,
691 p_TokenName2 => 'SUBLOT_NO',
692 p_Tokenvalue2 => p_lot_attribute_rec.sublot_no);
693 END IF;
694 END IF;
695
696 EXCEPTION WHEN OTHERS THEN
697 x_return_status := fnd_api.g_ret_sts_unexp_error;
698 insert_errors( p_rti_id => p_rti_id,
699 p_group_id => null,
700 p_header_interface_id => null,
701 p_column_name => null,
702 p_table_name => 'MTL_TRANSACTION_LOTS_INTERFACE',
703 p_error_message => 'UNHANDLED EXCEPTION IN VALIDATE_LOT_ATTRIBUTES :' || l_progress||'-' ||
704 substr(sqlerrm,1,1000),
705 p_mesg_owner => NULL,
706 p_Error_Message_name => NULL);
707 IF l_inv_debug = 1 THEN
708 print_debug('Unhandled exception in validate_lot_attributes'||l_progress||'-'||substr(sqlerrm,1,100), 1);
709 END IF;
710
711 END validate_lot_attributes ;
712
713
714 PROCEDURE check_lot_status(p_lot_attribute_rec IN OUT NOCOPY gml_opm_roi_grp.lot_attributes_rec_type,
715 p_organization_id IN NUMBER,
716 p_locator_id IN NUMBER,
717 p_trans_date IN DATE,
718 p_rti_id IN NUMBER,
719 x_return_status OUT NOCOPY VARCHAR2) IS
720
721 l_location VARCHAR2(20);
722 l_whse_code VARCHAR2(5);
723 l_inv_lot_status VARCHAR2(5) := NULL;
724 l_inv_loct_onhand NUMBER := 0;
725 l_default_lot_status VARCHAR2(4);
726
727 l_trans_rec GMIGAPI.qty_rec_typ ;
728
729 l_ic_jrnl_mst_row ic_jrnl_mst%ROWTYPE;
730 l_ic_adjs_jnl_row1 ic_adjs_jnl%ROWTYPE;
731 l_ic_adjs_jnl_row2 ic_adjs_jnl%ROWTYPE;
732 l_count NUMBER;
733 l_data VARCHAR2(2000);
734 l_count_msg NUMBER;
735 l_dummy_cnt NUMBER :=0;
736 l_reason_code_security VARCHAR2(1) := 'N';
737
738 l_message_data VARCHAR2(2000);
739
740 l_inv_debug NUMBER := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
741 l_progress VARCHAR2(4) := '010' ;
742
743 Cursor Get_Reason_Code IS
744 select reason_code
745 From sy_reas_cds
746 Where delete_mark = 0
747 and (l_reason_code_security = 'Y')
748 AND (reason_code in (select reason_code from gma_reason_code_security
749 where (doc_type = 'PORC' or doc_type IS NULL) and
750 (responsibility_id = FND_GLOBAL.RESP_id or responsibility_id IS NULL)))
751 Union all
752 Select reason_code
753 From sy_reas_cds
754 Where delete_mark = 0;
755
756 l_item_status_ctl NUMBER(5);
757
758 BEGIN
759
760 x_return_status := fnd_api.g_ret_sts_success;
761
762 SELECT whse_code INTO l_whse_code
763 FROM IC_WHSE_MST
764 WHERE mtl_organization_id = p_organization_id;
765
766
767 SELECT lot_status,status_ctl
768 INTO l_default_lot_status,l_item_status_ctl
769 FROM IC_ITEM_MST
770 WHERE item_id = p_lot_attribute_rec.opm_item_id;
771
772 IF l_inv_debug = 1 THEN
773 print_debug('Item lot status '||l_default_lot_status, 1);
774 END IF;
775
776 l_progress := '020';
777
778 -- check if item is status control. If not , return
779 IF l_item_status_ctl = 0 THEN
780 RETURN;
781 END IF;
782
783 IF p_locator_id is not null THEN
784 BEGIN
785 SELECT location INTO l_location
786 FROM IC_LOCT_MST
787 WHERE inventory_location_id = p_locator_id;
788
789 EXCEPTION
790 WHEN OTHERS THEN
791 l_location := NULL;
792 END;
793 ELSE
794 l_location := fnd_profile.value('IC$DEFAULT_LOCT');
795 END IF;
796
797
798 BEGIN
799 SELECT lot_status, loct_onhand
800 INTO l_inv_lot_status, l_inv_loct_onhand
801 FROM ic_loct_inv ilv
802 WHERE ilv.item_id = p_lot_attribute_rec.opm_item_id
803 AND ilv.lot_id = p_lot_attribute_rec.lot_id
804 AND ilv.whse_code = l_whse_code
805 AND ilv.location = l_location;
806
807 EXCEPTION
808 WHEN OTHERS THEN
809 l_inv_lot_status := NULL;
810 END;
811
812 l_progress := '030';
813
814 IF l_inv_debug = 1 THEN
815 print_debug('Inventory lot status and Profile Value for Diff_status'||l_inv_lot_status||'-'||g_moved_diff_stat, 1);
816 END IF;
817
818 IF g_moved_diff_stat = 0 THEN
819 IF (l_inv_lot_status IS NOT NULL) AND (l_inv_lot_status <> l_default_lot_status) THEN
820 FND_MESSAGE.SET_NAME('GML', 'GML_CANT_RECV_DIFF_STATUS');
821 FND_MSG_PUB.Add;
822 x_return_status := fnd_api.g_ret_sts_error;
823 RETURN ;
824 END IF;
825 ELSIF g_moved_diff_stat = 2 THEN
826 IF l_inv_lot_status IS NOT NULL AND l_inv_lot_status <> l_default_lot_status
827 AND l_inv_loct_onhand = 0 THEN
828
829 SELECT s.co_code,w.orgn_code INTO l_trans_rec.co_code,l_trans_rec.orgn_code
830 FROM IC_WHSE_MST W,SY_ORGN_MST S
831 WHERE w.whse_code = l_whse_code
832 and w.orgn_code = s.orgn_code;
833
834 IF l_inv_debug = 1 THEN
835 print_debug('Inventory lot status is different than item lot status with onhand as 0', 1);
836 END IF;
837
838 l_trans_rec.trans_type := 4;
839 l_trans_rec.item_no := p_lot_attribute_rec.item_no;
840 l_trans_rec.lot_no := p_lot_attribute_rec.lot_no;
841 l_trans_rec.sublot_no := p_lot_attribute_rec.sublot_no;
842 l_trans_rec.from_whse_code := l_whse_code;
843 l_trans_rec.from_location := l_location;
844 l_trans_rec.lot_status := l_default_lot_status;
845
846 l_progress := '040';
847
848 If p_lot_attribute_rec.reason_code IS NOT NULL THEN
849 l_trans_rec.reason_code := p_lot_attribute_rec.reason_code ;
850 Else
851 l_reason_code_security := nvl(fnd_profile.value('GMA_REASON_CODE_SECURITY'), 'N');
852 Open Get_Reason_Code;
853 Fetch Get_Reason_Code into l_trans_rec.reason_code;
854 If Get_Reason_Code%NOTFOUND Then
855 Close Get_Reason_Code;
856
857 Update IC_LOCT_INV
858 Set lot_status = l_default_lot_status
859 Where item_id = p_lot_attribute_rec.opm_item_id
860 And whse_code = l_whse_code
861 And location = l_location
862 And lot_id = p_lot_attribute_rec.lot_id;
863
864 IF l_inv_debug = 1 THEN
865 print_debug('Reason Code not found exit', 1);
866 END IF;
867
868 RETURN;
869 End If;/*Get_Reason_Code%NOTFOUND*/
870
871 Close Get_Reason_Code;
872
873 End If;
874
875 l_progress := '050';
876
877 l_trans_rec.trans_qty := NULL;
878
879 l_trans_rec.trans_date := p_trans_date ;
880
881 IF l_trans_rec.trans_date IS NULL
882 THEN
883 l_trans_rec.trans_date := SYSDATE;
884 END IF;
885
886 l_trans_rec.user_name := FND_GLOBAL.USER_NAME;
887
888 -- Set the context for the GMI APIs
889 IF( NOT Gmigutl.Setup(l_trans_rec.user_name))
890 THEN
891 x_return_status := fnd_api.g_ret_sts_error;
892 IF l_inv_debug = 1 THEN
893 print_debug('Inventory API SETUP Failed for User Name=>'|| l_trans_rec.user_name, 1);
894 END IF;
895 RETURN ;
896 END IF;
897
898 l_progress := '060';
899
900 Gmipapi.Inventory_Posting
901 ( p_api_version => 3.0
902 , p_init_msg_list => 'F'
903 , p_commit => 'F'
904 , p_validation_level => 100
905 , p_qty_rec => l_trans_rec
906 , x_ic_jrnl_mst_row => l_ic_jrnl_mst_row
907 , x_ic_adjs_jnl_row1 => l_ic_adjs_jnl_row1
908 , x_ic_adjs_jnl_row2 => l_ic_adjs_jnl_row2
909 , x_return_status => x_return_status
910 , x_msg_count => l_count
911 , x_msg_data => l_data
912 );
913
914 IF l_inv_debug = 1 THEN
915 print_debug('Inventory Posting API status => '||x_return_status, 1);
916 END IF;
917
918 IF ( x_return_status <> 'S' )
919 THEN
920 RETURN;
921 END IF;
922
923 ELSIF l_inv_lot_status IS NOT NULL AND l_inv_lot_status <> l_default_lot_status
924 AND l_inv_loct_onhand <> 0 THEN
925 FND_MESSAGE.SET_NAME('GML', 'GML_CANT_RECV_DIFF_STATUS');
926 FND_MSG_PUB.Add;
927 x_return_status := fnd_api.g_ret_sts_error;
928 RETURN ;
929 END IF; -- IF l_inv_lot_status IS NOT NULL
930 END IF; -- IF g_moved_diff_stat = 0 THEN
931
932 EXCEPTION WHEN OTHERS THEN
933 x_return_status := fnd_api.g_ret_sts_unexp_error;
934 insert_errors( p_rti_id => p_rti_id,
935 p_group_id => null,
936 p_header_interface_id => null,
937 p_column_name => null,
938 p_table_name => 'MTL_TRANSACTION_LOTS_INTERFACE',
939 p_error_message => 'UNHANDLED EXCEPTION IN CHECK_LOT_STATUS :' || l_progress||'-' ||
940 substr(sqlerrm,1,1000),
941 p_mesg_owner => NULL,
942 p_Error_Message_name => NULL);
943 IF l_inv_debug = 1 THEN
944 print_debug('Unhandled exception in check_lot_status'||l_progress||'-'||substr(sqlerrm,1,100), 1);
945 END IF;
946
947 END check_lot_status;
948
949
950 PROCEDURE create_new_lot(p_new_lot_rec IN GMIGAPI.lot_rec_typ,
951 p_organization_id IN NUMBER,
952 p_vendor_id IN NUMBER,
953 p_vendor_site_id IN NUMBER,
954 p_from_unit_of_measure IN VARCHAR2,
955 p_to_unit_of_measure IN VARCHAR2,
956 p_type_factor IN NUMBER,
957 p_rti_id IN NUMBER,
958 x_return_status OUT NOCOPY VARCHAR2 ) IS
959
960 l_new_lot_rec GMIGAPI.lot_rec_typ;
961 l_ic_lots_mst_rec ic_lots_mst%ROWTYPE;
962 l_ic_lots_cpg_rec ic_lots_cpg%ROWTYPE;
963 l_msg_count NUMBER(3);
964 l_msg_data VARCHAR2(2000);
965 l_inv_debug NUMBER := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
966 l_progress VARCHAR2(4) := '010' ;
967 l_to_um_type VARCHAR2(10);
968 l_to_um_code VARCHAR2(4);
969 l_from_um_type VARCHAR2(10);
970 l_from_um_code VARCHAR2(4);
971 l_dualum_ind NUMBER(1);
972
973 BEGIN
974
975 x_return_status := fnd_api.g_ret_sts_success;
976 l_new_lot_rec := p_new_lot_rec ;
977
978 l_new_lot_rec.user_name := FND_GLOBAL.USER_NAME;
979
980 -- the context for the GMI APIs
981 IF( NOT Gmigutl.Setup(l_new_lot_rec.user_name))
982 THEN
983 x_return_status := fnd_api.g_ret_sts_error;
984 IF l_inv_debug = 1 THEN
985 print_debug('Inventory API SETUP Failed for User Name=>'|| l_new_lot_rec.user_name, 1);
986 END IF;
987 RETURN ;
988 END IF;
989
990 l_progress := '020';
991
992 IF p_vendor_id IS NOT NULL and p_vendor_site_id IS NOT NULL THEN
993
994 BEGIN
995 Select pvm.vendor_no INTO l_new_lot_rec.shipvendor_no
996 From po_Vend_mst pvm,ic_whse_mst iwm,sy_orgn_mst som
997 Where pvm.OF_VENDOR_ID = p_vendor_id
998 and pvm.OF_VENDOR_SITE_ID = p_vendor_site_id
999 and iwm.mtl_organization_id = p_organization_id
1000 and iwm.orgn_code = som.orgn_code
1001 and som.co_code = pvm.co_code;
1002
1003 EXCEPTION WHEN OTHERS THEN
1004 l_new_lot_rec.shipvendor_no := NULL ;
1005 END ;
1006
1007 END IF;
1008
1009 l_progress := '030';
1010
1011 GMIPAPI.Create_Lot( p_api_version => 3.0
1012 , p_init_msg_list => 'F'
1013 , p_commit => 'F'
1014 , p_validation_level => FND_API.G_VALID_LEVEL_FULL
1015 , p_lot_rec => l_new_lot_rec
1016 , x_ic_lots_mst_row => l_ic_lots_mst_rec
1017 , x_ic_lots_cpg_row => l_ic_lots_cpg_rec
1018 , x_return_status => x_return_status
1019 , x_msg_count => l_msg_count
1020 , x_msg_data => l_msg_data
1021 );
1022
1023 IF l_inv_debug = 1 THEN
1024 print_debug('Create Lot status=>'|| x_return_status, 1);
1025 END IF;
1026
1027 IF x_return_status <> 'S' THEN
1028 RETURN;
1029 END IF;
1030
1031 l_progress := '040';
1032
1033 -- create lot specific conversion for dual uom item only.
1034 IF p_from_unit_of_measure IS NOT NULL AND p_to_unit_of_measure IS NOT NULL THEN
1035 -- check class of from and to uom. call conversion API only if they
1036 -- belong to different class.Also don't create for fixed UOM type(dualum_ind = 1)
1037 -- API requires OPM um_code so fetch OPM um code
1038 IF fnd_profile.value('GML_ENABLE_DYN_LOT_SPEC_CONV') = 'Y' THEN
1039
1040 SELECT um_type , um_code INTO l_from_um_type , l_from_um_code
1041 FROM SY_UOMS_MST
1042 WHERE unit_of_measure = p_from_unit_of_measure ;
1043
1044 l_progress := '050';
1045
1046 SELECT um_type , um_code INTO l_to_um_type , l_to_um_code
1047 FROM SY_UOMS_MST
1048 WHERE unit_of_measure = p_to_unit_of_measure ;
1049
1050 SELECT dualum_ind INTO l_dualum_ind
1051 FROM IC_ITEM_MST_B
1052 WHERE item_no = l_new_lot_rec.item_no ;
1053
1054
1055 IF (l_from_um_type <> l_to_um_type) AND (l_dualum_ind > 1) THEN
1056
1057 PO_GML_DB_COMMON.CREATE_LOT_SPECIFIC_CONVERSION(
1058 x_item_number => l_new_lot_rec.item_no,
1059 x_lot_number => l_new_lot_rec.lot_no,
1060 x_sublot_number => l_new_lot_rec.sublot_no,
1061 x_from_uom => l_from_um_code,
1062 x_to_uom => l_to_um_code,
1063 x_type_factor => p_type_factor,
1064 x_status => x_return_status,
1065 x_data => l_msg_data);
1066
1067 IF l_inv_debug = 1 THEN
1068 print_debug('Create Lot Specific Conversion status=>'|| x_return_status, 1);
1069 END IF;
1070
1071 IF x_return_status <> 'S' THEN
1072 RETURN;
1073 END IF;
1074
1075 END IF ;
1076 END IF ; -- IF fnd_profile.value('GML_ENABLE_DYN_LOT_SPEC_CONV') = 'Y' THEN
1077 END IF;
1078
1079
1080 EXCEPTION
1081 WHEN OTHERS THEN
1082 x_return_status := fnd_api.g_ret_sts_unexp_error;
1083 insert_errors( p_rti_id => p_rti_id,
1084 p_group_id => null,
1085 p_header_interface_id => null,
1086 p_column_name => null,
1087 p_table_name => 'MTL_TRANSACTION_LOTS_INTERFACE',
1088 p_error_message => 'UNHANDLED EXCEPTION IN CREATE_NEW_LOT :' || l_progress||'-' ||
1089 substr(sqlerrm,1,1000),
1090 p_mesg_owner => NULL,
1091 p_Error_Message_name => NULL);
1092 IF l_inv_debug = 1 THEN
1093 print_debug('Unhandled exception in create_new_lot'||l_progress||'-'||substr(sqlerrm,1,100), 1);
1094 END IF;
1095 END create_new_lot ;
1096
1097 -- ## OPM RMA ##
1098 FUNCTION opm_rma_lot_info_exists(p_oe_header_id IN NUMBER,
1099 p_oe_line_id IN NUMBER) RETURN BOOLEAN
1100 IS
1101
1102
1103 l_dummy NUMBER(1);
1104
1105 CURSOR Cr_lot_exists_line IS
1106 Select 1
1107 From oe_lot_serial_numbers
1108 Where (line_id = p_oe_line_id
1109 or line_set_id =
1110 (select line_set_id
1111 from oe_order_lines_all
1112 where line_id = p_oe_line_id
1113 and header_id = p_oe_header_id)
1114 );
1115
1116 BEGIN
1117
1118 IF p_oe_line_id IS NOT NULL THEN
1119 OPEN Cr_lot_exists_line;
1120 FETCH Cr_lot_exists_line INTO l_dummy;
1121 IF Cr_lot_exists_line%NOTFOUND THEN
1122 CLOSE Cr_lot_exists_line;
1123 RETURN FALSE;
1124 ELSE
1125 CLOSE Cr_lot_exists_line;
1126 RETURN TRUE;
1127 END IF;
1128 ELSE
1129 RETURN FALSE;
1130 END IF;
1131
1132 END opm_rma_lot_info_exists;
1133
1134 PROCEDURE opm_rma_valid_lot(p_oe_order_header_id IN NUMBER,
1135 p_oe_order_line_id IN NUMBER,
1136 p_lot_no IN VARCHAR2,
1137 p_sublot_no IN VARCHAR2,
1138 p_rti_id IN NUMBER,
1139 x_line_set_id OUT NOCOPY NUMBER,
1140 x_oe_lot_quantity OUT NOCOPY NUMBER,
1141 x_return_status OUT NOCOPY VARCHAR2 ) IS
1142
1143 l_inv_debug NUMBER := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
1144
1145 Cursor Cr_rma_lots Is
1146 Select o.quantity
1147 from oe_lot_serial_numbers o
1148 Where (o.line_id = p_oe_order_line_id
1149 OR
1150 o.line_set_id = x_line_set_id )
1151 AND o.lot_number = p_lot_no
1152 AND o.sublot_number = p_sublot_no ;
1153
1154 BEGIN
1155
1156 IF g_opm_rma_lot_info_exists AND
1157 g_opm_restrict_rma_lots IN ('RESTRICTED_WITH_WARNING','RESTRICTED') THEN
1158
1159 -- check whether lot specified by user is existing in the Sales Order Line.
1160
1161 SELECT line_set_id INTO x_line_set_id
1162 FROM oe_order_lines_all
1163 WHERE header_id = p_oe_order_header_id
1164 AND line_id = p_oe_order_line_id ;
1165
1166 IF l_inv_debug = 1 THEN
1167 print_debug('Line Set Id=>'||x_line_set_id, 1);
1168 END IF;
1169
1170 OPEN Cr_rma_lots ;
1171 FETCH Cr_rma_lots INTO x_oe_lot_quantity ;
1172 IF Cr_rma_lots%NOTFOUND THEN
1173 CLOSE Cr_rma_lots ;
1174 x_oe_lot_quantity := NULL ;
1175 IF l_inv_debug = 1 THEN
1176 print_debug('Lot Different From RMA Lot', 1);
1177 END IF;
1178
1179 IF g_opm_restrict_rma_lots IN ('RESTRICTED') THEN
1180 FND_MESSAGE.SET_NAME('GML','GML_DIFF_RMA_LOT');
1181 FND_MSG_PUB.Add;
1182 x_return_status := fnd_api.g_ret_sts_error;
1183 RAISE FND_API.G_EXC_ERROR;
1184 ELSE -- warning.put into interface table.
1185 insert_errors( p_rti_id => p_rti_id,
1186 p_group_id => null,
1187 p_header_interface_id => null,
1188 p_column_name => 'LOT_NUMBER',
1189 p_table_name => 'MTL_TRANSACTION_LOTS_INTERFACE',
1190 p_error_message => null,
1191 p_mesg_owner => 'GML',
1192 p_Error_Message_name => 'GML_DIFF_RMA_LOT'
1193 );
1194 END IF;
1195 ELSE
1196 CLOSE Cr_rma_lots ;
1197 IF l_inv_debug = 1 THEN
1198 print_debug('Lot Found In RMA Lot', 1);
1199 END IF;
1200 END IF;
1201 END IF; -- IF g_opm_rma_lot_info_exists THEN
1202
1203 END opm_rma_valid_lot ;
1204
1205 PROCEDURE validate_rma_quantity(p_opm_item_id IN NUMBER,
1206 p_lot_id IN NUMBER,
1207 p_lot_no IN VARCHAR2,
1208 p_sublot_no IN VARCHAR2,
1209 p_oe_order_header_id IN NUMBER,
1210 p_oe_order_line_id IN NUMBER,
1211 p_lot_qty IN NUMBER,
1212 p_unit_of_measure IN VARCHAR2,
1213 p_rma_lot_qty IN NUMBER,
1214 p_rma_lot_uom IN VARCHAR2,
1215 p_line_set_id IN NUMBER,
1216 p_rti_id IN NUMBER,
1217 x_allowed OUT NOCOPY VARCHAR2,
1218 x_allowed_quantity OUT NOCOPY NUMBER,
1219 x_return_status OUT NOCOPY VARCHAR2 ) IS
1220
1221 l_inv_debug NUMBER := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
1222
1223 BEGIN
1224 IF g_opm_restrict_rma_lots IN ('RESTRICTED_WITH_WARNING','RESTRICTED') AND
1225 g_opm_rma_lot_info_exists AND p_rma_lot_qty IS NOT NULL THEN
1226
1227 IF l_inv_debug = 1 THEN
1228 print_debug('parameters for validate_rma_lot_quantites lot_id=>'||p_lot_id, 1);
1229 print_debug('parameters for validate_rma_lot_quantites lot_qty_by_user=>'||p_lot_qty, 1);
1230 print_debug('parameters for validate_rma_lot_quantites rma_lot_qty=>'||p_rma_lot_qty, 1);
1231 END IF;
1232 gml_rcv_db_common.validate_rma_lot_quantities(
1233 p_init_msg_list => 'F',
1234 p_api_version => 1.0,
1235 p_opm_item_id => p_opm_item_id,
1236 p_lot_id => p_lot_id,
1237 p_lot_no => p_lot_no,
1238 p_sublot_no => p_sublot_no,
1239 p_oe_header_id => p_oe_order_header_id,
1240 p_oe_line_id => p_oe_order_line_id ,
1241 p_trx_qty => p_lot_qty ,
1242 p_trx_uom => p_unit_of_measure ,
1243 p_rma_lot_qty => p_rma_lot_qty,
1244 p_rma_lot_uom => p_rma_lot_uom,
1245 p_line_set_id => p_line_set_id,
1246 X_allowed => x_allowed,
1247 X_allowed_quantity => x_allowed_quantity,
1248 x_return_status => x_return_status);
1249
1250 IF l_inv_debug = 1 THEN
1251 print_debug('validate_rma_lot_quantites allowed and status=>'||x_allowed||'-'||x_return_status, 1);
1252 END IF;
1253
1254 IF x_return_status <> 'S' THEN
1255 return;
1256 END IF;
1257
1258 IF x_allowed = 'N' THEN
1259 IF g_opm_restrict_rma_lots IN ('RESTRICTED_WITH_WARNING') THEN
1260 insert_errors( p_rti_id => p_rti_id,
1261 p_group_id => null,
1262 p_header_interface_id => null,
1263 p_column_name => 'TRANSACTION_QUANTITY',
1264 p_table_name => 'MTL_TRANSACTION_LOTS_INTERFACE',
1265 p_error_message => null,
1266 p_mesg_owner => 'GML',
1267 p_Error_Message_name => 'GML_DIFF_RMA_QTY',
1268 p_TokenName1 => 'S1',
1269 p_TokenValue1 => x_allowed_quantity
1270 );
1271 ELSE
1272 /*Give error message that the quantity entered Is greater than the qty entered in the RMA for this lot */
1273 FND_MESSAGE.SET_NAME('GML', 'GML_DIFF_RMA_QTY');
1274 FND_MESSAGE.SET_TOKEN('S1',x_allowed_quantity);
1275 FND_MSG_PUB.Add;
1276 x_return_status := fnd_api.g_ret_sts_error;
1277 END IF;
1278 END IF; -- IF x_allowed = 'N' THEN
1279 END IF; -- IF g_opm_restrict_rma_lots IN ('RESTRICTED_WITH_WARNING','RESTRICTED')
1280
1281 END validate_rma_quantity;
1282
1283 PROCEDURE validate_opm_lot( p_api_version IN NUMBER,
1284 p_init_msg_lst IN VARCHAR2 := FND_API.G_FALSE,
1285 p_mtlt_rowid IN ROWID,
1286 p_new_lot IN VARCHAR2,
1287 p_opm_item_id IN NUMBER,
1288 p_item_no IN VARCHAR2,
1289 p_lots_specified_on_parent IN VARCHAR2,
1290 p_lot_id IN NUMBER,
1291 p_parent_txn_type IN VARCHAR2 DEFAULT NULL,
1292 p_grand_parent_txn_type IN VARCHAR2 DEFAULT NULL,
1293 x_return_status OUT NOCOPY VARCHAR2,
1294 x_msg_data OUT NOCOPY VARCHAR2,
1295 x_msg_count OUT NOCOPY NUMBER
1296 ) IS
1297
1298 l_api_name CONSTANT VARCHAR2(30) := 'Validate_Opm_Lot';
1299 l_api_version CONSTANT NUMBER := 1.0;
1300 l_pkg_name CONSTANT VARCHAR2(30) := 'GML_OPM_ROI_GRP';
1301
1302 l_transaction_type rcv_transactions_interface.transaction_type%TYPE ;
1303 l_source_document_code rcv_transactions_interface.source_document_code%TYPE ;
1304 l_item_id NUMBER;
1305 l_org_id NUMBER;
1306 l_to_locator_id NUMBER;
1307 l_from_locator_id NUMBER;
1308 l_locator_id NUMBER;
1309 l_unit_of_measure rcv_transactions_interface.unit_of_measure%TYPE;
1310 l_secondary_unit_of_measure rcv_transactions_interface.secondary_unit_of_measure%TYPE;
1311 l_validation_flag rcv_transactions_interface.validation_flag%TYPE;
1312 l_lot_attribute_rec gml_opm_roi_grp.lot_attributes_rec_type ;
1313
1314 l_new_lot_rec GMIGAPI.lot_rec_typ;
1315 l_lot_no ic_lots_mst.lot_no%TYPE;
1316 l_sublot_no ic_lots_mst.sublot_no%TYPE;
1317 l_lot_expiration_date DATE ;
1318 l_lot_quantity NUMBER;
1319 l_lot_secondary_quantity NUMBER;
1320 l_reason_code VARCHAR2(4);
1321 l_vendor_lot_no_on_lot ic_lots_mst.vendor_lot_no%TYPE;
1322 l_vendor_lot_no_on_line ic_lots_mst.vendor_lot_no%TYPE;
1323 l_vendor_id NUMBER;
1324 l_vendor_site_id NUMBER;
1325 l_transaction_date DATE ;
1326 l_rti_id NUMBER;
1327
1328 l_lot_desc ic_lots_mst.lot_desc%TYPE;
1329
1330 l_same_lot_count BINARY_INTEGER;
1331 l_parent_transaction_id NUMBER;
1332 l_shipment_header_id NUMBER;
1333 l_shipment_line_id NUMBER;
1334 l_net_received_lot_qty NUMBER;
1335 l_comment rcv_transactions.comments%TYPE;
1336 l_lot_primary_quantity NUMBER;
1337 l_old_recv_qty NUMBER;
1338 l_recv_id NUMBER;
1339 l_recvline_id NUMBER;
1340 l_old_rtrn_qty NUMBER;
1341 l_cr_avaliable_qty NUMBER;
1342 l_temp VARCHAR2(1);
1343 l_rti_primary_qty NUMBER;
1344
1345 l_inv_debug NUMBER := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
1346 l_progress VARCHAR2(4) := '010' ;
1347 l_update_lot_qty BOOLEAN := FALSE;
1348
1349 l_oe_order_header_id NUMBER;
1350 l_oe_order_line_id NUMBER;
1351 l_line_set_id NUMBER;
1352 l_oe_lot_quantity NUMBER := NULL;
1353 l_allowed VARCHAR2(1);
1354 l_allowed_quantity NUMBER;
1355 l_total_no_of_lots NUMBER;
1356 l_rti_quantity NUMBER;
1357 l_rti_secondary_quantity NUMBER;
1358
1359 Cursor Cr_get_total_qty_available IS
1360 SELECT sum(itp.trans_qty)
1361 FROM rcv_transactions rt , ic_tran_pnd itp
1362 WHERE
1363 rt.shipment_header_id = l_shipment_header_id
1364 AND rt.shipment_line_id = l_shipment_line_id
1365 AND itp.doc_id = rt.shipment_header_id
1366 AND itp.line_id = rt.transaction_id
1367 AND itp.doc_type = 'PORC'
1368 AND itp.lot_id = p_lot_id ;
1369
1370
1371 Cursor Cr_old_recv_qty Is
1372 Select sum(itp.trans_qty),
1373 itp.doc_id,
1374 itp.line_id
1375 from rcv_transactions rcv,
1376 gml_recv_trans_map grm,
1377 ic_tran_pnd itp
1378 where rcv.transaction_id = l_parent_transaction_id
1379 and rcv.comments = 'OPM RECEIPT'
1380 and rcv.interface_transaction_id = grm.interface_transaction_id
1381 and itp.doc_type = 'RECV'
1382 and grm.recv_id = itp.doc_id
1383 and grm.line_id = itp.line_id
1384 and itp.lot_id = p_lot_id
1385 group by itp.doc_id,itp.line_id
1386 having sum(itp.trans_qty) > 0 ;
1387
1388 Cursor Cr_old_rtrn_qty Is
1389 Select sum(itp.trans_qty) qty
1390 from ic_tran_pnd itp,
1391 po_Rtrn_dtl d,
1392 po_rtrn_hdr h
1393 where h.return_id = d.return_id
1394 and d.recv_id = l_recv_id
1395 and d.recvline_id = l_recvline_id
1396 and h.delete_mark <> -1
1397 and d.return_id = itp.doc_id
1398 and d.line_id = itp.line_id
1399 and itp.doc_type = 'RTRN'
1400 and itp.lot_id = p_lot_id
1401 and itp.delete_mark <> 1;
1402
1403 BEGIN
1404 x_return_status := fnd_api.g_ret_sts_success;
1405
1406 IF NOT FND_API.Compatible_API_Call (l_api_version,
1407 p_api_version,
1408 l_api_name,
1409 l_pkg_name )
1410 THEN
1411 RAISE FND_API.G_EXC_ERROR;
1412 END IF;
1413
1414 -- Initialize message list if p_init_msg_list is set to TRUE.
1415 IF FND_API.to_Boolean( p_init_msg_lst ) THEN
1416 FND_MSG_PUB.initialize;
1417 END IF;
1418
1419 l_progress := '020' ;
1420
1421 SELECT product_transaction_id,
1422 Ltrim(Rtrim(lot_number)) ,
1423 Ltrim(Rtrim(sublot_num)) ,
1424 lot_expiration_date,
1425 transaction_quantity,
1426 primary_quantity,
1427 secondary_quantity,
1428 reason_code,
1429 substr(ltrim(rtrim(supplier_lot_number)),1,32),
1430 substr(description,1,40)
1431 INTO l_rti_id ,
1432 l_lot_no ,
1433 l_sublot_no ,
1434 l_lot_expiration_date ,
1435 l_lot_quantity ,
1436 l_lot_primary_quantity ,
1437 l_lot_secondary_quantity ,
1438 l_reason_code ,
1439 l_vendor_lot_no_on_lot ,
1440 l_lot_desc
1441 FROM MTL_TRANSACTION_LOTS_TEMP
1442 WHERE rowid = p_mtlt_rowid ;
1443
1444 l_progress := '030' ;
1445
1446 IF l_inv_debug = 1 THEN
1447 print_debug('Lot record fetched with quantity =>'||l_lot_no||'-'||l_sublot_no||'-'||l_lot_quantity, 1);
1448 END IF;
1449
1450 SELECT TRANSACTION_TYPE
1451 , SOURCE_DOCUMENT_CODE
1452 , item_id
1453 , TO_ORGANIZATION_ID
1454 , locator_id
1455 , from_locator_id
1456 , unit_of_measure
1457 , secondary_unit_of_measure
1458 , transaction_date
1459 , vendor_lot_num
1460 , vendor_id
1461 , vendor_site_id
1462 , parent_transaction_id
1463 , shipment_header_id
1464 , shipment_line_id
1465 , Nvl(primary_quantity, quantity)
1466 , validation_flag
1467 , oe_order_header_id
1468 , oe_order_line_id
1469 , quantity
1470 , secondary_quantity
1471 INTO L_TRANSACTION_TYPE
1472 , L_SOURCE_DOCUMENT_CODE
1473 , l_item_id
1474 , l_org_id
1475 , l_to_locator_id
1476 , l_from_locator_id
1477 , l_unit_of_measure
1478 , l_secondary_unit_of_measure
1479 , l_transaction_date
1480 , l_vendor_lot_no_on_line
1481 , l_vendor_id
1482 , l_vendor_site_id
1483 , l_parent_transaction_id
1484 , l_shipment_header_id
1485 , l_shipment_line_id
1486 , l_rti_primary_qty
1487 , l_validation_flag
1488 , l_oe_order_header_id
1489 , l_oe_order_line_id
1490 , l_rti_quantity
1491 , l_rti_secondary_quantity
1492 FROM RCV_TRANSACTIONS_INTERFACE
1493 WHERE INTERFACE_TRANSACTION_ID = l_rti_id;
1494
1495 IF l_inv_debug = 1 THEN
1496 print_debug('Interface Record fetched =>'||l_rti_id, 1);
1497 print_debug('Source Document Code =>'||l_source_document_code, 1);
1498 print_debug('to_Locator Id and from_locator_id =>'||l_to_locator_id||'-'||l_from_locator_id, 1);
1499 print_debug('Transaction Type =>'||l_transaction_type, 1);
1500 print_debug('Secondary UOM =>'||l_secondary_unit_of_measure, 1);
1501 print_debug('Shipment header/line Id =>'||l_shipment_header_id||'-'||l_shipment_line_id, 1);
1502 END IF;
1503
1504 IF nvl(l_validation_flag,'N') = 'N' THEN
1505 IF l_inv_debug = 1 THEN
1506 print_debug('Return back. Coming from Desktop. No need to validate the lots', 1);
1507 END IF;
1508 RETURN ;
1509 END IF;
1510
1511 l_progress := '040' ;
1512 -- check for duplicate lots.
1513
1514 SELECT count(1) INTO l_same_lot_count
1515 FROM MTL_TRANSACTION_LOTS_TEMP
1516 WHERE
1517 PRODUCT_CODE = 'RCV'
1518 AND PRODUCT_TRANSACTION_ID = l_rti_id
1519 AND Ltrim(Rtrim(lot_number)) = l_lot_no
1520 AND ((sublot_num IS NULL AND l_sublot_no IS NULL) OR (Ltrim(Rtrim(sublot_num)) = l_sublot_no )) ;
1521
1522 IF l_same_lot_count > 1 THEN
1523 FND_MESSAGE.SET_NAME('GML', 'GML_LOT_SUBLOT_USED');
1524 FND_MSG_PUB.Add;
1525 x_return_status := fnd_api.g_ret_sts_error;
1526 RAISE FND_API.G_EXC_ERROR;
1527 END IF;
1528
1529 SELECT count(1) INTO l_total_no_of_lots
1530 FROM MTL_TRANSACTION_LOTS_TEMP
1531 WHERE
1532 PRODUCT_CODE = 'RCV'
1533 AND PRODUCT_TRANSACTION_ID = l_rti_id ;
1534
1535 -- IF there is only one lot for the receipt line and receipt line qty = lot qty
1536 -- default the secondary qty from line to lot record if secondary lot qty is null.
1537 IF l_total_no_of_lots = 1 and (l_rti_quantity = l_lot_quantity) AND l_lot_secondary_quantity IS NULL
1538 AND l_rti_secondary_quantity IS NOT NULL THEN
1539
1540 IF l_inv_debug = 1 THEN
1541 print_debug('Defaulting Lot Secondary Qty from Receipt Line=>'||l_rti_secondary_quantity, 1);
1542 END IF;
1543
1544 l_lot_secondary_quantity := l_rti_secondary_quantity ;
1545
1546 UPDATE mtl_transaction_lots_temp
1547 SET secondary_quantity = l_lot_secondary_quantity
1548 WHERE rowid = p_mtlt_rowid ;
1549
1550 END IF;
1551
1552
1553 l_lot_attribute_rec.opm_item_id := p_opm_item_id ;
1554 l_lot_attribute_rec.item_no := p_item_no ;
1555 l_lot_attribute_rec.lot_no := l_lot_no;
1556 l_lot_attribute_rec.sublot_no := l_sublot_no;
1557 l_lot_attribute_rec.expiration_date := l_lot_expiration_date;
1558 l_lot_attribute_rec.reason_code := l_reason_code ;
1559 l_lot_attribute_rec.new_lot := p_new_lot ;
1560
1561 l_progress := '050' ;
1562
1563 IF l_source_document_code = 'RMA' THEN
1564 g_opm_restrict_rma_lots := fnd_profile.value('GMI_RMA_LOT_RESTRICT') ;
1565 IF l_inv_debug = 1 THEN
1566 print_debug('GMI_RMA_LOT_RESTRICT =>'||g_opm_restrict_rma_lots, 1);
1567 END IF;
1568 IF g_opm_restrict_rma_lots IN ('RESTRICTED_WITH_WARNING','RESTRICTED') THEN
1569 -- check whether lot is specified for the Sales ORder Line.
1570 -- If there is no lot , then user can receive into any valid lot.
1571 g_opm_rma_lot_info_exists := opm_rma_lot_info_exists(
1572 p_oe_header_id => l_oe_order_header_id ,
1573 p_oe_line_id => l_oe_order_line_id );
1574 ELSE
1575 g_opm_rma_lot_info_exists := FALSE ;
1576 END IF;
1577 END IF;
1578
1579 IF (l_transaction_type IN ('RECEIVE','ACCEPT','REJECT','TRANSFER','DELIVER'))
1580 OR (l_transaction_type = 'SHIP' and l_source_document_code = 'PO') THEN
1581 IF l_source_document_code = 'RMA' THEN
1582 -- lots can be entered at any time - receive,accept, reject , transfer, deliver.
1583 opm_rma_valid_lot(p_oe_order_header_id => l_oe_order_header_id ,
1584 p_oe_order_line_id => l_oe_order_line_id,
1585 p_lot_no => l_lot_no,
1586 p_sublot_no => l_sublot_no,
1587 p_rti_id => l_rti_id,
1588 x_line_set_id => l_line_set_id,
1589 x_oe_lot_quantity => l_oe_lot_quantity,
1590 x_return_status => x_return_status );
1591
1592 -- no need to check x_return_status since the above API will raise exception in case of error.
1593
1594 END IF; -- IF l_source_document_code = 'RMA' THEN
1595
1596 l_progress := '060' ;
1597
1598 -- lot attributes will return lot_id in case of existing lot.
1599 validate_lot_attributes(p_lot_attribute_rec => l_lot_attribute_rec,
1600 p_inventory_item_id => l_item_id,
1601 p_organization_id => l_org_id,
1602 p_trans_date => l_transaction_date,
1603 p_rti_id => l_rti_id,
1604 x_return_status => x_return_status
1605 );
1606
1607 IF l_inv_debug = 1 THEN
1608 print_debug('Validate lot attribute status for transaction type '||l_transaction_type||' =>'||x_return_status, 1);
1609 print_debug('lot id=>'||l_lot_attribute_rec.lot_id, 1);
1610 END IF;
1611
1612 IF x_return_status <> 'S' THEN
1613 RAISE FND_API.G_EXC_ERROR;
1614 END IF;
1615
1616 l_progress := '065' ;
1617
1618 -- for restricted , it will come here only if lots are valid.
1619 -- for restricted with warning, it can come here even if lot specified
1620 -- by user is different than RMA lots. So validate the quantity
1621 -- only if user lot is existing in RMA by using l_oe_lot_quantity IS NOT NULL
1622
1623 IF l_source_document_code = 'RMA' AND l_transaction_type IN ('RECEIVE','DELIVER') THEN
1624 l_progress := '066' ;
1625 -- always call validate_rma_quantity before validate_lot_attributes.
1626 -- validate_lot_attributes gives a lot_id which in turn is used in validate_rma_quantity
1627
1628 validate_rma_quantity(p_opm_item_id => p_opm_item_id,
1629 p_lot_id => l_lot_attribute_rec.lot_id,
1630 p_lot_no => l_lot_no,
1631 p_sublot_no => l_sublot_no,
1632 p_oe_order_header_id => l_oe_order_header_id,
1633 p_oe_order_line_id => l_oe_order_line_id ,
1634 p_lot_qty => l_lot_quantity ,
1635 p_unit_of_measure => l_unit_of_measure ,
1636 p_rma_lot_qty => l_oe_lot_quantity,
1637 p_rma_lot_uom => NULL,
1638 p_line_set_id => l_line_set_id,
1639 p_rti_id => l_rti_id,
1640 x_allowed => l_allowed,
1641 x_allowed_quantity => l_allowed_quantity,
1642 x_return_status => x_return_status );
1643
1644 IF x_return_status <> 'S' THEN
1645 RAISE FND_API.G_EXC_ERROR;
1646 END IF;
1647 END IF; -- IF l_source_document_code = 'RMA' THEN
1648
1649
1650 -- validate/derive secondary quantity.
1651 l_progress := '070' ;
1652
1653 IF l_secondary_unit_of_measure IS NOT NULL THEN
1654 IF l_lot_secondary_quantity IS NULL THEN
1655 l_update_lot_qty := TRUE ;
1656 END IF;
1657
1658 GML_ValidateDerive_GRP.Secondary_Qty
1659 ( p_api_version => '1.0'
1660 , p_init_msg_list => 'F'
1661 , p_validate_ind => 'Y'
1662 , p_item_no => p_item_no
1663 , p_lot_id => nvl(l_lot_attribute_rec.lot_id,0)
1664 , p_unit_of_measure => l_unit_of_measure
1665 , p_secondary_unit_of_measure => l_secondary_unit_of_measure
1666 , p_quantity => l_lot_quantity
1667 , p_secondary_quantity => l_lot_secondary_quantity
1668 , x_return_status => x_return_status
1669 , x_msg_count => x_msg_count
1670 , x_msg_data => x_msg_data ) ;
1671
1672 IF l_inv_debug = 1 THEN
1673 print_debug('Validate Secondary qty status trx_type=>'||l_transaction_type||'-'||x_return_status, 1);
1674 print_debug('Secondary qty=>'||l_lot_secondary_quantity, 1);
1675 END IF;
1676
1677 IF x_return_status <> 'S' THEN
1678 RAISE FND_API.G_EXC_ERROR;
1679 END IF;
1680
1681 IF l_update_lot_qty THEN
1682 update mtl_transaction_lots_temp
1683 set secondary_quantity = l_lot_secondary_quantity
1684 where rowid = p_mtlt_rowid ;
1685 END IF;
1686 END IF; -- IF l_secondary_unit_of_measure IS NOT NULL THEN
1687
1688 l_progress := '080' ;
1689
1690 IF p_new_lot = 'N' THEN
1691 l_progress := '070' ;
1692 IF l_transaction_type = 'DELIVER' THEN
1693 -- validate lot status depending upon the profile option.
1694 g_moved_diff_stat := fnd_profile.value('IC$MOVEDIFFSTAT');
1695 IF g_moved_diff_stat in (0,2) THEN
1696 l_locator_id := l_to_locator_id ;
1697 check_lot_status(p_lot_attribute_rec => l_lot_attribute_rec,
1698 p_organization_id => l_org_id,
1699 p_trans_date => l_transaction_date,
1700 p_locator_id => l_locator_id,
1701 p_rti_id => l_rti_id,
1702 x_return_status => x_return_status
1703 );
1704
1705 IF l_inv_debug = 1 THEN
1706 print_debug('Check Lot Status for old lot for transaction type '||l_transaction_type||' =>'||x_return_status, 1);
1707 END IF;
1708
1709 IF x_return_status <> 'S' THEN
1710 RAISE FND_API.G_EXC_ERROR;
1711 END IF;
1712 END IF;
1713 END IF ;
1714 ELSE -- IF p_new_lot = 'N' THEN
1715
1716 -- validate_lot_attributes API may return expiration date depending upon the settings.
1717
1718 l_new_lot_rec.item_no := p_item_no ;
1719 l_new_lot_rec.lot_no := l_lot_no;
1720 l_new_lot_rec.sublot_no := l_sublot_no;
1721 l_new_lot_rec.expire_date := l_lot_attribute_rec.expiration_date;
1722 l_new_lot_rec.lot_desc := l_lot_desc ;
1723 l_new_lot_rec.qc_grade := NULL ;
1724 l_new_lot_rec.lot_created := l_transaction_date;
1725 l_new_lot_rec.origination_type := 3;
1726 l_new_lot_rec.vendor_lot_no := nvl(l_vendor_lot_no_on_lot,l_vendor_lot_no_on_line);
1727
1728 l_progress := '085' ;
1729
1730 create_new_lot(p_new_lot_rec => l_new_lot_rec,
1731 p_organization_id => l_org_id,
1732 p_vendor_id => l_vendor_id,
1733 p_vendor_site_id => l_vendor_site_id,
1734 p_from_unit_of_measure => l_unit_of_measure,
1735 p_to_unit_of_measure => l_secondary_unit_of_measure,
1736 p_type_factor => l_lot_secondary_quantity/l_lot_quantity,
1737 p_rti_id => l_rti_id,
1738 x_return_status => x_return_status );
1739
1740 IF l_inv_debug = 1 THEN
1741 print_debug(' Create new lot status for transaction type '||l_transaction_type||' =>'||x_return_status, 1);
1742 END IF;
1743
1744 IF x_return_status <> 'S' THEN
1745 RAISE FND_API.G_EXC_ERROR;
1746 END IF;
1747 END IF; -- IF p_new_lot = 'N'
1748
1749 ELSIF (l_transaction_type IN ('CORRECT','RETURN TO RECEIVING','RETURN TO VENDOR','RETURN TO CUSTOMER')) THEN
1750
1751 IF (l_shipment_header_id IS NULL) OR (l_shipment_line_id IS NULL) THEN
1752 SELECT shipment_header_id, shipment_line_id
1753 INTO l_shipment_header_id , l_shipment_line_id
1754 FROM rcv_transactions
1755 WHERE transaction_id = l_parent_transaction_id ;
1756
1757 END IF;
1758
1759 l_progress := '090' ;
1760
1761 IF (l_transaction_type IN ('RETURN TO RECEIVING','RETURN TO VENDOR','RETURN TO CUSTOMER'))
1762 OR (l_transaction_type = 'CORRECT' AND l_rti_primary_qty < 0) THEN
1763
1764 IF p_new_lot = 'N' THEN
1765 l_progress := '100' ;
1766 IF p_parent_txn_type IN ('RETURN TO VENDOR','RETURN TO CUSTOMER') THEN
1767 IF (p_grand_parent_txn_type = 'DELIVER') THEN
1768 IF l_inv_debug = 1 THEN
1769 print_debug(' Combination not possible trx_type,parent_trx_type,grand_parent_trx_type=>'||l_transaction_type||'-'||p_parent_txn_type||'-'||p_grand_parent_txn_type, 1);
1770 END IF;
1771 -- this combination not possible.
1772 RETURN;
1773 ELSE
1774
1775 -- -ve correction to RTV,RTC means adding +ve quantity to RECEIVE
1776 -- lots other than existing in RECEIVE can be specified.
1777 -- validate the lots in case of RMA.
1778
1779 IF l_source_document_code = 'RMA' THEN
1780 l_progress := '101' ;
1781 opm_rma_valid_lot(p_oe_order_header_id => l_oe_order_header_id ,
1782 p_oe_order_line_id => l_oe_order_line_id,
1783 p_lot_no => l_lot_no,
1784 p_sublot_no => l_sublot_no,
1785 p_rti_id => l_rti_id,
1786 x_line_set_id => l_line_set_id,
1787 x_oe_lot_quantity => l_oe_lot_quantity,
1788 x_return_status => x_return_status );
1789
1790
1791 END IF; -- IF l_source_document_code = 'RMA' THEN
1792
1793 validate_lot_attributes(p_lot_attribute_rec => l_lot_attribute_rec,
1794 p_inventory_item_id => l_item_id,
1795 p_organization_id => l_org_id,
1796 p_trans_date => l_transaction_date,
1797 p_rti_id => l_rti_id,
1798 x_return_status => x_return_status
1799 );
1800
1801 IF l_inv_debug = 1 THEN
1802 print_debug(' Validate lot attributes status trx_type,parent_trx_type,grand_parent_trx_type=>'||l_transaction_type||'-'||p_parent_txn_type||'-'||p_grand_parent_txn_type||'-'||x_return_status, 1);
1803 END IF;
1804
1805 IF x_return_status <> 'S' THEN
1806 RAISE fnd_api.g_exc_error;
1807 END IF;
1808
1809 IF l_source_document_code = 'RMA' THEN
1810 l_progress := '102' ;
1811
1812 validate_rma_quantity(p_opm_item_id => p_opm_item_id,
1813 p_lot_id => l_lot_attribute_rec.lot_id,
1814 p_lot_no => l_lot_no,
1815 p_sublot_no => l_sublot_no,
1816 p_oe_order_header_id => l_oe_order_header_id,
1817 p_oe_order_line_id => l_oe_order_line_id ,
1818 p_lot_qty => l_lot_quantity ,
1819 p_unit_of_measure => l_unit_of_measure ,
1820 p_rma_lot_qty => l_oe_lot_quantity,
1821 p_rma_lot_uom => NULL,
1822 p_line_set_id => l_line_set_id,
1823 p_rti_id => l_rti_id,
1824 x_allowed => l_allowed,
1825 x_allowed_quantity => l_allowed_quantity,
1826 x_return_status => x_return_status );
1827
1828 IF x_return_status <> 'S' THEN
1829 RAISE FND_API.G_EXC_ERROR;
1830 END IF;
1831
1832 END IF; -- IF l_source_document_code = 'RMA'
1833
1834
1835 -- validate/derive secondary quantity.
1836 l_progress := '103' ;
1837
1838 IF l_secondary_unit_of_measure IS NOT NULL THEN
1839 IF l_lot_secondary_quantity IS NULL THEN
1840 l_update_lot_qty := TRUE ;
1841 END IF;
1842
1843 GML_ValidateDerive_GRP.Secondary_Qty
1844 ( p_api_version => '1.0'
1845 , p_init_msg_list => 'F'
1846 , p_validate_ind => 'Y'
1847 , p_item_no => p_item_no
1848 , p_lot_id => nvl(l_lot_attribute_rec.lot_id,0)
1849 , p_unit_of_measure => l_unit_of_measure
1850 , p_secondary_unit_of_measure => l_secondary_unit_of_measure
1851 , p_quantity => l_lot_quantity
1852 , p_secondary_quantity => l_lot_secondary_quantity
1853 , x_return_status => x_return_status
1854 , x_msg_count => x_msg_count
1855 , x_msg_data => x_msg_data ) ;
1856
1857 IF l_inv_debug = 1 THEN
1858 print_debug('Validate Secondary qty status trx_type=>'||l_transaction_type||'-'||x_return_status, 1);
1859 print_debug('Secondary qty=>'||l_lot_secondary_quantity, 1);
1860 END IF;
1861
1862 IF x_return_status <> 'S' THEN
1863 RAISE FND_API.G_EXC_ERROR;
1864 END IF;
1865
1866 IF l_update_lot_qty THEN
1867 update mtl_transaction_lots_temp
1868 set secondary_quantity = l_lot_secondary_quantity
1869 where rowid = p_mtlt_rowid ;
1870 END IF;
1871 END IF; -- IF l_secondary_unit_of_measure IS NOT NULL THEN
1872 END IF ;
1873 ELSIF p_parent_txn_type IN ('RETURN TO RECEIVING') THEN
1874 IF l_inv_debug = 1 THEN
1875 print_debug(' Combination not possible trx_type,parent_trx_type,grand_parent_trx_type=>'||l_transaction_type||'-'||p_parent_txn_type||'-'||p_grand_parent_txn_type, 1);
1876 END IF;
1877 -- this combination not possible.
1878 RETURN;
1879 ELSE
1880 l_progress := '110' ;
1881 validate_lot_attributes(p_lot_attribute_rec => l_lot_attribute_rec,
1882 p_inventory_item_id => l_item_id,
1883 p_organization_id => l_org_id,
1884 p_trans_date => l_transaction_date,
1885 p_rti_id => l_rti_id,
1886 x_return_status => x_return_status
1887 );
1888
1889 IF l_inv_debug = 1 THEN
1890 print_debug(' Validate lot attributes status trx_type,parent_trx_type,grand_parent_trx_type=>'||l_transaction_type||'-'||p_parent_txn_type||'-'||p_grand_parent_txn_type||'-'||x_return_status, 1);
1891 END IF;
1892
1893 IF x_return_status <> 'S' THEN
1894 RAISE fnd_api.g_exc_error;
1895 END IF;
1896
1897 -- validate/derive secondary quantity.
1898 l_progress := '112' ;
1899
1900 IF l_secondary_unit_of_measure IS NOT NULL THEN
1901 IF l_lot_secondary_quantity IS NULL THEN
1902 l_update_lot_qty := TRUE ;
1903 END IF;
1904
1905 GML_ValidateDerive_GRP.Secondary_Qty
1906 ( p_api_version => '1.0'
1907 , p_init_msg_list => 'F'
1908 , p_validate_ind => 'Y'
1909 , p_item_no => p_item_no
1910 , p_lot_id => nvl(l_lot_attribute_rec.lot_id,0)
1911 , p_unit_of_measure => l_unit_of_measure
1912 , p_secondary_unit_of_measure => l_secondary_unit_of_measure
1913 , p_quantity => l_lot_quantity
1914 , p_secondary_quantity => l_lot_secondary_quantity
1915 , x_return_status => x_return_status
1916 , x_msg_count => x_msg_count
1917 , x_msg_data => x_msg_data ) ;
1918
1919 IF l_inv_debug = 1 THEN
1920 print_debug('Validate Secondary qty status trx_type=>'||l_transaction_type||'-'||x_return_status, 1);
1921 print_debug('Secondary qty=>'||l_lot_secondary_quantity, 1);
1922 END IF;
1923
1924 IF x_return_status <> 'S' THEN
1925 RAISE FND_API.G_EXC_ERROR;
1926 END IF;
1927
1928 IF l_update_lot_qty THEN
1929 update mtl_transaction_lots_temp
1930 set secondary_quantity = l_lot_secondary_quantity
1931 where rowid = p_mtlt_rowid ;
1932 END IF;
1933 END IF; -- IF l_secondary_unit_of_measure IS NOT NULL THEN
1934
1935 IF p_parent_txn_type = 'DELIVER' THEN
1936 -- to check if it is an OLD OPM RECEIPT(Common Purchasing)
1937 BEGIN
1938 select rcv.comments
1939 into l_comment
1940 from rcv_transactions rcv
1941 where rcv.transaction_id = l_parent_transaction_id;
1942
1943 EXCEPTION
1944 WHEN OTHERS THEN
1945 l_comment := null;
1946 END ;
1947
1948 l_progress := '120' ;
1949
1950 IF l_inv_debug = 1 THEN
1951 print_debug('-ve correction Old/new receipt=>'||nvl(l_comment,'NEW RECEIPT'), 1);
1952 END IF;
1953
1954 BEGIN
1955 IF nvl(l_comment,'NOT OPM RECEIPT') <> 'OPM RECEIPT' THEN
1956 -- check the lot specified by the user is existing in the DELIVER transaction.
1957 SELECT 'X' INTO l_temp
1958 FROM ic_tran_pnd itp
1959 WHERE
1960 itp.doc_id = l_shipment_header_id
1961 and itp.line_id = l_parent_transaction_id
1962 and itp.doc_type = 'PORC'
1963 and itp.lot_id = p_lot_id ;
1964
1965 ELSE
1966 SELECT 'X' INTO l_temp
1967 FROM rcv_transactions rcv,gml_recv_trans_map grm,ic_tran_pnd itp
1968 WHERE rcv.transaction_id = l_parent_transaction_id
1969 and rcv.interface_transaction_id = grm.interface_transaction_id
1970 and grm.recv_id = itp.doc_id
1971 and itp.doc_type = 'RECV'
1972 and grm.line_id = itp.line_id
1973 and itp.lot_id = p_lot_id ;
1974
1975 END IF;
1976
1977 EXCEPTION WHEN NO_DATA_FOUND THEN
1978 IF l_inv_debug = 1 THEN
1979 print_debug(' Lot does not exist for trx_type,parent_trx_type,grand_parent_trx_type=>'||l_transaction_type||'-'||p_parent_txn_type||'-'||p_grand_parent_txn_type, 1);
1980 END IF;
1981 FND_MESSAGE.SET_NAME('GML','GML_RECV_INVALID_LOT');
1982 FND_MESSAGE.SET_TOKEN('LOT_NO',l_lot_no);
1983 FND_MESSAGE.SET_TOKEN('SUBLOT_NO',l_sublot_no);
1984 FND_MSG_PUB.Add;
1985 x_return_status := fnd_api.g_ret_sts_error;
1986 RAISE FND_API.G_EXC_ERROR;
1987 END ;
1988
1989 l_progress := '130' ;
1990
1991 -- check that return quantity cannot be greater than received quantity.
1992 -- find total net quantity received against the lot
1993
1994 IF nvl(l_comment,'NOT OPM RECEIPT') <> 'OPM RECEIPT' THEN
1995
1996 Open Cr_get_total_qty_available;
1997 Fetch Cr_get_total_qty_available into l_net_received_lot_qty;
1998 Close Cr_get_total_qty_available;
1999
2000 IF l_lot_primary_quantity > l_net_received_lot_qty THEN
2001 IF l_inv_debug = 1 THEN
2002 print_debug(' Qty more than received for trx_type,parent_trx_type,grand_parent_trx_type=>'||l_transaction_type||'-'||p_parent_txn_type||'-'||p_grand_parent_txn_type, 1);
2003 END IF;
2004 FND_MESSAGE.SET_NAME('GML', 'PO_RETURN_MORE_RECEIVE');
2005 FND_MESSAGE.SET_TOKEN('AVAILABLE_QTY',l_net_received_lot_qty);
2006 FND_MSG_PUB.Add;
2007 x_return_status := fnd_api.g_ret_sts_error;
2008 RAISE FND_API.G_EXC_ERROR;
2009 END IF;
2010
2011 ELSE
2012 /*Total received quantity for that lot*/
2013 Open Cr_old_recv_qty;
2014 Fetch Cr_old_recv_qty into l_old_recv_qty,l_recv_id,l_recvline_id;
2015 Close Cr_old_recv_qty;
2016
2017 /*Total return quantity for that lot*/
2018 Open Cr_old_rtrn_qty;
2019 Fetch Cr_old_rtrn_qty into l_old_rtrn_qty;
2020 Close Cr_old_rtrn_qty;
2021
2022 /*Corrections and Returns done using common receiving for that lot*/
2023 Open Cr_get_total_qty_available;
2024 Fetch Cr_get_total_qty_available into l_cr_avaliable_qty;
2025 Close Cr_get_total_qty_available;
2026
2027 IF (nvl(l_old_recv_qty,0) - nvl(l_old_rtrn_qty,0) + nvl(l_cr_avaliable_qty,0)) < l_lot_primary_quantity THEN
2028 FND_MESSAGE.SET_NAME('GML', 'PO_RETURN_MORE_RECEIVE');
2029 FND_MESSAGE.SET_TOKEN('AVAILABLE_QTY',nvl(l_old_recv_qty,0) - nvl(l_old_rtrn_qty,0) + nvl(l_cr_avaliable_qty,0));
2030 FND_MSG_PUB.Add;
2031 x_return_status := fnd_api.g_ret_sts_error;
2032 RAISE FND_API.G_EXC_ERROR;
2033 END IF;
2034
2035 END IF; -- IF nvl(l_comment,'NOT OPM RECEIPT') <> 'OPM RECEIPT' THEN
2036
2037 l_progress := '140' ;
2038
2039 -- for -ve corrections and returns to a DELIVER transaction
2040 -- take from_locator_id - source location.
2041 -- to_locator_id - destination location.
2042 -- in the above case ,source location is DELIVER and destination is RECEIVE.
2043 l_locator_id := l_from_locator_id ;
2044 -- check for onhand quantity in the inventory
2045 validate_quantity_onhand(p_lot_id => p_lot_id,
2046 p_rti_id => l_rti_id,
2047 p_opm_item_id => p_opm_item_id,
2048 p_organization_id => l_org_id,
2049 p_locator_id => l_locator_id,
2050 p_primary_quantity => l_lot_primary_quantity,
2051 x_return_status => x_return_status );
2052
2053 IF l_inv_debug = 1 THEN
2054 print_debug(' validate_quantity_onhand status for trx_type,parent_trx_type,grand_parent_trx_type=>'||l_transaction_type||'-'||p_parent_txn_type||'-'||p_grand_parent_txn_type||'-'||x_return_status, 1);
2055 END IF;
2056
2057 IF x_return_status NOT IN ('S','W') THEN
2058 RAISE fnd_api.g_exc_error;
2059 END IF;
2060
2061 --Bug# 3664014 -- insert record in po_interface_error in case of warning.
2062
2063 IF x_return_status = 'W' THEN
2064 insert_errors(p_rti_id => l_rti_id,
2065 p_group_id => NULL,
2066 p_header_interface_id => NULL,
2067 p_column_name => 'TRANSACTION_QUANTITY',
2068 p_table_name => 'MTL_TRANSACTION_LOTS_INTERFACE',
2069 p_error_message => FND_MSG_PUB.GET(p_msg_index => FND_MSG_PUB.G_LAST,p_encoded => 'F'),
2070 p_mesg_owner => NULL,
2071 p_error_message_name => NULL );
2072
2073 -- reset warning to success otherwise ROI c routine will fail the transaction
2074 x_return_status := 'S';
2075 END IF;
2076
2077 END IF; -- IF p_parent_txn_type = 'DELIVER' THEN
2078
2079 END IF; -- IF p_parent_txn_type IN ('RETURN TO VENDOR','RETURN TO CUSTOMER') THEN
2080
2081 ELSE -- new lot
2082 IF p_parent_txn_type IN ('RETURN TO VENDOR','RETURN TO CUSTOMER') THEN
2083 l_progress := '150' ;
2084 IF (p_grand_parent_txn_type = 'DELIVER') THEN
2085 IF l_inv_debug = 1 THEN
2086 print_debug(' Combination not possible trx_type,parent_trx_type,grand_parent_trx_type=>'||l_transaction_type||'-'||p_parent_txn_type||'-'||p_grand_parent_txn_type, 1);
2087 END IF;
2088 -- this combination not possible.
2089 RETURN;
2090 ELSE
2091
2092 IF l_source_document_code = 'RMA' THEN
2093 l_progress := '151' ;
2094 opm_rma_valid_lot(p_oe_order_header_id => l_oe_order_header_id ,
2095 p_oe_order_line_id => l_oe_order_line_id,
2096 p_lot_no => l_lot_no,
2097 p_sublot_no => l_sublot_no,
2098 p_rti_id => l_rti_id,
2099 x_line_set_id => l_line_set_id,
2100 x_oe_lot_quantity => l_oe_lot_quantity,
2101 x_return_status => x_return_status );
2102
2103 END IF; -- IF l_source_document_code = 'RMA' THEN
2104
2105 -- this is only possible for a -ve correction.
2106 -- doing -ve correction to RTV , RTC means adding to RECEIVING
2107 -- so new lots can be created.
2108
2109 -- it is a new lot. create it.
2110 validate_lot_attributes(p_lot_attribute_rec => l_lot_attribute_rec,
2111 p_inventory_item_id => l_item_id,
2112 p_organization_id => l_org_id,
2113 p_trans_date => l_transaction_date,
2114 p_rti_id => l_rti_id,
2115 x_return_status => x_return_status
2116 );
2117
2118 IF l_inv_debug = 1 THEN
2119 print_debug(' Validate lot attributes status trx_type,parent_trx_type,grand_parent_trx_type=>'||l_transaction_type||'-'||p_parent_txn_type||'-'||p_grand_parent_txn_type||'-'||x_return_status, 1);
2120 END IF;
2121
2122 IF x_return_status <> 'S' THEN
2123 RAISE fnd_api.g_exc_error;
2124 END IF;
2125
2126 IF l_source_document_code = 'RMA' THEN
2127 l_progress := '152' ;
2128
2129 validate_rma_quantity(p_opm_item_id => p_opm_item_id,
2130 p_lot_id => l_lot_attribute_rec.lot_id,
2131 p_lot_no => l_lot_no,
2132 p_sublot_no => l_sublot_no,
2133 p_oe_order_header_id => l_oe_order_header_id,
2134 p_oe_order_line_id => l_oe_order_line_id ,
2135 p_lot_qty => l_lot_quantity ,
2136 p_unit_of_measure => l_unit_of_measure ,
2137 p_rma_lot_qty => l_oe_lot_quantity,
2138 p_rma_lot_uom => NULL,
2139 p_line_set_id => l_line_set_id,
2140 p_rti_id => l_rti_id,
2141 x_allowed => l_allowed,
2142 x_allowed_quantity => l_allowed_quantity,
2143 x_return_status => x_return_status );
2144
2145 IF x_return_status <> 'S' THEN
2146 RAISE FND_API.G_EXC_ERROR;
2147 END IF;
2148
2149 END IF; -- IF l_source_document_code = 'RMA'
2150
2151 -- validate/derive secondary quantity.
2152 l_progress := '155' ;
2153
2154 IF l_secondary_unit_of_measure IS NOT NULL THEN
2155 IF l_lot_secondary_quantity IS NULL THEN
2156 l_update_lot_qty := TRUE ;
2157 END IF;
2158
2159 GML_ValidateDerive_GRP.Secondary_Qty
2160 ( p_api_version => '1.0'
2161 , p_init_msg_list => 'F'
2162 , p_validate_ind => 'Y'
2163 , p_item_no => p_item_no
2164 , p_unit_of_measure => l_unit_of_measure
2165 , p_lot_id => 0
2166 , p_secondary_unit_of_measure => l_secondary_unit_of_measure
2167 , p_quantity => l_lot_quantity
2168 , p_secondary_quantity => l_lot_secondary_quantity
2169 , x_return_status => x_return_status
2170 , x_msg_count => x_msg_count
2171 , x_msg_data => x_msg_data ) ;
2172
2173 IF l_inv_debug = 1 THEN
2174 print_debug('Validate Secondary qty status trx_type=>'||l_transaction_type||'-'||x_return_status, 1);
2175 print_debug('Secondary qty=>'||l_lot_secondary_quantity, 1);
2176 END IF;
2177
2178 IF x_return_status <> 'S' THEN
2179 RAISE FND_API.G_EXC_ERROR;
2180 END IF;
2181
2182 IF l_update_lot_qty THEN
2183 update mtl_transaction_lots_temp
2184 set secondary_quantity = l_lot_secondary_quantity
2185 where rowid = p_mtlt_rowid ;
2186 END IF;
2187 END IF; -- IF l_secondary_unit_of_measure IS NOT NULL THEN
2188
2189 l_new_lot_rec.item_no := p_item_no ;
2190 l_new_lot_rec.lot_no := l_lot_no;
2191 l_new_lot_rec.sublot_no := l_sublot_no;
2192 l_new_lot_rec.expire_date := l_lot_attribute_rec.expiration_date;
2193 l_new_lot_rec.lot_desc := l_lot_desc ;
2194 l_new_lot_rec.qc_grade := NULL ;
2195 l_new_lot_rec.lot_created := l_transaction_date;
2196 l_new_lot_rec.origination_type := 3;
2197 l_new_lot_rec.vendor_lot_no := nvl(l_vendor_lot_no_on_lot,l_vendor_lot_no_on_line);
2198
2199 l_progress := '160' ;
2200
2201 create_new_lot(p_new_lot_rec => l_new_lot_rec,
2202 p_organization_id => l_org_id,
2203 p_vendor_id => l_vendor_id,
2204 p_vendor_site_id => l_vendor_site_id,
2205 p_from_unit_of_measure => l_unit_of_measure,
2206 p_to_unit_of_measure => l_secondary_unit_of_measure,
2207 p_type_factor => l_lot_secondary_quantity/l_lot_quantity,
2208 p_rti_id => l_rti_id,
2209 x_return_status => x_return_status );
2210
2211 IF l_inv_debug = 1 THEN
2212 print_debug('Create_new_lot status trx_type,parent_trx_type,grand_parent_trx_type=>'||l_transaction_type||'-'||p_parent_txn_type||'-'||p_grand_parent_txn_type||'-'||x_return_status, 1);
2213 END IF;
2214
2215 IF x_return_status <> 'S' THEN
2216 RAISE FND_API.G_EXC_ERROR;
2217 END IF;
2218 END IF ;
2219 ELSIF p_parent_txn_type IN ('RETURN TO RECEIVING') THEN
2220 l_progress := '170' ;
2221 IF l_inv_debug = 1 THEN
2222 print_debug(' Combination not possible trx_type,parent_trx_type,grand_parent_trx_type=>'||l_transaction_type||'-'||p_parent_txn_type||'-'||p_grand_parent_txn_type, 1);
2223 END IF;
2224
2225 -- this combination not possible.
2226 RETURN;
2227 ELSE
2228 IF l_inv_debug = 1 THEN
2229 print_debug(' WMS errors out for trx_type,parent_trx_type,grand_parent_trx_type=>'||l_transaction_type||'-'||p_parent_txn_type||'-'||p_grand_parent_txn_type, 1);
2230 END IF;
2231 -- wms will error out in this condition.
2232 -- new lots cannot be created for -ve correction and returns to RECEIVE/DELIVER transactions.
2233
2234 FND_MESSAGE.SET_NAME('GML','GML_RECV_INVALID_LOT');
2235 FND_MESSAGE.SET_TOKEN('LOT_NO',l_lot_no);
2236 FND_MESSAGE.SET_TOKEN('SUBLOT_NO',l_sublot_no);
2237 FND_MSG_PUB.Add;
2238 x_return_status := fnd_api.g_ret_sts_error;
2239 RAISE FND_API.G_EXC_ERROR;
2240
2241 END IF ;
2242 END IF; -- IF p_new_lot = 'N' THEN
2243
2244 ELSIF (l_transaction_type = 'CORRECT' AND l_rti_primary_qty > 0) THEN
2245 IF p_parent_txn_type IN ('RETURN TO VENDOR','RETURN TO CUSTOMER','RETURN TO RECEIVING') THEN
2246 l_progress := '180' ;
2247 IF (p_grand_parent_txn_type = 'DELIVER') THEN
2248 IF l_inv_debug = 1 THEN
2249 print_debug(' Combination not possible trx_type,parent_trx_type,grand_parent_trx_type=>'||l_transaction_type||'-'||p_parent_txn_type||'-'||p_grand_parent_txn_type, 1);
2250 END IF;
2251 -- this combination not possible.
2252 RETURN;
2253 ELSE
2254
2255
2256 validate_lot_attributes(p_lot_attribute_rec => l_lot_attribute_rec,
2257 p_inventory_item_id => l_item_id,
2258 p_organization_id => l_org_id,
2259 p_trans_date => l_transaction_date,
2260 p_rti_id => l_rti_id,
2261 x_return_status => x_return_status
2262 );
2263
2264 IF l_inv_debug = 1 THEN
2265 print_debug(' Validate lot attributes status trx_type,parent_trx_type,grand_parent_trx_type=>'||l_transaction_type||'-'||p_parent_txn_type||'-'||p_grand_parent_txn_type||'-'||x_return_status, 1);
2266 END IF;
2267
2268 IF x_return_status <> 'S' THEN
2269 RAISE fnd_api.g_exc_error;
2270 END IF;
2271
2272 -- validate/derive secondary quantity.
2273 l_progress := '181' ;
2274
2275 IF l_secondary_unit_of_measure IS NOT NULL THEN
2276 IF l_lot_secondary_quantity IS NULL THEN
2277 l_update_lot_qty := TRUE ;
2278 END IF;
2279
2280 GML_ValidateDerive_GRP.Secondary_Qty
2281 ( p_api_version => '1.0'
2282 , p_init_msg_list => 'F'
2283 , p_validate_ind => 'Y'
2284 , p_item_no => p_item_no
2285 , p_unit_of_measure => l_unit_of_measure
2286 , p_lot_id => nvl(l_lot_attribute_rec.lot_id,0)
2287 , p_secondary_unit_of_measure => l_secondary_unit_of_measure
2288 , p_quantity => l_lot_quantity
2289 , p_secondary_quantity => l_lot_secondary_quantity
2290 , x_return_status => x_return_status
2291 , x_msg_count => x_msg_count
2292 , x_msg_data => x_msg_data ) ;
2293
2294 IF l_inv_debug = 1 THEN
2295 print_debug('Validate Secondary qty status trx_type=>'||l_transaction_type||'-'||x_return_status, 1);
2296 print_debug('Secondary qty=>'||l_lot_secondary_quantity, 1);
2297 END IF;
2298
2299 IF x_return_status <> 'S' THEN
2300 RAISE FND_API.G_EXC_ERROR;
2301 END IF;
2302
2303 IF l_update_lot_qty THEN
2304 update mtl_transaction_lots_temp
2305 set secondary_quantity = l_lot_secondary_quantity
2306 where rowid = p_mtlt_rowid ;
2307 END IF;
2308 END IF; -- IF l_secondary_unit_of_measure IS NOT NULL THEN
2309 END IF ; -- IF (p_grand_parent_txn_type = 'DELIVER') THEN
2310 ELSE
2311 IF p_new_lot = 'N' THEN
2312 l_progress := '190' ;
2313
2314 IF l_source_document_code = 'RMA' THEN
2315 l_progress := '191' ;
2316
2317 opm_rma_valid_lot(p_oe_order_header_id => l_oe_order_header_id ,
2318 p_oe_order_line_id => l_oe_order_line_id,
2319 p_lot_no => l_lot_no,
2320 p_sublot_no => l_sublot_no,
2321 p_rti_id => l_rti_id,
2322 x_line_set_id => l_line_set_id,
2323 x_oe_lot_quantity => l_oe_lot_quantity,
2324 x_return_status => x_return_status );
2325
2326 END IF; -- IF l_source_document_code = 'RMA' THEN
2327
2328 validate_lot_attributes(p_lot_attribute_rec => l_lot_attribute_rec,
2329 p_inventory_item_id => l_item_id,
2330 p_organization_id => l_org_id,
2331 p_trans_date => l_transaction_date,
2332 p_rti_id => l_rti_id,
2333 x_return_status => x_return_status
2334 );
2335
2336 IF l_inv_debug = 1 THEN
2337 print_debug(' Validate lot attributes status trx_type,parent_trx_type,grand_parent_trx_type=>'||l_transaction_type||'-'||p_parent_txn_type||'-'||p_grand_parent_txn_type||'-'||x_return_status, 1);
2338 END IF;
2339
2340 IF x_return_status <> 'S' THEN
2341 RAISE fnd_api.g_exc_error;
2342 END IF;
2343
2344 IF l_source_document_code = 'RMA' THEN
2345 l_progress := '192' ;
2346
2347 validate_rma_quantity(p_opm_item_id => p_opm_item_id,
2348 p_lot_id => l_lot_attribute_rec.lot_id,
2349 p_lot_no => l_lot_no,
2350 p_sublot_no => l_sublot_no,
2351 p_oe_order_header_id => l_oe_order_header_id,
2352 p_oe_order_line_id => l_oe_order_line_id ,
2353 p_lot_qty => l_lot_quantity ,
2354 p_unit_of_measure => l_unit_of_measure ,
2355 p_rma_lot_qty => l_oe_lot_quantity,
2356 p_rma_lot_uom => NULL,
2357 p_line_set_id => l_line_set_id,
2358 p_rti_id => l_rti_id,
2359 x_allowed => l_allowed,
2360 x_allowed_quantity => l_allowed_quantity,
2361 x_return_status => x_return_status );
2362
2363 IF x_return_status <> 'S' THEN
2364 RAISE FND_API.G_EXC_ERROR;
2365 END IF;
2366 END IF; -- IF l_source_document_code = 'RMA'
2367
2368 -- validate/derive secondary quantity.
2369 l_progress := '195' ;
2370
2371 IF l_secondary_unit_of_measure IS NOT NULL THEN
2372 IF l_lot_secondary_quantity IS NULL THEN
2373 l_update_lot_qty := TRUE ;
2374 END IF;
2375
2376 GML_ValidateDerive_GRP.Secondary_Qty
2377 ( p_api_version => '1.0'
2378 , p_init_msg_list => 'F'
2379 , p_validate_ind => 'Y'
2380 , p_item_no => p_item_no
2381 , p_unit_of_measure => l_unit_of_measure
2382 , p_lot_id => nvl(l_lot_attribute_rec.lot_id,0)
2383 , p_secondary_unit_of_measure => l_secondary_unit_of_measure
2384 , p_quantity => l_lot_quantity
2385 , p_secondary_quantity => l_lot_secondary_quantity
2386 , x_return_status => x_return_status
2387 , x_msg_count => x_msg_count
2388 , x_msg_data => x_msg_data ) ;
2389
2390 IF l_inv_debug = 1 THEN
2391 print_debug('Validate Secondary qty status trx_type=>'||l_transaction_type||'-'||x_return_status, 1);
2392 print_debug('Secondary qty=>'||l_lot_secondary_quantity, 1);
2393 END IF;
2394
2395 IF x_return_status <> 'S' THEN
2396 RAISE FND_API.G_EXC_ERROR;
2397 END IF;
2398
2399 IF l_update_lot_qty THEN
2400 update mtl_transaction_lots_temp
2401 set secondary_quantity = l_lot_secondary_quantity
2402 where rowid = p_mtlt_rowid ;
2403 END IF;
2404 END IF; -- IF l_secondary_unit_of_measure IS NOT NULL THEN
2405
2406 -- for correction to deliver transaction,lot specified must be same as in deliver transaction.
2407 IF p_parent_txn_type = 'DELIVER' THEN
2408 l_progress := '200' ;
2409 -- to check if it is an OLD OPM RECEIPT(Common Purchasing)
2410 BEGIN
2411 select rcv.comments
2412 into l_comment
2413 from rcv_transactions rcv
2414 where rcv.transaction_id = l_parent_transaction_id;
2415
2416 EXCEPTION
2417 WHEN OTHERS THEN
2418 l_comment := null;
2419 END ;
2420
2421 IF l_inv_debug = 1 THEN
2422 print_debug('+ve correction Old/new receipt=>'||l_comment, 1);
2423 END IF;
2424
2425 BEGIN
2426 IF nvl(l_comment,'NOT OPM RECEIPT') <> 'OPM RECEIPT' THEN
2427 -- check the lot specified by the user is existing in the DELIVER transaction.
2428 SELECT 'X' INTO l_temp
2429 FROM ic_tran_pnd itp
2430 WHERE
2431 itp.doc_id = l_shipment_header_id
2432 and itp.line_id = l_parent_transaction_id
2433 and itp.doc_type = 'PORC'
2434 and itp.lot_id = p_lot_id ;
2435
2436 ELSE
2437 SELECT 'X' INTO l_temp
2438 FROM rcv_transactions rcv,gml_recv_trans_map grm,ic_tran_pnd itp
2439 WHERE rcv.transaction_id = l_parent_transaction_id
2440 and rcv.interface_transaction_id = grm.interface_transaction_id
2441 and grm.recv_id = itp.doc_id
2442 and itp.doc_type = 'RECV'
2443 and grm.line_id = itp.line_id
2444 and itp.lot_id = p_lot_id ;
2445
2446 END IF;
2447
2448 EXCEPTION WHEN NO_DATA_FOUND THEN
2449 IF l_inv_debug = 1 THEN
2450 print_debug(' Lot does not exist for trx_type,parent_trx_type,grand_parent_trx_type=>'||l_transaction_type||'-'||p_parent_txn_type||'-'||p_grand_parent_txn_type, 1);
2451 END IF;
2452 FND_MESSAGE.SET_NAME('GML','GML_RECV_INVALID_LOT');
2453 FND_MESSAGE.SET_TOKEN('LOT_NO',l_lot_no);
2454 FND_MESSAGE.SET_TOKEN('SUBLOT_NO',l_sublot_no);
2455 FND_MSG_PUB.Add;
2456 x_return_status := fnd_api.g_ret_sts_error;
2457 RAISE FND_API.G_EXC_ERROR;
2458 END ;
2459 l_progress := '210' ;
2460 END IF ; -- IF p_parent_txn_type = 'DELIVER'
2461
2462 ELSE -- IF p_new_lot = 'N' THEN
2463 l_progress := '220' ;
2464
2465 IF p_parent_txn_type = 'DELIVER' THEN
2466 IF l_inv_debug = 1 THEN
2467 print_debug(' new lots not allowed for trx_type,parent_trx_type,grand_parent_trx_type=>'||l_transaction_type||'-'||p_parent_txn_type||'-'||p_grand_parent_txn_type, 1);
2468 END IF;
2469 -- new lots not allowed for correction to deliver transaction.
2470 FND_MESSAGE.SET_NAME('GML','GML_RECV_INVALID_LOT');
2471 FND_MESSAGE.SET_TOKEN('LOT_NO',l_lot_no);
2472 FND_MESSAGE.SET_TOKEN('SUBLOT_NO',l_sublot_no);
2473 FND_MSG_PUB.Add;
2474 x_return_status := fnd_api.g_ret_sts_error;
2475 RAISE FND_API.G_EXC_ERROR;
2476 END IF;
2477
2478 IF l_source_document_code = 'RMA' THEN
2479 l_progress := '221' ;
2480
2481 opm_rma_valid_lot(p_oe_order_header_id => l_oe_order_header_id ,
2482 p_oe_order_line_id => l_oe_order_line_id,
2483 p_lot_no => l_lot_no,
2484 p_sublot_no => l_sublot_no,
2485 p_rti_id => l_rti_id,
2486 x_line_set_id => l_line_set_id,
2487 x_oe_lot_quantity => l_oe_lot_quantity,
2488 x_return_status => x_return_status );
2489
2490 END IF; -- IF l_source_document_code = 'RMA' THEN
2491
2492 -- it is a new lot. create it.
2493 validate_lot_attributes(p_lot_attribute_rec => l_lot_attribute_rec,
2494 p_inventory_item_id => l_item_id,
2495 p_organization_id => l_org_id,
2496 p_trans_date => l_transaction_date,
2497 p_rti_id => l_rti_id,
2498 x_return_status => x_return_status
2499 );
2500
2501 IF l_inv_debug = 1 THEN
2502 print_debug(' Validate lot attributes status trx_type,parent_trx_type,grand_parent_trx_type=>'||l_transaction_type||'-'||p_parent_txn_type||'-'||p_grand_parent_txn_type||'-'||x_return_status, 1);
2503 END IF;
2504
2505 IF x_return_status <> 'S' THEN
2506 RAISE fnd_api.g_exc_error;
2507 END IF;
2508
2509 IF l_source_document_code = 'RMA' THEN
2510 l_progress := '223' ;
2511
2512 validate_rma_quantity(p_opm_item_id => p_opm_item_id,
2513 p_lot_id => l_lot_attribute_rec.lot_id,
2514 p_lot_no => l_lot_no,
2515 p_sublot_no => l_sublot_no,
2516 p_oe_order_header_id => l_oe_order_header_id,
2517 p_oe_order_line_id => l_oe_order_line_id ,
2518 p_lot_qty => l_lot_quantity ,
2519 p_unit_of_measure => l_unit_of_measure ,
2520 p_rma_lot_qty => l_oe_lot_quantity,
2521 p_rma_lot_uom => NULL,
2522 p_line_set_id => l_line_set_id,
2523 p_rti_id => l_rti_id,
2524 x_allowed => l_allowed,
2525 x_allowed_quantity => l_allowed_quantity,
2526 x_return_status => x_return_status );
2527
2528 IF x_return_status <> 'S' THEN
2529 RAISE FND_API.G_EXC_ERROR;
2530 END IF;
2531
2532 END IF; -- IF l_source_document_code = 'RMA'
2533
2534 -- validate/derive secondary quantity.
2535 l_progress := '225' ;
2536
2537 IF l_secondary_unit_of_measure IS NOT NULL THEN
2538 IF l_lot_secondary_quantity IS NULL THEN
2539 l_update_lot_qty := TRUE ;
2540 END IF;
2541
2542 GML_ValidateDerive_GRP.Secondary_Qty
2543 ( p_api_version => '1.0'
2544 , p_init_msg_list => 'F'
2545 , p_validate_ind => 'Y'
2546 , p_item_no => p_item_no
2547 , p_unit_of_measure => l_unit_of_measure
2548 , p_lot_id => 0
2549 , p_secondary_unit_of_measure => l_secondary_unit_of_measure
2550 , p_quantity => l_lot_quantity
2551 , p_secondary_quantity => l_lot_secondary_quantity
2552 , x_return_status => x_return_status
2553 , x_msg_count => x_msg_count
2554 , x_msg_data => x_msg_data ) ;
2555
2556 IF l_inv_debug = 1 THEN
2557 print_debug('Validate Secondary qty status trx_type=>'||l_transaction_type||'-'||x_return_status, 1);
2558 print_debug('Secondary qty=>'||l_lot_secondary_quantity, 1);
2559 END IF;
2560
2561 IF x_return_status <> 'S' THEN
2562 RAISE FND_API.G_EXC_ERROR;
2563 END IF;
2564
2565 IF l_update_lot_qty THEN
2566 update mtl_transaction_lots_temp
2567 set secondary_quantity = l_lot_secondary_quantity
2568 where rowid = p_mtlt_rowid ;
2569 END IF;
2570 END IF; -- IF l_secondary_unit_of_measure IS NOT NULL THEN
2571
2572 l_progress := '230' ;
2573
2574 l_new_lot_rec.item_no := p_item_no ;
2575 l_new_lot_rec.lot_no := l_lot_no;
2576 l_new_lot_rec.sublot_no := l_sublot_no;
2577 l_new_lot_rec.expire_date := l_lot_attribute_rec.expiration_date;
2578 l_new_lot_rec.lot_desc := l_lot_desc ;
2579 l_new_lot_rec.qc_grade := NULL ;
2580 l_new_lot_rec.lot_created := l_transaction_date;
2581 l_new_lot_rec.origination_type := 3;
2582 l_new_lot_rec.vendor_lot_no := nvl(l_vendor_lot_no_on_lot,l_vendor_lot_no_on_line);
2583
2584 create_new_lot(p_new_lot_rec => l_new_lot_rec,
2585 p_organization_id => l_org_id,
2586 p_vendor_id => l_vendor_id,
2587 p_vendor_site_id => l_vendor_site_id,
2588 p_from_unit_of_measure => l_unit_of_measure,
2589 p_to_unit_of_measure => l_secondary_unit_of_measure,
2590 p_type_factor => l_lot_secondary_quantity/l_lot_quantity,
2591 p_rti_id => l_rti_id,
2592 x_return_status => x_return_status );
2593
2594 IF l_inv_debug = 1 THEN
2595 print_debug(' Create_new_lot status trx_type,parent_trx_type,grand_parent_trx_type=>'||l_transaction_type||'-'||p_parent_txn_type||'-'||p_grand_parent_txn_type||'-'||x_return_status, 1);
2596 END IF;
2597
2598 IF x_return_status <> 'S' THEN
2599 RAISE FND_API.G_EXC_ERROR;
2600 END IF;
2601 END IF; -- IF p_new_lot = 'N'
2602 END IF;
2603 END IF; -- ELSIF (l_transaction_type = 'CORRECT' AND l_rti_primary_qty > 0) THEN
2604 END IF; -- IF (l_transaction_type IN ('RECEIVE'
2605
2606
2607 EXCEPTION
2608 WHEN FND_API.G_EXC_ERROR THEN
2609 x_return_status := fnd_api.g_ret_sts_error;
2610 insert_errors(p_rti_id => l_rti_id,
2611 p_group_id => NULL,
2612 p_header_interface_id => NULL,
2613 p_column_name => NULL,
2614 p_table_name => 'MTL_TRANSACTION_LOTS_INTERFACE',
2615 p_error_message => FND_MSG_PUB.GET(p_msg_index => FND_MSG_PUB.G_LAST,p_encoded => 'F'),
2616 p_mesg_owner => NULL,
2617 p_Error_Message_name => NULL );
2618
2619 IF l_inv_debug = 1 THEN
2620 print_debug(' Main exception in validate_opm_lot'||l_progress||'-'||substr(FND_MSG_PUB.GET(p_msg_index => FND_MSG_PUB.G_LAST,p_encoded => 'F'),1,100), 1);
2621 END IF;
2622 fnd_msg_pub.count_and_get(p_count => x_msg_count, p_data => x_msg_data);
2623
2624 WHEN OTHERS THEN
2625 x_return_status := fnd_api.g_ret_sts_unexp_error;
2626 insert_errors( p_rti_id => l_rti_id,
2627 p_group_id => null,
2628 p_header_interface_id => null,
2629 p_column_name => null,
2630 p_table_name => 'MTL_TRANSACTION_LOTS_INTERFACE',
2631 p_error_message => 'UNHANDLED EXCEPTION IN VALIDATE_OPM_LOT :' || l_progress||'-' ||
2632 substr(sqlerrm,1,1000),
2633 p_mesg_owner => NULL,
2634 p_Error_Message_name => NULL);
2635 IF l_inv_debug = 1 THEN
2636 print_debug('Unhandled exception in validate_opm_lot'||l_progress||'-'||substr(sqlerrm,1,100), 1);
2637 END IF;
2638 fnd_msg_pub.count_and_get( p_count => x_msg_count, p_data => x_msg_data);
2639 END validate_opm_lot;
2640
2641 END GML_OPM_ROI_GRP ;