query for sales document#

[Former Member]

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?

[Former Member]

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

[Former Member]

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

[Former Member]

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