UFN_SALESORDER_GETAVAILABLEMANUALDISCOUNTS

CREATE function dbo.UFN_SALESORDER_GETAVAILABLEMANUALDISCOUNTS
(
    @SALESORDERID uniqueidentifier
)
returns table
as return
    select
        ID,
        NAME,
        DISCOUNTTYPECODE,
        CALCULATIONTYPECODE,
        APPLIESTO,
        0 as VALUE,  --use min and max value now.

        NUMBERTOPURCHASE,
        NUMBERTODISCOUNT,
        DESCRIPTION,
        APPLIESTOCODE,
        case [DISCOUNT].[APPLIESTOCODE]
            when 1 then -- When item-level discount

                case [DISCOUNT].[CALCULATIONTYPECODE]
                    when 1 then --percent

                        case [DISCOUNT].[DISCOUNTTYPECODE]
                            when 2 then
                                (select top (1) [PERCENT] from dbo.[GROUPSIZEDISCOUNT] where [GROUPSIZEDISCOUNT].[DISCOUNTID] = [DISCOUNT].[ID] order by [PERCENT] asc)
                            else
                                (select top (1) [PERCENT]
                                from
                                    (select [PERCENT] from dbo.[DISCOUNTPRICETYPE] where [DISCOUNTPRICETYPE].[DISCOUNTID] = [DISCOUNT].[ID]
                                        union all
                                    select [DISCOUNT].[MERCHANDISEPERCENT] as [PERCENT]) as [PERCENTS]
                                where [PERCENT] > 0
                                order by [PERCENT] asc)
                        end
                    else  --amount or specific value

                        case [DISCOUNT].[DISCOUNTTYPECODE]
                            when 2 then
                                (select top (1) [AMOUNT] from dbo.[GROUPSIZEDISCOUNT] where [GROUPSIZEDISCOUNT].[DISCOUNTID] = [DISCOUNT].[ID] order by [AMOUNT] asc)
                            else
                                (select top (1) [AMOUNT]
                                from
                                    (select [AMOUNT] from dbo.[DISCOUNTPRICETYPE] where [DISCOUNTPRICETYPE].[DISCOUNTID] = [DISCOUNT].[ID]
                                        union all
                                    select [DISCOUNT].[MERCHANDISEAMOUNT] as [AMOUNT]) as [AMOUNTS]
                                where [AMOUNT] > 0
                                order by [AMOUNT] asc)
                        end
                    end
            else --When order-level discount

                case [CALCULATIONTYPECODE]
                    when 0 then 
                        [DISCOUNT].[AMOUNT]
                    else
                        [DISCOUNT].[PERCENT]
                end
        end MINVALUE,
        case [DISCOUNT].[APPLIESTOCODE]
            when 1 then  -- When item-level discount

                case [DISCOUNT].[CALCULATIONTYPECODE]
                    when 1 then --percent

                        case [DISCOUNT].[DISCOUNTTYPECODE]
                            when 2 then
                                (select top (1) [PERCENT] from dbo.[GROUPSIZEDISCOUNT] where [GROUPSIZEDISCOUNT].[DISCOUNTID] = [DISCOUNT].[ID] order by [PERCENT] desc)
                            else
                                (select top (1) [PERCENT]
                                from
                                    (select [PERCENT] from dbo.[DISCOUNTPRICETYPE] where [DISCOUNTPRICETYPE].[DISCOUNTID] = [DISCOUNT].[ID]
                                        union all
                                    select [DISCOUNT].[MERCHANDISEPERCENT] as [PERCENT]) as [PERCENTS]
                                order by [PERCENT] desc)
                        end
                    else --amount or specific value

                        case [DISCOUNT].[DISCOUNTTYPECODE]
                            when 2 then
                                (select top (1) [AMOUNT] from dbo.[GROUPSIZEDISCOUNT] where [GROUPSIZEDISCOUNT].[DISCOUNTID] = [DISCOUNT].[ID] order by [AMOUNT] desc)
                            else
                                (select top (1) [AMOUNT]
                                from
                                    (select [AMOUNT] from dbo.[DISCOUNTPRICETYPE] where [DISCOUNTPRICETYPE].[DISCOUNTID] = [DISCOUNT].[ID]
                                        union all
                                    select [DISCOUNT].[MERCHANDISEAMOUNT] as [AMOUNT]) as [AMOUNTS]
                                order by [AMOUNT] desc)
                        end
                end
            else --When order-level discount

                case [CALCULATIONTYPECODE]
                    when 0 then 
                        [DISCOUNT].[AMOUNT]
                    else
                        [DISCOUNT].[PERCENT]
                end
        end MAXVALUE
    from dbo.DISCOUNT
    where
        ISACTIVE = 1 and
        APPLICATIONTYPECODE = 1 and --manually applied

        dbo.UFN_DISCOUNT_AVAILABLEFORORDER(ID, @SALESORDERID) = 1