USP_DATAFORMTEMPLATE_EDITLOAD_MEMBERSHIPDETAILS

Parameters

Parameter	Parameter Type	Mode
@ID	uniqueidentifier	IN
@DATALOADED	bit	INOUT
@TSLONG	bigint	INOUT
@MEMBERSHIPLEVELID	uniqueidentifier	INOUT
@MEMBERSHIPLEVELTYPECODEID	uniqueidentifier	INOUT
@COMMENTS	nvarchar(1000)	INOUT
@AUTOMATICALLYRENEWMEMBERSHIP	bit	INOUT
@VALIDFORAUTORENEW	bit	INOUT
@MEMBERSHIPPROGRAMID	uniqueidentifier	INOUT
@MEMBERSHIPLEVELTERMID	uniqueidentifier	INOUT
@EXPIRATIONDATE	datetime	INOUT
@TRANSACTIONDATE	datetime	INOUT
@ACTIONCODE	tinyint	INOUT
@STATUSCODE	tinyint	INOUT
@TERMCODE	tinyint	INOUT
@LEVELTERMS	xml	INOUT
@PROGRAMTYPECODE	int	INOUT
Definition

Copy


                    CREATE procedure dbo.USP_DATAFORMTEMPLATE_EDITLOAD_MEMBERSHIPDETAILS
                    (
                        @ID uniqueidentifier,
                        @DATALOADED bit = 0 output,
                        @TSLONG bigint = 0 output,
                        @MEMBERSHIPLEVELID uniqueidentifier = null output,
                        @MEMBERSHIPLEVELTYPECODEID uniqueidentifier = null output,
                        @COMMENTS nvarchar(1000) = null output,
                        @AUTOMATICALLYRENEWMEMBERSHIP bit = null output,
                        @VALIDFORAUTORENEW bit = null output,
                        @MEMBERSHIPPROGRAMID uniqueidentifier = null output,
                        @MEMBERSHIPLEVELTERMID uniqueidentifier = null output,
                        @EXPIRATIONDATE datetime = null output,
                        @TRANSACTIONDATE datetime = null output,
                        @ACTIONCODE tinyint = null output,
                        @STATUSCODE tinyint = null output,
                        @TERMCODE tinyint = null output,
                        @LEVELTERMS xml = null output,
                        @PROGRAMTYPECODE int = null output
                    )
                    as
                        set nocount on;

                        set @TSLONG = 0

                        select
                            @DATALOADED = 1,
                            @TSLONG = MS.TSLONG,
                            @MEMBERSHIPLEVELTYPECODEID = MS.MEMBERSHIPLEVELTYPECODEID,
                            @COMMENTS = MS.COMMENTS,
                            @AUTOMATICALLYRENEWMEMBERSHIP = MS.AUTOMATICALLYRENEWMEMBERSHIP,
                            @MEMBERSHIPLEVELID = MS.MEMBERSHIPLEVELID,
                            @MEMBERSHIPLEVELTERMID = MS.MEMBERSHIPLEVELTERMID,
                            @STATUSCODE = MS.STATUSCODE,
                            @TERMCODE = MLT.TERMCODE,
                            @PROGRAMTYPECODE = MP.PROGRAMTYPECODE,
                            @MEMBERSHIPPROGRAMID = MS.MEMBERSHIPPROGRAMID,
                            @LEVELTERMS = dbo.UFN_MEMBERSHIPLEVELTERM_GETTERMINFO_TOITEMLISTXML(MS.MEMBERSHIPPROGRAMID)
                        from
                            dbo.MEMBERSHIP MS
                            inner join dbo.MEMBER on MEMBER.MEMBERSHIPID = MS.ID
                            inner join dbo.MEMBERSHIPLEVELTERM MLT
                                on MS.MEMBERSHIPLEVELTERMID = MLT.ID
                            inner join dbo.MEMBERSHIPPROGRAM MP
                                on MP.ID = MS.MEMBERSHIPPROGRAMID
                        where
                            MS.ID = @ID and
                            MEMBER.ISPRIMARY = 1

                        select top 1
                            @VALIDFORAUTORENEW = case when PAYMENTMETHODCODE = 2 then 1 else 0 end
                        from
                            dbo.MEMBERSHIPTRANSACTION
                            inner join dbo.FINANCIALTRANSACTIONLINEITEM on MEMBERSHIPTRANSACTION.REVENUESPLITID = FINANCIALTRANSACTIONLINEITEM.ID
                            inner join dbo.REVENUEPAYMENTMETHOD on FINANCIALTRANSACTIONLINEITEM.FINANCIALTRANSACTIONID = REVENUEPAYMENTMETHOD.REVENUEID
                        where
                            MEMBERSHIPTRANSACTION.MEMBERSHIPID = @ID
                        order by
                            MEMBERSHIPTRANSACTION.TRANSACTIONDATE desc, MEMBERSHIPTRANSACTION.DATEADDED desc

                        --Get the latest transaction information.

                        select top 1
                            @EXPIRATIONDATE = MT.EXPIRATIONDATE,
                            @TRANSACTIONDATE = MT.TRANSACTIONDATE,
                            @ACTIONCODE = ACTIONCODE
                        from dbo.MEMBERSHIP M
                          inner join 
                            dbo.MEMBERSHIPTRANSACTION MT
                            on M.ID = MT.MEMBERSHIPID
                        where M.ID=@ID
                        order by MT.TRANSACTIONDATE desc, MT.DATEADDED desc;

                    return 0;