Skip to Content
0

Generic Datasource - open cursor, close cursor problem.

Jun 22, 2017 at 07:55 AM

177

avatar image
Former Member
Hi Experts,

I tried to cretae the Generic Datasource through Functional module. After creating the Datasource i tried to execute in RSA3, the below said runtime error is coming.

Short text
Cursor already closed or not yet open.

What happened?
Error in the ABAP Application Program

The current ABAP program "SAPLZBW_FG" had to be terminated because it has
come across a statement that unfortunately cannot be executed.

Error analysis
An exception occurred that is explained in detail below.
The exception, which is assigned to class 'CX_SY_OPEN_SQL_DB', was not caught
in
procedure "ZFREIGHTRATE" "(FUNCTION)", nor was it propagated by a RAISING
clause.
Since the caller of the procedure could not have anticipated that the
exception would occur, the current program is terminated.
The reason for the exception is:
The cursor used in a FETCH or CLOSE CURSOR command is
not open. It was either not yet open or has already
been closed. You can close the cursor explicitly with
the CLOSE CURSOR command, implicitly with the COMMIT WORK command,
or with a screen change.

*************************************************************************************************************************

Please see the below source code which i had written. please correct me where i had gone wrong.

=================================================================

  TABLES: ztc_asu_prod_qty, ztc_tank_lvl_stg, t001k.

  TYPES : BEGIN OF ty_asu_prod_qty,
            bukrs TYPE bukrs.
          INCLUDE STRUCTURE ztc_asu_prod_qty.
  TYPES:   END OF ty_asu_prod_qty.

  DATA : it_prodqty TYPE STANDARD TABLE OF ty_asu_prod_qty, "ztc_asu_prod_qty
        wa_prodqty LIKE LINE OF it_prodqty,
       it_prodqty1 TYPE STANDARD TABLE OF ty_asu_prod_qty, "ztc_asu_prod_qty,
       it_prodqty2 TYPE STANDARD TABLE OF ty_asu_prod_qty,
       wa_prodqty2 LIKE LINE OF it_prodqty2,
       wa_prodqty1 LIKE LINE OF it_prodqty1,
       it_final TYPE STANDARD TABLE OF zbw_asu_str_prodqty,
     wa_final LIKE LINE OF it_final.


  TYPES : BEGIN OF typ_tab2,
     werks TYPE werks_d,
     matnr TYPE matnr,
     prodate TYPE z_prodate,
     endinvqty TYPE menge_d,
    overrideqty TYPE menge_d,
    uom TYPE meins,
    aedat TYPE aedat,
     END OF typ_tab2.

  DATA : it_tab2 TYPE STANDARD TABLE OF typ_tab2,
        it_tab3 TYPE STANDARD TABLE OF typ_tab2,
wa_tab2 LIKE LINE OF it_tab2.

  FIELD-SYMBOLS: <fs_data> TYPE  zbw_asu_str_prodqty,
                 <fs_wa_tab2> TYPE typ_tab2.

** Maximum number of lines for DB table
  STATICS: s_s_if TYPE srsc_s_if_simple,

* counter
          s_counter_datapakid LIKE sy-tabix,
* cursor
          s_cursor TYPE cursor.
** Select ranges
  RANGES: l_r_matnr  FOR ztc_asu_prod_qty-matnr,
  l_r_prodate  FOR ztc_asu_prod_qty-prodate,
  l_r_erdat  FOR ztc_asu_prod_qty-erdat,
          l_r_werks  FOR ztc_asu_prod_qty-werks.

* Initialization mode (first call by SAPI) or data transfer mode
* (following calls) ?
  IF i_initflag = sbiwa_c_flag_on.
*************************************************************************
* Initialization: check input parameters
*                 buffer input parameters
*                 prepare data selection
*************************************************************************
** Check DataSource validity
    CASE i_dsource.
      WHEN 'ZSDS_TD_ASU_ACTUALS'.
      WHEN OTHERS.
        IF 1 = 2. MESSAGE e009(r3). ENDIF.
* this is a typical log call. Please write every error message like this
        log_write 'E'                  "message type
                  'R3'                 "message class
                  '009'                "message number
                  i_dsource   "message variable 1
                  ' '.                 "message variable 2
        RAISE error_passed_to_mess_handler.
    ENDCASE.
    APPEND LINES OF i_t_select TO s_s_if-t_select.
*
* Fill parameter buffer for data extraction calls
    s_s_if-requnr    = i_requnr.
    s_s_if-dsource   = i_dsource.
    IF i_maxsize LE 50000.
      s_s_if-maxsize   = i_maxsize.
    ELSE.
      s_s_if-maxsize = 50000.
    ENDIF.
*    s_s_if-requnr    = i_requnr.
*    s_s_if-dsource = i_dsource.
*    s_s_if-maxsize   = i_maxsize.

* Fill field list table for an optimized select statement
* (in case that there is no 1:1 relation between InfoSource fields
* and database table fields this may be far from beeing trivial)
    APPEND LINES OF i_t_fields TO s_s_if-t_fields.
*
  ELSE.                 "Initialization mode or data extraction ?
*
************************************************************************
* Data transfer: First Call      OPEN CURSOR + FETCH
*                Following Calls FETCH only
************************************************************************
** First data package -> OPEN CURSOR
    IF s_counter_datapakid = 0.
**
      LOOP AT s_s_if-t_select INTO i_t_select WHERE fieldnm = 'MATNR'.
        MOVE-CORRESPONDING i_t_select TO l_r_matnr.
        APPEND l_r_matnr.
      ENDLOOP.

      LOOP AT s_s_if-t_select INTO i_t_select WHERE fieldnm = 'PRODATE'.
        MOVE-CORRESPONDING i_t_select TO l_r_prodate.
        APPEND l_r_prodate.
      ENDLOOP.

      LOOP AT s_s_if-t_select INTO i_t_select WHERE fieldnm = 'ERDAT'.
        MOVE-CORRESPONDING i_t_select TO l_r_erdat.
        APPEND l_r_erdat.
      ENDLOOP.

      LOOP AT s_s_if-t_select INTO i_t_select WHERE fieldnm = 'WERKS'.
        MOVE-CORRESPONDING i_t_select TO l_r_werks.
        APPEND l_r_werks.
      ENDLOOP.

      OPEN CURSOR WITH HOLD s_cursor FOR
*      SELECT werks matnr prodate prodtype counter usage_qty usage_uom doc_num doc_type
*          FROM ztc_asu_prod_qty AS a INNER JOIN t001k AS b ON a~werks = b~bwkey
*          WHERE matnr IN l_r_matnr AND werks IN l_r_werks.

SELECT b~bukrs a~werks a~matnr a~prodate a~prodtype a~counter a~usage_qty a~usage_uom a~doc_num a~doc_type
*  New business reqt to use erdat instead of prodate for delta
  a~erdat
FROM ztc_asu_prod_qty AS a
  INNER JOIN t001k AS b
  ON a~werks = b~bwkey
WHERE a~matnr IN l_r_matnr
  AND a~werks IN l_r_werks
  AND a~erdat IN l_r_erdat
  AND a~prodate IN l_r_prodate.

    ENDIF.                             "First data package ?

    FETCH NEXT CURSOR s_cursor
               APPENDING CORRESPONDING FIELDS
               OF TABLE it_prodqty
               PACKAGE SIZE s_s_if-maxsize.
**
    IF sy-subrc <> 0.
      CLOSE CURSOR s_cursor.
      RAISE no_more_data.
    ENDIF.
*Begin of New changes as part of DE1K947464
*If there are multiple records for different ERDAT, then since its Delta, to avoid the latest record overwritten with the old one,
*  we get all entries again from the table for that Plant -Material-Prodate-Prodtype combination for ERDAT Less than Delta date
    IF it_prodqty[] IS NOT INITIAL.
      SELECT b~bukrs a~werks a~matnr a~prodate a~prodtype a~counter a~usage_qty a~usage_uom a~doc_num a~doc_type  a~erdat
      FROM ztc_asu_prod_qty AS a
        INNER JOIN t001k AS b
        ON a~werks = b~bwkey
        INTO CORRESPONDING FIELDS OF TABLE  it_prodqty2
        FOR ALL ENTRIES IN it_prodqty
      WHERE a~matnr EQ it_prodqty-matnr
        AND a~werks EQ it_prodqty-werks
        AND a~prodate EQ it_prodqty-prodate
        AND a~prodtype EQ it_prodqty-prodtype
        AND a~erdat LT l_r_erdat.

      CLEAR : wa_prodqty,wa_prodqty1, wa_prodqty2.

     IF sy-subrc EQ 0.
        SORT it_prodqty2 BY werks matnr prodate prodtype.
* Delete Records for which Plant-Material-Prodate-Prodtype combination, we have not received any delta as per today.
        LOOP AT it_prodqty2 INTO wa_prodqty2.
          READ TABLE it_prodqty INTO wa_prodqty WITH KEY werks = wa_prodqty2-werks
                                         matnr = wa_prodqty2-matnr
                                         prodate = wa_prodqty2-prodate
                                         prodtype = wa_prodqty2-prodtype.
          IF sy-subrc = 0.
            APPEND wa_prodqty2 TO it_prodqty1.
          ENDIF.
        ENDLOOP.

        APPEND LINES OF it_prodqty1 TO it_prodqty.
        SORT it_prodqty BY bukrs werks matnr prodate prodtype counter usage_qty usage_uom doc_num doc_type erdat.
        DELETE ADJACENT DUPLICATES FROM it_prodqty COMPARING ALL FIELDS.
      ENDIF.
      CLEAR : wa_prodqty,wa_prodqty1, wa_prodqty2.
*End of New changes as part of DE1K947464

      SORT it_prodqty BY werks matnr prodate prodtype erdat.
      LOOP AT it_prodqty INTO wa_prodqty.
        IF  ( wa_prodqty-bukrs <> wa_prodqty1-bukrs OR
          wa_prodqty-werks <> wa_prodqty1-werks OR
             wa_prodqty-matnr <> wa_prodqty1-matnr OR
            wa_prodqty-prodate <> wa_prodqty1-prodate )
         AND sy-tabix <> 1.
          wa_final-bukrs = wa_prodqty1-bukrs.
          wa_final-werks = wa_prodqty1-werks.
          wa_final-matnr = wa_prodqty1-matnr.
          wa_final-prodate = wa_prodqty1-prodate.
          wa_final-usage_uom = wa_prodqty1-usage_uom.
          wa_final-erdat = wa_prodqty1-erdat.
          APPEND wa_final TO it_final.
          CLEAR: wa_final-trl_qty,wa_final-plb_qty,wa_final-plt_qty,wa_final-con_qty,
                wa_final-loss_qty,wa_final-tot_qty,wa_final-tnl_qty,wa_final-pur_qty,
                wa_final-trl_c_qty,wa_final-prod_d_qty,wa_final-gas_qty,wa_final-railcar_qty,wa_final-liqvap_qty.
        ENDIF.
        IF wa_prodqty-prodtype = 1.
          wa_final-trl_qty  = wa_final-trl_qty + wa_prodqty-usage_qty.
        ELSEIF wa_prodqty-prodtype = 2.
          wa_final-trl_c_qty  = wa_final-trl_c_qty + wa_prodqty-usage_qty.
        ELSEIF wa_prodqty-prodtype = 3.
          wa_final-tnl_qty  = wa_final-tnl_qty + wa_prodqty-usage_qty.
        ELSEIF wa_prodqty-prodtype = 4.
          wa_final-pur_qty  = wa_final-pur_qty + wa_prodqty-usage_qty.
        ELSEIF wa_prodqty-prodtype = 5.
          wa_final-prod_d_qty  = wa_final-prod_d_qty + wa_prodqty-usage_qty.
        ELSEIF wa_prodqty-prodtype = 6.
          wa_final-loss_qty  = wa_final-loss_qty + wa_prodqty-usage_qty.
        ELSEIF wa_prodqty-prodtype = 7.
          wa_final-plt_qty  = wa_final-plt_qty + wa_prodqty-usage_qty.
        ELSEIF wa_prodqty-prodtype = 8.
          wa_final-plb_qty = wa_final-plb_qty + wa_prodqty-usage_qty.
*******Start*****Added Production types 9,10 and 11 through Transport DE1K946685************
        ELSEIF wa_prodqty-prodtype = 9.
          wa_final-gas_qty = wa_final-gas_qty + wa_prodqty-usage_qty.
        ELSEIF wa_prodqty-prodtype = 10.
          wa_final-railcar_qty = wa_final-railcar_qty + wa_prodqty-usage_qty.
        ELSEIF wa_prodqty-prodtype = 11.
          wa_final-liqvap_qty = wa_final-liqvap_qty + wa_prodqty-usage_qty.
*******End*****Added Production types 9,10 and 11 through Transport DE1K946685************
        ELSEIF wa_prodqty-prodtype = 98.
          wa_final-con_qty = wa_final-con_qty + wa_prodqty-usage_qty.
        ELSEIF wa_prodqty-prodtype = 99.
          wa_final-tot_qty = wa_final-tot_qty + wa_prodqty-usage_qty.
        ENDIF.
        wa_prodqty1 = wa_prodqty.

        CLEAR wa_prodqty.
      ENDLOOP.
      wa_final-bukrs = wa_prodqty1-bukrs.
      wa_final-werks = wa_prodqty1-werks.
      wa_final-matnr = wa_prodqty1-matnr.
      wa_final-prodate = wa_prodqty1-prodate.
      wa_final-usage_uom = wa_prodqty1-usage_uom.
      wa_final-erdat = wa_prodqty1-erdat.
      APPEND wa_final TO it_final.
      CLEAR : wa_final.

      SELECT werks matnr prodate endinvqty overrideqty uom aedat FROM ztc_tank_lvl_stg INTO TABLE it_tab2 WHERE werks <> '' AND matnr <> ''.
      SORT it_tab2 BY werks matnr prodate.
      LOOP AT it_tab2 INTO wa_tab2.
        COLLECT wa_tab2 INTO it_tab3.
        CLEAR wa_tab2.
      ENDLOOP.

      LOOP AT it_tab3 ASSIGNING <fs_wa_tab2>.
        <fs_wa_tab2>-prodate = <fs_wa_tab2>-prodate + 1.
      ENDLOOP.

      LOOP AT it_final ASSIGNING <fs_data>.
        READ TABLE it_tab3 INTO wa_tab2 WITH KEY werks = <fs_data>-werks
                                    matnr = <fs_data>-matnr
                                    prodate = <fs_data>-prodate.
*Begin of Changes by MANAHA as part of DE1K947464
        IF sy-subrc = 0.
          IF wa_tab2-aedat EQ '00000000'.
            <fs_data>-beginvqty = wa_tab2-endinvqty.
            <fs_data>-uom = wa_tab2-uom.
          ELSE.
            <fs_data>-beginvqty = wa_tab2-overrideqty.
            <fs_data>-uom = wa_tab2-uom.
          ENDIF.
          CLEAR wa_tab2.
        ENDIF.
      ENDLOOP.
*End of Changes by MANAHA as part of DE1K947464
      e_t_data[] = it_final.
    ENDIF.
    s_counter_datapakid = s_counter_datapakid + 1.
  ENDIF.
10 |10000 characters needed characters left characters exceeded
* Please Login or Register to Answer, Follow or Comment.

2 Answers

Mario Tibollo Jun 23, 2017 at 12:52 PM
0

try this:

OPENCURSORWITHHOLD s_cursor FOR

* SELECT werks matnr prodate prodtype counter usage_qty usage_uom doc_num doc_type

* FROM ztc_asu_prod_qty AS a INNER JOIN t001k AS b ON a~werks = b~bwkey

* WHERE matnr IN l_r_matnr AND werks IN l_r_werks.

SELECT b~bukrs a~werks a~matnr a~prodate a~prodtype a~counter a~usage_qty a~usage_uom a~doc_num a~doc_type

* New business reqt to use erdat instead of prodate for delta

a~erdat

FROM ztc_asu_prod_qty AS a

INNERJOIN t001k AS b

ON a~werks = b~bwkey

WHERE a~matnr IN l_r_matnr

AND a~werks IN l_r_werks

AND a~erdat IN l_r_erdat

AND a~prodate IN l_r_prodate.

ENDIF."First data package ?

FETCHNEXTCURSOR s_cursor

APPENDINGCORRESPONDINGFIELDS

OFTABLE it_prodqty

PACKAGESIZE s_s_if-maxsize.

**

IFsy-subrc = 0.

*Begin of New changes as part of DE1K947464

*If there are multiple records for different ERDAT, then since its Delta, to avoid the latest record overwritten with the old one,

* we get all entries again from the table for that Plant -Material-Prodate-Prodtype combination for ERDAT Less than Delta date

IF it_prodqty[] ISNOTINITIAL.

SELECT b~bukrs a~werks a~matnr a~prodate a~prodtype a~counter a~usage_qty a~usage_uom a~doc_num a~doc_type a~erdat

FROM ztc_asu_prod_qty AS a

INNERJOIN t001k AS b

ON a~werks = b~bwkey

INTOCORRESPONDINGFIELDSOFTABLE it_prodqty2

FORALLENTRIESIN it_prodqty

WHERE a~matnr EQ it_prodqty-matnr

AND a~werks EQ it_prodqty-werks

AND a~prodate EQ it_prodqty-prodate

AND a~prodtype EQ it_prodqty-prodtype

AND a~erdat LT l_r_erdat.

CLEAR : wa_prodqty,wa_prodqty1, wa_prodqty2.

IFsy-subrc EQ0.

SORT it_prodqty2 BY werks matnr prodate prodtype.

* Delete Records for which Plant-Material-Prodate-Prodtype combination, we have not received any delta as per today.

LOOPAT it_prodqty2 INTO wa_prodqty2.

READTABLE it_prodqty INTO wa_prodqty WITHKEY werks = wa_prodqty2-werks

matnr = wa_prodqty2-matnr

prodate = wa_prodqty2-prodate

prodtype = wa_prodqty2-prodtype.

IFsy-subrc = 0.

APPEND wa_prodqty2 TO it_prodqty1.

ENDIF.

ENDLOOP.

APPENDLINESOF it_prodqty1 TO it_prodqty.

SORT it_prodqty BY bukrs werks matnr prodate prodtype counter usage_qty usage_uom doc_num doc_type erdat.

DELETEADJACENTDUPLICATESFROM it_prodqty COMPARINGALLFIELDS.

ENDIF.

CLEAR : wa_prodqty,wa_prodqty1, wa_prodqty2.

*End of New changes as part of DE1K947464

SORT it_prodqty BY werks matnr prodate prodtype erdat.

LOOPAT it_prodqty INTO wa_prodqty.

IF ( wa_prodqty-bukrs <> wa_prodqty1-bukrs OR

wa_prodqty-werks <> wa_prodqty1-werks OR

wa_prodqty-matnr <> wa_prodqty1-matnr OR

wa_prodqty-prodate <> wa_prodqty1-prodate )

ANDsy-tabix <> 1.

wa_final-bukrs = wa_prodqty1-bukrs.

wa_final-werks = wa_prodqty1-werks.

wa_final-matnr = wa_prodqty1-matnr.

wa_final-prodate = wa_prodqty1-prodate.

wa_final-usage_uom = wa_prodqty1-usage_uom.

wa_final-erdat = wa_prodqty1-erdat.

APPEND wa_final TO it_final.

CLEAR: wa_final-trl_qty,wa_final-plb_qty,wa_final-plt_qty,wa_final-con_qty,

wa_final-loss_qty,wa_final-tot_qty,wa_final-tnl_qty,wa_final-pur_qty,

wa_final-trl_c_qty,wa_final-prod_d_qty,wa_final-gas_qty,wa_final-railcar_qty,wa_final-liqvap_qty.

ENDIF.

IF wa_prodqty-prodtype = 1.

wa_final-trl_qty = wa_final-trl_qty + wa_prodqty-usage_qty.

ELSEIF wa_prodqty-prodtype = 2.

wa_final-trl_c_qty = wa_final-trl_c_qty + wa_prodqty-usage_qty.

ELSEIF wa_prodqty-prodtype = 3.

wa_final-tnl_qty = wa_final-tnl_qty + wa_prodqty-usage_qty.

ELSEIF wa_prodqty-prodtype = 4.

wa_final-pur_qty = wa_final-pur_qty + wa_prodqty-usage_qty.

ELSEIF wa_prodqty-prodtype = 5.

wa_final-prod_d_qty = wa_final-prod_d_qty + wa_prodqty-usage_qty.

ELSEIF wa_prodqty-prodtype = 6.

wa_final-loss_qty = wa_final-loss_qty + wa_prodqty-usage_qty.

ELSEIF wa_prodqty-prodtype = 7.

wa_final-plt_qty = wa_final-plt_qty + wa_prodqty-usage_qty.

ELSEIF wa_prodqty-prodtype = 8.

wa_final-plb_qty = wa_final-plb_qty + wa_prodqty-usage_qty.

*******Start*****Added Production types 9,10 and 11 through Transport DE1K946685************

ELSEIF wa_prodqty-prodtype = 9.

wa_final-gas_qty = wa_final-gas_qty + wa_prodqty-usage_qty.

ELSEIF wa_prodqty-prodtype = 10.

wa_final-railcar_qty = wa_final-railcar_qty + wa_prodqty-usage_qty.

ELSEIF wa_prodqty-prodtype = 11.

wa_final-liqvap_qty = wa_final-liqvap_qty + wa_prodqty-usage_qty.

*******End*****Added Production types 9,10 and 11 through Transport DE1K946685************

ELSEIF wa_prodqty-prodtype = 98.

wa_final-con_qty = wa_final-con_qty + wa_prodqty-usage_qty.

ELSEIF wa_prodqty-prodtype = 99.

wa_final-tot_qty = wa_final-tot_qty + wa_prodqty-usage_qty.

ENDIF.

wa_prodqty1 = wa_prodqty.

CLEAR wa_prodqty.

ENDLOOP.

wa_final-bukrs = wa_prodqty1-bukrs.

wa_final-werks = wa_prodqty1-werks.

wa_final-matnr = wa_prodqty1-matnr.

wa_final-prodate = wa_prodqty1-prodate.

wa_final-usage_uom = wa_prodqty1-usage_uom.

wa_final-erdat = wa_prodqty1-erdat.

APPEND wa_final TO it_final.

CLEAR : wa_final.

SELECT werks matnr prodate endinvqty overrideqty uom aedat FROM ztc_tank_lvl_stg INTOTABLE it_tab2 WHERE werks <> ''AND matnr <> ''.

SORT it_tab2 BY werks matnr prodate.

LOOPAT it_tab2 INTO wa_tab2.

COLLECT wa_tab2 INTO it_tab3.

CLEAR wa_tab2.

ENDLOOP.

LOOPAT it_tab3 ASSIGNING <fs_wa_tab2>.

<fs_wa_tab2>-prodate = <fs_wa_tab2>-prodate + 1.

ENDLOOP.

LOOPAT it_final ASSIGNING <fs_data>.

READTABLE it_tab3 INTO wa_tab2 WITHKEY werks = <fs_data>-werks

matnr = <fs_data>-matnr

prodate = <fs_data>-prodate.

*Begin of Changes by MANAHA as part of DE1K947464

IFsy-subrc = 0.

IF wa_tab2-aedat EQ'00000000'.

<fs_data>-beginvqty = wa_tab2-endinvqty.

<fs_data>-uom = wa_tab2-uom.

ELSE.

<fs_data>-beginvqty = wa_tab2-overrideqty.

<fs_data>-uom = wa_tab2-uom.

ENDIF.

CLEAR wa_tab2.

ENDIF.

ENDLOOP.

*End of Changes by MANAHA as part of DE1K947464

e_t_data[] = it_final.

ENDIF.

Endif.

IFsy-subrc <> 0.

CLOSECURSOR s_cursor.

RAISE no_more_data.

ENDIF.

s_counter_datapakid = s_counter_datapakid + 1.

ENDIF.

Show 5 Share
10 |10000 characters needed characters left characters exceeded
Former Member

Hi Mario,

Thanks for your reply. Can you tell me what difference it makes by moving the close cursor statement to the end ?

Thanks

Satish

0

code is being executed before the close cursor. is it working now?

0
Former Member

We are facing the dump only in production system. So until the changes move to production I cannot comment on whether it is working or not. Anyways thanks.

0

We are facing the dump only in production system.

you didn't specify this in your question. Are you saying that your code is working fine in any other system except for production?

0
Former Member

Just checked , I was able to replicate the issue in Quality System.

0
Thomas Weinlick Jun 28, 2017 at 09:02 PM
0

Hi Mario,

This can happen if something else in your code is causing a Commit to occur, thereby closing your cursor. Try commenting out your 2nd select on ZTC_ASU_PRD_QTY table, just for test purposes, and see if you still get the error.

Kind Regards,
Tom

Share
10 |10000 characters needed characters left characters exceeded