USP_DATAFORMTEMPLATE_EDITLOAD_CONSTITUENTWINDOW_2

The load procedure used by the edit dataform template "Revenue Batch Constituent Window Edit Form 2"

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.
@LASTNAME	nvarchar(100)	INOUT	Last name
@FIRSTNAME	nvarchar(50)	INOUT	First name
@MIDDLENAME	nvarchar(50)	INOUT	Middle name
@MAIDENNAME	nvarchar(100)	INOUT	Maiden name
@NICKNAME	nvarchar(50)	INOUT	Nickname
@TITLECODEID	uniqueidentifier	INOUT	Title
@SUFFIXCODEID	uniqueidentifier	INOUT	Suffix
@GENDERCODE	tinyint	INOUT	Gender
@BIRTHDATE	UDT_FUZZYDATE	INOUT	Birth date
@GIVESANONYMOUSLY	bit	INOUT	Gives anonymously
@ADDRESS_STATEID	uniqueidentifier	INOUT	State
@ADDRESS_ADDRESSBLOCK	nvarchar(150)	INOUT	Address
@ADDRESS_CITY	nvarchar(50)	INOUT	City
@ADDRESS_POSTCODE	nvarchar(12)	INOUT	ZIP
@PHONE_PHONETYPECODEID	uniqueidentifier	INOUT	Phone type
@PHONE_NUMBER	nvarchar(100)	INOUT	Phone number
@SPOUSENAME	nvarchar(154)	INOUT
@SPOUSEHASMATCHINGCONTACT	bit	INOUT
@UPDATEMATCHINGSPOUSECONTACT	bit	INOUT	Update matching contact information for spouse
@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.
@ADDRESS_COUNTRYID	uniqueidentifier	INOUT	Country
@ZIPLOOKUPCOUNTRIES	xml	INOUT

Definition

Copy



                CREATE procedure dbo.USP_DATAFORMTEMPLATE_EDITLOAD_CONSTITUENTWINDOW_2(
                    @ID uniqueidentifier,
                    @DATALOADED bit = 0 output,
                    @LASTNAME nvarchar(100) = null output,
                    @FIRSTNAME nvarchar(50) = null output,
                    @MIDDLENAME nvarchar(50) = null output,
                    @MAIDENNAME nvarchar(100) = null output,
                    @NICKNAME nvarchar(50) = null output,
                    @TITLECODEID uniqueidentifier = null output,
                    @SUFFIXCODEID uniqueidentifier = null output,
                    @GENDERCODE tinyint = null output,
                    @BIRTHDATE dbo.UDT_FUZZYDATE = null output,
                    @GIVESANONYMOUSLY bit = null output,                
                    @ADDRESS_STATEID uniqueidentifier = null output,
                    @ADDRESS_ADDRESSBLOCK nvarchar(150) = null output,
                    @ADDRESS_CITY nvarchar(50) = null output,
                    @ADDRESS_POSTCODE nvarchar(12) = null output,
                    @PHONE_PHONETYPECODEID uniqueidentifier = null output,
                    @PHONE_NUMBER nvarchar(100) = null output,
                    @SPOUSENAME nvarchar(154) = null output,
                    @SPOUSEHASMATCHINGCONTACT bit = null output,
                    @UPDATEMATCHINGSPOUSECONTACT bit = null output,
                    @TSLONG bigint = 0 output,
                    @ADDRESS_COUNTRYID uniqueidentifier = null output,
                    @ZIPLOOKUPCOUNTRIES xml = null output
                    )
                as
                    set nocount on;

                    declare @SPOUSEID uniqueidentifier;

                    set @DATALOADED = 0;
                    set @TSLONG = 0;
                    set @SPOUSEHASMATCHINGCONTACT = 0;
                    set @UPDATEMATCHINGSPOUSECONTACT = 0;

                    if exists(select object_id from sys.objects where type = 'U' and name = 'RELATIONSHIP') begin
                        select
                            @DATALOADED = 1,
                            @LASTNAME = [CONSTITUENT].[KEYNAME],
                            @FIRSTNAME = [CONSTITUENT].[FIRSTNAME],
                            @MIDDLENAME = [CONSTITUENT].[MIDDLENAME],
                            @MAIDENNAME = [CONSTITUENT].[MAIDENNAME],
                            @NICKNAME = [CONSTITUENT].[NICKNAME],
                            @TITLECODEID = [CONSTITUENT].[TITLECODEID],
                            @SUFFIXCODEID = [CONSTITUENT].[SUFFIXCODEID],
                            @GENDERCODE = [CONSTITUENT].[GENDERCODE],
                            @BIRTHDATE = [CONSTITUENT].[BIRTHDATE],
                            @GIVESANONYMOUSLY = [CONSTITUENT].[GIVESANONYMOUSLY],
                            @ADDRESS_COUNTRYID = [ADDRESS].[COUNTRYID],
                            @ADDRESS_STATEID = [ADDRESS].[STATEID],
                            @ADDRESS_ADDRESSBLOCK = [ADDRESS].[ADDRESSBLOCK],
                            @ADDRESS_CITY = [ADDRESS].[CITY],
                            @ADDRESS_POSTCODE = [ADDRESS].[POSTCODE],
                            @PHONE_PHONETYPECODEID = [PHONE].[PHONETYPECODEID],
                            @PHONE_NUMBER = [PHONE].[NUMBER],
                            @SPOUSEID = [SPOUSE].[ID],
                            @SPOUSENAME = [SPOUSE].[NAME],
                            @TSLONG = [CONSTITUENT].[TSLONG]
                        from dbo.CONSTITUENT
                            left outer join dbo.ADDRESS on ADDRESS.CONSTITUENTID=CONSTITUENT.ID and ADDRESS.ISPRIMARY = 1
                            left outer join dbo.PHONE on PHONE.CONSTITUENTID=CONSTITUENT.ID and PHONE.ISPRIMARY = 1
                            left join dbo.RELATIONSHIP on RELATIONSHIP.RELATIONSHIPCONSTITUENTID = CONSTITUENT.ID and RELATIONSHIP.ISSPOUSE = 1
                            left join dbo.CONSTITUENT as SPOUSE on SPOUSE.ID = RELATIONSHIP.RECIPROCALCONSTITUENTID
                        where
                            CONSTITUENT.ID = @ID;

                        if exists (
                                select ADDRESS.ID
                                from dbo.ADDRESS
                                where
                                    ADDRESS.CONSTITUENTID = @SPOUSEID and
                                    ADDRESS.COUNTRYID = @ADDRESS_COUNTRYID and
                                    ADDRESS.ADDRESSBLOCK = @ADDRESS_ADDRESSBLOCK and
                                    ADDRESS.CITY = @ADDRESS_CITY and
                                    (ADDRESS.STATEID = @ADDRESS_STATEID or (ADDRESS.STATEID is null and @ADDRESS_STATEID is null)) and
                                    ADDRESS.POSTCODE = @ADDRESS_POSTCODE
                                ) or
                            exists (
                                select PHONE.ID
                                from dbo.PHONE
                                where
                                    PHONE.CONSTITUENTID = @SPOUSEID and
                                    PHONE.NUMBER = @PHONE_NUMBER
                                )begin
                                    set @SPOUSEHASMATCHINGCONTACT = 1;
                                    set @UPDATEMATCHINGSPOUSECONTACT = 1;
                            end
                    end
                    else begin
                        select
                            @DATALOADED = 1,
                            @LASTNAME = [CONSTITUENT].[KEYNAME],
                            @FIRSTNAME = [CONSTITUENT].[FIRSTNAME],
                            @MIDDLENAME = [CONSTITUENT].[MIDDLENAME],
                            @MAIDENNAME = [CONSTITUENT].[MAIDENNAME],
                            @NICKNAME = [CONSTITUENT].[NICKNAME],
                            @TITLECODEID = [CONSTITUENT].[TITLECODEID],
                            @SUFFIXCODEID = [CONSTITUENT].[SUFFIXCODEID],
                            @GENDERCODE = [CONSTITUENT].[GENDERCODE],
                            @BIRTHDATE = [CONSTITUENT].[BIRTHDATE],
                            @GIVESANONYMOUSLY = [CONSTITUENT].[GIVESANONYMOUSLY],    
                            @ADDRESS_COUNTRYID = [ADDRESS].[COUNTRYID],
                            @ADDRESS_STATEID = [ADDRESS].[STATEID],
                            @ADDRESS_ADDRESSBLOCK = [ADDRESS].[ADDRESSBLOCK],
                            @ADDRESS_CITY = [ADDRESS].[CITY],
                            @ADDRESS_POSTCODE = [ADDRESS].[POSTCODE],
                            @PHONE_PHONETYPECODEID = [PHONE].[PHONETYPECODEID],
                            @PHONE_NUMBER = [PHONE].[NUMBER],
                            @TSLONG = [CONSTITUENT].[TSLONG]
                        from dbo.CONSTITUENT
                            left outer join dbo.ADDRESS on ADDRESS.CONSTITUENTID=CONSTITUENT.ID and ADDRESS.ISPRIMARY = 1
                            left outer join dbo.PHONE on PHONE.CONSTITUENTID=CONSTITUENT.ID and PHONE.ISPRIMARY = 1
                        where
                            CONSTITUENT.ID = @ID;
                    end

                    select @ZIPLOOKUPCOUNTRIES = dbo.UFN_ZIPCITYSTATE_GETCOUNTRIES_TOITEMLISTXML();

                    return 0;