USP_DATAFORMTEMPLATE_EDITLOAD_PLEDGEWRITEOFFBATCHROWCOMMIT

CREATE procedure dbo.USP_DATAFORMTEMPLATE_EDITLOAD_PLEDGEWRITEOFFBATCHROWCOMMIT (
    @ID uniqueidentifier
    ,@DATALOADED bit = 0 output
    ,@TSLONG bigint = 0 output
    ,@BATCHROWID uniqueidentifier = null output
    ,@CONSTITUENTNAME nvarchar(700) = null output
    ,@PLEDGEDATE datetime = null output
    ,@PLEDGEBALANCE money = null output
    ,@POSTSTATUSCODE tinyint = null output
    ,@INSTALLMENTS xml = null output
    ,@TRANSACTIONTYPECODE tinyint = null output
    ,@RECOGNITIONCREDITS xml = null output
    ,@REVENUESPLITS xml = null output
    ,@ORIGINALPLEDGEAMOUNT money = null output
    ,@PLEDGEAMOUNTMINUSWRITEOFFS money = null output
    ,@TRANSACTIONCURRENCYID uniqueidentifier = null output
    ,@BASECURRENCYID uniqueidentifier = null output
    ,@EXCHANGERATE decimal(20, 8) = null output
    ,@ALLOWGLDISTRIBUTIONS bit = null output
    ,@WRITEOFFTOTALAMOUNT money = null output
  ,@DATE datetime = null output
  ,@POSTDATE datetime = null output
    ,@REASONCODEID uniqueidentifier = null output
    ,@REASON nvarchar(300) = null output
  ,@RECOGNITIONCREDITADJUSTMENTCODE tinyint = null output
    )
as
set nocount on;
set @DATALOADED = 0;
set @TSLONG = 0;

select @TSLONG = FINANCIALTRANSACTION.TSLONG
    ,@DATALOADED = 1
    ,@CONSTITUENTNAME = NF.name
    ,@PLEDGEDATE = FINANCIALTRANSACTION.date
    ,@PLEDGEBALANCE = dbo.UFN_PLEDGE_GETBALANCE(FINANCIALTRANSACTION.ID)
    ,@TRANSACTIONTYPECODE = FINANCIALTRANSACTION.TYPECODE
    ,@TRANSACTIONCURRENCYID = FINANCIALTRANSACTION.TRANSACTIONCURRENCYID
    ,@BASECURRENCYID = FINANCIALTRANSACTION.BASEEXCHANGERATEID
    ,@EXCHANGERATE = case 
        when FINANCIALTRANSACTION.TRANSACTIONCURRENCYID = CURRENCYSET.BASECURRENCYID
            then 1
        else coalesce(CURRENCYEXCHANGERATE.RATE, 0)
        end
    ,@POSTSTATUSCODE = case 
        when (
                case FINANCIALTRANSACTION.POSTSTATUSCODE
                    when 3
                        then 1
                    else 0
                    end
                ) = 1
            then 2
        else 1
        end
    ,@PLEDGEAMOUNTMINUSWRITEOFFS = (
        FINANCIALTRANSACTION.TRANSACTIONAMOUNT - coalesce((
                select sum(INSTALLMENTSPLITWRITEOFF.TRANSACTIONAMOUNT)
                from dbo.INSTALLMENTSPLITWRITEOFF
                inner join dbo.INSTALLMENTSPLIT on INSTALLMENTSPLITWRITEOFF.INSTALLMENTSPLITID = INSTALLMENTSPLIT.ID
                where INSTALLMENTSPLIT.PLEDGEID = @ID
                ), 0)
        )
    ,@ORIGINALPLEDGEAMOUNT = FINANCIALTRANSACTION.TRANSACTIONAMOUNT
from dbo.FINANCIALTRANSACTION
inner join dbo.REVENUE_EXT on FINANCIALTRANSACTION.ID = REVENUE_EXT.ID
inner join dbo.PDACCOUNTSYSTEM on FINANCIALTRANSACTION.PDACCOUNTSYSTEMID = PDACCOUNTSYSTEM.ID
inner join dbo.CURRENCYSET on PDACCOUNTSYSTEM.CURRENCYSETID = CURRENCYSET.ID
cross apply dbo.UFN_CONSTITUENT_DISPLAYNAME(FINANCIALTRANSACTION.CONSTITUENTID) NF
left join dbo.CURRENCYEXCHANGERATE on FINANCIALTRANSACTION.BASEEXCHANGERATEID = CURRENCYEXCHANGERATE.ID
where FINANCIALTRANSACTION.ID = @ID
    and FINANCIALTRANSACTION.DELETEDON is null;

if @DATALOADED = 1
begin
    set @INSTALLMENTS = (
            select ID
                ,INSTALLMENTID
                ,date
                ,TRANSACTIONAMOUNT as AMOUNT
                ,BALANCE
                ,WRITEOFFAMOUNT
                ,SEQUENCE
                ,TRANSACTIONCURRENCYID
            from dbo.UFN_PLEDGEWRITEOFFBATCH_GETINSTALLMENTS(@BATCHROWID, @ID) WRITEOFFINSTALLMENT
            order by SEQUENCE
            for xml raw('ITEM')
                ,type
                ,elements
                ,root('INSTALLMENTS')
                ,binary BASE64
            );
    set @RECOGNITIONCREDITS = (
      select ID
                ,REVENUESPLITID
                ,DESIGNATIONNAME
                ,CONSTITUENTNAME
                ,RECOGNITIONCREDITTYPE
                ,EFFECTIVEDATE
                ,ORIGINALAMOUNT
                ,ADJUSTEDAMOUNT
                ,BASECURRENCYID
            from dbo.UFN_PLEDGEWRITEOFFBATCH_GETRECOGNITIONCREDITS(NULL, @ID) RECOGNITIONCREDITS
            order by DESIGNATIONNAME, REVENUESPLITID, CONSTITUENTNAME
            for xml raw('ITEM')
                ,type
                ,elements
                ,root('RECOGNITIONCREDITS')
                ,binary BASE64
            );
    set @REVENUESPLITS = (
            select FINANCIALTRANSACTIONLINEITEM.ID
                ,FINANCIALTRANSACTIONLINEITEM.TRANSACTIONAMOUNT [AMOUNT]
                ,FINANCIALTRANSACTION.TRANSACTIONCURRENCYID
            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 = @ID
                and FINANCIALTRANSACTIONLINEITEM.DELETEDON is null
                and FINANCIALTRANSACTIONLINEITEM.TYPECODE <> 1
            for xml raw('ITEM')
                ,type
                ,elements
                ,root('REVENUESPLITS')
                ,binary BASE64
            )
end

-- Check GL business rule for this account system and set to 'Do not post' if needed.

-- ****

declare @PDACCOUNTSYSTEMID uniqueidentifier;

select @PDACCOUNTSYSTEMID = PDACCOUNTSYSTEMID
from dbo.PDACCOUNTSYSTEMFORREVENUE
where ID = @ID;

set @ALLOWGLDISTRIBUTIONS = dbo.UFN_PDACCOUNTSYSTEM_ALLOWGLDISTRIBUTIONS(@PDACCOUNTSYSTEMID);

-- ****                

return 0;