Application Development Discussions
Join the discussions or start your own on all things application development, including tools and APIs, programming models, and keeping your skills sharp.
cancel
Showing results for 
Search instead for 
Did you mean: 

ABAP: CX_SY_OPEN_SQL_DB when calling BAPI_MATERIAL_AVAILABILITY in infoset (t-code SQ02)

former_member250394
Discoverer
0 Kudos

Hi all,

I have an issue with a SAP infoset (join of tables KNVV, A552, TVKWZ): I want to have information about ATP-stock, as far as I know function module BAPI_MATERIAL_AVAILABILITY can be used for this purpose, so I'm trying to call this FM in Record Processing section of the infoset. But when I execute the query (built on that infoset) via SQ00, ABAP Runtime Error is raised:

Category        ABAP programming error
Runtime Errors  DBSQL_INVALID_CURSOR
Except.         CX_SY_OPEN_SQL_DB

Error happens at the moment of next record fetching:

  while %l_no_further_fetch = space.
    fetch next cursor %dbcursor
    into (KNVV-KUNNR , KNVV-VKORG , KNVV-VTWEG , KNVV-SPART , A552-MATNR , A552-DATBI , A552-PLTYP , TVKWZ-WERKS , TVKWZ-VKORG , TVKWZ-VTWEG ).
    if ( ( %rtmode-acc_check = 'X' and
           sy-dbcnt > %rtmode-acc_number )
        or sy-subrc <> 0 ).
      %l_no_further_fetch = 'X'.
    else.
    ...

During debugging I found out that the error happens after the first successful retrieval of stock information from BAPI_MATERIAL_AVAILABILITY, i.e. in other words query is working fine while FM cannot get stock quantity due to, for example, material not being shared in specified plant and etc. It looks like BAPI_MATERIAL_AVAILABILITY closes the query database cursor once FM returns the stock qty. Could you please help or advise? Thank you in advance!

Coding in Data section:

data:
  lv_atpqty like bapicm61v-wkbst,
  ls_return like bapireturn,
  lt_wmdvsx type table of bapiwmdvs with header line,
  lt_wmdvex type table of bapiwmdve with header line,
  lv_matnr like a552-matnr,
  lv_uom like mara-meins,
  lv_plant like tvkwz-werks.

Coding in Record Processing section:

clear f_atpqty.
clear lv_matnr.
clear lv_uom.
clear lv_atpqty.
select
    mvke~matnr,
    mara~meins
  into ( @lv_matnr, @lv_uom )
  up to 1 rows
  from mvke
  join mara on
    mara~matnr = mvke~matnr
  where
    mvke~pmatn = @a552-matnr and
    mvke~vrkme = ''.
endselect.
call function 'BAPI_MATERIAL_AVAILABILITY'
  exporting
    plant                    =  tvkwz-werks
    material                 =  lv_matnr
    unit                     =  lv_uom
    check_rule               =  'Z'
    read_atp_lock            =  'K'
    read_atp_lock_x          =  'X'
  importing
    av_qty_plt               =  lv_atpqty
    return                   =  ls_return
  tables
    wmdvsx                   =  lt_wmdvsx
    wmdvex                   =  lt_wmdvex.
if ls_return is initial.
  read table lt_wmdvex with key bdcnt = 0.
  f_atpqty = lt_wmdvex-com_qty.
  clear lt_wmdvsx[].
  clear lt_wmdvex[].
endif.
1 ACCEPTED SOLUTION

holakac
Explorer

Hello,

I think the problem is in cursors and FM - check https://help.sap.com/doc/abapdocu_752_index_htm/7.52/en-US/abendb_commit.htm

You have a synchronous FM call which does an implicit commit which cancels opened cursors.

You have to use WITH HOLD or other ways that can be found elsewhere: for example https://wiki.scn.sap.com/wiki/display/ABAP/FETCH+and+OPEN+CURSOR+Analysis

2 REPLIES 2

holakac
Explorer

Hello,

I think the problem is in cursors and FM - check https://help.sap.com/doc/abapdocu_752_index_htm/7.52/en-US/abendb_commit.htm

You have a synchronous FM call which does an implicit commit which cancels opened cursors.

You have to use WITH HOLD or other ways that can be found elsewhere: for example https://wiki.scn.sap.com/wiki/display/ABAP/FETCH+and+OPEN+CURSOR+Analysis

former_member250394
Discoverer
0 Kudos

Hi Jan,

Thank you, links you have provided helped me a lot. It is clearly stated: "database commit is performed implicitly in the following situation: ... Calling a function module in a synchronous or asynchronous remote function call"

Thus I had to bring call of BAPI_MATERIAL_AVAILABILITY out of Record Processing section (i.e. out of cursor loop body) to End-Of-Selection (after list). The main idea here is to loop through result set available via special variable '%G00[]' and update necessary values. Though such a solution does not work for ABAP List, but that's not critical for my purposes.

This solution was peeped here.

Finally it looks like this:

Data:

field-symbols:
  <lt_restab> type standard table,
  <wa_restab> type any,
  <l_atpqty> type any,
  <l_matnr> type any,
  <l_plant> type any,
  <l_iccount> type any.
types:
  begin of t_mat,
    matnr type mara-matnr,
    meins type mara-meins,
  end of t_mat.
data:
  lv_restab type char100 value '%G00[]',
  lv_atpqty like bapicm61v-wkbst,
  ls_return like bapireturn,
  lt_wmdvsx type table of bapiwmdvs with header line,
  lt_wmdvex type table of bapiwmdve with header line,
  lt_mat type standard table of t_mat,
  wa_mat type t_mat.

End-Of-Selection:

" assign result table %G00[] to dynamic table
assign (lv_restab) to <lt_restab>.
loop at <lt_restab> assigning <wa_restab>.
  assign component 'TVKWZ-WERKS'
    of structure <wa_restab> to <l_plant>.
  assign component 'A552-MATNR'
    of structure <wa_restab> to <l_matnr>.
  assign component 'f_atpqty'
    of structure <wa_restab> to <l_atpqty>.
  assign component 'f_iccount'
    of structure <wa_restab> to <l_iccount>.
  " get all ICs having EAN as pricing reference material
  " ignoring ones with status "invalid" ("U")
  select
        mvke~matnr,
        mara~meins
      into corresponding fields of @wa_mat
      from mvke
      join tvkwz on
        mvke~vkorg = tvkwz~vkorg and
        mvke~vtweg = tvkwz~vtweg
      join mara on
        mara~matnr = mvke~matnr
      where
        tvkwz~werks = @<l_plant> and
        mvke~vmsta <> 'U' and
        mvke~pmatn = @<l_matnr> and
        mvke~vrkme = ''.
    append wa_mat to lt_mat.
  endselect.
  " show amount of ICs belonging to current EAN
  <l_iccount> = sy-dbcnt.
  if <l_iccount> > 0.
    loop at lt_mat into wa_mat.
      call function 'BAPI_MATERIAL_AVAILABILITY'
        exporting
          plant                    =  <l_plant>
          material                 =  wa_mat-matnr
          unit                     =  wa_mat-meins
          check_rule               =  'Z'
        importing
          av_qty_plt               =  lv_atpqty
          return                   =  ls_return
        tables
          wmdvsx                   =  lt_wmdvsx
          wmdvex                   =  lt_wmdvex.
      if ls_return is initial.
        read table lt_wmdvex with key bdcnt = 0.
        if lt_wmdvex-com_qty <> '9999999999.000'.
          add lt_wmdvex-com_qty to <l_atpqty>.
        else.
          <l_atpqty> = lv_atpqty.
        endif.
        clear lt_wmdvsx[].
        clear lt_wmdvex[].
      endif.
    endloop.
  endif.
  clear lt_mat[].
  unassign <l_matnr>.
  unassign <l_plant>.
  unassign <l_atpqty>.
  unassign <l_iccount>.
endloop.