[Home] [Help]
PACKAGE BODY: APPS.PO_GML_DB_COMMON
Source
1 PACKAGE BODY PO_GML_DB_COMMON AS
2 /* $Header: GMLPOXCB.pls 120.0 2005/05/25 16:28:32 appldev noship $ */
3
4 /*##########################################################################
5 #
6 # FUNCTION
7 # check_process_org
8 #
9 # DESCRIPTION
10 #
11 # This function checks whether the inventory org. is process or not.
12 #
13 #
14 # MODIFICATION HISTORY
15 # 06-FEB-2001 MChandak Created
16 #
17 ## #######################################################################*/
18
19 -- bug# 3061052 create global package variable to indicate whether
20 -- procurement FP J or higher is installed or not.
21 G_PRC_PATCH_MIN_J BOOLEAN := NULL ;
22
23 FUNCTION check_process_org(x_inventory_org_id IN NUMBER) RETURN VARCHAR2 IS
24 v_process_enabled_flag VARCHAR2(1);
25 v_progress VARCHAR2(3) := '010';
26 BEGIN
27 IF nvl(x_inventory_org_id,-9999) < 0 OR NOT GML_PO_FOR_PROCESS.check_po_for_proc
28 THEN
29 RETURN ('N');
30 END IF;
31 BEGIN
32 SELECT process_enabled_flag INTO v_process_enabled_flag
33 FROM mtl_parameters
34 WHERE organization_id = x_inventory_org_id ;
35 RETURN v_process_enabled_flag;
36
37 EXCEPTION WHEN NO_DATA_FOUND THEN
38 RETURN('N');
39 END;
40
41 EXCEPTION
42 WHEN OTHERS THEN
43 po_message_s.sql_error('check_process_org',v_progress,sqlcode);
44 raise;
45 END check_process_org ;
46
47
48 /*##########################################################################
49 #
50 # FUNCTION
51 # get_opm_uom_code
52 #
53 # DESCRIPTION
54 #
55 #
56 #
57 #
58 # MODIFICATION HISTORY
59 # 06-FEB-2001 MChandak Created
60 #
61 ## #######################################################################*/
62 FUNCTION get_opm_uom_code(x_apps_unit_meas_lookup_code IN VARCHAR2) RETURN VARCHAR2 IS
63 v_um_code SY_UOMS_MST.UM_CODE%TYPE;
64 v_progress VARCHAR2(3) := '010';
65 BEGIN
66 /*
67 Select decode(length(uom.unit_of_measure), 1, uom.unit_of_measure,
68 2, uom.unit_of_measure, 3, uom.unit_of_measure,
69 4, uom.unit_of_measure, uom.uom_code) um_code
70 into v_um_code
71 from mtl_units_of_measure uom
72 WHERE unit_of_measure = x_apps_unit_meas_lookup_code;
73
74 */
75 /* Bug #3514053 */
76
77 Select um_code
78 Into v_um_code
79 From sy_uoms_mst
80 Where unit_of_measure = x_apps_unit_meas_lookup_code;
81
82 RETURN(v_um_code);
83
84 EXCEPTION
85 WHEN OTHERS THEN
86 po_message_s.sql_error('get_opm_uom_code',v_progress,sqlcode);
87 raise;
88
89 END get_opm_uom_code;
90
91
92 /*##########################################################################
93 #
94 # FUNCTION
95 # get_apps_uom_code
96 #
97 # DESCRIPTION
98 #
99 #
100 #
101 #
102 # MODIFICATION HISTORY
103 # 06-FEB-2001 MChandak Created
104 #
105 ## #######################################################################*/
106 FUNCTION get_apps_uom_code(x_opm_um_code IN VARCHAR2) RETURN VARCHAR2 IS
107 v_progress VARCHAR2(3) := '010';
108 v_unit_of_measure MTL_UNITS_OF_MEASURE.unit_of_measure%TYPE;
109 v_delete_mark NUMBER;
110 uom_deleted EXCEPTION ;
111 BEGIN
112 /*
113 SELECT uom.unit_of_measure,decode(sign(sysdate-uom.disable_date),1,1,0)
114 INTO v_unit_of_measure,v_delete_mark
115 FROM mtl_units_of_measure uom
116 WHERE decode(length(uom.unit_of_measure), 1, uom.unit_of_measure,
117 2, uom.unit_of_measure, 3, uom.unit_of_measure,
118 4, uom.unit_of_measure, uom.uom_code) = x_opm_um_code;
119
120 If v_delete_mark = 1 then
121 raise uom_deleted;
122 End If;
123
124 */
125 /* Bug #3514053 */
126
127 select unit_of_measure,delete_mark
128 into v_unit_of_measure,v_delete_mark
129 from sy_uoms_mst
130 where um_code = x_opm_um_code;
131
132 If v_delete_mark = 1 then
133 raise uom_deleted;
134 End If;
135
136 RETURN(v_unit_of_measure);
137
138 EXCEPTION
139 WHEN uom_deleted THEN
140 FND_MESSAGE.Set_Name('RLM', 'RLM_UOM_INACTIVE');
141 FND_MESSAGE.Set_Token('UOM_CODE',x_opm_um_code);
142 APP_EXCEPTION.Raise_Exception;
143 WHEN OTHERS THEN
144 po_message_s.sql_error('get_apps_uom_code',v_progress,sqlcode);
145 raise;
146
147 END get_apps_uom_code;
148
149
150 /*##########################################################################
151 #
152 # FUNCTION
153 # get_quantity_onhand
154 #
155 # DESCRIPTION
156 # This function returns onhand quantity for a given item,lot/sublot,
157 #
158 #
159 # MODIFICATION HISTORY
160 # 06-FEB-2001 jsrivast Created
161 #
162 ## #######################################################################*/
163
164
165 FUNCTION get_quantity_onhand( pitem_id IN NUMBER
166 ,plot_no IN VARCHAR2
167 ,psublot_no IN VARCHAR2
168 ,porg_id IN NUMBER
169 ,plocator_id IN NUMBER
170 ) RETURN NUMBER IS
171
172 v_progress VARCHAR2(3) := '010';
173 l_whse_code ic_whse_mst.whse_code%TYPE;
174 l_location ic_loct_mst.location%TYPE;
175 l_quantity_onhand NUMBER;
176 l_lot_id ic_lots_mst.lot_id%TYPE;
177 BEGIN
178 BEGIN
179 --get lot id
180 IF (psublot_no is NULL) THEN
181 select lot_id INTO l_lot_id
182 from ic_lots_mst
183 where item_id = pitem_id
184 and lot_no = plot_no
185 and sublot_no is null;
186
187 ELSE
188
189 select lot_id INTO l_lot_id
190 from ic_lots_mst
191 where item_id = pitem_id
192 and lot_no = plot_no
193 and sublot_no = psublot_no;
194
195 END IF;
196
197 EXCEPTION WHEN NO_DATA_FOUND
198 THEN
199 RETURN 0;
200
201 END;
202
203 --get whse code
204 select whse_code INTO l_whse_code
205 from ic_whse_mst
206 where mtl_organization_id = porg_id;
207
208
209 --get location code
210 IF (plocator_id IS NULL) THEN
211 l_location := fnd_profile.value('IC$DEFAULT_LOCT');
212
213 ELSE
214 BEGIN
215 select location INTO l_location
216 from ic_loct_mst
217 where whse_code = l_whse_code
218 and inventory_location_id = plocator_id;
219
220 EXCEPTION WHEN NO_DATA_FOUND
221 THEN
222 RETURN 0;
223 END;
224 END IF;
225
226 --OK , all set let's get quantity onhand
227 BEGIN
228 -- Bug 3869782 Round loct_onhand to 6 decimal
229 select ROUND(loct_onhand,6) INTO l_quantity_onhand
230 from ic_loct_inv
231 where item_id = pitem_id
232 and lot_id = l_lot_id
233 and whse_code = l_whse_code
234 and location = l_location;
235
236 EXCEPTION WHEN NO_DATA_FOUND
237 THEN
238 RETURN 0;
239 END;
240
241 return l_quantity_onhand;
242
243 EXCEPTION
244 WHEN OTHERS THEN
245 po_message_s.sql_error('get_quantity_onhand',v_progress,sqlcode);
246 raise;
247
248 END get_quantity_onhand;
249
250 /*##########################################################################
251 #
252 # PROCEDURE
253 # insert_po_errors
254 #
255 # DESCRIPTION
256 # This procedure inserts records in po_interface_errors table.
257 # This is an autonomous transaction.
258 #
259 #
260 # MODIFICATION HISTORY
261 #
262 #########################################################################*/
263 PROCEDURE INSERT_PO_ERRORS( p_interface_type IN VARCHAR2
264 , p_interface_transaction_id IN NUMBER
265 , p_error_message IN VARCHAR2
266 , p_processing_date IN DATE
267 , p_creation_date IN DATE
268 , p_created_by IN NUMBER
269 , p_last_update_date IN DATE
270 , p_last_updated_by IN NUMBER
271 , p_last_update_login IN NUMBER
272 , p_request_id IN NUMBER
273 , p_program_application_id IN NUMBER
274 , p_program_id IN NUMBER
275 , p_program_update_date IN DATE
276 , p_table_name IN VARCHAR2) IS
277
278 PRAGMA AUTONOMOUS_TRANSACTION;
279
280
281 BEGIN
282 INSERT INTO po_interface_errors
283 ( interface_type
284 , interface_transaction_id
285 , error_message
286 , processing_date
287 , creation_date
288 , created_by
289 , last_update_date
290 , last_updated_by
291 , last_update_login
292 , request_id
293 , program_application_id
294 , program_id
295 , program_update_date
296 , table_name )
297 VALUES ( p_interface_type
298 , p_interface_transaction_id
299 , p_error_message
300 , p_processing_date
301 , p_creation_date
302 , p_created_by
303 , p_last_update_date
304 , p_last_updated_by
305 , p_last_update_login
306 , p_request_id
307 , p_program_application_id
308 , p_program_id
309 , p_program_update_date
310 , p_table_name);
311
312 COMMIT;
313
314 EXCEPTION
315 WHEN OTHERS THEN
316 NULL;
317
318 END INSERT_PO_ERRORS;
319
320 /*##########################################################################
321 #
322 # FUNCTION
323 # GET_SHIPPED_QTY
324 #
325 # DESCRIPTION
326 # This function returns Primary shipped quantity.
327 # required by the Receiving transactions screen.
328 #
329 # MODIFICATION HISTORY
330 # 01-DEC-2004 pkanetka Created this function for Bug 3950010 FP for 3936459
331 #
332 ## #######################################################################*/
333
334 FUNCTION GET_SHIPPED_QTY(l_delivery_detail_id IN NUMBER, l_trans_qty2 IN NUMBER) RETURN NUMBER IS
335
336 l_shipped_quantity Number;
337 l_shipped_quantity2 Number;
338
339 Cursor cur_shipped_qty is
343 BEGIN
340 Select shipped_quantity, shipped_quantity2 from wsh_delivery_details
341 where delivery_detail_id = l_delivery_detail_id;
342
344
345 OPEN cur_shipped_qty;
346 FETCH cur_shipped_qty INTO l_shipped_quantity, l_shipped_quantity2;
347 CLOSE cur_shipped_qty;
348 -- If fully receiving only then get shipped quantity as trans quantity.
349 IF ( l_trans_qty2 = l_shipped_quantity2) THEN
350 RETURN l_shipped_quantity;
351 ELSE
352 RETURN 0;
353 END IF;
354
355 EXCEPTION
356 WHEN OTHERS THEN
357 RETURN 0;
358
359 END GET_SHIPPED_QTY;
360
361 /*##########################################################################
362 #
363 # FUNCTION
364 # CREATE_INV_TRANS_OPM
365 #
366 # DESCRIPTION
367 # This function is the inventory engine called from the Receiving Transaction Processor
368 # when it encounters a process item (OPM item shipped to an process organization).
369 #
370 #
371 # MODIFICATION HISTORY
372 # 06-FEB-2001 pbamb Created
373 # 23-MAR-2003 pbamb Added code to handel internal orders with direct shipment
374 #
375 #########################################################################*/
376 PROCEDURE CREATE_INV_TRANS_OPM (P_interface_trx_id IN NUMBER,
377 P_Line_Id IN NUMBER,
378 X_Return_Status IN OUT NOCOPY VARCHAR2) IS
379 l_organization_id NUMBER;
380 l_doc_id NUMBER;
381 l_line_id NUMBER;
382 l_ora_item_id NUMBER;
383 l_locator_id NUMBER;
384 l_to_locator_id NUMBER;
385 l_from_locator_id NUMBER;
386 l_trans_date DATE;
387 l_trans_qty NUMBER;
388 l_trans_qty2 NUMBER;
389 l_lot_exists NUMBER;
390 l_negate_lot_qty NUMBER;
391 l_return_status VARCHAR2(1000);
392 l_msg_count NUMBER;
393 l_msg_data VARCHAR2(1000);
394 dummy VARCHAR2(1000);
395 l_trans_opm_um VARCHAR2(25);
396 l_trans_opm_um2 VARCHAR2(25);
397
398 l_trans_um rcv_transactions_interface.primary_unit_of_measure%TYPE;
399 l_trans_um2 rcv_transactions_interface.primary_unit_of_measure%TYPE;
400 l_src_doc_type rcv_transactions_interface.source_document_code%TYPE;
401 l_trx_type rcv_transactions_interface.transaction_type%TYPE;
402 l_destination_type_code rcv_transactions_interface.destination_type_code%TYPE;
403 l_req_line_id rcv_transactions_interface.requisition_line_id%TYPE;
404 l_receipt_source_code rcv_transactions_interface.receipt_source_code%TYPE;
405
406 l_tran_row ic_tran_cmp%ROWTYPE;
407 l_tran_rec gmi_trans_engine_pub.ictran_rec;
408
409 l_whse_code ic_tran_pnd.whse_code%TYPE;
410 l_orgn_code ic_tran_pnd.orgn_code%TYPE;
411 l_loct_ctl ic_whse_mst.loct_ctl%TYPE;
412 l_item_loct_ctl ic_item_mst.loct_ctl%TYPE;
413 l_item_lot_ctl ic_item_mst.lot_ctl%TYPE;
414 l_co_code ic_tran_pnd.co_code%TYPE;
415 l_item_id ic_tran_pnd.item_id%TYPE;
416 l_lot_status ic_tran_pnd.lot_status%TYPE;
417 l_qc_grade ic_tran_pnd.qc_grade%TYPE := NULL;
418 l_location ic_tran_pnd.location%TYPE;
419 l_user_id ic_tran_pnd.created_by%TYPE;
420 l_doc_type ic_tran_pnd.doc_type%TYPE;
421 l_non_inv ic_item_mst.noninv_ind%TYPE;
422 l_lot_no ic_lots_mst.lot_no%TYPE;
423 l_sublot_no ic_lots_mst.sublot_no%TYPE;
424 ex_exception_found EXCEPTION;
425
426 l_creation_date DATE;
427
428 l_oe_line_id NUMBER;
429 l_oe_line_detail_id NUMBER;
430 l_auto_transact_code VARCHAR2(25);
431
432 l_loop_cnt NUMBER;
433 l_dummy_cnt NUMBER;
434 l_transaction_type VARCHAR2(25);
435 l_group_id NUMBER;
436 l_dummy VARCHAR2(2) := 'N';
437 l_created_by NUMBER;
438 l_last_update_date DATE;
439 l_last_updated_by NUMBER;
440 l_last_update_login NUMBER;
441 l_request_id NUMBER;
442 l_program_application_id NUMBER;
443 l_program_id NUMBER;
444 l_error_message VARCHAR2(1000);
445
446 l_receipt_qty NUMBER;
447 l_receipt_unit_of_measure VARCHAR2(25);
448 l_primary_quantity NUMBER;
449 l_receipt_opm_um VARCHAR2(25);
450 l_lot_receive_qty NUMBER;
451
452 -- lot status: bug 3278027
453 l_item_sts_ctl ic_item_mst.status_ctl%TYPE;
454 l_rec_cnt NUMBER := 0;
455 l_ship_status VARCHAR2(4) := NULL;
456 l_rcpt_status VARCHAR2(4) := NULL;
457 l_txn_allowed VARCHAR2(1) := NULL;
458 -- Bug 3936459
459 l_comments VARCHAR2(240);
463 CURSOR cr_intorg_tran IS
460 l_wdd NUMBER;
461 l_temp_qty NUMBER;
462
464 select TRANS_ID ,
465 ITEM_ID ,
466 LINE_ID ,
467 CO_CODE ,
468 ORGN_CODE ,
469 WHSE_CODE ,
470 LOT_ID ,
471 LOCATION ,
472 DOC_ID ,
473 DOC_TYPE ,
474 DOC_LINE ,
475 LINE_TYPE ,
476 REASON_CODE ,
477 CREATION_DATE ,
478 TRANS_DATE ,
479 TRANS_QTY ,
480 TRANS_QTY2 ,
481 QC_GRADE ,
482 LOT_STATUS ,
483 TRANS_STAT ,
484 TRANS_UM ,
485 TRANS_UM2 ,
486 OP_CODE ,
487 COMPLETED_IND ,
488 STAGED_IND ,
489 GL_POSTED_IND ,
490 EVENT_ID ,
491 DELETE_MARK ,
492 TEXT_CODE ,
493 LAST_UPDATE_DATE ,
494 CREATED_BY ,
495 LAST_UPDATED_BY ,
496 LAST_UPDATE_LOGIN ,
497 PROGRAM_APPLICATION_ID ,
498 PROGRAM_ID ,
499 PROGRAM_UPDATE_DATE ,
500 REQUEST_ID ,
501 REVERSE_ID ,
502 PICK_SLIP_NUMBER ,
503 MVT_STAT_STATUS ,
504 MOVEMENT_ID ,
505 LINE_DETAIL_ID ,
506 INVOICED_FLAG
507 FROM ic_tran_pnd
508 WHERE doc_type = 'OMSO'
509 AND line_id = l_oe_line_id
510 AND line_detail_id = l_oe_line_detail_id
511 AND COMPLETED_IND = 1;
512
513 cr_intorg_tran_rec cr_intorg_tran%ROWTYPE;
514
515 -- ROI enhancements bug# 3061052
516 -- create two new cursors for retreiving lot information.
517 l_validation_flag rcv_transactions_interface.validation_flag%TYPE ;
518
519
520 CURSOR cr_rcv_lots_interface IS
521 SELECT primary_quantity,
522 quantity,
523 secondary_quantity,
524 lot_num,
525 sublot_num,
526 reason_code
527 FROM rcv_lots_interface
528 WHERE interface_transaction_id = p_interface_trx_id ;
529
530 CURSOR cr_mtl_transaction_lots_temp IS
531 SELECT primary_quantity,
532 transaction_quantity quantity,
533 secondary_quantity,
534 lot_number lot_num,
535 sublot_num,
536 reason_code
537 FROM mtl_transaction_lots_temp
538 WHERE product_transaction_id = p_interface_trx_id
539 AND product_code = 'RCV' ;
540
541 l_lot_attributes_rec cr_rcv_lots_interface%ROWTYPE ;
542
543 -- Bug 3876496
544 l_line_num NUMBER := 0;
545
546 -- PK Bug 3991705 Declarations
547 l_recv_qty NUMBER;
548 l_receipt_um VARCHAR2(25);
549 l_return_qty NUMBER;
550 l_return_um VARCHAR2(25);
551 l_lot_txn_qty NUMBER;
552 l_rettxn_qty NUMBER;
553 l_rettxn_qty2 NUMBER;
554 l_parent_transaction_id NUMBER;
555 l_copy_return_txn BOOLEAN := FALSE;
556 l_lot_qty_mismatch BOOLEAN := TRUE;
557
558
559 CURSOR Cur_Receipt_qty IS
560 Select QUANTITY, UNIT_OF_MEASURE
561 From rcv_transactions
562 where TRANSACTION_ID = l_parent_transaction_id;
563
564
565 CURSOR Cur_rcv_lot_txn(l_parent_transaction_id IN NUMBER, v_lot_num IN VARCHAR2, v_sublot_num IN VARCHAR2) IS
566 Select PRIMARY_QUANTITY
567 From rcv_lot_transactions
568 where TRANSACTION_ID = l_parent_transaction_id
569 and LOT_NUM = v_lot_num
570 and SUBLOT_NUM = v_sublot_num;
571
572 CURSOR Cur_opm_txn(l_line_id IN NUMBER, v_item_id IN NUMBER, v_lot_id IN NUMBER) IS
573 SELECT trans_qty, trans_qty2
574 FROM IC_TRAN_PND
575 where doc_type = 'PORC'
576 and line_id = l_line_id
577 and delete_mark = 0
578 and completed_ind = 1
579 and lot_id = v_lot_id;
580
581 BEGIN
582
583 x_return_status := '0';
584 /* Get the details from the transaction table */
585 -- bug# 3061052 get from_locator_id also.
586 -- starting from 11.5.10 , receiving has added a from_locator_id
587 -- the following logic is being used.
588 -- assign locator_id to to_locator_id variable instead of locator_id.
589 /**
590 Trans. Type Locator_id From_locator_id Parent trans type
591 RECEIVE R1 - -
592 DELIVER D1 R1 RECEIVE
593 CORRECT(-VE) R1 D1 DELIVER
594 CORRECT(+VE) D1 R1 DELIVER
595 RTR R1 D1 DELIVER
596 RTC/RTV - D1 DELIVER
597 **/
598
599 BEGIN
600 SELECT rti.source_document_code,
601 rti.transaction_type,
602 rti.to_organization_id,
603 rti.shipment_header_id,
604 rti.shipment_line_id,
605 rti.item_id,
606 rti.locator_id,
607 rti.from_locator_id,
608 rti.transaction_date,
609 rti.creation_date,
610 rti.PRIMARY_QUANTITY,
611 rti.PRIMARY_UNIT_OF_MEASURE,
612 rti.SECONDARY_QUANTITY,
613 rti.SECONDARY_UNIT_OF_MEASURE,
614 rti.created_by ,
615 rti.interface_source_line_id,
616 rti.document_shipment_line_num,
617 rti.auto_transact_code,
618 rti.transaction_type,
619 rti.group_id,
620 rti.created_by,
621 rti.last_update_date,
622 rti.last_updated_by,
623 rti.last_update_login,
624 rti.request_id,
625 rti.program_application_id,
626 rti.program_id,
627 rti.quantity,
628 rti.unit_of_measure,
629 rti.destination_type_code,
630 rti.validation_flag,
631 rti.requisition_line_id, -- lot status: bug 3278027
632 rti.receipt_source_code, -- lot status: bug 3278027
633 rti.parent_transaction_id, -- Bug 3991705
634 rsl.comments -- Bug 3936459
635 INTO l_src_doc_type,
636 l_trx_type,
637 l_organization_id,
638 l_doc_id,
639 l_line_id,
640 l_ora_item_id,
641 l_to_locator_id,
642 l_from_locator_id,
643 l_trans_date,
644 l_creation_date,
645 l_trans_qty,
646 l_trans_um,
647 l_trans_qty2,
648 l_trans_um2,
649 l_user_id,
650 l_oe_line_id,
651 l_oe_line_detail_id,
652 l_auto_transact_code,
653 l_transaction_type,
654 l_group_id,
655 l_created_by,
656 l_last_update_date,
657 l_last_updated_by,
658 l_last_update_login,
659 l_request_id,
660 l_program_application_id,
661 l_program_id ,
662 l_receipt_qty,
663 l_receipt_unit_of_measure,
664 l_destination_type_code,
665 l_validation_flag,
666 l_req_line_id,
667 l_receipt_source_code,
668 l_parent_transaction_id,
669 l_comments
670 FROM rcv_transactions_interface rti,
671 rcv_shipment_lines rsl
672 WHERE rti.shipment_line_id = rsl.shipment_line_id(+) and
673 rti.interface_transaction_id = p_interface_trx_id;
674
675 EXCEPTION
676 WHEN NO_DATA_FOUND THEN
677 x_return_status := '-5';
678 raise ex_exception_found;
679 END;
680
681 -- PK Bug 3991705
682 /*
683 IF (l_trx_type = 'RETURN TO VENDOR' or l_trx_type = 'RETURN TO RECEIVING')
684
685 Check Profile OPtion GML Validate return
686 Check Receipt_qty = Return qty in same UM's in rcv_transactions
687 Check if rcv_Lots_interface records for return match with rcv_lot_transactions for receipt
688 then find ic_tran_pnd record for rcv_lots_interface for receipt and copy the quantitiy.
689
690 If anything does not match do Nothing */
691
692 IF (NVL(fnd_profile.value('GML_VAL_RET_CORR_LOT_QTY'), 'Y') = 'N') AND
693 (l_trx_type = 'RETURN TO VENDOR' or l_trx_type = 'RETURN TO RECEIVING') THEN
694 -- Now check for receipt_qty matching Return_qty
695 OPEN Cur_Receipt_qty;
696 FETCH Cur_Receipt_qty INTO l_recv_qty, l_receipt_um;
697 CLOSE Cur_Receipt_qty;
698
699 IF (l_recv_qty = l_receipt_qty) AND (l_receipt_um = l_receipt_unit_of_measure) THEN
700 -- Compare current txn qty with rcv_lot_transaction Should loop through all lot txn
701 FOR rliret in (select * from rcv_lots_interface where interface_transaction_id = p_interface_trx_id)
702 LOOP
703 OPEN Cur_rcv_lot_txn(l_parent_transaction_id, rliret.lot_num, rliret.sublot_num);
704 FETCH Cur_rcv_lot_txn INTO l_lot_txn_qty;
705 CLOSE Cur_rcv_lot_txn;
706
707 IF (l_lot_txn_qty <> rliret.PRIMARY_QUANTITY) THEN
708 l_lot_qty_mismatch := FALSE;
709 EXIT;
710 END IF;
711 END LOOP;
712
713 IF (l_lot_qty_mismatch = TRUE) THEN
714 l_copy_return_txn := TRUE;
715 END IF;
716
717
718 END IF; -- (l_recv_qty = l_return_qty) AND (l_receipt_um = l_return_um)
719
720
721
722 END IF; -- (l_val_ret_qty = 'N") AND(l_trx_type = 'RETURN TO VENDOR' or l_trx_type = 'RETURN TO RECEIVING')
723
724 -- End Bug 3991705
725
726 -- Begin Bug 3876496
727 BEGIN
728
729 select rs.line_num
730 into l_line_num
731 from rcv_shipment_lines rs, rcv_transactions rt
732 where rt.interface_transaction_id = p_interface_trx_id
733 and rs.shipment_header_id = rt.shipment_header_id
734 and rs.shipment_line_id = rt.shipment_line_id
735 and rt.transaction_type IN('DELIVER','RETURN TO RECEIVING','CORRECT');
736
737 EXCEPTION
738 WHEN OTHERS THEN
739 NULL;
740
741 END;
742 -- End Bug 3876496
743
744 -- If its an internal order with transfer type intransit then return.
745 IF (l_trx_type = 'SHIP' AND l_auto_transact_code = 'SHIP') THEN
746 x_return_status := '1';
747 RETURN;
748 END IF;
749
750 -- 3061052
751 IF PO_CODE_RELEASE_GRP.Current_Release >= PO_CODE_RELEASE_GRP.PRC_11i_Family_Pack_J THEN
752 G_PRC_PATCH_MIN_J := TRUE;
753 ELSE
754 G_PRC_PATCH_MIN_J := FALSE;
755 END IF;
756
757 l_negate_lot_qty := 0;
758
759 /* In case of returns negate the transaction quantity */
760 IF (l_trx_type = 'RETURN TO VENDOR' or l_trx_type = 'RETURN TO RECEIVING' or l_trx_type = 'RETURN TO CUSTOMER') THEN
761 l_trans_qty := -l_trans_qty;
762 l_trans_qty2 := -l_trans_qty2;
763 l_negate_lot_qty := 1;
764 END IF;
765
766 -- bug# 3061052
767 -- from PRC FP J onwards for -ve corrections and returns, take the
768 -- from_locator_id because from_locator_id stores the source location
769 -- in this case DELIVER locatoin.
770 -- below FP J , use locator_id ( to_locator_id).
771
772 IF G_PRC_PATCH_MIN_J THEN
773 IF l_trans_qty < 0 THEN
774 l_locator_id := l_from_locator_id ;
775 ELSE
776 l_locator_id := l_to_locator_id ;
777 END IF;
778 ELSE
779 l_locator_id := l_to_locator_id ;
780 END IF;
781
782
783 /* Get the whse, orgn and the company codes */
784 BEGIN
785 SELECT w.whse_code, w.orgn_code, o.co_code,w.loct_ctl
786 INTO l_whse_code, l_orgn_code, l_co_code, l_loct_ctl
787 FROM ic_whse_mst w, sy_orgn_mst o
788 WHERE mtl_organization_id = l_organization_id
789 AND w.orgn_code = o.orgn_code;
790
791 EXCEPTION
792 WHEN NO_DATA_FOUND THEN
793 x_return_status := '-10';
794 raise ex_exception_found;
795 END;
796 /* Get the OPM item_id */
797 BEGIN
798 -- lot status: bug 3278027, added l_item_sts_ctl
799 SELECT oi.item_id , oi.noninv_ind, oi.loct_ctl, oi.lot_ctl, oi.status_ctl
800 INTO l_item_id, l_non_inv, l_item_loct_ctl, l_item_lot_ctl, l_item_sts_ctl
801 FROM ic_item_mst oi, mtl_system_items ai
802 WHERE ai.organization_id = l_organization_id
803 AND ai.inventory_item_id = l_ora_item_id
804 AND ai.segment1 = oi.item_no;
805
806 EXCEPTION
807 WHEN NO_DATA_FOUND THEN
808 x_return_status := '-20';
809 raise ex_exception_found;
810 END;
811
812 /* Validate the UOM exist in OPM and fetch the corresponding OPM um_code*/
813 BEGIN
814 IF l_trans_um IS NOT NULL THEN
815 l_trans_opm_um := get_opm_uom_code(l_trans_um);
816 END IF;
817
818 IF l_trans_um2 IS NOT NULL THEN
819 l_trans_opm_um2 := get_opm_uom_code(l_trans_um2);
820 END IF;
821
822 --Bug# 2968924 added receipt uom and get corresponding opm uom
823 --we will need it to convert lot transaction qty into primary qty.
824 IF l_receipt_unit_of_measure IS NOT NULL THEN
825 l_receipt_opm_um := get_opm_uom_code(l_receipt_unit_of_measure);
826 END IF;
827 EXCEPTION
828 WHEN OTHERS THEN
829 x_return_status := '-30';
830 raise ex_exception_found;
831 END;
832
833 l_location := fnd_profile.value('IC$DEFAULT_LOCT');
834
835 --2491449 Preetam Bamb added check for item location control
836 --send location to the inventory API only if both the item and the
837 --warehouse are location controlled.Else send the default location populated above.
838
839 IF l_locator_id is NOT NULL and l_loct_ctl * l_item_loct_ctl = 1
840 then
841 BEGIN
842 select location
843 into l_location
844 from ic_loct_mst
845 where whse_code = l_whse_code
846 and inventory_location_id = l_locator_id;
847 EXCEPTION
848 When NO_DATA_FOUND then
849 x_return_status := '-40';
850 raise ex_exception_found;
851 END;
852 ELSIF l_locator_id is NOT NULL and l_loct_ctl * l_item_loct_ctl > 1
853 THEN
854 BEGIN
855 select substrb(segment1,1,16)
856 into l_location
857 from mtl_item_locations
858 where inventory_location_id = l_locator_id;
859 EXCEPTION
860 When NO_DATA_FOUND then
861 x_return_status := '-50';
862 raise ex_exception_found;
863 END;
864 -- Bug 3597203 following condition added.
865 ELSIF l_locator_id is NULL and l_loct_ctl * l_item_loct_ctl >= 1
866 THEN
867 x_return_status := '-55';
868 raise ex_exception_found;
869 END IF;
870
871 /* Assign the values to the transaction record */
872 l_tran_rec.item_id := l_item_id;
873 l_tran_rec.line_id := p_line_id;
874 l_tran_rec.co_code := l_co_code;
875 l_tran_rec.orgn_code := l_orgn_code;
876 l_tran_rec.whse_code := l_whse_code;
877 l_tran_rec.doc_id := l_doc_id;
878 l_tran_rec.doc_type := 'PORC';
879 -- Bug 3876496 assigned l_line_num instead of zero.
880 l_tran_rec.doc_line := l_line_num;
881 l_tran_rec.line_type := 0;
882 l_tran_rec.reason_code := NULL;
883
884 --Bug 2407358 Trans date in ic_tran_pnd does not have time stamp since transaction_date
885 --from rcv_Transactions_itnerface does not have time stamp
886 --If the profile option GML: Use Creation Date as Transaction Date is set to Yes then
887 --see if the date part of the creation_date and transaction_date from RTI table are same
888 --if they are then use the creation_date which has the time stamp else use the transaction_date
889
890 -- 3061052. starting from PRC FP J , receipt date in receiving forms will have a time portion.
891
892 IF G_PRC_PATCH_MIN_J THEN
893 l_tran_rec.trans_date := l_trans_date;
894 ELSE
895 IF G_USE_CREATION_DATE = 'Y' THEN
896 IF trunc(l_creation_date) = trunc(l_trans_date) THEN
897 l_tran_rec.trans_date := l_creation_date;
898 ELSE
899 l_tran_rec.trans_date := l_trans_date;
900 END IF;
901 ELSE
902 l_tran_rec.trans_date := l_trans_date;
903 END IF;
904 END IF;
905
906 l_tran_rec.trans_qty := l_trans_qty;
907 l_tran_rec.trans_qty2 := l_trans_qty2;
908 l_tran_rec.qc_grade := l_qc_grade;
909 l_tran_rec.lot_id := 0 ; /* (to be populated later) */
910 l_tran_rec.location := l_location;
911 l_tran_rec.lot_no := NULL;
912 l_tran_rec.sublot_no := NULL;
913 l_tran_rec.lot_status := NULL;
914 l_tran_rec.trans_stat := NULL;
915 l_tran_rec.trans_um := l_trans_opm_um;
916 l_tran_rec.trans_um2 := l_trans_opm_um2;
917 l_tran_rec.user_id := l_user_id;
918
919 l_tran_rec.staged_ind := 0;
920 --l_tran_rec.delete_mark := 0;
921 --l_tran_rec.gl_posted_ind := 0;
922 l_tran_rec.event_id := 0;
923
924
925 /* For internal order between 2 process org which have their shipping network set to
926 DIRECT instead of INTRANSIT the transaction type is SHIP and the PROC transactions should mimic
927 the OMSO transactions for lot information*/
928
929 IF l_trx_type = 'SHIP' and l_auto_transact_code = 'DELIVER' and l_oe_line_id IS NOT NULL
930 THEN
931
932 OPEN cr_intorg_tran;
933 FETCH cr_intorg_tran INTO cr_intorg_tran_rec;
934 IF cr_intorg_tran%NOTFOUND THEN
935 CLOSE cr_intorg_tran;
936 x_return_status := '-60';
937 raise ex_exception_found;
938 END IF;
939
940 /* Assign the values to the transaction record */
941 l_tran_rec.item_id := l_item_id;
942 l_tran_rec.line_id := p_line_id;
943 l_tran_rec.co_code := l_co_code;
944 l_tran_rec.orgn_code := l_orgn_code;
945 l_tran_rec.whse_code := l_whse_code;
946 l_tran_rec.doc_id := l_doc_id;
947 l_tran_rec.doc_type := 'PORC';
948 -- Bug 3876496 assigned l_line_num instead of zero.
949 l_tran_rec.doc_line := l_line_num;
950 l_tran_rec.line_type := 0;
951
952 l_tran_rec.trans_qty := cr_intorg_tran_rec.trans_qty * -1;
953 l_tran_rec.trans_qty2 := cr_intorg_tran_rec.trans_qty2 * -1;
954 l_tran_rec.trans_um := cr_intorg_tran_rec.trans_um;
955 l_tran_rec.trans_um2 := cr_intorg_tran_rec.trans_um2;
956 l_tran_rec.lot_id := cr_intorg_tran_rec.lot_id;
957 l_tran_rec.reason_code := cr_intorg_tran_rec.reason_code;
958 l_tran_rec.location := l_location;
959
960
961 IF l_tran_rec.lot_id > 0 THEN
962
963 select qc_grade
964 into l_tran_rec.qc_grade
965 from ic_lots_mst
966 where item_id = l_tran_rec.item_id
967 and lot_id = l_tran_rec.lot_id;
968
969 /* Select lot status Check a record in ic_loct_inv for the item,lot,warehouse,location
970 if no record in ic_loct_inv then get default status from ic_item_mst */
971
972 -- lot status: bug 3278027, this is for direct shipment of internal orders
973 -- Bug 3917381 changed following IF condition from l_item_sts_ctl = 1 to l_item_sts_ctl <> 0
974 IF l_item_sts_ctl <> 0 THEN
975 IF ( GML_INTORD_LOT_STS.G_retain_ship_lot_sts = 'Y'
976 AND l_receipt_source_code = 'INTERNAL ORDER' ) THEN
977
978 GML_INTORD_LOT_STS.derive_porc_lot_status( p_item_id => l_item_id
979 , p_whse_code => l_tran_rec.whse_code
980 , p_lot_id => l_tran_rec.lot_id
981 , p_location => l_tran_rec.location
982 , p_ship_lot_status => cr_intorg_tran_rec.lot_status
983 , x_rcpt_lot_status => l_rcpt_status
984 , x_txn_allowed => l_txn_allowed
985 , x_return_status => l_return_status
986 , x_msg_data => l_msg_data );
987
988 -- bug 3590359
989 -- proper error message was not getting logged in po_interface_erors in case
990 -- inventory lot status is different than shipped lot status.
991 -- the above API derive_porc_lot_status returns receipt status as NULL
992 -- in case transaction is not allowed. We need to get the actual receipt status
993 -- from the inventory for the message purpose.
994
995 IF l_return_status = 'S' THEN
996 IF (l_txn_allowed = 'N' OR l_rcpt_status IS NULL) THEN
997
998 -- start bug 3590359
999 BEGIN
1000
1001 SELECT lot_status
1002 INTO l_rcpt_status
1003 FROM ic_loct_inv
1004 WHERE item_id = l_item_id
1005 AND whse_code = l_tran_rec.whse_code
1006 AND lot_id = l_tran_rec.lot_id
1007 AND location = l_tran_rec.location ;
1008
1009 EXCEPTION WHEN OTHERS THEN
1010 l_rcpt_status := NULL ;
1011
1012 END ;
1013
1014 FND_MESSAGE.SET_NAME('GMI', 'GMI_INTORD_LOTSTS_ERROR');
1015 FND_MESSAGE.SET_TOKEN('S1',cr_intorg_tran_rec.lot_status);
1016 FND_MESSAGE.SET_TOKEN('S2',l_rcpt_status);
1017
1018 l_msg_data := FND_MESSAGE.GET ;
1019
1020 -- end bug 3590359
1021
1022 x_return_status := '-61';
1023 raise ex_exception_found;
1024
1025 END IF;
1026 END IF;
1027
1028 -- in case of error , the above api puts message in l_msg_data.
1029
1030 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
1031 x_return_status := '-62';
1032 raise ex_exception_found;
1033 END IF;
1034
1035 IF (l_txn_allowed = 'Y' AND l_return_status = FND_API.G_RET_STS_SUCCESS) THEN
1036 l_tran_rec.lot_status := l_rcpt_status;
1037 END IF;
1038 ELSE
1039 BEGIN
1040 select lot_status
1041 into l_tran_rec.lot_status
1042 from ic_loct_inv
1043 where item_id = l_item_id
1044 and WHSE_CODE = l_tran_rec.whse_code
1045 and LOT_ID = l_tran_rec.lot_id
1046 and LOCATION = l_tran_rec.location;
1047
1048 EXCEPTION
1049 When NO_DATA_FOUND then
1050 select lot_status
1051 into l_tran_rec.lot_status
1052 from ic_item_mst
1053 where item_id = l_item_id;
1054
1055 When OTHERS then
1056 x_return_status := '-70';
1057 raise ex_exception_found;
1058 END;
1059 END IF; /* IF ( GML_INTORD_LOT_STS.G_retain_ship_lot_sts = 'Y' */
1060 END IF; /* IF l_item_sts_ctl <> 0 */
1061
1062 END IF; /* IF l_tran_rec.lot_id is not null */
1063
1064 gmi_trans_engine_pub.create_completed_transaction(
1065 p_api_version => 1.0,
1066 p_init_msg_list => FND_API.G_FALSE,
1067 p_commit => FND_API.G_FALSE,
1068 p_validation_level => FND_API.G_VALID_LEVEL_FULL,
1069 p_tran_rec => l_tran_rec,
1070 x_tran_row => l_tran_row,
1071 x_return_status => l_return_status,
1072 x_msg_count => l_msg_count,
1073 x_msg_data => l_msg_data,
1074 p_table_name => 'IC_TRAN_PND');
1075
1076
1077
1078 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
1079 x_return_status := '-80';
1080 raise ex_exception_found;
1081 END IF;
1082
1083 --every thing is successful then return 1
1084 x_return_status := '1';
1085
1086 RETURN; /*Transactions created in ic_tran_pnd for Direct Shipment and so return from here*/
1087
1088 END IF; /*l_trx_type = 'SHIP' and l_auto_transact_code = 'DELIVER'*/
1089
1090 /* Now create the completed transaction */
1091 /* If the lot information exists create transaction for each lot and quantity*/
1092 l_lot_exists := 0;
1093 IF (l_trans_qty < 0) THEN
1094 l_negate_lot_qty := 1;
1095 END IF;
1096
1097 -- ROI enhancements bug# 3061052
1098 -- use mtl_transaction_lots_temp instead of rcv_lots_interface for the ROI.
1099 -- From the applications(forms) lot data goes into rcv_lots_interface and mtl_transaction_lots_temp.
1100 -- From the ROI(starting from 11.5.10) lot data goes only into mtl_transaction_lots_temp.
1101 -- validation_flag is used to check the source of the data into rcv_transactions_interface
1102 -- if it is 'Y' it is coming from ROI(third party) else it is coming from the Receiving forms.
1103
1104 IF l_validation_flag = 'Y' THEN
1105 OPEN cr_mtl_transaction_lots_temp ;
1106 ELSE
1107 OPEN cr_rcv_lots_interface ;
1108 END IF;
1109
1110 LOOP
1111 IF l_validation_flag = 'Y' THEN
1112 FETCH cr_mtl_transaction_lots_temp INTO l_lot_attributes_rec;
1113 IF cr_mtl_transaction_lots_temp%NOTFOUND THEN
1114 CLOSE cr_mtl_transaction_lots_temp;
1115 EXIT ;
1116 END IF;
1117 ELSE
1118 FETCH cr_rcv_lots_interface INTO l_lot_attributes_rec;
1119 IF cr_rcv_lots_interface%NOTFOUND THEN
1120 CLOSE cr_rcv_lots_interface;
1121 EXIT ;
1122 END IF;
1123
1124 END IF;
1125
1126 l_lot_exists := 1;
1127 l_tran_rec.trans_qty := l_lot_attributes_rec.primary_quantity;
1128 l_lot_receive_qty := l_lot_attributes_rec.quantity;
1129 l_tran_rec.trans_qty2 := l_lot_attributes_rec.secondary_quantity;
1130 l_tran_rec.lot_no := l_lot_attributes_rec.lot_num;
1131 l_tran_rec.sublot_no := l_lot_attributes_rec.sublot_num;
1132 l_tran_rec.reason_code := l_lot_attributes_rec.reason_code;
1133
1134 BEGIN
1135 IF l_lot_attributes_rec.sublot_num is not null then
1136 Select lot_id, qc_grade
1137 into l_tran_rec.lot_id, l_tran_rec.qc_grade
1138 from ic_lots_mst
1139 where lot_no = l_lot_attributes_rec.lot_num
1140 and sublot_no = l_lot_attributes_rec.sublot_num
1141 and item_id = l_item_id;
1142 ELSE
1143 Select lot_id, qc_grade
1144 into l_tran_rec.lot_id, l_tran_rec.qc_grade
1145 from ic_lots_mst
1146 where lot_no = l_lot_attributes_rec.lot_num
1147 and sublot_no is null
1148 and item_id = l_item_id;
1149 End if;
1150
1151 EXCEPTION
1152 When NO_DATA_FOUND then
1153 x_return_status := '-90';
1154 raise ex_exception_found;
1155 END;
1156
1157 /* Bug# 2968924 pbamb - Get the transaction quantity in
1158 Primary unit of measure if transaction uom and primary uom
1159 are not same and pass lot id to get lot specific conversion */
1160 IF l_receipt_unit_of_measure <> l_trans_um THEN
1161
1162 gmicuom.icuomcv ( l_item_id,
1163 l_tran_rec.lot_id,
1164 l_lot_receive_qty,
1165 l_receipt_opm_um,
1166 l_trans_opm_um,
1167 l_primary_quantity );
1168
1169 l_tran_rec.trans_qty := l_primary_quantity;
1170
1171 END IF;
1172
1173 /* for negative corrections */
1174 IF (l_negate_lot_qty = 1) THEN
1175 l_tran_rec.trans_qty := -l_tran_rec.trans_qty;
1176 l_tran_rec.trans_qty2 := -l_tran_rec.trans_qty2;
1177 END IF;
1178
1179 -- begin lot status: bug 3278027
1180 -- Bug 3917381 changed last AND condition from l_item_sts_ctl = 1 to l_item_sts_ctl <> 0
1181 IF ( GML_INTORD_LOT_STS.G_retain_ship_lot_sts = 'Y'
1182 AND l_req_line_id IS NOT NULL
1183 AND l_receipt_source_code = 'INTERNAL ORDER'
1184 AND l_item_sts_ctl <> 0 ) THEN
1185 BEGIN
1186 -- fetch the omso lots
1187 GML_INTORD_LOT_STS.get_omso_lot_status( p_req_line_id => l_req_line_id
1188 , p_item_id => l_item_id
1189 , x_lot_sts_tab => GML_INTORD_LOT_STS.G_lot_sts_tab
1190 , x_return_status => l_return_status
1191 , x_msg_data => l_msg_data);
1192 EXCEPTION
1193 WHEN OTHERS THEN
1194 x_return_status := '-91';
1195 raise ex_exception_found;
1196 END;
1197 END IF;
1198 -- end lot status: bug 3278027
1199
1200 -- lot status: bug 3278027, this is for indirect shipment of internal orders
1201 -- Bug 3917381 changed following IF condition from l_item_sts_ctl = 1 to l_item_sts_ctl <> 0
1202 IF l_item_sts_ctl <> 0 THEN
1203 IF ( GML_INTORD_LOT_STS.G_retain_ship_lot_sts = 'Y'
1204 AND l_receipt_source_code = 'INTERNAL ORDER' ) THEN
1205
1206 l_ship_status := NULL;
1207 l_rec_cnt := GML_INTORD_LOT_STS.G_lot_sts_tab.count;
1208 FOR i in 1 .. l_rec_cnt LOOP
1209 IF GML_INTORD_LOT_STS.G_lot_sts_tab(i).lot_id = l_tran_rec.lot_id THEN
1210 l_ship_status := GML_INTORD_LOT_STS.G_lot_sts_tab(i).lot_status;
1211 EXIT;
1212 END IF;
1213 END LOOP;
1214
1215 -- omso lot/status found
1216 IF l_ship_status IS NOT NULL THEN
1217
1218 -- derive porc lot status
1219 GML_INTORD_LOT_STS.derive_porc_lot_status( p_item_id => l_item_id
1220 , p_whse_code => l_tran_rec.whse_code
1221 , p_lot_id => l_tran_rec.lot_id
1222 , p_location => l_tran_rec.location
1223 , p_ship_lot_status => l_ship_status
1224 , x_rcpt_lot_status => l_rcpt_status
1225 , x_txn_allowed => l_txn_allowed
1226 , x_return_status => l_return_status
1227 , x_msg_data => l_msg_data );
1228
1229 -- bug 3590359
1230 -- proper error message was not getting logged in po_interface_erors in case
1231 -- inventory lot status is different than shipped lot status.
1232 -- removed the begin/exception handling portion of the code. No need.
1233
1234 IF l_return_status = 'S' THEN
1235 IF (l_txn_allowed = 'N' OR l_rcpt_status IS NULL) THEN
1236
1237 -- start bug 3590359
1238 BEGIN
1239
1240 SELECT lot_status
1241 INTO l_rcpt_status
1242 FROM ic_loct_inv
1243 WHERE item_id = l_item_id
1244 AND whse_code = l_tran_rec.whse_code
1245 AND lot_id = l_tran_rec.lot_id
1246 AND location = l_tran_rec.location ;
1247
1248 EXCEPTION WHEN OTHERS THEN
1249 l_rcpt_status := NULL ;
1250
1251 END ;
1252
1253 FND_MESSAGE.SET_NAME('GMI', 'GMI_INTORD_LOTSTS_ERROR');
1254 FND_MESSAGE.SET_TOKEN('S1',l_ship_status);
1255 FND_MESSAGE.SET_TOKEN('S2',l_rcpt_status);
1256
1257 l_msg_data := FND_MESSAGE.GET ;
1258
1259 -- end bug 3590359
1260 x_return_status := '-92';
1261 raise ex_exception_found;
1262 END IF;
1263 END IF;
1264
1265 -- in case of error , the above api puts message in l_msg_data.
1266
1267 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
1268 x_return_status := '-93';
1269 raise ex_exception_found;
1270 END IF;
1271
1272
1273 IF (l_txn_allowed = 'Y' AND l_return_status = FND_API.G_RET_STS_SUCCESS) THEN
1274 l_tran_rec.lot_status := l_rcpt_status;
1275 END IF;
1276 END IF; -- IF l_ship_status IS NOT NULL
1277 END IF; -- IF ( GML_INTORD_LOT_STS.G_retain_ship_lot_sts = 'Y'
1278
1279 /* lot status: bug 3278027, null l_ship_status indicates omso lot not found
1280 hence continue as per previous functionality */
1281 IF ( GML_INTORD_LOT_STS.G_retain_ship_lot_sts <> 'Y'
1282 OR l_receipt_source_code <> 'INTERNAL ORDER'
1283 OR l_ship_status IS NULL) THEN
1284
1285 /* Select lot status Check a record in ic_loct_inv for the item,lot,warehouse,location
1286 if no record in ic_loct_inv then get default status from ic_item_mst */
1287
1288 BEGIN
1289 select lot_status
1290 into l_tran_rec.lot_status
1291 from ic_loct_inv
1292 where item_id = l_item_id
1293 and WHSE_CODE = l_whse_code
1294 and LOT_ID = l_tran_rec.lot_id
1295 and LOCATION = l_location;
1296
1297 EXCEPTION
1298 When NO_DATA_FOUND then
1299 select lot_status
1300 into l_tran_rec.lot_status
1301 from ic_item_mst
1302 where item_id = l_item_id;
1303
1304 When OTHERS then
1305 x_return_status := '-100';
1306 raise ex_exception_found;
1307 END;
1308 END IF; -- IF ( GML_INTORD_LOT_STS.G_retain_ship_lot_sts <> 'Y'
1309 END IF; -- IF l_item_sts_ctl <> 1
1310 -- end lot status: bug 3278027
1311
1312 -- PK Bug 3991705
1313
1314 -- Keep only txn copy code here
1315
1316 IF (l_copy_return_txn = TRUE AND l_tran_rec.lot_id <> 0) THEN
1317 -- Find and copy reversal of OPM Txn
1318 OPEN Cur_opm_txn(l_parent_transaction_id , l_item_id , l_tran_rec.lot_id);
1319 FETCH Cur_opm_txn INTO l_rettxn_qty, l_rettxn_qty2;
1320 CLOSE Cur_opm_txn;
1321 l_tran_rec.trans_qty := -1 * l_rettxn_qty;
1322 l_tran_rec.trans_qty2 := -1 * l_rettxn_qty2;
1323 END IF;
1324
1325 -- End 3991705
1326
1327 /*--------------------------------------------------------------------------------------------------
1328 Parameters that need to be passed to the gmi_trans_engine_pub.create_pending_transaction routine are
1329 Parameter Value
1330 p_api_version 1.0
1331 p_init_msg_list FND_API.G_FALSE
1332 p_commit FND_API.G_FALSE
1333 p_validation_level FND_API.G_VALID_LEVEL_FULL
1334 p_tran_rec Record that needs to be processed. GMI_TRANS_ENGINE_PUB.ictran_rec
1335 x_tran_row Processed record with trans id created in the procedure.
1336 x_return_status Success of the procedure.
1337 x_msg_count Error message.
1338 */--------------------------------------------------------------------------------------------------
1339 -- PK Begin Bug 3936459
1340 BEGIN
1341 l_wdd := 0;
1342 -- l_trans_um = l_trans_um2 AND
1343 IF ( l_comments IS NOT NULL AND ( substr(l_comments, 1, 8) = 'OPM WDD:')) THEN
1344
1345 l_wdd := to_number(substr(l_comments, 9));
1346 END IF;
1347 EXCEPTION
1348
1349 WHEN OTHERS THEN
1350 l_wdd := 0;
1351 END;
1352 l_temp_qty := 0;
1353 IF (l_wdd <> 0) THEN
1354 l_temp_qty := GET_SHIPPED_QTY(l_wdd, l_tran_rec.trans_qty2);
1355 IF (l_temp_qty <> 0) THEN
1356 l_tran_rec.trans_qty := l_temp_qty;
1357 END IF;
1358 END IF;
1359 -- PK End Bug 3936459.
1360 gmi_trans_engine_pub.create_completed_transaction(
1361 p_api_version => 1.0,
1362 p_init_msg_list => FND_API.G_FALSE,
1363 p_commit => FND_API.G_FALSE,
1364 p_validation_level => FND_API.G_VALID_LEVEL_FULL,
1365 p_tran_rec => l_tran_rec,
1366 x_tran_row => l_tran_row,
1367 x_return_status => l_return_status,
1368 x_msg_count => l_msg_count,
1369 x_msg_data => l_msg_data,
1370 p_table_name => 'IC_TRAN_PND');
1371
1372 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
1373 x_return_status := '-110';
1374 raise ex_exception_found;
1375 END IF;
1376 END LOOP;
1377
1378 /*If there were no lots for this transactions then do the following insert */
1379 IF l_lot_exists = 0 THEN
1380 gmi_trans_engine_pub.create_completed_transaction(
1381 p_api_version => 1.0,
1382 p_init_msg_list => FND_API.G_FALSE,
1383 p_commit => FND_API.G_FALSE,
1384 p_validation_level => FND_API.G_VALID_LEVEL_FULL,
1385 p_tran_rec => l_tran_rec,
1386 x_tran_row => l_tran_row,
1387 x_return_status => l_return_status,
1388 x_msg_count => l_msg_count,
1389 x_msg_data => l_msg_data,
1390 p_table_name => 'IC_TRAN_PND');
1391
1392
1393
1394
1395 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
1396 x_return_status := '-120';
1397 raise ex_exception_found;
1398 END IF;
1399
1400 END IF;
1401
1402 /* Bug 3172267 Do not call DropShipReceive if a correction/return is made
1403 against a drop ship receipt. This is in line with discrete. */
1404 IF ((l_trx_type = 'DELIVER' and l_destination_type_code = 'INVENTORY') OR
1405 (l_trx_type = 'RECEIVE' and l_auto_transact_code = 'DELIVER' and l_destination_type_code = 'INVENTORY'))
1406 THEN
1407 if (OE_INSTALL.Get_Active_Product = 'ONT' ) and nvl(l_non_inv,0) <> 1 then
1408 /* New OE => ONT is installed. */
1409 if (not OE_DS_PVT.DropShipReceive(P_Line_Id,'INV')) then
1410 x_return_status := -95;
1411 raise ex_exception_found;
1412 end if;
1413 end if;
1414 END IF;
1415
1416 /* Everything went through smoothly, set the return status to success */
1417 x_return_status := '1';
1418
1419 EXCEPTION
1420 WHEN ex_exception_found THEN
1421
1422 -- ROI enhancements bug# 3061052
1423 IF cr_mtl_transaction_lots_temp%ISOPEN THEN
1424 CLOSE cr_mtl_transaction_lots_temp;
1425 END IF;
1426
1427 IF cr_rcv_lots_interface%ISOPEN THEN
1428 CLOSE cr_rcv_lots_interface;
1429 END IF;
1430
1431 IF x_return_status = '-5' THEN
1432 l_error_message := x_return_Status||' No data found in RCV_TRANSACTIONS_INTERFACE';
1433
1434 ELSIF x_return_status = '-10' THEN
1435 l_error_message := x_return_Status||' No data found in IC_WHSE_MST for organization with id '||to_char(l_organization_id);
1436
1437 ELSIF x_return_status = '-20' THEN
1438 l_error_message := x_return_Status||' No data found in IC_ITEM_MST for item with id '||to_char(l_ora_item_id);
1439
1440 ELSIF x_return_status = '-30' THEN
1441 l_error_message := x_return_Status||' When others in get_opm_uom_code';
1442
1443 ELSIF x_return_status = '-40' THEN
1444 l_error_message := x_return_Status||' No data found in IC_LOCT_MST for locator with id '||to_char(l_locator_id);
1445
1446 ELSIF x_return_status = '-50' THEN
1447 l_error_message := x_return_Status||' No data found in MTL_ITEM_LOCATIONS for locator with id '||to_char(l_locator_id);
1448 -- Bug 3597203 following condition added.
1449 ELSIF x_return_status = '-55' THEN
1450 FND_MESSAGE.SET_NAME('PO','RCV_LOCATOR_CONTROL_INVALID');
1451 l_error_message := x_return_status||' : '||FND_MESSAGE.GET;
1452 ELSIF x_return_status = '-60' THEN
1453 l_error_message := x_return_Status||' No data found in CURSOR CR_INTORG_TRAN for direct shipment in Internal Orders';
1454 -- bug 3590359
1455 ELSIF x_return_status = '-61' THEN
1456 l_error_message := x_return_Status||' '|| l_msg_data;
1457 ELSIF x_return_status = '-62' THEN
1458 l_error_message := x_return_Status||' derive_ship_lot_status failed with unexpected error '||l_msg_data;
1459 ELSIF x_return_status = '-70' THEN
1460
1461 x_return_status := sqlerrm;
1462 l_error_message := '-70'||x_return_Status||' When others while getting lot status';
1463 ELSIF x_return_status = '-80' THEN
1464
1465 IF (l_msg_count > 0) THEN
1466 l_loop_cnt :=1;
1467 LOOP
1468
1469 FND_MSG_PUB.Get (
1470 p_msg_index => l_loop_cnt,
1471 p_data => l_msg_data,
1472 p_encoded => FND_API.G_FALSE,
1473 p_msg_index_out => l_dummy_cnt);
1474
1475 --gml_po_log('l_msg_data :' ||l_msg_data);
1476
1477 l_loop_cnt := l_loop_cnt + 1;
1478 IF (l_loop_cnt > l_msg_count) THEN
1479 EXIT;
1480 END IF;
1481
1482 END LOOP;
1483 END IF;
1484 l_error_message := x_return_Status||'-'||l_msg_data;
1485
1486 ELSIF x_return_status = '-90' THEN
1487 l_error_message := x_return_Status||' Lot not found in po_gml_db_common.create_inv_transaction';
1488
1489 ELSIF x_return_status = '-91' THEN
1490 l_error_message := '-91'||x_return_Status||' When others in fetching omso lots, get_omso_lot_status';
1491
1492 -- bug# 3590359
1493 -- removed text error in derive_porc_lot_status
1494 ELSIF x_return_status = '-92' THEN
1495 l_error_message := x_return_Status||' '||l_msg_data;
1496
1497 ELSIF x_return_status = '-93' THEN
1498 l_error_message := x_return_Status||' '||l_msg_data||' unexpected error in derive_porc_lot_status';
1499
1500 ELSIF x_return_status = '-94' THEN
1501 l_error_message := x_return_Status||' '||l_msg_data||' when others in derive_porc_lot_status';
1502
1503 ELSIF x_return_status = '-100' THEN
1504 x_return_status := sqlerrm;
1505 l_error_message := '-100'||x_return_Status||' When others while getting lot status';
1506
1507 ELSIF x_return_status = '-110' THEN
1508 IF (l_msg_count > 0) THEN
1509 l_loop_cnt :=1;
1510 LOOP
1511
1512 FND_MSG_PUB.Get (
1513 p_msg_index => l_loop_cnt,
1514 p_data => l_msg_data,
1515 p_encoded => FND_API.G_FALSE,
1516 p_msg_index_out => l_dummy_cnt);
1517
1518 --gml_po_log('l_msg_data :' ||l_msg_data);
1519
1520 l_loop_cnt := l_loop_cnt + 1;
1521 IF (l_loop_cnt > l_msg_count) THEN
1522 EXIT;
1523 END IF;
1524
1525 END LOOP;
1526 END IF;
1527 l_error_message := x_return_Status||'-'||l_msg_data;
1528
1529 ELSIF x_return_status = '-120' THEN
1530 IF (l_msg_count > 0) THEN
1531 l_loop_cnt :=1;
1532 LOOP
1533
1534 FND_MSG_PUB.Get (
1535 p_msg_index => l_loop_cnt,
1536 p_data => l_msg_data,
1537 p_encoded => FND_API.G_FALSE,
1538 p_msg_index_out => l_dummy_cnt);
1539
1540 --gml_po_log('l_msg_data :' ||l_msg_data);
1541
1542 l_loop_cnt := l_loop_cnt + 1;
1543 IF (l_loop_cnt > l_msg_count) THEN
1544 EXIT;
1545 END IF;
1546
1547 END LOOP;
1548 END IF;
1549 l_error_message := x_return_Status||'-'||l_msg_data;
1550
1551 ELSIF x_return_status = '-130' THEN
1552 l_error_message := x_return_Status||' error in call oe_ds_pvt.DropShipReceive';
1553
1554 END IF;
1555
1556 INSERT_PO_ERRORS( 'RECEIVING',
1557 p_interface_trx_id,
1558 l_error_message,
1559 sysdate,
1560 l_creation_date,
1561 l_created_by,
1562 l_last_update_date,
1563 l_last_updated_by,
1564 l_last_update_login,
1565 l_request_id,
1566 l_program_application_id,
1567 l_program_id,
1568 sysdate,
1569 'IC_TRAN_PND' );
1570
1571 WHEN OTHERS THEN
1572 x_return_status := SQLERRM;
1573 l_error_message := '-140 '||x_return_Status||' when others in PO_GML_DB_COMMON.CREATE_INV_TRANS_OPM';
1574
1575 -- ROI enhancements bug# 3061052
1576 IF cr_mtl_transaction_lots_temp%ISOPEN THEN
1577 CLOSE cr_mtl_transaction_lots_temp;
1578 END IF;
1579
1580 IF cr_rcv_lots_interface%ISOPEN THEN
1581 CLOSE cr_rcv_lots_interface;
1582 END IF;
1583
1584 INSERT_PO_ERRORS( 'RECEIVING',
1585 p_interface_trx_id,
1586 l_error_message,
1587 sysdate,
1588 l_creation_date,
1589 l_created_by,
1590 l_last_update_date,
1591 l_last_updated_by,
1592 l_last_update_login,
1593 l_request_id,
1594 l_program_application_id,
1595 l_program_id,
1596 sysdate,
1597 'IC_TRAN_PND' );
1598
1599 END CREATE_INV_TRANS_OPM;
1600
1601
1602 /*##########################################################################
1603 #
1604 # FUNCTION
1605 # GET_CORR_QTY
1606 #
1607 # DESCRIPTION
1608 # This function returns correction quantity for a given transaction
1609 #
1610 #
1611 # MODIFICATION HISTORY
1612 # 06-APR-2001 pbamb Created
1613 #
1614 ## #######################################################################*/
1615
1616 PROCEDURE GET_CORR_QTY( X_tran_type IN VARCHAR2,
1617 X_tran_id IN NUMBER,
1618 X_final_qty IN OUT NOCOPY NUMBER)
1619 IS
1620
1621 Cursor get_corr_qty( v_tran_id NUMBER) is
1622 Select transaction_type,
1623 secondary_quantity
1624 from rcv_transactions
1625 where parent_transaction_id = v_tran_id
1626 and transaction_type in ('CORRECT','RETURN TO RECEIVING') ;
1627
1628 BEGIN
1629
1630 For v_rec in get_corr_qty(X_tran_id)
1631 Loop
1632 If v_rec.transaction_type = 'CORRECT'
1633 then
1634 X_final_qty := nvl(X_final_qty,0) - nvl(v_rec.secondary_quantity,0);
1635
1636 end if;
1637
1638 If X_tran_type = 'DELIVER' and v_rec.transaction_type = 'RETURN TO RECEIVING'
1639 Then
1640 X_final_qty := nvl(X_final_qty,0) + nvl(v_rec.secondary_quantity,0);
1641 End If;
1642
1643 End Loop;
1644 END GET_CORR_QTY;
1645
1646
1647 /*##########################################################################
1648 #
1649 # FUNCTION
1650 # GET_SECONDARY_TRAN_QTY
1651 #
1652 # DESCRIPTION
1653 # This function returns secondary transaction quantity for a given transaction
1654 # required by the Receiving transactions screen. This procedure is being called by the
1655 # package RCV_QUANTITIES_S.get_transaction_quantity in RCVTXQUB.pls file.
1656 #
1657 # MODIFICATION HISTORY
1658 # 06-APR-2001 pbamb Created
1659 #
1660 ## #######################################################################*/
1661 PROCEDURE GET_SECONDARY_TRAN_QTY ( p_transaction_id IN NUMBER,
1662 p_secondary_available_qty IN OUT NOCOPY NUMBER) IS
1663
1664 Cursor get_qty(v_transaction_id IN NUMBER) is
1665 select transaction_type,
1666 secondary_quantity
1667 from rcv_transactions
1668 where transaction_id = v_transaction_id;
1669
1670 Cursor get_child_qty(v_p_transaction_id IN NUMBER) is
1671 select transaction_id,
1672 transaction_type,
1673 secondary_quantity
1674 from rcv_transactions
1675 where parent_transaction_id = v_p_transaction_id;
1676
1677 v_transaction_type VARCHAR2(100);
1678 v_final_qty NUMBER DEFAULT 0;
1679
1680 BEGIN
1681
1682 Open get_qty(p_transaction_id);
1683 Fetch get_qty into v_transaction_type,
1684 v_final_qty;
1685 Close get_qty;
1686
1687 If v_transaction_type = 'RECEIVE'
1688 then
1689 For t_rec in get_child_qty(p_transaction_id)
1690 LOOP
1691 If t_rec.transaction_type in ('CORRECT' )
1692 then
1693 v_final_qty := nvl(v_final_qty,0) + nvl(t_rec.secondary_quantity,0);
1694
1695 ElsIf t_rec.transaction_type in ( 'REJECT' ,'ACCEPT' )
1696 then
1697 v_final_qty := nvl(v_final_qty,0) - nvl(t_rec.secondary_quantity,0);
1698 get_corr_qty(t_rec.transaction_type,t_rec.transaction_id ,v_final_qty);
1699
1700 ElsIf t_rec.transaction_type in ( 'RETURN TO VENDOR' ,'RETURN TO RECEIVING','DELIVER')
1701 then
1702 v_final_qty := nvl(v_final_qty,0) - nvl(t_rec.secondary_quantity,0);
1703 get_corr_qty(t_rec.transaction_type,t_rec.transaction_id ,v_final_qty);
1704 End if;
1705
1706 End LOOP;
1707
1708 Elsif v_transaction_type in ( 'REJECT' ,'ACCEPT')
1709 then
1710 For c_rec in get_child_qty(p_transaction_id)
1711 LOOP
1712 If c_rec.transaction_type = 'CORRECT'
1713 then
1714 v_final_qty := nvl(v_final_qty,0) + nvl(c_rec.secondary_quantity,0);
1715
1716 Elsif c_rec.transaction_type in ('ACCEPT' ,'REJECT')
1717 then
1718 v_final_qty := nvl(v_final_qty,0) - nvl(c_rec.secondary_quantity,0);
1719 get_corr_qty(c_rec.transaction_type,c_rec.transaction_id ,v_final_qty);
1720 End if;
1721
1722 End LOOP;
1723
1724 Elsif v_transaction_type in ('DELIVER')
1725 then
1726 For c_rec in get_child_qty(p_transaction_id)
1727 LOOP
1728 If c_rec.transaction_type = 'CORRECT'
1729 then
1730 v_final_qty := nvl(v_final_qty,0) + nvl(c_rec.secondary_quantity,0);
1731 End if;
1732 End LOOP;
1733 Elsif v_transaction_type in ('MATCH')
1734 then
1735 For c_rec in get_child_qty(p_transaction_id)
1736 LOOP
1737 If c_rec.transaction_type in ( 'RETURN TO VENDOR' ,'RETURN TO RECEIVING','DELIVER')
1738 then
1739 v_final_qty := nvl(v_final_qty,0) - nvl(c_rec.secondary_quantity,0);
1740 get_corr_qty(c_rec.transaction_type,c_rec.transaction_id ,v_final_qty);
1741
1742 End if;
1743 End LOOP;
1744 End if;
1745
1746 p_secondary_available_qty := v_final_qty;
1747 END GET_SECONDARY_TRAN_QTY;
1748
1749 /*##########################################################################
1750 #
1751 # PROCEDURE
1752 # VALIDATE_QUANTITY
1753 #
1754 # DESCRIPTION
1755 # This procedure validates the primary and secondary qty.are within deviation
1756 # and if not it will return correct secondary qty.If secondary qty is null,
1757 # it will calculate the corr. secondary qty for given primary qty.
1758 #
1759 #
1760 # MODIFICATION HISTORY
1761 # 07-JUN-2001 mchandak Created
1762 #
1763 ## #######################################################################*/
1764
1765
1766 PROCEDURE VALIDATE_QUANTITY(
1767 x_opm_item_id IN NUMBER,
1768 x_opm_dual_uom_type IN NUMBER,
1769 x_quantity IN NUMBER,
1770 x_opm_um_code IN VARCHAR2,
1771 x_opm_secondary_uom IN VARCHAR2,
1772 x_secondary_quantity IN OUT NOCOPY NUMBER) IS
1773
1774 v_ret_val pls_integer;
1775 v_secondary_quantity_temp number := x_secondary_quantity;
1776 BEGIN
1777 if x_opm_dual_uom_type = 0 or x_quantity is null then
1778 return;
1779 end if;
1780
1781 if x_secondary_quantity is not null and x_quantity is not null then
1782 v_ret_val := gmicval.dev_validation ( x_opm_item_id,
1783 0,
1784 x_quantity,
1785 x_opm_um_code,
1786 x_secondary_quantity,
1787 x_opm_secondary_uom,
1788 0 );
1789 if v_ret_val = -68 or v_ret_val = -69 then
1790 null; -- deviation error. need to recalculate secondary quantity.
1791 elsif v_ret_val < 0 then
1792 return; -- unknown error..
1793 else -- everything is fine
1794 if x_opm_dual_uom_type = 1 then
1795 null; -- for uom_type = 1,the procedure doesn't check for deviation
1796 else
1797 return;
1798 end if;
1799 end if;
1800
1801 end if;
1802
1803 gmicuom.icuomcv ( x_opm_item_id,
1804 0,
1805 x_quantity,
1806 x_opm_um_code,
1807 x_opm_secondary_uom,
1808 v_secondary_quantity_temp );
1809
1810 X_secondary_quantity := round(v_secondary_quantity_temp,5);
1811
1812 EXCEPTION
1813 WHEN OTHERS THEN
1814 return;
1815 END VALIDATE_QUANTITY;
1816
1817 /*##########################################################################
1818 #
1819 # PROCEDURE
1820 # CREATE_LOT_SPECIFIC_CONVERSION
1821 #
1822 # DESCRIPTION
1823 # This procedure will create lot specific conversion for a new lot if user
1824 # selects a yes to the question asked in RCVGMLCR.pld in when-validate-record
1825 # of Lot Entry block.
1826 # MODIFICATION HISTORY
1827 # 20-JUN-2002 pbamb Created
1828 #
1829 ## #######################################################################*/
1830
1831
1832 PROCEDURE CREATE_LOT_SPECIFIC_CONVERSION(
1833 x_item_number IN VARCHAR2,
1834 x_lot_number IN VARCHAR2,
1835 x_sublot_number IN VARCHAR2,
1836 x_from_uom IN VARCHAR2,
1837 x_to_uom IN VARCHAR2,
1838 x_type_factor IN NUMBER,
1839 x_status IN OUT NOCOPY VARCHAR2,
1840 x_data IN OUT NOCOPY VARCHAR2) IS
1841
1842 l_trans_rec Gmigapi.conv_rec_typ;
1843 l_ic_item_cnv_row IC_ITEM_CNV%ROWTYPE;
1844 l_status VARCHAR2(1);
1845 l_return_status VARCHAR2(1) :=FND_API.G_RET_STS_SUCCESS;
1846 l_count NUMBER;
1847 l_count_msg NUMBER;
1848 l_data VARCHAR2(2000);
1849 l_dummy_cnt NUMBER :=0;
1850 l_record_count NUMBER :=0;
1851 e_lot_conv_failed EXCEPTION;
1852
1853 BEGIN
1854
1855 l_trans_rec.item_no := x_item_number;
1856 l_trans_rec.lot_no := x_lot_number ;
1857 l_trans_rec.sublot_no := x_sublot_number;
1858 l_trans_rec.from_uom := x_from_uom ;
1859 l_trans_rec.to_uom := x_to_uom ;
1860 l_trans_rec.type_factor := x_type_factor;
1861 l_trans_rec.user_name := FND_GLOBAL.USER_NAME;
1862
1863 -- Set the context for the GMI APIs
1864 IF( NOT Gmigutl.Setup(l_trans_rec.user_name) )
1865 THEN
1866 RAISE e_lot_conv_failed;
1867 END IF;
1868
1869 -- Call the standard API and check the return status
1870 Gmipapi.Create_Item_Lot_Conv
1871 ( p_api_version => 3.0
1872 , p_init_msg_list => FND_API.G_TRUE
1873 , p_commit => FND_API.G_FALSE
1874 , p_validation_level => FND_API.G_valid_level_full
1875 , p_conv_rec => l_trans_rec
1876 , x_ic_item_cnv_row => l_ic_item_cnv_row
1877 , x_return_status => l_status
1878 , x_msg_count => l_count
1879 , x_msg_data => l_data
1880 );
1881
1882 x_status := l_status;
1883
1884 FOR l_loop_cnt IN 1..l_count
1885 LOOP
1886 FND_MSG_PUB.Get(
1887 p_msg_index => l_loop_cnt,
1888 p_data => l_data,
1889 p_encoded => FND_API.G_FALSE,
1890 p_msg_index_out => l_dummy_cnt);
1891
1892 x_data := x_data||l_data;
1893
1894 END LOOP;
1895
1896 EXCEPTION
1897 WHEN e_lot_conv_failed THEN
1898 -- API Failed. Error message must be on stack.
1899 l_count_msg := fnd_msg_pub.Count_Msg;
1900
1901 FOR l_loop_cnt IN 1..l_count_msg
1902 LOOP
1903 FND_MSG_PUB.GET(P_msg_index => l_loop_cnt,
1904 P_data => l_data,
1905 P_encoded => FND_API.G_FALSE,
1906 P_msg_index_out => l_dummy_cnt);
1907
1908 x_data := x_data||l_data;
1909
1910 END LOOP;
1911 X_status := 'U';
1912
1913
1914 WHEN OTHERS THEN
1915 RAISE;
1916
1917 END CREATE_LOT_SPECIFIC_CONVERSION;
1918
1919 END PO_GML_DB_COMMON;