Cleaning up AR transactions using Sage 300 Write-Off process

Companies sometimes have customer entries in Accounts Receivable that need to be cleared from the ledger because they are non-recoverable.  This includes transactions such as bad debts, debt forgiveness (e.g. interest charges) and small over/under payments.  Often the transactions sit on the ledger for a while because it is troublesome to correct them.

In Sage 300, there are a couple of processes that can be used to clear these items.  In situations where there are multiple entries to clear at one time, and where a set of criteria can be defined for doing that, the write-off function may be the answer.

First, review the Accounts Receivable account sets to make sure the Write-Offs General Ledger account is the correct one for each.  Write-Offs is the expense account to which amounts are debited when you write off transactions and balances; it is often called ‘bad debt’. The Create Write off Batch screen automatically produces transactions for this account.

Mary Blog may 4th pic 1

Either now or before posting the final results, print the Accounts Receivable Aged Trial Balance for the customers and transaction types used in the Write Off selection process, to use in verifying the results.  In this example, the Aged Trial Balance report used is for customer 1200, Ronald Black, and the transactions that will be written off are outstanding transactions on/before 10/31/2019:

Mary Blog May 4th pic 2

Mr. Black’s account has three transactions that will be part of the write-off:  a payment and two invoices.

Go to Accounts Receivable Periodic Processing and choose “Create Write-Off Batch”.  Complete the screen selections as required to gather the appropriate transactions.  The choice for the “type of transaction” to write off include outstanding transactions, overdue transactions, overdue balances, outstanding balances and outstanding retainage.  In this example, “outstanding transactions” is chosen.

Choose the Account Type; Ronald Black’s account is Balance Forward, so that is selected.

Choose the document types to include in the process; at least one must be selected.  In this example, “all” is chosen, though it could have been limited just the types of transactions outstanding.

Choose either “amount” or “percentage” as the “Select By”, and enter non-zero values in the “From” and “To” fields.  When creating a batch to write off small balances, enter a range where the values represent the limits the company wishes to write off.  In this example, to include the outstanding cash, which shows as a negative number, the “From” is set to that value, and the “To” to the value of the highest invoice value for the customer.   (Note:  it is not necessary to use the exact amounts for the range, only that they be set to allow the desired range of documents to be captured).

In the bottom portion of the screen, choose up to 4 different criteria to select the customers who will be included.  Leave all as (None) to gather data for all customers who meet the other criteria, or enter the appropriate range.   Selections may be done by a number of customer-related variables – use the drop downs to review and choose the criteria required.  In this example, only Customer Number 1200 (for Ronald Black) is chosen.

Mary Blog may 4th Pic 3

Note that there is an “optional fields” tab.  This may be used to further scope the transactions that will be included in the Write-Off batch.  This example does not use optional fields so none are selected.

Once all criteria are set, select “Process”.  A batch of transactions meeting the criteria is created and posted as an open batch with a description of “Write Off” in the Accounts Receivable Adjustments batch list.  Each transaction being written off is a separate entry in the adjustment batch.  The batch may be reviewed and transactions edited as needed, and then posted as a normal adjustment batch.  If the batch is incorrect, it can be deleted and the process started over.

The following shows one entry from the write-off on Ronald Black’s account:

Mary Blog May 4th Pic 4

Written by Mary Clark, Consultant

WAC Solution Partners- New England

Sage 300 Reports

Reports are one of the most important tools that everyone in a well-oiled organization needs to accomplish their goals. From the top of the organizational chain down to front line managers, reports are the life blood. When it’s difficult to summon the exact information you require, heads explode and the life blood starts hemorrhaging. A little over dramatic but point is, it shouldn’t have to be that way.

Canned reports that are provided by that new ERP application can be insufficient and ineffective. You require custom reports that offer the precise information in the correct sort order and so forth. You have reached Information Nirvana.

The problem with creating custom reports in many ERP systems are that they give you the tools such as SAP Crystal Reports but no real good solution to easily enter all those important parameters. If you rely on Crystal Reports internal parameter screen, it’s kludgy at the very least and ugly at best. If you’re providing your client with the panacea of reports, you are letting them in on a huge let down when they have to enter the necessary parameters.

In a recent Sage 300 project, I came across an incredibly elegant solution to my parameter screen problem. A fellow consultant provided me with some valuable information using Visual Basic Macro (.AVB) files to provide a customized parameter screen for Sage 300 projects. Although I’m not an expert in Sage 300, this quite literally has the potential to open a lot of custom report doors and I will share what I learned with you.

If you have some knowledge of Visual Basic for Applications (VBA) then you will have no problem utilizing Visual Basic Macro’s for your Sage 300 report projects. I will refrain from providing a history lesson on the programming language and its intricacies. Let’s get started!

The Three Elements

Sage 300 requires that you provide three elements to creating a fully functional parameter screen for you reports:

  • An .AVB file. This is essentially a Visual Basic program. It has the graphical screen and source code embedded in the file. If you have ever built a program using Visual Basic 6 or VBA in Microsoft Access; same thing.
  • An entry in the XXrpt.ini file. This file is located in the program modules folder. In our case, we will be using the Manufacturing Module that was developed by AutoSimply. The module folder starts with an “MF” so you will find this in the MF62A folder in our case. The entry will be added to the end of the ini file. The entry consists of report information such as the crystal report name, paper size, number of output parameters, and what those parameter names are. The image below is an example of our setting values.

John May 4th Blog Pic 1

  • And finally, the crystal report. Our report uses an ODBC driver for the data source. It points to our Sage 300 data located in Microsoft SQL Server. I won’t go into the details of setting up the database configuration or report development, since that is a topic deep in mystique and beyond the subject matter in this article. What you need to have in this report are parameters precisely named as the ones in the ini file example above. More on this later.

Where to Start?

What comes first, the report or the parameter screen? The obvious answer in my mind is the report. This, of course, is the most difficult task you might think, and you would be right. After you have developed a parameter screen, this becomes a reusable component in your future report projects in Sage 300 and your main focus and work will be towards making that awesome report for your boss or client.

Writing the Report

As mentioned earlier, I won’t go into the minutia of writing a crystal report but there are a couple of important elements that you will need to include to make this successful.

  • A SortBy Group
    • The SortBy group is a grouping that allow the report to be sorted by any field in the report that you deem necessary. The sortby group will be passed the value to sort by in a formula that will retrieve the parameter from our parameter screen. It is important to offer your client this option since hard coding sort fields are not dynamic and limit the effectiveness of the report. In our example, we will sort by three different fields, all mutually exclusive.
  • Parameters
    • This is the most important element to our report. This is the whole reason we’re building a parameter screen in the first place, right? Below is an example of where those parameters are located in the crystal reports designer.

John Blog May 4th pic 2

The parameters highlighted will be used in the report select expert. This will force Crystal Reports to only give us the information we requested with our parameters. Under the Crystal covers, it’s essentially creating a select statement to SQL Server for the data related to those parameter fields. Below is an example of one such query:

SELECT “MFORDH”.”MONUM”, “MFORDH”.”MOTYPE”, “MFORDH”.”ITEMNO”, “MFORDH”.”MODESC”, “MFORDH”.”MOREF”, “MFORDH”.”ORDQTY”, “MFORDH”.”PRODQTY”, “MFORDH”.”ITEMDESC”, “MFORDH”.”DUEDT”, “MFORDH”.”ORDERDT”, “MFORDH”.”RELEASEDT”, “ICITEM”.”STOCKUNIT”, “MFORDH”.”COMMENTS”, “MFOPT”.”MOSERIES”, “MFORDH”.”MOSERIES”, “MFORDH”.”MOSTATUS”, “OEORDH”.”PONUMBER”, “MFORDSO”.”SONUM”, “OEORDH”.”CUSTOMER”

FROM   (((“ASDAT”.”dbo”.”MFORDH” “MFORDH” INNER JOIN “ASDAT”.”dbo”.”ICITEM” “ICITEM” ON “MFORDH”.”ITEMNO”=”ICITEM”.”FMTITEMNO”) LEFT OUTER JOIN “ASDAT”.”dbo”.”MFORDSO” “MFORDSO” ON “MFORDH”.”MOUNIQ”=”MFORDSO”.”MOUNIQ”) LEFT OUTER JOIN “ASDAT”.”dbo”.”OEORDH” “OEORDH” ON “MFORDSO”.”SONUM”=”OEORDH”.”ORDNUMBER”) INNER JOIN “ASDAT”.”dbo”.”MFOPT” “MFOPT” ON “ICITEM”.”AUDTORG”=”MFOPT”.”AUDTORG”

WHERE  (“MFORDH”.”MONUM”>=” AND “MFORDH”.”MONUM”<=’ZZZZZZZZ’) AND (“MFORDH”.”ITEMNO”>=” AND “MFORDH”.”ITEMNO”<=’ZZZZZZZZ’)

Pretty long and confusing if you’re not fluent in the T-SQL language.

 

With your report completed, we will continue on to the .AVB file. As stated earlier, if you have experience writing Visual Basic code in any of the Microsoft Office suite of products, then you will know how to write code in Visual Basic Macros. Since this is a high level article, I will not give a line by line rundown of the code but a list of programming rudiments.

  • Your Main Module will include API definitions for Sage 300 and some MS Windows functions.
  • References to the Accpac Manager and MS Windows dll’s.
  • A main sub routine to start the program with your initializations.
  • A main form that will display the necessary parameter fields. (Figure 3)
  • Event code that will fire when any of those parameter fields or buttons are acted upon.
  • Error captures. This is needed to handle error exceptions in an elegant way with some form of exception description for debugging.

Conclusion

With the ability to create these custom parameter screens, you have a set of powerful development tools at your fingertips. There is no need for SDK’s that are costly and have heavy learning curves. For those with some programming ability, this capability with bring immediate dividends to your organization. If you were given the choice between figure 4 and figure 5, which one would you choose?

I think figure 5 is the obvious choice.

Figure 3.

John Blog May 4th pic 3

Figure 4.

John Blog May 4th Pic 4

Figure 5.

John Blog May 4th pic 5

written by John Trainer, Consultant

WAC Solution Partners- New England

Installing Intelligence Reporting Report Designer License in Sage 300

This should be easy, right?  Wrong!

Roger blog Pic 1Here is what I received from Sage, the Product Activation document.

 

I’m sure most of you have seen these before and once received, you think you know what to do. I know I thought that.

First, I know that I need to install the Report Designer component so, I go to Control Panel, and Change the Sage 300 installation to include this component. Done.

Now I log in to 300 as ADMIN and proceed to the Licensing Manger to enter the SERIAL NUMBER AND ACTIVATION CODE as required.

I enter the following, because that is what I have done at least 100 times.

logo

In this situation the Sage terminology is a little unclear. The License Manager says Activation Code but the paperwork says Activation Key

I get an error message stating the keys are invalid. I try again suspecting I fat fingered it (I do have fat fingers). No go, I get the error message again.

Looking again at the Product Activation document, I see that at the top there is another Product ID/Serial #.

pic 3

I figure why not and enter this serial number and the activation key/code again, and I get the same error.

Now I am getting frustrated. I take a quick glance at the installation manual for Sage 300 which has a section related to IR but I can’t find anything that leads me to believe I am doing anything wrong.

pic 4However, I have a second (harder to find) document that differs from the installation manual and it says this…

Well this all seems strange, new, and quite unnecessary to me, but let’s see if I can get this mythical Report Manager Serial #.

First, I look in the 300 license manager for an IR license and there is none. Yes you do get a Report Manager license installed, but Sage doesn’t make a license entry.

That’s OK, I figure it must be in the customer record of the Sage Partner Portal. I log in to the Partner Portal and pull up the customer. Nope, it is not there either.

According to the document, I can get it from the Sage Customer Portal (but not the Partner Portal). Unfortunately, I do not have those credentials to access the Customer Portal.

Back to the drawing board.

Once again, I look over the Product Activation document and once again I notice something new.

pic 5

Now in the two years that I have been implementing Sage 300 I have never used an unlocking code, but I notice that it has the same length as the activation key/code (minus the hyphens). Sooo. I try the serial number from below and the unlocking code from above – ERROR. Finally out of sheer boredom I try the Product ID/Serial Number with the Unlocking Key/Code in the Activation Code/Key field and (can I get an AMEN), IT WORKS!

And that my friends and co-workers is how you install a Report Manager license (though you may want to skip to the end)

Answer on how to install an Intelligence Reporting Report Designer License in Sage 300: Product ID/Serial Number with the Unlocking Key/Code in the Activation Code/Key field

Written by Roger Detzler, Consultant WAC Solution Partners- New England