USP_MKTPACKAGE_UPDATECODE

CREATE procedure dbo.[USP_MKTPACKAGE_UPDATECODE]
(
  @PACKAGEID uniqueidentifier,
  @PACKAGECODE nvarchar(10),
  @PACKAGECODEVALUEID uniqueidentifier,
  @CHANNELSOURCECODE nvarchar(10),
  @CHANNELSOURCECODEVALUEID uniqueidentifier,
  @CHANGEAGENTID uniqueidentifier,
  @CURRENTAPPUSERID uniqueidentifier = null
)
as
  set nocount on;

  /* Update the package with the selected code */
  declare @OLDCODE nvarchar(10);
  declare @OLDCODEVALUEID uniqueidentifier;
  declare @OLDCHANNELSOURCECODE nvarchar(10);
  declare @OLDCHANNELSOURCECODEVALUEID uniqueidentifier;
  declare @UPDATEPACKAGECODE bit;
  declare @UPDATECHANNELSOURCECODE bit;

  declare @CURRENTDATE datetime;
  set @CURRENTDATE = getdate();

  select
    @OLDCODE = coalesce([CODE], ''),
    @OLDCODEVALUEID = [PARTDEFINITIONVALUESID],
    @OLDCHANNELSOURCECODE = [CHANNELSOURCECODE] ,
    @OLDCHANNELSOURCECODEVALUEID = [CHANNELPARTDEFINITIONVALUESID]
  from dbo.[MKTPACKAGE]
  where [ID] = @PACKAGEID;

  set @UPDATEPACKAGECODE = case when @PACKAGECODEVALUEID is not null and (@OLDCODEVALUEID is null or not (@OLDCODE = @PACKAGECODE and @OLDCODEVALUEID = @PACKAGECODEVALUEID)) then 1 else 0 end;
  set @UPDATECHANNELSOURCECODE = case when @CHANNELSOURCECODEVALUEID is not null and (@OLDCHANNELSOURCECODEVALUEID is null or not (@OLDCHANNELSOURCECODE = @CHANNELSOURCECODE and @OLDCHANNELSOURCECODEVALUEID = @CHANNELSOURCECODEVALUEID)) then 1 else 0 end;

  if @UPDATEPACKAGECODE = 1
    set @UPDATEPACKAGECODE = dbo.[UFN_MKTPACKAGE_APPUSER_GRANTED_EDITFORM](@PACKAGEID, @CURRENTAPPUSERID);
  if @UPDATECHANNELSOURCECODE = 1
    set @UPDATECHANNELSOURCECODE = dbo.[UFN_MKTPACKAGE_APPUSER_GRANTED_EDITFORM](@PACKAGEID, @CURRENTAPPUSERID);

  /* Update the packagecode if the old code value ID is nothing and the package code value ID is supplied */
  if @UPDATEPACKAGECODE = 1
    update dbo.[MKTPACKAGE] set
      [CODE] = @PACKAGECODE,
      [PARTDEFINITIONVALUESID] = @PACKAGECODEVALUEID,
      [CHANGEDBYID] = @CHANGEAGENTID,
      [DATECHANGED] = @CURRENTDATE
    where [ID] = @PACKAGEID;

  /* Update the package with the new channel source code */
  if @UPDATECHANNELSOURCECODE = 1
    update dbo.[MKTPACKAGE] set
      [CHANNELSOURCECODE] = @CHANNELSOURCECODE,
      [CHANNELPARTDEFINITIONVALUESID] = @CHANNELSOURCECODEVALUEID,
      [CHANGEDBYID] = @CHANGEAGENTID,
      [DATECHANGED] = @CURRENTDATE
    where [ID] = @PACKAGEID;

  return 0;