If payment is missed in the saavypay and updated in the adyen portal, we need to update it manually. we need to update payment header and additionalinfo in the saavypay DB
Payment Header. Please collect the cooresponding valuse from webcheckin nlog and update.
INSERT INTO tbPaymentHeader
(
TransactionID,ReservationNumber, ReservationNameID, MaskedCardNumber, ExpiryDate, FundingSource, Amount,
Currency, RecurringIdentifier, AuthorisationCode, pspReferenceNumber, ParentPspRefereceNumber,TransactionTypeID,ResultCode,ResponseMessage,
CreatedDateTime,IsActive,CardTypeID,UserName,ClientIPAddress
)
SELECT
'1203151143',--mmddhhmmss TransactionID
'41679650', --ReservationNumber
'28800696', --ReservationNameID
'376214 **** 2002',--ENCRYPTBYKEY(KEY_GUID('SymWebCheckinKey'),@MaskedCardNumber),
'11/2024',--ENCRYPTBYKEY(KEY_GUID('SymWebCheckinKey'),@ExpiryDate),
'CREDIT',--ENCRYPTBYKEY(KEY_GUID('SymWebCheckinKey'),@FundingSource),
'394.25',--ENCRYPTBYKEY(KEY_GUID('SymWebCheckinKey'),@Amount),
'SGD',--ENCRYPTBYKEY(KEY_GUID('SymWebCheckinKey'),@Currency),
'W9DPVNSNT4L3BZ43',--ENCRYPTBYKEY(KEY_GUID('SymWebCheckinKey'),@RecurringIdentifier),
'842433',--ENCRYPTBYKEY(KEY_GUID('SymWebCheckinKey'),@AuthorisationCode),
'V2QSB59JPGC83VB2',--ENCRYPTBYKEY(KEY_GUID('SymWebCheckinKey'),@pspReferenceNumber),
'V2QSB59JPGC83VB2',--ENCRYPTBYKEY(KEY_GUID('SymWebCheckinKey'),@ParentPspRefereceNumber),
'2', --TransactionTypeID
'NULL',--ResultCode
'00 : Approved or completed successfully',--ResponseMessage
GETDATE(),--CreatedDateTime
'1',--IsActive
'5',--CardTypeID
'Service',--UserName
'NULL'--ClientIPAddress
Additionalinfo: update the values for keyhrader and keyvalues from the webcheckin nlog and took the corresponding reservation paymentid from the updated paymentheader table from saavypay and replace.
INSERT INTO [dbo].[tbPaymentAdditionalInfo] ([PaymentID] ,[KeyHeader] ,[KeyValue])
SELECT '46194', 'AID','A000000025010801'
UNION ALL
SELECT '46194', 'tid','62444775'
UNION ALL
SELECT '46194', 'transactionType','GOODS_SERVICES'
UNION ALL
SELECT '46194', 'backendGiftcardIndicator','false'
UNION ALL
SELECT '46194', 'posadditionalamounts.originalAmountValue','39425'
UNION ALL
SELECT '46194', 'expiryYear','2024'
UNION ALL
SELECT '46194', 'acquirerResponseCode','00 : Approved or completed successfully'
UNION ALL
SELECT '46194', 'authorisedAmountValue','39425'
UNION ALL
SELECT '46194', 'giftcardIndicator','false'
UNION ALL
SELECT '46194', 'posAmountGratuityValue','0'
UNION ALL
SELECT '46194', 'cardHolderName','LIP/NG FEE '
UNION ALL
SELECT '46194', 'expiryDate','11/2024'
UNION ALL
SELECT '46194', 'paymentMethodVariant','amex'
UNION ALL
SELECT '46194', 'pspReference','V2QSB59JPGC83VB2'
UNION ALL
SELECT '46194', 'refusalReasonRaw','00 : Approved or completed successfully'
UNION ALL
SELECT '46194', 'acquirerCode','Amex_Global_C'
UNION ALL
SELECT '46194', 'applicationPreferredName','AMERICAN EXPRESS'
UNION ALL
SELECT '46194', 'recurring.recurringDetailReference','W9DPVNSNT4L3BZ43'
UNION ALL
SELECT '46194', 'cardType','amex'
UNION ALL
SELECT '46194', 'iso8601TxDate','2022-12-03T07:14:55.0000000+0000'
UNION ALL
SELECT '46194', 'offline','false'
UNION ALL
SELECT '46194', 'posOriginalAmountValue','39425'
UNION ALL
SELECT '46194', 'recurring.shopperReference','28800696'
UNION ALL
SELECT '46194', 'txtime','15:14:55'
UNION ALL
SELECT '46194', 'authorisedAmountCurrency','SGD'
UNION ALL
SELECT '46194', 'cvcResult','0 Unknown'
UNION ALL
SELECT '46194', 'paymentMethod','amex'
UNION ALL
SELECT '46194', 'txdate','03-12-2022'
UNION ALL
SELECT '46194', 'avsResult','0 Unknown'
UNION ALL
SELECT '46194', 'recurring.contractTypes','"RECURRING,ONECLICK'
UNION ALL
SELECT '46194', 'shopperReference','28800696'
UNION ALL
SELECT '46194', 'startYear','2019'
UNION ALL
SELECT '46194', 'tc','345F924043DAFD7F'
UNION ALL
SELECT '46194', 'applicationLabel','AMERICAN EXPRESS'
UNION ALL
SELECT '46194', 'cardIssueNumber','0'
UNION ALL
SELECT '46194', 'cardSummary','2002'
UNION ALL
SELECT '46194', 'expiryMonth','11'
UNION ALL
SELECT '46194', 'merchantReference','41679650-28800696'
UNION ALL
SELECT '46194', 'mid','9800107716'
UNION ALL
SELECT '46194', 'posadditionalamounts.originalAmountCurrency','SGD'
UNION ALL
SELECT '46194', 'transactionReferenceNumber','V2QSB59JPGC83VB2'
UNION ALL
SELECT '46194', 'posAuthAmountCurrency','SGD'
UNION ALL
SELECT '46194', 'cardHolderVerificationMethodResults','5E0300'
UNION ALL
SELECT '46194', 'authCode','842433'
UNION ALL
SELECT '46194', 'adjustAuthorisationData','BQABAQByYyvzI2mv96k62M8/6OKuFpodzdcGT1RTs3h/OHNMyHeH7MlcUUprhqQ+zCTGmsR/8jltolD0a5UfeYNpa+sMpC8AwmKEnfqo8NXxkhreRI7xjne6cHWkIgRE0d2SyLgZXF6SK05/Y7pWB72cslRrQtnaO6Olq60qK5RzftJrCsnGJ7mc7fndmWFUgt9o+wtDkel5hE7SHJBOebCIR2QObmyXcbH9dU6MGPacVgLYkxHaLSs3+WGrUwcbkK+uVER0rCfWkKOaqF49GXiXWc7bj8ArFg5QkCnaMMOXrLdZGKvQQwcNQay6JQOm4oX0W1IlR+LrY4Dh3LtkB0R6mXnMEOs1ZGPVax8ibv15YVUisC0AAKENC6sh6DU0hIPItne8aWbBgGwc/OuHwKO2AidisJj6x7Y41GKNJCWTtpbqi9/FnTk8sC9kXerI9jLYdC9UT/3xDqfYmUz3xYfMib+eBLL7aKi7NF9lfKAnBS/j9Nmpm3vgyqh+7Y7wdSgiyouO3DnyQqJF0d/aKywCixEY909H9bGS6shcjk/lDLPRKqBrMsOEi4h4kLrfi1iGK1wly7e4bTAALIKJBj22YngZJWA+z6kGSIx2rQPLjXxe+Lv6E0bJ4otpGe8ZFax2RxHChpuNTijyFR6pmRtBmEf0NCkVhMJiuLmykbVVc9TptEXh/qCMV8hQti4m3CAczdYHgRG4KJu+RLRdrxrOyP784AKoyinbi5qRQOH8kHvvlRdwt7BINNuy5zZiDbTY8Kc7frodUkXnlrsvxUaW/j8hPsNUcQ7Nl+kA/3KKfAaNYDpAwXd1yRQ9RiYro5/9izM9zsdC399QWzRBE4g1YGSufQXJOoYZJRfw3rGTfQn8mk+AzyaTnF4BY/a2RWkfTO72PyNITMlaicaZPXmMSm0x1drahOvM'
UNION ALL
SELECT '46194', 'cardIssuerCountryId','702'
UNION ALL
SELECT '46194', 'posAmountCashbackValue','0'
UNION ALL
SELECT '46194', 'shopperCountry','SG'
UNION ALL
SELECT '46194', 'acquirerReference','003403198546320'
UNION ALL
SELECT '46194', 'fundingSource','CREDIT'
UNION ALL
SELECT '46194', 'posEntryMode','ICC'
UNION ALL
SELECT '46194', 'startMonth','12'
UNION ALL
SELECT '46194', 'cardBin','376214'
UNION ALL
SELECT '46194', 'cardScheme','amex'
UNION ALL
SELECT '46194', 'issuerCountry','SG'
UNION ALL
SELECT '46194', 'posAuthAmountValue','39425'
Prepared By Remesh