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