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

create view [dbo].[vOrderActualCPU]
as

select    OrderId = ls.OrdPrikey,
    OrderNumber = o.OrderNo,
    PackingDescription = mt.Description,
    ActualContainers = sum(
        case when mct.Type = 'Container'
        then isnull(lsm.ActQuantity, 0) else 0 end),
    ActualPacking = sum(
        case when mct.Type = 'Packing'
        then isnull(lsm.ActQuantity, 0) else 0 end),
    ActualUnpacking = sum(
        case when mct.Type = 'Unpacking'
        then isnull(lsm.ActQuantity, 0) else 0 end),
    SortOrder = mt.SortOrder
from    LSMaterial lsm
left join LocServ ls on lsm.LSMPriKey = ls.PriKey
left join Orders o on ls.OrdPriKey = o.PriKey
left join MaterialItemCodeMap micm on lsm.MaterialItemCodeMapFID = micm.MaterialItemCodeMapID
left join MaterialCPUType mct on micm.MaterialCPUTypeFID = mct.MaterialCPUTypeID
left join MaterialType mt on micm.MaterialTypeFID = mt.MaterialTypeID
where ls.OrdPriKey is not null
group by ls.ORDPriKey, o.OrderNo, mt.Description, mt.SortOrder
GO
GRANT SELECT ON  [dbo].[vOrderActualCPU] TO [MssExec]
GRANT INSERT ON  [dbo].[vOrderActualCPU] TO [MssExec]
GRANT DELETE ON  [dbo].[vOrderActualCPU] TO [MssExec]
GRANT UPDATE ON  [dbo].[vOrderActualCPU] TO [MssExec]
GO