======  ======



===== 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 sql>
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 =====
  * Entered : 12/2009
  * Version : Control 4.3+