details: https://code.openbravo.com/erp/devel/pi/rev/35ce07c42013
changeset: 35544:35ce07c42013
user: David Miguelez <david.miguelez <at> openbravo.com>
date: Wed Mar 20 10:25:27 2019 +0100
summary: Related to Issue 40343: Fixes in PL for Oracle.
When retrieving a value with a select into clause, it is necessary
to capture the exception for when there is no data.
diffstat:
src-db/database/model/functions/M_RESERVATION_CONSUMPTION.xml | 27 +++++++---
1 files changed, 18 insertions(+), 9 deletions(-)
diffs (47 lines):
diff -r 5be0e47e84c4 -r 35ce07c42013
src-db/database/model/functions/M_RESERVATION_CONSUMPTION.xml
--- a/src-db/database/model/functions/M_RESERVATION_CONSUMPTION.xml Wed Mar
20 13:38:11 2019 +0100
+++ b/src-db/database/model/functions/M_RESERVATION_CONSUMPTION.xml Wed Mar
20 10:25:27 2019 +0100
@@ -111,25 +111,34 @@
p_message := '@MoreQuantityToReleaseThanPending@'||' @of@'||' @Product@:
'||v_product_value||', @AttributeSetInstance@: '||COALESCE(v_attr_description,
'-')|| ', @UOM@: '||v_uom_value||' @And@'||' @StorageBin@:
'||COALESCE(v_locator_value, '-');
END IF;
- SELECT COALESCE(SUM(rs.quantity - COALESCE(rs.releasedqty,0)),0)
- INTO v_this_sd_reserved
- FROM m_reservation_stock rs
- WHERE rs.m_locator_id = p_locator_id
- AND rs.m_reservation_id = p_reservation_id
- AND COALESCE(rs.m_attributesetinstance_id, '0') =
COALESCE(p_attributesetinstance_id, '0');
+ BEGIN
+ SELECT COALESCE(SUM(rs.quantity - COALESCE(rs.releasedqty,0)),0)
+ INTO v_this_sd_reserved
+ FROM m_reservation_stock rs
+ WHERE rs.m_locator_id = p_locator_id
+ AND rs.m_reservation_id = p_reservation_id
+ AND COALESCE(rs.m_attributesetinstance_id, '0') =
COALESCE(p_attributesetinstance_id, '0');
+ EXCEPTION WHEN NO_DATA_FOUND THEN
+ v_this_sd_reserved := 0;
+ END;
- SELECT 1
- INTO v_res_stock_exists
+ BEGIN
+ SELECT 1
+ INTO v_res_stock_exists
FROM dual
WHERE EXISTS (SELECT 1 FROM m_reservation_stock rs WHERE rs.m_locator_id =
p_locator_id
AND rs.m_reservation_id = p_reservation_id
AND COALESCE(rs.m_attributesetinstance_id, '0') =
COALESCE(p_attributesetinstance_id, '0'));
-
+ EXCEPTION WHEN NO_DATA_FOUND THEN
+ v_res_stock_exists:= NULL;
+ END;
+
IF (v_pendingtorelease > v_this_sd_reserved AND v_res_stock_exists IS NOT
NULL) THEN
-- The reservation does not have enough stock reserved on given locator
and attributes, reallocation is needed.
M_RESERVATION_REALLOCATE(p_reservation_id, p_locator_id,
p_attributesetinstance_id, p_qty, p_user_id, p_result, p_message);
END IF;
+
-- Release stock reserved in the reservation.
v_ResultStr := 'Release reserved stock';
FOR cur_reserved_stock IN (
_______________________________________________
Openbravo-commits mailing list
Openbravo-commits@lists.sourceforge.net
https://lists.sourceforge.net/lists/listinfo/openbravo-commits
