Fixed Asset Transaction Analysis - Transaction 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.  

The Fixed Asset tables presented a unique challenge.  Transactions are written to the FA00902 table, and have either been interfaced with the General Ledger or not, so there is no easy way to identify transactions for a specific date. 

I came up with an approach I think works best.  The following queries return Transactions Not Interfaced with the GL - these all have the same GL Interface Date (GLINTTRXDATE) value of 1900-01-01 00:00:00.000. Transactions that have already been interfaced with the GL can be identified by a specific date, but will generally have been interfaced at a month-end.

This query reveals the Number of Fixed Asset Transactions, which have not been interfaced with the GL and interfaced transactions on a given day, as well as the number of overall lines.

/* Fixed Asset Transactions NOT Interfaced with GL */

select COUNT(FA_Trxs) FA_Trxs from (

select count(FA_Doc_Number) FA_Trxs from FA00902

Where GLINTTRXDATE = '01/01/1900'

Group by FA_Doc_Number ) FAT

/* Fixed Asset Transaction Lines NOT Interfaced with GL */

Select COUNT(FA_Doc_Number) FA_Trx_Lines from FA00902

Where GLINTTRXDATE = '01/01/1900'

DECLARE @Date as DATETIME

Set @Date = '12/31/2016'

/* Fixed Asset Transactions Interfaced with GL */

select COUNT(FA_Trxs) FA_Trxs from (

select count(FA_Doc_Number) FA_Trxs from FA00902

Where GLINTTRXDATE = @Date

Group by FA_Doc_Number ) FAT

/* Fixed Asset Transaction Lines Interfaced with GL */

Select COUNT(FA_Doc_Number) FA_Trx_Lines from FA00902

Where GLINTTRXDATE = @Date

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