Merge Header and Item files generated by Open Hub Destinations

[Former Member]

Applies to: SAP ECC 6.0, SAP BI 7.3.

Author: Komal Chandak

Company: Accenture Services Pvt. Ltd.

Created on: 18th October 2013

Author Bio: Komal is a Software Engineer Analyst in Accenture Services Private Limited. She has been involved in SAP ABAP and BW Implementation Projects.

Business Scenario:

The open hub destination is the object that allows you to distribute data from a BI system to non-SAP data marts, analytic applications, and other applications. The open hub destination defines the target to which the data is transferred in the form of files. Open hub generates two different files, one for header fields and other is of items detail. Business requirement is to get header and items detail together in one flat file for better analysis and time utilization. It is not possible to change open hub to generate one file instead of two. So what we followed the approach of merging header and items data in one file on application server using program.

Overview:

The open hub destination contains all the information about a data target: the type of destination, the name of the flat file or database table and its properties, and the field list and its properties. This document explains the merging of header data and item details which are present in two different files on application server in one flat file. On downloading the merged file, it can be used to see both header and item details of the open hub destination.

Configuring Logical File Path in BI

Login BI system and go to transaction ‘FILE’.

Create logical path and assign physical path where target file from open hub cab be generated.

Provide the logical file name for created logical path.

File path setting in Open Hub to generate files on application server

Open the created open hub destination and in destination tab, select logical file name in type of file name. In App.server file name select the name of logical path that created in earlier step.

This open hub generates file at file path and file name provided in logical path created using FILE T-Code on application server.

Creation of program to merge the header and item files generated by open hub:

To create a generic program to merge header data and item details in one file, we need to login into BI system and follow the below mentioned steps:

1. Go to SE38 T-code to create a custom program.

2.  In this, specify a Program name and the SubObjects as Source Code and click on create.

3.  Enter the title for the program and within the attributes section, select the Attribute type as Executable program and status as SAP Standard             Production Program.  You can also specify the package. Press Enter.

4. The ABAP Editor opens up which allows us to write a program.

5.  The following code explains the declarations and selection screen.

Selection screen will display parameter to enter logical path of the open hub.

*&---------------------------------------------------------------------*
*& Report <Program Name>
*&---------------------------------------------------------------------*
*& Program to merge the header and item files in one file generated by
*& open hub destination
*&---------------------------------------------------------------------*

REPORT <program name>.

INCLUDE <Include_top>.  “for declaration

INCLUDE <Include_sub>. “For all the performs and logic

START-OF-SELECTION.

*get the file name from logical path name
PERFORM f_get_filename USING p_path.
*Get the directory and file name
PERFORM f_directory_name.
*Read directory to get the list of files
PERFORM f_get_directory_files_list USING gv_dirname.
*Delete files othere than open hub from directory list
perform f_filter_dirlist.
*Open & read the source file and get the data in internal table
PERFORM f_read_source_file.
*Change the source file internal table
PERFORM f_process_sourcefile.
*Open & Read the target file in internal table
PERFORM f_process_targetfile.
*write openhub final file on app server
PERFORM f_write_final_file.

END-OF-SELECTION.

*&---------------------------------------------------------------------*
*&  Include           Include_TOP
*&---------------------------------------------------------------------*

DATA: BEGIN OF gt_file OCCURS 1,
line(5000),
END OF gt_file,

BEGIN OF gt_file2 OCCURS 1,
line(5000),
END OF gt_file2.

DATA:   gv_length     TYPE p,
gv_len        TYPE i,
gw_file       LIKE LINE OF gt_file,
gw_file2      LIKE LINE OF gt_file2,
gw_source     LIKE LINE OF gt_file.

DATA: gt_dirlist  TYPE TABLE OF eps2fili,
gt_dirlist1 TYPE TABLE OF eps2fili,
gw_dirlist  TYPE eps2fili,
gw_dirlist1 TYPE eps2fili.

DATA: gv_physical_filename TYPE rlgrap-filename,
gv_sourcefile         TYPE rlgrap-filename,
gv_string1            TYPE string,
gv_error              TYPE string,
gv_dirname            TYPE eps2filnam,
gv_filename           TYPE string,
gv_filename1          TYPE string,
gv_tabix              TYPE sy-tabix,
gv_result             TYPE match_result,
gv_exception          TYPE REF TO cx_root.

CONSTANTS: k_message TYPE c VALUE 'I',
k_error   tYPE c VALUE 'E'.

FIELD-SYMBOLS: <f1>.

*---------------------------------------------------------------------*
* Selection Screen
*---------------------------------------------------------------------*

*Text-002 – Enter the open hub logical path name
SELECTION-SCREEN BEGIN OF BLOCK b1 WITH FRAME TITLE text-002.
PARAMETERS: p_path TYPE fileintern.      “Logical Path of the open hub
SELECTION-SCREEN END OF BLOCK b1.

1. 6.       The below code retrieve the file name from the logical path given on the selection screen.

Then using that file name, it will retrieve directory name and all the files present in the directory. Non relevant files will get deleted. Header files will be present in one table and item files will present in another.

Using header file table, most recent header file data of particular open hub will be collected in one internal table and similarly, most recent item file data in another internal table.

Then item file will be overwritten by header and item data.

*&---------------------------------------------------------------------*
*&  Include           Include_SUB
*&---------------------------------------------------------------------*

*&---------------------------------------------------------------------*
*&      Form F_GET_FILENAME
*&---------------------------------------------------------------------*
FORM f_get_filename USING p_logical_filename TYPE fileintern.

CALL FUNCTION 'FILE_GET_NAME'
EXPORTING
logical_filename        = p_logical_filename
use_presentation_server = 'X'
IMPORTING
file_name               = gv_physical_filename
EXCEPTIONS
file_not_found          = 1
OTHERS                  = 2.
IF sy-subrc <> 0.
MESSAGE text-002 TYPE k_message DISPLAY LIKE k_error. "folder path is not maintained
LEAVE LIST-PROCESSING.
ENDIF.

ENDFORM. " F_GET_FILENAME

*&---------------------------------------------------------------------*
*&      Form f_directory_name
*&---------------------------------------------------------------------*
FORM f_directory_name.

DATA: lv_sourcefile TYPE rlgrap-filename,
lv_dir_off    TYPE i,
lv_dirname    TYPE rlgrap-filename,
lv_filename   TYPE rlgrap-filename,
lv_len        TYPE i.

IF gv_physical_filename IS NOT INITIAL.
lv_sourcefile = gv_physical_filename.
PERFORM f_reverse_string USING lv_sourcefile.
IF sy-subrc EQ 0.
FIND FIRST OCCURRENCE OF '/' IN lv_sourcefile RESULTS gv_result.
*Get the filename to search in directory
lv_filename = lv_sourcefile(gv_result-offset).
lv_len = strlen( lv_filename ).
gv_len = lv_len - 20.
PERFORM f_reverse_string USING lv_filename.
gv_filename = lv_filename(gv_len).
CONCATENATE 'S_' gv_filename INTO gv_filename1.
CLEAR lv_len.
*Get the directory name
lv_len = strlen( lv_sourcefile ).
lv_dir_off = lv_len - gv_result-offset.
PERFORM f_reverse_string USING lv_sourcefile.
lv_dirname = lv_sourcefile(lv_dir_off).
gv_dirname = lv_dirname.
CLEAR lv_len.
ENDIF.
ENDIF.

CLEAR: gv_string1, gv_result, lv_len, lv_dir_off, lv_filename, lv_dirname,
lv_sourcefile.

ENDFORM. " F_directory_name
*&---------------------------------------------------------------------*
*&      Form F_REVERSE_STRING
*&---------------------------------------------------------------------*
FORM f_reverse_string USING p_local_file TYPE rlgrap-filename.

CALL FUNCTION 'STRING_REVERSE'
EXPORTING
string    = p_local_file
lang      = '1'
IMPORTING
rstring   = p_local_file
EXCEPTIONS
too_small = 1
OTHERS    = 2.

IF sy-subrc NE 0.
CLEAR p_local_file.
ENDIF.

ENDFORM. " F_REVERSE_STRING

*&---------------------------------------------------------------------*
*&      Form F_GET_DIRECTORY_FILES_LIST
*&---------------------------------------------------------------------*
FORM f_get_directory_files_list USING p_dirname TYPE eps2filnam.

CALL FUNCTION 'EPS2_GET_DIRECTORY_LISTING'
EXPORTING
iv_dir_name            = p_dirname
TABLES
dir_list               = gt_dirlist
EXCEPTIONS
invalid_eps_subdir     = 1
sapgparam_failed       = 2
build_directory_failed = 3
no_authorization       = 4
read_directory_failed  = 5
too_many_read_errors   = 6
empty_directory_list   = 7
OTHERS                 = 8.
IF sy-subrc = 1.
MESSAGE text-003 TYPE k_message DISPLAY LIKE k_error.  “invalid_eps_subdir  
LEAVE PROGRAM.
ELSEIF sy-subrc = 2.
MESSAGE text-004 TYPE k_message DISPLAY LIKE k_error.  “sapgparam_failed
LEAVE PROGRAM.
ELSEIF sy-subrc = 3.
MESSAGE text-005 TYPE k_message DISPLAY LIKE k_error.  “build_directory_failed
LEAVE PROGRAM.
ELSEIF sy-subrc = 4.
MESSAGE text-006 TYPE k_message DISPLAY LIKE k_error.  “no_authorization
LEAVE PROGRAM.
ELSEIF sy-subrc = 5.
MESSAGE text-007 TYPE k_message DISPLAY LIKE k_error.  “read_directory_failed
LEAVE PROGRAM.
ELSEIF sy-subrc = 6.
MESSAGE text-008 TYPE k_message DISPLAY LIKE k_error.  “too_many_read_errors
LEAVE PROGRAM.
ELSEIF sy-subrc = 7.
MESSAGE text-009 TYPE k_message DISPLAY LIKE k_error.  “empty_directory_list
LEAVE PROGRAM.
ENDIF.

ENDFORM. " F_GET_DIRECTORY_FILES_LIST
*&---------------------------------------------------------------------*
*&      Form F_FILTER_DIRLIST
*&---------------------------------------------------------------------*
FORM f_filter_dirlist .

DATA: lv_offset TYPE i.

lv_offset = strlen( gv_filename1 ).

LOOP AT gt_dirlist INTO gw_dirlist.
IF gw_dirlist-name(gv_len) <> gv_filename AND
gw_dirlist-name(lv_offset) <> gv_filename1.
DELETE TABLE gt_dirlist FROM gw_dirlist.
ELSEIF gw_dirlist-name(lv_offset) = gv_filename1.
gw_dirlist1 = gw_dirlist.
APPEND gw_dirlist1 TO gt_dirlist1.
DELETE TABLE gt_dirlist FROM gw_dirlist.
ENDIF.
CLEAR gw_dirlist.
ENDLOOP.

CLEAR gw_dirlist1.

ENDFORM. " F_FILTER_DIRLIST
*&---------------------------------------------------------------------*
*&      Form F_READ_SOURCE_FILE
*&---------------------------------------------------------------------*
FORM f_read_source_file .

*•Open file of Application Server
SORT gt_dirlist1 BY mtim DESCENDING.
READ TABLE gt_dirlist1 INTO gw_dirlist1 INDEX 1.
gv_sourcefile = gw_dirlist1-name.
CONCATENATE gv_dirname gv_sourcefile INTO gv_sourcefile.

TRY.
OPEN DATASET gv_sourcefile FOR INPUT IN TEXT MODE ENCODING DEFAULT.
CATCH cx_dynamic_check INTO gv_exception.
gv_error = gv_exception->get_text( ).
MESSAGE gv_error TYPE k_message DISPLAY LIKE k_error.
LEAVE LIST-PROCESSING.
ENDTRY.

REFRESH gt_file.
*•read in Information File
DO.
TRY.
READ DATASET gv_sourcefile INTO gt_file LENGTH gv_length.
CATCH cx_dynamic_check INTO gv_exception.
gv_error = gv_exception->get_text( ).
MESSAGE gv_error TYPE k_message DISPLAY LIKE k_error.
LEAVE LIST-PROCESSING.
ENDTRY.
IF sy-subrc = 0.
APPEND gt_file.
ELSE.
EXIT.
ENDIF.
ENDDO.

*•close file on application server
TRY.
CLOSE DATASET gv_sourcefile.
CATCH cx_dynamic_check INTO gv_exception.
gv_error = gv_exception->get_text( ).
MESSAGE gv_error TYPE k_message DISPLAY LIKE k_error.
LEAVE LIST-PROCESSING.
ENDTRY.

*Delete dataset
* TRY.
*      DELETE DATASET gv_sourcefile.
*    CATCH cx_dynamic_check INTO gv_exception.
*      gv_error = gv_exception->get_text( ).
*      MESSAGE gv_error TYPE k_message DISPLAY LIKE k_error.
*      LEAVE LIST-PROCESSING.
* ENDTRY.

ENDFORM. " F_READ_SOURCE_FILE

*&---------------------------------------------------------------------*
*&      Form F_PROCESS_SOURCEFILE
*&---------------------------------------------------------------------*
FORM f_process_sourcefile .

DATA: lv_offset TYPE offset,
lv_lines  TYPE i.

DESCRIBE TABLE gt_file LINES lv_lines.
gv_tabix = '7'.

LOOP AT gt_file INTO gw_file.
IF sy-tabix >= gv_tabix AND gw_file IS NOT INITIAL.
FIND FIRST OCCURRENCE OF ';' IN gw_file-line RESULTS gv_result.
lv_offset = gv_result-offset + 1.
REPLACE gw_file-line(lv_offset) IN gw_file-line WITH space.
CLEAR gv_result.
FIND FIRST OCCURRENCE OF ';' IN gw_file-line RESULTS gv_result.
gv_string1 = gw_file-line(gv_result-offset).
IF sy-tabix NE lv_lines.
CONCATENATE gv_string1 ';' INTO gv_string1.
ENDIF.
CONCATENATE gw_source-line gv_string1 INTO gw_source-line.
ENDIF.
CLEAR: gv_result, lv_offset, gv_string1.
ENDLOOP.

CLEAR gv_tabix.

ENDFORM. " F_PROCESS_SOURCEFILE

*&---------------------------------------------------------------------*
*&      Form F_PROCESS_TARGETFILE
*&---------------------------------------------------------------------*
FORM f_process_targetfile .

SORT gt_dirlist BY mtim DESCENDING.
READ TABLE gt_dirlist INTO gw_dirlist INDEX 1.
gv_physical_filename = gw_dirlist-name.
CONCATENATE gv_dirname gv_physical_filename INTO gv_physical_filename.

TRY.
OPEN DATASET gv_physical_filename FOR INPUT IN TEXT MODE ENCODING DEFAULT.
CATCH cx_dynamic_check INTO gv_exception.
gv_error = gv_exception->get_text( ).
MESSAGE gv_error TYPE 'E'.
MESSAGE gv_error TYPE k_message DISPLAY LIKE k_error.
LEAVE LIST-PROCESSING.
ENDTRY.

REFRESH gt_file2.
*•read in Information File
DO.
TRY.
READ DATASET gv_physical_filename INTO gt_file2 LENGTH gv_length.
CATCH cx_dynamic_check INTO gv_exception.
gv_error = gv_exception->get_text( ).
MESSAGE gv_error TYPE k_message DISPLAY LIKE k_error.
LEAVE LIST-PROCESSING.
ENDTRY.
IF sy-subrc = 0.
APPEND gt_file2.
ELSE.
EXIT.
ENDIF.
ENDDO.

*•close file on application server
TRY.
CLOSE DATASET gv_physical_filename.
CATCH cx_dynamic_check INTO gv_exception.
gv_error = gv_exception->get_text( ).
MESSAGE gv_error TYPE k_message DISPLAY LIKE k_error.
LEAVE LIST-PROCESSING.
ENDTRY.

ENDFORM.                    " F_PROCESS_TARGETFILE
*&---------------------------------------------------------------------*
*&      Form F_WRITE_FINAL_FILE
*&---------------------------------------------------------------------*
FORM f_write_final_file .

*Open the filepath / dataset on the application server for writing

TRY.
OPEN DATASET gv_physical_filename FOR OUTPUT IN TEXT MODE ENCODING DEFAULT.
CATCH cx_dynamic_check INTO gv_exception.
gv_error = gv_exception->get_text( ).
MESSAGE gv_error TYPE k_message DISPLAY LIKE k_error.
LEAVE LIST-PROCESSING.
ENDTRY.

*Check if the OPEN has been successful
IF sy-subrc EQ 0.
TRY.
TRANSFER gw_source-line TO gv_physical_filename.
CATCH cx_dynamic_check INTO gv_exception.
gv_error = gv_exception->get_text( ).
MESSAGE gv_error TYPE k_message DISPLAY LIKE k_error.
LEAVE LIST-PROCESSING.
ENDTRY.
LOOP AT gt_file2 INTO gw_file2.
TRANSFER gw_file2 TO gv_physical_filename.
ENDLOOP.
ENDIF.

TRY.
CLOSE DATASET gv_physical_filename.
CATCH cx_dynamic_check INTO gv_exception.
gv_error = gv_exception->get_text( ).
MESSAGE gv_error TYPE k_message DISPLAY LIKE k_error.
LEAVE LIST-PROCESSING.
ENDTRY.
IF sy-subrc EQ 0.
MESSAGE text-001 TYPE k_message DISPLAY LIKE 'S'.                                            “File successfully uploaded on application server
LEAVE LIST-PROCESSING.
ENDIF.

ENDFORM. " F_WRITE_FINAL_FILE

7. Execute the program and you will get the selection screen ,based on your requirement ,

enter logical path of the open hub for which merging of header and item files needs to be done.

Selection Screen:

              

On successful merging of the file message will appear that “File successfully uploaded on application server”.

Download final file with both header and item details from application server

Login into ECC system, execute T-code ‘CG3Y’ and enter the source file name (path + item file name) and target file name. Save the target file in CSV format.

Click on export button. Now open the target file in notepad with word wrap setting on.