USP_DATALIST_DISBURSEMENTPROCESS_HISTORY

CREATE procedure dbo.USP_DATALIST_DISBURSEMENTPROCESS_HISTORY
(
    @DISBURSEMENTPROCESSID uniqueidentifier
    ,@PAYMENTMETHODCODE tinyint = null
    ,@STATUSCODE integer = null
)
as
    set nocount on;

    declare @temp table ([ID] uniqueidentifier, [FINANCIALTRANSACTIONID] uniqueidentifier, [ACTION] nvarchar(100), [ORIGINALNUMBER] int, [CHANGEDNUMBER] int, [FUTUREFINANCIALTRANSACTIONID] uniqueidentifier, [ADDEDBYID] uniqueidentifier, [DATEADDED] datetime);

    insert into @temp
    select H.ID, H.[FINANCIALTRANSACTIONID], H.[ACTION], H.[ORIGINALNUMBER], H.[CHANGEDNUMBER], H.[FUTUREFINANCIALTRANSACTIONID], H.[ADDEDBYID], H.[DATEADDED]
    from dbo.DISBURSEMENTHISTORY H
    left outer join dbo.BANKACCOUNTTRANSACTION_EXT as BATE on H.[FINANCIALTRANSACTIONID] = BATE.ID
    left outer join dbo.DISBURSEMENTPROCESSDISBURSEMENT DPD on H.FINANCIALTRANSACTIONID = DPD.ID
    where BATE.DISBURSEMENTPROCESSID = @DISBURSEMENTPROCESSID or DPD.DISBURSEMENTPROCESSID = @DISBURSEMENTPROCESSID

    update @temp
        set FUTUREFINANCIALTRANSACTIONID = T1.FUTUREFINANCIALTRANSACTIONID
    from @temp T
    inner join (select FINANCIALTRANSACTIONID, FUTUREFINANCIALTRANSACTIONID 
        from @temp where FUTUREFINANCIALTRANSACTIONID is not null) T1 on T1.FINANCIALTRANSACTIONID = T.FINANCIALTRANSACTIONID;

    update @temp
        set FUTUREFINANCIALTRANSACTIONID = null
    where FUTUREFINANCIALTRANSACTIONID not in (select FINANCIALTRANSACTIONID from @temp);

    WITH DisbursementHistoryInfo (ID, FINANCIALTRANSACTIONID, ORIGINALID)
    AS
    (
        select H.ID,  H.FINANCIALTRANSACTIONID, H.FINANCIALTRANSACTIONID
        from @temp H
        where H.FUTUREFINANCIALTRANSACTIONID is null

        union all

        select H.ID, HI.FINANCIALTRANSACTIONID, H.FINANCIALTRANSACTIONID
        from @temp H
        inner join DisbursementHistoryInfo HI on H.FUTUREFINANCIALTRANSACTIONID = HI.ORIGINALID and H.FINANCIALTRANSACTIONID not in (HI.ORIGINALID)
    )

    select DISTINCT FT.ID
        ,ISNULL(cast(BAT.TRANSACTIONNUMBER as nvarchar), 
            (select MAX(CASE WHEN T.CHANGEDNUMBER = 0 
                            THEN T.ORIGINALNUMBER 
                            ELSE T.CHANGEDNUMBER 
                        END) 
             from @temp T where T.FINANCIALTRANSACTIONID = FT.ID)) CHECKNUMBER
        ,CASE WHEN DPD.ID is not null or BAT.PROCESSING = 1 THEN 'In Process' ELSE CASE WHEN BAT.DELETED = 1 THEN 'Purged' ELSE BAT.STATUS END END [STATUS]
        ,C.NAME
        ,FT.TRANSACTIONAMOUNT [NETAMOUNT]
        ,'Check' PAYMENTMETHOD
        ,'' USERNAME
        ,null DATEADDED
        ,null PARENTID
        ,CASE WHEN BAT.PROCESSING = 0 THEN BAT.ID ELSE NULL END BANKACCOUNTTRANSACTIONID
        ,0 SORTORDER
    ,BAT.DELETED
    from dbo.FINANCIALTRANSACTION FT
    left outer join dbo.DISBURSEMENTPROCESSDISBURSEMENT DPD on DPD.ID = FT.ID and DPD.DISBURSEMENTPROCESSID = @DISBURSEMENTPROCESSID
  left outer join dbo.BANKACCOUNTTRANSACTION_EXT as BATE on BATE.ID = FT.ID and BATE.DISBURSEMENTPROCESSID = @DISBURSEMENTPROCESSID
    left outer join dbo.BANKACCOUNTTRANSACTION BAT on BAT.ID = BATE.ID
    inner join dbo.CONSTITUENT C on C.ID = FT.CONSTITUENTID
    where FT.ID in (select ISNULL(T1.FINANCIALTRANSACTIONID, T.FINANCIALTRANSACTIONID) 
            from @temp T
            left outer join (select Distinct ID, FINANCIALTRANSACTIONID from DisbursementHistoryInfo) T1 on T.ID = T1.ID)
        and ((BAT.DELETED = 0 or BAT.ID is null) or (BAT.DELETED = 1))
        and (@PAYMENTMETHODCODE is null or @PAYMENTMETHODCODE in (0, 1))
        and (@STATUSCODE is null or @STATUSCODE = -1 or (@STATUSCODE = -2 and BAT.STATUSCODE = 4 and BAT.DELETED = 1) or (@STATUSCODE = 0 AND (BAT.ID is null or BAT.PROCESSING = 1)) or @STATUSCODE = BAT.STATUSCODE and BAT.DELETED = 0)

    union all

    select T.ID
        ,CASE WHEN T.CHANGEDNUMBER = 0 THEN CAST(T.ORIGINALNUMBER as nvarchar) ELSE CAST(T.CHANGEDNUMBER as nvarchar) END
        ,T.ACTION
        ,''
        ,null
        ,''
        ,A.USERNAME
        ,T.DATEADDED
        ,ISNULL(T1.FINANCIALTRANSACTIONID, T.FINANCIALTRANSACTIONID) PARENTID
        ,ISNULL(BAT.ID, BAT1.ID)
        ,1 SORTORDER
    ,BAT.DELETED
    from @temp T
    left outer join (select Distinct ID, FINANCIALTRANSACTIONID, ORIGINALID from DisbursementHistoryInfo) T1 on T.ID = T1.ID
    inner join dbo.CHANGEAGENT as A on T.ADDEDBYID = A.ID
    left outer join dbo.BANKACCOUNTTRANSACTION BAT on (BAT.ID = ISNULL(ISNULL(T1.ORIGINALID, T1.FINANCIALTRANSACTIONID), T.FINANCIALTRANSACTIONID) and BAT.PROCESSING = 0) 
    left outer join dbo.BANKACCOUNTTRANSACTION BAT1 on (BAT.ID = ISNULL(T1.FINANCIALTRANSACTIONID, T.FINANCIALTRANSACTIONID) and BAT.PROCESSING = 0) and BAT.ID is null
    where (@PAYMENTMETHODCODE is null or @PAYMENTMETHODCODE in (0, 1))
        and (@STATUSCODE is null or @STATUSCODE = -1 or (@STATUSCODE = -2 and BAT.STATUSCODE = 4 and BAT.DELETED = 1) or (@STATUSCODE = 0 AND (BAT.ID is null or BAT.PROCESSING = 1)) or @STATUSCODE = BAT.STATUSCODE and BAT.DELETED = 0)
    order by CHECKNUMBER asc, DATEADDED asc, SORTORDER asc