Setting the Expiry date in SAP Query

By Jayanta Bej, Capgemini

Business Requirement: The client wants to maintain expiry dates to each and every SAP queries that are running in production. The expired query will not be allowed to run in production. 

Design Logic:

1. Maintain a custom table with two fields – query name and query expiry date.

2. Design a custom function module which will collect the current SAP query name at run time (i.e. during query execution); check the expiry date of that query from the custom table and generate an error message if the query is expired or work as per the code in the infoset if it’s not expired.

3. Put that custom function module in the infosets of all the queries. Keep all the executable codes of the infosets in the ‘ELSE’ part of the sy-subrc check.

Explanations: 

Step 1: Create a Z-table ZQUERY_EXPIRE along with a table maintenance generator.

For more details on user group, infoset and query name, refer to table AQLQCAT.

Step 2: Maintain query names along with their expiry dates in the Z-table ZQUERY_EXPIRE via SM30.

Step 3: Design a custom function module ZQUERY_EXP_CHECK with an exception QUERY_EXPIRED (Query has expired). 

FUNCTION zquery_exp_check.
*"---------------------------------------------------------------
*"*"Local Interface:
*"  EXCEPTIONS
*"      QUERY_EXPIRED
*"---------------------------------------------------------------

  TABLES: zquery_expire.

  CONSTANTS: c_fill TYPE c VALUE '='.
  DATA: v_sycprog LIKE sy-cprog,
        v_user_grp1(30) TYPE c,
        v_user_grp2(14) TYPE c,
        v_query_name1(14) TYPE c,
        v_rest(14) TYPE c,
        v_query_name TYPE aqs_quname.

  TYPES: BEGIN OF ty_query,
           qnum     TYPE aqs_quname,
           exp_date TYPE datum,
         END OF ty_query.

  DATA: wa_query TYPE ty_query,
        i_query TYPE STANDARD TABLE OF ty_query INITIAL SIZE 0.

  v_sycprog = sy-cprog.
  v_user_grp1 = v_sycprog+4(26).
  v_user_grp2 = v_user_grp1+12(14).
  SPLIT v_user_grp2 AT c_fill INTO v_query_name1 v_rest.
  v_query_name = v_query_name1.

  SELECT qnum
         exp_date
  FROM zquery_expire
  INTO TABLE i_query
  WHERE qnum EQ v_query_name.

  LOOP AT i_query INTO wa_query.

* If the query expires, generate the error message
    IF wa_query-exp_date LT sy-datum.
      MESSAGE e000(zbasis_msg) RAISING query_expired.

* If the expiary date is today or yet to come, don't do anything
    ELSE.
      MESSAGE i002(zbasis_msg).
    ENDIF.
  ENDLOOP.
ENDFUNCTION. 

Step 4: Call this custom function module from the START-OF-SELECTION code sections of all the infosets of the queries that are maintained in the custom table ZQUERY_EXPIRE.  

Here we’ve added the following code in all the infosets of the 3 queries maintained in the custom table. 

CALL FUNCTION 'ZQUERY_EXP_CHECK'
       EXCEPTIONS
         query_expired = 1
         OTHERS        = 2.

     IF sy-subrc <> 0.
       MESSAGE ID sy-msgid TYPE sy-msgty NUMBER sy-msgno
               WITH sy-msgv1 sy-msgv2 sy-msgv3 sy-msgv4.
     ELSE.
       WRITE: /5 'Hello1'.
     ENDIF. 

Test: Suppose today’s date is 20/03/2009. The expiry date of the query BEJ_QRY_TEST1 was 18.03.2009 (as maintained in the Z-table).         

The expiry date of the query QRY_TEST3 is today (20/03/2009). 

Scenario 1: Execute the query BEJ_QRY_TEST1 (expiry date is less than the current date) from SQ01.

Execute; the following error message will be generated.

Scenario 2: Execute the query QRY_TEST3 (expiry date is equal to the current date) from SQ01.

Execute; the actual code of the infoset will be executed.