USP_DATAFORMTEMPLATE_ADD_SALESORDERITEMMEMBERSHIPRENEWAL_PRELOAD

The load procedure used by the edit dataform template "Sales Order Item Membership Renewal Add Data Form"

Parameters

Parameter	Parameter Type	Mode	Description
@MEMBERSHIPID	uniqueidentifier	IN	Input parameter indicating the context ID for the record being added.
@CURRENTCONSTITUENTID	uniqueidentifier	INOUT
@MEMBERSHIPPROGRAMID	uniqueidentifier	INOUT	Program
@MEMBERSHIPLEVELID	uniqueidentifier	INOUT	Level
@MEMBERSHIPLEVELTERMID	uniqueidentifier	INOUT	Term
@TRANSACTIONDATE	datetime	INOUT
@EXPIRATIONDATE	datetime	INOUT
@MEMBERSHIPTYPECODEID	uniqueidentifier	INOUT	Type
@NUMBEROFCHILDREN	smallint	INOUT	No. of children
@COMMENTS	nvarchar(1000)	INOUT	Comments
@MEMBERS	xml	INOUT	Members
@ISRENEWAL	bit	INOUT
@ISREJOIN	bit	INOUT

Definition

Copy



                    CREATE procedure dbo.USP_DATAFORMTEMPLATE_ADD_SALESORDERITEMMEMBERSHIPRENEWAL_PRELOAD
                    (
                        @MEMBERSHIPID uniqueidentifier,
                        @CURRENTCONSTITUENTID uniqueidentifier = null output,
                        @MEMBERSHIPPROGRAMID uniqueidentifier = null output,
                        @MEMBERSHIPLEVELID uniqueidentifier = null output,
                        @MEMBERSHIPLEVELTERMID uniqueidentifier = null output,
                        @TRANSACTIONDATE datetime = null output,
                        @EXPIRATIONDATE datetime = null output,
                        @MEMBERSHIPTYPECODEID uniqueidentifier = null output,
                        @NUMBEROFCHILDREN smallint = null output,
                        @COMMENTS nvarchar(1000) = null output,
                        @MEMBERS xml = null output,
                        @ISRENEWAL bit = null output,
                        @ISREJOIN bit = null output
                    )
                    as
                        set nocount on;

                        declare @RENEWALBEFOREEXPIRATION datetime;
                        declare @RENEWALAFTEREXPIRATION datetime;
                        declare @STATUSCODE tinyint;

                        select 
                            @MEMBERSHIPPROGRAMID = MEMBERSHIP.MEMBERSHIPPROGRAMID,
                            @MEMBERSHIPLEVELID = MEMBERSHIP.MEMBERSHIPLEVELID,
                            @MEMBERSHIPLEVELTERMID = MEMBERSHIP.MEMBERSHIPLEVELTERMID,
                            @TRANSACTIONDATE = dbo.UFN_DATE_GETEARLIESTTIME(getdate()),
                            @EXPIRATIONDATE = dbo.UFN_DATE_GETLATESTTIME(MEMBERSHIP.EXPIRATIONDATE),
                            @MEMBERSHIPTYPECODEID = MEMBERSHIP.MEMBERSHIPLEVELTYPECODEID,
                            @NUMBEROFCHILDREN = MEMBERSHIP.NUMBEROFCHILDREN,
                            @COMMENTS = MEMBERSHIP.COMMENTS,
                            @STATUSCODE = MEMBERSHIP.STATUSCODE
                        from
                            dbo.MEMBERSHIP
                        where
                            ID = @MEMBERSHIPID;

                        set @RENEWALBEFOREEXPIRATION = dbo.UFN_MEMBERSHIPLEVEL_CREATERENEWALBEFOREEXPIRATIONDATE(@MEMBERSHIPLEVELID, @EXPIRATIONDATE);
                        set @RENEWALAFTEREXPIRATION = dbo.UFN_MEMBERSHIPLEVEL_CREATERENEWALAFTEREXPIRATIONDATE(@MEMBERSHIPLEVELID, @EXPIRATIONDATE);

                        set @MEMBERS =
                        (
                            select
                                newid() [ID],
                                M.CONSTITUENTID,
                                M.ISPRIMARY,
                                (
                                    select
                                        newid() [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 = @MEMBERSHIPID and ISDROPPED = 0
                                for xml raw ('ITEM'), type, elements, root('MEMBERS'),BINARY BASE64
                        )

                        select
                            @CURRENTCONSTITUENTID = MEMBER.CONSTITUENTID
                        from
                            dbo.MEMBER
                        inner join
                            dbo.MEMBERSHIP on MEMBER.MEMBERSHIPID = MEMBERSHIP.ID
                        where
                            MEMBERSHIP.ID = @MEMBERSHIPID;

                        if @STATUSCODE = 1 or (@TRANSACTIONDATE > @EXPIRATIONDATE and @TRANSACTIONDATE > @RENEWALAFTEREXPIRATION)
                            set @ISREJOIN = 1;
                        else
                            set @ISRENEWAL = 1;

                        return 0;