SQL view for Payables apply detail and GL distributions in Dynamics GP


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.

37 Responses to “SQL view for Payables apply detail and GL distributions in Dynamics GP”

  1. 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.

    Like

    • 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

      Like

      • 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!

        Like

        • 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

          Like

  2. 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).

    Like

    • 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

      Like

  3. Victoria – this is a great report! Is there any way to add the distribution reference for Purchase Orders as well?

    Erin

    Like

  4. 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.

    Like

  5. Hey Victoria

    I was wonder how i would add the PM30600.DSTSQNUM to this view.

    Thanks
    Vic

    Like

  6. 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.

    Like

    • 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:

      ,DistRef
      

      Let me know if I misunderstood what you’re asking.

      -Victoria

      Like

      • 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.

        Like

        • 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.

          Like

          • 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

            Like

            • 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.

              Like

              • 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:

                select * from  view_Payables_Apply_and_GL_Dist 
                where Vendor_ID = 'Vendor_ID' --replace with your Vendor ID
                and Apply_To_Doc_Number = 'Invoice_No'  --replace with your Invoice #
                

                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

                Like

                • 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.

                  Like

                  • 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:

                    select SUM(DEBITAMT) Functional_Debits,
                    SUM(CRDTAMNT) Functional_Credits,
                    SUM(ORDBTAMT) Originating_Debits,
                    SUM(ORCRDAMT) Originating_Credits
                    from PM30600 
                    where VCHRNMBR = 'Apply_To_Voucher_Number' --replace with yours
                    

                    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

                    Like

                    • 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?

                      Like

                    • 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

                      Like

                    • 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.

                      Like

                    • 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

                      Like

  7. 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

    Like

    • Hi Gordon,

      Good question. This would take quite a bit of additional coding. I am thinking you could do this with the following logic:

      • Link from the payables transaction to the GL transaction (in the originating company first) to get the TRXSORCE.
      • Link to the GL of the destination companies – from looking at the tables I believe you can link the TRXSORCE in the originating company to the ORGNTSRC in the destination companies.
      • Link to the GL account names/numbers in the destination companies.

      -Victoria

      Like

  8. 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

    Like

    • 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

      Like

  9. 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

    Like

  10. 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!!

    Like

  11. Thanks Victoria, another gem. I was just going to have to do the same thing myself. This is wonderful!!!

    Chuck

    Like

Trackbacks/Pingbacks

  1. DynamicAccounting.net | SQL view for Payables apply detail and GL distributions in Dynamics GPSQL view for Payables apply detail and GL distributions in Dynamics GP - DynamicAccounting.net - April 29, 2016

    […] Victoria Yudin gives us a new SQL view for Payables apply detail and GL distributions in Dynamics GP […]

    Like

  2. DynamicAccounting.net - September 27, 2010

    SQL view for Payables apply detail and GL distributions in Dynamics GP…

    Victoria Yudin gives us a new SQL view for Payables apply detail and GL distributions in Dynamics GP…

    Like

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: