Importing AR Transactions into Sage 500 ERP

SUGGESTED

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?

Parents
  • 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

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

  • 0 in reply to Chris Bailey
    SUGGESTED

    The Import AR Data Utility does not include the ability to import payments. What you are referencing is an application, which could be sourced from a credit or debit memo, a payment or in some cases other invoices but this utility only supports invoice applications. The capabilities of the flat file import utilities are generally only effective in certain scenarios and require user manipulation and intervention. You should research balance forward in more depth as you may not need to bother with applications.

    Also, the Data Import Manager has many more capabilities including mapping and automated scheduling so user interaction is not required. You can also leverage the data import capabilities directly in the back-end employing the staging tables. Both DI and direct staging use the same back-end stored procedure code for processing the data and this is where you can find the pending document abilities as well, which includes pending customer payments.

    All documents (invoices, vouchers, orders, etc.) are referenced with their given identification type but in the schema this is generally stored in the TranNo column. For posted AR invoices this would be tarInvoice.TranNo or tarInvoiceLog.TranNo (parent) and is how the document is referenced in the company context for users. The easiest way to view this in the front-end is with View / Edit Invoices. It appears the import references the TranID (tarInvoice.TranID) though so this column is the TranNo with a dash (-) and the document type as defined in the setup, with the default ID of IN for invoice, CM for credit memo and DM for debit memo.

    If you are reviewing the schema, data is generally referenced by surrogate keys (tarInvoice.InvcKey), with a fair amount of normalization, although rules for aggregate tables can differ. I am noting this because all the data import and manipulation code constructs reference the data using the parent-child concept wherever a one to many relationship may exist. That's important to understand because it is reflected in the import functions, so an invoice or memo are separate documents from a payment which is separate from an invoice application which is also separate from a payment application.

    The explanation of the concepts and capabilities only get more complex from that point but if you intend to employ the AR utility then test using the Print All Data log and Print Without Import option to get as much feedback as you can from the APIs.

  • 0 in reply to Contefication

    Thank you. My sin is that I like to go right to the solution rather than spend time on the process. In researching our companies prior payment imports we did use a 3rd party and staging tables.

  • 0 in reply to Chris Bailey

    Hi Chris, if any of the above suggested answers helped, please do mark them as verified White check mark for the benefit of others in this forum :). Thank you!

  • 0 in reply to Erzsi_I

    So far these suggestions are potential solutions, based upon this what I am now trying is "Balanced Forward " accounting for this tradeclass together with an import of pending invoices of sub-system paid customer statement to see if this will work. Where possible I have avoided the compliactions of splitting the customer statement into invoices/credit memos and payments.

  • 0 in reply to Chris Bailey

    We have reached an impasse which presents unique problems for Sage AR. In test we have imported the sub-system customer account statements Opening Balance and transactions using invoices and credit memos (including the Driver/Owner (DO) payments by EFT or cheques) over 3 weeks, successfully posted all of these batches as pending batches using Balance Forward accounting and compared them back to the sub-system account balances at the end of the period. For example this involved approx. 1,300 DO statements and 25,000 transactions. The differences are minor and relate to statement rounding of sales tax, etc. 

    Now here are the issues as we see them. These credit memos and invoices never disappear from AR, so the balance will continue to grow. Since we have imported the DO payments as a transaction the invoices are never converted into cash transactions. Alternatively we have consider the approach of removing the payment transactions and importing them separately as another batch, aligning these up using staging tables to pay off the invoices. The problem here is not all DO settlements involve a payment, in some cases the DO increases his account balance or conversely he pays down the balance without taking a payment. Similarily we are discontinuing the practice of leaving statements open a the end of the week, so agin these statements will be closed without a payment. Therefore these cases would never be addressed. 

    We would be very interested in knowing how a revolving credit account which has some similarities to this accounting is handled 

  • 0 in reply to Chris Bailey

    Most of what you're stating here are accounting problems. The documents don't "disappear" from a customer account unless you are able to purge them. In a balance forward methodology the transactions are indicated until the period has concluded at which point they are summed into a beginning balance on a statement in the next period. If a period is a week then you need to adjust the settings accordingly. These might include the fiscal periods and aging categories.

    As to the balancing methodology, that's up to you. You need to import the documents to represent the account transactions and balances you expect to see. If the account is closed at the end of a week, you need to balance it out by importing the document(s) that represent an off-set of the balance. This balancing methodology is also addressed in the write-off and refund features within Sage 500.

  • 0 in reply to Contefication

    RE DO2073.msg

    We proceeded to do a month's worth of a/r transaction imports in test after setting the tradeclass to Balance Forward making the term of daily. However we are surprised with the results. Overall the DO account balance balanced back to the subsystem, which is what we wanted, but per invoice line the balance shown is meaningless. Is there something we didn't do? If you can open the attachment please let me know and I try to demonstarte the issue some other way...

  • 0 in reply to Chris Bailey

    I should further explain because I can see confusion with understanding the attached email. What we are surprised about is that for a DO settlement where there is an invoice and matching credit memo, detailing all the related transactions (also included are the offsetting EFT payment). We expected that these two documents would be rolled together on a daily basis in order to produce an DO account balance. The system didn't do this. Is there something we didn't do?

  • 0 in reply to Chris Bailey

    AR_Aging_Cust_Summary.pdfAR_Aging_Invc_Detail.pdfAR_Aging_Invc_Summary.pdfWow, you're still on this? The testing really shouldn't have taken more than a day once you had the import configured. 

    • There is no "Trade Class" in 500, it's the billing type (might be terminology, might not)
    • You're looking at BIE (probably invoices) which is always going to show the detail
    • Looks like you ran the Aged Receivables with Invoice Detail, try Customer Summary
    • You imported unapplied memos. Generally the logic works with payments but as stated previously, import and apply the documents to represent the balance you want to see
    • One inconsistency is that I stated the invoices couldn't be negative. The keyword was supposed to be shouldn't. They can be negative but that normally presents a reconciliation nightmare. Maybe for you that will work with just an application to the same invoice. Open Item billing type might work for you then
    • All that detail you show in Enter Cash Receipts (and everywhere else) are unapplied invoices and memos. They can be applied using Applied Payments and Memos
    • You can build your own reports as well

    If you are still having some problems, I would suggest you get some additional help from either your reseller or a Sage 500 consultant. Aging attachments show 1 Open Item customer from sample data with different aging formats. 

Reply
  • 0 in reply to Chris Bailey

    AR_Aging_Cust_Summary.pdfAR_Aging_Invc_Detail.pdfAR_Aging_Invc_Summary.pdfWow, you're still on this? The testing really shouldn't have taken more than a day once you had the import configured. 

    • There is no "Trade Class" in 500, it's the billing type (might be terminology, might not)
    • You're looking at BIE (probably invoices) which is always going to show the detail
    • Looks like you ran the Aged Receivables with Invoice Detail, try Customer Summary
    • You imported unapplied memos. Generally the logic works with payments but as stated previously, import and apply the documents to represent the balance you want to see
    • One inconsistency is that I stated the invoices couldn't be negative. The keyword was supposed to be shouldn't. They can be negative but that normally presents a reconciliation nightmare. Maybe for you that will work with just an application to the same invoice. Open Item billing type might work for you then
    • All that detail you show in Enter Cash Receipts (and everywhere else) are unapplied invoices and memos. They can be applied using Applied Payments and Memos
    • You can build your own reports as well

    If you are still having some problems, I would suggest you get some additional help from either your reseller or a Sage 500 consultant. Aging attachments show 1 Open Item customer from sample data with different aging formats. 

Children
No Data