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

[Former Member]

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.

[Former Member]

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.

[holakac]

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 Member]

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.