Consolidation and SQL

Roelof

Hi,

One of our cllients has an issue with consolidating companies. Here are the facts:

- They have an G/L account set up like: 7300-0110 and 7300-0120.
- 2 Companies x and y are consolidated into company z.
- Version: 5.00.
- G/L AccountNos are matching for all companies.
- Database = SQL.
- Process: Financial Mgt-> General Ledger-> Periodic Activities-> Consolidation -> Business Units -> <Functions> "Import Database" (=Report 90).

When the process is finished, it inserted all entries in 7300-0110 and not (what it supposed to be) in 7300-0110 AND 7300-0120.

I have a feeling that this problem is related to the SQL sorting. SQL is sorting different then in a native database.

Anyone similar problems with this consilidation issue?

kine

In this case the sorting will be same, because both are not numbers but texts...

Roelof

Thanks for the replay.
But why would you think it's text? The G/L account No. is a code field.

kine

Code is still just text... ;-) I did't talk about Data type, but about the value, because there is '-' inside, it is not number but text and will be sorted in both system in same way.

Roelof

I disagree.

SQL is sorting different then in a native db, using 'code' fields. One of the problems I had was in 'Account Schedules'. The RowNo on the Acc. Schedule line is a 'code' field. SQL is sorting it different and I had to change the row-numbering in order to get SQL sorted correctly. And I think something similar is going on now in the Consolidiation function.

kine

Yes, it sort differently, but only if the values are numbers. If there is '-' it is not number. Yes, some "blocks" can be in different order (e.g. number first and after numbers other values or non-numbers first and numbers after etc.) but if the code have same length and some non-numeric character, it is text and will be sorted as text. But all that depends on the values, SQLDataType setting, collation etc.

AlexWiley

Yes, it is the same sorting issue that causes issues with Account Schedules. I normally tell clients to avoid any hashing of account numbers in their g/l (didn't someone tell them to use dimensions instead of sub accounts? ) And FYI, it is why you should begin your account schedules with pleanty of zeros and not use alphanumeric combinations to avoid calculation mistakes (i.e. row number 0005, 0010, etc.)

I don't know of a SQL based fix for this, but the recommendation I do have is to point them to accounts slighting different in the top level company to see if that works. So for the consolidation portion of the g/l:

Company X and Y GL Account: 7300-0110
Consolidation debit/credit GL Account: 73000110

Not sure if it will work, but it's the best I can think of right now. Good luck!