| Description |
As what had been discussed, since there's already a call to uspCMBankTransactionReversal, the delete statements are no longer needed. The AR script needs to be adjusted.
*Obsolete:*
*"'* Provide scripts for Deleting Bank transactions during Payment unpost."
Requirements:
1.Currently, we have this statement in our Payment Posting script. This is being executed during unpost:
{noformat} --DELETE IF NOT CHECK PAYMENT AND DOESN'T HAVE CHECK NUMBER
DELETE FROM tblCMBankTransaction
WHERE strTransactionId IN (
SELECT strRecordNumber
FROM tblARPayment
INNER JOIN tblSMPaymentMethod ON tblARPayment.intPaymentMethodId = tblSMPaymentMethod.intPaymentMethodID
WHERE intPaymentId IN (SELECT intPaymentId FROM @ARPaymentPostData)
AND tblSMPaymentMethod.[strPaymentMethod] != 'Check'
OR (ISNULL(tblARPayment.strPaymentInfo,'') = '' AND tblSMPaymentMethod.[strPaymentMethod] = 'Check')
)
DELETE FROM tblCMUndepositedFund
WHERE
intUndepositedFundId IN
(
SELECT
B.intUndepositedFundId
FROM
tblARPayment A
INNER JOIN
@ARPaymentPostData P
ON A.[intPaymentId] = P.[intPaymentId]
INNER JOIN
tblCMUndepositedFund B
ON A.[intPaymentId] = B.intSourceTransactionId
AND A.[strRecordNumber] = B.strSourceTransactionId
LEFT OUTER JOIN
tblCMBankTransactionDetail TD
ON B.intUndepositedFundId = TD.intUndepositedFundId
WHERE
B.strSourceSystem = 'AR'
AND TD.intUndepositedFundId IS NULL
){noformat}
If this is still valid, we need a stored procedure that will hold this. The script should be able to handle batches. And instead of triggering an error with sql's 'RaiseError()'. The script should continue updating the valid records and return a list for the invalid ones using this format:
* intTransactionId Int
* strTransactionId Nvarchar(50)
* strErrorMessage Nvarchar(250)
Also, there should be an option to skip validation when the sp is called. We'll do the validation before the call using the function that will be created for #2.
2. Provide a sql function for validation: This should be the same validation the will be executed in the stored procedure that will be created for #1. This should return a list of all the transactions that will fail validation using the same format in #1.
|