USP_DATALIST_MKTPACKAGECREATIVE

CREATE procedure dbo.[USP_DATALIST_MKTPACKAGECREATIVE]
(
  @PACKAGEID uniqueidentifier
)
as
  set nocount on;

  select
    [MKTPACKAGECREATIVE].[ID],
    [MKTCREATIVE].[ID] as [CREATIVEID],
    [MKTCREATIVE].[NAME],
    [MKTCREATIVETYPECODE].[DESCRIPTION] as [CREATIVETYPE],
    [CONSTITUENT].[NAME] as [VENDOR],
    [MKTCREATIVE].[COST],
    [MKTCREATIVE].[COSTDISTRIBUTIONMETHOD],
    case when ([MKTCREATIVE].[FILENAME] is null or len([MKTCREATIVE].[FILENAME]) = 0) then [MKTCREATIVE].[FILEURL] else [MKTCREATIVE].[FILENAME] end as [DOCUMENT],
    convert(bit, case when ([MKTCREATIVE].[FILEURL] is not null and len([MKTCREATIVE].[FILEURL]) > 0) or ([MKTCREATIVE].[FILENAME] is not null and len([MKTCREATIVE].[FILENAME]) > 0) then 1 else 0 end) as [HASDOCUMENT],
    convert(bit, case when [MKTCREATIVE].[FILENAME] is not null and len([MKTCREATIVE].[FILENAME]) > 0 then 1 else 0 end) as [ISATTACHMENT],
    convert(bit, 1) as [CANBEDELETED],
    [MKTCREATIVE].[BASECURRENCYID],
    [SITE].[NAME] as [SITE],
    [MKTCREATIVE].[DESCRIPTION],
    case when len(isnull([MKTCREATIVE].[FILENAME], '')) > 0 then convert(uniqueidentifier, '6e5ba4a0-8bdf-4124-86b6-bd5b78c2a04b') else null end as [VIEWDATAFORMID],
    case when len(isnull([MKTCREATIVE].[FILENAME], '')) > 0 then 'FILE' else null end as [FILEFIELDID]
  from dbo.[MKTCREATIVE]
  inner join dbo.[MKTPACKAGECREATIVE] on [MKTPACKAGECREATIVE].[CREATIVEID] = [MKTCREATIVE].[ID]
  left outer join dbo.[SITE] on [SITE].[ID] = [MKTCREATIVE].[SITEID]
  left outer join dbo.[MKTCREATIVETYPECODE] on [MKTCREATIVETYPECODE].[ID] = [MKTCREATIVE].[CREATIVETYPECODEID]
  left outer join dbo.[CONSTITUENT] on [CONSTITUENT].[ID] = [MKTCREATIVE].[VENDORID]
  where [MKTPACKAGECREATIVE].[PACKAGEID] = @PACKAGEID
  union all
  select
    [MKTPACKAGE].[ID],
    [MKTCREATIVE].[ID] as [CREATIVEID],
    [MKTCREATIVE].[NAME],
    [MKTCREATIVETYPECODE].[DESCRIPTION] as [CREATIVETYPE],
    [CONSTITUENT].[NAME] as [VENDOR],
    [MKTCREATIVE].[COST],
    [MKTCREATIVE].[COSTDISTRIBUTIONMETHOD],
    case when ([MKTCREATIVE].[FILENAME] is null or len([MKTCREATIVE].[FILENAME]) = 0) then [MKTCREATIVE].[FILEURL] else [MKTCREATIVE].[FILENAME] end as [DOCUMENT],
    convert(bit, case when ([MKTCREATIVE].[FILEURL] is not null and len([MKTCREATIVE].[FILEURL]) > 0) or ([MKTCREATIVE].[FILENAME] is not null and len([MKTCREATIVE].[FILENAME]) > 0) then 1 else 0 end) as [HASDOCUMENT],
    convert(bit, case when [MKTCREATIVE].[FILENAME] is not null and len([MKTCREATIVE].[FILENAME]) > 0 then 1 else 0 end) as [ISATTACHMENT],
    convert(bit, 0) as [CANBEDELETED],
    [MKTCREATIVE].[BASECURRENCYID],
    [SITE].[NAME] as [SITE],
    [MKTCREATIVE].[DESCRIPTION],
    case when len(isnull([MKTCREATIVE].[FILENAME], '')) > 0 then convert(uniqueidentifier, '26606542-4FC1-4E01-9431-6B7510D17005') else null end as [VIEWDATAFORMID],
    case when len(isnull([MKTCREATIVE].[FILENAME], '')) > 0 then 'FILE' else null end as [FILEFIELDID]
  from dbo.[MKTCREATIVE]
  inner join dbo.[MKTPACKAGE] on [MKTPACKAGE].[CREATIVEID] = [MKTCREATIVE].[ID]
  left outer join dbo.[SITE] on [SITE].[ID] = [MKTCREATIVE].[SITEID]
  left outer join dbo.[MKTCREATIVETYPECODE] on [MKTCREATIVETYPECODE].[ID] = [MKTCREATIVE].[CREATIVETYPECODEID]
  left outer join dbo.[CONSTITUENT] on [CONSTITUENT].[ID] = [MKTCREATIVE].[VENDORID]
  where [MKTPACKAGE].[ID] = @PACKAGEID
  order by 3;

  return 0;