UFN_RECONCILIATION_NONCASHTRANSACTION

create function dbo.UFN_RECONCILIATION_NONCASHTRANSACTION
(
    @RECONCILIATIONID uniqueidentifier
) returns table 
as
return
    select coalesce(CREDITTYPECODE.DESCRIPTION, OTHERPAYMENTMETHODCODE.DESCRIPTION, T.PAYMENTMETHOD) as PAYMENTMETHOD
        ,T.TOTALAMOUNT as AMOUNT
        ,T.EXPECTED as NUMBER
        ,T.PAYMENTMETHODCODE
        ,ISREFUND
        ,case when NONDEPOSITABLEPAYMENTMETHOD.ID is null then 1 else 0 end as DEPOSITABLE
    from dbo.UFN_RECONCILIATION_GETOTHERRECEIPTS(@RECONCILIATIONID, null, null) T
    left join dbo.CREDITTYPECODE on T.CREDITTYPECODEID = CREDITTYPECODE.ID
    left join dbo.OTHERPAYMENTMETHODCODE on T.OTHERPAYMENTMETHODCODEID = OTHERPAYMENTMETHODCODE.ID
    left join dbo.NONDEPOSITABLEPAYMENTMETHOD on NONDEPOSITABLEPAYMENTMETHOD.ID = OTHERPAYMENTMETHODCODE.ID
    where (PAYMENTMETHODCODE <> 1 and ISREFUND = 0)

    union all

    select T.PAYMENTMETHOD
        ,SUM(AMOUNT)
        ,COUNT(1)
        ,T.PAYMENTMETHODCODE
        ,1
        ,T.DEPOSITABLE
    from (
        select coalesce(CREDITTYPECODE.DESCRIPTION, OTHERPAYMENTMETHODCODE.DESCRIPTION, CP.PAYMENTMETHOD) as PAYMENTMETHOD
        ,CP.AMOUNT
        ,CP.PAYMENTMETHODCODE
        ,case when NONDEPOSITABLEPAYMENTMETHOD.ID is null then 1 else 0 end as DEPOSITABLE
        from dbo.CREDITPAYMENT CP
        left join dbo.REVENUEPAYMENTMETHOD PM on PM.REVENUEID = CP.REVENUEID
        left join dbo.CREDITCARDPAYMENTMETHODDETAIL D on D.ID = PM.ID
        left join dbo.CREDITTYPECODE on D.CREDITTYPECODEID = CREDITTYPECODE.ID
        left join dbo.OTHERPAYMENTMETHODCODE on CP.OTHERPAYMENTMETHODCODEID = OTHERPAYMENTMETHODCODE.ID
        left join dbo.NONDEPOSITABLEPAYMENTMETHOD on NONDEPOSITABLEPAYMENTMETHOD.ID = OTHERPAYMENTMETHODCODE.ID
        where CP.PAYMENTMETHODCODE != 0 and CP.RECONCILIATIONID = @RECONCILIATIONID) T
    group by T.PAYMENTMETHOD, T.PAYMENTMETHODCODE, T.DEPOSITABLE