UFN_SALESDEPOSITPROCESS_TEMPLATES_GETUNMAPPEDTRANSACTIONS_FORACCOUNTSYSTEM

CREATE function dbo.UFN_SALESDEPOSITPROCESS_TEMPLATES_GETUNMAPPEDTRANSACTIONS_FORACCOUNTSYSTEM
(
    @SALESDEPOSITPROCESSID uniqueidentifier
)
RETURNS TABLE AS
RETURN (
    select distinct
        PAYMENTS.PAYMENTMETHODCODE 
        ,PAYMENTS.PAYMENTMETHOD
        ,PAYMENTS.SALESMETHODCODE
        ,PAYMENTS.CREDITTYPECODEID
        ,PAYMENTS.OTHERPAYMENTMETHODCODEID
    ,PAYMENTS.TRANSACTIONCURRENCYID
    from (
        select distinct
            T.PAYMENTMETHODCODE
            ,T.PAYMENTMETHOD
            ,T.SALESMETHODCODE
            ,T.CREDITTYPECODEID
            ,T.OTHERPAYMENTMETHODCODEID
      ,T.TRANSACTIONCURRENCYID
        from dbo.UFN_SALESDEPOSITPROCESS_GETACCOUNTSYSTEMTRANSACTIONS(@SALESDEPOSITPROCESSID) T

        union all

        select 
            O.PAYMENTMETHODCODE
            ,O.PAYMENTMETHOD
            ,O.SALESMETHODCODE
            ,null as CREDITTYPECODEID
            ,null as OTHERPAYMENTMETHODCODEID
      ,(select top 1 ID from dbo.CURRENCY where ISORGANIZATIONCURRENCY = 1) as TRANSACTIONCURRENCYID
        from dbo.UFN_SALESDEPOSITPROCESS_TEMPLATES_GETOVERSHORTS(@SALESDEPOSITPROCESSID) O
        ) PAYMENTS
    where not exists(select 1
        from dbo.SALESDEPOSITTEMPLATE T
        left join dbo.SALESDEPOSITTEMPLATEPAYMENTMETHOD PM on T.ID = PM.SALESDEPOSITTEMPLATEID
        left join dbo.SALESDEPOSITTEMPLATESALESCHANNEL C on T.ID = C.SALESDEPOSITTEMPLATEID
        left join dbo.SALESDEPOSITTEMPLATECURRENCY CU on T.ID = cu.SALESDEPOSITTEMPLATEID
        where T.SALESDEPOSITPROCESSID = @SALESDEPOSITPROCESSID
            and (T.SALESCHANNELOPTIONCODE = 0 or C.SALESMETHODTYPECODE = PAYMENTS.SALESMETHODCODE)
            and (T.CURRENCYOPTIONCODE = 0 or CU.CURRENCYID = PAYMENTS.TRANSACTIONCURRENCYID)
            and (T.PAYMENTMETHODOPTIONCODE = 0 or 
                (PM.PAYMENTMETHODCODE = PAYMENTS.PAYMENTMETHODCODE
                    and ((PM.CREDITTYPECODEID is null and PAYMENTS.CREDITTYPECODEID is null)
                        or (PM.CREDITTYPECODEID = PAYMENTS.CREDITTYPECODEID))
                    and ((PM.OTHERPAYMENTMETHODCODEID is null and PAYMENTS.OTHERPAYMENTMETHODCODEID is null)
                        or (PM.OTHERPAYMENTMETHODCODEID = PAYMENTS.OTHERPAYMENTMETHODCODEID)))))

)