USP_DATAFORMTEMPLATE_EDITLOAD_DAILYSALESORDERITEMMEMBERSHIP

Parameters

Parameter	Parameter Type	Mode
@ID	uniqueidentifier	IN
@DATALOADED	bit	INOUT
@TSLONG	bigint	INOUT
@ORDERCONSTITUENTID	uniqueidentifier	INOUT
@ORDERCONSTITUENTNAME	nvarchar(200)	INOUT
@MEMBERSHIPID	uniqueidentifier	INOUT
@MEMBERSHIPPROGRAMID	uniqueidentifier	INOUT
@MEMBERSHIPLEVELID	uniqueidentifier	INOUT
@MEMBERSHIPLEVELTERMID	uniqueidentifier	INOUT
@MEMBERSHIPLEVELTYPECODEID	uniqueidentifier	INOUT
@COMMENTS	nvarchar(1000)	INOUT
@ISGIFT	bit	INOUT
@SENDRENEWALCODE	tinyint	INOUT
@EXPIRATIONDATE	datetime	INOUT
@MEMBERS	xml	INOUT
@MEMBERSHIPCARDS	xml	INOUT
@MEMBERSHIPPROGRAMADDON	xml	INOUT
@GIVENBYID	uniqueidentifier	INOUT
@STATUSCODE	tinyint	INOUT
@NUMBEROFCHILDREN	tinyint	INOUT
@PRICE	money	INOUT
@HASADDONS	bit	INOUT
@TOTALADDITIONALMEMBERS	tinyint	INOUT
@ISMIDTERMUPGRADE	bit	INOUT
@MIDTERMUPGRADEPRICE	money	INOUT
@LOADEDADDONS	xml	INOUT
@ACTIONCODES	xml	INOUT
@EXPIRATIONDATES	xml	INOUT
@CURRENTACTIONCODE	tinyint	INOUT
@CURRENTMEMBERSHIPEXPIRATIONDATE	date	INOUT

Definition

Copy


CREATE procedure dbo.USP_DATAFORMTEMPLATE_EDITLOAD_DAILYSALESORDERITEMMEMBERSHIP(
    @ID uniqueidentifier,
    @DATALOADED bit = 0 output,
    @TSLONG bigint = 0 output,
    @ORDERCONSTITUENTID uniqueidentifier = null output,
    @ORDERCONSTITUENTNAME nvarchar(200) = null output,
    @MEMBERSHIPID uniqueidentifier = null output,
    @MEMBERSHIPPROGRAMID uniqueidentifier = null output,
    @MEMBERSHIPLEVELID uniqueidentifier = null output,
    @MEMBERSHIPLEVELTERMID uniqueidentifier = null output,
    @MEMBERSHIPLEVELTYPECODEID uniqueidentifier = null output,
    @COMMENTS nvarchar(1000) = null output,
    @ISGIFT bit = null output,
    @SENDRENEWALCODE tinyint = null output,
    @EXPIRATIONDATE datetime = null output,
    @MEMBERS xml = null output,
    @MEMBERSHIPCARDS xml = null output,
    @MEMBERSHIPPROGRAMADDON xml = null output,
    @GIVENBYID uniqueidentifier = null output,
    @STATUSCODE tinyint = null output,
    @NUMBEROFCHILDREN tinyint = null output,
    @PRICE money = null output,
    @HASADDONS bit = null output,
    @TOTALADDITIONALMEMBERS tinyint = null output,
    @ISMIDTERMUPGRADE bit = null output,
    @MIDTERMUPGRADEPRICE money = null output,
    @LOADEDADDONS xml = null output,
    @ACTIONCODES xml = null output,
    @EXPIRATIONDATES xml = null output,
    @CURRENTACTIONCODE tinyint = null output,
    @CURRENTMEMBERSHIPEXPIRATIONDATE date = null output
)
as

    set nocount on;

    declare @TRANSACTIONDATE datetime = getdate();
    declare @INITIALMEMBERSHIPLEVELID uniqueidentifier = null;

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

    select
        @DATALOADED = 1,
        @TSLONG = SALESORDERITEMMEMBERSHIP.TSLONG,
        @ORDERCONSTITUENTID = CONSTITUENT.ID,
        @ORDERCONSTITUENTNAME = CONSTITUENT.NAME,
        @MEMBERSHIPID = SALESORDERITEMMEMBERSHIP.MEMBERSHIPID,
        @MEMBERSHIPPROGRAMID = SALESORDERITEMMEMBERSHIP.MEMBERSHIPPROGRAMID,
        @MEMBERSHIPLEVELID = SALESORDERITEMMEMBERSHIP.MEMBERSHIPLEVELID,
        @MEMBERSHIPLEVELTERMID = SALESORDERITEMMEMBERSHIP.MEMBERSHIPLEVELTERMID,
        @MEMBERSHIPLEVELTYPECODEID = SALESORDERITEMMEMBERSHIP.MEMBERSHIPLEVELTYPECODEID,
        @NUMBEROFCHILDREN = SALESORDERITEMMEMBERSHIP.NUMBEROFCHILDREN,
        @EXPIRATIONDATE = SALESORDERITEMMEMBERSHIP.EXPIRATIONDATE,
        @COMMENTS = SALESORDERITEMMEMBERSHIP.COMMENTS,
        @ISGIFT = SALESORDERITEMMEMBERSHIP.ISGIFT,
        @SENDRENEWALCODE = SALESORDERITEMMEMBERSHIP.SENDRENEWALCODE,
        @GIVENBYID = SALESORDERITEMMEMBERSHIP.GIVENBYID,
        @STATUSCODE = isnull(MEMBERSHIP.STATUSCODE, 0),        
        @MEMBERS = (
            select
                SALESORDERITEMMEMBER.ID,
                SALESORDERITEMMEMBER.MEMBERID as MEMBERID,
                SALESORDERITEMMEMBER.CONSTITUENTID,
                SALESORDERITEMMEMBER.ISPRIMARY
            from
                dbo.SALESORDERITEMMEMBER
            where
                SALESORDERITEMMEMBERSHIP.ID = SALESORDERITEMMEMBER.SALESORDERITEMMEMBERSHIPID
            for xml raw('ITEM'),type,elements,root('MEMBERS'),binary base64
        ),
        @MEMBERSHIPCARDS = (
            select
                SALESORDERITEMMEMBERSHIPCARD.ID,
                SALESORDERITEMMEMBER.ID as MEMBERID,
                SALESORDERITEMMEMBERSHIPCARD.MEMBERSHIPCARDID,
                SALESORDERITEMMEMBERSHIPCARD.NAMEONCARD,
                SALESORDERITEMMEMBERSHIPCARD.EXPIRATIONDATE
            from
                dbo.SALESORDERITEMMEMBERSHIPCARD
            inner join 
                dbo.SALESORDERITEMMEMBER on SALESORDERITEMMEMBER.ID = SALESORDERITEMMEMBERSHIPCARD.SALESORDERITEMMEMBERID
            where
                SALESORDERITEMMEMBERSHIP.ID = SALESORDERITEMMEMBER.SALESORDERITEMMEMBERSHIPID
            for xml raw('ITEM'),type,elements,root('MEMBERSHIPCARDS'),binary base64
        ),
        @LOADEDADDONS = (
            select
                SALESORDERITEMMEMBERSHIPADDON.ID,
                SALESORDERITEMMEMBERSHIPADDON.ADDONID,
                SALESORDERITEM.QUANTITY
            from
                dbo.SALESORDERITEMMEMBERSHIPADDON
            inner join
                dbo.SALESORDERITEM on SALESORDERITEM.ID = SALESORDERITEMMEMBERSHIPADDON.ID
            where
                SALESORDERITEMMEMBERSHIPADDON.SALESORDERITEMMEMBERSHIPID = @ID
            for xml raw('ITEM'),type,elements,root('LOADEDADDONS'),binary base64
        ),
        @ISMIDTERMUPGRADE = case SALESORDERITEMMEMBERSHIP.TYPECODE
                                when 1 then 0
                                else 1
                            end,
        @MIDTERMUPGRADEPRICE = SALESORDERITEM.TOTAL,
        @CURRENTMEMBERSHIPEXPIRATIONDATE = MEMBERSHIP.EXPIRATIONDATE
    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;

    select
        @INITIALMEMBERSHIPLEVELID = MEMBERSHIPLEVELID
    from
        dbo.MEMBERSHIP
    where
        ID = @MEMBERSHIPID;

    if @ISMIDTERMUPGRADE = 1
    begin
        set @CURRENTACTIONCODE = 250;
    end
    else
    begin
        set @CURRENTACTIONCODE = dbo.UFN_MEMBERSHIPTRANSACTION_DETERMINEACTIONCODE(@MEMBERSHIPID, @MEMBERSHIPLEVELID, @TRANSACTIONDATE);

        if @CURRENTACTIONCODE = 0 or @CURRENTACTIONCODE = 5
        begin
            set @ACTIONCODES = null;
            set @EXPIRATIONDATES = null;
        end
        else
        begin
            set @ACTIONCODES = dbo.UFN_MEMBERSHIPSALESACTIONCODE_TOITEMLISTXML(@MEMBERSHIPPROGRAMID, @INITIALMEMBERSHIPLEVELID, @CURRENTMEMBERSHIPEXPIRATIONDATE, @CURRENTACTIONCODE, @TRANSACTIONDATE);
            set @EXPIRATIONDATES = dbo.UFN_MEMBERSHIPSALESGETEXPIRATIONDATES_TOITEMLISTXML(@MEMBERSHIPPROGRAMID, @CURRENTMEMBERSHIPEXPIRATIONDATE);        
        end
    end

    return 0;