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:
- 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.
- 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.
- There were a number of records, which were
written to the IV30300 (IVTH) table, which were not written to the SEE30303
(HITB) table.
- 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.
