[Home] [Help]
396: SELECT from_subinventory,
397: from_locator_id
398: INTO l_from_subinventory,
399: l_from_locator_id
400: FROM rcv_transactions_interface rti
401: WHERE rti.interface_transaction_id = x_cascaded_table(n).parent_interface_txn_id;
402: ELSIF (x_cascaded_table(n).parent_transaction_id IS NOT NULL) THEN
403: SELECT from_subinventory,
404: from_locator_id
433: ) IS
434: l_po_header_id po_headers_all.po_header_id%type;
435: l_return_status VARCHAR2(1) :='S';
436: l_complex_flag varchar2(1);
437: l_interface_source_code rcv_transactions_interface.interface_source_code%type;
438: BEGIN
439: x_progress := '000';
440: -- Derive PO header and line at the beginning to differentiate between services/goods matching basis
441: -- 1) derive po header info
768: /* Fix for bug 2296720.
769: If both vendor_site_id and vendor_site_code are not populated
770: in interface tables, and if there are multiple vendor sites
771: associated to a particular vendor then we default them from PO
772: using the po_header_id of rcv_transactions_interface for each
773: line.
774: */
775: IF x_cascaded_table(n).vendor_site_id IS NULL
776: AND x_cascaded_table(n).vendor_site_code IS NULL THEN --{
942: item_id_record.to_organization_id := x_cascaded_table(n).to_organization_id;
943: item_id_record.item_revision := x_cascaded_table(n).item_revision;
944: item_id_record.error_record.error_status := 'S';
945: item_id_record.error_record.error_message := NULL;
946: rcv_transactions_interface_sv.default_item_revision(item_id_record);
947: x_cascaded_table(n).item_revision := item_id_record.item_revision;
948:
949: IF (g_asn_debug = 'Y') THEN
950: asn_debug.put_line(NVL(item_id_record.item_revision, 'Item Revision is null'));
1740: IF (g_asn_debug = 'Y') THEN
1741: asn_debug.put_line('Derive po_header_id');
1742: END IF;
1743:
1744: rcv_transactions_interface_sv.get_po_header_id(document_num_record);
1745: x_cascaded_table(n).po_header_id := document_num_record.po_header_id;
1746: x_cascaded_table(n).error_status := document_num_record.error_record.error_status;
1747: x_cascaded_table(n).error_message := document_num_record.error_record.error_message;
1748: rcv_error_pkg.log_interface_message(x_cascaded_table(n).error_status,
1821: IF (g_asn_debug = 'Y') THEN
1822: asn_debug.put_line('Derive item_id');
1823: END IF;
1824:
1825: rcv_transactions_interface_sv.get_item_id(item_id_record);
1826: x_cascaded_table(n).item_id := item_id_record.item_id;
1827: x_cascaded_table(n).primary_unit_of_measure := item_id_record.primary_unit_of_measure;
1828: x_cascaded_table(n).use_mtl_lot := item_id_record.use_mtl_lot; -- bug 608353
1829: x_cascaded_table(n).use_mtl_serial := item_id_record.use_mtl_serial; -- bug 608353
1864: IF (g_asn_debug = 'Y') THEN
1865: asn_debug.put_line('Derive Substitute Item Id');
1866: END IF;
1867:
1868: rcv_transactions_interface_sv.get_sub_item_id(sub_item_id_record);
1869: x_cascaded_table(n).substitute_item_id := sub_item_id_record.substitute_item_id;
1870: x_cascaded_table(n).error_status := sub_item_id_record.error_record.error_status;
1871: x_cascaded_table(n).error_message := sub_item_id_record.error_record.error_message;
1872:
1913: IF (g_asn_debug = 'Y') THEN
1914: asn_debug.put_line('Derive po_line_id');
1915: END IF;
1916:
1917: rcv_transactions_interface_sv.get_po_line_id(po_line_id_record);
1918:
1919: IF x_cascaded_table(n).item_id IS NULL THEN
1920: x_cascaded_table(n).item_id := po_line_id_record.item_id;
1921: END IF;
2069: END IF; -- set primary_uom
2070: END Comment: Bug: 4735484
2071: */
2072: /* Bug 2020269 : uom_code needs to be derived from unit_of_measure
2073: entered in rcv_transactions_interface.
2074: */
2075: IF (x_cascaded_table(n).unit_of_measure IS NOT NULL) THEN
2076: IF (g_asn_debug = 'Y') THEN
2077: asn_debug.put_line('X_progress ' || x_progress);
2349: IF (g_asn_debug = 'Y') THEN
2350: asn_debug.put_line('Derive location_id');
2351: END IF;
2352:
2353: rcv_transactions_interface_sv.get_location_id(location_id_record);
2354: x_cascaded_table(n).location_id := location_id_record.location_id;
2355: x_cascaded_table(n).error_status := location_id_record.error_record.error_status;
2356: x_cascaded_table(n).error_message := location_id_record.error_record.error_message;
2357: END IF;
2377: IF (g_asn_debug = 'Y') THEN
2378: asn_debug.put_line('Derive ship to location_id');
2379: END IF;
2380:
2381: rcv_transactions_interface_sv.get_location_id(location_id_record);
2382: x_cascaded_table(n).ship_to_location_id := location_id_record.location_id;
2383: x_cascaded_table(n).error_status := location_id_record.error_record.error_status;
2384: x_cascaded_table(n).error_message := location_id_record.error_record.error_message;
2385: END IF;
2405: IF (g_asn_debug = 'Y') THEN
2406: asn_debug.put_line('Derive routing_header_id');
2407: END IF;
2408:
2409: rcv_transactions_interface_sv.get_routing_header_id(routing_header_id_record);
2410: x_cascaded_table(n).routing_header_id := routing_header_id_record.routing_header_id;
2411: x_cascaded_table(n).error_status := routing_header_id_record.error_record.error_status;
2412: x_cascaded_table(n).error_message := routing_header_id_record.error_record.error_message;
2413: END IF;
2433: IF (g_asn_debug = 'Y') THEN
2434: asn_debug.put_line('Derive routing Step Id');
2435: END IF;
2436:
2437: rcv_transactions_interface_sv.get_routing_step_id(routing_step_id_record);
2438: x_cascaded_table(n).routing_step_id := routing_step_id_record.routing_step_id;
2439: x_cascaded_table(n).error_status := routing_step_id_record.error_record.error_status;
2440: x_cascaded_table(n).error_message := routing_step_id_record.error_record.error_message;
2441: END IF;
2505: IF (g_asn_debug = 'Y') THEN
2506: asn_debug.put_line('Derive deliver_to_location_id');
2507: END IF;
2508:
2509: rcv_transactions_interface_sv.get_location_id(location_id_record);
2510: x_cascaded_table(n).deliver_to_location_id := location_id_record.location_id;
2511: x_cascaded_table(n).error_status := location_id_record.error_record.error_status;
2512: x_cascaded_table(n).error_message := location_id_record.error_record.error_message;
2513: END IF;
2691: INTO l_to_subinventory,
2692: l_to_locator_id,
2693: l_from_subinventory,
2694: l_from_locator_id
2695: FROM rcv_transactions_interface rti
2696: WHERE rti.interface_transaction_id = x_cascaded_table(n).parent_interface_txn_id;
2697: ELSIF (x_cascaded_table(n).parent_transaction_id IS NOT NULL) THEN
2698: SELECT subinventory,
2699: locator_id,
2955: INTO l_to_subinventory,
2956: l_to_locator_id,
2957: l_from_subinventory,
2958: l_from_locator_id
2959: FROM rcv_transactions_interface rti
2960: WHERE rti.interface_transaction_id = x_cascaded_table(n).parent_interface_txn_id;
2961: ELSIF (x_cascaded_table(n).parent_transaction_id IS NOT NULL) THEN
2962: SELECT subinventory,
2963: locator_id,
3024: IF (g_asn_debug = 'Y') THEN
3025: asn_debug.put_line('Derive Reason_id');
3026: END IF;
3027:
3028: rcv_transactions_interface_sv.get_reason_id(reason_id_record);
3029: x_cascaded_table(n).reason_id := reason_id_record.reason_id;
3030: x_cascaded_table(n).error_status := reason_id_record.error_record.error_status;
3031: x_cascaded_table(n).error_message := reason_id_record.error_record.error_message;
3032: END IF;
3404: x_progress VARCHAR2(3);
3405: x_to_organization_code VARCHAR2(5);
3406: x_converted_trx_qty NUMBER := 0;
3407: transaction_ok BOOLEAN := FALSE;
3408: x_expected_date rcv_transactions_interface.expected_receipt_date%TYPE;
3409: high_range_date DATE;
3410: low_range_date DATE;
3411: rows_fetched NUMBER := 0;
3412: x_tolerable_qty NUMBER := 0;
3502: -- the following steps will create a set of rows linking the line_record with
3503: -- its corresponding po_line_location rows until the quantity value from
3504: -- the asn is consumed. (Cascade)
3505:
3506: /* 2119137 : If the user populates rcv_transactions_interface
3507: with po_line_id, then ROI errors out with
3508: RCV_ASN_NO_PO_LINE_LOCATION_ID when the docment_line_num
3509: is not provided for one time items. Modified the "if" criteria in
3510: such a way that the ROI validation does'nt error out when
3564: WHERE po_line_id = temp_cascaded_table(current_n).po_line_id;
3565: EXCEPTION
3566: WHEN OTHERS THEN
3567: IF (g_asn_debug = 'Y') THEN
3568: asn_debug.put_line('wrong po_line_id entered in rcv_transactions_interface');
3569: END IF;
3570: END;
3571: END IF; --}
3572:
3579: WHERE line_location_id = temp_cascaded_table(current_n).po_line_location_id;
3580: EXCEPTION
3581: WHEN OTHERS THEN
3582: IF (g_asn_debug = 'Y') THEN
3583: asn_debug.put_line('wrong po_line_location_id entered in rcv_transactions_interface');
3584: END IF;
3585: END;
3586: END IF; --}
3587:
3594: WHERE po_distribution_id = temp_cascaded_table(current_n).po_distribution_id;
3595: EXCEPTION
3596: WHEN OTHERS THEN
3597: IF (g_asn_debug = 'Y') THEN
3598: asn_debug.put_line('wrong po_distribution_id entered in rcv_transactions_interface');
3599: END IF;
3600: END;
3601: END IF; --}
3602:
4082: temp_cascaded_table(current_n).quantity := temp_cascaded_table(current_n).quantity + x_remaining_quantity;
4083: temp_cascaded_table(current_n).quantity_shipped := temp_cascaded_table(current_n).quantity_shipped + x_remaining_quantity;
4084: temp_cascaded_table(current_n).source_doc_quantity := temp_cascaded_table(current_n).source_doc_quantity + x_remaining_qty_po_uom;
4085: temp_cascaded_table(current_n).primary_quantity := temp_cascaded_table(current_n).primary_quantity
4086: + rcv_transactions_interface_sv.convert_into_correct_qty(x_remaining_quantity,
4087: temp_cascaded_table(1).unit_of_measure,
4088: temp_cascaded_table(1).item_id,
4089: temp_cascaded_table(1).primary_unit_of_measure
4090: );
4680: asn_debug.put_line('Cursor record ' || TO_CHAR(rows_fetched));
4681: asn_debug.put_line('Check date tolerance');
4682: END IF;
4683:
4684: rcv_transactions_interface_sv.check_date_tolerance(NVL(temp_cascaded_table(1).expected_receipt_date, x_header_record.header_record.expected_receipt_date), -- Bug 487222
4685: x_shipmentdistributionrec.promised_date,
4686: x_shipmentdistributionrec.days_early_receipt_allowed,
4687: x_shipmentdistributionrec.days_late_receipt_allowed,
4688: x_shipmentdistributionrec.receipt_days_exception_code
4815: END IF;
4816:
4817: -- Check shipto_location enforcement
4818:
4819: rcv_transactions_interface_sv.check_shipto_enforcement(x_shipmentdistributionrec.ship_to_location_id,
4820: NVL(temp_cascaded_table(1).ship_to_location_id, x_header_record.header_record.location_id),
4821: x_shipmentdistributionrec.enforce_ship_to_location_code
4822: );
4823:
5092: END IF;
5093: END IF;
5094:
5095: if ( l_complex_flag = 'Y' ) then --{
5096: x_remaining_qty_po_uom := rcv_transactions_interface_sv.convert_into_correct_qty(x_remaining_quantity,
5097:
5098: temp_cascaded_table(1).unit_of_measure,
5099:
5100: temp_cascaded_table(1).item_id,
5103:
5104: );
5105: -- using arbit qty for PO->ASN, PO->PRIMARY UOM conversion as this is just a check
5106:
5107: po_asn_uom_qty := rcv_transactions_interface_sv.convert_into_correct_qty(1000,
5108:
5109: x_shipmentdistributionrec.unit_meas_lookup_code,
5110:
5111: temp_cascaded_table(1).item_id,
5112:
5113: temp_cascaded_table(1).unit_of_measure
5114:
5115: );
5116: po_primary_uom_qty := rcv_transactions_interface_sv.convert_into_correct_qty(1000,
5117:
5118: x_shipmentdistributionrec.unit_meas_lookup_code,
5119:
5120: temp_cascaded_table(1).item_id,
5271: * errors out. Reverting to old code for complex work POs.
5272: */
5273: if ( l_complex_flag = 'Y' ) then --{
5274: x_remaining_quantity :=
5275: rcv_transactions_interface_sv.convert_into_correct_qty(
5276: x_remaining_qty_po_uom,
5277: x_shipmentdistributionrec.unit_meas_lookup_code,
5278: temp_cascaded_table(1).item_id,
5279: temp_cascaded_table(1).unit_of_measure);
5350: */
5351: if ( l_complex_flag = 'Y' ) then --{
5352: temp_cascaded_table(current_n).vendor_cum_shipped_qty :=
5353: temp_cascaded_table(current_n).vendor_cum_shipped_qty
5354: - rcv_transactions_interface_sv.convert_into_correct_qty
5355: (x_bkp_qty,
5356: temp_cascaded_table(current_n).unit_of_measure,
5357: temp_cascaded_table(current_n).item_id,
5358: temp_cascaded_table(current_n).primary_unit_of_measure);
5399: * return the original quantity whereas the new one
5400: * errors out. Reverting to old code for complex work POs.
5401: */
5402: if ( l_complex_flag = 'Y' ) then --{
5403: temp_cascaded_table(current_n).quantity := rcv_transactions_interface_sv.convert_into_correct_qty(x_converted_trx_qty,
5404:
5405: x_shipmentdistributionrec.unit_meas_lookup_code,
5406:
5407: temp_cascaded_table(current_n).item_id,
5432: * return the original quantity whereas the new one
5433: * errors out. Reverting to old code for complex work POs.
5434: */
5435: if ( l_complex_flag = 'Y' ) then --{
5436: temp_cascaded_table(current_n).primary_quantity := rcv_transactions_interface_sv.convert_into_correct_qty(x_converted_trx_qty,
5437: x_shipmentdistributionrec.unit_meas_lookup_code,
5438:
5439: temp_cascaded_table(current_n).item_id,
5440:
5494: END IF;
5495: END IF; --}
5496:
5497: /* ksareddy - 2329928 Ported changes by bao in branch to cache set_of_books_id */
5498: IF (rcv_transactions_interface_sv.x_set_of_books_id IS NULL) THEN
5499: SELECT set_of_books_id
5500: INTO rcv_transactions_interface_sv.x_set_of_books_id
5501: FROM financials_system_parameters;
5502: END IF;
5496:
5497: /* ksareddy - 2329928 Ported changes by bao in branch to cache set_of_books_id */
5498: IF (rcv_transactions_interface_sv.x_set_of_books_id IS NULL) THEN
5499: SELECT set_of_books_id
5500: INTO rcv_transactions_interface_sv.x_set_of_books_id
5501: FROM financials_system_parameters;
5502: END IF;
5503:
5504: x_sob_id := rcv_transactions_interface_sv.x_set_of_books_id;
5500: INTO rcv_transactions_interface_sv.x_set_of_books_id
5501: FROM financials_system_parameters;
5502: END IF;
5503:
5504: x_sob_id := rcv_transactions_interface_sv.x_set_of_books_id;
5505: /* Bug 1845702
5506: * Currency rate and date can be changed at the time of receipt
5507: * depending on the profile ALLOW_RATE_OVERRIDE_FOR_USER_RATE_TYPE.
5508: * This was not handled in the open interface. Introduced code
6054: x_progress VARCHAR2(3);
6055: x_to_organization_code VARCHAR2(5);
6056: x_converted_trx_amt NUMBER := 0;
6057: transaction_ok BOOLEAN := FALSE;
6058: x_expected_date rcv_transactions_interface.expected_receipt_date%TYPE;
6059: high_range_date DATE;
6060: low_range_date DATE;
6061: rows_fetched NUMBER := 0;
6062: x_tolerable_amt NUMBER := 0;
6192: -- the following steps will create a set of rows linking the line_record with
6193: -- its corresponding po_line_location rows until the quantity value from
6194: -- the asn is consumed. (Cascade)
6195:
6196: /* 2119137 : If the user populates rcv_transactions_interface
6197: with po_line_id, then ROI errors out with
6198: RCV_ASN_NO_PO_LINE_LOCATION_ID when the docment_line_num
6199: is not provided for one time items. Modified the "if" criteria in
6200: such a way that the ROI validation does'nt error out when
6236: WHERE po_line_id = temp_cascaded_table(current_n).po_line_id;
6237: EXCEPTION
6238: WHEN OTHERS THEN
6239: IF (g_asn_debug = 'Y') THEN
6240: asn_debug.put_line('wrong po_line_id entered in rcv_transactions_interface');
6241: END IF;
6242: END;
6243: END IF; --}
6244:
6251: WHERE line_location_id = temp_cascaded_table(current_n).po_line_location_id;
6252: EXCEPTION
6253: WHEN OTHERS THEN
6254: IF (g_asn_debug = 'Y') THEN
6255: asn_debug.put_line('wrong po_line_location_id entered in rcv_transactions_interface');
6256: END IF;
6257: END;
6258: END IF; --}
6259:
6266: WHERE po_distribution_id = temp_cascaded_table(current_n).po_distribution_id;
6267: EXCEPTION
6268: WHEN OTHERS THEN
6269: IF (g_asn_debug = 'Y') THEN
6270: asn_debug.put_line('wrong po_distribution_id entered in rcv_transactions_interface');
6271: END IF;
6272: END;
6273: END IF; --}
6274:
6845: IF (g_asn_debug = 'Y') THEN
6846: asn_debug.put_line('Check date tolerance for a Fixed Price services line');
6847: END IF;
6848:
6849: rcv_transactions_interface_sv.check_date_tolerance(NVL(temp_cascaded_table(1).expected_receipt_date, x_header_record.header_record.expected_receipt_date), -- Bug 487222
6850: x_shipmentdistributionrec.promised_date,
6851: x_shipmentdistributionrec.days_early_receipt_allowed,
6852: x_shipmentdistributionrec.days_late_receipt_allowed,
6853: x_shipmentdistributionrec.receipt_days_exception_code
6877: END IF;
6878:
6879: -- Check shipto_location enforcement
6880:
6881: rcv_transactions_interface_sv.check_shipto_enforcement(x_shipmentdistributionrec.ship_to_location_id,
6882: NVL(temp_cascaded_table(1).ship_to_location_id, x_header_record.header_record.location_id),
6883: x_shipmentdistributionrec.enforce_ship_to_location_code
6884: );
6885:
7114: END IF;
7115: END IF; --}
7116:
7117: /* ksareddy - 2329928 Ported changes by bao in branch to cache set_of_books_id */
7118: IF (rcv_transactions_interface_sv.x_set_of_books_id IS NULL) THEN
7119: SELECT set_of_books_id
7120: INTO rcv_transactions_interface_sv.x_set_of_books_id
7121: FROM financials_system_parameters;
7122: END IF;
7116:
7117: /* ksareddy - 2329928 Ported changes by bao in branch to cache set_of_books_id */
7118: IF (rcv_transactions_interface_sv.x_set_of_books_id IS NULL) THEN
7119: SELECT set_of_books_id
7120: INTO rcv_transactions_interface_sv.x_set_of_books_id
7121: FROM financials_system_parameters;
7122: END IF;
7123:
7124: x_sob_id := rcv_transactions_interface_sv.x_set_of_books_id;
7120: INTO rcv_transactions_interface_sv.x_set_of_books_id
7121: FROM financials_system_parameters;
7122: END IF;
7123:
7124: x_sob_id := rcv_transactions_interface_sv.x_set_of_books_id;
7125: /* Bug 1845702
7126: * Currency rate and date can be changed at the time of receipt
7127: * depending on the profile ALLOW_RATE_OVERRIDE_FOR_USER_RATE_TYPE.
7128: * This was not handled in the open interface. Introduced code
7543:
7544: quantity_invoiced_record.quantity_invoiced := x_cascaded_table(n).quantity_invoiced;
7545: quantity_invoiced_record.error_record.error_status := 'S';
7546: quantity_invoiced_record.error_record.error_message := NULL;
7547: rcv_transactions_interface_sv1.validate_quantity_invoiced(quantity_invoiced_record);
7548: x_cascaded_table(n).error_status := quantity_invoiced_record.error_record.error_status;
7549: x_cascaded_table(n).error_message := quantity_invoiced_record.error_record.error_message;
7550: rcv_error_pkg.log_interface_message(x_cascaded_table(n).error_status,
7551: 'UNIT_OF_MEASURE',
7582: IF (g_asn_debug = 'Y') THEN
7583: asn_debug.put_line('Validating UOM');
7584: END IF;
7585:
7586: rcv_transactions_interface_sv1.validate_uom(uom_record);
7587: x_cascaded_table(n).error_status := uom_record.error_record.error_status;
7588: x_cascaded_table(n).error_message := uom_record.error_record.error_message;
7589: rcv_error_pkg.log_interface_message(x_cascaded_table(n).error_status,
7590: 'UNIT_OF_MEASURE',
7659: IF (g_asn_debug = 'Y') THEN
7660: asn_debug.put_line('Validating Item Description ' || x_cascaded_table(n).item_description);
7661: END IF;
7662:
7663: rcv_transactions_interface_sv1.validate_item_description(item_id_record);
7664: x_cascaded_table(n).error_status := item_id_record.error_record.error_status;
7665: x_cascaded_table(n).error_message := item_id_record.error_record.error_message;
7666:
7667: IF (g_asn_debug = 'Y') THEN
7738: IF (g_asn_debug = 'Y') THEN
7739: asn_debug.put_line('Validating Freight Carrier');
7740: END IF;
7741:
7742: rcv_transactions_interface_sv1.validate_freight_carrier(freight_carrier_record);
7743: x_cascaded_table(n).error_status := freight_carrier_record.error_record.error_status;
7744: x_cascaded_table(n).error_message := freight_carrier_record.error_record.error_message;
7745: rcv_error_pkg.log_interface_message(x_cascaded_table(n).error_status,
7746: 'FREIGHT_CARRIER_CODE',
7770: IF (g_asn_debug = 'Y') THEN
7771: asn_debug.put_line('Validating Destination Type Code');
7772: END IF;
7773:
7774: rcv_transactions_interface_sv1.validate_po_lookup_code(po_lookup_code_record);
7775: x_cascaded_table(n).error_status := po_lookup_code_record.error_record.error_status;
7776: x_cascaded_table(n).error_message := po_lookup_code_record.error_record.error_message;
7777: rcv_error_pkg.log_interface_message(x_cascaded_table(n).error_status,
7778: 'DESTINATION_TYPE_CODE',
7819: IF (g_asn_debug = 'Y') THEN
7820: asn_debug.put_line('Validating Ship To Location');
7821: END IF;
7822:
7823: rcv_transactions_interface_sv1.validate_location(location_record);
7824: x_cascaded_table(n).error_status := location_record.error_record.error_status;
7825: x_cascaded_table(n).error_message := location_record.error_record.error_message;
7826: rcv_error_pkg.log_interface_message(x_cascaded_table(n).error_status,
7827: 'SHIP_TO_LOCATION_ID',
7852: IF (g_asn_debug = 'Y') THEN
7853: asn_debug.put_line('Validating Deliver to Person');
7854: END IF;
7855:
7856: rcv_transactions_interface_sv1.validate_employee(employee_record);
7857:
7858: /* Bug:6375015
7859: If the deliver_to_person_id provided is invalid/terminated employee no need
7860: to fail the transaction, as deliver_to_person_id is not the mandatory field.
7946: ELSE
7947:
7948: SELECT rti.inspection_status_code
7949: INTO l_insp_status
7950: FROM rcv_transactions_interface rti
7951: WHERE rti.interface_transaction_id = x_cascaded_table(n).parent_interface_txn_id;
7952:
7953: END IF;
7954:
8015: IF (g_asn_debug = 'Y') THEN
8016: asn_debug.put_line('Validating Deliver To Location');
8017: END IF;
8018:
8019: rcv_transactions_interface_sv1.validate_location(location_record);
8020: x_cascaded_table(n).error_status := location_record.error_record.error_status;
8021: x_cascaded_table(n).error_message := location_record.error_record.error_message;
8022: rcv_error_pkg.log_interface_message(x_cascaded_table(n).error_status,
8023: 'DELIVER_TO_LOCATION_ID',
8265: IF (g_asn_debug = 'Y') THEN
8266: asn_debug.put_line('Validating Locator');
8267: END IF;
8268:
8269: rcv_transactions_interface_sv1.validate_locator(locator_record);
8270: rcv_transactions_interface_sv1.validate_project_locator(locator_record); --Bug13844195
8271: x_cascaded_table(n).error_status := locator_record.error_record.error_status;
8272: x_cascaded_table(n).error_message := locator_record.error_record.error_message;
8273:
8266: asn_debug.put_line('Validating Locator');
8267: END IF;
8268:
8269: rcv_transactions_interface_sv1.validate_locator(locator_record);
8270: rcv_transactions_interface_sv1.validate_project_locator(locator_record); --Bug13844195
8271: x_cascaded_table(n).error_status := locator_record.error_record.error_status;
8272: x_cascaded_table(n).error_message := locator_record.error_record.error_message;
8273:
8274: -- Bug 13817635 added
8279: -- Bug 13817635 ended
8280:
8281: /* Bug 5468345.
8282: * If the user has specified locator_id or locator_num and org/sub/item is not under locator control,
8283: * then the rcv_transactions_interface_sv1.validate_locator procedure nulls out the locator_id.
8284: * We need to copy this back to the x_cascaded_table. This used to cause data corruption.
8285: * Changed the code to null it.
8286: */
8287:
8373: IF (g_asn_debug = 'Y') THEN
8374: asn_debug.put_line('Validating Tax Code');
8375: END IF;
8376:
8377: rcv_transactions_interface_sv1.validate_tax_code(tax_name_record);
8378: x_cascaded_table(n).error_status := tax_name_record.error_record.error_status;
8379: x_cascaded_table(n).error_message := tax_name_record.error_record.error_message;
8380: rcv_error_pkg.log_interface_message(x_cascaded_table(n).error_status,
8381: 'TAX_NAME',
8404: IF (g_asn_debug = 'Y') THEN
8405: asn_debug.put_line('Validating Country of Origin Code');
8406: END IF;
8407:
8408: rcv_transactions_interface_sv1.validate_country_of_origin(country_of_origin_record);
8409: x_cascaded_table(n).error_status := country_of_origin_record.error_record.error_status;
8410: x_cascaded_table(n).error_message := country_of_origin_record.error_record.error_message;
8411: rcv_error_pkg.log_interface_message(x_cascaded_table(n).error_status,
8412: 'COUNTRY_OF_ORIGIN_CODE',
8436: IF (g_asn_debug = 'Y') THEN
8437: asn_debug.put_line('Validating ASL');
8438: END IF;
8439:
8440: rcv_transactions_interface_sv1.validate_asl(asl_record);
8441: x_cascaded_table(n).error_status := asl_record.error_record.error_status;
8442: x_cascaded_table(n).error_message := asl_record.error_record.error_message;
8443: rcv_error_pkg.log_interface_message(x_cascaded_table(n).error_status,
8444: 'SUPPLY_AGREEMENT_FLAG',
8473: IF (g_asn_debug = 'Y') THEN
8474: asn_debug.put_line('Validating Cum Qty Shipped');
8475: END IF;
8476:
8477: rcv_transactions_interface_sv1.validate_cum_quantity_shipped(cum_quantity_record);
8478: x_cascaded_table(n).error_status := cum_quantity_record.error_record.error_status;
8479: x_cascaded_table(n).error_message := cum_quantity_record.error_record.error_message;
8480: rcv_error_pkg.log_interface_message(x_cascaded_table(n).error_status,
8481: 'VENDOR_CUM_SHIPPED_QTY',
8548: /* End of 3210821. */
8549: ref_integrity_record.error_record.error_status := 'S';
8550: ref_integrity_record.error_record.error_message := NULL;
8551: --replacing call with a validate_ref_integ call with the method in thid package
8552: -- rcv_transactions_interface_sv1.validate_ref_integ (ref_integrity_record,X_header_record);
8553: validate_ref_integ(ref_integrity_record, x_header_record);
8554:
8555: IF (g_asn_debug = 'Y') THEN
8556: asn_debug.put_line('After Validating Ref Integ: ' || ref_integrity_record.error_record.error_status);
8768: asn_debug.put_line('Primary UOM for substitute item is ' || x_primary_uom);
8769: END IF;
8770:
8771: IF x_primary_uom <> x_cascaded_table(n).primary_unit_of_measure THEN --{
8772: prim_uom_qty := rcv_transactions_interface_sv.convert_into_correct_qty(x_cascaded_table(n).quantity,
8773: x_cascaded_table(n).unit_of_measure,
8774: x_cascaded_table(n).item_id,
8775: x_primary_uom
8776: );
8847: IF (g_asn_debug = 'Y') THEN
8848: asn_debug.put_line('Validating ASBN for Consigned PO');
8849: END IF;
8850:
8851: rcv_transactions_interface_sv1.validate_consigned_po(l_consigned_po_rec);
8852:
8853: IF (g_asn_debug = 'Y') THEN
8854: asn_debug.put_line('After Validating ASBN for Consigned PO');
8855: END IF;
8877: IF (g_asn_debug = 'Y') THEN
8878: asn_debug.put_line('Validating Consumption PO');
8879: END IF;
8880:
8881: rcv_transactions_interface_sv1.validate_consumption_po(l_consumption_po_rec);
8882:
8883: IF (g_asn_debug = 'Y') THEN
8884: asn_debug.put_line('After Validating Consumption PO');
8885: END IF;
8906: IF (g_asn_debug = 'Y') THEN
8907: asn_debug.put_line('Validating Consumption Release');
8908: END IF;
8909:
8910: rcv_transactions_interface_sv1.validate_consumption_release(l_consumption_release_rec);
8911:
8912: IF (g_asn_debug = 'Y') THEN
8913: asn_debug.put_line('After Validating Consumption Release');
8914: END IF;
9135: l_wms_msg_count NUMBER;
9136: l_wms_msg_data VARCHAR2(400);
9137: lorgid NUMBER; -- For Bug 2110031
9138: --WMS lot serial support
9139: l_orig_trx_id rcv_transactions_interface.interface_transaction_id%TYPE;
9140: l_ls_table inv_rcv_integration_apis.child_rec_tb_tp;
9141: l_ls_table_idx BINARY_INTEGER := 0;
9142: l_api_version NUMBER := 1.0;
9143: l_return_status VARCHAR2(1);
9142: l_api_version NUMBER := 1.0;
9143: l_return_status VARCHAR2(1);
9144: l_msg_count NUMBER;
9145: l_msg_data fnd_new_messages.MESSAGE_TEXT%TYPE;
9146: l_group_id rcv_transactions_interface.GROUP_ID%TYPE;
9147: l_header_interface_id rcv_transactions_interface.header_interface_id%TYPE;
9148: l_interface_transaction_id rcv_transactions_interface.interface_transaction_id%TYPE;
9149: l_return BOOLEAN := TRUE;
9150: l_message VARCHAR2(2000);
9143: l_return_status VARCHAR2(1);
9144: l_msg_count NUMBER;
9145: l_msg_data fnd_new_messages.MESSAGE_TEXT%TYPE;
9146: l_group_id rcv_transactions_interface.GROUP_ID%TYPE;
9147: l_header_interface_id rcv_transactions_interface.header_interface_id%TYPE;
9148: l_interface_transaction_id rcv_transactions_interface.interface_transaction_id%TYPE;
9149: l_return BOOLEAN := TRUE;
9150: l_message VARCHAR2(2000);
9151: l_trx_type rcv_transactions_interface.transaction_type%TYPE;
9144: l_msg_count NUMBER;
9145: l_msg_data fnd_new_messages.MESSAGE_TEXT%TYPE;
9146: l_group_id rcv_transactions_interface.GROUP_ID%TYPE;
9147: l_header_interface_id rcv_transactions_interface.header_interface_id%TYPE;
9148: l_interface_transaction_id rcv_transactions_interface.interface_transaction_id%TYPE;
9149: l_return BOOLEAN := TRUE;
9150: l_message VARCHAR2(2000);
9151: l_trx_type rcv_transactions_interface.transaction_type%TYPE;
9152: l_src_doc_code rcv_transactions_interface.source_document_code%TYPE;
9147: l_header_interface_id rcv_transactions_interface.header_interface_id%TYPE;
9148: l_interface_transaction_id rcv_transactions_interface.interface_transaction_id%TYPE;
9149: l_return BOOLEAN := TRUE;
9150: l_message VARCHAR2(2000);
9151: l_trx_type rcv_transactions_interface.transaction_type%TYPE;
9152: l_src_doc_code rcv_transactions_interface.source_document_code%TYPE;
9153: l_ship_head_id rcv_transactions_interface.shipment_header_id%TYPE;
9154: l_count NUMBER;
9155: l_cnt NUMBER;
9148: l_interface_transaction_id rcv_transactions_interface.interface_transaction_id%TYPE;
9149: l_return BOOLEAN := TRUE;
9150: l_message VARCHAR2(2000);
9151: l_trx_type rcv_transactions_interface.transaction_type%TYPE;
9152: l_src_doc_code rcv_transactions_interface.source_document_code%TYPE;
9153: l_ship_head_id rcv_transactions_interface.shipment_header_id%TYPE;
9154: l_count NUMBER;
9155: l_cnt NUMBER;
9156: x_fail_all_lines VARCHAR2(1):='N';
9149: l_return BOOLEAN := TRUE;
9150: l_message VARCHAR2(2000);
9151: l_trx_type rcv_transactions_interface.transaction_type%TYPE;
9152: l_src_doc_code rcv_transactions_interface.source_document_code%TYPE;
9153: l_ship_head_id rcv_transactions_interface.shipment_header_id%TYPE;
9154: l_count NUMBER;
9155: l_cnt NUMBER;
9156: x_fail_all_lines VARCHAR2(1):='N';
9157: BEGIN
9160: e_o_t := v_trans_tab.LAST;
9161: v_current_interface_id := -999;
9162: v_prior_interface_id := -999;
9163:
9164: /* Delete only once from the rcv_transactions_interface table for every
9165: group of pl/sql table rows that have the same interface id */
9166: FOR j IN i .. e_o_t LOOP
9167: asn_debug.put_line('Transaction Type ' || v_trans_tab(j).transaction_type);
9168: v_current_interface_id := v_trans_tab(j).interface_transaction_id;
9185: asn_debug.put_line('lorgid ' || TO_CHAR(lorgid));
9186:
9187: IF v_current_interface_id <> v_prior_interface_id THEN
9188: asn_debug.put_line('Handle the original interface id ' || TO_CHAR(v_trans_tab(j).interface_transaction_id));
9189: asn_debug.put_line('delete from rcv_transactions_interface rowid ' || v_trans_tab(j).row_id);
9190: asn_debug.put_line('matching_basis=' || v_trans_tab(j).matching_basis);
9191:
9192: /* WMS Changes */
9193: IF v_trans_tab(j).matching_basis <> 'AMOUNT' THEN
9564: asn_debug.put_line('Interface Id ' || TO_CHAR(v_trans_tab(j).interface_transaction_id));
9565: v_prior_interface_id := v_current_interface_id;
9566: v_trans_tab.DELETE(j);
9567: ELSE
9568: asn_debug.put_line('insert into rcv_transactions_interface with new id ');
9569: /* Since we are inserting 1-> many rows need to generate a new interface id */
9570: v_trans_tab(j).interface_transaction_id := NULL;
9571: x_rowid := NULL;
9572:
10001: /* We error out the rti row here.
10002: * Erroring out the other lpm_group rows
10003: * or ASN rows are taken care in RCVPREPB.pls.
10004: */
10005: UPDATE rcv_transactions_interface
10006: SET processing_status_code = 'ERROR'
10007: WHERE interface_transaction_id = l_ls_table(k).new_interface_trx_id;
10008:
10009: SELECT GROUP_ID,
10017: l_interface_transaction_id,
10018: l_trx_type,
10019: l_src_doc_code,
10020: l_ship_head_id
10021: FROM rcv_transactions_interface
10022: WHERE interface_transaction_id = l_ls_table(k).new_interface_trx_id;
10023:
10024: fnd_profile.get('RCV_FAIL_IF_LINE_FAILS', x_fail_all_lines);
10025: IF ( x_fail_all_lines='Y') THEN
10041:
10042: IF l_header_interface_id IS NOT NULL THEN
10043: SELECT count(1)
10044: INTO l_count
10045: FROM rcv_transactions_interface
10046: WHERE header_interface_id=l_header_interface_id;
10047:
10048: SELECT count(1)
10049: INTO l_cnt
10046: WHERE header_interface_id=l_header_interface_id;
10047:
10048: SELECT count(1)
10049: INTO l_cnt
10050: FROM rcv_transactions_interface
10051: WHERE header_interface_id=l_header_interface_id
10052: AND processing_status_code='ERROR';
10053:
10054: IF (l_count=l_cnt AND l_trx_type in ('SHIP','RECEIVE') AND l_src_doc_code='PO') THEN
10068:
10069: /* update rcv_headers_interface rhi
10070: set processing_status_code='ERROR'
10071: where header_interface_id=l_header_interface_id
10072: and not exists(select 1 from rcv_transactions_interface
10073: where header_interface_id=l_header_interface_id
10074: and processing_status_code='RUNNING');
10075:
10076: delete from rcv_shipment_headers rsh
10074: and processing_status_code='RUNNING');
10075:
10076: delete from rcv_shipment_headers rsh
10077: where
10078: and not exists(select 1 from rcv_transactions_interface
10079: where shipment_header_id=rsh.shipment_header_id
10080: and processing_status_code='RUNNING');*/
10081:
10082: END IF; --} if split_lot_serial failed
10150: ) IS
10151: derive_values_from_plsql_table BOOLEAN := FALSE;
10152: cascaded_table_index NUMBER;
10153: derive_values_from_rti BOOLEAN := FALSE;
10154: l_processing_status_code rcv_transactions_interface.processing_status_code%TYPE;
10155: x_error_status VARCHAR2(1);
10156: e_unexpected_error EXCEPTION;
10157:
10158: /* Bug# 9855182 */
10155: x_error_status VARCHAR2(1);
10156: e_unexpected_error EXCEPTION;
10157:
10158: /* Bug# 9855182 */
10159: l_transaction_type rcv_transactions_interface.transaction_type%TYPE;
10160: l_auto_transact_code rcv_transactions_interface.auto_transact_code%TYPE;
10161: /* Bug# 9855182 End*/
10162: BEGIN
10163: x_error_status := rcv_error_pkg.g_ret_sts_error;
10156: e_unexpected_error EXCEPTION;
10157:
10158: /* Bug# 9855182 */
10159: l_transaction_type rcv_transactions_interface.transaction_type%TYPE;
10160: l_auto_transact_code rcv_transactions_interface.auto_transact_code%TYPE;
10161: /* Bug# 9855182 End*/
10162: BEGIN
10163: x_error_status := rcv_error_pkg.g_ret_sts_error;
10164:
10260: /* Bug# 9855182 */
10261: l_transaction_type,
10262: l_auto_transact_code
10263: /* Bug# 9855182 End*/
10264: FROM rcv_transactions_interface
10265: WHERE interface_transaction_id = x_cascaded_table(n).parent_interface_txn_id;
10266: EXCEPTION
10267: WHEN OTHERS THEN
10268: IF (g_asn_debug = 'Y') THEN
10354: x_progress VARCHAR2(3);
10355: x_to_organization_code VARCHAR2(5);
10356: x_converted_trx_qty NUMBER := 0;
10357: transaction_ok BOOLEAN := FALSE;
10358: x_expected_date rcv_transactions_interface.expected_receipt_date%TYPE;
10359: high_range_date DATE;
10360: low_range_date DATE;
10361: rows_fetched NUMBER := 0;
10362: x_tolerable_qty NUMBER := 0;
10495: 0 bom_resource_id,
10496: 0 ussgl_transaction_code,
10497: rti.quantity qty,
10498: rti.interface_available_qty
10499: FROM rcv_transactions_interface rti
10500: WHERE interface_transaction_id = v_parent_inter_trx_id;
10501:
10502: /* Bug 3292329.
10503: We used to use the same cursor for single deliver and
10613: pod.bom_resource_id,
10614: pod.ussgl_transaction_code,
10615: rti.quantity qty,
10616: rti.interface_available_qty
10617: FROM rcv_transactions_interface rti,
10618: po_distributions pod
10619: WHERE interface_transaction_id = v_parent_inter_trx_id
10620: AND pod.line_location_id = rti.po_line_location_id --Bug:5354379
10621: AND pod.po_header_id = rti.po_header_id
10721: pod.bom_resource_id,
10722: pod.ussgl_transaction_code,
10723: rti.quantity qty,
10724: rti.interface_available_qty
10725: FROM rcv_transactions_interface rti,
10726: po_distributions pod
10727: WHERE interface_transaction_id = v_parent_inter_trx_id
10728: AND pod.distribution_num = x_dist_num
10729: AND pod.po_header_id = rti.po_header_id
10797: v_rcv_transaction_id NUMBER,
10798: v_interface_transaction_id NUMBER
10799: ) IS
10800: SELECT nvl(quantity,0) quantity,unit_of_measure
10801: FROM rcv_transactions_interface
10802: WHERE (transaction_status_code = 'PENDING'
10803: AND processing_status_code <> 'ERROR')
10804: AND transaction_type = 'DELIVER'
10805: AND group_id = v_group_id
10973: END IF;
10974: EXCEPTION
10975: WHEN OTHERS THEN
10976: IF (g_asn_debug = 'Y') THEN
10977: asn_debug.put_line('wrong po_distribution_id entered in rcv_transactions_interface');
10978: END IF;
10979: END;
10980: END IF;
10981:
11044: SELECT COUNT(*)
11045: INTO l_num_of_distributions
11046: FROM po_distributions
11047: WHERE line_location_id = (SELECT po_line_location_id
11048: FROM rcv_transactions_interface
11049: WHERE interface_transaction_id = x_cascaded_table(n).parent_interface_txn_id);
11050:
11051: IF (g_asn_debug = 'Y') THEN
11052: asn_debug.put_line('l_num_of_distributions ' || l_num_of_distributions);
11085:
11086: EXCEPTION
11087: WHEN OTHERS THEN
11088: IF (g_asn_debug = 'Y') THEN
11089: asn_debug.put_line('wrong po_distribution_id entered in rcv_transactions_interface');
11090: END IF;
11091: END;
11092: END IF;
11093: -- } bug 5354379
11140: -- the following steps will create a set of rows linking the line_record with
11141: -- its corresponding po_line_location rows until the quantity value from
11142: -- the asn is consumed. (Cascade)
11143:
11144: /* 2119137 : If the user populates rcv_transactions_interface
11145: with po_line_id, then ROI errors out with
11146: RCV_ASN_NO_PO_LINE_LOCATION_ID when the docment_line_num
11147: is not provided for one time items. Modified the "if" criteria in
11148: such a way that the ROI validation does'nt error out when
11458: END IF;
11459:
11460: SELECT po_line_id,po_header_id
11461: INTO l_service_po_line_id,l_po_header_id
11462: FROM rcv_transactions_interface
11463: WHERE interface_transaction_id = x_cascaded_table(n).parent_interface_txn_id;
11464: END IF;
11465:
11466: IF (g_asn_debug = 'Y') THEN
11795: transaction_type
11796: INTO l_parent_interface_txn_id,
11797: l_parent_transaction_id,
11798: l_transaction_type
11799: FROM rcv_transactions_interface
11800: WHERE interface_transaction_id = l_interface_txn_id;
11801:
11802: IF l_parent_transaction_id IS NOT NULL THEN--{
11803: --parent txn is available in rcv_transactions table
11896: x_cascaded_table(n).error_status := 'E';
11897: ELSIF l_loop_exit_status = 'S' THEN --{ l_loop_exit_status = 'S'
11898: --Check where to get the AVAILABLE qty for this DELIVER txn
11899: -- if l_parent_receive_txn_in_rt is 'Y', Fetch the AVAILABLE qty from mtl_supply
11900: -- if l_parent_receive_txn_in_rt is 'N', Fetch the AVAILABLE qty from rcv_transactions_interface
11901: IF l_parent_receive_txn_in_rt = 'Y' THEN--{ l_parent_receive_txn_in_rt = 'Y'
11902: --Fetch AVAILABLE qty from mtl_supply using l_parent_transaction_id
11903:
11904: BEGIN --{
12001: --based on the shipment_level quantity available in mtl_supply
12002: BEGIN--{
12003: SELECT nvl(sum(quantity),0)
12004: INTO l_total_deliver_quantity
12005: FROM rcv_transactions_interface
12006: WHERE (transaction_status_code = 'PENDING'
12007: AND processing_status_code <> 'ERROR')
12008: AND transaction_type = 'DELIVER'
12009: AND group_id = x_cascaded_table(n).group_id
12072: rcv_error_pkg.log_interface_error('QUANTITY',FALSE);
12073: END IF;--} x_cascaded_table(n).quantity > (l_distribution_qty - l_total_deliver_quantity)
12074: --} l_parent_receive_txn_in_rt = 'Y'
12075: ELSIF l_parent_receive_txn_in_rt = 'N' THEN--{ l_parent_receive_txn_in_rt = 'N'
12076: --Fetch the AVAILABLE qty from rcv_transactions_interface using l_parent_interface_txn_id
12077: IF (g_asn_debug = 'Y') THEN
12078: asn_debug.put_line('Fetching AVAILABLE qty from fetched RECEIVE interface_txn_id....');
12079: END IF;
12080:
12089:
12090: BEGIN --{
12091: SELECT nvl(sum(quantity),0)
12092: INTO l_total_receiving_quantity
12093: FROM rcv_transactions_interface
12094: WHERE (transaction_status_code = 'PENDING'
12095: AND processing_status_code <> 'ERROR')
12096: AND transaction_type = 'RECEIVE'
12097: AND auto_transact_code <> 'DELIVER'
12118:
12119: BEGIN
12120: SELECT nvl(sum(quantity),0)
12121: INTO l_total_deliver_quantity
12122: FROM rcv_transactions_interface
12123: WHERE (transaction_status_code = 'PENDING'
12124: AND processing_status_code <> 'ERROR')
12125: AND transaction_type = 'DELIVER'
12126: AND group_id = x_cascaded_table(n).group_id
12141:
12142: --Fetch the receive quantity for the 'RECEIVE'txn of the current DELIVER txn
12143: SELECT quantity
12144: INTO l_receive_quantity
12145: FROM rcv_transactions_interface
12146: WHERE interface_transaction_id = l_parent_interface_txn_id;
12147:
12148: IF (g_asn_debug = 'Y') THEN
12149: asn_debug.put_line('Receive qty for RECEIVE txn...'||l_receive_quantity);
12151: END IF;
12152: --Fetch the deliver quantity for the current 'DELIVER'txn
12153: SELECT quantity
12154: INTO l_deliver_quantity
12155: FROM rcv_transactions_interface
12156: WHERE interface_transaction_id = x_cascaded_table(n).interface_transaction_id;
12157:
12158: IF (g_asn_debug = 'Y') THEN
12159: asn_debug.put_line('Deliver qty for DELIVER txn...'||l_deliver_quantity);
12822: rti.oe_order_line_id,
12823: rti.customer_id,
12824: rti.customer_site_id,
12825: rti.source_document_code
12826: FROM rcv_transactions_interface rti
12827: WHERE interface_transaction_id = v_parent_inter_trx_id;
12828:
12829: default_po_transfer_info po_transfer%ROWTYPE;
12830: x_progress VARCHAR2(3);
13187: END IF;
13188:
13189: SELECT po_line_id,po_header_id
13190: INTO x_cascaded_table(n).po_line_id,l_po_header_id
13191: FROM rcv_transactions_interface
13192: WHERE interface_transaction_id = x_cascaded_table(n).parent_interface_txn_id;
13193: END IF;
13194:
13195: IF (g_asn_debug = 'Y') THEN
13312: x_progress VARCHAR2(3);
13313: x_to_organization_code VARCHAR2(5);
13314: x_converted_trx_qty NUMBER := 0;
13315: transaction_ok BOOLEAN := FALSE;
13316: x_expected_date rcv_transactions_interface.expected_receipt_date%TYPE;
13317: high_range_date DATE;
13318: low_range_date DATE;
13319: rows_fetched NUMBER := 0;
13320: x_tolerable_qty NUMBER := 0;
13407: AND rt3.transaction_type = 'MATCH'
13408: AND rt3.parent_transaction_id = rt2.transaction_id)
13409: AND NOT EXISTS(SELECT 'rtv to unprocessed matched unordered receipt'
13410: FROM rcv_transactions rt2,
13411: rcv_transactions_interface rti
13412: WHERE rt2.transaction_id = rt.parent_transaction_id
13413: AND rt2.transaction_type = 'UNORDERED'
13414: AND rti.transaction_type = 'MATCH'
13415: AND rti.parent_transaction_id = rt2.transaction_id)
13422: FROM rcv_transactions rt2
13423: WHERE rt2.parent_transaction_id = rt.transaction_id
13424: AND rt2.transaction_type = 'MATCH')
13425: AND NOT EXISTS(SELECT 'unprocessed matched rows'
13426: FROM rcv_transactions_interface rti
13427: WHERE rti.parent_transaction_id = rt.transaction_id
13428: AND rti.transaction_type = 'MATCH')
13429: )
13430: )
13474: rti.wip_repetitive_schedule_id,
13475: rti.wip_line_id,
13476: rti.bom_resource_id,
13477: rti.ussgl_transaction_code
13478: FROM rcv_transactions_interface rti
13479: WHERE interface_transaction_id = v_parent_interface_txn_id;
13480:
13481: x_po_correctrec po_correct%ROWTYPE;
13482: x_temp_parent_trx_qty NUMBER := 0;
13498: l_exist2 VARCHAR2(30);
13499: l_destination_type_code rcv_transactions.destination_type_code%TYPE;
13500: l_source_document_code rcv_transactions.source_document_code%TYPE;
13501: /* Bug#5369121 */
13502: l_primary_uom rcv_transactions_interface.unit_of_measure%TYPE;
13503: l_transaction_uom rcv_transactions.unit_of_measure%TYPE;
13504: l_interface_quantity NUMBER;
13505: l_interface_qty_in_trx_uom NUMBER;
13506: l_item_id NUMBER;
13607: -- the following steps will create a set of rows linking the line_record with
13608: -- its corresponding po_line_location rows until the quantity value from
13609: -- the asn is consumed. (Cascade)
13610:
13611: /* 2119137 : If the user populates rcv_transactions_interface
13612: with po_line_id, then ROI errors out with
13613: RCV_ASN_NO_PO_LINE_LOCATION_ID when the docment_line_num
13614: is not provided for one time items. Modified the "if" criteria in
13615: such a way that the ROI validation does'nt error out when
13849: END IF;
13850:
13851: temp_cascaded_table(current_n).quantity := temp_cascaded_table(current_n).quantity + x_remaining_quantity;
13852: temp_cascaded_table(current_n).primary_quantity := temp_cascaded_table(current_n).primary_quantity
13853: + rcv_transactions_interface_sv.convert_into_correct_qty(x_remaining_quantity,
13854: temp_cascaded_table(1).unit_of_measure,
13855: temp_cascaded_table(1).item_id,
13856: temp_cascaded_table(1).primary_unit_of_measure
13857: );
14051: AND rt2.transaction_type = 'MATCH';
14052:
14053: SELECT MAX('record_exist')
14054: INTO l_exist1
14055: FROM rcv_transactions_interface rti
14056: WHERE rti.parent_transaction_id = temp_cascaded_table(current_n).parent_transaction_id
14057: AND rti.transaction_type = 'MATCH';
14058:
14059: IF l_exist = 'record_exist' THEN --{
14081:
14082: SELECT MAX('record_exist')
14083: INTO l_exist1
14084: FROM rcv_transactions rt2,
14085: rcv_transactions_interface rti
14086: WHERE rt2.transaction_id = l_grand_parent_trx_id
14087: AND rt2.transaction_type = 'UNORDERED'
14088: AND rti.transaction_type = 'MATCH'
14089: AND rti.parent_transaction_id = rt2.transaction_id;
14225: AND rt.transaction_id = x_po_correctrec.rcv_transaction_id;
14226:
14227: SELECT NVL(SUM(interface_transaction_qty),0)
14228: INTO l_interface_quantity
14229: FROM rcv_transactions_interface
14230: WHERE (transaction_status_code = 'PENDING'
14231: AND processing_status_code <> 'ERROR')
14232: --bug# 9583207 to comment group_id condition
14233: --AND group_id = temp_cascaded_table(current_n).group_id
14514: x_progress VARCHAR2(3);
14515: x_to_organization_code VARCHAR2(5);
14516: x_converted_trx_amt NUMBER := 0;
14517: transaction_ok BOOLEAN := FALSE;
14518: x_expected_date rcv_transactions_interface.expected_receipt_date%TYPE;
14519: high_range_date DATE;
14520: low_range_date DATE;
14521: rows_fetched NUMBER := 0;
14522: x_tolerable_amt NUMBER := 0;
14582: AND rt3.transaction_type = 'MATCH'
14583: AND rt3.parent_transaction_id = rt2.transaction_id)
14584: AND NOT EXISTS(SELECT 'rtv to unprocessed matched unordered receipt'
14585: FROM rcv_transactions rt2,
14586: rcv_transactions_interface rti
14587: WHERE rt2.transaction_id = rt.parent_transaction_id
14588: AND rt2.transaction_type = 'UNORDERED'
14589: AND rti.transaction_type = 'MATCH'
14590: AND rti.parent_transaction_id = rt2.transaction_id)
14597: FROM rcv_transactions rt2
14598: WHERE rt2.parent_transaction_id = rt.transaction_id
14599: AND rt2.transaction_type = 'MATCH')
14600: AND NOT EXISTS(SELECT 'unprocessed matched rows'
14601: FROM rcv_transactions_interface rti
14602: WHERE rti.parent_transaction_id = rt.transaction_id
14603: AND rti.transaction_type = 'MATCH')
14604: )
14605: )
14633: rti.deliver_to_location_id,
14634: rti.po_distribution_id,
14635: rti.destination_type_code,
14636: rti.ussgl_transaction_code
14637: FROM rcv_transactions_interface rti
14638: WHERE interface_transaction_id = v_parent_interface_txn_id;
14639:
14640: x_po_correctrec po_correct%ROWTYPE;
14641: x_temp_parent_trx_amt NUMBER := 0;
14761: -- the following steps will create a set of rows linking the line_record with
14762: -- its corresponding po_line_location rows until the quantity value from
14763: -- the asn is consumed. (Cascade)
14764:
14765: /* 2119137 : If the user populates rcv_transactions_interface
14766: with po_line_id, then ROI errors out with
14767: RCV_ASN_NO_PO_LINE_LOCATION_ID when the docment_line_num
14768: is not provided for one time items. Modified the "if" criteria in
14769: such a way that the ROI validation does'nt error out when
15202: AND rt2.transaction_type = 'MATCH';
15203:
15204: SELECT MAX('record_exist')
15205: INTO l_exist1
15206: FROM rcv_transactions_interface rti
15207: WHERE rti.parent_transaction_id = temp_cascaded_table(current_n).parent_transaction_id
15208: AND rti.transaction_type = 'MATCH';
15209:
15210: IF l_exist = 'record_exist' THEN --{
15232:
15233: SELECT MAX('record_exist')
15234: INTO l_exist1
15235: FROM rcv_transactions rt2,
15236: rcv_transactions_interface rti
15237: WHERE rt2.transaction_id = l_grand_parent_trx_id
15238: AND rt2.transaction_type = 'UNORDERED'
15239: AND rti.transaction_type = 'MATCH'
15240: AND rti.parent_transaction_id = rt2.transaction_id;
15607: AND rt3.transaction_type = 'MATCH'
15608: AND rt3.parent_transaction_id = rt2.transaction_id)
15609: AND NOT EXISTS(SELECT 'rtv to unprocessed matched unordered receipt'
15610: FROM rcv_transactions rt2,
15611: rcv_transactions_interface rti
15612: WHERE rt2.transaction_id = rt.parent_transaction_id
15613: AND rt2.transaction_type = 'UNORDERED'
15614: AND rti.transaction_type = 'MATCH'
15615: AND rti.parent_transaction_id = rt2.transaction_id)
15622: FROM rcv_transactions rt2
15623: WHERE rt2.parent_transaction_id = rt.transaction_id
15624: AND rt2.transaction_type = 'MATCH')
15625: AND NOT EXISTS(SELECT 'unprocessed matched rows'
15626: FROM rcv_transactions_interface rti
15627: WHERE rti.parent_transaction_id = rt.transaction_id
15628: AND rti.transaction_type = 'MATCH')
15629: )
15630: )
15670: rti.customer_id,
15671: rti.customer_site_id,
15672: rti.source_document_code,
15673: rti.job_id
15674: FROM rcv_transactions_interface rti
15675: WHERE interface_transaction_id = v_parent_inter_trx_id;
15676:
15677: default_po_correct_info po_correct%ROWTYPE;
15678: x_progress VARCHAR2(3);
15995: IF (g_asn_debug = 'Y') THEN
15996: asn_debug.put_line('Validating Source Type Code');
15997: END IF;
15998:
15999: rcv_transactions_interface_sv1.validate_po_lookup_code(po_lookup_code_record);
16000: x_cascaded_table(n).error_status := po_lookup_code_record.error_record.error_status;
16001: x_cascaded_table(n).error_message := po_lookup_code_record.error_record.error_message;
16002: rcv_error_pkg.log_interface_message(x_cascaded_table(n).error_status,
16003: 'SOURCE_TYPE_CODE',
16031: IF (g_asn_debug = 'Y') THEN
16032: asn_debug.put_line('Validating Destination Type Code');
16033: END IF;
16034:
16035: rcv_transactions_interface_sv1.validate_po_lookup_code(po_lookup_code_record);
16036: x_cascaded_table(n).error_status := po_lookup_code_record.error_record.error_status;
16037: x_cascaded_table(n).error_message := po_lookup_code_record.error_record.error_message;
16038: rcv_error_pkg.log_interface_message(x_cascaded_table(n).error_status,
16039: 'TRANSACTION_TYPE_CODE',
16067: IF (g_asn_debug = 'Y') THEN
16068: asn_debug.put_line('Validating INSPECTION STATUS ');
16069: END IF;
16070:
16071: rcv_transactions_interface_sv1.validate_po_lookup_code(po_lookup_code_record);
16072: x_cascaded_table(n).error_status := po_lookup_code_record.error_record.error_status;
16073: x_cascaded_table(n).error_message := po_lookup_code_record.error_record.error_message;
16074: rcv_error_pkg.log_interface_message(x_cascaded_table(n).error_status,
16075: 'INSPECTION_STATUS_CODE',
16093: ) IS
16094: /*INVCONV, gml_opm_roi_grp.opm_record_type to be removed , Punit Kumar */
16095: --l_opm_record_type gml_opm_roi_grp.opm_record_type;
16096:
16097: l_att_rec RCV_TRANSACTIONS_INTERFACE_SV1.attributes_record_type;
16098: l_column_name VARCHAR2(50);
16099: l_tokenname1 VARCHAR2(30);
16100: l_tokenvalue1 VARCHAR2(1800);
16101: l_quantity_shipped NUMBER;
16202: l_opm_record_type.qc_grade := x_cascaded_table(n).qc_grade;
16203: l_opm_record_type.rti_id := x_cascaded_table(n).interface_transaction_id;
16204: -- bug# 3664014
16205: -- added some more parameters. validate_opm_parameters API reads these parameters from
16206: -- rcv_transactions_interface table which does hold the latest data. All the latest data
16207: -- are in the cascaded table.
16208:
16209: l_opm_record_type.transaction_type := x_cascaded_table(n).transaction_type;
16210: l_opm_record_type.destination_type_code := x_cascaded_table(n).destination_type_code;
16240: l_transaction_id :=x_cascaded_table(n).interface_transaction_id; /*BUG#10380635 */
16241:
16242:
16243: IF (g_asn_debug = 'Y') THEN
16244: asn_debug.put_line('printing the values before calling RCV_TRANSACTIONS_INTERFACE_SV1.VALIDATE_SECONDARY_PARAMETERS');
16245: asn_debug.put_line('unit_of_measure = ' || l_att_rec.transaction_unit_of_measure);
16246: asn_debug.put_line('secondary unit_of_measure = ' || l_att_rec.secondary_unit_of_measure);
16247: asn_debug.put_line('Quantity = ' || l_att_rec.transaction_quantity);
16248: asn_debug.put_line('Secondary Quantity = ' || l_att_rec.secondary_quantity);
16270:
16271: IF x_cascaded_table(n).item_id IS NOT NULL THEN
16272:
16273: IF (g_asn_debug = 'Y') THEN
16274: asn_debug.put_line('Before calling new API RCV_TRANSACTIONS_INTERFACE_SV1.VALIDATE_SECONDARY_PARAMETERS');
16275: END IF;
16276:
16277:
16278: RCV_TRANSACTIONS_INTERFACE_SV1.VALIDATE_SECONDARY_PARAMETERS (
16274: asn_debug.put_line('Before calling new API RCV_TRANSACTIONS_INTERFACE_SV1.VALIDATE_SECONDARY_PARAMETERS');
16275: END IF;
16276:
16277:
16278: RCV_TRANSACTIONS_INTERFACE_SV1.VALIDATE_SECONDARY_PARAMETERS (
16279: 1.0,
16280: 'F',
16281: x_att_rec =>l_att_rec,
16282: x_return_status =>l_return_status,
16303: x_cascaded_table(n).error_status := l_att_rec.error_record.error_status;
16304: x_cascaded_table(n).error_message := l_att_rec.error_record.error_message;
16305:
16306: IF (g_asn_debug = 'Y') THEN
16307: asn_debug.put_line('After calling new API RCV_TRANSACTIONS_INTERFACE_SV1.VALIDATE_SECONDARY_PARAMETERS');
16308: asn_debug.put_line('return status of RCV_TRANSACTIONS_INTERFACE_SV1.VALIDATE_SECONDARY_PARAMETERS = ' || x_cascaded_table(n).error_status);
16309: asn_debug.put_line('return message of RCV_TRANSACTIONS_INTERFACE_SV1.VALIDATE_SECONDARY_PARAMETERS = ' || x_cascaded_table(n).error_message);
16310: END IF;
16311:
16304: x_cascaded_table(n).error_message := l_att_rec.error_record.error_message;
16305:
16306: IF (g_asn_debug = 'Y') THEN
16307: asn_debug.put_line('After calling new API RCV_TRANSACTIONS_INTERFACE_SV1.VALIDATE_SECONDARY_PARAMETERS');
16308: asn_debug.put_line('return status of RCV_TRANSACTIONS_INTERFACE_SV1.VALIDATE_SECONDARY_PARAMETERS = ' || x_cascaded_table(n).error_status);
16309: asn_debug.put_line('return message of RCV_TRANSACTIONS_INTERFACE_SV1.VALIDATE_SECONDARY_PARAMETERS = ' || x_cascaded_table(n).error_message);
16310: END IF;
16311:
16312:
16305:
16306: IF (g_asn_debug = 'Y') THEN
16307: asn_debug.put_line('After calling new API RCV_TRANSACTIONS_INTERFACE_SV1.VALIDATE_SECONDARY_PARAMETERS');
16308: asn_debug.put_line('return status of RCV_TRANSACTIONS_INTERFACE_SV1.VALIDATE_SECONDARY_PARAMETERS = ' || x_cascaded_table(n).error_status);
16309: asn_debug.put_line('return message of RCV_TRANSACTIONS_INTERFACE_SV1.VALIDATE_SECONDARY_PARAMETERS = ' || x_cascaded_table(n).error_message);
16310: END IF;
16311:
16312:
16313: /*If call fails*/
16312:
16313: /*If call fails*/
16314: IF x_cascaded_table(n).error_status IN ('U' ,'E') THEN
16315: IF (g_asn_debug = 'Y') THEN
16316: asn_debug.put_line('RCV_TRANSACTIONS_INTERFACE_SV1.VALIDATE_SECONDARY_PARAMETERS call failed');
16317: END IF;
16318:
16319: /*log error in po_interface_errors*/
16320: rcv_error_pkg.log_interface_message(
16330: x_cascaded_table(n).secondary_uom_code := l_att_rec.secondary_uom_code;
16331: x_cascaded_table(n).secondary_quantity := l_att_rec.secondary_quantity;
16332:
16333: IF (g_asn_debug = 'Y') THEN
16334: asn_debug.put_line('populating the return values from RCV_TRANSACTIONS_INTERFACE_SV1.VALIDATE_SECONDARY_PARAMETERS');
16335: asn_debug.put_line('secondary unit_of_measure = ' || x_cascaded_table(n).secondary_unit_of_measure);
16336: asn_debug.put_line('Secondary Quantity = ' ||x_cascaded_table(n).secondary_quantity );
16337: asn_debug.put_line('Secondary_uom_code = ' ||x_cascaded_table(n).secondary_uom_code);
16338: END IF;
16371: x_interface_qty OUT NOCOPY NUMBER
16372: ) IS
16373: l_grandparent_interface_trx_id NUMBER;
16374: l_interface_qty NUMBER;
16375: l_parent_trx_type rcv_transactions_interface.transaction_type%TYPE;
16376: l_parent_uom rcv_transactions_interface.unit_of_measure%TYPE;
16377: l_grandparent_uom rcv_transactions_interface.unit_of_measure%TYPE;
16378: test NUMBER;
16379: BEGIN
16372: ) IS
16373: l_grandparent_interface_trx_id NUMBER;
16374: l_interface_qty NUMBER;
16375: l_parent_trx_type rcv_transactions_interface.transaction_type%TYPE;
16376: l_parent_uom rcv_transactions_interface.unit_of_measure%TYPE;
16377: l_grandparent_uom rcv_transactions_interface.unit_of_measure%TYPE;
16378: test NUMBER;
16379: BEGIN
16380: x_interface_qty := 0; --Initialise
16373: l_grandparent_interface_trx_id NUMBER;
16374: l_interface_qty NUMBER;
16375: l_parent_trx_type rcv_transactions_interface.transaction_type%TYPE;
16376: l_parent_uom rcv_transactions_interface.unit_of_measure%TYPE;
16377: l_grandparent_uom rcv_transactions_interface.unit_of_measure%TYPE;
16378: test NUMBER;
16379: BEGIN
16380: x_interface_qty := 0; --Initialise
16381:
16391: INTO l_grandparent_interface_trx_id,
16392: l_parent_trx_type,
16393: l_parent_uom,
16394: l_interface_qty
16395: FROM rcv_transactions_interface
16396: WHERE interface_transaction_id = x_cascaded_table(n).parent_interface_txn_id;
16397: EXCEPTION
16398: /* For a receive transaction, both parent_trx_id and
16399: * interface_parent_trx_id will be null.
16416: SELECT interface_available_qty,
16417: unit_of_measure
16418: INTO l_interface_qty,
16419: l_grandparent_uom
16420: FROM rcv_transactions_interface
16421: WHERE interface_transaction_id = l_grandparent_interface_trx_id;
16422:
16423: /* Should be in terms of parent transaction uom */
16424: x_interface_qty := convert_into_correct_qty(l_interface_qty,
16455: x_interface_amt OUT NOCOPY NUMBER
16456: ) IS
16457: l_grandparent_interface_trx_id NUMBER;
16458: l_interface_amt NUMBER;
16459: l_parent_trx_type rcv_transactions_interface.transaction_type%TYPE;
16460: test NUMBER;
16461: BEGIN
16462: x_interface_amt := 0; --Initialise
16463:
16471: interface_available_amt
16472: INTO l_grandparent_interface_trx_id,
16473: l_parent_trx_type,
16474: l_interface_amt
16475: FROM rcv_transactions_interface
16476: WHERE interface_transaction_id = x_cascaded_table(n).parent_interface_txn_id;
16477: EXCEPTION
16478: /* For a receive transaction, both parent_trx_id and
16479: * interface_parent_trx_id will be null.
16493: IF (l_grandparent_interface_trx_id IS NOT NULL) THEN --{
16494: BEGIN
16495: SELECT interface_available_amt
16496: INTO x_interface_amt
16497: FROM rcv_transactions_interface
16498: WHERE interface_transaction_id = l_grandparent_interface_trx_id;
16499: EXCEPTION
16500: WHEN NO_DATA_FOUND THEN
16501: x_interface_amt := 0;
16523: x_cascaded_table IN OUT NOCOPY rcv_roi_preprocessor.cascaded_trans_tab_type,
16524: n IN BINARY_INTEGER
16525: ) IS
16526: l_grandparent_interface_trx_id NUMBER;
16527: l_parent_trx_type rcv_transactions_interface.transaction_type%TYPE;
16528: l_interface_transaction_qty rcv_transactions_interface.interface_transaction_qty%TYPE := 0;
16529: BEGIN
16530: /* Update interface_available_qty for the current rti row. */
16531: x_cascaded_table(n).interface_available_qty := x_cascaded_table(n).quantity;
16524: n IN BINARY_INTEGER
16525: ) IS
16526: l_grandparent_interface_trx_id NUMBER;
16527: l_parent_trx_type rcv_transactions_interface.transaction_type%TYPE;
16528: l_interface_transaction_qty rcv_transactions_interface.interface_transaction_qty%TYPE := 0;
16529: BEGIN
16530: /* Update interface_available_qty for the current rti row. */
16531: x_cascaded_table(n).interface_available_qty := x_cascaded_table(n).quantity;
16532:
16539: SELECT parent_interface_txn_id,
16540: transaction_type
16541: INTO l_grandparent_interface_trx_id,
16542: l_parent_trx_type
16543: FROM rcv_transactions_interface
16544: WHERE interface_transaction_id = x_cascaded_table(n).parent_interface_txn_id;
16545: EXCEPTION
16546: WHEN NO_DATA_FOUND THEN
16547: /* For an rti of receive or an rti row with parent in RT, select
16555: asn_debug.put_line('update_interace_qty l_parent_trx_type ' || l_parent_trx_type);
16556: END IF;
16557:
16558: IF (x_cascaded_table(n).transaction_type = 'CORRECT') THEN --{
16559: UPDATE rcv_transactions_interface rti
16560: SET rti.interface_available_qty = rti.interface_available_qty + x_cascaded_table(n).quantity
16561: WHERE rti.interface_transaction_id = x_cascaded_table(n).parent_interface_txn_id;
16562:
16563: IF (l_grandparent_interface_trx_id IS NOT NULL) THEN
16560: SET rti.interface_available_qty = rti.interface_available_qty + x_cascaded_table(n).quantity
16561: WHERE rti.interface_transaction_id = x_cascaded_table(n).parent_interface_txn_id;
16562:
16563: IF (l_grandparent_interface_trx_id IS NOT NULL) THEN
16564: UPDATE rcv_transactions_interface rti
16565: SET rti.interface_available_qty = rti.interface_available_qty - x_cascaded_table(n).quantity
16566: WHERE rti.interface_transaction_id = l_grandparent_interface_trx_id;
16567: END IF;
16568: ELSE --}{
16565: SET rti.interface_available_qty = rti.interface_available_qty - x_cascaded_table(n).quantity
16566: WHERE rti.interface_transaction_id = l_grandparent_interface_trx_id;
16567: END IF;
16568: ELSE --}{
16569: UPDATE rcv_transactions_interface rti
16570: SET rti.interface_available_qty = rti.interface_available_qty - x_cascaded_table(n).quantity
16571: WHERE rti.interface_transaction_id = x_cascaded_table(n).parent_interface_txn_id;
16572:
16573: IF ( x_cascaded_table(n).transaction_type = 'RETURN TO RECEIVING'
16571: WHERE rti.interface_transaction_id = x_cascaded_table(n).parent_interface_txn_id;
16572:
16573: IF ( x_cascaded_table(n).transaction_type = 'RETURN TO RECEIVING'
16574: AND l_grandparent_interface_trx_id IS NOT NULL) THEN
16575: UPDATE rcv_transactions_interface rti
16576: SET rti.interface_available_qty = rti.interface_available_qty + x_cascaded_table(n).quantity
16577: WHERE rti.interface_transaction_id = l_grandparent_interface_trx_id;
16578: END IF;
16579: END IF; --}
16606: x_cascaded_table IN OUT NOCOPY rcv_roi_preprocessor.cascaded_trans_tab_type,
16607: n IN BINARY_INTEGER
16608: ) IS
16609: l_grandparent_interface_trx_id NUMBER;
16610: l_parent_trx_type rcv_transactions_interface.transaction_type%TYPE;
16611: l_interface_transaction_amt rcv_transactions_interface.interface_transaction_amt%TYPE := 0;
16612: BEGIN
16613: /* Update interface_available_qty for the current rti row. */
16614: x_cascaded_table(n).interface_available_amt := x_cascaded_table(n).amount;
16607: n IN BINARY_INTEGER
16608: ) IS
16609: l_grandparent_interface_trx_id NUMBER;
16610: l_parent_trx_type rcv_transactions_interface.transaction_type%TYPE;
16611: l_interface_transaction_amt rcv_transactions_interface.interface_transaction_amt%TYPE := 0;
16612: BEGIN
16613: /* Update interface_available_qty for the current rti row. */
16614: x_cascaded_table(n).interface_available_amt := x_cascaded_table(n).amount;
16615:
16622: SELECT parent_interface_txn_id,
16623: transaction_type
16624: INTO l_grandparent_interface_trx_id,
16625: l_parent_trx_type
16626: FROM rcv_transactions_interface
16627: WHERE interface_transaction_id = x_cascaded_table(n).parent_interface_txn_id;
16628: EXCEPTION
16629: WHEN NO_DATA_FOUND THEN
16630: /* For an rti of receive or an rti row with parent in RT, select
16638: asn_debug.put_line('update_interface_amt l_parent_trx_type ' || l_parent_trx_type);
16639: END IF;
16640:
16641: IF (x_cascaded_table(n).transaction_type = 'CORRECT') THEN --{
16642: UPDATE rcv_transactions_interface rti
16643: SET rti.interface_available_amt = rti.interface_available_amt + x_cascaded_table(n).amount
16644: WHERE rti.interface_transaction_id = x_cascaded_table(n).parent_interface_txn_id;
16645:
16646: IF (l_grandparent_interface_trx_id IS NOT NULL) THEN
16643: SET rti.interface_available_amt = rti.interface_available_amt + x_cascaded_table(n).amount
16644: WHERE rti.interface_transaction_id = x_cascaded_table(n).parent_interface_txn_id;
16645:
16646: IF (l_grandparent_interface_trx_id IS NOT NULL) THEN
16647: UPDATE rcv_transactions_interface rti
16648: SET rti.interface_available_amt = rti.interface_available_amt - x_cascaded_table(n).amount
16649: WHERE rti.interface_transaction_id = l_grandparent_interface_trx_id;
16650: END IF;
16651: ELSE --}{
16648: SET rti.interface_available_amt = rti.interface_available_amt - x_cascaded_table(n).amount
16649: WHERE rti.interface_transaction_id = l_grandparent_interface_trx_id;
16650: END IF;
16651: ELSE --}{
16652: UPDATE rcv_transactions_interface rti
16653: SET rti.interface_available_amt = rti.interface_available_amt - x_cascaded_table(n).amount
16654: WHERE rti.interface_transaction_id = x_cascaded_table(n).parent_interface_txn_id;
16655:
16656: IF ( x_cascaded_table(n).transaction_type = 'RETURN TO RECEIVING'
16654: WHERE rti.interface_transaction_id = x_cascaded_table(n).parent_interface_txn_id;
16655:
16656: IF ( x_cascaded_table(n).transaction_type = 'RETURN TO RECEIVING'
16657: AND l_grandparent_interface_trx_id IS NOT NULL) THEN
16658: UPDATE rcv_transactions_interface rti
16659: SET rti.interface_available_amt = rti.interface_available_amt + x_cascaded_table(n).amount
16660: WHERE rti.interface_transaction_id = l_grandparent_interface_trx_id;
16661: END IF;
16662: END IF; --}
16685: rcv_error_pkg.log_interface_error('TRANSACTIONS_INTERFACE_ID', FALSE);
16686: END update_interface_available_amt;
16687:
16688: PROCEDURE update_total_transaction_qty(
16689: p_interface_transaction_id IN rcv_transactions_interface.interface_transaction_id%TYPE,
16690: p_parent_transaction_id IN rcv_transactions_interface.parent_transaction_id%TYPE,
16691: p_parent_interface_txn_id IN rcv_transactions_interface.parent_interface_txn_id%TYPE,
16692: p_primary_quantity IN rcv_transactions_interface.quantity%TYPE,
16693: p_transaction_type IN rcv_transactions_interface.transaction_type%TYPE,
16686: END update_interface_available_amt;
16687:
16688: PROCEDURE update_total_transaction_qty(
16689: p_interface_transaction_id IN rcv_transactions_interface.interface_transaction_id%TYPE,
16690: p_parent_transaction_id IN rcv_transactions_interface.parent_transaction_id%TYPE,
16691: p_parent_interface_txn_id IN rcv_transactions_interface.parent_interface_txn_id%TYPE,
16692: p_primary_quantity IN rcv_transactions_interface.quantity%TYPE,
16693: p_transaction_type IN rcv_transactions_interface.transaction_type%TYPE,
16694: x_interface_transaction_qty OUT NOCOPY rcv_transactions_interface.interface_transaction_qty%TYPE
16687:
16688: PROCEDURE update_total_transaction_qty(
16689: p_interface_transaction_id IN rcv_transactions_interface.interface_transaction_id%TYPE,
16690: p_parent_transaction_id IN rcv_transactions_interface.parent_transaction_id%TYPE,
16691: p_parent_interface_txn_id IN rcv_transactions_interface.parent_interface_txn_id%TYPE,
16692: p_primary_quantity IN rcv_transactions_interface.quantity%TYPE,
16693: p_transaction_type IN rcv_transactions_interface.transaction_type%TYPE,
16694: x_interface_transaction_qty OUT NOCOPY rcv_transactions_interface.interface_transaction_qty%TYPE
16695: ) IS
16688: PROCEDURE update_total_transaction_qty(
16689: p_interface_transaction_id IN rcv_transactions_interface.interface_transaction_id%TYPE,
16690: p_parent_transaction_id IN rcv_transactions_interface.parent_transaction_id%TYPE,
16691: p_parent_interface_txn_id IN rcv_transactions_interface.parent_interface_txn_id%TYPE,
16692: p_primary_quantity IN rcv_transactions_interface.quantity%TYPE,
16693: p_transaction_type IN rcv_transactions_interface.transaction_type%TYPE,
16694: x_interface_transaction_qty OUT NOCOPY rcv_transactions_interface.interface_transaction_qty%TYPE
16695: ) IS
16696: l_child_count NUMBER;
16689: p_interface_transaction_id IN rcv_transactions_interface.interface_transaction_id%TYPE,
16690: p_parent_transaction_id IN rcv_transactions_interface.parent_transaction_id%TYPE,
16691: p_parent_interface_txn_id IN rcv_transactions_interface.parent_interface_txn_id%TYPE,
16692: p_primary_quantity IN rcv_transactions_interface.quantity%TYPE,
16693: p_transaction_type IN rcv_transactions_interface.transaction_type%TYPE,
16694: x_interface_transaction_qty OUT NOCOPY rcv_transactions_interface.interface_transaction_qty%TYPE
16695: ) IS
16696: l_child_count NUMBER;
16697: l_grand_parent_id NUMBER;
16690: p_parent_transaction_id IN rcv_transactions_interface.parent_transaction_id%TYPE,
16691: p_parent_interface_txn_id IN rcv_transactions_interface.parent_interface_txn_id%TYPE,
16692: p_primary_quantity IN rcv_transactions_interface.quantity%TYPE,
16693: p_transaction_type IN rcv_transactions_interface.transaction_type%TYPE,
16694: x_interface_transaction_qty OUT NOCOPY rcv_transactions_interface.interface_transaction_qty%TYPE
16695: ) IS
16696: l_child_count NUMBER;
16697: l_grand_parent_id NUMBER;
16698: BEGIN
16730: END IF;
16731:
16732: SELECT parent_transaction_id
16733: INTO l_grand_parent_id
16734: FROM rcv_transactions_interface
16735: WHERE interface_transaction_id = p_parent_interface_txn_id;
16736:
16737: IF (g_asn_debug = 'Y') THEN
16738: asn_debug.put_line('update_total_transaction_qty l_grand_parent_id ' || l_grand_parent_id);
16739: END IF;
16740:
16741: IF ( (l_grand_parent_id IS NOT NULL)
16742: AND (p_transaction_type = 'CORRECT')) THEN
16743: UPDATE rcv_transactions_interface rti
16744: SET rti.interface_transaction_qty = rti.interface_transaction_qty + p_primary_quantity
16745: WHERE interface_transaction_id = p_parent_interface_txn_id;
16746: END IF;
16747:
16746: END IF;
16747:
16748: IF ( (l_grand_parent_id IS NOT NULL)
16749: AND (p_transaction_type = 'RETURN TO RECEIVING')) THEN
16750: UPDATE rcv_transactions_interface rti
16751: SET rti.interface_transaction_qty = rti.interface_transaction_qty - p_primary_quantity
16752: WHERE interface_transaction_id = p_parent_interface_txn_id;
16753: END IF;
16754: END IF; --}
16759: RAISE;
16760: END update_total_transaction_qty;
16761:
16762: PROCEDURE update_total_transaction_amt(
16763: p_interface_transaction_id IN rcv_transactions_interface.interface_transaction_id%TYPE,
16764: p_parent_transaction_id IN rcv_transactions_interface.parent_transaction_id%TYPE,
16765: p_parent_interface_txn_id IN rcv_transactions_interface.parent_interface_txn_id%TYPE,
16766: p_amount IN rcv_transactions_interface.amount%TYPE,
16767: p_transaction_type IN rcv_transactions_interface.transaction_type%TYPE,
16760: END update_total_transaction_qty;
16761:
16762: PROCEDURE update_total_transaction_amt(
16763: p_interface_transaction_id IN rcv_transactions_interface.interface_transaction_id%TYPE,
16764: p_parent_transaction_id IN rcv_transactions_interface.parent_transaction_id%TYPE,
16765: p_parent_interface_txn_id IN rcv_transactions_interface.parent_interface_txn_id%TYPE,
16766: p_amount IN rcv_transactions_interface.amount%TYPE,
16767: p_transaction_type IN rcv_transactions_interface.transaction_type%TYPE,
16768: x_interface_transaction_amt OUT NOCOPY rcv_transactions_interface.interface_transaction_amt%TYPE
16761:
16762: PROCEDURE update_total_transaction_amt(
16763: p_interface_transaction_id IN rcv_transactions_interface.interface_transaction_id%TYPE,
16764: p_parent_transaction_id IN rcv_transactions_interface.parent_transaction_id%TYPE,
16765: p_parent_interface_txn_id IN rcv_transactions_interface.parent_interface_txn_id%TYPE,
16766: p_amount IN rcv_transactions_interface.amount%TYPE,
16767: p_transaction_type IN rcv_transactions_interface.transaction_type%TYPE,
16768: x_interface_transaction_amt OUT NOCOPY rcv_transactions_interface.interface_transaction_amt%TYPE
16769: ) IS
16762: PROCEDURE update_total_transaction_amt(
16763: p_interface_transaction_id IN rcv_transactions_interface.interface_transaction_id%TYPE,
16764: p_parent_transaction_id IN rcv_transactions_interface.parent_transaction_id%TYPE,
16765: p_parent_interface_txn_id IN rcv_transactions_interface.parent_interface_txn_id%TYPE,
16766: p_amount IN rcv_transactions_interface.amount%TYPE,
16767: p_transaction_type IN rcv_transactions_interface.transaction_type%TYPE,
16768: x_interface_transaction_amt OUT NOCOPY rcv_transactions_interface.interface_transaction_amt%TYPE
16769: ) IS
16770: l_child_count NUMBER;
16763: p_interface_transaction_id IN rcv_transactions_interface.interface_transaction_id%TYPE,
16764: p_parent_transaction_id IN rcv_transactions_interface.parent_transaction_id%TYPE,
16765: p_parent_interface_txn_id IN rcv_transactions_interface.parent_interface_txn_id%TYPE,
16766: p_amount IN rcv_transactions_interface.amount%TYPE,
16767: p_transaction_type IN rcv_transactions_interface.transaction_type%TYPE,
16768: x_interface_transaction_amt OUT NOCOPY rcv_transactions_interface.interface_transaction_amt%TYPE
16769: ) IS
16770: l_child_count NUMBER;
16771: l_grand_parent_id NUMBER;
16764: p_parent_transaction_id IN rcv_transactions_interface.parent_transaction_id%TYPE,
16765: p_parent_interface_txn_id IN rcv_transactions_interface.parent_interface_txn_id%TYPE,
16766: p_amount IN rcv_transactions_interface.amount%TYPE,
16767: p_transaction_type IN rcv_transactions_interface.transaction_type%TYPE,
16768: x_interface_transaction_amt OUT NOCOPY rcv_transactions_interface.interface_transaction_amt%TYPE
16769: ) IS
16770: l_child_count NUMBER;
16771: l_grand_parent_id NUMBER;
16772: BEGIN
16799: END IF;
16800:
16801: SELECT parent_transaction_id
16802: INTO l_grand_parent_id
16803: FROM rcv_transactions_interface
16804: WHERE interface_transaction_id = p_parent_interface_txn_id;
16805:
16806: IF (g_asn_debug = 'Y') THEN
16807: asn_debug.put_line('update_total_transaction_amt l_grand_parent_id ' || l_grand_parent_id);
16808: END IF;
16809:
16810: IF ( (l_grand_parent_id IS NOT NULL)
16811: AND (p_transaction_type = 'CORRECT')) THEN
16812: UPDATE rcv_transactions_interface rti
16813: SET rti.interface_transaction_amt = rti.interface_transaction_amt + p_amount
16814: WHERE interface_transaction_id = p_parent_interface_txn_id;
16815: END IF;
16816:
16815: END IF;
16816:
16817: IF ( (l_grand_parent_id IS NOT NULL)
16818: AND (p_transaction_type = 'RETURN TO RECEIVING')) THEN
16819: UPDATE rcv_transactions_interface rti
16820: SET rti.interface_transaction_amt = rti.interface_transaction_amt - p_amount
16821: WHERE interface_transaction_id = p_parent_interface_txn_id;
16822: END IF;
16823: END IF; --}
17124: END get_deliver_to_person_from_rt;
17125:
17126: /* Function get_deliver_to_person_from_rti() is added as part of Bug#6375015 fix.
17127: This function is called from validate_vendor_rcv_line() and it tries to default
17128: the deliver_to_person_id mentioned in the parent transaction(i.e from rcv_transactions_interface).
17129: */
17130:
17131: FUNCTION get_deliver_to_person_from_rti(
17132: x_cascaded_table IN OUT NOCOPY rcv_roi_preprocessor.cascaded_trans_tab_type,
17135: l_parent_deliver_to_person_id NUMBER := null;
17136: BEGIN
17137: select deliver_to_person_id
17138: into l_parent_deliver_to_person_id
17139: from rcv_transactions_interface
17140: where interface_transaction_id = x_cascaded_table(n).parent_transaction_id;
17141:
17142: IF (g_asn_debug = 'Y') THEN
17143: asn_debug.put_line('Got deliver_to_person_id of parent txn as:'||l_parent_deliver_to_person_id);
17268:
17269: IF (l_lsl_exists = 0) THEN
17270: SELECT count(1)
17271: INTO l_lsl_exists
17272: FROM rcv_transactions_interface
17273: WHERE lcm_shipment_line_id is not null
17274: AND lcm_shipment_line_id = x_cascaded_table(n).lcm_shipment_line_id
17275: AND processing_status_code = 'RUNNING'
17276: AND interface_transaction_id <> x_cascaded_table(n).interface_transaction_id;
17498: IF (l_count = 0) then
17499:
17500: SELECT count(1)
17501: INTO l_count
17502: FROM rcv_transactions_interface
17503: WHERE source_transaction_num = x_cascaded_table(n).source_transaction_num
17504: AND processing_status_code = 'RUNNING'
17505: AND interface_transaction_id <> x_cascaded_table(n).interface_transaction_id;
17506: