USP_REVENUESPLITBUSINESSUNIT_GETMARKETINGFIELDS

CREATE procedure dbo.USP_REVENUESPLITBUSINESSUNIT_GETMARKETINGFIELDS(
 @REVENUESPLITID uniqueidentifier,
 @TESTSEGMENTID uniqueidentifier =null output,
 @SEGMENTID uniqueidentifier = null output,
 @MAILINGID uniqueidentifier = null output,
 @APPEALID uniqueidentifier = null output      
)
as
begin
    declare @REVENUEID uniqueidentifier;
  declare @CONSTITUENTID uniqueidentifier;
  declare @FINDERNUMBER bigint;

  declare @REVENUEMAILINGID uniqueidentifier;
  declare @REVENUEAPPEALID uniqueidentifier;
  declare @SOURCECODE nvarchar(max);

  declare @SOURCECODEMAILINGID uniqueidentifier;
  declare @SOURCECODEAPPEALID uniqueidentifier;

  --Grab some revenue related information.

  select 
    @REVENUEID= FINANCIALTRANSACTION.ID,
    @REVENUEMAILINGID= REVENUE_EXT.MAILINGID,
    @REVENUEAPPEALID= REVENUE_EXT.APPEALID,
    @SOURCECODE= REVENUE_EXT.SOURCECODE,
    @CONSTITUENTID = FINANCIALTRANSACTION.CONSTITUENTID,
    @FINDERNUMBER = REVENUE_EXT.FINDERNUMBER
  from dbo.FINANCIALTRANSACTION inner join dbo.REVENUE_EXT on FINANCIALTRANSACTION.ID = REVENUE_EXT.ID
  inner join dbo.FINANCIALTRANSACTIONLINEITEM on FINANCIALTRANSACTIONLINEITEM.FINANCIALTRANSACTIONID= FINANCIALTRANSACTION.ID
  where FINANCIALTRANSACTIONLINEITEM.ID = @REVENUESPLITID


  if @REVENUEMAILINGID is not null -- Mailing is on revenue record

    set @MAILINGID = @REVENUEMAILINGID

  if @REVENUEAPPEALID is not null -- Appeal is on revenue record

    set @APPEALID = @REVENUEAPPEALID

  -- Look in mailing data table first for normal constituent segments in a direct marketing efforts.

  if @MAILINGID is not null
  begin    
    declare @SQLEXEC nvarchar(max) ='select ';
    declare @COLUMNS nvarchar(max); 
    declare @FROMCLAUSE nvarchar(max);
    declare @WHERECLAUSE nvarchar(max);

    set @COLUMNS =  ' @TESTSEGMENTID = MD.TESTSEGMENTID, '+ char(13) +
                    ' @SEGMENTID = MD.SEGMENTID  ';

    set @FROMCLAUSE = ' from ' + dbo.UFN_MKTSEGMENTATIONACTIVATE_MAKETABLENAME(@MAILINGID)+' MD ';
    set @WHERECLAUSE =' where MD.DONORID=@CONSTITUENTID ';
    if @APPEALID is null
    begin
      set @COLUMNS= @COLUMNS + ',@APPEALID = A.ID '
      set @FROMCLAUSE = @FROMCLAUSE + ' inner join dbo.CONSTITUENTAPPEAL CA on CA.ID = MD.CONSTITUENTAPPEALID '+ char(13) +
                                      ' inner join dbo.APPEAL A on A.ID = CA.APPEALID '
    end

    set @SQLEXEC = @SQLEXEC + @COLUMNS + @FROMCLAUSE + @WHERECLAUSE;

    if @APPEALID is null
      exec sp_executesql @SQLEXEC,
        N'@TESTSEGMENTID uniqueidentifier output, @SEGMENTID uniqueidentifier output, @APPEALID uniqueidentifier output, @CONSTITUENTID uniqueidentifier',
        @TESTSEGMENTID = @TESTSEGMENTID output, @SEGMENTID=@SEGMENTID output, @APPEALID=@APPEALID output, @CONSTITUENTID = @CONSTITUENTID;
    else
      exec sp_executesql @SQLEXEC,
        N'@TESTSEGMENTID uniqueidentifier output, @SEGMENTID uniqueidentifier output, @CONSTITUENTID uniqueidentifier',
        @TESTSEGMENTID = @TESTSEGMENTID output, @SEGMENTID=@SEGMENTID output, @CONSTITUENTID = @CONSTITUENTID;   
  end

  -- Check for an imported list segment in a direct marketing effort.

  if @TESTSEGMENTID is null and @SEGMENTID is null and (@FINDERNUMBER is not null and @FINDERNUMBER<>0)
  begin
    if @MAILINGID is null
      set @MAILINGID= dbo.UFN_MKTSEGMENTATIONFINDERNUMBER_GETSEGMENTATION(@FINDERNUMBER)

    if @APPEALID is null
      select 
        @APPEALID = coalesce(APPEALSYSTEMID, '00000000-0000-0000-0000-000000000000')
      from dbo.MKTSEGMENTATIONACTIVATE
      inner join MKTSEGMENTATION 
        on MKTSEGMENTATION.ID = MKTSEGMENTATIONACTIVATE.SEGMENTATIONID
      where MKTSEGMENTATIONACTIVATE.SEGMENTATIONID = @MAILINGID 
        and MKTSEGMENTATION.ACTIVE=1
        and APPEALSYSTEMID <> ''

    declare @LISTSQLEXEC nvarchar(max);

    set @LISTSQLEXEC =  ' select ' + char(13)+ 
                    ' @TESTSEGMENTID = MD.TESTSEGMENTID, '+ char(13) +
                    ' @SEGMENTID = MD.SEGMENTID  ' +
                    ' from ' + dbo.UFN_MKTSEGMENTATIONACTIVATE_MAKETABLENAME(@MAILINGID)+' MD ' +
                    ' where MD.FINDERNUMBER = @FINDERNUMBER ';
    exec sp_executesql @LISTSQLEXEC,
      N'@TESTSEGMENTID uniqueidentifier output, @SEGMENTID uniqueidentifier output, @FINDERNUMBER bigint',
        @TESTSEGMENTID = @TESTSEGMENTID output, @SEGMENTID=@SEGMENTID output, @FINDERNUMBER = @FINDERNUMBER;   
  end


  -- Check for a vendor managed list segment in a direct marketing effort.

  if @TESTSEGMENTID is null and @SEGMENTID is null and (@SOURCECODE is not null or @SOURCECODE<>'')
  begin   
    --Grab source code information

    exec dbo.USP_DATAFORMTEMPLATE_VIEW_MKTSOURCECODEMAP
      @SOURCECODE,
      0,
      null,
      @SOURCECODEMAILINGID output,
      null,
      null,
      null,
      @SOURCECODEAPPEALID output,
      null

    if @APPEALID is null and @SOURCECODEAPPEALID is null
      select 
        @APPEALID = APPEALSYSTEMID
      from dbo.MKTSEGMENTATIONACTIVATE
      inner join MKTSOURCECODEMAP on MKTSOURCECODEMAP.SEGMENTATIONID = MKTSEGMENTATIONACTIVATE.SEGMENTATIONID
      inner join MKTSEGMENTATION on MKTSEGMENTATION.ID = MKTSOURCECODEMAP.SEGMENTATIONID
      where MKTSEGMENTATION.ACTIVE=1 and MKTSOURCECODEMAP.SOURCECODE = @SOURCECODE and MKTSOURCECODEMAP.SEGMENTATIONID = @MAILINGID
    else
      if @APPEALID is null
        set @APPEALID = @SOURCECODEAPPEALID

    if @SOURCECODEMAILINGID is not null    
    begin
      if @MAILINGID is null
        set @MAILINGID = @SOURCECODEMAILINGID

      select
        @TESTSEGMENTID = SEGMENTATIONTESTSEGMENTID,
        @SEGMENTID = SEGMENTATIONSEGMENTID
      from dbo.MKTSOURCECODEMAP where SOURCECODE = @SOURCECODE and SEGMENTATIONID= @MAILINGID
    end
  end
end