UFN_SALESDEPOSITTEMPLATE_GETUNLINKEDCREDITPAYMENTS4

CREATE function dbo.UFN_SALESDEPOSITTEMPLATE_GETUNLINKEDCREDITPAYMENTS4
(
    @SALESDEPOSITTEMPLATEID uniqueidentifier = null
    ,@PAYMENTMETHODCODE tinyint = null
    ,@TRANSACTIONDATE date = null
)
returns table
as return
    select
        CP.ID
        ,CP.AMOUNT
        ,CAST(SO.SEQUENCEID as nvarchar(20)) as SALESORDERNUMBER
        ,CP.PAYMENTMETHODCODE
        ,COALESCE(OC.[DESCRIPTION], CC.[DESCRIPTION], CP.PAYMENTMETHOD) as PAYMENTTYPE
        ,CCD.CREDITCARDPARTIALNUMBER
        ,CP.DATEADDED as TRANSACTIONDATE
    from (
        select 
            CP1.ID
            ,CP1.AMOUNT
            ,CP1.CREDITID
            ,CP1.PAYMENTMETHODCODE
            ,CP1.PAYMENTMETHOD
            ,CP1.RECONCILIATIONID
            ,CP1.REFUNDPROCESSED
            ,case when CP1.PAYMENTMETHODCODE = 2 
                then (
                    select top 1 RPM.ID
                    from dbo.REVENUEPAYMENTMETHOD RPM
                    left join dbo.FINANCIALTRANSACTIONLINEITEM FTLI on FTLI.FINANCIALTRANSACTIONID = RPM.REVENUEID
                    where CP1.REVENUEID = RPM.REVENUEID or CP1.REVENUESPLITID = FTLI.ID
                )
                else
                    null
             end as REVENUEPAYMENTMETHODID
            ,CP1.OTHERPAYMENTMETHODCODEID
            ,CP1.DATEADDED
            ,CP1.DEPOSITED
        from dbo.CREDITPAYMENT CP1
    ) CP
    inner join
        dbo.FINANCIALTRANSACTION as FT on FT.ID = CP.CREDITID
    inner join
        dbo.CREDIT_EXT as EXT on EXT.ID = FT.ID
    inner join
        dbo.RECONCILIATION R on CP.RECONCILIATIONID = R.ID
    left join
        dbo.SALESORDER SO on SO.ID = EXT.SALESORDERID or SO.REVENUEID = FT.PARENTID
    left join
        dbo.CREDITCARDPAYMENTMETHODDETAIL CCD on CCD.ID = CP.REVENUEPAYMENTMETHODID
    left join
        dbo.CREDITTYPECODE CC on CC.ID = CCD.CREDITTYPECODEID
    left join
        dbo.OTHERPAYMENTMETHODCODE OC on OC.ID = CP.OTHERPAYMENTMETHODCODEID
    where
        R.STATUSCODE > 1 and CP.DEPOSITED = 0
        and (@PAYMENTMETHODCODE is null or CP.PAYMENTMETHODCODE = @PAYMENTMETHODCODE)
        and (CP.PAYMENTMETHODCODE != 10 or CP.OTHERPAYMENTMETHODCODEID not in (select ID from dbo.NONDEPOSITABLEPAYMENTMETHOD))
        and CP.REFUNDPROCESSED = 1
        and exists(
            select 1
            from dbo.SALESDEPOSITTEMPLATE SDT
            left join dbo.SALESDEPOSITTEMPLATEPAYMENTMETHOD SDTPM on SDTPM.SALESDEPOSITTEMPLATEID = SDT.ID
            left join dbo.SALESDEPOSITTEMPLATESALESCHANNEL SDTSC on SDTSC.SALESDEPOSITTEMPLATEID = SDT.ID
            where
                SDT.ID = @SALESDEPOSITTEMPLATEID
                and (
                    SDT.PAYMENTMETHODOPTIONCODE = 0
                    or (
                        SDTPM.PAYMENTMETHODCODE = CP.PAYMENTMETHODCODE
                        and (
                            (SDTPM.CREDITTYPECODEID is null and CCD.CREDITTYPECODEID is null)
                            or SDTPM.CREDITTYPECODEID = CCD.CREDITTYPECODEID
                        )
                        and (
                            (SDTPM.OTHERPAYMENTMETHODCODEID is null and CP.OTHERPAYMENTMETHODCODEID is null)
                            or SDTPM.OTHERPAYMENTMETHODCODEID = CP.OTHERPAYMENTMETHODCODEID
                        )
                    )
                )
                and (SDT.SALESCHANNELOPTIONCODE = 0
                    or (SDTSC.SALESMETHODTYPECODE = SO.SALESMETHODTYPECODE))
        )
        and (convert(date, CP.DATEADDED) = @TRANSACTIONDATE or @TRANSACTIONDATE is null)