USP_RECURRINGGIFT_ADDSKIPANDWRITEOFF

CREATE procedure dbo.USP_RECURRINGGIFT_ADDSKIPANDWRITEOFF
(
  @ID uniqueidentifier = null output,
  @REVENUEID uniqueidentifier,
  @CHANGEAGENTID uniqueidentifier = null,
  @DATE date = null,
  @WRITEOFFREASONID uniqueidentifier = null,
  @SKIPREASONID uniqueidentifier = null,
  @AMOUNT money = 0,
  @INSTALLMENTS xml = null,
  @ASOFDATE date = null,
  @TYPECODE tinyint = 0,
  @ORIGINALSTATUSCODE tinyint = null
)
as
begin
  if @ID is null
      set @ID = newid()

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

  declare @CURRENTDATE datetime
  set @CURRENTDATE = getdate()

  begin try

    declare @RGAMOUNT money;
    declare @BASECURRENCYID uniqueidentifier;
    declare @RGORGANIZATIONAMOUNT money;
    declare @ORGANIZATIONEXCHANGERATEID uniqueidentifier;
    declare @RGTRANSACTIONAMOUNT money;
    declare @TRANSACTIONCURRENCYID uniqueidentifier;
    declare @BASEEXCHANGERATEID uniqueidentifier;

    select
      @RGAMOUNT = REVENUE.AMOUNT,
      @BASECURRENCYID = REVENUE.BASECURRENCYID,
      @RGORGANIZATIONAMOUNT = REVENUE.ORGANIZATIONAMOUNT,
      @ORGANIZATIONEXCHANGERATEID = REVENUE.ORGANIZATIONEXCHANGERATEID,
      @RGTRANSACTIONAMOUNT = REVENUE.TRANSACTIONAMOUNT,
      @TRANSACTIONCURRENCYID = REVENUE.TRANSACTIONCURRENCYID,
      @BASEEXCHANGERATEID = REVENUE.BASEEXCHANGERATEID
    from
      dbo.REVENUE
    where
      REVENUE.ID = @REVENUEID;


    if @BASECURRENCYID <> @TRANSACTIONCURRENCYID and @BASEEXCHANGERATEID is null
      raiserror('BBERR_BASEEXCHANGERATE_MISSING', 13, 1);

    -- add missing installments

    exec dbo.USP_RECURRINGGIFT_ADDMISSINGINSTALLMENTS
      @REVENUEID,
      @CHANGEAGENTID,
      @CURRENTDATE,
      @RGAMOUNT,
      null,
      @BASECURRENCYID,
      @RGORGANIZATIONAMOUNT,
      @ORGANIZATIONEXCHANGERATEID,
      @RGTRANSACTIONAMOUNT,
      @TRANSACTIONCURRENCYID,
      @BASEEXCHANGERATEID,     
      @ASOFDATE; 

    insert into dbo.RECURRINGGIFTWRITEOFF (ID, REVENUEID, DATE, REASONCODEID, ADDEDBYID, CHANGEDBYID, DATEADDED, DATECHANGED, SKIPREASONCODEID, TYPECODE)
    values (@ID, @REVENUEID, @DATE, @WRITEOFFREASONID, @CHANGEAGENTID, @CHANGEAGENTID, @CURRENTDATE, @CURRENTDATE, @SKIPREASONID, @TYPECODE);

    insert into dbo.RECURRINGGIFTINSTALLMENTWRITEOFF (
      ID,
      RECURRINGGIFTINSTALLMENTID,
      WRITEOFFID,
      AMOUNT,
      BASECURRENCYID,
      ORGANIZATIONAMOUNT,
      ORGANIZATIONEXCHANGERATEID,
      TRANSACTIONAMOUNT,
      TRANSACTIONCURRENCYID,
      BASEEXCHANGERATEID,
      ADDEDBYID, CHANGEDBYID, DATEADDED, DATECHANGED)
    select
      newid(),
      -- insert all installments to be skipped/written-off. When ID is null or empty guid try matching them by date to the existing installments

      coalesce(nullif(WRITEOFFINSTALLMENT.ID,'00000000-0000-0000-0000-000000000000'),(select ID from dbo.RECURRINGGIFTINSTALLMENT where REVENUEID = @REVENUEID and DATE = WRITEOFFINSTALLMENT.DATE)),
      @ID,
      case when @TYPECODE = 0 then WRITEOFFAMOUNTCURRENCYVALUES.BASEAMOUNT else 0 end,--if this is a 'Skip' set the  to 0

      @BASECURRENCYID,
      case when @TYPECODE = 0 then WRITEOFFAMOUNTCURRENCYVALUES.ORGANIZATIONAMOUNT else 0 end,
      @ORGANIZATIONEXCHANGERATEID,
      case when @TYPECODE = 0 then WRITEOFFINSTALLMENT.WRITEOFFAMOUNT else 0 end,
      @TRANSACTIONCURRENCYID,
      @BASEEXCHANGERATEID,
      @CHANGEAGENTID, @CHANGEAGENTID, @CURRENTDATE, @CURRENTDATE
    from
      dbo.UFN_RECURRINGGIFTINSTALLMENT_GETWRITEOFFINSTALLMENTS_2_FROMITEMLISTXML(@INSTALLMENTS) WRITEOFFINSTALLMENT
      outer apply dbo.UFN_CURRENCY_GETCURRENCYVALUES_2
        (
          WRITEOFFINSTALLMENT.WRITEOFFAMOUNT,
          null,
          @BASECURRENCYID,
          @BASEEXCHANGERATEID,
          @TRANSACTIONCURRENCYID,
          null,
          null,
          null,
          @ORGANIZATIONEXCHANGERATEID,
          0
        ) as WRITEOFFAMOUNTCURRENCYVALUES
    where
      WRITEOFFINSTALLMENT.WRITEOFFAMOUNT > 0;

    declare @STATUSCHANGETYPECODE tinyint = case @TYPECODE when 0 then 3 else 98 end;
    declare @NEWSTATUSCODE tinyint = case when @ORIGINALSTATUSCODE is null then null else 1 end;

    exec dbo.USP_RECURRINGGIFT_UPDATEREVENUESCHEDULE
      @REVENUEID,
      @STATUSCHANGETYPECODE,
      @SKIPREASONID,
      @ORIGINALSTATUSCODE,
      @NEWSTATUSCODE,
      @RGAMOUNT,
      @BASECURRENCYID,
      @RGORGANIZATIONAMOUNT,
      @ORGANIZATIONEXCHANGERATEID,
      @RGTRANSACTIONAMOUNT,
      @TRANSACTIONCURRENCYID,
      @BASEEXCHANGERATEID,     
      @CHANGEAGENTID,
      @CURRENTDATE
  end try
  begin catch
      exec dbo.USP_RAISE_ERROR
      return 1
  end catch

return 0    

end