When posting an A/R invoice, Users may want to an invoice with more quantity than the quantity on the Sales Order. To prevent this, use the query below to block posting the entry. The system will generate an error message to warn the user.
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
IF @transaction_type IN (N’A’, N’U’) AND (@Object_type = N’13’)
BEGIN
Declare @invoice int
SELECT @invoice = (LineNum + 1) FROM INV1
WHERE INV1.DocEntry = @list_of_cols_val_tab_del AND
((Quantity > BaseOpnQty) and (inv1.ItemCode != ‘LMF100451’)) AND
((Quantity > BaseOpnQty) and (inv1.ItemCode != ‘LMF100451’))
ORDER BY LineNum ASC
IF (not ISNULL(@invoice, 0) = 0)
BEGIN
SET @error = 10
SET @error_message = N’Invoiced quantity cannot exceed Sales order quantity ‘ + CONVERT(nvarchar(4), @invoice) + N’ !’
End
END