Sage 100

I have a customer request: 

This customer sells to the big box stores ( Walmart, Target, etc) . They receive cash receipts from the customers and the receipts may be paying off 100 or more invoices. In some cases, the invoice they are paying may be listed wrong ( missing on my customer's AR Aging ) or blank altogether.

Has anyone dealt with this before and put the "remainder" cash receipt balance into some type of holding account? 

Naturally,my customer could work around this by closely examining the spreadsheet and rectifying errors prior to import. I'm wondering if there's a faster way to just "plug" anything that didn't match up to an invoice to a clearing GL? 

Sage seems to say you cannot create new or prepaid invoice numbers on a cash receipt import.

------------------------------
Wayne Schulz - Schulz Consulting - 860-516-8990
------------------------------

My first impulse would be to run the input CSV through a VBScript, that does an ODBC lookup for the invoice first, and writes out two files: ToImport and BadLines... where they deal with BadLines manually.

------------------------------
Kevin Moyes
Technical Systems Analyst
Munjal White Consulting Co.
Toronto ON
------------------------------

I've dealt with it before.

When my customer got a check from Costco, their clerk was spending most of the day reconciling the 25 page Costco remittance advice report (a PDF downloaded from the vendor portal at the time) and keying in the CR Entry. Some of the issues were:
* Costco using their PO Number for reference instead of the Invoice No. 
* Order Entry clerks accidentally mis-keying the long PO numbers on the original SO.
* Costco deducting spiffs and other marketing charges right off the check, unannounced.

I decided to use V/I instead of creating a custom import program. I had it create prepayments for the invoice numbers not found in Sage (by setting the NewPrepayment field on the Lines) and created G/L Distribution lines for the spiffs. This combo allowed the import to complete (by being in balance) and then clerk was able to quickly review / fix the mismatched invoices. I also had to lookup the InvoiceNo from AR_OpenInvoice based on the CustomerPONo. Also had to modify the import source file on-the-fly to make it V/I friendlier .

So overall we were unable to identify a way to deal with it in standard Sage and came up with a V/I solution with heavy Perform Logic. Although custom, for them it was valuable and worth it because 6 hrs of work turned into 30 minutes.

------------------------------
Alnoor Cassim

Email: alnoor@asifocus.com
Ph: 949-689-9887
Orange County, CA
------------------------------

My customer is just getting around to implementing this.
The import works fine so long as the deposit total = the invoice detail

They sell to big box store and that store automatically subtracts 15% when they pay the invoices

The gotcha with Sage and importing the CR is if the deposit total <> the detail then Sage won't import anything. The only way I got this to work was to change the spreadsheet so that the deposit total = invoice lines paid.  And then the customer would have to remember to go back into Sage - change that deposit total back ( make it 15% less ) and add in a GL line on the detail to make the whole deposit in balance.

I'm thinking that having them manually adjust the spreadsheet is the easiest way to go. 
Have I overlooked anything simpler to get a cash receipt import to pull in the total received, apply in full against invoices listed and plug the difference to a GL for discounts?

------------------------------
Wayne Schulz - Schulz Consulting - 860-516-8990
------------------------------

I'd have the user add the 15% line... first column as "GL", with the amount listed too, then set up VI formulas to add the GL line based on that data.

------------------------------
Kevin Moyes
Technical Systems Analyst
Munjal White Consulting Co.
Toronto ON
------------------------------

Original Message:
Sent: 03-03-2021 10:39
From: Wayne Schulz
Subject: Importing cash receipts where not all invoices are allocated

My customer is just getting around to implementing this.
The import works fine so long as the deposit total = the invoice detail

They sell to big box store and that store automatically subtracts 15% when they pay the invoices

The gotcha with Sage and importing the CR is if the deposit total <> the detail then Sage won't import anything. The only way I got this to work was to change the spreadsheet so that the deposit total = invoice lines paid.  And then the customer would have to remember to go back into Sage - change that deposit total back ( make it 15% less ) and add in a GL line on the detail to make the whole deposit in balance.

I'm thinking that having them manually adjust the spreadsheet is the easiest way to go.
Have I overlooked anything simpler to get a cash receipt import to pull in the total received, apply in full against invoices listed and plug the difference to a GL for discounts?

------------------------------
Wayne Schulz - Schulz Consulting - 860-516-8990
------------------------------