USP_REVENUE_UPDATEWRITEOFFS

CREATE procedure dbo.USP_REVENUE_UPDATEWRITEOFFS
(
    @REVENUEID uniqueidentifier,
    @CHANGEAGENTID uniqueidentifier,
    @CURRENTDATE datetime,
    @ADJWRITEOFF_DATE datetime = null,
    @ADJWRITEOFF_POSTDATE datetime = null,
    @ADJWRITEOFF_REASONCODEID uniqueidentifier = null,
    @ADJWRITEOFF_DETAILS nvarchar(255) = null,
  @CLEARWRITEOFFGLDISTRIBUTION bit = null
)
as 
begin
    set nocount on;

  declare @UPDATEINSTALLMENTWRITEOFFS bit = 1;

    if @ADJWRITEOFF_DATE is null and @ADJWRITEOFF_POSTDATE is null and @ADJWRITEOFF_REASONCODEID is null and @ADJWRITEOFF_DETAILS is null
    begin
        set @UPDATEINSTALLMENTWRITEOFFS = 0
    end

  set @CLEARWRITEOFFGLDISTRIBUTION=COALESCE(@CLEARWRITEOFFGLDISTRIBUTION,0);

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

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

    --cache the current Write-off info

    declare @WriteOffInstallmentAmounts table (WriteOffID uniqueidentifier, InstallmentID uniqueidentifier, WriteoffAmount money)
    insert into @WriteOffInstallmentAmounts (WriteOffID, InstallmentID, WriteoffAmount)
    select INSTALLMENTSPLITWRITEOFF.WRITEOFFID, INSTALLMENTSPLIT.INSTALLMENTID, sum(INSTALLMENTSPLITWRITEOFF.TRANSACTIONAMOUNT)
    from dbo.INSTALLMENTSPLIT inner join dbo.INSTALLMENTSPLITWRITEOFF on INSTALLMENTSPLIT.ID = INSTALLMENTSPLITWRITEOFF.INSTALLMENTSPLITID
    where INSTALLMENTSPLIT.PLEDGEID = @REVENUEID
    group by INSTALLMENTSPLITWRITEOFF.WRITEOFFID, INSTALLMENTSPLIT.INSTALLMENTID

  --Update writeoffs

  declare @WRITEOFFID uniqueidentifier;
  declare @WRITEOFFIDTABLE UDT_GENERICID;

  insert into @WRITEOFFIDTABLE
  select ID
  from dbo.FINANCIALTRANSACTION
  where PARENTID = @REVENUEID
  and TYPECODE = 20 --writeoff

  and DELETEDON is null;

  declare @WRITEOFFCURSOR cursor;
  set @WRITEOFFCURSOR = cursor local fast_forward for
  select ID
  from @WRITEOFFIDTABLE

  open @WRITEOFFCURSOR;
  fetch next from @WRITEOFFCURSOR 
    into @WRITEOFFID;

  while @@FETCH_STATUS = 0 
    begin
       declare @WRITEOFFINSTALLMENTS xml = (
            select 
                ID, 
                DATE,
                WRITEOFFINSTALLMENT.TRANSACTIONAMOUNT as AMOUNT,
                BALANCE,
                        t1.WRITEOFFAMOUNT,
                SEQUENCE,
                TRANSACTIONCURRENCYID
            from dbo.UFN_WRITEOFF_GETINSTALLMENTSFOREDIT(@WRITEOFFID) WRITEOFFINSTALLMENT left join @WriteOffInstallmentAmounts t1 on WRITEOFFINSTALLMENT.ID = t1.InstallmentID
            for xml raw('ITEM'),type,elements,root('INSTALLMENTS'),BINARY BASE64
        );

        declare @WRITEOFFTOTALAMOUNT money = (
            select sum(WRITEOFFAMOUNT) 
            from @WriteOffInstallmentAmounts 
            where WRITEOFFID =@WRITEOFFID
        );

            exec dbo.USP_INSTALLMENT_WRITEOFFINSTALLMENTS @WRITEOFFID, @WRITEOFFTOTALAMOUNT, @CHANGEAGENTID, @CURRENTDATE, 1, @WRITEOFFINSTALLMENTS;

          if @CLEARWRITEOFFGLDISTRIBUTION = 1
              exec dbo.USP_WRITEOFF_FIXSPLITS @WRITEOFFID, @REVENUEID, @CHANGEAGENTID, @CURRENTDATE;

      fetch next from @WRITEOFFCURSOR 
        into @WRITEOFFID;            

    end

  deallocate @WRITEOFFCURSOR;

  declare @ADJUSTEDWRITEOFFS table(WRITEOFFID uniqueidentifier, ADJUSTMENTID uniqueidentifier);

  /* Adjust any write-offs for this pledge. */
  if @CLEARWRITEOFFGLDISTRIBUTION = 1
    begin
      if exists (select 1 from dbo.JOURNALENTRY 
                      inner join dbo.JOURNALENTRY_EXT on JOURNALENTRY_EXT.ID = JOURNALENTRY.ID 
                      inner join dbo.FINANCIALTRANSACTIONLINEITEM on JOURNALENTRY.FINANCIALTRANSACTIONLINEITEMID = FINANCIALTRANSACTIONLINEITEM.ID
                      inner join dbo.FINANCIALTRANSACTION on FINANCIALTRANSACTIONLINEITEM.FINANCIALTRANSACTIONID = FINANCIALTRANSACTION.ID
                      where FINANCIALTRANSACTION.PARENTID = @REVENUEID
                      and FINANCIALTRANSACTION.TYPECODE = 20    --writeoff

                      and JOURNALENTRY_EXT.OUTDATED = 0  
                      and FINANCIALTRANSACTIONLINEITEM.TYPECODE = 0) --standard

        begin

          declare WRITEOFFCURSOR cursor local fast_forward for
          select WRITEOFF.ID from dbo.WRITEOFF where REVENUEID = @REVENUEID;

          open WRITEOFFCURSOR;
          fetch next from WRITEOFFCURSOR into @WRITEOFFID;

          while @@FETCH_STATUS = 0
            begin

              declare @WRITEOFFADJUSTMENTID uniqueidentifier=null;

              exec dbo.USP_SAVE_WRITEOFFADJUSTMENT @WRITEOFFID, @WRITEOFFADJUSTMENTID output, @CHANGEAGENTID, @CURRENTDATE, @ADJWRITEOFF_DATE, @ADJWRITEOFF_POSTDATE, @ADJWRITEOFF_DETAILS, @ADJWRITEOFF_REASONCODEID;

              --Save adjustment IDs for adjustment history

              insert into @ADJUSTEDWRITEOFFS(WRITEOFFID,ADJUSTMENTID) values (@WRITEOFFID,@WRITEOFFADJUSTMENTID);

              fetch next from WRITEOFFCURSOR into @WRITEOFFID;    

            end

          close WRITEOFFCURSOR;
          deallocate WRITEOFFCURSOR;

        end

    end

    -- Save the adjustment history for any write-offs

    if (select count(*) from @ADJUSTEDWRITEOFFS) > 0
      begin
        declare @HISTORYWRITEOFFID uniqueidentifier;
        declare @HISTORYADJUSTMENTID uniqueidentifier;

        /* Cursor to use for logging history adjustments */
        declare HISTORYCURSOR cursor local fast_forward for
        select WRITEOFFID, ADJUSTMENTID from @ADJUSTEDWRITEOFFS

        open HISTORYCURSOR;
        fetch next from HISTORYCURSOR into @HISTORYWRITEOFFID, @HISTORYADJUSTMENTID;

        while @@FETCH_STATUS = 0 
          begin
            if @HISTORYADJUSTMENTID is not null
              exec dbo.USP_ADJUSTMENTHISTORY_WRITEOFF_SAVEHISTORY @HISTORYWRITEOFFID, @CHANGEAGENTID, null, @HISTORYADJUSTMENTID;

            fetch next from HISTORYCURSOR into @HISTORYWRITEOFFID, @HISTORYADJUSTMENTID;
          end

        close HISTORYCURSOR;
        deallocate HISTORYCURSOR;

      end

    -- clear the user-defined gl distributions

    if (@CLEARWRITEOFFGLDISTRIBUTION = 1)
      begin
        delete from dbo.WRITEOFFGLDISTRIBUTION where WRITEOFFID in (select WO.ID from dbo.WRITEOFF WO where WO.REVENUEID = @REVENUEID) and OUTDATED = 0;

        -- Add new writeoff GL distributions if appropriate

        if exists(select ID
                  from dbo.FINANCIALTRANSACTION
                  where PARENTID = @REVENUEID
                  and TYPECODE = 20 --writeoff

                  and POSTSTATUSCODE=1 --not posted

                  and DELETEDON is null)
          begin
            if (dbo.UFN_VALID_BASICGL_INSTALLED() = 1) 
              begin
                exec dbo.USP_SAVE_PLEDGEWRITEOFFGLDISTRIBUTION @REVENUEID, @WRITEOFFIDTABLE, @CHANGEAGENTID, @CURRENTDATE;
              end
            else 
              begin
                exec dbo.USP_SAVE_WRITEOFFGLDISTRIBUTION @REVENUEID, @CHANGEAGENTID, @CURRENTDATE;
              end
          end
      end

end