USP_DATALIST_MEMBERSHIPPROGRAMTRANSACTION

Displays a list of membership transactions and associated programs.
Parameters

Parameter	Parameter Type	Mode	Description
@CONSTITUENTID	uniqueidentifier	IN	Input parameter indicating the context ID for the data list.
@CURRENTAPPUSERID	uniqueidentifier	IN	Input parameter indicating the ID of the current user.
@SITEFILTERMODE	tinyint	IN	Sites
@SITESSELECTED	xml	IN
@SECURITYFEATUREID	uniqueidentifier	IN	Input parameter indicating the ID of the feature to use for site security checking.
@SECURITYFEATURETYPE	tinyint	IN	Input parameter indicating the type of the feature to use for site security checking.
@CURRENCYCODE	tinyint	IN	Currency
Definition

Copy


                CREATE procedure dbo.USP_DATALIST_MEMBERSHIPPROGRAMTRANSACTION
                (
                    @CONSTITUENTID uniqueidentifier,
                    @CURRENTAPPUSERID uniqueidentifier,
                    @SITEFILTERMODE tinyint = 0,
                    @SITESSELECTED xml = null,
                    @SECURITYFEATUREID uniqueidentifier = null,
                    @SECURITYFEATURETYPE tinyint = null,
                    @CURRENCYCODE tinyint = 0
                )
                as
                    set nocount on;

                    declare @SELECTEDCURRENCYID uniqueidentifier;
                    if coalesce(@CURRENCYCODE, 2) = 2
                    begin
                        set @SELECTEDCURRENCYID = dbo.UFN_CURRENCY_GETORGANIZATIONCURRENCY();
                    end

                    select
                        MT.ID,
                        MT.TRANSACTIONDATE,
                        MT.ACTION,
                        case @CURRENCYCODE 
                            when 0 then RS.TRANSACTIONAMOUNT - ( select coalesce(sum(SOIMIP.AMOUNT), 0)
                                      from dbo.SALESORDERITEMMEMBERSHIPITEMPROMOTION SOIMIP
                                          inner join dbo.SALESORDERITEMMEMBERSHIP SOIM on SOIMIP.SALESORDERITEMID = SOIM.ID
                                      where SOIM.MEMBERSHIPTRANSACTIONID = MT.ID ) 
                            when 1 then RS.AMOUNT - ( select coalesce(sum(SOIMIP.AMOUNT), 0)
                                      from dbo.SALESORDERITEMMEMBERSHIPITEMPROMOTION SOIMIP
                                          inner join dbo.SALESORDERITEMMEMBERSHIP SOIM on SOIMIP.SALESORDERITEMID = SOIM.ID
                                      where SOIM.MEMBERSHIPTRANSACTIONID = MT.ID ) 
                            else
                                RS.ORGANIZATIONAMOUNT - ( select coalesce(sum(SOIMIP.ORGANIZATIONAMOUNT), 0)
                                      from dbo.SALESORDERITEMMEMBERSHIPITEMPROMOTION SOIMIP
                                          inner join dbo.SALESORDERITEMMEMBERSHIP SOIM on SOIMIP.SALESORDERITEMID = SOIM.ID
                                      where SOIM.MEMBERSHIPTRANSACTIONID = MT.ID )
                        end as AMOUNT,
                        MP.NAME as PROGRAM,
                        ML.NAME,
                        TERM.TERM,
                        cast(MT.EXPIRATIONDATE as date) EXPIRATIONDATE,
                        TYPE.DESCRIPTION,
                        MT.ISGIFT,
                        GIVENBY_NF.NAME GIVENBYID,
                        RS.ID as REVENUESPLITID,
                        SITE.NAME SITE,
                        case @CURRENCYCODE
                            when 0 then RS.TRANSACTIONCURRENCYID 
                            when 1 then RS.BASECURRENCYID 
                            else @SELECTEDCURRENCYID 
                        end as SELECTEDCURRENCYID,
                        TIER.DESCRIPTION as TIER
                    from dbo.MEMBER
                    inner join dbo.MEMBERSHIP M on MEMBER.MEMBERSHIPID = M.ID
                    inner join dbo.MEMBERSHIPPROGRAM MP on M.MEMBERSHIPPROGRAMID = MP.ID
                    inner join dbo.MEMBERSHIPTRANSACTION MT on M.ID = MT.MEMBERSHIPID
                    inner join dbo.MEMBERSHIPLEVELTERM TERM on TERM.ID = MT.MEMBERSHIPLEVELTERMID
                    inner join dbo.MEMBERSHIPLEVEL ML on ML.ID = MT.MEMBERSHIPLEVELID
                    left join dbo.MEMBERSHIPLEVELTYPECODE TYPE on TYPE.ID = MT.MEMBERSHIPLEVELTYPECODEID
                    left join dbo.REVENUESPLIT RS on MT.REVENUESPLITID = RS.ID
                    left join dbo.SITE on SITE.ID = MP.SITEID
                    left join dbo.TIERCODE TIER on ML.TIERCODEID = TIER.ID
                    outer apply dbo.UFN_CONSTITUENT_DISPLAYNAME(MT.DONORID) GIVENBY_NF
                    where
                        (dbo.UFN_APPUSER_ISSYSADMIN(@CURRENTAPPUSERID) = 1 or exists (select 1 from dbo.UFN_SITESFORUSERONFEATURE(@CURRENTAPPUSERID,@SECURITYFEATUREID,@SECURITYFEATURETYPE) where SITEID=[MP].[SITEID] or (SITEID is null and [MP].[SITEID] is null)))
                        and MEMBER.CONSTITUENTID = @CONSTITUENTID
                        and MEMBER.ISDROPPED = 0
                        and (@SITEFILTERMODE = 0
                            or MP.SITEID in ( 
                                select SITEID
                                from dbo.UFN_SITE_BUILDDATALISTSITEFILTER (@CURRENTAPPUSERID, @SITEFILTERMODE, @SITESSELECTED)
                            )
                        )
                    order by MT.TRANSACTIONDATE, MT.DATEADDED asc