A Diary of reIMAGINE 2016: Part 19 – Conference Day 3/Leveraging GP Power Tools

● Ian Grieve ●  ● 0 Comments   ● 

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

Click to show/hide the A Diary of reIMAGINE 2016 Series Index

● Categories: Conferences & Events, Dynamics, GP, GP Power Tools, Microsoft, Third Party Add-on ● Tags: , , , , , , , , , , ,  ● Permalink ● Shortlink ●

SQL View To Allow Customer/Item Link In SmartList Builder

● Ian Grieve ●  ● 4 Comments   ● 

Microsoft Dynamics GPOn 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
● Categories: Dynamics, GP, Microsoft, SmartList, SmartList Builder, SmartList Builder, SmartList Designer, Third Party Add-on ● Tags: , , , , , , , , , , , ,  ● Permalink ● Shortlink ●

SQL View To Return Purchasing Transactions With Multicurrency

● Ian Grieve ●  ● 1 Comment   ● 

Microsoft Dynamics GPA 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
● Categories: Dynamics, GP, Microsoft, Multicurrency, SmartList, SmartList Builder, SmartList Designer, Third Party Add-on ● Tags: , , , , , , , , , ,  ● Permalink ● Shortlink ●

SQL View For Payables Transaction Distribution Accounts

● Ian Grieve ●  ● 1 Comment   ● 

Microsoft Dynamics GPOne 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
● Categories: Dynamics, GP, Microsoft, SmartList, SmartList Builder, SmartList Builder, SmartList Designer, Third Party Add-on ● Tags: , , , , , , ,  ● Permalink ● Shortlink ●

SQL View For Computer Cheques At Work Status To Check Email Enabled

● Ian Grieve ●  ● 0 Comments   ● 

Microsoft Dynamics GPWhile onsite with a client recently, I was asked if it was possible to view the payments in a computer checks batch and whether the creditors (vendors) were enabled for emailed remittances.

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.

● Categories: Dynamics, GP, Microsoft, SmartList, SmartList Builder, SmartList Designer, Third Party Add-on ● Tags: , , , , , , , , ,  ● Permalink ● Shortlink ●

SQL View For Remittance Enabled

● Ian Grieve ●  ● 2 Comments   ● 

Microsoft Dynamics GPWant an easy way to see which vendors are configured for remittances to be emailed? Plug this view into SmartList Designer, or SmartList Builder, and you’ll have that easy way.

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
● Categories: Dynamics, GP, Microsoft, SmartList, SmartList Builder, SmartList Builder, SmartList Designer, Third Party Add-on ● Tags: , , , , , , ,  ● Permalink ● Shortlink ●

GP Power Tools Now Available

● Ian Grieve ●  ● 0 Comments   ● 

Winthrop DCWinthrop DC has now officially launched the GP Power Tools and which are available to purchase from Winthrop DC’s partner Mekorma.

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.

I am/was a Support Debugging Tool user; what now?


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

● Categories: Dynamics, GP, GP Power Tools, Microsoft, Support Debugging Tool, Third Party Add-on ● Tags: , , , , , , , , ,  ● Permalink ● Shortlink ●

The Future Of The Support Debugging Tool

● Ian Grieve ●  ● 1 Comment   ● 

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

● Categories: Dynamics, GP, GP Power Tools, Microsoft, Support Debugging Tool, Third Party Add-on ● Tags: , , , , , , , , , ,  ● Permalink ● Shortlink ●

Security Views For Use In SmartList Designer: User Access & Granted Security Roles With Tasks & Operations

● Ian Grieve ●  ● 0 Comments   ● 

Microsoft Dynamics GPThe eighth and final view in the series, shows users with their access to companies, roles, tasks and security operations. I’ll state up front that this is probably the least useful fo the views due to the sheer number of rows that it returns.

CREATE VIEW [dbo].[uv_AZRCRV_UserAccessAndGrantedSecurityRolesWithTasksAndOperations] 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 2.0 UK: England & Wales (CC BY-NC-SA 2.0 UK).
*/
SELECT
	['User Master'].USERID AS 'User ID'
	,['User Master'].USERNAME AS 'Username'
	,['User Master'].USRCLASS AS 'User Class'
	,ISNULL(['Class Master'].DSCRIPTN, '') AS 'User Class Description'
	,ISNULL(['Company Master'].INTERID, '') AS 'Intercompany ID'
	,ISNULL(['Company Master'].CMPNYNAM, '') AS 'Company Name'
	,ISNULL(['Security Assignment User Role'].SECURITYROLEID, '') AS 'Security Role ID'
	,ISNULL(['Security Roles Master'].SECURITYROLENAME, '') AS 'Security Role Name'
	,ISNULL(['Security Role Task Assignment'].SECURITYTASKID, '') AS 'Security Task ID'
	,ISNULL(['Security Task Master'].SECURITYTASKNAME, '') AS 'Security Task Name'
	,ISNULL(['Security Task Master'].SECURITYTASKDESC, '') AS 'Security Task Description'
	,ISNULL(['Security Resource Descriptions'].PRODNAME, '') AS 'Product Name'
	,ISNULL(['Security Resource Descriptions'].Series_Name, '') AS 'Series Name'
	,ISNULL(['Security Resource Descriptions'].DSPLNAME, '') AS 'Security Operation Name'
	,ISNULL(['Security Resource Descriptions'].TYPESTR, '') AS 'Security Operation Type'
FROM
	SY01400 AS ['User Master']
LEFT JOIN
	SY40400 AS ['Class Master']
		ON ['Class Master'].USRCLASS = ['User Master'].USRCLASS
LEFT JOIN
	SY60100 AS ['User-Company Access']
		ON ['User-Company Access'].USERID = ['User Master'].USERID
LEFT JOIN
	SY10500 AS ['Security Assignment User Role']
		ON ['Security Assignment User Role'].CMPANYID = ['User-Company Access'].CMPANYID
			AND ['Security Assignment User Role'].USERID = ['User-Company Access'].USERID
LEFT JOIN
	SY09100 AS ['Security Roles Master']
		ON ['Security Roles Master'].SECURITYROLEID = ['Security Assignment User Role'].SECURITYROLEID
LEFT JOIN
	SY10600 AS ['Security Role Task Assignment']
		ON ['Security Role Task Assignment'].SECURITYROLEID = ['Security Roles Master'].SECURITYROLEID
LEFT JOIN
	SY01500 AS ['Company Master']
		ON ['Company Master'].CMPANYID = ['User-Company Access'].CMPANYID
LEFT JOIN
	SY09000 AS ['Security Task Master']
		ON ['Security Task Master'].SECURITYTASKID = ['Security Role Task Assignment'].SECURITYTASKID
LEFT JOIN
	SY10700 AS ['Security Assignment Task Operations']
		ON ['Security Assignment Task Operations'].SECURITYTASKID = ['Security Task Master'].SECURITYTASKID
LEFT JOIN
	SY09400 AS ['Security Resource Descriptions']
		ON ['Security Resource Descriptions'].SECURITYID = ['Security Assignment Task Operations'].SECURITYID
GO

GRANT SELECT ON uv_AZRCRV_UserAccessAndGrantedSecurityRolesWithTasks TO DYNGRP
GO

Click to show/hide the Security Views For Use In SmartList Designer Series Index

● Categories: Dynamics, GP, Microsoft, SmartList, SmartList Builder, SmartList Designer, Third Party Add-on ● Tags: , , , , , , , ,  ● Permalink ● Shortlink ●

Security Views For Use In SmartList Designer: Security Tasks & Operations

● Ian Grieve ●  ● 0 Comments   ● 

Microsoft Dynamics GPThe seventh SQL view in this series does not include users, but instead shows the tasks and the operations. To use this view you will need to have populated the Security Resource Descriptions table.

CREATE VIEW [dbo].[uv_AZRCRV_SecurityTasksWithOperations] 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 2.0 UK: England & Wales (CC BY-NC-SA 2.0 UK).
*/
SELECT
	['Security Task Master'].SECURITYTASKID AS 'Security Task ID'
	,['Security Task Master'].SECURITYTASKNAME AS 'Security Task Name'
	,['Security Task Master'].SECURITYTASKDESC AS 'Security Task Description'
	,['Security Resource Descriptions'].PRODNAME AS 'Product Name'
	,['Security Resource Descriptions'].Series_Name AS 'Series Name'
	,['Security Resource Descriptions'].DSPLNAME AS 'Security Operation Name'
	,['Security Resource Descriptions'].TYPESTR AS 'Security Operation Type'
FROM
	SY09000 AS ['Security Task Master']
INNER JOIN
	SY10700 AS ['Security Assignment Task Operations']
		ON ['Security Assignment Task Operations'].SECURITYTASKID = ['Security Task Master'].SECURITYTASKID
INNER JOIN
	SY09400 AS ['Security Resource Descriptions']
		ON ['Security Resource Descriptions'].SECURITYID = ['Security Assignment Task Operations'].SECURITYID
GO

GRANT SELECT ON uv_AZRCRV_SecurityTasksWithOperations TO DYNGRP
GO

Click to show/hide the Security Views For Use In SmartList Designer Series Index

● Categories: Dynamics, GP, Microsoft, SmartList, SmartList Builder, SmartList Designer, Third Party Add-on ● Tags: , , , , , , , ,  ● Permalink ● Shortlink ●