70,000 unapplied payments in SAGE 500 -

We have just discovered that we have 70,000 payments that were never applied to open invoices in our SAGE 500 System,  We know how to manually apply each one but that is just not realistic. We are desperate for a bulk solution to this problem. Is there any way to apply these payments, mostly credit card payments, to the open invoices via upload or in bulk some how.  Any help in GREATLY APPRECIATED.

  • The solution depends a bit on whether it is 70K customers or just a few customers with 70K payments.There is an Apply All option in Apply Payments and Memos that will automatically apply a payment to the oldest documents sequentially. There are a couple of methods to import the application data but it would require some manipulation and you would need either SQL programming experience or the Data Porter or maybe Data Import modules. Since most of that Apply All logic is contained in the UI code, a decent developer could build a mod to scroll through the data set and automatically apply it (it's an easy Customizer mod).

    Thinking out of the box in practical terms though, it does appear you have a scenario where the customers should have been configured with a Balance Forward billing type. If nothing else, this is something to research and test if you don't want to go down the custom development path. If it is the 70K customer scenario you would still need a way to change some customer attributes in bulk through the back-end. Or, if they are just a large number of POS customers, there's always the possibility of merging the bulk of them into a single account then Apply All would work really well.

    Okay, there might be some people rolling their eyes at this point, so although the problem is not insurmountable, you just need to have some extra skills or do some additional testing to come up with the right solution.

  • I am just curious ... were they 70k payments entered manually over time, or were they imported or added via some sort of application in bulky chunks?

  • in reply to Ramon M.

    They were entered manually over time, none have been applied since 2018, all different customers. 

  • in reply to Contefication

    It is 70K different customers, no payments have been applied since 2018

  • in reply to Alexandra Marks

    Not that this helps you now, but both AR and AP have payment explorers and reports.  To monitor going forward, perhaps a report / explorer should be run periodically looking for an unapplied amounts <> 0.  As I am a technical person and not a business user, perhaps others can comment on how they prevent unapplied amounts for getting out of control.  

    Also, both AP and AR have an option to warn about unapplied payments during document entry.  If these options are not checked, then perhaps you may wish to consider checking them.

  • in reply to Alexandra Marks

    Oh, that's classic! You need to make some decisions on how you want to proceed. The only way to effectively handled that much data is with either back-end manipulation or front-end modification or a bit of both. Cleaning up the current data set is a concern but you also need to contemplate what you will want to do if this is a continuing problem, and how you want to approach the purge of any of that data.

  • in reply to Alexandra Marks

    I didn't spend much time with this so no error trapping, it's not pretty, etc., but you could add a button to the toolbar in Apply Payments and Memos using Customizer, then add this code to the button and see if it works for you. I don't have much data to try it.

        Dim rs
        Dim sSQL
        Dim dataSetSize
        Dim sCompanyID
        Dim moStrings
        dataSetSize = InputBox("Enter Number of Pmts", "AutoApply Pmts", 0)
        If dataSetSize = 0 Then
            Exit Sub
        End If
        With Form.moForm.oClass
            Set moStrings = CreateObject("StringUtils.CStringUtils")
            sCompanyID = moStrings.gsQuoted(.moSysSession.CompanyId)
            sSQL = "SELECT DISTINCT " & _
                    " TOP " & dataSetSize & _
                    " [tarCustomer].[CustKey], [tarCustomer].[CustID], " & _
                    " [tarCustPmt].[CustPmtKey], [tarCustPmt].[TranID] " & _
                    " FROM [dbo].[tarCustomer] " & _
                    " INNER JOIN [dbo].[tarInvoice] " & _
                    " ON [tarCustomer].[CustKey] = [tarInvoice].[CustKey] " & _
                    " INNER JOIN [dbo].[tarCustPmt] " & _
                    " ON [tarCustomer].[CustKey] = [tarCustPmt].[CustKey] " & _
                    " LEFT OUTER JOIN [dbo].[tarPendCustPmtAppl] " & _
                    " ON [tarCustPmt].[CustPmtKey] = [tarPendCustPmtAppl].[ApplyFromPmtKey] " & _
                    " WHERE NOT [tarInvoice].[Balance] = 0 " & _
                    " AND NOT [tarCustPmt].[UnappliedAmt] = 0 " & _
                    " AND [tarPendCustPmtAppl].[ApplyFromPmtKey] IS NULL " & _
                    " AND [tarCustomer].[CompanyID] = " & sCompanyID & _
                    " ORDER BY [tarCustPmt].[CustPmtKey] ASC; "
            Set rs = .moAppDB.OpenRecordset(sSQL, 1, 0)
            While Not rs.IsEOF
                Form.Controls("cboApplyFrom").ListIndex = 0
                Form.Controls("txtCustID").Text = rs.Field("CustID")
                Form.Controls("TxtCustKey").Text = rs.Field("CustKey")
                Form.Controls("txtAppDocNum").Text = rs.Field("TranID")
                If Form.Controls("chkShowInvoices").Value = 0 Then
                    Form.Controls("chkShowInvoices").Value = 1
                End If
                CreateObject("WScript.Shell").PopUp "Calculating Applications", 1, "Delay for Steve", 4096
                If Not Form.Controls("CurUnappliedBal") = 0 Then
                End If
                Form.moForm.HandleToolbarClick "K8"
        End With
        If Not rs Is Nothing Then
            Set rs = Nothing
        End If
        MsgBox "Run Complete"