1 PACKAGE BODY rcv_processor_pvt AS
2 /* $Header: RCVPROCB.pls 120.2.12010000.1 2008/07/24 14:36:23 appldev ship $ */
3 --
4 -- Purpose: To maintain reservation
5 --
6 -- MODIFICATION HISTORY
7 -- Person Date Comments
8 -- --------- ------ ------------------------------------------
9 -- pparthas 07/24/03 Created Package
10 --
11
12 g_asn_debug VARCHAR2(1) := NVL(fnd_profile.VALUE('RCV_DEBUG_MODE'), 'N');
13
14 PROCEDURE insert_rcv_lots_supply(
15 p_api_version IN NUMBER,
16 p_init_msg_list IN VARCHAR2,
17 x_return_status OUT NOCOPY VARCHAR2,
18 p_interface_transaction_id IN NUMBER,
19 p_shipment_line_id IN NUMBER,
20 p_supply_source_id IN NUMBER,
21 p_source_type_code IN VARCHAR2,
22 p_transaction_type IN VARCHAR2
23 ) IS
24 CURSOR c IS
25 SELECT rls.ROWID
26 FROM rcv_lots_supply rls,
27 rcv_transactions rt
28 WHERE rt.interface_transaction_id = p_interface_transaction_id
29 AND rls.transaction_id = rt.transaction_id;
30
31 l_rowid VARCHAR2(255);
32 l_transaction_id rcv_transactions.transaction_id%TYPE;
33 l_lot_count NUMBER;
34 l_organization_id NUMBER;
35 l_lpn_id rcv_supply.lpn_id%TYPE;
36 l_validation_flag rcv_transactions_interface.validation_flag%TYPE;
37 l_shipment_header_id rcv_shipment_headers.shipment_header_id%TYPE;
38
39 CURSOR lot_numbers(
40 l_interface_id NUMBER
41 ) IS
42 SELECT mtlt.lot_number,
43 SUM(mtlt.primary_quantity),
44 SUM(mtlt.transaction_quantity),
45 rti.shipment_header_id,
46 rti.item_id,
47 rti.unit_of_measure,
48 rti.to_organization_id
49 FROM mtl_transaction_lots_temp mtlt,
50 rcv_transactions_interface rti
51 WHERE product_transaction_id = l_interface_id
52 AND product_code = 'RCV'
53 AND rti.interface_transaction_id = mtlt.product_transaction_id
54 GROUP BY mtlt.lot_number,
55 rti.shipment_header_id,
56 rti.shipment_line_id,
57 rti.item_id,
58 rti.unit_of_measure,
59 rti.to_organization_id;
60
61 /* Bug 4870857: Added condition for shipment_line_id in the Exists clause
62 ** to restrict the rows returned by the Cursor within a
63 ** given shipment header.
64 */
65 CURSOR supply_quantity(
66 l_lot_num VARCHAR2,
67 l_shipment_header_id NUMBER,
68 l_item_id NUMBER
69 ) IS
70 SELECT rls.quantity,
71 rls.primary_quantity,
72 rls.shipment_line_id
73 FROM rcv_lots_supply rls
74 WHERE rls.lot_num = l_lot_num
75 AND rls.supply_type_code = 'SHIPMENT'
76 AND EXISTS(SELECT 1
77 FROM rcv_shipment_lines rsl
78 WHERE rsl.shipment_header_id = l_shipment_header_id
79 AND rsl.item_id = l_item_id
80 AND rsl.shipment_line_id = rls.shipment_line_id);
81
82 l_lot_num rcv_lots_supply.lot_num%TYPE;
83 l_qty_to_be_updated NUMBER;
84 l_primary_qty_to_be_updated NUMBER;
85 l_ship_id NUMBER;
86 l_ship_line_id NUMBER;
87 l_rls_qty NUMBER;
88 l_rls_primary_qty NUMBER;
89 l_item_id rcv_transactions_interface.item_id%TYPE;
90 l_parent_uom VARCHAR2(25);
91 l_txn_uom VARCHAR2(25);
92 l_primary_uom VARCHAR2(25);
93 l_to_org_id NUMBER;
94 BEGIN
95 IF (g_asn_debug = 'Y') THEN
96 asn_debug.put_line('p_interface_transaction_id ' || p_interface_transaction_id);
97 asn_debug.put_line('p_shipment_line_id ' || p_shipment_line_id);
98 asn_debug.put_line('p_supply_source_id ' || p_supply_source_id);
99 asn_debug.put_line('p_source_type_code ' || p_source_type_code);
100 asn_debug.put_line('p_transaction_type ' || p_transaction_type);
101 END IF;
102
103 x_return_status := fnd_api.g_ret_sts_success;
104
105 /* We can now come here if it is a lot-serial item and there is
106 * no row in mtl_transaction_lots_temp if user has not entered
107 * any lot/serial info for this transaction(Receive, Transfer etc).
108 * In this case we do not error nor insert. So return.
109 */
110 SELECT COUNT(*)
111 INTO l_lot_count
112 FROM mtl_transaction_lots_temp mtlt
113 WHERE mtlt.product_transaction_id = p_interface_transaction_id
114 AND mtlt.product_code = 'RCV';
115
116 IF (l_lot_count = 0) THEN
117 RETURN;
118 END IF;
119
120 IF (g_asn_debug = 'Y') THEN
121 asn_debug.put_line('l_lot_count ' || l_lot_count);
122 END IF;
123
124 /* We need to insert into rcv_lots_supply and
125 * rcv_serials_supply table only when we come through ROI
126 * or when we come through desktop and have lpn info.
127 * We insert lpn_id in rcv_supply. So return if there is
128 * a value and validation_flag is N.
129 */
130 SELECT NVL(validation_flag, 'N')
131 INTO l_validation_flag
132 FROM rcv_transactions_interface
133 WHERE interface_transaction_id = p_interface_transaction_id;
134
135 SELECT NVL(lpn_id, -999)
136 INTO l_lpn_id
137 FROM rcv_supply
138 WHERE supply_source_id = p_supply_source_id;
139
140 IF ( l_validation_flag = 'N'
141 AND l_lpn_id = -999) THEN
142 RETURN;
143 END IF;
144
145 IF (g_asn_debug = 'Y') THEN
146 asn_debug.put_line('l_validation_flag ' || l_validation_flag);
147 asn_debug.put_line('l_lpn_id ' || l_lpn_id);
148 END IF;
149
150 SELECT transaction_id,
151 organization_id
152 INTO l_transaction_id,
153 l_organization_id
154 FROM rcv_transactions rt
155 WHERE rt.interface_transaction_id = p_interface_transaction_id;
156
157 IF (g_asn_debug = 'Y') THEN
158 asn_debug.put_line('before insert_lot_supply');
159 END IF;
160
161 insert_lot_supply(p_interface_transaction_id,
162 'RECEIVING',
163 p_supply_source_id,
164 x_return_status
165 );
166
167 IF (g_asn_debug = 'Y') THEN
168 asn_debug.put_line('After insert_lot_supply');
169 END IF;
170
171 /* If this is a receive for an internal shipment or interorg transfer
172 * then we need to update the values for the shipment
173 * supply in rcv_lots_supply.
174 */
175 /* INVCONV , update for process transactions also.
176 Remove the process specific restriction. Punit Kumar */
177
178 -- roi enhacements for OPM.bug# 3061052
179 -- don't update for OPM transactions.
180
181 /* IF (gml_process_flags.check_process_orgn(p_organization_id => l_organization_id) = 0) THEN */
182
183 IF ( p_transaction_type = 'RECEIVE'
184 AND p_source_type_code IN('INVENTORY', 'REQ')) THEN --{
185 /* Bug 3376348, 3459830.
186 * It might happen that the original shipment lines are split
187 * into multiple shipment_lines (when lpn has 2 lots for eg).
188 * So instead of using shipment_line_id to delete use
189 * shipment_header_id.
190 */
191 IF (g_asn_debug = 'Y') THEN
192 asn_debug.put_line('In insert rcv_lots_supply for source type code INVENTORY or REQ');
193 END IF;
194
195 OPEN lot_numbers(p_interface_transaction_id);
196
197 IF (g_asn_debug = 'Y') THEN
198 asn_debug.put_line('Opened lot_numbers cursor');
199 END IF;
200
201 LOOP --{
202 FETCH lot_numbers INTO l_lot_num,
203 l_primary_qty_to_be_updated,
204 l_qty_to_be_updated,
205 l_shipment_header_id,
206 l_item_id,
207 l_txn_uom,
208 l_to_org_id;
209 EXIT WHEN lot_numbers%NOTFOUND;
210
211 IF (g_asn_debug = 'Y') THEN
212 asn_debug.put_line('After fetch');
213 asn_debug.put_line('l_lot_num ' || l_lot_num);
214 asn_debug.put_line('l_primary_qty_to_be_updated ' || l_primary_qty_to_be_updated);
215 asn_debug.put_line('l_qty_to_be_updated ' || l_qty_to_be_updated);
216 asn_debug.put_line('l_shipment_header_id ' || l_shipment_header_id);
217 asn_debug.put_line('l_item_id ' || l_item_id);
218 asn_debug.put_line('l_txn_uom ' || l_txn_uom);
219 asn_debug.put_line('l_to_org_id ' || l_to_org_id);
220 END IF;
221
222 SELECT MAX(primary_unit_of_measure)
223 INTO l_primary_uom
224 FROM mtl_system_items
225 WHERE mtl_system_items.inventory_item_id = l_item_id
226 AND mtl_system_items.organization_id = l_to_org_id;
227
228 IF (g_asn_debug = 'Y') THEN
229 asn_debug.put_line('l_primary_uom ' || l_primary_uom);
230 END IF;
231
232 /* l_qty_to_be_updated shd be in
233 * terms of the parent's uom. For
234 * shipment supply qty, it must be
235 * in terms of uom in rsl.
236 */
237 OPEN supply_quantity(l_lot_num,
238 l_shipment_header_id,
239 l_item_id
240 );
241
242 IF (g_asn_debug = 'Y') THEN
243 asn_debug.put_line('Opened supply_quantity cursor');
244 END IF;
245
246 LOOP --{
247 FETCH supply_quantity INTO l_rls_qty,
248 l_rls_primary_qty,
249 l_ship_line_id;
250 EXIT WHEN supply_quantity%NOTFOUND
251 OR l_primary_qty_to_be_updated = 0;
252
253 IF (g_asn_debug = 'Y') THEN
254 asn_debug.put_line('l_rls_qty ' || l_rls_qty);
255 asn_debug.put_line('l_rls_primary_qty ' || l_rls_primary_qty);
256 asn_debug.put_line('l_ship_line_id ' || l_ship_line_id);
257 END IF;
258
259 SELECT unit_of_measure
260 INTO l_parent_uom
261 FROM rcv_shipment_lines
262 WHERE shipment_line_id = l_ship_line_id;
263
264 IF (g_asn_debug = 'Y') THEN
265 asn_debug.put_line('l_parent_uom ' || l_parent_uom);
266 END IF;
267
268 IF (l_txn_uom <> l_parent_uom) THEN
269 l_qty_to_be_updated := rcv_transactions_interface_sv.convert_into_correct_qty(l_primary_qty_to_be_updated,
270 l_primary_uom,
271 l_item_id,
272 l_parent_uom
273 );
274 END IF;
275
276 IF (g_asn_debug = 'Y') THEN
277 asn_debug.put_line('l_qty_to_be_updated ' || l_qty_to_be_updated);
278 asn_debug.put_line('l_rls_primary_qty ' || l_rls_primary_qty);
279 asn_debug.put_line('l_primary_qty_to_be_updated ' || l_primary_qty_to_be_updated);
280 END IF;
281
282 IF (l_rls_primary_qty >= l_primary_qty_to_be_updated) THEN --{
283 IF (g_asn_debug = 'Y') THEN
284 asn_debug.put_line('rls primary qty greater');
285 asn_debug.put_line('l_qty_to_be_updated ' || l_qty_to_be_updated);
286 asn_debug.put_line('l_primary_qty_to_be_updated ' || l_primary_qty_to_be_updated);
287 asn_debug.put_line('l_lot_num ' || l_lot_num);
288 asn_debug.put_line('l_ship_line_id ' || l_ship_line_id);
289 END IF;
290
291 UPDATE rcv_lots_supply rls
292 SET quantity = quantity - l_qty_to_be_updated,
293 primary_quantity = primary_quantity - l_primary_qty_to_be_updated
294 WHERE rls.lot_num = l_lot_num
295 AND shipment_line_id = l_ship_line_id
296 AND rls.supply_type_code = 'SHIPMENT';
297
298 l_qty_to_be_updated := 0;
299 l_primary_qty_to_be_updated := 0;
300 ELSE --}{
301 IF (g_asn_debug = 'Y') THEN
302 asn_debug.put_line('rls primary qty lesser');
303 asn_debug.put_line('l_qty_to_be_updated ' || l_qty_to_be_updated);
304 asn_debug.put_line('l_primary_qty_to_be_updated ' || l_primary_qty_to_be_updated);
305 asn_debug.put_line('l_lot_num ' || l_lot_num);
306 asn_debug.put_line('l_ship_line_id ' || l_ship_line_id);
307 END IF;
308
309 UPDATE rcv_lots_supply rls
310 SET quantity = 0,
311 primary_quantity = 0
312 WHERE rls.lot_num = l_lot_num
313 AND shipment_line_id = l_ship_line_id
314 AND rls.supply_type_code = 'SHIPMENT';
315
316 l_qty_to_be_updated := l_qty_to_be_updated - l_rls_qty;
317 l_primary_qty_to_be_updated := l_primary_qty_to_be_updated - l_rls_primary_qty;
318
319 IF (g_asn_debug = 'Y') THEN
320 asn_debug.put_line('l_qty_to_be_updated ' || l_qty_to_be_updated);
321 asn_debug.put_line('l_primary_qty_to_be_updated ' || l_primary_qty_to_be_updated);
322 END IF;
323 END IF; --}
324 END LOOP; --}
325
326 CLOSE supply_quantity;
327
328 IF (g_asn_debug = 'Y') THEN
329 asn_debug.put_line('Close supply_quantity ');
330 END IF;
331 END LOOP; --}
332
333 CLOSE lot_numbers;
334
335 IF (g_asn_debug = 'Y') THEN
336 asn_debug.put_line('Close lot_numbers cursor for source type code INVENTORY or REQ');
337 END IF;
338 END IF; --}
339 /* END IF; */
340
341 IF (g_asn_debug = 'Y') THEN
342 asn_debug.put_line('Exit insert_rcv_lots_supply');
343 END IF;
344 EXCEPTION
345 WHEN NO_DATA_FOUND THEN
346 IF (g_asn_debug = 'Y') THEN
347 asn_debug.put_line('no_data_found insert_rcv_lots_supply');
348 END IF;
349
350 x_return_status := fnd_api.g_ret_sts_error;
351
352 INSERT INTO po_interface_errors
353 (interface_type,
354 interface_transaction_id,
355 error_message,
356 processing_date,
357 creation_date,
358 created_by,
359 last_update_date,
360 last_updated_by,
361 last_update_login,
362 request_id,
363 program_application_id,
364 program_id,
365 program_update_date
366 )
367 SELECT 'RECEIVING',
368 p_interface_transaction_id,
369 'RCV_INSERT_LOT_SUPPLY_FAIL',
370 SYSDATE,
371 rti.creation_date,
372 rti.created_by,
373 rti.last_update_date,
374 rti.last_updated_by,
375 rti.last_update_login,
376 rti.request_id,
377 rti.program_application_id,
378 rti.program_id,
379 rti.program_update_date
380 FROM rcv_transactions_interface rti
381 WHERE rti.interface_transaction_id = p_interface_transaction_id;
382 WHEN OTHERS THEN
383 IF (g_asn_debug = 'Y') THEN
384 asn_debug.put_line('others insert_rcv_lots_supply');
385 END IF;
386
387 x_return_status := fnd_api.g_ret_sts_unexp_error;
388
389 INSERT INTO po_interface_errors
390 (interface_type,
391 interface_transaction_id,
392 error_message,
393 processing_date,
394 creation_date,
395 created_by,
396 last_update_date,
397 last_updated_by,
398 last_update_login,
399 request_id,
400 program_application_id,
401 program_id,
402 program_update_date
403 )
404 SELECT 'RECEIVING',
405 p_interface_transaction_id,
406 'RCV_INSERT_LOT_SUPPLY_ERROR',
407 SYSDATE,
408 rti.creation_date,
409 rti.created_by,
410 rti.last_update_date,
411 rti.last_updated_by,
412 rti.last_update_login,
413 rti.request_id,
414 rti.program_application_id,
415 rti.program_id,
416 rti.program_update_date
417 FROM rcv_transactions_interface rti
418 WHERE rti.interface_transaction_id = p_interface_transaction_id;
419 END insert_rcv_lots_supply;
420
421 --
422 PROCEDURE insert_rcv_serials_supply(
423 p_api_version IN NUMBER,
424 p_init_msg_list IN VARCHAR2,
425 x_return_status OUT NOCOPY VARCHAR2,
426 p_interface_transaction_id IN NUMBER,
427 p_shipment_line_id IN NUMBER,
428 p_supply_source_id IN NUMBER,
429 p_source_type_code IN VARCHAR2,
430 p_transaction_type IN VARCHAR2
431 ) IS
432 CURSOR select_serials(
433 p_interface_transaction_id NUMBER
434 ) IS
435 SELECT msnt.fm_serial_number,
436 msnt.to_serial_number,
437 mtlt.lot_number
438 FROM mtl_serial_numbers_temp msnt,
439 mtl_transaction_lots_temp mtlt
440 WHERE msnt.product_transaction_id = p_interface_transaction_id
441 AND msnt.product_code = 'RCV'
442 AND msnt.transaction_temp_id = mtlt.serial_transaction_temp_id(+);
443
444 l_select_serials select_serials%ROWTYPE;
445 l_serial_prefix VARCHAR2(31);
446 l_from_serial_number NUMBER;
447 l_to_serial_number NUMBER;
448 l_serial_num_length NUMBER;
449 l_prefix_length NUMBER;
450 l_cur_serial_numeric NUMBER;
451 l_cur_serial_number VARCHAR2(30);
452 l_range_numbers NUMBER;
453 l_serial_suffix_length NUMBER;
454 l_delete_shipment_supply VARCHAR2(1) := 'N';
455 l_transaction_id rcv_transactions.transaction_id%TYPE;
456 l_serial_count NUMBER;
457 l_lpn_id rcv_supply.lpn_id%TYPE;
458 l_validation_flag rcv_transactions_interface.validation_flag%TYPE;
459 l_shipment_header_id rcv_shipment_headers.shipment_header_id%TYPE;
460 l_item_id rcv_transactions_interface.item_id%TYPE;
461 BEGIN
462 IF (g_asn_debug = 'Y') THEN
463 asn_debug.put_line('p_interface_transaction_id ' || p_interface_transaction_id);
464 asn_debug.put_line('p_shipment_line_id ' || p_shipment_line_id);
465 asn_debug.put_line('p_supply_source_id ' || p_supply_source_id);
466 asn_debug.put_line('p_source_type_code ' || p_source_type_code);
467 asn_debug.put_line('p_transaction_type ' || p_transaction_type);
468 END IF;
469
470 x_return_status := fnd_api.g_ret_sts_success;
471
472 /* We can now come here if it is a lot-serial item and there is
473 * no row in mtl_transaction_lots_temp if user has not entered
474 * any lot/serial info for this transaction(Receive, Transfer etc).
475 * In this case we do not error nor insert. So return.
476 */
477 SELECT COUNT(*)
478 INTO l_serial_count
479 FROM mtl_serial_numbers_temp msnt
480 WHERE msnt.product_transaction_id = p_interface_transaction_id
481 AND msnt.product_code = 'RCV';
482
483 IF (l_serial_count = 0) THEN
484 RETURN;
485 END IF;
486
487 IF (g_asn_debug = 'Y') THEN
488 asn_debug.put_line('l_serial_count ' || l_serial_count);
489 END IF;
490
491 /* We need to insert into rcv_lots_supply and
492 * rcv_serials_supply table only when we come through ROI
493 * or when we come through desktop and have lpn info.
494 * We insert lpn_id in rcv_supply. So return if there is
495 * a value and validation_flag is N.
496 */
497 SELECT NVL(validation_flag, 'N')
498 INTO l_validation_flag
499 FROM rcv_transactions_interface
500 WHERE interface_transaction_id = p_interface_transaction_id;
501
502 SELECT NVL(lpn_id, -999)
503 INTO l_lpn_id
504 FROM rcv_supply
505 WHERE supply_source_id = p_supply_source_id;
506
507 IF ( l_validation_flag = 'N'
508 AND l_lpn_id = -999) THEN
509 RETURN;
510 END IF;
511
512 IF (g_asn_debug = 'Y') THEN
513 asn_debug.put_line('l_validation_flag ' || l_validation_flag);
514 asn_debug.put_line('l_lpn_id ' || l_lpn_id);
515 END IF;
516
517 OPEN select_serials(p_interface_transaction_id);
518
519 /* If this is a receive for an internal shipment or interorg transfer
520 * then we need to delete the values for the shipment
521 * supply in rcv_shipment_supply since we would have created the
522 * receiving supply for the serial numbers that are used for
523 * receiving. Set l_delete_shipment_supply to Y. This will be
524 * used later to delete the shipment serial supply row.
525 */
526 IF ( p_transaction_type = 'RECEIVE'
527 AND p_source_type_code IN('INVENTORY', 'REQ')) THEN
528 l_delete_shipment_supply := 'Y';
529 END IF;
530
531 LOOP --{
532 FETCH select_serials INTO l_select_serials;
533 EXIT WHEN select_serials%NOTFOUND;
534 split_serial_number(l_select_serials.fm_serial_number,
535 l_serial_prefix,
536 l_from_serial_number
537 );
538 split_serial_number(l_select_serials.to_serial_number,
539 l_serial_prefix,
540 l_to_serial_number
541 );
542 l_range_numbers := l_to_serial_number - l_from_serial_number + 1;
543 l_serial_num_length := LENGTH(l_select_serials.fm_serial_number);
544
545 /* Start Bug#3359105: Modified the following code to consider
546 * the case when the Serial number is numeric instead of being
547 * alphanumeric. In this case l_serial_prefix is NULL and
548 * so needs proper handling.
549 */
550 IF l_serial_prefix IS NOT NULL THEN
551 l_prefix_length := LENGTH(l_serial_prefix);
552 l_serial_suffix_length := l_serial_num_length - l_prefix_length;
553 ELSE
554 l_prefix_length := 0;
555 l_serial_suffix_length := l_serial_num_length;
556 END IF;
557
558 /* End Bug#3359105 */
559 SELECT transaction_id
560 INTO l_transaction_id
561 FROM rcv_transactions rt
562 WHERE rt.interface_transaction_id = p_interface_transaction_id;
563
564 IF (g_asn_debug = 'Y') THEN
565 asn_debug.put_line('l_range_numbers ' || l_range_numbers);
566 asn_debug.put_line('l_serial_num_length ' || l_serial_num_length);
567 asn_debug.put_line('l_prefix_length ' || l_prefix_length);
568 asn_debug.put_line('l_serial_suffix_length ' || l_serial_suffix_length);
569 END IF;
570
571 FOR i IN 1 .. l_range_numbers LOOP --{
572 l_cur_serial_numeric := l_from_serial_number + i - 1;
573 --l_cur_serial_number :=l_serial_prefix || l_cur_serial_numeric;
574 l_cur_serial_number := l_serial_prefix || LPAD(TO_CHAR(l_cur_serial_numeric),
575 l_serial_suffix_length,
576 '0'
577 );
578
579 IF (g_asn_debug = 'Y') THEN
580 asn_debug.put_line('l_serial_prefix ' || l_serial_prefix);
581 asn_debug.put_line('l_cur_serial_numeric ' || l_cur_serial_numeric);
582 asn_debug.put_line('l_serial_suffix_length ' || l_serial_suffix_length);
583 asn_debug.put_line('l_cur_serial_number ' || l_serial_suffix_length);
584 END IF;
585
586 insert_serial_supply(p_interface_transaction_id,
587 l_select_serials.lot_number,
588 l_cur_serial_number,
589 'RECEIVING',
590 p_supply_source_id,
591 x_return_status
592 );
593
594 IF (g_asn_debug = 'Y') THEN
595 asn_debug.put_line('After insert_serial_supply ');
596 END IF;
597 END LOOP; --}
598
599 IF (g_asn_debug = 'Y') THEN
600 asn_debug.put_line('l_delete_shipment_supply ' || l_delete_shipment_supply);
601 END IF;
602
603 IF (l_delete_shipment_supply = 'Y') THEN
604 SELECT shipment_header_id,
605 item_id
606 INTO l_shipment_header_id,
607 l_item_id
608 FROM rcv_shipment_lines
609 WHERE shipment_line_id = p_shipment_line_id;
610
611 /* Bug 3376348.
612 * It might happen that the original shipment lines are split
613 * into multiple shipment_lines (when lpn has 2 lots for eg).
614 * So instead of using shipment_line_id to delete use
615 * shipment_header_id.
616 */
617 DELETE FROM rcv_serials_supply rss
618 WHERE supply_type_code = 'SHIPMENT'
619 AND ( l_select_serials.lot_number IS NULL
620 OR NVL(lot_num, l_select_serials.lot_number) = l_select_serials.lot_number)
621 AND (serial_num BETWEEN(l_serial_prefix || LPAD(TO_CHAR(l_from_serial_number),
622 l_serial_suffix_length,
623 '0'
624 )) AND(l_serial_prefix || LPAD(TO_CHAR(l_to_serial_number),
625 l_serial_suffix_length,
626 '0'
627 )))
628 AND EXISTS(SELECT 1
629 FROM rcv_shipment_lines rsl
630 WHERE rsl.shipment_header_id = l_shipment_header_id
631 AND rsl.shipment_line_id = rss.shipment_line_id
632 AND rsl.item_id = l_item_id);
633 END IF;
634 END LOOP; --}
635
636 CLOSE select_serials;
637
638 IF (g_asn_debug = 'Y') THEN
639 asn_debug.put_line('Exit insert_rcv_serials_supply ');
640 END IF;
641 EXCEPTION
642 WHEN NO_DATA_FOUND THEN
643 IF (g_asn_debug = 'Y') THEN
644 asn_debug.put_line('no_data_found insert_rcv_serials_supply ');
645 END IF;
646
647 x_return_status := fnd_api.g_ret_sts_error;
648
649 INSERT INTO po_interface_errors
650 (interface_type,
651 interface_transaction_id,
652 error_message,
653 processing_date,
654 creation_date,
655 created_by,
656 last_update_date,
657 last_updated_by,
658 last_update_login,
659 request_id,
660 program_application_id,
661 program_id,
662 program_update_date
663 )
664 SELECT 'RECEIVING',
665 p_interface_transaction_id,
666 'RCV_INSERT_SERIAL_SUPPLY_FAIL',
667 SYSDATE,
668 rti.creation_date,
669 rti.created_by,
670 rti.last_update_date,
671 rti.last_updated_by,
672 rti.last_update_login,
673 rti.request_id,
674 rti.program_application_id,
675 rti.program_id,
676 rti.program_update_date
677 FROM rcv_transactions_interface rti
678 WHERE rti.interface_transaction_id = p_interface_transaction_id;
679 WHEN OTHERS THEN
680 IF (g_asn_debug = 'Y') THEN
681 asn_debug.put_line('others insert_rcv_serials_supply ');
682 END IF;
683
684 x_return_status := fnd_api.g_ret_sts_unexp_error;
685
686 INSERT INTO po_interface_errors
687 (interface_type,
688 interface_transaction_id,
689 error_message,
690 processing_date,
691 creation_date,
692 created_by,
693 last_update_date,
694 last_updated_by,
695 last_update_login,
696 request_id,
697 program_application_id,
698 program_id,
699 program_update_date
700 )
701 SELECT 'RECEIVING',
702 p_interface_transaction_id,
703 'RCV_INSERT_SERIAL_SUPPLY_ERROR',
704 SYSDATE,
705 rti.creation_date,
706 rti.created_by,
707 rti.last_update_date,
708 rti.last_updated_by,
709 rti.last_update_login,
710 rti.request_id,
711 rti.program_application_id,
712 rti.program_id,
713 rti.program_update_date
714 FROM rcv_transactions_interface rti
715 WHERE rti.interface_transaction_id = p_interface_transaction_id;
716 END insert_rcv_serials_supply;
717
718 PROCEDURE split_serial_number(
719 p_sequence IN VARCHAR2,
720 x_prefix OUT NOCOPY VARCHAR2,
721 x_number OUT NOCOPY NUMBER
722 ) IS
723 l_ascii_0 NUMBER;
724 l_ascii_code_minus_ascii_0 NUMBER;
725 l_sequence_length NUMBER;
726 l_loop_index NUMBER;
727 BEGIN
728 l_sequence_length := LENGTH(p_sequence);
729 l_loop_index := l_sequence_length;
730 l_ascii_0 := ASCII('0');
731
732 WHILE l_loop_index >= 1 LOOP
733 l_ascii_code_minus_ascii_0 := ASCII(SUBSTR(p_sequence,
734 l_loop_index,
735 1
736 )) - l_ascii_0;
737 EXIT WHEN( 0 > l_ascii_code_minus_ascii_0
738 OR l_ascii_code_minus_ascii_0 > 9);
739 l_loop_index := l_loop_index - 1;
740 END LOOP;
741
742 IF (l_loop_index = 0) THEN
743 x_prefix := '';
744 x_number := TO_NUMBER(p_sequence);
745 ELSIF(l_loop_index = l_sequence_length) THEN
746 x_prefix := p_sequence;
747 x_number := -1;
748 ELSE
749 x_prefix := SUBSTR(p_sequence,
750 1,
751 l_loop_index
752 );
753 x_number := TO_NUMBER(SUBSTR(p_sequence, l_loop_index + 1));
754 END IF;
755 END split_serial_number;
756
757 PROCEDURE update_rcv_lots_supply(
758 p_api_version IN NUMBER,
759 p_init_msg_list IN VARCHAR2,
760 x_return_status OUT NOCOPY VARCHAR2,
761 p_interface_transaction_id IN NUMBER,
762 p_transaction_type IN VARCHAR2,
763 p_shipment_line_id IN NUMBER,
764 p_source_type_code IN VARCHAR2,
765 p_parent_supply_id IN NUMBER,
766 p_correction_type IN VARCHAR2
767 ) IS
768 CURSOR lot_cursor(
769 p_interface_id NUMBER
770 ) IS
771
772 /* INVCONV , Remove sublot_num as part of new lot model. Punit Kumar */
773
774 /** OPM change Bug# 3061052 add sublot_num **/
775 SELECT mtlt.lot_number,
776 /* INVCONV */
777 /* mtlt.sublot_num, */
778 /* end , INVCONV*/
779 SUM(mtlt.primary_quantity),
780 SUM(mtlt.transaction_quantity),
781 SUM(mtlt.secondary_quantity),
782 rti.shipment_header_id,
783 rti.item_id,
784 rti.unit_of_measure,
785 rti.to_organization_id
786 FROM mtl_transaction_lots_temp mtlt,
787 rcv_transactions_interface rti
788 WHERE product_transaction_id = p_interface_id
789 AND product_code = 'RCV'
790 AND rti.interface_transaction_id = mtlt.product_transaction_id
791 GROUP BY mtlt.lot_number,
792 /* INVCONV */
793 /* mtlt.sublot_num, */
794 /* end , INVCONV*/
795 rti.shipment_header_id,
796 rti.shipment_line_id,
797 rti.item_id,
798 rti.unit_of_measure,
799 rti.to_organization_id;
800
801 /** Bug 5571740:
802 * Changed the declaration of l_lot_num from varchar2(30) to rcv_lots_supply.lot_num%TYPE
803 */
804 l_lot_num rcv_lots_supply.lot_num%TYPE;
805 l_factor NUMBER;
806 l_parent_trans_type rcv_transactions.transaction_type%TYPE;
807 l_count NUMBER;
808 l_count1 NUMBER;
809 l_count2 NUMBER;
810 l_transaction_id rcv_transactions.transaction_id%TYPE;
811 l_lot_count NUMBER;
812 l_update_shipment_supply VARCHAR2(1) := 'N';
813 l_organization_id NUMBER;
814 /* INVCONV*/
815 /* l_sublot_num VARCHAR2(32); */
816 /*end , INVCONV*/
817 l_lpn_id rcv_supply.lpn_id%TYPE;
818 l_validation_flag rcv_transactions_interface.validation_flag%TYPE;
819 l_shipment_header_id rcv_shipment_headers.shipment_header_id%TYPE;
820
821 CURSOR supply_quantity(
822 l_lot_num VARCHAR2,
823 /* INVCONV*/
824 /* l_sublot_num VARCHAR2, */
825 /*end , INVCONV*/
826 p_parent_supply_id NUMBER,
827 l_item_id NUMBER
828 ) IS
829 SELECT rls.quantity,
830 rls.primary_quantity
831 FROM rcv_lots_supply rls
832 WHERE rls.lot_num = l_lot_num
833 /* INVCONV*/
834 /*
835 AND ( (rls.sublot_num = l_sublot_num)
836 OR ( rls.sublot_num IS NULL
837 AND l_sublot_num IS NULL))
838 */
839 /* end , INVCONV */
840 AND rls.supply_type_code = 'RECEIVING'
841 AND rls.transaction_id = p_parent_supply_id;
842
843 /* Bug 4870857: Added condition for shipment_line_id in the Exists clause
844 ** to restrict the rows returned by the Cursor within a
845 ** given shipment header.
846 */
847 CURSOR shipment_supply_quantity(
848 l_lot_num VARCHAR2,
849 l_shipment_header_id NUMBER,
850 l_item_id NUMBER
851 ) IS
852 SELECT rls.quantity,
853 rls.primary_quantity,
854 rls.shipment_line_id
855 FROM rcv_lots_supply rls
856 WHERE rls.lot_num = l_lot_num
857 AND rls.supply_type_code = 'SHIPMENT'
858 AND EXISTS(SELECT 1
859 FROM rcv_shipment_lines rsl
860 WHERE rsl.shipment_header_id = l_shipment_header_id
861 AND rsl.item_id = l_item_id
862 AND rsl.shipment_line_id = rls.shipment_line_id);
863
864 l_qty_to_be_updated NUMBER;
865 l_primary_qty_to_be_updated NUMBER;
866 l_secondary_qty_to_be_updated NUMBER;
867 l_ship_id NUMBER;
868 l_ship_line_id NUMBER;
869 l_rls_qty NUMBER;
870 l_rls_primary_qty NUMBER;
871 l_item_id rcv_transactions_interface.item_id%TYPE;
872 l_parent_uom VARCHAR2(25);
873 l_parent_secondary_uom VARCHAR2(25);
874 l_txn_uom VARCHAR2(25);
875 l_primary_uom VARCHAR2(25);
876 l_to_org_id NUMBER;
877 BEGIN
878 IF (g_asn_debug = 'Y') THEN
879 asn_debug.put_line('Enter update_rcv_lots_supply ');
880 asn_debug.put_line('p_interface_transaction_id ' || p_interface_transaction_id);
881 asn_debug.put_line('p_transaction_type ' || p_transaction_type);
882 asn_debug.put_line('p_shipment_line_id ' || p_shipment_line_id);
883 asn_debug.put_line('p_source_type_code ' || p_source_type_code);
884 asn_debug.put_line('p_parent_supply_id ' || p_parent_supply_id);
885 asn_debug.put_line('p_correction_type ' || p_correction_type);
886 END IF;
887
888 x_return_status := fnd_api.g_ret_sts_success;
889
890 /* We can now come here if it is a lot-serial item and there is
891 * no row in mtl_transaction_lots_temp if user has not entered
892 * any lot/serial info for this transaction(Receive, Transfer etc).
893 * In this case we do not error nor insert. So return.
894 */
895 SELECT COUNT(*)
896 INTO l_lot_count
897 FROM mtl_transaction_lots_temp mtlt
898 WHERE mtlt.product_transaction_id = p_interface_transaction_id
899 AND mtlt.product_code = 'RCV';
900
901 IF (l_lot_count = 0) THEN
902 RETURN;
903 END IF;
904
905 IF (g_asn_debug = 'Y') THEN
906 asn_debug.put_line('l_lot_count ' || l_lot_count);
907 END IF;
908
909 /* We need to insert into rcv_lots_supply and
910 * rcv_serials_supply table only when we come through ROI
911 * or when we come through desktop and have lpn info.
912 * We insert lpn_id in rcv_supply. So return if there is
913 * a value and validation_flag is N.
914 */
915 SELECT NVL(validation_flag, 'N')
916 INTO l_validation_flag
917 FROM rcv_transactions_interface
918 WHERE interface_transaction_id = p_interface_transaction_id;
919
920 SELECT NVL(lpn_id, -999)
921 INTO l_lpn_id
922 FROM rcv_supply
923 WHERE supply_source_id = p_parent_supply_id;
924
925 IF ( l_validation_flag = 'N'
926 AND l_lpn_id = -999) THEN
927 RETURN;
928 END IF;
929
930 IF (g_asn_debug = 'Y') THEN
931 asn_debug.put_line('l_validation_flag ' || l_validation_flag);
932 asn_debug.put_line('l_lpn_id ' || l_lpn_id);
933 END IF;
934
935 /* When we update rcv_supply, we call this procedure and set
936 * the p_correction_type depending upon whether we need to add
937 * or subtract supply from rcv_lots_supply.
938 */
939 IF (p_correction_type = 'POSITIVE') THEN
940 l_factor := -1;
941 ELSE
942 l_factor := 1;
943 END IF;
944
945 /* We need to insert or update rcv_lot_supply only when there is
946 * already a row existing in rcv_lots_supply for a corresponding
947 * row in rcv_supply. If not dont do anything.
948 */
949 SELECT COUNT(*)
950 INTO l_count
951 FROM rcv_lots_supply
952 WHERE transaction_id = p_parent_supply_id
953 AND supply_type_code = 'RECEIVING';
954
955 IF (l_count = 0) THEN
956 RETURN;
957 END IF;
958
959 IF (g_asn_debug = 'Y') THEN
960 asn_debug.put_line('l_count ' || l_count);
961 END IF;
962
963 SELECT transaction_type,
964 organization_id,
965 unit_of_measure,
966 secondary_unit_of_measure
967 INTO l_parent_trans_type,
968 l_organization_id,
969 l_parent_uom,
970 l_parent_secondary_uom
971 FROM rcv_transactions
972 WHERE transaction_id = p_parent_supply_id;
973
974 /* INVCONV , Update for OPM transactions also. Punit Kumar */
975
976 -- roi enhacements for OPM.bug# 3061052
977 -- don't update for OPM transactions.
978
979 /* IF (gml_process_flags.check_process_orgn(p_organization_id => l_organization_id) = 0) THEN */
980 IF ( p_transaction_type = 'CORRECTION'
981 AND l_parent_trans_type = 'RECEIVE'
982 AND p_source_type_code IN('INVENTORY', 'REQ')) THEN --{
983 l_update_shipment_supply := 'Y';
984 END IF; --}
985 /* END IF; */
986
987 IF (g_asn_debug = 'Y') THEN
988 asn_debug.put_line('l_update_shipment_supply ' || l_update_shipment_supply);
989 asn_debug.put_line(' INVCONV , Update shipment supply for OPM transactions also.');
990 END IF;
991
992 OPEN lot_cursor(p_interface_transaction_id);
993
994 LOOP --{
995 FETCH lot_cursor INTO l_lot_num,
996 /* INVCONV */
997 /* l_sublot_num, */
998 /* end , INVCONV */
999 l_primary_qty_to_be_updated,
1000 l_qty_to_be_updated,
1001 l_secondary_qty_to_be_updated,
1002 l_shipment_header_id,
1003 l_item_id,
1004 l_txn_uom,
1005 l_to_org_id;
1006 EXIT WHEN lot_cursor%NOTFOUND;
1007
1008 IF (g_asn_debug = 'Y') THEN
1009 asn_debug.put_line('Opened lot_cursor ');
1010 asn_debug.put_line('l_lot_num ' || l_lot_num);
1011 asn_debug.put_line('INVCONV, Subllot_num has been removed in update_lot_supply1 ');
1012 /*INVCONV*/
1013 /* asn_debug.put_line('l_sublot_num ' || l_sublot_num); */
1014 /*end , INVCONV*/
1015 asn_debug.put_line('l_primary_qty_to_be_updated ' || l_primary_qty_to_be_updated);
1016 asn_debug.put_line('l_secondary_qty_to_be_updated ' || l_secondary_qty_to_be_updated);
1017 asn_debug.put_line('l_qty_to_be_updated ' || l_qty_to_be_updated);
1018 asn_debug.put_line('l_shipment_header_id ' || l_shipment_header_id);
1019 asn_debug.put_line('l_item_id ' || l_item_id);
1020 asn_debug.put_line('l_txn_uom, ' || l_txn_uom);
1021 asn_debug.put_line('l_to_org_id ' || l_to_org_id);
1022 END IF;
1023
1024 /* If there is already a row existing for this lot_num, update
1025 * the quantity. Else insert a new one since this might be a
1026 * new lot_num for this transaction. At this point, inventory
1027 * would have validated these numbers.
1028 */
1029
1030 /* INVCONV , Remove sublot_num. Punit Kumar */
1031
1032 /** OPM change Bug# 3061052 added sublot_num check**/
1033 SELECT COUNT(*)
1034 INTO l_count1
1035 FROM rcv_lots_supply
1036 WHERE transaction_id = p_parent_supply_id
1037 AND lot_num = l_lot_num
1038 /* INVCONV */
1039 /*
1040 AND ( (sublot_num = l_sublot_num)
1041 OR ( sublot_num IS NULL
1042 AND l_sublot_num IS NULL))
1043 */
1044 /*end , INVCONV*/
1045 AND supply_type_code = 'RECEIVING';
1046
1047 IF (g_asn_debug = 'Y') THEN
1048 asn_debug.put_line('l_count1 ' || l_count1);
1049 asn_debug.put_line('INVCONV, Subllot_num has been removed in update_lot_supply2 ');
1050 END IF;
1051
1052 IF (l_count1 = 0) THEN --{
1053 IF (g_asn_debug = 'Y') THEN
1054 asn_debug.put_line('Before insert_lot_supply ' || l_count1);
1055 END IF;
1056
1057 insert_lot_supply(p_interface_transaction_id,
1058 'RECEIVING',
1059 p_parent_supply_id,
1060 x_return_status
1061 );
1062
1063 IF (g_asn_debug = 'Y') THEN
1064 asn_debug.put_line('After insert_lot_supply ' || l_count1);
1065 END IF;
1066 ELSE --}{
1067 /** OPM Change Bug# 3061052 add sublot_num check **/
1068 IF (g_asn_debug = 'Y') THEN
1069 asn_debug.put_line('Else update rcv_lots_supply');
1070 END IF;
1071
1072 SELECT MAX(primary_unit_of_measure)
1073 INTO l_primary_uom
1074 FROM mtl_system_items
1075 WHERE mtl_system_items.inventory_item_id = l_item_id
1076 AND mtl_system_items.organization_id = l_to_org_id;
1077
1078 IF (g_asn_debug = 'Y') THEN
1079 asn_debug.put_line('l_primary_uom ' || l_primary_uom);
1080 END IF;
1081
1082 /* l_qty_to_be_updated shd be in
1083 * terms of the parent's uom. For
1084 * shipment supply qty, it must be
1085 * in terms of uom in rsl.
1086 */
1087 IF (l_txn_uom <> l_parent_uom) THEN
1088 l_qty_to_be_updated := rcv_transactions_interface_sv.convert_into_correct_qty(l_primary_qty_to_be_updated,
1089 l_primary_uom,
1090 l_item_id,
1091 l_parent_uom
1092 );
1093 END IF;
1094
1095 IF (g_asn_debug = 'Y') THEN
1096 asn_debug.put_line('l_qty_to_be_updated ' || l_qty_to_be_updated);
1097 asn_debug.put_line('l_factor ' || l_factor);
1098 asn_debug.put_line('l_primary_qty_to_be_updated ' || l_primary_qty_to_be_updated);
1099 asn_debug.put_line('l_secondary_qty_to_be_updated ' || l_secondary_qty_to_be_updated);
1100 asn_debug.put_line('l_lot_num ' || l_lot_num);
1101 asn_debug.put_line('p_parent_supply_id ' || p_parent_supply_id);
1102 /* INVCONV*/
1103 /* asn_debug.put_line('l_sublot_num ' || l_sublot_num); */
1104 /*end , INVCONV*/
1105 asn_debug.put_line('INVCONV, Subllot_num has been removed in update_lot_supply3 ');
1106 END IF;
1107
1108 /* INVCONV , Remove sublot_num. Punit Kumar */
1109
1110 UPDATE rcv_lots_supply rls
1111 SET quantity = quantity -(l_qty_to_be_updated * l_factor),
1112 primary_quantity = primary_quantity -(l_primary_qty_to_be_updated * l_factor),
1113 secondary_quantity = secondary_quantity -(l_secondary_qty_to_be_updated * l_factor)
1114 WHERE rls.lot_num = l_lot_num
1115 AND rls.transaction_id = p_parent_supply_id
1116 AND rls.supply_type_code = 'RECEIVING' ;
1117 /* INVCONV */
1118 /*
1119 AND ( (rls.sublot_num = l_sublot_num)
1120 OR ( rls.sublot_num IS NULL
1121 AND l_sublot_num IS NULL));
1122 */
1123 /* end , INVCONV */
1124
1125 END IF; --}
1126
1127 IF (g_asn_debug = 'Y') THEN
1128 asn_debug.put_line('l_update_shipment_supply ' || l_update_shipment_supply);
1129 asn_debug.put_line('INVCONV, Subllot_num has been removed in update_lot_supply4 ');
1130 END IF;
1131
1132 IF (l_update_shipment_supply = 'Y') THEN --{
1133 SELECT COUNT(*)
1134 INTO l_count2
1135 FROM rcv_lots_supply
1136 WHERE shipment_line_id = p_shipment_line_id
1137 AND supply_type_code = 'SHIPMENT'
1138 AND lot_num = l_lot_num;
1139
1140 IF (g_asn_debug = 'Y') THEN
1141 asn_debug.put_line('l_count2 ' || l_count2);
1142 END IF;
1143
1144 /* If correction type is positive, then there would
1145 * definitely be a row for shipment supply else we will
1146 * not be able to correct. For negative correction, there
1147 * may or may not be a row. Hence get the count and insert
1148 * a new shipment supply row if there is no row or update
1149 * if there is already a shipment supply row. For ASNs the
1150 * shipment lot numbers are just suggestions and users can
1151 * override those values. Hence update if the lot number
1152 * already exists. We will delete all the shipment supply
1153 * from rcv_lot and serial tables when we fully receive
1154 * the asn in the processor.
1155 */
1156 IF ( ( p_correction_type = 'POSITIVE'
1157 AND l_count2 >= 1)
1158 OR ( p_correction_type = 'NEGATIVE'
1159 AND l_count2 >= 1)) THEN --{
1160 /* Bug 3376348.
1161 * It might happen that the original shipment lines are split
1162 * into multiple shipment_lines (when lpn has 2 lots for eg).
1163 * So instead of using shipment_line_id to delete use
1164 * shipment_header_id.
1165 */
1166 SELECT MAX(primary_unit_of_measure)
1167 INTO l_primary_uom
1168 FROM mtl_system_items
1169 WHERE mtl_system_items.inventory_item_id = l_item_id
1170 AND mtl_system_items.organization_id = l_to_org_id;
1171
1172 /* l_qty_to_be_updated shd be in
1173 * terms of the parent's uom. For
1174 * shipment supply qty, it must be
1175 * in terms of uom in rsl.
1176 */
1177 OPEN shipment_supply_quantity(l_lot_num,
1178 l_shipment_header_id,
1179 l_item_id
1180 );
1181
1182 IF (g_asn_debug = 'Y') THEN
1183 asn_debug.put_line('Opened shipment_supply_quantity ');
1184 END IF;
1185
1186 LOOP --{
1187 FETCH shipment_supply_quantity INTO l_rls_qty,
1188 l_rls_primary_qty,
1189 l_ship_line_id;
1190 EXIT WHEN shipment_supply_quantity%NOTFOUND
1191 OR l_primary_qty_to_be_updated = 0;
1192
1193 IF (g_asn_debug = 'Y') THEN
1194 asn_debug.put_line('l_rls_qty ' || l_rls_qty);
1195 asn_debug.put_line('l_rls_primary_qty ' || l_rls_primary_qty);
1196 asn_debug.put_line('l_ship_line_id ' || l_ship_line_id);
1197 END IF;
1198
1199 SELECT unit_of_measure
1200 INTO l_parent_uom
1201 FROM rcv_shipment_lines
1202 WHERE shipment_line_id = l_ship_line_id;
1203
1204 IF (g_asn_debug = 'Y') THEN
1205 asn_debug.put_line('l_parent_uom ' || l_parent_uom);
1206 END IF;
1207
1208 IF (l_txn_uom <> l_parent_uom) THEN
1209 l_qty_to_be_updated := rcv_transactions_interface_sv.convert_into_correct_qty(l_primary_qty_to_be_updated,
1210 l_primary_uom,
1211 l_item_id,
1212 l_parent_uom
1213 );
1214 END IF;
1215
1216 IF (l_rls_primary_qty >= l_primary_qty_to_be_updated) THEN --{
1217 IF (g_asn_debug = 'Y') THEN
1218 asn_debug.put_line('rls_primary_qty is greater ');
1219 asn_debug.put_line('l_qty_to_be_updated ' || l_qty_to_be_updated);
1220 asn_debug.put_line('l_primary_qty_to_be_updated ' || l_primary_qty_to_be_updated);
1221 asn_debug.put_line('l_lot_num ' || l_lot_num);
1222 asn_debug.put_line('l_ship_line_id ' || l_ship_line_id);
1223 END IF;
1224
1225 UPDATE rcv_lots_supply rls
1226 SET quantity = quantity -(l_qty_to_be_updated * l_factor),
1227 primary_quantity = primary_quantity -(l_primary_qty_to_be_updated * l_factor)
1228 WHERE rls.lot_num = l_lot_num
1229 AND shipment_line_id = l_ship_line_id
1230 AND rls.supply_type_code = 'SHIPMENT';
1231
1232 l_qty_to_be_updated := 0;
1233 l_primary_qty_to_be_updated := 0;
1234 ELSE --}{
1235 IF (g_asn_debug = 'Y') THEN
1236 asn_debug.put_line('rls_primary_qty is lesser ');
1237 asn_debug.put_line('l_qty_to_be_updated ' || l_qty_to_be_updated);
1238 asn_debug.put_line('l_primary_qty_to_be_updated ' || l_primary_qty_to_be_updated);
1239 asn_debug.put_line('l_lot_num ' || l_lot_num);
1240 asn_debug.put_line('l_ship_line_id ' || l_ship_line_id);
1241 END IF;
1242
1243 UPDATE rcv_lots_supply rls
1244 SET quantity = quantity -(quantity * l_factor),
1245 primary_quantity = primary_quantity -(primary_quantity * l_factor)
1246 WHERE rls.lot_num = l_lot_num
1247 AND shipment_line_id = l_ship_line_id
1248 AND rls.supply_type_code = 'SHIPMENT';
1249
1250 l_qty_to_be_updated := l_qty_to_be_updated - l_rls_qty;
1251 l_primary_qty_to_be_updated := l_primary_qty_to_be_updated - l_rls_primary_qty;
1252 END IF; --}
1253 END LOOP; --}
1254
1255 CLOSE shipment_supply_quantity;
1256
1257 IF (g_asn_debug = 'Y') THEN
1258 asn_debug.put_line('Close shipment_supply_quantity ');
1259 END IF;
1260 /*
1261 select shipment_header_id,item_id
1262 into l_shipment_header_id,l_item_id
1263 from rcv_shipment_lines
1264 where shipment_line_id = p_shipment_line_id;
1265
1266 update rcv_lots_supply rls
1267 set rls.quantity =
1268 (select rls.quantity +
1269 (sum(mtlt.transaction_quantity) * l_factor)
1270 from mtl_transaction_lots_temp mtlt
1271 where mtlt.product_transaction_ID =
1272 p_interface_transaction_id
1273 and mtlt.product_code = 'RCV'
1274 and rls.lot_num = mtlt.lot_number),
1275 rls.primary_quantity =
1276 (select rls.primary_quantity -
1277 (sum(mtlt.primary_quantity) * l_factor)
1278 from mtl_transaction_lots_temp mtlt
1279 where mtlt.product_transaction_ID =
1280 p_interface_transaction_id
1281 and mtlt.product_code = 'RCV'
1282 and rls.lot_num = mtlt.lot_number)
1283 where supply_type_code = 'SHIPMENT'
1284 AND exists (select 1 from rcv_shipment_lines rsl
1285 where rsl.shipment_header_id = l_shipment_header_id
1286 and rsl.shipment_line_id = rls.shipment_line_id
1287 and rsl.item_id = l_item_id)
1288 and rls.lot_num = l_lot_num;
1289 */
1290 END IF; --}
1291 END IF; --}
1292 END LOOP; --}
1293
1294 CLOSE lot_cursor;
1295
1296 IF (g_asn_debug = 'Y') THEN
1297 asn_debug.put_line('Close lot_cursor ');
1298 END IF;
1299
1300 IF (g_asn_debug = 'Y') THEN
1301 asn_debug.put_line('Exit update_rcv_lots_supply ');
1302 END IF;
1303 EXCEPTION
1304 WHEN NO_DATA_FOUND THEN
1305 IF (g_asn_debug = 'Y') THEN
1306 asn_debug.put_line('no_data_found update_rcv_lots_supply ');
1307 END IF;
1308
1309 x_return_status := fnd_api.g_ret_sts_error;
1310
1311 INSERT INTO po_interface_errors
1312 (interface_type,
1313 interface_transaction_id,
1314 error_message,
1315 processing_date,
1316 creation_date,
1317 created_by,
1318 last_update_date,
1319 last_updated_by,
1320 last_update_login,
1321 request_id,
1322 program_application_id,
1323 program_id,
1324 program_update_date
1325 )
1326 SELECT 'RECEIVING',
1327 p_interface_transaction_id,
1328 'RCV_UPDATE_LOT_SUPPLY_FAIL',
1329 SYSDATE,
1330 rti.creation_date,
1331 rti.created_by,
1332 rti.last_update_date,
1333 rti.last_updated_by,
1334 rti.last_update_login,
1335 rti.request_id,
1336 rti.program_application_id,
1337 rti.program_id,
1338 rti.program_update_date
1339 FROM rcv_transactions_interface rti
1340 WHERE rti.interface_transaction_id = p_interface_transaction_id;
1341 WHEN OTHERS THEN
1342 IF (g_asn_debug = 'Y') THEN
1343 asn_debug.put_line('others update_rcv_lots_supply ');
1344 END IF;
1345
1346 x_return_status := fnd_api.g_ret_sts_unexp_error;
1347
1348 INSERT INTO po_interface_errors
1349 (interface_type,
1350 interface_transaction_id,
1351 error_message,
1352 processing_date,
1353 creation_date,
1354 created_by,
1355 last_update_date,
1356 last_updated_by,
1357 last_update_login,
1358 request_id,
1359 program_application_id,
1360 program_id,
1361 program_update_date
1362 )
1363 SELECT 'RECEIVING',
1364 p_interface_transaction_id,
1365 'RCV_UPDATE_LOT_SUPPLY_ERROR',
1366 SYSDATE,
1367 rti.creation_date,
1368 rti.created_by,
1369 rti.last_update_date,
1370 rti.last_updated_by,
1371 rti.last_update_login,
1372 rti.request_id,
1373 rti.program_application_id,
1374 rti.program_id,
1375 rti.program_update_date
1376 FROM rcv_transactions_interface rti
1377 WHERE rti.interface_transaction_id = p_interface_transaction_id;
1378 END update_rcv_lots_supply;
1379
1380 PROCEDURE update_rcv_serials_supply(
1381 p_api_version IN NUMBER,
1382 p_init_msg_list IN VARCHAR2,
1383 x_return_status OUT NOCOPY VARCHAR2,
1384 p_interface_transaction_id IN NUMBER,
1385 p_transaction_type IN VARCHAR2,
1386 p_shipment_line_id IN NUMBER,
1387 p_source_type_code IN VARCHAR2,
1388 p_parent_supply_id IN NUMBER,
1389 p_correction_type IN VARCHAR2
1390 ) IS
1391 CURSOR select_serials(
1392 p_interface_transaction_id NUMBER
1393 ) IS
1394 SELECT msnt.fm_serial_number,
1395 msnt.to_serial_number,
1396 mtlt.lot_number
1397 FROM mtl_serial_numbers_temp msnt,
1398 mtl_transaction_lots_temp mtlt
1399 WHERE msnt.product_transaction_id = p_interface_transaction_id
1400 AND msnt.product_code = 'RCV'
1401 AND msnt.transaction_temp_id = mtlt.serial_transaction_temp_id(+);
1402
1403 l_select_serials select_serials%ROWTYPE;
1404 l_insert_serial VARCHAR2(1) := 'N';
1405 l_delete_serial VARCHAR2(1) := 'N';
1406 l_count NUMBER;
1407 l_serial_prefix VARCHAR2(31);
1408 l_from_serial_number NUMBER;
1409 l_to_serial_number NUMBER;
1410 l_serial_num_length NUMBER;
1411 l_prefix_length NUMBER;
1412 l_serial_suffix_length NUMBER;
1413 l_cur_serial_numeric NUMBER;
1414 l_cur_serial_number VARCHAR2(30);
1415 l_range_numbers NUMBER;
1416 l_serial_count NUMBER;
1417 l_update_shipment_supply VARCHAR2(1) := 'N';
1418 l_parent_trans_type rcv_transactions.transaction_type%TYPE;
1419 l_lpn_id rcv_supply.lpn_id%TYPE;
1420 l_validation_flag rcv_transactions_interface.validation_flag%TYPE;
1421 l_shipment_header_id rcv_shipment_headers.shipment_header_id%TYPE;
1422 l_item_id rcv_transactions_interface.item_id%TYPE;
1423 BEGIN
1424 IF (g_asn_debug = 'Y') THEN
1425 asn_debug.put_line('Enter update_rcv_serials_supply ');
1426 asn_debug.put_line('p_interface_transaction_id ' || p_interface_transaction_id);
1427 asn_debug.put_line('p_transaction_type ' || p_transaction_type);
1428 asn_debug.put_line('p_shipment_line_id ' || p_shipment_line_id);
1429 asn_debug.put_line('p_source_type_code ' || p_source_type_code);
1430 asn_debug.put_line('p_parent_supply_id ' || p_parent_supply_id);
1431 asn_debug.put_line('p_correction_type ' || p_correction_type);
1432 END IF;
1433
1434 x_return_status := fnd_api.g_ret_sts_success;
1435
1436 /* We can now come here if it is a lot-serial item and there is
1437 * no row in mtl_transaction_lots_temp if user has not entered
1438 * any lot/serial info for this transaction(Receive, Transfer etc).
1439 * In this case we do not error nor insert. So return.
1440 */
1441 SELECT COUNT(*)
1442 INTO l_serial_count
1443 FROM mtl_serial_numbers_temp msnt
1444 WHERE msnt.product_transaction_id = p_interface_transaction_id
1445 AND msnt.product_code = 'RCV';
1446
1447 IF (l_serial_count = 0) THEN
1448 RETURN;
1449 END IF;
1450
1451 IF (g_asn_debug = 'Y') THEN
1452 asn_debug.put_line('l_Serial_count ' || l_serial_count);
1453 END IF;
1454
1455 /* We need to insert into rcv_lots_supply and
1456 * rcv_serials_supply table only when we come through ROI
1457 * or when we come through desktop and have lpn info.
1458 * We insert lpn_id in rcv_supply. So return if there is
1459 * a value and validation_flag is N.
1460 */
1461 SELECT NVL(validation_flag, 'N')
1462 INTO l_validation_flag
1463 FROM rcv_transactions_interface
1464 WHERE interface_transaction_id = p_interface_transaction_id;
1465
1466 SELECT NVL(lpn_id, -999)
1467 INTO l_lpn_id
1468 FROM rcv_supply
1469 WHERE supply_source_id = p_parent_supply_id;
1470
1471 IF ( l_validation_flag = 'N'
1472 AND l_lpn_id = -999) THEN
1473 RETURN;
1474 END IF;
1475
1476 IF (g_asn_debug = 'Y') THEN
1477 asn_debug.put_line('l_validation_flag ' || l_validation_flag);
1478 asn_debug.put_line('l_lpn_id ' || l_lpn_id);
1479 END IF;
1480
1481 OPEN select_serials(p_interface_transaction_id);
1482
1483 /* Correction_type is positive when we need to insert new rows and
1484 * and will be negative when we need to delete the existing rows.
1485 * We need to insert new rows only when we already have rows
1486 * in rcv_serials_supply for the corresponding row in rcv_supply.
1487 */
1488 IF (p_correction_type = 'POSITIVE') THEN --{
1489 SELECT COUNT(*)
1490 INTO l_count
1491 FROM rcv_serials_supply
1492 WHERE transaction_id = p_parent_supply_id
1493 AND supply_type_code = 'RECEIVING';
1494
1495 IF (l_count > 0) THEN
1496 l_insert_serial := 'Y';
1497 END IF;
1498 ELSIF(p_correction_type = 'NEGATIVE') THEN --}{
1499 l_delete_serial := 'Y';
1500 END IF; --}
1501
1502 IF (g_asn_debug = 'Y') THEN
1503 asn_debug.put_line('l_insert_serial ' || l_insert_serial);
1504 asn_debug.put_line('l_delete_serial ' || l_delete_serial);
1505 END IF;
1506
1507 SELECT transaction_type
1508 INTO l_parent_trans_type
1509 FROM rcv_transactions
1510 WHERE transaction_id = p_parent_supply_id;
1511
1512 IF ( p_transaction_type = 'CORRECTION'
1513 AND l_parent_trans_type = 'RECEIVE'
1514 AND p_source_type_code IN('INVENTORY', 'REQ')) THEN --{
1515 l_update_shipment_supply := 'Y';
1516 END IF; --}
1517
1518 IF (g_asn_debug = 'Y') THEN
1519 asn_debug.put_line('l_update_shipment_supply ' || l_update_shipment_supply);
1520 END IF;
1521
1522 LOOP --{
1523 FETCH select_serials INTO l_select_serials;
1524 EXIT WHEN select_serials%NOTFOUND;
1525 split_serial_number(l_select_serials.fm_serial_number,
1526 l_serial_prefix,
1527 l_from_serial_number
1528 );
1529 split_serial_number(l_select_serials.to_serial_number,
1530 l_serial_prefix,
1531 l_to_serial_number
1532 );
1533 l_range_numbers := l_to_serial_number - l_from_serial_number + 1;
1534 l_serial_num_length := LENGTH(l_select_serials.fm_serial_number);
1535
1536 /* Start Bug#3359105: Modified the following code to consider
1537 * the case when the Serial number is numeric instead of being
1538 * alphanumeric. In this case l_serial_prefix is NULL and
1539 * so needs proper handling.
1540 */
1541 IF l_serial_prefix IS NOT NULL THEN
1542 l_prefix_length := LENGTH(l_serial_prefix);
1543 l_serial_suffix_length := l_serial_num_length - l_prefix_length;
1544 ELSE
1545 l_prefix_length := 0;
1546 l_serial_suffix_length := l_serial_num_length;
1547 END IF;
1548
1549 /* End Bug#3359105 */
1550 IF (l_delete_serial = 'Y') THEN --{
1551 IF (g_asn_debug = 'Y') THEN
1552 asn_debug.put_line('l_serial_prefix ' || l_serial_prefix);
1553 asn_debug.put_line('l_from_serial_number ' || l_from_serial_number);
1554 asn_debug.put_line('l_to_serial_number ' || l_to_serial_number);
1555 END IF;
1556
1557 DELETE FROM rcv_serials_supply
1558 WHERE transaction_id = p_parent_supply_id
1559 AND supply_type_code = 'RECEIVING'
1560 AND ( l_select_serials.lot_number IS NULL
1561 OR NVL(lot_num, l_select_serials.lot_number) = l_select_serials.lot_number)
1562 AND (serial_num BETWEEN(l_serial_prefix || LPAD(TO_CHAR(l_from_serial_number),
1563 l_serial_suffix_length,
1564 '0'
1565 )) AND(l_serial_prefix || LPAD(TO_CHAR(l_to_serial_number),
1566 l_serial_suffix_length,
1567 '0'
1568 )));
1569 END IF; --}
1570
1571 FOR i IN 1 .. l_range_numbers LOOP --{
1572 l_cur_serial_numeric := l_from_serial_number + i - 1;
1573 -- l_cur_serial_number := l_serial_prefix || l_cur_serial_numeric;
1574 l_cur_serial_number := l_serial_prefix || LPAD(TO_CHAR(l_cur_serial_numeric),
1575 l_serial_suffix_length,
1576 '0'
1577 );
1578
1579 IF (l_insert_serial = 'Y') THEN --{
1580 IF (g_asn_debug = 'Y') THEN
1581 asn_debug.put_line('Before insert_serial_supply ');
1582 END IF;
1583
1584 insert_serial_supply(p_interface_transaction_id,
1585 l_select_serials.lot_number,
1586 l_cur_serial_number,
1587 'RECEIVING',
1588 p_parent_supply_id,
1589 x_return_status
1590 );
1591
1592 IF (g_asn_debug = 'Y') THEN
1593 asn_debug.put_line('After insert_serial_supply ');
1594 END IF;
1595 END IF; --}
1596
1597 IF ( (l_update_shipment_supply = 'Y')
1598 AND (l_delete_serial = 'Y')) THEN --{
1599 IF (g_asn_debug = 'Y') THEN
1600 asn_debug.put_line('Before insert_serial_supply when update and delete serial is Y');
1601 END IF;
1602
1603 insert_serial_supply(p_interface_transaction_id,
1604 l_select_serials.lot_number,
1605 l_cur_serial_number,
1606 'SHIPMENT',
1607 p_parent_supply_id,
1608 x_return_status
1609 );
1610
1611 IF (g_asn_debug = 'Y') THEN
1612 asn_debug.put_line('After insert_serial_supply when update and delete serial is Y');
1613 END IF;
1614 END IF; --}
1615 END LOOP; --}
1616
1617 IF ( (l_update_shipment_supply = 'Y')
1618 AND (l_insert_serial = 'Y')) THEN --{
1619 IF (g_asn_debug = 'Y') THEN
1620 asn_debug.put_line('Before delete when update and insert serial is Y');
1621 END IF;
1622
1623 SELECT shipment_header_id,
1624 item_id
1625 INTO l_shipment_header_id,
1626 l_item_id
1627 FROM rcv_shipment_lines
1628 WHERE shipment_line_id = p_shipment_line_id;
1629
1630 /* Bug 3376348.
1631 * It might happen that the original shipment lines are split
1632 * into multiple shipment_lines (when lpn has 2 lots for eg).
1633 * So instead of using shipment_line_id to delete use
1634 * shipment_header_id.
1635 */
1636 DELETE FROM rcv_serials_supply rss
1637 WHERE supply_type_code = 'SHIPMENT'
1638 AND ( l_select_serials.lot_number IS NULL
1639 OR NVL(lot_num, l_select_serials.lot_number) = l_select_serials.lot_number)
1640 AND (serial_num BETWEEN(l_serial_prefix || LPAD(TO_CHAR(l_from_serial_number),
1641 l_serial_suffix_length,
1642 '0'
1643 )) AND(l_serial_prefix || LPAD(TO_CHAR(l_to_serial_number),
1644 l_serial_suffix_length,
1645 '0'
1646 )))
1647 AND EXISTS(SELECT 1
1648 FROM rcv_shipment_lines rsl
1649 WHERE rsl.shipment_header_id = l_shipment_header_id
1650 AND rsl.shipment_line_id = rss.shipment_line_id
1651 AND rsl.item_id = l_item_id);
1652
1653 IF (g_asn_debug = 'Y') THEN
1654 asn_debug.put_line('After delete when update and insert serial is Y');
1655 END IF;
1656 END IF; --}
1657 END LOOP; --}
1658
1659 CLOSE select_serials;
1660 EXCEPTION
1661 WHEN NO_DATA_FOUND THEN
1662 IF (g_asn_debug = 'Y') THEN
1663 asn_debug.put_line('no_data_found update_rcv_serials_supply');
1664 END IF;
1665
1666 x_return_status := fnd_api.g_ret_sts_error;
1667
1668 INSERT INTO po_interface_errors
1669 (interface_type,
1670 interface_transaction_id,
1671 error_message,
1672 processing_date,
1673 creation_date,
1674 created_by,
1675 last_update_date,
1676 last_updated_by,
1677 last_update_login,
1678 request_id,
1679 program_application_id,
1680 program_id,
1681 program_update_date
1682 )
1683 SELECT 'RECEIVING',
1684 p_interface_transaction_id,
1685 'RCV_UPDATE_SERIAL_SUPPLY_FAIL',
1686 SYSDATE,
1687 rti.creation_date,
1688 rti.created_by,
1689 rti.last_update_date,
1690 rti.last_updated_by,
1691 rti.last_update_login,
1692 rti.request_id,
1693 rti.program_application_id,
1694 rti.program_id,
1695 rti.program_update_date
1696 FROM rcv_transactions_interface rti
1697 WHERE rti.interface_transaction_id = p_interface_transaction_id;
1698 WHEN OTHERS THEN
1699 IF (g_asn_debug = 'Y') THEN
1700 asn_debug.put_line('others update_rcv_serials_supply');
1701 END IF;
1702
1703 x_return_status := fnd_api.g_ret_sts_unexp_error;
1704
1705 INSERT INTO po_interface_errors
1706 (interface_type,
1707 interface_transaction_id,
1708 error_message,
1709 processing_date,
1710 creation_date,
1711 created_by,
1712 last_update_date,
1713 last_updated_by,
1714 last_update_login,
1715 request_id,
1716 program_application_id,
1717 program_id,
1718 program_update_date
1719 )
1720 SELECT 'RECEIVING',
1721 p_interface_transaction_id,
1722 'RCV_UPDATE_SERIAL_SUPPLY_ERROR',
1723 SYSDATE,
1724 rti.creation_date,
1725 rti.created_by,
1726 rti.last_update_date,
1727 rti.last_updated_by,
1728 rti.last_update_login,
1729 rti.request_id,
1730 rti.program_application_id,
1731 rti.program_id,
1732 rti.program_update_date
1733 FROM rcv_transactions_interface rti
1734 WHERE rti.interface_transaction_id = p_interface_transaction_id;
1735 END update_rcv_serials_supply;
1736
1737 PROCEDURE insert_lot_supply(
1738 p_interface_transaction_id IN NUMBER,
1739 p_supply_type_code IN VARCHAR2,
1740 p_supply_source_id IN NUMBER,
1741 x_return_status OUT NOCOPY VARCHAR2
1742 ) IS
1743 CURSOR c IS
1744 SELECT rls.ROWID
1745 FROM rcv_lots_supply rls
1746 WHERE rls.transaction_id = p_supply_source_id;
1747
1748 l_rowid VARCHAR2(255);
1749 l_lot_count NUMBER;
1750 l_lpn_id rcv_supply.lpn_id%TYPE;
1751 l_validation_flag rcv_transactions_interface.validation_flag%TYPE;
1752 BEGIN
1753 IF (g_asn_debug = 'Y') THEN
1754 asn_debug.put_line('Enter insert_lots_supply');
1755 asn_debug.put_line('p_interface_transaction_id ' || p_interface_transaction_id);
1756 asn_debug.put_line('p_supply_type_code ' || p_supply_type_code);
1757 asn_debug.put_line('p_supply_source_id ' || p_supply_source_id);
1758 END IF;
1759
1760 x_return_status := fnd_api.g_ret_sts_success;
1761
1762 /* We can now come here if it is a lot-serial item and there is
1763 * no row in mtl_transaction_lots_temp if user has not entered
1764 * any lot/serial info for this transaction(Receive, Transfer etc).
1765 * In this case we do not error nor insert. So return.
1766 */
1767 SELECT COUNT(*)
1768 INTO l_lot_count
1769 FROM mtl_transaction_lots_temp mtlt
1770 WHERE mtlt.product_transaction_id = p_interface_transaction_id
1771 AND mtlt.product_code = 'RCV';
1772
1773 IF (l_lot_count = 0) THEN
1774 RETURN;
1775 END IF;
1776
1777 IF (g_asn_debug = 'Y') THEN
1778 asn_debug.put_line('l_lot_count ' || l_lot_count);
1779 END IF;
1780
1781 /* We need to insert into rcv_lots_supply and
1782 * rcv_serials_supply table only when we come through ROI
1783 * or when we come through desktop and have lpn info.
1784 * We insert lpn_id in rcv_supply. So return if there is
1785 * a value and validation_flag is N.
1786 */
1787 SELECT NVL(validation_flag, 'N')
1788 INTO l_validation_flag
1789 FROM rcv_transactions_interface
1790 WHERE interface_transaction_id = p_interface_transaction_id;
1791
1792 SELECT NVL(lpn_id, -999)
1793 INTO l_lpn_id
1794 FROM rcv_supply
1795 WHERE supply_source_id = p_supply_source_id;
1796
1797 IF ( l_validation_flag = 'N'
1798 AND l_lpn_id = -999) THEN
1799 RETURN;
1800 END IF;
1801
1802 IF (g_asn_debug = 'Y') THEN
1803 asn_debug.put_line('l_validation_flag ' || l_validation_flag);
1804 asn_debug.put_line('l_lpn_id ' || l_lpn_id);
1805 END IF;
1806 /* INVCONV, Remove sublot_num . Punit Kumar*/
1807
1808 INSERT INTO rcv_lots_supply
1809 (supply_type_code,
1810 shipment_line_id,
1811 transaction_id,
1812 last_updated_by,
1813 last_update_date,
1814 last_update_login,
1815 created_by,
1816 creation_date,
1817 request_id,
1818 program_application_id,
1819 program_id,
1820 program_update_date,
1821 lot_num,
1822 quantity,
1823 primary_quantity,
1824 expiration_date,
1825 /** OPM change Bug# 3061052**/
1826 secondary_quantity,
1827 /*INVCONV*/
1828 /* sublot_num, */
1829 /*end , INVCONV*/
1830 reason_code
1831 )
1832 SELECT p_supply_type_code,
1833 rs.shipment_line_id,
1834 DECODE(p_supply_type_code,
1835 'RECEIVING', rs.supply_source_id,
1836 NULL
1837 ),
1838 rs.last_updated_by,
1839 rs.last_update_date,
1840 rs.last_update_login,
1841 rs.created_by,
1842 rs.creation_date,
1843 rs.request_id,
1844 rs.program_application_id,
1845 rs.program_id,
1846 SYSDATE,
1847 mtltview.lot_number,
1848 mtltview.qty,
1849 mtltview.primary_qty,
1850 mtltview.lot_expiration_date,
1851 mtltview.secondary_qty,
1852 /*INVCONV*/
1853 /* mtltview.sublot_num, */
1854 /*end , INVCONV*/
1855 mtltview.reason_code
1856 FROM rcv_supply rs,
1857 (SELECT SUM(mtlt.transaction_quantity) qty,
1858 SUM(mtlt.primary_quantity) primary_qty,
1859 SUM(mtlt.secondary_quantity) secondary_qty,
1860 mtlt.lot_number,
1861 mtlt.lot_expiration_date,
1862 mtlt.product_transaction_id,
1863 mtlt.product_code,
1864 /*INVCONV*/
1865 /* mtlt.sublot_num, */
1866 /*end , INVCONV*/
1867 mtlt.reason_code
1868 FROM mtl_transaction_lots_temp mtlt
1869 GROUP BY mtlt.product_transaction_id,
1870 mtlt.lot_number,
1871 /*INVCONV*/
1872 /* mtlt.sublot_num, */
1873 /*end , INVCONV*/
1874 mtlt.lot_expiration_date,
1875 mtlt.product_code,
1876 mtlt.reason_code) mtltview
1877 WHERE ( mtltview.product_transaction_id = p_interface_transaction_id
1878 AND mtltview.product_code = 'RCV'
1879 AND rs.supply_source_id = p_supply_source_id);
1880
1881 IF (g_asn_debug = 'Y') THEN
1882 asn_debug.put_line('After insert into rcv_lots_supply ');
1883 asn_debug.put_line('INVCONV, Subllot_num has been removed in insert_lot_supply1 ');
1884 END IF;
1885
1886 OPEN c;
1887 FETCH c INTO l_rowid;
1888
1889 IF (c%NOTFOUND) THEN
1890 CLOSE c;
1891 RAISE NO_DATA_FOUND;
1892 END IF;
1893
1894 CLOSE c;
1895
1896 IF (g_asn_debug = 'Y') THEN
1897 asn_debug.put_line('Exit insert_lots_supply ');
1898 END IF;
1899 EXCEPTION
1900 WHEN NO_DATA_FOUND THEN
1901 IF (g_asn_debug = 'Y') THEN
1902 asn_debug.put_line('no_data_found insert_lots_supply ');
1903 END IF;
1904
1905 x_return_status := fnd_api.g_ret_sts_error;
1906 RAISE;
1907 WHEN OTHERS THEN
1908 IF (g_asn_debug = 'Y') THEN
1909 asn_debug.put_line('others insert_lots_supply ');
1910 END IF;
1911
1912 x_return_status := fnd_api.g_ret_sts_unexp_error;
1913 RAISE;
1914 END insert_lot_supply;
1915
1916 PROCEDURE insert_serial_supply(
1917 p_interface_transaction_id IN NUMBER,
1918 p_lot_number IN VARCHAR2,
1919 p_serial_number IN VARCHAR2,
1920 p_supply_type_code IN VARCHAR2,
1921 p_supply_source_id IN NUMBER,
1922 x_return_status OUT NOCOPY VARCHAR2
1923 ) IS
1924 CURSOR c IS
1925 SELECT rss.ROWID
1926 FROM rcv_serials_supply rss
1927 WHERE rss.transaction_id = p_supply_source_id;
1928
1929 l_rowid VARCHAR2(255);
1930 l_serial_count NUMBER;
1931 l_lpn_id rcv_supply.lpn_id%TYPE;
1932 l_validation_flag rcv_transactions_interface.validation_flag%TYPE;
1933 BEGIN
1934 IF (g_asn_debug = 'Y') THEN
1935 asn_debug.put_line('Enter insert_serial_supply ');
1936 asn_debug.put_line('p_interface_transaction_id ' || p_interface_transaction_id);
1937 asn_debug.put_line('p_serial_number ' || p_serial_number);
1938 asn_debug.put_line('p_lot_number ' || p_lot_number);
1939 asn_debug.put_line('p_supply_type_code ' || p_supply_type_code);
1940 asn_debug.put_line('p_supply_source_id ' || p_supply_source_id);
1941 END IF;
1942
1943 x_return_status := fnd_api.g_ret_sts_success;
1944
1945 /* We can now come here if it is a lot-serial item and there is
1946 * no row in mtl_transaction_lots_temp if user has not entered
1947 * any lot/serial info for this transaction(Receive, Transfer etc).
1948 * In this case we do not error nor insert. So return.
1949 */
1950 SELECT COUNT(*)
1951 INTO l_serial_count
1952 FROM mtl_serial_numbers_temp msnt
1953 WHERE msnt.product_transaction_id = p_interface_transaction_id
1954 AND msnt.product_code = 'RCV';
1955
1956 IF (l_serial_count = 0) THEN
1957 RETURN;
1958 END IF;
1959
1960 IF (g_asn_debug = 'Y') THEN
1961 asn_debug.put_line('l_serial_count ' || l_serial_count);
1962 END IF;
1963
1964 /* We need to insert into rcv_lots_supply and
1965 * rcv_serials_supply table only when we come through ROI
1966 * or when we come through desktop and have lpn info.
1967 * We insert lpn_id in rcv_supply. So return if there is
1968 * a value and validation_flag is N.
1969 */
1970 SELECT NVL(validation_flag, 'N')
1971 INTO l_validation_flag
1972 FROM rcv_transactions_interface
1973 WHERE interface_transaction_id = p_interface_transaction_id;
1974
1975 SELECT NVL(lpn_id, -999)
1976 INTO l_lpn_id
1977 FROM rcv_supply
1978 WHERE supply_source_id = p_supply_source_id;
1979
1980 IF ( l_validation_flag = 'N'
1981 AND l_lpn_id = -999) THEN
1982 RETURN;
1983 END IF;
1984
1985 IF (g_asn_debug = 'Y') THEN
1986 asn_debug.put_line('l_validation_flag ' || l_validation_flag);
1987 asn_debug.put_line(' l_lpn_id ' || l_lpn_id);
1988 END IF;
1989
1990 /* We can only use rcv_transactions but in cases of direct deliver
1991 * there will be two rows in rt with same interface_txn_id. Hence
1992 * use rcv_supply and this way we will only use the receiving row.
1993 */
1994 INSERT INTO rcv_serials_supply
1995 (supply_type_code,
1996 shipment_line_id,
1997 transaction_id,
1998 lot_num,
1999 serial_num,
2000 last_update_date,
2001 last_updated_by,
2002 creation_date,
2003 created_by,
2004 last_update_login,
2005 request_id,
2006 program_application_id,
2007 program_id,
2008 program_update_date
2009 )
2010 SELECT p_supply_type_code,
2011 rs.shipment_line_id,
2012 DECODE(p_supply_type_code,
2013 'RECEIVING', rs.supply_source_id,
2014 NULL
2015 ),
2016 p_lot_number,
2017 p_serial_number,
2018 rs.last_update_date,
2019 rs.last_updated_by,
2020 rs.creation_date,
2021 rs.created_by,
2022 rs.last_update_login,
2023 rs.request_id,
2024 rs.program_application_id,
2025 rs.program_id,
2026 rs.program_update_date
2027 FROM rcv_supply rs
2028 WHERE rs.supply_source_id = p_supply_source_id;
2029
2030 IF (g_asn_debug = 'Y') THEN
2031 asn_debug.put_line(' After insert into rcv_serials_supply');
2032 END IF;
2033
2034 OPEN c;
2035 FETCH c INTO l_rowid;
2036
2037 IF (c%NOTFOUND) THEN
2038 CLOSE c;
2039 RAISE NO_DATA_FOUND;
2040 END IF;
2041
2042 CLOSE c;
2043
2044 IF (g_asn_debug = 'Y') THEN
2045 asn_debug.put_line(' Exit insert_serial_supply');
2046 END IF;
2047 EXCEPTION
2048 WHEN NO_DATA_FOUND THEN
2049 IF (g_asn_debug = 'Y') THEN
2050 asn_debug.put_line(' no_data_found insert_serial_supply');
2051 END IF;
2052
2053 x_return_status := fnd_api.g_ret_sts_error;
2054 RAISE;
2055 WHEN OTHERS THEN
2056 IF (g_asn_debug = 'Y') THEN
2057 asn_debug.put_line(' others insert_serial_supply');
2058 END IF;
2059
2060 x_return_status := fnd_api.g_ret_sts_unexp_error;
2061 RAISE;
2062 END insert_serial_supply;
2063
2064 PROCEDURE insert_lot_transactions(
2065 p_interface_transaction_id IN NUMBER,
2066 p_lot_context IN VARCHAR2,
2067 p_lot_context_id IN NUMBER,
2068 p_source_transaction_id IN NUMBER,
2069 p_correction_transaction_id IN NUMBER,
2070 p_negate_qty IN VARCHAR2,
2071 x_return_status OUT NOCOPY VARCHAR2
2072 ) IS
2073 l_lot_count NUMBER;
2074 l_lpn_id rcv_supply.lpn_id%TYPE;
2075 l_validation_flag rcv_transactions_interface.validation_flag%TYPE;
2076 BEGIN
2077 IF (g_asn_debug = 'Y') THEN
2078 asn_debug.put_line(' enter insert_lot_transactions');
2079 asn_debug.put_line('p_interface_transaction_id ' || p_interface_transaction_id);
2080 asn_debug.put_line('p_lot_context ' || p_lot_context);
2081 asn_debug.put_line('p_lot_context_id ' || p_lot_context_id);
2082 asn_debug.put_line('p_source_transaction_id ' || p_source_transaction_id);
2083 asn_debug.put_line('p_correction_transaction_id ' || p_correction_transaction_id);
2084 asn_debug.put_line('p_negate_qty ' || p_negate_qty);
2085 END IF;
2086
2087 x_return_status := fnd_api.g_ret_sts_success;
2088
2089 /* We can now come here if it is a lot-serial item and there is
2090 * no row in mtl_transaction_lots_temp if user has not entered
2091 * any lot/serial info for this transaction(Receive, Transfer etc).
2092 * In this case we do not error nor insert. So return.
2093 */
2094 SELECT COUNT(*)
2095 INTO l_lot_count
2096 FROM mtl_transaction_lots_temp mtlt
2097 WHERE mtlt.product_transaction_id = p_interface_transaction_id
2098 AND mtlt.product_code = 'RCV';
2099
2100 IF (l_lot_count = 0) THEN
2101 RETURN;
2102 END IF;
2103
2104 /* INVCONV , sublot_num to be removed as part of new lot model. Punit Kumar*/
2105
2106 INSERT INTO rcv_lot_transactions
2107 (lot_transaction_type,
2108 shipment_line_id,
2109 transaction_id,
2110 source_transaction_id,
2111 correction_transaction_id,
2112 created_by,
2113 creation_date,
2114 last_updated_by,
2115 last_update_date,
2116 last_update_login,
2117 request_id,
2118 program_application_id,
2119 program_id,
2120 program_update_date,
2121 transaction_date,
2122 item_id,
2123 lot_num,
2124 quantity,
2125 primary_quantity,
2126 expiration_date,
2127 /* INVCONV */
2128 /* sublot_num, */
2129 /* end , INVCONV*/
2130 secondary_quantity,
2131 reason_code
2132 )
2133 SELECT DECODE(p_lot_context,
2134 'CORRECTION', 'TRANSACTION',
2135 p_lot_context
2136 ),
2137 rti.shipment_line_id,
2138 DECODE(p_lot_context,
2139 'SHIPMENT', -1,
2140 p_lot_context_id
2141 ),
2142 DECODE(p_lot_context,
2143 'SHIPMENT', -1,
2144 p_source_transaction_id
2145 ),
2146 p_correction_transaction_id,
2147 rti.created_by,
2148 rti.creation_date,
2149 rti.last_updated_by,
2150 rti.last_update_date,
2151 rti.last_update_login,
2152 rti.request_id,
2153 rti.program_application_id,
2154 rti.program_id,
2155 SYSDATE,
2156 rti.transaction_date,
2157 rti.item_id,
2158 mtltview.lot_number,
2159 DECODE(p_negate_qty,
2160 'Y',(mtltview.qty * -1),
2161 mtltview.qty
2162 ),
2163 DECODE(p_negate_qty,
2164 'Y',(mtltview.primary_qty * -1),
2165 mtltview.primary_qty
2166 ),
2167 mtltview.lot_expiration_date,
2168 /*INVCONV*/
2169 /* mtltview.sublot_num, */
2170 /*end ,INVCONV*/
2171 mtltview.secondary_qty,
2172 mtltview.reason_code
2173 FROM rcv_transactions_interface rti,
2174 (SELECT SUM(mtlt.transaction_quantity) qty,
2175 SUM(mtlt.primary_quantity) primary_qty,
2176 SUM(mtlt.secondary_quantity) secondary_qty,
2177 mtlt.lot_number,
2178 mtlt.lot_expiration_date,
2179 mtlt.product_transaction_id,
2180 /*INVCONV*/
2181 /* mtlt.sublot_num, */
2182 /*end , INVCONV*/
2183 mtlt.reason_code,
2184 mtlt.product_code
2185 FROM mtl_transaction_lots_temp mtlt
2186 GROUP BY mtlt.product_transaction_id,
2187 mtlt.lot_number,
2188 mtlt.lot_expiration_date,
2189 /* INVCONV*/
2190 /* mtlt.sublot_num, */
2191 /*end , INVCONV*/
2192 mtlt.reason_code,
2193 mtlt.product_code) mtltview
2194 WHERE mtltview.product_transaction_id = p_interface_transaction_id
2195 AND mtltview.product_code = 'RCV'
2196 AND rti.interface_transaction_id = mtltview.product_transaction_id;
2197
2198 IF (g_asn_debug = 'Y') THEN
2199 asn_debug.put_line('Exit insert_lot_transactions ');
2200 asn_debug.put_line('INVCONV , sublot_num has not been inserted in rcv_lot_transactions');
2201 END IF;
2202 EXCEPTION
2203 WHEN NO_DATA_FOUND THEN
2204 IF (g_asn_debug = 'Y') THEN
2205 asn_debug.put_line('no_data_found insert_lot_transactions ');
2206 END IF;
2207
2208 x_return_status := fnd_api.g_ret_sts_error;
2209 RAISE;
2210 WHEN OTHERS THEN
2211 IF (g_asn_debug = 'Y') THEN
2212 asn_debug.put_line('others insert_lot_transactions ');
2213 END IF;
2214
2215 x_return_status := fnd_api.g_ret_sts_unexp_error;
2216 RAISE;
2217 END insert_lot_transactions;
2218
2219 PROCEDURE validate_lpn_groups(
2220 p_request_id IN NUMBER,
2221 p_group_id IN NUMBER
2222 ) IS
2223 l_lpn_group_id rcv_transactions_interface.lpn_group_id%TYPE;
2224 l_ret_status VARCHAR2(20);
2225 l_msg_cnt NUMBER;
2226 l_msg_data VARCHAR2(100);
2227 l_return BOOLEAN := TRUE;
2228 l_header_id NUMBER;
2229 l_ship_id NUMBER;
2230 l_asn_type VARCHAR2(10);
2231 x_fail_if_one_line_fails VARCHAR2(1) := 'N';
2232 x_interface_type VARCHAR2(25) := 'RCV-856';
2233 x_dummy_flag VARCHAR2(1) := 'Y';
2234 BEGIN
2235 IF (g_asn_debug = 'Y') THEN
2236 asn_debug.put_line('Enter validate_lpn_groups ');
2237 asn_debug.put_line('group_id ' || p_group_id);
2238 asn_debug.put_line('p_request_id ' || p_request_id);
2239 END IF;
2240
2241 fnd_profile.get('RCV_FAIL_IF_LINE_FAILS', x_fail_if_one_line_fails);
2242 OPEN rcv_processor_pvt.lpn_grps_cur(p_request_id, p_group_id);
2243
2244 LOOP --{
2245 FETCH rcv_processor_pvt.lpn_grps_cur INTO l_lpn_group_id;
2246 EXIT WHEN rcv_processor_pvt.lpn_grps_cur%NOTFOUND;
2247
2248 IF l_lpn_group_id IS NOT NULL THEN --{
2249 IF (g_asn_debug = 'Y') THEN
2250 asn_debug.put_line('Before calling inv api ');
2251 END IF;
2252
2253 l_return := inv_rcv_integration_apis.validate_lpn_info(1.0,
2254 fnd_api.g_true,
2255 l_ret_status,
2256 l_msg_cnt,
2257 l_msg_data,
2258 inv_rcv_integration_apis.g_exists_or_create,
2259 l_lpn_group_id
2260 );
2261
2262 IF (g_asn_debug = 'Y') THEN
2263 asn_debug.put_line('After calling inv api ');
2264 END IF;
2265
2266 --If l_ret_status <> fnd_api.g_ret_sts_success then --
2267 IF (l_return <> TRUE) THEN --{
2268 IF (g_asn_debug = 'Y') THEN
2269 asn_debug.put_line('l_return is false ');
2270 END IF;
2271
2272 IF (x_fail_if_one_line_fails = 'Y') THEN
2273 IF (g_asn_debug = 'Y') THEN
2274 asn_debug.put_line(' fail line is Y');
2275 END IF;
2276
2277 SELECT NVL(header_interface_id, -999),
2278 auto_transact_code,
2279 shipment_header_id
2280 INTO l_header_id,
2281 l_asn_type,
2282 l_ship_id
2283 FROM rcv_transactions_interface
2284 WHERE lpn_group_id = l_lpn_group_id
2285 AND GROUP_ID = DECODE(p_group_id,
2286 0, GROUP_ID,
2287 p_group_id
2288 )
2289 AND ROWNUM < 2;
2290 END IF;
2291
2292 /* for an asn, when the profile option says fail all lines,
2293 we must delete the shipment_header if it exists
2294 update the rhi and rti to error for the shipment_headerid */
2295 IF l_header_id <> -999
2296 AND l_asn_type = 'SHIP' THEN --{
2297 IF (g_asn_debug = 'Y') THEN
2298 asn_debug.put_line(' This is an ASN');
2299 END IF;
2300
2301 UPDATE rcv_headers_interface
2302 SET processing_status_code = 'ERROR'
2303 WHERE header_interface_id = l_header_id;
2304
2305 rcv_roi_preprocessor.update_rti_error(p_group_id => p_group_id,
2306 p_interface_id => NULL,
2307 p_header_interface_id => l_header_id,
2308 p_lpn_group_id => NULL
2309 );
2310 ELSE
2311 /* for non-ASN transactions we should update the corresponding lpn group
2312 to error */
2313 IF (g_asn_debug = 'Y') THEN
2314 asn_debug.put_line(' Before updating rti error');
2315 END IF;
2316
2317 rcv_roi_preprocessor.update_rti_error(p_group_id => p_group_id,
2318 p_interface_id => NULL,
2319 p_header_interface_id => NULL,
2320 p_lpn_group_id => l_lpn_group_id
2321 );
2322 END IF; --}
2323 -- insert into interface errors
2324 -- need to get correct error message for pushing to errors table
2325
2326 rcv_error_pkg.set_error_message('RCV_TRANSACTIONS_INTERFACE');
2327 rcv_error_pkg.set_token('LPN_GROUP_ID', l_lpn_group_id);
2328 rcv_error_pkg.log_interface_error('LPN_GROUP_ID', FALSE);
2329 END IF; --}
2330 END IF; --}
2331 END LOOP; --}
2332
2333 CLOSE rcv_processor_pvt.lpn_grps_cur;
2334
2335 IF (g_asn_debug = 'Y') THEN
2336 asn_debug.put_line(' Exit validate_lpn_groups');
2337 END IF;
2338 END validate_lpn_groups;
2339 END rcv_processor_pvt;
[Home] [Help]