USP_RECORDOPERATION_RECEIPTINGPROCESSCLEARRESULTS

CREATE procedure dbo.USP_RECORDOPERATION_RECEIPTINGPROCESSCLEARRESULTS
(
  @ID uniqueidentifier,
  @CHANGEAGENTID uniqueidentifier = null
)
as
  set nocount on;

  declare @CURRENTDATE datetime;
  set @CURRENTDATE = getdate();

  -- WI#1903845: Declared id locally to resolve parameter sniffing issue.

  declare @NEW_ID uniqueidentifier;
  set @NEW_ID = @ID;

  --Cache CONTEXT INFO

  declare @contextCache varbinary(128);
  set @contextCache = CONTEXT_INFO();

  if not @CHANGEAGENTID is null
    set CONTEXT_INFO @CHANGEAGENTID;

  if (coalesce((select top 1 RERECEIPTPAYMENTS from dbo.RECEIPTPREFERENCEINFO), 1)) = 1
  begin

    -- If we are clearing results for the most recent revenue, reset the NEEDSRERECEIPT flag on the revenue record

    update dbo.[REVENUE_EXT]
    set 
      [REVENUE_EXT].[NEEDSRERECEIPT] = 1     
    where 
      [REVENUE_EXT].[DONOTRECEIPT] = 0
      and [REVENUE_EXT].[ID] in 
        (
          select 
            [OUTER_RECEIPT].[REVENUEID]
          from dbo.[REVENUERECEIPT] as [OUTER_RECEIPT]
          where 
            [OUTER_RECEIPT].[RECEIPTINGPROCESSSTATUSID] = @NEW_ID

            -- the revenue was already receipted

            and (select count(*) from dbo.[REVENUERECEIPT] as [INNER_RECEIPT] where [INNER_RECEIPT].[REVENUEID] = [OUTER_RECEIPT].[REVENUEID]) > 1

            -- and was not receipted in a later process

            and not exists (select 1 from dbo.[REVENUERECEIPT] as [INNER_RECEIPT] where [INNER_RECEIPT].[REVENUEID] = [OUTER_RECEIPT].[REVENUEID] and [INNER_RECEIPT].[DATEADDED] > [OUTER_RECEIPT].[DATEADDED])
        )   
  end              

  update 
    dbo.REVENUERECEIPT
  set 
    ORIGINALREVENUERECEIPTID = null,
    CHANGEDBYID = @CHANGEAGENTID,
    DATECHANGED = @CURRENTDATE
  where
    ORIGINALREVENUERECEIPTID in (select RR.ID from dbo.REVENUERECEIPT RR where RR.RECEIPTINGPROCESSSTATUSID = @NEW_ID);

  update 
    dbo.REVENUERECEIPT
  set 
    PREVIOUSREVENUERECEIPTID = null,
    CHANGEDBYID = @CHANGEAGENTID,
    DATECHANGED = @CURRENTDATE
  where
    PREVIOUSREVENUERECEIPTID in (select RR.ID from dbo.REVENUERECEIPT RR where RR.RECEIPTINGPROCESSSTATUSID = @NEW_ID);


  declare @NEXTNUMBER int;
  declare @RECEIPTSTACKID uniqueidentifier;
  declare @RECEIPTPROCESSDATE datetime;

  select 
    @NEXTNUMBER = MIN(RECEIPTNUMBER), 
    @RECEIPTSTACKID = RECEIPTSTACKINFOID, 
    @RECEIPTPROCESSDATE = RECEIPTPROCESSDATE
  from dbo.REVENUERECEIPT 
  where RECEIPTINGPROCESSSTATUSID = @NEW_ID
  group by RECEIPTSTACKINFOID,RECEIPTPROCESSDATE;

  if not exists (select ID from REVENUERECEIPT where @RECEIPTSTACKID = RECEIPTSTACKINFOID and RECEIPTPROCESSDATE > @RECEIPTPROCESSDATE and RECEIPTINGPROCESSSTATUSID <> @NEW_ID)
    exec dbo.USP_REVENUE_SETNEXTRECEIPTNUMBER @RECEIPTSTACKID,@NEXTNUMBER; 

  delete from dbo.REVENUERECEIPT where RECEIPTINGPROCESSSTATUSID = @NEW_ID;

  --Restore CONTEXT_INFO

  if not @contextCache is null
    set CONTEXT_INFO @contextCache;

  update 
    dbo.BUSINESSPROCESSSTATUS 
  set 
    STATUSCODE = 3,
    CHANGEDBYID = @CHANGEAGENTID,
    DATECHANGED = @CURRENTDATE
  where 
    ID = @NEW_ID;

  return 0;