USP_ADVOCACYLOG_ADD_EDIT

Add Update AdvocacyLog table

Parameters

Parameter	Parameter Type	Mode
@ADVOCACYLOGID	int	IN
@SITECONTENTID	int	IN
@ACTIONITEMID	uniqueidentifier	IN
@CONSTITUENTID	uniqueidentifier	IN
@MESSAGETEXT	nvarchar(max)	IN
@MESSAGEHEADER	nvarchar(255)	IN
@SUBJECTLINE	nvarchar(255)	IN
@LEGISID	nvarchar(10)	IN
@TARGETNAME	varchar(500)	IN
@FEDERALDISTRICTCODE	varchar(4)	IN
@UPPERSTATEDISTRICTCODE	varchar(5)	IN
@LOWERSTATEDISTRICTCODE	varchar(5)	IN
@DISTRICTNAME	varchar(100)	IN
@DISTRICT	varchar(13)	IN
@ADDRESSLINE1	varchar(100)	IN
@ADDRESSLINE2	varchar(100)	IN
@CITY	varchar(50)	IN
@STATE	varchar(100)	IN
@COUNTRY	varchar(5)	IN
@ZIP	varchar(12)	IN
@ISMESSAGETEXTCHANGED	bit	IN
@ISSUBJECTCHANGED	bit	IN
@FROMADDRESS	nvarchar(255)	IN
@TOADDRESS	nvarchar(2000)	IN
@SALUTATION	nvarchar(100)	IN
@MESSAGESIGNATURE	nvarchar(255)	IN
@TESTFLAG	bit	IN
@SENDTYPE	varchar(10)	IN
@STATUSCODE	tinyint	IN
@SERVERDATEUPDATED	datetime	IN
@ORIGINALADVOCACYLOGID	int	IN

Definition

Copy



        CREATE procedure dbo.USP_ADVOCACYLOG_ADD_EDIT
        (
            @ADVOCACYLOGID int,
            @SITECONTENTID int,
            @ACTIONITEMID uniqueidentifier,
            @CONSTITUENTID uniqueidentifier,
            @MESSAGETEXT nvarchar(MAX) = '',
            @MESSAGEHEADER nvarchar(255) = '',
            @SUBJECTLINE nvarchar(255) = '',
            @LEGISID nvarchar(10),
            @TARGETNAME varchar(500) = null,
            @FEDERALDISTRICTCODE varchar(4) = null,
            @UPPERSTATEDISTRICTCODE varchar(5) = null,
            @LOWERSTATEDISTRICTCODE varchar(5) = null,
            @DISTRICTNAME varchar(100) = null,
            @DISTRICT varchar(13) = null,
            @ADDRESSLINE1 varchar(100) = null,
            @ADDRESSLINE2 varchar(100) = null,
            @CITY varchar(50) = null,
            @STATE varchar(100) = null,
            @COUNTRY varchar(5) = null,
            @ZIP varchar(12) = null,
            @ISMESSAGETEXTCHANGED bit = 0,
            @ISSUBJECTCHANGED bit = 0,
            @FROMADDRESS nvarchar(255) = '',
            @TOADDRESS nvarchar(2000) = '',
            @SALUTATION nvarchar(100) = '',
            @MESSAGESIGNATURE nvarchar(255) = '',
            @TESTFLAG bit = 0,
            @SENDTYPE varchar(10) = '',
        --    @TYPECODE tinyint = 0, -- 0:EMAIL, 1:FAX, 2:WEBFORM

            @STATUSCODE tinyint=0,
            @SERVERDATEUPDATED datetime = null,
            @ORIGINALADVOCACYLOGID int = 0

        )
        as
        begin

            DECLARE @TYPECODE tinyint

            IF UPPER(@SENDTYPE) = 'FAX'
                SET @TYPECODE = 1
            ELSE IF UPPER(@SENDTYPE) = 'WEBFORM'
                SET @TYPECODE = 2
            ELSE SET @TYPECODE = 0

            IF NOT EXISTS (SELECT 1 FROM dbo.ADVOCACYLOG WITH (NOLOCK) WHERE ADVOCACYLOGID = @ADVOCACYLOGID)
            BEGIN
                EXEC USP_DATAFORMTEMPLATE_ADD_ADVOCACY_LOG @ID = NULL, @CHANGEAGENTID = NULL, @ADVOCACYLOGID = @ADVOCACYLOGID, @SITECONTENTID = @SITECONTENTID,
                @ACTIONITEMID = @ACTIONITEMID, @CONSTITUENTID = @CONSTITUENTID, @MESSAGETEXT = @MESSAGETEXT, @MESSAGEHEADER = @MESSAGEHEADER, @SUBJECTLINE = @SUBJECTLINE,
                @LEGISID = @LEGISID, @TARGETNAME = @TARGETNAME, @FEDERALDISTRICTCODE = @FEDERALDISTRICTCODE, @UPPERSTATEDISTRICTCODE = @UPPERSTATEDISTRICTCODE, 
                @LOWERSTATEDISTRICTCODE = @LOWERSTATEDISTRICTCODE, @DISTRICTNAME = @DISTRICTNAME, @DISTRICT = @DISTRICT, @ADDRESSLINE1 = @ADDRESSLINE1, @ADDRESSLINE2 = @ADDRESSLINE2,
                @CITY = @CITY , @STATE = @STATE, @COUNTRY = @COUNTRY, @ZIP = @ZIP, @ISMESSAGETEXTCHANGED = @ISMESSAGETEXTCHANGED, @ISSUBJECTCHANGED = @ISSUBJECTCHANGED, 
                @FROMADDRESS = @FROMADDRESS, @TOADDRESS = @TOADDRESS, @SALUTATION = @SALUTATION, @MESSAGESIGNATURE = @MESSAGESIGNATURE, @TESTFLAG = @TESTFLAG, @TYPECODE = @TYPECODE,
                @STATUSCODE = @STATUSCODE, @ORIGINALADVOCACYLOGID = @ORIGINALADVOCACYLOGID

                UPDATE dbo.ADVOCACYLOG
                SET SERVERDATEUPDATED = ISNULL(@SERVERDATEUPDATED,getdate())
                WHERE ADVOCACYLOGID = @ADVOCACYLOGID
            END

        return 0;
        end