USP_DATAFORMTEMPLATE_VIEW_MEMBERSHIPBATCHREVENUEDATA

The load procedure used by the view dataform template "Batch Revenue Application Membership View Data 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
@MEMBERSHIPPROGRAMID	uniqueidentifier	INOUT	Program
@TERM	nvarchar(8)	INOUT	Term
@TRANSACTIONDATE	datetime	INOUT	Transaction date
@MEMBERSHIPLEVELID	uniqueidentifier	INOUT	Level
@MEMBERSHIPLEVELTERMID	uniqueidentifier	INOUT	Term
@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

Definition

Copy


            CREATE procedure dbo.USP_DATAFORMTEMPLATE_VIEW_MEMBERSHIPBATCHREVENUEDATA
                (
                    @ID uniqueidentifier,    
                    @DATALOADED bit = 0 output,
                    @MEMBERSHIP nvarchar(203) = null output,
                    @PROGRAM nvarchar(100) = null output,
                    @MEMBERSHIPPROGRAMID uniqueidentifier = null output,
                    @TERM nvarchar(8) = null output,
                    @TRANSACTIONDATE datetime = null output,
                    @MEMBERSHIPLEVELID uniqueidentifier = null output,
                    @MEMBERSHIPLEVELTERMID 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
                )
                as
                set nocount on;

                set @DATALOADED = 0;

                select
                    @DATALOADED = 1,
                    @MEMBERSHIP = MP.NAME + ' - ' + ML.NAME,
                    @PROGRAM = MP.NAME,
                    @MEMBERSHIPPROGRAMID = MP.ID,
                    @TERM = MLT.TERM,
                    @TRANSACTIONDATE = getdate(),
                    @MEMBERSHIPLEVELID = ML.ID,
                    @MEMBERSHIPLEVELTERMID = MLT.ID,
                    @FORCEMANUALDOWNGRADE = ML.FORCEMANUALDOWNGRADES,
                    @EXPIRESONCODE = MP.EXPIRESONCODE,
                    @CUTOFFDAY = MP.CUTOFFDAY,
                    @CUTOFFDATEFORYEAR = MP.CUTOFFDATEFORYEAR
                from dbo.MEMBERSHIPLEVELTERM MLT
                inner join dbo.MEMBERSHIPLEVEL ML on MLT.LEVELID = ML.ID
                inner join dbo.MEMBERSHIPPROGRAM MP on ML.MEMBERSHIPPROGRAMID = MP.ID
                where MLT.ID = @ID;

                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;