USP_DATAFORMTEMPLATE_EDITLOAD_SALESORDERITEMMEMBERSHIP

The load procedure used by the edit dataform template "Sales Order Item Membership Edit 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.
@TSLONG	bigint	INOUT	Output parameter indicating the TSLONG value of the record being edited. This is used to manage multi-user concurrency issues when multiple users access the same record.
@ORDERCONSTITUENTID	uniqueidentifier	INOUT
@ORDERCONSTITUENTNAME	nvarchar(154)	INOUT
@TRANSACTIONDATE	datetime	INOUT	Transaction date
@INITIALMEMBERSHIPID	uniqueidentifier	INOUT
@DESCRIPTION	nvarchar(255)	INOUT
@MEMBERSHIPID	uniqueidentifier	INOUT	Membership
@MEMBERSHIPPROGRAMID	uniqueidentifier	INOUT	Program
@MEMBERSHIPLEVELID	uniqueidentifier	INOUT	Level
@MEMBERSHIPLEVELTERMID	uniqueidentifier	INOUT	Term
@MEMBERSHIPLEVELTYPECODEID	uniqueidentifier	INOUT	Type
@NUMBEROFCHILDREN	smallint	INOUT	No. of children
@COMMENTS	nvarchar(1000)	INOUT	Comments
@ISGIFT	bit	INOUT	This membership is a gift
@SENDRENEWALCODE	tinyint	INOUT	Send renewal notice to
@EXPIRATIONDATE	datetime	INOUT	Expiration date
@GIVENBYID	uniqueidentifier	INOUT	Given by
@STATUSCODE	tinyint	INOUT
@MEMBERS	xml	INOUT	Members
@MEMBERSHIPLEVELS	xml	INOUT
@NUMBEROFADDONADULTS	smallint	INOUT	Number of additional members purchased
@NUMBEROFADDONGUESTS	smallint	INOUT	Number of guests purchased
@ISMIDTERMUPGRADE	bit	INOUT	Is mid-term upgrade
@MIDTERMUPGRADEPRICE	money	INOUT	Mid-term upgrade price

Definition

Copy



                    CREATE procedure dbo.USP_DATAFORMTEMPLATE_EDITLOAD_SALESORDERITEMMEMBERSHIP
                    (
                        @ID uniqueidentifier,
                        @DATALOADED bit = 0 output,
                        @TSLONG bigint = 0 output,
                        @ORDERCONSTITUENTID uniqueidentifier = null output,
                        @ORDERCONSTITUENTNAME nvarchar(154) = null output,
                        @TRANSACTIONDATE datetime = null output,
                        @INITIALMEMBERSHIPID uniqueidentifier = null output,
                        @DESCRIPTION nvarchar(255) = null output,
                        @MEMBERSHIPID uniqueidentifier = null output,
                        @MEMBERSHIPPROGRAMID uniqueidentifier = null output,
                        @MEMBERSHIPLEVELID uniqueidentifier = null output,
                        @MEMBERSHIPLEVELTERMID uniqueidentifier = null output,
                        @MEMBERSHIPLEVELTYPECODEID uniqueidentifier = null output,
                        @NUMBEROFCHILDREN smallint = null output,
                        @COMMENTS nvarchar(1000) = null output,
                        @ISGIFT bit = null output,
                        @SENDRENEWALCODE tinyint = null output,
                        @EXPIRATIONDATE datetime = null output,
                        @GIVENBYID uniqueidentifier = null output,
                        @STATUSCODE tinyint = null output,
                        @MEMBERS xml = null output,
                        @MEMBERSHIPLEVELS xml = null output,
                        @NUMBEROFADDONADULTS smallint = null output,
                        @NUMBEROFADDONGUESTS smallint = null output,
                        @ISMIDTERMUPGRADE bit = null output,
                        @MIDTERMUPGRADEPRICE money = null output
                    )
                    as
                        set nocount on;

                        set @DATALOADED = 0;
                        set @TSLONG = 0;

                        set @TRANSACTIONDATE = getdate();

                        select
                            @DATALOADED = 1,
                            @TSLONG = SALESORDERITEMMEMBERSHIP.TSLONG,
                            @ORDERCONSTITUENTID = CONSTITUENT.ID,
                            @ORDERCONSTITUENTNAME = CONSTITUENT.NAME,
                            @MEMBERSHIPID = isnull(SALESORDERITEMMEMBERSHIP.MEMBERSHIPID, '00000000-0000-0000-0000-000000000000'),
                            @DESCRIPTION = (
                                case
                                    when SALESORDERITEMMEMBERSHIP.MEMBERSHIPID is null then
                                        '<New membership>'
                                    else
                                        dbo.UFN_MEMBERSHIP_GETDESCRIPTION(SALESORDERITEMMEMBERSHIP.MEMBERSHIPID)
                                end
                            ),
                            @MEMBERSHIPPROGRAMID = SALESORDERITEMMEMBERSHIP.MEMBERSHIPPROGRAMID,
                            @MEMBERSHIPLEVELID = SALESORDERITEMMEMBERSHIP.MEMBERSHIPLEVELID,
                            @MEMBERSHIPLEVELTERMID = SALESORDERITEMMEMBERSHIP.MEMBERSHIPLEVELTERMID,
                            @MEMBERSHIPLEVELTYPECODEID = SALESORDERITEMMEMBERSHIP.MEMBERSHIPLEVELTYPECODEID,
                            @NUMBEROFCHILDREN = SALESORDERITEMMEMBERSHIP.NUMBEROFCHILDREN,
                            @EXPIRATIONDATE = MEMBERSHIP.EXPIRATIONDATE,
                            @COMMENTS = SALESORDERITEMMEMBERSHIP.COMMENTS,
                            @ISGIFT = SALESORDERITEMMEMBERSHIP.ISGIFT,
                            @SENDRENEWALCODE = SALESORDERITEMMEMBERSHIP.SENDRENEWALCODE,
                            @GIVENBYID = SALESORDERITEMMEMBERSHIP.GIVENBYID,
                            @STATUSCODE = isnull(MEMBERSHIP.STATUSCODE, 0),
                            @MEMBERS = (
                                select
                                        SALESORDERITEMMEMBER.ID,
                                        SALESORDERITEMMEMBER.MEMBERID,
                                        SALESORDERITEMMEMBER.CONSTITUENTID,
                                        SALESORDERITEMMEMBER.ISPRIMARY,
                                        (
                                            select
                                                SALESORDERITEMMEMBERSHIPCARD.ID,
                                                SALESORDERITEMMEMBERSHIPCARD.MEMBERSHIPCARDID,
                                                SALESORDERITEMMEMBERSHIPCARD.NAMEONCARD,
                                                SALESORDERITEMMEMBERSHIPCARD.EXPIRATIONDATE
                                            from
                                                dbo.SALESORDERITEMMEMBERSHIPCARD
                                            where
                                                SALESORDERITEMMEMBER.ID = SALESORDERITEMMEMBERSHIPCARD.SALESORDERITEMMEMBERID
                                            for xml raw('ITEM'),type,elements,root('MEMBERSHIPCARDS'),binary base64
                                        ),
                                        SALESORDERITEMMEMBER.ISCAREGIVER
                                    from
                                        dbo.SALESORDERITEMMEMBER
                                    where
                                        SALESORDERITEMMEMBERSHIP.ID = SALESORDERITEMMEMBER.SALESORDERITEMMEMBERSHIPID
                                    for xml raw('ITEM'),type,elements,root('MEMBERS'),binary base64
                            ),
                            @MEMBERSHIPLEVELS = (
                                select
                                    MEMBERSHIPLEVEL.ID,
                                    dbo.UFN_MEMBERSHIPTRANSACTION_DETERMINEACTIONCODE(SALESORDERITEMMEMBERSHIP.MEMBERSHIPID, MEMBERSHIPLEVEL.ID, @TRANSACTIONDATE) [ACTIONCODE]
                                from
                                    dbo.MEMBERSHIPLEVEL
                                where
                                    SALESORDERITEMMEMBERSHIP.MEMBERSHIPPROGRAMID = MEMBERSHIPLEVEL.MEMBERSHIPPROGRAMID
                                for xml raw('ITEM'),type,elements,root('MEMBERSHIPLEVELS'),binary base64
                            ),
                            @ISMIDTERMUPGRADE = case SALESORDERITEMMEMBERSHIP.TYPECODE
                                                    when 1 then 0
                                                    else 1
                                                end,
                            @MIDTERMUPGRADEPRICE = SALESORDERITEM.TOTAL
                        from
                            dbo.SALESORDERITEMMEMBERSHIP
                        inner join
                            dbo.SALESORDERITEM on SALESORDERITEMMEMBERSHIP.ID = SALESORDERITEM.ID
                        inner join
                            dbo.SALESORDER on SALESORDERITEM.SALESORDERID = SALESORDER.ID
                        left outer join
                            dbo.CONSTITUENT on SALESORDER.CONSTITUENTID = CONSTITUENT.ID
                        left outer join
                            dbo.MEMBERSHIP on SALESORDERITEMMEMBERSHIP.MEMBERSHIPID = MEMBERSHIP.ID
                        where
                            SALESORDERITEMMEMBERSHIP.ID = @ID;

                        set @INITIALMEMBERSHIPID = @MEMBERSHIPID;

                        select
                            @NUMBEROFADDONADULTS = isnull(SALESORDERITEM.QUANTITY, 0)
                        from dbo.SALESORDERITEMMEMBERSHIPADDON
                            inner join dbo.SALESORDERITEM on SALESORDERITEMMEMBERSHIPADDON.ID = SALESORDERITEM.ID
                        where SALESORDERITEMMEMBERSHIPADDON.SALESORDERITEMMEMBERSHIPID = @ID and SALESORDERITEMMEMBERSHIPADDON.ADDONTYPECODE = 0;

                        select
                            @NUMBEROFADDONGUESTS = isnull(SALESORDERITEM.QUANTITY, 0)
                        from dbo.SALESORDERITEMMEMBERSHIPADDON
                            inner join dbo.SALESORDERITEM on SALESORDERITEMMEMBERSHIPADDON.ID = SALESORDERITEM.ID
                        where SALESORDERITEMMEMBERSHIPADDON.SALESORDERITEMMEMBERSHIPID = @ID and SALESORDERITEMMEMBERSHIPADDON.ADDONTYPECODE = 1;

                        return 0;