Mark Polino has a set of Historical Excel Reports available for sale. The reports available are as follows:
- Receivables Management Historical Aged Trial Balance
- Payables Management Historical Aged Trial Balance
- Historical Inventory Trial Balance
- Historic Stock Status Report
Now is the ideal time to buy them, as the price is increasing on 1st April 2017.
Configuring Nolan’s Advanced Bank Reconciliation (ABR) to automatically extract transactions when reconciling a bank account is not complicated, but I keep on forgetting where the option is located. That gets a little embarrassing when you tell a client, “yes, you can do that” and then have to spend five minutes looking for the option.
So by writing it down here, I am hoping to be able to commit it to memory.
this option is per bank account and is located on the Bank Account Setup window ().
Click the Options menu and then select Extract options:
The final session I attended was Leveraging GP Power Tools which was a joint session between David Musgrave and Mariano Gomez; Mariano did the introductions to each section, with David then doing the demonstration.
I’m not as familiar with the GP Power Tools as I should be; I wasn;t that familiar with it’s predecessor, the Support Debugging Tool.
This session was aimed mainly at the deveoper tools, but the guys did have time to cover tools in other parts of the program.
GP Power Tools is sold by David via Mekorma, which is where Mariano works, with three modules being available for purchase separately but any one module and you get a System module included.
Unfortunately, I was unable to say goodbye to the guys as their session ended at 1230 and I was scheduled to take some Association of Dynamics Professionals examinations at 1300 back at the Hilton Garden Inn.
When I am back from vacation, I am going to have to get the latest verson of the GP Power Tools and spend some time going through them as, from what I saw, there are features from which not only Perfect Image, but also our customers, would benefit.
While I was at the conference, I did have an email exchange with one of PI’s Development Consultants where I advised that they would probably find the GP Power Tools useful.
On this one I am open to someone telling me that there is actually a way to do this out of the box, but I haven’t been able to find it. On a SmartList I was creating, I needed to pull the customer item number and description through to a SOP transaction SmartList, but couldn’t find any table to link with.
As a workaround, I created this view which contains the required link using the CUSTNMBR from SOP10100 (Sales Transaction Work) and the ITEMNMBR from SOP10200 (Sales Transaction Amounts Work):
CREATE VIEW uv_AZRCRV_CustomerItemLink AS /* Created by Ian Grieve of azurecurve|Ramblings of a Dynamics GP Consultant (http://www.azurecurve.co.uk) This code is licensed under the Creative Commons Attribution-NonCommercial-ShareAlike 4.0 International (CC BY-NC-SA 4.0 Int). */ SELECT SOP102.SOPNUMBE ,SOP102.SOPTYPE ,SOP102.LNITMSEQ ,SOP101.CUSTNMBR ,SOP102.ITEMNMBR FROM SOP10200 AS SOP102 WITH (NOLOCK) INNER JOIN SOP10100 AS SOP101 WITH (NOLOCK) ON SOP101.SOPNUMBE = SOP102.SOPNUMBE AND SOP101.SOPTYPE = SOP102.SOPTYPE INNER JOIN SOP60300 AS SOP603 WITH (NOLOCK) ON SOP603.CUSTNMBR = SOP101.CUSTNMBR AND SOP603.ITEMNMBR = SOP102.ITEMNMBR GO GRANT SELECT ON uv_AZRCRV_CustomerItemLink TO DYNGRP GO
A client I was working with recently was using two SmartLists, one for Payables Transactions and the other from Purchase Order Transactions, to generate a listing of purchasing transactions. However, they had to do manual fiddling around in Excel to get some of the formatting correct and didn;t have all of the information they wanted. Always up for a challenge, I had a go at producing a view we could plug in using SmartList Designer which would give them the information they wanted in the format the needed.
The below is the SQL view which was produced. It includes the PO and Receipt Numbers as well as the Originating Currency (with symbol) and exchange rate; the Functional Currency was left without a symbol so it can be easily totalled in Excel. Where the transaction does not have an Originating Currency I am outputting the Functional Currency.
/* Created by Ian Grieve of azurecurve|Ramblings of a Dynamics GP Consultant (http://www.azurecurve.co.uk) This code is licensed under the Creative Commons Attribution-NonCommercial-ShareAlike 4.0 International (CC BY-NC-SA 4.0 Int). */ CREATE VIEW uv_AZRCRV_PurchasingTrxList AS SELECT PM.VCHRNMBR AS 'Voucher Number' ,CASE WHEN PM.DOCTYPE = 1 THEN PM401.PMTRXDSC_1 WHEN PM.DOCTYPE = 2 THEN PM401.PMTRXDSC_2 WHEN PM.DOCTYPE = 3 THEN PM401.PMTRXDSC_3 WHEN PM.DOCTYPE = 4 THEN PM401.PMTRXDSC_4 WHEN PM.DOCTYPE = 5 THEN PM401.PMTRXDSC_5 WHEN PM.DOCTYPE = 6 THEN PM401.PMTRXDSC_6 WHEN PM.DOCTYPE = 7 THEN PM401.PMTRXDSC_7 WHEN PM.DOCTYPE = 8 THEN PM401.PMTRXDSC_8 ELSE 'Unknown' END AS 'Document Type' ,PM004.DOCTYPE ,CASE WHEN PM004.DCSTATUS = 1 THEN 'Work' WHEN PM004.DCSTATUS = 2 THEN 'Open' WHEN PM004.DCSTATUS = 3 THEN 'History' ELSE 'Unknown' END AS 'Document Status' ,PM004.DCSTATUS ,PM.PORDNMBR AS 'PO Number' ,ISNULL(POP303.POPRCTNM,'') AS 'Receipt Number' ,PM.VENDORID AS 'Creditor Name' ,PM002.VENDNAME AS 'Creditor ID' ,CONVERT(VARCHAR,PM.DOCDATE,103) AS 'Document Date' ,CASE WHEN PM.DOCTYPE <= 3 THEN '' ELSE '-' END + ISNULL(RTRIM(MC402.CRNCYSYM),RTRIM(MC402F.CRNCYSYM)) + CAST(CAST(ISNULL(MC.OPURAMT,PM.PRCHAMNT) AS DECIMAL(10,2)) AS VARCHAR) AS 'Originating Purchasing Amount' ,CASE WHEN PM.DOCTYPE <= 3 THEN '' ELSE '-' END + ISNULL(RTRIM(MC402.CRNCYSYM),RTRIM(MC402F.CRNCYSYM)) + CAST(CAST(ISNULL(MC.ORTAXAMT,PM.TAXAMNT) AS DECIMAL(10,2)) AS VARCHAR) AS 'Originating Tax Amount' ,CASE WHEN PM.DOCTYPE <= 3 THEN '' ELSE '-' END + ISNULL(RTRIM(MC402.CRNCYSYM),RTRIM(MC402F.CRNCYSYM)) + CAST(CAST(ISNULL(MC.ORDOCAMT,PM.DOCAMNT) AS DECIMAL(10,2)) AS VARCHAR) AS 'Originating Document Amount' ,CASE WHEN MC.XCHGRATE IS NULL THEN 0 ELSE MC.XCHGRATE END AS 'Exchange Rate' ,CAST(CASE WHEN PM.DOCTYPE <= 3 THEN PM.PRCHAMNT ELSE PM.PRCHAMNT * -1 END AS DECIMAL(10,2)) AS 'Functional Purchasing Amount' ,CAST(CASE WHEN PM.DOCTYPE <= 3 THEN PM.TAXAMNT ELSE PM.TAXAMNT * -1 END AS DECIMAL(10,2)) AS 'Functional Tax Amount' ,CAST(CASE WHEN PM.DOCTYPE <= 3 THEN PM.DOCAMNT ELSE PM.DOCAMNT * -1 END AS DECIMAL(10,2)) AS 'Functional Document Amount' FROM ( -- Transaction History SELECT VCHRNMBR,DOCTYPE,DOCDATE,PORDNMBR,VENDORID,PRCHAMNT,TAXAMNT,DOCAMNT FROM PM30200 WITH (NOLOCK) UNION ALL -- Transaction Open SELECT VCHRNMBR,DOCTYPE,DOCDATE,PORDNMBR,VENDORID,PRCHAMNT,TAXAMNT,DOCAMNT FROM PM20000 WITH (NOLOCK) UNION ALL -- Transaction Work SELECT VCHRNMBR,DOCTYPE,DOCDATE,PORDNMBR,VENDORID,PRCHAMNT,TAXAMNT,DOCAMNT FROM PM10000 WITH (NOLOCK) UNION ALL -- Payment Work SELECT VCHRNMBR,DOCTYPE,DOCDATE,'',VENDORID,0,0,CHEKTOTL FROM PM10300 WITH (NOLOCK) UNION ALL -- Manual Payment Work SELECT VCHRNMBR,DOCTYPE,DOCDATE,'',VENDORID,0,0,DOCAMNT FROM PM10400 WITH (NOLOCK) ) AS PM INNER JOIN PM00400 PM004 WITH (NOLOCK) ON PM004.CNTRLNUM = PM.VCHRNMBR AND PM004.DOCTYPE = PM.DOCTYPE INNER JOIN PM00200 AS PM002 WITH (NOLOCK) ON PM002.VENDORID = PM.VENDORID LEFT JOIN MC020103 AS MC WITH (NOLOCK) ON MC.VCHRNMBR = PM.VCHRNMBR AND MC.DOCTYPE = PM.DOCTYPE LEFT JOIN POP30300 AS POP303 WITH (NOLOCK) ON POP303.VCHRNMBR = PM.VCHRNMBR LEFT JOIN DYNAMICS..MC40200 AS MC402 WITH (NOLOCK) ON MC402.CURRNIDX = MC.CURRNIDX INNER JOIN MC40000 AS MC400 WITH (NOLOCK) ON MC400.FUNLCURR = MC400.FUNLCURR INNER JOIN DYNAMICS..MC40200 AS MC402F WITH (NOLOCK) ON MC402F.CURRNIDX = MC400.FUNCRIDX INNER JOIN PM40100 AS PM401 WITH (NOLOCK) ON PM401.UNIQKEY = PM401.UNIQKEY ORDER BY PM.DOCDATE GO GRANT SELECT ON uv_AZRCRV_PurchasingTrxList TO DYNGRP GO
One of the odd limitations in SmartList is the inability to run a report and get the distribution accounts for payables transactions; there is a field available for the different distributions, but these fields are from the Vendor Card, not the transaction. This view was the result of a query from someone; it returns the account number and description for the payables distributions.
CREATE VIEW uv_AZRCRV_PayablesTransactionDistributionAccounts AS SELECT ['PM Distribution WORK OPEN HIST'].VCHRNMBR AS 'Voucher Number' ,CASE WHEN ['PM Distribution WORK OPEN HIST'].DISTTYPE = 1 THEN 'Cash' WHEN ['PM Distribution WORK OPEN HIST'].DISTTYPE = 2 THEN 'Payable' WHEN ['PM Distribution WORK OPEN HIST'].DISTTYPE = 3 THEN 'Discount Available' WHEN ['PM Distribution WORK OPEN HIST'].DISTTYPE = 4 THEN 'Discount Taken' WHEN ['PM Distribution WORK OPEN HIST'].DISTTYPE = 5 THEN 'Finance Charge' WHEN ['PM Distribution WORK OPEN HIST'].DISTTYPE = 6 THEN 'Purchase' WHEN ['PM Distribution WORK OPEN HIST'].DISTTYPE = 7 THEN 'Trade Discount' WHEN ['PM Distribution WORK OPEN HIST'].DISTTYPE = 8 THEN 'Miscellaneous Charge' WHEN ['PM Distribution WORK OPEN HIST'].DISTTYPE = 9 THEN 'Freight' WHEN ['PM Distribution WORK OPEN HIST'].DISTTYPE = 10 THEN 'Taxes' WHEN ['PM Distribution WORK OPEN HIST'].DISTTYPE = 11 THEN 'Writeoffs' WHEN ['PM Distribution WORK OPEN HIST'].DISTTYPE = 12 THEN 'Other' WHEN ['PM Distribution WORK OPEN HIST'].DISTTYPE = 13 THEN 'GST Disc' WHEN ['PM Distribution WORK OPEN HIST'].DISTTYPE = 14 THEN 'PPS Amount' WHEN ['PM Distribution WORK OPEN HIST'].DISTTYPE = 16 THEN 'Round' WHEN ['PM Distribution WORK OPEN HIST'].DISTTYPE = 17 THEN 'Realized Gain' WHEN ['PM Distribution WORK OPEN HIST'].DISTTYPE = 18 THEN 'Realized Loss' WHEN ['PM Distribution WORK OPEN HIST'].DISTTYPE = 19 THEN 'Due To' WHEN ['PM Distribution WORK OPEN HIST'].DISTTYPE = 20 THEN 'Due From' END AS 'Distribution Type' ,['PM Distribution WORK OPEN HIST'].DistRef AS 'Distribution Reference' ,['Account Index Master'].ACTNUMST AS 'Account Number' ,['Account Master'].ACTDESCR AS 'Account Description' FROM (SELECT VCHRNMBR ,DISTTYPE ,DSTINDX ,DistRef FROM PM10100 AS ['PM Distribution WORK OPEN HIST'] UNION ALL SELECT VCHRNMBR ,DISTTYPE ,DSTINDX ,DistRef FROM PM30600 AS ['PM Distribution History File'] ) AS ['PM Distribution WORK OPEN HIST'] INNER JOIN GL00105 AS ['Account Index Master'] ON ['Account Index Master'].ACTINDX = ['PM Distribution WORK OPEN HIST'].DSTINDX INNER JOIN GL00100 AS ['Account Master'] ON ['Account Master'].ACTINDX = ['Account Index Master'].ACTINDX GO GRANT SELECT ON uv_AZRCRV_PayablesTransactionDistributionAccounts TO DYNGRP GO
Out of the box no, but SmartList Designer can be used to do this. I created a new SQL view to get payments at a status of work and, in SmartList Designer, combined it with the remittance enabled view and vendor email addresses view I have previously posted.
I could have used the table in GP, but I find using views in SmartList Designer easier so created the view.
CREATE VIEW uv_AZRCRV_PaymentRunWORK AS SELECT PM103.BACHNUMB AS 'Batch Number' ,PM103.VENDORID AS 'Creditor ID' ,PM2.VENDNAME AS 'Creditor Name' ,PM2.VADDCDPR AS 'Primary Address' ,PM2.VADCDTRO AS 'Remit To Address' ,PM103.DOCDATE AS 'Document Date' FROM PM00200 AS PM2 INNER JOIN PM10300 AS PM103 ON PM103.VENDORID = PM2.VENDORID GO GRANT SELECT ON uv_AZRCRV_PaymentRunWORK TO DYNGRP GO
All of these views could have been created natively in SmartList Builder, but by creating them as views I can reuse them in SmartList Designer or any other reporting tool.
CREATE VIEW uv_AZRCRV_VendorRemittanceEnabled AS SELECT EmailCardID AS VENDORID ,CASE WHEN EmailDocumentEnabled = 1 THEN 'Yes' ELSE 'No' END AS 'Remittance Enabled' FROM SY04905 WHERE EmailSeriesID = 4 AND MODULE1 = 19 AND EmailDocumentID = 6 GO GRANT SELECT ON uv_AZRCRV_VendorRemittanceEnabled TO DYNGRP GO
The GP Power Tools includes all of the functionality of the old Support Debugging Tool that they replace, but have new functionality, enhancements and bug fixes.
There is a special introductory annual subscription price of US$365 (one free day every four years!).
More information can be found on the GP Power Tools here.
If you were a Support Debugging Tool user on Microsoft Dynamics GP 2010 or 2013 then absolutely nothing needs to change. SUpport Debugging Tool will not expire.
That said, you should consider upgrading to GP Power Tools for one major benefit: GP Power Tools is fully supported by Winthrop DC.
While this may not sound like much, the old Support Debugging Tool, which was offered by Microsoft, was not supported at all (not even by Microsoft).
I am slightly behind the times with this post as this news is a few weeks old, but I have been busy and am now trying to ctahc up. When David Musgrave was working for Microsoft he wrote the Support Debugging Tool which contained some very useful functions. I did wonder what the future held for it when he left Microsoft last year, but he posted a while ago that he had negotiated an exclusive agreement with Microsoft which allows him to continue work on and release the tool.
There will be some changed to the Support Debugging Tool under this agreement. Most noticeable is the fact that it will now be called GP Power Tools.
GP Power Tools will be initially released for the following Microsoft Dynamics GP versions:
- v11.0: Microsoft Dynamics GP 2010
- v12.0: Microsoft Dynamics GP 2013 and GP 2013 R2
- v14.0: Microsoft Dynamics GP 2015
There is going to be some changes to the functionality when GP Power Tools is launched:
- New simpler Navigation with menus and area page
- Database Validation, to ensure that your upgrades work
- Numerous enhancements and the odd bug fix
- And lots more….
Another change is that GP Power Tools will now be available via an annual subscription for each customer site at the special introductory price of US$365.00. That’s a dollar a day, and every four years you will get a day for free.
For now continue to use the free Support Debugging Tool for Microsoft Dynamics GP 2010 and GP 2013 (inc. GP 2013 R2) which is available from http://winthropdc.com/SDT.
Stay tuned here or to the WInthrop DC blog for more information on when to upgrade to GP Power Tools for continued support and improved functionality.
Those of you on Microsoft Dynamics GP 2015 will need to be patient for a while longer and wait for the release of GP Power Tools.