USP_DATAFORMTEMPLATE_EDITLOAD_PLEDGEWRITEOFFBATCHROW

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

select @TSLONG = B.TSLONG
    ,@DATALOADED = 1
    ,@CONSTITUENTNAME = NF.name
    ,@PLEDGEDATE = FINANCIALTRANSACTION.date
    ,@PLEDGEBALANCE = dbo.UFN_PLEDGE_GETBALANCE(FINANCIALTRANSACTION.ID)
    ,@DATE = B.date
    ,@WRITEOFFTOTALAMOUNT = B.WRITEOFFTOTALAMOUNT
    ,@POSTSTATUSCODE = B.POSTSTATUSCODE
    ,@POSTDATE = B.POSTDATE
    ,@REASON = B.REASON
    ,@REASONCODEID = B.REASONCODEID
    ,@RECOGNITIONCREDITADJUSTMENTCODE = B.RECOGNITIONCREDITADJUSTMENTCODE
    ,@TRANSACTIONTYPECODE = FINANCIALTRANSACTION.TYPECODE
    ,@TRANSACTIONCURRENCYID = FINANCIALTRANSACTION.TRANSACTIONCURRENCYID
    ,@BASECURRENCYID = FINANCIALTRANSACTION.BASEEXCHANGERATEID
    ,@EXCHANGERATE = COALESCE(CURRENCYEXCHANGERATE.RATE, 1)
    ,@REVENUEID = B.REVENUEID
  ,@SEQUENCE = B.SEQUENCE
    ,@PLEDGEAMOUNTMINUSWRITEOFFS = (
        FINANCIALTRANSACTION.TRANSACTIONAMOUNT - coalesce((
                select sum(INSTALLMENTSPLITWRITEOFF.TRANSACTIONAMOUNT)
                from dbo.INSTALLMENTSPLITWRITEOFF
                inner join dbo.INSTALLMENTSPLIT on INSTALLMENTSPLITWRITEOFF.INSTALLMENTSPLITID = INSTALLMENTSPLIT.ID
                where INSTALLMENTSPLIT.PLEDGEID = @REVENUEID
                ), 0)
        )
    ,@ORIGINALPLEDGEAMOUNT = FINANCIALTRANSACTION.TRANSACTIONAMOUNT
  ,@USERMANUALLYADJUSTEDRECOGNITIONCREDITS = B.USERMANUALLYADJUSTEDRECOGNITIONCREDITS 
  from dbo.BATCHPLEDGEWRITEOFF B
inner join dbo.FINANCIALTRANSACTION on B.REVENUEID = FINANCIALTRANSACTION.ID
inner join dbo.REVENUE_EXT on FINANCIALTRANSACTION.ID = REVENUE_EXT.ID
cross apply dbo.UFN_CONSTITUENT_DISPLAYNAME(FINANCIALTRANSACTION.CONSTITUENTID) NF
left join dbo.CURRENCYEXCHANGERATE on FINANCIALTRANSACTION.BASEEXCHANGERATEID = CURRENCYEXCHANGERATE.ID
where B.ID = @ID
    and FINANCIALTRANSACTION.DELETEDON is null;

if @DATALOADED = 1
begin
  declare @BATCHSTATUS tinyint;
  select @BATCHSTATUS=COALESCE(STATUSCODE,0) from dbo.BATCH inner join dbo.BATCHPLEDGEWRITEOFF on BATCH.ID=BATCHPLEDGEWRITEOFF.BATCHID where BATCHPLEDGEWRITEOFF.ID=@ID;
  if @BATCHSTATUS=0 --uncommitted batches only  
      set @INSTALLMENTS = (
              select ID
          ,INSTALLMENTID
                  ,date
                  ,TRANSACTIONAMOUNT as AMOUNT
                  ,BALANCE
                  ,WRITEOFFAMOUNT
                  ,SEQUENCE
                  ,TRANSACTIONCURRENCYID
              from dbo.UFN_PLEDGEWRITEOFFBATCH_GETINSTALLMENTS(@ID, @REVENUEID) WRITEOFFINSTALLMENT
              order by SEQUENCE
              for xml raw('ITEM')
                  ,type
                  ,elements
                  ,root('INSTALLMENTS')
                  ,binary BASE64
              );
  else
    set @INSTALLMENTS = (select 
          BATCHPLEDGEWRITEOFFINSTALLMENT.ID,
          INSTALLMENT.ID as INSTALLMENTID,
            INSTALLMENT.date,
            INSTALLMENT.TRANSACTIONAMOUNT AMOUNT,
            A.BALANCE as BALANCE,
            COALESCE(BATCHPLEDGEWRITEOFFINSTALLMENT.AMOUNT, 0) as WRITEOFFAMOUNT,
            INSTALLMENT.SEQUENCE,
            INSTALLMENT.TRANSACTIONCURRENCYID
        from dbo.INSTALLMENT
        left join dbo.BATCHPLEDGEWRITEOFFINSTALLMENT on INSTALLMENT.ID = BATCHPLEDGEWRITEOFFINSTALLMENT.INSTALLMENTID and BATCHPLEDGEWRITEOFFINSTALLMENT.BATCHPLEDGEWRITEOFFID = @ID
        left join dbo.UFN_INSTALLMENT_GETINSTALLMENTBALANCE_BULK() A on A.ID = INSTALLMENT.ID
        where INSTALLMENT.REVENUEID = @REVENUEID
            order by INSTALLMENT.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(@ID, @REVENUEID) 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 = @REVENUEID
                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 = @REVENUEID;

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

-- ****                
return 0;