USP_DATAFORMTEMPLATE_VIEW_CONSTITUENTMEMBERSHIPPROGRAM2

Parameters

Parameter	Parameter Type	Mode
@ID	nvarchar(72)	IN
@CONSTITUENTID	uniqueidentifier	INOUT
@DATALOADED	bit	INOUT
@PRIMARYMEMBERNAME	nvarchar(100)	INOUT
@PRIMARYMEMBERCONSTITUENTID	uniqueidentifier	INOUT
@PRIMARYMEMBERID	uniqueidentifier	INOUT
@MEMBERSHIPPROGRAMNAME	nvarchar(100)	INOUT
@MEMBERSHIPLEVELTYPE	nvarchar(100)	INOUT
@MEMBERSHIPID	uniqueidentifier	INOUT
@MEMBERSHIPLEVELNAME	nvarchar(100)	INOUT
@MEMBERSHIPTERM	nvarchar(12)	INOUT
@HIDEMEMBERSHIPTERM	bit	INOUT
@AMOUNT	money	INOUT
@BASECURRENCYID	uniqueidentifier	INOUT
@MEMBERSHIPSTATUS	nvarchar(15)	INOUT
@MEMBERS	xml	INOUT
@NUMBEROFMEMBERS	int	INOUT
@CHILDREN	int	INOUT
@EXPIRATIONDATE	date	INOUT
@RENEWALWINDOWEXPIRATIONDATE	date	INOUT
@MEMBERSHIPSTATUSAFTERRENEWALWINDOW	tinyint	INOUT
@MEMBERSINCEDATE	date	INOUT
@ISGIFT	bit	INOUT
@GIVENBYID	uniqueidentifier	INOUT
@GIVENBYNAME	nvarchar(154)	INOUT
@RENEWALWINDOWREVENUETYPECODE	tinyint	INOUT
@AFTEREXPIRATION	int	INOUT
@PROGRAMTYPECODE	tinyint	INOUT
@PROGRAMTYPE	nvarchar(40)	INOUT
@PAYS	nvarchar(40)	INOUT
@BALANCE	money	INOUT
@NEXTPAYMENT	date	INOUT
@PAIDINFULLDATE	date	INOUT
@TOTALPAYMENTSMADE	int	INOUT
@LASTPAIDDATE	date	INOUT
@LASTPAIDAMOUNT	money	INOUT
@FREQUENCY	nvarchar(40)	INOUT
@INSTALLMENTAMOUNT	money	INOUT
@PAYSINSTALLMENTS	bit	INOUT
@REVENUEID	uniqueidentifier	INOUT
@LASTPAIDINFO	nvarchar(40)	INOUT
@MEMBERSHIPSTATUSCODE	int	INOUT
@MEMBERSHIPLOOKUPID	nvarchar(100)	INOUT
@AUTOMATICALLYRENEWMEMBERSHIP	bit	INOUT
@CANCELLATIONREASONTIP	nvarchar(50)	INOUT
@WHEREISREVENUETRACKEDCODE	tinyint	INOUT
@ISUPGRADEABLE	bit	INOUT
@PROGRAMISACTIVE	bit	INOUT
@PDACCOUNTSYSTEMID	uniqueidentifier	INOUT
@PENDINGBATCHNUMBER	nvarchar(100)	INOUT
@HASOUTSTANDINGPLEDGE	bit	INOUT
@HASMEMBERSHIPLEVELTYPES	bit	INOUT
Definition

Copy


                CREATE procedure dbo.USP_DATAFORMTEMPLATE_VIEW_CONSTITUENTMEMBERSHIPPROGRAM2
                (
                    @ID nvarchar(72),
                    @CONSTITUENTID uniqueidentifier = null output,
                    @DATALOADED bit = 0 output,
                    @PRIMARYMEMBERNAME nvarchar(100) = null output,
                    @PRIMARYMEMBERCONSTITUENTID uniqueidentifier = null output,
                    @PRIMARYMEMBERID uniqueidentifier = null output,
                    @MEMBERSHIPPROGRAMNAME nvarchar(100) = null output,
                    @MEMBERSHIPLEVELTYPE nvarchar(100) = null output,
                    @MEMBERSHIPID uniqueidentifier = null output,
                    @MEMBERSHIPLEVELNAME nvarchar(100) = null output,
                    @MEMBERSHIPTERM nvarchar(12) = null output,
                    @HIDEMEMBERSHIPTERM bit = null output,
                    @AMOUNT money = null output,
                    @BASECURRENCYID uniqueidentifier = null output,
                    @MEMBERSHIPSTATUS nvarchar(15) = null output,
                    @MEMBERS xml = null output,
                    @NUMBEROFMEMBERS integer = null output,
                    @CHILDREN integer = null output,
                    @EXPIRATIONDATE date = null output,
                    @RENEWALWINDOWEXPIRATIONDATE date = null output,
                    @MEMBERSHIPSTATUSAFTERRENEWALWINDOW tinyint = null output,
                    @MEMBERSINCEDATE date = null output,
                    @ISGIFT bit = null output,
                    @GIVENBYID uniqueidentifier = null output,
                    @GIVENBYNAME nvarchar(154) = null output,
                    @RENEWALWINDOWREVENUETYPECODE tinyint = null output,
                    @AFTEREXPIRATION integer = null output,
                    @PROGRAMTYPECODE tinyint = null output,
                    @PROGRAMTYPE nvarchar(40) = null output,
                    @PAYS nvarchar(40) = null output,
                    @BALANCE money = null output,
                    @NEXTPAYMENT date = null output,
                    @PAIDINFULLDATE date = null output,
                    @TOTALPAYMENTSMADE int = null output,
                    @LASTPAIDDATE date = null output,
                    @LASTPAIDAMOUNT money = null output,
                    @FREQUENCY nvarchar(40) = null output,
                    @INSTALLMENTAMOUNT money = null output,
                    @PAYSINSTALLMENTS bit = null output,
                    @REVENUEID uniqueidentifier = null output,
                    @LASTPAIDINFO nvarchar(40) = null output,
                    @MEMBERSHIPSTATUSCODE integer = null output,
                    @MEMBERSHIPLOOKUPID nvarchar(100) = null output,
                    @AUTOMATICALLYRENEWMEMBERSHIP bit = null output,
                    @CANCELLATIONREASONTIP nvarchar(50) = null output,
                    @WHEREISREVENUETRACKEDCODE tinyint = null output,
                    @ISUPGRADEABLE bit = null output,
                    @PROGRAMISACTIVE bit = null output,
                    @PDACCOUNTSYSTEMID uniqueidentifier = null output,
                    @PENDINGBATCHNUMBER nvarchar(100) = null output,
                    @HASOUTSTANDINGPLEDGE bit = null output,
                    @HASMEMBERSHIPLEVELTYPES bit = null output
                )
                as
                    declare @TOTALMEMBERSHIPAMOUNT money;

                    set nocount on;

                    set @DATALOADED = 0;

                    -- Extract membership ID

                    set @MEMBERSHIPID = convert(uniqueidentifier,substring(CONVERT(nvarchar(72),@ID),1,36));

                    -- Extract constituent ID

                    set @CONSTITUENTID = convert(uniqueidentifier,substring(CONVERT(nvarchar(72),@ID),37,36));

                    declare @PROGRAMBASEDONCODE tinyint = 0, @MEMBERSHIPTRANSACTIONBASEAMOUNT money;

                    select
                        @PRIMARYMEMBERNAME = NF.NAME,
                        @PRIMARYMEMBERCONSTITUENTID = M.CONSTITUENTID,
                        @PRIMARYMEMBERID = M.ID
                    from
                        dbo.MEMBER M
                        cross apply dbo.UFN_CONSTITUENT_DISPLAYNAME(M.CONSTITUENTID) NF
                    where
                        M.MEMBERSHIPID = @MEMBERSHIPID and
                        M.ISPRIMARY = 1 and
                        M.ISDROPPED = 0;

                    select
                        @MEMBERSHIPPROGRAMNAME = MP.NAME,
                        @MEMBERSHIPLEVELTYPE = MT.DESCRIPTION,
                        @PROGRAMTYPECODE = MP.PROGRAMTYPECODE,
                        @PROGRAMTYPE = MP.PROGRAMTYPE,
                        @PROGRAMBASEDONCODE = MP.PROGRAMBASEDONCODE,
                        @MEMBERSHIPLOOKUPID = M.LOOKUPID,
                        @PROGRAMISACTIVE = MP.ISACTIVE
                    from
                        dbo.MEMBERSHIP M
                        inner join MEMBERSHIPPROGRAM MP on M.MEMBERSHIPPROGRAMID = MP.ID
                            left join dbo.MEMBERSHIPLEVELTYPECODE MT
                        on M.MEMBERSHIPLEVELTYPECODEID = MT.ID
                    where
                        M.ID = @MEMBERSHIPID;

                                set @MEMBERS =
                    (
                        select
                            NAMEFORMAT.NAME as MEMBERNAME,
                            CONSTITUENT.LOOKUPID,
                            MEMBERS.CONSTITUENTID as MEMBERCONSTITUENTID,
                            MEMBERS.ISPRIMARY
                        from dbo.UFN_MEMBERSHIP_GETACTIVEMEMBERS(@MEMBERSHIPID) MEMBERS
                            inner join dbo.CONSTITUENT on MEMBERS.CONSTITUENTID = CONSTITUENT.ID
                            outer apply dbo.UFN_CONSTITUENT_DISPLAYNAME(CONSTITUENT.ID) NAMEFORMAT
                        order by MEMBERS.ISPRIMARY desc
                        for xml raw('ITEM'),type,elements,root('MEMBERS'),BINARY BASE64
                    );

                    select
                        @NUMBEROFMEMBERS = count(CONSTITUENTID)
                    from
                        dbo.UFN_MEMBERSHIP_GETACTIVEMEMBERS(@MEMBERSHIPID);

                    select
                        @DATALOADED = 1,
                        @MEMBERSHIPLEVELNAME = ML.NAME,
                        @MEMBERSHIPTERM =
                            case
                                when (select count(ID) from dbo.MEMBERSHIPLEVELTERM TERMS where ML.ID = TERMS.LEVELID) > 1 and MP.PROGRAMTYPECODE = 0 then cast(MLT.TERMTIMELENGTH as nvarchar(5)) + ' ' + MLT.TERMLENGTH 
                                else null 
                            end,
                        @HIDEMEMBERSHIPTERM =
                            case
                                when (select count(ID) from dbo.MEMBERSHIPLEVELTERM TERMS where ML.ID = TERMS.LEVELID) > 1 and MP.PROGRAMTYPECODE = 0 then 0
                                else 1
                            end,
                        @BASECURRENCYID = MLT.BASECURRENCYID,
                        @MEMBERSHIPSTATUS = M.STATUS,
                        @MEMBERSHIPSTATUSCODE = M.STATUSCODE,
                        @CHILDREN = M.NUMBEROFCHILDREN,
                        @EXPIRATIONDATE = M.EXPIRATIONDATE,
                        @RENEWALWINDOWEXPIRATIONDATE = dbo.UFN_MEMBERSHIP_GETRENEWALWINDOWENDDATE(M.ID, M.EXPIRATIONDATE),
                        @MEMBERSHIPSTATUSAFTERRENEWALWINDOW = dbo.UFN_MEMBERSHIP_GETSTATUSCODE_AFTERRENEWALWINDOW(ML.ID),
                        @MEMBERSINCEDATE = M.JOINDATE,
                        @ISGIFT = M.ISGIFT,
                        @GIVENBYID = M.GIVENBYID,
                        @GIVENBYNAME = CONSTITUENT.NAME,
                        @RENEWALWINDOWREVENUETYPECODE = ML.RENEWALWINDOWREVENUETYPECODE,
                        @AFTEREXPIRATION = ML.AFTEREXPIRATION,
                        @FREQUENCY = MLT.RECURRINGPAYMENTOPTION,
                        @CANCELLATIONREASONTIP = MCC.CODE + ' - ' + MCC.DESCRIPTION,
                        @AUTOMATICALLYRENEWMEMBERSHIP = M.AUTOMATICALLYRENEWMEMBERSHIP,
                        @WHEREISREVENUETRACKEDCODE = MP.WHEREISREVENUETRACKEDCODE,
                        @HASMEMBERSHIPLEVELTYPES = case when (select count(ID) from dbo.MEMBERSHIPLEVELTYPE MLT where LEVELID = ML.ID) > 0 then 1 else 0 end
                    from
                        dbo.MEMBERSHIP M
                        inner join dbo.MEMBERSHIPLEVEL ML on M.MEMBERSHIPLEVELID = ML.ID
                        inner join dbo.MEMBERSHIPLEVELTERM MLT on M.MEMBERSHIPLEVELTERMID = MLT.ID
                        inner join dbo.MEMBERSHIPPROGRAM MP on M.MEMBERSHIPPROGRAMID = MP.ID
                        left join dbo.CONSTITUENT on CONSTITUENT.ID = M.GIVENBYID
                        left join dbo.MEMBERSHIPCANCELLATIONCODE MCC on M.CANCELLATIONREASONCODEID = MCC.ID
                    where
                        M.ID = @MEMBERSHIPID;

                    set @TOTALPAYMENTSMADE = 0;

                    select  @TOTALMEMBERSHIPAMOUNT = sum(FINANCIALTRANSACTIONLINEITEM.BASEAMOUNT)
                    from dbo.MEMBERSHIPTRANSACTION
                        inner join dbo.FINANCIALTRANSACTIONLINEITEM on FINANCIALTRANSACTIONLINEITEM.ID = MEMBERSHIPTRANSACTION.REVENUESPLITID
                    where MEMBERSHIPTRANSACTION.MEMBERSHIPID = @ID;

                    declare @LASTPAIDTRANSACTIONAMOUNT money;

                    select top 1
                        @LASTPAIDAMOUNT = MEMBERSHIPTRANSACTIONAMOUNT.AMOUNT,
                        @LASTPAIDTRANSACTIONAMOUNT = MEMBERSHIPTRANSACTIONAMOUNT.AMOUNT,
                        @LASTPAIDDATE = MT.TRANSACTIONDATE,
                        @PDACCOUNTSYSTEMID = FINANCIALTRANSACTION.PDACCOUNTSYSTEMID
                    from dbo.MEMBERSHIPTRANSACTION MT
                        inner join dbo.FINANCIALTRANSACTIONLINEITEM on FINANCIALTRANSACTIONLINEITEM.ID = MT.REVENUESPLITID
                        inner join dbo.FINANCIALTRANSACTION on FINANCIALTRANSACTION.ID  = FINANCIALTRANSACTIONLINEITEM.FINANCIALTRANSACTIONID
                        outer apply dbo.UFN_MEMBERSHIPTRANSACTION_AMOUNT(MT.ID, 1) as MEMBERSHIPTRANSACTIONAMOUNT  -- 1 = Currency base amount

                    where MT.MEMBERSHIPID = @MEMBERSHIPID
                    and FINANCIALTRANSACTIONLINEITEM.TYPECODE <> 1
                    and FINANCIALTRANSACTIONLINEITEM.DELETEDON is null
                    order by MT.TRANSACTIONDATE DESC, MT.DATEADDED DESC;

                    set @REVENUEID = dbo.UFN_MEMBERSHIP_GETPLEDGE(@MEMBERSHIPID);

                    declare @ISPENDING bit = 0;

                    if @REVENUEID is not null
                    begin
                    select
                        @TOTALPAYMENTSMADE = count(distinct FT.ID),
                        @LASTPAIDDATE = max(FT.DATE)
                    from dbo.INSTALLMENTSPLIT S
                        join dbo.INSTALLMENTSPLITPAYMENT SP on S.ID = SP.INSTALLMENTSPLITID and S.PLEDGEID = @REVENUEID
                        inner join dbo.FINANCIALTRANSACTIONLINEITEM FTLI on SP.PAYMENTID = FTLI.ID
                        inner join dbo.FINANCIALTRANSACTION FT on FTLI.FINANCIALTRANSACTIONID = FT.ID
                        where FTLI.TYPECODE <> 1
                        and FTLI.DELETEDON is null;

                        select
                            @INSTALLMENTAMOUNT = INSTALLMENT.AMOUNT,
                            @NEXTPAYMENT = INSTALLMENT.DATE,
                            @FREQUENCY = REVENUESCHEDULE.FREQUENCY
                        from MEMBERSHIP
                            left join dbo.FINANCIALTRANSACTION on FINANCIALTRANSACTION.ID = dbo.UFN_MEMBERSHIP_GETPLEDGE(MEMBERSHIP.ID)
                            left join dbo.INSTALLMENT on INSTALLMENT.ID = dbo.UFN_PLEDGE_GETNEXTINSTALLMENT(@REVENUEID, null)
                            left join dbo.REVENUESCHEDULE ON FINANCIALTRANSACTION.ID = REVENUESCHEDULE.ID
                        where MEMBERSHIP.ID = @MEMBERSHIPID
                        and FINANCIALTRANSACTION.DELETEDON is null;

                        select @BALANCE = dbo.UFN_PLEDGE_GETBALANCE(@REVENUEID);

                        set @PAYSINSTALLMENTS = 1;

                        set @ISPENDING = (select REVENUESCHEDULE.ISPENDING from dbo.REVENUESCHEDULE where REVENUESCHEDULE.ID = @REVENUEID);

                        if @ISPENDING = 1
                        begin
                                select top 1 
                                    @PENDINGBATCHNUMBER = BATCH.BATCHNUMBER
                                from dbo.BATCH
                                inner join dbo.BATCHMEMBERSHIPDUES on BATCHMEMBERSHIPDUES.BATCHID = BATCH.ID and dbo.UFN_MEMBERSHIP_GETPLEDGE(BATCHMEMBERSHIPDUES.EXISTINGMEMBERSHIPID) = @REVENUEID
                                where 
                                    BATCH.STATUSCODE not in (1, 2) 
                                    and BATCHMEMBERSHIPDUES.EXISTINGMEMBERSHIPID is not null
                                    and BATCHMEMBERSHIPDUES.REVENUETYPECODE = 1;
                        end

                    end
                    else --OK now try it as a recurring

                    begin
                        set @REVENUEID = dbo.UFN_MEMBERSHIP_GETRECURRINGGIFT_2(@MEMBERSHIPID, 1);

                        if @REVENUEID is not null
                        begin
                            select
                                @NEXTPAYMENT = case when REVENUESCHEDULE.NEXTTRANSACTIONDATE > REVENUESCHEDULE.ENDDATE then null else REVENUESCHEDULE.NEXTTRANSACTIONDATE end,
                                @FREQUENCY = REVENUESCHEDULE.FREQUENCY
                            from dbo.FINANCIALTRANSACTION
                                inner join dbo.REVENUE_EXT on REVENUE_EXT.ID = FINANCIALTRANSACTION.ID
                                inner join dbo.REVENUESCHEDULE on REVENUESCHEDULE.ID = FINANCIALTRANSACTION.ID
                                left join dbo.CHANNELCODE on CHANNELCODE.ID = REVENUE_EXT.CHANNELCODEID
                            where FINANCIALTRANSACTION.ID = @REVENUEID
                            and FINANCIALTRANSACTION.DELETEDON is null;

                            set @LASTPAIDDATE = null;
                            set @LASTPAIDAMOUNT = null;

                        select top 1
                            @LASTPAIDDATE = FINANCIALTRANSACTION.[DATE],
                            @LASTPAIDAMOUNT = sum(RECURRINGGIFTINSTALLMENTPAYMENT.AMOUNT)
                        from dbo.RECURRINGGIFTINSTALLMENT
                            inner join dbo.RECURRINGGIFTINSTALLMENTPAYMENT on RECURRINGGIFTINSTALLMENT.ID = RECURRINGGIFTINSTALLMENTPAYMENT.RECURRINGGIFTINSTALLMENTID
                            inner join dbo.FINANCIALTRANSACTION on FINANCIALTRANSACTION.ID = RECURRINGGIFTINSTALLMENTPAYMENT.PAYMENTID
                        where
                            RECURRINGGIFTINSTALLMENT.REVENUEID = @REVENUEID and
                            FINANCIALTRANSACTION.DELETEDON is null
                        group by FINANCIALTRANSACTION.[DATE]
                        order by FINANCIALTRANSACTION.[DATE] desc;

                            set @ISPENDING = (select REVENUESCHEDULE.ISPENDING from dbo.REVENUESCHEDULE where REVENUESCHEDULE.ID = @REVENUEID);

                            if @ISPENDING = 1
                            begin
                                    select top 1 
                                        @PENDINGBATCHNUMBER = BATCH.BATCHNUMBER
                                    from dbo.BATCH
                                    inner join dbo.BATCHMEMBERSHIPDUES on BATCHMEMBERSHIPDUES.BATCHID = BATCH.ID and dbo.UFN_MEMBERSHIP_GETRECURRINGGIFT(BATCHMEMBERSHIPDUES.EXISTINGMEMBERSHIPID) = @REVENUEID
                                    where 
                                        BATCH.STATUSCODE not in (1, 2) 
                                        and BATCHMEMBERSHIPDUES.EXISTINGMEMBERSHIPID is not null
                                        and BATCHMEMBERSHIPDUES.REVENUETYPECODE = 1;
                            end

                        end
                                                else
                                                begin
                                                        --If this is a lifetime program and it wasn't trapped by the installment plan check above, then the term option is pay in full and the number of payments is 1.

                                                        if @PROGRAMTYPECODE = 2
                                                        begin
                                                                set @TOTALPAYMENTSMADE = 1;
                                                        end
                                                end
                    end

                    if @BALANCE = 0 or @BALANCE is null set @PAIDINFULLDATE = @LASTPAIDDATE;

                    set @PAYS = cast(@INSTALLMENTAMOUNT as nvarchar(max)) + ' ' + lower(@FREQUENCY); --this will get formatted properly in the VB


                    declare @OBTAINLEVELCODE tinyint;
                    declare @MOSTRECENTTRANSACTIONID uniqueidentifier;
                    declare @MOSTRECENTLEVEL uniqueidentifier;

                    select top 1
                        @OBTAINLEVELCODE = MEMBERSHIPLEVEL.OBTAINLEVELCODE,
                        @MOSTRECENTTRANSACTIONID = MEMBERSHIPTRANSACTION.ID,
                        @MOSTRECENTLEVEL = MEMBERSHIPLEVEL.ID,
                        @MEMBERSHIPTRANSACTIONBASEAMOUNT = MEMBERSHIPTRANSACTION.BASEAMOUNT
                    from dbo.MEMBERSHIPTRANSACTION
                        inner join dbo.MEMBERSHIPLEVEL on MEMBERSHIPTRANSACTION.MEMBERSHIPLEVELID = MEMBERSHIPLEVEL.ID
                    where MEMBERSHIPTRANSACTION.MEMBERSHIPID = @MEMBERSHIPID
                    order by MEMBERSHIPTRANSACTION.TRANSACTIONDATE DESC, MEMBERSHIPTRANSACTION.DATEADDED DESC;

                    set @AMOUNT = @LASTPAIDTRANSACTIONAMOUNT;
                    if @WHEREISREVENUETRACKEDCODE = 1
                        set @AMOUNT = @MEMBERSHIPTRANSACTIONBASEAMOUNT;

                    if @OBTAINLEVELCODE = 1 and @WHEREISREVENUETRACKEDCODE = 0
                    begin
                        select @AMOUNT = SUM(AMOUNT)
                        from dbo.MEMBERSHIPPROGRAMCONTRIBUTIONREVENUE
                        where (ORIGINALMEMBERSHIPTRANSACTIONID = @MOSTRECENTTRANSACTIONID);
                    end

                    set @ISUPGRADEABLE = dbo.UFN_MEMBERSHIP_ISUPGRADEABLE(@MEMBERSHIPID,default);

                    set @HASOUTSTANDINGPLEDGE = dbo.UFN_MEMBERSHIP_HASOUTSTANDINGPLEDGE(@ID);