SQL view for all posted Payables transactions in Dynamics GP


Payables Transactions have been a popular request for reports lately. Below is a view that lists all posted Payables transactions in functional currency. Voided transactions are not excluded.

For other SQL views on Dynamics GP data, please visit my GP Reports page.

~~~~~

CREATE VIEW view_PM_Transactions
AS

/***************************************************************
view_PM_Transactions
Created Oct 12 2009 by Victoria Yudin - Flexible Solutions, Inc.
For updates see https://victoriayudin.com/gp-reports/
Shows all posted Payables transactions with Functional amounts.
Does not exclude voided transactions.
Tables used:
	PM00200 - Vendor Master
	PM20000 - Open/Posted Transactions
	PM30200 - Historical/Paid Transactions
***************************************************************/

SELECT	P.VENDORID Vendor_ID,
	V.VENDNAME Vendor_Name,
	P.VCHRNMBR Voucher,
	CASE P.DOCTYPE
	     WHEN 1 THEN 'Invoice'
	     WHEN 2 THEN 'Finance Charge'
     	     WHEN 3 THEN 'Misc Charge'
     	     WHEN 4 THEN 'Return'
     	     WHEN 5 THEN 'Credit Memo'
	     WHEN 6 THEN 'Payment'
	     END Document_Type,
	P.DOCDATE Document_Date,
	P.PSTGDATE GL_Posting_Date,
	P.DUEDATE Due_Date,
	P.DOCNUMBR Document_Number,
	P.DOCAMNT Document_Amount,
	P.CURTRXAM Unapplied_Amount,
	P.TRXDSCRN [Description],
	CASE P.VOIDED
	     WHEN 0 THEN 'No'
	     WHEN 1 THEN 'Yes'
	     END Voided

FROM	(SELECT VENDORID, VCHRNMBR, DOCTYPE, DOCDATE, PSTGDATE,
	 DUEDATE, DOCNUMBR, DOCAMNT, CURTRXAM, TRXDSCRN, VOIDED
	 FROM PM20000
	UNION ALL
	 SELECT VENDORID, VCHRNMBR, DOCTYPE, DOCDATE, PSTGDATE,
	 DUEDATE, DOCNUMBR, DOCAMNT, CURTRXAM, TRXDSCRN, VOIDED
	 FROM PM30200) P

     INNER JOIN
	PM00200 V
	ON V.VENDORID = P.VENDORID

/** the following will grant permissions to this view to DYNGRP,
leave this section off if you do not want to grant permissions **/
GO
GRANT SELECT ON view_PM_Transactions 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 all posted Payables transactions in Dynamics GP”

  1. I am looking to find the date that an invoice was actually entered into the system. I see a Posteddt field in both the PM20000 table and the PM30200 table, but when I try to validate these do not seem like the correct field. Where might I find the date entered?
    Thanks in advance

    Like

    • Hi Kelli,

      POSTEDDT will be the date an invoice is posted, not when it’s entered. There is no date that will definitively store the original date an invoice was entered in the PM20000 and PM30200 tables. If this is important, you could create a SQL trigger and a custom table to track this.

      -Victoria

      Like

  2. Hi Victoria – I love this view. How can I include PO Receipt transactions? Ideally, I’d like this view to allow for: POP Type, Vendor Doc #, Receipt Date, Amount, Vendor Name & ID, Currency ID, POP Receipt #, Batch Number, Line description (not the transaction’s reference at the header)…in addition to what you’re already providing above. I’m just not good with when to use inner or outter joins, so hoping you can guide me.

    Like

    • Hi Kristie,

      What you’re asking for is not necessarily so easy, as there can be multiple POP receipts for each payables invoice. And each receipt can have multiple line items. What is the goal of the view? How will you be using it?

      -Victoria

      Like

      • We plan to use it to confirm data entry. I’ve constructed a SmartList query for the group using the data available in the general ledger, but as you know, this data (using Finanical >> Account Transactions sub-folder) does not include the invoice’s date and for the receipts, you get the receipt number but not the corresponding invoice number. The group’s ultimate goal is to be able to reconcile these entries back to another internal application.

        Like

        • Hi Kristie,

          Thanks for the additional detail. This is probably doable, but not easily with this view. Since you’re looking in the General Ledger, I would possibly start with that and link back to the POP and Payables tables from there. Maybe start with my Posted GL Transactions view, filter it on POP and PM transactions and then link back to the appropriate tables depending on the transaction type.

          This is probably not something I would be posting on my blog… If you’re interested in having this created for you as a consulting project (or working together to learn how to do this), let me know.

          -Victoria

          Like

  3. Hi Victoria,

    How would I join in GL00105 or another table that has the GL Account Number(ACTNUMST) with PM20000 or PM 30200?

    Like

  4. I need to identify all vendors that have had no activity in the last two years.
    Can I use your “view_PM_Transactions” view as a place to start?

    Like

    • Hi David,

      Yes, that can be a good start. If you’re using the POP module, you may also want to check the POP10100 table for any new POs for vendors.

      -Victoria

      Like

      • Actually I went with some other code you posted a few years ago.

        “…So if you want the last payment date, regardless of the type of payment, and assuming you’re only looking at posted payments, you could use the following code:…”

        SELECT VENDORID AS [Vendor_ID], max(DOCDATE) AS [Last_Payment]
        FROM (SELECT VENDORID,DOCDATE FROM PM30200 –historical trx
        — only look at payments and ignore voids 6 = Payment
        WHERE PM30200.DOCTYPE = 6 AND PM30200.VOIDED = ‘0’
        UNION
        SELECT VENDORID ,DOCDATE FROM PM20000 –posted but open trx
        — only look at payments and ignore voids
        WHERE DOCTYPE = 6 AND VOIDED = ‘0’
        ) a
        GROUP BY VENDORID
        ORDER BY [Last_Payment]

        Do you think I still need to check POP also? Hmmm, how would I add that to the above? Or do I need to with the posted payment assumption above?

        Like

        • David,

          I don’t know if I would look at payments only. You might have a vendor just set up last month that you have invoices for, but no payments yet. Similarly, you may have a new vendor that you just issued a PO to, but do not have invoices in payables yet.

          Try something like the following instead:

          SELECT VENDORID [Vendor ID], 
          max(DOCDATE) [Last Trx or PO]
          FROM 
          (SELECT VENDORID, DOCDATE 
           FROM PM30200 
           WHERE VOIDED = 0
           UNION
           SELECT VENDORID, DOCDATE 
           FROM PM20000
           WHERE VOIDED = 0
           UNION
           SELECT VENDORID, DOCDATE 
           FROM POP10100 ) a
          GROUP BY VENDORID
          ORDER BY max(DOCDATE)
          

          -Victoria

          Like

          • I think that’s got it. I put it together with vendor Info and will let the powers that be decide the cut-off point and inactivate the oldies.

            Thanks!

            Like

  5. Can some one help me with the following issue. I posted Inv with PO attached to accrued purchases. I forgot to take a discount and voided the payable. When I tryed to readd the payable I could not fimd my PO number. Is there any way to readd or reopen that PO and book the AP through Accrued Purchases account?
    Thanks for all the help

    Like

    • Elena,

      I am not aware of any way to do this. You would need to re-enter a manual Payables invoice at this point, you can manually type in the PO number, but it will not be ‘linked’ to the actual PO or receipt.

      -Victoria

      Like

  6. hi Victoria,

    I’m trying enter a manual payment entry. When i select the vendor which has a currency of GBP the Apply button is grayed out. The functional current for this company is USD. Any help would be greatly appreciated. Thanks.

    Like

    • Kevin,

      This is because of the different currencies. You need to post the payment first, then you will be able to apply it as a separate step.

      -Victoria

      Like

      • Thanks Victoria!! But pardon for my questions (i’m still a rookie) I would post the entry in the manual payment entry screen but where do i apply the entry? and how? Thanks again

        Like

        • Kevin,

          Transactions > Purchasing > Apply Payables Documents. You can click on the Help button on the window to get specific instructions once there.

          -Victoria

          Like

          • Many thanks again!! I have another questions regarding Bank transfers. I’m encountering the following error message when i attempt to transfer funds from one checkbook to another. “You do not have authorization to perform transaction posting”. I’ve checked the users account and he has access to all posting options. Both checkbooks are the same currency which is the functional currency. Not sure where the problem lies. Thanks again.

            Like

            • Kevin,

              Have you determined that this is a GP user specific issue? Security is completely different depending on what version of GP you are using, so it is hard to give you a generic test. If you are on GP 10.0 or 2010, can you log into GP as a user with the POWERUSER role and see if that user can post a Bank Transfer?

              -Victoria

              Like

              • Hi Victoria,

                I’ve checked his security settings and everything is fine. He should be able to perform bank transfers since that option is checked under his security settings. Is there anything else outside of the security settings that can cause this? Thanks.

                Like

                • Kevin,

                  I think you may have misunderstood what I am asking. Do you have another GP user that can perform a bank transfer with no error? Also, what version of GP are you using?

                  -Victoria

                  Like

                  • Yes, others are able to perform bank transfers with no issues. We are on GP V8.00g19.

                    Like

                    • Ok, then next step is to compare settings from a user than can post a bank transfer to the one that cannot – what are the differences?

                      -Victoria

                      Like

                    • There are no differences. Which is why it’s so weird.

                      Like

                    • Kevin,

                      Are you checking ALL the settings or just the settings for posting Bank Transfers? If you are using Advanced Security, you should have an option to copy security settings from one user to another (it’s there in GP 9.0, I am pretty sure it was there in 8.0)…as a test, you could create a new user, copy all the security settings from the user without the issue and test – if it works, copy all the settings from the user that is getting the error (make sure to check ‘revert security’ when copying). If it now does not work, you know there is a difference. It might be some other related permission that is needed…

                      -Victoria

                      Like

  7. Thank you very much Victoria!

    Like

  8. Hello again Victoria

    I was wondering if it was at all possible to update this view with the originating currency information please? This view is perfect for my clients request to create a check payment document – however, we are always reporting in Originating & Functional currency. I’ve noted the fields for the multicurrency reporting purposes below:

    1. Currency ID
    2. Exchange rate
    3. Originating Check amount
    4. Originating applied amount
    5. Checkbook ID

    Thank you & regards

    Like

    • Hi Lulu,

      Adding multicurrency details to this is quite involved and will bring up a number of additional issues/questions to be resolved. This is not something I am planning on posting on my blog, at least in the foreseeable future, however, it may be something we can create as a consulting project. Please let me know if this is something you would be interested in.

      -Victoria

      Like

  9. Victoria,

    This is a WONDERFUL website – thank you sooooooooooooo much. I’m bookmarking it!!!!

    Lisa

    Like

  10. Victoria,
    In your case for the PYENTTYP you have 3 for EFT. I am working on doing a custom app for our company to write checks and process EFT. So if I just put in the PYENTTYP = 3 will the system automatically pick it up as an EFT? Or is there more I will need to do to get the system to recognize 3 as EFT?

    Like

  11. Hi Victoria,
    Thanks for all the great GP reports and info. This site is amazing!
    I am new to GP, and just started working with Smartlist Builder and SQL Views a couple weeks ago. My company needs a clean downloadable AP aging and I have just about got it right, except I am finding it difficult to create a total for the aging buckets.
    I have created a view, originally because I wanted to create an aging date that would be the docdate if the open item wasn’t an invoice, and the due date if it was an invoice so that I could then use the aging date to calculate which bucket the open invoice belonged to.
    Then I created 5 buckets for the aging days and have the invoice amts totalling correctly in those buckets.

    Now I just want to create a total for all the buckets and SLB doesn’t allow you to use calculated fields inside calculated fields. And I can’t figure out an easy way to total the buckets in the view I created because it doesn’t seem to allow me to use a newly created field either.
    Here’s my view and a couple of the bucket fields. I would really appreciate your help with creating a total for all the bucket fields.

    Thanks,
    Laura

    CREATE VIEW [LAW_payables_aging_date] AS
    SELECT
    CASE DOCTYPE
    WHEN 1 THEN DUEDATE
    WHEN 2 THEN DOCDATE
    WHEN 3 THEN DOCDATE
    WHEN 4 THEN DOCDATE
    WHEN 5 THEN DOCDATE
    WHEN 6 THEN DOCDATE
    ELSE DOCDATE
    END Aging_Date,
    VCHRNMBR,DOCTYPE,DOCDATE,DOCNUMBR,DUEDATE,
    PORDNMBR,WROFAMNT,VOIDED,DOCAMNT, T1.VENDORID, VENDNAME
    from dbo.PM20000 T1
    INNER JOIN dbo.PM00200 T2
    on
    T2.[VENDORID] = T1.[VENDORID]

    2 of the BUCKETS which were created in calculations in SLB:

    CURRENT BUCKET

    CASE
    WHEN (DATEDIFF ( day , {*LAW_payables_aging_date:Aging Date} , GETDATE() ) < '0' and
    {*LAW_payables_aging_date:Document Type} = 5) THEN ({*LAW_payables_aging_date:Document Amount} * -1)
    WHEN (DATEDIFF ( day , {*LAW_payables_aging_date:Aging Date} , GETDATE() ) ‘0’ and DATEDIFF ( day , {*LAW_payables_aging_date:Aging Date}, GETDATE() ) ‘0’ and DATEDIFF ( day , {*LAW_payables_aging_date:Aging Date}, GETDATE() ) < '31') and
    {*LAW_payables_aging_date:Document Type} = 1 THEN
    {*LAW_payables_aging_date:Document Amount}
    ELSE NULL
    END

    I want to add the current bucket to 1 – 30 days and 31-60 days and 61-90 days, etc.

    Like

    • Laura,

      Thanks for the kind words!

      I am not sure I follow – are you looking to add a column with a total of all the buckets? If so, that should just be the Current Trx Amount (CURTRXAM), why add the calculations? Also, I would suggest 2 changes to your code:

      1. Use Current Trx Amount (CURTRXAM) instead of the Document Amount – anything partially paid will have the amount still due in the CURTRXAM, using the Document amount will overstate your payables.
      2. Change the sign on the ‘credit’ transactions (DOCTYPE 4, 5 and 6). GP stores everything in payables as a positive number. If you have open payments, credit memos or returns, then they will show up as positive numbers and overstate your payables.

      Hope that helps. Please write back if you need additional help.

      -Victoria

      Like

Trackbacks/Pingbacks

  1. DynamicAccounting.net | SQL View for All Posted Payables TransactionsSQL View for All Posted Payables Transactions - DynamicAccounting.net - April 25, 2016

    […] Posted on October 13, 2009 by Mark Polino Victoria Yudin has published a new SQL view to show all posted Payables transactions in Dynamics GP. […]

    Like

  2. SQL View for All Posted Payables Transactions - DynamicAccounting.net - October 13, 2009

    […] View for All Posted Payables Transactions Victoria Yudin has published a new SQL view to show all posted Payables transactions in Dynamics GP. Published: Tuesday, October 13, 2009, 06:30 […]

    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: