September 23, 2010 
Who doesn’t need yet another view for Payables transactions in Dynamics GP? 🙂 The view below is a combination of my Payment Apply Detail and GL Distributions for AP Transactions views. It lists all Payments, then shows the transactions they were applied to and the GL distributions of those applied to transactions.
You can find other Dynamics GP Payables views here or check out my GP Reports page for other views and reporting links.
CREATE VIEW view_Payables_Apply_and_GL_Dist
AS
-- ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~
-- view_Payables_Apply_and_GL_Dist
-- Created on Sep 23 2010 by Victoria Yudin
-- Flexible Solutions, Inc. 2
-- For updates see https://victoriayudin.com/gp-reports/
-- Does not take Multicurrency into account
-- Will return multiple lines for payments that were applied
-- to more than one transaction and for invoices with
-- multiple GL distributions
-- GL Distributions are shown for the Applied To documents
-- Voided payments are excluded
-- Payments for $0 are excluded
-- Updated on Feb 22, 2012 to allow for multiple identical
-- GL distributions on same transaction - thanks to
-- Geraldine for helping me track this down!
-- Updated on Nov 20, 2012 to add Distribution Reference
-- Updated Nov 24, 2013 to add apply to doc due date and
-- optimize code
-- Updated Feb 24, 2015 to add transaction description
-- ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~
SELECT
P.VENDORID Vendor_ID,
V.VENDNAME Vendor_Name,
V.VNDCHKNM Vendor_Check_Name,
CASE P.PYENTTYP
WHEN 0 THEN 'Check'
WHEN 1 THEN 'Cash'
WHEN 2 THEN 'Credit Card'
WHEN 3 THEN 'EFT'
ELSE 'Other'
END Payment_Type,
CASE
WHEN P.PYENTTYP in (0,1,3) THEN P.CHEKBKID
ELSE ''
END Checkbook_ID,
CASE P.PYENTTYP
WHEN 2 THEN P.CARDNAME
ELSE ''
END Credit_Card_ID,
P.DOCDATE Payment_Date,
P.PSTGDATE Payment_GL_Date,
P.VCHRNMBR Payment_Voucher_Number,
P.DOCNUMBR Payment_Document_Number,
P.DOCAMNT Payment_Functional_Amount,
coalesce(PA.APTVCHNM,'') Apply_To_Voucher_Number,
CASE PA.APTODCTY
WHEN 1 THEN 'Invoice'
WHEN 2 THEN 'Finance Charge'
WHEN 3 THEN 'Misc Charge'
ELSE ''
END Apply_To_Doc_Type,
coalesce(PA.APTODCNM,'') Apply_To_Doc_Number,
coalesce(PA.APTODCDT,'1/1/1900') Apply_To_Doc_Date,
coalesce(PA.ApplyToGLPostDate,'1/1/1900') Apply_To_GL_Date,
coalesce(AD.DUEDATE,'1/1/1900') Apply_To_Due_Date,
coalesce(PA.APPLDAMT,0) Applied_Amount,
coalesce(G.ACTNUMST,'') GL_Account_Number,
coalesce(G2.ACTDESCR,'') GL_Account_Name,
CASE D.DISTTYPE
WHEN 1 THEN 'Cash'
WHEN 2 THEN 'Payable'
WHEN 3 THEN 'Discount Available'
WHEN 4 THEN 'Discount Taken'
WHEN 5 THEN 'Finance Charge'
WHEN 6 THEN 'Purchase'
WHEN 7 THEN 'Trade Disc.'
WHEN 8 THEN 'Misc. Charge'
WHEN 9 THEN 'Freight'
WHEN 10 THEN 'Taxes'
WHEN 11 THEN 'Writeoffs'
WHEN 12 THEN 'Other'
WHEN 13 THEN 'GST Disc'
WHEN 14 THEN 'PPS Amount'
ELSE ''
END Distribution_Type,
coalesce(D.DEBITAMT,0) Debit,
coalesce(D.CRDTAMNT,0) Credit,
coalesce(D.DistRef,'') Distribution_Reference,
AD.TRXDSCRN Transaction_Description
FROM -- payments
(SELECT VENDORID, DOCTYPE, DOCDATE, VCHRNMBR, DOCNUMBR,
DOCAMNT, VOIDED, TRXSORCE, CHEKBKID, PSTGDATE, PYENTTYP,
CARDNAME
FROM PM30200
WHERE DOCTYPE = 6 AND DOCAMNT <> 0 AND VOIDED = 0
UNION
SELECT VENDORID, DOCTYPE, DOCDATE, VCHRNMBR, DOCNUMBR,
DOCAMNT, VOIDED, TRXSORCE, CHEKBKID, PSTGDATE, PYENTTYP,
CARDNAME
FROM PM20000
WHERE DOCTYPE = 6 AND DOCAMNT <> 0 AND VOIDED = 0) P
INNER JOIN PM00200 V -- vendor master
ON P.VENDORID = V.VENDORID
LEFT OUTER JOIN -- apply records
(SELECT VENDORID, VCHRNMBR, DOCTYPE, APTVCHNM, APTODCTY,
APTODCNM, APTODCDT, ApplyToGLPostDate, APPLDAMT
FROM PM10200
UNION
SELECT VENDORID, VCHRNMBR, DOCTYPE, APTVCHNM, APTODCTY,
APTODCNM, APTODCDT, ApplyToGLPostDate, APPLDAMT
FROM PM30300) PA
ON P.VCHRNMBR = PA.VCHRNMBR AND P.VENDORID = PA.VENDORID
AND P.DOCTYPE = PA.DOCTYPE
LEFT OUTER JOIN -- distributions
(SELECT VENDORID, VCHRNMBR, CNTRLTYP, DEBITAMT, CRDTAMNT,
DSTINDX, DISTTYPE, DistRef, PSTGDATE
FROM PM10100
WHERE PSTGSTUS = 1 AND CNTRLTYP = 0
UNION ALL
SELECT VENDORID, VCHRNMBR, CNTRLTYP, DEBITAMT, CRDTAMNT,
DSTINDX, DISTTYPE, DistRef, PSTGDATE
FROM PM30600 WHERE CNTRLTYP = 0) D
ON PA.VENDORID = D.VENDORID AND PA.APTVCHNM = D.VCHRNMBR
LEFT OUTER JOIN GL00105 G -- for account number
ON D.DSTINDX = G.ACTINDX
LEFT OUTER JOIN GL00100 G2 -- for account name
ON D.DSTINDX = G2.ACTINDX
LEFT OUTER JOIN -- apply to docs, added for due date
(SELECT DOCTYPE, VCHRNMBR, DUEDATE, TRXDSCRN
FROM PM30200
UNION
SELECT DOCTYPE, VCHRNMBR, DUEDATE, TRXDSCRN
FROM PM20000) AD
ON PA.APTODCTY = AD.DOCTYPE and PA.APTVCHNM = AD.VCHRNMBR
-- add permissions for DYNGRP
GO
GRANT SELECT on view_Payables_Apply_and_GL_Dist to DYNGRP
Disclaimer: I tested this on limited data, if you find an issue or have a suggestion for improvement, please let me know and I will post the update here for everyone.
Victoria Yudin 
How hard would it be to add credit memos to this view? This view has been a lifesaver for one of my clients but their auditors are asking questions about missing distributions when the invoices were offset by credit memos. In other words, a payment for an invoices or invoices maybe be less than the total of the invoices because credit memos were applied.
LikeLike
Hi Ann,
The following code should do it. If it does not come through properly in the blog comment, let me know, I can email it to you.
SELECT
P.VENDORID Vendor_ID,
V.VENDNAME Vendor_Name,
V.VNDCHKNM Vendor_Check_Name,
CASE WHEN P.DOCTYPE = 5
THEN 'Credit Memo'
ELSE CASE P.PYENTTYP
WHEN 0 THEN 'Check'
WHEN 1 THEN 'Cash'
WHEN 2 THEN 'Credit Card'
WHEN 3 THEN 'EFT'
ELSE 'Other'
END
END Payment_Type,
CASE
WHEN P.PYENTTYP in (0,1,3) THEN P.CHEKBKID
ELSE ''
END Checkbook_ID,
CASE P.PYENTTYP
WHEN 2 THEN P.CARDNAME
ELSE ''
END Credit_Card_ID,
P.DOCDATE Payment_Date,
P.PSTGDATE Payment_GL_Date,
P.VCHRNMBR Payment_Voucher_Number,
P.DOCNUMBR Payment_Document_Number,
P.DOCAMNT Payment_Functional_Amount,
coalesce(PA.APTVCHNM,'') Apply_To_Voucher_Number,
CASE PA.APTODCTY
WHEN 1 THEN 'Invoice'
WHEN 2 THEN 'Finance Charge'
WHEN 3 THEN 'Misc Charge'
ELSE ''
END Apply_To_Doc_Type,
coalesce(PA.APTODCNM,'') Apply_To_Doc_Number,
coalesce(PA.APTODCDT,'1/1/1900') Apply_To_Doc_Date,
coalesce(PA.ApplyToGLPostDate,'1/1/1900') Apply_To_GL_Date,
coalesce(AD.DUEDATE,'1/1/1900') Apply_To_Due_Date,
coalesce(PA.APPLDAMT,0) Applied_Amount,
coalesce(G.ACTNUMST,'') GL_Account_Number,
coalesce(G2.ACTDESCR,'') GL_Account_Name,
CASE D.DISTTYPE
WHEN 1 THEN 'Cash'
WHEN 2 THEN 'Payable'
WHEN 3 THEN 'Discount Available'
WHEN 4 THEN 'Discount Taken'
WHEN 5 THEN 'Finance Charge'
WHEN 6 THEN 'Purchase'
WHEN 7 THEN 'Trade Disc.'
WHEN 8 THEN 'Misc. Charge'
WHEN 9 THEN 'Freight'
WHEN 10 THEN 'Taxes'
WHEN 11 THEN 'Writeoffs'
WHEN 12 THEN 'Other'
WHEN 13 THEN 'GST Disc'
WHEN 14 THEN 'PPS Amount'
ELSE ''
END Distribution_Type,
coalesce(D.DEBITAMT,0) Debit,
coalesce(D.CRDTAMNT,0) Credit,
coalesce(D.DistRef,'') Distribution_Reference,
AD.TRXDSCRN Transaction_Description
FROM -- payments
(SELECT VENDORID, DOCTYPE, DOCDATE, VCHRNMBR, DOCNUMBR,
DOCAMNT, VOIDED, TRXSORCE, CHEKBKID, PSTGDATE, PYENTTYP,
CARDNAME
FROM PM30200
WHERE DOCTYPE in (5,6) AND DOCAMNT <> 0 AND VOIDED = 0
UNION
SELECT VENDORID, DOCTYPE, DOCDATE, VCHRNMBR, DOCNUMBR,
DOCAMNT, VOIDED, TRXSORCE, CHEKBKID, PSTGDATE, PYENTTYP,
CARDNAME
FROM PM20000
WHERE DOCTYPE in (5,6) AND DOCAMNT <> 0 AND VOIDED = 0) P
INNER JOIN PM00200 V -- vendor master
ON P.VENDORID = V.VENDORID
LEFT OUTER JOIN -- apply records
(SELECT VENDORID, VCHRNMBR, DOCTYPE, APTVCHNM, APTODCTY,
APTODCNM, APTODCDT, ApplyToGLPostDate, APPLDAMT
FROM PM10200
UNION
SELECT VENDORID, VCHRNMBR, DOCTYPE, APTVCHNM, APTODCTY,
APTODCNM, APTODCDT, ApplyToGLPostDate, APPLDAMT
FROM PM30300) PA
ON P.VCHRNMBR = PA.VCHRNMBR AND P.VENDORID = PA.VENDORID
AND P.DOCTYPE = PA.DOCTYPE
LEFT OUTER JOIN -- distributions
(SELECT VENDORID, VCHRNMBR, CNTRLTYP, DEBITAMT, CRDTAMNT,
DSTINDX, DISTTYPE, DistRef, PSTGDATE
FROM PM10100
WHERE PSTGSTUS = 1 AND CNTRLTYP = 0
UNION ALL
SELECT VENDORID, VCHRNMBR, CNTRLTYP, DEBITAMT, CRDTAMNT,
DSTINDX, DISTTYPE, DistRef, PSTGDATE
FROM PM30600 WHERE CNTRLTYP = 0) D
ON PA.VENDORID = D.VENDORID AND PA.APTVCHNM = D.VCHRNMBR
LEFT OUTER JOIN GL00105 G -- for account number
ON D.DSTINDX = G.ACTINDX
LEFT OUTER JOIN GL00100 G2 -- for account name
ON D.DSTINDX = G2.ACTINDX
LEFT OUTER JOIN -- apply to docs, added for due date
(SELECT DOCTYPE, VCHRNMBR, DUEDATE, TRXDSCRN
FROM PM30200
UNION
SELECT DOCTYPE, VCHRNMBR, DUEDATE, TRXDSCRN
FROM PM20000) AD
ON PA.APTODCTY = AD.DOCTYPE and PA.APTVCHNM = AD.VCHRNMBR
-Victoria
LikeLike
Victoria,
Thank you for this….as I just came on to search for an answer to the same issue. I implemented the updated view and I’m showing the CM records, however, their associated “Payment_Document_Number” is the invoice to which it was applied, and not the Check Number of the check where the invoice and credit memo were applied. Is there a fix for this?
Thanks!
LikeLike
Hi Shawn,
Technically, you cannot apply a check to a credit memo, as they are both credit documents. GP only allows you to apply a credit document (payment, credit memo, return) to a debit document (invoice, misc charge, fin charge). If you look up your credit memo in GP and look at what it’s applied to, you will see the invoices the view shows. If you look at the check in GP and look at what it’s applied to, you will not see credit memos.
-Victoria
LikeLike
Hi, technically on line 119, I think that you should be using the pa.doctype, not pa.vendorid. At my company, we get gp data from some of our clients and I have to put it together. The 30600 seems to have gl information for all the document types, but we only use it where the doctype is 1 (so we can see gl information for invoices). Your usage of vendor id on line 107 is likely redundant. The voucherno+doctype should be fine, and it’s doubtful that the vendor id can ever differ. When I’ve queried on different clients’ data to see if vnd_no differs in situations like this, it seems like it never does (although maybe it’s possible).
LikeLike
Jay,
I think you are right and linking on the Vendor ID is redundant. However, I remember adding that for a reason that I just cannot remember right now. I don’t think it hurts anything to leave it in there. 🙂
-Victoria
LikeLike
Victoria – this is a great report! Is there any way to add the distribution reference for Purchase Orders as well?
Erin
LikeLike
Hi Erin,
It should be possible, but would require some work, not just a simple change to the existing code. I will add this to my ‘wish list’ for future blog posts.
-Victoria
LikeLike
This is a great view…thanks for providing it. I’d like to add the distribution reference to the view as well. Would you please direct me to the tables I would need.
LikeLike
Hi Eric,
Good idea! I just updated the code to include it. I actually already had it in the code, just needed to add it to the ‘select’ section at the top.
-Victoria
LikeLike
I noticed that as well and changed it…i guess i posted that message too soon….thanks for the response though.
LikeLike
It’s all good. I am sure others would want that in the view, so now it’s updated for everyone. 🙂
-Victoria
LikeLike
Hey Victoria
I was wonder how i would add the PM30600.DSTSQNUM to this view.
Thanks
Vic
LikeLike
Hi Vic,
You can add the following to the end of lines 82, 108 and 113:
,DSTSQNUM
Let me know if you need additional help on this.
-Victoria
LikeLike
Thank you, I have been quietly following your posts and have also used a few of your views. The one I first started on was the AP Payment Apply details (what are the payments applied to) then I also used this one. I thought that this was the better report to fit our situation and I used it as a basis for an excel report and added the filter of taking out all the lines where Distribution Type was PAYABLE because I only wanted to see where the funds were spent and if the PAYABLE lines were there then it looked like you had paid twice the amount.
It was then that I noticed that the AP Payment Apply details resulting pivot table was different from the AP Payment Apply with GL Distributions. And when I viewed the transactions that created the differences, I found that they were transactions were the same GL code was used 2 or more times in the distribution panel (we sometimes break up the distribution into parts because we add more details into the description field in the distribution screen.
Is there a way to add this into the script? Hope you can help with this.
LikeLike
Hi Geraldine,
Are you looking to add the ‘distribution reference’? If so, the code is actually already in there, I am just not pulling it into the results. You can add the following on line 081 to see it:
Let me know if I misunderstood what you’re asking.
-Victoria
LikeLike
The listing only picks up one of many GL distribution lines that used the GL code. Imagine a distribution panel were there are 4 lines going to the FA clearing account and 3 other lines going to various other GL accounts. The SQL only picks up one line out of the 4 that were coded to FA clearing and the 3 other lines. I think it picks up the first line that uses that GL and then forgets the other 3 then picks up the other 3 coded to other GL accounts so if I were to look at the PAYABLES distribution amount less all the PURCHASE distribution amounts I found that the payment looked unbalanced.
LikeLike
Victoria, forgot to add that in these occasions the additional lines were added after the PAYABLES line. So the panel would have one line that says PURCHASE, then the line for the AP control account then all the other lines would then come after them as PURCHASE lines. I didn’t know if this would impact the SQL as well.
LikeLike
Hi Geraldine,
That doesn’t sound right. Are you looking at the results from my original code or something that you have created from it/made changes to? To double check, I just tested this and am seeing the same number of lines in the results as there are distribution lines on the Apply To Document. The order of entries on the GL distribution window should not make any difference at all. 🙂 Can you please test this with my original code directly in SQL to confirm? If you’re not seeing the same thing I am seeing, please let me know and we’ll figure out a way to troubleshoot this for you.
Thanks,
-Victoria
LikeLike
Victoria, I used your original SQLs as I am just beginning to learn SQL myself. The only difference is when the view was created I then used that view in Excel Builder and added the filter that I only wanted PURCHASE distributions thereby removing the PAYABLES entries.
I hope you can help with this one.
LikeLike
Hi Geraldine,
If you have the view using my original code created in SQL, you can run the following code in SQL Server Management Studio to check one transaction at a time:
Please make sure to replace the Vendor ID and invoice numbers with yours. This should result in the same number of lines as you are seeing on the Payables Distributions Zoom window. Can you please check a few transactions and let me know what you find?
-Victoria
LikeLike
I used your new SQL to look into individual transactions which I had previously identified as the ones with the missing transaction lines, This SQL made it easy to see that the credits did not match the debits. The Payment functional amount remained as per the full payment amount but the applied to amount (which was the credit/PAYABLE) did not match the DEBITS which is the invoice distribution.
LikeLike
Hi Geraldine,
Are the transactions you’re looking at entered in anything other than your functional currency? If so, this view may not show all information as you are expecting because it does not take multicurrency into account at all.
I am a little confused at what you’re comparing through, from your description. The “applied to amount…did not match the debits” – there is no reason it has to. For example, if you partial pay something, the applied amount will be something completely different than the debits and credits on the invoice. And if you have more than one payment applied to an invoice, that invoice will show up in your results more than once. So you will need to find a unique combination of Payment_Voucher_Number and Apply_To_Voucher_Number.
However, I believe that no matter what, when you look at one transaction all the debits should still equal all the credits. Is that not the case? The ‘Debit’ and ‘Credit columns of my view when you choose a unique combination of Payment_Voucher_Number and Apply_To_Voucher_Number should show total up to the Functional amounts in the following query:
If you’re still having trouble with this, I would offer to look at your data together so we can talk specifics and not generalities. If you’d like to do this, let me know.
-Victoria
LikeLike
I ran the SQL you’ve given me to show that DR & CR match for the voucher and that one works. I know it would have because I went into GP to look into the distribution of the invoice and that one balances (no FX involved).
In this example, the payment was for $5000 which paid for 4 invoices so the payment functional amount is $5000 but of course when I used the script that was supposed to do it one by one the functional payment amount remained the same at $5000 but then for the other 3 invoices that worked the DR (all the P&L codes) and the CR (the invoice value/creditors control account) totalled something different but DR = CR. For the odd one out though:
Functional Payment Amount is $5000.00
Credit = 3593.83 (went to creditors control account)
Debit = 12.00 (GL 3-53600, recoverable other)
Debit = 115.47 (GL 3-53200 recoverable hotel)
Debit = 163.48 (GL 3-53100 recoverable transport domestic)
Debit = 3139.40 (GL 3-53100 recoverable transport domestic)
This totals $3430.35. the missing debit is 163.48 that went to GL 3-53100 recoverable transport domestic. The distribution panel had 3 entries going into 3-53100 (163.48 x 2 times and 3139.40) I’ve found that the majority of these differences were invoices were we had to split the costs into the same GL code 2 or more times for the same amount.
Does this make it clearer?
LikeLike
Hi Geraldine,
Yes! Thank you so much for the additional details – that helped tremendously. Needless to say I didn’t have any of these in my test data, but as soon as I entered a transaction with similar GL distributions I found the issue. What was happening was that I was returning all unique combinations of the distribution details. But in your case, there are two distributions details that are identical, which is perfectly legitimate, I was just not allowing for that. As crazy as this might sound, the fix is to simply change UNION to UNION ALL on line 111. 🙂 I have updated the code in my post to reflect this and gave you credit in the comments.
Thank you again for helping me track this down!
-Victoria
LikeLike
Thank you for the fix Victoria. I now just have to add a caveat to this SQL. This SQL must be used in conjunction with your SQL view_Payables_Apply_Detail because that view shows all the payments made to the creditor/supplier however if the user thinks that this view can supplant that view then that is not entirely correct. I would use the 2 to reconcile that the amounts given in this report matches the actual payments made.
The reason for the discrepancy is If the user regularly pays suppliers in part payments then this SQL will not work perfectly because the Applied Amount is the amount applied (multiplied several times by the number of the GL distribution lines) and the amount on the GL distribution lines are the actual GL distribution amounts and not the perceived balances paid out on that occasion.
Let me give you an example:
Invoice from Supplier A is $4500.00 broken down into 3 P&L accounts at $1500 each. Invoice is paid in 2 parts. Once as $4300 and the balance paid as $200.
The SQL will give you a result of :
Payment 1 – Applied Amount 4300.00 GL XXXX-XX Amount $1500.00
Payment 1 – Applied Amount 4300.00 GL YYYY-XX Amount $1500.00
Payment 1 – Applied Amount 4300.00 GL ZZZZ-XX Amount $1500.00
Payment 2 – Applied Amount 200.00 GL XXXX-XX Amount $1500.00
Payment 2 – Applied Amount 200.00 GL YYYY-XX Amount $1500.00
Payment 2 – Applied Amount 200.00 GL ZZZZ-XX Amount $1500.00
When I summarised the report by the logical column amount, I had inflated the amount paid to the supplier. I therefore reconciled the 2 views, found out the differences, and created an additional column called Final amount
Payment 1 – Applied Amount 4300.00 GL XXXX-XX Amount $1500.00 final amt $1500.00
Payment 1 – Applied Amount 4300.00 GL YYYY-XX Amount $1500.00 final amt $1500.00
Payment 1 – Applied Amount 4300.00 GL ZZZZ-XX Amount $1500.00 final amt $1300.00
Payment 2 – Applied Amount 200.00 GL XXXX-XX Amount $1500.00 final amt $ 0.00
Payment 2 – Applied Amount 200.00 GL YYYY-XX Amount $1500.00 final amt $ 0.00
Payment 2 – Applied Amount 200.00 GL ZZZZ-XX Amount $1500.00 final amt $ 200.00
I had just arbitrarily chosen one line to work the difference out of as long as you ensure that the final balance is correct. I don’t think GP or SQL can make that determination for you.
Hope I have saved someone else the worry of not having the amounts balance. The 2 views taken together work brilliantly though.
LikeLike
Hi Geraldine,
Thanks for the follow up. Interestingly, different people use this in a variety of different ways – you’ve showed us how you are using it. I originally developed this code for a customer that wanted to determine what portion of their vendor payments should go on their 1099’s (they were able to determine this by account number on the original invoice). Another customer of ours uses this to check whether invoices are getting properly coded, but only after they are paid. Yet another wanted the apply detail and only wanted to see the original GL distributions as ‘informational’ – they were just looking at them, not actually performing any calculations or reconciliations.
What I have found through many years of doing reporting work is that very seldom will the same report work for multiple companies (or even users in the same company!) without at least some tweaking. So, understanding that, my intention with all the code I post is more to give examples on how something can be done than give someone the complete finished product. This way you at least do not have to start from scratch and the table links are something that many people have trouble with, so I think that is nice to have. A lot of times when working on new reports I copy just the table linking sections from my own blog. 🙂
Have a great weekend!
-Victoria
LikeLike
Hi Victoria,
Happy New Year!!!
I am using this view for AP Invoice accruals which is a huge time saver. Basically, we had to close 2011 AP on 1/3, which is not nearly enough time to get everything for 2011 entered into AP. I had my AP Admin code any 2011 invoices in the reference field with an “A” for Accrual, since she booked them in January. Using Select Expert I isolated those transactions in the view above by date and those coded with “A” and then exported the report into Excel, link that sheet to a General Journal upload sheet, and then use IM to import the entries, change them to Reversing and accruals are all done!
However…
There is a slight problem, one I hope you have a solution for, with regard to Intercompany AP transactions. The View picks up the transactions from the Source company, but doesn’t pick up the other side of the transactions in the Destination company. That is understandable since it doesn’t go through AP in the Destination company.
So the question to you is, is there a way the View could pick up the other side (DestinationCompamy) transactions too
LikeLike
Hi Gordon,
Good question. This would take quite a bit of additional coding. I am thinking you could do this with the following logic:
-Victoria
LikeLike
Would anyone know how I would create a view to identify a payables or purchasing journal which posted in the sub-ledger and, before it could be posted in the GL, was deleted? This is also throwing our Payables control account out-of-balance, but our user does not remember which payables./purchasing batch was accidentally deleted. I’ve gone as far as using the view above, which is a keeper for transactions posting from payables and not posted using the control account, and running a SmartList from Account Transactions to find where any of our Originating Trx Source journals are out of numerical order, but it’s not getting me to the original batch as I had hoped it would.
Thanks,
Kristie
LikeLike
Kristie,
Try something like this:
select P.* from
(select VENDORID, DOCTYPE, DOCDATE, VCHRNMBR,
DOCNUMBR, DOCAMNT,VOIDED, TRXSORCE, CHEKBKID,
PSTGDATE, PYENTTYP, CARDNAME
from PM30200
union
select VENDORID, DOCTYPE, DOCDATE, VCHRNMBR,
DOCNUMBR, DOCAMNT, VOIDED, TRXSORCE, CHEKBKID,
PSTGDATE, PYENTTYP, CARDNAME
from PM20000) P
where P.TRXSORCE not in
(select ORTRXSRC from GL30000
union
select ORTRXSRC from GL20000)
-Victoria
LikeLike
Hi Victoria,
Using the view above I want to tie back to the GL tables to get journal information.
This is the join I have added, but am not sure this is tying back correctly, is this join from GL20000 and GL30000 back to the payment correct?
Select g.ORMSTRID, g.ORDOCNUM, g.ORCTRNUM, g.TRXSORCE, g.JRNENTRY, g.CRDTAMNT, g.DEBITAMT,
p.DOCNUMBR, p.DOCAMNT–, PA.APTODCNM, D.DEBITAMT, D.CRDTAMNT
FROM (SELECT JRNENTRY, TRXDATE, TRXSORCE, ACTINDX, ORCTRNUM, ORMSTRID, ORDOCNUM, CRDTAMNT, DEBITAMT, ORTRXSRC, ORMSTRNM, OrigSeqNum, DSCRIPTN
FROM GL20000 WITH (NOLOCK)
UNION ALL
— all historical trx distributions
SELECT JRNENTRY, TRXDATE, TRXSORCE, ACTINDX, ORCTRNUM, ORMSTRID, ORDOCNUM, CRDTAMNT, DEBITAMT, ORTRXSRC, ORMSTRNM, OrigSeqNum, DSCRIPTN
FROM GL30000 WITH (NOLOCK) ) G
JOIN (SELECT VENDORID, DOCTYPE, DOCDATE, VCHRNMBR, DOCNUMBR, DOCAMNT,VOIDED, TRXSORCE, CHEKBKID, PSTGDATE, PYENTTYP, CARDNAME
FROM PM30200 WITH (NOLOCK)
UNION
SELECT VENDORID, DOCTYPE, DOCDATE, VCHRNMBR, DOCNUMBR, DOCAMNT,VOIDED, TRXSORCE, CHEKBKID, PSTGDATE, PYENTTYP, CARDNAME
FROM PM20000 WITH (NOLOCK)) P ON g.ORMSTRID=p.VENDORID AND g.ORDOCNUM=p.DOCNUMBR
Thanks
Sue
LikeLike
Hi Sue,
Recurring transactions in GP can have the same document number, so I think this will be better if you add the following to the end:
AND g.ORCTRNUM = p.VCHRNMBR-Victoria
LikeLike
Victoria,
This was awesome! I selected from the view “where distribution_type = ‘Payable’ and gl_account_number ‘21000-43-000′” (our AP account) and quickly found the transaction that was causing our AP to not reconcile to GL. I spent about an hour or more looking other ways (GJs to the account, Reconcile to GL tool) but once I found your view, I found the error in seconds!!
Thanks very much!!
LikeLike
Thanks Victoria, another gem. I was just going to have to do the same thing myself. This is wonderful!!!
Chuck
LikeLike