i found you!

SQL view to show AP apply information in GP


Here is a corollary to the view I posted last week. The view below will show you what payments/credit memos were applied to payables transactions in GP. If you have SmartList Builder you can easily incorporate this SQL view into a new SmartList.  [Update: You can also use this in Crystal with the changes I made on May 27, 2009.]

More Dynamics GP Payables SQL views are available on my Payables SQL Views page.
For additional Dynamics GP code, please check out my GP Reports page.

create view view_AP_Apply
as

-- ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~
-- view_AP_Apply
-- Created on Oct 10 2008 by Victoria Yudin, Flexible Solutions
-- For updates see http://victoriayudin.com/gp-reports/
-- Does not take Multicurrency into account
-- Will return multiple lines for transactions applied to by
--    more than one check, credit memo or return
-- Updated on Jan 6 2009 to include Vendor Name
-- Updated on Feb 20 2009 to include trx not fully applied
-- Updated on Mar 20 2009 to include Payment Date
-- Updated on Mar 30 2009 to include Apply Date
-- Updated on Apr 28 2009 to include Apply GL Posting Date
-- Updated on May 27 2009 to add Payment Status, take space
--     out of column names and eliminate duplicates for
--     payment that are not fully applied
-- Updated on Jan 29 2014 to add 1099 columns
-- ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~

select
P.VENDORID Vendor_ID,
PM.VENDNAME Vendor_Name,
case P.DOCTYPE
   when 1 then 'Invoice'
   when 2 then 'Finance Charge'
   when 3 then 'Misc Charge'
   end Document_Type,
P.DOCDATE Document_Date,
P.VCHRNMBR Voucher_Number,
P.DOCNUMBR Document_Number,
P.DOCAMNT Document_Amount,
P.TEN99AMNT [1099_Amount], 
P.TEN99BOXNUMBER [1099_Box], 
case P.TEN99TYPE 
   when 1 then 'Not a 1099 Vendor'
   when 2 then 'Divident'
   when 3 then 'Interest'
   when 4 then 'Miscellaneous'
   when 5 then 'Withholding'
   else ''
   end [1099_Type],
coalesce(PA.APFRMAPLYAMT,0) Applied_Amount,
coalesce(PA.VCHRNMBR,'') Payment_Voucher_Number,
coalesce(P2.DOCNUMBR,'') Payment_Doc_Number,
coalesce(P2.DOCDATE,'1/1/1900') Payment_Date,
coalesce(PA.DATE1,'1/1/1900') Apply_Date,
coalesce(PA.GLPOSTDT, '1/1/1900') Apply_GL_Posting_Date,
case PA.DOCTYPE
   when 4 then 'Return'
   when 5 then 'Credit Memo'
   when 6 then 'Payment'
   else ''
   end Payment_Type,
coalesce(PA.POSTED, 'Unpaid') Payment_Status
 
from
 (select VENDORID, DOCTYPE, DOCDATE, VCHRNMBR, DOCNUMBR,
  DOCAMNT, VOIDED, TEN99AMNT, TEN99BOXNUMBER, TEN99TYPE
  from PM30200
 union all
  select VENDORID, DOCTYPE, DOCDATE, VCHRNMBR, DOCNUMBR,
  DOCAMNT, VOIDED, TEN99AMNT, TEN99BOXNUMBER, TEN99TYPE
  from PM20000) P
 
left outer join
 (select VENDORID, APTVCHNM, APTODCTY, APFRDCNM, DOCTYPE,
  DOCDATE, APFRMAPLYAMT, VCHRNMBR, DATE1, GLPOSTDT,
  case POSTED when 0 then 'Unposted' else 'Posted' end POSTED
  from PM10200
 union
  select VENDORID, APTVCHNM, APTODCTY, APFRDCNM, DOCTYPE,
  DOCDATE, APFRMAPLYAMT, VCHRNMBR, DATE1, GLPOSTDT,
  'Posted' POSTED
  from PM30300) PA
    on P.VCHRNMBR = PA.APTVCHNM and P.VENDORID = PA.VENDORID
    and P.DOCTYPE = PA.APTODCTY
 
left outer join
 (select VCHRNMBR, DOCTYPE, DOCNUMBR, DOCDATE
  from PM20000
 union all
  select VCHRNMBR, DOCTYPE, DOCNUMBR, DOCDATE
  from PM30200) P2
    on P2.VCHRNMBR = PA.VCHRNMBR and P2.DOCTYPE = PA.DOCTYPE
 
left outer join PM00200 PM
    on P.VENDORID = PM.VENDORID
 
where P.DOCTYPE in (1,2,3) and P.VOIDED = 0

-- add permissions for DYNGRP
go
grant select on view_AP_Apply 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.
 
 
 

63 Responses to “SQL view to show AP apply information in GP”

  1. Hi Victoria,
    There’s a manual payment transaction that has been applied to an invoice
    but when the payables transaction report is printed,
    the apply information of the payment to the invoice does not show. The invoice that has been applied has an “OPEN” status.
    What could be the reason? Further, is there any way, through SQL, that the apply information of a certain invoice can be viewed?
    Thanks!

    Like

  2. Hi Victoria,
    There’s a manual payment transaction that has been applied to an invoice
    but when the payables transaction report is printed, the apply information of the payment to the invoice does not show. What could be the reason? Further,the invoice that has been applied has an “OPEN” status.
    Another question, is there any way, through SQL, that the apply information of a certain invoice can be viewed?
    Thanks!

    Like

    • Hi Kim,

      Unless something goes wrong during the payment entry or apply process, an invoice with a status of “OPEN” is not fully applied.

      If you go to Inquiry | Purchasing | Transaction by Vendor and pull up the invoice, then click on it to highlight it and click the Amount Remaining hyperlink, does it show the manual payment? Also, what is the Amount Remaining shown for the invoice?

      To your other question – this view is one of the ways to view the apply information in SQL. If the invoice is not showing in the results when you run this, then it does not have any payments or credits applied to it.

      -Victoria

      Like

  3. Victoria,
    Your script is great but I’m trying to create a report that shows all documents on a check and their related expense accounts for a specific check run. I’ve got all documents (including credits and returns applied) captured except invoices that have been fully applied by credit memos or returns. They do not appear in the PM30300, the apply table.

    Any thoughts?

    Like

    • Hi Dave,

      Technically, an invoice that is fully applied by a credit memo or a return really has nothing to do with a check run. If you’re seeing these on the check stub, it’s because you have a setting in the payables module enabled to show all records applied since the last check run. Once printed on the check stub, this information is no longer stored by GP. :-( At least until version 2013.

      Reprinting the remittance/check stub exactly how it appeared has been a very popular request on many forums, so in GP 2013 there is new functionality added to reprint a payables check stub/remittance and show not only what invoices were paid, but also what credits were applied and shown on the original check stub. I have not experimented with this yet, so I would recommend some testing, but by the description it sounds like this will address what you’re looking to do. An important note on this is that you cannot go backwards – this will only work for new checks printed with GP 2013.

      Hope this helps,
      -Victoria

      Like

  4. I founded it Victoria. no need to respond. By the way bought your book when it first came out and loved it. Good job.

    Like

  5. Victoria is there a way to tell how an invoice was paid? Your view just shows Payment but I need to know if it was a cheque, cash, EFT, etc.

    Thanks.

    Like

  6. At the risk of sounding totally without a clue (which I am), why is there an “Apply” table for Open Transactions? Is this for batches that haven’t been posted yet or are otherwise still in some stage of transition? Our’s (PM10200) has only one record in it at the moment. For most analysis work, could I just join the PM30300 to the GL Transactions (GL20000) to match cash expenditures withe their associated GL coding?

    Like

    • Hi Jeanette,

      First a little terminology for payables transactions:

      • Work = unposted
      • Open = posted but not fully applied/paid
      • History = posted and fully applied/paid

      As soon as a payables transaction is fully applied/paid, it moves to History. The PM30300 table only has apply information for historical transactions. Any apply information for transactions that are not fully applied (open) or are unposted (work) will be in the PM10200 table.

      While right now in your company there may only be one partially applied transaction, that does not mean this will always be the case. So to make sure that you are reporting on all your data, I would recommend including the PM10200 table, as I show in this view. If you want to exclude unposted apply transactions you can check the POSTED field in PM10200 (0 = Unposted).

      Hope that helps,
      -Victoria

      Like

  7. Victoria,

    I used this sql view a while back and added it to each company with no issue. Now, we’ve upgraded to GP 2010, and for some reason, I cannot see the option under one of our newer companies. I see it under all of the other companies. I’ve even tried adding it again (repaeating the steaps in SQL Mgmt Studio, but I cannot add it in security. I am logged in as SA. Is there something that you know of that is causing this now that we are on aGP 2010> I love these SQL views and want to add more that you have shared. Thanks.

    Like

    • Hi Julie,

      As a first step, in SQL Server Management Studio, can you check that all of your company databases have the view? You can simply run the following statement against each of your databases to both confirm this and also make sure you have granted SQL permissions to it:

      GRANT SELECT ON view_AP_Apply TO DYNGRP

      Any company that does not have the view will come back with an error. For any companies that do not have the view, please create it with the script on this page.

      Once that is done, go into SmartList Builder SQL Security and make sure that each of your companies has this view marked.

      If you’re still having a problem, please write back with more details.

      -Victoria

      Like

  8. Hi Victoria
    is there any sql script available to check if i have duplicate vendors or is there any script to check if payment was duplicated

    Regards
    Logan

    Like

    • Logan,

      I don’t have anything like that posted. Part of the problem with writing something generic for what you are asking is that the definition or criteria for duplicates may vary by company. First step in identifying duplicates would be determining the logic for what would constitute a duplicate for you.

      -Victoria

      Like

  9. Hello Victoria,

    I am trying get see not only applied payments but also applied CM,
    I added last line to your applied pymts scripts but it is still only returining invoices.
    CASE PA.APTODCTY
    WHEN 1 THEN ‘Invoice’
    WHEN 2 THEN ‘Finance Charge’
    WHEN 3 THEN ‘Misc Charge’
    WHEN 5 THEN ‘Credit Memo’

    Additionally I do not want to see any cks taht had been voided, can you please tell me how to do this?

    Thanks!

    Like

    • Tami,

      Your change will not work because you cannot apply from a payment to a credit memo in GP, as they are both ‘credit’ transactions. GP only allows you to apply FROM a ‘credit’ transaction (payment, credit memo, return) TO a ‘debit’ transaction (invoice, finance charge, misc charge). They way I have set up this view, the credit transactions will be listed as ‘payments’, so you should see Credit Memo in the Payment_Type column without having to make any changes to my original code. Voided checks should not be showing up in the results, since they are not applied to anything and I am only showing applied credit transactions. Please let me know if you are seeing voided checks in your results and I will double check my code.

      -Victoria

      Like

  10. Hello, Victoria,
    Thanks for the view. I replaced APFRMAPLYAMT with ActualApplyToAmount for payment to display the orginal apply amount in multi currency (Z-NZD). It works fine.
    1. I need the same for Document amount of invoices. I can not.
    2. I need Originating Currecy ( CURNCYID or FROMCURR ) as column for invoice appy amount

    Appriciate your help.

    – Siddeek -

    Like

    • Hi Siddeek,

      Adding Multicurrency information to this requires a lot more work and linking in at least one additional table (MC020103) for the multicurrency amounts. 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

      • Hi, Vic,
        Thanks for the tip. This is good idea for future. Actually, I just want to create a smart list by smart list builder with originating invoice amount in a column by removing currency symbol and add as a column for currency using the tables in your view.

        – Siddeek

        Like

  11. Victoria,

    I am an auditor for a company that uses great plains. I need a listing of all invoices that were open as of a certain date (the audit date). This total should equal the total in the accounts payable account on there G/L. Currently it does not. Will this report help them? And how do they create it.

    Like

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

      • Thanks your reply Victoria.
        Unfortunately right now I don’t have the budget to consider a consulting project. I’ll have an in-depth look at my requirements again and what I can do about the multicurrency transactions.

        I am using SQL for some other reporting so already that’s been a big help :-)

        Like

        • Hello Victoria

          We’ve been using your view to create the one for our multicurrency payments requirements. We’re getting there, slowly!

          I noticed that your view does not include the currency ID field. I understand why you did it – it wasn’t written for multicurrency purposes – but I thought I’d let you know that it is still a nice field to have since I’ve been working with it somewhat :-)

          Take care

          Like

  13. Hi Victoria,

    Thanks for posting this view. I always find your posts very helpful. Now I have a question. Do you know if it’s possible in either Report Writer or in a SQL view to get all the invoices applied to a particular check into one row? For instance, if one check paid three invoices, we want just one row for that check showing separate columns for each invoice number and amount paid.

    Like

    • Hi Deborah,

      Thanks for the kind words!

      I believe this can be done in SQL, but not without some serious effort. If you knew upfront how many columns you would need for the apply information, this might be a little easier, otherwise, you would have to build the columns dynamically in your code which is pretty difficult. If it can be done in SQL it is possible that you could then use the results in Report Writer, but I think that would add yet another level of complexity. Creating something like this in RW would probably force you to either concatenate the apply data into one field or force a maximum allowed. Either way not pretty.

      -Victoria

      Like

  14. Hi,

    Per this conversation about credit in GP. I understand if you print checks the applied documents can appear on the stub. What happens if you pay electronically the vendor? how would they know???

    Like

    • JHM,

      If you are paying the vendor electronically and they do not already know about the credits, then you would have to find some way of letting them know. The method of doing that will depend greatly on your exact work flow – I would recommend talking to your GP Partner about the best way for your specific situation. You may be able to print the remittance information from GP or it may be better/easier to create a custom report.

      -Victoria

      Like

  15. Hello Victoria,
    Thank you for all of your work published here on your web site.

    I am a novice with the GP database so I have a lot to learn:

    I have a question in regards to the SQL view to show AP apply information in GP:
    1) How come you are not using PM30600 table? what is the difference between PM30600 and the other PM tables?
    2) I need to write a crystal report to show all the expenses including the journal entries, credit memo…
    I found some of the data in PM30600 and the journal entries are in the GL20000 table. What other tables do I need to include in order to get all the expenses?
    3) I guess I have to create a view like the example you had above and then I can use Crystal report to display the data.

    I really appreciate your help.

    Kim Hoang

    Like

    • Hi Kim,

      PM30600 holds the GL Distributions for historical payables transactions. Since this particular view is only showing apply information (what invoice was paid with what check, credit memo, etc.) there was no reason to show the GL distributions here. You may want to take a look at my view for Payables GL Distributions – I think that would help get you started on your report.

      -Victoria

      Like

  16. Thanks for all this Victoria.

    Is the term “Invoice” synonymous with the items that have a Doc Type of 1 and the corresponding Doc Number?

    Like

  17. I am not even sure how to ask this question. I am an IT guy, not an accountant. We just started using Dynamics GP a couple of months ago. An incident came up recently and the Accounting folks are asking me to come up with some form of solution for them. The issue is that a vendor called in saying that we didn’t pay an invoice.

    Normally they would go in to our old system and put in the invoice number and be able to see what check number we paid that invoice on. Using your query above this works only some of the times in Dynamics. The instance they brought to me when I use your query to search for the Document Number, the document is returned but for payment type it shows Credit Memo. I am having no luck finding a way to trace that invoice number back to the check number that was used to pay it. So far the only way we have found to find the check number is to go back to the hard copy filed away and look at the remittance sheet to find the check number.

    Hopefully I have explained the problem well enough.

    Is there any help you can offer? :-)

    Like

    • Keith,

      If the results of this view bring back Credit Memo, then the invoice was not paid with a check. In GP, for both payables and receivables, the concept is that any credit transaction (return, credit memo or payment) can be applied to any debit transaction (invoice, debit memo, finance charge). So if I have an invoice for $50 and a credit memo for $50 and I apply the credit memo to the invoice, there is no check involved, technically the credit memo ‘paid’ the invoice.

      Whether the fact that you have applied a credit memo to an invoice appears on a check stub or not the next time you cut a check for this vendor will very much depend on the settings you have for payables and how checks are cut. If you happen to print this information on the check stubs and save your copy of the check stub, then you can go back to the paper files to see this, but it will not be saved anywhere in GP. The reason for this is that the check had nothing to do with paying the invoice, the credit memo did. If it does show on the check stub, it is ‘informational only’ and a check stub can bet set to show any apply information since the last time a check was cut.

      Typically it should be enough for a vendor to get the number of their credit memo that you have applied to their invoice. This is sometimes a little bit of a different concept for accounting users who have migrated from other systems and the terminology may take a little getting used to. If your users need more clarification on this, it may be a good idea to get some training on these concepts from your GP Partner.

      -Victoria

      Like

      • Victoria,

        Thanks for the response. It was the response we figured we would get but thought we would ask the question.

        When I broke the news to my accounting people, they responded with this inquiry:

        “It sounds like there is no way to change a setting in Dynamics that would allow the credits to not be applied. In other words, don’t distinguish between a negative invoice and a positive invoice. Just list the invoices (positive or negative) on the check without applying them to anything. This would permit us to inquiry about an invoice or credit and determine what check number they were paid on or deducted on without having to go to the paper check copy.”

        Is there a setting in Dynamics to do what they are asking?

        Thanks again for all your help. We have not been too impressed with our local partner. Unfortunately, they are the only one local that we have been able to find.

        Keith

        Like

        • Keith,

          There is no such concept as a ‘negative invoice’ in GP. And no setting to turn something like that on either. While this question comes up periodically in the GP newsgroups, this kind of change to the underlying logic of GP would be a tremendous effort and I do not believe you will see that in any new releases in the near future (if ever).

          -Victoria

          Like

  18. Hi Victoria,

    Thanks for posting these types of tools that makes our lives easier. =)

    This view is showing payments that were manually applied but not posted. So the Applied Amount is filled but the Payment Doc number is blank. When I checked the Payment was not posted yet. Is there a way to filter posted and unposted payments using this view?

    Thanks Victoria! All the best!

    honeylyn

    Like

    • Honeylyn,

      That is a great question! i just altered the view to include a Payment Status. I also changed the column names so that this view can be used in Crystal Reports with no issues.

      -Victoria

      Like

      • Thank you so much Victoria!

        I’m actually using this in my Crystal Report so what you did on changing the columns helped a lot!

        Btw, I am checking my output using your view and found out that when the payment amount was not fully applied, it display 2 lines with the same information. For example:

        Payment – 2000

        Invoice 1 = 1000
        Invoice 2 = 900

        In the output, it’ll display
        Invoice 1 = 1000
        Invoice 1 = 1000
        Invoice 2 = 900
        Invoice 2 = 900

        I’m so sorry for all these questions. I’m not well versed in SQL so I can’t modify your view.

        Thanks again!

        Like

        • Holeylyn,

          Great catch – thanks! If the payment is not fully applied, the apply information may be in both the PM10200 and PM30300 table at the same time. Since I was doing a UNION ALL it was bringing in both sets of records. I just updated the view to have UNION instead, which will take out any doubles.

          -Victoria

          Like

  19. Thanks Victoria,

    I am not really a SQL guru so you dont happen to have anything that shows the accounts, credits, debits and Distribution references as well do you??

    Thanks
    Nev

    Like

  20. Hi again Victoria,

    I am getting the following error when trying to use a view in smartlist.

    GPS Error: 58
    SQL Error: 156 [Microsoft][SQL Native Client][SQL Server]Incorrect syntax near the keyword ‘and’.
    ODBC Error: 37000

    The view I am runnning is as follows and is fine in SQL Server.

    The error occurrs when I am trying to access the key fields. Any idea’s??

    SELECT P.VENDORID AS [Vendor ID], PM.VENDNAME AS [Vendor Name],
    CASE P.DOCTYPE WHEN 1 THEN ‘Invoice’ WHEN 2 THEN ‘Finance Charge’ WHEN 3 THEN ‘Misc Charge’ END AS [Document Type],
    P.DOCDATE AS [Document Date], P.VCHRNMBR AS [Voucher Number], P.DOCNUMBR AS [Document Number], P.DOCAMNT AS [Document Amount],
    COALESCE (PA.APFRMAPLYAMT, 0) AS [Applied Amount], COALESCE (PA.VCHRNMBR, ”) AS [Payment Voucher Number], COALESCE (P2.DOCNUMBR, ”)
    AS [Payment Doc Number], COALESCE (P2.DOCDATE, ‘1/1/1900′) AS [Payment Date], COALESCE (PA.DATE1, ‘1/1/1900′) AS [Apply Date],
    COALESCE (PA.GLPOSTDT, ‘1/1/1900′) AS [Apply GL Posting Date], OPENDIST.CRDTAMNT AS [OPENDIST Credit Amount],
    OPENDIST.DEBITAMT AS [OPENDIST Debit Amount], OPENDIST.DistRef AS [OPENDIST Dist Ref], HISTDIST.CRDTAMNT AS [HISTDIST Credit Amount],
    HISTDIST.DEBITAMT AS [HISTDIST Debit Amount], HISTDIST.DistRef AS [HISTDIST Dist Ref], ACCDETAILSOPEN.ACTNUMST AS [In Open Acc],
    ACCDETAILSHIST.ACTNUMST AS [In Hist Acc],
    CASE PA.DOCTYPE WHEN 4 THEN ‘Return’ WHEN 5 THEN ‘Credit Memo’ WHEN 6 THEN ‘Payment’ ELSE ” END AS [Payment Type]
    FROM (SELECT VENDORID, DOCTYPE, DOCDATE, VCHRNMBR, DOCNUMBR, DOCAMNT, VOIDED
    FROM dbo.PM30200
    UNION ALL
    SELECT VENDORID, DOCTYPE, DOCDATE, VCHRNMBR, DOCNUMBR, DOCAMNT, VOIDED
    FROM dbo.PM20000) AS P LEFT OUTER JOIN
    (SELECT VENDORID, APTVCHNM, APTODCTY, APFRDCNM, DOCTYPE, DOCDATE, APFRMAPLYAMT, VCHRNMBR, DATE1, GLPOSTDT
    FROM dbo.PM10200
    UNION ALL
    SELECT VENDORID, APTVCHNM, APTODCTY, APFRDCNM, DOCTYPE, DOCDATE, APFRMAPLYAMT, VCHRNMBR, DATE1, GLPOSTDT
    FROM dbo.PM30300) AS PA ON P.VCHRNMBR = PA.APTVCHNM AND P.VENDORID = PA.VENDORID AND
    P.DOCTYPE = PA.APTODCTY LEFT OUTER JOIN
    (SELECT VCHRNMBR, DOCTYPE, DOCNUMBR, DOCDATE
    FROM dbo.PM20000 AS PM20000_1
    UNION ALL
    SELECT VCHRNMBR, DOCTYPE, DOCNUMBR, DOCDATE
    FROM dbo.PM30200 AS PM30200_1) AS P2 ON P2.VCHRNMBR = PA.VCHRNMBR AND P2.DOCTYPE = PA.DOCTYPE LEFT OUTER JOIN
    dbo.PM00200 AS PM ON P.VENDORID = PM.VENDORID LEFT OUTER JOIN
    dbo.PM10100 AS OPENDIST ON P.VCHRNMBR = OPENDIST.VCHRNMBR LEFT OUTER JOIN
    dbo.GL00105 AS ACCDETAILSOPEN ON OPENDIST.DSTINDX = ACCDETAILSOPEN.ACTINDX LEFT OUTER JOIN
    dbo.PM30600 AS HISTDIST ON P2.VCHRNMBR = HISTDIST.VCHRNMBR LEFT OUTER JOIN
    dbo.GL00105 AS ACCDETAILSHIST ON HISTDIST.DSTINDX = ACCDETAILSHIST.ACTINDX
    WHERE (P.DOCTYPE IN (1, 2, 3)) AND (P.VOIDED = 0)

    Like

    • Nev,

      I added the following lines at the top of your query:
      CREATE VIEW view_Nev_test
      AS

      and the following at the bottom:
      GO
      GRANT SELECT on view_Nev_test to DYNGRP

      and created a view. Then I created a SmartList based on that view in GP 10.0 SP 3 and it works just fine for me.

      You will want to add code to replace any NULLs possible with a 0 for numbers and a ” for strings, otherwise you’re going to get garbage back on some of the columns you’ve added. I typically use COALESCE for that – take a look in my code above for examples on that.

      However, the error that you’re getting is caused by something else. At what step of creating the SmartList are you getting that? Also, what versions of GP and SQL and who are you logged into GP as?

      -Victoria

      Like

  21. Hi Victoria,

    Can you include the Apply Posting Date as well, when we print Historical Aging by GL posting date, the Apply Posting date is the date used to reconcile against GL and we can’t view the Apply Date/Apply Posting Date when drill down to the source Apply Information window.

    Like

  22. Thanks, Victoria. I’ll check again.

    Like

  23. Victoria,

    Thanks so much – I can’t tell you what a help this has been. I’m just beginning to get familiar with the Dynamics table structure.

    My folks are asking me for all items entered into A/P with payment info just for the ones paid. I’m figuring I need an outer join somewhere but am not familiar enough with the tables. Is there a quick way to give them that?

    Thanks!!

    Like

    • Kathy,

      It sounds like the view on this page should give you what you’re looking for. Any AP transactions with no payment will just show blanks for the payment information and $0 for the Applied Amount. Please let me know if I am misunderstanding what you’re looking for.

      -Victoria

      Like

  24. I have smartlist builder installed but I’m not sure how to incorporate this script into it. Is there a process doc or something that I can find that can guide me through this?

    Like

    • Boris,

      I have been asked this question a lot recently, I will try to put together a step by step guide sometime soon. In the meantime, here is the brief version:

      This script can be used in SQL Server Management Studio to create a SQL View. Just copy it into a New Query window, choose your database and execute the script. (Warning: if you’re not comfortable working in SQL Server, please ask for help from someone in your organization who is.) Once the view is created, you can point to it in SmartList Builder, there are instructions and an explanation in the SmartList Builder manual for using a SQL table or view to create a SmartList.

      -Victoria

      Like

  25. Hello Victoria,

    Thanks for sharing this useful view!. Is it not needed to use the PM20100 table to show all transactions that are not fully applied?

    Like

    • Vito,

      Transactions that are not fully applied will be in the PM20000 table and the apply information will be in the PM10200 table. So I don’t see why PM20100 would be needed. As a general rule, when you see ‘Temporary’ in the name of the table you’re not going to need it.

      -Victoria

      Like

  26. Hi Victoria, Thank you so much for the useful information!

    One question… is there a way to modify the sql to retrieve the same info for transactions that have not been full applied? If not, do you know of another way to do this?

    Like

  27. Please include vendor name

    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

Follow

Get every new post delivered to your Inbox.

Join 1,494 other followers

%d bloggers like this: