USP_DATAFORMTEMPLATE_VIEW_ADVANCESALESPATRONDETAILS
The load procedure used by the view dataform template "Advance Sales Patron Details View Data 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. |
| @CURRENTAPPUSERID | uniqueidentifier | IN | Input parameter indicating the ID of the current user. |
| @PLANNEDGIVERCONSTITUENCYTEXT | nvarchar(100) | INOUT | PLANNEDGIVERCONSTITUENCYTEXT |
| @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 |
| @MEMBERCONSTITUENCYTEXT | nvarchar(100) | INOUT | MEMBERCONSTITUENCYTEXT |
| @RECOGNITIONCONSTITUENCYTEXT | nvarchar(100) | INOUT | RECOGNITIONCONSTITUENCYTEXT |
| @USERDEFINEDCONSTITUENCYTEXT | nvarchar(4000) | INOUT | USERDEFINEDCONSTITUENCYTEXT |
| @ALUMNUSSTATUSTEXT | nvarchar(100) | INOUT | ALUMNUSSTATUSTEXT |
| @REGISTRANTSTATUSTEXT | nvarchar(100) | INOUT | REGISTRANTSTATUSTEXT |
| @VENDORSTATUSTEXT | nvarchar(100) | INOUT | VENDORSTATUSTEXT |
| @MATCHFINDERONLINERECORDID | int | INOUT | MATCHFINDERONLINERECORDID |
| @MATCHFINDERCONSTITUENCYTEXT | nvarchar(100) | INOUT | MATCHFINDERCONSTITUENCYTEXT |
| @BANKCONSTITUENCYTEXT | nvarchar(100) | INOUT | BANKCONSTITUENCYTEXT |
| @PATRONCONSTITUENCYTEXT | nvarchar(100) | INOUT | PATRONCONSTITUENCYTEXT |
| @LOYALDONORCONSTITUENCYTEXT | nvarchar(100) | INOUT | LOYALDONORCONSTITUENCYTEXT |
| @MAJORDONORCONSTITUENCYTEXT | nvarchar(100) | INOUT | MAJORDONORCONSTITUENCYTEXT |
| @USERDEFINEDCONSTITUENCIES | xml | INOUT | USERDEFINEDCONSTITUENCIES |
| @CONSTITUENTISORGANIZATION | bit | INOUT | CONSTITUENTISORGANIZATION |
| @CONSTITUENTISGROUP | bit | INOUT | CONSTITUENTISGROUP |
| @CONSTITUENTISHOUSEHOLD | bit | INOUT | CONSTITUENTISHOUSEHOLD |
| @ADDRESS | nvarchar(300) | INOUT | ADDRESS |
| @PHONENUMBER | nvarchar(100) | INOUT | PHONENUMBER |
| @EMAILADDRESS | UDT_EMAILADDRESS | INOUT | EMAILADDRESS |
| @MEMBERSHIPS | xml | INOUT | MEMBERSHIPS |
Definition
Copy
CREATE procedure dbo.USP_DATAFORMTEMPLATE_VIEW_ADVANCESALESPATRONDETAILS
(
@ID uniqueidentifier,
@DATALOADED bit = 0 output,
@CURRENTAPPUSERID uniqueidentifier,
@PLANNEDGIVERCONSTITUENCYTEXT nvarchar(100)=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,
@MEMBERCONSTITUENCYTEXT nvarchar(100)= null output,
@RECOGNITIONCONSTITUENCYTEXT nvarchar(100)= null output,
@USERDEFINEDCONSTITUENCYTEXT nvarchar(4000) = null output,
@ALUMNUSSTATUSTEXT nvarchar(100) = null output,
@REGISTRANTSTATUSTEXT nvarchar(100) = null output,
@VENDORSTATUSTEXT nvarchar(100) = null output,
@MATCHFINDERONLINERECORDID int = null output,
@MATCHFINDERCONSTITUENCYTEXT nvarchar(100) = null output,
@BANKCONSTITUENCYTEXT nvarchar(100) = null output,
@PATRONCONSTITUENCYTEXT nvarchar(100) = null output,
@LOYALDONORCONSTITUENCYTEXT nvarchar(100) = null output,
@MAJORDONORCONSTITUENCYTEXT nvarchar(100) = null output,
@USERDEFINEDCONSTITUENCIES xml = null output,
@CONSTITUENTISORGANIZATION bit = null output,
@CONSTITUENTISGROUP bit = null output,
@CONSTITUENTISHOUSEHOLD bit = null output,
@ADDRESS nvarchar(300) = null output,
@PHONENUMBER nvarchar(100) = null output,
@EMAILADDRESS dbo.UDT_EMAILADDRESS = null output,
@MEMBERSHIPS xml = null output
)
as
set nocount on;
set @DATALOADED = 0;
declare @CURRENTDATE datetime = getdate();
declare @MAXDATETIME datetime = cast('12/31/9999' as datetime);
select @EMAILADDRESS = EMAILADDRESS
from dbo.EMAILADDRESS
where CONSTITUENTID = @ID and
ISPRIMARY = 1;
--PHONE
select @PHONENUMBER = PHONE.NUMBER
from dbo.PHONE
where PHONE.CONSTITUENTID = @ID and
PHONE.ISPRIMARY = 1;
select
@ADDRESS = dbo.UFN_BUILDFULLADDRESS(ID, ADDRESSBLOCK, CITY, STATEID, POSTCODE, COUNTRYID)
from dbo.ADDRESS
where CONSTITUENTID = @ID and
ISPRIMARY = 1;
--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 @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 @USERDEFINEDCONSTITUENCIES = dbo.UFN_CONSTITUENT_GETCONSTITUENCIES_USERDEFINED_TOITEMLISTXML(@ID, @CURRENTDATE);
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 @PATRONCONSTITUENCYTEXT = case
when dbo.UFN_CONSTITUENT_ISPATRON(@ID) = 1 then dbo.UFN_CONSTITUENCY_GETDESCRIPTION('A843B859-4C6B-445B-97F3-179582E270A5') --Patron
else null end
exec dbo.USP_DATAFORMTEMPLATE_VIEW_DAILYSALESPATRONMEMBERSHIPS @ID, @MEMBERSHIPS = @MEMBERSHIPS output;
select
@DATALOADED = 1,
@CONSTITUENTISORGANIZATION = ISORGANIZATION,
@CONSTITUENTISGROUP = ISGROUP,
@CONSTITUENTISHOUSEHOLD = dbo.UFN_CONSTITUENT_ISHOUSEHOLD(ID)
from
dbo.CONSTITUENT
where
ID = @ID
return 0;