UFN_ORDER_GETAPPLIEDDISCOUNTS

            CREATE function dbo.UFN_ORDER_GETAPPLIEDDISCOUNTS(@SALESORDERID uniqueidentifier)
            returns table
            as return 

                --Get all manual discounts and applied discount

                select  --Get all manual discounts

                    SOMD.DISCOUNTID as [ID],
                    DISCOUNT.NAME,
                    dbo.UFN_DISCOUNT_GENERATEVALUEDESCRIPTION(DISCOUNT.ID) as DESCRIPTION,
                    SOMD.PROMOTIONALCODE,
                    case 
                        when [DISCOUNT].[APPLIESTOCODE] = 0 then
                            null
                        when [LIMIT].[ID] is not null or [DISCOUNT].[LIMITDISCOUNTSPERORDER] = 1 then
                            1
                        else
                            0
                    end [LIMITAPPLICATIONTYPECODE],
                    case
                        when [LIMIT].[ID] is not null then
                            [LIMIT].[NUMBEROFDISCOUNTSPERORDER]
                        when [DISCOUNT].[LIMITDISCOUNTSPERORDER] = 1 then
                            [DISCOUNT].[NUMBEROFDISCOUNTSPERORDER]
                        else
                            null
                    end [TIMESLIMITED],
                    [DISCOUNT].[APPLICATIONTYPECODE],
                    case 
                        when [LIMITDISCOUNTSPERORDER] = 1 then
                            [DISCOUNT].[NUMBEROFDISCOUNTSPERORDER]
                        else
                            -1
                    end [MAXLIMIT],
                    [DISCOUNTTYPECODE]
                from
                    dbo.SALESORDERMANUALDISCOUNT SOMD
                    inner join dbo.DISCOUNT on SOMD.DISCOUNTID = DISCOUNT.ID
                    left join dbo.[SALESORDERDISCOUNTLIMITOVERRIDE] [LIMIT] on [LIMIT].[DISCOUNTID] = [DISCOUNT].[ID] and [LIMIT].[SALESORDERID] = @SALESORDERID
                where
                    SOMD.SALESORDERID = @SALESORDERID

                union all

                --Get all automatically applied item-level discounts

                select distinct  
                    [DISCOUNT].[ID],
                    [DISCOUNT].[NAME],
                    dbo.UFN_DISCOUNT_GENERATEVALUEDESCRIPTION([DISCOUNT].[ID]) as [DESCRIPTION],
                    null as [PROMOTIONALCODE],
                    case 
                        when [DISCOUNT].[APPLIESTOCODE] = 0 then
                            null
                        when [LIMIT].[ID] is not null or [DISCOUNT].[LIMITDISCOUNTSPERORDER] = 1 then
                            1
                        else
                            0
                    end [LIMITAPPLICATIONTYPECODE],
                    case
                        when [LIMIT].[ID] is not null then
                            [LIMIT].[NUMBEROFDISCOUNTSPERORDER]
                        when [DISCOUNT].[LIMITDISCOUNTSPERORDER] = 1 then
                            [DISCOUNT].[NUMBEROFDISCOUNTSPERORDER]
                        else
                            null
                    end [TIMESLIMITED],
                    [DISCOUNT].[APPLICATIONTYPECODE],
                    case 
                        when [LIMITDISCOUNTSPERORDER] = 1 then
                            [DISCOUNT].[NUMBEROFDISCOUNTSPERORDER]
                        else 
                            -1
                    end [MAXLIMIT],
                    [DISCOUNTTYPECODE]
                from dbo.[SALESORDERITEMITEMDISCOUNT]
                inner join dbo.[SALESORDERITEM] on
                    [SALESORDERITEMITEMDISCOUNT].[SALESORDERITEMID] = [SALESORDERITEM].[ID]
                inner join dbo.[DISCOUNT] on 
                    [SALESORDERITEMITEMDISCOUNT].[DISCOUNTID] = [DISCOUNT].[ID]
                left join dbo.[SALESORDERDISCOUNTLIMITOVERRIDE] [LIMIT] on 
                    [LIMIT].[DISCOUNTID] = [SALESORDERITEMITEMDISCOUNT].[DISCOUNTID] and 
                    [LIMIT].[SALESORDERID] = @SALESORDERID
                where [SALESORDERITEM].[SALESORDERID] = @SALESORDERID
                and [DISCOUNT].[ID] not in (select [DISCOUNTID] from [SALESORDERMANUALDISCOUNT] where [SALESORDERID] = @SALESORDERID)

                union all

                --Get all automatically applied order-level discounts

                select
                    [DISCOUNT].[ID],
                    [DISCOUNT].[NAME],
                    dbo.UFN_DISCOUNT_GENERATEVALUEDESCRIPTION([DISCOUNT].[ID]) as [DESCRIPTION],
                    null as [PROMOTIONALCODE],
                    case 
                        when [DISCOUNT].[APPLIESTOCODE] = 0 then
                            null
                        when [LIMIT].[ID] is not null or [DISCOUNT].[LIMITDISCOUNTSPERORDER] = 1 then
                            1
                        else
                            0
                    end [LIMITAPPLICATIONTYPECODE],
                    case
                        when [LIMIT].[ID] is not null then
                            [LIMIT].[NUMBEROFDISCOUNTSPERORDER]
                        when [DISCOUNT].[LIMITDISCOUNTSPERORDER] = 1 then
                            [DISCOUNT].[NUMBEROFDISCOUNTSPERORDER]
                        else
                            null
                    end [TIMESLIMITED],
                    [DISCOUNT].[APPLICATIONTYPECODE],
                    case 
                        when [LIMITDISCOUNTSPERORDER] = 1 then
                            [DISCOUNT].[NUMBEROFDISCOUNTSPERORDER]
                        else
                            -1
                    end [MAXLIMIT],
                    [DISCOUNTTYPECODE]
                from dbo.[SALESORDERITEM]
                inner join dbo.[SALESORDERITEMORDERDISCOUNT] on
                    [SALESORDERITEMORDERDISCOUNT].[ID] = [SALESORDERITEM].[ID]
                inner join dbo.[DISCOUNT] on
                    [SALESORDERITEMORDERDISCOUNT].[DISCOUNTID] = [DISCOUNT].[ID]
                left join dbo.[SALESORDERDISCOUNTLIMITOVERRIDE] [LIMIT] on 
                    [LIMIT].[DISCOUNTID] = [DISCOUNT].[ID] and 
                    [LIMIT].[SALESORDERID] = @SALESORDERID        
                where [SALESORDERITEM].[SALESORDERID] = @SALESORDERID
                and [DISCOUNT].[ID] not in (select [DISCOUNTID] from dbo.[SALESORDERMANUALDISCOUNT] where [SALESORDERID] = @SALESORDERID)

                -- Get membership promos

                union all

                select
                    MEMBERSHIPPROMO.[ID],
                    MEMBERSHIPPROMO.[NAME],
                    MEMBERSHIPPROMO.FORMATTEDVALUE as [DESCRIPTION],
                    SALESORDERMEMBERSHIPPROMO.[PROMOTIONALCODE],
                    null as [LIMITAPPLICATIONTYPECODE],
                    null as [TIMESLIMITED],
                    MEMBERSHIPPROMO.[APPLICATIONTYPECODE] + 1 as APPLICATIONTYPECODE,
                    -1 as [MAXLIMIT],
                    MEMBERSHIPPROMO.PROMOTIONTYPECODE + 10 as [DISCOUNTTYPECODE]
                from dbo.SALESORDERMEMBERSHIPPROMO
                    inner join dbo.MEMBERSHIPPROMO on SALESORDERMEMBERSHIPPROMO.MEMBERSHIPPROMOID = MEMBERSHIPPROMO.ID
                where
                    SALESORDERMEMBERSHIPPROMO.SALESORDERID = @SALESORDERID

                -- Get adjustable discount

                union all

                select
                    SALESORDERADJUSTABLEDISCOUNT.[ID],
                    SALESORDERITEMORDERDISCOUNT.[DISCOUNTNAME] [NAME],
                    SALESORDERITEMORDERDISCOUNT.[DISCOUNTNAME] as [DESCRIPTION],
                    null [PROMOTIONALCODE],
                    null as [LIMITAPPLICATIONTYPECODE],
                    null as [TIMESLIMITED],
                    1 as APPLICATIONTYPECODE,
                    -1 as [MAXLIMIT],
                    0 as [DISCOUNTTYPECODE]
                from dbo.SALESORDERITEM
           inner join dbo.SALESORDERADJUSTABLEDISCOUNT on SALESORDERITEM.SALESORDERID = SALESORDERADJUSTABLEDISCOUNT.SALESORDERID
                     inner join dbo.SALESORDERITEMORDERDISCOUNT on SALESORDERITEM.ID = SALESORDERITEMORDERDISCOUNT.ID                    
                where
                    SALESORDERITEM.SALESORDERID = @SALESORDERID and
                    SALESORDERITEMORDERDISCOUNT.DISCOUNTID is null

                -- Get applied ticket promos

                union all

                select
                    SALESORDERITEMMEMBERSHIPITEMPROMOTION.[ID],
                    SALESORDERITEMMEMBERSHIPITEMPROMOTION.[PROMOTIONNAME] as [NAME],
                    [SALESORDERITEM].[DESCRIPTION] as [DESCRIPTION],
                    null as [PROMOTIONALCODE],
                    null as [LIMITAPPLICATIONTYPECODE],
                    null as [TIMESLIMITED],
                    1 as APPLICATIONTYPECODE,
                    -1 as [MAXLIMIT],
                    14 as [DISCOUNTTYPECODE]
                from dbo.SALESORDERITEM
                    inner join dbo.SALESORDERITEMMEMBERSHIPITEMPROMOTION on SALESORDERITEM.ID = SALESORDERITEMMEMBERSHIPITEMPROMOTION.SALESORDERITEMID                    
                where
                    SALESORDERITEM.SALESORDERID = @SALESORDERID and
                    SALESORDERITEMMEMBERSHIPITEMPROMOTION.MEMBERSHIPPROMOID is null