UFN_SALESDEPOSITTEMPLATE_PAYMENTMETHODS_TOITEMLISTXML

CREATE function dbo.UFN_SALESDEPOSITTEMPLATE_PAYMENTMETHODS_TOITEMLISTXML
(
    @SALESDEPOSITTEMPLATEID uniqueidentifier = null
)
returns xml
as begin
    declare @XML xml

        --The payment types temp table selectable in form

    declare @PAYMENTTYPES table (
        [PAYMENTMETHOD] nvarchar(100),
        [PAYMENTMETHODCODE] tinyint,
        [CREDITTYPECODEID] uniqueidentifier,
        [OTHERPAYMENTMETHODCODEID] uniqueidentifier
    )

    insert into @PAYMENTTYPES
    select
        dbo.UFN_REVENUEPAYMENTMETHOD_PAYMENTMETHODCODE_GETDESCRIPTION(0) as [PAYMENTMETHOD],
        0 as [PAYMENTMETHODCODE],
        null as [CREDITTYPECODEID],
        null as [OTHERPAYMENTMETHODCODEID]

    union all
    select
        dbo.UFN_REVENUEPAYMENTMETHOD_PAYMENTMETHODCODE_GETDESCRIPTION(1) as [PAYMENTMETHOD],
        1 as [PAYMENTMETHODCODE],
        null as [CREDITTYPECODEID],
        null as [OTHERPAYMENTMETHODCODEID]

    union all
    select
        dbo.UFN_REVENUEPAYMENTMETHOD_PAYMENTMETHODCODE_GETDESCRIPTION(2) as [PAYMENTMETHOD],
        2 as [PAYMENTMETHODCODE],
        null as [CREDITTYPECODEID],
        null as [OTHERPAYMENTMETHODCODEID]

    union all
    select 
        [CREDITTYPECODE].[DESCRIPTION] as [PAYMENTMETHOD],
        2 as [PAYMENTMETHODCODE],
        [CREDITTYPECODE].[ID] as [CREDITTYPECODEID],
        null as [OTHERPAYMENTMETHODCODEID]        
    from dbo.[CREDITTYPECODE]
    where [ACTIVE] = 1

    union all
    select 
        [OTHERPAYMENTMETHODCODE].[DESCRIPTION] as [PAYMENTMETHOD],
        10 as [PAYMENTMETHODCODE],
        null as [CREDITTYPECODEID],
        [OTHERPAYMENTMETHODCODE].[ID] as [OTHERPAYMENTMETHODCODEID]
    from dbo.[OTHERPAYMENTMETHODCODE]
    where [ACTIVE] = 1
        and [ID] not in (select ID from dbo.NONDEPOSITABLEPAYMENTMETHOD);

    set @XML = (
        select
            PM.ID
            ,case when @SALESDEPOSITTEMPLATEID is null then 1 else case when PM.ID is null then 0 else 1 end end as [INCLUDE]
            ,P.PAYMENTMETHODCODE
            ,P.PAYMENTMETHOD
            ,P.CREDITTYPECODEID
            ,P.OTHERPAYMENTMETHODCODEID
        from @PAYMENTTYPES P
        left join dbo.SALESDEPOSITTEMPLATEPAYMENTMETHOD PM 
            on @SALESDEPOSITTEMPLATEID is not null 
                and P.PAYMENTMETHODCODE = PM.PAYMENTMETHODCODE
                and (P.CREDITTYPECODEID = PM.CREDITTYPECODEID or (P.CREDITTYPECODEID is null and PM.CREDITTYPECODEID is null))
                and (P.OTHERPAYMENTMETHODCODEID = PM.OTHERPAYMENTMETHODCODEID or (P.OTHERPAYMENTMETHODCODEID is null or PM.OTHERPAYMENTMETHODCODEID is null))
                and PM.SALESDEPOSITTEMPLATEID = @SALESDEPOSITTEMPLATEID
        order by P.PAYMENTMETHODCODE, P.PAYMENTMETHOD
        for xml raw('ITEM'), type, elements, root('PAYMENTTYPE'), BINARY BASE64
    )

    return @XML

end