USP_REVENUETRANSACTION_DONOTRERECEIPT

CREATE procedure dbo.[USP_REVENUETRANSACTION_DONOTRERECEIPT]
(
  @ID uniqueidentifier,
  @CHANGEAGENTID uniqueidentifier
)
as begin

  declare @REVENUERECEIPTID uniqueidentifier = null;
  declare @CURRENTDATE datetime = getdate();
  declare @RECEIPTTYPECODE tinyint;
  declare @CONSTITUENTID uniqueidentifier;

  begin try

    select 
      @RECEIPTTYPECODE = [REVENUE_EXT].[RECEIPTTYPECODE], 
      @CONSTITUENTID = [FINANCIALTRANSACTION].[CONSTITUENTID] 
    from dbo.[REVENUE_EXT] 
    inner join dbo.[FINANCIALTRANSACTION] on [FINANCIALTRANSACTION].[ID] = [REVENUE_EXT].[ID]
    where [FINANCIALTRANSACTION].[ID] = @ID

    if @RECEIPTTYPECODE = 0 --per payment

      begin

        update dbo.[REVENUE_EXT] set
          [NEEDSRERECEIPT] = 0, 
          [CHANGEDBYID] = @CHANGEAGENTID,
          [DATECHANGED] = @CURRENTDATE
        where [ID] = @ID;

        select top 1 @REVENUERECEIPTID = [REVENUERECEIPT].[ID]
        from dbo.[REVENUERECEIPT]
        where [REVENUERECEIPT].[REVENUEID] = @ID
        order by [REVENUERECEIPT].[RECEIPTPROCESSDATE] desc;

        declare @RECEIPTDETAILID uniqueidentifier = null

        select 
          @RECEIPTDETAILID = [REVENUERECEIPTRERECEIPTDETAIL].[ID]
        from dbo.[REVENUERECEIPTRERECEIPTDETAIL]
        join dbo.[REVENUERECEIPT] on [REVENUERECEIPT].[ID] = [REVENUERECEIPTRERECEIPTDETAIL].REVENUERECEIPTID
        where [REVENUERECEIPT].[ID] = @REVENUERECEIPTID and [REVENUERECEIPT].[REVENUEID] = @ID;

        if not @RECEIPTDETAILID is null
          exec dbo.[USP_REVENUERECEIPTRERECEIPTDETAIL_DELETEBYID_WITHCHANGEAGENTID] @RECEIPTDETAILID, @CHANGEAGENTID;

      end

    else if @RECEIPTTYPECODE = 1 --consolidated

      begin

        declare @RECEIPTINGPROCESSSTATUSID uniqueidentifier
        select top 1 
          @RECEIPTINGPROCESSSTATUSID = [RECEIPTINGPROCESSSTATUSID]
        from dbo.[REVENUERECEIPT]
        where [REVENUEID] = @ID
        order by [RECEIPTPROCESSDATE] desc;

        --Cache current context information and set CONTEXT_INFO to @CHANGEAGENTID

        declare @CONTEXTCACHE varbinary(128);
        set @CONTEXTCACHE = CONTEXT_INFO();
        set CONTEXT_INFO @CHANGEAGENTID;

        -- Deleting the ereceipt details if they exist

        delete dbo.[REVENUERECEIPTRERECEIPTDETAIL]
        from dbo.[REVENUERECEIPTRERECEIPTDETAIL]
        inner join dbo.[REVENUERECEIPT] on  [REVENUERECEIPT].[ID] = [REVENUERECEIPTRERECEIPTDETAIL].[REVENUERECEIPTID]
        inner join dbo.[REVENUE_EXT] on [REVENUE_EXT].[ID] = [REVENUERECEIPT].[REVENUEID]
        inner join dbo.[FINANCIALTRANSACTION] on [FINANCIALTRANSACTION].[ID] = [REVENUE_EXT].[ID]
        where 
          [FINANCIALTRANSACTION].[CONSTITUENTID] = @CONSTITUENTID
          and [REVENUE_EXT].[RECEIPTTYPECODE] = 1 
          and [REVENUERECEIPT].[RECEIPTINGPROCESSSTATUSID] = @RECEIPTINGPROCESSSTATUSID;

        --Update any other associated consolidated revenue

        update [REVENUE_EXT] 
        set 
          [NEEDSRERECEIPT] = 0,
          [CHANGEDBYID] = @CHANGEAGENTID,
          [DATECHANGED] = @CURRENTDATE
        from dbo.[REVENUE_EXT]
        inner join dbo.[REVENUERECEIPT] on [REVENUERECEIPT].[REVENUEID] = [REVENUE_EXT].[ID]
        inner join dbo.[FINANCIALTRANSACTION] on [FINANCIALTRANSACTION].[ID] = [REVENUE_EXT].[ID]
        where 
          [FINANCIALTRANSACTION].[CONSTITUENTID] = @CONSTITUENTID 
          and [REVENUE_EXT].[RECEIPTTYPECODE] = 1 
          and [REVENUERECEIPT].[RECEIPTINGPROCESSSTATUSID] = @RECEIPTINGPROCESSSTATUSID;

        --Reset CONTEXT_INFO to previous value

        if @CONTEXTCACHE is not null
        set CONTEXT_INFO @CONTEXTCACHE;

      end

  end try
  begin catch
    exec dbo.[USP_RAISE_ERROR];
    return 1;
  end catch

  return 0;

end