UFN_SALESDEPOSITTEMPLATE_SALESCHANNELS_TOITEMLISTXML

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

  declare @ISPROGRAMMING bit = 0;
    declare @SHOWONLINESALES bit = 0;
  if exists (select 1 from dbo.INSTALLEDPRODUCTLIST where ID = 'bb1c17bc-9e0b-4683-b490-ee40d511fa05')
    set @ISPROGRAMMING = 1;
    else if exists(select 1 from dbo.INSTALLEDPRODUCTLIST where ID = 'F238E8FE-06AE-4FDC-BEAF-FDF6637E1982')
        and exists(select 1 from dbo.INSTALLEDPRODUCTLIST where ID = 'BA7F0054-A013-43A9-B61E-F7B3E4A791C5')
        and exists(select 1 from dbo.INSTALLEDPRODUCTLIST where ID = 'DD4E6FC7-4A9F-4178-9500-6520A78BBD54')
        and exists(select 1 from dbo.INSTALLEDPRODUCTLIST where ID = '919143DE-C062-4413-9AE2-6DCE56413A09')
        and exists(select 1 from dbo.INSTALLEDPRODUCTLIST where ID = '0D85A247-CE4F-4853-9BE7-BE8CF77601ED')
        and exists(select 1 from dbo.INSTALLEDPRODUCTLIST where ID = 'FC0C7D3B-F6DF-4853-8BA5-5B1355AA67F9')
        and exists(select 1 from dbo.INSTALLEDPRODUCTLIST where ID = '9B715093-7A95-4080-89B6-E096FAF4798A')
        and exists(select 1 from dbo.INSTALLEDPRODUCTLIST where ID = '25A6B0A9-E26A-4047-9BC7-DE8B7020C155')
        and exists(select 1 from dbo.INSTALLEDPRODUCTLIST where ID = '3433B876-A882-4819-B1EA-DD6E78718B0F')
        set @SHOWONLINESALES = 1

    declare @SALESCHANNELS table (
        [SALESMETHODTYPECODE] tinyint,
        [SALESMETHOD] nvarchar(100)
    )

  if @ISPROGRAMMING = 1
      insert into @SALESCHANNELS (SALESMETHODTYPECODE, SALESMETHOD)
      values
      (0, 'Daily Sales'),
      (1, 'Advance Sales'),
      (2, 'Online Sales'),
      (3, 'Group Sales')
    else if @SHOWONLINESALES = 1
        insert into @SALESCHANNELS (SALESMETHODTYPECODE, SALESMETHOD)
      values
      (2, 'Online Sales')

  insert into @SALESCHANNELS (SALESMETHODTYPECODE, SALESMETHOD)
    values
  (4, 'Back Office Revenue'),
  (5, 'Treasury Miscellaneous Payments')

    set @XML = (
        select
            SC.ID
            ,case when @SALESDEPOSITTEMPLATEID is null then 1 else case when SC.ID is null then 0 else 1 end end as [INCLUDE]
            ,P.SALESMETHODTYPECODE
            ,P.SALESMETHOD
        from @SALESCHANNELS P
        left join dbo.SALESDEPOSITTEMPLATESALESCHANNEL SC 
            on @SALESDEPOSITTEMPLATEID is not null 
                and P.SALESMETHODTYPECODE = SC.SALESMETHODTYPECODE
                and SC.SALESDEPOSITTEMPLATEID = @SALESDEPOSITTEMPLATEID
        order by P.SALESMETHOD
        for xml raw('ITEM'), type, elements, root('SALESCHANNEL'), BINARY BASE64
    )

    return @XML

end