 For some reason I always had the impression that CoA definitions
are very static once the first transactions are created in the ledger.
This still holds true. But when a customer asked me to investigate to
increase the size of a value set from 3 to 4 positions, I learned that
(with some restrictions) there is a bit more flexibility than I
initially thought.
Unfreezing the CoA definition allows you to change the displayed
name of the segment (which is just a cosmetical change) and to change
the linked value set. That possibility gives some possibilities. But
before doing anything, it's always a good idea to check the Flexfields
Guide about what is allowed in this context. I found the following
section:
Oracle Applications
prevents you from invalidating your existing flexfields data by
preventing you from decreasing the maximum size of an existing value
set. You should never attach a new value set to your segment where the
maximum size of the new value set is smaller than the maximum size of
the old value set. You will cause data corruption because your existing
segment values will be truncated.
In
general, increasing the maximum size of an existing value set (or
replacing your value set with a bigger one instead) does not cause any
problem with your existing flexfields data so long as your new maximum
size is still small enough to fit in the underlying flexfield table's
segment columns. However, you should never change to a value set with a
larger (or smaller) maximum size if your value set is Right-justify
Zero-fill, since 001 is not the same as 0000001, and all of your
existing values would become invalid. Oracle Applications products
prevent you from invalidating your existing flexfields data by
preventing you from changing the maximum size of an existing value set
at all if the value set is Right-justify Zero-fill.
Excerpt from: Changing the Maximum Size of Your Value Set, page 4-43, Oracle Applications Flexfields Guide, Release 12.1
The key issue in this exercise is that the new flexfield may not contain invalid values, so in my example a four character flexfield still need to contain the old three-character values.
As long as this is the case, the new value set may be increased to any
size you like. This leads to the conclusion that the new value set may
not have the 'Right-justify and Zero-fill numbers' option may not be
checked to preserve backward compatibility with old segment values.
Test case
So
I tried this in a test environment. First I created a new
four-character value set and added some values (both three and four
characters). After unfreezing the flexfield definition I removed the
old value set and entered the new four-character value set. After
freeze and recompilation the new value set was active.
The
first surprise came when I opened an accounting flexfield window. Yes,
both value sizes were available, but the sorting was alpha-numerical
instead of the usual numerical. So the sequence was 012,100,1000,110
instead of 012,100,110,1000. After some thinking I came to the
conclusion that it was logical: the sorting is always alpha-numerical
(the fields are stored in the database as alpha-numerical), but the
'Right-justify and Zero-fill numbers' option forced the prefixing of
the zero's, hence the numerical sorting. So sorting behaviour is
different ... and difficult to explain to the average user.
The
related issue, and it is a big one, is about ranges. All EBS
functionality using account ranges will be impacted because of the
changed sorting behaviour. This includes report parameters,
cross-validation rules, parent definitions and probably more. When
submitting an Account Analysis report, the range 000-999 will include
value 1000 ... Same with cross-validation rules, which are usually
based on logical ranges in chart of account values. Visually the ranges
are still there, but the changed sorting makes the structure almost
useless. All cross-validation rules need to be redefined when using
this options ...
Reimplementation
Changing
your chart of accounts usually means a reimplementation. In order to
reduce this risk, smart implementers include always one or two spare
segments in the CoA for future use. So far, nothing news.
If the pain in the current CoA is about the lack of space in a
particular accounting segment (all values are used, or the logical
value ranges are messed up), the option to switch to a new value set
with more positions is still an option. In that case, all old values
must be included in the new value set. But I would not recommend this
without a integrated system cleanup.
- duplicate old values with new values (with new their size)
- update ALL accounts in setup and master data with the new values from the new value set
- update ALL open transactions with the new values from the new value set
- end-date old values in new value set
- extensive testing of the new situation
As you see I would suggest to finally duplicate the old values with a
new size and finally disable them. This gives the end-user the same LOV
and sorting experience as before, because values with the same size
prevents the issues with the alpha-numerical sorting. The old values
are only needed for querying historical data, so they may be disabled.
The new value set may not have the 'Right-justify and
Zero-fill numbers' option enabled, because you cannot enter the old
values without the leading zero's. If this is not acceptable, maybe
it's worth to investigate the option to add values using the backend
(read: SQL insert query). Needless to say that this is not officially
supported.
In case of R12, the alternate account functionality can be used to for
all code combinations that (even after all the effort) arrive in GL
with the old values. But unfortunately this functionality is on code
combination level, so you have to update a lot of existing code
combinations with their new values. It is also possible to create a
mapping set in subledger accounting, for a mapping between the old and
new values for the particular value set.
I know the impact of these changes are huge and require careful
investigation and planning. But if there are no other valid business
reasons for a reimplementation, this solution can create some space.
And the effort and costs for the cleanup is probably less than the
effort and costs of a reimplementation.
Conclusion
So I have
mixed feelings with the current possibilities in EBS. Yes, it is
possible to extend the size of an accounting flexfield value set, but
the price is high. I would only recommend this option if there is an
urgent need to increase a value set and reimplementation is not an
option.
What do you think ... would you ever opt for this possibility ?
|
Comments