UFN_DISBURSEMENTTRANSACTIONS_TOITEMLISTXML

CREATE function dbo.UFN_DISBURSEMENTTRANSACTIONS_TOITEMLISTXML(
  @DISBURSEMENTID uniqueidentifier
)
returns xml
with execute as caller
as begin
    return (
    select 
      FTA.ID
      ,case
        when FTA.AMOUNT = 0 then 0
        else 1
       end [INCLUDE]
      ,FTPAID.USERDEFINEDID  [NUMBER]
      ,FTPAID.TYPECODE
      ,FTPAID.[DATE] [TRANSACTIONDATE]
      ,FTS.DUEDATE [DUEDATE]
      ,case 
        when I.ID is not null then FTS.AMOUNT 
        else -FTS.AMOUNT 
       end [SCHEDULEDAMOUNT]
      ,FTDISC.POSSIBLEDISCOUNT [DISCOUNTAMOUNT]
      ,FTDISC.DISCOUNTAMOUNT [DISCOUNTTAKEN]
      ,case 
        when I.ID is not null then FTS.AMOUNT + coalesce(FTDISC.POSSIBLEDISCOUNT, 0)
        else -FTS.AMOUNT 
       end [NETAMOUNT]
      ,case 
        when I.ID is not null then FTA.AMOUNT
        else -FTA.AMOUNT
       end [AMOUNTTOPAY]
    from dbo.FINANCIALTRANSACTION as FT
      inner join dbo.CONSTITUENT as C on FT.CONSTITUENTID = C.ID
      inner join dbo.DISBURSEMENTPROCESSDISBURSEMENT as DPD on FT.ID = DPD.ID
      inner join dbo.FINANCIALTRANSACTIONAPPLICATION as FTA on FT.ID = FTA.FINANCIALTRANSACTIONID and FTA.TYPECODE = 0
      inner join dbo.FINANCIALTRANSACTIONSCHEDULE as FTS on FTA.FINANCIALTRANSACTIONSCHEDULEID = FTS.ID and FTS.DELETED = 0
      inner join dbo.FINANCIALTRANSACTION as FTPAID on FTS.FINANCIALTRANSACTIONID = FTPAID.ID
      left outer join dbo.INVOICE as I on FTPAID.ID = I.ID
      left outer join dbo.CREDITMEMO as CM on FTPAID.ID = CM.ID
            left outer join (
                select
                    FTA.FINANCIALTRANSACTIONSCHEDULEID
                    ,-FTA.AMOUNT [DISCOUNTAMOUNT]
                    ,-I.DISCOUNTAMOUNT [POSSIBLEDISCOUNT]
                from dbo.FINANCIALTRANSACTIONAPPLICATION FTA
                    inner join dbo.FINANCIALTRANSACTIONSCHEDULE FTS on FTA.FINANCIALTRANSACTIONSCHEDULEID = FTS.ID and FTS.DELETED = 0
                    inner join dbo.INVOICE I on FTS.FINANCIALTRANSACTIONID = I.ID
                    inner join dbo.DISBURSEMENTPROCESSDISBURSEMENT DPD on DPD.ID = FTA.FINANCIALTRANSACTIONID
                    inner join dbo.DISBURSEMENTPROCESS DP on DPD.DISBURSEMENTPROCESSID = DP.ID
                where FTA.TYPECODE = 1
                    and (I.DISCOUNTEXPIRATIONDATE is NULL or I.DISCOUNTEXPIRATIONDATE >= DP.DISCOUNTASOFDATE)
            ) as FTDISC on FTA.FINANCIALTRANSACTIONSCHEDULEID = FTDISC.FINANCIALTRANSACTIONSCHEDULEID
    where
      FT.TYPECODE = 255
      and FT.ID = @DISBURSEMENTID
    for xml raw('ITEM'),type,elements,root('DISBURSEMENT'),BINARY BASE64
  )
end