USP_DATAFORMTEMPLATE_EDIT_SALESORDER_MANUALDISCOUNTS

CREATE procedure dbo.USP_DATAFORMTEMPLATE_EDIT_SALESORDER_MANUALDISCOUNTS (
    @ID uniqueidentifier,
    @CHANGEAGENTID uniqueidentifier = null,
    @MANUALDISCOUNTS xml
)
as
    set nocount on;

    if @CHANGEAGENTID is null  
        exec dbo.USP_CHANGEAGENT_GETORCREATECHANGEAGENT @CHANGEAGENTID output

    declare @CURRENTDATE datetime
    set @CURRENTDATE = getdate();

    begin try        
        declare @e int;
        declare @contextCache varbinary(128);
        declare @STATUSCODE tinyint;
        declare @SALESMETHODTYPECODE tinyint;

        select 
            @SALESMETHODTYPECODE = SALESMETHODTYPECODE,
            @STATUSCODE = STATUSCODE
        from dbo.SALESORDER
        where ID = @ID

        if @STATUSCODE in (1, 5) -- Complete or Cancelled

            raiserror('BBERR_INVALIDSTATUS', 13, 1);

        set @contextCache = CONTEXT_INFO();

        if not @CHANGEAGENTID is null
            set CONTEXT_INFO @CHANGEAGENTID

        --Delete the manual discounts and promotions that we chose to delete on the form

        delete from dbo.SALESORDERMANUALDISCOUNT
        where
            SALESORDERID = @ID and
            DISCOUNTID not in (select ID from dbo.UFN_ORDER_GETAPPLIEDDISCOUNTS_FROMITEMLISTXML(@MANUALDISCOUNTS))

        if @SALESMETHODTYPECODE <> 3 -- Group Sales

        begin
            delete from dbo.SALESORDERMEMBERSHIPPROMO
            where
                SALESORDERID = @ID and
                MEMBERSHIPPROMOID not in (select ID from dbo.UFN_ORDER_GETAPPLIEDDISCOUNTS_FROMITEMLISTXML(@MANUALDISCOUNTS))
        end

         --Delete the adjustable discounts that we chose to delete on the form

        delete from dbo.SALESORDERADJUSTABLEDISCOUNT
        where
            SALESORDERID = @ID and
            ID not in (select ID from dbo.UFN_ORDER_GETAPPLIEDDISCOUNTS_FROMITEMLISTXML(@MANUALDISCOUNTS))

        --Delete the adjustable discounts that we chose to delete on the form

        if @SALESMETHODTYPECODE <> 3 -- Group Sales

        begin
            delete from dbo.SALESORDERITEMMEMBERSHIPITEMPROMOTION
            from dbo.SALESORDERITEMMEMBERSHIPITEMPROMOTION 
            inner join dbo.SALESORDERITEM on
                SALESORDERITEMMEMBERSHIPITEMPROMOTION.SALESORDERITEMID = SALESORDERITEM.ID
            where
                SALESORDERITEM.SALESORDERID = @ID and
                SALESORDERITEMMEMBERSHIPITEMPROMOTION.ID not in (select ID from dbo.UFN_ORDER_GETAPPLIEDDISCOUNTS_FROMITEMLISTXML(@MANUALDISCOUNTS))
        end

        --Delete the corresponding limits

        delete from dbo.[SALESORDERDISCOUNTLIMITOVERRIDE]
        where
            [SALESORDERID] = @ID and
            [DISCOUNTID] not in (select [DISCOUNTID] from dbo.[SALESORDERMANUALDISCOUNT] where [SALESORDERID] = @ID)

        --Delete the limits where we chose to make the discount unlimited

        delete from dbo.[SALESORDERDISCOUNTLIMITOVERRIDE]
        where
            [SALESORDERID] = @ID and
            [DISCOUNTID] in (select [ID] from dbo.UFN_ORDER_GETAPPLIEDDISCOUNTS_FROMITEMLISTXML(@MANUALDISCOUNTS) where [LIMITAPPLICATIONTYPECODE] = 0)

        --Update the limits that were updated on the form

        update [LIMIT]
            set [LIMIT].[NUMBEROFDISCOUNTSPERORDER] = [UPDATEDVALUES].[TIMESLIMITED]
        from dbo.[SALESORDERDISCOUNTLIMITOVERRIDE] [LIMIT]
        inner join dbo.UFN_ORDER_GETAPPLIEDDISCOUNTS_FROMITEMLISTXML(@MANUALDISCOUNTS) [UPDATEDVALUES]
            on [LIMIT].[DISCOUNTID] = [UPDATEDVALUES].[ID]
        where [LIMIT].[SALESORDERID] = @ID and
            [UPDATEDVALUES].[LIMITAPPLICATIONTYPECODE] = 1

        --Add the limits that were previously not on the form

        insert into dbo.[SALESORDERDISCOUNTLIMITOVERRIDE] (
            [ID],
            [SALESORDERID],
            [DISCOUNTID],
            [NUMBEROFDISCOUNTSPERORDER],
            [ADDEDBYID],
            [CHANGEDBYID],
            [DATEADDED],
            [DATECHANGED])
        select
            newid(),
            @ID,
            [ID],
            [TIMESLIMITED],
            @CHANGEAGENTID,
            @CHANGEAGENTID,
            @CURRENTDATE,
            @CURRENTDATE
        from dbo.UFN_ORDER_GETAPPLIEDDISCOUNTS_FROMITEMLISTXML(@MANUALDISCOUNTS) as [UPDATEDVALUES]
        where [ID] not in (select [DISCOUNTID] from dbo.[SALESORDERDISCOUNTLIMITOVERRIDE] where [SALESORDERID] = @ID) and
            [UPDATEDVALUES].[LIMITAPPLICATIONTYPECODE] = 1

        if not @contextCache is null
            set CONTEXT_INFO @contextCache

        select @e=@@error;

        if @e<>0 return -456; --always return non-zero sp result if an error occurs


        exec dbo.USP_SALESORDER_APPLYITEMDISCOUNTS @ID, @CHANGEAGENTID;
        exec dbo.USP_SALESORDER_APPLYORDERDISCOUNTS @ID, @CHANGEAGENTID;

        if @SALESMETHODTYPECODE <> 3 -- Group Sales

            exec dbo.USP_SALESORDER_UPDATEMEMBERSHIPPROMOS @ID, @CHANGEAGENTID;

        exec dbo.USP_SALESORDER_CALCULATETAXES @ID, @CHANGEAGENTID;
    end try
    begin catch
        exec dbo.USP_RAISE_ERROR
        return 1
    end catch

return 0;