/**
* This view is for ProServ, used in custom reports. Don't rename the view without confirmation from ProServ.
*/

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