SAP Query Difficulties

[benjamin_allsopp]

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.

[benjamin_allsopp]

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.

[Lakshmipathi]

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

G. Lakshmipathi

[Former Member]

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.

[Former Member]

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.

[benjamin_allsopp]

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.

[benjamin_allsopp]

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.

[Former Member]

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.

[benjamin_allsopp]

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.

[Former Member]

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.

[vamsilakshman_pendurti]

Hi

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.

endloop.

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

Hope you understand,

Vamsilakshman.p