I have created a stored procedure to get the discount based on type of customer and amount as input fields. I want to use the stored procedure in my calculation view as calculated attribute. Is it doable?
I tried to run the stored procedure in SQL Editor with call function, it gives me error. How to use variables in SQL Editor?
Here is stored procedure
CREATE PROCEDURE SP_CAL_DISC( IN V_KUNNR VARCHAR(10) , IN V_AMT INTEGER) , OUT V_DISC INTEGER )
LANGUAGE SQLSCRIPT AS
V_ LAND1 VARCHAR(2);
-- Get Status
SELECT " LAND1" INTO V_ LAND1 FROM "ECC6"."KNA1"
WHERE "KUNNR" = :V_KUNNR ;
IF :V_ LAND1 = 'US' THEN
V_DISC := : V_AMT * 0.2;
V_DISC := : V_AMT * 0.1;
Now I want to use it in calculation view, but it does not work.
SQLA_VIEW = SELECT [ "MANDT",
SUM("NETWR ") AS NETWR,
var_out = CE_PROJECTION (:SQLA_VIEW ,
CE_CALC(CALL SP_CAL_DISC("KUNNR","NETWR",ZDISC),INTEGER)) AS ZDISC ] );
The approuch is not supported, you can't call a produce inside CE_CALC, maybe in future at least L functions can be called.
To move forward the best option for you is adjust the SP_CAL_DISC procedure to accept a table type with all customers at once, processing in lot and sending it back... On the calling routine you need to LEFT JOIN this result with the original source.
See it in general (I didn't tested syntax):
create type tt_cal_disc_in as table ( MANDT VARCHAR(3), KUNNR VARCHAR(10),
NETWR INTEGER,WAERK VARCHAR(10));
create type tt_cal_disc_out as table ( KUNNR VARCHAR(10), WAERK VARCHAR(10),DISC INTEGER);
create procedure sp_cal_disc( in input tt_cal_disc_in, out output tt_cal_disc_out ) language sqlscript as
output = select
case when ifnull(kna1.land1,'') = 'US' then :intab.netwr * 0.2 else :intab.netwr * 0.1 end as disc
from :input as intab left outer join ecc6.kna1 as kna1
on kna1.mandt = intab.mandt
and kna1.kunnr = intab.kunnr;
Inside your calc view, you need to prepare data, call procedure, join result to original data...
call sp_cal_disp( :SQLA_VIEW , lt_result );
lt_result = CE_LEFT_OUTER_JOIN(:SQLA_VIEW,:lt_result,["MANDT","KUNNR","WAERK"]);
var_out = CE_PROJECTION (:lt_result,
BTW: Review the datatypes you posted, not sure about NETWR as integer.
Regards, Fernando Da Ros
As per my knowledge there is no possibility to use stored procedure to build calculated attributes ,If it is there most of the data cleansing job can be done easily. the grammar which we use in calculated attributes itself is different .You can go through the below thread to see what all we can use as part of calculated attributes ,at the last Fernando provided the pointer which would be helpful,you can try that .But I doubt the possibility of using Stored procedure .Awaiting your feedback