USP_SAVE_ADJUSTMENT

CREATE procedure dbo.USP_SAVE_ADJUSTMENT (
  @REVENUEID uniqueidentifier = null
  ,@ADJUSTMENTID uniqueidentifier = null output
  ,@CHANGEAGENTID uniqueidentifier = null
  ,@CHANGEDATE datetime = null
  ,@DATE datetime = null
  ,@POSTDATE datetime = null
  ,@ADJUSTMENTREASON nvarchar(300) = null
  ,@ISNEWREVENUE bit = 0
  ,@ADJUSTMENTREASONCODEID uniqueidentifier = null
  ,@POSTSTATUSCODE tinyint = 1
  ,@ADJUSTMENTCODE tinyint = 0
  )
  with execute as owner
as
/*call this procedure before making changes to the revenue tables.*/
set nocount on;

declare @REVENUEPOSTDATE datetime;
declare @CONSTITUENTID uniqueidentifier;

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

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

select @ADJUSTMENTID = ID
from dbo.ADJUSTMENT
where REVENUEID = @REVENUEID
  and POSTSTATUSCODE <> 0

if @ADJUSTMENTID is null
  set @ADJUSTMENTID = newid();

--JamesWill WI197067 2012-03-08 Ensure that the date used does not have a timestamp

if not @DATE is null
  set @DATE = dbo.UFN_DATE_GETEARLIESTTIME(@DATE);

--Is the revenue posted?

/*
            if (select count(REVENUE.ID) from dbo.REVENUE
                inner join dbo.REVENUEPOSTED on REVENUEPOSTED.ID = REVENUE.ID
                where REVENUE.ID = @REVENUEID) = 0*/
if (
    select POSTSTATUSCODE
    from dbo.FINANCIALTRANSACTION
    where ID = @REVENUEID
    ) != 2
  raiserror (
      'You cannot adjust an unposted gift'
      ,13
      ,1
      )

--kwb This sp checks to see if it's the original pledge record, ie it hasn't been adjusted yet.

--If yes, create a row in FINANCIALTRANSACTIONLINEITEMADJUSTMENT to store the current ConstituentID

exec dbo.USP_CREATEINITIALADJUSTMENTHISTORY @REVENUEID
  ,@CHANGEAGENTID

select @CONSTITUENTID = CONSTITUENTID
from dbo.FINANCIALTRANSACTION
where ID = @REVENUEID

--Update unposted adjustment if existing

if @ADJUSTMENTID is not null
  and exists (
    select 1
    from dbo.FINANCIALTRANSACTIONLINEITEMADJUSTMENT
    where ID = @ADJUSTMENTID
    )
  update dbo.FINANCIALTRANSACTIONLINEITEMADJUSTMENT
  set ADJUSTMENTREASONCODEID = @ADJUSTMENTREASONCODEID
    ,REASON = @ADJUSTMENTREASON
    ,CONSTITUENTID = @CONSTITUENTID
    ,[DATE] = @DATE
    ,CHANGEDBYID = @CHANGEAGENTID
    ,DATECHANGED = @CHANGEDATE
  where ID = @ADJUSTMENTID;
else
  if @ADJUSTMENTID is not null
    insert into dbo.FINANCIALTRANSACTIONLINEITEMADJUSTMENT (
      ID
      ,ADJUSTMENTREASONCODEID
      ,REASON
      ,CONSTITUENTID
      ,[DATE]
      ,ADDEDBYID
      ,CHANGEDBYID
      ,DATEADDED
      ,DATECHANGED
      )
    values (
      @ADJUSTMENTID
      ,@ADJUSTMENTREASONCODEID
      ,@ADJUSTMENTREASON
      ,@CONSTITUENTID
      ,@DATE
      ,@CHANGEAGENTID
      ,@CHANGEAGENTID
      ,@CHANGEDATE
      ,@CHANGEDATE
      );

update dbo.ADJUSTMENT
set [DATE] = @DATE
  ,POSTDATE = @POSTDATE
  ,REASON = @ADJUSTMENTREASON
  ,REASONCODEID = @ADJUSTMENTREASONCODEID
  ,CHANGEDBYID = @CHANGEAGENTID
  ,DATECHANGED = @CHANGEDATE
  ,POSTSTATUSCODE = @POSTSTATUSCODE
  ,ADJUSTMENTCODE = case when ADJUSTMENTCODE = 0 then @ADJUSTMENTCODE else ADJUSTMENTCODE end
where ID = @ADJUSTMENTID;

if @@ROWCOUNT = 0
begin
  if @POSTSTATUSCODE = 0
    set @POSTSTATUSCODE = 1

  --Log Adjustment if new

  insert into dbo.ADJUSTMENT (
    ID
    ,REVENUEID
    ,PREVIOUSAMOUNT
    ,[DATE]
    ,POSTDATE
    ,POSTSTATUSCODE
    ,REASON
    ,REASONCODEID
    ,ADJUSTMENTCODE
    ,ADDEDBYID
    ,CHANGEDBYID
    ,DATEADDED
    ,DATECHANGED
    ,BASECURRENCYID
    ,TRANSACTIONCURRENCYID
    ,TRANSACTIONPREVIOUSAMOUNT
    ,BASEEXCHANGERATEID
    ,ORGANIZATIONPREVIOUSAMOUNT
    ,ORGANIZATIONEXCHANGERATEID
    )
  select @ADJUSTMENTID
    ,FINANCIALTRANSACTION.ID
    ,FINANCIALTRANSACTION.BASEAMOUNT
    ,@DATE
    ,@POSTDATE
    ,@POSTSTATUSCODE
    ,@ADJUSTMENTREASON
    ,@ADJUSTMENTREASONCODEID
    ,@ADJUSTMENTCODE
    ,@CHANGEAGENTID
    ,@CHANGEAGENTID
    ,@CHANGEDATE
    ,@CHANGEDATE
    ,CURRENCYSET.BASECURRENCYID
    ,FINANCIALTRANSACTION.TRANSACTIONCURRENCYID
    ,FINANCIALTRANSACTION.TRANSACTIONAMOUNT
    ,FINANCIALTRANSACTION.BASEEXCHANGERATEID
    ,FINANCIALTRANSACTION.ORGAMOUNT
    ,FINANCIALTRANSACTION.ORGEXCHANGERATEID
  from dbo.FINANCIALTRANSACTION
  inner join dbo.PDACCOUNTSYSTEM on FINANCIALTRANSACTION.PDACCOUNTSYSTEMID = PDACCOUNTSYSTEM.ID
  inner join dbo.CURRENCYSET on PDACCOUNTSYSTEM.CURRENCYSETID = CURRENCYSET.ID
  where FINANCIALTRANSACTION.ID = @REVENUEID;

  --Save the snapshot before deleting the GL information

  exec dbo.USP_ADJUSTMENTHISTORY_REVENUE_SAVESNAPSHOT @ADJUSTMENTID
    ,@CHANGEAGENTID
    ,@ISNEWREVENUE;

  --Log reversals in the GLTRANSACTION table                work item 55993 - added postdate 

  exec dbo.USP_GLTRANSACTION_ADDREVENUEREVERSALS @REVENUEID
    ,@CHANGEAGENTID
    ,@CHANGEDATE
    ,@POSTDATE;
end
else /*make sure @ADJUSTMENTID gets set to the correct value*/
begin

    update dbo.FINANCIALTRANSACTIONLINEITEM set
        POSTDATE = @POSTDATE
        ,POSTSTATUSCODE = case @POSTSTATUSCODE when 2 then 3 else 1 end
        ,DATECHANGED = @CHANGEDATE
        ,CHANGEDBYID = @CHANGEAGENTID
    where FINANCIALTRANSACTIONLINEITEM.FINANCIALTRANSACTIONID = @REVENUEID and FINANCIALTRANSACTIONLINEITEM.POSTSTATUSCODE != 2

  -- Update the post date on the GL transaction records

  update dbo.JOURNALENTRY
  set JOURNALENTRY.POSTDATE = @POSTDATE
    ,JOURNALENTRY.DATECHANGED = @CHANGEDATE
    ,JOURNALENTRY.CHANGEDBYID = @CHANGEAGENTID
  from dbo.JOURNALENTRY as JE
  inner join dbo.JOURNALENTRY_EXT as JEE on JE.ID = JEE.ID
  inner join dbo.FINANCIALTRANSACTIONLINEITEM as FTLI on JE.FINANCIALTRANSACTIONLINEITEMID = FTLI.ID
    and FTLI.POSTSTATUSCODE != 2
  where FTLI.FINANCIALTRANSACTIONID = @REVENUEID
    and (
      JEE.OUTDATED = 0
      or JEE.TABLENAMECODE = 0
      );

  -- post date for unposted reversals should match the adjustment post date

  update dbo.JOURNALENTRY set 
    JOURNALENTRY.POSTDATE = @POSTDATE
    ,JOURNALENTRY.DATECHANGED = @CHANGEDATE
    ,JOURNALENTRY.CHANGEDBYID = @CHANGEAGENTID
  from dbo.FINANCIALTRANSACTIONLINEITEM as FTLI 
  inner join dbo.JOURNALENTRY on FTLI.ID = JOURNALENTRY.FINANCIALTRANSACTIONLINEITEMID
  inner join dbo.JOURNALENTRY_EXT as JEE on JOURNALENTRY.ID = JEE.ID
  where FTLI.FINANCIALTRANSACTIONID = @REVENUEID
    and FTLI.POSTSTATUSCODE = 1
    and FTLI.TYPECODE = 1
    and FTLI.DELETEDON is null
    and JEE.TABLENAMECODE in (0, 1);

  --Save the snapshot

  exec dbo.USP_ADJUSTMENTHISTORY_REVENUE_SAVESNAPSHOT @ADJUSTMENTID
    ,@CHANGEAGENTID;
end

declare @Error nvarchar(255) = ''

-- Bug 70136 - Null post date if adjustment is DNP

if @@ROWCOUNT > 0
  and @POSTSTATUSCODE <> 2
  set @Error = dbo.UFN_GLDISTRIBUTION_VALIDPOSTDATE(@POSTDATE)

if @Error <> ''
  raiserror (
      @Error
      ,13
      ,1
      )