Featured Post

Dynamics GP 2016 R2 is Now Available .. and #MSDynGP will Always be Avilable

The Microsoft Dynamics GP team announced today that Microsoft Dynamics GP 2016 R2 has released . Dynamics GP 2016 R2 continues the grea...

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