Sage 100

Has anyone created a report that shows the General Ledger accounts that will be posted to for unposted sales order invoice batches? 

The background:  client has had two invoices with NonTaxableAmt being an outrageous figure, in the millions, when the total of the Lines is, say, under $1000.  We don't have enough data yet to determine the cause (could be 3rd party).  Unfortunately the users updated the journal without looking at it, causing the DT posting to be out of balance.  We had THAT discussion, and now the client is requesting a report that shows the GL accounts and amounts that will be posted BEFORE they update the invoice journal.

I modified the Daily Sales Report to show the sales order totals (they don't change the totals on the invoices, so they should match).  If the SO totals don't match the invoice totals - bold red.  This helps but doesn't fulfill the requirements of the Accounting executives.

Initially I created a report showing the invoice lines' COGS and Sales accounts, with a link to the product line IM account - but this won't capture the instances where the totals don't match.

I identified the SO_DailyPostingWrk table as the table that holds the needed data, but the Daily Sales Report doesn't use this table, and darned if I can find what report DOES use it.  Not only is it a wrk table, rendering it useless if added to a report; it is not even in the list of available tables for a Crystal report or Excel query. 

Thanks in advance for any thoughts or ideas!

------------------------------
Susan Pawlowic
Sage 100 Solutions Architect
RKL eSolutions, LLC
spawlowic@rklesolutions.com
(717) 735-9109 Ext. 4352
------------------------------

We don't do calculations by GL account... just check for the symptoms by comparing the Lines data vs header totals (or header taxable amount vs sales tax amount, if that is the customer issue).  Here are examples of automated alerts we have set up... pretty simple reports (plus Visual Cut).


------------------------------
Kevin Moyes
Technical Systems Analyst
Munjal White Consulting Co.
Toronto ON
------------------------------

Thanks Kevin.  The sales journal has already been modified to display these discrepancies.  I will let them know the GL report is a no go.  I suspected as much but was really hoping I could get to that SO_DailyPostingWrk data!

------------------------------
Susan Pawlowic
Sage 100 Solutions Architect
RKL eSolutions, LLC
spawlowic@rklesolutions.com
(717) 735-9109 Ext. 4352
------------------------------

Original Message:
Sent: 11-04-2021 09:55
From: Kevin Moyes
Subject: Sales Order Invoice GL Posting Accounts Report

We don't do calculations by GL account... just check for the symptoms by comparing the Lines data vs header totals (or header taxable amount vs sales tax amount, if that is the customer issue).  Here are examples of automated alerts we have set up... pretty simple reports (plus Visual Cut).


------------------------------
Kevin Moyes
Technical Systems Analyst
Munjal White Consulting Co.
Toronto ON
------------------------------

Original Message:
Sent: 11/4/2021 9:55:00 AM
From: Kevin Moyes
Subject: RE: Sales Order Invoice GL Posting Accounts Report

We don't do calculations by GL account... just check for the symptoms by comparing the Lines data vs header totals (or header taxable amount vs sales tax amount, if that is the customer issue).  Here are examples of automated alerts we have set up... pretty simple reports (plus Visual Cut).


------------------------------
Kevin Moyes
Technical Systems Analyst
Munjal White Consulting Co.
Toronto ON
------------------------------

@Randy Marion what gave me a glimmer of hope about SO_DailyPostingWrk is that it is showed populated with data when the sales journal was run and before proceeding with posting  (at least in my test it was).  No, none of the standard sales journal reports show the future GL postings, only the GL accounts for the line-items.  The GL postings for those two erroneous invoices were based upon the out of balance totals.  The sales order totals comparison report will need to suffice.  And yes, I would have thought the users were looking at the journals as well...we have had that talk in the past as well as very recently!​

------------------------------
Susan Pawlowic
Sage 100 Solutions Architect
RKL eSolutions, LLC
spawlowic@rklesolutions.com
(717) 735-9109 Ext. 4352
------------------------------

Original Message:
Sent: 11-04-2021 10:47
From: Randy Marion
Subject: Sales Order Invoice GL Posting Accounts Report

FYI - all of the Wrk tables in the system, regardless of module, should work like this.  They typically never have data in them.  I think of them more as a template.  Whenever a report program, (not custom crystal reports you or I may write), is executed, a copy of the Wrk file is created with a unique identifier appended to the file name, this unique Wrk file is populated with data as the selection process dictates and is passed to Crystal for final processing and printing.  An easy way to check this for yourself is to pull up a report in View and while it is displaying on your screen, goto DFDM and browse to the data directory, sort the files by Date so the newest float to the top and you should see your Wrk file.  You can open it up and take a look at the data if you like in DFDM.  When you get out of DFDM and close the viewer, the unique Wrk file should disappear. 

The advantages of this are, multiple people can be running the same report with their own selection criteria and not step on anybody's toes, and Crystal gets a single file to process without any joins with other tables to slow things down and muck up the works.  On the downside, if you want to grab some data, like you do, well you can't get it from the Wrk file.  You would need to write a Crystal report that goes to the source files which sounds like SO_SalesOrderHeader and SO_SalesOrderDetail at a minimum and possibly others.  Or perhaps the SO Invoice equivalents. 

All that said, depending on how your system is set up, you may not be able to accurately create a report the way it sounds like you want.  The data in the detail tables for COGS and Sales are account keys and a lookup of the proper formatted account number would need to be employed.  Sometimes the account key data is recognizable as the account the users are familiar with, but it does not have to be.  The lookup is not impossible, but a complication.  If you have Posting by Whs or Posting by Division turned on, now things get really interesting and I am not sure how you would get Crystal to swap out various account number segments in either or both of these cases.

There are a whole slew of sales reports that can be set up to run at SO Invoice Register Update time just prior to clicking Yes to update... none of these give any indication of the problem or can be adapted to show what you need to see?  I would like to think your users will be more attentive to reports before updating now...  We can always hope!

--

Randy Marion * Software Systems Engineer *

Original Message:
Sent: 11/4/2021 9:55:00 AM
From: Kevin Moyes
Subject: RE: Sales Order Invoice GL Posting Accounts Report

We don't do calculations by GL account... just check for the symptoms by comparing the Lines data vs header totals (or header taxable amount vs sales tax amount, if that is the customer issue).  Here are examples of automated alerts we have set up... pretty simple reports (plus Visual Cut).


------------------------------
Kevin Moyes
Technical Systems Analyst
Munjal White Consulting Co.
Toronto ON
------------------------------