USP_GET_CMSTRANSACTIONS

      CREATE procedure dbo.USP_GET_CMSTRANSACTIONS
      (
          @NUMBEROFRECORDS int
      )
      as
      begin

      declare @RECORDSTODELETE table (ID uniqueidentifier, TRANSACTIONID uniqueidentifier, TYPE nvarchar(20));
    declare @PAYMENT2_0RECORDSTODELETE table (RowID INT IDENTITY(1,1), ID uniqueidentifier, TRANSACTIONID uniqueidentifier);
    declare @tranType nvarchar(20) = 'Payment2_0'



    -- GET THE RECORDS WHICH WILL NEVER BE PROCESSED

    insert into @RECORDSTODELETE
    select ID, TRANSACTIONID, TYPE
    from [dbo].[CMSTRANSACTIONS]
    where status in (
            4 -- CheckoutCancelled

            , 16 -- ValidationFailed

            , 128 -- AcknowledgementSent

            , 256 -- Complete

            , 512 -- CheckoutError

            , 1024 -- ChargeError

            , 4096 -- InternalError

            , 8192 -- CheckoutExpired

            ) 



    -- INSERT THE RECORDS (WHICH WILL NEVER BE PROCESSED) INTO THE [ARCHIVEDCMSTRANSACTIONS] TABLE

    insert into [dbo].[ARCHIVEDCMSTRANSACTIONS] (
        TRANSACTIONID
        , ORDERID
        , DATA
        , status
        , STATUSTEXT
        , TYPE
        , ADDEDBYID
        , CHANGEDBYID
        , DATEADDED
        , DATECHANGED
        , RESULTCODE
        , FAILDATA
        )
    select TRANSACTIONID
        , ORDERID
        , DATA
        , status
        , STATUSTEXT
        , TYPE
        , ADDEDBYID
        , CHANGEDBYID
        , DATEADDED
        , DATECHANGED
        , RESULTCODE
        , FAILDATA
    from [dbo].[CMSTRANSACTIONS]
    where id in (
            select Id
            from @RECORDSTODELETE
            )

    -- Also pick Payment2_0 records from ARCHIVEDCMSTRANSACTIONS to delete from CMS_SESSIONVARIABLEBACKUP as per LifeSpan value

    -- Add query to remove SP31 Checkout Transaction record from CMS_SESSIONVARIABLEBACKUP table

    insert into @RECORDSTODELETE
    select ACT.ID, ACT.TRANSACTIONID, ACT.TYPE
    from dbo.CMS_SESSIONVARIABLEBACKUP SVB
    inner join dbo.ARCHIVEDCMSTRANSACTIONS ACT ON ACT.TRANSACTIONID = SVB.KEYGUID
    where SVB.TYPECODE = 1 
    and CAST(SVB.DATEADDED as date) <= CAST(ACT.DATEADDED as date)

  begin try
    -- ONLY GET Payment2_0 RECORD FROM TABLE @RECORDSTODELETE & COPIED INTO TABLE @PAYMENT2_0RECORDSTODELETE BEFORE DELETE from TABLE @RECORDSTODELETE

    if exists (select 1 from @RECORDSTODELETE where type = @tranType)
    begin
      DECLARE @RowCnt int = 0, @RowID int = 1, @NodeValue bit;
      DECLARE @TRANSACTIONID uniqueidentifier, @CARTID uniqueidentifier, @CHANGEAGENTID?uniqueidentifier;

        insert into @PAYMENT2_0RECORDSTODELETE (ID, TRANSACTIONID) 
        select ID, TRANSACTIONID from @RECORDSTODELETE
        where type = @tranType

        exec?dbo.USP_CHANGEAGENT_GETORCREATECHANGEAGENT?@CHANGEAGENTID?output;

      select @RowCnt = Count(1) from @PAYMENT2_0RECORDSTODELETE

      while @RowID <= @RowCnt
      begin
        select @TRANSACTIONID = TRANSACTIONID from @PAYMENT2_0RECORDSTODELETE where @RowID = RowID        

        -- if the user cancelled and did not check out, delete the sponsorship row

        select @NodeValue = dbo.UFN_GET_BACKUPITEMVALUENODE_BY_KEYGUID(@TRANSACTIONID, 'ContainsSponsorshipItem')
        if @NodeValue = 1 -- If CART contain Sponsorship item, then it should be 1

        begin
          select @CARTID = SALESORDERID from SALESORDERBBPAYTRANSACTION
          where  TRANSACTIONID = @TRANSACTIONID
          exec dbo.USP_DELETE_CMS_SESSIONVARIABLEBACKUP @ID = @CARTID, @CHANGEAGENTID = @CHANGEAGENTID, @KEYGUID = @CARTID
        end

        -- If the XML have ArchiveFlag value true, we move the record from dbo.CMS_SESSIONVARIABLEBACKUP to dbo.CMS_UNPROCESSEDBBSPTRANSACTION;

        -- Else we just delete it.

        select @NodeValue = dbo.UFN_GET_BACKUPITEMVALUENODE_BY_KEYGUID(@TRANSACTIONID, 'ArchiveFlag')
        if @NodeValue = 1
        begin
          exec dbo.USP_RECORDOPERATION_ARCHIVEDEADBBSPTRANSACTION @ID = @TRANSACTIONID, @CHANGEAGENTID = @CHANGEAGENTID, @KEYGUID = @TRANSACTIONID
        end
        else
        begin
          exec dbo.USP_DELETE_CMS_SESSIONVARIABLEBACKUP @ID = @TRANSACTIONID, @CHANGEAGENTID = @CHANGEAGENTID, @KEYGUID = @TRANSACTIONID
        end
        exec dbo.USP_CLEAR_BBPAYLOG @ID = @TRANSACTIONID, @ISCONFIRMED = false, @ClearSalesOrderItem = 0

        SET @RowID += 1;
      end      
    end
   end try
     begin catch
      declare @ServerName nvarchar(200) =  @@SERVERNAME, @ErrorMsg nvarchar(200) =  ERROR_MESSAGE() ;
    SET @ErrorMsg = 'An error occurred while deleting data from SALESORDERBBPAYTRANSACTION & CMS_SESSIONVARIABLEBACKUP table - ' + @ErrorMsg
    exec dbo.spAddUpdate_Error 0, 'SQL Procedure Exception', @ErrorMsg, @ErrorMsg, @ServerName, 'Error while processing Payment2.0 records in USP_GET_CMSTRANSACTIONS'
     end catch

    -- DELETE THE RECORDS (WHICH WILL NEVER BE PROCESSED) FROM THE [CMSTRANSACTIONS] TABLE

    delete
    from dbo.[CMSTRANSACTIONS]
    where id in (
            select Id
            from @RECORDSTODELETE
            )

      select top (@NUMBEROFRECORDS) ID, TRANSACTIONID, DATA, STATUS, TYPE as TYPE from dbo.CMSTRANSACTIONS with (nolock)
      where DATEADDED < DateADD(mi, -30, Current_TimeStamp)
      order by DATEADDED;

      end