DTW - Unbalanced Transaction

[Former Member]

2007A SP01 PL 08

Currently trying to load ojournal entries through DTW - but the error 'Unbalanced transaction application - defined or object0defined error 65171' is occuring.

SAP Note 1306610, titled DTW - Unbalanced Transactions has been followed, and totals balance - BUT error is still occuring.

Can you please assist.

SAP Note 1306610 DTW - Unbalanced Transactions is outlined below

Symptom

When importing a Journal Entry via DTW, you receive an error "Unbalanced Transaction". However, when you check the sums of the debit and credit columns in Excel, they are equal so the transaction seems like it should be balanced.

Other terms

DTW, SAP Business One, Journal Entry, journalentry, journalentries, excel, autosum, unbalanced transcation, transaction, decimal, rounding

Reason and Prerequisites

Consulting

Solution

Excel will round the value of an autosum orSUM(A,B)formula for displaying to the user. Therefore the Debit and Credit amounts may be different but displayed as being equal.

It is the underlying value of the cell that will be used in the DTW import and not the value shown in the Excel GUI.

e.g. You see the sum of both debit and credit as 1362758.2

However, if you add the debit and credit columns manually, you may find the actual values to be 1362758.15 and 1362758.17. This will be correctly identified by DTW as an unbalanced entry.

The solution is to set Excel to display the required number of decimal places so you can ensure the two columns are balanced:

In Excel 2007:

- Select all cells

- Go to "Home" on the ribbon

- Click the shortcut arrow beside "Number"

- Then in the "Format Cells" Window select "Number"

- Set the desired number of decimal places.

[former_member206437 · Accepted solution]

HI

Post ur fileds here, also just check by giving only two lines.

Giri

[Former Member · Accepted solution]

RecordKey LineNum AccountCode AdditionalReference BaseSum ContraAccount CostingCode Credit Debit DueDate FCCredit FCCurrency FCDebit GrossValue LineMemo ProjectCode Reference1 Reference2 ReferenceDate1 ReferenceDate2 ShortName SystemBaseAmount SystemVatAmount TaxDate TaxGroup VatAmount VatDate VatLine

1 1 _SYS00000000236 5151.10 WAXMET 5151.10 0.00 20091224 OB JE TF 2009 OB JE TF 2009 20091224 20091224 _SYS00000000236 20091224 20091224 tNO

1 2 _SYS00000000285 180358.20 WAXMET 180358.20 0.00 20091224 OB JE TF 2009 OB JE TF 2009 20091224 20091224 _SYS00000000285 20091224 20091224 tNO

[Former Member · Accepted solution]

Thanga Raj.K,

Error still occurs when populating the SystemBaseAmount field.

Also, confirming that the 'BaseSum' field is always positve?

Cheers Lisa

[former_member206437 · Accepted solution]

Hi

Just Try with This fields only then try with other fields one by one:

JE Document header:

RecordKey ReferenceDate Reference

RecordKey ReferenceDate Reference

1 20090215 Opening G/L Credit

JE Line:

RecordKey LineNum AccountCode Credit Debit

RecordKey LineNum AccountCode Credit Debit

1 0 133213 182781.03

1 1 133215 1719998.77

1 3 900004 1902779.80

Giri

[Former Member · Accepted solution]

Hi Giri dharan,

I have followed your instructions with error still occuring.

I then tried your recommendations, including the JdtNum field (left blank) but same error.

Cheers Lisa

[former_member206437 · Accepted solution]

Hi

THis works perfectly for me , check whether the account no which u have given is there in COA r not..Also check whether any account which u have given is Control account, if itso then it willthrough the error.

Giri

[Former Member · Accepted solution]

Hi Giri,

Are you on the same version? 2007A Sp01 PL08?

check whether the account no which u have given is there in COA r not - all accounts exist as S_ numbers matched

Also check whether any account which u have given is Control account - no accounts in the list are control accounts (a different error occured earlier and has been rectified)

Cheers Lisa

[former_member206437 · Accepted solution]

Hi

Sorry for the Late reply, so busyy...

I was using 2007B...it wiorks perfectly for me.... as i told do one by one step...then u will get where u r doing mistake...

i will get back to u , if i get soln for u

Giri

[former_member583013 · Accepted solution]

Lisa,

I see that you are using the Posting date from 2009. Please check if this period is marked as closing period.

I also notice that the BaseSum field is the same as the Credit amount but it should be the sum of Credits.

5151.10

180358.20

185509.30

The amount in the Credit col is not balanced by a equal debit so please add another line to match this sum and enter the sum in the debit line.

Suda

[Former Member · Accepted solution]

Hi Gordon

Confirming that a test environment is being used

The problem is occuring for all records (there is not discrimination in the error from DTW, and does not refer to a particular line)

A manual creation of a JE does work.

Hi Suda,

Confirming that the Posting period is 'unlocked'.

Yes - we have a winner!

The basesum is the sum of credits and debits - this works.

Thanks to all those who has assisted.

Cheers Lisa

[Former Member · Accepted solution]

Hi All,

FYI - this was not actually the solution - The basesum has been removed and did not affect the import.

The error was with 1 account - a bank account set up in a different currency to the system and local currencies.

The upload now works.

Hope this helps others in the future.

Cheers Lisa

[Former Member · Accepted solution]

Dear Suda,

I have the same problem but I don't understand what I have to write in the BaseSum col

Thks

[kothandaraman_nagarajan · Accepted solution]

Hi,

Please post as new discussion and link this thread. This is standard forum rule.

Thanks & Regards,

Nagarajan

[BattleshipCobra]

I had the same issue, in my case it was a currency issue giving me the error.  I was importing opening balances and one of the customers was set to a foreign currency.  I would imagine it would give off the same issue if an account was in a foreign currency so this is worth checking if you run into the "Unbalanced Transaction" error.

[Former Member]

Hi Lisa,

Please make sure you are doing everything in the test environment first. Is the problem only occurred for certain records or all? If it is for all, have you tried manually enter at least one of the OB already?

Thanks,

Gordon

[Former Member]

Hi!

Do you having Local and System Currency as Different ?

[Former Member]

Thanga Raj.K,

The SAP settings are Local Currency = System currency.

The System base amount field in the DTW template is blank.

Cheers Lisa