topic Re: SAP Query Difficulties in Application Development and Automation Discussions

SAP Query Difficulties

benjamin_allsopp — Wed, 06 Aug 2014 10:19:10 GMT

I have a requirement which I'm not quite sure how to get around.

I'm writing a query based on Sales Documents and Delivery Documents.

I am using table joins between VBAK, VBAP, VBFA, LIKP, LIPS, and VBUP.

The output I want is to display the shortage on deliveries compared to sales orders where applicable. I calculate this by Sales Order Quantity - Delivery Quantity.

I have nearly been able to get the output I want, however consider this scenario:

A customer orders 20 of a particular item.

10 of these are packed and delivered. Then, in the same night, the rest of the items are packed and delivered.

This means there is one item on the Sales Order with quantity 20, and two items on the delivery notes of 10 and 10.

The output I receive in this instance is as such:

Depot	Material	Description	Short or Over	Sales Order Qty	Delivery Qty	Sales Order No	Sales order Item
FAVERSHAM	111111	Material 1	1	33	32	443011	60
FAVERSHAM	111111	Material 1	32	33	1	443011	60
CREWE	111112	Material 2	1	10	9	443020	50
CREWE	111112	Material 3	4	15	11	443020	10

Material 1 hasn't had a shortage...it's just been delivered in two separate packs of 1 and 32 to add up to the order quantity of 33, so it shouldn't be displayed at all.

Materials 2 and 3 are displaying fine because there's only one delivery note assigned to them thus far.

How can I get around this issue?

Thanks in advance.

Re: SAP Query Difficulties

Lakshmipathi — Wed, 06 Aug 2014 10:28:45 GMT

Standard Transaction VA05 is having the option to select "Open Sale Orders". Explore this standard transaction and modify to suit the requirement

G. Lakshmipathi

Re: SAP Query Difficulties

Former Member — Wed, 06 Aug 2014 10:29:42 GMT

Hi,

Your Requirement is Sales Order Qty(Line Item) - Sum Of Delivery order Qty.

If you are using Join then use aggregate function like Max(sales order line item Qty),Sum (Delivery Item Qty) & Don't Include Line item of Delivery in select query.

Re: SAP Query Difficulties

Former Member — Wed, 06 Aug 2014 10:34:52 GMT

I believe, you need to group your result set by depot, material, description, sales order and sales order item. Delivery qty should be calculated as a SUM, Sales Order Qty can be an AVG or MIN/MAX, whatever. If you need help in re-writing your query, please, post the code here.

Re: SAP Query Difficulties

vamsilakshman_pendurti — Wed, 06 Aug 2014 11:17:52 GMT

It is based on delivery items....

so how many items had been delivered and how much pending that information you should gather first..

for example wa_final is the workarea for your final internal table and WA_VBAK is the SALES ORDER table and WA_LIPS is the Delivery details table workarea....

U should loop the Delivery table inside of main table then count the delivery quantity details...of material......

See for clarity....Rough coding is below....

loop at it_vbak.

loop at it_lips into wa_lips.

if sy-tabix = 1.

wa_final-Sales_ord = wa_vbak-Sales_ord.

l_sum = wa_lips-del_qty.

wa_final-del_qty = wa_lips-del_qty.

wa_final-short = wa_vbak-sales_ord - wa_lips-del_qty.

wa_del_sent = wa_lips-del_qty.

else .

wa_final-Sales_ord = wa_vbak-Sales_ord.

l_sum = wa_del_sent + l_sum. ( each time delivery qty will added to l_Sum )

wa_final-short = wa_vbak-sales_ord - l_sum. ( here subtract that added qty with actual Sales Order )

endif.

endloop.

like this we can fill the output table adding all delivery quantities and subtract from actual Sales orders..

Hope you understand,

Vamsilakshman.p

Re: SAP Query Difficulties

benjamin_allsopp — Fri, 08 Aug 2014 12:59:35 GMT

I am carrying this report out in SQ01.

All the joins have been achieved with the standard infoset functionality.

Within a custom field 'SHORTS' I have the following code (I know it's not right but can't figure out how to write the correct code in to achieve desired results):

DATA:

begin of WA_FINAL,

VBELV like VBFA-VBELV,

POSNV like VBFA-POSNV,

VBELN like VBFA-VBELN,

POSNN like VBFA-POSNN,

KWMENG like VBAP-KWMENG,

LFIMG like LIPS-LFIMG,

end of WA_FINAL,

I_FINAL like standard table of WA_FINAL with header line,

l_sum like LIPS-LFIMG,

wa_del_sent like LIPS-LFIMG.

SELECT

VBELV POSNV VBELV POSNV

INTO

I_FINAL

FROM

VBFA.

ENDSELECT.

SELECT

KWMENG

FROM

VBAP

INTO

I_FINAL for all entries in I_FINAL

WHERE

VBELN = I_FINAL-VBELV AND POSNR = I_FINAL-POSNV.

ENDSELECT.

SELECT

LFIMG

FROM

LIPS

INTO

I_FINAL for all entries in I_FINAL

WHERE

VBELN = I_FINAL-VBELN AND POSNR = I_FINAL-POSNN.

ENDSELECT.

loop at I_FINAL into WA_FINAL.

if sy-tabix = 1.

l_sum = WA_FINAL-LFIMG.

WA_FINAL-LFIMG = WA_FINAL-LFIMG.

SHORTS = WA_FINAL-KWMENG - WA_FINAL-LFIMG.

wa_del_sent = WA_FINAL-LFIMG.

else .

l_sum = wa_del_sent + l_sum.

SHORTS = WA_FINAL-KWMENG - l_sum.

endif.

endloop.

Re: SAP Query Difficulties

benjamin_allsopp — Fri, 08 Aug 2014 14:43:16 GMT

Newest code (seen below) is populating the table I_FINAL with the first select statement .

However when it comes to populate KWMENG and LFIMG, there doesn't seem to be any data pulling through from VBAP-KWMENG and LIPS-LFIMG. (I know this from inserting a break (as seen in the code) and reviewing the fields in debugger)

How can I resolve this?

DATA:

begin of WA_FINAL,

VBELV like VBFA-VBELV,

POSNV like VBFA-POSNV,

VBELN like VBFA-VBELN,

POSNN like VBFA-POSNN,

KWMENG like VBAP-KWMENG,

LFIMG like LIPS-LFIMG,

end of WA_FINAL,

I_FINAL like standard table of WA_FINAL with header line,

l_sum like LIPS-LFIMG,

wa_del_sent like LIPS-LFIMG.

CLEAR I_FINAL.

SELECT

VBELV POSNV VBELN POSNN

INTO

I_FINAL

FROM VBFA

WHERE

VBELV = VBAP-VBELN AND

POSNV = VBAP-POSNR AND

VBELN = LIPS-VBELN AND

POSNN = LIPS-POSNR.

ENDSELECT.

BREAK.

SELECT SINGLE

KWMENG

FROM

VBAP

into

I_FINAL-KWMENG

WHERE

VBELN = I_FINAL-VBELV AND POSNR = I_FINAL-POSNV.

SELECT SINGLE

LFIMG

FROM

LIPS

INTO

I_FINAL-LFIMG

WHERE

VBELN = I_FINAL-VBELN AND POSNR = I_FINAL-POSNN.

loop at I_FINAL into WA_FINAL.

if sy-tabix = 1.

l_sum = WA_FINAL-LFIMG.

WA_FINAL-LFIMG = WA_FINAL-LFIMG.

SHORTS = WA_FINAL-KWMENG - WA_FINAL-LFIMG.

wa_del_sent = WA_FINAL-LFIMG.

else .

l_sum = wa_del_sent + l_sum.

SHORTS = WA_FINAL-KWMENG - l_sum.

endif.

endloop.

Re: SAP Query Difficulties

Former Member — Mon, 11 Aug 2014 06:16:44 GMT

First of all, in our SELECT SINGLE queries you use I_FINAL-VBELN, I_FINAL_POSNN and so on. This will not work since I_FINAL header workarea is not filled yet (check the "WITH HEADER LINE" docs). You need to change the code like the following:

LOOP AT I_FINAL.

SELECT SINGLE KWMENG FROM VBAP

into I_FINAL-KWMENG

WHERE

VBELN = I_FINAL-VBELV AND POSNR = I_FINAL-POSNV.

...

ENDLOOP.

Secondly, I still think that a single DB query with grouping will be more effective than a bunch of queries inside a LOOP. Your query should be something like the following:

SELECT

vbap~vbeln

vbap~posnr

vbap~matnr

vbap~kwmeng

SUM( lips~lfimg ) AS lfimg

UP TO 3 ROWS

INTO CORRESPONDING FIELDS OF TABLE it_data

FROM

vbap

INNER JOIN vbfa ON vbfa~vbelv = vbap~vbelv AND vbfa~posnv = vbap~posnr

LEFT JOIN lips ON vbfa~vbeln = lips~vbeln AND vbfa~posnn = lips~posnr

WHERE

vbfa~vbtyp_v = 'C'

AND vbfa~vbtyp_n = 'J'

AND vbfa~bwart = '601'

GROUP BY

vbap~vbeln

vbap~posnr

vbap~matnr

vbap~kwmeng.

NB: I'm a bit new to SAP, so, please, double-check the WHERE conditions, I may be missing or misunderstanding something.

Re: SAP Query Difficulties

benjamin_allsopp — Mon, 11 Aug 2014 08:09:52 GMT

Using the suggested code (the group by method) I am still not seeing success with populating the quantity fields...

as you can see above, the fields populating the internal table are present and correct but the quantities are 0.000 which isn't correct.

Re: SAP Query Difficulties

Former Member — Mon, 11 Aug 2014 08:40:18 GMT

As I have already mentioned, you may need to change the WHERE clause. In my example:

vbtyp_v = 'C' should identify the sales order;
vbtyp_n = 'J' identifies delivery;
bwart = '601' is a material issue for delivery (AFAIR).

However, I may be wrong or your business processes may be a bit different, so you'd better ask your consultant to provide you proper conditions.