Generate a Report with Customer Master Long Texts

Jimbo's picture
Submitted by Jimbo on Fri, 06/07/2019 - 04:12

http://www.apogeephoto.com/oct2013/Long_photos/rubbing-off-goat-mtn-goats_5392.jpg|http://www.wakeolda.com/Photos/mtevans99/goat_climbing_2.jpg|http://bloximages.chicago2.vip.townnews.com/missoulian.com/content/tncms/assets/v3/editorial/2/95/295319a2-dd10-11e2-aaa0-0019bb2963f4/51c8b1d9b08cf.image.jpg|http://ngm.nationalgeographic.com/u/TvyamNb-BivtNwcoxtkc5xGBuGkIMh_nj4UJHQKuorjj2yrzzTsBkwgD8q53LHYCkF7IMbQbjmJgWA/|http://www.maxingout.com/images/100%20update/big-horn-goat.jpg|http://cache2.allpostersimages.com/p/LRG/28/2806/OUCOD00Z/posters/edwards-walter-meayers-close-view-of-a-goat-with-long-horns.jpgBecause of the cumbersome way that SAP stores long texts, it is not possible to access them with a custom query, with nested select statements or even with complex joins. The only way to access the long texts for the purpose of a report is through the READ_TEXT function.

Unlike the report for Material Master long texts, this provides a list of long text types related to Customer Masters instead of a radio button backed by custom ABAP code. Once selected, the report then knits together the parameters that are passed to the READ_TEXT function.

The first step is to create a selection screen that allows for the selection of which Customer Masters should appear in the report along with the type long text to be included. In this case the screen will be relatively complex with SELECT-OPTIONS and a drop down menu. It is important to note that the tables KNA1, KNB1, KNVV and T002 are declared and cannot be used in User Defined Routines.

TYPE-POOLS: vrm.
DATA: gt_list     TYPE vrm_values.
DATA: gwa_list    TYPE vrm_value.
DATA: gt_values   TYPE TABLE OF dynpread,
      gwa_values  TYPE dynpread.
DATA: gv_selected_value(10) TYPE c.
data: lTTXID like TTXID, lTTXIT like TTXIT.
tables: kna1, knb1, knvv, T002.
SELECTION-SCREEN BEGIN OF BLOCK B1 WITH FRAME TITLE btext1.
  PARAMETERS: p_list(10) TYPE c AS LISTBOX VISIBLE LENGTH 20.
  SELECT-OPTIONS: s_KUNNR FOR KNA1-KUNNR,     "Customer Number
                  s_BUKRS for KNB1-BUKRS,     "Company Code
                  s_vkorg FOR KNVV-vkorg,     "Sales Organization
                  s_vtweg FOR KNVV-vtweg,     "Distribution Channel
                  s_SPART for KNVV-SPART,     "Division
                  s_SPRAS for T002-SPRAS.
SELECTION-SCREEN END OF BLOCK B1.
SELECTION-SCREEN BEGIN OF BLOCK B2 WITH FRAME TITLE btext2.
  parameters p_break as checkbox.
SELECTION-SCREEN END OF BLOCK B2.
selection-screen begin of block bfile with frame title bfile1.
  selection-screen begin of line.
    PARAMETERS : P_RAD1 RADIOBUTTON GROUP RB1 default 'X'.
    selection-screen comment 3(20) coscreen.
  selection-screen end of line.
  selection-screen begin of line.
    PARAMETERS : P_RAD2 RADIOBUTTON GROUP RB1.
    selection-screen comment 3(14) cofile.
    parameters: p_filenm type string.
  selection-screen end of line.
  selection-screen begin of line.
    PARAMETERS : P_RAD3 RADIOBUTTON GROUP RB1.
    selection-screen comment 3(16) coexcel.
    parameters: p_friend as checkbox default 'X'.
    selection-screen comment 23(30) cofriend.
  selection-screen end of line.
selection-screen end of block bfile.

Once the selection screen is complete, it is time to populate the drop down list with all of the possible long texts that exist in the system--including the custom ones! This turns out to be easier than one might think as they are all stored in the table TTXID and described in table TTXIT.

There is no guarantee that a description exists in the user's language, so the code loops through the TTXIT table once for any available language and then again to see if the user's language is available. That record containing the concatenated TDOBJECT and TDID value, along with the associated description, are then appended to the gt_list internal table where it can be associated with the parameter P_LIST by the VRM_SET_VALUES function.

initialization.
select * from TTXID into lTTXID
 where TDOBJECT eq 'KNA1' or TDOBJECT eq 'KNB1' or TDOBJECT eq 'KNVV'.
  concatenate lTTXID-TDOBJECT lTTXID-TDID into gwa_list-key.
  select * from TTXIT into lTTXIT
   where TDOBJECT eq lTTXID-TDOBJECT
     and TDID     eq lTTXID-TDID.
    concatenate lTTXID-TDOBJECT lTTXIT-TDTEXT into gwa_list-text
     separated by space.
  endselect.
  select * from TTXIT into lTTXIT
   where TDOBJECT eq lTTXID-TDOBJECT
     and TDID     eq lTTXID-TDID
     and TDSPRAS  eq SY-LANGU. "Use user's language if possible.
    concatenate lTTXID-TDOBJECT lTTXIT-TDTEXT into gwa_list-text
     separated by space.
  endselect.
  append gwa_list to gt_list.
endselect.
  CALL FUNCTION 'VRM_SET_VALUES'
    EXPORTING
      id              = 'P_LIST'
      values          = gt_list
    EXCEPTIONS
      id_illegal_name = 1
      OTHERS          = 2.

The last part of building the selection screen is adding some text and removing the frame and parameters that LSMW expects. These aren't use as this LSMW object is being used as a framework for reporting and there are no incoming records to handle. 

loop at screen. "Clean up screen by removing unwanted lines.
  if g_cnt_records_read_progress lt 7.
    screen-invisible = 1.
    screen-active = 0.
    modify screen.
  endif.
  add 1 to g_cnt_records_read_progress.
endloop.

btext1 = 'Report parameters'.
btext2 = 'Testing tools'.
bfile1 = 'Export to file'.
coscreen = 'Write to screen.'.
cofile = 'Write to file:'.
coexcel = 'Present in Excel.'.
cofriend = 'Friendly field names.'.

The next step is to create an internal table to hold a list of Customer Masters and related data that will be looped through to find long texts. The KNVV structure was chosen in this case as it can be used to hold Customer Master number, Customer Master number and Company Code or Customer Master Number, Sales Org, Division and Distribution Channel information effectively.

Additionally, the code is sped up considerably by reading the contents of the STXH table into an internal table where the records have information related to the long texts chosen in the selection screen. This obviates the need to call READ_TEXT function for every combination of Customer Master and language, but instead calls the function only when a long text record exists. 

data: it_KNVV type standard table of KNVV initial size 0.
data: lKNVV like KNVV, lT002 like T002, lKNA1 like KNA1,
 it_KNA1 type standard table of KNA1 initial size 0,
 lKNB1 like KNB1,
 lTDNAME like THEAD-TDNAME,
 it_TLINE type standard table of TLINE,
 wa_TLINE type TLINE, lText type string.

"Folliwing lines added to severely optimize code.
data: wa_STXH like STXH,
      it_STXH type standard table of STXH initial size 0.

types: begin of i_Report,
        KUNNR(10) type c,
        NAME1 like KNA1-NAME1,
        VKORG(5) type c,
        VTWEG(5) type c,
        SPART(5) type c,
        SPRAS(2) type c,
        Text type string,
       end of i_Report.
data: wa_Report type i_Report,
      it_Report type standard table of i_Report initial size 0.
data: cID(4) type c, cObject(10) type c.
cObject = p_List+0(4).
cID = p_List+4(4).

"Speeding things up to shave hours off of runtime.
select * from STXH into table it_STXH
 where TDOBJECT eq cObject
   and TDID     eq cID
   and TDSPRAS in s_SPRAS.
select * from KNA1 into table it_KNA1 where KUNNR in s_KUNNR.

Now the it_KNVV table is populated with the desired Customer Master data based on the type of long text chosen in the selection screen. Where the long text is based on the Company Code data, the it_KNVV-VKORG field is populated with the LFB1-BUKRS for use later in the program. 

if cObject eq 'KNVV'.
  select * from KNVV into table it_KNVV
   where KUNNR in s_KUNNR
     and VKORG in s_VKORG
     and VTWEG in s_VTWEG
     and SPART in s_SPART.
elseif cObject eq 'KNB1'.
  select * from KNB1 into lKNB1
   where KUNNR in s_KUNNR
     and BUKRS in s_BUKRS.
    lKNVV-KUNNR = lKNB1-KUNNR.
    lKNVV-VKORG = lKNB1-BUKRS. "Stored in wrong field, but moot.
    append lKNVV to it_KNVV.
  endselect.
else.
  loop at it_KNA1 into lKNA1.
    lKNVV-KUNNR = lKNA1-KUNNR.
    append lKNVV to it_KNVV.
  endloop.
endif.

Next, a header line is added to the report for convenience. The fields of wa_Report are simply populated with their own names and then that record is appended to the it_Report internal table. 

wa_Report-KUNNR = 'KUNNR'.
wa_Report-NAME1 = 'NAME1'.
if cObject eq 'KNVV'.
  wa_Report-VKORG = 'VKORG'.
  wa_Report-VTWEG = 'VTWEG'.
  wa_REPORT-SPART = 'SPART'.
elseif cObject eq 'KNB1'.
  wa_Report-VKORG = 'BUKRS'. "Company code. =P
else.
  "Leave these headers blank.
endif.
wa_Report-SPRAS = 'SPRAS'.
wa_Report-Text = 'Text'.
append wa_Report to it_Report.

Now the work begins. The code loops through the it_KNVV and it_STXH internal tables and, where they meet, calls the READ_TEXT function using the values from the tables as parameters. 

loop at it_KNVV into lKNVV.
  perform CheckRuntime. "Prevent 'Uninterrupted Runtime' error.

  if cObject eq 'KNVV'.
    concatenate lKNVV-KUNNR lKNVV-VKORG lKNVV-VTWEG lKNVV-SPART
     into lTDNAME.
  elseif cObject eq 'KNB1'.
    concatenate lKNVV-KUNNR lKNVV-VKORG into lTDNAME.
  else.
    lTDNAME = lKNVV-KUNNR.
  endif.
  loop at it_STXH into wa_STXH
   where TDNAME eq lTDNAME.

    CALL FUNCTION 'READ_TEXT'
      EXPORTING
        CLIENT                  = SY-MANDT
        ID                      = cID
        LANGUAGE                = wa_STXH-TDSPRAS
        NAME                    = lTDNAME
        OBJECT                  = cObject
      TABLES
        LINES                   = it_TLINE
      EXCEPTIONS
        ID                      = 1
        OTHERS                  = 8.

    IF SY-SUBRC EQ 0.
      loop at it_KNA1 into lKNA1 where KUNNR = lTDNAME+0(10).
        wa_Report-KUNNR = lKNA1-KUNNR.
        wa_Report-NAME1 = lKNA1-NAME1.
        wa_Report-VKORG = lKNVV-VKORG.
        wa_Report-VTWEG = lKNVV-VTWEG.
        wa_Report-SPART = lKNVV-SPART.
        wa_Report-SPRAS = wa_STXH-TDSPRAS.
        LOOP AT it_TLINE INTO wa_TLINE.
          wa_Report-TEXT = wa_TLINE-TDLINE.
          append wa_Report to it_Report.
        ENDLOOP.
      endloop.
    ENDIF.
  endloop. 
endloop.

Finally, the it_Report internal table is populated with all of the long text values related to the Customer Masters selected on the selection screen of the type selected on the selection screen. The final step is to loop through the table and display the records on the screen, export the records to a text file or to display the records in Excel based on the options from the selection screen. 

if p_Rad1 eq 'X'. "Write to screen...
  loop at it_Report into wa_Report.
    write: / wa_Report-KUNNR, wa_Report-NAME1,
     wa_Report-VKORG, wa_Report-VTWEG, wa_Report-SPART,
     wa_Report-SPRAS, wa_Report-Text.
  endloop.
elseif p_Rad2 eq 'X'. "Write to file...
  if p_Filenm ne ''.  "Export to file!
    CALL FUNCTION 'GUI_DOWNLOAD'
      EXPORTING
        FILENAME                        = p_filenm
        FILETYPE                        = 'ASC'
        APPEND                          = ''
*        CODEPAGE                        = 'IBM'
        WRITE_FIELD_SEPARATOR = 'X'
        HEADER = '00'
        TRUNC_TRAILING_BLANKS = 'X'
      TABLES
        DATA_TAB                        = it_Report
      EXCEPTIONS
        FILE_WRITE_ERROR                = 1
        OTHERS                          = 2.
    endif.
else. "Show in Excel...
  if p_friend eq 'X'.
    wa_STXH-TDSPRAS = sy-langu.
  else.
    wa_STXH-TDSPRAS = ''. "No friendly lables.
  endif.
  perform InternalTableToExcel using it_Report 1 wa_STXH-TDSPRAS.
endif.

Download this tool
This tool, along with many others are included in Jimbo's LSMW Toolbox. It's free and much easier than trying to copy-paste this into a new LSMW object. Additionally, all of the required subroutines are included, so there's that, too.