USP_DATAFORMTEMPLATE_VIEW_BANKPROFILE

The load procedure used by the view dataform template "Bank 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
@DECEASEDDATE	UDT_FUZZYDATE	INOUT	DECEASEDDATE
@PICTURE	varbinary	INOUT	PICTURE
@PRIMARYEDUCATION	nvarchar(100)	INOUT	PRIMARYEDUCATION
@PRIMARYBUSINESS	nvarchar(100)	INOUT	PRIMARYBUSINESS
@PRIMARYBUSINESSID	uniqueidentifier	INOUT	PRIMARYBUSINESSID
@LOOKUPID	nvarchar(100)	INOUT	Lookup ID
@WEALTHPOINTUPDATEPENDING	bit	INOUT	WEALTHPOINTUPDATEPENDING
@PRIMARYEDUCATIONID	uniqueidentifier	INOUT	PRIMARYEDUCATIONID
@EDUCATIONATTRIBUTEDEFINED	bit	INOUT	EDUCATIONATTRIBUTEDEFINED
@MATCHFINDERCONSTITUENCYTEXT	nvarchar(100)	INOUT	MATCHFINDERCONSTITUENCYTEXT
@MATCHFINDERONLINERECORDID	int	INOUT	MATCHFINDERONLINERECORDID
@SOLICITCODECOUNT	int	INOUT	SOLICITCODECOUNT
@HOUSEHOLDTEXT	nvarchar(154)	INOUT	HOUSEHOLDTEXT
@HOUSEHOLDID	uniqueidentifier	INOUT	HOUSEHOLDID
@ISGROUP	bit	INOUT	ISGROUP
@GROUPTYPE	nvarchar(300)	INOUT	Group type
@GROUPMEMBERCOUNT	int	INOUT	No. of members
@ISHOUSEHOLD	bit	INOUT	Is Household
@GIVESANONYMOUSLY	bit	INOUT	Gives anonymously
@MEMBERCONSTITUENCYTEXT	nvarchar(100)	INOUT	MEMBERCONSTITUENCYTEXT
@RECOGNITIONCONSTITUENCYTEXT	nvarchar(100)	INOUT	RECOGNITIONCONSTITUENCYTEXT
@ISSPOUSEDECEASED	bit	INOUT	Is spouse deceased
@ISDISSOLVED	bit	INOUT	Is group dissolved
@PLANNEDGIVERCONSTITUENCYTEXT	nvarchar(100)	INOUT	PLANNEDGIVERCONSTITUENCYTEXT
@BANKCONSTITUENCYTEXT	nvarchar(16)	INOUT	BANKCONSTITUENCYTEXT
@ISBANKPROFILE	bit	INOUT	ISBANKPROFILE
@BANKCURRENTBALANCE	money	INOUT	Current balance
@BANKENDINGBALANCE	money	INOUT	Ending balance
@PATRONCONSTITUENCYTEXT	nvarchar(100)	INOUT	PATRONCONSTITUENCYTEXT
@GRANTORCONSTITUENCYTEXT	nvarchar(100)	INOUT	GRANTORCONSTITUENCYTEXT
@SPONSORCONSTITUENCYTEXT	nvarchar(100)	INOUT	SPONSORCONSTITUENCYTEXT
@COMMITTEEMEMBERCONSTITUENCYTEXT	nvarchar(100)	INOUT	COMMITTEEMEMBERCONSTITUENCYTEXT
@LOYALDONORCONSTITUENCYTEXT	nvarchar(100)	INOUT	LOYALDONORCONSTITUENCYTEXT
@MAJORDONORCONSTITUENCYTEXT	nvarchar(100)	INOUT	MAJORDONORCONSTITUENCYTEXT
@TRANSACTIONCURRENCYID	uniqueidentifier	INOUT	Transaction currency
@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_BANKPROFILE
(
    @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,
    @DECEASEDDATE dbo.UDT_FUZZYDATE = null output,
    @PICTURE varbinary(max) = null output,
    @PRIMARYEDUCATION nvarchar(100) = null output,
    @PRIMARYBUSINESS nvarchar(100) = null output,
    @PRIMARYBUSINESSID uniqueidentifier = null output,
    @LOOKUPID nvarchar(100) = null output,
    @WEALTHPOINTUPDATEPENDING bit = null output,
    @PRIMARYEDUCATIONID uniqueidentifier = null output,
    @EDUCATIONATTRIBUTEDEFINED bit = null output,
    @MATCHFINDERCONSTITUENCYTEXT nvarchar(100) = null output,
    @MATCHFINDERONLINERECORDID int = null output,
    @SOLICITCODECOUNT int = null output,
    @HOUSEHOLDTEXT nvarchar(154) = null output,
    @HOUSEHOLDID uniqueidentifier = null output,
    @ISGROUP bit = null output,
    @GROUPTYPE nvarchar(300) = null output,
    @GROUPMEMBERCOUNT integer = null output,
    @ISHOUSEHOLD bit = null output,
    @GIVESANONYMOUSLY bit = null output,
    @MEMBERCONSTITUENCYTEXT nvarchar(100)=null output,
    @RECOGNITIONCONSTITUENCYTEXT nvarchar(100)=null output,
    @ISSPOUSEDECEASED bit = null output,
    @ISDISSOLVED bit = null output,
    @PLANNEDGIVERCONSTITUENCYTEXT nvarchar(100)=null output,
    @BANKCONSTITUENCYTEXT nvarchar(16) = null output,
    @ISBANKPROFILE bit = null output,
    @BANKCURRENTBALANCE money = null output,
    @BANKENDINGBALANCE money = null output,
    @PATRONCONSTITUENCYTEXT nvarchar(100) = null output,
    @GRANTORCONSTITUENCYTEXT nvarchar(100) = null output,
    @SPONSORCONSTITUENCYTEXT nvarchar(100)=null output,
    @COMMITTEEMEMBERCONSTITUENCYTEXT nvarchar(100) = null output,
    @LOYALDONORCONSTITUENCYTEXT nvarchar(100) = null output,
    @MAJORDONORCONSTITUENCYTEXT nvarchar(100) = null output,
    @TRANSACTIONCURRENCYID uniqueidentifier = 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;

    -- be sure to set this, in case the select returns no rows
    set @DATALOADED = 0;

    set @ISBANKPROFILE = 1;

    exec dbo.USP_DATAFORMTEMPLATE_VIEW_INDIVIDUALPROFILE
        @ID = @ID,
        @CURRENTAPPUSERID = @CURRENTAPPUSERID,
        @DATALOADED = @DATALOADED output,
        @ISCONSTITUENTPROFILE = @ISCONSTITUENTPROFILE output,
        @ISORGANIZATION = @ISORGANIZATION output,
        @ADDRESS = @ADDRESS output,
        @PHONENUMBER = @PHONENUMBER output,
        @PHONETYPE = @PHONETYPE output,
        @EMAILADDRESS = @EMAILADDRESS output,
        @DONOTMAIL = @DONOTMAIL output,
        @DONOTEMAIL = @DONOTEMAIL output,
        @DONOTPHONE = @DONOTPHONE output,
        @WEBADDRESS = @WEBADDRESS output,
        @RELATEDCONSTITUENT = @RELATEDCONSTITUENT output,
        @RELATEDCONSTITUENTID = @RELATEDCONSTITUENTID output,
        @ISINACTIVE = @ISINACTIVE output,
        @PICTURE = @PICTURE output,
        @PRIMARYEDUCATION = @PRIMARYEDUCATION output,
        @PRIMARYEDUCATIONID = @PRIMARYEDUCATIONID output,
        @PRIMARYBUSINESS = @PRIMARYBUSINESS output,
        @PRIMARYBUSINESSID = @PRIMARYBUSINESSID output,
        @LOOKUPID = @LOOKUPID output,
        @BOARDMEMBERCONSTITUENCYTEXT = @BOARDMEMBERCONSTITUENCYTEXT output,
        @RELATIONCONSTITUENCYTEXT = @RELATIONCONSTITUENCYTEXT output,
        @STAFFCONSTITUENCYTEXT = @STAFFCONSTITUENCYTEXT output,
        @DONORCONSTITUENCYTEXT = @DONORCONSTITUENCYTEXT output,
        @FUNDRAISERCONSTITUENCYTEXT = @FUNDRAISERCONSTITUENCYTEXT output,
        @PROSPECTCONSTITUENCYTEXT = @PROSPECTCONSTITUENCYTEXT output,
        @VOLUNTEERCONSTITUENCYTEXT = @VOLUNTEERCONSTITUENCYTEXT output,
        @COMMUNITYMEMBERCONSTITUENCYTEXT = @COMMUNITYMEMBERCONSTITUENCYTEXT output,
        @USERDEFINEDCONSTITUENCYTEXT = @USERDEFINEDCONSTITUENCYTEXT output,
        @ALUMNUSSTATUSTEXT = @ALUMNUSSTATUSTEXT output,
        @REGISTRANTSTATUSTEXT = @REGISTRANTSTATUSTEXT output,
        @VENDORSTATUSTEXT = @VENDORSTATUSTEXT output,
        @MATCHFINDERCONSTITUENCYTEXT = @MATCHFINDERCONSTITUENCYTEXT output,
        @HOUSEHOLDTEXT = @HOUSEHOLDTEXT output,
        @MEMBERCONSTITUENCYTEXT = @MEMBERCONSTITUENCYTEXT output,
        @RECOGNITIONCONSTITUENCYTEXT = @RECOGNITIONCONSTITUENCYTEXT output,
        @PLANNEDGIVERCONSTITUENCYTEXT= @PLANNEDGIVERCONSTITUENCYTEXT output,
        @BANKCONSTITUENCYTEXT = @BANKCONSTITUENCYTEXT output,
        @DECEASEDDATE = @DECEASEDDATE output,
        @WEALTHPOINTUPDATEPENDING = @WEALTHPOINTUPDATEPENDING output,
        @EDUCATIONATTRIBUTEDEFINED = @EDUCATIONATTRIBUTEDEFINED output,
        @MATCHFINDERONLINERECORDID = @MATCHFINDERONLINERECORDID output,
        @SOLICITCODECOUNT = @SOLICITCODECOUNT output,
        @HOUSEHOLDID = @HOUSEHOLDID output,
        @ISGROUP = @ISGROUP output,
        @GROUPTYPE = @GROUPTYPE output,
        @GROUPMEMBERCOUNT = @GROUPMEMBERCOUNT output,
        @ISHOUSEHOLD = @ISHOUSEHOLD output,
        @GIVESANONYMOUSLY = @GIVESANONYMOUSLY output,
        @ISSPOUSEDECEASED = @ISSPOUSEDECEASED output,
        @ISDISSOLVED = @ISDISSOLVED output,
        @PATRONCONSTITUENCYTEXT = @PATRONCONSTITUENCYTEXT output,
        @GRANTORCONSTITUENCYTEXT = @GRANTORCONSTITUENCYTEXT output,
        @SPONSORCONSTITUENCYTEXT = @SPONSORCONSTITUENCYTEXT output,
        @COMMITTEEMEMBERCONSTITUENCYTEXT = @COMMITTEEMEMBERCONSTITUENCYTEXT output,
        @CONSTITUENTINACTIVITYREASON = @CONSTITUENTINACTIVITYREASON output,
        @PHONEISCONFIDENTIAL = @PHONEISCONFIDENTIAL output,
        @ADDRESSISCONFIDENTIAL = @ADDRESSISCONFIDENTIAL output, 
        @ADDRESSID = @ADDRESSID output,
        @PHONENUMBERID = @PHONENUMBERID output,
        @EMAILADDRESSID = @EMAILADDRESSID output,
        @SOCIALMEDIAACCOUNTS = @SOCIALMEDIAACCOUNTS output

        begin try
            set    @BANKCURRENTBALANCE = dbo.UFN_BANK_GETCURRENTBALANCE(@ID);
            set @BANKENDINGBALANCE = dbo.UFN_BANK_GETENDINGBALANCE(@ID);
            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 (select count(T.TRANSACTIONCURRENCYID) from (select TRANSACTIONCURRENCYID from dbo.BANKACCOUNT where BANKID = @ID group by TRANSACTIONCURRENCYID) as T) = 1
                select @TRANSACTIONCURRENCYID = TRANSACTIONCURRENCYID from dbo.BANKACCOUNT where BANKID = @ID group by TRANSACTIONCURRENCYID;
            else
                set @TRANSACTIONCURRENCYID = NULL;
        end try
        begin catch
        end catch

    return 0;