SQL View to return most recent five purchase orders by vendor

Microsoft Dynamics GPI don’t recall when I wrote this SQL view, but I stumbled across it when I was looking for something else and thought it worth posting.

It uses a common table expression to return the most recent five purchase orders for each vendor.

-- drop view if it exists
IF OBJECT_ID(N'uv_AZRCRV_MostRecentFivePurchaseOrdersByVendor ', N'V') IS NOT NULL
	DROP VIEW uv_AZRCRV_MostRecentFivePurchaseOrdersByVendor
GO
-- create view
CREATE VIEW uv_AZRCRV_MostRecentFivePurchaseOrdersByVendor 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 PONUMBER ,DOCDATE ,VENDORID ,SUBTOTAL ,TAXAMNT ,POSTATUS ,ROW_NUMBER() OVER (PARTITION BY VENDORID ORDER BY DOCDATE DESC) AS RowNumber FROM ( SELECT PONUMBER ,DOCDATE ,VENDORID ,SUBTOTAL ,TAXAMNT ,POSTATUS FROM POP10100 UNION ALL SELECT PONUMBER ,DOCDATE ,VENDORID ,SUBTOTAL ,TAXAMNT ,POSTATUS FROM POP30100 ) AS PurchaseOrders ) SELECT PONUMBER ,DOCDATE ,VENDORID ,SUBTOTAL ,TAXAMNT ,CASE POSTATUS WHEN 1 THEN 'New' WHEN 2 THEN 'Released' WHEN 3 THEN 'Change Order' WHEN 4 THEN 'Received' WHEN 5 THEN 'Closed' WHEN 6 THEN 'Cancelled' END AS POSTATUS FROM PurchaseOrders WHERE RowNumber <= 5 GO GRANT SELECT ON uv_AZRCRV_MostRecentFivePurchaseOrdersByVendor TO DYNGRP GO

As it is written as a view, it can easily be used in SmartList Designer, SmartList Builder, Refreshable Excel Reports, SSRS or other reporting tools.

SQL Script to update vendor and customer email addresses

Microsoft Dynamics GPBack in August 2011 I posted a SQL script which transfers vendor email addresses from the INET1 to the Email Address field and today’s script is a variation of that one.

The original script I posted only did a transfer for vendors and would migrate the INET1 one field regardless of whether there was already something in the Email Address field.

This new script only does the transfer if INET1 is longer than 1 character and the Email Address field is empty.

It also sets the Email Bcc Field to a supplied email address.

/*
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). */
UPDATE SY01200 SET EmailToAddress = INET1 ,EmailBccAddress = 'purchase.archive@example.com' WHERE Master_Type = 'VEN' AND LEN(CAST(INET1 AS VARCHAR(100))) > 0 AND LEN(CAST(EmailToAddress AS VARCHAR(100))) = 0 GO UPDATE SY01200 SET EmailToAddress = INET1 ,EmailBccAddress = 'sales.archive@example.com' WHERE Master_Type = 'CUS' AND LEN(CAST(INET1 AS VARCHAR(100))) > 0 AND LEN(CAST(EmailToAddress AS VARCHAR(100))) = 0 GO

SQL Script to get all accounts set as available in Purchasing lookups

Microsoft Dynamics GPFollowing on from the last post, in which I posted a script to get accounts linked to a vendor, this post is a script which returns all accounts which are set as available in lookups in the Purchasing series.

The SQL is straightforward, but I had to work out the binary entries to check for by saving and checking each combination.

/*
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 ['Account Master'].ACTINDX ,['Account Index Master'].ACTNUMST ,['Account Master'].ACTDESCR FROM GL00100 AS ['Account Master'] INNER JOIN GL00105 AS ['Account Index Master'] ON ['Account Index Master'].ACTINDX = ['Account Master'].ACTINDX WHERE DSPLKUPS IN ( 0x04000000 -- purchasing ,0x05000000 -- sales/purchasing ,0x06000000 -- inventory control/purchasing ,0x07000000 -- sales/inventory control/purchasing ,0x0C000000 -- purchasing/payroll ,0x0D000000 -- sales/purchasing/payroll ,0x0E000000 -- inventory control/purchasing/payroll ,0x0F000000 -- sales/inventory control/purchasing/payroll ,0xFFFFFF7F -- never set ) GO

SQL Script to get all vendor purchasing accounts

Microsoft Dynamics GPI recently needed to select all assigned purchasing accounts for a particular vendor; this would include both the normal purchasing one on the Vendor Account Maintenance window and the additional ones in the Additional Vendor Accounts window.

The SQL is fairly straightforward, but I had to make sure each account was only included once:

/*
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 DISTINCT ['Account Master'].ACTINDX ,['Account Index Master'].ACTNUMST ,['Account Master'].ACTDESCR FROM PM00200 AS ['PM Vendor Master'] LEFT JOIN PM00203 AS ['Vendor Accounts'] ON ['Vendor Accounts'].VENDORID = ['PM Vendor Master'].VENDORID LEFT JOIN GL00100 AS ['Account Master'] ON ['Account Master'].ACTINDX = ['Vendor Accounts'].ACTINDX OR ['Account Master'].ACTINDX = ['PM Vendor Master'].PMPRCHIX LEFT JOIN GL00105 AS ['Account Index Master'] ON ['Account Index Master'].ACTINDX = ['Account Master'].ACTINDX WHERE ['PM Vendor Master'].VENDORID = 'ADVANCED0001' GO

Implementing Fastpath’s Audit Trail: View reports

FastpathThis post is part of the series on Implementing Fastpath's Audit Trail and is part of the parent Implementing Fastpath's Assure Suite series.

In yesterday’s post, I took a look at the Data Changes view available in the Fastpath Assure portal for Audit Trail. This is the way of looking at the raw data, but is not the friendliest method of reviewing the audited changes which is available, It is suitable for someone like me who understands the data structure of Microsoft Dynamics GP, but the ordinary end-user needs something more user friendly.

Fortunately, this is available from the reports which are included as standard. To access these reports, expand Audit Trails and select Reports; there is a long list of reports then shown in the navigation pane:

Audit Trail Available Reports

Continue reading “Implementing Fastpath’s Audit Trail: View reports”

Implementing Fastpath’s Audit Trail: Review data changes

FastpathThis post is part of the series on Implementing Fastpath's Audit Trail and is part of the parent Implementing Fastpath's Assure Suite series.

With Audit Trails installed, configured with triggers created and a scheduled data collect in place, the audited changes are available for users to view in the Fastpath Assure portal.

The first palc you can see them, and the one covered in this post, is in Data Changes (accessible from (Audit Trail » Data changes):

Data Changes

Continue reading “Implementing Fastpath’s Audit Trail: Review data changes”

Implementing Fastpath’s Audit Trail: Ad hoc data collect

FastpathThis post is part of the series on Implementing Fastpath's Audit Trail and is part of the parent Implementing Fastpath's Assure Suite series.

Ina previous post I covered the scheduled collection of Audit Trails data. This will allow you to schedule a regular collection of data which, for example, may run every day in the early hours, but sometimes you might need to audit ome data same day. To facilitate this, the Assure portal includes an ad hoc data collection routine for Audit Trails.

To run this ad hoc collect data, select the Manage Data page (Audit Trail » Setup » Manage Data) and click the arrows button.

Clicking the Collect button will run the data collection and pull all new data through to Fastpath:

Collect Data changes

Click to show/hide the Implementing Fastpath's Audit Trail Series Index

Implementing Fastpath’s Assure Suite: Assign Permissions

FastpathThis post is part of the series on Implementing Fastpath's Assure Suite.

In the last post, I showed how to create a new user which would allow that user to log into the portal, but that would not allow them to do anything else; to allow them to do something more you need to assign permissions.

There is two ways to assign security and I find the first way to be, by far, the easiest.

Firstly, you can assign security by user (Setup » Users) by selecting the user and then by clicking the toggle next to the role to which you want to assign them:

Assign roles to users

Continue reading “Implementing Fastpath’s Assure Suite: Assign Permissions”

Implementing Fastpath’s Assure Suite: Create new portal user

FastpathThis post is part of the series on Implementing Fastpath's Assure Suite.

The management of Fastpath Assure is done through the Fastpath Assure portal (in the UK, the address is https://uk.fastpathassure.com/). The administrator account will be configured by Fastpath when the portal is created with additional users being created by that administrator.

To create a new user, log into the portal and open the Users page (Setup » Users). Existing users will be displayed; at the top of the view click the green plus icon to create a new user:

Continue reading “Implementing Fastpath’s Assure Suite: Create new portal user”

Implementing Fastpath’s Audit Trail: Transfer between systems

FastpathThis post is part of the series on Implementing Fastpath's Audit Trail and is part of the parent Implementing Fastpath's Assure Suite series.

As mentioned in a previous post, triggers are created in one company and can be deployed to other companies on the same system, but in a lot of cases, you might need to create them on a development system and then deploy them to the live one.

Fastpath Audit Trails does allow you to do this via the Save Settings function available as a button on the toolbar:

Save settings button

Continue reading “Implementing Fastpath’s Audit Trail: Transfer between systems”