USP_DATALIST_DISBURSEMENTPROCESS_TRANSACTIONS

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

-- Transactions being paid

select 
    ID
  -- display fields

    , VENDORNAME
    , AMOUNT
    , DISCOUNTAMOUNT 
    , NETAMOUNT
  , AMOUNTTOPAY
    , DUEDATE
    , PAYMENTMETHOD
  , REMITADDRESS
  -- grid interaction fields

  , PARENTID
  , DISBURSEMENTID
  , TYPECODE
  , TRANSACTIONRECORDID
    , RECORDID
  , VENDORSORTORDERNAME   -- Used to sort by constituent.keyname since individuals need to be sorted by last name but we want to display constituent.name

from (
 select
    FTS.ID
    , case when len(FTPAID.USERDEFINEDID) = 0 
            then '' 
            else FTPAID.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), FTPAID.[DATE], 101) 
        [VENDORNAME]
    ,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
    ,FTDISC.DISCOUNTAMOUNT
  ,case 
    when FTPAID.TYPECODE = 102 then -FTS.AMOUNT --Credit memos

    else FTS.AMOUNT + coalesce(FTDISC.DISCOUNTAMOUNT, 0)
   end NETAMOUNT
    ,case 
    when FTPAID.TYPECODE = 102 then -FTA.AMOUNT --Credit memos

    else FTA.AMOUNT
  end AMOUNTTOPAY
    ,FTS.DUEDATE DUEDATE
    ,'' PAYMENTMETHOD
  ,'' REMITADDRESS
    ,1 SORTORDER
  ,FT.ID PARENTID
  ,FT.ID DISBURSEMENTID
  ,FTPAID.TYPECODE
  ,FTPAID.ID TRANSACTIONRECORDID
    ,FTA.ID RECORDID
  ,null VENDORSORTORDERNAME
    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 and FTA.STATUSCODE != 2
        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 (
            select
                FTA.FINANCIALTRANSACTIONSCHEDULEID
                ,-FTA.AMOUNT [DISCOUNTAMOUNT]
            from dbo.FINANCIALTRANSACTIONAPPLICATION FTA
            where FTA.TYPECODE = 1 and FTA.STATUSCODE != 2
        ) as FTDISC on FTA.FINANCIALTRANSACTIONSCHEDULEID = FTDISC.FINANCIALTRANSACTIONSCHEDULEID

        left outer join dbo.INVOICE as I on FTPAID.ID= I.ID
        left outer join dbo.CREDITMEMO as CM on FTPAID.ID = CM.ID        
where
    DPD.DISBURSEMENTPROCESSID = @DISBURSEMENTPROCESSID
    and FT.TYPECODE = 255
    and FTA.AMOUNT > 0

union all

-- Disbursements Created

select 
    FT.ID
    ,C.NAME  VENDORNAME
    ,APPLIED.NETAMOUNT [TRANSACTIONAMOUNT]
    ,-APPLIED.DISCOUNTAMOUNT
    ,APPLIED.NETAMOUNT - APPLIED.DISCOUNTAMOUNT [NETAMOUNT]
    ,FT.TRANSACTIONAMOUNT [AMOUNTTOPAY]
    ,null DUEDATE
    ,'Check' PAYMENTMETHOD
    ,dbo.UFN_VENDOR_FORMATADDRESS(DPD.REMITADDRESSID) REMITADDRESS
    ,0 SORTORDER
    ,null PARENTID
    ,FT.ID DISBURSEMENTID
    ,-1 TYPECODE  -- Using a negative to keep from clashing with FT typecodes.

    ,C.ID TRANSACTIONRECORDID
    ,DPD.ID RECORDID
  ,C.KEYNAME VENDORSORTORDERNAME
    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 (
            select sum(
                        case FTA.TYPECODE 
                            when 1 then FTA.AMOUNT
                            else 0 
                        end
                     ) DISCOUNTAMOUNT
          , sum(
                        case FT.TYPECODE 
                            when 101 then FTA.AMOUNT
              when 102 then -FTA.AMOUNT
                            else 0 
                        end
                    ) AMOUNTTOPAY
                    , sum(
                        case FTA.TYPECODE 
                            when 0 then 
                            case FT.TYPECODE
                                when 101 then FTS.AMOUNT
                                when 102 then -FTS.AMOUNT
                                else 0
                            end
                            else 0 
                        end
                    ) NETAMOUNT
                    , FTA.FINANCIALTRANSACTIONID
                from 
                    dbo.FINANCIALTRANSACTIONAPPLICATION as FTA
                    inner join dbo.FINANCIALTRANSACTIONSCHEDULE as FTS on FTA.FINANCIALTRANSACTIONSCHEDULEID=FTS.ID and FTS.DELETED = 0
                    inner join dbo.FINANCIALTRANSACTION as FT on FTS.FINANCIALTRANSACTIONID = FT.ID
                where FTA.AMOUNT > 0
                group by FTA.FINANCIALTRANSACTIONID
        ) as APPLIED on APPLIED.FINANCIALTRANSACTIONID = FT.ID
where
    DPD.DISBURSEMENTPROCESSID = @DISBURSEMENTPROCESSID
    and FT.TYPECODE = 255
    and APPLIED.AMOUNTTOPAY >= 0
) AS T
order by T.VENDORSORTORDERNAME ASC, T.VENDORNAME ASC, T.NETAMOUNT DESC, T.SORTORDER ASC;