USP_DATAFORMTEMPLATE_EDIT_PLANNEDGIFTREVENUEADJUST

CREATE procedure dbo.USP_DATAFORMTEMPLATE_EDIT_PLANNEDGIFTREVENUEADJUST
(
  @ID uniqueidentifier,
  @CHANGEAGENTID uniqueidentifier = null,
  @DATE datetime,
  @AMOUNT money,
  @POSTSTATUSCODE tinyint,
  @POSTDATE datetime,
  @SPLITS xml,
  @SOURCECODE nvarchar(50),
  @APPEALID uniqueidentifier,
  @BENEFITS xml,
  @BENEFITSWAIVED bit,
  @GIVENANONYMOUSLY bit,
  @MAILINGID uniqueidentifier,
  @CHANNELCODEID uniqueidentifier,
  @DONOTACKNOWLEDGE bit,
  @PLEDGESUBTYPEID uniqueidentifier,
  @OPPORTUNITYID uniqueidentifier,
  @REFERENCE nvarchar(255),
  @CATEGORYCODEID uniqueidentifier,
  @PERCENTAGEBENEFITS xml
)
as
begin
  set nocount on;

  declare @CURRENTDATE datetime;

  declare @contextCache varbinary(128);

  --cache current context information

  set @contextCache = CONTEXT_INFO();

  --set CONTEXT_INFO to @CHANGEAGENTID

  set CONTEXT_INFO @CHANGEAGENTID;

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

    set @CURRENTDATE = GetDate();

    exec dbo.USP_REVENUESPLIT_VALIDATESPLITS @SPLITS, @AMOUNT, 1;

    declare @TRIBUTEAMOUNT money;
    select @TRIBUTEAMOUNT = sum(AMOUNT) from dbo.REVENUETRIBUTE where REVENUEID = @ID;

    -- do not allow the gift amount to be adjusted less than the applied tribute amount

    if (@TRIBUTEAMOUNT is not null) and (@AMOUNT < @TRIBUTEAMOUNT)
    begin
      raiserror('The planned gift amount cannot be less than the sum of the tribute amounts applied to this planned gift.', 13, 1)
    end

    declare @DONOTPOST bit;
    set @DONOTPOST = case @POSTSTATUSCODE when 2 then 1 else 0 end;

    /* RSC - 10/17/08 - Commenting out post to GL code until we decide on a strategy for posting planned gifts. */
    --declare @CLEARGLDISTRIBUTION bit;

    --set @CLEARGLDISTRIBUTION = 0;


    -- check to see if designations have changed

    --if dbo.UFN_CHECKDETAIL_SPLITSCHANGED(@ID, @SPLITS) = 1

    --begin

    --    set @CLEARGLDISTRIBUTION = 1;

    --end    


    --declare @OLDCATEGORYCODEID uniqueidentifier

    --select top 1 @OLDCATEGORYCODEID = REVENUECATEGORY.GLREVENUECATEGORYMAPPINGID

    --from dbo.REVENUECATEGORY

    --inner join dbo.REVENUESPLIT on REVENUECATEGORY.ID = REVENUESPLIT.ID

    --where REVENUESPLIT.REVENUEID = @ID


    -- check to see if amount has changed

    --if @CLEARGLDISTRIBUTION = 0

    --begin

    --if (select AMOUNT from dbo.REVENUE where ID = @ID) <> @AMOUNT or 

    --not ((@CATEGORYCODEID is null and @OLDCATEGORYCODEID is null) and (@CATEGORYCODEID = @OLDCATEGORYCODEID))

    --  begin

    --    set @CLEARGLDISTRIBUTION = 1;

    --  end

    --end


-- check to see if the revenue record needs to be re-acknowledged

    if (coalesce((select top 1 REACKNOWLEDGEREVENUE from dbo.ACKNOWLEDGEMENTPREFERENCE), 0)) = 1
    begin
      declare @FIELDCHANGED bit;    
      set @FIELDCHANGED = 0;            

      -- check to see if amount have changed

      if (select count(REVENUE.ID) from dbo.REVENUE where REVENUE.ID = @ID and AMOUNT = @AMOUNT) = 0
        set @FIELDCHANGED = 1;

      -- check to see if designations have changed

      if @FIELDCHANGED = 0
        if dbo.UFN_CHECKDETAIL_SPLITSCHANGED(@ID, @SPLITS) = 1
          set @FIELDCHANGED = 1;

      -- if a field has changed, mark the revenue letters for this record out of date, if necessary

      if @FIELDCHANGED = 1
        exec dbo.USP_REVENUELETTER_SETREACKNOWLEDGEMENTS @ID, @CHANGEAGENTID;     

    end

    update
      dbo.REVENUE
    set
      DATE = @DATE,
      DONOTPOST = @DONOTPOST,
      POSTDATE = @POSTDATE,
      AMOUNT = @AMOUNT,
      SOURCECODE = @SOURCECODE,
      APPEALID = @APPEALID,
      BENEFITSWAIVED = @BENEFITSWAIVED,
      GIVENANONYMOUSLY = @GIVENANONYMOUSLY,
      MAILINGID = @MAILINGID,
      CHANNELCODEID = @CHANNELCODEID,
      DONOTACKNOWLEDGE = @DONOTACKNOWLEDGE,
      CHANGEDBYID = @CHANGEAGENTID,
      DATECHANGED = @CURRENTDATE
    where
      ID = @ID;

    update 
      dbo.INSTALLMENT
    set 
      AMOUNT = @AMOUNT,
      DATE = @DATE,
      CHANGEDBYID = @CHANGEAGENTID,
      DATECHANGED = @CURRENTDATE
    where 
      REVENUEID = @ID;

    declare @SPLITSCHANGED bit
    set @SPLITSCHANGED = dbo.UFN_CHECKDETAIL_SPLITSCHANGED(@ID, @SPLITS)

    exec dbo.USP_REVENUE_GETSPLITS_UPDATEFROMXML @ID, @SPLITS, @CHANGEAGENTID, @CURRENTDATE;

    -- update benefits

    declare @TOTALBENEFITS xml;
    set @TOTALBENEFITS = dbo.UFN_REVENUEDETAIL_JOINBENEFITS(@BENEFITS, @PERCENTAGEBENEFITS);
    exec dbo.USP_REVENUE_GETBENEFITS2_UPDATEFROMXML @ID, @TOTALBENEFITS, @CHANGEAGENTID, @CURRENTDATE;

    exec dbo.USP_REVENUE_UPDATESOLICITORS @ID, @CHANGEAGENTID, @CURRENTDATE;

    if @SPLITSCHANGED = 1
    begin
      --assume one installment

      declare @INSTALLMENTSPLITS xml;
      set @INSTALLMENTSPLITS = (
        select distinct
          INSTALLMENTSPLIT.ID, 
          INSTALLMENT.ID INSTALLMENTID, 
          REVENUESPLIT.DESIGNATIONID, 
          REVENUESPLIT.AMOUNT,
          REVENUESPLIT.ID as REVENUESPLITID
        from 
          dbo.REVENUESPLIT
        inner join 
          dbo.INSTALLMENT on INSTALLMENT.REVENUEID = REVENUESPLIT.REVENUEID
        left outer join 
          dbo.INSTALLMENTSPLIT on REVENUESPLIT.ID = INSTALLMENTSPLIT.REVENUESPLITID
        where 
          REVENUESPLIT.REVENUEID = @ID
        for xml raw('ITEM'),type,elements,root('INSTALLMENTSPLITS'),BINARY BASE64
      );

      exec dbo.USP_PLEDGE_GETINSTALLMENTSPLITS_UPDATEFROMXML @ID, @INSTALLMENTSPLITS, @CHANGEAGENTID, @CURRENTDATE;
    end

    exec dbo.USP_REVENUEREFERENCE_EDIT @ID, @REFERENCE, @CHANGEAGENTID

    exec dbo.USP_REVENUECATEGORY_UPDATESPLITS @ID, @CATEGORYCODEID, @CHANGEAGENTID, @CURRENTDATE

    if @OPPORTUNITYID is null
      delete REVENUEOPPORTUNITY
      from dbo.REVENUEOPPORTUNITY 
      inner join dbo.REVENUESPLIT
        on REVENUESPLIT.ID = REVENUEOPPORTUNITY.ID
      where REVENUESPLIT.REVENUEID = @ID
    else begin
      exec dbo.USP_REVENUE_PULLSOLICITORSFROMOPPORTUNITY @ID, @OPPORTUNITYID, @CHANGEAGENTID, @CURRENTDATE

      insert into dbo.REVENUEOPPORTUNITY (ID, OPPORTUNITYID, ADDEDBYID, CHANGEDBYID, DATEADDED, DATECHANGED)
        select ID, @OPPORTUNITYID, @CHANGEAGENTID, @CHANGEAGENTID, @CURRENTDATE, @CURRENTDATE
        from dbo.REVENUESPLIT where REVENUEID = @ID
        and not exists(select top 1 REVENUEOPPORTUNITY.ID from REVENUEOPPORTUNITY where REVENUEOPPORTUNITY.ID = REVENUESPLIT.ID);

      exec dbo.USP_OPPORTUNITY_UPDATEACCEPTEDSTATUS @OPPORTUNITYID, @DATE, @CHANGEAGENTID, @CURRENTDATE
    end


    -- clear the user-defined gl distributions

    --if @CLEARGLDISTRIBUTION = 1

    --begin

      --Clear GL

    --delete from dbo.REVENUEGLDISTRIBUTION where REVENUEID = @ID and OUTDATED = 0;


      -- Add new GL distributions

    --if @POSTSTATUSCODE <> 2

    --  exec dbo.USP_SAVE_REVENUEGLDISTRIBUTION @ID, @CHANGEAGENTID, @CURRENTDATE;

    --end


    if dbo.UFN_PLEDGEPAYMENT_DESIGNATIONSBALANCE(@ID) = 0
      raiserror('PLEDGEPAYMENT_DESIGNATIONSBALANCE', 13, 10);

  end try

  begin catch
    --reset CONTEXT_INFO to previous value

    if not @contextCache is null
      set CONTEXT_INFO @contextCache;


    exec dbo.USP_RAISE_ERROR;
    return 1;
  end catch

  --reset CONTEXT_INFO to previous value

  if not @contextCache is null
    set CONTEXT_INFO @contextCache;

  return 0;
end