Inventory Reconcile to GL - Inventory HITB Report Missing Records

When reconciling inventory module with the general ledger in Dynamics GP, the historical inventory trial balance represents the most powerful tool which is used primarily by GP end users. several previous posts were introduced on Dynamics GP Essentials as part of the Essentials Series . In this post, we are shedding a light on a specific support case related to missing records in the HITB report. 

Support Case 

HITB report and Inventory Reconcile to GL retrieves data primarily from the SEE30303 table. In this essence, the following should perfectly match without any issue.

• Inventory Reconcile to GL
• HITB - Historical Inventory Trial Balance Report ( Reports > Inventory > Activity > Historical Inventory Trial Balance) 

One additional customized report for the HITB that was previously provided on this link retrieves the inventory cumulative balances as of a specific period. Here is the report details:

Inventory Balances - HITB Cumulative

Logically speaking, the following three parts should match perfectly since they all retrieve data from the HITB table - SEE30303. 

In once of the recent support cases, the client reported that there are missing transactions from the HITB, when they print both; reconcile to GL and HITB, these transactions don't appear as part of the details. The result is that the Inventory and the GL are not reconcile. 

The variance was represented with these missing records, which total value closes the variance and results in zero difference between the GL and the Inventory module

Resolution

When checking the SEE30303 table, these transactions were available, although, they were not printing out on the HITB report since the following two columns were populated as 0 

• IVIVINDX
• IVIVOFIX

The resolution was to simply update the following fields in order to retrieve the correct inventory account as derived from the item card.

The following SQL Script retrieves these records which have incorrect inventory and inventory offset account index:

SELECT ITEMNMBR,

       SEQNUMBR,

       DOCDATE,

       GLPOSTDT,

       DOCNUMBR,

       DOCTYPE,

       LOCNCODE,

       RCPTNMBR,

       RCTSEQNM,

       PCHSRCTY,

       QTYTYPE,

       UOFM,

       TRXQTYInBase,

       TRXQTY,

       VARIANCEQTY,

       UNITCOST,

       EXTDCOST,

       DECPLQTY,

       DECPLCUR,

       IsOverrideReceipt,

       IsOverrideRelieved,

       OverrideRelievedDate,

       RCPTNMBR1,

       HSTMODUL,

       ORTRXSRC,

       LNSEQNBR,

       LNITMSEQ,

       CMPNTSEQ,

       SRCRFRNCNMBR,

       VENDORID,

       PONUMBER,

       TRXREFERENCE,

       VCTNMTHD,

       IVIVINDX,

       IVIVOFIX,

       JRNENTRY,

       TRXSORCE,

       DEBITAMT,

       CRDTAMNT,

       DATE1,

       TIME1,

       DEX_ROW_ID

FROM dbo.SEE30303

WHERE IVIVINDX = 0

      AND IVIVOFIX = 0;

Important Note !

If the SQL Query above retrieved any records at all, you will have to update the inventory and inventory offset index columns and then run the HITB or the IV reconcile to GL again.

Best Regards, 

Mahmoud M. AlSaadi