I have several items on my HITB Report that have zero quantities but have values of positive and negative numbers. What is the best way to correct these items?

This issue is a result of the cost adjustment document for transactions that have been pulled out of inventory just before the cost adjustment is recorded.

What is the valuation method of the inventory ... ?

In order to ensure that this is related to cost adjustment, you need to calculate the cost of the remaining cost layers in purchase receipt work [ IV10200 ]. The value must be compared to the one calculated from HITB. You might find out the following;

Item_Number Quantity_Available IV_10200_Extended_Cost HIT_Extended_Cost

A 20 100 110

Now if you withdraw all the quantity available of Item (A), you will have the following record in your HITB.

Item_Number Quantity_Available IV_10200_Extended_Cost HIT_Extended_Cost

A 0 0 10

The remaining HITB value is caused by cost adjustment document as illustrated above. Of course, this is part of reconciling inventory internally, which means, ensuring that all the IV resources have the same value. I consider that Purchase Receipt Work is correct, because it records the new unit cost for all the remaining quantity once a cost adjustment record is thrown in HITB. It keeps the old cost though in filed "Adjusted Cost - ADJUNITCOST "

One more important point to be considered, after matching your inventory internally, you need to reconcile Inventory versus General Ledger.

Regardless of the No. of transactions, what I am proposing is to calculate the extended cost per item from purchase receipt work, to be compared by the extended cost calculated from HITB. I hope this would help you,

SELECT

ISNULL(X.[ITEMNMBR],Y.[ITEMNMBR]) [ITEMNMBR],

X.HITB_Quantity_Available,

X.HITB_Cost,

Y.IV_Quantity_Available,

Y.IV_EX_Cost,

X.HITB_Cost - Y.IV_EX_Cost AS Cost_Variance

FROM

(

SELECT [ITEMNMBR],

SUM([TRXQTYInBase]) HITB_Quantity_Available,

SUM([EXTDCOST]) HITB_Cost

FROM [SEE30303]

GROUP BY [ITEMNMBR]

) AS X ------ Calculate the Extended_Cost per Item [SEE30303]

FULL OUTER JOIN

(

SELECT [ITEMNMBR],

SUM([QTYRECVD]-[QTYSOLD]) IV_Quantity_Available,

SUM(([QTYRECVD]-[QTYSOLD])*[UNITCOST]) IV_EX_Cost

FROM [IV10200]

GROUP BY [ITEMNMBR]

) AS Y ------ Calculate the Extended_Cost per Item [IV10200]

ON X.[ITEMNMBR] = Y.[ITEMNMBR]

WHERE ABS( X.HITB_Cost - Y.IV_EX_Cost) > 1 OR

X.HITB_Quantity_Available<> Y.IV_Quantity_Available

Actually, I have been exposed to such issues before and have found no predefined out-of-the-box solution for such case. Therefore, there was an urgent need to come up with a correction criteria that works on the same business logic of Dynamics GP.

First of all, it is essential to understand the root causes of such problems, which might be quite too many. Some of them is a result of improper business processes, while a small portion could be related to the system. I will be soon publishing a detailed article on my blog in order to thoroughly analyze this issue.

As for the correction criteria, I suggest matching the HITB table to the purchase receipt work. Based on the fact that [ IV10200 ] is the correct table. This means that new records (cost adjustment) must be inserted inton[ SEE30303 ] with the value variances retrieved by the script above. You need to justify the need to insert such records by the fact that these records must have been created automatically by the system, but haven't for several reasons. On the other hand, the purchase receipt work fortunately stores the new unit cost. For instance, let's take the item mentioned above (200-B20), the cost adjustment record that must be inserted into

[ SEE30303 ] is shown below;

**ITEMNMBR:** 200-B20

**DOCDATE:** 2013-07-13

**GLPOSTDT:** 2013-07-13

**DOCNUMBR:** COST_ADJUSTMENT

**TRXQTYInBase:** 0

**TRXQTY:** 0

**VARIANCEQTY:** 0

**UNITCOST:** 2377.59 / 7730 - - - - - - Variance value / Remaining Quantity

**EXTDCOST:** 2377.59

**IVIVINDX:** XXX - - - - - - The Inventory Index of item (200-B20)

**IVIVOFIX: **XXX - - - - - - An offset account

**JRNENTRY:** XXX - - - - - - The no. of the Journal Entry

**DEBITAMT:** 2377.59

**CRDTAMNT:** 0

In this case, the purchase receipt value is 25122.50 while HITB value is (22744.91). Therefore, you need to increase the HITB value by the record above in order to be identical to purchase receipt work. Please take into consideration that the value is written under the Debit amount, since you're increasing inventory.

Finally, a journal entry shall be entered with the variance value;

**Journal Entry No. Debit_Amount Credit_Amount Inventory_Index Inventory_Offset**

XXX 2377.59 0 XXX XXX

Now, you have purchase receipt work, HITB and GL all matched with the correct cost of

[ IV10200 ].

Please note that this is a work around solution to apply the same logic of cost adjustment and ensure that the correct cost of the remaining layers is entered into HITB. Though, There is not KB article that supports such solution. In case you are to apply this correction criteria, I recommend consulting with your partner.

How many QTY decimal places are setup for the items in question? How many decimals show on the report? I would first suspect you have partial quantities that aren't being reflected properly on the report.

*** Please, if this answers your question, mark it as 'Answered' so others experiencing the same will know it resolved your issue. ***

Frank E. Hamelly, MCP-GP, MCP-AX, MCITP, MCT, MVP

Frank is correct. Check the decimal palcd quantities on the items in question. You may also want to run IV Reconcile as well.

Richard E. Wheeler 2013 and 2014 MVP

Member Microsoft Academic Alliance

www.rbsolutions.com Revered Business Solutions Ballston Lake, NY 518-877-0763 x10

This issue is a result of the cost adjustment document for transactions that have been pulled out of inventory just before the cost adjustment is recorded.

What is the valuation method of the inventory ... ?

In order to ensure that this is related to cost adjustment, you need to calculate the cost of the remaining cost layers in purchase receipt work [ IV10200 ]. The value must be compared to the one calculated from HITB. You might find out the following;

Item_Number Quantity_Available IV_10200_Extended_Cost HIT_Extended_Cost

A 20 100 110

Now if you withdraw all the quantity available of Item (A), you will have the following record in your HITB.

Item_Number Quantity_Available IV_10200_Extended_Cost HIT_Extended_Cost

A 0 0 10

The remaining HITB value is caused by cost adjustment document as illustrated above. Of course, this is part of reconciling inventory internally, which means, ensuring that all the IV resources have the same value. I consider that Purchase Receipt Work is correct, because it records the new unit cost for all the remaining quantity once a cost adjustment record is thrown in HITB. It keeps the old cost though in filed "Adjusted Cost - ADJUNITCOST "

One more important point to be considered, after matching your inventory internally, you need to reconcile Inventory versus General Ledger.

Regardless of the No. of transactions, what I am proposing is to calculate the extended cost per item from purchase receipt work, to be compared by the extended cost calculated from HITB. I hope this would help you,

SELECT

ISNULL(X.[ITEMNMBR],Y.[ITEMNMBR]) [ITEMNMBR],

X.HITB_Quantity_Available,

X.HITB_Cost,

Y.IV_Quantity_Available,

Y.IV_EX_Cost,

X.HITB_Cost - Y.IV_EX_Cost AS Cost_Variance

FROM

(

SELECT [ITEMNMBR],

SUM([TRXQTYInBase]) HITB_Quantity_Available,

SUM([EXTDCOST]) HITB_Cost

FROM [SEE30303]

GROUP BY [ITEMNMBR]

) AS X ------ Calculate the Extended_Cost per Item [SEE30303]

FULL OUTER JOIN

(

SELECT [ITEMNMBR],

SUM([QTYRECVD]-[QTYSOLD]) IV_Quantity_Available,

SUM(([QTYRECVD]-[QTYSOLD])*[UNITCOST]) IV_EX_Cost

FROM [IV10200]

GROUP BY [ITEMNMBR]

) AS Y ------ Calculate the Extended_Cost per Item [IV10200]

ON X.[ITEMNMBR] = Y.[ITEMNMBR]

WHERE ABS( X.HITB_Cost - Y.IV_EX_Cost) > 1 OR

X.HITB_Quantity_Available<> Y.IV_Quantity_Available

Actually, I have been exposed to such issues before and have found no predefined out-of-the-box solution for such case. Therefore, there was an urgent need to come up with a correction criteria that works on the same business logic of Dynamics GP.

First of all, it is essential to understand the root causes of such problems, which might be quite too many. Some of them is a result of improper business processes, while a small portion could be related to the system. I will be soon publishing a detailed article on my blog in order to thoroughly analyze this issue.

As for the correction criteria, I suggest matching the HITB table to the purchase receipt work. Based on the fact that [ IV10200 ] is the correct table. This means that new records (cost adjustment) must be inserted inton[ SEE30303 ] with the value variances retrieved by the script above. You need to justify the need to insert such records by the fact that these records must have been created automatically by the system, but haven't for several reasons. On the other hand, the purchase receipt work fortunately stores the new unit cost. For instance, let's take the item mentioned above (200-B20), the cost adjustment record that must be inserted into

[ SEE30303 ] is shown below;

**ITEMNMBR:** 200-B20

**DOCDATE:** 2013-07-13

**GLPOSTDT:** 2013-07-13

**DOCNUMBR:** COST_ADJUSTMENT

**TRXQTYInBase:** 0

**TRXQTY:** 0

**VARIANCEQTY:** 0

**UNITCOST:** 2377.59 / 7730 - - - - - - Variance value / Remaining Quantity

**EXTDCOST:** 2377.59

**IVIVINDX:** XXX - - - - - - The Inventory Index of item (200-B20)

**IVIVOFIX: **XXX - - - - - - An offset account

**JRNENTRY:** XXX - - - - - - The no. of the Journal Entry

**DEBITAMT:** 2377.59

**CRDTAMNT:** 0

In this case, the purchase receipt value is 25122.50 while HITB value is (22744.91). Therefore, you need to increase the HITB value by the record above in order to be identical to purchase receipt work. Please take into consideration that the value is written under the Debit amount, since you're increasing inventory.

Finally, a journal entry shall be entered with the variance value;

**Journal Entry No. Debit_Amount Credit_Amount Inventory_Index Inventory_Offset**

XXX 2377.59 0 XXX XXX

Now, you have purchase receipt work, HITB and GL all matched with the correct cost of

[ IV10200 ].

Please note that this is a work around solution to apply the same logic of cost adjustment and ensure that the correct cost of the remaining layers is entered into HITB. Though, There is not KB article that supports such solution. In case you are to apply this correction criteria, I recommend consulting with your partner.