topic Re: Select Stmt in Application Development and Automation Discussions

Select Stmt

Former Member — Fri, 22 Sep 2006 16:49:58 GMT

Hi All,

The data I am getting for Rate field from flat file for my sales order BDC is not correct. So I have to get the Rate based on material number on condition type PR00.

What is condition type PR00 and how to write select stmt on this. The Rate field I want to get from material num RV45A-MABNR is KOMV-KBETR.

Please help me.

Thanks

Veni.

Re: Select Stmt

Former Member — Fri, 22 Sep 2006 17:00:11 GMT

Hi,

If you want to get the sales order conditions..use the following method..

VBAK-KNUMV.

Use the KNUMV in the table KONV with the condition type = 'PR00' and then get the rate in the field KBETR.

If you want to get the condition rate from the condition tables.

ask your function consultant to get the table name..

Thanks,

Naren

Re: Select Stmt

RichHeilman — Fri, 22 Sep 2006 17:00:58 GMT

I would assume that this code would give you want you need. Check the ikomv internal table after the function call. You will see the conditions there.




report zrich_0002 .

data: l_vbak type vbak.
data: l_komk type komk.
data: l_komp type komp.
data: ikomv type table of komv.
data: ikomvd type table of komvd.

parameters: p_vbeln type vbak-vbeln.

select single * from vbak
             into l_vbak
                       where vbeln = p_vbeln.
check sy-subrc = 0.

l_komk-belnr = l_vbak-vbeln.
l_komk-kappl = 'V'.
l_komk-knumv = l_vbak-knumv.

call function 'RV_PRICE_PRINT_REFRESH'
     tables
          tkomv = ikomv.

call function 'RV_PRICE_PRINT_ITEM'
     exporting
          comm_head_i = l_komk
          comm_item_i = l_komp
          language    = sy-langu
     tables
          tkomv       = ikomv
          tkomvd      = ikomvd.
check sy-subrc = 0.

Regards,

Rich Heilman

Re: Select Stmt

Former Member — Fri, 22 Sep 2006 17:11:32 GMT

Hi Naren& Rich,

Thankyou. But I need to get price based on Material Number RV45A-MABNR.

Please help me.

Thanks

Veni.

Re: Select Stmt

Former Member — Fri, 22 Sep 2006 17:12:31 GMT

Hi,

The material number will be available in the sales order line item table.

VBAP-MATNR..

Thanks,

Naren

Re: Select Stmt

RichHeilman — Fri, 22 Sep 2006 17:31:39 GMT

Take this program for example.




report zrich_0002 .

data: l_vbak type vbak.
data: l_vbap type vbap.
data: l_komk type komk.
data: l_komp type komp.
data: ikomv type table of komv.
data: xkomv type komv.
data: ikomvd type table of komvd.

parameters: p_vbeln type vbak-vbeln.
parameters: p_matnr type vbap-matnr.

select single * from vbak
             into l_vbak
                       where vbeln = p_vbeln.

select single * from vbap
             into l_vbap
                       where vbeln = p_vbeln
                         and matnr = p_matnr.

check sy-subrc = 0.

l_komk-belnr = l_vbak-vbeln.
l_komk-kappl = 'V'.
l_komk-knumv = l_vbak-knumv.

call function 'RV_PRICE_PRINT_REFRESH'
     tables
          tkomv = ikomv.

call function 'RV_PRICE_PRINT_ITEM'
     exporting
          comm_head_i = l_komk
          comm_item_i = l_komp
          language    = sy-langu
     tables
          tkomv       = ikomv
          tkomvd      = ikomvd.

* I believe that the POSNR is related to the KPOSN
* in the IKOMV table
loop at ikomv into xkomv where kposn = l_vbap-posnr.
  write:/ xkomv.
endloop.

Regards,

Rich Heilman

Re: Select Stmt

venkata_ramisetti — Fri, 22 Sep 2006 18:04:53 GMT

Hi,

Is the BDC for sales order change?

You can get PR00 condition rate values from KONv table using VBAK-KNUMV.

Thanks,

ramakrishna

Re: Select Stmt

Former Member — Fri, 22 Sep 2006 18:24:26 GMT

Hi All,

I came up with this,

Data: Begin of it_output occurs 0,

kbetr like komv-kbetr,

End of it_output.

select komv~kbetr from komv

into it_output

from komv

inner join vbak on vbak_{knumv = komv}knumv

inner join vbap on vbak_{vbeln = vbap}vbeln

where komv~kschl = 'PR00'

and vbap~matnr = lv_matnr.

Do you think this will work?

Please help me.

Thanks

Veni.

Re: Select Stmt

anversha_s — Fri, 22 Sep 2006 18:30:48 GMT

hi,

change lik this.

Data: Begin of it_output occurs 0,

kbetr like komv-kbetr,

End of it_output.

select komv~kbetr

into table it_output

from komv

inner join vbak

on vbak_{knumv = komv}knumv

inner join vbap

on vbak_{vbeln = vbap}vbeln

where komv~kschl = 'PR00'

and vbap~matnr = lv_matnr.

rgds

anver

Re: Select Stmt

Former Member — Fri, 22 Sep 2006 20:36:43 GMT

Hi,

I gave the query and it is giving me syntax error saying '"KOMV" is not defined in the ABAP Dictionary as a table, projection view or database view.' because KOMV is a structure not a table.

What should I do now.

Thanks

Veni.

Re: Select Stmt

Former Member — Fri, 22 Sep 2006 20:43:28 GMT

Hi,

The table KONV instead of KOMV..

Thanks,

Naren

Re: Select Stmt

Former Member — Fri, 22 Sep 2006 20:50:18 GMT

Thank you Naren,

Now I am getting this error.

You cannot use comparisons with column references in pool and cluster tables: "KONV~KNUMV".

select konv~kbetr

into table it_output

from konv

inner join vbak

on vbak_{knumv = konv}knumv

inner join vbap

on vbak_{vbeln = vbap}vbeln

where konv~kschl = 'PR00'

and vbap~matnr = gs_sdata-mabnr.

Thank you

Veni.

Re: Select Stmt

Former Member — Fri, 22 Sep 2006 20:54:48 GMT

Yes..KONV is a cluster table..You cannot do join on cluster tables..

You have to split SQLs into two..

one with VBAK and VBAP..

Then do a FOR ALL ENTRIES IN with the internal table got from the table VBAK & VBAP to get the values from KONV..

Thanks,

Naren

Re: Select Stmt

Former Member — Fri, 22 Sep 2006 21:37:19 GMT

Thank you all.