UFN_SALESDEPOSITTEMPLATE_GETUNLINKEDCREDITPAYMENTS

            CREATE function dbo.UFN_SALESDEPOSITTEMPLATE_GETUNLINKEDCREDITPAYMENTS
            (
                @SALESDEPOSITTEMPLATEID uniqueidentifier = null,
                @ISRECONCILEDPROCESS bit = 1,
                @PAYMENTMETHODCODE tinyint = null,
                @SALESMETHODTYPECODE tinyint = null
            )
            returns table
            as return

                select
                    [CREDITPAYMENTS].[ID],
                    [CREDITPAYMENTS].[AMOUNT],
                    convert(nvarchar(20), [SALESORDER].[SEQUENCEID]) as SALESORDERNUMBER,
                    [CREDITPAYMENTS].[PAYMENTMETHODCODE],
                    case [CREDITPAYMENTS].[PAYMENTMETHODCODE] 
                        when 0 then dbo.UFN_REVENUEPAYMENTMETHOD_PAYMENTMETHODCODE_GETDESCRIPTION([CREDITPAYMENTS].[PAYMENTMETHODCODE])
                        when 1 then dbo.UFN_REVENUEPAYMENTMETHOD_PAYMENTMETHODCODE_GETDESCRIPTION([CREDITPAYMENTS].[PAYMENTMETHODCODE])
                        when 2 then dbo.UFN_CREDITTYPECODE_GETDESCRIPTION([CREDITCARDPAYMENTMETHODDETAIL].[CREDITTYPECODEID]) 
                        when 10 then dbo.UFN_OTHERPAYMENTMETHODCODE_GETDESCRIPTION([CREDITPAYMENTS].[OTHERPAYMENTMETHODCODEID])
                    end as [PAYMENTTYPE],
                    [CREDITCARDPAYMENTMETHODDETAIL].[CREDITCARDPARTIALNUMBER]
                from (
                    select 
                        [ID],
                        [AMOUNT],
                        [CREDITID],
                        [PAYMENTMETHODCODE],
                        [RECONCILIATIONID],
                        [REFUNDPROCESSED],
                        case when [PAYMENTMETHODCODE] = 2 then
                            ( 
                                select top 1 [REVENUEPAYMENTMETHOD].[ID]
                                from dbo.[REVENUEPAYMENTMETHOD]
                                left join dbo.[REVENUESPLIT]
                                    on [REVENUEPAYMENTMETHOD].[REVENUEID] = [REVENUESPLIT].[REVENUEID]
                                where
                                        [CREDITPAYMENT].[REVENUEID] = [REVENUEPAYMENTMETHOD].[REVENUEID] or
                                        [CREDITPAYMENT].[REVENUESPLITID] = [REVENUESPLIT].[ID]
                            ) 
                            else null
                        end as [REVENUEPAYMENTMETHODID],
                        [OTHERPAYMENTMETHODCODEID]
                    from dbo.[CREDITPAYMENT]
                ) as [CREDITPAYMENTS]
                inner join dbo.[CREDIT]
                    on [CREDITPAYMENTS].[CREDITID] = [CREDIT].[ID]
                left join dbo.[RECONCILIATION]
                    on [CREDITPAYMENTS].[RECONCILIATIONID] = [RECONCILIATION].[ID]
                left join dbo.[SALESORDER]
                    on [CREDIT].[SALESORDERID] = [SALESORDER].[ID]
                left join dbo.[CREDITCARDPAYMENTMETHODDETAIL] 
                    on [CREDITPAYMENTS].[REVENUEPAYMENTMETHODID] = [CREDITCARDPAYMENTMETHODDETAIL].[ID]
                where 
                    (@ISRECONCILEDPROCESS = 1 and [RECONCILIATION].[STATUSCODE] = 2) and
                    (
                        @PAYMENTMETHODCODE is null or
                        [CREDITPAYMENTS].[PAYMENTMETHODCODE] = @PAYMENTMETHODCODE
                    ) and
                    (
                        [CREDITPAYMENTS].[PAYMENTMETHODCODE] <> 10 or
                        ([CREDITPAYMENTS].[OTHERPAYMENTMETHODCODEID] not in (select ID from dbo.NONDEPOSITABLEPAYMENTMETHOD))                        
                    ) and
                    (
                        [SALESORDER].[SALESMETHODTYPECODE] = @SALESMETHODTYPECODE or
                        @SALESMETHODTYPECODE is null
                    ) and
                    [CREDITPAYMENTS].[REFUNDPROCESSED] = 1 and
                    --Payment type in sales deposit

                    exists (
                        select [SALESDEPOSITTEMPLATEPAYMENTMETHOD].[ID]
                        from dbo.[SALESDEPOSITTEMPLATEPAYMENTMETHOD]
                        where 
                            [SALESDEPOSITTEMPLATEPAYMENTMETHOD].[SALESDEPOSITTEMPLATEID] = @SALESDEPOSITTEMPLATEID and
                            [SALESDEPOSITTEMPLATEPAYMENTMETHOD].[PAYMENTMETHODCODE] = [CREDITPAYMENTS].[PAYMENTMETHODCODE] and 
                            (
                                [CREDITPAYMENTS].[PAYMENTMETHODCODE] = 0 or
                                [CREDITPAYMENTS].[PAYMENTMETHODCODE] = 1 or
                                [SALESDEPOSITTEMPLATEPAYMENTMETHOD].[CREDITTYPECODEID] = [CREDITCARDPAYMENTMETHODDETAIL].[CREDITTYPECODEID] or
                                [SALESDEPOSITTEMPLATEPAYMENTMETHOD].[OTHERPAYMENTMETHODCODEID] = [CREDITPAYMENTS].[OTHERPAYMENTMETHODCODEID]
                            )
                        )