Intro

I did already some blog posts about SAP transaction notifications (Part1, Part2, Part3). This blog shows a specific kind of transaction notification when you need to validate something on different documents. Instead of doing for each document an own query, this one shows you how to solve it in a more dynamic way.

Example

I use a really simple example to illustrate the way how the transaction notification works. The goal is, to prevent on all marketing documents entering the string “Hello World” in the remarks field.

Head script

To use dynamic tables like OQUT for quotation or ORDR for sales order, there is a script, which should always be placed on top of the transaction notification. The script checks the object type and defines the corresponding SQL tables:

--    ADD    YOUR    CODE    HERE

/*************************************************************************/
-- Set table variables for usage in multi-document rules, must be in the begin of transaction notification
/*************************************************************************/

DECLARE @tableHead AS nvarchar(4)
DECLARE @tableLine AS nvarchar(4)
DECLARE @sqlTN AS nvarchar(max)
DECLARE @sqlParameterTN AS nvarchar(50)
DECLARE @returnValueTN AS int

SET @sqlParameterTN = N'@SQLResultTN int OUTPUT'

-- Set table variables for marketing documents (sales and purchase)
IF @object_type = '23' BEGIN SET @tableHead = 'OQUT' SET @tableLine = 'QUT1' END -- Quotation
ELSE IF @object_type = '17' BEGIN SET @tableHead = 'ORDR' SET @tableLine = 'RDR1' END -- Sales Order
ELSE IF @object_type = '15' BEGIN SET @tableHead = 'ODLN' SET @tableLine = 'DLN1' END -- Delivery
ELSE IF @object_type = '16' BEGIN SET @tableHead = 'ORDN' SET @tableLine = 'RDN1' END -- Return
ELSE IF @object_type = '203' BEGIN SET @tableHead = 'ODPI' SET @tableLine = 'DPI1' END -- Down Payment Request / Invoice
ELSE IF @object_type = '13' BEGIN SET @tableHead = 'OINV' SET @tableLine = 'INV1' END -- A/R Invoice
ELSE IF @object_type = '14' BEGIN SET @tableHead = 'ORIN' SET @tableLine = 'RIN1' END -- A/R Credit Memo
ELSE IF @object_type = '1470000113' BEGIN SET @tableHead = 'OPRQ' SET @tableLine = 'PRQ1' END -- Purchase Request
ELSE IF @object_type = '540000006' BEGIN SET @tableHead = 'OPQT' SET @tableLine = 'PQT1' END -- Purchase Quotation
ELSE IF @object_type = '22' BEGIN SET @tableHead = 'OPOR' SET @tableLine = 'POR1' END -- Purchase Order
ELSE IF @object_type = '20' BEGIN SET @tableHead = 'OPDN' SET @tableLine = 'PDN1' END -- Goods Receipt PO
ELSE IF @object_type = '21' BEGIN SET @tableHead = 'ORPD' SET @tableLine = 'RPD1' END -- Goods Return
ELSE IF @object_type = '204' BEGIN SET @tableHead = 'ODPO' SET @tableLine = 'DPO1' END -- A/P Down Payment Request / Invoice
ELSE IF @object_type = '18' BEGIN SET @tableHead = 'OPCH' SET @tableLine = 'PCH1' END -- A/P Invoice
ELSE IF @object_type = '19' BEGIN SET @tableHead = 'ORPC' SET @tableLine = 'RPC1' END -- A/P Credit Memo

Transaction validation script

In the validation script it is important to declare the desired object types where something needs to be blocked. Important is, that the query which you design contains the part SELECT @SQLResultTN = COUNT. In the last lines of this code example, the error message is triggered when the count is not 0.

/*************************************************************************/
-- Block "hello world" in documents remarks
/*************************************************************************/

-- For @object_type please choose the documents where you want to block
-- For @transaction_type select which operations should trigger the script
IF @object_type IN ('23', '17', '15', '16', '203', '13', '14', '1470000113', '540000006', '22', '20', '21', '204', '18', '19') AND @transaction_type IN ('A', 'U')
BEGIN
    SET @sqlTN =
        'SELECT @SQLResultTN =
        COUNT(*)
        FROM ' + @tableHead + ' T0
        WHERE T0.Comments = ''hello world''
        AND T0.ObjType = ' + @object_type + '
        AND CAST(T0.DocEntry AS NVARCHAR(255)) = ' + @list_of_cols_val_tab_del

    EXEC sp_executesql @sqlTN, @sqlParameterTN, @SQLResultTN = @returnValueTN OUTPUT

    IF ISNULL(@returnValueTN,0) != 0
    BEGIN
        SET @error = -1
        SET @error_message = 'Transaction Notification XY: You are not allowed to write Hello World in this field'
    END
END

Result

The code snipped above is triggered on 15 different marketing documents. Instead of repeating the code 15 times (with other tables in the FROM part), there is only one query which solves our task. If you have more validations, the whole transaction notification is better to read and better to maintain.