USP_DATAFORMTEMPLATE_LOAD_UPDATEMULTIPLECONSTITUENTSFROMMFO_BATCHROW

The load procedure used by the edit dataform template "Update Constituents From MatchFinder Online Batch Row Edit Form"

Parameters

Parameter	Parameter Type	Mode	Description
@ID	uniqueidentifier	IN	The input ID parameter used to load the fields defined on the form.
@DATALOADED	bit	INOUT	Output parameter indicating whether or not data was actually loaded.
@ORGANIZATIONID	uniqueidentifier	INOUT	Linked constituent
@MATCHFINDERRECORDID	int	INOUT	MatchFinder record id
@MATCHFINDERSUMMARYFIELD	nvarchar(512)	INOUT	MatchFinder record
@UPDATEORGNAME	bit	INOUT
@SYSTEMORGNAME	nvarchar(100)	INOUT	System org name
@MFORGNAME	nvarchar(100)	INOUT	MF org name
@UPDATEALIAS	bit	INOUT
@SYSTEMALIAS	nvarchar(100)	INOUT	System alias
@MFALIAS	nvarchar(100)	INOUT	MF alias
@UPDATEINDUSTRY	bit	INOUT
@SYSTEMINDUSTRY	nvarchar(100)	INOUT	System industry
@MFINDUSTRY	nvarchar(100)	INOUT	MF industry
@UPDATEADDRESS	bit	INOUT
@SYSTEMADDRESS	nvarchar(300)	INOUT	System formatted address
@MFADDRESS	nvarchar(300)	INOUT	MF formatted address
@MFADDRESSBLOCK	nvarchar(150)	INOUT	MF address block
@MFCITY	nvarchar(50)	INOUT	MF city
@MFSTATE	nvarchar(100)	INOUT	MF state
@MFSTATEID	uniqueidentifier	INOUT	MF state ID
@MFCOUNTRY	nvarchar(100)	INOUT	MF country
@MFCOUNTRYID	uniqueidentifier	INOUT	MF country ID
@MFPOSTCODE	nvarchar(12)	INOUT	MF postcode
@UPDATEPHONE	bit	INOUT
@SYSTEMPHONE	nvarchar(100)	INOUT	System phone number
@MFPHONE	nvarchar(100)	INOUT	MF phone number
@UPDATEFAX	bit	INOUT
@SYSTEMFAX	nvarchar(100)	INOUT	System fax number
@MFFAX	nvarchar(100)	INOUT	MF fax number
@UPDATEWEBADDRESS	bit	INOUT
@SYSTEMWEBADDRESS	nvarchar(2047)	INOUT	System web address
@MFWEBADDRESS	nvarchar(2047)	INOUT	MF web address
@CREATENEWCONTACT	bit	INOUT
@UPDATECONTACT	bit	INOUT
@CONTACTLINKID	uniqueidentifier	INOUT
@UPDATECONTACTNAME	bit	INOUT
@SYSTEMCONTACTNAME	nvarchar(150)	INOUT
@MFCONTACTNAME	nvarchar(150)	INOUT
@MFCONTACTFIRSTNAME	nvarchar(150)	INOUT
@MFCONTACTLASTNAME	nvarchar(150)	INOUT
@UPDATECONTACTEMAIL	bit	INOUT
@SYSTEMCONTACTEMAIL	nvarchar(100)	INOUT
@MFCONTACTEMAIL	nvarchar(100)	INOUT
@MATCHINGGIFTCONDITIONEXISTS	bit	INOUT	Match condition exists
@UPDATEMATCHINGFACTOR	bit	INOUT
@SYSTEMMATCHINGFACTOR	decimal(5, 2)	INOUT	System matching factor
@MFMATCHINGFACTOR	decimal(5, 2)	INOUT	MF matching factor
@UPDATEMINMATCHPERGIFT	bit	INOUT
@SYSTEMMINMATCHPERGIFT	money	INOUT	System min match per gift
@MFMINMATCHPERGIFT	money	INOUT	MF min match per gift
@UPDATEMAXMATCHPERGIFT	bit	INOUT
@SYSTEMMAXMATCHPERGIFT	money	INOUT	System max match per gift
@MFMAXMATCHPERGIFT	money	INOUT	MF max match per gift
@UPDATEMAXMATCHANNUAL	bit	INOUT
@SYSTEMMAXMATCHANNUAL	money	INOUT	System max match annual
@MFMAXMATCHANNUAL	money	INOUT	MF max match per year
@UPDATEMAXMATCHTOTAL	bit	INOUT
@SYSTEMMAXMATCHTOTAL	money	INOUT	System max match total
@MFMAXMATCHTOTAL	money	INOUT	MF max match total
@UPDATEMATCHNOTES	bit	INOUT
@SYSTEMMATCHNOTES	nvarchar(max)	INOUT	SYstem match notes
@MFMATCHNOTES	nvarchar(max)	INOUT	MF match notes
@SEQUENCE	int	INOUT	Sequence
@TSLONG	bigint	INOUT	Output parameter indicating the TSLONG value of the record being edited. This is used to manage multi-user concurrency issues when multiple users access the same record.

Definition

Copy


                    CREATE procedure dbo.USP_DATAFORMTEMPLATE_LOAD_UPDATEMULTIPLECONSTITUENTSFROMMFO_BATCHROW
                    (
                        @ID uniqueidentifier,
                        @DATALOADED bit = 0 output,

                        @ORGANIZATIONID uniqueidentifier = null output,
                        @MATCHFINDERRECORDID int = null output,
                        @MATCHFINDERSUMMARYFIELD nvarchar(512) = null output,

                        @UPDATEORGNAME bit = null output,
                        @SYSTEMORGNAME nvarchar(100) = null output,
                        @MFORGNAME nvarchar(100) = null output,

                        @UPDATEALIAS bit = null output,
                        @SYSTEMALIAS nvarchar(100) = null output,
                        @MFALIAS nvarchar(100) = null output,

                        @UPDATEINDUSTRY bit = null output,
                        @SYSTEMINDUSTRY nvarchar(100) = null output,
                        @MFINDUSTRY nvarchar(100) = null output,

                        @UPDATEADDRESS bit = null output,
                        @SYSTEMADDRESS nvarchar(300) = null output,
                        @MFADDRESS nvarchar(300) = null output,
                        @MFADDRESSBLOCK nvarchar(150) = null output,
                        @MFCITY nvarchar(50) = null output,
                        @MFSTATE nvarchar(100) = null output,
                        @MFSTATEID uniqueidentifier = null output,
                        @MFCOUNTRY nvarchar(100) = null output,
                        @MFCOUNTRYID uniqueidentifier = null output,
                        @MFPOSTCODE nvarchar(12) = null output,

                        @UPDATEPHONE bit = null output,
                        @SYSTEMPHONE nvarchar(100) = null output,
                        @MFPHONE nvarchar(100) = null output,

                        @UPDATEFAX bit = null output,
                        @SYSTEMFAX nvarchar(100) = null output,
                        @MFFAX nvarchar(100) = null output,

                        @UPDATEWEBADDRESS bit = null output,
                        @SYSTEMWEBADDRESS nvarchar(2047) = null output,
                        @MFWEBADDRESS nvarchar(2047) = null output,

                        @CREATENEWCONTACT bit = null output,
                        @UPDATECONTACT bit = null output,
                        @CONTACTLINKID uniqueidentifier = null output,

                        @UPDATECONTACTNAME bit = null output,
                        @SYSTEMCONTACTNAME nvarchar(150) = null output,
                        @MFCONTACTNAME nvarchar(150) = null output,
                        @MFCONTACTFIRSTNAME nvarchar(150) = null output,
                        @MFCONTACTLASTNAME nvarchar(150) = null output,
                        @UPDATECONTACTEMAIL bit = null output,
                        @SYSTEMCONTACTEMAIL nvarchar(100) = null output,
                        @MFCONTACTEMAIL nvarchar(100) = null output,

                        @MATCHINGGIFTCONDITIONEXISTS bit = null output,

                        @UPDATEMATCHINGFACTOR bit = null output,
                        @SYSTEMMATCHINGFACTOR decimal(5,2) = null output,
                        @MFMATCHINGFACTOR decimal(5,2) = null output,

                        @UPDATEMINMATCHPERGIFT bit = null output,
                        @SYSTEMMINMATCHPERGIFT money = null output,
                        @MFMINMATCHPERGIFT money = null output,

                        @UPDATEMAXMATCHPERGIFT bit = null output,
                        @SYSTEMMAXMATCHPERGIFT money = null output,
                        @MFMAXMATCHPERGIFT money = null output,

                        @UPDATEMAXMATCHANNUAL bit = null output,
                        @SYSTEMMAXMATCHANNUAL money = null output,
                        @MFMAXMATCHANNUAL money = null output,

                        @UPDATEMAXMATCHTOTAL bit = null output,
                        @SYSTEMMAXMATCHTOTAL money = null output,
                        @MFMAXMATCHTOTAL money = null output,

                        @UPDATEMATCHNOTES bit = null output,
                        @SYSTEMMATCHNOTES nvarchar(max) = null output,
                        @MFMATCHNOTES nvarchar(max) = null output,
                        @SEQUENCE int = null output,
                        @TSLONG bigint = 0 output
                    )
                    as
                    set nocount on;

                    begin try
                        select
                            @DATALOADED = 1,
                            @ORGANIZATIONID = CONSTITUENTID,
                            @MATCHFINDERRECORDID = MATCHFINDERRECORDID,
                            @MATCHFINDERSUMMARYFIELD = MFORGNAME,
                            @UPDATEORGNAME = UPDATEORGNAME,
                            @MFORGNAME = MFORGNAME,
                            @UPDATEALIAS = UPDATEALIAS,
                            @MFALIAS = MFALIAS,
                            @UPDATEINDUSTRY = UPDATEINDUSTRY,
                            @MFINDUSTRY = MFINDUSTRY,
                            @UPDATEADDRESS = UPDATEADDRESS,
                            @MFADDRESS = dbo.UFN_MATCHFINDER_BUILDADDRESS(MFADDRESSBLOCK, MFCITY, MFSTATE, MFPOSTCODE, MFCOUNTRY),
                            @MFADDRESSBLOCK = MFADDRESSBLOCK,
                            @MFCITY = MFCITY,
                            @MFSTATE = MFSTATE,
                            @MFSTATEID = MFSTATEID,
                            @MFCOUNTRY = MFCOUNTRY,
                            @MFCOUNTRYID = MFCOUNTRYID,
                            @MFPOSTCODE = MFPOSTCODE,
                            @UPDATEPHONE = UPDATEPHONE,
                            @MFPHONE = MFPHONE,
                            @UPDATEFAX = UPDATEFAX,
                            @MFFAX = MFFAX,
                            @UPDATEWEBADDRESS = UPDATEWEBADDRESS,
                            @MFWEBADDRESS = MFWEBADDRESS,
                            @UPDATEMATCHINGFACTOR = UPDATEMATCHINGFACTOR,
                            @MFMATCHINGFACTOR = MFMATCHINGFACTOR,
                            @UPDATEMINMATCHPERGIFT = UPDATEMINMATCHPERGIFT,
                            @MFMINMATCHPERGIFT = MFMINMATCHPERGIFT,
                            @UPDATEMAXMATCHPERGIFT = UPDATEMAXMATCHPERGIFT,
                            @MFMAXMATCHPERGIFT = MFMAXMATCHPERGIFT,
                            @UPDATEMAXMATCHANNUAL = UPDATEMAXMATCHANNUAL,
                            @MFMAXMATCHANNUAL = MFMAXMATCHANNUAL,
                            @UPDATEMAXMATCHTOTAL = UPDATEMAXMATCHTOTAL,
                            @MFMAXMATCHTOTAL = MFMAXMATCHTOTAL,
                            @UPDATEMATCHNOTES = UPDATEMATCHNOTES,
                            @MFMATCHNOTES = MFMATCHNOTES,
                            @CREATENEWCONTACT = CREATENEWCONTACT,
                            @UPDATECONTACT = UPDATECONTACT,
                            @CONTACTLINKID = CONTACTLINKID,
                            @UPDATECONTACTNAME = UPDATECONTACTNAME,
                            @UPDATECONTACTEMAIL = UPDATECONTACTEMAIL,
                            @MFCONTACTNAME = MFCONTACTFULLNAME,
                            @MFCONTACTFIRSTNAME = MFCONTACTFIRSTNAME,
                            @MFCONTACTLASTNAME = MFCONTACTLASTNAME,
                            @MFCONTACTEMAIL = MFCONTACTEMAIL,
                            @SEQUENCE = SEQUENCE,
                            @TSLONG = TSLONG
                        from dbo.UPDATEMULTIPLECONSTITUENTSFROMMFOBATCH
                        where ID = @ID;


                        if not @ORGANIZATIONID is null
                        begin
                            declare @SYSTEMDATA xml;
                            exec dbo.USP_MATCHFINDER_GETSYSTEMDATA_FORCONSTITUENT @CONSTITUENTID = @ORGANIZATIONID, @XMLOUTPUT = @SYSTEMDATA output, @RETURNDATA = 0;

                            if not @SYSTEMDATA is null
                                select 
                                    @SYSTEMORGNAME = coalesce(T.c.value('ORGNAME[1]', 'nvarchar(100)'), N''),
                                    @SYSTEMWEBADDRESS = coalesce(T.c.value('WEBADDRESS[1]', 'nvarchar(2047)'), N''),
                                    @SYSTEMADDRESS = coalesce(T.c.value('ADDRESS[1]', 'nvarchar(150)'), N''),
                                    @SYSTEMPHONE = coalesce(T.c.value('PHONE[1]', 'nvarchar(100)'), N''),
                                    @SYSTEMFAX = coalesce(T.c.value('FAX[1]', 'nvarchar(100)'), N''),
                                    --T.c.value('CONTACTID[1]', 'uniqueidentifier'), 
                                    --@SYSTEMCONTACTNAME = coalesce(T.c.value('CONTACTNAME[1]', 'nvarchar(150)'), N''),
                                    --@SYSTEMCONTACTEMAIL = coalesce(T.c.value('CONTACTEMAIL[1]', 'nvarchar(100)'), N''),
                                    @SYSTEMALIAS = coalesce(T.c.value('ALIAS[1]', 'nvarchar(100)'), N''),
                                    @SYSTEMINDUSTRY = coalesce(T.c.value('INDUSTRY[1]', 'nvarchar(100)'), N''),
                                    @MATCHINGGIFTCONDITIONEXISTS = case when T.c.value('MATCHINGGIFTCONDITIONID[1]', 'uniqueidentifier') is null then 0 else 1 end,
                                    @SYSTEMMATCHINGFACTOR = coalesce(T.c.value('MATCHINGFACTOR[1]', 'decimal(5,2)'), 0),
                                    @SYSTEMMINMATCHPERGIFT = coalesce(T.c.value('MINMATCHPERGIFT[1]', 'money'), 0),
                                    @SYSTEMMAXMATCHPERGIFT = coalesce(T.c.value('MAXMATCHPERGIFT[1]', 'money'), 0),
                                    @SYSTEMMAXMATCHANNUAL = coalesce(T.c.value('MAXMATCHANNUAL[1]', 'money'), 0),
                                    @SYSTEMMAXMATCHTOTAL = coalesce(T.c.value('MAXMATCHTOTAL[1]', 'money'), 0),
                                    @SYSTEMMATCHNOTES = coalesce(T.c.value('MATCHNOTES[1]', 'nvarchar(max)'), N'')
                                from @SYSTEMDATA.nodes('/SYSTEMDATA/ITEM') T(c)

                            if @CONTACTLINKID is null
                            begin
                                set @SYSTEMCONTACTNAME = N'';
                                set @SYSTEMCONTACTEMAIL = N'';
                            end
                            else
                                exec dbo.USP_DATAFORMTEMPLATE_VIEW_MATCHFINDERCONTACT @ID = @CONTACTLINKID, @DATALOADED = null, @NAME = @SYSTEMCONTACTNAME output, @EMAIL = @SYSTEMCONTACTEMAIL output;

                        end

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

                    return 0;