UFN_SALESDEPOSITTEMPLATE_GETUNLINKEDPAYMENTS_4

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

    ,@TRANSACTIONDATE date = null
)
returns table
as
    return
        select
            FT.ID
            ,FT.[DATE] as TRANSACTIONDATE
            ,PM.PAYMENTMETHOD
            ,FT.TRANSACTIONAMOUNT as AMOUNT
            ,FT.[TYPE] as TRANSACTIONTYPE
        from dbo.FINANCIALTRANSACTION FT with (nolock)
        inner join (
            select SOP.ID, SOP.SALESORDERID, SOP.PAYMENTID, SOP.RECONCILIATIONID, SOP.AMOUNT, SOP.DONOTRECONCILE, SOP.DEPOSITED
            from dbo.SALESORDERPAYMENT SOP with (nolock)

            union all

            select RSD.ID, RSD.RESERVATIONID, RSD.PAYMENTID, RSD.RECONCILIATIONID, RSD.AMOUNT, 0, RSD.DEPOSITED
            from dbo.RESERVATIONSECURITYDEPOSITPAYMENT RSD with (nolock)) P on P.PAYMENTID = FT.ID
        inner join dbo.SALESORDER SO with (nolock) on SO.ID = P.SALESORDERID
        inner join dbo.REVENUEPAYMENTMETHOD PM with (nolock) on PM.REVENUEID = FT.ID
        left join dbo.CREDITCARDPAYMENTMETHODDETAIL CCD with (nolock) on CCD.ID = PM.ID
        left join dbo.OTHERPAYMENTMETHODDETAIL OD with (nolock) on OD.ID = PM.ID
        where P.DEPOSITED = 0
            and (PM.PAYMENTMETHODCODE != 10 or OD.OTHERPAYMENTMETHODCODEID not in (select ID from dbo.NONDEPOSITABLEPAYMENTMETHOD))
            and FT.ID not in (select BADP.ID from dbo.BANKACCOUNTDEPOSITPAYMENT BADP with (nolock))
            and ((P.DONOTRECONCILE = 1 and SO.STATUSCODE = 1)
                or (P.DONOTRECONCILE = 0 and P.RECONCILIATIONID in (select R.ID from dbo.RECONCILIATION R where R.STATUSCODE > 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 = 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)))
            and (@POSTSTATUSCODE is null or FT.POSTSTATUSCODE = (case when @POSTSTATUSCODE = 2 then 3 else @POSTSTATUSCODE end))
            and (@TRANSACTIONDATE is null or cast(FT.[DATE] as date) = @TRANSACTIONDATE)