topic Re: Modify internal table and do calculation in Query in Application Development and Automation Discussions

Modify internal table and do calculation in Query

susan_su2 — Mon, 04 Apr 2016 15:24:24 GMT

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

Re: Modify internal table and do calculation in Query

Former Member — Mon, 04 Apr 2016 15:47:46 GMT

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.

Re: Modify internal table and do calculation in Query

Former Member — Mon, 04 Apr 2016 15:51:43 GMT

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

Re: Modify internal table and do calculation in Query

Juwin — Mon, 04 Apr 2016 18:06:43 GMT

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

Re: Modify internal table and do calculation in Query

susan_su2 — Tue, 05 Apr 2016 05:17:03 GMT

Thanks Chandra for help.

I am not sure if POSNR must be populated as the billing cancellation is always on billing level. I am not sure how to populate that field though as the field name is not POSNR in VBFA.

The problem I have is, when I do "loop at it_sum into wa_sum", I find no data in we_sum (by checking table content during debug, while I can see data in it_sum and it_vbfa).

if i simply test: "if_sum-fkimg = it_sum-fkimg * -1." under loop, nothing happen.

if I test:

loop at it_sum.

select single vbtyp_n INTO wa_sum-vbtyp_n FROM it_vbfa WHERE vbeln = it_sum-vbeln.

endloop.

it says: "IT_VBFA" is not defined in the ABAP Dictionary as a table, projection.

I do not know how to solve this problem. Thanks.

Re: Modify internal table and do calculation in Query

susan_su2 — Tue, 05 Apr 2016 05:24:55 GMT

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.

Re: Modify internal table and do calculation in Query

former_member186741 — Tue, 05 Apr 2016 05:35:55 GMT

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.

Re: Modify internal table and do calculation in Query

susan_su2 — Tue, 05 Apr 2016 05:40:17 GMT

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

Re: Modify internal table and do calculation in Query

susan_su2 — Tue, 05 Apr 2016 05:51:19 GMT

Hi Neil,

The problem is, if I bring VBRK/VBRP into the join tables, records become duplicated in SQ01 because order line and billing line could be one to multiple relationship (there are batch splits). Same issue with LIKP and LIPS. So now I use these tables as alias tables then I won't get duplicates...

Susan

Re: Modify internal table and do calculation in Query

former_member186741 — Tue, 05 Apr 2016 07:03:27 GMT

Hi Susan, I think for you to get the correct information you are going to have to get the duplicates and then weed them out. I don't see how you can get the billing info without looking at the billing tables ( or for that matter the delivery data without the likp/lips info).

Re: Modify internal table and do calculation in Query

susan_su2 — Wed, 06 Apr 2016 05:50:45 GMT

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

Re: Modify internal table and do calculation in Query

susan_su2 — Wed, 06 Apr 2016 06:02:04 GMT

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