Deploy SQL View to All Databases

● Ian Grieve ●  ● 0 Comments   ● 

Microsoft Dynamics GPI have a few clients who have quite a few company databases in Microsoft Dynamics GP. One of them has well over a hundred live companies. This can make deploying reports somewhat long winded when you need to deploy an SQL view to all of the databases.

Fortunately, Microsoft SQL Server has ways and means which you can use to make the process a lot easier. In this case, I am using a SQL cursor to select all of the databases from the Company Master (SY01500) and loop through them to deploy the view; the deployment is in three phases:

  • Delete any existing view with the same name (this allows for an easy redeployment).
  • Create the view.
  • Grant the SELECT permission to DYNGRP.
  • The script is posted below with a simplified PO report being created; the view name is set in the highlighted parameter near the top of the script.

    The large highlighted section is where you please the content of the view which is to be deployed.
    Continue reading → Deploy SQL View to All Databases

    ● Categories: Dynamics, GP, Microsoft ● Tags: , , , , , , ,  ● Permalink ● Shortlink ●

    SQL View to Return Budgets By Month

    ● Ian Grieve ●  ● 0 Comments   ● 

    Microsoft Dynamics GPThe budget functionality in Microsoft Dynamics GP isn’t the strongest with reporting being particularly weak. The ability to report on budgets in Management Reporter does somewhat redeem this area of functionality.

    However, the absence of a SmartList Object for budgets is quite a big issue, as SmartList is a very nice flexible reporting tool which the majority of my clients know well. For those with SmartList Builder, it was easy enough to create a SmartList Object for them.

    With the introduction of SmartList Designer, we were able to roll out the SmartList budget report to all of the clients who wanted it.

    The script is below and returns the budget information with the beginning balance, 12 hard-coded periods and total horizontally across the page.

    Continue reading → SQL View to Return Budgets By Month

    ● Categories: Dynamics, GP, Microsoft ● Tags: , , , , , , , , , , , , ,  ● Permalink ● Shortlink ●

    Stored Procedure To Get Next Purchase Receipt Number

    ● Ian Grieve ●  ● 0 Comments   ● 

    Microsoft Dynamics GPThis stored procedure can be executed to generate the next sequential purchase receipt number which can be used for both receivings transactions (Shipment and Shipment/Invoice) and invoices; the generated invoice was then added to the integration file which was then submitted to eConnect. I’ve written this stored procedure at least three times for different integrations, so thought it best to post it here so I don’t write it again.

    -- drop stored proc if it exists
    IF OBJECT_ID (N'usp_AZRCRV_GetNextPOPReceiptNumber', N'P') IS NOT NULL
        DROP PROCEDURE usp_AZRCRV_GetNextPOPReceiptNumber
    GO
    -- create stored proc
    CREATE PROCEDURE usp_AZRCRV_GetNextPOPReceiptNumber 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).
    */
    BEGIN
    	DECLARE @return_value INT
    	DECLARE @I_vInc_Dec TINYINT = 1
    	DECLARE @O_vPOPRCTNM AS VARCHAR(17)
    	DECLARE @O_iErrorState INT
    
    	exec @return_value = taGetPurchReceiptNextNumber  @I_vInc_Dec, @O_vPOPRCTNM = @O_vPOPRCTNM OUTPUT,  @O_iErrorState = @O_iErrorState OUTPUT
    	SELECT @O_vPOPRCTNM
    END
    GO
    
    -- grant execute permission on stored proc to DYNGRP
    GRANT EXECUTE ON usp_AZRCRV_GetNextPOPReceiptNumber TO DYNGRP
    GO
    
    -- execute stored proc
    EXEC usp_AZRCRV_GetNextPOPReceiptNumber
    GO

    The stored proc calls a Microsoft Dynamics GP stored procedure which actually does the work, so we are still getting the receipt number using standard functionality.

    ● Categories: Dynamics, GP, Microsoft ● Tags: , , , , , , , ,  ● Permalink ● Shortlink ●

    Microsoft Dynamics GP 2016 R2 Financial Dashboard Error

    ● Ian Grieve ●  ● 1 Comment   ● 

    Microsoft Dynamics GPWhen reviewing some dashboards with a client a while ago we encountered an error launching the Financial Dashboard:

    Microsoft Excel - We found a problem with some content in 'T16R2 Financial Dashboard.xlsx'. Do you want us to try to recover as much as we can? If you trust the source of this workbook, click Yes.

    Microsoft Excel

    We found a problem with some content in 'T16R2 Financial Dashboard.xlsx'. Do you want us to try to recover as much as we can? If you trust the source of this workbook, click Yes.

    Continue reading → Microsoft Dynamics GP 2016 R2 Financial Dashboard Error

    ● Categories: Dynamics, Excel Reports, GP, Microsoft ● Tags: , , , , , ,  ● Permalink ● Shortlink ●

    “Service ‘GP OData Service (GPODataService) failed to start. Verify that you have sufficient privilages to start the system services.”

    ● Ian Grieve ●  ● 2 Comments   ● 

    Microsoft Dynamics GPWhen working on the Hands On With Microsoft Dynamics GP 2016 R2 series of posts, I installed the OData Service. While doing so, I received an error:

    Service 'GP OData Service' (GPODataService) failed to start. Verify that you have sufficient privileges to start the system services.

    Microsoft Dynamics GP OData Service

    Service 'GP OData Service' (GPODataService) failed to start. Verify that you have sufficient privileges to start the system services.

    This type of error is not atypical when working with new service accounts, which I was on the series of posts as it was a brand new virtual machine I was working on. Fortunately, with experience of working with Microsoft Dynamics GP for nearly 14 years, I have a little experience of resolving this type of error.

    In fact, I have blogged about it a couple of times. The first was back in March 2013; the instructions are towards the bottom of the post.

    ● Categories: Dynamics, GP, GP OData Service, Microsoft ● Tags: , , , , , ,  ● Permalink ● Shortlink ●

    MS Connect Suggestion: Allow Workflow Steps to be Copied

    ● Ian Grieve ●  ● 2 Comments   ● 

    Microsoft Dynamics GPI am on a bit of a kick with suggestions for improving Workflow 2.0 at the moment and have another MS Connect suggestion for you to vote for.

    The Workflow Maintenance window currently has the facility to copy an entire workflow process (including between companies), but does not have the facility to copy a workflow step; many times when I am creating a workflow process with, or for, a client we are creating many steps which only vary in the approver and part of the condition (for example a different site or segment in the account, and it would reduce the effort and time needed if the workflow step could be copied and amended, rather than created from scratch each time.

    You can vote for this suggestion here.

    ● Categories: Dynamics, GP, Microsoft, Microsoft Connect, Workflow ● Tags: , , , , , ,  ● Permalink ● Shortlink ●

    SQL Script To Return Functional Currencies For All Companies Without a Cursor

    ● Ian Grieve ●  ● 3 Comments   ● 

    Microsoft Dynamics GPI posted a script a while ago which used a cursor to return the functional currencies for all companies connected to a system database. However, I have recently revisited this script and created a version which does not use a cursor.

    This script has been written to only return the companies which do not have a functional currency set; if you want to see all companies, regardless of the functional currency, remove the highlighted section.

    /*
    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 TABLE #FunctionalCurrencies(
    	INTERID VARCHAR(5)
    	,FUNLCURR VARCHAR(20)
    )
    GO
    
    DECLARE @SQL NVARCHAR(MAX)
    SELECT @SQL = STUFF((
    					SELECT 
    						CHAR(13) 
    							+ 'SELECT 
    								''' + INTERID + '''
    								,FUNLCURR
    							FROM
    								' + INTERID + '.dbo.MC40000
    							WHERE
    								LEN(FUNLCURR) = 0'
    					FROM
    						DYNAMICS.dbo.SY01500
    					FOR XML PATH(''), TYPE).value('.', 'NVARCHAR(MAX)'), 1, 1, '')
    
    INSERT INTO #FunctionalCurrencies
    	EXEC sys.sp_executesql @SQL
    GO
    
    SELECT * FROM #FunctionalCurrencies
    GO
    
    DROP TABLE #FunctionalCurrencies
    GO
    ● Categories: Dynamics, GP, Microsoft, Multicurrency ● Tags: , , , , , ,  ● Permalink ● Shortlink ●

    SQL Script Linking Purchase Orders to Purchase Requisitions

    ● Ian Grieve ●  ● 1 Comment   ● 

    Microsoft Dynamics GPI have been doing a lot of work recently on Purchase Order Processing and Workflow 2.0. One of the requests I had was for a SmartList report which allowed the purchase order (PO) to be compared back to the purchase requisition (PR) it originated from. I had to take a few minutes to investigate as I knew the link from PR to PO was maintained within the system, but I wasn’t sure of the table.

    It took me a little longer to determine which table held the link as I was expecting a company table, but instead found that the link was stored in the SOP_POPLink table (SOP60100). One fairly quick view later and I have the basis for the required report, and probably for some others in future too.

    Continue reading → SQL Script Linking Purchase Orders to Purchase Requisitions

    ● Categories: Dynamics, GP, Microsoft ● Tags: , , , , , , , ,  ● Permalink ● Shortlink ●

    You Cannot Print An Unauthorized Purchase Order

    ● Ian Grieve ●  ● 0 Comments   ● 

    Microsoft Dynamics GPThe below error message was reported to me by a user performing UAT (User Acceptance Testing) on an implementation of Purchase Order Processing and Workflow 2.0:

    Microsoft Dynamics GP - You cannot print an unauthorized purchase order

    Microsoft Dynamics GP

    You cannot print an unauthorized purchase order

    This error was somewhat surprising as it was produced when trying to print a purchase order (PO) which had been converted from a purchase requisition (PR). There was a workflow process on the PR, which had been final approved and the PO created, but there was not a workflow on the PO.

    I double checked to make sure that the old Purchase Order Enhancements (POE) Approvals functionality was disabled (it was) as the customer had previously been using this.

    It was when I was checking for POE Approvals, that I realised that, as part of the project, we had enabled the PO Commitments part of POE.

    The warning message was being produced as the PO line had exceeded the commitment budget value. Loading the PO, clicking the save button, accepting the override of the commitment budget value, reloading the PO and clicking the Print button successfully printed the PO.

    ● Categories: Dynamics, GP, Microsoft ● Tags: , , , , , , , , , , ,  ● Permalink ● Shortlink ●

    SQL Function To Return Workflow Approval Status

    ● Ian Grieve ●  ● 5 Comments   ● 

    Microsoft Dynamics GPIf you have been paying any sort of attention, you will know that I do quite a lot of work with the Workflow 2.0 module of Microsoft Dynamics GP.

    We have created a number of reports in the past which allows the status of transactions or card going through a Workflow Approval process; to simplify matters somewhat, I created a SQL function which can be called with the Workflow Type Name and the Workflow Business Object Key and have the approval status returned.

    CREATE FUNCTION dbo.uf_AZRCRV_GetWorkflowApprovalStatus(@WorkflowTypeName CHAR(50),@WfBusObjKey CHAR(20))
    	RETURNS VARCHAR(14)
    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).
    
    Returns Workflow Approval status of a specified workflow item.
    
    Requires input parameters of WorkflowTypeName and WfBusObjKey
    
    Valid Workflow Type Names are (as of Microsoft Dynamics GP 2016 R2):
    	General Ledger Batch Approval
    	Receivables Batch Approval
    	Payables Batch Approval
    	Payables Transaction Approval
    	Purchase Order Approval
    	Purchase Requisition Approval
    	Vendor Approval
    	Employee Profile Approval
    	Employee Skills Approval
    	Payroll Direct Deposit Approval
    	Payroll Timecard Approval
    	Payroll W4 Approval
    	Expense Report Approval
    	Timesheet Approval
    	Smartlist Designer View Approval
    */
    BEGIN
    	RETURN ISNULL((
    		SELECT TOP 1 
    			CASE WHEN ['Workflow History'].Workflow_Action = 1 THEN
    				'Submitted'
    			WHEN ['Workflow History'].Workflow_Action = 2 THEN
    				'Resubmitted'
    			WHEN ['Workflow History'].Workflow_Action = 3  THEN
    				'Approved'
    			WHEN ['Workflow History'].Workflow_Action = 4 THEN
    				'Task Complete'
    			WHEN ['Workflow History'].Workflow_Action = 5 THEN
    				'Rejected'
    			WHEN ['Workflow History'].Workflow_Action = 6 THEN
    				'Delegated'
    			WHEN ['Workflow History'].Workflow_Action = 7 THEN
    				'Recalled'
    			WHEN ['Workflow History'].Workflow_Action = 8 THEN
    				'Escalated'
    			WHEN ['Workflow History'].Workflow_Action = 9 THEN
    				'Edit'
    			WHEN ['Workflow History'].Workflow_Action = 10 THEN
    				'Final Approved'
    			END
    		FROM 
    			WF30100 AS ['Workflow History']
    		INNER JOIN
    			WFI10002 AS ['Workflow Master']
    				ON
    					['Workflow Master'].WorkflowInstanceID = ['Workflow History'].WorkflowInstanceID
    		WHERE
    			['Workflow Master'].Workflow_Type_Name = @WorkflowTypeName
    		AND
    			['Workflow Master'].WfBusObjKey = @WfBusObjKey
    		ORDER BY
    			['Workflow History'].DEX_ROW_ID DESC)
    	,'Not Submitted')
    END
    GO
    
    GRANT EXECUTE ON uf_AZRCRV_GetWorkflowApprovalStatus TO DYNGRP
    GO

    This view can be called from a SmartList created using either SmartList Designer, SmartList Builder, via a Reporting Services or Excel Report or any other type of report.

    ● Categories: Dynamics, GP, Microsoft, Workflow ● Tags: , , , , , , , , , , , ,  ● Permalink ● Shortlink ●