[dbo].[vOrderEstimatedCPU]
create view [dbo].[vOrderEstimatedCPU]
as
select OrderId = om.OrdPrikey,
OrderNumbers = o.OrderNo,
PackingDescription = mt.Description,
EstimatedContainer = sum(
case when mct.Type = 'Container'
then isnull(om.EstQuantity, 0) else 0 end),
EstimatedPacking = sum(
case when mct.Type = 'Packing'
then isnull(om.EstQuantity, 0) else 0 end),
EstimatedUnpacking = sum(
case when mct.Type = 'Unpacking'
then isnull(om.EstQuantity, 0) else 0 end),
SortOrder = mt.SortOrder
from MaterialItemCodeMap micm
left join MaterialCPUType mct on micm.MaterialCPUTypeFID = mct.MaterialCPUTypeID
left join MaterialType mt on micm.MaterialTypeFID = mt.MaterialTypeID
left join OrdMaterial om on micm.MaterialItemCodeMapID = om.MaterialItemCodeMapFID
left join Orders o on om.ORDPriKey = o.PriKey
where om.ORDPriKey is not null
group by om.ORDPriKey, o.OrderNo, mt.Description, mt.SortOrder
GO
GRANT SELECT ON [dbo].[vOrderEstimatedCPU] TO [MssExec]
GRANT INSERT ON [dbo].[vOrderEstimatedCPU] TO [MssExec]
GRANT DELETE ON [dbo].[vOrderEstimatedCPU] TO [MssExec]
GRANT UPDATE ON [dbo].[vOrderEstimatedCPU] TO [MssExec]
GO