USP_DATAFORMTEMPLATE_VIEW_CONSTITUENTWINDOW

The load procedure used by the view dataform template "Revenue Batch Constituent Window 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.
@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	nvarchar(50)	INOUT	Title
@SUFFIXCODEID	nvarchar(50)	INOUT	Suffix
@GENDERCODE	nvarchar(50)	INOUT	Gender
@BIRTHDATE	UDT_FUZZYDATE	INOUT	Birth date
@GIVESANONYMOUSLY	bit	INOUT	Gives anonymously
@ADDRESS_STATEID	nvarchar(50)	INOUT	State
@ADDRESS_ADDRESSBLOCK	nvarchar(150)	INOUT	Address
@ADDRESS_CITY	nvarchar(50)	INOUT	City
@ADDRESS_POSTCODE	nvarchar(12)	INOUT	ZIP
@PHONE_PHONETYPECODEID	nvarchar(50)	INOUT	Phone type
@PHONE_NUMBER	nvarchar(100)	INOUT	Phone number
@ADDRESS_COUNTRY	nvarchar(100)	INOUT	Country
@ADDRESS_COUNTRYID	uniqueidentifier	INOUT	CountryID

Definition

Copy



            CREATE procedure dbo.USP_DATAFORMTEMPLATE_VIEW_CONSTITUENTWINDOW(
                @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 nvarchar(50) = null output,
                @SUFFIXCODEID nvarchar(50) = null output,
                @GENDERCODE nvarchar(50) = null output,
                @BIRTHDATE dbo.UDT_FUZZYDATE = null output,
                @GIVESANONYMOUSLY bit = null output,                
                @ADDRESS_STATEID nvarchar(50) = null output,
                @ADDRESS_ADDRESSBLOCK nvarchar(150) = null output,
                @ADDRESS_CITY nvarchar(50) = null output,
                @ADDRESS_POSTCODE nvarchar(12) = null output,
                @PHONE_PHONETYPECODEID nvarchar(50) = null output,
                @PHONE_NUMBER nvarchar(100) = null output,
                @ADDRESS_COUNTRY nvarchar(100) = null output,
                @ADDRESS_COUNTRYID uniqueidentifIer = null output
                )
            as

            begin
                set nocount on;

                set @DATALOADED = 0;

                select
                    @DATALOADED = 1,
                    @LASTNAME = [CONSTITUENT].[KEYNAME],
                    @FIRSTNAME = [CONSTITUENT].[FIRSTNAME],
                    @MIDDLENAME = [CONSTITUENT].[MIDDLENAME],
                    @MAIDENNAME = [CONSTITUENT].[MAIDENNAME],
                    @NICKNAME = [CONSTITUENT].[NICKNAME],
                    @TITLECODEID = (select [DESCRIPTION] from dbo.[TITLECODE] where [TITLECODE].[ID]=[CONSTITUENT].[TITLECODEID]),
                    @SUFFIXCODEID = (select [DESCRIPTION] from dbo.[SUFFIXCODE] where [SUFFIXCODE].[ID]=[CONSTITUENT].[SUFFIXCODEID]),
                    @GENDERCODE = [CONSTITUENT].[GENDER],
                    @BIRTHDATE = [CONSTITUENT].[BIRTHDATE],
                    @GIVESANONYMOUSLY = [CONSTITUENT].[GIVESANONYMOUSLY],                                
                    @ADDRESS_STATEID = (select [DESCRIPTION] from dbo.[STATE] where [STATE].[ID]=[ADDRESS].[STATEID]),
                    @ADDRESS_ADDRESSBLOCK=[ADDRESS].[ADDRESSBLOCK],
                    @ADDRESS_CITY=[ADDRESS].[CITY],
                    @ADDRESS_POSTCODE=[ADDRESS].[POSTCODE],
                    @PHONE_PHONETYPECODEID=(select [DESCRIPTION] from dbo.[PHONETYPECODE] where [PHONETYPECODE].[ID]=[PHONE].[PHONETYPECODEID]),
                    @PHONE_NUMBER=dbo.UFN_PHONE_GETINTERNATIONALNUMBER([PHONE].[COUNTRYID], [PHONE].[NUMBER]),
                    @ADDRESS_COUNTRY = (select [DESCRIPTION] from dbo.[COUNTRY] where [COUNTRY].[ID] = [ADDRESS].[COUNTRYID]),
                    @ADDRESS_COUNTRYID = (select [COUNTRY].[ID] from dbo.[COUNTRY] where [COUNTRY].[ID] = [ADDRESS].[COUNTRYID])

                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

                return 0
            end