USP_DATAFORMTEMPLATE_EDITLOAD_REVENUE_INSTALLMENTS_2

CREATE procedure dbo.USP_DATAFORMTEMPLATE_EDITLOAD_REVENUE_INSTALLMENTS_2 (
  @ID uniqueidentifier
  ,@DATALOADED bit = 0 output
  ,@AMOUNT money = null output
  ,@RESCHEDULEBALANCE money = null output
  ,@PLEDGEDATE datetime = null output
  ,@LASTPAYMENTSEQUENCE int = null output
  ,@FREQUENCYCODE tinyint = null output
  ,@NUMBEROFINSTALLMENTS int = null output
  ,@STARTDATE datetime = null output
  ,@INSTALLMENTS xml = null output
  ,@TSLONG bigint = 0 output
  ,@SPLITS xml = null output
  ,@SINGLEDESIGNATIONID uniqueidentifier = null output
  ,@TRANSACTIONTYPECODE tinyint = null output
  ,@LASTUNPAIDROW int = null output
  ,@TRANSACTIONCURRENCYID uniqueidentifier = null output
  ,@ISMEMBERSHIPPLEDGE bit = null output
  ,@TRANSACTIONHASDESIGNATIONS bit = null output
  ,@HASPOSTEDPAYMENTS bit = null output
  ,@HASPOSTEDWRITEOFFS bit = null output
  ,@HASPAYMENTS bit = null output
  ,@ADJPAYMENT_DATE datetime = null output
  ,@ADJPAYMENT_POSTDATE datetime = null output
  ,@ADJPAYMENT_REASONCODEID uniqueidentifier = null output
  ,@ADJPAYMENT_DETAILS nvarchar(255) = null output
  ,@BENEFITVALUE money = null output
  ,@INSTALLMENTAMOUNT money = null output
  ,@ORIGINALINSTALLMENTAMOUNT money = null output
  ,@ISPOSTED bit = null output
  ,@ISGRANTAWARD bit = null output
  ,@TOTALAMOUNTWRITTENOFF money = null output
  )
as
begin
  set nocount on;
  set @DATALOADED = 0;
  set @TSLONG = 0;

  select @DATALOADED = 1
    ,@AMOUNT = FINANCIALTRANSACTION.TRANSACTIONAMOUNT
    ,@PLEDGEDATE = cast(FINANCIALTRANSACTION.date as datetime)
    ,@FREQUENCYCODE = REVENUESCHEDULE.FREQUENCYCODE
    ,@NUMBEROFINSTALLMENTS = REVENUESCHEDULE.NUMBEROFINSTALLMENTS
    ,@TRANSACTIONTYPECODE = FINANCIALTRANSACTION.TYPECODE
    ,@TRANSACTIONCURRENCYID = FINANCIALTRANSACTION.TRANSACTIONCURRENCYID
    ,@ISPOSTED = case FINANCIALTRANSACTION.POSTSTATUSCODE when 2 then 1 else 0 end
    ,@ISGRANTAWARD = case FINANCIALTRANSACTION.TYPECODE when 6 then 1 else 0 end
    ,@ISMEMBERSHIPPLEDGE = case when FINANCIALTRANSACTION.TYPECODE = 15 then 1 else 0 end
  from dbo.FINANCIALTRANSACTION
  inner join dbo.REVENUE_EXT on FINANCIALTRANSACTION.ID = REVENUE_EXT.ID
  inner join dbo.REVENUESCHEDULE on FINANCIALTRANSACTION.ID = REVENUESCHEDULE.ID
  where FINANCIALTRANSACTION.ID = @ID
    and FINANCIALTRANSACTION.DELETEDON is null;

  select @INSTALLMENTAMOUNT = PLEDGEINSTALLMENTOPTION.INSTALLMENTAMOUNT
  from dbo.FINANCIALTRANSACTION
  left outer join dbo.PLEDGEINSTALLMENTOPTION on FINANCIALTRANSACTION.ID = PLEDGEINSTALLMENTOPTION.ID 
  where FINANCIALTRANSACTION.ID = @ID and FINANCIALTRANSACTION.DELETEDON is null; 

  set @ORIGINALINSTALLMENTAMOUNT = @INSTALLMENTAMOUNT;

  select @TOTALAMOUNTWRITTENOFF = isnull(SUM(I.AMOUNTWRITTENOFF) ,0)
    from dbo.UFN_INSTALLMENT_GETINFO_BULK() I
    where I.REVENUEID = @ID


  if @DATALOADED = 1
  begin
    set @TRANSACTIONHASDESIGNATIONS = dbo.UFN_FINANCIALTRANSACTION_HASDESIGNATIONS(@ID)
    set @HASPAYMENTS = dbo.UFN_PLEDGE_PAYMENTSEXIST(@ID);

    if @HASPAYMENTS = 0
      set @HASPOSTEDPAYMENTS = 0 --if there are no payments, then there can't be posted payments
    else
      set @HASPOSTEDPAYMENTS = dbo.UFN_REVENUE_HASPOSTEDPAYMENTS(@ID)

    set @HASPOSTEDWRITEOFFS = dbo.UFN_REVENUE_HASPOSTEDWRITEOFFS(@ID);

    --Find the first and last installment that have a full balance/no payments
    select @RESCHEDULEBALANCE = COALESCE(SUM(INSTALLMENT.BALANCE), 0)
      ,@STARTDATE = COALESCE(MIN(INSTALLMENT.date), @PLEDGEDATE)
      ,@LASTPAYMENTSEQUENCE = COALESCE(MAX(INSTALLMENT.SEQUENCE), 0)
      ,@NUMBEROFINSTALLMENTS = COUNT(INSTALLMENT.ID)
    from dbo.UFN_INSTALLMENT_GETINSTALLMENTS_2(@ID) INSTALLMENT
    where INSTALLMENT.BALANCE > 0;

    set @LASTUNPAIDROW = @LASTPAYMENTSEQUENCE;
    set @INSTALLMENTS = (
        select I.ID
          ,I.[DATE]
          ,I.TRANSACTIONAMOUNT AMOUNT
          ,I. TRANSACTIONRECEIPTAMOUNT as RECEIPTAMOUNT
          ,(I.TRANSACTIONAMOUNT - (I.AMOUNTPAID + I.AMOUNTWRITTENOFF)) as BALANCE
     ,(I.AMOUNTPAID + I.AMOUNTWRITTENOFF) as APPLIED
          ,I.SEQUENCE
          ,I.PAYMENTCOUNT
          ,I.WRITEOFFCOUNT
          ,I.AMOUNTWRITTENOFF
          ,(
            select SPLITINFO.ID
              ,SPLITINFO.DESIGNATIONID
              ,SPLITINFO.TRANSACTIONAMOUNT AMOUNT
              ,(SPLITINFO.AMOUNTPAID + SPLITINFO.AMOUNTWRITTENOFF) APPLIED
              ,SPLITINFO.TRANSACTIONCURRENCYID
              ,SPLITINFO.REVENUESPLITID
              ,SPLITINFO.PAYMENTCOUNT
              ,SPLITINFO.WRITEOFFCOUNT
              ,(
                select SPLITPAYMENT.ID
                      ,SPLITPAYMENT.AMOUNT
                      ,I.REVENUEID as TRANSACTIONID
                      ,SPLITINFO.REVENUESPLITID as LINEITEMID
                from dbo.INSTALLMENTSPLITPAYMENT SPLITPAYMENT
                where SPLITPAYMENT.INSTALLMENTSPLITID = SPLITINFO.ID
                for xml raw('ITEM')
                  ,type
                  ,elements
                  ,binary BASE64
               ) as INSTALLMENTSPLITPAYMENTS
              ,(
                select SPLITWRITEOFF.ID
                      ,SPLITWRITEOFF.AMOUNT
                      ,I.REVENUEID as TRANSACTIONID
                      ,SPLITINFO.REVENUESPLITID as LINEITEMID
                from dbo.INSTALLMENTSPLITWRITEOFF SPLITWRITEOFF
                where SPLITWRITEOFF.INSTALLMENTSPLITID = SPLITINFO.ID
                for xml raw('ITEM')
                  ,type
                  ,elements
                  ,binary BASE64
               ) as INSTALLMENTSPLITWRITEOFFS
            from dbo.UFN_INSTALLMENT_GETINSTALLMENTSPLITINFO_BULK() SPLITINFO
            left join dbo.DESIGNATION on DESIGNATION.ID = SPLITINFO.DESIGNATIONID
            where SPLITINFO.INSTALLMENTID = I.ID
            order by DESIGNATION.VANITYNAME
            for xml raw('ITEM')
              ,type
              ,elements
              ,binary BASE64
            ) as INSTALLMENTSPLITS
          ,I.BASECURRENCYID
          ,I.ORGANIZATIONAMOUNT
          ,I.ORGANIZATIONEXCHANGERATEID
          ,I.TRANSACTIONCURRENCYID
          ,I.BASEEXCHANGERATEID
        from dbo.UFN_INSTALLMENT_GETINFO_BULK() I
        where I.REVENUEID = @ID
        order by I.[DATE]
        for xml raw('ITEM')
          ,type
          ,elements
          ,root('INSTALLMENTS')
          ,binary BASE64
        );
    set @SPLITS = (
        select SPLIT.ID
          ,SPLIT.DESIGNATIONID
          ,SPLIT.TRANSACTIONAMOUNT as AMOUNT
          ,SPLIT.TRANSACTIONCURRENCYID
        from dbo.UFN_REVENUE_GETSPLITS_2(@ID) SPLIT
        for xml raw('ITEM')
          ,type
          ,elements
          ,root('SPLITS')
          ,binary BASE64
        );
  end

  if @INSTALLMENTS is not null
    select @TSLONG = max(TSLONG)
    from dbo.INSTALLMENT
    where INSTALLMENT.REVENUEID = @ID;
  else
    set @TSLONG = 0;

  select
    @BENEFITVALUE = sum(REVENUEBENEFIT.TOTALVALUE)
  from dbo.REVENUEBENEFIT
  where REVENUEBENEFIT.REVENUEID = @ID

  return 0;
end