Views [dbo].[vOrderActualCPU]
Properties
PropertyValue
ANSI Nulls OnYes
Quoted Identifier OnYes
Created3:10:26 PM Tuesday, July 21, 2015
Last Modified2:30:44 AM Saturday, February 4, 2017
Columns
Name
OrderId
OrderNumber
PackingDescription
ActualContainers
ActualPacking
ActualUnpacking
SortOrder
Permissions
TypeActionOwning Principal
GrantDeleteMssExec
GrantInsertMssExec
GrantSelectMssExec
GrantUpdateMssExec
SQL Script
/**
* 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
Uses