Explanation of SQL
This SQL will create a function that returns a table with any discrepencies between the inventory counts of a part and what the inventory should be based on a recreation of all events in the inventory log table.
Risk of Data Corruption if Run Improperly
None. This is a selection query and no data is modified in the running of it.
SQL
SQL to Create Function
code_formatsql|code format"sql"
–Transact-SQL Multistatement Table-valued Function Syntax
CREATE FUNCTION Check_CompareInventoryToLog()
RETURNS @result
TABLE (
RowID INT Primary Key,
InventoryID INT,
PartID INT,
DivisionID INT,
WarehouseID INT,
PartName varchar(155),
BilledInv FLOAT,
ROInv FLOAT,
OnHandInv FLOAT,
ReservedInv FLOAT,
AvailableInv FLOAT,
OnOrderInv FLOAT,
ExpectedInv FLOAT,
BilledLog FLOAT,
ROLog FLOAT,
OnHandLog FLOAT,
ReservedLog FLOAT,
AvailableLog FLOAT,
OnOrderLog FLOAT,
ExpectedLog FLOAT,
BilledDelta FLOAT,
RODelta FLOAT,
OnHandDelta FLOAT,
ReservedDelta FLOAT,
AvailableDelta FLOAT,
OnOrderDelta FLOAT,
ExpectedDelta FLOAT
)
AS
BEGIN
DECLARE @UnbalancedParts Table(ID INT Primary Key);
Insert into @UnbalancedParts
Select Coalesce( Inv.ID, IL.InventoryID, -1 )
from
( select ID, PartID, Sum(QuantityBilled) QuantityBilled, SUM(QuantityReceivedOnly) QuantityReceivedOnly, SUM(QuantityOnHand) QuantityOnHand, SUM(QuantityReserved) QuantityReserved, SUM(QuantityAvailable) QuantityAvailable, SUM(QuantityOnOrder) QuantityOnOrder, SUM(QuantityExpected) QuantityExpected
from Inventory
Where ID > 0 and ClassTypeID 12200 and PartID is not NULL
Group By ID, PartID ) Inv
Full Join
( select InventoryID, PartID, Sum(QuantityBilled) QuantityBilled, SUM(QuantityReceivedOnly) QuantityReceivedOnly, SUM(QuantityOnHand) QuantityOnHand, SUM(QuantityReserved) QuantityReserved, SUM(QuantityAvailable) QuantityAvailable, SUM(QuantityOnOrder) QuantityOnOrder, SUM(QuantityExpected) QuantityExpected
from InventoryLog
Where ID > 0 and InventoryID is not NULL
group By InventoryID, PartID ) IL on (Inv.ID IL.InventoryID)
Where ( ABS( Coalesce(IL.QuantityBilled, 0) - Coalesce(Inv.QuantityBilled, 0) ) > 0.00001 )
OR ( ABS( Coalesce(IL.QuantityReceivedOnly, 0) - Coalesce(Inv.QuantityReceivedOnly, 0) ) > 0.00001 )
OR ( ABS( Coalesce(IL.QuantityReserved, 0) - Coalesce(Inv.QuantityReserved, 0) ) > 0.00001 )
OR ( ABS( Coalesce(IL.QuantityOnOrder, 0) - Coalesce(Inv.QuantityOnOrder, 0) ) > 0.00001 )
;
DECLARE @InventoryTotal Table(InventoryID INT Primary Key,
DivisionID INT, WarehouseID INT,
PartID INT, EntryType INT,
QuantityBilled FLOAT, QuantityReceivedOnly FLOAT, QuantityOnHand FLOAT,
QuantityReserved FLOAT, QuantityAvailable FLOAT, QuantityOnOrder FLOAT, QuantityExpected FLOAT)
; -- 1 Inventory
insert into @InventoryTotal
select ID as InventoryID,
coalesce(DivisionID, 10) as DivisionID,
coalesce(WarehouseID, 10) as WarehouseID,
PartID, 1 as EntryType,
Sum(QuantityBilled) QuantityBilled, SUM(QuantityReceivedOnly) QuantityReceivedOnly,
SUM(QuantityOnHand) QuantityOnHand, SUM(QuantityReserved) QuantityReserved,
SUM(QuantityAvailable) QuantityAvailable, SUM(QuantityOnOrder) QuantityOnOrder,
SUM(QuantityExpected) QuantityExpected
from Inventory
Where ID in (Select ID from @UnbalancedParts)
and ClassTypeID 12200
Group By ID, PartID, coalesce(DivisionID, 10), coalesce(WarehouseID, 10)
;
DECLARE @LogTotal Table(InventoryID INT Primary Key,
DivisionID INT, WarehouseID INT,
PartID INT, EntryType INT,
QuantityBilled FLOAT, QuantityReceivedOnly FLOAT, QuantityOnHand FLOAT,
QuantityReserved FLOAT, QuantityAvailable FLOAT, QuantityOnOrder FLOAT, QuantityExpected FLOAT)
; -- 2 Full Log
insert into @LogTotal
select InventoryID,
null as DivisionID, null as WarehouseID, -- not needed, because tied to warehouse
PartID, 2 as EntryType,
Sum(QuantityBilled) QuantityBilled, SUM(QuantityReceivedOnly) QuantityReceivedOnly,
SUM(QuantityOnHand) QuantityOnHand, SUM(QuantityReserved) QuantityReserved,
SUM(QuantityAvailable) QuantityAvailable, SUM(QuantityOnOrder) QuantityOnOrder,
SUM(QuantityExpected) QuantityExpected
from InventoryLog
Where ID > 0 and InventoryID in (Select ID from @UnbalancedParts) and InventoryID is not NULL
group By InventoryID, PartID
;
insert into @Result
select
ROW_NUMBER() OVER(Order By InventoryID) as RowID,
InventoryID,
PartID,
DivisionID,
WarehouseID,
PartName,
BilledInv,
ROInv,
OnHandInv,
ReservedInv,
AvailableInv,
OnOrderInv,
ExpectedInv,
BilledLog,
ROLog,
OnHandLog,
ReservedLog,
AvailableLog,
OnOrderLog,
ExpectedLog,
BilledDelta,
RODelta,
BilledDelta + RODelta as OnHandDelta,
ReservedDelta,
BilledDelta + RODelta - ReservedDelta as AvailableDelta,
OnOrderDelta,
BilledDelta + RODelta - ReservedDelta + OnOrderDelta as ExpectedDelta
from
(
select Coalesce( I.InventoryID, L.InventoryID ) InventoryID,
Coalesce( I.PartID, L.PartID ) PartID,
(Select ItemName from Part where ID Coalesce( I.PartID, L.PartID )) PartName,
I.DivisionID as DivisionID,
I.WarehouseID as WarehouseID,
Round( I.QuantityBilled - L.QuantityBilled, 6) as BilledDelta,
Round( I.QuantityReceivedOnly - L.QuantityReceivedOnly, 6) as RODelta,
Round( I.QuantityReserved - L.QuantityReserved, 6) as ReservedDelta,
Round(I.QuantityOnOrder - L.QuantityOnOrder, 6) as OnOrderDelta,
I.QuantityBilled as BilledInv,
L.QuantityBilled as BilledLog,
I.QuantityReceivedOnly as ROInv,
L.QuantityReceivedOnly as ROLog,
I.QuantityOnHand as OnHandInv,
L.QuantityOnHand as OnHandLog,
I.QuantityReserved as ReservedInv,
L.QuantityReserved as ReservedLog,
I.QuantityAvailable as AvailableInv,
L.QuantityAvailable as AvailableLog,
I.QuantityOnOrder as OnOrderInv,
L.QuantityOnOrder as OnOrderLog,
I.QuantityExpected as ExpectedInv,
L.QuantityExpected as ExpectedLog
from @InventoryTotal I
Full Join @LogTotal L on (L.InventoryID I.InventoryID)
) Detail
where (BilledDelta 0) or (RODelta 0) or (ReservedDelta 0) or (OnOrderDelta 0)
Order by PartName, BilledDelta, RODelta, ReservedDelta, OnOrderDelta
RETURN
END
;
<code> SQL to Use Function to Return Discrepencies code_formatsql|code format"sql"
select *
from Check_CompareInventoryToLog()
<code> Sample Results The following shows a sample result set returned from the query. (This table has been transposed to show all the information.)
RowID | 1 | 2 | |||
InventoryID | 1007 | 1008 | |||
PartID | 1003 | 151 | |||
DivisionID | 10 | 10 | |||
WarehouseID | 10 | 10 | |||
PartName | Hasp - USB | Other Labor | |||
BilledInv | 0 | -2019.32 | |||
ROInv | 0 | 0 | |||
OnHandInv | 0 | -2019.32 | |||
ReservedInv | 69 | 207.82 | |||
AvailableInv | -69 | -2227.14 | |||
OnOrderInv | 0 | 0 | |||
ExpectedInv | -69 | -2227.14 | |||
BilledLog | 0 | -25643.03 | |||
ROLog | 0 | 0 | |||
OnHandLog | 0 | -25643.03 | |||
ReservedLog | 59 | 207.82 | |||
AvailableLog | -59 | -25850.85 | |||
OnOrderLog | 0 | 0 | |||
ExpectedLog | -59 | -25850.85 | |||
BilledDelta | 0 | 23623.71 | |||
RODelta | 0 | 0 | |||
OnHandDelta | 0 | 23623.71 | |||
ReservedDelta | 10 | 0 | |||
AvailableDelta | -10 | 23623.71 | |||
OnOrderDelta | 0 | 0 | |||
ExpectedDelta | -10 | 23623.71 |
Version Information
- Entered : 01/2011
- Version : 4.5+
Related SQLs