USP_APPEALMAILINGSETUPLETTER_DELETE

CREATE procedure dbo.USP_APPEALMAILINGSETUPLETTER_DELETE
(
    @ID uniqueidentifier,
    @CHANGEAGENTID uniqueidentifier
)
as begin
    --check deletion rules, if any


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

    declare @CURRENTDATE datetime = getDate();
    declare @APPEALMAILINGSETUPID uniqueidentifier;
    declare @MAILPACKAGEID uniqueidentifier;
    declare @MAILSEGMENTID uniqueidentifier;
    declare @MAILLETTERCODEID uniqueidentifier;
    declare @EMAILPACKAGEID uniqueidentifier;
    declare @EMAILSEGMENTID uniqueidentifier;
    declare @EMAILLETTERCODEID uniqueidentifier;
    declare @CANNEDSELECTIONIDSETREGISTERID uniqueidentifier;

    select 
        @APPEALMAILINGSETUPID = APPEALMAILINGSETUPLETTER.APPEALMAILINGSETUPID,
        @MAILPACKAGEID = APPEALMAILINGSETUPLETTER.MAILPACKAGEID,
        @MAILSEGMENTID = APPEALMAILINGSETUPLETTER.MAILSEGMENTID,
        @MAILLETTERCODEID = MAILPACKAGE.LETTERCODEID,
        @EMAILPACKAGEID = APPEALMAILINGSETUPLETTER.EMAILPACKAGEID,
        @EMAILSEGMENTID = APPEALMAILINGSETUPLETTER.EMAILSEGMENTID,
        @EMAILLETTERCODEID = EMAILPACKAGE.LETTERCODEID,
        @CANNEDSELECTIONIDSETREGISTERID = APPEALMAILINGSETUPLETTER.CANNEDSELECTIONIDSETREGISTERID
    from dbo.APPEALMAILINGSETUPLETTER
    left join dbo.MKTPACKAGE MAILPACKAGE
        on APPEALMAILINGSETUPLETTER.MAILPACKAGEID = MAILPACKAGE.ID
    left join dbo.MKTPACKAGE EMAILPACKAGE
        on APPEALMAILINGSETUPLETTER.EMAILPACKAGEID = EMAILPACKAGE.ID
    where APPEALMAILINGSETUPLETTER.ID = @ID;

    if @APPEALMAILINGSETUPID is not null
        exec dbo.USP_APPEALMAILINGSETUPLETTER_REMOVE @ID, @CHANGEAGENTID;

    exec USP_APPEALMAILINGSETUPLETTER_DELETEBYID_WITHCHANGEAGENTID @ID, @CHANGEAGENTID;

    if @MAILPACKAGEID is not null
        begin
            update dbo.MKTPACKAGE set
                ISSYSTEM = 0,
                CHANGEDBYID = @CHANGEAGENTID,
                DATECHANGED = @CURRENTDATE
            where ID = @MAILPACKAGEID;

            exec dbo.USP_MKTPACKAGE_DELETE @MAILPACKAGEID, @CHANGEAGENTID;
        end

    if @MAILSEGMENTID is not null
        begin
            update dbo.MKTSEGMENT set
                ISSYSTEM = 0,
                CHANGEDBYID = @CHANGEAGENTID,
                DATECHANGED = @CURRENTDATE
            where ID = @MAILSEGMENTID;

            exec dbo.USP_MKTSEGMENT_DELETE @MAILSEGMENTID, @CHANGEAGENTID;
        end

    if @MAILLETTERCODEID is not null
        begin
            update dbo.LETTERCODE set
                ISSYSTEM = 0,
                CHANGEDBYID = @CHANGEAGENTID,
                DATECHANGED = @CURRENTDATE
            where ID = @MAILLETTERCODEID;

            exec dbo.USP_LETTERCODE_DELETE @MAILLETTERCODEID, @CHANGEAGENTID;
        end

    if @EMAILPACKAGEID is not null
        begin
            update dbo.MKTPACKAGE set
                ISSYSTEM = 0,
                CHANGEDBYID = @CHANGEAGENTID,
                DATECHANGED = @CURRENTDATE
            where ID = @EMAILPACKAGEID;

            exec dbo.USP_MKTPACKAGE_DELETE @EMAILPACKAGEID, @CHANGEAGENTID;
        end

    if @EMAILSEGMENTID is not null
        begin
            update dbo.MKTSEGMENT set
                ISSYSTEM = 0,
                CHANGEDBYID = @CHANGEAGENTID,
                DATECHANGED = @CURRENTDATE
            where ID = @EMAILSEGMENTID;

            exec dbo.USP_MKTSEGMENT_DELETE @EMAILSEGMENTID, @CHANGEAGENTID;
        end

    if @EMAILLETTERCODEID is not null
        begin
            update dbo.LETTERCODE set
                ISSYSTEM = 0,
                CHANGEDBYID = @CHANGEAGENTID,
                DATECHANGED = @CURRENTDATE
            where ID = @EMAILLETTERCODEID;

            exec dbo.USP_LETTERCODE_DELETE @EMAILLETTERCODEID, @CHANGEAGENTID;
        end

    if @CANNEDSELECTIONIDSETREGISTERID is not null
        begin
            update dbo.IDSETREGISTER set
                ISSYSTEM = 0,
                CHANGEDBYID = @CHANGEAGENTID,
                DATECHANGED = @CURRENTDATE
            where ID = @CANNEDSELECTIONIDSETREGISTERID;

            exec dbo.USP_IDSETREGISTER_DELETEBYID_WITHCHANGEAGENTID @CANNEDSELECTIONIDSETREGISTERID, @CHANGEAGENTID;
        end

    return 0;

end