UFN_MKTASKLADDER_GETMONEYFIELDS

CREATE function dbo.[UFN_MKTASKLADDER_GETMONEYFIELDS]
(
  @QUERYVIEWCATALOGID uniqueidentifier
)
returns @T table 
(
  [RECORDTYPE] nvarchar(100),
  [PARENTTYPE] nvarchar(100),
  [NAME] nvarchar(128),
  [DISPLAYNAME] nvarchar(128),
  [OBJECTNAME] nvarchar(128),
  [OBJECTTYPE] nvarchar(100),
  [PRIMARYKEYFIELD] nvarchar(128),
  [OBJECTKEY] nvarchar(257),
  [CURRENCYID] uniqueidentifier
)
as
begin
  declare @RECORDTYPEID uniqueidentifier;
  declare @RECORDTYPENAME nvarchar(100);
  declare @ORGANIZATIONCURRENCYID uniqueidentifier;

  set @ORGANIZATIONCURRENCYID = dbo.[UFN_CURRENCY_GETORGANIZATIONCURRENCY]();

  select 
    @RECORDTYPENAME = (select [NAME] from dbo.[RECORDTYPE] where [ID] = [QUERYVIEWCATALOG].[RECORDTYPEID]),
    @RECORDTYPEID = [RECORDTYPEID]
  from dbo.[QUERYVIEWCATALOG] 
  where [ID] = @QUERYVIEWCATALOGID;

  with xmlnamespaces ('bb_appfx_queryview' as qvns, 'bb_appfx_commontypes' as common)
  insert into @T ([RECORDTYPE], [PARENTTYPE], [NAME], [DISPLAYNAME], [OBJECTNAME], [OBJECTTYPE], [PRIMARYKEYFIELD], [OBJECTKEY], [CURRENCYID])
    -- find any smartfields that are defined as money and match the record type

    select
      @RECORDTYPENAME as [RECORDTYPE],
      'Smart Field' as [PARENTTYPE],
      [SMARTFIELD].[VALUECOLUMNNAME] as [NAME],
      [SMARTFIELD].[NAME] as [DISPLAYNAME],
      [TABLECATALOG].[TABLENAME] as [OBJECTNAME],
      'Smart Field' as [OBJECTTYPE],
      'ID' as [PRIMARYKEYFIELD],
      ([SMARTFIELD].[VALUECOLUMNNAME] + '.' + [TABLECATALOG].[TABLENAME]) as [OBJECTKEY],
      isnull([SMARTFIELD].[CURRENCYID], @ORGANIZATIONCURRENCYID) as [CURRENCYID]
    from dbo.[SMARTFIELD]
    inner join dbo.[SMARTFIELDCATALOG] on [SMARTFIELDCATALOG].[ID] = [SMARTFIELD].[SMARTFIELDCATALOGID]
    inner join dbo.[TABLECATALOG] on [TABLECATALOG].[ID] = [SMARTFIELD].[TABLECATALOGID]
    where [SMARTFIELDCATALOG].[DATATYPECODE] = 3 -- Money = 3

    and [SMARTFIELDCATALOG].[RECORDTYPEID] = @RECORDTYPEID

    union

    -- find any queryview fields that are defined as money and match the record type

    select
      @RECORDTYPENAME as [RECORDTYPE],
      [QUERYVIEWCATALOG].[DISPLAYNAME] as [PARENTTYPE],
      c.[name] as [NAME],
      (select top(1) isnull(T.c.value('(@Caption)[1]','nvarchar(255)'), T.c.value('(@Name)[1]','nvarchar(255)')) 
       from [QUERYVIEWCATALOG].[OUTPUTDEFINITIONXML].nodes('QueryViewOutput/qvns:OutputFields/qvns:OutputField') T(c)
       where T.c.value('(@Name)[1]','nvarchar(255)') = c.[name]
       and isnull(T.c.value('(@IsHidden)[1]','bit'), '0') in ('0', 'false')) as [DISPLAYNAME],
      [QUERYVIEWCATALOG].[OBJECTNAME] as [OBJECTNAME],
      [QUERYVIEWCATALOG].[OBJECTTYPENAME] as [OBJECTTYPENAME],
      [QUERYVIEWCATALOG].[PRIMARYKEYFIELD] as [PRIMARYKEYFIELD],
      (c.[name] + '.' + [QUERYVIEWCATALOG].[OBJECTNAME]) as [OBJECTNAME],
      @ORGANIZATIONCURRENCYID as [CURRENCYID]
    from dbo.[QUERYVIEWCATALOG]
    inner join sys.objects o on o.[name] = [QUERYVIEWCATALOG].[OBJECTNAME] 
    inner join sys.columns c on c.[object_id] = o.[object_id]
    where [QUERYVIEWCATALOG].[RECORDTYPEID] = @RECORDTYPEID
    and [QUERYVIEWCATALOG].[PRIMARYKEYFIELD] is not null
    and dbo.[UFN_INSTALLEDPRODUCTS_OPTIONALPRODUCTSINSTALLED]([QUERYVIEWCATALOG].[QUERYVIEWSPEC].query('qvns:QueryViewSpec/common:InstalledProductList')) = 1
    and dbo.[UFN_INSTALLEDPRODUCTS_OPTIONALPRODUCTSINSTALLED](
        (select top(1) T.c.query('./common:InstalledProductList')
         from [QUERYVIEWCATALOG].[OUTPUTDEFINITIONXML].nodes('QueryViewOutput/qvns:OutputFields/qvns:OutputField') T(c)
         where T.c.value('(@Name)[1]', 'nvarchar(255)') = c.[name] and isnull(T.c.value('(@IsHidden)[1]', 'bit'), '0') in ('0', 'false'))) = 1
    and (select top(1) T.c.value('(@DataType)[1]', 'nvarchar(255)') 
         from [QUERYVIEWCATALOG].[OUTPUTDEFINITIONXML].nodes('QueryViewOutput/qvns:OutputFields/qvns:OutputField') T(c)
         where T.c.value('(@Name)[1]', 'nvarchar(255)') = c.[name] and isnull(T.c.value('(@IsHidden)[1]', 'bit'), '0') in ('0', 'false')) = 'Money'

    order by [PARENTTYPE];

  return;
end