USP_DATAFORMTEMPLATE_VIEW_DECEASEDOPTIONSPAGEDATA

The load procedure used by the view dataform template "Deceased Options Page Expression 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.
@NAME	nvarchar(400)	INOUT	NAME
@ISREVIEWED	bit	INOUT	ISREVIEWED
@HASSPOUSE	bit	INOUT	HASSPOUSE
@SPOUSEID	uniqueidentifier	INOUT	SPOUSEID
@SPOUSENAME	nvarchar(400)	INOUT	SPOUSENAME
@HASHOUSEHOLD	bit	INOUT	HASHOUSEHOLD
@HOUSEHOLDID	uniqueidentifier	INOUT	HOUSEHOLDID
@HOUSEHOLDNAME	nvarchar(400)	INOUT	HOUSEHOLDNAME
@HOUSEHOLDMEMBERCOUNT	int	INOUT	HOUSEHOLDMEMBERCOUNT
@GROUPCOUNT	int	INOUT	GROUPCOUNT
@ISPROSPECT	bit	INOUT	ISPROSPECT
@ISFORMERPROSPECT	bit	INOUT	ISFORMERPROSPECT
@ISFUNDRAISER	bit	INOUT	ISFUNDRAISER
@ISFORMERFUNDRAISER	bit	INOUT	ISFORMERFUNDRAISER
@ISVOLUNTEER	bit	INOUT	ISVOLUNTEER
@ISFORMERVOLUNTEER	bit	INOUT	ISFORMERVOLUNTEER
@ISCOMMUNITYMEMBER	bit	INOUT	ISCOMMUNITYMEMBER
@ISREGISTRANT	bit	INOUT	ISREGISTRANT
@ISVENDOR	bit	INOUT	ISVENDOR
@APPUSERID	uniqueidentifier	INOUT	APPUSERID
@ISALUMNUS	bit	INOUT	ISALUMNUS
@ISSTUDENT	bit	INOUT	ISSTUDENT
@ISFORMERSTUDENT	bit	INOUT	ISFORMERSTUDENT
@HASCOMMUNITYDATA	bit	INOUT	HASCOMMUNITYDATA
@SHOWCONSTITUENCIES	bit	INOUT	SHOWCONSTITUENCIES
@SHOWRELATIONSHIPS	bit	INOUT	SHOWRELATIONSHIPS
@SHOWDOCUMENTATION	bit	INOUT	SHOWDOCUMENTATION
@SHOWCONSTITUENTINTERACTIONS	bit	INOUT	SHOWCONSTITUENTINTERACTIONS
@SHOWCONSTITUENTNAMEFORMATS	bit	INOUT	SHOWCONSTITUENTNAMEFORMATS
@SHOWTRIBUTE	bit	INOUT	SHOWTRIBUTE
@SHOWHOUSEHOLDMEMBERS	bit	INOUT	SHOWHOUSEHOLDMEMBERS
@SHOWHOUSEHOLDINTERACTIONS	bit	INOUT	SHOWHOUSEHOLDINTERACTIONS
@SHOWHOUSEHOLDNAMEFORMATS	bit	INOUT	SHOWHOUSEHOLDNAMEFORMATS
@SHOWGROUPS	bit	INOUT	SHOWGROUPS
@ISSPONSOR	bit	INOUT	ISSPONSOR
@SPONSORTYPECODE	tinyint	INOUT	SPONSORTYPECODE
@ISFACULTY	bit	INOUT	ISFACULTY
@ISFORMERSPONSOR	bit	INOUT	ISFORMERSPONSOR
@ISGROUPMEMBER	bit	INOUT
@ISPLANNEDGIVER	bit	INOUT
@ISCOMMITTEEMEMBER	bit	INOUT

Definition

Copy


CREATE procedure dbo.USP_DATAFORMTEMPLATE_VIEW_DECEASEDOPTIONSPAGEDATA
(
  @ID uniqueidentifier,
  @DATALOADED bit = 0 output,
  @NAME nvarchar(400) = null output,
  @ISREVIEWED bit = null output,
  @HASSPOUSE bit = null output,
  @SPOUSEID uniqueidentifier = null output,
  @SPOUSENAME nvarchar(400) = null output,
  @HASHOUSEHOLD bit = null output,
  @HOUSEHOLDID uniqueidentifier = null output,
  @HOUSEHOLDNAME nvarchar(400) = null output,
  @HOUSEHOLDMEMBERCOUNT int = null output,
  @GROUPCOUNT int = null output,
  @ISPROSPECT bit = null output,
  @ISFORMERPROSPECT bit = null output,
  @ISFUNDRAISER bit = null output,
  @ISFORMERFUNDRAISER bit = null output,
  @ISVOLUNTEER bit = null output,
  @ISFORMERVOLUNTEER bit = null output,
  @ISCOMMUNITYMEMBER bit = null output,
  @ISREGISTRANT bit = null output,
  @ISVENDOR bit = null output,
  @APPUSERID uniqueidentifier = null output,
  @ISALUMNUS bit = null output,
  @ISSTUDENT bit = null output,
  @ISFORMERSTUDENT bit = null output,
  @HASCOMMUNITYDATA bit = null output,
  @SHOWCONSTITUENCIES bit = null output,
  @SHOWRELATIONSHIPS bit = null output,
  @SHOWDOCUMENTATION bit = null output,
  @SHOWCONSTITUENTINTERACTIONS bit = null output,
  @SHOWCONSTITUENTNAMEFORMATS bit = null output,
  @SHOWTRIBUTE bit = null output,
  @SHOWHOUSEHOLDMEMBERS bit = null output,
  @SHOWHOUSEHOLDINTERACTIONS bit = null output,
  @SHOWHOUSEHOLDNAMEFORMATS bit = null output,
  @SHOWGROUPS bit = null output,
  @ISSPONSOR bit = null output,
  @SPONSORTYPECODE tinyint = null output,
  @ISFACULTY bit = null output,
  @ISFORMERSPONSOR bit = null output,
  @ISGROUPMEMBER bit = null output,
  @ISPLANNEDGIVER bit = null output,
  @ISCOMMITTEEMEMBER bit = null output
)
as
set nocount on;

set @DATALOADED = 0;

declare @CURRENTDATE datetime;
set @CURRENTDATE = dbo.UFN_DATE_GETEARLIESTTIME(getdate());

select
  @NAME = NF.NAME,
  @ISREVIEWED = DECEASEDCONSTITUENT.ISREVIEWED,
  @HASSPOUSE = case when RELATIONSHIP.ID is not null then 1 else 0 end,
  @SPOUSEID = RELATIONSHIP.RECIPROCALCONSTITUENTID,
  @SPOUSENAME = NF_SPOUSE.NAME,
  @ISPROSPECT = dbo.UFN_CONSTITUENT_ISPROSPECT(@ID),
  @ISFORMERPROSPECT = dbo.UFN_CONSTITUENT_ISFORMERPROSPECT(@ID),
  @ISFUNDRAISER = dbo.UFN_CONSTITUENT_ISFUNDRAISER(@ID),
  @ISFORMERFUNDRAISER = dbo.UFN_CONSTITUENT_ISFORMERFUNDRAISER(@ID),
  @ISVOLUNTEER = dbo.UFN_CONSTITUENT_ISVOLUNTEER(@ID),
  @ISFORMERVOLUNTEER = dbo.UFN_CONSTITUENT_ISFORMERVOLUNTEER(@ID),
  @ISCOMMUNITYMEMBER = CONSTIT.NETCOMMUNITYMEMBER,
  @ISREGISTRANT = dbo.UFN_CONSTITUENT_ISREGISTRANT(@ID),
  @ISVENDOR = dbo.UFN_CONSTITUENT_ISVENDOR(@ID),
  @HASCOMMUNITYDATA = dbo.UFN_CONSTITUENT_HASCOMMUNITYDATA(@ID),
  @ISALUMNUS = dbo.UFN_CONSTITUENT_ISALUMNUS(@ID),
  @ISSTUDENT = dbo.UFN_CONSTITUENT_ISSTUDENT(@ID),
  @ISFORMERSTUDENT = dbo.UFN_CONSTITUENT_ISFORMERSTUDENT(@ID),
  @ISFACULTY = dbo.UFN_CONSTITUENT_ISFACULTY(@ID),
  @ISPLANNEDGIVER = dbo.UFN_CONSTITUENT_ISPLANNEDGIVER(@ID),
  @DATALOADED = 1
from
  dbo.CONSTITUENT CONSTIT
left join
  dbo.RELATIONSHIP
on
  RELATIONSHIP.RELATIONSHIPCONSTITUENTID = CONSTIT.ID and RELATIONSHIP.ISSPOUSE = 1
left join
  dbo.CONSTITUENT SPOUSE
on
  RELATIONSHIP.RECIPROCALCONSTITUENTID = SPOUSE.ID
inner join
  dbo.DECEASEDCONSTITUENT
on
  DECEASEDCONSTITUENT.ID = CONSTIT.ID
outer apply dbo.UFN_CONSTITUENT_DISPLAYNAME(CONSTIT.ID) NF
outer apply dbo.UFN_CONSTITUENT_DISPLAYNAME(SPOUSE.ID) NF_SPOUSE
where 
  CONSTIT.ID = @ID

  exec dbo.USP_SPONSOR_GETINFORMATION
  @ID,
  @ISSPONSOR output,
  @ISFORMERSPONSOR output,
  @SPONSORTYPECODE output

--Deceasing options
select
  @SHOWCONSTITUENCIES = UPDATECONSTITUENCIES,
  @SHOWRELATIONSHIPS = UPDATERELATIONSHIPS,
  @SHOWDOCUMENTATION = ADDDOCUMENTATION,
  @SHOWCONSTITUENTINTERACTIONS = UPDATECONSTITUENTINTERACTIONS,
  @SHOWCONSTITUENTNAMEFORMATS = UPDATECONSTITUENTNAMEFORMATS,
  @SHOWTRIBUTE = ADDTRIBUTE,
  @SHOWHOUSEHOLDMEMBERS = UPDATEHOUSEHOLDMEMBERS,
  @SHOWHOUSEHOLDINTERACTIONS = UPDATEHOUSEHOLDINTERACTIONS,
  @SHOWHOUSEHOLDNAMEFORMATS = UPDATEHOUSEHOLDNAMEFORMATS,
  @SHOWGROUPS = SHOWGROUPS
from
  dbo.UFN_DECEASINGOPTION_GETRULES() 

--Household fields
select
  @HASHOUSEHOLD = case when CONSTITUENT.ID is not null then 1 else 0 end,
  @HOUSEHOLDID = coalesce(CONSTITUENT.ID, @ID),
  @HOUSEHOLDNAME = CONSTITUENT.NAME
from
  dbo.GROUPMEMBER
inner join
  dbo.GROUPDATA
on
  GROUPDATA.ID = GROUPMEMBER.GROUPID and GROUPDATA.GROUPTYPECODE = 0
left join
  dbo.CONSTITUENT
on
  CONSTITUENT.ID = GROUPMEMBER.GROUPID
left join 
  dbo.GROUPMEMBERDATERANGE as GMDR 
on 
  GROUPMEMBER.ID = GMDR.GROUPMEMBERID
where 
  GROUPMEMBER.MEMBERID = @ID
and
  ((GMDR.DATEFROM is null and (GMDR.DATETO is null or GMDR.DATETO > @CURRENTDATE))
  or 
  (GMDR.DATETO is null and (GMDR.DATEFROM is null or GMDR.DATEFROM <= @CURRENTDATE)) 
  or 
  (GMDR.DATEFROM <= @CURRENTDATE and GMDR.DATETO > @CURRENTDATE))

if @@rowcount = 0
  begin
    set @HASHOUSEHOLD = 0;
    --This is for page display - if the system has show household marked, we still go
    --to deceasing options page, and want to show that there are no households (@ID is an individual
    --so it should return 0 results in the household member datalist)
    set @HOUSEHOLDID = @ID;
  end

--Household member count
select 
  @HOUSEHOLDMEMBERCOUNT = count(GM.ID) 
from 
  dbo.GROUPMEMBER as GM
left join 
  dbo.GROUPMEMBERDATERANGE as GMDR 
on 
  GM.ID = GMDR.GROUPMEMBERID
where 
  ((GMDR.DATEFROM is null and (GMDR.DATETO is null or GMDR.DATETO > @CURRENTDATE))
  or 
  (GMDR.DATETO is null and (GMDR.DATEFROM is null or GMDR.DATEFROM <= @CURRENTDATE)) 
  or 
  (GMDR.DATEFROM <= @CURRENTDATE and GMDR.DATETO > @CURRENTDATE))
and 
  GM.GROUPID = @HOUSEHOLDID;

--Group count
select
  @GROUPCOUNT = count(GROUPMEMBER.ID)
from
  dbo.GROUPMEMBER
inner join
  dbo.GROUPDATA
on
  GROUPDATA.ID = GROUPMEMBER.GROUPID and GROUPDATA.GROUPTYPECODE = 1
left join 
  dbo.GROUPMEMBERDATERANGE as GMDR 
on 
  GROUPMEMBER.ID = GMDR.GROUPMEMBERID
where
  ((GMDR.DATEFROM is null and (GMDR.DATETO is null or GMDR.DATETO > @CURRENTDATE))
  or 
  (GMDR.DATETO is null and (GMDR.DATEFROM is null or GMDR.DATEFROM <= @CURRENTDATE)) 
  or 
  (GMDR.DATEFROM <= @CURRENTDATE and GMDR.DATETO > @CURRENTDATE))
and
  GROUPMEMBER.MEMBERID = @ID;

--App user
select @APPUSERID = ID from dbo.APPUSER where CONSTITUENTID = @ID;

set @ISGROUPMEMBER = dbo.UFN_CONSTITUENT_HASGROUPMEMBERRECORD(@ID);

set @ISCOMMITTEEMEMBER = dbo.UFN_CONSTITUENT_ISCOMMITTEEMEMBER(@ID);

return 0;