USP_DATALIST_MKTPACKAGEDOCUMENT

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

  select
    [MKTPACKAGEDOCUMENT].[ID],
    [MKTDOCUMENT].[ID] as [DOCUMENTID],
    [MKTDOCUMENT].[NAME],
    [MKTDOCUMENTTYPECODE].[DESCRIPTION] as [DOCUMENTTYPE],
    [MKTDOCUMENT].[COST],
    [MKTDOCUMENT].[COSTDISTRIBUTIONMETHOD],
    case when ([MKTDOCUMENT].[FILENAME] is null or len([MKTDOCUMENT].[FILENAME]) = 0) then [MKTDOCUMENT].[FILEURL] else [MKTDOCUMENT].[FILENAME] end as [DOCUMENT],
    convert(bit, case when ([MKTDOCUMENT].[FILEURL] is not null and len([MKTDOCUMENT].[FILEURL]) > 0) or ([MKTDOCUMENT].[FILENAME] is not null and len([MKTDOCUMENT].[FILENAME]) > 0) then 1 else 0 end) as [HASDOCUMENT],
    convert(bit, case when [MKTDOCUMENT].[FILENAME] is not null and len([MKTDOCUMENT].[FILENAME]) > 0 then 1 else 0 end) as [ISATTACHMENT],
    convert(bit, case when [MKTPACKAGEDOCUMENT].[ID] is null then 0 else 1 end) as [CANBEDELETED],
    case when len(isnull([MKTDOCUMENT].[FILENAME], '')) > 0 then convert(uniqueidentifier, 'EE0B7FDB-1466-4A0A-AB79-DCE605AC3F77') else null end as [VIEWDATAFORMID],
    case when len(isnull([MKTDOCUMENT].[FILENAME], '')) > 0 then 'FILE' else null end as [FILEFIELDID],
    [MKTDOCUMENT].[BASECURRENCYID],
    [SITE].[NAME] as [SITE],
    [MKTDOCUMENT].[DESCRIPTION]
  from dbo.[MKTDOCUMENT]
  inner join dbo.[MKTPACKAGEDOCUMENT] on [MKTPACKAGEDOCUMENT].[DOCUMENTID] = [MKTDOCUMENT].[ID]
  left outer join dbo.[SITE] on [SITE].[ID] = [MKTDOCUMENT].[SITEID]
  left outer join dbo.[MKTDOCUMENTTYPECODE] on [MKTDOCUMENTTYPECODE].[ID] = [MKTDOCUMENT].[DOCUMENTTYPECODEID]
  where [MKTPACKAGEDOCUMENT].[PACKAGEID] = @PACKAGEID
  order by [MKTDOCUMENT].[NAME]

  return 0;