details: https://code.openbravo.com/erp/devel/pi/rev/19e19b829b48
changeset: 24222:19e19b829b48
user: Víctor Martínez Romanos <victor.martinez <at> openbravo.com>
date: Thu Aug 07 18:35:47 2014 +0200
summary: Issue 27140: fixed execution in Oracle
The previous SQL queries raised a ORA-01403: no data found exception when no
records were found.
They have been rewritten to use count(*) instead, which either returns 0 if no
records are found or 1 when it finds any record.
diffstat:
src-db/database/model/functions/C_ORDER_POST1.xml | 20 ++++++++++----------
1 files changed, 10 insertions(+), 10 deletions(-)
diffs (59 lines):
diff -r 09161e698613 -r 19e19b829b48
src-db/database/model/functions/C_ORDER_POST1.xml
--- a/src-db/database/model/functions/C_ORDER_POST1.xml Thu Aug 07 15:19:21
2014 +0200
+++ b/src-db/database/model/functions/C_ORDER_POST1.xml Thu Aug 07 18:35:47
2014 +0200
@@ -61,9 +61,9 @@
v_IsBinding CHAR(1):='Y';
v_istaxincluded CHAR(1);
--
- ToDeliver CHAR(1):='N';
- ToInvoice CHAR(1):='N';
- ToDeliverOrToInvoice CHAR(1):='N';
+ ToDeliver NUMBER:=0;
+ ToInvoice NUMBER:=0;
+ ToDeliverOrToInvoice NUMBER:=0;
--
InOut_ID VARCHAR2(32);
Invoice_ID VARCHAR2(32);
@@ -1360,11 +1360,11 @@
*/
BEGIN
v_ResultStr:='OrderCompleteCheck';
- SELECT 'Y' INTO ToDeliverOrToInvoice FROM DUAL
+ SELECT COUNT(*) INTO ToDeliverOrToInvoice FROM DUAL
WHERE 0 <> ANY (select QtyOrdered - QtyDelivered from c_orderline
where c_order_id = v_Record_ID)
OR 0 <> ANY (select QtyOrdered - QtyInvoiced from c_orderline
where c_order_id = v_Record_ID);
- -- If something to deliver or to invoice, then ToDeliverOrToInvoice =
'Y'
- IF (ToDeliverOrToInvoice <> 'Y') THEN
+ -- If something to deliver or to invoice, then ToDeliverOrToInvoice = 1
+ IF (ToDeliverOrToInvoice = 0) THEN
DBMS_OUTPUT.PUT_LINE('OrderComplete') ;
IF (v_DocAction='CL') THEN
END_PROCESSING:=TRUE;
@@ -1507,15 +1507,15 @@
/**
* Final Completeness check
*/
- SELECT 'Y' INTO ToDeliver FROM DUAL
+ SELECT COUNT(*) INTO ToDeliver FROM DUAL
WHERE 0 <> ANY (select QtyOrdered - QtyDelivered from c_orderline where
c_order_id = v_Record_ID);
- SELECT 'Y' INTO ToInvoice FROM DUAL
+ SELECT COUNT(*) INTO ToInvoice FROM DUAL
WHERE 0 <> ANY (select QtyOrdered - QtyInvoiced from c_orderline where
c_order_id = v_Record_ID);
DBMS_OUTPUT.PUT_LINE('To deliver - ' || ToDeliver) ;
DBMS_OUTPUT.PUT_LINE('ToInvoice - ' || ToInvoice) ;
DBMS_OUTPUT.PUT_LINE('v_DocSubTypeSO - ' || v_DocSubTypeSO) ;
-- Nothing to Deliver + Invoice for (W)illCall(I)nvoice and
(W)alkIn(R)eceipt
- IF (v_DocSubTypeSO IN ('WI', 'WR') AND ToDeliver <> 'Y' AND ToInvoice <>
'Y') THEN
+ IF (v_DocSubTypeSO IN ('WI', 'WR') AND ToDeliver=0 AND ToInvoice=0) THEN
UPDATE C_ORDER
SET DocStatus='CO',
DocAction='--',
@@ -1531,7 +1531,7 @@
END IF;
END IF;
-- Nothing to Deliver for (W)illCall(P)ickup (Invoice generated
independently)
- IF (v_DocSubTypeSO='WP' AND ToDeliver <> 'Y') THEN
+ IF (v_DocSubTypeSO='WP' AND ToDeliver=0) THEN
UPDATE C_ORDER
SET DocStatus='CO',
DocAction='--',
------------------------------------------------------------------------------
Infragistics Professional
Build stunning WinForms apps today!
Reboot your WinForms applications with our WinForms controls.
Build a bridge from your legacy apps to the future.
http://pubads.g.doubleclick.net/gampad/clk?id=153845071&iu=/4140/ostg.clktrk
_______________________________________________
Openbravo-commits mailing list
[email protected]
https://lists.sourceforge.net/lists/listinfo/openbravo-commits
