Question Status

Suggested Answer
ssuire asked a question on 12 Jul 2013 7:45 AM

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?

Reply
Frank Hamelly responded on 12 Jul 2013 8:08 AM

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

http://gp2themax.blogspot.com/

Reply
Richard Wheeler responded on 12 Jul 2013 8:21 AM

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

Reply
ssuire responded on 12 Jul 2013 8:24 AM

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.

Reply
Frank Hamelly responded on 12 Jul 2013 8:29 AM

I would take Richard's advise then and run Inventory Reconcile.  Then check your report again.

** 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

http://gp2themax.blogspot.com/

Reply
Suggested Answer
Mahmoud M. AlSaadi responded on 12 Jul 2013 8:42 AM

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.

Best Regards,

Mahmoud M. AlSaadi

Dynamics GP Essentials |   mahmoudsaadi.blogspot.com

Reply
ssuire responded on 12 Jul 2013 8:51 AM

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?

Reply
Suggested Answer
Mahmoud M. AlSaadi responded on 12 Jul 2013 9:13 AM

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

Best Regards,

Mahmoud M. AlSaadi

Dynamics GP Essentials |   mahmoudsaadi.blogspot.com

Reply
ssuire responded on 12 Jul 2013 11:19 AM

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?

Reply
ssuire responded on 12 Jul 2013 1:01 PM

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.

Reply
ssuire responded on 12 Jul 2013 2:41 PM

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?  

Reply
Suggested Answer
Mahmoud M. AlSaadi responded on 12 Jul 2013 4:54 PM

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.

Best Regards,

Mahmoud M. AlSaadi

Dynamics GP Essentials |   mahmoudsaadi.blogspot.com

Reply
Suggested Answer
Mahmoud M. AlSaadi responded on 12 Jul 2013 8:42 AM

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.

Best Regards,

Mahmoud M. AlSaadi

Dynamics GP Essentials |   mahmoudsaadi.blogspot.com

Reply
Suggested Answer
Mahmoud M. AlSaadi responded on 12 Jul 2013 9:13 AM

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

Best Regards,

Mahmoud M. AlSaadi

Dynamics GP Essentials |   mahmoudsaadi.blogspot.com

Reply
Suggested Answer
Mahmoud M. AlSaadi responded on 12 Jul 2013 4:54 PM

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.

Best Regards,

Mahmoud M. AlSaadi

Dynamics GP Essentials |   mahmoudsaadi.blogspot.com

Reply