Importing AR Transactions into Sage 500 ERP


I have been able to successfully import an Invoice batch of customer statements from a subsystem into Sage 500 AR and then post this batch. This batch consist of driver account invoices (fees and charges) and offsetting credit memos (redeemed CC script, etc.). Naturally these driver accounts statements individually balance enabling the posting. I was able to proceed to the point where the credit memos need to be applied. This then presents a problem, since the batches include the EFT payments, cheques and on account credits, so in some cases the credit memo amount exceeds the invoice amount. This is a "no go" in Sage. Alternatively we can't just leave the credit memos unapplied because process would not be complete and we eventually want an aging of these receivables in Sage.

It has been suggested that I split out the EFT payments, cheques, redeemed CC script and on account credits and post them as a separate Payment batch. I prefer the first approach because it keeps everything together, however, in that we have hit a roadblock in Sage, the second idea has merit. Has anyone else attempted to do this and had some success?

  • 0

    Your credit amounts cannot exceed the open amount on an invoice. This means your credits may remain open and unapplied in whatever form they take but the end balance of either document must follow the rules of that document type: an invoice or debit memo has to have a 0 or positive balance, a credit memo or payment has to have a 0 or negative balance. The balances are calculated according to a standard rule that it equals the amount of the document minus or plus any applications (depending on type).

    That does present a situation where they must be manually reconciled later if the documents have a remaining balance. From your description though, it seems that the customer billing types might be better configured as balance forward rather than open item. With that setting change your unapplied credits should be applied to the oldest invoices by the system and just roll into the balance at the beginning of each month. That might allow you to import the invoices and credits without having to worry about calculating the applications.

    It's been a while since I've used it but the invoice, credit and payment detail is still there, it is just the logic behind mostly the reports and statements that changes. It may be worth researching and testing.

    From the user guide:

    When you enter a payment, the system reduces the total balance owed by the customer by applying the payment to the oldest balance first.
    The system calculates the current balance for a balance forward customer as the balance forward amount plus any additional invoices entered during the current month minus any payments received as follows:

    Current balance = balance forward + additional invoices – payments received.

    You can only assign a single currency to a balance forward customer. The currency in the customer’s address is restricted to the currency specified for the customer. 

  • 0 in reply to Contefication

    Thank you for taking the time to provide this feedback. I trust that the following attachments do not confuse this situation any further; this illustrates the various scenarios which I have drawn on a PowerPoint chart, I have also included an example where we appear to be having problems applying Credit memos. All batches are balanced.

  • 0 in reply to Chris Bailey

    There is a problem with the invoice, so I suspect you may have developed your own import interface. If that is the case, your document balancing logic is incorrect, if it is not, you have encountered a bug or logic hole in the import you are using with your version. Either way, no balance on a document should exceed the original amount so you would need to adjust your import logic or methodology to reflect that rule. 

    Since these are posted, you can review the applications in a detailed Aged Receivables report, or in the back-end if you familiar with SQL. If this is production, you will need to reverse the applications.

  • 0 in reply to Contefication

    Fortunately we are doing all of this in test. You have help greatly in that all signs are pointing to splitting the payments, on account credits and possibly the redeemed CC script will need to be spun out of the invoice import and submitted separately as a matching payment import.

  • 0 in reply to Chris Bailey

    I'm relieved it's not production, reversing applications and manually reapplying can be tedious. You really should look into using balance forward where it may allow you to dispense with the application logic entirely.  

  • 0 in reply to Contefication

    Could you explain "Balance Forward"? Or alternatively point me in the right direction. Or best of all show me some examples.

  • 0 in reply to Chris Bailey

    That snippet from the user guide explains the setting but the behavior changes to that of a credit card statement, i.e., the previous period's invoices and payments are summarized to a beginning balance and only the current period's activity is indicated. Whether or not it works properly and in your scenario would require you test it. You can search on the term in application help as well but the What's This? help (right-click on any form control) on the Billing Type on Default tab in Maintain Customers will lead you to the basic description.

    Billing type
    A very important feature of Sage 500 is the ability to accommodate both open item and balance forward methods for billing customers. You have the option of selecting open item, balance forward, or both in Set Up AR Options. If you select a billing type of Open Item in Set Up AR Options, all customers are classified as open item customers. Likewise, if you select the billing type of Balance Forward, all customers
    are classified as balance forward customers. However, if you select Both, you can assign either billing type to individual customers in Maintain Customers. If you initially select Both in the Billing Type field and you enter balance forward and open item customers, you cannot restrict the billing type to one method in the future.

    You can convert balance forward customers to open item customers. However, you cannot convert an open item customer to a balance forward customer unless all transactions for that customer use the same currency..

  • 0 in reply to Contefication

    I will try this to see if it works in my situation. What I like from the sound of it, is it keeps all the transactions together versus having to split part of the customer's batch into payments.

  • 0 in reply to Chris Bailey

    Hello I have decided to take a different approach to try to accomplish my objective. I have also decided to use your suggestion of using Balanced Forward accounting which as much as i can see requires me to split the import into (1) invoice and credit memo and (2) payments. This will also test the limitations of importing data.

    Instead of trying to bring in all the sub-system paid customer statement entries in one batch, try to import two batches (1) invoice and credit memos and (2) the payments. Importing and posting the invoice was not an issue. In importing the flat file for payments I ran into errors using Posted Invoices. First a sequence error, so I added a Batch Header. This appeared to solve this error. Next I experienced then number of fields error, however my import exactly mimics the example given in Sage. Here is the file...

    B; ; ;020121CTTestp; ;501; ; ; ;04/12/2021;CTG ; ; ; ; ; ; ;
    A; ; ;04/09/2021;0005134119-IN;511;5134119;0;0;-105.47;-105.47;04/09/2021;

    The bottom row for ApplyFromTranID includes the transition reference number from the posted batch and for ApplyTo InvcNo, not knowing or finding an invoice number in the system, for the lack of a number I inserted the same.

    Can you help clarify

  • 0 in reply to Chris Bailey

    correction "transaction reference number", my error...i am sure you figured this out