USP_DATALIST_CONSTITUENTRECOGNITIONDEFAULTSBYRECIPIENT
Display a list of the recognition defaults where the constituent is the recipient.
Parameters
| Parameter | Parameter Type | Mode | Description |
|---|---|---|---|
| @CONSTITUENTID | uniqueidentifier | IN | Input parameter indicating the context ID for the data list. |
Definition
Copy
CREATE procedure dbo.USP_DATALIST_CONSTITUENTRECOGNITIONDEFAULTSBYRECIPIENT
(
@CONSTITUENTID uniqueidentifier
)
as
set nocount on
declare @recognitiondefaults table
(
ID uniqueidentifier default '00000000-0000-0000-0000-000000000000',
SOURCECONSTITUENTID uniqueidentifier,
MATCHFACTOR decimal(5, 2),
REVENUERECOGNITIONTYPECODEID uniqueidentifier,
HOUSEHOLDSETTINGSGENERATED bit default 0,
STARTDATE datetime,
ENDDATE datetime,
PREVENTRECOGNITIONSDEFAULTING bit default 0
)
declare @CURRENTDATEEARLIEST datetime
set @CURRENTDATEEARLIEST = dbo.UFN_DATE_GETEARLIESTTIME(getdate())
declare @MEMBERREVENUERECOGNITIONTYPECODEID uniqueidentifier;
declare @HOUSEHOLDREVENUERECOGNITIONTYPECODEID uniqueidentifier;
-- Determine the constituent's household, if they have one
declare @MEMBERSHOUSEHOLDID uniqueidentifier
select
@MEMBERSHOUSEHOLDID = GM.GROUPID
from dbo.GROUPMEMBER GM
inner join dbo.GROUPDATA GD on GM.GROUPID = GD.ID
left join dbo.GROUPMEMBERDATERANGE GMDR on GM.ID = GMDR.GROUPMEMBERID
where
GM.MEMBERID = @CONSTITUENTID and
GD.GROUPTYPECODE = 0 and -- Indicates household
-- Verify they are still active
((GMDR.DATEFROM is null and (GMDR.DATETO is null or GMDR.DATETO > @CURRENTDATEEARLIEST))
or (GMDR.DATETO is null and (GMDR.DATEFROM is null or GMDR.DATEFROM <= @CURRENTDATEEARLIEST))
or (GMDR.DATEFROM <= @CURRENTDATEEARLIEST and GMDR.DATETO > @CURRENTDATEEARLIEST))
-- Handle household members
if @MEMBERSHOUSEHOLDID is not null
begin
declare @MEMBERRECOGNIZEOTHERMEMBERSCODE tinyint, @HOUSEHOLDRECOGNIZEMEMBERSCODE tinyint
select
@MEMBERRECOGNIZEOTHERMEMBERSCODE = MEMBERRECOGNIZEOTHERMEMBERSCODE,
@HOUSEHOLDRECOGNIZEMEMBERSCODE = HOUSEHOLDRECOGNIZEMEMBERSCODE,
@MEMBERREVENUERECOGNITIONTYPECODEID = MEMBERREVENUERECOGNITIONTYPECODEID,
@HOUSEHOLDREVENUERECOGNITIONTYPECODEID = HOUSEHOLDREVENUERECOGNITIONTYPECODEID
from dbo.UFN_HOUSEHOLD_GETRECOGNITIONSETTINGS(@MEMBERSHOUSEHOLDID)
if @MEMBERRECOGNIZEOTHERMEMBERSCODE = 1 -- All other members
insert into @recognitiondefaults
(
SOURCECONSTITUENTID,
HOUSEHOLDSETTINGSGENERATED,
REVENUERECOGNITIONTYPECODEID,
MATCHFACTOR
)
select
MEMBERID,
1,
@MEMBERREVENUERECOGNITIONTYPECODEID,
100
from dbo.GROUPMEMBER GM
left join dbo.GROUPMEMBERDATERANGE GMDR on GM.ID = GMDR.GROUPMEMBERID
where
GROUPID = @MEMBERSHOUSEHOLDID and
MEMBERID <> @CONSTITUENTID and
-- Verify included members are still active
((GMDR.DATEFROM is null and (GMDR.DATETO is null or GMDR.DATETO > @CURRENTDATEEARLIEST))
or (GMDR.DATETO is null and (GMDR.DATEFROM is null or GMDR.DATEFROM <= @CURRENTDATEEARLIEST))
or (GMDR.DATEFROM <= @CURRENTDATEEARLIEST and GMDR.DATETO > @CURRENTDATEEARLIEST))
if @HOUSEHOLDRECOGNIZEMEMBERSCODE = 1 -- All members
insert into @recognitiondefaults
(
SOURCECONSTITUENTID,
HOUSEHOLDSETTINGSGENERATED,
REVENUERECOGNITIONTYPECODEID,
MATCHFACTOR
)
select
@MEMBERSHOUSEHOLDID,
1,
@HOUSEHOLDREVENUERECOGNITIONTYPECODEID,
100
-- @MEMBERRECOGNIZEOTHERMEMBERSCODE = 2 and @HOUSEHOLDRECOGNIZEMEMBERSCODE = 2 will be handled later,
-- when the insert from REVENUERECOGNITIONDEFAULT occurs
end
else
begin
declare @ISHOUSEHOLD bit
-- Determine if the constituent is a household
select
@ISHOUSEHOLD = case when GD.GROUPTYPECODE = 0 then 1 else 0 end
from dbo.GROUPDATA GD
where
GD.ID = @CONSTITUENTID
-- Handle households
if @ISHOUSEHOLD = 1
begin
-- Determine the member recognize household setting for the household
declare @MEMBERRECOGNIZEHOUSEHOLD bit;
select
@MEMBERRECOGNIZEHOUSEHOLD = MEMBERRECOGNIZEHOUSEHOLD,
@MEMBERREVENUERECOGNITIONTYPECODEID = MEMBERREVENUERECOGNITIONTYPECODEID
from dbo.UFN_HOUSEHOLD_GETRECOGNITIONSETTINGS(@CONSTITUENTID)
if @MEMBERRECOGNIZEHOUSEHOLD is null
select top 1
@MEMBERRECOGNIZEHOUSEHOLD = MEMBERRECOGNIZEHOUSEHOLD
from dbo.HOUSEHOLDINFO
order by DATEADDED
if @MEMBERRECOGNIZEHOUSEHOLD = 1
insert into @recognitiondefaults
(
SOURCECONSTITUENTID,
HOUSEHOLDSETTINGSGENERATED,
REVENUERECOGNITIONTYPECODEID,
MATCHFACTOR
)
select
GM.MEMBERID,
1,
@MEMBERREVENUERECOGNITIONTYPECODEID,
100
from dbo.GROUPMEMBER GM
inner join dbo.GROUPDATA GD on GM.GROUPID = GD.ID
left join dbo.GROUPMEMBERDATERANGE GMDR on GM.ID = GMDR.GROUPMEMBERID
where
GM.GROUPID = @CONSTITUENTID and
-- Verify they are still active
((GMDR.DATEFROM is null and (GMDR.DATETO is null or GMDR.DATETO > @CURRENTDATEEARLIEST))
or (GMDR.DATETO is null and (GMDR.DATEFROM is null or GMDR.DATEFROM <= @CURRENTDATEEARLIEST))
or (GMDR.DATEFROM <= @CURRENTDATEEARLIEST and GMDR.DATETO > @CURRENTDATEEARLIEST))
end
end
insert into @recognitiondefaults
(
ID,
SOURCECONSTITUENTID,
MATCHFACTOR,
REVENUERECOGNITIONTYPECODEID,
STARTDATE,
ENDDATE,
PREVENTRECOGNITIONSDEFAULTING
)
select
ID,
SOURCECONSTITUENTID,
MATCHFACTOR,
REVENUERECOGNITIONTYPECODEID,
STARTDATE,
ENDDATE,
PREVENTRECOGNITIONSDEFAULTING
from dbo.REVENUERECOGNITIONDEFAULT RM
where
RECIPIENTCONSTITUENTID = @CONSTITUENTID
-- Remove duplicate records. There should only be duplicates
-- if a default was added both by a household setting and an entry
-- in REVENUERECOGNITIONDEFAULT. Priority is given to the record
-- from REVENUERECOGNITIONDEFAULT since that means a user overrode
-- the household setting.
delete @recognitiondefaults
from @recognitiondefaults as BASETABLE
where
BASETABLE.HOUSEHOLDSETTINGSGENERATED = 1 and
exists (
select 1
from @recognitiondefaults SUBTABLE
where
SUBTABLE.HOUSEHOLDSETTINGSGENERATED = 0 and
BASETABLE.SOURCECONSTITUENTID = SUBTABLE.SOURCECONSTITUENTID)
select
RM.ID,
NF.NAME,
RM.MATCHFACTOR,
RT.DESCRIPTION as RECOGNITIONTYPE,
RM.HOUSEHOLDSETTINGSGENERATED,
RM.STARTDATE,
RM.ENDDATE,
C.ID,
RT.ID RECOGNITIONCREDITTYPECODEID
from @recognitiondefaults as RM
cross apply dbo.CONSTITUENT C
outer apply dbo.UFN_CONSTITUENT_DISPLAYNAME(C.ID) NF
left join dbo.REVENUERECOGNITIONTYPECODE RT on RM.REVENUERECOGNITIONTYPECODEID = RT.ID
where
C.ID = RM.SOURCECONSTITUENTID and
PREVENTRECOGNITIONSDEFAULTING = 0
order by C.KEYNAME, C.FIRSTNAME