USP_DATALIST_MKTPACKAGEMATERIALEXPENSE

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

  select
    [MKTPACKAGEMATERIAL].[ID],
    1 as [MATERIALOREXPENSECODE],
    [MKTMATERIAL].[ID] as [MATERIALID],
    null as [EXPENSEID],
    [MKTMATERIAL].[NAME],
    [MKTMATERIALTYPECODE].[DESCRIPTION] as [TYPE],
    [CONSTITUENT].[NAME] as [VENDOR],
    [MKTMATERIAL].[COST],
    [MKTMATERIAL].[COSTDISTRIBUTIONMETHOD],
    convert(bit, case when [MKTPACKAGEMATERIAL].[ID] is null then 0 else 1 end) as [CANBEDELETED],
    [MKTMATERIAL].[BASECURRENCYID],
    [SITE].[NAME] as [SITE],
    [MKTMATERIAL].[DESCRIPTION]
  from dbo.[MKTMATERIAL]
  inner join dbo.[MKTPACKAGEMATERIAL] on [MKTPACKAGEMATERIAL].[MATERIALID] = [MKTMATERIAL].[ID]
  left outer join dbo.[SITE] on [SITE].[ID] = [MKTMATERIAL].[SITEID]
  left outer join dbo.[MKTMATERIALTYPECODE] on [MKTMATERIALTYPECODE].[ID] = [MKTMATERIAL].[MATERIALTYPECODEID]
  left outer join dbo.[CONSTITUENT] on [CONSTITUENT].[ID] = [MKTMATERIAL].[VENDORID]
  where [MKTPACKAGEMATERIAL].[PACKAGEID] = @PACKAGEID
  union all
  select
    [MKTPACKAGEEXPENSE].[ID],
    2 as [MATERIALOREXPENSECODE],
    null as [MATERIALID],
    [MKTEXPENSE].[ID] as [EXPENSEID],
    [MKTEXPENSE].[NAME],
    [MKTEXPENSETYPECODE].[DESCRIPTION] as [TYPE],
    [CONSTITUENT].[NAME] as [VENDOR],
    [MKTEXPENSE].[COST],
    [MKTEXPENSE].[COSTDISTRIBUTIONMETHOD],
    convert(bit, case when [MKTPACKAGEEXPENSE].[ID] is null then 0 else 1 end) as [CANBEDELETED],
    [MKTEXPENSE].[BASECURRENCYID],
    [SITE].[NAME] as [SITE],
    [MKTEXPENSE].[DESCRIPTION]
  from dbo.[MKTEXPENSE]
  inner join dbo.[MKTPACKAGEEXPENSE] on [MKTPACKAGEEXPENSE].[EXPENSEID] = [MKTEXPENSE].[ID]
  left outer join dbo.[SITE] on [SITE].[ID] = [MKTEXPENSE].[SITEID]
  left outer join dbo.[MKTEXPENSETYPECODE] on [MKTEXPENSETYPECODE].[ID] = [MKTEXPENSE].[EXPENSETYPECODEID]
  left outer join dbo.[CONSTITUENT] on [CONSTITUENT].[ID] = [MKTEXPENSE].[VENDORID]
  where [MKTPACKAGEEXPENSE].[PACKAGEID] = @PACKAGEID
  order by 5;

  return 0;