USP_RECURRINGGIFTINSTALLMENTEVENT_CREATEFORBATCH

            CREATE procedure dbo.USP_RECURRINGGIFTINSTALLMENTEVENT_CREATEFORBATCH
            (
             @BATCHID uniqueidentifier,
             @CHANGEAGENTID uniqueidentifier,
             @PROCESSNAME nvarchar(60), 
             @PROCESSRUNDATE datetime
            )
            as
            begin
              set nocount on; 

              declare @CURRENTDATE datetime;
              set @CURRENTDATE = getdate();

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

              if exists(select 'x' from dbo.UFN_RECURRINGGIFTSETTING_GETCURRENT() where FIRSTINSTALLMENTCODE = 1)
              begin
                -- If the first installment setting for recurring gift payment handling is "Most recent", then

                -- the events should be logged on the most recent installment, not the oldest.  It is not guaranteed

                -- that the most recent installment will exist in the database yet.  As such, we would need to call

                -- USP_RECURRINGGIFT_ADDMISSINGINSTALLMENTS for any RG where that next installment in fact does not

                -- exist, so that the installment will exist so we can associate the event with it.


                declare @RGS table (ID uniqueidentifier,
                    AMOUNT money,
                    BASECURRENCYID uniqueidentifier,
                    ORGANIZATIONAMOUNT money,
                    ORGANIZATIONEXCHANGERATEID uniqueidentifier,
                    TRANSACTIONAMOUNT money,
                    TRANSACTIONCURRENCYID uniqueidentifier,
                    BASEEXCHANGERATEID uniqueidentifier);

                -- get list of RGs where the next installment doesn't exist yet

                --CC only

                insert into @RGS
                select R.ID,
                       R.AMOUNT,
                       R.BASECURRENCYID,
                       R.ORGANIZATIONAMOUNT,
                       R.ORGANIZATIONEXCHANGERATEID,
                       R.TRANSACTIONAMOUNT,
                       R.TRANSACTIONCURRENCYID,
                       R.BASEEXCHANGERATEID
                from dbo.BATCHREVENUE BR
                left join dbo.BATCHREVENUEAPPLICATION BRA on BRA.BATCHREVENUEID = BR.ID
                cross apply dbo.UFN_RECURRINGGIFT_GETNEXTINSTALLMENTINFO(BRA.REVENUEID, null) NEXTINSTALLMENT
                inner join dbo.REVENUE R on R.ID = BRA.REVENUEID
                where BR.PAYMENTMETHODCODE in (2) -- CC only

                and BRA.REVENUEID in (select ID from dbo.FINANCIALTRANSACTION where TYPECODE = 2) --recurring gifts only 

                and BR.BATCHID = @BATCHID
                and NEXTINSTALLMENT.ID is null;

                --EFT Only

                insert into @RGS
                select R.ID,
                       R.AMOUNT,
                       R.BASECURRENCYID,
                       R.ORGANIZATIONAMOUNT,
                       R.ORGANIZATIONEXCHANGERATEID,
                       R.TRANSACTIONAMOUNT,
                       R.TRANSACTIONCURRENCYID,
                       R.BASEEXCHANGERATEID
                from dbo.BATCHREVENUE BR
                left join dbo.BATCHREVENUEAPPLICATION BRA on BRA.BATCHREVENUEID = BR.ID
                cross apply dbo.UFN_RECURRINGGIFT_GETNEXTINSTALLMENTINFO(isnull(BR.PAYINGPENDINGREVENUEID, BRA.REVENUEID),null) NEXTINSTALLMENT
                inner join dbo.REVENUE R on R.ID = isnull(BR.PAYINGPENDINGREVENUEID, BRA.REVENUEID)
                where BR.PAYMENTMETHODCODE in (3) -- EFT only

                and isnull(BR.PAYINGPENDINGREVENUEID, BRA.REVENUEID) in (select ID from dbo.FINANCIALTRANSACTION where TYPECODE = 2) --recurring gifts only 

                and BR.BATCHID = @BATCHID
                and NEXTINSTALLMENT.ID is null;

         declare @ORGANIZATIONCURRENCYID uniqueidentifier = dbo.UFN_CURRENCY_GETORGANIZATIONCURRENCY();

                -- add missing installments to those RGs

                insert into dbo.RECURRINGGIFTINSTALLMENT (
                  ID,
                  REVENUEID,
                  AMOUNT,
                  DATE,
                  BASECURRENCYID,
                  ORGANIZATIONAMOUNT,
                  ORGANIZATIONEXCHANGERATEID,
                  TRANSACTIONAMOUNT,
                  TRANSACTIONCURRENCYID,
                  BASEEXCHANGERATEID,
                  ADDEDBYID, CHANGEDBYID, DATEADDED, DATECHANGED)
                select
                  newid(),
                  rg.ID,
                  rg.AMOUNT,
                  i.DATE,
                  coalesce(rg.BASECURRENCYID,@ORGANIZATIONCURRENCYID),
                  coalesce(rg.ORGANIZATIONAMOUNT,rg.AMOUNT),
                  rg.ORGANIZATIONEXCHANGERATEID,
                  coalesce(rg.TRANSACTIONAMOUNT,rg.AMOUNT),
                  coalesce(rg.TRANSACTIONCURRENCYID,@ORGANIZATIONCURRENCYID),
                  rg.BASEEXCHANGERATEID,
                  @CHANGEAGENTID, @CHANGEAGENTID, @CURRENTDATE, @CURRENTDATE
                from @RGS rg
                cross apply dbo.UFN_RECURRINGGIFT_GETMISSINGINSTALLMENTS_ASOFDATE(rg.ID,@CURRENTDATE) i;
              end

              -- capture CC events

              insert into dbo.RECURRINGGIFTINSTALLMENTEVENT
              (
                ID,
                RECURRINGGIFTINSTALLMENTID,
                EVENTCODE,
                DATE,
                PROCESSNAME,
                RESULTCODE,
                REJECTIONMESSAGE,
                ADDEDBYID,
                CHANGEDBYID,
                DATEADDED,
                DATECHANGED,
                BATCHID
              ) 
              select
                newid(),
                dbo.UFN_RECURRINGGIFT_GETNEXTINSTALLMENT(BRA.REVENUEID, null),
                case when AUTHORIZATIONCODE <> '' then 1 when REJECTIONMESSAGE <> '' then 2 when DIRECTDEBITISREJECTED = 1 then 3 else 0 end,
                isnull(@PROCESSRUNDATE, @CURRENTDATE),
                isnull(@PROCESSNAME,''),
                case when DIRECTDEBITISREJECTED = 1 then DIRECTDEBITRESULTCODE else AUTHORIZATIONCODE end,
                REJECTIONMESSAGE,
                @CHANGEAGENTID,
                @CHANGEAGENTID,
                @CURRENTDATE,
                @CURRENTDATE,
                @BATCHID
              from dbo.BATCHREVENUE BR
                left join dbo.BATCHREVENUEAPPLICATION BRA on BRA.BATCHREVENUEID = BR.ID
              where 
                BR.PAYMENTMETHODCODE in (2) -- CC only

                and BRA.REVENUEID in (select ID from dbo.FINANCIALTRANSACTION where TYPECODE = 2) --recurring gifts only 

                and BR.BATCHID = @BATCHID

              -- capture EFT events

              insert into dbo.RECURRINGGIFTINSTALLMENTEVENT
              (
                ID,
                RECURRINGGIFTINSTALLMENTID,
                EVENTCODE,
                DATE,
                PROCESSNAME,
                RESULTCODE,
                REJECTIONMESSAGE,
                ADDEDBYID,
                CHANGEDBYID,
                DATEADDED,
                DATECHANGED,
                BATCHID
              ) 
              select
                newid(),
                dbo.UFN_RECURRINGGIFT_GETNEXTINSTALLMENT(isnull(BR.PAYINGPENDINGREVENUEID, BRA.REVENUEID),null),
                case when AUTHORIZATIONCODE <> '' then 1 when REJECTIONMESSAGE <> '' then 2 when DIRECTDEBITISREJECTED = 1 then 3 else 0 end,
                isnull(@PROCESSRUNDATE, @CURRENTDATE),
                isnull(@PROCESSNAME,''),
                case when DIRECTDEBITISREJECTED = 1 then DIRECTDEBITRESULTCODE else AUTHORIZATIONCODE end,
                REJECTIONMESSAGE,
                @CHANGEAGENTID,
@CHANGEAGENTID,
                @CURRENTDATE,
                @CURRENTDATE,
                @BATCHID
              from dbo.BATCHREVENUE BR
                left join dbo.BATCHREVENUEAPPLICATION BRA on BRA.BATCHREVENUEID = BR.ID
              where 
                BR.PAYMENTMETHODCODE in (3) -- EFT only

                and isnull(BR.PAYINGPENDINGREVENUEID, BRA.REVENUEID) in (select ID from dbo.FINANCIALTRANSACTION where TYPECODE = 2) --recurring gifts only 

                and BR.BATCHID = @BATCHID

            end