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.
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.
Mahmoud M. AlSaadi
Dynamics GP Essentials | mahmoudsaadi.blogspot.com
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_VarianceFROM( 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
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;
UNITCOST: 2377.59 / 7730 - - - - - - Variance value / Remaining Quantity
IVIVINDX: XXX - - - - - - The Inventory Index of item (200-B20)
IVIVOFIX: XXX - - - - - - An offset account
JRNENTRY: XXX - - - - - - The no. of the Journal Entry
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
We have 3 quantity decimal places setup for this one particular item but we have 20 plus items this has happened with. The report shows 3 decimal places as well for this item.
I would take Richard's advise then and run Inventory Reconcile. Then check your report again.
I have over 4500 transactions in the IV10200 table for one item. How am I to determine which is causing the issue and fix it?
here is the script for one item above which shows me your results.
200-B20 7730.00000 22744.91277 7730.00000 25122.50000000 -2377.58723
The negative number is what shows up on my HITB. That is the amount I need to correct. How do I go about correcting it?
I have a zero quantity for this item but a -2377.58 amount. I need to clear this up and prevent this from happening in the future if possible.
Your script gave me an even larger list that what I had. Which is great I have additional items as well. Is there a way to find the actual cost adjustment that caused the issue so I can correct it? Or do you have another script that can show me the adjusted Unit cost for the items in question so I can see which GL accounts were affected and sites?