Database table info and downloading the table data

By Swarna S, Tata Consultancy Services

*&---------------------------------------------------------------------*
*& Report  Z_FIELDINFO                                                 *
*& Author :Swarna.S.
*&---------------------------------------------------------------------*
*&
*& AS Description
*& Simple ALV report -- User enters a table name and gets in ALV the
*& list of all the fields in table with their salient characteristics
*& and the key field is given a different color for quick recognition
*& and when the user presses the button list it downloads all the table
*& data in excel format as DAT file and when user presses EXIT, they
*& can leave the program
*&---------------------------------------------------------------------*

REPORT  Z_FIELDINFO.

* Published at SAPTechnical.com

*Type pools declaration for ALV.
TYPE-POOLS : slis.

*Type pool declarations for ABAP language
type-pools : abap.

*String for filename
data p_string type string.

*Structure declaration for the result of Function module
TYPES : BEGIN OF ty_fies.
        INCLUDE STRUCTURE DFIES.
TYPES :  END OF ty_fies.

*Structure declaration for header - fieldnames
types : BEGIN OF ty_header,
        fieldname(100) TYPE c,
        END OF ty_header.

*Internal table and work area declaration for header
data : it_header type standard table of ty_header initial size 0,
       wa_header type ty_header.

*Internal table and work area declaration for FM .
data : it_fies type standard table of ty_fies initial size 0,
       wa_fies type ty_fies.

*Dynamic internal table declarations
DATA STRUCT_REF TYPE REF TO CL_ABAP_STRUCTDESCR.
DATA wa_FieldCAT TYPE LVC_S_FCAT.
DATA IT_FIELDCAT TYPE LVC_T_FCAT.
DATA DATAREFERENCE TYPE REF TO DATA.

* Field-Symbols for dynamic internal table

FIELD-SYMBOLS:
              <dyn_tab> TYPE ANY TABLE,
              <DYN_TABLE> TYPE STANDARD TABLE,
              <DESR_COMP> TYPE ABAP_COMPDESCR,
              <MY_FS>  TYPE ANY.

*Structure declaration for dd02t
TYPES : BEGIN OF ty_desc.
        INCLUDE STRUCTURE DD02T.
TYPES :  END OF ty_desc.

*Internal table and work area declaration for dd02t .
data : it_desc type standard table of ty_desc initial size 0,
       wa_desc type ty_desc.

*Structure declaration for the output in ALV format
TYPES : BEGIN OF ty_output.
        INCLUDE STRUCTURE DFIES.
TYPES : color_line(4) TYPE c,  " Line color
        END OF ty_output.

*Internal table and work area declaration for output ALV.
data : it_output type standard table of ty_output initial size 0,
       wa_output type ty_output.

*Data declarations for ALV
DATA: c_cont type ref to cl_gui_custom_container,
      c_alvgd type ref to cl_gui_alv_grid,
      it_fcat            TYPE lvc_t_fcat,
      it_layout          TYPE lvc_s_layo.

* Grid title.
data : text2 type string.

* Selection screen
selection-screen begin of block blk with frame.
*HERE ENTER THE TABLE NAME
parameters : p_table  like dd02l-tabname,
*HERE ENTER THE PATH WHERE YOU HAVE TO DOWNLOAD YOUR FILE
*please enter the file as XLS extension as we download
*the data in DAT format as 'C:\temp\file.xls'
             p_file like rlgrap-filename.
selection-screen end of block blk.

*initialization event
INITIALIZATION.

*Start of selection event
START-OF-SELECTION.

*fetch values from the DD02t
  select * from DD02t into table it_desc where tabname = p_table and
  ddlanguage = 'E'.

*Function module to get all the fields and their characteristics in a
*table

  CALL FUNCTION 'DDIF_FIELDINFO_GET'
    EXPORTING
      TABNAME              = p_table
*   FIELDNAME            = ' '
*   LANGU                = SY-LANGU
*   LFIELDNAME           = ' '
*   ALL_TYPES            = ' '
*   GROUP_NAMES          = ' '
*   UCLEN                =
* IMPORTING
*   X030L_WA             =
*   DDOBJTYPE            =
*   DFIES_WA             =
*   LINES_DESCR          =
   TABLES
     DFIES_TAB            = it_fies
*   FIXED_VALUES         =
 EXCEPTIONS
   NOT_FOUND            = 1
   INTERNAL_ERROR       = 2
   OTHERS               = 3
            .
  IF SY-SUBRC <> 0.
* MESSAGE ID SY-MSGID TYPE SY-MSGTY NUMBER SY-MSGNO
*         WITH SY-MSGV1 SY-MSGV2 SY-MSGV3 SY-MSGV4.
  ENDIF.

*Appending the values fetched through the Function module in the output
*Alv report
  loop at it_fies into wa_fies.

    wa_output-fieldname = wa_fies-fieldname.
    wa_output-inttype = wa_fies-inttype.
    wa_OUTPUT-MEMORYID = wa_FIES-MEMORYID.
    wa_OUTPUT-CHECKTABLE = wa_fies-checktable.
    wa_OUTPUT-LENG = wa_FIES-LENG.
    wa_output-fieldtext = wa_fies-fieldtext.
    wa_output-keyflag = wa_fies-keyflag.
    wa_output-convexit = wa_fies-convexit.
    wa_output-domname = wa_fies-domname.
    wa_output-rollname = wa_fies-rollname.
    if wa_fies-keyflag = 'X'.

      wa_output-color_line = 'C600'.

    endif.

    APPEND wa_output to it_output.
    CLEAR wa_output.
  endloop.

* Table description for pop up
  READ TABLE IT_DESC INTO WA_DESC INDEX 1.

  text2 = wa_desc-ddtext.

*POP up to show the table and its defintion
  CALL FUNCTION 'POPUP_TO_DISPLAY_TEXT'
    EXPORTING
      TITEL        = 'Table for display'
      TEXTLINE1    = p_table
      TEXTLINE2    = text2
      START_COLUMN = 25
      START_ROW    = 6.

*Call the ALV screen with custom container
  call screen 0600.

*On this statement double click  it takes you to the screen painter SE51
*.Enter the attributes
*Create a Custom container and name it C_CONT and OK code as OK_CODE.
*Save check and Activate the scren painter.
*NOw a normal screen witn number 600 is created which holds the ALV grid
*PBO of the actual screen,Here we can give a title and customized menus
*Go to SE41 and create status GETZ and create THE function code 'LIST'
*and 'EXIT' with icons and icon texts
*Also create a TitleBar ALV and give the relevant title.
*&---------------------------------------------------------------------*
*&      Module  STATUS_0600  OUTPUT
*&---------------------------------------------------------------------*
*       text
*----------------------------------------------------------------------*
MODULE STATUS_0600 OUTPUT.
  SET PF-STATUS 'GETZ'.        "Defining own PF status
  SET TITLEBAR 'ALV'.          "Defining a title

ENDMODULE.                 " STATUS_0600  OUTPUT

* calling the PBO module ALV_OUTPUT.
*&---------------------------------------------------------------------*
*&      Module  ALV_GRID  OUTPUT
*&---------------------------------------------------------------------*
*       text
*----------------------------------------------------------------------*
MODULE ALV_GRID OUTPUT.

*Create object for container
  CREATE OBJECT c_cont
         EXPORTING
            container_name = 'C_CONT'.

*Create object for ALV grid
  CREATE OBJECT c_alvgd
      EXPORTING
        i_parent = c_cont.

* Set field for ALV
  PERFORM alv_build_fieldcat.
* Set ALV attributes FOR LAYOUT
  PERFORM alv_report_layout.

  CHECK NOT c_alvgd IS INITIAL.

* Call ALV GRID
  CALL METHOD c_alvgd->set_table_for_first_display
    EXPORTING
      is_layout                     = it_layout
    CHANGING
      it_outtab                     = it_output[]
      it_fieldcatalog               = it_fcat
    EXCEPTIONS
      invalid_parameter_combination = 1
      program_error                 = 2
      too_many_lines                = 3
      OTHERS                        = 4.
  IF sy-subrc <> 0.
    MESSAGE ID sy-msgid TYPE sy-msgty NUMBER sy-msgno
               WITH sy-msgv1 sy-msgv2 sy-msgv3 sy-msgv4.
  ENDIF.

ENDMODULE.                 " ALV_GRID  OUTPUT

* PAI module of the screen created. In case we use an interactive ALV or
*for additional functionalities we can create OK codes and based on the
*user command we can do the coding as shown below

*&---------------------------------------------------------------------*
*&      Module  USER_COMMAND_0600  INPUT
*&---------------------------------------------------------------------*
*       text
*----------------------------------------------------------------------*
MODULE USER_COMMAND_0600 INPUT.

  case sy-ucomm.
    when 'LIST'.
      PERFORM LIST_DOWNLOAD.
    WHEN 'EXIT'.
      leave program.
  endcase.

ENDMODULE.                 " USER_COMMAND_0600  INPUT

*&---------------------------------------------------------------------*
*&      Form  alv_build_fieldcat
*&---------------------------------------------------------------------*
*       text
*----------------------------------------------------------------------*
*      field catalogue build
*----------------------------------------------------------------------*
FORM alv_build_fieldcat.

  DATA lv_fldcat TYPE lvc_s_fcat.

  CLEAR lv_fldcat.

  lv_fldcat-row_pos   = '1'.
  lv_fldcat-col_pos   = '1'.
  lv_fldcat-fieldname = 'FIELDNAME'.
  lv_fldcat-tabname   = 'IT_OUTPUT'.
  lv_fldcat-outputlen = 15.
  lv_fldcat-scrtext_m = 'Fieldname'.
  lv_fldcat-icon = 'X'.
  APPEND lv_fldcat TO it_fcat.

  CLEAR lv_fldcat.

  lv_fldcat-row_pos   = '1'.
  lv_fldcat-col_pos   = '2'.
  lv_fldcat-fieldname = 'INTTYPE'.
  lv_fldcat-tabname   = 'IT_OUTPUT'.
  lv_fldcat-outputlen = 5.
  lv_fldcat-scrtext_m = 'Datatype'.
  lv_fldcat-icon = ''.
  APPEND lv_fldcat TO it_fcat.

  CLEAR lv_fldcat.

  lv_fldcat-row_pos   = '1'.
  lv_fldcat-col_pos   = '3'.
  lv_fldcat-fieldname = 'LENG'.
  lv_fldcat-tabname   = 'IT_OUTPUT'.
  lv_fldcat-outputlen = 5.
  lv_fldcat-scrtext_m = 'Length'.
  lv_fldcat-icon = ''.
  APPEND lv_fldcat TO it_fcat.

  CLEAR lv_fldcat.

  lv_fldcat-row_pos   = '1'.
  lv_fldcat-col_pos   = '4'.
  lv_fldcat-fieldname = 'FIELDTEXT'.
  lv_fldcat-tabname   = 'IT_OUTPUT'.
  lv_fldcat-outputlen = 60.
  lv_fldcat-scrtext_m = 'Description'.
  lv_fldcat-icon = ''.
  APPEND lv_fldcat TO it_fcat.

  CLEAR lv_fldcat.

  lv_fldcat-row_pos   = '1'.
  lv_fldcat-col_pos   = '6'.
  lv_fldcat-fieldname = 'MEMORYID'.
  lv_fldcat-tabname   = 'IT_OUTPUT'.
  lv_fldcat-outputlen = 5.
  lv_fldcat-scrtext_m = 'ParameterID'.
  lv_fldcat-icon = ''.
  APPEND lv_fldcat TO it_fcat.

  CLEAR lv_fldcat.

  lv_fldcat-row_pos   = '1'.
  lv_fldcat-col_pos   = '7'.
  lv_fldcat-fieldname = 'CHECKTABLE'.
  lv_fldcat-tabname   = 'IT_OUTPUT'.
  lv_fldcat-outputlen = 8.
  lv_fldcat-scrtext_m = 'Checktable'.
  lv_fldcat-icon = ''.
  APPEND lv_fldcat TO it_fcat.

  CLEAR lv_fldcat.

  lv_fldcat-row_pos   = '1'.
  lv_fldcat-col_pos   = '8'.
  lv_fldcat-fieldname = 'DOMNAME'.
  lv_fldcat-tabname   = 'IT_OUTPUT'.
  lv_fldcat-outputlen = 8.
  lv_fldcat-scrtext_m = 'Domain'.
  lv_fldcat-icon = ''.
  APPEND lv_fldcat TO it_fcat.

  CLEAR lv_fldcat.

  lv_fldcat-row_pos   = '1'.
  lv_fldcat-col_pos   = '9'.
  lv_fldcat-fieldname = 'ROLLNAME'.
  lv_fldcat-tabname   = 'IT_OUTPUT'.
  lv_fldcat-outputlen = 8.
  lv_fldcat-scrtext_m = 'DataElement'.
  lv_fldcat-icon = ''.
  APPEND lv_fldcat TO it_fcat.

  CLEAR lv_fldcat.

  lv_fldcat-row_pos   = '1'.
  lv_fldcat-col_pos   = '10'.
  lv_fldcat-fieldname = 'CONVEXIT'.
  lv_fldcat-tabname   = 'IT_OUTPUT'.
  lv_fldcat-outputlen = 8.
  lv_fldcat-scrtext_m = 'Conversion Routine'.
  lv_fldcat-icon = ''.
  APPEND lv_fldcat TO it_fcat.

ENDFORM.                    " alv_build_fieldcat
*&---------------------------------------------------------------------*
*&      Form  alv_report_layout
*&---------------------------------------------------------------------*
*       text
*----------------------------------------------------------------------*
*      Report layout
*----------------------------------------------------------------------*
FORM alv_report_layout.
  it_layout-cwidth_opt = 'X'.
  it_layout-zebra = 'X'.
  it_layout-info_fname  = 'COLOR_LINE'.
ENDFORM.                    " alv_report_layout
*&---------------------------------------------------------------------*
*&      Form  LIST_DOWNLOAD
*&---------------------------------------------------------------------*
*       text
*----------------------------------------------------------------------*
*  -->  p1        text
*  <--  p2        text
*----------------------------------------------------------------------*
FORM LIST_DOWNLOAD .

*Subroutine to create a dynamic internal table
  PERFORM CREATE_TABLE.
*Subroutine to download the values of table into excel
  PERFORM DOWNLOAD_TABLE.

endform.                    "LIST_DOWNLOAD
*---------------------------------------------------------------------*
*       FORM DOWNLOAD_TABLE                                           *
*---------------------------------------------------------------------*
*       Downloads the table data.
*---------------------------------------------------------------------*
FORM DOWNLOAD_TABLE.

*Assignment of the file to a string for gui download
  p_string = p_file.

*select to fetch the table data
  SELECT *
  INTO TABLE <DYN_TAB>
  FROM (p_table).

  ASSIGN <DYN_TAB> TO <dyn_table>.

*download values as a DAT format with the fieldnames as headings
  CALL FUNCTION 'GUI_DOWNLOAD'
    EXPORTING
*   BIN_FILESIZE                    =
      FILENAME                        =  p_string
      FILETYPE                        = 'DAT'
    TABLES
      DATA_TAB                        = <dyn_table>
     FIELDNAMES                      = it_header
   EXCEPTIONS
     FILE_WRITE_ERROR                = 1
     NO_BATCH                        = 2
     GUI_REFUSE_FILETRANSFER         = 3
     INVALID_TYPE                    = 4
     NO_AUTHORITY                    = 5
     UNKNOWN_ERROR                   = 6
     HEADER_NOT_ALLOWED              = 7
     SEPARATOR_NOT_ALLOWED           = 8
     FILESIZE_NOT_ALLOWED            = 9
     HEADER_TOO_LONG                 = 10
     DP_ERROR_CREATE                 = 11
     DP_ERROR_SEND                   = 12
     DP_ERROR_WRITE                  = 13
     UNKNOWN_DP_ERROR                = 14
     ACCESS_DENIED                   = 15
     DP_OUT_OF_MEMORY                = 16
     DISK_FULL                       = 17
     DP_TIMEOUT                      = 18
     FILE_NOT_FOUND                  = 19
     DATAPROVIDER_EXCEPTION          = 20
     CONTROL_FLUSH_ERROR             = 21
     OTHERS                          = 22
            .

  IF SY-SUBRC EQ 0.
*POP up to inform is download is successful or not
    CALL FUNCTION 'POPUP_TO_INFORM'
      EXPORTING
        TITEL = 'Success!'
        TXT1  = p_table
        TXT2  = 'was correctly downloaded.'.
  ELSE.
    CALL FUNCTION 'POPUP_TO_INFORM'
      EXPORTING
        TITEL = 'Error!'
        TXT1  = p_table
        TXT2  = 'NOT downloaded.'.
  ENDIF.

ENDFORM.                    "DOWNLOAD_TABLE

*---------------------------------------------------------------------*
*       FORM CREATE_TABLE                                             *
*---------------------------------------------------------------------*
*       Creates a dynamic internal table.
*---------------------------------------------------------------------*
FORM CREATE_TABLE.
  CREATE DATA DATAREFERENCE TYPE (P_TABLE).

  ASSIGN DATAREFERENCE->* TO <my_fs>.

  struct_ref ?= CL_ABAP_TYPEDESCR=>DESCRIBE_BY_DATA( <my_fs> ).

  LOOP AT struct_ref->components ASSIGNING <DESR_COMP>.
    wa_fieldcat-fieldname     = <DESR_COMP>-NAME.
    wa_fieldcat-ref_table     = P_TABLE.
    wa_fieldcat-ref_field     = <DESR_COMP>-NAME.
    wa_header-fieldname       = <desr_comp>-name.
    APPEND wa_fieldcat TO it_fieldcat.
    append wa_header to it_header.
  ENDLOOP.

  CALL METHOD CL_ALV_TABLE_CREATE=>CREATE_DYNAMIC_TABLE
    EXPORTING
      IT_FIELDCATALOG           = IT_FIELDCAT
    IMPORTING
      EP_TABLE                  = DATAREFERENCE
    EXCEPTIONS
      GENERATE_SUBPOOL_DIR_FULL = 1
      OTHERS                    = 2.

  ASSIGN DATAREFERENCE->* TO <DYN_TAB>.
ENDFORM.                    "CREATE_TABLE

Click here to continue..