UFN_CURRENCYEXCHANGERATEDOWNLOADPROCESS_GETDEFAULTRATES

create function dbo.[UFN_CURRENCYEXCHANGERATEDOWNLOADPROCESS_GETDEFAULTRATES]()
returns xml
as
  begin
    declare @RATES table ([FROMCURRENCYID] uniqueidentifier not null, [TOCURRENCYID] uniqueidentifier not null);

    -- this gets all of the rates the client _should_ be populating

    -- see http://meebee/BBSites/Products/InfinityDevelopment/Wiki/Wiki%20Pages/Setup%20and%20use%20of%20corporate%20and%20daily%20exchange%20rates.aspx


    with [ORGANIZATIONCURRENCY] as (
      select dbo.[UFN_CURRENCY_GETORGANIZATIONCURRENCY]() as [ORGANIZATIONCURRENCYID]
    ),
    [BASETOORGANIZATIONPAIRS] as (
      select 
        [FROM].[BASECURRENCYID] as [FROMCURRENCYID],
        [TO].[ORGANIZATIONCURRENCYID] as [TOCURRENCYID]
      from dbo.[CURRENCYSET] as [FROM]
      cross join [ORGANIZATIONCURRENCY] as [TO]
    ),
    [TRANSACTIONTOORGANIZATIONPAIRS] as (
      select 
        [FROM].[CURRENCYID] as [FROMCURRENCYID],
        [TO].[ORGANIZATIONCURRENCYID] as [TOCURRENCYID]
      from dbo.[CURRENCYSETTRANSACTIONCURRENCY] as [FROM]
      cross join [ORGANIZATIONCURRENCY] as [TO]
    ),
    [BASETOBASEPAIRS] as (
      select
        [FROM].[BASECURRENCYID] as [FROMCURRENCYID],
        [TO].[BASECURRENCYID] as [TOCURRENCYID]
      from dbo.[CURRENCYSET] as [FROM]
      cross join dbo.[CURRENCYSET] as [TO]
    ),
    [TRANSACTIONTOBASEPAIRS] as (
      select
        [FROM].[CURRENCYID] as [FROMCURRENCYID],
        [TO].[BASECURRENCYID] as [TOCURRENCYID]
      from dbo.[CURRENCYSETTRANSACTIONCURRENCY] as [FROM]
      cross join dbo.[CURRENCYSET] as [TO]
    ),
    [BASETOTRANSACTIONPAIRS] as (
      select
        [FROM].[BASECURRENCYID] as [FROMCURRENCYID],
        [TO].[CURRENCYID] as [TOCURRENCYID]
      from dbo.[CURRENCYSET] as [FROM]
      cross join dbo.[CURRENCYSETTRANSACTIONCURRENCY] as [TO]
    ),
    [TRANSACTIONTOTRANSACTIONPAIRS] as (
      select
        [FROM].[CURRENCYID] as [FROMCURRENCYID],
        [TO].[CURRENCYID] as [TOCURRENCYID]
      from dbo.[CURRENCYSETTRANSACTIONCURRENCY] as [FROM]
      cross join dbo.[CURRENCYSETTRANSACTIONCURRENCY] as [TO]
    ),
    [RATES] as (
      select
        [FROMCURRENCYID],
        [TOCURRENCYID]
      from [BASETOORGANIZATIONPAIRS]
      where [FROMCURRENCYID] <> [TOCURRENCYID]
      union
      select
        [FROMCURRENCYID],
        [TOCURRENCYID]
      from [TRANSACTIONTOORGANIZATIONPAIRS]
      where [FROMCURRENCYID] <> [TOCURRENCYID]
      union
      select
        [FROMCURRENCYID],
        [TOCURRENCYID]
      from [BASETOBASEPAIRS]
      where [FROMCURRENCYID] <> [TOCURRENCYID]
      union
      select
        [FROMCURRENCYID],
        [TOCURRENCYID]
      from [TRANSACTIONTOBASEPAIRS]
      where [FROMCURRENCYID] <> [TOCURRENCYID]
      union
      select
        [FROMCURRENCYID],
        [TOCURRENCYID]
      from [BASETOTRANSACTIONPAIRS]
      where [FROMCURRENCYID] <> [TOCURRENCYID]
      union
      select
        [FROMCURRENCYID],
        [TOCURRENCYID]
      from [TRANSACTIONTOTRANSACTIONPAIRS]
      where [FROMCURRENCYID] <> [TOCURRENCYID]
    )
    insert into @RATES
    select
      [RATES].[FROMCURRENCYID],
      [RATES].[TOCURRENCYID]
    from [RATES]
    inner join dbo.[CURRENCY] as [FROM] on [FROM].[ID] = [FROMCURRENCYID]
    inner join dbo.[CURRENCY] as [TO] on [TO].[ID] = [TOCURRENCYID]
    order by [FROM].[NAME], [TO].[NAME]

    return (select [FROMCURRENCYID], [TOCURRENCYID] from @RATES for xml raw('ITEM'), type, elements, root('RATES'), binary base64);
  end