Alpha Baking

Go to the SQL script 

N:\Alpha IT\Scripts\ManualTasks\Online Web Payment Error Fix

We have instructions from Steve Carr in an email:

All,

I resubmitted the payment process and all should be good on this one.  Below is the SQL that I used.

First I found the sessionId involved.

Then I confirmed data.

Then I set the @sessionId variable as noted in the BEGIN TRAN – EXEC section.

Then I confirmed the results, and did a COMMIT when all was good.

Remember that this needs to be done within an hour or so to make sure the session is not deleted.

-- You can copy the documents from the email table to find the sessionId for the invoices that were attempted for payment

-- Also check that there has not been any attempt to repay them under a different session id

declare @keys varchar(max) =

'32455349

32458807

32468192

32472308

32484562

32490044

32494220

32506180

32511268

32524035

32531836

'

select * from astreceiptlinesession (nolock) 

where documentkey in ( 

select ltrim(rtrim(ddata)) from dbo.split(@keys,'

')

-- Note there should only be one line break above to split by that

where len(ltrim(rtrim(ddata)))>3

)

order by sessionid desc

-- if found, then confirm all needed records are in these 2 tables

select * from astreceiptlinesession (nolock) where sessionId = 37858287           -- one per document

select * from astreceiptsession (nolock) where sessionId = 37858287        -- just one record

BEGIN TRAN

DECLARE

@messageArgs VARCHAR(255),

@messageKey INTEGER,

@messageText VARCHAR(255),

@returnValue INTEGER,

@sessionId INTEGER,

@userKey INTEGER

SET @messageArgs = null

SET @messageKey = null

SET @messageText = null

SET @returnValue = null

SET @sessionId = 31080438         -- SET THIS VALUE

SELECT top 1 @userKey = a.userKey from ASTICSUser a join ASTReceiptLineSession b on b.login = a.login where b.sessionId = @sessionId

SET NOCOUNT ON

EXECUTE ASTABProcessOnlinePayment @sessionId = @sessionId, @userKey = @userKey, @messageArgs = @messageArgs /* null */ OUT, @messageKey = @messageKey /* null */ OUT, @messageText = @messageText /* null */ OUT, @returnValue = @returnValue /* null */ OUT, @isControlTransaction = 1

IF @messageArgs IS null PRINT '@messageArgs is null' ELSE PRINT '@messageArgs = ' + @messageArgs

IF @messageKey IS null PRINT '@messageKey is null' ELSE PRINT '@messageKey = ' + CONVERT(varchar(255), @messageKey)

IF @messageText IS null PRINT '@messageText is null' ELSE PRINT '@messageText = ' + @messageText

IF @returnValue IS null PRINT '@returnValue is null' ELSE PRINT '@returnValue = ' + CONVERT(varchar(255), @returnValue)

select top 3 * from astabpaymentheader order by 1 desc -- confirm the 1 new record is there as expected

select top 20 * from astabpaymentdetail order by paymentid desc     -- confirm the documents are there as expected

ROLLBACK      -- rollback or commit as needed

--COMMIT

First run the Begin Tran and if it looks fine with the payment, then go commit.

If we get more session id's

I checked all 3 sessionId values and used 39276388.  It had a record on ASTReceiptSession and records on ASTReceiptLineSession, but the others only had records on ASTReceiptLineSession.  I think that is because they retried to make the payment multiple times.

I think it has now been processed successfully, so we are all good with all payments.  Let me know if you see any problems.

Thanks.

Steve Carr

405-473-2816

From: Munnanoor, Keerthana <kmunnanoor@AlphaBaking.com>
Sent: Monday, May 1, 2023 12:06 PM
To: Carr, Steve <scarr@assistcorp.com>
Subject: RE: Online Web Payment Error

Yes. The first one with $55.95 went well. I got payment confirmation email also. 

$1585.19 is a bit confusing. I got 3 session Ids.

I took the recent session ID to check if it is paid. I did not hit commit for that.

Received this email:

Error message: Invalid sessionId: 39276494