USP_COMMUNICATIONLETTER_DELETE

CREATE procedure dbo.USP_COMMUNICATIONLETTER_DELETE
(
    @ID uniqueidentifier,
    @CHANGEAGENTID uniqueidentifier = null
)
as begin
    set nocount on;

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

    declare @CURRENTDATE datetime = getDate();
    declare @SEGMENTATIONID uniqueidentifier;
    declare @MAILPACKAGEID uniqueidentifier;
    declare @MAILSEGMENTID uniqueidentifier;
    declare @MAILSEGMENTIDSETREGISTERID uniqueidentifier;
    declare @EMAILPACKAGEID uniqueidentifier;
    declare @EMAILSEGMENTID uniqueidentifier;
    declare @EMAILSEGMENTIDSETREGISTERID uniqueidentifier;
    declare @COMMUNICATIONEXCLUSIONIDSETREGISTERID uniqueidentifier;

    select 
        @SEGMENTATIONID = COMMUNICATIONLETTER.SEGMENTATIONID,
        @MAILPACKAGEID = COMMUNICATIONLETTER.MAILPACKAGEID,
        @MAILSEGMENTID = COMMUNICATIONLETTER.MAILSEGMENTID,
        @EMAILPACKAGEID = COMMUNICATIONLETTER.EMAILPACKAGEID,
        @EMAILSEGMENTID = COMMUNICATIONLETTER.EMAILSEGMENTID,
        @COMMUNICATIONEXCLUSIONIDSETREGISTERID = COMMUNICATIONLETTERACTIVITYEXCLUSIONS.IDSETREGISTERID,
        @MAILSEGMENTIDSETREGISTERID = MAILSEGMENT.IDSETREGISTERID,
        @EMAILSEGMENTIDSETREGISTERID = EMAILSEGMENT.IDSETREGISTERID
    from dbo.COMMUNICATIONLETTER
    left join dbo.MKTPACKAGE MAILPACKAGE
        on COMMUNICATIONLETTER.MAILPACKAGEID = MAILPACKAGE.ID
    left join dbo.MKTPACKAGE EMAILPACKAGE
        on COMMUNICATIONLETTER.EMAILPACKAGEID = EMAILPACKAGE.ID
    left join dbo.MKTSEGMENT MAILSEGMENT
        on COMMUNICATIONLETTER.MAILSEGMENTID = MAILSEGMENT.ID
    left join dbo.MKTSEGMENT EMAILSEGMENT
        on COMMUNICATIONLETTER.EMAILSEGMENTID = EMAILSEGMENT.ID
    left join dbo.COMMUNICATIONLETTERACTIVITYEXCLUSIONS
        on COMMUNICATIONLETTER.ID = COMMUNICATIONLETTERACTIVITYEXCLUSIONS.COMMUNICATIONLETTERID
    where COMMUNICATIONLETTER.ID = @ID;

    if @SEGMENTATIONID is not null
        exec dbo.USP_COMMUNICATIONLETTER_REMOVE @ID, @CHANGEAGENTID;

    exec USP_COMMUNICATIONLETTER_DELETEBYID_WITHCHANGEAGENTID @ID, @CHANGEAGENTID;

    if @COMMUNICATIONEXCLUSIONIDSETREGISTERID is not null
        exec dbo.USP_IDSETREGISTER_DELETEBYID_WITHCHANGEAGENTID @COMMUNICATIONEXCLUSIONIDSETREGISTERID, @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;

            if @MAILSEGMENTIDSETREGISTERID is not null
                exec dbo.USP_IDSETREGISTER_DELETEBYID_WITHCHANGEAGENTID @MAILSEGMENTIDSETREGISTERID, @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;

            if @EMAILSEGMENTIDSETREGISTERID is not null
                exec dbo.USP_IDSETREGISTER_DELETEBYID_WITHCHANGEAGENTID @EMAILSEGMENTIDSETREGISTERID, @CHANGEAGENTID;
        end

    return 0;

end