Featured Post

Dynamics GP 2018 is now Released

It is officially published that Microsoft Dynamics GP 2018 is available, the download link is provided below: Product download page ...

Wednesday, October 16, 2013

HITB Essentials Series – Zero Quantity and Negative/Positive Cost

One essential part of reconciling inventory tables is to check the Extended Cost for each item within Historical Inventory Trial Balance (SEE30303) versus The Extended Cost in Purchase Receipt Layer (IV10200)

Theoretically speaking, these two values must match for each item number. Although, for several reasons these two values are not the same.

ddd

This post is part of the HITB Essentials Series.In this post, I will be providing the script through which we could compare the HITB with the Purchase Receipt Layer only, to get an initial assessment of the Inventory Variances per item.

HITB Essentials Series will reveal several critical inventory issues regarding primarily two issues (Internal Inventory Reconciliation – IV Tables) and (External Inventory Reconciliation – Inventory versus GL)

Tables Included:

  • IV10200  | Purchase Receipt Layer Work
  • SEE30303 | Historical Inventory Trial Balance


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

No comments:

Post a Comment