[Home] [Help]
1: PACKAGE BODY rcv_roi_preprocessor AS
2: /* $Header: RCVPREPB.pls 120.37 2012/01/19 10:09:23 sadibhat ship $*/
3: -- Read the profile option that enables/disables the debug log
4: g_asn_debug VARCHAR2(1) := asn_debug.is_debug_on; -- Bug 9152790
5: x_interface_type VARCHAR2(25) := 'RCV-856';
6:
7: /* Shikyu Project This helping function is needed for cursor*/
8: FUNCTION get_oe_osa_flag(
81: n IN OUT NOCOPY BINARY_INTEGER) IS
82:
83: l_destination_type VARCHAR2(10);
84: BEGIN
85: IF (g_asn_debug = 'Y') THEN
86: asn_debug.put_line('In derive_destination_info');
87: asn_debug.put_line('x_cascaded_table(n).transaction_type = ' || x_cascaded_table(n).transaction_type);
88: asn_debug.put_line('x_cascaded_table(n).auto_transact_code = ' || x_cascaded_table(n).auto_transact_code);
89: asn_debug.put_line('x_cascaded_table(n).destination_type_code = ' || x_cascaded_table(n).destination_type_code);
82:
83: l_destination_type VARCHAR2(10);
84: BEGIN
85: IF (g_asn_debug = 'Y') THEN
86: asn_debug.put_line('In derive_destination_info');
87: asn_debug.put_line('x_cascaded_table(n).transaction_type = ' || x_cascaded_table(n).transaction_type);
88: asn_debug.put_line('x_cascaded_table(n).auto_transact_code = ' || x_cascaded_table(n).auto_transact_code);
89: asn_debug.put_line('x_cascaded_table(n).destination_type_code = ' || x_cascaded_table(n).destination_type_code);
90: asn_debug.put_line('x_cascaded_table(n).destination_context = ' || x_cascaded_table(n).destination_context);
83: l_destination_type VARCHAR2(10);
84: BEGIN
85: IF (g_asn_debug = 'Y') THEN
86: asn_debug.put_line('In derive_destination_info');
87: asn_debug.put_line('x_cascaded_table(n).transaction_type = ' || x_cascaded_table(n).transaction_type);
88: asn_debug.put_line('x_cascaded_table(n).auto_transact_code = ' || x_cascaded_table(n).auto_transact_code);
89: asn_debug.put_line('x_cascaded_table(n).destination_type_code = ' || x_cascaded_table(n).destination_type_code);
90: asn_debug.put_line('x_cascaded_table(n).destination_context = ' || x_cascaded_table(n).destination_context);
91: END IF;
84: BEGIN
85: IF (g_asn_debug = 'Y') THEN
86: asn_debug.put_line('In derive_destination_info');
87: asn_debug.put_line('x_cascaded_table(n).transaction_type = ' || x_cascaded_table(n).transaction_type);
88: asn_debug.put_line('x_cascaded_table(n).auto_transact_code = ' || x_cascaded_table(n).auto_transact_code);
89: asn_debug.put_line('x_cascaded_table(n).destination_type_code = ' || x_cascaded_table(n).destination_type_code);
90: asn_debug.put_line('x_cascaded_table(n).destination_context = ' || x_cascaded_table(n).destination_context);
91: END IF;
92:
85: IF (g_asn_debug = 'Y') THEN
86: asn_debug.put_line('In derive_destination_info');
87: asn_debug.put_line('x_cascaded_table(n).transaction_type = ' || x_cascaded_table(n).transaction_type);
88: asn_debug.put_line('x_cascaded_table(n).auto_transact_code = ' || x_cascaded_table(n).auto_transact_code);
89: asn_debug.put_line('x_cascaded_table(n).destination_type_code = ' || x_cascaded_table(n).destination_type_code);
90: asn_debug.put_line('x_cascaded_table(n).destination_context = ' || x_cascaded_table(n).destination_context);
91: END IF;
92:
93: IF ( ( x_cascaded_table(n).transaction_type IN ('TRANSFER', 'ACCEPT', 'REJECT', 'UNORDERED', 'RETURN TO VENDOR', 'RETURN TO CUSTOMER'))
86: asn_debug.put_line('In derive_destination_info');
87: asn_debug.put_line('x_cascaded_table(n).transaction_type = ' || x_cascaded_table(n).transaction_type);
88: asn_debug.put_line('x_cascaded_table(n).auto_transact_code = ' || x_cascaded_table(n).auto_transact_code);
89: asn_debug.put_line('x_cascaded_table(n).destination_type_code = ' || x_cascaded_table(n).destination_type_code);
90: asn_debug.put_line('x_cascaded_table(n).destination_context = ' || x_cascaded_table(n).destination_context);
91: END IF;
92:
93: IF ( ( x_cascaded_table(n).transaction_type IN ('TRANSFER', 'ACCEPT', 'REJECT', 'UNORDERED', 'RETURN TO VENDOR', 'RETURN TO CUSTOMER'))
94: OR
160: WHERE po_distribution_id = x_cascaded_table(n).po_distribution_id;
161: END IF;
162: END IF;
163:
164: IF (g_asn_debug = 'Y') THEN
165: asn_debug.put_line('New x_cascaded_table(n).destination_type_code = ' || x_cascaded_table(n).destination_type_code);
166: asn_debug.put_line('New x_cascaded_table(n).destination_context = ' || x_cascaded_table(n).destination_context);
167: asn_debug.put_line('Leaving derive_destination_info');
168: END IF;
161: END IF;
162: END IF;
163:
164: IF (g_asn_debug = 'Y') THEN
165: asn_debug.put_line('New x_cascaded_table(n).destination_type_code = ' || x_cascaded_table(n).destination_type_code);
166: asn_debug.put_line('New x_cascaded_table(n).destination_context = ' || x_cascaded_table(n).destination_context);
167: asn_debug.put_line('Leaving derive_destination_info');
168: END IF;
169:
162: END IF;
163:
164: IF (g_asn_debug = 'Y') THEN
165: asn_debug.put_line('New x_cascaded_table(n).destination_type_code = ' || x_cascaded_table(n).destination_type_code);
166: asn_debug.put_line('New x_cascaded_table(n).destination_context = ' || x_cascaded_table(n).destination_context);
167: asn_debug.put_line('Leaving derive_destination_info');
168: END IF;
169:
170: EXCEPTION
163:
164: IF (g_asn_debug = 'Y') THEN
165: asn_debug.put_line('New x_cascaded_table(n).destination_type_code = ' || x_cascaded_table(n).destination_type_code);
166: asn_debug.put_line('New x_cascaded_table(n).destination_context = ' || x_cascaded_table(n).destination_context);
167: asn_debug.put_line('Leaving derive_destination_info');
168: END IF;
169:
170: EXCEPTION
171: WHEN OTHERS THEN
168: END IF;
169:
170: EXCEPTION
171: WHEN OTHERS THEN
172: IF (g_asn_debug = 'Y') THEN
173: asn_debug.put_line('Exception in derive_destination_info');
174: asn_debug.put_line('sqlerrm : ' || SQLERRM);
175: END IF;
176: x_cascaded_table(n).error_status := rcv_error_pkg.g_ret_sts_error;
169:
170: EXCEPTION
171: WHEN OTHERS THEN
172: IF (g_asn_debug = 'Y') THEN
173: asn_debug.put_line('Exception in derive_destination_info');
174: asn_debug.put_line('sqlerrm : ' || SQLERRM);
175: END IF;
176: x_cascaded_table(n).error_status := rcv_error_pkg.g_ret_sts_error;
177:
170: EXCEPTION
171: WHEN OTHERS THEN
172: IF (g_asn_debug = 'Y') THEN
173: asn_debug.put_line('Exception in derive_destination_info');
174: asn_debug.put_line('sqlerrm : ' || SQLERRM);
175: END IF;
176: x_cascaded_table(n).error_status := rcv_error_pkg.g_ret_sts_error;
177:
178: END derive_destination_info;
303: x_asn_rhi_record rcv_roi_preprocessor.header_rec_type;
304:
305: BEGIN
306: <
307: g_asn_debug := asn_debug.is_debug_on; -- Bug 9152790
308:
309:
310: /* For online mode, we send request_id as null. Consider it as -999 if
311: * it is null.
312: */
313: p_request_id := NVL(x_request_id, 0);
314: p_group_id := NVL(x_group_id, 0);
315:
316: IF (g_asn_debug = 'Y') THEN
317: asn_debug.put_line('Entering preprocessor. Request_id = ' || p_request_id || ',Group_id=' || p_group_id);
318: END IF;
319:
320: SAVEPOINT dcp_preprocessor_start;
313: p_request_id := NVL(x_request_id, 0);
314: p_group_id := NVL(x_group_id, 0);
315:
316: IF (g_asn_debug = 'Y') THEN
317: asn_debug.put_line('Entering preprocessor. Request_id = ' || p_request_id || ',Group_id=' || p_group_id);
318: END IF;
319:
320: SAVEPOINT dcp_preprocessor_start;
321: l_check_dcp := rcv_dcp_pvt.g_check_dcp;
335: IF x_fail_all_lines = 'Y' THEN
336: x_fail_if_one_line_fails := TRUE;
337: END IF;
338:
339: IF (g_asn_debug = 'Y') THEN
340: asn_debug.put_line('RCV_FAIL_IF_LINE_FAILS profile option =' || x_fail_all_lines);
341: END IF;
342:
343: /* the garbage collector is no longer needed by essential character of the normalization package.
336: x_fail_if_one_line_fails := TRUE;
337: END IF;
338:
339: IF (g_asn_debug = 'Y') THEN
340: asn_debug.put_line('RCV_FAIL_IF_LINE_FAILS profile option =' || x_fail_all_lines);
341: END IF;
342:
343: /* the garbage collector is no longer needed by essential character of the normalization package.
344: if a row belongs to an org, run it, else fail it (or leave it pending).
392: AND ( (transaction_type = 'DELIVER')
393: OR ( transaction_type = 'RECEIVE'
394: AND auto_transact_code = 'DELIVER'));
395:
396: IF (g_asn_debug = 'Y') THEN
397: asn_debug.put_line('Set transfer_lpn_id and transfer_license_plate_number to null for deliver transactions for the non-wms org ' || l_to_org_id);
398: END IF;
399: END IF;
400: END LOOP;
393: OR ( transaction_type = 'RECEIVE'
394: AND auto_transact_code = 'DELIVER'));
395:
396: IF (g_asn_debug = 'Y') THEN
397: asn_debug.put_line('Set transfer_lpn_id and transfer_license_plate_number to null for deliver transactions for the non-wms org ' || l_to_org_id);
398: END IF;
399: END IF;
400: END LOOP;
401: CLOSE distinct_org_id;--Bug#6862487
399: END IF;
400: END LOOP;
401: CLOSE distinct_org_id;--Bug#6862487
402: else--Online mode transaction
403: if (g_asn_debug = 'Y') then
404: asn_debug.put_line('Skipped Set transfer_lpn_id and transfer_license_plate_number to null for ONLINE mode txn '||p_request_id);
405: end if;
406: end if;--Bug#6862487
407: /* End of 3434460. */
400: END LOOP;
401: CLOSE distinct_org_id;--Bug#6862487
402: else--Online mode transaction
403: if (g_asn_debug = 'Y') then
404: asn_debug.put_line('Skipped Set transfer_lpn_id and transfer_license_plate_number to null for ONLINE mode txn '||p_request_id);
405: end if;
406: end if;--Bug#6862487
407: /* End of 3434460. */
408: IF (g_asn_debug = 'Y') THEN
404: asn_debug.put_line('Skipped Set transfer_lpn_id and transfer_license_plate_number to null for ONLINE mode txn '||p_request_id);
405: end if;
406: end if;--Bug#6862487
407: /* End of 3434460. */
408: IF (g_asn_debug = 'Y') THEN
409: asn_debug.put_line('After update to order_transaction_id');
410: END IF;
411:
412: /* this belongs before the transaction looping */
405: end if;
406: end if;--Bug#6862487
407: /* End of 3434460. */
408: IF (g_asn_debug = 'Y') THEN
409: asn_debug.put_line('After update to order_transaction_id');
410: END IF;
411:
412: /* this belongs before the transaction looping */
413: FOR bad_shikyu IN get_bad_asbn_shikyu LOOP --Shikyu project
436: END LOOP;
437:
438: OPEN rcv_roi_preprocessor.txns_cur(p_request_id, p_group_id);
439:
440: IF (g_asn_debug = 'Y') THEN
441: asn_debug.put_line('Opened transactions cursor.');
442: END IF;
443:
444: x_progress := '010';
437:
438: OPEN rcv_roi_preprocessor.txns_cur(p_request_id, p_group_id);
439:
440: IF (g_asn_debug = 'Y') THEN
441: asn_debug.put_line('Opened transactions cursor.');
442: END IF;
443:
444: x_progress := '010';
445: n := 0;
446: x_cascaded_table.DELETE;
447:
448: -- Loop through the entries in rcv_transactions_interface.
449: LOOP --{
450: asn_debug.put_line('enter loop');
451: n := n + 1;
452: FETCH rcv_roi_preprocessor.txns_cur INTO x_cascaded_table(n);
453: EXIT WHEN rcv_roi_preprocessor.txns_cur%NOTFOUND;
454: x_cascaded_table(n).error_status := 'S';
460: l_proc_status_code := 'SUCCESS';
461: l_update_lpn_group := FALSE;
462: l_transaction_type_old := x_cascaded_table(n).transaction_type; -- Bug 7684677
463:
464: IF (g_asn_debug = 'Y') THEN
465: asn_debug.put_line('Current counter is ' || TO_CHAR(n));
466: asn_debug.put_line('No of records in cascaded table ' || TO_CHAR(x_cascaded_table.COUNT));
467: asn_debug.put_line('header interface id is ' || TO_CHAR(x_cascaded_table(n).header_interface_id));
468: END IF;
461: l_update_lpn_group := FALSE;
462: l_transaction_type_old := x_cascaded_table(n).transaction_type; -- Bug 7684677
463:
464: IF (g_asn_debug = 'Y') THEN
465: asn_debug.put_line('Current counter is ' || TO_CHAR(n));
466: asn_debug.put_line('No of records in cascaded table ' || TO_CHAR(x_cascaded_table.COUNT));
467: asn_debug.put_line('header interface id is ' || TO_CHAR(x_cascaded_table(n).header_interface_id));
468: END IF;
469:
462: l_transaction_type_old := x_cascaded_table(n).transaction_type; -- Bug 7684677
463:
464: IF (g_asn_debug = 'Y') THEN
465: asn_debug.put_line('Current counter is ' || TO_CHAR(n));
466: asn_debug.put_line('No of records in cascaded table ' || TO_CHAR(x_cascaded_table.COUNT));
467: asn_debug.put_line('header interface id is ' || TO_CHAR(x_cascaded_table(n).header_interface_id));
468: END IF;
469:
470: x_progress := '040';
463:
464: IF (g_asn_debug = 'Y') THEN
465: asn_debug.put_line('Current counter is ' || TO_CHAR(n));
466: asn_debug.put_line('No of records in cascaded table ' || TO_CHAR(x_cascaded_table.COUNT));
467: asn_debug.put_line('header interface id is ' || TO_CHAR(x_cascaded_table(n).header_interface_id));
468: END IF;
469:
470: x_progress := '040';
471: rcv_error_pkg.initialize(x_cascaded_table(n).GROUP_ID,
491: AND ( x_cascaded_table(n).org_id is NOT NULL ) ) THEN
492:
493: MO_GLOBAL.set_policy_context('S',TO_NUMBER(x_cascaded_table(n).org_id));
494:
495: IF (g_asn_debug = 'Y') THEN
496: asn_debug.put_line('Setting Operating unit context to ' ||x_cascaded_table(n).org_id);
497: END IF;
498:
499: l_prev_org_id := x_cascaded_table(n).org_id;
492:
493: MO_GLOBAL.set_policy_context('S',TO_NUMBER(x_cascaded_table(n).org_id));
494:
495: IF (g_asn_debug = 'Y') THEN
496: asn_debug.put_line('Setting Operating unit context to ' ||x_cascaded_table(n).org_id);
497: END IF;
498:
499: l_prev_org_id := x_cascaded_table(n).org_id;
500:
549: x_cascaded_table(n).error_message := rcv_error_pkg.get_last_message;
550: x_cascaded_table(n).processing_status_code := 'ERROR';
551: END;
552:
553: IF (g_asn_debug = 'Y') THEN
554: asn_debug.put_line('l_proc_status_code ' || l_proc_status_code);
555: END IF;
556:
557: -- begin processing of the header
550: x_cascaded_table(n).processing_status_code := 'ERROR';
551: END;
552:
553: IF (g_asn_debug = 'Y') THEN
554: asn_debug.put_line('l_proc_status_code ' || l_proc_status_code);
555: END IF;
556:
557: -- begin processing of the header
558: -- does the row have a header
580: x_error_record.error_status := 'S';
581: x_error_record.error_message := NULL;
582: END IF;
583:
584: IF (g_asn_debug = 'Y') THEN
585: asn_debug.put_line('x_error_record.error_status: '|| x_error_record.error_status);
586: asn_debug.put_line('transaction_type: '||x_cascaded_table(n).transaction_type);
587: asn_debug.put_line('header_record.header_id: '||x_header_record.header_record.header_interface_id);
588: asn_debug.put_line('x_cascaded_table(n).header_interface_id: '||x_cascaded_table(n).header_interface_id);
581: x_error_record.error_message := NULL;
582: END IF;
583:
584: IF (g_asn_debug = 'Y') THEN
585: asn_debug.put_line('x_error_record.error_status: '|| x_error_record.error_status);
586: asn_debug.put_line('transaction_type: '||x_cascaded_table(n).transaction_type);
587: asn_debug.put_line('header_record.header_id: '||x_header_record.header_record.header_interface_id);
588: asn_debug.put_line('x_cascaded_table(n).header_interface_id: '||x_cascaded_table(n).header_interface_id);
589: END IF;
582: END IF;
583:
584: IF (g_asn_debug = 'Y') THEN
585: asn_debug.put_line('x_error_record.error_status: '|| x_error_record.error_status);
586: asn_debug.put_line('transaction_type: '||x_cascaded_table(n).transaction_type);
587: asn_debug.put_line('header_record.header_id: '||x_header_record.header_record.header_interface_id);
588: asn_debug.put_line('x_cascaded_table(n).header_interface_id: '||x_cascaded_table(n).header_interface_id);
589: END IF;
590:
583:
584: IF (g_asn_debug = 'Y') THEN
585: asn_debug.put_line('x_error_record.error_status: '|| x_error_record.error_status);
586: asn_debug.put_line('transaction_type: '||x_cascaded_table(n).transaction_type);
587: asn_debug.put_line('header_record.header_id: '||x_header_record.header_record.header_interface_id);
588: asn_debug.put_line('x_cascaded_table(n).header_interface_id: '||x_cascaded_table(n).header_interface_id);
589: END IF;
590:
591: /* If rhi is success and all the other processed rti rows
584: IF (g_asn_debug = 'Y') THEN
585: asn_debug.put_line('x_error_record.error_status: '|| x_error_record.error_status);
586: asn_debug.put_line('transaction_type: '||x_cascaded_table(n).transaction_type);
587: asn_debug.put_line('header_record.header_id: '||x_header_record.header_record.header_interface_id);
588: asn_debug.put_line('x_cascaded_table(n).header_interface_id: '||x_cascaded_table(n).header_interface_id);
589: END IF;
590:
591: /* If rhi is success and all the other processed rti rows
592: * in the same header id is also successful.
595: IF (x_error_record.error_status IN('S', 'W')) THEN --{
596: IF x_cascaded_table(n).header_interface_id <>
597: nvl(x_header_record.header_record.header_interface_id, -1) THEN
598: --{ exclude the case where the current trxn shares header with the previous trxn.
599: IF (g_asn_debug = 'Y') THEN
600: asn_debug.put_line('Initialize header record for RTI id: '||
601: to_char(x_cascaded_table(n).interface_transaction_id));
602: END IF;
603: x_header_record := x_empty_header_record;
596: IF x_cascaded_table(n).header_interface_id <>
597: nvl(x_header_record.header_record.header_interface_id, -1) THEN
598: --{ exclude the case where the current trxn shares header with the previous trxn.
599: IF (g_asn_debug = 'Y') THEN
600: asn_debug.put_line('Initialize header record for RTI id: '||
601: to_char(x_cascaded_table(n).interface_transaction_id));
602: END IF;
603: x_header_record := x_empty_header_record;
604: -- initialize error_record
607: OPEN rcv_roi_preprocessor.headers_cur(p_request_id,
608: p_group_id,
609: x_cascaded_table(n).header_interface_id
610: );
611: IF (g_asn_debug = 'Y') THEN
612: asn_debug.put_line('Before processing header');
613: END IF;
614:
615: FETCH rcv_roi_preprocessor.headers_cur INTO x_header_record.header_record;
608: p_group_id,
609: x_cascaded_table(n).header_interface_id
610: );
611: IF (g_asn_debug = 'Y') THEN
612: asn_debug.put_line('Before processing header');
613: END IF;
614:
615: FETCH rcv_roi_preprocessor.headers_cur INTO x_header_record.header_record;
616: -- there should be 1 header record for this transaction
613: END IF;
614:
615: FETCH rcv_roi_preprocessor.headers_cur INTO x_header_record.header_record;
616: -- there should be 1 header record for this transaction
617: asn_debug.put_line('Processing header for interface txn id =' ||
618: TO_CHAR(x_cascaded_table(n).interface_transaction_id));
619: -- header cursor found, header is not processed yet : process the header
620: IF RCV_ROI_PREPROCESSOR.headers_cur%FOUND THEN --{
621: IF x_header_record.header_record.processing_status_code = 'RUNNING' THEN --{
647: rcv_roi_header.process_cancellation(x_header_record);
648:
649: IF (x_header_record.error_record.error_status NOT IN('S', 'W')) THEN --{
650: -- the cancellation failed
651: IF (g_asn_debug = 'Y') THEN
652: asn_debug.put_line('RCV_ASN_NOT_ACCEPT');
653: asn_debug.put_line('The header has failed ' || TO_CHAR(x_header_record.header_record.header_interface_id));
654: asn_debug.put_line('ASN could not be cancelled');
655: END IF;
648:
649: IF (x_header_record.error_record.error_status NOT IN('S', 'W')) THEN --{
650: -- the cancellation failed
651: IF (g_asn_debug = 'Y') THEN
652: asn_debug.put_line('RCV_ASN_NOT_ACCEPT');
653: asn_debug.put_line('The header has failed ' || TO_CHAR(x_header_record.header_record.header_interface_id));
654: asn_debug.put_line('ASN could not be cancelled');
655: END IF;
656:
649: IF (x_header_record.error_record.error_status NOT IN('S', 'W')) THEN --{
650: -- the cancellation failed
651: IF (g_asn_debug = 'Y') THEN
652: asn_debug.put_line('RCV_ASN_NOT_ACCEPT');
653: asn_debug.put_line('The header has failed ' || TO_CHAR(x_header_record.header_record.header_interface_id));
654: asn_debug.put_line('ASN could not be cancelled');
655: END IF;
656:
657: rcv_error_pkg.set_error_message('RCV_ASN_NOT_ACCEPT');
650: -- the cancellation failed
651: IF (g_asn_debug = 'Y') THEN
652: asn_debug.put_line('RCV_ASN_NOT_ACCEPT');
653: asn_debug.put_line('The header has failed ' || TO_CHAR(x_header_record.header_record.header_interface_id));
654: asn_debug.put_line('ASN could not be cancelled');
655: END IF;
656:
657: rcv_error_pkg.set_error_message('RCV_ASN_NOT_ACCEPT');
658: rcv_error_pkg.set_token('SHIPMENT', x_header_record.header_record.shipment_num);
673: END IF; --}
674: END IF; --} if processing_status_code is running
675: ELSE -- } { no header row is picked up by header cursor
676: -- header record is missing. need to error out this trxn.
677: IF (g_asn_debug = 'Y') THEN
678: asn_debug.put_line('Header missing for trxn '||
679: to_char(x_cascaded_table(n).interface_transaction_id) ||', set error_status to E');
680: END IF;
681: x_header_record.error_record.error_status := 'E';
674: END IF; --} if processing_status_code is running
675: ELSE -- } { no header row is picked up by header cursor
676: -- header record is missing. need to error out this trxn.
677: IF (g_asn_debug = 'Y') THEN
678: asn_debug.put_line('Header missing for trxn '||
679: to_char(x_cascaded_table(n).interface_transaction_id) ||', set error_status to E');
680: END IF;
681: x_header_record.error_record.error_status := 'E';
682: -- need to insert po_inerface_errors
684: rcv_error_pkg.set_token('TXN_TYPE', x_cascaded_table(n).transaction_type);
685: rcv_error_pkg.log_interface_error('HEADER_INTERFACE_ID', FALSE);
686: END IF; --} this is the check for whether header is processed
687:
688: asn_debug.put_line('closing the header cursor for txn = ' || TO_CHAR(x_cascaded_table(n).interface_transaction_id));
689: CLOSE rcv_roi_preprocessor.headers_cur;
690: END IF; --} matches excluding shared header.
691:
692: -- after processing header update rhi/rti
706: /* Bug 4344351: Log a message indicating an error in RCV_HEADERS_INTERFACE table.*/
707: rcv_error_pkg.log_interface_error('RCV_HEADERS_INTERFACE','',FALSE);
708:
709: -- 2) update rti
710: IF (g_asn_debug = 'Y') THEN
711: asn_debug.put_line('update_rti_error after rhi error ');
712: END IF;
713:
714: update_rti_error(p_group_id => x_cascaded_table(n).GROUP_ID,
707: rcv_error_pkg.log_interface_error('RCV_HEADERS_INTERFACE','',FALSE);
708:
709: -- 2) update rti
710: IF (g_asn_debug = 'Y') THEN
711: asn_debug.put_line('update_rti_error after rhi error ');
712: END IF;
713:
714: update_rti_error(p_group_id => x_cascaded_table(n).GROUP_ID,
715: p_interface_id => NULL,
723: * we dont process the rti row which belongs to a
724: * different OU.
725: */
726: ELSIF (x_header_record.error_record.error_status = 'P') THEN --}{
727: IF (g_asn_debug = 'Y') THEN
728: asn_debug.put_line('Set x_cascaded_table.error_status to P');
729: END IF;
730:
731: x_cascaded_table(n).error_status := 'P';
724: * different OU.
725: */
726: ELSIF (x_header_record.error_record.error_status = 'P') THEN --}{
727: IF (g_asn_debug = 'Y') THEN
728: asn_debug.put_line('Set x_cascaded_table.error_status to P');
729: END IF;
730:
731: x_cascaded_table(n).error_status := 'P';
732: x_cascaded_table(n).processing_status_code := 'PENDING';
737: receipt_header_id = x_header_record.header_record.receipt_header_id
738: WHERE header_interface_id = x_header_record.header_record.header_interface_id
739: AND processing_status_code <> 'SUCCESS';
740:
741: IF (g_asn_debug = 'Y') THEN
742: asn_debug.put_line('RCV_ASN_ACCEPT_NO_ERR');
743: END IF;
744: END IF; --} header errored out
745: /** Bug 8717477 when x_error_record.error_status is error, we also need x_header_record.header_record for following code **/
738: WHERE header_interface_id = x_header_record.header_record.header_interface_id
739: AND processing_status_code <> 'SUCCESS';
740:
741: IF (g_asn_debug = 'Y') THEN
742: asn_debug.put_line('RCV_ASN_ACCEPT_NO_ERR');
743: END IF;
744: END IF; --} header errored out
745: /** Bug 8717477 when x_error_record.error_status is error, we also need x_header_record.header_record for following code **/
746: ELSE -- IF (x_error_record.error_status IN('S', 'W')) THEN
745: /** Bug 8717477 when x_error_record.error_status is error, we also need x_header_record.header_record for following code **/
746: ELSE -- IF (x_error_record.error_status IN('S', 'W')) THEN
747: IF x_cascaded_table(n).header_interface_id <>
748: nvl(x_header_record.header_record.header_interface_id, -1) THEN
749: IF (g_asn_debug = 'Y') THEN
750: asn_debug.put_line('x_error_record.error_status:' || x_error_record.error_status);
751: asn_debug.put_line('Initialize header record for RTI id: '||
752: to_char(x_cascaded_table(n).interface_transaction_id));
753: END IF;
746: ELSE -- IF (x_error_record.error_status IN('S', 'W')) THEN
747: IF x_cascaded_table(n).header_interface_id <>
748: nvl(x_header_record.header_record.header_interface_id, -1) THEN
749: IF (g_asn_debug = 'Y') THEN
750: asn_debug.put_line('x_error_record.error_status:' || x_error_record.error_status);
751: asn_debug.put_line('Initialize header record for RTI id: '||
752: to_char(x_cascaded_table(n).interface_transaction_id));
753: END IF;
754: x_header_record := x_empty_header_record;
747: IF x_cascaded_table(n).header_interface_id <>
748: nvl(x_header_record.header_record.header_interface_id, -1) THEN
749: IF (g_asn_debug = 'Y') THEN
750: asn_debug.put_line('x_error_record.error_status:' || x_error_record.error_status);
751: asn_debug.put_line('Initialize header record for RTI id: '||
752: to_char(x_cascaded_table(n).interface_transaction_id));
753: END IF;
754: x_header_record := x_empty_header_record;
755: x_header_record.error_record := x_error_record;
759: );
760: FETCH rcv_roi_preprocessor.headers_cur INTO x_header_record.header_record;
761: IF RCV_ROI_PREPROCESSOR.headers_cur%NOTFOUND THEN --{
762: -- header record is missing. need to error out this trxn.
763: IF (g_asn_debug = 'Y') THEN
764: asn_debug.put_line('Header missing for trxn '||
765: to_char(x_cascaded_table(n).interface_transaction_id) ||', set error_status to E');
766: END IF;
767: x_header_record.error_record.error_status := 'E';
760: FETCH rcv_roi_preprocessor.headers_cur INTO x_header_record.header_record;
761: IF RCV_ROI_PREPROCESSOR.headers_cur%NOTFOUND THEN --{
762: -- header record is missing. need to error out this trxn.
763: IF (g_asn_debug = 'Y') THEN
764: asn_debug.put_line('Header missing for trxn '||
765: to_char(x_cascaded_table(n).interface_transaction_id) ||', set error_status to E');
766: END IF;
767: x_header_record.error_record.error_status := 'E';
768: -- need to insert po_inerface_errors
770: rcv_error_pkg.set_token('TXN_TYPE', x_cascaded_table(n).transaction_type);
771: rcv_error_pkg.log_interface_error('HEADER_INTERFACE_ID', FALSE);
772:
773: END IF;
774: asn_debug.put_line('closing the header cursor for txn = ' || TO_CHAR(x_cascaded_table(n).interface_transaction_id));
775: CLOSE rcv_roi_preprocessor.headers_cur;
776:
777: END IF;
778: /**End Bug 8717477 **/
777: END IF;
778: /**End Bug 8717477 **/
779: END IF; --} matches with x_error_record.error_record <> E
780: ELSE --}{
781: IF (g_asn_debug = 'Y') THEN
782: asn_debug.put_line('reset header record to empty for headerless trxns or errored out trxns '||
783: to_char(x_cascaded_table(n).interface_transaction_id) );
784: END IF;
785: x_header_record := x_empty_header_record;
778: /**End Bug 8717477 **/
779: END IF; --} matches with x_error_record.error_record <> E
780: ELSE --}{
781: IF (g_asn_debug = 'Y') THEN
782: asn_debug.put_line('reset header record to empty for headerless trxns or errored out trxns '||
783: to_char(x_cascaded_table(n).interface_transaction_id) );
784: END IF;
785: x_header_record := x_empty_header_record;
786: END IF; --} matches with whether transaction has header
791: IF x_cascaded_table(n).error_status IN('S', 'W') THEN
792: l_update_lpn_group := TRUE;
793: END IF;
794:
795: IF (g_asn_debug = 'Y') THEN
796: asn_debug.put_line('After processing header for this transaction:');
797: asn_debug.put_line('X_cascaded_table(n).header_interface_id=' || x_cascaded_table(n).header_interface_id);
798: asn_debug.put_line('X_header_record.error_record.error_status=' || x_header_record.error_record.error_status);
799: asn_debug.put_line('x_cascaded_table(n).error_status=' || x_cascaded_table(n).error_status);
792: l_update_lpn_group := TRUE;
793: END IF;
794:
795: IF (g_asn_debug = 'Y') THEN
796: asn_debug.put_line('After processing header for this transaction:');
797: asn_debug.put_line('X_cascaded_table(n).header_interface_id=' || x_cascaded_table(n).header_interface_id);
798: asn_debug.put_line('X_header_record.error_record.error_status=' || x_header_record.error_record.error_status);
799: asn_debug.put_line('x_cascaded_table(n).error_status=' || x_cascaded_table(n).error_status);
800: asn_debug.put_line('x_cascaded_table(n).error_message=' || x_cascaded_table(n).error_message);
793: END IF;
794:
795: IF (g_asn_debug = 'Y') THEN
796: asn_debug.put_line('After processing header for this transaction:');
797: asn_debug.put_line('X_cascaded_table(n).header_interface_id=' || x_cascaded_table(n).header_interface_id);
798: asn_debug.put_line('X_header_record.error_record.error_status=' || x_header_record.error_record.error_status);
799: asn_debug.put_line('x_cascaded_table(n).error_status=' || x_cascaded_table(n).error_status);
800: asn_debug.put_line('x_cascaded_table(n).error_message=' || x_cascaded_table(n).error_message);
801: END IF;
794:
795: IF (g_asn_debug = 'Y') THEN
796: asn_debug.put_line('After processing header for this transaction:');
797: asn_debug.put_line('X_cascaded_table(n).header_interface_id=' || x_cascaded_table(n).header_interface_id);
798: asn_debug.put_line('X_header_record.error_record.error_status=' || x_header_record.error_record.error_status);
799: asn_debug.put_line('x_cascaded_table(n).error_status=' || x_cascaded_table(n).error_status);
800: asn_debug.put_line('x_cascaded_table(n).error_message=' || x_cascaded_table(n).error_message);
801: END IF;
802:
795: IF (g_asn_debug = 'Y') THEN
796: asn_debug.put_line('After processing header for this transaction:');
797: asn_debug.put_line('X_cascaded_table(n).header_interface_id=' || x_cascaded_table(n).header_interface_id);
798: asn_debug.put_line('X_header_record.error_record.error_status=' || x_header_record.error_record.error_status);
799: asn_debug.put_line('x_cascaded_table(n).error_status=' || x_cascaded_table(n).error_status);
800: asn_debug.put_line('x_cascaded_table(n).error_message=' || x_cascaded_table(n).error_message);
801: END IF;
802:
803: /* bug 4368726, for asn cancel's the call rcv_roi_header.process_cancellation(x_header_record)
796: asn_debug.put_line('After processing header for this transaction:');
797: asn_debug.put_line('X_cascaded_table(n).header_interface_id=' || x_cascaded_table(n).header_interface_id);
798: asn_debug.put_line('X_header_record.error_record.error_status=' || x_header_record.error_record.error_status);
799: asn_debug.put_line('x_cascaded_table(n).error_status=' || x_cascaded_table(n).error_status);
800: asn_debug.put_line('x_cascaded_table(n).error_message=' || x_cascaded_table(n).error_message);
801: END IF;
802:
803: /* bug 4368726, for asn cancel's the call rcv_roi_header.process_cancellation(x_header_record)
804: will delete all the pending RTI rows and insert new RTI rows ready for the processor.
861: AND x_cascaded_table(n).error_status IN('S', 'W')) THEN --{
862:
863: /* Bug 7830436: Code changes to Freight and Special Charges flow */
864: if nvl(l_fsc_enabled,'N') = 'N' then
865: asn_debug.put_line('Freight and Special Charges is disabled, so charges are not processed');
866: else
867: RCV_CHARGES_GRP.preprocess_charges
868: ( p_api_version => 1.0
869: , p_init_msg_list => 'Y'
907: ** error_status is 'S' or 'W'
908: */
909: IF (x_cascaded_table(n).error_status NOT IN('S', 'W')) THEN
910:
911: IF (g_asn_debug = 'Y') THEN
912: asn_debug.put_line('Skipping call to handle_rcv_asn_txn ');
913: END IF;
914: x_cascaded_table(n).processing_status_code := 'ERROR';
915: l_proc_status_code := 'ERROR';
908: */
909: IF (x_cascaded_table(n).error_status NOT IN('S', 'W')) THEN
910:
911: IF (g_asn_debug = 'Y') THEN
912: asn_debug.put_line('Skipping call to handle_rcv_asn_txn ');
913: END IF;
914: x_cascaded_table(n).processing_status_code := 'ERROR';
915: l_proc_status_code := 'ERROR';
916: ELSE
933: where header_interface_id =
934: x_cascaded_table(n).header_interface_id))
935: AND asn_status = 'NEW_SHIP';
936:
937: IF (g_asn_debug = 'Y') THEN
938: asn_debug.put_line(sql%rowcount || ' new_ship RSH updated');
939: asn_debug.put_line('Before handle_rcv_asn_txn ');
940: END IF;
941: rcv_roi_transaction.handle_rcv_asn_transactions(x_cascaded_table, x_header_record);
934: x_cascaded_table(n).header_interface_id))
935: AND asn_status = 'NEW_SHIP';
936:
937: IF (g_asn_debug = 'Y') THEN
938: asn_debug.put_line(sql%rowcount || ' new_ship RSH updated');
939: asn_debug.put_line('Before handle_rcv_asn_txn ');
940: END IF;
941: rcv_roi_transaction.handle_rcv_asn_transactions(x_cascaded_table, x_header_record);
942: END IF;
935: AND asn_status = 'NEW_SHIP';
936:
937: IF (g_asn_debug = 'Y') THEN
938: asn_debug.put_line(sql%rowcount || ' new_ship RSH updated');
939: asn_debug.put_line('Before handle_rcv_asn_txn ');
940: END IF;
941: rcv_roi_transaction.handle_rcv_asn_transactions(x_cascaded_table, x_header_record);
942: END IF;
943:
954: IF ((x_header_record.header_record.asn_type in ('ASN','ASBN')) AND
955: (x_header_record.header_record.receipt_num is NULL) AND
956: (x_header_record.error_record.error_status IN('S', 'W'))
957: ) THEN --{
958: IF (g_asn_debug = 'Y') THEN
959: asn_debug.put_line('ASN or ASBN Transaction');
960: END IF;
961:
962: SELECT count(*)
955: (x_header_record.header_record.receipt_num is NULL) AND
956: (x_header_record.error_record.error_status IN('S', 'W'))
957: ) THEN --{
958: IF (g_asn_debug = 'Y') THEN
959: asn_debug.put_line('ASN or ASBN Transaction');
960: END IF;
961:
962: SELECT count(*)
963: INTO l_drop_ship_exists
966: WHERE rti.header_interface_id = x_header_record.header_record.header_interface_id
967: and rti.po_line_location_id = plla.line_location_id
968: and plla.drop_ship_flag = 'Y';
969:
970: IF (g_asn_debug = 'Y') THEN
971: asn_debug.put_line('Number of Drop Ship Lines:' || l_drop_ship_exists);
972: END IF;
973: IF l_drop_ship_exists > 0 THEN --{
974: FND_PROFILE.GET('PO_AUTO_DELIVER_DROPSHIP_ASN', l_auto_deliver);
967: and rti.po_line_location_id = plla.line_location_id
968: and plla.drop_ship_flag = 'Y';
969:
970: IF (g_asn_debug = 'Y') THEN
971: asn_debug.put_line('Number of Drop Ship Lines:' || l_drop_ship_exists);
972: END IF;
973: IF l_drop_ship_exists > 0 THEN --{
974: FND_PROFILE.GET('PO_AUTO_DELIVER_DROPSHIP_ASN', l_auto_deliver);
975: IF (g_asn_debug = 'Y') THEN
971: asn_debug.put_line('Number of Drop Ship Lines:' || l_drop_ship_exists);
972: END IF;
973: IF l_drop_ship_exists > 0 THEN --{
974: FND_PROFILE.GET('PO_AUTO_DELIVER_DROPSHIP_ASN', l_auto_deliver);
975: IF (g_asn_debug = 'Y') THEN
976: asn_debug.put_line('Profile Option PO_AUTO_DELIVER_DROPSHIP_ASN:' || l_auto_deliver);
977: END IF;
978: IF l_auto_deliver = 'Y' THEN --{
979: IF (g_asn_debug = 'Y') THEN
972: END IF;
973: IF l_drop_ship_exists > 0 THEN --{
974: FND_PROFILE.GET('PO_AUTO_DELIVER_DROPSHIP_ASN', l_auto_deliver);
975: IF (g_asn_debug = 'Y') THEN
976: asn_debug.put_line('Profile Option PO_AUTO_DELIVER_DROPSHIP_ASN:' || l_auto_deliver);
977: END IF;
978: IF l_auto_deliver = 'Y' THEN --{
979: IF (g_asn_debug = 'Y') THEN
980: asn_debug.put_line('Generate Receipt Number');
975: IF (g_asn_debug = 'Y') THEN
976: asn_debug.put_line('Profile Option PO_AUTO_DELIVER_DROPSHIP_ASN:' || l_auto_deliver);
977: END IF;
978: IF l_auto_deliver = 'Y' THEN --{
979: IF (g_asn_debug = 'Y') THEN
980: asn_debug.put_line('Generate Receipt Number');
981: END IF;
982:
983: RCV_ROI_HEADER_COMMON.default_receipt_info(x_header_record);
976: asn_debug.put_line('Profile Option PO_AUTO_DELIVER_DROPSHIP_ASN:' || l_auto_deliver);
977: END IF;
978: IF l_auto_deliver = 'Y' THEN --{
979: IF (g_asn_debug = 'Y') THEN
980: asn_debug.put_line('Generate Receipt Number');
981: END IF;
982:
983: RCV_ROI_HEADER_COMMON.default_receipt_info(x_header_record);
984:
981: END IF;
982:
983: RCV_ROI_HEADER_COMMON.default_receipt_info(x_header_record);
984:
985: IF (g_asn_debug = 'Y') THEN
986: asn_debug.put_line('Generated Receipt Number:' || x_header_record.header_record.receipt_num);
987: END IF;
988:
989: UPDATE RCV_SHIPMENT_HEADERS
982:
983: RCV_ROI_HEADER_COMMON.default_receipt_info(x_header_record);
984:
985: IF (g_asn_debug = 'Y') THEN
986: asn_debug.put_line('Generated Receipt Number:' || x_header_record.header_record.receipt_num);
987: END IF;
988:
989: UPDATE RCV_SHIPMENT_HEADERS
990: SET RECEIPT_NUM = x_header_record.header_record.receipt_num
1002:
1003: l_ship_header_id := nvl(x_cascaded_table(n).shipment_header_id,
1004: x_header_record.header_record.receipt_header_id);
1005:
1006: IF (g_asn_debug = 'Y') THEN
1007: asn_debug.put_line('Erroring out RHI/RTI');
1008: asn_debug.put_line('shipment_header_id : '|| l_ship_header_id);
1009: END IF;
1010: /* Bug 4779020. A line is ASN/Non ASN is decided by transaction tye */
1003: l_ship_header_id := nvl(x_cascaded_table(n).shipment_header_id,
1004: x_header_record.header_record.receipt_header_id);
1005:
1006: IF (g_asn_debug = 'Y') THEN
1007: asn_debug.put_line('Erroring out RHI/RTI');
1008: asn_debug.put_line('shipment_header_id : '|| l_ship_header_id);
1009: END IF;
1010: /* Bug 4779020. A line is ASN/Non ASN is decided by transaction tye */
1011: /* need to check auto_transact_code as well to exclude inv shipments */
1004: x_header_record.header_record.receipt_header_id);
1005:
1006: IF (g_asn_debug = 'Y') THEN
1007: asn_debug.put_line('Erroring out RHI/RTI');
1008: asn_debug.put_line('shipment_header_id : '|| l_ship_header_id);
1009: END IF;
1010: /* Bug 4779020. A line is ASN/Non ASN is decided by transaction tye */
1011: /* need to check auto_transact_code as well to exclude inv shipments */
1012: /* Bug 7684677 rcv:fail all asn lines if one line fails doesn't work for ship */
1018: -- delete rsh and rsl
1019: DELETE FROM rcv_shipment_headers
1020: WHERE shipment_header_id = l_ship_header_id;
1021:
1022: IF (g_asn_debug = 'Y') THEN
1023: asn_debug.put_line(sql%rowcount || ' RSH record deleted');
1024: END IF;
1025:
1026: DELETE FROM rcv_shipment_lines
1019: DELETE FROM rcv_shipment_headers
1020: WHERE shipment_header_id = l_ship_header_id;
1021:
1022: IF (g_asn_debug = 'Y') THEN
1023: asn_debug.put_line(sql%rowcount || ' RSH record deleted');
1024: END IF;
1025:
1026: DELETE FROM rcv_shipment_lines
1027: WHERE shipment_header_id = l_ship_header_id;
1025:
1026: DELETE FROM rcv_shipment_lines
1027: WHERE shipment_header_id = l_ship_header_id;
1028:
1029: IF (g_asn_debug = 'Y') THEN
1030: asn_debug.put_line(sql%rowcount || ' RSL record deleted');
1031: END IF;
1032:
1033: -- update rti
1026: DELETE FROM rcv_shipment_lines
1027: WHERE shipment_header_id = l_ship_header_id;
1028:
1029: IF (g_asn_debug = 'Y') THEN
1030: asn_debug.put_line(sql%rowcount || ' RSL record deleted');
1031: END IF;
1032:
1033: -- update rti
1034: IF (g_asn_debug = 'Y') THEN
1030: asn_debug.put_line(sql%rowcount || ' RSL record deleted');
1031: END IF;
1032:
1033: -- update rti
1034: IF (g_asn_debug = 'Y') THEN
1035: asn_debug.put_line('update_rti_error for a fail all ASN transaction ');
1036: END IF;
1037:
1038: update_rti_error(p_group_id => x_cascaded_table(n).group_id,
1031: END IF;
1032:
1033: -- update rti
1034: IF (g_asn_debug = 'Y') THEN
1035: asn_debug.put_line('update_rti_error for a fail all ASN transaction ');
1036: END IF;
1037:
1038: update_rti_error(p_group_id => x_cascaded_table(n).group_id,
1039: p_interface_id => NULL,
1041: p_lpn_group_id => NULL
1042: );
1043:
1044: /* Bug 4779020 .Update the RHI to error.Exit the Loop to prevent processing the line */
1045: IF (g_asn_debug = 'Y') THEN
1046: asn_debug.put_line('error out rhi for a fail all ASN transaction ');
1047: END IF;
1048:
1049: UPDATE rcv_headers_interface
1042: );
1043:
1044: /* Bug 4779020 .Update the RHI to error.Exit the Loop to prevent processing the line */
1045: IF (g_asn_debug = 'Y') THEN
1046: asn_debug.put_line('error out rhi for a fail all ASN transaction ');
1047: END IF;
1048:
1049: UPDATE rcv_headers_interface
1050: SET processing_status_code = 'ERROR',
1055: x_header_record.error_record.error_status := 'E';
1056:
1057: /* Bug 4779020 End */
1058: ELSE --}{ not an asn with fail all option turned on
1059: IF (g_asn_debug = 'Y') THEN
1060: asn_debug.put_line('update_rti_error for an non fail all ASN transaction ');
1061: END IF;
1062:
1063: update_rti_error(p_group_id => x_cascaded_table(n).group_id,
1056:
1057: /* Bug 4779020 End */
1058: ELSE --}{ not an asn with fail all option turned on
1059: IF (g_asn_debug = 'Y') THEN
1060: asn_debug.put_line('update_rti_error for an non fail all ASN transaction ');
1061: END IF;
1062:
1063: update_rti_error(p_group_id => x_cascaded_table(n).group_id,
1064: p_interface_id => x_cascaded_table(n).interface_transaction_id,
1066: p_lpn_group_id => x_cascaded_table(n).lpn_group_id
1067: );
1068:
1069: -- update the rhi to error if all of its line processing failed
1070: IF (g_asn_debug = 'Y') THEN
1071: asn_debug.put_line('update rhi for non fail-all-ASN transaction ');
1072: END IF;
1073:
1074: -- Bug 9268956
1067: );
1068:
1069: -- update the rhi to error if all of its line processing failed
1070: IF (g_asn_debug = 'Y') THEN
1071: asn_debug.put_line('update rhi for non fail-all-ASN transaction ');
1072: END IF;
1073:
1074: -- Bug 9268956
1075: IF (NVL(x_header_record.header_record.asn_type, 'STD') = 'STD' AND x_header_record.header_record.receipt_source_code = 'VENDOR'
1075: IF (NVL(x_header_record.header_record.asn_type, 'STD') = 'STD' AND x_header_record.header_record.receipt_source_code = 'VENDOR'
1076: AND (x_header_record.header_record.shipment_num IS NOT NULL AND x_header_record.header_record.receipt_header_id IS NOT NULL)) THEN
1077: IF (x_cascaded_table(n).transaction_type = 'RECEIVE' OR l_transaction_type_old = 'RECEIVE')
1078: AND x_cascaded_table(n).auto_transact_code in ('RECEIVE','DELIVER') THEN
1079: IF (g_asn_debug = 'Y') THEN
1080: asn_debug.put_line('Rollback receipt num if new receipt against ASN/ASBN failed.');
1081: asn_debug.put_line('Shipment num: ' || x_header_record.header_record.shipment_num);
1082: asn_debug.put_line('Receipt header id: ' || x_header_record.header_record.receipt_header_id);
1083: asn_debug.put_line('Transaction type: ' || x_cascaded_table(n).transaction_type);
1076: AND (x_header_record.header_record.shipment_num IS NOT NULL AND x_header_record.header_record.receipt_header_id IS NOT NULL)) THEN
1077: IF (x_cascaded_table(n).transaction_type = 'RECEIVE' OR l_transaction_type_old = 'RECEIVE')
1078: AND x_cascaded_table(n).auto_transact_code in ('RECEIVE','DELIVER') THEN
1079: IF (g_asn_debug = 'Y') THEN
1080: asn_debug.put_line('Rollback receipt num if new receipt against ASN/ASBN failed.');
1081: asn_debug.put_line('Shipment num: ' || x_header_record.header_record.shipment_num);
1082: asn_debug.put_line('Receipt header id: ' || x_header_record.header_record.receipt_header_id);
1083: asn_debug.put_line('Transaction type: ' || x_cascaded_table(n).transaction_type);
1084: asn_debug.put_line('l_transaction_type_old: ' || l_transaction_type_old);
1077: IF (x_cascaded_table(n).transaction_type = 'RECEIVE' OR l_transaction_type_old = 'RECEIVE')
1078: AND x_cascaded_table(n).auto_transact_code in ('RECEIVE','DELIVER') THEN
1079: IF (g_asn_debug = 'Y') THEN
1080: asn_debug.put_line('Rollback receipt num if new receipt against ASN/ASBN failed.');
1081: asn_debug.put_line('Shipment num: ' || x_header_record.header_record.shipment_num);
1082: asn_debug.put_line('Receipt header id: ' || x_header_record.header_record.receipt_header_id);
1083: asn_debug.put_line('Transaction type: ' || x_cascaded_table(n).transaction_type);
1084: asn_debug.put_line('l_transaction_type_old: ' || l_transaction_type_old);
1085: asn_debug.put_line('Auto transact code: ' || x_cascaded_table(n).auto_transact_code);
1078: AND x_cascaded_table(n).auto_transact_code in ('RECEIVE','DELIVER') THEN
1079: IF (g_asn_debug = 'Y') THEN
1080: asn_debug.put_line('Rollback receipt num if new receipt against ASN/ASBN failed.');
1081: asn_debug.put_line('Shipment num: ' || x_header_record.header_record.shipment_num);
1082: asn_debug.put_line('Receipt header id: ' || x_header_record.header_record.receipt_header_id);
1083: asn_debug.put_line('Transaction type: ' || x_cascaded_table(n).transaction_type);
1084: asn_debug.put_line('l_transaction_type_old: ' || l_transaction_type_old);
1085: asn_debug.put_line('Auto transact code: ' || x_cascaded_table(n).auto_transact_code);
1086: END IF;
1079: IF (g_asn_debug = 'Y') THEN
1080: asn_debug.put_line('Rollback receipt num if new receipt against ASN/ASBN failed.');
1081: asn_debug.put_line('Shipment num: ' || x_header_record.header_record.shipment_num);
1082: asn_debug.put_line('Receipt header id: ' || x_header_record.header_record.receipt_header_id);
1083: asn_debug.put_line('Transaction type: ' || x_cascaded_table(n).transaction_type);
1084: asn_debug.put_line('l_transaction_type_old: ' || l_transaction_type_old);
1085: asn_debug.put_line('Auto transact code: ' || x_cascaded_table(n).auto_transact_code);
1086: END IF;
1087:
1080: asn_debug.put_line('Rollback receipt num if new receipt against ASN/ASBN failed.');
1081: asn_debug.put_line('Shipment num: ' || x_header_record.header_record.shipment_num);
1082: asn_debug.put_line('Receipt header id: ' || x_header_record.header_record.receipt_header_id);
1083: asn_debug.put_line('Transaction type: ' || x_cascaded_table(n).transaction_type);
1084: asn_debug.put_line('l_transaction_type_old: ' || l_transaction_type_old);
1085: asn_debug.put_line('Auto transact code: ' || x_cascaded_table(n).auto_transact_code);
1086: END IF;
1087:
1088: UPDATE rcv_shipment_headers
1081: asn_debug.put_line('Shipment num: ' || x_header_record.header_record.shipment_num);
1082: asn_debug.put_line('Receipt header id: ' || x_header_record.header_record.receipt_header_id);
1083: asn_debug.put_line('Transaction type: ' || x_cascaded_table(n).transaction_type);
1084: asn_debug.put_line('l_transaction_type_old: ' || l_transaction_type_old);
1085: asn_debug.put_line('Auto transact code: ' || x_cascaded_table(n).auto_transact_code);
1086: END IF;
1087:
1088: UPDATE rcv_shipment_headers
1089: SET receipt_num = NULL
1098: WHERE rhi.header_interface_id = rti.header_interface_id
1099: AND rti.processing_status_code in ('RUNNING', 'PENDING')
1100: AND rhi.receipt_header_id = x_header_record.header_record.receipt_header_id);
1101:
1102: IF (g_asn_debug = 'Y') THEN
1103: asn_debug.put_line(sql%rowcount || ' RSH updated.');
1104: END IF;
1105: END IF;
1106: END IF;
1099: AND rti.processing_status_code in ('RUNNING', 'PENDING')
1100: AND rhi.receipt_header_id = x_header_record.header_record.receipt_header_id);
1101:
1102: IF (g_asn_debug = 'Y') THEN
1103: asn_debug.put_line(sql%rowcount || ' RSH updated.');
1104: END IF;
1105: END IF;
1106: END IF;
1107: -- End bug 9268956
1116: FROM rcv_transactions_interface rti
1117: WHERE rhi.header_interface_id = rti.header_interface_id
1118: AND rti.processing_status_code in ('RUNNING', 'PENDING'));
1119:
1120: IF (g_asn_debug = 'Y') THEN
1121: asn_debug.put_line(sql%rowcount || ' RHI record updated to error. ');
1122: END IF;
1123:
1124: -- Bug 13259799
1117: WHERE rhi.header_interface_id = rti.header_interface_id
1118: AND rti.processing_status_code in ('RUNNING', 'PENDING'));
1119:
1120: IF (g_asn_debug = 'Y') THEN
1121: asn_debug.put_line(sql%rowcount || ' RHI record updated to error. ');
1122: END IF;
1123:
1124: -- Bug 13259799
1125: IF (sql%rowcount > 0) THEN
1145: WHERE rhi.header_interface_id = rti.header_interface_id
1146: AND rti.processing_status_code in ('RUNNING', 'PENDING')
1147: AND rhi.receipt_header_id = l_ship_header_id );
1148:
1149: IF (g_asn_debug = 'Y') THEN
1150: asn_debug.put_line(sql%rowcount || ' rsh record deleted');
1151: END IF;
1152:
1153: END IF; --} not asn and fail all case
1146: AND rti.processing_status_code in ('RUNNING', 'PENDING')
1147: AND rhi.receipt_header_id = l_ship_header_id );
1148:
1149: IF (g_asn_debug = 'Y') THEN
1150: asn_debug.put_line(sql%rowcount || ' rsh record deleted');
1151: END IF;
1152:
1153: END IF; --} not asn and fail all case
1154:
1196: /* End 4355172 */
1197:
1198:
1199: CLOSE rcv_roi_preprocessor.txns_cur;
1200: asn_debug.put_line('after loop');
1201:
1202: /* Bug 8831292
1203: * Need to call 824 Interface to insert records into ECE_ADVO_HEADERS
1204: * and ECE_ADVO_DETAILS for errored out transactions if it was an ASN
1205: * import and EDI is installed. Data in these 2 tables will be extracted
1206: * to generate outbound 824 Application Advice.*/
1207:
1208: IF g_is_edi_installed = 'I' THEN
1209: IF (g_asn_debug = 'Y') THEN
1210: asn_debug.put_line('EDI installed. Checking for errored ASNs');
1211: END IF;
1212:
1213: OPEN errored_asn_rhi_cursor;
1206: * to generate outbound 824 Application Advice.*/
1207:
1208: IF g_is_edi_installed = 'I' THEN
1209: IF (g_asn_debug = 'Y') THEN
1210: asn_debug.put_line('EDI installed. Checking for errored ASNs');
1211: END IF;
1212:
1213: OPEN errored_asn_rhi_cursor;
1214: LOOP
1213: OPEN errored_asn_rhi_cursor;
1214: LOOP
1215: FETCH errored_asn_rhi_cursor INTO x_asn_rhi_record.header_record;
1216: EXIT WHEN errored_asn_rhi_cursor%NOTFOUND;
1217: IF (g_asn_debug = 'Y') THEN
1218: asn_debug.put_line('Calling 824 API for rhi: ' || x_asn_rhi_record.header_record.header_interface_id);
1219: END IF;
1220: rcv_824_sv.rcv_824_insert(x_asn_rhi_record, 'ASN');
1221: END LOOP;
1214: LOOP
1215: FETCH errored_asn_rhi_cursor INTO x_asn_rhi_record.header_record;
1216: EXIT WHEN errored_asn_rhi_cursor%NOTFOUND;
1217: IF (g_asn_debug = 'Y') THEN
1218: asn_debug.put_line('Calling 824 API for rhi: ' || x_asn_rhi_record.header_record.header_interface_id);
1219: END IF;
1220: rcv_824_sv.rcv_824_insert(x_asn_rhi_record, 'ASN');
1221: END LOOP;
1222: CLOSE errored_asn_rhi_cursor;
1223: END IF;
1224:
1225: --DCP call
1226: BEGIN
1227: IF (g_asn_debug = 'Y') THEN
1228: asn_debug.put_line('l_check_dcp ' || l_check_dcp);
1229: asn_debug.put_line('g_check_dcp ' || rcv_dcp_pvt.g_check_dcp);
1230: END IF;
1231:
1224:
1225: --DCP call
1226: BEGIN
1227: IF (g_asn_debug = 'Y') THEN
1228: asn_debug.put_line('l_check_dcp ' || l_check_dcp);
1229: asn_debug.put_line('g_check_dcp ' || rcv_dcp_pvt.g_check_dcp);
1230: END IF;
1231:
1232: IF l_check_dcp > 0 THEN
1225: --DCP call
1226: BEGIN
1227: IF (g_asn_debug = 'Y') THEN
1228: asn_debug.put_line('l_check_dcp ' || l_check_dcp);
1229: asn_debug.put_line('g_check_dcp ' || rcv_dcp_pvt.g_check_dcp);
1230: END IF;
1231:
1232: IF l_check_dcp > 0 THEN
1233: -- Moved the driving cursor to the DCP package itself
1236: --END LOOP;
1237: END IF;
1238: EXCEPTION
1239: WHEN rcv_dcp_pvt.data_inconsistency_exception THEN
1240: IF (g_asn_debug = 'Y') THEN
1241: asn_debug.put_line('Data Inconsistency Exception');
1242: END IF;
1243: IF distinct_org_id%ISOPEN THEN
1244: CLOSE distinct_org_id;
1237: END IF;
1238: EXCEPTION
1239: WHEN rcv_dcp_pvt.data_inconsistency_exception THEN
1240: IF (g_asn_debug = 'Y') THEN
1241: asn_debug.put_line('Data Inconsistency Exception');
1242: END IF;
1243: IF distinct_org_id%ISOPEN THEN
1244: CLOSE distinct_org_id;
1245: END IF;
1250:
1251: ROLLBACK TO dcp_preprocessor_start;
1252: GOTO dcp_pre_processor_start;
1253: WHEN OTHERS THEN
1254: IF (g_asn_debug = 'Y') THEN
1255: asn_debug.put_line('When Others ' || SQLERRM);
1256: END IF;
1257: NULL;
1258: END;
1251: ROLLBACK TO dcp_preprocessor_start;
1252: GOTO dcp_pre_processor_start;
1253: WHEN OTHERS THEN
1254: IF (g_asn_debug = 'Y') THEN
1255: asn_debug.put_line('When Others ' || SQLERRM);
1256: END IF;
1257: NULL;
1258: END;
1259: --End DCP call
1257: NULL;
1258: END;
1259: --End DCP call
1260:
1261: IF (g_asn_debug = 'Y') THEN
1262: asn_debug.put_line('Exit preprocessor');
1263: END IF;
1264: EXCEPTION
1265: WHEN NO_DATA_FOUND THEN
1258: END;
1259: --End DCP call
1260:
1261: IF (g_asn_debug = 'Y') THEN
1262: asn_debug.put_line('Exit preprocessor');
1263: END IF;
1264: EXCEPTION
1265: WHEN NO_DATA_FOUND THEN
1266: rcv_error_pkg.set_sql_error_message('RCV_ROI_PREPROCESSOR.preprocessor','sqlcode');
1274: IF rcv_roi_preprocessor.headers_cur%ISOPEN THEN
1275: CLOSE rcv_roi_preprocessor.headers_cur;
1276: END IF;
1277: WHEN rcv_error_pkg.e_fatal_error THEN --we didn't catch an error that we should have caught
1278: asn_debug.put_line('uncaught e_fatal_error in rcv_roi_preprocess.preprocessor - abnormal execution');
1279: asn_debug.put_line('last error message = ' || rcv_error_pkg.get_last_message);
1280:
1281: rcv_error_pkg.set_sql_error_message('RCV_ROI_PREPROCESSOR.preprocessor','sqlcode'); -- Bug 13093917
1282: rcv_error_pkg.log_interface_error('INTERFACE_TRANSACTION_ID', FALSE); -- Bug 13093917
1275: CLOSE rcv_roi_preprocessor.headers_cur;
1276: END IF;
1277: WHEN rcv_error_pkg.e_fatal_error THEN --we didn't catch an error that we should have caught
1278: asn_debug.put_line('uncaught e_fatal_error in rcv_roi_preprocess.preprocessor - abnormal execution');
1279: asn_debug.put_line('last error message = ' || rcv_error_pkg.get_last_message);
1280:
1281: rcv_error_pkg.set_sql_error_message('RCV_ROI_PREPROCESSOR.preprocessor','sqlcode'); -- Bug 13093917
1282: rcv_error_pkg.log_interface_error('INTERFACE_TRANSACTION_ID', FALSE); -- Bug 13093917
1283:
1281: rcv_error_pkg.set_sql_error_message('RCV_ROI_PREPROCESSOR.preprocessor','sqlcode'); -- Bug 13093917
1282: rcv_error_pkg.log_interface_error('INTERFACE_TRANSACTION_ID', FALSE); -- Bug 13093917
1283:
1284: WHEN OTHERS THEN
1285: IF (g_asn_debug = 'Y') THEN
1286: asn_debug.put_line('Exception in preprocessor:');
1287: asn_debug.put_line('sqlerrm: ' || SQLERRM);
1288: asn_debug.put_line('l_msg_count: ' || l_msg_count);
1289: asn_debug.put_line('l_msg_data: ' || l_msg_data);
1282: rcv_error_pkg.log_interface_error('INTERFACE_TRANSACTION_ID', FALSE); -- Bug 13093917
1283:
1284: WHEN OTHERS THEN
1285: IF (g_asn_debug = 'Y') THEN
1286: asn_debug.put_line('Exception in preprocessor:');
1287: asn_debug.put_line('sqlerrm: ' || SQLERRM);
1288: asn_debug.put_line('l_msg_count: ' || l_msg_count);
1289: asn_debug.put_line('l_msg_data: ' || l_msg_data);
1290: asn_debug.put_line('Set rti rows to error for this and call txn complete');
1283:
1284: WHEN OTHERS THEN
1285: IF (g_asn_debug = 'Y') THEN
1286: asn_debug.put_line('Exception in preprocessor:');
1287: asn_debug.put_line('sqlerrm: ' || SQLERRM);
1288: asn_debug.put_line('l_msg_count: ' || l_msg_count);
1289: asn_debug.put_line('l_msg_data: ' || l_msg_data);
1290: asn_debug.put_line('Set rti rows to error for this and call txn complete');
1291: END IF;
1284: WHEN OTHERS THEN
1285: IF (g_asn_debug = 'Y') THEN
1286: asn_debug.put_line('Exception in preprocessor:');
1287: asn_debug.put_line('sqlerrm: ' || SQLERRM);
1288: asn_debug.put_line('l_msg_count: ' || l_msg_count);
1289: asn_debug.put_line('l_msg_data: ' || l_msg_data);
1290: asn_debug.put_line('Set rti rows to error for this and call txn complete');
1291: END IF;
1292:
1285: IF (g_asn_debug = 'Y') THEN
1286: asn_debug.put_line('Exception in preprocessor:');
1287: asn_debug.put_line('sqlerrm: ' || SQLERRM);
1288: asn_debug.put_line('l_msg_count: ' || l_msg_count);
1289: asn_debug.put_line('l_msg_data: ' || l_msg_data);
1290: asn_debug.put_line('Set rti rows to error for this and call txn complete');
1291: END IF;
1292:
1293: rcv_error_pkg.set_sql_error_message('RCV_ROI_PREPROCESSOR.preprocessor','sqlcode'); -- Bug 13093917
1286: asn_debug.put_line('Exception in preprocessor:');
1287: asn_debug.put_line('sqlerrm: ' || SQLERRM);
1288: asn_debug.put_line('l_msg_count: ' || l_msg_count);
1289: asn_debug.put_line('l_msg_data: ' || l_msg_data);
1290: asn_debug.put_line('Set rti rows to error for this and call txn complete');
1291: END IF;
1292:
1293: rcv_error_pkg.set_sql_error_message('RCV_ROI_PREPROCESSOR.preprocessor','sqlcode'); -- Bug 13093917
1294: rcv_error_pkg.log_interface_error('INTERFACE_TRANSACTION_ID', FALSE); -- Bug 13093917
1304:
1305: /*We default p_group_id to 0 */
1306: IF ( p_group_id IS NOT NULL
1307: AND p_group_id <> 0) THEN
1308: IF (g_asn_debug = 'Y') THEN
1309: asn_debug.put_line('update_rti_error in exception with group_id ');
1310: END IF;
1311:
1312: update_rti_error(p_group_id => p_group_id,
1305: /*We default p_group_id to 0 */
1306: IF ( p_group_id IS NOT NULL
1307: AND p_group_id <> 0) THEN
1308: IF (g_asn_debug = 'Y') THEN
1309: asn_debug.put_line('update_rti_error in exception with group_id ');
1310: END IF;
1311:
1312: update_rti_error(p_group_id => p_group_id,
1313: p_interface_id => NULL,
1321: LOOP
1322: FETCH distinct_groups INTO l_exception_group_id(n);
1323: EXIT WHEN distinct_groups%NOTFOUND;
1324:
1325: IF (g_asn_debug = 'Y') THEN
1326: asn_debug.put_line('update_rti_error in exception with request_id ');
1327: END IF;
1328:
1329: update_rti_error(p_group_id => l_exception_group_id(n),
1322: FETCH distinct_groups INTO l_exception_group_id(n);
1323: EXIT WHEN distinct_groups%NOTFOUND;
1324:
1325: IF (g_asn_debug = 'Y') THEN
1326: asn_debug.put_line('update_rti_error in exception with request_id ');
1327: END IF;
1328:
1329: update_rti_error(p_group_id => l_exception_group_id(n),
1330: p_interface_id => NULL,
1729: l_msg_count NUMBER;
1730: l_msg_data fnd_new_messages.MESSAGE_TEXT%TYPE;
1731: l_to_org_id rcv_transactions_interface.to_organization_id%TYPE;
1732: BEGIN
1733: IF (g_asn_debug = 'Y') THEN
1734: asn_debug.put_line('Enter create shipment line');
1735: END IF;
1736:
1737: IF (g_asn_debug = 'Y') THEN
1730: l_msg_data fnd_new_messages.MESSAGE_TEXT%TYPE;
1731: l_to_org_id rcv_transactions_interface.to_organization_id%TYPE;
1732: BEGIN
1733: IF (g_asn_debug = 'Y') THEN
1734: asn_debug.put_line('Enter create shipment line');
1735: END IF;
1736:
1737: IF (g_asn_debug = 'Y') THEN
1738: asn_debug.put_line('Initialize the table structure used for storing the cascaded rows' || x_cascaded_table(n).transaction_type);
1733: IF (g_asn_debug = 'Y') THEN
1734: asn_debug.put_line('Enter create shipment line');
1735: END IF;
1736:
1737: IF (g_asn_debug = 'Y') THEN
1738: asn_debug.put_line('Initialize the table structure used for storing the cascaded rows' || x_cascaded_table(n).transaction_type);
1739: asn_debug.put_line('receipt source code' || x_cascaded_table(n).receipt_source_code);
1740: END IF;
1741:
1734: asn_debug.put_line('Enter create shipment line');
1735: END IF;
1736:
1737: IF (g_asn_debug = 'Y') THEN
1738: asn_debug.put_line('Initialize the table structure used for storing the cascaded rows' || x_cascaded_table(n).transaction_type);
1739: asn_debug.put_line('receipt source code' || x_cascaded_table(n).receipt_source_code);
1740: END IF;
1741:
1742: -- delete all records from used_for_cascaded_rows
1735: END IF;
1736:
1737: IF (g_asn_debug = 'Y') THEN
1738: asn_debug.put_line('Initialize the table structure used for storing the cascaded rows' || x_cascaded_table(n).transaction_type);
1739: asn_debug.put_line('receipt source code' || x_cascaded_table(n).receipt_source_code);
1740: END IF;
1741:
1742: -- delete all records from used_for_cascaded_rows
1743: used_for_cascaded_rows.DELETE;
1760: used_for_cascaded_rows,
1761: v_header_record
1762: );
1763: ELSIF(x_cascaded_table(n).transaction_type IN('TRANSFER', 'ACCEPT', 'REJECT', 'DELIVER')) THEN
1764: asn_debug.put_line('calling derive routine for transaction ' || x_cascaded_table(n).transaction_type);
1765: rcv_roi_transaction.derive_vendor_trans_del(x_cascaded_table,
1766: n,
1767: used_for_cascaded_rows,
1768: v_header_record
1767: used_for_cascaded_rows,
1768: v_header_record
1769: );
1770: ELSIF(x_cascaded_table(n).transaction_type =('CORRECT')) THEN
1771: asn_debug.put_line('calling derive routine for transaction ' || x_cascaded_table(n).transaction_type);
1772: rcv_roi_transaction.derive_correction_line(x_cascaded_table,
1773: n,
1774: used_for_cascaded_rows,
1775: v_header_record
1774: used_for_cascaded_rows,
1775: v_header_record
1776: );
1777: ELSIF(x_cascaded_table(n).transaction_type = 'RETURN TO VENDOR') THEN
1778: asn_debug.put_line('calling derive routine for transaction ' || x_cascaded_table(n).transaction_type);
1779: rcv_roi_return.derive_return_line(x_cascaded_table,
1780: n,
1781: used_for_cascaded_rows,
1782: v_header_record
1781: used_for_cascaded_rows,
1782: v_header_record
1783: );
1784: ELSIF(x_cascaded_table(n).transaction_type = 'RETURN TO RECEIVING') THEN
1785: asn_debug.put_line('calling derive routine for transaction ' || x_cascaded_table(n).transaction_type);
1786: rcv_roi_return.derive_return_line(x_cascaded_table,
1787: n,
1788: used_for_cascaded_rows,
1789: v_header_record
1788: used_for_cascaded_rows,
1789: v_header_record
1790: );
1791: ELSE
1792: asn_debug.put_line('We do not support transaction type ' || x_cascaded_table(n).transaction_type);
1793: rcv_error_pkg.set_error_message('RCV_INVALID_TRANSACTION_TYPE');
1794: rcv_error_pkg.log_interface_error('TRANSACTION_TYPE');
1795: END IF;
1796: ELSIF(x_cascaded_table(n).receipt_source_code = 'INTERNAL ORDER') THEN
1794: rcv_error_pkg.log_interface_error('TRANSACTION_TYPE');
1795: END IF;
1796: ELSIF(x_cascaded_table(n).receipt_source_code = 'INTERNAL ORDER') THEN
1797: IF (x_cascaded_table(n).transaction_type = 'RECEIVE') THEN
1798: asn_debug.put_line('calling derive routine for transaction ' || x_cascaded_table(n).transaction_type);
1799: rcv_int_order_pp_pvt.derive_io_receive_line(x_cascaded_table,
1800: n,
1801: used_for_cascaded_rows,
1802: v_header_record
1801: used_for_cascaded_rows,
1802: v_header_record
1803: );
1804: ELSIF(x_cascaded_table(n).transaction_type IN('TRANSFER', 'ACCEPT', 'REJECT', 'DELIVER')) THEN
1805: asn_debug.put_line('calling derive routine for transaction ' || x_cascaded_table(n).transaction_type);
1806: rcv_int_order_pp_pvt.derive_io_trans_line(x_cascaded_table,
1807: n,
1808: used_for_cascaded_rows,
1809: v_header_record
1808: used_for_cascaded_rows,
1809: v_header_record
1810: );
1811: ELSIF(x_cascaded_table(n).transaction_type =('CORRECT')) THEN
1812: asn_debug.put_line('calling derive routine for transaction ' || x_cascaded_table(n).transaction_type);
1813: rcv_int_order_pp_pvt.derive_io_correct_line(x_cascaded_table,
1814: n,
1815: used_for_cascaded_rows,
1816: v_header_record
1815: used_for_cascaded_rows,
1816: v_header_record
1817: );
1818: ELSE
1819: asn_debug.put_line('We do not support transaction type ' || x_cascaded_table(n).transaction_type);
1820: rcv_error_pkg.set_error_message('RCV_INVALID_TRANSACTION_TYPE');
1821: rcv_error_pkg.log_interface_error('TRANSACTION_TYPE');
1822: END IF; -- IF INTERNAL ORDER
1823: ELSIF(x_cascaded_table(n).receipt_source_code = 'INVENTORY') THEN --} {
1827: used_for_cascaded_rows,
1828: v_header_record
1829: );
1830: ELSIF(x_cascaded_table(n).transaction_type IN('TRANSFER', 'ACCEPT', 'REJECT', 'DELIVER')) THEN
1831: asn_debug.put_line('calling derive routine for transaction ' || x_cascaded_table(n).transaction_type);
1832: rcv_int_org_transfer.derive_int_org_trans_del(x_cascaded_table,
1833: n,
1834: used_for_cascaded_rows,
1835: v_header_record
1834: used_for_cascaded_rows,
1835: v_header_record
1836: );
1837: ELSIF(x_cascaded_table(n).transaction_type =('CORRECT')) THEN
1838: asn_debug.put_line('calling derive routine for transaction ' || x_cascaded_table(n).transaction_type);
1839: rcv_int_org_transfer.derive_int_org_cor_line(x_cascaded_table,
1840: n,
1841: used_for_cascaded_rows,
1842: v_header_record
1841: used_for_cascaded_rows,
1842: v_header_record
1843: );
1844: ELSE
1845: asn_debug.put_line('We do not support transaction type ' || x_cascaded_table(n).transaction_type);
1846: rcv_error_pkg.set_error_message('RCV_INVALID_TRANSACTION_TYPE');
1847: rcv_error_pkg.log_interface_error('TRANSACTION_TYPE');
1848: END IF;
1849: ELSIF(x_cascaded_table(n).receipt_source_code = 'CUSTOMER') THEN --} {
1846: rcv_error_pkg.set_error_message('RCV_INVALID_TRANSACTION_TYPE');
1847: rcv_error_pkg.log_interface_error('TRANSACTION_TYPE');
1848: END IF;
1849: ELSIF(x_cascaded_table(n).receipt_source_code = 'CUSTOMER') THEN --} {
1850: asn_debug.put_line('calling derive routine for RMA transaction ' || x_cascaded_table(n).transaction_type);
1851:
1852: IF (x_cascaded_table(n).transaction_type = 'RECEIVE') THEN --{
1853: rcv_rma_transactions.derive_rma_line(x_cascaded_table,
1854: n,
1873: used_for_cascaded_rows,
1874: v_header_record
1875: );
1876: ELSE
1877: asn_debug.put_line('We do not support transaction type ' || x_cascaded_table(n).transaction_type);
1878: rcv_error_pkg.set_error_message('RCV_INVALID_TRANSACTION_TYPE');
1879: rcv_error_pkg.log_interface_error('TRANSACTION_TYPE');
1880: END IF; --}
1881: ELSE --}{
1878: rcv_error_pkg.set_error_message('RCV_INVALID_TRANSACTION_TYPE');
1879: rcv_error_pkg.log_interface_error('TRANSACTION_TYPE');
1880: END IF; --}
1881: ELSE --}{
1882: asn_debug.put_line('We do not support receipt_source_code ' || x_cascaded_table(n).receipt_source_code);
1883: rcv_error_pkg.set_error_message('RCV_INVALID_TRANSACTION_TYPE');
1884: rcv_error_pkg.log_interface_error('TRANSACTION_TYPE');
1885: END IF; --}
1886: END IF;
1883: rcv_error_pkg.set_error_message('RCV_INVALID_TRANSACTION_TYPE');
1884: rcv_error_pkg.log_interface_error('TRANSACTION_TYPE');
1885: END IF; --}
1886: END IF;
1887: IF (g_asn_debug = 'Y') THEN
1888: asn_debug.put_line('Back from derive routine with ' || TO_CHAR(used_for_cascaded_rows.COUNT) || ' rows');
1889: asn_debug.put_line('Error Status ' || x_cascaded_table(n).error_status);
1890: asn_debug.put_line('Error Message ' || x_cascaded_table(n).error_message);
1891: END IF;
1884: rcv_error_pkg.log_interface_error('TRANSACTION_TYPE');
1885: END IF; --}
1886: END IF;
1887: IF (g_asn_debug = 'Y') THEN
1888: asn_debug.put_line('Back from derive routine with ' || TO_CHAR(used_for_cascaded_rows.COUNT) || ' rows');
1889: asn_debug.put_line('Error Status ' || x_cascaded_table(n).error_status);
1890: asn_debug.put_line('Error Message ' || x_cascaded_table(n).error_message);
1891: END IF;
1892:
1885: END IF; --}
1886: END IF;
1887: IF (g_asn_debug = 'Y') THEN
1888: asn_debug.put_line('Back from derive routine with ' || TO_CHAR(used_for_cascaded_rows.COUNT) || ' rows');
1889: asn_debug.put_line('Error Status ' || x_cascaded_table(n).error_status);
1890: asn_debug.put_line('Error Message ' || x_cascaded_table(n).error_message);
1891: END IF;
1892:
1893: x_progress := '010';
1886: END IF;
1887: IF (g_asn_debug = 'Y') THEN
1888: asn_debug.put_line('Back from derive routine with ' || TO_CHAR(used_for_cascaded_rows.COUNT) || ' rows');
1889: asn_debug.put_line('Error Status ' || x_cascaded_table(n).error_status);
1890: asn_debug.put_line('Error Message ' || x_cascaded_table(n).error_message);
1891: END IF;
1892:
1893: x_progress := '010';
1894:
1894:
1895: IF (x_cascaded_table(n).error_status IN('S', 'W'))
1896: AND used_for_cascaded_rows.COUNT > 0 THEN --{ we have returned with a cascaded table
1897: FOR i IN 1 .. used_for_cascaded_rows.COUNT LOOP --{
1898: IF (g_asn_debug = 'Y') THEN
1899: asn_debug.put_line('Defaulting for cascaded row ' || TO_CHAR(i));
1900: END IF;
1901:
1902: -- default shipment line information
1895: IF (x_cascaded_table(n).error_status IN('S', 'W'))
1896: AND used_for_cascaded_rows.COUNT > 0 THEN --{ we have returned with a cascaded table
1897: FOR i IN 1 .. used_for_cascaded_rows.COUNT LOOP --{
1898: IF (g_asn_debug = 'Y') THEN
1899: asn_debug.put_line('Defaulting for cascaded row ' || TO_CHAR(i));
1900: END IF;
1901:
1902: -- default shipment line information
1903: IF (x_cascaded_table(n).receipt_source_code = 'VENDOR') THEN --{
1907: x_header_id,
1908: v_header_record
1909: );
1910: ELSIF(x_cascaded_table(n).transaction_type IN('TRANSFER', 'ACCEPT', 'REJECT', 'DELIVER')) THEN --}{
1911: asn_debug.put_line('Defaulting for cascaded row ' || TO_CHAR(i));
1912: asn_debug.put_line('Defaulting for cascaded row ' || x_cascaded_table(n).transaction_type);
1913: rcv_roi_transaction.default_vendor_trans_del(used_for_cascaded_rows, i);
1914: ELSIF(x_cascaded_table(n).transaction_type = 'CORRECT') THEN --}{
1915: asn_debug.put_line('Defaulting for cascaded row ' || TO_CHAR(i));
1908: v_header_record
1909: );
1910: ELSIF(x_cascaded_table(n).transaction_type IN('TRANSFER', 'ACCEPT', 'REJECT', 'DELIVER')) THEN --}{
1911: asn_debug.put_line('Defaulting for cascaded row ' || TO_CHAR(i));
1912: asn_debug.put_line('Defaulting for cascaded row ' || x_cascaded_table(n).transaction_type);
1913: rcv_roi_transaction.default_vendor_trans_del(used_for_cascaded_rows, i);
1914: ELSIF(x_cascaded_table(n).transaction_type = 'CORRECT') THEN --}{
1915: asn_debug.put_line('Defaulting for cascaded row ' || TO_CHAR(i));
1916: asn_debug.put_line('Defaulting for cascaded row ' || x_cascaded_table(n).transaction_type);
1911: asn_debug.put_line('Defaulting for cascaded row ' || TO_CHAR(i));
1912: asn_debug.put_line('Defaulting for cascaded row ' || x_cascaded_table(n).transaction_type);
1913: rcv_roi_transaction.default_vendor_trans_del(used_for_cascaded_rows, i);
1914: ELSIF(x_cascaded_table(n).transaction_type = 'CORRECT') THEN --}{
1915: asn_debug.put_line('Defaulting for cascaded row ' || TO_CHAR(i));
1916: asn_debug.put_line('Defaulting for cascaded row ' || x_cascaded_table(n).transaction_type);
1917: rcv_roi_transaction.default_vendor_correct(used_for_cascaded_rows, i);
1918: ELSIF(x_cascaded_table(n).transaction_type = 'RETURN TO VENDOR') THEN --}{
1919: asn_debug.put_line('Defaulting for cascaded row ' || TO_CHAR(i));
1912: asn_debug.put_line('Defaulting for cascaded row ' || x_cascaded_table(n).transaction_type);
1913: rcv_roi_transaction.default_vendor_trans_del(used_for_cascaded_rows, i);
1914: ELSIF(x_cascaded_table(n).transaction_type = 'CORRECT') THEN --}{
1915: asn_debug.put_line('Defaulting for cascaded row ' || TO_CHAR(i));
1916: asn_debug.put_line('Defaulting for cascaded row ' || x_cascaded_table(n).transaction_type);
1917: rcv_roi_transaction.default_vendor_correct(used_for_cascaded_rows, i);
1918: ELSIF(x_cascaded_table(n).transaction_type = 'RETURN TO VENDOR') THEN --}{
1919: asn_debug.put_line('Defaulting for cascaded row ' || TO_CHAR(i));
1920: asn_debug.put_line('Defaulting for cascaded row ' || x_cascaded_table(n).transaction_type);
1915: asn_debug.put_line('Defaulting for cascaded row ' || TO_CHAR(i));
1916: asn_debug.put_line('Defaulting for cascaded row ' || x_cascaded_table(n).transaction_type);
1917: rcv_roi_transaction.default_vendor_correct(used_for_cascaded_rows, i);
1918: ELSIF(x_cascaded_table(n).transaction_type = 'RETURN TO VENDOR') THEN --}{
1919: asn_debug.put_line('Defaulting for cascaded row ' || TO_CHAR(i));
1920: asn_debug.put_line('Defaulting for cascaded row ' || x_cascaded_table(n).transaction_type);
1921: rcv_roi_return.default_return_line(used_for_cascaded_rows, i);
1922: ELSIF(x_cascaded_table(n).transaction_type = 'RETURN TO RECEIVING') THEN --}{
1923: asn_debug.put_line('Defaulting for cascaded row ' || TO_CHAR(i));
1916: asn_debug.put_line('Defaulting for cascaded row ' || x_cascaded_table(n).transaction_type);
1917: rcv_roi_transaction.default_vendor_correct(used_for_cascaded_rows, i);
1918: ELSIF(x_cascaded_table(n).transaction_type = 'RETURN TO VENDOR') THEN --}{
1919: asn_debug.put_line('Defaulting for cascaded row ' || TO_CHAR(i));
1920: asn_debug.put_line('Defaulting for cascaded row ' || x_cascaded_table(n).transaction_type);
1921: rcv_roi_return.default_return_line(used_for_cascaded_rows, i);
1922: ELSIF(x_cascaded_table(n).transaction_type = 'RETURN TO RECEIVING') THEN --}{
1923: asn_debug.put_line('Defaulting for cascaded row ' || TO_CHAR(i));
1924: rcv_roi_return.default_return_line(used_for_cascaded_rows, i);
1919: asn_debug.put_line('Defaulting for cascaded row ' || TO_CHAR(i));
1920: asn_debug.put_line('Defaulting for cascaded row ' || x_cascaded_table(n).transaction_type);
1921: rcv_roi_return.default_return_line(used_for_cascaded_rows, i);
1922: ELSIF(x_cascaded_table(n).transaction_type = 'RETURN TO RECEIVING') THEN --}{
1923: asn_debug.put_line('Defaulting for cascaded row ' || TO_CHAR(i));
1924: rcv_roi_return.default_return_line(used_for_cascaded_rows, i);
1925: END IF; --}
1926: ELSIF(x_cascaded_table(n).receipt_source_code = 'INTERNAL ORDER') THEN
1927: IF (x_cascaded_table(n).transaction_type = 'RECEIVE') THEN
1926: ELSIF(x_cascaded_table(n).receipt_source_code = 'INTERNAL ORDER') THEN
1927: IF (x_cascaded_table(n).transaction_type = 'RECEIVE') THEN
1928: rcv_int_order_pp_pvt.default_io_receive_line(used_for_cascaded_rows, i);
1929: ELSIF(x_cascaded_table(n).transaction_type IN('TRANSFER', 'ACCEPT', 'REJECT', 'DELIVER')) THEN
1930: asn_debug.put_line('Defaulting for cascaded row ' || TO_CHAR(i) || ' TYPE: ' || x_cascaded_table(n).transaction_type);
1931: rcv_int_order_pp_pvt.default_io_trans_line(used_for_cascaded_rows, i);
1932: ELSIF(x_cascaded_table(n).transaction_type = 'CORRECT') THEN
1933: asn_debug.put_line('Defaulting for cascaded row ' || TO_CHAR(i) || ' TYPE: ' || x_cascaded_table(n).transaction_type);
1934: rcv_int_order_pp_pvt.default_io_correct_line(used_for_cascaded_rows, i);
1929: ELSIF(x_cascaded_table(n).transaction_type IN('TRANSFER', 'ACCEPT', 'REJECT', 'DELIVER')) THEN
1930: asn_debug.put_line('Defaulting for cascaded row ' || TO_CHAR(i) || ' TYPE: ' || x_cascaded_table(n).transaction_type);
1931: rcv_int_order_pp_pvt.default_io_trans_line(used_for_cascaded_rows, i);
1932: ELSIF(x_cascaded_table(n).transaction_type = 'CORRECT') THEN
1933: asn_debug.put_line('Defaulting for cascaded row ' || TO_CHAR(i) || ' TYPE: ' || x_cascaded_table(n).transaction_type);
1934: rcv_int_order_pp_pvt.default_io_correct_line(used_for_cascaded_rows, i);
1935: ELSIF(x_cascaded_table(n).transaction_type IN('RETURN TO VENDOR', 'RETURN TO RECEIVING')) THEN
1936: asn_debug.put_line('Defaulting for cascaded row ' || TO_CHAR(i) || ' TYPE: ' || x_cascaded_table(n).transaction_type);
1937: rcv_roi_return.default_return_line(used_for_cascaded_rows, i);
1932: ELSIF(x_cascaded_table(n).transaction_type = 'CORRECT') THEN
1933: asn_debug.put_line('Defaulting for cascaded row ' || TO_CHAR(i) || ' TYPE: ' || x_cascaded_table(n).transaction_type);
1934: rcv_int_order_pp_pvt.default_io_correct_line(used_for_cascaded_rows, i);
1935: ELSIF(x_cascaded_table(n).transaction_type IN('RETURN TO VENDOR', 'RETURN TO RECEIVING')) THEN
1936: asn_debug.put_line('Defaulting for cascaded row ' || TO_CHAR(i) || ' TYPE: ' || x_cascaded_table(n).transaction_type);
1937: rcv_roi_return.default_return_line(used_for_cascaded_rows, i);
1938: END IF; -- FOR default INTERNAL ORDER
1939: ELSIF(x_cascaded_table(n).receipt_source_code = 'INVENTORY') THEN -- } {
1940: IF (x_cascaded_table(n).transaction_type = 'RECEIVE') THEN --{
1939: ELSIF(x_cascaded_table(n).receipt_source_code = 'INVENTORY') THEN -- } {
1940: IF (x_cascaded_table(n).transaction_type = 'RECEIVE') THEN --{
1941: rcv_int_org_transfer.default_int_org_rcv_line(used_for_cascaded_rows, i);
1942: ELSIF(x_cascaded_table(n).transaction_type IN('TRANSFER', 'ACCEPT', 'REJECT', 'DELIVER')) THEN --}{
1943: asn_debug.put_line('Defaulting for cascaded row ' || TO_CHAR(i));
1944: asn_debug.put_line('Defaulting for cascaded row ' || x_cascaded_table(n).transaction_type);
1945: rcv_int_org_transfer.default_int_org_trans_del(used_for_cascaded_rows, i);
1946: ELSIF(x_cascaded_table(n).transaction_type IN('CORRECT')) THEN --}{
1947: asn_debug.put_line('Defaulting for cascaded row ' || TO_CHAR(i));
1940: IF (x_cascaded_table(n).transaction_type = 'RECEIVE') THEN --{
1941: rcv_int_org_transfer.default_int_org_rcv_line(used_for_cascaded_rows, i);
1942: ELSIF(x_cascaded_table(n).transaction_type IN('TRANSFER', 'ACCEPT', 'REJECT', 'DELIVER')) THEN --}{
1943: asn_debug.put_line('Defaulting for cascaded row ' || TO_CHAR(i));
1944: asn_debug.put_line('Defaulting for cascaded row ' || x_cascaded_table(n).transaction_type);
1945: rcv_int_org_transfer.default_int_org_trans_del(used_for_cascaded_rows, i);
1946: ELSIF(x_cascaded_table(n).transaction_type IN('CORRECT')) THEN --}{
1947: asn_debug.put_line('Defaulting for cascaded row ' || TO_CHAR(i));
1948: rcv_int_org_transfer.default_int_org_cor_line(used_for_cascaded_rows, i);
1943: asn_debug.put_line('Defaulting for cascaded row ' || TO_CHAR(i));
1944: asn_debug.put_line('Defaulting for cascaded row ' || x_cascaded_table(n).transaction_type);
1945: rcv_int_org_transfer.default_int_org_trans_del(used_for_cascaded_rows, i);
1946: ELSIF(x_cascaded_table(n).transaction_type IN('CORRECT')) THEN --}{
1947: asn_debug.put_line('Defaulting for cascaded row ' || TO_CHAR(i));
1948: rcv_int_org_transfer.default_int_org_cor_line(used_for_cascaded_rows, i);
1949: ELSIF(x_cascaded_table(n).transaction_type = 'RETURN TO VENDOR') THEN --}{
1950: asn_debug.put_line('Defaulting for cascaded row ' || TO_CHAR(i));
1951: asn_debug.put_line('Defaulting for cascaded row ' || x_cascaded_table(n).transaction_type);
1946: ELSIF(x_cascaded_table(n).transaction_type IN('CORRECT')) THEN --}{
1947: asn_debug.put_line('Defaulting for cascaded row ' || TO_CHAR(i));
1948: rcv_int_org_transfer.default_int_org_cor_line(used_for_cascaded_rows, i);
1949: ELSIF(x_cascaded_table(n).transaction_type = 'RETURN TO VENDOR') THEN --}{
1950: asn_debug.put_line('Defaulting for cascaded row ' || TO_CHAR(i));
1951: asn_debug.put_line('Defaulting for cascaded row ' || x_cascaded_table(n).transaction_type);
1952: rcv_roi_return.default_return_line(used_for_cascaded_rows, i);
1953: ELSIF(x_cascaded_table(n).transaction_type = 'RETURN TO RECEIVING') THEN --}{
1954: asn_debug.put_line('Defaulting for cascaded row ' || TO_CHAR(i));
1947: asn_debug.put_line('Defaulting for cascaded row ' || TO_CHAR(i));
1948: rcv_int_org_transfer.default_int_org_cor_line(used_for_cascaded_rows, i);
1949: ELSIF(x_cascaded_table(n).transaction_type = 'RETURN TO VENDOR') THEN --}{
1950: asn_debug.put_line('Defaulting for cascaded row ' || TO_CHAR(i));
1951: asn_debug.put_line('Defaulting for cascaded row ' || x_cascaded_table(n).transaction_type);
1952: rcv_roi_return.default_return_line(used_for_cascaded_rows, i);
1953: ELSIF(x_cascaded_table(n).transaction_type = 'RETURN TO RECEIVING') THEN --}{
1954: asn_debug.put_line('Defaulting for cascaded row ' || TO_CHAR(i));
1955: rcv_roi_return.default_return_line(used_for_cascaded_rows, i);
1950: asn_debug.put_line('Defaulting for cascaded row ' || TO_CHAR(i));
1951: asn_debug.put_line('Defaulting for cascaded row ' || x_cascaded_table(n).transaction_type);
1952: rcv_roi_return.default_return_line(used_for_cascaded_rows, i);
1953: ELSIF(x_cascaded_table(n).transaction_type = 'RETURN TO RECEIVING') THEN --}{
1954: asn_debug.put_line('Defaulting for cascaded row ' || TO_CHAR(i));
1955: rcv_roi_return.default_return_line(used_for_cascaded_rows, i);
1956: END IF; --}
1957: ELSIF(x_cascaded_table(n).receipt_source_code = 'CUSTOMER') THEN -- } {
1958: asn_debug.put_line('Defaulting for cascaded row ' || TO_CHAR(i));
1954: asn_debug.put_line('Defaulting for cascaded row ' || TO_CHAR(i));
1955: rcv_roi_return.default_return_line(used_for_cascaded_rows, i);
1956: END IF; --}
1957: ELSIF(x_cascaded_table(n).receipt_source_code = 'CUSTOMER') THEN -- } {
1958: asn_debug.put_line('Defaulting for cascaded row ' || TO_CHAR(i));
1959:
1960: IF (x_cascaded_table(n).transaction_type = 'RECEIVE') THEN --{
1961: rcv_rma_transactions.default_rma_line(used_for_cascaded_rows,
1962: i,
1993: AND l_return_status = fnd_api.g_ret_sts_success) THEN
1994: used_for_cascaded_rows(i).transfer_lpn_id := NULL;
1995: used_for_cascaded_rows(i).transfer_license_plate_number := NULL;
1996:
1997: IF (g_asn_debug = 'Y') THEN
1998: asn_debug.put_line('Set transfer_lpn_id and transfer_licese_plate_number to null for a deliver transaction in an non-WMS org for interface_trx_id ' || used_for_cascaded_rows(i).order_transaction_id);
1999: END IF;
2000: END IF;
2001: END IF;
1994: used_for_cascaded_rows(i).transfer_lpn_id := NULL;
1995: used_for_cascaded_rows(i).transfer_license_plate_number := NULL;
1996:
1997: IF (g_asn_debug = 'Y') THEN
1998: asn_debug.put_line('Set transfer_lpn_id and transfer_licese_plate_number to null for a deliver transaction in an non-WMS org for interface_trx_id ' || used_for_cascaded_rows(i).order_transaction_id);
1999: END IF;
2000: END IF;
2001: END IF;
2002:
2020: used_for_cascaded_rows(i).transfer_lpn_id := NULL;
2021: used_for_cascaded_rows(i).lpn_group_id := NULL;
2022: used_for_cascaded_rows(i).license_plate_number := NULL;
2023:
2024: IF (g_asn_debug = 'Y') THEN
2025: asn_debug.put_line('Set lpn_id and license_plate_number to null for a RETURN/CORRECTION transaction in an non-WMS org for interface_trx_id ' || used_for_cascaded_rows(i).order_transaction_id);
2026: END IF;
2027: END IF;
2028: END IF;
2021: used_for_cascaded_rows(i).lpn_group_id := NULL;
2022: used_for_cascaded_rows(i).license_plate_number := NULL;
2023:
2024: IF (g_asn_debug = 'Y') THEN
2025: asn_debug.put_line('Set lpn_id and license_plate_number to null for a RETURN/CORRECTION transaction in an non-WMS org for interface_trx_id ' || used_for_cascaded_rows(i).order_transaction_id);
2026: END IF;
2027: END IF;
2028: END IF;
2029: -- Bug 8584133: End
2068: x_cascaded_table(n).processing_status_code := 'ERROR';
2069: x_cascaded_table(n).error_status := used_for_cascaded_rows(i).error_status;
2070: x_cascaded_table(n).error_message := used_for_cascaded_rows(i).error_message;
2071:
2072: IF (g_asn_debug = 'Y') THEN
2073: asn_debug.put_line('Have hit error condition in validation');
2074: asn_debug.put_line('Mark needed flags and error message');
2075: asn_debug.put_line('Delete the cascaded rows');
2076: END IF;
2069: x_cascaded_table(n).error_status := used_for_cascaded_rows(i).error_status;
2070: x_cascaded_table(n).error_message := used_for_cascaded_rows(i).error_message;
2071:
2072: IF (g_asn_debug = 'Y') THEN
2073: asn_debug.put_line('Have hit error condition in validation');
2074: asn_debug.put_line('Mark needed flags and error message');
2075: asn_debug.put_line('Delete the cascaded rows');
2076: END IF;
2077:
2070: x_cascaded_table(n).error_message := used_for_cascaded_rows(i).error_message;
2071:
2072: IF (g_asn_debug = 'Y') THEN
2073: asn_debug.put_line('Have hit error condition in validation');
2074: asn_debug.put_line('Mark needed flags and error message');
2075: asn_debug.put_line('Delete the cascaded rows');
2076: END IF;
2077:
2078: used_for_cascaded_rows.DELETE;
2071:
2072: IF (g_asn_debug = 'Y') THEN
2073: asn_debug.put_line('Have hit error condition in validation');
2074: asn_debug.put_line('Mark needed flags and error message');
2075: asn_debug.put_line('Delete the cascaded rows');
2076: END IF;
2077:
2078: used_for_cascaded_rows.DELETE;
2079: EXIT;
2082: * this column to get the available qty if the current row
2083: * is a child of another row in rti.
2084: */
2085: IF used_for_cascaded_rows(i).matching_basis = 'AMOUNT' THEN
2086: asn_debug.put_line('calling update interface amt ');
2087: rcv_roi_transaction.update_interface_available_amt(used_for_cascaded_rows, i);
2088: ELSE
2089: asn_debug.put_line('calling update interface qty ');
2090: rcv_roi_transaction.update_interface_available_qty(used_for_cascaded_rows, i);
2085: IF used_for_cascaded_rows(i).matching_basis = 'AMOUNT' THEN
2086: asn_debug.put_line('calling update interface amt ');
2087: rcv_roi_transaction.update_interface_available_amt(used_for_cascaded_rows, i);
2088: ELSE
2089: asn_debug.put_line('calling update interface qty ');
2090: rcv_roi_transaction.update_interface_available_qty(used_for_cascaded_rows, i);
2091: END IF;
2092: END IF; --}
2093: END LOOP; --}
2092: END IF; --}
2093: END LOOP; --}
2094:
2095: IF x_cascaded_table(n).processing_status_code = 'ERROR' THEN --{
2096: IF (g_asn_debug = 'Y') THEN
2097: asn_debug.put_line('Have hit error condition in validation');
2098: asn_debug.put_line('Mark needed flags and error message');
2099: asn_debug.put_line('Delete the cascaded rows');
2100: END IF;
2093: END LOOP; --}
2094:
2095: IF x_cascaded_table(n).processing_status_code = 'ERROR' THEN --{
2096: IF (g_asn_debug = 'Y') THEN
2097: asn_debug.put_line('Have hit error condition in validation');
2098: asn_debug.put_line('Mark needed flags and error message');
2099: asn_debug.put_line('Delete the cascaded rows');
2100: END IF;
2101:
2094:
2095: IF x_cascaded_table(n).processing_status_code = 'ERROR' THEN --{
2096: IF (g_asn_debug = 'Y') THEN
2097: asn_debug.put_line('Have hit error condition in validation');
2098: asn_debug.put_line('Mark needed flags and error message');
2099: asn_debug.put_line('Delete the cascaded rows');
2100: END IF;
2101:
2102: used_for_cascaded_rows.DELETE;
2095: IF x_cascaded_table(n).processing_status_code = 'ERROR' THEN --{
2096: IF (g_asn_debug = 'Y') THEN
2097: asn_debug.put_line('Have hit error condition in validation');
2098: asn_debug.put_line('Mark needed flags and error message');
2099: asn_debug.put_line('Delete the cascaded rows');
2100: END IF;
2101:
2102: used_for_cascaded_rows.DELETE;
2103: ELSE --} {
2100: END IF;
2101:
2102: used_for_cascaded_rows.DELETE;
2103: ELSE --} {
2104: IF (g_asn_debug = 'Y') THEN
2105: asn_debug.put_line('Have finished default and validation');
2106: asn_debug.put_line('Process has encountered no fatal errors');
2107: asn_debug.put_line('Will write the cascaded rows into actual table');
2108: asn_debug.put_line('Count of cascaded rows ' || TO_CHAR(used_for_cascaded_rows.COUNT));
2101:
2102: used_for_cascaded_rows.DELETE;
2103: ELSE --} {
2104: IF (g_asn_debug = 'Y') THEN
2105: asn_debug.put_line('Have finished default and validation');
2106: asn_debug.put_line('Process has encountered no fatal errors');
2107: asn_debug.put_line('Will write the cascaded rows into actual table');
2108: asn_debug.put_line('Count of cascaded rows ' || TO_CHAR(used_for_cascaded_rows.COUNT));
2109: END IF;
2102: used_for_cascaded_rows.DELETE;
2103: ELSE --} {
2104: IF (g_asn_debug = 'Y') THEN
2105: asn_debug.put_line('Have finished default and validation');
2106: asn_debug.put_line('Process has encountered no fatal errors');
2107: asn_debug.put_line('Will write the cascaded rows into actual table');
2108: asn_debug.put_line('Count of cascaded rows ' || TO_CHAR(used_for_cascaded_rows.COUNT));
2109: END IF;
2110:
2103: ELSE --} {
2104: IF (g_asn_debug = 'Y') THEN
2105: asn_debug.put_line('Have finished default and validation');
2106: asn_debug.put_line('Process has encountered no fatal errors');
2107: asn_debug.put_line('Will write the cascaded rows into actual table');
2108: asn_debug.put_line('Count of cascaded rows ' || TO_CHAR(used_for_cascaded_rows.COUNT));
2109: END IF;
2110:
2111: FOR j IN 1 .. used_for_cascaded_rows.COUNT LOOP --{
2104: IF (g_asn_debug = 'Y') THEN
2105: asn_debug.put_line('Have finished default and validation');
2106: asn_debug.put_line('Process has encountered no fatal errors');
2107: asn_debug.put_line('Will write the cascaded rows into actual table');
2108: asn_debug.put_line('Count of cascaded rows ' || TO_CHAR(used_for_cascaded_rows.COUNT));
2109: END IF;
2110:
2111: FOR j IN 1 .. used_for_cascaded_rows.COUNT LOOP --{
2112: IF (g_asn_debug = 'Y') THEN
2108: asn_debug.put_line('Count of cascaded rows ' || TO_CHAR(used_for_cascaded_rows.COUNT));
2109: END IF;
2110:
2111: FOR j IN 1 .. used_for_cascaded_rows.COUNT LOOP --{
2112: IF (g_asn_debug = 'Y') THEN
2113: asn_debug.put_line('Current counter in actual table is at ' || TO_CHAR(n));
2114: END IF;
2115:
2116: x_cascaded_table(n) := used_for_cascaded_rows(j);
2109: END IF;
2110:
2111: FOR j IN 1 .. used_for_cascaded_rows.COUNT LOOP --{
2112: IF (g_asn_debug = 'Y') THEN
2113: asn_debug.put_line('Current counter in actual table is at ' || TO_CHAR(n));
2114: END IF;
2115:
2116: x_cascaded_table(n) := used_for_cascaded_rows(j);
2117: used_for_cascaded_rows.DELETE(j);
2117: used_for_cascaded_rows.DELETE(j);
2118: n := n + 1;
2119: END LOOP; --}
2120:
2121: IF (g_asn_debug = 'Y') THEN
2122: asn_debug.put_line('Current counter before decrementing in actual table is at ' || TO_CHAR(n));
2123: END IF;
2124:
2125: n := n - 1; -- Get the counter in sync
2118: n := n + 1;
2119: END LOOP; --}
2120:
2121: IF (g_asn_debug = 'Y') THEN
2122: asn_debug.put_line('Current counter before decrementing in actual table is at ' || TO_CHAR(n));
2123: END IF;
2124:
2125: n := n - 1; -- Get the counter in sync
2126:
2123: END IF;
2124:
2125: n := n - 1; -- Get the counter in sync
2126:
2127: IF (g_asn_debug = 'Y') THEN
2128: asn_debug.put_line('Current counter in actual table is at ' || TO_CHAR(n));
2129: END IF;
2130: END IF; --}
2131: ELSE --} {
2124:
2125: n := n - 1; -- Get the counter in sync
2126:
2127: IF (g_asn_debug = 'Y') THEN
2128: asn_debug.put_line('Current counter in actual table is at ' || TO_CHAR(n));
2129: END IF;
2130: END IF; --}
2131: ELSE --} {
2132: x_cascaded_table(n).processing_status_code := 'ERROR'; -- changed (i) -> (n)
2132: x_cascaded_table(n).processing_status_code := 'ERROR'; -- changed (i) -> (n)
2133: RETURN;
2134: END IF; --}
2135:
2136: IF (g_asn_debug = 'Y') THEN
2137: asn_debug.put_line('Exit create shipment line');
2138: END IF;
2139: EXCEPTION
2140: WHEN rcv_error_pkg.e_fatal_error THEN
2133: RETURN;
2134: END IF; --}
2135:
2136: IF (g_asn_debug = 'Y') THEN
2137: asn_debug.put_line('Exit create shipment line');
2138: END IF;
2139: EXCEPTION
2140: WHEN rcv_error_pkg.e_fatal_error THEN
2141: x_cascaded_table(n).error_status := 'E';
2140: WHEN rcv_error_pkg.e_fatal_error THEN
2141: x_cascaded_table(n).error_status := 'E';
2142: x_cascaded_table(n).error_message := rcv_error_pkg.get_last_message;
2143: WHEN OTHERS THEN
2144: IF (g_asn_debug = 'Y') THEN
2145: asn_debug.put_line('Exception in process_line');
2146: END IF;
2147: END process_line;
2148:
2141: x_cascaded_table(n).error_status := 'E';
2142: x_cascaded_table(n).error_message := rcv_error_pkg.get_last_message;
2143: WHEN OTHERS THEN
2144: IF (g_asn_debug = 'Y') THEN
2145: asn_debug.put_line('Exception in process_line');
2146: END IF;
2147: END process_line;
2148:
2149: PROCEDURE explode_lpn_failed(
2151: x_group_id NUMBER,
2152: x_lpn_group_id NUMBER
2153: ) IS
2154: BEGIN
2155: IF (g_asn_debug = 'Y') THEN
2156: asn_debug.put_line('update_rti_error in explode_lpn_failed ');
2157: END IF;
2158:
2159: update_rti_error(p_group_id => x_group_id,
2152: x_lpn_group_id NUMBER
2153: ) IS
2154: BEGIN
2155: IF (g_asn_debug = 'Y') THEN
2156: asn_debug.put_line('update_rti_error in explode_lpn_failed ');
2157: END IF;
2158:
2159: update_rti_error(p_group_id => x_group_id,
2160: p_interface_id => x_interface_txn_id,
2165: rcv_error_pkg.set_token('LPN_GROUP_ID', x_lpn_group_id);
2166: rcv_error_pkg.log_interface_warning('LPN_GROUP_ID');
2167: EXCEPTION
2168: WHEN OTHERS THEN
2169: IF (g_asn_debug = 'Y') THEN
2170: asn_debug.put_line('Exception in explode_lpn_failed');
2171: END IF;
2172: END explode_lpn_failed;
2173:
2166: rcv_error_pkg.log_interface_warning('LPN_GROUP_ID');
2167: EXCEPTION
2168: WHEN OTHERS THEN
2169: IF (g_asn_debug = 'Y') THEN
2170: asn_debug.put_line('Exception in explode_lpn_failed');
2171: END IF;
2172: END explode_lpn_failed;
2173:
2174: PROCEDURE update_rti_error(
2183: l_inventory_id NUMBER;
2184: l_txn_mode VARCHAR2(25);
2185: l_processing_mode_code rcv_transactions_interface.processing_mode_code%TYPE;
2186: BEGIN
2187: IF (g_asn_debug = 'Y') THEN
2188: asn_debug.put_line('Set rti row to error');
2189: asn_debug.put_line('p_group_id ' || p_group_id);
2190: asn_debug.put_line('p_interface_id ' || p_interface_id);
2191: asn_debug.put_line('p_header_interface_id ' || p_header_interface_id);
2184: l_txn_mode VARCHAR2(25);
2185: l_processing_mode_code rcv_transactions_interface.processing_mode_code%TYPE;
2186: BEGIN
2187: IF (g_asn_debug = 'Y') THEN
2188: asn_debug.put_line('Set rti row to error');
2189: asn_debug.put_line('p_group_id ' || p_group_id);
2190: asn_debug.put_line('p_interface_id ' || p_interface_id);
2191: asn_debug.put_line('p_header_interface_id ' || p_header_interface_id);
2192: asn_debug.put_line('p_lpn_group_id ' || p_lpn_group_id);
2185: l_processing_mode_code rcv_transactions_interface.processing_mode_code%TYPE;
2186: BEGIN
2187: IF (g_asn_debug = 'Y') THEN
2188: asn_debug.put_line('Set rti row to error');
2189: asn_debug.put_line('p_group_id ' || p_group_id);
2190: asn_debug.put_line('p_interface_id ' || p_interface_id);
2191: asn_debug.put_line('p_header_interface_id ' || p_header_interface_id);
2192: asn_debug.put_line('p_lpn_group_id ' || p_lpn_group_id);
2193: END IF;
2186: BEGIN
2187: IF (g_asn_debug = 'Y') THEN
2188: asn_debug.put_line('Set rti row to error');
2189: asn_debug.put_line('p_group_id ' || p_group_id);
2190: asn_debug.put_line('p_interface_id ' || p_interface_id);
2191: asn_debug.put_line('p_header_interface_id ' || p_header_interface_id);
2192: asn_debug.put_line('p_lpn_group_id ' || p_lpn_group_id);
2193: END IF;
2194:
2187: IF (g_asn_debug = 'Y') THEN
2188: asn_debug.put_line('Set rti row to error');
2189: asn_debug.put_line('p_group_id ' || p_group_id);
2190: asn_debug.put_line('p_interface_id ' || p_interface_id);
2191: asn_debug.put_line('p_header_interface_id ' || p_header_interface_id);
2192: asn_debug.put_line('p_lpn_group_id ' || p_lpn_group_id);
2193: END IF;
2194:
2195: -- bug 3676436, if there is a pending error message than we log it
2188: asn_debug.put_line('Set rti row to error');
2189: asn_debug.put_line('p_group_id ' || p_group_id);
2190: asn_debug.put_line('p_interface_id ' || p_interface_id);
2191: asn_debug.put_line('p_header_interface_id ' || p_header_interface_id);
2192: asn_debug.put_line('p_lpn_group_id ' || p_lpn_group_id);
2193: END IF;
2194:
2195: -- bug 3676436, if there is a pending error message than we log it
2196: rcv_error_pkg.log_interface_error('INTERFACE_TRANSACTION_ID',FALSE);
2217: FROM rcv_transactions_interface
2218: WHERE GROUP_ID = p_group_id;
2219: END IF;
2220:
2221: IF (g_asn_debug = 'Y') THEN
2222: asn_debug.put_line('Processing_mode_code ' || l_processing_mode_code);
2223: END IF;
2224:
2225: IF (l_processing_mode_code = 'ONLINE') THEN
2218: WHERE GROUP_ID = p_group_id;
2219: END IF;
2220:
2221: IF (g_asn_debug = 'Y') THEN
2222: asn_debug.put_line('Processing_mode_code ' || l_processing_mode_code);
2223: END IF;
2224:
2225: IF (l_processing_mode_code = 'ONLINE') THEN
2226: IF (g_asn_debug = 'Y') THEN
2222: asn_debug.put_line('Processing_mode_code ' || l_processing_mode_code);
2223: END IF;
2224:
2225: IF (l_processing_mode_code = 'ONLINE') THEN
2226: IF (g_asn_debug = 'Y') THEN
2227: asn_debug.put_line('online error ');
2228: END IF;
2229:
2230: UPDATE rcv_transactions_interface
2223: END IF;
2224:
2225: IF (l_processing_mode_code = 'ONLINE') THEN
2226: IF (g_asn_debug = 'Y') THEN
2227: asn_debug.put_line('online error ');
2228: END IF;
2229:
2230: UPDATE rcv_transactions_interface
2231: SET processing_status_code = 'ERROR'
2239: x_msg_count => l_msg_count
2240: );
2241: ELSE /* For Batch and immediate */
2242: IF (p_header_interface_id IS NOT NULL) THEN
2243: IF (g_asn_debug = 'Y') THEN
2244: asn_debug.put_line('header_interface_id not null ');
2245: END IF;
2246:
2247: UPDATE rcv_transactions_interface
2240: );
2241: ELSE /* For Batch and immediate */
2242: IF (p_header_interface_id IS NOT NULL) THEN
2243: IF (g_asn_debug = 'Y') THEN
2244: asn_debug.put_line('header_interface_id not null ');
2245: END IF;
2246:
2247: UPDATE rcv_transactions_interface
2248: SET processing_status_code = 'ERROR'
2250:
2251: l_inventory_id := p_header_interface_id;
2252: l_txn_mode := 'HEADER';
2253: ELSIF(p_lpn_group_id IS NOT NULL) THEN
2254: IF (g_asn_debug = 'Y') THEN
2255: asn_debug.put_line('lpn_group_id not null ');
2256: END IF;
2257:
2258: UPDATE rcv_transactions_interface
2251: l_inventory_id := p_header_interface_id;
2252: l_txn_mode := 'HEADER';
2253: ELSIF(p_lpn_group_id IS NOT NULL) THEN
2254: IF (g_asn_debug = 'Y') THEN
2255: asn_debug.put_line('lpn_group_id not null ');
2256: END IF;
2257:
2258: UPDATE rcv_transactions_interface
2259: SET processing_status_code = 'ERROR'
2261:
2262: l_inventory_id := p_lpn_group_id;
2263: l_txn_mode := 'LPN_GROUP';
2264: ELSIF(p_interface_id IS NOT NULL) THEN
2265: IF (g_asn_debug = 'Y') THEN
2266: asn_debug.put_line('interface_id not null ');
2267: END IF;
2268:
2269: UPDATE rcv_transactions_interface
2262: l_inventory_id := p_lpn_group_id;
2263: l_txn_mode := 'LPN_GROUP';
2264: ELSIF(p_interface_id IS NOT NULL) THEN
2265: IF (g_asn_debug = 'Y') THEN
2266: asn_debug.put_line('interface_id not null ');
2267: END IF;
2268:
2269: UPDATE rcv_transactions_interface
2270: SET processing_status_code = 'ERROR'
2281: * WMS assumes that when we call them with
2282: * ONLINE/BATCH/IMMEDIATE then we call them with
2283: * only group_id.
2284: */
2285: IF (g_asn_debug = 'Y') THEN
2286: asn_debug.put_line('update all rti rows to error');
2287: END IF;
2288:
2289: UPDATE rcv_headers_interface
2282: * ONLINE/BATCH/IMMEDIATE then we call them with
2283: * only group_id.
2284: */
2285: IF (g_asn_debug = 'Y') THEN
2286: asn_debug.put_line('update all rti rows to error');
2287: END IF;
2288:
2289: UPDATE rcv_headers_interface
2290: SET processing_status_code = 'ERROR'
2297: l_inventory_id := p_group_id;
2298: l_txn_mode := l_processing_mode_code;
2299: END IF;
2300:
2301: IF (g_asn_debug = 'Y') THEN
2302: asn_debug.put_line('Before call to txn_complete');
2303: END IF;
2304:
2305: inv_receiving_transaction.txn_complete(p_group_id => l_inventory_id,
2298: l_txn_mode := l_processing_mode_code;
2299: END IF;
2300:
2301: IF (g_asn_debug = 'Y') THEN
2302: asn_debug.put_line('Before call to txn_complete');
2303: END IF;
2304:
2305: inv_receiving_transaction.txn_complete(p_group_id => l_inventory_id,
2306: p_txn_status => 'FALSE',
2309: x_msg_data => l_msg_data,
2310: x_msg_count => l_msg_count
2311: );
2312:
2313: IF (g_asn_debug = 'Y') THEN
2314: asn_debug.put_line('After call to txn_complete');
2315: END IF;
2316: END IF;
2317: EXCEPTION
2310: x_msg_count => l_msg_count
2311: );
2312:
2313: IF (g_asn_debug = 'Y') THEN
2314: asn_debug.put_line('After call to txn_complete');
2315: END IF;
2316: END IF;
2317: EXCEPTION
2318: WHEN OTHERS THEN
2315: END IF;
2316: END IF;
2317: EXCEPTION
2318: WHEN OTHERS THEN
2319: IF (g_asn_debug = 'Y') THEN
2320: asn_debug.put_line('Exception in update_rti_error');
2321: END IF;
2322: END update_rti_error;
2323: END rcv_roi_preprocessor;
2316: END IF;
2317: EXCEPTION
2318: WHEN OTHERS THEN
2319: IF (g_asn_debug = 'Y') THEN
2320: asn_debug.put_line('Exception in update_rti_error');
2321: END IF;
2322: END update_rti_error;
2323: END rcv_roi_preprocessor;