USP_DATALIST_DISBURSEMENTPROCESS_EXCLUDEDTRANSACTIONS

CREATE procedure dbo.USP_DATALIST_DISBURSEMENTPROCESS_EXCLUDEDTRANSACTIONS (
  @DISBURSEMENTPROCESSID uniqueidentifier
)
as
    set nocount on;

declare @FILTEREDTRANSACTIONS table (
  TRANSACTIONID uniqueidentifier
  , SCHEDULEID uniqueidentifier
  , [STATUS] varchar(1000)
    , [STATUSCODE] tinyint
);

insert into @FILTEREDTRANSACTIONS (
    TRANSACTIONID
    ,SCHEDULEID
    ,STATUS
    ,STATUSCODE
)
exec dbo.USP_DISBURSEMENTPROCESS_GETFILTEREDSCHEDULES @DISBURSEMENTPROCESSID;

-- Transactions that match the filter but we won't pay

select 
    FTS.ID [ID]
    ,C.NAME VENDORNAME
    ,case when len(FT.USERDEFINEDID) = 0 
            then '' 
            else FT.USERDEFINEDID + ' '
        end +
        case 
            when CM.ID is null and I.ID is not null then 'Invoice: '
            when I.ID is null and CM.ID is not null then 'Credit memo: '
            else ''
        end +
        convert(varchar(10), FT.[DATE], 101) 
    as [TRANSACTIONNUMBER]
    ,case 
        when I.ID is null and CM.ID is not null then -FTS.AMOUNT 
        when CM.ID is null and I.ID is not null then FTS.AMOUNT 
        else null
    end AMOUNT
    ,case
      when FTDISC.FINANCIALTRANSACTIONSCHEDULEID is null then 0
      else I.DISCOUNTAMOUNT
    end DISCOUNTAMOUNT  -- When a transaction is excluded we change the FTA amounts to zero so to get the discount amount we have to get it from the invoice (remember that you can only have 1 discount FTA per invoice).

    ,case 
    when FTS.AMOUNT = FT.TRANSACTIONAMOUNT then
      case 
        when FTDISC.FINANCIALTRANSACTIONSCHEDULEID is null then FTS.AMOUNT
          else FTS.AMOUNT - coalesce(I.DISCOUNTAMOUNT, 0)
      end
    else FTS.AMOUNT
  end NETAMOUNT  -- When a transaction is excluded we change the FTA amounts to zero so to get the net amount we have to get it from the invoice (remember that you can only have 1 discount FTA per invoice).

    ,FTS.DUEDATE DUEDATE
    ,T.STATUS
    ,T.STATUSCODE
 from 
    @FILTEREDTRANSACTIONS as T
    inner join dbo.FINANCIALTRANSACTIONSCHEDULE FTS on T.SCHEDULEID = FTS.ID and FTS.DELETED = 0
    inner join dbo.FINANCIALTRANSACTION FT on FTS.FINANCIALTRANSACTIONID = FT.ID
    inner join dbo.CONSTITUENT as C on FT.CONSTITUENTID = C.ID
    left outer join
    (
      select
        FTA.FINANCIALTRANSACTIONSCHEDULEID
      from dbo.FINANCIALTRANSACTIONAPPLICATION FTA
      where FTA.TYPECODE = 1
    ) as FTDISC on FTS.ID = FTDISC.FINANCIALTRANSACTIONSCHEDULEID
    left outer join dbo.INVOICE as I on FT.ID = I.ID
    left outer join dbo.CREDITMEMO as CM on FT.ID = CM.ID
where not T.STATUS is null