USP_DATAFORMTEMPLATE_EDIT_POSTEDGRANTAWARD_4

CREATE procedure dbo.USP_DATAFORMTEMPLATE_EDIT_POSTEDGRANTAWARD_4 (
  @ID uniqueidentifier
  ,@CHANGEAGENTID uniqueidentifier = null
  ,@DATE datetime
  ,@AMOUNT money
  ,@POSTSTATUSCODE tinyint
  ,@POSTDATE datetime
  ,@SPLITS xml
  ,@FREQUENCYCODE tinyint
  ,@NUMBEROFINSTALLMENTS int
  ,@NEXTTRANSACTIONDATE datetime
  ,@INSTALLMENTS xml
  ,@DONOTACKNOWLEDGE bit
  ,@CATEGORYCODEID uniqueidentifier
  ,@ADJUSTMENTDATE datetime
  ,@ADJUSTMENTPOSTDATE datetime
  ,@ADJUSTMENTREASON nvarchar(300)
  ,@ISREIMBURSABLE bit
  ,@OPPORTUNITYID uniqueidentifier
  ,@BASECURRENCYID uniqueidentifier
  ,@TRANSACTIONCURRENCYID uniqueidentifier
  ,@BASEEXCHANGERATEID uniqueidentifier
  ,@EXCHANGERATE decimal(20, 8)
  ,@HADSPOTRATE bit
  ,@RATECHANGED bit
  ,@ADJUSTMENTREASONCODEID uniqueidentifier
  ,@ADJPAYMENT_DATE datetime
  ,@ADJPAYMENT_POSTDATE datetime
  ,@ADJPAYMENT_REASONCODEID uniqueidentifier
  ,@ADJPAYMENT_DETAILS nvarchar(255)
  ,@UPDATERECOGNITIONOPTION tinyint
  )
as
set nocount on;

declare @EMPTYGUID uniqueidentifier = '00000000-0000-0000-0000-000000000000';
if @CHANGEAGENTID is null
  exec dbo.USP_CHANGEAGENT_GETORCREATECHANGEAGENT @CHANGEAGENTID output;

declare @CURRENTDATE datetime;

set @CURRENTDATE = getdate();

begin try
  declare @SUM money;
  declare @COUNT int;
  declare @ADJUST bit;
  declare @ADJUSTMENTID uniqueidentifier;
  declare @REACKNOWLEDGEFIELDCHANGED bit;
  declare @CLEARGLDISTRIBUTION bit;

  set @ADJUST = 0;
  set @ADJUSTMENTID = null;
  set @CLEARGLDISTRIBUTION = 0;
  set @REACKNOWLEDGEFIELDCHANGED = 0;

  declare @contextCache varbinary(128);
  -- Multicurrency - If the revenue previously used a spot rate, but

  -- its rate has changed, store the old rate's ID, so we can remove it later.

  declare @OLDSPOTRATE uniqueidentifier

  if @HADSPOTRATE = 1
    and @RATECHANGED = 1
  begin
    select @OLDSPOTRATE = BASEEXCHANGERATEID
    from dbo.REVENUE
    where ID = @ID
  end

  --If the record uses a new spot rate, create it and set the rate ID.

  if @BASEEXCHANGERATEID = '00000000-0000-0000-0000-000000000001'
  begin
    set @BASEEXCHANGERATEID = newid()

    insert into dbo.CURRENCYEXCHANGERATE (
      ID
      ,FROMCURRENCYID
      ,TOCURRENCYID
      ,RATE
      ,ASOFDATE
      ,TYPECODE
      ,SOURCECODEID
      ,ADDEDBYID
      ,CHANGEDBYID
      ,DATEADDED
      ,DATECHANGED
      )
    values (
      @BASEEXCHANGERATEID
      ,@TRANSACTIONCURRENCYID
      ,@BASECURRENCYID
      ,@EXCHANGERATE
      ,@DATE
      ,2
      ,null
      ,@CHANGEAGENTID
      ,@CHANGEAGENTID
      ,@CURRENTDATE
      ,@CURRENTDATE
      );
  end

  --Multicurrency - Retrieve and calculate the necessary multicurrency values.                

  declare @BASEAMOUNT money;
  declare @ORGANIZATIONCURRENCYID uniqueidentifier;
  declare @ORGANIZATIONAMOUNT money;
  declare @ORGANIZATIONEXCHANGERATEID uniqueidentifier;

  exec dbo.USP_CURRENCY_GETCURRENCYVALUES @AMOUNT
    ,@DATE
    ,@BASECURRENCYID
    ,@BASEEXCHANGERATEID
    ,@TRANSACTIONCURRENCYID output
    ,@BASEAMOUNT output
    ,@ORGANIZATIONCURRENCYID output
    ,@ORGANIZATIONAMOUNT output
    ,@ORGANIZATIONEXCHANGERATEID output
    ,1;

  exec dbo.USP_REVENUESPLIT_VALIDATESPLITS @SPLITS = @SPLITS
    ,@REVENUEAMOUNT = @AMOUNT
    ,@REVENUEID = @ID
    ,@TRANSACTIONCURRENCYID = @TRANSACTIONCURRENCYID;

  select @SUM = sum(AMOUNT)
    ,@COUNT = count(AMOUNT)
  from dbo.UFN_INSTALLMENT_GETINSTALLMENTS_2_FROMITEMLISTXML(@INSTALLMENTS);

  if @COUNT = 0
    raiserror (
        'INSTALLMENTCOUNT'
        ,13
        ,1
        );

  if @SUM <> @AMOUNT
    raiserror (
        'INSTALLMENTSUM'
        ,13
        ,1
        );

  /* You can only edit posted grant awards here */
  if not exists (
      select 1
      from dbo.REVENUEPOSTED
      where REVENUEPOSTED.ID = @ID
      )
    raiserror (
        'You cannot edit an unposted grant award.'
        ,13
        ,1
        );

  if dbo.UFN_INSTALLMENT_DATESOVERLAP(@INSTALLMENTS) = 1
    raiserror (
        'Installment dates are out of sequence.'
        ,13
        ,1
        );

  if (
      select count(INSTALLMENT.ID)
      from dbo.INSTALLMENT
      inner join dbo.UFN_INSTALLMENT_GETINSTALLMENTS_2_FROMITEMLISTXML(@INSTALLMENTS) XMLINST on INSTALLMENT.ID = XMLINST.ID
      where XMLINST.AMOUNT < XMLINST.APPLIED
      ) > 0
    raiserror (
        'GRANTAWARDPAYMENT_INSTALLMENTAPPLIED'
        ,13
        ,1
        );

    if exists (
        select 1
        from dbo.INSTALLMENT
        inner join dbo.UFN_INSTALLMENT_GETINSTALLMENTS_2_FROMITEMLISTXML(@INSTALLMENTS) XMLINST on INSTALLMENT.ID = XMLINST.ID
        where XMLINST.Date < @DATE
        )
      raiserror (
          'CK_INSTALLMENT_STARTDATEVALID2'
          ,13
          ,1
          );  

  declare @DONOTPOST bit;

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

  /* Check if currency values changed */
  if exists (
      select 1
      from dbo.REVENUE
      where REVENUE.ID = @ID
        and (
          REVENUE.TRANSACTIONAMOUNT <> @AMOUNT
          or REVENUE.AMOUNT <> @BASEAMOUNT
          or REVENUE.ORGANIZATIONAMOUNT <> @ORGANIZATIONAMOUNT
          or REVENUE.BASEEXCHANGERATEID <> @BASEEXCHANGERATEID
          or REVENUE.ORGANIZATIONEXCHANGERATEID <> @ORGANIZATIONEXCHANGERATEID
          or REVENUE.TRANSACTIONCURRENCYID <> @TRANSACTIONCURRENCYID
          or REVENUE.BASECURRENCYID <> @BASECURRENCYID
          )
      )
  begin
    set @ADJUST = 1;
    set @CLEARGLDISTRIBUTION = 1;
  end

  -- check to see if designations have changed

  if dbo.UFN_CHECKDETAIL_SPLITSCHANGED(@ID, @SPLITS) = 1
  begin
    set @ADJUST = 1;
    set @CLEARGLDISTRIBUTION = 1;
    set @REACKNOWLEDGEFIELDCHANGED = 1;
  end

  -- check to see if installments have changed

  if @CLEARGLDISTRIBUTION = 0
  begin
    if dbo.UFN_CHECKDETAIL_INSTALLMENTSCHANGED(@ID, @INSTALLMENTS) = 1
    begin
      set @ADJUST = 1;
      set @CLEARGLDISTRIBUTION = 1;
    end
  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 if revenue category changed */
  if (
      @CATEGORYCODEID is null
      and @OLDCATEGORYCODEID is not null
      )
    or (
      @CATEGORYCODEID is not null
      and @OLDCATEGORYCODEID is null
      )
    or (@CATEGORYCODEID <> @OLDCATEGORYCODEID)
  begin
    set @ADJUST = 1;
    set @CLEARGLDISTRIBUTION = 1;
  end

  /* Already adjusted */
  if @ADJUST = 0
    if (
        select COUNT(ADJUSTMENT.ID)
        from dbo.ADJUSTMENT
        where REVENUEID = @ID
          and POSTSTATUSCODE = 1
        ) > 0
      set @ADJUST = 1

  /* If there was a change to GL related data log an adjustment */
  if @ADJUST = 1
  begin
    if @ADJUSTMENTREASONCODEID is null
      raiserror (
          'BBERR_ADJUSTMENTREASONCODEIDREQUIRED'
          ,13
          ,1
          )

    exec dbo.USP_SAVE_ADJUSTMENT @ID
      ,@ADJUSTMENTID output
      ,@CHANGEAGENTID
      ,@CURRENTDATE
      ,@ADJUSTMENTDATE
      ,@ADJUSTMENTPOSTDATE
      ,@ADJUSTMENTREASON
      ,default
      ,@ADJUSTMENTREASONCODEID;
  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
    -- check to see if amount have changed

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

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

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

  update dbo.REVENUE
  set [DATE] = @DATE
    ,DONOTPOST = @DONOTPOST
    ,POSTDATE = @POSTDATE
    ,AMOUNT = @BASEAMOUNT
    ,DONOTACKNOWLEDGE = @DONOTACKNOWLEDGE
    ,CHANGEDBYID = @CHANGEAGENTID
    ,DATECHANGED = @CURRENTDATE
    ,ISREIMBURSABLE = @ISREIMBURSABLE
    ,BASECURRENCYID = @BASECURRENCYID
    ,ORGANIZATIONAMOUNT = @ORGANIZATIONAMOUNT
    ,TRANSACTIONAMOUNT = @AMOUNT
    ,BASEEXCHANGERATEID = @BASEEXCHANGERATEID
    ,ORGANIZATIONEXCHANGERATEID = @ORGANIZATIONEXCHANGERATEID
  where ID = @ID;

  update dbo.REVENUESCHEDULE
  set FREQUENCYCODE = @FREQUENCYCODE
    ,NUMBEROFINSTALLMENTS = @COUNT
    ,NEXTTRANSACTIONDATE = @NEXTTRANSACTIONDATE
    ,CHANGEDBYID = @CHANGEAGENTID
    ,DATECHANGED = @CURRENTDATE
  from dbo.REVENUESCHEDULE
  where REVENUESCHEDULE.ID = @ID;

  -- Cache the old split and recognition values for recognition updates

  declare @OLDSPLITS xml = dbo.UFN_REVENUE_GETSPLITS_2_TOITEMLISTXML(@ID);
  declare @OLDRECOGNITIONS xml = dbo.UFN_REVENUE_GETRECOGNITIONS_FORREVENUE_TOITEMLISTXML(@ID);

  declare @SPLITSCHANGED bit

  set @SPLITSCHANGED = dbo.UFN_CHECKDETAIL_SPLITSCHANGED(@ID, @SPLITS)
  -- Multicurrency - Process the splits xml to calculate the base and organization amounts and place them in proper nodes.

  set @SPLITS = dbo.UFN_REVENUESPLIT_CONVERTAMOUNTSINXML(@SPLITS, @BASECURRENCYID, @ORGANIZATIONEXCHANGERATEID, @TRANSACTIONCURRENCYID, @BASEEXCHANGERATEID);

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

  -- Update recognition credits based on user selected option

  exec dbo.USP_REVENUE_UPDATERECOGNITION @ID
    ,@OLDSPLITS
    ,@UPDATERECOGNITIONOPTION
    ,@CHANGEAGENTID
    ,@CURRENTDATE
    ,@OLDRECOGNITIONS;

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

  -- Multicurrency - Pass in multicurrency information so we can convert the amount in the xml 

  -- once installment splits are pulled out of their installments.

    -- writeoffs without a payment will use pledge reason codes so that reversals fire correctly

    declare @ADJ_DATE datetime = ISNULL(@ADJPAYMENT_DATE,@ADJUSTMENTDATE);
    declare @ADJ_POSTDATE datetime = ISNULL(@ADJPAYMENT_POSTDATE,@ADJUSTMENTPOSTDATE);
    declare @ADJ_REASONCODEID uniqueidentifier = ISNULL(NULLIF(@ADJPAYMENT_REASONCODEID,@EMPTYGUID),@ADJUSTMENTREASONCODEID); 
    declare @ADJ_REASON nvarchar(300) = ISNULL(@ADJPAYMENT_DETAILS,@ADJUSTMENTREASON);  

  exec dbo.USP_PLEDGE_UPDATEINSTALLMENT3 @ID
    ,@INSTALLMENTS
    ,@CHANGEAGENTID
    ,@CURRENTDATE
    ,@ADJ_DATE
    ,@ADJ_POSTDATE
    ,@ADJ_REASONCODEID
    ,@ADJ_REASON
    ,@BASECURRENCYID
    ,@ORGANIZATIONEXCHANGERATEID
    ,@TRANSACTIONCURRENCYID
    ,@BASEEXCHANGERATEID;

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

  if (@OPPORTUNITYID is not null)
    and (@OPPORTUNITYID <> '00000000-0000-0000-0000-000000000000')
  begin
    -- Only update if opportunity is newly associated.

    if not exists (
        select REVENUEOPPORTUNITY.ID
        from dbo.REVENUEOPPORTUNITY
        inner join dbo.REVENUESPLIT on REVENUESPLIT.ID = REVENUEOPPORTUNITY.ID
        where REVENUEID = @ID
          and OPPORTUNITYID = @OPPORTUNITYID
        )
    begin
      --Pull in default solicitors before changing the opportunity record.

      exec dbo.USP_REVENUE_PULLSOLICITORSFROMOPPORTUNITY @ID
        ,@OPPORTUNITYID
        ,@CHANGEAGENTID
        ,@CURRENTDATE

      delete REVENUEOPPORTUNITY
      from dbo.REVENUEOPPORTUNITY
      inner join dbo.REVENUESPLIT on REVENUESPLIT.ID = REVENUEOPPORTUNITY.ID
      where REVENUESPLIT.REVENUEID = @ID

      insert into dbo.REVENUEOPPORTUNITY (
        ID
        ,OPPORTUNITYID
        ,ADDEDBYID
        ,CHANGEDBYID
        ,DATEADDED
        ,DATECHANGED
        )
      select ID
        ,@OPPORTUNITYID
        ,@CHANGEAGENTID
        ,@CHANGEAGENTID
        ,@CURRENTDATE
        ,@CURRENTDATE
      from dbo.REVENUESPLIT
      where REVENUEID = @ID

      exec dbo.USP_OPPORTUNITY_UPDATEACCEPTEDSTATUS @OPPORTUNITYID
        ,@DATE
        ,@CHANGEAGENTID
        ,@CURRENTDATE
    end
  end
  else
  begin
    exec dbo.USP_REVENUE_PULLSOLICITORSFROMFUNDINGREQUEST_ONLYNEWSPLITS @SPLITS
      ,@ID
      ,@CHANGEAGENTID
      ,@CURRENTDATE;
  end


  --MMR not sure we still need this?

  --exec dbo.USP_PLEDGE_FIXDEPENDENTSPLITS @ID

    --,@CHANGEAGENTID

    --,@CURRENTDATE;


  /* USP_PLEDGE_UPDATEINSTALLMENT3 has to update the writeoffs, so USP_REVENUE_UPDATEWRITEOFFS should not be called
  exec dbo.USP_REVENUE_UPDATEWRITEOFFS @ID
    ,@CHANGEAGENTID
    ,@CURRENTDATE
    ,@ADJPAYMENT_DATE
    ,@ADJPAYMENT_POSTDATE
    ,@ADJPAYMENT_REASONCODEID
    ,@ADJPAYMENT_DETAILS
    ,@CLEARGLDISTRIBUTION
  */
  -- 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

  exec dbo.USP_PLEDGE_VALIDATE @ID;

  if @SPLITSCHANGED = 1
    exec dbo.USP_PLEDGE_CLEARANDADDCAMPAIGNS @ID
      ,null
      ,@CHANGEAGENTID
      ,@CURRENTDATE;

  if @ADJUST = 1 /*call USP_ADJUSTMENTHISTORY_REVENUE_SAVEHISTORY after the revenue tables are updated */
    exec dbo.USP_ADJUSTMENTHISTORY_REVENUE_SAVEHISTORY @ID
      ,@CHANGEAGENTID
      ,null
      ,@ADJUSTMENTID;

  -- Multicurrency - If we stored an old spot rate earlier, now is the time to remove it.

  if @OLDSPOTRATE is not null
  begin
    --RobertDi 11/23/2010 - The currency exchange rate may be used elsewhere.

    begin try
      delete CURRENCYEXCHANGERATE
      where ID = @OLDSPOTRATE
    end try

    begin catch
    end catch
  end
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;