Openbravo Issue Tracking System - Openbravo ERP
View Issue Details
IDProjectCategoryView StatusDate SubmittedLast Update
0054761Openbravo ERPA. Platformpublic2024-02-26 12:512024-02-26 13:46
Reportervmromanos 
Assigned ToTriage Platform Base 
PrioritynormalSeveritymajorReproducibilityalways
StatusnewResolutionopen 
PlatformOS5OS Version
Product Version 
Target VersionFixed 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
Summary0054761: AD_SEQUENCE_DOC and AD_SEQUENCE_DOCTYPE don't respect AD_SEQUENCE_NAME unique constraint
DescriptionThe AD_SEQUENCE table defines the following unique constraint:

      <unique name="AD_SEQUENCE_NAME">
        <unique-column name="AD_CLIENT_ID"/>
        <unique-column name="AD_ORG_ID"/>
        <unique-column name="NAME"/>
      </unique>

Note that at the very beginning the unique constraint didn't include AD_ORG_ID, and it was added in 2010 year.

However the AD_SEQUENCE_DOC and AD_SEQUENCE_DOCTYPE procedures weren't properly adapted and might fail because they are considering it only exists one sequence with the same name for a client, but actually we might have many (one per organization).

For example, the code does:
SELECT CurrentNext - (CASE WHEN p_Update_Next='Y' THEN IncrementNo ELSE 0 END), Prefix, Suffix
  INTO v_NextNo, v_Prefix, v_Suffix
  FROM AD_Sequence
  WHERE Name=p_SequenceName AND IsActive='Y' AND IsTableID='N' AND IsAutoSequence='Y' AND AD_Client_ID=p_AD_Client_ID;

This where clause might return more than one record.
Steps To ReproduceAs F&B Admin,
Create two equal sequences, with the same name (for example 'A'), one for Región Norte and one for Región Sur organizations.

Execute in database:
select AD_SEQUENCE_DOC('A', '23C59575B9CF467C9620760EB255B389', 'Y');

Check Next Assigned Number for both sequence. The process has decided to randomly select any of them.
Proposed SolutionIMHO these old pl/sql procedures should be deprecated. The org.openbravo.erpCommon.utility.Utility getDocumentNo() methods, which call these old procedures, should be deprecated as well in favor of the new org.openbravo.erpCommon.utility.SequenceUtil class.

Note that the methods in SequenceUtil and FIN_Utility doesn't have these problem because:
a) they are implementing these methods in Java (so no calling the old pl/sql procedures)
b) they receive a concrete AD_SEQUENCE_ID, and they don't calculate it based on the sequence's name

Note that deprecating these pl/sql code might have impact in other pl/sql procedures calling them.
It might be also very difficult to deprecate Utility class' methods, as it might be difficult to get the Organization parameter.

Tackling properly this design defect will be very difficult and time consuming.


Another alternative will be to remove the AD_ORG_ID from the unique constraint. A module script should be developed to add the Org name to the sequence name for those scenarios where the new unique constraint is not valid.
Additional Information
TagsNo tags attached.
Relationships
Attached Files
Issue History
Date ModifiedUsernameFieldChange
2024-02-26 12:51vmromanosNew Issue
2024-02-26 12:51vmromanosAssigned To => Triage Platform Base
2024-02-26 12:51vmromanosModules => Core
2024-02-26 12:51vmromanosTriggers an Emergency Pack => No
2024-02-26 12:56vmromanosProposed Solution updated
2024-02-26 13:46vmromanosProposed Solution updated

There are no notes attached to this issue.