Friday, October 31, 2014

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.

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
and CHANGEDATE_I = @Date

/* Work Orders Released - Based on WO Start Date */
Select COUNT(MANUFACTUREORDER_I) Released_MOs from WO010032 WO

No comments:

Post a Comment