Skip to Content

Improve Query related to Sales Order - Delivery

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

Add comment
10|10000 characters needed characters exceeded

  • Get RSS Feed

5 Answers

  • Best Answer
    Jan 13, 2015 at 05:30 PM

    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.

    Add comment
    10|10000 characters needed characters exceeded

    • 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

  • Jan 14, 2015 at 12:21 PM

    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

    Add comment
    10|10000 characters needed characters exceeded

  • Jan 14, 2015 at 09:40 AM

    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.

    Add comment
    10|10000 characters needed characters exceeded

    • 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.

  • Jan 16, 2015 at 04:33 AM

    Hi,

    Try using below Function module

    RV_ORDER_FLOW_INFORMATION

    Regards

    HarsH

    Add comment
    10|10000 characters needed characters exceeded

    • 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.

  • Jan 23, 2015 at 10:20 AM

    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

    Add comment
    10|10000 characters needed characters exceeded