UFN_TREASURYDEPOSITTEMPLATE_GETUNLINKEDCREDITPAYMENTS

CREATE function dbo.UFN_TREASURYDEPOSITTEMPLATE_GETUNLINKEDCREDITPAYMENTS
(
    @SALESDEPOSITTEMPLATEID uniqueidentifier = null
  ,@POSTSTATUSCODE tinyint = null
    ,@PAYMENTMETHODCODE tinyint = null
    ,@TRANSACTIONDATE date = null
  ,@PAYMENTDATE 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
    ,FT.TRANSACTIONCURRENCYID
    from dbo.CREDITPAYMENT CP
    inner join dbo.FINANCIALTRANSACTION FT on CP.CREDITID = FT.ID
    inner join dbo.CREDIT_EXT CX on CX.ID = FT.ID
    left join dbo.RECONCILIATION R on CP.RECONCILIATIONID = R.ID
    left join dbo.BANKACCOUNTDEPOSITCREDITPAYMENT BADCP on BADCP.ID = CP.ID
    left join dbo.SALESORDER SO on SO.ID = CX.SALESORDERID
    left join dbo.CREDITCARDPAYMENTMETHODDETAIL CCD 
        on CCD.ID = (select top 1 RPM.ID
                    from dbo.REVENUEPAYMENTMETHOD RPM
                    left join dbo.FINANCIALTRANSACTIONLINEITEM FTLI on FTLI.FINANCIALTRANSACTIONID = RPM.REVENUEID
                    where CP.REVENUEID = RPM.REVENUEID or CP.REVENUESPLITID = FTLI.ID) and cp.PAYMENTMETHODCODE = 2
    left join dbo.CREDITTYPECODE CC on CC.ID = CCD.CREDITTYPECODEID
    left join dbo.OTHERPAYMENTMETHODCODE OC on OC.ID = CP.OTHERPAYMENTMETHODCODEID
    where (R.STATUSCODE > 1 or SO.ID is null)
    and BADCP.DEPOSITID is null
    and (@POSTSTATUSCODE is null or FT.POSTSTATUSCODE = (case when @POSTSTATUSCODE = 2 then 3 else @POSTSTATUSCODE end))
        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
      left join dbo.SALESDEPOSITTEMPLATECURRENCY SDTC on SDTC.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)
          or (SDTSC.SALESMETHODTYPECODE = 4 and SO.ID is null))
        and (SDT.CURRENCYOPTIONCODE = 0
          or (SDTC.CURRENCYID = FT.TRANSACTIONCURRENCYID)))
        and (convert(Date, CP.DATEADDED) = @TRANSACTIONDATE or @TRANSACTIONDATE is null)
        and (((FT.CALCULATEDDATE <= @PAYMENTDATE and SO.ID is null)
            or (CAST((SELECT max(FT2.CALCULATEDDATE) 
                    from dbo.CREDITPAYMENT CP2
                    inner join dbo.FINANCIALTRANSACTION FT2 on FT2.ID = CP2.CREDITID
                    inner join RECONCILIATION R2 on CP2.RECONCILIATIONID = R2.ID
                    where R2.ID = R.ID) as DATE) <= @PAYMENTDATE))
        or @PAYMENTDATE is null)
    and exists (select 1 from dbo.SALESDEPOSITPROCESS SDP
                inner join dbo.SALESDEPOSITTEMPLATE SDT on SDT.SALESDEPOSITPROCESSID = SDP.ID
                where SDP.PDACCOUNTSYSTEMID = FT.PDACCOUNTSYSTEMID
                and SDT.ID = @SALESDEPOSITTEMPLATEID)