USP_DATAFORMTEMPLATE_EDIT_RESERVATIONCONTACTINFORMATION

Parameters

Parameter	Parameter Type	Mode
@ID	uniqueidentifier	IN
@CHANGEAGENTID	uniqueidentifier	IN
@CONTACTCONSTITUENTID	uniqueidentifier	IN
@ADDRESSID	uniqueidentifier	IN
@PHONEID	uniqueidentifier	IN
@EMAILADDRESSID	uniqueidentifier	IN
@ISORGANIZATION	bit	IN
@CONTACTRELATIONSHIPID	uniqueidentifier	IN
@RELATIONSHIPTYPECODEID	uniqueidentifier	IN
@RECIPROCALTYPECODEID	uniqueidentifier	IN
@RELATIONSHIPSTARTDATE	datetime	IN
@RELATIONSHIPENDDATE	datetime	IN
@ISCONTACT	bit	IN
@ISPRIMARYCONTACT	bit	IN
@CONTACTTYPECODEID	uniqueidentifier	IN
@POSITION	nvarchar(50)	IN
@ISPRIMARYBUSINESS	bit	IN
@ISMATCHINGGIFTRELATIONSHIP	bit	IN
@ADDADDRESS	bit	IN
@EDITADDRESS	bit	IN
@ADDRESSTYPECODEID	uniqueidentifier	IN
@PRIMARY	bit	IN
@DONOTMAIL	bit	IN
@STARTDATE	UDT_MONTHDAY	IN
@ENDDATE	UDT_MONTHDAY	IN
@COUNTRYID	uniqueidentifier	IN
@STATEID	uniqueidentifier	IN
@ADDRESSBLOCK	nvarchar(150)	IN
@CITY	nvarchar(50)	IN
@POSTCODE	nvarchar(12)	IN
@CART	nvarchar(10)	IN
@DPC	nvarchar(8)	IN
@LOT	nvarchar(5)	IN
@UPDATEMATCHINGSPOUSEADDRESSES	bit	IN
@OMITFROMVALIDATION	bit	IN
@COUNTYCODEID	uniqueidentifier	IN
@CONGRESSIONALDISTRICTCODEID	uniqueidentifier	IN
@STATEHOUSEDISTRICTCODEID	uniqueidentifier	IN
@STATESENATEDISTRICTCODEID	uniqueidentifier	IN
@LOCALPRECINCTCODEID	uniqueidentifier	IN
@INFOSOURCECODEID	uniqueidentifier	IN
@REGIONCODEID	uniqueidentifier	IN
@LASTVALIDATIONATTEMPTDATE	datetime	IN
@VALIDATIONMESSAGE	nvarchar(200)	IN
@CERTIFICATIONDATA	int	IN
@UPDATECONTACTS	bit	IN
@DONOTMAILREASONCODEID	uniqueidentifier	IN
@INFOSOURCECOMMENTS	nvarchar(256)	IN
@COMMENTS	nvarchar(max)	IN
@ADDPHONE	bit	IN
@EDITPHONE	bit	IN
@PHONETYPECODEID	uniqueidentifier	IN
@NUMBER	nvarchar(100)	IN
@PHONECOUNTRYID	uniqueidentifier	IN
@PHONESTARTTIME	UDT_HOURMINUTE	IN
@PHONEENDTIME	UDT_HOURMINUTE	IN
@PHONESTARTDATE	date	IN
@PHONEENDDATE	date	IN
@PHONEPRIMARY	bit	IN
@DONOTCALL	bit	IN
@DONOTCALLREASONCODEID	uniqueidentifier	IN
@PHONEISCONFIDENTIAL	bit	IN
@PHONEINFOSOURCECODEID	uniqueidentifier	IN
@PHONEINFOSOURCECOMMENTS	nvarchar(256)	IN
@ADDEMAILADDRESS	bit	IN
@EDITEMAILADDRESS	bit	IN
@EMAILADDRESSTYPECODEID	uniqueidentifier	IN
@EMAILADDRESS	UDT_EMAILADDRESS	IN
@EMAILADDRESSPRIMARY	bit	IN
@DONOTEMAIL	bit	IN
@EMAILADDRESSINFOSOURCECODEID	uniqueidentifier	IN
@EMAILADDRESSINFOSOURCECOMMENTS	nvarchar(256)	IN
@PRIMARYSOFTCREDITRELATIONSHIPEXISTS	bit	IN
@PRIMARYSOFTCREDITMATCHFACTOR	decimal(5, 2)	IN
@RECIPROCALSOFTCREDITRELATIONSHIPEXISTS	bit	IN
@RECIPROCALSOFTCREDITMATCHFACTOR	decimal(5, 2)	IN
@PRIMARYRECOGNITIONTYPECODEID	uniqueidentifier	IN
@RECIPROCALRECOGNITIONTYPECODEID	uniqueidentifier	IN

Definition

Copy



CREATE procedure dbo.USP_DATAFORMTEMPLATE_EDIT_RESERVATIONCONTACTINFORMATION (
    @ID uniqueidentifier,
    @CHANGEAGENTID uniqueidentifier = null,
    @CONTACTCONSTITUENTID uniqueidentifier,
    @ADDRESSID uniqueidentifier,
    @PHONEID uniqueidentifier,
    @EMAILADDRESSID uniqueidentifier,

    @ISORGANIZATION bit,
    @CONTACTRELATIONSHIPID uniqueidentifier,

    -- Relationship fields

    @RELATIONSHIPTYPECODEID uniqueidentifier,
    @RECIPROCALTYPECODEID uniqueidentifier,
    @RELATIONSHIPSTARTDATE datetime,
    @RELATIONSHIPENDDATE datetime,
    @ISCONTACT bit,
    @ISPRIMARYCONTACT bit,
    @CONTACTTYPECODEID uniqueidentifier,
    @POSITION nvarchar(50),
    @ISPRIMARYBUSINESS bit,
    @ISMATCHINGGIFTRELATIONSHIP bit,
    @ADDADDRESS bit,
    @EDITADDRESS bit,
    @ADDRESSTYPECODEID uniqueidentifier,
    @PRIMARY bit,
    @DONOTMAIL bit,
    @STARTDATE dbo.UDT_MONTHDAY,
    @ENDDATE dbo.UDT_MONTHDAY,
    @COUNTRYID uniqueidentifier,
    @STATEID uniqueidentifier,
    @ADDRESSBLOCK nvarchar(150),
    @CITY nvarchar(50),
    @POSTCODE nvarchar(12),
    @CART nvarchar(10),
    @DPC nvarchar(8),
    @LOT nvarchar(5),
    @UPDATEMATCHINGSPOUSEADDRESSES bit,
    @OMITFROMVALIDATION bit,
    @COUNTYCODEID uniqueidentifier,
    @CONGRESSIONALDISTRICTCODEID uniqueidentifier,
    @STATEHOUSEDISTRICTCODEID uniqueidentifier,
    @STATESENATEDISTRICTCODEID uniqueidentifier,
    @LOCALPRECINCTCODEID uniqueidentifier,
    @INFOSOURCECODEID uniqueidentifier,
    @REGIONCODEID uniqueidentifier,
    @LASTVALIDATIONATTEMPTDATE datetime,
    @VALIDATIONMESSAGE nvarchar(200),
    @CERTIFICATIONDATA integer,
    @UPDATECONTACTS bit,
    @DONOTMAILREASONCODEID uniqueidentifier,
    @INFOSOURCECOMMENTS nvarchar(256),
    @COMMENTS nvarchar(max),
    @ADDPHONE bit,
    @EDITPHONE bit,
    @PHONETYPECODEID uniqueidentifier,
    @NUMBER nvarchar(100),
    @PHONECOUNTRYID uniqueidentifier,
    @PHONESTARTTIME dbo.UDT_HOURMINUTE,
    @PHONEENDTIME dbo.UDT_HOURMINUTE,
    @PHONESTARTDATE date,
    @PHONEENDDATE date,
    @PHONEPRIMARY bit,
    @DONOTCALL bit,
    @DONOTCALLREASONCODEID uniqueidentifier,
    @PHONEISCONFIDENTIAL bit,
    @PHONEINFOSOURCECODEID uniqueidentifier,
    @PHONEINFOSOURCECOMMENTS nvarchar(256),
    @ADDEMAILADDRESS bit,
    @EDITEMAILADDRESS bit,
    @EMAILADDRESSTYPECODEID uniqueidentifier,
    @EMAILADDRESS dbo.UDT_EMAILADDRESS,
    @EMAILADDRESSPRIMARY bit,
    @DONOTEMAIL bit,
    @EMAILADDRESSINFOSOURCECODEID uniqueidentifier,
    @EMAILADDRESSINFOSOURCECOMMENTS nvarchar(256),
    @PRIMARYSOFTCREDITRELATIONSHIPEXISTS bit,
    @PRIMARYSOFTCREDITMATCHFACTOR decimal(5,2),
    @RECIPROCALSOFTCREDITRELATIONSHIPEXISTS bit,
    @RECIPROCALSOFTCREDITMATCHFACTOR decimal(5,2),
    @PRIMARYRECOGNITIONTYPECODEID uniqueidentifier,
    @RECIPROCALRECOGNITIONTYPECODEID uniqueidentifier
)
as
    set nocount on;

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

    declare @CURRENTDATE datetime = getdate();

    begin try
        if @CONTACTCONSTITUENTID is null begin
            raiserror('BBERR_CONTACTCONSTITUENTIDREQUIRED', 13, 1);
        end

        if @ADDRESSID is not null and not exists (select * from dbo.ADDRESS where ID = @ADDRESSID and CONSTITUENTID = @CONTACTCONSTITUENTID) begin
            raiserror('BBERR_INVAIDADDRESS', 13, 1);
        end

        if @PHONEID is not null and not exists (select * from dbo.PHONE where ID = @PHONEID and CONSTITUENTID = @CONTACTCONSTITUENTID) begin
            raiserror('BBERR_INVAIDPHONE', 13, 1);
        end

        if @EMAILADDRESSID is not null and not exists (select * from dbo.EMAILADDRESS where ID = @EMAILADDRESSID and CONSTITUENTID = @CONTACTCONSTITUENTID) begin
            raiserror('BBERR_INVAIDEMAILADDRESS', 13, 1);
        end

        if @ISORGANIZATION = 1 and @CONTACTRELATIONSHIPID is not null
            begin
                exec dbo.USP_DATAFORMTEMPLATE_EDIT_RELATIONSHIP_ORGTOIND_7
                    @ID = @CONTACTRELATIONSHIPID,
                    @CHANGEAGENTID = @CHANGEAGENTID,
                    @RELATIONSHIPTYPECODEID = @RELATIONSHIPTYPECODEID,
                    @RECIPROCALTYPECODEID = @RECIPROCALTYPECODEID,
                    @RELATIONSHIPSTARTDATE = @RELATIONSHIPSTARTDATE,
                    @RELATIONSHIPENDDATE = @RELATIONSHIPENDDATE,
                    @ISCONTACT = @ISCONTACT,
                    @ISPRIMARYCONTACT = @ISPRIMARYCONTACT,
                    @CONTACTTYPECODEID = @CONTACTTYPECODEID,
                    @POSITION = @POSITION,
                    @ISPRIMARYBUSINESS = @ISPRIMARYBUSINESS,
                    @ISMATCHINGGIFTRELATIONSHIP = @ISMATCHINGGIFTRELATIONSHIP,
                    @RELATIONADDRESSID = @ADDRESSID,
                    @ADDADDRESS = @ADDADDRESS,
                    @EDITADDRESS = @EDITADDRESS,
                    @ADDRESSTYPECODEID = @ADDRESSTYPECODEID,
                    @PRIMARY = @PRIMARY,
                    @DONOTMAIL = @DONOTMAIL,
                    @STARTDATE = @STARTDATE,
                    @ENDDATE = @ENDDATE,
                    @COUNTRYID = @COUNTRYID,
                    @STATEID = @STATEID,
                    @ADDRESSBLOCK = @ADDRESSBLOCK,
                    @CITY = @CITY,
                    @POSTCODE = @POSTCODE,
                    @CART = @CART,
                    @DPC = @DPC,
                    @LOT = @LOT,
                    @UPDATEMATCHINGSPOUSEADDRESSES = @UPDATEMATCHINGSPOUSEADDRESSES,
                    @OMITFROMVALIDATION = @OMITFROMVALIDATION,
                    @COUNTYCODEID = @COUNTYCODEID,
                    @CONGRESSIONALDISTRICTCODEID = @CONGRESSIONALDISTRICTCODEID,
                    @STATEHOUSEDISTRICTCODEID = @STATEHOUSEDISTRICTCODEID,
                    @STATESENATEDISTRICTCODEID = @STATESENATEDISTRICTCODEID,
                    @LOCALPRECINCTCODEID = @LOCALPRECINCTCODEID,
                    @INFOSOURCECODEID = @INFOSOURCECODEID,
                    @REGIONCODEID = @REGIONCODEID,
                    @LASTVALIDATIONATTEMPTDATE = @LASTVALIDATIONATTEMPTDATE,
                    @VALIDATIONMESSAGE = @VALIDATIONMESSAGE,
                    @CERTIFICATIONDATA = @CERTIFICATIONDATA,
                    @UPDATECONTACTS = @UPDATECONTACTS,
                    @DONOTMAILREASONCODEID = @DONOTMAILREASONCODEID,
                    @INFOSOURCECOMMENTS = @INFOSOURCECOMMENTS,
                    @COMMENTS = @COMMENTS,
                    @RELATIONPHONEID = @PHONEID,
                    @ADDPHONE = @ADDPHONE,
                    @EDITPHONE = @EDITPHONE,
                    @PHONETYPECODEID = @PHONETYPECODEID,
                    @NUMBER = @NUMBER,
                    @PHONECOUNTRYID = @PHONECOUNTRYID,
                    @PHONESTARTTIME = @PHONESTARTTIME,
                    @PHONEENDTIME = @PHONEENDTIME,
                    @PHONESTARTDATE = @PHONESTARTDATE,
                    @PHONEENDDATE = @PHONEENDDATE,
                    @PHONEPRIMARY = @PHONEPRIMARY,
                    @DONOTCALL = @DONOTCALL,
                    @DONOTCALLREASONCODEID = @DONOTCALLREASONCODEID,
                    @PHONEISCONFIDENTIAL = @PHONEISCONFIDENTIAL,
                    @PHONEINFOSOURCECODEID = @PHONEINFOSOURCECODEID,
                    @PHONEINFOSOURCECOMMENTS = @PHONEINFOSOURCECOMMENTS,
                    @RELATIONEMAILADDRESSID = @EMAILADDRESSID,
                    @ADDEMAILADDRESS = @ADDEMAILADDRESS,
                    @EDITEMAILADDRESS = @EDITEMAILADDRESS,
                    @EMAILADDRESSTYPECODEID = @EMAILADDRESSTYPECODEID,
                    @EMAILADDRESS = @EMAILADDRESS,
                    @EMAILADDRESSPRIMARY = @EMAILADDRESSPRIMARY,
                    @DONOTEMAIL = @DONOTEMAIL,
                    @EMAILADDRESSINFOSOURCECODEID = @EMAILADDRESSINFOSOURCECODEID,
                    @EMAILADDRESSINFOSOURCECOMMENTS = @EMAILADDRESSINFOSOURCECOMMENTS,
                    @PRIMARYSOFTCREDITRELATIONSHIPEXISTS = @PRIMARYSOFTCREDITRELATIONSHIPEXISTS,
                    @PRIMARYSOFTCREDITMATCHFACTOR = @PRIMARYSOFTCREDITMATCHFACTOR,
                    @RECIPROCALSOFTCREDITRELATIONSHIPEXISTS = @RECIPROCALSOFTCREDITRELATIONSHIPEXISTS,
                    @RECIPROCALSOFTCREDITMATCHFACTOR = @RECIPROCALSOFTCREDITMATCHFACTOR,
                    @PRIMARYRECOGNITIONTYPECODEID = @PRIMARYRECOGNITIONTYPECODEID,
                    @RECIPROCALRECOGNITIONTYPECODEID = @RECIPROCALRECOGNITIONTYPECODEID;    

                set @ADDRESSID = null;
                set @PHONEID = null;
                set @EMAILADDRESSID = null;

            end

        update dbo.SALESORDER set
            CONTACTRELATIONSHIPID = @CONTACTRELATIONSHIPID,
            RECIPIENTID = @CONTACTCONSTITUENTID,
            ADDRESSID = @ADDRESSID,
            PHONEID = @PHONEID,
            EMAILADDRESSID = @EMAILADDRESSID,
            CHANGEDBYID = @CHANGEAGENTID,
            DATECHANGED = @CURRENTDATE
        where
            ID = @ID;
    end try

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

    return 0;