UFN_TREASURYDEPOSITTEMPLATE_GETUNLINKEDPAYMENTS

CREATE function dbo.UFN_TREASURYDEPOSITTEMPLATE_GETUNLINKEDPAYMENTS
(
    @SALESDEPOSITTEMPLATEID uniqueidentifier
    ,@POSTSTATUSCODE tinyint = null -- 1 = Not posted, 2 = Posted, 3 = Do not post

    ,@TRANSACTIONDATE date = null
    ,@PAYMENTDATE date = null
)
returns table
as
    return
select distinct
    FT.ID, 
    FT.DATE as TRANSACTIONDATE, 
    PM.PAYMENTMETHOD,
    FT.TRANSACTIONAMOUNT as AMOUNT,
    FT.TYPE as TRANSACTIONTYPE,
    so.SALESMETHODTYPECODE,
    FT.TRANSACTIONCURRENCYID
from 
    dbo.FINANCIALTRANSACTION FT
    inner join dbo.FINANCIALTRANSACTIONLINEITEM LI on LI.FINANCIALTRANSACTIONID = FT.ID
    inner join dbo.REVENUEPAYMENTMETHOD PM on PM.REVENUEID = FT.ID
    inner join dbo.REVENUESPLIT_EXT RSX on RSX.ID = LI.ID
    left join dbo.BANKACCOUNTDEPOSITPAYMENT on FT.ID = BANKACCOUNTDEPOSITPAYMENT.ID
    left join dbo.SALESORDERPAYMENT SOP on SOP.PAYMENTID = FT.ID
    left join dbo.RESERVATIONSECURITYDEPOSITPAYMENT RSDP on RSDP.PAYMENTID = FT.ID
    left join dbo.SALESORDER SO on SO.ID = SOP.SALESORDERID or SO.ID = RSDP.RESERVATIONID
    left join dbo.RECONCILIATION R1 on SOP.RECONCILIATIONID = R1.ID or RSDP.RECONCILIATIONID = R1.ID
    left join dbo.OTHERPAYMENTMETHODDETAIL OD on OD.ID = PM.ID
    left join dbo.CREDITCARDPAYMENTMETHODDETAIL CCD on CCD.ID = PM.ID
where
    BANKACCOUNTDEPOSITPAYMENT.DEPOSITID is null
    AND FT.TYPECODE = 0
    and PM.PAYMENTMETHODCODE in (0,1,2,10)
    and (NOT (FT.POSTSTATUSCODE = 2 and LI.POSTSTATUSCODE = 3))
    and (@POSTSTATUSCODE is null 
        or (@POSTSTATUSCODE = FT.POSTSTATUSCODE)
        or (@POSTSTATUSCODE = LI.POSTSTATUSCODE and FT.POSTSTATUSCODE = 2))
    and (NOT exists(select ID from dbo.NONDEPOSITABLEPAYMENTMETHOD where ID = OD.OTHERPAYMENTMETHODCODEID))
    and ((SOP.DONOTRECONCILE = 1 and SO.STATUSCODE = 1)
        or (R1.STATUSCODE > 1 or R1.ID is null))
    and exists(select 1
                from dbo.SALESDEPOSITTEMPLATE SDT
                inner join dbo.SALESDEPOSITPROCESS SDP on SDT.SALESDEPOSITPROCESSID = SDP.ID
                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 SDP.PDACCOUNTSYSTEMID = FT.PDACCOUNTSYSTEMID
                    and (SDT.PAYMENTMETHODOPTIONCODE = 0
                        or ((SDTPM.PAYMENTMETHODCODE = PM.PAYMENTMETHODCODE)
                        and ((SDTPM.CREDITTYPECODEID is null and CCD.CREDITTYPECODEID is null)
                            or SDTPM.CREDITTYPECODEID = CCD.CREDITTYPECODEID)
                        and ((SDTPM.OTHERPAYMENTMETHODCODEID is null and OD.OTHERPAYMENTMETHODCODEID is null)
                            or SDTPM.OTHERPAYMENTMETHODCODEID = OD.OTHERPAYMENTMETHODCODEID)))
                    and (SDT.SALESCHANNELOPTIONCODE = 0
                        or (SDTSC.SALESMETHODTYPECODE = SO.SALESMETHODTYPECODE)
                        or (SDTSC.SALESMETHODTYPECODE = 4 and SOP.ID is null and RSX.APPLICATIONCODE not in(10, 11))
                        or (SDTSC.SALESMETHODTYPECODE = 5 and SOP.ID is null and RSX.APPLICATIONCODE = 11))
          and (SDT.CURRENCYOPTIONCODE = 0
            or (SDTC.CURRENCYID = FT.TRANSACTIONCURRENCYID)))
    and (((FT.CALCULATEDDATE <= @PAYMENTDATE and (SO.ID is null or SOP.DONOTRECONCILE = 1))
            or (CAST((SELECT max(FT2.CALCULATEDDATE) 
                    from dbo.RECONCILIATION R2
                    left join dbo.SALESORDERPAYMENT SOP2 on SOP2.RECONCILIATIONID = R2.ID
                    left join dbo.RESERVATIONSECURITYDEPOSITPAYMENT RSDP2 on RSDP2.RECONCILIATIONID = R2.ID
                    inner join FINANCIALTRANSACTION FT2 on FT2.ID = SOP2.PAYMENTID or FT2.ID =  RSDP2.PAYMENTID
                    where R2.ID = R1.ID) as DATE) <= @PAYMENTDATE))
        or @PAYMENTDATE is null)
    and (@TRANSACTIONDATE is null or FT.CALCULATEDDATE = @TRANSACTIONDATE)