Introduction

Some time ago, I did 2 blog posts to introduce the SAP Business transaction notification:

  • SAP Business One Transaction Notification Part 1
    How the transaction notification works, How to build a debug table
  • SAP Business One Transaction Notification Part 2
    Example for a price list validation

This post is just a small example for creating a transaction notification on multiple sales documents. With the simple EXISTS expression, I saved some code lines.

HINT: Meanwhile there is another blog post with a more dynamic way, if you need to validate lots of documents: Dynamic SAP Business One Transaction Notification

Transaction notification

This script is interesting, if you need to validate for example the distribution rule 1 and distribution rule 2 on more documents. For example quotations and sales orders. Instead of creating 2 code blocks in the transaction notification, it is possible to create just one. Following points help to make the code simple:

  • In the first line, include all the object types, where you want to run the transaction notification. In my case, it is ’23’ and ’17’, which means quote and order
  • I use the IF EXISTS statement. If there is only one row in the following expression, the transaction notification is triggered
  • In all those UNION ALL statements, it is just necessary to adapt the table header and line, for example OQUT and QUT1 in case of quote or ORDR and RDR1 in case of order
  • In the last line of each UNION ALL statement, the object types are compared –> T0.ObjType = @object_type. This line helps that only the UNION ALL expression is triggered which is really relevant.
IF @object_type IN ('23','17') AND @transaction_type IN ('A', 'U') --Quotes and orders
BEGIN
    IF EXISTS
    (
        -- Quotation
        SELECT T0.DocEntry
        FROM OQUT T0
        INNER JOIN QUT1 T1 ON T0.DocEntry = T1.DocEntry
        WHERE (ISNULL(T1.OcrCode,'') = '' OR ISNULL(T1.OcrCode2,'') = '')
        AND CAST(T0.DocEntry AS NVARCHAR(255)) = @list_of_cols_val_tab_del
        AND T0.ObjType = @object_type

        UNION ALL

        -- Sales Order
        SELECT T0.DocEntry
        FROM ORDR T0
        INNER JOIN RDR1 T1 ON T0.DocEntry = T1.DocEntry
        WHERE (ISNULL(T1.OcrCode,'') = '' OR ISNULL(T1.OcrCode2,'') = '')
        AND CAST(T0.DocEntry AS NVARCHAR(255)) = @list_of_cols_val_tab_del
        AND T0.ObjType = @object_type

    )
    BEGIN
        SET @error = -1
        SET @error_message = 'Transaction Notification : Dimension 1 and 2 is mandatory'
    END
END

Geri Grenacher

ERP Project manager at coresystems ag, Switzerland

6 Comments

Gerardo Orozco · August 11, 2015 at 22:45

Hi, how do we to denny duplicate sales order?
same cardname, same itemcode and quantity, and sales orders is open
we need to denny this, best regards

Manish · September 17, 2015 at 13:15

Great.. I want to put validation that once a master data has been used in transaction block it so that no one can update or remove it ,is it possible through SP Transaction notification.

Geri Grenacher · October 16, 2015 at 10:14

@Manish: Yes, that should be not a problem. But SAP anyway blocks the removing of items when they are used in documents. So you just need to take care about the update.

SAP Business One Transaction Notification Part 1 | Geri Grenacher · July 31, 2014 at 09:57

[…] Business One Transaction Notification Part 3 Example for a validation on multiple sales […]

SAP Business One Transaction Notification Part 2 | Geri Grenacher · July 31, 2014 at 09:58

[…] Business One Transaction Notification Part 3 Example for a validation on multiple sales […]

Dynamic SAP Business One Transaction Notification | Geri Grenacher · November 14, 2014 at 09:29

[…] 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 […]

Leave a Reply

Avatar placeholder

Your email address will not be published. Required fields are marked *