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
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.
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;