Skip to end of metadata
Go to start of metadata

(Back to Z-TS Transaction Notification SQL Scripts Posted to Wiki Listing -Z-TS Transaction Notification SQL Scripts Posted to Wiki )

TYPE: Frequently Requested SQL

AUTHOR NAME: Neetu Dhami

PURPOSE AND BACKGROUND:  This SQL was created in 2007A SP01 PL08. It can be used to set field mandatory in SAP Business in trsansaction notification procedure and can be modifed as per the customer requirement. As It is not possible to set field mandatory , as a workarround we can use SQL script in transaction notification procedure.

Copy and Paste this SQL:

--Z-TS Setting Mandatory Field Ver 1 ND 01 June,2011

--DESCRIPTION:  Transaction notification SQL script can be used to set project code mandatory field on all marketing and stock transaction documents and can be modified as required.

--Version 1 Neetu Dhami 01 June, 2011

DECLARE @tableName NVARCHAR(4), @lineTableName NVARCHAR(4), @sqlString NVARCHAR(4000), @Output INT,@DocEntry INT
DECLARE @DocumentInfo TABLE( ObjectType INT,     TableName NVARCHAR(4), LinesTableName NVARCHAR(4))
INSERT INTO @DocumentInfo VALUES(23, 'OQUT', 'QUT1') -- Sales Quotation
INSERT INTO @DocumentInfo VALUES(17, 'ORDR', 'RDR1') -- Sales Order
INSERT INTO @DocumentInfo VALUES(15, 'ODLN', 'DLN1') -- Delivery
INSERT INTO @DocumentInfo VALUES(16, 'ORDN', 'RDN1') -- Return
INSERT INTO @DocumentInfo VALUES(13, 'OINV', 'INV1') -- AR Invoice
INSERT INTO @DocumentInfo VALUES(14, 'ORIN', 'RIN1') -- AR Credit Note
INSERT INTO @DocumentInfo VALUES(22, 'OPOR', 'POR1') -- Purchase Order
INSERT INTO @DocumentInfo VALUES(20, 'OPDN', 'PDN1') -- Goods Receipt PO
INSERT INTO @DocumentInfo VALUES(21, 'ORPD', 'RPD1') -- Goods Return
INSERT INTO @DocumentInfo VALUES(18, 'OPCH', 'PCH1') -- AP Invoice
INSERT INTO @DocumentInfo VALUES(59, 'OIGN', 'IGN1') -- Goods Receipt
INSERT INTO @DocumentInfo VALUES(60, 'OIGE', 'IGE1') -- Goods Issue
INSERT INTO @DocumentInfo VALUES(67, 'OWTR', 'WTR1') -- Stock Transfer
IF @Object_Type IN (SELECT ObjectType FROM @DocumentInfo) AND @transaction_type IN ('A', 'U')
      SET @DocEntry = cast (@list_of_cols_val_tab_del as int)
      SELECT TOP 1 @tableName = TableName, @lineTableName = LinesTableName FROM @DocumentInfo WHERE ObjectType = @Object_Type
      SET @sqlString = 'SELECT @result = COUNT(1) FROM ' +  @lineTableName + ' T0 INNER JOIN ' +  @tableName + ' T1 ON T1.DocEntry = T0.DocEntry

      WHERE T0.DocEntry = ' + CAST(@DocEntry AS NVARCHAR(80)) + ' AND ISNULL(T0.Project, '''') = '''''
      EXEC sp_executeSql @sqlString, N'@result (mailto:N'@result) INT OUTPUT', @Output OUTPUT
      IF @Output > 0
            SELECT @error = 1, @error_message = N'Project Code is mandatory!'

  • No labels