create procedure [dbo].[MssWebGetOrderGuaranteePriceInfo]
    @inOrderId int
as
begin
    set nocount on;
    declare @orderModule nvarchar(50)
    select @orderModule = OpenOrderInModule from Orders where PriKey = @inOrderId
    
    if @orderModule = 'Special Services'
        set @orderModule = 'Order Information'

    select
        MaterialType.MaterialTypeID as MaterialTypeId,
        MaterialType.Description as MaterialTypeName,
        MaterialCPUType.[Type] as RateType,
        GuaranteedPriceCPU.Rate as Rate
    from MaterialType
    inner join UsableMaterialTypes on UsableMaterialTypes.MaterialTypeFID = MaterialType.MaterialTypeID
    inner join MaterialItemCodeMap as MaterialItemCodeMap
        on MaterialItemCodeMap.MaterialTypeFID = MaterialType.MaterialTypeID
    inner join MaterialCPUType as MaterialCPUType
        on MaterialCPUType.MaterialCPUTypeID = MaterialItemCodeMap.MaterialCPUTypeFID
    left outer join GuaranteedPriceCPU as GuaranteedPriceCPU on
    (
        GuaranteedPriceCPU.MaterialItemCodeMapFID = MaterialItemCodeMap.MaterialItemCodeMapID and
        GuaranteedPriceCPU.OrderFID = @inOrderId
    )
    where
        GuaranteedPriceCPU.Rate is not null
        -- or show active materials available for this orders module
        or (MaterialType.Inactive = 0
            and (
                (@orderModule = 'Order Information' and MaterialType.AvailableToOrderInformation = 1)
                or (@orderModule = 'International Orders' and MaterialType.AvailableToInternational = 1)
                or (@orderModule = 'Office & Industrial' and MaterialType.AvailableToOfficeIndustrial = 1)
            )
        )
    order by MaterialType.SortOrder, MaterialType.Description;
end
GO
GRANT EXECUTE ON  [dbo].[MssWebGetOrderGuaranteePriceInfo] TO [MssExec]
GO