Skip to Content
0

SAP Hana calculation view distribute quantities example

Aug 14, 2017 at 04:57 AM

130

avatar image
Former Member

Hello experts,

I am working on the scenario below, where I have two tables TableA and TableB and the final result needed is "Result". Join between TableA and TableB is by SalesOrd and Line fields.

Please see the screen shot below on the required result needed. Delivered Quantity (Del.Qty) needs be distributed among various schedule lines. Since the first schedule line has only 100 quantity, we need to subtract only 100 and carry over 50 to subtract from the next schedule line.

Is this possible to achieve using Calculation view? If yes, please help me with your ideas. Or do we need a table function? In case of table function we need to loop using cursor?

Thanks for your help.

Regards

Ravi

10 |10000 characters needed characters left characters exceeded
* Please Login or Register to Answer, Follow or Comment.

1 Answer

avatar image
Former Member Aug 14, 2017 at 07:15 PM
0

You are right a scripted view would work and you might have to iterate through every row.

Let us know what you have tried so far to achieve this. That would help people help you better.

Show 1 Share
10 |10000 characters needed characters left characters exceeded
Former Member

Thanks for your reply. So far I tried creating a new Table function, code below:

I have a calculation view z_rep/CV_OPENSCH_LINES1_C which has Delivered Qty (delqty) and Scheduled Quantity "bmeng". I am trying to loop and change "bmeng" but getting a syntax error at:

Syntax error := is incorrect or misplaced. Not sure if I am on the right track. Also how can return the table function output in this case.

r1.bmeng := r.bmeng - remqty;
FUNCTION "RCHITTUM"."functions::adjust_delivery_quantity_schlines" ( ) 
RETURNS "_SYS_BIC"."zrep/CV_OPENSCH_LINES1_C"
LANGUAGE SQLSCRIPT
SQL SECURITY INVOKER AS
BEGIN
/***************************** 
Write your function logic
 *****************************/
declare remqty decimal(15,3) := 0;
declare vbeln varchar(10);
declare cursor c_cursor1 for
select * from "_SYS_BIC"."z_rep/CV_OPENSCH_LINES1_C";
for r1 as c_cursor1 do
if vbeln <> r1.vbeln
then
remqty := r1.delqty;
end if;
r1.bmeng := r.bmeng - remqty;
return
r1;
end for;
END;
0