UFN_SALESDEPOSITTEMPLATE_GETUNLINKEDPAYMENTS

            CREATE function dbo.UFN_SALESDEPOSITTEMPLATE_GETUNLINKEDPAYMENTS
            (
                @SALESDEPOSITTEMPLATEID uniqueidentifier,
                @REQUIRERECONCILED bit = 1, --6/6/11 Now directly related to sales order payments' DoNotReconcile flag

                @SALESMETHODTYPECODE tinyint = null
            ) 
            returns table
            as 
                return 
                    select 
                        [REVENUE].[ID],
                        [REVENUE].[DATE] as [TRANSACTIONDATE],
                        [REVENUEPAYMENTMETHOD].[PAYMENTMETHOD],
                        [REVENUE].[AMOUNT],
                        [REVENUE].[TRANSACTIONTYPE]
                    from dbo.[REVENUE] with (nolock)
                    inner join (select ID, SALESORDERID, PAYMENTID, RECONCILIATIONID, AMOUNT, DONOTRECONCILE from dbo.SALESORDERPAYMENT with (nolock)
                                      union all 
                                      select ID, RESERVATIONID as SALESORDERID, PAYMENTID, RECONCILIATIONID, AMOUNT, 0 as DONOTRECONCILE from dbo.RESERVATIONSECURITYDEPOSITPAYMENT with (nolock)
                                ) as SALESORDERPAYMENT
                        on [REVENUE].[ID] = [SALESORDERPAYMENT].[PAYMENTID]
                    inner join dbo.[SALESORDER] with (nolock)
                        on [SALESORDERPAYMENT].[SALESORDERID] = [SALESORDER].[ID]
                    inner join dbo.[REVENUEPAYMENTMETHOD] with (nolock)
                        on [REVENUE].[ID] = [REVENUEPAYMENTMETHOD].[REVENUEID]
                    left join dbo.[CREDITCARDPAYMENTMETHODDETAIL]  with (nolock)
                        on [REVENUEPAYMENTMETHOD].[ID] = [CREDITCARDPAYMENTMETHODDETAIL].[ID]
                    left join dbo.[OTHERPAYMENTMETHODDETAIL] with (nolock)
                        on [REVENUEPAYMENTMETHOD].[ID] = [OTHERPAYMENTMETHODDETAIL].[ID]
                    where
                        --Unlinked (Not linked) to a deposit

                        [REVENUE].[ID] not in (
                            select [BANKACCOUNTDEPOSITPAYMENT].[ID] 
                            from dbo.[BANKACCOUNTDEPOSITPAYMENT] with (nolock)
                        ) and
                        --Reconciled/Unreconciled

                        (
                            (@REQUIRERECONCILED = 0 and [SALESORDERPAYMENT].[DONOTRECONCILE] = 1 and [SALESORDER].[STATUSCODE] = 1) or
                            (
                                [SALESORDERPAYMENT].[DONOTRECONCILE] = 0 and
                                [SALESORDERPAYMENT].[RECONCILIATIONID] in (
                                    select [RECONCILIATION].[ID]
                                    from dbo.[RECONCILIATION]
                                    where [RECONCILIATION].[STATUSCODE] = 2
                                )
                            )
                        ) and
                        --Payment type

                        (
                            [REVENUEPAYMENTMETHOD].[PAYMENTMETHODCODE] in (
                                select [SALESDEPOSITTEMPLATEPAYMENTMETHOD].[PAYMENTMETHODCODE]
                                from dbo.[SALESDEPOSITTEMPLATEPAYMENTMETHOD]
                                where [SALESDEPOSITTEMPLATEPAYMENTMETHOD].[SALESDEPOSITTEMPLATEID] = @SALESDEPOSITTEMPLATEID
                            ) and 
                            (
                                [REVENUEPAYMENTMETHOD].[PAYMENTMETHODCODE] not in (2, 10) or
                                (
                                    [REVENUEPAYMENTMETHOD].[PAYMENTMETHODCODE] = 2 and
                                    [CREDITCARDPAYMENTMETHODDETAIL].[CREDITTYPECODEID] in (
                                        select [SALESDEPOSITTEMPLATEPAYMENTMETHOD].[CREDITTYPECODEID]
                                        from dbo.[SALESDEPOSITTEMPLATEPAYMENTMETHOD]
                                        where [SALESDEPOSITTEMPLATEPAYMENTMETHOD].[SALESDEPOSITTEMPLATEID] = @SALESDEPOSITTEMPLATEID) 
                                ) or
                                (
                                    [REVENUEPAYMENTMETHOD].[PAYMENTMETHODCODE] = 10 and
                                    [OTHERPAYMENTMETHODDETAIL].[OTHERPAYMENTMETHODCODEID] in (
                                        select [SALESDEPOSITTEMPLATEPAYMENTMETHOD].[OTHERPAYMENTMETHODCODEID]
                                        from dbo.[SALESDEPOSITTEMPLATEPAYMENTMETHOD]
                                        where [SALESDEPOSITTEMPLATEPAYMENTMETHOD].[SALESDEPOSITTEMPLATEID] = @SALESDEPOSITTEMPLATEID) and 
                                    [OTHERPAYMENTMETHODDETAIL].[OTHERPAYMENTMETHODCODEID] not in (select ID from dbo.NONDEPOSITABLEPAYMENTMETHOD)
                                )
                            )
                        ) and
                        --Sales method

                        ([SALESORDER].[SALESMETHODTYPECODE] = @SALESMETHODTYPECODE or @SALESMETHODTYPECODE is null)