Manufacturing Orders Released per day - SQL

Recently I answered a forum post for a user who wanted to know the number of transactions by module entered in a given day.  I authored a series of queries which allow a user to input a date and execute the query. 

These queries identify the number of unique transactions based on various definitions of release date.  

First, a record is written to the MOP10213 table (MO Activity), when a Manufacturing Order is release. By selecting type 3 Manufacturing Order Status (MANUFACTUREORDERST_I), which is Released and the CHANGEDATE_I value, you can capture the actual date the Release transaction occurred. This is Work Orders Released - Based on Change Date in MOP Activity Table.

Second, a the Manufacturing Order Start Date, which may be a future date, at the time the MO is released can be instructive as to what the Manufacturing Order volume is for a given date, rather than the date the Manufacturing Order release transaction occurred.

For instance, lets imagine a planner comes in on Saturday, and releases a number of Manufacturing Orders with a Monday Start Date.  These queries can show both the transaction date of the Manufacturing Orders release and the actual release date (planned start date) of the Manufacturing Orders.  This allows you to determine which is the most relevant to your analysis.

DECLARE @Date as DATETIME

SET @Date = '04/12/2017'

/* Work Orders Released - Based on Change Date in MOP Activity Table */

Select Count(Released_MOs) Released_MOs from (select COUNT(MANUFACTUREORDER_I) Released_MOs from MOP10213

where MANUFACTUREORDERST_I = 3

and CHANGEDATE_I = @Date

Group By MANUFACTUREORDER_I) a

/* Work Orders Released - Based on WO Start Date */

Select COUNT(MANUFACTUREORDER_I) Released_MOs from WO010032 WO

WHERE STRTDATE = @Date

CASE Statement for Manufacturing Order Status in Dynamics GP (MANUFACTUREORDERST_I)

I have amassed a number of useful CASE statements, which can be incorporated into SQL queries run against Dynamics GP. When Querying the Manufacturing Order Status Field in Dynamics GP (MANUFACTUREORDERST_I): use the following case statement to return text versions of the default statuses in Dynamics GP:

case WO010032.MANUFACTUREORDERST_I

      when 0 then 'invalid entry'

      when 1 then 'Quote/Estimate'

      when 2 then 'Open'

      when 3 then 'Released'

      when 4 then 'Hold'

      when 5 then 'Canceled'

      when 6 then 'Complete'

      when 7 then 'Partially Received'

      when 8 then 'Closed'

      else 'ERROR'

End MO_Status

Here is a list of the Dynamics GP Tables containing MANUFACTUREORDERST_I

ISSP0201 – Manufacturing Series Sales Order Preferences

MOP10213 – MOP Order Activity

MOP1070 – MOP Edit MO Status Header

MOP1071 – MOP Edit MO Status Line

MOP3000 – MOP Variances

MOPGetMOVariances

MOPV1100 - MFGMOList

MOPV3001

MPO10001 – MRP CRP Scheduled Orders

mvarMOStartEndQtys

PK010033 – MOP Item Master

SOPS113B – MOP Sys Preferences

WO010032 – Manufacture Order Master

WO010213 – Manufacture Order History