USP_DATAFORMTEMPLATE_VIEW_SALESORDERMEMBERSHIPLOAD

The load procedure used by the view dataform template "Sales Order Membership Load 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.
@DESCRIPTION	nvarchar(511)	INOUT	Description
@MEMBERSHIPPROGRAMID	uniqueidentifier	INOUT	Program
@MEMBERSHIPLEVELID	uniqueidentifier	INOUT	Level
@MEMBERSHIPLEVELTERMID	uniqueidentifier	INOUT	Term
@MEMBERSHIPLEVELTYPECODEID	uniqueidentifier	INOUT	Type
@EXPIRATIONDATE	datetime	INOUT	Expiration date
@NUMBEROFCHILDREN	smallint	INOUT	No. of children
@COMMENTS	nvarchar(1000)	INOUT	Comments
@ISGIFT	bit	INOUT	Is gift
@SENDRENEWALCODE	smallint	INOUT	Send renewal notice to
@GIVENBYID	uniqueidentifier	INOUT	Given by
@STATUSCODE	tinyint	INOUT	STATUSCODE
@MEMBERS	xml	INOUT	MEMBERS
@MEMBERSHIPLEVELS	xml	INOUT	MEMBERSHIPLEVELS
Definition

Copy


                CREATE procedure dbo.USP_DATAFORMTEMPLATE_VIEW_SALESORDERMEMBERSHIPLOAD
                (
                    @ID uniqueidentifier,
                    @DATALOADED bit = 0 output,
                    @DESCRIPTION nvarchar(511) = null output,
                    @MEMBERSHIPPROGRAMID uniqueidentifier = null output,
                    @MEMBERSHIPLEVELID uniqueidentifier = null output,
                    @MEMBERSHIPLEVELTERMID uniqueidentifier = null output,
                    @MEMBERSHIPLEVELTYPECODEID uniqueidentifier = null output,
                    @EXPIRATIONDATE datetime = null output,
                    @NUMBEROFCHILDREN smallint = null output,
                    @COMMENTS nvarchar(1000) = null output,
                    @ISGIFT bit = null output,
                    @SENDRENEWALCODE smallint = null output,
                    @GIVENBYID uniqueidentifier = null output,
                    @STATUSCODE tinyint = null output,
                    @MEMBERS xml = null output,
                    @MEMBERSHIPLEVELS xml = null output
                )
                as
                    set nocount on;

                    declare @TRANSACTIONDATE datetime;
                    set @TRANSACTIONDATE = getdate();

                    set @DATALOADED = 0;

                    select
                        @DATALOADED = 1,
                        @DESCRIPTION = dbo.UFN_CONSTITUENT_BUILDNAME(MEMBER.CONSTITUENTID) + ': ' + dbo.UFN_MEMBERSHIP_GETDESCRIPTION(MEMBERSHIP.ID),
                        @MEMBERSHIPPROGRAMID =MEMBERSHIP.MEMBERSHIPPROGRAMID,
                        @MEMBERSHIPLEVELID = MEMBERSHIP.MEMBERSHIPLEVELID,
                        @MEMBERSHIPLEVELTERMID = MEMBERSHIP.MEMBERSHIPLEVELTERMID,
                        @MEMBERSHIPLEVELTYPECODEID = MEMBERSHIP.MEMBERSHIPLEVELTYPECODEID,
                        @EXPIRATIONDATE = MEMBERSHIP.EXPIRATIONDATE,
                        @NUMBEROFCHILDREN = MEMBERSHIP.NUMBEROFCHILDREN,
                        @COMMENTS = MEMBERSHIP.COMMENTS,
                        @ISGIFT = MEMBERSHIP.ISGIFT,
                        @SENDRENEWALCODE = MEMBERSHIP.SENDRENEWALCODE,
                        @GIVENBYID = MEMBERSHIP.GIVENBYID,
                        @STATUSCODE = MEMBERSHIP.STATUSCODE,
                        @MEMBERS = (
                            select
                                newid() [ID],
                                MEMBER.ID [MEMBERID],
                                MEMBER.CONSTITUENTID,
                                MEMBER.ISPRIMARY,
                                case when exists(select * from dbo.MEMBERSHIPCARD 
                                                    where MEMBER.ID = MEMBERSHIPCARD.MEMBERID
                                                            and MEMBERSHIPCARD.STATUSCODE <> 2)
                                    then
                                        (
                                            select
                                                newid() [ID],
                                                MEMBERSHIPCARD.ID [MEMBERSHIPCARDID],
                                                MEMBERSHIPCARD.NAMEONCARD [NAMEONCARD],
                                                MEMBERSHIPCARD.EXPIRATIONDATE [EXPIRATIONDATE]
                                            from
                                                dbo.MEMBERSHIPCARD
                                            where
                                                MEMBER.ID = MEMBERSHIPCARD.MEMBERID
                                                and MEMBERSHIPCARD.STATUSCODE <> 2
                                            for xml raw('ITEM'),type,elements,root('MEMBERSHIPCARDS'),binary base64
                                        )
                                    else
                                        (
                                            select
                                                newid() [ID],
                                                null [MEMBERSHIPCARDID],
                                                dbo.UFN_CONSTITUENT_BUILDNAME(MEMBER.CONSTITUENTID) [NAMEONCARD],
                                                null [EXPIRATIONDATE]
                                            for xml raw('ITEM'),type,elements,root('MEMBERSHIPCARDS'),binary base64
                                        )
                                end
                            from
                                dbo.MEMBER
                            where
                                MEMBERSHIP.ID = MEMBER.MEMBERSHIPID
                                and MEMBER.ISDROPPED = 0
                            for xml raw ('ITEM'), type, elements, root('MEMBERS'), binary base64
                        ),
                        @MEMBERSHIPLEVELS = (
                            select
                                MEMBERSHIPLEVEL.ID,
                                dbo.UFN_MEMBERSHIPTRANSACTION_DETERMINEACTIONCODE(MEMBERSHIP.ID, MEMBERSHIPLEVEL.ID, @TRANSACTIONDATE) [ACTIONCODE]
                            from
                                dbo.MEMBERSHIPLEVEL
                            where
                                MEMBERSHIP.MEMBERSHIPPROGRAMID = MEMBERSHIPLEVEL.MEMBERSHIPPROGRAMID
                            for xml raw('ITEM'),type,elements,root('MEMBERSHIPLEVELS'),binary base64
                        )
                    from
                        dbo.MEMBERSHIP
                    inner join
                        dbo.MEMBER on MEMBERSHIP.ID = MEMBER.MEMBERSHIPID and MEMBER.ISPRIMARY = 1
                    where
                        MEMBERSHIP.ID = @ID;

                    return 0;