Skip to Content
0
Former Member
Jun 21, 2016 at 02:56 PM

Stored procedure in HANA

98 Views

hello All

I converted the following stored procedure in HANA in the SBOTRANSACTION NOTIFICATION, but I am getting errors.

please can someone assist with this :

CREATE PROCEDURE "TEST".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 AS -- Return values

temp_var_0 int; -- user variable

cnt int;

error int; -- Result (0 for no error)

error_message nvarchar (200); -- Error string to be displayed

TruckNo nvarchar(30);

NewLoadNoYN nvarchar(1);

DocDate timestamp;

LoadID integer ;

begin error := 0;

error_message := N'Ok';

----

IF (:transaction_type = 'A' AND :object_type = '15') THEN

--Insert Load Sheet starts here. Trigger on Delivery Note ------

BEGIN

--assigns values

SELECT "U_TRUCK", "U_UseNewLoadNo", "DocDate"

INTO TruckNo, NewLoadNoYN, DocDate

FROM ODLN

WHERE "DocEntry" = :list_of_cols_val_tab_del;

--gets the Load ID for the current Truck and current date

if (exists(Select U_LoadID

from "@LOADSHEET"

where U_LoadingDate = :DocDate

and U_Truck = :TruckNo)

) then

begin

SELECT TOP 1 "U_LoadID" INTO LoadID

FROM "@LOADSHEET"

WHERE "U_LoadingDate" = :DocDate

AND "U_Truck" = :TruckNo

ORDER BY "U_LoadID" DESC;

if (:NewLoadNoYN = 'N') then

begin

INSERT INTO "@LOADSHEET"

("Code", "Name", "U_LoadID", "U_LoadingDate", "U_Truck", "U_DeliveryNo")

VALUES

(:list_of_cols_val_tab_del

,LEFT('Truck No. ' + :TruckNo + ' on Delivery ' + :list_of_cols_val_tab_del,30)

,:LoadID

,:DocDate

,:TruckNo

,:list_of_cols_val_tab_del);

end;

if (:NewLoadNoYN = 'Y') then

begin

--get the new load number

SELECT TOP 1 "U_LoadID" INTO LoadID

FROM "@LOADSHEET"

WHERE "U_LoadingDate" = :DocDate

AND "U_Truck" = :TruckNo

ORDER BY "U_LoadID" DESC;

INSERT INTO "@LOADSHEET"

("Code", "Name", "U_LoadID", "U_LoadingDate", "U_Truck", "U_DeliveryNo")

VALUES

(:list_of_cols_val_tab_del

,:list_of_cols_val_tab_del

,(:LoadID + 1)

,:DocDate

,:TruckNo

,:list_of_cols_val_tab_del);

end;

end if;

else

begin

-- if the truck number does not exist at all

--get the new load number

SELECT TOP 1 "U_LoadID" INTO LoadID

FROM "@LOADSHEET"

WHERE "U_LoadingDate" = :DocDate

AND "U_Truck" = :TruckNo

ORDER BY "U_LoadID" DESC;

--if (LoadID = 0 or LoadID is null) then

-- :LoadID = 1;

--else

-- :LoadID = :LoadID + 1;

INSERT INTO "@LOADSHEET"

("Code", "Name", "U_LoadID", "U_LoadingDate", "U_Truck", "U_DeliveryNo")

VALUES

(list_of_cols_val_tab_del

,LEFT('Truck No. ' + :TruckNo + ' on Delivery ' + list_of_cols_val_tab_del,30)

,:LoadID

,:DocDate

,:TruckNo

,:list_of_cols_val_tab_del);

end;

end if;

---------------------------------------------------------------------- -- Select the return values

select

:error,

:error_message

FROM dummy;

end;