USP_REVENUEBATCH_GETMARKETINGDATA

CREATE procedure dbo.[USP_REVENUEBATCH_GETMARKETINGDATA]
(
  @FINDERNUMBER bigint = 0,
  @LOOKUPSINGLEDESIGNATION bit = 0,
  @CHANGEAGENTID uniqueidentifier = null,
  @CURRENTAPPUSERID uniqueidentifier = null,
  @SOURCECODE nvarchar(50) = '' output,
  @MAILINGID uniqueidentifier = null output,
  @APPEALID uniqueidentifier = null output,
  @CONSTITUENTID uniqueidentifier = null output,
  @FINDERNUMBERISVALID bit = 0 output,
  @SINGLEDESIGNATIONID uniqueidentifier = null output,
  @BATCHID uniqueidentifier = null,
  @IMPORT bit = 0
)
as
  set nocount on;

  declare @LOOKUPDEFAULTDESIGNATIONFROMAPPEAL bit = 0;

  begin try
    if @CHANGEAGENTID is null
      exec dbo.[USP_CHANGEAGENT_GETORCREATECHANGEAGENT] @CHANGEAGENTID output;

    if @FINDERNUMBER > 0
      begin
        /*
          Stop parameter sniffing on @FINDERNUMBER.  When a plan is generated for this procedure and FINDERNUMBER is found
          in a large number of rows, SQL may determine it is more efficient to do an index scan than seek any FINDERNUMBER index.
          We do not want to cache this plan as it does not represent a typical finder number and the scan can be significantly 
          worse in cases where FINDERNUMBER appears in a small amount of rows.
        */
        declare @FINDERNUMBER_LOCAL bigint = @FINDERNUMBER;

        /* Set the appeal, marketing effort, source code, and constituent if finder number is supplied. */
        declare @FN_CONSTITUENTID uniqueidentifier;
        declare @FN_APPEALID uniqueidentifier;
        declare @FN_SOURCECODE nvarchar(60);
        declare @FN_MAILINGID uniqueidentifier; 

        --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_LOCAL;

        if @FN_CONSTITUENTID is null and @BATCHID is not null --not a constituent yet

          begin

            -- Check to see if there is a discrepancy in the constituent records between the BATCHREVENUE and BATCHREVENUECONSTITUENTAPPEAL

            declare @BATCHREVENUE_CONSTITUENTID uniqueidentifier;

            select top 1
              @BATCHREVENUE_CONSTITUENTID = [CONSTITUENTID]
            from dbo.[BATCHREVENUE]
            where [FINDERNUMBER] = @FINDERNUMBER_LOCAL
            and [BATCHID] = @BATCHID;

            declare @CONSTITUENT_EXISTS bit = case when exists(select 1 from dbo.[CONSTITUENT] where [ID] = @BATCHREVENUE_CONSTITUENTID) then 1 else 0 end;

            if @CONSTITUENT_EXISTS = 0
              begin

                --If no match was found above, then we have a finder number from an acquisition list,

                --so we need to lookup the finder number and add the list person as a constituent.

                exec dbo.[USP_FINDERNUMBER_ADDMEMBER]
                  @ID = @FN_CONSTITUENTID output,
                  @CHANGEAGENTID = @CHANGEAGENTID,
                  @FINDERNUMBER = @FINDERNUMBER_LOCAL,
                  @REVENUEBATCH = 1,
                  @CURRENTAPPUSERID = @CURRENTAPPUSERID;

              end

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

            -- WI 350038: Check if there is a discrepancy between the batch revenue and batch constituent appeal tables.  It could be an indication

            -- of a resolved duplicate constituent record in the batch revenue table.  So therefore we should take the record in batch revenue.

            if @FN_CONSTITUENTID <> @BATCHREVENUE_CONSTITUENTID and @CONSTITUENT_EXISTS = 1
              begin

                -- Delete the record in the batch revenue constituent table because the constituent now exists

                exec dbo.[USP_BATCHREVENUECONSTITUENT_DELETEBYID_WITHCHANGEAGENTID] @FN_CONSTITUENTID, @CHANGEAGENTID;

                -- Update the batch revenue constituent appeal constituent id to be the correct constituent id

                update dbo.[BATCHREVENUECONSTITUENTAPPEAL]
                set [CONSTITUENTID] = @BATCHREVENUE_CONSTITUENTID 
                where [CONSTITUENTID] = @FN_CONSTITUENTID;

                -- Setting finder number constituent id to the correct constituent id from the batch revenue table

                set @FN_CONSTITUENTID = @BATCHREVENUE_CONSTITUENTID;

              end

          end

        if @FN_CONSTITUENTID is not null
          begin
            -- If finder number info was loaded, override any defaulted mailing values.

            select
              @CONSTITUENTID = @FN_CONSTITUENTID,
              @SOURCECODE = isnull(@FN_SOURCECODE, ''),
              @APPEALID = @FN_APPEALID,
              @MAILINGID = @FN_MAILINGID,
              @FINDERNUMBERISVALID = 1,
              @LOOKUPDEFAULTDESIGNATIONFROMAPPEAL = 1;
          end
      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
        /* Set the appeal and marketing effort if a source code is supplied, and the source code is unique across all marketing efforts */
        select
          @APPEALID = nullif([MKTSEGMENTATIONACTIVATE].[APPEALSYSTEMID], ''),
          @MAILINGID = [MKTSOURCECODEMAP].[SEGMENTATIONID],
          @LOOKUPDEFAULTDESIGNATIONFROMAPPEAL = 1
        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;
      end

    else if @MAILINGID is not null
      begin
        /* Set the appeal if a marketing effort is supplied */
        select
          @APPEALID = nullif([MKTSEGMENTATIONACTIVATE].[APPEALSYSTEMID], ''),
          @LOOKUPDEFAULTDESIGNATIONFROMAPPEAL = 1
        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;
      end

    else if @APPEALID is not null and (select count(*) 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 nullif([MKTSEGMENTATIONACTIVATE].[APPEALSYSTEMID], '') = @APPEALID) = 1  and @IMPORT = 1
      begin
        /* Set the marketing effort if an appeal is supplied, and the appeal is unique across all marketing efforts */
        select
          @MAILINGID = [MKTSEGMENTATION].[ID],
          @LOOKUPDEFAULTDESIGNATIONFROMAPPEAL = 1
        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 nullif([MKTSEGMENTATIONACTIVATE].[APPEALSYSTEMID], '') = @APPEALID;
      end

    if @LOOKUPDEFAULTDESIGNATIONFROMAPPEAL = 1 and @SINGLEDESIGNATIONID is null and @LOOKUPSINGLEDESIGNATION = 1
      begin
        select
          @SINGLEDESIGNATIONID = [DESIGNATIONID]
        from dbo.[APPEALDESIGNATION]
        where [APPEALID] = @APPEALID
        and [ISDEFAULT] = 1;
      end
  end try

  begin catch
    exec dbo.[USP_RAISE_ERROR];
  end catch

  return 0;