I imported a SWIFT file from my bank. Then the cumbersome work starts in matching weborders with the customers. The software recognizes bank accounts linked to customers which I added manually. However, once this match is done, I cannot post the journal due to next error:
Voucher BUC000099, date 14-8-2009, account 1305, amount currency -65,45, amount MST -65,45, alternative amount 0,00, currency EUR, text CustomerName CompanyName: WebOrderNumber
factuur/order: W2
The transactions on voucher BUC000099 do not balance as per 14-8-2009. (Company currency: -65,45 - secondary currency: 0,00)
It seems that ‘offset’ is empty and for the other lines (not giving) problems filled in with the bank name.
My work around was to duplicate the line and then remove the voucher that throws the error. In this way I can post the vouchers to the ledger. However, I find it weird above situation happens. Settling also does not help.
So the voucher creation is incorrect. The initial entry is the customer, this is who is paying you and therefore the debtors control is hit through the posting group. The offset is the bank account where the money goes. Do you understand what the basic cash receipt process is prior to trying to map the data into one?
“So the voucher creation is incorrect. The initial entry is the customer, this is who is paying you and therefore the debtors control is hit through the posting group. The offset is the bank account where the money goes. Do you understand what the basic cash receipt process is prior to trying to map the data into one?”
You mean the offset it where the money is ‘registered’ on (I don’t know the English word). So if AX finds a customer based on bank no, it does not know where to ‘registers’ the money on so leaves it empty? That means a setting missing or we abuse some functionality here?
In this particular case we use the ‘bank’ as a virtual representative of the money which arrives weekly from the third party payment provider. The offset accounts should be BANK to BANK so that this bank has a balance of zero all the time. The money from the customers is paid weekly to my reel existing bank account. I do the next ledger booking:
Ledger 1326 ‘To be received on bank account’ Debit: sum of all debitors
To Customer CUSTNO Credit: invoice amount of debitor received from payment gateway (third party)
To Customer CUSTNO Credit: invoice amount of debitor received from payment gateway (third party)
Upon receipt of the money from the third party I register in the journal (general) by reading the MT940 bank transactions:
Ledger Bank:
Ledger 1326 ‘To be received on bank account’ Credit: sum of all debitors
Balance
The balance on ledger 1326 should be 0 if all money is received from the third-party. Or is it more straightforward to leave the ledger 1326 out and have the balance ‘increase’ since the ledger 1326 is not used anymore? Then in receiving the money on the bank a direct BANK-to-BANK booking should be possible to ‘decrease’ the balance of the ‘virtual bank’ towards 0. In this case the idea of ‘bank is more clear maybe’. I don’t know, I am not an accountant.
So how to continue?
So the offset is not filled in by AX. With is most likely a setting missing. However, the balance of the customers is 0 after posting the ledger.
Maybe option is that ‘automatic settlement’ to the 1326 ledger can occur on invoice creation? However, the reserve invoiced (credit notes) are not done by the ‘third party’. So a credit should be settled somewhere else. Also for ‘COD’ payments. I do not know this can be controlled in AX.
But lets stick to the problem here first. What to do?
I was answering the journal issue, the journal does not balance, simple as.
You may want to look at the bridging account configuration with methods of payments in AX, this allows a two step process to the payment between banks, but usually this is vendor related, you would need to investigate if this standard solution fits what you want.
As I said previously I thought you were the developer - are you not working to a specification? Has a consultant not mapped out the requirements of what it should do and therefore taken teh accounting consideration and the needs of the business together - or are you doing this in house on the fly?
The offset would come from the bank, but it depends upon how you are importing the file and what it contains. Yes you do not have the bank, but the file does so the import could create it, but then in reality the offset is your bank account where the money goes, this of course will be on the import file,
Sounds to me like you have a whole mass of considerations and processes that are as yet undefined and unresolved, you will struggle to get definitive answers for that on the forum, because it is so broad and all encompassing. I suggest you define the needs and requirements and then map these to the available data and what it needs to do in AX.
“As I said previously I thought you were the developer - are you not working to a specification? Has a consultant not mapped out the requirements of what it should do and therefore taken teh accounting consideration and the needs of the business together - or are you doing this in house on the fly?”
==> My implementation partner wants to write invoices for every hour of consultancy. I have no budget for it. In my RFI my business processes where clearly defined. However, the complete RFI has not been read at all. Now I end up in problems with matching and processing data in a fast way. Also some processes are not clear/ or fully defined yet. I do not want to change much on the default AX system other then some report layouts and maybe some interfaces. I do not like the way with all the ‘transports’ like in SAP. It makes a mess in the end and costs a lot of money to keep track of all these changes. I cannot affort Visual Team Studio, which I would like to use. I use Subversion, but it cannot work with AX. So I am sticked to myself and the help of volunteers now.
==> It is quite funny to see that developers help out eachother on forums and post throughout the whole day. But for business systems like SAP and AX everybody keeps it secret so that they can invoice for the knowledge they own. Problem with this behaviour is that the system gets ‘person dependent’ in the end. So the consultants create value for a company or not based on the invoices you want to pay. In my opinion not good at all. Once we launch the bbayt shopping engine it should fit for about 99% to somebody’s need.
"The offset would come from the bank, but it depends upon how you are importing the file and what it contains. Yes you do not have the bank, but the file does so the import could create it, but then in reality the offset is your bank account where the money goes, this of course will be on the import file, "
==> I don’t get what you state here. The code processes the import file and matches it. Then it does not place an offset account for the customer it matches. Is this a bug or can it be set to ‘bank’ somewhere?
Okay so the partner wants to bill you for what they do. I see no issue with this. You have no budget however is an issue with what you are trying to do.
You state you have a clearly defined RFI, but this is miles from a specification. You also then state that the processes are not clear or fully defined. How can you successfully integrate with half finished processes?
I have no issue with no changes in AX, you need to write a successful script import to complete a general journal from a file. This is pretty simple depending upon the file and details.
I see no difference between AX and NAV - AX code is shared on here, albeit at a lower rate because there are less AX users. As for keeping things a secret I also disagree - are you paying for me to contribute to this forum? I also assume you will be giving bbayt away to everyone for free? It fits 99% of customers and you want it to be built by people giving you free code and advice, so the compliment will be repaid I presume - you would not want to keep your functionality a “secret” and charge people for it would you?
You have witten code to create a journal from an import file. The import file is from a bank, you know the bank so you can get the import file to populate the offset account to get the journal to balance. The matching of the payment to the customer is a completely different concept to the balancing of the journal. Settling receipts to open invoices is one thing, financially processing the debtors with the offset to represent the money in your bank for reconciliation is another.
It is the task of the implementation partner to create specifications and then deliver a quote that matches the RFI. I follow the standard processes of AX which I am tought, but some are not tought, so I don’t know. Other don’t match as how they where told. Like reading this balance. It is actually not done that every transaction needs to be reconcilled through the bank; far too cumbersome. So I will specify what I need here and change the code for as far I can do that. I think this discussion is something for another thread.
“Secret”
Maybe my English is not well enough to explain you better. I drop the discussion because of that. Sure, my source code can be bought and pricing is much different than that of AX. But it is not ready yet. Must be a bit more customer friendly and the basic processes regardless of having an ERP system must be added.
“Import”.
This is what the thread is about. I assume there must be a bug not matching the payment to the customer with the offset as ‘bank’ . I have the feeling I do not fully understand. I will search customer source for it. I believed the match through the ledger will automatically ‘settle the receipt with the open invoice’. I see a balance = 0 after posting the ledger with bank data. However, the voucher without offset cannot be booked, the offset cannot be set manually, so there must be a bug in the software. Which is not mine by the way. It is a path of the partner since the transaction text was not read form the MT940 file.
So after a long discussion I still am not sure what to do. I have the feeling settling the open invoices is done automatically on reading the bank file import.
I meant quoting in the forum [:D] so I can see what reply you make against the line you are quoting!
With the import there is no bug in matching the payment to the customer. I can as standard load a journal for a cash receipt against a customer offset to a bank and applied to an invoice. Alternatively you may have configured AX to performa automatic settlement for you - have you done this? You will not find your answer on customer source, unless you are looking in the finance manual at cash receipt processing - it will tell you how it is done. Your issue is that the import you have is not doing what someone would do manually. Your balance will be 0 but they are not applied so they are open,
Again with the offset your import needs to set this. It needs to set the offset account type to bank and the offset account to the bank account where the receipts have been processed . I assume you mean it cannot be set manually due to the volume, not that it cannot be set manually - therefore the issue is again in your import.
If a partner wrote this for you I presume you tested it, and I presume you accepted it, otherwise I would have sent it back as not worksing to specification and therefore I would not pay for it and I would not expect to be billed by the partner for correcting the mistakes they have written in. Assuming you can get away with saying that. My guess is you paid little for a lot with tight restrictions on what you would get, and therefore it falls to you to fix. But in your shoes I would go to the partner, because this is a relationship, a partnership, and if they will do nothing, they are not for you.
So as to what you do.
Ensure you can manually do what you want. Load a cash receipt, apply it to an invoice and offset it to the bank.
Assumign you can do 1 manually all of the issues are with your code, so depending upon the situation tell your partner to resolve the issues they have delivered, or fix them yourself depending upon the requirement.
First of all ‘automatic settlement’ is on. I checked also the balance per customer. If I look at a balance before the import of the file, a customer has a negative balance and there are invoices in transaction editing. After posting the ledger, the balance is 0 and "open transaction editing’ does not shown any invoices anymore. So that is oke. I think the current import code still wants to settle manually as I understand from above. It leaves the offset account empty and therefore cannot be posted and throws invalid voucher records. For the vendors the automatic settlement is off. So I think this is a bug. I think I can test this by turning off automatic settlement for sales orders and thn load. In that case I need to do a settlement meself and select the invoice. I think this is what you are referring to. I however do not understand “Your balance will be 0 but they are not applied so they are open”. Automatic settlement would do this job, won’t it?
Selecting the drop down for “account type”, "then looking up the customer based on the transaction text is quit cumbersome if you process a lot of internet payments.
I leased my AX so I cannot dispute the work by with holding the invoice. I have not done any acceptance testing since there was no time scheduled for that. I am not happy with the service provided so I am working out things myself now. I asked another firm for help, but also the do not qoute: thety only want to invoice for at least 4 hrs per day. At start they did not want to quote because the only quote for above 20 users or above 100K euro. So I am dead locked with AX.
So turn automatic settlement off?
If turned on and not woking something wrong in code. As I assumed already?
From the first statement here your automatic settlement is working perfectly. Automatic settlement applies the cash receipt to the invoice automatically. Having a customer balance of zero and nothing in open transaction editing proves this.
The offset account is your bank - every line will need the offset account type set to bank and the account to the bank number in AX - I am guessing this would be fairly easy to add in to the import code.
Not sure what you mean with automatic settlement for vendors is off and this is a bug - we are only talking about sales and cash receipts here aren’t we?
You lease AX - but presumably you paid for the work to develop the import code? Acceptance testing is obviously critical, and you are now paying the cost of not having the time. I would suggest you work with your partner rather than trying to work it out yourself - you will keep on pulling on bits of string otherwise - it will be much more painful but that choice is yours.
You may be deadlocked with AX - why did you buy it?
No leave automatic settlement on, this is working, the non-importing of the offset account is an unrelated issue. The settlement is working fine from what you say - I presume you test this by manually adding in the offset and posting it and seeing the settlement? For me from what you have said your issue is the import not populating the offset, not the settlement which seems to be working form what you have said.
==> Yes, settlement on sales orders. I referred to Purchase orders since in that scenario the ‘offset’ is always filled in. So there must be a bug in the import code, not adding the ‘offset’ to the ledger. I only try to figure out the scenario where is goes wrong based on the information I get from you.
==> Since no other ERP system (small scale) has a configuration for sizes. We sell shoes with various sizes. NAV and AX have these variants.
==> Once the import code matches a customer it does not add the ‘offset’ (which you state should not be blank, but BANK). So I removed that record and added a duplicate with a ‘offset’ BANK since ‘offset’ cannot be changed after commit. As I understand the ‘offset’ is only used to show the ‘offset on the bank account’ and has nothing to do with ledger posting. It is used to calculate the start and end balance of the BANK account. But once it is missing, the balance is wrong so therefor the errors pops up? Right?
==> So the import code is wrong. I received this piece of code from my partner since the MT940 (default of AX) contained a bug and did not import the transaction text. Now it does, but the report is gone. Before this code change I received a nice report with all transactions. That report is gone and now this ‘matching’ does not put the offset. The partner says this is a fix to MS AX. But I cannot track down any issue in a tool from Microsoft to see what is happening. This ‘bug fix’ is free since it is in the Business Ready program. I created an issue in Customer Source towards MS since otherwise bugs keep existing and need to be patched by all partners everytime. But the system does not allow to track down adequately if it is resolved and in what release. Therefor the ‘version tracking’ of AX could be better.
==> This ask me the question: can I see easily what changes have been applied. Then I can figure out easier what is wrong. I will ask the partner also. I think I know already their answer: “this is not under support, you have to pay for it”. :-(.
Yes there are standard import routines in AX, I have not had a customer use one as standard however. If though they have “fixed” it you will have applied the objects through the xpo so you know what objects were updated, it would have told you. I believe in the AOT you can identify these although not as easily as they are standard MS now not user driven. I would take the updated code out and start again. If you told MS it was not working and they gave you a fix that made it worse I would tell them this and ask them to fix it.
At a simple level does the payment journal you use for the payment proposal/import have the balancing account defined on the journal itself? I do not know your process but this is one easy eay of having the offset defined.
I believe there are users in this very forum using NAV to handle the garment industry requirements, so I would say you did not look very far into systems having configuration for sizes.
Thank you. I have been studying the changes. Only changes from the implemenation partner are there. But I cannot see that they moved out the ‘offset’, I even do not see it in code. Only that a new ‘transaction’ is created. Maybe that method uses defaults? I don’t know how to step easily in the code to drill down into the function. I will spend some time later.
Interesting. I wrote a complete user instruction including the errors. But I cannot attach a MS Word document here. The very strange thing is that for another bank account the import with settlements on the vendors and even customers having bank accounts work without any problem. The offset account is filled in. The strange thing is that in this code the vendor/customer are lookup. How can I attach a file here?
The code creating the ledger is next I guess. But I don’t see an offset, or is that the bankAccountStr added ?
I would like to revert the code. I think I need to export the current object and then revert? to sys ? How to do that?
Yes, you are right. I investigated two retail solutions, but they did not meet the demand for a sole ecommerce shop. They had cash registers for shops and/or could not handle prepayments or work only with own printed barcodes. Secondly, we want to develop something ‘generic’ not related to a specific vendor, so therefor we choose to use the defaults of AX. Also because of the item table not being multiplicated for every entity (company) as in Navision. One article (item) should be used throughout all companies. I guess now NAV is changing this. NAV was/is more a country-based application, where AX is more global and using a more normalized database. However, in AX the configuration with items are not normalized and therefor causing troubles in case you want to augment/ enrich the variant data like sizes, colors. But that is other discussion.