Certain businesses process may require payment to be made in advance. On payment receipt through the Incoming Payment module either by Bank, Credit Card or any other means that payment should be linked to the sales Order.
This will prevent the user from creating a Sales order with an amount that is greater than the amount received and vice versa.
In this case you will need to create a UDF for example U_ReceiptNo. on the Sales Order document. The user will need to add the Incoming payment document number on this field while doing the Sales order.
You therefore need to make this field (U_ReceiptNo) mandatory. Check out on the coming blog to see how to make this field Mandatory using SP_TransactionNotification.
To Block Sales order if the Order Amount is not equal to Receipt Amount
Follow the path to post the query:
Login to Microsoft SQL Server Management Studio
Expand Databases.
- Expand Your Database in this case (SVTL2022).
- Expand Programmability.
- Expand Stored Procedures.
- Scroll down to dbo.SBO_SP_TransactionNotification.
- Right Click on dbo.SBO_SP_TransactionNotification.
6. Click Modify
7. Paste Query in the section indicated above “Insert your query here”
8. Click Execute
The following SP_TransactionNotification can be used:
IF (@object_type = ’17’) and (@transaction_type IN (N’A’, N’U’))
BEGIN
IF EXISTS (SELECT T0.Docentry FROM ORDR T0 inner join ORCT T1 on T0.[U_ReceiptNo] = T1.[DocNum]
where T0.DocEntry = @list_of_cols_val_tab_del and (T0.[DocTotal] <> T1.[DocTotal]))
BEGIN
Select @error = -1, @error_message = N’Receipt Amount must be equal to sales order Amount’
END
END