USP_DATALIST_STEWARDSHIPPLAN_BENEFITS

List of all appeal, membership program level, and recognition program level benefits that this constituent has received.
Parameters

Parameter	Parameter Type	Mode	Description
@STEWARDSHIPPLANID	uniqueidentifier	IN	Input parameter indicating the context ID for the data list.
Definition

Copy


CREATE procedure dbo.USP_DATALIST_STEWARDSHIPPLAN_BENEFITS
(
    @STEWARDSHIPPLANID uniqueidentifier
) as
begin
    set nocount on;

    declare @CONSTITUENTID uniqueidentifier;
    select @CONSTITUENTID = CONSTITUENTID from dbo.STEWARDSHIPPLAN where STEWARDSHIPPLAN.ID = @STEWARDSHIPPLANID;

    select
      BENEFIT.ID,
      CONSTITUENTBENEFITS.TYPE,
      BENEFIT.NAME,
      CONSTITUENTBENEFITS.QUANTITY,
      CONSTITUENTBENEFITS.UNITVALUE,
      CONSTITUENTBENEFITS.TOTALVALUE,
      CONSTITUENTBENEFITS.DETAILS,
      CONSTITUENTBENEFITS.VALUEPERCENT,
      CONSTITUENTBENEFITS.BASECURRENCYID
    from
    (
      select
        REVENUEBENEFIT.BENEFITID,
        'Revenue Benefit' as TYPE,
        REVENUEBENEFIT.QUANTITY,
        REVENUEBENEFIT.UNITVALUE,
        REVENUEBENEFIT.TOTALVALUE,
        REVENUEBENEFIT.DETAILS,
        REVENUEBENEFIT.VALUEPERCENT,
        REVENUEBENEFIT.BASECURRENCYID
      from
        dbo.REVENUE
      inner join
        dbo.REVENUEBENEFIT on REVENUEBENEFIT.REVENUEID = REVENUE.ID
      where
        REVENUE.CONSTITUENTID = @CONSTITUENTID

      union all

      select
        REGISTRANTBENEFIT.BENEFITID,
        'Event Registrant Benefit' as TYPE,
        REGISTRANTBENEFIT.QUANTITY,
        REGISTRANTBENEFIT.UNITVALUE,
        REGISTRANTBENEFIT.TOTALVALUE,
        REGISTRANTBENEFIT.DETAILS,
        0 as VALUEPERCENT,
        REGISTRANTBENEFIT.BASECURRENCYID
      from
        dbo.REGISTRANT
      inner join
        dbo.REGISTRANTBENEFIT on REGISTRANTBENEFIT.REGISTRANTID = REGISTRANT.ID
      where
        REGISTRANT.CONSTITUENTID = @CONSTITUENTID

      union all

      select
        CONSTITUENTRECOGNITIONBENEFIT.BENEFITID,
        'Constituent Recognition Benefit' as TYPE,
        CONSTITUENTRECOGNITIONBENEFIT.QUANTITY,
        CONSTITUENTRECOGNITIONBENEFIT.UNITVALUE,
        (CONSTITUENTRECOGNITIONBENEFIT.QUANTITY * CONSTITUENTRECOGNITIONBENEFIT.UNITVALUE) as TOTALVALUE,
        CONSTITUENTRECOGNITIONBENEFIT.DETAILS,
        0 as VALUEPERCENT,
        null as BASECURRENCYID
      from
        dbo.CONSTITUENTRECOGNITION
      inner join
        dbo.CONSTITUENTRECOGNITIONBENEFIT on CONSTITUENTRECOGNITIONBENEFIT.CONSTITUENTRECOGNITIONID = CONSTITUENTRECOGNITION.ID
      where
        CONSTITUENTRECOGNITION.CONSTITUENTID = @CONSTITUENTID

    ) as CONSTITUENTBENEFITS
    inner join
      dbo.BENEFIT on BENEFIT.ID = CONSTITUENTBENEFITS.BENEFITID
    order by
      CONSTITUENTBENEFITS.TYPE, BENEFIT.NAME

end