Views [dbo].[vOrderEstimatedCPU]
Properties
PropertyValue
ANSI Nulls OnYes
Quoted Identifier OnYes
Created10:56:48 AM Monday, May 4, 2015
Last Modified9:13:04 AM Thursday, May 23, 2024
Columns
Name
OrderId
OrderNumbers
PackingDescription
EstimatedContainer
EstimatedPacking
EstimatedUnpacking
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].[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
Uses