USP_DATAFORMTEMPLATE_ADD_MEMBERSHIPPROGRAMCONFIGURATIONWIZARD

Parameters

Parameter	Parameter Type	Mode
@ID	uniqueidentifier	INOUT
@CHANGEAGENTID	uniqueidentifier	IN
@PROGRAMNAME	nvarchar(100)	IN
@CATEGORY	tinyint	IN
@DESCRIPTION	nvarchar(255)	IN
@SITEID	uniqueidentifier	IN
@ANNUAL	bit	IN
@RECURRINGSUSTAINING	bit	IN
@LIFETIME	bit	IN
@DUESBASED	bit	IN
@CONTRIBUTIONSBASED	bit	IN
@BOTHWAYSBASED	bit	IN
@SINGLETERM	bit	IN
@SINGLETERMLENGTH	tinyint	IN
@SINGLETERMLENGTHCODE	tinyint	IN
@MULTIPLETERMS	bit	IN
@MEMBERSHIPLEVELS	xml	IN
@ADDONS	xml	IN
@MEMBERSHIPLEVELTERMS	xml	IN
@RECURRINGPRICES	xml	IN
@CARDFORMAT	nvarchar(255)	IN
@NAMEFORMATLIST	uniqueidentifier	IN
@ENTIREAMOUNT	bit	IN
@PARTIALAMOUNT	bit	IN
@NOAMOUNT	bit	IN
@DUESTREATEDASCONTRIBUTION	bit	IN
@DUESONEPAYMENTEACHTERM	bit	IN
@DUESMULTIPLEPAYMENTSEACHTERM	bit	IN
@DESIGNATIONSFORCONTRIBUTEDPORTION	xml	IN
@DISCOUNTSFORPROGRAM	xml	IN
@MEMBERSHIPTERMTYPE	tinyint	IN
@DUESBASEDEXPIRATIONDATETYPE	tinyint	IN
@DUESBASEDEXPIRATIONDATE	UDT_MONTHDAY	IN
@CONTRIBUTIONISMEMBERSHIPDUECOUNTED	bit	IN
@CONTRIBUTIONISEVENTREGISTRATIONCOUNTED	bit	IN
@CONTRIBUTIONINCLUDEPAYMENTACTIVITYTYPECODE	tinyint	IN
@CONTRIBUTIONISRECURRINGGIFTCOUNTED	bit	IN
@CONTRIBUTIONISPLEDGECOUNTED	bit	IN
@CONTRIBUTIONISPAYMENTCOUNTED	bit	IN
@CONTRIBUTIONINCLUDEEVENTTYPECODE	tinyint	IN
@CONTRIBUTIONINCLUDEMEMBERSHIPTYPECODE	tinyint	IN
@CONTRIBUTIONEVENTCATEGORYCODEID	uniqueidentifier	IN
@CONTRIBUTIONMEMBERSHIPPROGRAMID	uniqueidentifier	IN
@CONTRIBUTIONDESIGNATIONID	uniqueidentifier	IN
@CONTRIBUTIONIDSETREGISTERID	uniqueidentifier	IN
@CONTRIBUTIONISMULTIPLEREVENUETRANSACTIONSCOUNTED	bit	IN
@EXPIRATIONENDOFPRIORMONTH	bit	IN
@EXPIRATIONENDOFPRIORMONTHCUTOFF	tinyint	IN
@RENEWALWINDOWSTART	tinyint	IN
@AUTOMATICRENEWALRADIO	tinyint	IN
@AUTOMATICRENEWALNUMBERDAY	tinyint	IN
@AUTOMATICRENEWALNUMBERMONTH	tinyint	IN
@AUTOMATICRENEWALTIMETYPE	tinyint	IN
@AUTOMATICRENEWALDAYOFMONTH	int	IN
@RENEWALWINDOWEND	tinyint	IN
@AFTEREXPIRATIONCLASSIFICATION1	tinyint	IN
@AFTEREXPIRATIONCLASSIFICATION2	tinyint	IN
@AFTEREXPIRATIONCLASSIFICATION3	tinyint	IN
@AFTEREXPIRATIONTIMEPERIOD1	tinyint	IN
@AFTEREXPIRATIONTIMEPERIOD2	tinyint	IN
@AFTEREXPIRATIONTIMEPERIOD3	tinyint	IN
@REVENUEAFTERRENEWALWINDOWTYPE	tinyint	IN
@CLASSIFICATIONAFTERRENEWALWINDOWTYPE	tinyint	IN
@BASECURRENCYID	uniqueidentifier	IN
@GLOBALBENEFITS	xml	IN
@WHATHAPPENSIFTHEYGIVEMORECODE	tinyint	IN
@WHATDATETOCALCULATEEXPIRATIONDATECODE	tinyint	IN
@MULTIPLETERMSUNITS	xml	IN
@ANNUALLYPAYMENTOPTION	bit	IN
@QUARTERLYPAYMENTOPTION	bit	IN
@SEMIANNUALLYPAYMENTOPTION	bit	IN
@MONTHLYPAYMENTOPTION	bit	IN
@LIFETIMEPAYMENTOPTIONS	xml	IN
@ADDONSUSED	bit	IN
@REPORTCATALOGID	uniqueidentifier	IN
@LETTERTEMPLATEID	uniqueidentifier	IN
@ISTYPEPROGRAM	bit	IN
@WHEREISREVENUETRACKED	tinyint	IN
@EXPIRATIONDATES	xml	IN
@INSTALLMENTPOSTSTATUSCODE	tinyint	IN
@CONTRIBUTIONCRITERIADEFINITIONTYPECODE	tinyint	IN
@CONTRIBUTIONAPPLICATIONSELECTIONID	uniqueidentifier	IN
Definition

Copy

                CREATE procedure dbo.USP_DATAFORMTEMPLATE_ADD_MEMBERSHIPPROGRAMCONFIGURATIONWIZARD
                (
                    @ID uniqueidentifier = null output,
                    @CHANGEAGENTID uniqueidentifier = null,
                    @PROGRAMNAME nvarchar(100) = '',
                    @CATEGORY tinyint = 9,
                    @DESCRIPTION nvarchar(255) = '',
                    @SITEID uniqueidentifier = null,
                    @ANNUAL bit = 1,
                    @RECURRINGSUSTAINING bit = 0,
                    @LIFETIME bit = 0,
                    @DUESBASED bit = 1,
                    @CONTRIBUTIONSBASED bit = 0,
                    @BOTHWAYSBASED bit = 0,
                    @SINGLETERM bit = 1,
                    @SINGLETERMLENGTH tinyint = 1,
                    @SINGLETERMLENGTHCODE tinyint = 1,
                    @MULTIPLETERMS bit = 0,
                    @MEMBERSHIPLEVELS XML = null,
                    @ADDONS XML = null,
                    @MEMBERSHIPLEVELTERMS xml = null,
                    @RECURRINGPRICES xml = null,
                    @CARDFORMAT nvarchar(255) = null,
                    @NAMEFORMATLIST uniqueidentifier = null,
                    @ENTIREAMOUNT bit = 1,
                    @PARTIALAMOUNT bit = 0,
                    @NOAMOUNT bit = 0,
                    @DUESTREATEDASCONTRIBUTION bit = 0,
                    @DUESONEPAYMENTEACHTERM bit = 1,
                    @DUESMULTIPLEPAYMENTSEACHTERM bit = 0,
                    @DESIGNATIONSFORCONTRIBUTEDPORTION xml = null,
                    @DISCOUNTSFORPROGRAM xml = null,
                    @MEMBERSHIPTERMTYPE tinyint = 0,
                    @DUESBASEDEXPIRATIONDATETYPE tinyint = 0,
                    @DUESBASEDEXPIRATIONDATE dbo.UDT_MONTHDAY = 0000,
                    @CONTRIBUTIONISMEMBERSHIPDUECOUNTED bit = 0,
                    @CONTRIBUTIONISEVENTREGISTRATIONCOUNTED bit = 0,
                    @CONTRIBUTIONINCLUDEPAYMENTACTIVITYTYPECODE tinyint = 0,
                    @CONTRIBUTIONISRECURRINGGIFTCOUNTED bit = 0,
                    @CONTRIBUTIONISPLEDGECOUNTED bit = 0,
                    @CONTRIBUTIONISPAYMENTCOUNTED bit = 0,
                    @CONTRIBUTIONINCLUDEEVENTTYPECODE tinyint = 0,
                    @CONTRIBUTIONINCLUDEMEMBERSHIPTYPECODE tinyint = 0,
                    @CONTRIBUTIONEVENTCATEGORYCODEID uniqueidentifier = null,
                    @CONTRIBUTIONMEMBERSHIPPROGRAMID uniqueidentifier = null,
                    @CONTRIBUTIONDESIGNATIONID uniqueidentifier = null,
                    @CONTRIBUTIONIDSETREGISTERID uniqueidentifier = null,
                    @CONTRIBUTIONISMULTIPLEREVENUETRANSACTIONSCOUNTED bit = 0,
                    @EXPIRATIONENDOFPRIORMONTH bit = 0,
                    @EXPIRATIONENDOFPRIORMONTHCUTOFF tinyint = 1,
                    @RENEWALWINDOWSTART tinyint = 0,
                    @AUTOMATICRENEWALRADIO tinyint = 0,
                    @AUTOMATICRENEWALNUMBERDAY tinyint = 0,
                    @AUTOMATICRENEWALNUMBERMONTH tinyint = 0,
                    @AUTOMATICRENEWALTIMETYPE tinyint = 0,
                    @AUTOMATICRENEWALDAYOFMONTH int = 1,
                    @RENEWALWINDOWEND tinyint = 0,
                    @AFTEREXPIRATIONCLASSIFICATION1 tinyint = null,
                    @AFTEREXPIRATIONCLASSIFICATION2 tinyint = null,
                    @AFTEREXPIRATIONCLASSIFICATION3 tinyint = null,
                    @AFTEREXPIRATIONTIMEPERIOD1 tinyint = null,
                    @AFTEREXPIRATIONTIMEPERIOD2 tinyint = null,
                    @AFTEREXPIRATIONTIMEPERIOD3 tinyint = null,
                    @REVENUEAFTERRENEWALWINDOWTYPE tinyint = 0,
                    @CLASSIFICATIONAFTERRENEWALWINDOWTYPE tinyint = 4,
                    @BASECURRENCYID uniqueidentifier = null,
                    @GLOBALBENEFITS xml = null,
                    @WHATHAPPENSIFTHEYGIVEMORECODE tinyint = 0,
                    @WHATDATETOCALCULATEEXPIRATIONDATECODE tinyint = 0,
                    @MULTIPLETERMSUNITS xml = null,
                    @ANNUALLYPAYMENTOPTION bit = 1,
                    @QUARTERLYPAYMENTOPTION bit = 0,
                    @SEMIANNUALLYPAYMENTOPTION bit = 0,
                    @MONTHLYPAYMENTOPTION bit = 0,
                    @LIFETIMEPAYMENTOPTIONS XML = null,
                    @ADDONSUSED bit = 0,
                    @REPORTCATALOGID uniqueidentifier = null,
                    @LETTERTEMPLATEID uniqueidentifier = null,
                    @ISTYPEPROGRAM bit = 0,
                    @WHEREISREVENUETRACKED tinyint = 0,
                    @EXPIRATIONDATES xml = null,
                    @INSTALLMENTPOSTSTATUSCODE tinyint = 3,
                    @CONTRIBUTIONCRITERIADEFINITIONTYPECODE tinyint = 0,
                    @CONTRIBUTIONAPPLICATIONSELECTIONID uniqueidentifier = null
                )
                as

                set nocount on;

                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 @RENEWALINFOID uniqueidentifier

                declare @MEMBERSHIPTERMTYPE_DATEVARIESWITHSTARTDATE tinyint = 0
                declare @MEMBERSHIPTERMTYPE_DATEISFIXED tinyint = 1

                --Default do not post if no post status is provided
                if isnull(@INSTALLMENTPOSTSTATUSCODE,0) = 0
                    set @INSTALLMENTPOSTSTATUSCODE = 3

                begin try

                    -- save program
                    insert into dbo.MEMBERSHIPPROGRAM
                    (
                        ID,
                        NAME,
                        DESCRIPTION,
                        SITEID,
                        ISACTIVE,
                        EXPIRESONCODE,
                        BACKDATEMEMBERSHIPS,
                        CUTOFFDAY,
                        CATEGORYCODE,
                        PROGRAMTYPECODE,
                        PROGRAMBASEDONCODE,
                        CARDFORMAT,
                        NAMEFORMATID,
                        DEDUCTIBILITYCODE,
                        DUESTREATEDASCONTRIBUTION,
                        ONEPAYMENTEACHTERM,
                        MULTIPLEPAYMENTSEACHTERM,
                        RENEWALWINDOWSTARTTYPECODE,
                        RENEWALWINDOWSTARTTIMECODE,
                        RENEWALWINDOWSTARTINTERVALCODE,
                        RENEWALWINDOWSTARTCUTOFFDAY,
                        RENEWALWINDOWREVENUETYPECODE,
                        NONRENEWALACTIONTYPECODE,
                        INSTALLMENTPOSTSTATUSCODE,
                        ADDEDBYID,
                        CHANGEDBYID,
                        DATEADDED,
                        DATECHANGED,
                        BASECURRENCYID,
                        REPORTCATALOGID,
                        LETTERTEMPLATEID,
                        ISTYPEPROGRAM,
                        WHEREISREVENUETRACKEDCODE,
                        MULTIPLETERMS
                    )
                    values
                    (
                        @ID,
                        @PROGRAMNAME,
                        @DESCRIPTION,
                        @SITEID,
                        1,
                        @DUESBASEDEXPIRATIONDATETYPE,
                        @EXPIRATIONENDOFPRIORMONTH,
                        case when @EXPIRATIONENDOFPRIORMONTH = 1 then @EXPIRATIONENDOFPRIORMONTHCUTOFF else 0 end,
                        @CATEGORY,
                        case when @ANNUAL = 1 then 0 when @RECURRINGSUSTAINING = 1 then 1 when @LIFETIME = 1 then 2 end,
                        case when @DUESBASED = 1 then 0 when @CONTRIBUTIONSBASED = 1 then 1 when @BOTHWAYSBASED = 1 then 2 end,
                        @CARDFORMAT,
                        @NAMEFORMATLIST,
                   case when @ENTIREAMOUNT = 1 then 0 when @PARTIALAMOUNT = 1 then 1 when @NOAMOUNT = 1 then 2 end,
                        @DUESTREATEDASCONTRIBUTION,
                        @DUESONEPAYMENTEACHTERM,
                        case when @LIFETIME = 1 then 1 else @DUESMULTIPLEPAYMENTSEACHTERM end,
                        @AUTOMATICRENEWALRADIO,
                        case when @AUTOMATICRENEWALTIMETYPE = 0 then @AUTOMATICRENEWALNUMBERDAY else @AUTOMATICRENEWALNUMBERMONTH end,
                        @AUTOMATICRENEWALTIMETYPE,
                        case when @AUTOMATICRENEWALRADIO = 1 then @AUTOMATICRENEWALDAYOFMONTH else 1 end,
                        @REVENUEAFTERRENEWALWINDOWTYPE,
                        @CLASSIFICATIONAFTERRENEWALWINDOWTYPE,
                        @INSTALLMENTPOSTSTATUSCODE,
                        @CHANGEAGENTID,
                        @CHANGEAGENTID,
                        @CURRENTDATE,
                        @CURRENTDATE,
                        @BASECURRENCYID,
                        @REPORTCATALOGID,
                        @LETTERTEMPLATEID,
                        @ISTYPEPROGRAM,
                        @WHEREISREVENUETRACKED,
                        @MULTIPLETERMS
                    )

                    if @MEMBERSHIPTERMTYPE = @MEMBERSHIPTERMTYPE_DATEISFIXED
                    begin
                        insert into dbo.MEMBERSHIPPROGRAMENDDATE
                        (
                            ID,
                            MEMBERSHIPPROGRAMID,
                            EXPIRATIONDATE,
                            PUSHNEXTDATE,
                            SEQUENCE,
                            ADDEDBYID,
                            CHANGEDBYID,
                            DATEADDED,
                            DATECHANGED
                        )
                        select
                            newid(),
                            @ID as MEMBERSHIPPROGRAMID,
                            T.c.value('(EXPIRATIONDATE)[1]', 'char(4)') as EXPIRATIONDATE,
                            T.c.value('(PUSHNEXTDATE)[1]', 'char(4)') as PUSHNEXTDATE,
                            row_number() over (order by T.c.value('(EXPIRATIONDATE)[1]', 'char(4)'), T.c.value('(PUSHNEXTDATE)[1]', 'char(4)')) as SEQUENCE,
                            @CHANGEAGENTID,
                            @CHANGEAGENTID,
                            @CURRENTDATE,
                            @CURRENTDATE
                        from @EXPIRATIONDATES.nodes('/EXPIRATIONDATES/ITEM') T(c)
                    end

                    -- save levels
                    insert into dbo.MEMBERSHIPLEVEL
                    (
                        ID,
                        NAME,
                        MEMBERSHIPPROGRAMID,
                        DESCRIPTION,
                        TIERCODEID,
                        MEMBERSALLOWED,
                        CHILDRENALLOWED,
                        CARDSALLOWED,
                        ISACTIVE,
                        FORCEMANUALDOWNGRADES,
                        RENEWALWINDOWREVENUETYPECODE,
                        NONRENEWALACTIONTYPECODE,
                        MEMBERSHIPTERMTYPECODE,
                        BEFOREEXPIRATION,
                        AFTEREXPIRATION,
                        RENEWALWINDOWSTARTTYPECODE,
                        RENEWALWINDOWSTARTTIMECODE,
                        RENEWALWINDOWSTARTINTERVALCODE,
                        RENEWALWINDOWSTARTCUTOFFDAY,
                        ADDEDBYID,
                        CHANGEDBYID,
                        DATEADDED,
                        DATECHANGED,
                        SEQUENCE,
                        RECEIPTAMOUNT,
                        OBTAINLEVELCODE,
                        BASECURRENCYID
                    )
                    select
                        T.c.value('(ID)[1]', 'uniqueidentifier'),
                        T.c.value('(NAME)[1]', 'nvarchar(100)'),
                        @ID,
                        T.c.value('(DESCRIPTION)[1]', 'nvarchar(255)'),
                        T.c.value('(TIER)[1]', 'uniqueidentifier'),
                        T.c.value('(MEMBERSALLOWED)[1]', 'smallint'),
                        T.c.value('(CHILDRENALLOWED)[1]', 'smallint'),
                        T.c.value('(CARDSALLOWED)[1]', 'smallint'),
                        1,
            T.c.value('(CANDEMOTE)[1]', 'bit'),
                        @REVENUEAFTERRENEWALWINDOWTYPE,
                        @CLASSIFICATIONAFTERRENEWALWINDOWTYPE,
                        @MEMBERSHIPTERMTYPE,
                        @RENEWALWINDOWSTART,
                        @RENEWALWINDOWEND,
                        @AUTOMATICRENEWALRADIO,
                        case when @AUTOMATICRENEWALTIMETYPE = 0 then @AUTOMATICRENEWALNUMBERDAY else @AUTOMATICRENEWALNUMBERMONTH end,
                        @AUTOMATICRENEWALTIMETYPE,
                        case when @AUTOMATICRENEWALRADIO = 1 then @AUTOMATICRENEWALDAYOFMONTH else 1 end,
                        @CHANGEAGENTID,
                        @CHANGEAGENTID,
                        @CURRENTDATE,
                        @CURRENTDATE,
                        T.c.value('(SEQUENCE)[1]', 'int'),
                        T.c.value('(RECEIPTAMOUNT[1])','money'),
                        case when @DUESBASED = 1 then 0 when @CONTRIBUTIONSBASED = 1 then 1 when @BOTHWAYSBASED = 1 then T.c.value('(HOWPEOPLEOBTAINLEVEL)[1]','tinyint') end,
                        @BASECURRENCYID
                    from @MEMBERSHIPLEVELS.nodes('/MEMBERSHIPLEVELS/ITEM') T(c)

                    --Save benefits
                    insert into dbo.MEMBERSHIPLEVELBENEFIT
                    (
                        ID, MEMBERSHIPLEVELID, BENEFITID,
                        QUANTITY, UNITVALUE, DETAILS,
                        ADDEDBYID, CHANGEDBYID, DATEADDED,
                        DATECHANGED, USEPERCENT, VALUEPERCENT,
                        BASECURRENCYID, FREQUENCYCODE, NUMBERTOOFFERCODE
                    )
                    select
                        newid(), T.c.value('(MEMBERSHIPLEVELID)[1]','uniqueidentifier'), T.c.value('(BENEFITID)[1]','uniqueidentifier'),
                        T.c.value('(QUANTITY)[1]','int'), T.c.value('(UNITVALUE)[1]','money'), coalesce(T.c.value('(DETAILS)[1]','nvarchar(255)'),''),
                        @CHANGEAGENTID, @CHANGEAGENTID, @CURRENTDATE,
                        @CURRENTDATE, T.c.value('(USEPERCENT)[1]','bit'), T.c.value('(VALUEPERCENT)[1]','decimal'),
                        BENEFIT.BASECURRENCYID, T.c.value('(FREQUENCY)[1]','tinyint'), T.c.value('(NUMBERTOOFFER)[1]','tinyint')

                    from
                        @GLOBALBENEFITS.nodes('/GLOBALBENEFITS/ITEM') T(c)
            inner join dbo.BENEFIT on BENEFIT.ID = T.c.value('(BENEFITID)[1]','uniqueidentifier')

                    if @AFTEREXPIRATIONCLASSIFICATION1 is not null
                    begin
                        set @RENEWALINFOID = newid()

                        -- create new default record
                        insert into dbo.MEMBERSHIPPROGRAMRENEWAL
                        (
                            ID, MEMBERSHIPPROGRAMID, STATUSCODE,
                            INTERVALCODE, ADDEDBYID, CHANGEDBYID,
                            DATEADDED, DATECHANGED
                        )
                        values
                        (
                            @RENEWALINFOID, @ID, @AFTEREXPIRATIONCLASSIFICATION1,
                            @AFTEREXPIRATIONTIMEPERIOD1, @CHANGEAGENTID, @CHANGEAGENTID,
                            @CURRENTDATE, @CURRENTDATE
                        )

                        -- link default record to this one
                        update
                            dbo.MEMBERSHIPPROGRAM
                        set
                            MEMBERSHIPPROGRAMRENEWAL1ID = @RENEWALINFOID
                        where
                            ID = @ID


                        declare @RENEWALIDTABLE1 table (RENEWALID uniqueidentifier, MEMBERSHIPLEVELID uniqueidentifier)
                        insert into @RENEWALIDTABLE1 (RENEWALID,MEMBERSHIPLEVELID) select newid(),ID from dbo.MEMBERSHIPLEVEL where MEMBERSHIPPROGRAMID = @ID
                        -- create new level-specific records
                        insert into dbo.MEMBERSHIPLEVELRENEWAL
                        (
                            ID, MEMBERSHIPLEVELID, STATUSCODE,
                            INTERVALCODE, ADDEDBYID, CHANGEDBYID,
                            DATEADDED, DATECHANGED
                        )
                        select
                            RENEWALID, MEMBERSHIPLEVELID, @AFTEREXPIRATIONCLASSIFICATION1,
                            @AFTEREXPIRATIONTIMEPERIOD1, @CHANGEAGENTID, @CHANGEAGENTID,
                            @CURRENTDATE, @CURRENTDATE
                        from @RENEWALIDTABLE1

                        -- initialize all levels to default
                        update
                            dbo.MEMBERSHIPLEVEL
                        set
                                        MEMBERSHIPLEVELRENEWAL1ID = RIT.RENEWALID
                          from dbo.MEMBERSHIPLEVEL
                          inner join @RENEWALIDTABLE1 RIT on RIT.MEMBERSHIPLEVELID = MEMBERSHIPLEVEL.ID
                    end

                    if @AFTEREXPIRATIONCLASSIFICATION2 is not null
                    begin
                        set @RENEWALINFOID = newid()

                        -- create new default record
                        insert into dbo.MEMBERSHIPPROGRAMRENEWAL
                        (
                            ID, MEMBERSHIPPROGRAMID, STATUSCODE,
                            INTERVALCODE, ADDEDBYID, CHANGEDBYID,
                            DATEADDED, DATECHANGED
                        )
                        values
                        (
                            @RENEWALINFOID, @ID, @AFTEREXPIRATIONCLASSIFICATION2,
                            @AFTEREXPIRATIONTIMEPERIOD2, @CHANGEAGENTID, @CHANGEAGENTID,
                            @CURRENTDATE, @CURRENTDATE
                        )

                        -- link default record to this one
                        update
                            dbo.MEMBERSHIPPROGRAM
                        set
                            MEMBERSHIPPROGRAMRENEWAL2ID = @RENEWALINFOID
                        where
                            ID = @ID

                        declare @RENEWALIDTABLE2 table (RENEWALID uniqueidentifier, MEMBERSHIPLEVELID uniqueidentifier)
                        insert into @RENEWALIDTABLE2 (RENEWALID,MEMBERSHIPLEVELID) select newid(),ID from dbo.MEMBERSHIPLEVEL where MEMBERSHIPPROGRAMID = @ID
                        -- create new level-specific records
                        insert into dbo.MEMBERSHIPLEVELRENEWAL
                        (
                            ID, MEMBERSHIPLEVELID, STATUSCODE,
                            INTERVALCODE, ADDEDBYID, CHANGEDBYID,
                            DATEADDED, DATECHANGED
                        )
                        select
                            RENEWALID, MEMBERSHIPLEVELID, @AFTEREXPIRATIONCLASSIFICATION2,
                            @AFTEREXPIRATIONTIMEPERIOD2, @CHANGEAGENTID, @CHANGEAGENTID,
                            @CURRENTDATE, @CURRENTDATE
                        from
                            @RENEWALIDTABLE2


                        -- initialize all levels to default
                        update
                            dbo.MEMBERSHIPLEVEL
                        set
                                        MEMBERSHIPLEVELRENEWAL2ID =  RIT.RENEWALID
                          from dbo.MEMBERSHIPLEVEL
                          inner join @RENEWALIDTABLE2 RIT on RIT.MEMBERSHIPLEVELID = MEMBERSHIPLEVEL.ID

                    end

                    if @AFTEREXPIRATIONCLASSIFICATION3 is not null
                    begin
                        set @RENEWALINFOID = newid()

                        -- create new default record
                        insert into dbo.MEMBERSHIPPROGRAMRENEWAL
                        (
                            ID, MEMBERSHIPPROGRAMID, STATUSCODE,
                            INTERVALCODE, ADDEDBYID, CHANGEDBYID,
                            DATEADDED, DATECHANGED
                        )
                        values
                        (
                            @RENEWALINFOID, @ID, @AFTEREXPIRATIONCLASSIFICATION3,
                            @AFTEREXPIRATIONTIMEPERIOD3, @CHANGEAGENTID, @CHANGEAGENTID,
                            @CURRENTDATE, @CURRENTDATE
                        )

                        -- link default record to this one
                        update
                            dbo.MEMBERSHIPPROGRAM
                        set
                            MEMBERSHIPPROGRAMRENEWAL3ID = @RENEWALINFOID
                        where
                            ID = @ID

                        -- create new level-specific records
                        declare @RENEWALIDTABLE3 table (RENEWALID uniqueidentifier, MEMBERSHIPLEVELID uniqueidentifier)
                        insert into @RENEWALIDTABLE3 (RENEWALID,MEMBERSHIPLEVELID) select newid(),ID from dbo.MEMBERSHIPLEVEL where MEMBERSHIPPROGRAMID = @ID

                        insert into dbo.MEMBERSHIPLEVELRENEWAL
                        (
                            ID, MEMBERSHIPLEVELID, STATUSCODE,
                            INTERVALCODE, ADDEDBYID, CHANGEDBYID,
                            DATEADDED, DATECHANGED
                        )
                        select
                            RENEWALID, MEMBERSHIPLEVELID, @AFTEREXPIRATIONCLASSIFICATION3,
                            @AFTEREXPIRATIONTIMEPERIOD3, @CHANGEAGENTID, @CHANGEAGENTID,
                            @CURRENTDATE, @CURRENTDATE
                        from
                            @RENEWALIDTABLE3

                        -- initialize all levels to default
                        update
                            dbo.MEMBERSHIPLEVEL
                        set
                                        MEMBERSHIPLEVELRENEWAL3ID =   RIT.RENEWALID
                          from dbo.MEMBERSHIPLEVEL
                          inner join @RENEWALIDTABLE3 RIT on RIT.MEMBERSHIPLEVELID = MEMBERSHIPLEVEL.ID
                    end

                    declare @ORGANIZATIONEXCHANGERATEID uniqueidentifier = dbo.UFN_CURRENCYEXCHANGERATE_GETLATESTORGANIZATIONRATE(@BASECURRENCYID, dbo.UFN_CURRENCY_GETORGANIZATIONCURRENCY(), @CURRENTDATE, null, null)              

                    if @ANNUAL = 1
                    begin                        
                        --save terms
                        if @SINGLETERM = 1
                        begin
                            --same term for every level
                            insert into dbo.MEMBERSHIPLEVELTERM
                                (ID, LEVELID, AMOUNT, TERMTIMELENGTH, TERMLENGTHCODE, BASECURRENCYID, ADDEDBYID, CHANGEDBYID, DATEADDED, DATECHANGED, LOWAMOUNT, ORGANIZATIONEXCHANGERATEID, ORGANIZATIONAMOUNT, ORGANIZATIONLOWAMOUNT)
                            select newid(), T.c.value('(ID)[1]', 'uniqueidentifier'), case when @DUESBASED = 1 then T.c.value('(PRICE)[1]', 'money') else T.c.value('(LARGESTGIFTAMOUNT)[1]','money') end, @SINGLETERMLENGTH, @SINGLETERMLENGTHCODE, @BASECURRENCYID, @CHANGEAGENTID, @CHANGEAGENTID, @CURRENTDATE, @CURRENTDATE, T.c.value('(SMALLESTGIFTAMOUNT)[1]','money'), @ORGANIZATIONEXCHANGERATEID, dbo.UFN_CURRENCY_CONVERT(case when @DUESBASED = 1 then T.c.value('(PRICE)[1]', 'money') else T.c.value('(LARGESTGIFTAMOUNT)[1]','money') end, @ORGANIZATIONEXCHANGERATEID), dbo.UFN_CURRENCY_CONVERT(T.c.value('(SMALLESTGIFTAMOUNT)[1]','money'), @ORGANIZATIONEXCHANGERATEID)
                                from @MEMBERSHIPLEVELS.nodes('/MEMBERSHIPLEVELS/ITEM') T(c)
                        end
                else
                        begin
                            --different terms for every level
                            insert into dbo.MEMBERSHIPLEVELTERM
                                (ID, LEVELID, AMOUNT, SEQUENCE, TERMTIMELENGTH, TERMLENGTHCODE, BASECURRENCYID, ADDEDBYID, CHANGEDBYID, DATEADDED, DATECHANGED, LOWAMOUNT, ORGANIZATIONEXCHANGERATEID, ORGANIZATIONAMOUNT, ORGANIZATIONLOWAMOUNT)
                            select newid(), T.c.value('(LEVELID)[1]', 'uniqueidentifier'), T.c.value('(HIGHPRICE)[1]', 'money'), T.c.value('(SEQUENCE)[1]','int'), S.c.value('(NUMBEROFUNITS)[1]', 'tinyint'), S.c.value('(UNITSCODE)[1]', 'tinyint'), @BASECURRENCYID, @CHANGEAGENTID, @CHANGEAGENTID, @CURRENTDATE, @CURRENTDATE, T.c.value('(LOWPRICE)[1]', 'money'), @ORGANIZATIONEXCHANGERATEID, dbo.UFN_CURRENCY_CONVERT(T.c.value('(HIGHPRICE)[1]','money'), @ORGANIZATIONEXCHANGERATEID), dbo.UFN_CURRENCY_CONVERT(T.c.value('(LOWPRICE)[1]','money'), @ORGANIZATIONEXCHANGERATEID)
                                from @MEMBERSHIPLEVELTERMS.nodes('/MEMBERSHIPLEVELTERMS/ITEM') T(c)
                                inner join @MULTIPLETERMSUNITS.nodes('/MULTIPLETERMSUNITS/ITEM') S(c) on T.c.value('(TERMID)[1]', 'uniqueidentifier') = S.c.value('(ID)[1]', 'uniqueidentifier')
                        end
                    end
                    else if @RECURRINGSUSTAINING = 1
                    begin
                        declare @RECURRINGPRICESSEQUENCE int = 0

                        if @ANNUALLYPAYMENTOPTION = 1
                        begin
                            insert into dbo.MEMBERSHIPLEVELTERM
                                (ID, LEVELID, RECURRINGPAYMENTOPTIONCODE, SEQUENCE, AMOUNT, BASECURRENCYID, ADDEDBYID, CHANGEDBYID, DATEADDED, DATECHANGED, ORGANIZATIONEXCHANGERATEID, ORGANIZATIONAMOUNT)
                            select newid(), T.c.value('(LEVELID)[1]','uniqueidentifier'), 0, @RECURRINGPRICESSEQUENCE, T.c.value('(ANNUALLYPRICE)[1]','money'), @BASECURRENCYID, @CHANGEAGENTID, @CHANGEAGENTID, @CURRENTDATE, @CURRENTDATE, @ORGANIZATIONEXCHANGERATEID, dbo.UFN_CURRENCY_CONVERT(T.c.value('(ANNUALLYPRICE)[1]','money'), @ORGANIZATIONEXCHANGERATEID)
                                from @RECURRINGPRICES.nodes('/RECURRINGPRICES/ITEM') T(c);

                            set @RECURRINGPRICESSEQUENCE = @RECURRINGPRICESSEQUENCE + 1
                        end

                        if @SEMIANNUALLYPAYMENTOPTION = 1
                        begin
                            insert into dbo.MEMBERSHIPLEVELTERM
                                (ID, LEVELID, RECURRINGPAYMENTOPTIONCODE, SEQUENCE, AMOUNT, BASECURRENCYID, ADDEDBYID, CHANGEDBYID, DATEADDED, DATECHANGED, ORGANIZATIONEXCHANGERATEID, ORGANIZATIONAMOUNT)
                            select newid(), T.c.value('(LEVELID)[1]','uniqueidentifier'), 1, @RECURRINGPRICESSEQUENCE, T.c.value('(SEMIANNUALLYPRICE)[1]','money'), @BASECURRENCYID, @CHANGEAGENTID, @CHANGEAGENTID, @CURRENTDATE, @CURRENTDATE, @ORGANIZATIONEXCHANGERATEID, dbo.UFN_CURRENCY_CONVERT(T.c.value('(SEMIANNUALLYPRICE)[1]','money'), @ORGANIZATIONEXCHANGERATEID)
                                from @RECURRINGPRICES.nodes('/RECURRINGPRICES/ITEM') T(c);

                            set @RECURRINGPRICESSEQUENCE = @RECURRINGPRICESSEQUENCE + 1
                        end

                        if @QUARTERLYPAYMENTOPTION = 1
                        begin
                            insert into dbo.MEMBERSHIPLEVELTERM
                                (ID, LEVELID, RECURRINGPAYMENTOPTIONCODE, SEQUENCE, AMOUNT, BASECURRENCYID, ADDEDBYID, CHANGEDBYID, DATEADDED, DATECHANGED, ORGANIZATIONEXCHANGERATEID, ORGANIZATIONAMOUNT)
                            select newid(), T.c.value('(LEVELID)[1]','uniqueidentifier'), 2, @RECURRINGPRICESSEQUENCE, T.c.value('(QUARTERLYPRICE)[1]','money'), @BASECURRENCYID, @CHANGEAGENTID, @CHANGEAGENTID, @CURRENTDATE, @CURRENTDATE, @ORGANIZATIONEXCHANGERATEID, dbo.UFN_CURRENCY_CONVERT(T.c.value('(QUARTERLYPRICE)[1]','money'), @ORGANIZATIONEXCHANGERATEID)
                                from @RECURRINGPRICES.nodes('/RECURRINGPRICES/ITEM') T(c);

                            set @RECURRINGPRICESSEQUENCE = @RECURRINGPRICESSEQUENCE + 1
                        end

                        if @MONTHLYPAYMENTOPTION = 1
                        begin
                            insert into dbo.MEMBERSHIPLEVELTERM
                                (ID, LEVELID, RECURRINGPAYMENTOPTIONCODE, SEQUENCE, AMOUNT, BASECURRENCYID, ADDEDBYID, CHANGEDBYID, DATEADDED, DATECHANGED, ORGANIZATIONEXCHANGERATEID, ORGANIZATIONAMOUNT)
                            select newid(), T.c.value('(LEVELID)[1]','uniqueidentifier'), 3, @RECURRINGPRICESSEQUENCE, T.c.value('(MONTHLYPRICE)[1]','money'), @BASECURRENCYID, @CHANGEAGENTID, @CHANGEAGENTID, @CURRENTDATE, @CURRENTDATE, @ORGANIZATIONEXCHANGERATEID, dbo.UFN_CURRENCY_CONVERT(T.c.value('(MONTHLYPRICE)[1]','money'), @ORGANIZATIONEXCHANGERATEID)
                                from @RECURRINGPRICES.nodes('/RECURRINGPRICES/ITEM') T(c)
                        end
                    end
                    else
                    begin
                        insert into dbo.MEMBERSHIPLEVELTERM
                            (ID, LEVELID, LIFETIMEPAYMENTOPTIONCODE, LIFETIMENUMBEROFPAYMENTS, AMOUNT, SEQUENCE, BASECURRENCYID, ADDEDBYID, CHANGEDBYID, DATEADDED, DATECHANGED, LOWAMOUNT, ORGANIZATIONEXCHANGERATEID, ORGANIZATIONAMOUNT, ORGANIZATIONLOWAMOUNT)
                        select newid(), T.c.value('(LEVELID)[1]', 'uniqueidentifier'), S.c.value('(PAYMENTOPTION)[1]','tinyint'), S.c.value('(NUMBEROFPAYMENTS)[1]','smallint'), T.c.value('(LOWPRICE)[1]', 'money'), T.c.value('(SEQUENCE)[1]','int'), @BASECURRENCYID, @CHANGEAGENTID, @CHANGEAGENTID, @CURRENTDATE, @CURRENTDATE, T.c.value('(HIGHPRICE)[1]', 'money'), @ORGANIZATIONEXCHANGERATEID, dbo.UFN_CURRENCY_CONVERT(T.c.value('(LOWPRICE)[1]','money'), @ORGANIZATIONEXCHANGERATEID), dbo.UFN_CURRENCY_CONVERT(T.c.value('(HIGHPRICE)[1]','money'), @ORGANIZATIONEXCHANGERATEID)
                            from @MEMBERSHIPLEVELTERMS.nodes('/MEMBERSHIPLEVELTERMS/ITEM') T(c)
                            inner join @LIFETIMEPAYMENTOPTIONS.nodes('/LIFETIMEPAYMENTOPTIONS/ITEM') S(c) on T.c.value('(TERMID)[1]', 'uniqueidentifier') = S.c.value('(ID)[1]', 'uniqueidentifier')
                    end

                    if @DUESBASED = 1 or @BOTHWAYSBASED = 1
                    begin
                        --save program designations
                        if @DUESTREATEDASCONTRIBUTION = 1
                        begin
                            insert into dbo.MEMBERSHIPPROGRAMDESIGNATION
                                (ID, MEMBERSHIPPROGRAMID, DESIGNATIONID, [PERCENT], ADDEDBYID, CHANGEDBYID, DATEADDED, DATECHANGED)
                            select newid(), @ID, T.c.value('(DESIGNATIONID)[1]', 'uniqueidentifier'), T.c.value('(PERCENTAGE)[1]', 'decimal(7,4)'), @CHANGEAGENTID, @CHANGEAGENTID, @CURRENTDATE, @CURRENTDATE 
                            from @DESIGNATIONSFORCONTRIBUTEDPORTION.nodes('/DESIGNATIONSFORCONTRIBUTEDPORTION/ITEM') T(c)

                            insert into dbo.MEMBERSHIPLEVELDESIGNATION
                                (ID, MEMBERSHIPLEVELID, DESIGNATIONID, [PERCENT], ADDEDBYID, CHANGEDBYID, DATEADDED, DATECHANGED)
                            select newid(), T.c.value('(LEVELID)[1]', 'uniqueidentifier'), T.c.value('(DESIGNATIONID)[1]', 'uniqueidentifier'), T.c.value('(PERCENTAGE)[1]', 'decimal(7,4)'), @CHANGEAGENTID, @CHANGEAGENTID, @CURRENTDATE, @CURRENTDATE
                            from @MEMBERSHIPLEVELS.nodes('/MEMBERSHIPLEVELS/ITEM/MEMBERSHIPLEVELDESIGNATIONS/ITEM') T(c)
                        end

                        --save program add-ons
                        if @ADDONSUSED = 1
                        begin
                    insert into dbo.MEMBERSHIPPROGRAMADDON
                                (ID, MEMBERSHIPPROGRAMID, ADDONID, PRICE, MULTIPLEALLOWED, DESCRIPTION, ADDEDBYID, CHANGEDBYID, DATEADDED, DATECHANGED,BASECURRENCYID)
                            select newid(), @ID, T.c.value('(ADDONCODE)[1]', 'uniqueidentifier'), T.c.value('(PRICE)[1]', 'money'), T.c.value('(MULTIPLEALLOWED)[1]', 'bit'), T.c.value('(DESCRIPTION)[1]', 'nvarchar(255)'), @CHANGEAGENTID, @CHANGEAGENTID, @CURRENTDATE, @CURRENTDATE,@BASECURRENCYID
                                from @ADDONS.nodes('/ADDONS/ITEM') T(c)
                        end

                        --insert discount information for the membership program
                        insert into dbo.MEMBERSHIPPROMOAVAILABILITY
                            (ID, MEMBERSHIPPROMOID, MEMBERSHIPPROGRAMID, ADDEDBYID, CHANGEDBYID, DATEADDED, DATECHANGED)
                        select newid(), T.c.value('(ID)[1]', 'uniqueidentifier'), @ID, @CHANGEAGENTID, @CHANGEAGENTID, @CURRENTDATE, @CURRENTDATE
                                from @DISCOUNTSFORPROGRAM.nodes('/DISCOUNTSFORPROGRAM/ITEM') T(c)
                                where T.c.value('(APPLY)[1]', 'bit') = 1
                    end

                    if @CONTRIBUTIONSBASED = 1 or @BOTHWAYSBASED = 1
                    begin
                        if @CONTRIBUTIONCRITERIADEFINITIONTYPECODE = 1
                        begin
                            select
                                @CONTRIBUTIONISPAYMENTCOUNTED = 0,
                                @CONTRIBUTIONISPLEDGECOUNTED = 0,
                                @CONTRIBUTIONISRECURRINGGIFTCOUNTED = 0,
                                @CONTRIBUTIONINCLUDEPAYMENTACTIVITYTYPECODE = 0,
                                @CONTRIBUTIONDESIGNATIONID = null,
                                @CONTRIBUTIONIDSETREGISTERID = null,
                                @CONTRIBUTIONISEVENTREGISTRATIONCOUNTED = 0,
                                @CONTRIBUTIONINCLUDEEVENTTYPECODE = 0,
                                @CONTRIBUTIONEVENTCATEGORYCODEID = null,
                                @CONTRIBUTIONISMEMBERSHIPDUECOUNTED = 0,
                                @CONTRIBUTIONINCLUDEMEMBERSHIPTYPECODE = 0,
                                @CONTRIBUTIONMEMBERSHIPPROGRAMID = null;
                        end
                        else
                        begin
                            select
                                @CONTRIBUTIONCRITERIADEFINITIONTYPECODE = 0,
                                @CONTRIBUTIONAPPLICATIONSELECTIONID = null;
                        end

                        insert into dbo.MEMBERSHIPPROGRAMCONTRIBUTION (
                            ID,
                            ISPAYMENTCOUNTED,
                            ISPLEDGECOUNTED,
                            ISRECURRINGGIFTCOUNTED,
                            INCLUDEPAYMENTACTIVITYTYPECODE,
                            DESIGNATIONID,
                            IDSETREGISTERID,
                            ISEVENTREGISTRATIONCOUNTED,
                            INCLUDEEVENTTYPECODE,
                            EVENTCATEGORYCODEID,
                            ISMEMBERSHIPDUECOUNTED,
                            INCLUDEMEMBERSHIPTYPECODE,
                            MEMBERSHIPPROGRAMID,
                            ISMULTIPLEREVENUETRANSACTIONSCOUNTED,
                            WHATHAPPENSIFTHEYGIVEMORECODE,
                            WHATDATETOCALCULATEEXPIRATIONDATECODE,
                            CRITERIADEFINITIONTYPECODE,
                            APPLICATIONSELECTIONID,
                            ADDEDBYID,
                            CHANGEDBYID,
                            DATEADDED,
                            DATECHANGED
                        )
                        values (
                            @ID,
                            @CONTRIBUTIONISPAYMENTCOUNTED,
                            @CONTRIBUTIONISPLEDGECOUNTED,
                            @CONTRIBUTIONISRECURRINGGIFTCOUNTED,
                            @CONTRIBUTIONINCLUDEPAYMENTACTIVITYTYPECODE,
                            @CONTRIBUTIONDESIGNATIONID,
                            @CONTRIBUTIONIDSETREGISTERID,
                            @CONTRIBUTIONISEVENTREGISTRATIONCOUNTED,
                            @CONTRIBUTIONINCLUDEEVENTTYPECODE,
      @CONTRIBUTIONEVENTCATEGORYCODEID,
                            @CONTRIBUTIONISMEMBERSHIPDUECOUNTED,
                            @CONTRIBUTIONINCLUDEMEMBERSHIPTYPECODE,
                            @CONTRIBUTIONMEMBERSHIPPROGRAMID,
                            @CONTRIBUTIONISMULTIPLEREVENUETRANSACTIONSCOUNTED,
                            @WHATHAPPENSIFTHEYGIVEMORECODE,
                            @WHATDATETOCALCULATEEXPIRATIONDATECODE,
                            @CONTRIBUTIONCRITERIADEFINITIONTYPECODE,
                            @CONTRIBUTIONAPPLICATIONSELECTIONID,
                            @CHANGEAGENTID,
                            @CHANGEAGENTID,
                            @CURRENTDATE,
                            @CURRENTDATE
                        )

                    end    

          insert into dbo.MEMBERSHIPLEVELRENEWALRULES(
            MEMBERSHIPLEVELID,
            BEFOREEXPIRATION,
            AFTEREXPIRATION,
            STARTDATE,
            MEMBERSHIPLEVELRENEWAL1ID,
            MEMBERSHIPLEVELRENEWAL2ID,
            MEMBERSHIPLEVELRENEWAL3ID,
            NONRENEWALACTIONTYPECODE,
            ADDEDBYID, 
              CHANGEDBYID, 
              DATEADDED, 
              DATECHANGED
          )
          select
          MEMBERSHIPLEVEL.ID,
          MEMBERSHIPLEVEL.BEFOREEXPIRATION,
          MEMBERSHIPLEVEL.AFTEREXPIRATION,
              '1/1/1753', -- use the smallest date
          MEMBERSHIPLEVEL.MEMBERSHIPLEVELRENEWAL1ID,
          MEMBERSHIPLEVEL.MEMBERSHIPLEVELRENEWAL2ID,
          MEMBERSHIPLEVEL.MEMBERSHIPLEVELRENEWAL3ID,
          MEMBERSHIPLEVEL.NONRENEWALACTIONTYPECODE,
          @CHANGEAGENTID,
          @CHANGEAGENTID,
          @CURRENTDATE,
          @CURRENTDATE
          from dbo.MEMBERSHIPLEVEL
          where MEMBERSHIPLEVEL.MEMBERSHIPPROGRAMID = @ID


       exec dbo.USP_MEMBERSHIPPROGRAM_CREATEQUERY @ID, @PROGRAMNAME, @CHANGEAGENTID;
       end try

       begin catch
         exec dbo.USP_RAISE_ERROR
         return 1
      end catch

      return 0