USP_DATAFORMTEMPLATE_VIEW_DONORINFORMATIONPAYMENTDETAIL

CREATE procedure dbo.USP_DATAFORMTEMPLATE_VIEW_DONORINFORMATIONPAYMENTDETAIL (
  @ID nvarchar(73)
  ,@DATALOADED bit = 0 output
  ,@PAYMENTID uniqueidentifier = null output
  ,@GIVENANONYMOUSLY bit = null output
  ,@AMOUNT money = null output
  ,@BASECURRENCYID uniqueidentifier = null output
  ,@TRANSACTIONTYPE nvarchar(100) = null output
  ,@TOTALSPLITS int = null output
  ,@TOTALAPPLIEDTOTHISPURPOSE money = null output
  ,@THISPURPOSESPLITS xml = null output
  ,@TOTALAPPLIEDTOOTHERPURPOSES money = null output
  ,@OTHERPURPOSESPLITS xml = null output
  ,@TRANSACTIONCURRENCYID uniqueidentifier = null output
  )
as
begin
  set nocount on;
  set @DATALOADED = 0;

  declare @PURPOSEID uniqueidentifier = null;

  select @PAYMENTID = cast(substring(@ID, 0, 37) as uniqueidentifier)

  select @PURPOSEID = cast(substring(@ID, 38, 36) as uniqueidentifier)

  select @DATALOADED = 1
    ,@BASECURRENCYID = null
    ,@TRANSACTIONTYPE = FT.TYPE
    ,@GIVENANONYMOUSLY = REVENUE_EXT.GIVENANONYMOUSLY
    ,@TRANSACTIONCURRENCYID = FT.TRANSACTIONCURRENCYID
  from dbo.FINANCIALTRANSACTION FT
  inner join dbo.REVENUE_EXT on FT.ID = REVENUE_EXT.ID
  where FT.ID = @PAYMENTID

  declare @designationttable table (
    ID uniqueidentifier
    ,USERID nvarchar(512)
    ,AMOUNT money
    ,CURRENCYID uniqueidentifier
    ,THISPURPOSE bit
    );

  insert into @designationttable (
    ID
    ,USERID
    ,AMOUNT
    ,CURRENCYID
    ,THISPURPOSE
    )
  select D.ID
    ,[USERID] = D.USERID
    ,PAYMENTLINEITEM.TRANSACTIONAMOUNT
    ,@TRANSACTIONCURRENCYID
    ,THISPURPOSE = case 
      when @PURPOSEID in (
          D.DESIGNATIONLEVEL1ID
          ,D.DESIGNATIONLEVEL2ID
          ,D.DESIGNATIONLEVEL3ID
          ,D.DESIGNATIONLEVEL4ID
          ,D.DESIGNATIONLEVEL5ID
          )
        then 1
      else 0
      end
  from dbo.FINANCIALTRANSACTION PAYMENT with (nolock)
  inner join dbo.FINANCIALTRANSACTIONLINEITEM PAYMENTLINEITEM with (nolock) on PAYMENT.ID = PAYMENTLINEITEM.FINANCIALTRANSACTIONID
    and PAYMENT.DELETEDON is null
    and PAYMENTLINEITEM.DELETEDON is null
    and PAYMENT.TYPECODE = 0
  inner join dbo.REVENUESPLIT_EXT PAYMENTAPPLICATION with (nolock) on PAYMENTLINEITEM.ID = PAYMENTAPPLICATION.ID
  inner join dbo.DESIGNATION D with (nolock) on PAYMENTAPPLICATION.DESIGNATIONID = D.ID
  where PAYMENT.ID = @PAYMENTID
    and (
      PAYMENTAPPLICATION.APPLICATIONCODE in (
        0
        ,1
        ,4
        ,9
        ,10
        ,11
        ,12
        ,15
        ,16
        ,18
        )
      or (
        PAYMENTAPPLICATION.APPLICATIONCODE = 7
        and not exists (
          select 1
          from dbo.INSTALLMENTSPLITPAYMENT
          where PAYMENTID = PAYMENTLINEITEM.ID
          )
        )
      )

  select @AMOUNT = sum(AMOUNT)
    ,@TOTALSPLITS = COUNT(distinct ID) 
    ,@TOTALAPPLIEDTOTHISPURPOSE = sum(case 
        when THISPURPOSE = 1
          then AMOUNT
        else 0
        end)
    ,@TOTALAPPLIEDTOOTHERPURPOSES = sum(case 
        when THISPURPOSE = 0
          then AMOUNT
        else 0
        end)
  from @designationttable;

  set @THISPURPOSESPLITS = (
      select top 6 [ID] = D.ID
        ,[AMOUNT] = SUM(D.AMOUNT)
        ,[DESIGNATION] = D.USERID
        ,[TRANSACTIONCURRENCYID] = @TRANSACTIONCURRENCYID
      from @designationttable D
      where THISPURPOSE = 1
      group by D.ID
        ,D.USERID
      order by [AMOUNT] desc
        ,D.USERID
      for xml raw('ITEM')
        ,type
        ,elements
        ,root('THISPURPOSESPLITS')
        ,binary BASE64
      )
  set @THISPURPOSESPLITS = isnull(@THISPURPOSESPLITS, '<THISPURPOSESPLITS></THISPURPOSESPLITS>')
  set @OTHERPURPOSESPLITS = (
      select top 6 [ID] = D.ID
        ,[AMOUNT] = SUM(D.AMOUNT)
        ,[DESIGNATION] = D.USERID
        ,[TRANSACTIONCURRENCYID] = @TRANSACTIONCURRENCYID
      from @designationttable D
      where THISPURPOSE = 0
      group by D.ID
        ,D.USERID
      order by [AMOUNT] desc
        ,D.USERID
      for xml raw('ITEM')
        ,type
        ,elements
        ,root('OTHERPURPOSESPLITS')
        ,binary BASE64
      )
  set @OTHERPURPOSESPLITS = isnull(@OTHERPURPOSESPLITS, '<OTHERPURPOSESPLITS></OTHERPURPOSESPLITS>');

  return 0;
end