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.


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

Inventory Transaction Analysis - Unique Transactions 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. 

The queries identify the number of unique transactions and the number of lines in the table. Dividing one by the other will reveal the average lines per transaction.  

This query reveals the Number of Inventory Transactions processed on a given day and the number of overall lines.

DECLARE @Date as DATETIME
SET @Date = '04/12/2017'

/* Inventory Transactions */
Select SUM(a.IV_Trxs) IV_Trxs from (
select count(I.IV_TRXS) IV_Trxs from (
select count(IVDOCNBR) IV_Trxs from IV10001 group by IVDOCNBR) I
Union All
select COUNT(IH.IV_TRXS) IV_Trxs from (
select COUNT(DOCNUMBR) IV_Trxs from IV30300 WHERE DOCDATE = @Date group by DOCNUMBR) IH ) a
/* Inventory Transaction Lines */
select SUM(IV_Trxs) from (
select COUNT(I.IVDOCNBR) IV_Trxs from IV10001 I
Union All
Select COUNT(DOCNUMBR) IV_Trxs from IV30300 where DOCDATE = @Date) a

Thursday, October 30, 2014

Payables Transaction Analysis - Unique Purchasing Transactions per day

Recently I answered a forum post by 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.

This query identifies the number of unique transactions in the appropriate tables (Open or Historical), and reveals the Number of Purchasing Transactions processed on a given day.

declare @Date as DATETIME
SET @Date = '04/12/2017'

/*Payables Transactions*/
Select SUM(PM_Trxs) PM_Trxs from (
Select COUNT(vchrnmbr) PM_Trxs FROM PM20000 where DOCDATE = @Date
Union All
Select COUNT(vchrnmbr) PM_Trxs FROM PM30200 where DOCDATE = @Date) a

Purchase Order Entry Transaction Analysis - Unique Purchase Orders and Total Lines

Recently I answered a forum post by 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. 

The queries identify the number of unique transactions and the number of lines in the appropriate tables (Open or Historical). Dividing one by the other will reveal the average lines per transaction.  

This query reveals the Number of Purchases Orders processed on a given day and the number of overall lines.

declare @Date as DATETIME
SET @Date = '04/12/2017'

/*Purchase Orders*/
select sum(a.Sales_Orders) Purchase_Orders from (
Select count(P.Sales_Orders) Sales_Orders  from (Select COUNT(PL.PONUMBER) Sales_Orders from POP10100 PH
Inner Join POP10110 PL
on PH.PONUMBER = PL.PONUMBER
and PH.POTYPE = PL.POTYPE
where PH.DOCDATE = @Date
group by PL.PONUMBER) P
Union All
Select count(P.Sales_Orders) Purchase_Orders  from (Select COUNT(PL.PONUMBER) Sales_Orders from POP30100 PH
Inner Join POP30110 PL
on PH.PONUMBER = PL.PONUMBER
and PH.POTYPE = PL.POTYPE
where PH.DOCDATE = @Date
group by PL.PONUMBER) P) a
/*Purchase Lines*/
select sum(a.SOP_Lines) POP_Lines from (
Select COUNT(PH.PONUMBER) SOP_Lines from POP10100 PH
Inner Join POP10110 PL
on PH.PONUMBER = PL.PONUMBER
and PH.POTYPE = PL.POTYPE
where PH.DOCDATE = @Date
Union All
Select COUNT(PH.PONUMBER) POP_Lines from POP30100 PH
Inner Join POP30110 PL
on PH.PONUMBER = PL.PONUMBER
and PH.POTYPE = PL.POTYPE
where PH.DOCDATE = @Date) a

Sales Transaction Entry Open and History Files Transaction Volume Analysis

Recently I answered a forum post by 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. 

The queries identify the number of unique transactions and the number of lines in the table. Dividing one by the other will reveal the average lines per transaction.  

This query reveals the Number of Sales Transactions processed on a given day and the number of overall lines.


DECLARE @Date as DATETIME

SET @Date = '04/12/2017'

/*Sales Orders*/
select sum(a.Sales_Orders) Sales_Orders from
(Select count(s.Sales_Orders) Sales_Orders  from (Select COUNT(sl.SOPNUMBE) Sales_Orders from SOP10100 SH
Inner Join SOP10200 SL
on SH.SOPNUMBE = SL.SOPNUMBE
and SH.SOPTYPE = SL.SOPTYPE
where sh.DOCDATE = @Date
group by SL.SOPNUMBE) S
Union All
Select count(s.Sales_Orders) Sales_Orders  from (Select COUNT(sl.SOPNUMBE) Sales_Orders from SOP30200 SH
Inner Join SOP30300 SL
on SH.SOPNUMBE = SL.SOPNUMBE
and SH.SOPTYPE = SL.SOPTYPE
where sh.DOCDATE = @Date
group by SL.SOPNUMBE) S) a
/*Sales Lines*/
select sum(a.SOP_Lines) SOP_Lines from
(Select COUNT(SH.SOPNUMBE) SOP_Lines from SOP10100 SH
Inner Join SOP10200 SL
on SH.SOPNUMBE = SL.SOPNUMBE
and SH.SOPTYPE = SL.SOPTYPE
where sh.DOCDATE = @Date
Union All
Select COUNT(SH.SOPNUMBE) SOP_Lines from SOP30200 SH
Inner Join SOP30300 SL
on SH.SOPNUMBE = SL.SOPNUMBE
and SH.SOPTYPE = SL.SOPTYPE
where sh.DOCDATE = @Date) a

General Ledger Transactions and Line Totals per Day

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

The queries identify the number of unique transactions and the number of lines in the table. Dividing one by the other will reveal the average lines per transaction.  

This query reveals the Number of General Ledger Journal Entries on a given day and the number of overall lines.

DECLARE @Date as DATETIME
SET @Date = '04/12/2017'

/*Journal Entries*/
select sum(a.Journal_Entries) GL_Transactions from (
select count(c.GL_Transactions) Journal_Entries from (Select COUNT(jrnentry) GL_Transactions FROM GL20000 where TRXDATE = @Date group by JRNENTRY) c
Union All
select count(c.GL_Transactions) Journal_Entries from (Select COUNT(jrnentry) GL_Transactions FROM GL30000 where TRXDATE = @Date group by JRNENTRY) c ) a
/*GL Transaction Lines*/
select SUM(a.GL_Trx_Lines) GL_Trx_Lines from (
Select count(JRNENTRY) GL_Trx_Lines FROM GL20000 where TRXDATE = @Date group by TRXDATE
Union All

Select count(JRNENTRY) GL_Trx_Lines FROM GL30000 where TRXDATE = @Date group by TRXDATE) a

Wednesday, October 29, 2014

How to Maximize Material Utilization by Nesting

Manufacturers are always keen to maximize utilization of materials.   Consequently, when making smaller components from large raw materials, it pays big to nest smaller parts in the void spaces created by the production of larger ones.

A Bill of Materials typically corresponds to a single made item (finished good).  Consequently, the conundrum of how to report production of several items from a single work order is a perplexing one.  We came up the following solution. WilloWare.com sells a tool, called Quick Disassembly, this recommendation would require the purchase of this tool - Accellos,com also sells such a tool as part of their Bar Coding integration to Dynamics GP Manufacturing.

Here's a brief overview of the main points of this solution:
  1. Configure the Large Raw Material as a Make/Buy item in Item Engineering Data.
  2. Create an Archive Bill of Material for each configuration of cuts (nested smaller finished goods).
  3. Purchase the Large Raw Material
  4. Create disassembly transactions using the ARCHIVE Bill of Materials to convert the large raw material into the numerous smaller finished goods.
  5. Using SmartLists and WilloWare's BOM Import utility, this process can be done, on-the fly for ad-hoc configurations.

Example:

A single sheet of MDF is approximately 60 x 120 inches.  In order to optimize consumption of the material, MDF is typically cut into several disparate “nested” items simultaneously.  For instance:

2 x T-5s and 2 x T-4s – the most common scenario

Or 

2 x T-5s and 4 x T-3s – the least common scenario

Figure 1

As you can see from the figure above, to optimize the yield from MDF materials, a decision must be made as to what additional items should be made when T-5s are made.

Our recommendation is to create an ARCHIVE Bill of Materials for each configuration of cuts:

1.     T-5A = 2 x T-5s and 2 x T-4s
2.     T-5B = 2 x T-5s and 4 x T-3s

Sheets of MDF are purchased and Quick Disassembly transactions are made for T-5As or T-5Bs resulting in the consumption of the MDF and production of the required T-5's and the additional T-3's or 4's.

Item BOM        SUB        Components

T-5A----|
            |--- T-5 x 2 --- Usable sqft of MDF
            |
            |--- T-4 x 2 --- Usable sqft of MDF


T-5B----|
            |--- T-5 x 2 --- Usable sqft of MDF
            |
            |--- T-3 x 4 --- Usable sqft of MDF

Quick Disassembly by WilloWare uses the GP Manufacturing Bill of Materials to disassemble inventory items into component parts. This tool typically creates a simple disassembly transaction based on an Inventory Adjustment, and is ideal for manufacturers needing a simple method to take apart an item and get the components back into inventory.

SQL Query MOJO - Use Excel to build a redundant query

From time to time, I find it necessary to create a query, which has a redundant element.  Something like a user hands me an Excel based list, which contains customer email addresses...  I am sure I am not the first person to figure this out, but I was quite pleased with myself when I did.

It is possible to use Excel's concatenation feature to build a redundant query. In the example below, I was asked by a new controller to tell them what the first year each company database was placed in use, at an organization which had dozens of company databases.  I did a simple select statement for INTERID on the SY01500 table to create a list of company database ID's as a foundation for this query. 



I then pasted the ID's into Excel and wrote a query using the list field to pull the first financial year from each database.  The list of databases in my test system is short, and I don't want to publish a client's list of company database ID's so my example only includes two databases. 

Foundation Query for Historical Fixed Asset Depreciation Reporting in SSRS or SLB

Prior to version 2013 of Dynamics GP there was not Historical Depreciation Reporting.  Here is a query, which can be used as a foundation for reporting on depreciation using SmartList Builder or SQL Server Reporting Services (SSRS)


DECLARE @Year INT, @StartPeriod TINYINT, @EndPeriod TINYINT
Set @Year = '2015' --Sets the Year of the Report
Set @StartPeriod = '1' --Sets the Starting Period of the Report
Set @EndPeriod = '12' --Sets the Ending Period of the Report

select
FAM.ASSETID Asset_ID,
FAM.ASSETIDSUF Asset_Suffix,
FAM.ASSETDESC Asset_Description,
GLT.FAPERIOD 'Period',
GLT.FAYEAR 'Year',
--GLT.FA_Doc_Number, --Removes this column, not in versions prior to 2013
convert(char(10),GLT.DEPRFROMDATE,101) Dep_From_Date,
convert(char(10),GLT.DEPRTODATE,101) Dep_To_Date,
GLD.ACTNUMST Account,
GLM.ACTDESCR Acct_Description,
cast(GLT.AMOUNT as money) Amount
from FA00902 GLT
left join FA00100 FAM on GLT.ASSETINDEX = FAM.ASSETINDEX
left join GL00100 GLM on GLT.GLINTACCTINDX = GLM.ACTINDX
left join GL00105 GLD on GLT.GLINTACCTINDX = GLD.ACTINDX
where FAYEAR = @Year
and FAPERIOD between @StartPeriod and @EndPeriod

Order by GLT.FAPERIOD,FAM.ASSETID,FAM.ASSETIDSUF,GLD.ACTNUMST

Tuesday, October 28, 2014

Inventory Inquiry Allocated Total does not match Item Allocation Inquiry Total on Drill Down

A couple of years ago, a client who uses Dynamics GP 2010 Manufacturing discovered a problem. The amount Allocated in the Inventory Inquiry window disagreed with the Drill Down detail in the Item Allocation Inquiry window for some items. What was obvious was Numerous Manufacturing Orders were throwing off the totals in the Item Allocation Inquiry window. What we discovered was Item Allocation was accurate in the Inventory Inquiry window. True Item Allocation at the Inventory Control level appears to be correct, but the drilldown in the Item Allocation Inquiry window details was incorrect.


Most of the client’s operation was Make-to-Order manufacturing. As Manufacturing Orders were released, Dynamics GP allocated component inventory, as it was configured to do. With a high order volume/low order value and liberal cancellation policy, numerous orders were cancelled at various stages, using different methods, including an aborted attempt to cancel orders using custom code, which was meant to provide a single method to cancel Manufacturing Orders regardless of the production stage.

After much research, thought, discussion and data validation, we created a query to identify the records appearing in the Item Allocation Inquiry window, which are not included in the Allocation Totals in Item Inquiry window. Furthermore, we believe we have identified why the problem occurred.

The detail of the “issue” causing this is as follows:

When an MO is cancelled, typically, a Reverse Allocation entry is written to the MOP1210 (MOP Pick Line) table to properly offset the original allocation transaction written to the MOP1210 table at Manufacturing Order Release. This record adjusts the total allocation to zero. 

The problem appears to be, when some Manufacturing Orders were cancelled, the reverse allocation entry to MOP1210 table was not entered.  This presents a problem, in that the Great Plains method of pulling information into the Item Allocation Inquiry window verifies a cancelled MO also has the Reverse Allocation entry in these tables, and when it does not – the MO continues to appear in the inquiry window.

The only way to keep the cancelled Manufacturing Orders for items from appearing in the Item Allocation Inquiry window, which meet this criteria is to Close the problem Manufacturing Orders. Alternatively, knowing what we know, we could update the SQL tables holding the Picklist data that is incorrect – we felt this approach is not advisable, because it is performed outside the system; therefore, bad practice and hard to justify to auditors.

This query identifies the MO#, Item number and Quantity. When we cross referenced this data to the Quantity Master Allocation and the data in Item Allocation Inquiry, it proved to be the missing link.  This approach not only tells us, which inventory items have allocation issues, it also identifies the specific Manufacturing Orders causing the allocation issues, removing the necessity of either having someone review the allocation details for problem items and closing all cancelled work orders related to problem items.

Select  WO.MANUFACTUREORDER_I MO#,
            MPL.ITEMNMBR Item#,
            WO.STRTDATE 'Start_Date',
            MPL.TRXQTY-MPL.QTY_ISSUED_I Allocation_Detail,
            case WO.MANUFACTUREORDERST_I
                  when 1 then 'Quote/Estimate'
                  when 2 then 'Open'
                  when 3 then 'Released'
                  when 4 then 'Hold'
                  when 5 then 'Cancelled'
                  when 6 then 'Complete'
                  when 7 then 'Partially Received'
                  when 8 then 'Closed'
            END MO_Status 
from MOP1210 MPL, MOP1200 MPH, WO010032 WO
where WO.MANUFACTUREORDER_I = MPL.MANUFACTUREORDER_I
      and WO.MANUFACTUREORDERST_I = 6
      and MPL.PICKNUMBER = MPH.PICKNUMBER
      and MPL.TRX_TYPE in (3)
      and MPH.posted = 0
      and (MPL.TRXQTY-MPL.QTY_ISSUED_I) > 0
     
--alternate form of the query, may produce false positives

Select Trx_Qty,Issue_Qty, Trx_Qty-Issue_Qty as Allocation_Detail, (IA.ATYALLOC-(Select SUM(ATYALLOC) from SOP10200 where ITEMNMBR = IA.ITEMNMBR)) as Item_Allocation, rtrim(IA.ITEMNMBR) as Item#  from
(
select rtrim(MPL.ITEMNMBR) as Item# ,SUM(MPL.TRXQTY) as Trx_Qty, sum(MPL.QTY_ISSUED_I) as Issue_Qty  from MOP1210 MPL (Nolock)
      inner join WO010032 WO (Nolock)
      on WO.MANUFACTUREORDER_I = MPL.MANUFACTUREORDER_I
      inner join MOP1200 MPH (Nolock)
      on MPH.PICKNUMBER = MPL.PICKNUMBER
      and WO.MANUFACTUREORDERST_I <> 8
      and MPH.POSTED = 0
      and MPL.TRX_TYPE = 3
group by MPL.ITEMNMBR
) ALLOCATIONS,
IV00102 IA where IA.ITEMNMBR = ALLOCATIONS.Item#
and IA.LOCNCODE = 'WAREHOUSE'
and (Trx_Qty-Issue_Qty) != (IA.ATYALLOC-(Select SUM(ATYALLOC) from SOP10200 where ITEMNMBR = IA.ITEMNMBR))
order by ITEMNMBR

Because of the specificity of this query, which identifies the actual problem Manufacturing Orders, the number of records (specific MOs) identified by the new query is significantly lower than the number originally identified using our previous approach, which was to identify all open Manufacturing Orders for items, which met the out of balance criteria (6081).  There were approximately 1600 Manufacturing Orders, which needed to be closed manually (1670); less than 30% of the open order total.

Considering the drastic reduction in the number of work orders, which need to be cancelled, and the hours we invested in the analysis, and the risk associated with automation, we concentrated on the manual process to close the MOs, as opposed to continuing work on automation.

For reference I am including the logic that GP uses to fill the Item Allocation Detail window.

There is more to it, but this is where the problem is:
The MO has a status <> 8 (Closed) in the WO010032 table 
The MOP1210 has a TRX_TYPE of 1 or 3 (Allocation) and 
The quantity allocated in MOP1210 table TRXQTY-QTY_ISSUED_I > 0

When an MO is cancelled, the appropriate logic should be:
Marks the MO as cancelled in WO010032 (Updates Status = 5)
Creates a Reverse allocation entry in MOP1210
This is missing on all problem MOs – TRX_TYPE of 4
Change QTY_ISSUED of the original allocation entry in MOP1210 to the TRXQTY 
This is also missing on the problem MOs


In the end, the initial finding proved out: True Item Allocation at the Inventory Control level appears to be correct, and just the drill down in the Item Allocation Inquiry window details were incorrect. The issue was caused by Great Plains failing to create an offsetting transaction in the MOP1210 table, complicated by a design flaw in the Inventory Allocation Detail window which basically expects work orders to be closed after cancellation.