How to print a Purchase Order using DRM and update the Purchase Order Status Flag.

Issue:

The Purchase Order Status does not change from New to Released after printing an alternate Purchase Order report using Dynamics Report Manager (DRM).

Cause: 

A custom stored procedure is needed to return the data for the report and then update the Purchase Order Status flag. Once this is created and the report is setup to use the stored procedure, the report should run correctly and the Purchase Order Status Flag should be changed. 

Resolution:

1. Write a custom stored procedure to return the data for the report to run, and at the same time, have it run an update on the Purchase Order Status flag.  
   1. The Stored Procedure should receive the PO Number as the parameter. 
   2. Here is an example of a Stored Procedure for a Crystal Report or an SRS Report, which will return the data for the report and then update the Purchase Order Status flag.  Important Note: The script updates just the Purchase Order header record. It is a sample ONLY and may not accomplish updating all the data that need to be updated. If you need the script to update more, you will need to write it or you can contract it with Rockton Software to be written.
      –run against company database
      –EXEC spPOStatusFlag ‘PO2076’
      IF EXISTS(SELECT * FROM sysobjects WHERE NAME = ‘spPOStatusFlag’) DROP PROC spPOStatusFlag
      GO
      CREATE PROC spPOStatusFlag
      (@PONumber CHAR(17))
      AS
      BEGIN
      UPDATE POP10100 SET
      LSTPRTDT = CAST (STR(MONTH(GETDATE())) + ‘/’ + STR(DAY(GETDATE())) + ‘/’ + STR(YEAR(GETDATE())) AS DATETIME),
      POSTATUS = 2,
      TIMESPRT = TIMESPRT + 1,
      PO_Status_Orig = 1
      — UNSURE OF FIELDS ONORDAMT, ORORDAMT, AND PO_Field_Changes
      WHERE PONUMBER = @PONumber
      SELECT * FROM POP10100 WHERE PONUMBER = @PONumber
      END
      GO
      GRANT EXECUTE ON spPOStatusFlag TO DYNGRP
      GO 
2. Create the Crystal Report or SRS Report using the stored procedure you created in step 1.  
3. Create a DRM Report for the Crystal Report you created in step 2. For more information on creating a DRM report, download the DRM manual from our Product Downloads page: https://www.rocktonsoftware.com/gp/resources/product-downloads.