V_DISBURSEMENT_SHOWTRANSACTIONS

CREATE view dbo.V_DISBURSEMENT_SHOWTRANSACTIONS
as

select
      FT.ID                                                             as TRANSACTIONID
      ,FT.CALCULATEDUSERDEFINEDID                                                    as TRANSACTIONNUMBER
      ,FT.[DATE]                                                        as TRANSACTIONDATE            
      ,FT.[DESCRIPTION]                                                    as TRANSACTIONDESCRIPTION
      ,case FT.TYPECODE
            when 101 then (APPLIED.AMOUNT + APPLIED.DISCOUNTAMOUNT)        -- 101 = Invoice
            when 102 then -APPLIED.AMOUNT                                -- 102 = Credit Memo (a credit memo shows as negative)
       end                                                              as TRANSACTIONAMOUNT
      ,-APPLIED.DISCOUNTAMOUNT                                            as TRANSACTIONDISCOUNTAMOUNT     
      ,case FT.TYPECODE
            when 101 then (APPLIED.AMOUNT)                                -- 101 = Invoice
            when 102 then -APPLIED.AMOUNT                                -- 102 = Credit Memo (a credit memo shows as negative)
       end                                                              as TRANSACTIONNETAMOUNT
      ,FTD.ID                                                           as DISBURSEMENTID
      ,FT.TYPECODE                                                      as TRANSACTIONTYPECODE
from 
      dbo.FINANCIALTRANSACTION as FTD
      inner join
      (
            select 
                  sum 
                  (
                        case FTAD.TYPECODE
                              when 0 then FTAD.AMOUNT                    -- 0 = Regular
                              else 0                                    -- If this is a discount then return 0.
                        end
                  )                                                        as AMOUNT
                  ,sum 
                  (
                        case FTAD.TYPECODE
                              when 1 then FTAD.AMOUNT                    -- 1 = Discount
                              else 0                                    -- If this is a regular then return 0.
                        end 
                  )                                                        as DISCOUNTAMOUNT
                  ,FTAD.FINANCIALTRANSACTIONID
                  ,FTS.FINANCIALTRANSACTIONID as SCHEDULETRANSACTIONID
            from 
                  dbo.FINANCIALTRANSACTIONAPPLICATION as FTAD
                  inner join dbo.FINANCIALTRANSACTIONSCHEDULE as FTS on FTAD.FINANCIALTRANSACTIONSCHEDULEID = FTS.ID
            group by 
                  FTAD.FINANCIALTRANSACTIONID
                  ,FTS.FINANCIALTRANSACTIONID
      ) as APPLIED on FTD.ID = APPLIED.FINANCIALTRANSACTIONID
      inner join dbo.FINANCIALTRANSACTION as FT on APPLIED.SCHEDULETRANSACTIONID= FT.ID
where
    FTD.TYPECODE = 255 or FTD.TYPECODE = 9