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.

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

  • in reply to Contefication

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

  • 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)
            rs.MoveFirst
    
            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")
    
                Form.Controls("chkShowInvoices").SetFocus
                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
                    Form.moForm.moPmtApp.Process_AutoApplyClick
                End If
    
                Form.moForm.HandleToolbarClick "K8"
                rs.MoveNext
            WEnd
        End With
    
        If Not rs Is Nothing Then
            rs.Close
            Set rs = Nothing
        End If
    
        MsgBox "Run Complete"

Reply
  • 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)
            rs.MoveFirst
    
            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")
    
                Form.Controls("chkShowInvoices").SetFocus
                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
                    Form.moForm.moPmtApp.Process_AutoApplyClick
                End If
    
                Form.moForm.HandleToolbarClick "K8"
                rs.MoveNext
            WEnd
        End With
    
        If Not rs Is Nothing Then
            rs.Close
            Set rs = Nothing
        End If
    
        MsgBox "Run Complete"

Children
No Data