RRW3_GET_QUERY_VIEW_DATA – Execution of BEx Query in ABAP

Following program is an example of use of the function RRW3_GET_QUERY_VIEW_DATA

&———————————————————————*
*& Report  zbw_bpc_capex_follow_up_export.
*&
*&———————————————————————*
*&
*&
*&———————————————————————*
REPORT  zbw_bpc_capex_follow_up_export.
” Objective = Generate a Data File for import in BPC CAPEX.
” Source query = ZPS00M0006_Q0003 which is a copy of the query
” BW CAPEX Follow Up ZPS00M0006_Q0002 as it exists in sept 2010
” differences btw the 2 queries: No result displayed. No Descriptions.
” Project / WBS_Plant / Project Size / Project Category (Ongoing, CUrrent) in rows.
” File will contain 1 KF per line with the following mapping between KF col and BPC ACCOUNT member
” Col 1 = FINAL_EST
” Col 2 = BU_APP
” COl 3 = PO_GR
” Col 4 = PO_N_GR
” Col 5 = INT_COSTS_COM and INT_COSTS_CA (this KF is included twice in the data file)
” Col 6  is ignored
” Col 7 = INVOICED
” Col 8 = DIRECT_PAY
” Col 9 = DOWN_PAY
” Col 10 –  is ignored
” The other project dimensions are set as follows
” TIME set to AAAAMM of SY-DATUM
” PRJ_SIZE, PLANT, PRJ_STATUS set to the values of the row characteristics of the BW Query
” CATEGORY set to ACTUAL
” INTCO set to NON_INTERCO
” DATA_SOURCE set to IMPORT
” Selection screen
” Fiscal persiod lower range can be selected. Default value is 001/2000
” Fiscal persiod upper range set to current fiscal period
” Load to BPC will run the BPC Data Management Package to load data into Application
TYPES: BEGIN OF ty_lineout,
         project     TYPE char20,
         entity      TYPE char20,
         prj_size    TYPE char20,
         prj_status  TYPE char20,
         account     TYPE char20,
         category    TYPE char20,
         intco       TYPE char20,
         data_source TYPE char20,
         rpt_currency        TYPE char20,
         time        TYPE char20,
         amount      TYPE char20,
       END OF ty_lineout.
DATA: lt_fileout TYPE STANDARD TABLE OF ty_lineout,
      la_lineout TYPE ty_lineout,
      la_line     TYPE string.
DATA: l_fileout_name TYPE string.
DATA: it_parameters TYPE rrxw3tquery                WITH HEADER LINE.
DATA: it_cell       TYPE rrws_t_cell                WITH HEADER LINE.
DATA: it_axis       TYPE rrws_thx_axis_data         WITH HEADER LINE.
*DATA: it_plant      LIKE TABLE OF /bi0/pplant-plant WITH HEADER LINE.
*DATA: it_tabout     TYPE TABLE OF ty_char92         WITH HEADER LINE.
DATA: l_plant       TYPE /bi0/oiplant.
DATA: l_coarea      TYPE /bi0/oico_area.
DATA: l_proj        TYPE /bi0/oiproject.
DATA: l_cntry       TYPE /bi0/oicountry.
DATA: l_subrc       LIKE sy-subrc.
DATA: l_count       LIKE sy-dbcnt.
DATA: l_length      TYPE i.
DATA: l_nblines     TYPE i.
DATA: l_buffer(10).
*DATA: l_old_file    LIKE dynpread-fieldvalue.
DATA: ls_cell       TYPE rrws_sx_tuple.
DATA: l_col TYPE rrtcellordinal.
DATA: l_lin TYPE rrtcellordinal.
DATA: l_project TYPE char20.
DATA l_fphig TYPE /bi0/oifiscper.
DATA: l_timeout TYPE char20.
DATA: l_year TYPE char4.
FIELD-SYMBOLS: <fs_lineout> TYPE ty_lineout.
SELECTION-SCREEN: skip.
PARAMETERS p_lfil1  TYPE fileintern DEFAULT ‘Z00COPS03’.
PARAMETERS p_skip0 TYPE char1 AS CHECKBOX DEFAULT ‘X’.
PARAMETERS p_2BPC type c AS CHECKBOX DEFAULT ‘X’.
SELECTION-SCREEN: skip.
PARAMETERS p_timbu TYPE c RADIOBUTTON GROUP time.
PARAMETERS p_tim03 TYPE c RADIOBUTTON GROUP time.
PARAMETERS p_tim06 TYPE c RADIOBUTTON GROUP time.
PARAMETERS p_tim09 TYPE c RADIOBUTTON GROUP time.
PARAMETERS p_tim12a TYPE c RADIOBUTTON GROUP time.
PARAMETERS p_tim12b TYPE c RADIOBUTTON GROUP time.
SELECTION-SCREEN: skip.
*PARAMETERS p_budyr TYPE /bic/oizpsyear DEFAULT sy-datum(4) OBLIGATORY.
PARAMETERS p_fplow TYPE /bi0/oifiscper OBLIGATORY.
*PARAMETERS: pa_file LIKE rsldpsel-filename OBLIGATORY.
SELECT-OPTIONS: so_carea FOR l_coarea .
SELECT-OPTIONS: so_plant FOR l_plant .
SELECT-OPTIONS: so_proj FOR l_proj .
*SELECT-OPTIONS: so_cntry FOR l_cntry .
PARAMETERS p_acct1 TYPE char20 DEFAULT ‘FINAL_EST’.
PARAMETERS p_acct2 TYPE char20 DEFAULT ‘BU_APP’.
PARAMETERS p_acct3 TYPE char20 DEFAULT ‘PO_N_GR’.
PARAMETERS p_acct4 TYPE char20 DEFAULT ‘PO_GR’.
PARAMETERS p_acct5a TYPE char20 DEFAULT ‘INT_COSTS_COM’.
PARAMETERS p_acct5b TYPE char20 DEFAULT ‘INT_COSTS_CA’.
PARAMETERS p_acct7 TYPE char20 DEFAULT ‘INVOICED’.
PARAMETERS p_acct8 TYPE char20 DEFAULT ‘DIRECT_PAY’.
PARAMETERS p_acct9 TYPE char20 DEFAULT ‘DOWN_PAY’.
l_year = sy-datum(4).
IF p_timbu IS NOT INITIAL.
  l_year = l_year + 1.
  CONCATENATE l_year ‘.JAN’ INTO l_timeout.
  l_year = l_year – 1.  ” Budget Year for the query is current year.
  CONCATENATE l_year ‘009’ INTO l_fphig. ” Budget run in october, seletion till end of sept
ENDIF.
IF p_tim03 IS NOT INITIAL.
  CONCATENATE l_year ‘003’ INTO l_fphig.
  CONCATENATE l_year ‘.MAR’ INTO l_timeout.
ENDIF.
IF p_tim06 IS NOT INITIAL.
  CONCATENATE l_year ‘006’ INTO l_fphig.
  CONCATENATE l_year ‘.JUN’ INTO l_timeout.
ENDIF.
IF p_tim09 IS NOT INITIAL.
  CONCATENATE l_year ‘009’ INTO l_fphig.
  CONCATENATE l_year ‘.SEP’ INTO l_timeout.
ENDIF.
IF p_tim12a IS NOT INITIAL.
  CONCATENATE l_year ‘012’ INTO l_fphig.
  CONCATENATE l_year ‘.DEC’ INTO l_timeout.
ENDIF.
IF p_tim12b IS NOT INITIAL.
  l_year = l_year – 1.
  CONCATENATE l_year ‘012’ INTO l_fphig.
  CONCATENATE l_year ‘.DEC’ INTO l_timeout.
ENDIF.
START-OF-SELECTION.
  CONCATENATE sy-datum(4) ‘0’ sy-datum+4(2) INTO l_fphig.
  REFRESH: it_parameters.
  it_parameters-name  = ‘VAR_NAME_1’.
  it_parameters-value = ‘0I_FPER’.
  APPEND it_parameters.
  it_parameters-name  = ‘VAR_VALUE_LOW_EXT_1’.
  it_parameters-value = p_fplow.
  APPEND it_parameters.
  it_parameters-name  = ‘VAR_VALUE_HIGH_EXT_1’.
  it_parameters-value = l_fphig.
  APPEND it_parameters.
  it_parameters-name  = ‘VAR_NAME_2’.
  it_parameters-value = ‘ZVPSYEAR’.
  APPEND it_parameters.
  it_parameters-name  = ‘VAR_VALUE_EXT_2’.
  it_parameters-value = l_year.
  APPEND it_parameters.
*
  DATA l_index TYPE char1.
  l_index = ‘3’.
  LOOP AT so_carea.
    CONCATENATE ‘VAR_NAME_’ l_index INTO it_parameters-name.
*    it_parameters-name  = ‘VAR_NAME_4’.
    it_parameters-value = ‘ZSOCONTR’.
    APPEND it_parameters.
    CONCATENATE ‘VAR_SIGN_’ l_index INTO it_parameters-name.
*        it_parameters-name  = ‘VAR_SIGN_4’.
    it_parameters-value = so_carea-sign.
    APPEND it_parameters.
    CONCATENATE ‘VAR_OPERATOR_’ l_index INTO it_parameters-name.
*    it_parameters-name  = ‘VAR_OPERATOR_4’.
    it_parameters-value = so_carea-option.
    APPEND it_parameters.
    CONCATENATE ‘VAR_VALUE_LOW_EXT_’ l_index INTO it_parameters-name.
*    it_parameters-name  = ‘VAR_VALUE_LOW_EXT_4’.
    it_parameters-value = so_carea-low.
    APPEND it_parameters.
    CONCATENATE ‘VAR_VALUE_HIGH_EXT_’ l_index INTO it_parameters-name.
*    it_parameters-name  = ‘VAR_VALUE_HIGH_EXT_4’.
    it_parameters-value = so_carea-high.
    APPEND it_parameters.
    l_index = l_index + 1.
  ENDLOOP.
  LOOP AT so_plant.
    CONCATENATE ‘VAR_NAME_’ l_index INTO it_parameters-name.
*    it_parameters-name  = ‘VAR_NAME_4’.
    it_parameters-value = ‘0S_PLANT’.
    APPEND it_parameters.
    CONCATENATE ‘VAR_SIGN_’ l_index INTO it_parameters-name.
*        it_parameters-name  = ‘VAR_SIGN_4’.
    it_parameters-value = so_plant-sign.
    APPEND it_parameters.
    CONCATENATE ‘VAR_OPERATOR_’ l_index INTO it_parameters-name.
*    it_parameters-name  = ‘VAR_OPERATOR_4’.
    it_parameters-value = so_plant-option.
    APPEND it_parameters.
    CONCATENATE ‘VAR_VALUE_LOW_EXT_’ l_index INTO it_parameters-name.
*    it_parameters-name  = ‘VAR_VALUE_LOW_EXT_4’.
    it_parameters-value = so_plant-low.
    APPEND it_parameters.
    CONCATENATE ‘VAR_VALUE_HIGH_EXT_’ l_index INTO it_parameters-name.
*    it_parameters-name  = ‘VAR_VALUE_HIGH_EXT_4’.
    it_parameters-value = so_plant-high.
    APPEND it_parameters.
    l_index = l_index + 1.
  ENDLOOP.
  LOOP AT so_proj.
    CONCATENATE ‘VAR_NAME_’ l_index INTO it_parameters-name.
*    it_parameters-name  = ‘VAR_NAME_4’.
    it_parameters-value = ‘0S_PRJ’.
    APPEND it_parameters.
    CONCATENATE ‘VAR_SIGN_’ l_index INTO it_parameters-name.
*        it_parameters-name  = ‘VAR_SIGN_4’.
    it_parameters-value = so_proj-sign.
    APPEND it_parameters.
    CONCATENATE ‘VAR_OPERATOR_’ l_index INTO it_parameters-name.
*    it_parameters-name  = ‘VAR_OPERATOR_4’.
    it_parameters-value = so_proj-option.
    APPEND it_parameters.
    CONCATENATE ‘VAR_VALUE_LOW_EXT_’ l_index INTO it_parameters-name.
*    it_parameters-name  = ‘VAR_VALUE_LOW_EXT_4’.
    it_parameters-value = so_proj-low.
    APPEND it_parameters.
    CONCATENATE ‘VAR_VALUE_HIGH_EXT_’ l_index INTO it_parameters-name.
*    it_parameters-name  = ‘VAR_VALUE_HIGH_EXT_4’.
    it_parameters-value = so_proj-high.
    APPEND it_parameters.
    l_index = l_index + 1.
  ENDLOOP.
*  LOOP AT so_cntry.
*    CONCATENATE ‘VAR_NAME_’ l_index into it_parameters-name.
**    it_parameters-name  = ‘VAR_NAME_4’.
*    it_parameters-value = ‘Z_COUNTRY’.
*    APPEND it_parameters.
*    CONCATENATE ‘VAR_SIGN_’ l_index into it_parameters-name.
**        it_parameters-name  = ‘VAR_SIGN_4’.
*    it_parameters-value = so_cntry-sign.
*    APPEND it_parameters.
*    CONCATENATE ‘VAR_OPERATOR_’ l_index into it_parameters-name.
**    it_parameters-name  = ‘VAR_OPERATOR_4’.
*    it_parameters-value = so_cntry-option.
*    APPEND it_parameters.
*    CONCATENATE ‘VAR_VALUE_LOW_EXT_’ l_index into it_parameters-name.
**    it_parameters-name  = ‘VAR_VALUE_LOW_EXT_4’.
*    it_parameters-value = so_cntry-low.
*    APPEND it_parameters.
*    CONCATENATE ‘VAR_VALUE_HIGH_EXT_’ l_index into it_parameters-name.
**    it_parameters-name  = ‘VAR_VALUE_HIGH_EXT_4’.
*    it_parameters-value = so_cntry-high.
*    APPEND it_parameters.
*    l_index = l_index + 1.
*  ENDLOOP.
** Calls the query and retrieve the result in a table
  CALL FUNCTION ‘RRW3_GET_QUERY_VIEW_DATA’
   EXPORTING
     i_infoprovider                = ‘PS00M0006’
     i_query                       = ‘ZPS00M0006_Q0003’
*      I_VIEW_ID                     = ”
     i_t_parameter                 = it_parameters[]
   IMPORTING
*      e_axis_info                   = it_axis_info[]
     e_cell_data                   = it_cell[]
     e_axis_data                   = it_axis[]
*      e_txt_symbols                 = it_texts[]
   EXCEPTIONS
     no_applicable_data            = 1
     invalid_variable_values       = 2
     no_authority                  = 3
     abort                         = 4
     invalid_input                 = 5
     invalid_view                  = 6
     OTHERS                        = 7.
  IF sy-subrc = 0.
    la_lineout-intco = ‘NON_INTERCO’.
    la_lineout-data_source = ‘IMPORT’.
    la_lineout-category = ‘ACTUAL’.
    la_lineout-rpt_currency = ‘LC’.
    la_lineout-time = l_timeout.
    l_col = 0.
    l_lin = 0.
    LOOP AT it_cell.
      l_col = l_col + 1.
      IF l_col = 10.
        l_col = 1.
        l_lin = l_lin + 1.
      ENDIF.
      IF l_col = 6 OR
        l_col = 9.
        CONTINUE.
      ENDIF.
      IF ( it_cell-value IS INITIAL OR
         it_cell-value EQ ‘0.00’ ) AND
        p_skip0 EQ ‘X’.
        CONTINUE.
      ENDIF.
      CASE l_col.
        WHEN 1.
          la_lineout-account = p_acct1.
        WHEN 2.
          la_lineout-account = p_acct2.
        WHEN 3.
          la_lineout-account = p_acct3.
        WHEN 4.
          la_lineout-account = p_acct4.
        WHEN 5.
          la_lineout-account = p_acct5a. ” Before append test if col =5 to add a 2nd line with
                                                            ” p_acct5b
        WHEN 7.
          la_lineout-account = p_acct7.
        WHEN 8.
          la_lineout-account = p_acct8.
        WHEN 9.
          la_lineout-account = p_acct9.
      ENDCASE.
      READ TABLE it_axis WITH KEY axis = 1.
      IF sy-subrc <> 0.
        EXIT.
      ENDIF.
      READ TABLE it_axis-set
        INTO ls_cell
        WITH KEY tuple_ordinal = l_lin
                 chanm         = ‘0PROJECT’.
      IF sy-subrc <> 0.
        EXIT.
      ENDIF.
      la_lineout-project = ls_cell-chavl_ext.
      READ TABLE it_axis-set
          INTO ls_cell
          WITH KEY tuple_ordinal = l_lin
                   chanm         = ‘0WBS_ELEMT__0PLANT’.
      IF sy-subrc <> 0 .
        EXIT.
      ENDIF.
      la_lineout-entity = ls_cell-chavl.
      READ TABLE it_axis-set
          INTO ls_cell
          WITH KEY tuple_ordinal = l_lin
                   chanm         = ‘0WBS_ELEMT__0PRIORITY’.
      IF sy-subrc <> 0 .
        EXIT.
      ENDIF.
      la_lineout-prj_size = ls_cell-chavl.
      READ TABLE it_axis-set
          INTO ls_cell
          WITH KEY tuple_ordinal = l_lin
                   chanm         = ‘ZPSCATPRJ’.
      IF sy-subrc <> 0 .
        EXIT.
      ENDIF.
      la_lineout-prj_status = ls_cell-chavl.
      ” For BUdget Cycle, all PRJ_STATUS are set to ‘O’, (Ongoing)
      IF p_timbu IS NOT INITIAL.
        la_lineout-prj_status = ‘O’.
      ENDIF.
      IF it_cell-value IS NOT INITIAL.
        la_lineout-amount =  it_cell-value .
      ELSE.
        la_lineout-amount = ‘0.00’.
      ENDIF.
      APPEND la_lineout TO lt_fileout.
      IF l_col = 5.
        la_lineout-account = p_acct5b.
        APPEND la_lineout TO lt_fileout.
      ENDIF.
    ENDLOOP.
    LOOP AT lt_fileout ASSIGNING <fs_lineout>.
      l_project = <fs_lineout>-project.
      CONCATENATE
      l_project(2)
      l_project+3(2)
      l_project+6(2)
      l_project+9(2)
      l_project+12(4)
      ‘000’
      INTO l_project.
      SELECT SINGLE wbs_elm_ex FROM /bi0/mwbs_elemt
         INTO <fs_lineout>-project
         WHERE objvers = ‘A’
         AND project = l_project
         AND ps_level = ‘1’.
    ENDLOOP.
    CALL FUNCTION ‘FILE_GET_NAME’
      EXPORTING
        logical_filename = p_lfil1
      IMPORTING
        file_name        = l_fileout_name.
    OPEN DATASET l_fileout_name FOR OUTPUT IN TEXT MODE ENCODING DEFAULT.
    la_line = ‘PROJECT;ENTITY;PRJ_SIZE;PRJ_STATUS;ACCOUNT;CATEGORY;INTCO;DATA_SOURCE;RPT_CURRENCY;TIME;AMOUNT’.
    TRANSFER la_line TO l_fileout_name.
    LOOP AT lt_fileout INTO la_lineout.
      CONCATENATE
        la_lineout-project
        la_lineout-entity
        la_lineout-prj_size
        la_lineout-prj_status
        la_lineout-account
        la_lineout-category
        la_lineout-intco
        la_lineout-data_source
        la_lineout-rpt_currency
        la_lineout-time
        la_lineout-amount
        INTO la_line
        SEPARATED BY ‘;’.
      TRANSFER la_line TO l_fileout_name.
    ENDLOOP.
    CLOSE DATASET l_fileout_name .
    IF p_2bpc = ‘X’.
      SUBMIT zbw_bpc_run_package USING SELECTION-SET ‘WBSTD2BPC’.
    ENDIF.
  ENDIF.

Leave a Reply