SAP PM Order Status Table Query Generator (JEST, TJ02T, STATUS_TEXT_EDIT)

REPORT zpm_order_status_list.

* Orders in plant 1000, category 30, type PM01
* with active system status TECO and without active status CLSD.
* Internal status examples (check TJ02T in your system):
*   I0001 CRTD   I0002 REL   I0045 TECO   I0046 CLSD   I0076 DLFL

TYPES: BEGIN OF ty_obj,
         aufnr TYPE aufk-aufnr,
         auart TYPE aufk-auart,
         objnr TYPE aufk-objnr,
       END OF ty_obj.

DATA: gt_obj  TYPE STANDARD TABLE OF ty_obj,
      gs_obj  TYPE ty_obj,
      gv_incl TYPE tj02t-istat,
      gv_excl TYPE tj02t-istat,
      gv_line TYPE bsvx-sttxt,
      gv_user TYPE bsvx-sttxt.

PARAMETERS: p_werks TYPE aufk-werks  DEFAULT '1000' OBLIGATORY,
            p_autyp TYPE aufk-autyp  DEFAULT '30' OBLIGATORY,
            p_auart TYPE aufk-auart  DEFAULT 'PM01' OBLIGATORY,
            p_incl  TYPE tj02t-txt04 DEFAULT 'TECO' OBLIGATORY,
            p_excl  TYPE tj02t-txt04 DEFAULT 'CLSD',
            p_erdat TYPE aufk-erdat  DEFAULT '20260101'.

START-OF-SELECTION.

* Translate the English 4-character texts to internal numbers once,
* so the main query does not depend on the logon language.
  SELECT SINGLE istat FROM tj02t INTO gv_incl
    WHERE spras = 'E'
      AND txt04 = p_incl.
  IF sy-subrc <> 0.
    MESSAGE 'Include status not found in TJ02T' TYPE 'E'.
  ENDIF.

  IF p_excl IS NOT INITIAL.
    SELECT SINGLE istat FROM tj02t INTO gv_excl
      WHERE spras = 'E'
        AND txt04 = p_excl.
  ENDIF.

* JEST-INACT = space means the status is currently active.
  SELECT a~aufnr a~auart a~objnr
    INTO TABLE gt_obj
    FROM aufk AS a
    INNER JOIN jest AS j ON j~objnr = a~objnr
    WHERE a~werks = p_werks
      AND a~autyp = p_autyp
      AND a~auart = p_auart
      AND a~erdat >= p_erdat
      AND j~stat  = gv_incl
      AND j~inact = space
      AND NOT EXISTS ( SELECT objnr FROM jest
                         WHERE objnr = a~objnr
                           AND stat  = gv_excl
                           AND inact = space ).

  LOOP AT gt_obj INTO gs_obj.
    CLEAR: gv_line, gv_user.
    CALL FUNCTION 'STATUS_TEXT_EDIT'
      EXPORTING
        flg_user_stat    = 'X'
        objnr            = gs_obj-objnr
        only_active      = 'X'
        spras            = sy-langu
      IMPORTING
        line             = gv_line
        user_line        = gv_user
      EXCEPTIONS
        object_not_found = 1
        OTHERS           = 2.
    IF sy-subrc = 0.
      WRITE: / gs_obj-aufnr, gs_obj-auart, gv_line, gv_user.
    ENDIF.
  ENDLOOP.

* User statuses are E-numbers in JEST-STAT. The status profile
* comes from JSTO-STSMA; texts are in TJ30T by profile and language.
  TYPES: BEGIN OF ty_ustat,
           objnr TYPE jest-objnr,
           stat  TYPE jest-stat,
           txt04 TYPE tj30t-txt04,
         END OF ty_ustat.
  DATA gt_ustat TYPE STANDARD TABLE OF ty_ustat.

  IF gt_obj IS NOT INITIAL.
    SELECT j~objnr j~stat t~txt04
      INTO TABLE gt_ustat
      FROM jest AS j
      INNER JOIN jsto  AS s ON s~objnr = j~objnr
      INNER JOIN tj30t AS t ON t~stsma = s~stsma
                           AND t~estat = j~stat
      FOR ALL ENTRIES IN gt_obj
      WHERE j~objnr = gt_obj-objnr
        AND j~inact = space
        AND t~spras = sy-langu.
  ENDIF.