USP_REPORT_PLANNEDGIFTDETAIL_GROUPGIFTSUM

Returns the sum of planned gift amounts and planned gift remaining values for groups that the constituent has membership in.
Parameters

Parameter	Parameter Type	Mode
@MEMBERID	uniqueidentifier	IN
@SELECTIONID	uniqueidentifier	IN
@FROMDATE	datetime	IN
@TODATE	datetime	IN
@VEHICLECODE	tinyint	IN
@STATUSCODE	tinyint	IN
@DESIGNATIONID	uniqueidentifier	IN
@EXPECTEDMATURITYYEAR	int	IN
@SHOWGROUPDATAFORMEMBERS	bit	IN
@CURRENCYCODE	tinyint	IN
Definition

Copy


CREATE procedure dbo.USP_REPORT_PLANNEDGIFTDETAIL_GROUPGIFTSUM
(
  @MEMBERID uniqueidentifier,
  @SELECTIONID uniqueidentifier = null,
  @FROMDATE datetime = null,
  @TODATE datetime = null,
  @VEHICLECODE tinyint = null,
  @STATUSCODE tinyint = null,
  @DESIGNATIONID uniqueidentifier = null,
  @EXPECTEDMATURITYYEAR int = null,
  @SHOWGROUPDATAFORMEMBERS bit = null,
  @CURRENCYCODE tinyint = 1
)
as
  set nocount on;

  if @SHOWGROUPDATAFORMEMBERS = 1
  begin
    declare @CURRENTDATEEARLIESTTIME datetime
    set @CURRENTDATEEARLIESTTIME = dbo.UFN_DATE_GETEARLIESTTIME(getdate());

    if @SELECTIONID is null
      select
        CDT.CONSTITUENTNAME as CONSTITUENTNAME,
        CDT.GIFTAMOUNTSUM as GIFTAMOUNTSUM,
        CDT.REMAINDERVALUESUM as REMAINDERVALUESUM,
        CINFO.CURRENCYSYMBOL as CURRENCYSYMBOL,
        CINFO.ISO4217 as CURRENCYISOCODE,
        CINFO.DECIMALDIGITS as CURRENCYDECIMALCOUNT,
        CINFO.SYMBOLDISPLAYSETTINGCODE as CURRENCYSYMBOLDISPLAY
      from 
        (select
          NF_C.NAME as CONSTITUENTNAME,
          case
            --when we are dealing with an addition, do not count the planned gift amount

            when exists (select 1 from dbo.PLANNEDGIFTADDITIONDESIGNATION PGAD inner join dbo.PLANNEDGIFTADDITION PGA on PGAD.PLANNEDGIFTADDITIONID = PGA.ID where PGAD.DESIGNATIONID = @DESIGNATIONID and PGA.PLANNEDGIFTID = PG.ID) then 0
            else
              case @CURRENCYCODE
                when 0 then sum(PG.GIFTAMOUNT)
                when 2 then sum(PG.TRANSACTIONGIFTAMOUNT)
                when 1 then sum(PG.ORGANIZATIONGIFTAMOUNT)
              end
          end + coalesce(sum(PGA.PLANNEDGIFTADDITIONAMOUNT), 0) as GIFTAMOUNTSUM,
          case @CURRENCYCODE
            when 0 then sum(PG.REMAINDERVALUE)
            when 2 then sum(PG.TRANSACTIONREMAINDERVALUE)
            when 1 then sum(PG.ORGANIZATIONREMAINDERVALUE)
          end as REMAINDERVALUESUM,
          case @CURRENCYCODE
            when 0 then
              case count(distinct(PG.BASECURRENCYID))
                when 1 then max(cast(PG.BASECURRENCYID as nvarchar(36)))
                else null
              end
            when 2 then
              case count(distinct(PG.TRANSACTIONCURRENCYID))
                when 1 then max(cast(PG.TRANSACTIONCURRENCYID as nvarchar(36)))
                else null
              end
            when 1 then cast(dbo.UFN_CURRENCY_GETORGANIZATIONCURRENCY() as nvarchar(36))
          end as CURRENCYID
        from
          dbo.PLANNEDGIFT PG
        inner join
          dbo.GROUPMEMBER GM on PG.CONSTITUENTID = GM.GROUPID
        inner join
          dbo.GROUPMEMBERDATERANGE GMDR on GM.ID = GMDR.GROUPMEMBERID
        inner join
          dbo.CONSTITUENT C with (nolock) on C.ID = GM.GROUPID
        left join
          (select
            PLANNEDGIFTID,
            case @CURRENCYCODE
              when 0 then sum(GIFTAMOUNT)
              when 2 then sum(TRANSACTIONGIFTAMOUNT)
              when 1 then sum(ORGANIZATIONGIFTAMOUNT)
            end as PLANNEDGIFTADDITIONAMOUNT
          from
            dbo.PLANNEDGIFTADDITION PLGA
              inner join
                dbo.PLANNEDGIFTADDITIONDESIGNATION PGAD on PGAD.PLANNEDGIFTADDITIONID = PLGA.ID
          where
            PGAD.DESIGNATIONID = @DESIGNATIONID
          group by
            PLANNEDGIFTID) PGA on PGA.PLANNEDGIFTID = PG.ID
        outer apply dbo.UFN_CONSTITUENT_DISPLAYNAME(C.ID) NF_C
        where
          GM.MEMBERID = @MEMBERID and
          (PG.GIFTDATE between @FROMDATE and @TODATE or @FROMDATE is null or @TODATE is null) and
          (PG.VEHICLECODE = @VEHICLECODE or @VEHICLECODE is null) and
          (PG.STATUSCODE = @STATUSCODE or @STATUSCODE is null) and
          (@DESIGNATIONID is null or exists (select 1 from dbo.PLANNEDGIFTDESIGNATION PGD where PGD.PLANNEDGIFTID = PG.ID and PGD.DESIGNATIONID = @DESIGNATIONID) or
                                  exists (select 1 from dbo.PLANNEDGIFTADDITIONDESIGNATION PGAD inner join dbo.PLANNEDGIFTADDITION PGA on PGAD.PLANNEDGIFTADDITIONID = PGA.ID where PGAD.DESIGNATIONID = @DESIGNATIONID and PGA.PLANNEDGIFTID = PG.ID)) and
          (PG.EXPECTEDMATURITY = @EXPECTEDMATURITYYEAR or @EXPECTEDMATURITYYEAR is null) and
          ((GMDR.DATEFROM is null and (GMDR.DATETO is null or GMDR.DATETO > @CURRENTDATEEARLIESTTIME))
            or (GMDR.DATETO is null and (GMDR.DATEFROM is null or GMDR.DATEFROM <= @CURRENTDATEEARLIESTTIME)) 
            or (GMDR.DATEFROM <= @CURRENTDATEEARLIESTTIME and GMDR.DATETO > @CURRENTDATEEARLIESTTIME))
        group by
          C.ID, NF_C.NAME, PG.ID
        ) as CDT
      outer apply
        dbo.UFN_CURRENCY_GETPROPERTIES(cast(CDT.CURRENCYID as uniqueidentifier)) as CINFO

    else
      select
        CDT.CONSTITUENTNAME as CONSTITUENTNAME,
        CDT.GIFTAMOUNTSUM as GIFTAMOUNTSUM,
        CDT.REMAINDERVALUESUM as REMAINDERVALUESUM,
        CINFO.CURRENCYSYMBOL as CURRENCYSYMBOL,
        CINFO.ISO4217 as CURRENCYISOCODE,
        CINFO.DECIMALDIGITS as CURRENCYDECIMALCOUNT,
        CINFO.SYMBOLDISPLAYSETTINGCODE as CURRENCYSYMBOLDISPLAY
      from
        (select
          NF_C.NAME as CONSTITUENTNAME,
          case
            --when we are dealing with an addition, do not count the planned gift amount

            when exists (select 1 from dbo.PLANNEDGIFTADDITIONDESIGNATION PGAD inner join dbo.PLANNEDGIFTADDITION PGA on PGAD.PLANNEDGIFTADDITIONID = PGA.ID where PGAD.DESIGNATIONID = @DESIGNATIONID and PGA.PLANNEDGIFTID = PG.ID) then 0
            else
              case @CURRENCYCODE
                when 0 then sum(PG.GIFTAMOUNT)
                when 2 then sum(PG.TRANSACTIONGIFTAMOUNT)
                when 1 then sum(PG.ORGANIZATIONGIFTAMOUNT)
              end
          end + coalesce(sum(PGA.PLANNEDGIFTADDITIONAMOUNT), 0) as GIFTAMOUNTSUM,
          case @CURRENCYCODE
            when 0 then sum(PG.REMAINDERVALUE)
            when 2 then sum(PG.TRANSACTIONREMAINDERVALUE)
            when 1 then sum(PG.ORGANIZATIONREMAINDERVALUE)
          end as REMAINDERVALUESUM,
          case @CURRENCYCODE
            when 0 then
              case count(distinct(PG.BASECURRENCYID))
                when 1 then max(cast(PG.BASECURRENCYID as nvarchar(36)))
                else null
              end
            when 2 then
              case count(distinct(PG.TRANSACTIONCURRENCYID))
                when 1 then max(cast(PG.TRANSACTIONCURRENCYID as nvarchar(36)))
                else null
              end
            when 1 then cast(dbo.UFN_CURRENCY_GETORGANIZATIONCURRENCY() as nvarchar(36))
          end as CURRENCYID
        from
          dbo.PLANNEDGIFT PG
        inner join
          dbo.GROUPMEMBER GM on PG.CONSTITUENTID = GM.GROUPID
        inner join
          dbo.GROUPMEMBERDATERANGE GMDR on GM.ID = GMDR.GROUPMEMBERID
        inner join
          dbo.CONSTITUENT C with (nolock) on C.ID = GM.GROUPID
        inner join
          dbo.UFN_IDSETREADER_GETRESULTS_GUID(@SELECTIONID) SELECTION on PG.ID = SELECTION.ID
        left join
          (select
            PLANNEDGIFTID,
            case @CURRENCYCODE
              when 0 then sum(GIFTAMOUNT)
              when 2 then sum(TRANSACTIONGIFTAMOUNT)
              when 1 then sum(ORGANIZATIONGIFTAMOUNT)
            end as PLANNEDGIFTADDITIONAMOUNT
          from
            dbo.PLANNEDGIFTADDITION PLGA
              inner join
                dbo.PLANNEDGIFTADDITIONDESIGNATION PGAD on PGAD.PLANNEDGIFTADDITIONID = PLGA.ID
          where
            PGAD.DESIGNATIONID = @DESIGNATIONID
          group by
            PLANNEDGIFTID) PGA on PGA.PLANNEDGIFTID = PG.ID
        outer apply dbo.UFN_CONSTITUENT_DISPLAYNAME(C.ID) NF_C
        where
          GM.MEMBERID = @MEMBERID and
          (PG.GIFTDATE between @FROMDATE and @TODATE or @FROMDATE is null or @TODATE is null) and
          (PG.VEHICLECODE = @VEHICLECODE or @VEHICLECODE is null) and
          (PG.STATUSCODE = @STATUSCODE or @STATUSCODE is null) and
          (@DESIGNATIONID is null or exists (select 1 from dbo.PLANNEDGIFTDESIGNATION PGD where PGD.PLANNEDGIFTID = PG.ID and PGD.DESIGNATIONID = @DESIGNATIONID) or
                                     exists (select 1 from dbo.PLANNEDGIFTADDITIONDESIGNATION PGAD inner join dbo.PLANNEDGIFTADDITION PGA on PGAD.PLANNEDGIFTADDITIONID = PGA.ID where PGAD.DESIGNATIONID = @DESIGNATIONID and PGA.PLANNEDGIFTID = PG.ID)) and
          (PG.EXPECTEDMATURITY = @EXPECTEDMATURITYYEAR or @EXPECTEDMATURITYYEAR is null) and
          ((GMDR.DATEFROM is null and (GMDR.DATETO is null or GMDR.DATETO > @CURRENTDATEEARLIESTTIME))
            or (GMDR.DATETO is null and (GMDR.DATEFROM is null or GMDR.DATEFROM <= @CURRENTDATEEARLIESTTIME)) 
            or (GMDR.DATEFROM <= @CURRENTDATEEARLIESTTIME and GMDR.DATETO > @CURRENTDATEEARLIESTTIME))
        group by
          C.ID, NF_C.NAME, PG.ID
        ) as CDT
        outer apply
          dbo.UFN_CURRENCY_GETPROPERTIES(cast(CDT.CURRENCYID as uniqueidentifier)) as CINFO
    end