USP_SAVE_WRITEOFFADJUSTMENT

CREATE procedure dbo.USP_SAVE_WRITEOFFADJUSTMENT (
  @WRITEOFFID uniqueidentifier = null
  ,@WRITEOFFADJUSTMENTID uniqueidentifier = null output
  ,@CHANGEAGENTID uniqueidentifier = null
  ,@CHANGEDATE datetime = null
  ,@DATE datetime = null
  ,@POSTDATE datetime = null
  ,@ADJUSTMENTREASON nvarchar(300) = ''
  ,@ADJUSTMENTREASONCODEID uniqueidentifier = null
  )
  with execute as owner
as
set nocount on;

--Is the write-off posted?

if not exists (
    select 1
    from dbo.FINANCIALTRANSACTION
    where ID = @WRITEOFFID
      and POSTSTATUSCODE = 2
    )
  raiserror (
      'You cannot adjust an unposted write-off'
      ,13
      ,1
      )

declare @CURRENTDATE datetime;
declare @WRITEOFFPOSTDATE datetime;
declare @CONSTITUENTID uniqueidentifier;

--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);

select @WRITEOFFADJUSTMENTID = ID
from dbo.WRITEOFFADJUSTMENT
where WRITEOFFID = @WRITEOFFID
  and POSTSTATUSCODE <> 0

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

--kwb Create FINANCIALTRANSACTIONLINEITEMADJUSTMENT records for original write-offs that will be adjusted

exec dbo.USP_CREATEINITIALADJUSTMENTHISTORY @WRITEOFFID
  ,@CHANGEAGENTID

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

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

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

--Update unposted adjustment if existing

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

update dbo.WRITEOFFADJUSTMENT
set [DATE] = @DATE
  ,POSTDATE = @POSTDATE
  ,REASON = @ADJUSTMENTREASON
  ,REASONCODEID = @ADJUSTMENTREASONCODEID
  ,CHANGEDBYID = @CHANGEAGENTID
  ,DATECHANGED = @CHANGEDATE
where ID = @WRITEOFFADJUSTMENTID;

if @@ROWCOUNT = 0
begin
  declare @TRANSACTIONAMOUNT money;
  declare @AMOUNT money;
  declare @ORGANIZATIONAMOUNT money;
  declare @TRANSACTIONCURRENCYID uniqueidentifier;
  declare @BASECURRENCYID uniqueidentifier;
  declare @BASEEXCHANGERATEID uniqueidentifier;
  declare @ORGANIZATIONEXCHANGERATEID uniqueidentifier;

  select @TRANSACTIONAMOUNT = sum(WRITEOFFSPLIT.TRANSACTIONAMOUNT)
    ,@AMOUNT = sum(WRITEOFFSPLIT.AMOUNT)
    ,@ORGANIZATIONAMOUNT = sum(WRITEOFFSPLIT.ORGANIZATIONAMOUNT)
    ,@TRANSACTIONCURRENCYID = WRITEOFFSPLIT.TRANSACTIONCURRENCYID
    ,@BASECURRENCYID = WRITEOFFSPLIT.BASECURRENCYID
    ,@BASEEXCHANGERATEID = WRITEOFFSPLIT.BASEEXCHANGERATEID
    ,@ORGANIZATIONEXCHANGERATEID = WRITEOFFSPLIT.ORGANIZATIONEXCHANGERATEID
  from dbo.WRITEOFFSPLIT
  where WRITEOFFSPLIT.WRITEOFFID = @WRITEOFFID
  group by WRITEOFFSPLIT.TRANSACTIONCURRENCYID
    ,WRITEOFFSPLIT.BASECURRENCYID
    ,WRITEOFFSPLIT.BASEEXCHANGERATEID
    ,WRITEOFFSPLIT.ORGANIZATIONEXCHANGERATEID;

  --Log Adjustment if new

  insert into dbo.WRITEOFFADJUSTMENT (
    ID
    ,WRITEOFFID
    ,TRANSACTIONPREVIOUSAMOUNT
    ,PREVIOUSAMOUNT
    ,ORGANIZATIONPREVIOUSAMOUNT
    ,[DATE]
    ,POSTDATE
    ,POSTSTATUSCODE
    ,REASON
    ,REASONCODEID
    ,ADDEDBYID
    ,CHANGEDBYID
    ,DATEADDED
    ,DATECHANGED
    ,TRANSACTIONCURRENCYID
    ,BASECURRENCYID
    ,BASEEXCHANGERATEID
    ,ORGANIZATIONEXCHANGERATEID
    )
  select @WRITEOFFADJUSTMENTID
    ,ID
    ,@TRANSACTIONAMOUNT
    ,@AMOUNT
    ,@ORGANIZATIONAMOUNT
    ,@DATE
    ,@POSTDATE
    ,1
    ,@ADJUSTMENTREASON
    ,@ADJUSTMENTREASONCODEID
    ,@CHANGEAGENTID
    ,@CHANGEAGENTID
    ,@CHANGEDATE
    ,@CHANGEDATE
    ,@TRANSACTIONCURRENCYID
    ,@BASECURRENCYID
    ,@BASEEXCHANGERATEID
    ,@ORGANIZATIONEXCHANGERATEID
  from dbo.WRITEOFF
  where ID = @WRITEOFFID;

  --Save the snapshot before deleting the GL information

  exec dbo.USP_ADJUSTMENTHISTORY_WRITEOFF_SAVESNAPSHOT @WRITEOFFADJUSTMENTID
    ,@CHANGEAGENTID;

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

  exec dbo.USP_GLTRANSACTION_ADDWRITEOFFREVERSALS @WRITEOFFID
    ,@CHANGEAGENTID
    ,@CHANGEDATE
    ,@POSTDATE;
end
else /*make sure @WRITEOFFADJUSTMENTID gets set to the correct value */
begin
  --select

  --    @WRITEOFFADJUSTMENTID = ID

  --from dbo.WRITEOFFADJUSTMENT

  --where WRITEOFFID = @WRITEOFFID and POSTSTATUSCODE <> 0;

  -- 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
  where FTLI.FINANCIALTRANSACTIONID = @WRITEOFFID
    and JEE.OUTDATED = 0
    and FTLI.POSTSTATUSCODE = 1;

  -- 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.JOURNALENTRY
  inner join dbo.JOURNALENTRY_EXT as JEE on JOURNALENTRY.ID = JEE.ID
  inner join dbo.JOURNALENTRY as JE_REV on JEE.REVERSEDGLTRANSACTIONID = JE_REV.ID
  inner join dbo.FINANCIALTRANSACTIONLINEITEM as FTLI_REV on JE_REV.FINANCIALTRANSACTIONLINEITEMID = FTLI_REV.ID
  inner join dbo.FINANCIALTRANSACTIONLINEITEM as FTLI on FTLI.ID = JOURNALENTRY.FINANCIALTRANSACTIONLINEITEMID
  where FTLI_REV.FINANCIALTRANSACTIONID = @WRITEOFFID
    and FTLI.POSTSTATUSCODE = 1;

  --Save the snapshot before deleting the GL information

  exec dbo.USP_ADJUSTMENTHISTORY_WRITEOFF_SAVESNAPSHOT @WRITEOFFADJUSTMENTID
    ,@CHANGEAGENTID;
end

declare @Error nvarchar(255) = ''

if @@ROWCOUNT > 0
  set @Error = dbo.UFN_GLDISTRIBUTION_VALIDPOSTDATE(@POSTDATE)

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