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.

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