[Home] [Help]
PACKAGE BODY: APPS.INV_RCV_STD_INSPECT_APIS
Source
1 PACKAGE BODY inv_rcv_std_inspect_apis AS
2 /* $Header: INVSTDIB.pls 120.20.12020000.4 2013/01/30 06:46:33 jianpyu ship $ */
3
4 /*
5 ** -------------------------------------------------------------------------
6 ** Function: main_process
7 ** Description:
8 ** Output:
9 ** x_return_status
10 ** return status indicating success, error, unexpected error
11 ** x_msg_count
12 ** number of messages in message list
13 ** x_msg_data
14 ** if the number of messages in message list is 1, contains
15 ** message text
16 ** Input:
17 **
18 ** Returns:
19 ** --------------------------------------------------------------------------
20 */
21 g_pkg_name CONSTANT VARCHAR2(30) := 'INV_RCV_STD_INSPECT_APIS';
22
23 g_to_be_inspected CONSTANT NUMBER := 1;
24 g_accept CONSTANT NUMBER := 2;
25 g_reject CONSTANT NUMBER := 3;
26
27 g_inspection_routing CONSTANT NUMBER := 2;
28
29 -- From mfg_lookups,
30 -- lookup_type = WMS_LPN_CONTEXT
31 -- lookup_code = 3
32 -- meaning = Resides in Receiving
33 g_resides_in_receiving CONSTANT NUMBER := 3;
34
35 g_interface_transaction_id NUMBER;
36
37 -- Added for 12942776
38
39 type t_serial_rec_tbl is table of varchar2(30) index by binary_integer;
40
41 l_serial_tbl t_serial_rec_tbl;
42
43
44 TYPE t_rt_serial_rec IS RECORD (
45 rcv_transaction_id NUMBER,
46 serial_number VARCHAR2 (100)
47 );
48
49 TYPE t_rt_serial_rec_tbl IS TABLE OF t_rt_serial_rec
50 INDEX BY BINARY_INTEGER;
51
52
53 -- 12942776
54
55 PROCEDURE print_debug(p_err_msg VARCHAR2,
56 p_level NUMBER)
57 IS
58 l_debug number := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
59 BEGIN
60
61 IF (l_debug = 1) THEN
62 inv_mobile_helper_functions.tracelog
63 (p_err_msg => p_err_msg,
64 p_module => g_pkg_name||'($Revision: 120.20.12020000.4 $)',
65 p_level => p_level);
66 END IF;
67
68 -- dbms_output.put_line(p_err_msg);
69 END print_debug;
70
71 -- 12942776 Added function
72 FUNCTION list_serials
73 RETURN serset_t PIPELINED
74 IS
75 l_api_name VARCHAR2 (30) := 'list_serials';
76 l_debug NUMBER := NVL (fnd_profile.VALUE ('INV_DEBUG_TRACE'), 0);
77 l_count NUMBER;
78 BEGIN
79 IF (l_debug = 1)
80 THEN
81 print_debug ('Enter:' || l_api_name, 4);
82 END IF;
83
84 l_count := l_serial_tbl.COUNT;
85
86 FOR i IN 1 .. l_count
87 LOOP
88 PIPE ROW (l_serial_tbl (i));
89 END LOOP;
90
91 IF (l_debug = 1)
92 THEN
93 print_debug ('Successfull.. Returning from :' || l_api_name, 4);
94 END IF;
95
96 RETURN;
97 EXCEPTION
98 WHEN OTHERS
99 THEN
100 IF (l_debug = 1)
101 THEN
102 print_debug ('Unknown Exception occurred: ' || SQLERRM, 4);
103 END IF;
104
105 RETURN;
106 END;
107 -- 12942776
108
109 /* FP-J Lot/Serial Support Enhancement
110 * Helper routine to create interface records for the inspected lot number
111 * (in MTL_TRANSACTION_LOT_NUMBERS) and/or the inspected serial number (in
112 * MTL_SERIAL_NUMBERS_INTERFACE) if the item is lot and/or serial controlled.
113 * The interface records created here would be used by the receiving TM to
114 * update the receiving onhand for the lots and serials (RCV_LOTS_SUPPLY and
115 * RCV_SERIALS_SUPPLY)
116 */
117 PROCEDURE process_lot_serial_intf(
118 x_return_status OUT NOCOPY VARCHAR2
119 , x_msg_count OUT NOCOPY NUMBER
120 , x_msg_data OUT NOCOPY VARCHAR2
121 , p_organization_id IN NUMBER
122 , p_inventory_item_id IN NUMBER
123 , p_lot_control_code IN NUMBER
124 , p_serial_control_code IN NUMBER
125 , p_lot_number IN VARCHAR2
126 , p_txn_qty IN NUMBER
127 , p_primary_qty IN NUMBER
128 , p_serial_number IN VARCHAR2
129 , p_product_transaction_id IN NUMBER
130 , p_lpn_id IN NUMBER
131 , p_sec_txn_qty IN NUMBER --OPM Convergence
132 ) IS
133
134 l_txn_if_id NUMBER;
135 l_serial_temp_id NUMBER;
136 l_lot_status_id NUMBER;
137 l_serial_status_id NUMBER;
138 l_lot_expiration_date DATE;
139 l_prod_code VARCHAR2(5) := inv_rcv_integration_apis.G_PROD_CODE;
140 l_product_txn_id NUMBER;
141 l_yes VARCHAR2(1) := inv_rcv_integration_apis.G_YES;
142 l_no VARCHAR2(1) := inv_rcv_integration_apis.G_NO;
143 l_false VARCHAR2(1) := inv_rcv_integration_apis.G_FALSE;
144 l_debug NUMBER := NVL(fnd_profile.VALUE('INV_DEBUG_TRACE'), 0);
145 l_is_rma NUMBER;
146
147 l_origination_type number;--OPM Convergence
148 l_expiration_action_code VARCHAR2(32);--OPM Convergence
149 l_expiration_action_date DATE;--OPM Convergence
150 l_hold_date DATE;--OPM Convergence
151 l_reason_id number;--OPM Convergence
152 BEGIN
153
154 --Initialize the return status
155 x_return_status := fnd_api.g_ret_sts_success;
156
157 l_product_txn_id := p_product_transaction_id;
158
159 --First create the MTLI record for the lot that was inspected
160 IF (p_lot_control_code > 1 AND p_lot_number IS NOT NULL) THEN
161 SELECT expiration_date
162 , status_id
163 , origination_type --OPM Convergence
164 , expiration_action_code --OPM Convergence
165 , expiration_action_date --OPM Convergence
166 , hold_date --OPM Convergence
167 INTO l_lot_expiration_date
168 , l_lot_status_id
169 , l_origination_type --OPM Convergence
170 , l_expiration_action_code --OPM Convergence
171 , l_expiration_action_date --OPM Convergence
172 , l_hold_date --OPM Convergence
173 FROM mtl_lot_numbers
174 WHERE lot_number = p_lot_number
175 AND inventory_item_id = p_inventory_item_id
176 AND organization_id = p_organization_id;
177
178 --Call the insert_mtli API
179 inv_rcv_integration_apis.insert_mtli(
180 p_api_version => 1.0
181 , p_init_msg_lst => l_false
182 , x_return_status => x_return_status
183 , x_msg_count => x_msg_count
184 , x_msg_data => x_msg_data
185 , p_transaction_interface_id => l_txn_if_id
186 , p_lot_number => p_lot_number
187 , p_transaction_quantity => p_txn_qty
188 , p_primary_quantity => p_primary_qty
189 , p_organization_id => p_organization_id
190 , p_inventory_item_id => p_inventory_item_id
191 , p_expiration_date => l_lot_expiration_date
192 , p_status_id => l_lot_status_id
193 , x_serial_transaction_temp_id => l_serial_temp_id
194 , p_product_transaction_id => l_product_txn_id
195 , p_product_code => l_prod_code
196 , p_att_exist => l_yes
197 , p_update_mln => l_no
198 , p_origination_type => l_origination_type--OPM Convergence
199 , p_expiration_action_code => l_expiration_action_code--OPM Convergence
200 , p_expiration_action_date => l_expiration_action_date--OPM Convergence
201 , p_hold_date => l_hold_date);--OPM Convergence
202
203
204
205 IF x_return_status = fnd_api.g_ret_sts_error THEN
206 RAISE fnd_api.g_exc_error;
207 END IF;
208
209 IF x_return_status = fnd_api.g_ret_sts_unexp_error THEN
210 RAISE fnd_api.g_exc_unexpected_error;
211 END IF;
212
213 IF (l_debug = 1) THEN
214 print_debug('process_lot_serial_intf 1.1: Created MTLI record for lot: txn if: ' || l_txn_if_id || ', serial_temp_id: ' ||
215 l_serial_temp_id || ', prod_txn_id: ' || l_product_txn_id , 4);
216 END IF;
217
218 --Bug #3405320
219 --For items that are serial controlled at SO Issue, need to NULL out
220 --serial_transaction_temp_id in the MTLI just generated (for split_lot_serial).
221 --However, if there is a serial to be inspected, in which case the serial
222 --number is passed, do not NULL out the serial_transaction_temp_id
223 IF (p_serial_control_code = 6 AND p_serial_number IS NULL) THEN
224 IF (l_debug = 1) THEN
225 print_debug('process_lot_serial_intf 1.2: serial_control_code IS 6, need TO NULL OUT mtli', 4);
226 END IF;
227
228 UPDATE mtl_transaction_lots_interface
229 SET serial_transaction_temp_id = NULL
230 WHERE product_transaction_id = l_product_txn_id
231 AND product_code = 'RCV';
232 END IF; -- IF (l_is_rma = 1)
233 END IF; --END IF for a lot controlled item
234
235 IF (p_serial_control_code > 1 AND p_serial_number IS NOT NULL) THEN
236 --Get the serial status
237 SELECT status_id
238 INTO l_serial_status_id
239 FROM mtl_serial_numbers
240 WHERE serial_number = p_serial_number
241 AND inventory_item_id = p_inventory_item_id;
242
243 --If the item is also lot controlled then set use the serial_transaction_temp_id
244 --of the MTLI record to create the MSNI record
245 IF (p_lot_control_code > 1 AND p_lot_number IS NOT NULL) THEN
246 l_txn_if_id := l_serial_temp_id;
247 END IF;
248
249 --Call the insert_msni API
250 inv_rcv_integration_apis.insert_msni(
251 p_api_version => 1.0
252 , p_init_msg_lst => l_false
253 , x_return_status => x_return_status
254 , x_msg_count => x_msg_count
255 , x_msg_data => x_msg_data
256 , p_transaction_interface_id => l_txn_if_id
257 , p_fm_serial_number => p_serial_number
258 , p_to_serial_number => p_serial_number
259 , p_organization_id => p_organization_id
260 , p_inventory_item_id => p_inventory_item_id
261 , p_status_id => l_serial_status_id
262 , p_product_transaction_id => l_product_txn_id
263 , p_product_code => l_prod_code
264 , p_att_exist => l_yes
265 , p_update_msn => l_no);
266
267 IF x_return_status = fnd_api.g_ret_sts_error THEN
268 RAISE fnd_api.g_exc_error;
269 END IF;
270
271 IF x_return_status = fnd_api.g_ret_sts_unexp_error THEN
272 RAISE fnd_api.g_exc_unexpected_error;
273 END IF;
274
275 IF (l_debug = 1) THEN
276 print_debug('process_lot_serial_intf 1.2: Created MSNI record for serial: ' || p_serial_number || ' with txn_if_id: '
277 || l_txn_if_id || ', prod_txn_id: ' || l_product_txn_id , 4);
278 END IF;
279 END IF; --END IF for a serial controlled item
280
281 EXCEPTION
282 WHEN fnd_api.g_exc_error THEN
283 x_return_status := fnd_api.g_ret_sts_error;
284 -- Get message count and data
285 fnd_msg_pub.count_and_get(p_count => x_msg_count, p_data => x_msg_data);
286 WHEN fnd_api.g_exc_unexpected_error THEN
287 x_return_status := fnd_api.g_ret_sts_unexp_error;
288 fnd_msg_pub.count_and_get(p_count => x_msg_count, p_data => x_msg_data);
289 WHEN OTHERS THEN
290 x_return_status := fnd_api.g_ret_sts_unexp_error;
291 IF fnd_msg_pub.check_msg_level(fnd_msg_pub.g_msg_lvl_unexp_error) THEN
292 fnd_msg_pub.add_exc_msg(g_pkg_name, 'process_lot_serial_intf');
293 END IF;
294 fnd_msg_pub.count_and_get(p_count => x_msg_count, p_data => x_msg_data);
295 END process_lot_serial_intf;
296
297 -- 12942776 new procedure to split mtrl if required and to inset rti/msni/mtli
298
299 PROCEDURE split_mol_create_rti (
300 p_rcv_transaction_id IN NUMBER,
301 p_rtv_qty IN NUMBER,
302 p_rtv_sec_qty IN NUMBER,
303 p_mol_qty IN NUMBER,
304 p_primary_qty IN NUMBER,
305 p_mol_line_id IN NUMBER,
306 p_inventory_item_id IN NUMBER,
307 p_uom IN VARCHAR2,
308 p_uom_code IN VARCHAR2,
309 p_inspection_code IN VARCHAR2,
310 p_quality_code IN VARCHAR2,
311 p_transaction_date IN DATE,
312 p_transaction_type IN VARCHAR2,
313 p_vendor_lot IN VARCHAR2,
314 p_reason_id IN NUMBER,
315 p_organization_id IN NUMBER,
316 p_lot_number IN VARCHAR2,
317 p_comments IN VARCHAR2,
318 p_attribute_category IN VARCHAR2 DEFAULT NULL,
319 p_attribute1 IN VARCHAR2 DEFAULT NULL,
320 p_attribute2 IN VARCHAR2 DEFAULT NULL,
321 p_attribute3 IN VARCHAR2 DEFAULT NULL,
322 p_attribute4 IN VARCHAR2 DEFAULT NULL,
323 p_attribute5 IN VARCHAR2 DEFAULT NULL,
324 p_attribute6 IN VARCHAR2 DEFAULT NULL,
325 p_attribute7 IN VARCHAR2 DEFAULT NULL,
326 p_attribute8 IN VARCHAR2 DEFAULT NULL,
327 p_attribute9 IN VARCHAR2 DEFAULT NULL,
328 p_attribute10 IN VARCHAR2 DEFAULT NULL,
329 p_attribute11 IN VARCHAR2 DEFAULT NULL,
330 p_attribute12 IN VARCHAR2 DEFAULT NULL,
331 p_attribute13 IN VARCHAR2 DEFAULT NULL,
332 p_attribute14 IN VARCHAR2 DEFAULT NULL,
333 p_attribute15 IN VARCHAR2 DEFAULT NULL,
334 p_qa_collection_id IN NUMBER,
335 p_lpn_id IN NUMBER,
336 p_transfer_lpn_id IN NUMBER,
337 p_sec_uom VARCHAR2
338 )
339 IS
340 l_uom VARCHAR2 (30) := p_uom;
341 l_inspection_code VARCHAR2 (25) := p_inspection_code;
342 l_quality_code VARCHAR2 (25) := p_quality_code;
343 l_transaction_date DATE := p_transaction_date;
344 l_transaction_type VARCHAR2 (30) := p_transaction_type;
345 l_vendor_lot VARCHAR2 (30) := p_vendor_lot;
346 l_reason_id NUMBER := p_reason_id;
347 l_organization_id NUMBER := p_organization_id;
348 l_comments VARCHAR2 (240) := p_comments;
349 l_attribute_category VARCHAR2 (30) := p_attribute_category;
350 l_attribute1 VARCHAR2 (150) := p_attribute1;
351 l_attribute2 VARCHAR2 (150) := p_attribute2;
352 l_attribute3 VARCHAR2 (150) := p_attribute3;
353 l_attribute4 VARCHAR2 (150) := p_attribute4;
354 l_attribute5 VARCHAR2 (150) := p_attribute5;
355 l_attribute6 VARCHAR2 (150) := p_attribute6;
356 l_attribute7 VARCHAR2 (150) := p_attribute7;
357 l_attribute8 VARCHAR2 (150) := p_attribute8;
358 l_attribute9 VARCHAR2 (150) := p_attribute9;
359 l_attribute10 VARCHAR2 (150) := p_attribute10;
360 l_attribute11 VARCHAR2 (150) := p_attribute11;
361 l_attribute12 VARCHAR2 (150) := p_attribute12;
362 l_attribute13 VARCHAR2 (150) := p_attribute13;
363 l_attribute14 VARCHAR2 (150) := p_attribute14;
364 l_attribute15 VARCHAR2 (150) := p_attribute15;
365 l_qa_collection_id NUMBER := p_qa_collection_id;
366 l_rti_lpn_id NUMBER := p_lpn_id;
367 l_rti_transfer_lpn_id NUMBER := p_transfer_lpn_id;
368 l_sec_uom VARCHAR2 (25) := p_sec_uom;
369 l_rtv_sec_qty NUMBER := p_rtv_sec_qty;
370 l_rtv_qty NUMBER := p_rtv_qty;
371 l_mol_qty NUMBER := p_mol_qty;
372 l_primary_qty NUMBER := p_primary_qty;
373 l_mol_line_id NUMBER := p_mol_line_id;
374 l_rcv_transaction_id NUMBER := p_rcv_transaction_id;
375 l_return_status VARCHAR2 (5);
376 l_msg_count NUMBER;
377 l_msg_data VARCHAR2 (1000);
378 l_mo_splt_tb inv_rcv_integration_apis.mo_in_tb_tp;
379 l_new_mol_id NUMBER;
380 l_progress NUMBER;
381 l_lot_control_code NUMBER;
382 l_serial_control_code NUMBER;
383 l_uom_code VARCHAR2 (5) := p_uom_code;
384 l_inventory_item_id NUMBER := p_inventory_item_id;
385 l_rec_count NUMBER;
386 x_return_status VARCHAR2 (5);
387 x_msg_count NUMBER;
388 x_msg_data VARCHAR2 (1000);
389 l_lot_status_id NUMBER;
390 l_lot_expiration_date DATE;
391 l_origination_type NUMBER; --OPM Convergence
392 l_expiration_action_code VARCHAR2 (32); --OPM Convergence
393 l_expiration_action_date DATE; --OPM Convergence
394 l_hold_date DATE; --OPM Convergence
395 l_false VARCHAR2 (1)
396 := inv_rcv_integration_apis.g_false;
397 l_txn_if_id NUMBER;
398 l_serial_temp_id NUMBER;
399 l_prod_code VARCHAR2 (5)
400 := inv_rcv_integration_apis.g_prod_code;
401 l_yes VARCHAR2 (1)
402 := inv_rcv_integration_apis.g_yes;
403 l_no VARCHAR2 (1) := inv_rcv_integration_apis.g_no;
404 l_serial_status_id NUMBER;
405
406 TYPE number_tb_tp IS TABLE OF NUMBER
407 INDEX BY BINARY_INTEGER;
408
409 l_mmtt_ids number_tb_tp;
410 l_primary_quantities number_tb_tp;
411 l_transaction_quantities number_tb_tp;
412 l_secondary_txn_quantities number_tb_tp;
413 l_rt_serial_tbl t_rt_serial_rec_tbl;
414 l_debug NUMBER
415 := NVL (fnd_profile.VALUE ('INV_DEBUG_TRACE'), 0);
416 BEGIN
417
418 -- below select is necessary. We are calling this api per RT and we can
419 -- have multiple RTs associated for the same mol. In that case, the second
420 -- and further calls to this api for same mol will have wrong qty.
421 begin
422 select quantity into l_mol_qty
423 from mtl_txn_request_lines
424 where line_id = l_mol_line_id;
425 exception
426 when others then
427 if l_debug = 1 then
428 print_debug (' Exception while fetching current mol qty for mol ' || l_mol_line_id, 4);
429 end if;
430 end;
431 IF (l_rtv_qty < l_mol_qty)
432 THEN
433 l_mo_splt_tb (1).prim_qty := l_primary_qty;
434
435 IF (l_debug = 1)
436 THEN
437 print_debug ( 'split_mol_create_rti : Calling split_mo: '
438 || l_return_status,
439 4
440 );
441 print_debug (' p_orig_mol_id ============> ' || l_mol_line_id, 4);
442 print_debug ( ' p_mo_splt_tb(1).prim_qty => '
443 || l_mo_splt_tb (1).prim_qty,
444 4
445 );
446 END IF;
447
448 inv_rcv_integration_apis.split_mo (p_orig_mol_id => l_mol_line_id,
449 p_mo_splt_tb => l_mo_splt_tb,
450 x_return_status => l_return_status,
451 x_msg_count => l_msg_count,
452 x_msg_data => l_msg_data
453 );
454
455 IF (l_debug = 1)
456 THEN
457 print_debug ( 'split_mol_create_rti : Call to split_mo returns: '
458 || l_return_status,
459 4
460 );
461 END IF;
462
463 IF l_return_status = fnd_api.g_ret_sts_error
464 THEN
465 RAISE fnd_api.g_exc_error;
466 END IF;
467
468 IF l_return_status = fnd_api.g_ret_sts_unexp_error
469 THEN
470 RAISE fnd_api.g_exc_unexpected_error;
471 END IF;
472
473 l_new_mol_id := l_mo_splt_tb (1).line_id;
474 ELSE
475 l_new_mol_id := l_mol_line_id;
476 END IF; -- IF (l_remaining_mol_qty < l_mol_qty) THEN
477
478 l_progress := '50';
479
480 -- clear records before bulk collecting
481 if l_rt_serial_tbl.count > 0 then
482 l_rt_serial_tbl.DELETE; -- 12942776
483 end if;
484
485 IF (l_mmtt_ids.COUNT > 0)
486 THEN
487 l_mmtt_ids.DELETE;
488 END IF;
489
490 l_progress := '60';
491
492 IF (l_transaction_quantities.COUNT > 0)
493 THEN
494 l_transaction_quantities.DELETE;
495 END IF;
496
497 /* OPM Convergence */
498 IF (l_secondary_txn_quantities.COUNT > 0)
499 THEN
500 l_secondary_txn_quantities.DELETE;
501 END IF;
502
503 l_progress := '70';
504
505 IF (l_primary_quantities.COUNT > 0)
506 THEN
507 l_primary_quantities.DELETE;
508 END IF;
509
510 l_progress := '80';
511
512 BEGIN
513 print_debug ( 'Select mmtt records based on move_order_line_id'
514 || 'order by transaction_temp_id',
515 4
516 ); --6160359,6189438
517
518 SELECT transaction_temp_id, primary_quantity,
519 DECODE (transaction_uom,
520 l_uom_code, transaction_quantity
521 /*Bug6133345*/
522 ,
523 inv_rcv_cache.convert_qty (l_inventory_item_id,
524 transaction_quantity,
525 transaction_uom,
526 l_uom_code,
527 NULL
528 )
529 ) quantity,
530 secondary_transaction_quantity --OPM Convergence
531 BULK COLLECT INTO l_mmtt_ids, l_primary_quantities,
532 l_transaction_quantities,
533 l_secondary_txn_quantities --OPM Convergence
534 FROM mtl_material_transactions_temp
535 WHERE move_order_line_id = l_new_mol_id;
536 EXCEPTION
537 WHEN OTHERS
538 THEN
539 l_mmtt_ids (1) := NULL;
540 l_primary_quantities (1) := l_primary_qty;
541 l_transaction_quantities (1) := l_rtv_qty;
542 l_secondary_txn_quantities (1) := l_rtv_sec_qty;
543 --OPM Convergence
544 END;
545
546 l_progress := '90';
547
548 -- IF there are no mmtts, then insert RTI with no MMTT id
549 -- with l_rtv_qty and l_primary_qty
550 IF (l_mmtt_ids.COUNT = 0)
551 THEN
552 l_mmtt_ids (1) := NULL;
553 l_primary_quantities (1) := l_primary_qty;
554 l_transaction_quantities (1) := l_rtv_qty;
555 l_secondary_txn_quantities (1) := l_rtv_sec_qty;
556 --OPM Convergence
557 END IF;
558
559 l_progress := '100';
560
561 FOR i IN 1 .. l_mmtt_ids.COUNT
562 LOOP
563 IF (l_debug = 1)
564 THEN
565 print_debug ( 'inserting RTI for MMTT:'
566 || NVL (l_mmtt_ids (i), -1)
567 || ' quantity:'
568 || l_transaction_quantities (i)
569 || ' uom:'
570 || l_uom,
571 4
572 );
573 END IF;
574
575 l_progress := '110';
576 insert_inspect_rec_rti (x_return_status => l_return_status,
577 x_msg_count => l_msg_count,
578 x_msg_data => l_msg_data,
579 p_rcv_transaction_id => l_rcv_transaction_id,
580 p_quantity => l_transaction_quantities
581 (i),
582 p_uom => l_uom,
583 p_inspection_code => l_inspection_code,
584 p_quality_code => l_quality_code,
585 p_transaction_date => l_transaction_date,
586 p_transaction_type => l_transaction_type,
587 p_vendor_lot => l_vendor_lot,
588 p_reason_id => l_reason_id,
589 p_primary_qty => l_primary_quantities
590 (i),
591 p_organization_id => l_organization_id,
592 p_comments => l_comments,
593 p_attribute_category => l_attribute_category,
594 p_attribute1 => l_attribute1,
595 p_attribute2 => l_attribute2,
596 p_attribute3 => l_attribute3,
597 p_attribute4 => l_attribute4,
598 p_attribute5 => l_attribute5,
599 p_attribute6 => l_attribute6,
600 p_attribute7 => l_attribute7,
601 p_attribute8 => l_attribute8,
602 p_attribute9 => l_attribute9,
603 p_attribute10 => l_attribute10,
604 p_attribute11 => l_attribute11,
605 p_attribute12 => l_attribute12,
606 p_attribute13 => l_attribute13,
607 p_attribute14 => l_attribute14,
608 p_attribute15 => l_attribute15,
609 p_qa_collection_id => l_qa_collection_id,
610 p_lpn_id => l_rti_lpn_id,
611 p_transfer_lpn_id => l_rti_transfer_lpn_id,
612 p_mmtt_temp_id => l_mmtt_ids (i),
613 p_sec_uom => l_sec_uom,
614 p_secondary_qty => l_rtv_sec_qty
615 ); --OPM Convergence
616
617 IF l_return_status = fnd_api.g_ret_sts_error
618 THEN
619 RAISE fnd_api.g_exc_error;
620 END IF;
621
622 IF l_return_status = fnd_api.g_ret_sts_unexp_error
623 THEN
624 RAISE fnd_api.g_exc_unexpected_error;
625 END IF;
626
627 l_progress := '120';
628
629 SELECT lot_control_code, serial_number_control_code
630 INTO l_lot_control_code, l_serial_control_code
631 FROM mtl_system_items
632 WHERE inventory_item_id = p_inventory_item_id
633 AND organization_id = p_organization_id;
634
635 IF (l_lot_control_code > 1 OR l_serial_control_code > 1)
636 THEN
637 IF (l_debug = 1)
638 THEN
639 print_debug ( 'creating lots and/or serials interface '
640 || 'records with product_transaction_id : '
641 || g_interface_transaction_id,
642 4
643 );
644 END IF;
645
646 l_progress := '130';
647
648 SELECT rt.transaction_id,
649 rss.serial_num
650 BULK COLLECT INTO l_rt_serial_tbl
651 FROM rcv_transactions rt, rcv_serials_supply rss
652 WHERE rt.transaction_id = l_rcv_transaction_id
653 AND rss.transaction_id = rt.transaction_id
654 AND rss.serial_num IN (SELECT *
655 FROM TABLE (list_serials));
656
657 l_progress := '140';
658
659 IF (l_debug = 1)
660 THEN
661 print_debug ('l_rt_serial_tbl count ' || l_rt_serial_tbl.COUNT,
662 4);
663 END IF;
664
665 IF l_rt_serial_tbl.COUNT > 0
666 THEN
667 l_progress := '150';
668
669 IF l_lot_control_code > 1 AND p_lot_number IS NOT NULL
670 THEN
671 SELECT expiration_date, status_id,
672 origination_type --OPM Convergence
673 ,
674 expiration_action_code --OPM Convergence
675 ,
676 expiration_action_date --OPM Convergence
677 ,
678 hold_date --OPM Convergence
679 INTO l_lot_expiration_date, l_lot_status_id,
680 l_origination_type --OPM Convergence
681 ,
682 l_expiration_action_code --OPM Convergence
683 ,
684 l_expiration_action_date --OPM Convergence
685 ,
686 l_hold_date --OPM Convergence
687 FROM mtl_lot_numbers
688 WHERE lot_number = p_lot_number
689 AND inventory_item_id = p_inventory_item_id
690 AND organization_id = p_organization_id;
691
692 --Call the insert_mtli API
693 inv_rcv_integration_apis.insert_mtli
694 (p_api_version => 1.0,
695 p_init_msg_lst => l_false,
696 x_return_status => x_return_status,
697 x_msg_count => x_msg_count,
698 x_msg_data => x_msg_data,
699 p_transaction_interface_id => l_txn_if_id,
700 p_lot_number => p_lot_number,
701 p_transaction_quantity => l_transaction_quantities
702 (i),
703 p_primary_quantity => l_primary_quantities
704 (i),
705 p_organization_id => p_organization_id,
706 p_inventory_item_id => p_inventory_item_id,
707 p_expiration_date => l_lot_expiration_date,
708 p_status_id => l_lot_status_id,
709 x_serial_transaction_temp_id => l_serial_temp_id,
710 p_product_transaction_id => g_interface_transaction_id,
711 p_product_code => l_prod_code,
712 p_att_exist => l_yes,
713 p_update_mln => l_no,
714 p_origination_type => l_origination_type
715 --OPM Convergence
716 ,
717 p_expiration_action_code => l_expiration_action_code
718 --OPM Convergence
719 ,
720 p_expiration_action_date => l_expiration_action_date
721 --OPM Convergence
722 ,
723 p_hold_date => l_hold_date
724 ); --OPM Convergence
725
726 IF x_return_status = fnd_api.g_ret_sts_error
727 THEN
728 RAISE fnd_api.g_exc_error;
729 END IF;
730
731 IF x_return_status = fnd_api.g_ret_sts_unexp_error
732 THEN
733 RAISE fnd_api.g_exc_unexpected_error;
734 END IF;
735
736 IF (l_debug = 1)
737 THEN
738 print_debug
739 ( 'process_lot_serial_intf 1.1: Created MTLI record for lot: txn if: '
740 || l_txn_if_id
741 || ', serial_temp_id: '
742 || l_serial_temp_id
743 || ', prod_txn_id: '
744 || g_interface_transaction_id,
745 4
746 );
747 END IF;
748 -- No need to check if the item is serial controlled or not
749 -- as we come here only if the item is serial controlled.
750 -- no need to null out the serial_transaction_temp_id in mtli
751 END IF;
752
753 FOR i IN l_rt_serial_tbl.FIRST .. l_rt_serial_tbl.LAST
754 LOOP
755 IF l_rt_serial_tbl (i).rcv_transaction_id =
756 l_rcv_transaction_id
757 THEN
758 SELECT status_id
759 INTO l_serial_status_id
760 FROM mtl_serial_numbers
761 WHERE serial_number = l_rt_serial_tbl (i).serial_number
762 AND inventory_item_id = p_inventory_item_id;
763
764 IF (l_lot_control_code > 1 AND p_lot_number IS NOT NULL)
765 THEN
766 l_txn_if_id := l_serial_temp_id;
767 END IF;
768
769 --Call the insert_msni API
770 inv_rcv_integration_apis.insert_msni
771 (p_api_version => 1.0,
772 p_init_msg_lst => l_false,
773 x_return_status => x_return_status,
774 x_msg_count => x_msg_count,
775 x_msg_data => x_msg_data,
776 p_transaction_interface_id => l_txn_if_id,
777 p_fm_serial_number => l_rt_serial_tbl (i).serial_number,
778 p_to_serial_number => l_rt_serial_tbl (i).serial_number,
779 p_organization_id => p_organization_id,
780 p_inventory_item_id => p_inventory_item_id,
781 p_status_id => l_serial_status_id,
782 p_product_transaction_id => g_interface_transaction_id,
783 p_product_code => l_prod_code,
784 p_att_exist => l_yes,
785 p_update_msn => l_no
786 );
787
788 IF x_return_status = fnd_api.g_ret_sts_error
789 THEN
790 RAISE fnd_api.g_exc_error;
791 END IF;
792
793 IF x_return_status = fnd_api.g_ret_sts_unexp_error
794 THEN
795 RAISE fnd_api.g_exc_unexpected_error;
796 END IF;
797
798 IF (l_debug = 1)
799 THEN
800 print_debug
801 ( 'process_lot_serial_intf 1.2: Created MSNI record for serial: '
802 || l_rt_serial_tbl (i).serial_number
803 || ' with txn_if_id: '
804 || l_txn_if_id
805 || ', prod_txn_id: '
806 || g_interface_transaction_id,
807 4
808 );
809 END IF;
810
811 l_progress := '160';
812 END IF;
813 END LOOP;
814
815 l_progress := '166';
816 ELSE
817 process_lot_serial_intf
818 (x_return_status => l_return_status,
819 x_msg_count => l_msg_count,
820 x_msg_data => l_msg_data,
821 p_organization_id => p_organization_id,
822 p_inventory_item_id => p_inventory_item_id,
823 p_lot_control_code => l_lot_control_code,
824 p_serial_control_code => l_serial_control_code,
825 p_lot_number => p_lot_number,
826 p_txn_qty => l_transaction_quantities
827 (i),
828 p_primary_qty => l_primary_quantities
829 (i),
830 p_serial_number => NULL,
831 p_product_transaction_id => g_interface_transaction_id,
832 p_lpn_id => p_lpn_id,
833 p_sec_txn_qty => l_secondary_txn_quantities
834 (i)
835 );
836 END IF;
837
838 IF (l_debug = 1)
839 THEN
840 print_debug ('process_lot_serial_intf returns: '
841 || l_return_status,
842 4
843 );
844 END IF;
845
846 IF l_return_status = fnd_api.g_ret_sts_error
847 THEN
848 RAISE fnd_api.g_exc_error;
849 END IF;
850
851 IF l_return_status = fnd_api.g_ret_sts_unexp_error
852 THEN
853 RAISE fnd_api.g_exc_unexpected_error;
854 END IF;
855
856 l_progress := '170';
857 END IF; --END IF check lot and serial controls
858 END LOOP; -- End MMTT Loop
859
860 -- Activate the INSPECT operation
861 l_rec_count :=
862 wms_putaway_utils.activate_plan_for_inspect
863 (x_return_status => x_return_status,
864 x_msg_count => x_msg_count,
865 x_msg_data => x_msg_data,
866 p_org_id => l_organization_id,
867 p_mo_line_id => l_new_mol_id
868 );
869 l_progress := '180';
870
871 IF (x_return_status <> fnd_api.g_ret_sts_success)
872 THEN
873 IF (l_debug = 1)
874 THEN
875 print_debug (' Error in Activate_Plan_For_Load ' || x_msg_data, 1);
876 END IF;
877
878 RAISE fnd_api.g_exc_error;
879 ELSE
880 IF (l_debug = 1)
881 THEN
882 print_debug ( 'Successfully called Activate_Plan_For_Load for '
883 || l_rec_count
884 || ' row(s)',
885 9
886 );
887 END IF;
888 END IF;
889
890 l_progress := '190';
891
892 -- Activate the INSPECT operation
893 --Update the wms_process_flag for the current MOL so that one else
894 --messes with it
895 UPDATE mtl_txn_request_lines
896 SET wms_process_flag = 2
897 WHERE line_id = l_new_mol_id;
898
899 l_progress := '200';
900 EXCEPTION
901 WHEN fnd_api.g_exc_error
902 THEN
903 IF (l_debug = 1)
904 THEN
905 print_debug ('l_progress ' || l_progress, 4);
906 END IF;
907
908 x_return_status := fnd_api.g_ret_sts_error;
909 -- Get message count and data
910 fnd_msg_pub.count_and_get (p_count => x_msg_count,
911 p_data => x_msg_data);
912 WHEN fnd_api.g_exc_unexpected_error
913 THEN
914 IF (l_debug = 1)
915 THEN
916 print_debug ('l_progress ' || l_progress, 4);
917 print_debug (' SQLCODE ' || SQLCODE, 4);
918 print_debug (' SQLERRM ' || SQLERRM, 4);
919 END IF;
920
921 x_return_status := fnd_api.g_ret_sts_unexp_error;
922 fnd_msg_pub.count_and_get (p_count => x_msg_count,
923 p_data => x_msg_data);
924 WHEN OTHERS
925 THEN
926 IF (l_debug = 1)
927 THEN
928 print_debug ('l_progress ' || l_progress, 4);
929 print_debug (' SQLCODE ' || SQLCODE, 4);
930 print_debug (' SQLERRM ' || SQLERRM, 4);
931 END IF;
932
933 x_return_status := fnd_api.g_ret_sts_unexp_error;
934
935 IF fnd_msg_pub.check_msg_level (fnd_msg_pub.g_msg_lvl_unexp_error)
936 THEN
937 fnd_msg_pub.add_exc_msg (g_pkg_name, 'split_mol_create_rti');
938 END IF;
939
940 fnd_msg_pub.count_and_get (p_count => x_msg_count,
941 p_data => x_msg_data);
942 END split_mol_create_rti;
943
944 -- 12942776 new main_process
945
946 PROCEDURE main_process (
947 x_return_status OUT NOCOPY VARCHAR2,
948 x_msg_count OUT NOCOPY NUMBER,
949 x_msg_data OUT NOCOPY VARCHAR2,
950 p_inventory_item_id IN NUMBER,
951 p_organization_id IN NUMBER,
952 p_lpn_id IN NUMBER,
953 p_revision IN VARCHAR2,
954 p_lot_number IN VARCHAR2,
955 p_uom_code IN VARCHAR2,
956 p_quantity IN NUMBER,
957 p_inspection_code IN VARCHAR2,
958 p_quality_code IN VARCHAR2,
959 p_transaction_type IN VARCHAR2,
960 p_reason_id IN NUMBER,
961 p_serial_number IN VARCHAR2,
962 p_accept_lpn_id IN NUMBER,
963 p_reject_lpn_id IN NUMBER,
964 p_transaction_date IN DATE DEFAULT SYSDATE,
965 p_qa_collection_id IN NUMBER DEFAULT NULL,
966 p_vendor_lot IN VARCHAR2 DEFAULT NULL,
967 p_comments IN VARCHAR2 DEFAULT NULL,
968 p_attribute_category IN VARCHAR2 DEFAULT NULL,
969 p_attribute1 IN VARCHAR2 DEFAULT NULL,
970 p_attribute2 IN VARCHAR2 DEFAULT NULL,
971 p_attribute3 IN VARCHAR2 DEFAULT NULL,
972 p_attribute4 IN VARCHAR2 DEFAULT NULL,
973 p_attribute5 IN VARCHAR2 DEFAULT NULL,
974 p_attribute6 IN VARCHAR2 DEFAULT NULL,
975 p_attribute7 IN VARCHAR2 DEFAULT NULL,
976 p_attribute8 IN VARCHAR2 DEFAULT NULL,
977 p_attribute9 IN VARCHAR2 DEFAULT NULL,
978 p_attribute10 IN VARCHAR2 DEFAULT NULL,
979 p_attribute11 IN VARCHAR2 DEFAULT NULL,
980 p_attribute12 IN VARCHAR2 DEFAULT NULL,
981 p_attribute13 IN VARCHAR2 DEFAULT NULL,
982 p_attribute14 IN VARCHAR2 DEFAULT NULL,
983 p_attribute15 IN VARCHAR2 DEFAULT NULL,
984 p_secondary_qty IN NUMBER DEFAULT NULL,
985 p_serial_tbl IN inv_rcv_std_inspect_apis.t_serial_rec_tbl
986 )
987 IS
988 l_inventory_item_id NUMBER := p_inventory_item_id;
989 l_organization_id NUMBER := p_organization_id;
990 l_lpn_id NUMBER := p_lpn_id;
991 l_revision VARCHAR2 (10) := p_revision;
992 -- Increased lot size to 80 Char - Mercy Thomas - B4625329
993 l_lot_number VARCHAR2 (80) := p_lot_number;
994 l_uom_code VARCHAR2 (5) := p_uom_code;
995 l_uom VARCHAR2 (30);
996 l_quantity NUMBER := p_quantity;
997 l_serial_number VARCHAR2 (30) := p_serial_number;
998 l_accept_lpn_id NUMBER := p_accept_lpn_id;
999 l_reject_lpn_id NUMBER := p_reject_lpn_id;
1000 l_rti_lpn_id NUMBER;
1001 l_rti_transfer_lpn_id NUMBER;
1002 l_inspection_code VARCHAR2 (25) := p_inspection_code;
1003 l_quality_code VARCHAR2 (25) := p_quality_code;
1004 l_transaction_date DATE := p_transaction_date;
1005 l_comments VARCHAR2 (240) := p_comments;
1006 l_attribute_category VARCHAR2 (30) := p_attribute_category;
1007 l_attribute1 VARCHAR2 (150) := p_attribute1;
1008 l_attribute2 VARCHAR2 (150) := p_attribute2;
1009 l_attribute3 VARCHAR2 (150) := p_attribute3;
1010 l_attribute4 VARCHAR2 (150) := p_attribute4;
1011 l_attribute5 VARCHAR2 (150) := p_attribute5;
1012 l_attribute6 VARCHAR2 (150) := p_attribute6;
1013 l_attribute7 VARCHAR2 (150) := p_attribute7;
1014 l_attribute8 VARCHAR2 (150) := p_attribute8;
1015 l_attribute9 VARCHAR2 (150) := p_attribute9;
1016 l_attribute10 VARCHAR2 (150) := p_attribute10;
1017 l_attribute11 VARCHAR2 (150) := p_attribute11;
1018 l_attribute12 VARCHAR2 (150) := p_attribute12;
1019 l_attribute13 VARCHAR2 (150) := p_attribute13;
1020 l_attribute14 VARCHAR2 (150) := p_attribute14;
1021 l_attribute15 VARCHAR2 (150) := p_attribute15;
1022 l_transaction_type VARCHAR2 (30) := p_transaction_type;
1023 l_vendor_lot VARCHAR2 (30) := p_vendor_lot;
1024 l_reason_id NUMBER := p_reason_id;
1025 l_qa_collection_id NUMBER := p_qa_collection_id;
1026 l_primary_qty NUMBER;
1027 l_primary_uom_code VARCHAR2 (5);
1028 l_mol_line_id NUMBER;
1029 l_mol_new_line_id NUMBER;
1030 l_mol_header_id NUMBER;
1031 l_mol_uom_code VARCHAR2 (5);
1032 l_mol_qty NUMBER;
1033 l_rcv_transaction_id NUMBER;
1034 l_rtv_qty NUMBER;
1035 l_rls_qty NUMBER;
1036 l_cnv_rls_qty NUMBER; -- Added for bug 6688055
1037 l_rtv_uom VARCHAR2 (25);
1038 /* Each */
1039 l_rtv_uom_code VARCHAR2 (5);
1040 /* Ea */
1041 l_receipt_source_code VARCHAR2 (25);
1042 l_tolerable_qty NUMBER;
1043 l_remaining_qty NUMBER;
1044 l_remaining_mol_qty NUMBER;
1045 l_inspection_status NUMBER;
1046 l_return_status VARCHAR2 (5);
1047 l_msg_count NUMBER;
1048 l_msg_data VARCHAR2 (1000);
1049 l_rec_count NUMBER;
1050 l_secondary_qty NUMBER := p_secondary_qty;
1051 --OPM COnvergence
1052 l_remaining_sec_qty NUMBER; --OPM Convergence
1053 l_rtv_sec_uom VARCHAR2 (25); --OPM Convergence
1054 l_sec_uom_code VARCHAR2 (3); --OPM Convergence
1055 l_sec_uom VARCHAR2 (25); --OPM Convergence
1056 l_sec_mol_qty NUMBER; --OPM COnvergence
1057 l_sec_remaining_mol_qty NUMBER; --OPM Convergence
1058 l_sec_remaining_qty NUMBER; --OPM Convergence
1059 l_rtv_sec_qty NUMBER; --OPM Convergence
1060 l_processed_lot_prim_qty NUMBER;
1061
1062 TYPE number_tb_tp IS TABLE OF NUMBER
1063 INDEX BY BINARY_INTEGER;
1064
1065 l_mmtt_ids number_tb_tp;
1066 l_primary_quantities number_tb_tp;
1067 l_transaction_quantities number_tb_tp;
1068 l_secondary_txn_quantities number_tb_tp; --OPM Convergence
1069
1070 l_mol_ref varchar2(30);
1071 l_mol_ref_id number;
1072 l_mol_ref_code number;
1073 l_mol_txn_src_id number;
1074 l_mol_inspect_status number;
1075 l_goto_remaining_qty number;
1076
1077 -- 12942776 start
1078
1079 TYPE t_tmp_rec IS RECORD (
1080 rcv_transaction_id NUMBER,
1081 receipt_source_code VARCHAR2 (30),
1082 unit_of_measure VARCHAR2 (30),
1083 secondary_unit_of_measure VARCHAR2 (30),
1084 quantity NUMBER
1085 );
1086
1087 TYPE t_tmp_rec_tbl IS TABLE OF t_tmp_rec
1088 INDEX BY BINARY_INTEGER;
1089
1090 t_tmp_tbl t_tmp_rec_tbl;
1091 t_tmp_count NUMBER;
1092 t_touch NUMBER;
1093 l_ser_rtv_qty NUMBER;
1094 i NUMBER;
1095
1096 -- 12942776 end
1097 CURSOR mol_cursor (
1098 k_inventory_item_id NUMBER,
1099 k_organization_id NUMBER,
1100 k_lpn_id NUMBER,
1101 k_revision VARCHAR2,
1102 k_lot_number VARCHAR2
1103 )
1104 IS
1105 SELECT line_id, header_id, uom_code,
1106 quantity - NVL (quantity_delivered, 0),
1107 secondary_quantity
1108 - NVL (secondary_quantity_delivered, 0), --OPM Convergence
1109 REFERENCE, REFERENCE_TYPE_CODE,REFERENCE_ID,
1110 TXN_SOURCE_ID, inspection_status
1111 FROM mtl_txn_request_lines
1112 WHERE inventory_item_id = k_inventory_item_id
1113 AND organization_id = k_organization_id
1114 AND lpn_id = k_lpn_id
1115 AND (revision = k_revision OR revision IS NULL AND p_revision IS NULL
1116 )
1117 AND ( lot_number = k_lot_number
1118 OR lot_number IS NULL AND p_lot_number IS NULL
1119 )
1120 AND inspection_status IS NOT NULL
1121 AND line_status = 7
1122 AND quantity - NVL (quantity_delivered, 0) > 0
1123 AND wms_process_flag = 1
1124 order by inspection_status, abs(quantity - l_quantity);
1125
1126 -- MOLCON
1127 CURSOR rtv_van_cursor (
1128 k_item_id NUMBER,
1129 k_item_revision VARCHAR2,
1130 k_lpn_id NUMBER
1131 )
1132 IS
1133 SELECT rs.rcv_transaction_id, rsh.receipt_source_code,
1134 rs.unit_of_measure,
1135 rs.secondary_unit_of_measure --OPM Convergence
1136 FROM rcv_supply rs, rcv_transactions rt, rcv_shipment_headers rsh
1137 WHERE rs.rcv_transaction_id = rt.transaction_id
1138 AND rsh.shipment_header_id = rs.shipment_header_id
1139 AND rs.supply_type_code = 'RECEIVING'
1140 AND rt.transaction_type <> 'UNORDERED'
1141 AND rt.routing_header_id = g_inspection_routing
1142 AND rs.item_id = k_item_id
1143 AND ( k_item_revision IS NULL -- Bug : 6139900
1144 OR NVL (rs.item_revision, '@#*') = NVL (k_item_revision, '@#*')
1145 )
1146 AND rs.lpn_id = k_lpn_id; --l_lpn_id should always be NOT NULL
1147
1148 CURSOR rtv_lot_cursor (
1149 k_item_id NUMBER,
1150 k_item_revision VARCHAR2,
1151 k_lpn_id NUMBER,
1152 k_lot_number VARCHAR2
1153 )
1154 IS
1155 SELECT rs.rcv_transaction_id, rsh.receipt_source_code,
1156 rs.unit_of_measure,
1157 rs.secondary_unit_of_measure --OPM Convergence
1158 ,
1159 rls.quantity quantity
1160 FROM rcv_supply rs,
1161 rcv_lots_supply rls,
1162 rcv_transactions rt,
1163 rcv_shipment_headers rsh
1164 WHERE rs.rcv_transaction_id = rt.transaction_id
1165 AND rsh.shipment_header_id = rs.shipment_header_id
1166 AND rs.supply_type_code = 'RECEIVING'
1167 AND rt.transaction_type <> 'UNORDERED'
1168 AND rt.routing_header_id = g_inspection_routing
1169 AND rs.item_id = k_item_id
1170 AND ( k_item_revision IS NULL -- Bug : 6139900
1171 OR NVL (rs.item_revision, '@#*') = NVL (k_item_revision, '@#*')
1172 )
1173 AND rs.lpn_id = k_lpn_id --l_lpn_id should always be NOT NULL
1174 AND rls.transaction_id = rs.rcv_transaction_id
1175 AND rls.lot_num = k_lot_number;
1176
1177 l_debug NUMBER
1178 := NVL (fnd_profile.VALUE ('INV_DEBUG_TRACE'), 0);
1179 --New variables for Lot/Serial Support
1180 l_lot_control_code NUMBER;
1181 l_serial_control_code NUMBER;
1182 l_mo_splt_tb inv_rcv_integration_apis.mo_in_tb_tp;
1183 l_txn_qty_to_split NUMBER;
1184 l_primary_qty_to_split NUMBER;
1185 l_new_mol_id NUMBER;
1186 l_split_line_id NUMBER; --for debug
1187 l_progress VARCHAR2 (10) := '0';
1188 BEGIN
1189 x_return_status := fnd_api.g_ret_sts_success;
1190
1191 IF (l_debug = 1)
1192 THEN
1193 print_debug (' new main_process: Just entering main_process', 4);
1194 print_debug ('p_inventory_item_id => ' || p_inventory_item_id, 4);
1195 print_debug ('p_organization_id => ' || p_organization_id, 4);
1196 print_debug ('p_lpn_id => ' || p_lpn_id, 4);
1197 print_debug ('p_revision => ' || p_revision, 4);
1198 print_debug ('p_lot_number => ' || p_lot_number, 4);
1199 print_debug ('p_uom_code => ' || p_uom_code, 4);
1200 print_debug ('p_quantity => ' || p_quantity, 4);
1201 print_debug ('p_serial_number => ' || p_serial_number, 4);
1202 print_debug ('p_inspection_code => ' || p_inspection_code, 4);
1203 END IF;
1204
1205 if t_tmp_tbl.count > 0 then
1206 t_tmp_tbl.DELETE;
1207 end if;
1208
1209 --First check if the transaction date satisfies the validation.
1210 --If the transaction date is invalid then error out the transaction
1211 IF inv_rcv_common_apis.g_po_startup_value.sob_id IS NULL
1212 THEN
1213 --BUG 3444196: Used the HR view instead for performance reasons
1214 SELECT TO_NUMBER (hoi.org_information1)
1215 INTO inv_rcv_common_apis.g_po_startup_value.sob_id
1216 FROM hr_organization_information hoi
1217 WHERE hoi.organization_id = p_organization_id
1218 AND (hoi.org_information_context || '') = 'Accounting Information';
1219 END IF;
1220
1221 inv_rcv_common_apis.validate_trx_date
1222 (p_trx_date => SYSDATE,
1223 p_organization_id => p_organization_id,
1224 p_sob_id => inv_rcv_common_apis.g_po_startup_value.sob_id,
1225 x_return_status => x_return_status,
1226 x_error_code => x_msg_data
1227 );
1228
1229 IF x_return_status <> fnd_api.g_ret_sts_success
1230 THEN
1231 RETURN;
1232 END IF;
1233
1234 SAVEPOINT inspect_main_sp;
1235 -- Quantity entered on form
1236 l_remaining_qty := l_quantity;
1237 l_sec_remaining_qty:= l_secondary_qty; --Bug 13924968
1238
1239 -- Mapping of Inspection code values from Receiving to WMS/Inventory.
1240 -- Wrong habit to hardcode but that's what receiving
1241 -- transactions interface expects.
1242 -- Inspection Status values:
1243 -- 1 - Yet to be inspected
1244 -- 2 - Accepted
1245 -- 3 - Rejected
1246 IF l_inspection_code = 'ACCEPT'
1247 THEN
1248 l_inspection_status := g_accept;
1249 /* Accept */
1250 ELSE
1251 l_inspection_status := g_reject;
1252 /* Reject */
1253 END IF;
1254
1255 l_primary_uom_code :=
1256 inv_rcv_cache.get_primary_uom_code (l_organization_id,
1257 l_inventory_item_id
1258 );
1259 l_sec_uom_code :=
1260 inv_rcv_cache.get_secondary_uom_code (l_organization_id,
1261 l_inventory_item_id
1262 );
1263
1264 -- Purchasing/receiving uses unit of measure (Each)
1265 -- rather than uom code(Ea) and hence the following..
1266 -- This will be used later while inserting into interface table
1267 SELECT unit_of_measure
1268 INTO l_uom
1269 FROM mtl_units_of_measure
1270 WHERE uom_code = l_uom_code;
1271
1272 /*OPM Convergence */
1273 IF l_sec_uom_code IS NOT NULL
1274 THEN
1275 SELECT unit_of_measure
1276 INTO l_sec_uom
1277 FROM mtl_units_of_measure
1278 WHERE uom_code = l_sec_uom_code;
1279 END IF;
1280
1281 -- Open Move Order Line cursor
1282 OPEN mol_cursor (l_inventory_item_id,
1283 l_organization_id,
1284 l_lpn_id,
1285 l_revision,
1286 l_lot_number
1287 );
1288
1289 WHILE (l_remaining_qty > 0)
1290 LOOP
1291
1292 l_goto_remaining_qty := l_remaining_qty;
1293 IF (l_debug = 1)
1294 THEN
1295 print_debug ( 'new Main process l_remaining_qty : '
1296 || TO_CHAR (l_remaining_qty),
1297 4
1298 );
1299 END IF;
1300
1301 -- MOLCON
1302
1303 -- REFERENCE, REFERENCE_TYPE_CODE,REFERENCE_ID
1304 FETCH mol_cursor
1305 INTO l_mol_line_id, l_mol_header_id, l_mol_uom_code,
1306 l_mol_qty, l_sec_mol_qty, --OPM Convergence
1307 l_mol_ref,l_mol_ref_code,l_mol_ref_id,
1308 l_mol_txn_src_id, l_mol_inspect_status;
1309
1310 -- MOLCON
1311 IF mol_cursor%NOTFOUND
1312 THEN
1313 EXIT;
1314 END IF;
1315
1316 IF (l_debug = 1)
1317 THEN
1318 print_debug (' l_mol_line_id :' || l_mol_line_id, 4);
1319 print_debug (' l_mol_header_id:' || l_mol_header_id, 4);
1320 print_debug (' l_mol_qty :' || l_mol_qty, 4);
1321 print_debug (' l_mol_uom_code :' || l_mol_uom_code, 4);
1322 print_debug (' l_uom_code :' || l_uom_code, 4);
1323 print_debug (' l_sec_mol_qty :' || l_sec_mol_qty, 4);
1324 print_debug (' l_mol_ref :' || l_mol_ref, 4);
1325 print_debug (' l_mol_ref_code :' || l_mol_ref_code, 4);
1326 print_debug (' l_mol_ref_id :' || l_mol_ref_id, 4);
1327 print_debug (' l_mol_txn_src_id :' || l_mol_txn_src_id, 4);
1328 print_debug (' l_mol_inspect_status :' || l_mol_inspect_status, 4);
1329 END IF;
1330
1331 -- If inspection uom is not same as move order uom, we convert
1332 IF (l_uom_code <> l_mol_uom_code)
1333 THEN
1334 l_mol_qty :=
1335 inv_rcv_cache.convert_qty
1336 (p_inventory_item_id => l_inventory_item_id,
1337 p_from_qty => l_mol_qty,
1338 p_from_uom_code => l_mol_uom_code,
1339 p_to_uom_code => l_uom_code
1340 );
1341 END IF;
1342
1343 IF (l_debug = 1)
1344 THEN
1345 print_debug ('new main process l_mol_qty ' || TO_CHAR (l_mol_qty),
1346 4);
1347 print_debug ('new main process l_mol_line_id ' || l_mol_line_id, 4);
1348 END IF;
1349
1350 -- l_remaing_mol_qty := min(l_remaining_qty, l_mol_qty)
1351 IF (l_mol_qty >= l_remaining_qty)
1352 THEN
1353 l_remaining_mol_qty := l_remaining_qty;
1354 l_sec_remaining_mol_qty := l_sec_remaining_qty; --OPM Convergence
1355 l_remaining_qty := 0;
1356 l_sec_remaining_qty := 0; --OPM Convergence
1357 ELSE
1358 l_remaining_mol_qty := l_mol_qty;
1359 l_sec_remaining_mol_qty := l_sec_mol_qty; --OPM Convergence
1360 l_remaining_qty := l_remaining_qty - l_mol_qty;
1361 l_sec_remaining_qty := l_sec_remaining_qty - l_sec_mol_qty;
1362 --OPM Convergence
1363 END IF;
1364
1365 IF (l_debug = 1)
1366 THEN
1367 print_debug
1368 ( 'new main process: '
1369 || ' l_remaining_mol_qty = min(l_mol_qty, l_remaining_qty) = '
1370 || l_remaining_mol_qty,
1371 4
1372 );
1373 END IF;
1374
1375 -- Open Rcv Transactions cursor
1376 -- MOLCON
1377 --IF (l_serial_number IS NOT NULL)
1378 -- 12942776
1379 -- Motive of the entire change in code is to get a table
1380 -- in which there will be one record per RT(rcv) for which we
1381 -- will insert RTI
1382 IF l_debug = 1
1383 THEN
1384 print_debug ('before opening l_serial_tbl ', 4);
1385 END IF;
1386
1387 IF (p_serial_tbl.COUNT <> 0) -- 12942776
1388 THEN
1389 IF l_debug = 1
1390 THEN
1391 print_debug ('before opening l_serial_tbl contents ', 4);
1392 END IF;
1393
1394 SELECT rs.rcv_transaction_id,
1395 rsh.receipt_source_code,
1396 rs.unit_of_measure,
1397 rs.secondary_unit_of_measure,
1398 COUNT (rs.rcv_transaction_id)
1399 BULK COLLECT INTO t_tmp_tbl
1400 FROM rcv_supply rs,
1401 rcv_serials_supply rss,
1402 rcv_transactions rt,
1403 rcv_shipment_headers rsh
1404 WHERE rs.rcv_transaction_id = rt.transaction_id
1405 and rs.rcv_transaction_id = nvl(l_mol_txn_src_id, rs.rcv_transaction_id)
1406 and decode(l_mol_ref_code,4,Decode(l_mol_ref, 'PO_LINE_LOCATION_ID',rs.po_line_location_id,'SHIPMENT_LINE_ID',rs.shipment_line_id), -- 13597819
1407 6,rs.shipment_line_id, -- 13597819 For Intransit
1408 7,rs.oe_order_line_id,
1409 8,rs.shipment_line_id) = l_mol_ref_id
1410 and decode(rt.INSPECTION_STATUS_CODE, 'ACCEPTED', 2,
1411 'REJECTED', 3,
1412 'NOT INSPECTED',1) = l_mol_inspect_status
1413 AND rsh.shipment_header_id = rs.shipment_header_id
1414 AND rs.supply_type_code = 'RECEIVING'
1415 AND rt.transaction_type <> 'UNORDERED'
1416 AND rt.routing_header_id = g_inspection_routing
1417 AND rs.item_id = l_inventory_item_id
1418 AND ( l_revision IS NULL -- Bug : 6139900
1419 OR NVL (rs.item_revision, '@#*') = NVL (l_revision, '@#*')
1420 )
1421 AND rs.lpn_id = l_lpn_id
1422 --l_lpn_id should always be NOT NULL
1423 AND rss.transaction_id = rs.rcv_transaction_id
1424 AND rss.serial_num IN (SELECT *
1425 FROM TABLE (list_serials))
1426 AND rss.supply_type_code = 'RECEIVING'
1427 AND NVL (rss.lot_num, '@#@') = NVL (l_lot_number, '@#@')
1428 GROUP BY rs.rcv_transaction_id,
1429 rsh.receipt_source_code,
1430 rs.unit_of_measure,
1431 rs.secondary_unit_of_measure
1432 order by 5 desc;
1433
1434 if t_tmp_tbl.count = 0 then
1435 IF l_debug = 1
1436 THEN
1437 print_debug ('jumping to mol_no_match ', 4);
1438 END IF;
1439 -- As no qty is satisfied by this mol, remaining qty shud be the reverted back
1440 l_remaining_qty := l_goto_remaining_qty;
1441 goto mol_no_match;
1442 end if;
1443
1444
1445 IF l_debug = 1
1446 THEN
1447 print_debug ('After bulk collect into t_tmp_tbl ', 4);
1448 END IF;
1449
1450 l_rtv_qty := 0;
1451
1452 FOR i IN t_tmp_tbl.FIRST .. t_tmp_tbl.LAST
1453 LOOP
1454 l_rtv_qty := l_rtv_qty + t_tmp_tbl (i).quantity;
1455 END LOOP;
1456 ELSIF (l_lot_number IS NOT NULL)
1457 THEN
1458 OPEN rtv_lot_cursor (l_inventory_item_id,
1459 l_revision,
1460 l_lpn_id,
1461 l_lot_number
1462 );
1463 ELSE
1464 OPEN rtv_van_cursor (l_inventory_item_id, l_revision, l_lpn_id);
1465 END IF;
1466
1467 -- One MOL can only be tied to 1 RT, which can only has 1 RS
1468 -- So, at least for J or higher, assume that this loop
1469 -- will only be executed once
1470 WHILE (l_remaining_mol_qty > 0)
1471 LOOP
1472 IF (l_debug = 1)
1473 THEN
1474 print_debug ( 'new Main process l_remaining_mol_qty : '
1475 || TO_CHAR (l_remaining_mol_qty),
1476 4
1477 );
1478 END IF;
1479
1480 -- MOLCON
1481 -- LOOP FROM THE FETCH HERE FOR RTV CURSOR
1482 -- AS THERE MAY BE MULTIPLE RT ROWS FOR SINGLE MOL LINE NOW
1483 -- RTV_CURSOR IF NOTHING IS FOUND AND STILL REMAINING QTY EXISTS FAIL THE TXN
1484 -- ALSO FOR THE CONDITION for L_RTV_QTY > 0 , THE ELSE PART IS NOT NEEDED
1485 -- AS IF THERE ARE MULTIPLE RT's FETCHED THEN THERE ARE PARENT RECEIPT TXN's
1486 -- FOR WHICH SOME RTI IS ALREADY CREATED FOR INSPECTION TXN.
1487 -- MOLCON
1488 IF p_serial_tbl.COUNT > 0
1489 THEN
1490 print_debug ( 'dont remove this if though unnecesary as '
1491 || 'there is a chance that plain cursor would open',
1492 4
1493 );
1494 ELSIF (l_lot_number IS NOT NULL)
1495 THEN
1496 FETCH rtv_lot_cursor
1497 INTO l_rcv_transaction_id, l_receipt_source_code, l_rtv_uom,
1498 l_rtv_sec_uom --OPM Convergence
1499 ,
1500 l_rls_qty;
1501
1502 IF rtv_lot_cursor%NOTFOUND
1503 THEN
1504 -- MOLCON
1505 -- CHECK FOR ERROR HERE
1506 IF l_remaining_mol_qty > 0
1507 THEN
1508 fnd_message.set_name ('INV', 'INV_RCV_NO_ROWS');
1509 fnd_msg_pub.ADD;
1510 RAISE fnd_api.g_exc_error;
1511 END IF;
1512
1513 -- MOLCON
1514 EXIT;
1515 END IF;
1516 ELSE
1517 FETCH rtv_van_cursor
1518 INTO l_rcv_transaction_id, l_receipt_source_code, l_rtv_uom,
1519 l_rtv_sec_uom; --OPM Convergence
1520
1521 IF rtv_van_cursor%NOTFOUND
1522 THEN
1523 -- MOLCON
1524 -- CHECK FOR ERROR HERE
1525 IF l_remaining_mol_qty > 0
1526 THEN
1527 fnd_message.set_name ('INV', 'INV_RCV_NO_ROWS');
1528 fnd_msg_pub.ADD;
1529 RAISE fnd_api.g_exc_error;
1530 END IF;
1531
1532 -- MOLCON
1533 EXIT;
1534 END IF;
1535 END IF; --END IF (l_serial_number IS NOT NULL) THEN
1536
1537 -- start from here 12942776
1538 IF (l_debug = 1)
1539 THEN
1540 print_debug ( 'l_rcv_transaction_id:'
1541 || l_rcv_transaction_id
1542 || ' l_receipt_source_code:'
1543 || l_receipt_source_code
1544 || ' l_rtv_uom:'
1545 || l_rtv_uom
1546 || ' l_rtv_sec_uom:'
1547 || l_rtv_sec_uom
1548 || ' l_rls_qty:'
1549 || l_rls_qty,
1550 4
1551 );
1552 END IF;
1553
1554 -- 12942776
1555 IF (p_serial_tbl.COUNT > 0)
1556 THEN
1557 -- we wont initialize the qty here... there is still way to go
1558 IF l_debug = 1
1559 THEN
1560 print_debug
1561 ( 'dont remove this if though unnecesary'
1562 || 'as there is a chance that plain cursor would open',
1563 4
1564 );
1565 END IF;
1566 ELSIF (l_lot_number IS NOT NULL)
1567 THEN
1568 BEGIN
1569 SELECT SUM (NVL (mtli.primary_quantity, 0))
1570 INTO l_processed_lot_prim_qty
1571 FROM mtl_transaction_lots_interface mtli,
1572 rcv_transactions_interface rti
1573 WHERE mtli.product_code = 'RCV'
1574 AND mtli.product_transaction_id =
1575 rti.interface_transaction_id
1576 AND mtli.lot_number = l_lot_number
1577 AND rti.parent_transaction_id = l_rcv_transaction_id
1578 AND rti.transaction_status_code = 'PENDING'
1579 AND rti.processing_status_code <> 'ERROR';
1580
1581 IF (l_processed_lot_prim_qty IS NULL)
1582 THEN
1583 l_processed_lot_prim_qty := 0;
1584 END IF;
1585 EXCEPTION
1586 WHEN OTHERS
1587 THEN
1588 l_processed_lot_prim_qty := 0;
1589 END;
1590
1591 IF (l_debug = 1)
1592 THEN
1593 print_debug ( 'l_processed_lot_prim_qty: '
1594 || l_processed_lot_prim_qty,
1595 4
1596 );
1597 END IF;
1598
1599 -- Modified for bug 6688055
1600 -- Convert l_rtv_uom(Each) into l_rtv_uom_code(Ea)
1601 SELECT uom_code
1602 INTO l_rtv_uom_code
1603 FROM mtl_units_of_measure
1604 WHERE unit_of_measure = l_rtv_uom;
1605
1606 IF (l_debug = 1)
1607 THEN
1608 print_debug ( 'l_rtv_uom_code: '
1609 || l_rtv_uom_code
1610 || ' l_uom_code : '
1611 || l_uom_code,
1612 4
1613 );
1614 END IF;
1615
1616 IF (l_uom_code <> l_rtv_uom_code)
1617 THEN
1618 l_cnv_rls_qty :=
1619 inv_rcv_cache.convert_qty
1620 (p_inventory_item_id => l_inventory_item_id,
1621 p_from_qty => l_rls_qty,
1622 p_from_uom_code => l_rtv_uom_code,
1623 p_to_uom_code => l_uom_code
1624 );
1625 ELSE
1626 l_cnv_rls_qty := l_rls_qty;
1627 END IF;
1628
1629 IF (l_debug = 1)
1630 THEN
1631 print_debug ('l_cnv_rls_qty : ' || l_cnv_rls_qty, 4);
1632 END IF;
1633
1634 IF (l_primary_uom_code <> l_uom_code)
1635 THEN
1636 l_rtv_qty :=
1637 l_cnv_rls_qty
1638 - inv_rcv_cache.convert_qty
1639 (p_inventory_item_id => l_inventory_item_id,
1640 p_from_qty => l_processed_lot_prim_qty,
1641 p_from_uom_code => l_primary_uom_code,
1642 p_to_uom_code => l_uom_code
1643 );
1644 ELSE
1645 l_rtv_qty := l_cnv_rls_qty - l_processed_lot_prim_qty;
1646 END IF;
1647 -- Modification for bug 6688055 ended
1648 ELSE
1649 rcv_quantities_s.get_available_quantity ('INSPECT',
1650 l_rcv_transaction_id,
1651 l_receipt_source_code,
1652 NULL,
1653 l_rcv_transaction_id,
1654 NULL,
1655 l_rtv_qty,
1656 l_tolerable_qty,
1657 l_rtv_uom
1658 );
1659
1660 IF (l_debug = 1)
1661 THEN
1662 print_debug ( 'new main process l_rtv_qty : '
1663 || TO_CHAR (l_rtv_qty),
1664 4
1665 );
1666 END IF;
1667
1668 IF (l_rtv_qty > 0)
1669 THEN
1670 -- Purchasing/receiving uses unit of measure (Each)
1671 -- rather than uom code(Ea) and hence the following..
1672 -- Convert l_rtv_uom(Each) into l_rtv_uom_code(Ea)
1673 SELECT uom_code
1674 INTO l_rtv_uom_code
1675 FROM mtl_units_of_measure
1676 WHERE unit_of_measure = l_rtv_uom;
1677
1678 -- If inspection uom is not same as receipt uom, convert
1679 IF (l_uom_code <> l_rtv_uom_code)
1680 THEN
1681 l_rtv_qty :=
1682 inv_rcv_cache.convert_qty
1683 (p_inventory_item_id => l_inventory_item_id,
1684 p_from_qty => l_rtv_qty,
1685 p_from_uom_code => l_rtv_uom_code,
1686 p_to_uom_code => l_uom_code
1687 );
1688 END IF;
1689 END IF;
1690 END IF;
1691
1692 IF (l_rtv_qty > 0)
1693 THEN
1694 IF l_rtv_qty >= l_remaining_mol_qty
1695 THEN
1696 IF (l_debug = 1)
1697 THEN
1698 print_debug
1699 ('new main_process: l_rtv >= l_remaining_mol_qty',
1700 4
1701 );
1702 END IF;
1703
1704 l_rtv_qty := l_remaining_mol_qty;
1705 l_remaining_mol_qty := 0;
1706 ELSE
1707 IF (l_debug = 1)
1708 THEN
1709 print_debug
1710 ('new main_process: l_rtv < l_remaining_mol_qty',
1711 4
1712 );
1713 END IF;
1714
1715 l_remaining_mol_qty := l_remaining_mol_qty - l_rtv_qty;
1716 END IF;
1717
1718 IF (l_debug = 1)
1719 THEN
1720 print_debug
1721 ( 'new main_process:'
1722 || 'l_rtv_qty = min(available qty, l_remaining_mol_qty) = '
1723 || l_rtv_qty,
1724 4
1725 );
1726 END IF;
1727
1728 -- If required convert into primary unit of measure
1729 IF (l_uom_code <> l_primary_uom_code)
1730 THEN
1731 l_primary_qty :=
1732 inv_rcv_cache.convert_qty
1733 (p_inventory_item_id => l_inventory_item_id,
1734 p_from_qty => l_rtv_qty,
1735 p_from_uom_code => l_uom_code,
1736 p_to_uom_code => l_primary_uom_code
1737 );
1738 ELSE
1739 l_primary_qty := l_rtv_qty;
1740 END IF;
1741
1742 IF l_inspection_status = g_accept
1743 THEN
1744 IF (l_accept_lpn_id > 0)
1745 THEN
1746 l_rti_lpn_id := l_lpn_id;
1747 l_rti_transfer_lpn_id := l_accept_lpn_id;
1748 ELSE
1749 l_rti_lpn_id := l_lpn_id;
1750 END IF;
1751 ELSE
1752 IF (l_reject_lpn_id > 0)
1753 THEN
1754 l_rti_lpn_id := l_lpn_id;
1755 l_rti_transfer_lpn_id := l_reject_lpn_id;
1756 ELSE
1757 l_rti_lpn_id := l_lpn_id;
1758 END IF;
1759 END IF;
1760
1761 IF (l_debug = 1)
1762 THEN
1763 print_debug
1764 ('new main_process : inside RTV cursor Loop, before split_mo',
1765 4
1766 );
1767 print_debug (' l_rtv_qty =======> ' || l_rtv_qty, 4);
1768 print_debug ( ' l_remaining_mol_qty => '
1769 || l_remaining_mol_qty,
1770 4
1771 );
1772 print_debug (' l_primary_qty ===> ' || l_primary_qty, 4);
1773 print_debug (' l_mol_qty =======> ' || l_mol_qty, 4);
1774 print_debug (' l_remaining_qty => ' || l_remaining_qty, 4);
1775 print_debug (' l_mol_line_id ===> ' || l_mol_line_id, 4);
1776 END IF;
1777
1778 ---------
1779 -- call a new procedure to take care of inserting
1780 -- new MTRL, RTI, MSNI, MTLI
1781 -- 12942776
1782 IF p_serial_tbl.COUNT <> 0
1783 THEN
1784 FOR i IN t_tmp_tbl.FIRST .. t_tmp_tbl.LAST
1785 LOOP
1786 l_rcv_transaction_id := t_tmp_tbl (i).rcv_transaction_id;
1787 l_ser_rtv_qty := t_tmp_tbl (i).quantity;
1788 l_rtv_qty := l_rtv_qty - l_ser_rtv_qty;
1789
1790 -- need to check if l_rtv_qty is always >0 else
1791 -- need to quit this loop as mol has no more qty.
1792 IF l_rtv_qty < 0
1793 THEN
1794 EXIT; -- quit the loop
1795 END IF;
1796
1797 SELECT uom_code
1798 INTO l_rtv_uom_code
1799 FROM mtl_units_of_measure
1800 WHERE unit_of_measure = t_tmp_tbl (i).unit_of_measure;
1801
1802 IF l_uom_code <> l_rtv_uom_code
1803 THEN
1804 IF (l_uom_code <> l_rtv_uom_code)
1805 THEN
1806 l_ser_rtv_qty :=
1807 inv_rcv_cache.convert_qty
1808 (p_inventory_item_id => l_inventory_item_id,
1809 p_from_qty => l_ser_rtv_qty,
1810 p_from_uom_code => l_rtv_uom_code,
1811 p_to_uom_code => l_uom_code
1812 );
1813 END IF;
1814 END IF;
1815
1816 IF (l_uom_code <> l_primary_uom_code)
1817 THEN
1818 l_primary_qty :=
1819 inv_rcv_cache.convert_qty
1820 (p_inventory_item_id => l_inventory_item_id,
1821 p_from_qty => l_ser_rtv_qty,
1822 p_from_uom_code => l_uom_code,
1823 p_to_uom_code => l_primary_uom_code
1824 );
1825 ELSE
1826 l_primary_qty := l_ser_rtv_qty;
1827 END IF;
1828
1829 --l_rtv_qty, l_rtv_sec_qty, l_mol_qty, l_primary_qty,
1830 split_mol_create_rti (l_rcv_transaction_id,
1831 l_ser_rtv_qty,
1832 l_rtv_sec_qty,
1833 l_mol_qty,
1834 l_primary_qty,
1835 l_mol_line_id,
1836 l_inventory_item_id,
1837 l_uom,
1838 l_uom_code,
1839 l_inspection_code,
1840 l_quality_code,
1841 l_transaction_date,
1842 l_transaction_type,
1843 l_vendor_lot,
1844 l_reason_id,
1845 p_organization_id,
1846 p_lot_number,
1847 l_comments,
1848 l_attribute_category,
1849 l_attribute1,
1850 l_attribute2,
1851 l_attribute3,
1852 l_attribute4,
1853 l_attribute5,
1854 l_attribute6,
1855 l_attribute7,
1856 l_attribute8,
1857 l_attribute9,
1858 l_attribute10,
1859 l_attribute11,
1860 l_attribute12,
1861 l_attribute13,
1862 l_attribute14,
1863 l_attribute15,
1864 l_qa_collection_id,
1865 l_rti_lpn_id,
1866 l_rti_transfer_lpn_id,
1867 l_sec_uom
1868 );
1869 END LOOP;
1870 ELSE
1871 split_mol_create_rti (l_rcv_transaction_id,
1872 l_rtv_qty,
1873 l_rtv_sec_qty,
1874 l_mol_qty,
1875 l_primary_qty,
1876 l_mol_line_id,
1877 l_inventory_item_id,
1878 l_uom,
1879 l_uom_code,
1880 l_inspection_code,
1881 l_quality_code,
1882 l_transaction_date,
1883 l_transaction_type,
1884 l_vendor_lot,
1885 l_reason_id,
1886 p_organization_id,
1887 p_lot_number,
1888 l_comments,
1889 l_attribute_category,
1890 l_attribute1,
1891 l_attribute2,
1892 l_attribute3,
1893 l_attribute4,
1894 l_attribute5,
1895 l_attribute6,
1896 l_attribute7,
1897 l_attribute8,
1898 l_attribute9,
1899 l_attribute10,
1900 l_attribute11,
1901 l_attribute12,
1902 l_attribute13,
1903 l_attribute14,
1904 l_attribute15,
1905 l_qa_collection_id,
1906 l_rti_lpn_id,
1907 l_rti_transfer_lpn_id,
1908 l_sec_uom
1909 );
1910 END IF;
1911 ELSE
1912 IF (l_debug = 1)
1913 THEN
1914 print_debug
1915 ('new main_process: There is no quantity available to Inspect: ',
1916 4
1917 );
1918 END IF;
1919 END IF; --END IF IF (l_rtv_qty > 0)
1920 END LOOP;
1921
1922 /* ctrl shud return here if there are no serials being fetched for this
1923 mol with this inspection status. */
1924 <<mol_no_match>>
1925
1926 IF (rtv_van_cursor%ISOPEN)
1927 THEN
1928 CLOSE rtv_van_cursor;
1929 END IF;
1930
1931 IF (rtv_lot_cursor%ISOPEN)
1932 THEN
1933 CLOSE rtv_lot_cursor;
1934 END IF;
1935
1936 END LOOP; -- WHILE(l_remaining_qty > 0) LOOP
1937
1938 CLOSE mol_cursor;
1939
1940 IF (l_remaining_qty > 0)
1941 THEN
1942 IF (l_debug = 1)
1943 THEN
1944 print_debug
1945 ('new main_process: No more MOL, but remaining qty still exists',
1946 4
1947 );
1948 END IF;
1949
1950 RAISE fnd_api.g_exc_error;
1951 END IF;
1952 EXCEPTION
1953 WHEN fnd_api.g_exc_error
1954 THEN
1955 IF (l_debug = 1)
1956 THEN
1957 print_debug
1958 ( 'Exception raised in new main_process at progress: '
1959 || l_progress,
1960 4
1961 );
1962 END IF;
1963
1964 ROLLBACK TO inspect_main_sp;
1965 x_return_status := fnd_api.g_ret_sts_error;
1966 -- Get message count and data
1967 fnd_msg_pub.count_and_get (p_count => x_msg_count,
1968 p_data => x_msg_data);
1969
1970 IF (mol_cursor%ISOPEN)
1971 THEN
1972 CLOSE mol_cursor;
1973 END IF;
1974
1975 IF (rtv_van_cursor%ISOPEN)
1976 THEN
1977 CLOSE rtv_van_cursor;
1978 END IF;
1979
1980 IF (rtv_lot_cursor%ISOPEN)
1981 THEN
1982 CLOSE rtv_lot_cursor;
1983 END IF;
1984 WHEN fnd_api.g_exc_unexpected_error
1985 THEN
1986 IF (l_debug = 1)
1987 THEN
1988 print_debug
1989 ( 'Exception raised in new main_process at progress: '
1990 || l_progress,
1991 4
1992 );
1993 END IF;
1994
1995 ROLLBACK TO inspect_main_sp;
1996 x_return_status := fnd_api.g_ret_sts_unexp_error;
1997 -- Get message count and data
1998 fnd_msg_pub.count_and_get (p_count => x_msg_count,
1999 p_data => x_msg_data);
2000
2001 IF (mol_cursor%ISOPEN)
2002 THEN
2003 CLOSE mol_cursor;
2004 END IF;
2005
2006 IF (rtv_van_cursor%ISOPEN)
2007 THEN
2008 CLOSE rtv_van_cursor;
2009 END IF;
2010
2011 IF (rtv_lot_cursor%ISOPEN)
2012 THEN
2013 CLOSE rtv_lot_cursor;
2014 END IF;
2015 WHEN OTHERS
2016 THEN
2017 IF (l_debug = 1)
2018 THEN
2019 print_debug
2020 ( 'Exception raised in new main_process at progress: '
2021 || l_progress,
2022 4
2023 );
2024 print_debug ('SQLCODE: ' || SQLCODE, 4);
2025 print_debug ('SQLERRM ' || SQLERRM, 4);
2026 END IF;
2027
2028 ROLLBACK TO inspect_main_sp;
2029 x_return_status := fnd_api.g_ret_sts_unexp_error;
2030
2031 --
2032 IF fnd_msg_pub.check_msg_level (fnd_msg_pub.g_msg_lvl_unexp_error)
2033 THEN
2034 fnd_msg_pub.add_exc_msg (g_pkg_name, 'main_process');
2035 END IF;
2036
2037 -- Get message count and data
2038 fnd_msg_pub.count_and_get (p_count => x_msg_count,
2039 p_data => x_msg_data);
2040
2041 IF (mol_cursor%ISOPEN)
2042 THEN
2043 CLOSE mol_cursor;
2044 END IF;
2045
2046 IF (rtv_van_cursor%ISOPEN)
2047 THEN
2048 CLOSE rtv_van_cursor;
2049 END IF;
2050
2051 IF (rtv_lot_cursor%ISOPEN)
2052 THEN
2053 CLOSE rtv_lot_cursor;
2054 END IF;
2055 END main_process;
2056
2057 PROCEDURE main_process(
2058 x_return_status OUT NOCOPY VARCHAR2
2059 , x_msg_count OUT NOCOPY NUMBER
2060 , x_msg_data OUT NOCOPY VARCHAR2
2061 , p_inventory_item_id IN NUMBER
2062 , p_organization_id IN NUMBER
2063 , p_lpn_id IN NUMBER
2064 , p_revision IN VARCHAR2
2065 , p_lot_number IN VARCHAR2
2066 , p_uom_code IN VARCHAR2
2067 , p_quantity IN NUMBER
2068 , p_inspection_code IN VARCHAR2
2069 , p_quality_code IN VARCHAR2
2070 , p_transaction_type IN VARCHAR2
2071 , p_reason_id IN NUMBER
2072 , p_serial_number IN VARCHAR2
2073 , p_accept_lpn_id IN NUMBER
2074 , p_reject_lpn_id IN NUMBER
2075 , p_transaction_date IN DATE DEFAULT SYSDATE
2076 , p_qa_collection_id IN NUMBER DEFAULT NULL
2077 , p_vendor_lot IN VARCHAR2 DEFAULT NULL
2078 , p_comments IN VARCHAR2 DEFAULT NULL
2079 , p_attribute_category IN VARCHAR2 DEFAULT NULL
2080 , p_attribute1 IN VARCHAR2 DEFAULT NULL
2081 , p_attribute2 IN VARCHAR2 DEFAULT NULL
2082 , p_attribute3 IN VARCHAR2 DEFAULT NULL
2083 , p_attribute4 IN VARCHAR2 DEFAULT NULL
2084 , p_attribute5 IN VARCHAR2 DEFAULT NULL
2085 , p_attribute6 IN VARCHAR2 DEFAULT NULL
2086 , p_attribute7 IN VARCHAR2 DEFAULT NULL
2087 , p_attribute8 IN VARCHAR2 DEFAULT NULL
2088 , p_attribute9 IN VARCHAR2 DEFAULT NULL
2089 , p_attribute10 IN VARCHAR2 DEFAULT NULL
2090 , p_attribute11 IN VARCHAR2 DEFAULT NULL
2091 , p_attribute12 IN VARCHAR2 DEFAULT NULL
2092 , p_attribute13 IN VARCHAR2 DEFAULT NULL
2093 , p_attribute14 IN VARCHAR2 DEFAULT NULL
2094 , p_attribute15 IN VARCHAR2 DEFAULT NULL
2095 , p_secondary_qty IN NUMBER DEFAULT NULL) --OPM Convergence
2096 IS
2097 l_inventory_item_id NUMBER := p_inventory_item_id;
2098 l_organization_id NUMBER := p_organization_id;
2099 l_lpn_id NUMBER := p_lpn_id;
2100 l_revision VARCHAR2(10) := p_revision;
2101 -- Increased lot size to 80 Char - Mercy Thomas - B4625329
2102 l_lot_number VARCHAR2(80) := p_lot_number;
2103 l_uom_code VARCHAR2(5) := p_uom_code;
2104 l_uom VARCHAR2(30);
2105 l_quantity NUMBER := p_quantity;
2106 l_serial_number VARCHAR2(30) := p_serial_number;
2107 l_accept_lpn_id NUMBER := p_accept_lpn_id;
2108 l_reject_lpn_id NUMBER := p_reject_lpn_id;
2109 l_rti_lpn_id NUMBER;
2110 l_rti_transfer_lpn_id NUMBER;
2111 l_inspection_code VARCHAR2(25) := p_inspection_code;
2112 l_quality_code VARCHAR2(25) := p_quality_code;
2113 l_transaction_date DATE := p_transaction_date;
2114 l_comments VARCHAR2(240) := p_comments;
2115 l_attribute_category VARCHAR2(30) := p_attribute_category;
2116 l_attribute1 VARCHAR2(150) := p_attribute1;
2117 l_attribute2 VARCHAR2(150) := p_attribute2;
2118 l_attribute3 VARCHAR2(150) := p_attribute3;
2119 l_attribute4 VARCHAR2(150) := p_attribute4;
2120 l_attribute5 VARCHAR2(150) := p_attribute5;
2121 l_attribute6 VARCHAR2(150) := p_attribute6;
2122 l_attribute7 VARCHAR2(150) := p_attribute7;
2123 l_attribute8 VARCHAR2(150) := p_attribute8;
2124 l_attribute9 VARCHAR2(150) := p_attribute9;
2125 l_attribute10 VARCHAR2(150) := p_attribute10;
2126 l_attribute11 VARCHAR2(150) := p_attribute11;
2127 l_attribute12 VARCHAR2(150) := p_attribute12;
2128 l_attribute13 VARCHAR2(150) := p_attribute13;
2129 l_attribute14 VARCHAR2(150) := p_attribute14;
2130 l_attribute15 VARCHAR2(150) := p_attribute15;
2131 l_transaction_type VARCHAR2(30) := p_transaction_type;
2132 l_vendor_lot VARCHAR2(30) := p_vendor_lot;
2133 l_reason_id NUMBER := p_reason_id;
2134 l_qa_collection_id NUMBER := p_qa_collection_id;
2135 l_primary_qty NUMBER;
2136 l_primary_uom_code VARCHAR2(5);
2137 l_mol_line_id NUMBER;
2138 l_mol_new_line_id NUMBER;
2139 l_mol_header_id NUMBER;
2140 l_mol_uom_code VARCHAR2(5);
2141 l_mol_qty NUMBER;
2142 l_rcv_transaction_id NUMBER;
2143 l_rtv_qty NUMBER;
2144 l_rls_qty NUMBER;
2145 l_cnv_rls_qty NUMBER; -- Added for bug 6688055
2146 l_rtv_uom VARCHAR2(25); /* Each */
2147 l_rtv_uom_code VARCHAR2(5); /* Ea */
2148 l_receipt_source_code VARCHAR2(25);
2149 l_tolerable_qty NUMBER;
2150 l_remaining_qty NUMBER;
2151 l_remaining_mol_qty NUMBER;
2152 l_inspection_status NUMBER;
2153 l_return_status VARCHAR2(5);
2154 l_msg_count NUMBER;
2155 l_msg_data VARCHAR2(1000);
2156 l_rec_count NUMBER;
2157
2158 l_secondary_qty NUMBER := p_secondary_qty; --OPM COnvergence
2159 l_remaining_sec_qty NUMBER; --OPM Convergence
2160 l_rtv_sec_uom VARCHAR2(25);--OPM Convergence
2161 l_sec_uom_code VARCHAR2(3);--OPM Convergence
2162 l_sec_uom VARCHAR2(25);--OPM Convergence
2163 l_sec_mol_qty NUMBER;--OPM COnvergence
2164 l_sec_remaining_mol_qty NUMBER;--OPM Convergence
2165 l_sec_remaining_qty NUMBER; --OPM Convergence
2166 l_rtv_sec_qty NUMBER;--OPM Convergence
2167 l_processed_lot_prim_qty NUMBER;
2168
2169 TYPE number_tb_tp IS TABLE OF NUMBER
2170 INDEX BY BINARY_INTEGER;
2171
2172 l_mmtt_ids number_tb_tp;
2173 l_primary_quantities number_tb_tp;
2174 l_transaction_quantities number_tb_tp;
2175 L_SECONDARY_TXN_QUANTITIES number_tb_tp; --OPM Convergence
2176
2177 -- Added 12942776
2178 l_mol_ref varchar2(30);
2179 l_mol_ref_id number;
2180 l_mol_ref_code number;
2181 l_mol_txn_src_id number;
2182 l_mol_inspect_status number;
2183
2184
2185 /* FP-J Lot/Serial Support Enhancement
2186 * If WMS and PO J are installed, then the move order line quantity updates
2187 * will be handled by the receiving TM. The logic for MO handling would be:
2188 * If MOL quantity > Inspection Quantity Then
2189 * Do not update quantity. Set the process_flag to 2 so that
2190 * this line does not get picked up again.
2191 * Split the move order line to create one for the uninspected quantity
2192 * Else
2193 * Do not update quantity. Set the process_flag to 2 so that this line
2194 * does not get picked up again.
2195 * End If
2196 * If either WMS or PO J are not installed, retain the original processing
2197 * So am opening the cursor with a new parameter k_wms_po_j_higher.
2198 * If this flag is set, then filter the move order lines on process_flag (=1)
2199 * If this flag is not set, then filter lines on quantity
2200 */
2201 CURSOR mol_cursor(
2202 k_inventory_item_id NUMBER
2203 , k_organization_id NUMBER
2204 , k_lpn_id NUMBER
2205 , k_revision VARCHAR2
2206 , k_lot_number VARCHAR2
2207 ) IS
2208 SELECT line_id
2209 , header_id
2210 , uom_code
2211 , quantity - NVL(quantity_delivered,0)
2212 , secondary_quantity - NVL(secondary_quantity_delivered,0) --OPM Convergence
2213 , REFERENCE, REFERENCE_TYPE_CODE,REFERENCE_ID
2214 , TXN_SOURCE_ID, inspection_status,
2215 PRIMARY_QUANTITY--Bug 13484877
2216 FROM mtl_txn_request_lines
2217 WHERE inventory_item_id = k_inventory_item_id
2218 AND organization_id = k_organization_id
2219 AND lpn_id = k_lpn_id
2220 AND (revision = k_revision
2221 OR revision IS NULL
2222 AND p_revision IS NULL)
2223 AND (lot_number = k_lot_number
2224 OR lot_number IS NULL
2225 AND p_lot_number IS NULL)
2226 AND inspection_status is not null --8405606
2227 AND line_status = 7
2228 AND quantity - Nvl(quantity_delivered,0) > 0
2229 AND wms_process_flag = 1
2230 order by inspection_status -- added 12942776
2231 ;
2232
2233 -- MOLCON
2234 --bug 8405606 removed the condition for rt.inspection_status_code = 'NOT INSPECTED'
2235 CURSOR rtv_van_cursor(k_item_id NUMBER,
2236 k_item_revision VARCHAR2,
2237 k_lpn_id NUMBER,
2238 k_reference VARCHAR2, --BugFix:14554730
2239 k_reference_id NUMBER
2240 ) IS
2241 SELECT rs.rcv_transaction_id
2242 , rsh.receipt_source_code
2243 , rs.unit_of_measure
2244 , rs.secondary_unit_of_measure --OPM Convergence
2245 FROM rcv_supply rs, rcv_transactions rt, rcv_shipment_headers rsh,rcv_shipment_lines rsl --BugFix:14554730
2246 WHERE rs.rcv_transaction_id = rt.transaction_id
2247 AND rsl.shipment_line_id = rs.shipment_line_id --BugFix:14554730
2248 AND rsh.shipment_header_id = rs.shipment_header_id
2249 AND ((rsl.shipment_line_id = k_reference_id AND k_reference='SHIPMENT_LINE_ID') --BugFix:14554730
2250 OR
2251 (rsl.po_line_location_id = k_reference_id AND k_reference='PO_LINE_LOCATION_ID')
2252 OR
2253 (rsl.oe_order_line_id = k_reference_id AND k_reference='ORDER_LINE_ID')
2254 )
2255 AND rs.supply_type_code = 'RECEIVING'
2256 AND rt.transaction_type <> 'UNORDERED'
2257 AND rt.routing_header_id = g_inspection_routing
2258 AND rs.item_id = k_item_id
2259 AND (k_item_revision IS NULL -- Bug : 6139900
2260 OR nvl(rs.item_revision,'@#*') = nvl(k_item_revision,'@#*'))
2261 AND rs.lpn_id = k_lpn_id; --l_lpn_id should always be NOT NULL
2262
2263 CURSOR rtv_lot_cursor(k_item_id NUMBER,
2264 k_item_revision VARCHAR2,
2265 k_lpn_id NUMBER,
2266 k_lot_number VARCHAR2) IS
2267 SELECT rs.rcv_transaction_id
2268 , rsh.receipt_source_code
2269 , rs.unit_of_measure
2270 , rs.secondary_unit_of_measure --OPM Convergence
2271 , Sum(rls.quantity) quantity --Bug 14117094
2272 FROM rcv_supply rs, rcv_lots_supply rls, rcv_transactions rt, rcv_shipment_headers rsh
2273 WHERE rs.rcv_transaction_id = rt.transaction_id
2274 AND rsh.shipment_header_id = rs.shipment_header_id
2275 AND rs.supply_type_code = 'RECEIVING'
2276 AND rt.transaction_type <> 'UNORDERED'
2277 AND rt.routing_header_id = g_inspection_routing
2278 AND rs.item_id = k_item_id
2279 AND (k_item_revision IS NULL -- Bug : 6139900
2280 OR nvl(rs.item_revision,'@#*') = nvl(k_item_revision,'@#*'))
2281 AND rs.lpn_id = k_lpn_id --l_lpn_id should always be NOT NULL
2282 AND rls.transaction_id = rs.rcv_transaction_id
2283 AND rls.lot_num = k_lot_number
2284 GROUP BY rs.rcv_transaction_id, rsh.receipt_source_code, rs.unit_of_measure, rs.secondary_unit_of_measure; --Bug 14117094
2285
2286
2287 /* 12942776 - the extra conditions added are to fetch the mol with the correct
2288 inspection status, so that it does not fail in mo consolidation */
2289 CURSOR rtv_serial_cursor(k_item_id NUMBER,
2290 k_item_revision VARCHAR2,
2291 k_lpn_id NUMBER,
2292 k_serial_number VARCHAR2,
2293 k_lot_number VARCHAR2) IS
2294 SELECT rs.rcv_transaction_id
2295 , rsh.receipt_source_code
2296 , rs.unit_of_measure
2297 , rs.secondary_unit_of_measure --OPM Convergence
2298 FROM rcv_supply rs, rcv_serials_supply rss, rcv_transactions rt, rcv_shipment_headers rsh
2299 WHERE rs.rcv_transaction_id = rt.transaction_id
2300 -- added 12942776 start
2301 and rs.rcv_transaction_id = nvl(l_mol_txn_src_id, rs.rcv_transaction_id)
2302 AND DECODE(l_mol_ref_code,4, Decode(l_mol_ref, 'PO_LINE_LOCATION_ID' , rs.po_line_location_id,
2303 'SHIPMENT_LINE_ID' , rs.shipment_line_id),
2304 6, rs.shipment_line_id,
2305 7, rs.oe_order_line_id,
2306 8, rs.shipment_line_id) = l_mol_ref_id --Modified the condition for 14109506. Added the above Decode and condition for intransit
2307 and decode(rt.INSPECTION_STATUS_CODE, 'ACCEPTED', 2,
2308 'REJECTED', 3,
2309 'NOT INSPECTED',1) = l_mol_inspect_status
2310 -- added 12942776 end
2311 AND rsh.shipment_header_id = rs.shipment_header_id
2312 AND rs.supply_type_code = 'RECEIVING'
2313 AND rt.transaction_type <> 'UNORDERED'
2314 AND rt.routing_header_id = g_inspection_routing
2315 AND rs.item_id = k_item_id
2316 AND (k_item_revision IS NULL -- Bug : 6139900
2317 OR nvl(rs.item_revision,'@#*') = nvl(k_item_revision,'@#*'))
2318 AND rs.lpn_id = k_lpn_id --l_lpn_id should always be NOT NULL
2319 AND rss.transaction_id = rs.rcv_transaction_id
2320 AND rss.serial_num = k_serial_number
2321 AND rss.supply_type_code = 'RECEIVING'
2322 AND Nvl(rss.lot_num,'@#@') = Nvl(k_lot_number,'@#@');
2323 -- MOLCON
2324
2325 l_debug NUMBER := NVL(fnd_profile.VALUE('INV_DEBUG_TRACE'), 0);
2326 --New variables for Lot/Serial Support
2327 l_lot_control_code NUMBER;
2328 l_serial_control_code NUMBER;
2329 l_mo_splt_tb inv_rcv_integration_apis.mo_in_tb_tp;
2330 l_txn_qty_to_split NUMBER;
2331 l_primary_qty_to_split NUMBER;
2332 l_new_mol_id NUMBER;
2333 l_split_line_id NUMBER; --for debug
2334 l_progress VARCHAR2(10) := '0';
2335 l_mol_prim_qty NUMBER;--Bug 13484877
2336
2337 BEGIN
2338 x_return_status := fnd_api.g_ret_sts_success;
2339
2340 IF (l_debug = 1) THEN
2341 print_debug('main_process: Just entering main_process', 4);
2342 print_debug('p_inventory_item_id => '||p_inventory_item_id,4);
2343 print_debug('p_organization_id => '||p_organization_id,4);
2344 print_debug('p_lpn_id => '||p_lpn_id,4);
2345 print_debug('p_revision => '||p_revision,4);
2346 print_debug('p_lot_number => '||p_lot_number,4);
2347 print_debug('p_uom_code => '||p_uom_code,4);
2348 print_debug('p_quantity => '||p_quantity,4);
2349 print_debug('p_serial_number => '||p_serial_number,4);
2350 print_debug('p_inspection_code => '||p_inspection_code,4);
2351 END IF;
2352
2353 --First check if the transaction date satisfies the validation.
2354 --If the transaction date is invalid then error out the transaction
2355 IF inv_rcv_common_apis.g_po_startup_value.sob_id IS NULL THEN
2356 --BUG 3444196: Used the HR view instead for performance reasons
2357 SELECT TO_NUMBER(hoi.org_information1)
2358 INTO inv_rcv_common_apis.g_po_startup_value.sob_id
2359 FROM hr_organization_information hoi
2360 WHERE hoi.organization_id = p_organization_id
2361 AND (hoi.org_information_context || '') = 'Accounting Information' ;
2362 END IF;
2363
2364 inv_rcv_common_apis.validate_trx_date(
2365 p_trx_date => SYSDATE
2366 , p_organization_id => p_organization_id
2367 , p_sob_id => inv_rcv_common_apis.g_po_startup_value.sob_id
2368 , x_return_status => x_return_status
2369 , x_error_code => x_msg_data
2370 );
2371
2372 IF x_return_status <> fnd_api.g_ret_sts_success THEN
2373 RETURN;
2374 END IF;
2375
2376 SAVEPOINT inspect_main_sp;
2377
2378 -- Quantity entered on form
2379 l_remaining_qty := l_quantity;
2380 l_sec_remaining_qty:= l_secondary_qty; --Bug 13924968
2381
2382 -- Mapping of Inspection code values from Receiving to WMS/Inventory.
2383 -- Wrong habit to hardcode but that's what receiving
2384 -- transactions interface expects.
2385 -- Inspection Status values:
2386 -- 1 - Yet to be inspected
2387 -- 2 - Accepted
2388 -- 3 - Rejected
2389
2390
2391 IF l_inspection_code = 'ACCEPT' THEN
2392 l_inspection_status := g_accept; /* Accept */
2393 ELSE
2394 l_inspection_status := g_reject; /* Reject */
2395 END IF;
2396
2397 l_primary_uom_code := inv_rcv_cache.get_primary_uom_code(l_organization_id,l_inventory_item_id);
2398 l_sec_uom_code := inv_rcv_cache.get_secondary_uom_code(l_organization_id,l_inventory_item_id);
2399
2400 -- Purchasing/receiving uses unit of measure (Each)
2401 -- rather than uom code(Ea) and hence the following..
2402 -- This will be used later while inserting into interface table
2403
2404 SELECT unit_of_measure
2405 INTO l_uom
2406 FROM mtl_units_of_measure
2407 WHERE uom_code = l_uom_code;
2408
2409 /*OPM Convergence */
2410 IF l_sec_uom_code IS NOT NULL THEN
2411
2412 SELECT unit_of_measure
2413 INTO l_sec_uom
2414 FROM mtl_units_of_measure
2415 WHERE uom_code = l_sec_uom_code;
2416
2417 END IF;
2418
2419 -- Open Move Order Line cursor
2420 OPEN mol_cursor(
2421 l_inventory_item_id
2422 , l_organization_id
2423 , l_lpn_id
2424 , l_revision
2425 , l_lot_number
2426 );
2427
2428 WHILE(l_remaining_qty > 0) LOOP
2429
2430 IF (l_debug = 1) THEN
2431 print_debug('Main process l_remaining_qty : ' || TO_CHAR(l_remaining_qty), 4);
2432 END IF;
2433
2434 -- MOLCON
2435 FETCH mol_cursor
2436 INTO l_mol_line_id
2437 , l_mol_header_id
2438 , l_mol_uom_code
2439 , l_mol_qty
2440 , l_sec_mol_qty --OPM Convergence
2441 , l_mol_ref
2442 , l_mol_ref_code
2443 , l_mol_ref_id
2444 , l_mol_txn_src_id
2445 , l_mol_inspect_status
2446 , l_mol_prim_qty;--Bug 13484877;
2447 -- MOLCON
2448
2449 IF mol_cursor%NOTFOUND THEN
2450 EXIT;
2451 END IF;
2452
2453 IF (l_debug = 1) THEN
2454 print_debug(' l_mol_line_id :'||l_mol_line_id,4);
2455 print_debug(' l_mol_header_id:'||l_mol_header_id,4);
2456 print_debug(' l_mol_qty :'||l_mol_qty,4);
2457 print_debug(' l_mol_uom_code :'||l_mol_uom_code,4);
2458 print_debug(' l_uom_code :'||l_uom_code,4);
2459 print_debug(' l_sec_mol_qty :'||l_sec_mol_qty,4);
2460 print_debug (' l_mol_ref :' || l_mol_ref, 4);
2461 print_debug (' l_mol_ref_code :' || l_mol_ref_code, 4);
2462 print_debug (' l_mol_ref_id :' || l_mol_ref_id, 4);
2463 print_debug (' l_mol_txn_src_id :' || l_mol_txn_src_id, 4);
2464 print_debug (' l_mol_inspect_status :' || l_mol_inspect_status, 4);
2465 print_debug (' l_primary_uom_code :' || l_primary_uom_code, 4);--BUG 13484877
2466 print_debug (' Inspected uom l_uom_code :' || l_uom_code, 4);--BUG 13484877
2467 END IF;
2468
2469 -- If inspection uom is not same as move order uom, we convert
2470 --Bug 13484877 Start- If primary UOM is same as inspected UOM,then instead of comparing the l_uom_code(inspected uom)
2471 --with l_mol_uom_code(receipt uom code), first compare l_uom_code with primary uom, so that directly the MOL prim qty can
2472 --assigned to l_mol_qty and conversion can be avoided here.
2473 IF (l_primary_uom_code = l_uom_code) THEN
2474 l_mol_qty := l_mol_prim_qty;
2475 IF (l_debug = 1) THEN
2476 print_debug ('ASSIGNED THE PRIMARY QTY TO l_mol_qty:' || l_mol_qty, 4);
2477 END IF;
2478 ELSE-- -Bug 13484877 End
2479 IF (l_uom_code <> l_mol_uom_code) THEN
2480 l_mol_qty := inv_rcv_cache.convert_qty
2481 (p_inventory_item_id => l_inventory_item_id
2482 ,p_from_qty => l_mol_qty
2483 ,p_from_uom_code => l_mol_uom_code
2484 ,p_to_uom_code => l_uom_code);
2485 END IF;
2486 END IF;-- -Bug 13484877
2487
2488 IF (l_debug = 1) THEN
2489 print_debug('main process l_mol_qty ' || TO_CHAR(l_mol_qty), 4);
2490 print_debug('main process l_mol_line_id ' || l_mol_line_id, 4);
2491 END IF;
2492
2493 -- l_remaing_mol_qty := min(l_remaining_qty, l_mol_qty)
2494 IF (l_mol_qty >= l_remaining_qty) THEN
2495 l_remaining_mol_qty := l_remaining_qty;
2496 l_sec_remaining_mol_qty := l_sec_remaining_qty; --OPM Convergence
2497 l_remaining_qty := 0;
2498 l_sec_remaining_qty := 0; --OPM Convergence
2499 ELSE
2500 l_remaining_mol_qty := l_mol_qty;
2501 l_sec_remaining_mol_qty := l_sec_mol_qty; --OPM Convergence
2502 l_remaining_qty := l_remaining_qty - l_mol_qty;
2503 l_sec_remaining_qty := l_sec_remaining_qty - l_sec_mol_qty; --OPM Convergence
2504 END IF;
2505
2506 IF (l_debug = 1) THEN
2507 print_debug('main process: l_remaining_mol_qty = min(l_mol_qty, l_remaining_qty) = ' || l_remaining_mol_qty, 4);
2508 END IF;
2509
2510 -- Open Rcv Transactions cursor
2511 -- MOLCON
2512 IF (l_serial_number IS NOT NULL) THEN
2513 OPEN rtv_serial_cursor(
2514 l_inventory_item_id
2515 , l_revision
2516 , l_lpn_id
2517 , l_serial_number
2518 , l_lot_number);
2519 ELSIF (l_lot_number IS NOT NULL) THEN
2520 OPEN rtv_lot_cursor(
2521 l_inventory_item_id
2522 , l_revision
2523 , l_lpn_id
2524 , l_lot_number);
2525 ELSE
2526 OPEN rtv_van_cursor( --Bug Fix:14554730
2527 l_inventory_item_id
2528 , l_revision
2529 , l_lpn_id
2530 ,l_mol_ref
2531 ,l_mol_ref_id
2532 );
2533 END IF;
2534
2535 -- One MOL can only be tied to 1 RT, which can only has 1 RS
2536 -- So, at least for J or higher, assume that this loop
2537 -- will only be executed once
2538
2539 WHILE(l_remaining_mol_qty > 0) LOOP
2540 IF (l_debug = 1) THEN
2541 print_debug('Main process l_remaining_mol_qty : ' || TO_CHAR(l_remaining_mol_qty), 4);
2542 END IF;
2543
2544 -- MOLCON
2545 -- LOOP FROM THE FETCH HERE FOR RTV CURSOR
2546 -- AS THERE MAY BE MULTIPLE RT ROWS FOR SINGLE MOL LINE NOW
2547 -- RTV_CURSOR IF NOTHING IS FOUND AND STILL REMAINING QTY EXISTS FAIL THE TXN
2548 -- ALSO FOR THE CONDITION for L_RTV_QTY > 0 , THE ELSE PART IS NOT NEEDED
2549 -- AS IF THERE ARE MULTIPLE RT's FETCHED THEN THERE ARE PARENT RECEIPT TXN's
2550 -- FOR WHICH SOME RTI IS ALREADY CREATED FOR INSPECTION TXN.
2551 -- MOLCON
2552
2553 IF (l_serial_number IS NOT NULL) THEN
2554 FETCH rtv_serial_cursor
2555 INTO l_rcv_transaction_id
2556 , l_receipt_source_code
2557 , l_rtv_uom
2558 , l_rtv_sec_uom; --OPM Convergence
2559
2560 IF rtv_serial_cursor%NOTFOUND THEN
2561 -- MOLCON
2562 -- CHECK FOR ERROR HERE
2563 IF l_remaining_mol_qty > 0 then
2564 -- this api is called only for one serial
2565 -- say this mol does not qualify, remaining_qty is to be made to 1
2566 -- so as to continue fetching other mols..
2567 l_remaining_qty := 1; -- 12942776
2568
2569 IF (l_debug = 1) THEN
2570 print_debug('jumping to no_mol_match '||l_uom_code,4);
2571 END IF;
2572
2573 goto no_mol_match;
2574 /* commenting for 12942776
2575 fnd_message.set_name('INV', 'INV_RCV_NO_ROWS');
2576 fnd_msg_pub.ADD;
2577 RAISE fnd_api.g_exc_error;
2578 */
2579 END IF;
2580 -- MOLCON
2581 EXIT;
2582 END IF;
2583
2584 ELSIF (l_lot_number IS NOT NULL) THEN
2585 FETCH rtv_lot_cursor
2586 INTO l_rcv_transaction_id
2587 , l_receipt_source_code
2588 , l_rtv_uom
2589 , l_rtv_sec_uom--OPM Convergence
2590 , l_rls_qty;
2591
2592 IF rtv_lot_cursor%NOTFOUND THEN
2593 -- MOLCON
2594 -- CHECK FOR ERROR HERE
2595 IF l_remaining_mol_qty > 0 then
2596 fnd_message.set_name('INV', 'INV_RCV_NO_ROWS');
2597 fnd_msg_pub.ADD;
2598 RAISE fnd_api.g_exc_error;
2599 END IF;
2600 -- MOLCON
2601 EXIT;
2602 END IF;
2603
2604 ELSE
2605 FETCH rtv_van_cursor
2606 INTO l_rcv_transaction_id
2607 , l_receipt_source_code
2608 , l_rtv_uom
2609 , l_rtv_sec_uom; --OPM Convergence
2610
2611 IF rtv_van_cursor%NOTFOUND THEN
2612 -- MOLCON
2613 -- CHECK FOR ERROR HERE
2614 IF l_remaining_mol_qty > 0 then
2615 fnd_message.set_name('INV', 'INV_RCV_NO_ROWS');
2616 fnd_msg_pub.ADD;
2617 RAISE fnd_api.g_exc_error;
2618 END IF;
2619 -- MOLCON
2620 EXIT;
2621 END IF;
2622 END IF; --END IF (l_serial_number IS NOT NULL) THEN
2623
2624 IF (l_debug = 1) THEN
2625 print_debug('l_rcv_transaction_id:'||l_rcv_transaction_id||
2626 ' l_receipt_source_code:'||l_receipt_source_code||
2627 ' l_rtv_uom:'||l_rtv_uom||
2628 ' l_rtv_sec_uom:'||l_rtv_sec_uom||
2629 ' l_rls_qty:'||l_rls_qty,4);
2630 END IF;
2631
2632 IF (l_serial_number IS NOT NULL) THEN
2633 l_rtv_qty := 1;
2634 ELSIF (l_lot_number IS NOT NULL) THEN
2635 BEGIN
2636 SELECT SUM(Nvl(mtli.primary_quantity,0))
2637 INTO l_processed_lot_prim_qty
2638 FROM mtl_transaction_lots_interface mtli
2639 , rcv_transactions_interface rti
2640 WHERE mtli.product_code = 'RCV'
2641 AND mtli.product_transaction_id = rti.interface_transaction_id
2642 AND mtli.lot_number = l_lot_number
2643 AND rti.parent_transaction_id = l_rcv_transaction_id
2644 AND rti.transaction_status_code = 'PENDING'
2645 AND rti.processing_status_code <> 'ERROR';
2646
2647 IF (l_processed_lot_prim_qty IS NULL) THEN
2648 l_processed_lot_prim_qty := 0;
2649 END IF;
2650 EXCEPTION
2651 WHEN OTHERS THEN
2652 l_processed_lot_prim_qty := 0;
2653 END;
2654
2655 IF (l_debug = 1) THEN
2656 print_debug('l_processed_lot_prim_qty: '||l_processed_lot_prim_qty,4);
2657 END IF;
2658
2659 -- Modified for bug 6688055
2660 -- Convert l_rtv_uom(Each) into l_rtv_uom_code(Ea)
2661 SELECT uom_code
2662 INTO l_rtv_uom_code
2663 FROM mtl_units_of_measure
2664 WHERE unit_of_measure = l_rtv_uom;
2665
2666 IF (l_debug = 1) THEN
2667 print_debug('l_rtv_uom_code: '||l_rtv_uom_code||' l_uom_code : '||l_uom_code,4);
2668 END IF;
2669
2670 IF (l_uom_code <> l_rtv_uom_code) THEN
2671 l_cnv_rls_qty := inv_rcv_cache.convert_qty
2672 (p_inventory_item_id => l_inventory_item_id
2673 ,p_from_qty => l_rls_qty
2674 ,p_from_uom_code => l_rtv_uom_code
2675 ,p_to_uom_code => l_uom_code);
2676 ELSE
2677 l_cnv_rls_qty := l_rls_qty;
2678 END IF;
2679
2680 IF (l_debug = 1) THEN
2681 print_debug('l_cnv_rls_qty : '||l_cnv_rls_qty,4);
2682 END IF;
2683
2684
2685 -- If inspection uom is not same as receipt uom, convert
2686 /*
2687 IF (l_primary_uom_code <> l_uom_code) THEN
2688 l_rtv_qty := l_rls_qty - inv_rcv_cache.convert_qty
2689 (p_inventory_item_id => l_inventory_item_id
2690 ,p_from_qty => l_processed_lot_prim_qty
2691 ,p_from_uom_code => l_primary_uom_code
2692 ,p_to_uom_code => l_uom_code);
2693 ELSE
2694 l_rtv_qty := l_rls_qty - l_processed_lot_prim_qty;
2695 END IF;
2696 */
2697 IF (l_primary_uom_code <> l_uom_code) THEN
2698 l_rtv_qty := l_cnv_rls_qty - inv_rcv_cache.convert_qty
2699 (p_inventory_item_id => l_inventory_item_id
2700 ,p_from_qty => l_processed_lot_prim_qty
2701 ,p_from_uom_code => l_primary_uom_code
2702 ,p_to_uom_code => l_uom_code);
2703 ELSE
2704 l_rtv_qty := l_cnv_rls_qty - l_processed_lot_prim_qty;
2705 END IF;
2706
2707
2708 -- Modification for bug 6688055 ended
2709 ELSE
2710 rcv_quantities_s.get_available_quantity(
2711 'INSPECT'
2712 , l_rcv_transaction_id
2713 , l_receipt_source_code
2714 , NULL
2715 , l_rcv_transaction_id
2716 , NULL
2717 , l_rtv_qty
2718 , l_tolerable_qty
2719 , l_rtv_uom);
2720
2721 IF (l_debug = 1) THEN
2722 print_debug('main process l_rtv_qty : ' || TO_CHAR(l_rtv_qty), 4);
2723 END IF;
2724
2725 IF (l_rtv_qty > 0) THEN
2726 -- Purchasing/receiving uses unit of measure (Each)
2727 -- rather than uom code(Ea) and hence the following..
2728 -- Convert l_rtv_uom(Each) into l_rtv_uom_code(Ea)
2729 SELECT uom_code
2730 INTO l_rtv_uom_code
2731 FROM mtl_units_of_measure
2732 WHERE unit_of_measure = l_rtv_uom;
2733
2734 -- If inspection uom is not same as receipt uom, convert
2735
2736 IF (l_uom_code <> l_rtv_uom_code) THEN
2737 l_rtv_qty := inv_rcv_cache.convert_qty
2738 (p_inventory_item_id => l_inventory_item_id
2739 ,p_from_qty => l_rtv_qty
2740 ,p_from_uom_code => l_rtv_uom_code
2741 ,p_to_uom_code => l_uom_code);
2742 END IF;
2743 END IF;
2744 END IF;
2745
2746 IF (l_rtv_qty > 0) THEN
2747 IF l_rtv_qty >= l_remaining_mol_qty THEN
2748 IF (l_debug = 1) THEN
2749 print_debug('main_process: l_rtv >= l_remaining_mol_qty', 4);
2750 END IF;
2751 l_rtv_qty := l_remaining_mol_qty;
2752 l_remaining_mol_qty := 0;
2753 ELSE
2754 IF (l_debug = 1) THEN
2755 print_debug('main_process: l_rtv < l_remaining_mol_qty', 4);
2756 END IF;
2757 l_remaining_mol_qty := l_remaining_mol_qty - l_rtv_qty;
2758 END IF;
2759
2760 IF (l_debug = 1) THEN
2761 print_debug('main_process: l_rtv_qty = min(available qty, l_remaining_mol_qty) = ' || l_rtv_qty, 4);
2762 END IF;
2763
2764 -- If required convert into primary unit of measure
2765 IF (l_uom_code <> l_primary_uom_code) THEN
2766 l_primary_qty := inv_rcv_cache.convert_qty
2767 (p_inventory_item_id => l_inventory_item_id
2768 ,p_from_qty => l_rtv_qty
2769 ,p_from_uom_code => l_uom_code
2770 ,p_to_uom_code => l_primary_uom_code);
2771 ELSE
2772 l_primary_qty := l_rtv_qty;
2773 END IF;
2774
2775 IF l_inspection_status = g_accept THEN
2776 IF (l_accept_lpn_id > 0) THEN
2777 l_rti_lpn_id := l_lpn_id;
2778 l_rti_transfer_lpn_id := l_accept_lpn_id;
2779 ELSE
2780 l_rti_lpn_id := l_lpn_id;
2781 END IF;
2782 ELSE
2783 IF (l_reject_lpn_id > 0) THEN
2784 l_rti_lpn_id := l_lpn_id;
2785 l_rti_transfer_lpn_id := l_reject_lpn_id;
2786 ELSE
2787 l_rti_lpn_id := l_lpn_id;
2788 END IF;
2789 END IF;
2790
2791 -- If l_rtv_quantity < l_mol_qty
2792 -- Split MOL
2793 -- Create new RTI for each MMTT in the new MOL
2794 -- Insert Lot/Serials Interface record for each of these RTI
2795 -- Call ATF API
2796 IF (l_debug = 1) THEN
2797 print_debug('main_process : inside RTV cursor Loop, before split_mo', 4);
2798 print_debug(' l_rtv_qty =======> ' || l_rtv_qty, 4);
2799 print_debug(' l_remaining_mol_qty => ' || l_remaining_mol_qty, 4);
2800 print_debug(' l_primary_qty ===> ' || l_primary_qty, 4);
2801 print_debug(' l_mol_qty =======> ' || l_mol_qty, 4);
2802 print_debug(' l_remaining_qty => ' || l_remaining_qty, 4);
2803 print_debug(' l_mol_line_id ===> ' || l_mol_line_id, 4);
2804 END IF;
2805
2806 IF (l_rtv_qty < l_mol_qty) THEN
2807 l_mo_splt_tb(1).prim_qty := l_primary_qty;
2808 l_mo_splt_tb(1).sec_qty := l_secondary_qty;--13431255
2809
2810 IF (l_debug = 1) THEN
2811 print_debug('main_process : Calling split_mo: ' || l_return_status, 4);
2812 print_debug(' p_orig_mol_id ============> ' || l_mol_line_id, 4);
2813 print_debug(' p_mo_splt_tb(1).prim_qty => ' || l_mo_splt_tb(1).prim_qty, 4);
2814 END IF;
2815
2816 inv_rcv_integration_apis.split_mo(
2817 p_orig_mol_id => l_mol_line_id
2818 , p_mo_splt_tb => l_mo_splt_tb
2819 , x_return_status => l_return_status
2820 , x_msg_count => l_msg_count
2821 , x_msg_data => l_msg_data);
2822
2823 IF (l_debug = 1) THEN
2824 print_debug('main_process : Call to split_mo returns: ' || l_return_status, 4);
2825 END IF;
2826
2827 IF l_return_status = fnd_api.g_ret_sts_error THEN
2828 RAISE fnd_api.g_exc_error;
2829 END IF;
2830
2831 IF l_return_status = fnd_api.g_ret_sts_unexp_error THEN
2832 RAISE fnd_api.g_exc_unexpected_error;
2833 END IF;
2834 l_new_mol_id := l_mo_splt_tb(1).line_id;
2835 ELSE
2836 l_new_mol_id := l_mol_line_id;
2837 END IF; -- IF (l_remaining_mol_qty < l_mol_qty) THEN
2838
2839 l_progress := '50';
2840 -- clear records before bulk collecting
2841 IF (l_mmtt_ids.COUNT > 0) THEN
2842 l_mmtt_ids.DELETE;
2843 END IF;
2844
2845 l_progress := '60';
2846 IF (l_transaction_quantities.COUNT > 0) THEN
2847 l_transaction_quantities.DELETE;
2848 END IF;
2849
2850 /* OPM Convergence */
2851 IF (l_secondary_txn_quantities.COUNT > 0) THEN
2852 l_secondary_txn_quantities.DELETE;
2853 END IF;
2854 l_progress := '70';
2855 IF (l_primary_quantities.COUNT > 0) THEN
2856 l_primary_quantities.DELETE;
2857 END IF;
2858
2859 l_progress := '80';
2860 BEGIN
2861 print_debug('Select mmtt records based on move_order_line_id order by transaction_temp_id', 4); --6160359,6189438
2862 SELECT transaction_temp_id
2863 , primary_quantity
2864 , DECODE(l_uom_code,ITEM_PRIMARY_UOM_CODE,primary_quantity,transaction_uom--added for Bug 13484877
2865 --commented for Bug 13484877, l_uom_code
2866 , transaction_quantity /*Bug6133345*/
2867 , inv_rcv_cache.convert_qty
2868 (l_inventory_item_id
2869 ,transaction_quantity
2870 ,transaction_uom
2871 ,l_uom_code
2872 ,NULL)
2873 ) quantity
2874 , secondary_transaction_quantity --OPM Convergence
2875 BULK COLLECT INTO
2876 l_mmtt_ids
2877 , l_primary_quantities
2878 , l_transaction_quantities
2879 , l_secondary_txn_quantities --OPM Convergence
2880 FROM mtl_material_transactions_temp
2881 WHERE move_order_line_id = l_new_mol_id;
2882 EXCEPTION
2883 WHEN OTHERS THEN
2884 l_mmtt_ids(1) := NULL;
2885 l_primary_quantities(1) := l_primary_qty;
2886 l_transaction_quantities(1) := l_rtv_qty;
2887 l_secondary_txn_quantities(1) := l_rtv_sec_qty; --OPM Convergence
2888 END;
2889
2890 l_progress := '90';
2891 -- IF there are no mmtts, then insert RTI with no MMTT id
2892 -- with l_rtv_qty and l_primary_qty
2893 IF (l_mmtt_ids.COUNT = 0) THEN
2894 l_mmtt_ids(1) := NULL;
2895 l_primary_quantities(1) := l_primary_qty;
2896 l_transaction_quantities(1) := l_rtv_qty;
2897 l_secondary_txn_quantities(1) := l_rtv_sec_qty; --OPM Convergence
2898 END IF;
2899
2900 l_progress := '100';
2901
2902 FOR i IN 1 .. l_mmtt_ids.COUNT LOOP
2903 IF (l_debug = 1) THEN
2904 print_debug('Main process inserting RTI for MMTT:' || NVL(l_mmtt_ids(i), -1)
2905 || ' quantity:' || l_transaction_quantities(i) || ' uom:' || l_uom, 4);
2906 END IF;
2907
2908 l_progress := '110';
2909 insert_inspect_rec_rti(
2910 x_return_status => l_return_status
2911 , x_msg_count => l_msg_count
2912 , x_msg_data => l_msg_data
2913 , p_rcv_transaction_id => l_rcv_transaction_id
2914 , p_quantity => l_transaction_quantities(i)
2915 , p_uom => l_uom
2916 , p_inspection_code => l_inspection_code
2917 , p_quality_code => l_quality_code
2918 , p_transaction_date => l_transaction_date
2919 , p_transaction_type => l_transaction_type
2920 , p_vendor_lot => l_vendor_lot
2921 , p_reason_id => l_reason_id
2922 , p_primary_qty => l_primary_quantities(i)
2923 , p_organization_id => l_organization_id
2924 , p_comments => l_comments
2925 , p_attribute_category => l_attribute_category
2926 , p_attribute1 => l_attribute1
2927 , p_attribute2 => l_attribute2
2928 , p_attribute3 => l_attribute3
2929 , p_attribute4 => l_attribute4
2930 , p_attribute5 => l_attribute5
2931 , p_attribute6 => l_attribute6
2932 , p_attribute7 => l_attribute7
2933 , p_attribute8 => l_attribute8
2934 , p_attribute9 => l_attribute9
2935 , p_attribute10 => l_attribute10
2936 , p_attribute11 => l_attribute11
2937 , p_attribute12 => l_attribute12
2938 , p_attribute13 => l_attribute13
2939 , p_attribute14 => l_attribute14
2940 , p_attribute15 => l_attribute15
2941 , p_qa_collection_id => l_qa_collection_id
2942 , p_lpn_id => l_rti_lpn_id
2943 , p_transfer_lpn_id => l_rti_transfer_lpn_id
2944 , p_mmtt_temp_id => l_mmtt_ids(i)
2945 , p_sec_uom => l_sec_uom --OPM Convergence
2946 , p_secondary_qty => l_rtv_sec_qty); --OPM Convergence
2947
2948 IF l_return_status = fnd_api.g_ret_sts_error THEN
2949 RAISE fnd_api.g_exc_error;
2950 END IF;
2951
2952 IF l_return_status = fnd_api.g_ret_sts_unexp_error THEN
2953 RAISE fnd_api.g_exc_unexpected_error;
2954 END IF;
2955
2956 /* FP-J Lot/Serial Support Enhancement
2957 * Process the lot numbers and serial numbers corresponding to the RTI
2958 * that was just created.
2959 * Since the lots and serials are stored by receiving tables, the
2960 * changes to RTI must be reflected in RCV_LOTS_SUPPLY (lot controlled item)
2961 * and RCV_SERIALS_SUPPLY (serial controlled item).
2962 * We would be creating the interface records in MTLI and MSNI corresponding
2963 * to the inspected quantity, lot number and the serial numbers inspected
2964 * Do this only if WMS and PO patch levels are J or higher
2965 */
2966 SELECT lot_control_code
2967 , serial_number_control_code
2968 INTO l_lot_control_code
2969 , l_serial_control_code
2970 FROM mtl_system_items
2971 WHERE inventory_item_id = p_inventory_item_id
2972 AND organization_id = p_organization_id;
2973
2974 IF (l_lot_control_code > 1 OR l_serial_control_code > 1) THEN
2975 IF (l_debug = 1) THEN
2976 print_debug('creating lots and/or serials interface records with product_transaction_id : '
2977 || g_interface_transaction_id, 4);
2978 END IF;
2979
2980 process_lot_serial_intf(
2981 x_return_status => l_return_status
2982 , x_msg_count => l_msg_count
2983 , x_msg_data => l_msg_data
2984 , p_organization_id => p_organization_id
2985 , p_inventory_item_id => p_inventory_item_id
2986 , p_lot_control_code => l_lot_control_code
2987 , p_serial_control_code => l_serial_control_code
2988 , p_lot_number => p_lot_number
2989 , p_txn_qty => l_transaction_quantities(i)
2990 , p_primary_qty => l_primary_quantities(i)
2991 , p_serial_number => p_serial_number
2992 , p_product_transaction_id => g_interface_transaction_id
2993 , p_lpn_id => p_lpn_id
2994 , p_sec_txn_qty => l_secondary_txn_quantities(i) ); --OPM Convergence
2995
2996 IF (l_debug = 1) THEN
2997 print_debug('main_process: process_lot_serial_intf returns: ' || l_return_status, 4);
2998 END IF;
2999
3000 IF l_return_status = fnd_api.g_ret_sts_error THEN
3001 RAISE fnd_api.g_exc_error;
3002 END IF;
3003
3004 IF l_return_status = fnd_api.g_ret_sts_unexp_error THEN
3005 RAISE fnd_api.g_exc_unexpected_error;
3006 END IF;
3007 END IF; --END IF check lot and serial controls
3008 END LOOP; -- End MMTT Loop
3009
3010 -- Activate the INSPECT operation
3011 l_rec_count := wms_putaway_utils.activate_plan_for_inspect(
3012 x_return_status => x_return_status
3013 , x_msg_count => x_msg_count
3014 , x_msg_data => x_msg_data
3015 , p_org_id => l_organization_id
3016 , p_mo_line_id => l_new_mol_id);
3017
3018 IF (x_return_status <> fnd_api.g_ret_sts_success) THEN
3019 IF (l_debug = 1) THEN
3020 print_debug(' Error in Activate_Plan_For_Load ' || x_msg_data, 1);
3021 END IF;
3022 RAISE fnd_api.g_exc_error;
3023 ELSE
3024 IF (l_debug = 1) THEN
3025 print_debug('Successfully called Activate_Plan_For_Load for ' || l_rec_count || ' row(s)', 9);
3026 END IF;
3027 END IF;
3028
3029 -- Activate the INSPECT operation
3030 --Update the wms_process_flag for the current MOL so that one else
3031 --messes with it
3032 UPDATE mtl_txn_request_lines
3033 SET wms_process_flag = 2
3034 WHERE line_id = l_new_mol_id;
3035 ELSE
3036
3037 IF (l_debug = 1) THEN
3038 print_debug('main_process: There is no quantity available to Inspect: ', 4);
3039 END IF;
3040
3041 -- MOLCON COMMENTED THIS CALL HERE
3042 -- THIS HAS TO BE TRACKED ABOVE
3043 -- fnd_message.set_name('INV', 'INV_RCV_NO_ROWS');
3044 -- fnd_msg_pub.ADD;
3045 -- RAISE fnd_api.g_exc_error;
3046
3047 END IF; --END IF IF (l_rtv_qty > 0)
3048 END LOOP;
3049
3050 <<no_mol_match>>
3051
3052 IF (rtv_van_cursor%ISOPEN) THEN
3053 CLOSE rtv_van_cursor;
3054 END IF;
3055
3056 IF (rtv_lot_cursor%isopen) THEN
3057 CLOSE rtv_lot_cursor;
3058 END IF;
3059
3060 IF (rtv_serial_cursor%isopen) THEN
3061 CLOSE rtv_serial_cursor;
3062 END IF;
3063 END LOOP; -- WHILE(l_remaining_qty > 0) LOOP
3064
3065 CLOSE mol_cursor;
3066
3067 IF (l_remaining_qty > 0) THEN
3068 IF (l_debug = 1) THEN
3069 print_debug('main_process: No more MOL, but remaining qty still exists', 4);
3070 END IF;
3071 RAISE fnd_api.g_exc_error;
3072 END IF;
3073
3074 EXCEPTION
3075 WHEN fnd_api.g_exc_error THEN
3076 IF (l_debug = 1) THEN
3077 print_debug('Exception raised in main_process at progress: ' || l_progress, 4);
3078 END IF;
3079
3080 ROLLBACK TO inspect_main_sp;
3081 x_return_status := fnd_api.g_ret_sts_error;
3082 -- Get message count and data
3083 fnd_msg_pub.count_and_get(p_count => x_msg_count, p_data => x_msg_data);
3084
3085 IF (mol_cursor%ISOPEN) THEN
3086 CLOSE mol_cursor;
3087 END IF;
3088
3089 IF (rtv_van_cursor%ISOPEN) THEN
3090 CLOSE rtv_van_cursor;
3091 END IF;
3092
3093 IF (rtv_lot_cursor%isopen) THEN
3094 CLOSE rtv_lot_cursor;
3095 END IF;
3096
3097 IF (rtv_serial_cursor%isopen) THEN
3098 CLOSE rtv_serial_cursor;
3099 END IF;
3100
3101 WHEN fnd_api.g_exc_unexpected_error THEN
3102 IF (l_debug = 1) THEN
3103 print_debug('Exception raised in main_process at progress: ' || l_progress, 4);
3104 END IF;
3105
3106 ROLLBACK TO inspect_main_sp;
3107 x_return_status := fnd_api.g_ret_sts_unexp_error;
3108 -- Get message count and data
3109 fnd_msg_pub.count_and_get(p_count => x_msg_count, p_data => x_msg_data);
3110
3111 IF (mol_cursor%ISOPEN) THEN
3112 CLOSE mol_cursor;
3113 END IF;
3114
3115
3116 IF (rtv_van_cursor%ISOPEN) THEN
3117 CLOSE rtv_van_cursor;
3118 END IF;
3119
3120 IF (rtv_lot_cursor%isopen) THEN
3121 CLOSE rtv_lot_cursor;
3122 END IF;
3123
3124 IF (rtv_serial_cursor%isopen) THEN
3125 CLOSE rtv_serial_cursor;
3126 END IF;
3127 WHEN OTHERS THEN
3128 IF (l_debug = 1) THEN
3129 print_debug('Exception raised in main_process at progress: ' || l_progress, 4);
3130 END IF;
3131
3132 ROLLBACK TO inspect_main_sp;
3133 x_return_status := fnd_api.g_ret_sts_unexp_error;
3134
3135 --
3136 IF fnd_msg_pub.check_msg_level(fnd_msg_pub.g_msg_lvl_unexp_error) THEN
3137 fnd_msg_pub.add_exc_msg(g_pkg_name, 'main_process');
3138 END IF;
3139
3140 -- Get message count and data
3141 fnd_msg_pub.count_and_get(p_count => x_msg_count, p_data => x_msg_data);
3142
3143 IF (mol_cursor%ISOPEN) THEN
3144 CLOSE mol_cursor;
3145 END IF;
3146
3147 IF (rtv_van_cursor%ISOPEN) THEN
3148 CLOSE rtv_van_cursor;
3149 END IF;
3150
3151 IF (rtv_lot_cursor%isopen) THEN
3152 CLOSE rtv_lot_cursor;
3153 END IF;
3154
3155 IF (rtv_serial_cursor%isopen) THEN
3156 CLOSE rtv_serial_cursor;
3157 END IF;
3158 END main_process;
3159
3160 PROCEDURE range_serial_process (
3161 x_return_status OUT NOCOPY VARCHAR2,
3162 x_msg_count OUT NOCOPY NUMBER,
3163 x_msg_data OUT NOCOPY VARCHAR2,
3164 p_inventory_item_id IN NUMBER,
3165 p_organization_id IN NUMBER,
3166 p_lpn_id IN NUMBER,
3167 p_revision IN VARCHAR2,
3168 p_lot_number IN VARCHAR2,
3169 p_inspection_code IN VARCHAR2,
3170 p_quality_code IN VARCHAR2,
3171 p_transaction_type IN VARCHAR2,
3172 p_reason_id IN NUMBER,
3173 p_from_serial_number IN VARCHAR2,
3174 p_to_serial_number IN VARCHAR2,
3175 p_accept_lpn_id IN NUMBER,
3176 p_reject_lpn_id IN NUMBER,
3177 p_transaction_date IN DATE DEFAULT SYSDATE,
3178 p_vendor_lot IN VARCHAR2 DEFAULT NULL,
3179 p_comments IN VARCHAR2 DEFAULT NULL,
3180 p_attribute_category IN VARCHAR2 DEFAULT NULL,
3181 p_attribute1 IN VARCHAR2 DEFAULT NULL,
3182 p_attribute2 IN VARCHAR2 DEFAULT NULL,
3183 p_attribute3 IN VARCHAR2 DEFAULT NULL,
3184 p_attribute4 IN VARCHAR2 DEFAULT NULL,
3185 p_attribute5 IN VARCHAR2 DEFAULT NULL,
3186 p_attribute6 IN VARCHAR2 DEFAULT NULL,
3187 p_attribute7 IN VARCHAR2 DEFAULT NULL,
3188 p_attribute8 IN VARCHAR2 DEFAULT NULL,
3189 p_attribute9 IN VARCHAR2 DEFAULT NULL,
3190 p_attribute10 IN VARCHAR2 DEFAULT NULL,
3191 p_attribute11 IN VARCHAR2 DEFAULT NULL,
3192 p_attribute12 IN VARCHAR2 DEFAULT NULL,
3193 p_attribute13 IN VARCHAR2 DEFAULT NULL,
3194 p_attribute14 IN VARCHAR2 DEFAULT NULL,
3195 p_attribute15 IN VARCHAR2 DEFAULT NULL
3196 )
3197 IS
3198 l_temp_prefix VARCHAR2 (30);
3199 l_from_ser_number NUMBER;
3200 l_to_ser_number NUMBER;
3201 l_range_numbers NUMBER;
3202 l_cur_ser_number NUMBER;
3203 l_cur_serial_number VARCHAR2 (30);
3204 l_primary_uom_code VARCHAR2 (5);
3205 l_return_status VARCHAR2 (5);
3206 l_msg_count NUMBER;
3207 l_msg_data VARCHAR2 (1000);
3208 -- Increased lot size to 80 Char - 3ercy Thomas - B4625329
3209 l_lot_number VARCHAR2 (80);
3210 l_debug NUMBER
3211 := NVL (fnd_profile.VALUE ('INV_DEBUG_TRACE'), 0);
3212 --l_serial_tbl inv_rcv_std_inspect_apis.t_serial_rec_tbl;
3213 BEGIN
3214 x_return_status := fnd_api.g_ret_sts_success;
3215 SAVEPOINT process_sl_sp;
3216
3217 --
3218 -- Get the primary uom of item. If we are here the item should be serial
3219 -- controlled. Most probably the uom should be 'Ea' but then we can't assume
3220 -- this. This would also not be a right assumption for non English cases
3221 --
3222 if l_serial_tbl.count > 0 then
3223 l_serial_tbl.DELETE;
3224 end if;
3225
3226 SELECT primary_uom_code
3227 INTO l_primary_uom_code
3228 FROM mtl_system_items
3229 WHERE organization_id = p_organization_id
3230 AND inventory_item_id = p_inventory_item_id
3231 AND serial_number_control_code IN (2, 5, 6);
3232
3233 -- get the number part of the from serial
3234 inv_validate.number_from_sequence (p_from_serial_number,
3235 l_temp_prefix,
3236 l_from_ser_number
3237 );
3238 -- get the number part of the to serial
3239 inv_validate.number_from_sequence (p_to_serial_number,
3240 l_temp_prefix,
3241 l_to_ser_number
3242 );
3243 -- total number of serials
3244 l_range_numbers := l_to_ser_number - l_from_ser_number + 1;
3245
3246 FOR i IN 1 .. l_range_numbers
3247 LOOP
3248 -- Number part of serial number like 123
3249 l_cur_ser_number := l_from_ser_number + i - 1;
3250 -- concatenate the serial number to be inserted like XYZ123
3251 -- l_cur_serial_number := l_temp_prefix || l_cur_ser_number;
3252 l_cur_serial_number :=
3253 SUBSTR (p_from_serial_number,
3254 1,
3255 LENGTH (p_from_serial_number)
3256 - LENGTH (l_cur_ser_number)
3257 )
3258 || l_cur_ser_number;
3259 -- dbms_output.put_line('Curr Sl No:' || l_cur_serial_number || 'ZZZ');
3260 -- We cannot assume that the serial number range belong to the samelot..
3261 print_debug ('Is the prblem before ', 4);
3262 l_serial_tbl (i) := l_cur_serial_number;
3263 print_debug ('Is the prblem after ', 4);
3264 END LOOP; -- brought the loop to outside 12942776
3265
3266 SELECT lot_number
3267 INTO l_lot_number
3268 FROM mtl_serial_numbers
3269 WHERE inventory_item_id = p_inventory_item_id
3270 AND serial_number = l_cur_serial_number;
3271
3272 -- Call processing for each serial number
3273 -- A new parameter qa_collection_id has been added to
3274 -- main process for QA, but QA will not inspect range of
3275 -- sl. nos. Hence passing it a value of NULL
3276 -- added extra variable 12942776
3277 main_process (x_return_status => l_return_status,
3278 x_msg_count => l_msg_count,
3279 x_msg_data => l_msg_data,
3280 p_inventory_item_id => p_inventory_item_id,
3281 p_organization_id => p_organization_id,
3282 p_lpn_id => p_lpn_id,
3283 p_revision => p_revision,
3284 p_lot_number => l_lot_number,
3285 p_uom_code => l_primary_uom_code,
3286 p_quantity => l_serial_tbl.COUNT,
3287 p_inspection_code => p_inspection_code,
3288 p_quality_code => p_quality_code,
3289 p_transaction_type => p_transaction_type,
3290 p_reason_id => p_reason_id,
3291 p_serial_number => l_cur_serial_number,
3292 p_accept_lpn_id => p_accept_lpn_id,
3293 p_reject_lpn_id => p_reject_lpn_id,
3294 p_transaction_date => p_transaction_date,
3295 p_qa_collection_id => NULL,
3296 p_vendor_lot => p_vendor_lot,
3297 p_comments => p_comments,
3298 p_attribute_category => p_attribute_category,
3299 p_attribute1 => p_attribute1,
3300 p_attribute2 => p_attribute2,
3301 p_attribute3 => p_attribute3,
3302 p_attribute4 => p_attribute4,
3303 p_attribute5 => p_attribute5,
3304 p_attribute6 => p_attribute6,
3305 p_attribute7 => p_attribute7,
3306 p_attribute8 => p_attribute8,
3307 p_attribute9 => p_attribute9,
3308 p_attribute10 => p_attribute10,
3309 p_attribute11 => p_attribute11,
3310 p_attribute12 => p_attribute12,
3311 p_attribute13 => p_attribute13,
3312 p_attribute14 => p_attribute14,
3313 p_attribute15 => p_attribute15,
3314 p_serial_tbl => l_serial_tbl
3315 );
3316
3317 IF l_return_status = fnd_api.g_ret_sts_error
3318 THEN
3319 RAISE fnd_api.g_exc_error;
3320 END IF;
3321
3322 IF l_return_status = fnd_api.g_ret_sts_unexp_error
3323 THEN
3324 RAISE fnd_api.g_exc_unexpected_error;
3325 END IF;
3326 EXCEPTION
3327 WHEN fnd_api.g_exc_error
3328 THEN
3329 ROLLBACK TO process_sl_sp;
3330 x_return_status := fnd_api.g_ret_sts_error;
3331 -- Get message count and data
3332 fnd_msg_pub.count_and_get (p_count => x_msg_count,
3333 p_data => x_msg_data
3334 );
3335 WHEN fnd_api.g_exc_unexpected_error
3336 THEN
3337 ROLLBACK TO process_sl_sp;
3338 x_return_status := fnd_api.g_ret_sts_unexp_error;
3339 -- Get message count and data
3340 fnd_msg_pub.count_and_get (p_count => x_msg_count,
3341 p_data => x_msg_data
3342 );
3343 WHEN OTHERS
3344 THEN
3345 ROLLBACK TO process_sl_sp;
3346 x_return_status := fnd_api.g_ret_sts_unexp_error;
3347
3348 --
3349 IF fnd_msg_pub.check_msg_level (fnd_msg_pub.g_msg_lvl_unexp_error)
3350 THEN
3351 fnd_msg_pub.add_exc_msg (g_pkg_name, 'range_serial_process');
3352 END IF;
3353
3354 -- Get message count and data
3355 fnd_msg_pub.count_and_get (p_count => x_msg_count,
3356 p_data => x_msg_data
3357 );
3358 END range_serial_process;
3359
3360 procedure main_process_po(
3361 x_return_status OUT NOCOPY VARCHAR2
3362 , x_msg_count OUT NOCOPY NUMBER
3363 , x_msg_data OUT NOCOPY VARCHAR2
3364 , p_inventory_item_id IN NUMBER
3365 , p_organization_id IN NUMBER
3366 , p_po_header_id IN NUMBER
3367 , p_revision IN VARCHAR2
3368 , p_uom_code IN VARCHAR2
3369 , p_quantity IN NUMBER
3370 , p_inspection_code IN VARCHAR2
3371 , p_quality_code IN VARCHAR2
3372 , p_transaction_type IN VARCHAR2
3373 , p_reason_id IN NUMBER
3374 , p_transaction_date IN DATE DEFAULT SYSDATE
3375 , p_qa_collection_id IN NUMBER DEFAULT NULL
3376 , p_vendor_lot IN VARCHAR2 DEFAULT NULL
3377 , p_comments IN VARCHAR2 DEFAULT NULL
3378 , p_attribute_category IN VARCHAR2 DEFAULT NULL
3379 , p_attribute1 IN VARCHAR2 DEFAULT NULL
3380 , p_attribute2 IN VARCHAR2 DEFAULT NULL
3381 , p_attribute3 IN VARCHAR2 DEFAULT NULL
3382 , p_attribute4 IN VARCHAR2 DEFAULT NULL
3383 , p_attribute5 IN VARCHAR2 DEFAULT NULL
3384 , p_attribute6 IN VARCHAR2 DEFAULT NULL
3385 , p_attribute7 IN VARCHAR2 DEFAULT NULL
3386 , p_attribute8 IN VARCHAR2 DEFAULT NULL
3387 , p_attribute9 IN VARCHAR2 DEFAULT NULL
3388 , p_attribute10 IN VARCHAR2 DEFAULT NULL
3389 , p_attribute11 IN VARCHAR2 DEFAULT NULL
3390 , p_attribute12 IN VARCHAR2 DEFAULT NULL
3391 , p_attribute13 IN VARCHAR2 DEFAULT NULL
3392 , p_attribute14 IN VARCHAR2 DEFAULT NULL
3393 , p_attribute15 IN VARCHAR2 DEFAULT NULL
3394 , p_secondary_qty IN NUMBER DEFAULT NULL) --OPM Convergence
3395 is
3396 l_inventory_item_id NUMBER := p_inventory_item_id;
3397 l_organization_id NUMBER := p_organization_id;
3398 l_revision VARCHAR2(10) := p_revision;
3399 l_revision_control NUMBER; -- Added for bug 3134272
3400 l_uom_code VARCHAR2(5) := p_uom_code;
3401 l_uom VARCHAR2(30);
3402 l_quantity NUMBER := p_quantity;
3403 l_po_header_id NUMBER := p_po_header_id;
3404
3405 l_inspection_code VARCHAR2(25) := p_inspection_code;
3406 l_quality_code VARCHAR2(25) := p_quality_code;
3407 l_transaction_date DATE := p_transaction_date;
3408 l_comments VARCHAR2(240) := p_comments;
3409 l_attribute_category VARCHAR2(30) := p_attribute_category;
3410 l_attribute1 VARCHAR2(150) := p_attribute1;
3411 l_attribute2 VARCHAR2(150) := p_attribute2;
3412 l_attribute3 VARCHAR2(150) := p_attribute3;
3413 l_attribute4 VARCHAR2(150) := p_attribute4;
3414 l_attribute5 VARCHAR2(150) := p_attribute5;
3415 l_attribute6 VARCHAR2(150) := p_attribute6;
3416 l_attribute7 VARCHAR2(150) := p_attribute7;
3417 l_attribute8 VARCHAR2(150) := p_attribute8;
3418 l_attribute9 VARCHAR2(150) := p_attribute9;
3419 l_attribute10 VARCHAR2(150) := p_attribute10;
3420 l_attribute11 VARCHAR2(150) := p_attribute11;
3421 l_attribute12 VARCHAR2(150) := p_attribute12;
3422 l_attribute13 VARCHAR2(150) := p_attribute13;
3423 l_attribute14 VARCHAR2(150) := p_attribute14;
3424 l_attribute15 VARCHAR2(150) := p_attribute15;
3425 l_transaction_type VARCHAR2(30) := p_transaction_type;
3426 l_vendor_lot VARCHAR2(30) := p_vendor_lot;
3427 l_reason_id NUMBER := p_reason_id;
3428
3429 l_qa_collection_id NUMBER := p_qa_collection_id;
3430
3431 l_primary_qty NUMBER;
3432 l_primary_uom_code varchar2(5);
3433
3434 l_rcv_transaction_id number;
3435 l_rtv_qty number;
3436 l_rtv_uom varchar2(25); /* Each */
3437 l_rtv_uom_code varchar2(5); /* Ea */
3438 l_receipt_source_code varchar2(25);
3439 l_tolerable_qty number;
3440
3441 l_remaining_qty number;
3442 l_transacted_qty number;
3443
3444 l_return_status varchar2(5);
3445 l_msg_count number;
3446 l_msg_data varchar2(1000);
3447
3448 l_secondary_qty NUMBER := p_secondary_qty; --OPM COnvergence
3449 l_remaining_sec_qty NUMBER; --OPM Convergence
3450 l_rtv_sec_uom VARCHAR2(25);--OPM Convergence
3451 l_sec_uom_code VARCHAR2(3);--OPM Convergence
3452 l_sec_uom VARCHAR2(25);--OPM Convergence
3453 l_rtv_sec_qty NUMBER;--OPM COnvergence
3454 L_SEC_REMAINING_QTY NUMBER;--OPM Convergence
3455 /* cursor rtv_cursor(
3456 k_po_header_id number
3457 , k_organization_id number
3458 , k_inventory_item_id number
3459 , k_revision varchar2)
3460 is
3461 select
3462 rcv_transaction_id
3463 , receipt_source_code
3464 , unit_of_measure
3465 from rcv_transactions_v
3466 where po_header_id = k_po_header_id
3467 and to_organization_id = k_organization_id
3468 and item_id = k_inventory_item_id
3469 and (item_revision = k_revision OR
3470 item_revision is null and p_revision is null)
3471 and inspection_status_code = 'NOT INSPECTED'
3472 and routing_id = g_inspection_routing;
3473 */
3474 /* Bug 1542687: For performance reasons, the cursor is based on base tables below.*/
3475
3476 --bug 8405606 removed the condition for rt.inspection_status_code = 'NOT INSPECTED'
3477 cursor rtv_cursor(
3478 k_po_header_id number
3479 , k_organization_id number
3480 , k_inventory_item_id number
3481 , k_revision varchar2
3482 , k_revision_control number -- Added for bug 3134272
3483 ) is
3484 select
3485 rs.rcv_transaction_id
3486 , rsh.receipt_source_code
3487 , rs.unit_of_measure
3488 , rs.secondary_unit_of_measure --OPM Convergence
3489 from rcv_supply rs, rcv_transactions rt, rcv_shipment_headers rsh
3490 where rs.po_header_id = k_po_header_id
3491 and rs.to_organization_id = k_organization_id
3492 and rs.item_id = k_inventory_item_id
3493 and (k_revision_control = 2
3494 and Nvl(rs.item_revision,-1) = Nvl(k_revision,-1)
3495 OR k_revision_control = 1)
3496 -- Changed the above for bug 3134272
3497 and rs.rcv_transaction_id = rt.transaction_id
3498 and rsh.shipment_header_id = rs.shipment_header_id
3499 and rs.supply_type_code = 'RECEIVING'
3500 and rt.transaction_type <> 'UNORDERED'
3501 and rt.routing_header_id = g_inspection_routing;
3502
3503 l_debug number := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
3504 begin
3505 x_return_status := fnd_api.g_ret_sts_success;
3506
3507 -- dbms_output.put_line('main_process_po: Just entering main_process_po');
3508
3509 --First check if the transaction date satisfies the validation.
3510 --If the transaction date is invalid then error out the transaction
3511 IF inv_rcv_common_apis.g_po_startup_value.sob_id IS NULL THEN
3512 --BUG 3444196: Used the HR view instead for performance reasons
3513 SELECT TO_NUMBER(hoi.org_information1)
3514 INTO inv_rcv_common_apis.g_po_startup_value.sob_id
3515 FROM hr_organization_information hoi
3516 WHERE hoi.organization_id = p_organization_id
3517 AND (hoi.org_information_context || '') = 'Accounting Information' ;
3518 END IF;
3519
3520 inv_rcv_common_apis.validate_trx_date(
3521 p_trx_date => SYSDATE
3522 , p_organization_id => p_organization_id
3523 , p_sob_id => inv_rcv_common_apis.g_po_startup_value.sob_id
3524 , x_return_status => x_return_status
3525 , x_error_code => x_msg_data
3526 );
3527
3528 IF x_return_status <> fnd_api.g_ret_sts_success THEN
3529 RETURN;
3530 END IF;
3531
3532 savepoint inspect_main_po_sp;
3533
3534 -- Quantity entered on form
3535 l_remaining_qty := l_quantity;
3536 l_sec_remaining_qty:= l_secondary_qty; --Bug 13924968
3537
3538 -- Quantity successfully transacted
3539 l_transacted_qty := 0;
3540
3541 -- One time fetch of item's primary uom code
3542 -- Fetching revision control for bug 3134272
3543 select primary_uom_code,revision_qty_control_code, secondary_uom_code --OPM Convergence
3544 into l_primary_uom_code,l_revision_control, l_sec_uom_code --OPM Convergence
3545 from mtl_system_items
3546 where organization_id = l_organization_id
3547 and inventory_item_id = l_inventory_item_id;
3548
3549 -- dbms_output.put_line('main_process_po: Fetched item primary uom code');
3550
3551 -- Purchasing/receiving uses unit of measure (Each)
3552 -- rather than uom code(Ea) and hence the following..
3553 -- This will be used later while inserting into interface table
3554
3555 SELECT unit_of_measure
3556 INTO l_uom
3557 FROM mtl_units_of_measure
3558 WHERE uom_code = l_uom_code;
3559 /* OPM Convergence */
3560 IF l_sec_uom_code IS NOT NULL THEN
3561
3562 SELECT unit_of_measure
3563 INTO l_sec_uom
3564 FROM mtl_units_of_measure
3565 WHERE uom_code = l_sec_uom_code;
3566
3567 END IF;
3568 -- dbms_output.put_line('main_process_po: Convert inspection uom code into uom');
3569
3570 -- Open RCV Transactions V cursor
3571 open rtv_cursor(
3572 l_po_header_id
3573 , l_organization_id
3574 , l_inventory_item_id
3575 , l_revision
3576 , l_revision_control -- Added for bug 3134272
3577 );
3578
3579 -- dbms_output.put_line('main_process_po: Opened RTV Cursor');
3580
3581 while(l_remaining_qty > 0)
3582 loop
3583 fetch rtv_cursor into
3584 l_rcv_transaction_id
3585 , l_receipt_source_code
3586 , l_rtv_uom
3587 , l_rtv_sec_uom; --OPM Convergence
3588
3589 if rtv_cursor%notfound then
3590 exit;
3591 end if;
3592
3593 -- Get quantity that can be still inspected
3594
3595 RCV_QUANTITIES_S.GET_AVAILABLE_QUANTITY (
3596 'INSPECT'
3597 , l_rcv_transaction_id
3598 , l_receipt_source_code
3599 , null
3600 , l_rcv_transaction_id
3601 , null
3602 , l_rtv_qty
3603 , l_tolerable_qty
3604 , l_rtv_uom );
3605
3606 if (l_rtv_qty > 0) then
3607
3608 -- dbms_output.put_line('main_process_po: convert rtv uom into uom code');
3609
3610 SELECT uom_code
3611 INTO l_rtv_uom_code
3612 FROM mtl_units_of_measure
3613 WHERE unit_of_measure = l_rtv_uom;
3614
3615 -- If inspection uom is not same as receipt uom, convert
3616
3617 if (l_uom_code <> l_rtv_uom_code) then
3618 l_rtv_qty := inv_convert.inv_um_convert(
3619 l_inventory_item_id
3620 , NULL
3621 , l_rtv_qty
3622 , l_rtv_uom_code
3623 , l_uom_code
3624 , NULL
3625 , NULL);
3626 end if;
3627
3628 if l_rtv_qty >= l_remaining_qty then
3629 l_rtv_qty := l_remaining_qty;
3630 l_rtv_sec_qty := l_sec_remaining_qty; --OPM Convergence
3631 l_remaining_qty := 0;
3632 l_sec_remaining_qty :=0; --OPM Convergence
3633 else
3634 l_remaining_qty := l_remaining_qty - l_rtv_qty;
3635 l_sec_remaining_qty := l_sec_remaining_qty - l_rtv_sec_qty; --OPM Convergence
3636 end if;
3637
3638 -- If required convert into primary unit of measure
3639 if (l_uom_code <> l_primary_uom_code) then
3640
3641 -- dbms_output.put_line('main_process_po: convert inspect uom into primary uom');
3642
3643 l_primary_qty := inv_convert.inv_um_convert(
3644 l_inventory_item_id
3645 , NULL
3646 , l_rtv_qty
3647 , l_uom_code
3648 , l_primary_uom_code
3649 , NULL
3650 , NULL);
3651 else
3652 l_primary_qty := l_rtv_qty;
3653 end if;
3654
3655 -- dbms_output.put_line('main_process_po: Calling insert_inspect_rec_rti');
3656
3657 -- Insert into rti, passing l_rtv_qty, inspection information
3658 insert_inspect_rec_rti (
3659 x_return_status => l_return_status
3660 , x_msg_count => l_msg_count
3661 , x_msg_data => l_msg_data
3662 , p_rcv_transaction_id => l_rcv_transaction_id
3663 , p_quantity => l_rtv_qty
3664 , p_uom => l_uom
3665 , p_inspection_code => l_inspection_code
3666 , p_quality_code => l_quality_code
3667 , p_transaction_date => l_transaction_date
3668 , p_transaction_type => l_transaction_type
3669 , p_vendor_lot => l_vendor_lot
3670 , p_reason_id => l_reason_id
3671 , p_primary_qty => l_primary_qty
3672 , p_organization_id => l_organization_id
3673 , p_comments => l_comments
3674 , p_attribute_category => l_attribute_category
3675 , p_attribute1 => l_attribute1
3676 , p_attribute2 => l_attribute2
3677 , p_attribute3 => l_attribute3
3678 , p_attribute4 => l_attribute4
3679 , p_attribute5 => l_attribute5
3680 , p_attribute6 => l_attribute6
3681 , p_attribute7 => l_attribute7
3682 , p_attribute8 => l_attribute8
3683 , p_attribute9 => l_attribute9
3684 , p_attribute10 => l_attribute10
3685 , p_attribute11 => l_attribute11
3686 , p_attribute12 => l_attribute12
3687 , p_attribute13 => l_attribute13
3688 , p_attribute14 => l_attribute14
3689 , p_attribute15 => l_attribute15
3690 , p_qa_collection_id => l_qa_collection_id
3691 , p_sec_uom => l_sec_uom --OPM Convergence
3692 , p_secondary_qty => l_rtv_sec_qty); --OPM Convergence
3693
3694 IF l_return_status = fnd_api.g_ret_sts_error THEN
3695 RAISE fnd_api.g_exc_error;
3696 END IF ;
3697
3698 IF l_return_status = fnd_api.g_ret_sts_unexp_error THEN
3699 RAISE fnd_api.g_exc_unexpected_error;
3700 END IF;
3701
3702 -- dbms_output.put_line('main_process_po: Successful insert_inspect_rec_rti');
3703
3704 -- Count successfully transacted qty
3705 l_transacted_qty := l_transacted_qty + l_rtv_qty;
3706 end if;
3707 end loop;
3708
3709 IF l_remaining_qty > 0 THEN
3710 FND_MESSAGE.set_name('INV','INV_QTY_LESS_OR_EQUAL');
3711 FND_MSG_PUB.Add;
3712 RAISE FND_API.G_EXC_ERROR;
3713 END IF;
3714
3715 close rtv_cursor;
3716
3717 exception
3718 when fnd_api.g_exc_error THEN
3719 rollback to inspect_main_po_sp;
3720
3721 x_return_status := fnd_api.g_ret_sts_error;
3722
3723 -- Get message count and data
3724 fnd_msg_pub.count_and_get
3725 ( p_count => x_msg_count
3726 , p_data => x_msg_data
3727 );
3728
3729 IF (rtv_cursor%isopen) THEN
3730 CLOSE rtv_cursor;
3731 END IF;
3732
3733 when fnd_api.g_exc_unexpected_error THEN
3734 rollback to inspect_main_po_sp;
3735
3736 x_return_status := fnd_api.g_ret_sts_unexp_error ;
3737
3738 -- Get message count and data
3739 fnd_msg_pub.count_and_get
3740 ( p_count => x_msg_count
3741 , p_data => x_msg_data
3742 );
3743
3744 IF (rtv_cursor%isopen) THEN
3745 CLOSE rtv_cursor;
3746 END IF;
3747
3748 when others THEN
3749 rollback to inspect_main_po_sp;
3750
3751 x_return_status := fnd_api.g_ret_sts_unexp_error ;
3752 --
3753 IF fnd_msg_pub.check_msg_level(fnd_msg_pub.g_msg_lvl_unexp_error)
3754 THEN
3755 fnd_msg_pub.add_exc_msg
3756 ( g_pkg_name
3757 , 'main_process_po'
3758 );
3759 END IF;
3760
3761 -- Get message count and data
3762 fnd_msg_pub.count_and_get
3763 ( p_count => x_msg_count
3764 , p_data => x_msg_data
3765 );
3766
3767 IF (rtv_cursor%isopen) THEN
3768 CLOSE rtv_cursor;
3769 END IF;
3770
3771 end main_process_po;
3772
3773 procedure main_process_intransit(
3774 x_return_status OUT NOCOPY VARCHAR2
3775 , x_msg_count OUT NOCOPY NUMBER
3776 , x_msg_data OUT NOCOPY VARCHAR2
3777 , p_inventory_item_id IN NUMBER
3778 , p_organization_id IN NUMBER
3779 , p_shipment_header_id IN NUMBER
3780 , p_revision IN VARCHAR2
3781 , p_uom_code IN VARCHAR2
3782 , p_quantity IN NUMBER
3783 , p_inspection_code IN VARCHAR2
3784 , p_quality_code IN VARCHAR2
3785 , p_transaction_type IN VARCHAR2
3786 , p_reason_id IN NUMBER
3787 , p_transaction_date IN DATE DEFAULT SYSDATE
3788 , p_qa_collection_id IN NUMBER DEFAULT NULL
3789 , p_vendor_lot IN VARCHAR2 DEFAULT NULL
3790 , p_comments IN VARCHAR2 DEFAULT NULL
3791 , p_attribute_category IN VARCHAR2 DEFAULT NULL
3792 , p_attribute1 IN VARCHAR2 DEFAULT NULL
3793 , p_attribute2 IN VARCHAR2 DEFAULT NULL
3794 , p_attribute3 IN VARCHAR2 DEFAULT NULL
3795 , p_attribute4 IN VARCHAR2 DEFAULT NULL
3796 , p_attribute5 IN VARCHAR2 DEFAULT NULL
3797 , p_attribute6 IN VARCHAR2 DEFAULT NULL
3798 , p_attribute7 IN VARCHAR2 DEFAULT NULL
3799 , p_attribute8 IN VARCHAR2 DEFAULT NULL
3800 , p_attribute9 IN VARCHAR2 DEFAULT NULL
3801 , p_attribute10 IN VARCHAR2 DEFAULT NULL
3802 , p_attribute11 IN VARCHAR2 DEFAULT NULL
3803 , p_attribute12 IN VARCHAR2 DEFAULT NULL
3804 , p_attribute13 IN VARCHAR2 DEFAULT NULL
3805 , p_attribute14 IN VARCHAR2 DEFAULT NULL
3806 , p_attribute15 IN VARCHAR2 DEFAULT NULL
3807 , p_secondary_qty IN NUMBER DEFAULT NULL) --OPM Convergence
3808 is
3809 l_inventory_item_id NUMBER := p_inventory_item_id;
3810 l_organization_id NUMBER := p_organization_id;
3811 l_revision VARCHAR2(10) := p_revision;
3812 l_revision_control NUMBER; -- Added for bug 3134272
3813 l_uom_code VARCHAR2(5) := p_uom_code;
3814 l_uom VARCHAR2(30);
3815 l_quantity NUMBER := p_quantity;
3816 l_shipment_header_id NUMBER := p_shipment_header_id;
3817
3818 l_inspection_code VARCHAR2(25) := p_inspection_code;
3819 l_quality_code VARCHAR2(25) := p_quality_code;
3820 l_transaction_date DATE := p_transaction_date;
3821 l_comments VARCHAR2(240) := p_comments;
3822 l_attribute_category VARCHAR2(30) := p_attribute_category;
3823 l_attribute1 VARCHAR2(150) := p_attribute1;
3824 l_attribute2 VARCHAR2(150) := p_attribute2;
3825 l_attribute3 VARCHAR2(150) := p_attribute3;
3826 l_attribute4 VARCHAR2(150) := p_attribute4;
3827 l_attribute5 VARCHAR2(150) := p_attribute5;
3828 l_attribute6 VARCHAR2(150) := p_attribute6;
3829 l_attribute7 VARCHAR2(150) := p_attribute7;
3830 l_attribute8 VARCHAR2(150) := p_attribute8;
3831 l_attribute9 VARCHAR2(150) := p_attribute9;
3832 l_attribute10 VARCHAR2(150) := p_attribute10;
3833 l_attribute11 VARCHAR2(150) := p_attribute11;
3834 l_attribute12 VARCHAR2(150) := p_attribute12;
3835 l_attribute13 VARCHAR2(150) := p_attribute13;
3836 l_attribute14 VARCHAR2(150) := p_attribute14;
3837 l_attribute15 VARCHAR2(150) := p_attribute15;
3838 l_transaction_type VARCHAR2(30) := p_transaction_type;
3839 l_vendor_lot VARCHAR2(30) := p_vendor_lot;
3840 l_reason_id NUMBER := p_reason_id;
3841
3842 l_qa_collection_id NUMBER := p_qa_collection_id;
3843
3844 l_primary_qty NUMBER;
3845 l_primary_uom_code varchar2(5);
3846
3847 l_rcv_transaction_id number;
3848 l_rtv_qty number;
3849 l_rtv_uom varchar2(25); /* Each */
3850 l_rtv_uom_code varchar2(5); /* Ea */
3851 l_receipt_source_code varchar2(25);
3852 l_tolerable_qty number;
3853
3854 l_remaining_qty number;
3855 l_transacted_qty number;
3856
3857 l_return_status varchar2(5);
3858 l_msg_count number;
3859 l_msg_data varchar2(1000);
3860
3861 l_secondary_qty NUMBER := p_secondary_qty; --OPM COnvergence
3862 l_remaining_sec_qty NUMBER; --OPM Convergence
3863 l_rtv_sec_uom VARCHAR2(25);--OPM Convergence
3864 l_sec_uom_code VARCHAR2(3);--OPM Convergence
3865 l_sec_uom VARCHAR2(25);--OPM Convergence
3866 l_rtv_sec_qty NUMBER;--OPM COnvergence
3867 l_sec_remaining_qty NUMBER; --OPM Convergence
3868
3869 /* cursor rtv_cursor(
3870 k_shipment_header_id number
3871 , k_organization_id number
3872 , k_inventory_item_id number
3873 , k_revision varchar2)
3874 is
3875 select
3876 rcv_transaction_id
3877 , receipt_source_code
3878 , unit_of_measure
3879 from rcv_transactions_v
3880 where receipt_source_code <> 'VENDOR'
3881 and shipment_header_id = k_shipment_header_id
3882 and to_organization_id = k_organization_id
3883 and item_id = k_inventory_item_id
3884 and (item_revision = k_revision OR
3885 item_revision is null and p_revision is null)
3886 and inspection_status_code = 'NOT INSPECTED'
3887 and routing_id = g_inspection_routing;
3888 */
3889 /* Bug 1542687: For performance reasons, the cursor is based on base tables below.*/
3890
3891 --bug 8405606 removed the condition for rt.inspection_status_code = 'NOT INSPECTED'
3892 cursor rtv_cursor(
3893 k_shipment_header_id number
3894 , k_organization_id number
3895 , k_inventory_item_id number
3896 , k_revision varchar2
3897 , k_revision_control number -- Added for bug 3134272
3898 ) is
3899 select
3900 rs.rcv_transaction_id
3901 , rsh.receipt_source_code
3902 , rs.unit_of_measure
3903 , rs.secondary_unit_of_measure --OPM Convergence
3904 from rcv_supply rs, rcv_transactions rt, rcv_shipment_headers rsh
3905 where rsh.receipt_source_code <> 'VENDOR'
3906 and rs.shipment_header_id = k_shipment_header_id
3907 and rs.to_organization_id = k_organization_id
3908 and rs.item_id = k_inventory_item_id
3909 and (k_revision_control = 2
3910 and Nvl(rs.item_revision,-1) = Nvl(k_revision,-1)
3911 OR k_revision_control = 1)
3912 -- Changed the above for bug 3134272
3913 and rs.rcv_transaction_id = rt.transaction_id
3914 and rsh.shipment_header_id = rs.shipment_header_id
3915 and rs.supply_type_code = 'RECEIVING'
3916 and rt.transaction_type <> 'UNORDERED'
3917 and rt.routing_header_id = g_inspection_routing;
3918 l_debug number := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
3919 begin
3920 x_return_status := fnd_api.g_ret_sts_success;
3921
3922 -- dbms_output.put_line('main_process_intransit: Just entering main_process_intransit');
3923
3924 --First check if the transaction date satisfies the validation.
3925 --If the transaction date is invalid then error out the transaction
3926 IF inv_rcv_common_apis.g_po_startup_value.sob_id IS NULL THEN
3927 --BUG 3444196: Used the HR view instead for performance reasons
3928 SELECT TO_NUMBER(hoi.org_information1)
3929 INTO inv_rcv_common_apis.g_po_startup_value.sob_id
3930 FROM hr_organization_information hoi
3931 WHERE hoi.organization_id = p_organization_id
3932 AND (hoi.org_information_context || '') = 'Accounting Information' ;
3933 END IF;
3934
3935 inv_rcv_common_apis.validate_trx_date(
3936 p_trx_date => SYSDATE
3937 , p_organization_id => p_organization_id
3938 , p_sob_id => inv_rcv_common_apis.g_po_startup_value.sob_id
3939 , x_return_status => x_return_status
3940 , x_error_code => x_msg_data
3941 );
3942
3943 IF x_return_status <> fnd_api.g_ret_sts_success THEN
3944 RETURN;
3945 END IF;
3946
3947 savepoint inspect_main_intransit_sp;
3948
3949 -- Quantity entered on form
3950 l_remaining_qty := l_quantity;
3951 l_sec_remaining_qty:= l_secondary_qty; --Bug 13924968
3952
3953 -- Quantity successfully transacted
3954 l_transacted_qty := 0;
3955
3956 -- One time fetch of item's primary uom code
3957 -- Fetching revision control for bug 3134272
3958 select primary_uom_code,revision_qty_control_code, secondary_uom_code --OPM Convergence
3959 into l_primary_uom_code,l_revision_control, l_sec_uom_code --OPM Convergence
3960 from mtl_system_items
3961 where organization_id = l_organization_id
3962 and inventory_item_id = l_inventory_item_id;
3963
3964 -- dbms_output.put_line('main_process_intransit: Fetched item primary uom code');
3965
3966 -- Purchasing/receiving uses unit of measure (Each)
3967 -- rather than uom code(Ea) and hence the following..
3968 -- This will be used later while inserting into interface table
3969
3970 SELECT unit_of_measure
3971 INTO l_uom
3972 FROM mtl_units_of_measure
3973 WHERE uom_code = l_uom_code;
3974
3975 /* OPM Convergence */
3976 IF l_sec_uom_code IS NOT NULL THEN
3977
3978 SELECT unit_of_measure
3979 INTO l_sec_uom
3980 FROM mtl_units_of_measure
3981 WHERE uom_code = l_sec_uom_code;
3982
3983 END IF;
3984
3985 -- dbms_output.put_line('main_process_intransit: Convert inspection uom code into uom');
3986
3987 -- Open RCV Transactions V cursor
3988 open rtv_cursor(
3989 l_shipment_header_id
3990 , l_organization_id
3991 , l_inventory_item_id
3992 , l_revision
3993 , l_revision_control -- Added for bug 3134272
3994 );
3995
3996 -- dbms_output.put_line('main_process_intransit: Opened RTV Cursor');
3997
3998 while(l_remaining_qty > 0)
3999 loop
4000 fetch rtv_cursor into
4001 l_rcv_transaction_id
4002 , l_receipt_source_code
4003 , l_rtv_uom
4004 , l_rtv_sec_uom; --OPM Convergence
4005
4006 if rtv_cursor%notfound then
4007 exit;
4008 end if;
4009
4010 -- Get quantity that can be still inspected
4011
4012 RCV_QUANTITIES_S.GET_AVAILABLE_QUANTITY (
4013 'INSPECT'
4014 , l_rcv_transaction_id
4015 , l_receipt_source_code
4016 , null
4017 , l_rcv_transaction_id
4018 , null
4019 , l_rtv_qty
4020 , l_tolerable_qty
4021 , l_rtv_uom );
4022
4023 if (l_rtv_qty > 0) then
4024
4025 -- dbms_output.put_line('main_process_intransit: convert rtv uom into uom code');
4026
4027 SELECT uom_code
4028 INTO l_rtv_uom_code
4029 FROM mtl_units_of_measure
4030 WHERE unit_of_measure = l_rtv_uom;
4031
4032 -- If inspection uom is not same as receipt uom, convert
4033
4034 if (l_uom_code <> l_rtv_uom_code) then
4035 l_rtv_qty := inv_convert.inv_um_convert(
4036 l_inventory_item_id
4037 , NULL
4038 , l_rtv_qty
4039 , l_rtv_uom_code
4040 , l_uom_code
4041 , NULL
4042 , NULL);
4043 end if;
4044
4045 if l_rtv_qty >= l_remaining_qty then
4046 l_rtv_qty := l_remaining_qty;
4047 l_rtv_sec_qty := l_sec_remaining_qty;
4048 l_remaining_qty := 0;
4049 l_sec_remaining_qty := 0;
4050 else
4051 l_remaining_qty := l_remaining_qty - l_rtv_qty;
4052 l_sec_remaining_qty := l_sec_remaining_qty - l_rtv_sec_qty;
4053 end if;
4054
4055 -- If required convert into primary unit of measure
4056 if (l_uom_code <> l_primary_uom_code) then
4057
4058 -- dbms_output.put_line('main_process_intransit: convet inspect uom into primary uom');
4059
4060 l_primary_qty := inv_convert.inv_um_convert(
4061 l_inventory_item_id
4062 , NULL
4063 , l_rtv_qty
4064 , l_uom_code
4065 , l_primary_uom_code
4066 , NULL
4067 , NULL);
4068 else
4069 l_primary_qty := l_rtv_qty;
4070 end if;
4071
4072 -- dbms_output.put_line('main_process_intransit: Calling insert_inspect_rec_rti');
4073
4074 -- Insert into rti, passing l_rtv_qty, inspection information
4075 insert_inspect_rec_rti (
4076 x_return_status => l_return_status
4077 , x_msg_count => l_msg_count
4078 , x_msg_data => l_msg_data
4079 , p_rcv_transaction_id => l_rcv_transaction_id
4080 , p_quantity => l_rtv_qty
4081 , p_uom => l_uom
4082 , p_inspection_code => l_inspection_code
4083 , p_quality_code => l_quality_code
4084 , p_transaction_date => l_transaction_date
4085 , p_transaction_type => l_transaction_type
4086 , p_vendor_lot => l_vendor_lot
4087 , p_reason_id => l_reason_id
4088 , p_primary_qty => l_primary_qty
4089 , p_organization_id => l_organization_id
4090 , p_comments => l_comments
4091 , p_attribute_category => l_attribute_category
4092 , p_attribute1 => l_attribute1
4093 , p_attribute2 => l_attribute2
4094 , p_attribute3 => l_attribute3
4095 , p_attribute4 => l_attribute4
4096 , p_attribute5 => l_attribute5
4097 , p_attribute6 => l_attribute6
4098 , p_attribute7 => l_attribute7
4099 , p_attribute8 => l_attribute8
4100 , p_attribute9 => l_attribute9
4101 , p_attribute10 => l_attribute10
4102 , p_attribute11 => l_attribute11
4103 , p_attribute12 => l_attribute12
4104 , p_attribute13 => l_attribute13
4105 , p_attribute14 => l_attribute14
4106 , p_attribute15 => l_attribute15
4107 , p_qa_collection_id => l_qa_collection_id
4108 , p_sec_uom => l_sec_uom --OPM Convergence
4109 , p_secondary_qty => l_rtv_sec_qty); --OPM Convergence
4110
4111 IF l_return_status = fnd_api.g_ret_sts_error THEN
4112 RAISE fnd_api.g_exc_error;
4113 END IF ;
4114
4115 IF l_return_status = fnd_api.g_ret_sts_unexp_error THEN
4116 RAISE fnd_api.g_exc_unexpected_error;
4117 END IF;
4118
4119 -- dbms_output.put_line('main_process_intransit: Successful insert_inspect_rec_rti');
4120
4121 -- Count successfully transacted qty
4122 l_transacted_qty := l_transacted_qty + l_rtv_qty;
4123 end if;
4124 end loop;
4125
4126 IF l_remaining_qty > 0 THEN
4127 FND_MESSAGE.set_name('INV','INV_QTY_LESS_OR_EQUAL');
4128 FND_MSG_PUB.Add;
4129 RAISE FND_API.G_EXC_ERROR;
4130 END IF;
4131
4132 close rtv_cursor;
4133
4134 exception
4135 when fnd_api.g_exc_error THEN
4136 rollback to inspect_main_intransit_sp;
4137
4138 x_return_status := fnd_api.g_ret_sts_error;
4139
4140 -- Get message count and data
4141 fnd_msg_pub.count_and_get
4142 ( p_count => x_msg_count
4143 , p_data => x_msg_data
4144 );
4145
4146 IF (rtv_cursor%isopen) THEN
4147 CLOSE rtv_cursor;
4148 END IF;
4149
4150 when fnd_api.g_exc_unexpected_error THEN
4151 rollback to inspect_main_intransit_sp;
4152
4153 x_return_status := fnd_api.g_ret_sts_unexp_error ;
4154
4155 -- Get message count and data
4156 fnd_msg_pub.count_and_get
4157 ( p_count => x_msg_count
4158 , p_data => x_msg_data
4159 );
4160
4161 IF (rtv_cursor%isopen) THEN
4162 CLOSE rtv_cursor;
4163 END IF;
4164
4165 when others THEN
4166 rollback to inspect_main_intransit_sp;
4167
4168 x_return_status := fnd_api.g_ret_sts_unexp_error ;
4169 --
4170 IF fnd_msg_pub.check_msg_level(fnd_msg_pub.g_msg_lvl_unexp_error)
4171 THEN
4172 fnd_msg_pub.add_exc_msg
4173 ( g_pkg_name
4174 , 'main_process_intransit'
4175 );
4176 END IF;
4177
4178 -- Get message count and data
4179 fnd_msg_pub.count_and_get
4180 ( p_count => x_msg_count
4181 , p_data => x_msg_data
4182 );
4183
4184 IF (rtv_cursor%isopen) THEN
4185 CLOSE rtv_cursor;
4186 END IF;
4187
4188 end main_process_intransit;
4189
4190 procedure main_process_rma(
4191 x_return_status OUT NOCOPY VARCHAR2
4192 , x_msg_count OUT NOCOPY NUMBER
4193 , x_msg_data OUT NOCOPY VARCHAR2
4194 , p_inventory_item_id IN NUMBER
4195 , p_organization_id IN NUMBER
4196 , p_oe_order_header_id IN NUMBER
4197 , p_revision IN VARCHAR2
4198 , p_uom_code IN VARCHAR2
4199 , p_quantity IN NUMBER
4200 , p_inspection_code IN VARCHAR2
4201 , p_quality_code IN VARCHAR2
4202 , p_transaction_type IN VARCHAR2
4203 , p_reason_id IN NUMBER
4204 , p_transaction_date IN DATE DEFAULT SYSDATE
4205 , p_qa_collection_id IN NUMBER DEFAULT NULL
4206 , p_vendor_lot IN VARCHAR2 DEFAULT NULL
4207 , p_comments IN VARCHAR2 DEFAULT NULL
4208 , p_attribute_category IN VARCHAR2 DEFAULT NULL
4209 , p_attribute1 IN VARCHAR2 DEFAULT NULL
4210 , p_attribute2 IN VARCHAR2 DEFAULT NULL
4211 , p_attribute3 IN VARCHAR2 DEFAULT NULL
4212 , p_attribute4 IN VARCHAR2 DEFAULT NULL
4213 , p_attribute5 IN VARCHAR2 DEFAULT NULL
4214 , p_attribute6 IN VARCHAR2 DEFAULT NULL
4215 , p_attribute7 IN VARCHAR2 DEFAULT NULL
4216 , p_attribute8 IN VARCHAR2 DEFAULT NULL
4217 , p_attribute9 IN VARCHAR2 DEFAULT NULL
4218 , p_attribute10 IN VARCHAR2 DEFAULT NULL
4219 , p_attribute11 IN VARCHAR2 DEFAULT NULL
4220 , p_attribute12 IN VARCHAR2 DEFAULT NULL
4221 , p_attribute13 IN VARCHAR2 DEFAULT NULL
4222 , p_attribute14 IN VARCHAR2 DEFAULT NULL
4223 , p_attribute15 IN VARCHAR2 DEFAULT NULL
4224 , p_secondary_qty IN NUMBER DEFAULT NULL) --OPM Convergence)
4225 is
4226 l_inventory_item_id NUMBER := p_inventory_item_id;
4227 l_organization_id NUMBER := p_organization_id;
4228 l_revision VARCHAR2(10) := p_revision;
4229 l_revision_control NUMBER; -- Added for bug 3134272
4230 l_uom_code VARCHAR2(5) := p_uom_code;
4231 l_uom VARCHAR2(30);
4232 l_quantity NUMBER := p_quantity;
4233 l_oe_order_header_id NUMBER := p_oe_order_header_id;
4234
4235 l_inspection_code VARCHAR2(25) := p_inspection_code;
4236 l_quality_code VARCHAR2(25) := p_quality_code;
4237 l_transaction_date DATE := p_transaction_date;
4238 l_comments VARCHAR2(240) := p_comments;
4239 l_attribute_category VARCHAR2(30) := p_attribute_category;
4240 l_attribute1 VARCHAR2(150) := p_attribute1;
4241 l_attribute2 VARCHAR2(150) := p_attribute2;
4242 l_attribute3 VARCHAR2(150) := p_attribute3;
4243 l_attribute4 VARCHAR2(150) := p_attribute4;
4244 l_attribute5 VARCHAR2(150) := p_attribute5;
4245 l_attribute6 VARCHAR2(150) := p_attribute6;
4246 l_attribute7 VARCHAR2(150) := p_attribute7;
4247 l_attribute8 VARCHAR2(150) := p_attribute8;
4248 l_attribute9 VARCHAR2(150) := p_attribute9;
4249 l_attribute10 VARCHAR2(150) := p_attribute10;
4250 l_attribute11 VARCHAR2(150) := p_attribute11;
4251 l_attribute12 VARCHAR2(150) := p_attribute12;
4252 l_attribute13 VARCHAR2(150) := p_attribute13;
4253 l_attribute14 VARCHAR2(150) := p_attribute14;
4254 l_attribute15 VARCHAR2(150) := p_attribute15;
4255 l_transaction_type VARCHAR2(30) := p_transaction_type;
4256 l_vendor_lot VARCHAR2(30) := p_vendor_lot;
4257 l_reason_id NUMBER := p_reason_id;
4258
4259 l_qa_collection_id NUMBER := p_qa_collection_id;
4260
4261 l_primary_qty NUMBER;
4262 l_primary_uom_code varchar2(5);
4263
4264 l_rcv_transaction_id number;
4265 l_rtv_qty number;
4266 l_rtv_uom varchar2(25); /* Each */
4267 l_rtv_uom_code varchar2(5); /* Ea */
4268 l_receipt_source_code varchar2(25);
4269 l_tolerable_qty number;
4270
4271 l_remaining_qty number;
4272 l_transacted_qty number;
4273
4274 l_return_status varchar2(5);
4275 l_msg_count number;
4276 l_msg_data varchar2(1000);
4277
4278 l_secondary_qty NUMBER := p_secondary_qty; --OPM COnvergence
4279 l_remaining_sec_qty NUMBER; --OPM Convergence
4280 l_rtv_sec_uom VARCHAR2(25);--OPM Convergence
4281 l_sec_uom_code VARCHAR2(3);--OPM Convergence
4282 l_sec_uom VARCHAR2(25);--OPM Convergence
4283 l_rtv_sec_qty NUMBER;--OPM COnvergence
4284 l_sec_remaining_qty NUMBER; --OPM Convergence
4285 /* cursor rtv_cursor(
4286 k_oe_order_header_id number
4287 , k_organization_id number
4288 , k_inventory_item_id number
4289 , k_revision varchar2)
4290 is
4291 select
4292 rcv_transaction_id
4293 , receipt_source_code
4294 , unit_of_measure
4295 from rcv_transactions_v
4296 where receipt_source_code = 'CUSTOMER'
4297 and oe_order_header_id = k_oe_order_header_id
4298 and to_organization_id = k_organization_id
4299 and item_id = k_inventory_item_id
4300 and (item_revision = k_revision OR
4301 item_revision is null and p_revision is null)
4302 and inspection_status_code = 'NOT INSPECTED'
4303 and routing_id = g_inspection_routing;
4304 */
4305 /* Bug 1542687: For performance reasons, the cursor is based on base tables below.*/
4306
4307 --bug 8405606 removed the condition for rt.inspection_status_code = 'NOT INSPECTED'
4308
4309 cursor rtv_cursor(
4310 k_oe_order_header_id number
4311 , k_organization_id number
4312 , k_inventory_item_id number
4313 , k_revision varchar2
4314 , k_revision_control number -- Added for bug 3134272
4315 ) is
4316 select
4317 rs.rcv_transaction_id
4318 , rsh.receipt_source_code
4319 , rs.unit_of_measure
4320 , rs.secondary_unit_of_measure --OPM Convergence
4321 from rcv_supply rs, rcv_transactions rt, rcv_shipment_headers rsh
4322 where rsh.receipt_source_code = 'CUSTOMER'
4323 and rs.oe_order_header_id = k_oe_order_header_id
4324 and rs.to_organization_id = k_organization_id
4325 and rs.item_id = k_inventory_item_id
4326 and (k_revision_control = 2
4327 and Nvl(rs.item_revision,-1) = Nvl(k_revision,-1)
4328 OR k_revision_control = 1)
4329 -- Changed the above for bug 3134272
4330 and rs.rcv_transaction_id = rt.transaction_id
4331 and rsh.shipment_header_id = rs.shipment_header_id
4332 and rs.supply_type_code = 'RECEIVING'
4333 and rt.transaction_type <> 'UNORDERED'
4334 and rt.routing_header_id = g_inspection_routing;
4335 l_debug number := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
4336 begin
4337 x_return_status := fnd_api.g_ret_sts_success;
4338
4339 -- dbms_output.put_line('main_process_rma: Just entering main_process_rma');
4340 --First check if the transaction date satisfies the validation.
4341 --If the transaction date is invalid then error out the transaction
4342 IF inv_rcv_common_apis.g_po_startup_value.sob_id IS NULL THEN
4343 --BUG 3444196: Used the HR view instead for performance reasons
4344 SELECT TO_NUMBER(hoi.org_information1)
4345 INTO inv_rcv_common_apis.g_po_startup_value.sob_id
4346 FROM hr_organization_information hoi
4347 WHERE hoi.organization_id = p_organization_id
4348 AND (hoi.org_information_context || '') = 'Accounting Information' ;
4349 END IF;
4350
4351 inv_rcv_common_apis.validate_trx_date(
4352 p_trx_date => SYSDATE
4353 , p_organization_id => p_organization_id
4354 , p_sob_id => inv_rcv_common_apis.g_po_startup_value.sob_id
4355 , x_return_status => x_return_status
4356 , x_error_code => x_msg_data
4357 );
4358
4359 IF x_return_status <> fnd_api.g_ret_sts_success THEN
4360 RETURN;
4361 END IF;
4362
4363 savepoint inspect_main_rma_sp;
4364
4365 -- Quantity entered on form
4366 l_remaining_qty := l_quantity;
4367 l_sec_remaining_qty:= l_secondary_qty; --Bug 13924968
4368
4369 -- Quantity successfully transacted
4370 l_transacted_qty := 0;
4371
4372 -- One time fetch of item's primary uom code
4373 -- Fetching revision control for bug 3134272
4374 select primary_uom_code,revision_qty_control_code, secondary_uom_code --OPM Convergence
4375 into l_primary_uom_code,l_revision_control, l_sec_uom_code --OPM Convergence
4376 from mtl_system_items
4377 where organization_id = l_organization_id
4378 and inventory_item_id = l_inventory_item_id;
4379
4380 -- dbms_output.put_line('main_process_rma: Fetched item primary uom code');
4381
4382 -- Purchasing/receiving uses unit of measure (Each)
4383 -- rather than uom code(Ea) and hence the following..
4384 -- This will be used later while inserting into interface table
4385
4386 SELECT unit_of_measure
4387 INTO l_uom
4388 FROM mtl_units_of_measure
4389 WHERE uom_code = l_uom_code;
4390
4391 /* OPM Convergence */
4392 IF l_sec_uom_code IS NOT NULL THEN
4393
4394 SELECT unit_of_measure
4395 INTO l_sec_uom
4396 FROM mtl_units_of_measure
4397 WHERE uom_code = l_sec_uom_code;
4398
4399 END IF;
4400 -- dbms_output.put_line('main_process_rma: Convert inspection uom code into uom');
4401
4402 -- Open RCV Transactions V cursor
4403 open rtv_cursor(
4404 l_oe_order_header_id
4405 , l_organization_id
4406 , l_inventory_item_id
4407 , l_revision
4408 , l_revision_control -- added for bug 3134272
4409 );
4410
4411 -- dbms_output.put_line('main_process_rma: Opened RTV Cursor');
4412
4413 while(l_remaining_qty > 0)
4414 loop
4415 fetch rtv_cursor into
4416 l_rcv_transaction_id
4417 , l_receipt_source_code
4418 , l_rtv_uom
4419 , l_rtv_sec_uom;
4420
4421 if rtv_cursor%notfound then
4422 exit;
4423 end if;
4424
4425 -- Get quantity that can be still inspected
4426
4427 RCV_QUANTITIES_S.GET_AVAILABLE_QUANTITY (
4428 'INSPECT'
4429 , l_rcv_transaction_id
4430 , l_receipt_source_code
4431 , null
4432 , l_rcv_transaction_id
4433 , null
4434 , l_rtv_qty
4435 , l_tolerable_qty
4436 , l_rtv_uom );
4437
4438 if (l_rtv_qty > 0) then
4439
4440 -- dbms_output.put_line('main_process_rma: convert rtv uom into uom code');
4441
4442 SELECT uom_code
4443 INTO l_rtv_uom_code
4444 FROM mtl_units_of_measure
4445 WHERE unit_of_measure = l_rtv_uom;
4446
4447 -- If inspection uom is not same as receipt uom, convert
4448
4449 if (l_uom_code <> l_rtv_uom_code) then
4450 l_rtv_qty := inv_convert.inv_um_convert(
4451 l_inventory_item_id
4452 , NULL
4453 , l_rtv_qty
4454 , l_rtv_uom_code
4455 , l_uom_code
4456 , NULL
4457 , NULL);
4458 end if;
4459
4460 if l_rtv_qty >= l_remaining_qty then
4461 l_rtv_qty := l_remaining_qty;
4462 l_rtv_sec_qty := l_sec_remaining_qty; --Bug 13924968
4463 l_remaining_qty := 0;
4464 l_sec_remaining_qty :=0; --Bug 13924968
4465 else
4466 l_remaining_qty := l_remaining_qty - l_rtv_qty;
4467 l_sec_remaining_qty := l_sec_remaining_qty - l_rtv_sec_qty; --Bug 13924968
4468 end if;
4469
4470 -- If required convert into primary unit of measure
4471 if (l_uom_code <> l_primary_uom_code) then
4472
4473 -- dbms_output.put_line('main_process_rma: convet inspect uom into primary uom');
4474
4475 l_primary_qty := inv_convert.inv_um_convert(
4476 l_inventory_item_id
4477 , NULL
4478 , l_rtv_qty
4479 , l_uom_code
4480 , l_primary_uom_code
4481 , NULL
4482 , NULL);
4483 else
4484 l_primary_qty := l_rtv_qty;
4485 end if;
4486
4487 -- dbms_output.put_line('main_process_rma: Calling insert_inspect_rec_rti');
4488
4489 -- Insert into rti, passing l_rtv_qty, inspection information
4490 insert_inspect_rec_rti (
4491 x_return_status => l_return_status
4492 , x_msg_count => l_msg_count
4493 , x_msg_data => l_msg_data
4494 , p_rcv_transaction_id => l_rcv_transaction_id
4495 , p_quantity => l_rtv_qty
4496 , p_uom => l_uom
4497 , p_inspection_code => l_inspection_code
4498 , p_quality_code => l_quality_code
4499 , p_transaction_date => l_transaction_date
4500 , p_transaction_type => l_transaction_type
4501 , p_vendor_lot => l_vendor_lot
4502 , p_reason_id => l_reason_id
4503 , p_primary_qty => l_primary_qty
4504 , p_organization_id => l_organization_id
4505 , p_comments => l_comments
4506 , p_attribute_category => l_attribute_category
4507 , p_attribute1 => l_attribute1
4508 , p_attribute2 => l_attribute2
4509 , p_attribute3 => l_attribute3
4510 , p_attribute4 => l_attribute4
4511 , p_attribute5 => l_attribute5
4512 , p_attribute6 => l_attribute6
4513 , p_attribute7 => l_attribute7
4514 , p_attribute8 => l_attribute8
4515 , p_attribute9 => l_attribute9
4516 , p_attribute10 => l_attribute10
4517 , p_attribute11 => l_attribute11
4518 , p_attribute12 => l_attribute12
4519 , p_attribute13 => l_attribute13
4520 , p_attribute14 => l_attribute14
4521 , p_attribute15 => l_attribute15
4522 , p_qa_collection_id => l_qa_collection_id
4523 , p_sec_uom => l_sec_uom --OPM Convergence
4524 , p_secondary_qty => l_rtv_sec_qty); --OPM Convergence);
4525
4526 IF l_return_status = fnd_api.g_ret_sts_error THEN
4527 RAISE fnd_api.g_exc_error;
4528 END IF ;
4529
4530 IF l_return_status = fnd_api.g_ret_sts_unexp_error THEN
4531 RAISE fnd_api.g_exc_unexpected_error;
4532 END IF;
4533
4534 -- dbms_output.put_line('main_process_rma: Successful insert_inspect_rec_rti');
4535
4536 -- Count successfully transacted qty
4537 l_transacted_qty := l_transacted_qty + l_rtv_qty;
4538 end if;
4539 end loop;
4540
4541 IF l_remaining_qty > 0 THEN
4542 FND_MESSAGE.set_name('INV','INV_QTY_LESS_OR_EQUAL');
4543 FND_MSG_PUB.Add;
4544 RAISE FND_API.G_EXC_ERROR;
4545 END IF;
4546
4547 close rtv_cursor;
4548
4549 exception
4550 when fnd_api.g_exc_error THEN
4551 rollback to inspect_main_rma_sp;
4552
4553 x_return_status := fnd_api.g_ret_sts_error;
4554
4555 -- Get message count and data
4556 fnd_msg_pub.count_and_get
4557 ( p_count => x_msg_count
4558 , p_data => x_msg_data
4559 );
4560
4561 IF (rtv_cursor%isopen) THEN
4562 CLOSE rtv_cursor;
4563 END IF;
4564
4565 when fnd_api.g_exc_unexpected_error THEN
4566 rollback to inspect_main_rma_sp;
4567
4568 x_return_status := fnd_api.g_ret_sts_unexp_error ;
4569
4570 -- Get message count and data
4571 fnd_msg_pub.count_and_get
4572 ( p_count => x_msg_count
4573 , p_data => x_msg_data
4574 );
4575
4576 IF (rtv_cursor%isopen) THEN
4577 CLOSE rtv_cursor;
4578 END IF;
4579
4580 when others THEN
4581 rollback to inspect_main_rma_sp;
4582
4583 x_return_status := fnd_api.g_ret_sts_unexp_error ;
4584 --
4585 IF fnd_msg_pub.check_msg_level(fnd_msg_pub.g_msg_lvl_unexp_error)
4586 THEN
4587 fnd_msg_pub.add_exc_msg
4588 ( g_pkg_name
4589 , 'main_process_rma'
4590 );
4591 END IF;
4592
4593 -- Get message count and data
4594 fnd_msg_pub.count_and_get
4595 ( p_count => x_msg_count
4596 , p_data => x_msg_data
4597 );
4598
4599 IF (rtv_cursor%isopen) THEN
4600 CLOSE rtv_cursor;
4601 END IF;
4602 end main_process_rma;
4603
4604 procedure main_process_receipt(
4605 x_return_status OUT NOCOPY VARCHAR2
4606 , x_msg_count OUT NOCOPY NUMBER
4607 , x_msg_data OUT NOCOPY VARCHAR2
4608 , p_inventory_item_id IN NUMBER
4609 , p_organization_id IN NUMBER
4610 , p_receipt_num IN VARCHAR2
4611 , p_revision IN VARCHAR2
4612 , p_uom_code IN VARCHAR2
4613 , p_quantity IN NUMBER
4614 , p_inspection_code IN VARCHAR2
4615 , p_quality_code IN VARCHAR2
4616 , p_transaction_type IN VARCHAR2
4617 , p_reason_id IN NUMBER
4618 , p_transaction_date IN DATE DEFAULT SYSDATE
4619 , p_qa_collection_id IN NUMBER DEFAULT NULL
4620 , p_vendor_lot IN VARCHAR2 DEFAULT NULL
4621 , p_comments IN VARCHAR2 DEFAULT NULL
4622 , p_attribute_category IN VARCHAR2 DEFAULT NULL
4623 , p_attribute1 IN VARCHAR2 DEFAULT NULL
4624 , p_attribute2 IN VARCHAR2 DEFAULT NULL
4625 , p_attribute3 IN VARCHAR2 DEFAULT NULL
4626 , p_attribute4 IN VARCHAR2 DEFAULT NULL
4627 , p_attribute5 IN VARCHAR2 DEFAULT NULL
4628 , p_attribute6 IN VARCHAR2 DEFAULT NULL
4629 , p_attribute7 IN VARCHAR2 DEFAULT NULL
4630 , p_attribute8 IN VARCHAR2 DEFAULT NULL
4631 , p_attribute9 IN VARCHAR2 DEFAULT NULL
4632 , p_attribute10 IN VARCHAR2 DEFAULT NULL
4633 , p_attribute11 IN VARCHAR2 DEFAULT NULL
4634 , p_attribute12 IN VARCHAR2 DEFAULT NULL
4635 , p_attribute13 IN VARCHAR2 DEFAULT NULL
4636 , p_attribute14 IN VARCHAR2 DEFAULT NULL
4637 , p_attribute15 IN VARCHAR2 DEFAULT NULL
4638 , p_secondary_qty IN NUMBER DEFAULT NULL) --OPM Convergence)
4639 is
4640 l_inventory_item_id NUMBER := p_inventory_item_id;
4641 l_organization_id NUMBER := p_organization_id;
4642 l_revision VARCHAR2(10) := p_revision;
4643 l_revision_control NUMBER; -- Added for bug 3134272
4644 l_uom_code VARCHAR2(5) := p_uom_code;
4645 l_uom VARCHAR2(30);
4646 l_quantity NUMBER := p_quantity;
4647 l_receipt_num NUMBER := p_receipt_num;
4648
4649 l_inspection_code VARCHAR2(25) := p_inspection_code;
4650 l_quality_code VARCHAR2(25) := p_quality_code;
4651 l_transaction_date DATE := p_transaction_date;
4652 l_comments VARCHAR2(240) := p_comments;
4653 l_attribute_category VARCHAR2(30) := p_attribute_category;
4654 l_attribute1 VARCHAR2(150) := p_attribute1;
4655 l_attribute2 VARCHAR2(150) := p_attribute2;
4656 l_attribute3 VARCHAR2(150) := p_attribute3;
4657 l_attribute4 VARCHAR2(150) := p_attribute4;
4658 l_attribute5 VARCHAR2(150) := p_attribute5;
4659 l_attribute6 VARCHAR2(150) := p_attribute6;
4660 l_attribute7 VARCHAR2(150) := p_attribute7;
4661 l_attribute8 VARCHAR2(150) := p_attribute8;
4662 l_attribute9 VARCHAR2(150) := p_attribute9;
4663 l_attribute10 VARCHAR2(150) := p_attribute10;
4664 l_attribute11 VARCHAR2(150) := p_attribute11;
4665 l_attribute12 VARCHAR2(150) := p_attribute12;
4666 l_attribute13 VARCHAR2(150) := p_attribute13;
4667 l_attribute14 VARCHAR2(150) := p_attribute14;
4668 l_attribute15 VARCHAR2(150) := p_attribute15;
4669 l_transaction_type VARCHAR2(30) := p_transaction_type;
4670 l_vendor_lot VARCHAR2(30) := p_vendor_lot;
4671 l_reason_id NUMBER := p_reason_id;
4672
4673 l_qa_collection_id NUMBER := p_qa_collection_id;
4674
4675 l_primary_qty NUMBER;
4676 l_primary_uom_code varchar2(5);
4677
4678 l_rcv_transaction_id number;
4679 l_rtv_qty number;
4680 l_rtv_uom varchar2(25); /* Each */
4681 l_rtv_uom_code varchar2(5); /* Ea */
4682 l_receipt_source_code varchar2(25);
4683 l_tolerable_qty number;
4684
4685 l_remaining_qty number;
4686 l_transacted_qty number;
4687
4688 l_return_status varchar2(5);
4689 l_msg_count number;
4690 l_msg_data varchar2(1000);
4691
4692 l_secondary_qty NUMBER := p_secondary_qty; --OPM COnvergence
4693 l_remaining_sec_qty NUMBER; --OPM Convergence
4694 l_rtv_sec_uom VARCHAR2(25);--OPM Convergence
4695 l_sec_uom_code VARCHAR2(3);--OPM Convergence
4696 l_sec_uom VARCHAR2(25);--OPM Convergence
4697 l_rtv_sec_qty NUMBER;--OPM COnvergence
4698 l_sec_remaining_qty NUMBER; --OPM Convergence
4699
4700 /* cursor rtv_cursor(
4701 k_receipt_num varchar2
4702 , k_organization_id number
4703 , k_inventory_item_id number
4704 , k_revision varchar2)
4705 is
4706 select
4707 rcv_transaction_id
4708 , receipt_source_code
4709 , unit_of_measure
4710 from rcv_transactions_v
4711 where receipt_num = k_receipt_num
4712 and to_organization_id = k_organization_id
4713 and item_id = k_inventory_item_id
4714 and (item_revision = k_revision OR
4715 item_revision is null and p_revision is null)
4716 and inspection_status_code = 'NOT INSPECTED'
4717 and routing_id = g_inspection_routing;
4718 */
4719 /* Bug 1542687: For performance reasons, the cursor is based on base tables below.*/
4720
4721 --bug 8405606 removed the condition for rt.inspection_status_code = 'NOT INSPECTED'
4722
4723 cursor rtv_cursor(
4724 k_receipt_num varchar2
4725 , k_organization_id number
4726 , k_inventory_item_id number
4727 , k_revision varchar2
4728 , k_revision_control number -- Added for bug 3134272
4729 ) is
4730 select
4731 rs.rcv_transaction_id
4732 , rsh.receipt_source_code
4733 , rs.unit_of_measure
4734 , rs.secondary_unit_of_measure --OPM Convergence
4735 from rcv_supply rs, rcv_transactions rt, rcv_shipment_headers rsh
4736 where rsh.receipt_num = k_receipt_num
4737 and rs.to_organization_id = k_organization_id
4738 and rs.item_id = k_inventory_item_id
4739 and (k_revision_control = 2
4740 and Nvl(rs.item_revision,-1) = Nvl(k_revision,-1)
4741 OR k_revision_control = 1)
4742 -- Changed the above for bug 3134272
4743 and rs.rcv_transaction_id = rt.transaction_id
4744 and rsh.shipment_header_id = rs.shipment_header_id
4745 and rs.supply_type_code = 'RECEIVING'
4746 and rt.transaction_type <> 'UNORDERED'
4747 and rt.routing_header_id = g_inspection_routing;
4748 l_debug number := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
4749 begin
4750 x_return_status := fnd_api.g_ret_sts_success;
4751
4752 -- dbms_output.put_line('main_process_receipt: Just entering main_process_receipt');
4753 --First check if the transaction date satisfies the validation.
4754 --If the transaction date is invalid then error out the transaction
4755 IF inv_rcv_common_apis.g_po_startup_value.sob_id IS NULL THEN
4756 --BUG 3444196: Used the HR view instead for performance reasons
4757 SELECT TO_NUMBER(hoi.org_information1)
4758 INTO inv_rcv_common_apis.g_po_startup_value.sob_id
4759 FROM hr_organization_information hoi
4760 WHERE hoi.organization_id = p_organization_id
4761 AND (hoi.org_information_context || '') = 'Accounting Information' ;
4762 END IF;
4763
4764 inv_rcv_common_apis.validate_trx_date(
4765 p_trx_date => SYSDATE
4766 , p_organization_id => p_organization_id
4767 , p_sob_id => inv_rcv_common_apis.g_po_startup_value.sob_id
4768 , x_return_status => x_return_status
4769 , x_error_code => x_msg_data
4770 );
4771
4772 IF x_return_status <> fnd_api.g_ret_sts_success THEN
4773 RETURN;
4774 END IF;
4775
4776 savepoint inspect_main_receipt_sp;
4777
4778 -- Quantity entered on form
4779 l_remaining_qty := l_quantity;
4780 l_sec_remaining_qty:= l_secondary_qty; --Bug 13924968
4781
4782 -- Quantity successfully transacted
4783 l_transacted_qty := 0;
4784
4785 -- One time fetch of item's primary uom code
4786 -- Fetching revision control for bug 3134272
4787 select primary_uom_code,revision_qty_control_code, secondary_uom_code --OPM Convergence
4788 into l_primary_uom_code,l_revision_control, l_sec_uom_code --OPM Convergence
4789 from mtl_system_items
4790 where organization_id = l_organization_id
4791 and inventory_item_id = l_inventory_item_id;
4792
4793 -- dbms_output.put_line('main_process_receipt: Fetched item primary uom code');
4794
4795 -- Purchasing/receiving uses unit of measure (Each)
4796 -- rather than uom code(Ea) and hence the following..
4797 -- This will be used later while inserting into interface table
4798
4799 SELECT unit_of_measure
4800 INTO l_uom
4801 FROM mtl_units_of_measure
4802 WHERE uom_code = l_uom_code;
4803
4804 /* OPM Convergence */
4805 IF l_sec_uom_code IS NOT NULL THEN
4806
4807 SELECT unit_of_measure
4808 INTO l_sec_uom
4809 FROM mtl_units_of_measure
4810 WHERE uom_code = l_sec_uom_code;
4811
4812 END IF;
4813 -- dbms_output.put_line('main_process_receipt: Convert inspection uom code into uom');
4814
4815 -- Open RCV Transactions V cursor
4816 open rtv_cursor(
4817 l_receipt_num
4818 , l_organization_id
4819 , l_inventory_item_id
4820 , l_revision
4821 , l_revision_control -- Added for bug 3134272
4822 );
4823
4824 -- dbms_output.put_line('main_process_receipt: Opened RTV Cursor');
4825
4826 IF (l_debug = 1) THEN
4827 print_debug('l_receipt_num is ' || to_char(l_receipt_num), 4);
4828 END IF;
4829
4830 while(l_remaining_qty > 0)
4831 loop
4832 fetch rtv_cursor into
4833 l_rcv_transaction_id
4834 , l_receipt_source_code
4835 , l_rtv_uom
4836 , l_rtv_sec_uom; --OPM Convergence
4837
4838 if rtv_cursor%notfound then
4839 IF (l_debug = 1) THEN
4840 print_debug('exited from cursor', 4);
4841 END IF;
4842 exit;
4843 end if;
4844
4845 -- Get quantity that can be still inspected
4846
4847 IF (l_debug = 1) THEN
4848 print_debug('l_rcv_transaction_id is ' || to_char(l_rcv_transaction_id), 4);
4849 print_debug('l_receipt_source_code is ' || l_receipt_source_code, 4);
4850 END IF;
4851 RCV_QUANTITIES_S.GET_AVAILABLE_QUANTITY (
4852 'INSPECT'
4853 , l_rcv_transaction_id
4854 , l_receipt_source_code
4855 , null
4856 , l_rcv_transaction_id
4857 , null
4858 , l_rtv_qty
4859 , l_tolerable_qty
4860 , l_rtv_uom );
4861
4862 /* print_debug('l_rtv_qty is ' || to_char(l_rtv_qty), 4); */
4863
4864 if (l_rtv_qty > 0) then
4865
4866 -- dbms_output.put_line('main_process_receipt: convert rtv uom into uom code');
4867
4868 SELECT uom_code
4869 INTO l_rtv_uom_code
4870 FROM mtl_units_of_measure
4871 WHERE unit_of_measure = l_rtv_uom;
4872
4873 -- If inspection uom is not same as receipt uom, convert
4874
4875 if (l_uom_code <> l_rtv_uom_code) then
4876 l_rtv_qty := inv_convert.inv_um_convert(
4877 l_inventory_item_id
4878 , NULL
4879 , l_rtv_qty
4880 , l_rtv_uom_code
4881 , l_uom_code
4882 , NULL
4883 , NULL);
4884 end if;
4885
4886 if l_rtv_qty >= l_remaining_qty then
4887 l_rtv_qty := l_remaining_qty;
4888 l_rtv_sec_qty := l_sec_remaining_qty; --OPM Convergence
4889 l_remaining_qty := 0;
4890 l_sec_remaining_qty := 0; --OPM Convergence
4891 else
4892 l_remaining_qty := l_remaining_qty - l_rtv_qty;
4893 l_sec_remaining_qty := l_sec_remaining_qty - l_rtv_sec_qty; --OPM Convergence
4894 end if;
4895
4896 -- If required convert into primary unit of measure
4897 if (l_uom_code <> l_primary_uom_code) then
4898
4899 -- dbms_output.put_line('main_process_receipt: convet inspect uom into primary uom');
4900
4901 l_primary_qty := inv_convert.inv_um_convert(
4902 l_inventory_item_id
4903 , NULL
4904 , l_rtv_qty
4905 , l_uom_code
4906 , l_primary_uom_code
4907 , NULL
4908 , NULL);
4909 else
4910 l_primary_qty := l_rtv_qty;
4911 end if;
4912
4913 -- dbms_output.put_line('main_process_receipt: Calling insert_inspect_rec_rti');
4914
4915 -- Insert into rti, passing l_rtv_qty, inspection information
4916 insert_inspect_rec_rti (
4917 x_return_status => l_return_status
4918 , x_msg_count => l_msg_count
4919 , x_msg_data => l_msg_data
4920 , p_rcv_transaction_id => l_rcv_transaction_id
4921 , p_quantity => l_rtv_qty
4922 , p_uom => l_uom
4923 , p_inspection_code => l_inspection_code
4924 , p_quality_code => l_quality_code
4925 , p_transaction_date => l_transaction_date
4926 , p_transaction_type => l_transaction_type
4927 , p_vendor_lot => l_vendor_lot
4928 , p_reason_id => l_reason_id
4929 , p_primary_qty => l_primary_qty
4930 , p_organization_id => l_organization_id
4931 , p_comments => l_comments
4932 , p_attribute_category => l_attribute_category
4933 , p_attribute1 => l_attribute1
4934 , p_attribute2 => l_attribute2
4935 , p_attribute3 => l_attribute3
4936 , p_attribute4 => l_attribute4
4937 , p_attribute5 => l_attribute5
4938 , p_attribute6 => l_attribute6
4939 , p_attribute7 => l_attribute7
4940 , p_attribute8 => l_attribute8
4941 , p_attribute9 => l_attribute9
4942 , p_attribute10 => l_attribute10
4943 , p_attribute11 => l_attribute11
4944 , p_attribute12 => l_attribute12
4945 , p_attribute13 => l_attribute13
4946 , p_attribute14 => l_attribute14
4947 , p_attribute15 => l_attribute15
4948 , p_qa_collection_id => l_qa_collection_id ,
4949 p_sec_uom => l_sec_uom --OPM Convergence
4950 , p_secondary_qty => l_rtv_sec_qty); --OPM Convergence
4951
4952 IF l_return_status = fnd_api.g_ret_sts_error THEN
4953 IF (l_debug = 1) THEN
4954 print_debug('exc_error ' || l_return_status, 4);
4955 END IF;
4956 RAISE fnd_api.g_exc_error;
4957 END IF ;
4958
4959 IF l_return_status = fnd_api.g_ret_sts_unexp_error THEN
4960 IF (l_debug = 1) THEN
4961 print_debug('exc_unexpected_error ' || l_return_status, 4);
4962 END IF;
4963 RAISE fnd_api.g_exc_unexpected_error;
4964 END IF;
4965
4966 -- dbms_output.put_line('main_process_receipt: Successful insert_inspect_rec_rti');
4967
4968 -- Count successfully transacted qty
4969 l_transacted_qty := l_transacted_qty + l_rtv_qty;
4970 IF (l_debug = 1) THEN
4971 print_debug('transacted qty ' || l_transacted_qty, 4);
4972 END IF;
4973 end if;
4974 IF (l_debug = 1) THEN
4975 print_debug('remaining qty ' || l_remaining_qty, 4);
4976 END IF;
4977 end loop;
4978
4979 IF l_remaining_qty > 0 THEN
4980 FND_MESSAGE.set_name('INV','INV_QTY_LESS_OR_EQUAL');
4981 FND_MSG_PUB.Add;
4982 RAISE FND_API.G_EXC_ERROR;
4983 END IF;
4984
4985 close rtv_cursor;
4986
4987 exception
4988 when fnd_api.g_exc_error THEN
4989 IF (l_debug = 1) THEN
4990 print_debug('Jumped to Exception exc_error ', 4);
4991 END IF;
4992 rollback to inspect_main_receipt_sp;
4993
4994 x_return_status := fnd_api.g_ret_sts_error;
4995 IF (l_debug = 1) THEN
4996 print_debug('Jumped to Exception exc_error ' || x_return_status, 4);
4997 END IF;
4998
4999 -- Get message count and data
5000 fnd_msg_pub.count_and_get
5001 ( p_count => x_msg_count
5002 , p_data => x_msg_data
5003 );
5004
5005 IF (rtv_cursor%isopen) THEN
5006 CLOSE rtv_cursor;
5007 END IF;
5008
5009 when fnd_api.g_exc_unexpected_error THEN
5010 IF (l_debug = 1) THEN
5011 print_debug('Jumped to Exception unexpected_exc_error ', 4);
5012 END IF;
5013 rollback to inspect_main_receipt_sp;
5014
5015 x_return_status := fnd_api.g_ret_sts_unexp_error ;
5016 IF (l_debug = 1) THEN
5017 print_debug('Jumped to Exception unexpected_exc_error ' || x_return_status, 4);
5018 END IF;
5019
5020 -- Get message count and data
5021 fnd_msg_pub.count_and_get
5022 ( p_count => x_msg_count
5023 , p_data => x_msg_data
5024 );
5025
5026 IF (rtv_cursor%isopen) THEN
5027 CLOSE rtv_cursor;
5028 END IF;
5029
5030 when others THEN
5031 IF (l_debug = 1) THEN
5032 print_debug('Jumped to Exception others', 4);
5033 END IF;
5034 rollback to inspect_main_receipt_sp;
5035
5036 x_return_status := fnd_api.g_ret_sts_unexp_error ;
5037 IF (l_debug = 1) THEN
5038 print_debug('Jumped to Exception others' || x_return_status, 4);
5039 END IF;
5040 --
5041 IF fnd_msg_pub.check_msg_level(fnd_msg_pub.g_msg_lvl_unexp_error)
5042 THEN
5043 fnd_msg_pub.add_exc_msg
5044 ( g_pkg_name
5045 , 'main_process_receipt'
5046 );
5047 END IF;
5048
5049 -- Get message count and data
5050 fnd_msg_pub.count_and_get
5051 ( p_count => x_msg_count
5052 , p_data => x_msg_data
5053 );
5054
5055 IF (rtv_cursor%isopen) THEN
5056 CLOSE rtv_cursor;
5057 END IF;
5058 end main_process_receipt;
5059
5060 procedure insert_inspect_rec_rti (
5061 x_return_status OUT NOCOPY VARCHAR2
5062 , x_msg_count OUT NOCOPY NUMBER
5063 , x_msg_data OUT NOCOPY VARCHAR2
5064 , p_rcv_transaction_id IN NUMBER
5065 , p_quantity IN NUMBER
5066 , p_uom IN VARCHAR2
5067 , p_inspection_code IN VARCHAR2
5068 , p_quality_code IN VARCHAR2
5069 , p_transaction_date IN DATE
5070 , p_transaction_type IN VARCHAR2
5071 , p_vendor_lot IN VARCHAR2
5072 , p_reason_id IN NUMBER
5073 , p_primary_qty IN NUMBER
5074 , p_organization_id IN NUMBER
5075 , p_comments IN VARCHAR2 DEFAULT NULL
5076 , p_attribute_category IN VARCHAR2 DEFAULT NULL
5077 , p_attribute1 IN VARCHAR2 DEFAULT NULL
5078 , p_attribute2 IN VARCHAR2 DEFAULT NULL
5079 , p_attribute3 IN VARCHAR2 DEFAULT NULL
5080 , p_attribute4 IN VARCHAR2 DEFAULT NULL
5081 , p_attribute5 IN VARCHAR2 DEFAULT NULL
5082 , p_attribute6 IN VARCHAR2 DEFAULT NULL
5083 , p_attribute7 IN VARCHAR2 DEFAULT NULL
5084 , p_attribute8 IN VARCHAR2 DEFAULT NULL
5085 , p_attribute9 IN VARCHAR2 DEFAULT NULL
5086 , p_attribute10 IN VARCHAR2 DEFAULT NULL
5087 , p_attribute11 IN VARCHAR2 DEFAULT NULL
5088 , p_attribute12 IN VARCHAR2 DEFAULT NULL
5089 , p_attribute13 IN VARCHAR2 DEFAULT NULL
5090 , p_attribute14 IN VARCHAR2 DEFAULT NULL
5091 , p_attribute15 IN VARCHAR2 DEFAULT NULL
5092 , p_qa_collection_id IN NUMBER DEFAULT NULL
5093 , p_lpn_id IN NUMBER DEFAULT NULL
5094 , p_transfer_lpn_id IN NUMBER DEFAULT NULL
5095 , p_mmtt_temp_id IN NUMBER DEFAULT NULL
5096 , p_sec_uom IN VARCHAR2 DEFAULT NULL --OPM Convergenc
5097 , p_secondary_qty IN NUMBER DEFAULT NULL
5098 ) --OPM Convergence)
5099 is
5100 l_interface_transaction_id NUMBER;
5101 l_group_id NUMBER;
5102
5103 l_user_id NUMBER;
5104 l_logon_id NUMBER;
5105 l_employee_id NUMBER;
5106 l_processor_value VARCHAR2(10);
5107
5108 l_dest_type_code VARCHAR2(25) := 'RECEIVING';
5109 l_po_dist_id NUMBER := NULL;
5110 l_deliver_to_location_id NUMBER := NULL;
5111 l_dest_context VARCHAR2(30) := 'RECEIVING';
5112 l_movement_id NUMBER := NULL;
5113
5114 l_inspection_type VARCHAR2(30);
5115
5116 l_rcv_transaction_id NUMBER := p_rcv_transaction_id;
5117 l_quantity NUMBER := p_quantity;
5118 l_uom VARCHAR2(25) := p_uom;
5119 l_inspection_code VARCHAR2(25) := p_inspection_code;
5120 l_quality_code VARCHAR2(25) := p_quality_code;
5121 l_transaction_date DATE := p_transaction_date;
5122 l_organization_id NUMBER := p_organization_id;
5123 l_comments VARCHAR2(240) := p_comments;
5124 l_attribute_category VARCHAR2(30) := p_attribute_category;
5125 l_attribute1 VARCHAR2(150) := p_attribute1;
5126 l_attribute2 VARCHAR2(150) := p_attribute2;
5127 l_attribute3 VARCHAR2(150) := p_attribute3;
5128 l_attribute4 VARCHAR2(150) := p_attribute4;
5129 l_attribute5 VARCHAR2(150) := p_attribute5;
5130 l_attribute6 VARCHAR2(150) := p_attribute6;
5131 l_attribute7 VARCHAR2(150) := p_attribute7;
5132 l_attribute8 VARCHAR2(150) := p_attribute8;
5133 l_attribute9 VARCHAR2(150) := p_attribute9;
5134 l_attribute10 VARCHAR2(150) := p_attribute10;
5135 l_attribute11 VARCHAR2(150) := p_attribute11;
5136 l_attribute12 VARCHAR2(150) := p_attribute12;
5137 l_attribute13 VARCHAR2(150) := p_attribute13;
5138 l_attribute14 VARCHAR2(150) := p_attribute14;
5139 l_attribute15 VARCHAR2(150) := p_attribute15;
5140 l_transaction_type VARCHAR2(30) := p_transaction_type;
5141 l_vendor_lot VARCHAR2(30) := p_vendor_lot;
5142 l_reason_id NUMBER := p_reason_id;
5143 l_primary_qty NUMBER := p_primary_qty;
5144
5145 l_sec_uom VARCHAR2(25) := p_sec_uom;--OPM Convergence
5146 l_secondary_qty number := p_secondary_qty; --OPM Convergence
5147
5148 l_receipt_source_code VARCHAR2(25);
5149 l_source_document_code VARCHAR2(25);
5150 l_shipment_hdr_id NUMBER;
5151 l_shipment_line_id NUMBER;
5152 l_substitute_code VARCHAR2(25);
5153 l_transaction_id NUMBER;
5154 l_po_hdr_id NUMBER;
5155 l_po_release_id NUMBER;
5156 l_po_line_id NUMBER;
5157 l_po_line_location_id NUMBER;
5158 l_po_rev_num NUMBER;
5159 l_po_unit_price NUMBER;
5160 l_currency_code VARCHAR2(15);
5161 l_currency_conv_rate NUMBER;
5162 l_currency_conv_date DATE;
5163 l_currency_conv_type VARCHAR2(30);
5164 l_req_line_id NUMBER;
5165 l_req_dist_id NUMBER;
5166 l_routing_id NUMBER;
5167 l_routing_step_id NUMBER;
5168 l_location_id NUMBER;
5169 l_category_id NUMBER;
5170 l_primary_uom VARCHAR2(25);
5171 l_item_id NUMBER;
5172 l_item_revision VARCHAR2(3);
5173 l_vendor_id NUMBER;
5174 l_mtl_lot NUMBER;
5175 l_mtl_serial NUMBER;
5176 l_routing_header_id NUMBER;
5177 l_qa_collection_id NUMBER;
5178 l_ussgl_transaction_code VARCHAR2(30);
5179 l_government_context VARCHAR2(30);
5180 l_vendor_site_id NUMBER;
5181 l_oe_order_header_id NUMBER;
5182 l_oe_order_line_id NUMBER;
5183 l_customer_id NUMBER;
5184 l_customer_site_id NUMBER;
5185 l_customer_item_number VARCHAR2(30);
5186 l_lpn_id NUMBER := p_lpn_id;
5187 l_transfer_lpn_id NUMBER := p_transfer_lpn_id;
5188 l_debug number := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
5189 l_receipt_num VARCHAR2(30);
5190 l_validation_flag VARCHAR2(1);
5191 l_lpn_group_id NUMBER;
5192 l_mmtt_temp_id NUMBER := p_mmtt_temp_id;
5193 l_lpn_sub mtl_secondary_inventories.secondary_inventory_name%TYPE;
5194 l_lpn_loc_id NUMBER;
5195 l_xfer_lpn_sub mtl_secondary_inventories.secondary_inventory_name%TYPE;
5196 l_xfer_lpn_loc_id NUMBER;
5197 l_xfer_lpn_ctxt NUMBER;
5198 l_rti_sub_code mtl_secondary_inventories.secondary_inventory_name%TYPE;
5199 l_rti_loc_id NUMBER;
5200
5201 l_rti_project_id NUMBER := NULL;
5202 l_rti_task_id NUMBER := NULL;
5203
5204
5205 -- For Bug 7440217
5206 v_lcm_enabled_org varchar2(1);
5207 v_pre_receive varchar2(1);
5208 v_lcm_ship_line_id NUMBER;
5209 v_unit_landed_cost NUMBER;
5210 -- End for Bug 7440217
5211
5212
5213 l_operating_unit_id MO_GLOB_ORG_ACCESS_TMP.ORGANIZATION_ID%TYPE; --<R12 MOAC>
5214
5215 begin
5216 x_return_status := fnd_api.g_ret_sts_success;
5217
5218 savepoint insert_rti_sp;
5219
5220 SELECT
5221 rsh.RECEIPT_SOURCE_CODE
5222 , rt.SOURCE_DOCUMENT_CODE
5223 , rsup.SHIPMENT_HEADER_ID
5224 , rsup.SHIPMENT_LINE_ID
5225 , rt.SUBSTITUTE_UNORDERED_CODE
5226 , rsup.RCV_TRANSACTION_ID
5227 , rsup.PO_HEADER_ID
5228 , rsup.PO_RELEASE_ID
5229 , rsup.PO_LINE_ID
5230 , rsup.PO_LINE_LOCATION_ID
5231 , rt.PO_REVISION_NUM
5232 , NVL(PLL.PRICE_OVERRIDE, POL.UNIT_PRICE)
5233 , rt.CURRENCY_CODE
5234 , rt.CURRENCY_CONVERSION_RATE
5235 , rt.CURRENCY_CONVERSION_DATE
5236 , rt.CURRENCY_CONVERSION_TYPE
5237 , rsup.REQ_LINE_ID
5238 , rsl.REQ_DISTRIBUTION_ID
5239 , rt.ROUTING_header_ID
5240 , rt.ROUTING_STEP_ID
5241 , rt.LOCATION_ID
5242 , rsl.CATEGORY_ID
5243 , rt.PRIMARY_Unit_of_measure
5244 , rsup.ITEM_ID
5245 , rsup.ITEM_REVISION
5246 , rsh.VENDOR_ID
5247 , msi.LOT_CONTROL_CODE
5248 , msi.SERIAL_NUMBER_CONTROL_CODE
5249 , rt.ROUTING_HEADER_ID
5250 , rt.QA_COLLECTION_ID
5251 , rsl.USSGL_TRANSACTION_CODE
5252 , rsl.GOVERNMENT_CONTEXT
5253 , rt.VENDOR_SITE_ID
5254 , rsup.OE_ORDER_HEADER_ID
5255 , rsup.OE_ORDER_LINE_ID
5256 , rsh.CUSTOMER_ID
5257 , rsh.CUSTOMER_SITE_ID
5258 , decode(oel.item_identifier_type, 'CUST', MCI.CUSTOMER_ITEM_NUMBER, '')
5259 INTO
5260 l_receipt_source_code
5261 , l_source_document_code
5262 , l_shipment_hdr_id
5263 , l_shipment_line_id
5264 , l_substitute_code
5265 , l_transaction_id
5266 , l_po_hdr_id
5267 , l_po_release_id
5268 , l_po_line_id
5269 , l_po_line_location_id
5270 , l_po_rev_num
5271 , l_po_unit_price
5272 , l_currency_code
5273 , l_currency_conv_rate
5274 , l_currency_conv_date
5275 , l_currency_conv_type
5276 , l_req_line_id
5277 , l_req_dist_id
5278 , l_routing_id
5279 , l_routing_step_id
5280 , l_location_id
5281 , l_category_id
5282 , l_primary_uom
5283 , l_item_id
5284 , l_item_revision
5285 , l_vendor_id
5286 , l_mtl_lot
5287 , l_mtl_serial
5288 , l_routing_header_id
5289 , l_qa_collection_id
5290 , l_USSGL_TRANSACTION_CODE
5291 , l_GOVERNMENT_CONTEXT
5292 , l_vendor_site_id
5293 , l_oe_order_header_id
5294 , l_oe_order_line_id
5295 , l_customer_id
5296 , l_customer_site_id
5297 , l_customer_item_number
5298 FROM rcv_supply rsup
5299 ,rcv_shipment_headers rsh
5300 ,rcv_shipment_lines rsl
5301 ,rcv_transactions rt
5302 ,po_line_locations pll
5303 ,po_lines pol
5304 ,mtl_system_items msi
5305 ,mtl_customer_items mci
5306 ,oe_order_lines_all oel
5307 WHERE rt.transaction_id = l_rcv_transaction_id
5308 AND rt.transaction_type <> 'UNORDERED'
5309 AND rsup.supply_type_code = 'RECEIVING'
5310 AND rsup.rcv_transaction_id = rt.transaction_id
5311 AND rsh.shipment_header_id = rsup.shipment_header_id
5312 AND rsl.shipment_line_id = rsup.shipment_line_id
5313 AND pll.line_location_id(+) = rsup.po_line_location_id
5314 AND pol.po_line_id(+) = rsup.po_line_id
5315 AND msi.organization_id (+) = rsup.to_organization_id
5316 AND msi.inventory_item_id (+) = rsup.item_id
5317 AND oel.line_id(+) = rsup.oe_order_line_id
5318 AND oel.ordered_item_id = mci.customer_item_id(+);
5319
5320 Begin
5321 IF (l_debug = 1) THEN
5322 print_debug('IN INSERT_INSPECT_REC_RTF ',9);
5323 END IF;
5324 SELECT receipt_num
5325 INTO l_receipt_num
5326 FROM rcv_shipment_headers
5327 WHERE shipment_header_id = l_shipment_hdr_id
5328 AND ship_to_org_id = p_organization_id;
5329
5330 inv_rcv_common_apis.g_rcv_global_var.receipt_num := l_receipt_num;
5331 IF (l_debug = 1) THEN
5332 print_debug('create_intship_rcpt_intf_rec: 10.1 '|| inv_rcv_common_apis.g_rcv_global_var.receipt_num, 9);
5333 END IF;
5334 EXCEPTION
5335 WHEN NO_DATA_FOUND THEN
5336 l_receipt_num := NULL;
5337 END; --end of changes for bug 2894137
5338
5339 /*
5340 dbms_output.put_line('insinsprecrti: rcvtcnid ' || l_rcv_transaction_id);
5341 dbms_output.put_line('insinsprecrti: poid ' || l_po_hdr_id);
5342 dbms_output.put_line('insinsprecrti: polineid ' || l_po_line_id);
5343 dbms_output.put_line('insinsprecrti: polinelocid ' || l_po_line_location_id);
5344 */
5345 INV_RCV_COMMON_APIS.init_startup_values(l_organization_id);
5346
5347 l_user_id := INV_RCV_COMMON_APIS.g_po_startup_value.user_id;
5348 l_logon_id := INV_RCV_COMMON_APIS.g_po_startup_value.logon_id;
5349 l_employee_id := INV_RCV_COMMON_APIS.g_po_startup_value.employee_id;
5350 l_processor_value := INV_RCV_COMMON_APIS.g_po_startup_value.transaction_mode;
5351
5352 IF inv_rcv_common_apis.g_rcv_global_var.interface_group_id is NULL THEN
5353 SELECT rcv_interface_groups_s.nextval
5354 INTO l_group_id FROM dual;
5355
5356 inv_rcv_common_apis.g_rcv_global_var.interface_group_id := l_group_id;
5357 ELSE
5358 l_group_id := inv_rcv_common_apis.g_rcv_global_var.interface_group_id;
5359 END IF;
5360
5361 select rcv_transactions_interface_s.nextval
5362 into l_interface_transaction_id from dual;
5363
5364
5365 if l_inspection_code = 'ACCEPT' then
5366 l_inspection_type := 'ACCEPTED';
5367 else
5368 l_inspection_type := 'REJECTED';
5369 end if;
5370
5371 SELECT RT.MOVEMENT_ID
5372 INTO l_movement_id
5373 FROM RCV_TRANSACTIONS RT
5374 WHERE RT.TRANSACTION_ID = l_rcv_transaction_id;
5375
5376 --<R12 MOAC>
5377 l_operating_unit_id := inv_rcv_common_apis.get_operating_unit_id( l_receipt_source_code,
5378 l_po_hdr_id,
5379 l_req_line_id,
5380 l_oe_order_header_id );
5381
5382 /*
5383 ** If collection id is passed (by QA) use it i.e.overwrite
5384 ** l_qa_collection_id
5385 */
5386
5387 if (p_qa_collection_id is not null) then
5388 l_qa_collection_id := p_qa_collection_id;
5389 end if;
5390
5391 /* FP-J Enhancement
5392 * Populate the LPN_GROUP_ID, validation_flag columns, subinventory
5393 * and locator_id columns in RTI if WMS and PO patch levels are J or higher
5394 */
5395 IF ((inv_rcv_common_apis.g_wms_patch_level >= inv_rcv_common_apis.g_patchset_j) AND
5396 (inv_rcv_common_apis.g_po_patch_level >= inv_rcv_common_apis.g_patchset_j_po)) THEN
5397 l_validation_flag := 'Y';
5398 l_lpn_group_id := l_group_id;
5399
5400 /* If the current transaction is LPN-based (for a WMS org), then we need to
5401 * populate the subinventory,locator_id columns in the RTI record
5402 * If the transfer LPN has context "Resides in Receiving" Then
5403 * Populate the RTI record from WLPN for the transfer LPN
5404 * Else
5405 * Populate the RTI record from WLPN for the inspected LPN
5406 * End If
5407 */
5408 IF (p_lpn_id IS NOT NULL OR p_transfer_lpn_id IS NOT NULL) THEN
5409 BEGIN
5410 SELECT lpn_context
5411 , subinventory_code
5412 , locator_id
5413 INTO l_xfer_lpn_ctxt
5414 , l_xfer_lpn_sub
5415 , l_xfer_lpn_loc_id
5416 FROM wms_license_plate_numbers
5417 WHERE lpn_id = p_transfer_lpn_id;
5418
5419 IF (NVL(l_xfer_lpn_ctxt, 5) = 3) THEN
5420 l_rti_sub_code := l_xfer_lpn_sub;
5421 l_rti_loc_id := l_xfer_lpn_loc_id;
5422 ELSE
5423 --Transfer LPN has been generated afresh, so we need to default the RTI
5424 --with the sub/locator of the inspected LPN
5425 BEGIN
5426 SELECT subinventory_code
5427 , locator_id
5428 INTO l_lpn_sub
5429 , l_lpn_loc_id
5430 FROM wms_license_plate_numbers
5431 WHERE lpn_id = p_lpn_id;
5432
5433 l_rti_sub_code := l_lpn_sub;
5434 l_rti_loc_id := l_lpn_loc_id;
5435
5436 EXCEPTION
5437 WHEN OTHERS THEN
5438 l_rti_sub_code := NULL;
5439 l_rti_loc_id := NULL;
5440 END;
5441 END IF; --END IF check xfer lpn context
5442 EXCEPTION
5443 WHEN OTHERS THEN
5444 l_rti_sub_code := NULL;
5445 l_rti_loc_id := NULL;
5446 END;
5447
5448 -- For lpn transactions we also need to populate project and task
5449 -- FROM mol
5450 IF (p_lpn_id IS NOT NULL) THEN
5451 IF (l_debug = 1) THEN
5452 print_debug('insert_inspect_rec_rti: Before calculating project ' , 4);
5453 END IF;
5454 BEGIN
5455 SELECT project_id
5456 , task_id
5457 INTO l_rti_project_id
5458 , l_rti_task_id
5459 FROM mtl_txn_request_lines
5460 WHERE lpn_id = p_lpn_id
5461 AND inventory_item_id = l_item_id
5462 -- Bug 3366617
5463 -- The following check was not needed as the process_flag is not yet updated.
5464 -- AND wms_process_flag = 2
5465 AND ROWNUM < 2;
5466 EXCEPTION
5467 WHEN OTHERS THEN
5468 IF (l_debug = 1) THEN
5469 print_debug('insert_inspect_rec_rti: In the exception of calculating project ' , 4);
5470 END IF;
5471 l_rti_project_id := NULL;
5472 l_rti_task_id := NULL;
5473 END;
5474 END IF; --IF (p_lpn_id IN NOT NULL) THEN
5475 ELSE
5476 --For a non-LPN based transaction, subinventory/locator would be NULL
5477 l_rti_sub_code := NULL;
5478 l_rti_loc_id := NULL;
5479 --For non-lpn based transactions, project/task will also be null
5480 l_rti_project_id := NULL;
5481 l_rti_task_id := NULL;
5482 END IF;
5483 --WMS or PO patch levels are < J, default the values for these new columns to NULL
5484 ELSE
5485 l_validation_flag := NULL;
5486 l_lpn_group_id := NULL;
5487 l_rti_sub_code := NULL;
5488 l_rti_loc_id := NULL;
5489 l_rti_project_id := NULL;
5490 l_rti_task_id := NULL;
5491 END IF;
5492
5493 IF (l_debug = 1) THEN
5494 print_debug('insert_inspect_rec_rti: validation_flag : ' || l_validation_flag || ', lpn_group_id: ' || l_lpn_group_id, 4);
5495 print_debug('insert_inspect_rec_rti: subinventory : ' || l_rti_sub_code || ', locator_id: ' || l_rti_loc_id, 4);
5496 END IF;
5497
5498 -- bug 3452845
5499 IF ((inv_rcv_common_apis.g_inv_patch_level >= inv_rcv_common_apis.g_patchset_j) AND
5500 (inv_rcv_common_apis.g_po_patch_level >= inv_rcv_common_apis.g_patchset_j_po)) THEN
5501 l_transaction_date := Sysdate;
5502 ELSE
5503 l_transaction_date := Trunc(Sysdate);
5504 END IF;
5505
5506 insert into RCV_TRANSACTIONS_INTERFACE
5507 (
5508 receipt_source_code,
5509 interface_transaction_id,
5510 group_id,
5511 last_update_date,
5512 last_updated_by,
5513 created_by,
5514 creation_date,
5515 last_update_login,
5516 interface_source_code,
5517 source_document_code,
5518 destination_type_code,
5519 transaction_date,
5520 quantity,
5521 unit_of_measure,
5522 shipment_header_id,
5523 shipment_line_id,
5524 substitute_unordered_code,
5525 employee_id,
5526 parent_transaction_id,
5527 inspection_status_code,
5528 inspection_quality_code,
5529 po_header_id,
5530 po_release_id,
5531 po_line_id,
5532 po_line_location_id,
5533 po_distribution_id,
5534 po_revision_num,
5535 po_unit_price,
5536 currency_code,
5537 currency_conversion_rate,
5538 requisition_line_id,
5539 req_distribution_id,
5540 routing_header_id,
5541 routing_step_id,
5542 comments,
5543 attribute_category,
5544 attribute1,
5545 attribute2,
5546 attribute3,
5547 attribute4,
5548 attribute5,
5549 attribute6,
5550 attribute7,
5551 attribute8,
5552 attribute9,
5553 attribute10,
5554 attribute11,
5555 attribute12,
5556 attribute13,
5557 attribute14,
5558 attribute15,
5559 transaction_type,
5560 location_id,
5561 processing_status_code,
5562 processing_mode_code,
5563 transaction_status_code,
5564 category_id,
5565 vendor_lot_num,
5566 reason_id,
5567 primary_quantity,
5568 primary_unit_of_measure,
5569 item_id,
5570 item_revision,
5571 to_organization_id,
5572 deliver_to_location_id,
5573 destination_context,
5574 vendor_id,
5575 use_mtl_lot,
5576 use_mtl_serial,
5577 movement_id,
5578 currency_conversion_date,
5579 currency_conversion_type,
5580 qa_collection_id,
5581 ussgl_transaction_code,
5582 government_context,
5583 vendor_site_id,
5584 oe_order_header_id,
5585 oe_order_line_id,
5586 customer_id,
5587 customer_site_id,
5588 lpn_id,
5589 transfer_lpn_id,
5590 mobile_txn,
5591 validation_flag,
5592 lpn_group_id,
5593 mmtt_temp_id,
5594 subinventory,
5595 locator_id,
5596 project_id,
5597 task_id,
5598 secondary_quantity, --OPM Convergence
5599 secondary_unit_of_measure, --OPM Convergence
5600 org_id --<R12 MOAC>
5601 )
5602 values
5603 (
5604 l_receipt_source_code,
5605 l_interface_transaction_id,
5606 l_group_id,
5607 SYSDATE,
5608 l_user_id,
5609 l_user_id,
5610 SYSDATE,
5611 l_logon_id,
5612 'RCV',
5613 l_source_document_code,
5614 l_dest_type_code,
5615 l_transaction_date,
5616 l_quantity,
5617 l_uom,
5618 l_shipment_hdr_id,
5619 l_shipment_line_id,
5620 l_substitute_code,
5621 l_employee_id,
5622 l_transaction_id,
5623 l_inspection_type,
5624 l_quality_code,
5625 l_po_hdr_id,
5626 l_po_release_id,
5627 l_po_line_id,
5628 l_po_line_location_id,
5629 l_po_dist_id,
5630 l_po_rev_num,
5631 l_po_unit_price,
5632 l_currency_code,
5633 l_currency_conv_rate,
5634 l_req_line_id,
5635 l_req_dist_id,
5636 l_routing_id,
5637 l_routing_step_id,
5638 l_comments,
5639 l_attribute_category,
5640 l_attribute1,
5641 l_attribute2,
5642 l_attribute3,
5643 l_attribute4,
5644 l_attribute5,
5645 l_attribute6,
5646 l_attribute7,
5647 l_attribute8,
5648 l_attribute9,
5649 l_attribute10,
5650 l_attribute11,
5651 l_attribute12,
5652 l_attribute13,
5653 l_attribute14,
5654 l_attribute15,
5655 l_transaction_type,
5656 l_location_id,
5657 'PENDING', -- Formerly INSPECTION
5658 l_processor_value,
5659 'PENDING', -- Formerly INSPECTION
5660 l_category_id,
5661 l_vendor_lot,
5662 l_reason_id,
5663 l_primary_qty,
5664 l_primary_uom,
5665 l_item_id,
5666 l_item_revision,
5667 l_organization_id,
5668 l_deliver_to_location_id,
5669 l_dest_context,
5670 l_vendor_id,
5671 l_mtl_lot,
5672 l_mtl_serial,
5673 l_movement_id,
5674 Trunc(l_currency_conv_date),
5675 l_currency_conv_type,
5676 l_qa_collection_id,
5677 l_ussgl_transaction_code,
5678 l_government_context,
5679 l_vendor_site_id,
5680 l_oe_order_header_id,
5681 l_oe_order_line_id,
5682 l_customer_id,
5683 l_customer_site_id,
5684 l_lpn_id,
5685 l_transfer_lpn_id,
5686 'Y',
5687 l_validation_flag,
5688 l_lpn_group_id,
5689 l_mmtt_temp_id,
5690 l_rti_sub_code,
5691 l_rti_loc_id,
5692 l_rti_project_id,
5693 l_rti_task_id,
5694 l_secondary_qty, --OPM Convergence
5695 l_sec_uom, --OPM Convergence
5696 l_operating_unit_id --<R12 MOAC>
5697 );
5698
5699
5700
5701 -- For Bug 7440217 added the following code to update RTI with the status as PENDING so that it gets picked up for processing
5702 SELECT mp.lcm_enabled_flag
5703 INTO v_lcm_enabled_org
5704 FROM mtl_parameters mp
5705 WHERE mp.organization_id = l_organization_id;
5706
5707 SELECT rp.pre_receive
5708 INTO v_pre_receive
5709 FROM rcv_parameters rp
5710 WHERE rp.organization_id = l_organization_id;
5711
5712 IF nvl(v_lcm_enabled_org, 'N') = 'Y' THEN
5713
5714 SELECT LCM_SHIPMENT_LINE_ID, UNIT_LANDED_COST
5715 INTO v_lcm_ship_line_id, v_unit_landed_cost
5716 FROM rcv_shipment_lines
5717 WHERE shipment_line_id = l_shipment_line_id;
5718
5719 UPDATE rcv_transactions_interface
5720 SET lcm_shipment_line_id = v_lcm_ship_line_id,
5721 unit_landed_cost = v_unit_landed_cost
5722 WHERE interface_transaction_id = l_interface_transaction_id
5723 AND to_organization_id = l_organization_id;
5724 END IF;
5725 -- End for Bug 7440217
5726
5727
5728
5729 --Set the global variable for interface_transaction_id to be used in
5730 --setting product_transaction_id for the MTLI/MSNI records
5731 g_interface_transaction_id := l_interface_transaction_id;
5732
5733 exception
5734 when fnd_api.g_exc_error THEN
5735 rollback to insert_rti_sp;
5736
5737 x_return_status := fnd_api.g_ret_sts_error;
5738
5739 -- Get message count and data
5740 fnd_msg_pub.count_and_get
5741 ( p_count => x_msg_count
5742 , p_data => x_msg_data
5743 );
5744
5745 when fnd_api.g_exc_unexpected_error THEN
5746 rollback to insert_rti_sp;
5747
5748 x_return_status := fnd_api.g_ret_sts_unexp_error ;
5749
5750 -- Get message count and data
5751 fnd_msg_pub.count_and_get
5752 ( p_count => x_msg_count
5753 , p_data => x_msg_data
5754 );
5755
5756 when others THEN
5757 rollback to insert_rti_sp;
5758
5759 x_return_status := fnd_api.g_ret_sts_unexp_error ;
5760 --
5761 IF fnd_msg_pub.check_msg_level(fnd_msg_pub.g_msg_lvl_unexp_error)
5762 THEN
5763 fnd_msg_pub.add_exc_msg
5764 ( g_pkg_name
5765 , 'insert_inspect_rec_rti'
5766 );
5767 END IF;
5768
5769 -- Get message count and data
5770 fnd_msg_pub.count_and_get
5771 ( p_count => x_msg_count
5772 , p_data => x_msg_data
5773 );
5774 end insert_inspect_rec_rti;
5775
5776 procedure rcv_manager_rpc_call(
5777 x_return_status out NOCOPY varchar2
5778 , x_return_code out NOCOPY number)
5779 is
5780 rc NUMBER;
5781 --l_timeout NUMBER := 300;
5782 l_timeout NUMBER; ----bug 5169107
5783 l_outcome VARCHAR2(200) := NULL;
5784 l_message VARCHAR2(200) := NULL;
5785 x_str varchar2(4000) := NULL;
5786
5787 r_val1 varchar2(200) := NULL;
5788 r_val2 varchar2(200) := NULL;
5789 r_val3 varchar2(200) := NULL;
5790 r_val4 varchar2(200) := NULL;
5791 r_val5 varchar2(200) := NULL;
5792 r_val6 varchar2(200) := NULL;
5793 r_val7 varchar2(200) := NULL;
5794 r_val8 varchar2(200) := NULL;
5795 r_val9 varchar2(200) := NULL;
5796 r_val10 varchar2(200) := NULL;
5797 r_val11 varchar2(200) := NULL;
5798 r_val12 varchar2(200) := NULL;
5799 r_val13 varchar2(200) := NULL;
5800 r_val14 varchar2(200) := NULL;
5801 r_val15 varchar2(200) := NULL;
5802 r_val16 varchar2(200) := NULL;
5803 r_val17 varchar2(200) := NULL;
5804 r_val18 varchar2(200) := NULL;
5805 r_val19 varchar2(200) := NULL;
5806 r_val20 varchar2(200) := NULL;
5807
5808 l_debug number := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
5809 begin
5810 x_return_status := fnd_api.g_ret_sts_success;
5811
5812 /*
5813 dbms_output.put_line('rcv_mgr_rpc_call: group_id '
5814 || inv_RCV_COMMON_APIS.g_rcv_global_var.interface_group_id);
5815 */
5816 --bug 5169107
5817 l_timeout := fnd_profile.value('INV_RPC_TIMEOUT');
5818 if l_timeout is null then
5819 l_timeout := 300;
5820 end if;
5821 --bug 5169107
5822
5823 rc := fnd_transaction.synchronous (
5824 l_timeout, l_outcome, l_message, 'PO', 'RCVTPO',
5825 'ONLINE', inv_RCV_COMMON_APIS.g_rcv_global_var.interface_group_id,
5826 NULL, NULL, NULL, NULL, NULL, NULL,
5827 NULL, NULL, NULL, NULL, NULL, NULL,
5828 NULL, NULL, NULL, NULL, NULL, NULL);
5829
5830 -- dbms_output.put_line('rc of RPC:' || rc);
5831
5832 x_return_code := rc;
5833
5834 IF (rc = 0 and (l_outcome NOT IN ('WARNING', 'ERROR'))) THEN
5835 NULL;
5836 ELSIF (rc = 1) THEN
5837 x_return_status := fnd_api.g_ret_sts_error;
5838 ELSIF (rc = 2) THEN
5839 x_return_status := fnd_api.g_ret_sts_error;
5840 ELSIF (rc = 3 or (l_outcome IN ('WARNING', 'ERROR'))) THEN
5841 x_return_status := fnd_api.g_ret_sts_error;
5842
5843 rc := fnd_transaction.get_values (
5844 r_val1, r_val2, r_val3, r_val4, r_val5,
5845 r_val6, r_val7, r_val8, r_val9, r_val10,
5846 r_val11, r_val12, r_val13, r_val14, r_val15,
5847 r_val16, r_val17, r_val18, r_val19, r_val20);
5848
5849 /*
5850 dbms_output.put_line('r_val1 :' || r_val1);
5851 dbms_output.put_line('r_val2 :' || r_val2);
5852 dbms_output.put_line('r_val3 :' || r_val3);
5853 dbms_output.put_line('r_val4 :' || r_val4);
5854 dbms_output.put_line('r_val5 :' || r_val5);
5855 dbms_output.put_line('r_val6 :' || r_val6);
5856 dbms_output.put_line('r_val7 :' || r_val7);
5857 dbms_output.put_line('r_val8 :' || r_val8);
5858 dbms_output.put_line('r_val9 :' || r_val9);
5859 dbms_output.put_line('r_val10:' || r_val10);
5860 dbms_output.put_line('r_val11:' || r_val11);
5861 dbms_output.put_line('r_val12:' || r_val12);
5862 dbms_output.put_line('r_val13:' || r_val13);
5863 dbms_output.put_line('r_val14:' || r_val14);
5864 dbms_output.put_line('r_val15:' || r_val15);
5865 dbms_output.put_line('r_val16:' || r_val16);
5866 dbms_output.put_line('r_val17:' || r_val17);
5867 dbms_output.put_line('r_val18:' || r_val18);
5868 dbms_output.put_line('r_val19:' || r_val19);
5869 dbms_output.put_line('r_val20:' || r_val20);
5870 */
5871
5872 END IF;
5873
5874 -- reset group id
5875 inv_RCV_COMMON_APIS.g_rcv_global_var.interface_group_id := '';
5876 end rcv_manager_rpc_call;
5877
5878
5879
5880
5881
5882
5883
5884
5885 PROCEDURE launch_rcv_manager_rpc(
5886 x_return_status OUT NOCOPY VARCHAR2
5887 , x_return_code OUT NOCOPY NUMBER) IS
5888 l_return_status VARCHAR2(1) := fnd_api.g_ret_sts_success;
5889 l_msg_count NUMBER;
5890 l_msg_data VARCHAR2(400);
5891 l_label_status VARCHAR2(500);
5892 l_txn_id_tbl inv_label.transaction_id_rec_type;
5893 l_counter NUMBER := 0;
5894 CURSOR c_rti_txn_id IS
5895 -- Bug 2377796
5896 -- LPN lables are not getting printed for rejected LPNS
5897 --SELECT MIN(rti.interface_transaction_id)
5898 SELECT rti.interface_transaction_id
5899 FROM rcv_transactions_interface rti
5900 WHERE rti.GROUP_ID = inv_rcv_common_apis.g_rcv_global_var.interface_group_id;
5901 -- GROUP BY rti.lpn_id;
5902 l_debug NUMBER := NVL(fnd_profile.VALUE('INV_DEBUG_TRACE'), 0);
5903 BEGIN
5904
5905 --Commenting out the commit below since it would be done by the wrapper
5906 --to the receiving manager call (INV_RCV_MOBILE_PROCESS_TXN)
5907 --COMMIT;
5908
5909 -- calling label printing API
5910 /* FP-J Lot/Serial Support Enhancement
5911 * If WMS and PO patch levels are J or higher then the label printing calls
5912 * would be done from the receiving TM and should NOT be done here.
5913 * If either of these are lower than J, then retain the original processing
5914 */
5915 IF ((inv_rcv_common_apis.g_wms_patch_level < inv_rcv_common_apis.g_patchset_j) OR
5916 (inv_rcv_common_apis.g_po_patch_level < inv_rcv_common_apis.g_patchset_j_po)) THEN
5917 IF (l_debug = 1) THEN
5918 print_debug('create_std_rcpt_intf_rec: 8.1 before inv_label.print_label ', 4);
5919 END IF;
5920
5921 l_counter := 1;
5922 OPEN c_rti_txn_id;
5923
5924 LOOP
5925 FETCH c_rti_txn_id INTO l_txn_id_tbl(l_counter);
5926 EXIT WHEN c_rti_txn_id%NOTFOUND;
5927
5928 IF (l_debug = 1) THEN
5929 print_debug('create_std_rcpt_intf_rec calling printing for:' || l_txn_id_tbl(l_counter), 4);
5930 END IF;
5931
5932 l_counter := l_counter + 1;
5933 END LOOP;
5934
5935 CLOSE c_rti_txn_id;
5936 inv_label.print_label(
5937 x_return_status => l_return_status
5938 , x_msg_count => l_msg_count
5939 , x_msg_data => l_msg_data
5940 , x_label_status => l_label_status
5941 , p_api_version => 1.0
5942 , p_print_mode => 1
5943 , p_business_flow_code => 2
5944 , p_transaction_id => l_txn_id_tbl
5945 );
5946
5947 IF l_return_status <> fnd_api.g_ret_sts_success THEN
5948 fnd_message.set_name('INV', 'INV_RCV_CRT_PRINT_LAB_FAIL'); -- MSGTBD
5949 fnd_msg_pub.ADD;
5950 x_return_status := 'W';
5951
5952 IF (l_debug = 1) THEN
5953 print_debug('create_std_rcpt_intf_rec 8.2: inv_label.print_label FAILED;' || TO_CHAR(SYSDATE, 'YYYY-MM-DD HH:DD:SS'), 4);
5954 END IF;
5955 END IF;
5956 --If both WMS and PO are at Patchset J or higher
5957 ELSE
5958 IF (l_debug = 1) THEN
5959 print_debug('launch_rcv_manager_rpc 6.3: WMS and PO patch levels are J or higher. So NO label printing from UI', 4);
5960 END IF;
5961 END IF; --END IF check WMS and PO patch levels
5962
5963 --Calling the receiving manager using the wrapper to honor the processing mode profile
5964 --instead of the direct RPC call
5965 --rcv_manager_rpc_call(x_return_status, x_return_code);
5966
5967
5968 IF (l_debug =1 ) THEN
5969 print_debug('********* PROCESSING_MODE IS :' ||
5970 INV_RCV_COMMON_APIS.g_po_startup_value.transaction_mode
5971 || ' ************',4);
5972 END IF;
5973
5974 INV_RCV_MOBILE_PROCESS_TXN.rcv_process_receive_txn(
5975 x_return_status => x_return_status
5976 , x_msg_data => l_msg_data);
5977
5978 IF (l_debug = 1) THEN
5979 print_debug('return status is launch procedure ' || x_return_status, 4);
5980 print_debug('return msg data l_msg_data: ' || l_msg_data, 4);
5981 END IF;
5982
5983 IF x_return_status = FND_API.G_RET_STS_ERROR THEN
5984 IF (l_debug = 1) THEN
5985 print_debug('launch_rcv_manager_rpc 6.5: Encountered g_exc_error while calling receiving manager;'|| to_char(sysdate, 'YYYY-MM-DD HH:DD:SS'), 4);
5986 END IF;
5987 RAISE FND_API.G_EXC_ERROR;
5988 END IF;
5989
5990 IF x_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
5991 IF (l_debug = 1) THEN
5992 print_debug('launch_rcv_manager_rpc 6.6: Encountered g_exc_unexp_error while calling receiving manager;'|| to_char(sysdate, 'YYYY-MM-DD HH:DD:SS'), 4);
5993 END IF;
5994 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
5995 END IF;
5996
5997 EXCEPTION
5998 WHEN fnd_api.g_exc_error THEN
5999 x_return_status := fnd_api.g_ret_sts_error;
6000 IF (c_rti_txn_id%ISOPEN) THEN
6001 CLOSE c_rti_txn_id;
6002 END IF;
6003 WHEN fnd_api.g_exc_unexpected_error THEN
6004 x_return_status := fnd_api.g_ret_sts_unexp_error;
6005 IF (c_rti_txn_id%ISOPEN) THEN
6006 CLOSE c_rti_txn_id;
6007 END IF;
6008 WHEN OTHERS THEN
6009 x_return_status := fnd_api.g_ret_sts_unexp_error;
6010 IF (c_rti_txn_id%ISOPEN) THEN
6011 CLOSE c_rti_txn_id;
6012 END IF;
6013 END launch_rcv_manager_rpc;
6014
6015
6016
6017
6018
6019
6020 procedure rcv_manager_conc_call
6021 is
6022 v_req_id number;
6023 l_debug number := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
6024 begin
6025 v_req_id := fnd_request.submit_request('PO',
6026 'RVCTP',
6027 null,
6028 null,
6029 false,
6030 'IMMEDIATE',
6031 inv_RCV_COMMON_APIS.g_rcv_global_var.interface_group_id,
6032 '0', --fnd_char.local_chr(0), ?
6033 NULL,
6034 NULL,
6035 NULL,
6036 NULL,
6037 NULL, NULL,
6038 NULL, NULL, NULL, NULL, NULL, NULL, NULL,
6039 NULL, NULL, NULL, NULL, NULL, NULL, NULL,
6040 NULL, NULL, NULL, NULL, NULL, NULL, NULL,
6041 NULL, NULL, NULL, NULL, NULL, NULL, NULL,
6042 NULL, NULL, NULL, NULL, NULL, NULL, NULL,
6043
6044 NULL, NULL, NULL, NULL, NULL, NULL, NULL,
6045 NULL, NULL, NULL, NULL, NULL, NULL, NULL,
6046 NULL, NULL, NULL, NULL, NULL, NULL, NULL,
6047 NULL, NULL, NULL, NULL, NULL, NULL, NULL,
6048
6049 NULL, NULL, NULL, NULL, NULL, NULL, NULL,
6050 NULL, NULL, NULL, NULL, NULL, NULL, NULL,
6051 NULL, NULL, NULL, NULL, NULL, NULL, NULL,
6052 NULL, NULL, NULL, NULL, NULL, NULL, NULL);
6053
6054 /*
6055 dbms_output.put_line('request id:' || v_req_id);
6056 */
6057
6058 if (v_req_id <= 0 or v_req_id is null) then
6059 -- concurrent manager error, Handle error and rollback
6060 -- need error message etc. here ?
6061 NULL;
6062 ELSE
6063 NULL;
6064 end if;
6065
6066 -- reset group id
6067 inv_RCV_COMMON_APIS.g_rcv_global_var.interface_group_id := '';
6068
6069 end rcv_manager_conc_call;
6070
6071 procedure launch_rcv_manager_conc
6072 is
6073 l_debug number := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
6074 begin
6075 commit;
6076 rcv_manager_conc_call;
6077 end launch_rcv_manager_conc;
6078
6079
6080
6081 --------------------------------------------------------
6082 ----------ADDED BY MANU GUPTA 10-18-2000 ---------------
6083 -- returns S if ok, E if not
6084 -- type=LPN,RMA,INTSHIP,PO,RECEIPT
6085
6086 FUNCTION get_inspection_qty(
6087 p_type IN VARCHAR2
6088 , p_lpn_id IN NUMBER := NULL
6089 , p_po_header_id IN NUMBER := NULL
6090 , p_po_release_id IN NUMBER := NULL
6091 , p_po_line_id IN NUMBER := NULL
6092 , p_shipment_header_id IN NUMBER := NULL
6093 , p_oe_order_header_id IN NUMBER := NULL
6094 , p_organization_id IN NUMBER
6095 , p_item_id IN NUMBER
6096 , p_uom_code IN VARCHAR2
6097 , x_inspection_qty OUT NOCOPY NUMBER
6098 , x_return_status OUT NOCOPY VARCHAR2
6099 , x_msg_data OUT NOCOPY VARCHAR2) RETURN NUMBER
6100 IS
6101 l_total_qty NUMBER;
6102 l_cur_qty NUMBER;
6103 --The variable will hold the value of UOM
6104 l_cur_uom_code VARCHAR2(26); --Bug #3908752
6105 l_msg_count NUMBER;
6106 l_rcv_transaction_id NUMBER;
6107 l_tolerable_qty NUMBER;
6108
6109 CURSOR c_txn_lines IS
6110 SELECT uom_code, (quantity - Nvl(quantity_delivered,0)) quantity --bug#12663552
6111 FROM mtl_txn_request_lines
6112 WHERE inspection_status is not null --8405606
6113 AND organization_id = p_organization_id
6114 AND line_status = 7 --bug#12663552
6115 AND inventory_item_id = p_item_id
6116 AND lpn_id = p_lpn_id;
6117
6118 -- bug 8405606 removed the condition for rt.inspection_status_code = 'NOT INSPECTED'
6119
6120 CURSOR c_po_source_lines IS
6121 SELECT rs.rcv_transaction_id
6122 FROM rcv_supply rs
6123 , rcv_transactions rt
6124 WHERE rs.item_id = p_item_id
6125 AND rs.po_header_id = p_po_header_id
6126 AND nvl(rs.po_release_id,-1) = nvl(p_po_release_id,nvl(rs.po_release_id,-1))
6127 AND nvl(rs.po_line_id,-1) = nvl(p_po_line_id, nvl(rs.po_line_id,-1))
6128 AND rs.rcv_transaction_id = rt.transaction_id
6129 AND rs.supply_type_code = 'RECEIVING'
6130 AND rt.transaction_type <> 'UNORDERED'
6131 AND rt.routing_header_id = 2
6132 --BUG 4103743: Need to query on org id also
6133 AND rs.to_organization_id = p_organization_id; /* Inspection routing */
6134
6135 -- use this for receipts also --
6136 CURSOR c_intship_source_lines IS
6137 SELECT rs.rcv_transaction_id
6138 FROM rcv_supply rs
6139 , rcv_transactions rt
6140 WHERE rs.item_id = p_item_id
6141 AND rs.shipment_header_id = p_shipment_header_id
6142 AND rs.rcv_transaction_id = rt.transaction_id
6143 AND rs.supply_type_code = 'RECEIVING'
6144 AND rt.transaction_type <> 'UNORDERED'
6145 AND rt.routing_header_id = 2; /* Inspection routing */
6146
6147 CURSOR c_rma_source_lines IS
6148 SELECT rs.rcv_transaction_id
6149 FROM rcv_supply rs
6150 , rcv_transactions rt
6151 WHERE rs.item_id = p_item_id
6152 AND rs.oe_order_header_id = p_oe_order_header_id
6153 AND rs.rcv_transaction_id = rt.transaction_id
6154 AND rs.supply_type_code = 'RECEIVING'
6155 AND rt.transaction_type <> 'UNORDERED'
6156 AND rt.routing_header_id = 2; /* Inspection routing */
6157
6158 l_debug number := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
6159 BEGIN
6160
6161 l_total_qty := 0;
6162
6163 -- lpn section --
6164 IF (p_type = 'LPN') THEN
6165
6166 --print_debug('Inside get_inspection_qty LPN...', 4);
6167
6168 OPEN c_txn_lines;
6169 LOOP
6170 FETCH c_txn_lines INTO l_cur_uom_code, l_cur_qty;
6171 EXIT WHEN c_txn_lines%NOTFOUND;
6172
6173 --print_debug('LPN l_cur_qty is ' || to_char(l_cur_qty), 4);
6174
6175 IF (l_cur_uom_code <> p_uom_code) THEN
6176 l_cur_qty := inv_convert.inv_um_convert(
6177 p_item_id
6178 , null
6179 , l_cur_qty
6180 , l_cur_uom_code
6181 , p_uom_code
6182 , ''
6183 , '');
6184 END IF;
6185
6186 l_total_qty := l_total_qty + l_cur_qty;
6187 END LOOP;
6188 CLOSE c_txn_lines;
6189
6190 -- po section --
6191 ELSE IF (p_type = 'PO') THEN
6192 --print_debug('Inside get_inspection_qty PO...', 4);
6193
6194 OPEN c_po_source_lines;
6195 LOOP
6196 FETCH c_po_source_lines INTO l_rcv_transaction_id;
6197 EXIT WHEN c_po_source_lines%NOTFOUND;
6198 --print_debug('PO l_supply_source_id is ' || to_char(l_rcv_transaction_id), 4);
6199
6200 rcv_quantities_s.get_available_quantity(
6201 'INSPECT'
6202 ,l_rcv_transaction_id
6203 ,''
6204 ,''
6205 ,null
6206 ,''
6207 ,l_cur_qty
6208 ,l_tolerable_qty
6209 ,l_cur_uom_code);
6210
6211 -- they pass me unit of measure, now i get uom code
6212
6213 select uom_code
6214 into l_cur_uom_code
6215 from mtl_units_of_measure
6216 where unit_of_measure = l_cur_uom_code;
6217
6218 IF (l_cur_uom_code <> p_uom_code) THEN
6219 l_cur_qty := inv_convert.inv_um_convert(
6220 p_item_id
6221 , null
6222 , l_cur_qty
6223 , l_cur_uom_code
6224 , p_uom_code
6225 , ''
6226 ,'');
6227 END IF;
6228 l_total_qty := l_total_qty + l_cur_qty;
6229 END LOOP;
6230 CLOSE c_po_source_lines;
6231
6232 -- intransit or receipt section --
6233 ELSE IF (p_type in ('INTSHIP','RECEIPT')) THEN
6234 --print_debug('Inside get_inspection_qty RECEIPT...', 4);
6235
6236 OPEN c_intship_source_lines;
6237 LOOP
6238 FETCH c_intship_source_lines INTO l_rcv_transaction_id;
6239
6240 --print_debug('before exit RECEIPT l_rcv_transaction_id is ' || l_rcv_transaction_id, 4);
6241
6242 EXIT WHEN c_intship_source_lines%NOTFOUND;
6243
6244 --print_debug('after exit RECEIPT l_rcv_transaction_id is ' || l_rcv_transaction_id, 4);
6245
6246 rcv_quantities_s.get_available_quantity(
6247 'INSPECT'
6248 ,l_rcv_transaction_id
6249 ,''
6250 ,''
6251 ,null
6252 ,''
6253 ,l_cur_qty
6254 ,l_tolerable_qty
6255 ,l_cur_uom_code);
6256
6257 -- they pass me unit of measure, now i get uom code
6258 select uom_code
6259 into l_cur_uom_code
6260 from mtl_units_of_measure
6261 where unit_of_measure = l_cur_uom_code;
6262
6263 IF (l_cur_uom_code <> p_uom_code) THEN
6264 l_cur_qty := inv_convert.inv_um_convert(
6265 p_item_id
6266 , null
6267 , l_cur_qty
6268 , l_cur_uom_code
6269 , p_uom_code
6270 , ''
6271 , '');
6272 END IF;
6273 l_total_qty := l_total_qty + l_cur_qty;
6274 END LOOP;
6275 CLOSE c_intship_source_lines;
6276
6277 -- rma section --
6278 ELSE IF (p_type = 'RMA') THEN
6279 OPEN c_rma_source_lines;
6280 LOOP
6281 FETCH c_rma_source_lines INTO l_rcv_transaction_id;
6282 EXIT WHEN c_rma_source_lines%NOTFOUND;
6283
6284 rcv_quantities_s.get_available_quantity(
6285 'INSPECT'
6286 ,l_rcv_transaction_id
6287 ,''
6288 ,''
6289 ,null
6290 ,''
6291 ,l_cur_qty
6292 ,l_tolerable_qty
6293 ,l_cur_uom_code);
6294
6295 -- they pass me unit of measure, now i get uom code
6296 select uom_code
6297 into l_cur_uom_code
6298 from mtl_units_of_measure
6299 where unit_of_measure = l_cur_uom_code;
6300
6301 IF (l_cur_uom_code <> p_uom_code) THEN
6302 l_cur_qty := inv_convert.inv_um_convert(
6303 p_item_id
6304 , null
6305 , l_cur_qty
6306 , l_cur_uom_code
6307 , p_uom_code
6308 , ''
6309 , '');
6310 END IF;
6311 l_total_qty := l_total_qty + l_cur_qty;
6312 END LOOP;
6313 CLOSE c_rma_source_lines;
6314
6315 END IF;
6316 END IF;
6317 END IF;
6318 END IF;
6319
6320 x_inspection_qty := l_total_qty;
6321 x_return_status := fnd_api.g_ret_sts_success;
6322
6323 return x_inspection_qty;
6324
6325 EXCEPTION
6326 when fnd_api.g_exc_error THEN
6327 x_return_status := fnd_api.g_ret_sts_error;
6328 -- Get message count and data
6329 fnd_msg_pub.count_and_get ( p_count => l_msg_count , p_data => x_msg_data);
6330 print_debug('***Execution error occured***', 4);
6331 return 0; --Bug #3908752
6332 when fnd_api.g_exc_unexpected_error THEN
6333 x_return_status := fnd_api.g_ret_sts_unexp_error;
6334 -- Get message count and data
6335 fnd_msg_pub.count_and_get ( p_count => l_msg_count , p_data => x_msg_data);
6336 print_debug('***Unexpected error occured***', 4);
6337 return 0;
6338 when others THEN
6339 x_return_status := fnd_api.g_ret_sts_unexp_error ;
6340 IF fnd_msg_pub.check_msg_level(fnd_msg_pub.g_msg_lvl_unexp_error)
6341 THEN
6342 fnd_msg_pub.add_exc_msg ( g_pkg_name, 'get_inspection_qty');
6343 END IF;
6344 -- Get message count and data
6345 fnd_msg_pub.count_and_get ( p_count => l_msg_count, p_data => x_msg_data);
6346 IF (c_txn_lines%isopen) THEN
6347 CLOSE c_txn_lines;
6348 END IF;
6349 print_debug('***Error occured while getting Inspection Qty : ' || sqlerrm || ' ***' , 4);
6350 return 0;
6351 END get_inspection_qty;
6352
6353 -------------------------------------------------------------
6354 --------- wrapper function
6355 -------------------------------------------------------------
6356 FUNCTION get_inspection_qty_wrapper(
6357 p_type IN VARCHAR2
6358 , p_id1 IN NUMBER := NULL
6359 , p_id2 IN NUMBER := NULL
6360 , p_id3 IN NUMBER := NULL
6361 , p_organization_id IN NUMBER
6362 , p_item_id IN NUMBER
6363 , p_uom_code IN VARCHAR2) RETURN NUMBER
6364 IS
6365 l_inspection_qty NUMBER;
6366 l_return_status VARCHAR2(10);
6367 l_msg_data VARCHAR2(5000);
6368
6369 l_debug number := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
6370 BEGIN
6371 --print_debug('Inside wrapper, p_type is ' || p_type, 4);
6372 --print_debug('Inside wrapper, p_id1 is ' || p_id1, 4);
6373 --print_debug('Inside wrapper, p_org is ' || to_char(p_organization_id), 4);
6374 --print_debug('Inside wrapper, p_item is ' || to_char(p_item_id), 4);
6375 --print_debug('Inside wrapper,p_uom is ' || p_uom_code, 4);
6376
6377 IF (p_type = 'LPN') THEN
6378 --print_debug('Inside LPN...', 4);
6379
6380 return inv_rcv_std_inspect_apis.get_inspection_qty(
6381 p_type => p_type
6382 , p_lpn_id => p_id1
6383 , p_po_header_id => NULL
6384 , p_po_release_id => NULL
6385 , p_po_line_id => NULL
6386 , p_shipment_header_id => NULL
6387 , p_oe_order_header_id => NULL
6388 , p_organization_id => p_organization_id
6389 , p_item_id => p_item_id
6390 , p_uom_code => p_uom_code
6391 , x_inspection_qty => l_inspection_qty
6392 , x_return_status => l_return_status
6393 , x_msg_data => l_msg_data);
6394
6395 ELSE IF (p_type = 'RMA') THEN
6396 --print_debug('Inside RMA...', 4);
6397
6398 return inv_rcv_std_inspect_apis.get_inspection_qty(
6399 p_type => p_type
6400 , p_lpn_id => NULL
6401 , p_po_header_id => NULL
6402 , p_po_release_id => NULL
6403 , p_po_line_id => NULL
6404 , p_shipment_header_id => NULL
6405 , p_oe_order_header_id => p_id1
6406 , p_organization_id => p_organization_id
6407 , p_item_id => p_item_id
6408 , p_uom_code => p_uom_code
6409 , x_inspection_qty => l_inspection_qty
6410 , x_return_status => l_return_status
6411 , x_msg_data => l_msg_data);
6412
6413 ELSE IF (p_type in ('INTSHIP', 'RECEIPT')) THEN
6414 --print_debug('Inside Intship/Receipt...', 4);
6415
6416 --print_debug('p_shipment_header_id=p_id1 is ' || to_char(p_id1), 4);
6417
6418 return inv_rcv_std_inspect_apis.get_inspection_qty(
6419 p_type => p_type
6420 , p_lpn_id => NULL
6421 , p_po_header_id => NULL
6422 , p_po_release_id => NULL
6423 , p_po_line_id => NULL
6424 , p_shipment_header_id => p_id1
6425 , p_oe_order_header_id => NULL
6426 , p_organization_id => p_organization_id
6427 , p_item_id => p_item_id
6428 , p_uom_code => p_uom_code
6429 , x_inspection_qty => l_inspection_qty
6430 , x_return_status => l_return_status
6431 , x_msg_data => l_msg_data);
6432
6433 ELSE IF (p_type = 'PO') THEN
6434 --print_debug('Inside PO...', 4);
6435
6436 return inv_rcv_std_inspect_apis.get_inspection_qty(
6437 p_type => p_type
6438 , p_lpn_id => NULL
6439 , p_po_header_id => p_id1
6440 , p_po_release_id => p_id2
6441 , p_po_line_id => p_id3
6442 , p_shipment_header_id => NULL
6443 , p_oe_order_header_id => NULL
6444 , p_organization_id => p_organization_id
6445 , p_item_id => p_item_id
6446 , p_uom_code => p_uom_code
6447 , x_inspection_qty => l_inspection_qty
6448 , x_return_status => l_return_status
6449 , x_msg_data => l_msg_data);
6450 END IF;
6451 END IF;
6452 END IF;
6453 END IF;
6454
6455 END get_inspection_qty_wrapper;
6456
6457
6458
6459
6460
6461
6462
6463
6464 -- given a particular lpn id, organization, and item, this method will return
6465 -- the po associated with that item. if there are multiple po's associated with
6466 -- that restriction criteria, then it will return a status of 1.
6467 -- if successful, then a status of 0.
6468 PROCEDURE obtain_receiving_information(
6469 p_lpn_id IN NUMBER
6470 , p_organization_id IN NUMBER
6471 , p_inventory_item_id IN NUMBER
6472 , x_po_id OUT NOCOPY VARCHAR2
6473 , x_po_number OUT NOCOPY VARCHAR2
6474 , x_po_return_status OUT NOCOPY VARCHAR2
6475 , x_vendor_id OUT NOCOPY VARCHAR2
6476 , x_vendor_name OUT NOCOPY VARCHAR2
6477 , x_asl_status_id OUT NOCOPY VARCHAR2
6478 , x_asl_status_dsp OUT NOCOPY VARCHAR2
6479 , x_rma_id OUT NOCOPY VARCHAR2
6480 , x_rma_number OUT NOCOPY VARCHAR2
6481 , x_rma_return_status OUT NOCOPY VARCHAR2
6482 , x_customer_id OUT NOCOPY VARCHAR2
6483 , x_customer_number OUT NOCOPY VARCHAR2
6484 , x_customer_name OUT NOCOPY VARCHAR2
6485 , x_intshp_id OUT NOCOPY VARCHAR2
6486 , x_intshp_number OUT NOCOPY VARCHAR2
6487 , x_intshp_return_status OUT NOCOPY VARCHAR2
6488 , x_receipt_number OUT NOCOPY VARCHAR2
6489 , x_receipt_return_status OUT NOCOPY VARCHAR2
6490 , x_msg_count OUT NOCOPY VARCHAR2
6491 , x_msg_data OUT NOCOPY VARCHAR2)
6492 IS
6493 v_count_po NUMBER;
6494 v_count_rma NUMBER;
6495 v_count_intshp NUMBER;
6496 v_po_line_id NUMBER;
6497
6498 l_progress VARCHAR2(30);
6499 l_debug number := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
6500 BEGIN
6501
6502 IF (l_debug = 1) THEN
6503 print_debug('begin obtain_receiving_info in db', 4);
6504 print_debug('passed in lpn, org, item' || p_lpn_id || ':' || p_organization_id || ':' || p_inventory_item_id, 4);
6505 END IF;
6506 l_progress := '0';
6507
6508 --BUG 3444196: Modify the following query to avoid the
6509 --'Non-mergable view exists for the following SQL' complaints
6510 SELECT COUNT(DISTINCT pha.po_header_id)
6511 INTO v_count_po
6512 FROM mtl_txn_request_lines mtrl, po_line_locations_all plla, po_headers_all pha
6513 WHERE reference = 'PO_LINE_LOCATION_ID'
6514 AND mtrl.reference_id = plla.line_location_id
6515 AND plla.po_header_id = pha.po_header_id
6516 AND mtrl.quantity > nvl(mtrl.quantity_delivered, 0)
6517 AND mtrl.lpn_id = p_lpn_id
6518 AND mtrl.organization_id = p_organization_id
6519 AND mtrl.inventory_item_id = p_inventory_item_id ;
6520
6521 --dbms_output.put_line('vcountpo=' || v_count_po);
6522 l_progress := '10';
6523 IF (l_debug = 1) THEN
6524 print_debug('obtain_receiving_info:progress=' || l_progress, 4);
6525 END IF;
6526
6527 --if only 1 line is returned by the above query, then we need to retrieve multiple values
6528 -- for that line and set the status to 0, representing success
6529 if (v_count_po = 1) then
6530 x_po_return_status := 0;
6531 x_msg_count := ' ';
6532 x_msg_data := ' ';
6533
6534 select distinct pha.po_header_id, pha.segment1, pv.vendor_id, pv.vendor_name, plla.po_line_id
6535 into x_po_id, x_po_number, x_vendor_id, x_vendor_name, v_po_line_id
6536 from mtl_txn_request_lines mtrl, po_line_locations_all plla, po_headers_all pha, po_vendors pv
6537 where reference = 'PO_LINE_LOCATION_ID'
6538 and mtrl.reference_id = plla.line_location_id
6539 and plla.po_header_id = pha.po_header_id
6540 and mtrl.quantity > nvl(mtrl.quantity_delivered, 0)
6541 and pha.vendor_id = pv.vendor_id
6542 and mtrl.lpn_id = p_lpn_id
6543 and mtrl.organization_id = p_organization_id
6544 and mtrl.inventory_item_id = p_inventory_item_id;
6545 l_progress := '20';
6546 IF (l_debug = 1) THEN
6547 print_debug('obtain_receiving_info:progress=' || l_progress, 4);
6548 END IF;
6549
6550
6551 ----dbms_output.put_line('x_po_id=' || x_po_id);
6552 --dbms_output.put_line('x_po_number=' || x_po_number);
6553 --dbms_output.put_line('x_vendor_id=' || x_vendor_id);
6554
6555
6556 begin
6557 -- get ASL -- query provided by jenny zheng from QA team
6558 SELECT pasv.asl_status_id, pasv.asl_status_dsp
6559 into x_asl_status_id, x_asl_status_dsp
6560 FROM po_asl_suppliers_v pasv, po_lines pl, po_headers ph
6561 WHERE pl.item_id = pasv.item_id
6562 AND pl.po_line_id = v_po_line_id -- here use the variable from above
6563 AND pl.po_header_id = ph.po_header_id
6564 AND ph.vendor_id(+) = pasv.vendor_id
6565 AND ph.vendor_site_id(+) = pasv.vendor_site_id
6566 AND (p_organization_id = pasv.using_organization_id
6567 OR pasv.using_organization_id = -1);
6568 l_progress := '30';
6569 IF (l_debug = 1) THEN
6570 print_debug('obtain_receiving_info:progress=' || l_progress, 4);
6571 END IF;
6572 exception
6573 when others then
6574 x_asl_status_id := ' ';
6575 x_asl_status_dsp := ' ';
6576 end;
6577
6578
6579 begin
6580 -- obtain receipt number
6581 select distinct rsh.receipt_num, '0'
6582 into x_receipt_number, x_receipt_return_status
6583 from mtl_txn_request_lines mtrl, rcv_transactions rt, rcv_shipment_headers rsh
6584 where reference = 'PO_LINE_LOCATION_ID'
6585 and mtrl.reference_id = rt.po_line_location_id
6586 and mtrl.quantity > nvl(mtrl.quantity_delivered, 0)
6587 and rt.shipment_header_id = rsh.shipment_header_id
6588 and mtrl.lpn_id = p_lpn_id
6589 and mtrl.lpn_id = rt.transfer_lpn_id --Bug#7390895
6590 and mtrl.organization_id = p_organization_id
6591 and mtrl.inventory_item_id = p_inventory_item_id;
6592 l_progress := '40';
6593 IF (l_debug = 1) THEN
6594 print_debug('obtain_receiving_info:progress=' || l_progress, 4);
6595 END IF;
6596 exception
6597 when others then
6598 x_receipt_number := ' ';
6599 x_receipt_return_status := ' ';
6600 end;
6601
6602
6603 else if (v_count_po = 0) then
6604 x_po_return_status := -1;
6605 x_msg_count := ' ';
6606 x_msg_data := 'NO PO LINES FOUND';
6607 --dbms_output.put_line('no po lines found');
6608
6609 else if (v_count_po > 1) then
6610 x_po_return_status := 1;
6611 x_msg_count := ' ';
6612 x_msg_data := 'MULTIPLE PO LINES FOUND';
6613 --dbms_output.put_line('many po lines found');
6614
6615 end if;
6616 end if;
6617 end if;
6618
6619
6620
6621
6622 --obtain RMA, CUSTOMER INFO
6623 --BUG 3444196: Modify the following query to avoid the
6624 --'Non-mergable view exists for the following SQL' complaints
6625 SELECT COUNT(DISTINCT oeh.header_id)
6626 INTO v_count_rma
6627 FROM mtl_txn_request_lines mtrl, oe_order_lines_all oel, oe_order_headers_all oeh
6628 WHERE reference = 'ORDER_LINE_ID'
6629 AND mtrl.reference_id = oel.line_id
6630 AND mtrl.quantity > nvl(mtrl.quantity_delivered, 0)
6631 AND oel.header_id = oeh.header_id
6632 AND mtrl.lpn_id = p_lpn_id
6633 AND mtrl.organization_id = p_organization_id
6634 AND mtrl.inventory_item_id = p_inventory_item_id;
6635
6636 l_progress := '50';
6637 IF (l_debug = 1) THEN
6638 print_debug('obtain_receiving_info:progress=' || l_progress, 4);
6639 END IF;
6640 if (v_count_rma = 1) then
6641 x_rma_return_status := 0;
6642 x_msg_count := ' ';
6643 x_msg_data := ' ';
6644
6645 select distinct oeh.header_id, oeh.order_number, oest.customer_id, oest.customer_number, oest.name
6646 into x_rma_id, x_rma_number, x_customer_id, x_customer_number, x_customer_name
6647 from mtl_txn_request_lines mtrl, oe_order_lines_all oel, oe_order_headers_all oeh, oe_sold_to_orgs_v oest
6648 where reference = 'ORDER_LINE_ID'
6649 and mtrl.reference_id = oel.line_id
6650 and mtrl.quantity > nvl(mtrl.quantity_delivered, 0)
6651 and oel.header_id = oeh.header_id
6652 and oeh.sold_to_org_id = oest.customer_id
6653 and mtrl.lpn_id = p_lpn_id
6654 and mtrl.organization_id = p_organization_id
6655 and mtrl.inventory_item_id = p_inventory_item_id;
6656 l_progress := '60';
6657 IF (l_debug = 1) THEN
6658 print_debug('obtain_receiving_info:progress=' || l_progress, 4);
6659 END IF;
6660
6661 begin
6662 --obtain the receipt number
6663 select distinct rsh.receipt_num, '0'
6664 into x_receipt_number, x_receipt_return_status
6665 from mtl_txn_request_lines mtrl, rcv_transactions rt, rcv_shipment_headers rsh
6666 where reference = 'ORDER_LINE_ID'
6667 and mtrl.reference_id = rt.oe_order_line_id
6668 and mtrl.quantity > nvl(mtrl.quantity_delivered, 0)
6669 and rt.shipment_header_id = rsh.shipment_header_id
6670 and mtrl.lpn_id = p_lpn_id
6671 and mtrl.organization_id = p_organization_id
6672 and mtrl.inventory_item_id = p_inventory_item_id;
6673 l_progress := '70';
6674 IF (l_debug = 1) THEN
6675 print_debug('obtain_receiving_info:progress=' || l_progress, 4);
6676 END IF;
6677 exception
6678 when others then
6679 x_receipt_number := ' ';
6680 x_receipt_return_status := ' ';
6681 end;
6682
6683
6684 else if (v_count_rma = 0) then
6685 x_rma_return_status := -1;
6686 x_msg_count := ' ';
6687 x_msg_data := x_msg_data || 'NO RMA LINES FOUND';
6688
6689 else if (v_count_rma > 1) then
6690 x_rma_return_status := 1;
6691 x_msg_count := ' ';
6692 x_msg_data := x_msg_data || 'MULTIPLE RMA LINES FOUND';
6693
6694 end if;
6695 end if;
6696 end if;
6697
6698
6699
6700 -- obtain SHIPMENT RECEIPT INFORMATION
6701 SELECT COUNT(DISTINCT rsl.shipment_header_id)
6702 INTO v_count_intshp
6703 FROM mtl_txn_request_lines mtrl, rcv_shipment_lines rsl
6704 WHERE reference = 'SHIPMENT_LINE_ID'
6705 AND mtrl.reference_id = rsl.shipment_line_id
6706 AND mtrl.quantity > nvl(mtrl.quantity_delivered, 0)
6707 AND mtrl.lpn_id = p_lpn_id
6708 AND mtrl.organization_id = p_organization_id
6709 AND mtrl.inventory_item_id = p_inventory_item_id;
6710
6711 l_progress := '80';
6712 IF (l_debug = 1) THEN
6713 print_debug('obtain_receiving_info:progress=' || l_progress, 4);
6714 END IF;
6715
6716 if (v_count_intshp = 1) then
6717 x_intshp_return_status := 0;
6718 x_msg_count := ' ';
6719 x_msg_data := ' ';
6720
6721 select distinct rsl.shipment_header_id, rsh.shipment_num, rsh.receipt_num
6722 into x_intshp_id, x_intshp_number, x_receipt_number
6723 from mtl_txn_request_lines mtrl, rcv_shipment_lines rsl, rcv_shipment_headers rsh
6724 where reference = 'SHIPMENT_LINE_ID'
6725 and mtrl.quantity > nvl(mtrl.quantity_delivered, 0)
6726 and mtrl.reference_id = rsl.shipment_line_id
6727 and rsl.shipment_header_id = rsh.shipment_header_id
6728 and mtrl.lpn_id = p_lpn_id
6729 and mtrl.organization_id = p_organization_id
6730 and mtrl.inventory_item_id = p_inventory_item_id;
6731 l_progress := '90';
6732 IF (l_debug = 1) THEN
6733 print_debug('obtain_receiving_info:progress=' || l_progress, 4);
6734 END IF;
6735
6736 else if (v_count_intshp = 0) then
6737 x_intshp_return_status := -1;
6738 x_msg_count := ' ';
6739 x_msg_data := x_msg_data || 'NO SHIPMENT RECEIPT LINES FOUND';
6740
6741 else if (v_count_intshp > 1) then
6742 x_intshp_return_status := 1;
6743 x_msg_count := ' ';
6744 x_msg_data := x_msg_data || 'MULTIPLE SHIPMENT RECEIPT LINES FOUND';
6745
6746 end if;
6747 end if;
6748 end if;
6749
6750 exception
6751 when others then
6752 IF SQLCODE IS NOT NULL THEN
6753 inv_mobile_helper_functions.sql_error('inv_rcv_std_inspect_apis.obtain_receiving_information', l_progress, SQLCODE);
6754 END IF;
6755
6756
6757
6758
6759 end obtain_receiving_information;
6760
6761
6762 ---------------- END OF SECTION ADDED BY MANU GUPTA --------------------
6763 ------------------------------------------------------------------------
6764
6765
6766 FUNCTION is_revision_required (
6767 p_source_type IN VARCHAR2
6768 , p_source_id IN NUMBER
6769 , p_item_id IN NUMBER
6770 ) RETURN NUMBER
6771 IS
6772 l_count NUMBER;
6773 l_debug number := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
6774 BEGIN
6775
6776 l_count := 1;
6777
6778 IF (p_source_type = 'PO') THEN
6779 BEGIN
6780 SELECT 1
6781 INTO l_count
6782 FROM rcv_supply rs
6783 , rcv_transactions rt
6784 WHERE rs.item_id = p_item_id
6785 AND rs.item_revision IS NULL
6786 AND rs.po_header_id = p_source_id
6787 AND rs.rcv_transaction_id = rt.transaction_id
6788 AND rt.inspection_status_code = 'NOT INSPECTED'
6789 AND rs.supply_type_code = 'RECEIVING'
6790 AND rt.transaction_type <> 'UNORDERED'
6791 AND ROWNUM < 2;
6792 EXCEPTION
6793 WHEN no_data_found THEN
6794 l_count := 0;
6795 END;
6796 ELSE IF (p_source_type IN ('INTSHIP', 'RECEIPT')) THEN
6797 BEGIN
6798 SELECT 1
6799 INTO l_count
6800 FROM rcv_supply rs
6801 , rcv_transactions rt
6802 WHERE rs.item_id = p_item_id
6803 AND rs.item_revision IS NULL
6804 AND rs.shipment_header_id = p_source_id
6805 AND rs.rcv_transaction_id = rt.transaction_id
6806 AND rt.inspection_status_code = 'NOT INSPECTED'
6807 AND rs.supply_type_code = 'RECEIVING'
6808 AND rt.transaction_type <> 'UNORDERED'
6809 AND ROWNUM < 2;
6810 EXCEPTION
6811 WHEN no_data_found THEN
6812 l_count := 0;
6813 END;
6814 ELSE IF (p_source_type = 'RMA') THEN
6815 BEGIN
6816 SELECT 1
6817 INTO l_count
6818 FROM rcv_supply rs
6819 , rcv_transactions rt
6820 WHERE rs.item_id = p_item_id
6821 AND rs.item_revision IS NULL
6822 AND rs.oe_order_header_id = p_source_id
6823 AND rs.rcv_transaction_id = rt.transaction_id
6824 AND rt.inspection_status_code = 'NOT INSPECTED'
6825 AND rs.supply_type_code = 'RECEIVING'
6826 AND rt.transaction_type <> 'UNORDERED'
6827 AND ROWNUM < 2;
6828 EXCEPTION
6829 WHEN no_data_found THEN
6830 l_count := 0;
6831 END;
6832 END IF;
6833 END IF;
6834 END IF;
6835
6836 RETURN l_count;
6837 END is_revision_required;
6838
6839
6840 end inv_rcv_std_inspect_apis;