USP_COMMUNICATIONEXCLUSIONS_CREATEORUPDATE

Create or update an instance of communication exclusions.

Parameters

Parameter	Parameter Type	Mode
@ID	uniqueidentifier	INOUT
@IDSETREGISTERID	uniqueidentifier	INOUT
@CHANGEAGENTID	uniqueidentifier	IN
@SEGMENTATIONID	uniqueidentifier	IN
@EXCLUDEBASEDONRECENTCOMMUNICATION	bit	IN
@NUMRECENTCOMMUNICATIONPERIODS	int	IN
@RECENTCOMMUNICATIONPERIODTYPECODE	tinyint	IN
@EXCLUDEBASEDONTOTALCOMMUNICATIONSINPASTYEAR	bit	IN
@NUMTOTALCOMMUNICATIONSINPASTYEAR	int	IN
@EXCLUDEBASEDONRECENTGIVING	bit	IN
@NUMRECENTGIVINGPERIODS	int	IN
@RECENTGIVINGPERIODTYPECODE	tinyint	IN
@EXCLUDEBASEDONTOTALGIVINGINPASTYEAR	bit	IN
@TOTALREVENUEAMOUNTINPASTYEAR	money	IN
@COMMUNICATIONTYPES	xml	IN

Definition

Copy



create procedure dbo.USP_COMMUNICATIONEXCLUSIONS_CREATEORUPDATE
(
    @ID uniqueidentifier = null output,
    @IDSETREGISTERID uniqueidentifier = null output,
    @CHANGEAGENTID uniqueidentifier = null,    
    @SEGMENTATIONID uniqueidentifier,
    @EXCLUDEBASEDONRECENTCOMMUNICATION bit,
    @NUMRECENTCOMMUNICATIONPERIODS int,    
    @RECENTCOMMUNICATIONPERIODTYPECODE tinyint,
    @EXCLUDEBASEDONTOTALCOMMUNICATIONSINPASTYEAR bit,
    @NUMTOTALCOMMUNICATIONSINPASTYEAR int,
    @EXCLUDEBASEDONRECENTGIVING bit,
    @NUMRECENTGIVINGPERIODS int,    
    @RECENTGIVINGPERIODTYPECODE tinyint,
    @EXCLUDEBASEDONTOTALGIVINGINPASTYEAR bit,
    @TOTALREVENUEAMOUNTINPASTYEAR money,
    @COMMUNICATIONTYPES xml
)
as
begin

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

    declare @CURRENTDATE datetime = getDate();

    select
        @ID = ID,
        @IDSETREGISTERID = IDSETREGISTERID
    from dbo.COMMUNICATIONEXCLUSIONS
    where SEGMENTATIONID = @SEGMENTATIONID;

    exec dbo.USP_COMMUNICATIONEXCLUSIONS_CREATEORUPDATEIDSET
        @IDSETREGISTERID output,
        @CHANGEAGENTID,
        @EXCLUDEBASEDONRECENTCOMMUNICATION,
        @NUMRECENTCOMMUNICATIONPERIODS,    
        @RECENTCOMMUNICATIONPERIODTYPECODE,
        @EXCLUDEBASEDONTOTALCOMMUNICATIONSINPASTYEAR,
        @NUMTOTALCOMMUNICATIONSINPASTYEAR,
        @EXCLUDEBASEDONRECENTGIVING,
        @NUMRECENTGIVINGPERIODS,    
        @RECENTGIVINGPERIODTYPECODE,
        @EXCLUDEBASEDONTOTALGIVINGINPASTYEAR,
        @TOTALREVENUEAMOUNTINPASTYEAR,
        @COMMUNICATIONTYPES;

    if @ID is null
        begin
            set @ID = newID();

            insert into dbo.COMMUNICATIONEXCLUSIONS
                (ID, SEGMENTATIONID, IDSETREGISTERID, EXCLUDEBASEDONRECENTCOMMUNICATION, NUMRECENTCOMMUNICATIONPERIODS, RECENTCOMMUNICATIONPERIODTYPECODE, 
                 EXCLUDEBASEDONTOTALCOMMUNICATIONSINPASTYEAR, NUMTOTALCOMMUNICATIONSINPASTYEAR, EXCLUDEBASEDONRECENTGIVING, NUMRECENTGIVINGPERIODS,
                 RECENTGIVINGPERIODTYPECODE, EXCLUDEBASEDONTOTALGIVINGINPASTYEAR, TOTALREVENUEAMOUNTINPASTYEAR,
                 ADDEDBYID, CHANGEDBYID, DATEADDED, DATECHANGED)
            values
                (@ID, @SEGMENTATIONID, @IDSETREGISTERID, @EXCLUDEBASEDONRECENTCOMMUNICATION, @NUMRECENTCOMMUNICATIONPERIODS, @RECENTCOMMUNICATIONPERIODTYPECODE, 
                 @EXCLUDEBASEDONTOTALCOMMUNICATIONSINPASTYEAR, @NUMTOTALCOMMUNICATIONSINPASTYEAR, @EXCLUDEBASEDONRECENTGIVING, @NUMRECENTGIVINGPERIODS,
                 @RECENTGIVINGPERIODTYPECODE, @EXCLUDEBASEDONTOTALGIVINGINPASTYEAR, @TOTALREVENUEAMOUNTINPASTYEAR,
                 @CHANGEAGENTID, @CHANGEAGENTID, @CURRENTDATE, @CURRENTDATE);

            exec dbo.USP_COMMUNICATIONEXCLUSION_GETCOMMUNICATIONTYPES_ADDFROMXML
                @ID,
                @COMMUNICATIONTYPES,
                @CHANGEAGENTID,
                @CURRENTDATE;        
        end
    else
        begin
            update dbo.COMMUNICATIONEXCLUSIONS set    
                SEGMENTATIONID = @SEGMENTATIONID,
                IDSETREGISTERID = @IDSETREGISTERID,
                EXCLUDEBASEDONRECENTCOMMUNICATION = @EXCLUDEBASEDONRECENTCOMMUNICATION,
                NUMRECENTCOMMUNICATIONPERIODS = @NUMRECENTCOMMUNICATIONPERIODS,
                RECENTCOMMUNICATIONPERIODTYPECODE = @RECENTCOMMUNICATIONPERIODTYPECODE,
                EXCLUDEBASEDONTOTALCOMMUNICATIONSINPASTYEAR = @EXCLUDEBASEDONTOTALCOMMUNICATIONSINPASTYEAR,
                NUMTOTALCOMMUNICATIONSINPASTYEAR = @NUMTOTALCOMMUNICATIONSINPASTYEAR,
                EXCLUDEBASEDONRECENTGIVING = @EXCLUDEBASEDONRECENTGIVING,
                NUMRECENTGIVINGPERIODS = @NUMRECENTGIVINGPERIODS,
                RECENTGIVINGPERIODTYPECODE = @RECENTGIVINGPERIODTYPECODE,
                EXCLUDEBASEDONTOTALGIVINGINPASTYEAR = @EXCLUDEBASEDONTOTALGIVINGINPASTYEAR,
                TOTALREVENUEAMOUNTINPASTYEAR = @TOTALREVENUEAMOUNTINPASTYEAR,
                CHANGEDBYID = @CHANGEAGENTID,
                DATECHANGED = @CURRENTDATE
            where ID = @ID;

            exec dbo.USP_COMMUNICATIONEXCLUSION_GETCOMMUNICATIONTYPES_UPDATEFROMXML
                @ID,
                @COMMUNICATIONTYPES,
                @CHANGEAGENTID,
                @CURRENTDATE;                
        end

end