Mining SQL Database for total Accounts Payable balance from Dynamics GP 9.0

ammexit
ammexit used Ask the Experts™
on
I'm looking to export the total AP balance from the Dynamics GP SQL database to use for a SQL report.  My total does not match the total when the report is run directly from GP.  To get the total out of SQL I'm going to table "SP20000" and grabbing all entires in column "curtrxam".  Can anyone shed some light on what I'm missing?
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®

Commented:
Hi,

I assume you meant "PM20000" for your table?  (PM = Payables management)

Regarding the difference between your total and the report total, it could be a few things.  

Some initial questions:

1)  What is the *exact* report that you are running from AP to get your AP balance total?  Is it the Aged Trial Balance, Aged Trial Balance with Options, or Historical Aged Trial Balance?  Or is it some other report?

2) Are you using the Multi-Currency module?

3) Have you tried using SmartLists to query open Payables Transactions to see if that matches your report total?


Thanks,

Steve Endow
Dynamics GP MCT

Author

Commented:
Yes, sorry about the typo.  I meant PM20000.

1.  The report we're running is the "Aged Trial Balance with Options".
2.  No multi-currency mode.
3.  I haven't tried the Smartlist, but I will right now.

Commented:
Okay, great, that is helpful.

So the Aged Trial Balance with Options has some options that might affect the report balance:

1) Print / Age as of date
2) Exclude Credit Balance
3) Exclude Unposted Applied Credit Documents
4) Range restrictions, such as From/To Vendor ID, Class, Type, etc.

Can you tell me what settings you have for these three options when you run the AP aging?

Thanks,

Steve
How to Generate Services Revenue the Easiest Way

This Tuesday! Learn key insights about modern cyber protection services & gain practical strategies to skyrocket business:

- What it takes to build a cloud service portfolio
- How to determine which services will help your unique business grow
- Various use-cases and examples

Commented:
Correction, the Print/Age as of date on the TB with Options report should not affect the total, only the aging buckets.

Author

Commented:
My apologies, but I am not a GP user.  How do I determine the settings for these options?

Commented:
Not a problem.  If you able to login to GP, go to the following menu option:

  1. Reports --> Purchasing --> Trial Balance
  2. Select Aged TB with Options
  3. In the "Options" list box on the left, select the Option set that is used to run the aging report you are trying to match
  4. Click on the Modify button
When the options window appears, see if:
  1. The Credit Balance checkbox is checked
  2. The Unposted Applied Credit Documents is Checked
  3. There are any Restrictions listed in the box at the bottom of the window

Author

Commented:
Great, thanks.  The only boxes checked are "Zero Balance" and "No Activity".  There are no restrictions entered.
Commented:
Okay, great, thanks.

I have two suggestions.  

1) If you have to match that report, and if "Unposted Applied Credit Documents" is UNchecked, then you will need to union in the PM Transaction Work table (PM10000) to check for applied payments in that table.  This is because the report is NOT excluding Unposted credits, so if there were ever an unposted, applied credit in GP, it could cause your query to be different than the report.  You'll have to do a little bit of work to get that setup, as you'll need to join in the PM10200 table to see if any of the payment records in PM10200 are applied.  The alternative is to ask your AP folks if it is okay for them to Exclude Unposted Applied Credit Documents on the AP aging report.  (which shouldn't be a huge issue, unless there is something about your business that makes that option critical)

2) When you query the PM20000 table, are you differentiating between invoices and credits and payments?  The PM20000 table stores vendor invoices, vendor credit memos, and your AP payments to vendors.  But GP stores everything as a positive dollar amount in the table, so if you aren't changing the signs on credits and payments, that would cause your query to have a balance that is greater than the report.  When you query the balances, invoices need to be positive, credits and returns and checks need to be negative.  So I would recommend using a CASE statement to swtich the signs depending on the document type.

I've attached some sample SQL that accomplishes this using a derived table, but you can probably do it a few other ways depending on how your query is structured.

Let me know if this helps, or if you have already looked into these items.

Steve


SELECT VENDORID, SUM(CURTRXAM) FROM (
SELECT VENDORID, 
CASE 
	WHEN DOCTYPE = 1 THEN CURTRXAM
	WHEN DOCTYPE IN (4, 5, 6) THEN -CURTRXAM
END AS CURTRXAM FROM PM20000
) AS dtAPDocs
GROUP BY VENDORID ORDER BY VENDORID

Open in new window

Author

Commented:
Great!  Thanks.  Huge help!

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial