USP_CONSTITUENTMEMBERSHIPPROGRAM_GETBENEFITSDATALIST

          CREATE procedure dbo.USP_CONSTITUENTMEMBERSHIPPROGRAM_GETBENEFITSDATALIST
          (
            @MEMBERSHIPID uniqueidentifier
          )
          as
          begin

            set nocount on;

            -- View form IDs

            declare @BENEFITVIEWFORMID uniqueidentifier = '7d4ab86a-81c5-42e1-87b0-540033851305'
            declare @MAILINGVIEWFORMID uniqueidentifier = '47445040-3beb-4fef-9cac-701710f80f4b'

            declare @MEMBERSHIPPROGRAMID uniqueidentifier;
            select 
              @MEMBERSHIPPROGRAMID = MEMBERSHIP.MEMBERSHIPPROGRAMID
            from 
              dbo.MEMBERSHIP
            where MEMBERSHIP.ID = @MEMBERSHIPID

            declare @MEMBERS table (CONSTITUENTID uniqueidentifier)
            insert into @MEMBERS select CONSTITUENTID from dbo.MEMBER where MEMBERSHIPID = @MEMBERSHIPID

            --Benefits

            select
                REVENUE_EXT.ID,
                @BENEFITVIEWFORMID as VIEWFORMID,
                4 as TYPECODE,
                REVENUEBENEFIT.TOTALVALUE AS BENEFITAMOUNT,
                case
                    when REVENUEBENEFIT.QUANTITY = 0 then 1
                    else REVENUEBENEFIT.QUANTITY
                end AS BENEFITQUANTITY,
                BENEFIT.NAME as BENEFITNAME,
                BENEFIT.ID as BENEFITID,
                'Benefit' as ITEMTYPE,
                MEMBERSHIPTRANSACTION.TRANSACTIONDATE as DATE,                
                '' as NAME,
                REVENUEBENEFIT.BASECURRENCYID,
                '00000000-0000-0000-0000-000000000000' as MKTID,
                REVENUEBENEFIT.ID as CONTEXTID,
                null as PRINTDATE,
                null as CARDSTATUS
            from dbo.MEMBERSHIPTRANSACTION
                inner join dbo.MEMBERSHIPLEVEL on MEMBERSHIPTRANSACTION.MEMBERSHIPLEVELID=MEMBERSHIPLEVEL.ID
                inner join dbo.MEMBERSHIPLEVELTERM on MEMBERSHIPTRANSACTION.MEMBERSHIPLEVELTERMID=MEMBERSHIPLEVELTERM.ID
                inner join dbo.REVENUESPLIT_EXT on MEMBERSHIPTRANSACTION.REVENUESPLITID = REVENUESPLIT_EXT.ID
                inner join dbo.FINANCIALTRANSACTIONLINEITEM FTLI on FTLI.ID = REVENUESPLIT_EXT.ID
                inner join dbo.FINANCIALTRANSACTION FT on FT.ID = FTLI.FINANCIALTRANSACTIONID
                inner join dbo.REVENUE_EXT on FT.ID = REVENUE_EXT.ID
                inner join dbo.REVENUEBENEFIT on REVENUE_EXT.ID = REVENUEBENEFIT.REVENUEID
                inner join dbo.BENEFIT on REVENUEBENEFIT.BENEFITID = BENEFIT.ID

             where
                MEMBERSHIPTRANSACTION.MEMBERSHIPID = @MEMBERSHIPID
                and FTLI.TYPECODE <> 1
                and FT.DELETEDON is null 
                and FTLI.DELETEDON is null                

            union all

            --Mailings

            select
                CONSTITUENTAPPEAL.ID,
                @MAILINGVIEWFORMID as VIEWFORMID,
                case
                    when APPEALMAILING.ID is not null then 3 
                    else 2
                end as TYPECODE,
                null AS BENEFITAMOUNT,
                null as BENEFITQUANTITY,
                '' as BENEFITNAME,
                null as BENEFITID,
                'Mailing' as ITEMTYPE,
                CONSTITUENTAPPEAL.DATESENT as DATE,                
                MKTSEGMENTATION.NAME as NAME,
                null as BASECURRENCYID,
                CONSTITUENTAPPEAL.MKTSEGMENTATIONID as MKTID,
                CONSTITUENTAPPEAL.MKTSEGMENTATIONID as CONTEXTID,
                null as PRINTDATE,
                null as CARDSTATUS
            from @MEMBERS MEMBERS
                inner join dbo.CONSTITUENTAPPEAL on CONSTITUENTAPPEAL.CONSTITUENTID = MEMBERS.CONSTITUENTID
                inner join dbo.APPEAL on APPEAL.ID = CONSTITUENTAPPEAL.APPEALID
                left join dbo.MKTPACKAGE on MKTPACKAGE.ID = CONSTITUENTAPPEAL.MKTPACKAGEID
                left join dbo.MKTSEGMENTATION on MKTSEGMENTATION.ID = CONSTITUENTAPPEAL.MKTSEGMENTATIONID 
                left join dbo.APPEALMAILING on APPEALMAILING.ID = CONSTITUENTAPPEAL.MKTSEGMENTATIONID
                left join dbo.APPEALMAILINGSETUPLETTER MAILLETTER on MKTPACKAGE.ID = MAILLETTER.MAILPACKAGEID
                left join dbo.APPEALMAILINGSETUPLETTER EMAILLETTER on MKTPACKAGE.ID = EMAILLETTER.EMAILPACKAGEID
            where
                APPEAL.MEMBERSHIPPROGRAMID=@MEMBERSHIPPROGRAMID
            order by DATE DESC
        end