Skip to Content
avatar image
Former Member

Modify internal table and do calculation in Query

Hi experts,

My task is to create a sales order report via query to show sales order qty, delivery and billing qty. And I run into problem with getting the billing qty.

VBAK and VBAP are my main tables and I add VBFA as alias table to in order to find the billings. I create an additional field "fkimg". I can not just sum up the billing qty as there could be cancelled billing. So my intention is when vbfa-vbtyp_n = 'N' (cancelled billing), I shall take vbrp-fkimg (billing qty) as negative qty and then sum up the billing qty.

I define 3 internal tables it_vbfa, it_sum and wa_sum with same table structure, like below

DATA: BEGIN OF it_vbfa OCCURS 0,

vbeln TYPE vbeln,

posnr TYPE posnr,

fkimg TYPE fkimg,

vbtyp_n TYPE vbtyp_n,

END OF it_vbfa.


And I pull data it_vbfa and it_sum :


select vbeln posnr fkimg from vbrp into corresponding fields of table it_sum

where AUBEL = vbap-vbeln and AUPOS = vbap-posnr.


select * from vbfa into corresponding fields of table it_vbfa

where vbelv = vbak-vbeln and ( vbtyp_n = 'M' or vbtyp_n = 'N').


and I get the data into these two tables like this:


it_sum:

vbeln posnr fkimg vbtyp_n 90007086 10 1 90007086 11 0 90007088 10 1 90007088 11 0 90007089 10 1 90007089 11 0


it_vbfa:

vbeln posnr fkimg vbtyp_n 90007086 0 0 M 90007086 0 0 M 90007088 0 0 N 90007088 0 0 N 90007089 0 0 M 90007089 0 0 M


I wanted to merge them into one table and then do the calculation and sum as said above, but I do not know how to do it.

Your direction would be very much appreciated!

Susan




Add comment
10|10000 characters needed characters exceeded

  • Follow
  • Get RSS Feed

4 Answers

  • Apr 04, 2016 at 03:47 PM

    First make sure that the POSNR value is populated correctly in it_vbfa.

    Then do the following (do minor modifications where necessary):

    loop at it_sum into wa_sum.

    read table it_vbfa into wa_vbfa

    with key vbeln = wa_sum-vbeln

    posnr = wa_sum-posnr.

    if wa_vbfa-vbtyp_n = 'N'.

    wa_sum-fkimg = wa_sum-fkimg * -1. " Make it negative

    endif.

    "Add the record to new internal table

    read table it_final assigning <fs_final>

    with key vbeln = wa_sum-vbeln.

    if sy-subrc = 0.

    " Already record exists

    <fs_final>-billing_qty = <fs_final>-billing_qty + wa_sum-fkimg.

    else.

    wa_final-vbeln = wa_sum-vbeln.

    wa_final-billing_qty = wa_sum-fkimg.

    append wa_final to it_final.

    endif.

    endloop.

    Add comment
    10|10000 characters needed characters exceeded

    • Former Member

      Thanks Chandra and everyone's kind help.

      The codes works! It was my misunderstanding that I thought "loop at it_sum into wa_sum...." didn't work as I saw nothing in table content of wa_sum. When I continue finishing the coding and run sq01 data get populated!

      Thanks very much!

      Susan

  • avatar image
    Former Member
    Apr 04, 2016 at 03:51 PM

    How about a join in your SELECT statement? So you don't need two tables and an intersection of them.

    If you still want to use both tables, I suggest to use the FILTER operator. But it's available only since NW 7.4 SP08 (see ABAP News for 7.40, SP08 - More for Internal Tables

    Add comment
    10|10000 characters needed characters exceeded

    • Former Member

      Hi Armin,

      I do not really want to use two tables. If I can do the calculation within internal table of vbrp that would be better. Just I do not know how. Would you give me sample codes? Thanks a lot!

      Susan

  • Apr 04, 2016 at 06:06 PM

    You dont need to check vbfa-vbtyp_n = 'N'.

    While selecting invoices, just exclude VBRK-FKSTO = 'X', that will automatically exclude all cancelled invoices.

    Thanks,

    Juwin

    Add comment
    10|10000 characters needed characters exceeded

    • Former Member

      Thanks Juwin.

      I did try first to use VBRK to get that cancellation indicator. The problem is I never get data from VBRK successfully with select statement. I suspect because query can only link to tables that has direct relation with main tables. i.g. my main tables are vbak and vbap. Only vbfa and vbrp have direct linkage with vbak and vbap. (in same table record I can find both sales order number and billing number.) But VBRK I can only find the billing number via vbfa or vbrp not direct via vbak/vbap.

  • Apr 05, 2016 at 05:35 AM

    I think you need to bring VBRK/VBRP into the picture. VBAK/VBAP is sales dox, VBRK/P is billing dox. VBFA is the link between the two...it is used by the document flow functions.

    Add comment
    10|10000 characters needed characters exceeded

    • Former Member Neil Woodruff

      Hi Neil,

      You are right VBRK is better. Using VBFA there are lot of scenario to be considered and making coding more complicated. Since I have solved my problem. I would want to try your way in my next query. Let me study on removing duplication...Thank you!

      Susan