Announcements
910
Or does GP10 even record the matching P&L GL account numbers to an AP check? I have found the Balance Sheet GL accounts for the AP checks, which just gives me the AP cash account, but I am looking for the GL accounts for the distributions to find out what expense categories it was allocated to. Let me know if you need more details, thanks.
*This post is locked for comments
Thanks, Mike! That did it...your "working backwards" reply was actually the exact way I was working forwards to find this...I just was joining to the wrong fields!! I had never realized the APTVCHNM field in the PM30300 Apply To History table...what a revelation. I kept using the original VCHRNMBR to try and link to the PM30600 Distributions table and was going in circles. From the Distributions table I used the DSTINDX values to link to the GL00100 to get our GL account #'s. This is perfect. Thanks again!
I believe this view will give you what you're looking for: victoriayudin.com/.../sql-view-for-payables-apply-detail-and-gl-distributions-in-dynamics-gp.
Might be easier to see the potential end result of a SQL query pulling this type of data.
Here is a mock-up of a report I've created around this type of information:
The issue I've encountered with this with clients is for any single AP check, users want to see the underlying associated expense accounts for review.
To my knowledge, I don't know of a way to see that check 12345 for $1000 actually paid expenses associated with X expense accounts.
A query in the above manner allows the user to see the AP check, the transactions it was applied to, and the expense accounts associated with the transactions the check was applied to.
There are scenarios where the data may be incorrect under the circumstance of a partial pay with the check and validating against actual distribution amounts, but there really isn't a way around this if those numbers are needed under that circumstance.
Maybe I'm missing something in the request here but I would think you should just run the Financial -> Account Transactions SmartList and filter on Series = Purchasing. This will give you the GL postings for the AP vouchers as grouped by journal. The check is not where I would start as it is likely to pay many vouchers and really is just AP and cash but the posted voucher itself will put both sides of the entry in a single journal which can be queried through SmartList and show the expense offset(s).
You kind of need to work backwards from the check to find this. The distribution tables, when linked to directly from the check, will only have your cash and AP account. What needs to be done is to go from the check and link to the apply tables. This will give you the transactions that the check was applied to. From the transactions the check was applied to, you then need to link to the distribution table to find the expense accounts associated with those transactions.
Thanks for the reply, Mike.
Where in the PM30600 table are the P&L accounts for these transactions found? If you're referring to using the TRXSORCE field to join to the GL20000 table and using the ACTINDX field to find the GL accounts (or simply using the DSTINDX field in PM30600 to find the same GL account numbers), then these are not P&L accounts for AP checks, those are the Balance Sheet accounts...that's what I've been running into all day. The AP check numbers are payment documents and I am looking for the matching invoice details which will give me the P&L accounts. And that may be the root issue, as I am unsure if GP10 matches AP checks to invoice details or if it is simply a Payment document type and that's that. Any other suggestions?
From the transaction associated with the AP check, you would need to link to the PM Apply To History (PM30300) and PM Apply To Work Open (PM10200) to see what transaction(s) the check is applied to. From the Apply tables above, you would then need to link to PM Distribution Work Open (PM10100) and PM Distribution History (PM30600) to find the actual P&L accounts in the series of transactions.
This is probably best done through a SQL query due to the issues around the apply tables and the exact distribution type you're after once you get to that level.
#1
Almas Mahfooz
3
User Group Leader
Share
Report
All responses (
Answers (
