0054761: AD_SEQUENCE_DOC and AD_SEQUENCE_DOCTYPE don't respect AD_SEQUENCE_NAME unique constraint

Description

The 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 Reproduce

As 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 Solution

IMHO 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.

Issue History
Date Modified	Username	Field	Change
2024-02-26 12:51	vmromanos	New Issue
2024-02-26 12:51	vmromanos	Assigned To	=> Triage Platform Base
2024-02-26 12:51	vmromanos	Modules	=> Core
2024-02-26 12:51	vmromanos	Triggers an Emergency Pack	=> No
2024-02-26 12:56	vmromanos	Proposed Solution updated
2024-02-26 13:46	vmromanos	Proposed Solution updated

Relationships [ Relation Graph ] [ Dependency Graph ]

Notes
There are no notes attached to this issue.