USP_DATAFORMTEMPLATE_EDIT_GRANTAWARD_4

CREATE procedure dbo.USP_DATAFORMTEMPLATE_EDIT_GRANTAWARD_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
  ,@ISREIMBURSABLE bit
  ,@OPPORTUNITYID uniqueidentifier
  ,@BASECURRENCYID uniqueidentifier
  ,@TRANSACTIONCURRENCYID uniqueidentifier
  ,@BASEEXCHANGERATEID uniqueidentifier
  ,@EXCHANGERATE decimal(20, 8)
  ,@HADSPOTRATE bit
  ,@RATECHANGED bit
  ,@ADJPAYMENT_DATE datetime
  ,@ADJPAYMENT_POSTDATE datetime
  ,@ADJPAYMENT_REASONCODEID uniqueidentifier
  ,@ADJPAYMENT_DETAILS nvarchar(255)
  ,@UPDATERECOGNITIONOPTION tinyint
  )
as
set nocount on;

-- Check GL business rule for this account system and set to 'Do not post' if needed.

-- ****

declare @PDACCOUNTSYSTEMID uniqueidentifier;

select @PDACCOUNTSYSTEMID = PDACCOUNTSYSTEMID
from dbo.PDACCOUNTSYSTEMFORREVENUE
where ID = @ID;

declare @ALLOWGLDISTRIBUTIONS bit;

set @ALLOWGLDISTRIBUTIONS = dbo.UFN_PDACCOUNTSYSTEM_ALLOWGLDISTRIBUTIONS(@PDACCOUNTSYSTEMID);

if @ALLOWGLDISTRIBUTIONS = 0
begin
  set @POSTSTATUSCODE = 2 -- Do not post

  set @POSTDATE = null
end

-- ****                

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 @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

  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 unposted grant awards here */
  if exists (
      select 1
      from dbo.REVENUEPOSTED
      where REVENUEPOSTED.ID = @ID
      )
    raiserror (
        'You cannot edit a posted grant award.'
        ,13
        ,1
        );

  if dbo.UFN_INSTALLMENT_DATESOVERLAP(@INSTALLMENTS) = 1
    raiserror (
        'Installment dates are out of sequence.'
        ,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.AMOUNT < XMLINST.APPLIED
      )
    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;

  declare @REACKNOWLEDGEFIELDCHANGED bit;
  declare @CLEARGLDISTRIBUTION bit;
  declare @CLEARWRITEOFFGLDISTRIBUTION bit;

  set @REACKNOWLEDGEFIELDCHANGED = 0;
  set @CLEARGLDISTRIBUTION = 0;
  set @CLEARWRITEOFFGLDISTRIBUTION = 0;

  -- check to see if designations have changed

  if dbo.UFN_CHECKDETAIL_SPLITSCHANGED(@ID, @SPLITS) = 1
  begin
    set @CLEARGLDISTRIBUTION = 1;
    set @CLEARWRITEOFFGLDISTRIBUTION = 1;
    set @REACKNOWLEDGEFIELDCHANGED = 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 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 @CLEARGLDISTRIBUTION = 1;
    set @CLEARWRITEOFFGLDISTRIBUTION = 1;
  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;

  -- check to see if amounts, postdate, post status, or exchange rates has changed

  if @CLEARGLDISTRIBUTION = 0
    if not exists (
        select 1
        from dbo.REVENUE
        where REVENUE.ID = @ID
          and TRANSACTIONAMOUNT = @AMOUNT
          and POSTDATE = @POSTDATE
          and (
            (
              @POSTSTATUSCODE = 2
              and DONOTPOST = 1
              )
            or (
              @POSTSTATUSCODE = 1
              and DONOTPOST = 0
              )
            )          
          and AMOUNT = @BASEAMOUNT
          and ORGANIZATIONAMOUNT = @ORGANIZATIONAMOUNT
          and ((BASEEXCHANGERATEID = @BASEEXCHANGERATEID) or (BASEEXCHANGERATEID is null and @BASEEXCHANGERATEID is null))
          and ((ORGANIZATIONEXCHANGERATEID = @ORGANIZATIONEXCHANGERATEID) or (ORGANIZATIONEXCHANGERATEID is null and @ORGANIZATIONEXCHANGERATEID is null))
          and TRANSACTIONCURRENCYID = @TRANSACTIONCURRENCYID
          and BASECURRENCYID = @BASECURRENCYID          
        )
    begin
      set @CLEARGLDISTRIBUTION = 1;
    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 not exists (
          select 1
          from dbo.REVENUE
          where REVENUE.ID = @ID
            and TRANSACTIONAMOUNT = @AMOUNT
          )
        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_CUSTOMUPDATEFROMXML @ID
    ,@SPLITS
    ,@CHANGEAGENTID
    ,@CURRENTDATE;

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

  -- Update recognition credits based on user selected option

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

  if (@OPPORTUNITYID is not null)
    and (@OPPORTUNITYID <> '00000000-0000-0000-0000-000000000000')
  begin
    --Pull in default solicitors before changing the opportunity record.

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

    update dbo.REVENUEOPPORTUNITY
    set OPPORTUNITYID = @OPPORTUNITYID
      ,CHANGEDBYID = @CHANGEAGENTID
      ,DATECHANGED = @CURRENTDATE
    where ID in (
        select ID
        from dbo.REVENUESPLIT
        where REVENUEID = @ID
        )

    insert into dbo.REVENUEOPPORTUNITY (
      ID
      ,OPPORTUNITYID
      ,ADDEDBYID
      ,CHANGEDBYID
      ,DATEADDED
      ,DATECHANGED
      )
    select ID
      ,@OPPORTUNITYID
      ,@CHANGEAGENTID
      ,@CHANGEAGENTID
      ,@CURRENTDATE
      ,@CURRENTDATE
    from (
      select REVENUESPLIT.ID [ID]
      from dbo.REVENUESPLIT
      left join dbo.REVENUEOPPORTUNITY on REVENUEOPPORTUNITY.ID = REVENUESPLIT.ID
      where REVENUEID = @ID
        and REVENUEOPPORTUNITY.ID is null
      ) [NEWSPLITS];

    exec dbo.USP_OPPORTUNITY_UPDATEACCEPTEDSTATUS @OPPORTUNITYID
      ,@DATE
      ,@CHANGEAGENTID
      ,@CURRENTDATE
  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;

  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.

  exec dbo.USP_PLEDGE_UPDATEINSTALLMENT3 @ID
    ,@INSTALLMENTS
    ,@CHANGEAGENTID
    ,@CURRENTDATE
    ,@ADJPAYMENT_DATE
    ,@ADJPAYMENT_POSTDATE
    ,@ADJPAYMENT_REASONCODEID
    ,@ADJPAYMENT_DETAILS
    ,@BASECURRENCYID
    ,@ORGANIZATIONEXCHANGERATEID
    ,@TRANSACTIONCURRENCYID
    ,@BASEEXCHANGERATEID;

  /* USP_PLEDGE_UPDATEINSTALLMENT3 has to update the writeoffs, so USP_REVENUE_UPDATEWRITEOFFS should not be called
  exec dbo.USP_REVENUE_UPDATEWRITEOFFS @ID
    ,@CHANGEAGENTID
    ,@CURRENTDATE
    ,null
    ,null
    ,null
    ,null
    ,@CLEARWRITEOFFGLDISTRIBUTION
  */
  -- 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;

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

  if @OLDSPOTRATE is not null
  begin
    delete CURRENCYEXCHANGERATE
    where ID = @OLDSPOTRATE;
  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;