Openbravo Issue Tracking System - Openbravo ERP
View Issue Details
IDProjectCategoryView StatusDate SubmittedLast Update
0020350Openbravo ERP00. Application dictionarypublic2012-04-25 16:172012-05-25 12:12
Reportervmromanos 
Assigned Tomirurita 
PriorityhighSeveritymajorReproducibilityN/A
StatusclosedResolutionfixed 
PlatformOS20OS VersionDebian 5.0
Product Version 
Target VersionFixed in Version3.0MP12 
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
Summary0020350: Multiple API Changes in Accounting Tabs
DescriptionSeveral accounting tabs in the ERP allow the user to introduce duplicated records that can generate unexpected accounting.
After reviewing all the available accounting tabs, these are the problematic ones:

1. C_BP_GROUP_ACCT (Business Partner Category Accounting tab)
Inside this tab we can currently create several records for the same accounting schema. A new unique constraint is proposed:
+ <unique name="C_BP_GROUP_ACCT_SCHEM_GROUP_UN">
+ <unique-column name="C_ACCTSCHEMA_ID"/>
+ <unique-column name="C_BP_GROUP_ID"/>
+ </unique>


2. C_BP_CUSTOMER_ACCT (Business Partner | Customer | Customer Accounting tab)
Currently we have the following unique constraint:
       <unique name="C_BP_CUSTOMER_ACCT_BPARTNER_UN">
         <unique-column name="C_BPARTNER_ID"/>
         <unique-column name="STATUS"/>
         <unique-column name="C_ACCTSCHEMA_ID"/>
       </unique>
The status column is not used anymore with the new financial flow so we should remove it from the constraint. Probably this reason is not enough for approving the API change, however the current unique constraint has an important problem in PostgreSQL.
By default the value of this column is always NULL. In PostgreSQL, when the value of any of the columns in an unique constraint is null, it doesn't work as expected. Example: if we want to store two records in the C_BP_CUSTOMER_ACCT table with the same C_BPARTNER_ID and C_ACCTSCHEMA_ID and with STATUS=NULL, PostgreSQL will allow us to do it, which is wrong. This behaviour is not reproducible in Oracle.

Finally, apart from the previous reasons, there is another important one for instances migrated from Openbravo 2.50. For this instances we can have the same records for different STATUS values because it had sense in OB 2.50, but in a OB3 environment this configuration could create wrong accounting since the STATUS column is not taken into account anymore and the records appear as they were duplicated.


3. C_BP_VENDOR_ACCT (Business Partner | Vendor | Vendor Accounting tab)
It's exactly the same scenario described in 2.
Steps To ReproduceSee description.
Proposed SolutionAlthough these API changes can be risky for the affected customers, it should be done ASAP because with the current configuration, the system can create wrong accounting information.

To avoid problems during the update process, apart from the addition/modification of the unique constraints (and the associated messages), a build validation including alerts will be created to help the affected customers to fix their data before updating their instances.
Additional Information
TagsNo tags attached.
Relationships
related to defect 0013691 closed mirurita The same pair of Accounting schema and Status can be used several times in the Accounting tab of BP and BP Category windows 
related to defect 00208913.0MP12.1 closed mirurita It is not possible to upgrade from 2.50 MP43 to 3.0 MP12 
Attached Files
Issue History
Date ModifiedUsernameFieldChange
2012-04-25 16:17vmromanosNew Issue
2012-04-25 16:17vmromanosAssigned To => iciordia
2012-04-25 16:17vmromanosModules => Core
2012-04-25 16:18vmromanosRelationship addedrelated to 0013691
2012-04-25 20:53iciordiaNote Added: 0047983
2012-04-25 20:53iciordiaAssigned Toiciordia => vmromanos
2012-04-26 12:14vmromanosStatusnew => scheduled
2012-04-26 12:14vmromanosNote Added: 0048006
2012-04-26 12:14vmromanosfix_in_branch => pi
2012-05-02 13:29miruritaPriorityimmediate => high
2012-05-02 13:29miruritafix_in_branchpi =>
2012-05-10 12:12gorka_gilAssigned Tovmromanos => mirurita
2012-05-15 18:54hgbotCheckin
2012-05-15 18:54hgbotNote Added: 0048664
2012-05-15 18:54hgbotStatusscheduled => resolved
2012-05-15 18:54hgbotResolutionopen => fixed
2012-05-15 18:54hgbotFixed in SCM revision => http://code.openbravo.com/erp/devel/pi/rev/7bd897420af5cc6bb4fbb318f7215f67d6c15011 [^]
2012-05-16 19:02hgbotCheckin
2012-05-16 19:02hgbotNote Added: 0048714
2012-05-22 11:39miruritaNote Added: 0048851
2012-05-22 11:39miruritaStatusresolved => closed
2012-05-22 11:39miruritaFixed in Version => 3.0MP12
2012-05-25 12:12hudsonbotCheckin
2012-05-25 12:12hudsonbotNote Added: 0049139
2012-05-25 12:12hudsonbotCheckin
2012-05-25 12:12hudsonbotNote Added: 0049153
2012-06-28 16:00alostaleRelationship addedrelated to 0020891

Notes
(0047983)
iciordia   
2012-04-25 20:53   
Approved.

Ismael
(0048006)
vmromanos   
2012-04-26 12:14   
Test plan:

1. Do not apply 0013691 fix yet
2. Login as Openbravo
3. Go to Business Partner Category window. Select any record
4. Go to accounting tab
5. Insert several records for the same accounting schema
6. Go to Business Partner window
7. Select any customer
8. Go to customer tab and then to Customer accounting tab
9. Insert several records for the same accounting schema
10. Go back to Business Partner header
11. Select any vendor
12. Go to vendor tab and then to Vendor accounting tab
13. Insert several records for the same accounting schema
14. Go back to Business Partner header
15. Select another vendor and repeat steps 12 and 13
16. Apply 0013691 and 0020350 fixes
17. Run ant smartbuild -Dlocal=no
18. The process should fail showing a descriptive error
19. Go to the application and login as System Admin
20. Check that some alerts have automatically created
21. Fix some of them
22. Run again the smartbuild -Dlocal=no
23. The concrete errors appear again
24. Fix all the records
25. Run again smartbuild -Dlocal=no
26. The process should complete successfully

Note: When running smartbuild -Dlocal=no from the command line, only the System admin can login into the ERP. If you need access with other roles, just run smartbuild, without "-Dlocal=no" and refresh your tomcat server
(0048664)
hgbot   
2012-05-15 18:54   
Repository: erp/devel/pi
Changeset: 7bd897420af5cc6bb4fbb318f7215f67d6c15011
Author: Víctor Martínez Romanos <victor.martinez <at> openbravo.com>
Date: Wed May 09 09:42:55 2012 +0200
URL: http://code.openbravo.com/erp/devel/pi/rev/7bd897420af5cc6bb4fbb318f7215f67d6c15011 [^]

Fixed bug 20350: Included new AccountingTabs build validation
It validates client data and introduce alerts in case any of the
new database contraints introduced in 0013691 affect client data.

The Cbpvendoracct build validation has been removed because it has no
sense anymore since 0013691

---
A src-util/buildvalidation/build/classes/org/openbravo/buildvalidation/AccountingTabs.class
A src-util/buildvalidation/build/classes/org/openbravo/buildvalidation/AccountingTabsData.class
A src-util/buildvalidation/src/org/openbravo/buildvalidation/AccountingTabs.java
A src-util/buildvalidation/src/org/openbravo/buildvalidation/AccountingTabs_data.xsql
R src-util/buildvalidation/build/classes/org/openbravo/buildvalidation/Cbpvendoracct.class
R src-util/buildvalidation/build/classes/org/openbravo/buildvalidation/CbpvendoracctData.class
R src-util/buildvalidation/src/org/openbravo/buildvalidation/Cbpvendoracct.java
R src-util/buildvalidation/src/org/openbravo/buildvalidation/Cbpvendoracct_data.xsql
---
(0048714)
hgbot   
2012-05-16 19:02   
Repository: erp/devel/pi
Changeset: 5e580de24a962f3cf4d5070bb69814ca604ff346
Author: Mikel Irurita <mikel.irurita <at> openbravo.com>
Date: Wed May 16 18:03:53 2012 +0200
URL: http://code.openbravo.com/erp/devel/pi/rev/5e580de24a962f3cf4d5070bb69814ca604ff346 [^]

Related to issue 20350: do not use status column in ad_alert.
Status column in ad_alert table does not exist on 2.50.

---
M src-util/buildvalidation/build/classes/org/openbravo/buildvalidation/AccountingTabsData.class
M src-util/buildvalidation/src/org/openbravo/buildvalidation/AccountingTabs_data.xsql
---
(0048851)
mirurita   
2012-05-22 11:39   
code review + testing OK
(0049139)
hudsonbot   
2012-05-25 12:12   
A changeset related to this issue has been promoted main and to the
Central Repository, after passing a series of tests.

Promotion changeset: https://code.openbravo.com/erp/devel/main/rev/5401e185a8b0 [^]

Maturity status: Test
(0049153)
hudsonbot   
2012-05-25 12:12   
A changeset related to this issue has been promoted main and to the
Central Repository, after passing a series of tests.

Promotion changeset: https://code.openbravo.com/erp/devel/main/rev/5401e185a8b0 [^]

Maturity status: Test