• • Home
  • Forum
  • Download
  • Affiliate
  • Product Directory
  • Knowledge Base
  • News

Program to find if item location accounts have been used other places in the application

Support utility to find incorrect item location

Solution ID = KB-362

Goal : Program to find if item location accounts have been used other places in the application

Version = All Version

Module : SLMFG

Fact 1 : Support Utility

Fact 2 : KB-351

Database : Progress, SQL

Fix

As described in KB-351, there are several reasons why the totals on one of the inventory module's inventory valuation reports may be out of sync with the G/L account balances for your inventory accounts. One way this occurs is if you use an inventory location account anywhere in the system other than on item stockroom locations. For example, if you would enter an inventory account into the account field on a PO line item that is for a non-item master item, the inventory reports and G/L would be out of sync once the line was received since its value would be in the account in G/L but not included in the inventory valuation reports.

The attached programs can help determine if an inventory account has been used in such a fashion. While it does not check every account field in the system, it does check many places that would be the most likely to contain an inventory account. The utility finds all G/L accounts that are used on an item location and then checks for the following uses of those accounts which will all cause G/L to be out of balance with the inventory reports.
If an inventory location account is used as:

 

·          the G/L Account on purchase order line items that are for items that are not in the item master.

·          a WIP account on a job.

·          a WIP account on a work center.

·          the Inventory Adjustment account for a product code.

·          the Routing variance account for a product code.

·          the Sales account for a product code.

·          a Cost of Goods Sold account on a product code.

·          the manually entered offsetting account when a miscellaneous receipt or issue was performed.

·          an account in any journal or ledger transaction for which the reference does not begin "INV" or "PRJ".

 

The last item listed is meant to find any cases where someone entered an inventory account in a transaction being processed in the accounting modules or entered it directly into a transaction in a non-distribution journal. Since all transactions generated from the manufacturing modules have a reference that starts "INV" or "PRJ", any that do not have such a reference were probably entered elsewhere (if you have made manual journal entries in the past to get them back in balance, those will probably appear in the output).

The output will need reviewed since it may or may not be responsible for the discrepancy. For example, if you made manual journal entries to get inventory and G/L in sync at the end of the previous month, they are out of sync once again, and a miscellaneous receipt from three months ago appears in the file, it obviously isn't responsible for the most recent problem.

If the problems listed include an account being used in one of the product code accounts, in a WC WIP account, on a PO line that is not Complete, or a job WIP account for a job that is not Complete, they should be corrected. If the problem listed is from a closed PO, a closed job, a miscellaneous receipt or issue, or a journal or ledger transaction, the only remedy at this point is to make manual journal entries to get them back in balance.

For Progress versions (SL6 and lower):  The loc-acct-use.p program should be run from the query editor.  It outputs the details of any discovered occurrences to the file c:\temp\loc-acct.out.   At the top of the program there is statement which defines a variable which can be used as a starting date for scanning journal, ledger, and miscellaneous material transactions which use a location account.  It is currently set to 1/1/08.  You can change it to a more recent date in order to reduce the number of transactions the script must process.


For SQL versions (SL7 and higher): The loc_acct_use.sql  script should be run from query analyzer while connected to the appropriate App database.  In this script there is a set statement about 25 lines from the top in which you can change the starting transaction date described above.