USP_REVENUEBATCH_VALIDATEMARKETINGDATA

CREATE procedure dbo.[USP_REVENUEBATCH_VALIDATEMARKETINGDATA]
(
  @FINDERNUMBER bigint = 0,
  @SOURCECODE nvarchar(50) = '',
  @MAILINGID uniqueidentifier = null,
  @APPEALID uniqueidentifier = null,
  @CONSTITUENTID uniqueidentifier = null,
  @BATCHID uniqueidentifier = null
)
as
  set nocount on;

  if @FINDERNUMBER is null
    set @FINDERNUMBER = 0;
  if @SOURCECODE is null
    set @SOURCECODE = '';

  declare @MAILINGIDLOOKUP uniqueidentifier;

  begin try
    if @FINDERNUMBER <> 0
      begin
        /* Validate the finder number and dependent fields */
        declare @FN_CONSTITUENTID uniqueidentifier;
        declare @FN_APPEALID uniqueidentifier;
        declare @FN_SOURCECODE nvarchar(50);
        declare @FN_MAILINGID uniqueidentifier; 

        if @FINDERNUMBER > 0
          begin
            --First look in the constituent segment table for a match (don't bother looking in the constituent appeal table since it can be modified by the user)...

            select top 1
              @FN_CONSTITUENTID = [CONSTITUENTSEGMENT].[CONSTITUENTID],
              @FN_APPEALID = nullif([MKTSEGMENTATIONACTIVATE].[APPEALSYSTEMID], ''),
              @FN_SOURCECODE = [MKTSOURCECODEMAP].[SOURCECODE],
              @FN_MAILINGID = [MKTSEGMENTATIONSEGMENT].[SEGMENTATIONID]
            from dbo.[CONSTITUENTSEGMENT]
            inner join dbo.[MKTSEGMENTATIONSEGMENT] on [MKTSEGMENTATIONSEGMENT].[ID] = [CONSTITUENTSEGMENT].[SEGMENTID]
            left join dbo.[MKTSEGMENTATIONACTIVATE] on [MKTSEGMENTATIONACTIVATE].[SEGMENTATIONID] = [MKTSEGMENTATIONSEGMENT].[SEGMENTATIONID] and [MKTSEGMENTATIONACTIVATE].[RECORDSOURCEID] = 'DFB4B8C1-5E9A-4C14-ACE3-01C096B53BA0'
            left join dbo.[MKTSOURCECODEMAP] on [MKTSOURCECODEMAP].[ID] = [CONSTITUENTSEGMENT].[SOURCECODEMAPID]
            where [CONSTITUENTSEGMENT].[FINDERNUMBER] = @FINDERNUMBER
            option (recompile);

            if @FN_CONSTITUENTID is null --not a constituent yet

              select top 1
                @FN_CONSTITUENTID = [CONSTITUENTID],
                @FN_APPEALID = [APPEALID],
                @FN_SOURCECODE = [SOURCECODE],
                @FN_MAILINGID = [MKTSEGMENTATIONID]
              from dbo.[BATCHREVENUECONSTITUENTAPPEAL]
              where [FINDERNUMBER] = @FINDERNUMBER;

            if @FN_CONSTITUENTID is null and @BATCHID is not null
            begin
              --it is possible they are now a constituent but were in a list for this finder number

              --so there is no entry in CONSTITUENTSEGMENT or BATCHREVENUECONSTITUENTAPPEAL

              --so look in BATCHREVENUE

              select top 1
                @FN_CONSTITUENTID = [CONSTITUENTID],
                @FN_APPEALID = [APPEALID],
                @FN_SOURCECODE = [SOURCECODE],
                @FN_MAILINGID = [MAILINGID]
              from
                dbo.[BATCHREVENUE]
              where
                [FINDERNUMBER] = @FINDERNUMBER
                and [BATCHID] = @BATCHID;

              --check to make sure the finder number actually belongs on the effort

              set @MAILINGIDLOOKUP = dbo.[UFN_MKTSEGMENTATIONFINDERNUMBER_GETSEGMENTATION](@FINDERNUMBER);

              if @MAILINGIDLOOKUP is null or @MAILINGIDLOOKUP <> @FN_MAILINGID
                set @FN_CONSTITUENTID = null;
            end
          end

        if @FN_CONSTITUENTID is null
          raiserror('BBERR_FINDERNUMBERINVALID', 13, 1);
        else if @CONSTITUENTID is not null and (@FN_CONSTITUENTID is null or @CONSTITUENTID <> @FN_CONSTITUENTID)
          raiserror('BBERR_FINDERNUMBERCONSTITUENTMISMATCH', 13, 1);
        else if @APPEALID is not null and (@FN_APPEALID is null or @APPEALID <> @FN_APPEALID)
          raiserror('BBERR_FINDERNUMBERAPPEALMISMATCH', 13, 1);
        else if @MAILINGID is not null and (@FN_MAILINGID is null or @MAILINGID <> @FN_MAILINGID)
          raiserror('BBERR_FINDERNUMBERMAILINGMISMATCH', 13, 1);
        else if len(@SOURCECODE) > 0 and upper(@SOURCECODE) <> upper(isnull(@FN_SOURCECODE, ''))
          raiserror('BBERR_FINDERNUMBERSOURCECODEMISMATCH', 13, 1);
      end

    else if len(@SOURCECODE) > 0 and (select count(*) from dbo.[MKTSOURCECODEMAP] inner join dbo.[MKTSEGMENTATION] on [MKTSEGMENTATION].[ID] = [MKTSOURCECODEMAP].[SEGMENTATIONID] where [MKTSEGMENTATION].[ACTIVE] = 1 and [MKTSOURCECODEMAP].[SOURCECODE] = @SOURCECODE) = 1
      begin
        /* Validate source code and dependent fields, only if the source code is unique across all marketing efforts */
        declare @SC_APPEALID uniqueidentifier;
        declare @SC_MAILINGID uniqueidentifier;

        select
          @SC_APPEALID = nullif([MKTSEGMENTATIONACTIVATE].[APPEALSYSTEMID], ''),
          @SC_MAILINGID = [MKTSOURCECODEMAP].[SEGMENTATIONID]
        from dbo.[MKTSOURCECODEMAP] 
        inner join dbo.[MKTSEGMENTATIONACTIVATE] on [MKTSEGMENTATIONACTIVATE].[SEGMENTATIONID] = [MKTSOURCECODEMAP].[SEGMENTATIONID] and [MKTSEGMENTATIONACTIVATE].[RECORDSOURCEID] = 'DFB4B8C1-5E9A-4C14-ACE3-01C096B53BA0'
        inner join dbo.[MKTSEGMENTATION] on [MKTSEGMENTATION].[ID] = [MKTSOURCECODEMAP].[SEGMENTATIONID]
        where [MKTSEGMENTATION].[ACTIVE] = 1
        and [MKTSOURCECODEMAP].[SOURCECODE] = @SOURCECODE;

        if @MAILINGID is not null and (@SC_MAILINGID is null or @MAILINGID <> @SC_MAILINGID)
          raiserror('BBERR_SOURCECODEMAILINGMISMATCH', 13, 1);
        else if @APPEALID is not null and (@SC_APPEALID is null or @APPEALID <> @SC_APPEALID)
          raiserror('BBERR_SOURCECODEAPPEALMISMATCH', 13, 1);
      end

    else if @MAILINGID is not null
      begin
        /* Validate mailing and dependent fields */
        declare @ME_APPEALID uniqueidentifier;

        select
          @ME_APPEALID = nullif([MKTSEGMENTATIONACTIVATE].[APPEALSYSTEMID], '')
        from dbo.[MKTSEGMENTATIONACTIVATE]
        inner join dbo.[MKTSEGMENTATION] on [MKTSEGMENTATION].[ID] = [MKTSEGMENTATIONACTIVATE].[SEGMENTATIONID]
        where [MKTSEGMENTATION].[ACTIVE] = 1
        and [MKTSEGMENTATIONACTIVATE].[RECORDSOURCEID] = 'DFB4B8C1-5E9A-4C14-ACE3-01C096B53BA0'
        and [MKTSEGMENTATIONACTIVATE].[SEGMENTATIONID] = @MAILINGID;

        if @APPEALID is not null and (@ME_APPEALID is null or @APPEALID <> @ME_APPEALID)
          raiserror('BBERR_MAILINGAPPEALMISMATCH', 13, 1);

        if len(@SOURCECODE) > 0
          begin
            /* Validate that the source code is used by the mailing or not.  There could be     */
            /* duplicate source codes even within one mailing, but we don't care at this point. */
            declare @ME_SOURCECODE nvarchar(50);

            select top 1
              @ME_SOURCECODE = [MKTSOURCECODEMAP].[SOURCECODE]
            from dbo.[MKTSOURCECODEMAP]
            inner join dbo.[MKTSEGMENTATION] on [MKTSEGMENTATION].[ID] = [MKTSOURCECODEMAP].[SEGMENTATIONID]
            where [MKTSEGMENTATION].[ACTIVE] = 1
            and [MKTSOURCECODEMAP].[SEGMENTATIONID] = @MAILINGID
            and [MKTSOURCECODEMAP].[SOURCECODE] = @SOURCECODE;

            if upper(@SOURCECODE) <> upper(isnull(@ME_SOURCECODE, ''))
              raiserror('BBERR_MAILINGSOURCECODEMISMATCH', 13, 1);
          end
      end
  end try

  begin catch
    exec dbo.[USP_RAISE_ERROR];
    return 1;
  end catch

  return 0;