USP_DATAFORMTEMPLATE_VIEW_GRANTORPROFILE

The load procedure used by the view dataform template "Grantor Summary Profile View Form"

Parameters

Parameter	Parameter Type	Mode	Description
@ID	uniqueidentifier	IN	The input ID parameter used to load the fields defined on the form.
@CURRENTAPPUSERID	uniqueidentifier	IN	Input parameter indicating the ID of the current user.
@DATALOADED	bit	INOUT	Output parameter indicating whether or not data was actually loaded.
@ISCONSTITUENTPROFILE	bit	INOUT	ISCONSTITUENTPROFILE
@ISORGANIZATION	bit	INOUT	ISORGANIZATION
@ADDRESS	nvarchar(300)	INOUT	ADDRESS
@PHONENUMBER	nvarchar(100)	INOUT	PHONENUMBER
@PHONETYPE	nvarchar(100)	INOUT	PHONETYPE
@EMAILADDRESS	UDT_EMAILADDRESS	INOUT	EMAILADDRESS
@DONOTMAIL	bit	INOUT	DONOTMAIL
@DONOTEMAIL	bit	INOUT	DONOTEMAIL
@DONOTPHONE	bit	INOUT	DONOTPHONE
@WEBADDRESS	UDT_WEBADDRESS	INOUT	WEBADDRESS
@RELATEDCONSTITUENT	nvarchar(154)	INOUT	RELATEDCONSTITUENT
@RELATEDCONSTITUENTID	uniqueidentifier	INOUT	RELATEDCONSTITUENTID
@BOARDMEMBERCONSTITUENCYTEXT	nvarchar(100)	INOUT	BOARDMEMBERCONSTITUENCYTEXT
@RELATIONCONSTITUENCYTEXT	nvarchar(100)	INOUT	RELATIONCONSTITUENCYTEXT
@STAFFCONSTITUENCYTEXT	nvarchar(100)	INOUT	STAFFCONSTITUENCYTEXT
@DONORCONSTITUENCYTEXT	nvarchar(100)	INOUT	DONORCONSTITUENCYTEXT
@FUNDRAISERCONSTITUENCYTEXT	nvarchar(100)	INOUT	FUNDRAISERCONSTITUENCYTEXT
@PROSPECTCONSTITUENCYTEXT	nvarchar(100)	INOUT	PROSPECTCONSTITUENCYTEXT
@VOLUNTEERCONSTITUENCYTEXT	nvarchar(100)	INOUT	VOLUNTEERCONSTITUENCYTEXT
@COMMUNITYMEMBERCONSTITUENCYTEXT	nvarchar(100)	INOUT	COMMUNITYMEMBERCONSTITUENCYTEXT
@USERDEFINEDCONSTITUENCYTEXT	nvarchar(4000)	INOUT	USERDEFINEDCONSTITUENCYTEXT
@ALUMNUSSTATUSTEXT	nvarchar(100)	INOUT	ALUMNUSSTATUSTEXT
@REGISTRANTSTATUSTEXT	nvarchar(100)	INOUT	REGISTRANTSTATUSTEXT
@VENDORSTATUSTEXT	nvarchar(100)	INOUT	VENDORSTATUSTEXT
@ISINACTIVE	bit	INOUT	ISINACTIVE
@PICTURE	varbinary	INOUT	PICTURE
@LOOKUPID	nvarchar(100)	INOUT	Lookup ID
@WEALTHPOINTUPDATEPENDING	bit	INOUT	WEALTHPOINTUPDATEPENDING
@MATCHFINDERCONSTITUENCYTEXT	nvarchar(100)	INOUT	MATCHFINDERCONSTITUENCYTEXT
@MATCHFINDERONLINERECORDID	int	INOUT	MATCHFINDERONLINERECORDID
@SOLICITCODECOUNT	int	INOUT	SOLICITCODECOUNT
@GIVESANONYMOUSLY	bit	INOUT	Gives anonymously
@MEMBERCONSTITUENCYTEXT	nvarchar(100)	INOUT	MEMBERCONSTITUENCYTEXT
@RECOGNITIONCONSTITUENCYTEXT	nvarchar(100)	INOUT	RECOGNITIONCONSTITUENCYTEXT
@PLANNEDGIVERCONSTITUENCYTEXT	nvarchar(100)	INOUT	PLANNEDGIVERCONSTITUENCYTEXT
@BANKCONSTITUENCYTEXT	nvarchar(100)	INOUT	BANKCONSTITUENCYTEXT
@DECLARATIONSONFILE	bit	INOUT	DECLARATIONSONFILE
@PATRONCONSTITUENCYTEXT	nvarchar(100)	INOUT	PATRONCONSTITUENCYTEXT
@GRANTORTYPE	nvarchar(250)	INOUT	Grantor type
@GRANTORCONTACT	nvarchar(700)	INOUT	Grantor contact
@GRANTORCONSTITUENCYTEXT	nvarchar(100)	INOUT	GRANTORCONSTITUENCYTEXT
@COMMITTEECONSTITUENCYTEXT	nvarchar(100)	INOUT	COMMITTEECONSTITUENCYTEXT
@COMMITTEEMEMBERCONSTITUENCYTEXT	nvarchar(100)	INOUT	COMMITTEEMEMBERCONSTITUENCYTEXT
@SPONSORCONSTITUENCYTEXT	nvarchar(100)	INOUT	SPONSORCONSTITUENCYTEXT
@LOYALDONORCONSTITUENCYTEXT	nvarchar(100)	INOUT	LOYALDONORCONSTITUENCYTEXT
@MAJORDONORCONSTITUENCYTEXT	nvarchar(100)	INOUT	MAJORDONORCONSTITUENCYTEXT
@CONSTITUENTINACTIVITYREASON	nvarchar(63)	INOUT	Inactive reason
@PHONEISCONFIDENTIAL	bit	INOUT	PHONEISCONFIDENTIAL
@ADDRESSISCONFIDENTIAL	bit	INOUT	ADDRESSISCONFIDENTIAL
@ADDRESSID	uniqueidentifier	INOUT	ADDRESSID
@PHONENUMBERID	uniqueidentifier	INOUT	PHONENUMBERID
@EMAILADDRESSID	uniqueidentifier	INOUT	EMAILADDRESSID
@SOCIALMEDIAACCOUNTS	xml	INOUT

Definition

Copy


CREATE  procedure [dbo].[USP_DATAFORMTEMPLATE_VIEW_GRANTORPROFILE]
(
    @ID uniqueidentifier,
    @CURRENTAPPUSERID uniqueidentifier=null,
    @DATALOADED bit = 0 output,
    @ISCONSTITUENTPROFILE bit = null output,
    @ISORGANIZATION bit = null output,
    @ADDRESS nvarchar(300) = null output,
    @PHONENUMBER nvarchar(100) = null output,
    @PHONETYPE nvarchar(100) = null output,
    @EMAILADDRESS dbo.UDT_EMAILADDRESS = null output,
    @DONOTMAIL bit = null output,
    @DONOTEMAIL bit = null output,
    @DONOTPHONE bit = null output,
    @WEBADDRESS dbo.UDT_WEBADDRESS = null output,
    @RELATEDCONSTITUENT nvarchar(154) = null output,
    @RELATEDCONSTITUENTID uniqueidentifier = null output,
    @BOARDMEMBERCONSTITUENCYTEXT nvarchar(100) = null output,
    @RELATIONCONSTITUENCYTEXT nvarchar(100) = null output,
    @STAFFCONSTITUENCYTEXT nvarchar(100) = null output,
    @DONORCONSTITUENCYTEXT nvarchar(100) = null output,
    @FUNDRAISERCONSTITUENCYTEXT nvarchar(100) = null output,
    @PROSPECTCONSTITUENCYTEXT nvarchar(100) = null output,
    @VOLUNTEERCONSTITUENCYTEXT nvarchar(100) = null output,
    @COMMUNITYMEMBERCONSTITUENCYTEXT nvarchar(100) = null output,
    @USERDEFINEDCONSTITUENCYTEXT nvarchar(4000) = null output,
    @ALUMNUSSTATUSTEXT nvarchar(100) = null output,
    @REGISTRANTSTATUSTEXT nvarchar(100) = null output,
    @VENDORSTATUSTEXT nvarchar(100) = null output,
    @ISINACTIVE bit = null output,                
    @PICTURE varbinary(max) = null output,
    @LOOKUPID nvarchar(100) = null output,
    @WEALTHPOINTUPDATEPENDING bit = null output,
    @MATCHFINDERCONSTITUENCYTEXT nvarchar(100) = null output,
    @MATCHFINDERONLINERECORDID int = null output,
    @SOLICITCODECOUNT int = null output,
    @GIVESANONYMOUSLY bit = null output,
    @MEMBERCONSTITUENCYTEXT nvarchar(100) = null output,
    @RECOGNITIONCONSTITUENCYTEXT nvarchar(100) = null output,
    @PLANNEDGIVERCONSTITUENCYTEXT nvarchar(100) = null output,
    @BANKCONSTITUENCYTEXT nvarchar(100) = null output,
    @DECLARATIONSONFILE bit = null output,
    @PATRONCONSTITUENCYTEXT nvarchar(100) = null output,
    @GRANTORTYPE nvarchar(250) = null output,
    @GRANTORCONTACT nvarchar(700) = null output,
    @GRANTORCONSTITUENCYTEXT nvarchar(100) = null output,
    @COMMITTEECONSTITUENCYTEXT nvarchar(100) = null output,
    @COMMITTEEMEMBERCONSTITUENCYTEXT nvarchar(100) = null output,
    @SPONSORCONSTITUENCYTEXT nvarchar(100) = null output,
    @LOYALDONORCONSTITUENCYTEXT nvarchar(100) = null output,
    @MAJORDONORCONSTITUENCYTEXT nvarchar(100) = null output,
    @CONSTITUENTINACTIVITYREASON nvarchar(63) = null output,
    @PHONEISCONFIDENTIAL bit = null output,
    @ADDRESSISCONFIDENTIAL bit = null output,
    @ADDRESSID uniqueidentifier = null output,
    @PHONENUMBERID uniqueidentifier = null output,
    @EMAILADDRESSID uniqueidentifier = null output,
    @SOCIALMEDIAACCOUNTS xml = null output
)    
as
    set nocount on;

    set @DATALOADED = 0;
    set @MATCHFINDERONLINERECORDID = 0;            

    --CONSTIT FIELDS
    select
        @DATALOADED = 1,
        @ISORGANIZATION = CONSTITUENT.ISORGANIZATION,
        @WEBADDRESS = CONSTITUENT.WEBADDRESS,
        @PICTURE = CONSTITUENT.PICTURETHUMBNAIL,
        @ISINACTIVE = CONSTITUENT.ISINACTIVE,
        @LOOKUPID = CONSTITUENT.LOOKUPID,
        @ISCONSTITUENTPROFILE = 1,
        @GIVESANONYMOUSLY = CONSTITUENT.GIVESANONYMOUSLY
    from
        dbo.CONSTITUENT
    where
        CONSTITUENT.ID = @ID;                                                                  

    if @ISINACTIVE = 1
        select @CONSTITUENTINACTIVITYREASON = dbo.UFN_CONSTITUENTINACTIVITYREASONCODE_TRANSLATE(CONSTITUENTINACTIVEDETAIL.CONSTITUENTINACTIVITYREASONCODEID)
        from dbo.CONSTITUENTINACTIVEDETAIL
        where ID = @ID        

    --Grantor information
    select
        @GRANTORTYPE = dbo.UFN_GRANTORTYPECODE_GETDESCRIPTION(GRANTOR.GRANTORTYPECODEID),
        @GRANTORCONTACT = isnull(NF.NAME, '')
    from
        dbo.GRANTOR
        left join dbo.RELATIONSHIP on RELATIONSHIP.ID = GRANTOR.CONTACTID
        outer apply dbo.UFN_CONSTITUENT_DISPLAYNAME(RELATIONSHIP.RECIPROCALCONSTITUENTID) NF
    where
        GRANTOR.ID = @ID;

    --RELATIONSHIP FIELDS FOR RELATED CONSTIT LABEL
    SELECT 
        @RELATEDCONSTITUENT = PARENTORG.[NAME],
        @RELATEDCONSTITUENTID = PARENTORG.ID
    FROM 
        dbo.ORGANIZATIONDATA 
        inner join dbo.CONSTITUENT AS PARENTORG on PARENTORG.ID = ORGANIZATIONDATA.PARENTCORPID
    WHERE 
        ORGANIZATIONDATA.ID = @ID;                

    --EMAIL
    SELECT 
        @EMAILADDRESS = EMAILADDRESS,
        @DONOTEMAIL = EMAILADDRESS.DONOTEMAIL,
        @EMAILADDRESSID = EMAILADDRESS.ID
    FROM 
        dbo.EMAILADDRESS
    WHERE
        EMAILADDRESS.CONSTITUENTID = @ID and
        EMAILADDRESS.ISPRIMARY = 1;

    --PHONE
    SELECT
        @PHONENUMBER = PHONE.NUMBER,
        @PHONETYPE = (select DESCRIPTION FROM dbo.PHONETYPECODE WHERE ID = PHONE.PHONETYPECODEID),
        @DONOTPHONE = PHONE.DONOTCALL,
        @PHONEISCONFIDENTIAL = PHONE.ISCONFIDENTIAL,
        @PHONENUMBERID = PHONE.ID
    FROM 
        dbo.PHONE
    WHERE 
        PHONE.CONSTITUENTID = @ID and
        PHONE.ISPRIMARY = 1;

    --ADDRESS
    select 
        @ADDRESS = dbo.UFN_BUILDFULLADDRESS(ADDRESS.ID, ADDRESS.ADDRESSBLOCK, ADDRESS.CITY, ADDRESS.STATEID, ADDRESS.POSTCODE, ADDRESS.COUNTRYID),
        @DONOTMAIL = ADDRESS.DONOTMAIL,
        @ADDRESSISCONFIDENTIAL = ADDRESS.ISCONFIDENTIAL,
        @ADDRESSID = ADDRESS.ID
    from
        dbo.ADDRESS
    where
        ADDRESS.CONSTITUENTID = @ID and
        ADDRESS.ISPRIMARY = 1;

    select top 1
        @MATCHFINDERONLINERECORDID = MATCHFINDERRECORDID 
    from 
        dbo.MATCHFINDERCONSTITUENT
    where 
        ID = @ID;

    --SOCIAL MEDIA ACCOUNTS
    select @SOCIALMEDIAACCOUNTS = dbo.UFN_CONSTITUENT_GETSOCIALMEDIAACCOUNTS_TOITEMLISTXML(@ID);

    --Constituencies

    set @PLANNEDGIVERCONSTITUENCYTEXT = case 
              when dbo.UFN_CONSTITUENT_ISPLANNEDGIVER(@ID) = 1 then dbo.UFN_CONSTITUENCY_GETDESCRIPTION('CEE46FE7-3FBB-4DFE-97EB-BA67DD33C634')  --Planned giver
              else null end;

    set @RECOGNITIONCONSTITUENCYTEXT = case 
                 when dbo.UFN_CONSTITUENT_ISRECOGNITION(@ID,@CURRENTAPPUSERID) = 1 then dbo.UFN_CONSTITUENCY_GETDESCRIPTION('3dfac92e-78bd-4051-abdc-02c675deb8f6')  --Recognition
                 else null end;

    set @MEMBERCONSTITUENCYTEXT = case 
              when dbo.UFN_CONSTITUENT_ISMEMBER(@ID,@CURRENTAPPUSERID) = 1 then dbo.UFN_CONSTITUENCY_GETDESCRIPTION('2d11326e-8f3b-4322-9797-57c1aacfa5df')  --Member
              else null end;

    set    @BOARDMEMBERCONSTITUENCYTEXT = case
                                        when dbo.UFN_CONSTITUENT_ISBOARDMEMBER(@ID) = 1 then dbo.UFN_CONSTITUENCY_GETDESCRIPTION('F828E957-5F5E-479A-8F23-2FFD6C7C68FF') --Board member
                                        else null end;

    set @STAFFCONSTITUENCYTEXT = case
                                    when dbo.UFN_CONSTITUENT_ISSTAFF(@ID) = 1 then dbo.UFN_CONSTITUENCY_GETDESCRIPTION('6093915E-ADE9-42BE-88AE-304731754467') --Staff
                                    else null end;
    set @DONORCONSTITUENCYTEXT = case
                                    when dbo.UFN_CONSTITUENT_ISDONOR(@ID) = 1 then dbo.UFN_CONSTITUENCY_GETDESCRIPTION('70165682-4324-46EC-9439-83FC0CC67E7F') --Donor
                                    else null end;

    set @LOYALDONORCONSTITUENCYTEXT = case when dbo.UFN_CONSTITUENT_ISLOYALDONOR(@ID) = 1 then dbo.UFN_CONSTITUENCY_GETDESCRIPTION('E5A0EA42-65BA-4B25-AFE2-9B709F99E72B') --Loyal donor
                    else null end;

    set @MAJORDONORCONSTITUENCYTEXT = case when dbo.UFN_CONSTITUENT_ISMAJORDONOR(@ID) = 1 then dbo.UFN_CONSTITUENCY_GETDESCRIPTION('1A9BFE80-604D-4B5B-8065-E751DDF6EF39') --Major donor
                    else null end;

    if len(@LOYALDONORCONSTITUENCYTEXT)>0 or len(@MAJORDONORCONSTITUENCYTEXT)>0 
        set @DONORCONSTITUENCYTEXT = null;

    set @FUNDRAISERCONSTITUENCYTEXT = case
                                        when dbo.UFN_CONSTITUENT_ISFUNDRAISER(@ID) = 1 then dbo.UFN_CONSTITUENCY_GETDESCRIPTION('D2DCA06A-BE6E-40B3-B95D-59A926181923') --Fundraiser
                                        else null end;
    set @PROSPECTCONSTITUENCYTEXT = case
                                        when dbo.UFN_CONSTITUENT_ISPROSPECT(@ID) = 1 then dbo.UFN_CONSTITUENCY_GETDESCRIPTION('00E748FB-940D-4A7D-A133-C148B29410A8') --Major giving prospect
                                        else null end;
    set @VOLUNTEERCONSTITUENCYTEXT = case
                                        when dbo.UFN_CONSTITUENT_ISVOLUNTEER(@ID) = 1 then dbo.UFN_CONSTITUENCY_GETDESCRIPTION('E7489703-3D63-4017-A2BC-88C092563C5D') --Volunteer
                                        else null end;
--    set @COMMUNITYMEMBERCONSTITUENCYTEXT = case
--                                            when dbo.UFN_CONSTITUENT_ISCOMMUNITYMEMBER(@ID) = 1 then dbo.UFN_CONSTITUENCY_GETDESCRIPTION('55FE8E7C-2B68-44C8-B35C-818AD1944C03') --Community member
--                                            else null end;    
    set @PATRONCONSTITUENCYTEXT = case
                                when dbo.UFN_CONSTITUENT_ISPATRON(@ID) = 1 then dbo.UFN_CONSTITUENCY_GETDESCRIPTION('A843B859-4C6B-445B-97F3-179582E270A5') --Patron
                                else null end;                                                        
    set @ALUMNUSSTATUSTEXT = dbo.UFN_CONSTITUENT_GETALUMNUSSTATUSTEXT(@ID);

    set @REGISTRANTSTATUSTEXT = case
                                    when dbo.UFN_CONSTITUENT_ISREGISTRANT(@ID) = 1 then dbo.UFN_CONSTITUENCY_GETDESCRIPTION('C49D4B46-72A7-4206-91AA-BEABA2323E3C') --Event registrant
                                    else null end;
    set @VENDORSTATUSTEXT = case
                                when dbo.UFN_CONSTITUENT_ISVENDOR(@ID) = 1 then dbo.UFN_CONSTITUENCY_GETDESCRIPTION('5435C96D-8617-46C3-9A62-5AFF08451A53') --Vendor
                                else null end;

    set @MATCHFINDERCONSTITUENCYTEXT = case
                                when not @MATCHFINDERONLINERECORDID is null and @MATCHFINDERONLINERECORDID <> 0 then dbo.UFN_CONSTITUENCY_GETDESCRIPTION('2D04A9C5-27D0-4646-BF0F-6826E4C12632') --Matching gift organization
                                else null end;
    set @USERDEFINEDCONSTITUENCYTEXT = dbo.UFN_CONSTITUENCIES_USERDEFINED(@ID);

    set @BANKCONSTITUENCYTEXT = case
                                        when dbo.UFN_CONSTITUENT_ISBANK(@ID) = 1 then dbo.UFN_CONSTITUENCY_GETDESCRIPTION('171AB3CD-C4E1-4825-B693-10F524A7A594') --Bank
                                        else null end;                        

    set @GRANTORCONSTITUENCYTEXT = case
        when dbo.UFN_CONSTITUENT_ISGRANTOR(@ID) = 1 then dbo.UFN_CONSTITUENCY_GETDESCRIPTION('D9982C99-15C1-4C90-873E-56FD4B164056') --Grantor
        else null end;

    set @COMMITTEECONSTITUENCYTEXT = case
                                        when dbo.UFN_CONSTITUENT_ISCOMMITTEE(@ID) = 1 then dbo.UFN_CONSTITUENCY_GETDESCRIPTION('AC9DB5A4-14E0-416A-9FB2-04038AC66799') --Committee
                                        else null end;

    set @COMMITTEEMEMBERCONSTITUENCYTEXT = case 
                                when dbo.UFN_CONSTITUENT_ISCOMMITTEEMEMBER(@ID) = 1 
                                then dbo.UFN_CONSTITUENCY_GETDESCRIPTION('093A3D4F-2974-447F-AD92-870EB4A04593') /* Committee member */ 
                                else null end;

    set @SPONSORCONSTITUENCYTEXT = case dbo.UFN_CONSTITUENT_SPONSORTYPECODE(@ID)
                                     when 0 then dbo.UFN_CONSTITUENCY_GETDESCRIPTION('4D746A03-A0AB-45F3-A30B-1AD4F304E622') --Sponsor
                                     when 1 then dbo.UFN_CONSTITUENCY_GETDESCRIPTION('F89E03BC-E724-4e5d-943B-72D4D1E1E916') --Sponsorship recipient
                                     when 2 then dbo.UFN_CONSTITUENCY_GETDESCRIPTION('908E521C-B0A5-4832-B664-7D7B079D77C2') --Sponsorship donor
                                    else null end;

    if exists(select 1 from dbo.WEALTH where ID=@ID and PENDINGSEARCH=1) begin
        set @WEALTHPOINTUPDATEPENDING = 1;
    end else begin
        set @WEALTHPOINTUPDATEPENDING = 0;
    end

    --Solicit Codes
    select 
        @SOLICITCODECOUNT = count(ID)
    from 
        CONSTITUENTSOLICITCODE
    where 
        CONSTITUENTID=@ID

    --Gift Aid declarations indicator
    select 
        @DECLARATIONSONFILE = case  
            when count(*) > 0 then 1
            else 0
        end
    from 
        dbo.TAXDECLARATION
    where 
        TAXDECLARATION.CONSTITUENTID=@ID and 
        (
            (DECLARATIONSTARTS <= getdate() and DECLARATIONENDS >= getdate())
            or (DECLARATIONSTARTS <= getdate() and DECLARATIONENDS is null)
        ) and 
        exists (select PERMISSIONGRANTED from dbo.UFN_APPUSER_HASPERMISSIONFORDECLARATION(@CURRENTAPPUSERID, TAXDECLARATION.CHARITYCLAIMREFERENCENUMBERID));


    return 0;