Improve Query related to Sales Order - Delivery

[antonio_bruno]

Hi,

I've realized a query that join the VBAK, VBAP, VBEP to LIPS and LIKP. The query works, but it's too slow.

How I can create an internal table with the following fields?

VBAP-VBELN	VBAP-POSNR	VBEP-ETENR	VBEP-EDATU	LIPS-VBELN	LIPS_POSNR	LIKP-WADAT_IST
Key	Key	Key	Output	Output (sort)	Output (sort)	Output

I need this table to extract:

• the last delivery connected to the sales order position (LIPS-VBELN).
• the goods issue date linked to the last delivery (LIKP-WADAT_IST)
• the 1st delivery date scheduled (VBEP-EDATU).

I'm open to apply other solutions.

Excuse me, but i'm not skilled as abaper.

Thanks and best regards.

Antonio

[VenkatRamesh_V]

Hi Antonio,

Hope you are linking   the non primary key field for linking  table likp   with  vbap or vbep .

Create Secondary Index  to Improve the Performance.

Hope it helpful.

Regards,

Venkat.

[Former Member]

There is no need to create a secondary index.

Use the Sales Document Flow table (VBFA).

Rob

[antonio_bruno]

Hi,

I try to clarify.

To improve the perfermonce of the query, I've modified as follow the infoset:

• join VBAK and VBAP
• datas

data: begin of ODV occurs 0,

VBELN like VBAK-VBELN,

AUDAT like VBAK-AUDAT,

VKORG like VBAK-VKORG,

VTWEG like VBAK-VTWEG,

AUART like VBAK-AUART,

POSNR like VBAP-POSNR,

EDATU like VBEP-EDATU,

VBELN2 like LIPS-VBELN,

POSNR2 like LIPS-POSNR,

VGBEL like LIPS-VGBEL,

VGPOS like LIPS-VGPOS,

WADAT_IST like LIKP-WADAT_IST,

end of ODV.

• START-OF-SELECTION --> With Error Message: Wrong table name or table alias name table alias name "C"...

select AVBELN AAUDAT AVKORG AVTWEG AAUART BPOSNR CEDATU DVBELN DPOSNR EWADAT_IST

into table ODV

from VBAK as A inner join VBAP as B

on AVBELN = BVBELN

where VKORG = ORG

and AAUDAT between PIN and PFI

inner join VBEP C

on BVBELN = CVBELN

and BPOSNR = CPOSNR

and CETENR = '1'

left outer join LIPS D

on AVBELN = DVGBEL

and BPOSNR = DVGPOS

left outer join LIKP E

on DVBELN = EVBELN.

• Customer field CONS like LIKP-VBELN with the following code (I need the last delivery referred to the position oder):

clear CONS.

loop at ODV

where VGBEL = VBAP-VBELN

and VGPOS = VBAP-POSNR.

endloop.

How I can fix the codes?

Thanks and best regards.

Antonio

[VenkatRamesh_V]

Hi Antonio,

Try,

Avoid left Outer Join.

Use.

separate select query for lips likp   tables by  using  FOR ALL ENTRIES .

Hope it helpful.

Regards,

Venkat.

[antonio_bruno]

Hi Venkat,

As indicated I've solved with different internal tables and different select that using for all entries.

But the performance is similar to the other version.

Thanks.

Antonio

[Former Member]

There's an old saying - "You can't make a silk purse out of a sow's ear". Different internal tables and trying out joins and for all entries isn't going to help when you are using the wrong tables.

Like Raymond and I suggested, VBFA is the better starting point.

Rob

[VenkatRamesh_V]

Hi Antonio,

Hope you never used key fields.

Pass the sale order and item number to vbfa table, Separate  the delivery no and billing document no by the following field  vbtyp_n.

Pass the vbfa   field  to the lips  key field.

OR  Create secondary index for  lips.

Before doing code  View the entries  of the tables.

Hope it helpful.

Regards,

Venkat.

[raymond_giuseppi]

There is a FAQ note on OSS : 185530 - Performance: Customer developments in SD, did you read it, this note suggest to use VBFA to read deliveries from sales orders.

Correct:      SELECT FROM vbfa WHERE VBELV = ... and VBTYP_N = 'J'
                   SELECT FROM lips WHERE vbeln = vbfa-vbeln AND posnr = vbfa-posnn

And usually A correct JOIN give better performance than poor FOR ALL ENTRIES...

Regards,
Raymond

[antonio_bruno]

Hi, I've modified the datas and start of selection as follow, and the performance is better: DATA: BEGIN OF ODVCON occurs 0,  VBELN like VBAK-VBELN,  POSNR like VBAP-POSNR,  EDATU like VBEP-EDATU,  VBELN2 like VBFA-VBELN, END OF ODVCON. SELECT A~VBELN B~POSNR C~EDATU D~VBELN INTO TABLE ODVCON FROM vbak as A INNER JOIN VBAP as B ON A~VBELN = B~VBELN INNER JOIN VBEP as C ON B~VBELN = C~VBELN                              AND B~POSNR = C~POSNR                              AND C~ETENR = '1' LEFT OUTER JOIN VBFA as D ON B~VBELN = D~VBELV                        AND B~POSNR = D~POSNV                    AND D~VBTYP_N = 'J'. How I can insert this other codes to reduce the datas of the internal table? *where A~VKORG = ORG *and A~AUDAT between PIN and PFI *and A~VBTYP = 'C' *and A~AUART = TDOC. Thanks and best regards. Antonio

[Former Member]

How big a date range is being used  for audat?

Rob

[harshsisodia31]

Hi,

Try using below Function module

RV_ORDER_FLOW_INFORMATION

Regards

HarsH

[antonio_bruno]

Hi,

to improve the performance, in the join of VBAK, VBAP and VBFA, I need to reduce the number of the sales orders analyzed.

So i thought to use as selection parameter the following VBAK fields: VKORG, AUDAT, VBTYP and AUART, but i don't know how can insert this fields in the join?

Thanks.

[antonio_bruno]

Hi, At the end, I've created a custom field in reference to VBFA with the following code (the performance is good): clear CONS. select VBELN from VBFA into CONS where VBELV = VBAP-VBELN and POSNV = VBAP-POSNR and VBTYP_N = 'J' and PLMIN = '+'. endselect. Thanks and regards. Antonio