[Home] [Help]
1082: , To_number(NULL) po_distribution_id
1083: , rsl.item_description item_description
1084: , Nvl(rsh.expected_receipt_date, Sysdate) expected_receipt_date
1085: FROM rcv_supply rsup
1086: , rcv_transactions rt
1087: , rcv_shipment_headers rsh
1088: , rcv_shipment_lines rsl
1089: WHERE rsl.shipment_header_id = v_shipment_header_id
1090: AND rsl.shipment_line_id = Nvl(v_shipment_line_id,rsl.shipment_line_id)
1105: -- transfer_lpn_id should match the lpn being putaway.
1106: --AND Nvl(rt.transfer_lpn_id,-1) = Nvl(v_lpn_id,-1)
1107: -- Fix for 1865886. Commented the above and added the following for lpn
1108: AND Nvl(v_lpn_id,-1) IN (select nvl(rt2.transfer_lpn_id,-1)
1109: from rcv_transactions rt2
1110: where rt2.transaction_type <> 'DELIVER'
1111: start with rt2.transaction_id = rsup.supply_source_id
1112: connect by prior rt2.transaction_id = rt2.parent_transaction_id
1113: union all
1111: start with rt2.transaction_id = rsup.supply_source_id
1112: connect by prior rt2.transaction_id = rt2.parent_transaction_id
1113: union all
1114: select nvl(rt2.lpn_id,-1)
1115: from rcv_transactions rt2
1116: /* Changes made for bug #4926987 -- added ACCEPT and REJECT */
1117: where rt2.transaction_type not in ('RECEIVE', 'DELIVER','ACCEPT','REJECT')
1118: /* End of changes made for bug #4926987 -- added ACCEPT and REJECT */
1119: start with rt2.transaction_id = rsup.supply_source_id
1154: ,v_parent_txn_id_to_match NUMBER)
1155: IS
1156: SELECT COUNT(*)
1157: FROM rcv_supply rsup
1158: , rcv_transactions rt
1159: , rcv_shipment_headers rsh
1160: , rcv_shipment_lines rsl
1161: WHERE rsl.shipment_header_id = v_shipment_header_id
1162: AND rsl.shipment_line_id = Nvl(v_shipment_line_id,rsl.shipment_line_id)
1177: -- transfer_lpn_id should match the lpn being putaway.
1178: --AND Nvl(rt.transfer_lpn_id,-1) = Nvl(v_lpn_id,-1)
1179: -- Fix for 1865886. Commented the above and added the following for lpn
1180: AND Nvl(v_lpn_id,-1) IN (select nvl(rt2.transfer_lpn_id,-1)
1181: from rcv_transactions rt2
1182: where rt2.transaction_type <> 'DELIVER'
1183: start with rt2.transaction_id = rsup.supply_source_id
1184: connect by prior transaction_id = parent_transaction_id
1185: union all
1183: start with rt2.transaction_id = rsup.supply_source_id
1184: connect by prior transaction_id = parent_transaction_id
1185: union all
1186: select nvl(lpn_id,-1)
1187: from rcv_transactions
1188: /* Changes made for bug #4926987 -- added ACCEPT and REJECT */
1189: where transaction_type not in ('RECEIVE', 'DELIVER', 'ACCEPT', 'REJECT')
1190: /* End Changes made for bug #4926987 -- added ACCEPT and REJECT */
1191: start with transaction_id = rsup.supply_source_id
1247: , To_number(NULL)
1248: , To_char(NULL)
1249: , To_char(NULL)
1250: FROM rcv_supply rsup
1251: , rcv_transactions rt
1252: , rcv_shipment_headers rsh
1253: WHERE rsh.receipt_source_code = 'CUSTOMER'
1254: AND rsup.item_id = v_item_id
1255: AND rsup.oe_order_header_id = v_oe_order_header_id
1269: -- transfer_lpn_id should match the lpn being putaway.
1270: -- AND Nvl(rt.transfer_lpn_id,-1) = Nvl(v_lpn_id,-1)
1271: -- Fix for 1865886. Commented the above and added the following for lpn
1272: AND Nvl(v_lpn_id,-1) IN (select nvl(rt2.transfer_lpn_id,-1)
1273: from rcv_transactions rt2
1274: where rt2.transaction_type <> 'DELIVER'
1275: start with rt2.transaction_id = rsup.supply_source_id
1276: connect by prior transaction_id = parent_transaction_id
1277: union all
1275: start with rt2.transaction_id = rsup.supply_source_id
1276: connect by prior transaction_id = parent_transaction_id
1277: union all
1278: select nvl(lpn_id,-1)
1279: from rcv_transactions
1280: where transaction_type not in ('RECEIVE', 'DELIVER')
1281: start with transaction_id = rsup.supply_source_id
1282: connect by prior transaction_id = parent_transaction_id
1283: )
1316: ,v_parent_txn_id_to_match NUMBER)
1317: IS
1318: SELECT COUNT(*)
1319: FROM rcv_supply rsup
1320: , rcv_transactions rt
1321: , rcv_shipment_headers rsh
1322: WHERE rsh.receipt_source_code = 'CUSTOMER'
1323: AND rsup.item_id = v_item_id
1324: AND rsup.oe_order_header_id = v_oe_order_header_id
1338: -- transfer_lpn_id should match the lpn being putaway.
1339: --AND Nvl(rt.transfer_lpn_id,-1) = Nvl(v_lpn_id,-1)
1340: -- Fix for 1865886. Commented the above and added the following for lpn
1341: AND Nvl(v_lpn_id,-1) IN (select nvl(rt2.transfer_lpn_id,-1)
1342: from rcv_transactions rt2
1343: where rt2.transaction_type <> 'DELIVER'
1344: start with rt2.transaction_id = rsup.supply_source_id
1345: connect by prior transaction_id = parent_transaction_id
1346: union all
1344: start with rt2.transaction_id = rsup.supply_source_id
1345: connect by prior transaction_id = parent_transaction_id
1346: union all
1347: select nvl(lpn_id,-1)
1348: from rcv_transactions
1349: where transaction_type not in ('RECEIVE', 'DELIVER')
1350: start with transaction_id = rsup.supply_source_id
1351: connect by prior transaction_id = parent_transaction_id
1352: )
1399: , pod.po_distribution_id po_distribution_id
1400: , rsl.item_description item_description
1401: , to_char(null)
1402: FROM rcv_supply rsup
1403: , rcv_transactions rt
1404: , rcv_shipment_headers rsh
1405: , rcv_shipment_lines rsl
1406: , po_line_locations poll
1407: , po_distributions pod
1433: -- transfer_lpn_id should match the lpn being putaway.
1434: --AND Nvl(rt.transfer_lpn_id,-1) = Nvl(v_lpn_id,-1)
1435: -- Fix for 1865886. Commented the above and added the following for lpn
1436: AND Nvl(v_lpn_id,-1) IN (select nvl(rt2.transfer_lpn_id,-1)
1437: from rcv_transactions rt2
1438: where rt2.transaction_type <> 'DELIVER'
1439: start with rt2.transaction_id = rsup.supply_source_id
1440: connect by prior rt2.transaction_id = rt2.parent_transaction_id
1441: union all
1439: start with rt2.transaction_id = rsup.supply_source_id
1440: connect by prior rt2.transaction_id = rt2.parent_transaction_id
1441: union all
1442: select nvl(rt2.lpn_id,-1)
1443: from rcv_transactions rt2
1444: where rt2.transaction_type not in ('RECEIVE', 'DELIVER')
1445: start with rt2.transaction_id = rsup.supply_source_id
1446: connect by prior rt2.transaction_id = rt2.parent_transaction_id
1447: )
1476: ,v_lpn_id_to_match NUMBER)
1477: IS
1478: SELECT COUNT(*)
1479: FROM rcv_supply rsup
1480: , rcv_transactions rt
1481: , rcv_shipment_headers rsh
1482: , rcv_shipment_lines rsl
1483: , po_line_locations poll
1484: , po_distributions pod
1510: -- transfer_lpn_id should match the lpn being putaway.
1511: --AND Nvl(rt.transfer_lpn_id,-1) = Nvl(v_lpn_id,-1)
1512: -- Fix for 1865886. Commented the above and added the following for lpn
1513: AND Nvl(v_lpn_id,-1) IN (select nvl(rt2.transfer_lpn_id,-1)
1514: from rcv_transactions rt2
1515: where rt2.transaction_type <> 'DELIVER'
1516: start with rt2.transaction_id = rsup.supply_source_id
1517: connect by prior rt2.transaction_id = rt2.parent_transaction_id
1518: union all
1516: start with rt2.transaction_id = rsup.supply_source_id
1517: connect by prior rt2.transaction_id = rt2.parent_transaction_id
1518: union all
1519: select nvl(rt2.lpn_id,-1)
1520: from rcv_transactions rt2
1521: where rt2.transaction_type not in ('RECEIVE', 'DELIVER')
1522: start with rt2.transaction_id = rsup.supply_source_id
1523: connect by prior rt2.transaction_id = rt2.parent_transaction_id
1524: )
1569: , pod.po_distribution_id po_distribution_id
1570: , rsl.item_description item_description
1571: , to_char(null)
1572: FROM rcv_supply rsup
1573: , rcv_transactions rt
1574: , rcv_shipment_headers rsh
1575: , rcv_shipment_lines rsl
1576: , po_line_locations poll
1577: , po_distributions pod
1603: -- transfer_lpn_id should match the lpn being putaway.
1604: --AND Nvl(rt.transfer_lpn_id,-1) = Nvl(v_lpn_id,-1)
1605: -- Fix for 1865886. Commented the above and added the following for lpn
1606: AND Nvl(v_lpn_id,-1) IN (select nvl(rt2.transfer_lpn_id,-1)
1607: from rcv_transactions rt2
1608: where rt2.transaction_type <> 'DELIVER'
1609: start with rt2.transaction_id = rsup.supply_source_id
1610: connect by prior rt2.transaction_id = rt2.parent_transaction_id
1611: union all
1609: start with rt2.transaction_id = rsup.supply_source_id
1610: connect by prior rt2.transaction_id = rt2.parent_transaction_id
1611: union all
1612: select nvl(rt2.lpn_id,-1)
1613: from rcv_transactions rt2
1614: where rt2.transaction_type not in ('RECEIVE', 'DELIVER')
1615: start with rt2.transaction_id = rsup.supply_source_id
1616: connect by prior rt2.transaction_id = rt2.parent_transaction_id
1617: )
1646: ,v_lpn_id_to_match NUMBER)
1647: IS
1648: SELECT COUNT(*)
1649: FROM rcv_supply rsup
1650: , rcv_transactions rt
1651: , rcv_shipment_headers rsh
1652: , rcv_shipment_lines rsl
1653: , po_line_locations poll
1654: , po_distributions pod
1680: -- transfer_lpn_id should match the lpn being putaway.
1681: --AND Nvl(rt.transfer_lpn_id,-1) = Nvl(v_lpn_id,-1)
1682: -- Fix for 1865886. Commented the above and added the following for lpn
1683: AND Nvl(v_lpn_id,-1) IN (select nvl(rt2.transfer_lpn_id,-1)
1684: from rcv_transactions rt2
1685: where rt2.transaction_type <> 'DELIVER'
1686: start with rt2.transaction_id = rsup.supply_source_id
1687: connect by prior rt2.transaction_id = rt2.parent_transaction_id
1688: union all
1686: start with rt2.transaction_id = rsup.supply_source_id
1687: connect by prior rt2.transaction_id = rt2.parent_transaction_id
1688: union all
1689: select nvl(rt2.lpn_id,-1)
1690: from rcv_transactions rt2
1691: where rt2.transaction_type not in ('RECEIVE', 'DELIVER')
1692: start with rt2.transaction_id = rsup.supply_source_id
1693: connect by prior rt2.transaction_id = rt2.parent_transaction_id
1694: )
1719: x_bkp_qty NUMBER := 0;
1720: x_progress VARCHAR2(3);
1721: x_converted_trx_qty NUMBER := 0;
1722: transaction_ok BOOLEAN := FALSE;
1723: x_expected_date rcv_transactions_interface.expected_receipt_date%TYPE;
1724: high_range_date DATE;
1725: low_range_date DATE;
1726: rows_fetched NUMBER := 0;
1727: x_tolerable_qty NUMBER := 0;
1810: END IF;
1811:
1812: x_return_status := fnd_api.g_ret_sts_success;
1813:
1814: SAVEPOINT rcv_transactions_gen_sa;
1815:
1816: -- storing the passed value to match with the parent_txn_id in a local
1817: -- var and nulling out the variable in the record to simulate the
1818: -- behavior as was prior to patchset J changes.
2484: /* Bug 4004656 -Commented the following statement
2485: IF (lastrecord OR x_remaining_quantity <= 0) THEN */
2486: /* Bug 4747997: We have to compare the rounded off values by 5 decimal places,
2487: as the value hold by this variable is non-rounded value returned
2488: from the API rcv_transactions_interface_sv.convert_into_correct_qty() */
2489: IF (lastrecord OR round(l_rem_qty_trans_uom,5) <=0) THEN --Bug 4004656, Bug:4747997
2490: IF (l_debug = 1) THEN
2491: print_debug('No more rows or the remaining qty is less than zero',4);
2492: END IF;
2622: is getting calculated in RMA uom instead of transaction uom.
2623: So, We need to convert the tolerance qty for RMA to transaction uom. */
2624: l_rcv_tolerance_qty_rma_uom := x_oe_line_qty * (x_qty_rcv_tolerance/100);
2625:
2626: l_rcv_tolerance_qty_txn_uom := rcv_transactions_interface_sv.convert_into_correct_qty(
2627: l_rcv_tolerance_qty_rma_uom,
2628: x_MatchedRec.unit_of_measure,
2629: temp_cascaded_table(1).item_id,
2630: temp_cascaded_table(1).unit_of_measure);
3080: Retained it in the transaction uom through the variable l_rcv_qty_trans_uom
3081: by assigning the value of the remaining quantity l_rem_qty_trans_uom
3082: which is already in the transaciton uom */
3083: /* x_remaining_qty_po_uom :=
3084: rcv_transactions_interface_sv.convert_into_correct_qty
3085: (x_remaining_quantity,
3086: temp_cascaded_table(1).unit_of_measure,
3087: temp_cascaded_table(1).item_id,
3088: x_MatchedRec.unit_of_measure); */
3112: x_converted_trx_qty;
3113:
3114: -- change asn uom qty so both qtys are in sync
3115: x_remaining_quantity :=
3116: rcv_transactions_interface_sv.convert_into_correct_qty
3117: (x_remaining_qty_po_uom,
3118: x_MatchedRec.unit_of_measure,
3119: temp_cascaded_table(1).item_id,
3120: temp_cascaded_table(1).unit_of_measure);
3164: IF round(l_rcv_qty_trans_uom,5) <> 0 THEN --Bug 4747997
3165: if l_trx_qty_po_uom > 0 then
3166: --Added the following code
3167: l_trx_qty_trans_uom:=
3168: rcv_transactions_interface_sv.convert_into_correct_qty
3169: (l_trx_qty_po_uom,
3170: x_MatchedRec.unit_of_measure,
3171: temp_cascaded_table(1).item_id,
3172: temp_cascaded_table(1).unit_of_measure);
3177:
3178: IF (round(l_trx_qty_trans_uom,5) < round(l_rcv_qty_trans_uom,5)) THEN --Bug: 4747997
3179: -- compare like uoms which is the transaction uom
3180: l_rcv_qty_trans_uom := l_rcv_qty_trans_uom - l_trx_qty_trans_uom;
3181: l_rcv_qty_po_uom:= rcv_transactions_interface_sv.convert_into_correct_qty(l_rcv_qty_trans_uom,
3182: temp_cascaded_table(1).unit_of_measure,
3183: temp_cascaded_table(1).item_id,
3184: x_MatchedRec.unit_of_measure);
3185:
3240: END IF;
3241:
3242: temp_cascaded_table(current_n).source_doc_quantity :=
3243: -- x_converted_trx_qty; -- in po uom --Bug 4004656
3244: rcv_transactions_interface_sv.convert_into_correct_qty(
3245: l_trx_qty_trans_uom ,
3246: temp_cascaded_table(current_n).unit_of_measure,
3247: temp_cascaded_table(current_n).item_id,
3248: x_MatchedRec.unit_of_measure);
3257: END IF;
3258:
3259: temp_cascaded_table(current_n).quantity :=
3260: /* Bug 4004656
3261: rcv_transactions_interface_sv.convert_into_correct_qty
3262: (x_converted_trx_qty,
3263: x_MatchedRec.unit_of_measure,
3264: temp_cascaded_table(current_n).item_id,
3265: temp_cascaded_table(current_n).unit_of_measure); -- in asn uom */
3271:
3272: -- Primary qty in Primary UOM
3273: temp_cascaded_table(current_n).primary_quantity :=
3274: /* Bug 4004656
3275: rcv_transactions_interface_sv.convert_into_correct_qty
3276: (x_converted_trx_qty,
3277: x_MatchedRec.unit_of_measure,
3278: temp_cascaded_table(current_n).item_id,
3279: temp_cascaded_table(current_n).primary_unit_of_measure); */
3277: x_MatchedRec.unit_of_measure,
3278: temp_cascaded_table(current_n).item_id,
3279: temp_cascaded_table(current_n).primary_unit_of_measure); */
3280:
3281: rcv_transactions_interface_sv.convert_into_correct_qty(
3282: l_trx_qty_trans_uom,
3283: temp_cascaded_table(current_n).unit_of_measure,
3284: temp_cascaded_table(current_n).item_id,
3285: temp_cascaded_table(current_n).primary_unit_of_measure);
3420:
3421:
3422: EXCEPTION
3423: WHEN fnd_api.g_exc_error THEN
3424: ROLLBACK TO rcv_transactions_gen_sa;
3425: x_return_status := fnd_api.g_ret_sts_error;
3426:
3427: -- Get message count and data
3428: fnd_msg_pub.count_and_get
3504: CLOSE count_int_req_delivery_lines;
3505: END IF;
3506:
3507: WHEN fnd_api.g_exc_unexpected_error THEN
3508: ROLLBACK TO rcv_transactions_gen_sa;
3509: x_return_status := fnd_api.g_ret_sts_unexp_error ;
3510:
3511: -- Get message count and data
3512: fnd_msg_pub.count_and_get
3576: CLOSE rma_delivery_lines;
3577: END IF;
3578:
3579: WHEN OTHERS THEN
3580: ROLLBACK TO rcv_transactions_gen_sa;
3581: x_return_status := fnd_api.g_ret_sts_unexp_error ;
3582:
3583: IF SQLCODE IS NOT NULL THEN
3584: inv_mobile_helper_functions.sql_error('inv_rcv_txn_match.matching_logic', l_progress, SQLCODE);