USP_COMMUNICATIONLETTER_MOVEUP

CREATE procedure dbo.USP_COMMUNICATIONLETTER_MOVEUP
(
    @ID uniqueidentifier,
    @CHANGEAGENTID uniqueidentifier
)
as begin
    declare @SEGMENTATIONID uniqueidentifier;
    declare @SEQUENCE int;
    declare @CHANNELCODE tinyint;
    declare @CHANNELPREFERENCECODE tinyint;
    declare @MAILSEGMENTATIONSEGMENTID uniqueidentifier;
    declare @EMAILSEGMENTATIONSEGMENTID uniqueidentifier;
    declare @CHANNELCODE_ABOVE tinyint;
    declare @CURRENTDATE datetime = getDate();
    declare @ACTIVE bit = 0;
    declare @DELETEAPPEALINFO bit = 0;

    if @CHANGEAGENTID is null  
      exec dbo.[USP_CHANGEAGENT_GETORCREATECHANGEAGENT] @CHANGEAGENTID output;

    begin try
        select
            @SEGMENTATIONID = COMMUNICATIONLETTER.SEGMENTATIONID,
            @ACTIVE = MKTSEGMENTATION.ACTIVE,
            @DELETEAPPEALINFO = case APPEALMAILING.APPEALID when null then 0 else 1 end,
            @SEQUENCE = COMMUNICATIONLETTER.SEQUENCE,
            @CHANNELCODE = COMMUNICATIONLETTER.CHANNELCODE,
            @CHANNELPREFERENCECODE = COMMUNICATIONLETTER.CHANNELPREFERENCECODE,
            @MAILSEGMENTATIONSEGMENTID = MAILSEGMENTATIONSEGMENT.ID,
            @EMAILSEGMENTATIONSEGMENTID = EMAILSEGMENTATIONSEGMENT.ID
        from dbo.COMMUNICATIONLETTER
        inner join dbo.MKTSEGMENTATION
            on COMMUNICATIONLETTER.SEGMENTATIONID = MKTSEGMENTATION.ID
        left outer join dbo.APPEALMAILING
            on COMMUNICATIONLETTER.SEGMENTATIONID = APPEALMAILING.ID
        left outer join dbo.MKTSEGMENTATIONSEGMENT MAILSEGMENTATIONSEGMENT
            on COMMUNICATIONLETTER.MAILPACKAGEID = MAILSEGMENTATIONSEGMENT.PACKAGEID
            and COMMUNICATIONLETTER.MAILSEGMENTID = MAILSEGMENTATIONSEGMENT.SEGMENTID
        left outer join dbo.MKTSEGMENTATIONSEGMENT EMAILSEGMENTATIONSEGMENT
            on COMMUNICATIONLETTER.EMAILPACKAGEID = EMAILSEGMENTATIONSEGMENT.PACKAGEID
            and COMMUNICATIONLETTER.EMAILSEGMENTID = EMAILSEGMENTATIONSEGMENT.SEGMENTID
        where COMMUNICATIONLETTER.ID = @ID;

        if @SEQUENCE = 1
            raiserror('BBERR_COMMUNICATIONLETTER_CANNOTMOVEUP', 13, 1);

        select
            @CHANNELCODE_ABOVE = CHANNELCODE
        from dbo.COMMUNICATIONLETTER
        where COMMUNICATIONLETTER.SEGMENTATIONID = @SEGMENTATIONID
            and COMMUNICATIONLETTER.SEQUENCE = @SEQUENCE - 1;

        -- Rollback active mailings before you can update move segmentation segments around

        if @ACTIVE = 1 or dbo.UFN_MKTSEGMENTATION_HASACTIVESEGMENTS(@SEGMENTATIONID) = 1
            exec dbo.USP_MKTSEGMENTATIONACTIVATE_ROLLBACK @SEGMENTATIONID, @CHANGEAGENTID, @DELETEAPPEALINFO, 1;

        update dbo.COMMUNICATIONLETTER set
            SEQUENCE = 
                case COMMUNICATIONLETTER.SEQUENCE
                    when @SEQUENCE then @SEQUENCE - 1
                    when @SEQUENCE - 1 then @SEQUENCE
                end,
            CHANGEDBYID = @CHANGEAGENTID,
            DATECHANGED = @CURRENTDATE
        where COMMUNICATIONLETTER.SEGMENTATIONID = @SEGMENTATIONID
            and (
                COMMUNICATIONLETTER.SEQUENCE = @SEQUENCE
                or COMMUNICATIONLETTER.SEQUENCE = @SEQUENCE - 1
            );

        if @CHANNELCODE = 0 and @MAILSEGMENTATIONSEGMENTID is not null
            begin
                exec dbo.USP_MKTSEGMENTATIONSEGMENT_MOVEUP @MAILSEGMENTATIONSEGMENTID, @CHANGEAGENTID;

                if @CHANNELCODE_ABOVE = 2
                    exec dbo.USP_MKTSEGMENTATIONSEGMENT_MOVEUP @MAILSEGMENTATIONSEGMENTID, @CHANGEAGENTID;
            end

        if @CHANNELCODE = 1 and @EMAILSEGMENTATIONSEGMENTID is not null
            begin
                exec dbo.USP_MKTSEGMENTATIONSEGMENT_MOVEUP @EMAILSEGMENTATIONSEGMENTID, @CHANGEAGENTID;

                if @CHANNELCODE_ABOVE = 2
                    exec dbo.USP_MKTSEGMENTATIONSEGMENT_MOVEUP @EMAILSEGMENTATIONSEGMENTID, @CHANGEAGENTID;
            end

        -- if sending both mail and email, you must move the preferred channel first

        if @CHANNELCODE = 2
            begin
                if @CHANNELPREFERENCECODE = 0
                    begin
                        if @EMAILSEGMENTATIONSEGMENTID is not null
                            begin
                                exec dbo.USP_MKTSEGMENTATIONSEGMENT_MOVEUP @EMAILSEGMENTATIONSEGMENTID, @CHANGEAGENTID;

                                if @CHANNELCODE_ABOVE = 2
                                    exec dbo.USP_MKTSEGMENTATIONSEGMENT_MOVEUP @EMAILSEGMENTATIONSEGMENTID, @CHANGEAGENTID;
                            end

                        if @MAILSEGMENTATIONSEGMENTID is not null
                            begin
                                exec dbo.USP_MKTSEGMENTATIONSEGMENT_MOVEUP @MAILSEGMENTATIONSEGMENTID, @CHANGEAGENTID;

                                if @CHANNELCODE_ABOVE = 2
                                    exec dbo.USP_MKTSEGMENTATIONSEGMENT_MOVEUP @MAILSEGMENTATIONSEGMENTID, @CHANGEAGENTID;
                            end
                    end
                else
                    begin
                        if @MAILSEGMENTATIONSEGMENTID is not null
                            begin
                                exec dbo.USP_MKTSEGMENTATIONSEGMENT_MOVEUP @MAILSEGMENTATIONSEGMENTID, @CHANGEAGENTID;

                                if @CHANNELCODE_ABOVE = 2
                                    exec dbo.USP_MKTSEGMENTATIONSEGMENT_MOVEUP @MAILSEGMENTATIONSEGMENTID, @CHANGEAGENTID;
                            end

                        if @EMAILSEGMENTATIONSEGMENTID is not null
                            begin
                                exec dbo.USP_MKTSEGMENTATIONSEGMENT_MOVEUP @EMAILSEGMENTATIONSEGMENTID, @CHANGEAGENTID;

                                if @CHANNELCODE_ABOVE = 2
                                    exec dbo.USP_MKTSEGMENTATIONSEGMENT_MOVEUP @EMAILSEGMENTATIONSEGMENTID, @CHANGEAGENTID;
                            end
                    end
            end
    end try

    begin catch
        exec dbo.USP_RAISE_ERROR;
        return 1;
      end catch

    return 0;

end