UFN_MKTCREATIVE_GETVIEW

create function dbo.[UFN_MKTCREATIVE_GETVIEW]
(
  @CREATIVEID uniqueidentifier
)
returns table
as
return (select 
          [MKTCREATIVE].[VENDORID] as [VENDORID],
          [CONSTITUENT].[NAME] as [VENDOR],
          [MKTCREATIVETYPECODE].[DESCRIPTION] as [CREATIVETYPE],
          [MKTCREATIVE].[NAME] as [NAME],
          [MKTCREATIVE].[DESCRIPTION] as [DESCRIPTION],
          dbo.[UFN_TRANSLATIONFUNCTION_SITE_GETNAME]([MKTCREATIVE].[SITEID]) as [SITE],
          [MKTCREATIVE].[COST] as [COST],
          [MKTCREATIVE].[COSTDISTRIBUTIONMETHODCODE] as [COSTDISTRIBUTIONMETHODCODE],
          [MKTCREATIVE].[COSTDISTRIBUTIONMETHOD] as [COSTDISTRIBUTIONMETHOD],
          [MKTCREATIVE].[FILEURL] as [FILEURL],
          null as [FILE], /* do not load the file here; it will be downloaded on demand */
          [MKTCREATIVE].[FILENAME] as [FILENAME],
          [MKTCREATIVE].[BASECURRENCYID] as [BASECURRENCYID],
          (select count([MKTPACKAGECREATIVE].[CREATIVEID]) from dbo.[MKTPACKAGECREATIVE] where [MKTPACKAGECREATIVE].[CREATIVEID] = [MKTCREATIVE].[ID]) as [NUMPACKAGES],
          dbo.[UFN_CURRENCY_GETDESCRIPTION]([MKTCREATIVE].[BASECURRENCYID]) as [CURRENCY]
        from dbo.[MKTCREATIVE]
        left outer join dbo.[MKTCREATIVETYPECODE] on [MKTCREATIVETYPECODE].[ID] = [MKTCREATIVE].[CREATIVETYPECODEID]
        left outer join dbo.[CONSTITUENT] on [CONSTITUENT].[ID] = [MKTCREATIVE].[VENDORID]
        where [MKTCREATIVE].[ID] = @CREATIVEID);