Table of Contents

{$page}

Explanation of SQL

This query is still a work-in-process.

The purpose of this query is to produce a historical tracking of all inventory movements for a part. It combines entries from the GL, InventoryLog, Journal, and PartUsage tables.

Risk of Data Corruption if Run Improperly

None. This is a selection query and no data is modified in the running of it.

SQL

code_formatsql

declare @PartID int;

set @PartID = 1378

select Journal.PartID 

     ,(select ItemName from Part where ID = @PartID) as PartName
     , QueryStartDateTime as TheDate
     , coalesce(QuantityBilled, DetailAmount) as QuantityBilled
     , coalesce(InventoryLog.QuantityReceivedOnly, 0) as QuantityReceivedOnly
     , coalesce(InventoryLog.QuantityReserved, 0) as QuantityReserved
     , coalesce(InventoryLog.QuantityOnOrder, 0) as QuantityOnOrder
     , InventoryLog.QuantityBilled * InventoryLog.UnitCost as Cost
     , InventoryLog.UnitCost
     , Journal.Description
     , case 
         when Journal.ClassTypeID = 20520 then 'Order Usage' 
         when Journal.ClassTypeID = 20530 then 'Inv. Adjustment' 
         else 'Journal' 
       end as TheSource
     , cast(Notes as varchar(255)) as Notes
     , (Select OrderNumber from TransHeader where ID = TransactionID) as OrderNumber
     , TransactionID as TransHeaderID
     , EmployeeID
     , Journal.ID, Journal.ClassTypeID
     , StartGLGroupID as GLGroupID
     , case 
         when Journal.ClassTypeID = 20520 then 2
         when Journal.ClassTypeID = 20530 then 1
         else 0
       end as SortOrder

from Journal

left join InventoryLog on Journal.ID = InventoryLog.ID

where Journal.PartID = @PartID

union

select PartID 

     ,(select ItemName from Part where ID = @PartID) as PartName
     , EntryDateTime as TheDate
     , Quantity as QuantityBilled
     , 0 as QuantityReceivedOnly
     , 0 as QuantityReserved
     , 0 as QuantityOnOrder
     , Amount as Cost
     , case when coalesce(Quantity, 0) = 0 then NULL else Amount / Quantity end as UnitCost
     , Description
     , 'GL' as TheSource
     , cast(Notes as varchar(255)) as Notes
     , (Select OrderNumber from TransHeader where ID = TransactionID) as OrderNumber
     , TransactionID as TransHeaderID
     , EmployeeID
     , ID, ClassTypeID
     , GroupID as GLGroupID
     , 4 as SortOrder

from GL

where PartID = @PartID and GLClassificationType = 1003 – only pull changes to inventory

union

select PartID 

     ,(select ItemName from Part where ID = @PartID) as PartName
     , PostDate as TheDate
     , Amount as QuantityBilled
     , 0 as QuantityReceivedOnly
     , 0 as QuantityReserved
     , 0 as QuantityOnOrder
     , Cost
     , case when coalesce(Amount, 0) = 0 then NULL else Cost / Amount end as UnitCost
     , coalesce(Description, 'Usage Card') as Description
     , 'Part Usage' as TheSource
     , NULL as Notes
     , (Select OrderNumber from TransHeader where ID = TransHeaderID) as OrderNumber
     , TransHeaderID
     , EmployeeID
     , ID, ClassTypeID
     , NULL as GLGroupID
     , 3 as SortOrder

from PartUsageCard

where PartID = @PartID

order by TheDate desc, SortOrder

code

Version Information