Dynamics GP 2018 R2 - HITB Include and Exclude Zero Quantity or Value

With the release of Dynamics GP 2018 R2, several new features were introduced to the core functionality in the system. In this post, we will shed a light on an interesting feature provided for the inventory module in regards of the Historical Inventory Trial Balance.

In previous posts, it has been thoroughly discussed that HITB usually encounters issues represented with zero quantity and negative/positive cost. The source of the problem is represented with mismatching issues between inventory cost layers ( IV10200, IV10201) from one side and (SEE30303) from the other side. 

Here is a brief on the issue:

 

Inventory Cost Layers versus HITB (SEE30303)

I have seen such case with multiple clients, resolving this case requires internal matching or rebuilding of the HITB inventory source in order to recalculate quantity and value records and ensure that historical and current inventory balances are matching.

In the same context, the recent release of Dynamics GP 2018 R2 has introduced an interesting feature for the HITB report represented with the ability to either include or exclude either items with "Zero Quantity" or "Zero Value", this will ensure that HITB report is being filter properly for all the zero quantity or value records. 

In order to print the Historical Inventory Trial Balance report, follow the path below:

Reports > Inventory > Activity > Historical IV Trial Balance,

HITB Report Modification

Best Regards

Mahmoud M. AlSaadi

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