USP_DATALIST_BANKACCOUNTDEPOSITPAYMENT

CREATE procedure dbo.USP_DATALIST_BANKACCOUNTDEPOSITPAYMENT(@DEPOSITID uniqueidentifier)
as
    set nocount on;

    select Revenue.ID,
           cast([REVENUE].[DATE] as datetime) as [PAYMENTDATE],
           REVENUEREFERENCE.REFERENCE AS [PAYMENTSOURCE],
           REVENUE.TRANSACTIONAMOUNT [AMOUNT],
           T1.APPLCATION as APPLICATION,
           REVENUE.TYPECODE as TRANSACTIONTYPECODE,
       case when REVENUEPAYMENTMETHOD.PAYMENTMETHODCODE = 2 then REVENUEPAYMENTMETHOD.PAYMENTMETHOD + ' - ' + coalesce (CREDITTYPECODE.DESCRIPTION,OTHERPAYMENTMETHODCODE.DESCRIPTION,'<Card type not specified>') 
                when REVENUEPAYMENTMETHOD.PAYMENTMETHODCODE = 10 then REVENUEPAYMENTMETHOD.PAYMENTMETHOD + ' - ' + coalesce (CREDITTYPECODE.DESCRIPTION,OTHERPAYMENTMETHODCODE.DESCRIPTION) 
        else REVENUEPAYMENTMETHOD.PAYMENTMETHOD
            end as [PAYMENTMETHOD],
            'REFERENCE' = 
            case when REVENUEPAYMENTMETHOD.PAYMENTMETHODCODE = 0 then [CASH].REFERENCENUMBER
                when REVENUEPAYMENTMETHOD.PAYMENTMETHODCODE = 1 then [CHECK].CHECKNUMBER
                when REVENUEPAYMENTMETHOD.PAYMENTMETHODCODE = 2 then [CREDIT].CREDITCARDPARTIALNUMBER
                when REVENUEPAYMENTMETHOD.PAYMENTMETHODCODE = 10 then [OTHER].REFERENCENUMBER
            end
      ,REVENUE.TRANSACTIONCURRENCYID
            ,[CREDIT].AUTHORIZATIONCODE
    from dbo.FINANCIALTRANSACTION as REVENUE
    left outer join dbo.BANKACCOUNTDEPOSITPAYMENT on REVENUE.ID = BANKACCOUNTDEPOSITPAYMENT.ID
    left outer join dbo.REVENUEPAYMENTMETHOD on REVENUEPAYMENTMETHOD.REVENUEID = REVENUE.ID
    left outer join dbo.REVENUE_EXT as REVENUEREFERENCE on REVENUEREFERENCE.ID = REVENUE.ID
    left outer join dbo.CASHPAYMENTMETHODDETAIL as [CASH] on [CASH].ID = REVENUEPAYMENTMETHOD.ID
    left outer join dbo.CHECKPAYMENTMETHODDETAIL as [CHECK] on [CHECK].ID = REVENUEPAYMENTMETHOD.ID 
    left outer join dbo.CREDITCARDPAYMENTMETHODDETAIL as [CREDIT] on [CREDIT].ID = REVENUEPAYMENTMETHOD.ID
    left outer join dbo.OTHERPAYMENTMETHODDETAIL as [OTHER] on [OTHER].ID = REVENUEPAYMENTMETHOD.ID
    left outer join dbo.OTHERPAYMENTMETHODCODE on OTHERPAYMENTMETHODCODE.ID = [OTHER].OTHERPAYMENTMETHODCODEID
    left outer join dbo.CREDITTYPECODE on CREDITTYPECODE.ID = [CREDIT].CREDITTYPECODEID
    outer apply (select isnull((select MAX(REVENUESPLIT.APPLICATION) from dbo.REVENUESPLIT where REVENUESPLIT.REVENUEID = REVENUE.ID group by REVENUESPLIT.REVENUEID  having COUNT(REVENUESPLIT.REVENUEID) = 1),'Multiple')as APPLCATION) T1     
    where BANKACCOUNTDEPOSITPAYMENT.DEPOSITID = @DEPOSITID and REVENUE.DELETEDON is null
    order by REVENUE.DATE desc