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