Friday, October 17, 2014

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.

8 comments:

  1. Thanks Harry

    Excelent Information

    I have this error why?
    Msg 208, Level 16, State 1, Line 5
    Invalid object name 'IV00101'.

    ReplyDelete
  2. The IV00101 table is the Inventory Item Master table, and is a company level table. The query should be pointed at the company level in SQL Server Managemetn Studio to achieve the desired results.

    ReplyDelete
  3. Dang, I wish I'd found this three hours ago! Thanks for posting, Harry!

    ReplyDelete
  4. Hi Harry -- ran your script above and it is showing the transaction that is in IV30300 but not in SEE30303. Ran reconcile and did not fix anything. Is this where I am going to have to add a record to the SEE30303 table?

    ReplyDelete
  5. Indeed Joseph, it is. As you can see from my post, this is the most common scenario, when reconcile does not work. Feel free to use the query above as an template for the fix.

    ReplyDelete
  6. Hi Harry -- thank you. Just one more question: what makes the SEQNUMBR field in SEE30303? Is this just taking an incrementing by 1 field that looks at the max number of item records and adds one to it?

    ReplyDelete
  7. Joseph - the SEE30303.SEQNUMBR field is a long integer formatted field used to link to the DTA10200.SEQNUMBR, if the client uses MDA (Multi Dimensional Analysis). In the case laid out in my post above MDA was not in use, so the SEQNUMBR was incremented.

    ReplyDelete