USP_DATAFORMTEMPLATE_VIEW_CONSTITUENTPRIMARYCONTACTINFO

The load procedure used by the view dataform template "Constituent Primary Contact Information 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.
@ADDRESSTYPELABEL	nvarchar(100)	INOUT	ADDRESSTYPELABEL
@ADDRESSTYPEID	uniqueidentifier	INOUT	ADDRESSTYPEID
@ADDRESS	nvarchar(150)	INOUT	ADDRESS
@CITY	nvarchar(50)	INOUT	CITY
@STATEID	uniqueidentifier	INOUT	STATEID
@POSTCODE	nvarchar(12)	INOUT	POSTCODE
@COUNTRYID	uniqueidentifier	INOUT	COUNTRYID
@PHONETYPELABEL	nvarchar(100)	INOUT	PHONETYPELABEL
@PHONETYPEID	uniqueidentifier	INOUT	PHONETYPEID
@PHONE	nvarchar(100)	INOUT	PHONE
@EMAILTYPELABEL	nvarchar(100)	INOUT	EMAILTYPELABEL
@EMAILTYPEID	uniqueidentifier	INOUT	EMAILTYPEID
@EMAIL	UDT_EMAILADDRESS	INOUT	EMAIL
@WEBADDRESS	UDT_WEBADDRESS	INOUT	WEBADDRESS
@DONOTMAIL	bit	INOUT	DONOTMAIL
@DONOTMAILREASONCODEID	uniqueidentifier	INOUT	DONOTMAILREASONCODEID
@DONOTCALL	bit	INOUT	DONOTCALL
@DONOTEMAIL	bit	INOUT	DONOTEMAIL
@ISCONFIDENTIAL	bit	INOUT	ISCONFIDENTIAL
@DONOTCALLREASONCODEID	uniqueidentifier	INOUT	DONOTCALLREASONCODEID
@PHONEISCONFIDENTIAL	bit	INOUT	PHONEISCONFIDENTIAL

Definition

Copy


                CREATE procedure dbo.USP_DATAFORMTEMPLATE_VIEW_CONSTITUENTPRIMARYCONTACTINFO
                (
                    @ID uniqueidentifier,
                    @DATALOADED bit = 0 output,
                    @ADDRESSTYPELABEL nvarchar(100) = null output,
                    @ADDRESSTYPEID uniqueidentifier = null output,
                    @ADDRESS nvarchar(150) = null output,
                    @CITY nvarchar(50) = null output,
                    @STATEID uniqueidentifier = null output,
                    @POSTCODE nvarchar(12) = null output,
                    @COUNTRYID uniqueidentifier = null output,
                    @PHONETYPELABEL nvarchar(100) = null output,
                    @PHONETYPEID uniqueidentifier = null output,
                    @PHONE nvarchar(100) = null output,
                    @EMAILTYPELABEL nvarchar(100) = null output,
                    @EMAILTYPEID uniqueidentifier = null output,
                    @EMAIL dbo.UDT_EMAILADDRESS = null output,
                    @WEBADDRESS dbo.UDT_WEBADDRESS = null output,
                    @DONOTMAIL bit = null output,
                    @DONOTMAILREASONCODEID uniqueidentifier = null output,
                    @DONOTCALL bit = null output,
                    @DONOTEMAIL bit = null output,
                    @ISCONFIDENTIAL bit = null output,
                    @DONOTCALLREASONCODEID uniqueidentifier = null output,
                    @PHONEISCONFIDENTIAL bit = null output
                )
                as
                    set nocount on;

                    set @DATALOADED = 1;

                    --Adding this so this view can be used for both ERB and the real tables

                    if exists(select ID from dbo.CONSTITUENT where ID = @ID)
                    begin
                        select                        
                            @ADDRESSTYPELABEL = ADDRESSTYPECODE.DESCRIPTION,
                            @ADDRESSTYPEID = ADDRESS.ADDRESSTYPECODEID,
                            @ADDRESS = ADDRESS.ADDRESSBLOCK,
                            @CITY = ADDRESS.CITY,
                            @STATEID = ADDRESS.STATEID,
                            @POSTCODE = ADDRESS.POSTCODE,
                            @COUNTRYID = ADDRESS.COUNTRYID,
                            @DONOTMAIL = ADDRESS.DONOTMAIL,
                            @DONOTMAILREASONCODEID = ADDRESS.DONOTMAILREASONCODEID,
                            @ISCONFIDENTIAL = ADDRESS.ISCONFIDENTIAL
                        from
                            dbo.ADDRESS
                            left join dbo.ADDRESSTYPECODE 
                            on ADDRESS.ADDRESSTYPECODEID = ADDRESSTYPECODE.ID
                        where
                            ADDRESS.CONSTITUENTID = @ID
                            and ADDRESS.ISPRIMARY = 1;

                        select
                            @PHONETYPELABEL = PHONETYPECODE.DESCRIPTION,
                            @PHONETYPEID = PHONE.PHONETYPECODEID,
                            @PHONE = PHONE.NUMBER,
                            @DONOTCALL = PHONE.DONOTCALL,
                            @DONOTCALLREASONCODEID = PHONE.DONOTCALLREASONCODEID,
                            @PHONEISCONFIDENTIAL = PHONE.ISCONFIDENTIAL
                        from
                            dbo.PHONE
                            left join dbo.PHONETYPECODE
                            on PHONE.PHONETYPECODEID = PHONETYPECODE.ID
                        where
                            PHONE.CONSTITUENTID = @ID
                            and PHONE.ISPRIMARY = 1;

                        select
                            @EMAILTYPELABEL = EMAILADDRESSTYPECODE.DESCRIPTION,
                            @EMAILTYPEID = EMAILADDRESS.EMAILADDRESSTYPECODEID,
                            @EMAIL = EMAILADDRESS.EMAILADDRESS,
                            @DONOTEMAIL = EMAILADDRESS.DONOTEMAIL
                        from
                            dbo.EMAILADDRESS
                            left join dbo.EMAILADDRESSTYPECODE
                            on EMAILADDRESS.EMAILADDRESSTYPECODEID = EMAILADDRESSTYPECODE.ID
                        where
                            EMAILADDRESS.CONSTITUENTID = @ID
                            and EMAILADDRESS.ISPRIMARY = 1;

                        select
                            @WEBADDRESS = CONSTITUENT.WEBADDRESS
                        from
                            dbo.CONSTITUENT
                        where
                            CONSTITUENT.ID = @ID;
                    end
                    else
                    begin
                        select 
                            @ADDRESSTYPEID = BATCHREVENUECONSTITUENT.ADDRESSTYPECODEID,
                            @ADDRESS = BATCHREVENUECONSTITUENT.ADDRESSBLOCK,
                            @CITY = BATCHREVENUECONSTITUENT.CITY,
                            @STATEID = BATCHREVENUECONSTITUENT.STATEID,
                            @POSTCODE = BATCHREVENUECONSTITUENT.POSTCODE,
                            @COUNTRYID = BATCHREVENUECONSTITUENT.COUNTRYID,
                            @PHONETYPEID = BATCHREVENUECONSTITUENT.PHONETYPECODEID,
                            @PHONE = BATCHREVENUECONSTITUENT.NUMBER,
                            @EMAILTYPEID = BATCHREVENUECONSTITUENT.EMAILADDRESSTYPECODEID,
                            @EMAIL = BATCHREVENUECONSTITUENT.EMAILADDRESS,
                            @WEBADDRESS = BATCHREVENUECONSTITUENT.WEBADDRESS,
                            @DONOTMAIL = BATCHREVENUECONSTITUENT.DONOTMAIL,
                            @DONOTMAILREASONCODEID = BATCHREVENUECONSTITUENT.DONOTMAILREASONCODEID,
                            @DONOTCALL = BATCHREVENUECONSTITUENT.DONOTCALL,
                            @DONOTEMAIL = BATCHREVENUECONSTITUENT.DONOTEMAIL
                        from dbo.BATCHREVENUECONSTITUENT
                        where ID = @ID
                    end