USP_DATAFORMTEMPLATE_LOAD_BBNCCONSTITUENTDATA

The load procedure used by the view dataform template "NetCommunity Constituent Data In Database View 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.
@CONSTITUENTID	uniqueidentifier	INOUT	Constituent ID
@BIRTHDATE	UDT_FUZZYDATE	INOUT	Birthdate
@TITLECODE	nvarchar(100)	INOUT	Title code
@FIRSTNAME	nvarchar(50)	INOUT	First name
@MIDDLENAME	nvarchar(50)	INOUT	Middle name
@MAIDENNAME	nvarchar(100)	INOUT	Maiden name
@KEYNAME	nvarchar(100)	INOUT	Key name
@GENDER	nvarchar(10)	INOUT	Gender
@COUNTRYLONG	nvarchar(100)	INOUT	Country (long)
@COUNTRYABBREVIATION	nvarchar(5)	INOUT	Country (abbreviation)
@ADDRESSBLOCK	nvarchar(150)	INOUT	Address block
@CITY	nvarchar(50)	INOUT	City
@NZSUBURB	nvarchar(50)	INOUT	NZ Suburb
@STATE	nvarchar(100)	INOUT	State
@NZCITY	nvarchar(100)	INOUT	NZ City
@COUNTY	nvarchar(100)	INOUT	County
@POSTCODE	nvarchar(12)	INOUT	Post code
@LOOKUPID	nvarchar(100)	INOUT	Lookup ID
@TYPE	nvarchar(100)	INOUT	Type
@EMAIL	nvarchar(200)	INOUT
@PHONENUMBER	nvarchar(200)	INOUT

Definition

Copy


                CREATE procedure dbo.USP_DATAFORMTEMPLATE_LOAD_BBNCCONSTITUENTDATA
                (
                    @ID uniqueidentifier,
                    @DATALOADED bit = 0 output,
                    @CONSTITUENTID uniqueidentifier = null output,
                    @BIRTHDATE dbo.UDT_FUZZYDATE = null output,
                    @TITLECODE nvarchar(100) = null output,
                    @FIRSTNAME nvarchar(50) = null output,
                    @MIDDLENAME nvarchar(50) = null output,
                    @MAIDENNAME nvarchar(100) = null output,
                    @KEYNAME nvarchar(100) = null output,
                    @GENDER nvarchar(10) = null output,
                    @COUNTRYLONG nvarchar(100) = null output,
                    @COUNTRYABBREVIATION nvarchar(5) = null output,
                    @ADDRESSBLOCK nvarchar(150) = null output,
                    @CITY nvarchar(50) = null output,
                    @NZSUBURB nvarchar(50) = null output,
                    @STATE nvarchar(100) = null output,
                    @NZCITY nvarchar(100) = null output,
                    @COUNTY nvarchar(100) = null output,
                    @POSTCODE nvarchar(12) = null output,
                    @LOOKUPID nvarchar(100) = null output,
          @TYPE nvarchar(100) = null output,
          @EMAIL nvarchar(200) = null output,
          @PHONENUMBER nvarchar(200) = null output

                )
                as
                set nocount on;

                select
                    @DATALOADED = 1,
                    @CONSTITUENTID = CONSTITUENT.ID,
                    @BIRTHDATE = BIRTHDATE,
                    @FIRSTNAME = FIRSTNAME,
                    @MIDDLENAME = MIDDLENAME,
                    @MAIDENNAME = MAIDENNAME,
                    @KEYNAME = KEYNAME,
                    @GENDER = GENDER,
                    @LOOKUPID = LOOKUPID,
                    @TITLECODE = coalesce((select DESCRIPTION from dbo.TITLECODE where TITLECODE.ID = CONSTITUENT.TITLECODEID), N'')
                from dbo.CONSTITUENT
                where CONSTITUENT.ID = @ID;

                if @DATALOADED = 1
                begin
                    select
                        @COUNTRYLONG = COUNTRY.DESCRIPTION,
                        @COUNTRYABBREVIATION = COUNTRY.ABBREVIATION,
                        @ADDRESSBLOCK = ADDRESS.ADDRESSBLOCK,
                        @CITY = ADDRESS.CITY,
                        @STATE = 
                            case
                            --TMV 04/11/2007 CR272266-040907 BBNC expects the full description for New Zealand city, GUID copied from dbo.USP_COUNTRYADDRESSFORMAT_GETDATA.
                            --TMV 04/13/2007 CR272671-041207 BBNC expects the full description for United Kingdom county, GUID copied from dbo.USP_COUNTRYADDRESSFORMAT_GETDATA.
                            when 
                            (
                                COUNTRY.COUNTRYADDRESSFORMATID = '959809FB-8FA7-4A19-888F-9951BD9B29D3' --United Kingdom
                                or
                                COUNTRY.COUNTRYADDRESSFORMATID = 'A3B050A5-E1C8-4E1B-99AE-40E9FCADA0BC' --New Zealand
                            )
                            then
                                STATE.DESCRIPTION
                            else
                                STATE.ABBREVIATION
                            end,
                        @POSTCODE = ADDRESS.POSTCODE,
            @TYPE = ADDRESSTYPECODE.DESCRIPTION
                    from dbo.ADDRESS 
                    left join dbo.COUNTRY on COUNTRY.ID = ADDRESS.COUNTRYID
                    left join dbo.STATE on STATE.ID = ADDRESS.STATEID
          left join ADDRESSTYPECODE on ADDRESS.ADDRESSTYPECODEID = ADDRESSTYPECODE.ID
                    where ADDRESS.CONSTITUENTID = @ID and ADDRESS.ISPRIMARY = 1;

          select
            @EMAIL = EMAILADDRESS
          from dbo.EMAILADDRESS
          where EMAILADDRESS.CONSTITUENTID = @ID and EMAILADDRESS.ISPRIMARY = 1

          select 
            @PHONENUMBER = NUMBER
          from dbo.PHONE
          where PHONE.CONSTITUENTID = @ID and PHONE.ISPRIMARY = 1

                    set @NZSUBURB = @CITY;
                    set @NZCITY = @STATE;
                    set @COUNTY = @STATE;
                end
                return 0;