USP_SALESORDER_APPLYORDERDISCOUNTS

CREATE procedure dbo.USP_SALESORDER_APPLYORDERDISCOUNTS
(
    @SALESORDERID uniqueidentifier,
    @CHANGEAGENTID uniqueidentifier
)
as
    set nocount on;

    declare @SALESMETHODTYPECODE tinyint;
    declare @HASMERCHANDISE bit = 0;
    declare @HASTICKETS bit = 0;

    if exists (select 1 from dbo.SALESORDERITEM where SALESORDERID = @SALESORDERID and TYPECODE = 0)
        set @HASTICKETS = 1;
    if exists (select 1 from dbo.SALESORDERITEM where SALESORDERID = @SALESORDERID and TYPECODE = 14)
        set @HASMERCHANDISE = 1;

    declare @EXISTINGORDERDISCOUNTS table (
        SALESORDERITEMID uniqueidentifier,
        ORIGINALNAME nvarchar(100),
        DISCOUNTID uniqueidentifier, 
        ORIGINALID uniqueidentifier,
        APPLICATIONTYPECODE tinyint,
        CALCULATIONTYPECODE tinyint,
        AMOUNT decimal(5,2),
        [PERCENT] decimal(5,2),
        APPLIESTOMERCHANDISE bit,
        APPLIESTOTICKETS bit,
        ISHISTORICAL bit
    );

    -- This statement needs to include adjustable discounts so that the @@rowcount check below will be nonzero and they get deleted (otherwise, they will be inserted again).

    -- The @@rowcount check exists to prevent SQL Server from locking the table for the delete when there's nothing to delete (needless deadlock).

    insert into @EXISTINGORDERDISCOUNTS
    select
        SALESORDERITEM.ID,
        ORDERDISCOUNT.DISCOUNTNAME,
        DISCOUNT.ID,
        DISCOUNT.ORIGINALDISCOUNTID,
        DISCOUNT.APPLICATIONTYPECODE,
        DISCOUNT.CALCULATIONTYPECODE,
        DISCOUNT.AMOUNT,
        DISCOUNT.[PERCENT],
        DISCOUNT.APPLIESTOMERCHANDISE,
        DISCOUNT.APPLIESTOTICKETS,
        DISCOUNT.ISHISTORICALVERSION
    from dbo.SALESORDERITEM with (nolock)
    inner join dbo.SALESORDERITEMORDERDISCOUNT ORDERDISCOUNT on ORDERDISCOUNT.ID = SALESORDERITEM.ID
    left join dbo.DISCOUNT on DISCOUNT.ID = ORDERDISCOUNT.DISCOUNTID
    where SALESORDERITEM.SALESORDERID = @SALESORDERID
        and SALESORDERITEM.TYPECODE = 5;

    if @@rowcount > 0 begin
        --remove order discount to recalculate

        delete from dbo.SALESORDERITEM with (rowlock)
        where SALESORDERID = @SALESORDERID
            and TYPECODE = 5;
    end

    declare @SALESMETHODID uniqueidentifier;

    select @SALESMETHODID = [SALESMETHOD].[ID],
        @SALESMETHODTYPECODE = [SALESMETHOD].[TYPECODE]
    from dbo.[SALESMETHOD]
    inner join dbo.[SALESORDER]
        on [SALESORDER].[SALESMETHODTYPECODE] = [SALESMETHOD].[TYPECODE]
    where [SALESORDER].[ID] = @SALESORDERID;

    declare @SALESMETHODAVAILABLEORDERDISCOUNTS table (
        ID uniqueidentifier,
        APPLICATIONTYPECODE tinyint,
        CALCULATIONTYPECODE tinyint,
        AMOUNT money,
        [PERCENT] decimal(5,2),
        APPLIESTOMERCHANDISE bit,
        APPLIESTOTICKETS bit
    );

    insert into @SALESMETHODAVAILABLEORDERDISCOUNTS
    select
        AVAILABLEDISCOUNTS.ID,
        AVAILABLEDISCOUNTS.APPLICATIONTYPECODE,
        AVAILABLEDISCOUNTS.CALCULATIONTYPECODE,
        AVAILABLEDISCOUNTS.AMOUNT,
        AVAILABLEDISCOUNTS.[PERCENT],
        AVAILABLEDISCOUNTS.APPLIESTOMERCHANDISE,
        AVAILABLEDISCOUNTS.APPLIESTOTICKETS
    from dbo.UFN_SALESMETHOD_AVAILABLEDISCOUNTS(@SALESMETHODID, 0) AVAILABLEDISCOUNTS  -- applies to orders

    inner join dbo.DISCOUNT on AVAILABLEDISCOUNTS.ID = DISCOUNT.ID
    left join @EXISTINGORDERDISCOUNTS EXISTINGDISCOUNTS on (EXISTINGDISCOUNTS.ORIGINALID = DISCOUNT.ORIGINALDISCOUNTID and EXISTINGDISCOUNTS.DISCOUNTID <> DISCOUNT.ID)
    where EXISTINGDISCOUNTS.DISCOUNTID is null;

    -- Ignore new version of discount and use the older version that was already on the order

    insert into @SALESMETHODAVAILABLEORDERDISCOUNTS
    select
        EXISTINGDISCOUNTS.DISCOUNTID,
        EXISTINGDISCOUNTS.APPLICATIONTYPECODE,
        EXISTINGDISCOUNTS.CALCULATIONTYPECODE,
        EXISTINGDISCOUNTS.AMOUNT,
        EXISTINGDISCOUNTS.[PERCENT],
        EXISTINGDISCOUNTS.APPLIESTOMERCHANDISE,
        EXISTINGDISCOUNTS.APPLIESTOTICKETS
    from @EXISTINGORDERDISCOUNTS EXISTINGDISCOUNTS
    where ISHISTORICAL = 1;

    --find active, available and eligible discounts for sales order

    declare @SALESORDERAVAILABLEDISCOUNTS as UDT_APPLYDISCOUNTS_SALESORDERAVAILABLEDISCOUNT

    --add max available/eligible/active percent discount

    insert into @SALESORDERAVAILABLEDISCOUNTS
    select top 1
        newid(),
        DISCOUNTID,
        APPLIEDMANUALLY
    from
    (
        select
            DISCOUNT.[PERCENT],
            DISCOUNT.ID DISCOUNTID,
            1 APPLIEDMANUALLY
        from
            dbo.SALESORDERMANUALDISCOUNT 
        inner join @SALESMETHODAVAILABLEORDERDISCOUNTS as DISCOUNT on
            SALESORDERMANUALDISCOUNT.DISCOUNTID = DISCOUNT.ID
        where
            SALESORDERMANUALDISCOUNT.SALESORDERID = @SALESORDERID and
            DISCOUNT.APPLICATIONTYPECODE <> 0 and -- not automatic (manual, promo)

            DISCOUNT.CALCULATIONTYPECODE = 1 and -- percent

            dbo.UFN_DISCOUNT_ELIGIBLEFORORDER(DISCOUNT.ID, @SALESORDERID) = 1 and
            DISCOUNT.APPLIESTOMERCHANDISE = 1 and
            DISCOUNT.APPLIESTOTICKETS = 1

        union all

        select
            DISCOUNT.[PERCENT],
            DISCOUNT.ID,
            0 APPLIEDMANUALLY
        from
            @SALESMETHODAVAILABLEORDERDISCOUNTS as DISCOUNT
        where
            DISCOUNT.APPLICATIONTYPECODE = 0 and -- automatic discount

            DISCOUNT.CALCULATIONTYPECODE = 1 and -- percent

            dbo.UFN_DISCOUNT_ELIGIBLEFORORDER(DISCOUNT.ID, @SALESORDERID) = 1 and
            DISCOUNT.APPLIESTOMERCHANDISE = 1 and
            DISCOUNT.APPLIESTOTICKETS = 1
    ) D
    order by
        D.[PERCENT] desc, D.APPLIEDMANUALLY desc


    --add max available/eligible/active percent discount

    insert into @SALESORDERAVAILABLEDISCOUNTS
    select top 1
        newid(),
        DISCOUNTID,
        APPLIEDMANUALLY
    from
    (
        select
            DISCOUNT.[PERCENT],
            DISCOUNT.ID DISCOUNTID,
            1 APPLIEDMANUALLY
        from
            dbo.SALESORDERMANUALDISCOUNT
        inner join @SALESMETHODAVAILABLEORDERDISCOUNTS as DISCOUNT on
            SALESORDERMANUALDISCOUNT.DISCOUNTID = DISCOUNT.ID
        where
            SALESORDERMANUALDISCOUNT.SALESORDERID = @SALESORDERID and
            DISCOUNT.APPLICATIONTYPECODE <> 0 and -- not automatic (manual, promo)

            DISCOUNT.CALCULATIONTYPECODE = 1 and -- percent

            dbo.UFN_DISCOUNT_ELIGIBLEFORORDER(DISCOUNT.ID, @SALESORDERID) = 1 and
            DISCOUNT.APPLIESTOMERCHANDISE = 0 and
            DISCOUNT.APPLIESTOTICKETS = 1

        union all

        select
            DISCOUNT.[PERCENT],
            DISCOUNT.ID,
            0 APPLIEDMANUALLY
        from
            @SALESMETHODAVAILABLEORDERDISCOUNTS as DISCOUNT
        where
            DISCOUNT.APPLICATIONTYPECODE = 0 and -- automatic discount

            DISCOUNT.CALCULATIONTYPECODE = 1 and -- percent

            dbo.UFN_DISCOUNT_ELIGIBLEFORORDER(DISCOUNT.ID, @SALESORDERID) = 1 and
            DISCOUNT.APPLIESTOMERCHANDISE = 0 and
            DISCOUNT.APPLIESTOTICKETS = 1
    ) D
    order by
        D.[PERCENT] desc, D.APPLIEDMANUALLY desc


    --add max available/eligible/active percent discount

    insert into @SALESORDERAVAILABLEDISCOUNTS
    select top 1
        newid(),
        DISCOUNTID,
        APPLIEDMANUALLY
    from
    (
        select
            DISCOUNT.[PERCENT],
            DISCOUNT.ID DISCOUNTID,
            1 APPLIEDMANUALLY
        from
            dbo.SALESORDERMANUALDISCOUNT 
        inner join @SALESMETHODAVAILABLEORDERDISCOUNTS as DISCOUNT on
            SALESORDERMANUALDISCOUNT.DISCOUNTID = DISCOUNT.ID
        where
            SALESORDERMANUALDISCOUNT.SALESORDERID = @SALESORDERID and
            DISCOUNT.APPLICATIONTYPECODE <> 0 and -- not automatic (manual, promo)

            DISCOUNT.CALCULATIONTYPECODE = 1 and -- percent

            dbo.UFN_DISCOUNT_ELIGIBLEFORORDER(DISCOUNT.ID, @SALESORDERID) = 1 and
            DISCOUNT.APPLIESTOMERCHANDISE = 1 and
            DISCOUNT.APPLIESTOTICKETS = 0

        union all

        select
            DISCOUNT.[PERCENT],
            DISCOUNT.ID,
            0 APPLIEDMANUALLY
        from
            @SALESMETHODAVAILABLEORDERDISCOUNTS as DISCOUNT
        where
            DISCOUNT.APPLICATIONTYPECODE = 0 and -- automatic discount

            DISCOUNT.CALCULATIONTYPECODE = 1 and -- percent

            dbo.UFN_DISCOUNT_ELIGIBLEFORORDER(DISCOUNT.ID, @SALESORDERID) = 1 and
            DISCOUNT.APPLIESTOMERCHANDISE = 1 and
            DISCOUNT.APPLIESTOTICKETS = 0
    ) D
    order by
        D.[PERCENT] desc, D.APPLIEDMANUALLY desc        

    --add max available/eligible/active amount discount

    insert into @SALESORDERAVAILABLEDISCOUNTS
    select top 1
        newid(),
        DISCOUNTID,
        APPLIEDMANUALLY
    from
    (
        select
            DISCOUNT.AMOUNT,
            DISCOUNT.ID DISCOUNTID,
            1 APPLIEDMANUALLY
        from
            dbo.SALESORDERMANUALDISCOUNT 
        inner join @SALESMETHODAVAILABLEORDERDISCOUNTS as DISCOUNT on
            SALESORDERMANUALDISCOUNT.DISCOUNTID = DISCOUNT.ID
        where
            SALESORDERMANUALDISCOUNT.SALESORDERID = @SALESORDERID and
            DISCOUNT.APPLICATIONTYPECODE <> 0 and -- Not Automatic (manual, promo)

            DISCOUNT.CALCULATIONTYPECODE = 0 and -- Amount

            dbo.UFN_DISCOUNT_ELIGIBLEFORORDER(DISCOUNT.ID, @SALESORDERID) = 1 

        union all

        select
            DISCOUNT.AMOUNT,
            DISCOUNT.ID,
            0 APPLIEDMANUALLY
        from
            @SALESMETHODAVAILABLEORDERDISCOUNTS as DISCOUNT
        where
            DISCOUNT.APPLICATIONTYPECODE = 0 and -- Automatic

            DISCOUNT.CALCULATIONTYPECODE = 0 and -- Amount

            dbo.UFN_DISCOUNT_ELIGIBLEFORORDER(DISCOUNT.ID, @SALESORDERID) = 1 
    ) D
    order by
        D.AMOUNT desc, D.APPLIEDMANUALLY desc

    declare @CONFIGUREDORDERDISCOUNTSAVAILABLE bit = 0
    declare @ADJUSTABLEORDERDISCOUNTAVAILABLE bit = 0

    if exists(select * from @SALESORDERAVAILABLEDISCOUNTS)
        set @CONFIGUREDORDERDISCOUNTSAVAILABLE = 1

    if exists(select * from dbo.SALESORDERADJUSTABLEDISCOUNT where SALESORDERID = @SALESORDERID)
        set @ADJUSTABLEORDERDISCOUNTAVAILABLE = 1

    if @ADJUSTABLEORDERDISCOUNTAVAILABLE = 1 or @CONFIGUREDORDERDISCOUNTSAVAILABLE = 1 begin -- gotta exist one way or the other                

        declare @TOTALTODISCOUNT money;
        declare @TOTALTODISCOUNTTICKETS money = 0;
        declare @TOTALTODISCOUNTMERCHANDISE money = 0;
        declare @TICKETAMOUNT money;
        declare @TICKETQUANTITY decimal(5,2);
        declare @MERCHANDISEQUANTITY decimal(5, 2);
        declare @MERCHANDISEAMOUNT money;
        declare @CURRENTDATE datetime = getdate();

        if @HASTICKETS = 1 begin
            select
                @TOTALTODISCOUNTTICKETS = isnull(sum(SALESORDERITEM.TOTAL - DISCOUNTS.AMOUNT), 0)
            from
                dbo.SALESORDERITEM
            outer apply (
                select isnull(sum(SALESORDERITEMITEMDISCOUNT.AMOUNT), 0) as AMOUNT
                from dbo.SALESORDERITEMITEMDISCOUNT
                where SALESORDERITEMITEMDISCOUNT.SALESORDERITEMID = SALESORDERITEM.ID
            ) as DISCOUNTS
            where
                SALESORDERITEM.SALESORDERID = @SALESORDERID 
                and    SALESORDERITEM.TYPECODE = 0;
        end

        if @HASMERCHANDISE = 1 begin
            select 
                @TOTALTODISCOUNTMERCHANDISE = isnull(sum(SALESORDERITEM.TOTAL - DISCOUNTS.AMOUNT), 0)
            from dbo.SALESORDERITEM
            inner join dbo.SALESORDERITEMMERCHANDISE
                on SALESORDERITEMMERCHANDISE.ID = SALESORDERITEM.ID
            inner join dbo.MERCHANDISEPRODUCTINSTANCE
                on MERCHANDISEPRODUCTINSTANCE.ID = SALESORDERITEMMERCHANDISE.MERCHANDISEPRODUCTINSTANCEID
            inner join dbo.MERCHANDISEPRODUCT
                on MERCHANDISEPRODUCT.ID = MERCHANDISEPRODUCTINSTANCE.MERCHANDISEPRODUCTID
            outer apply (
                select isnull(sum(SALESORDERITEMITEMDISCOUNT.AMOUNT), 0) as AMOUNT
                from dbo.SALESORDERITEMITEMDISCOUNT
                where SALESORDERITEMITEMDISCOUNT.SALESORDERITEMID = SALESORDERITEM.ID
            ) as DISCOUNTS
            where
                SALESORDERITEM.SALESORDERID = @SALESORDERID 
                and SALESORDERITEM.TYPECODE = 14
                and MERCHANDISEPRODUCT.ISDISCOUNTABLE = 1
        end

        declare @TOTALADJUSTABLETODISCOUNT money
        set @TOTALTODISCOUNT = @TOTALTODISCOUNTMERCHANDISE + @TOTALTODISCOUNTTICKETS        
        set @TOTALADJUSTABLETODISCOUNT = @TOTALTODISCOUNT

        --configured order discounts

        if @TOTALTODISCOUNT > 0 and @CONFIGUREDORDERDISCOUNTSAVAILABLE = 1 begin
            if @CHANGEAGENTID is null
                exec dbo.USP_CHANGEAGENT_GETORCREATECHANGEAGENT @CHANGEAGENTID output;                                

            declare @SALESORDERITEMID uniqueidentifier;                                    

            set @SALESORDERITEMID = newid();

            declare @DISCOUNTID uniqueidentifier;
            declare @DISCOUNTAMOUNT decimal(20, 2);
            declare @DISCOUNTNAME nvarchar(255);
            declare @APPLIESTOMERCHANDISE bit;
            declare @APPLIESTOTICKETS bit;

            -- Grabbing the best deal out of all the discounts available.

            select top 1
                @DISCOUNTID = DISCOUNT.ID,
                @DISCOUNTNAME = coalesce(EXISTINGDISCOUNTS.ORIGINALNAME, DISCOUNT.NAME),
                @DISCOUNTAMOUNT =
                case
                    when DISCOUNT.CALCULATIONTYPECODE = 0 then  -- Amount off

                        case
                            when DISCOUNT.APPLIESTOMERCHANDISE = 1 and DISCOUNT.APPLIESTOTICKETS = 1 then
                                case when DISCOUNT.AMOUNT >= @TOTALTODISCOUNT then
                                    @TOTALTODISCOUNT
                                else
                                    DISCOUNT.AMOUNT             
                                end
                            else
                                case when DISCOUNT.AMOUNT >= @TOTALTODISCOUNTTICKETS then
                                    @TOTALTODISCOUNTTICKETS
                                else
                                    DISCOUNT.AMOUNT
                                end
                            end
                    else
                        case 
                            when DISCOUNT.APPLIESTOMERCHANDISE = 1 and DISCOUNT.APPLIESTOTICKETS = 1 then
                                @TOTALTODISCOUNT * (DISCOUNT.[PERCENT]/100)
                        else
                            case 
                                when DISCOUNT.APPLIESTOMERCHANDISE = 0 and DISCOUNT.APPLIESTOTICKETS = 1 then
                                    @TOTALTODISCOUNTTICKETS * (DISCOUNT.[PERCENT]/100)
                            else
                                @TOTALTODISCOUNTMERCHANDISE * (DISCOUNT.[PERCENT]/100)
                            end
                        end
                    end,
                @APPLIESTOMERCHANDISE = DISCOUNT.APPLIESTOMERCHANDISE,
                @APPLIESTOTICKETS = DISCOUNT.APPLIESTOTICKETS
            from @SALESORDERAVAILABLEDISCOUNTS SALESORDERAVAILABLEDISCOUNTS 
            inner join dbo.DISCOUNT on SALESORDERAVAILABLEDISCOUNTS.DISCOUNTID = DISCOUNT.ID
            left join @EXISTINGORDERDISCOUNTS EXISTINGDISCOUNTS on EXISTINGDISCOUNTS.DISCOUNTID = DISCOUNT.ID
            order by
                3 desc, SALESORDERAVAILABLEDISCOUNTS.APPLIEDMANUALLY desc

            -- Get total applied to tickets, if it's split up as an order amount level discount, gonna have to see the total

            -- of tickets in the order and the total of the discountable merchandise in the order and split evenly amongst them.

            -- If applies to both and is a percentage... gotta do the same thing

            -- If applies to only one... just put the total discount amount there.



            -- check to see if it is split between tickets and merchandise... if split between merchandise and tickets and

            -- there are amounts to discount (@TOTALTODISCOUNTMERCHANDISE and @TOTALTODISCOUNTTICKETS) 


            if @DISCOUNTAMOUNT > 0 begin
                declare @TOTALDISCOUNTABLEFORTHISDISCOUNT money;
                declare @DISCOUNTEDITEMS xml

                if @APPLIESTOMERCHANDISE = 1 and @APPLIESTOTICKETS = 1 begin
                    set @TOTALDISCOUNTABLEFORTHISDISCOUNT = @TOTALTODISCOUNT

                    set @DISCOUNTEDITEMS = 
                    (
                        select
                            ID,
                            AMOUNT
                        from
                        (
                            select
                                SALESORDERITEM.ID,
                                (TOTAL -  sum(coalesce(SALESORDERITEMITEMDISCOUNT.amount, 0))) AMOUNT
                            from dbo.SALESORDERITEM
                            inner join dbo.SALESORDERITEMMERCHANDISE
                                on SALESORDERITEMMERCHANDISE.ID = SALESORDERITEM.ID
                            inner join dbo.MERCHANDISEPRODUCTINSTANCE
                                on MERCHANDISEPRODUCTINSTANCE.ID = SALESORDERITEMMERCHANDISE.MERCHANDISEPRODUCTINSTANCEID
                            inner join dbo.MERCHANDISEPRODUCT
                                on MERCHANDISEPRODUCT.ID = MERCHANDISEPRODUCTINSTANCE.MERCHANDISEPRODUCTID
                            left join dbo.SALESORDERITEMITEMDISCOUNT on
                                SALESORDERITEMITEMDISCOUNT.SALESORDERITEMID = SALESORDERITEM.ID
                            where
                                TYPECODE = 14
                                and MERCHANDISEPRODUCT.ISDISCOUNTABLE = 1 and
                                SALESORDERID = @SALESORDERID
                            group by SALESORDERITEM.ID, SALESORDERITEM.TOTAL

                            union all

                            select 
                                SALESORDERITEM.ID,
                                (TOTAL -  sum(coalesce(SALESORDERITEMITEMDISCOUNT.amount, 0))) AMOUNT
                            from dbo.SALESORDERITEM
                            left join dbo.SALESORDERITEMITEMDISCOUNT on
                                SALESORDERITEMITEMDISCOUNT.SALESORDERITEMID = SALESORDERITEM.ID
                            where
                                TYPECODE = 0 and
                                SALESORDERID = @SALESORDERID
                            group by SALESORDERITEM.ID, SALESORDERITEM.TOTAL
                        ) ITEMS
                        for xml raw('ITEM'),type,elements,root('AMOUNTSTOPRORATE'),binary base64
                    )
                end
                else if @APPLIESTOMERCHANDISE = 1 and @APPLIESTOTICKETS = 0 begin
                    set @TOTALDISCOUNTABLEFORTHISDISCOUNT = @TOTALTODISCOUNTMERCHANDISE;

                    set @DISCOUNTEDITEMS = 
                    (
                        select
                            ID,
                            AMOUNT
                        from
                        (
                            select
                                SALESORDERITEM.ID,
                                (TOTAL -  sum(coalesce(SALESORDERITEMITEMDISCOUNT.amount, 0))) AMOUNT
                            from dbo.SALESORDERITEM
                            inner join dbo.SALESORDERITEMMERCHANDISE
                                on SALESORDERITEMMERCHANDISE.ID = SALESORDERITEM.ID
                            inner join dbo.MERCHANDISEPRODUCTINSTANCE
                                on MERCHANDISEPRODUCTINSTANCE.ID = SALESORDERITEMMERCHANDISE.MERCHANDISEPRODUCTINSTANCEID
                            inner join dbo.MERCHANDISEPRODUCT
                                on MERCHANDISEPRODUCT.ID = MERCHANDISEPRODUCTINSTANCE.MERCHANDISEPRODUCTID
                            left join dbo.SALESORDERITEMITEMDISCOUNT on
                                SALESORDERITEMITEMDISCOUNT.SALESORDERITEMID = SALESORDERITEM.ID
                            where
                                TYPECODE = 14
                                and MERCHANDISEPRODUCT.ISDISCOUNTABLE = 1 and
                                SALESORDERID = @SALESORDERID
                            group by SALESORDERITEM.ID, SALESORDERITEM.TOTAL
                        ) ITEMS
                        for xml raw('ITEM'),type,elements,root('AMOUNTSTOPRORATE'),binary base64
                    )
                end
                else begin
                    set @TOTALDISCOUNTABLEFORTHISDISCOUNT = @TOTALTODISCOUNTTICKETS;
                    set @DISCOUNTEDITEMS = 
                    (

                        select
                            ID,
                            AMOUNT
                        from
                        (
                            select 
                                SALESORDERITEM.ID,
                                (TOTAL -  sum(coalesce(SALESORDERITEMITEMDISCOUNT.amount, 0))) AMOUNT
                            from dbo.SALESORDERITEM
                            left join dbo.SALESORDERITEMITEMDISCOUNT on
                                SALESORDERITEMITEMDISCOUNT.SALESORDERITEMID = SALESORDERITEM.ID
                            where
                                TYPECODE = 0 and
                                SALESORDERID = @SALESORDERID
                            group by SALESORDERITEM.ID, SALESORDERITEM.TOTAL
                        ) ITEMS
                        for xml raw('ITEM'),type,elements,root('AMOUNTSTOPRORATE'),binary base64
                    )
                end

                insert into dbo.SALESORDERITEM
                (
                    ID,
                    SALESORDERID,
                    TYPECODE,
                    [DESCRIPTION],
                    QUANTITY,
                    PRICE,
                    [PERCENT],            
                    ADDEDBYID,
                    CHANGEDBYID,
                    DATEADDED,
                    DATECHANGED,
                    ASSEMBLYNAME,
                    SYSTEMTYPENAME
                )
                select
                    @SALESORDERITEMID,
                    @SALESORDERID,
                    5,
                    @DISCOUNTNAME,
                    1,
                    @DISCOUNTAMOUNT,
                    0,        
                    @CHANGEAGENTID,
                    @CHANGEAGENTID,
                    @CURRENTDATE,
                    @CURRENTDATE,
                    case @SALESMETHODTYPECODE when 2 then 'Blackbaud.AppFx.CMS.FCL.ShoppingCart.SalesOrder' else '' end,
                    case @SALESMETHODTYPECODE when 2 then 'Blackbaud.AppFx.ContentManagement.FrameworkClassLibrary.ShoppingCart.SalesOrder.DiscountItem' else '' end

                insert into dbo.SALESORDERITEMORDERDISCOUNT
                (
                    ID,
                    DISCOUNTID,    
                    DISCOUNTNAME,
                    ISADJUSTABLEDISCOUNT,
                    ADDEDBYID,
                    CHANGEDBYID,
                    DATEADDED,
                    DATECHANGED
                )    
                select
                    @SALESORDERITEMID,
                    @DISCOUNTID,
                    coalesce(@DISCOUNTNAME,''),
                    0,
                    @CHANGEAGENTID,
                    @CHANGEAGENTID,
                    @CURRENTDATE,
                    @CURRENTDATE
            end

            insert into dbo.SALESORDERITEMORDERDISCOUNTDETAIL
            (
                ID,
                SALESORDERITEMID,
                SALESORDERITEMORDERDISCOUNTID,
                AMOUNT,
                ADDEDBYID,
                CHANGEDBYID,
                DATEADDED,
                DATECHANGED
            )
            select 
                newid(),
                SPLITS.ID,
                @SALESORDERITEMID,
                SPLITS.AMOUNT,
                @CHANGEAGENTID,
                @CHANGEAGENTID,
                @CURRENTDATE,
                @CURRENTDATE
            from dbo.UFN_SPLITS_GETPRORATEDSPLITS(@TOTALDISCOUNTABLEFORTHISDISCOUNT, @DISCOUNTAMOUNT, @DISCOUNTEDITEMS) as SPLITS
            where SPLITS.AMOUNT > 0;

            set @TOTALADJUSTABLETODISCOUNT = @TOTALTODISCOUNT - @DISCOUNTAMOUNT

        end                                

            --adjustable discounts

        if @TOTALADJUSTABLETODISCOUNT > 0 and @ADJUSTABLEORDERDISCOUNTAVAILABLE = 1 begin                                    
            declare @ADJUSTABLETYPE as smallint
            declare @ADJUSTABLEAMOUNT as decimal(20,2)
            declare @ADJUSTABLEPERCENT as decimal(20,2)

            select
                @ADJUSTABLETYPE = CALCULATIONTYPECODE,
                @ADJUSTABLEAMOUNT = AMOUNT,
                @ADJUSTABLEPERCENT = [PERCENT],
                @DISCOUNTNAME = coalesce(DISCOUNTNAME, 'Adjustable discount')
            from
                dbo.SALESORDERADJUSTABLEDISCOUNT
            where
                SALESORDERID = @SALESORDERID                                                                                                    

            if @ADJUSTABLETYPE = 1 begin
                set @DISCOUNTAMOUNT = (@ADJUSTABLEPERCENT * 0.01) * @TOTALADJUSTABLETODISCOUNT
            end
            else begin
                if @ADJUSTABLEAMOUNT > @TOTALADJUSTABLETODISCOUNT
                    set @ADJUSTABLEAMOUNT = @TOTALADJUSTABLETODISCOUNT

                set @DISCOUNTAMOUNT = @ADJUSTABLEAMOUNT
            end

            if @DISCOUNTAMOUNT > 0 begin

                set @DISCOUNTEDITEMS = 
                (

                    select
                        ID,
                        AMOUNT
                    from
                    (
                        select
                            SALESORDERITEM.ID,
                            (SALESORDERITEM.TOTAL -  coalesce(ITEMDISCOUNTS.TOTAL, 0) - coalesce(ORDERDISCOUNTS.TOTAL, 0)) AMOUNT
                        from dbo.SALESORDERITEM
                        inner join dbo.SALESORDERITEMMERCHANDISE
                            on SALESORDERITEMMERCHANDISE.ID = SALESORDERITEM.ID
                        inner join dbo.MERCHANDISEPRODUCTINSTANCE
                            on MERCHANDISEPRODUCTINSTANCE.ID = SALESORDERITEMMERCHANDISE.MERCHANDISEPRODUCTINSTANCEID
                        inner join dbo.MERCHANDISEPRODUCT
                            on MERCHANDISEPRODUCT.ID = MERCHANDISEPRODUCTINSTANCE.MERCHANDISEPRODUCTID
                        outer apply (
                            select sum(SALESORDERITEMITEMDISCOUNT.AMOUNT) TOTAL
                            from dbo.SALESORDERITEMITEMDISCOUNT
                            where SALESORDERITEMITEMDISCOUNT.SALESORDERITEMID = SALESORDERITEM.ID
                        ) ITEMDISCOUNTS
                        outer apply (
                            select sum(SALESORDERITEMORDERDISCOUNTDETAIL.AMOUNT) TOTAL
                            from dbo.SALESORDERITEMORDERDISCOUNTDETAIL
                            where SALESORDERITEMORDERDISCOUNTDETAIL.SALESORDERITEMID = SALESORDERITEM.ID
                        ) ORDERDISCOUNTS    
                        where
                            SALESORDERITEM.TYPECODE = 14
                            and MERCHANDISEPRODUCT.ISDISCOUNTABLE = 1
                            and SALESORDERITEM.SALESORDERID = @SALESORDERID
                        group by SALESORDERITEM.ID, SALESORDERITEM.TOTAL, ITEMDISCOUNTS.TOTAL, ORDERDISCOUNTS.TOTAL

                        union all

                        select 
                            SALESORDERITEM.ID,
                            (SALESORDERITEM.TOTAL -  coalesce(ITEMDISCOUNTS.TOTAL, 0) - coalesce(ORDERDISCOUNTS.TOTAL, 0)) AMOUNT
                        from dbo.SALESORDERITEM
                        outer apply (
                            select sum(SALESORDERITEMITEMDISCOUNT.AMOUNT) TOTAL
                            from dbo.SALESORDERITEMITEMDISCOUNT
                            where SALESORDERITEMITEMDISCOUNT.SALESORDERITEMID = SALESORDERITEM.ID
                        ) ITEMDISCOUNTS
                        outer apply (
                            select sum(SALESORDERITEMORDERDISCOUNTDETAIL.AMOUNT) TOTAL
                            from dbo.SALESORDERITEMORDERDISCOUNTDETAIL
                            where SALESORDERITEMORDERDISCOUNTDETAIL.SALESORDERITEMID = SALESORDERITEM.ID
                        ) ORDERDISCOUNTS                    
                        where
                            SALESORDERITEM.TYPECODE = 0
                            and SALESORDERITEM.SALESORDERID = @SALESORDERID
                        group by SALESORDERITEM.ID, SALESORDERITEM.TOTAL, ITEMDISCOUNTS.TOTAL, ORDERDISCOUNTS.TOTAL
                    ) ITEMS
                    for xml raw('ITEM'),type,elements,root('AMOUNTSTOPRORATE'),binary base64
                )

                set @SALESORDERITEMID = newid()

                insert into dbo.SALESORDERITEM
                (
                    ID,
                    SALESORDERID,
                    TYPECODE,
                    [DESCRIPTION],
                    QUANTITY,
                    PRICE,
                    [PERCENT],            
                    ADDEDBYID,
                    CHANGEDBYID,
                    DATEADDED,
                    DATECHANGED,
                    ASSEMBLYNAME,
                    SYSTEMTYPENAME
                )
                select
                    @SALESORDERITEMID,
                    @SALESORDERID,
                    5, -- Discount

                    @DISCOUNTNAME,
                    1,
                    @DISCOUNTAMOUNT,
                    0,        
                    @CHANGEAGENTID,
                    @CHANGEAGENTID,
                    @CURRENTDATE,
                    @CURRENTDATE,
                    case @SALESMETHODTYPECODE when 2 then 'Blackbaud.AppFx.CMS.FCL.ShoppingCart.SalesOrder' else '' end,
                    case @SALESMETHODTYPECODE when 2 then 'Blackbaud.AppFx.ContentManagement.FrameworkClassLibrary.ShoppingCart.SalesOrder.DiscountItem' else '' end

                insert into dbo.SALESORDERITEMORDERDISCOUNT
                (
                    ID,
                    DISCOUNTID,    
                    DISCOUNTNAME,
                    ISADJUSTABLEDISCOUNT,
                    ADDEDBYID,
                    CHANGEDBYID,
                    DATEADDED,
                    DATECHANGED
                )    
                select
                    @SALESORDERITEMID,
                    null,
                    coalesce(@DISCOUNTNAME,''),
                    1,
                    @CHANGEAGENTID,
                    @CHANGEAGENTID,
                    @CURRENTDATE,
                    @CURRENTDATE

                insert into dbo.SALESORDERITEMORDERDISCOUNTDETAIL
                (
                    ID,
                    SALESORDERITEMID,
                    SALESORDERITEMORDERDISCOUNTID,
                    AMOUNT,
                    ADDEDBYID,
                    CHANGEDBYID,
                    DATEADDED,
                    DATECHANGED
                )
                select 
                    newid(),
                    SPLITS.ID,
                    @SALESORDERITEMID,
                    SPLITS.AMOUNT,
                    @CHANGEAGENTID,
                    @CHANGEAGENTID,
                    @CURRENTDATE,
                    @CURRENTDATE
                from dbo.UFN_SPLITS_GETPRORATEDSPLITS(@TOTALADJUSTABLETODISCOUNT, @DISCOUNTAMOUNT, @DISCOUNTEDITEMS) as SPLITS
                where SPLITS.AMOUNT > 0;
            end
        end
    end