USP_REGISTRANT_FAF_BENEFIT_ADD

    CREATE procedure dbo.USP_REGISTRANT_FAF_BENEFIT_ADD
    (
                    @REGISTRANTID uniqueidentifier,
                    @BENEFITID uniqueidentifier,
                    @EVENTPRICEID uniqueidentifier = null,
                    @BENEFITTYPECODE tinyint = 0,
                    @BENEFITOPTIONIDLIST varchar(4000) = '',
                    @PREFERENCELIST varchar(4000) = '',
                    @QUANTITY int = 1,
                    @CHANGEAGENTID uniqueidentifier = null
                )
                with execute as caller
    as
    begin
      set nocount on;

                declare @CURRENTDATE datetime
                declare @EVENTID uniqueidentifier
                declare @BASECURRENCYID uniqueidentifier
                declare @BENEFITOPTIONID uniqueidentifier
                declare @BENEFITPREFERENCE nvarchar(500)

                declare @BENEFITOPTIONS table (ID int identity(1,1), BENEFITOPTIONID uniqueidentifier);

                declare @BENEFITPREFERENCES table (ID int identity(1,1), BENEFITPREFERENCE nvarchar(500));

                declare @ORGANIZATIONCURRENCYID uniqueidentifier = dbo.UFN_CURRENCY_GETORGANIZATIONCURRENCY();

                declare @i int
                declare @TOTAL int


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

                set @CURRENTDATE = getdate();

                SELECT @EVENTID = EVENTID from dbo.REGISTRANT where ID = @REGISTRANTID;

                SELECT @BASECURRENCYID = BASECURRENCYID from dbo.EVENT where ID = @EVENTID

                if ISNULL(@BENEFITOPTIONIDLIST,'') > ''
                INSERT INTO @BENEFITOPTIONS(BENEFITOPTIONID)
                SELECT element_value from dbo.UFN_PARSE_STRING(@BENEFITOPTIONIDLIST,',');

                if ISNULL(@PREFERENCELIST,'') > ''
                INSERT INTO @BENEFITPREFERENCES(BENEFITPREFERENCE)
                SELECT element_value from dbo.UFN_PARSE_STRING(@PREFERENCELIST,',');

                SELECT @TOTAL = COUNT(*) from @BENEFITOPTIONS;
                SET @i = 1;

                BEGIN TRAN
                begin try
                    IF @BENEFITTYPECODE = 0 AND @EVENTPRICEID IS NOT NULL
                    BEGIN
                        insert into dbo.REGISTRANTBENEFIT
                        (
                            REGISTRANTID,
                            BENEFITID,
                            UNITVALUE,
                            QUANTITY,
                            TOTALVALUE,
                            DETAILS,
                            ADDEDBYID,
                            CHANGEDBYID,
                            DATEADDED,
                            DATECHANGED,
                            BASECURRENCYID,
                            BASEEXCHANGERATEID,
                            ORGANIZATIONEXCHANGERATEID,
                            EVENTBASECURRENCYTOTALVALUE,
                            ORGANIZATIONTOTALVALUE
                        )
                        select
                            R.ID,
                            EPB.BENEFITID,
                            EPB.UNITVALUE,
                            EPB.QUANTITY,
                            (EPB.UNITVALUE * EPB.QUANTITY),
                            EPB.DETAILS,
                            @CHANGEAGENTID,
                            @CHANGEAGENTID,
                            @CURRENTDATE,
                            @CURRENTDATE,
                            EPB.BASECURRENCYID,
                            case when EPB.BASECURRENCYID = E.BASECURRENCYID
                                then null
                                else dbo.UFN_CURRENCYEXCHANGERATE_GETLATEST(EPB.BASECURRENCYID, E.BASECURRENCYID, @CURRENTDATE, 1, null)
                            end,
                            case when EPB.BASECURRENCYID = @ORGANIZATIONCURRENCYID
                                then null
                                else dbo.UFN_CURRENCYEXCHANGERATE_GETLATEST(EPB.BASECURRENCYID, @ORGANIZATIONCURRENCYID, @CURRENTDATE, 0, null)
                            end,
                            case when EPB.BASECURRENCYID = E.BASECURRENCYID
                                then (EPB.UNITVALUE * EPB.QUANTITY)
                                else dbo.UFN_CURRENCY_CONVERT((EPB.UNITVALUE * EPB.QUANTITY), dbo.UFN_CURRENCYEXCHANGERATE_GETLATEST(EPB.BASECURRENCYID, E.BASECURRENCYID, @CURRENTDATE, 1, null))
                            end,
                            case when EPB.BASECURRENCYID = @ORGANIZATIONCURRENCYID
                                then (EPB.UNITVALUE * EPB.QUANTITY)
                                else dbo.UFN_CURRENCY_CONVERT((EPB.UNITVALUE * EPB.QUANTITY), dbo.UFN_CURRENCYEXCHANGERATE_GETLATEST(EPB.BASECURRENCYID, @ORGANIZATIONCURRENCYID, @CURRENTDATE, 0, null))
                            end
                        from
                            dbo.REGISTRANT R (NOLOCK)
                            INNER JOIN dbo.EVENT E (NOLOCK)
                            ON R.EVENTID = E.ID
                            INNER JOIN dbo.EVENTPRICE EP (NOLOCK)
                            ON EP.EVENTID = E.ID
                            INNER JOIN dbo.EVENTPRICEBENEFIT EPB (NOLOCK)
                            ON EPB.EVENTPRICEID = EP.ID
                        where
                            EPB.EVENTPRICEID = @EVENTPRICEID
                            AND EPB.BENEFITID = @BENEFITID
                            AND R.ID = @REGISTRANTID;                    
                    END

                    IF ISNULL(@BENEFITTYPECODE,0) > 0 
                    BEGIN
                        insert into dbo.REGISTRANTBENEFIT
                        (
                            REGISTRANTID,
                            BENEFITID,
                            UNITVALUE,
                            QUANTITY,
                            TOTALVALUE,
                            DETAILS,
                            ADDEDBYID,
                            CHANGEDBYID,
                            DATEADDED,
                            DATECHANGED,
                            BASECURRENCYID,
                            BASEEXCHANGERATEID,
                            ORGANIZATIONEXCHANGERATEID,
                            EVENTBASECURRENCYTOTALVALUE,
                            ORGANIZATIONTOTALVALUE
                        )
                        select
                            @REGISTRANTID,
                            B.ID,
                            B.VALUE,
                            @QUANTITY,
                            (B.VALUE * @QUANTITY),
                            '',
                            @CHANGEAGENTID,
                            @CHANGEAGENTID,
                            @CURRENTDATE,
                            @CURRENTDATE,
                            @BASECURRENCYID,
                            null,
                            null,
                            (B.VALUE * @QUANTITY),
                            (B.VALUE * @QUANTITY)
                        from
                            dbo.BENEFIT B (NOLOCK)
                        where 
                            B.ID = @BENEFITID                
                    END

                    WHILE (@i <= @TOTAL)
                    BEGIN

                            SET @BENEFITOPTIONID = null;
                            SET @BENEFITPREFERENCE = '';

                            SELECT @BENEFITOPTIONID = BENEFITOPTIONID FROM @BENEFITOPTIONS WHERE ID = @i;

                            SELECT @BENEFITPREFERENCE = BENEFITPREFERENCE FROM @BENEFITPREFERENCES WHERE ID = @i;

                            IF @BENEFITOPTIONID IS NOT NULL
                            EXEC dbo.USP_DATAFORMTEMPLATE_ADD_REGISTRANTBENEFITEXTENSION @ID = null, @CHANGEAGENTID = @CHANGEAGENTID, @REGISTRANTID = @REGISTRANTID, @BENEFITID = @BENEFITID, @BENEFITOPTIONID = @BENEFITOPTIONID, @BENEFITPREFERENCE = @BENEFITPREFERENCE, @BENEFITTYPECODE = @BENEFITTYPECODE

                            SET @i = @i + 1;


                    END

                end try

                begin catch
                    exec dbo.USP_RAISE_ERROR
                    ROLLBACK TRAN
                    return 1;
                end catch

                COMMIT TRAN;
                return 0;            
      end