USP_DATAFORMTEMPLATE_EDIT_PLEDGEWRITEOFFBATCHROWCOMMIT

CREATE procedure dbo.USP_DATAFORMTEMPLATE_EDIT_PLEDGEWRITEOFFBATCHROWCOMMIT (
    @ID uniqueidentifier
    ,@BATCHROWID uniqueidentifier
    ,@CHANGEAGENTID uniqueidentifier
    ,@WRITEOFFTOTALAMOUNT money
    ,@DATE datetime
    ,@POSTSTATUSCODE tinyint
    ,@POSTDATE datetime
    ,@REASONCODEID uniqueidentifier
    ,@REASON nvarchar(300)
    ,@INSTALLMENTS xml
    ,@RECOGNITIONCREDITS xml
    ,@RECOGNITIONCREDITADJUSTMENTCODE tinyint
    ,@PLEDGEAMOUNTMINUSWRITEOFFS money
    )
as
set nocount on;

declare @REVENUEID uniqueidentifier = @ID;
declare @WRITEOFFID uniqueidentifier = NewID ();

declare @CONSTITUENTSECURITY bit = (select CONSTITUENTSECURITY from dbo.REVENUEBATCHCONSTITUENTSECURITY); 
if @CONSTITUENTSECURITY = 1
begin
  declare @BATCHOWNERID uniqueidentifier;
  declare @CONSTITUENTID uniqueidentifier;
  select 
    @BATCHOWNERID = BATCH.APPUSERID, @CONSTITUENTID=C.CONSTITUENTID 
  from 
    dbo.BATCH
    inner join dbo.BATCHPLEDGEWRITEOFF on BATCH.ID = BATCHPLEDGEWRITEOFF.BATCHID
    inner join dbo.FINANCIALTRANSACTION C on BATCHPLEDGEWRITEOFF.REVENUEID=C.ID    
  where 
    BATCHPLEDGEWRITEOFF.ID = @BATCHROWID;

  if dbo.UFN_CONSTITUENT_CANAPPUSERMODIFY(@CONSTITUENTID, @BATCHOWNERID) = 0
    begin
      raiserror('BBERR_CONSTITUENTSECURITY',13,1);
    end
end

-- we have to reformat the installments to remove the batchinstallment.ID field and replace it with the installmentid

if @INSTALLMENTS IS NOT NULL
    set @INSTALLMENTS = (
            select INSTALLMENTID as ID
                ,date
                ,TRANSACTIONAMOUNT as AMOUNT
                ,BALANCE
                ,WRITEOFFAMOUNT
                ,SEQUENCE
                ,TRANSACTIONCURRENCYID
            from dbo.UFN_PLEDGEWRITEOFFBATCH_GETINSTALLMENTS_FROMITEMLISTXML(@INSTALLMENTS) 
            for xml raw('ITEM')
                ,type
                ,elements
                ,root('INSTALLMENTS')
                ,binary BASE64
            );

set @RECOGNITIONCREDITADJUSTMENTCODE = COALESCE(@RECOGNITIONCREDITADJUSTMENTCODE, 3);

-- RECOGNITIONCREDITADJUSTMENTCODES.ReduceRecognitionCreditsByProportionalAmount

if (@RECOGNITIONCREDITADJUSTMENTCODE = 0)
begin
    set @RECOGNITIONCREDITS = (
            select REVENUERECOGNITION.ID
                ,FINANCIALTRANSACTIONLINEITEM.ID as REVENUESPLITID
                ,dbo.UFN_DESIGNATION_BUILDNAME(REVENUESPLIT_EXT.DESIGNATIONID) as DESIGNATIONNAME
                ,CONSTITUENT.name as CONSTITUENTNAME
                ,REVENUERECOGNITIONTYPECODE.DESCRIPTION as RECOGNITIONCREDITTYPE
                ,REVENUERECOGNITION.EFFECTIVEDATE
                ,REVENUERECOGNITION.AMOUNT as ORIGINALAMOUNT
                ,(REVENUERECOGNITION.AMOUNT * (@PLEDGEAMOUNTMINUSWRITEOFFS - @WRITEOFFTOTALAMOUNT)) / @PLEDGEAMOUNTMINUSWRITEOFFS as ADJUSTEDAMOUNT
                ,REVENUERECOGNITION.BASECURRENCYID
            from dbo.FINANCIALTRANSACTION
            inner join dbo.REVENUE_EXT on FINANCIALTRANSACTION.ID = REVENUE_EXT.ID
            inner join dbo.FINANCIALTRANSACTIONLINEITEM on FINANCIALTRANSACTION.ID = FINANCIALTRANSACTIONLINEITEM.FINANCIALTRANSACTIONID
            inner join dbo.REVENUESPLIT_EXT on FINANCIALTRANSACTIONLINEITEM.ID = REVENUESPLIT_EXT.ID
            inner join dbo.REVENUERECOGNITION on FINANCIALTRANSACTIONLINEITEM.ID = REVENUERECOGNITION.REVENUESPLITID
            inner join dbo.CONSTITUENT on REVENUERECOGNITION.CONSTITUENTID = CONSTITUENT.ID
            left join dbo.REVENUERECOGNITIONTYPECODE on REVENUERECOGNITION.REVENUERECOGNITIONTYPECODEID = REVENUERECOGNITIONTYPECODE.ID
            where FINANCIALTRANSACTIONLINEITEM.FINANCIALTRANSACTIONID = @REVENUEID
                and FINANCIALTRANSACTIONLINEITEM.DELETEDON is null
                and FINANCIALTRANSACTIONLINEITEM.TYPECODE <> 1
            order by DESIGNATIONNAME
                ,REVENUESPLITID
            for xml raw('ITEM')
                ,type
                ,elements
                ,root('RECOGNITIONCREDITS')
                ,binary BASE64
            );
end

-- RECOGNITIONCREDITADJUSTMENTCODES.ReduceRecognitionCreditsByWriteOffAmount 

if (@RECOGNITIONCREDITADJUSTMENTCODE = 1)
begin
    declare @PRORATEDAMOUNTS table (
        ID uniqueidentifier
        ,AMOUNT money
        )
    -- adjust splits for new amount

    declare @CURRENTAMOUNT decimal(30, 5);
    declare @WEIGHT decimal(30, 10);
    declare @tempID uniqueidentifier;
    declare @ORIGINALAMOUNT decimal(30, 5);

  SELECT @ORIGINALAMOUNT=COALESCE(TRANSACTIONAMOUNT,0) FROM dbo.FINANCIALTRANSACTION WHERE ID=@REVENUEID;

    declare @NEWAMOUNT decimal(30, 5) = COALESCE (
        @WRITEOFFTOTALAMOUNT
        ,0
        );

    -- Load return table with current amounts to prorate

    insert into @PRORATEDAMOUNTS (
        AMOUNT
        ,ID
        )
    select FINANCIALTRANSACTIONLINEITEM.TRANSACTIONAMOUNT [AMOUNT]
          ,FINANCIALTRANSACTIONLINEITEM.ID
                from dbo.FINANCIALTRANSACTIONLINEITEM
                inner join dbo.REVENUESPLIT_EXT on FINANCIALTRANSACTIONLINEITEM.ID = REVENUESPLIT_EXT.ID
                inner join dbo.FINANCIALTRANSACTION on FINANCIALTRANSACTIONLINEITEM.FINANCIALTRANSACTIONID = FINANCIALTRANSACTION.ID
                where FINANCIALTRANSACTIONLINEITEM.FINANCIALTRANSACTIONID = @REVENUEID
                    and FINANCIALTRANSACTIONLINEITEM.DELETEDON is null
                    and FINANCIALTRANSACTIONLINEITEM.TYPECODE <> 1;

    -- Loop through and calculate new split amounts

    declare AMOUNTSTOPRORATECURSOR cursor local fast_forward
    for
    select ID
        ,AMOUNT
    from @PRORATEDAMOUNTS;

    open AMOUNTSTOPRORATECURSOR;

    fetch next
    from AMOUNTSTOPRORATECURSOR
    into @tempID
        ,@WEIGHT;

    while @@FETCH_STATUS = 0
    begin
        if @ORIGINALAMOUNT <> 0
            set @CURRENTAMOUNT = (@WEIGHT / @ORIGINALAMOUNT) * @NEWAMOUNT;
        else
            set @CURRENTAMOUNT = 0;

        update @PRORATEDAMOUNTS
        set AMOUNT = @CURRENTAMOUNT
        where ID = @tempID;

        set @NEWAMOUNT = @NEWAMOUNT + @CURRENTAMOUNT;
        set @ORIGINALAMOUNT = @ORIGINALAMOUNT + @WEIGHT;

        fetch next
        from AMOUNTSTOPRORATECURSOR
        into @tempID
            ,@WEIGHT;
    end

    close AMOUNTSTOPRORATECURSOR;

    deallocate AMOUNTSTOPRORATECURSOR;

    set @RECOGNITIONCREDITS = (
            select REVENUERECOGNITION.ID
                ,FINANCIALTRANSACTIONLINEITEM.ID as REVENUESPLITID
                ,dbo.UFN_DESIGNATION_BUILDNAME(REVENUESPLIT_EXT.DESIGNATIONID) as DESIGNATIONNAME
                ,CONSTITUENT.name as CONSTITUENTNAME
                ,REVENUERECOGNITIONTYPECODE.DESCRIPTION as RECOGNITIONCREDITTYPE
                ,REVENUERECOGNITION.EFFECTIVEDATE
                ,REVENUERECOGNITION.AMOUNT as ORIGINALAMOUNT
                ,case when REVENUERECOGNITION.AMOUNT > COALESCE(P.AMOUNT, 0) then REVENUERECOGNITION.AMOUNT - COALESCE(P.AMOUNT, 0) else 0 end as ADJUSTEDAMOUNT
                ,REVENUERECOGNITION.BASECURRENCYID
            from dbo.FINANCIALTRANSACTION
            inner join dbo.REVENUE_EXT on FINANCIALTRANSACTION.ID = REVENUE_EXT.ID
            inner join dbo.FINANCIALTRANSACTIONLINEITEM on FINANCIALTRANSACTION.ID = FINANCIALTRANSACTIONLINEITEM.FINANCIALTRANSACTIONID
            inner join dbo.REVENUESPLIT_EXT on FINANCIALTRANSACTIONLINEITEM.ID = REVENUESPLIT_EXT.ID
            inner join dbo.REVENUERECOGNITION on FINANCIALTRANSACTIONLINEITEM.ID = REVENUERECOGNITION.REVENUESPLITID
            inner join dbo.CONSTITUENT on REVENUERECOGNITION.CONSTITUENTID = CONSTITUENT.ID
            left join dbo.REVENUERECOGNITIONTYPECODE on REVENUERECOGNITION.REVENUERECOGNITIONTYPECODEID = REVENUERECOGNITIONTYPECODE.ID
            left join @PRORATEDAMOUNTS P on FINANCIALTRANSACTIONLINEITEM.ID = P.ID
            where FINANCIALTRANSACTIONLINEITEM.FINANCIALTRANSACTIONID = @REVENUEID
                and FINANCIALTRANSACTIONLINEITEM.DELETEDON is null
                and FINANCIALTRANSACTIONLINEITEM.TYPECODE <> 1
            order by DESIGNATIONNAME
                ,REVENUESPLITID
            for xml raw('ITEM')
                ,type
                ,elements
                ,root('RECOGNITIONCREDITS')
                ,binary BASE64
            );
end

begin try
  declare @BATCHID uniqueidentifier;
  select @BATCHID = BATCHID
  from dbo.BATCHPLEDGEWRITEOFF
  where ID = @BATCHROWID;

    exec dbo.USP_DATAFORMTEMPLATE_ADD_PLEDGEWRITEOFF_3 @ID = @WRITEOFFID
        ,@CHANGEAGENTID = @CHANGEAGENTID
        ,@REVENUEID = @REVENUEID
        ,@DATE = @DATE
        ,@WRITEOFFTOTALAMOUNT = @WRITEOFFTOTALAMOUNT
        ,@POSTSTATUSCODE = @POSTSTATUSCODE
        ,@POSTDATE = @POSTDATE
        ,@REASON = @REASON
        ,@INSTALLMENTS = @INSTALLMENTS
        ,@REASONCODEID = @REASONCODEID
        ,@RECOGNITIONCREDITS = @RECOGNITIONCREDITS
    ,@BATCHID = @BATCHID
    ;

    --the following is not necessary for the actual commit, it is just housekeeping to make the committed batch look like it did when the user committed (otherwise, it will be too up-to-date)

    -- RECOGNITIONCREDITADJUSTMENTCODES.DoNotAdjust

    if (@RECOGNITIONCREDITADJUSTMENTCODE <> 3)
    begin
        declare @CURRENTDATE datetime = getdate();
      delete from dbo.BATCHPLEDGEWRITEOFFRECOGNITIONCREDITS where BATCHPLEDGEWRITEOFFID=@BATCHROWID;
      insert into dbo.BATCHPLEDGEWRITEOFFRECOGNITIONCREDITS (BATCHPLEDGEWRITEOFFID, REVENUESPLITID, AMOUNT, ADDEDBYID, CHANGEDBYID, DATEADDED, DATECHANGED) select @BATCHROWID, REVENUESPLITID, ADJUSTEDAMOUNT, @CHANGEAGENTID, @CHANGEAGENTID, @CURRENTDATE, @CURRENTDATE from dbo.UFN_PLEDGEWRITEOFFBATCH_GETRECOGNITIONCREDITS_FROMITEMLISTXML(@RECOGNITIONCREDITS);    
    end

end try

begin catch
    exec.dbo.USP_RAISE_ERROR;

    return 1;
end catch

return 0;