USP_REVENUESPLIT_APPLYBUSINESSUNITS

CREATE procedure dbo.USP_REVENUESPLIT_APPLYBUSINESSUNITS
(
  @REVENUEID uniqueidentifier,
  @CHANGEAGENTID uniqueidentifier = null,
  @CHANGEDATE datetime = null
)
as
begin
  /*
  1) Payments
      a) For each payment split, check if each split is towards a RG or Pledge
      b) For payments splits to commitments(RG or pledge), use the following hierarchy to find BU's.
          i)    Commitment
          ii)   Solicitor
          iii)  Test segment
          iv)   Segment
          v)    Effort
          vi)   Appeal
      c) For payment splits to straight donations, use the following hierarchy to find BU's
          i)    Solicitor
          ii)   Test segment
          iii)  Segment
          iv)   Effort
          v)    Appeal

  2) Commitments (RG and Pledge)- For all commitments apply business units based on this hierarchy.
      i)    Solicitor
      ii)   Test segment
      iii)  Segment
      iv)   Effort
      v)    Appeal
  */

  declare @REVENUESPLITID uniqueidentifier;
  declare @TRANSACTIONTYPECODE tinyint;
  declare @APPLICATIONCODE tinyint;
  declare @SPLITAMOUNT money;
  declare @DESIGNATIONID uniqueidentifier;
  declare @BASECURRENCYID uniqueidentifier;

  if @CHANGEAGENTID is null
    exec dbo.USP_CHANGEAGENT_GETORCREATECHANGEAGENT @CHANGEAGENTID output;

  if @CHANGEDATE is null
    set @CHANGEDATE = getdate(); 

  declare @CONTEXTCACHE varbinary(128);
  set @CONTEXTCACHE = context_info();
  set context_info @CHANGEAGENTID;

  declare @COUNT tinyint = 1;

    select @TRANSACTIONTYPECODE = FINANCIALTRANSACTION.TYPECODE
        ,@BASECURRENCYID = isnull(REVENUE_EXT.NONPOSTABLEBASECURRENCYID, CURRENCYSET.BASECURRENCYID)
    from dbo.FINANCIALTRANSACTION
    inner join dbo.REVENUE_EXT on REVENUE_EXT.ID = FINANCIALTRANSACTION.ID
    inner join dbo.PDACCOUNTSYSTEM on PDACCOUNTSYSTEM.ID = FINANCIALTRANSACTION.PDACCOUNTSYSTEMID
    inner join dbo.CURRENCYSET on CURRENCYSET.ID = PDACCOUNTSYSTEM.CURRENCYSETID
    where FINANCIALTRANSACTION.ID = @REVENUEID

  declare REVENUESPLIT_CURSOR cursor local fast_forward for
    select 
      FINANCIALTRANSACTIONLINEITEM.ID,
      REVENUESPLIT_EXT.APPLICATIONCODE,
      FINANCIALTRANSACTIONLINEITEM.BASEAMOUNT,
      REVENUESPLIT_EXT.DESIGNATIONID
    from
      dbo.FINANCIALTRANSACTIONLINEITEM
    inner join
      dbo.REVENUESPLIT_EXT on REVENUESPLIT_EXT.ID = FINANCIALTRANSACTIONLINEITEM.ID
    where
      FINANCIALTRANSACTIONLINEITEM.FINANCIALTRANSACTIONID = @REVENUEID and
      REVENUESPLIT_EXT.OVERRIDEBUSINESSUNITS = 0 and
      FINANCIALTRANSACTIONLINEITEM.DELETEDON is null;

    open REVENUESPLIT_CURSOR;
    begin try
        fetch next from REVENUESPLIT_CURSOR into
          @REVENUESPLITID,
          @APPLICATIONCODE,
          @SPLITAMOUNT,
          @DESIGNATIONID;
        while (@@FETCH_STATUS = 0)
        begin

      declare @BU_APPLIED bit=0; --If flag is set, no further processing is required for this split.


      --Payment to a commitment(RG, pledge), look at business units on the commitment      

      if @TRANSACTIONTYPECODE=0 and (@APPLICATIONCODE=2 or @APPLICATIONCODE=3) -- Only handling donation, RG's and pledges right now

      begin
        declare @COMMITMENTID uniqueidentifier;

        if @APPLICATIONCODE=2
        begin
          select
            @COMMITMENTID = INSTALLMENTSPLITPAYMENT.PLEDGEID
          from dbo.INSTALLMENTSPLITPAYMENT
          inner join dbo.FINANCIALTRANSACTION on FINANCIALTRANSACTION.ID = INSTALLMENTSPLITPAYMENT.PLEDGEID
          inner join dbo.REVENUE_EXT on FINANCIALTRANSACTION.ID = REVENUE_EXT.ID
          where INSTALLMENTSPLITPAYMENT.PAYMENTID = @REVENUESPLITID
            and FINANCIALTRANSACTION.DELETEDON is null
        end

        if @APPLICATIONCODE=3
        begin
          select
            @COMMITMENTID = SOURCEREVENUEID
          from dbo.RECURRINGGIFTACTIVITY
          inner join dbo.FINANCIALTRANSACTION on FINANCIALTRANSACTION.ID = SOURCEREVENUEID
          inner join dbo.REVENUE_EXT on FINANCIALTRANSACTION.ID = REVENUE_EXT.ID
          where PAYMENTREVENUEID = @REVENUESPLITID
            and FINANCIALTRANSACTION.DELETEDON is null
        end

        delete from dbo.REVENUESPLITBUSINESSUNIT where REVENUESPLITBUSINESSUNIT.REVENUESPLITID = @REVENUESPLITID

        insert into dbo.REVENUESPLITBUSINESSUNIT
        (
          ID,
          REVENUESPLITID,
          BUSINESSUNITCODEID,
          AMOUNT,
          BASECURRENCYID,
          ADDEDBYID,
          CHANGEDBYID,
          DATEADDED,
          DATECHANGED
        )
        select
          newid(),
          @REVENUESPLITID,
          BUSINESSUNITCODEID,
          case REVENUESPLIT_EXT.OVERRIDEBUSINESSUNITS
          when 0 then
            case when FINANCIALTRANSACTIONLINEITEM.BASEAMOUNT = 0 
                then 0
                else @SPLITAMOUNT * (RSB.AMOUNT/FINANCIALTRANSACTIONLINEITEM.BASEAMOUNT)
            end
          else @SPLITAMOUNT * (RSB.AMOUNT/FINANCIALTRANSACTIONLINEITEM.BASEAMOUNT) end,
          @BASECURRENCYID,
          @CHANGEAGENTID,
          @CHANGEAGENTID,
          @CHANGEDATE,
          @CHANGEDATE
        from dbo.REVENUESPLITBUSINESSUNIT RSB
        inner join dbo.FINANCIALTRANSACTIONLINEITEM on FINANCIALTRANSACTIONLINEITEM.ID = RSB.REVENUESPLITID
        inner join dbo.REVENUESPLIT_EXT on FINANCIALTRANSACTIONLINEITEM.ID = REVENUESPLIT_EXT.ID
        inner join dbo.FINANCIALTRANSACTION on FINANCIALTRANSACTIONLINEITEM.FINANCIALTRANSACTIONID = FINANCIALTRANSACTION.ID
        inner join dbo.REVENUE_EXT on FINANCIALTRANSACTION.ID = REVENUE_EXT.ID
        where FINANCIALTRANSACTIONLINEITEM.FINANCIALTRANSACTIONID = @COMMITMENTID and REVENUESPLIT_EXT.DESIGNATIONID = @DESIGNATIONID
            and FINANCIALTRANSACTION.DELETEDON is null
            and FINANCIALTRANSACTIONLINEITEM.DELETEDON is null
            and FINANCIALTRANSACTIONLINEITEM.TYPECODE <> 1

        if (select count(*) from REVENUESPLITBUSINESSUNIT where REVENUESPLITID=@REVENUESPLITID)>0
         set @BU_APPLIED=1          
      end

      --Solicitor

      if @BU_APPLIED=0
        begin
          declare @SOLICITORSBUSINESSUNITS table (BUSINESSUNITCODEID uniqueidentifier,AMOUNT money)

          insert into @SOLICITORSBUSINESSUNITS
          select 
          distinct BUSINESSUNITCODEID,
          case when SUM(REVENUESOLICITOR.AMOUNT) < @SPLITAMOUNT then
              SUM(REVENUESOLICITOR.AMOUNT)
          else
              @SPLITAMOUNT
          end as AMOUNT
          from dbo.REVENUESOLICITOR
          inner join dbo.FINANCIALTRANSACTIONLINEITEM on FINANCIALTRANSACTIONLINEITEM.ID = REVENUESOLICITOR.REVENUESPLITID
          inner join dbo.REVENUESPLIT_EXT on FINANCIALTRANSACTIONLINEITEM.ID = REVENUESPLIT_EXT.ID
          where FINANCIALTRANSACTIONLINEITEM.ID = @REVENUESPLITID  and BUSINESSUNITCODEID is not null
            and FINANCIALTRANSACTIONLINEITEM.DELETEDON is null
            and FINANCIALTRANSACTIONLINEITEM.TYPECODE <> 1
          group by BUSINESSUNITCODEID

          if exists(select BUSINESSUNITCODEID from @SOLICITORSBUSINESSUNITS where BUSINESSUNITCODEID is not null)
          begin
            delete from dbo.REVENUESPLITBUSINESSUNIT where REVENUESPLITBUSINESSUNIT.REVENUESPLITID = @REVENUESPLITID

            insert into dbo.REVENUESPLITBUSINESSUNIT
            (
              ID,
              REVENUESPLITID,
              BUSINESSUNITCODEID,
              AMOUNT,
              BASECURRENCYID,
              ADDEDBYID,
              CHANGEDBYID,
              DATEADDED,
              DATECHANGED
            )
            select
              newid(),
              @REVENUESPLITID,
              BUSINESSUNITCODEID,
              AMOUNT,
              @BASECURRENCYID,
              @CHANGEAGENTID,
              @CHANGEAGENTID,
              @CHANGEDATE,
              @CHANGEDATE
            from @SOLICITORSBUSINESSUNITS

            delete from @SOLICITORSBUSINESSUNITS
            set @BU_APPLIED=1;
          end
      end 

      declare @MAILINGID uniqueidentifier;
      declare @SOURCECODE nvarchar(max);
      declare @APPEALID uniqueidentifier;
      declare @SEGMENTID uniqueidentifier;
      declare @TESTSEGMENTID uniqueidentifier;

      if @BU_APPLIED=0
      begin
         --Set the DM fields here correctly.

         exec dbo.USP_REVENUESPLITBUSINESSUNIT_GETMARKETINGFIELDS
         @REVENUESPLITID,
         @TESTSEGMENTID output,
         @SEGMENTID output,
         @MAILINGID output,
         @APPEALID output         
      end

      --Test Segment

      if @BU_APPLIED=0 and @TESTSEGMENTID is not null
      begin
        if (select OVERRIDEBUSINESSUNITS from dbo.MKTSEGMENTATIONTESTSEGMENT where ID=@TESTSEGMENTID) =1 
        begin
          delete from dbo.REVENUESPLITBUSINESSUNIT where REVENUESPLITBUSINESSUNIT.REVENUESPLITID = @REVENUESPLITID

          insert into dbo.REVENUESPLITBUSINESSUNIT
          (
            ID,
            REVENUESPLITID,
            BUSINESSUNITCODEID,
            AMOUNT,
            BASECURRENCYID,
            ADDEDBYID,
            CHANGEDBYID,
            DATEADDED,
            DATECHANGED
          )
          select
            newid(),
            @REVENUESPLITID,
            BUSINESSUNITCODEID,
            @SPLITAMOUNT * (PERCENTVALUE/100),
            @BASECURRENCYID,
            @CHANGEAGENTID,
            @CHANGEAGENTID,
            @CHANGEDATE,
            @CHANGEDATE
          from dbo.UFN_MKTSEGMENTATIONTESTSEGMENTBUSINESSUNIT_GETBUSINESSUNITS(@TESTSEGMENTID)
          set @BU_APPLIED=1
        end
      end

      --Segment

      if @BU_APPLIED=0 and @SEGMENTID is not null
      begin
        if (select OVERRIDEBUSINESSUNITS from dbo.MKTSEGMENTATIONSEGMENT where ID=@SEGMENTID) =1
        begin
          delete from dbo.REVENUESPLITBUSINESSUNIT where REVENUESPLITBUSINESSUNIT.REVENUESPLITID = @REVENUESPLITID

          insert into dbo.REVENUESPLITBUSINESSUNIT
          (
            ID,
            REVENUESPLITID,
            BUSINESSUNITCODEID,
            AMOUNT,
            BASECURRENCYID,
            ADDEDBYID,
            CHANGEDBYID,
            DATEADDED,
            DATECHANGED
          )
          select
            newid(),
            @REVENUESPLITID,
            BUSINESSUNITCODEID,
            @SPLITAMOUNT * (PERCENTVALUE/100),
            @BASECURRENCYID,
            @CHANGEAGENTID,
            @CHANGEAGENTID,
            @CHANGEDATE,
            @CHANGEDATE
          from dbo.UFN_MKTSEGMENTATIONSEGMENTBUSINESSUNIT_GETBUSINESSUNITS(@SEGMENTID)
          set @BU_APPLIED=1
        end
      end

      --Effort

      if @BU_APPLIED=0 and @MAILINGID is not null
      begin
        if (select OVERRIDEBUSINESSUNITS from dbo.MKTSEGMENTATION where ID=@MAILINGID) =1
        begin
          delete from dbo.REVENUESPLITBUSINESSUNIT where REVENUESPLITBUSINESSUNIT.REVENUESPLITID = @REVENUESPLITID

          insert into dbo.REVENUESPLITBUSINESSUNIT
          (
            ID,
            REVENUESPLITID,
            BUSINESSUNITCODEID,
            AMOUNT,
            BASECURRENCYID,
            ADDEDBYID,
            CHANGEDBYID,
            DATEADDED,
            DATECHANGED
          )
          select
            newid(),
            @REVENUESPLITID,
            BUSINESSUNITCODEID,
            @SPLITAMOUNT * (PERCENTVALUE/100),
            @BASECURRENCYID,
            @CHANGEAGENTID,
            @CHANGEAGENTID,
            @CHANGEDATE,
            @CHANGEDATE
          from dbo.UFN_MKTSEGMENTATIONBUSINESSUNIT_GETBUSINESSUNITS(@MAILINGID)
          set @BU_APPLIED=1
        end
      end

      --Appeal

      if @BU_APPLIED=0 and @APPEALID is not null
      begin
        if (select count(*) from dbo.UFN_APPEALBUSINESSUNIT_GETBUSINESSUNITS(@APPEALID))>0
        begin
          delete from dbo.REVENUESPLITBUSINESSUNIT where REVENUESPLITBUSINESSUNIT.REVENUESPLITID = @REVENUESPLITID

          insert into dbo.REVENUESPLITBUSINESSUNIT
          (
            ID,
            REVENUESPLITID,
            BUSINESSUNITCODEID,
            AMOUNT,
            BASECURRENCYID,
            ADDEDBYID,
            CHANGEDBYID,
            DATEADDED,
            DATECHANGED
          )
          select
            newid(),
            @REVENUESPLITID,
            BUSINESSUNITCODEID,
            @SPLITAMOUNT * (PERCENTVALUE/100),
            @BASECURRENCYID,
            @CHANGEAGENTID,
            @CHANGEAGENTID,
            @CHANGEDATE,
            @CHANGEDATE
          from dbo.UFN_APPEALBUSINESSUNIT_GETBUSINESSUNITS(@APPEALID)
          set @BU_APPLIED=1
        end
      end

      fetch next from REVENUESPLIT_CURSOR into
        @REVENUESPLITID,
        @APPLICATIONCODE,
        @SPLITAMOUNT,
        @DESIGNATIONID;

        end
        close REVENUESPLIT_CURSOR;
        deallocate REVENUESPLIT_CURSOR;

      if not @CONTEXTCACHE is null
        set context_info @CONTEXTCACHE;

    end try
    begin catch
      close REVENUESPLIT_CURSOR;
      deallocate REVENUESPLIT_CURSOR;
      exec dbo.USP_RAISE_ERROR;
      return 1;
    end catch

end