(Back to Z-TS Transaction Notification SQL Scripts Posted to Wiki Listing -Z-TS Transaction Notification SQL Scripts Posted to Wiki )
AUTHOR NAME: Balakumar Viswanathan
PURPOSE AND BACKGROUND: This SQL was created in SAP 8.8 PL19. It can be used to prevent users from entering the marketing documents without base document and can be modifed as per the customer requirement.This script can be pasted in transaction notification procedure.
Copy and Paste this SQL:
--Z-TS Script to restrict Documents without Base documents Ver 1 VB 08 June,2011
--DESCRIPTION: Transaction notification SQL script can be used torestrict Documents without Base documents.
--Version 1 Balakumar Viswanathan 08 June, 2011
-- In the following example, we are going to see, how to block the AR invoice posting without Delivery for Item type invoices only.
--Restrict A/R Invoice w.o Delivery for 'Item' type only
IF @Object_type = N'13' and @transaction_type in (N'A', N'U') -- Object type 13 for AR invoice
DECLARE @line6 int --- Variable used to display the line number of the error row in the Error message
SELECT @line6 = (T1.LineNum + 1)
FROM OINV T0
INNER JOIN INV1 T1 on T0.DocEntry = T1.DocEntry
WHERE T1.DocEntry = @list_of_cols_val_tab_del
AND T1.[BaseType]!= N'15' --- Checks whether the base document is Delivery or not
AND T0.DocType = 'I' --- Checks only for 'Item' type documents only.
ORDER BY T1.LineNum
If (not ISNULL(@line6, 0) = 0) -- If any line found without base document
Set @error = 10
Set @error_message = N'Line ' + CONVERT(nvarchar(4), @line6) + N' is not copied from Delivery!'
ELSE -- Displays no error message
SET @error = 0