HITB Quanity On Hand vs. Inventory Transaction History Issue

When reconciling Inventory Count Sheets Quantity On Hand to the Historical Inventory Trial Balance Quantities on Hand, we discovered a number of Items which did not reconcile.   

I will start by saying this was disconcerting. It is hard to explain to a client why a tool promoted for use in the reconciliation process does not reconcile. The first step was obvious - run the Inventory Reconcile Utility, which fixed some of the problems but not all. Not being one to simply shrug and say "meh" I dived into the problem. Detailed analysis of the data in the SEE30303 (HITB) and IV30300 (IVTH) indicated there were quality issues with the data. 

Once it was clear there were problems with data, I had to acknowledge we'd need to figure out the full measure of these problems.  after initialization of HITB, the SEE30303 (HITB) and IV30300 (IVTH) should contain the same detailed data. This prompted me to create an Except query to compare the detailed data contained in these tables.  

NOTE:  There were differences in the detailed records written in both these tables, which caused mismatches - summarizing the data by Item Number, Document Number, Document Date and Transaction Source resolved this issue.  Failure to implement this approach produced massive amounts of transaction mismatch data, many of which were false positives.

Analysis indicated these tables did not reconcile because of the following causes:

1. There were a number of items, which initially failed to reconcile, but when the Inventory Reconcile Utility was run on these items, the error was corrected by the system. 
2. There were a number of records in the SEE30303 (HITB) table, which contained transactions with a GLPOSTDT (GL Post Date) of 1900-01-01 00:00:00.000, which is Great Plains’ mechanism of denoting transactions have never been posted.   Because of this, Great Plains did not show these transactions on the HITB report. I found this particularly interesting, as the HITB report pulls data from a table which should contain only posted transactions, who would think to restrict this report to not include unposted data? Inventory transactions are initially written to the IV10001 (IVTA – Inventory Transaction Activity) table, and are moved to the IV30300 table only after they are posted.  The fact transactions exist in IV30300 (IVTH – Inventory Transaction History) table indicates, these transactions were posted.  In this case, they were also written to the HITB table; however, they GLPOSTDT field was not updated correctly – likely due to a malfunction in the software or network error.
3. There were a number of records, which were written to the IV30300 (IVTH) table, which were not written to the SEE30303 (HITB) table.  
4. During the research phase which identified the causes listed above, we identified two disparate scenarios, which do not fit within these causes:

a.   There are matching transactions in either table… the IV30300 and SEE30303 tables reconcile to one another; however, the Inventory Quantity On Hand and Historical Inventory Trial Balance reports have disparate totals.

b.   There are reported missing transactions (mismatched records) in either table…  IV30300 and SEE30303 tables do not reconcile to one another; however the Inventory Quantity On Hand and Historical Inventory Trial Balance reports have matching totals.

The following master query identifies both the items and the transactions which do not reconcile between the IV30300 and SEE30303 tables.  

NOTE- there is a restriction on DOCDATE in these queries...  This restriction indicates the date of initialization of the HITB Tool.  When the HITB tool is initialized, a summary of Inventory transactions is written and then GP begins to write detailed records HITB table.  There will be no detail records HITB before this date, therefore the IV30300 table detail cannot be compared to it.

Create table #temp1 (ITEMNMBR char(31),trxqty INT, DOCNUMBR char(25), DOCDATE datetime, TRXSORCE char(25) )

Create table #temp2 (ITEMNMBR char(31),trxqty INT, DOCNUMBR char(25), DOCDATE datetime, TRXSORCE char(25))

Declare @ITEMNMBR char(31)

Declare ROLLT cursor Forward_only

for Select ITEMNMBR from IV00101 where itemtype = 1

OPEN ROLLT;

      FETCH NEXT FROM ROLLT

      INTO @ITEMNMBR

      WHILE @@FETCH_STATUS = 0

      BEGIN

     Insert into #temp1

      select

      IVTH.itemnmbr ITEM,

      sum(IVTH.TRXQTY*IVTH.QTYBSUOM) TrxQty,

      IVTH.DOCNUMBR DOCNUMBR,

      convert(char(10),IVTH.DOCDATE,101) DOCDATE,

      IVTH.TRXSORCE TRXSORC from IV30300 IVTH (nolock)

      where IVTH.ITEMNMBR = @ITEMNMBR and IVTH.DOCTYPE != 3

      and IVTH.DOCDATE > '9/10/2011'

     

      group by IVTH.itemnmbr,IVTH.DOCNUMBR,convert(char(10),IVTH.DOCDATE,101) ,IVTH.TRXSORCE

      having sum(IVTH.TRXQTY*IVTH.QTYBSUOM) != (select sum(hitb.TRXQTYInBase)

      from SEE30303 HITB (nolock)

      where HITB.ITEMNMBR = IVTH.ITEMNMBR and HITB.DOCNUMBR = IVTH.DOCNUMBR 
       and HITB.TRXSORCE = IVTH.TRXSORCE

       and sum(IVTH.TRXQTY*IVTH.QTYBSUOM) != 0

     

      and hitb.DOCDATE > '9/10/2011')

     

      union all

     

      select

      IVTH.itemnmbr ITEM,

      sum(IVTH.TRXQTY*IVTH.QTYBSUOM) TrxQty,

      IVTH.DOCNUMBR DOCNUMBR,

      convert(char(10),IVTH.DOCDATE,101) DOCDATE,

      IVTH.TRXSORCE TRXSORC from IV30300 IVTH (nolock)

      where IVTH.ITEMNMBR = @ITEMNMBR and IVTH.DOCTYPE != 3

      and IVTH.DOCDATE > '9/10/2011' and

      not exists(Select * from SEE30303 HITB (nolock) where HITB.ITEMNMBR = IVTH.ITEMNMBR

      and HITB.DOCNUMBR = IVTH.DOCNUMBR and HITB.TRXSORCE = IVTH.TRXSORCE

     

      and hitb.DOCDATE > '9/10/2011')

      group by IVTH.itemnmbr,IVTH.DOCNUMBR,convert(char(10),IVTH.DOCDATE,101) ,IVTH.TRXSORCE

      having sum(IVTH.TRXQTY*IVTH.QTYBSUOM) !=0

      

            

     Insert into #temp2

      

      select

      hitb.itemnmbr ITEM,

      sum(hitb.TRXQTYInBase)  TrxQty,

      hitb.DOCNUMBR DOCNUMBR,

      convert(char(10),hitb.DOCDATE,101) DOCDATE,

      hitb.TRXSORCE TRXSORC from SEE30303 hitb (nolock)

      where hitb.ITEMNMBR = @ITEMNMBR

      and hitb.DOCDATE > '9/10/2011'

      group by hitb.itemnmbr,

      hitb.DOCNUMBR,

      DOCDATE,

      hitb.TRXSORCE

      having sum(hitb.TRXQTYInBase) != (Select SUM(IVTH.TRXQTY*IVTH.QTYBSUOM) 
      from IV30300 IVTH (nolock)

      where IVTH.ITEMNMBR = hitb.ITEMNMBR and IVTH.DOCNUMBR = hitb.DOCNUMBR 
      and IVTH.TRXSORCE = hitb.TRXSORCE

      and sum(hitb.TRXQTYInBase) != 0

      and IVTH.DOCDATE > '9/10/2011')

     

      union all

      

      select

      hitb.itemnmbr ITEM,

      sum(hitb.TRXQTYInBase)  TrxQty,

      hitb.DOCNUMBR DOCNUMBR,

      convert(char(10),hitb.DOCDATE,101) DOCDATE,

      hitb.TRXSORCE TRXSORC from SEE30303 hitb (nolock)

      where hitb.ITEMNMBR = @ITEMNMBR

      and hitb.DOCDATE > '9/10/2011'

      and not exists(Select * from IV30300 IVTH (nolock)

      where IVTH.ITEMNMBR = hitb.ITEMNMBR and IVTH.DOCNUMBR = hitb.DOCNUMBR 
      and IVTH.TRXSORCE = hitb.TRXSORCE

     

      and IVTH.DOCDATE > '9/10/2011')

      group by hitb.itemnmbr,

      hitb.DOCNUMBR,

      DOCDATE,

      hitb.TRXSORCE

      having sum(hitb.TRXQTYInBase) != 0

     

     

FETCH NEXT FROM ROLLT INTO @ITEMNMBR

END

     

     

      Close ROLLT

      DEALLOCATE ROLLT

     

      Select * from #temp1

      Select * from #temp2

     

      drop table #temp1

      drop table #temp2

The results of this query will resemble the screen capture below.

The data produced by this query is restricted to the records present in either the IV30300 (IVTH) table [top results] or the SEE30303 (HITB) table [bottom results] which don’t completely reconcile with the data in the other table (i.e. the quantities don’t match or the record is present in one table, but not in the other – the latter has, to date, meant the record existed in the IV30300 table, but no corresponding record was present in the SEE30303 table).

Resolutions:

First, in any and all cases, it is imperative to run the Inventory Reconcile Utility for all items identified by the above master query.  Roughly one third of the errors initially identified during this process (Type one problems) were resolved by running the Inventory Reconcile Utility for the affected item.

Inventory > Utilitites > Reconcile

Second, records which show as not completely posted by the system in the HITB table (Type 2 problems), should have their GLPOSTDT set to their DOCDATE, using an Update Query similar to the following:

The first part of the query will identify the records in the database in need of updating:

The second part of the query will update the GLPOSTDT, which ensures these records will appear on the HITB report.

Third, the missing records, identified by the master query as missing from the HITB table (typically), after all other mitigating mechanisms have been attempted, must be inserted into the HITB table using the following query style.

Finally, in the end we decided altering the database in the case of type 4 transactions posed too great a risk when weighed against relatively minor rewards.  

All of these proposed resolutions were successfully tested in a Virtual Test Environment, which was created by making a copy of the live environment with VM Ware tools.  Each of these resolutions were subsequently implemented in the client's live environment.