USP_DATAFORMTEMPLATE_ADD_SALESORDERITEMMEMBERSHIP_MIDTERMUPGRADE

The save procedure used by the add dataform template "Sales Order Item Membership Midterm Upgrade Add Data Form".

Parameters

Parameter	Parameter Type	Mode	Description
@ID	uniqueidentifier	INOUT	The output parameter indicating the ID of the record added.
@SALESORDERID	uniqueidentifier	IN	Input parameter indicating the context ID for the record being added.
@CHANGEAGENTID	uniqueidentifier	IN	Input parameter indicating the ID of the change agent invoking the procedure.
@MEMBERSHIPID	uniqueidentifier	IN	Membership to upgrade
@MEMBERSHIPLEVELID	uniqueidentifier	IN	Level

Definition

Copy


CREATE procedure dbo.USP_DATAFORMTEMPLATE_ADD_SALESORDERITEMMEMBERSHIP_MIDTERMUPGRADE
(
    @ID uniqueidentifier = null output,
    @SALESORDERID uniqueidentifier,
    @CHANGEAGENTID uniqueidentifier = null,
    @MEMBERSHIPID uniqueidentifier = null,
    @MEMBERSHIPLEVELID uniqueidentifier = null
)
as

set nocount on;

-- Check if there is already a mid-term upgrade item in the cart for the same membership
if exists (
    select 1 from dbo.SALESORDERITEMMEMBERSHIP SOIM
        inner join dbo.SALESORDERITEM SOI on SOIM.ID = SOI.ID
    where SOI.SALESORDERID = @SALESORDERID and SOIM.MEMBERSHIPID = @MEMBERSHIPID
)
raiserror('ERR_MEMBERSHIPMIDTERMUPGRADE_DUPLICATEITEM', 13, 1)

if @ID is null
    set @ID = newid()

if @CHANGEAGENTID is null  
    exec dbo.USP_CHANGEAGENT_GETORCREATECHANGEAGENT @CHANGEAGENTID output

declare @CURRENTDATE datetime
set @CURRENTDATE = getdate()

declare @CURRENTLEVELTERMLENGTHCODE tinyint;
declare @CURRENTLEVELTERMTIMELENGTH tinyint;
declare @PRICE money;

declare @MEMBERSHIPPROGRAMID uniqueidentifier;
declare @MEMBERSHIPLEVELTERMID uniqueidentifier;
declare @MEMBERSHIPLEVELTYPECODEID uniqueidentifier;
declare @NUMBEROFCHILDREN smallint;
declare @COMMENTS nvarchar(1000);
declare @ISGIFT bit;
declare @SENDRENEWALCODE tinyint;
declare @EXPIRATIONDATE datetime;
declare @MEMBERS xml;
declare @GIVENBYID uniqueidentifier;
declare @NUMBEROFADDONADULTS smallint;
declare @NUMBEROFADDONGUESTS smallint;
declare @MEMBERSHIPCARDS xml;
declare @MEMBERSHIPPROGRAMADDON xml;
declare @ADDONMEMBERCOUNT int;

begin try
    select
        @MEMBERSHIPPROGRAMID = MEMBERSHIP.MEMBERSHIPPROGRAMID,
        @MEMBERSHIPLEVELTYPECODEID = MEMBERSHIP.MEMBERSHIPLEVELTYPECODEID,
        @NUMBEROFCHILDREN = MEMBERSHIP.NUMBEROFCHILDREN,
        @COMMENTS = '',
        @ISGIFT = MEMBERSHIP.ISGIFT,
        @SENDRENEWALCODE = MEMBERSHIP.SENDRENEWALCODE,
        @EXPIRATIONDATE = MEMBERSHIP.EXPIRATIONDATE,
        -- @MEMBERS = dbo.UFN_MEMBERSHIP_GETMEMBERS_TOITEMLISTXML(MEMBERSHIP.ID),
        @GIVENBYID = MEMBERSHIP.GIVENBYID,
        @NUMBEROFADDONADULTS = MEMBERSHIP.NUMBEROFADDONADULTS,
        @NUMBEROFADDONGUESTS = MEMBERSHIP.NUMBEROFADDONGUESTS,
        @CURRENTLEVELTERMLENGTHCODE = MEMBERSHIPLEVELTERM.TERMLENGTHCODE,
        @CURRENTLEVELTERMTIMELENGTH = MEMBERSHIPLEVELTERM.TERMTIMELENGTH
    from dbo.MEMBERSHIP
        inner join dbo.MEMBERSHIPLEVELTERM on MEMBERSHIP.MEMBERSHIPLEVELTERMID = MEMBERSHIPLEVELTERM.ID
    where MEMBERSHIP.ID = @MEMBERSHIPID


    select
        @MEMBERSHIPLEVELTERMID = ID
    from dbo.MEMBERSHIPLEVELTERM
    where
        LEVELID = @MEMBERSHIPLEVELID and
        TERMLENGTHCODE = @CURRENTLEVELTERMLENGTHCODE and
        TERMTIMELENGTH = @CURRENTLEVELTERMTIMELENGTH

    set @PRICE = dbo.UFN_MEMBERSHIP_GETMIDTERMUPGRADEPRICE(@MEMBERSHIPID, @MEMBERSHIPLEVELID)

    if @MEMBERSHIPLEVELTERMID is not null and @PRICE is not null
    begin
        exec dbo.USP_DAILYSALES_MEMBERSHIPRENEWAL_GETMEMBERSANDCARDS 
            @SALESORDERID, 
            @MEMBERSHIPID, 
            @MEMBERSHIPPROGRAMID, 
            @MEMBERSHIPLEVELID, 
            @MEMBERSHIPLEVELTERMID, 
            @MEMBERSHIPLEVELTYPECODEID, 
            @EXPIRATIONDATE, 
            @MEMBERS output, 
            @MEMBERSHIPCARDS output, 
            @MEMBERSHIPPROGRAMADDON output, 
            @ADDONMEMBERCOUNT output

        exec dbo.USP_SALESORDERITEMMEMBERSHIP_ADD2
            @ID output,
            @CHANGEAGENTID,
            @SALESORDERID,
            @MEMBERSHIPID,
            @MEMBERSHIPPROGRAMID,
            @MEMBERSHIPLEVELID,
            @MEMBERSHIPLEVELTERMID,
            @MEMBERSHIPLEVELTYPECODEID,
            @NUMBEROFCHILDREN,
            @COMMENTS,
            @ISGIFT,
            @SENDRENEWALCODE,
            @EXPIRATIONDATE,
            @MEMBERS,
            @MEMBERSHIPCARDS,
            @MEMBERSHIPPROGRAMADDON,
            @GIVENBYID,
            0,
            @PRICE,
            250,
            @ADDONMEMBERCOUNT; 
    end
end try

begin catch
    exec dbo.USP_RAISE_ERROR
    return 1
end catch

return 0