Skip to Content
avatar image
Former Member

SAP Hana calculation view distribute quantities example

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

Add comment
10|10000 characters needed characters exceeded

  • Get RSS Feed

1 Answer

  • Aug 14, 2017 at 07:15 PM

    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.

    Add comment
    10|10000 characters needed 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;