Import New Chart of Accounts Broken?

[blacklevel]

Hi there,

currently i am trying to find out which branch/fork of ADempiere to use for our new company here in Germany. From a technical perspective the iDempiere fork seems to be the most appealing. I know the first released version is still some month away. But being of the curious sort I'd like to see how usable it is in our scenario right now. Also, I'd be glad to contribute back whatever we will learn in the process. I know this is expected and should be unecessary to write. But it feels good to do so anyway.

To get my feet wet I tried to see how far I could get with the iDempiere fork with regard to the german localization. After some struggling I got it running and successfully imported the translations. Now I am stuck at the initial client setup. The first screen fails with an error concerning the chart of accounts file. This is the relevant excerpt from the server log:

Code: Select all

14:37:54.215===========> DB.executeUpdate: INSERT INTO C_AcctSchema_GL (IntercompanyDueFrom_Acct,UseCurrencyBalancing,C_AcctSchema_ID,IntercompanyDueTo_Acct,CurrencyBalancing_Acct,UseSuspenseBalancing,SuspenseBalancing_Acct,UseSuspenseError,PPVOffset_Acct,CommitmentOffset_Acct,CommitmentOffsetSales_Acct,C_AcctSchema_GL_UU,Updated,AD_Org_ID,CreatedBy,UpdatedBy,Created,IsActive,AD_Client_ID) VALUES (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?) [Setup_f296d1ed-faa7-4679-800f-7bfd7837e3b0] [14]
org.postgresql.util.PSQLException: ERROR: null value in column "retainedearning_acct" violates not-null constraint; State=23502; ErrorCode=0
        at org.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse(QueryExecutorImpl.java:2102)
        at org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:1835)
        at org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:257)
        at org.postgresql.jdbc2.AbstractJdbc2Statement.execute(AbstractJdbc2Statement.java:500)
        at org.postgresql.jdbc2.AbstractJdbc2Statement.executeWithFlags(AbstractJdbc2Statement.java:388)
        at org.postgresql.jdbc2.AbstractJdbc2Statement.executeUpdate(AbstractJdbc2Statement.java:334)
        at com.mchange.v2.c3p0.impl.NewProxyPreparedStatement.executeUpdate(NewProxyPreparedStatement.java:105)
        at sun.reflect.GeneratedMethodAccessor42.invoke(Unknown Source)
        at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
        at java.lang.reflect.Method.invoke(Method.java:616)
        at org.compiere.db.StatementProxy.invoke(StatementProxy.java:125)
        at $Proxy0.executeUpdate(Unknown Source)
        at org.compiere.util.DB.executeUpdate(DB.java:1027)
        at org.compiere.util.DB.executeUpdate(DB.java:992)
        at org.compiere.model.PO.doInsert(PO.java:2897)
        at org.compiere.model.PO.saveNew(PO.java:2701)
        at org.compiere.model.PO.save(PO.java:2070)
        at org.compiere.model.MSetup.createAccountingRecord(MSetup.java:797)
        at org.compiere.model.MSetup.createAccounting(MSetup.java:588)
        at org.adempiere.process.InitialClientSetup.doIt(InitialClientSetup.java:232)
        at org.compiere.process.SvrProcess.process(SvrProcess.java:163)
        at org.compiere.process.SvrProcess.startProcess(SvrProcess.java:116)

14:37:54.398===========> DB.saveError: DBExecuteError - ERROR: null value in column "retainedearning_acct" violates not-null constraint [14]
14:37:54.404-----------> MAcctSchemaGL.doInsert: [Setup_f296d1ed-faa7-4679-800f-7bfd7837e3b0]Not inserted - C_AcctSchema_GL [14]
14:37:54.409===========> MSetup.createAccounting: ERROR: null value in column "retainedearning_acct" violates not-null constraint [14]
14:37:54.443===========> InitialClientSetup.process: Fehler beim Einrichten der Buchführung.
Prüfen Sie das Fehlerlog und das Format der verwendeten Datei "Accounting*.csv"  - Es dürfen keine doppelten Konten enthalten sein und es müssen alle Standardkonten enthalten sein (auch wenn Sie sie nicht nutzen). [14]
org.adempiere.exceptions.AdempiereException: Fehler beim Einrichten der Buchführung.
Prüfen Sie das Fehlerlog und das Format der verwendeten Datei "Accounting*.csv"  - Es dürfen keine doppelten Konten enthalten sein und es müssen alle Standardkonten enthalten sein (auch wenn Sie sie nicht nutzen).
        at org.adempiere.process.InitialClientSetup.doIt(InitialClientSetup.java:236)
        at org.compiere.process.SvrProcess.process(SvrProcess.java:163)
        at org.compiere.process.SvrProcess.startProcess(SvrProcess.java:116)
        at org.adempiere.util.ProcessUtil.startJavaProcess(ProcessUtil.java:212)
        at org.compiere.apps.AbstractProcessCtl.startProcess(AbstractProcessCtl.java:448)
        at org.compiere.apps.AbstractProcessCtl.run(AbstractProcessCtl.java:232)
        at org.adempiere.webui.apps.WProcessCtl.process(WProcessCtl.java:156)
        at org.adempiere.webui.apps.ProcessDialog$ProcessDialogRunnable.doRun(ProcessDialog.java:774)
        at org.adempiere.util.ContextRunnable.run(ContextRunnable.java:38)
        at java.util.concurrent.Executors$RunnableAdapter.call(Executors.java:471)


I am a bit stumped here since this has worked without problems with ADempiere 3.70lts. So unless the procedure has changed the might something be wrong here. Has the CSV format of the chart of accounts fild extended by the "retainedearning_acct" or is this a deeper issue? I used this file (the latest version) for testing:

https://bitbucket.org/idempiere/idempie ... _SKR04.csv

One thing I tried was to append "[retainedearning_acct]" as an additional column to the file. But the error returned.

Thanks, Michael

[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]

Hhm. Unfortunately both files do not work. The error message remains identical to the one in the OP:

Code: Select all

DB.executeUpdate: INSERT INTO C_AcctSchema_GL (IntercompanyDueFrom_Acct,UseCurrencyBalancing,C_AcctSchema_ID,IntercompanyDueTo_Acct,CurrencyBalancing_Acct,UseSuspenseBalancing,SuspenseBalancing_Acct,UseSuspenseError,PPVOffset_Acct,CommitmentOffset_Acct,CommitmentOffsetSales_Acct,C_AcctSchema_GL_UU,Updated,AD_Org_ID,CreatedBy,UpdatedBy,Created,IsActive,AD_Client_ID) VALUES (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?) [Setup_58be4e1b-f985-4b79-ac32-c278de0b77c7] [15]
org.postgresql.util.PSQLException: ERROR: null value in column "retainedearning_acct" violates not-null constraint; State=23502; ErrorCode=0

What i find curious is that the INSERT command does not include a column named "retainedearning_acct". The DDL for the adempiere.c_acctschema_gl table reads like so:

Code: Select all

CREATE TABLE adempiere.c_acctschema_gl
(
  c_acctschema_id numeric(10,0) NOT NULL,
  ad_client_id numeric(10,0) NOT NULL,
  ad_org_id numeric(10,0) NOT NULL,
  isactive character(1) NOT NULL DEFAULT 'Y'::bpchar,
  created timestamp without time zone NOT NULL DEFAULT now(),
  createdby numeric(10,0) NOT NULL,
  updated timestamp without time zone NOT NULL DEFAULT now(),
  updatedby numeric(10,0) NOT NULL,
  usesuspensebalancing character(1) NOT NULL DEFAULT 'Y'::bpchar,
  suspensebalancing_acct numeric(10,0),
  usesuspenseerror character(1) NOT NULL DEFAULT 'Y'::bpchar,
  suspenseerror_acct numeric(10,0),
  usecurrencybalancing character(1) NOT NULL DEFAULT 'Y'::bpchar,
  currencybalancing_acct numeric(10,0),
  retainedearning_acct numeric(10,0) NOT NULL,
  incomesummary_acct numeric(10,0) NOT NULL,
  intercompanydueto_acct numeric(10,0) NOT NULL,
  intercompanyduefrom_acct numeric(10,0) NOT NULL,
  ppvoffset_acct numeric(10,0) NOT NULL,
  commitmentoffset_acct numeric(10,0) NOT NULL,
  commitmentoffsetsales_acct numeric(10,0) NOT NULL,
  c_acctschema_gl_uu character varying(36) DEFAULT NULL::character varying,
  CONSTRAINT c_acctschema_gl_pkey PRIMARY KEY (c_acctschema_id ),
  CONSTRAINT cacctschema_cacctschemagl FOREIGN KEY (c_acctschema_id)
      REFERENCES adempiere.c_acctschema (c_acctschema_id) MATCH SIMPLE
      ON UPDATE NO ACTION ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED,
  CONSTRAINT commitmentoffset_cacctschemagl FOREIGN KEY (commitmentoffset_acct)
      REFERENCES adempiere.c_validcombination (c_validcombination_id) MATCH SIMPLE
      ON UPDATE NO ACTION ON DELETE NO ACTION DEFERRABLE INITIALLY DEFERRED,
  CONSTRAINT commitmentoffsetsales_cacctsch FOREIGN KEY (commitmentoffsetsales_acct)
      REFERENCES adempiere.c_validcombination (c_validcombination_id) MATCH SIMPLE
      ON UPDATE NO ACTION ON DELETE NO ACTION DEFERRABLE INITIALLY DEFERRED,
  CONSTRAINT vc_currencybalancing_cschemagl FOREIGN KEY (currencybalancing_acct)
      REFERENCES adempiere.c_validcombination (c_validcombination_id) MATCH SIMPLE
      ON UPDATE NO ACTION ON DELETE NO ACTION DEFERRABLE INITIALLY DEFERRED,
  CONSTRAINT vc_incomesummary_cschemagl FOREIGN KEY (incomesummary_acct)
      REFERENCES adempiere.c_validcombination (c_validcombination_id) MATCH SIMPLE
      ON UPDATE NO ACTION ON DELETE NO ACTION DEFERRABLE INITIALLY DEFERRED,
  CONSTRAINT vc_intercompanyduefrom_cschema FOREIGN KEY (intercompanyduefrom_acct)
      REFERENCES adempiere.c_validcombination (c_validcombination_id) MATCH SIMPLE
      ON UPDATE NO ACTION ON DELETE NO ACTION DEFERRABLE INITIALLY DEFERRED,
  CONSTRAINT vc_intercompanydueto_cschemagl FOREIGN KEY (intercompanydueto_acct)
      REFERENCES adempiere.c_validcombination (c_validcombination_id) MATCH SIMPLE
      ON UPDATE NO ACTION ON DELETE NO ACTION DEFERRABLE INITIALLY DEFERRED,
  CONSTRAINT vc_ppvoffset_cschemagl FOREIGN KEY (ppvoffset_acct)
      REFERENCES adempiere.c_validcombination (c_validcombination_id) MATCH SIMPLE
      ON UPDATE NO ACTION ON DELETE NO ACTION DEFERRABLE INITIALLY DEFERRED,
  CONSTRAINT vc_retainedearning_cschemagl FOREIGN KEY (retainedearning_acct)
      REFERENCES adempiere.c_validcombination (c_validcombination_id) MATCH SIMPLE
      ON UPDATE NO ACTION ON DELETE NO ACTION DEFERRABLE INITIALLY DEFERRED,
  CONSTRAINT vc_suspensebalancing_cschemagl FOREIGN KEY (suspensebalancing_acct)
      REFERENCES adempiere.c_validcombination (c_validcombination_id) MATCH SIMPLE
      ON UPDATE NO ACTION ON DELETE NO ACTION DEFERRABLE INITIALLY DEFERRED,
  CONSTRAINT vc_suspenseerror_cschemagl FOREIGN KEY (suspenseerror_acct)
      REFERENCES adempiere.c_validcombination (c_validcombination_id) MATCH SIMPLE
      ON UPDATE NO ACTION ON DELETE NO ACTION DEFERRABLE INITIALLY DEFERRED,
  CONSTRAINT c_acctschema_gl_isactive_check CHECK (isactive = ANY (ARRAY['Y'::bpchar, 'N'::bpchar])),
  CONSTRAINT c_acctschema_gl_usecurrencybalancing_check CHECK (usecurrencybalancing = ANY (ARRAY['Y'::bpchar, 'N'::bpchar])),
  CONSTRAINT c_acctschema_gl_usesuspensebalancing_check CHECK (usesuspensebalancing = ANY (ARRAY['Y'::bpchar, 'N'::bpchar])),
  CONSTRAINT c_acctschema_gl_usesuspenseerror_check CHECK (usesuspenseerror = ANY (ARRAY['Y'::bpchar, 'N'::bpchar]))
)
WITH (
  OIDS=FALSE
);
ALTER TABLE adempiere.c_acctschema_gl
  OWNER TO adempiere;

-- Index: adempiere.c_acctschema_gl_uu_idx

-- DROP INDEX adempiere.c_acctschema_gl_uu_idx;
CREATE UNIQUE INDEX c_acctschema_gl_uu_idx
  ON adempiere.c_acctschema_gl
  USING btree
  (c_acctschema_gl_uu COLLATE pg_catalog."default" );


So the cause for the problem definitely is the "retainedearning_acct numeric(10,0) NOT NULL" part which makes the INSERT always fail if this column is missing.

Now, Carlos said to make sure all migrations need to be applied. Perhaps this column used to exist and should have been removed by a migration? I did run them up to 920 though. To check I did a "grep retained *" inside the "migration/360lts-release/postgresql" directory but got no match.

Did anyone successfully run the initial client setup yet?

I try to verify the following possible causes next:

* screwup migrating the database
* missing migration for the c_acctschema_gl table
* wrong database seed

Cheers
Michael

[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]

Hi Carlos,

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

Thank you!

I really like to get the AccountingDE_SKR04.csv file into a clean state. So, again, I removed all "dropped" default accounts. This time I took the default_account.properties file. The CSV file I ended up with could be imported successfully. This is great. But there are still 2 issues with it.

1) Log entries during the initial client setup process:

Code: Select all

23:41:12.539===========> DocumentTypeVerify.unlock: Did not find PInstance 0 [19]
23:41:12.990===========> MBPartner.insert_Accounting: No Columns for C_BP_Employee_Acct [19]
23:41:13.956===========> MBPartner.insert_Accounting: No Columns for C_BP_Employee_Acct [19]
23:41:14.077===========> MBPartner.insert_Accounting: No Columns for C_BP_Employee_Acct [19]

2) missing default accounts

These 6 default accounts are present in "default_accounts.properties" but are missing in "AccountingDE_SKR04.csv".

* C_PREPAYMENT_ACCT
* C_RECEIVABLE_ACCT
* C_RECEIVABLE_SERVICES_ACCT
* P_ASSET_ACCT
* P_RATEVARIANCE_ACCT
* W_DIFFERENCES_ACCT

Does this need to be fixed?

Cheers
Michael

[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]

I have not yet attempted to import the CSV.

The import via the Data Loader procedure worked without an apparent problem.

Big thanks to Dirk and Carlos.

Cheers
Michael

[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]

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

Hi Carlos,

probably I am stupider than I thought or the "Adempiere Account Editor" does not quite work on my system. Loading COA files works. Displaying the data works too. Modifying data in the "Details" tab also works for all fields except

* Account parent
* Balance Sheet

Those two fields remain read-only no matter what I tried.

What I am trying to do is to move some new accounts into the proper locations inside the hierarchical structure. I also tried drag-and-drop inside the tree or right-clicking a node. But a context menu does not appear.

I tried to run it with OpenJDK 6 as well as OpenJDK 7 on my Linux box.

Do you have an idea what might be causing this?

Thanks and best regards
Michael

[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