SAP Hana Transaction Notification error

[former_member813554]

Experts,

I added to an existing Transaction Notification and I think I'm missing something between the 2 IF statements. The first IF statement worked well.

I get this error:

Could not execute 'ALTER PROCEDURE SBO_SP_TransactionNotification ( in object_type nvarchar(30), -- SBO Object Type in ...'

SAP DBTech JDBC: [257]: sql syntax error: incorrect syntax near "IF": line 51 col 3 (at pos 1862)

Here is the Code I used:

ALTER PROCEDURE SBO_SP_TransactionNotification
(
	in object_type nvarchar(30), 				-- SBO Object Type
	in transaction_type nchar(1),			-- [A]dd, [U]pdate, [D]elete, [C]ancel, C[L]ose
	in num_of_cols_in_key int,
	in list_of_key_cols_tab_del nvarchar(255),
	in list_of_cols_val_tab_del nvarchar(255)
)
LANGUAGE SQLSCRIPT
SQL SECURITY INVOKER
AS
-- Return values
error  int;				-- Result (0 for no error)
error_message nvarchar (200); 		-- Error string to be displayed
invoice_cnt int;
delivery_cnt int;
begin

error := 0;
error_message := N'Ok';
invoice_cnt := 0;
delivery_cnt := 0;

--------------------------------------------------------------------------------------------------------------------------------

-- Block AR Invoice when AR control account does not match Samples and Warranty UDF
IF :object_type = N'13' AND :transaction_type = N'A' THEN 
	SELECT IFNULL(COUNT(*),0) INTO invoice_cnt
	FROM OINV T0
	INNER JOIN OACT T1 ON T0."CtlAccount" = T1."AcctCode"
	WHERE T0."DocEntry" = :list_of_cols_val_tab_del AND ((T0."U_RT" = 'Warranty' AND T1."FormatCode" <> '12250000') OR (T0."U_RT" = 'Samples' AND T1."FormatCode" <> '12200000'));
		IF :invoice_cnt > 0 THEN
			BEGIN
			error := 1;
			error_message := N'AR control account does not match Samples and Warranty UDF';
			END;
		END IF;
END IF;

invoice_cnt := 0;

-- Block AR Delivery when the SO UDF 'SO Hold' is "Shipping Hold" or "Credit Hold"

IF :object_type = N'15' AND :transaction_type = N'A' THEN
SELECT IFNULL(COUNT(*),0) INTO delivery_cnt
	FROM ODLN T0
		INNER JOIN DLN1 T1 ON T0."DocEntry" = T1."DocEntry"
		INNER JOIN ORDR T2 ON T1."BaseEntry" = T2."DocEntry"
		INNER JOIN RDR1 T3 ON T2."DocEntry" = T3."DocEntry"
	WHERE T0."DocEntry" = :list_of_cols_val_tab_del AND (T0."U_SO_Hold" = 'Credit Hold' OR T0."U_SO_Hold" = 'Shipping Hold')
		IF :invoice_cnt > 0 THEN
	
			BEGIN
			error := 1;
			error_message := N'Sales Order Hold (Shipping/Credit). Cannot create Delivery, until Hold is released';
			END;
		END IF;
END IF;

delivery_cnt := 0;

--------------------------------------------------------------------------------------------------------------------------------

-- Select the return values
select :error, :error_message FROM dummy;

end;

Your help will be much appreciated.Regards,Marli

[SonTran · Accepted solution]

Hi,

You missed ";" at the end of the statement

WHERE T0."DocEntry" = :list_of_cols_val_tab_del AND (T0."U_SO_Hold" = 'Credit Hold' OR T0."U_SO_Hold" = 'Shipping Hold');

Hope this helps,

Son Tran