Online Payment NOT POSTED IN VMIS

Product	VMIS
Database Path	production/training/devtest
TDX - Incident	#90331
TDX - Service	[For enhancement / refinement team]

Overview

[describe the nature/purpose of the change]

Technical Notes

Data Query

Technical Review

N/A

Deployment Notes/Instructions

Change requires deployment of VMIS: No
Change Requires deployment of Schema Changes: No
Change Requires deployment of Data Changes: Yes
Change Requires deployment of websites (if yes, please specify): No
Is timing or order of the deployments critical (if yes, please specify)? No

Deployment Data Changes:

-- 2025-01-06 14:06
--#region Input Parameters
Declare @Id      [int]                       = ###
,       @Account [int]                       = ###
,       @Amount  [decimal](9,2)              = ###
,       @Date    [varchar](50)               = ###
;
--#endregion Parameters

--#region Programatically Set Variables
Declare @system_tracking_id [int]     = @Id,
        @payment_refno [varchar](50)  = @Id,
        @trans_date [datetime]        = TRY_CONVERT(datetime, @Date);
Declare @ref_no varchar(150) = @payment_refno + '-' + @payment_refno;
Declare @TouchnetTransactionId int;
--#endregion Programatically Set Variables



--#region Find Touchnet Transaction to Update
--use accountid=clientid to get latest transaction that where is_success=0
Select top 1 @TouchnetTransactionId = tt.id
FROM touchnet_transaction tt
WHERE tt.client_id = @Account
AND CONVERT(date, date_created) like CONVERT(date, @Date)
AND is_success = 0
order by tt.date_created DESC
--#endregion Find Touchnet Transaction to Update

--#region Update Touchnet and Execute Update payment
--@TouchnetTransactionId is not successfully
--touchnet_transaction.id = @TouchnetTransactionId
UPDATE touchnet_transaction
SET paid_amount        = @Amount,
	trans_date         = @trans_date,
	is_success         = 1, -- Set to Success
	system_tracking_id = @system_tracking_id,
	payment_refno      = @payment_refno
WHERE id               = @TouchnetTransactionId


exec dbo.sp_touchnet_update_payment @Account, @Amount, @ref_no
--#endregion Update Touchnet and Execute Update payment


--#region Check it Updated Properly
SELECT *
FROM touchnet_transaction tt
WHERE tt.client_id = @Account
order by tt.trans_date desc
--#endregion Check it Updated Properly

Background History:

Usually:

“[Client],A#[Account Number] made an online payment of $[Account Number] on [date], however, this payment did not post in VMIS. The confirmation below shows the $[Account Number] payment to VMIS A#[Account] on [date], ID#[Reference Number]. Please post to the VMIS A#[Account Number]“

ID	Account	Payer	Amount	Date	Effective/Funding Date	Status	Payment Method	Type

ID

Account

Payer

Amount

Date

Effective/Funding Date

Status

Payment Method

Type

[Flywire Id]
[Sql:@Id]

[Client Name]

[Sql:@Account]

[Client Name]

$[Amount]

[Sql:@Amount]

[Date & Time]

[Sql: @Date]

[Date Posted]

Success

[Card Issuer & #]

[Usually Regual]

VMBS Manual