USP_MKTPACKAGE_GETDATALIST

CREATE procedure dbo.[USP_MKTPACKAGE_GETDATALIST]
(
  @CURRENTAPPUSERID uniqueidentifier,
  @CHANNELCODE tinyint = null,
  @CATEGORYCODEID uniqueidentifier = null,
  @SITEFILTERMODE tinyint = 0,
  @SITESSELECTED xml = null,
  @SECURITYFEATUREID uniqueidentifier = null,
  @SECURITYFEATURETYPE tinyint,
  @CURRENCYCODE tinyint = 0
)
as
  set nocount on;

  select
    [MKTPACKAGE].[ID],
    [MKTPACKAGE].[NAME],
    [MKTPACKAGE].[CODE],
    case @CURRENCYCODE when 1 then [MKTPACKAGE].[ORGANIZATIONUNITCOST]
                       else [MKTPACKAGE].[UNITCOST] end [UNITCOST],
    [MKTPACKAGE].[COSTDISTRIBUTIONMETHOD],
    [MKTPACKAGE].[DESCRIPTION],
    dbo.[UFN_TRANSLATIONFUNCTION_SITE_GETNAME]([MKTPACKAGE].[SITEID]) as [SITE],
    [MKTPACKAGE].[CHANNEL],
    [MKTPACKAGE].[CHANNELCODE],
    [MKTPACKAGECATEGORYCODE].[DESCRIPTION] as [CATEGORY],
    case [MKTPACKAGE].[CHANNELCODE] when 0 then [LETTERCODE].[NAME]
                                    when 1 then [EMAILTEMPLATE].[NAME]
                                    when 2 then [MKTPACKAGE].[DOCUMENTFILENAME]
                                    when 3 then [MKTCREATIVE].[NAME] end as [CONTENT],
    [MKTPACKAGE].[NETCOMMUNITYTEMPLATEID],
    coalesce([EXPORTDEFINITIONEMAIL].[NAME], [EXPORTDEFINITIONMAIL].[NAME], [EXPORTDEFINITIONPACKAGE].[NAME]) as [EXPORTDEFINITION],
    case @CURRENCYCODE when 1 then dbo.[UFN_CURRENCY_GETORGANIZATIONCURRENCY]()
                       else [MKTPACKAGE].[BASECURRENCYID] end [CURRENCYID]
  from dbo.[MKTPACKAGE]
  left outer join dbo.[MKTPACKAGECATEGORYCODE] on [MKTPACKAGECATEGORYCODE].[ID] = [MKTPACKAGE].[PACKAGECATEGORYCODEID]
  left outer join dbo.[LETTERCODE] on [LETTERCODE].[ID] = [MKTPACKAGE].[LETTERCODEID]
  left outer join dbo.[MKTCREATIVE] on [MKTCREATIVE].[ID] = [MKTPACKAGE].[CREATIVEID]
  left outer join dbo.[EXPORTDEFINITION] as [EXPORTDEFINITIONMAIL] on [EXPORTDEFINITIONMAIL].[ID] = isnull(case [MKTPACKAGE].[CHANNELCODE] when 0 then [LETTERCODE].[EXPORTDEFINITIONID] else null end, [MKTPACKAGE].[EXPORTDEFINITIONID])
  left outer join dbo.[MKTEXPORTDEFINITION] on ([MKTEXPORTDEFINITION].[NETCOMMUNITYDATASOURCEID] = [MKTPACKAGE].[NETCOMMUNITYDATASOURCEID] and [MKTPACKAGE].[NETCOMMUNITYDATASOURCEID] > 0)
  left outer join dbo.[EXPORTDEFINITION] as [EXPORTDEFINITIONEMAIL] on [EXPORTDEFINITIONEMAIL].[ID] = isnull(case [MKTPACKAGE].[CHANNELCODE] when 1 then [MKTEXPORTDEFINITION].[EXPORTDEFINITIONID] else null end, [MKTPACKAGE].[EXPORTDEFINITIONID])
  left outer join dbo.[EXPORTDEFINITION] as [EXPORTDEFINITIONPACKAGE] on [EXPORTDEFINITIONPACKAGE].[ID] = [MKTPACKAGE].[EXPORTDEFINITIONID]
  left outer join dbo.[EMAILTEMPLATE] as [EMAILTEMPLATE] on [EMAILTEMPLATE].[ID] = [MKTPACKAGE].[NETCOMMUNITYTEMPLATEID]
  where 
    (@CHANNELCODE is null or @CHANNELCODE = [MKTPACKAGE].[CHANNELCODE])
  and 
    (@CATEGORYCODEID is null or @CATEGORYCODEID = [MKTPACKAGE].[PACKAGECATEGORYCODEID])
  and (
        (
          ( -- check site security

            select count(*) 
            from
              (select [SITEID]
               from dbo.[MKTPACKAGE] as [PACKAGESITE]
               where [PACKAGESITE].[ID] = [MKTPACKAGE].[ID]) 
            as [PACKAGESITE]
            where (dbo.[UFN_APPUSER_ISSYSADMIN](@CURRENTAPPUSERID) = 1 or exists (select top 1 1 from dbo.[UFN_SITESFORUSERONFEATURE](@CURRENTAPPUSERID, @SECURITYFEATUREID, @SECURITYFEATURETYPE) where SITEID = [PACKAGESITE].[SITEID] or (SITEID is null and [PACKAGESITE].[SITEID] is null)))
          ) > 0
        )
      and 
        ( -- apply site filter

          @SITEFILTERMODE = 0
          or [MKTPACKAGE].[SITEID] in (select [SITEID] from dbo.[UFN_SITE_BUILDDATALISTSITEFILTER](@CURRENTAPPUSERID, @SITEFILTERMODE, @SITESSELECTED))
        )
      )    
  and [MKTPACKAGE].[ISSYSTEM] = 0

  order by
    [NAME];

  return 0;