Openbravo Issue Tracking System - Openbravo ERP
View Issue Details
IDProjectCategoryView StatusDate SubmittedLast Update
0004681Openbravo ERP07. Sales managementpublic2008-08-22 12:042009-02-18 18:44
Reporterdalsasua 
Assigned Topheenan 
PrioritynormalSeverityminorReproducibilityalways
StatusclosedResolutionduplicate 
PlatformOS20OS Versionubuntu hardy
Product Version2.35 
Target Version2.35Fixed in Version 
Merge Request Status
Review Assigned To
OBNetwork customer
Web browser
ModulesCore
Support ticket
Regression level
Regression date
Regression introduced in release
Regression introduced by commit
Triggers an Emergency PackNo
Summary0004681: Prepaid sales orders crashes when completing in postgres
DescriptionOn r2.35 branch, rev. 6486, prepaid sales orders crashes when completing in postgres
Steps To ReproduceCreate a prepaid sales order, add a line, and press complete.

11:44:16 [ajp-8009-5] ERROR org.openbravo.erpCommon.utility.ExecuteQuery - SQL error in query: SELECT SUM(Line) AS TOTAL
FROM C_OrderLine
left join (SELECT M_Product_ID, Name FROM M_Product) td1 on C_OrderLine.M_Product_ID = td1.M_Product_ID
 
left join (SELECT C_UOM_ID, Name FROM C_UOM) td2 on C_OrderLine.C_UOM_ID = td2.C_UOM_ID
 
left join (SELECT AD_Language, C_UOM_ID, Name FROM C_UOM_Trl) td_trl3 on td2.C_UOM_ID = td_trl3.C_UOM_ID AND td_trl3.AD_Language = ?
left join (SELECT C_Tax_ID, Name FROM C_Tax) td4 on C_OrderLine.C_Tax_ID = td4.C_Tax_ID
left join (SELECT AD_Language, C_Tax_ID, Name FROM C_Tax_Trl) td_trl5 on td4.C_Tax_ID = td_trl5.C_Tax_ID AND td_trl5.AD_Language = ?
WHERE C_OrderLine.AD_Client_ID IN (0,1000000)
AND C_OrderLine.AD_Org_ID IN (0,1000000)
AND C_OrderLine.C_Order_ID = ?
AND C_OrderLine.C_OrderLine_ID IN ('1000066')
ORDER BY C_OrderLine.Line ASC, C_OrderLine.C_OrderLine_IDException:org.postgresql.util.PSQLException: ERROR: column "c_orderline.line" must appear in the GROUP BY clause or be used in an aggregate function
Proposed Solution
Additional Information
TagsPostgreSQL
Relationships
duplicate of defect 0007262 closed AinhoaPagola User not able to complete the Sales order with document type as 'Prepay' 
Attached Files
Issue History
Date ModifiedUsernameFieldChange
2008-08-22 12:04dalsasuaNew Issue
2008-08-22 12:04dalsasuaAssigned To => cromero
2008-08-22 12:04dalsasuasf_bug_id0 => 2067129
2008-08-22 12:04dalsasuaRegression testing => No
2008-09-01 12:25psarobeStatusnew => scheduled
2008-09-01 12:25psarobefix_in_branch => trunk
2008-09-03 18:25cromeroStatusscheduled => feedback
2008-09-03 18:25cromeroStatusfeedback => scheduled
2008-09-05 11:25cromeroTag Attached: PostgreSQL
2008-09-05 11:45cromeroNote Added: 0008890
2008-09-05 11:45cromeroAssigned Tocromero => marvintm
2008-11-05 13:35cromeroAssigned Tomarvintm => AinhoaPagola
2008-11-05 13:36cromeroStatusscheduled => closed
2008-11-05 13:36cromeroResolutionopen => unable to reproduce
2008-11-11 11:59rafarodaAssigned ToAinhoaPagola => gorkaion
2008-11-11 11:59rafarodaStatusclosed => new
2008-11-11 11:59rafarodaResolutionunable to reproduce => open
2008-11-11 11:59rafarodaNote Added: 0010156
2008-11-11 12:00rafarodaStatusnew => scheduled
2008-11-25 17:02rafarodaAssigned Togorkaion => Dowid
2008-12-09 14:30PavelPonomarevAssigned ToDowid => pheenan
2009-02-18 18:44AinhoaPagolaRelationship addedduplicate of 0007262
2009-02-18 18:44AinhoaPagolaStatusscheduled => closed
2009-02-18 18:44AinhoaPagolaDuplicate ID0 => 7262
2009-02-18 18:44AinhoaPagolaResolutionopen => duplicate

Notes
(0008890)
cromero   
2008-09-05 11:45   
The problem in PostgreSQL is that we are trying to catch a code exception 'DATA_EXCEPTION' when we should use 'RAISE_EXCEPTION'

''RAISE EXCEPTION presently always generates the same SQLSTATE code, P0001, no matter what message it is invoked with. It is possible to trap this exception with EXCEPTION ... WHEN RAISE_EXCEPTION THEN ... but there is no way to tell one RAISE from another.''

So, now we are using exceptions like NO_DATA_FOUND (Oracle) that it is translated to DATA_EXCEPTION (PostgreSQL), but it does not work since we are trying to catch an exception code that is never thrown.

What we should do in Postgres would be something like:
RAISE EXCEPTION '%', 'ORACLE_EXCEPTION_CODE';
EXCEPTION WHEN RAISE_EXCEPTION THEN --OBTG:ORACLE_EXCEPTION_CODE --
(0010156)
rafaroda   
2008-11-11 11:59   
This bug still reproduces in the trunk. It seems to be an odd behavior in the messaging of C_OrderPOST1 procedure [1]. See lines 175-177 (in the XML file):

IF (v_DocSubTypeSO='PR' AND v_invoicerule <> 'I') THEN
   RAISE_APPLICATION_ERROR(-20000, '@PrepayMustImmediate@');
END IF;

[1] https://dev.openbravo.com/websvn/openbravo/trunk/src-db/database/model/functions/C_ORDER_POST1.xml [^]