Setting the default expense account for consumables

You and I both know that, out of the box, VISUAL does not allow you to specify a default GL expense account on a consumable part.  And that you have to manually specify the expense GL account every time you purchase it.   AUGH!

Yes, there is a work around where you can set the default GL expense account at the VENDOR level.  This may be okay if you expense all consumables to the one GL account.

Infor VISUAL ERP - Vendor Maintenance
Setting the "Default Expense Account ID" in Vendor Maintenance (under the ACCOUNTING tab)

However, this workaround doesn't work if you buy more than one type of consumable from the same vendor and each type of consumable needs to be expensed differently.  It would be great if VISUAL allowed us to define the GL expense account at the PART level - this wouldn't be an issue.

Here's one way to get around this. 

It involves using a user defined field (UDF) in PART MAINTENANCE and a OnSave macro in the PURCHASE ORDER ENTRY window.  The UDF can be used to enter the expense account and we can have the macro look this up when entering a purchase order for a consumable part. 

  1. In PART MAINTENANCE, designate one of the standard UDFs to be used to enter the expense GL account for a consumable part.  You could also use a custom user defined field and create a drop down list linked to your GL accounts table.

    Infor VISUAL ERP - Part Maintenance

  2. Create an OnSave macro in the PURCHASE ORDER ENTRY window that will look up and use the GL account entered in the UDF (created in step 1) to populate the GL Expense Account ID column of the purchase order.

An OnSave macro like the following would work: (mind the line wraps!)

strServerName="E N T E R    N A M E    O F    D A T A B A S E    S E R V E R"
strDatabase="E N T E R    N A M E    O F    D A T A B A S E"
strUserName="E N T E R    U S E R N A M E"
strPassword="E N T E R    P A S S W O R D"

strcnn="Driver={SQL Server};Server=" & strServerName & ";Database=" & strDatabase & ";Uid=" & strUsername & ";Pwd=" & strPassword & ";"

set rs = CreateObject("ADODB.Recordset")

Set ORDERLINES = Lines.Value
LCnt = ORDERLINES.Count

If LCnt>0 then
    For i = 0 To (LCnt - 1)
        Set ORDERLINE = ORDERLINES(i)

        if len(Orderline("PART_INFO") & "")>0 then

            PartID=Split(Orderline("PART_INFO")," - ")

            SQL="SELECT USER_1, CONSUMABLE FROM PART WHERE ID='" & PartID(0) & "';"
            rs.open sql, strcnn

            ExpAcctID=""

            if not rs.eof then
                rs.movefirst

                if rs("CONSUMABLE")="Y" then
                    ExpAcctID=rs("USER_1")
                end if

            end if

            if len(ExpAcctID & "")>0 then
                Orderline("EXPENSE_GL_ACCT_INFO")=ExpAcctID
            end if

            rs.close

        end if

    Next

end if

This macro assumes that we're using the standard user defined field 1 for the expense GL account.  You may want to beef up this script to include validation checks - ie ensure that the expense GL account id exists, prevent the changing of the GL accounts on PO lines that have already been received, etc.  This should get you going in the right direction.

Now you're able to set your default expense account on each consumable part.

TrackBack

TrackBack URL for this entry:
https://www.typepad.com/services/trackback/6a01053702bb53970c017d3c7f7125970c

Listed below are links to weblogs that reference Setting the default expense account for consumables:

Comments

Feed You can follow this conversation by subscribing to the comment feed for this post.

The comments to this entry are closed.

WHO IS VISUALNUGGETS?

Rich Ferlatte
Results Driven VISUAL Specialist

CONSULTING SERVICES

Of course, I am available for hire!

ERP Implementations & Upgrades
Analytics & Reporting
Integrations & Macros
General VISUAL Support

ONLINE VISUAL WORKSHOPS

Taking it to the next level and designing a program around "Hands On VISUAL Training".

Check them out - there's sure to be one to interest you!

Operational Workshops
Reporting Workshops
Financial Workshops
Inventory Workshops
Quality Workshops

VISUAL EXTENSIONS

BOM Xploder
Drastically improve your BOM visibility and discover shortages at ANY level of your bill of material no matter how deep.
SmartViews
See, analyze, and track information that helps you better support individual, departmental, and your company’s performance metrics and goals.
eBoard - Financial Power App
Developed for accounting professionals looking for powerful and easy-to-use GL reporting software. Access live financial data, drill down to support details and export financial statements to Excel in one click.
Inventory App
This Inventory Reconciliation App provides a systematic approach to control and manage your Visual inventory effectively and efficiently
Credit Card Integration
Easily accept and process credit card payments right from within VISUAL.
ACH Document Processing
Leveraging VISUAL's payment scheduling and batch payment functions, with the added convenience, performance, and security of Electronic Funds Transfer payment processing.
Latitude Manifest & Shipping System
A seamless multi-carrier shipping solution that integrates to Infor VISUAL ERP. See how it can simplify your small parcel and LTL shipping and package tracking.
Sales Tax Automation with AvaTax
Easily automate sales and use tax in VISUAL with AvaTax. No more manually entering data or uploading rate tables and tax information.
Workforce Management with Kebrite Solutions
Streamlines the collection of Job, Labor, Payroll, Time & Attendance, and a vast array of HR related data-- with live VISUAL integration.