topic Re: query for sales document# in Application Development and Automation Discussions

query for sales document#

Former Member — Fri, 18 May 2007 16:08:05 GMT

Hi experts,

I am a new ABAPer, I need your help, given below is my requirement

KUNNR--> ACT+store ---> input

role: ship to party SH

SALES_ORG - Sales organization ->1000

DISTR_CHAN - Distribution channel ->10

DIVISION - Division ->40

Open sales document - --> LFSTK <> 'C'.

I want all sales document#,total order qty related data with material# (Also other fields, if its necessary to identify)

In given below query what I have to change?

loop at I_ORDERSUMMARY (this structure give me input)

select a_{KUNNR a}VBELN appending table <b>I_VBAK</b>

from VBAK as a inner join VBUK as b on a_{VBELN = b}VBELN

where KUNNR = I_ORDERSUMMARY-ACCT_STORE and

VKORG = '1000' and

VTWEG = '10' and

LFSTK <> 'C'.

endloop.

loop at <b>I_VBAK</b>.

select VBELN POSNR MATNR KWMENG

appending table I_VBAP

from VBAP where VBELN = I_VBAK-VBELN.

I am getting more number of sales document# in my output, instead of that I have to get back same o/p as i am getting in VA05 t-code.

how to add division code & Ship to party functionality in my query?

Re: query for sales document#

Former Member — Fri, 18 May 2007 16:47:30 GMT

Hi,

in this case, you need to add the VBPA table for to obtain the SHIP TO, SOLD TO fields. the link is like with inner join.

Regards

David

Re: query for sales document#

Former Member — Fri, 18 May 2007 16:51:11 GMT

Write the query vbak,vbuk and vbpa,vbup,vbap as inner join and for all entries,so that you will have good performance..

Re: query for sales document#

Former Member — Fri, 18 May 2007 17:44:42 GMT

Hi Neo,

A few points about your code.

From a functionality perspective:

a) Your code does not take care of DIVISION - Division ->40.

b) Your code does not take care of role: ship to party SH.

From a performance perspective.

a) Select within a loop is never a good idea.

Take a look at this code and let me know if it helps. You will have to test it because I do not have relevant data.

IF NOT i_ordersummary[] IS INITIAL.

  i_ordersummary_tmp[] = i_ordersummary[].
  SORT i_ordersummary_tmp BY kunnr.
  DELETE ADJACENT DUPLICATES FROM i_ordersummary COMPARING kunnr.

  SELECT vbeln
         kunnr
    FROM vbak
    INTO TABLE i_vbak
    FOR ALL ENTRIES IN i_ordersummary_tmp
    WHERE vkorg EQ '1000'
    AND   vtweg EQ '10'
    AND   spart EQ '40'
    AND   kunnr EQ i_ordersummary_tmp-kunnr.

  IF sy-subrc EQ 0.

    SORT i_vbak BY vbeln.

    SELECT vbeln
           lfstk
      FROM vbuk
      INTO TABLE i_vbuk
      FOR ALL ENTRIES IN i_vbak
      WHERE vbeln EQ i_vbak-vbeln.

    IF sy-subrc EQ 0.

      DELETE i_vbuk WHERE lfstk EQ 'C'.

      IF NOT i_vbuk[] IS INITIAL.

        SORT i_vbuk BY vbeln.

        SELECT vbeln
               posnr
               matnr
               kwmeng
          FROM vbap
          INTO TABLE i_vbap
          FOR ALL ENTRIES IN i_vbuk
          WHERE vbeln EQ i_vbuk-vbeln.

        IF sy-subrc EQ 0.

          SORT i_vbap BY vbeln
                         posnr.

          SELECT vbeln
                 posnr
            FROM vbpa
            INTO TABLE i_vbpa
            FOR ALL ENTRIES IN i_vbap
            WHERE vbeln EQ i_vbap-vbeln
            AND   posnr EQ i_vbap-posnr
            AND   parvw EQ 'WE'.
          IF sy-subrc EQ 0.
            SORT i_vbpa BY vbeln
                           posnr.
          ENDIF.

        ENDIF.

      ENDIF.

    ENDIF.

  ENDIF.

ENDIF.


w_index = 1.
LOOP AT i_vbak.

  LOOP AT i_vbap FROM w_index.

    IF i_vbap-vbeln GT i_vbak-vbeln.
      w_index = sy-tabix.
      EXIT.
    ELSEIF i_vbap-vbeln EQ i_vbak-vbeln.
      READ TABLE i_vbpa WITH KEY vbeln = i_vbap-vbeln
                                 posnr = i_vbap-posnr
                                 BINARY SEARCH
                                 TRANSPORTING NO FIELDS.
      IF sy-subrc EQ 0.
        i_output-vbeln  = i_vbak-vbeln .
        i_output-posnr  = i_vbap-posnr .
        i_output-kunnr  = i_vbak-kunnr .
        i_output-matnr  = i_vbap-matnr .
        i_output-kwmeng = i_vbap-kwmeng.
        APPEND i_output.
        CLEAR  i_output.
      ENDIF.
    ENDIF.

  ENDLOOP.

ENDLOOP.

Regards,

Mark