Import New Chart of Accounts Broken?

[blacklevel]

[blacklevel]

BTW: the same error occurs upon trying to use the standard "AccountingUS.csv" file, which is what I did during the successful initial client setup process in ADempiere 3.70lts.

[globalqss]

Hi Michael,

The procedure is the same - the CSV file has changed.

1 - You must apply all the migration scripts as suggested here -> http://www.globalqss.com/wiki/index.php ... on_scripts

2 - The CSV file must be adapted to the simpler version - we dropped all the default accounts that are not used - and now there are just 53 default accounts (in adempiere 361 there were 84 default accounts)

3 - The new AccountingUS.csv file show the sample of the default accounts -> https://bitbucket.org/idempiere/idempie ... tingUS.csv

Regards,

Carlos Ruiz

[blacklevel]

Hi Carlos,

thank you, i see the light now. I am going to change https://bitbucket.org/idempiere/idempie ... _SKR04.csv accordingly and post it here.

Is there a list of the dropped default accounts or can you give me a pointer to where I could assemble a list of the remaining valid default accounts?

Thanks again,
Michael

[a42niem]

Hi Michael,

please find attached a changed version of AccountingDE_SKR04.csv with some relations dropped

Please report its usability. Thanks.

Regards

Dirk Niemeyer

[blacklevel]

Hi Dirk,

thank you for the file. I will test it shortly. I just noticed your reply. Before I spent some time to get to the bottom of this. What I did was to extract the default accounts from AccountingUS.csv and the AccountingDE_SKR04.csv files, remove all duplicates, sort the remaining items, and diff the files to get a list of the dropped accounts. I attached the resulting files. The most interesting file in the ZIP archive is defaultAccounts_dropped.txt which contains the delta of the other two files with 31 "dropped" default accounts.

What I did was to remove each of these dropped accounts from AccountingDE_SKR04.xls and export this to AccountingDE_SKR04.csv. I just compared your "csv" to mine. There are differences:

Accounts you removed but I kept:

* P_COSTADJUSTMENT_ACCT
* P_RATEVARIANCE_ACCT

Accounts I removed but you kept:

* CH_REVENUE_ACCT
* CB_EXPENSE_ACCT

I will try to run through the initial client setup with both files and post the results here.

Cheers
Michael

[globalqss]

Hi, I'm also maintaining an updated version of the AcctEditor from Daniel Tamn here:

https://bitbucket.org/CarlosRuiz_global ... reacctedit

It's a swing program that can help you with the CSV management.

The list of required default accounts is here:
https://bitbucket.org/CarlosRuiz_global ... properties

Regards,

Carlos Ruiz

[blacklevel]

[globalqss]

Michael - I think you're right the script that must have changed that column to NULLABLE was 915_IDEMPIERE-362.sql - but seems we forgot to do it.

Will test and fix. Thanks for reporting.

Regards,

Carlos Ruiz

[globalqss]

Fixed
https://bitbucket.org/idempiere/idempie ... 92f90aa019

[blacklevel]

Hi Carlos,

my pleasure. In case you haven't run into this yourself: besides "retainedearning_acct" also "incomesummary_acct" needs to have the "NOT NULL" property removed. I just did this manually to be able to test if the CSV import is possible then.

The original error went away, fortunately. Now I continue to debug the AccountingDE_SKR04.csv. That is necessary because Dirk's file and my file still produce errors.

1) Dirk's file: MSetup.createAccounting: Account not defined: P_CostAdjustment_Acct
2) my file: MSetup.createAccounting: Account not defined: CB_Expense_Acct

It looks like we both removed a bit too much. I should have this running soon. Starting to align the default accounts in AccountingDE_SKR04.csv with default_accounts.properties...

Cheers
Michael

[blacklevel]

[a42niem]

Hi Michael,

thank you for the testing. I checked against iDempiereAcctEdit / src / default_accounts.properties and generated a new version containing all 53 listed accounts.

Regards

Dirk Niemeyer

[blacklevel]

Hi Dirk,

wow, thanks for the new version and the huge improvements! I assume you are working on producing an improved version of the AccountingDE_SKR04.csv file for the iDempiere repository. If that is true, please have a look at the attached file (if not, just ignore the rest of this post). I compared your latest CSV to my most recent version.

(Disclaimer: I am pretty clueless when actual accounting expertise and experience is required)

The file lists two things:

1) list of accounts which exist in my version but are missing in your latest (only 13 records)
2) list of accounts that only exist in your version (~250 records)

I guess you dropped the 13 account from (1) on purpose since they are not relevant for SKR04.

When browsing through the ~250 records in (2) it appears to me that there are some records which are customer specific and should be dropped (e.g. 1811, 1812 or 65xx).

Thanks again
Michael

[a42niem]

Hi Michael,

thanks for the review and the findings!

I have replaced the file with a cleaned out one concerning the customer data (please update your list as well, thanks). I also added the 99xx accounts you have listed which are not contained in SKR04 as you said but might be useful for people.
Cncerning the others mentioned: 0096 is no more existing in the 2012 version and a number of accounts like 0803, 0804 and so on have been added.

I hope we now have a workable version.

Regards

Dirk Niemeyer

[blacklevel]

Hi Dirk,

thanks again for the update. I took it and did these things:

* tried to fix as many typos as I could find
* cleaned up whitespace
* replaced in-field commas to enable a CSV structure without quoted fields (I did my best to keep a sensible structure in the wording)
* converted the encoding to UTF8
* added lines for accounts 6500, 6805 and 6810
* changed [Account_Description] for accounts 3336, 3337 and 3338 (there were some implementation specifics left)

Please have a look if that all makes sense to you. I have not yet attempted to import the CSV. Instead I drag my old self into bed now and try this tomorrow.

Best regards
Michael

[blacklevel]

[red1]

This is a good trail of a living community and i enjoyed reading it! I just edited the opening topic title to the above to be more instructive to readers looking for the same solution. Hope it is OK with you guys.

[blacklevel]

Sure, I like the new title better than the old one.

[blacklevel]

[globalqss]

Hi Michael, I haven't played with that too much - I usually use that program to check the consistency of a CSV edited externally.

But as I see you must navigate to a summary account and push the New Account button, that open a window to add a children there.

About the Balance Sheet - yes - here is also not editable - maybe a bug? Again, I've never used those extra "financial reports" fields at the end of that format.

Regards,

Carlos Ruiz