USP_DATAFORMTEMPLATE_EDIT_MEMBERSHIPPROGRAMBASECURRENCY

The save procedure used by the edit dataform template "Membership Program Base Currency Edit Data Form".
Parameters

Parameter	Parameter Type	Mode	Description
@ID	uniqueidentifier	IN	The input ID parameter indicating the ID of the record being edited.
@CHANGEAGENTID	uniqueidentifier	IN	Input parameter indicating the ID of the change agent invoking the procedure.
@BASECURRENCYID	uniqueidentifier	IN	Base currency
Definition

Copy

CREATE procedure dbo.USP_DATAFORMTEMPLATE_EDIT_MEMBERSHIPPROGRAMBASECURRENCY (
    @ID uniqueidentifier,
    @CHANGEAGENTID uniqueidentifier = null,
    @BASECURRENCYID uniqueidentifier
)
as

    set nocount on;

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

    declare @CURRENTDATE datetime
    set @CURRENTDATE = getdate()

    begin try
        if exists(select 1 from dbo.MEMBERSHIP where MEMBERSHIP.MEMBERSHIPPROGRAMID = @ID)
        begin
            raiserror('The base currency of this membership program cannot be changed.', 13, 1);
            return 1;
        end

        if exists(select 1 from dbo.MEMBERSHIPLEVEL L inner join dbo.MEMBERSHIPLEVELBENEFIT B on L.ID = B.MEMBERSHIPLEVELID where L.MEMBERSHIPPROGRAMID = @ID)
        begin
            raiserror('BBERRR_CANNOT_CHANGE_BASECURRENCY_BENEFIT', 13, 1);
            return 1;
        end

        declare @OLDBASECURRENCYID uniqueidentifier;

        select 
            @OLDBASECURRENCYID = BASECURRENCYID
        from 
            dbo.MEMBERSHIPPROGRAM
        where
            ID = @ID

        --If the user saves without changing the base currency, don't update org amounts.
        if @OLDBASECURRENCYID <> @BASECURRENCYID
        begin
            declare @ORGCURRENCYID uniqueidentifier = dbo.UFN_CURRENCY_GETORGANIZATIONCURRENCY()

            --If the new base currency is the org currency, org amount equals amount, so don't bother getting
            --    an exchange rate or doing a conversion.
            if @BASECURRENCYID = @ORGCURRENCYID
            begin

                update dbo.MEMBERSHIPPROGRAM 
                set
                    ORGANIZATIONADDONADULTPRICE = ADDONADULTPRICE,
                    ORGANIZATIONADDONGUESTPRICE = ADDONGUESTPRICE,
                    ORGANIZATIONEXCHANGERATEID = null,
                    BASECURRENCYID = @BASECURRENCYID,
                    CHANGEDBYID = @CHANGEAGENTID,
                    DATECHANGED = @CURRENTDATE
                where 
                    ID = @ID

                update dbo.MEMBERSHIPLEVEL
                set
                    BASECURRENCYID = @BASECURRENCYID,
                    CHANGEDBYID = @CHANGEAGENTID,
                    DATECHANGED = @CURRENTDATE
                where
                    MEMBERSHIPPROGRAMID = @ID

                update dbo.MEMBERSHIPLEVELTERM 
                set
                    ORGANIZATIONAMOUNT = AMOUNT,
                    ORGANIZATIONEXCHANGERATEID = null,
                    BASECURRENCYID = @BASECURRENCYID,
                    CHANGEDBYID = @CHANGEAGENTID,
                    DATECHANGED = @CURRENTDATE
                from 
                    MEMBERSHIPLEVELTERM
                join dbo.MEMBERSHIPLEVEL
                    on MEMBERSHIPLEVELTERM.LEVELID = MEMBERSHIPLEVEL.ID and MEMBERSHIPPROGRAMID = @ID

            end
            else --Base currency has changed and isn't the org currency
            begin

                declare @CURRENCYEXCHANGERATEID uniqueidentifier;
                select 
                    @CURRENCYEXCHANGERATEID = dbo.UFN_CURRENCYEXCHANGERATE_GETLATEST(@BASECURRENCYID, @ORGCURRENCYID, MEMBERSHIPPROGRAM.DATEADDED, 0, null)
                from 
                    dbo.MEMBERSHIPPROGRAM
                where 
                    ID = @ID

                update dbo.MEMBERSHIPPROGRAM
                set
                    ORGANIZATIONADDONADULTPRICE = dbo.UFN_CURRENCY_CONVERT(ADDONADULTPRICE, @CURRENCYEXCHANGERATEID),
                    ORGANIZATIONADDONGUESTPRICE = dbo.UFN_CURRENCY_CONVERT(ADDONGUESTPRICE, @CURRENCYEXCHANGERATEID),
                    ORGANIZATIONEXCHANGERATEID = @CURRENCYEXCHANGERATEID,
                    BASECURRENCYID = @BASECURRENCYID,
                    CHANGEDBYID = @CHANGEAGENTID,
                    DATECHANGED = @CURRENTDATE
                where 
                    ID = @ID

                update dbo.MEMBERSHIPLEVEL
                set
                    BASECURRENCYID = @BASECURRENCYID,
                    CHANGEDBYID = @CHANGEAGENTID,
                    DATECHANGED = @CURRENTDATE
                where
                    MEMBERSHIPPROGRAMID = @ID

                update dbo.MEMBERSHIPLEVELTERM 
                set
                    ORGANIZATIONAMOUNT = dbo.UFN_CURRENCY_CONVERT(AMOUNT, dbo.UFN_CURRENCYEXCHANGERATE_GETLATEST(@BASECURRENCYID, @ORGCURRENCYID, MEMBERSHIPLEVELTERM.DATEADDED, 0, null)),
                    ORGANIZATIONEXCHANGERATEID = dbo.UFN_CURRENCYEXCHANGERATE_GETLATEST(@BASECURRENCYID, @ORGCURRENCYID, MEMBERSHIPLEVELTERM.DATEADDED, 0, null),
                    BASECURRENCYID = @BASECURRENCYID,
                    CHANGEDBYID = @CHANGEAGENTID,
                    DATECHANGED = @CURRENTDATE
                from 
                    MEMBERSHIPLEVELTERM
                join dbo.MEMBERSHIPLEVEL
                    on MEMBERSHIPLEVELTERM.LEVELID = MEMBERSHIPLEVEL.ID and MEMBERSHIPPROGRAMID = @ID

            end        
        end        
    end try
    begin catch
        exec dbo.USP_RAISE_ERROR
        return 1
    end catch

return 0;