topic Re: problem in selecting a single record... in Application Development and Automation Discussions

problem in selecting a single record...

aris_hidalgo — Thu, 23 Feb 2006 03:22:36 GMT

Hello experts,

How can I modify my code below in selecting only one wrbtr from table ekbe? Also, what is wrbtr and how does it relate to a certain PO? is it the balance? the PO amount? Thanks guys and take care!

CONCATENATE t_pohistory-mat_doc t_pohistory-doc_year

INTO bkpf-awkey.

SELECT SINGLE * FROM bkpf

WHERE awkey = bkpf-awkey.

*AVH - removed wrbtr from selection

SELECT augdt augbl shkzg dmbtr FROM bsak

INTO (bsak-augdt,bsak-augbl,bsak-shkzg,

bsak-dmbtr)

WHERE bukrs = bkpf-bukrs

AND gjahr = bkpf-gjahr

AND belnr = bkpf-belnr.

*AVH

if not bsak-augbl is initial.

select belnr gjahr from bsak

into (bsak-belnr, bsak-gjahr)

where bukrs = bkpf-bukrs

and belnr = bkpf-belnr

and gjahr = bkpf-gjahr.

select awkey from bkpf

into v_bkpf_aw

where bukrs = 'GLOB'

and belnr = bsak-belnr

and gjahr = bsak-gjahr.

w_len = strlen( v_bkpf_aw ).

w_off = w_len - 4.

v_awkey_1 = v_bkpf_aw+0(10).

v_awkey_2 = v_bkpf_aw+w_off(4).

select wrbtr from ekbe

into ekbe-wrbtr

where belnr = v_awkey_1

and gjahr = v_awkey_2.

*AVH - Changed all bsak-wrbtr to ekbe-wrbtr.

IF bsak-shkzg = 'H'.

bsak-dmbtr = - bsak-dmbtr.

ekbe-wrbtr = - ekbe-wrbtr.

ENDIF.

IF t_pohistory-currency <> 'PHP'.

IF bsak-augdt GT pa_augdt.

IF bsak-augdt IN so_augdt.

t_amount-tramt = t_amount-tramt + ekbe-wrbtr.

ENDIF.

ELSE.

IF bsak-augdt GT pa_augdt.

IF bsak-augdt IN so_augdt.

t_amount-tramt = t_amount-tramt + bsak-dmbtr.

ENDIF.

endselect.

endif.

ENDSELECT.

ENDIF.

Re: problem in selecting a single record...

suresh_datti — Thu, 23 Feb 2006 03:28:56 GMT

Hi,

PL change the SELECT form EBKE as noted below..


select wrbtr up to 1 rows
             from ekbe
             into ekbe-wrbtr 
             where belnr = v_awkey_1
             and gjahr = v_awkey_2.
endselect.

WRBTR is the Amount in Purchasing Document Currency. It is the Total Value of the Qunatity.

Regards,

Suresh Datti

Re: problem in selecting a single record...

Former Member — Thu, 23 Feb 2006 03:30:39 GMT

You can use :

select <b>single</b> wrbtr from ekbe

into ekbe-wrbtr

where belnr = v_awkey_1

and gjahr = v_awkey_2.

Re: problem in selecting a single record...

aris_hidalgo — Thu, 23 Feb 2006 03:36:34 GMT

So how can I validate if a certain PO's amount is correct? for example, where would I go if I want to check PO 4500000006 amount? will I go to ekbe-wrbtr?or to toher tables?Thanks!

Re: problem in selecting a single record...

Former Member — Thu, 23 Feb 2006 03:46:45 GMT

Hi,

I think you want total for a PO.

Write this :

data : ZWRBTR type EKBE-WRBTR.

select SUM ( wrbtr )from ekbe into ZWRBTR where belnr = v_awkey_1

and gjahr = v_awkey_2.

ZWRBTR will be the total amount of a PO means sum of all line items.

As you are using BELNR(Number of Material Document) and year then you want only one record then I think you want total PO value. The above code will retrieve total PO value.

Regards,

Lanka

Re: problem in selecting a single record...

suresh_datti — Thu, 23 Feb 2006 03:49:34 GMT

Hi,

I think you should be using EKPO for that..

Regards,

Suresh Datti

Re: problem in selecting a single record...

Former Member — Thu, 23 Feb 2006 03:53:06 GMT

Hi ,

You can check the amount at EKPO with PO no and line item .

Means check EKPO-EBELN = EKBE-EBELN

and EKPO-EBELP = EKBE-EBELP.

check EKPO-NETWR.

Lanka

Re: problem in selecting a single record...

former_member186741 — Thu, 23 Feb 2006 03:59:35 GMT

you need to think about what you mean by a 'PO's amount'. PO headers are stored on EKKO. DO you want the sum of all amounts relating to a complete PO?

More likely you are trying to deal with line items. These are held in EKPO. EKPO has NETWR for net order value and BRTWR for gross order value.

EKBE has history of each line item but there can be multiples per line item which is probably not what you want.

Re: problem in selecting a single record...

Former Member — Thu, 23 Feb 2006 14:31:18 GMT

I think you need to look at your logic as well. You have heavily nested selects and some of them don't use keys.

Rob