This query produces a SQL of the same data that is in the parts grid.
None. This is a selection query and no data is modified in the running of it.
declare @TransHeaderID int
set @TransHeaderID = ;
declare @IncludeVariationRow bit;
set @IncludeVariationRow = 0;
select FormattedName as Line,
case when IsTransPart = 1 then 'Yes' else 'No' end as IsPart,
coalesce('Part: '+PartCode, NodeName) as Item,
case when (IsTransPart=0 and NodeClassTypeID=10100) then (Select SubTotalPrice from TransDetail where ID = OrderTree.NodeID) else NULL end as CurrentPrice,
coalesce(CalculatedValue, sum(CalculatedValue) over(PARTITION BY OrderTree.SortIndex)) as CalculatedValue,
coalesce(EstimatedValue, sum(EstimatedValue) over(PARTITION BY OrderTree.SortIndex)) as EstimatedValue,
coalesce(ActualValue, sum(ActualValue) over(PARTITION BY OrderTree.SortIndex)) as ActualValue,
coalesce(UnitsText, ) as Units,
coalesce(CalculatedCost, sum(CalculatedCost) over(PARTITION BY OrderTree.SortIndex)) as CalculatedCost,
coalesce(EstimatedCost, sum(EstimatedCost) over(PARTITION BY OrderTree.SortIndex)) as EstimatedCost,
coalesce(ActualCost, sum(ActualCost) over(PARTITION BY OrderTree.SortIndex)) as ActualCost,
coalesce(SuggestedPrice, sum(SuggestedPrice) over(PARTITION BY OrderTree.SortIndex)) as SuggestedPrice,
coalesce(ReferenceNumber, 0) as ReferenceNumber,
OrderTree.SortIndex +'/'+CAST(IsTransPart as varchar(1)) as SortIndex,
HasChildren, Depth,
TransPart.ID as TransPartID,
TransPart.PartID, TransPart.UnitID,
NodeID, NodeClassTypeID, OrderTree.TransHeaderID as TransHeaderID
from
(
Select
case when GrandChildItem.ID is not NULL then SPACE(4 * 4)
else case when ChildItem.ID is not NULL then SPACE(4 * 3)
else case when TopItem.ID is not NULL then SPACE(4 * 2)
else case when TV.ID is not NULL then SPACE(4 * 1)
else case when TH.ID is not NULL then SPACE(4 * 0)
end end end end end +
Coalesce(GrandChildItem.LineItemNumber+'. '+GrandChildItem.GoodsItemCode,
ChildItem.LineItemNumber+'. '+ChildItem.GoodsItemCode,
TopItem.LineItemNumber+'. '+TopItem.GoodsItemCode,
TV.VariationName,
'Order '+Cast(TH.OrderNumber as VarChar(12))) AS FormattedName,
Coalesce(GrandChildItem.GoodsItemCode, ChildItem.GoodsItemCode, TopItem.GoodsItemCode, TV.VariationName, 'Order '+Cast(TH.OrderNumber as VarChar(12))) AS NodeName,
Coalesce(GrandChildItem.ID, ChildItem.ID, TopItem.ID, TV.ID, TH.ID) AS NodeID,
Coalesce(GrandChildItem.ClassTypeID, ChildItem.ClassTypeID, TopItem.ClassTypeID, TV.ClassTypeID, TH.ClassTypeID) AS NodeClassTypeID,
1 AS IsActive, – just kept for compatibility
case when coalesce(GrandChildItem.ChildItemCount, ChildItem.ChildItemCount, TopItem.ChildItemCount, 1) > 0 Then 1 else 0 end as HasChildren,
Coalesce(GrandChildItem.ID - GrandChildItem.ID + 3, ChildItem.ID - ChildItem.ID + 2, TopItem.ID - TopItem.ID + 1, 0, 0) AS Depth,
Coalesce(GrandChildItem.GoodsItemCode, ChildItem.GoodsItemCode, TopItem.GoodsItemCode) AS ProductName,
('Order '+cast(TH.OrderNumber as Varchar(12)) + ' ' + cast(TH.Description as varchar(100)) ) as Category1Name,
TV.VariationName as Category2Name,
Coalesce(ChildItem.GoodsItemCode, TopItem.GoodsItemCode) AS Category3Name,
ChildItem.GoodsItemCode AS Category4Name,
GrandChildItem.GoodsItemCode as Category5Name,
coalesce(CAST(TH.OrderNumber as varchar(12)), ) + '/' +
coalesce(right('0000000'+convert(varchar(8), TV.SortIndex), 8) + '/', ) +
coalesce(right('0000000'+ltrim(convert(varchar(8), TopItem.LineItemIndex)), 8)+'/', ) +
coalesce(right('0000000'+ltrim(convert(varchar(8), ChildItem.LineItemIndex)), 8)+'/', ) +
coalesce(right('0000000'+ltrim(convert(varchar(8), GrandChildItem.LineItemIndex)), 8)+'/', ) as SortIndex,
TH.ID as TransHeaderID,
TV.ID as TransVariationID,
TopItem.ID as TopItemID,
ChildItem.ID as ChildItemID,
GrandChildItem.ID as GrandChildItemID,
coalesce(case TH.TransactionType when 1 then 'Ord ' when 2 then 'Est ' else 'Other ' end + Cast(TH.OrderNumber as VarChar(10))+'\\', ) +
coalesce(TV.VariationName + '\\', ) +
coalesce(TopItem.LineItemNumber + '\\', ) +
coalesce(ChildItem.LineItemNumber + '\\', ) +
coalesce(GrandChildItem.LineItemNumber + '\\', '') FormattedPath
from TransHeader as TH
join (Select 1 as IsHeader Union Select 0) TempH on 1=1 – create two rows for each of the above
left join TransVariation as TV on TV.ParentID = TH.ID and TempH.IsHeader = 0
join (Select 1 as IsVariation Union Select 0) TempV on 1=1
left join TransDetail as TopItem on TopItem.ParentID = TH.ID and TopItem.VariationID = TV.ID and TempV.IsVariation = 0
join (Select 1 as IsTopItem Union Select 0) TempTI on 1=1
left join TransDetail as ChildItem on ChildItem.ParentID = TopItem.ID and ChildItem.VariationID = TV.ID and TempTI.IsTopItem = 0
join (Select 1 as IsChildItem Union Select 0) TempCI on 1=1
left join TransDetail as GrandChildItem on GrandChildItem.ParentID = GrandChildItem.ID and GrandChildItem.VariationID = TV.ID
join (Select 1 as IsGrandChildItem Union Select 0) TempGCI on 1=1
where (TH.ID = @TransHeaderID) and (IsHeader+IsVariation+IsTopItem+IsChildItem+IsGrandChildItem=1)
and ((IsHeader=1 and TV.ID is NULL)
or (IsVariation=1 and TopItem.ID is NULL)
or (IsTopItem=1 and TopItem.ID is not NULL)
or (IsChildItem=1 and ChildItem.ID is not NULL)
or (IsGrandChildItem=1 and GrandChildItem.ID is not NULL)
)
and (@IncludeVariationRow = 1 or IsVariation = 0)
) OrderTree
join (Select 1 as IsTransPart Union Select 0) TempTP on 1=1 – create two rows for each of the above
full outer join
(
Select *
from TransPart
where TransPart.TransHeaderID = @TransHeaderID
) as TransPart
on TransPart.TransDetailID = OrderTree.NodeID and OrderTree.NodeClassTypeID = 10100 and IsTransPart = 1
left join Part on Part.ID = TransPart.PartID
left join Inventory on Inventory.ID = Part.InventoryID
where (IsTransPart=0 or TransPart.ID is not NULL)
order by SortIndex, IsTransPart