[Home] [Help]
16: ) IS
17: l_po_header_id po_headers_all.po_header_id%type;
18: l_return_status VARCHAR2(1) :='S';
19: l_complex_flag varchar2(1);
20: l_interface_source_code rcv_transactions_interface.interface_source_code%type;
21: BEGIN
22: x_progress := '000';
23: -- Derive PO header and line at the beginning to differentiate between services/goods matching basis
24: -- 1) derive po header info
351: /* Fix for bug 2296720.
352: If both vendor_site_id and vendor_site_code are not populated
353: in interface tables, and if there are multiple vendor sites
354: associated to a particular vendor then we default them from PO
355: using the po_header_id of rcv_transactions_interface for each
356: line.
357: */
358: IF x_cascaded_table(n).vendor_site_id IS NULL
359: AND x_cascaded_table(n).vendor_site_code IS NULL THEN --{
525: item_id_record.to_organization_id := x_cascaded_table(n).to_organization_id;
526: item_id_record.item_revision := x_cascaded_table(n).item_revision;
527: item_id_record.error_record.error_status := 'S';
528: item_id_record.error_record.error_message := NULL;
529: rcv_transactions_interface_sv.default_item_revision(item_id_record);
530: x_cascaded_table(n).item_revision := item_id_record.item_revision;
531:
532: IF (g_asn_debug = 'Y') THEN
533: asn_debug.put_line(NVL(item_id_record.item_revision, 'Item Revision is null'));
1313: IF (g_asn_debug = 'Y') THEN
1314: asn_debug.put_line('Derive po_header_id');
1315: END IF;
1316:
1317: rcv_transactions_interface_sv.get_po_header_id(document_num_record);
1318: x_cascaded_table(n).po_header_id := document_num_record.po_header_id;
1319: x_cascaded_table(n).error_status := document_num_record.error_record.error_status;
1320: x_cascaded_table(n).error_message := document_num_record.error_record.error_message;
1321: rcv_error_pkg.log_interface_message(x_cascaded_table(n).error_status,
1394: IF (g_asn_debug = 'Y') THEN
1395: asn_debug.put_line('Derive item_id');
1396: END IF;
1397:
1398: rcv_transactions_interface_sv.get_item_id(item_id_record);
1399: x_cascaded_table(n).item_id := item_id_record.item_id;
1400: x_cascaded_table(n).primary_unit_of_measure := item_id_record.primary_unit_of_measure;
1401: x_cascaded_table(n).use_mtl_lot := item_id_record.use_mtl_lot; -- bug 608353
1402: x_cascaded_table(n).use_mtl_serial := item_id_record.use_mtl_serial; -- bug 608353
1437: IF (g_asn_debug = 'Y') THEN
1438: asn_debug.put_line('Derive Substitute Item Id');
1439: END IF;
1440:
1441: rcv_transactions_interface_sv.get_sub_item_id(sub_item_id_record);
1442: x_cascaded_table(n).substitute_item_id := sub_item_id_record.substitute_item_id;
1443: x_cascaded_table(n).error_status := sub_item_id_record.error_record.error_status;
1444: x_cascaded_table(n).error_message := sub_item_id_record.error_record.error_message;
1445:
1485: IF (g_asn_debug = 'Y') THEN
1486: asn_debug.put_line('Derive po_line_id');
1487: END IF;
1488:
1489: rcv_transactions_interface_sv.get_po_line_id(po_line_id_record);
1490:
1491: IF x_cascaded_table(n).item_id IS NULL THEN
1492: x_cascaded_table(n).item_id := po_line_id_record.item_id;
1493: END IF;
1613: END IF; -- set primary_uom
1614: END Comment: Bug: 4735484
1615: */
1616: /* Bug 2020269 : uom_code needs to be derived from unit_of_measure
1617: entered in rcv_transactions_interface.
1618: */
1619: IF (x_cascaded_table(n).unit_of_measure IS NOT NULL) THEN
1620: IF (g_asn_debug = 'Y') THEN
1621: asn_debug.put_line('X_progress ' || x_progress);
1867: IF (g_asn_debug = 'Y') THEN
1868: asn_debug.put_line('Derive location_id');
1869: END IF;
1870:
1871: rcv_transactions_interface_sv.get_location_id(location_id_record);
1872: x_cascaded_table(n).location_id := location_id_record.location_id;
1873: x_cascaded_table(n).error_status := location_id_record.error_record.error_status;
1874: x_cascaded_table(n).error_message := location_id_record.error_record.error_message;
1875: END IF;
1895: IF (g_asn_debug = 'Y') THEN
1896: asn_debug.put_line('Derive ship to location_id');
1897: END IF;
1898:
1899: rcv_transactions_interface_sv.get_location_id(location_id_record);
1900: x_cascaded_table(n).ship_to_location_id := location_id_record.location_id;
1901: x_cascaded_table(n).error_status := location_id_record.error_record.error_status;
1902: x_cascaded_table(n).error_message := location_id_record.error_record.error_message;
1903: END IF;
1923: IF (g_asn_debug = 'Y') THEN
1924: asn_debug.put_line('Derive routing_header_id');
1925: END IF;
1926:
1927: rcv_transactions_interface_sv.get_routing_header_id(routing_header_id_record);
1928: x_cascaded_table(n).routing_header_id := routing_header_id_record.routing_header_id;
1929: x_cascaded_table(n).error_status := routing_header_id_record.error_record.error_status;
1930: x_cascaded_table(n).error_message := routing_header_id_record.error_record.error_message;
1931: END IF;
1951: IF (g_asn_debug = 'Y') THEN
1952: asn_debug.put_line('Derive routing Step Id');
1953: END IF;
1954:
1955: rcv_transactions_interface_sv.get_routing_step_id(routing_step_id_record);
1956: x_cascaded_table(n).routing_step_id := routing_step_id_record.routing_step_id;
1957: x_cascaded_table(n).error_status := routing_step_id_record.error_record.error_status;
1958: x_cascaded_table(n).error_message := routing_step_id_record.error_record.error_message;
1959: END IF;
2023: IF (g_asn_debug = 'Y') THEN
2024: asn_debug.put_line('Derive deliver_to_location_id');
2025: END IF;
2026:
2027: rcv_transactions_interface_sv.get_location_id(location_id_record);
2028: x_cascaded_table(n).deliver_to_location_id := location_id_record.location_id;
2029: x_cascaded_table(n).error_status := location_id_record.error_record.error_status;
2030: x_cascaded_table(n).error_message := location_id_record.error_record.error_message;
2031: END IF;
2187: PROCEDURE default_from_subloc_info(
2188: x_cascaded_table IN OUT NOCOPY rcv_roi_preprocessor.cascaded_trans_tab_type,
2189: n IN BINARY_INTEGER
2190: ) IS
2191: l_from_subinventory rcv_transactions.from_subinventory%TYPE;
2192: l_from_locator_id rcv_transactions.from_locator_id%TYPE;
2193: l_to_subinventory rcv_transactions.subinventory%TYPE;
2194: l_to_locator_id rcv_transactions.locator_id%TYPE;
2195: BEGIN
2188: x_cascaded_table IN OUT NOCOPY rcv_roi_preprocessor.cascaded_trans_tab_type,
2189: n IN BINARY_INTEGER
2190: ) IS
2191: l_from_subinventory rcv_transactions.from_subinventory%TYPE;
2192: l_from_locator_id rcv_transactions.from_locator_id%TYPE;
2193: l_to_subinventory rcv_transactions.subinventory%TYPE;
2194: l_to_locator_id rcv_transactions.locator_id%TYPE;
2195: BEGIN
2196: IF (g_asn_debug = 'Y') THEN
2189: n IN BINARY_INTEGER
2190: ) IS
2191: l_from_subinventory rcv_transactions.from_subinventory%TYPE;
2192: l_from_locator_id rcv_transactions.from_locator_id%TYPE;
2193: l_to_subinventory rcv_transactions.subinventory%TYPE;
2194: l_to_locator_id rcv_transactions.locator_id%TYPE;
2195: BEGIN
2196: IF (g_asn_debug = 'Y') THEN
2197: asn_debug.put_line('inside default_from_subloc_info');
2190: ) IS
2191: l_from_subinventory rcv_transactions.from_subinventory%TYPE;
2192: l_from_locator_id rcv_transactions.from_locator_id%TYPE;
2193: l_to_subinventory rcv_transactions.subinventory%TYPE;
2194: l_to_locator_id rcv_transactions.locator_id%TYPE;
2195: BEGIN
2196: IF (g_asn_debug = 'Y') THEN
2197: asn_debug.put_line('inside default_from_subloc_info');
2198: END IF;
2198: END IF;
2199:
2200: /* If parent_interface_id is not null then it means it is a parent
2201: * child relation ship. So look in rti. If parent_transaction_id
2202: * is not null, then look in rcv_transactions.
2203: */
2204: IF (x_cascaded_table(n).parent_interface_txn_id IS NOT NULL) THEN
2205: SELECT subinventory,
2206: locator_id,
2209: INTO l_to_subinventory,
2210: l_to_locator_id,
2211: l_from_subinventory,
2212: l_from_locator_id
2213: FROM rcv_transactions_interface rti
2214: WHERE rti.interface_transaction_id = x_cascaded_table(n).parent_interface_txn_id;
2215: ELSIF (x_cascaded_table(n).parent_transaction_id IS NOT NULL) THEN
2216: SELECT subinventory,
2217: locator_id,
2220: INTO l_to_subinventory,
2221: l_to_locator_id,
2222: l_from_subinventory,
2223: l_from_locator_id
2224: FROM rcv_transactions rt
2225: WHERE rt.transaction_id = x_cascaded_table(n).parent_transaction_id;
2226: END IF;
2227:
2228: IF (g_asn_debug = 'Y') THEN
2266: PROCEDURE default_to_subloc_info(
2267: x_cascaded_table IN OUT NOCOPY rcv_roi_preprocessor.cascaded_trans_tab_type,
2268: n IN BINARY_INTEGER
2269: ) IS
2270: l_from_subinventory rcv_transactions.from_subinventory%TYPE;
2271: l_from_locator_id rcv_transactions.from_locator_id%TYPE;
2272: l_to_subinventory rcv_transactions.subinventory%TYPE;
2273: l_to_locator_id rcv_transactions.locator_id%TYPE;
2274: x_locator_control NUMBER;
2267: x_cascaded_table IN OUT NOCOPY rcv_roi_preprocessor.cascaded_trans_tab_type,
2268: n IN BINARY_INTEGER
2269: ) IS
2270: l_from_subinventory rcv_transactions.from_subinventory%TYPE;
2271: l_from_locator_id rcv_transactions.from_locator_id%TYPE;
2272: l_to_subinventory rcv_transactions.subinventory%TYPE;
2273: l_to_locator_id rcv_transactions.locator_id%TYPE;
2274: x_locator_control NUMBER;
2275: x_default_subinventory VARCHAR2(10);
2268: n IN BINARY_INTEGER
2269: ) IS
2270: l_from_subinventory rcv_transactions.from_subinventory%TYPE;
2271: l_from_locator_id rcv_transactions.from_locator_id%TYPE;
2272: l_to_subinventory rcv_transactions.subinventory%TYPE;
2273: l_to_locator_id rcv_transactions.locator_id%TYPE;
2274: x_locator_control NUMBER;
2275: x_default_subinventory VARCHAR2(10);
2276: x_default_locator_id NUMBER;
2269: ) IS
2270: l_from_subinventory rcv_transactions.from_subinventory%TYPE;
2271: l_from_locator_id rcv_transactions.from_locator_id%TYPE;
2272: l_to_subinventory rcv_transactions.subinventory%TYPE;
2273: l_to_locator_id rcv_transactions.locator_id%TYPE;
2274: x_locator_control NUMBER;
2275: x_default_subinventory VARCHAR2(10);
2276: x_default_locator_id NUMBER;
2277: x_success BOOLEAN;
2462: /* End of bug 3568861. */
2463:
2464: /* If parent_interface_id is not null then it means it is a parent
2465: * child relation ship. So look in rti. If parent_transaction_id
2466: * is not null, then look in rcv_transactions.
2467: */
2468: IF (x_cascaded_table(n).parent_interface_txn_id IS NOT NULL) THEN
2469: SELECT subinventory,
2470: locator_id,
2473: INTO l_to_subinventory,
2474: l_to_locator_id,
2475: l_from_subinventory,
2476: l_from_locator_id
2477: FROM rcv_transactions_interface rti
2478: WHERE rti.interface_transaction_id = x_cascaded_table(n).parent_interface_txn_id;
2479: ELSIF (x_cascaded_table(n).parent_transaction_id IS NOT NULL) THEN
2480: SELECT subinventory,
2481: locator_id,
2484: INTO l_to_subinventory,
2485: l_to_locator_id,
2486: l_from_subinventory,
2487: l_from_locator_id
2488: FROM rcv_transactions rt
2489: WHERE rt.transaction_id = x_cascaded_table(n).parent_transaction_id;
2490: END IF;
2491:
2492: IF (g_asn_debug = 'Y') THEN
2542: IF (g_asn_debug = 'Y') THEN
2543: asn_debug.put_line('Derive Reason_id');
2544: END IF;
2545:
2546: rcv_transactions_interface_sv.get_reason_id(reason_id_record);
2547: x_cascaded_table(n).reason_id := reason_id_record.reason_id;
2548: x_cascaded_table(n).error_status := reason_id_record.error_record.error_status;
2549: x_cascaded_table(n).error_message := reason_id_record.error_record.error_message;
2550: END IF;
2918: x_progress VARCHAR2(3);
2919: x_to_organization_code VARCHAR2(5);
2920: x_converted_trx_qty NUMBER := 0;
2921: transaction_ok BOOLEAN := FALSE;
2922: x_expected_date rcv_transactions_interface.expected_receipt_date%TYPE;
2923: high_range_date DATE;
2924: low_range_date DATE;
2925: rows_fetched NUMBER := 0;
2926: x_tolerable_qty NUMBER := 0;
3008: -- the following steps will create a set of rows linking the line_record with
3009: -- its corresponding po_line_location rows until the quantity value from
3010: -- the asn is consumed. (Cascade)
3011:
3012: /* 2119137 : If the user populates rcv_transactions_interface
3013: with po_line_id, then ROI errors out with
3014: RCV_ASN_NO_PO_LINE_LOCATION_ID when the docment_line_num
3015: is not provided for one time items. Modified the "if" criteria in
3016: such a way that the ROI validation does'nt error out when
3070: WHERE po_line_id = temp_cascaded_table(current_n).po_line_id;
3071: EXCEPTION
3072: WHEN OTHERS THEN
3073: IF (g_asn_debug = 'Y') THEN
3074: asn_debug.put_line('wrong po_line_id entered in rcv_transactions_interface');
3075: END IF;
3076: END;
3077: END IF; --}
3078:
3085: WHERE line_location_id = temp_cascaded_table(current_n).po_line_location_id;
3086: EXCEPTION
3087: WHEN OTHERS THEN
3088: IF (g_asn_debug = 'Y') THEN
3089: asn_debug.put_line('wrong po_line_location_id entered in rcv_transactions_interface');
3090: END IF;
3091: END;
3092: END IF; --}
3093:
3100: WHERE po_distribution_id = temp_cascaded_table(current_n).po_distribution_id;
3101: EXCEPTION
3102: WHEN OTHERS THEN
3103: IF (g_asn_debug = 'Y') THEN
3104: asn_debug.put_line('wrong po_distribution_id entered in rcv_transactions_interface');
3105: END IF;
3106: END;
3107: END IF; --}
3108:
3588: temp_cascaded_table(current_n).quantity := temp_cascaded_table(current_n).quantity + x_remaining_quantity;
3589: temp_cascaded_table(current_n).quantity_shipped := temp_cascaded_table(current_n).quantity_shipped + x_remaining_quantity;
3590: temp_cascaded_table(current_n).source_doc_quantity := temp_cascaded_table(current_n).source_doc_quantity + x_remaining_qty_po_uom;
3591: temp_cascaded_table(current_n).primary_quantity := temp_cascaded_table(current_n).primary_quantity
3592: + rcv_transactions_interface_sv.convert_into_correct_qty(x_remaining_quantity,
3593: temp_cascaded_table(1).unit_of_measure,
3594: temp_cascaded_table(1).item_id,
3595: temp_cascaded_table(1).primary_unit_of_measure
3596: );
4150: asn_debug.put_line('Cursor record ' || TO_CHAR(rows_fetched));
4151: asn_debug.put_line('Check date tolerance');
4152: END IF;
4153:
4154: 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
4155: x_shipmentdistributionrec.promised_date,
4156: x_shipmentdistributionrec.days_early_receipt_allowed,
4157: x_shipmentdistributionrec.days_late_receipt_allowed,
4158: x_shipmentdistributionrec.receipt_days_exception_code
4255: END IF;
4256:
4257: -- Check shipto_location enforcement
4258:
4259: rcv_transactions_interface_sv.check_shipto_enforcement(x_shipmentdistributionrec.ship_to_location_id,
4260: NVL(temp_cascaded_table(1).ship_to_location_id, x_header_record.header_record.location_id),
4261: x_shipmentdistributionrec.enforce_ship_to_location_code
4262: );
4263:
4532: END IF;
4533: END IF;
4534:
4535: if ( l_complex_flag = 'Y' ) then --{
4536: x_remaining_qty_po_uom := rcv_transactions_interface_sv.convert_into_correct_qty(x_remaining_quantity,
4537:
4538: temp_cascaded_table(1).unit_of_measure,
4539:
4540: temp_cascaded_table(1).item_id,
4543:
4544: );
4545: -- using arbit qty for PO->ASN, PO->PRIMARY UOM conversion as this is just a check
4546:
4547: po_asn_uom_qty := rcv_transactions_interface_sv.convert_into_correct_qty(1000,
4548:
4549: x_shipmentdistributionrec.unit_meas_lookup_code,
4550:
4551: temp_cascaded_table(1).item_id,
4552:
4553: temp_cascaded_table(1).unit_of_measure
4554:
4555: );
4556: po_primary_uom_qty := rcv_transactions_interface_sv.convert_into_correct_qty(1000,
4557:
4558: x_shipmentdistributionrec.unit_meas_lookup_code,
4559:
4560: temp_cascaded_table(1).item_id,
4659: * errors out. Reverting to old code for complex work POs.
4660: */
4661: if ( l_complex_flag = 'Y' ) then --{
4662: x_remaining_quantity :=
4663: rcv_transactions_interface_sv.convert_into_correct_qty(
4664: x_remaining_qty_po_uom,
4665: x_shipmentdistributionrec.unit_meas_lookup_code,
4666: temp_cascaded_table(1).item_id,
4667: temp_cascaded_table(1).unit_of_measure);
4738: */
4739: if ( l_complex_flag = 'Y' ) then --{
4740: temp_cascaded_table(current_n).vendor_cum_shipped_qty :=
4741: temp_cascaded_table(current_n).vendor_cum_shipped_qty
4742: - rcv_transactions_interface_sv.convert_into_correct_qty
4743: (x_bkp_qty,
4744: temp_cascaded_table(current_n).unit_of_measure,
4745: temp_cascaded_table(current_n).item_id,
4746: temp_cascaded_table(current_n).primary_unit_of_measure);
4787: * return the original quantity whereas the new one
4788: * errors out. Reverting to old code for complex work POs.
4789: */
4790: if ( l_complex_flag = 'Y' ) then --{
4791: temp_cascaded_table(current_n).quantity := rcv_transactions_interface_sv.convert_into_correct_qty(x_converted_trx_qty,
4792:
4793: x_shipmentdistributionrec.unit_meas_lookup_code,
4794:
4795: temp_cascaded_table(current_n).item_id,
4818: * return the original quantity whereas the new one
4819: * errors out. Reverting to old code for complex work POs.
4820: */
4821: if ( l_complex_flag = 'Y' ) then --{
4822: temp_cascaded_table(current_n).primary_quantity := rcv_transactions_interface_sv.convert_into_correct_qty(x_converted_trx_qty,
4823: x_shipmentdistributionrec.unit_meas_lookup_code,
4824:
4825: temp_cascaded_table(current_n).item_id,
4826:
4878: END IF;
4879: END IF; --}
4880:
4881: /* ksareddy - 2329928 Ported changes by bao in branch to cache set_of_books_id */
4882: IF (rcv_transactions_interface_sv.x_set_of_books_id IS NULL) THEN
4883: SELECT set_of_books_id
4884: INTO rcv_transactions_interface_sv.x_set_of_books_id
4885: FROM financials_system_parameters;
4886: END IF;
4880:
4881: /* ksareddy - 2329928 Ported changes by bao in branch to cache set_of_books_id */
4882: IF (rcv_transactions_interface_sv.x_set_of_books_id IS NULL) THEN
4883: SELECT set_of_books_id
4884: INTO rcv_transactions_interface_sv.x_set_of_books_id
4885: FROM financials_system_parameters;
4886: END IF;
4887:
4888: x_sob_id := rcv_transactions_interface_sv.x_set_of_books_id;
4884: INTO rcv_transactions_interface_sv.x_set_of_books_id
4885: FROM financials_system_parameters;
4886: END IF;
4887:
4888: x_sob_id := rcv_transactions_interface_sv.x_set_of_books_id;
4889: /* Bug 1845702
4890: * Currency rate and date can be changed at the time of receipt
4891: * depending on the profile ALLOW_RATE_OVERRIDE_FOR_USER_RATE_TYPE.
4892: * This was not handled in the open interface. Introduced code
4908:
4909: /* Bug#3746516 - START
4910: ** Modified the following code to consider all the possible scenarios such that the
4911: ** rate and the rate date values will be derived correctly and the correct rate and
4912: ** rate date values will now be posted in rcv_transactions table so that there will
4913: ** not be any discrepency in the calculations and hence all the data between the two
4914: ** tables rcv_transactions and rcv_receiving_sub_ledger will be in sync. */
4915:
4916: BEGIN
4910: ** Modified the following code to consider all the possible scenarios such that the
4911: ** rate and the rate date values will be derived correctly and the correct rate and
4912: ** rate date values will now be posted in rcv_transactions table so that there will
4913: ** not be any discrepency in the calculations and hence all the data between the two
4914: ** tables rcv_transactions and rcv_receiving_sub_ledger will be in sync. */
4915:
4916: BEGIN
4917:
4918: SELECT count(po_distribution_id),max(rate),max(rate_date)
5411: x_progress VARCHAR2(3);
5412: x_to_organization_code VARCHAR2(5);
5413: x_converted_trx_amt NUMBER := 0;
5414: transaction_ok BOOLEAN := FALSE;
5415: x_expected_date rcv_transactions_interface.expected_receipt_date%TYPE;
5416: high_range_date DATE;
5417: low_range_date DATE;
5418: rows_fetched NUMBER := 0;
5419: x_tolerable_amt NUMBER := 0;
5544: -- the following steps will create a set of rows linking the line_record with
5545: -- its corresponding po_line_location rows until the quantity value from
5546: -- the asn is consumed. (Cascade)
5547:
5548: /* 2119137 : If the user populates rcv_transactions_interface
5549: with po_line_id, then ROI errors out with
5550: RCV_ASN_NO_PO_LINE_LOCATION_ID when the docment_line_num
5551: is not provided for one time items. Modified the "if" criteria in
5552: such a way that the ROI validation does'nt error out when
5588: WHERE po_line_id = temp_cascaded_table(current_n).po_line_id;
5589: EXCEPTION
5590: WHEN OTHERS THEN
5591: IF (g_asn_debug = 'Y') THEN
5592: asn_debug.put_line('wrong po_line_id entered in rcv_transactions_interface');
5593: END IF;
5594: END;
5595: END IF; --}
5596:
5603: WHERE line_location_id = temp_cascaded_table(current_n).po_line_location_id;
5604: EXCEPTION
5605: WHEN OTHERS THEN
5606: IF (g_asn_debug = 'Y') THEN
5607: asn_debug.put_line('wrong po_line_location_id entered in rcv_transactions_interface');
5608: END IF;
5609: END;
5610: END IF; --}
5611:
5618: WHERE po_distribution_id = temp_cascaded_table(current_n).po_distribution_id;
5619: EXCEPTION
5620: WHEN OTHERS THEN
5621: IF (g_asn_debug = 'Y') THEN
5622: asn_debug.put_line('wrong po_distribution_id entered in rcv_transactions_interface');
5623: END IF;
5624: END;
5625: END IF; --}
5626:
6167: IF (g_asn_debug = 'Y') THEN
6168: asn_debug.put_line('Check date tolerance for a Fixed Price services line');
6169: END IF;
6170:
6171: 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
6172: x_shipmentdistributionrec.promised_date,
6173: x_shipmentdistributionrec.days_early_receipt_allowed,
6174: x_shipmentdistributionrec.days_late_receipt_allowed,
6175: x_shipmentdistributionrec.receipt_days_exception_code
6199: END IF;
6200:
6201: -- Check shipto_location enforcement
6202:
6203: rcv_transactions_interface_sv.check_shipto_enforcement(x_shipmentdistributionrec.ship_to_location_id,
6204: NVL(temp_cascaded_table(1).ship_to_location_id, x_header_record.header_record.location_id),
6205: x_shipmentdistributionrec.enforce_ship_to_location_code
6206: );
6207:
6436: END IF;
6437: END IF; --}
6438:
6439: /* ksareddy - 2329928 Ported changes by bao in branch to cache set_of_books_id */
6440: IF (rcv_transactions_interface_sv.x_set_of_books_id IS NULL) THEN
6441: SELECT set_of_books_id
6442: INTO rcv_transactions_interface_sv.x_set_of_books_id
6443: FROM financials_system_parameters;
6444: END IF;
6438:
6439: /* ksareddy - 2329928 Ported changes by bao in branch to cache set_of_books_id */
6440: IF (rcv_transactions_interface_sv.x_set_of_books_id IS NULL) THEN
6441: SELECT set_of_books_id
6442: INTO rcv_transactions_interface_sv.x_set_of_books_id
6443: FROM financials_system_parameters;
6444: END IF;
6445:
6446: x_sob_id := rcv_transactions_interface_sv.x_set_of_books_id;
6442: INTO rcv_transactions_interface_sv.x_set_of_books_id
6443: FROM financials_system_parameters;
6444: END IF;
6445:
6446: x_sob_id := rcv_transactions_interface_sv.x_set_of_books_id;
6447: /* Bug 1845702
6448: * Currency rate and date can be changed at the time of receipt
6449: * depending on the profile ALLOW_RATE_OVERRIDE_FOR_USER_RATE_TYPE.
6450: * This was not handled in the open interface. Introduced code
6466:
6467: /* Bug#3746516 - START
6468: ** Modified the following code to consider all the possble scenarios such that the
6469: ** rate and the rate date values will be derived correctly and the correct rate and
6470: ** rate date values will now be posted in rcv_transactions table so that there will
6471: ** not be any discrepency in the calculations and hence all the data between the two
6472: ** tables rcv_transactions and rcv_receiving_sub_ledger will be in sync.
6473: ** Assuming that only Direct delivery of Amount Based lines are possible and
6474: ** x_ShipmentDistributionRec would thus have only distribution information. */
6468: ** Modified the following code to consider all the possble scenarios such that the
6469: ** rate and the rate date values will be derived correctly and the correct rate and
6470: ** rate date values will now be posted in rcv_transactions table so that there will
6471: ** not be any discrepency in the calculations and hence all the data between the two
6472: ** tables rcv_transactions and rcv_receiving_sub_ledger will be in sync.
6473: ** Assuming that only Direct delivery of Amount Based lines are possible and
6474: ** x_ShipmentDistributionRec would thus have only distribution information. */
6475:
6476: IF (x_ShipmentDistributionRec.match_option = 'P') THEN --{
6790:
6791: quantity_invoiced_record.quantity_invoiced := x_cascaded_table(n).quantity_invoiced;
6792: quantity_invoiced_record.error_record.error_status := 'S';
6793: quantity_invoiced_record.error_record.error_message := NULL;
6794: rcv_transactions_interface_sv1.validate_quantity_invoiced(quantity_invoiced_record);
6795: x_cascaded_table(n).error_status := quantity_invoiced_record.error_record.error_status;
6796: x_cascaded_table(n).error_message := quantity_invoiced_record.error_record.error_message;
6797: rcv_error_pkg.log_interface_message(x_cascaded_table(n).error_status,
6798: 'UNIT_OF_MEASURE',
6829: IF (g_asn_debug = 'Y') THEN
6830: asn_debug.put_line('Validating UOM');
6831: END IF;
6832:
6833: rcv_transactions_interface_sv1.validate_uom(uom_record);
6834: x_cascaded_table(n).error_status := uom_record.error_record.error_status;
6835: x_cascaded_table(n).error_message := uom_record.error_record.error_message;
6836: rcv_error_pkg.log_interface_message(x_cascaded_table(n).error_status,
6837: 'UNIT_OF_MEASURE',
6906: IF (g_asn_debug = 'Y') THEN
6907: asn_debug.put_line('Validating Item Description ' || x_cascaded_table(n).item_description);
6908: END IF;
6909:
6910: rcv_transactions_interface_sv1.validate_item_description(item_id_record);
6911: x_cascaded_table(n).error_status := item_id_record.error_record.error_status;
6912: x_cascaded_table(n).error_message := item_id_record.error_record.error_message;
6913:
6914: IF (g_asn_debug = 'Y') THEN
6956: IF (g_asn_debug = 'Y') THEN
6957: asn_debug.put_line('Validating Freight Carrier');
6958: END IF;
6959:
6960: rcv_transactions_interface_sv1.validate_freight_carrier(freight_carrier_record);
6961: x_cascaded_table(n).error_status := freight_carrier_record.error_record.error_status;
6962: x_cascaded_table(n).error_message := freight_carrier_record.error_record.error_message;
6963: rcv_error_pkg.log_interface_message(x_cascaded_table(n).error_status,
6964: 'FREIGHT_CARRIER_CODE',
6988: IF (g_asn_debug = 'Y') THEN
6989: asn_debug.put_line('Validating Destination Type Code');
6990: END IF;
6991:
6992: rcv_transactions_interface_sv1.validate_po_lookup_code(po_lookup_code_record);
6993: x_cascaded_table(n).error_status := po_lookup_code_record.error_record.error_status;
6994: x_cascaded_table(n).error_message := po_lookup_code_record.error_record.error_message;
6995: rcv_error_pkg.log_interface_message(x_cascaded_table(n).error_status,
6996: 'DESTINATION_TYPE_CODE',
7022: IF (g_asn_debug = 'Y') THEN
7023: asn_debug.put_line('Validating Ship To Location');
7024: END IF;
7025:
7026: rcv_transactions_interface_sv1.validate_location(location_record);
7027: x_cascaded_table(n).error_status := location_record.error_record.error_status;
7028: x_cascaded_table(n).error_message := location_record.error_record.error_message;
7029: rcv_error_pkg.log_interface_message(x_cascaded_table(n).error_status,
7030: 'SHIP_TO_LOCATION_ID',
7055: IF (g_asn_debug = 'Y') THEN
7056: asn_debug.put_line('Validating Deliver to Person');
7057: END IF;
7058:
7059: rcv_transactions_interface_sv1.validate_employee(employee_record);
7060:
7061: /* Bug:6375015
7062: If the deliver_to_person_id provided is invalid/terminated employee no need
7063: to fail the transaction, as deliver_to_person_id is not the mandatory field.
7173: IF (g_asn_debug = 'Y') THEN
7174: asn_debug.put_line('Validating Deliver To Location');
7175: END IF;
7176:
7177: rcv_transactions_interface_sv1.validate_location(location_record);
7178: x_cascaded_table(n).error_status := location_record.error_record.error_status;
7179: x_cascaded_table(n).error_message := location_record.error_record.error_message;
7180: rcv_error_pkg.log_interface_message(x_cascaded_table(n).error_status,
7181: 'DELIVER_TO_LOCATION_ID',
7369: IF (g_asn_debug = 'Y') THEN
7370: asn_debug.put_line('Validating Locator');
7371: END IF;
7372:
7373: rcv_transactions_interface_sv1.validate_locator(locator_record);
7374: x_cascaded_table(n).error_status := locator_record.error_record.error_status;
7375: x_cascaded_table(n).error_message := locator_record.error_record.error_message;
7376:
7377: /* Bug 5468345.
7375: x_cascaded_table(n).error_message := locator_record.error_record.error_message;
7376:
7377: /* Bug 5468345.
7378: * If the user has specified locator_id or locator_num and org/sub/item is not under locator control,
7379: * then the rcv_transactions_interface_sv1.validate_locator procedure nulls out the locator_id.
7380: * We need to copy this back to the x_cascaded_table. This used to cause data corruption.
7381: * Changed the code to null it.
7382: */
7383:
7430: IF (g_asn_debug = 'Y') THEN
7431: asn_debug.put_line('Validating Tax Code');
7432: END IF;
7433:
7434: rcv_transactions_interface_sv1.validate_tax_code(tax_name_record);
7435: x_cascaded_table(n).error_status := tax_name_record.error_record.error_status;
7436: x_cascaded_table(n).error_message := tax_name_record.error_record.error_message;
7437: rcv_error_pkg.log_interface_message(x_cascaded_table(n).error_status,
7438: 'TAX_NAME',
7461: IF (g_asn_debug = 'Y') THEN
7462: asn_debug.put_line('Validating Country of Origin Code');
7463: END IF;
7464:
7465: rcv_transactions_interface_sv1.validate_country_of_origin(country_of_origin_record);
7466: x_cascaded_table(n).error_status := country_of_origin_record.error_record.error_status;
7467: x_cascaded_table(n).error_message := country_of_origin_record.error_record.error_message;
7468: rcv_error_pkg.log_interface_message(x_cascaded_table(n).error_status,
7469: 'COUNTRY_OF_ORIGIN_CODE',
7493: IF (g_asn_debug = 'Y') THEN
7494: asn_debug.put_line('Validating ASL');
7495: END IF;
7496:
7497: rcv_transactions_interface_sv1.validate_asl(asl_record);
7498: x_cascaded_table(n).error_status := asl_record.error_record.error_status;
7499: x_cascaded_table(n).error_message := asl_record.error_record.error_message;
7500: rcv_error_pkg.log_interface_message(x_cascaded_table(n).error_status,
7501: 'SUPPLY_AGREEMENT_FLAG',
7530: IF (g_asn_debug = 'Y') THEN
7531: asn_debug.put_line('Validating Cum Qty Shipped');
7532: END IF;
7533:
7534: rcv_transactions_interface_sv1.validate_cum_quantity_shipped(cum_quantity_record);
7535: x_cascaded_table(n).error_status := cum_quantity_record.error_record.error_status;
7536: x_cascaded_table(n).error_message := cum_quantity_record.error_record.error_message;
7537: rcv_error_pkg.log_interface_message(x_cascaded_table(n).error_status,
7538: 'VENDOR_CUM_SHIPPED_QTY',
7605: /* End of 3210821. */
7606: ref_integrity_record.error_record.error_status := 'S';
7607: ref_integrity_record.error_record.error_message := NULL;
7608: --replacing call with a validate_ref_integ call with the method in thid package
7609: -- rcv_transactions_interface_sv1.validate_ref_integ (ref_integrity_record,X_header_record);
7610: validate_ref_integ(ref_integrity_record, x_header_record);
7611:
7612: IF (g_asn_debug = 'Y') THEN
7613: asn_debug.put_line('After Validating Ref Integ: ' || ref_integrity_record.error_record.error_status);
7825: asn_debug.put_line('Primary UOM for substitute item is ' || x_primary_uom);
7826: END IF;
7827:
7828: IF x_primary_uom <> x_cascaded_table(n).primary_unit_of_measure THEN --{
7829: prim_uom_qty := rcv_transactions_interface_sv.convert_into_correct_qty(x_cascaded_table(n).quantity,
7830: x_cascaded_table(n).unit_of_measure,
7831: x_cascaded_table(n).item_id,
7832: x_primary_uom
7833: );
7904: IF (g_asn_debug = 'Y') THEN
7905: asn_debug.put_line('Validating ASBN for Consigned PO');
7906: END IF;
7907:
7908: rcv_transactions_interface_sv1.validate_consigned_po(l_consigned_po_rec);
7909:
7910: IF (g_asn_debug = 'Y') THEN
7911: asn_debug.put_line('After Validating ASBN for Consigned PO');
7912: END IF;
7934: IF (g_asn_debug = 'Y') THEN
7935: asn_debug.put_line('Validating Consumption PO');
7936: END IF;
7937:
7938: rcv_transactions_interface_sv1.validate_consumption_po(l_consumption_po_rec);
7939:
7940: IF (g_asn_debug = 'Y') THEN
7941: asn_debug.put_line('After Validating Consumption PO');
7942: END IF;
7963: IF (g_asn_debug = 'Y') THEN
7964: asn_debug.put_line('Validating Consumption Release');
7965: END IF;
7966:
7967: rcv_transactions_interface_sv1.validate_consumption_release(l_consumption_release_rec);
7968:
7969: IF (g_asn_debug = 'Y') THEN
7970: asn_debug.put_line('After Validating Consumption Release');
7971: END IF;
8062: IF (x_ref_integrity_rec.parent_txn_id IS NOT NULL) THEN
8063:
8064: SELECT NVL(MAX(vendor_site_id), 0)
8065: INTO x_po_vendor_site_id
8066: FROM rcv_transactions
8067: WHERE po_header_id = x_ref_integrity_rec.po_header_id
8068: AND transaction_id = x_ref_integrity_rec.parent_txn_id
8069: AND vendor_site_id = x_ref_integrity_rec.vendor_site_id;
8070:
8067: WHERE po_header_id = x_ref_integrity_rec.po_header_id
8068: AND transaction_id = x_ref_integrity_rec.parent_txn_id
8069: AND vendor_site_id = x_ref_integrity_rec.vendor_site_id;
8070:
8071: asn_debug.put_line('vendor_site_id from RCV_TRANSACTIONS ' || TO_CHAR(x_po_vendor_site_id));
8072:
8073: END IF;
8074:
8075:
8192: l_wms_msg_count NUMBER;
8193: l_wms_msg_data VARCHAR2(400);
8194: lorgid NUMBER; -- For Bug 2110031
8195: --WMS lot serial support
8196: l_orig_trx_id rcv_transactions_interface.interface_transaction_id%TYPE;
8197: l_ls_table inv_rcv_integration_apis.child_rec_tb_tp;
8198: l_ls_table_idx BINARY_INTEGER := 0;
8199: l_api_version NUMBER := 1.0;
8200: l_return_status VARCHAR2(1);
8199: l_api_version NUMBER := 1.0;
8200: l_return_status VARCHAR2(1);
8201: l_msg_count NUMBER;
8202: l_msg_data fnd_new_messages.MESSAGE_TEXT%TYPE;
8203: l_group_id rcv_transactions_interface.GROUP_ID%TYPE;
8204: l_header_interface_id rcv_transactions_interface.header_interface_id%TYPE;
8205: l_interface_transaction_id rcv_transactions_interface.interface_transaction_id%TYPE;
8206: l_return BOOLEAN := TRUE;
8207: l_message VARCHAR2(2000);
8200: l_return_status VARCHAR2(1);
8201: l_msg_count NUMBER;
8202: l_msg_data fnd_new_messages.MESSAGE_TEXT%TYPE;
8203: l_group_id rcv_transactions_interface.GROUP_ID%TYPE;
8204: l_header_interface_id rcv_transactions_interface.header_interface_id%TYPE;
8205: l_interface_transaction_id rcv_transactions_interface.interface_transaction_id%TYPE;
8206: l_return BOOLEAN := TRUE;
8207: l_message VARCHAR2(2000);
8208: BEGIN
8201: l_msg_count NUMBER;
8202: l_msg_data fnd_new_messages.MESSAGE_TEXT%TYPE;
8203: l_group_id rcv_transactions_interface.GROUP_ID%TYPE;
8204: l_header_interface_id rcv_transactions_interface.header_interface_id%TYPE;
8205: l_interface_transaction_id rcv_transactions_interface.interface_transaction_id%TYPE;
8206: l_return BOOLEAN := TRUE;
8207: l_message VARCHAR2(2000);
8208: BEGIN
8209: IF v_trans_tab.COUNT > 0 THEN --{
8211: e_o_t := v_trans_tab.LAST;
8212: v_current_interface_id := -999;
8213: v_prior_interface_id := -999;
8214:
8215: /* Delete only once from the rcv_transactions_interface table for every
8216: group of pl/sql table rows that have the same interface id */
8217: FOR j IN i .. e_o_t LOOP
8218: asn_debug.put_line('Transaction Type ' || v_trans_tab(j).transaction_type);
8219: v_current_interface_id := v_trans_tab(j).interface_transaction_id;
8236: asn_debug.put_line('lorgid ' || TO_CHAR(lorgid));
8237:
8238: IF v_current_interface_id <> v_prior_interface_id THEN
8239: asn_debug.put_line('Handle the original interface id ' || TO_CHAR(v_trans_tab(j).interface_transaction_id));
8240: asn_debug.put_line('delete from rcv_transactions_interface rowid ' || v_trans_tab(j).row_id);
8241: asn_debug.put_line('matching_basis=' || v_trans_tab(j).matching_basis);
8242:
8243: /* WMS Changes */
8244: IF v_trans_tab(j).matching_basis <> 'AMOUNT' THEN
8614: asn_debug.put_line('Interface Id ' || TO_CHAR(v_trans_tab(j).interface_transaction_id));
8615: v_prior_interface_id := v_current_interface_id;
8616: v_trans_tab.DELETE(j);
8617: ELSE
8618: asn_debug.put_line('insert into rcv_transactions_interface with new id ');
8619: /* Since we are inserting 1-> many rows need to generate a new interface id */
8620: v_trans_tab(j).interface_transaction_id := NULL;
8621: x_rowid := NULL;
8622:
9049: /* We error out the rti row here.
9050: * Erroring out the other lpm_group rows
9051: * or ASN rows are taken care in RCVPREPB.pls.
9052: */
9053: UPDATE rcv_transactions_interface
9054: SET processing_status_code = 'ERROR'
9055: WHERE interface_transaction_id = l_ls_table(k).new_interface_trx_id;
9056:
9057: SELECT GROUP_ID,
9059: interface_transaction_id
9060: INTO l_group_id,
9061: l_header_interface_id,
9062: l_interface_transaction_id
9063: FROM rcv_transactions_interface
9064: WHERE interface_transaction_id = l_ls_table(k).new_interface_trx_id;
9065:
9066: IF (l_message IS NOT NULL) THEN
9067: rcv_error_pkg.log_interface_error_message(l_message);
9141: ) IS
9142: derive_values_from_plsql_table BOOLEAN := FALSE;
9143: cascaded_table_index NUMBER;
9144: derive_values_from_rti BOOLEAN := FALSE;
9145: l_processing_status_code rcv_transactions_interface.processing_status_code%TYPE;
9146: x_error_status VARCHAR2(1);
9147: e_unexpected_error EXCEPTION;
9148: BEGIN
9149: x_error_status := rcv_error_pkg.g_ret_sts_error;
9223:
9224: BEGIN
9225: SELECT interface_transaction_id
9226: INTO x_cascaded_table(n).parent_interface_txn_id
9227: FROM rcv_transactions_interface
9228: WHERE GROUP_ID = x_cascaded_table(n).GROUP_ID
9229: AND source_transaction_num = x_cascaded_table(n).parent_source_transaction_num;
9230: EXCEPTION
9231: WHEN NO_DATA_FOUND THEN
9251: IF ( x_cascaded_table(n).parent_interface_txn_id IS NULL
9252: AND x_cascaded_table(n).parent_transaction_id IS NULL) THEN --{
9253: /* This is an error. It will come here when the user has
9254: * provided parent_source_transaction_num but that did not match with
9255: * with any parent in rcv_transactions or with any
9256: * row in rti or the pl/sql table. Hence need to insert
9257: * into po_interface_errors saying that this is a child
9258: * without any parent in rti or rt.
9259: */
9267: IF (NOT derive_values_from_plsql_table) THEN --{
9268: BEGIN
9269: SELECT processing_status_code
9270: INTO l_processing_status_code
9271: FROM rcv_transactions_interface
9272: WHERE interface_transaction_id = x_cascaded_table(n).parent_interface_txn_id;
9273: EXCEPTION
9274: WHEN OTHERS THEN
9275: IF (g_asn_debug = 'Y') THEN
9332: x_progress VARCHAR2(3);
9333: x_to_organization_code VARCHAR2(5);
9334: x_converted_trx_qty NUMBER := 0;
9335: transaction_ok BOOLEAN := FALSE;
9336: x_expected_date rcv_transactions_interface.expected_receipt_date%TYPE;
9337: high_range_date DATE;
9338: low_range_date DATE;
9339: rows_fetched NUMBER := 0;
9340: x_tolerable_qty NUMBER := 0;
9418: 0 ussgl_transaction_code,
9419: rt.quantity qty,
9420: 0 interface_available_qty
9421: FROM rcv_supply rsup,
9422: rcv_transactions rt,
9423: rcv_shipment_lines rsl,
9424: po_headers poh,
9425: po_lines pol
9426: WHERE rt.transaction_id = v_parent_trx_id
9473: 0 bom_resource_id,
9474: 0 ussgl_transaction_code,
9475: rti.quantity qty,
9476: rti.interface_available_qty
9477: FROM rcv_transactions_interface rti
9478: WHERE interface_transaction_id = v_parent_inter_trx_id;
9479:
9480: /* Bug 3292329.
9481: We used to use the same cursor for single deliver and
9525: pod.ussgl_transaction_code,
9526: rt.quantity qty,
9527: 0 interface_available_qty
9528: FROM rcv_supply rsup,
9529: rcv_transactions rt,
9530: rcv_shipment_lines rsl,
9531: po_headers poh,
9532: po_lines pol,
9533: po_distributions pod
9591: pod.bom_resource_id,
9592: pod.ussgl_transaction_code,
9593: rti.quantity qty,
9594: rti.interface_available_qty
9595: FROM rcv_transactions_interface rti,
9596: po_distributions pod
9597: WHERE interface_transaction_id = v_parent_inter_trx_id
9598: AND pod.line_location_id = rti.po_line_location_id --Bug:5354379
9599: AND pod.po_header_id = rti.po_header_id
9640: pod.ussgl_transaction_code,
9641: rt.quantity qty,
9642: 0 interface_available_qty
9643: FROM rcv_supply rsup,
9644: rcv_transactions rt,
9645: rcv_shipment_lines rsl,
9646: po_headers poh,
9647: po_lines pol,
9648: po_distributions pod
9699: pod.bom_resource_id,
9700: pod.ussgl_transaction_code,
9701: rti.quantity qty,
9702: rti.interface_available_qty
9703: FROM rcv_transactions_interface rti,
9704: po_distributions pod
9705: WHERE interface_transaction_id = v_parent_inter_trx_id
9706: AND pod.distribution_num = x_dist_num
9707: AND pod.po_header_id = rti.po_header_id
9748: pod.ussgl_transaction_code,
9749: rt.quantity qty,
9750: 0 interface_available_qty
9751: FROM rcv_supply rsup,
9752: rcv_transactions rt,
9753: rcv_shipment_lines rsl,
9754: po_headers poh,
9755: po_lines pol,
9756: po_distributions pod
9785: cascaded_table_index NUMBER;
9786: l_num_of_distributions NUMBER;
9787: temp_index NUMBER;
9788: l_supply_code rcv_supply.supply_type_code%TYPE;
9789: l_transaction_type rcv_transactions.transaction_type%TYPE;
9790: /* FPJ SERVICES */
9791: l_value_basis po_lines.order_type_lookup_code%TYPE;
9792: l_purchase_basis po_lines.purchase_basis%TYPE;
9793: l_matching_basis po_lines.matching_basis%TYPE;
9835:
9836: IF (x_cascaded_table(n).parent_transaction_id IS NOT NULL) THEN --{
9837: SELECT po_header_id
9838: INTO x_cascaded_table(n).po_header_id
9839: FROM rcv_transactions
9840: WHERE transaction_id = x_cascaded_table(n).parent_transaction_id;
9841:
9842: SELECT COUNT(*)
9843: INTO l_num_of_distributions
9890: END IF;
9891: EXCEPTION
9892: WHEN OTHERS THEN
9893: IF (g_asn_debug = 'Y') THEN
9894: asn_debug.put_line('wrong po_distribution_id entered in rcv_transactions_interface');
9895: END IF;
9896: END;
9897: END IF;
9898:
9926: */
9927: BEGIN
9928: SELECT transaction_id
9929: INTO x_cascaded_table(n).parent_transaction_id
9930: FROM rcv_transactions
9931: WHERE source_transaction_num = x_cascaded_table(n).parent_source_transaction_num;
9932:
9933: already_derived := TRUE;
9934: EXCEPTION
9981: SELECT COUNT(*)
9982: INTO l_num_of_distributions
9983: FROM po_distributions
9984: WHERE line_location_id = (SELECT po_line_location_id
9985: FROM rcv_transactions_interface
9986: WHERE interface_transaction_id = x_cascaded_table(n).parent_interface_txn_id);
9987:
9988: IF (g_asn_debug = 'Y') THEN
9989: asn_debug.put_line('l_num_of_distributions ' || l_num_of_distributions);
10022:
10023: EXCEPTION
10024: WHEN OTHERS THEN
10025: IF (g_asn_debug = 'Y') THEN
10026: asn_debug.put_line('wrong po_distribution_id entered in rcv_transactions_interface');
10027: END IF;
10028: END;
10029: END IF;
10030: -- } bug 5354379
10077: -- the following steps will create a set of rows linking the line_record with
10078: -- its corresponding po_line_location rows until the quantity value from
10079: -- the asn is consumed. (Cascade)
10080:
10081: /* 2119137 : If the user populates rcv_transactions_interface
10082: with po_line_id, then ROI errors out with
10083: RCV_ASN_NO_PO_LINE_LOCATION_ID when the docment_line_num
10084: is not provided for one time items. Modified the "if" criteria in
10085: such a way that the ROI validation does'nt error out when
10386: END IF;
10387:
10388: SELECT po_line_id,po_header_id
10389: INTO l_service_po_line_id,l_po_header_id
10390: FROM rcv_transactions
10391: WHERE transaction_id = x_cascaded_table(n).parent_transaction_id;
10392: ELSIF(x_cascaded_table(n).parent_interface_txn_id IS NOT NULL) THEN
10393: IF (g_asn_debug = 'Y') THEN
10394: asn_debug.put_line('parent_interface_txn_id ' || x_cascaded_table(n).parent_interface_txn_id);
10395: END IF;
10396:
10397: SELECT po_line_id,po_header_id
10398: INTO l_service_po_line_id,l_po_header_id
10399: FROM rcv_transactions_interface
10400: WHERE interface_transaction_id = x_cascaded_table(n).parent_interface_txn_id;
10401: END IF;
10402:
10403: IF (g_asn_debug = 'Y') THEN
10549: IF (x_cascaded_table(n).parent_transaction_id IS NOT NULL) THEN --{
10550:
10551: SELECT transaction_type
10552: INTO l_transaction_type
10553: FROM rcv_transactions
10554: WHERE transaction_id = temp_cascaded_table(current_n).parent_transaction_id;
10555:
10556: IF l_transaction_type = 'UNORDERED' THEN
10557: x_cascaded_table(n).error_status := rcv_error_pkg.g_ret_sts_error;
10683: transaction_type
10684: INTO l_parent_interface_txn_id,
10685: l_parent_transaction_id,
10686: l_transaction_type
10687: FROM rcv_transactions_interface
10688: WHERE interface_transaction_id = l_interface_txn_id;
10689:
10690: IF l_parent_transaction_id IS NOT NULL THEN--{
10691: --parent txn is available in rcv_transactions table
10687: FROM rcv_transactions_interface
10688: WHERE interface_transaction_id = l_interface_txn_id;
10689:
10690: IF l_parent_transaction_id IS NOT NULL THEN--{
10691: --parent txn is available in rcv_transactions table
10692: l_transaction_id := l_parent_transaction_id;
10693: LOOP --{ loop2
10694: --Fetch the record from rcv_transactions table
10695: IF (g_asn_debug = 'Y') THEN
10690: IF l_parent_transaction_id IS NOT NULL THEN--{
10691: --parent txn is available in rcv_transactions table
10692: l_transaction_id := l_parent_transaction_id;
10693: LOOP --{ loop2
10694: --Fetch the record from rcv_transactions table
10695: IF (g_asn_debug = 'Y') THEN
10696: asn_debug.put_line('Inside LOOP2 to fetch RECEIVE txn_id....');
10697: asn_debug.put_line('Fetching record from RT, l_transaction_id'||l_transaction_id);
10698: END IF;
10700: SELECT transaction_type,
10701: parent_transaction_id
10702: INTO l_transaction_type,
10703: l_parent_transaction_id
10704: FROM rcv_transactions
10705: WHERE transaction_id = l_transaction_id;
10706:
10707: IF l_transaction_type = 'RECEIVE' THEN --{
10708: --exit loop1 and loop2
10727: END IF; --} l_transaction_type = 'RECEIVE'
10728: END LOOP; --{ loop2
10729:
10730: ELSIF l_parent_interface_txn_id IS NOT NULL THEN --{ l_parent_interface_txn_id IS NOT NULL
10731: --parent txn is available in rcv_transactions_intreface table
10732: --continue LOOP1, till you fetch RECEIVE txnid
10733: l_interface_txn_id := l_parent_interface_txn_id;
10734: --}l_parent_interface_txn_id IS NOT NULL
10735: ELSE --{
10784: x_cascaded_table(n).error_status := 'E';
10785: ELSIF l_loop_exit_status = 'S' THEN --{ l_loop_exit_status = 'S'
10786: --Check where to get the AVAILABLE qty for this DELIVER txn
10787: -- if l_parent_receive_txn_in_rt is 'Y', Fetch the AVAILABLE qty from mtl_supply
10788: -- if l_parent_receive_txn_in_rt is 'N', Fetch the AVAILABLE qty from rcv_transactions_interface
10789: IF l_parent_receive_txn_in_rt = 'Y' THEN--{ l_parent_receive_txn_in_rt = 'Y'
10790: --Fetch AVAILABLE qty from mtl_supply using l_parent_transaction_id
10791: BEGIN --{
10792: IF (g_asn_debug = 'Y') THEN
10862: --based on the shipment_level quantity available in mtl_supply
10863: BEGIN--{
10864: SELECT nvl(sum(quantity),0)
10865: INTO l_total_deliver_quantity
10866: FROM rcv_transactions_interface
10867: WHERE (transaction_status_code = 'PENDING'
10868: AND processing_status_code <> 'ERROR')
10869: AND transaction_type = 'DELIVER'
10870: AND group_id = x_cascaded_table(n).group_id
10906: rcv_error_pkg.log_interface_error('QUANTITY',FALSE);
10907: END IF;--} x_cascaded_table(n).quantity > (l_distribution_qty - l_total_deliver_quantity)
10908: --} l_parent_receive_txn_in_rt = 'Y'
10909: ELSIF l_parent_receive_txn_in_rt = 'N' THEN--{ l_parent_receive_txn_in_rt = 'N'
10910: --Fetch the AVAILABLE qty from rcv_transactions_interface using l_parent_interface_txn_id
10911: IF (g_asn_debug = 'Y') THEN
10912: asn_debug.put_line('Fetching AVAILABLE qty from fetched RECEIVE interface_txn_id....');
10913: END IF;
10914:
10923:
10924: BEGIN --{
10925: SELECT nvl(sum(quantity),0)
10926: INTO l_total_receiving_quantity
10927: FROM rcv_transactions_interface
10928: WHERE (transaction_status_code = 'PENDING'
10929: AND processing_status_code <> 'ERROR')
10930: AND transaction_type = 'RECEIVE'
10931: AND auto_transact_code <> 'DELIVER'
10952:
10953: BEGIN
10954: SELECT nvl(sum(quantity),0)
10955: INTO l_total_deliver_quantity
10956: FROM rcv_transactions_interface
10957: WHERE (transaction_status_code = 'PENDING'
10958: AND processing_status_code <> 'ERROR')
10959: AND transaction_type = 'DELIVER'
10960: AND group_id = x_cascaded_table(n).group_id
10975:
10976: --Fetch the receive quantity for the 'RECEIVE'txn of the current DELIVER txn
10977: SELECT quantity
10978: INTO l_receive_quantity
10979: FROM rcv_transactions_interface
10980: WHERE interface_transaction_id = l_parent_interface_txn_id;
10981:
10982: IF (g_asn_debug = 'Y') THEN
10983: asn_debug.put_line('Receive qty for RECEIVE txn...'||l_receive_quantity);
10985: END IF;
10986: --Fetch the deliver quantity for the current 'DELIVER'txn
10987: SELECT quantity
10988: INTO l_deliver_quantity
10989: FROM rcv_transactions_interface
10990: WHERE interface_transaction_id = x_cascaded_table(n).interface_transaction_id;
10991:
10992: IF (g_asn_debug = 'Y') THEN
10993: asn_debug.put_line('Deliver qty for DELIVER txn...'||l_deliver_quantity);
11485: rt.oe_order_line_id,
11486: rt.customer_id,
11487: rt.customer_site_id,
11488: rt.source_document_code
11489: FROM rcv_transactions rt,
11490: rcv_shipment_lines rsl,
11491: rcv_supply rsup
11492: WHERE rt.transaction_id = v_parent_trx_id
11493: AND rt.transaction_id = rsup.rcv_transaction_id
11526: rti.oe_order_line_id,
11527: rti.customer_id,
11528: rti.customer_site_id,
11529: rti.source_document_code
11530: FROM rcv_transactions_interface rti
11531: WHERE interface_transaction_id = v_parent_inter_trx_id;
11532:
11533: default_po_transfer_info po_transfer%ROWTYPE;
11534: x_progress VARCHAR2(3);
11881: END IF;
11882:
11883: SELECT po_line_id,po_header_id
11884: INTO x_cascaded_table(n).po_line_id,l_po_header_id
11885: FROM rcv_transactions
11886: WHERE transaction_id = x_cascaded_table(n).parent_transaction_id;
11887: ELSIF(x_cascaded_table(n).parent_interface_txn_id IS NOT NULL) THEN
11888: IF (g_asn_debug = 'Y') THEN
11889: asn_debug.put_line('parent_interface_txn_id ' || x_cascaded_table(n).parent_interface_txn_id);
11890: END IF;
11891:
11892: SELECT po_line_id,po_header_id
11893: INTO x_cascaded_table(n).po_line_id,l_po_header_id
11894: FROM rcv_transactions_interface
11895: WHERE interface_transaction_id = x_cascaded_table(n).parent_interface_txn_id;
11896: END IF;
11897:
11898: IF (g_asn_debug = 'Y') THEN
11988: x_progress VARCHAR2(3);
11989: x_to_organization_code VARCHAR2(5);
11990: x_converted_trx_qty NUMBER := 0;
11991: transaction_ok BOOLEAN := FALSE;
11992: x_expected_date rcv_transactions_interface.expected_receipt_date%TYPE;
11993: high_range_date DATE;
11994: low_range_date DATE;
11995: rows_fetched NUMBER := 0;
11996: x_tolerable_qty NUMBER := 0;
12066: rt.wip_repetitive_schedule_id,
12067: rt.wip_line_id,
12068: rt.bom_resource_id,
12069: rsl.ussgl_transaction_code
12070: FROM rcv_transactions rt,
12071: rcv_shipment_lines rsl,
12072: po_headers poh,
12073: po_lines pol
12074: WHERE rt.transaction_id = v_parent_trx_id
12075: AND rt.organization_id = NVL(v_to_organization_id, rt.organization_id)
12076: AND ( rt.transaction_type IN('RECEIVE', 'TRANSFER', 'ACCEPT', 'REJECT', 'MATCH')
12077: OR ( rt.transaction_type IN('RETURN TO VENDOR', 'RETURN TO CUSTOMER')
12078: AND NOT EXISTS(SELECT 'rtv to processed matched unordered receipt'
12079: FROM rcv_transactions rt2,
12080: rcv_transactions rt3
12081: WHERE rt2.transaction_id = rt.parent_transaction_id
12082: AND rt2.transaction_type = 'UNORDERED'
12083: AND rt3.transaction_type = 'MATCH'
12076: AND ( rt.transaction_type IN('RECEIVE', 'TRANSFER', 'ACCEPT', 'REJECT', 'MATCH')
12077: OR ( rt.transaction_type IN('RETURN TO VENDOR', 'RETURN TO CUSTOMER')
12078: AND NOT EXISTS(SELECT 'rtv to processed matched unordered receipt'
12079: FROM rcv_transactions rt2,
12080: rcv_transactions rt3
12081: WHERE rt2.transaction_id = rt.parent_transaction_id
12082: AND rt2.transaction_type = 'UNORDERED'
12083: AND rt3.transaction_type = 'MATCH'
12084: AND rt3.parent_transaction_id = rt2.transaction_id)
12082: AND rt2.transaction_type = 'UNORDERED'
12083: AND rt3.transaction_type = 'MATCH'
12084: AND rt3.parent_transaction_id = rt2.transaction_id)
12085: AND NOT EXISTS(SELECT 'rtv to unprocessed matched unordered receipt'
12086: FROM rcv_transactions rt2,
12087: rcv_transactions_interface rti
12088: WHERE rt2.transaction_id = rt.parent_transaction_id
12089: AND rt2.transaction_type = 'UNORDERED'
12090: AND rti.transaction_type = 'MATCH'
12083: AND rt3.transaction_type = 'MATCH'
12084: AND rt3.parent_transaction_id = rt2.transaction_id)
12085: AND NOT EXISTS(SELECT 'rtv to unprocessed matched unordered receipt'
12086: FROM rcv_transactions rt2,
12087: rcv_transactions_interface rti
12088: WHERE rt2.transaction_id = rt.parent_transaction_id
12089: AND rt2.transaction_type = 'UNORDERED'
12090: AND rti.transaction_type = 'MATCH'
12091: AND rti.parent_transaction_id = rt2.transaction_id)
12094: AND NOT( rt.destination_type_code = 'INVENTORY'
12095: AND rt.source_document_code IN('REQ', 'INVENTORY', 'RMA')))
12096: OR ( rt.transaction_type = 'UNORDERED'
12097: AND NOT EXISTS(SELECT 'processed matched rows'
12098: FROM rcv_transactions rt2
12099: WHERE rt2.parent_transaction_id = rt.transaction_id
12100: AND rt2.transaction_type = 'MATCH')
12101: AND NOT EXISTS(SELECT 'unprocessed matched rows'
12102: FROM rcv_transactions_interface rti
12098: FROM rcv_transactions rt2
12099: WHERE rt2.parent_transaction_id = rt.transaction_id
12100: AND rt2.transaction_type = 'MATCH')
12101: AND NOT EXISTS(SELECT 'unprocessed matched rows'
12102: FROM rcv_transactions_interface rti
12103: WHERE rti.parent_transaction_id = rt.transaction_id
12104: AND rti.transaction_type = 'MATCH')
12105: )
12106: )
12150: rti.wip_repetitive_schedule_id,
12151: rti.wip_line_id,
12152: rti.bom_resource_id,
12153: rti.ussgl_transaction_code
12154: FROM rcv_transactions_interface rti
12155: WHERE interface_transaction_id = v_parent_interface_txn_id;
12156:
12157: x_po_correctrec po_correct%ROWTYPE;
12158: x_temp_parent_trx_qty NUMBER := 0;
12159: x_converted_parent_trx_qty NUMBER := 0;
12160: x_temp_convert_parent_trx_qty NUMBER := 0;
12161: x_remaining_qty_parent_uom NUMBER := 0;
12162: l_to_organization_id NUMBER := 0;
12163: l_transaction_type rcv_transactions.transaction_type%TYPE;
12164: x_temp_already_allocated_qty NUMBER := 0;
12165: derive_values_from_table BOOLEAN := FALSE;
12166: derive_values_from_rti BOOLEAN := FALSE;
12167: already_derived BOOLEAN := FALSE;
12165: derive_values_from_table BOOLEAN := FALSE;
12166: derive_values_from_rti BOOLEAN := FALSE;
12167: already_derived BOOLEAN := FALSE;
12168: cascaded_table_index NUMBER;
12169: l_grand_parent_trx_id rcv_transactions.parent_transaction_id%TYPE;
12170: temp_index NUMBER;
12171: l_line_loc_id po_line_locations.line_location_id%TYPE;
12172: l_exist VARCHAR2(30);
12173: l_exist1 VARCHAR2(30);
12171: l_line_loc_id po_line_locations.line_location_id%TYPE;
12172: l_exist VARCHAR2(30);
12173: l_exist1 VARCHAR2(30);
12174: l_exist2 VARCHAR2(30);
12175: l_destination_type_code rcv_transactions.destination_type_code%TYPE;
12176: l_source_document_code rcv_transactions.source_document_code%TYPE;
12177: /* Bug#5369121 */
12178: l_primary_uom rcv_transactions_interface.unit_of_measure%TYPE;
12179: l_transaction_uom rcv_transactions.unit_of_measure%TYPE;
12172: l_exist VARCHAR2(30);
12173: l_exist1 VARCHAR2(30);
12174: l_exist2 VARCHAR2(30);
12175: l_destination_type_code rcv_transactions.destination_type_code%TYPE;
12176: l_source_document_code rcv_transactions.source_document_code%TYPE;
12177: /* Bug#5369121 */
12178: l_primary_uom rcv_transactions_interface.unit_of_measure%TYPE;
12179: l_transaction_uom rcv_transactions.unit_of_measure%TYPE;
12180: l_interface_quantity NUMBER;
12174: l_exist2 VARCHAR2(30);
12175: l_destination_type_code rcv_transactions.destination_type_code%TYPE;
12176: l_source_document_code rcv_transactions.source_document_code%TYPE;
12177: /* Bug#5369121 */
12178: l_primary_uom rcv_transactions_interface.unit_of_measure%TYPE;
12179: l_transaction_uom rcv_transactions.unit_of_measure%TYPE;
12180: l_interface_quantity NUMBER;
12181: l_interface_qty_in_trx_uom NUMBER;
12182: l_item_id NUMBER;
12175: l_destination_type_code rcv_transactions.destination_type_code%TYPE;
12176: l_source_document_code rcv_transactions.source_document_code%TYPE;
12177: /* Bug#5369121 */
12178: l_primary_uom rcv_transactions_interface.unit_of_measure%TYPE;
12179: l_transaction_uom rcv_transactions.unit_of_measure%TYPE;
12180: l_interface_quantity NUMBER;
12181: l_interface_qty_in_trx_uom NUMBER;
12182: l_item_id NUMBER;
12183: /* Bug#5369121 */
12199: */
12200: BEGIN
12201: SELECT transaction_id
12202: INTO x_cascaded_table(n).parent_transaction_id
12203: FROM rcv_transactions
12204: WHERE source_transaction_num = x_cascaded_table(n).parent_source_transaction_num;
12205:
12206: already_derived := TRUE;
12207: EXCEPTION
12267: -- the following steps will create a set of rows linking the line_record with
12268: -- its corresponding po_line_location rows until the quantity value from
12269: -- the asn is consumed. (Cascade)
12270:
12271: /* 2119137 : If the user populates rcv_transactions_interface
12272: with po_line_id, then ROI errors out with
12273: RCV_ASN_NO_PO_LINE_LOCATION_ID when the docment_line_num
12274: is not provided for one time items. Modified the "if" criteria in
12275: such a way that the ROI validation does'nt error out when
12509: END IF;
12510:
12511: temp_cascaded_table(current_n).quantity := temp_cascaded_table(current_n).quantity + x_remaining_quantity;
12512: temp_cascaded_table(current_n).primary_quantity := temp_cascaded_table(current_n).primary_quantity
12513: + rcv_transactions_interface_sv.convert_into_correct_qty(x_remaining_quantity,
12514: temp_cascaded_table(1).unit_of_measure,
12515: temp_cascaded_table(1).item_id,
12516: temp_cascaded_table(1).primary_unit_of_measure
12517: );
12661: l_transaction_type,
12662: l_line_loc_id,
12663: l_destination_type_code,
12664: l_source_document_code
12665: FROM rcv_transactions rt
12666: WHERE rt.transaction_id = temp_cascaded_table(current_n).parent_transaction_id;
12667:
12668: IF (l_transaction_type = 'DELIVER') THEN --{
12669: IF ( l_destination_type_code = 'INVENTORY'
12676:
12677: IF (l_transaction_type = 'UNORDERED') THEN --{
12678: SELECT MAX('record_exist')
12679: INTO l_exist
12680: FROM rcv_transactions rt2
12681: WHERE rt2.parent_transaction_id = temp_cascaded_table(current_n).parent_transaction_id
12682: AND rt2.transaction_type = 'MATCH';
12683:
12684: SELECT MAX('record_exist')
12682: AND rt2.transaction_type = 'MATCH';
12683:
12684: SELECT MAX('record_exist')
12685: INTO l_exist1
12686: FROM rcv_transactions_interface rti
12687: WHERE rti.parent_transaction_id = temp_cascaded_table(current_n).parent_transaction_id
12688: AND rti.transaction_type = 'MATCH';
12689:
12690: IF l_exist = 'record_exist' THEN --{
12702:
12703: IF (l_transaction_type = 'RETURN TO VENDOR') THEN --{
12704: SELECT MAX('record_exist')
12705: INTO l_exist
12706: FROM rcv_transactions rt2,
12707: rcv_transactions rt3
12708: WHERE rt2.transaction_id = l_grand_parent_trx_id
12709: AND rt2.transaction_type = 'UNORDERED'
12710: AND rt3.transaction_type = 'MATCH'
12703: IF (l_transaction_type = 'RETURN TO VENDOR') THEN --{
12704: SELECT MAX('record_exist')
12705: INTO l_exist
12706: FROM rcv_transactions rt2,
12707: rcv_transactions rt3
12708: WHERE rt2.transaction_id = l_grand_parent_trx_id
12709: AND rt2.transaction_type = 'UNORDERED'
12710: AND rt3.transaction_type = 'MATCH'
12711: AND rt3.parent_transaction_id = rt2.transaction_id;
12711: AND rt3.parent_transaction_id = rt2.transaction_id;
12712:
12713: SELECT MAX('record_exist')
12714: INTO l_exist1
12715: FROM rcv_transactions rt2,
12716: rcv_transactions_interface rti
12717: WHERE rt2.transaction_id = l_grand_parent_trx_id
12718: AND rt2.transaction_type = 'UNORDERED'
12719: AND rti.transaction_type = 'MATCH'
12712:
12713: SELECT MAX('record_exist')
12714: INTO l_exist1
12715: FROM rcv_transactions rt2,
12716: rcv_transactions_interface rti
12717: WHERE rt2.transaction_id = l_grand_parent_trx_id
12718: AND rt2.transaction_type = 'UNORDERED'
12719: AND rti.transaction_type = 'MATCH'
12720: AND rti.parent_transaction_id = rt2.transaction_id;
12849: rt.primary_unit_of_measure
12850: INTO l_transaction_uom,
12851: l_item_id,
12852: l_primary_uom
12853: FROM rcv_transactions rt,
12854: rcv_shipment_lines rsl
12855: WHERE rsl.shipment_line_id = rt.shipment_line_id
12856: AND rt.transaction_id = x_po_correctrec.rcv_transaction_id;
12857:
12856: AND rt.transaction_id = x_po_correctrec.rcv_transaction_id;
12857:
12858: SELECT NVL(SUM(interface_transaction_qty),0)
12859: INTO l_interface_quantity
12860: FROM rcv_transactions_interface
12861: WHERE (transaction_status_code = 'PENDING'
12862: AND processing_status_code <> 'ERROR')
12863: AND group_id = temp_cascaded_table(current_n).group_id
12864: AND transaction_type = 'CORRECT'
12862: AND processing_status_code <> 'ERROR')
12863: AND group_id = temp_cascaded_table(current_n).group_id
12864: AND transaction_type = 'CORRECT'
12865: AND parent_transaction_id IN ( SELECT transaction_id
12866: FROM rcv_transactions
12867: WHERE parent_transaction_id = x_po_correctrec.rcv_transaction_id);
12868:
12869: IF (l_interface_quantity = 0) THEN
12870:
13144: x_progress VARCHAR2(3);
13145: x_to_organization_code VARCHAR2(5);
13146: x_converted_trx_amt NUMBER := 0;
13147: transaction_ok BOOLEAN := FALSE;
13148: x_expected_date rcv_transactions_interface.expected_receipt_date%TYPE;
13149: high_range_date DATE;
13150: low_range_date DATE;
13151: rows_fetched NUMBER := 0;
13152: x_tolerable_amt NUMBER := 0;
13195: rt.deliver_to_location_id,
13196: rt.po_distribution_id,
13197: rt.destination_type_code,
13198: rsl.ussgl_transaction_code
13199: FROM rcv_transactions rt,
13200: rcv_shipment_lines rsl,
13201: po_headers poh,
13202: po_lines pol
13203: WHERE rt.transaction_id = v_parent_trx_id
13204: AND rt.organization_id = NVL(v_to_organization_id, rt.organization_id)
13205: AND ( rt.transaction_type IN('RECEIVE', 'TRANSFER', 'ACCEPT', 'REJECT', 'MATCH')
13206: OR ( rt.transaction_type IN('RETURN TO VENDOR', 'RETURN TO CUSTOMER')
13207: AND NOT EXISTS(SELECT 'rtv to processed matched unordered receipt'
13208: FROM rcv_transactions rt2,
13209: rcv_transactions rt3
13210: WHERE rt2.transaction_id = rt.parent_transaction_id
13211: AND rt2.transaction_type = 'UNORDERED'
13212: AND rt3.transaction_type = 'MATCH'
13205: AND ( rt.transaction_type IN('RECEIVE', 'TRANSFER', 'ACCEPT', 'REJECT', 'MATCH')
13206: OR ( rt.transaction_type IN('RETURN TO VENDOR', 'RETURN TO CUSTOMER')
13207: AND NOT EXISTS(SELECT 'rtv to processed matched unordered receipt'
13208: FROM rcv_transactions rt2,
13209: rcv_transactions rt3
13210: WHERE rt2.transaction_id = rt.parent_transaction_id
13211: AND rt2.transaction_type = 'UNORDERED'
13212: AND rt3.transaction_type = 'MATCH'
13213: AND rt3.parent_transaction_id = rt2.transaction_id)
13211: AND rt2.transaction_type = 'UNORDERED'
13212: AND rt3.transaction_type = 'MATCH'
13213: AND rt3.parent_transaction_id = rt2.transaction_id)
13214: AND NOT EXISTS(SELECT 'rtv to unprocessed matched unordered receipt'
13215: FROM rcv_transactions rt2,
13216: rcv_transactions_interface rti
13217: WHERE rt2.transaction_id = rt.parent_transaction_id
13218: AND rt2.transaction_type = 'UNORDERED'
13219: AND rti.transaction_type = 'MATCH'
13212: AND rt3.transaction_type = 'MATCH'
13213: AND rt3.parent_transaction_id = rt2.transaction_id)
13214: AND NOT EXISTS(SELECT 'rtv to unprocessed matched unordered receipt'
13215: FROM rcv_transactions rt2,
13216: rcv_transactions_interface rti
13217: WHERE rt2.transaction_id = rt.parent_transaction_id
13218: AND rt2.transaction_type = 'UNORDERED'
13219: AND rti.transaction_type = 'MATCH'
13220: AND rti.parent_transaction_id = rt2.transaction_id)
13223: AND NOT( rt.destination_type_code = 'INVENTORY'
13224: AND rt.source_document_code IN('REQ', 'INVENTORY', 'RMA')))
13225: OR ( rt.transaction_type = 'UNORDERED'
13226: AND NOT EXISTS(SELECT 'processed matched rows'
13227: FROM rcv_transactions rt2
13228: WHERE rt2.parent_transaction_id = rt.transaction_id
13229: AND rt2.transaction_type = 'MATCH')
13230: AND NOT EXISTS(SELECT 'unprocessed matched rows'
13231: FROM rcv_transactions_interface rti
13227: FROM rcv_transactions rt2
13228: WHERE rt2.parent_transaction_id = rt.transaction_id
13229: AND rt2.transaction_type = 'MATCH')
13230: AND NOT EXISTS(SELECT 'unprocessed matched rows'
13231: FROM rcv_transactions_interface rti
13232: WHERE rti.parent_transaction_id = rt.transaction_id
13233: AND rti.transaction_type = 'MATCH')
13234: )
13235: )
13263: rti.deliver_to_location_id,
13264: rti.po_distribution_id,
13265: rti.destination_type_code,
13266: rti.ussgl_transaction_code
13267: FROM rcv_transactions_interface rti
13268: WHERE interface_transaction_id = v_parent_interface_txn_id;
13269:
13270: x_po_correctrec po_correct%ROWTYPE;
13271: x_temp_parent_trx_amt NUMBER := 0;
13271: x_temp_parent_trx_amt NUMBER := 0;
13272: x_converted_parent_trx_amt NUMBER := 0;
13273: x_temp_convert_parent_trx_amt NUMBER := 0;
13274: l_to_organization_id NUMBER := 0;
13275: l_transaction_type rcv_transactions.transaction_type%TYPE;
13276: x_temp_already_allocated_qty NUMBER := 0;
13277: derive_values_from_table BOOLEAN := FALSE;
13278: derive_values_from_rti BOOLEAN := FALSE;
13279: already_derived BOOLEAN := FALSE;
13277: derive_values_from_table BOOLEAN := FALSE;
13278: derive_values_from_rti BOOLEAN := FALSE;
13279: already_derived BOOLEAN := FALSE;
13280: cascaded_table_index NUMBER;
13281: l_grand_parent_trx_id rcv_transactions.parent_transaction_id%TYPE;
13282: temp_index NUMBER;
13283: l_line_loc_id po_line_locations.line_location_id%TYPE;
13284: l_exist VARCHAR2(30);
13285: l_exist1 VARCHAR2(30);
13283: l_line_loc_id po_line_locations.line_location_id%TYPE;
13284: l_exist VARCHAR2(30);
13285: l_exist1 VARCHAR2(30);
13286: l_exist2 VARCHAR2(30);
13287: l_destination_type_code rcv_transactions.destination_type_code%TYPE;
13288: l_source_document_code rcv_transactions.source_document_code%TYPE;
13289: l_time_count NUMBER;
13290: BEGIN
13291: IF (g_asn_debug = 'Y') THEN
13284: l_exist VARCHAR2(30);
13285: l_exist1 VARCHAR2(30);
13286: l_exist2 VARCHAR2(30);
13287: l_destination_type_code rcv_transactions.destination_type_code%TYPE;
13288: l_source_document_code rcv_transactions.source_document_code%TYPE;
13289: l_time_count NUMBER;
13290: BEGIN
13291: IF (g_asn_debug = 'Y') THEN
13292: asn_debug.put_line('enter derive_amount ' || x_cascaded_table(n).parent_transaction_id);
13304: */
13305: BEGIN
13306: SELECT transaction_id
13307: INTO x_cascaded_table(n).parent_transaction_id
13308: FROM rcv_transactions
13309: WHERE source_transaction_num = x_cascaded_table(n).parent_source_transaction_num;
13310:
13311: already_derived := TRUE;
13312: EXCEPTION
13375: -- the following steps will create a set of rows linking the line_record with
13376: -- its corresponding po_line_location rows until the quantity value from
13377: -- the asn is consumed. (Cascade)
13378:
13379: /* 2119137 : If the user populates rcv_transactions_interface
13380: with po_line_id, then ROI errors out with
13381: RCV_ASN_NO_PO_LINE_LOCATION_ID when the docment_line_num
13382: is not provided for one time items. Modified the "if" criteria in
13383: such a way that the ROI validation does'nt error out when
13766: l_transaction_type,
13767: l_line_loc_id,
13768: l_destination_type_code,
13769: l_source_document_code
13770: FROM rcv_transactions rt
13771: WHERE rt.transaction_id = temp_cascaded_table(current_n).parent_transaction_id;
13772:
13773: IF (l_transaction_type = 'DELIVER') THEN --{
13774: IF ( l_destination_type_code = 'INVENTORY'
13781:
13782: IF (l_transaction_type = 'UNORDERED') THEN --{
13783: SELECT MAX('record_exist')
13784: INTO l_exist
13785: FROM rcv_transactions rt2
13786: WHERE rt2.parent_transaction_id = temp_cascaded_table(current_n).parent_transaction_id
13787: AND rt2.transaction_type = 'MATCH';
13788:
13789: SELECT MAX('record_exist')
13787: AND rt2.transaction_type = 'MATCH';
13788:
13789: SELECT MAX('record_exist')
13790: INTO l_exist1
13791: FROM rcv_transactions_interface rti
13792: WHERE rti.parent_transaction_id = temp_cascaded_table(current_n).parent_transaction_id
13793: AND rti.transaction_type = 'MATCH';
13794:
13795: IF l_exist = 'record_exist' THEN --{
13807:
13808: IF (l_transaction_type = 'RETURN TO VENDOR') THEN --{
13809: SELECT MAX('record_exist')
13810: INTO l_exist
13811: FROM rcv_transactions rt2,
13812: rcv_transactions rt3
13813: WHERE rt2.transaction_id = l_grand_parent_trx_id
13814: AND rt2.transaction_type = 'UNORDERED'
13815: AND rt3.transaction_type = 'MATCH'
13808: IF (l_transaction_type = 'RETURN TO VENDOR') THEN --{
13809: SELECT MAX('record_exist')
13810: INTO l_exist
13811: FROM rcv_transactions rt2,
13812: rcv_transactions rt3
13813: WHERE rt2.transaction_id = l_grand_parent_trx_id
13814: AND rt2.transaction_type = 'UNORDERED'
13815: AND rt3.transaction_type = 'MATCH'
13816: AND rt3.parent_transaction_id = rt2.transaction_id;
13816: AND rt3.parent_transaction_id = rt2.transaction_id;
13817:
13818: SELECT MAX('record_exist')
13819: INTO l_exist1
13820: FROM rcv_transactions rt2,
13821: rcv_transactions_interface rti
13822: WHERE rt2.transaction_id = l_grand_parent_trx_id
13823: AND rt2.transaction_type = 'UNORDERED'
13824: AND rti.transaction_type = 'MATCH'
13817:
13818: SELECT MAX('record_exist')
13819: INTO l_exist1
13820: FROM rcv_transactions rt2,
13821: rcv_transactions_interface rti
13822: WHERE rt2.transaction_id = l_grand_parent_trx_id
13823: AND rt2.transaction_type = 'UNORDERED'
13824: AND rti.transaction_type = 'MATCH'
13825: AND rti.parent_transaction_id = rt2.transaction_id;
14178: rt.customer_id,
14179: rt.customer_site_id,
14180: rt.source_document_code,
14181: rt.job_id
14182: FROM rcv_transactions rt,
14183: rcv_shipment_lines rsl
14184: WHERE rt.transaction_id = v_parent_trx_id
14185: AND ( rt.transaction_type IN('RECEIVE', 'TRANSFER', 'ACCEPT', 'REJECT', 'MATCH')
14186: OR ( rt.transaction_type IN('RETURN TO VENDOR', 'RETURN TO CUSTOMER')
14184: WHERE rt.transaction_id = v_parent_trx_id
14185: AND ( rt.transaction_type IN('RECEIVE', 'TRANSFER', 'ACCEPT', 'REJECT', 'MATCH')
14186: OR ( rt.transaction_type IN('RETURN TO VENDOR', 'RETURN TO CUSTOMER')
14187: AND NOT EXISTS(SELECT 'rtv to processed matched unordered receipt'
14188: FROM rcv_transactions rt2,
14189: rcv_transactions rt3
14190: WHERE rt2.transaction_id = rt.parent_transaction_id
14191: AND rt2.transaction_type = 'UNORDERED'
14192: AND rt3.transaction_type = 'MATCH'
14185: AND ( rt.transaction_type IN('RECEIVE', 'TRANSFER', 'ACCEPT', 'REJECT', 'MATCH')
14186: OR ( rt.transaction_type IN('RETURN TO VENDOR', 'RETURN TO CUSTOMER')
14187: AND NOT EXISTS(SELECT 'rtv to processed matched unordered receipt'
14188: FROM rcv_transactions rt2,
14189: rcv_transactions rt3
14190: WHERE rt2.transaction_id = rt.parent_transaction_id
14191: AND rt2.transaction_type = 'UNORDERED'
14192: AND rt3.transaction_type = 'MATCH'
14193: AND rt3.parent_transaction_id = rt2.transaction_id)
14191: AND rt2.transaction_type = 'UNORDERED'
14192: AND rt3.transaction_type = 'MATCH'
14193: AND rt3.parent_transaction_id = rt2.transaction_id)
14194: AND NOT EXISTS(SELECT 'rtv to unprocessed matched unordered receipt'
14195: FROM rcv_transactions rt2,
14196: rcv_transactions_interface rti
14197: WHERE rt2.transaction_id = rt.parent_transaction_id
14198: AND rt2.transaction_type = 'UNORDERED'
14199: AND rti.transaction_type = 'MATCH'
14192: AND rt3.transaction_type = 'MATCH'
14193: AND rt3.parent_transaction_id = rt2.transaction_id)
14194: AND NOT EXISTS(SELECT 'rtv to unprocessed matched unordered receipt'
14195: FROM rcv_transactions rt2,
14196: rcv_transactions_interface rti
14197: WHERE rt2.transaction_id = rt.parent_transaction_id
14198: AND rt2.transaction_type = 'UNORDERED'
14199: AND rti.transaction_type = 'MATCH'
14200: AND rti.parent_transaction_id = rt2.transaction_id)
14203: AND NOT( rt.destination_type_code = 'INVENTORY'
14204: AND rt.source_document_code IN('REQ', 'INVENTORY')))
14205: OR ( rt.transaction_type = 'UNORDERED'
14206: AND NOT EXISTS(SELECT 'processed matched rows'
14207: FROM rcv_transactions rt2
14208: WHERE rt2.parent_transaction_id = rt.transaction_id
14209: AND rt2.transaction_type = 'MATCH')
14210: AND NOT EXISTS(SELECT 'unprocessed matched rows'
14211: FROM rcv_transactions_interface rti
14207: FROM rcv_transactions rt2
14208: WHERE rt2.parent_transaction_id = rt.transaction_id
14209: AND rt2.transaction_type = 'MATCH')
14210: AND NOT EXISTS(SELECT 'unprocessed matched rows'
14211: FROM rcv_transactions_interface rti
14212: WHERE rti.parent_transaction_id = rt.transaction_id
14213: AND rti.transaction_type = 'MATCH')
14214: )
14215: )
14255: rti.customer_id,
14256: rti.customer_site_id,
14257: rti.source_document_code,
14258: rti.job_id
14259: FROM rcv_transactions_interface rti
14260: WHERE interface_transaction_id = v_parent_inter_trx_id;
14261:
14262: default_po_correct_info po_correct%ROWTYPE;
14263: x_progress VARCHAR2(3);
14580: IF (g_asn_debug = 'Y') THEN
14581: asn_debug.put_line('Validating Source Type Code');
14582: END IF;
14583:
14584: rcv_transactions_interface_sv1.validate_po_lookup_code(po_lookup_code_record);
14585: x_cascaded_table(n).error_status := po_lookup_code_record.error_record.error_status;
14586: x_cascaded_table(n).error_message := po_lookup_code_record.error_record.error_message;
14587: rcv_error_pkg.log_interface_message(x_cascaded_table(n).error_status,
14588: 'SOURCE_TYPE_CODE',
14616: IF (g_asn_debug = 'Y') THEN
14617: asn_debug.put_line('Validating Destination Type Code');
14618: END IF;
14619:
14620: rcv_transactions_interface_sv1.validate_po_lookup_code(po_lookup_code_record);
14621: x_cascaded_table(n).error_status := po_lookup_code_record.error_record.error_status;
14622: x_cascaded_table(n).error_message := po_lookup_code_record.error_record.error_message;
14623: rcv_error_pkg.log_interface_message(x_cascaded_table(n).error_status,
14624: 'TRANSACTION_TYPE_CODE',
14652: IF (g_asn_debug = 'Y') THEN
14653: asn_debug.put_line('Validating INSPECTION STATUS ');
14654: END IF;
14655:
14656: rcv_transactions_interface_sv1.validate_po_lookup_code(po_lookup_code_record);
14657: x_cascaded_table(n).error_status := po_lookup_code_record.error_record.error_status;
14658: x_cascaded_table(n).error_message := po_lookup_code_record.error_record.error_message;
14659: rcv_error_pkg.log_interface_message(x_cascaded_table(n).error_status,
14660: 'INSPECTION_STATUS_CODE',
14678: ) IS
14679: /*INVCONV, gml_opm_roi_grp.opm_record_type to be removed , Punit Kumar */
14680: --l_opm_record_type gml_opm_roi_grp.opm_record_type;
14681:
14682: l_att_rec RCV_TRANSACTIONS_INTERFACE_SV1.attributes_record_type;
14683: l_column_name VARCHAR2(50);
14684: l_tokenname1 VARCHAR2(30);
14685: l_tokenvalue1 VARCHAR2(1800);
14686: l_quantity_shipped NUMBER;
14786: l_opm_record_type.qc_grade := x_cascaded_table(n).qc_grade;
14787: l_opm_record_type.rti_id := x_cascaded_table(n).interface_transaction_id;
14788: -- bug# 3664014
14789: -- added some more parameters. validate_opm_parameters API reads these parameters from
14790: -- rcv_transactions_interface table which does hold the latest data. All the latest data
14791: -- are in the cascaded table.
14792:
14793: l_opm_record_type.transaction_type := x_cascaded_table(n).transaction_type;
14794: l_opm_record_type.destination_type_code := x_cascaded_table(n).destination_type_code;
14823: l_att_rec.error_record.error_message := NULL;
14824:
14825:
14826: IF (g_asn_debug = 'Y') THEN
14827: asn_debug.put_line('printing the values before calling RCV_TRANSACTIONS_INTERFACE_SV1.VALIDATE_SECONDARY_PARAMETERS');
14828: asn_debug.put_line('unit_of_measure = ' || l_att_rec.transaction_unit_of_measure);
14829: asn_debug.put_line('secondary unit_of_measure = ' || l_att_rec.secondary_unit_of_measure);
14830: asn_debug.put_line('Quantity = ' || l_att_rec.transaction_quantity);
14831: asn_debug.put_line('Secondary Quantity = ' || l_att_rec.secondary_quantity);
14852:
14853: IF x_cascaded_table(n).item_id IS NOT NULL THEN
14854:
14855: IF (g_asn_debug = 'Y') THEN
14856: asn_debug.put_line('Before calling new API RCV_TRANSACTIONS_INTERFACE_SV1.VALIDATE_SECONDARY_PARAMETERS');
14857: END IF;
14858:
14859:
14860: RCV_TRANSACTIONS_INTERFACE_SV1.VALIDATE_SECONDARY_PARAMETERS (
14856: asn_debug.put_line('Before calling new API RCV_TRANSACTIONS_INTERFACE_SV1.VALIDATE_SECONDARY_PARAMETERS');
14857: END IF;
14858:
14859:
14860: RCV_TRANSACTIONS_INTERFACE_SV1.VALIDATE_SECONDARY_PARAMETERS (
14861: 1.0,
14862: 'F',
14863: x_att_rec =>l_att_rec,
14864: x_return_status =>l_return_status,
14884: x_cascaded_table(n).error_status := l_att_rec.error_record.error_status;
14885: x_cascaded_table(n).error_message := l_att_rec.error_record.error_message;
14886:
14887: IF (g_asn_debug = 'Y') THEN
14888: asn_debug.put_line('After calling new API RCV_TRANSACTIONS_INTERFACE_SV1.VALIDATE_SECONDARY_PARAMETERS');
14889: asn_debug.put_line('return status of RCV_TRANSACTIONS_INTERFACE_SV1.VALIDATE_SECONDARY_PARAMETERS = ' || x_cascaded_table(n).error_status);
14890: asn_debug.put_line('return message of RCV_TRANSACTIONS_INTERFACE_SV1.VALIDATE_SECONDARY_PARAMETERS = ' || x_cascaded_table(n).error_message);
14891: END IF;
14892:
14885: x_cascaded_table(n).error_message := l_att_rec.error_record.error_message;
14886:
14887: IF (g_asn_debug = 'Y') THEN
14888: asn_debug.put_line('After calling new API RCV_TRANSACTIONS_INTERFACE_SV1.VALIDATE_SECONDARY_PARAMETERS');
14889: asn_debug.put_line('return status of RCV_TRANSACTIONS_INTERFACE_SV1.VALIDATE_SECONDARY_PARAMETERS = ' || x_cascaded_table(n).error_status);
14890: asn_debug.put_line('return message of RCV_TRANSACTIONS_INTERFACE_SV1.VALIDATE_SECONDARY_PARAMETERS = ' || x_cascaded_table(n).error_message);
14891: END IF;
14892:
14893:
14886:
14887: IF (g_asn_debug = 'Y') THEN
14888: asn_debug.put_line('After calling new API RCV_TRANSACTIONS_INTERFACE_SV1.VALIDATE_SECONDARY_PARAMETERS');
14889: asn_debug.put_line('return status of RCV_TRANSACTIONS_INTERFACE_SV1.VALIDATE_SECONDARY_PARAMETERS = ' || x_cascaded_table(n).error_status);
14890: asn_debug.put_line('return message of RCV_TRANSACTIONS_INTERFACE_SV1.VALIDATE_SECONDARY_PARAMETERS = ' || x_cascaded_table(n).error_message);
14891: END IF;
14892:
14893:
14894: /*If call fails*/
14893:
14894: /*If call fails*/
14895: IF x_cascaded_table(n).error_status IN ('U' ,'E') THEN
14896: IF (g_asn_debug = 'Y') THEN
14897: asn_debug.put_line('RCV_TRANSACTIONS_INTERFACE_SV1.VALIDATE_SECONDARY_PARAMETERS call failed');
14898: END IF;
14899:
14900: /*log error in po_interface_errors*/
14901: rcv_error_pkg.log_interface_message(
14911: x_cascaded_table(n).secondary_uom_code := l_att_rec.secondary_uom_code;
14912: x_cascaded_table(n).secondary_quantity := l_att_rec.secondary_quantity;
14913:
14914: IF (g_asn_debug = 'Y') THEN
14915: asn_debug.put_line('populating the return values from RCV_TRANSACTIONS_INTERFACE_SV1.VALIDATE_SECONDARY_PARAMETERS');
14916: asn_debug.put_line('secondary unit_of_measure = ' || x_cascaded_table(n).secondary_unit_of_measure);
14917: asn_debug.put_line('Secondary Quantity = ' ||x_cascaded_table(n).secondary_quantity );
14918: asn_debug.put_line('Secondary_uom_code = ' ||x_cascaded_table(n).secondary_uom_code);
14919: END IF;
14952: x_interface_qty OUT NOCOPY NUMBER
14953: ) IS
14954: l_grandparent_interface_trx_id NUMBER;
14955: l_interface_qty NUMBER;
14956: l_parent_trx_type rcv_transactions_interface.transaction_type%TYPE;
14957: l_parent_uom rcv_transactions_interface.unit_of_measure%TYPE;
14958: l_grandparent_uom rcv_transactions_interface.unit_of_measure%TYPE;
14959: test NUMBER;
14960: BEGIN
14953: ) IS
14954: l_grandparent_interface_trx_id NUMBER;
14955: l_interface_qty NUMBER;
14956: l_parent_trx_type rcv_transactions_interface.transaction_type%TYPE;
14957: l_parent_uom rcv_transactions_interface.unit_of_measure%TYPE;
14958: l_grandparent_uom rcv_transactions_interface.unit_of_measure%TYPE;
14959: test NUMBER;
14960: BEGIN
14961: x_interface_qty := 0; --Initialise
14954: l_grandparent_interface_trx_id NUMBER;
14955: l_interface_qty NUMBER;
14956: l_parent_trx_type rcv_transactions_interface.transaction_type%TYPE;
14957: l_parent_uom rcv_transactions_interface.unit_of_measure%TYPE;
14958: l_grandparent_uom rcv_transactions_interface.unit_of_measure%TYPE;
14959: test NUMBER;
14960: BEGIN
14961: x_interface_qty := 0; --Initialise
14962:
14972: INTO l_grandparent_interface_trx_id,
14973: l_parent_trx_type,
14974: l_parent_uom,
14975: l_interface_qty
14976: FROM rcv_transactions_interface
14977: WHERE interface_transaction_id = x_cascaded_table(n).parent_interface_txn_id;
14978: EXCEPTION
14979: /* For a receive transaction, both parent_trx_id and
14980: * interface_parent_trx_id will be null.
14997: SELECT interface_available_qty,
14998: unit_of_measure
14999: INTO l_interface_qty,
15000: l_grandparent_uom
15001: FROM rcv_transactions_interface
15002: WHERE interface_transaction_id = l_grandparent_interface_trx_id;
15003:
15004: /* Should be in terms of parent transaction uom */
15005: x_interface_qty := convert_into_correct_qty(l_interface_qty,
15036: x_interface_amt OUT NOCOPY NUMBER
15037: ) IS
15038: l_grandparent_interface_trx_id NUMBER;
15039: l_interface_amt NUMBER;
15040: l_parent_trx_type rcv_transactions_interface.transaction_type%TYPE;
15041: test NUMBER;
15042: BEGIN
15043: x_interface_amt := 0; --Initialise
15044:
15052: interface_available_amt
15053: INTO l_grandparent_interface_trx_id,
15054: l_parent_trx_type,
15055: l_interface_amt
15056: FROM rcv_transactions_interface
15057: WHERE interface_transaction_id = x_cascaded_table(n).parent_interface_txn_id;
15058: EXCEPTION
15059: /* For a receive transaction, both parent_trx_id and
15060: * interface_parent_trx_id will be null.
15074: IF (l_grandparent_interface_trx_id IS NOT NULL) THEN --{
15075: BEGIN
15076: SELECT interface_available_amt
15077: INTO x_interface_amt
15078: FROM rcv_transactions_interface
15079: WHERE interface_transaction_id = l_grandparent_interface_trx_id;
15080: EXCEPTION
15081: WHEN NO_DATA_FOUND THEN
15082: x_interface_amt := 0;
15104: x_cascaded_table IN OUT NOCOPY rcv_roi_preprocessor.cascaded_trans_tab_type,
15105: n IN BINARY_INTEGER
15106: ) IS
15107: l_grandparent_interface_trx_id NUMBER;
15108: l_parent_trx_type rcv_transactions_interface.transaction_type%TYPE;
15109: l_interface_transaction_qty rcv_transactions_interface.interface_transaction_qty%TYPE := 0;
15110: BEGIN
15111: /* Update interface_available_qty for the current rti row. */
15112: x_cascaded_table(n).interface_available_qty := x_cascaded_table(n).quantity;
15105: n IN BINARY_INTEGER
15106: ) IS
15107: l_grandparent_interface_trx_id NUMBER;
15108: l_parent_trx_type rcv_transactions_interface.transaction_type%TYPE;
15109: l_interface_transaction_qty rcv_transactions_interface.interface_transaction_qty%TYPE := 0;
15110: BEGIN
15111: /* Update interface_available_qty for the current rti row. */
15112: x_cascaded_table(n).interface_available_qty := x_cascaded_table(n).quantity;
15113:
15120: SELECT parent_interface_txn_id,
15121: transaction_type
15122: INTO l_grandparent_interface_trx_id,
15123: l_parent_trx_type
15124: FROM rcv_transactions_interface
15125: WHERE interface_transaction_id = x_cascaded_table(n).parent_interface_txn_id;
15126: EXCEPTION
15127: WHEN NO_DATA_FOUND THEN
15128: /* For an rti of receive or an rti row with parent in RT, select
15136: asn_debug.put_line('update_interace_qty l_parent_trx_type ' || l_parent_trx_type);
15137: END IF;
15138:
15139: IF (x_cascaded_table(n).transaction_type = 'CORRECT') THEN --{
15140: UPDATE rcv_transactions_interface rti
15141: SET rti.interface_available_qty = rti.interface_available_qty + x_cascaded_table(n).quantity
15142: WHERE rti.interface_transaction_id = x_cascaded_table(n).parent_interface_txn_id;
15143:
15144: IF (l_grandparent_interface_trx_id IS NOT NULL) THEN
15141: SET rti.interface_available_qty = rti.interface_available_qty + x_cascaded_table(n).quantity
15142: WHERE rti.interface_transaction_id = x_cascaded_table(n).parent_interface_txn_id;
15143:
15144: IF (l_grandparent_interface_trx_id IS NOT NULL) THEN
15145: UPDATE rcv_transactions_interface rti
15146: SET rti.interface_available_qty = rti.interface_available_qty - x_cascaded_table(n).quantity
15147: WHERE rti.interface_transaction_id = l_grandparent_interface_trx_id;
15148: END IF;
15149: ELSE --}{
15146: SET rti.interface_available_qty = rti.interface_available_qty - x_cascaded_table(n).quantity
15147: WHERE rti.interface_transaction_id = l_grandparent_interface_trx_id;
15148: END IF;
15149: ELSE --}{
15150: UPDATE rcv_transactions_interface rti
15151: SET rti.interface_available_qty = rti.interface_available_qty - x_cascaded_table(n).quantity
15152: WHERE rti.interface_transaction_id = x_cascaded_table(n).parent_interface_txn_id;
15153:
15154: IF ( x_cascaded_table(n).transaction_type = 'RETURN TO RECEIVING'
15152: WHERE rti.interface_transaction_id = x_cascaded_table(n).parent_interface_txn_id;
15153:
15154: IF ( x_cascaded_table(n).transaction_type = 'RETURN TO RECEIVING'
15155: AND l_grandparent_interface_trx_id IS NOT NULL) THEN
15156: UPDATE rcv_transactions_interface rti
15157: SET rti.interface_available_qty = rti.interface_available_qty + x_cascaded_table(n).quantity
15158: WHERE rti.interface_transaction_id = l_grandparent_interface_trx_id;
15159: END IF;
15160: END IF; --}
15187: x_cascaded_table IN OUT NOCOPY rcv_roi_preprocessor.cascaded_trans_tab_type,
15188: n IN BINARY_INTEGER
15189: ) IS
15190: l_grandparent_interface_trx_id NUMBER;
15191: l_parent_trx_type rcv_transactions_interface.transaction_type%TYPE;
15192: l_interface_transaction_amt rcv_transactions_interface.interface_transaction_amt%TYPE := 0;
15193: BEGIN
15194: /* Update interface_available_qty for the current rti row. */
15195: x_cascaded_table(n).interface_available_amt := x_cascaded_table(n).amount;
15188: n IN BINARY_INTEGER
15189: ) IS
15190: l_grandparent_interface_trx_id NUMBER;
15191: l_parent_trx_type rcv_transactions_interface.transaction_type%TYPE;
15192: l_interface_transaction_amt rcv_transactions_interface.interface_transaction_amt%TYPE := 0;
15193: BEGIN
15194: /* Update interface_available_qty for the current rti row. */
15195: x_cascaded_table(n).interface_available_amt := x_cascaded_table(n).amount;
15196:
15203: SELECT parent_interface_txn_id,
15204: transaction_type
15205: INTO l_grandparent_interface_trx_id,
15206: l_parent_trx_type
15207: FROM rcv_transactions_interface
15208: WHERE interface_transaction_id = x_cascaded_table(n).parent_interface_txn_id;
15209: EXCEPTION
15210: WHEN NO_DATA_FOUND THEN
15211: /* For an rti of receive or an rti row with parent in RT, select
15219: asn_debug.put_line('update_interface_amt l_parent_trx_type ' || l_parent_trx_type);
15220: END IF;
15221:
15222: IF (x_cascaded_table(n).transaction_type = 'CORRECT') THEN --{
15223: UPDATE rcv_transactions_interface rti
15224: SET rti.interface_available_amt = rti.interface_available_amt + x_cascaded_table(n).amount
15225: WHERE rti.interface_transaction_id = x_cascaded_table(n).parent_interface_txn_id;
15226:
15227: IF (l_grandparent_interface_trx_id IS NOT NULL) THEN
15224: SET rti.interface_available_amt = rti.interface_available_amt + x_cascaded_table(n).amount
15225: WHERE rti.interface_transaction_id = x_cascaded_table(n).parent_interface_txn_id;
15226:
15227: IF (l_grandparent_interface_trx_id IS NOT NULL) THEN
15228: UPDATE rcv_transactions_interface rti
15229: SET rti.interface_available_amt = rti.interface_available_amt - x_cascaded_table(n).amount
15230: WHERE rti.interface_transaction_id = l_grandparent_interface_trx_id;
15231: END IF;
15232: ELSE --}{
15229: SET rti.interface_available_amt = rti.interface_available_amt - x_cascaded_table(n).amount
15230: WHERE rti.interface_transaction_id = l_grandparent_interface_trx_id;
15231: END IF;
15232: ELSE --}{
15233: UPDATE rcv_transactions_interface rti
15234: SET rti.interface_available_amt = rti.interface_available_amt - x_cascaded_table(n).amount
15235: WHERE rti.interface_transaction_id = x_cascaded_table(n).parent_interface_txn_id;
15236:
15237: IF ( x_cascaded_table(n).transaction_type = 'RETURN TO RECEIVING'
15235: WHERE rti.interface_transaction_id = x_cascaded_table(n).parent_interface_txn_id;
15236:
15237: IF ( x_cascaded_table(n).transaction_type = 'RETURN TO RECEIVING'
15238: AND l_grandparent_interface_trx_id IS NOT NULL) THEN
15239: UPDATE rcv_transactions_interface rti
15240: SET rti.interface_available_amt = rti.interface_available_amt + x_cascaded_table(n).amount
15241: WHERE rti.interface_transaction_id = l_grandparent_interface_trx_id;
15242: END IF;
15243: END IF; --}
15266: rcv_error_pkg.log_interface_error('TRANSACTIONS_INTERFACE_ID', FALSE);
15267: END update_interface_available_amt;
15268:
15269: PROCEDURE update_total_transaction_qty(
15270: p_interface_transaction_id IN rcv_transactions_interface.interface_transaction_id%TYPE,
15271: p_parent_transaction_id IN rcv_transactions_interface.parent_transaction_id%TYPE,
15272: p_parent_interface_txn_id IN rcv_transactions_interface.parent_interface_txn_id%TYPE,
15273: p_primary_quantity IN rcv_transactions_interface.quantity%TYPE,
15274: p_transaction_type IN rcv_transactions_interface.transaction_type%TYPE,
15267: END update_interface_available_amt;
15268:
15269: PROCEDURE update_total_transaction_qty(
15270: p_interface_transaction_id IN rcv_transactions_interface.interface_transaction_id%TYPE,
15271: p_parent_transaction_id IN rcv_transactions_interface.parent_transaction_id%TYPE,
15272: p_parent_interface_txn_id IN rcv_transactions_interface.parent_interface_txn_id%TYPE,
15273: p_primary_quantity IN rcv_transactions_interface.quantity%TYPE,
15274: p_transaction_type IN rcv_transactions_interface.transaction_type%TYPE,
15275: x_interface_transaction_qty OUT NOCOPY rcv_transactions_interface.interface_transaction_qty%TYPE
15268:
15269: PROCEDURE update_total_transaction_qty(
15270: p_interface_transaction_id IN rcv_transactions_interface.interface_transaction_id%TYPE,
15271: p_parent_transaction_id IN rcv_transactions_interface.parent_transaction_id%TYPE,
15272: p_parent_interface_txn_id IN rcv_transactions_interface.parent_interface_txn_id%TYPE,
15273: p_primary_quantity IN rcv_transactions_interface.quantity%TYPE,
15274: p_transaction_type IN rcv_transactions_interface.transaction_type%TYPE,
15275: x_interface_transaction_qty OUT NOCOPY rcv_transactions_interface.interface_transaction_qty%TYPE
15276: ) IS
15269: PROCEDURE update_total_transaction_qty(
15270: p_interface_transaction_id IN rcv_transactions_interface.interface_transaction_id%TYPE,
15271: p_parent_transaction_id IN rcv_transactions_interface.parent_transaction_id%TYPE,
15272: p_parent_interface_txn_id IN rcv_transactions_interface.parent_interface_txn_id%TYPE,
15273: p_primary_quantity IN rcv_transactions_interface.quantity%TYPE,
15274: p_transaction_type IN rcv_transactions_interface.transaction_type%TYPE,
15275: x_interface_transaction_qty OUT NOCOPY rcv_transactions_interface.interface_transaction_qty%TYPE
15276: ) IS
15277: l_child_count NUMBER;
15270: p_interface_transaction_id IN rcv_transactions_interface.interface_transaction_id%TYPE,
15271: p_parent_transaction_id IN rcv_transactions_interface.parent_transaction_id%TYPE,
15272: p_parent_interface_txn_id IN rcv_transactions_interface.parent_interface_txn_id%TYPE,
15273: p_primary_quantity IN rcv_transactions_interface.quantity%TYPE,
15274: p_transaction_type IN rcv_transactions_interface.transaction_type%TYPE,
15275: x_interface_transaction_qty OUT NOCOPY rcv_transactions_interface.interface_transaction_qty%TYPE
15276: ) IS
15277: l_child_count NUMBER;
15278: l_grand_parent_id NUMBER;
15271: p_parent_transaction_id IN rcv_transactions_interface.parent_transaction_id%TYPE,
15272: p_parent_interface_txn_id IN rcv_transactions_interface.parent_interface_txn_id%TYPE,
15273: p_primary_quantity IN rcv_transactions_interface.quantity%TYPE,
15274: p_transaction_type IN rcv_transactions_interface.transaction_type%TYPE,
15275: x_interface_transaction_qty OUT NOCOPY rcv_transactions_interface.interface_transaction_qty%TYPE
15276: ) IS
15277: l_child_count NUMBER;
15278: l_grand_parent_id NUMBER;
15279: BEGIN
15311: END IF;
15312:
15313: SELECT parent_transaction_id
15314: INTO l_grand_parent_id
15315: FROM rcv_transactions_interface
15316: WHERE interface_transaction_id = p_parent_interface_txn_id;
15317:
15318: IF (g_asn_debug = 'Y') THEN
15319: asn_debug.put_line('update_total_transaction_qty l_grand_parent_id ' || l_grand_parent_id);
15320: END IF;
15321:
15322: IF ( (l_grand_parent_id IS NOT NULL)
15323: AND (p_transaction_type = 'CORRECT')) THEN
15324: UPDATE rcv_transactions_interface rti
15325: SET rti.interface_transaction_qty = rti.interface_transaction_qty + p_primary_quantity
15326: WHERE interface_transaction_id = p_parent_interface_txn_id;
15327: END IF;
15328:
15327: END IF;
15328:
15329: IF ( (l_grand_parent_id IS NOT NULL)
15330: AND (p_transaction_type = 'RETURN TO RECEIVING')) THEN
15331: UPDATE rcv_transactions_interface rti
15332: SET rti.interface_transaction_qty = rti.interface_transaction_qty - p_primary_quantity
15333: WHERE interface_transaction_id = p_parent_interface_txn_id;
15334: END IF;
15335: END IF; --}
15340: RAISE;
15341: END update_total_transaction_qty;
15342:
15343: PROCEDURE update_total_transaction_amt(
15344: p_interface_transaction_id IN rcv_transactions_interface.interface_transaction_id%TYPE,
15345: p_parent_transaction_id IN rcv_transactions_interface.parent_transaction_id%TYPE,
15346: p_parent_interface_txn_id IN rcv_transactions_interface.parent_interface_txn_id%TYPE,
15347: p_amount IN rcv_transactions_interface.amount%TYPE,
15348: p_transaction_type IN rcv_transactions_interface.transaction_type%TYPE,
15341: END update_total_transaction_qty;
15342:
15343: PROCEDURE update_total_transaction_amt(
15344: p_interface_transaction_id IN rcv_transactions_interface.interface_transaction_id%TYPE,
15345: p_parent_transaction_id IN rcv_transactions_interface.parent_transaction_id%TYPE,
15346: p_parent_interface_txn_id IN rcv_transactions_interface.parent_interface_txn_id%TYPE,
15347: p_amount IN rcv_transactions_interface.amount%TYPE,
15348: p_transaction_type IN rcv_transactions_interface.transaction_type%TYPE,
15349: x_interface_transaction_amt OUT NOCOPY rcv_transactions_interface.interface_transaction_amt%TYPE
15342:
15343: PROCEDURE update_total_transaction_amt(
15344: p_interface_transaction_id IN rcv_transactions_interface.interface_transaction_id%TYPE,
15345: p_parent_transaction_id IN rcv_transactions_interface.parent_transaction_id%TYPE,
15346: p_parent_interface_txn_id IN rcv_transactions_interface.parent_interface_txn_id%TYPE,
15347: p_amount IN rcv_transactions_interface.amount%TYPE,
15348: p_transaction_type IN rcv_transactions_interface.transaction_type%TYPE,
15349: x_interface_transaction_amt OUT NOCOPY rcv_transactions_interface.interface_transaction_amt%TYPE
15350: ) IS
15343: PROCEDURE update_total_transaction_amt(
15344: p_interface_transaction_id IN rcv_transactions_interface.interface_transaction_id%TYPE,
15345: p_parent_transaction_id IN rcv_transactions_interface.parent_transaction_id%TYPE,
15346: p_parent_interface_txn_id IN rcv_transactions_interface.parent_interface_txn_id%TYPE,
15347: p_amount IN rcv_transactions_interface.amount%TYPE,
15348: p_transaction_type IN rcv_transactions_interface.transaction_type%TYPE,
15349: x_interface_transaction_amt OUT NOCOPY rcv_transactions_interface.interface_transaction_amt%TYPE
15350: ) IS
15351: l_child_count NUMBER;
15344: p_interface_transaction_id IN rcv_transactions_interface.interface_transaction_id%TYPE,
15345: p_parent_transaction_id IN rcv_transactions_interface.parent_transaction_id%TYPE,
15346: p_parent_interface_txn_id IN rcv_transactions_interface.parent_interface_txn_id%TYPE,
15347: p_amount IN rcv_transactions_interface.amount%TYPE,
15348: p_transaction_type IN rcv_transactions_interface.transaction_type%TYPE,
15349: x_interface_transaction_amt OUT NOCOPY rcv_transactions_interface.interface_transaction_amt%TYPE
15350: ) IS
15351: l_child_count NUMBER;
15352: l_grand_parent_id NUMBER;
15345: p_parent_transaction_id IN rcv_transactions_interface.parent_transaction_id%TYPE,
15346: p_parent_interface_txn_id IN rcv_transactions_interface.parent_interface_txn_id%TYPE,
15347: p_amount IN rcv_transactions_interface.amount%TYPE,
15348: p_transaction_type IN rcv_transactions_interface.transaction_type%TYPE,
15349: x_interface_transaction_amt OUT NOCOPY rcv_transactions_interface.interface_transaction_amt%TYPE
15350: ) IS
15351: l_child_count NUMBER;
15352: l_grand_parent_id NUMBER;
15353: BEGIN
15380: END IF;
15381:
15382: SELECT parent_transaction_id
15383: INTO l_grand_parent_id
15384: FROM rcv_transactions_interface
15385: WHERE interface_transaction_id = p_parent_interface_txn_id;
15386:
15387: IF (g_asn_debug = 'Y') THEN
15388: asn_debug.put_line('update_total_transaction_amt l_grand_parent_id ' || l_grand_parent_id);
15389: END IF;
15390:
15391: IF ( (l_grand_parent_id IS NOT NULL)
15392: AND (p_transaction_type = 'CORRECT')) THEN
15393: UPDATE rcv_transactions_interface rti
15394: SET rti.interface_transaction_amt = rti.interface_transaction_amt + p_amount
15395: WHERE interface_transaction_id = p_parent_interface_txn_id;
15396: END IF;
15397:
15396: END IF;
15397:
15398: IF ( (l_grand_parent_id IS NOT NULL)
15399: AND (p_transaction_type = 'RETURN TO RECEIVING')) THEN
15400: UPDATE rcv_transactions_interface rti
15401: SET rti.interface_transaction_amt = rti.interface_transaction_amt - p_amount
15402: WHERE interface_transaction_id = p_parent_interface_txn_id;
15403: END IF;
15404: END IF; --}
15678: END get_deliver_to_person_from_po;
15679:
15680: /* Function get_deliver_to_person_from_rt() is added as part of Bug#6375015 fix.
15681: This function is called from validate_vendor_rcv_line() and it tries to default
15682: the deliver_to_person_id mentioned in the parent transaction(i.e from rcv_transactions).
15683: */
15684: FUNCTION get_deliver_to_person_from_rt(
15685: x_cascaded_table IN OUT NOCOPY rcv_roi_preprocessor.cascaded_trans_tab_type,
15686: n IN BINARY_INTEGER
15688: l_parent_deliver_to_person_id NUMBER := null;
15689: BEGIN
15690: select deliver_to_person_id
15691: into l_parent_deliver_to_person_id
15692: from rcv_transactions
15693: where transaction_id = x_cascaded_table(n).parent_transaction_id;
15694:
15695: IF (g_asn_debug = 'Y') THEN
15696: asn_debug.put_line('Got deliver_to_person_id of parent txn as:'||l_parent_deliver_to_person_id);
15705: END get_deliver_to_person_from_rt;
15706:
15707: /* Function get_deliver_to_person_from_rti() is added as part of Bug#6375015 fix.
15708: This function is called from validate_vendor_rcv_line() and it tries to default
15709: the deliver_to_person_id mentioned in the parent transaction(i.e from rcv_transactions_interface).
15710: */
15711:
15712: FUNCTION get_deliver_to_person_from_rti(
15713: x_cascaded_table IN OUT NOCOPY rcv_roi_preprocessor.cascaded_trans_tab_type,
15716: l_parent_deliver_to_person_id NUMBER := null;
15717: BEGIN
15718: select deliver_to_person_id
15719: into l_parent_deliver_to_person_id
15720: from rcv_transactions_interface
15721: where interface_transaction_id = x_cascaded_table(n).parent_transaction_id;
15722:
15723: IF (g_asn_debug = 'Y') THEN
15724: asn_debug.put_line('Got deliver_to_person_id of parent txn as:'||l_parent_deliver_to_person_id);
15761: p_commit VARCHAR2(1);
15762: x_return_status VARCHAR2(1);
15763: x_msg_count NUMBER;
15764: x_msg_data VARCHAR2(2000);
15765: x_currency_code rcv_transactions.currency_code%type;
15766: x_currency_conversion_type rcv_transactions.currency_conversion_type%type;
15767: x_currency_conversion_rate NUMBER;
15768:
15769: BEGIN
15762: x_return_status VARCHAR2(1);
15763: x_msg_count NUMBER;
15764: x_msg_data VARCHAR2(2000);
15765: x_currency_code rcv_transactions.currency_code%type;
15766: x_currency_conversion_type rcv_transactions.currency_conversion_type%type;
15767: x_currency_conversion_rate NUMBER;
15768:
15769: BEGIN
15770: IF (g_asn_debug = 'Y') THEN
15852:
15853: IF (l_lsl_exists = 0) THEN
15854: SELECT count(1)
15855: INTO l_lsl_exists
15856: FROM rcv_transactions_interface
15857: WHERE lcm_shipment_line_id is not null
15858: AND lcm_shipment_line_id = x_cascaded_table(n).lcm_shipment_line_id
15859: AND processing_status_code = 'RUNNING'
15860: AND interface_transaction_id <> x_cascaded_table(n).interface_transaction_id;