04-04-2016 4:24 PM
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
04-04-2016 4: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.
04-05-2016 6:17 AM
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.
04-06-2016 6:50 AM
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
04-04-2016 4:51 PM
04-05-2016 6:40 AM
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
04-04-2016 7: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
04-05-2016 6:24 AM
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.
04-05-2016 6: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.
04-05-2016 6:51 AM
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
04-05-2016 8:03 AM
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).
04-06-2016 7:02 AM
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