Enabling the PO Email Document Without an Exchange Account.

● Ian Grieve ●  ● 1 Comment   ● 

Microsoft Dynamics GPI am very much an advocate for using the Microsoft Dynamics GP application for making changes to configuration or data rather than jumping straight to the SQL database. However, sometimes needs really must.

I was onsite with a client a few weeks ago to assist them in implementing Purchase Order Processing and, as part of this implementation, I needed to enable the emailing of the purchase order. Typically, I would do this through the Company E-mail Setup (Administration >> Setup >> Company >>
E-mail Setup
)) window, but the client is configured to use Exchange for emails and I don’t have an Exchange account on their system.

Due to my location compared to the users, it was quicker to create an SQL script to enable the email rather than trying to locate a user with the relevant permissions to screen share with:

UPDATE
	SY04903
SET
	EmailDocumentEnabled = 1
	,EmailMessageID = 'PO'
WHERE
	EmailDictionaryID = 0
AND
	EmailSeriesID = 4
AND
	MODULE1 = 12
AND
	EmailDocumentID = 1

The highlighted section is the name of the Message ID for the PO.

As always before running a script, ensure you test the script and have a good backup of your company database.

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

“You cannot receive against unauthorised purchase orders.”

● Ian Grieve ●  ● 0 Comments   ● 

Microsoft Dynamics GPI am working with a client at the moment on a large scale roll-out of Purchase Order Processing with a large, complex Workflow approval process. The project started towards the end of the last financial year and into the current one. While users were performing UAT, an issue suddenly arose where a goods receipt notes could not be entered in Receivings Transaction Entry (test). When the user tried, they received the following error:

You cannot receive against unauthorised purchase orders.

Microsoft Dynamics GP

You cannot receive against unauthorised purchase orders.

Continue reading → “You cannot receive against unauthorised purchase orders.”

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

Deploy SQL View to All Databases

● Ian Grieve ●  ● 1 Comment   ● 

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 ●

    Stored Procedure To Get Next Purchase Receipt Number

    ● Ian Grieve ●  ● 1 Comment   ● 

    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 ●

    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 ●

    Purchase Order Approval Workflow Automatically Rejected

    ● Ian Grieve ●  ● 4 Comments   ● 

    Microsoft Dynamics GPA client reported a problem with their PO Approval Workflow process failing whenever a PO was submitted for approval. As soon as the Submit button was pressed, the document was rejected, but did not show a user:

    Purchase Order Entry

    Continue reading → Purchase Order Approval Workflow Automatically Rejected

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

    Nothing Happens When Submitting A Purchase Requisition For Approval

    ● Ian Grieve ●  ● 3 Comments   ● 

    Microsoft Dynamics GPI was onsite with a client to start a project implementing Workflow 2.0 for Purchase Requisition Approval and ran into a problem while doing training on how to interact with the workflow.

    Everything had been fine until this point, but after creating a simple example approval workflow, I clicked the Submit button on the Purchase Requisition Entry window and nothing happened; I tried again through the navigation list, again without success.

    I promised to figure out the issue in the next break and moved onto discussing other ways of interacting and then called the break.

    It occurred to me that this might be another manifestation of an issue I have encountered previously, but without the visible error message.

    I ran the script against the system database which redeploys the .NET Assemblies used by Workflow:

    exec wfDeployClrAssemblies

    It took a few minutes to run through, but I was then able to approve the requisition without further problem.

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

    Hands On With Microsoft Dynamics GP 2016 R2: POP To FA Link Includes Taxes

    ● Ian Grieve ●  ● 3 Comments   ● 

    Microsoft Dynamics GPWith the release of Microsoft Dynamics GP 2016 R2 it’s time for a series of “hands on” posts where I go through the installation of all of it’s components and also look at the new functionality introduced; the index for this series can be found here.

    The thirteenth Feature of the Day I am going “hands on” with is POP to FA Link to Include Taxes.

    This feature will be popular with some of our clients who deal with non-recoeverable VAT (VAT is a UK sales tax charged at a standard rate of 20%) as non-rec VAT means they would need to depreciate the gross value of the asset including VAT where in previous versions the asset would be created net and need to be amended.

    To use this feature, the Include Tax in Acquisition Cost checkbox on the Fixed Assets Company Setup window (Financial >> Setup >> Fixed Assets >> Company) under the Purchasing Options:

    Fixed Assets Company 	Setup

    Continue reading → Hands On With Microsoft Dynamics GP 2016 R2: POP To FA Link Includes Taxes

    ● Categories: Dynamics, Fixed Asset Management, GP, Microsoft ● Tags: , , , , , , , , , , , , ,  ● Permalink ● Shortlink ●

    Hands On With Microsoft Dynamics GP 2016 R2: Cancel A Purchase Order Linked To A Purchase Requisition

    ● Ian Grieve ●  ● 4 Comments   ● 

    Microsoft Dynamics GPWith the release of Microsoft Dynamics GP 2016 R2 it’s time for a series of “hands on” posts where I go through the installation of all of it’s components and also look at the new functionality introduced; the index for this series can be found here.

    The third feature I’m going to take a look at is the Cancel Purchase Orders Linked To A Requisition. For those doubting why this feature was needed, in previous versions of Microsoft Dynamics GP, when yuo tried to cancel a quantity on a purchase order you would receive a message if the remaining quantity didn’t cover the requisition quantity:

    Microsoft Dynamics GP: The quantity can

    Continue reading → Hands On With Microsoft Dynamics GP 2016 R2: Cancel A Purchase Order Linked To A Purchase Requisition

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