UFN_BANKACCOUNTDISBURSEMENTTRANSACTIONS_TOITEMLISTXML

CREATE function dbo.UFN_BANKACCOUNTDISBURSEMENTTRANSACTIONS_TOITEMLISTXML(
    @DISBURSEMENTID uniqueidentifier,
    @REVERSALPOSTDATETYPECODE  tinyint,
    @REVERSALPOSTDATE date,
    @TRANSACTIONTYPE nvarchar(23))
returns xml
with execute as caller
as begin

            return (
                select
                    FT.ID [BANKACCOUNTTRANSACTIONID],
                    CASE WHEN FTPAID.DELETEDON IS NULL THEN 0 ELSE 1 END [DELETE],
                    FTPAID.ID [TRANSACTIONID],
                    FTPAID.CALCULATEDUSERDEFINEDID [NUMBER],
                    FTPAID.DATE [DATE],
                    FTPAID.DESCRIPTION,
                    I.DATEDUE,
                    case 
                        when I.ID is not null then FTS.AMOUNT 
                        else -FTS.AMOUNT 
                       end [SCHEDULEDAMOUNT],
                    FTPAID.TRANSACTIONAMOUNT [INVOICEAMOUNT],
                    FTPAID.POSTSTATUSCODE as [POSTSTATUSCODE],
                    FTPAID.POSTDATE,
                    FTPAID.TYPECODE,
                    1 as [REVERSALSTATUSCODE],
                    @REVERSALPOSTDATETYPECODE as [REVERSEON],
                    @REVERSALPOSTDATE as [REVERSALDATE],
                    @TRANSACTIONTYPE as [TRANSACTIONTYPE]
                from dbo.FINANCIALTRANSACTION as FT
                  inner join dbo.CONSTITUENT as C on FT.CONSTITUENTID = C.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
                  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
                                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
                    and FTPAID.DELETEDON IS NULL


    for xml raw('ITEM'),type,elements,root('TRANSACTIONS'),BINARY BASE64
  )
end