ZBW_EXTRACTOR_TABLE_ANALYZER.abap

*&---------------------------------------------------------------------*
*& Report  ZBW_EXTRACTOR_TABLE_ANALYZER
*&
*&---------------------------------------------------------------------*
*&  Extractor & table analyzer
*&  Author: Joury Jonkergouw
*&  Created on:   19.08.2014
*&  Last changed: 25.10.2014
*&---------------------------------------------------------------------*
*&  12.09.2014; Sven van Leuken; Solved Shortdump with Call Function call
*&---------------------------------------------------------------------*
*&
*& Program to analyze contents of a SAP table or datasource.
*& It analyzes all fields part of a table and datasource and comes
*& with an analysis of which fields should be added when creating a
*& new datasource or enhancing a existing datasource
*&
*& Note: It is not possible to analyze datasource who have structures
*& which are based on structures or are technical extraction structures
*& for e.g. 2LIS_11_VAKON or 2LIS_11_V_SSL
*&
*& Caution: Running this program can take very long on big databases!
*&          Do not execute in productive systems
*&
*& Parameters:
*&              SAP Table: ECC table name (e.g. VBAK)
*&              Datasource: Datasource name (e.g. 2LIS_11_VAHDR)
*&              Output to Excel: X or blank
*&              Output location: Complete path of output file
*&
*&---------------------------------------------------------------------*
report  zbw_extractor_table_analyzer.

type-pools: abap.

selection-screen begin of block box1 with frame title text-001.
parameter: pm_table type ddobjname obligatory,
*           pm_datas TYPE roosource-oltpsource,
           pm_excel type c default 'X',
           pm_file  type rlgrap-filename default 'P:\temp\'.
selection-screen end of block box1.

types: begin of ty_results_h,
         line(50) type c,
       end of ty_results_h,
       begin of ty_results,
        table       type c length 40,
        field       type c length 40,
        element     type c length 40,
        datatype    type c length 40,
        length      type c length 6,
        decimals    type c length 8,
        description type c length 60,
        total       type c length 40,
        unique      type c length 40,
        percentage  type c length 40,
        used        type c length 4,
        structure   type c length 42,
        active      type c length 48,
        append      type c length 6,
        add         type c length 3,
      end of ty_results,
      begin of ty_tmcexcfs,
        cstruc(30) type c,
        fieldname  type fieldname,
        estruc  type tabname,
        state(1)  type c,
        mcapp(2)  type c,
      end of ty_tmcexcfs.

data : lv_table      type ddobjname,
       lt_table      type table of dfies,
       wa_table      like line of lt_table,
       lt_excfs      type table of ty_tmcexcfs,
       wa_excfs      like line of lt_excfs,
       lt_extab      type table of ty_tmcexcfs,
       lt_excfz      type table of ty_tmcexcfs,
       lv_estruc(30) type c,
       lv_cstruc(30) type c,
       lv_extra      type roosource-extractor,
       lv_exmeth     type roosource-exmethod,
       lt_dd03m      type standard table of dd03m,
       wa_dd03m      like line of lt_dd03m,
       lt_results_h  type standard table of ty_results_h,
       wa_results_h  like line of lt_results_h,
       lt_results    type standard table of ty_results,   "Output type excel with all columns
       wa_result     like line of lt_results,
       lv_type       type c,
       lv_total      type i,
       lv_unique     type i,
       lv_percentage type /bic/of_dec_000004_01,
       lv_where      type c length 250,
       lv_header     type string,
       lt_split      type table of char40,
       lr_split      type ref to char40,
       tp_class      type dd02v-tabclass.

field-symbols :  type ty_tmcexcfs,
                 like line of lt_excfs,
                 type dfies.


initialization.

*  CONCATENATE 'p:\temp\' sy-uname '\Documents\temp\' INTO pm_file IN CHARACTER MODE.

start-of-selection.

* Clear all variables.
  clear: wa_excfs, lv_extra, lv_estruc, lv_exmeth, lt_extab, lv_type,
         lt_excfs, lt_excfz, lt_results, lv_header, lt_split, lt_results_h.

** Check if one of the two parameters is filled.
*  IF pm_table IS INITIAL." AND pm_datas IS NOT INITIAL.
*    MESSAGE e000(38) WITH 'Please enter a table name'.
*  ENDIF.

* If parameter table is filled then assign this value to the structure internal table.
* else get the extractor name from the table roosource.
*  IF pm_table IS NOT INITIAL.
**  Type is table (T)
  lv_type = 'T'.
  wa_excfs-cstruc = pm_table.
  append wa_excfs to lt_extab.
*  ENDIF.

* Loop over tables part of the structure
  loop at lt_extab assigning .

    clear: lv_cstruc, lt_table[], lv_total, lv_table, tp_class.

* Store original structure name
    lv_cstruc = -cstruc.

*  If two characters of the structure name is equal to MC, strip these.
    if lv_cstruc(2) eq 'MC'.
      shift lv_cstruc left by 2 places.
    endif.

* Store table name.
    lv_table = lv_cstruc.

    call function 'DDIF_FIELDINFO_GET'
      exporting
        tabname        = lv_table
      importing
        ddobjtype      = tp_class
      tables
        dfies_tab      = lt_table
      exceptions
        not_found      = 1
        internal_error = 2
        others         = 3.

*- Added 4 checks to make sure only tables (and not views/structures)
*- are being processed. Otherwise program will dump (Sven 12.09.14)
    check sy-subrc eq 0.
    check tp_class <> 'INTTAB'.
    check tp_class <> 'VIEW'.
    check tp_class <> 'APPEND'.

*   Get total amount of records
    select count( * ) into lv_total from (lv_table).

    write: / 'Table:', lv_table .
    write: / 'Number of records:', lv_total.
    write: /.

    sort lt_table by fieldname ascending.
    sort lt_excfs by fieldname ascending.

    loop at lt_table into wa_table.
      clear: lv_where, lv_unique, wa_excfs, wa_result.

      case wa_table-datatype.
        when 'CHAR'.
          concatenate wa_table-fieldname '<>' '''''' into lv_where separated by space.
        when 'DATS'.
          concatenate wa_table-fieldname '<>' '''00000000''' into lv_where separated by space.
        when 'CURR' or 'QUAN' or 'DEC' or 'FLTP' or 'INIT1' or 'INT2' or 'INT4' or 'NUMC'.
          concatenate wa_table-fieldname '<>' '''0''' into lv_where separated by space.
        when 'TIMS'.
          concatenate wa_table-fieldname '<>' '''000000''' into lv_where separated by space.
        when others.
          concatenate wa_table-fieldname '<>' '''''' into lv_where separated by space.
      endcase.

*   Count number of records of specific field
      select count( * ) into lv_unique from (lv_table) where (lv_where).

*   Has no records
      if lv_unique eq 0.
        wa_result-used = 'N'.
      else.
        wa_result-used = 'Y'.
      endif.

*   Check if field is part of structure
      read table lt_excfs into wa_excfs with key fieldname = wa_table-fieldname cstruc = -cstruc.

*   If part of structure then set variable to Y
      if sy-subrc = 0.
        wa_result-structure = 'Y'.

*     If state is active then set variable to Y
        if wa_excfs-state eq 'A'.
          wa_result-active = 'Y'.
        else.
          wa_result-active = 'N'.
        endif.
      else.
        wa_result-structure = 'N'.
        wa_result-active = 'N'.
      endif.

*   If field has records and is not added in the structure, then this should be appended
      if wa_result-used = 'Y' and wa_result-structure = 'N' and ( pm_table is initial ).
        wa_result-append = 'Y'.
      else.
        wa_result-append = 'N'.
      endif.

*   If field has records and is not added in the structure, then this should be added
      if wa_result-used = 'Y' and ( wa_result-structure = 'N' or ( wa_result-structure = 'Y' and wa_result-active = 'N' ) ) .
        wa_result-add = 'Y'.
      else.
        wa_result-add = 'N'.
      endif.

**   Write output
*      WRITE: / 'Field:', wa_table-fieldname, 'Filled records', lv_unique, 'Used:', wa_result-used, 'In structure:',
*             wa_result-structure, 'Active:', wa_result-active, 'Append:', wa_result-append, 'Add:', wa_result-add.

*   Add results to a internal table. Used for Excel outputting.
      wa_result-table       = lv_table.
      wa_result-field       = wa_table-fieldname.
      wa_result-element     = wa_table-rollname.
      wa_result-datatype    = wa_table-datatype.
      wa_result-length      = wa_table-leng.
      wa_result-decimals    = wa_table-decimals.
      wa_result-description = wa_table-fieldtext.
      wa_result-unique      = lv_unique.
      wa_result-total       = lv_total.
      if lv_total <> 0.
        lv_percentage = lv_unique / lv_total * 100.
        wa_result-percentage = lv_percentage.
      else.
        clear wa_result-percentage.
      endif.
      append wa_result to lt_results.

    endloop.

*    WRITE: /.

  endloop.

* Output to Excel
  if pm_excel eq 'X'.

    sort lt_results by field ascending.

* If standard parameter is a table then exclude the structure variables from write statement
    lv_header = 'Table,Field,Element,Data type,Length,Decimals,Description,Total records,Filled records,Percentage,Used,In structure,Active,Append,Add'.

    split lv_header at ',' into table lt_split.

    loop at lt_split reference into lr_split.
      clear: wa_results_h.
      wa_results_h-line = lr_split->*.
      append wa_results_h to lt_results_h.
    endloop.

    concatenate pm_file pm_table '_analysis.xls' into pm_file in character mode.

    call function 'MS_EXCEL_OLE_STANDARD_DAT'
      exporting
        file_name                 = pm_file
      tables
        data_tab                  = lt_results
        fieldnames                = lt_results_h
      exceptions
        file_not_exist            = 1
        filename_expected         = 2
        communication_error       = 3
        ole_object_method_error   = 4
        ole_object_property_error = 5
        invalid_pivot_fields      = 6
        download_problem          = 7
        others                    = 8.
    if sy-subrc <> 0.
      write: / 'Something went wrong when saving the Excel file'.
    endif.

  endif.

end-of-selection.