USP_DATAFORMTEMPLATE_VIEW_BATCHREVENUEMEMBERSHIPDATA

The load procedure used by the view dataform template "Batch Revenue Membership Data View Form"

Parameters

Parameter	Parameter Type	Mode	Description
@ID	uniqueidentifier	IN	The input ID parameter used to load the fields defined on the form.
@DATALOADED	bit	INOUT	Output parameter indicating whether or not data was actually loaded.
@MEMBERSHIP	nvarchar(203)	INOUT	Membership
@PROGRAM	nvarchar(100)	INOUT	Program
@TERM	nvarchar(8)	INOUT	Term
@EXPIRATIONDATE	datetime	INOUT	Expiration date
@AMOUNT	money	INOUT	Amount
@MEMBERSHIPPROGRAMID	uniqueidentifier	INOUT	Program
@MEMBERSHIPLEVELID	uniqueidentifier	INOUT	Level
@MEMBERSHIPLEVELTERMID	uniqueidentifier	INOUT	Term
@MEMBERSHIPTYPECODEID	uniqueidentifier	INOUT	Type
@NUMBEROFCHILDREN	tinyint	INOUT	No. of children
@COMMENTS	nvarchar(1000)	INOUT	Comments
@MEMBERS	xml	INOUT	Members
@ISGIFT	bit	INOUT	This membership is a gift
@SENDRENEWALCODE	tinyint	INOUT	Send renewal notice to
@GIVENBYID	uniqueidentifier	INOUT	Given By
@MEMBERSHIPID	uniqueidentifier	INOUT	ID
@TERMSLIST	xml	INOUT	TERMSLIST
@FORCEMANUALDOWNGRADE	bit	INOUT	FORCEMANUALDOWNGRADE
@EXPIRESONCODE	tinyint	INOUT	EXPIRESONCODE
@CUTOFFDAY	tinyint	INOUT	CUTOFFDAY
@CUTOFFDATEFORYEAR	nvarchar(4)	INOUT	CUTOFFDATEFORYEAR
@EXPIRATIONDATES	xml	INOUT	EXPIRATIONDATES
@STATUS	tinyint	INOUT	STATUS
@EXPIRATIONWINDOW	datetime	INOUT	EXPIRATIONWINDOW

Definition

Copy


                CREATE procedure dbo.USP_DATAFORMTEMPLATE_VIEW_BATCHREVENUEMEMBERSHIPDATA
                (
                    @ID uniqueidentifier,    
                    @DATALOADED bit = 0 output,
                    @MEMBERSHIP nvarchar(203) = null output,
                    @PROGRAM nvarchar(100) = null output,
                    @TERM nvarchar(8) = null output,
                    @EXPIRATIONDATE datetime = null output,
                    @AMOUNT money = null output,
                    @MEMBERSHIPPROGRAMID uniqueidentifier = null output,
                    @MEMBERSHIPLEVELID uniqueidentifier = null output,
                    @MEMBERSHIPLEVELTERMID uniqueidentifier = null output,
                    @MEMBERSHIPTYPECODEID uniqueidentifier = null output,
                    @NUMBEROFCHILDREN tinyint = null output,
                    @COMMENTS nvarchar(1000) = null output,
                    @MEMBERS xml = null output,
                    @ISGIFT bit = null output,
                    @SENDRENEWALCODE tinyint = null output,
                    @GIVENBYID uniqueidentifier = null output,
                    @MEMBERSHIPID uniqueidentifier = null output,
                    @TERMSLIST xml = null output,
                    @FORCEMANUALDOWNGRADE bit = null output,
                    @EXPIRESONCODE tinyint = null output,
                    @CUTOFFDAY tinyint = null output,
                    @CUTOFFDATEFORYEAR nvarchar(4) = null output,
                    @EXPIRATIONDATES xml = null output,
                    @STATUS tinyint = null output,
                    @EXPIRATIONWINDOW datetime = null output
                )
                as
                set nocount on;

                set @DATALOADED = 0;

                select 
                    @DATALOADED = 1,
                    @MEMBERSHIP = MP.NAME + ' - ' + ML.NAME,
                    @PROGRAM = MP.NAME,
                    @MEMBERSHIPPROGRAMID = MEMBERSHIP.MEMBERSHIPPROGRAMID,
                    @TERM = MLT.TERM,
                    @EXPIRATIONDATE = null,
                        --case when MEMBERSHIP.STATUSCODE = 0 then
                        --    dbo.UFN_MEMBERSHIP_CALCULATERENEWALEXPIRATION(MEMBERSHIP.EXPIRATIONDATE,MEMBERSHIP.MEMBERSHIPLEVELTERMID)
                        --else
                        --    dbo.UFN_MEMBERSHIP_CALCULATEEXPIRATIONDATEBYLEVEL(MEMBERSHIP.MEMBERSHIPLEVELID, MLT.ID, getdate()) 
                        --end,
                    @AMOUNT = MLT.AMOUNT,
                    @MEMBERSHIPLEVELID = MEMBERSHIP.MEMBERSHIPLEVELID,
                    @MEMBERSHIPLEVELTERMID = MEMBERSHIP.MEMBERSHIPLEVELTERMID,
                    @MEMBERSHIPTYPECODEID = MEMBERSHIP.MEMBERSHIPLEVELTYPECODEID,
                    @NUMBEROFCHILDREN = MEMBERSHIP.NUMBEROFCHILDREN,
                    @COMMENTS = MEMBERSHIP.COMMENTS,
                    @ISGIFT = MEMBERSHIP.ISGIFT,
                    @SENDRENEWALCODE = MEMBERSHIP.SENDRENEWALCODE,
                    @GIVENBYID = MEMBERSHIP.GIVENBYID,
                    @MEMBERSHIPID = @ID,
                    @FORCEMANUALDOWNGRADE = ML.FORCEMANUALDOWNGRADES,
                    @EXPIRESONCODE = MP.EXPIRESONCODE,
                    @CUTOFFDAY = MP.CUTOFFDAY,
                    @CUTOFFDATEFORYEAR = MP.CUTOFFDATEFORYEAR,
                    @STATUS = MEMBERSHIP.STATUSCODE,
                    @EXPIRATIONWINDOW = dbo.UFN_MEMBERSHIPLEVEL_CREATERENEWALAFTEREXPIRATIONDATE(MEMBERSHIP.MEMBERSHIPLEVELID, MEMBERSHIP.EXPIRATIONDATE)
                from dbo.MEMBERSHIP 
                inner join dbo.MEMBERSHIPLEVELTERM MLT on MEMBERSHIP.MEMBERSHIPLEVELTERMID = MLT.ID
                inner join dbo.MEMBERSHIPLEVEL ML on MEMBERSHIP.MEMBERSHIPLEVELID = ML.ID
                inner join dbo.MEMBERSHIPPROGRAM MP on MEMBERSHIP.MEMBERSHIPPROGRAMID = MP.ID
                where MEMBERSHIP.ID = @ID;

                set @MEMBERS =
                    (
                        select
                            M.ID,
                            M.CONSTITUENTID,
                            M.ISPRIMARY,
                            (
                                select
                                    MC.ID,
                                    MC.NAMEONCARD,
                                    MC.EXPIRATIONDATE
                                from dbo.MEMBERSHIPCARD MC
                                where MC.MEMBERID = M.ID and MC.STATUSCODE <> 2
                                for xml raw('ITEM'),type,elements,root('MEMBERSHIPCARDS'),BINARY BASE64
                            )
                            from dbo.MEMBER M
                            where MEMBERSHIPID = @ID and ISDROPPED = 0
                            for xml raw ('ITEM'), type, elements, root('MEMBERS'),BINARY BASE64
                    )

                set @TERMSLIST =
                (
                    select
                        MEMBERSHIPLEVELTERM.ID,
                        case when TERMCODE = 0 then 1
                            when TERMCODE = 1 then 2
                            when TERMCODE = 2 then 3
                            when TERMCODE = 3 then 4
                            when TERMCODE = 4 then 5
                            when TERMCODE = 5 then 10
                            else 0
                        end AS TERM,
                        AMOUNT,
                        MEMBERSHIPLEVELTERM.LEVELID
                    from dbo.MEMBERSHIPLEVELTERM
                    inner join dbo.MEMBERSHIPLEVEL on MEMBERSHIPLEVELTERM.LEVELID = MEMBERSHIPLEVEL.ID
                    where MEMBERSHIPLEVEL.MEMBERSHIPPROGRAMID = @MEMBERSHIPPROGRAMID
                    order by MEMBERSHIPLEVELTERM.AMOUNT desc, MEMBERSHIPLEVEL.SEQUENCE desc
                    for xml raw ('ITEM'), type, elements, root('TERMSLIST'), BINARY BASE64
                )

                if @EXPIRESONCODE = 4 --specific dates
                    begin
                    set @EXPIRATIONDATES = 
                    (
                        select 
                            ME.ID,
                            ME.EXPIRATIONDATE,
                            ME.PUSHNEXTDATE,
                            ME.SEQUENCE
                        from 
                            dbo.MEMBERSHIPPROGRAMENDDATE ME
                        where 
                            MEMBERSHIPPROGRAMID = @MEMBERSHIPPROGRAMID
                        for xml raw ('ITEM'), type, elements, root('EXPIRATIONDATES'), BINARY BASE64
                    )
                    end

                return 0;