USP_DATAFORMTEMPLATE_VIEW_STEWARDSHIPPLANS

The load procedure used by the view dataform template "Stewardship Plans 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.
@ADDRESS	nvarchar(300)	INOUT	Address
@PHONENUMBER	nvarchar(100)	INOUT	Phone
@PHONETYPE	nvarchar(100)	INOUT	Phone type
@EMAILADDRESS	UDT_EMAILADDRESS	INOUT	Email
@WEBADDRESS	UDT_WEBADDRESS	INOUT	Web
@PICTURE	varbinary	INOUT	Picture
@RECENTGIFTS	xml	INOUT	Recent Revenue
@ISORGANIZATION	bit	INOUT	Is organization
@ISGROUP	bit	INOUT	Is group
@ISHOUSEHOLD	bit	INOUT	Is household
@DONOTMAIL	bit	INOUT	DONOTMAIL
@DONOTEMAIL	bit	INOUT	DONOTEMAIL
@DONOTPHONE	bit	INOUT	DONOTPHONE
@PHONEISCONFIDENTIAL	bit	INOUT	PHONEISCONFIDENTIAL
@ADDRESSISCONFIDENTIAL	bit	INOUT	ADDRESSISCONFIDENTIAL
@ADDRESSID	uniqueidentifier	INOUT	ADDRESSID
@PHONENUMBERID	uniqueidentifier	INOUT	PHONENUMBERID
@EMAILADDRESSID	uniqueidentifier	INOUT	EMAILADDRESSID
@CURRENTAPPUSERID	uniqueidentifier	IN	Input parameter indicating the ID of the current user.

Definition

Copy



CREATE procedure dbo.USP_DATAFORMTEMPLATE_VIEW_STEWARDSHIPPLANS(
    @ID uniqueidentifier,
    @DATALOADED bit = 0 output,
    @ADDRESS nvarchar(300) = null output,
    @PHONENUMBER nvarchar(100) = null output,
    @PHONETYPE nvarchar(100) = null output,
    @EMAILADDRESS dbo.UDT_EMAILADDRESS = null output,
    @WEBADDRESS dbo.UDT_WEBADDRESS = null output,
    @PICTURE varbinary(max) = null output,
    @RECENTGIFTS xml = null output,
    @ISORGANIZATION bit = null output,
    @ISGROUP bit = null output,
    @ISHOUSEHOLD bit = null output,
    @DONOTMAIL bit = null output,
    @DONOTEMAIL bit = null output,
    @DONOTPHONE bit = null output,
    @PHONEISCONFIDENTIAL bit = null output,
    @ADDRESSISCONFIDENTIAL bit = null output,
    @ADDRESSID uniqueidentifier = null output,
    @PHONENUMBERID uniqueidentifier = null output,
    @EMAILADDRESSID uniqueidentifier = null output,
    @CURRENTAPPUSERID uniqueidentifier = null
) as
    set nocount on;

    set @DATALOADED = 0;
    declare @FEATURETYPEFORM tinyint = 1;
    declare @THISFORMINSTANCEID uniqueidentifier = '685E4DA5-1823-4940-956A-E2AEB059B6A1';

    declare @CURRENTDATE datetime;
    set @CURRENTDATE = getdate();

    select
        @DATALOADED = 1,
        @ISORGANIZATION = C.ISORGANIZATION,
        @ISGROUP = C.ISGROUP,
        @ADDRESS = dbo.UFN_BUILDFULLADDRESS(A.ID, A.ADDRESSBLOCK, A.CITY, A.STATEID, A.POSTCODE, A.COUNTRYID),
        @DONOTMAIL = A.DONOTMAIL,
        @ADDRESSISCONFIDENTIAL = A.ISCONFIDENTIAL,
        @ADDRESSID = A.ID,        
        @PHONENUMBER = dbo.UFN_PHONE_GETINTERNATIONALNUMBER(PH.COUNTRYID, PH.NUMBER),
        @PHONETYPE = dbo.UFN_PHONETYPECODE_GETDESCRIPTION(PH.PHONETYPECODEID),
        @DONOTPHONE = PH.DONOTCALL,
        @PHONEISCONFIDENTIAL = PH.ISCONFIDENTIAL,
        @PHONENUMBERID = PH.ID,
        @EMAILADDRESS = E.EMAILADDRESS,
        @DONOTEMAIL = E.DONOTEMAIL,
        @EMAILADDRESSID = E.ID,
        @WEBADDRESS = C.WEBADDRESS,
        @PICTURE = C.PICTURETHUMBNAIL,
        @RECENTGIFTS = dbo.UFN_STEWARDSHIPPLAN_RECENTGIFTS2_TOITEMLISTXML(C.ID, @CURRENTAPPUSERID, @THISFORMINSTANCEID, @FEATURETYPEFORM)
    from 
        dbo.CONSTITUENT C
        left outer join dbo.ADDRESS A on A.CONSTITUENTID=C.ID and A.ISPRIMARY = 1
        left outer join dbo.PHONE PH on PH.CONSTITUENTID=C.ID and PH.ISPRIMARY = 1
        left outer join dbo.EMAILADDRESS E on E.CONSTITUENTID=C.ID and E.ISPRIMARY = 1
    where
        C.ID = @ID;

    select 
      @ISHOUSEHOLD = case when G.GROUPTYPECODE = 0 then 1 else 0 end
    from dbo.GROUPDATA G
    where G.ID = @ID;