Skip to Content
Technical Articles
Author's profile photo Michael Keller

get customizing from different levels

Dear community, from time to time I have the need to read customizing from custom defined database tables. You probably too? ๐Ÿ˜‰ Often these database tables have one or two organizational elements as primary key. For example company code and purchasing organization. Or company code and sales organization. The following logic should be used while reading:

If an entry is found with both organizational elements, this must be used. Otherwise an entry is only searched for with the first organizational element, the second one is initial.

Need an example? Here is a little design I’ve made to demonstrate what I mean.

example table design

“ORGANIZATION” could be the company code and “SUB_ORGANIZATION” could be the sales organization. Let’s call them different “levels”. I’ve made some example entries to play with.

example%20entries

example entries

I’ve programmed three different ways of solving this problem/requirement (see methods “GET_BY_SOLUTION_<NR>”). I’ve seen them in a slightly modified form in real projects. Personally, I like solution 2. But I assume that there are more, perhaps much simpler solutions? Whereby simpler is perhaps not necessarily more understandable or more elegant. Who knows? ๐Ÿ™‚

CLASS lcx_error DEFINITION INHERITING FROM cx_static_check.
ENDCLASS.

CLASS lcx_error IMPLEMENTATION.
ENDCLASS.

CLASS lcl_switches DEFINITION.

  PUBLIC SECTION.
    METHODS constructor.

    METHODS get_by_solution_1
      IMPORTING
        iv_organization     TYPE char4
        iv_sub_organization TYPE char4
      RETURNING
        VALUE(rs_result)    TYPE zswitches.

    METHODS get_by_solution_2
      IMPORTING
        iv_organization     TYPE char4
        iv_sub_organization TYPE char4
      RETURNING
        VALUE(rs_result)    TYPE zswitches.

    METHODS get_by_solution_3
      IMPORTING
        iv_organization     TYPE char4
        iv_sub_organization TYPE char4
      RETURNING
        VALUE(rs_result)    TYPE zswitches.

  PRIVATE SECTION.
    METHODS static_select
      IMPORTING
        iv_organization     TYPE char4
        iv_sub_organization TYPE char4
      RETURNING
        VALUE(rs_result)    TYPE zswitches.

    METHODS dynamic_select
      IMPORTING
        iv_condition     TYPE string
      RETURNING
        VALUE(rs_result) TYPE zswitches
      RAISING
        lcx_error.
ENDCLASS.

CLASS lcl_switches IMPLEMENTATION.
  METHOD constructor.
  ENDMETHOD.

  METHOD get_by_solution_1.
    SELECT SINGLE *
           FROM zswitches
           INTO rs_result
           WHERE organization = iv_organization
           AND sub_organization = iv_sub_organization .

    IF sy-subrc <> 0.
      SELECT SINGLE *
             FROM zswitches
             INTO rs_result
             WHERE organization = iv_organization
             AND sub_organization = space.

      IF sy-subrc <> 0.
        RETURN.
      ENDIF.
    ENDIF.
  ENDMETHOD.

  METHOD get_by_solution_2.
    rs_result = static_select( iv_organization     = iv_organization
                               iv_sub_organization = iv_sub_organization ).
    IF rs_result IS INITIAL.
      rs_result = static_select( iv_organization     = iv_organization
                                 iv_sub_organization = space ).
    ENDIF.
  ENDMETHOD.

  METHOD get_by_solution_3.
    IF iv_organization IS INITIAL OR iv_sub_organization IS INITIAL.
      RETURN.
    ENDIF.

    DATA(lv_condition) = |organization = { iv_organization  } AND | &&
                         |sub_organization = { iv_sub_organization }|.

    TRY.
        rs_result = dynamic_select( lv_condition ).
      CATCH lcx_error.
        IF lv_condition CS 'space'.
          RETURN.
        ELSE.
          lv_condition = |organization = { iv_organization  } AND | &&
                         |sub_organization = space |.

          RETRY.
        ENDIF.
    ENDTRY.
  ENDMETHOD.

  METHOD static_select.
    SELECT SINGLE *
           FROM zswitches
           INTO rs_result
           WHERE organization = iv_organization
           AND sub_organization = iv_sub_organization.

    IF sy-subrc <> 0.
      RETURN.
    ENDIF.
  ENDMETHOD.

  METHOD dynamic_select.
    TRY.
        SELECT SINGLE *
               FROM zswitches
               INTO rs_result
               WHERE (iv_condition).

      CATCH cx_sy_dynamic_osql_error INTO DATA(osql_error).
        MESSAGE osql_error->get_text( ) TYPE 'I'.
        RAISE EXCEPTION NEW lcx_error( ).
    ENDTRY.

    IF sy-subrc <> 0.
      RAISE EXCEPTION NEW lcx_error( ).
    ENDIF.
  ENDMETHOD.
ENDCLASS.

My example serves as a basis for discussion here. Any proposals to solve the requirement on a different way? ๐Ÿ™‚

 

Best regards, thanks for reading and please stay healthy

Michael

 

P.S.: Please support the virtual wishing well.

P.S.S.: Not tired of reading blogs? Visit the Devtoberfest 2020.

Assigned Tags

      10 Comments
      You must be Logged on to comment or reply to a post.
      Author's profile photo Gaurav Karkara
      Gaurav Karkara

      Hi Michael,

      Quite a common scenario, thanks for initiating this discussion. My observation - Probably 4th option could be to select data from table using first key field only and then do the READ in code where IF~ELSE for second key field can be used. This way, DB is hit only once if second key field is space. Otherwise, we are hitting it twice.

      I am looking forward to more comments here...

      Gaurav

      Author's profile photo Michael Keller
      Michael Keller
      Blog Post Author

      How could I have forgotten this solution? Thanks for the note!

      Author's profile photo Karl Peham
      Karl Peham

      In the past, I've been using something like this:

            SELECT *
                   FROM zswitches
                   WHERE organization = iv_organization 
                     AND sub_organization IN ( iv_sub_organization, space )
                   ORDER BY sub_organization DESCENDING.

      This is working even with many optional key fields, returning the "best match" in first line (the sequence of columns in the "order by" clause defines the priorization!).

      In my company we are using a small framework with a set of class methods for that purpose, which is reading all lines in a similar way. It reads all relevant lines first, then loops through the result identifying the best match.

       

      Karl

      Author's profile photo Michael Keller
      Michael Keller
      Blog Post Author

      Nice solution. Have to check that. Thanks! ๐Ÿ™‚

      Author's profile photo Enno Wulff
      Enno Wulff

      Hey Michael,

      the solution to a requirement that every company has sooner or later... ๐Ÿ˜€

       

      three suggestions:

      • I would add a module field as first key field to define switches for different modules/ processes. here could also aย sub module field be added to have a high level module (Material Master, Sales % Distribution and so on) and a low level for specific programs or processes
        • the disadvantage is, that it's often not a good idea to have too many departments for one table.
        • as a work-around you could define the main table as a structure and create different tables for each module and implement the structure.
        • In the constructor you might define the module (like MM) and read the data dynamiically from table ZSWITCH_MM
      • instead of two org fields you just might one longer one. the application then needs to know what fields are relevant. when using a concatenated org field you also could dynamically adapt an access sequence
        • requested org field: 2000-20-02
        • customized org field: 2000
        • disadvantage: more complicated
        • Advantage: more flexible
      • with you data structure you are not able to transport one single exit. Always the complete entry will be transported.
      • plus: if there are more exits needed, then the database table must be extended.
        • Solution: also use exit number as key field:
          • MODULE
          • ORG_KEY_1
          • ORG_KEY_2
          • EXIT_NR
        • disadvantage: maintenance maybe is not as comfortable as only "one line" can be maintained at once
          • if you want to display (or edit) data "in a grid" you will need to programm a specific report to get that
        • advantage: more flexible for enhancements and transporting single exit definitions

       

      For my company I build a complete dynamic tool for switching exits on and off.

      • The org fields can be defined completely by the user
        • access sequence can be defined
      • Exits can be documented
      • Exits can not only be TRUE or FALSE but can also have other defined values. That makes usage even more flexible ๐Ÿ˜‰
        • most common example: customized messages. For some org units (sales document type) a message must be an [E]rror , in other cases it should be a [W]arning. The exit characteristic then will not be "X" for check something, but "E" for check and display an error and "W" forย check and display a warning.

      Cheers
      ~Enno

      Author's profile photo Michael Keller
      Michael Keller
      Blog Post Author

      Hi Enno, I like the idea of having a longer org field with a delimiter to split the value in meaningful parts. That's an interesting design idea. It could have impact to some select possibilities but that's an other discussion. I didn't unterstand the point with the "exits". Could you provide an example? Thanks in advance.

      Author's profile photo Enno Wulff
      Enno Wulff

      Exit = Switch ?

      Author's profile photo Sougata Chatterjee
      Sougata Chatterjee

      Yes Michael, I do have an elegant solution ?

      Note: I have not compiled the code; you will get syntax errors but I will try and give you the idea.

      ย 

      Define your Exception object as Resumable.

      Then:

      Definition:

      METHODS get_by_solution_4
        IMPORTING  iv_organization      TYPE char4
                   iv_sub_organization  TYPE char4
        RETURNING VALUE(rs_result)      TYPE switches
        RAISING RESUMABLE(lcx_error).

      ย 

      Implementation:

      METHOD get_by_solution_4.
      
       SELECT SINGLE *
          FROM zswitches
          INTO rs_result
          WHERE organization     = iv_organization
            AND sub_organization = iv_sub_organization .
      
       CHECK sy-subrc <> 0.
      
       RAISE RESUMABLE EXCEPTION lcx_error.   "Resumable
      
       SELECT SINGLE *
         FROM zswitches
         INTO rs_result
         WHERE organization     = iv_organization
           AND sub_organization = space.
      
       CHECK sy-subrc <> 0.
      
       RAISE EXCEPTION lcx_error.             โ€œNot Resumable
      
      ENDMETHOD.
      

      Caller:

      TRY.
       DATA(ls_result) = get_by_solution_4( param1 = value1 param2 = value2).
      
       CATCH BEFORE UNWIND lcx_error INTO DATA(lo_error).
        IF lo_error->is_resumable = abap_true.
         RESUME.
        ELSE.
         โ€œcaller handles exception here
        ENDIF.
      ENDTRY.

      ย 

      Lunch is getting cold so I must go right now ?

      Author's profile photo Michael Keller
      Michael Keller
      Blog Post Author

      Another interesting solution. However, I have to admit that I have to debug them on the system to understand everything ๐Ÿ™‚

      Author's profile photo Sougata Chatterjee
      Sougata Chatterjee

      Yeah โ€ฆ. I find it quite powerful and elegant because when it throws the exception I have all the information in the exception object of what exactly happened inside to be able to control the subsequent flow/processing without having to depend on additional helper variables and/or IF/ELSE statements etcโ€ฆ I could just continue to call the method like normal as long as it either Resumes it or throws a hard/non-resumable exception.

      The point is, from the OO perspective, the outside/caller should have all the information of what exactly happened inside (the method in case of an error happened) thus controlling the subsequent processing steps of the calling program.

      Sougata.