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