USP_DATALIST_DIRECTMARKETINGEFFORTBATCHPACKAGE

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

  if exists(select * from dbo.[MKTPACKAGE] where [ID] = @PACKAGEID)
    select top 1
      cast(1 as bit) as [EXISTINGPACKAGE],
      [MKTPACKAGE].[NAME],
      [MKTPACKAGE].[DESCRIPTION],
      [CODEVALUE].[ID] as [CODEVALUEID],
      [CODEVALUE].[DESCRIPTION] as [CODEVALUE],
      (select [FORMAT] from dbo.[MKTSOURCECODEPARTDEFINITIONVALUES] where [ID] = [MKTPACKAGE].[PARTDEFINITIONVALUESID]) as [CODEFORMAT],
      (select [REGEX] from dbo.[MKTSOURCECODEPARTDEFINITIONVALUES] where [ID] = [MKTPACKAGE].[PARTDEFINITIONVALUESID]) as [CODEREGEX],
      [MKTPACKAGE].[CODE],
      [CHANNELCODEVALUE].[ID] as [CHANNELCODEVALUEID],
      [CHANNELCODEVALUE].[DESCRIPTION] as [CHANNELCODEVALUE],
      (select [FORMAT] from dbo.[MKTSOURCECODEPARTDEFINITIONVALUES] where [ID] = [MKTPACKAGE].[CHANNELPARTDEFINITIONVALUESID]) as [CHANNELCODEFORMAT],
      (select [REGEX] from dbo.[MKTSOURCECODEPARTDEFINITIONVALUES] where [ID] = [MKTPACKAGE].[CHANNELPARTDEFINITIONVALUESID]) as [CHANNELCODEREGEX],
      [MKTPACKAGE].[CHANNELSOURCECODE] as [CHANNELCODE],
      [MKTPACKAGECATEGORYCODE].[ID] as [CATEGORYCODEID],
      [MKTPACKAGECATEGORYCODE].[DESCRIPTION] as [CATEGORY],
      [MKTPACKAGE].[UNITCOST] as [COST],
      [MKTPACKAGE].[COSTDISTRIBUTIONMETHODCODE],
      [MKTPACKAGE].[COSTDISTRIBUTIONMETHOD],
      [MKTPACKAGE].[CHANNELCODE] as [PACKAGETYPECODE],
      [MKTPACKAGE].[CHANNEL] as [PACKAGETYPE],
      dbo.[UFN_TRANSLATIONFUNCTION_SITE_GETNAME]([MKTPACKAGE].[SITEID]) as [SITE],
      [MKTPACKAGE].[SITEID]
    from dbo.[MKTPACKAGE]
    left join dbo.[MKTSOURCECODEPARTDEFINITIONVALUES] as [CODEVALUE] on [CODEVALUE].[ID] = [MKTPACKAGE].[PARTDEFINITIONVALUESID]
    left join dbo.[MKTSOURCECODEPARTDEFINITIONVALUES] as [CHANNELCODEVALUE] on [CHANNELCODEVALUE].[ID] = [MKTPACKAGE].[CHANNELPARTDEFINITIONVALUESID]
    left join dbo.[MKTPACKAGECATEGORYCODE] on [MKTPACKAGECATEGORYCODE].[ID] = [MKTPACKAGE].[PACKAGECATEGORYCODEID]
    where [MKTPACKAGE].[ID] = @PACKAGEID;
  else
    select top 1
      cast(0 as bit) as [EXISTINGPACKAGE],
      [BATCHDIRECTMARKETINGEFFORTPACKAGE].[NAME],
      [BATCHDIRECTMARKETINGEFFORTPACKAGE].[DESCRIPTION],
      [CODEVALUE].[ID] as [CODEVALUEID],
      [CODEVALUE].[DESCRIPTION] as [CODEVALUE],
      (select [FORMAT] from dbo.[MKTSOURCECODEPARTDEFINITIONVALUES] where [ID] = [BATCHDIRECTMARKETINGEFFORTPACKAGE].[CODEPARTDEFINITIONVALUESID]) as [CODEFORMAT],
      (select [REGEX] from dbo.[MKTSOURCECODEPARTDEFINITIONVALUES] where [ID] = [BATCHDIRECTMARKETINGEFFORTPACKAGE].[CODEPARTDEFINITIONVALUESID]) as [CODEREGEX],
      [BATCHDIRECTMARKETINGEFFORTPACKAGE].[CODE],
      [CHANNELCODEVALUE].[ID] as [CHANNELCODEVALUEID],
      [CHANNELCODEVALUE].[DESCRIPTION] as [CHANNELCODEVALUE],
      (select [FORMAT] from dbo.[MKTSOURCECODEPARTDEFINITIONVALUES] where [ID] = [BATCHDIRECTMARKETINGEFFORTPACKAGE].[CHANNELCODEPARTDEFINITIONVALUESID]) as [CHANNELCODEFORMAT],
      (select [REGEX] from dbo.[MKTSOURCECODEPARTDEFINITIONVALUES] where [ID] = [BATCHDIRECTMARKETINGEFFORTPACKAGE].[CHANNELCODEPARTDEFINITIONVALUESID]) as [CHANNELCODEREGEX],
      [BATCHDIRECTMARKETINGEFFORTPACKAGE].[CHANNELCODE],
      [MKTPACKAGECATEGORYCODE].[ID] as [CATEGORYCODEID],
      [MKTPACKAGECATEGORYCODE].[DESCRIPTION] as [CATEGORY],
      [BATCHDIRECTMARKETINGEFFORTPACKAGE].[COST],
      [BATCHDIRECTMARKETINGEFFORTPACKAGE].[COSTDISTRIBUTIONMETHODCODE],
      [BATCHDIRECTMARKETINGEFFORTPACKAGE].[COSTDISTRIBUTIONMETHOD],
      [BATCHDIRECTMARKETINGEFFORTPACKAGE].[PACKAGETYPECODE],
      [BATCHDIRECTMARKETINGEFFORTPACKAGE].[PACKAGETYPE],
      dbo.[UFN_TRANSLATIONFUNCTION_SITE_GETNAME]([BATCHDIRECTMARKETINGEFFORTPACKAGE].[SITEID]) as [SITE],
      [BATCHDIRECTMARKETINGEFFORTPACKAGE].[SITEID]
    from dbo.[BATCHDIRECTMARKETINGEFFORT]
inner join dbo.[BATCHDIRECTMARKETINGEFFORTPACKAGE] on [BATCHDIRECTMARKETINGEFFORTPACKAGE].[ID] = [BATCHDIRECTMARKETINGEFFORT].[BATCHDIRECTMARKETINGEFFORTPACKAGEID]
    left join dbo.[MKTSOURCECODEPARTDEFINITIONVALUES] as [CODEVALUE] on [CODEVALUE].[ID] = [BATCHDIRECTMARKETINGEFFORTPACKAGE].[CODEPARTDEFINITIONVALUESID]
    left join dbo.[MKTSOURCECODEPARTDEFINITIONVALUES] as [CHANNELCODEVALUE] on [CHANNELCODEVALUE].[ID] = [BATCHDIRECTMARKETINGEFFORTPACKAGE].[CHANNELCODEPARTDEFINITIONVALUESID]
    left join dbo.[MKTPACKAGECATEGORYCODE] on [MKTPACKAGECATEGORYCODE].[ID] = [BATCHDIRECTMARKETINGEFFORTPACKAGE].[MKTPACKAGECATEGORYCODEID]
    where [BATCHDIRECTMARKETINGEFFORT].[BATCHID] = @BATCHID
    and [BATCHDIRECTMARKETINGEFFORTPACKAGE].[ID] = @PACKAGEID;

  return 0;