“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 ●

    MS Connect Suggestion: Include Multicurrency Vouchers in Select Checks Window

    ● Ian Grieve ●  ● 1 Comment   ● 

    Microsoft Dynamics GPThis suggestion is one from MVP Mariano Gomez to improve the payment run process by allowing multicurrency invoices to be included.

    Currently, the payment selection process only allows vouchers issued in the batch currency to be selected and added to the check run. If the user wants to add foreign currency vouchers, he/she needs to use the Edit Checks window to add such vouchers. It would be nice to be able to have an option to include functional currency vouchers in the case of a foreign currency batch, or foreign currency vouchers in the case of a functional currency batch.

    This is the use case:

    Vendor A Invoice 1 CA$10.00
    Vendor A Invoice 2 US$20.00
    Vendor A Invoice 3 US$30.00

    If a payment selection is performed and the batch currency is CA$, only the first voucher will be selected. To add Invoice 2 and 3, you would have to use the Edit Checks window to include both vouchers.

    Conversely, if the batch currency is US$ and the payment selection for vendor A is performed, only invoices 2 and 3 would be selected and you would have to use the Edit Checks window to include Invoice 1.

    What Mariano would like to see is all invoices selected (based on selection rules) and let the user deal with the exceptions, as it should be.

    As mentioned, all the logic is already there via the Edit Checks window and it would be nice to incorporate as part of the payment selection process. As others have mentioned, having multicurrency vouchers included could be a payables configuration item or simply an additional option in the Select Payments (Select Checks) window.

    Mark Polino has also suggested that this functionality be optioned with Rod O’Connor further suggesting the option be on the Select Checks window itself to allow maximum flexibility. Both of these seem like a good addition to Mariano’s idea.

    The suggestion can be voted on here.

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

    Historical Excel Reporting Price Increase Coming

    ● Ian Grieve ●  ● 3 Comments   ● 

    Microsoft Dynamics GPMark 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.

    ● Categories: Dynamics, GP, Microsoft, Third Party Add-on ● 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 ●

    SQL Script To Update EFT Payment Register Report

    ● Ian Grieve ●  ● 2 Comments   ● 

    Microsoft Dynamics GPPretty much every client we have of Microsoft Dynamics GP uses the EFT Payment Register Report; a number of the save the file instead of printing it as the report has been customised to generate the EFT payment file for upload to the bank.

    The clients who do this are either those whose installations of Microsoft Dynamics GP predate version 10 when the EFT for Payables Management module was made available to the UK market or they implemented before the EFT File Format was enhanced to allow a CSV output.

    While some clients have created or amended a file format since the above, not all of them have been willing to spend the time (or money) to make the change.

    As such, when they copy live to test they need to amend the path to which the report is being output, to ensure that a live file is not accidentally overwritten.

    To faciliate this for users who have automated the live to test restore I created a script they could build into the process:

    /*
    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
    	SY02200
    SET
    	FILEXPNM = 'C:\BACS OUTPUT\BACS.TXT'
    WHERE
    	PTGRPTNM = 'EFT Payment Register'

    The highlighted section is the path name which needs to be changed depending on where the test file is to be output.

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